SO到订单行Closed

--entered

SELECT * FROM inv.mtl_sales_orders WHERE segment1 = 1054627; --sales_order_id:935491

SELECT h.flow_status_code, h.* --header_id:6577988 --flow_status_code:entered 
  FROM ont.oe_order_headers_all h
 WHERE order_number = 1054627;

SELECT l.flow_status_code, l * --flow_status_code:entered            
  FROM ont.oe_order_lines_all l
 WHERE header_id = 6577988
   AND line_id = 15514837;

/*select *
  from inv.mtl_onhand_quantities_detail
 where inventory_item_id = 13139193
   and organization_id = 87;

select *
  from inv.mtl_system_items_b
 where segment1 = 'TCS_707534'
   and organization_id = 87;*/
--------------------------------------
--booked  Book之后产生发运明细记录，对应Shipping Transaction Form

SELECT h.flow_status_code, h.* --header_id:6577988 --flow_status_code:booked
  FROM ont.oe_order_headers_all h
 WHERE order_number = 1054627;

SELECT l.flow_status_code, l.* --flow_status_code:AWAITING_SHIPPING            
  FROM ont.oe_order_lines_all l
 WHERE header_id = 6577988
   AND line_id = 15514837;

SELECT * --delivery_detail_id:7661571  released_status:R (Ready to release)  Shipping Transaction Form
  FROM wsh.wsh_delivery_details
 WHERE source_header_id = 6577988
   AND source_line_id = 15514837;

SELECT *
  FROM wsh.wsh_delivery_assignments
 WHERE delivery_detail_id = 7661571;
---------------------------------------
--Released  Release之后产生Move Order和Pendding Transaction记录

SELECT h.flow_status_code, h.* --header_id:6577988 --flow_status_code:booked
  FROM ont.oe_order_headers_all h
 WHERE order_number = 1054627;

SELECT l.flow_status_code, l.* --flow_status_code:AWAITING_SHIPPING            
  FROM ont.oe_order_lines_all l
 WHERE header_id = 6577988
   AND line_id = 15514837;

SELECT d.released_status, d.* --delivery_detail_id:7661571  released_status:S (Released to Warehouse)  Shipping Transaction Form
  FROM wsh.wsh_delivery_details d
 WHERE source_header_id = 6577988
   AND source_line_id = 15514837;

SELECT *
  FROM wsh.wsh_delivery_assignments
 WHERE delivery_detail_id = 7661571;

SELECT * --line_status:7
  FROM inv.mtl_txn_request_lines
 WHERE txn_source_id = 935491
   AND txn_source_line_id = 15514837;

SELECT * FROM inv.mtl_txn_request_headers WHERE header_id = 7098004; --request_number:7097984 header_status:7

SELECT * FROM wsh.wsh_picking_batches WHERE order_header_id = 6577988;

SELECT * --transaction_temp_id:119820378  will be the key of MMT, move_order_line_id:126685846 move_order_header_id:7098004
  FROM mtl_material_transactions_temp
 WHERE transaction_source_id = 935491
   AND trx_source_line_id = 15514837;
------------------------------------------
--picked  Pick之后，Move Order和Pendding Transaction结束

SELECT h.flow_status_code, h.* --header_id:6577988 --flow_status_code:booked
  FROM ont.oe_order_headers_all h
 WHERE order_number = 1054627;

SELECT l.flow_status_code, l.* --flow_status_code:AWAITING_SHIPPING            
  FROM ont.oe_order_lines_all l
 WHERE header_id = 6577988
   AND line_id = 15514837;

SELECT d.released_status, d.* --released_status:Y (Staged)  Shipping Transaction Form
  FROM wsh.wsh_delivery_details d
 WHERE source_header_id = 6577988
   AND source_line_id = 15514837;

SELECT *
  FROM wsh.wsh_delivery_assignments
 WHERE delivery_detail_id = 7661571;

SELECT * --line_status:5
  FROM inv.mtl_txn_request_lines
 WHERE txn_source_id = 935491
   AND txn_source_line_id = 15514837;

SELECT * FROM inv.mtl_txn_request_headers WHERE header_id = 7098004; --request_number:7097984 header_status:7

/*SELECT * FROM wsh.wsh_picking_batches WHERE order_header_id = 6577988;*/

SELECT * -- o rows selected
  FROM mtl_material_transactions_temp
 WHERE transaction_source_id = 935491
   AND trx_source_line_id = 15514837;

SELECT *
  FROM inv.mtl_material_transactions
 WHERE transaction_source_id = 935491 --sales_order_id
   AND trx_source_line_id = 15514837; --line_id

SELECT *
  FROM inv.mtl_onhand_quantities_detail
 WHERE inventory_item_id = 13139193
   AND organization_id = 87;

/*SELECT *
  FROM inv.mtl_transaction_lot_numbers
 WHERE transaction_id = mtl_material_transactions.transaction_id
 lot_number = mtl_onhand_quantities.lot_number;*/

/*SELECT *
  FROM inv.mtl_serial_numbers
 WHERE last_txn_source_id = mtl_material_transactions.transaction_source_id;*/

-----------------------------------------
--autocreate deliveries 产生交货单，wsh_new_deliveries,对应Shipping Transaction Form第二个Tab

SELECT h.flow_status_code, h.* --header_id:6577988 --flow_status_code:booked
  FROM ont.oe_order_headers_all h
 WHERE order_number = 1054627;

SELECT l.flow_status_code, l.* --flow_status_code:AWAITING_SHIPPING            
  FROM ont.oe_order_lines_all l
 WHERE header_id = 6577988
   AND line_id = 15514837;

SELECT d.released_status, d.* --released_status:Y (Staged)  Shipping Transaction Form
  FROM wsh.wsh_delivery_details d
 WHERE source_header_id = 6577988
   AND source_line_id = 15514837;

SELECT wnd.status_code, wnd.* --delivery_id:9731514  status_code:OP(Delivery is Open, has not been shipped)
  FROM wsh.wsh_new_deliveries wnd
 WHERE source_header_id = 6577988;

SELECT *
  FROM wsh.wsh_delivery_assignments
 WHERE delivery_detail_id = 7661571
   AND delivery_id = 9731514;
---------------------------------------------
--ship confirm then run Interface Trip Stop to transfer OM infomation to INV and AR

SELECT h.flow_status_code, h.* --header_id:6577988 --flow_status_code:booked
  FROM ont.oe_order_headers_all h
 WHERE order_number = 1054627;

SELECT l.flow_status_code, l.* --flow_status_code:SHIPPED            
  FROM ont.oe_order_lines_all l
 WHERE header_id = 6577988
   AND line_id = 15514837;

SELECT d.released_status, d.* --released_status:C (Shipped)  Shipping Transaction Form
  FROM wsh.wsh_delivery_details d
 WHERE source_header_id = 6577988
   AND source_line_id = 15514837;

SELECT wnd.status_code, wnd.* --status_code:CL(Delivery has arrived at the destination)
  FROM wsh.wsh_new_deliveries wnd
 WHERE source_header_id = 6577988;

SELECT *
  FROM wsh.wsh_delivery_assignments
 WHERE delivery_detail_id = 7661571
   AND delivery_id = 9731514;

SELECT * --transaction_type_id:52 52 33
  FROM inv.mtl_material_transactions
 WHERE transaction_source_id = 935491 --sales_order_id
   AND trx_source_line_id = 15514837; --line_id

SELECT * --Sales Order Pick,Sales Order Pick,Sales order issue
  FROM inv.mtl_transaction_types
 WHERE transaction_type_id IN (52, 33);

SELECT *
  FROM inv.mtl_onhand_quantities_detail
 WHERE inventory_item_id = 13139193
   AND organization_id = 87;

---------------------------------------------
--System Administrator/Requests/Workflow Background Process

SELECT h.flow_status_code, h.* --header_id:6577988 --flow_status_code:booked
  FROM ont.oe_order_headers_all h
 WHERE order_number = 1054627;

SELECT l.flow_status_code, l.* --flow_status_code:CLOSED           
  FROM ont.oe_order_lines_all l
 WHERE header_id = 6577988
   AND line_id = 15514837;

--------------------------------------------
--AR/Requests/Run/Autoinvoice Import Program
SELECT *
  FROM ra_interface_lines_all
 WHERE trunc(SYSDATE) = trunc(creation_date);

ont.oe_order_headers_all

ont.oe_order_lines_all

wsh.wsh_delivery_details

wsh.wsh_delivery_assignments

inv.mtl_txn_request_headers

inv.mtl_txn_request_lines

inv.mtl_material_transactions_temp

inv.mtl_material_transactions

wsh.wsh_new_deliveries

inv.mtl_transaction_types

inv.mtl_onhand_quantities_detail

inv.mtl_system_items_b