How to pass the request_id into
temporary table after running the RDF report.
My Program is to process data and dump the data into custom
table, while inserting a data I want to track the details of dump data. For
that I have created one procedure in which we are capturing a data by using
cursor & hose values we are going to insert into our custom table.
We have created a custom table for storing all info as below.
CREATE TABLE CUS.LD_DEBIT_NOTE
(VENDOR_NAME VARCHAR2 (240)
,VENDOR_SITE VARCHAR2 (240)
,PO_NUMBER VARCHAR2 (20)
,PO_DATE DATE
,ITEM_CODE VARCHAR2 (160)
,ITEM_DESCRIPTION VARCHAR2 (240)
,PO_QUANTITY NUMBER
,PO_RATE NUMBER
,NEED_BY_DATE DATE
,GRN_NUMBER VARCHAR2 (30)
,GRN_DATE DATE
,QUANTITY_RECEIVED NUMBER
,AMOUNT NUMBER
,late_days NUMBER
,DEBIT_NOTE_AMOUNT NUMBER
,request_date DATE
,REQUEST_ID NUMBER
,TRANSFER_FLAG VARCHAR2 (1)
)
Synonym is created for custome table
CREATE SYNONYM APPS.LD_DEBIT_NOTE FOR CUS.LD_DEBIT_NOTE;
Necessary grants can be given to APPS
(I have given all the grants to APPS user)
GRANT
ALL ON LD_DEBIT_NOTE TO APPS;
The procedure is as below.
CREATE OR REPLACE PROCEDURE CUS.LD_DEBIT_NOTE_INS
(retcode OUT NUMBER
, errbuff OUT VARCHAR2
, p_from_date IN DATE
, p_to_date IN DATE
, P_CONC_REQUEST_ID IN NUMBER)
AS
-----------------------------------------------------------------------------------------------------
-- Purpose: Create new
records in AP interface tables from source LD_DEBIT_NOTE custome table
--
-- MODIFICATION HISTORY
-- Person Date Comments
-- ----------- ----------
-------------------------------------------
-- Vaibhav Panchgade 26-FEB-2014 New Procedure
-----------------------------------------------------------------------------------------------------
temp_sysdate VARCHAR2(100);
n_of_rows NUMBER;
CURSOR c_dbt_note
IS
SELECT
aps.vendor_name
, ap.vendor_site_code vendor_site
, ph.segment1 po_num
, TO_CHAR(ph.creation_date,'DD-MON-YY') po_creation_date
, TO_CHAR(pll.need_by_date,'DD-MON-YY') need_by_date
, msi.segment1||'.'||msi.segment2||'.'||msi.segment3||'.'||msi.segment4 item_code
, msi.description item_description
, pll.quantity po_quantity
, pol.unit_price po_rate
, rsh.receipt_num grn_num
, TO_CHAR(rct.creation_date,'DD-MON-YY') grn_date
, rct.quantity rcv_quantity
, pol.unit_price*rct.quantity amount
, (TRUNC(rct.creation_date)-TRUNC(pll.need_by_date)) late_days
, (CASE WHEN (TRUNC(rct.creation_date)-TRUNC(pll.need_by_date))<=7
THEN (0.005* pol.unit_price*rct.quantity)
WHEN (TRUNC(rct.creation_date)-TRUNC(pll.need_by_date))<=14
THEN (0.01* pol.unit_price*rct.quantity )
WHEN (TRUNC(rct.creation_date)-TRUNC(pll.need_by_date))<=21
THEN (0.015* pol.unit_price*rct.quantity )
WHEN (TRUNC(rct.creation_date)-TRUNC(pll.need_by_date))<=28
THEN (0.02* pol.unit_price*rct.quantity )
WHEN (TRUNC(rct.creation_date)-TRUNC(pll.need_by_date))<=35
THEN (0.025* pol.unit_price*rct.quantity )
WHEN (TRUNC(rct.creation_date)-TRUNC(pll.need_by_date))<=42
THEN (0.03* pol.unit_price*rct.quantity )
WHEN (TRUNC(rct.creation_date)-TRUNC(pll.need_by_date))<=49
THEN (0.035* pol.unit_price*rct.quantity )
WHEN (TRUNC(rct.creation_date)-TRUNC(pll.need_by_date))<=56
THEN (0.04* pol.unit_price*rct.quantity )
WHEN (TRUNC(rct.creation_date)-TRUNC(pll.need_by_date))<=63
THEN (0.045* pol.unit_price*rct.quantity )
WHEN (TRUNC(rct.creation_date)-TRUNC(pll.need_by_date))>=64
THEN(0.05* pol.unit_price*rct.quantity )
END ) DEBIT_NOTE_AMOUNT
, TO_CHAR(SYSDATE,'DD-MON-YY') request_date
, NULL request_id
FROM rcv_transactions rct
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_lines_all pol
, po_line_locations_all pll
, po_headers_all ph
, org_organization_definitions ood
, ap_suppliers aps
, mtl_system_items_b msi
, ap_supplier_sites_all ap
WHERE 1=1
AND TO_CHAR(rct.creation_date, 'DD-MON-YY') BETWEEN p_from_date AND p_to_date
AND rct.po_header_id = ph.po_header_id
AND rct.po_line_location_id = pll.line_location_id
AND rct.po_line_id = pol.po_line_id
AND rct.shipment_line_id=rsl.shipment_line_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND rsh.ship_to_org_id = ood.organization_id
AND ood.organization_id=94 --Organizaion_id hard coded
AND msi.organization_id=ood.organization_id
AND aps.vendor_id=rsh.vendor_id
AND msi.inventory_item_id=rsl.item_id
AND rsl.item_id =msi.inventory_item_id
AND rct.creation_date > pll.need_by_date
AND rct.vendor_site_id=ap.vendor_site_id
AND rsh.receipt_num NOT IN (SELECT grn_number FROM cus.ld_debit_note)
ORDER BY aps.vendor_name,rsh.receipt_num ;
BEGIN
SELECT TO_CHAR(sysdate, 'DD/MM/RRRR HH24:MI:SS') INTO temp_sysdate FROM DUAL;
fnd_file.new_line(FND_FILE.LOG, 2);
fnd_file.put_line(FND_FILE.LOG, '**********************************************');
fnd_file.put_line(FND_FILE.LOG, 'Process started at '||temp_sysdate);
fnd_file.put_line(FND_FILE.LOG, '**********************************************');
fnd_file.new_line(FND_FILE.LOG, 2);
FOR c_dbt_note_rec IN c_dbt_note
LOOP
INSERT INTO cus.awl_ld_debit_note (VENDOR_NAME
,VENDOR_SITE
,PO_NUMBER
,PO_DATE
,ITEM_CODE
,ITEM_DESCRIPTION
,PO_QUANTITY
,PO_RATE
,NEED_BY_DATE
,GRN_NUMBER
,GRN_DATE
,QUANTITY_RECEIVED
,AMOUNT
,late_days
,DEBIT_NOTE_AMOUNT
,request_date
,REQUEST_ID
)
VALUES (c_dbt_note_rec.VENDOR_NAME
,c_dbt_note_rec.VENDOR_SITE
,c_dbt_note_rec.PO_NUM
,c_dbt_note_rec.po_creation_date
,c_dbt_note_rec.ITEM_CODE
,c_dbt_note_rec.ITEM_DESCRIPTION
,c_dbt_note_rec.PO_QUANTITY
,c_dbt_note_rec.PO_RATE
,c_dbt_note_rec.NEED_BY_DATE
,c_dbt_note_rec.GRN_NUM
,c_dbt_note_rec.GRN_DATE
,c_dbt_note_rec.rcv_quantity
,c_dbt_note_rec.AMOUNT
,c_dbt_note_rec.late_days
,c_dbt_note_rec.DEBIT_NOTE_AMOUNT
,c_dbt_note_rec.request_date
--,--P_CONC_REQ_ID
,P_CONC_REQUEST_ID
) ;
END LOOP;
fnd_file.new_line(FND_FILE.LOG, 2);
fnd_file.put_line(FND_FILE.LOG, '**********************************************');
fnd_file.put_line(FND_FILE.LOG, 'Process Ends ');
fnd_file.put_line(FND_FILE.LOG, 'Number of records inserted in
table AWL_LD_DEBIT_NOTE are : '||n_of_rows||' for request_id : '||P_CONC_REQUEST_ID);
fnd_file.put_line(FND_FILE.LOG, '**********************************************');
fnd_file.new_line(FND_FILE.LOG, 2);
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line(fnd_file.log,' *** No records are found
between given Date range :'||','||sqlerrm||','||' ***');
retcode :=0;
WHEN INVALID_CURSOR THEN
fnd_file.put_line(fnd_file.log,' *** Invalid Operation is occoured on cursor :'||','||sqlerrm||','||' ***');
retcode :=0;
WHEN TOO_MANY_ROWS THEN
fnd_file.put_line(fnd_file.log,' *** Too many rows are inserted on our records :'||','||sqlerrm||','||' ***');
retcode :=0;
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,' *** Un-expected error in procedure
AWL_SUPPLIER_MONEY_TRANSFER : Outside'||','||sqlerrm||','||' ***');
retcode :=2;
END;
/
This procedure fetches records where receipts GRN date is
greater than PO’s need by date. By using cursor ‘c_dbt_note’ & created debit note amount for delayed delivery as
per following information.
|
No
of days delay in receipt of material
|
LD%
on Cost of Material
|
|
|
|
|
upto
7 days
|
0.50%
|
|
|
|
|
7
days to 14 days
|
1.00%
|
|
|
|
|
14
Days to 21 days
|
1.50%
|
|
|
|
|
21
to 28 days
|
2.00%
|
|
|
|
|
28
to 35 days
|
2.50%
|
|
|
|
|
35
to 42 days
|
3.00%
|
|
|
|
|
42
days to 49 days
|
3.50%
|
|
|
|
|
49
days to 56 days
|
4.00%
|
|
|
|
|
56
days to 63 days
|
4.50%
|
|
|
|
|
Above
63 days
|
5.00%
|
Now I have created a RDF report having query as follows
SELECT *
FROM cus.ld_debit_note
WHERE transfer_flag IS NULL
AND grn_date BETWEEN :p_from_date AND :p_to_date;
Having user parameter as
P_CONC_REQUEST_ID
P_FROM_DATE
P_TO_DATE
Report Trigger
Before Report Trigger
In before report trigger we have to initialize the “SRW.USER_EXIT” to get all SRW packages to be initialized.
Here we are using above procedure “AWL_LD_DEBIT_NOTE_INS” to insert record into custome table
LD_DEBIT_NOTE with the request_id which is coming from
the running the report.
FUNCTION BeforeReport RETURN BOOLEAN IS
v1 varchar2(240);
v2 varchar2(240);
BEGIN
SRW.USER_EXIT('FND SRWINIT');
awl_ld_debit_note_ins(v1,v2,:p_from_date,:p_to_date,:p_conc_request_id);
RETURN (TRUE);
END;
Note: When we are calling multiple user
exits in a trigger we should call these
User
Exits in separate Blocks (i.e. BEGIN .. END).
After Report Trigger
In after report trigger we have to de initialized the “SRW.USER_EXIT” .
FUNCTION AfterReport RETURN BOOLEAN IS
BEGIN
SRW.USER_EXIT('FND SRWEXIT');
RETURN (TRUE);
END;
