create or replace PACKAGE BODY XX_Ar_Collector_Assign_Pkg AS
PROCEDURE update_collectors(errbuf OUT VARCHAR2, retcode OUT NUMBER, p_oracle_user_id VARCHAR2) IS
l_oracle_user_id NUMBER;
l_update_rowcount NUMBER;
v_customer_profile_rec_type hz_customer_profile_v2pub.customer_profile_rec_type;
v_latest_ver_num NUMBER;
x_return_status VARCHAR2 (10);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
l_cust_account_profile_id number;
l_site_use_id number;
l_customer_name varchar2(500);
l_customer_number varchar2(100);
l_collector_name varchar2(500);
l_party_site_number varchar2(100);
l_address1 varchar2(500);
l_city varchar2(500);
CURSOR output_cur IS
SELECT *
FROM xx_ar_coll_assignments_rep;
BEGIN
l_oracle_user_id := TO_NUMBER(p_oracle_user_id);
Fnd_File.Put_Line(Fnd_File.LOG,’Purging empty rows…’);
DELETE FROM xx_ar_coll_assignments
WHERE account_number IS NULL
AND collector_name IS NULL;
Fnd_File.Put_Line(Fnd_File.LOG,TO_CHAR(SQL%ROWCOUNT) || ‘ empty rows purged.’ );
COMMIT;
Fnd_File.Put_Line(Fnd_File.LOG,’Validating records in the staging (AR_COLL_ASSIGNMENTS) table…’);
UPDATE xx_ar_coll_assignments ca
SET cust_account_id = (SELECT cust_account_id
FROM hz_cust_accounts_all
WHERE account_number = ca.account_number),
collector_id = (SELECT collector_id
FROM ar_collectors
WHERE NAME = ca.collector_name),
created_by = l_oracle_user_id,
creation_date = SYSDATE,
last_update_date = SYSDATE,
last_updated_by = l_oracle_user_id;
Fnd_File.Put_Line(Fnd_File.LOG,’Validated ‘ || TO_CHAR(SQL%ROWCOUNT) || ‘ records in the staging table.’);
Fnd_File.Put_Line(Fnd_File.LOG,’ ‘);
COMMIT;
Fnd_File.Put_Line(Fnd_File.LOG,’Updating Collector Assignment for Customer Accounts (HZ_CUSTOMER_PROFILES)…’);
l_update_rowcount := 0;
FOR rec_coll_update in (SELECT iaca.cust_account_id,iaca.collector_id,
iaca.COLLECTOR_PROFILE, iaca.COLLECTOR_SCRIPT,
iaca.COLLECTOR_GROUP
FROM xx_ar_coll_assignments iaca
where iaca.collector_id IS NOT NULL ) LOOP
for rec_site_update in (select hcp.cust_account_profile_id,
hcp.site_use_id
from hz_customer_profiles hcp
where cust_Account_id=rec_coll_update.cust_account_id
and status=’A’) LOOP
BEGIN
l_update_rowcount := l_update_rowcount +1 ;
v_customer_profile_rec_type.cust_account_profile_id := rec_site_update.cust_account_profile_id;
v_customer_profile_rec_type.cust_account_id := rec_coll_update.cust_account_id;
v_customer_profile_rec_type.collector_id := rec_coll_update.collector_id;
v_customer_profile_rec_type.site_use_id := rec_site_update.site_use_id;
v_customer_profile_rec_type.attribute_category := ‘Collection Reporting’;
v_customer_profile_rec_type.attribute1 := rec_coll_update. COLLECTOR_PROFILE;
v_customer_profile_rec_type.attribute2 := rec_coll_update.COLLECTOR_SCRIPT;
v_customer_profile_rec_type.attribute3 := rec_coll_update.COLLECTOR_GROUP;
BEGIN
SELECT object_version_number
INTO v_latest_ver_num
FROM hz_customer_profiles
WHERE cust_account_profile_id =
v_customer_profile_rec_type.cust_account_profile_id;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
BEGIN
hz_customer_profile_v2pub.update_customer_profile
(p_customer_profile_rec => v_customer_profile_rec_type,
p_object_version_number => v_latest_ver_num,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF x_return_status = ‘S’
THEN
fnd_file.put_line
(fnd_file.LOG,
‘Successfully updated customer profile for Customer Account ID ‘
||rec_coll_update.cust_account_id
);
fnd_file.put_line
(fnd_file.output,
‘Successfully updated customer profile for Customer Account ID ‘
|| rec_coll_update.cust_account_id
);
update xx_ar_coll_assignments
set status=’SUCCESS’
where cust_Account_id= rec_coll_update.cust_account_id;
COMMIT;
ELSE
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
fnd_file.put_line
(fnd_file.LOG,i|| ‘.’|| SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,255));
END LOOP;
update xx_ar_coll_assignments
set status=’ERROR’
where cust_Account_id= rec_coll_update.cust_account_id;
ELSE
fnd_file.put_line (fnd_file.LOG,’x_msg_data = ‘|| SUBSTR (x_msg_data, 1, 255));
ROLLBACK;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, ‘Error: ‘ || SQLERRM);
update xx_ar_coll_assignments
set status=’ERROR’
where cust_Account_id= rec_coll_update.cust_account_id;
END;
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line
(fnd_file.log,’Unexpected Error in updating customer profile for Customer Account ID ‘
|| rec_coll_update.cust_account_id || ‘ – Error : ‘||SQLERRM);
update xx_ar_coll_assignments
set status=’ERROR’
where cust_Account_id= rec_coll_update.cust_account_id;
END;
begin
SELECT SUBSTR(hp.party_name,1,25) customer_name,
SUBSTR(hca.account_number,1,20) customer_number,
SUBSTR(iaca.collector_NAME,1,15) collector_name,
SUBSTR(hps.party_site_number,1,15) party_site_number,
SUBSTR(loc.address1,1,30) address1,
SUBSTR(loc.city,1,20) city
into l_customer_name,
l_customer_number,
l_collector_name,
l_party_site_number,
l_address1,
l_city
from xx_ar_coll_assignments iaca,
hz_cust_accounts_all hca,
hz_parties hp,
hz_party_sites hps,
hz_locations loc,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua
where iaca.cust_Account_id=hca.cust_account_id
and hcasa.cust_acct_site_id=hcsua.cust_acct_site_id
and hcasa.cust_Account_id=hca.cust_Account_id
and hcsua.site_use_id=rec_site_update.site_use_id
and hps.party_site_id=hcasa.party_site_id
and hca.party_id=hp.party_id
and hca.cust_Account_id=rec_Coll_update.cust_Account_id
and loc.location_id=hps.location_id
and iaca.status =’SUCCESS’;
exception when others then
NULL;
end;
insert into xx_ar_coll_assignments_rep
(customer_name,
customer_number,
collector_name ,
party_site_number,
address1,
city)
values(
l_customer_name,
l_customer_number,
l_collector_name ,
l_party_site_number,
l_address1,
l_city
);
END LOOP;
END LOOP;
–write records updated to output file
Fnd_File.Put_Line(Fnd_File.LOG,’Write data that was updated to output file…’ );
Fnd_File.put_line(Fnd_File.output, ‘RECORDS UPDATED:’);
IF NVL(l_update_rowcount,0) > 0 THEN — At least one profile update.
Fnd_File.put_line(Fnd_File.output, ‘——————————————————————‘);
FOR rec IN output_cur LOOP
Fnd_File.put_line(Fnd_File.output, rec.customer_name||’,’||rec.customer_number||’,’||rec.collector_name||’,’||
rec.party_site_number||’,’||rec.address1||’,’||rec.city);
END LOOP;
END IF;
— filter the records updated
Fnd_File.Put_Line(Fnd_File.LOG,’Purging Collector Assignments that were updated successfully…’ );
DELETE FROM xx_ar_coll_assignments ca
WHERE status=’SUCCESS’;
Fnd_File.Put_Line(Fnd_File.LOG,TO_CHAR(SQL%ROWCOUNT) || ‘ Collector Assignments were purged successfully.’);
COMMIT;
— report the exceptions
Fnd_File.Put_Line(Fnd_File.LOG, ‘ ‘);
Fnd_File.Put_Line(Fnd_File.LOG, ‘~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~’ );
Fnd_File.Put_Line(Fnd_File.LOG, ‘ ‘);
Fnd_File.Put_Line(Fnd_File.LOG, ‘EXCEPTIONS (list of Accounts not processed)’ );
Fnd_File.Put_Line(Fnd_File.LOG, ‘ ‘);
Fnd_File.Put_Line(Fnd_File.LOG, ‘ ————— ————–‘ );
Fnd_File.Put_Line(Fnd_File.LOG, ‘ Cust Account No Collector Name’ );
Fnd_File.Put_Line(Fnd_File.LOG, ‘ ————— ————–‘ );
FOR collRec IN (SELECT account_number, collector_name FROM xx_ar_coll_assignments )
LOOP
Fnd_File.Put_Line(Fnd_File.LOG, ‘ ‘ || RPAD(collRec.account_number,15,’ ‘) || ‘ ‘ ||RPAD(collRec.collector_name,14,’ ‘) );
END LOOP;
Fnd_File.Put_Line(Fnd_File.LOG, ‘ ‘);
Fnd_File.Put_Line(Fnd_File.LOG, ‘~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~’ );
Fnd_File.Put_Line(Fnd_File.LOG, ‘NOTE: Please fix these records and re-process them for Collector Assignments.’ );
Fnd_File.Put_Line(Fnd_File.LOG, ‘~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~’ );
Fnd_File.Put_Line(Fnd_File.LOG, ‘ ‘);
Fnd_File.Put_Line(Fnd_File.LOG, ‘Purging exception records (IRON_AR_COLL_ASSIGNMENTS)…’ );
EXECUTE IMMEDIATE ‘truncate table xx_ar_coll_assignments’;
Fnd_File.Put_Line(Fnd_File.LOG, ‘Purged exception records.’ );
Fnd_File.Put_Line(Fnd_File.LOG, ‘ ‘);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
EXECUTE IMMEDIATE ‘truncate table xx_ar_coll_assignments’;
Fnd_File.Put_Line(Fnd_File.LOG,’Error: ‘ || SUBSTR(SQLERRM, 1, 175) );
END update_collectors;
END xx_Ar_Collector_Assign_Pkg;
