Sql for dummies

Oracle’s SQL*LOADER and Oracle’s CUSTOMER INTERFACE for DUMMIES
© 2002 George R Lewycky keywords: oracle, financials, receivables, customer interface, sql*loader, sqlldr, customer data Email: grlewycky@yahoo.com www : http://georgenet.net/oracle

Introduction and Overview
This document will illustrate step by step how to use both the SQL*Loader and Interface data to load customer data forReceivables and other financial products which use Customers. With Oracle’s poor documentation and tech support and how they force you into using their consultants projects on a tight budget such a basic task can become an expensive time consuming task. I suffered for months trying to load my legacy customer data into Oracle Financials – AR and after all the research and suggestions I stuck withusing SQL*Loader to load the data into the Oracle tables. Only to learn later on that Oracle “requires” other fields, values, settings in order for the interface to port the data from the interface table into the real table(s) which are used by Oracle’s Accounts Receivable. Oracle’s Customer Interface imports and validates current and/or historical customer information from other systems intoReceivables. Once customer information is imported into the system, you can use Customer Interface menu option to import additional data for that customer, such as additional contacts or addresses, and to update existing information. You can also manually update and add new information using the Customer windows. The RA_CUSTOMERS_INTERFACE_ALL table is one of the Customer Interface tables that OracleReceivables uses to import customer, address, profiles, and site information. Receivables uses this customer information to create records in the RA_CUSTOMERS, RA_ADDRESSES_ALL, RA_CUSTOMER_RELATIONSHIPS_ALL, and RA_SITE_USES_ALL tables. The interface then deletes all records from the table(s) after all of your customer data is successfully imported.

1

Customer Interface also ensures thatrecords marked for insertions are unique and that the updates exist. The same applies to the updates which must pre-exist.

2

Table of Contents Introduction and Overview What is needed, Preliminary Set Up and Special SQL functions
Postal Code, Address Validation Automatic Customer and Site Numbering Default Profile Class & Profile Class Amounts How to acquire the valid values for Country CodesQuickcodes: Customer Category, Class, Type, Site Use Code Creating a duplicate TABLE of an existing Table Copying the data from one TABLE to another DELETE ALL ROWS (RECORDS) FROM THE TABLE

How to execute SQL*Loader Errors and tips
SQL*Loader-625: Multibyte character error in control file.

Truncating trailing spaces from the Name Validating record inserted using SQL*Plus Customer Interfacetables and their destination table(s)

Creating Data and Control files for SQL*LOADER Other Tips and Procedures Useful Excel macro to create custom delimiter Creating a copy of an existing table to test with Comma Delimited a SQL statement to extract data

Data tips Cleaning up your customer file (removing old, redundant data) Transform all data into uppercase Remove special characters such as“ and , else extracts, etc. Will become troublesome

3

Customer Interface What it does Limitations Reporting Error Handling Setup values

4

Creating a duplicate TABLE of an existing Table Copying the data from one TABLE to another DELETE ALL ROWS (RECORDS) FROM THE TABLE

SQL> select territory_code||','||nls_territory from fnd_territories 2 order by nls_territory;TERRITORY_CODE||','||NLS_TERRITOR --------------------------------DZ,ALGERIA US,AMERICA AU,AUSTRALIA AT,AUSTRIA BH,BAHRAIN BD,BANGLADESH BE,BELGIUM BR,BRAZIL BG,BULGARIA CA,CANADA CN,CHINA RU,CIS HR,CROATIA CY,CYPRUS CZ,CZECH REPUBLIC

5

WHAT IS NEEDED and SETTING UP

The following is needed on the PC you are working on: 1. 2. 3. 4. SQL*PLUS SQLLDR.EXE (DOS program on the Server machine)

.ctl

file...