CUSTOMER INTERFACE USING SQL LOADER

CUSTOMER INTERFACE USING SQL LOADER

Save this file .csv(comma separated value)
Create the following Staging Table in apps schema
Name Null? Type
------------------------------- -------- --------------------------
ORIG_SYSTEM_CUSTOMER_REF VARCHAR2(240)
SITE_USE_CODE VARCHAR2(30)
ORIG_SYSTEM_ADDRESS_REF_BILL VARCHAR2(240)
ORIG_SYSTEM_ADDRESS_REF_SHIP VARCHAR2(240)
CUSTOMER_NAME VARCHAR2(360)
CUSTOMER_TYPE VARCHAR2(25)
CUSTOMER_CLASS_CODE VARCHAR2(30)
CUSTOMER_CATEGORY_CODE VARCHAR2(30)
ADDRESS1 VARCHAR2(240)
ADDRESS2 VARCHAR2(240)
ADDRESS3 VARCHAR2(240)
ADDRESS4 VARCHAR2(240)
CITY VARCHAR2(60)
COUNTY VARCHAR2(60)
STATE VARCHAR2(60)
POSTAL_CODE VARCHAR2(60)
COUNTRY VARCHAR2(60)
SITE_USE_TAX_CODE VARCHAR2(50)
SITE_SHIP_VIA_CODE VARCHAR2(25)
BILL_TO_ORIG_ADDRESS_REF VARCHAR2(240)
CUST_TAX_EXEMPT_NUM VARCHAR2(30)
CUSTOMER_PROFILE_CLASS_NAME VARCHAR2(30)
OVERALL_CREDIT_LIMIT NUMBER
COLLECTOR_NAME VARCHAR2(30)
PAYMENT_METHOD_NAME VARCHAR2(30)
CONTACT_FIRST_NAME VARCHAR2(40)
CONTACT_LAST_NAME VARCHAR2(50)
CONTACT_TITLE VARCHAR2(30)
TELEPHONE_AREA_CODE VARCHAR2(10)
TELEPHONE VARCHAR2(25)
TELEPHONE_TYPE VARCHAR2(30)
EMAIL_ADDRESS VARCHAR2(240)
Query and find the following interface tables
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
And insert the data in these interface table in following manner
Now create the control file using TOAD sql loader wizard it is described in SQLLOADER.DOC FILE
After then create sql stored procedure for inserting data in interface table from staging table
Staging table is CUSTOMER_INT
Interface tables are
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
Create the following PL/SQL procedure
declare
cursor cust_cur is select * from customer_int;
l_address_ref varchar2(100);
l_primary_site_use_flag varchar2(1) := 'Y';
l_flag varchar2(1) := 'Y';
begin
delete from ra_customers_interface_all;
delete from ra_customer_profiles_int_all;
commit;

for c in cust_cur loop
if c.site_use_code = 'Bill-To' then
l_address_ref := c.orig_system_address_ref_bill;
else
l_address_ref := c.orig_system_address_ref_ship;
end if;
if l_flag = 'Y' then
insert into ra_customers_interface_all
(orig_system_customer_ref
,site_use_code
,orig_system_address_ref
,insert_update_flag
,customer_name
,customer_type
,customer_class_code
,customer_category_code
,address1
,address2
,address3
,address4
,city
,county
,state
,postal_code
,country
,site_use_tax_code
,site_ship_via_code
,bill_to_orig_address_ref
,cust_tax_exempt_num
,last_updated_by
,last_update_date
,creation_date
,created_by
,org_id
,primary_site_use_flag
,customer_status
)
values (c.orig_system_customer_ref
,decode(c.site_use_code,'Bill-To','BILL_TO','Ship-To','SHIP_TO')
,l_address_ref
,'I'
,c.customer_name
,decode(c.customer_type,'External','R','Internal','I')
,c.customer_class_code
,c.customer_category_code
,c.address1
,c.address2
,c.address3
,c.address4
,c.city
,c.county
,c.state
,c.postal_code
,c.country
,c.site_use_tax_code
,c.site_ship_via_code
,c.bill_to_orig_address_ref
,c.cust_tax_exempt_num
,-1
,sysdate
,sysdate
,-1
,204
,l_primary_site_use_flag
,'A'
);
insert into ra_customer_profiles_int_all
(orig_system_customer_ref
,insert_update_flag
,customer_profile_class_name
,credit_hold
,overall_credit_limit
,credit_checking
,collector_name
,last_updated_by
,last_update_date
,creation_date
,created_by
,org_id
,currency_code
,trx_credit_limit
,validated_flag
)
values (c.orig_system_customer_ref
,'I'
,c.customer_profile_class_name
,'N'
,c.overall_credit_limit
,'Y'
,c.collector_name
,-1
,sysdate
,sysdate
,-1
,204
,'USD'
,c.overall_credit_limit
,'Y'
);
end if;

if l_flag = 'N' then
insert into ra_customers_interface_all
(orig_system_customer_ref
,site_use_code
,orig_system_address_ref
,insert_update_flag
,customer_name
,customer_type
,customer_class_code
,customer_category_code
,address1
,address2
,address3
,address4
,city
,county
,state
,postal_code
,country
,site_use_tax_code
,site_ship_via_code
,bill_to_orig_address_ref
,cust_tax_exempt_num
,last_updated_by
,last_update_date
,creation_date
,created_by
,org_id
,primary_site_use_flag
,customer_status
)
values (c.orig_system_customer_ref
,decode(c.site_use_code,'Bill-To','BILL_TO','Ship-To','SHIP_TO')
,l_address_ref
,'I'
,c.customer_name
,decode(c.customer_type,'External','R','Internal','I')
,c.customer_class_code
,c.customer_category_code
,c.address1
,c.address2
,c.address3
,c.address4
,c.city
,c.county
,c.state
,c.postal_code
,c.country
,c.site_use_tax_code
,c.site_ship_via_code
,c.bill_to_orig_address_ref
,c.cust_tax_exempt_num
,-1
,sysdate
,sysdate
,-1
,204
,l_primary_site_use_flag
,'A'
);
end if;
commit;
l_flag := 'N';
l_primary_site_use_flag := 'N';
end loop;
end;
When we run this procedure in apps schema, this is procedure copy the data to interface tables from staging table

After then after we move to oracle apps and do the following steps
First go to responsibility: Receivables Vision Operations(USA)

Run the customer interface

The output of customer interface
Here all records are inserted in base tables with out any exception