Customer Interface roadblock & questions
Date: 5 Jun 2002 13:14:37 -0700
Message-ID: <68aecc05.0206051214.1b01dc20_at_posting.google.com>
Has anyone had any success performing customer interface from a legacy to oracle financials on NT ???
Also has anyone been to do Customer Interface on a VISION db??
Below is what I encountered:
I used SQL*LOADER as shown below:
LOAD DATA
INFILE 'cust_spcl.txt'
INSERT INTO TABLE RA_CUSTOMERS_INTERFACE_ALL
FIELDS TERMINATED BY '","' TRAILING NULLCOLS
(
ORIG_SYSTEM_CUSTOMER_REF,CUSTOMER_NAME,ADDRESS1,
ADDRESS2,ADDRESS3,ADDRESS4,CITY,STATE,
POSTAL_CODE,COUNTRY,INSERT_UPDATE_FLAG,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,CREATION_DATE,CUSTOMER_CATEGORY_CODE,
CUSTOMER_STATUS,ORIG_SYSTEM_ADDRESS_REF,
PRIMARY_SITE_USE_FLAG,SITE_USE_CODE
)
and then run under INTERFACE - Customer
only to face useless messages
To Whom It May Concern:
Below is my history of customer interface and my SQL*LOADER run,
results, etc
I am encountering difficulty while running the CUSTOMER INTERFACE, the
third pass being the most recent.
Currently this is being done on the VISION database since our TEST machine isn’t fully ready yet.
I was instructed to find the values in the INTERFACE_STATUS field of the RA_CUSTOMERS_INTERFACE_ALL table and I found these values:
I’M BAFFLED WITH D0 THRU D7, E1 & E2 which is indicating I have two different addresses, cities, states, etc????? B0 pertaining to the SITE_USE_CODE not being defined in AR_LOOKUPS im baffled with also since I took a value directly from it.
I set autonumbering ON now so disregard O3
Attached is a zipped file of my code and the data file being loaded
Thanks in Advance
George R Lewycky
NY City Transit Authority
Brooklyn NYC
gelewyc_at_nyct.com
http://groups.yahoo.com/group/OracleSharedInfo/
INTERFACE_STATUS
B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1,
B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1,
B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1,
Customer Interface
Transfer Report
Current system time is
20-MAY-2002 12:40:19
Request Id =
803696
User Id =
1001634
- Summary of Transfer =======================================================
RA_CUSTOMERS_INTERFACE RA_CUSTOMERS RA_ADDRESSES RA_SITE_USES
--------------------------- --------------------------- --------------------------- --------------------------- Total Records = 221 Total Customers = 0 Total Addresses = 0 Total Site Uses = 0 Inserted Records = 0 Customers Inserted = 0 Addresses Inserted = 0 Site Uses Inserted = 0 Updated Records = 0 Customers Updated = 0 Addresses Updated = 0 Site Uses Updated = N/AException Records = 221
RA_CUSTOMER_RELATIONSHIPS
Total Relations = 0 Relations Inserted = 0
Relations Updated = N/A
RA_CUSTOMER_PROFILES_INTERFACE AR_CUSTOMER_PROFILES AR_CUSTOMER_PROFILE_AMOUNTS
------------------------------ --------------------------
--------------------------------
Total Records = 0 Total Profiles = 0 Total Profile Amounts = 0 Inserted Records = 0 Profiles Inserted = 0 Profile Amounts Inserted = 0 Updated Records = 0 Profiles Updated = 0 Profile Amounts Updated = 0 Exception Records = 0
RA_CONTACT_PHONES_INTERFACE RA_CONTACTS RA_PHONES
--------------------------- ---------------------------
---------------------------
Total Records = 0 Total Contacts = 0 Total Phones = 0
Inserted Records = 0 Contacts Inserted = 0 Phones
Inserted = 0
Updated Records = 0 Contacts Updated = 0 Phones Updated =
0
Exception Records = 0
RA_CUST_PAY_METHOD_INTERFACE AR_CUST_RECEIPT_METHODS
---------------------------- --------------------------------- Total Records = 0 Total Payment Methods = 0 Inserted Records = 0 Payment Methods Inserted = 0 Exception Records = 0
RA_CUSTOMER_BANKS_INTERFACE AP_BANK_BRANCHES AP_BANK_ACCOUNTS AP_BANK_ACCOUNT_USES
--------------------------- --------------------------- --------------------------- ------------------------------- Total Records = 0 Total Branches = 0 Total Accounts = 0 Total Customer Banks = 0 Inserted Records = 0 Branches Inserted = 0 Accounts Inserted = 0 Customer Banks Inserted = 0 Exception Records = 0 ============================================ Exception Records in RA_CUSTOMERS_INTERFACE ===========================================
Customer Reference Address Reference Site Use Error Message
--------------------------- --------------------------- -----------
----------------------------------------------------------------
0240 n/a ACCOUNTING SITE_USE_CODE is not defined in AR_LOOKUPS. LOCATION is mandatory when the system option Automatic Site Numb ering is set to No. Address reference has two different customers defined in RA_CUST OMERS_INTERFACE. Please update the ORIG_SYSTEM_CUSTOMER_REF fiel Address reference has two different ADDRESS1 values defined in R A_CUSTOMERS_INTERFACE. Please update the ADDRESS1 field of all r Address reference has two different ADDRESS2 values defined in 7 A_CUSTOMERS_INTERFACE. Please update the ADDRESS2 field of all r Address reference has two different ADDRESS3 values defined in R A_CUSTOMERS_INTERFACE. Please update the ADDRESS3 field of all r Address reference has two different ADDRESS4 values defined in R A_CUSTOMERS_INTERFACE. Please update the ADDRESS4 field of all r Address reference has two different cities defined in RA_CUSTOME RS_INTERFACE. Please update the ADDRESS4 field of all records re Address reference has two different postal codes defined in RA_C USTOMERS_INTERFACE. Please update the POSTAL_CODE field of all r Address reference has two different states defined in RA_CUSTOME RS_INTERFACE. Please update the State field of all records refer Address reference has two different countries defined in RA_CUST OMERS_INTERFACE. Please update the Country field of all records Address reference has two identical site use codes defined in RA _CUSTOMERS_INTERFACE. Please update the SITE_USE_CODE field of a 0245 n/a ACCOUNTING SITE_USE_CODE is not defined in AR_LOOKUPS. LOCATION is mandatory when the system option Automatic Site Numb ering is set to No. Address reference has two different customers defined in RA_CUST OMERS_INTERFACE. Please update the ORIG_SYSTEM_CUSTOMER_REF fiel Address reference has two different ADDRESS1 values defined in R A_CUSTOMERS_INTERFACE. Please update the ADDRESS1 field of all r Address reference has two different ADDRESS2 values defined in R A_CUSTOMERS_INTERFACE. Please update the ADDRESS2 field of all r Address reference has two different ADDRESS3 values defined in R A_CUSTOMERS_INTERFACE. Please update the ADDRESS3 field of all r Address reference has two different ADDRESS4 values defined in R A_CUSTOMERS_INTERFACE. Please update the ADDRESS4 field of all r Address reference has two different cities defined in RA_CUSTOME RS_INTERFACE. Please update the ADDRESS4 field of all records re Address reference has two different postal codes defined in RA_C USTOMERS_INTERFACE. Please update the POSTAL_CODE field of all r Address reference has two different states defined in RA_CUSTOME RS_INTERFACE. Please update the State field of all records refer Address reference has two different countries defined in RA_CUST OMERS_INTERFACE. Please update the Country field of all records Address reference has two identical site use codes defined in RA
_CUSTOMERS_INTERFACE. Please update the SITE_USE_CODE field of a
- Warning Records in RA_CUSTOMERS_INTERFACE ============================================
WHATS WRONG WITH MY ZIP CODES????????????????
How Does Oracle handle postal codes/zip codes?????? does it validate somewhere, add the zips as they are entered or assumes they are valid or ?????????????
Customer Reference Address Reference Error Message
------------------------------- -----------------------------Options
---------------------------------------
0240 n/a Warning: The Postal Code is not within the range defined in the System Options window. 0245 n/a Warning: The Postal Code is not within the range defined in the System Options window. 0246 n/a Warning: The Postal Code is not within the range defined in the System Options window. 0247 n/a Warning: The Postal Code is not within the range defined in the System Options window. 0253 n/a Warning: The Postal Code is not within the range defined in the System Options window. 0267 n/a Warning: The Postal Code is not within the range defined in the System
SOMEONE INSTRUCTED ME TO LOOK AT THIS FIELD ON THE
RA_CUSTOMER_INTERFACE_ALL
TABLE FOR DETAILED ERRORS
I’M BAFFLED WITH D0 THRU D7, E1 & E2 which is indicating I
have two different addresses, cities, states, etc?????
B0 pertaining to the SITE_USE_CODE not being defined in AR_LOOKUPS im
baffled with also since I took a value directly from it.
INTERFACE_STATUS
B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1, B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1, B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1, B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1, B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1, B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1, B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1, B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1, B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1, B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1, B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1,
INTERFACE_STATUS
B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1,
221 rows selected. Received on Wed Jun 05 2002 - 22:14:37 CEST