create table xx_pricelist_del_conv_stg
(
TRANSACTION_ID_STG NUMBER
,STATUS_STG VARCHAR2(10)
,LIST_HEADER_ID NUMBER
,NAME VARCHAR2(240)
,DESCRIPTION VARCHAR2(2000)
,LIST_LINE_ID NUMBER
,list_line_type_code VARCHAR2(240)
,list_type_code VARCHAR2(240)
,OPERAND NUMBER);
CREATE SEQUENCE xx_pricelist_del_conv_stg_s
START WITH 1
INCREMENT BY 1;
--drop trigger xxcus.XX_GL_BUD_FISCAL_CONV_t
CREATE OR REPLACE TRIGGER xx_pricelist_del_conv_t
BEFORE insert
ON xx_pricelist_del_conv_stg
FOR EACH ROW
BEGIN
IF :NEW.transaction_id_stg IS NULL
THEN
SELECT xx_pricelist_del_conv_stg_S.NEXTVAL,'V'
INTO :NEW.transaction_id_stg,:NEW.status_stg
FROM DUAL;
END IF;
END;
The transaction_id_stg is the unique number sequence of the table.
2. Extract and insert the data from Oracle into the staging table
insert into xx_pricelist_del_conv_stg (
list_header_id,
NAME,
description,
list_line_id,
operand,
list_line_type_code,
list_type_code)
SELECT qlh.list_header_id,
NAME,
description,
qll.list_line_id,
operand,
list_line_type_code,
list_type_code
FROM qp_list_headers qlh, qp_list_lines qll
WHERE qlh.list_header_id = qll.list_header_id
AND NAME = '573810 SMITHS MEDICAL CANADA LTD INTERNAL (US TO CANADA)'
AND operand = 0;
commit;
3. Compile the below package and run the delete process.
----------------------------------------------------------------------------------------
declare
l_errbuf varchar2(2000);
l_retcode number;
begin
xx_pricelist_lines_del_pkg.delete_process (
l_errbuf, l_retcode,
commit;
end;
----------------------------------------------------------------------------------------
The package is as follows:
CREATE OR REPLACE PACKAGE BODY xx_pricelist_lines_del_pkg
AS
/*===================================================================================================================
FILE NAME : xx_pricelist_lines_del_pkg.pkb
VERSION : 1.1
OBJECT NAME: PACKAGE BODY
OBJECT TYPE: public package body
DESCRIPTION: This package is used to delete the lines for the pricelist in the staging table from the base table.
PARAMETERS :
==========
NAME TYPE DESCRIPTION
----------------- -------- ----------------------------------------------
P_
RETURNS : --If Necessary
HISTORY :
=======
ver date author Modification
1.0 05-May-07 S.R.Arun Kumar Package Created
1.1 08-Jul-07 S.R.Arun Kumar Added comments to the package
===================================================================================================================*/
PROCEDURE delete_process (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
ip_transaction_from NUMBER,
ip_transaction_to NUMBER
)
/*===================================================================================================================
OBJECT NAME: PRODEDURE
OBJECT TYPE: public procedure
DESCRIPTION: This procedure is used to delete the lines for the pricelist in the staging table from the base table.
PARAMETERS :
==========
NAME TYPE DESCRIPTION
----------------- -------- ----------------------------------------------
ERRBUF OUT This is used to get the error description after the
concurrent program is ran
RETCODE OUT This is used to get the return code after the
concurrent program is ran
IP_TRANSACTION_FROM IN This the transaction_id from where we want to start
IP_TRANSACTION_TO IN This the transaction_id to where we want to end
===================================================================================================================*/
AS
gpr_return_status VARCHAR2 (1) := 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;
l_count1 NUMBER := 1;
l_count2 NUMBER := 1;
l_transaction_from NUMBER := ip_transaction_from;
l_transaction_to NUMBER := ip_transaction_to;
/******************************************************************************
-- Cursor to retrive the data from the staging table and pass into the interface
table
******************************************************************************/
CURSOR cur_pricelist
IS
SELECT list_header_id, NAME, description, list_line_type_code,
list_line_id, list_type_code
FROM xx_pricelist_del_conv_stg
WHERE transaction_id_stg BETWEEN l_transaction_from
AND l_transaction_to
AND status_stg = 'V';
BEGIN
FOR pricelist_rec IN cur_pricelist
LOOP
fnd_global.apps_initialize (1169, 50773, 660);
DBMS_OUTPUT.put_line ('Record = ');
DBMS_OUTPUT.put_line (pricelist_rec.list_header_id);
DBMS_OUTPUT.put_line (pricelist_rec.list_line_id);
gpr_price_list_rec.list_header_id := pricelist_rec.list_header_id;
gpr_price_list_rec.NAME := pricelist_rec.NAME;
gpr_price_list_rec.list_type_code := pricelist_rec.list_type_code;
gpr_price_list_rec.description := pricelist_rec.description;
gpr_price_list_rec.operation := qp_globals.g_opr_update;
-- delete the price list line rec
gpr_price_list_line_tbl (l_count1).list_header_id :=
pricelist_rec.list_header_id;
gpr_price_list_line_tbl (l_count1).list_line_id :=
pricelist_rec.list_line_id;
gpr_price_list_line_tbl (l_count1).list_line_type_code :=
pricelist_rec.list_line_type_code;
gpr_price_list_line_tbl (l_count1).operation :=
qp_globals.g_opr_delete;
--dbms_output.put_line('before process price list ');
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
);
IF ppr_price_list_line_tbl.COUNT > 0
THEN
FOR l_count1 IN 1 .. ppr_price_list_line_tbl.COUNT
LOOP
DBMS_OUTPUT.put_line
( 'Record = '
|| l_count1
|| 'Return Status = '
|| ppr_price_list_line_tbl (l_count1).return_status
);
END LOOP;
END IF;
COMMIT;
IF ppr_price_list_line_tbl (l_count1).return_status = 'S'
THEN
/******************************************************************************
-- Updating the status_stg to 'S' after sucessfull completion of delete
******************************************************************************/
UPDATE xx_pricelist_del_conv_stg
SET status_stg = 'S'
WHERE list_line_id = pricelist_rec.list_line_id
AND list_header_id = pricelist_rec.list_header_id;
COMMIT;
ELSE
/******************************************************************************
-- Updating the status_stg to 'IE' if any error occurs in the deletion process
******************************************************************************/
UPDATE xx_pricelist_del_conv_stg
SET status_stg = 'IE'
WHERE list_line_id = pricelist_rec.list_line_id
AND list_header_id = pricelist_rec.list_header_id;
COMMIT;
END IF;
IF gpr_return_status <> fnd_api.g_ret_sts_success
THEN
RAISE fnd_api.g_exc_unexpected_error;
UPDATE xx_pricelist_del_conv_stg
SET status_stg = 'IE'
WHERE list_line_id = pricelist_rec.list_line_id
AND list_header_id = pricelist_rec.list_header_id;
COMMIT;
END IF;
DBMS_OUTPUT.put_line ('after process price list ');
FOR l_count1 IN 1 .. gpr_msg_count
LOOP
gpr_msg_data :=
oe_msg_pub.get (p_msg_index => l_count1,
p_encoded => 'F');
DBMS_OUTPUT.put_line ( 'err msg '
|| l_count1
|| ' is: '
|| gpr_msg_data
);
NULL;
END LOOP;
/******************************************************************************
-- Commiting the records after every 500 records deletion process
******************************************************************************/
IF MOD (cur_pricelist%ROWCOUNT, 500) = 0
THEN
DBMS_OUTPUT.put_line ('We are in price_list_id commit block');
COMMIT;
END IF;
COMMIT;
END LOOP;
EXCEPTION
WHEN fnd_api.g_exc_error
THEN
gpr_return_status := fnd_api.g_ret_sts_error;
-- Get message count and data
--dbms_output.put_line('err msg 1 is : ' || gpr_msg_data);
COMMIT;
WHEN fnd_api.g_exc_unexpected_error
THEN
gpr_return_status := fnd_api.g_ret_sts_unexp_error;
FOR l_count1 IN 1 .. gpr_msg_count
LOOP
gpr_msg_data :=
oe_msg_pub.get (p_msg_index => l_count1,
p_encoded => 'F');
-- Get message count and data
DBMS_OUTPUT.put_line ( 'err msg '
|| l_count1
|| ' is: '
|| gpr_msg_data
);
NULL;
COMMIT;
END LOOP;
WHEN OTHERS
THEN
gpr_return_status := fnd_api.g_ret_sts_unexp_error;
-- Get message count and data
END;
END xx_pricelist_lines_del_pkg;
/
No comments:
Post a Comment