Troubleshooting AutoInvoice Customer, Address and Contact Errors and Exceptions (Doc ID 1097660.1)

转自：Document 1097660.1 (oracle.com)

Troubleshooting AutoInvoice Customer, Address and Contact Errors and Exceptions (Doc ID 1097660.1)

In this Document
Purpose
Troubleshooting Steps
     1. Customer Fields: Usage and Validation in AutoInvoice
         a. Transaction (Batch) Source Setup:  Value vs. ID vs. None
             i. Value
             ii. ID
             iii. None
         b. Sold To
         c. Bill To
         d. Ship To
         e. Paying Customer
     2. Troubleshooting Common Errors and Exceptions
         a. Validate Record(s) Using AutoInvoice Interface Data Diagnostic test
         b. Review AutoInvoice Execution Report
         c. Review the Failed Transactions
             i. Interface Lines Form
             ii. Interface Data Collection Scripts & Querying RA_CUSTOMER_TRX_LINES_ALL
         d. Review the Transaction Source to Check which Fields are Validated
         e. Make Certain the Values are Valid
         f. AutoInvoice will Import Invoices for Inactive Customers
         g. Review Common Exceptions and Errors in Section 3
     3. Common Customer, Address and Contact Errors
     4. Still Have Questions?
References

APPLIES TO:
Oracle Receivables - Version 11.5.10.0 and later
Information in this document applies to any platform.
Executable:RAXTRX - Autoinvoice Import Program

PURPOSE
Oracle Receivables (AR): AutoInvoice Information Center >Troubleshooting AutoInvoice for Oracle Receivables Release 11.5 Through 12 > Note 1097660.1

The purpose of this note is explain the usage of the Customer ID, Customer Contact and Customer Address fields in AutoInvoice (AI) and to provide step-by-step instructions to troubleshoot common errors and exceptions returned by AutoInvoice related to these fields.

This note should be used in conjunction with the following two documents:

    - Note 1067402.1, AutoInvoice Setup for Release 12: A Case Study Using a Simple Script
    - Note 1075757.1, Troubleshooting AutoInvoice for Oracle Receivables Release 11.5 Through 12

TROUBLESHOOTING STEPS

1. Customer Fields: Usage and Validation in AutoInvoice

AutoInvoice allows for imported transactions to be associated to three distinct parties.  These are:

• Sold To
• Bill To
• Ship To  

In addition to identifying the parties, the Bill To and Ship To also allow for an address (Site) and a contact to be passed.  A Paying Customer is derived by AutoInvoice and cannot be passed.

Section 1.a. explains the batch source setup and implications on customer fields in AutoInvoice.
Sections 1.b. - 1.d. below show the fields and usage for populating data in the RA_INTERFACE_LINES_ALL table. 

    a. Transaction (Batch) Source Setup:  Value vs. ID vs. None

For each of the fields the Party values that are generated on a Transaction in Receivables (AR), the Batch Source allows you to specify the column being populated.  For example, in this screenshot showing Bill To Contact, you can specify 'Value,' 'ID' or 'None.'  Descriptions on the impact of these selections provided below.

 

Note: Selecting 'Value' or 'ID' in your batch source setup does not always mean that these are required fields, rather it indicates what source AutoInvoice should use when importing to see if data exists.  As noted below, 'None' is used to force AI to skip data during the invoice creation steps.  Refer to sections 1.b.-1.d. below for the criteria and validation used for each column.

        i. Value

Select Value when you are passing a field mapped with a 'REF' suffix such as ORIG_SYSTEM_BILL_CONTACT_REF.  The value passed can be alpha-numeric and is intended to store the unique identifier from your legacy system.  Note that in order to use this field, you must have passed the REF value as part of the customer data feed using Customer Interface. 

Tip: Use the Reference fields as a way to maintain the association between your feeder system and the records within Oracle Receivables. This also helps in eliminating the need to create and maintain secondary mapping tables to associate customer information in your legacy system with the Oracle Customer Master. For more on reference fields, see the Customer Interface chapter in the Oracle Trading Community Architecture Reference Guide available through the OTN Documentation Library.

        ii. ID

Select ID when you are passing in the unique ID generated within Oracle for the record.  Refer to the validation sections in 1.b.-1.d. below to identify the source table to use for the ID.

        iii. None

None should be selected when you do not wish to import the column shown.  For columns where you occasionally have data (Contacts, Ship-To addresses, etc.), you should select the data type that you will pass in these cases. 
 

Important: Even if you set a field on a batch source to 'None' because you do not want to import this information into Receivables tables,  AutoInvoice might still validate the data and could reject the containing line(s) if that data is invalid.

    b. Sold To

The Sold To record typically stores the same customer record as the ORIG_SYSTEM_BILL_CUSTOMER_ID or ORIG_SYSTEM_BILL_CUSTOMER_REF, though this is not always the case.  This is therefore a required field that must be provided in one of the two columns noted below.  Determination of which is required is controlled by your Transaction Source setup (see the partial screenshot below).

AutoInvoice Column Name	Description	Optional or Required?	Validation
ORIG_SYSTEM_SOLD_ CUSTOMER_ID	This column specifies the Customer that made the purchase.	If the Batch Source has 'ID' selected for Sold To Customer, this column is required (subject to the line type criteria below) and the ORIG_SYSTEM_SOLD_CUSTOMER_REF must be null. Line Type: LINE, CHARGES, Header Level Freight Either supply a value for this column or ORIG_SYSTEM_SOLD_CUSTOMER_REF. Line Type: Tax, Line level freight Leave this Column Null.	Must exist in HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID
ORIG_SYSTEM_SOLD_ CUSTOMER_REF	Pass this column as an alternative to the ORIG_SYSTEM_SOLD_CUSTOMER_ID column. This column typically stores the unique ID value generated from your legacy application. See the 'Tip' section above for more on using reference fields with AutoInvoice.	If the Batch Source has 'VALUE' selected for Sold To Customer, this column is required (subject to the line type criteria below) and the ORIG_SYSTEM_SOLD_CUSTOMER_ID must be null. Line Type: LINE, CHARGES, Header Level Freight Either supply a value for this column or ORIG_SYSTEM_SOLD_CUSTOMER_ID Line Type: Tax, Line level freight Leave this Column Null.	Must exist in HZ_CUST_ACCOUNTS.ORIG_SYSTEM_REFERENCE

    c. Bill To

The Bill To records specify both the party being billed and the party site (address) to be used.  Optionally a Bill To contact can be provided as well.  The fields that are required are determined by the batch source setup (see partial screen shot below)

 
For more on Batch Source Setup and Troubleshooting, refer to:  Note 1068008.1 'Creating Transaction Sources Used For AutoInvoice' and  Note1088132.1 'Troubleshooting Transaction Batch Sources For AutoInvoice'.

AutoInvoice Column Name	Description	Optional or Required?	Validation
ORIG_SYSTEM_BILL_ CUSTOMER_ID	This column specifies the Customer that is being billed.  The ORIG_SYSTEM_BILL_ADDRESS_ID column will specify which site (address) is affiliated with the party entered in this column.	If the Batch Source has "ID" selected for Bill To Customer, this column is required (subject to the line type criteria below) and the ORIG_SYSTEM_BILL_CUSTOMER_REF must be null. Line Type:  LINE, CHARGES, Header Level Freight It is required to supply a value either for this column or ORIG_SYSTEM_BILL_CUSTOMER_REF Line Type: Tax, Line level freight Leave this Column Null	Value entered must exist in HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID For Applied Credit Memo's, the ID must match that of the transaction being credited or it must be that of another related customer.
ORIG_SYSTEM_BILL_ CUSTOMER_REF	Pass this column as an alternative to the ORIG_SYSTEM_BILL_CUSTOMER_ID column.  This column typically stores the unique ID value generated from your legacy application.  See the 'Tip" section above for more on using reference fields with AutoInvoice	If the Batch Source has "Value" selected for Bill To Customer, this column is required (subject to the line type criteria below) and the ORIG_SYSTEM_BILL_CUSTOMER_ID must be null. Line Type: LINE, CHARGES, Header Level Freight It is required to supply a value either for this column or ORIG_SYSTEM_BILL_CUSTOMER_ID Line Type: Tax, Line level freight Leave this Column Null	Value entered must exist in HZ_CUST_ACCOUNTS.ORIG_SYSTEM_REFERENCE for the Bill to customer For Applied Credit Memo's, the ID must match that of the transaction being credited or it must be that of another related customer.
ORIG_SYSTEM_BILL_ ADDRESS_ID	This column specifies the Customer Site (aka Address) associated with the Customer entered in either the ORIG_SYSTEM_BILL_CUSTOMER_ID or ORIG_SYSTEM_BILL_CUSTOMER_REF column.	If the Batch Source has "ID" selected for Bill To Address, this column is required (subject to the line type criteria below) and the ORIG_SYSTEM_BILL_ADDRESS_REF must be null. Line Type: LINE, CHARGES, Header Level Freight It is required to supply a value either for this column or ORIG_SYSTEM_BILL_ADDRESS_REF Line Type: Tax, Line level freight Leave this Column Null	The Address ID must be for the customer specified in the ORIG_SYSTEM_BILL_CUSTOMER_ID above and must exist in HZ_CUST_ACCT_SITE.CUSTOMER_SITE_ID Condition Used to Validate RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_BILL_ADDRESS_ID = HZ_CUST_ACCT_SITE.CUSTOMER_ACCT_SITE_ID and RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_BILL_CUS TOMER_ID = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID and HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = HZ_CUST_ACCT_SITE.CUST_ACCOUNT_ID and HZ_CUST_ACCT_SITE.CUSTOMER_SITE_ID = HZ_CUST_SITE_USES.CUST_ACCT_SITE_ID and HZ_CUST_SITE_USES.SITE_USE_CODE = 'BILL_TO'
ORIG_SYSTEM_BILL_ ADDRESS_REF	Pass this column as an alternative to the ORIG_SYSTEM_BILL_ADDRESS_ID column. This column typically stores the unique ID value generated from your legacy application. See the 'Tip" section above for more on using reference fields with AutoInvoice	If the Batch Source has "Value" selected for Bill To Address this column is required (subject to the line type criteria below) and the ORIG_SYSTEM_BILL_ADDRESS_ID must be null. Line Type: LINE, CHARGES, Header Level Freight It is required to supply a value either for this column or ORIG_SYSTEM_BILL_ADDRESS_ID Line Type: Tax, Line level freight Leave this Column Null	must exist in HZ_PARTY_SITES.ORIG_SYSTEM_REFERENCE for Bill to customer Condition Used to Validate RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_BILL_ADDRESS_REF = HZ_PARTY_SITES.ORIG_SYSTEM_REFERENCE and CUSTOMER_REF = HZ_CUST_ACCOUNTS.ORIG_SYSTEM_REFERENCE and HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = HZ_CUST_ACCT_SITE.CUST_ACCOUNT_ID and HZ_CUST_ACCT_SITE.CUSTOMER_SITE_ID = HZ_CUST_SITE_USES.CUST_ACCT_SITE_ID and HZ_CUST_SITE_USES.SITE_USE_CODE = 'BILL_TO'
ORIG_SYSTEM_BILL_ CONTACT_ID	This column specifies the Customer Contact associated with the Customer entered in either the ORIG_SYSTEM_BILL_CUSTOMER_ID or ORIG_SYSTEM_BILL_CUSTOMER_REF column.	Batch Source determines if this imported If the Batch Source (Transaction Source) has none then this field is not imported. If the batch source has "ID" checked then this column can optionally be populated subject to the line type criteria below. Line Type: LINE, CHARGES and for Header Level Freight. It is optional to supply a value either for this column or ORIG_SYSTEM_BILL_CONTACT_REF. Line Type: Tax, Line level freight Leave this Column Null	11i only - must exist in RA_CONTACTS.CONTACT_ID for Bill to customer Condition used to Validate (subject to batch source setup) RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_BILL_CUSTOMER_ID = HZ_CUST_ACCT_ROLES.CUST_ACCOUNT_ID and RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_BILL_CONTACT_ID = HZ_CUST_SITE_USES.CUSTOMER_SITE_ID R12: Validation is done as Follows (raavci,lpc) HZ_CUST_ACCOUNT_ROLES.ORIG_SYSTEM_REFERENCE = RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_BILL_CONTACT_ID HZ_CUST_ACCOUNT_ROLES.CUST_ACCOUNT_ID = RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_BILL_CUSTOMER_ID
ORIG_SYSTEM_BILL_ CONTACT_REF	Pass this column as an alternative to the ORIG_SYSTEM_BILL_CONTACT_ID column. This column typically stores the unique ID value generated from your legacy application. See the 'Tip" section above for more on using reference fields with AutoInvoice	Batch Source determines if this is imported If the Batch Source (Transaction Source) has none then this field is not imported. If the batch source has "VALUE" checked then this column can optionally be populated subject to the line type criteria below Line Type: LINE, CHARGES and for Header Level Freight.  It is optional to supply a value either for this column or ORIG_SYSTEM_BILL_CONTACT_ID Line Type: Tax, Line level freight Leave this Column Null	11i Only - must exist in RA_CONTACTS.CONTACT_REF for Bill to customer Condition used to validate (subject to batch source setup) R12: Validation is done as Follows (raavci,lpc) HZ_CUST_ACCOUNT_ROLES.ORIG_SYSTEM_REFERENCE = RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_BILL_CONTACT_REF HZ_CUST_ACCOUNT_ROLES.CUST_ACCOUNT_ID = RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_BILL_CUSTOMER_ID

Adapt the following SQL to find a valid ID's in the TCA tables for a specific customer. 

select
      c.cust_acct_site_id orig_system_bill_address_id,
      b.cust_account_id orig_system_bill_customer_id
from
      hz_parties a,
      hz_cust_accounts b,
      hz_cust_acct_sites_all c,
      hz_cust_site_uses_all d
where
      a.party_name = '&Customer_Name'  --Enter Your Customer Name Here
and a.party_id = b.party_id
and c.cust_account_id = b.cust_account_id
and c.cust_acct_site_id = d.cust_acct_site_id
and d.site_use_code = 'BILL_TO';

    d. Ship To

The Ship To records specify both the party that will receive the goods and the parties address (site).  Optionally a Ship To contact can be provided as well.  The fields that are used are controlled by the batch source setup

AutoInvoice Column Name	Description	Optional or Required?	Validation
ORIG_SYSTEM_SHIP_ CUSTOMER_ID	This column specifies the Customer that will receive the purchase.  The ORIG_SYSTEM_SHIP_ADDRESS_ID column will specify which site (address) is affiliated with the party on the invoice.	If the Batch Source has "ID" selected for Ship To Customer, this column is validated and the ORIG_SYSTEM_SHIP_CUSTOMER_REF must be null. If "None" is selected then both columns should be left as null. Line Type:  LINE, CHARGES, Header Level Freight The Ship Customer is Optional Line Type: Tax, Line level freight Leave this Column Null Credit Memo:   Leave Null	If a value is provided it must exist in HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID for the Ship to customer For Applied Credit Memo's, this column must be null. AutoInvoice uses the ship-to customer from the invoice being credited.
ORIG_SYSTEM_SHIP_ CUSTOMER_REF	Pass this column as an alternative to the ORIG_SYSTEM_SHIP_CUSTOMER_ID column.  This column typically stores the unique ID value generated from your legacy application.  See the 'Tip" section above for more on using reference fields with AutoInvoice	If the Batch Source has "Value" selected for Ship To Customer, this column is validated and the ORIG_SYSTEM_SHIP_CUSTOMER_ID must be null. If "None" is selected then both columns should be left as null. Line Type: LINE, CHARGES, Header Level Freight The Ship Customer is Optional Line Type: Tax, Line level freight Leave this Column Null Credit Memo: Leave Null	If a value is provided it must exist in HZ_CUST_ACCOUNTS.ORIG_SYSTEM_REFERENCE for the Ship to customer For Applied Credit Memo's, this column must be null.  AutoInvoice uses the ship-to customer from the invoice being credited.
ORIG_SYSTEM_SHIP_ ADDRESS_ID	This column specifies the Customer Site (aka Address) associated with the Customer entered in either the ORIG_SYSTEM_SHIP_CUSTOMER_ID or ORIG_SYSTEM_SHIP_CUSTOMER_REF column.	If the Batch Source has "ID" selected for Ship To Address, this column is required (subject to the line type criteria below) and the ORIG_SYSTEM_SHIP_ADDRESS_REF must be null. Line Type: LINE, CHARGES, Header Level Freight Supply a value for this column or ORIG_SYSTEM_SHIP_ADDRESS_REF as a required column if a ORIG_SYSTEM_SHIP_CUSTOMER was specified.  Leave null if none was specified. Line Type: Tax, Line level freight Leave this Column Null Credit Memo: Leave Null	The Address ID must exist and be for the customer specified in the ORIG_SYSTEM_SHIP_CUSTOMER_ID above. Must exist in HZ_CUST_ACCT_SITE.CUSTOMER_SITE_ID for the ship to customer. If no ORIG_SYSTEM_SHIP_CUSTOMER record was entered then this field must be null For Applied Credit Memo's, this column must be null. Condition Used to Validate RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_SHIP_ADDRESS_ID = HZ_CUST_ACCT_SITE.CUSTOMER_SITE_ID and RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_SHIP_CUS TOMER_ID = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID and HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = HZ_CUST_ACCT_SITE.CUST_ACCOUNT_ID and HZ_CUST_ACCT_SITE.CUSTOMER_SITE_ID = HZ_CUST_SITE_USES.CUST_ACCT_SITE_ID and HZ_CUST_SITE_USES.SITE_USE_CODE = 'SHIP_TO'
ORIG_SYSTEM_SHIP_ ADDRESS_REF	Pass this column as an alternative to the ORIG_SYSTEM_SHIP_ADDRESS_ID column. This column typically stores the unique ID value generated from your legacy application. See the 'Tip" section above for more on using reference fields with AutoInvoice	If the Batch Source has "Value" selected for ShipTo Address this column is required (subject to the line type criteria below) and the ORIG_SYSTEM_SHIP_ADDRESS_ID must be null. Line Type: LINE, CHARGES, Header Level Freight Supply a value for this column or ORIG_SYSTEM_SHIP_ADDRESS_ID as a required column if a ORIG_SYSTEM_SHIP_CUSTOMER was specified. Leave null if none was specified. Line Type: Tax, Line level freight Leave this Column Null Credit Memo: Leave Null	The Address must exist and be for the customer specified in the ORIG_SYSTEM_SHIP_CUSTOMER_REF above. Must exist in HZ_PARTY_SITES.ORIG_SYSTEM_REFERENCE for the ship to customer If no ORIG_SYSTEM_SHIP_CUSTOMER record was entered then this field must be null For Applied Credit Memo's, this column must be null. Condition Used to Validate RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_SHIP_ADDRESS_REF = HZ_PARTY_SITES.ORIG_SYSTEM_REFERENCE and CUSTOMER_REF = HZ_CUST_ACCOUNTS.ORIG_SYSTEM_REFERENCE and HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = HZ_CUST_ACCT_SITE.CUST_ACCOUNT_ID and HZ_CUST_ACCT_SITE.CUSTOMER_SITE_ID = HZ_CUST_SITE_USES.CUST_ACCT_SITE_ID and HZ_CUST_SITE_USES.SITE_USE_CODE = 'SHIP_TO'
ORIG_SYSTEM_SHIP_ CONTACT_ID	This column specifies the Customer Contact associated with the Customer entered in either the ORIG_SYSTEM_SHIP_CUSTOMER_ID or ORIG_SYSTEM_SHIP_CUSTOMER_REF column.	If the Batch Source (Transaction Source) has none then this field is not imported. If the batch source has "ID" checked then this column may optionally be populated subject to the line type criteria below. Line Type: LINE, CHARGES and for Header Level Freight Optionally set this field or ORIG_SYSTEM_SHIP_CONTACT_REF Line Type: Tax, Line level freight Leave this Column Null Credit Memo: Leave Null	Must exist in RA_CONTACTS.CONTACT_ID for the ship to customer For Applied Credit Memo's, this column must be null. Condition used to Validate (subject to batch source setup) RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_SHIP_CUSTOMER_ID = HZ_CUST_ACCT_ROLES.CUST_ACCOUNT_ID and RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_SHIP_CONTACT_ID = HZ_CUST_SITE_USES.CUSTOMER_SITE_ID
ORIG_SYSTEM_SHIP_ CONTACT_REF	Pass this column as an alternative to the ORIG_SYSTEM_SHIP_CONTACT_ID column. This column typically stores the unique ID value generated from your legacy application. See the 'Tip" section above for more on using reference fields with AutoInvoice	If the Batch Source (Transaction Source) has none then this field is not imported. If the batch source has "VALUE" checked then this column may optionally be populated subject to the line type criteria below Line Type: LINE, CHARGES and for Header Level Freight Optionally set this field or ORIG_SYSTEM_SHIP_CONTACT_REF Line Type: Tax, Line level freight Leave this Column Null Credit Memo: Leave Null	Must exist in RA_CONTACTS.CONTACT_REF for the ship to customer. Must be Null if ORIG_SYSTEM_SHIP_CUSTOMER is null For Applied Credit Memo's, this column must be null. Condition used to validate (subject to batch source setup) RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_SHIP_CUSTOMER_ID = HZ_CUST_ACCT_ROLES.CUST_ACCOUNT_ID and RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_SHIP_CONTACT_REF = RA_CONTACTS.ORIG_SYSTEM_REFERENCE

Adapt the following SQL to find a valid ID's in the TCA tables for a specific customer. 

select
      c.cust_acct_site_id orig_system_ship_address_id,
      b.cust_account_id orig_system_ship_customer_id
from
      hz_parties a,
      hz_cust_accounts b,
      hz_cust_acct_sites_all c,
      hz_cust_site_uses_all d
where
      a.party_name = '&Customer_Name'  --Enter Your Customer Name Here
and a.party_id = b.party_id
and c.cust_account_id = b.cust_account_id
and c.cust_acct_site_id = d.cust_acct_site_id
and d.site_use_code = 'SHIP_TO';

    e. Paying Customer

Paying Customer is derived (not passed as a parameter). 

AutoInvoice uses the customer bank account to determine whether the paying customer is the parent or the bill-to customer. If the paying customer is the bill-to customer, the paying site is the bill-to site. If the paying customer is the parent, the paying site is the primary bill-to site of the parent. Customer bank accounts are not used for manual receipt methods.

The logic used is as using the query below:
 

select cr.cust_account_id,
nvl(cr.customer_reciprocal_flag,'N')
from hz_cust_acct_relate cr
where cr.related_cust_account_id = :hbill_custid
and cr.status = 'A'
and cr.bill_to_flag = 'Y';

Paying Customer Site Use ID is derived using this query:
 

SELECT su.site_use_id
FROM hz_cust_site_uses su,
hz_cust_acct_sites acct_site
WHERE su.site_use_code = 'BILL_TO'
and su.cust_acct_site_id = acct_site.cust_acct_site_id
and acct_site.cust_account_id = p_customer_id
and su.primary_flag = 'Y'
ORDER BY su.status, su.site_use_id DESC;

See Section 3 below for problem and fix reported on Payment Customer in 11i Bills Receivable.

2. Troubleshooting Common Errors and Exceptions

    a. Validate Record(s) Using AutoInvoice Interface Data Diagnostic test

A fast an easy way to ensure that the customer attributes on the transactions loaded into the interface table are valid is to use the AutoInvoice Interface Data diagnostic test referenced in  Note 1079666.1.    If the customer attributes provided in the interface are valid then you should be able to look at the corresponding records in the HZ tables and see the related data presented in those tables.  This is an easy way to identify when values from the RA_INTERFACE_LINES_ALL tables are not valid and it can be adapted when investigating many of the fields being loaded.

Let's say for example that you want to validate the bill-to address that you are passing as being valid.  You have loaded the interface table and now can run thte AutoInvoice Interface Data test.  This will produce a report and the header will look something like this:

Note the Red circled values which map to the bill to address tables that correspond to the data in the interface.


Looking at the table RA_INTERFACE_LINES_ALL you can find the columns in the table related to the bill to address



If these values are valid then you will see the corresponding values in the underlying tables from the diagnostic:

HZ_LOCATIONS - Bill To


HZ_CUST_ACCOUNTS - Bill To


HZ_PARTY_STIES - Bill To


 

Note:  The example above shows how this works when the ID values are passed into the interface table.  Your batch source setup controls which values you are in fact passing and thus it is possible that this diagnostic will show records in the HZ tables for the value in the interface table but that AutoInvoice will still reject the transaction.  This would happen for example if your batch source setup specified "Value" and you passed ID's into the table.  For more on Batch Sources See Note 1088132.1 Troubleshooting Transaction Batch Sources For AutoInvoice

    b. Review AutoInvoice Execution Report

When AutoInvoice fails due to customer validation errors the exceptions are normally written to the AutoInvoice Execution report.  A typical presentation in the report will be as follows:

1) Invalid bill to customer reference (ORIG_SYSTEM_BILL_CUSTOMER_REF) ((Nil))
2) The Bill To address reference must exist in Oracle Receivables, and it must be assigned to the Bill To customer (ORIG_SYSTEM_BILL_ADDRESS_REF) ((Nil))

Each error message should state the column in RA_INTERFACE_LINES_ALL that failed validation. Refer to section 3 below for common errors and some exceptions to this rule.

    c. Review the Failed Transactions

There are two ways to review failed transactions:

        i. Interface Lines Form

Responsibility: Receivables Manager
Navigation: Control > AutoInvoice > Interface Lines

Run a query using either the INTERFACE_LINE_ID or the Batch Source name or just check the 'Errors Exist' checkbox to view lines with errors. Position the cursor on the record you want to fix, then click on the Errors button to see details of the errors.

This option is good if you have relatively few errors and you just want to correct those quickly. 

        ii. Interface Data Collection Scripts & Querying RA_CUSTOMER_TRX_LINES_ALL

When diagnosing more complex problems with addresses it is often helpful to gather data at the table level.  For this option we suggest that you refer to the 'Diagnostic Scripts and File Versions' section in  Note 1079666.1 and run the AutoInvoice Interface Data Collection Test.  This output will show you the data from the table and then use that to further diagnose the problem. 

After running this script (or querying directly against the table ra_customer_trx_lines_all) you should find the values entered in the columns from step 1b-1d. 

Use the Error message from the execution report to determine if you should look at the Sold To, Bill To, Ship To or all three Customer types.
 

In the example from step 2a this would point us to the bill to customer and bill to customer address.

    d. Review the Transaction Source to Check which Fields are Validated

Responsibility: Receivables Manager
Navigation: Setup > Transactions > Sources

Query the Transaction Source and review the Customer Information tab to see which values you should be passing.  Make sure the values you supplied match those with the radio buttons selected on this form.

In the example from step 2a (errors pasted below), the error message itself identified the column where the validation failed (ORIG_SYSTEM_BILL_CUSTOMER_REF). 
 

1) Invalid bill to customer reference (ORIG_SYSTEM_BILL_CUSTOMER_REF) ((Nil))

2) The Bill To address reference must exist in Oracle Receivables, and it must be
assigned to the Bill To customer (ORIG_SYSTEM_BILL_ADDRESS_REF) ((Nil))

In this case, if the AutoInvoice Interface Data Collection Test had shown that you passed the "ID", the fix could take one of two forms.  Either you could modify your transaction source to use ID and re-run AutoInvoice or you could modify the data being passed so that it populates the columns corresponding with your batch source setup ("Value").

    e. Make Certain the Values are Valid

Sections 1c-1d above show some sample SQL statements that you can adapt to find the proper values to use for a specific customer name. 
Make certain that the values in the ID or REF columns indicated by your batch source setup exist and are valid for the date used when running AutoInvoice. 
 

    f. AutoInvoice will Import Invoices for Inactive Customers

Please note:   ER 5611593 was logged to request that autoinvoice reject customers that are inactive.  As discussed in this bug, Autoinvoice does not validate whether a customer is inactive or not. The logic is that the customer has had goods shipped, and so should be invoiced whether or not the customer was subsequently made inactive.

 

    g. Review Common Exceptions and Errors in Section 3

Section 3 of this note provides a listing of common customer, address and contact errors and exceptions that are reported when running AutoInvoice.
 

3. Common Customer, Address and Contact Errors

Error String or Presentation	Most Common Cause(s)	Comprehensive List of Known Causes
Invalid bill to customer ID (ORIG_SYSTEM_BILL_CUSTOMER_ID)	Value passed as Null Value passed does not exist in HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID Transaction Source Setup Error (should have listed REF)	Note 1097862.1
Invalid bill to customer reference (ORIG_SYSTEM_BILL_CUSTOMER_REF)	Value passed as Null Value passed does not exist in HZ_CUST_ACCOUNTS.original_system_reference Transaction Source Setup Error (should have listed ID)	Note 1097846.1
The Bill To address ID must exist in Oracle Receivables, and it must be assigned to the Bill To customer ORIG_SYSTEM_BILL_ADDRESS_ID.	Value passed as Null Value passed does not exist in HZ_CUST_ACCT_SITE.CUSTOMER_SITE_ID Value passed is not properly defined as a Bill To Site (invalid value) Transaction Source Setup Error (should have listed REF)	Note 1097925.1
The Bill To address reference must exist in Oracle Receivables, and it must be assigned to the Bill To customer (ORIG_SYSTEM_BILL_ADDRESS_REF) Documentation Bug 8359807 Validation for this column is incorrect in the Oracle Receivables Reference Manual and should read:   Validation should read: RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_BILL_ADDRESS_REF = HZ_CUST_ACCT_SITES_ALL.ORIG_SYSTEM_REFERENCE RA_INTERFACE_LINES_ALL.ORIG_SYSTEM_BILL_CUSTOMER_REF = HZ_CUST_ACCOUNTS.ORIG_SYSTEM_REFERENCE and  HZ_CUST_ACCT_SITES.CUST_ACCT_SITE_ID = HZ_CUST_SITE_USES.CUST_ACCT_SITE_ID and HZ_CUST_SITE_USES.SITE_USE_CODE = 'BILL_TO'	Value passed as Null Value passed does not exist in HZ_CUST_ACCOUNTS.original_system_reference Value passed is not properly defined as a Bill To Site (invalid value) Transaction Source Setup Error (should have listed ID)	Note 365287.1
The bill to customer of your credit memo transaction must be the same or related to the one of the invoice it is crediting	RMA was created and customer or customer address was changed	Note 413607.1
Invalid ship to customer ID (ORIG_SYSTEM_SHIP_CUSTOMER_ID)	Value passed as Null Value passed does not exist in HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID Value passed is not properly defined as a Ship To site use (invalid value) Transaction Source Setup Error (should have listed REF)	Due to Bug 3424950 this error is not being displayed and instead the system is returning the error noted below for ORIG_SYSTEM_SHIP_ADDRESS_ID.  Steps to resolve have been included in that resolution article.
Invalid ship to customer reference (ORIG_SYSTEM_SHIP_CUSTOMER_REF)	Value passed as Null Value passed does not exist in HZ_CUST_ACCOUNTS.ORIG_SYSTEM_REFERENCE Value passed is not properly defined as a Ship To site use (invalid value) Transaction Source Setup Error (should have listed ID)	Note 219868.1
The supplied ship to address ID must exist in Oracle Receivables, and it must agree with the supplied ship to customer (ORIG_SYSTEM_SHIP_ADDRESS_ID) or The supplied ship to address ID must exist in Oracle Receivables, and it must agree with the supplied ID	Value passed as Null Value passed is not properly defined as a Ship To Site (invalid value) value passed does not exist in HZ_CUST_ACCT_SITE.CUSTOMER_SITE_ID Transaction Source Setup Error (should have listed REF)	Note 262735.1
The supplied ship to address reference must exist in Oracle Receivables, and it must agree with the supplied ship to customer  (ORIG_SYSTEM_SHIP_ADDRESS_REF)	Value passed as Null Value passed is not properly defined as a Ship To Site (invalid value) Value passed does not exist in HZ_CUST_ACCOUNTS.ORIG_SYSTEM_REFERENCE Transaction Source Setup Error (should have listed ID)	Note 219868.1
The Bill To contact ID must exist in Oracle Receivables,  and it must be assigned to the Bill To customer (ORIG_SYSTEM_BILL_CONTACT_ID)	Contact is not valid for the site specified value passed does not exist in RA_CONTACTS.CONTACT_ID Contact has been end dated or disabled	Note 747900.1
The supplied customer bank account(CUSTOMER_BANK_ACCOUNT_ID) must be both valid and active.	Bank account was created on or after the invoice date	Note 438237.1
Balance forward billing is enabled for this customer at the account level,  but is either disabled or not defined at site level or Balance forward billing is enabled for this customer but disabled at the account site level	Incorrect Setup	Step-by-Step instructions to fix are in Note 1053006.1
11.5 Only AutoInvoice does not default the paying customer information, if the payment method associated with the Bill to customer is of type 'BR', Bills Receivable.	Bug 3666519	Fixed files: raaihd.lpc to 115.10.1158.3 raavpm.lpc 115.7.1159.4 Recommended Patch: Patch 3666519 (This fix is included in 11i.AR.O / 11i.FP.G)
When customer names contain double quuotes ", AutoInvoice fails to import the names correctly.	Bug 12606882	Fixed files: AR.O: arstr.lc 115.0.15104.2 Recommended Patch: AR.O: Patch 12606882
Primary Bill To Site is always used by AutoInvoice even when you pass another Bill To site value in the interface tables	In Release 11i Bug 5441905 In Release 12.x Bug 8522446	Please review Note 382740.1 Customer Form, Business Purposes Issue: Form Mandates Primary Flag for Bill To / Ship To and You Cannot Un-check it

4. Still Have Questions?

To discuss this information further with Oracle experts and industry peers, we encourage you to review, join or start a discussion in the My Oracle Support Receivables Community.

To provide feedback on this note, click on the Rate this document link.

This article along with  Note 1075757.1 'Troubleshooting AutoInvoice for Oracle Receivables Release 11.5 Through 12' and the auxiliary notes referenced within attempt to guide you to resolution of most known issues with AutoInvoice Customers, Addresses and Contacts.

 If you find that you require assistance after completing the steps outlined in this note please provide the following information to your support representative to expedite resolution of your issue:

1) Can you create a manual invoice in AR with this customer Bill To, Ship To and Contacts?
2) Can you replicate this at will in AutoInvoice?
3) Do you have any research findings that would help us in understanding or troubleshooting this issue? If yes what?
4) Did this work before? If so what has changed?
5) If this is a problem with a specific invoice, provide the AutoInvoice debug log referenced in Note 1079666.1.
6) Provide the AutoInvoice Data Collection Test Diagnostic and Setup diagnostic applicable for your release.  Refer to Note 1079666.1 for the latest instructions.