Oracle Order Management - Version 11.5.10.2 and later
Oracle Receivables - Version 11.5.10.0 and later
Information in this document applies to any platform.

Purpose

To understand and troubleshoot Order Management integration with Receivables.

Troubleshooting Steps

Overview of Integration

Oracle Order Management inserts invoice data from Orders and Returns into the Receivables Interface tables through the workflow activity 'Invoice Interface’. 'Invoice Interface' is executed by running the workflow background process. The code that performs this action is in file OEXPINVB.PLS.

The data flow of an Order Management Transaction (Sales Order, Return Material Authorization, etc) being transferred to a Receivables Transaction (Invoice, Credit Memo, etc) is:

Once an order is "Fulfilled", the order lines that are invoice eligible are transferred to the AR interface tables. Workflow background picks eligible records and posts them to AR interface tables.
Sales order line status is closed after the Sales Order data is interfaced to AR.
When Order line is closed from Order Management the invoice interface code updates the invoiced quantity and invoice_interface_status_code for the order lines.
Once all the data is transferred to Interface tables the AutoInvoice program transfers the data from the interface tables into the Receivable Tables.

Oracle Order Management inserts information into the following interface tables:

RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS_ALL
AR_INTERFACE_CONTS_ALL --- Release 12+ only for customer acceptance data
RA_INTERFACE_ERRORS_ALL - stores information about interface data that failed validation.

After the data is loaded into the interface tables, Receivables picks up the data from the interface tables through the 'Autoinvoice' concurrent program to create the Invoices/Credits of these Orders. During the 'Autoinvoice' concurrent program the interface data processed and loaded INTO the following Receivables tables:

RA_CUSTOMER_TRX_ALL - cust_trx_id is primary key to link it to trx_lines table and trx_number is the invoice number. The column INTERFACE_HEADER_ATTRIBUTE1 will have the Order Number.
RA_CUSTOMER_TRX_LINES_ALL - line_attribute_1 and line_attribute_6 are linked to order number and line_id of the orders respectively.
RA_CUST_TRX_LINE_SALESREPS_ALL - Stores Sales Credit Information
AR_LINE_CONTS_ALL -- Release 12 only for custom acceptance data. Stores the information about the lines that have contingencies attached to them.

How to View the Created Receivables Transaction

The Invoice created can be seen using the Receivables responsibility.

Navigation: Receivables Super User > Transactions > Transactions

Query with the Order Number as Reference.

What data/Columns are populated into AR interface tables from OM

For details, refer to the Oracle? Order Management Open Interfaces, API, & Electronic Messaging Guide > ‘Understanding the Receivables Interface Tables' section.

INVOICE_INTERFACE_STATUS_CODE = NOT_ELIGIBLE In OE_ORDER_LINES_ALL

If the invoice_interface_status_code of an order line is updated as "Not eligible" for the order line in oe_order_lines_all, here are the conditions that would result in an order line being not eligible for invoice interface:

1. The invoiceable item flag or invoice enabled flag for the item attributes in Inventory setup for the item is set to 'No.
2. Item inactivated: The STATUS of the Item in Inventory Item setup is INACTIVE.
3. Internal order lines are not invoiceable. Source_document_type_id for internal Order lines in oe_order_lines_all is 10 and 0 for regular order. OM does not interface internal order lines to AR. The invoice_interface_status_code for internal order lines in oe_order_lines_all is set to NOT_ELIGIBLE. Refer to Note 1357030.1 How to Invoice for Internal Sales Order?
4. Included items are not invoiceable.
5. Service items with non-serviceable parent are not invoiceable: This means that the serviceable product flag for the parent item is set to 'N'. If it not set, it defaults to 'Y'.
6. Configuration items are not invoiceable.
7. If line flow does not include invoice interface activity, the invoice_interface_status_code will never be set and remain null.
8. For RMA order Lines: Profile option OM: Generate credit for rejected returns if set to 'No'. The profile option OM: Generate credit for rejected returns allows users to choose if customer should be given credit for the "Rejected Quantity during RMA Receipt". If the profile is set to "Yes", when a Deliver Transaction is done in Inventory for the Rejected Qty for a RMA Line, customer will be given credit for the Rejected Qty. If this is set to N then line will not interface to Receivables and the invoice_interface_status_code will be NOT_ELIGIBLE in oe_order_lines_all and line will close without interfacing any data to Receivables. Note this profile option is only applicable for the 'Line Flow - Return for Credit with receipt'. From Diagnostics Order Information output (Note 133464.1), check if deliver transaction is rejected under Receiving trxn details; it will be shown as an follows:

     Line             Trxn Id          Trxn Id Type     Trxn    Date     Dest Type  Status
     ================ ================ ================ ======= ========= ========= ========
     1192745          6905527          6905090          DELIVER 18-MAR-11 INVENTORY REJECTED

You can also use Is_Line_Invoiceable.sql to determine exactly why a line was not invoiceable.
Execute the script from sqlplus prompt [as user APPS]. This script will take the Line_Id as input parameter. You can get the Line_Id of the Sales Order Line through front end via following navigation:
- Query the Sales Order from Sales Order form.
- Go to Line Items tab.
- Place your cursor on problematic Sales Order Line
- Go to Help > Diagnostics > Examine For Block = LINE, Select the Field as LINE_ID and note down the value.
Check the Script output and correct the setup for future transactions.
Note that there will not be any datafix for these transactions as the application behavior, what you are observing, is intended and is as per the setup. If at all you want to generated the Invoice/Credit Memo, generate it manually.

Examining Invoice related Data in OE_ORDER_LINES_ALL

To examine data for the Order lines related to Invoicing, specifically for the columns

SOURCE_DOCUMENT_TYPE_ID - provides if this is Internal or Regular order line

LINE_CATEGORY_CODE - provides if this Regular Or return Line

ITEM_TYPE_CODE - provides Item type like standard, Service, Included.

INVENTORY_ITEM_ID - providesInternal Item Id of the Item

SHIP_FROM_ORG_ID - provides Warehouse on the Order Line.

INVOICED_QUANTITY - provides Quantity Invoiced If the line is interfaced to AR.

INVOICE_INTERFACE_STATUS_CODE - provides invoice status if the Line Interfaced to AR.

Run following select for all lines of an Order:

Select line_id, line_number, source_document_type_id, LINE_CATEGORY_CODE , ITEM_TYPE_CODE , inventory_item_id, ship_from_org_id, invoiced_quantity, invoice_interface_status_code

FROM oe_order_lines_all where header_id in (select header_id from oe_order_headers_all

WHERE order_number = 2345) -- replace XXX with of an Order.

Run following select for specific order lines of an order:

Select line_id, line_number, source_document_type_id, LINE_CATEGORY_CODE , ITEM_TYPE_CODE , inventory_item_id, ship_from_org_id, invoiced_quantity, invoice_interface_status_code

FROM oe_order_lines_all

WHERE line_id in ( xxx, xxxx, xxxxx) -- replace XXX with ine_id's of an Order.

Examining the Item Attributes for Items in MTL_SYSTEM_ITEMS_B

The Invoice attributes of an Item in Inventory can be seen using the INVENTORY Responsibility.

Navigation: INVENTORY Responsibility > Setup > Items > Organization Items
Query with the item Number.

The following select statements can be used also:

SELECT Organization_code , organization_name , Organization_id

FROM ORG_ORGANIZATION_DEFINITIONS

WHERE Organization_id = &Enter value of SHIP_FROM_ORG_ID -- from select of order line

SELECT inventory_item_id, organization_id,description , Enabled_flag, invoiceable_item_flag, invoice_enabled_flag

FROM mtl_system_items_b

WHERE inventory_item_id in (&Enter value of INVENTORY_ITEM_ID from query in A)

and organization_id in (&Enter value of Organization_id from query above )

order by inventory_item_id, organization_id;

The above query results will provide the information on ENABLED_FLAG (which is status of item), invoiceable_item_flag, invoice_enabled_flag for the Item in the warehouse (SHIP_FROM_ORG_ID ) specified on the Order line.

OM System Parameters Related to Invoicing

Navigate to the System Parameters window: Order Management > Setup > System Parameters

Credit Memo Transaction Type - This value is transferred to Receivables if no value is defined for the credit memo Receivables transaction type associated with the Inbound Order Line OM transaction type and either: The Order Header has an OM transaction type of Mixed or The Receivables transaction type associated with the Order Header OM transaction type is NULL.

Credit Salesperson for Freight on Sales - This parameter determines whether to credit the Salesperson on the invoicing line or order header for freight charges when the freight charges are treated as revenue. The default value is No.

Inventory Item for Freight - This parameter is used only when the freight item is passed as revenue line. If you set the value to Inventory Item then the Invoicing module passes this item for freight charges, which will be treated as revenue lines.

Invoice Freight as Revenue - If the Receivables profile option TAX: Allow Tax Code Override is set to YES, and this parameter value is also set to YES, then freight charges are treated as revenue lines, and the Invoicing module will pass VAT tax and associated sales credits for processing. The default value is No.

Invoice Source - This parameter value is transferred to Receivables if the Invoice Source value is null for your transaction type at Order Line and null at the Order Header level.

Invoice Transaction Type - This parameter value is transferred to Receivables if no value is defined for the Receivables transaction type associated with the Outbound OM order line transaction type and OM order Header transaction type.

Non-Delivery Invoice Source - This system parameter value is transferred to Receivables if the OM: Invoice Numbering Method profile option is set to Delivery and the line is non-shippable.

Over shipment Invoice Basis - This parameter determines whether to invoice the ordered quantity or the shipped quantity for an over shipment.

Show Discount Details on Invoice - This parameter determines whether the discount details are passed to Oracle Receivables for printing on an invoice. Default value is No. If you set this parameter to No, then Extended Amounts will includes discounts.

Generating Debug Log Workflow background process to review invoice data interfaced to AR

There are 2 ways to generate the debug log file of the workflow background process.

From UI

Set the om debug level profile to 5 at site level.
Run Workflow back ground process for OM order line with Deferred mode set to YES and Time out no, process stuck = NO. Once this completes stop the workflow background process. This will push all existing records that are in deferred status for OEOL. Make sure to stop the all WF background processes that are scheduled with Deferred Mode = YES.
Enter an order with the lines details. Book, Pick and Ship confirm the order.
Now run the workflow background process manually for OM order line with Deferred mode set to YES and Time out no, process stuck = NO.
Collect the log file of this request when it completes.

From back-end

The point (4) above can also be executed from backend and log file collected.

---Running following script in sql plus or toad or sqldeveloper.

set serveroutput on size 500000;

DECLARE

l_file_val VARCHAR2(260);

BEGIN

oe_debug_pub.debug_on;

oe_debug_pub.initialize;

l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');

OE_DEBUG_PUB.SETDEBUGLEVEL(5);

DBMS_OUTPUT.PUT_LINE(' Log file : '||l_file_val);

DBMS_OUTPUT.PUT_LINE('Output Debug File is stored at :'||OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);

Dbms_Output.put_line('Setting the Org context');

--un-comment following statement If it is release 11 i

--fnd_client_info.set_org_context('XXX'); --Substitute value of xxx by org_id.

--un-comment following statement If it is release 12.

--mo_global.set_policy_context('S',XXX); --Substitute value of xxx by org_id.

--Call to workflow Background process--

wf_engine.background(itemtype=> 'OEOL', minthreshold=>Null, maxthreshold=>Null,

process_deferred=>TRUE, process_timeout=>FALSE, process_stuck=>FALSE);

oe_debug_pub.debug_off;

EXCEPTION

WHEN OTHERS THE

DBMS_OUTPUT.PUT_LINE('Error in base Script: '||SQLERRM);

OE_DEBUG_PUB.ADD('### Error in the base script : '||sqlerrm);

oe_debug_pub.debug_off;

END;

/

Identifying the Orders/ Order Line with Missing Invoice data

For Order lines that have invoiced_quantity and invoice_interface_status_code updated correctly in oe_order_lines_all but no invoicing data is found in AR tables ra_customer_trx_lines_all and ra_interface_lines_all, following select can be used to identify all such order lines and a data fix be provided to re-interface the Order lines to AR.

Also verify fix for following known issue has been applied: Overlapping PRICE_ADJUSTMENT_ID And LINE_ID Will Not Invoice Order Line Note 563044.1

SELECT l.line_id, h.order_number

FROM oe_order_lines_all l,

oe_order_headers_all h,

mtl_system_items_b itm

WHERE l.HEADER_ID = h.HEADER_ID

AND l.flow_status_code = 'CLOSED'

AND l.open_flag = 'N'

AND l.line_category_code IN ('ORDER', 'RETURN')

AND l.invoice_interface_status_code is NOT Null

AND NVL(l.invoiced_quantity, 0) > 0

AND l.item_type_Code != 'INCLUDED'

AND NVL(h.SOURCE_DOCUMENT_TYPE_ID ,0) != 10

AND l.inventory_item_id = itm.inventory_item_id

AND l.ship_from_org_id = itm.organization_id

AND NVL(itm.INVOICEABLE_ITEM_FLAG,'N') = 'Y'

AND NVL(ITM. INVOICE_ENABLED_FLAG,'N') = 'Y'

and h.Creation_DATE >= to_date('01-JAN-2012', 'DD-MON-YYYY')

AND NOT EXISTS

(

SELECT 1

FROM ra_customer_trx_lines_all rac

WHERE rac.interface_line_attribute6 = TO_CHAR(l.line_id)

AND rac.sales_order = h.order_number

AND rac.sales_order_line IS NOT NULL

)

AND NOT EXISTS

(

SELECT 1

FROM ra_interface_lines_all rai

WHERE rai.interface_line_attribute6 = TO_CHAR(l.line_id)

AND rai.sales_order = h.order_number

AND rai.SALES_ORDER_LINE IS NOT NULL

)

AND NOT EXISTS

(

SELECT 1

FROM wf_items

WHERE ITEM_TYPE='OEOL'

AND ITEM_KEY=TO_CHAR(l.LINE_ID)

AND END_DATE IS NULL

);

When are the RA_INTERFACE tables purged?

AutoInvoice Purge program purges records from Interface tables once the interface status for the lines is 'P' (successfully processed by the Auto Invoice program). There are two ways to invoke the AutoInvoice Purge program: Automatically and Manually. You can refer to Note 1127413.1 Understanding and Troubleshooting AutoInvoice Purge Interface Tables for additional details.

Automatically

By checking Purge Interface Tables checkbox in the System Options form, you are allowing AutoInvoice to clean up after itself once it has completed processing.Responsibility: Receivables Manager, Navigation: Setup > System > System Options.

Manually

Submitting via the Submission Request form, gives you the flexibility of choosing when to purge processed data from the Interface tables.
Responsibility: Receivables Manager > Navigation: Interfaces > AutoInvoice > Requests > Submit a New Request > Single Request > AutoInvoice Purge Program

References

NOTE:1563415.1 - Order Management Invoice Interface FAQ
NOTE:563044.1 - Overlapping PRICE_ADJUSTMENT_ID And LINE_ID Will Not Invoice Order Line
NOTE:396121.1 - How to Avoid One Shipment Creating Two Invoices
NOTE:373209.1 - How to force service lines to invoice with the shippable lines they reference? Placing these lines in fulfillment set doesn't work
NOTE:790150.1 - Non-Shippable Orders Are Getting Fulfilled Even When There is a Hold

AUTOINVOICE 3

Applies to:

Purpose

Troubleshooting Steps

Overview of Integration

How to View the Created Receivables Transaction

What data/Columns are populated into AR interface tables from OM

INVOICE_INTERFACE_STATUS_CODE = NOT_ELIGIBLE In OE_ORDER_LINES_ALL

Examining Invoice related Data in OE_ORDER_LINES_ALL

To examine data for the Order lines related to Invoicing, specifically for the columns

Run following select for all lines of an Order:

Run following select for specific order lines of an order:

Examining the Item Attributes for Items in MTL_SYSTEM_ITEMS_B

OM System Parameters Related to Invoicing

Generating Debug Log Workflow background process to review invoice data interfaced to AR

From UI

From back-end

Identifying the Orders/ Order Line with Missing Invoice data

When are the RA_INTERFACE tables purged?

Automatically

Manually

References