Search over 500+ 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

16 comments:

  1. Column Mapping from Oracle Order Management and Oracle Receivables:

    How can I change this setup for the column mappings?

    Regards,
    Shantanu

    ReplyDelete

  2. 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. 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. 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. 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. 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. 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. 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. Why you want to associate a Debit memo with OM Order ?
    Can you explain your requirement little more.

    ReplyDelete

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

Subscribe to: Post Comments (Atom)