详解EBS接口开发之库事务处理带提前发运通知(ASN)采购接收入库-补充



A)   Via ROI Create a ASN [ship,ship]  for a quantity =3 on STANDARD PURCHASE ORDER Create  via ROI an Advanced Shipment Notice ASN number F-ASN-PO-17646-3
for  Standard Purchase Order Number 17646 (PO_HEADER_ID=97981) on Vendor =F_Supplier of 3 items in destination organization V1

1) Insert following Data:
. 1 record in RCV_HEADERS_INTERFACE
    RECEIPT_SOURCE_CODE = VENDOR
    ASN_TYPE  = ASN
    TRANSACTION_TYPE=   NEW
    AUTO_TRANSACT_CODE =  SHIP
    SHIPMENT_NUM  = F-ASN-PO-17646-3

. 1 record in RCV_TRANSACTIONS_INTERFACE
   TRANSACTION_TYPE =   SHIP
   AUTO_TRANSACT_CODE = SHIP
   RECEIPT_SOURCE_CODE = VENDOR
   QUANTITY  = 3

 Use the insert script of Document 225233.1 to create ASN shipment number
  'F-ASN-PO-17646-3' SHIPMENT_NUM with quantity shipped=3
  for Standard PO 17646 - Line 1 - Shipment line 1

INSERT INTO RCV_HEADERS_INTERFACE            (HEADER_INTERFACE_ID,             GROUP_ID,             PROCESSING_STATUS_CODE,             RECEIPT_SOURCE_CODE,             ASN_TYPE,             TRANSACTION_TYPE,             AUTO_TRANSACT_CODE,             LAST_UPDATE_DATE,             LAST_UPDATED_BY,             LAST_UPDATE_LOGIN,             CREATION_DATE,             CREATED_BY,             SHIPMENT_NUM,             VENDOR_ID,             SHIP_TO_ORGANIZATION_ID,             SHIPPED_DATE,             EXPECTED_RECEIPT_DATE,             EMPLOYEE_ID,             ORG_ID,             VALIDATION_FLAG            )            VALUES             (rcv_headers_interface_s.nextval , --Header_Interface_Id             rcv_interface_groups_s.nextval,   --Group_Id             'PENDING', --Processing_Status_Code             'VENDOR', --Receipt_Source_Code             'ASN', --ASN_TYPE             'NEW', --Transaction_Type             'SHIP',  --Auto_Transact_Code             SYSDATE, --Last_Update_Date             0,  --Last_Updated_By             0,  --Last_Update_Login             SYSDATE, --Creation_Date             0, --Created_By             'F-ASN-PO-17646-3', --SHIPMENT_NUM             7927,   --Vendor_Id             204, -- SHIP_TO_ORGANIZATION_ID             SYSDATE, --SHIPPED_DATE             SYSDATE+1, --Expected_Receipt_Date             13706,    --Employee_Id             204, --Operating_Unit_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,           ITEM_ID,           AUTO_TRANSACT_CODE,           RECEIPT_SOURCE_CODE,           SOURCE_DOCUMENT_CODE,           DOCUMENT_NUM,           DOCUMENT_LINE_NUM,           SHIP_TO_LOCATION_ID,           TO_ORGANIZATION_ID,           HEADER_INTERFACE_ID,           ORG_ID,           VALIDATION_FLAG          )        SELECT           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           'SHIP',               --TRANSACTION_TYPE_SHIP           SYSDATE,              --TRANSACTION_DATE           'PENDING',            --PROCESSING_STATUS_CODE           'BATCH',              --PROCESSING_MODE_CODE           'PENDING',            --TRANSACTION_STATUS_CODE           3,                    --QUANTITY           'Each',               --UNIT_OF_MEASURE           26816,                --ITEM_ID           'SHIP',               --AUTO_TRANSACT_CODE           'VENDOR',             --RECEIPT_SOURCE_CODE           'PO',                 --SOURCE_DOCUMENT_CODE           '17646',              --DOCUMENT_NUM           '1',                  --DOCUMENT_LINE_NUM           204,                  --SHIP_TO_LOCATION_ID           204,                  --TO_ORGANIZATION_ID           rcv_headers_interface_s.currval, --Header_interface_id           204,                  --ORG_ID           'Y'     --Validation_Flag          FROM DUAL;COMMIT;

Note: On R12 instance, RHI.ORG_ID or RTI.ORG_id need to be populated.
On 11.5.10 instance in RHI and in RTI ORG_ID columns do not exist

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

SQL> Select * from RCV_HEADERS_INTERFACEwhere SHIPMENT_NUM like 'F-ASN-PO-17646-3%;

HEADER_INTERFACE_ID     488353
GROUP_ID        63266
PROCESSING_STATUS_CODE  PENDING
RECEIPT_SOURCE_CODE     VENDOR
ASN_TYPE        ASN
TRANSACTION_TYPE        NEW
AUTO_TRANSACT_CODE      SHIP
LAST_UPDATE_DATE        11/01/2008 12:14:52
LAST_UPDATED_BY 0
LAST_UPDATE_LOGIN       0
CREATION_DATE   11/01/2008 12:14:52
CREATED_BY      0
SHIPMENT_NUM    F-ASN-PO-17646-3
RECEIPT_NUM   
RECEIPT_HEADER_ID     
FROM_ORGANIZATION_ID  
SHIP_TO_ORGANIZATION_CODE     
SHIP_TO_ORGANIZATION_ID 204
LOCATION_CODE 
SHIPPED_DATE    11/01/2008 12:14:52
FREIGHT_CARRIER_CODE  
EXPECTED_RECEIPT_DATE   11/01/2008 12:14:52
EMPLOYEE_ID     13706
VALIDATION_FLAG Y
ORG_ID 204
PROCESSING_REQUEST_ID 

SQL> Select * from RCV_TRANSACTIONS_INTERFACE where HEADER_INTERFACE_ID=488353;

INTERFACE_TRANSACTION_ID        2159384
GROUP_ID        63266
TRANSACTION_TYPE        SHIP
TRANSACTION_DATE        11/01/2008 12:15:12
PROCESSING_STATUS_CODE  PENDING
PROCESSING_MODE_CODE    BATCH
PROCESSING_REQUEST_ID 
TRANSACTION_STATUS_CODE PENDING
QUANTITY        3
UNIT_OF_MEASURE Each
ITEM_ID 26816
AUTO_TRANSACT_CODE      SHIP
SHIPMENT_HEADER_ID    
SHIPMENT_LINE_ID      
SHIP_TO_LOCATION_ID     204
RECEIPT_SOURCE_CODE     VENDOR
TO_ORGANIZATION_ID      204
SOURCE_DOCUMENT_CODE    PO
HEADER_INTERFACE_ID     488353
DOCUMENT_NUM    17646
DOCUMENT_LINE_NUM       1
ORG_ID 204
VALIDATION_FLAG Y


3) In Purchasing Responsibility, Change to receiving organization V1 and run the Receiving Transaction Processor for the given group_id used in RHI and RTI:
Run RVCTP  for GROUP_ID=63266
-> Request 6913750 RVCTP module: Receiving Transaction Processor


4) Navigate to Manage Shipments form

    Org V1
    Receiving/Manage Shipments
    In Manage Shipments form   
    Find In-Transit Shipments
    Search Criteria
             Source Type=Supplier
             Shipment = F-ASN-PO-17646-3
    One shipment line is retrieved for Qty Shipped =3 for PO Number 17646

 

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

SQL> Select * from RCV_HEADERS_INTERFACEwhere SHIPMENT_NUM like 'F-ASN-PO-17646-3%;

HEADER_INTERFACE_ID     488353
GROUP_ID        63266
RECEIPT_HEADER_ID 1920749
PROCESSING_STATUS_CODE  SUCCESS
PROCESSING_REQUEST_ID   6913750

SQL> Select * from RCV_SHIPMENT_HEADERSwhere SHIPMENT_NUM like 'F-ASN-PO-17646-3%;

1 record has been created
SHIPMENT_HEADER_ID      1920749
RECEIPT_SOURCE_CODE     VENDOR
VENDOR_ID       7927
VENDOR_SITE_ID  4724
SHIPMENT_NUM    F-ASN-PO-17646-3
RECEIPT_NUM   
SHIPPED_DATE    11/01/2008 12:14:52
EXPECTED_RECEIPT_DATE   12/01/2008 12:14:52
EMPLOYEE_ID     13706
REQUEST_ID      6913750
ASN_TYPE        ASN
ASN_STATUS      NEW_SHIP
SHIP_TO_ORG_ID  204

SQL> Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_header_id;

->One record with
SHIPMENT_LINE_ID        1874712
SHIPMENT_HEADER_ID      1920749
LINE_NUM        1
CATEGORY_ID     1
QUANTITY_SHIPPED        3
QUANTITY_RECEIVED       0
UNIT_OF_MEASURE Each
ITEM_DESCRIPTION        Standard Purchased Item
ITEM_ID 26816
SHIPMENT_LINE_STATUS_CODE       EXPECTED
SOURCE_DOCUMENT_CODE    PO
PO_HEADER_ID    97981
PO_LINE_ID      105281
PO_LINE_LOCATION_ID     139830
PO_DISTRIBUTION_ID    
DESTINATION_TYPE_CODE   RECEIVING
TO_ORGANIZATION_ID      204
REQUEST_ID      6913750
PRIMARY_UNIT_OF_MEASURE Each
ASN_LINE_FLAG   Y
ORIGINAL_ASN_PARENT_LINE_ID   
ORIGINAL_ASN_LINE_FLAG
SHIP_TO_LOCATION_ID     204


B)   Via ROI Create a Receive / Deliver transaction  for a quantity =1  on ASN already created

Perform the deliver transaction for the ASN for a quantity = 1
Follow instructions of  Document 461928.1   For Receiving an ASN which is already  created and Document 368811.1

1)  Insert the following data
. 1 record in RCV_HEADERS_INTERFACE
    RECEIPT_SOURCE_CODE = VENDOR
    ASN_TYPE  = null (or STD)
    TRANSACTION_TYPE=   NEW
    AUTO_TRANSACT_CODE =  DELIVER
    SHIPMENT_NUM  = RCV_SHIPMENT_HEADERS.SHIPMENT_NUM
    RECEIPT_HEADER_ID = RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID
    VENDOR_ID= RCV_SHIPMENT_HEADERS.VENDOR_ID

. 1 record in RCV_TRANSACTIONS_INTERFACE
   TRANSACTION_TYPE=   RECEIVE
   AUTO_TRANSACT_CODE =  DELIVER
   RECEIPT_SOURCE_CODE = VENDOR
   VENDOR_ID= RCV_SHIPMENT_HEADERS.VENDOR_ID
   QUANTITY  = 1
   SHIPMENT_HEADER_ID = RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID
   SHIPMENT_LINE_ID = RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID
   PO_HEADER_ID=PO_HEADERS_ALL.PO_HEADER_ID

INSERT INTO RCV_HEADERS_INTERFACE            (HEADER_INTERFACE_ID,             GROUP_ID,             PROCESSING_STATUS_CODE,             RECEIPT_SOURCE_CODE,             ASN_TYPE,             TRANSACTION_TYPE,             AUTO_TRANSACT_CODE,             LAST_UPDATE_DATE,             LAST_UPDATED_BY,             LAST_UPDATE_LOGIN,             CREATION_DATE,             CREATED_BY,             SHIPMENT_NUM,             RECEIPT_HEADER_ID,             VENDOR_ID,             SHIP_TO_ORGANIZATION_ID,             EXPECTED_RECEIPT_DATE,             EMPLOYEE_ID,             ORG_ID,             VALIDATION_FLAG              )            VALUES             (rcv_headers_interface_s.nextval , --Header_Interface_Id             rcv_interface_groups_s.nextval,   --Group_Id             'PENDING', --Processing_Status_Code             'VENDOR', --Receipt_Source_Code             null, --ASN_TYPE             '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             'F-ASN-PO-17646-3', --SHIPMENT_NUM             1920749,  --RECEIPT_HEADER_ID             7927,     --VENDOR_ID             204, -- SHIP_TO_ORGANIZATION_ID             SYSDATE+1, --Expected_Receipt_Date             13706,    --Employee_Id             204,      --Org_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,           ITEM_ID,           AUTO_TRANSACT_CODE,           SHIPMENT_HEADER_ID,           SHIPMENT_LINE_ID,           SHIP_TO_LOCATION_ID,           RECEIPT_SOURCE_CODE,           VENDOR_ID,           TO_ORGANIZATION_ID,           PO_HEADER_ID,           DESTINATION_TYPE_CODE,           DELIVER_TO_PERSON_ID,           LOCATION_ID,           DELIVER_TO_LOCATION_ID,           SUBINVENTORY,           HEADER_INTERFACE_ID,           ORG_ID,           VALIDATION_FLAG          )        SELECT           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_RECEIVE           SYSDATE,              --TRANSACTION_DATE           'PENDING',            --PROCESSING_STATUS_CODE           'BATCH',              --PROCESSING_MODE_CODE           'PENDING',            --TRANSACTION_STATUS_CODE           1,                    --QUANTITY           'Each',               --UNIT_OF_MEASURE           26816,                --ITEM_ID           'DELIVER',               --AUTO_TRANSACT_CODE           1920749,                 --SHIPMENT_HEADER_ID           1874712,                  --SHIPMENT_LINE_ID           204,                  --SHIP_TO_LOCATION_ID           'VENDOR',             --RECEIPT_SOURCE_CODE           7927,                 --VENDOR_ID           204,                  --TO_ORGANIZATION_ID           97981,           --PO_HEADER_ID            'INVENTORY',          --DESTINATION_TYPE_CODE           13706,                 --DELIVER_TO_PERSON_ID           204,                  --LOCATION_ID           204,                  --DELIVER_TO_LOCATION_ID           'Stores',             --SUBINVENTORY           rcv_headers_interface_s.currval, --Header_interface_id           204,                  --ORG_ID           'Y'                   --VALIDATION_FLAG          FROM DUAL;COMMIT;

Note: On R12 instance, RHI.ORG_ID or RTI.ORG_ID need to be populated.
On 11.5.10 instance in RHI and in RTI ORG_ID columns do not exist

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

SQL> Select * from RCV_HEADERS_INTERFACEwhere SHIPMENT_NUM like 'F-ASN-PO-17646-3%;

There is a new record with
HEADER_INTERFACE_ID     491353
GROUP_ID        63406 
PROCESSING_STATUS_CODE  PENDING
RECEIPT_SOURCE_CODE     VENDOR
ASN_TYPE                null
TRANSACTION_TYPE        NEW
AUTO_TRANSACT_CODE      DELIVER
SHIPMENT_NUM            F-ASN-PO-17646-3
RECEIPT_HEADER_ID       1920749
VENDOR_ID               7927
SHIP_TO_ORGANIZATION_ID 204   
EXPECTED_RECEIPT_DATE   15/01/2008 15:58:50
EMPLOYEE_ID     13706
VALIDATION_FLAG Y
PROCESSING_REQUEST_ID 

SQL> Select * from RCV_TRANSACTIONS_INTERFACEwhere HEADER_INTERFACE_ID = 491353;

INTERFACE_TRANSACTION_ID        2167384
GROUP_ID        63406
LAST_UPDATE_DATE        14/01/2008 15:59
LAST_UPDATED_BY 0
CREATION_DATE   14/01/2008 15:59
CREATED_BY      0
LAST_UPDATE_LOGIN       0
REQUEST_ID   
TRANSACTION_TYPE        RECEIVE
TRANSACTION_DATE        14/01/2008 15:59
PROCESSING_STATUS_CODE  PENDING
PROCESSING_MODE_CODE    BATCH
PROCESSING_REQUEST_ID
TRANSACTION_STATUS_CODE PENDING
QUANTITY        1
UNIT_OF_MEASURE Each
ITEM_ID 26816
AUTO_TRANSACT_CODE      DELIVER
SHIPMENT_HEADER_ID      1920749
SHIPMENT_LINE_ID        1874712
SHIP_TO_LOCATION_ID     204
RECEIPT_SOURCE_CODE     VENDOR
VENDOR_ID       7927
TO_ORGANIZATION_ID      204
PO_HEADER_ID    97981
DESTINATION_TYPE_CODE   INVENTORY
DELIVER_TO_PERSON_ID    13706
LOCATION_ID     204
DELIVER_TO_LOCATION_ID  204
SUBINVENTORY    Stores
HEADER_INTERFACE_ID     491353
VALIDATION_FLAG Y

3) In Purchasing Responsibility, Change to receiving organization and run the Receiving Transaction Processor for the given group_id used in RHI and RTI
Run RVCTP  for GROUP_ID=63406
Request 6924706 RVCTP module: Receiving Transaction Processor

4) Navigate to Receiving Transactions Summary form

     Receiving / Receiving Transactions Summary RCVRCVRC Form
    Org V1
    When searching for Receipts for Shipment F-ASN-PO-17646-3
    or Purchase Order 17646
    Receipt Number 9033 appears and 2 transactions can be seen
    receipt_num in rcv_shipment_headers is now = 9033

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

SQL> Select * from RCV_HEADERS_INTERFACEwhere SHIPMENT_NUM like 'F-ASN-PO-17646-3%;

2 records have SUCCESS status

SQL> Select * from RCV_SHIPMENT_HEADERSwhere SHIPMENT_NUM like 'F-ASN-PO-17646-3%;

SHIPMENT_HEADER_ID      1920749
RECEIPT_SOURCE_CODE     VENDOR
VENDOR_ID       7927
VENDOR_SITE_ID  4724
SHIPMENT_NUM    F-ASN-PO-17646-3
RECEIPT_NUM     9033
SHIPPED_DATE    11/01/2008 12:14
EXPECTED_RECEIPT_DATE   15/01/2008 15:58
EMPLOYEE_ID     13706
REQUEST_ID      6913750
ASN_TYPE        ASN
SHIP_TO_ORG_ID  204

SQL> Select * from RCV_SHIPMENT_LINESwhere SHIPMENT_HEADER_ID=&Shipment_header_id;

SHIPMENT_LINE_ID        1874712
SHIPMENT_HEADER_ID      1920749
LINE_NUM        1
QUANTITY_SHIPPED        3
QUANTITY_RECEIVED       1
UNIT_OF_MEASURE Each
ITEM_DESCRIPTION        Standard Purchased Item
ITEM_ID 26816
SHIPMENT_LINE_STATUS_CODE       PARTIALLY RECEIVED
SOURCE_DOCUMENT_CODE    PO
PO_HEADER_ID    97981
PO_LINE_ID      105281
PO_LINE_LOCATION_ID     139830
TO_ORGANIZATION_ID      204
REQUEST_ID      6924706
ASN_LINE_FLAG   Y

SQL> Select * from RCV_TRANSACTIONS where SHIPMENT_HEADER_ID=&Shipment_header_id;

2 receive and deliver transactions have been created

For TRANSACTION_TYPE = RECEIVE
    TRANSACTION_ID    2016379
    REQUEST_ID    6924706
    SHIPMENT_HEADER_ID    1920749
    SHIPMENT_LINE_ID    1874712
    DESTINATION_TYPE_CODE    RECEIVING
    PARENT_TRANSACTION_ID    -1
    PO_HEADER_ID    97981
    QUANTITY    1
    INTERFACE_TRANSACTION_ID    2167384

For TRANSACTION_TYPE = DELIVER
    TRANSACTION_ID    2016380
    REQUEST_ID    6924706
    SHIPMENT_HEADER_ID    1920749
    SHIPMENT_LINE_ID    1874712
    DESTINATION_TYPE_CODE    INVENTORY
    PARENT_TRANSACTION_ID    2016379
    PO_HEADER_ID    97981
    QUANTITY    1
    INTERFACE_TRANSACTION_ID    2167384