Oracle Apps Technical: Price List API

CREATE OR REPLACE PACKAGE BODY apps."XXABC_AUS_PRICE_LOAD_PKG"

/*+-------------------------------------------------------------------------+

| Project Name : XXX

| Application: Oracle Applications

| Program: XXABC_AUS_PRICE_LOAD_PKG.pkb

| Package for Price List for Australia Rollout

| Description :

| Author:

| Date: 13-May-2022

| Version: 1.0

| Modifications:

| Date Name Version Comments

|---------------------------------------------------------------------------+

| 13-May-2022 1.0 Initial Version

+---------------------------------------------------------------------------+*/

PROCEDURE price_list_load (p_errbuf OUT VARCHAR2, p_retcode OUT NUMBER)

v_return_status VARCHAR2 (1) := NULL;

v_msg_count NUMBER := 0;

v_msg_data VARCHAR2 (2000);

l_price_list_rec qp_price_list_pub.price_list_rec_type;

v_price_list_rec qp_price_list_pub.price_list_rec_type;

v_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;

l_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;

v_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;

v_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;

v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;

v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;

l_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;

v_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;

v_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;

k NUMBER := 1;

j NUMBER := 1;

l_list_header_id VARCHAR2 (240) DEFAULT NULL;

l_exist_item_id NUMBER;

l_pricing_attr_id NUMBER;

l_list_line_id NUMBER;

v_uom_code VARCHAR2 (24);

v_process_flag VARCHAR2 (24);

v_customer_order_flag VARCHAR2 (24);

l_user_id NUMBER := fnd_global.user_id;

l_resp_id NUMBER := fnd_global.resp_id;

l_resp_appl_id NUMBER := fnd_global.resp_appl_id;

v_inventory_item_id NUMBER;

v_status VARCHAR2 (10);

v_err_msg VARCHAR2 (2000);

l_rec_cnt NUMBER := 0;

l_rec_passed NUMBER := 0;

l_rec_falied NUMBER := 0;

CURSOR cur_price_list

SELECT ROWID, TRIM (conversion_site_code) conversion_site_code,

TRIM (conversion_plant_code) conversion_plant_code,

TRIM (legacy_identifier) legacy_identifier,

TRIM (target_destination) target_destination,

TRIM (legacy_item_number) legacy_item_number,

TRIM (uom_code) uom_code,

TRIM (price_list_name) price_list_name,

TRIM (price_currency) price_currency,

TRIM (price_list_price) price_list_price,

TRIM (effective_start_date) effective_start_date,

TRIM (effective_end_date) effective_end_date,

TRIM (product_attribute) product_attribute,

TRIM (product_attribute_context) product_attribute_context,

TRIM (status) status, TRIM (error_message) error_message

FROM xxcust.XXABC_au_price_stg

WHERE NVL (status, 'X') <> 'P';

BEGIN

-- INITIALIZATION REQUIRED FOR R12

mo_global.set_policy_context ('S', fnd_profile.VALUE ('ORG_ID'));

mo_global.init ('ONT');

fnd_global.apps_initialize (user_id => l_user_id,

resp_id => l_resp_id,

resp_appl_id => l_resp_appl_id

);

-------------------------------------------------------------------------------------

-- Update the flag to NULL before processing error records

BEGIN

UPDATE xxcust.XXABC_au_price_stg

SET error_message = NULL,

process_flag = NULL

WHERE status = 'E';

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG,

'Error in updating staging table'

);

END;

FOR i_rec IN cur_price_list

LOOP

v_inventory_item_id := NULL;

l_list_header_id := NULL;

l_exist_item_id := NULL;

l_pricing_attr_id := NULL;

l_list_line_id := NULL;

v_uom_code := 'EA'; -- reset to EA if not find

v_process_flag := NULL;

v_customer_order_flag := NULL;

v_status := 'P'; -- reset to P every time

v_err_msg := NULL; -- reset to NULL

-- Check if item exists in validation org to assign price list

BEGIN

SELECT inventory_item_id, primary_uom_code,

msib.customer_order_flag

INTO v_inventory_item_id, v_uom_code,

v_customer_order_flag

FROM mtl_system_items_b msib, org_organization_definitions ood

WHERE TRIM (segment1) = TRIM (i_rec.legacy_item_number)

AND ood.organization_id = msib.organization_id

AND ood.organization_code = '590';

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line

(fnd_file.LOG,

'Item does not exists in validation org --> '

|| i_rec.legacy_item_number

);

v_status := 'E';

v_err_msg :=

'Item does not exists in validation org --> '

|| i_rec.legacy_item_number;

END;

IF v_customer_order_flag = 'N'

THEN

fnd_file.put_line

(fnd_file.LOG,

'Customer order checkbox is not checked for item in validation org --> '

|| i_rec.legacy_item_number

);

v_status := 'E';

v_err_msg :=

'Customer order checkbox is not checked for item in validation org --> '

|| i_rec.legacy_item_number;

END IF;

------------------------------------------------------------------

-- Price list alredy exist or not in XXX

BEGIN

SELECT DISTINCT qlh.list_header_id

INTO l_list_header_id

FROM apps.qp_list_headers qlh

WHERE qlh.NAME = TRIM (i_rec.price_list_name);

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line

(fnd_file.LOG,

'Exception while deriving the price list name -'

|| i_rec.price_list_name

);

v_status := 'E';

v_err_msg :=

v_err_msg

|| '/Exception while deriving the price list name -'

|| i_rec.price_list_name;

END;

IF v_status = 'P'

THEN

--------------------------------------------------------------------------------

-- Item already exist on price list check

BEGIN

SELECT qpp.product_attr_value, qpp.pricing_attribute_id,

qll.list_line_id

INTO l_exist_item_id, l_pricing_attr_id,

l_list_line_id

FROM apps.qp_list_headers qlh,

apps.qp_list_lines qll,

apps.qp_pricing_attributes qpp

WHERE qlh.list_header_id = qll.list_header_id

AND qll.list_line_id = qpp.list_line_id

AND qpp.list_header_id = qlh.list_header_id

AND qlh.NAME = TRIM (i_rec.price_list_name)

AND qpp.product_attr_value = TRIM (v_inventory_item_id);

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

-----------------------------------------------------------------------------

IF l_exist_item_id IS NOT NULL

THEN

v_process_flag := 'UPDATE';

k := 1; -- UPDATE the price list line rec

l_price_list_line_tbl (k).list_header_id := l_list_header_id;

l_price_list_line_tbl (k).list_line_id := l_list_line_id;

l_price_list_line_tbl (k).list_line_type_code := 'PLL';

l_price_list_line_tbl (k).operation := qp_globals.g_opr_update;

l_price_list_line_tbl (k).operand := i_rec.price_list_price;

l_price_list_line_tbl (k).start_date_active := SYSDATE;

l_price_list_line_tbl (k).arithmetic_operator := 'UNIT_PRICE';

j := 1;

l_pricing_attr_tbl (j).pricing_attribute_id :=

l_pricing_attr_id;

l_pricing_attr_tbl (j).list_line_id := l_list_line_id;

l_pricing_attr_tbl (j).product_attribute_context := 'ITEM';

l_pricing_attr_tbl (j).product_attribute :=

'PRICING_ATTRIBUTE1';

l_pricing_attr_tbl (j).product_attr_value :=

v_inventory_item_id;

l_pricing_attr_tbl (j).product_uom_code := v_uom_code;

l_pricing_attr_tbl (j).excluder_flag := 'N';

l_pricing_attr_tbl (j).attribute_grouping_no := 1;

l_pricing_attr_tbl (j).price_list_line_index := 1;

l_pricing_attr_tbl (j).operation := qp_globals.g_opr_update;

ELSE

v_process_flag := 'INSERT';

k := 1; -- create the price list line rec

l_price_list_line_tbl (k).list_header_id := l_list_header_id;

l_price_list_line_tbl (k).list_line_id :=

qp_list_lines_s.NEXTVAL;

l_price_list_line_tbl (k).list_line_type_code := 'PLL';

l_price_list_line_tbl (k).operation := qp_globals.g_opr_create;

l_price_list_line_tbl (k).operand := i_rec.price_list_price;

l_price_list_line_tbl (k).start_date_active := SYSDATE;

l_price_list_line_tbl (k).arithmetic_operator := 'UNIT_PRICE';

j := 1; -- create the price list attributes rec

l_pricing_attr_tbl (j).pricing_attribute_id :=

qp_pricing_attributes_s.NEXTVAL;

l_pricing_attr_tbl (j).list_line_id := qp_list_lines_s.CURRVAL;

l_pricing_attr_tbl (j).product_attribute_context := 'ITEM';

l_pricing_attr_tbl (j).product_attribute :=

'PRICING_ATTRIBUTE1';

l_pricing_attr_tbl (j).product_attr_value :=

v_inventory_item_id;

l_pricing_attr_tbl (j).product_uom_code := v_uom_code;

l_pricing_attr_tbl (j).excluder_flag := 'N';

l_pricing_attr_tbl (j).attribute_grouping_no := 1;

l_pricing_attr_tbl (j).price_list_line_index := 1;

l_pricing_attr_tbl (j).operation := qp_globals.g_opr_create;

END IF;

qp_price_list_pub.process_price_list

(p_api_version_number => 1,

p_init_msg_list => fnd_api.g_false,

p_return_values => fnd_api.g_false,

p_commit => fnd_api.g_false,

x_return_status => v_return_status,

x_msg_count => v_msg_count,

x_msg_data => v_msg_data,

p_price_list_rec => l_price_list_rec,

p_price_list_line_tbl => l_price_list_line_tbl,

p_pricing_attr_tbl => l_pricing_attr_tbl,

x_price_list_rec => v_price_list_rec,

x_price_list_val_rec => v_price_list_val_rec,

x_price_list_line_tbl => v_price_list_line_tbl,

x_price_list_line_val_tbl => v_price_list_line_val_tbl,

x_qualifiers_tbl => v_qualifiers_tbl,

x_qualifiers_val_tbl => v_qualifiers_val_tbl,

x_pricing_attr_tbl => v_pricing_attr_tbl,

x_pricing_attr_val_tbl => v_pricing_attr_val_tbl

);

IF v_return_status = fnd_api.g_ret_sts_success

THEN

v_status := 'P';

v_err_msg :=

'The Item has been successfully loaded into the price list --> '

|| i_rec.legacy_item_number;

ELSE

ROLLBACK;

v_msg_data := NULL;

FOR k IN 1 .. v_msg_count

LOOP

v_msg_data :=

oe_msg_pub.get (p_msg_index => k,

p_encoded => 'F');

v_msg_data := v_msg_data || v_msg_data;

END LOOP;

v_status := 'E';

v_err_msg :=

'The Error Message Due to which The Item has not been loaded to Price List is --> '

|| v_msg_data;

END IF;

IF v_status = 'P'

THEN

UPDATE xxcust.XXABC_au_price_stg

SET status = v_status,

error_message = v_err_msg,

process_flag = v_process_flag

WHERE ROWID = i_rec.ROWID;

COMMIT;

l_rec_cnt := l_rec_cnt + 1;

ELSIF v_status = 'E'

THEN

UPDATE xxcust.XXABC_au_price_stg

SET status = v_status,

error_message = v_err_msg,

process_flag = v_process_flag

WHERE ROWID = i_rec.ROWID;

l_rec_falied := l_rec_falied + 1;

COMMIT;

END IF;

l_rec_passed := l_rec_passed + 1;

END LOOP;

fnd_file.put_line (fnd_file.LOG,

'Total Records passed in the program =-> '

|| l_rec_passed

);

fnd_file.put_line (fnd_file.LOG,

'Total Records Inserted successfully =-> '

|| l_rec_cnt

);

fnd_file.put_line (fnd_file.LOG,

'Total Records Failed =-> '

|| l_rec_falied

);

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG,

'Exception while loading the price list :- '

|| SQLERRM

);

END price_list_load;

END XXABC_aus_price_load_pkg;

Tuesday, September 3, 2024

Price List API