Data Flow for Oracle Order-to-Cash (O2C) Cycle

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 (FLOW_STATUS_CODE as BOOKED, BOOKED_FLAG updated to Y)

• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE as AWAITING_SHIPPING, BOOKED_FLAG updated Y)

• WSH_DELIVERY_DETAILS (DELIVERY_DETAIL_ID is assigned here, RELEASED_STATUS ‘R’ ready to release, LINE_ID comes as SOURCE_LINE_ID)

• WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ASSIGNMENT_ID is assigned for DELIVERY_DETAIL_ID present in WSH_DELIVERY_DETAILS, DELIVERY_ID remains blank till this stage)

*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 (one record gets inserted with SOURCE_HEADER_ID= order header ID, STATUS_CODE=OP =>open)

• WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ID gets assigned which comes from WSH_NEW_DELIVERIES)

• WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘S’ ‘submitted for release’)

• 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 (link to above tables through MOVE_ORDER_HEADER_ID/LINE_ID, this table holds the record temporally)

• MTL_SERIAL_NUMBERS_TEMP (if item is serial controlled at receipt then record goes in this table)

• MTL_SERIAL_NUMBERS (enter value in GROUP_MARK_ID )

*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 (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)

• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘PICKED’ )

• MTL_MATERIAL_TRANSACTIONS (LINE_ID goes as TXN_SOURCE_LINE_ID)

• MTL_TRANSACTION_ACCOUNTS

• WSH_DELIVERY_DETAILS (RELEASED_STATUS becomes ‘Y’ => ‘Released’ )

• WSH_DELIVERY_ASSIGNMENTS

• MTL_ONHAND_QUANTITIES

• MTL_SERIAL_NUMBERS_TEMP (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')

• MTL_SERIAL_NUMBERS (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')

* 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 (FLOW_STATUS_CODE ‘shipped’)

• WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘C’ ‘Shipped’, SERIAL_NUMBER if quantity is ONE)

• WSH_SERIAL_NUMBERS (records gets inserted with the DELIVERY_DETAIL_ID reference, only in case of shipped quantity is two or more)

• MTL_TRANSACTION_INTERFACE

• MTL_MATERIAL_TRANSACTIONS (linked through Transaction source header id)

• MTL_TRANSACTION_ACCOUNTS

• Data deleted from MTL_DEMAND, MTL_RESERVATIONS

• Item deducted from MTL_ONHAND_QUANTITIES

• MTL_SERIAL_NUMBERS_TEMP (records gets deleted from this table)

• MTL_SERIAL_NUMBERS (Serial number stauts gets updated CURRENT_STATUS=4 , 'Issued out of store')

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 (CUST_TRX_ID is primary key to link it to TRX_LINES table and TRX_NUMBER is the invoice number)

RA_CUSTOMER_TRX_LINES_ALL (LINE_ATTRIBUTE_1 and LINE_ATTRIBUTE_6 are linked to order number and LINE_ID of the orders)

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 (FLOW_STATUS_CODE ‘closed’, OPEN_FLAG “N”)

OE_ORDER_HEADERS_ALL

Overview of Procure to Pay Cycle and Order to Cash cycle

Procure to Pay:

First let’s see what the heading itself means? Procure to Pay means Procuring Raw Materials required to manufacture the final or finished Goods to Paying the Supplier from whom the material was purchased. But this is not just two steps. It involves many steps. Let’s see the steps and Oracle Application involved in performing those steps.

1.      Oracle Purchasing:You enter Suppliers of different materials and products you want to purchase to manufacture a finished good that your organization plans to sell.

2.      Oracle Purchasing:You prepare a Request for Quotation (RFQ) and send it to different suppliers to get the best and/or economical price for the product.

3.      Oracle Purchasing:Suppliers sends their quotations and you upload those quotations in Oracle Purchasing to get the best three quotes and further to get the one best quote.

4.      Oracle Purchasing:You prepare a Purchase Order(PO) against the best RFQ to buy the goods from the supplier who quoted the suitable price and sends the PO to that supplier

5.      Oracle Purchasing:The supplier receives the confirmation of purchase from PO and ships the ordered goods. You receive the goods enter a Goods Received Note (GRN) in Oracle Purchasing.

6.      Oracle Inventory / Oracle Assets: It’s up to you whether you want to receive the goods at your head office or you Inventory directly. In either case you move the received goods to your different Raw Material Inventory from Oracle Purchasing to Oracle Inventory and the Item Count increases. If the item is Asset Type then it will move to Oracle Assets at the time of Invoice creation in Oracle Payables.

7.      Oracle General Ledger: Once you move the goods to Oracle Inventory, it sends the Material Accounting to Oracle General Ledger.

8.      Oracle Payables:After this the supplier sends you the invoice for the purchased goods and you Enter or Match the invoice against the PO from Oracle Purchasing in Oracle Payables. As said before, if the item is Asset in nature then it will move to Oracle Asset.

9.      Oracle General Ledger: When you enter the invoice it means that you have created a Liability against that supplier and also you have recorded the expense incurred or asset purchased. Oracle Payables sends the invoice accounting to Oracle General Ledger.

10.  Oracle Payables:You pay the invoice and settle the Liability.

11.  Oracle General Ledger: The liability is settled and your cash movement account is updated.

12.  Oracle Cash Management: As you pay the invoice Oracle Payables sends the payment information to Oracle Cash Management for Bank Reconciliation. Once reconciled, Oracle Cash Management sends the updated Bank/Cash accounting entry to Oracle General Ledger.

13.  Oracle General Ledger: Your cash at bank is updated with actual balance.

14.  Oracle Process Manufacturing(OPM) / Oracle Discrete Manufacturing(ODM):You start the manufacturing of your final product. Both OPM or ODM requests the different raw materials from you inventory organizations and manufactures a finished good.

15.  Oracle Inventory: As the raw materials are issued to OPM and ODM the inventory sends the issuing material accounting to General Ledger and decreases the Item Count from the Raw Material Store. As the finished good is prepared, Oracle Inventory receives the finished good in Finished Good Store and increase the Item Count.

Now the final product is ready to be sold in the market and from here the O2C cycle starts.

Order to Cash Cycle:

Order to Cash means Customer’s Order Placing to Vendor’s Cash Receiving. When your final product is ready to be sold, you market it. The customer gets fascinated with the marketing campaign and decides to buy your product and from here starts the O2C cycle.

1.      Oracle Order Management: Customer places the order.

2.      Oracle Order Management: You enter the customer order

3.      Oracle Inventory:Check the available unit and the quantity ordered by the customer.

4.      Oracle Order Management: You ship the product to customer site and decreases the Finished Goods inventory.

5.      Oracle Receivables:The customer receives the product and you invoice the customer.

6.      Oracle General Ledger: You record your revenue and receivables.

7.      Oracle Receivables:The customer pays and you receive the cash/check.

8.      Oracle Cash Management: Oracle Receivables sends the customer receipt for Bank Reconciliation. After reconciliation, Oracle Cash Management send the actual bank balance or Oracle General Ledger.

9.      Oracle General Ledger: You have the actual bank balance.

This is how the P2P and O2C cycle works, but this is not the only way, obviously there are many other applications with different cycles. This is one of them.

ORDER TO CASH CYCLE

The first and foremost thing before raising a sales order, a customer and the Item must be created.

In order to created a customer, follow below navigation

Order Management Super User Operations (USA)>Customer>Standard

     It then opens a self service page. Search whether the customer already exists as shown in the below screen.

Click on the create button to create if the customer doesn’t exists. Fill the mandatory fields, profile class and account site address as shown in the below screens.

Click ‘Apply’ button to save the details.

Then edit the payment tab of the saved customer to enter the bank details as shown in the below screen

The underlined tables which stores the customer information are

HZ_PARTIES, HZ_LOCATIONS, HZ_PARTY_SITES, HZ_PARTY_SITE_USES.

Another prerequisite is to create an item

Navigation: Inventory > Items > Master Items

Create an item and select item attributes as shown in different tabs in the below screen.

Select the item template type, which specifies the item characteristic and accordingly the attributes are defaulted. So select Tools > copy from

Select the Purchased item as example we are taking the item as Purchased item and press apply button.

Then assign an organization where physically item is stored. For that select tool>Organization Assignment and check the organization you want to select as shown in the below screens.

Important tables which store the item definition and SQL tip as below

select inventory_item_id, segment1, organization_id from apps.mtl_system_items_b

where segment1 = 'Fossil Watch';

NVENTORY_ITEM_ID SEGMENT1 ORGANIZATION_ID
----------------- ---------------------------------------- ---------------
43817                                     Fossil Watch           204
43817                                     Fossil Watch           207

You can get the Organization details form the following

select organization_id, organization_code from apps.mtl_parameters

where organization_id in (204,207);

ORGANIZATION_ID ORG
---------------                 ---
204                                  V1
207                                  M1

You can assign the item to a category as shown in the below screen

Create stock for item using Miscellaneous Receipt

Navigation: Inventory, Vision Operations (USA)>Transactions>Miscellaneous Transaction

And enter the following information:
Type: Miscellaneous receipt
Click on the Transaction Lines button

In the Miscellaneous receipt (M1) form, enter the following information:
Item: Fossil Watch

Sub inventory: FGI
Quantity: 100
Account: Invoke the LOV and enter the Account Alias 'Miscellaneous'
Save changes.

Check the material transaction that was just created.
Navigate to Inventory > Transactions > Material Transactions
Enter the item in the Find Material Transactions screen and click on Find

Check the On-hand quantity for your item. Navigate to Inventory > On-hand, Availability > On-hand Quantity. Enter the item in the Query Material screen and click on Find. Verify that the On-hand quantity exists.

Assign item to Price List:

Navigate to Oracle Pricing Manager > Price Lists > Price List Setup. You could create a new price list, but in this test flow, we will assign your item to an existing price list 'Corporate'.
Query the price list 'Corporate'.

Move the cursor to the List Lines section, and add a new line using Ctrl + Down Arrow on your keyboard, or by going to File > New. Enter the following information:
Product Context: Item
Product Attribute: Item Number
Product Value: Fossil Watch
Value: 100
the rest of the required fields should default in.

Underlying tables are affected:

QP_LIST_HEADERS_ALL_B, QP_LIST_HEADERS_TL

QP_LIST_LINES, QP_PRICING_ATTRIBUTES

Create Order:

We are now ready to create a sales order.

1. Navigate to Order Management Super User, Vision Operations (USA) >Orders, Returns > Sales Orders and make sure the following information is entered.
Order Information tab (Order Header)
Customer Name: NTT DATA Incorporation
Make sure the Ship To/Bill to Locations are defaulted in.
Order Type: Mixed
Price List: Corporate
Salesperson: No Sales Credit

Go to the Line Items tab, and enter the following information.
Line Items tab (Order Lines)
Ordered Item : Fossil Watch
Qty : 20
UOM : Defaults to Ea
Save.

In the Pricing tab, notice that the Price List Corporate has defaulted from the Header, and that the List Price 100 is the price you set up in the price list.

Click Book Order

Now you can see the status of the Sales as ‘Booked’.

Underlying Tables affected: 

In Oracle, Order information is maintained at the header and line level.

The header information is stored in OE_ORDER_HEADERS_ALL and the line information in OE_ORDER_LINES_ALL when the order is entered. The column called FLOW_STATUS_CODE is available in both the headers and lines tables which tell us the status of the order at each stage.

select order_number, header_id, flow_status_code from oe_order_headers_all
where order_number = 66518;

ORDER_NUMBER HEADER_ID FLOW_STATUS_CODE
---------------------       -------------   -      -----------------
66518                     212312                     BOOKED

Finding the line_id and status of the order line based on header_id

select header_id, line_id, line_number, flow_status_code from oe_order_lines_all where header_id = 212312;

HEADER_ID LINE_ID LINE_NUMBER FLOW_STATUS_CODE
---------- ---------- ----------- ------------------------------
212312              408282           1                   AWAITING_SHIPPING

Delivery Details: Delivery details of the items are created in the shipping execution module. It inserts the records in the following tables.

WSH_DELIVERY_DETAILS and WSH_DELIVERY_ASSIGNMENTS

Finding delivery detail information based on the order_line_id

select source_header_number, source_header_id, source_line_id, delivery_detail_id, released_status
from wsh_delivery_details
where source_code = 'OE' and source_line_id =408282;

SOURCE_HEADER_NUMBER SOURCE_HEADER_ID SOURCE_LINE_ID DELIVERY_DETAIL_ID

66518                                          212312                         408282            3979467    

RELEASED_STATUS

R

Where ‘R’ is for Ready to Release

Pick Release:

Navigation:

Shipping > Release Sales Order > Release Sales Orders.

 In order tab, enter the Order number.

Go to the Shipping tab and select Auto create Delivery, Auto Pick Confirm and Auto Pack Delivery fields to ‘YES’

In Inventory tab select Ware house as ‘M1’ and Auto Allocate to YES. Then click on Execute Now button

This runs the Pick Release process, in turn will kick off several other requests like Pick Slip Report,

Shipping Exceptions Report and Auto Pack Report as shown in the following screen.

Underlying Tables affected:

As Auto create Delivery was set to ‘Yes’ a new record is created in the table WSH_NEW_DELIVERIES.

DELIVERY_ID is populated in the table WSH_DELIVERY_ASSIGNMENTS.

The RELEASED_STATUS in WSH_DELIVERY_DETAILS would be now set to ‘Y’ (Pick Confirmed) as Auto Pick Confirm is set to Yes ,otherwise RELEASED_STATUS is ‘S’ (Release to Warehouse).

Finding the delivery associated to the delivery detail and its status

select wnd.delivery_id, wnd.status_code
from wsh_new_deliveries wnd, wsh_delivery_assignments wda, wsh_delivery_details wdd
where wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
and wdd.delivery_detail_id = 3979467;

DELIVERY_ID STATUS
-----------          ---------
3785372              OP

Where ‘OP’ stands for Open and describes Delivery is Open, has not been shipped.

Ship Confirm the Order:

Navigation: 

Order Management Super User>Shipping >Transactions. 

Query with the Order Number.

Notice the following in the Lines/LPNs tab.
Detail: The delivery detail of the shipment
Delivery: The delivery that got auto created during pick release
Line Status: Staged/Pick Confirmed
Next Step: Ship Confirm/Close Trip Stop

Click On Delivery Tab

Click on Ship Confirm. A message pop ups confirming the delivery was successfully confirmed

This will kick off concurrent programs like INTERFACE TRIP Stop, Vehicle Load Sheet Details, Commercial Invoice, Packing Slip Report and Bill of Lading as shown in the below screen.

Underlying tables affected:

RELEASED_STATUS in WSH_DELIVERY_DETAILS would be ‘C’ (Ship Confirmed)

FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL would be “BOOKED“

FLOW_STATUS_CODE in OE_ORDER_LINES_ALL would be “SHIPPED“

Finding delivery details from OM to Inv as below

select delivery_detail_id, released_status, oe_interfaced_flag, inv_interfaced_flag
from wsh_delivery_details
where source_code = 'OE' and source_line_id = 408282;

Output as below

3979467          C         Y         Y

released_status = C (Shipped) -- becomes Shipped after ship confirm, even before ITS
oe_interfaced_flag = Y -- signifies ITS has interfaced shipping data to OM
inv_interfaced_flag = Y -- signifies ITS has interfaced shipping data to INV

As an optional step, you can check if the item has actually been shipped out of inventory.
Navigate to Inventory > Transactions > Material Transactions
Enter the following information in the Find Material Transactions screen and click on Find
Transaction Date : Ensure the transaction date is within the range specified
Source Type : Sales Order
Source :
-Evoke the LOV and click on the button Combinations
-Enter your Sales Order Number and Order Type Mixed, then click OK
-As 66518.Mixed.ORDER ENTRY

Go to the Transaction Type tab, and notice the line with Transaction Type as Sales Order Issue. This is the Inventory Transaction that happened when the item was shipped out of the warehouse M1, hence the negative quantity -20.
The other two lines with Transaction Type = Sales Order Pick, are for the move order transaction that happened automatically during Pick Release , when the item was moved from the pick-from subinventory to the staging subinventory. Hence there is one line each for a negative and positive transaction from/to each subinventory. The final shipment to the customer (sales order issue) is made from the staging subinventory.

Invoice

First check the status of the order line.
Navigate to the sales order form, and query your sales order. Go to the Line Items tab and check that the status of the line is 'Shipped'.
Go to Tools > Workflow Status to verify the workflow. This shows that the order line workflow is now at the Fulfill - Deferred activity with a status of Deferred as shown below. This means that the next steps is to run the Workflow Background Process.

Navigation:

Order Management >view >Requests and run the Workflow Background Process

In addition, notice that the AR concurrent requests Autoinvoice and Autoinvoice Import Program get kicked off automatically to import the invoices into AR. OM inserts invoice data into the AR interface tables through 'Invoice Interface', which runs in the background.AR picks up the data from the interface tables through the 'Autoinvoice' concurrent program.

Tables Effected:

Inserts the records RA_INTERFACE_LINES_ALL with

INTERFACE_LINE_CONTEXT     =     ’ORDER ENTRY’

INTERFACE_LINE_ATTRIBUTE1=     Order_number and INTERFACE_LINE_ATTRIBUTE3=     Delivery_id

And

select * from apps.RA_CUSTOMER_TRX_ALL  where INTERFACE_HEADER_ATTRIBUTE1='66518'

select * from apps.ra_customer_trx_lines_all where INTERFACE_LINE_ATTRIBUTE1='66518'

Select the output to check whether data has successfully imported into receivables tables to create Invoice as shown below

The Invoice created can be seen using the Receivables responsibility

Navigation:

Receivables Super User> Transactions> Transactions 

Query with the Order Number as Reference and Source as ORDER ENTRY

Click the Line Items to view the line information

Create Receipt:

Navigation:

Receivables> Receipts> Receipts

Enter the Receipt method as manual and receipt type as Standard, it doesn’t need to do remittance and clearance method is direct. Also enter customer and amount. Click apply button, which insert the records into below table.

AR_CASH_RECEIPTS_ALL

Transfer to General Ledger:

  Select Tool>Create Accounting from the receipt form as shown below

Check  ‘Create Final Accounting Post to GL’ and click OK.

This kicks Create Accounting program.

It creates records in the GL_INTERFACE table. You can view the created account information as shown in the below screen

Created accounting information displayed

Journal Import:

Navigation:

General Ledger > Journal> Import> Run

To transfer the data from General Ledger Interface table to General Ledger, run the Journal Import program from Oracle General Ledger. Enter parameters as below, ‘selection criteria’ is to run for a specific group id, no group id or all group id. Click import.

It kicks off Journal Import program

Click on the output to check whether import was successful. If it successful, underlying tables are effected

GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES

Posting: 

We have to Post journal batches that we have imported previously to update the account balances in General Ledger. 

Navigation: 

General Ledger> Journals > Enter

Query for the unposted journals for a specific period as shown below

From the list of unposted journals displayed, select the journal that to be poated and click on Post button to post the journal

It runs Posting: Single Ledger Program

To view Journal, click Review Journal button. Table effected GL_BALANCES