Records needs to be inserted into rcv_transactions_interface with processing_status_code and transaction_status_code as ‘PENDING’ and transaction_type of ‘RECEIVE’. and also inserted into rcv_shipment_headers which creates the shipment header.

Interface Tables: –

• rcv_headers_interface
• rcv_transactions_interface
• mtl_transaction_lots_interface

Error Table: –

• po_interface_errors

Base Tables:

• rcv_shipment_headers
• rcv_shipment_lines
• rcv_transactions
• mtl_lot_numbers
• mtl_material_transactions
• rcv_lot_transactions

R12 – Sample Procedure to Receive PO by inserting records into ROI
DECLARE

x_user_id NUMBER;
x_resp_id NUMBER;
x_appl_id NUMBER;
x_po_header_id NUMBER;
x_vendor_id NUMBER;
x_segment1 VARCHAR2 (20);
x_org_id NUMBER;
x_line_num NUMBER;
l_chr_lot_number VARCHAR2 (50);
l_chr_return_status VARCHAR2 (2000);
l_num_msg_count NUMBER;
l_chr_msg_data VARCHAR2 (50);
v_count NUMBER;

BEGIN

DBMS_OUTPUT.put_line (‘RCV Sample Insert Script Starts’);
DBMS_OUTPUT.put_line (‘**************************************’);

SELECT po_header_id, vendor_id, segment1, org_id
INTO x_po_header_id, x_vendor_id, x_segment1, x_org_id
FROM po_headers_all
WHERE segment1 = ‘380087’ — Enter The Po Number which needs to be received
AND org_id = 308 — Enter the org_id
AND approved_flag = ‘Y’
AND nvl(cancel_flag, ‘N’) = ‘N’;
SELECT DISTINCT
u.user_id,
to_char(a.responsibility_id) responsibility_id,
b.application_id
INTO
x_user_id, x_resp_id, x_appl_id
from
apps.fnd_user_resp_groups_direct a,
apps.fnd_responsibility_vl b,
apps.fnd_user u,
apps.fnd_application fa
where
a.user_id = u.user_id
and a.responsibility_id = b.responsibility_id
and a.responsibility_application_id = b.application_id
and sysdate between a.start_date and nvl(a.end_date,sysdate+1)
and fa.application_id (+) = b.application_id
and upper(u.user_name) = ‘A42485’ — Enter the User_name
and b.responsibility_name = ‘Inventory’; — Enter The Responsibility Name

DBMS_OUTPUT.put_line (‘Inserting the Record into Rcv_headers_interface’);
DBMS_OUTPUT.put_line (‘*********************************************’);

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, creation_date, created_by,
vendor_id,expected_receipt_date, validation_flag)
SELECT rcv_headers_interface_s.NEXTVAL, rcv_interface_groups_s.NEXTVAL,
‘PENDING’, ‘VENDOR’, ‘NEW’, SYSDATE, x_user_id, 0,SYSDATE, x_user_id,
x_vendor_id, SYSDATE, ‘Y’
FROM DUAL;
DECLARE

CURSOR po_line
IS
SELECT
pl.org_Id, pl.po_header_id, pl.item_id, pl.po_line_id, pl.line_num, pll.quantity,
pl.unit_meas_lookup_code, mp.organization_code,
pll.line_location_id, pll.closed_code, pll.quantity_received,
pll.cancel_flag, pll.shipment_num,
pda.destination_type_code,
pda.deliver_to_person_id,
pda.deliver_to_location_id,
pda.destination_subinventory,
pda.destination_organization_id
FROM po_lines_all pl, po_line_locations_all pll,mtl_parameters mp, apps.po_distributions_all pda
WHERE pl.po_header_id = x_po_header_id
AND pl.po_line_id = pll.po_line_id
AND pll.line_location_id = pda.line_location_id
AND pll.ship_to_organization_id = mp.organization_id;
BEGIN

FOR rec_det IN po_line LOOP

IF rec_det.closed_code IN (‘APPROVED’, ‘OPEN’)
AND rec_det.quantity_received <>
THEN

DBMS_OUTPUT.put_line (‘Inserting the Record into Rcv_Transactions_Interface’);
DBMS_OUTPUT.put_line (‘*********************************************’);
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, auto_transact_code,
receipt_source_code, to_organization_code,
source_document_code, document_num,
destination_type_code,deliver_to_person_id,
deliver_to_location_id,subinventory,
header_interface_id, validation_flag)
SELECT rcv_transactions_interface_s.NEXTVAL,
rcv_interface_groups_s.CURRVAL, SYSDATE, x_user_id,
SYSDATE, x_user_id, 0, ‘RECEIVE’, SYSDATE, ‘PENDING’,
‘BATCH’, ‘PENDING’, rec_det.po_header_id,rec_det.po_line_id,
rec_det.item_id, rec_det.quantity,
rec_det.unit_meas_lookup_code,
rec_det.line_location_id, ‘DELIVER’, ‘VENDOR’,
rec_det.organization_code, ‘PO’, x_segment1,
rec_det.destination_type_code, rec_det.deliver_to_person_id,
rec_det.deliver_to_location_id, rec_det.destination_subinventory,
rcv_headers_interface_s.CURRVAL, ‘Y’
FROM DUAL;
DBMS_OUTPUT.put_line (‘PO line:’ rec_det.line_num ‘ Shipment: ‘ rec_det.shipment_num ‘ has been inserted into ROI.’);
select count(*)
into v_count
from mtl_system_items
where inventory_item_id = rec_det.item_id
and lot_control_code = 2 — 2 – full_control, 1 – no control
and organization_id = rec_det.destination_organization_id;
IF v_count > 0 then

DBMS_OUTPUT.put_line (‘The Ordered Item is Lot Controlled’);
DBMS_OUTPUT.put_line (‘Generate the Lot Number for the Lot Controlled Item’);
BEGIN
— initialization required for R12
mo_global.set_policy_context (‘S’, rec_det.org_id);
mo_global.init (‘INV’);
— Initialization for Organization_id
inv_globals.set_org_id (rec_det.destination_organization_id);
— initialize environment
fnd_global.apps_initialize (user_id => x_user_id,
resp_id => x_resp_id,
resp_appl_id => x_appl_id);
DBMS_OUTPUT.put_line (‘Calling inv_lot_api_pub.auto_gen_lot API to Create Lot Numbers’);
DBMS_OUTPUT.put_line (‘*********************************************’);
l_chr_lot_number :=
inv_lot_api_pub.auto_gen_lot
(p_org_id => rec_det.destination_organization_id,
p_inventory_item_id => rec_det.item_id,
p_parent_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
p_api_version => 1.0,
p_init_msg_list => ‘F’,
p_commit => ‘T’,
p_validation_level => 100,
x_return_status => l_chr_return_status,
x_msg_count => l_num_msg_count,
x_msg_data => l_chr_msg_data);

IF l_chr_return_status = ‘S’ THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
DBMS_OUTPUT.put_line (‘Lot Number Created for the item is => ‘ l_chr_lot_number);
END;
DBMS_OUTPUT.put_line (‘Inserting the Record into mtl_transaction_lots_interface ‘);
DBMS_OUTPUT.put_line (‘*********************************************’);
INSERT INTO mtl_transaction_lots_interface
( transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
lot_number,
transaction_quantity,
primary_quantity,
serial_transaction_temp_id,
product_code,
product_transaction_id)
(select
mtl_material_transactions_s.nextval,–transaction_interface_id
sysdate, –last_update_date
x_user_id, –last_updated_by
sysdate, –creation_date
x_user_id, –created_by
-1, –last_update_login
l_chr_lot_number, –lot_number
rec_det.quantity, –transaction_quantity
rec_det.quantity, –primary_quantity
NULL, –serial_transaction_temp_id
‘RCV’, –product_code
rcv_transactions_interface_s.currval –product_transaction_id
from dual);
ELSE
DBMS_OUTPUT.put_line (‘The Ordered Item is Not Lot Controlled’);
DBMS_OUTPUT.put_line (‘********************************************’);
END IF;
ELSE
DBMS_OUTPUT.put_line ( ‘PO line ‘ rec_det.line_num’-‘ rec_det.shipment_num ‘ is either closed, cancelled, received.’);
DBMS_OUTPUT.put_line (‘*********************************************’);
END IF;
END LOOP;
DBMS_OUTPUT.put_line (‘RCV Sample Insert Script Ends’);
DBMS_OUTPUT.put_line (‘*****************************************’);
END;
COMMIT;
END;

— Cross Check the Records in the Interface Table

select * from apps.rcv_headers_interface
where created_by = 2083
and group_id = ***

select *
from apps.rcv_transactions_interface
where created_by = 2083
and group_id = ***
select * from apps.mtl_transaction_lots_interface
where created_by = 2083
and lot_number = ***
and product_transaction_id in
(select interface_transaction_id from apps.rcv_transactions_interface
where created_by = 2083 and group_id = ***)

— Check for the Error

select * from po_interface_errors
where batch_id = ***
— Reprocessing the records from the interface if the same errored out there.
UPDATE rcv_headers_interface
SET processing_request_id = NULL,
validation_flag = ‘Y’,
processing_status_code = ‘PENDING’
WHERE GROUP_ID = ***

UPDATE rcv_transactions_interface
SET request_id = NULL,
processing_request_id = NULL,
validation_flag = ‘Y’,
processing_status_code = ‘PENDING’,
transaction_status_code = ‘PENDING’,
processing_mode_code = ‘BATCH’
WHERE interface_transaction_id = ***
AND batch_id = ***

— Verification of the base tables Once the Receiving Transactions Processor is Completed

select * from apps.rcv_shipment_headers
where created_by = 2083

select * from apps.rcv_shipment_lines
where created_by = 2083
and po_header_id = 619
select * from apps.rcv_transactions
where po_header_id = 619
and created_by = 2083

select * from apps.mtl_lot_numbers
where lot_number in (‘A6631684’, ‘A6631685’, ‘A6631686’)
select * from apps.rcv_lot_transactions
where lot_num in (‘A6631684’, ‘A6631685’, ‘A6631686’)
select * from apps.mtl_material_transactions
where created_by = 2083
and rcv_transaction_id in (select transaction_id from apps.rcv_transactions
where po_header_id = 619
and created_by = 2083)

SELECT (SELECT segment1
FROM po_headers_all
WHERE po_header_id = pl.po_header_id
AND org_id = pl.org_id) po_number, pl.po_header_id,
pl.item_id, pl.po_line_id, pl.line_num, pll.shipment_num,
pll.quantity, pl.unit_meas_lookup_code, mp.organization_code, pll.line_location_id,
pll.closed_code, pll.quantity_received, pll.cancel_flag,
pll.shipment_num, pda.destination_type_code, pda.deliver_to_person_id,
pda.deliver_to_location_id, pda.destination_subinventory
FROM
apps.po_lines_all pl,
apps.po_line_locations_all pll,
apps.mtl_parameters mp,
apps.po_distributions_all pda
WHERE 1 = 1
AND pl.po_header_id = 619
AND pl.org_id = 308
AND pl.po_line_id = pll.po_line_id
AND pll.line_location_id = pda.line_location_id
AND pll.ship_to_organization_id = mp.organization_id
order by 1, 5, 6