O2C Cycle with Tables

Entered the order
-----------------
select ORDER_NUMBER,CUST_PO_NUMBER,FLOW_STATUS_CODE,HEADER_ID from oe_order_headers_all where CUST_PO_NUMBER='123456789';

select CUST_PO_NUMBER,FLOW_STATUS_CODE,HEADER_ID from oe_order_LINEs_all where cust_po_number='123456789';

#FLOW_STATUS_CODE  --#HEADERS(Entered)
                   --#LINES(ENTERED)                 
#LINK ---oe_order_headers_all & oe_order_LINEs_all---header_id,CUST_PO_NUMBER

booking the order
----------------
select CUST_PO_NUMBER,FLOW_STATUS_CODE,HEADER_ID from oe_order_HEADERs_all where cust_po_number='123456789';

select CUST_PO_NUMBER,FLOW_STATUS_CODE,HEADER_ID,INVENTORY_ITEM_ID,ORDERED_ITEM_ID from oe_order_lines_all where cust_po_number='123456789';

select * from WSH_DELIVERY_DETAILS where SOURCE_HEADER_ID='987456'; --and SOURCE_HEADER_NUMBER='14123';---RELEASED_STATUS=='R'

select * from WSH_DELIVERY_ASSIGNMENTS where DELIVERY_DETAIL_ID='+++++';-- delivery id null

FLOW_STATUS_CODE  --#HEADERS(BOOKED)
                  --#LINES(AWAITING SHIPPING)    
                  
#LINK
oe_order_headers_all & oe_order_LINEs_all           ---header_id,CUST_PO_NUMBER
WSH_DELIVERY_DETAILS,oe_order_headers_all           ---SOURCE_HEADER_ID,(HEADER_ID) and order_number(SOURCE_HEADER_NUMBER)
WSH_DELIVERY_DETAILS & WSH_DELIVERY_ASSIGNMENTS     ----DELIVERY_DETAIL_ID

IMP NOTE: #TRIP SHOULD BE CREATE FROM SHIPPING TRANSACTIONS FORM BY ORDER_NUMBER

pick release the order
---------------------

-->Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.  

select CUST_PO_NUMBER,FLOW_STATUS_CODE,HEADER_ID from oe_order_HEADERs_all where cust_po_number='123456789';

select CUST_PO_NUMBER,FLOW_STATUS_CODE,HEADER_ID from oe_order_lines_all where cust_po_number='123456789';--NO CHANGE IN FLOW_STATUS_CODE

select * from WSH_DELIVERABLE_TRIPS_v where TRIP_ID='789456';--NAME,TRIP_ID ARE SAME

select RELEASED_STATUS  from WSH_DELIVERY_DETAILS where SOURCE_HEADER_ID='987456';--RELEASED_STATUS=='B'(Released to Warehouse)

select CUST_PO_NUMBER,FLOW_STATUS_CODE,HEADER_ID,INVENTORY_ITEM_ID from oe_order_lines_all where cust_po_number='123456789';

select * from WSH_DELIVERY_ASSIGNMENTS where DELIVERY_DETAIL_ID='+++++';--delivery id populated

select * from WSH_NEW_DELIVERIES where DELIVERY_ID='14123';--CREATION_DATE like sysdate;

select * from MTL_DEMAND where INVENTORY_ITEM_ID='5429593';--CREATION_DATE like sysdate;

LINK
----
1)WSH_DELIVERABLE_TRIPS_v,WSH_DELIVERY_DETAILS,WSH_DELIVERY_ASSIGNMENTS---DELIVERY_DETAIL_ID
2)WSH_NEW_DELIVERIES,WSH_DELIVERY_ASSIGNMENTS,WSH_DELIVERABLE_TRIPS_v  ---DELIVERY_ID

pick confirm the order
---------------------
-->Pick Confirm is to transact the move order created in Pick Release process.

-->WHEN WE ARE PICK RELEASING THE ORDER THERE IS AUTO PICK CONFIRM SET 'YES' THEN THE ORDER WILL PICK CONFIRM AUTOMATICALLY.

PICK CONFIRM --->'YES'

select RELEASED_STATUS  from WSH_DELIVERY_DETAILS where SOURCE_HEADER_ID='987456';--RELEASED_STATUS=='Y'(Pick Confirmed)
select CUST_PO_NUMBER,FLOW_STATUS_CODE,HEADER_ID from oe_order_LINEs_all where cust_po_number='123456789';--flow_status_code ='PICKED'

NOTE:CHECK THE TRACKING NUMBERS FOR THE TRIP(IF  THERE IS NO TRACKING NUMBERS PUT A TRACKING NUMBERS THROUGH BACKEND OR FRONTEND)


SELECT distinct ship_to_location_id,tracking_number FROM WSH_DELIVERY_DETAILS
WHERE DELIVERY_DETAIL_ID
IN(SELECT DELIVERY_DETAIL_ID FROM WSH_DELIVERABLE_TRIPS_V
WHERE NAME in('789456'));
--and ship_to_location_id='497308';
--and tracking_number='010008471144695';    
          
update WSH_DELIVERY_DETAILS set tracking_number='11112221Z1Z121565415641'
where ship_to_location_id in('865086'); 
select *  from WSH_DELIVERY_DETAILS where SOURCE_HEADER_ID='987456';

--important colums data
SELECT tracking_number,
  ship_to_location_id,
  DELIVERY_DETAIL_ID,
  SHIPPED_QUANTITY,
  DELIVERED_QUANTITY,
  REQUESTED_QUANTITY,
  SUBINVENTORY,
  ATTRIBUTE3 container,
  BATCH_ID
FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_HEADER_ID='987456';

select * from WSH_NEW_DELIVERIES where DELIVERY_ID='14123';--CREATION_DATE like sysdate;

select TRANSACTION_QUANTITY,SUBINVENTORY_CODE from mtl_onhand_quantities where INVENTORY_ITEM_ID='5429593';

select CUST_PO_NUMBER,FLOW_STATUS_CODE,HEADER_ID,ordered_quantity from oe_order_lines_all where HEADER_ID='987456';



SHIP CONFIRM THE ORDER
---------------------
The goods are picked from staging area and given to shipping. “Interface Trip Stop” program runs in the backend.

In shipping transaction form we have to ship confirm the order by trip

select RELEASED_STATUS  from WSH_DELIVERY_DETAILS where SOURCE_HEADER_ID='987456';--RELEASED_STATUS=='C'(Ship Confirmed(Closed))

select CUST_PO_NUMBER,FLOW_STATUS_CODE,HEADER_ID from oe_order_LINEs_all where Header_id ='987456';--FLOW_STATUS_CODE='SHIPPED'

select CUST_PO_NUMBER,FLOW_STATUS_CODE,HEADER_ID from oe_order_headers_all where Header_id ='987456';

select * from WSH_DELIVERABLE_TRIPS_v where TRIP_ID='789456';--STATUS_NAME='Closed' & STATUS_CODE='CL'

select TRANSACTION_QUANTITY,SUBINVENTORY_CODE from mtl_onhand_quantities where INVENTORY_ITEM_ID='5429593';--- quantity deducted

WSH_NEW_DELIVERIES---Data Deleted   
MTL_RESERVATIONS--Data Deleted   
MTL_DEMAND---Data Deleted  

--->now run Interface Trip Stop - SRS

navigation:OM-->shipping--interfaces—run

Creating invoices
-----------------
After shipping the order,the order lines gets eligible to get transfered to RA_INTERFACE_LINES_ALL.
Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL.  

-->run “ Workflow Background Process” program.
Parameters;
item type-om order line
process oreferred—yes
no
no
Navigation:OM-->interfaces--run

select * from RA_INTERFACE_LINES_ALL where interface_line_attribute1='14123';--interface_line_attribute1(order_number)

select invoice_interface_status_code  from OE_ORDER_LINES_ALL where header_id='987456';--invoice_interface_status_code(YES)

select RELEASED_STATUS  from WSH_DELIVERY_DETAILS where SOURCE_HEADER_ID='987456';--RELEASED_STATUS=='C'(Ship Confirmed(Closed))

--->run auto invoice master program(the program which sends the interface data to base tables)

Navigation: receivables manager-->interfaces-->Autoinvoice


SELECT * FROM RA_CUSTOMER_TRX_ALL where INTERFACE_HEADER_ATTRIBUTE1='14123';---TRX_NUMBER IS INVOICE
SELECT * FROM RA_CUSTOMER_TRX_lines_ALL where INTERFACE_line_ATTRIBUTE1='14123';

LINK
-----
RA_CUSTOMER_TRX_ALL,RA_CUSTOMER_TRX_lines_ALL---CUSTOMER_TRX_ID

---->Last step of the process is to close the order which happens automatically once the goods are shipped

select CUST_PO_NUMBER,FLOW_STATUS_CODE,HEADER_ID from oe_order_LINEs_all where header_id='987456';--FLOW_STATUS_CODE='CLOSED'
select CUST_PO_NUMBER,FLOW_STATUS_CODE,HEADER_ID from oe_order_headers_all where header_id='987456';


PO# 123456789
SO# 14123
Header_id #987456
delivery_id #14123
trip #789456
DELIVERY_DETAIL_ID #+++++
TRX_NUMBER #654321