ROI - How To Perform. Receipt On Internal Requisition / Internal Sales Order Via Receiving Open Interface? [ID 365200.1]

ROI - How To Perform. Receipt On Internal Requisition / Internal Sales Order Via Receiving Open Interface? [ID 365200.1] (come from metalink)

Applies to:
Oracle Inventory Management - Version: 11.5.1 to 12.1.3 - Release: 11.5 to 12.1
Information in this document applies to any platform.
EXECUTABLE:RVCTP - Receiving Transaction Processor
Goal

How to enter a Receipt (Receive/Deliver Transactions) for an Internal Requisition / Internal Sales Order via the Receiving Open Interface?

New functionality of 11.5.10/11i.SCM_PF.J/11i.PRC_PF.J (and higher) is ROI supports for Internal Order
. RECEIVE transaction
. DELIVER transaction

Solution
Following SAMPLE script. is intended to enter a Receive and Deliver to Inventory Transaction with Inventory destination type for an Internal Sales Order between 2 inventory organizations with Direct Receipt Routing through the Receiving Open Interface (ROI)
(ie Perform. the RECEIVE and DELIVER transaction at the same time)

The script. will load records into the tables

•RCV_HEADERS_INTERFACE,
•RCV_TRANSACTIONS_INTERFACE,
If the item is a lot or serial controlled standard item, records must also be inserted into
MTL_TRANSACTION_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE tables.

SETUP
0) In release 11.5.10 Ensure to apply the patches listed in Note 367396.1- Recommended Patches for Enhanced Receiving Validation after upgrade to or install of 11.5.10 (or 11i.SCM_PF.J)
For Release 12 see Note 423541.1 

1) Inventory Setup: Organizations/Shipping Networks
from Sending Organization Org1 D2 Miami to Destination Organization Org2 M2 Boston
Transfer Type=Intransit
Receipt Routing=Direct

2) Miscellaneous Account receipt in org D2 On subinventory FGI for a quantity =500
has been entered on item AS54888
Item AS54888 is a standard item - Not a lot and serial controlled Item

SQL> Select mp.organization_id Org_Id, mp.organization_code Org_Code,
msi.inventory_item_id, msi.segment1, decode(to_char(msi.lot_control_code),
'2','Y',
'1','N') LOT_CONTROL,
decode(to_char(msi.serial_number_control_code),
'1','None',
'2','Predefined',
'5','Dynamic at INV receipt',
'6','Dynamic at SO issue') SERIAL_CONTROL
from mtl_system_items_b msi,mtl_parameters mp
where msi.segment1 like 'item' and msi.organization_id=mp.organization_id;
It returns LOT_CONTROL='N' and SERIAL_CONTROL='None'
inventory_item_id=149 for item=AS54888
3) In Purchasing responsibility
. Enter and approve Internal requisition number 5709
One line for inventory item AS54888 quantity=10 UOM=Each
with
Destination rganization=M2 Boston
Destination location= M2- Boston
Source=Inventory
Source rganization=D2 Miami
Source Subinventory=FGI
. Run the Create Internal Orders program to interface the Internal item to OM

4) In Order Management Responsibility, process the Internal Sales Order:
. Run Order Import concurrent program. (OEOIMP module: Order Import)
. Orders, Returns/Order Organizer
For Internal Requisition 5709 Internal Sales Order number 56753 has been created and booked
. Shipping/Release Sales Orders
Release the sales order 56753
. Shipping Transactions
1 Line related to delivery = 64883
Line status = Staged/Pick Confirmed
. Shipping/Ship Confirm Deliveries SRS
Run the program for Delivery = 64883
. Ensure to run WSHINTERFACE module: Interface Trip Stop
. Shipping / Transactions - For ISO Sales Order 56753
Line Status=Interfaced
Delivery status=Closed

5) In Purchasing Responsibility
Receiving/Receipts
In org M2 Boston
Find expected receipts
Source Type =Internal
Requisition = 5709
- Ensure 1 line is available to be receipted
Order type= Internal Order
Order =64883
(= Shipment Number = Delivery name of Internal Sales order 56753)

6) Run the following scripts so to find the necessary information to insert into the RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE table
Receipt of internal Requisition Number 5709

6.1) SQL> Select rh.*
from po_requisition_headers_all rh
where segment1 like 'InternaReqNumber';
REQUISITION_HEADER_ID=56678
SEGMENT1=5709
TYPE_LOOKUP_CODE=INTERNAL
ORG_ID=204

6.2) SQL> Select rl.*
from po_requisition_lines_all rl,
po_requisition_headers_all rh
where rh.requisition_header_id = rl.requisition_header_id
and rh.requisition_header_id =ReqHeaderId;
REQUISITION_LINE_ID=60678
REQUISITION_HEADER_ID=56678
CATEGORY_ID=91
UNIT_MEAS_LOOKUP_CODE=Each
QUANTITY=10
DELIVER_TO_LOCATION_ID=209
TO_PERSON_ID=13536
SOURCE_TYPE_CODE=INVENTORY
ITEM_ID=149
QUANTITY_DELIVERED=0
SOURCE_ORGANIZATION_ID=210
DESTINATION_TYPE_CODE=INVENTORY
DESTINATION_ORGANIZATION_ID=209
ORG_ID=204

6.3) SQL> Select rsh.*
from rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_requisition_lines_all rl
where rsl.shipment_header_id = rsh.shipment_header_id
and rsl.requisition_line_id = rl.requisition_line_id
and rl.requisition_line_id =RequisitionLineId;
SHIPMENT_HEADER_ID=59417
RECEIPT_SOURCE_CODE=INTERNAL ORDER
ORGANIZATION_ID=210
SHIPMENT_NUM=64883
SHIP_TO_LOCATION_ID=209
SHIP_TO_ORG_ID=209

6.4) SQL> Select rsl.*
from rcv_shipment_lines rsl,
po_requisition_lines_all rl
where rsl.requisition_line_id = rl.requisition_line_id
and rl.requisition_line_id =RequisitionLineId;
SHIPMENT_LINE_ID=66367
SHIPMENT_HEADER_ID=59417
LINE_NUM=1
CATEGORY_ID=91
QUANTITY_SHIPPED=10
QUANTITY_RECEIVED=0
UNIT_OF_MEASURE=Each
ITEM_DESCRIPTION=Sentinel Standard Desktop
ITEM_ID=149
SHIPMENT_LINE_STATUS_CODE=EXPECTED
SOURCE_DOCUMENT_CODE=REQ
REQUISITION_LINE_ID=60678
REQ_DISTRIBUTION_ID=59631
ROUTING_HEADER_ID=3
FROM_ORGANIZATION_ID=210
DELIVER_TO_PERSON_ID=13536
DESTINATION_TYPE_CODE=INVENTORY
TO_ORGANIZATION_ID=209
DELIVER_TO_LOCATION_ID=209
SHIP_TO_LOCATION_ID=209
OE_ORDER_HEADER_ID=null
OE_ORDER_LINE_ID=null
AMOUNT_RECEIVED=0

6.5) SQL> Select ms.*
from mtl_supply ms,
po_requisition_lines_all rl
where ms.req_line_id = rl.requisition_line_id
and rl.requisition_line_id =RequisitionLineId;
SUPPLY_TYPE_CODE=SHIPMENT
SUPPLY_SOURCE_ID=66367
REQ_HEADER_ID=56678
REQ_LINE_ID=60678
SHIPMENT_HEADER_ID=59417
SHIPMENT_LINE_ID=66367
ITEM_ID=149
QUANTITY=10
UNIT_OF_MEASURE=Each
NEED_BY_DATE=05-APR-06
EXPECTED_DELIVERY_DATE=07-APR-06
DESTINATION_TYPE_CODE=INVENTORY
FROM_ORGANIZATION_ID=210
TO_ORGANIZATION_ID=209
6.6) You may also run the OMSE11i.SQL release 11i script. for the Sales Order Note 133464.1
In order to find the sales order number please do the following in Purchasing responsibility:
1. Open Requisition Summary
2. Enter Requisition and Line then Find
3. View Sales Orders

If only the internal requisition number is known and not the sales order number,
following query can be used to find the sales order number:

Select ORDER_NUMBER, ORIG_SYS_DOCUMENT_REF, SOURCE_DOCUMENT_ID
from OE_ORDER_HEADERS_ALL
where ORIG_SYS_DOCUMENT_REF like '&InternalReqNumber';

(SOURCE_DOCUMENT_ID = Requisition Header Id)
If only the shipment number is known following query can be used to retrieve internal requisition number from intransit shipment number:

Select prh.segment1 Internal_req_number,
prh.ORG_ID Operating_Unit,
prh.REQUISITION_HEADER_ID ,
rsh.SHIPMENT_NUM shipment_num,
rsl.SHIPMENT_LINE_ID shipment_line_id,
rsl.TO_ORGANIZATION_ID organization_id,
prl.REQUISITION_LINE_ID
FROM
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_requisition_headers_all prh,
po_requisition_lines_all prl
WHERE rsh.shipment_num like '&Shipment'
and rsh.receipt_source_code ='INTERNAL ORDER'
and rsh.shipment_header_id=rsl.shipment_header_id
and rsl.requisition_line_id = prl.requisition_line_id
and prl.requisition_header_id = prh.requisition_header_id

- RECEIVE/DELIVER to INVENTORY Transaction for Internal REQUISITION / Sales ORDER Example -
1) Insert via ROI a Direct DELIVER Receipt for Shipment Number '64883' (SHIPMENT_HEADER_ID=59417)
of 2 items in destination organization

Insert
. 1 record in RCV_HEADERS_INTERFACE table for the receipt header information
with SHIPMENT_NUM= '64883'
RECEIPT_SOURCE_CODE='INTERNAL ORDER'
and VALIDATION_FLAG='Y'

. 1 record in RCV_TRANSACTIONS_INTERFACE table for SHIPMENT_LINE_ID=66367
with TRANSACTION_TYPE='RECEIVE' and AUTO_TRANSACT_CODE='DELIVER'
for a quantity =2 indicating
DESTINATION_TYPE_CODE='INVENTORY'
RECEIPT_SOURCE_CODE='INTERNAL ORDER' and SOURCE_DOCUMENT_CODE='REQ'
VALIDATION_FLAG='Y'
and specifying
REQUISITION_LINE_ID= 60678
REQ_DISTRIBUTION_ID= 59631

SQL> INSERT INTO RCV_HEADERS_INTERFACE
(HEADER_INTERFACE_ID,
GROUP_ID,
PROCESSING_STATUS_CODE,
RECEIPT_SOURCE_CODE,
TRANSACTION_TYPE,
AUTO_TRANSACT_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
SHIPMENT_NUM,
SHIP_TO_ORGANIZATION_ID,
EXPECTED_RECEIPT_DATE,
SHIPPED_DATE,
EMPLOYEE_ID,
VALIDATION_FLAG
)
VALUES
(rcv_headers_interface_s.nextval , --HEADER_INTERFACE_ID
rcv_interface_groups_s.nextval, --GROUP_ID
'PENDING', --PROCESSING_STATUS_CODE
'INTERNAL ORDER', --RECEIPT_SOURCE_CODE
'NEW', --TRANSACTION_TYPE
'DELIVER', --AUTO_TRANSACT_CODE
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATE_BY
0, --LAST_UPDATE_LOGIN
SYSDATE, --CREATION_DATE
0, --CREATED_BY
'64883', --SHIPMENT_NUM
209, --SHIP_TO_ORGANIZATION_ID
SYSDATE+1, --EXPECTED_RECEIPT_DATE
SYSDATE, --SHIPPED_DATE
13536, --EMPLOYEE_ID
'Y' --VALIDATION_FLAG
);

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,
QUANTITY,
UNIT_OF_MEASURE,
INTERFACE_SOURCE_CODE,
ITEM_ID,
EMPLOYEE_ID,
AUTO_TRANSACT_CODE,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
SHIP_TO_LOCATION_ID,
RECEIPT_SOURCE_CODE,
TO_ORGANIZATION_ID,
SOURCE_DOCUMENT_CODE,
REQUISITION_LINE_ID,
REQ_DISTRIBUTION_ID,
DESTINATION_TYPE_CODE,
DELIVER_TO_PERSON_ID,
LOCATION_ID,
DELIVER_TO_LOCATION_ID,
SUBINVENTORY,
SHIPMENT_NUM,
EXPECTED_RECEIPT_DATE,
SHIPPED_DATE,
HEADER_INTERFACE_ID,
VALIDATION_FLAG
)
VALUES
( rcv_transactions_interface_s.nextval, -- INTERFACE_TRANSACTION_ID
rcv_interface_groups_s.currval, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RECEIVE', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
2, --QUANTITY
'Each', --UNIT_OF_MEASURE
'RCV', --INTERFACE_SOURCE_CODE
149, --ITEM_ID
13536, --EMPLOYEE_ID
'DELIVER', --AUTO_TRANSACT_CODE
59417, --SHIPMENT_HEADER_ID
66367, --SHIPMENT_LINE_ID
209, --SHIP_TO_LOCATION_ID
'INTERNAL ORDER', --RECEIPT_SOURCE_CODE
209, --TO_ORGANIZATION_ID
'REQ', --SOURCE_DOCUMENT_CODE
60678, --REQUISITION_LINE_ID
59631, --REQ_DISTRIBUTION_ID
'INVENTORY', --DESTINATION_TYPE_CODE
13536, --DELIVER_TO_PERSON_ID
209, --LOCATION_ID
209, --DELIVER_TO_LOCATION_ID
'Stores', --SUBINVENTORY
'64883', --SHIPMENT_NUM
SYSDATE+1, --EXPECTED_RECEIPT_DATE,
SYSDATE, --SHIPPED_DATE
rcv_headers_interface_s.currval, --HEADER_INTERFACE_ID
'Y' --VALIDATION_FLAG
);

Commit;
Note: On R12 instance, RHI.org_id or RTI.org_id should also be populated.

2) Run the following scripts to check data have been correctly inserted

SQL> Select * from RCV_HEADERS_INTERFACE where SHIPMENT_NUM like 'Shipment_Num';
GROUP_ID=21309
HEADER_INTERFACE_ID=40152
RECEIPT_SOURCE_CODE=INTERNAL ORDER
SHIPMENT_NUM=64883
VALIDATION_FLAG=Y

SQL> Select * from RCV_TRANSACTIONS_INTERFACE where SHIPMENT_NUM like 'Shipment_Num';
INTERFACE_TRANSACTION_ID=67448
GROUP_ID=21309
HEADER_INTERFACE_ID=40152
TRANSACTION_TYPE=RECEIVE
AUTO_TRANSACT_CODE=DELIVER
RECEIPT_SOURCE_CODE=INTERNAL ORDER
TO_ORGANIZATION_ID=209
SOURCE_DOCUMENT_CODE=REQ
REQUISITION_LINE_ID=60678
REQ_DISTRIBUTION_ID=59631
SHIPMENT_NUM=64883
VALIDATION_FLAG=Y
3) In Purchasing Responsibility, Change to receiving organization M2 and
run the Receiving Transaction Processor for the given group_id used in RHI and RTI (GROUP_ID=21309)

4) Navigate to Receiving / Receiving Transactions Summary form
For Internal Requisition 5709, Receipt Number 5020 on Shipment Number 64883 has
Receive and Deliver transactions.

5) Check how the following application tables have been populated/updated

SQL> Select * from RCV_HEADERS_INTERFACE where SHIPMENT_NUM like 'Shipment_Num';
GROUP_ID=21309
HEADER_INTERFACE_ID=40152
PROCESSING_STATUS_CODE=SUCCESS
RECEIPT_SOURCE_CODE=INTERNAL ORDER
SHIPMENT_NUM=64883

SQL> Select * from RCV_TRANSACTIONS where REQUISITION_LINE_ID=60678
It returns 2 records
For TRANSACTION_TYPE=RECEIVE
TRANSACTION_ID=110699
DESTINATION_TYPE_CODE=RECEIVING
PARENT_TRANSACTION_ID=-1
QUANTITY=2

For TRANSACTION_TYPE=DELIVER
TRANSACTION_ID=110700
DESTINATION_TYPE_CODE=INVENTORY
PARENT_TRANSACTION_ID=110699
QUANTITY=2

NESTED TABLE INITIALIZATION - Work in Progress

CREATE OR REPLACE TYPE APPS.wwt_xx_wms_serial_number_type AS TABLE OF VARCHAR2 (30)

CREATE OR REPLACE TYPE APPS.wwt_xxwms_item_reserve_rectype AS OBJECT (

   doc_name             VARCHAR2 (100),

   doc_source_type_id   NUMBER,

   doc_header_id        NUMBER,

   doc_line_id          NUMBER,

   inventory_item_id    NUMBER,

   uom                  VARCHAR2 (10),

   quantity             NUMBER,

   serial_number        wwt_xx_wms_serial_number_type

)

CREATE OR REPLACE TYPE APPS.wwt_xxwms_item_reserve_tabtype AS TABLE OF apps.wwt_xxwms_item_reserve_rectype

DECLARE

   l_return_status         VARCHAR2 (10);
   l_return_message        VARCHAR2 (32000);
   l_item_reservation_rec  wwt_xxwms_item_reserve_rectype := wwt_xxwms_item_reserve_rectype (null,null,null,null,null,null,null,wwt_xx_wms_serial_number_type() );
   l_item_reservation_tbl  wwt_xxwms_item_reserve_tabtype := wwt_xxwms_item_reserve_tabtype();

BEGIN

    l_item_reservation_rec  := wwt_xxwms_item_reserve_rectype (null,null,null,null,null,null,null,wwt_xx_wms_serial_number_type() );
    l_item_reservation_rec.doc_line_id := 24550833;
    l_item_reservation_rec.serial_number.EXTEND;
    l_item_reservation_rec.serial_number(1) := 'X531316060';
    l_item_reservation_rec.serial_number.EXTEND;
    l_item_reservation_rec.serial_number(2) := 'X531316061';  

    l_item_reservation_tbl.EXTEND;
    l_item_reservation_tbl(1) := l_item_reservation_rec;  


    l_item_reservation_rec  := wwt_xxwms_item_reserve_rectype (null,null,null,null,null,null,null,wwt_xx_wms_serial_number_type() );
    l_item_reservation_rec.doc_line_id := 24550834;
    l_item_reservation_rec.serial_number.EXTEND;
    l_item_reservation_rec.serial_number(1) := 'X531316062';
    l_item_reservation_rec.serial_number.EXTEND;
    l_item_reservation_rec.serial_number(2) := 'X531316063';  
    l_item_reservation_rec.serial_number.EXTEND;
    l_item_reservation_rec.serial_number(3) := 'X531316064';  
    l_item_reservation_rec.serial_number.EXTEND;
    l_item_reservation_rec.serial_number(4) := 'X531316065';  

    l_item_reservation_tbl.EXTEND;
    l_item_reservation_tbl(2) := l_item_reservation_rec;  



    FOR idx IN l_item_reservation_tbl.FIRST .. l_item_reservation_tbl.LAST LOOP
 
       DBMS_OUTPUT.put_line ( idx||'Doc line id '||l_item_reservation_tbl(idx).doc_line_id);  

       FOR srl_idx IN l_item_reservation_tbl(idx).serial_number.FIRST .. l_item_reservation_tbl(idx).serial_number.LAST   LOOP
     
       DBMS_OUTPUT.put_line (srl_idx|| 'serial number '||l_item_reservation_tbl(idx).serial_number(srl_idx));  
     
       END LOOP;
       
    END LOOP;


   DBMS_OUTPUT.put_line ('l_return_status => ' || l_return_status);
   DBMS_OUTPUT.put_line ('ln_msg_count => ' || l_return_message);


END;