ORDER TO CASH (O2C) Process Flow with Effected Tables in ORACLE EBS R12

LET LOOK the FLOW PART FOR O2C 

• Order  Entry : This is first stage , When the order is entered in the system 
• Order Booking: This is next stage , When the order is booked then the flow status changed from entered into Booked
• Pick Release : Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick then particular sales order 
• Pick confirm / Move order Transaction : Items transferred from Source sub inventory into staging sub inventory
• Ship Confirm: Items are loaded in Truck / Transportation Mode for the delivery to customers 
• Invoice Generation : Invoice is generated
• Close Order 

O2C - TABLE FLOW STATUS 

=> OE_ORDER_HEADERS_ALL - Flow Status Code

1. ENTERED
2. CANCELLED
3. CLOSED
4. BOOKED

=> OE_ORDER_LINES_ALL - Flow Status Code

1. ENTERED
2. REPRICE_COMPLETE
3. CANCELLED
4. AWAITING_SHIPPING
5. CLOSED
6. BOOKED
7. INVOICE_HOLD
8. AWAITING_FULFILLMENT
9. REPRICE_PRICING_ERROR

=> WSH_DELIVERY_DETAILS - Released Status

1. B:Backordered
2. C:Shipped 
3. D:Cancelled 
4. N:Not ready to release 
5. R:Ready to release 
6. S:Released to warehouse 
7. X:Not Applicable 
8. Y:Staged

Step 1; Creating / Entered New Sales Order 

Select * from OE_ORDER_HEADERS_ALL where  ORDER_NUMBER = '830001659'; 

select * from OE_ORDER_HEADERS_ALL where Header_ID = 21009209;

select Flow_status_code from OE_order_Headers_all 

                                where Header_ID = 21009209; -->ENTERED STATUS

select Flow_status_code from OE_ORDER_LINES_ALL 

                               where Header_ID = oe_header_id -->ENTERED 

Step 2; Booked the Sales Order 

Once Order is booked or confirmed then 

select Flow_status_code from OE_order_Headers_all 

                                where Header_ID = 21009209; -->BOOKED STATUS

select Flow_status_code from OE_ORDER_LINES_ALL 

                               where Header_ID = oe_header_id -->AWAITING_SHIPPING

select * from WSH_DELIVERY_DETAILS

                                 where source_header_ID =   OE_HEADER_ID  

select released_status from WSH_DELIVERY_DETAILS

                              where source_header_ID =   OE_HEADER_ID  --> R - READY To RELEASE

Step 3; Release Sales order / Pick Release , Pick Confirm (Staging Location)

Once Release order and pick release some concurrent program trigger out 

1. Pick Selection List Generation 

2. Pick Slip Report ( We can get Move Order Number) 

Select * from MTL_TXN_REQUEST_HEADERS 

            where Request_Number= '<<Move Oder Number(getting from PICKSLIP Report)>>';

Select * from MTL_TXN_REQUEST_LINES where HEADER_ID = '<<Header_ID>>';

Select * from WSH_DELIVERY_DETAILS 

               where Released_Status = 'S';  -- Released to warehouse (Pick Release)

Select * from WSH_DELIVERY_DETAILS

           where Released_Status = 'Y'; -- Staging 

(Pick Confirm , Line has been released to inventory for Processing)

Select * from WSH_picking_batches; --- After batch is created for pick release.

Select * from MTL_Reservations 

             where Inventory_Item_ID = '<<MTL_ITEM_ID>>'; -- Soft move , Not for physical Move

select * From WSH_NEW_DELIVERIES 

             where Deliverd_ID = '<<Delivery_ID- wsh_delivery_assignments>>';

Step 4; Ship Confirm

once ship confirmation done, then it will be triggered out 5 concurrent programs 

1. Bill of Landing , 

2. packing Slip Report , 

3. Commerical Invoice ,

4. Vechile Load Sheet , 

5. Interface Trip stop 

Select * from WSH_DELIVERY_DETAILS where Released_Status = 'C';  -- Shipped

Select flow_status_Code from OE_ORDER_LINES_ALL 

                                       where header_ID = 'Oe_header_ID';  --> Shipped Status

Select * FROM MTL_REVERSATIONS 

               WHERE Inventory_Item_ID = 'ITEM_ID'; --> will be FREEZE

Step 5 : Auto Invoice: 

After Successful completed the respective CC program then run the work flow background process

to Move AR module and Generate Invoice 

 -- >>  RUN WorkFlow - 'WORK FLOW BACKGROUND PROCESS ' INTERFACE 

 --.>>  Auto Invoice Program 

  select * from RA_Interface_lines_all 

                 where Interface_LINE_ATTRIBUTE1 ='ORDER_NUMBER';

  Select * from RA_Interface_Distributions_all 

                  where Interface_Line_ID  ='LINE ID'; 

Step 6 : Account Receivable:

   Select * FROM RA_CUSTOMER_TRX_ALL 

                   Where TRX_NUMBER = 'Invoice_number';

   Select * FROM RA_CUSTOMER_TRX_lines_All 

                   where Customer_Trx_ID = '';

Step 7 : General Ledger:

  Select * From GL_JE_BATCHES Where Name ='Receivables A 155 etc';

  Select * From GL_JE_Headers where JE_Batch_ID ='';

  Select * from GL_JE_LInes where JE_Header_ID = '';

SAMPLE QUERY FOR JOIN BETWEEN  OM, WSH, AR TABLES

SELECT ooh.order_number ,

  ool.line_id ,

  ool.ordered_quantity ,

  ool.shipped_quantity ,

  ool.invoiced_quantity ,

  wdd.delivery_detail_id ,

  wnd.delivery_id ,

  rctl.interface_line_attribute1 ,

  rctl.interface_line_attribute3 ,

  rctl.interface_line_attribute6 ,

  rct.org_id ,

  rct.creation_date ,

  trx_number ,

  rctl.quantity_ordered ,

  rct.interface_header_context

FROM oe_order_headers_all ooh ,

  oe_order_lines_all ool ,

  wsh_delivery_details wdd ,

  wsh_new_deliveries wnd ,

  wsh_delivery_assignments wda ,

  ra_customer_trx_all rct ,

  ra_customer_trx_lines_all rctl

WHERE ooh.header_Id               =ool.header_id

AND wdd.source_header_id          =ooh.header_id

AND wdd.delivery_detail_Id        =wda.delivery_detail_id

AND wda.delivery_id               =wnd.delivery_id

AND rctl.interface_line_attribute1=TO_CHAR(ooh.order_number)

AND rctl.interface_line_attribute6=TO_CHAR(ool.line_id)

AND rctl.interface_line_attribute3=TO_CHAR(wnd.delivery_id)

AND rctl.customer_trx_id          =rct.customer_trx_id;

--      AND rct.interface_header_context='ORDER ENTRY'

/

Example query linking MTL_MATERIAL_TRANSACTIONS to the move order:

SELECT mmt.transaction_id,

  tol.organization_id,

  toh.request_number,

  toh.header_id,

  tol.line_number,

  tol.line_id,

  tol.inventory_item_id,

  toh.description,

  toh.move_order_type,

  tol.line_status,

  tol.quantity,

  tol.quantity_delivered,

  tol.quantity_detailed

FROM mtl_txn_request_headers toh,

  mtl_txn_request_lines tol,

  mtl_material_transactions mmt

WHERE toh.header_id     = tol.header_id

AND toh.organization_id = tol.organization_id

AND tol.line_id         = mmt.move_order_line_id

AND toh.request_number  = '&EnterMONumber' 

/

SQL Statement to find out the Drop Ship SO and corresponding Requistion and PO details along with their status

select ooh.order_number

       ,ool.ordered_item

       ,ool.ordered_quantity

       ,ooh.flow_status_code header_status                   

       ,ool.flow_status_code line_status

       ,prha.segment1 requisition

       ,poh.segment1 po_number

       ,poh.closed_code po_status

       ,pll.quantity

       ,pll.quantity_received

       ,pll.closed_code po_shipment_status

fromapps.oe_order_headers_all                   ooh

    ,apps.oe_order_lines_all                     ool

    ,apps.oe_drop_ship_sources                   odss

    ,apps.po_requisition_headers_all             prha

    ,apps.po_headers_all                         poh

    ,apps.po_lines_all                           pol

    ,apps.po_line_locations_all                  pll

              

whereool.header_id = ooh.header_id

and   odss.header_id = ooh.header_id

and   odss.line_id = ool.line_id

and   prha.requisition_header_id = odss.requisition_header_id

and   poh.po_header_id = odss.po_header_id

and   pol.po_line_id = odss.po_line_id

and   pol.po_header_id = poh.po_header_id

and   pll.po_line_id = pol.po_line_id

and   ooh.order_number = ‘89899’;