Oracle Applications
Simple and easy steps to oracle Account receivables
A beginners guide for oracle Account receivables
-Oracle apps Account receivables
-Oracle apps Account receivables Customer creation using interface
Introduction -
we can use customer interface to import recent, current or past information from other systems into our database. If customer information is imported from our source of information that is flat file,CSV,XML etc we can use CUTOMER INTERFACE to import additional data for that perticular customer.
Lets see via Simple diagram how it could be done -
Step 1 - create or get a flat file in which the data about customer is feeded. Here we need at least two flat files for updating two mandatory interface tables which is
RA_CUSTOMERS_INTERFACE_ALL
and
RA_CUSTOMER_PROFILES_INT_ALL
so for
RA_CUSTOMERS_INTERFACE_ALL flat file is
01-00-22|RRCUS011|01-00-22|3951 OXCXEAIC DR||||OXXNSIDE|SAN DIEGO|CA||US|92056|BILL_TO|N|A|I|99936||999036||||CUSTOMER|4999186|1000|
and for
RA_CUSTOMER_PROFILES_INT_ALL flat file is
01-00-22|I|DEFAULT|N|99936| |99936| |1000|
Step 2 - for loading this data into staging tables we need SQL * Loader so
for first Flat file SQL * Loader is
LOAD DATA
INFILE *
APPEND INTO TABLE XX_TEMP_RCIA
fields terminated by '|'
TRAILING NULLCOLS
(
ORIG_SYSTEM_CUSTOMER_REF "TRIM(:ORIG_SYSTEM_CUSTOMER_REF)",
CUSTOMER_NAME "TRIM(:CUSTOMER_NAME)",
ORIG_SYSTEM_ADDRESS_REF "TRIM(:ORIG_SYSTEM_ADDRESS_REF)",
ADDRESS1 "TRIM(:ADDRESS1)",
ADDRESS2 "TRIM(:ADDRESS2)",
ADDRESS3 "TRIM(:ADDRESS3)",
ADDRESS4 "TRIM(:ADDRESS4)",
CITY "TRIM(:CITY)",
COUNTY "TRIM(:COUNTY)",
STATE "TRIM(:STATE)",
PROVINCE "TRIM(:PROVINCE)",
COUNTRY "TRIM(:COUNTRY)",
POSTAL_CODE "TRIM(:POSTAL_CODE)",
SITE_USE_CODE "TRIM(:SITE_USE_CODE)",
PRIMARY_SITE_USE_FLAG "TRIM(:PRIMARY_SITE_USE_FLAG)",
CUSTOMER_STATUS "TRIM(:CUSTOMER_STATUS)",
INSERT_UPDATE_FLAG "TRIM(:INSERT_UPDATE_FLAG)",
LAST_UPDATED_BY "TRIM(:LAST_UPDATED_BY)",
LAST_UPDATE_DATE "TRIM(:LAST_UPDATE_DATE )",
CREATED_BY "TRIM(:CREATED_BY)",
CREATION_DATE "TRIM(:CREATION_DATE)",
CUSTOMER_NUMBER "TRIM(:CUSTOMER_NUMBER)",
CUSTOMER_NAME_PHONETIC "TRIM(:CUSTOMER_NAME_PHONETIC)",
CUSTOMER_CATEGORY_CODE "TRIM(:CUSTOMER_CATEGORY_CODE)",
LOCATION "TRIM(:LOCATION)",
ORG_ID "TRIM(:ORG_ID)"
)
and for second Flat file SQL * Loader is
LOAD DATA
INFILE *
APPEND INTO TABLE XX_TEMP_RCPIA
FIELDS TERMINATED
BY '|'
TRAILING NULLCOLS
(
ORIG_SYSTEM_CUSTOMER_REF LOAD DATA
INFILE *
APPEND INTO TABLE XX_TEMP_RCPIA
FIELDS TERMINATED
BY '|'
TRAILING NULLCOLS
(
ORIG_SYSTEM_CUSTOMER_REF "TRIM(:ORIG_SYSTEM_CUSTOMER_REF)",
INSERT_UPDATE_FLAG "TRIM(:INSERT_UPDATE_FLAG)",
CUSTOMER_PROFILE_CLASS_NAME "TRIM(:CUSTOMER_PROFILE_CLASS_NAME)",
CREDIT_HOLD "TRIM(:CREDIT_HOLD)",
LAST_UPDATED_BY "TRIM(:LAST_UPDATED_BY)",
LAST_UPDATE_DATE "TRIM(:LAST_UPDATE_DATE)",
CREATED_BY "TRIM(:CREATED_BY)",
CREATION_DATE "TRIM(:CREATION_DATE)",
ORG_ID "TRIM(:ORG_ID )"
)
Step 3 - Now register this SQL Loader or control file into the Oracle application in System Admin Responsibility and first register the executable then create a concurrent program for both of the SQL Loader.
Step 4 - Now we need a PL/SQL block to insert data from our staging tables to interface tables
DECLARE
CURSOR CUR_RCIA ---===++ FECTING DATA FOR ra_customers_interface_all;
IS
SELECT * FROM XX_TEMP_RCIA;
CURSOR CUR_RCPIA ----===+ FETCHING DATA FOR ra_customer_profiles_int_all
IS
SELECT * FROM XX_TEMP_RCPIA;
-----------------
--VARIBALE FOR CUR_RCIA
-----------------
RCIA_FLAG VARCHAR2(3);
REC_RCIA XX_TEMP_RCIA%ROWTYPE;
C_ORIG_SYSTEM_CUSTOMER_REF VARCHAR2(240);
C_CUSTOMER_NAME VARCHAR2(360);
C_ORIG_SYSTEM_ADDRESS_REF VARCHAR2(240);
C_ADDRESS1 VARCHAR2(240);
C_ADDRESS2 VARCHAR2(240);
C_ADDRESS3 VARCHAR2(240);
C_ADDRESS4 VARCHAR2(240);
C_CITY VARCHAR2(240);
C_COUNTY VARCHAR2(240);
C_STATE VARCHAR2(240);
C_PROVINCE VARCHAR2(240);
C_COUNTRY VARCHAR2(240);
C_POSTAL_CODE VARCHAR2(240);
C_SITE_USE_CODE VARCHAR2(240);
C_PRIMARY_SITE_USE_FLAG VARCHAR2(1);
C_CUSTOMER_STATUS VARCHAR2(1);
C_INSERT_UPDATE_FLAG VARCHAR2(1);
C_LAST_UPDATED_BY VARCHAR2(30);
C_LAST_UPDATE_DATE DATE;
C_CREATED_BY VARCHAR2(30);
C_CREATION_DATE DATE;
C_CUSTOMER_NUMBER NUMBER(15);
C_CUSTOMER_NAME_PHONETIC VARCHAR2(320);
C_CUSTOMER_CATEGORY_CODE VARCHAR2(30);
C_LOCATION VARCHAR2(30);
C_ORG_ID NUMBER(15);
-----------------
--VARIBALE FOR CUR_RCPIA
-----------------
RCPIA_FLAG VARCHAR2(3);
REC_RCPIA XX_TEMP_RCPIA%ROWTYPE; --TEMP VARIABLE FOR FOR LOOP
CP_ORIG_SYSTEM_CUSTOMER_REF VARCHAR2(240);
CP_INSERT_UPDATE_FLAG VARCHAR2(1);
CP_CUSTOMER_PROFILE_CLASS_NAME VARCHAR2(30);
CP_CREDIT_HOLD VARCHAR2(1);
CP_LAST_UPDATED_BY NUMBER(15);
CP_LAST_UPDATE_DATE DATE;
CP_CREATED_BY NUMBER(15);
CP_CREATION_DATE DATE;
CP_ORG_ID NUMBER(15);
BEGIN
FOR REC_RCIA IN CUR_RCIA
LOOP
RCIA_FLAG := 'YES';
/*
================================================================================
SAMPLE VALIDATION======================================================================
================================================================================
*/
IF REC_RCIA.orig_system_customer_ref IS NOT NULL THEN
BEGIN
select ORIG_SYSTEM_CUSTOMER_REF
into C_ORIG_SYSTEM_CUSTOMER_REF
FROM AR.RA_CUSTOMERS_INTERFACE_ALL;
EXCEPTION
when OTHERS then
DBMS_OUTPUT.PUT_LINE('NO CUSTOMER REFERENCE');
RCIA_FLAG :='N';
END;
else
--if REC_RCIA.ORIG_SYSTEM_CUSTOMER_REF is null then
RCIA_FLAG :='N';
DBMS_OUTPUT.PUT_LINE('NO ITEM FOUND');
-- END IF; --IF FOR THIS >>IF REC_RCIA.orig_system_customer_ref IS NULL
END IF; --IF FOR THIS >>IF REC_RCIA.orig_system_customer_ref IS NOT NULL
/*
================================================================================
SAMPLE validation======================================================================
================================================================================
*/
IF REC_RCIA.CUSTOMER_NAME IS NOT NULL THEN
BEGIN
select CUSTOMER_NAME
into C_CUSTOMER_NAME
FROM AR.RA_CUSTOMERS_INTERFACE_ALL;
EXCEPTION
when OTHERS then
DBMS_OUTPUT.PUT_LINE('NO CUSTOMER name');
RCIA_FLAG :='N';
END;
else
RCIA_FLAG :='N';
DBMS_OUTPUT.PUT_LINE('NO ITEM FOUND');
-- END IF; --IF FOR THIS >>IF REC_RCIA.CUSTOMER_NAME IS NULL
END IF; --IF FOR THIS >>IF REC_RCIA.CUSTOMER_NAME IS NOT NULL
/*
================================================================================
SAMPLE validation======================================================================
================================================================================
*/
/*
================================================================================
SAMPLE validation======================================================================
================================================================================
*/
IF REC_RCIA.ORIG_SYSTEM_ADDRESS_REF IS NOT NULL THEN
BEGIN
select ORIG_SYSTEM_ADDRESS_REF
into C_ORIG_SYSTEM_ADDRESS_REF
FROM AR.RA_CUSTOMERS_INTERFACE_ALL;
EXCEPTION
when OTHERS then
DBMS_OUTPUT.PUT_LINE('ORIG_SYSTEM_ADDRESS_REF');
RCIA_FLAG :='N';
END;
else
RCIA_FLAG :='N';
DBMS_OUTPUT.PUT_LINE('NO ITEM FOUND');
-- END IF; --IF FOR THIS >>IF REC_RCIA.ORIG_SYSTEM_ADDRESS_REF IS NULL
END IF; --IF FOR THIS >>IF REC_RCIA.ORIG_SYSTEM_ADDRESS_REF IS NOT NULL
/*
================================================================================
SAMPLE validation======================================================================
================================================================================
*/
if RCIA_FLAG LIKE 'YES'
then
BEGIN
insert
INTO AR.RA_CUSTOMERS_INTERFACE_ALL
(
orig_system_customer_ref,
customer_name,
orig_system_address_ref,
address1,
address2,
address3,
address4,
city,
county,
state,
province,
country,
postal_code,
site_use_code,
primary_site_use_flag,
customer_status,
insert_update_flag,
last_updated_by,
last_update_date,
created_by,
creation_date,
customer_number,
customer_name_phonetic,
customer_category_code,
location,
ORG_ID
)
VALUES
(
REC_RCIA.orig_system_customer_ref,
REC_RCIA.customer_name,
REC_RCIA.orig_system_address_ref,
REC_RCIA.address1,
REC_RCIA.address2,
REC_RCIA.address3,
REC_RCIA.address4,
REC_RCIA.city,
REC_RCIA.county,
REC_RCIA.state,
REC_RCIA.province,
REC_RCIA.country,
REC_RCIA.postal_code,
REC_RCIA.site_use_code,
REC_RCIA.primary_site_use_flag,
REC_RCIA.customer_status,
REC_RCIA.insert_update_flag,
REC_RCIA.last_updated_by,
REC_RCIA.last_update_date,
REC_RCIA.created_by,
REC_RCIA.creation_date,
REC_RCIA.customer_number,
REC_RCIA.customer_name_phonetic,
REC_RCIA.customer_category_code,
REC_RCIA.LOCATION,
REC_RCIA.ORG_ID
);
end;
end if;
END LOOP;
FOR REC_RCPIA IN CUR_RCPIA
LOOP
insert
INTO ar.RA_CUSTOMER_PROFILES_INT_ALL
(
orig_system_customer_ref,
insert_update_flag,
customer_profile_class_name,
credit_hold,
last_updated_by,
last_update_date,
created_by,
creation_date,
ORG_ID
)
VALUES
(
REC_RCPIA.orig_system_customer_ref,
REC_RCPIA.INSERT_UPDATE_FLAG,
REC_RCPIA.CUSTOMER_PROFILE_CLASS_NAME,
REC_RCPIA.CREDIT_HOLD,
REC_RCPIA.LAST_UPDATED_BY,
REC_RCPIA.LAST_UPDATE_DATE,
REC_RCPIA.CREATED_BY,
REC_RCPIA.CREATION_DATE,
REC_RCPIA.ORG_ID
);
END LOOP;
END;
No comments:
Post a Comment