Search 800 + Posts

Nov 18, 2010

Data flow Between Oracle Order Management and Oracle Receivables

This and Post are about data flow between Oracle order Management and Oracle Receivable.

This post is very focus on the Invoices in AR from the Order Management and not about the invoices in AR from any other source.
Oracle Order Management is Workflow driven and almost all the workflow in Order management has Invoicing Activity, and while executing the code in the Invoicing activity, OM dump the data in RA_INTERFACE_LINES_ALL and other related Interface tables, and eventually when we run the Auto Invoice it created Invoices in AR.

As I have stated almost all workflow in the Order Management has Invoicing activity , though some exceptions like Ship Only workflow ,these workflow don’t have Invoicing activity.

Invoice Interface activity in Oracle Order Management workflow collects order, return, freight charges, discounts and sales credits information from Oracle Order Management tables, and transfer the information to Oracle Receivables and creates
  1.  Invoices
  2.  Credit Memo
  3.  Debit Memo

 Interface tables that Oracle Order management Populated in Receivables

  1.  RA_INTERFACE_LINES_ALL
  2.  RA_INTERFACE_SALESCREDITS_ALL
  3.  RA_INTERFACE_ERRORS_ALL 
Column Mapping from Oracle Order Management and Oracle Receivables

Interface_Line_Context              values from profile option OM: Source Code

Interface_Line_Attribute1          Order Number

Interface_Line_Attribute2          Transaction Type

Interface_Line_Attribute3          Delivery Name (if Required) , for Non Shippable Line it is 0

Interface_Line_Attribute4          waybill

Interface_Line_Attribute5

Interface_Line_Attribute6          Order Line ID

Interface_Line_Attribute7

Interface_Line_Attribute8           Bill of Lading

Interface_Line_Attribute9          Customer Item Num (If Required)


 Cases When Invoice/Credit Memo/Debit Memo Not Created

  1.  Invoice Enabled item property is set to NO or Invoicable item property is set to NO.
  2.  Line is part of an internal order.
  3.  Line is a service line and the servicable_product_flag for parent is set to NO.
  4.  Line is entered using Included Item
  5.  Referenced returns with CONFIG/INCLUDED item type code.
Types of Dataflow from Oracle Order management transfer to Oracle Receivables

 Header Level Interface – In this case data from Oracle Order management transfer to Oracle Receivables only when all the lines of Order are Closed. In such scenario there will not be any Invoicing data transfer from OM to AR at order Line level. Basic purpose of this Interface is to create only 1 Invoice for all the sales Order lines.

Line Level Interface – in this case data from Oracle Order management transfer to Oracle Receivables as soon as the Invoicing activity at the Order line level is executed. Basic purpose of this Interface is to created only 1 Invoice for all I order lines, unless some Grouping rules has defined.


 Delivery Based Invoice Interface – in this case All lines in a Delivery are shipped together, so customers would like one Invoice per Delivery. So data from Oracle Order management transfer to Oracle Receivableswill transfer only when all the lines associated with the delivery shipped.

Grouping Rules

Grouping Rules - Grouping Rules decide which lines from the interface table in appear in one invoice

30 comments:

  1. AnonymousDecember 14, 2010 at 9:13 PM

    Column Mapping from Oracle Order Management and Oracle Receivables:

    How can I change this setup for the column mappings?

    Regards,
    Shantanu

    ReplyDelete
  2. eoracleappsDecember 14, 2010 at 11:14 PM

    Hi Shantanu

    very Good question.Please note that the above Mapping is for the Invoices created for the Sales Order Created in Order Management Or any Source with Source Name as ORDER ENTRY or
    Delivery Based Invoices ( Not sure about exact name).
    this is a fixed mapping for these 2sources and you can't change this ( Reason - it is used by Internal APIs while dumping data from OM to AR)

    ReplyDelete
  3. eoracleappsDecember 14, 2010 at 11:17 PM

    with continuatation to my previous update please note that ,you can define you own Transaction flexfield (in case you are importing invoices and then populate the Transaction type flexfield based on your requirements).But any thing that come from OM will have fixed mapping defined by oracle.

    Do you have any buiness reason to change that mapping

    ReplyDelete
  4. AnonymousJanuary 12, 2012 at 10:01 AM

    Hi

    In continuation to question raised by Shantanu as above, we have a business case where on sales order we have defined 'Additional Line Attribute Information'DFFs which bring profile number, length and other order related values. These needs to be brought to AR 'Invoice Line Information' DFFs. These values needs to be printed on the Invoice.

    Please advice

    Regards
    Kavitha

    ReplyDelete
  5. eoracleappsJanuary 13, 2012 at 6:17 AM

    If I am not wrong , any thing that is captured in Additional Order Line DFF (ATTRIBUTE1....n ) should goes to Invoice Line DFF.

    Mapping that I have explained under "Column Mapping from Oracle Order Management and Oracle Receivables" is for Transaction Type DFF and not for Invoice Line DFF ie I am talking about Interface_Line_Attribute1....n and not for DFF attributes like ATTRIBUTE1....n.

    Please define your additional line attributes at order lines and then Process your Order ( but make sure that you have corresponding Invoice line DFF enabled).

    Hope that helps , Let me know if you have questions.

    ReplyDelete
  6. AnonymousMay 15, 2012 at 12:56 PM

    Hi, is there a way to supress sending any OM Order Line DFFs to AR Line DFFs. Currently, in OM, we can have 20 Attributes on Lines but in AR, there are only 15 attributes. Is there a way to re-map what attributes we can choose to send to AR?

    ReplyDelete
  7. AnonymousMay 16, 2012 at 6:23 PM

    How do you create debit memo's from sales orders ? Is there a way you can link the debit memo transaction type created in AR to the order management transaction type ?

    ReplyDelete
  8. eoracleappsMay 17, 2012 at 3:35 PM

    well you can write a database trigger on ra_interface_lines_all table to play around with the values in DFF table.

    also , if you run auto invoice CP one some particular time in day say in evening or say after every 3-4 hours then you can write a concurrent program that do the same thing I suggested above and schedule it to run just before auto invoice.

    In case you don't want to suppress the additional 5 attributes with above suggestions , let pass all the attributes to AR , but don't enable the DFF for these attributes.

    ReplyDelete
  9. eoracleappsMay 17, 2012 at 3:37 PM

    Why you want to associate a Debit memo with OM Order ?
    Can you explain your requirement little more.

    ReplyDelete
  10. UnknownApril 5, 2013 at 8:02 AM

    we want to upload our freight amounts in the DFF on the shipping transaction form ,because we ship confirm before we get the freight amount from the carriers .can this DFF be mapped to the freight line on the invoice ?

    thank you
    Preethi Anandula

    ReplyDelete
  11. Bizinsight Consulting IncApril 7, 2013 at 12:30 AM

    Yes that can be done, But required customization .

    Option #1
    Write a database trigger on RA_INTERFACE_LINES_ALL table and update the Freight line with the Value from DFF.

    Option #2
    Add a Workflow activity any where after ship Confirm and before Invoicing , and update Freight Charges ( There is some API to do that , but I am not able to recall API Name).


    But I have question , why you guys add the Freight Amount after the Ship Confirm .


    Hope that helps , feel free to reply back if you still looking for additional information

    Regards
    SR
    bizinsight consulting Inc

    ReplyDelete
  12. AnonymousApril 19, 2013 at 11:32 AM

    Hi

    Is there any field updated in order management tables to identify that AR invoice is created for the sales order.

    Thanks in advance
    Gibbs

    ReplyDelete
  13. Bizinsight Consulting IncApril 20, 2013 at 8:58 AM

    No there no as such filed in OM side that tell that Invoice got created in AR...But there are few options that you might like to considered

    1.Before populating the data into ra_interface_lins_all tables , OM populate the invoiced_quantity = Shipped quantity , so you can considered invoiced_quantity field , but there is catch , in case someone accidently updated/deleted the data in AR interfaced or Auto-invoiced failed , data in OM remain intact, so based on your business /process need you can try invoiced_quantity. Other option is used query listed in http://eoracleapps.blogspot.com/2010/04/queries-to-connect-racustomertrxall-and.html

    to join OM and AR tables

    ReplyDelete
  14. AnonymousApril 24, 2013 at 2:08 AM

    Is there are way of transferring the ship_method from OM to AR tables. Currently the Ship_via field only passes the carrier but not the entire ship_method. We require the entire ship_method of OM in AR

    ReplyDelete
  15. AnonymousApril 25, 2013 at 2:19 AM

    Hi
    What is the best way to get the entire ship_method (Carrier/Mode/SLA) into invoice lines during OM interface. Currently the Ship_via field only has the carrier details. We building a solution around the ship method and require that field on the invoice line or header. The seeded interface_Line_Attribute does not have ship_method field.

    ReplyDelete
  16. Bizinsight Consulting IncApril 29, 2013 at 1:43 AM

    I don't think there is any direct mapping exists from OM to AR for Shipping Method .

    You can use DFF to process this data from OM to AR.

    ReplyDelete
  17. AnonymousJuly 12, 2013 at 1:20 PM

    I am importing sales orders from a legacy system and need to generate the invoice and receipts. You mention the workflow moves the data to the OIT tables and I assume you need to kick off the AutoInvoice CP. What processes/steps or how to structure the sale orders in order for the workflow to move the data to the OIT tables? Can you provide some direction? Thanks

    ReplyDelete
  18. eoracleappsAugust 13, 2013 at 12:29 AM

    My apologies for late Reply , when workflow for order line reached the Invoice activity , it will insert data into OIT , you need not to do any thing .

    Once data is in OIT , we need to run autoinvoice concurrent program to process the data from OIT and created invoices in AR.Usually companies schedule to run the Autoinvoice CP after every 30/60 etc minutes based on volume of data.

    ReplyDelete
  19. AnonymousAugust 30, 2013 at 12:03 AM

    how can i see the all data of Order to cash in html page.

    ReplyDelete
  20. eoracleappsSeptember 1, 2013 at 9:37 PM

    You can run HTMonse12i.htm to see all the data from O2C cycle

    ReplyDelete
  21. AnonymousDecember 4, 2013 at 8:31 AM

    I want to pull the reference field from the OM header (because the order came from another source and has that source order number. How do I map that in the interface table?

    ReplyDelete
  22. eoracleappsDecember 14, 2013 at 10:37 PM

    In OM Order Header we have filed that starts with ORIG_ , please check those.

    ReplyDelete
  23. AnonymousJanuary 14, 2016 at 12:08 PM

    interface_line_attribute11 mapped to Price_adjustment_id in OM side

    ReplyDelete
  24. gowsalyaSeptember 8, 2019 at 11:14 PM

    Really nice experience you have. Thank you for sharing. It will surely be an experience to someone.
    uipath course ceritification

    ReplyDelete
  25. D. James AndersonOctober 19, 2019 at 5:06 AM

    There's nothing amiss with being a specialist in your specialty.outsource payroll singapore

    ReplyDelete
  26. saivenkatMay 6, 2020 at 10:13 PM

    I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.
    best quoting software

    ReplyDelete
  27. radhikaJuly 21, 2020 at 12:01 PM

    I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.
    AWS training in Chennai

    AWS Online Training in Chennai

    AWS training in Bangalore

    AWS training in Hyderabad

    AWS training in Coimbatore

    AWS training

    ReplyDelete
  28. KITS TechnologiesAugust 13, 2020 at 7:12 AM

    Definitely, what a fantastic website and informative posts, I will bookmark your website.Have an awsome day!
    Oracle DBA online training
    Oracle DBA training
    Oracle Enterprise Manager online training
    Oracle Enterprise Manager training
    Oracle Exadata online training
    Oracle Exadata training
    Oracle fusion order management online training

    ReplyDelete
  29. UnknownSeptember 20, 2020 at 11:51 PM

    Good Post! it was so good to read and useful to improve my knowledge as an updated one, keep blogging. After seeing your article I want to say that also a well-written article with some very good information which is very useful for the readers....thanks for sharing it and do share more posts like this.
    https://www.3ritechnologies.com/course/salesforce-training-in-pune/

    ReplyDelete

Subscribe to: Post Comments (Atom)