Saturday, March 17, 2018
Script to create Purchase order Receipt using "Receiving Transaction Processor" (RCV_HEADERS_INTERFACE, RCV_TRANSACTIONS_INTERFACE)
In this post, we have shared a
sample script that can be used to generate a goods receipt based on the
purchase order number. This script can be modified to import goods receipts via
flat file as well.
Instance: R12.2.4
Steps to Create a Receipt:
- Create data into Interface table RCV_HEADERS_INTERFACE AND RCV_TRANSACTIONS_INTERFACE
- Submit the standard import program named “Receiving Transaction Processor”
Script:
DECLARE
ln_user_id NUMBER;
ln_po_header_id NUMBER;
ln_vendor_id NUMBER;
lv_segment1 VARCHAR2(20);
ln_org_id NUMBER;
ln_line_num NUMBER;
ln_parent_txn_id NUMBER;
CURSOR po_line IS
SELECT pl.item_id,
pl.po_line_id,
pl.line_num,
--pll.quantity,
pd.quantity_ordered quantity,
pd.po_distribution_id,
pl.unit_meas_lookup_code,
mp.organization_code,
pll.line_location_id,
pll.closed_code,
pll.quantity_received,
pll.cancel_flag,
pll.shipment_num
FROM po_lines_all pl,
po_line_locations_all
pll,
po_distributions_all pd,
mtl_parameters mp
WHERE pl.po_header_id = ln_po_header_id
AND pl.po_line_id = pll.po_line_id
AND pd.line_location_id = pll.line_location_id
AND pd.po_line_id = pl.po_line_id
AND pll.ship_to_organization_id = mp.organization_id;
BEGIN
dbms_output.put_line('***ezroi rcv api insert script***');
SELECT po_header_id,
vendor_id,
segment1,
org_id
INTO ln_po_header_id,
ln_vendor_id,
lv_segment1,
ln_org_id
FROM po_headers_all
WHERE segment1 = '401000028'
AND org_id = 169;
SELECT user_id
INTO ln_user_id
FROM fnd_user
WHERE user_name = upper('RECEIVERUSER');
INSERT INTO rcv_headers_interface
(header_interface_id,
group_id,
processing_status_code,
receipt_source_code,
transaction_type,
last_update_date,
last_updated_by,
last_update_login,
vendor_id,
expected_receipt_date,
validation_flag,
org_id)
SELECT rcv_headers_interface_s.nextval,
rcv_interface_groups_s.nextval,
'PENDING',
'VENDOR',
'NEW',
sysdate,
ln_user_id,
0,
ln_vendor_id,
sysdate,
'y',
ln_org_id
FROM dual;
FOR cur_po_line IN po_line
LOOP
IF cur_po_line.closed_code IN ('APPROVED', 'OPEN')
AND cur_po_line.quantity_received < cur_po_line.quantity
AND NVL(cur_po_line.cancel_flag,'N') = 'N'
THEN
INSERT INTO rcv_transactions_interface
(interface_transaction_id,
group_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
po_header_id,
po_line_id,
item_id,
quantity,
unit_of_measure,
po_line_location_id,
po_distribution_id,
auto_transact_code,
receipt_source_code,
to_organization_code,
source_document_code,
header_interface_id,
validation_flag,
org_id)
SELECT rcv_transactions_interface_s.nextval,
rcv_interface_groups_s.currval,
sysdate,
ln_user_id,
sysdate,
ln_user_id,
0,
'RECEIVE',
SYSDATE,
'PENDING',
'BATCH',
'PENDING',
ln_po_header_id,
cur_po_line.po_line_id,
cur_po_line.item_id,
cur_po_line.quantity,
cur_po_line.unit_meas_lookup_code,
cur_po_line.line_location_id,
cur_po_line.po_distribution_id,
'RECEIVE',
'VENDOR',
cur_po_line.organization_code,
'PO',
rcv_headers_interface_s.currval,
'Y',
ln_org_id
FROM dual;
ln_parent_txn_id := rcv_transactions_interface_s.currval;
INSERT INTO rcv_transactions_interface
(
parent_interface_txn_id,
interface_transaction_id,
group_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
po_header_id,
po_line_id,
item_id,
quantity,
unit_of_measure,
po_line_location_id,
po_distribution_id,
auto_transact_code,
receipt_source_code,
to_organization_code,
source_document_code,
header_interface_id,
validation_flag,
org_id)
SELECT ln_parent_txn_id,
rcv_transactions_interface_s.nextval,
rcv_interface_groups_s.currval,
sysdate,
ln_user_id,
sysdate,
ln_user_id,
0,
'DELIVER',
SYSDATE,
'PENDING',
'BATCH',
'PENDING',
ln_po_header_id,
cur_po_line.po_line_id,
cur_po_line.item_id,
cur_po_line.quantity,
cur_po_line.unit_meas_lookup_code,
cur_po_line.line_location_id,
cur_po_line.po_distribution_id,
NULL,--'RECEIVE',
'VENDOR',
cur_po_line.organization_code,
'PO',
rcv_headers_interface_s.currval,
'Y',
ln_org_id
FROM dual;
dbms_output.put_line('po line: ' || cur_po_line.line_num || ' shipment: ' || cur_po_line.shipment_num ||
' has been inserted into roi.');
ELSE
dbms_output.put_line('po line ' || cur_po_line.line_num || ' is either closed, cancelled, received.');
END IF;
END LOOP;
dbms_output.put_line('*** ezroi complete - end ***');
COMMIT;
END;
Step 2: Submit the below concurrent program
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
1 Responses to “Script to create Purchase order Receipt using "Receiving Transaction Processor" (RCV_HEADERS_INTERFACE, RCV_TRANSACTIONS_INTERFACE)”
January 29, 2019 at 12:32 PM
Thank you So much , Your post are very very helpful , I am following up past 2013 Cheers!!!!
Post a Comment