CREATE OR REPLACE PACKAGE BODY apps."XXABC_AUS_PRICE_LOAD_PKG"
AS
/*+-------------------------------------------------------------------------+
| Copyright (c) 2005 ABC Corporation
| Project Name : XXX
| All rights reserved.
|
| 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)
IS
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
IS
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;
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;
/