Tuesday, January 3, 2017

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’;

No comments:

Post a Comment

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...