详解EBS接口开发之库存事务处理采购接收--补充

摘自：http://blog.csdn.net/cai_xingyun/article/details/17533505

除了可以用



详解EBS接口开发之库存事务处理采购接收的方法还可以用一下方法，不同之处在于带有批次和序列控制的时候实现方式不同

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 Document 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 Document 423541.1 


1) Purchasing / Purchase Orders / Purchase Orders

Standard Purchase Order number 10084 is entered
Vendor F-Supplier
One line / shipment line/ distribution line for
Item=F-Lot-Serial-Controlled-4
Quantity=10
Destination Org=M2
Ship To=M2-Boston
Receipt Routing=Direct Delivery

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

[sql]  view plain  copy

1. SQL> Select mp.organization_id Org_Id, mp.organization_code Org_Code,  
2. msi.inventory_item_id, msi.segment1, decode(to_char(msi.lot_control_code),  
3.   '2','Y',  
4.   '1','N') LOT_CONTROL,  
5. decode(to_char(msi.serial_number_control_code),  
6.   '1','None',  
7.   '2','Predefined',  
8.   '5','Dynamic at INV receipt',  
9.   '6','Dynamic at SO issue') SERIAL_CONTROL  
10.   from mtl_system_items_b msi,mtl_parameters mp  
11. where msi.segment1 like '&item' and msi.organization_id=mp.organization_id;  

For item=F-Lot-Serial-Controlled-4 , inventory_item_id=378856 and LOT_CONTROL='Y' and SERIAL_CONTROL='Dynamic at INV receipt'

3)  In Purchasing Responsibility

Receiving/Receipts
In org M2 Boston
Find expected receipts
Source Type =Supplier
Purchase Order = 10084
-> Ensure 1 line is available to be receipted 
Order type = Standard
Order = 10084
Routing = Direct Delivery

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

[sql]  view plain  copy

1. Select * from PO_HEADERS_ALL where SEGMENT1 = '&po_number'  
2.   PO_HEADER_ID=44274  
3.   TYPE_LOOKUP_CODE=STANDARD  
4.   SEGMENT1=10084  
5.   VENDOR_ID=7927  
6.   VENDOR_SITE_ID=4724  
7.   APPROVED_FLAG=Y  
8.   ORG_ID=204  
9.   
10. Select * from PO_LINES_ALL where PO_HEADER_ID=&Po_header_id;  
11.   PO_LINE_ID=50364  
12.   PO_HEADER_ID=44274  
13.   LINE_NUM=1  
14.   ITEM_ID=378856  
15.   CATEGORY_ID=1  
16.   ITEM_DESCRIPTION=F-Lot Serial Controlled Item for ROI/Internal SO  
17.   UNIT_MEAS_LOOKUP_CODE=Each  
18.   QUANTITY=10  
19.   ORG_ID=204  
20.   
21. Select * from PO_LINE_LOCATIONS_ALL where PO_HEADER_ID=&Po_header_id;  
22.   LINE_LOCATION_ID=85112  
23.   PO_HEADER_ID=44274  
24.   PO_LINE_ID=50364  
25.   QUANTITY=10,00  
26.   QUANTITY_RECEIVED=0,00  
27.   SHIP_TO_LOCATION_ID=209  
28.   NEED_BY_DATE=01/05/2006  
29.   PROMISED_DATE=01/05/2006  
30.   SHIP_TO_ORGANIZATION_ID=209  
31.   SHIPMENT_NUM=1  
32.   RECEIVING_ROUTING_ID=3  
33.   ORG_ID=204  
34.   
35. Select * from PO_DISTRIBUTIONS_ALL where PO_HEADER_ID=&Po_header_id;  
36.   PO_DISTRIBUTION_ID=86928  
37.   PO_LINE_LOCATION_ID=85112  
38.   PO_HEADER_ID=44274  
39.   PO_LINE_ID=50364  
40.   QUANTITY_DELIVERED=0,00  
41.   DELIVER_TO_LOCATION_ID=209  
42.   DELIVER_TO_PERSON_ID=13706  
43.   DESTINATION_TYPE_CODE=INVENTORY  
44.   DESTINATION_ORGANIZATION_ID=209  
45.   DESTINATION_SUBINVENTORY=Stores  
46.   DISTRIBUTION_NUM=1  
47.   ORG_ID=204  
48.   
49. Select * from MTL_SUPPLY where PO_HEADER_ID=&Po_header_id;  
50. SUPPLY_TYPE_CODE=PO  
51. SUPPLY_SOURCE_ID=86928  
52. PO_HEADER_ID=44274  
53. PO_LINE_ID=50364  
54. PO_LINE_LOCATION_ID=85112  
55. PO_DISTRIBUTION_ID=86928  
56. ITEM_ID=378856  
57. QUANTITY=10  
58. UNIT_OF_MEASURE=Each  
59. DESTINATION_TYPE_CODE=INVENTORY  
60. TO_ORGANIZATION_ID=209  

RECEIVE/ DELIVER to INVENTORY Transaction for STANDARD PURCHASE ORDER Example

1) Insert via ROI a Direct DELIVER Receipt for Standard Purchase Order Number 10084 (PO_HEADER_ID=44274) on vendor F-Supplier of 2 items in destination organization M2 with LOT_NUM=B00406 and serial numbers SN_00001 to SN_00002

Insert 
. 1 record in RCV_HEADERS_INTERFACE table for the receipt header information with RECEIPT_SOURCE_CODE='VENDOR' and VALIDATION_FLAG='Y'

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

. 1 record in MTL_TRANSACTION_LOTS_INTERFACE for a quantity=2, primary_quantity =2
on lot number B00406
(After Bug 9399287, there is not need to populate MTL_TRANSACTION_LOTS_INTERFACE.primary_quantity anymore)

. 1 record in MTL_SERIAL_NUMBERS_INTERFACE, indicating
FM_SERIAL_NUMBER='SN_00001'and TO_SERIAL_NUMBER='SN_00002'

[sql]  view plain  copy

1. INSERT INTO RCV_HEADERS_INTERFACE  
2.             (HEADER_INTERFACE_ID,  
3.              GROUP_ID,  
4.              PROCESSING_STATUS_CODE,  
5.              RECEIPT_SOURCE_CODE,  
6.              TRANSACTION_TYPE,  
7.              AUTO_TRANSACT_CODE,  
8.              LAST_UPDATE_DATE,  
9.              LAST_UPDATED_BY,  
10.              LAST_UPDATE_LOGIN,  
11.              CREATION_DATE,  
12.              CREATED_BY,  
13.              VENDOR_ID,  
14.              SHIP_TO_ORGANIZATION_ID,  
15.              EXPECTED_RECEIPT_DATE,  
16.              VALIDATION_FLAG  
17.               )  
18.             VALUES  
19.              (rcv_headers_interface_s.nextval , --Header_Interface_Id  
20.              rcv_interface_groups_s.nextval,   --Group_Id  
21.              'PENDING', --Processing_Status_Code  
22.              'VENDOR', --Receipt_Source_Code  
23.              'NEW', --Transaction_Type  
24.              'DELIVER',  --Auto_Transact_Code  
25.              SYSDATE, --Last_Update_Date  
26.              0,  --Last_Updated_By  
27.              0,  --Last_Update_Login  
28.              SYSDATE, --Creation_Date  
29.              0, --Created_By  
30.              7927,   --Vendor_Id  
31.              209,            --Ship_To_Organization_Id,  
32.              SYSDATE,        --Expected_Receipt_Date  
33.              'Y'             --Validation_Flag  
34.              );  
35.      
36.     INSERT INTO RCV_TRANSACTIONS_INTERFACE  
37.           (INTERFACE_TRANSACTION_ID,  
38.            GROUP_ID,  
39.            LAST_UPDATE_DATE,  
40.            LAST_UPDATED_BY,  
41.            CREATION_DATE,  
42.            CREATED_BY,  
43.            LAST_UPDATE_LOGIN,  
44.            TRANSACTION_TYPE,  
45.            TRANSACTION_DATE,  
46.            PROCESSING_STATUS_CODE,  
47.            PROCESSING_MODE_CODE,  
48.            TRANSACTION_STATUS_CODE,  
49.            QUANTITY,  
50.            UNIT_OF_MEASURE,  
51.            ITEM_ID,  
52.            EMPLOYEE_ID,  
53.            AUTO_TRANSACT_CODE,  
54.            SHIP_TO_LOCATION_ID,  
55.            RECEIPT_SOURCE_CODE,  
56.            VENDOR_ID,  
57.            SOURCE_DOCUMENT_CODE,  
58.            PO_HEADER_ID,  
59.            PO_LINE_ID,  
60.            PO_LINE_LOCATION_ID,  
61.            DESTINATION_TYPE_CODE,  
62.            DELIVER_TO_PERSON_ID,  
63.            LOCATION_ID,  
64.            DELIVER_TO_LOCATION_ID,  
65.            SUBINVENTORY,  
66.            HEADER_INTERFACE_ID,  
67.            DOCUMENT_NUM,  
68.            TO_ORGANIZATION_ID,  
69.            VALIDATION_FLAG  
70.           )  
71.         SELECT  
72.            rcv_transactions_interface_s.nextval, --Interface_Transaction_id  
73.            rcv_interface_groups_s.currval,         --Group_id  
74.            SYSDATE,              --Last_update_date  
75.            0,                    --Last_updated_by  
76.            SYSDATE,              --Creation_date  
77.            0,                    --Created_by  
78.            0,                    --Last_update_login  
79.            'RECEIVE',            --TRANSACTION_TYPE  
80.            SYSDATE,              --TRANSACTION_DATE  
81.            'PENDING',            --PROCESSING_STATUS_CODE  
82.            'BATCH',              --PROCESSING_MODE_CODE  
83.            'PENDING',            --TRANSACTION_STATUS_CODE  
84.            2,                       --QUANTITY  
85.            'Each',               --UNIT_OF_MEASURE  
86.            378856,            --ITEM_ID  
87.            13706,              --EMPLOYEE_ID  
88.            'DELIVER',       --AUTO_TRANSACT_CODE  
89.            209,                  --SHIP_TO_LOCATION_ID  
90.            'VENDOR',        --RECEIPT_SOURCE_CODE  
91.            7927,                  --VENDOR_ID  
92.            'PO',                   --SOURCE_DOCUMENT_CODE  
93.            44274,                --PO_HEADER_ID  
94.            50364,                --PO_LINE_ID  
95.            85112,                --PO_LINE_LOCATION_ID  
96.            'INVENTORY',          --DESTINATION_TYPE_CODE  
97.            13706,                 --DELIVER_TO_PERSON_ID  
98.            209,                  --LOCATION_ID  
99.            209,                  --DELIVER_TO_LOCATION_ID  
100.            'Stores',             --SUBINVENTORY  
101.            rcv_headers_interface_s.currval, --Header_interface_id  
102.            10084,                 --DOCUMENT_NUM  
103.            209,                  --TO_ORGANIZATION_ID  
104.            'Y'                   --VALIDATION_FLAG  
105.           FROM DUAL;  
106.   
107.    
108. INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE  
109.             ( TRANSACTION_INTERFACE_ID,  
110.             LAST_UPDATE_DATE,  
111.             LAST_UPDATED_BY,  
112.             CREATION_DATE,  
113.             CREATED_BY,  
114.             LAST_UPDATE_LOGIN,  
115.             LOT_NUMBER,  
116.             TRANSACTION_QUANTITY,  
117.             PRIMARY_QUANTITY,  
118.             SERIAL_TRANSACTION_TEMP_ID,  
119.             PRODUCT_CODE,  
120.             PRODUCT_TRANSACTION_ID  
121.             )  
122.             VALUES  
123.             ( MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,--TRANSACTION_INTERFACE_ID  
124.             SYSDATE, --LAST_UPDATE_DATE  
125.             0,                --LAST_UPDATED_BY  
126.             SYSDATE, --CREATION_DATE  
127.             0,                --CREATED_BY  
128.             0,                --LAST_UPDATE_LOGIN  
129.             'B00406',    --LOT_NUMBER  
130.             2,                --TRANSACTION_QUANTITY  
131.             2,                --PRIMARY_QUANTITY  
132.             MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --SERIAL_TRANSACTION_TEMP_ID  
133.             'RCV',         --PRODUCT_CODE  
134.             RCV_TRANSACTIONS_INTERFACE_S.CURRVAL  --PRODUCT_TRANSACTION_ID  
135.             );  
136.   
137. INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE  
138.             ( TRANSACTION_INTERFACE_ID,  
139.             LAST_UPDATE_DATE,  
140.             LAST_UPDATED_BY,  
141.             CREATION_DATE,  
142.             CREATED_BY,  
143.             LAST_UPDATE_LOGIN,  
144.             FM_SERIAL_NUMBER,  
145.             TO_SERIAL_NUMBER,  
146.             PRODUCT_CODE,  
147.             PRODUCT_TRANSACTION_ID)  
148.             VALUES  
149.             (MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,--TRANSACTION_INTERFACE_ID  
150.             SYSDATE,  --LAST_UPDATE_DATE  
151.             0,                 --LAST_UPDATED_BY  
152.             SYSDATE,  --CREATION_DATE  
153.             0,                 --CREATED_BY  
154.             0,                 --LAST_UPDATE_LOGIN  
155.             'SN_00001', --FM_SERIAL_NUMBER  
156.             'SN_00002', --TO_SERIAL_NUMBER  
157.             'RCV',         --PRODUCT_CODE  
158.             RCV_TRANSACTIONS_INTERFACE_S.CURRVAL  --PRODUCT_TRANSACTION_ID  
159.             );  
160. 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]  view plain  copy

1. SQL> Select * from RCV_TRANSACTIONS_INTERFACE where PO_HEADER_ID=&Po_header_id;  
2. INTERFACE_TRANSACTION_ID=576924  
3. GROUP_ID=32140  
4. HEADER_INTERFACE_ID=180194  
5. TRANSACTION_TYPE=RECEIVE  
6. AUTO_TRANSACT_CODE=DELIVER  
7. RECEIPT_SOURCE_CODE=VENDOR  
8. TO_ORGANIZATION_ID=209  
9. SOURCE_DOCUMENT_CODE=PO  
10. PO_HEADER_ID=44274  
11. DESTINATION_TYPE_CODE='INVENTORY'  
12. VALIDATION_FLAG=Y  
13.   
14. SQL> Select * from RCV_HEADERS_INTERFACE where HEADER_INTERFACE_ID=&Header_Interface_ID  
15. GROUP_ID=32140  
16. HEADER_INTERFACE_ID=180194  
17. VENDOR_ID=7927  
18. VALIDATION_FLAG=Y  
19.   
20. SQL> Select * from MTL_TRANSACTION_LOTS_INTERFACE  
21. where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;  
22. TRANSACTION_INTERFACE_ID=11599958  
23. LOT_NUMBER=B00406  
24. SERIAL_TRANSACTION_TEMP_ID=11599958  
25. PRODUCT_CODE=RCV  
26. PRODUCT_TRANSACTION_ID=576924  
27.    
28. SQL> Select * from MTL_SERIAL_NUMBERS_INTERFACE  
29. where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;  
30. TRANSACTION_INTERFACE_ID=11599958  
31. FM_SERIAL_NUMBER=SN_00001  
32. TO_SERIAL_NUMBER=SN_00002  
33. PRODUCT_TRANSACTION_ID=576924  

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=32140) 

4) Navigate to Receiving / Receiving Transactions Summary form For PO Number 10084, Receipt Number 5034 has Receive and Deliver transactions.

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

[sql]  view plain  copy

1. SQL> Select * from RCV_HEADERS_INTERFACE where HEADER_INTERFACE_ID=&Header_Interface_ID  
2. GROUP_ID=32140  
3. HEADER_INTERFACE_ID=180194  
4. PROCESSING_STATUS_CODE=SUCCESS  
5. RECEIPT_HEADER_ID=594567  
6. VENDOR_ID=7927  
7. VALIDATION_FLAG=Y  
8. PROCESSING_REQUEST_ID=3053037  
9.   
10. SQL> Select * from RCV_TRANSACTIONS where PO_HEADER_ID=&Po_header_id;  
11. It returns 2 records  

For TRANSACTION_TYPE=RECEIVE
    TRANSACTION_ID=638052
    REQUEST_ID=3053037
    SHIPMENT_HEADER_ID=594567
    SHIPMENT_LINE_ID=600529
    DESTINATION_TYPE_CODE=RECEIVING
    PARENT_TRANSACTION_ID=-1
    QUANTITY=2

For TRANSACTION_TYPE=DELIVER
    TRANSACTION_ID=638053
    REQUEST_ID=3053037
    SHIPMENT_HEADER_ID=594567
    SHIPMENT_LINE_ID=600529
    DESTINATION_TYPE_CODE=INVENTORY
    PARENT_TRANSACTION_ID=638052
    QUANTITY=2

[sql]  view plain  copy

1. SQL> Select * from RCV_SHIPMENT_HEADERS where SHIPMENT_HEADER_ID=&Shipment_header_id;  
2. 1 record has been created  
3.     SHIPMENT_HEADER_ID=594567  
4.     RECEIPT_SOURCE_CODE=VENDOR  
5.     RECEIPT_NUM=5034   
6.   
7. SQL> Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_header_id;  
8.     SHIPMENT_LINE_ID=600529  
9.     SHIPMENT_HEADER_ID=594567  
10.     QUANTITY_SHIPPED=2  
11.     QUANTITY_RECEIVED=2  
12.     SHIPMENT_LINE_STATUS_CODE=FULLY RECEIVED  
13.   
14. SQL> Select * from MTL_SERIAL_NUMBERS where INVENTORY_ITEM_ID=&Item_id;  
15. 2 records have been inserted with  
16. INVENTORY_ITEM_ID=378856  
17. SERIAL_NUMBER = SN_00001 / SN_00002  
18. LOT_NUMBER=B00406  
19. CURRENT_STATUS=3  
20. CURRENT_ORGANIZATION_ID=209  
21.   
22. SQL> Select * from MTL_LOT_NUMBERS where INVENTORY_ITEM_ID=&Item_id;  
23. INVENTORY_ITEM_ID=378856  
24. ORGANIZATION_ID=209  
25. LOT_NUMBER=B00406  
26.   
27. SQL> Select * from MTL_SUPPLY where PO_HEADER_ID=&Po_header_id;  
28. SUPPLY_TYPE_CODE=PO  
29. SUPPLY_SOURCE_ID=86928  
30. PO_HEADER_ID=44274  
31. PO_LINE_ID=50364  
32. PO_LINE_LOCATION_ID=85112  
33. PO_DISTRIBUTION_ID=86928  
34. ITEM_ID=378856  
35. QUANTITY=8  
36. UNIT_OF_MEASURE=Each  
37. DESTINATION_TYPE_CODE=INVENTORY  
38. TO_ORGANIZATION_ID=209