Oracle Customer interface tables
We will be discuss about the oracle customer interface tables. Customer Interface tables
helps to upload or import the Customers from External sources into the Oracle
environment or applications. Using Customer Interface tables, we can bulk load
the customers in oracle. Oracle customer
interface tables also helps to create the integration for customers in oracle
application which do create or update the customers information’s in oracle.
Oracle Customer interface tables works like bridge or mediator to import the
external customer data in oracle. We just need to prepare the customer data in
the form of Customer Interface tables. Here below is the detail explanation of oracle customer interface tables and the script to import
customers in oracle.
2 Most Important oracle customer interface tables
These are the two important customer interface table , which
help to upload the customers in oracle apps.
1.
ra_customers_interface_all
2.
ra_customer_profiles_int_all
Important Points about Oracle Customer Interface.
1.
We need to put the Data in the Interface table to
import in Oracle Application.
2.
We need to prepare and transform the data in the format
of Oracle Interface table. We need to do the Customer data conversion for
Customer Interface tables if we are importing the customers from non-oracle
environment.
3.
Once the data Uploaded into the Customer Interface
tables , need to run the Oracle Standard program ‘Customer Interface program’
which do transfers the data from Customer Interface tables to Oracle Customer
Base tables in oracle apps r12.
PLSQL Customer Import Script using Oracle Customer Interface Tables
PROCEDURE xx_cust_interface
IS
------------------Cursor For Customer Detail
v_org_id NUMBER;
v_erro_msg VARCHAR2 (32765);
v_ref_number VARCHAR2 (2000);
f_ref_number VARCHAR2 (2000);
o_ref_number VARCHAR2 (2000);
r_ref_number VARCHAR2 (2000);
m_ref_number VARCHAR2 (2000);
e_ref_number VARCHAR2 (2000);
contact_ref VARCHAR2 (2000);
v_ref_number_ship VARCHAR2 (2000);
v_ship_count NUMBER;
v_primary_site_use_flag VARCHAR2 (20);
v_payment_terms_id NUMBER;
CURSOR cur_customer_detail
IS
SELECT
FROM xx_customer_stag
WHERE
NVL (vailidate_flag_process, 'E') = 'P'
AND NVL(flag_process ,'NULL') = 'NULL'
AND UPPER (site_use_code) = 'BILL_TO'
GROUP BY org_code,
site_use_code,
CUSTOMER_NUMBER,
CUSTOMER_NAME;
CURSOR cur_customer_detail_ship_to (
v_customer_name VARCHAR2,
v_customer_number VARCHAR2,
v_org_code VARCHAR2
)
IS
SELECT /*+ USE_HASH_AGGREGATION INDEX_RS_ASC (xx_customer_stag
xx_customer_stag_5)*/ *
FROM xx_customer_stag
WHERE
NVL (vailidate_flag_process, 'E') = 'P'
AND NVL (flag_process, 'NULL') = 'NULL'
AND UPPER (site_use_code) = 'SHIP_TO'
AND customer_name = v_customer_name
AND NVL (customer_number, 'E') = NVL (v_customer_number, 'E')
AND org_code = v_org_code
GROUP BY org_code,
site_use_code,
customer_number,
customer_name,
address1,
address2,
ADDRESS3,
ADDRESS4;
CURSOR cur_get_org_id (v_org_code VARCHAR2)
IS
SELECT organization_id
FROM hr_operating_units
WHERE UPPER (NAME) = UPPER (v_org_code);
CURSOR cur_term_id (v_name VARCHAR2)
IS
SELECT term_id
FROM ra_terms
WHERE NAME = v_name;
IS
------------------Cursor For Customer Detail
v_org_id NUMBER;
v_erro_msg VARCHAR2 (32765);
v_ref_number VARCHAR2 (2000);
f_ref_number VARCHAR2 (2000);
o_ref_number VARCHAR2 (2000);
r_ref_number VARCHAR2 (2000);
m_ref_number VARCHAR2 (2000);
e_ref_number VARCHAR2 (2000);
contact_ref VARCHAR2 (2000);
v_ref_number_ship VARCHAR2 (2000);
v_ship_count NUMBER;
v_primary_site_use_flag VARCHAR2 (20);
v_payment_terms_id NUMBER;
CURSOR cur_customer_detail
IS
SELECT
FROM xx_customer_stag
WHERE
NVL (vailidate_flag_process, 'E') = 'P'
AND NVL(flag_process ,'NULL') = 'NULL'
AND UPPER (site_use_code) = 'BILL_TO'
GROUP BY org_code,
site_use_code,
CUSTOMER_NUMBER,
CUSTOMER_NAME;
CURSOR cur_customer_detail_ship_to (
v_customer_name VARCHAR2,
v_customer_number VARCHAR2,
v_org_code VARCHAR2
)
IS
SELECT /*+ USE_HASH_AGGREGATION INDEX_RS_ASC (xx_customer_stag
xx_customer_stag_5)*/ *
FROM xx_customer_stag
WHERE
NVL (vailidate_flag_process, 'E') = 'P'
AND NVL (flag_process, 'NULL') = 'NULL'
AND UPPER (site_use_code) = 'SHIP_TO'
AND customer_name = v_customer_name
AND NVL (customer_number, 'E') = NVL (v_customer_number, 'E')
AND org_code = v_org_code
GROUP BY org_code,
site_use_code,
customer_number,
customer_name,
address1,
address2,
ADDRESS3,
ADDRESS4;
CURSOR cur_get_org_id (v_org_code VARCHAR2)
IS
SELECT organization_id
FROM hr_operating_units
WHERE UPPER (NAME) = UPPER (v_org_code);
CURSOR cur_term_id (v_name VARCHAR2)
IS
SELECT term_id
FROM ra_terms
WHERE NAME = v_name;
BEGIN
FOR cur_det IN cur_customer_detail
LOOP
v_ref_number := 'AA' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);
f_ref_number := 'FF' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);
FOR cur_det IN cur_customer_detail
LOOP
v_ref_number := 'AA' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);
f_ref_number := 'FF' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);
o_ref_number := 'OO' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);
r_ref_number := 'RR' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);
m_ref_number := 'MM' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);
e_ref_number := 'EE' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);
IF cur_det.contact_last_name IS NULL
THEN
contact_ref := NULL;
ELSE
contact_ref := 'CC' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);
END IF;
BEGIN
OPEN cur_get_org_id (cur_det.org_code);
FETCH cur_get_org_id
INTO v_org_id;
IF cur_get_org_id%NOTFOUND
THEN
v_org_id := NULL;
CLOSE cur_get_org_id;
END IF;
CLOSE cur_get_org_id;
INSERT INTO ra_customers_interface_all
(org_id, orig_system_customer_ref, site_use_code,
orig_system_address_ref, insert_update_flag,
customer_name, customer_number, customer_status,
primary_site_use_flag, LOCATION, address1,
address2, address3,
address4, city, state,
postal_code, country, cust_tax_reference,
last_updated_by, last_update_date, created_by,
creation_date, person_flag, person_first_name,
person_last_name, orig_system_party_ref,
customer_name_phonetic,
customer_category_code
)
VALUES (v_org_id, v_ref_number, cur_det.site_use_code,
v_ref_number, 'I',
cur_det.customer_name, cur_det.customer_number, 'A',
'Y', cur_det.LOCATION, cur_det.address1,
cur_det.address2, cur_det.address3,
cur_det.address4, cur_det.city, cur_det.state,
cur_det.postal_code, cur_det.country, NULL,
0, SYSDATE, 0,
SYSDATE, cur_det.person_flag, cur_det.first_name,
cur_det.last_name, cur_det.party_reference,
cur_det.alternate_name,
UPPER (cur_det.customer_category_code)
);
v_ship_count := 1;
------------------------------- SHIP----------------------------
FOR cur_det_ship IN
cur_customer_detail_ship_to (cur_det.customer_name,
cur_det.customer_number,
cur_det.org_code
)
LOOP
IF UPPER ( cur_det_ship.address1
|| cur_det_ship.address2
|| cur_det_ship.address3
|| cur_det_ship.address4
) <>
UPPER ( cur_det.address1
|| cur_det.address2
|| cur_det.address3
|| cur_det.address4
)
THEN
v_ref_number_ship :=
'A' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);
v_primary_site_use_flag := 'N';
ELSIF UPPER ( cur_det_ship.address1
|| cur_det_ship.address2
|| cur_det_ship.address3
|| cur_det_ship.address4
) =
UPPER ( cur_det.address1
|| cur_det.address2
|| cur_det.address3
|| cur_det.address4
)
THEN
v_ref_number_ship := v_ref_number;
v_primary_site_use_flag := 'Y';
END IF;
INSERT INTO ra_customers_interface_all
(org_id, orig_system_customer_ref,
site_use_code, orig_system_address_ref,
insert_update_flag, customer_name,
customer_number, customer_status,
primary_site_use_flag, LOCATION,
address1, address2,
address3, address4,
city, state,
postal_code, country,
cust_tax_reference, last_updated_by,
last_update_date, created_by, creation_date,
person_flag,
person_first_name, person_last_name,
orig_system_party_ref,
customer_name_phonetic
)
VALUES (v_org_id, v_ref_number,
cur_det_ship.site_use_code, v_ref_number_ship,
'I', cur_det_ship.customer_name,
cur_det_ship.customer_number, 'A',
v_primary_site_use_flag, cur_det_ship.LOCATION,
cur_det_ship.address1, cur_det_ship.address2,
cur_det_ship.address3, cur_det_ship.address4,
cur_det_ship.city, cur_det_ship.state,
cur_det_ship.postal_code, cur_det_ship.country,
NULL, 0,
SYSDATE, 0, SYSDATE,
cur_det_ship.person_flag,
cur_det_ship.first_name, cur_det_ship.last_name,
cur_det_ship.party_reference,
cur_det_ship.alternate_name
);
v_ship_count := 1 + 1;
UPDATE xx_customer_stag
SET flag_process = 'P',
upload_no = v_ref_number_ship
WHERE entry_id = cur_det_ship.entry_id;
END LOOP;
INSERT INTO ra_customer_profiles_int_all
(org_id, insert_update_flag,
orig_system_customer_ref,
customer_profile_class_name, credit_checking,
credit_hold,
currency_code,
overall_credit_limit,
trx_credit_limit,
-- STANDARD_TERMS ,
last_updated_by, last_update_date,
created_by, creation_date
)
VALUES (v_org_id, 'I',
v_ref_number,
'DEFAULT', NVL (cur_det.credit_checking_flag, 'N'),
NVL (cur_det.credit_hold_flag, 'N'),
NVL (cur_det.currency_code, 'INR'),
cur_det.over_all_credit_limit,
cur_det.order_credit_limit,
-- V_PAYMENT_TERMS_ID ,
-1, SYSDATE,
-1, SYSDATE
);
UPDATE xx_customer_stag
SET flag_process = 'P',
upload_no = v_ref_number
WHERE entry_id = cur_det.entry_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
v_erro_msg := SQLERRM;
UPDATE xx_customer_stag
SET flag_process = 'E',
error_message = v_erro_msg
WHERE entry_id = cur_det.entry_id;
COMMIT;
END;
END LOOP;
BEGIN
SELECT COUNT (*)
INTO vailidate_num
FROM xx_customer_stag
WHERE flag_process = 'E';
EXCEPTION
WHEN OTHERS
THEN
vailidate_num := 0;
END;
END xx_cust_interface.
0 comments:
Post a Comment