Purchasing & Inventory Interfaces

Purchase Orders Receiving with Receiving Transaction Processes Open Interface (RVCTP)

The Receiving Transaction Processor processes pending or unprocessed receiving transactions. We can receive the Purchase Order either using the Expected Receipt form or by putting the record into the Receiving Open Interface (ROI). And then if we will submit the receiving transactions processor so the PO will be received.

records that 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

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 – draft package 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 =
AND 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 = <1111>
and group_id = <1111>

select *
from apps.rcv_transactions_interface
where created_by = <1111>
and group_id = <1111>

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

-- Check for the Error

select * from po_interface_errors
where batch_id = <1111>

SELECT pi.interface_type,

pi.column_name,

pi.error_message,

prti.po_header_id,

prti.po_line_id

FROM po.po_interface_errors pi,

po.rcv_transactions_interface prti

WHERE pi.interface_line_id = prti.interface_transaction_id

AND prti.processing_status_code = 'ERROR';

-- 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 = <1111>

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 = <1111>
AND batch_id = <1111>


-- 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 <1111>

select * from apps.rcv_lot_transactions
where lot_num in <1111>
select * from apps.mtl_material_transactions
where created_by = <1111>
and rcv_transaction_id in (select transaction_id from apps.rcv_transactions
where po_header_id = <1111>
and created_by = <1111>)

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 = <1111>
AND pl.org_id = <1111>
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

Resolution for pending transactions

1. Pending Receiving

If there is un processed purchase order receiving, can be processed with the below method:

Insert into rcv_transactions_interface, rcv_headers_interface

Submit: Receiving Transaction Processes

2. Pending Material

It can be processed thru mtl_transactions_interface, mtl_transactions_lots_interface and then submit Process Transaction Interface (INCTCM)

3. Pending shop floor move

It can be completed thru WIP_MOVE_TXN_INTERFACE to wip_move_transactions

Process Transaction Interface (INCTCM)

Process Transaction Interface (INCTCM) will submit the Inventory transaction worker (INCTCW) internally to process the MTL Interface Transactions & MTL LOTS interface.

The inventory interface manager (transaction manager - INCTCM) runs and validates the data and inserts the records into MTL_MATERIAL_TRANSACTIONS_TEMP table. When the transaction worker processes the temp data, the records are inserted into the transactions history table (MMT).

When the Cost Manager runs, it costs the transactions, and creates SLA events. Pl note in 11i, the cost manager used to create accounting as well.

When the Create Accounting (Cost Management) process runs, it creates the accounting. When the transfer to GL is run from SLA, it transfers all the accounting data to GL

Interface Tables: -

• mtl_transactions_interface
• mtl_transaction_lots_interface

MTL_TRANSACTIONS_INTERFACE

1. PROCESS_FLAG : Flag indicating whether transaction is ready to be processed by the Transaction Manager or Worker

'1' for ready
'2' for not ready
'3' transaction fail

if the transaction fails for some reason, the Transaction Worker sets
the value of PROCESS_FLAG to '3'

2. TRANSACTION_MODE :
Code that indicates whether the transaction is to be processed in immediate concurrent
processing mode (2) or background processing mode (3).

1: online
2. immediate processing mode
3: background processing mode

3. LOCK_FLAG :
Flag indicating whether the transaction is locked by the Transaction Manager or Workers

'1' : for locked,
'2' or NULL : for not locked);
this prevents two different Workers from processing the same transaction;
You should always specify '2'

Inventory Interface tables

• MTL_TRANSACTIONS_INTERFACE (transaction Information) • MTL_TRANSACTION_LOTS_INTERFACE (transaction lot numbers) • MTL_SERIAL_NUMBERS_INTERFACE(transaction serial numbers) • CST_COMP_SNAP_INTERFACE (Completion Cost Calculation Interface) à Process Transaction Interface (INCTCM) à It will call Transaction Worker (INCTCW) à MTL_MATERIAL_TRANSACTIONS • MTL_TRANSACTION_LOT_NUMBERS • MTL_UNIT_TRANSACTIONS • MTL_LOT_NUMBERS • MTL_SERIAL_NUMBERS • MTL_ONHAND_QUANTITIES • MTL_DEMAND • MTL_MATERIAL_TXN_ALLOCATIONS • RCV_TRANSACTIONS_INTERFACE • CST_ITEM_COSTS • CST_ITEM_COST_DETAILS

1. Transaction_interface_id will be the link for all the above three interface tables

2. Process_flag values 1=’YES’ , 2=’NO’, 3=’ERROR’

Open Items Interface

you can import items from any source into oracle inventory, when we run the item import it will create the items in master organization and we can assign it to multiple orgs. There are two interface tables for this.

1. MTL_SYSTEMS_ITEM_INTERFACE 2. MTL_INTERFACE_ERRORS 3.MTL_ITEM_REVISIONS_INTERFACE

4. MTL_ITEM_CATEGORIES_INTERFACE

PROCESS_FLAG can be 1 (pending), 2(Assign complete), 3(validation failed) 4 (validation success, import failed) 5 (import in process), 7 (import success)

è Item Import can be Insert/Update

1. MTL_SYSTEM_ITEMS_B

2. MTL_ITEM_CATEGORIES

Interface for tem on hand quantity & Lot & Serial Numbers

Interface Tables are :-
----------------------------------
mtl_transactions_interface
mtl_transaction_lots_interface
mtl_serial_numbers_interface


Submit the standard interface program:
-----------------------------------------------------
Process Transaction Interface
---------------------------------------
update mtl_transactions_interface
set process_flag = 1,
lock_flag = 2,
transaction_mode = 3,
validation_required = null,
error_code = null,
error_explanation = null
where process_flag in (1,3);
----------------------------------------------------------------