1, Sales Order: the line status how to changed from booked to closed
1,when you entered the SO, there will be 3 records applied and saved. example as below:
1. SELECT * FROM inv.mtl_sales_orders WHERE segment1 = 1054627; --sales_order_id:935491
2.
3. SELECT h.flow_status_code, h.* --header_id:6577988 --flow_status_code:entered
4. FROM ont.oe_order_headers_all h
5. WHERE order_number = 1054627;
6.
7. SELECT l.flow_status_code, l * --flow_status_code:entered
8. FROM ont.oe_order_lines_all l
9. WHERE header_id = 6577988
10. AND line_id = 15514837;
2,when you booked the SO, the header workflow status of the Sales Order would be changed to booked, the line workflow status of the Sales Order would be changed to awaiting shipping and there will be 2 records applied and saved about delivery details:
1. --booked Book之后产生发运明细记录，对应Shipping Transaction Form
2.
3. SELECT h.flow_status_code, h.* --header_id:6577988 --flow_status_code:booked
4. FROM ont.oe_order_headers_all h
5. WHERE order_number = 1054627;
6.
7. SELECT l.flow_status_code, l.* --flow_status_code:AWAITING_SHIPPING
8. FROM ont.oe_order_lines_all l
9. WHERE header_id = 6577988
10. AND line_id = 15514837;
11.
12. SELECT * --delivery_detail_id:7661571 released_status:R (Ready to release) Shipping Transaction Form
13. FROM wsh.wsh_delivery_details
14. WHERE source_header_id = 6577988
15. AND source_line_id = 15514837;
16.
17. SELECT *
18. FROM wsh.wsh_delivery_assignments
19. WHERE delivery_detail_id = 7661571;
Note: the wsh_delivery_assignments table will make relationship between wsh_delivery_details and wsh_new_deliverys
3,when you released the SO, the status of wsh_delivery_details would be changed from R(Ready to Release) to S(Released to Warehouse) and 3 new records of MO and transaction temp would be created:
1. SELECT d.released_status, d.* --delivery_detail_id:7661571 released_status:S (Released to Warehouse) Shipping Transaction Form
2. FROM wsh.wsh_delivery_details d
3. WHERE source_header_id = 6577988
4. AND source_line_id = 15514837;
5.
6. SELECT *
7. FROM wsh.wsh_delivery_assignments
8. WHERE delivery_detail_id = 7661571;
9.
10. SELECT * --line_status:7
11. FROM inv.mtl_txn_request_lines
12. WHERE txn_source_id = 935491
13. AND txn_source_line_id = 15514837;
14.
15. SELECT * FROM inv.mtl_txn_request_headers WHERE header_id = 7098004; --request_number:7097984 header_status:7
16.
17. SELECT * --transaction_temp_id:119820378 will be the key of MMT, move_order_line_id:126685846 move_order_header_id:7098004
18. FROM mtl_material_transactions_temp
19. WHERE transaction_source_id = 935491
20. AND trx_source_line_id = 15514837;
4, when you complete the SO Picking, the released status would be changed to Y(staged) and the line status of the MO would be changed from 7(Pre-approval) to 5(closed) and Pending Transaction (mtl_material_transaction_temp)will be null:
1. SELECT d.released_status, d.* --released_status:Y (Staged) Shipping Transaction Form
2. FROM wsh.wsh_delivery_details d
3. WHERE source_header_id = 6577988
4. AND source_line_id = 15514837;
5.
6. SELECT *
7. FROM wsh.wsh_delivery_assignments
8. WHERE delivery_detail_id = 7661571;
9.
10. SELECT * --line_status:5
11. FROM inv.mtl_txn_request_lines
12. WHERE txn_source_id = 935491
13. AND txn_source_line_id = 15514837;
14.
15. SELECT * FROM inv.mtl_txn_request_headers WHERE header_id = 7098004; --request_number:7097984 header_status:7
16.
17.
18.
19. SELECT * -- o rows selected
20. FROM mtl_material_transactions_temp
21. WHERE transaction_source_id = 935491
22. AND trx_source_line_id = 15514837;
23.
24. SELECT *
25. FROM inv.mtl_material_transactions
26. WHERE transaction_source_id = 935491 --sales_order_id
27. AND trx_source_line_id = 15514837; --line_id
5, when you created new deliveries(wsh_new_deliverys) using the button of ‘autocreate deliveries’, there would be one record applied and saved in the wsh_new_deliverys table with status OP(Delivery is Open):
1. SELECT wnd.status_code, wnd.* --delivery_id:9731514 status_code:OP(Delivery is Open, has not been shipped)
2. FROM wsh.wsh_new_deliveries wnd
3. WHERE source_header_id = 6577988;
4.
5. SELECT *
6. FROM wsh.wsh_delivery_assignments
7. WHERE delivery_detail_id = 7661571
8. AND delivery_id = 9731514;
6, when you confirmed the shipping using the button of ‘ship confirm’ and then run the ‘interface trip stop’ to transfer the OM information to INV and AR, the line workflow status of the SO would be changed to shipped, the released status would be changed to C(shipped), the status code of wsh_new_deliverys would be changed from OP(delivery is open) to CL(delivery has arrived at destination):
1. SELECT h.flow_status_code, h.* --header_id:6577988 --flow_status_code:booked
2. FROM ont.oe_order_headers_all h
3. WHERE order_number = 1054627;
4.
5. SELECT l.flow_status_code, l.* --flow_status_code:SHIPPED
6. FROM ont.oe_order_lines_all l
7. WHERE header_id = 6577988
8. AND line_id = 15514837;
9.
10. SELECT d.released_status, d.* --released_status:C (Shipped) Shipping Transaction Form
11. FROM wsh.wsh_delivery_details d
12. WHERE source_header_id = 6577988
13. AND source_line_id = 15514837;
14.
15. SELECT wnd.status_code, wnd.* --status_code:CL(Delivery has arrived at the destination)
16. FROM wsh.wsh_new_deliveries wnd
17. WHERE source_header_id = 6577988;
18.
19. SELECT *
20. FROM wsh.wsh_delivery_assignments
21. WHERE delivery_detail_id = 7661571
22. AND delivery_id = 9731514;
There were 3 material transactions occurred in the process, Sales Order Pick, Sales Order Pick and Sales Order Issue:
1. SELECT * --transaction_type_id:52 52 33
2. FROM inv.mtl_material_transactions
3. WHERE transaction_source_id = 935491 --sales_order_id
4. AND trx_source_line_id = 15514837; --line_id
5.
6. SELECT * --Sales Order Pick,Sales Order Pick,Sales order issue
7. FROM inv.mtl_transaction_types
8. WHERE transaction_type_id IN (52, 33);
7, finally ran the request ’Workflow Background Process’ to complete the workflow, the line workflow status of the SO would be changed to closed:
1. --System Administrator/Requests/Workflow Background Process
2.
3. SELECT h.flow_status_code, h.* --header_id:6577988 --flow_status_code:booked
4. FROM ont.oe_order_headers_all h
5. WHERE order_number = 1054627;
6.
7. SELECT l.flow_status_code, l.* --flow_status_code:CLOSED
8. FROM ont.oe_order_lines_all l
9. WHERE header_id = 6577988
10. AND line_id = 15514837;
8, the table related in this process:
1. ont.oe_order_headers_all
2.
3. ont.oe_order_lines_all
4.
5. wsh.wsh_delivery_details
6.
7. wsh.wsh_delivery_assignments
8.
9. inv.mtl_txn_request_headers
10.
11. inv.mtl_txn_request_lines
12.
13. inv.mtl_material_transactions_temp
14.
15. inv.mtl_material_transactions
16.
17. wsh.wsh_new_deliveries
18.
19. inv.mtl_transaction_types
20.
21. inv.mtl_onhand_quantities_detail
22.
23. inv.mtl_system_items_b
2, Purchase Order: the information of POR how to flow
1, when you created a POR and saved, there would be 3 records applied and saved:
1. --POR entered --3 records applied and saved
2. SELECT rh.authorization_status,
3. rh.requisition_header_id,
4. rh.*
5. FROM po_requisition_headers_all rh
6. WHERE segment1 = '1952058'
7. AND org_id = 84;
8. --authorization_status:INCOMPLETE
9.
10. SELECT *
11. FROM po_requisition_lines_all
12. WHERE requisition_header_id = 4327744
13. AND requisition_line_id = 4409451;
14.
15. SELECT d.distribution_id,
16. d.*
17. FROM po_req_distributions_all d
18. WHERE requisition_line_id = 4409451;
19. --distribution_id:4455570
2, submitted this POR and gone to ‘Workflow User’ to approve the POR and then you could use this POR to create a PO, there would be 4 records applied and saved:
1. --PO approved --4 records applied and saved
2. SELECT h.wf_item_type,
3. h.wf_item_key,
4. h.authorization_status,
5. h.type_lookup_code,
6. h.closed_code,
7. h.org_id,
8. h.*
9. FROM po_headers_all h
10. WHERE segment1 = '439825775';
11. --po_header_id = 1015429 Authorization_status:APPROVED
12.
13. SELECT l.po_line_id,
14. l.closed_code,
15. l.*
16. FROM po_lines_all l
17. WHERE po_header_id = 1015429;
18. --po_line_id = 8971344
19.
20. SELECT loc.line_location_id,
21. loc.ship_to_organization_id,
22. loc.approved_flag,
23. loc.inspection_required_flag,
24. receipt_required_flag,
25. loc.closed_code,
26. loc.accrue_on_receipt_flag,
27. loc.*
28. FROM po_line_locations_all loc
29. WHERE po_line_id = 8971344;
30. --Line_location_id:14293816
31. --Approved_Flag:Y
32. --Receipt_required_flag=Y
33. --Closed_code:OPEN
34. --accrue_on_receipt_flag:Y
35.
36. SELECT dis.po_distribution_id,
37. dis.code_combination_id,
38. dis.destination_type_code,
39. dis.*
40. FROM po_distributions_all dis
41. WHERE line_location_id = 14293816;
42. --po_distribution_id:14224451
43. --Code_combination_id:1209
44. --Destination_type_code:INVENTORY
3, when the PO was approved, you did ‘PO receipts’, there would be 2 records applied and saved and there would be a new receiving transaction and a new Move Order sourced from the ‘Receipts’:
1. --PO receipts --2 records applied and saved
2.
3. SELECT * --shipment_line_id:9655858
4. FROM rcv_shipment_lines
5. WHERE po_header_id = 1015429
6. AND po_line_id = 8971344
7. AND po_line_location_id = 14293816;
8. --shipment_line_id:9655858
9. --shipment_header_id = 4081557
10.
11. SELECT * FROM rcv_shipment_headers WHERE shipment_header_id = 4081557;
12.
13. SELECT rt.transaction_type,
14. rt.interface_source_code,
15. rt.source_document_code,
16. rt.destination_type_code,
17. rt.inspection_status_code,
18. rt.*
19. FROM rcv_transactions rt
20. WHERE po_header_id = 1015429
21. AND po_line_id = 8971344
22. AND po_line_location_id = 14293816
23. AND shipment_line_id = 9655858
24. AND shipment_header_id = 4081557;
25. --transaction_id = 16396290
26. --transaction_type:RECEIVE
27. --destination_type_code:RECEIVING
28.
29. SELECT mo.txn_source_id, --PO Receipts: rcv_transactions.transaction_id:16396290
30. mo.reference, --PO_LINE_LOCATION_ID
31. mo.reference_id, --po_line_location_id:14293816
32. mo.*
33. FROM inv.mtl_txn_request_lines mo
34. WHERE inventory_item_id = 13139193;
35. --header_id:7098464
36.
37. SELECT * FROM inv.mtl_txn_request_headers WHERE header_id = 7098464;
4, when you did ‘delivery receiving transaction’, there would be one new record in rcv_transactions, and a new records in mtl_material_transactions when you completed the MO:
1. --PO receiving transactions(DELIVER) --1 records applied and saved
2.
3. SELECT rt.transaction_type,
4. rt.interface_source_code,
5. rt.source_document_code,
6. rt.destination_type_code,
7. rt.inspection_status_code,
8. rt.subinventory,
9. rt.transfer_lpn_id,
10. rt.*
11. FROM rcv_transactions rt
12. WHERE po_header_id = 1015429
13. AND po_line_id = 8971344
14. AND po_line_location_id = 14293816
15. AND shipment_line_id = 9655858
16. AND shipment_header_id = 4081557
17. AND po_distribution_id = 14224451;
18. ---transaction_id = 16396310
19. --transaction_type = DELIVER
20. --destination_type_code = INVENTORY
21.
22. SELECT mmt.source_code, --RCV
23. mmt.source_line_id, --rcv_transaction_id:16396310 接受阶段的接受事务处理id
24. transaction_source_id, --po_header_id:1015429
25. mmt.*
26. FROM inv.mtl_material_transactions mmt
27. WHERE rcv_transaction_id = 16396310
28. AND transaction_source_id = 1015429; --po_header_id
29. --Transaction Type:PO Receipt
30. --Transaction Action:Receipt into stores
31. --Inventory_item_id:13139193
32.
33. SELECT mo.txn_source_id, --PO Receipts: rcv_transactions.transaction_id:16396290
34. mo.reference, --PO_LINE_LOCATION_ID
35. mo.reference_id, --po_line_location_id:14293816
36. mo.*
37. FROM inv.mtl_txn_request_lines mo
38. WHERE txn_source_id = 16396290;
39. --header_id:7098464
40.
41. SELECT * FROM inv.mtl_txn_request_headers WHERE header_id = 7098464;
5, there were two types Returns, ‘return to receiving’ and ‘return to supplier’, now let’s tried ‘RTV’, first, you should do ‘return to receiving’:
1. SELECT rt.transaction_type,
2. rt.interface_source_code,
3. rt.source_document_code,
4. rt.destination_type_code,
5. rt.inspection_status_code,
6. rt.subinventory,
7. rt.transfer_lpn_id,
8. rt.*
9. FROM rcv_transactions rt
10. WHERE po_header_id = 1015429
11. AND po_line_id = 8971344
12. AND po_line_location_id = 14293816
13. AND shipment_line_id = 9655858
14. AND shipment_header_id = 4081557
15. AND po_distribution_id = 14224451
16. AND interface_source_code IS NULL;
17. --Transactiion_type:RETURN TO RECEIVING
18. --Source_document_code:PO
19. --Destination_type_code = INVENTORY
20. --Transaction_id:16396330
21.
22. SELECT mmt.source_code, --RCV
23. mmt.source_line_id, --rcv_transaction_id:16396330
24. transaction_source_id, --po_header_id:1015429
25. mmt.*
26. FROM inv.mtl_material_transactions mmt
27. WHERE rcv_transaction_id = 16396330
28. AND transaction_source_id = 1015429; --po_header_id
29. --Transaction Type: Return to Vendor
30. --Transaction Action: Issue from stores
31. --Inventory_item_id: 13139193
32.
33. --0 rows selected
34. SELECT mo.txn_source_id, --PO Receipts: rcv_transactions.transaction_id:16396290
35. mo.*
36. FROM inv.mtl_txn_request_lines mo
37. WHERE txn_source_id = 16396330;
6, then did return to supplier:
1. SELECT rt.transaction_type,
2. rt.interface_source_code,
3. rt.source_document_code,
4. rt.destination_type_code,
5. rt.inspection_status_code,
6. rt.subinventory,
7. rt.transfer_lpn_id,
8. rt.*
9. FROM rcv_transactions rt
10. WHERE po_header_id = 1015429
11. AND po_line_id = 8971344
12. AND po_line_location_id = 14293816
13. AND shipment_line_id = 9655858
14. AND shipment_header_id = 4081557
15. --AND po_distribution_id = 14224451
16. AND interface_source_code IS NULL;
17. --Transactiion_type:RETURN TO VENDOR
18. --Source_document_code:PO
19. --Destination_type_code = RECEIVING
20. --transaction_id:16396350
21.
22. --0 rows selected
23. SELECT mo.txn_source_id, --PO Receipts: rcv_transactions.transaction_id
24. mo.*
25. FROM inv.mtl_txn_request_lines mo
26. WHERE txn_source_id = 16396350;
7,check the ledger in the rcv_transaction_sub_ledger:
1. SELECT *
2. FROM rcv_receiving_sub_ledger
3. WHERE rcv_transaction_id IN (16396290, 16396350);
4. --RECEIVING, RETURN TO VENDOR
8,related tables in this process:
1. po_requisition_headers_all
2.
3. po_requisition_lines_all
4.
5. po_req_distributions_all
6.
7. po_headers_all
8.
9. po_lines_all
10.
11. po_line_locations_all
12.
13. po_distributions_all
14.
15. po_releases_all
16.
17. rcv_shipment_lines
18.
19. rcv_shipment_headers
20.
21. rcv_transactions
22.
23. mtl_txn_request_lines
24.
25. mtl_txn_request_headers
26.
27. mtl_material_transactions_temp
28.
29. mtl_material_transactions
30.
31. mtl_onhand_quantities_detail
32.
33. rcv_receiving_sub_ledger
![Oracle EBS开发查找数据源（续）[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)