Key Tables for Order Management and O2C Work Flow

O2C Work Flow and Key Tables

STEPS	Particulars
Step-1	Creation of Sales order
Step-2	Book the Sales Order
Step-3	Pick release the Sales Order
Step-4	Release the sales order
Step-5	Transact move order
Step-6	Ship Confirm
Step-7	India - Excise Invoice Generation
Step-8	India - VAT Invoice Generation / Accounting
Step-9	Interface Trip Stop – SRS……Scheduled
Step-10	Workflow background process
Step-11	Interface Lines
Step-12	Auto invoice Master Program
Step-13	Auto invoice Import  Program
Step-14	Invoice Transaction
Step-15	India - Importing Excise/VAT Invoice Numbers in AR
Step-16	India – Local Concurrent for processing Order lines AR
Step-17	Create Accounting
Step-18	Receipt applied to AR transaction
Step-19	Create Accounting
Step-20	Transfer Journal Entries to GL
Step-21	Journal Import
Step-22	GL Balances

Accounting Entry for O2C Cycle

Sales Order
Ac Code	Ac Description	JV	Amount
Asset	Deferred COGS account (item_cost)	Dr	1000
Asset	Inventory Valuation Account (item_cost)	Cr	1000
COGS Recognition transaction
Ac Code	Ac Description	JV	Amount
Expenses	COGS (Actual revenue percentage )	Dr	1000
Asset	Deffered COGS (Actual revenue percentage)	Cr	1000
Stores
Ac Code	Ac Description	JV	Amount
Asset	Inventory RM Account	Dr	1000
Asset	Inventory RM Account	Cr	1000
Invoice:
Ac Code	Ac Description	JV	Amount
Asset	Receivable Ac	Dr	1122
Income	To Revenue Ac	Cr	1000
Asset	To Tax Ac	Cr	10
Asset	To VAT	Cr	2
Asset	To Service Tax	Cr	10
Income	To frieght Inward	Cr	100
Receipts
Ac Code	Ac Description	JV	Amount
Asset	Bank /Cash Ac Dr	Dr	1122
Asset	To Receivable Ac Cr	Cr	1122

KEY TABLES FOR ORDER MANAGEMENT

Sale Order Entered
OE_ORDER_HEADERS_ALL	1t records header data created in Sale order .
OE_ORDER_LINES_ALL	It records Lines data for particular records
OE_PRICE_ADJUSTMENTS	When discount gets applied
OE_ORDER_PRICE_ATTRIBS	If line has price attributes then populated
OE_ORDER_HOLDS_ALL	If any hold applied for order like credit check etc
Booked
OE_ORDER_HEADERS_ALL	1t records header data created in Sale order .Booked Flag=Y, Order booked.
WSH_DELIVERY_DETAILS	Released Status Ready to release
Pick Released
WSH_DELIVERY_DETAILS	Released Status=Y Released to Warehouse (Line has been released to Inventory for processing)
WSH_PICKING_BATCHES	After batch is created for pick release
MTL_RESERVATIONS	This is only soft reservations. No physical movement of stock
Full Transaction
MTL_MATERIAL_TRANSACTIONS	No records in MTL_MATERIAL_TRANSACTIONS
MTL_TXN_REQUEST_HEADERS
MTL_TXN_REQUEST_LINES
WSH_DELIVERY_DETAILS	Released to warehouse
WSH_NEW_DELIVERIES	If Auto-Create is Yes then data populated
WSH_DELIVERY_ASSIGNMENTS	Deliveries get assigned
Pick Confirmed
WSH_DELIVERY_DETAILS	Released Status=Y Hard Reservations. Picked the stock. Physical movement of stock
Ship Confirmed
WSH_DELIVERY_DETAILS	Released_Status=C ;Shipped ;Delivery Note get printed Delivery assigned to trip stop quantity will be decreased
MTL_MATERIAL_TRANSACTIONS	On the ship confirm form, check Ship all box
WSH_NEW_DELIVERIES	If Defer Interface is checked then OM & inventory not updated. If Defer Interface is not checked: Shipped
OE_ORDER_LINES_ALL	Shipped_Quantity get populated
WSH_DELIVERY_LEGS	1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
OE_ORDER_HEADERS_ALL	If all the lines get shipped then only flag N
Auto Invoice
WSH_DELIVERY_DETAILS	Released_Status=I Need to run workflow background process.
RA_INTERFACE_LINES_ALL	Data will be populated after work flow process.
RA_CUSTOMER_TRX_ALL	After running Auto Invoice Master Program for
RA_CUSTOMER_TRX_LINES_ALL	Specific batch transaction tables get populated

Customer Information
HZ_PARTIES	A party is an entity that can enter into a business relationship.This table stores basic information about parties, which is true regardless of this relationship to the deploying company. Entities are modeled only once in HZ_PARTIES, regardless of how many roles they play. For example, if an organization is a customer, a distributor, and a partner, there is still only one record for them in HZ_PARTIES.
HZ_PARTY_SITES	This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
HZ_LOCATIONS	A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude. • This table stores information about an address such as: street address and postal code. • This table provides physical location information about parties (organizations and people) and customer accounts. • Records in HZ_LOCATIONS can store delivery and postal code information about a location, store latitude and longitude, and can be used to determine the appropriate calculations and tax rates for sales tax and VAT calculations.
HZ_CUST_ACCOUNTS
HZ_CUST_ACCOUNT_SITES_ALL
HZ_CUST_SITE_USES_ALL
RA_CUSTOMERS
Price Details
QP_LIST_HEADERS_B	To Get Item Price Details.
QP_LIST_LINES
Items On Hand Qty
MTL_ONHAND_QUANTITIES	To check On Hand Qty Items.
Payment Terms
RA_TERMS	Payment terms
Sales Order Holds
OE_HOLD_DEFINITIONS	Order Hold and Managing Details
OE_HOLD_AUTHORIZATIONS
OE_HOLD_SOURCES_ALL
OE_ORDER_HOLDS_ALL
Hold Release
OE_HOLD_RELEASES_ALL	Hold released Sales Order
Credit Check Details
OE_CREDIT_CHECK_RULES	To get the Credit Check Against Customer.
Cancel Orders
OE_ORDER_LINES_ALL	Cancel Order Details.
Automatic Numbering System
AR_SYSTEM_PARAMETES_ALL	You can check Automatic Numbering is enabled/disabled
Document Sequence
FND_DOCUMENT_SEQUENCES	Document Sequence Numbers
FND_DOC_SEQUENCE_CATEGORIES
FND_DOC_SEQUENCE_ASSIGNMENTS
Default Rules For Price List
OE_DEF_ATTR_DEF_RULES	Price List Default Rules
OE_DEF_ATTR_CONDNS
AK_OBJECT_ATTRIBUTES
End User Details
CSI_T_PARTY_DETAILS	To capture End user Details
Sales Credit
OE_SALES_CREDITS	Sales Credit Information
Attaching Documents
FND_ATTACHED_DOCUMENTS	Attached Documents and Text information
FND_DOCUMENTS_TL	Fnd documents short text
Blanket Sales Order
OE_BLANKET_HEADERS_AL	Blanket Sales Order Information.
OE_BLANKET_LINES_ALL
Processing Constraints
OE_PC_ASSIGNMENTS	Sales order Shipment schedule Processing Constraints
OE_PC_EXCLUSIONS

TECHNICAL DATA FLOW IN ORDER TO CASH

STEP 1:
Enter order received from customer in Order Management (Sales Order)
Also enter the line level information of items ordered for. On saving the information, the data will be inserted in OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL Order_number will be automatically generated, Header and line Flow_status_code = ‘ENTERED’

OE_ORDER_HEADERS_ALL

select header_id,
order_number,
request_date,
transactional_curr_code,
shipping_method_code,
ship_from_org_id,
ship_to_org_id,
org_id,
flow_status_code,
salesrep_id,
booked_date
from OE_ORDER_HEADERS_ALL
where flow_status_code = 'CLOSED'
and order_number = '10001'

OE_ORDER_LINES_ALL

select header_id,
        line_id,
        line_number,
        ordered_item,
        inventory_item_id ,
        request_date,
        promise_date,
        schedule_ship_date,
        pricing_quantity,
        ordered_quantity,
        cancelled_quantity,
        shipped_quantity,
        invoiced_quantity,
        payment_term_id,
        visible_demand_flag,
        actual_shipment_date,
        unit_selling_price,
        tax_value,
        cancelled_flag,
        flow_status_code
from OE_ORDER_LINES_ALL
where header_id = 20001


STEP 2:
Book the order. Header Flow_status_code = ‘BOOKED’
Line Flow_status_code = ‘AWAITING SHIPPING’
The data is captured in WSH_DELIVERY_DETAILS.
WSH_DLIVERY_ASSIGNMENTS is the intermediate table between WSH_DELIVERY_DETAIL and WSH_NEW_DELIVERIES.

WSH_DELIVERY_DETAILS

select delivery_detail_id,
        source_header_id,
        source_line_id,
        source_code,
        customer_id,
        inventory_item_id,
        item_description,
        ship_from_location_id,
        ship_to_location_id,
        move_order_line_id,
        requested_quantity,
        shipped_quantity,
        subinventory,
        released_status,
        ship_method_code,
        carrier_id,
        net_weight,
        unit_weight,
        unit_volume,
        unit_price,
        inspection_flag,
        source_header_number,
        batch_id
from WSH_DELIVERY_DETAILS
where source_header_id = 20001


WSH_DELIVERY_ASSIGNMENTS

select delivery_detail_id ,
        delivery_id,
        parent_delivery_detail_id,
        creation_date
from WSH_DELIVERY_ASSIGNMENTS
where delivery_detail_id in (216151, 216152, 216153) -- from above query


WSH_NEW_DELIVERIES

select delivery_id,
        name,
        status_code,
        customer_id ,
        gross_weight,
        net_weight,
        weight_uom_code,
        confirm_date,
        delivery_type,
        source_header_id,
        hash_string
from WSH_NEW_DELIVERIES
where delivery_id in (65881, 65882) -- from above query




STEP 3:
You can enter, view and update the Sales order information using Sales Order window.
Release the sales Order
Once the order is released, the data is updated in WSH_DELIVERY_DETAILS.The released_status becomes ‘Y’ and MOVE ORDER is generated in MTL_TXN_REQUEST_HEADERS and LINES. The move_order_line_id is also populated in WSH_DELIVERY_DETAILS

STEP 4:
Ship Confirm will update the data in WSH_DELIVERY_DETAILS. Released_status = ‘C’ and Shipped_quantity gets updated.
If you choose Tools >> Workflow Status then you will see that the workflow activity is in Deferred stage.
The concurrent program Workflow Background Engine needs to be executed to proceed further.

MTL_TXN_REQUEST_LINES

select line_id move_order_line_id,
        header_id move_order_header_id,
        line_number,
        inventory_item_id,
        organization_id,
        quantity_delivered,
        line_status,
        txn_source_id,
        transaction_type_id,
        pick_slip_date
from MTL_TXN_REQUEST_LINES
where line_id in (174779, 174780, 174781)


MTL_TXN_REQUEST_HEADERS

select header_id move_order_header_id,
        request_number move_order_number,
        move_order_type,
        organization_id,
        header_status move_order_status
from MTL_TXN_REQUEST_HEADERS
where header_id in (55272, 55273)


MTL_MATERIAL_TRANSACTIONS

select transaction_id,
        inventory_item_id,
        organization_id,
        subinventory_code,
        transaction_type_id,
        transaction_action_id,
        transaction_quantity,
        transaction_uom,
        transaction_date,
        distribution_account_id,
        invoiced_flag,
        shipment_number delvery_name
from MTL_MATERIAL_TRANSACTIONS
where shipment_number in ('65881', '65882')


Workflow Background Process executes Auto invoice master, Auto invoice Import Program.
The date is first transferred from Shipping to RA_INTERFACE_LINES_ALL.
Then the Auto invoice Import program validates the records and populates the base table in Account Receivable. RA_CUSTOMER_TXN_ALL , RA_CUSTOMER_TXN_LINES_ALL

RA_CUSTOMER_TRX_ALL

select interface_header_attribute1,
        customer_trx_id,
        trx_number,
        cust_trx_type_id,
        complete_flag,
        ship_date_actual
from RA_CUSTOMER_TRX_ALL
where interface_header_attribute1 = '12001'

Order Entry

Sales Order entry consists of two parts, Order header and Order lines.

Order Header

As order header information is entered and saved a record is created in OE_ORDER_HEADERS_ALL only and this record will be linked to many other tables and we shall discuss about those dependent tables now.

Transaction Type:

Transaction type is used to select the workflow path/process and it also defaults many other fields on the sales order. This transaction type is associated with a sequence which is responsible for providing the sales order number for the sales order.

--Transaction Type  SELECT oha.order_number ,   oha.order_type_id      ,   ott.name               ,   ott.description    FROM oe_order_headers_all oha,   oe_transaction_types_tl ott   WHERE 1            = 1 AND oha.order_number = '&ur_order#' AND oha.order_type_id=ott.transaction_type_id AND ott.LANGUAGE     = 'US';

Actually order transaction type details are stored in OE_TRANSACTION_TYPES_ALL but in the above query we have used _TL table (multilingual table) as we want to display transaction name.

Price List:

Price list information is stored in QP_LIST_HEADERS_B table but we need to refer to its multilingual table QP_LIST_HEADERS_TL

--Price List  SELECT oha.order_number ,   oha.price_list_id      ,   qlht.name              ,   qlht.description    FROM oe_order_headers_all oha,   qp_list_headers_tl qlht   WHERE 1            = 1 AND oha.order_number = '&ur_order#' AND oha.price_list_id=qlht.list_header_id AND qlht.LANGUAGE    = 'US';

Sales Person:

--Sales Person  SELECT oha.order_number ,   jrre.source_name       ,   jrre.source_email    FROM oe_order_headers_all oha,   jtf_rs_salesreps jrs          ,   jtf_rs_resource_extns jrre   WHERE 1            = 1 AND oha.order_number = '&ur_order#' AND oha.salesrep_id  =jrs.salesrep_id AND jrs.resource_id  = jrre.resource_id;

Order Header Status:

--Header Status  SELECT oha.order_number,   flv.lookup_type       ,   flv.lookup_code       ,   flv.meaning           ,   flv.description    FROM fnd_lookup_values flv,   oe_order_headers_all oha   WHERE 1                = 1 AND oha.order_number     = '&ur_order#' AND oha.flow_status_code = flv.lookup_code AND lookup_type          = 'FLOW_STATUS' AND LANGUAGE             = 'US';

Payment Terms:

--Payment terms  SELECT oha.order_number,   name                  ,   description    FROM oe_order_headers_all oha,   ra_terms rt   WHERE 1            = 1 AND oha.order_number = '&ur_order#' AND rt.term_id       = oha.payment_term_id ;

Shipping Method:

--Shipping Method   SELECT oha.order_number,   wcsm.service_level    FROM oe_order_headers_all oha,   wsh_carrier_ship_methods wcsm   WHERE 1                   = 1 AND oha.order_number        = '&ur_order#' AND oha.shipping_method_code= wcsm.ship_method_code AND wcsm.organization_id    = oha.ship_from_org_id;

Order Lines

Order lines consist of item ordered as part of the sales order.
On Hand Quantity of Item:

--On Hand Quantity  SELECT oha.order_number,   ool.line_number       ,   ool.ordered_item      ,   SUM(moq.transaction_quantity)    FROM oe_order_headers_all oha,   oe_order_lines_all ool        ,   mtl_onhand_quantities moq   WHERE 1                = 1 AND oha.order_number     = '&ur_order#' AND oha.header_id        = ool.header_id AND ool.ship_from_org_id = moq.organization_id GROUP BY oha.order_number,   ool.line_number        ,   ool.ordered_item;

Though this is not an exact procedure to find out the on hand quantity, this is just for example.

Things to notice after entering the order
you can notice that the values of the following column in line and headers table as follows

flow_status_code = ENTERED, booked_flag = N, open_flag = Y

Order Booking

Once the order is booked from the application, following are the changes occurred in the backed

• OE_ORDER_HEADERS_ALL (flow_status_code as BOOKED, booked_flag updated to Y)
• OE_ORDER_LINES_ALL (flow_status_code as AWAITING_SHIPPING, booked_flag updated Y)
• Since the order is now booked, delivery details of the order are created in WSH_DELIVERY_DETAILS table. WSH_DELIVERY_DETAILS has delivery lines and LPNs.
•  

OE_ORDER_LINES_ALL.line_id = WSH_DELIVERY_DETAILS.source_line_id

• These delivery details information can been viewed form ‘Shipping Transactions Form’ and the delivery status in the application will be as ‘Ready to Release’
• WSH_DELIVERY_DETAILS.released_status =’R’
• WSH_DELIVERY_DETAILS.Release_Status can have any of the below valid values
• Execute the below query to see lookup values

SELECT lookup_type,   lookup_code,   meaning,   description FROM fnd_lookup_values WHERE lookup_type = 'PICK_STATUS' AND LANGUAGE      = 'US';

• WSH_DELIVERY_ASSIGNMENTS assigns delivery details to a delivery and/or a parent delivery detail (LPN).

WSH_DELIVERY_ASSIGNMENTS.delivery_detail_id = WSH_DELIVERY_DETAILS.delivery_detail_id

• WSH_DELIVERY_ASSIGNMENTS.delivery_id will be NULL as still pick release operation is not performed as final delivery is not yet created.
• At the same time when order is booked ‘Demand interface program‘ is triggered in the background and demand of the item with specified quantity is created and these demand information is stored in MTL_DEMAND

·         MTL_DEMAND.demand_source_line = OE_ORDER_LINES_ALL.line_id

Reservations

This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background(if scheduled) and quantities are reserved. Once this program get successfully get completed, the MTL_DEMAND and MTL_RESERVATIONS tables are updated.  OE_ORDER_LINES_ALL.line_id = MTL_RESERVATIONS.demand_source_line_id

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 Selection List Generation – 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 material reservation is not done then MTL_RESERVATIONS gets updated now.
• A new record is created in WSH_NEW_DELIVERIES with status_code = ‘OP’ (Open). WSH_NEW_DELIVERIES has the delivery records.
•  

WSH_DELIVERY_ASSIGNMENTS.delivery_id = WSH_NEW_DELIVERIES.delivery_id

• WSH_DELIVERY_DETAILS.released_status changes to ‘S’ (‘submitted for release’)
• A move order is created in Pick Release process which is used to pick and move the goods to staging area (here move order is just created but not transacted). MTL_TXN_REQUEST_HEADERS, MTL_TXN_REQUEST_LINES  are move order tables
• MTL_MATERIAL_TRANSACTIONS_TEMP (link to above tables through move_order_header_id/line_id, this table holds the record temporally)

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

Pick Confirm

Pick Confirm is to transact the move order created in Pick Release process. In 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 (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)
• OE_ORDER_LINES_ALL.flow_status_code =’PICKED’
• MTL_MATERIAL_TRANSACTIONS is updated with Sales Order Pick Transaction

MTL_MATERIAL_TRANSACTIONS.trx_source_line_id  = OE_ORDER_LINES_ALL.line_id

• MTL_TRANSACTION_ACCOUNTS is updated with accounting information
• WSH_DELIVERY_DETAILS. released status=‘Y’ (‘Released’)
• WSH_DELIVERY_ASSIGNMENTS, MTL_ONHAND_QUANTITIES are updated accordingly

Note: Pick Confirm step can be eliminated if ‘Auto Pick Confirm’ is selected as ‘YES’ while performed manual pick release. If Pick release is done through shipping transactions form and if you want to perform auto pick confirm then picking rules setup has to be done.

Ship Confirm

Shipping execution of the order starts here. The goods are picked from staging area and given to shipping. “Interface Trip Stop” program runs in the backend.

• OE_ORDER_LINES_ALL .flow_status_code =‘SHIPPED’
• WSH_DELIVERY_DETAILS. released_status=‘C’ (‘Shipped’)
• Data from MTL_TRANSACTIONS_INTERFACE is moved to MTL_MATERIAL_TRANACTIONS and MTL_MATERIAL_TRANSACTIONS is updated with Sales Order Issue transaction.
• Data is deleted from MTL_DEMAND, MTL_RESERVATIONS and WSH_NEW_DELIVERIES
• Item deducted from MTL_ONHAND_QUANTITIES
• MTL_TRANSACTION_ACCOUNTS is updated with accounting information.

Generating 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.

OE_ORDER_LINES_ALL.invoice_interface_status_code = ‘YES’

ra_interface_lines_all (interface table into which the data is transferred from order management) Then Auto invoice program imports data from this table which get affected into this stage are receivables base table (especially ra_customer_trx_all and ra_customer_trx_lines_all).

RA_CUSTOMER_TRX_LINES_ALL.line_attribute_1 = OE_ORDER_HEADERS_ALL.order_number RA_CUSTOMER_TRX_LINES_ALL.line_attribute_6 = OE_ORDER_LINES_ALL.line_id

Close Order

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

OE_ORDER_LINES_ALL.flow_status_code =’CLOSED’
OE_ORDER_LINES_ALL.open_flag = ‘N’

Header is closed in the month end, its general oracle standard process