Skip to main content

Base Tables for Order to Cash (O2C) Cycle in Oracle Apps

Base Tables for Order to Cash (O2C) Cycle in Oracle Apps

1. Order Entry
This is the first stage where Order is entered into the system. It creates a record in Headers table and Lines table
OE_ORDER_HEADERS_ALL:
This table stores the Header Information of the Sales Order
Important columns in this table:
HEADER_ID: Unique system generated ID
ORG_ID, ORDER_NUMBER, SHIP_FROM_ORG_ID, SHIP_TO_ORG_ID and FLOW_STATUS_CODE
At the time of Order Entry, the FLOW_STATUS_CODE is ‘Entered’
Sample code:
SELECT HEADER_ID, ORG_ID, ORDER_TYPE_ID, FLOW_STATUS_CODE, TRANSACTIONAL_CURR_CODE, SHIPPING_METHOD_CODE, SHIP_FROM_ORG_ID, SHIP_TO_ORG_ID FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER= 66405;
OE_ORDER_LINES_ALL:
This table stores the Line Information of the Sales Order
Important columns of this table
LINE_ID: Unique system generated ID
HEADER_ID: It is the link between OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL
ORDERED_ITEM, INVENTORY_ITEM_ID, PRICING_QUANTITY, ORDERED_QUANTITY, FLOW_STATUS_CODE and UNIT_SELLING_PRICE_PER_PQTY
Sample code:
SELECT LINE_ID FROM OE_ORDER_LINES_ALL WHERE HEADER_ID= 190452;
SELECT ORDERED_ITEM, INVENTORY_ITEM_ID, PRICING_QUANTITY, ORDERED_QUANTITY, FLOW_STATUS_CODE, UNIT_SELLING_PRICE_PER_PQTY FROM OE_ORDER_LINES_ALL WHERE LINE_ID= 388401;
2. Order Booking
Order Booking is the final stage in the Sales Order entry. Now that the Order Entry process is complete and that the order is eligible for the next stage in the line flow for this order, as defined by its Transaction Type. By clicking Book Order button, the Order is booked.
OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
WSH_DELIVERY_DETAILS
When the Order is Booked, the FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL would be ‘BOOKED’ and the FLOW_STATUS_CODE in OE_ORDER_LINES_ALL would be ‘AWAITING_SHIPPING’
RELEASED_STATUS in WSH_DELIVERY_DETAILS would be ‘R’ (means- ready to release)
Important columns of WSH_DELIVERY_DETAILS table:
DELIVERY_DETAIL_ID: Unique system generated id with reference to SOURCE_HEADER_ID (it is the HEADER_ID from OE_ORDER_HEADERS_ALL)
SOURCE_HEADER_ID: It is the HEADER_ID generated from OE_ORDER_HEADERS_ALL
SOURCE_LINE_ID: It is the LINE_ID generated from OE_ORDER_LINES_ALL
RELEASED_STATUS, SOURCE_CODE, CUSTOMER_ID, INVENTORY_ITEM_ID, SHIP_FROM_LOCATION_ID, SHIP_TO_LOCATION_ID, MOVE_ORDER_LINE_ID, REQUESTED_QUANTITY, SHIPPED_QUANTITY, SUBINVENTORY, SHIP_METHOD_CODE etc.
Sample Code:
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 FROM WSH_DELIVERY_DETAILS WHERE SOURCE_HEADER_ID= 190452;
3. Launch Pick Release:
Pick release is the process by which the items on the sales order are taken out from inventory. This process allocates on-hand inventory to your order and inform the warehouse personnel to move the item from inventory to the shipping staging area.  Once your item is in the shipping staging area, it is ready to be shipped.
OE_ORDER_LINES_ALL:  Here the FLOW_STATUS_CODE should be ‘PICKED’ or ‘AWAITING_SHIPPING’ depending on Auto Pick Confirm (set to No or Yes)
WSH_DELIVERY_DETAILS: Here RELEASED_STATUS should be ‘S’ (Submitted for Release) or ‘Y’ (Pick Confirmed). These values again depend on the parameters given at Auto Pick Confirm (set to No or Yes)
WSH_DELIVERY_ASSIGNMENTS: DELIVERY_ID is populated here (from DELIVERY_DETAIL_ID with reference to WSH_DELIVERY_DETAILS table)
Sample Code:
SELECT DELIVERY_DETAIL_ID FROM WSH_DELIVERY_DETAILS WHERE SOURCE_HEADER_ID= 190452;
SELECT DELIVERY_ASSIGNMENT_ID, DELIVERY_ID, PARENT_DELIVERY_ID, DELIVERY_DETAIL_ID, PARENT_DELIVERY_DETAIL_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, ACTIVE_FLAG, TYPE FROM WSH_DELIVERY_ASSIGNMENTS WHERE DELIVERY_DETAIL_ID= 3966467;
4. Ship Confirm the Order:
It is a process of sending the Items from shipping staging area to the Customer site. By ship confirming you will notify EBS that the shipment is complete and thereby updating the on-hand Inventory.  Ships confirm process will kick off the following Concurrent Programs:
Interface Trip stop, Packing slip Report, Bill of Lading, Commercial Invoice
OE_ORDER_LINES_ALL:  Here the FLOW_STATUS_CODE should be ‘SHIPPED’
WSH_DELIVERY_DETAILS:  Here RELEASED_STATUS should be ‘C’ (Shipped)
5. Creating Invoices in Receivables:
Here the Invoices are created based on the goods sold. We need to run the ‘Workflow Background Process’ where it picks the shipping records and transfers to Receivables interface
Workflow Background Process inserts new records in RA_INTERFACE_LINES_ALL
Important columns of this table:
INTERFACE_LINE_ID: It is the LINE_ID with reference to OE_ORDER_LINES_ALL
INTERFACE_LINE_CONTEXT, INTERFACE_LINE_ATTRIBUTE1, INTERFACE_LINE_ATTRIBUTE3
Sample Code:
SELECT INTERFACE_LINE_CONTEXT, INTERFACE_LINE_ATTRIBUTE1, INTERFACE_LINE_ATTRIBUTE3 FROM RA_INTERFACE_LINES_ALL WHERE INTERFACE_LINE_ID= 388401;
INTERFACE_LINE_CONTEXT:                       Order Entry
INTERFACE_LINE_ATTRIBUTE1:                  Order Number
INTERFACE_LINE_ATTRIBUTE3:                  Delivery ID
RA_CUSTOMER_TRX_ALL: Stores Invoice header information.
INTERFACE_HEADER_ATTRIBUTE1 column will have the Order Number.
INTERFACE_HEADER_ATTRIBUTE2 column will have Order Type
TRX_NUMBER column is the Invoice Number
Sample Code:
SELECT INTERFACE_HEADER_ATTRIBUTE2, CUSTOMER_TRX_ID, TRX_NUMBER, CUST_TRX_TYPE_ID, COMPLETE_FLAG, SHIP_DATE_ACTUAL FROM RA_CUSTOMER_TRX_ALL WHERE INTERFACE_HEADER_ATTRIBUTE1= ‘66405’;
RA_CUSTOMER_TRX_LINES_ALL: Stores Invoice lines information.
INTERFACE_LINE_ATTRIBUTE1 column will have the Order Number.
INTERFACE_LINE_ATTRIBUTE2 column will have Order Type
INTERFACE_LINE_ATTRIBUTE3 column will have Delivery
INTERFACE_LINE_ATTRIBUTE4 column will have Waybill
INTERFACE_LINE_ATTRIBUTE5 column will have count
INTERFACE_LINE_ATTRIBUTE6 column will have Line ID
INTERFACE_LINE_ATTRIBUTE7 column will have Picking Line ID
INTERFACE_LINE_ATTRIBUTE8 column will have Bill of Lading
INTERFACE_LINE_ATTRIBUTE9 column will have Customer Item Part
INTERFACE_LINE_ATTRIBUTE10 column will have warehouse
INTERFACE_LINE_ATTRIBUTE11 column will have Price Adjustment
INTERFACE_LINE_ATTRIBUTE12 column will have Shipment Number
INTERFACE_LINE_ATTRIBUTE13 column will have Option Number
INTERFACE_LINE_ATTRIBUTE14 column will have Service Number
6. Create Receipt
Underlying tables:
AR_CASH_RECEIPTS_ALL
CASH_RECEIPT_ID is the unique system generated ID
FLOW_STATUS_CODE in OE_ORDER_LINES_ALL should be ‘CLOSED’

Comments

  1. Thank you so much for sharing this worth able content with us. The concept taken here will be useful for my future programs and i will surely implement them in my study. Keep blogging article like this.
    Best quoting software

    ReplyDelete

Post a Comment

Popular posts from this blog

Oracle Subledger Accounting (SLA) Tables, Views

Oracle Subledger Accounting (SLA) Tables, Views Oracle Subledger Accounting Tables: TABLE NAME DESCRIPTION XLA_AAD_GROUPS The XLA_AAD_GROUPS table stores the merge dependencies analyzed during the merge analysis.  All application accounting definitions with the same GROUP_NUM must be merged together. XLA_AAD_HDR_ACCT_ATTRS The XLA_AAD_HDR_ACCT_ATTRS stores standard, system and custom sources assigned to an accounting attribute at the AAD level. XLA_AAD_HEADER_AC_ASSGNS Store the analytical criteria for the application accounting definitions. XLA_AAD_LINE_DEFN_ASSGNS This table stores the journal lines definitions for the application accounting definitions. XLA_AAD_LOADER_DEFNS_T The XLA_AAD_LOADER_DEFNS_T table is the interface table that facilitates the data transfer from data files and the database. XLA_AAD_LOADER_LOGS The XLA_AAD_LOADER_LOGS table stores the errors and logs generated by the application accounting definitions loader. XLA_AAD_SOURCES XLA_AAD_SOURCES table stores a...

Oracle Fusion Cloud: Supplier Import Process using File Based Data Import (FBDI)

 Supplier Data Migration or Upload to Oracle Fusion environment File-Based Data Import for Oracle Procurement Cloud Supplier import in oracle fusion   In this post , We will discuss about Supplier import in oracle fusion. Oracle has provided the FBDI tool to import suppliers from External  Source  to the Oracle fusion. Supplier Import in Oracle fusion we first need to  Download  the Supplier Import FDBI  templates  given by the Oracle fusion to import suppliers. Oracle has given 5 Different FBDI templates to Import supplier in Fusion. Here in this post , I will share the Complete steps for Supplier import in oracle fusion. You can refer this post for Supplier Import.   Steps for Supplier import in oracle fusion     Step1- First we need to download Supplier Data  Template  from Oracle Repository. Go to this path for Oracle Repository. ( select Based on your Cloud version) https://docs.oracle.com/en/...

Unbilled Receivables and Unearned Revenue Accounting in Oracle Projects

Unbilled Receivables and Unearned Revenue Accounting in Oracle Projects Introduction When it comes to contractual billing, invoice and revenue generation are two separate processes, which during the lifespan of a project may or may not always coincide with each other and so do the balances in revenue and receivables accounts.  This interim difference between revenue and invoice account balances is bridged using Unbilled Receivables (UBR) and Unearned Revenue (UER) Accounts. Unearned Revenue (UER) Unearned Revenue (also termed as deferred revenue or UER) signifies money received for the goods or services, which are yet to be delivered.  As per the principles of Revenue Recognition, UER is recorded as on the balance sheet unless it is converted to Revenue upon delivery of goods or services For Example XYZ Consulting Ltd. receives an annual maintenance contract of $ 12,000 on Dec 31, 2014 for the period of Jan 01, 2015 to Dec 31, 2015. At the start of the contract as of Dec 31, 2...