Query to get Business Group, Legal Entity Name, Operating Unit Details

SELECT distinct hrl.country, hroutl_bg.NAME bg, hroutl_bg.organization_id,
       lep.legal_entity_id, lep.NAME legal_entity,
       hroutl_ou.NAME ou_name, hroutl_ou.organization_id org_id,
       hrl.location_id,
       hrl.location_code,
       glev.FLEX_SEGMENT_VALUE
  FROM xle_entity_profiles lep,
       xle_registrations reg,
       hr_locations_all hrl,
       hz_parties hzp,
       fnd_territories_vl ter,
       hr_operating_units hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units gloperatingunitseo,
       gl_legal_entities_bsvs glev
 WHERE lep.transacting_entity_flag = 'Y'
   AND lep.party_id = hzp.party_id
   AND lep.legal_entity_id = reg.source_id
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id = reg.location_id
   AND reg.identifying_flag = 'Y'
   AND ter.territory_code = hrl.country
   AND lep.legal_entity_id = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id = hro.business_group_id
   AND hroutl_ou.organization_id = hro.organization_id
   AND glev.legal_entity_id = lep.legal_entity_id

ROI: Which Interface Tables are Used by ROI to Process Serial and Lot Controlled Items

Goal
 Which interface tables are used to populate Serial and Lot information for Receiving Open Interface (ROI)?

Executable:RVCTP - Receiving Transaction Processor

Solution

In addition to rcv_headers_interface and rcv_transactions_interface, the mtl_transaction_lots_interface and mtl_serial_numbers_interface tables must be populated for the Receiving Open Interface (ROI) to process Delivery, Correction and Return of Serial and Lot Controlled Items.

Significant software changes were made in 11.5.10/11i.SCM_PF.J/11i.PRC_PF.J (and higher) to accommodate new ROI functionality.  Due to dependencies, the new ROI functionality will not be backported.

Example:
1) If Item is Serial Controlled only:


INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE (
TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID)  --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'sr000016',
'sr000016',
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL); 


2) If Item is Lot Controlled only:

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,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID) --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'lt2002',
1,
1,
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL); 


3) If Item is both Serial and Lot Controlled:


INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE (
TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID)  --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'sr000016',
'sr000016',
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL); 

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, --mtl_serial_numbers_interface.transaction_interface_id
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID) --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'lt2002',
1,
1,
MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL);  --rcv_transactions_interface.interface_transaction_id



When transacting Items that are both Lot and Serial Controlled, if Quantity is greater than 1, populate mtl_serial_numbers_interface and mtl_transaction_lots_interface as follows:
1.  one record in mtl_serial_numbers_interface table for each Serial Number (or Serial Number range)
2.  for each of the mtl_serial_numbers_interface records, populate a corresponding record in mtl_transaction_lots_interface with the Lot Number associated with each mtl_serial_numbers_interface record (mtl_transaction_lots_interface.serial_transaction_temp_id=mtl_serial_numbers_interface.transaction_interface_id)

11.5.10 / R12 ROI How to Receive Intransit Shipment (Inter-org transfer) for Lot / Serial Controlled Items via Receiving Open Interface

Goal
 New functionality of 11.5.10/11i.SCM_PF.J/11i.PRC_PF.J (and higher)  supports for Inter-Org Transfers:
         . RECEIVE transaction
         . DELIVER to Inventory Transaction

How to enter a Receipt (Receive/Deliver Transactions) for Inter-Organization Shipments for Lot and Serial Controlled item?
 Solution

Following SAMPLE script is intended

To enter a Receive and Deliver to Inventory Transaction with Inventory destination type
for an Inter-Organization Transfer done 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)


In order to be most comprehensive, the sample script has been tested for a Lot
and Serial Controlled Item.

The script will load records into the tables
          RCV_HEADERS_INTERFACE,
          RCV_TRANSACTIONS_INTERFACE,
          MTL_TRANSACTION_LOTS_INTERFACE
          MTL_SERIAL_NUMBERS_INTERFACE

If the item is only a standard item, only the records into
RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE tables have to be created.


          ------------------------SETUP------------------------

0) 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) and For Release 12 see Note 423541.1


1) Inventory Setup/Organizations/Shipping Networks
from Sending Organization M1 to Destination Organization D2
Transfer Type=Intransit
Receipt Routing=Direct

2) Item  'Lot-Serial-Controlled-1' is a lot and serial controlled Item in
sending and Destination Organization

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;

For both organizations, for item=Lot-Serial-Controlled-1 , inventory_item_id=169845
and LOT_CONTROL='Y' and SERIAL_CONTROL='Dynamic at INV receipt'


3) Inventory  / Transactions/ Inter Organization transfer
Create manual "Intransit Shipment" 'ROI-Lot-Serial-1' from M1 to D2 for a quantity of 10
- From Org= M1, To Org= D2
- Shipment: 'ROI-Lot-Serial-1' 
- Item= Lot-Serial-Controlled-1
- Lot=S00226
- From Subinventory=Stores
- To Subinventory=Staging1
- Quantity=10
- Start Serial Number=SM1_00001
- End Serial Number=SM1_00010


4) Run the following scripts so to find the necessary information to insert
into the RCV_TRANSACTIONS_INTERFACE table:

Select * from RCV_SHIPMENT_HEADERS where SHIPMENT_NUM like '&Shipment_Num';
        SHIPMENT_HEADER_ID=233534
        SHIPMENT_NUM= ROI-Lot-Serial-1
        RECEIPT_SOURCE_CODE=INVENTORY
        RECEIPT_NUM=null
        SHIP_TO_ORG_ID=210

Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_Header_id;
        SHIPMENT_HEADER_ID=233534
        SHIPMENT_LINE_ID=246486
        UNIT_OF_MEASURE=Each
        ITEM_ID=169845
        QUANTITY_SHIPPED=10
        QUANTITY_RECEIVED=0
        SHIPMENT_LINE_STATUS_CODE=EXPECTED
        SOURCE_DOCUMENT_CODE=INVENTORY
        ROUTING_HEADER_ID=3
        FROM_ORGANIZATION=207
        TO_ORGANIZATION_ID=210
        TO_SUBINVENTORY=Staging1


5)  Run the following scripts to identify the Lot/Serial Information
related to the SHIPMENT_LINE_ID=246486 of the Inter-Org Shipment with SHIPMENT_HEADER_ID=233534

Select * from RCV_LOTS_SUPPLY where SHIPMENT_LINE_ID=&Shipment_Line_id;
         SHIPMENT_LINE_ID=246486
         SUPPLY_TYPE_CODE=SHIPMENT
         LOT_NUM=S00226
         QUANTITY=10

Select * from RCV_SERIALS_SUPPLY where SHIPMENT_LINE_ID=&Shipment_Line_id;
10 records with
         SHIPMENT_LINE_ID=246486
         SUPPLY_TYPE_CODE=SHIPMENT
         SERIAL_NUM= serial numbers from SM1_00001 to SM1_00010
         LOT_NUM=S00226



RECEIVE/ DELIVER to INVENTORY Transaction for INTER-ORG TRANSFER SHIPMENT Example

1) Insert via ROI a Direct DELIVER Receipt for Inter Organization Shipment Number
'ROI-Lot-Serial-1' (SHIPMENT_HEADER_ID=233534)
of 2 items in destination organization
with LOT_NUM=S00226 and serial numbers SM1_00001 to SM1_00002

Insert
. 1 record in RCV_HEADERS_INTERFACE table for the receipt header information
with SHIPMENT_NUM='ROI-Lot-Serial-1'
and VALIDATION_FLAG='Y'

. 1 record in RCV_TRANSACTIONS_INTERFACE table for SHIPMENT_LINE_ID=246486
with TRANSACTION_TYPE='RECEIVE' and AUTO_TRANSACT_CODE='DELIVER'
for a quantity =2 indicating
DESTINATION_TYPE_CODE='INVENTORY'
RECEIPT_SOURCE_CODE='INVENTORY' and SOURCE_DOCUMENT_CODE='INVENTORY'
VALIDATION_FLAG='Y'

. 1 record in MTL_TRANSACTION_LOTS_INTERFACE for a quantity=2
on lot number S00226

. 1 record in MTL_SERIAL_NUMBERS_INTERFACE , indicating
FM_SERIAL_NUMBER='SM1_00001'and TO_SERIAL_NUMBER='SM1_00002'


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,
             VALIDATION_FLAG
              )
            VALUES
             (rcv_headers_interface_s.nextval , --Header_Interface_Id
             rcv_interface_groups_s.nextval,   --Group_Id
             'PENDING', --Processing_Status_Code
             'INVENTORY', --Receipt_Source_Code
             'NEW', --Transaction_Type
             'DELIVER',  --Auto_Transact_Code
             SYSDATE, --Last_Update_Date
             0,  --Last_Updated_By
             0,  --Last_Update_Login
             SYSDATE, --Creation_Date
             0, --Created_By
             'ROI-Lot-Serial-1',   --Shipment_Num
             210,            --Ship_To_Organization_Id,
             SYSDATE,        --Expected_Receipt_Date
             '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,
             RECEIPT_SOURCE_CODE,
             TO_ORGANIZATION_ID,
             SOURCE_DOCUMENT_CODE,
             DESTINATION_TYPE_CODE,
             SUBINVENTORY,
             SHIPMENT_NUM,
             EXPECTED_RECEIPT_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
             169845,                    --ITEM_ID
             13706,                      --EMPLOYEE_ID
             'DELIVER',              --AUTO_TRANSACT_CODE
             233534,                    --SHIPMENT_HEADER_ID
             246486,                    --SHIPMENT_LINE_ID
             'INVENTORY',        --RECEIPT_SOURCE_CODE
             210,                          --TO_ORGANIZATION_ID
             'INVENTORY',        --SOURCE_DOCUMENT_CODE
             'INVENTORY',        --DESTINATION_TYPE_CODE
             'Staging1',                 --SUBINVENTORY
             'ROI-Lot-Serial-1',    --SHIPMENT_NUM
             SYSDATE,               --EXPECTED_RECEIPT_DATE,
             rcv_headers_interface_s.currval,  --HEADER_INTERFACE_ID
             'Y'                            --VALIDATION_FLAG
             );


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
            )
            VALUES
            ( MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,--TRANSACTION_INTERFACE_ID
            SYSDATE, --LAST_UPDATE_DATE
            0,                --LAST_UPDATED_BY
            SYSDATE, --CREATION_DATE
            0,                --CREATED_BY
            0,                --LAST_UPDATE_LOGIN
            'S00226',    --LOT_NUMBER
            2,                --TRANSACTION_QUANTITY
            2,                --PRIMARY_QUANTITY
            MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --SERIAL_TRANSACTION_TEMP_ID
            'RCV',         --PRODUCT_CODE
            RCV_TRANSACTIONS_INTERFACE_S.CURRVAL  --PRODUCT_TRANSACTION_ID
            );

INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
            ( TRANSACTION_INTERFACE_ID,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN,
            FM_SERIAL_NUMBER,
            TO_SERIAL_NUMBER,
            PRODUCT_CODE,
            PRODUCT_TRANSACTION_ID)
            VALUES
            (MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,--TRANSACTION_INTERFACE_ID
            SYSDATE,  --LAST_UPDATE_DATE
            0,                 --LAST_UPDATED_BY
            SYSDATE,  --CREATION_DATE
            0,                 --CREATED_BY
            0,                 --LAST_UPDATE_LOGIN
            'SM1_00001', --FM_SERIAL_NUMBER
            'SM1_00002', --TO_SERIAL_NUMBER
            'RCV',         --PRODUCT_CODE
            RCV_TRANSACTIONS_INTERFACE_S.CURRVAL  --PRODUCT_TRANSACTION_ID
            );
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=25168
HEADER_INTERFACE_ID=107193
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y

SQL> Select * from RCV_TRANSACTIONS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
INTERFACE_TRANSACTION_ID=238839
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
TRANSACTION_TYPE=RECEIVE
AUTO_TRANSACT_CODE=DELIVER
RECEIPT_SOURCE_CODE=INVENTORY
TO_ORGANIZATION_ID=210
SOURCE_DOCUMENT_CODE=INVENTORY
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y

SQL> Select * from MTL_TRANSACTION_LOTS_INTERFACE
where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;
TRANSACTION_INTERFACE_ID=11305732
LOT_NUMBER=S00226
SERIAL_TRANSACTION_TEMP_ID=11305732
PRODUCT_CODE=RCV
PRODUCT_TRANSACTION_ID=238839

SQL> Select * from MTL_SERIAL_NUMBERS_INTERFACE
where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;
TRANSACTION_INTERFACE_ID=11305732
FM_SERIAL_NUMBER=SM1_00001
TO_SERIAL_NUMBER=SM1_00002
PRODUCT_TRANSACTION_ID=238839


3) In Purchasing Responsibility, Change to receiving organization and
run the Receiving Transaction Processor for the given group_id used in RHI and RTI (GROUP_ID=25168)


4) Navigate to Receiving / Receiving Transactions Summary form
For Shipment Number ROI-Lot-Serial-1, Receipt Number 5012 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=25168
HEADER_INTERFACE_ID=107193
PROCESSING_STATUS_CODE=SUCCESS
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y
             
SQL> Select * from RCV_SHIPMENT_HEADERS where SHIPMENT_NUM like '&Shipment_Num';
SHIPMENT_HEADER_ID=233534
SHIPMENT_NUM= ROI-Lot-Serial-1
RECEIPT_NUM=5012

SQL> Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_Header_id;
SHIPMENT_HEADER_ID=233534
SHIPMENT_LINE_ID=246486
UNIT_OF_MEASURE=Each
ITEM_ID=169845
QUANTITY_SHIPPED=10
QUANTITY_RECEIVED=2
SHIPMENT_LINE_STATUS_CODE=PARTIALLY RECEIVED

SQL> Select * from RCV_TRANSACTIONS where SHIPMENT_HEADER_ID=233534
It returns 2 records

For TRANSACTION_TYPE=RECEIVE
    TRANSACTION_ID=307969
    DESTINATION_TYPE_CODE=RECEIVING
    PARENT_TRANSACTION_ID=-1
    QUANTITY=2

For TRANSACTION_TYPE=DELIVER
    TRANSACTION_ID=307970
    DESTINATION_TYPE_CODE=INVENTORY
    PARENT_TRANSACTION_ID=307969
    QUANTITY=2