Customer Interface in oracle apps r12
In this post , we will be discuss about customer interface in oracle apps r12. Customer Interface in oracle apps , helps to import the customer from external system in to the Oracle application. Customer Interface , helps to Mass upload the customers in oracle system. We can create the bulk customers using customer interface in oracle apps r12. we can also create the customer integration using customer interface in oracle apps r12. Here below is the detail about customer interface in oracle apps r12.
 |
| customer interface in oracle apps r12 |
Important Points about customer interface in oracle apps r12.
RA_CUSTOMERS_INTERFACE
Name
|
Address
Reference |
Address
|
Site
Code |
|
C1001
|
ACME
|
A2001
|
200
Main
|
BILL_TO
|
C1001
|
ACME
|
A2001
|
200
Main
|
SHIP_TO
|
C1001
|
ACME
|
A2002
|
550
State
|
SHIP_TO
|
RA_CUSTOMER_PROFILES_INTERFACE
Address
Reference |
Profile
Name |
|
C1001
|
Good
|
RA_CONTACT_PHONES_INTERFACE
Address
Reference
|
Contact
Reference
|
Last
Name |
Phone
Reference
|
Phone
Number |
|
C1001
|
A2001
|
S4001
|
KWAN
|
S5001
|
555-1234
|
C1001
|
A2001
|
S4001
|
KWAN
|
S5002
|
555-7000
|
C1001
|
A2001
|
S4002
|
SMITH
|
||
C1001
|
A2001
|
S5003
|
474-8664
|
||
C1001
|
A2002
|
S4003
|
BASS
|
||
C1001
|
S4004
|
RUDIN
|
|||
C1001
|
S5004
|
506-7000
|
|||
Table
1 - 13. (Page 1 of 1)
|
|||||
To import a customer with multiple addresses:- Enter multiple records into RA_CUSTOMERS_INTERFACE
with identical ORIG_SYSTEM_CUSTOMER_REF values, but different
ORIG_SYSTEM_ADDRESS_REF values.
To import a customer with multiple contacts: - Enter multiple records into
RA_CONTACT_PHONES_INTERFACE with identical ORIG_SYSTEM_CUSTOMER_REF
values, but different ORIG_SYSTEM_CONTACT_REF values.
To import a customer with multiple telephone numbers:- Enter multiple records into
RA_CONTACT_PHONES_INTERFACE with identical ORIG_SYSTEM_CUSTOMER_REF
values, but different ORIG_SYSTEM_TELEPHONE_REF values.
To import an address with multiple business purposes: - Enter multiple records into RA_CUSTOMERS_INTERFACE
with identical ORIG_SYSTEM_CUSTOMER_REF and ORIG_SYSTEM_ADDRESS_REF
values, but different SITE_USE_CODES values.
To import an address with multiple contacts: - Enter multiple records into RA_CONTACT_PHONES_INTERFACE
with identical ORIG_SYSTEM_CUSTOMER_REF and ORIG_SYSTEM_ADDRESS_REF
values, but different ORIG_SYSTEM_CONTACT_REF values.
To import an address with multiple telephone numbers: - Enter multiple records into
RA_CONTACT_PHONES_INTERFACE with identical ORIG_SYSTEM_CUSTOMER_REF and
ORIG_SYSTEM_ADDRESS_REF values, but different ORIG_SYSTEM_TELEPHONE_REF
values.
To import a contact with multiple telephone numbers: - Enter multiple records into RA_CONTACT_PHONES_INTERFACE with identical ORIG_SYSTEM_CUSTOMER_REF and ORIG_SYSTEM_CONTACT_REF values, but different ORIG_SYSTEM_TELEPHONE_REF values.
There are four activities in loading of customer Master.
1. Load data from Excel file in to the Staging tables with
the help of sql loader.
2. When we have loaded data in staging tables then the
second thing is to validate the data means to check whether this data is ok to
load in Customer Interfaces Tables.
3. Records which successful in Validation test will go to
customer Interface.
4. Customer Import Program will run.
Detail PLSQL Script about customer interface in oracle apps r12
Create or replace procedure xx_validate_customer is
CURSOR C IS
select load_id,ORG_CODE,SITE_USE_CODE,CUSTOMER_NAME,ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,CITY,STATE,POSTAL_CODE,COUNTRY,PAYMENT_TERMS
from XX_CUSTOMER_LOAD_STG
where process_flag<>'Y';
V_org NUMBER;
V_SITE_USE NUMBER;
V_COUNTRY NUMBER;
V_TERM NUMBER;
v_name number;
BEGIN
FOR I IN C LOOP
BEGIN
SELECT COUNT (*) INTO V_org
FROM hr_operating_units
WHERE UPPER (NAME) = UPPER (I.ORG_CODE);
EXCEPTION WHEN OTHERS THEN
V_CUSTOMER:=0;
END;
BEGIN
SELECT COUNT(*) INTO
V_SITE_USE
FROM ar_lookups
WHERE lookup_type = 'SITE_USE_CODE'
AND lookup_code =I.SITE_USE_CODE
EXCEPTION WHEN OTHERS THEN
V_SITE_USE:=0;
END;
BEGIN
SELECT COUNT(*) INTO
V_COUNTRY
FROM
fnd_territories_tl
WHERE UPPER (territory_code) =
UPPER (I.COUNTRY)
EXCEPTION WHEN OTHERS THEN
V_COUNTRY:=0;
END;
BEGIN
SELECT COUNT(*) INTO
V_TERM
FROM ra_terms
WHERE NAME = I.PAYMENT_TERMS
EXCEPTION WHEN OTHERS THEN
V_TERM:=0;
END;
BEGIN
SELECT COUNT(*) INTO
V_TERM
FROM ra_terms
WHERE NAME = I.PAYMENT_TERMS
EXCEPTION WHEN OTHERS THEN
V_TERM:=0;
END;
BEGIN
SELECT COUNT(*) INTO
V_party
FROM hz_parties
WHERE party_name=i.CUSTOMER_NAME;
EXCEPTION WHEN OTHERS THEN
V_TERM:=0;
END;
if V_org<>0 and V_SITE_USE<>0 and V_COUNTRY<>0 and V_TERM <>0 and V_party = 0 then
update XX_CUSTOMER_LOAD_STG
SET VALIDATE_FLAG='Y'
WHERE LOAD_ID=I.LOAD_ID;
ELSE
update XX_CUSTOMER_LOAD_STG
SET VALIDATE_FLAG='N'
WHERE LOAD_ID=I.LOAD_ID;
END IF;
END LOOP;
END ;
create or replace procedure xx_LOAD_customer is
CURSOR C IS
select load_id,ORG_CODE,SITE_USE_CODE,CUSTOMER_NAME,ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,CITY,STATE,POSTAL_CODE,COUNTRY,PAYMENT_TERMS,credit_checking_flag,credit_hold_flag,
over_all_credit_limit,ORDER_credit_limit
from XX_CUSTOMER_LOAD_STG
where process_flag<>'Y'
AND VALIDATE_FLAG='Y'
AND SITE_USE_CODE='BILL_TO'
AND load_id NOT IN (SELECT LOAD_ID FROM XX_CUSTOMER_LOAD_STG WHERE process_flag='Y');
CURSOR B(CUSTOMER_N VARCHAR2,ORG_C VARCHAR2) IS
select load_id,ORG_CODE,SITE_USE_CODE,CUSTOMER_NAME,ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,CITY,STATE,POSTAL_CODE,COUNTRY,PAYMENT_TERMS
from XX_CUSTOMER_LOAD_STG
where process_flag<>'Y'
AND VALIDATE_FLAG='Y'
AND SITE_USE_CODE='SHIP_TO'
AND CUSTOMER_NAME=CUSTOMER_N
AND ORG_CODE=ORG_C
AND load_id NOT IN (SELECT LOAD_ID FROM XX_CUSTOMER_LOAD_STG WHERE process_flag='Y');
V_ORG_ID NUMBER;
V_CUST_REF
VARCHAR2(400);
V_ADDR_REF VARCHAR2(400);
BEGIN
FOR I IN C LOOP
SELECT organization_id INTO
V_ORG_ID
FROM hr_operating_units
WHERE UPPER (NAME) = UPPER (I.ORG_CODE);
V_CUST_REF:='C'||XX_CUST_REF.NEXTVAL;
V_ADDR_REF:='A'||XX_ADDR_REF.NEXTVAL;
UPDATE xxfilix_customer_stag
SET flag_process = 'P',
CUSTOMER_REF=V_CUST_REF,
ADDRESS_REF=V_ADDR_REF
WHERE LOAD_ID=I.LOAD_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_status,
primary_site_use_flag, 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_CUST_REF,I.SITE_USE_CODE,
V_ADDR_REF,'I',
I.CUSTOMER_NAME, 'A',
'Y',I.ADDRESS1,
I.ADDRESS2,I.ADDRESS3,
I.ADDRESS4,I.CITY,I.STATE,
I.POSTAL_CODE,I.COUNTRY,NULL,
0, SYSDATE, 0,
SYSDATE,NULL,I.CUSTOMER_NAME,
NULL,NULL,
NULL,
NULL
);
FOR J IN B LOOP
IF IF UPPER (J.address1
||J.address2
||J.address3
||J.address4
) <>
UPPER (I.address1
||I.address2
||I.address3
||I.address4
)
V_ADDR_REF:='A'||XX_ADDR_REF.NEXTVAL;
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_status,
primary_site_use_flag, 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_CUST_REF,I.SITE_USE_CODE,
V_ADDR_REF,'I',
I.CUSTOMER_NAME, 'A',
'Y',I.ADDRESS1,
I.ADDRESS2,I.ADDRESS3,
I.ADDRESS4,I.CITY,I.STATE,
I.POSTAL_CODE,I.COUNTRY,NULL,
0, SYSDATE, 0,
SYSDATE,NULL,I.CUSTOMER_NAME,
NULL,NULL,
NULL,
NULL
);
UPDATE xxfilix_customer_stag
SET flag_process = 'P',
CUSTOMER_REF=V_CUST_REF,
ADDRESS_REF=V_ADDR_REF
WHERE LOAD_ID =J.LOAD_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_CUST_REF,
'DEFAULT', NVL (I.credit_checking_flag, 'N'),
NVL (I.credit_hold_flag, 'N'),
NVL (I.currency_code, 'INR'),
I.over_all_credit_limit,
I.order_credit_limit,
-- V_PAYMENT_TERMS_ID ,
-1, SYSDATE,
-1, SYSDATE
);
END
LOOP;
END;
create or replace procedure xx_LOAD_customer_PHONES is
CURSOR C IS
select load_id,ORG_CODE,CONTACT_FIRST_NAME,CONTACT_LAST_NAME,RES_TELEPHONE_NO,CUSTOMER_REF,ADDRESS_REF,FAX_NO,EMAIL
from XX_CUSTOMER_LOAD_STG
where process_flag='Y'
AND VALIDATE_FLAG='Y';
V_CONTACT_REF
VARCHAR2(400);
V_PHONE_REF
VARCHAR2(40);
V_ORG_ID NUMBER;
BEGIN
FOR I IN C LOOP
BEGIN
SELECT ORG_ID INTO V_org_ID
FROM hr_operating_units
WHERE UPPER (NAME) = UPPER (I.ORG_CODE);
EXCEPTION WHEN OTHERS THEN
V_CUSTOMER:=0;
END;
IF CONTACT_FIRST_NAME IS NOT NULL AND CONTACT_LAST_NAME IS NOT NULL
V_CONTACT_REF :='C'||XX_CONTACT_REF.NEXTVAL;
ELSE
V_CONTACT_REF :=NULL;
END IF;
V_PHONE_REF :='P'||XX_PHONE_REF.NEXTVAL;
IF RES_TELEPHONE_NO IS NOT NULL
INSERT INTO ar.ra_contact_phones_int_all
(orig_system_customer_ref,
orig_system_address_ref,
orig_system_contact_ref,
orig_system_telephone_ref,
insert_update_flag,
contact_first_name,
contact_last_name,
telephone,
telephone_type,
last_update_date,
last_updated_by, creation_date,
created_by, org_id,
contact_point_type
)
VALUES (I.CUSTOMER_REF ,
I.ADDRESS_REF,
V_CONTACT_REF,
V_PHONE_REF ,
'I' ,
I.contact_first_name,
I.contact_last_name ,
I.RES_TELEPHONE_NO,
'GEN' ,
SYSDATE,
0,
SYSDATE ,
0 ,
, v_org_id ,
'PHONE'
);
END IF ;
IF FAX_NO IS NOT NULL
INSERT INTO ar.ra_contact_phones_int_all
(orig_system_customer_ref,
orig_system_address_ref,
orig_system_contact_ref,
orig_system_telephone_ref,
insert_update_flag,
contact_first_name,
contact_last_name,
telephone,
telephone_type,
last_update_date,
last_updated_by, creation_date,
created_by, org_id,
contact_point_type
)
VALUES (I.CUSTOMER_REF ,
I.ADDRESS_REF,
V_CONTACT_REF,
V_PHONE_REF ,
'I' ,
I.contact_first_name,
I.contact_last_name ,
I.RES_TELEPHONE_NO,
'FAX' ,
SYSDATE,
0,
SYSDATE ,
0 ,
, v_org_id ,
'PHONE'
);
END IF ;
IF EMAIL IS NOT NULL
INSERT INTO ar.ra_contact_phones_int_all
(orig_system_customer_ref,
orig_system_address_ref,
orig_system_contact_ref,
orig_system_telephone_ref,
insert_update_flag,
contact_first_name,
contact_last_name,
email_address,
last_update_date,
last_updated_by, creation_date,
created_by, org_id,
contact_point_type
)
VALUES (I.CUSTOMER_REF ,
I.ADDRESS_REF,
V_CONTACT_REF,
V_PHONE_REF ,
'I' ,
I.contact_first_name,
I.contact_last_name ,
I.EMAIL,
SYSDATE,
0,
SYSDATE ,
0 ,
, v_org_id ,
'EMAIL'
);
END IF ;
END;
 |
| customer interface in oracle apps r12 |
customer interface in oracle apps r12
0 comments:
Post a Comment