ORDER TO CASH (O2C) Process Flow with Effected Tables in ORACLE EBS R12

LET LOOK the FLOW PART FOR O2C 

• Order  Entry : This is first stage , When the order is entered in the system 
• Order Booking: This is next stage , When the order is booked then the flow status changed from entered into Booked
• Pick Release : Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick then particular sales order 
• Pick confirm / Move order Transaction : Items transferred from Source sub inventory into staging sub inventory
• Ship Confirm: Items are loaded in Truck / Transportation Mode for the delivery to customers 
• Invoice Generation : Invoice is generated
• Close Order 

O2C - TABLE FLOW STATUS 

=> OE_ORDER_HEADERS_ALL - Flow Status Code

1. ENTERED
2. CANCELLED
3. CLOSED
4. BOOKED

=> OE_ORDER_LINES_ALL - Flow Status Code

1. ENTERED
2. REPRICE_COMPLETE
3. CANCELLED
4. AWAITING_SHIPPING
5. CLOSED
6. BOOKED
7. INVOICE_HOLD
8. AWAITING_FULFILLMENT
9. REPRICE_PRICING_ERROR

=> WSH_DELIVERY_DETAILS - Released Status

1. B:Backordered
2. C:Shipped 
3. D:Cancelled 
4. N:Not ready to release 
5. R:Ready to release 
6. S:Released to warehouse 
7. X:Not Applicable 
8. Y:Staged

Step 1; Creating / Entered New Sales Order 

Select * from OE_ORDER_HEADERS_ALL where  ORDER_NUMBER = '830001659'; 

select * from OE_ORDER_HEADERS_ALL where Header_ID = 21009209;

select Flow_status_code from OE_order_Headers_all 

                                where Header_ID = 21009209; -->ENTERED STATUS

select Flow_status_code from OE_ORDER_LINES_ALL 

                               where Header_ID = oe_header_id -->ENTERED 

Step 2; Booked the Sales Order 

Once Order is booked or confirmed then 

select Flow_status_code from OE_order_Headers_all 

                                where Header_ID = 21009209; -->BOOKED STATUS

select Flow_status_code from OE_ORDER_LINES_ALL 

                               where Header_ID = oe_header_id -->AWAITING_SHIPPING

select * from WSH_DELIVERY_DETAILS

                                 where source_header_ID =   OE_HEADER_ID  

select released_status from WSH_DELIVERY_DETAILS

                              where source_header_ID =   OE_HEADER_ID  --> R - READY To RELEASE

Step 3; Release Sales order / Pick Release , Pick Confirm (Staging Location)

Once Release order and pick release some concurrent program trigger out 

1. Pick Selection List Generation 

2. Pick Slip Report ( We can get Move Order Number) 

Select * from MTL_TXN_REQUEST_HEADERS 

            where Request_Number= '<<Move Oder Number(getting from PICKSLIP Report)>>';

Select * from MTL_TXN_REQUEST_LINES where HEADER_ID = '<<Header_ID>>';

Select * from WSH_DELIVERY_DETAILS 

               where Released_Status = 'S';  -- Released to warehouse (Pick Release)

Select * from WSH_DELIVERY_DETAILS

           where Released_Status = 'Y'; -- Staging 

(Pick Confirm , Line has been released to inventory for Processing)

Select * from WSH_picking_batches; --- After batch is created for pick release.

Select * from MTL_Reservations 

             where Inventory_Item_ID = '<<MTL_ITEM_ID>>'; -- Soft move , Not for physical Move

select * From WSH_NEW_DELIVERIES 

             where Deliverd_ID = '<<Delivery_ID- wsh_delivery_assignments>>';

Step 4; Ship Confirm

once ship confirmation done, then it will be triggered out 5 concurrent programs 

1. Bill of Landing , 

2. packing Slip Report , 

3. Commerical Invoice ,

4. Vechile Load Sheet , 

5. Interface Trip stop 

Select * from WSH_DELIVERY_DETAILS where Released_Status = 'C';  -- Shipped

Select flow_status_Code from OE_ORDER_LINES_ALL 

                                       where header_ID = 'Oe_header_ID';  --> Shipped Status

Select * FROM MTL_REVERSATIONS 

               WHERE Inventory_Item_ID = 'ITEM_ID'; --> will be FREEZE

Step 5 : Auto Invoice: 

After Successful completed the respective CC program then run the work flow background process

to Move AR module and Generate Invoice 

 -- >>  RUN WorkFlow - 'WORK FLOW BACKGROUND PROCESS ' INTERFACE 

 --.>>  Auto Invoice Program 

  select * from RA_Interface_lines_all 

                 where Interface_LINE_ATTRIBUTE1 ='ORDER_NUMBER';

  Select * from RA_Interface_Distributions_all 

                  where Interface_Line_ID  ='LINE ID'; 

Step 6 : Account Receivable:

   Select * FROM RA_CUSTOMER_TRX_ALL 

                   Where TRX_NUMBER = 'Invoice_number';

   Select * FROM RA_CUSTOMER_TRX_lines_All 

                   where Customer_Trx_ID = '';

Step 7 : General Ledger:

  Select * From GL_JE_BATCHES Where Name ='Receivables A 155 etc';

  Select * From GL_JE_Headers where JE_Batch_ID ='';

  Select * from GL_JE_LInes where JE_Header_ID = '';

SAMPLE QUERY FOR JOIN BETWEEN  OM, WSH, AR TABLES

SELECT ooh.order_number ,

  ool.line_id ,

  ool.ordered_quantity ,

  ool.shipped_quantity ,

  ool.invoiced_quantity ,

  wdd.delivery_detail_id ,

  wnd.delivery_id ,

  rctl.interface_line_attribute1 ,

  rctl.interface_line_attribute3 ,

  rctl.interface_line_attribute6 ,

  rct.org_id ,

  rct.creation_date ,

  trx_number ,

  rctl.quantity_ordered ,

  rct.interface_header_context

FROM oe_order_headers_all ooh ,

  oe_order_lines_all ool ,

  wsh_delivery_details wdd ,

  wsh_new_deliveries wnd ,

  wsh_delivery_assignments wda ,

  ra_customer_trx_all rct ,

  ra_customer_trx_lines_all rctl

WHERE ooh.header_Id               =ool.header_id

AND wdd.source_header_id          =ooh.header_id

AND wdd.delivery_detail_Id        =wda.delivery_detail_id

AND wda.delivery_id               =wnd.delivery_id

AND rctl.interface_line_attribute1=TO_CHAR(ooh.order_number)

AND rctl.interface_line_attribute6=TO_CHAR(ool.line_id)

AND rctl.interface_line_attribute3=TO_CHAR(wnd.delivery_id)

AND rctl.customer_trx_id          =rct.customer_trx_id;

--      AND rct.interface_header_context='ORDER ENTRY'

/

Example query linking MTL_MATERIAL_TRANSACTIONS to the move order:

SELECT mmt.transaction_id,

  tol.organization_id,

  toh.request_number,

  toh.header_id,

  tol.line_number,

  tol.line_id,

  tol.inventory_item_id,

  toh.description,

  toh.move_order_type,

  tol.line_status,

  tol.quantity,

  tol.quantity_delivered,

  tol.quantity_detailed

FROM mtl_txn_request_headers toh,

  mtl_txn_request_lines tol,

  mtl_material_transactions mmt

WHERE toh.header_id     = tol.header_id

AND toh.organization_id = tol.organization_id

AND tol.line_id         = mmt.move_order_line_id

AND toh.request_number  = '&EnterMONumber' 

/

SQL Statement to find out the Drop Ship SO and corresponding Requistion and PO details along with their status

select ooh.order_number

       ,ool.ordered_item

       ,ool.ordered_quantity

       ,ooh.flow_status_code header_status                   

       ,ool.flow_status_code line_status

       ,prha.segment1 requisition

       ,poh.segment1 po_number

       ,poh.closed_code po_status

       ,pll.quantity

       ,pll.quantity_received

       ,pll.closed_code po_shipment_status

fromapps.oe_order_headers_all                   ooh

    ,apps.oe_order_lines_all                     ool

    ,apps.oe_drop_ship_sources                   odss

    ,apps.po_requisition_headers_all             prha

    ,apps.po_headers_all                         poh

    ,apps.po_lines_all                           pol

    ,apps.po_line_locations_all                  pll

              

whereool.header_id = ooh.header_id

and   odss.header_id = ooh.header_id

and   odss.line_id = ool.line_id

and   prha.requisition_header_id = odss.requisition_header_id

and   poh.po_header_id = odss.po_header_id

and   pol.po_line_id = odss.po_line_id

and   pol.po_header_id = poh.po_header_id

and   pll.po_line_id = pol.po_line_id

and   ooh.order_number = ‘89899’;

Oracle EDI Application Transaction Code

EDI module provide standard interface for transactions with your trading partner.

Purchase Order:-

Transaction Code	Transaction Name	Direction	EDIFACT
832	Price/Sales Catalogue	Inbound	PRICAT
843	Response to Request for Quotation	Inbound	QUOTES
856	Ship Notice/Manifest	Inbound	DESADV
857	Shipment and Billing Notice	Inbound	-
824	Application Advice	Outbound	APERAK
850	Purchase Order	Outbound	ORDERS
860	Purchase Order Change	Outbound	ORDCHG

Order Management:-

Transaction Code	Transaction Name	Direction	EDIFACT
850	Purchase Oder	Inbound	ORDERS
860	Purchase Oder Change	Inbound	ORDCHG
855	Purchase Oder Acknowledgment	Outbound	ORDRSP
865	PO Change Acknowledgment	Outbound	ORDRSP

Oracle Payables:-

Transaction Code	Transaction Name	Direction	EDIFACT
810	Invoice	Inbound	INVOIC
857	Shipment and Billing Notice	Inbound	-
824	Application Advice	Outbound	APERAK
820	Payment Order/Remittance Advice	Outbound	PAYORD-REMADV PAYEXT-REMADV

Oracle Receivables:-

Transaction Code	Transaction Name	Direction	EDIFACT
810	Invoice	Outbound	INVOIC
812	Credit Memo/Debit Memo	Outbound	CREADV/DEBADV

Process Manufacturing:-

Transaction Code	Transaction Name	Direction	EDIFACT
850	OPM: Purchase Order	Inbound	ORDERS
855	OPM: Purchase Order Acknowledgment	Outbound	ORDRSP
856	OPM: Ship Notice/Manifest	Outbound	DESADV

EDI Transaction Support:-

The following transactions are supported:

ASC X12	EDIFACT	Document ID	Description
Inbound Transactions
810	INVOIC	INI	Inbound Invoice
832	PRICAT	CATI	Inbound Price / Sales Catalog
843	QUOTES	RRQI	Inbound Response to Request for Quote
850	ORDERS	POI	Inbound Purchase Order
856	DESADV	ASNI	Inbound Ship Notice / Manifest
857	No equivalent	SBNI	Inbound Shipping and Billing Notice
Outbound Transactions
824	APERAK	ADVO	Outbound Application Advice
810	INVOIC	INO	Outbound Invoice
820	PAYORD / REMADV	PYO	Outbound Payment Order / Remittance Advice
830	DELFOR	SPSO	Outbound Planning Schedule
862	DELJIT	SSSO	Outbound Shipping Schedule
850	ORDERS	POO	Outbound Purchase Order
860 865	ORDCHG ORDCHGACK	POCO POCOACK	Outbound Purchase Order Change Request Outbound Order Change Acknowledgment
856	DESADV	DNSO	Outbound Ship Notice / Manifest

Oracle - E- Business – E-Commerce - EDI

EDI Stands for Electronic Data Interchange.

EDI is the electronic data exchange the information between trading partners. Data files are exchanged in a standard format to minimize manual efforts, speed data processing and ensure accuracy.

EDI, electronic funds transfer (EFF), electronic mail and fax as increasingly being used to schedule operations, streamline order fulfillment and optimize cash flow

 Simple EDI Business Flow:-

1.       Customer creates the Purchase Order (PO) for purchase the items.

2.       Then customer send the PO through mail or fax  to supplier

3.       The supplier receive the PO and valid the PO requirement information

4.       Next supplier Enter/Create the Sales Order  on this System

5.       The supplier send the Sales order acknowledge to customer through fax or mail.

6.       The Supplier shipped the items to the customer .when the items shipped, the supplier arise Invoices to the customer.

7.       The customer makes the payment based on invoice.

Let us imagine, how much information needs to transfer the data and tracked run the business, cost, process times and data errors etc., using Intermediate communication link - EDI performing effective way of business

Organizations that send or receive documents between each other are referred to as "trading partners" in EDI terminology. Trading partners are free to use any method for the transmission of documents. Few of the transmission mediums used are Value Added Network (VAN), Internet/AS and Web EDI.

EDI translation software, popularly known as Translator, validates the partner and checks if the data received meets the standard formats defined before converting it to a desired file format which can be read and imported by the receiver's computer systems (for e.g. ERP).

EDIFACT & X12 are most widely followed EDI standards. The standards prescribe the formats, character sets, and data elements used in the exchange of business documents and forms.

EDI Standard Exchange format:

X12 - The American National Standards Institute (ANSI) chartered the Accredited Standards Committee (ASC) X12 to develop uniform standards for industry electronic exchange of business transactions-electronic data interchange (EDI)

UN/EDIFACT - United Nation Electronic Data Interchange for Administration, Commerce and Transport. UN/EDIFACT is an international EDI standard designed to meet the needs of both government and private industry.

The UN/EDIFACT Working Group (EWG), a permanent working group of the United Nations Centre for Trade Facilitation and Electronic Business (UN/CEFACT), develops and maintains UN/EDIFACT.

Hints: X12 is used in the USA but most of the rest of the world uses the EDIFACT transaction sets.

Components:

There are five process involved in EDI

ü  Sender

ü  Receiver

ü  Language

ü  Content

ü  Medium

In EDI, Sender and Receiver are called as trading partner (like customer and vendors). X12 or EDIFACT standards supply a common language for formatting the information content of common message

Terminology used in EDI:

Ø  Transaction Set - Business document transmitted between companies (e.g. Purchase Order).

Ø  Data Segment - Units of data used to communicate specific information within a message (e.g. the header).

Ø  Data Element - Specific unit of data within a data segment.

Ø  Trading Partner - Entity such as a customer, supplier, or organization with whom electronic commerce is executed.

Ø  EDI Standards - Agreed to formats for exchanging electronic data (typically developed by representatives of industry verticals).

Ø  Translator - Software application that translates data into or out of a specific format or standard for processing by an ERP system

Oracle EDI e-Commerce Gateway – Functionality

• Define Trading Partner Relationships
• Define Code Conversions
• Customize Flat File Formats (like xml,xls,.csv etc)
• Use Standard Report Submission
• Initiate Extract Process
• Initiate Import Process
• View Extract/Import Process Status