Return to Vendor of Purchase Order Receipts Script: Oracle EBS R12
Return to Vendor is done in two steps: "Return to Receiving" and then "Return to Vendor".
Follow below steps to return the received goods to vendor.
Step:1 Run the following script to find the necessary information to be inserted in RCV_TRANSACTIONS_INTERFACE table.
SELECT rsh.receipt_num ,
ph.segment1 po_number,
rt.transaction_id ,
rt.transaction_type ,
rt.transaction_date ,
rt.quantity ,
rt.unit_of_measure ,
rt.shipment_header_id ,
rt.shipment_line_id ,
rt.source_document_code ,
rt.destination_type_code ,
rt.employee_id ,
rt.parent_transaction_id ,
rt.po_header_id ,
rt.po_line_id ,
pl.line_num ,
pl.item_id ,
pl.unit_price ,
rt.po_line_location_id ,
rt.po_distribution_id ,
rt.routing_header_id,
rt.routing_step_id ,
rt.deliver_to_person_id ,
rt.deliver_to_location_id ,
rt.vendor_id ,
rt.vendor_site_id ,
rt.organization_id ,
rt.subinventory ,
rt.locator_id ,
rt.location_id,
rsh.ship_to_org_id
FROM apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.po_headers_all ph,
apps.po_lines_all pl
WHERE rsh.receipt_num = '4298'
AND ph.segment1 = '6026'
AND ph.po_header_id = pl.po_header_id
AND rt.po_header_id = ph.po_header_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND rt.po_line_id =pl.po_line_id
AND rt.destination_type_code='INVENTORY'
AND rt.transaction_type='DELIVER';
Step:2 Return from Deliver to Receiving i.e Return to Receiving
INSERT INTO apps.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,
quantity,
unit_of_measure,
item_id,
employee_id,
shipment_header_id,
shipment_line_id,
receipt_source_code,
vendor_id,
from_organization_id,
from_subinventory,
from_locator_id,
source_document_code,
parent_transaction_id,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
destination_type_code,
deliver_to_person_id,
location_id,
deliver_to_location_id,
validation_flag
)
VALUES
(apps.rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
apps.rcv_interface_groups_s.nextval, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
1318, --LAST_UPDATE_BY
SYSDATE, --CREATION_DATE
1318, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RETURN TO RECEIVING', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
25, --QUANTITY
'METRICTON', --UNIT_OF_MEASURE
208955, --ITEM_ID
25, --EMPLOYEE_ID
5048067, --SHIPMENT_HEADER_ID
5035611, --SHIPMENT_LINE_ID
'VENDOR', --RECEIPT_SOURCE_CODE
557, --VENDOR_ID
204, --FROM_ORGANIZATION_ID
'Stores', --FROM_SUBINVENTORY
null, --FROM_LOCATOR_ID
'PO', --SOURCE_DOCUMENT_CODE
5090687, --TRANSACTION_ID
165864, --PO_HEADER_ID
232886, --PO_LINE_ID
323424, --PO_LINE_LOCATION_ID
329884, --PO_DISTRIBUTION_ID
'INVENTORY', --DESTINATION_TYPE_CODE
null, --DELIVER_TO_PERSON_ID
NULL, --LOCATION_ID
null, --DELIVER_TO_LOCATION_ID
'Y' --Validation_flag
);
commit;
Step:3 Submit the Receiving Transaction Processor concurrent program
SET serveroutput ON
DECLARE
v_request_id NUMBER;
BEGIN
apps.mo_global.init ('PO');
apps.mo_global.set_policy_context ('S',204);
apps.fnd_global.apps_initialize ( user_id => 1318, resp_id => 50578, resp_appl_id => 201 );
--------CALLING STANDARD RECEIVING TRANSACTION PROCESSOR ---------------------------------
v_request_id := apps.fnd_request.submit_request ( application => 'PO',
PROGRAM => 'RVCTP',
argument1 => 'BATCH',
argument2 => apps.rcv_interface_groups_s.currval,
argument3 => 204);
commit;
dbms_output.put_line('Request Id'||v_request_id);
END;
Step:4 Run the script to check data is inserted in proper way in rcv_transactions.
SELECT * FROM apps.rcv_transactions WHERE po_header_id=58641;
Step:5 Once the goods are transferred to receiving destination return to vendor.
INSERT INTO apps.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,
quantity,
unit_of_measure,
item_id,
employee_id,
shipment_header_id,
shipment_line_id,
receipt_source_code,
vendor_id,
from_organization_id,
from_subinventory,
from_locator_id,
source_document_code,
parent_transaction_id,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
destination_type_code,
deliver_to_person_id,
location_id,
deliver_to_location_id,
validation_flag
)
values
(apps.rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
apps.rcv_interface_groups_s.nextval, --GROUP_ID
sysdate, --LAST_UPDATE_DATE
1318, --LAST_UPDATE_BY
sysdate, --CREATION_DATE
1318, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RETURN TO VENDOR', --TRANSACTION_TYPE
sysdate, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
25, --QUANTITY
'METRICTON', --UNIT_OF_MEASURE
208955, --ITEM_ID
25, --EMPLOYEE_ID
5048067, --SHIPMENT_HEADER_ID
5035611, --SHIPMENT_LINE_ID
'VENDOR', --RECEIPT_SOURCE_CODE
557, --VENDOR_ID
204, --FROM_ORGANIZATION_ID
'Stores', --FROM_SUBINVENTORY
null, --FROM_LOCATOR_ID
'PO', --SOURCE_DOCUMENT_CODE
5090686, --PARENT_TRANSACTION_ID
165864, --PO_HEADER_ID
232886, --PO_LINE_ID
323424, --PO_LINE_LOCATION_ID
329884, --PO_DISTRIBUTION_ID
'RECEIVING', --DESTINATION_TYPE_CODE
null, --DELIVER_TO_PERSON_ID
null, --LOCATION_ID
null, --DELIVER_TO_LOCATION_ID
'Y' --Validation_flag
);
commit;
Step:6 Submit the Receiving Transaction Processor concurrent program
SET serveroutput ON
DECLARE
v_request_id NUMBER;
BEGIN
apps.mo_global.init ('PO');
apps.mo_global.set_policy_context ('S',204);
apps.fnd_global.apps_initialize ( user_id => 1318, resp_id => 50578, resp_appl_id => 201 );
--------CALLING STANDARD RECEIVING TRANSACTION PROCESSOR ---------------------------------
v_request_id := apps.fnd_request.submit_request ( application => 'PO',
PROGRAM => 'RVCTP',
argument1 => 'BATCH',
argument2 => apps.rcv_interface_groups_s.currval,
argument3 => 204);
commit;
dbms_output.put_line('Request Id'||v_request_id);
END;
Return to Vendor is done in two steps: "Return to Receiving" and then "Return to Vendor".
Follow below steps to return the received goods to vendor.
Step:1 Run the following script to find the necessary information to be inserted in RCV_TRANSACTIONS_INTERFACE table.
SELECT rsh.receipt_num ,
ph.segment1 po_number,
rt.transaction_id ,
rt.transaction_type ,
rt.transaction_date ,
rt.quantity ,
rt.unit_of_measure ,
rt.shipment_header_id ,
rt.shipment_line_id ,
rt.source_document_code ,
rt.destination_type_code ,
rt.employee_id ,
rt.parent_transaction_id ,
rt.po_header_id ,
rt.po_line_id ,
pl.line_num ,
pl.item_id ,
pl.unit_price ,
rt.po_line_location_id ,
rt.po_distribution_id ,
rt.routing_header_id,
rt.routing_step_id ,
rt.deliver_to_person_id ,
rt.deliver_to_location_id ,
rt.vendor_id ,
rt.vendor_site_id ,
rt.organization_id ,
rt.subinventory ,
rt.locator_id ,
rt.location_id,
rsh.ship_to_org_id
FROM apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.po_headers_all ph,
apps.po_lines_all pl
WHERE rsh.receipt_num = '4298'
AND ph.segment1 = '6026'
AND ph.po_header_id = pl.po_header_id
AND rt.po_header_id = ph.po_header_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND rt.po_line_id =pl.po_line_id
AND rt.destination_type_code='INVENTORY'
AND rt.transaction_type='DELIVER';
Step:2 Return from Deliver to Receiving i.e Return to Receiving
INSERT INTO apps.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,
quantity,
unit_of_measure,
item_id,
employee_id,
shipment_header_id,
shipment_line_id,
receipt_source_code,
vendor_id,
from_organization_id,
from_subinventory,
from_locator_id,
source_document_code,
parent_transaction_id,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
destination_type_code,
deliver_to_person_id,
location_id,
deliver_to_location_id,
validation_flag
)
VALUES
(apps.rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
apps.rcv_interface_groups_s.nextval, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
1318, --LAST_UPDATE_BY
SYSDATE, --CREATION_DATE
1318, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RETURN TO RECEIVING', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
25, --QUANTITY
'METRICTON', --UNIT_OF_MEASURE
208955, --ITEM_ID
25, --EMPLOYEE_ID
5048067, --SHIPMENT_HEADER_ID
5035611, --SHIPMENT_LINE_ID
'VENDOR', --RECEIPT_SOURCE_CODE
557, --VENDOR_ID
204, --FROM_ORGANIZATION_ID
'Stores', --FROM_SUBINVENTORY
null, --FROM_LOCATOR_ID
'PO', --SOURCE_DOCUMENT_CODE
5090687, --TRANSACTION_ID
165864, --PO_HEADER_ID
232886, --PO_LINE_ID
323424, --PO_LINE_LOCATION_ID
329884, --PO_DISTRIBUTION_ID
'INVENTORY', --DESTINATION_TYPE_CODE
null, --DELIVER_TO_PERSON_ID
NULL, --LOCATION_ID
null, --DELIVER_TO_LOCATION_ID
'Y' --Validation_flag
);
commit;
Step:3 Submit the Receiving Transaction Processor concurrent program
SET serveroutput ON
DECLARE
v_request_id NUMBER;
BEGIN
apps.mo_global.init ('PO');
apps.mo_global.set_policy_context ('S',204);
apps.fnd_global.apps_initialize ( user_id => 1318, resp_id => 50578, resp_appl_id => 201 );
--------CALLING STANDARD RECEIVING TRANSACTION PROCESSOR ---------------------------------
v_request_id := apps.fnd_request.submit_request ( application => 'PO',
PROGRAM => 'RVCTP',
argument1 => 'BATCH',
argument2 => apps.rcv_interface_groups_s.currval,
argument3 => 204);
commit;
dbms_output.put_line('Request Id'||v_request_id);
END;
Step:4 Run the script to check data is inserted in proper way in rcv_transactions.
SELECT * FROM apps.rcv_transactions WHERE po_header_id=58641;
Step:5 Once the goods are transferred to receiving destination return to vendor.
INSERT INTO apps.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,
quantity,
unit_of_measure,
item_id,
employee_id,
shipment_header_id,
shipment_line_id,
receipt_source_code,
vendor_id,
from_organization_id,
from_subinventory,
from_locator_id,
source_document_code,
parent_transaction_id,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
destination_type_code,
deliver_to_person_id,
location_id,
deliver_to_location_id,
validation_flag
)
values
(apps.rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
apps.rcv_interface_groups_s.nextval, --GROUP_ID
sysdate, --LAST_UPDATE_DATE
1318, --LAST_UPDATE_BY
sysdate, --CREATION_DATE
1318, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RETURN TO VENDOR', --TRANSACTION_TYPE
sysdate, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
25, --QUANTITY
'METRICTON', --UNIT_OF_MEASURE
208955, --ITEM_ID
25, --EMPLOYEE_ID
5048067, --SHIPMENT_HEADER_ID
5035611, --SHIPMENT_LINE_ID
'VENDOR', --RECEIPT_SOURCE_CODE
557, --VENDOR_ID
204, --FROM_ORGANIZATION_ID
'Stores', --FROM_SUBINVENTORY
null, --FROM_LOCATOR_ID
'PO', --SOURCE_DOCUMENT_CODE
5090686, --PARENT_TRANSACTION_ID
165864, --PO_HEADER_ID
232886, --PO_LINE_ID
323424, --PO_LINE_LOCATION_ID
329884, --PO_DISTRIBUTION_ID
'RECEIVING', --DESTINATION_TYPE_CODE
null, --DELIVER_TO_PERSON_ID
null, --LOCATION_ID
null, --DELIVER_TO_LOCATION_ID
'Y' --Validation_flag
);
commit;
Step:6 Submit the Receiving Transaction Processor concurrent program
SET serveroutput ON
DECLARE
v_request_id NUMBER;
BEGIN
apps.mo_global.init ('PO');
apps.mo_global.set_policy_context ('S',204);
apps.fnd_global.apps_initialize ( user_id => 1318, resp_id => 50578, resp_appl_id => 201 );
--------CALLING STANDARD RECEIVING TRANSACTION PROCESSOR ---------------------------------
v_request_id := apps.fnd_request.submit_request ( application => 'PO',
PROGRAM => 'RVCTP',
argument1 => 'BATCH',
argument2 => apps.rcv_interface_groups_s.currval,
argument3 => 204);
commit;
dbms_output.put_line('Request Id'||v_request_id);
END;
Good Blog, Thanks for sharing this informative article.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad
Thank you for sharing such a nice and interesting blog and really very helpful article
ReplyDeleteOracle Fusion Financials Online Training