Customer Interface

This chapter describes using Customer Interface to load customer account data into your database.

This chapter covers the following topics:

Customer Interface

Use Customer Interface to import current or historical customer information from other systems into your database. Once customer information is imported, you can use Customer Interface to import additional data for that customer (such as additional contacts or addresses) and to update existing information. You can also manually update and enter new information using the Customers set of pages.

Important: You can use Customer Interface to import both party and account information, including accounts that are associated with parties already loaded into your database. However, customer Interface runs independently and does not regard party level information already loaded into your database using Bulk Import. If you plan to use Customer Interface to import accounts that are associated with parties that have already been imported using Bulk Import, you must ensure that the source ID alone is unique across all source systems in the bulk import process. While Bulk Import requires source IDs to be unique only within an identified source system, customer interface does not recognize the source system and therefore requires that the source ID is unique across all sources systems. See:

The following diagram shows how customer information is imported into the customer tables.

Customer Interface Process

the picture is described in the document text

This illustration demonstrates that an import program is used to format and load data from a feeder system into the Customer Interface tables. The Customer Interface tables are:

You then run the Customer Interface program to validate your imported data and transfer the data to the Customer tables within your system.

Customer Interface Validation Rules

The Customer Interface program validates the data you load in the Customer Interface tables by ensuring that the columns in the interface tables reference the appropriate values and columns in the rest of your system. The interface supports the same data relationship for customer information as the Customers set of pages. See: Customers Overview.

Customer Interface will not create location combinations for foreign locations. The system considers a customer's address to be foreign if the country segment is not the same as the Default Country you defined in the System Options window. See: Defining Receivables System Options, Oracle Receivables Implementation Guide.

If you are trying to perform updates, Customer Interface ensures that the record to be updated already exists within your system. If the record does not exist in your system, or it only exists in a Customer Interface table in the Insert mode, the program displays an error.

Additionally, Customer Interface ensures that certain column values are consistent with each other. For example, if a profile class is not assigned to a customer, the interface program ensures that interest charge, collector, discount terms, and other profile class information is defined in the appropriate columns.

Customer Interface also ensures that records marked for insertion are unique.

Import Program

An import program is a custom program that you write which converts data from your feeder system into a standard data format that Customer Interface can read. The data can then be transferred into the Customer Interface tables. Once the import data is loaded into the interface tables, you can run Customer Interface to validate the data and convert it into customer information.

The type of feeder program you write depends on the environment from which you are importing data. For example, you can use SQL*Loader, SQL*Report, PL/SQL, or C to write an import program to import data from a non-Oracle system. You can also write a conversion program to import historical data from your original customer database. Regardless of the type of import program you write, the output should be in a standard data format that Customer Interface can use to import the information into the system.

Inserting and Updating Customer Information

When importing data into the interface tables, the column INSERT_UPDATE_FLAG indicates whether you are inserting new or updating existing information. This column is required in RA_CUSTOMERS_INTERFACE, RA_CONTACT_PHONES_ INTERFACE, and RA_CUSTOMER PROFILES_INTERFACE. Set this flag to I only if you are importing customer information into the interface tables and Customer tables for the first time (for example, when you initially import data from a legacy system). Set this flag to U if the customer already exists in the Customer tables and you want to update specific information.

When updating existing information, the data you import into the interface tables must contain information in each of the required columns, regardless of whether you want to update that information. For example, to modify a customer's name you must specify a value for the CUSTOMER_NAME column and all of the other required columns in RA_CUSTOMERS_INTERFACE, such as CUSTOMER_NUMBER, CUSTOMER_STATUS, and LAST_UPDATED_BY.

When you import a party, the HZ: Generate Party Number and HZ: Generate Party Site Number profile options determine whether you must import corresponding party numbers and party site numbers. If you set the HZ: Generate Party Number and HZ: Generate Party Site Number profile options to No, you must populate the PARTY_NUMBER and PARTY_SITE_NUMBER columns in the interface table. If you set these profile options to Yes, the system automatically generates party and party site numbers, and you cannot populate the interface table with these numbers.

If you do not populate the ORIG_SYSTEM_PARTY_REF column with an original system reference number for a party, the system assigns the imported customer reference number from the ORIG_SYSTEM_CUSTOMER _REF column to the party as well as to the customer account. During the import process, you must provide the existing party's original system reference number when you create a new customer account for an existing party.

If the INSERT_UPDATE_FLAG is not set correctly or a required column is missing a value, Customer Interface rejects the entire customer record, not just the attribute(s) you want to update.

Tip: Before you load data into the interface tables, create a copy of your import file. Then, if you want to update customer attributes later, you can set the insert update flag to U in your import file, modify only the values you want to update, and then reimport the data. This eliminates the need to recreate a new import file each time you want to update existing information.

Note: You cannot use Customer Interface to update information in the SITE_USE_ATTRIBUTE1-15 columns. These columns are used for descriptive flexfield information, and Customer Interface performs no validation on them.

Related Topics

Preparing for Import

Interface Data Required to Run Customer Interface

System Tables Updated by Customer Interface

A Sample Customer Import

Importing Customers Using Customer Interface

Customer Interface Transfer Report

Using the Customer Interface Program, Oracle Financials for the Americas User Guide

Preparing for Import

To ensure that Customer Interface runs smoothly, you need to prepare your system for any new data that you require Customer Interface to import. This data can include the following:

Related Topics

Interface Data Required to Run Customer Interface

System Tables Updated by Customer Interface

A Sample Customer Import

Overview of Setting Up, Oracle Receivables Implementation Guide

Interface Data Required to Run Customer Interface

This section lists the required columns for each Customer Interface table. For example, to enter a new contact for a previously entered customer, you must enter values for ORIG_SYSTEM_CUSTOMER_REF, ORIG_SYSTEM_CONTACT_REF, INSERT_UPDATE_FLAG and CONTACT_LAST_NAME.

You can use Customer Interface to import other pieces of information not listed in this section by populating additional "optional" columns. For example, you can optionally populate the LANGUAGE column for a customer site.

For a list of the validation for both required and optional columns and to see a list of optional columns, see: Customer Interface Table Descriptions and Validation, Oracle Trading Community Architecture Reference Guide.

RA_CUSTOMERS_INTERFACE

To import a customer, address, or business purpose, populate the following mandatory columns of RA_CUSTOMERS_INTERFACE:

If you are importing an address and a business purpose, you must also populate the following columns:

The system requires that you separate your city, state, and postal codes, whereas your current system may not. To save time, separate these components in your current system before importing customers.

You must enter values for the columns you reference in your Tax Location Flexfield if you are calculating sales tax and your Address Validation option is set to 'Error.' (You define this option in the System Options window, Tax tabbed region.)

RA_CUSTOMER_PROFILES_INTERFACE

A customer level profile must exist in RA_CUSTOMER_PROFILES_INTERFACE for new customers.

If you are entering a profile for a customer address, you must also enter a bill-to, dunning, or statements site in ORIG_SYSTEM_ADDRESS_REF.

If you populate the ORIG_SYSTEM_ADDRESS_REF column, the corresponding address, either a new one in the RA_CUSTOMERS_INTERFACE table or an existing address, must have an active bill-to, dunning, or statements business purpose. These business purposes can be either existing ones or new purposes that you are importing. If you do not populate the ORIG_SYSTEM_ADDRESS_REF column, those business purposes are created without any profile attached.

RA_CONTACT_PHONES_INTERFACE

To import telephone numbers for customers, addresses, and contacts, populate the following mandatory columns of RA_CONTACT_PHONES_INTERFACE:

If you are entering a telephone number for an address, you must also enter a value in ORIG_SYSTEM_ADDRESS_REF.

If you are entering a telephone number for a contact, you must also enter a value in ORIG_SYSTEM_CONTACT_REF and CONTACT_LAST_NAME.

RA_CUSTOMER_BANKS_INTERFACE

To import banks for customers and bill-to business purposes, populate the following mandatory columns of RA_BANKS_INTERFACE:

If you are entering a bank for a customer address, you must also enter a bill-to site in ORIG_SYSTEM_ADDRESS_REF.

RA_CUST_PAY_METHOD_INTERFACE

To import payment methods for customers and bill-to business purposes, populate the following mandatory columns of RA_CUST_PAY_METHOD_INTERFACE:

If you are entering a payment method for a customer address, you must also enter a bill-to site in ORIG_SYSTEM_ADDRESS_REF.

Related Topics

System Tables Updated by Customer Interface

A Sample Customer Import

Creating Unique Customer References

Importing Customers Using Customer Interface

System Tables Updated by Customer Interface

Customer Interface transfers customer data from the interface tables into the following tables:

The Customer Interface program will not allow updates to the following tables:

Note: The customer and address information descriptive flexfields are updated according to the data provided in the Interface table. If the data provided in the Interface table is Null, then data in the corresponding flexfield is cleared as well.

Caution: The Customer Interface Transfer Report will not display errors for records attempting to update these tables; the records will simply not be processed.

Related Topics

Interface Data Required to Run Customer Interface

Customer Interface Table Descriptions and Validation, Oracle Trading Community Architecture Reference Guide

A Sample Customer Import

The following diagram shows a customer with several addresses, customer profiles, contacts, telephone numbers, business purposes, bank accounts, and payment methods. Compare this diagram to the data examples that follow to see how you would prepare your interface to successfully import this information.

Sample Customer Import

the picture is described in the document text

This illustration shows the following information:

To import the customer illustrated in the previous diagram, your import program should load the Customer Interface tables as follows:

This table illustrates how your import program should load the RA_CUSTOMERS_INTERFACE table:

Customer Reference Name Address Reference Address Site Code
C1001 ACME A2001 200 Main BILL_TO
C1001 ACME A2001 200 Main SHIP_TO
C1001 ACME A2002 550 State SHIP_TO

Tip: You must separate your city, state, and postal codes, whereas the system you are importing from may not. To save time, separate these components in that system before importing customers.

This table illustrates how your import program should load the RA_CUSTOMER_PROFILES_INTERFACE table:

Customer Reference Address Reference Profile Name
C1001   Good
C1001 A2001 (This address reference refers to the bill-to site) Excellent

This table illustrates how your import program should load the RA_CONTACT_PHONES_INTERFACE table:

Customer Reference Address Reference Contact Reference Last Name Phone Reference Phone Number
C1001 A2001 S4001 KWAN S5001 555-1234
C1001 A2001 S4001 KWAN S5002 555-7000
C1001 A2001 S4002 SMITH    
C1001 A2001     S5003 474-8664
C1001 A2002 S4003 BASS    
C1001   S4004 RUDIN    
C1001       S5004 506-7000

This table illustrates how your import program should load the RA_CUSTOMER_BANKS_INTERFACE table:

Customer Reference Address Reference Bank Account
C1001   WF-0784
C1001 A2001 (This address reference refers to the bill-to site) BA-5431

This table illustrates how your import program should load the RA_CUST_PAY_METHOD_INTERFACE table:

Customer Reference Address Reference Bank Account
C1001   Automatic1
C1001 A2001 (This address reference refers to the bill-to site) Automatic2

Related Topics

Customer Interface

Creating Unique Customer References

Importing Customers Using Customer Interface

Creating Unique Customer References

Each attribute of a customer must be unique. For example, each contact for a customer or for a customer address must have a unique identifier. Following are some tips on how to create unique identifiers for imported customer information.

To select useful original system reference values:

Choose a value that you can easily derive from your original customer database. For example, if your original customer database has a 4 digit customer ID, a 6 digit address ID, a 5 digit contact ID, and a 7 digit telephone ID, you could create the following standard format for these values:

ORIG_SYSTEM_CUSTOMER_REF

CUST0001

ORIG_SYSTEM_ADDRESS_REF

CUST0001-ADDR000001

ORIG_SYSTEM_CONTACT_REF

CUST0001-ADDR000001-CONT00001

ORIG_SYSTEM_TELEPHONE_REF

CUST0001-ADDR000001-CONT00001-TELE0000001

Tip: If the value you enter is numeric, add an alpha character to the end to ensure that this number never conflicts with a system-generated ID number.

To import a customer with multiple addresses:

Enter multiple records into RA_CUSTOMERS_INTERFACE_ALL with identical ORIG_SYSTEM_CUSTOMER_REF values, but different ORIG_SYSTEM_ADDRESS_REF values.

To import a customer with multiple contacts:

Enter multiple records into RA_CONTACT_PHONES_INT_ALL with identical ORIG_SYSTEM_CUSTOMER_REF values, but different ORIG_SYSTEM_CONTACT_REF values.

To import a customer with multiple telephone numbers:

Enter multiple records into RA_CONTACT_PHONES_INT_ALL with identical ORIG_SYSTEM_CUSTOMER_REF values, but different ORIG_SYSTEM_TELEPHONE_REF values.

To import an address with multiple business purposes:

Enter multiple records into RA_CUSTOMERS_INTERFACE_ALL with identical ORIG_SYSTEM_CUSTOMER_REF and ORIG_SYSTEM_ADDRESS_REF values, but different SITE_USE_CODES values.

To import an address with multiple contacts:

Enter multiple records into RA_CONTACT_PHONES_INT_ALL with identical ORIG_SYSTEM_CUSTOMER_REF and ORIG_SYSTEM_ADDRESS_REF values, but different ORIG_SYSTEM_CONTACT_REF values.

To import an address with multiple telephone numbers:

Enter multiple records into RA_CONTACT_PHONES_INT_ALL with identical ORIG_SYSTEM_CUSTOMER_REF and ORIG_SYSTEM_ADDRESS_REF values, but different ORIG_SYSTEM_TELEPHONE_REF values.

To import a contact with multiple telephone numbers:

Enter multiple records into RA_CONTACT_PHONES_INT_ALL with identical ORIG_SYSTEM_CUSTOMER_REF and ORIG_SYSTEM_CONTACT_REF values, but different ORIG_SYSTEM_TELEPHONE_REF values.

Related Topics

Importing Customers

A Sample Customer Import

Importing Customers Using Customer Interface

Use the Customer Interface program to import and validate customer information from the interface tables into the Customer tables.

The interface tables receive data from an import program which converts data from your source system into a standard format that Customer Interface can read. For each record that passes validation, Customer Interface imports new or updates existing customer information in your system. See: Customer Interface.

You must write an import program that is compatible with the environment from which you want to import your data. For example, you can use SQL*Loader, SQL*Report, PL/SQL, or C to write an import program to import data from an external system. You can also write a conversion program to import historical data from your original customer database.

Important: Customer Interface does not import territory flexfield information.

Prerequisites

To import customers and customer related information using the Customer Interface program:

  1. Run your import program to load the Customer Interface tables.

    Caution: When loading the interface tables, you should remove all trailing spaces from the import data. Otherwise, if you attempt to load two records with the same customer name, but one of the records has trailing spaces, Customer Interface will treat each record as unique. If you are using SQL*loader to load the interface tables, you can easily remove all trailing spaces from the import data.

    For more information, refer to the documentation that corresponds to your version of Oracle Server.

  2. Use the Standard Request Submission windows or the Request Submission page to run the Customer Interface program. See: Running Reports and Programs, Oracle Applications User's Guide.

    Important: Enter Yes or No in the Create Reciprocal Customer Accounts parameter to indicate if you want to create reciprocal customers or not. The default is No.

    Tip: If you are importing a large number of customers, use the Customer Interface Master concurrent program to invoke parallel workers that process data at the same time. Before you run this program, define the number of workers to use in the HZ: Number of Workers Used by Customer Interface profile option.

    Tip: You can specify the operating unit for which you want the Customer Interface program to validate and import customer information.

    In case you do not select an operating unit, the Customer Interface program validates and imports customer information for all the operating units in your access list.

  3. To view the status of your request, navigate to the Find Requests window or View Requests page and click View Details.

  4. When the report phase is Complete, you can view the output by selecting View Output. See: Customer Interface Transfer Report.

Related Topics

Interface Data Required to Run Customer Interface

A Sample Customer Import

Customers Overview

Monitoring Requests, Oracle Applications User's Guide

Customer Interface Transfer Report

The application generates the Customer Interface Transfer report each time you run Customer Interface. This report lists the number of records imported into each table and records specific error messages describing any problems. If a record in the interface tables has several problems, the Customer Interface Transfer report displays multiple error messages to help you fix all of the problems in one step. You can then interface the records successfully the next time you run Customer Interface.

You may need to make changes in either your feeder program or your system to resolve the errors. For example, if you receive an error message explaining that the payment term that you specified for an address does not exist in your system, then you can either enter this term in the Payment Terms window, or modify your feeder program to import only existing payment terms.

The Customer Interface Transfer Report has 3 main sections:

The Header section displays the date and the time that Customer Interface began processing your transactions. The date is displayed in the format DD-MM-YY, while the time is displayed in the 24-hour format (e.g. 2:30 PM displays as 14:30). The header section also displays the concurrent request ID used by the Concurrent Manager and the user ID of the person who submitted the Concurrent Request.

The Summary of Transfer section displays a count of the customer information processed for each of the tables updated by the interface program. See: System Tables Updated by Customer Interface.

The Exception Records section displays detailed error messages about each record in the interface tables that was not successfully processed. This section also displays the original system reference columns from the interface tables to help you identify and correct exception records.

Related Topics

A Sample Customer Import

Importing Customers Using Customer Interface

Address Validation, Oracle Receivables Implementation Guide