/*Upload the customer data that needs to be updated in a temp table*/
CREATE TABLE scratch.RITM0669580
(account_number VARCHAR2(30),
tax_payer_id VARCHAR2(20),
tax_reg_num VARCHAR2(50),
status VARCHAR2(1),
message VARCHAR2(1000));
/
set serveroutput on;
/
–DOC ID 2171256.1–
DECLARE
l_account_number VARCHAR2(30);
l_init_msg_list VARCHAR2 (200);
l_organization_rec hz_party_v2pub.organization_rec_type;
l_party_rec hz_party_v2pub.party_rec_type;
l_party_object_version_number NUMBER;
x_profile_id NUMBER;
l_error_message VARCHAR2 (2000);
l_msg_index_out NUMBER;
x_return_status VARCHAR2 (200);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (200);
l_errm VARCHAR2(250);
cursor c1 is
select temp.account_number, ltrim(rtrim(temp.tax_payer_id)) tax_payer_id, ltrim(rtrim(temp.tax_reg_num)) tax_reg_num, hp.party_id, hp.object_version_number, hca.cust_account_id
from scratch.RITM0669580 temp,
apps.hz_cust_accounts hca,
apps.hz_parties hp
where temp.account_number = hca.account_number
and hca.party_id = hp.party_id
and (nvl(temp.tax_payer_id,-1) != nvl(hp.jgzz_fiscal_code,-1) or nvl(temp.tax_reg_num,-1) != nvl(hp.tax_reference,-1));
BEGIN
for c1_rec in c1 loop
update scratch.RITM0669580 set status = ‘P’ where account_number = c1_rec.account_number;
l_errm := NULL;
l_error_message := NULL;
l_account_number := c1_rec.account_number;
l_init_msg_list := 1.0;
l_party_rec.party_id := c1_rec.party_id;
l_organization_rec.party_rec := l_party_rec;
l_organization_rec.tax_reference := c1_rec.tax_reg_num;
l_organization_rec.jgzz_fiscal_code:= c1_rec.tax_payer_id;
x_profile_id := NULL;
x_return_status := NULL;
x_msg_count := NULL;
x_msg_data := NULL;
SELECT object_version_number
INTO l_party_object_version_number
FROM apps.hz_parties
WHERE party_id = l_party_rec.party_id;
apps.hz_party_v2pub.update_organization (
p_init_msg_list => fnd_api.g_true,
p_organization_rec => l_organization_rec,
p_party_object_version_number => l_party_object_version_number,
x_profile_id => x_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
IF x_return_status <> fnd_api.g_ret_sts_success
THEN
FOR i IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get(p_msg_index => i, p_encoded => fnd_api.g_false, p_data => x_msg_data
, p_msg_index_out => l_msg_index_out);
IF l_error_message IS NULL
THEN
l_error_message := SUBSTR(x_msg_data, 1, 250);
ELSE
l_error_message := l_error_message || ‘/’ || SUBSTR(x_msg_data, 1, 250);
END IF;
END LOOP;
DBMS_OUTPUT.put_line(‘API Error : ‘ || l_error_message || ‘ ‘ ||c1_rec.account_number);
ROLLBACK;
update scratch.RITM0669580 set status = ‘E’, message = l_error_message where account_number = c1_rec.account_number;
COMMIT;
ELSE
–DBMS_OUTPUT.put_line(‘Party ID: ‘ || l_party_rec.party_id || ‘ Updated Successfully ‘);
update scratch.RITM0669580 set status = ‘S’, error_message = NULL where account_number = c1_rec.account_number;
COMMIT;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_errm := SUBSTR(SQLERRM, 1, 250);
DBMS_OUTPUT.put_line(‘Unexpected Error ‘ || l_errm || ‘ ‘ || l_account_number);
ROLLBACK;
update scratch.RITM0669580 set status = ‘E’, message = ‘Unexpected Error ‘ || l_errm where account_number = l_account_number;
COMMIT;
END;
/
