Customer Conversions into 11i – The Interfacing Process and Common Pitfalls

Oracle Applications offers developers a relatively simple interface table structure to use when converting customer data from a legacy system into the 11i Receivables module. The Oracle Customer Interface process uses these tables as a staging area to select from and insert the legacy customer data into the complex HZ data table structure. Most of this complexity is shielded from the developer performing the conversion, however, a basic understanding of the interface process is required to debug and ultimately avoid some common pitfalls occurred when using the interface.

There are five main interface tables used in the Customer Interface, two of which will be discussed here. The RA_CUSTOMERS_INTERFACE table is where your customer and corresponding address data should be inserted. If more than one address exists for a given customer, there must be two entries placed into this table with the duplicate customer information. Most customers from your legacy system will have contact and phone records, which will be inserted into the RA_CONTACT_PHONES_INT_ALL table. Once the developer has successfully populated these tables with the source data from the legacy system (usually via a custom PL/SQL procedure which selects the legacy data from a custom staging table populated by SQL*Loader), the Oracle Customer Interface process can then be run to process this data and enter it into the HZ schema tables. If any records fail during the interface process, the corresponding error report will show these exceptions. The developer can then update these records manually and re-run the interface program to try and get the legacy data converted into Oracle. Common pitfalls have been identified which can help the developer determine how to fix these exception records, either by manually updating them in the interface tables or fixing the code used to populate the interface table:

Pitfall #1 – Multiple primary sites

The business rule in Oracle Applications is that a maximum of one primary site use can be defined for combination of customer and site use code. For example, a customer with two “Bill To” address sites can have the primary_site_use_flag set to ‘Y’ in the interface table for only one of the sites. If both have this flag set to ‘Y’, the interface will error out. When populating the interface table, your code should somehow decipher which site will be the primary one or set both to ‘N’. If, however, you only have a few customers with multiple sites and feel it is too time consuming to work the logic into the code, you can always let the multiple primary sites fail, update the corresponding records in the interface table, and run the interface again.

Pitfall #2 – Undefined lookups

Be careful when inserting data into columns that depend on lookup values. Examples of these are the country column in RA_CUSTOMERS_INTERFACE and the contact_title column in RA_CONTACT_PHONES_INT_ALL. The value supplied in the interface table must exactly match the lookup value, so a value of ‘MS’ would not work for contact title if the value in the AR_LOOKUPS table were ‘MS.’ (Note the period). The interface report will explicitly state these errors and what lookup table holds the values. A few exception records can be easily handled by updating the incorrect values in the interface tables. However, if there are a substantial amount of these invalid values the source data should be modified and reloaded.

Pitfall #3 – Already existing reference values

Another common error that might occur during the customer interface process is original system references that already exist in the HZ schema. For example, the orig_system_telephone_reference column of the RA_CONTACT_PHONES_INT_ALL interface table can be populated with a sequence number or a legacy reference id, but be careful that these references do not already exist in Oracle. The Oracle Applications table that holds telephone references is called HZ_CONTACT_POINTS, and if the reference number already exists in another row in this table, the interface will not process these records (Note that the interface error report will explicitly state which table to look at for the conflicting reference values). The developer then must determine how to make the system references unique, either by manually updating the exception records with a unique reference or by fixing the source data or code used to populate the interface tables.

Remember, conversions are usually a one-time occurrence, so fixing a few records manually in the interface tables might be a better alternative than figuring out the proper logic in your code when dealing with these and other pitfalls during the conversion process. Following this simple methodology should allow you to complete your conversions with minimal effort.