Troubleshooting Grouping Rule Issues When Using AutoInvoice in AR

1.One Credit Memo Is Created When Expecting Several

After importing credit memo records, you find that they are grouped together on one credit memo transaction.  You are expecting the records to be imported as two separate transactions.  The problem is that the grouping rule was defined only for the invoice transaction class. 

Review the Grouping Rule defined, to see what has been selected for Transaction Class.  You can do this using the following navigation:

Responsibility: Receivables Manager
Navigation: Setup > Transactions > Autoinvoice > Grouping Rules

Note that the Transaction Class is Invoice in the following example of a defined Grouping Rule:

Now navigate to the Class field and use the down arrow to determine what (if any) other classes are defined for this grouping rule:

Then use the List of Values (LOV) to select the Credit Memo Transaction Class.

Now use the LOV in the Optional Grouping Characteristics field to select the desired column.  For this example we chose INTERFACE_LINE_ATTRIBUTE1:

After applying the changes, the Grouping Rule now looks like this:

In order for a grouping rule to work, it must be defined for each transaction class (Invoice, Credit Memo, and/or Debit Memo), based on the types of transactions being imported.

2. Multiple Invoice Transactions Created When Expecting One

    a. Consider the Mandatory and Optional Grouping Attributes

You are importing a sales order that has several sales order lines. The sales order is split into several invoice transactions after executing AutoInvoice.

There are a few reasons why this might happen.  First, a custom grouping rule could be assigned to the Transaction Source.  When optional grouping attributes are applied, this increases the chances for grouping into separate transactions.   Secondly, if there are no optional grouping attributes defined for a grouping rule, then one of the sales order lines has a mandatory grouping attribute that is different then the other sales order lines.   The following will demonstrate both examples:

Example 1 - Additional Optional Grouping Attributes are Defined

AutoInvoice uses the following hierarchy to default Grouping Rules, stopping when the first Grouping Rule is found:

1. From the Transaction Sources screen
2. From the customer profile at customer site level
3. From the customer profile at customer level
4. From the System Options

This order cannot be changed.  As a result, if you have customer specific grouping exceptions then you must define grouping rules at the customer or site level for all customers or set a default grouping rule in the system options form. 

Review the Transaction Source used to determine the Grouping Rule that has been assigned:

In this example the WSH GROUPING RULE is associated to the transaction source, Order Entry Delivery Invoicing, which is the transaction source used when the transactions were imported.

When we review the WSH GROUPING RULE, we see that an optional grouping attribute has been defined for the INTERFACE_LINE_ATTRIBUTE3 column.

Because of this additional attribute, the sales order lines are grouped by INTERFACE_LINE_ATTRIBUTE3.  If any one sales order line has a different value for INTERFACE_LINE_ATTRIBUTE3, then it is grouped into a separate transaction.


Example 2 - Sales Order Line Has Mandatory Grouping Attribute That Differs From Other Sales Order Lines
Usually this issue is identified in the early stages of implementation when testing the AutoInvoice process.  To help verify the issue, you can use the script below to review the sales order lines before importing.

Notes:

1. For this example INTERFACE_LINE_ATTRIBUTE1 is used as the driving column since sales orders imported from Oracle Order Management use this column for the sales order number.   The query selects all of the Mandatory Grouping Attribute columns (as noted later in this document).  So you can change the where condition as needed, if you are importing from a legacy system or other source.
   
2. Different releases of Oracle Receivables may introduce changes to the Mandatory and Optional grouping attributes, so please carefully review the User Guide for your release to determine the proper grouping attributes.
   
   You may also use the select statements provided in step 4 below to query the database for the grouping attributes.

/*
Test for Mandatory Grouping Attribute Columns Only
Please note that the attributes listed below are for Release 11.5 
*/

Select AGREEMENT_ID,
COMMENTS,CONS_BILLING_NUMBER,
CONVERSION_DATE,CONVERSION_RATE,CONVERSION_TYPE,
CREDIT_METHOD_FOR_ACCT_RULE,
CREDIT_METHOD_FOR_INSTALLMENTS,
CURRENCY_CODE, CUSTOMER_BANK_ACCOUNT_ID,
CUST_TRX_TYPE_ID, DOCUMENT_NUMBER,
DOCUMENT_NUMBER_SEQUENCE_ID,
GL_DATE, HEADER_ATTRIBUTE1,
HEADER_ATTRIBUTE2,HEADER_ATTRIBUTE3,
HEADER_ATTRIBUTE4,HEADER_ATTRIBUTE5,
HEADER_ATTRIBUTE6,HEADER_ATTRIBUTE7,
HEADER_ATTRIBUTE8,HEADER_ATTRIBUTE9,
HEADER_ATTRIBUTE10,HEADER_ATTRIBUTE11,
HEADER_ATTRIBUTE12,HEADER_ATTRIBUTE13,
HEADER_ATTRIBUTE14,HEADER_ATTRIBUTE15,
HEADER_ATTRIBUTE_CATEGORY,
HEADER_GDF_ATTRIBUTE1,HEADER_GDF_ATTRIBUTE2,
HEADER_GDF_ATTRIBUTE3,HEADER_GDF_ATTRIBUTE4,
HEADER_GDF_ATTRIBUTE5,HEADER_GDF_ATTRIBUTE6,
HEADER_GDF_ATTRIBUTE7,HEADER_GDF_ATTRIBUTE8,
HEADER_GDF_ATTRIBUTE9,HEADER_GDF_ATTRIBUTE10,
HEADER_GDF_ATTRIBUTE11,HEADER_GDF_ATTRIBUTE12,
HEADER_GDF_ATTRIBUTE13,HEADER_GDF_ATTRIBUTE14,
HEADER_GDF_ATTRIBUTE15,HEADER_GDF_ATTRIBUTE16,
HEADER_GDF_ATTRIBUTE17,HEADER_GDF_ATTRIBUTE18,
HEADER_GDF_ATTRIBUTE19,HEADER_GDF_ATTRIBUTE20,
HEADER_GDF_ATTRIBUTE21,HEADER_GDF_ATTRIBUTE22,
HEADER_GDF_ATTRIBUTE23,HEADER_GDF_ATTRIBUTE24,
HEADER_GDF_ATTRIBUTE25,HEADER_GDF_ATTRIBUTE26,
HEADER_GDF_ATTRIBUTE27,HEADER_GDF_ATTRIBUTE28,
HEADER_GDF_ATTRIBUTE29,HEADER_GDF_ATTRIBUTE30,
INITIAL_CUSTOMER_TRX_ID,
INTERNAL_NOTES, INVOICING_RULE_ID,
ORIG_SYSTEM_BILL_ADDRESS_ID,
ORIG_SYSTEM_BILL_CONTACT_ID,
ORIG_SYSTEM_BILL_CUSTOMER_ID,
ORIG_SYSTEM_SHIP_CONTACT_ID,
ORIG_SYSTEM_SHIP_CUSTOMER_ID,
ORIG_SYSTEM_SOLD_CUSTOMER_ID,
ORIG_SYSTEM_BATCH_NAME,
PAYMENT_SET_ID, PREVIOUS_CUSTOMER_TRX_ID,
PRIMARY_SALESREP_ID, PRINTING_OPTION,
PURCHASE_ORDER, PURCHASE_ORDER_DATE,
PURCHASE_ORDER_REVISION, REASON_CODE,
RECEIPT_METHOD_ID,RELATED_CUSTOMER_TRX_ID,
SET_OF_BOOKS_ID,TERM_ID,
TERRITORY_ID,TRX_DATE,
TRX_NUMBER
From RA_INTERFACE_LINES_ALL
where INTREFACE_LINE_ATTRIBUTE1 = '&sales_order';

 If Optional Grouping Attributes are used, then you can use the following query, in addition to the one above:

/*
Test for Optional Grouping Attribute Columns Only
Please note that the attributes listed below are for Release 11.5
*/

Select ACCOUNTING_RULE_DURATION,
ACCOUNTING_RULE_ID,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3,
INTERFACE_LINE_ATTRIBUTE4,
INTERFACE_LINE_ATTRIBUTE5,
INTERFACE_LINE_ATTRIBUTE6,
INTERFACE_LINE_ATTRIBUTE7,
INTERFACE_LINE_ATTRIBUTE8,
INTERFACE_LINE_ATTRIBUTE9,
INTERFACE_LINE_ATTRIBUTE10,
INTERFACE_LINE_ATTRIBUTE11,
INTERFACE_LINE_ATTRIBUTE12,
INTERFACE_LINE_ATTRIBUTE13,
INTERFACE_LINE_ATTRIBUTE14,
INTERFACE_LINE_ATTRIBUTE15,
INTERFACE_LINE_CONTEXT,
INVENTORY_ITEM_ID,
REFERENCE_LINE_ID,
RULE_START_DATE,
SALES_ORDER,
SALES_ORDER_DATE,
SALES_ORDER_LINE,
SALES_ORDER_REVISION,
SALES_ORDER_SOURCE,
TAX_CODE,
TAX_RATE
FROM RA_INTERFACE_LINES_ALL
where INTERFACE_LINE_ATTRIBUTE1 = '&sales_order'

When you execute either of the above queries, the output allows you to compare the sales order lines by column to verify the values assigned to the attributes.  You can use SQL*DEVELOPER to review the output or save the output to a spreadsheet to verify the same.

The following reflects the output from SQL*DEVELOPER for the Mandatory Grouping Attributes for one sales order:

Note:  The TRX_DATE values are different for each record of the two sales order lines. In this case each record will be grouped as a separate invoice transaction.

 

    b. Consider the frequency in which AutoInvoice is executed

When scheduling the AutoInvoice process to execute on a frequent basis, you should consider the timing as to when the interface tables are populated from a source system.

For instance, if you schedule the AutoInvoice process to execute every 5 minutes, you need to consider when the sales orders lines are interfaced from Order Management for a complete sales order.   If it takes 10 minutes for all sales order lines to be processed and interfaced to Receivables, then the AutoInvoice process should be scheduled to execute every 15 minutes.  

Considering the interval of the scheduled AutoInvoice process, will ensure that all sales order lines are imported together.  Otherwise a sales order can be invoiced on multiple invoices when the AutoInvoice process is executed before all sales order lines are interfaced to Oracle Receivables.

3. Order Management - Line Transaction Flexfield

What are the Order Management Details that are interfaced to Receivables in the Line Transaction Flexfield?

Note: These columns can be grouped using the Optional Grouping Attributes

Column Name	Order Management Value
INTERFACE_LINE_ATTRIBUTE1	Sales Order Number
INTERFACE_LINE_ATTRIBUTE2	Order Type (Type Name)
INTERFACE_LINE_ATTRIBUTE3	For Shipped Line: WSH_NEW_DELIVERIES.DEL.NAME For Non-Shipped: Non-Shipped: Order Line Return Line: 0 (zero) Freight Charges: Delivery Name Discount Lines: 0 (zero)
INTERFACE_LINE_ATTRIBUTE4	For Shipped Line: substr(DEL.WAYBILL,1,30) Non-Shipped Order Line: 0 (zero) Freight Charges: substr(DEL.WAYBILL,1,30) Discount Lines: 0 (zero)
INTERFACE_LINE_ATTRIBUTE5	For Sales Order Lines and Return Lines:  The number of times the order or return line has been interfaced for invoice or credit. Freight Charges: 1 (one)
INTERFACE_LINE_ATTRIBUTE6	Sales Order Lines, Return Lines, Discount Lines: LINE.LINE_ID Freight Charges: Charge.Charge_id
INTERFACE_LINE_ATTRIBUTE7	0
INTERFACE_LINE_ATTRIBUTE8	Shipped Order Line: WSH_DOCUMENT_INSTANCES.SEQUENCE_NUMBER Non-Shipped Order Line: 0 (zero) Return Lines: 0 (zero) Freight Charges: 0 (zero) Discount Lines: 0  (zero)
INTERFACE_LINE_ATTRIBUTE9	Customer Item Number if one is defined, otherwise insert 0 (zero).
INTERFACE_LINE_ATTRIBUTE10	Null
INTERFACE_LINE_ATTRIBUTE11	Discount Lines: PA.PRICE_ADJUSTMENT_ID
INTERFACE_LINE_ATTRIBUTE12	Sales Order Lines, Return Lines, and Discount Lines: LINE.SHIPMENT_NUMBER Freight Charges: None
INTERFACE_LINE_ATTRIBUTE13	Sales Order Lines, Return Lines, and Discount Lines: LINE.OPTION_NUMBER Freight Charges: None
INTERFACE_LINE_ATTRIBUTE14	Sales Order Lines, Return Lines, and Discount Lines: LINE.SERVICE_NUMBER Freight Charges: None
INTERFACE_LINE_ATTRIBUTE15	Null

For more details regarding the Order Management Interfaces, review the Oracle Order Management Open Interfaces, API, and Electronic Messaging Guide, page 8-6.

4. Mandatory And Optional Grouping Attribute Columns

The following provides a list of mandatory and optional attributes from the RA_INTERFACE_LINES_ALL table.

Note: 

• Some attributes have changed between Release 11.5 and 12.x
• An Enhancement Request (5943921) has been logged requesting the REASON_CODE is removed from the Mandatory Grouping Attributes. 

   a. Release 12.x, Mandatory Grouping Attributes

Note:
Grouping attribute behavior changed between Release 11.5., and Release 12.
Note 1280194.1, Why Autoinvoice Ship to fields were mandatory grouping attributes in release 11i, but are optional in release 12?

AGREEMENT_ID
APPLICATION_ID
BILLING_DATE
COMMENTS
CONS_BILLING_NUMBER
CONTRACT_ID
CONVERSION_DATE
CONVERSION_RATE
CONVERSION_TYPE
CREDIT_METHOD_FOR_ACCT_RULE
CREDIT_METHOD_FOR_INSTALLMENTS
CURRENCY_CODE
CUSTOMER_BANK_ACCOUNT_ID
CUST_TRX_TYPE_ID
DEFAULT_TAXATION_COUNTRY
DOCUMENT_NUMBER
DOCUMENT_NUMBER_SEQUENCE_ID
DOCUMENT_SUB_TYPE
GL_DATE
HEADER_ATTRIBUTE1-15
HEADER_ATTRIBUTE_CATEGORY
HEADER_GDF_ATTRIBUTE1-30
HEADER_GDF_ATTR_CATEGORY
INITIAL_CUSTOMER_TRX_ID
INTERNAL_NOTES
INVOICING_RULE_ID
LEGAL_ENTITY_ID
ORIG_SYSTEM_BILL_ADDRESS_ID
ORIG_SYSTEM_BILL_CONTACT_ID
ORIG_SYSTEM_BILL_CUSTOMER_ID
ORIG_SYSTEM_SOLD_CUSTOMER_ID
PAYMENT_ATTRIBUTES
ORIG_SYSTEM_BATCH_NAME
PAYMENT_SET_ID
PREVIOUS_CUSTOMER_TRX_ID
PRIMARY_SALESREP_ID
PRINTING_OPTION
PURCHASE_ORDER
PURCHASE_ORDER_DATE
PURCHASE_ORDER_REVISION
REASON_CODE
RECEIPT_METHOD_ID
RELATED_CUSTOMER_TRX_ID
SET_OF_BOOKS_ID
TAXED_UPSTREAM_FLAG
TERM_ID
TERRITORY_ID
TRX_DATE
TRX_NUMBER

   b. Release 12.x, Optional Grouping Attributes

ACCOUNTING_RULE_DURATION
ACCOUNTING_RULE_ID
ATTRIBUTE1-15
ATTRIBUTE_CATEGORY
CODE_COMBINATION_ID
INTERFACE_LINE_ATTRIBUTE1-15
INTERFACE_LINE_CONTEXT
INVENTORY_ITEM_ID
LINE_GDF_ATTRIBUTE1-20
LINE_GDF_ATTR_CATEGORY
ORIG_SYSTEM_SHIP_ADDRESS_ID
ORIG_SYSTEM_SHIP_CONTACT_ID
ORIG_SYSTEM_SHIP_CUSTOMER_ID
REFERENCE_LINE_ID
RULE_START_DATE
SALES_ORDER
SALES_ORDER_DATE
SALES_ORDER_LINE
SALES_ORDER_REVISION
SALES_ORDER_SOURCE
TAX_CODE
TAX_RATE

   c. Release 11.5, Mandatory Grouping Attributes

AGREEMENT_ID
COMMENTS
CONS_BILLING_NUMBER
CONTRACT_ID
CONVERSION_DATE
CONVERSION_RATE
CONVERSION_TYPE
CREDIT_METHOD_FOR_ACCT_RULE
CREDIT_METHOD_FOR_INSTALLMENTS
CURRENCY_CODE
CUSTOMER_BANK_ACCOUNT_ID
CUST_TRX_TYPE_ID
DOCUMENT_NUMBER
DOCUMENT_NUMBER_SEQUENCE_ID
GL_DATE
HEADER_ATTRIBUTE1-15
HEADER_ATTRIBUTE_CATEGORY
HEADER_GDF_ATTRIBUTE1-30
INITIAL_CUSTOMER_TRX_ID
INTERNAL_NOTES
INVOICING_RULE_ID
ORIG_SYSTEM_BILL_ADDRESS_ID
ORIG_SYSTEM_BILL_CONTACT_ID
ORIG_SYSTEM_SHIP_CUSTOMER_ID
ORIG_SYSTEM_SOLD_CUSTOMER_ID
ORIG_SYSTEM_SOLD_CUSTOMER_ID
ORIG_SYSTEM_BATCH_NAME
PAYMENT_SET_ID
PREVIOUS_CUSTOMER_TRX_ID
PRIMARY_SALESREP_ID
PRINTING_OPTION
PURCHASE_ORDER
PURCHASE_ORDER_DATE
PURCHASE_ORDER_REVISION
REASON_CODE
RECEIPT_METHOD_ID
RELATED_CUSTOMER_TRX_ID
SET_OF_BOOKS_ID
TERM_ID
TERRITORY_ID
TRX_DATE
TRX_NUMBER

   d. Release 11.5, Optional Grouping Attributes

ACCOUNTING_RULE_DURATION
ACCOUNTING_RULE_ID
ATTRIBUTE1-15
ATTRIBUTE_CATEGORY
INTERFACE_LINE_ATTRIBUTE1-15
INTERFACE_LINE_CONTEXT
INVENTORY_ITEM_ID
REFERENCE_LINE_ID
RULE_START_DATE
SALES_ORDER
SALES_ORDER_DATE
SALES_ORDER_LINE
SALES_ORDER_REVISION
SALES_ORDER_SOURCE
TAX_CODE
TAX_RATE

   e. Scripts to identify grouping attributes

For a comprehensive list of all Mandatory and Optional Grouping Attributes used in your instance, you can run the following:

-- for MANDATORY

select column_type, from_column_name
from ra_group_by_columns
where column_type = 'M';

-- for OPTIONAL

select column_type, from_column_name
from ra_group_by_columns
where column_type <> 'M';

To see a list of fields that will be used by the grouping rule for a particular transaction type, you can use the following scripts:

First, Determine the value to use in &group_rule_id below by running the following:

SELECT GROUPING_RULE_ID
FROM RA_GROUPING_RULES
WHERE NAME = '&Enter Grouping Rule Name';

For example, using the value = 'DEFAULT' will result in GROUPING_RULE_ID = -1

Then run the following replacing &group_rule_id with the value returned by previous script and &trx_type with INV for Invoice, CM for Credit Memo and DM for Debit Memo:

SELECT UPPER(C.FROM_COLUMN_NAME),
C.FROM_COLUMN_LENGTH
FROM RA_GROUPING_TRX_TYPES T,
RA_GROUP_BYS B,
RA_GROUP_BY_COLUMNS C
WHERE T.GROUPING_RULE_ID = &group_rule_id
AND T.CLASS = DECODE('&trx_type',
'INV', 'I',
'CM', 'C',
'DM', 'D')
AND T.GROUPING_TRX_TYPE_ID = B.GROUPING_TRX_TYPE_ID
AND B.COLUMN_ID = C.COLUMN_ID
UNION
SELECT UPPER(C.FROM_COLUMN_NAME),
C.FROM_COLUMN_LENGTH
FROM RA_GROUP_BY_COLUMNS C
WHERE C.COLUMN_TYPE = 'M'
order by 1;

By using the script above, you can identify which fields need to be similar for interface rows to be grouped into a single transaction.

For more information about Grouping Rules, you can also review the Receivables User's Guide, page 2-241.

5. Intercompany Limitations

Is it possible to split two intercompany lines from one order with unique delivery numbers into separate invoices?
Answer:  No - Order management does not pass delivery number to receivables interface tables in any field and thus grouping rules cannot take this into account during invoice creation.

6. What Happens if One Line is Rejected?

Let's assume the Batch Source is set to Invalid Line: REJECT INVOICE in the batch source definition.  Is it possible to reject the entire invoice or is the only option to fix the problem and generate a second invoice?

If you state that you want to import (despite rejected lines), what you are really saying is that you want to import what you can of the transaction despite any rejections that occur to individual lines. However, once the transaction has been grouped, a
rejection on any one line will reject the entire transaction. So, in other words, the feature favors rejecting entire post-group transactions.  In cases such as when a Customer ID is invalid, the customer is part of the grouping criteria and thus if you have a 5 line invoice rejected for invalid customer ID's and then you update 2 of the 5 lines to correct the error and re-run, AutoInvoice will group the two corrected lines into an invoice and import the record, leaving three lines unprocessed.

7. How Commitment Lines are Grouped

In Lines attached to commitments the reference_line_id will populate Initial_customer_trx_id which is a mandatory grouping column.  Lines without commitments have this field as null.  Sales orders subsequently will always  split commitments into separate invoices from non-commitment order lines.