Oracle Order to Cash
1. Order Entry
This is first stage, When the order is entered in the system, it creates a record in order headers and Order Lines table.
* Enter header details: Once you enter details on the order header and save it or move it to lines, record goes to one table oe_order_headers_all flow_status_code = ENTERED, booked_flag = N), Primary key=HEADER_ID
o No record exist in any other table for this order till now.
* Enter Line details for this order: Enter different item numbers, quantity and other details in line tab. When the record gets saved, it goes to one table. Order header details will be linked with line details by order HEADER_ID. oe_order_lines_all (flow_status_code = ENTERED, booked_flag = N, open_flag = Y) Primary key= LINE_ID
2.Order Booking
This is next stage, when Order is booked then the Flow status changed from Entered to Booked. At this stage, these below table get affected.
* oe_order_headers_alL
* oe_order_lines_all
* wsh_delivery_details
* wsh_delivery_assignments
*In shipping transaction form order status remains "Ready to Release".
At the same time, Demand interface program runs in background And insert into inventory tables mtl_demand, here LINE_ID come as a reference in DEMAND_SOURCE_LINE
3. Reservation
This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved. Once this program get successfully get completed, the mtl_demand and mtl_reservations table get updated. LINE_ID gets updated in DEMAND_SOURCE_LINE_ID in both the tables.
4. Pick Release
Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.
Pick release can be done from 'Release Sales Order' form or 'Pick release SRS' program can be scheduled in background. In both of these cases all lines of the order gets pick released depending on the Picking rule used. If specific line/s needs to be pick release it can be done from 'Shipping Transaction form. For this case Pick Release is done from 'Release Sales Order' form with Pick Confirm=NO.
Once pick release is done these are the tables get affected:
* If step 3 is not done then MTL_RESERVATIONS gets updated now.
* wsh_new_deliveries
* wsh_delivery_assignments
* wsh_delivery_details
* MTL_TXN_REQUEST_HEADERS
* MTL_TXN_REQUEST_LINES (LINE_ID goes as TXN_SOURCE_LINE_ID)
* (move order tables. Here request is generated to move item from Source (RM or FG) sub-inventory to staging sub-inventory)
* Mtl_material_transactions_temp
* MTL_SERIAL_NUMBERS_TEMP
* MTL_SERIAL_NUMBERS
*In shipping transaction form order status remains "Released to Warehouse" and all the material still remains in source sub-inventory. We need to do Move Order Transaction for this order. Till this no material transaction has been posted to MTL_MATERIAL_TRANSACTIONS
5.Pick Confirm/ Move Order Transaction
Items are transferred from source sub-inventory to staging Sub-inventory. Here material transaction occurs.
Order line status becomes 'Picked' on Sales Order and 'Staged/Pick Confirmed' on Shipping Transaction Form.
* MTL_MATERIAL_TRANSACTIONS_TEMP
* oe_order_lines_all
* MTL_MATERIAL_TRANSACTIONS
* mtl_transaction_accounts
* wsh_delivery_details
* wsh_delivery_assignments
* MTL_ONHAND_QUANTITIES
* MTL_SERIAL_NUMBERS_TEMP
* MTL_SERIAL_NUMBERS
* This step can be eliminated if we set Pick Confirm=YES at the time of Pick Release
6.Ship Confirm
Here ship confirm interface program runs in background. Data removed from wsh_new_deliveries.
The items on the delivery gets shipped to customer at this stage.
* oe_order_lines_all
* wsh_delivery_details
* WSH_SERIAL_NUMBERS
* mtl_transaction_interface
* mtl_material_TRANSACTIONS
* mtl_transaction_accounts
* Data deleted from mtl_demand, MTL_reservations
* Item deducted from MTL_ONHAND_QUANTITIES
* MTL_SERIAL_NUMBERS_TEMP
* MTL_SERIAL_NUMBERS
7.Enter Invoice
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. This is also called Receivables interface, that mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables. At the same time records also goes in the table RA_INTERFACE_SALESCREDITS_ALL which hold details of sales credit for the particular order.
ra_interface_lines_all (interface table into which the data is transferred from order management) Then Autoinvoice program imports data from this table which get affected into this stage are receivables base table. At the same time records goes in
ra_customer_trx_all
ra_customer_trx_lines_all
8.Complete Line
In this stage order line level table get updated with Flow status and open flag.
oe_order_lines_all (flow_status_code ‘shipped’, open_flag “N”)
9.Close Order
This is last step of Order Processing. In this stage only oe_order_lines_all table get updated. These are the table get affected in this step.
oe_order_lines_all
oe_order_HEADERS_all
DROP SHIP FLOW
1 Order Entry Here the activity is entering process where oe_order_headers_all (flow_status_code as entered) oe_order_lines_all . The order is booked as DROP SHIP
2 Order Booking
3 The Purchase Release program passes information about eligible drop-ship order lines to Oracle Purchasing.The interface table which gets populated is
po_requisitions_interface_all
4 After Purchase Release has completed successfully, run Requisition Import in Oracle Purchasing to generate purchase requisitions for the processed order lines. The Requisition Import program reads the table po_requisitions_interface_all validates your data, derives or defaults additional information and writes an error message for every validation that fails into the po_interface_errors table.The validated data is then inserted into the requisition base tables po_requisition_headers_all,po_requisition_lines_all,po_requisition_distributions_all.Then use autocreate PO fuctionality to create purchase orders and then perform receipts against these purchase orders
5 After the goods are successfully received invoices for vendors are created in accounts payables as in normal purchase orders.
6 Invoices are generated for customers In account receivables.
7 oe_order_lines_all (flow_status_code 'shipped', open_flag "N")
8 oe_order_lines_all (flow_status_code 'closed', open_flag "N")
9 Hope this is great help to understand the flow.
OE_DROP_SHIP_SOURCES: is the link table between OE/PO/REQ.
What are the advantages of Drop Shipment Orders?
These are the benefits:
- No inventory is required
- Reduced order fulfillment processing costs
- Reduced flow times
- Elimination of losses on non-sellable goods
- Elimination of packing and shipping costs
- Reduced inventory space requirements
- Reduced shipping time to your customer
- Allows you to offer a variety of products to your customers
Technical Details:
When you entered the Order and Booked the Order following table will store the Information:
SELECT * FROM OE_ORDER_HEADERS_ALL
WHERE ORDER_NUMBER = <>
AND HEADER_ID = <>
SELECT * FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = <>
AND LINE_ID IN (<>)
SELECT * FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_HEADER_ID = <>
AND SOURCE_LINE_ID in (<>)
à Release Status is ‘R’ (Ready to Release)
SELECT * FROM WSH_DELIVERY_ASSIGNMENTS
WHERE DELIVERY_DETAIL_ID IN (<>)
When u Create Delivery Details that time this table will populate the record.
AND DELIVERY_ID COLUMN ALSO WILL UPDATED IN WSH_DELIVERY_ASSIGNMENTS TABLE
SELECT * FROM WSH_NEW_DELIVERIES
WHERE DELIVERY_ID IN (<>)
After “Launch Pick Release” Following table will populate:
SELECT * FROM WSH_PICKING_BATCHES
WHERE BATCH_ID IN (<>)
Move Order Number = <>68
SELECT * FROM MTL_TXN_REQUEST_HEADERS
WHERE REQUEST_NUMBER = <>
Here Header_id is Batch Number
SELECT * FROM MTL_TXN_REQUEST_LINES
WHERE TXN_SOURCE_LINE_ID = <>
Here TXN_SOURCE_LINE_ID is the Line_ID of the OE_ORDER_LINES_TABLES.
After Ship Confirm Following table will populate:
SELECT * FROM MTL_SALES_ORDERS
WHERE SEGMENT1 = <>
AND SALES_ORDER_ID = <>
SELECT * FROM MTL_MATERIAL_TRANSACTIONS
WHERE INVENTORY_ITEM_ID = <>
AND TRANSACTION_REFERENCE = <>
AND TRANSACTION_SOURCE_ID = <>
Where TRANSACTION_REFERENCE is storing the HEADER_ID.
SELECT * FROM WSH_DELIVERY_LEGS
WHERE DELIVERY_ID IN (<>)
select * from MTL_ONHAND_QUANTITIES
where inventory_item_id = <>
After Running the “Workflow Background Process” Programme Invoice will Generate and that time following table will populate:
SELECT * FROM RA_CUSTOMER_TRX_ALL
WHERE INTERFACE_HEADER_ATTRIBUTE1 =<>
Here INTERFACE_HEADER_ATTRIBUTE1 is the Order_Number.
And TRX_COLUMN is the Invoice Number.
SELECT * FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE CUSTOMER_TRX_ID = <>
Here INTERFACE_LINE_ATTRIBUTE6 Column is the LINE_ID.
SALES_ORDER Column is also there which is storing ORDER_NUMBER.
SELECT * FROM AR_PAYMENT_SCHEDULES_ALL
WHERE CUSTOMER_ID = <>
and CUSTOMER_TRX_ID = <>
To get the Outstanding of the Customer
After Transfer into the GL tables through “General Ledger Transfer Program” concurrent Programe:
SELECT * FROM GL_JE_BATCHES
WHERE NAME = '<>'
SELECT * FROM GL_JE_HEADERS
WHERE JE_BATCH_ID =<>
AND JE_HEADER_ID = <>
SELECT * FROM GL_JE_LINES
WHERE JE_HEADER_ID = <>
--AND REFERENCE_5 = <>
Here Reference_5 is the Invoice Number.
Receipt Transaction :
SELECT * FROM AR_CASH_RECEIPTS_ALL
WHERE RECEIPT_NUMBER = 'NKREC_240105'
Before Adjustment STATUS is UNAPP Or UNID but Once u Applied with Invoice It will change into “APP”.
SELECT * FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE CASH_RECEIPT_ID = <>
In this Table APPLIED_CUSTOMER_TRX_ID store the CUSTOMER_TRX_ID of the RA_CUSTOMER_TRX_ALL table.
Order Type is Return Only:
SELECT * FROM OE_ORDER_HEADERS_ALL
WHERE ORDER_NUMBER = <>
SELECT * FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = <>
LINE STATUS IS “AWAITING_RETURNS”.
Order Line Type should be “Return (Receipt)”.
SELECT * FROM RCV_SHIPMENT_HEADERS
WHERE RECEIPT_NUM = <>
AND SHIPMENT_HEADER_ID = <>
SELECT * FROM RCV_SHIPMENT_LINES
WHERE SHIPMENT_HEADER_ID = <>
SELECT * FROM RCV_TRANSACTIONS
WHERE SHIPMENT_HEADER_ID = <>
IN RCV_SHIPMENT_LINES Table OE_ORDER_HEADER_ID AND OE_ORDER_LINE_ID Column is Link with OE_ORDER_HEADER AND OE_ORDER_LINE Tables.
After Receiving the Material at “Receiving Stage” at that time Order Line Status will be “Awaiting Return Disposition”.
After Deliver the Material to “Inventory” at that time Order Line Status will be “Returned” and One More Line will be Created with “Remaining Quantity”
For Example: If your Order line quantity is 3 and you have received 1 quantity than one More line will be created with 2 quantity in OE_OREDER_LINES table with status “AWAITING_RETURNS”
Base Tables of List of LOV in Sales Order Screen:
Customer Name:
PARTY_NAME Column of HZ_PARTIES table.
Customer Number:
ACCOUNT_NUMBER Column of HZ_CUST_ACCOUNTS table.
Ø Where PARTY_ID Column is the Link between HZ_PARTIES and HZ_CUST_ACCOUNTS tables.
Customer Contact:
FIRST_NAMELAST_NAME Column of AR_CONTACTS_V view.
Bill To and Ship to Locations:
CUST_ACCT_SITE_ID Column of HZ_CUST_SITE_USES_ALL table.
Order Type:
Name and Description Column of OE_TRANSACTION_TYPES_V View
To Get the Payment Terms:
SELECT * FROM RA_TERMS
Look Up Values:
Ø REQUEST_DATE_TYPE For Line Set
Ø SALES_CHANNEL
Ø FREIGHT_TERMS
Ø SHIPMENT_PRIORITY
Ø PAYMENT TYPE
To Get the Shipping Method:
SELECT * FROM WSH_CARRIER_SERVICES
SELECT * FROM WSH_ORG_CARRIER_SERVICES
WHERE ORGANIZATION_ID = '207'
To get the FOB:
Value is storing into the AR_LOOKUP where LOOKUP_TYPE = ‘FOB’
Drop Shipment Process:
Ø Once you booked the Order with type “Mixed” and line source type is “External” in “Shipping” tab that time the line status will be “Booked” in both the levels Header as well as line.
Ø After booked the Order we have to do “Purchase Release” or run the “Workflow Background Process”.
Workflow back ground process transfer the Sales Order Data into the PO_REQUISITIONS_INTERFACE_ALL Interface table to create the Purchase Requisition.
Once the Data is populated in Interface table then Run “Requisition Import” program to transfer the data into the base tables.
After that Line Status will be “Awaiting Receipt”.
Purchase Requisition Number can see at Line level in Additional Information option under that “Drop Ship” tab.
Link between Sales Order, Purchase Requisition and Purchase Order we can fine in
SELECT * FROM OE_DROP_SHIP_SOURCES
Here you will get ORDER_HEADER_ID, REQUISITION_HEADER_ID, REQUISITION_LINE_ID, PO_HEADER_ID, and PO_LINE_ID
Ø After Creating the Requisition Run the Auto creates option to create the Purchase Order against that purchases requisition.
Ø Receipt the Material :
If you received partially then Order Line status will be remain same like “Awaiting Receipt” Once you completely receipt the material then only status will changed into “Shipped”.
Ø Then after one Purchase Invoice will be created in Payables Module against the Purchase order and once sales invoice will be created in Receivables module against the sales order.
