Script for Updating Price List Lines :-
Issue :- Need to update unit price of items which are in given Price List ans having unit price as $9999999.
Process Followed :- First end date all the lines in QP_LIST_LINES which falls under above conditions and then insert new records in QP_LIST_LINES.
API Used :- qp_price_list_pub.process_price_list(Both for Insert and Update)
Steps Followed :-
1. Create a staging table to store Item_Id, New Unit_Price, Transaction_ID, Status and Errror_Message.
2. Created a synonym, sequence, trigger and index on staging table.
3. Validated Items Ids given (For Duplicate Records, Null Records and existence of item ids) in the data file).
4. Send List_Header_Id, List_Line_Id, Pricing_Attribute_Id and field that has to be updated (In this example, end_date_active) and operation should be qp_globals.g_opr_update.
===========================================================================
Staging Table , Synonym, Sequence, Trigger Creation
SET serverout on size 1000000
CL SCR;
PROMPT +------------------------------------------------------------------------+
PROMPT Execution of script starts
PROMPT +------------------------------------------------------------------------+
--drop table lss_unit_price_stg;
CREATE TABLE lss_unit_price_stg(
transaction_id_stg NUMBER NOT NULL
,status_stg VARCHAR2(2)
,item_id NUMBER
,unit_price NUMBER
,error_message VARCHAR2(2000)
);
--drop synonym lss_unit_price_stg;
CREATE SYNONYM lss_unit_price_stg FOR lss.lss_unit_price_stg;
--drop index lss_unit_price_stg_ui;
CREATE UNIQUE INDEX lss_unit_price_stg_ui ON lss_unit_price_stg(transaction_id_stg);
--drop sequence lss_unit_price_stg_s1;
CREATE SEQUENCE lss_unit_price_stg_s1
START WITH 1
INCREMENT BY 1;
-- Creation Of Trigger
CREATE OR REPLACE TRIGGER lss_unit_price_stg_trg
BEFORE INSERT
ON lss_unit_price_stg
FOR EACH ROW
BEGIN
IF :NEW.transaction_id_stg IS NULL
THEN
SELECT lss_unit_price_stg_s1.NEXTVAL, 'N'
INTO :NEW.transaction_id_stg, :NEW.status_stg
FROM DUAL;
END IF;
END;
/
============================
Package Specification
CREATE OR REPLACE PACKAGE lss_unit_price_update_pkg
IS
PROCEDURE unit_price_validate (p_list_header_id IN NUMBER);
PROCEDURE unit_price_load (p_list_header_id IN NUMBER);
END lss_unit_price_update_pkg;
/
===============================
Package Body
CREATE OR REPLACE PACKAGE BODY lss_unit_price_update_pkg
IS
PROCEDURE unit_price_validate (p_list_header_id IN NUMBER)
IS
l_error_msg VARCHAR2 (3000);
l_item_id NUMBER;
CURSOR item_stg_cur
IS
SELECT item_id, transaction_id_stg
FROM lss_unit_price_stg
WHERE status_stg IN ('N', 'VE');
CURSOR dup_item_cur
IS
SELECT item_id
FROM lss_unit_price_stg
GROUP BY item_id
HAVING COUNT (*) > 1;
CURSOR item_nul_cur
IS
SELECT transaction_id_stg
FROM lss_unit_price_stg
WHERE item_id IS NULL;
BEGIN
-- Validating Item_Id
FOR item_stg_rec IN item_stg_cur
LOOP
BEGIN
SELECT DISTINCT inventory_item_id
INTO l_item_id
FROM qp_list_lines
WHERE 1 = 1
AND list_header_id = p_list_header_id
--AND operand = 9999999
--AND end_date_active IS NULL
AND inventory_item_id = item_stg_rec.item_id;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg :=
'Inventory Item Id Not Found' || SQLERRM || ';;';
UPDATE lss_unit_price_stg
SET status_stg = 'VE',
error_message = error_message || l_error_msg
WHERE 1 = 1 AND item_id = item_stg_rec.item_id;
COMMIT;
END;
END LOOP;
DBMS_OUTPUT.put_line ('Vallidating ItemID');
-- Validating for Duplicate records
FOR dup_item_rec IN dup_item_cur
LOOP
BEGIN
l_error_msg := 'Duplicate Item Id ;;';
UPDATE lss_unit_price_stg
SET status_stg = 'VE',
error_message = error_message || l_error_msg
WHERE item_id = dup_item_rec.item_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error While updating Staging Table for the Item(Duplicate)'
|| dup_item_rec.item_id
);
END;
END LOOP;
DBMS_OUTPUT.put_line ('Vallidating Duplicate Records');
-- Validating Null Item Ids
FOR item_nul_rec IN item_nul_cur
LOOP
BEGIN
l_error_msg := 'Given Item Id is Null ;;';
UPDATE lss_unit_price_stg
SET status_stg = 'VE',
error_message = error_message || l_error_msg
WHERE transaction_id_stg = item_nul_rec.transaction_id_stg;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error While updating Staging Table for the Transaction(NULL)'
|| item_nul_rec.transaction_id_stg
);
END;
END LOOP;
DBMS_OUTPUT.put_line ('Vallidating D');
BEGIN
UPDATE lss_unit_price_stg
SET status_stg = 'V'
WHERE status_stg = 'N' AND error_message IS NULL;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
('Exception while updating staging table for successfully calidated records'
);
END;
END unit_price_validate;
PROCEDURE unit_price_load (p_list_header_id IN NUMBER)
AS
pl_code VARCHAR2 (2000);
l_price NUMBER;
l_count NUMBER;
l_msg_dummy VARCHAR2 (2000);
l_output VARCHAR2 (2000);
l_msg_data VARCHAR2 (2000);
l_msg_count NUMBER;
l_list_line_id NUMBER;
l_pricing_attribute_id NUMBER;
l_item_id NUMBER;
gpr_return_status VARCHAR2 (10) := NULL;
gpr_msg_count NUMBER := 0;
gpr_msg_data VARCHAR2 (2000);
gpr_price_list_rec qp_price_list_pub.price_list_rec_type;
gpr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
gpr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
gpr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
gpr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
gpr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
gpr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
gpr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
ppr_price_list_rec qp_price_list_pub.price_list_rec_type;
ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
i NUMBER := 1;
k NUMBER := 0;
j NUMBER := 0;
v_userid NUMBER;
v_applid NUMBER;
v_respid NUMBER;
v_loginid NUMBER;
CURSOR item_stg_cur
IS
SELECT item_id, transaction_id_stg
FROM lss_unit_price_stg
WHERE status_stg = 'V';
BEGIN
DBMS_OUTPUT.put_line (p_list_header_id);
BEGIN
fnd_client_info.set_org_context (141);
END;
BEGIN
SELECT user_id
INTO v_userid
FROM fnd_user
WHERE user_name LIKE 'XXXX';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_userid := NULL;
END;
BEGIN
SELECT MAX (login_id)
INTO v_loginid
FROM fnd_logins
WHERE 1 = 1 AND user_id = v_userid;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_loginid := NULL;
END;
BEGIN
SELECT application_id, responsibility_id
INTO v_applid, v_respid
FROM fnd_responsibility_tl
WHERE responsibility_name LIKE 'Oracle Pricing Manager';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_applid := NULL;
v_respid := NULL;
END;
fnd_global.apps_initialize (v_userid, v_respid, v_applid); -- Prod
FOR item_stg_rec IN item_stg_cur
LOOP
BEGIN
DBMS_OUTPUT.put_line ('Entered Loop');
SELECT qpll.list_line_id, qppa.pricing_attribute_id,
qpll.inventory_item_id
INTO l_list_line_id, l_pricing_attribute_id,
l_item_id
FROM qp_list_lines qpll, qp_pricing_attributes qppa
WHERE qpll.list_line_id = qppa.list_line_id
AND qpll.list_header_id = p_list_header_id
AND qpll.inventory_item_id = item_stg_rec.item_id;
DBMS_OUTPUT.put_line ('Retrieving The Price List');
gpr_price_list_rec.list_header_id := p_list_header_id;
gpr_price_list_rec.list_type_code := 'PRL';
gpr_price_list_rec.NAME := 'Test_Price_List_API_990';
gpr_price_list_rec.description := 'Test_Price_List_API_990';
gpr_price_list_rec.currency_code := 'USD';
pl_code := NULL;
gpr_price_list_rec.operation := qp_globals.g_opr_update;
gpr_pricing_attr_tbl.DELETE;
k := 1;
gpr_price_list_line_tbl (k).list_line_id := l_list_line_id;
gpr_price_list_line_tbl (k).operation := qp_globals.g_opr_update;
gpr_price_list_line_tbl (k).end_date_active := NULL;
gpr_price_list_line_tbl (k).last_updated_by := v_userid;
gpr_price_list_line_tbl (k).last_update_login := v_loginid;
gpr_pricing_attr_tbl (k).pricing_attribute_id :=
l_pricing_attribute_id;
gpr_pricing_attr_tbl (k).list_line_id := l_list_line_id;
gpr_pricing_attr_tbl (k).product_attr_value := TO_CHAR (l_item_id);
gpr_pricing_attr_tbl (k).operation := qp_globals.g_opr_update;
BEGIN
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 => gpr_return_status,
x_msg_count => gpr_msg_count,
x_msg_data => gpr_msg_data,
p_price_list_rec => gpr_price_list_rec,
p_price_list_line_tbl => gpr_price_list_line_tbl,
p_pricing_attr_tbl => gpr_pricing_attr_tbl,
x_price_list_rec => ppr_price_list_rec,
x_price_list_val_rec => ppr_price_list_val_rec,
x_price_list_line_tbl => ppr_price_list_line_tbl,
x_price_list_line_val_tbl => ppr_price_list_line_val_tbl,
x_qualifiers_tbl => ppr_qualifiers_tbl,
x_qualifiers_val_tbl => ppr_qualifiers_val_tbl,
x_pricing_attr_tbl => ppr_pricing_attr_tbl,
x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl
);
DBMS_OUTPUT.put_line ('The Price List Line' || k
|| 'is inserted'
);
DBMS_OUTPUT.put_line
( 'Price List line : '
|| gpr_pricing_attr_tbl (k).product_attr_value
);
COMMIT;
BEGIN
UPDATE lss_unit_price_stg
SET status_stg = 'L'
WHERE transaction_id_stg = item_stg_rec.transaction_id_stg;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Excpetion when updating Staging table for Item(Updated Item)'
|| l_item_id
);
END;
IF gpr_return_status <> 'S'
THEN
oe_msg_pub.get (k,
fnd_api.g_false,
gpr_msg_data,
l_msg_dummy
);
l_output := (TO_CHAR (k) || ': ' || l_msg_data);
DBMS_OUTPUT.put_line (l_output);
BEGIN
UPDATE lss_unit_price_stg
SET status_stg = 'LE',
error_message = error_message || l_output || ';;'
WHERE transaction_id_stg =
item_stg_rec.transaction_id_stg;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Excpetion when updating Staging table for Item(Update Failed)'
|| l_item_id
);
END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
UPDATE lss_unit_price_stg
SET status_stg = 'LE',
error_message =
error_message || 'Unknown Exception ;;'
WHERE transaction_id_stg =
item_stg_rec.transaction_id_stg;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Excpetion when updating Staging table for Item(Update Failed)'
|| l_item_id
);
END;
DBMS_OUTPUT.put_line ('Error Processing The Price List...');
DBMS_OUTPUT.put_line ( 'Price List Name : '
|| gpr_price_list_rec.NAME
);
DBMS_OUTPUT.put_line (SQLERRM);
END;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
END LOOP;
END unit_price_load;
END lss_unit_price_update_pkg;
/
