Bookmark and Share Subscribe You may face visibility problem if http://docs.google.com is blocked on your network

Tuesday, May 6, 2008

Data flow for Order-to-Cash 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
    • 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 TRX_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.
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

----------------------------------------------------------------------------------------------

70 comments:

  1. excellent... they way you delivered helped both a functional as well as Technical guys to understand the basic flow. Can you please update more on different business cases and setups.

    Thanks
    Kaushik

    ReplyDelete
  2. hey there,

    just want to say that this is an excellent blog.. thanks!

    ReplyDelete
  3. hey i just want to say that this is an excellent blog~

    ReplyDelete
  4. Wonderful job, very nice. exactly i am looking for this.

    ReplyDelete
  5. How About if backordered, please update.
    Thanks DEVendra Gulve for the wonderful blog.

    ReplyDelete
  6. Hi Khaled,
    Following are the changes which will happen if you backorder the line/s
    1. In WSH_DELIVERY_DETAILS table:
    MOVE_ORDER_LINE_ID becomes NULL as mover order gets canceled
    RELEASED_STATUS becomes 'B' from 'Y'

    2. In WSH_DELIVERY_ASSIGNMENTS table:
    DELIVERY_ID becomes NULL

    3. In WSH_NEW_DELIVERIES table:
    STATUS_CODE becomes 'CL' from 'OP'

    4. Record/s from MTL_RESERVATIONS table gets deleted

    This is not all... there would be other changes also in table MTL_ONHAND_QUANTITIES, also if the item is serial controlled then there would be changes in those tables also.
    Keep searching...

    Thanks
    Devendra

    ReplyDelete
  7. Hi,

    Can you please explain me in detail about step 3,( which conc. program is used to do reservations)

    Cheers
    Ramana

    ReplyDelete
  8. Excellent one. This will be very helpful for both functional and technical guys

    ReplyDelete
  9. That was a brilliant article I have ever found on O2C cycle, great work.....

    Vipin

    ReplyDelete
  10. Great post! Thanks...

    ReplyDelete
  11. good one

    srikanth

    ReplyDelete
  12. Hi Dev,
    I need to know what the meaningof different values in apps.wsh_delivery_details.inv_interfaced_flag

    ReplyDelete
  13. Since inventory handles shipping updates, these flags get populated by the
    Shipping Transactions function, and are also usually a part of the
    WSH_DELIVERY_DETAILS status as well. In any case, these are the normal
    definitions applied to those single letter abbreviations:

    'Y','Y=Staged',
    'R','R=Ready to Release',
    'S','S=Rel to Warehouse',
    'B','B=Backorder',
    'P','P=Pending Inv',
    'C','C=Shipped',
    'N','N=Not Ready',
    'D','D=Cancelled',
    'X','X=Not Applicable

    NULL indicates either the record is still in some early state and not ready
    to commit a record or is not a shippable or transactable item. (It can also
    mean a missing record, if it has appropriate attributes and has progressed
    to where a workflow activity should have provided a status).

    For details check out doc ID 207780.1 on metalink

    ReplyDelete
  14. Hi Dev,

    Thanks for the GR8 article. Can you go further beyond OM and give the details of the Cash Cycle as well.

    Thanks Again.
    Anas MS.

    ReplyDelete
  15. This is really good one.. keep it up..

    Vijay

    ReplyDelete
  16. Greate Itis very useful in understanging the flow
    thanks

    ReplyDelete
  17. can we have the same kind of data flow of procure to purchase

    ReplyDelete
  18. hai devendra..great work..
    keep it up.. I expect more
    articles like this fro you...

    ReplyDelete
  19. HI,
    Great job, Can you please send me different accounting entries generated in O2C cycle.
    Thanks,
    Sasikala

    ReplyDelete
  20. Very Nice article.

    Ashish wakale

    ReplyDelete
  21. This is an excellent article...something so brief and yet informative is very hard to find. Thanks

    ReplyDelete
  22. Wow, this is a master piece… mind blowing article I have to say.
    Its our (readers') privilege to have such kind of document which gives so much of information in such a concise manner and simplest way.

    Christina

    ReplyDelete
  23. Dev,
    your blog is very helpful.
    Thanks allot.

    ReplyDelete
  24. Excellent.. In R12, Can you explain me where ct. acceptance comes into picture and also when revenue is recognized i.e., whether we are considering ct. acceptance before generating Invoice or after generating invoice? Please explain to me.

    ReplyDelete
  25. Hi Deva,
    Excellent document for O2C.I need one clarification.From which statge , Move order is created? What is difference bet move order and transction move order? Kindly explain.

    Thanks and Regards
    Senthil

    ReplyDelete
  26. Hi Senthil,
    When we try to do Pick Release, we basically do two steps:
    1. Request required material to be transferred from stored to shipping area (or Staging sub-inventory). This is done by raising a Move Order. Mover order is a document based on which store keeper transfers the material.
    2. Actual/Physical movement of the requested material. When the material is issued on the basis of Move Order and shifted to staging sub-inventory then this transaction should be registered in system, which is done by doing Transact Move Order.

    Hope this answers are query.

    Thanks
    Devendra

    ReplyDelete
  27. Thanks Davendra.so these 2 steps are done in Pick release stage.

    Regards
    Senthil

    ReplyDelete
  28. Can you please provide the detail steps in both technical & functional aspects when :-

    1.Drop shipment occured
    2.In case of RMA

    ReplyDelete
  29. can anyone help in writing the query for internal orders status. whertein i require two feilds.
    "Location_A_IO_Transit" it should show the Sum of qty that is shipped from any other locations for location_A and that is not delivered(or taken into inventory)

    "Location_A_IO_Reserved" it should show the Sum of qty that is reserved from any other locations for location_A and that is not shipped(or reserve qty for the Locatio_A from any other locations)

    ReplyDelete
  30. Amazing Article Sir .. greatest detail possible .. ! :) Thank you very much for such a beautiful writing ...

    Venki

    ReplyDelete
  31. Hi Devendra,

    I am having a situation where sales credit information is not exsiting in some transactions. Now revenue accounting is faling for no sales credit in RA_CUST_TRX_LINE_SALESREPS. Is there a way you can insert lines into this table after the fact the invoice is transferred to GL. definitely it will be back end update.

    Thanks,

    Piyush

    ReplyDelete
  32. Very nice document for technical persons

    ReplyDelete
  33. An order was created with both source and destination org as the same - 'US'. The destination org should ahve been Columbus. I tried fixing by doing the following:
    - Updated PO_REQUISITION_LINES_ALL with the correct destination org.
    - Update mtl_transactions_interface with transfer_subinventory and transfer_locator.

    I am still getting the error "|Transaction failed to transfer reservation|INV_XFR_RSV_FAILURE|Failed to update reservation|Update Reservation failed.|Warning: For Delivery Detail 1"

    Has anyone come across this error?

    ReplyDelete
  34. Has anyone come across the error in MTI table
    "|Transaction failed to transfer reservation|INV_XFR_RSV_FAILURE|Failed to update reservation|Update Reservation failed.|Warning: For Delivery Detail 1"

    Please help me resolve this error in mtl_transactions_interface table

    ReplyDelete
  35. Great post!!
    Many Thanks.

    I have an issue.
    One order has file linse, Order header is booked and all 4 lines are closed except one. One line status showing "Picked".

    As far as I know, once the order get booked then it also insert the value in WSH delivery details table. all the 4 lines are available in this table except one.
    Can someone assist?

    ReplyDelete
  36. Great post!!
    Many Thanks.

    I have an issue.
    One order has file linse, Order header is booked and all 4 lines are closed except one. One line status showing "Picked".

    As far as I know, once the order get booked then it also insert the value in WSH delivery details table. all the 4 lines are available in this table except one.
    Can someone assist?

    ReplyDelete
  37. I believe, you are not able to find record in WSH for the line with 'Picked' status.
    It is not possible to pick the line with out having record in WSH tables, please check whether anybody has access to delete it manually or not. If nobody has access, then Oracle support will be the best people to help you in this.

    Thanks
    Devendra

    ReplyDelete
  38. Hello,

    Looking for some assistance. I have 3 consumer swap orders, lines status of the order is showing shipped instead of closed. Workflow for the line is complete.

    Can you assist?

    ReplyDelete
  39. Hi Neeraj,
    Check out status of interface trip stop program for this order. or try running it again for this delivery or STOP_ID.
    Id these are inventory item also check INV_INTERFACED_FLAG in wsh_delivery_details table, this flag shall be 'Y' or 'X' (X for phantom item).

    ReplyDelete
  40. Hi Devendra,

    In Ship Confirm step, data never deleted from wsh_new_deliveries table.Could you please check and remove this clause from article? It can misguide to anyone.

    Thanks,
    Anand Jaisawal

    ReplyDelete
  41. Hello Anand,
    Thanks a lot for your suggestion, I removed the line now.

    Thanks
    Devendra

    ReplyDelete
  42. Some of my order lines are not in WSH_DELIVERY_DETAILS even though the order is booked..??

    ReplyDelete
  43. Please check whether those lines are eligible for shipping? Also check their status in Shipping Transactions form.

    Thanks
    Devendra

    ReplyDelete
  44. Excellent Post. Thanks.

    Sankar.N

    ReplyDelete
  45. This is awesome, Could you please do the same for P2P cycle in R12 please.
    Keep it up..God bless you friend..

    ReplyDelete
  46. wonderful work, really appreciate the efforts you put to arrange such important information,

    Thanks,

    ReplyDelete
  47. Hi I went through this document and it is simply awesome .. everything has been explained very clearly

    However I have one doubt ... I have lines in booked status in OM but still they are getting picked up when i m using api WSH_PICKING_BATCHES_PUB.CREATE_BATCH and subsequently releasing using WSH_PICKING_BATCHES_PUB.RELEASE_BATCH... how is this possible that we are able to pick lines which are still not in awaiting shipping status in OM

    ReplyDelete
  48. Hi Deva ,

    Excellent Work ..
    Keep It Up.

    Thanks

    ReplyDelete
  49. Hi Deva.
    I have DELIVERY_ID. With the help of that id how can i retrieve excise data?
    Please help

    Rahul

    ReplyDelete
  50. Hi Rahul,
    Get DELIVERY_DETAIL_ID which is associated with DELIVERY_ID from table WSH_DELIVERY_ASSIGNMENTS.
    Get order line ID (SOURCE_LINE_ID)associated with DELIVERY_DETAIL_ID from table WSH_DELIVERY_DETAILS. Then based on Order line ID search for Order and look for excise details.
    Hope this answers your question.

    Thanks
    Devendra

    ReplyDelete
  51. hi devendra,
    wht the knowledge u sahre with us is always keeps u in my mind.nice work.u have to share a lot.please give me the interview related question on o2c

    ReplyDelete
  52. Dear Dev,

    Really good work, you have out done the other article on same subject.

    I have one question.

    If i need to know that in past lets say 6 months before what was my onhand/available quantity for an item at that time?

    1. Is it possible to know the onhand/available quantity 6 months before??
    2. If Yes, Could you provide table/column level info/query to calculate it.

    Regards
    SMSA

    ReplyDelete
  53. Hi SMSA,
    Try this report "Transaction historical summary" from Inventory.

    Thanks
    Devendra Gulve

    ReplyDelete
  54. Hi it a very useful naration to functional people. Thank u Dev

    ReplyDelete
  55. Hey DEVendra, very nice article, but I am not clear about step 7 and 8.... which program is updating these values. oe_order_lines_all.Flow_Status_Code = Shipped is already done in step 6 wat happens in step 8 then?

    any information on ITS - which tables gets affected after it is completed?
    Thanking you in anticipation. Hope you will answer ASAP
    Pia

    ReplyDelete
  56. Hey Devendra,
    Thanks a lot for this blog. It is very useful to me who is a newcomes to the Oracle EBS world. I have a question about PICK CONFIRM DATE. What is the query to get the PICK CONFIRM DATE ? Appreciate your response.

    ReplyDelete
  57. Hi Devendra,

    Is there a way to do partial pick release.
    Basically what I need to achieve.
    If the required quantity is 10 for a specific Item and there is only 8 available then it should get pick relesaed for 8 and the remaning 2 should get back ordered.
    If so what are the steps to achieve the same.

    Thanks
    Jin

    ReplyDelete
  58. You can certainly split the delivery and specify qty as 8 at the time of pick release.
    Else same can be done at the time of Pick Confirm (transact Move Order)

    Thanks
    Devendra

    ReplyDelete
  59. Hi Devendra,

    We have a requirement to pull the lot numbers for which pick release has happened. But we are unable to find any lot_number details in the WSH_DELIVERY_DETAILS , MTL_RESERVATIONS or in any other table which gets affected once pick release is done. Can you let me know if there's any other table that might have the lot_number detail post pick release. Thanks in advance.

    Regards,
    Karthik.

    ReplyDelete
  60. Hi Devendra,

    We have a requirement to pull the lot numbers for which pick release has happened. But we are unable to find any lot_number details in the WSH_DELIVERY_DETAILS , MTL_RESERVATIONS or in any other table which gets affected once pick release is done. Can you let me know if there's any other table that might have the lot_number detail post pick release. Thanks in advance.

    Regards,
    Karthik.

    ReplyDelete
  61. Hi Karthik,
    Check in MTL_TRANSACTION_LOT_NUMBERS in reference of TRANSACTION_ID.

    Thanks
    Devendra Gulve

    ReplyDelete
  62. Hi Devendra,

    Thanks alot for the info and your time. Let me try it out and get back to you with my feedback.

    Regards,
    Karthik.

    ReplyDelete
  63. Hi Devendra,

    For the previous question from Karthik, you had said that lot_numbers can be pulled from MTL_TRANSACTION_LOT_NUMBERS in reference of TRANSACTION_ID. But WSH_DELIVERY_DETAILS table is giving many transaction_id for one particular source_header_id. In this case how do i get the lot_number of an order/delivery that has been pick released,pick confirmed and ship confirmed. Thanks.

    ReplyDelete
  64. Hey, nice site you have here! Keep up the excellent work!

    ReplyDelete
  65. Wonderful article on same subject at below link
    http://amaralam.blogspot.in/2013/06/order-to-cash-cycle-in-oracle-apps.html

    ReplyDelete

Please add your valuable feedback / comments

Declaimer:

This blog is purely personal and the thoughts expressed here represent only me. The purpose of this blog is to share information and knowledge about Oracle's product which I have come across with my exposure to the product, practice and observations. The blog has been created keeping only one intention of sharing knowledge and for learning purpose. The blog has been created solely as a educational, for storing portions of the vast Oracle knowledge world. Oracle EBS is an Oracle Corp. product and you should contact Oracle directly for any specific fact or issue.

*NOTE: Few articles on this blog are not completely prepared by me, content is edited and complied after referring various sites to make visitor's job easy