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
=> OE_ORDER_HEADERS_ALL - Flow Status Code
- ENTERED
- CANCELLED
- CLOSED
- BOOKED
=> OE_ORDER_LINES_ALL - Flow Status Code
- ENTERED
- REPRICE_COMPLETE
- CANCELLED
- AWAITING_SHIPPING
- CLOSED
- BOOKED
- INVOICE_HOLD
- AWAITING_FULFILLMENT
- REPRICE_PRICING_ERROR
=> WSH_DELIVERY_DETAILS - Released Status
- B:Backordered
- C:Shipped
- D:Cancelled
- N:Not ready to release
- R:Ready to release
- S:Released to warehouse
- X:Not Applicable
- 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’;
you are posting a good information for people and keep maintin it and give more updates too.
ReplyDeleteseo company india
This comment has been removed by the author.
ReplyDelete