CREATE OR REPLACE PACKAGE APPS.xxcofi_cust_cov_pkg
AS
PROCEDURE get_cust_home_phone (
p_party_id IN NUMBER
,p_home_phone_country_code OUT VARCHAR2
,p_home_phone_area_code OUT VARCHAR2
,p_home_phone_number OUT VARCHAR2
,p_home_phone_extension OUT VARCHAR2
);
PROCEDURE get_cust_office_phone (
p_party_id IN NUMBER
,p_business_phone_country_code OUT VARCHAR2
,p_business_phone_area_code OUT VARCHAR2
,p_business_phone_number OUT VARCHAR2
,p_business_phone_extension OUT VARCHAR2
);
PROCEDURE get_cust_cell_details (
p_party_id NUMBER
,p_cell_phone_country_code OUT VARCHAR2
,p_cell_phone_area_code OUT VARCHAR2
,p_cell_phone_number OUT VARCHAR2
,p_cell_phone_extension OUT VARCHAR2
);
PROCEDURE get_cust_fax_details (
p_party_id IN NUMBER
,p_fax_phone_country_code OUT VARCHAR2
,p_fax_phone_area_code OUT VARCHAR2
,p_fax_phone_number OUT VARCHAR2
,p_fax_phone_extension OUT VARCHAR2
);
PROCEDURE get_cust_gen_details (
p_party_id IN NUMBER
,p_gen_phone_country_code OUT VARCHAR2
,p_gen_phone_area_code OUT VARCHAR2
,p_gen_phone_number OUT VARCHAR2
);
PROCEDURE get_cust_email_details (
p_party_id IN NUMBER
,p_email_address OUT VARCHAR2
);
/*PROCEDURE get_cust_loc_details (
p_party_id IN NUMBER
,p_city OUT VARCHAR2
,p_province OUT VARCHAR2
,p_country OUT VARCHAR2
,p_postal_code OUT VARCHAR2
,p_address_line_1 OUT VARCHAR2
,p_address_line_2 OUT VARCHAR2
,p_address_line_3 OUT VARCHAR2
,p_address_use_type OUT VARCHAR2
);
*/
PROCEDURE get_cust_vm_details (
p_party_id IN NUMBER
,p_vm_phone_country_code OUT VARCHAR2
,p_vm_phone_area_code OUT VARCHAR2
,p_vm_phone_number OUT VARCHAR2
,p_vm_phone_extension OUT VARCHAR2
);
PROCEDURE get_cust_pager_details (
p_party_id IN NUMBER
,p_pager_country_code OUT VARCHAR2
,p_pager_area_code OUT VARCHAR2
,p_pager_phone_number OUT VARCHAR2
,p_pager_extension OUT VARCHAR2
);
PROCEDURE extract_customer_data (p_batch_size IN NUMBER);
PROCEDURE main (errbuf OUT VARCHAR2, retcode OUT VARCHAR2, p_batch_size IN NUMBER);
END xxcofi_cust_cov_pkg;
/
CREATE OR REPLACE PACKAGE BODY APPS.xxcofi_cust_cov_pkg
AS
-- Procedure to get the customer home phone details
PROCEDURE get_cust_home_phone(
p_party_id IN NUMBER
,p_home_phone_country_code OUT VARCHAR2
,p_home_phone_area_code OUT VARCHAR2
,p_home_phone_number OUT VARCHAR2
,p_home_phone_extension OUT VARCHAR2
)
AS
BEGIN
SELECT phone_country_code
,phone_area_code
,phone_number
,phone_extension
INTO p_home_phone_country_code
,p_home_phone_area_code
,p_home_phone_number
,p_home_phone_extension
FROM hz_contact_points
WHERE owner_table_name(+) = 'HZ_PARTIES'
AND owner_table_id = p_party_id
AND status = 'A'
AND primary_flag = 'Y'
AND contact_point_type = 'PHONE'
AND phone_line_type = 'HOME:';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_home_phone_country_code := NULL;
p_home_phone_area_code := NULL;
p_home_phone_number := NULL;
p_home_phone_extension := NULL;
fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer Home Phone Address for Customer ID:'
p_party_id);
WHEN OTHERS
THEN
p_home_phone_country_code := NULL;
p_home_phone_area_code := NULL;
p_home_phone_number := NULL;
p_home_phone_extension := NULL;
fnd_file.put_line(fnd_file.LOG, '*****'
''
SQLERRM
'*****'
'for'
''
'Customer Home Phone Address ');
END;
-- Procedure to get the customer office phone details
PROCEDURE get_cust_office_phone(
p_party_id IN NUMBER
,p_business_phone_country_code OUT VARCHAR2
,p_business_phone_area_code OUT VARCHAR2
,p_business_phone_number OUT VARCHAR2
,p_business_phone_extension OUT VARCHAR2
)
AS
BEGIN
SELECT phone_country_code
,phone_area_code
,phone_number
,phone_extension
INTO p_business_phone_country_code
,p_business_phone_area_code
,p_business_phone_number
,p_business_phone_extension
FROM hz_contact_points
WHERE owner_table_name(+) = 'HZ_PARTIES'
AND owner_table_id = p_party_id
AND status = 'A'
AND primary_flag = 'Y'
AND contact_point_type = 'PHONE'
AND phone_line_type = 'OFFICE:';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_business_phone_country_code := NULL;
p_business_phone_area_code := NULL;
p_business_phone_number := NULL;
p_business_phone_extension := NULL;
fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer Business Phone Address for Customer ID:'
p_party_id);
WHEN OTHERS
THEN
p_business_phone_country_code := NULL;
p_business_phone_area_code := NULL;
p_business_phone_number := NULL;
p_business_phone_extension := NULL;
fnd_file.put_line(fnd_file.LOG, '*****'
''
SQLERRM
'*****'
'for'
''
'Business Phone Address ');
END;
-- Procedure to get the customer cell phone details
PROCEDURE get_cust_cell_details(
p_party_id NUMBER
,p_cell_phone_country_code OUT VARCHAR2
,p_cell_phone_area_code OUT VARCHAR2
,p_cell_phone_number OUT VARCHAR2
,p_cell_phone_extension OUT VARCHAR2
)
AS
BEGIN
SELECT phone_country_code
,phone_area_code
,phone_number
,phone_extension
INTO p_cell_phone_country_code
,p_cell_phone_area_code
,p_cell_phone_number
,p_cell_phone_extension
FROM hz_contact_points
WHERE owner_table_name(+) = 'HZ_PARTIES'
AND owner_table_id = p_party_id
AND status = 'A'
AND primary_flag = 'Y'
AND contact_point_type = 'PHONE'
AND phone_line_type = 'MOBILE';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_cell_phone_country_code := NULL;
p_cell_phone_area_code := NULL;
p_cell_phone_number := NULL;
p_cell_phone_extension := NULL;
fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer Cell Phone Address for Customer ID:'
p_party_id);
WHEN OTHERS
THEN
p_cell_phone_country_code := NULL;
p_cell_phone_area_code := NULL;
p_cell_phone_number := NULL;
p_cell_phone_extension := NULL;
fnd_file.put_line(fnd_file.LOG, '*****'
''
SQLERRM
'*****'
'for'
''
'Cell Phone Address ');
END;
-- Procedure to get the customer fax details
PROCEDURE get_cust_fax_details(
p_party_id IN NUMBER
,p_fax_phone_country_code OUT VARCHAR2
,p_fax_phone_area_code OUT VARCHAR2
,p_fax_phone_number OUT VARCHAR2
,p_fax_phone_extension OUT VARCHAR2
)
AS
BEGIN
SELECT phone_country_code
,phone_area_code
,phone_number
,phone_extension
INTO p_fax_phone_country_code
,p_fax_phone_area_code
,p_fax_phone_number
,p_fax_phone_extension
FROM hz_contact_points
WHERE owner_table_name(+) = 'HZ_PARTIES'
AND owner_table_id = p_party_id ---party_id from main query
AND status = 'A'
AND primary_flag = 'Y'
AND contact_point_type = 'PHONE'
AND phone_line_type = 'FAX';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_fax_phone_country_code := NULL;
p_fax_phone_area_code := NULL;
p_fax_phone_number := NULL;
p_fax_phone_extension := NULL;
fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer Fax Address for Customer ID:'
p_party_id);
WHEN OTHERS
THEN
p_fax_phone_country_code := NULL;
p_fax_phone_area_code := NULL;
p_fax_phone_number := NULL;
p_fax_phone_extension := NULL;
fnd_file.put_line(fnd_file.LOG, '*****'
''
SQLERRM
'*****'
'for'
''
'Fax Address ');
END;
-- Procedure to get the customer pager details
PROCEDURE get_cust_pager_details(
p_party_id IN NUMBER
,p_pager_country_code OUT VARCHAR2
,p_pager_area_code OUT VARCHAR2
,p_pager_phone_number OUT VARCHAR2
,p_pager_extension OUT VARCHAR2
)
AS
BEGIN
SELECT phone_country_code
,phone_area_code
,phone_number
,phone_extension
INTO p_pager_country_code
,p_pager_area_code
,p_pager_phone_number
,p_pager_extension
FROM hz_contact_points
WHERE owner_table_name(+) = 'HZ_PARTIES'
AND owner_table_id = p_party_id ---party_id from main query
AND status = 'A'
AND primary_flag = 'Y'
AND contact_point_type = 'PHONE'
AND phone_line_type = 'PAGER';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_pager_country_code := NULL;
p_pager_area_code := NULL;
p_pager_phone_number := NULL;
p_pager_extension := NULL;
fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer Pager Address for Customer ID:'
p_party_id);
WHEN OTHERS
THEN
p_pager_country_code := NULL;
p_pager_area_code := NULL;
p_pager_phone_number := NULL;
p_pager_extension := NULL;
fnd_file.put_line(fnd_file.LOG, '*****'
''
SQLERRM
'*****'
'for'
''
'Pager Address ');
END;
-- Procedure to get the customer gen details
PROCEDURE get_cust_gen_details(
p_party_id IN NUMBER
,p_gen_phone_country_code OUT VARCHAR2
,p_gen_phone_area_code OUT VARCHAR2
,p_gen_phone_number OUT VARCHAR2
)
AS
BEGIN
SELECT phone_country_code
,phone_area_code
,phone_number
INTO p_gen_phone_country_code
,p_gen_phone_area_code
,p_gen_phone_number
FROM hz_contact_points
WHERE owner_table_name(+) = 'HZ_PARTIES'
AND owner_table_id = p_party_id
AND status = 'A'
AND primary_flag = 'Y'
AND contact_point_type = 'PHONE'
AND phone_line_type = 'GEN';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_gen_phone_country_code := NULL;
p_gen_phone_area_code := NULL;
p_gen_phone_number := NULL;
fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer General Details for Customer ID:'
p_party_id);
WHEN OTHERS
THEN
p_gen_phone_country_code := NULL;
p_gen_phone_area_code := NULL;
p_gen_phone_number := NULL;
fnd_file.put_line(fnd_file.LOG, '*****'
''
SQLERRM
'*****'
'for'
''
'Customer General Details');
END;
-- Procedure to get the customer email details
PROCEDURE get_cust_email_details(
p_party_id IN NUMBER
,p_email_address OUT VARCHAR2
)
AS
BEGIN
SELECT email_address
INTO p_email_address
FROM hz_contact_points
WHERE owner_table_name(+) = 'HZ_PARTIES' AND owner_table_id = p_party_id AND status = 'A' AND primary_flag = 'Y'
AND contact_point_type = 'EMAIL';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_email_address := NULL;
fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer e-mail Address for Customer ID:'
p_party_id);
WHEN OTHERS
THEN
p_email_address := NULL;
fnd_file.put_line(fnd_file.LOG, '*****'
''
SQLERRM
'*****'
'for'
''
'E-mail Address');
END;
/*
-- Procedure to get the customer locations details
PROCEDURE get_cust_loc_details(
p_party_id IN NUMBER
,p_city OUT VARCHAR2
,p_province OUT VARCHAR2
,p_country OUT VARCHAR2
,p_postal_code OUT VARCHAR2
,p_address_line_1 OUT VARCHAR2
,p_address_line_2 OUT VARCHAR2
,p_address_line_3 OUT VARCHAR2
,p_address_use_type OUT VARCHAR2
)
AS
BEGIN
SELECT ship_loc.city
,ship_loc.province
,ship_loc.country
,ship_loc.postal_code
,ship_loc.address1
,ship_loc.address2
,ship_loc.address3
,DECODE(ship_su.site_use_code, 'BILL_TO', 'Y', 'SHIP_TO', 'N')
INTO p_city
,p_province
,p_country
,p_postal_code
,p_address_line_1
,p_address_line_2
,p_address_line_3
,p_address_use_type
FROM hz_cust_site_uses_all ship_su
,hz_party_sites ship_ps
,hz_locations ship_loc
,hz_cust_acct_sites_all ship_cas
WHERE ship_ps.party_id = p_party_id
AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id
AND ship_cas.party_site_id = ship_ps.party_site_id
AND ship_loc.location_id = ship_ps.location_id
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_city := NULL;
p_province := NULL;
p_country := NULL;
p_postal_code := NULL;
p_address_line_1 := NULL;
p_address_line_2 := NULL;
p_address_line_3 := NULL;
fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer Location Details for Customer ID:'
p_party_id);
WHEN OTHERS
THEN
p_city := NULL;
p_province := NULL;
p_country := NULL;
p_postal_code := NULL;
p_address_line_1 := NULL;
p_address_line_2 := NULL;
p_address_line_3 := NULL;
fnd_file.put_line(fnd_file.LOG, '*****'
''
SQLERRM
'*****'
'for'
''
'Customer Locatation Details');
END;
*/
PROCEDURE get_cust_vm_details(
p_party_id IN NUMBER
,p_vm_phone_country_code OUT VARCHAR2
,p_vm_phone_area_code OUT VARCHAR2
,p_vm_phone_number OUT VARCHAR2
,p_vm_phone_extension OUT VARCHAR2
)
AS
BEGIN
SELECT phone_country_code
,phone_area_code
,phone_number
,phone_extension
INTO p_vm_phone_country_code
,p_vm_phone_area_code
,p_vm_phone_number
,p_vm_phone_extension
FROM hz_contact_points
WHERE owner_table_name(+) = 'HZ_PARTIES'
AND owner_table_id = p_party_id
AND status = 'A'
AND primary_flag = 'Y'
AND contact_point_type = 'PHONE'
AND phone_line_type = 'VM';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_vm_phone_country_code := NULL;
p_vm_phone_area_code := NULL;
p_vm_phone_number := NULL;
p_vm_phone_extension := NULL;
fnd_file.put_line(fnd_file.LOG, 'NO Data Found For Customer VM Details for Customer ID:'
p_party_id);
WHEN OTHERS
THEN
p_vm_phone_country_code := NULL;
p_vm_phone_area_code := NULL;
p_vm_phone_number := NULL;
p_vm_phone_extension := NULL;
fnd_file.put_line(fnd_file.LOG, '*****'
''
SQLERRM
'*****'
'for'
''
'Customer VM Details');
END;
PROCEDURE extract_customer_data(
p_batch_size IN NUMBER
)
AS
CURSOR c1_customer_extract
IS
SELECT
hzp.party_id
,hzp.person_first_name
,hzp.person_middle_name
,hzp.salutation
,hzp.person_last_name
,hzp.person_pre_name_adjunct
,hzp.attribute1 privacy_consent_indicator
,hzp.attribute7 last_update_date
,ship_loc.city
,ship_loc.province
,ship_loc.country
,ship_loc.postal_code
,ship_loc.address1
,ship_loc.address2
,ship_loc.address3
,DECODE(ship_su.site_use_code, 'BILL_TO', 'Y', 'SHIP_TO', 'N') address_use_type
FROM hz_parties hzp
,hz_cust_site_uses_all ship_su
,hz_party_sites ship_ps
,hz_locations ship_loc
,hz_cust_acct_sites_all ship_cas
WHERE hzp.party_id=ship_ps.party_id
AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id
AND ship_cas.party_site_id = ship_ps.party_site_id
AND ship_loc.location_id = ship_ps.location_id
AND ROWNUM <= p_batch_size
AND NOT EXISTS(SELECT 'X' FROM xxcofi_cust_outbound_tbl xxcot
WHERE hzp.party_id = xxcot.customer_id);
v_home_country_code hz_contact_points.phone_country_code%TYPE;
v_home_area_code hz_contact_points.phone_area_code%TYPE;
v_home_phone_number hz_contact_points.phone_number%TYPE;
v_home_phone_extension hz_contact_points.phone_extension%TYPE;
v_business_country_code hz_contact_points.phone_country_code%TYPE;
v_business_area_code hz_contact_points.phone_area_code%TYPE;
v_business_phone_number hz_contact_points.phone_number%TYPE;
v_business_phone_extension hz_contact_points.phone_extension%TYPE;
v_cell_country_code hz_contact_points.phone_country_code%TYPE;
v_cell_area_code hz_contact_points.phone_area_code%TYPE;
v_cell_phone_number hz_contact_points.phone_number%TYPE;
v_cell_phone_extension hz_contact_points.phone_extension%TYPE;
v_fax_country_code hz_contact_points.phone_country_code%TYPE;
v_fax_area_code hz_contact_points.phone_area_code%TYPE;
v_fax_phone_number hz_contact_points.phone_number%TYPE;
v_fax_phone_extension hz_contact_points.phone_extension%TYPE;
v_gen_country_code hz_contact_points.phone_country_code%TYPE;
v_gen_area_code hz_contact_points.phone_area_code%TYPE;
v_gen_phone_number hz_contact_points.phone_number%TYPE;
v_city hz_locations.city%TYPE;
v_province hz_locations.province%TYPE;
v_country hz_locations.country%TYPE;
v_postal_code hz_locations.postal_code%TYPE;
v_address_line_1 hz_locations.address1%TYPE;
v_address_line_2 hz_locations.address2%TYPE;
v_address_line_3 hz_locations.address3%TYPE;
v_email_address hz_contact_points.email_address%TYPE;
v_vm_number VARCHAR2(200) := NULL;
v_gen_number VARCHAR2(200) := NULL;
v_pager_number VARCHAR2(200) := NULL;
v_vm_country_code hz_contact_points.phone_country_code%TYPE;
v_vm_area_code hz_contact_points.phone_area_code%TYPE;
v_vm_phone_number hz_contact_points.phone_number%TYPE;
v_vm_phone_extension hz_contact_points.phone_extension%TYPE;
v_customer_id hz_parties.party_id%TYPE;
v_pager_country_code hz_contact_points.phone_country_code%TYPE;
v_pager_area_code hz_contact_points.phone_area_code%TYPE;
v_pager_phone_number hz_contact_points.phone_number%TYPE;
v_pager_extension hz_contact_points.phone_extension%TYPE;
v_address_use_type VARCHAR2(1) := NULL;
BEGIN
FOR rec_customer_extract IN c1_customer_extract
LOOP
--- GET THE CUSTOMER HOME PHONE DETAILS
v_customer_id := rec_customer_extract.party_id;
DBMS_OUTPUT.put_line('shekhar'
rec_customer_extract.party_id);
BEGIN
get_cust_home_phone(v_customer_id, v_home_country_code, v_home_area_code, v_home_phone_number, v_home_phone_extension);
END;
--- GET THE CUSTOMER BUSINESS PHONE DETAILS
BEGIN
get_cust_office_phone(v_customer_id, v_business_country_code, v_business_area_code, v_business_phone_number, v_business_phone_extension);
END;
--- GET THE CUSTOMER CELL PHONE DETAILS
BEGIN
get_cust_cell_details(v_customer_id, v_cell_country_code, v_cell_area_code, v_cell_phone_number, v_cell_phone_extension);
END;
--- GET THE CUSTOMER FAX DETAILS
BEGIN
get_cust_fax_details(v_customer_id, v_fax_country_code, v_fax_area_code, v_fax_phone_number, v_fax_phone_extension);
END;
BEGIN
--- GET THE CUSTOMER GEN DETAILS
get_cust_gen_details(v_customer_id, v_gen_country_code, v_gen_area_code, v_gen_phone_number);
END;
BEGIN
--- GET THE EMAIL ADDRESS
get_cust_email_details(v_customer_id, v_email_address);
END;
/*BEGIN
--- GET THE LOCATION ADDRESS
get_cust_loc_details(v_customer_id
,v_city
,v_province
,v_country
,v_postal_code
,v_address_line_1
,v_address_line_2
,v_address_line_3
,v_address_use_type
);
END;*/
BEGIN
-- GET CUSTOMER VM DETAILS
get_cust_vm_details(v_customer_id, v_vm_country_code, v_vm_area_code, v_vm_phone_number, v_vm_phone_extension);
END;
BEGIN
get_cust_pager_details(v_customer_id, v_pager_country_code, v_pager_area_code, v_pager_phone_number, v_pager_extension);
END;
v_vm_number :=(v_vm_country_code
v_vm_area_code
v_vm_phone_number
v_vm_phone_extension);
v_gen_number :=(v_gen_country_code
v_gen_area_code
v_gen_phone_number);
v_pager_number :=(v_pager_country_code
v_pager_area_code
v_pager_phone_number
v_pager_extension);
-- v_fax_number := (v_fax_phone_country_code
v_fax_phone_area_code
v_fax_phone_number);
-- v_cell_off_phone_number := (v_cell_phone_country_code
v_cell_phone_area_code
v_cell_phone_number);
BEGIN
INSERT INTO xxcofi_cust_outbound_tbl
(customer_id
,person_first_name
,person_middle_name
,person_last_name
,salutation
,privacy_consent_indicator
,last_update_date
,last_updated_by
,language_flag
,home_country_code
,home_area_code
,home_phone_number
,home_phone_extension
,business_country_code
,business_area_code
,business_phone_number
,business_phone_extension
,cell_country_code
,cell_phone_areacode
,cell_phone_number
,cell_extension
,fax_country_code
,fax_area_code
,fax_number
,fax_extension
,pager_number
,vm_number
,gen_number
,address_line_1
,address_line_2
,city
,province
,country
,postal_code
,address_use_type
,email_address
)
VALUES (rec_customer_extract.party_id
,rec_customer_extract.person_first_name
,rec_customer_extract.person_middle_name
,rec_customer_extract.person_last_name
,rec_customer_extract.salutation
,rec_customer_extract.privacy_consent_indicator
,SYSDATE
,fnd_global.user_id
,'E'
,v_home_country_code
,v_home_area_code
,v_home_phone_number
,v_home_phone_extension
,v_business_country_code
,v_business_area_code
,v_business_phone_number
,v_business_phone_extension
,v_cell_country_code
,v_cell_area_code
,v_cell_phone_number
,v_cell_phone_extension
,v_fax_country_code
,v_fax_area_code
,v_fax_phone_number
,v_fax_phone_extension
,v_pager_number
,v_vm_number
,v_gen_number
,rec_customer_extract.address1
,rec_customer_extract.address2
,rec_customer_extract.city
,rec_customer_extract.province
,rec_customer_extract.country
,rec_customer_extract.postal_code
,rec_customer_extract.address_use_type
,v_email_address
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG, '*****'
''
SQLERRM
'*****'
'for'
''
'Inserting into custom table');
DBMS_OUTPUT.put_line('*****'
''
SQLERRM
'*****'
'for'
''
'Inserting into custom table');
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG, SQLERRM);
END extract_customer_data;
PROCEDURE main(
errbuf OUT VARCHAR2
,retcode OUT VARCHAR2
,p_batch_size IN NUMBER
)
AS
/*****************************************************************************************/
--- Procedure to create customer data extract
--- This procedure populates the custom table
--- and then creates the extract from the custom table
/*****************************************************************************************/
report_name VARCHAR2(70) := 'xxcofi_customer_conversion_program';
v_file_dc UTL_FILE.file_type;
buff_size NUMBER := 24576;
l_record_no NUMBER := 0;
l_top VARCHAR2(200) := NULL;
l_date VARCHAR2(60) := NULL;
l_mode VARCHAR2(5) := NULL;
CURSOR out_cust_data_c1
IS
SELECT customer_id
,person_first_name
,person_middle_name
,person_last_name
,salutation
,privacy_consent_indicator
,last_update_date
,last_updated_by
,language_flag
,home_country_code
,home_area_code
,home_phone_number
,home_phone_extension
,business_country_code
,business_area_code
,business_phone_number
,business_phone_extension
,cell_country_code
,cell_phone_areacode
,cell_phone_number
,cell_extension
,fax_country_code
,fax_area_code
,fax_number
,fax_extension
,pager_number
,vm_number
,gen_number
,address_line_1
,address_line_2
,city
,province
,country
,postal_code
,address_use_type
,email_address
FROM xxcofi_cust_outbound_tbl
WHERE extract_flag IS NULL;
BEGIN
xxcofi_cust_cov_pkg.extract_customer_data(p_batch_size);
---For Populating staging table
--******************************************************************************
-- CREATE OUTPUT FILE IN txt FORMAT
fnd_profile.get('XXCOFIDATA_OUT', l_top);
l_date := TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI');
fnd_file.put_line(fnd_file.LOG, 'START RUNNING '
report_name
'_SS'
' REPORT PROGRAM.');
fnd_file.put_line(fnd_file.output, RPAD('HUDSON''S BAY COMPANY', 50)
'DATE:'
SYSDATE);
fnd_file.put_line(fnd_file.output, RPAD('COFI CUSTOMER DATA EXTRACTION REPORT', 50)
'TIME:'
TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.LOG, 'Outputing '
report_name
'_'
l_date
'.csv FILE TO '
l_top);
v_file_dc := UTL_FILE.fopen(l_top, 'customer.dat', 'W', buff_size);
-- PUT START MESSAGE IN THE LOG FILE
FOR out_cust_data IN out_cust_data_c1
LOOP
l_record_no := l_record_no + 1;
UTL_FILE.put_line(v_file_dc
, RPAD(NVL(TO_CHAR(out_cust_data.customer_id), ' '), 10)
RPAD(NVL(out_cust_data.person_first_name, ' '), 40)
RPAD(NVL(out_cust_data.person_last_name, ' '), 40)
RPAD(NVL(out_cust_data.person_middle_name, ' '), 34)
RPAD(NVL(out_cust_data.salutation, ' '), 4)
RPAD(NVL(out_cust_data.privacy_consent_indicator, ' '), 1)
rpad(to_char(out_cust_data.last_update_date,'DD-MM-YY'),8)
RPAD(NVL(out_cust_data.last_updated_by, ' '), 34)
RPAD(NVL(out_cust_data.language_flag, ' '), 2)
RPAD(NVL(out_cust_data.home_country_code
out_cust_data.home_area_code
out_cust_data.home_phone_number, ' '), 40)
RPAD(NVL(out_cust_data.home_phone_extension, ' '), 20)
RPAD(NVL(out_cust_data.business_country_code
out_cust_data.business_area_code
out_cust_data.business_phone_number
,' '
)
,40
)
RPAD(NVL(out_cust_data.business_phone_extension, ' '), 20)
RPAD(NVL(out_cust_data.cell_country_code
out_cust_data.cell_phone_areacode
out_cust_data.cell_phone_number, ' ')
,40
)
RPAD(NVL(out_cust_data.cell_extension, ' '), 20)
RPAD(NVL(out_cust_data.fax_country_code
out_cust_data.fax_area_code
out_cust_data.fax_number, ' '), 40)
RPAD(NVL(out_cust_data.fax_extension, ' '), 20)
RPAD(NVL(out_cust_data.pager_number, ' '), 40)
RPAD(NVL(out_cust_data.vm_number, ' '), 40)
RPAD(NVL(out_cust_data.gen_number, ' '), 40)
RPAD(NVL(out_cust_data.address_line_1, ' '), 153)
RPAD(NVL(out_cust_data.address_line_2, ' '), 87)
RPAD(NVL(out_cust_data.city, ' '), 35)
RPAD(NVL(out_cust_data.province, ' '), 22)
RPAD(NVL(out_cust_data.country, ' '), 2)
RPAD(NVL(out_cust_data.postal_code, ' '), 12)
RPAD(NVL(out_cust_data.address_use_type, ' '), 10)
RPAD(NVL(out_cust_data.email_address, ' '), 57)
);
fnd_file.put_line(fnd_file.output
, out_cust_data.customer_id
','
out_cust_data.person_first_name
','
out_cust_data.person_middle_name
','
out_cust_data.person_last_name
','
out_cust_data.salutation
','
out_cust_data.privacy_consent_indicator
','
out_cust_data.last_update_date
','
out_cust_data.last_updated_by
','
out_cust_data.language_flag
','
out_cust_data.home_country_code
','
out_cust_data.home_area_code
','
out_cust_data.home_phone_number
','
out_cust_data.home_phone_extension
','
out_cust_data.business_country_code
','
out_cust_data.business_area_code
','
out_cust_data.business_phone_number
','
out_cust_data.business_phone_extension
','
out_cust_data.cell_country_code
','
out_cust_data.cell_phone_areacode
','
out_cust_data.cell_phone_number
','
out_cust_data.cell_extension
','
out_cust_data.fax_country_code
','
out_cust_data.fax_area_code
','
out_cust_data.fax_number
','
out_cust_data.fax_extension
','
out_cust_data.pager_number
','
out_cust_data.vm_number
','
out_cust_data.gen_number
','
out_cust_data.address_line_1
','
out_cust_data.address_line_2
','
out_cust_data.city
','
out_cust_data.province
','
out_cust_data.country
','
out_cust_data.postal_code
','
out_cust_data.address_use_type
','
out_cust_data.email_address
);
BEGIN
UPDATE xxcofi_cust_outbound_tbl
SET extract_flag = 'Y'
WHERE customer_id = out_cust_data.customer_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG, 'Update failed for Customer ID:'
out_cust_data.customer_id);
END;
END LOOP;
COMMIT;
fnd_file.put_line(fnd_file.LOG, 'Number of Records created to customer File'
' : '
l_record_no);
--------------------------------------------------------------------------------
fnd_file.put_line(fnd_file.output, ' ');
--------------------------------------------------------------------------------
-- BEGINNING OF ERROR CUSTOMER CONVERSION REPORT
--------------------------------------------------------------------------------
-- CREATE THE TRAILER RECORD FOR OUTPUT REPORT FILE
UTL_FILE.fflush(v_file_dc);
UTL_FILE.fclose(v_file_dc);
--******************************************************************************
errbuf := report_name
' REPORT FILE JOB COMPLETED SUCCESSFULLY';
retcode := '0';
-- Output Number Of Record Output
fnd_file.put_line(fnd_file.LOG, '------------------*********************************************----------------+');
fnd_file.put_line(fnd_file.LOG, 'customer extraction Report ');
fnd_file.put_line(fnd_file.LOG, ' ');
fnd_file.put_line(fnd_file.LOG, 'JOB COMPLETED SUCCESSFULLY');
-- FND_FILE.CLOSE;
EXCEPTION
WHEN UTL_FILE.invalid_filehandle
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'INVALID FILE HANDLE';
retcode := '1';
fnd_file.put_line(fnd_file.LOG, SQLCODE
' AND ERROR is INVALID FILE HANDLE : '
SQLERRM);
fnd_file.CLOSE;
WHEN UTL_FILE.invalid_path
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'INVALID PATH';
retcode := '2';
fnd_file.put_line(fnd_file.LOG, SQLCODE
' AND ERROR is INVALID PATH : '
SQLERRM);
fnd_file.CLOSE;
WHEN UTL_FILE.invalid_mode
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'INVALID MODE';
retcode := '3';
fnd_file.put_line(fnd_file.LOG, SQLCODE
' AND ERROR is INVALID MODE : '
SQLERRM);
fnd_file.CLOSE;
WHEN UTL_FILE.invalid_operation
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'INVALID OPERATION';
retcode := '4';
fnd_file.put_line(fnd_file.LOG, SQLCODE
' AND ERROR is INVALID OPERATION : '
SQLERRM);
fnd_file.CLOSE;
WHEN UTL_FILE.read_error
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'READ ERROR';
retcode := '5';
fnd_file.put_line(fnd_file.LOG, SQLCODE
' AND ERROR is READ ERROR : '
SQLERRM);
fnd_file.CLOSE;
WHEN UTL_FILE.write_error
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'WRITE ERROR';
retcode := '6';
fnd_file.put_line(fnd_file.LOG, SQLCODE
' AND ERROR is WRITE ERROR : '
SQLERRM);
fnd_file.CLOSE;
WHEN UTL_FILE.internal_error
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'INTERNAL ERROR';
retcode := '7';
fnd_file.put_line(fnd_file.LOG, SQLCODE
' AND ERROR is INTERNAL ERROR : '
SQLERRM);
fnd_file.CLOSE;
WHEN NO_DATA_FOUND
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'NO DATA FOUND';
retcode := '8';
fnd_file.put_line(fnd_file.LOG, SQLCODE
' Error is NO DATA FOUND :'
SQLERRM);
fnd_file.CLOSE;
WHEN OTHERS
THEN
UTL_FILE.fclose(v_file_dc);
errbuf := 'OTHERS ';
retcode := '9';
fnd_file.put_line(fnd_file.LOG, SQLCODE
' Error is OTHERS : '
SQLERRM);
fnd_file.CLOSE;
END main;
END xxcofi_cust_cov_pkg;
/
select line,text from user_errors where upper(name) like'XXCOFI_CUST_COV_PKG'
No comments:
Post a Comment