CREATE OR REPLACE PACKAGE BODY APPS.

xx_customer_imp_pkg
IS
PROCEDURE xx_customer_imp_proc (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
v_cust_ref VARCHAR2 (50);
v_add_ref VARCHAR2 (50);
v_cont_ref VARCHAR2 (50);
v_tele_ref VARCHAR2 (50);
v_fax_ref VARCHAR2 (50);
x NUMBER;
v_cust_ref1 VARCHAR2 (50);
v_add_ref1 VARCHAR2 (50);
v_cont_ref1 VARCHAR2 (50);
v_tele_ref1 VARCHAR2 (50);
v_fax_ref1 VARCHAR2 (50);
p_customer_number VARCHAR2 (150);
p_primary_site_use_flag VARCHAR2 (1);
v_country VARCHAR2 (50);
v_classification_code VARCHAR2 (50);
v_gl_id_rec NUMBER;
v_gl_id_rev NUMBER;
v_gl_id_tax NUMBER;
CURSOR c1
IS
SELECT a.ROWID, a.*
FROM xx_customer_stg a
WHERE a.status_code = 'N' and ORG_ID=106;
BEGIN
FOR c_cust IN c1
LOOP
BEGIN
p_customer_number := c_cust.customer_number;
p_primary_site_use_flag := 'Y';
SELECT xx_cust_ref_s.NEXTVAL
INTO v_cust_ref1
FROM DUAL;
v_cust_ref := 'CUST' || v_cust_ref1;
SELECT xx_cust_add_ref_s.NEXTVAL
INTO v_add_ref1
FROM DUAL;
v_add_ref := 'CUST' || v_add_ref1;
BEGIN
SELECT lookup_code
INTO v_classification_code
FROM fnd_lookup_values
WHERE lookup_type = 'CUSTOMER CLASS'
AND UPPER (meaning) = UPPER (TRIM (c_cust.classification));
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,c_cust.classification||' '||SQ
LCODE || ' ' || SQLERRM);
END;
 BEGIN
SELECT lookup_code
INTO v_country
FROM fnd_lookup_values
WHERE lookup_type = 'GHR_US_CNTRY_WRLD_CTZN'
AND UPPER (meaning) = UPPER (TRIM (c_cust.country));
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,c_cust.country||' '||SQLCODE |
| ' ' || SQLERRM);
END;
BEGIN
SELECT code_combination_id
INTO v_gl_id_rec
FROM gl_code_combinations
WHERE segment1
|| '.'
|| segment2
|| '.'
|| segment3
|| '.'
|| segment4
|| '.'
|| segment5
|| '.'
|| segment6
|| '.'
|| segment7 = TRIM (c_cust.gl_id_rec);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,'GL REC : '||c_cust.gl_id_rec|
|' '|| SQLCODE || ' ' || SQLERRM);
END;
BEGIN
SELECT code_combination_id
INTO v_gl_id_rev
FROM gl_code_combinations
WHERE segment1
|| '.'
|| segment2
|| '.'
|| segment3
|| '.'
|| segment4
|| '.'
|| segment5
|| '.'
|| segment6
|| '.'
|| segment7 = TRIM (c_cust.gl_id_rev);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'GL REV : '||c_cust.gl_id_rev
||' '||SQLCODE || ' ' || SQLERRM);
END;
 BEGIN
SELECT code_combination_id
INTO v_gl_id_tax
FROM gl_code_combinations
WHERE segment1
|| '.'
|| segment2
|| '.'
|| segment3
|| '.'
|| segment4
|| '.'
|| segment5
|| '.'
|| segment6
|| '.'
|| segment7 = TRIM (c_cust.gl_id_tax);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'GL TAX : '||c_cust.gl_id_tax
||' '||SQLCODE || ' ' || SQLERRM);
END;

BEGIN
INSERT INTO ra_customers_interface_all
(orig_system_customer_ref,
orig_system_address_ref,
insert_update_flag,
last_updated_by,
last_update_date,
created_by,
creation_date,
-- customer_number,
customer_name,
customer_class_code,
customer_status,
address1,
address2,
city,
country,
postal_code,
gl_id_rec,
gl_id_rev,
gl_id_tax,
primary_site_use_flag,
site_use_code,
org_id,
customer_attribute_category,
customer_attribute1
)
VALUES (TRIM (c_cust.customer_number),--v_cust_ref,
v_add_ref,
'I', 1110,
SYSDATE, 1110, SYSDATE,
-- TRIM (c_cust.customer_number),
TRIM (c_cust.customer_name), v_classification_code,
'A', TRIM (c_cust.address1), TRIM (c_cust.address2),
 TRIM (c_cust.city), v_country,
TRIM (c_cust.postal_code), v_gl_id_rec,
v_gl_id_rev, v_gl_id_tax,
p_primary_site_use_flag, 'BILL_TO',
(c_cust.org_id), 'SUB CATEGORY',
c_cust.sub_catergory
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, '1 : '||c_cust.Customer_Name||'
'||SQLCODE || ' ' || SQLERRM);
END;
COMMIT;
BEGIN
INSERT INTO ra_customers_interface_all
(orig_system_customer_ref,
orig_system_address_ref,
insert_update_flag,
last_updated_by,
last_update_date,
created_by,
creation_date,
-- customer_number,
customer_name,
customer_class_code,
customer_status,
address1,
address2,
city,
country,
postal_code,
-- gl_id_rec,
-- gl_id_rev,
-- gl_id_tax,
primary_site_use_flag,
site_use_code,
org_id,
customer_attribute_category,
customer_attribute1
)
VALUES (TRIM (c_cust.customer_number),--v_cust_ref,
v_add_ref,
'I', 1110,
SYSDATE, 1110, SYSDATE,
-- TRIM (c_cust.customer_number),
TRIM (c_cust.customer_name), v_classification_code,
'A', TRIM (c_cust.address1), TRIM (c_cust.address2),
TRIM (c_cust.city), v_country,
TRIM (c_cust.postal_code),
-- v_gl_id_rec,
-- v_gl_id_rev, v_gl_id_tax,
p_primary_site_use_flag, 'SHIP_TO',
(c_cust.org_id), 'SUB CATEGORY',
c_cust.sub_catergory
);
EXCEPTION
 WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, '2 : '||c_cust.Customer_Name||'
'||SQLCODE || ' ' || SQLERRM);
END;
COMMIT;
BEGIN
INSERT INTO ra_customer_profiles_int_all
(insert_update_flag, orig_system_customer_ref,
customer_profile_class_name,
standard_term_name,
statements,
-- dunning_letters,
statement_cycle_name,
credit_checking,
credit_hold,
currency_code,CREDIT_BALANCE_STATEMENTS,
overall_credit_limit,trx_credit_limit, last_updated_by,
last_update_date, created_by, creation_date, org_id
)
VALUES ('I', TRIM (c_cust.customer_number),--v_cust_ref,
'DEFAULT',
TRIM (c_cust.payment_terms),
TRIM (c_cust.statements),
-- 'N',--TRIM (c_cust.dunning_letters),
TRIM (c_cust.statement_cycle_name),
TRIM (c_cust.credit_checking),
TRIM (c_cust.credit_hold),
TRIM (c_cust.currency_code),'N',
(c_cust.overall_credit_limit),(c_cust.overall_credit_li
mit), 1110,
SYSDATE, 1110, SYSDATE, (c_cust.org_id)
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, '3 : '||c_cust.Customer_Name||'
'||SQLCODE || ' ' || SQLERRM);
END;
COMMIT;
/*BEGIN
INSERT INTO ra_customer_profiles_int_all
(insert_update_flag, orig_system_customer_ref,
customer_profile_class_name,
standard_term_name,
statements,
dunning_letters,
statement_cycle_name,
credit_checking,
credit_hold,
currency_code,
overall_credit_limit, last_updated_by,
last_update_date, created_by, creation_date, org_id
)
VALUES ('I', v_cust_ref,
TRIM (c_cust.customer_profile_class_name),
 TRIM (c_cust.payment_terms),
TRIM (c_cust.statements),
TRIM (c_cust.dunning_letters),
TRIM (c_cust.statement_cycle_name),
TRIM (c_cust.credit_checking),
TRIM (c_cust.credit_hold),
TRIM (c_cust.currency_code),
(c_cust.overall_credit_limit), 1110,
SYSDATE, 1110, SYSDATE, (c_cust.org_id)
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, '4 : '||c_cust.Customer_Name||'
'||SQLCODE || ' ' || SQLERRM);
END;
COMMIT;*/

BEGIN
IF c_cust.cust_telephone1 IS NOT NULL
THEN
SELECT xx_cust_tele_ref_s.NEXTVAL
INTO v_tele_ref1
FROM DUAL;
v_tele_ref := 'CUST' || v_tele_ref1;
x := x + 1;

INSERT INTO ra_contact_phones_int_all

(orig_system_customer_ref,
orig_system_telephone_ref, insert_update_flag,
telephone_type, telephone, last_updated_by,
created_by, creation_date, last_update_date,
org_id, contact_point_type
)
VALUES (TRIM (c_cust.customer_number),--v_cust_ref,
v_tele_ref, 'I',
'GEN', TRIM (c_cust.cust_telephone1), 1110,
1110, SYSDATE, SYSDATE,
(c_cust.org_id), 'PHONE'
);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, '5 : '||c_cust.Customer_Name||'
'||SQLCODE || ' ' || SQLERRM);
END;
BEGIN
IF c_cust.contact_name1 IS NOT NULL
THEN
SELECT xx_cust_cont_ref_s.NEXTVAL
INTO v_cont_ref1
FROM DUAL;
 v_cont_ref := 'CUST' || v_cont_ref1;
IF c_cust.contact_phone1 IS NOT NULL
THEN
SELECT xx_cust_tele_ref_s.NEXTVAL
INTO v_tele_ref1
FROM DUAL;
v_tele_ref := 'CUST' || v_tele_ref1;
x := x + 1;
INSERT INTO ra_contact_phones_int_all
(orig_system_customer_ref,
orig_system_address_ref,
orig_system_telephone_ref,
orig_system_contact_ref, insert_update_flag,
telephone_type, telephone, last_updated_by,
created_by, creation_date, last_update_date,
contact_last_name,
org_id, contact_point_type
)
VALUES (TRIM (c_cust.customer_number),--v_cust_ref,
v_add_ref,
v_tele_ref,
v_cont_ref, 'I',
'GEN', c_cust.contact_phone1, 1110,
1110, SYSDATE, SYSDATE,
TRIM (c_cust.contact_name1),
(c_cust.org_id), 'PHONE'
);
COMMIT;
ELSE
x := x + 1;
INSERT INTO ra_contact_phones_int_all
(orig_system_customer_ref,
orig_system_address_ref,
orig_system_contact_ref, insert_update_flag,
last_updated_by, created_by, creation_date,
last_update_date, contact_last_name,
org_id
)
VALUES (TRIM (c_cust.customer_number),--v_cust_ref,
v_add_ref,
v_cont_ref, 'I',
1110, 1110, SYSDATE,
SYSDATE, TRIM (c_cust.contact_name1),
(c_cust.org_id)
);
COMMIT;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, '6 : '||c_cust.Customer_Name||'
 '||SQLCODE || ' ' || SQLERRM);
END;

BEGIN
IF c_cust.contact_name2 IS NOT NULL
THEN
SELECT xx_cust_cont_ref_s.NEXTVAL
INTO v_cont_ref1
FROM DUAL;
v_cont_ref := 'CUST' || v_cont_ref1;
IF c_cust.contact_phone2 IS NOT NULL
THEN
SELECT xx_cust_tele_ref_s.NEXTVAL
INTO v_tele_ref1
FROM DUAL;
v_tele_ref := 'CUST' || v_tele_ref1;
x := x + 1;
INSERT INTO ra_contact_phones_int_all
(orig_system_customer_ref,
--orig_system_address_ref,
orig_system_telephone_ref,
orig_system_contact_ref, insert_update_flag,
telephone_type, telephone, last_updated_by,
created_by, creation_date, last_update_date,
contact_last_name,
-- email_address,
org_id, contact_point_type
)
VALUES (TRIM (c_cust.customer_number),--v_cust_ref,
--v_add_ref,
v_tele_ref,
v_cont_ref, 'I',
'GEN', TRIM (c_cust.contact_phone2), 1110,
1110, SYSDATE, SYSDATE,
TRIM (c_cust.contact_name2),
-- C_CUST.CONTACT_EMAIL2,
(c_cust.org_id), 'PHONE'
);
COMMIT;
ELSE
x := x + 1;
INSERT INTO ra_contact_phones_int_all
(orig_system_customer_ref,
--orig_system_address_ref,
-- orig_system_telephone_ref,
orig_system_contact_ref, insert_update_flag,
-- telephone_type,
-- telephone,
last_updated_by, created_by, creation_date,
last_update_date, contact_last_name,
-- email_address,
org_id
--,CONTACT_POINT_TYPE
 )
VALUES (TRIM (c_cust.customer_number),--v_cust_ref,
--v_add_ref,
-- v_tele_ref,
v_cont_ref, 'I',
-- 'GEN',
-- C_CUST.CONTACT_PHONE2,
1110, 1110, SYSDATE,
SYSDATE, TRIM (c_cust.contact_name2),
-- C_CUST.CONTACT_EMAIL2,
(c_cust.org_id)
--,'PHONE'
);
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, '7 : '||c_cust.Customer_Name||'
'||SQLCODE || ' ' || SQLERRM);
END;
UPDATE xx_customer_stg
SET status_code = 'P'
WHERE ROWID = c_cust.ROWID;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'END Code : '||c_cust.Customer_
Name||' '||SQLCODE || ' ' || SQLERRM);
NULL;
END;
END LOOP;
fnd_file.put_line (fnd_file.LOG, 'x= ' || x);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'x= ' || x);
NULL;
END;
END xx_customer_imp_pkg;
/