ROI脚本(一)

ROI: receiving open interface, 是提供给客户的接口, 通过 ROI 客户可以不通过EBS form 界面做receiving 的动作, 而是通过脚本插入相关的接口表 ( RHI, RTI 等), 再手动调用 concurrent request: RTP 来处理接口表的数据. 下面的脚本总结了几个常用业务的ROI 脚本, 以便平时使用.

1. PO receipt for lot & serial controlled item -- Note: 368811.1

脚本适用于: 标准 PO, Direct deliver routine, Lot & Serial item

SELECT * FROM mtl_system_items_b    WHERE segment1 = 'yuLotSerial';
SELECT * FROM po_headers_all        WHERE segment1 = '7615';
SELECT * FROM po_lines_all          WHERE po_header_id = 883313;
SELECT * FROM po_line_locations_all WHERE po_header_id = 883313;
SELECT * FROM po_distributions_all  WHERE po_header_id = 883313;
SELECT * FROM mtl_supply            WHERE po_header_id = 883313;

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,
             VENDOR_ID,
             SHIP_TO_ORGANIZATION_ID,
             EXPECTED_RECEIPT_DATE,
             VALIDATION_FLAG,
             ORG_ID
              )
            VALUES
             (rcv_headers_interface_s.nextval , 
             rcv_interface_groups_s.nextval,  
             'PENDING', 
             'VENDOR', 
             'NEW', 
             'DELIVER',  
             SYSDATE, 
             0, 
             0,  
             SYSDATE, 
             0, 
             147,       --po_headers_all.Vendor_Id
             207,       --po_line_locations_all.SHIP_TO_ORGANIZATION_ID
             SYSDATE,        
             'Y',           
             204        --po_headers_all.Org_id
             );
    
    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,
           ITEM_ID,
           EMPLOYEE_ID,
           AUTO_TRANSACT_CODE,
           SHIP_TO_LOCATION_ID,
           RECEIPT_SOURCE_CODE,
           VENDOR_ID,
           SOURCE_DOCUMENT_CODE,
           PO_HEA





DER_ID,
           PO_LINE_ID,
           PO_LINE_LOCATION_ID,
           DESTINATION_TYPE_CODE,
           DELIVER_TO_PERSON_ID,
           LOCATION_ID,
           DELIVER_TO_LOCATION_ID,
           SUBINVENTORY,
           HEADER_INTERFACE_ID,
           DOCUMENT_NUM,
           TO_ORGANIZATION_ID,
           VALIDATION_FLAG,
           ORG_ID
          )
        SELECT
           rcv_transactions_interface_s.nextval, 
           rcv_interface_groups_s.currval,        
           SYSDATE,              
           0,                   
           SYSDATE,             
           0,                   
           0,                    
           'RECEIVE',            
           SYSDATE,              
           'PENDING',           
           'BATCH',             
           'PENDING',           
           50,                    --QUANTITY
           'Each',                --po_lines_all.UNIT_OF_MEASURE
           736956,                --po_lines_all.ITEM_ID
           0,              
           'DELIVER',       
           207,                   --po_line_locations_all.SHIP_TO_LOCATION_ID
           'VENDOR',       
           147,                   --po_headers_all.VENDOR_ID
           'PO',               
           883313,                --mtl_supply.PO_HEADER_ID
           954242,                --mtl_supply.PO_LINE_ID
           1051390,               --mtl_supply.PO_LINE_LOCATION_ID
           'INVENTORY',          
           null,                 
           207,                   --LOCATION_ID
           207,                   --DELIVER_TO_LOCATION_ID
           'FGI',                 --SUBINVENTORY
           rcv_headers_interface_s.currval, 
           7615,                  --PO number
           207,                   --TO_ORGANIZATION_ID
           'Y',                   --VALIDATION_FLAG
           204                    --Org_id_Operating_Unit_Id
FROM DUAL;

 
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,
            SERIAL_TRANSACTION_TEMP_ID,
            PRODUCT_CODE,
            PRODUCT_TRANSACTION_ID
            )
            VALUES
            ( MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
            SYSDATE, 
            0,                
            SYSDATE, 
            0,                
            0,               
            'L-1000',          --LOT_NUMBER
            50,                --TRANSACTION_QUANTITY
            MTL_