Deep Drive : Customer Interface in AR

Hi Anand;

Thank you for all your articles, very helpful in understanding the process.
Qustion for you?
DO you or will you have some info on the AR changes in new release 12.

Thank you in advance

Hi Anand,

its very nice to see people helping others and your work is really commendable…bcause for the starters in AR like me the info given is immensely helpful..especially the error part…keep it up….

Thanks You.

your step should be:

1. try to understand what is the source of data.Understand the data and attribute first, then try to map with interface table as discussed in this post.

2.If source is in Oracle, use db link, else in case of other system, you need to write a loader program .

3. Write a pre-validation program which will validate the data from staging and insert into interface table.

4. run the customer import seeded program.If any expection , review log and re-import the step 4.

This is the standrad way to do conversion. If you are new to oracle apps adviced to use Customer import else API’s are are too good to use.

In case if you are looking any specfic information, let me know, will share.

hey Prasad,

The Customer import normally throws such kind of error when you are not passing country field.
Make sure the field in the country you are passing and must exist in the FND_TERRITORIES table

If Address Validation is set to ‘No validation’ and you pass a value that does not exist in AR_LOCATION_VALUES, Customer Interface inserts this value
into AR_LOCATION_VALUES.
Validation - The county must exist in FND_TERRITORIES.

for 1: do a quick cehck on
SELECT territory_code
FROM fnd_territories_vl
WHERE territory_code =

For 2:Check the value in AR System Options ??
Navigate to AR go to system options.

In case if you are still stuck, share your insert script, then will figure out where it goes wrong.

I’ll send you the code i am using.

Thanks again anand.

I would advice you to refer TRM and user guide, which is freely avaiable on internet from Oracle site, in case if you are still looking any specfic information or document just let me know.

Oracle University has different courses, they are also benifical. You can check to there site for details and fee.

Thanks Pauline ,
Yes, we can.

Donot get confused with these terms:
Let me recap these two words:

“Party” is an entity in the Trading Community Model that can enter into business relationships. A party is a real person, organization, branch,subsidiary, legal entity, holding company, etc. The attributes of a party are universal. In other words, they are independent of your selling (or ultimately buying) relationship with the party.

“Account” refers to the details of the deploying company’s selling relationship with a particular customer.

Account – the attributes of the selling relationship between the company deploying Oracle Applications and a party. Account attributes do not describe a
party; they only exist when a selling relationship is present between the deploying company and the party.

More over you should note that: An account is created for a party not for a party site. The selling relationship is not with a location but with the party that is using that location.

An Account cannot be created without a Party.

Account Site – a party site that is used within the context of an account; e.g., for billing or shipping purposes.

You need to have 2 tables:
1) HZ_CUST_ACCOUNTS
The HZ_CUST_ACCOUNTS table stores information about customer relationships established with a party. When a party becomes a customer, information about the customer account is stored in this table. Since a party can have multiple customer accounts, this table may contain several records for a single party. For example, an individual person may establish a personal account, a family account, and a professional account for a consulting practice. Note that the focus of this table is a business relationship and how transactions are conducted in the relationship. The primary key for this table is CUST_ACCOUNT_ID.

A single PARTY_ID is stored on this table to designate the customer account owner.

Each customer account may have only one owner (although other parties may be associated to the customer account via the HZ_CUST_ACCOUNT_ROLES table).

2)HZ_CUST_ACCT_RELATE_ALL
HZ_CUST_ACCT_RELATE_ALL stores information about relationships between customer accounts. A flag allows you to indicate whether a relationship is reciprocal. Note that columns also exist to reflect if the account relationship exist to enable shipping or billing to other accounts.

In real business world, these are approch normally we take:
1.Party Centric Approach : where each custmer is traeted as Party.
2.Site Centric Approach

Here are the screen snapshots how it looks:
1.Party Centric Approach

I have done setup for three account, account#1,#2,#3

2.Site Centric Approach

This can be best understood as customer Number 10009831-10009832-10009834

select party_id, party_number,party_name from hz_parties where party_name=’PAULINE’

Means we have 4 party created
Case 1: 3 party as 3 customer name with 3 different account
Case 2: 1 party as 1 Customer name with 3 site , each site pointing to one account.

Case 2, is more popular in industry. Typical Healthcare , uses case 2.

select * from HZ_CUST_ACCOUNTS where party_id in (select party_id from hz_parties where party_name=’PAULINE’)

I have already sent you a mail, in case if more information , let me know.
Hope this helps.
sanjit

Hi Anand,
The above article is really very useful. Similarly do you have the complete flow of the AR and AP till GL including table level updates and API’s used?
Regards,
Harsh

hi Anand,

Article is really good to understand the customer interface.
I have one quick question regarding errors…which table is used to store the interface errors

Thanks,
Darshini

During Customer conversion is there a way to populate payment terms for customer at the site level.In other words,is there any field in the interface tables to load the ‘Payment terms’ at the site level in addition to, the profile level?
Thanks inadvance

Darshini,

you need to populate ra_customer_profiles_int_all.standard_term_name.

Now , you need to populate these two columns for tag this payment term.

1) orig_system_address_ref column
This is one of mandatory column.

2)Orig_system_customer_ref

If you provide this value , of site then payment term is tag at site leval

Hope this helps

Hi Anand,

there is no way to load the payment terms using interface,its know by oracle and they have an enhance request created for this.

customer conversion and loading the customers as ‘Person’ is it possible to create the same party(person,sam peter) as the contact of himself(sam peter) .

I am able to load data according to above scenario using custom script and ‘Customer Interface’ but when we tried create the contact as same person using forms it errors and doesn’t let us save the data. Its strange some times it lets us create the contact even from forms too…its not stable?
Do you have any thoughts on this?

Main concern is can we create same person (’SAM PETER’) as the contact at the customer level (’SAM PETER’)..is this allowable in oracle?
Thanks!

Hi Anand,

Thanks for the good explanation.

We are looking for the payment term at business purpose for ‘Consolidated billing ‘ for which this field is required.

and regarding second.So we can create same person as the contact/relationship as ‘contact’ and has contact with himself..it is permissible?

Thanks!

1) Do we need to decide on a specific model before we convert? like you mentioned the site centric and party centric approach earlier.
In case we have to how is one different from the other and how would that change the conversion strategy?

>>>Yes, you have to decide based out of your billing revenue model, what is best way to manage your customer. If you are not using other CRM products, your focus should be only on Billing part and you have to look a particular customer same as party.

The model varies from industry to industry , like Telecom, healthcare, and banking. The more customer centric business like (Telecom, healtcare ) they prefer to go site /location centric. Once you decided which model should be , they it’s a simple matter to tag location and site with particular customer/party.Accounts will be drived by site or location leval. Technically, few columns like orig_system_ref_id,orig_system_%%

2) If we use the standard Customer interface and convert will that be in the TCA model? What is the difference between using the standard interface versus the TCA API’s.

>>> Customer Import and TCA API’s does not make any difference while converting customer from other system. The customer import is very easy to use , thus people tried to use. as API will have lot many capabilities, but its requires bit longer time for development efforts.

3)If we import using the Standard Interface can we convert later to the TCA model??

>>>> Donot confuse with TCA model, TCA is just a model, the way in which you can manage your customer. There are underline API’s , which iteslf a subset of underline API’s by customer Import. There is no difference at all.

Yes, TCA, will empower you to extend customer in some other area like CDH(data hub), D &B and other CRM & sales Model.

Donot get confused with these two things
let me recap
Customer Import - The way to create customer by oracle OIT based API’s
TCA API’S - The way to create custmor data by Oracle API’S without OIT(Oracle Open Interface Table)
TCA - The architure provided by Oracle to manage business entity like Customer, bank, Supplier, student.

My advice, if you are dealing with Technical things , come out with a genralize picture of a particular customer, then only you can 2 options to go either customer import or TCA API’s

If Functional , then try to see the customer from billing point and others aspect like statement of account, bill to, ship to, dunning kind of view

Still, have any query let me know, will try to help you out in understanding.

SB,

Technically its not so tough to understand.

can u understand comment #16, which clearly understood tow approach. Once its understood, i will provide provide input which additional column you should be more specfic.

These are the error message , sharing for the rest of people who seek some information.

These are the message codes and their meaning:

A1 –> The customer reference for update does not exist in RA_CUSTOMERS

A2 –> The address reference for update does not exist in RA_ADDRESSES

A3 –> Customer reference for insert is already defined in RA_CUSTOMERS

A4 –> Site use for this address reference already exists in the database

A5 –> Customer Number already assigned to a different customer

B1 –> ORIG_SYSTEM_ADDRESS_REF is mandatory when specifying an address

B2 –> ADDRESS1 is mandatory when specifying an address

B3 –> COUNTRY is mandatory when specifying an address

B4 –> SITE_USE_CODE is mandatory when inserting an address

B5 –> PRIMARY_SITE_USE_FLAG is mandatory when inserting an address

B6 –> CUSTOMER_CLASS_CODE is not defined in AR_LOOKUPS

B7 –> CUSTOMER_PROFILE_CLASS_NAME has an invalid value

B8 –> STATE is not defined in AR_LOCATION_VALUES

B9 –> COUNTRY is not defined in fnd_territories

B0 –> SITE_USE_CODE is not defined in AR_LOOKUPS

C1 –> This customer reference has two different customer names defined

C2 –> This customer reference has two different customer numbers defined

C3 –> This customer reference has two different parent customer references

C5 –> Customer reference has two different customer class codes defined

C6 –> This customer reference has two identical primary site uses defined

D1 –> Address reference has two different ADDRESS1 values

D2 –> Address reference has two different ADDRESS2 values

D3 –> Address reference has two different ADDRESS3 values

D4 –> Address reference has two different ADDRESS4 values

D5 –> Address reference has two different cities

D6 –> Address reference has two different postal codes

D7 –> Address reference has two different states

D8 –> Address reference has two different provinces

D9 –> Address reference has two different counties

D0 –> Address reference has two different countries

E1 –> Address reference has two identical site use codes

E2 –> Address reference has two different customers

F1 –> ORIG_SYSTEM_TELEPHONE_REF mandatory for telephone information

F2 –> TELEPHONE is mandatory when specifying telephone information

F3 –> TELEPHONE_TYPE is mandatory when specifying telephone information

F4 –> TELEPHONE_TYPE is not defined in AR_LOOKUPS

F5 –> Telephone reference for insert is already defined in RA_PHONES

F6 –> Telephone reference for update does not exist in RA_PHONES

G1 –> ORIG_SYSTEM_CONTACT_REF mandatory for contact information

G2 –> LAST_NAME is mandatory when specifying a contact

G3 –> CONTACT_TITLE is not defined in AR_LOOKUPS

G4 –> Contact reference for insert is already defined in RA_CONTACTS

G5 –> Contact reference for update is not defined in RA_CONTACTS

G6 –> The address reference specified is not defined for this customer

G7 –> CONTACT_JOB_TITLE must be defined in AR_LOOKUPS

H1 –> Contact reference has two different first names

H2 –> Contact reference has two different last names

H3 –> Contact reference has two different titles

H4 –> Contact reference has two different job titles

H5 –> Contact reference has two different customers

H6 –> Contact reference has two different addresses

I1 –> Telephone reference has two different phone numbers

I2 –> Telephone reference has multiple extensions

I3 –> Telephone reference has two different types

I4 –> Telephone reference has two different area codes

I6 –> Telephone reference has two different customers

I7 –> Telephone reference has two different addresses

J1 –> SITE_USE_CODE is not updateable

J2 –> PRIMARY_SITE_USE_FLAG is not updateable

J3 –> LOCATION is not updateable

J4 –> CUSTOMER_TYPE is not defined in AR_LOOKUPS

J5 –> PRIMARY_SITE_USE_FLAG has an invalid value

J6 –> CUSTOMER_NUMBER must be null when auto-numbering is set to “Yes”

J7 –> CUSTOMER_NUMBER is mandatory when auto-numbering is set to “No”

J8 –> INSERT_UPDATE_FLAG has an invalid value

J9 –> CUSTOMER_STATUS must have a value of ‘A’ or ‘I’

K1 –> Concurrent request failed

K3 –> This customer reference has two different customer types defined

L1 –> COLLECTOR_NAME is mandatory when no profile class specified

L2 –> TOLERANCE is mandatory when no profile class specified

L3 –> DISCOUNT_TERMS is mandatory when no profile class specified

L4 –> DUNNING_LETTERS is mandatory when no profile class specified

L5 –> INTEREST_CHARGES is mandatory when no profile class specified

L6 –> STATEMENTS is mandatory when no profile class specified

L7 –> CREDIT_BALANCE_STATEMENTS mandatory when no profile class specified

L9 –> DUNNING_LETTER_SET_NAME is mandatory when DUNNING_LETTERS is “Yes”

L0 –> CHARGE_ON_FINANCE_CHARGE_FLAG mandatory when INTEREST_CHARGES is “Yes”

M1 –> INTEREST_PERIOD_DAYS is mandatory when INTEREST_CHARGES is “Yes”

M3 –> COLLECTOR_NAME has an invalid value

M4 –> CREDIT_CHECKING has an invalid value

M5 –> TOLERANCE has an invalid value

M6 –> DISCOUNT_TERMS has an invalid value

M7 –> DUNNING_LETTERS has an invalid value

M8 –> INTEREST_CHARGES has an invalid value

M9 –> STATEMENTS has an invalid value

M0 –> CREDIT_BALANCE_STATEMENTS has an invalid value

N1 –> CREDIT_HOLD has an invalid value

N2 –> CREDIT_RATING has an invalid value

N3 –> RISK_CODE has an invalid value

N4 –> STANDARD_TERM_NAME which contains the payment terms has an invalid value

N5 –> OVERRIDE_TERMS has an invalid value

N6 –> DUNNING_LETTER_SET_NAME has an invalid value

N7 –> STATEMENT_CYCLE_NAME has an invalid value

N8 –> ACCOUNT_STATUS has an invalid value

N9 –> PERCENT_COLLECTABLE has an invalid value

N0 –> AUTOCASH_HIERARCHY_NAME which contains the AutoCash rule has
an invalid value

O1 –> STATEMENT_CYCLE_NAME is mandatory when STATEMENTS is “Yes”

O2 –> LOCATION must be null when auto-numbering is set to “Yes”

O3 –> LOCATION is mandatory when auto-numbering is set to “No”

O4 –> CREDIT_CHECKING is mandatory when profile class is null

O5 –> CHARGE_ON_FINANCE_CHARGE_FLAG must be null if INTEREST_CHARGES is No

O6 –> INTEREST_PERIOD_DAYS must be null if INTEREST_CHARGES is “No”

O7 –> INTEREST_PERIOD_DAYS must be greater than zero

P1 –> Postal Code is not in the defined range of system options

Q1 –> A new location was created for a value in an address segment field

Q2 –> Validation failed for the key location flexfield structure

R1 –> CUST_SHIP_VIA_CODE is not defined in ORG_FREIGHT

R2 –> CUSTOMER_CATEGORY_CODE is not defined in AR_LOOKUPS

R3 –> CUSTOMER_CATEGORY_CODE is not enabled in AR_LOOKUPS

R4 –> CUST_TAX_CODE is not defined in AR_VAT_TAX

R5 –> CUST_TAX_REFERENCE cannot be null when CUST_TAX_CODE is ‘EXEMPT’

R6 –> SITE_USE_TAX_CODE is not defined in AR_VAT_TAX

R7 –> SITE_USE_TAX_REFERENCE is required when SITE_USE_TAX_CODE is ‘EXEMPT’

R8 –> Invalid demand class code.

R9 –> SITE_SHIP_VIA_CODE not defined in ORG_FREIGHT

S1 –> The customer reference specified is invalid

S2 –> The address reference specified is invalid

S3 –> The address reference specified is not valid for this customer

S4 –> Payment Method is not defined in AR_RECEIPT_METHODS

S5 –> A bank account does not exist for the specified customer

S6 –> The end date specified cannot be before the start date

S7 –> The address specified must have an active BILL_TO site defined

T1 –> Customer payment method already active between the dates specified

T2 –> Customer site payment method already active between the dates specified

T3 –> Customer already has a primary payment method for specified dates

T4 –> Customer site has a primary payment method on the dates specified

T5 –> This customer payment method is already active in this date range

T6 –> Multiple primary payment methods defined

V2 –> The bank account specified must be of type ‘EXTERNAL’

V3 –> Customer bank account is already active between the dates specified

V4 –> Customer site bank account already active between these dates

V5 –> This customer already has primary bank account for specified dates

V6 –> Customer site can have only 1 primary bank account for the dates
specified

V7 –> Duplicate rows exist in Interface table for this Customer Bank and
date run

V8 –> Duplicate primary customer banks defined within the interface table

W1 –> BANK_NAME is mandatory when creating a new bank account

W2 –> BANK_BRANCH_NAME is mandatory when creating a new bank account

W3 –> BANK_ACCOUNT_CURRENCY_CODE is mandatory creating a new bank account

W4 –> BANK_ACCOUNT_CURRENCY_CODE is not defined in FND_CURRENCIES

W5 –> Bank number already exists.

W6 –> Duplicate bank number in interface table.

W7 –> Primary flag should be ‘Y’ or ‘N’.

W8 –> Duplicate bank and branch name in interface table.

W9 –> Duplicate Location

W0 –> Bank and branch name already exists.

X1 –> AUTO_REC_INCL_DISPUTED_FLAG mandatory when profile class is null

X2 –> TAX_PRINTING_OPTION is mandatory when no profile class specified

X3 –> GROUPING_RULE_NAME is mandatory when no profile class is specified

X4 –> CHARGE_ON_FINANCE_CHARGES_FLAG has an invalid value

X5 –> GROUPING_RULE_NAME has an invalid value

X6 –> CURRENCY_CODE has an invalid value

X7 –> CREDIT_BALANCE_STATEMENTS is mandatory when STATEMENTS is “Yes”

X8 –> CREDIT_BALANCE_STATEMENTS must be “No” when STATEMENTS is “No”

X9 –> STATEMENT_CYCLE_NAME must be null when STATEMENTS is “No”

X0 –> OVERRIDE_TERMS is mandatory when no profile class is specified

Y1 –> PARTY_NUMBER must be null when auto-numbering is set

Y2 –> PARTY_NUMBER is mandatory when auto-numbering is set to “No”

Y3 –> Party Number already assigned to a different party.

Y4 –> This party reference has two different party numbers defined in
RA_CUSTOMERS_INTERFACE.

Y5 –> PERSON_FLAG has an invalid value

Y6 –> Party Site Number already assigned to a different address

Y7 –> Address reference has two different party site numbers defined in
RA_CUSTOMERS_INTERFACE.

Y8 –> PARTY_SITE_NUMBER must be null when auto-numbering is set

Y9 –> PARTY_SITE_NUMBER is mandatory when auto-numbering is set to “No”

Z1 –> CREDIT_BALANCE_STATEMENTS must be null when STATEMENTS is null

Z2 –> STATEMENT_CYCLE_NAME must be null when STATEMENTS is null

Z3 –> CHARGE_ON_FINANCE_CHARGE_FLAG must be null when INTEREST_CHARGES is null

Z4 –> INTEREST_PERIOD_DAYS must be null when INTEREST_CHARGES is null

Z5 –> DISCOUNT_GRACE_DAYS must be null when DISCOUNT_TERMS is null

Z6 –> DISCOUNT_GRACE_DAYS must positive

Z7 –> DISCOUNT_GRACE_DAYS must be null when DISCOUNT_TERMS is “No”

Z8 –> DUNNING_LETTER_SET_NAME must be null when DUNNING_LETTERS is “No”

Z9 –> DUNNING_LETTER_SET_NAME must be null when DUNNING_LETTERS is null

Z0 –> CURRENCY_CODE is mandatory when a profile amount value is populated

a1 –> Customer record for insert must have validated profile record defined

a2 –> TAX_PRINTING_OPTION has an invalid value

a3 –> The customer profile for this customer reference already exists

a4 –> The customer profile class for update does not exist

a7 –> Duplicate record within the interface table

a8 –> Conflicting profile classes specified for this customer/site

b1 –> Both TRX_CREDIT_LIMIT and OVERALL_CREDIT_LIMIT must be populated

b2 –> TRX_CREDIT_LIMIT may not be greater than the OVERALL_CREDIT_LIMIT

b3 –> DUNNING_LETTER_SET_NAME must have a unique value

b4 –> COLLECTOR_NAME must have a unique value

b5 –> STANDARD_TERM_NAME must have a unique value

b6 –> STATEMENT_CYCLE_NAME must have a unique value

b7 –> BANK_ACCOUNT_NUM is mandatory when creating a new bank account

b8 –> AUTO_REC_INCL_DISPUTE_FLAG has an invalid value

b9 –> PAYMENT_GRACE_DAYS must be a positive value

e2 –> Bill_to_orig_address_ref should only be defined for Ship-to Addresses

e3 –> Bill_to_orig_address_ref is not a valid bill-to address

f1 –> You may have only one active Dunning site use for each customer

f2 –> For each customer, you may only have one active “Statements” type

f3 –> For each customer, you may only have one active Legal site

f4 –> Clearing Days must be greater than or equal to zero

f5 –> Address language is not installed

f6 –> Address reference has different languages

f7 –> Duplicate telephone reference in table RA_CONTACT_PHONES_INTERFACE

f8 –> A bank and branch with this bank number and branch number already exists

f9 –> Customer Prospect Code must be either CUSTOMER or PROSPECT

g1 –> This customer reference has two different customer prospect codes

u5 –> Contact reference has two different e-mail addresses

w2 –> CREDIT_CLASSIFICATION must have a valid value

w3 –> You cannot update the PARTY_TYPE using Customer Interface.
Please do not specify a value for PARTY_TYPE when the
INSERT_UPDATE_FLAG is set to U.

w4 –> When you create a PERSON party_type, you must provide
PERSON_FIRST_NAME or PERSON_LAST_NAME.

y0 –> CONTACT_JOB_TITLE is not defined

y1 –> PHONE_COUNTRY_CODE is not defined in HZ_PHONE_COUNTRY_CODES

y2 –> This customer is already assigned to a different party

y3 –> This customer is already assigned to a different party

y4 –> LOCKBOX_MATCHING_OPTION must have a valid value

y6 –> TELEPHONE_TYPE cannot be updated from telex to any other type or any
other type to telex.

y7 –> You cannot update this address. A printed, posted, or applied
transaction with an associated tax line exists for this address

y8 –> ADDRESS_CATEGORY_CODE does not exist. Please enter a valid adress
category code or define a new one using the Receivables Lookups
window.

y9 –> ADDRESS_CATEGORY_CODE is not enabled. Please enable this address
category by updating the Enabled flag in the Receivables Lookups window.

Hi Anand,

Thank u very much this documnet really helped me a lot….

Hello Sanjit, Great article - nicely written, and informative. Thank you.

Question - For Customer conversion (R12) how does one decide between using the Open Interface Tables or the HZ APIs? Is it a matter of data fields supported by the two APIs (Open Interface has fewer that the HZ APIs), or some other consideration(s)?

[...] Deep Drive : Customer Interface in AR [...]

[...] Customer Interface aka RACUST is a concurrent program that is responsible for the import and update of AR customer information from open interface tables to AR Customer tables. Check out the details here. [...]