Oracle OM – Add Customer

Table of Contents
INTRODUCTION....................................................................................................1
SCOPE.......................................................................................................................1
FEATURES...............................................................................................................1
CREATE NEW CUSTOMER THROUGH ORDER IMPORT..............................................1
CREATE NEW ADDRESS THROUGH ORDER IMPORT................................................1
SUPPORT MULTIPLE ADDRESS FORMATS FOR NEW ADDRESSES.............................1
CREATE NEW CONTACTS THROUGH ORDER IMPORT..............................................2
CREATE NEW RELATIONSHIPS THROUGH ORDER IMPORT......................................2
CAPTURE EMAIL ADDRESSES AND PHONE NUMBERS THROUGH ORDER IMPORT....2
SETUP REQUIREMENTS.....................................................................................2
PROFILE OPTION.................................................................................................3
OM: E-MAIL REQUIRED ON NEW CUSTOMERS:.....................................................3
DATA FLOW DIAGRAM......................................................................................3
SIMPLE BUSINESS SCENARIOS........................................................................4
Flow #1: Creating New Customer & Party.....................................................5
Flow #2: Creating a New Customer and Associating with an Existing Party.6
Flow #3: Creating Address for an Existing Customer.....................................7
TROUBLESHOOTING...........................................................................................8
TECHNICAL CONSIDERATIONS......................................................................9
DATA PURGE ISSUES..............................................................................................9
PERFORMANCE ISSUES............................................................................................9
CONCLUSION.........................................................................................................9
DEFINITIONS.........................................................................................................9
APPENDIX.............................................................................................................10
Appendix 1: New Table Data Mapping for
OE_CUSTOMER_INFO_IFACE_ALL...........................................................10
Appendix 2: New Column Data Mapping of Header Table
OE_HEADERS_IFACE_ALL...........................................................................1
Appendix 3: New Column Data Mapping of Line Table
OE_LINES_IFACE_ALL..................................................................................2
Oracle OM– Add Customer

INTRODUCTION
‘Adding a Customer’ is a feature, which is an addition in OM Family Pack G. It provides one more way off adding
a customer to the database.
‘Add a Customer’ is a feature which can be used by any customer who imports orders from legacy systems or EDI
and who wants to add new customers, addresses or contacts during that import.
SCOPE
This document provides you details of the major features brought in, the setup required and the dataflow diagram.
This would be followed by troubleshooting steps.
FEATURES

Create New Customer through Order Import

It is possible to enter a new customer account with minimal data at the sold-to level on the order header via Order
Import. A new customer can also be entered at the ship-to, bill-to level or deliver-to at the order header level or
order line level.
The new customer gets assigned to the Default customer profile class, which specifies various financial and credit
checking information.
Create New Address through Order Import
A new ship-to, deliver-to or bill-to address for an existing customer or for a customer just created through Order
Import can be populated.
A new ship-to address gets created in Receivables automatically associated with the bill-to location that is on the
corresponding order header or line.
The address data is uploaded in the interface table indicating that a new customer account is to be created. When
this is done Order Import creates a new customer account address, if all required data is present and valid in the
interface tables.
Support Multiple Address Formats for new Addresses
Oracle Order Management – Add Customer Page 1
Flexible address styles are supported, as a result of which it is possible to enter the new customer address in the
country specific address styles defined.
Create New Contacts through Order Import
A new contact with minimal data at the account level can be defined via Order Import. The required data is loaded
in the interface table Order Import creates a new contact, if all required data is present and valid in the interface
tables.
Create New Relationships through Order Import
This feature also adds a new a customer relationship based on the OM System Parameter – Customer
Relationships parameter setting.
If the parameter is set to N, then the ship-to, deliver-to and bill-to addresses must belong to the sold-to customer
on the order. If an attempt is made to import an order with a bill-to, deliver-to or ship-to from a different
customer than the ship-to, Order Import should raise an error and not import the order.
If it is set to Y, and a new customer is added for a ship-to or bill-to or deliver-to, addresses and the relationship is
created.
If it is set to U, then a new customer can be added, but no relationship gets created.
Capture email Addresses and Phone Numbers through Order Import
An email address and a phone number can be captured when creating a new customer or contact.
The OM profile option OM: Email Required On New Customers is used to control whether an email address is
required for new customers and contacts. If the profile is set to Yes, then Order Import requires an email address
when it creates a new customer account or a contact.
SETUP REQUIREMENTS
• A new ‘add customer’ interface table (OE_CUSTOMER_INFO_IFACE_ALL ) is created to accommodate
this feature. This table needs to be populated with all the required fields for a new customer whenever a
new customer is to be added to the database thru order import. Order Import will then create a new
customer account and possibly a party, if all required data is present and valid in the interface tables.

It is possible to associate the new customer account with an existing party by providing the party
(organization or person) number in the interface tables. If that column is left null, then Order Import will
create the party as well as the customer account.
Oracle Order Management – Add Customer Page 2
• If OM – Customer Relationships parameter is set to Yes, , and a new customer is added for a ship-to or bill-
to or deliver-to, addresses and the relationship is created

PROFILE OPTION
OM: ADD CUSTOMER (ORDER IMPORT)
A new OM Profile Option “Add Customer (Order Import)” is introduced to control the addition of customer
through order import. This is a profile option and can be set at user level.
Navigation: System Admin > Profiles > User Profiles
It has 3 settings.
1. None: Meaning the functionality is not to be used.
2. Address and Contact Only: Meaning only new addresses or contacts can be added to an existing
customer. But no new customer an be added.
3. All: All rights are available. Full ability to add customer, addresses and contacts.

OM: E-Mail Required on New Customers:

This profile option determines whether the field E-mail address is required for any customer or customer contact
you define within Order
Yes: E-mail address is required, for both the customer and customer contact, when defining or updating a
customer or customer contact.
No: E-mail address is not required when defining or updating either a customer or customer contact.
The default for this profile option is Null. Can only be set at the site level under system administrator and is
optional. Not visible to any other level.
DATA FLOW DIAGRAM
Oracle Order Management – Add Customer Page 3
OE_HEADERS_IFACE_ALLOE_CUSTOMER_INFO_IFACE_ALLOE_LINES_IFACE_ALL
For Order Import the two tables are populated and they are
OE_HEADERS_IFACE_ALL – Header level details
OE_LINES_IFACE_ALL – Line Level details
Now for the new functionality of customer addition a new table is introduced
OE_CUSTOMER_INFO_IFACE_ALL
The relationship between the
OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL
and
OE_CUSTOMER_INFO_IFACE_ALL is of many is to many.
But for a given row in OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL, one of the representative
column should be populated in the OE_CUSTOMER_INFO_IFACE_ALL table.
Delete rule constraint is there on the table OE_CUSTOMER_INFO_IFACE_ALL meaning a record cannot be
deleted from that table if there is a referencing record existing in either OE_HEADERS_IFACE_ALL,
OE_LINES_IFACE_ALL tables.
For details on which fields need to be populated please refer to Appendix I.
SIMPLE BUSINESS SCENARIOS
Oracle Order Management – Add Customer Page 4
Flow #1: Creating New Customer & Party
1. Load a new order and customer through the Order Import open interface tables.
2. The order is for a customer and party that are new. Since the order is for a customer that does not exist,
new customer data needs to be loaded into interface tables and creates a new customer.
3. The data to be loaded in interface tables, for new customer addition

• OE_HEADERS_IFACE_ALL:

Not null value for

ORIG_SYS_CUSTOMER_REF - (to indicate new customer need to be added)
• OE_CUSTOMER_INFO_IFACE_ALL:

Not null value for

CUSTOMER_INFO_REF – Value same as ORIG_SYS_CUSTOMER_REF
CUSTOMER_TYPE – should be ‘ORGANIZATION’
CUSTOMER_INFO_TYPE_CODE – should be ‘ACCOUNT’
ORGANIZATION_NAME – name of the customer organization
4. Sample Script, which imports data and creates a new order and customer data with minimum of required
columns, with only order header level details

INSERT INTO OE_CUSTOMER_INFO_IFACE_ALL

(
CUSTOMER_INFO_REF,
CUSTOMER_TYPE,
CUSTOMER_INFO_TYPE_CODE,
ORGANIZATION_NAME
)
VALUES
(
'1234',
'ORGANIZATION',
'ACCOUNT',
'XYZA'
);
INSERT INTO OE_HEADERS_IFACE_ALL
(
ORDER_SOURCE,
order_source_id,
ORIG_SYS_DOCUMENT_REF,
ORDER_TYPE,
CREATED_BY,
CREATION_DATE,
Oracle Order Management – Add Customer Page 5
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
PRICE_LIST,
SOLD_FROM_ORG_ID,
ORIG_SYS_CUSTOMER_REF,
sold_to_org_id,
operation_code
)
VALUES
(
'Web',
9,
'ABC01',
'Mixed',
1318,
'24-May-02',
1318,
'24-May-02',
'Corporate',
207,
'1234',
1234,
'INSERT'
);
Before adding a new customer and system calls to HZ apis, there will be check perform on the customer interface
itself to make sure that there is not duplicate data, i.e., same kind of information for each column but only
reference information is different. In such case the processing for that batch referring to that record will not be
processed and error_flag will be set to ‘Y’.
Flow #2: Creating a New Customer and Associating with an Existing Party
1. Load a new order and a customer for existing party through the Order Import open interface tables.
2. The order is for a customer who is new but for an existing party details. For this information related to the
existing Party Number should also be loaded in, with new customer information
3. The data to be loaded in interface tables, for new customer addition

• OE_HEADERS_IFACE_ALL:

Not null value for

ORIG_SYS_CUSTOMER_REF - (to indicate new customer need to be added)
4. The only change here is one needs to add the existing party number in the
OE_CUSTOMER_INFO_IFACE_ALL interface table. Sample script with only order header level
details.

Oracle Order Management – Add Customer Page 6

INSERT INTO OE_CUSTOMER_INFO_IFACE_ALL
(
CUSTOMER_INFO_REF,
CUSTOMER_TYPE,
CUSTOMER_INFO_TYPE_CODE,
ORGANIZATION_NAME,
party_number
)
VALUES
INSERT INTO OE_HEADERS_IFACE_ALL
(
ORDER_SOURCE,
order_source_id,
ORIG_SYS_DOCUMENT_REF,
ORDER_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
PRICE_LIST,
SOLD_FROM_ORG_ID,
ORIG_SYS_CUSTOMER_REF,
sold_to_org_id,
operation_code
)
VALUES …..(put your values)
Flow #3: Creating Address for an Existing Customer
1. Load a new order and a customer and create a new address for existing customer through the Order Import
open interface tables.
2. For this customer/account information should already be there, Order import will use the information
provided for the existing customer to get the valid id, and use that Id to create the new address.
3. The important point to be noted are the following columns needs to be populated with

CUSTOMER_INFO_TYPE_CODE = ‘ADDRESS’ in table OE_CUSTOMER_INFO_IFACE_ALL

And the ORIG_SHIP_ADDRESS_REF in place of ORIG_SYS_CUSTOMER_REF
4. Sample Script with only order header level details.

INSERT INTO OE_CUSTOMER_INFO_IFACE_ALL

(
CUSTOMER_INFO_REF,
CUSTOMER_TYPE,
CUSTOMER_INFO_TYPE_CODE,
CURRENT_CUSTOMER_ID,
COUNTRY,
Oracle Order Management – Add Customer Page 7
ADDRESS1,
CITY,
POSTAL_CODE,
STATE,
COUNTY,
IS_SHIP_TO_ADDRESS,
LOCATION_NUMBER,
SITE_NUMBER
)
VALUES
INSERT INTO OE_HEADERS_IFACE_ALL
(
ORDER_SOURCE,
order_source_id,
ORIG_SYS_DOCUMENT_REF,
ORDER_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
PRICE_LIST,
SOLD_FROM_ORG_ID,
ORIG_SHIP_ADDRESS_REF,
sold_to_org_id,
operation_code
)
VALUES
TROUBLESHOOTING
The import transaction can fail due to various reasons. Certain points that need to be taken care off when
importing customer details are
• Value for column CUSTOMER_TYPE should be in Capital
• Value for column CUSTOMER_INFO_TYPE_CODE should be in capital
• ORIG_SYS_DOCUMENT_REF should be specified.
• If the order import failsset the debug level to 5 at user level. Rerun the transaction and check the
log file generated. It will give hints as to which area is failing.
• Order Import fails importing an order because the customer, address or contact is not found.
• The error can seen in the ‘Order Import Correction’ form by finding the record, and then displaying the
corresponding error messages.
• Research will make it clear whether a new customer or address or contact needs to be added. If so, it can
be done through ‘Add

Oracle Order Management – Add Customer Page 8

Customer’ window in Order Import, view or correct data there and save it. Otherwise, they can add the
customer or address or contact using the Customer Standard forms and not change the Order Import
tables. This would require re-keying data from the Order Import tables into the Customer forms,
however.

TECHNICAL CONSIDERATIONS

Data Purge Issues

The valid data is removed from the interface table once a new customer/address/contact is created in the system.
When the record errors out and the import does not happen an error flag is set to ‘Y’. Further to this there is an
option provided where user can set this error record as reject (sets the reject flag = ‘Y’). This rejected record then
can be deleted out of the interface table thru a concurrent program.
Performance Issues
As this functionality is going to allow addition of new records, which can be a new customer account, address or
contact. This additional processing will add time to the total processing time of order import, depending on
number of new customer account/address/contact are being added during import process. This additional time
will be directly proportional to the amount of new data being added in that run, for example if one call of Order
Import need to add account, its address and as well as its contact information that call will be slower than a Order
Import with no new addition
CONCLUSION
With the above specified functionality it is possible to import customer related information along with the order
import. But this will call for proper updating of the new table OE_CUSTOMER_INFO_IFACE_ALL, based on
what kind of data you are importing. And correct reference fields need to be populated in
OE_HEADERS_IFACE_ALL and

Oracle Order Management – Add Customer Page 9 Contact A representative who is responsible for
communication between you and a specific part of
your customer’s company
Customer Account Models a customer relationship between the
company deploying Oracle Applications and a
party
Customer Account Site A party site that is used within the context of a
customer account
Location A point in geographical space described by a street
address
Party An entity that can enter into a business
relationship. Can be an Organization, Person or
Group.
Party Site Links a party with a location, indicating that
party’s usage of the location.