Order to Cash (O2C) Cycle – Complete Flow

Order to cash cycle is the business process of selling of goods to the customer and receiving the payment for the sold goods.

Order to cash cycle is the basic cycle which is the backbone of operation in any supply chain business.

In Order to Cash cycles following steps are involved:

·         Order Entry
·         Order Booking
·         Pick Release
1.       Reservation
2.       Move Order Transaction
·         Ship Confirm
·         Enter Invoice/Auto Invoice  
.       Close Order

1.       Enter Sales Order/ Order Entry:

Header Details:

In this stage sales order entered in the system at header and line level means in this stage creates a record in order header and order line tables.

User enters the header details and save the entered information/details, record goes to header level table:

Table Name: OE_ORDER_HEADERS_ALL

Some Key Columns: HEADER_ID (Primary Key)

                                    FLOW_STATUS_CODE = ENTERED

                                       BOOKED_FLAG = N

                                    ORG_ID

Line Details:

Enter the line level information like as item quantity, Item, Price and other details at line level. When all line level information entered user saved it, then these records goes to line table.

Table Name: OE_ORDER_LINES_ALL

Some Key Columns: HEADER_ID

                                    LINE_ID (Primary Key)

                                    FLOW_STATUS_CODE = ENTERED

                                       BOOKED_FLAG = N

* OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL tables are linked by HEADER_ID.

      Freight and Discount Charges:

If discounts or any charges are applied on the order then an entry is made in the table OE_PRICE_ADJUSTMENTS to reflect that adjustment. We can check if any discount has been applied to an order by querying the table on the basis of the HEADER_ID of the Order.

Navigation for Screen: Order Line --> Actions Button --> Charges

2.       Order Booking:

When order is entered then after it’s ready for next stage and next stage is booking.

When user booked the order then the following changes are done and tables are affected:

1.       OE_ORDER_HEADERS_ALL :- FLOW_STATUS_CODE = BOOKED

                                                    BOOKED_FLAG = Y

2.       OE_ORDER_LINES_ALL :- FLOW_STATUS_CODE = AWAITING_SHIPPING

                                                    BOOKED_FLAG = Y

3.       WSH_DELIVERY_DETAILS :- DELIVERY_DETAIL_ID assigned

                                                  RELEASED_STATUS = R (Ready to Release)

                                                  SOURCE_LINE_ID (As LINE_ID of OE_ORDER_LINES_ALL)

4.       WSH_DELIVERY_ASSIGNMENTS :- DELIVERY_ASSIGNMENT_ID

*This ID assigned for DELIVERY_DETAIL_ID which is available in WSH_DELIVERY_DETAILS.

* Till this stage DELIVERY_ID remains blank as still pick release operation is not      performed as final delivery is not yet created.

* Till this stage sale order status remains R = Ready to Release at Shipping Transaction Form.

**At the time of Order Booking Demand Interface Program runs in the background and insert the data into Inventory table which is MTL_DEMANDS. In this table DEMAND_SOURCE_LINE comes as LINE_ID (OE_ORDER_LINES_ALL Table).

Schedule order:

·  Check if the order lines are scheduled or not. If any of the order line is in BOOKED status, it means that the order line is not yet scheduled.

·  Run the Schedule Order concurrent program to schedule the order. After the concurrent program is scheduled the order line will move to Awaiting Shipping status.

·  If the order lines still do not go to Awaiting Shipping Status, check for any holds on the order line/ header. Release the holds (if any) and re-run the Schedule Order program.

·  You can even schedule the order line in the following way: Right click on the order line and progress the line to schedule it.

**When Oracle tries to book the order, checks are performed to check if the order is eligible for booking or not.

• Credit check validation is performed against the customer for whom the order is booked to check if the credit limit for the customer exceeds on booking the order, If so then the order is put on hold with the entries being made in OE_ORDER_HOLDS table, the entry can be tracked using the HEADER_ID of the order.
• Oracle checks if a Customer item cross reference exists for the item being booked for the customer. If not the Order will be booked and will be put on hold.
• If the Customer is enabled only for EDI order creation then the order is put on hold, since manual booking is not permissible for this customer.
• Oracle checks if the requested item is active, eligible for Sale.
• All these validations are defined in the Order Management setup, processing constraints based on which specific checks are performed whenever the user tries to perform any action.

In case the order is put on hold the user needs to check which hold has been applied on the order and then take corrective actions and release the holds. 

To check the holds applied at:

Order Header level Go to Order Header à Actions Button, select Additional Order Information.

Line level holds can be checked by navigating to order lines à Actions à Additional Line Information, holds tab of the form.

In case the user is eligible to release the holds the user can release the header/line level holds by navigating to Order Header/Line à Actions à Release holds

3.       Pick Release:

Reservation:

This stem required to for items are reserved to fulfill the requirements for particular sales order and these reserved items are not available for the fulfillment of other order requirements.

SCHEDULE ORDER PROGRAM runs in the background and item quantities are reserved.

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

Pick Release is the process of putting reservation on on-hand quantity available in the inventory and picks 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.

When pick release done then following tables are affected:

     1.    WSH_DELIVERY_DETAILS : RELEASED_STATUS = S (Submitted for Release)

     2.    WSH_DELIVERY_ASSIGNMENTS: DELIVERY_ID which comes from WSH_NEW_DELIVERIES table.

     3.    WSH_NEW_DELIVERIES: Record inserted in this table.

                                           SOURCE_HEADER_ID (comes as HEADER_ID from    OE_ORDER_HEADERS_ALL table)

                                           STATUS_CODE = OP (Open)

     4.    MTL_RESERVATIONS: If reservations not done earlier (in Reservation Step) then this table get updated now.

     5.    MTL_TXN_REQUEST_HEADERS: Having header info of move order transactions.

     6.    MTL_TXN_REQUEST_LINES: TXN_SOURCE_LINE_ID (comes as LINE_ID from OE_ORDER_LINES_ALL table)  

These (MTL table) are move order tables here a request is generated to move item from Source (RM or FG) sub-inventory to staging sub-inventory)  

      7.    MTL_MATERIAL_TRANSACTIONS_TEMP: We can this table to above move order tables thorough MOVE_ORDER_HEADER_ID and MOVE_ORDER_LINE_ID. This table holds records 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 (its inventory transaction table).

When pick release is done, 

·         Move order is created for the specified items

·         Items from the inventory are allocated to the move order

• Move order is transacted which leads to the transfer of items from the source sub inventory to the STAGE area of the warehouse
• Generally a pick release document set is defined , these documents are also generated when pick release is performed
• The documents generated on pick release are the basis on which the pick release action / movement of goods is performed in the warehouse

Move Order Transaction:

 Move orders get created in the system and the same is transacted to keep a track of the item transfer from source sub-inventory to stage area of the warehouse or staging sub-inventory.

Order line status becomes 'Picked' on Sales Order and 'Staged/Pick Confirmed' on Shipping Transaction Form.

Following tables are affected:

1.       OE_ORDER_LINES_ALL: FLOW_STATUS_CODE = Picked

2.       WSH_DELIVERY_DETAILS: RELEASED_STATUS = ‘Y’ (Staged) if Pick Release successful otherwise ‘B’ in case of line back ordered.

3.       WSH_NEW_DELIVERIES:  Details of the delivery created for the lines wick are pick released.

4.       WSH_DELIVERY_ASSIGNMENTS

5.       MTL_ONHAND_QUANTITIES: Entries made for the increase of on-hand in STAGE and updates for the goods moving out of the source sub-inventory.

6.       MTL_TXN_REQUEST_HEADERS: Having move order header information

7.       MTL_TXN_REQUEST_LINES: TRX_SOURCE_LINE_ID comes as LINE_ID (OE_ORDER_LINES_ALL).

8.       MTL_MATERIAL_TRANSACTIONS_TEMP: Record deleted from this table and inserted in to MTL_MATERIAL_TRANSACTIONS: Entries tracking the movement of goods in the warehouse.

When a pick release of order line is done a delivery is created and on or more order lines are associated to the same delivery.

An entry is made in the WSH_NEW_DELIVERIES table for the delivery created. DELIVERY_ID field is updated in WSH_DELIVERY_ASSIGNMENTS table. The Delivery Id is the shipment number assigned to that particular order.

If Pick Release process is successful then the RELEASED_STATUS field of WSH_DELIVERY_DETAILS table is updated to ‘Y’, In case enough quantity is not available when the order is picked the order will be back-ordered due to unavailability of the quantity and the released status is set to ‘B’.

4.       Ship Confirm:

After the Order has been picked next stage is shipping of the Order.

Ship Confirm Interface Program runs in background.

Ship confirmation is the step in which the goods are shipped to the customer site. This is the step where the inventory leaves the suppliers warehouse to be sent to the customer.

Following tables are affected:

1.       OE_ORDER_HEADERS_ALL: FLOW_STATUS_COED = BOOKED

2.       OE_ORDER_LINES_ALL: FLOW_STATUS_COED = SHIPPED

3.       WSH_DELIVERY_DETAILS: RELEASED_STATUS = ‘C’ (Shipped)

4.       MTL_DEMAND, MTL_RESERVATIONS:- Data deleted from these tables.

5.       MTL_ONHAND_QUANTITIES: Items deducted from this table, goods have been shipped to customer.

6.       MTL_MATERIAL_TRANSACTIONS: Transaction quantity is negative which means that goods are moving out of the system.

Following are the pick release status:

Table: WSH_DELIVERY_DETAILS

Column: RELEASED_STATUS

Possible Values:

 B: Backordered- Line failed to be allocated in Inventory
C: Shipped -Line has been shipped
D: Cancelled -Line is Cancelled
N: Not Ready for Release -Line is not ready to be released
R: Ready to Release: Line is ready to be released
S: Released to Warehouse: Line has been released to Inventory for processing
X: Not Applicable- Line is not applicable for Pick Release
Y: Staged/Pick Confirmed- Line has been picked and staged by Inventory

Run the Interface Trip Stop – SRS Program

·         The Interface Trip Stop (ITS) will be trigger at the time of Shipping if check box “Defer Interface” is not check. ITS can also be executed from concurrent request.
Interface Trip Stop has two main components:

1.    Update the Order Management Data (OE_ORDER_LINES_ALL)

2.    Trigger the Inventory Interface (to Update the Inventory tables)

 ITS updates the following fields on OE_ORDER_LINES_ALL table:

·         SHIPPED_QUANTITY

·         SHIPPING_QUANTITY

·         ACTUAL_SHIPMENT_DATE

Inventory Interface will be trigger only if the first part, that means related to OM has successfully completed and flag OE_INTERFACED_FLAG = ‘Y’ on WSH_DELIVERY_DETAILS.  If value of this flag is N/P then Inventory Interface will never be triggered. And even if you try to submit the Inventory Interface from SRS, delivery detail with OE_INTERFACED_FLAG =’N’ will never be picked up.

OE_INTERFACED_FLAG = ‘Y’ = signifies ITS has interfaced shipping data to OM

INV_INTERFACED_FLAG = ‘Y’ = signifies ITS has interfaced shipping data to INV

5.       Enter Invoice/Auto Invoice:

After shipping or shipment of the goods to customer next stage comes as invoicing where invoice are created for sold goods.

For this data to be interfaced to AR interface tables. Invoicing workflow activity transfers the shipped item information to Oracle Receivables.

Run the Workflow Background Process which picks all shipped order lines and inserts records in the RA_INTERFACE_LINES_ALL table. At the same time records also goes in the table RA_INTERFACE_SALESCREDITS_ALL which hold details of sales credit for the particular order. This is called Receivable Interface means information moved to accounting area for invoicing.

RA_INTERFACE_LINES_ALL tables key columns:

INTRFACE_LINE_ATTRIBUTE1 = Order Number, 

INTERFACE_LINE_ATTRIBUTE3 = DELIVERY_ID

INTERFACE_LINE_ATTRIBUTE6 = Order LINE_ID

To generate the invoice:

Navigate: AR Responsibility à Interface à Auto Invoice

Run the Autoinvoice Master Program with the appropriate parameters. After successfully completion of this program records are inserted in the RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL from receivables interface tables.

RA_CUSTOMER_TRX_ALL (CUSTOMER_TRX_ID is primary key to link it to TRX_LINES table and TRX_NUMBER is the invoice number and TRX_DATE is the invoice date)
RA_CUSTOMER_TRX_LINES_ALL (LINE_ATTRIBUTE_1 and LINE_ATTRIBUTE_6 are linked to order number and LINE_ID of the orders)

Create Receipts:

When payment is received from a customer the receipts needs to be entered in Oracle. This is done from the Receipts screen.

User needs to enter the amount received from the customer and create receipts accordingly then after apply it to the corresponding invoices to which he wants. The receipt can either applied to single invoice or multiple invoices.

After performing the receipts for the Invoices, the whole order to cash (O2C) cycle gets completed since all the activities likes Received of Order à Fulfillment of Order à Raising Invoice to Customer à Receiving payments for the goods which shipped to customer is completed.

6.       Close Order:

This is the last stage of Order Processing.

In this stage OE_ORDER_HEADERS_ALL table get updated and following columns are updated.

FLOW_STATUS_CODE: CLOSED

OPEN_FLAG: N

Accounting Entries:
Sales order creation – No entries
    Pick release:
    Inventory Stage A/c…………………Debit
    Inventory Finished goods a/c……..Credit
    Ship confirm:
    Cogs A/c ……………………………Debit
    Inventory Organization a/c………Credit
    Receviable:
    Receviable A/c………………………Debit
    Revenue A/c………………………Credit
    Tax ………………..…………………Credit
    Freight…………..….……………….Credit
    Cash:
    Cash A/c Dr…………………………Debit
    Receivable A/c……………………….Credit

Oracle Alerts Overview

Oracle Alert facilitates the flow of information within your organization by letting you
create entities called alerts to monitor business information and to notify you of the
information you want.
Two types of alerts:
1. Event Alert
2. Periodic Alert

1. Event Alert:-
An event alerts are triggered/fired or notifies you of activity when some changes occures in data in the database. When we create an event alert -

1. A database event that we want to monitor, an insert or an update event on a specific database table.
2. A SQL Select query/statement which fetch specific database information as a result of that database event.
3. Define actions which we want alert to perform as a result of the database event.
An action can entail sending someone an email, submit a concurrent program, operating script and SQL statement script running,. We include all the actions which are we want Oracle Alert to perform, in an action set.

2. Periodic Alert:-
A periodic alert checks the information according to a schedule we define.
Periodic alerts are triggered/fired hourly, daily, weekly, monthly or yearly based on schedule we define or input.
When we create an periodic alert -

1. A SQL Select query/statement that retrieves specific database information.
2. Define or set frequency that you want the periodic alert to run the SQL statement.
3. Define actions which we want alert to perform as a result of the database event.
An action can entail sending someone an email, submit a concurrent program, operating script and SQL statement script running,. We include all the actions which are we want Oracle Alert to perform, in an action set.

Query to Find Suppliers With Banking Details

SELECT ap.segment1 Vendor_number,       
              ap.vendor_name,
            aps.vendor_site_code,
            aps.ADDRESS_LINE1,
            aps.ADDRESS_LINE2,
            aps.ADDRESS_LINE3,
            aps.CITY ,
            aps.STATE ,
            aps.country,
            (select tax_reference
               from hz_parties
              where party_id = ap.party_id) Tax_Registration_No,
            ieb.bank_name,
            ieb.Bank_Number,
            ieba.bank_account_name,
            ieba.bank_account_num,
            ieba.iban iban_Number,
            iebb.bank_branch_name,
            iebb.eft_swift_code,
            iebb.branch_number,
            iebb.bank_branch_type,
            ieb.country
  FROM ap_suppliers ap,
           ap_supplier_sites_all aps,
           iby_ext_bank_accounts ieba,
           iby_account_owners iao,
           iby_ext_banks_v ieb,
           iby_ext_bank_branches_v iebb
 WHERE 1=1
   AND ap.vendor_id = aps.vendor_id
   AND iao.account_owner_party_id = ap.party_id
   AND ieba.ext_bank_account_id = iao.ext_bank_account_id
   AND ieb.bank_party_id = iebb.bank_party_id
   AND ieba.branch_id = iebb.branch_party_id
   AND ieba.bank_id = ieb.bank_party_id
   AND iao.primary_flag = 'Y'