PLSQL Programs

 

1. AR Invoice creation
2. Supplier creation
3. Customer creation
4. HRMS API’s for data anonymization/ masking/ scrambling

 


1. AR Invoice creation

DECLARE
 l_return_status VARCHAR2(1);
 l_msg_count NUMBER;
 l_msg_data VARCHAR2(2000);
 l_batch_id NUMBER;
 l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
 l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
 l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
 l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
 l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
 l_trx_contingencies_tbl ar_invoice_api_pub.trx_contingencies_tbl_type;
 v_line_no NUMBER := 0;
 v_line_dist_no NUMBER := 0;
 
CURSOR cbatch IS SELECT customer_trx_id FROM ra_customer_trx_all
 WHERE batch_id = l_batch_id;
 
CURSOR cvalidtxn IS SELECT trx_header_id FROM ar_trx_header_gt
 WHERE trx_header_id NOT IN ( SELECT trx_header_id FROM ar_trx_errors_gt);

BEGIN
--Set the applications context. Initialize the session before calling any public or private APIs. This is recommended practice
 fnd_global.apps_initialize(
 user_id => fnd_profile.value('USER_ID')
 ,resp_id => fnd_profile.value('RESP_ID')
 ,resp_appl_id => fnd_profile.value('RESP_APPL_ID')
 );
 mo_global.init('AR');
 mo_global.set_policy_context('S',fnd_profile.value('ORG_ID'));
 fnd_client_info.set_org_context(fnd_profile.value('ORG_ID'));

--Populate batch source information
 l_batch_source_rec.batch_source_id := 5027;

--Populate header information
 l_trx_header_tbl(1).trx_header_id := 2764315;
 l_trx_header_tbl(1).bill_to_customer_id := 2651;
 l_trx_header_tbl(1).ship_to_customer_id := 1035;
 l_trx_header_tbl(1).cust_trx_type_id := 1026;
 l_trx_header_tbl(1).trx_date := SYSDATE;
 l_trx_header_tbl(1).term_id := 1000;
 l_trx_header_tbl(1).primary_salesrep_id :=-3;
 l_trx_header_tbl(1).trx_currency := 'GBP';
 l_trx_header_tbl(1).exchange_rate_type := NULL;
 l_trx_header_tbl(1).exchange_date := NULL;
 l_trx_header_tbl(1).exchange_rate := NULL;
 l_trx_header_tbl(1).attribute5 := 'ABC123';
 l_trx_header_tbl(1).purchase_order := 'DEF456';
 l_trx_header_tbl(1).purchase_order_date := SYSDATE - 5;
 l_trx_header_tbl(1).trx_class := 'INV';

--Populate line information
 v_line_no := v_line_no + 1;--Fetch line records and LOOP them
 l_trx_lines_tbl(v_line_no).trx_header_id := 2764315;
 l_trx_lines_tbl(v_line_no).trx_line_id := 3003327;
 l_trx_lines_tbl(v_line_no).line_number := 1;
 l_trx_lines_tbl(v_line_no).quantity_invoiced := 10;
 l_trx_lines_tbl(v_line_no).unit_selling_price := 100;
 l_trx_lines_tbl(v_line_no).line_type := 'LINE';
 l_trx_lines_tbl(v_line_no).description := 'Goods1@STANDARD Rate';
 l_trx_lines_tbl(v_line_no).tax_classification_code := 'MIXED'; --'ZERO RATED' or 'MIXED' or 'STANDARD'

--Populate distribution information
 v_line_dist_no := v_line_dist_no + 1; --Fetch line records and LOOP them
 l_trx_dist_tbl(v_line_dist_no).trx_dist_id := 3382546;
 l_trx_dist_tbl(v_line_dist_no).trx_line_id := 3003327;
 l_trx_dist_tbl(v_line_dist_no).trx_header_id := 2764315;
 l_trx_dist_tbl(v_line_dist_no).account_class := 'REV';
 l_trx_dist_tbl(v_line_dist_no).percent := 100;
 l_trx_dist_tbl(v_line_dist_no).code_combination_id := 2173791;
 l_trx_dist_tbl(v_line_dist_no).amount := 1000;

--Populate line tax information
 v_line_no := v_line_no + 1;
 l_trx_lines_tbl(v_line_no).taxable_flag := 'N';
 l_trx_lines_tbl(v_line_no).trx_header_id := 2764315;
 l_trx_lines_tbl(v_line_no).trx_line_id := 3003328;
 l_trx_lines_tbl(v_line_no).link_to_trx_line_id := 3003327;
 l_trx_lines_tbl(v_line_no).line_number := 1;
 l_trx_lines_tbl(v_line_no).line_type := 'TAX';
 l_trx_lines_tbl(v_line_no).tax_regime_code := 'GB VAT';
 l_trx_lines_tbl(v_line_no).tax := 'GB VAT';
 l_trx_lines_tbl(v_line_no).tax_jurisdiction_code := 'GB VAT UK';
 l_trx_lines_tbl(v_line_no).tax_status_code := 'MIXED'; --'ZERO RATED' or 'MIXED' or 'STANDARD'
 l_trx_lines_tbl(v_line_no).tax_rate_code := 'MIXED'; --'ZERO RATED' or 'MIXED' or 'STANDARD'
 l_trx_lines_tbl(v_line_no).tax_rate := 20;
 l_trx_lines_tbl(v_line_no).amount := 200;
 
--Call the invoice api to create multiple invoices in a batch
 ar_invoice_api_pub.create_invoice(
 p_api_version => 1.0
 ,p_batch_source_rec => l_batch_source_rec
 ,p_trx_header_tbl => l_trx_header_tbl
 ,p_trx_lines_tbl => l_trx_lines_tbl
 ,p_trx_dist_tbl => l_trx_dist_tbl
 ,p_trx_salescredits_tbl => l_trx_salescredits_tbl
 ,p_trx_contingencies_tbl => l_trx_contingencies_tbl
 ,x_return_status => l_return_status
 ,x_msg_count => l_msg_count
 ,x_msg_data => l_msg_data
 );
 IF
 l_return_status = fnd_api.g_ret_sts_error OR l_return_status = fnd_api.g_ret_sts_unexp_error
 THEN
 dbms_output.put_line('unexpected errors found!');
 ELSE
 FOR cvalidtxnrec IN cvalidtxn LOOP
 IF
 ( ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL )
 THEN
 dbms_output.put_line('Invoice(s) suceessfully created!');
 dbms_output.put_line('Batch ID: '|| ar_invoice_api_pub.g_api_outputs.batch_id);
 l_batch_id := ar_invoice_api_pub.g_api_outputs.batch_id;
 FOR cbatchrec IN cbatch LOOP
 dbms_output.put_line('Cust Trx Id '|| cbatchrec.customer_trx_id);
 END LOOP;
 ELSE
 dbms_output.put_line('Errors found!');
 END IF;
 END LOOP;
 END IF;
 COMMIT;
END;

2. Supplier creation

CREATE OR REPLACE PACKAGE BODY xxab_supplier_conversion AS
/*************************************************************************
* TYPE : Package Body *
* NAME : xxab_supplier_conversion *
************************************************************************/
------------------------------
-- Global Variable Declaration
------------------------------
 g_return_status VARCHAR2(2000);
 g_msg_count NUMBER;
 g_msg_data VARCHAR2(2000);
 g_msg_index_out NUMBER;
------------------------------
-- * Procedure Name : xxab_create_vendor
-- * Description : This main Procedure creates the Supplier
-- * and call procedures internally to
-- * create Sites, Contacts and Tax details
------------------------------
 PROCEDURE xxab_create_vendor (
 p_errbuf OUT NOCOPY VARCHAR2
 ,p_retcode OUT NOCOPY VARCHAR2
--p_unit IN VARCHAR2
 ) IS
 l_vendor_rec apps.ap_vendor_pub_pkg.r_vendor_rec_type;
 x_vendor_id NUMBER;
 x_party_id NUMBER;
 x_party_site_id NUMBER;
 lv_vendor_number VARCHAR2(100);
 lv_vendor_name VARCHAR2(1000);
 lv_registration_number VARCHAR2(50);
 l_rowcount NUMBER;
 v_resp_name VARCHAR2(50);
 p_unit VARCHAR2(100);
 
CURSOR cur_stg_tab (
 p_unit VARCHAR2
 ) IS SELECT DISTINCT vendor_number
 ,vendor_name
 ,company_registration_number
 ,primary_category
 ,hold_from_payment
 ,organization_type
 ,payment_hold_reason
 ,invoice_currency_code
 ,payment_currency_code
 ,payment_priority
 ,inspection_required_flag
 ,receipt_required_flag
 ,qty_rcv_tolerance
 ,qty_rcv_exception_code
 ,enforce_ship_to_location_code
 ,days_early_receipt_allowed
 ,days_late_receipt_allowed
 ,receipt_days_exception_code
 ,receiving_routing_id
 ,terms_name
 ,vat_registration_num
 FROM xxab_supp_stg_tab
 WHERE unit = p_unit
 ORDER BY vendor_number;
 l_vendors cur_stg_tab%rowtype;

 BEGIN
 v_resp_name := fnd_profile.value('RESP_NAME');
 fnd_global.resp_appl_id;

 IF
 v_resp_name = 'ABC Payables Supplier Setup'
 THEN
 p_unit := ( 'USABC, USDEF' );
 ELSIF v_resp_name = 'Payables AB Supplier Setup' THEN
 p_unit := 'USXYZ';
 ELSE
 p_retcode := 2;
 apps.fnd_file.put_line(
 apps.fnd_file.log,'Please select valid responsibility to execute Supplier conversion');
 END IF;
 FOR cur_unit IN ( SELECT TRIM(regexp_substr(
 upper(p_unit)
 ,'[^,]+'
 ,1
 ,level
 ) ) unit
 FROM dual CONNECT BY
 regexp_substr(
 upper(p_unit)
 ,'[^,]+'
 ,1
 ,level
 ) IS NOT NULL
 ) LOOP

 OPEN cur_stg_tab(cur_unit.unit);
 LOOP
 l_rowcount := cur_stg_tab%rowcount;
 FETCH cur_stg_tab INTO l_vendors;
 EXIT WHEN cur_stg_tab%notfound;
 fnd_file.put_line(fnd_file.log,'————————————————————');
 apps.fnd_file.put_line(apps.fnd_file.log,'Fetching data for Vendor: '|| l_vendors.vendor_number);
 lv_vendor_number := NULL;
 lv_vendor_name := NULL;
 lv_registration_number := NULL;
 
BEGIN
 SELECT segment1 INTO lv_vendor_number FROM ap_suppliers
 WHERE segment1 = TO_CHAR(l_vendors.vendor_number);
 EXCEPTION
 WHEN no_data_found THEN
 lv_vendor_number := 'A';
 WHEN OTHERS THEN
 lv_vendor_number := 'A';
 apps.fnd_file.put_line(apps.fnd_file.log,sqlerrm);
 END;
 BEGIN
 SELECT vendor_name INTO lv_vendor_name FROM ap_suppliers
 WHERE vendor_name = TO_CHAR(l_vendors.vendor_name);
 EXCEPTION
 WHEN no_data_found THEN
 lv_vendor_name := 'A';
 WHEN OTHERS THEN
 lv_vendor_name := 'A';
 apps.fnd_file.put_line(apps.fnd_file.log,sqlerrm);
 END;
 BEGIN
 SELECT rep_registration_number INTO lv_registration_number FROM zx_party_tax_profile
 WHERE rep_registration_number = l_vendors.vat_registration_num
 AND ROWNUM = 1;
 EXCEPTION
 WHEN no_data_found THEN
 lv_registration_number := 'A';
 WHEN OTHERS THEN
 lv_registration_number := 'A';
 apps.fnd_file.put_line(apps.fnd_file.log,sqlerrm);
 END;

 IF
 nvl(lv_vendor_number,'A') <> l_vendors.vendor_number AND nvl(lv_registration_number,'A') <> nvl(l_vendors.vat_registration_num,'B') 
AND nvl(lv_vendor_name,'A') <> l_vendors.vendor_name
 THEN
 l_vendor_rec.vendor_id := NULL;
 l_vendor_rec.segment1 := l_vendors.vendor_number;
 l_vendor_rec.vendor_name := l_vendors.vendor_name;
 l_vendor_rec.attribute2 := l_vendors.company_registration_number;
 l_vendor_rec.attribute3 := l_vendors.primary_category;
 l_vendor_rec.hold_unmatched_invoices_flag := l_vendors.hold_from_payment;
 l_vendor_rec.vendor_type_lookup_code := l_vendors.organization_type;
 l_vendor_rec.hold_reason := l_vendors.payment_hold_reason;
 l_vendor_rec.invoice_currency_code := l_vendors.invoice_currency_code;
 l_vendor_rec.payment_currency_code := l_vendors.payment_currency_code;
 l_vendor_rec.payment_priority := l_vendors.payment_priority;
 l_vendor_rec.inspection_required_flag := l_vendors.inspection_required_flag;
 l_vendor_rec.receipt_required_flag := l_vendors.receipt_required_flag;
 l_vendor_rec.qty_rcv_tolerance := l_vendors.qty_rcv_tolerance;
 l_vendor_rec.qty_rcv_exception_code := l_vendors.qty_rcv_exception_code;
 l_vendor_rec.enforce_ship_to_location_code := l_vendors.enforce_ship_to_location_code;
 l_vendor_rec.days_early_receipt_allowed := l_vendors.days_early_receipt_allowed;
 l_vendor_rec.days_late_receipt_allowed := l_vendors.days_late_receipt_allowed;
 l_vendor_rec.receipt_days_exception_code := l_vendors.receipt_days_exception_code;
 l_vendor_rec.receiving_routing_id := l_vendors.receiving_routing_id;
 l_vendor_rec.terms_name := l_vendors.terms_name;
 l_vendor_rec.ext_payee_rec.default_pmt_method := 'EFT';

-- Call the API
 ap_vendor_pub_pkg.create_vendor(
 p_api_version => 1.0
 ,p_init_msg_list => fnd_api.g_true
 ,p_commit => fnd_api.g_true
 ,p_validation_level => fnd_api.g_valid_level_full
 ,x_return_status => g_return_status
 ,x_msg_count => g_msg_count
 ,x_msg_data => g_msg_data
 ,p_vendor_rec => l_vendor_rec
 ,x_vendor_id => x_vendor_id
 ,x_party_id => x_party_id
 );

 IF ( g_return_status <> fnd_api.g_ret_sts_success )
 THEN
 FOR i IN 1..fnd_msg_pub.count_msg LOOP
 fnd_msg_pub.get(
 p_msg_index => i
 ,p_encoded => 'F'
 ,p_data => g_msg_data
 ,p_msg_index_out => g_msg_index_out
 );
 g_msg_data := substr(g_msg_data,1,200);
 apps.fnd_file.put_line(apps.fnd_file.log,'Error at Vendor creation :'|| g_msg_data);
 END LOOP;
 ELSE
 apps.fnd_file.put_line(apps.fnd_file.log,'Vendor: '|| l_vendors.vendor_number|| ' created');
 xxab_create_vendor_site(x_vendor_id);
 xxab_create_vendor_contact(x_vendor_id,x_party_site_id);
 xxab_create_vendor_tax(x_party_id);
 xxab_update_vendor(x_vendor_id);
 xxab_bank_account_creation(x_vendor_id);
 xxab_bank_account_site_assg(x_vendor_id);
 COMMIT;
 END IF;
 ELSE
 apps.fnd_file.put_line(apps.fnd_file.log,'Vendor Name/ Number or Tax registration number already exists for :'|| l_vendors.vendor_number);
 p_retcode := 1;
 END IF;
 COMMIT;
 END LOOP;

 apps.fnd_file.put_line(
 apps.fnd_file.log
 ,'Total number of records fetched for :'
 || cur_unit.unit
 || ' are '
 || l_rowcount
 );
 apps.fnd_file.put_line(
 apps.fnd_file.log
 ,'*************************************************************'
 );
 IF
 l_rowcount = 0
 THEN
 p_retcode := 1;
 apps.fnd_file.put_line(
 apps.fnd_file.log
 ,'Please check the data file..!'
 );
 END IF;
 CLOSE cur_stg_tab;
 END LOOP;
 END xxab_create_vendor;
------------------------------
-- * Procedure Name : xxab_create_vendor_site
-- * Description : This Procedure creates the Supplier sites
------------------------------
 PROCEDURE xxab_create_vendor_site (
 p_vendor_id IN NUMBER
 ) IS
 l_vendor_site_rec apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;
 l_vendor_cont_rec apps.ap_vendor_pub_pkg.r_vendor_contact_rec_type;
 p_party_site_use_rec hz_party_site_v2pub.party_site_use_rec_type;
 p_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
 p_edi_rec hz_contact_point_v2pub.edi_rec_type;
 p_email_rec hz_contact_point_v2pub.email_rec_type;
 p_phone_rec hz_contact_point_v2pub.phone_rec_type;
 p_telex_rec hz_contact_point_v2pub.telex_rec_type;
 p_web_rec hz_contact_point_v2pub.web_rec_type;
 x_contact_point_id NUMBER;
 x_party_site_use_id NUMBER;
 x_vendor_site_id NUMBER;
 x_party_site_id NUMBER;
 x_location_id NUMBER;
 lv_supplier_number VARCHAR2(100);
 lv_supplier_name VARCHAR2(1000);
 ln_count NUMBER;
 l_vendor_site_org_id VARCHAR2(50);
 lv_user_id NUMBER;
 lv_responsibility_id NUMBER;
 lv_application_id NUMBER;
 lv_organization_id NUMBER;
 ln_user_id NUMBER;
 ln_responsibility_id NUMBER;
 ln_application_id NUMBER;
 ln_organization_id NUMBER;
 lv_unit VARCHAR2(20);
 l_party_tax_profile_id VARCHAR2(100);
 CURSOR cur_sup_tab (
 p_vendor_id NUMBER
 ) IS SELECT segment1
 ,vendor_name
 FROM ap_suppliers
 WHERE vendor_id = p_vendor_id;
 CURSOR cur_stg_tab (
 p_supplier_number VARCHAR2
 ,p_supplier_name VARCHAR2
 ) IS SELECT *
 FROM xxab_supp_stg_tab
 WHERE 1 = 1
 AND vendor_number = p_supplier_number
 AND vendor_name = p_supplier_name;
 l_vendor_sites cur_stg_tab%rowtype;
 CURSOR cur_org_id (
 p_unit VARCHAR2
 ) IS SELECT organization_id
 FROM hr_all_organization_units
 WHERE name = DECODE(
 p_unit
 ,'USABC'
 ,'ABCAAA'
 ,'USDEF'
 ,'ABCAAA'
 ,'USXYZ'
 ,'ABC'
 );
 BEGIN
 OPEN cur_sup_tab(p_vendor_id);
 FETCH cur_sup_tab INTO lv_supplier_number,lv_supplier_name;
 CLOSE cur_sup_tab;
 OPEN cur_stg_tab(
 lv_supplier_number
 ,lv_supplier_name
 );
 FETCH cur_stg_tab INTO l_vendor_sites;
 IF
 l_vendor_sites.unit = 'USXYZ'
 THEN
 lv_user_id := NULL;
 lv_responsibility_id := NULL;
 lv_application_id := NULL;
 BEGIN
 SELECT fu.user_id
 ,frt.responsibility_id
 ,frt.application_id INTO
 lv_user_id,lv_responsibility_id,lv_application_id
 FROM fnd_user fu
 ,fnd_responsibility_tl frt
 WHERE fu.user_name = 'SYSADMIN'
 AND frt.responsibility_name = 'Payables AB Supplier Setup';
 EXCEPTION
 WHEN OTHERS THEN
 NULL;
 END;
 fnd_global.apps_initialize(
 lv_user_id
 ,lv_responsibility_id
 ,lv_application_id
 );
 mo_global.init('SQLAP');
 OPEN cur_org_id(l_vendor_sites.unit);
 FETCH cur_org_id INTO l_vendor_site_org_id;
 CLOSE cur_org_id;
 fnd_client_info.set_org_context(l_vendor_site_org_id);
 l_vendor_site_rec.last_update_date := SYSDATE;
 l_vendor_site_rec.last_updated_by := fnd_global.user_id;
 l_vendor_site_rec.vendor_id := p_vendor_id;
 l_vendor_site_rec.vendor_site_code := l_vendor_sites.vendor_site_code;
 l_vendor_site_rec.country := l_vendor_sites.vendor_site_country;
 l_vendor_site_rec.address_line1 := l_vendor_sites.vendor_site_add1;
 l_vendor_site_rec.address_line2 := l_vendor_sites.vendor_site_add2;
 l_vendor_site_rec.address_line3 := l_vendor_sites.vendor_site_add3;
 l_vendor_site_rec.address_line4 := l_vendor_sites.vendor_site_add4;
 l_vendor_site_rec.city := l_vendor_sites.vendor_site_city;
 l_vendor_site_rec.zip := l_vendor_sites.vendor_site_zip;
 l_vendor_site_rec.org_id := l_vendor_site_org_id;
 l_vendor_site_rec.area_code := l_vendor_sites.vendor_phone_area_code;
 l_vendor_site_rec.phone := l_vendor_sites.vendor_phone_number;
 l_vendor_site_rec.email_address := l_vendor_sites.vendor_email_address;
 l_vendor_site_rec.vat_registration_num := l_vendor_sites.vat_registration_num;
 l_vendor_site_rec.ap_tax_rounding_rule := 'NEAREST';
 l_vendor_site_rec.vat_code := 'STANDARD';
 l_vendor_site_rec.auto_tax_calc_flag := 'Y';
 l_vendor_site_rec.supplier_notif_method := 'PRINT';
 l_vendor_site_rec.purchasing_site_flag := 'Y';
 l_vendor_site_rec.pay_site_flag := 'Y';
 l_vendor_site_rec.pay_group_lookup_code := l_vendor_sites.pay_group_lookup_code;
 l_vendor_site_rec.hold_unmatched_invoices_flag := 'Y';
 l_vendor_site_rec.hold_reason := 'UNMATCHED INVOICES';
 l_vendor_site_rec.ext_payee_rec.default_pmt_method := 'EFT';
 l_vendor_site_rec.remittance_email := l_vendor_sites.remittance_email_address; --v0.2
 IF
 l_vendor_sites.remittance_email_address IS NOT NULL
 THEN
 l_vendor_site_rec.remit_advice_delivery_method := 'EMAIL';
 END IF;
-- Call the Vendor site API
 ap_vendor_pub_pkg.create_vendor_site(
 p_api_version => 1.0
 ,p_init_msg_list => fnd_api.g_true
 ,p_commit => fnd_api.g_true
 ,p_validation_level => fnd_api.g_valid_level_full
 ,x_return_status => g_return_status
 ,x_msg_count => g_msg_count
 ,x_msg_data => g_msg_data
 ,p_vendor_site_rec => l_vendor_site_rec
 ,x_vendor_site_id => x_vendor_site_id
 ,x_party_site_id => x_party_site_id
 ,x_location_id => x_location_id
 );
 IF
 g_return_status <> fnd_api.g_ret_sts_success
 THEN
 FOR v_index IN 1..g_msg_count LOOP
 fnd_msg_pub.get(
 p_msg_index => v_index
 ,p_encoded => 'F'
 ,p_data => g_msg_data
 ,p_msg_index_out => g_msg_index_out
 );
 g_msg_data := substr(
 g_msg_data
 ,1
 ,200
 );
 apps.fnd_file.put_line(
 apps.fnd_file.log
 ,'Error at Vendor site creation :'
 || g_msg_data
 );
 ROLLBACK;
 END LOOP;
 ELSE
 apps.fnd_file.put_line(
 apps.fnd_file.log
 ,'Vendor site: '
 || l_vendor_site_rec.vendor_site_code
 || ' created for vendor: '
 || lv_supplier_number
 );
/*API to update Communication details under Address Book section*/ --v0.4
 IF
 l_vendor_sites.vendor_phone_area_code IS NOT NULL OR l_vendor_sites.vendor_phone_number IS NOT NULL OR l_vendor_sites.vendor_email_address IS
NOT NULL
 THEN
-- Initializing the Mandatory API parameters
 p_contact_point_rec.contact_point_type := 'PHONE';
 p_contact_point_rec.owner_table_name := 'HZ_PARTY_SITES';
 p_contact_point_rec.owner_table_id := x_party_site_id;
 p_contact_point_rec.primary_flag := 'Y';
 p_contact_point_rec.created_by_module := 'POS_SUPPLIER_MGMT';
 p_phone_rec.phone_area_code := l_vendor_sites.vendor_phone_area_code;
 p_phone_rec.phone_number := l_vendor_sites.vendor_phone_number;
 p_phone_rec.phone_line_type := 'GEN';
 hz_contact_point_v2pub.create_contact_point(
 p_init_msg_list => fnd_api.g_true
 ,p_contact_point_rec => p_contact_point_rec
 ,p_edi_rec => p_edi_rec
 ,p_email_rec => p_email_rec
 ,p_phone_rec => p_phone_rec
 ,p_telex_rec => p_telex_rec
 ,p_web_rec => p_web_rec
 ,x_contact_point_id => x_contact_point_id
 ,x_return_status => g_return_status
 ,x_msg_count => g_msg_count
 ,x_msg_data => g_msg_data
 );
 IF
 g_return_status <> fnd_api.g_ret_sts_success
 THEN
 FOR i IN 1..g_msg_count LOOP
 g_msg_data := fnd_msg_pub.get(
 p_msg_index => i
 ,p_encoded => 'F'
 );
 dbms_output.put_line(i
 || ') '
 || g_msg_data);
 END LOOP;
 END IF;
-- Initializing the Mandatory API parameters
 p_contact_point_rec.contact_point_type := 'EMAIL';
 p_contact_point_rec.owner_table_name := 'HZ_PARTY_SITES';
 p_contact_point_rec.owner_table_id := x_party_site_id;
 p_contact_point_rec.primary_flag := 'Y';
 p_contact_point_rec.created_by_module := 'POS_SUPPLIER_MGMT';
 p_email_rec.email_address := l_vendor_sites.vendor_email_address;
 hz_contact_point_v2pub.create_contact_point(
 p_init_msg_list => fnd_api.g_true
 ,p_contact_point_rec => p_contact_point_rec
 ,p_edi_rec => p_edi_rec
 ,p_email_rec => p_email_rec
 ,p_phone_rec => p_phone_rec
 ,p_telex_rec => p_telex_rec
 ,p_web_rec => p_web_rec
 ,x_contact_point_id => x_contact_point_id
 ,x_return_status => g_return_status
 ,x_msg_count => g_msg_count
 ,x_msg_data => g_msg_data
 );
 IF
 g_return_status <> fnd_api.g_ret_sts_success
 THEN
 FOR i IN 1..g_msg_count LOOP
 g_msg_data := fnd_msg_pub.get(
 p_msg_index => i
 ,p_encoded => 'F'
 );
 dbms_output.put_line(i
 || ') '
 || g_msg_data);
 END LOOP;
 END IF;
 END IF;
/*API to update Address Purpose to Purchasing*/
 p_party_site_use_rec.site_use_type := 'PURCHASING';
 p_party_site_use_rec.party_site_id := x_party_site_id;
 p_party_site_use_rec.created_by_module := 'POS_SUPPLIER_MGMT';
 hz_party_site_v2pub.create_party_site_use(
 p_init_msg_list => fnd_api.g_true
 ,p_party_site_use_rec => p_party_site_use_rec
 ,x_party_site_use_id => x_party_site_use_id
 ,x_return_status => g_return_status
 ,x_msg_count => g_msg_count
 ,x_msg_data => g_msg_data
 );
/*API to update Address Purpose to Payments*/
 p_party_site_use_rec.site_use_type := 'PAY';
 p_party_site_use_rec.party_site_id := x_party_site_id;
 p_party_site_use_rec.created_by_module := 'POS_SUPPLIER_MGMT';
 hz_party_site_v2pub.create_party_site_use(
 p_init_msg_list => fnd_api.g_true
 ,p_party_site_use_rec => p_party_site_use_rec
 ,x_party_site_use_id => x_party_site_use_id
 ,x_return_status => g_return_status
 ,x_msg_count => g_msg_count
 ,x_msg_data => g_msg_data
 );
 BEGIN
 SELECT party_tax_profile_id INTO
 l_party_tax_profile_id
 FROM zx_party_tax_profile
 WHERE party_id = x_party_site_id
 AND party_type_code = 'THIRD_PARTY_SITE'
 AND ROWNUM = 1;
 EXCEPTION
 WHEN OTHERS THEN
 l_party_tax_profile_id := NULL;
 END;
 IF
 l_vendor_sites.vat_registration_num IS NOT NULL
 THEN
/*API to update Tax Registration Number at Supplier site level under Tax Details section*/
 zx_registrations_pkg.insert_row(
 p_request_id => NULL
 ,p_attribute1 => NULL
 ,p_attribute2 => NULL
 ,p_attribute3 => NULL
 ,p_attribute4 => NULL
 ,p_attribute5 => NULL
 ,p_attribute6 => NULL
 ,p_rounding_rule_code => 'NEAREST'
 ,p_validation_rule => NULL
 ,p_tax_jurisdiction_code => NULL
 ,p_self_assess_flag => NULL
 ,p_registration_status_code => NULL
 ,p_registration_source_code => NULL
 ,p_registration_reason_code => NULL
 ,p_tax => 'GB VAT'
 ,p_tax_regime_code => 'GB VAT'
 ,p_inclusive_tax_flag => NULL
 ,p_effective_from => SYSDATE
 ,p_effective_to => NULL
 ,p_rep_party_tax_name => NULL
 ,p_default_registration_flag => NULL
 ,p_bank_account_num => NULL
 ,p_record_type_code => NULL
 ,p_legal_location_id => NULL
 ,p_tax_authority_id => NULL
 ,p_rep_tax_authority_id => NULL
 ,p_coll_tax_authority_id => NULL
 ,p_registration_type_code => NULL
 ,p_registration_number => l_vendor_sites.vat_registration_num
 ,p_party_tax_profile_id => l_party_tax_profile_id
 ,p_legal_registration_id => NULL
 ,p_bank_id => NULL
 ,p_bank_branch_id => NULL
 ,p_account_site_id => NULL
 ,p_attribute14 => NULL
 ,p_attribute15 => NULL
 ,p_attribute_category => NULL
 ,p_program_login_id => NULL
 ,p_account_id => NULL
 ,p_tax_classification_code => NULL
 ,p_attribute7 => NULL
 ,p_attribute8 => NULL
 ,p_attribute9 => NULL
 ,p_attribute10 => NULL
 ,p_attribute11 => NULL
 ,p_attribute12 => NULL
 ,p_attribute13 => NULL
 ,x_return_status => g_return_status
 );
/*API to update Tax Registration Number at Supplier site level under Tax and Reporting section*/
 FOR v_site IN ( SELECT pvsa.*
 ,pvsa.rowid
 FROM po_vendor_sites_all pvsa
 WHERE party_site_id = x_party_site_id
 ) LOOP
 ap_vendor_sites_pkg.update_row(
 x_rowid => v_site.rowid
 ,x_vendor_site_id => v_site.vendor_site_id
 ,x_last_update_date => v_site.last_update_date
 ,x_last_updated_by => v_site.last_updated_by
 ,x_vendor_id => v_site.vendor_id
 ,x_vendor_site_code => v_site.vendor_site_code
 ,x_last_update_login => v_site.last_update_login
 ,x_creation_date => v_site.creation_date
 ,x_created_by => v_site.created_by
 ,x_purchasing_site_flag => v_site.purchasing_site_flag
 ,x_rfq_only_site_flag => v_site.rfq_only_site_flag
 ,x_pay_site_flag => v_site.pay_site_flag
 ,x_attention_ar_flag => v_site.attention_ar_flag
 ,x_address_line1 => v_site.address_line1
 ,x_address_line2 => v_site.address_line2
 ,x_address_line3 => v_site.address_line3
 ,x_city => v_site.city
 ,x_state => v_site.state
 ,x_zip => v_site.zip
 ,x_province => v_site.province
 ,x_country => v_site.country
 ,x_area_code => v_site.area_code
 ,x_phone => v_site.phone
 ,x_customer_num => v_site.customer_num
 ,x_ship_to_location_id => v_site.ship_to_location_id
 ,x_bill_to_location_id => v_site.bill_to_location_id
 ,x_ship_via_lookup_code => v_site.ship_via_lookup_code
 ,x_freight_terms_lookup_code => v_site.freight_terms_lookup_code
 ,x_fob_lookup_code => v_site.fob_lookup_code
 ,x_inactive_date => v_site.inactive_date
 ,x_fax => v_site.fax
 ,x_fax_area_code => v_site.fax_area_code
 ,x_telex => v_site.telex
 ,x_bank_account_name => v_site.bank_account_name
 ,x_bank_account_num => v_site.bank_account_num
 ,x_bank_num => v_site.bank_num
 ,x_bank_account_type => v_site.bank_account_type
 ,x_terms_date_basis => v_site.terms_date_basis
 ,x_current_catalog_num => v_site.current_catalog_num
 ,x_distribution_set_id => v_site.distribution_set_id
 ,x_accts_pay_ccid => v_site.accts_pay_code_combination_id
 ,x_future_dated_payment_ccid => v_site.future_dated_payment_ccid
 ,x_prepay_code_combination_id => v_site.prepay_code_combination_id
 ,x_pay_group_lookup_code => v_site.pay_group_lookup_code
 ,x_payment_priority => v_site.payment_priority
 ,x_terms_id => v_site.terms_id
 ,x_invoice_amount_limit => v_site.invoice_amount_limit
 ,x_pay_date_basis_lookup_code => v_site.pay_date_basis_lookup_code
 ,x_always_take_disc_flag => v_site.always_take_disc_flag
 ,x_invoice_currency_code => v_site.invoice_currency_code
 ,x_payment_currency_code => v_site.payment_currency_code
 ,x_hold_all_payments_flag => v_site.hold_all_payments_flag
 ,x_hold_future_payments_flag => v_site.hold_future_payments_flag
 ,x_hold_reason => v_site.hold_reason
 ,x_hold_unmatched_invoices_flag => v_site.hold_unmatched_invoices_flag
 ,x_match_option => v_site.match_option
 ,x_create_debit_memo_flag => v_site.create_debit_memo_flag
 ,x_tax_reporting_site_flag => v_site.tax_reporting_site_flag
 ,x_attribute_category => v_site.attribute_category
 ,x_attribute1 => v_site.attribute1
 ,x_attribute2 => v_site.attribute2
 ,x_attribute3 => v_site.attribute3
 ,x_attribute4 => v_site.attribute4
 ,x_attribute5 => v_site.attribute5
 ,x_attribute6 => v_site.attribute6
 ,x_attribute7 => v_site.attribute7
 ,x_attribute8 => v_site.attribute8
 ,x_attribute9 => v_site.attribute9
 ,x_attribute10 => v_site.attribute10
 ,x_attribute11 => v_site.attribute11
 ,x_attribute12 => v_site.attribute12
 ,x_attribute13 => v_site.attribute13
 ,x_attribute14 => v_site.attribute14
 ,x_attribute15 => v_site.attribute15
 ,x_validation_number => v_site.validation_number
 ,x_exclude_freight_from_disc => v_site.exclude_freight_from_discount
 ,x_vat_registration_num => l_vendor_sites.vat_registration_num
 ,–v_site.vat_registration_num
 ,x_check_digits => v_site.check_digits
 ,x_bank_number => v_site.bank_number
 , --v0.3
 x_address_line4 => v_site.address_line4
 ,x_county => v_site.county
 ,x_address_style => v_site.address_style
 ,x_language => v_site.language
 ,x_allow_awt_flag => v_site.allow_awt_flag
 ,x_awt_group_id => v_site.awt_group_id
 ,x_pay_awt_group_id => v_site.pay_awt_group_id
 ,x_pay_on_code => v_site.pay_on_code
 ,x_default_pay_site_id => v_site.default_pay_site_id
 ,x_pay_on_receipt_summary_code => v_site.pay_on_receipt_summary_code
 ,x_bank_branch_type => v_site.bank_branch_type
 ,x_edi_id_number => v_site.edi_id_number
 ,x_vendor_site_code_alt => v_site.vendor_site_code_alt
 ,x_address_lines_alt => v_site.address_lines_alt
 ,x_global_attribute_category => v_site.global_attribute_category
 ,x_global_attribute1 => v_site.global_attribute1
 ,x_global_attribute2 => v_site.global_attribute2
 ,x_global_attribute3 => v_site.global_attribute3
 ,x_global_attribute4 => v_site.global_attribute4
 ,x_global_attribute5 => v_site.global_attribute5
 ,x_global_attribute6 => v_site.global_attribute6
 ,x_global_attribute7 => v_site.global_attribute7
 ,x_global_attribute8 => v_site.global_attribute8
 ,x_global_attribute9 => v_site.global_attribute9
 ,x_global_attribute10 => v_site.global_attribute10
 ,x_global_attribute11 => v_site.global_attribute11
 ,x_global_attribute12 => v_site.global_attribute12
 ,x_global_attribute13 => v_site.global_attribute13
 ,x_global_attribute14 => v_site.global_attribute14
 ,x_global_attribute15 => v_site.global_attribute15
 ,x_global_attribute16 => v_site.global_attribute16
 ,x_global_attribute17 => v_site.global_attribute17
 ,x_global_attribute18 => v_site.global_attribute18
 ,x_global_attribute19 => v_site.global_attribute19
 ,x_global_attribute20 => v_site.global_attribute20
 ,x_bank_charge_bearer => v_site.bank_charge_bearer
 ,x_ece_tp_location_code => v_site.ece_tp_location_code
 ,x_pcard_site_flag => v_site.pcard_site_flag
 ,x_country_of_origin_code => v_site.country_of_origin_code
 ,x_calling_sequence => 'SQL SCRIPT – RFC7917'
 ,x_shipping_location_id => NULL
 , --v_Site.shipping_location_id, -- do not update shipping location information
 x_supplier_notif_method => v_site.supplier_notif_method
 ,x_email_address => v_site.email_address
 ,x_primary_pay_site_flag => v_site.primary_pay_site_flag
 ,x_org_id => v_site.org_id
 );
 END LOOP;
 END IF;
 END IF;
 ELSE
 lv_user_id := NULL;
 lv_responsibility_id := NULL;
 lv_application_id := NULL;
 BEGIN
 SELECT fu.user_id
 ,frt.responsibility_id
 ,frt.application_id INTO
 lv_user_id,lv_responsibility_id,lv_application_id
 FROM fnd_user fu
 ,fnd_responsibility_tl frt
 WHERE fu.user_name = 'SYSADMIN'
 AND frt.responsibility_name = 'ABC Payables Supplier Setup';
 EXCEPTION
 WHEN OTHERS THEN
 NULL;
 END;
 fnd_global.apps_initialize(
 lv_user_id
 ,lv_responsibility_id
 ,lv_application_id
 );
 mo_global.init('SQLAP');
 OPEN cur_org_id(l_vendor_sites.unit);
 FETCH cur_org_id INTO l_vendor_site_org_id;
 CLOSE cur_org_id;
 fnd_client_info.set_org_context(l_vendor_site_org_id);
 l_vendor_site_rec.last_update_date := SYSDATE;
 l_vendor_site_rec.last_updated_by := fnd_global.user_id;
 l_vendor_site_rec.vendor_id := p_vendor_id;
 l_vendor_site_rec.vendor_site_code := l_vendor_sites.vendor_site_code;
 l_vendor_site_rec.country := l_vendor_sites.vendor_site_country;
 l_vendor_site_rec.address_line1 := l_vendor_sites.vendor_site_add1;
 l_vendor_site_rec.address_line2 := l_vendor_sites.vendor_site_add2;
 l_vendor_site_rec.address_line3 := l_vendor_sites.vendor_site_add3;
 l_vendor_site_rec.address_line4 := l_vendor_sites.vendor_site_add4;
 l_vendor_site_rec.city := l_vendor_sites.vendor_site_city;
 l_vendor_site_rec.zip := l_vendor_sites.vendor_site_zip;
 l_vendor_site_rec.org_id := l_vendor_site_org_id;
 l_vendor_site_rec.area_code := l_vendor_sites.vendor_phone_area_code;
 l_vendor_site_rec.phone := l_vendor_sites.vendor_phone_number;
 l_vendor_site_rec.email_address := l_vendor_sites.vendor_email_address;
 l_vendor_site_rec.vat_registration_num := l_vendor_sites.vat_registration_num;
 l_vendor_site_rec.ap_tax_rounding_rule := 'NEAREST';
 l_vendor_site_rec.vat_code := 'STANDARD';
 l_vendor_site_rec.auto_tax_calc_flag := 'Y';
 –l_vendor_site_rec.tolerance_name := 'ABC – Tolerance';
 l_vendor_site_rec.supplier_notif_method := 'PRINT';
 l_vendor_site_rec.purchasing_site_flag := 'Y';
 l_vendor_site_rec.pay_site_flag := 'Y';
 l_vendor_site_rec.pay_group_lookup_code := l_vendor_sites.pay_group_lookup_code;
 l_vendor_site_rec.hold_unmatched_invoices_flag := 'Y';
 l_vendor_site_rec.hold_reason := 'UNMATCHED INVOICES';
 l_vendor_site_rec.ext_payee_rec.default_pmt_method := 'EFT';
 l_vendor_site_rec.remittance_email := l_vendor_sites.remittance_email_address; --v0.2
 IF
 l_vendor_sites.remittance_email_address IS NOT NULL
 THEN
 l_vendor_site_rec.remit_advice_delivery_method := 'EMAIL';
 END IF;
-- Call the API
 ap_vendor_pub_pkg.create_vendor_site(
 p_api_version => 1.0
 ,p_init_msg_list => fnd_api.g_true
 ,p_commit => fnd_api.g_true
 ,p_validation_level => fnd_api.g_valid_level_full
 ,x_return_status => g_return_status
 ,x_msg_count => g_msg_count
 ,x_msg_data => g_msg_data
 ,p_vendor_site_rec => l_vendor_site_rec
 ,x_vendor_site_id => x_vendor_site_id
 ,x_party_site_id => x_party_site_id
 ,x_location_id => x_location_id
 );
 IF
 g_return_status <> fnd_api.g_ret_sts_success
 THEN
 FOR v_index IN 1..g_msg_count LOOP
 fnd_msg_pub.get(
 p_msg_index => v_index
 ,p_encoded => 'F'
 ,p_data => g_msg_data
 ,p_msg_index_out => g_msg_index_out
 );
 g_msg_data := substr(
 g_msg_data
 ,1
 ,200
 );
 apps.fnd_file.put_line(
 apps.fnd_file.log
 ,'Error at Vendor site creation :'
 || g_msg_data
 );
 ROLLBACK;
 END LOOP;
 ELSE
 apps.fnd_file.put_line(
 apps.fnd_file.log
 ,'Vendor site: '
 || l_vendor_site_rec.vendor_site_code
 || ' created for vendor: '
 || lv_supplier_number
 );
/*API to update Communication details under Address Book section*/ --v0.4
 IF
 l_vendor_sites.vendor_phone_area_code IS NOT NULL OR l_vendor_sites.vendor_phone_number IS NOT NULL OR l_vendor_sites.vendor_email_address IS
NOT NULL
 THEN
-- Initializing the Mandatory API parameters
 p_contact_point_rec.contact_point_type := 'PHONE';
 p_contact_point_rec.owner_table_name := 'HZ_PARTY_SITES';
 p_contact_point_rec.owner_table_id := x_party_site_id;
 p_contact_point_rec.primary_flag := 'Y';
 p_contact_point_rec.created_by_module := 'POS_SUPPLIER_MGMT';
 p_phone_rec.phone_area_code := l_vendor_sites.vendor_phone_area_code;
 p_phone_rec.phone_number := l_vendor_sites.vendor_phone_number;
 p_phone_rec.phone_line_type := 'GEN';
 hz_contact_point_v2pub.create_contact_point(
 p_init_msg_list => fnd_api.g_true
 ,p_contact_point_rec => p_contact_point_rec
 ,p_edi_rec => p_edi_rec
 ,p_email_rec => p_email_rec
 ,p_phone_rec => p_phone_rec
 ,p_telex_rec => p_telex_rec
 ,p_web_rec => p_web_rec
 ,x_contact_point_id => x_contact_point_id
 ,x_return_status => g_return_status
 ,x_msg_count => g_msg_count
 ,x_msg_data => g_msg_data
 );
 IF
 g_return_status <> fnd_api.g_ret_sts_success
 THEN
 FOR i IN 1..g_msg_count LOOP
 g_msg_data := fnd_msg_pub.get(
 p_msg_index => i
 ,p_encoded => 'F'
 );
 dbms_output.put_line(i
 || ') '
 || g_msg_data);
 END LOOP;
 END IF;
-- Initializing the Mandatory API parameters
 p_contact_point_rec.contact_point_type := 'EMAIL';
 p_contact_point_rec.owner_table_name := 'HZ_PARTY_SITES';
 p_contact_point_rec.owner_table_id := x_party_site_id;
 p_contact_point_rec.primary_flag := 'Y';
 p_contact_point_rec.created_by_module := 'POS_SUPPLIER_MGMT';
 p_email_rec.email_address := l_vendor_sites.vendor_email_address;
 hz_contact_point_v2pub.create_contact_point(
 p_init_msg_list => fnd_api.g_true
 ,p_contact_point_rec => p_contact_point_rec
 ,p_edi_rec => p_edi_rec
 ,p_email_rec => p_email_rec
 ,p_phone_rec => p_phone_rec
 ,p_telex_rec => p_telex_rec
 ,p_web_rec => p_web_rec
 ,x_contact_point_id => x_contact_point_id
 ,x_return_status => g_return_status
 ,x_msg_count => g_msg_count
 ,x_msg_data => g_msg_data
 );
 IF
 g_return_status <> fnd_api.g_ret_sts_success
 THEN
 FOR i IN 1..g_msg_count LOOP
 g_msg_data := fnd_msg_pub.get(
 p_msg_index => i
 ,p_encoded => 'F'
 );
 dbms_output.put_line(i
 || ') '
 || g_msg_data);
 END LOOP;
 END IF;
 END IF;
/*API to update Address Purpose to Purchasing*/
 p_party_site_use_rec.site_use_type := 'PURCHASING';
 p_party_site_use_rec.party_site_id := x_party_site_id;
 p_party_site_use_rec.created_by_module := 'POS_SUPPLIER_MGMT';
 hz_party_site_v2pub.create_party_site_use(
 p_init_msg_list => fnd_api.g_true
 ,p_party_site_use_rec => p_party_site_use_rec
 ,x_party_site_use_id => x_party_site_use_id
 ,x_return_status => g_return_status
 ,x_msg_count => g_msg_count
 ,x_msg_data => g_msg_data
 );
/*API to update Address Purpose to Payments*/
 p_party_site_use_rec.site_use_type := 'PAY';
 p_party_site_use_rec.party_site_id := x_party_site_id;
 p_party_site_use_rec.created_by_module := 'POS_SUPPLIER_MGMT';
 hz_party_site_v2pub.create_party_site_use(
 p_init_msg_list => fnd_api.g_true
 ,p_party_site_use_rec => p_party_site_use_rec
 ,x_party_site_use_id => x_party_site_use_id
 ,x_return_status => g_return_status
 ,x_msg_count => g_msg_count
 ,x_msg_data => g_msg_data
 );
 BEGIN
 SELECT party_tax_profile_id INTO
 l_party_tax_profile_id
 FROM zx_party_tax_profile
 WHERE party_id = x_party_site_id
 AND party_type_code = 'THIRD_PARTY_SITE'
 AND ROWNUM = 1;
 EXCEPTION
 WHEN OTHERS THEN
 l_party_tax_profile_id := NULL;
 END;
 IF
 l_vendor_sites.vat_registration_num IS NOT NULL
 THEN
/*API to update Tax Registration Number at Supplier site level under Tax Details section*/
 zx_registrations_pkg.insert_row(
 p_request_id => NULL
 ,p_attribute1 => NULL
 ,p_attribute2 => NULL
 ,p_attribute3 => NULL
 ,p_attribute4 => NULL
 ,p_attribute5 => NULL
 ,p_attribute6 => NULL
 ,p_rounding_rule_code => 'NEAREST'
 ,p_validation_rule => NULL
 ,p_tax_jurisdiction_code => NULL
 ,p_self_assess_flag => NULL
 ,p_registration_status_code => NULL
 ,p_registration_source_code => NULL
 ,p_registration_reason_code => NULL
 ,p_tax => 'GB VAT'
 ,p_tax_regime_code => 'GB VAT'
 ,p_inclusive_tax_flag => NULL
 ,p_effective_from => SYSDATE
 ,p_effective_to => NULL
 ,p_rep_party_tax_name => NULL
 ,p_default_registration_flag => NULL
 ,p_bank_account_num => NULL
 ,p_record_type_code => NULL
 ,p_legal_location_id => NULL
 ,p_tax_authority_id => NULL
 ,p_rep_tax_authority_id => NULL
 ,p_coll_tax_authority_id => NULL
 ,p_registration_type_code => NULL
 ,p_registration_number => l_vendor_sites.vat_registration_num
 ,p_party_tax_profile_id => l_party_tax_profile_id
 ,p_legal_registration_id => NULL
 ,p_bank_id => NULL
 ,p_bank_branch_id => NULL
 ,p_account_site_id => NULL
 ,p_attribute14 => NULL
 ,p_attribute15 => NULL
 ,p_attribute_category => NULL
 ,p_program_login_id => NULL
 ,p_account_id => NULL
 ,p_tax_classification_code => NULL
 ,p_attribute7 => NULL
 ,p_attribute8 => NULL
 ,p_attribute9 => NULL
 ,p_attribute10 => NULL
 ,p_attribute11 => NULL
 ,p_attribute12 => NULL
 ,p_attribute13 => NULL
 ,x_return_status => g_return_status
 );
/*API to update Tax Registration Number at Supplier site level under Tax and Reporting section*/
 FOR v_site IN ( SELECT pvsa.*
 ,pvsa.rowid
 FROM po_vendor_sites_all pvsa
 WHERE party_site_id = x_party_site_id
 ) LOOP
 ap_vendor_sites_pkg.update_row(
 x_rowid => v_site.rowid
 ,x_vendor_site_id => v_site.vendor_site_id
 ,x_last_update_date => v_site.last_update_date
 ,x_last_updated_by => v_site.last_updated_by
 ,x_vendor_id => v_site.vendor_id
 ,x_vendor_site_code => v_site.vendor_site_code
 ,x_last_update_login => v_site.last_update_login
 ,x_creation_date => v_site.creation_date
 ,x_created_by => v_site.created_by
 ,x_purchasing_site_flag => v_site.purchasing_site_flag
 ,x_rfq_only_site_flag => v_site.rfq_only_site_flag
 ,x_pay_site_flag => v_site.pay_site_flag
 ,x_attention_ar_flag => v_site.attention_ar_flag
 ,x_address_line1 => v_site.address_line1
 ,x_address_line2 => v_site.address_line2
 ,x_address_line3 => v_site.address_line3
 ,x_city => v_site.city
 ,x_state => v_site.state
 ,x_zip => v_site.zip
 ,x_province => v_site.province
 ,x_country => v_site.country
 ,x_area_code => v_site.area_code
 ,x_phone => v_site.phone
 ,x_customer_num => v_site.customer_num
 ,x_ship_to_location_id => v_site.ship_to_location_id
 ,x_bill_to_location_id => v_site.bill_to_location_id
 ,x_ship_via_lookup_code => v_site.ship_via_lookup_code
 ,x_freight_terms_lookup_code => v_site.freight_terms_lookup_code
 ,x_fob_lookup_code => v_site.fob_lookup_code
 ,x_inactive_date => v_site.inactive_date
 ,x_fax => v_site.fax
 ,x_fax_area_code => v_site.fax_area_code
 ,x_telex => v_site.telex
 ,x_bank_account_name => v_site.bank_account_name
 ,x_bank_account_num => v_site.bank_account_num
 ,x_bank_num => v_site.bank_num
 ,x_bank_account_type => v_site.bank_account_type
 ,x_terms_date_basis => v_site.terms_date_basis
 ,x_current_catalog_num => v_site.current_catalog_num
 ,x_distribution_set_id => v_site.distribution_set_id
 ,x_accts_pay_ccid => v_site.accts_pay_code_combination_id
 ,x_future_dated_payment_ccid => v_site.future_dated_payment_ccid
 ,x_prepay_code_combination_id => v_site.prepay_code_combination_id
 ,x_pay_group_lookup_code => v_site.pay_group_lookup_code
 ,x_payment_priority => v_site.payment_priority
 ,x_terms_id => v_site.terms_id
 ,x_invoice_amount_limit => v_site.invoice_amount_limit
 ,x_pay_date_basis_lookup_code => v_site.pay_date_basis_lookup_code
 ,x_always_take_disc_flag => v_site.always_take_disc_flag
 ,x_invoice_currency_code => v_site.invoice_currency_code
 ,x_payment_currency_code => v_site.payment_currency_code
 ,x_hold_all_payments_flag => v_site.hold_all_payments_flag
 ,x_hold_future_payments_flag => v_site.hold_future_payments_flag
 ,x_hold_reason => v_site.hold_reason
 ,x_hold_unmatched_invoices_flag => v_site.hold_unmatched_invoices_flag
 ,x_match_option => v_site.match_option
 ,x_create_debit_memo_flag => v_site.create_debit_memo_flag
 ,x_tax_reporting_site_flag => v_site.tax_reporting_site_flag
 ,x_attribute_category => v_site.attribute_category
 ,x_attribute1 => v_site.attribute1
 ,x_attribute2 => v_site.attribute2
 ,x_attribute3 => v_site.attribute3
 ,x_attribute4 => v_site.attribute4
 ,x_attribute5 => v_site.attribute5
 ,x_attribute6 => v_site.attribute6
 ,x_attribute7 => v_site.attribute7
 ,x_attribute8 => v_site.attribute8
 ,x_attribute9 => v_site.attribute9
 ,x_attribute10 => v_site.attribute10
 ,x_attribute11 => v_site.attribute11
 ,x_attribute12 => v_site.attribute12
 ,x_attribute13 => v_site.attribute13
 ,x_attribute14 => v_site.attribute14
 ,x_attribute15 => v_site.attribute15
 ,x_validation_number => v_site.validation_number
 ,x_exclude_freight_from_disc => v_site.exclude_freight_from_discount
 ,x_vat_registration_num => l_vendor_sites.vat_registration_num
 ,--v_Site.vat_registration_num,
 x_check_digits => v_site.check_digits
 ,x_bank_number => v_site.bank_number
 , --v0.3
 x_address_line4 => v_site.address_line4
 ,x_county => v_site.county
 ,x_address_style => v_site.address_style
 ,x_language => v_site.language
 ,x_allow_awt_flag => v_site.allow_awt_flag
 ,x_awt_group_id => v_site.awt_group_id
 ,x_pay_awt_group_id => v_site.pay_awt_group_id
 ,x_pay_on_code => v_site.pay_on_code
 ,x_default_pay_site_id => v_site.default_pay_site_id
 ,x_pay_on_receipt_summary_code => v_site.pay_on_receipt_summary_code
 ,x_bank_branch_type => v_site.bank_branch_type
 ,x_edi_id_number => v_site.edi_id_number
 ,x_vendor_site_code_alt => v_site.vendor_site_code_alt
 ,x_address_lines_alt => v_site.address_lines_alt
 ,x_global_attribute_category => v_site.global_attribute_category
 ,x_global_attribute1 => v_site.global_attribute1
 ,x_global_attribute2 => v_site.global_attribute2
 ,x_global_attribute3 => v_site.global_attribute3
 ,x_global_attribute4 => v_site.global_attribute4
 ,x_global_attribute5 => v_site.global_attribute5
 ,x_global_attribute6 => v_site.global_attribute6
 ,x_global_attribute7 => v_site.global_attribute7
 ,x_global_attribute8 => v_site.global_attribute8
 ,x_global_attribute9 => v_site.global_attribute9
 ,x_global_attribute10 => v_site.global_attribute10
 ,x_global_attribute11 => v_site.global_attribute11
 ,x_global_attribute12 => v_site.global_attribute12
 ,x_global_attribute13 => v_site.global_attribute13
 ,x_global_attribute14 => v_site.global_attribute14
 ,x_global_attribute15 => v_site.global_attribute15
 ,x_global_attribute16 => v_site.global_attribute16
 ,x_global_attribute17 => v_site.global_attribute17
 ,x_global_attribute18 => v_site.global_attribute18
 ,x_global_attribute19 => v_site.global_attribute19
 ,x_global_attribute20 => v_site.global_attribute20
 ,x_bank_charge_bearer => v_site.bank_charge_bearer
 ,x_ece_tp_location_code => v_site.ece_tp_location_code
 ,x_pcard_site_flag => v_site.pcard_site_flag
 ,x_country_of_origin_code => v_site.country_of_origin_code
 ,x_calling_sequence => 'SQL SCRIPT – RFC7917'
 ,x_shipping_location_id => NULL
 , --v_Site.shipping_location_id, -- do not update shipping location information
 x_supplier_notif_method => v_site.supplier_notif_method
 ,x_email_address => v_site.email_address
 ,x_primary_pay_site_flag => v_site.primary_pay_site_flag
 ,x_org_id => v_site.org_id
 );
 END LOOP;
 END IF;
 END IF;
 END IF;
 CLOSE cur_stg_tab;
 END xxab_create_vendor_site;
--------------------------------------------------
-- * Procedure Name : xxab_create_vendor_contact
-- * Description : This Procedure creates the Supplier site
-- * contacts
--------------------------------------------------
 PROCEDURE xxab_create_vendor_contact (
 p_vendor_id IN NUMBER
 ,p_vendor_site_id IN NUMBER
 ) IS
 l_vendor_contact_rec apps.ap_vendor_pub_pkg.r_vendor_contact_rec_type;
 x_vendor_contact_id NUMBER;
 x_per_party_id NUMBER;
 x_rel_party_id NUMBER;
 x_rel_id NUMBER;
 x_org_contact_id NUMBER;
 x_party_site_id NUMBER;
 lv_vendor_number VARCHAR2(100);
 lv_vendor_site_code VARCHAR2(100);
 l_vendor_site_org_id VARCHAR2(50);
 CURSOR cur_sup_tab (
 p_vendor_id NUMBER
 ) IS SELECT segment1 FROM ap_suppliers WHERE vendor_id = p_vendor_id;
 CURSOR cur_sup_site_tab (
 p_vendor_number VARCHAR2
 ) IS SELECT vendor_site_code FROM ap_supplier_sites_all WHERE vendor_id = p_vendor_id;
 CURSOR cur_stg_tab (
 p_vendor_number VARCHAR2
 ,p_vendor_site_code VARCHAR2
 ) IS SELECT *
 FROM xxab_supp_stg_tab
 WHERE 1 = 1
 AND vendor_number = p_vendor_number
 AND vendor_site_code = p_vendor_site_code;
 l_site_contacts cur_stg_tab%rowtype;
 CURSOR cur_org_id (
 p_unit VARCHAR2
 ) IS SELECT organization_id
 FROM hr_all_organization_units
 WHERE name = DECODE(
 p_unit
 ,'USABC'
 ,'ABCAAA'
 ,'USDEF'
 ,'ABCAAA'
 ,'USXYZ'
 ,'ABC'
 );
 BEGIN
 OPEN cur_sup_tab(p_vendor_id);
 FETCH cur_sup_tab INTO lv_vendor_number;
 CLOSE cur_sup_tab;
 OPEN cur_sup_site_tab(lv_vendor_number);
 FETCH cur_sup_site_tab INTO lv_vendor_site_code;
 CLOSE cur_sup_site_tab;
 OPEN cur_stg_tab(
 lv_vendor_number
 ,lv_vendor_site_code
 );
 LOOP
 FETCH cur_stg_tab INTO l_site_contacts;
 EXIT WHEN cur_stg_tab%notfound;
 OPEN cur_org_id(l_site_contacts.unit);
 FETCH cur_org_id INTO l_vendor_site_org_id;
 CLOSE cur_org_id;
-- Assign Contact Details
 l_vendor_contact_rec.vendor_id := p_vendor_id;
 l_vendor_contact_rec.vendor_site_id := p_vendor_site_id;
 l_vendor_contact_rec.person_first_name := l_site_contacts.vendor_contact_first_name;
 l_vendor_contact_rec.person_last_name := l_site_contacts.vendor_contact_last_name;
 l_vendor_contact_rec.email_address := l_site_contacts.vendor_contact_email;
 l_vendor_contact_rec.org_id := l_vendor_site_org_id;
 l_vendor_contact_rec.person_title := l_site_contacts.person_title;
 IF
 ( l_site_contacts.vendor_contact_first_name IS NOT NULL OR l_site_contacts.vendor_contact_last_name IS NOT NULL OR l_site_contacts.vendor_contact_email
IS NOT NULL )
 THEN
 ap_vendor_pub_pkg.create_vendor_contact(
 p_api_version => 1.0
 ,p_init_msg_list => fnd_api.g_true
 ,p_commit => fnd_api.g_true
 ,p_validation_level => fnd_api.g_valid_level_full
 ,x_return_status => g_return_status
 ,x_msg_count => g_msg_count
 ,x_msg_data => g_msg_data
 ,p_vendor_contact_rec => l_vendor_contact_rec
 ,x_vendor_contact_id => x_vendor_contact_id
 ,x_per_party_id => x_per_party_id
 ,x_rel_party_id => x_rel_party_id
 ,x_rel_id => x_rel_id
 ,x_org_contact_id => x_org_contact_id
 ,x_party_site_id => x_party_site_id
 );
 IF
 g_return_status <> fnd_api.g_ret_sts_success
 THEN
 FOR v_index IN 1..fnd_msg_pub.count_msg LOOP
 fnd_msg_pub.get(
 p_msg_index => v_index
 ,p_encoded => 'F'
 ,p_data => g_msg_data
 ,p_msg_index_out => g_msg_index_out
 );
 g_msg_data := substr(
 g_msg_data
 ,1
 ,200
 );
 apps.fnd_file.put_line(
 apps.fnd_file.log
 ,'Error at Vendor site contacts :'
 || g_msg_data
 );
 ROLLBACK;
 END LOOP;
 ELSE
 apps.fnd_file.put_line(
 apps.fnd_file.log
 ,'Vendor site contact '
 || l_site_contacts.vendor_contact_first_name
 || ' '
 || l_site_contacts.vendor_contact_last_name
 || ' created for vendor: '
 || lv_vendor_number
 );
 END IF;
 ELSE
 apps.fnd_file.put_line(
 apps.fnd_file.log
 ,'Missing Information – Vendor contact details does not exist for vendor :'
 || lv_vendor_number
 || '..!'
 );
 END IF;
 END LOOP;
 CLOSE cur_stg_tab;
 END xxab_create_vendor_contact;
--------------------------------------------------------
-- * Procedure Name : xxab_create_vendor_tax
-- * Description : This Procedure creates the Supplier Tax
--------------------------------------------------------
 PROCEDURE xxab_create_vendor_tax (
 p_vendor_party_id IN NUMBER
 ) IS
 lv_party_tax_profile_id VARCHAR2(50);
 lv_tax_registration_number VARCHAR2(50);
 lv_vendor_number VARCHAR2(50);
 CURSOR cur_ven_tax IS SELECT party_tax_profile_id FROM zx_party_tax_profile WHERE party_id = p_vendor_party_id;
 BEGIN
 OPEN cur_ven_tax;
 FETCH cur_ven_tax INTO lv_party_tax_profile_id;
 CLOSE cur_ven_tax;
 zx_party_tax_profile_pkg.update_row(
 p_party_tax_profile_id => lv_party_tax_profile_id
 ,p_collecting_authority_flag => NULL
 ,p_provider_type_code => NULL
 ,p_create_awt_dists_type_code => NULL
 ,p_create_awt_invoices_type_cod => NULL
 ,p_tax_classification_code => NULL
 ,p_self_assess_flag => NULL
 ,p_allow_offset_tax_flag => NULL
 ,p_rep_registration_number => NULL
 ,p_effective_from_use_le => NULL
 ,p_record_type_code => NULL
 ,p_request_id => NULL
 ,p_attribute1 => NULL
 ,p_attribute2 => NULL
 ,p_attribute3 => NULL
 ,p_attribute4 => NULL
 ,p_attribute5 => NULL
 ,p_attribute6 => NULL
 ,p_attribute7 => NULL
 ,p_attribute8 => NULL
 ,p_attribute9 => NULL
 ,p_attribute10 => NULL
 ,p_attribute11 => NULL
 ,p_attribute12 => NULL
 ,p_attribute13 => NULL
 ,p_attribute14 => NULL
 ,p_attribute15 => NULL
 ,p_attribute_category => NULL
 ,p_party_id => NULL
 ,p_program_login_id => NULL
 ,p_party_type_code => NULL
 ,p_supplier_flag => NULL
 ,p_customer_flag => NULL
 ,p_site_flag => NULL
 ,p_process_for_applicability_fl => NULL
 ,p_rounding_level_code => NULL
 ,p_rounding_rule_code => 'NEAREST'
 ,p_withholding_saaat_date => NULL
 ,p_inclusive_tax_flag => NULL
 ,p_allow_awt_flag => NULL
 ,p_use_le_as_subscriber_flag => NULL
 ,p_legal_establishment_flag => NULL
 ,p_first_party_le_flag => NULL
 ,p_reporting_authority_flag => NULL
 ,x_return_status => g_return_status
 ,p_registration_type_code => NULL
 ,p_country_code => NULL
 );
 IF
 g_return_status <> fnd_api.g_ret_sts_success
 THEN
 FOR v_index IN 1..fnd_msg_pub.count_msg LOOP
 fnd_msg_pub.get(
 p_msg_index => v_index
 ,p_encoded => 'F'
 ,p_data => g_msg_data
 ,p_msg_index_out => g_msg_index_out
 );
 g_msg_data := substr(
 g_msg_data
 ,1
 ,200
 );
 dbms_output.put_line('Error at Vendor tax creation :'
 || g_msg_data);
 ROLLBACK;
 END LOOP;
 ELSE
 dbms_output.put_line('Tax details updated succesfully');
 END IF;
 END xxab_create_vendor_tax;
----------------------------------------------------------
-- * Procedure Name : xxab_bank_account_creation
-- * Description : This Procedure creates the Supplier bank
-- * account
----------------------------------------------------------
 PROCEDURE xxab_bank_account_creation (
 p_vendor_id IN NUMBER
 ) IS
 x_response iby_fndcpt_common_pub.result_rec_type;
 p_ext_bank_acct_rec iby_ext_bankacct_pub.extbankacct_rec_type;
 v_supplier_party_id NUMBER;
 x_acct_id NUMBER;
 p_count NUMBER;
 lv_supplier_number VARCHAR2(100);
 lv_supplier_name VARCHAR2(1000);
 ln_party_id NUMBER;
 ln_bank_party_id NUMBER;
 ln_branch_party_id NUMBER;
 CURSOR cur_sup_tab (
 p_vendor_id NUMBER
 ) IS SELECT segment1
 ,party_id
 ,vendor_name
 FROM ap_suppliers
 WHERE vendor_id = p_vendor_id;
 CURSOR cur_stg_tab (
 p_supplier_number VARCHAR2
 ,p_supplier_name VARCHAR2
 ) IS SELECT *
 FROM xxab_supp_stg_tab
 WHERE 1 = 1
 AND vendor_number = p_supplier_number
 AND vendor_name = p_supplier_name;
 l_vendor_bank cur_stg_tab%rowtype;
 BEGIN
 OPEN cur_sup_tab(p_vendor_id);
 FETCH cur_sup_tab INTO lv_supplier_number,ln_party_id,lv_supplier_name;
 CLOSE cur_sup_tab;
 OPEN cur_stg_tab(
 lv_supplier_number
 ,lv_supplier_name
 );
 FETCH cur_stg_tab INTO l_vendor_bank;
 IF
 ( l_vendor_bank.bank_name IS NOT NULL AND l_vendor_bank.bank_number IS NOT NULL ) AND ( l_vendor_bank.bank_account_number IS NOT NULL AND l_vendor_bank
.bank_account_name IS NOT NULL )
 THEN
 BEGIN
 SELECT bank_party_id INTO
 ln_bank_party_id
 FROM iby_ext_banks_v
 WHERE 1 = 1
 AND bank_name = l_vendor_bank.bank_name
 AND bank_number = l_vendor_bank.bank_number;
 EXCEPTION
 WHEN OTHERS THEN
 ln_bank_party_id := NULL;
 END;
 IF
 nvl(
 ln_bank_party_id
 ,1
 ) <> 1
 THEN
 BEGIN
 SELECT branch_party_id INTO
 ln_branch_party_id
 FROM iby_ext_bank_branches_v
 WHERE bank_party_id = ln_bank_party_id;
 EXCEPTION
 WHEN OTHERS THEN
 NULL;
 END;
 p_ext_bank_acct_rec.acct_owner_party_id := ln_party_id;
 p_ext_bank_acct_rec.bank_account_name := l_vendor_bank.bank_account_name;
 p_ext_bank_acct_rec.bank_account_num := l_vendor_bank.bank_account_number;
 p_ext_bank_acct_rec.bank_id := ln_bank_party_id;
 p_ext_bank_acct_rec.branch_id := ln_branch_party_id;
 p_ext_bank_acct_rec.saaat_date := SYSDATE;
 p_ext_bank_acct_rec.country_code := 'GB';
 p_ext_bank_acct_rec.currency := 'GBP';
 p_ext_bank_acct_rec.foreign_payment_use_flag := 'N';
 p_ext_bank_acct_rec.payment_factor_flag := 'N';
 iby_ext_bankacct_pub.create_ext_bank_acct(
 p_api_version => 1.0
 ,p_init_msg_list => fnd_api.g_true
 ,p_ext_bank_acct_rec => p_ext_bank_acct_rec
 ,x_acct_id => x_acct_id
 ,x_return_status => g_return_status
 ,x_msg_count => g_msg_count
 ,x_msg_data => g_msg_data
 ,x_response => x_response
 );
 IF
 g_return_status <> fnd_api.g_ret_sts_success
 THEN
 LOOP
 p_count := p_count + 1;
 g_msg_data := fnd_msg_pub.get(
 fnd_msg_pub.g_next
 ,fnd_api.g_false
 );
 IF
 g_msg_data IS NULL
 THEN
 EXIT;
 END IF;
 apps.fnd_file.put_line(
 apps.fnd_file.log
 ,'Error at bank account creation '
 || p_count
 || ' —'
 || g_msg_data
 );
 ROLLBACK;
 END LOOP;
 ELSE
 apps.fnd_file.put_line(
 apps.fnd_file.log
 ,'Vendor site bank account created for vendor: '
 || lv_supplier_number
 );
 END IF;
 ELSE
 apps.fnd_file.put_line(
 apps.fnd_file.log
 ,'Missing Information – Bank details provided are invalid for vendor: '
 || lv_supplier_number
 || '. Please enter manually in application..!'
 );
 END IF;
 ELSE
 apps.fnd_file.put_line(
 apps.fnd_file.log
 ,'Missing Information – Bank details are not provided for vendor: '
 || lv_supplier_number
 || '. Please enter manually in application..!'
 );
 END IF;
 END xxab_bank_account_creation;
----------------------------------------------------------
-- * Procedure Name : xxab_bank_account_site_assg
-- * Description : This Procedure assigns the Supplier bank
-- * account at Site level
----------------------------------------------------------
 PROCEDURE xxab_bank_account_site_assg (
 p_vendor_id IN NUMBER
 ) IS
--Setting payee Instrument Assignment varibales
 lr_payee_rec iby_disbursement_setup_pub.payeecontext_rec_type;
 lr_assg_attr iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
 ln_assg_id NUMBER;
 lr_response iby_fndcpt_common_pub.result_rec_type;
 ln_acct_id NUMBER;
 l_ext_bank_account_id NUMBER;
 lv_bank_name VARCHAR2(200);
 lv_bank_number VARCHAR2(200);
 ln_bank_account_number VARCHAR2(200);
 CURSOR cur_sup_tab (
 p_vendor_id NUMBER
 ) IS SELECT a.party_id
 ,b.org_id
 ,b.party_site_id
 ,b.vendor_site_id
 ,a.segment1
 ,a.vendor_name
 FROM ap_suppliers a
 ,ap_supplier_sites_all b
 WHERE a.vendor_id = p_vendor_id
 AND a.vendor_id = b.vendor_id;
 l_vendor_bank_acnt cur_sup_tab%rowtype;
 BEGIN
 OPEN cur_sup_tab(p_vendor_id);
 FETCH cur_sup_tab INTO l_vendor_bank_acnt;
 BEGIN
 SELECT bank_name
 ,bank_number
 ,bank_account_number INTO
 lv_bank_name,lv_bank_number,ln_bank_account_number
 FROM xxab_supp_stg_tab
 WHERE 1 = 1
 AND vendor_number = l_vendor_bank_acnt.segment1
 AND vendor_name = l_vendor_bank_acnt.vendor_name
 AND ROWNUM = 1;
 EXCEPTION
 WHEN OTHERS THEN
 NULL;
 END;
 IF
 lv_bank_name IS NOT NULL AND lv_bank_number IS NOT NULL AND ln_bank_account_number IS NOT NULL
 THEN
 BEGIN
 SELECT ext_bank_account_id INTO
 l_ext_bank_account_id
 FROM iby_ext_bank_accounts
 WHERE bank_id = ( SELECT bank_party_id
 FROM iby_ext_banks_v
 WHERE 1 = 1
 AND bank_name = lv_bank_name
 AND bank_number = lv_bank_number
 AND bank_account_num = ln_bank_account_number
 );
 EXCEPTION
 WHEN OTHERS THEN
 NULL;
 END;
 lr_payee_rec.payment_function := 'PAYABLES_DISB';
 lr_payee_rec.party_id := l_vendor_bank_acnt.party_id;
 lr_payee_rec.org_type := 'OPERATING_UNIT';
 lr_payee_rec.org_id := l_vendor_bank_acnt.org_id;
 lr_payee_rec.party_site_id := l_vendor_bank_acnt.party_site_id;
 lr_payee_rec.supplier_site_id := l_vendor_bank_acnt.vendor_site_id;
 lr_assg_attr.instrument.instrument_type := 'BANKACCOUNT';
 lr_assg_attr.instrument.instrument_id := l_ext_bank_account_id;
 lr_assg_attr.priority := 1;
 lr_assg_attr.saaat_date := SYSDATE;
 lr_assg_attr.end_date := NULL;
 iby_disbursement_setup_pub.set_payee_instr_assignment(
 p_api_version => 1.0
 ,p_init_msg_list => fnd_api.g_true
 ,p_commit => fnd_api.g_true
 ,x_return_status => g_return_status
 ,x_msg_count => g_msg_count
 ,x_msg_data => g_msg_data
 ,p_payee => lr_payee_rec
 ,p_assignment_attribs => lr_assg_attr
 ,x_assign_id => ln_assg_id
 ,x_response => lr_response
 );
 IF
 g_msg_count = 0
 THEN
 apps.fnd_file.put_line(apps.fnd_file.log,'Vendor site bank account assigned to vendor :'|| l_vendor_bank_acnt.segment1);
 END IF;
 END IF;
 CLOSE cur_sup_tab;
 EXCEPTION
 WHEN OTHERS THEN
 apps.fnd_file.put_line(
 apps.fnd_file.log
 ,'Error in SET_PAYEE_INSTR_ASSIGNMENT: '
 || sqlerrm
 );
 END xxab_bank_account_site_assg;
END xxab_supplier_conversion;
/*
—————————————————————————————————————–
Alternate approach of Supplier creation through interface tables:
Supplier Creation
Maintain Data in Excel and upload it in interfaces table as mentioned below either using SQL Loader or WinSCP
Supplier Interface Table: AP_SUPPLIERS_INT
Mandatory Columns: VENDOR_INTERFACE_ID & VENDOR_NAME
Once data is uploaded in staging tables, use below mentioned requests to upload it
Request Name for Supplier: Supplier Open Interface Import
Supplier Site Creation
Supplier Site Interface Table: AP_SUPPLIER_SITES_INT
Mandatory Columns: VENDOR_SITE_INTERFACE_ID, VENDOR_SITE_CODE & VENDOR_ID
(Vendor ID created in AP_SUPPLIERS table after submission of Supplier Open Interface Import request.)
Once data is uploaded in staging tables, use below mentioned requests to upload it
Request Name for Supplier: Supplier Open Interface Import
Request Name for Supplier Sites: Supplier Sites Open Interface Import*/

 


3. Customer creation

create or replace package BODY xxar_xx_create_customer AS
/************************************************************************
* TYPE : Package *
* NAME : xxar_xx_create_customer *
* INPUT Parameters : operatting unit *
* OUTPUT Parametrs : out_errbuf,out_retcode *
* PURPOSE : xxtar and xx Customer Creation *
* *
* Author Date Ver Description *
* ——– ———- —- ————————————- *
* ABC 25/05/2018 1.0 Initial *
************************************************************************/
--------------------------------
-- Global Variable Declaration —
--------------------------------
g_return_status VARCHAR2(2000);
g_msg_count NUMBER;
g_msg_data VARCHAR2(2000);
--------------------------------
-- * Procedure Name : CREATE_CUST_ACCOUNT
-- * Description : This main Procedure creates the customer
-- * account and call procedures internally to
-- * create location and customer sites
--------------------------------
PROCEDURE create_cust_account(x_errbuff OUT VARCHAR2,
x_retcode OUT VARCHAR2,
p_operating_unit IN VARCHAR2) is
--------------------------------
-- Local Variable Declaration —
--------------------------------
l_cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
l_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
l_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
l_cust_profile_amt_rec HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE;
l_error_flag VARCHAR2(2000) := 'N';
l_error_status VARCHAR2(2000) := 'N';
l_child_status VARCHAR2(2000) := 'SUCCESS';
l_location_id hz_locations.location_id%type;
l_party_site_id hz_party_sites.party_site_id%type;
l_cust_acc_site_id HZ_CUST_ACCT_SITES_ALL.cust_acct_site_id%type;
l_site_use_id HZ_CUST_SITE_USES_ALL.site_use_id%type;
l_collector_id ar_collectors.collector_id%type;
l_application_id fnd_application.application_id%type;
l_responsibility_id fnd_responsibility.responsibility_id%type;
l_cust_account_id hz_cust_accounts.cust_account_id%type;
l_account_number hz_cust_accounts.account_number%type;
l_party_id hz_parties.party_id%type;
l_party_number hz_parties.party_number%type;
l_profile_id NUMBER;
l_profile_class_id NUMBER;
l_status varchar2(2000);
l_msg_count varchar2(2000);
l_msg_data varchar2(2000);
l_cust_act_prof_amt_id varchar2(2000);
l_cust_account_profile_id NUMBER(15);
l_customer_count NUMBER;

--Cursor to get customer data
CURSOR cur_data_file(p_operating_unit IN VARCHAR2) IS
SELECT DISTINCT PARTY_NAME,
ACCOUNT_NAME,
PAYMENT_TERM_ID,
SITE_USE_CODE,
NVL(IDENTIFYING_ADDRESS_FLAG, 'Y') IDENTIFYING_ADDRESS_FLAG,
COLLECTOR,
credit_rating,
credit_classification,
review_cycle,
account_status,
NVL(CREATED_BY_MODULE, 'HZ_CPUI') CREATED_BY_MODULE,
PROFILE_CLASS,
currency_code,
overall_credit_limit,
NVL(min_dunning_amount, '25') min_dunning_amount,
NVL(min_dunning_invoice_amount, '25') min_dunning_invoice_amount,
NVL(min_statement_amount, '25') min_statement_amount,
BU
FROM xxAR_xx_CUSTOMER
WHERE DECODE(BU, 'xx', 'xx', 'xx', 'xx', 'xx', 'xx') =
p_operating_unit;
--Cursor to get contact details
CURSOR cur_create_contact(l_party_name in VARCHAR2) IS
SELECT DISTINCT PARTY_NAME, first_name, last_name
FROM xxAR_xx_CUSTOMER
WHERE party_name = l_party_name
AND DECODE(BU, 'xx', 'xx', 'xx', 'xx', 'xx', 'xx') =
p_operating_unit;
--Cursor to get address details
CURSOR cur_address_data(l_party_name in VARCHAR2) IS
SELECT DISTINCT PARTY_NAME,
ADDRESS1,
ADDRESS2,
ADDRESS3,
CITY,
COUNTRY,
POSTAL_CODE
FROM xxAR_xx_CUSTOMER
WHERE party_name = l_party_name
AND DECODE(BU, 'xx', 'xx', 'xx', 'xx', 'xx', 'xx') =
p_operating_unit;
BEGIN
IF p_operating_unit NOT IN ('xx','xx') THEN
x_retcode := '2';
apps.fnd_file.put_line(apps.fnd_file.log,'Error: Invalid Operating Unit');
END IF;
BEGIN
SELECT application_id
INTO l_application_id
FROM fnd_application
WHERE application_short_name LIKE 'AR';
EXCEPTION
WHEN OTHERS THEN
x_retcode := '2';
apps.fnd_file.put_line(apps.fnd_file.log,'Error: Unable to fetch application id');
apps.fnd_file.put_line(apps.fnd_file.log, 'Error: ' || SQLERRM);
END;
--Get values for new customer
FOR rec_data_file IN cur_data_file(p_operating_unit) LOOP
EXIT WHEN cur_data_file%NOTFOUND;
l_error_flag := 'N';
l_child_status := 'SUCCESS';
apps.fnd_file.put_line(apps.fnd_file.log,'*********************************************************************');
apps.fnd_file.put_line(apps.fnd_file.log,'Customer: ' || rec_data_file.Party_name);

BEGIN
SELECT COUNT(1)
INTO l_customer_count
FROM hz_parties
WHERE party_name = rec_data_file.Party_name;
EXCEPTION
WHEN OTHERS THEN
l_error_flag := 'Y';
apps.fnd_file.put_line(apps.fnd_file.log,'Error: While checking duplicate customer');
apps.fnd_file.put_line(apps.fnd_file.log, 'Error: ' || SQLERRM);
END;
IF l_customer_count > 0 THEN
l_error_flag := 'Y';
apps.fnd_file.put_line(apps.fnd_file.log,'Error: Customer with this name already exists');
END IF;
IF rec_data_file.collector IS NOT NULL THEN
BEGIN
SELECT collector_id
INTO l_collector_id
FROM ar_collectors
WHERE name = rec_data_file.collector;
EXCEPTION
WHEN OTHERS THEN
l_error_flag := 'Y';
apps.fnd_file.put_line(apps.fnd_file.log,'Error: Unable to fetch collector id');
apps.fnd_file.put_line(apps.fnd_file.log, 'Error: ' || SQLERRM);
END;
END IF;
IF rec_data_file.profile_class IS NOT NULL THEN
BEGIN
SELECT profile_class_id
INTO l_profile_class_id
FROM HZ_CUST_PROFILE_CLASSES
WHERE name = rec_data_file.profile_class;
EXCEPTION
WHEN OTHERS THEN
l_error_flag := 'Y';
apps.fnd_file.put_line(apps.fnd_file.log,'Error: Unable to fetch profile class');
apps.fnd_file.put_line(apps.fnd_file.log, 'Error: ' || SQLERRM);
END;
END IF;
l_cust_account_rec.created_by_module := rec_data_file.created_by_module;
l_cust_account_rec.customer_type := 'R';
l_cust_account_rec.account_name := rec_data_file.account_name;
l_organization_rec.organization_name := rec_data_file.Party_name;
l_organization_rec.created_by_module := rec_data_file.created_by_module;
l_customer_profile_rec.profile_class_id := l_profile_class_id;
l_customer_profile_rec.created_by_module := rec_data_file.created_by_module;
l_customer_profile_rec.collector_id := l_collector_id;
l_customer_profile_rec.standard_terms := rec_data_file.payment_term_id;
l_customer_profile_rec.application_id := l_application_id;
l_customer_profile_rec.status := 'A';
l_customer_profile_rec.credit_rating := rec_data_file.credit_rating;
l_customer_profile_rec.credit_classification := rec_data_file.credit_classification;
l_customer_profile_rec.review_cycle := rec_data_file.review_cycle;
l_customer_profile_rec.account_status := rec_data_file.account_status;

--Calling API to create customer account
IF l_error_flag = 'N' THEN
BEGIN
hz_cust_account_v2pub.create_cust_account(p_init_msg_list => 'T',
p_cust_account_rec => l_cust_account_rec,
p_organization_rec => l_organization_rec,
p_customer_profile_rec => l_customer_profile_rec,
p_create_profile_amt => 'F',
x_cust_account_id => l_cust_account_id,
x_account_number => l_account_number,
x_party_id => l_party_id,
x_party_number => l_party_number,
x_profile_id => l_profile_id,
x_return_status => g_return_status,
x_msg_count => g_msg_count,
x_msg_data => g_msg_data);

BEGIN
SELECT cust_account_profile_id
INTO l_cust_account_profile_id
FROM HZ_CUSTOMER_PROFILES
WHERE cust_account_id = l_cust_account_id;
apps.fnd_file.put_line(apps.fnd_file.log,'l_cust_account_id: ' ||l_cust_account_id);
apps.fnd_file.put_line(apps.fnd_file.log,'l_cust_account_profile_id: ' ||l_cust_account_profile_id);
EXCEPTION
WHEN OTHERS THEN
l_error_flag := 'Y';
apps.fnd_file.put_line(apps.fnd_file.log,'Error: Unable to fetch Customer account profile id');
apps.fnd_file.put_line(apps.fnd_file.log,'Error: ' || SQLERRM);
END;
IF g_return_status = fnd_api.g_ret_sts_success AND
l_error_flag = 'N' THEN
apps.fnd_file.put_line(apps.fnd_file.log,'Party Id : ' || l_party_id);
apps.fnd_file.put_line(apps.fnd_file.log,'Party Number : ' || l_party_number);
apps.fnd_file.put_line(apps.fnd_file.log,'Customer Account Created Sucessfully');

--Create Profile Amount
l_cust_profile_amt_rec.cust_account_profile_id := l_cust_account_profile_id;
l_cust_profile_amt_rec.cust_account_id := l_cust_account_id;
l_cust_profile_amt_rec.currency_code := rec_data_file.currency_code;
l_cust_profile_amt_rec.overall_credit_limit := rec_data_file.overall_credit_limit;
l_cust_profile_amt_rec.min_dunning_amount := rec_data_file.min_dunning_amount;
l_cust_profile_amt_rec.min_statement_amount := rec_data_file.min_statement_amount;
l_cust_profile_amt_rec.min_dunning_invoice_amount := rec_data_file.min_dunning_invoice_amount;
l_cust_profile_amt_rec.created_by_module := rec_data_file.created_by_module;

HZ_CUSTOMER_PROFILE_V2PUB.create_cust_profile_amt(p_init_msg_list => 'T',
p_check_foreign_key => FND_API.G_TRUE,
p_cust_profile_amt_rec => l_cust_profile_amt_rec,
x_cust_acct_profile_amt_id => l_cust_act_prof_amt_id,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);

IF l_status = fnd_api.g_ret_sts_success THEN
apps.fnd_file.put_line(apps.fnd_file.log,'Customer Profile Amount Created Sucessfully');
ELSE
l_error_flag := 'Y';
l_error_status := 'Y';
apps.fnd_file.put_line(apps.fnd_file.log,'ERROR: Customer Profile Amount not created');
apps.fnd_file.put_line(apps.fnd_file.log,'ERROR: ' || l_msg_data);
END IF;
ELSE
l_error_flag := 'Y';
l_error_status := 'Y';
apps.fnd_file.put_line(apps.fnd_file.log,'Error: Unable to create Customer ' ||
rec_data_file.Party_name);
apps.fnd_file.put_line(apps.fnd_file.log,'Error: ' || g_msg_data);

IF g_msg_count > 1 THEN
FOR I IN 1 .. g_msg_count LOOP
apps.fnd_file.put_line(apps.fnd_file.log, I || '.' || SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),
1,255));
END LOOP;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
l_error_flag := 'Y';
l_error_status := 'Y';
apps.fnd_file.put_line(apps.fnd_file.log,'Error in Creating Customer: ' ||
SQLERRM);
END;
END IF;
IF g_return_status = fnd_api.g_ret_sts_success AND l_error_flag = 'N' THEN
FOR rec_address_data IN cur_address_data(rec_data_file.party_name) LOOP
EXIT WHEN cur_address_data%NOTFOUND;
apps.fnd_file.put_line(apps.fnd_file.log, 'Calling the procedure to create location');
create_location(rec_address_data.country,
rec_address_data.address1,
rec_address_data.address2,
rec_address_data.address3,
rec_address_data.city,
rec_address_data.postal_code,
rec_data_file.created_by_module,
l_child_status,
l_location_id);
IF l_child_status != 'ERROR' AND l_error_flag = 'N' THEN
apps.fnd_file.put_line(apps.fnd_file.log,
'Calling the procedure to create party site');
create_party_site(l_party_id,
l_location_id,
rec_data_file.identifying_address_flag,
rec_data_file.created_by_module,
l_application_id,
l_child_status,
l_party_site_id);

IF l_child_status != 'ERROR' AND l_error_flag = 'N' THEN
apps.fnd_file.put_line(apps.fnd_file.log, 'Calling the procedure to create customer account site');

create_cust_acct_site(l_cust_account_id,
l_party_site_id,
rec_data_file.created_by_module,
l_child_status,
l_cust_acc_site_id);

IF l_child_status != 'ERROR' AND l_error_flag = 'N' THEN
apps.fnd_file.put_line(apps.fnd_file.log,
'Calling the procedure to create customer account site use');
create_cust_site_use(l_cust_acc_site_id,
'BILL_TO',
rec_data_file.created_by_module,
l_child_status,
l_site_use_id);
create_cust_site_use(l_cust_acc_site_id,
'INV',
rec_data_file.created_by_module,
l_child_status,
l_site_use_id);
IF l_child_status = 'ERROR' AND l_error_flag = 'N' THEN
l_error_status := 'Y';
l_error_flag := 'Y';
apps.fnd_file.put_line(apps.fnd_file.log,
'Error: Unable to create Customer Site Use');
END IF;
ELSE
l_error_status := 'Y';
l_error_flag := 'Y';
apps.fnd_file.put_line(apps.fnd_file.log,
'Error: Unable to create Customer Account Site');
END IF;
ELSE
l_error_status := 'Y';
l_error_flag := 'Y';
apps.fnd_file.put_line(apps.fnd_file.log,
'Error: Unable to create Party Site');
END IF;
ELSE
l_error_status := 'Y';
l_error_flag := 'Y';
apps.fnd_file.put_line(apps.fnd_file.log,
'Error: Unable to create Location');
END IF;
END LOOP;
END IF;
--create person
IF l_child_status != 'ERROR' AND l_error_flag = 'N' THEN
FOR rec_create_contact IN cur_create_contact(rec_data_file.party_name) LOOP
EXIT WHEN cur_create_contact%NOTFOUND;
IF rec_create_contact.first_name IS NOT NULL OR
rec_create_contact.last_name IS NOT NULL THEN
apps.fnd_file.put_line(apps.fnd_file.log,
'Calling procedure to create customer contact details');
create_person(rec_create_contact.first_name,
rec_create_contact.last_name,
rec_data_file.created_by_module,
l_party_id,
l_cust_account_id,
l_child_status);
IF l_child_status = 'ERROR' THEN
l_error_status := 'Y';
l_error_flag := 'Y';
apps.fnd_file.put_line(apps.fnd_file.log,
'Error: Unable to create customer contact');
END IF;
END IF;
END LOOP;
ELSE
l_error_status := 'Y';
END IF;
IF l_error_flag = 'Y' THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END LOOP;
apps.fnd_file.put_line(apps.fnd_file.log,
'*********************************************************************');
IF l_error_status = 'Y' THEN
apps.fnd_file.put_line(apps.fnd_file.log,
'ERROR: One or more customer has not been created due to errors');
ELSE
apps.fnd_file.put_line(apps.fnd_file.log,
'SUCCESS: All Customers have been created successfully');
END IF;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.log, 'Error: ' || SQLERRM);
END create_cust_account;
----------------------------
-- * Procedure Name : CREATE_LOCATION
-- * Description : This Procedure creates the Location
----------------------------
PROCEDURE create_location(p_country IN VARCHAR2,
p_address1 IN VARCHAR2,
p_address2 IN VARCHAR2,
p_address3 IN VARCHAR2,
p_city IN VARCHAR2,
p_postal_code IN VARCHAR2,
p_created_by_module IN VARCHAR2,
p_return OUT VARCHAR2,
p_location_id OUT VARCHAR2) is
l_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
l_location_id hz_locations.location_id%type;
BEGIN
l_location_rec.country := p_country;
l_location_rec.address1 := p_address1;
l_location_rec.address2 := p_address2;
l_location_rec.address3 := p_address3;
l_location_rec.city := p_city;
l_location_rec.postal_code := p_postal_code;
l_location_rec.created_by_module := p_created_by_module;
apps.fnd_file.put_line(apps.fnd_file.log,'Calling the API hz_location_v2pub.create_location');

BEGIN

HZ_LOCATION_V2PUB.CREATE_LOCATION(p_init_msg_list => FND_API.G_TRUE,
p_location_rec => l_location_rec,
x_location_id => p_location_id,
x_return_status => g_return_status,
x_msg_count => g_msg_count,
x_msg_data => g_msg_data);
IF g_return_status = fnd_api.g_ret_sts_success THEN
p_return := 'SUCCESS';
apps.fnd_file.put_line(apps.fnd_file.log,
'Creation of Location is Successful');
apps.fnd_file.put_line(apps.fnd_file.log,
'Location Id: ' || p_location_id);
ELSE
apps.fnd_file.put_line(apps.fnd_file.log, 'Error: Creation of Location failed: ' ||
g_msg_data);
p_return := 'ERROR';
FOR i IN 1 .. g_msg_count LOOP
g_msg_data := oe_msg_pub.get(p_msg_index => i, p_encoded => 'F');
apps.fnd_file.put_line(apps.fnd_file.log,
i || ') ' || g_msg_data);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_return := 'ERROR';
apps.fnd_file.put_line(apps.fnd_file.log,
'Error in location: ' || SQLERRM);
END;
END create_location;
----------------------------------------
-- * Procedure Name : CREATE_PARTY_SITE
-- * Description : This Procedure creates the party sites for the customer
----------------------------------------
PROCEDURE create_party_site(p_party_id IN VARCHAR2,
p_location_id IN VARCHAR2,
p_identifying_address_flag IN VARCHAR2,
p_created_by_module IN VARCHAR2,
p_application_id IN VARCHAR2,
p_return OUT VARCHAR2,
p_party_site_id OUT VARCHAR2) is
l_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
l_party_site_id hz_party_sites.party_site_id%type;
l_party_site_number hz_party_sites.party_site_number%type;
BEGIN
l_party_site_rec.party_id := p_party_id;
l_party_site_rec.location_id := p_location_id;
l_party_site_rec.identifying_address_flag := p_identifying_address_flag;
l_party_site_rec.created_by_module := p_created_by_module;
l_party_site_rec.status := 'A';
l_party_site_rec.application_id := p_application_id;
apps.fnd_file.put_line(apps.fnd_file.log,
'Calling the API hz_party_site_v2pub.create_party_site');
BEGIN
HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE(p_init_msg_list => FND_API.G_TRUE,
p_party_site_rec => l_party_site_rec,
x_party_site_id => p_party_site_id,
x_party_site_number => l_party_site_number,
x_return_status => g_return_status,
x_msg_count => g_msg_count,
x_msg_data => g_msg_data);
IF g_return_status = fnd_api.g_ret_sts_success THEN
p_return := 'SUCCESS';
apps.fnd_file.put_line(apps.fnd_file.log,
'Creation of Party Site is Successful');
apps.fnd_file.put_line(apps.fnd_file.log,
'Party Site Id: ' || p_party_site_id);
ELSE
p_return := 'ERROR';
apps.fnd_file.put_line(apps.fnd_file.log,
'ERROR: Creation of Party Site failed: ' ||
g_msg_data);
FOR i IN 1 .. g_msg_count LOOP
g_msg_data := fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F');
apps.fnd_file.put_line(apps.fnd_file.log,
i || ') ' || g_msg_data);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_return := 'ERROR';
apps.fnd_file.put_line(apps.fnd_file.log, 'Error: ' || SQLERRM);
END;
END create_party_site;
--------------------------------
-- * Procedure Name : CREATE_CUST_ACCT_SITE
-- * Description : This Procedure creates the customer site account
--------------------------------
PROCEDURE create_cust_acct_site(p_cust_account_id IN VARCHAR2,
p_party_site_id IN VARCHAR2,
p_created_by_module IN VARCHAR2,
p_return OUT VARCHAR2,
p_cust_acc_site_id OUT VARCHAR2) is
l_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
BEGIN
l_cust_acct_site_rec.cust_account_id := p_cust_account_id;
l_cust_acct_site_rec.party_site_id := p_party_site_id;
l_cust_acct_site_rec.created_by_module := p_created_by_module;
l_cust_acct_site_rec.territory := 'UK Default';
l_cust_acct_site_rec.territory_id := '1001';
l_cust_acct_site_rec.status := 'A';
apps.fnd_file.put_line(apps.fnd_file.log,
'Calling the API hz_cust_account_site_v2pub.create_cust_acct_site');
BEGIN
hz_cust_account_site_v2pub.create_cust_acct_site('T',
l_cust_acct_site_rec,
p_cust_acc_site_id,
g_return_status,
g_msg_count,
g_msg_data);
IF g_return_status = fnd_api.g_ret_sts_success THEN
p_return := 'SUCCESS';
apps.fnd_file.put_line(apps.fnd_file.log,
'Creation of Customer Account Site is Successful');
apps.fnd_file.put_line(apps.fnd_file.log,
'Customer Account Site Id is = ' ||
TO_CHAR(p_cust_acc_site_id));
ELSE
p_return := 'ERROR';
apps.fnd_file.put_line(apps.fnd_file.log,
'ERROR: Creation of Customer Account Site: ' ||
g_msg_data);
IF g_msg_count > 1 THEN
FOR I IN 1 .. g_msg_count LOOP
apps.fnd_file.put_line(apps.fnd_file.log,
I || '.' || SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),
1,
255));
END LOOP;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_return := 'ERROR';
apps.fnd_file.put_line(apps.fnd_file.log,
'Error in Creating Customer Account Site: ' ||
SQLERRM);
END;
END create_cust_acct_site;
--------------------------------
-- * Procedure Name : CREATE_CUST_SITE_USE
-- * Description : This Procedure creates the customer site uses
--------------------------------
PROCEDURE create_cust_site_use(p_cust_acct_site_id IN VARCHAR2,
p_site_use_code IN VARCHAR2,
p_created_by_module IN VARCHAR2,
p_return OUT VARCHAR2,
p_site_use_id OUT VARCHAR2) is
l_cust_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE;
l_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
l_site_use_id HZ_CUST_SITE_USES_ALL.site_use_id%type;
BEGIN
l_cust_site_use_rec.cust_acct_site_id := p_cust_acct_site_id;
l_cust_site_use_rec.site_use_code := p_site_use_code;
l_cust_site_use_rec.created_by_module := p_created_by_module;
l_cust_site_use_rec.primary_flag := 'Y';
apps.fnd_file.put_line(apps.fnd_file.log,
'Calling the API hz_cust_account_site_v2pub.create_cust_site_use');
BEGIN
hz_cust_account_site_v2pub.create_cust_site_use(p_init_msg_list => 'T',
p_cust_site_use_rec => l_cust_site_use_rec,
p_customer_profile_rec => l_customer_profile_rec,
p_create_profile => ”,
p_create_profile_amt => ”,
x_site_use_id => p_site_use_id,
x_return_status => g_return_status,
x_msg_count => g_msg_count,
x_msg_data => g_msg_data);
IF g_return_status = fnd_api.g_ret_sts_success THEN
p_return := 'SUCCESS';
apps.fnd_file.put_line(apps.fnd_file.log,
'Creation of Customer Account Site Use is Successful');
apps.fnd_file.put_line(apps.fnd_file.log,
'Site Use Id = ' || TO_CHAR(p_site_use_id));
ELSE
p_return := 'ERROR';
apps.fnd_file.put_line(apps.fnd_file.log,
'ERROR: Creation of Customer Account Site Use: ' ||
g_msg_data);
IF g_msg_count > 1 THEN
FOR I IN 1 .. g_msg_count LOOP
apps.fnd_file.put_line(apps.fnd_file.log,
I || '.' || SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),
1,
255));
END LOOP;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_return := 'ERROR';
apps.fnd_file.put_line(apps.fnd_file.log, 'Error: ' || SQLERRM);
END;
END create_cust_site_use;
--------------------------------
-- * Procedure Name : create_person
-- * Description : This Procedure creates the contact person and party relation
--------------------------------
PROCEDURE create_person(p_person_first_name IN VARCHAR2,
p_person_last_name IN VARCHAR2,
p_created_by_module IN VARCHAR2,
p_party_id IN VARCHAR2,
p_cust_acc_id IN VARCHAR2,
p_return OUT VARCHAR2) is
l_person_rec hz_party_v2pub.person_rec_type;
l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
l_phone_rec hz_contact_point_v2pub.phone_rec_type;
l_party_id hz_parties.party_id%type;
l_party_number hz_parties.party_number%type;
l_profile_id HZ_CUSTOMER_PROFILES.cust_account_profile_id%type;
l_org_contact_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
l_org_contact_id NUMBER;
l_party_rel_id NUMBER;
l_rel_party_id NUMBER;
l_rel_party_number VARCHAR2(2000);
l_cr_cust_acc_role_rec hz_cust_account_role_v2pub.cust_account_role_rec_type;
l_cust_account_role_id VARCHAR2(2000);
l_contact_point_party_id hz_contact_points.contact_point_id%type;
l_status varchar2(2000);
l_msg_count varchar2(2000);
l_msg_data varchar2(2000);
BEGIN
l_person_rec.person_first_name := p_person_first_name;
l_person_rec.person_last_name := p_person_last_name;
l_person_rec.created_by_module := p_created_by_module;
apps.fnd_file.put_line(apps.fnd_file.log,
'Calling the API HZ_PARTY_V2PUB.CREATE_PERSON');
HZ_PARTY_V2PUB.CREATE_PERSON(p_init_msg_list => 'T',
p_person_rec => l_person_rec,
x_party_id => l_party_id,
x_party_number => l_party_number,
x_profile_id => l_profile_id,
x_return_status => g_return_status,
x_msg_count => g_msg_count,
x_msg_data => g_msg_data);
IF g_return_status = fnd_api.g_ret_sts_success THEN
p_return := 'SUCCESS';
apps.fnd_file.put_line(apps.fnd_file.log,
'Creation of Customer Contact Person is Successful');
apps.fnd_file.put_line(apps.fnd_file.log,
'Person Party Id = ' || TO_CHAR(l_party_id));
--Call create org contact
l_org_contact_rec.created_by_module := p_created_by_module;
l_org_contact_rec.party_rel_rec.subject_id := l_party_id; --Person party id
l_org_contact_rec.party_rel_rec.subject_type := ('PERSON');
l_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
l_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
l_org_contact_rec.party_rel_rec.object_id := p_party_id; --Organization Party Id
l_org_contact_rec.party_rel_rec.object_type := ('ORGANIZATION');
l_org_contact_rec.party_rel_rec.relationship_code := ('CONTACT_OF'); --EMPLOYEE_OF
l_org_contact_rec.party_rel_rec.relationship_type := ('CONTACT'); --EMPLOYEE
l_org_contact_rec.party_rel_rec.relationship_type := ('CONTACT');
l_org_contact_rec.party_rel_rec.start_date := SYSDATE;
apps.fnd_file.put_line(apps.fnd_file.log,
'Calling the API hz_party_contact_v2pub.create_org_contact');
hz_party_contact_v2pub.create_org_contact(p_init_msg_list => 'T',
p_org_contact_rec => l_org_contact_rec,
x_org_contact_id => l_org_contact_id,
x_party_rel_id => l_party_rel_id,
x_party_id => l_rel_party_id,
x_party_number => l_rel_party_number,
x_return_status => g_return_status,
x_msg_count => g_msg_count,
x_msg_data => g_msg_data);
IF g_return_status = fnd_api.g_ret_sts_success THEN
p_return := 'SUCCESS';
apps.fnd_file.put_line(apps.fnd_file.log,'Creation of Customer party relation is Successful');
l_cr_cust_acc_role_rec.party_id := l_rel_party_id;
l_cr_cust_acc_role_rec.cust_account_id := p_cust_acc_id;
l_cr_cust_acc_role_rec.role_type := 'CONTACT';
l_cr_cust_acc_role_rec.created_by_module := p_created_by_module;
apps.fnd_file.put_line(apps.fnd_file.log, 'Calling the API hz_cust_account_role_v2pub.create_cust_account_role');
hz_cust_account_role_v2pub.create_cust_account_role('T',
l_cr_cust_acc_role_rec,
l_cust_account_role_id,
g_return_status,
g_msg_count,
g_msg_data);
ELSE
p_return := 'ERROR';
apps.fnd_file.put_line(apps.fnd_file.log,
'Error: Creation of Customer party relation: ' ||
SUBSTR(g_msg_data, 1, 255));
IF g_msg_count > 1 THEN
FOR I IN 1 .. g_msg_count LOOP
apps.fnd_file.put_line(apps.fnd_file.log,
I || '.' || SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),
1,
255));
END LOOP;
END IF;
END IF;
ELSE
p_return := 'ERROR';
apps.fnd_file.put_line(apps.fnd_file.log,'ERROR: Creation of Customer Contact' ||g_msg_data);

IF g_msg_count > 1 THEN
FOR I IN 1 .. g_msg_count LOOP
apps.fnd_file.put_line(apps.fnd_file.log, I || '.' || SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),1,255));
END LOOP;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_return := 'ERROR';
apps.fnd_file.put_line(apps.fnd_file.log,'Error in Creating Customer Contact: ' ||SQLERRM);
END create_person;
END xxar_xx_create_customer;

4. HRMS API’s for data anonymization/ masking/ scrambling

DECLARE
 CURSOR cur_name IS SELECT * FROM dba_objects;
 TYPE lv_cur_name_typ IS
 TABLE OF cur_name%rowtype;
 lv_cur_name_tab lv_cur_name_typ := lv_cur_name_typ ();
 ln_api_error NUMBER;
 lv_error EXCEPTION;
BEGIN
 OPEN cur_name;
 LOOP
 FETCH cur_name BULK COLLECT INTO lv_cur_name_tab LIMIT 200;
 EXIT WHEN lv_cur_name_tab.count = 0;
 FOR i IN 1..lv_cur_name_tab.count LOOP
 BEGIN
 BEGIN
 FOR j IN ( SELECT * FROM all_objects
 ) LOOP
--API call or action
 NULL;
 END LOOP;
 EXCEPTION
 WHEN OTHERS THEN
 RAISE lv_error;
 ln_api_error := ln_api_error + 1;
 END;
 BEGIN
 FOR j IN ( SELECT * FROM all_objects
 ) LOOP
--API call or action
 NULL;
 END LOOP;
 EXCEPTION
 WHEN OTHERS THEN
 RAISE lv_error;
 ln_api_error := ln_api_error + 1;
 END;
 BEGIN
 FOR j IN ( SELECT * FROM all_objects
 ) LOOP
--API call or action
 NULL;
 END LOOP;
 EXCEPTION
 WHEN OTHERS THEN
 RAISE lv_error;
 ln_api_error := ln_api_error + 1;
 END;
 EXCEPTION
 WHEN lv_error THEN
 ROLLBACK;
 END;
 END LOOP;
 END LOOP;
 CLOSE cur_name;
EXCEPTION
 WHEN OTHERS THEN
 sqlerrm;
END;
CREATE OR REPLACE PACKAGE BODY xxab_hrms_data_anonymization AS
/***********************************************************************************
* TYPE : Package Body *
* NAME : xxab_hrms_data_anonymization *
* PURPOSE : HRMS Data Anonymization *
* Author Date Ver Description *
* --------- ----------------- ---- --------------------------------- *
* ABC 27-May-2019 1.0 Initial *
**********************************************************************************/
-------------------------------------------------------------------------------------------------------
-- * Procedure Name : xxab_hrms_data_scrambling
-- * INPUT Parameters : p_from_address, p_to_address
-- * OUTPUT Parametrs : p_errbuf, p_retcode
-- * Description : This Procedure anonymize the fields of Employees
-- * Address, Phone numbers, Payment Method and
-- * Elements Entry Values
-------------------------------------------------------------------------------------------------------
 PROCEDURE xxab_hrms_data_scrambling (
 p_errbuf OUT NOCOPY VARCHAR2
 ,p_retcode OUT NOCOPY VARCHAR2
 ,p_from_date IN VARCHAR2
 ,p_to_date IN VARCHAR2
 ,p_employee_number IN VARCHAR2
 ) IS
 CURSOR cur_emp IS SELECT /*+ PARALLEL */ person_id
 ,employee_number
 FROM per_all_people_f papf
 WHERE trunc(
 papf.creation_date
 ) BETWEEN trunc(
 TO_DATE(
 p_from_date
 ,'DD-MON-RRRR HH24:MI:SS'
 )
 ) AND trunc(
 TO_DATE(
 p_to_date
 ,'DD-MON-RRRR HH24:MI:SS'
 )
 )
 AND papf.employee_number IN ( SELECT TRIM(regexp_substr(
 upper(
 nvl(
 p_employee_number
 ,papf.employee_number
 )
 )
 ,'[^,]+'
 ,1
 ,level
 ) ) employee_number
 FROM dual CONNECT BY
 regexp_substr(
 upper(
 nvl(
 p_employee_number
 ,papf.employee_number
 )
 )
 ,'[^,]+'
 ,1
 ,level
 ) IS NOT NULL
 )
 GROUP BY person_id
 ,employee_number;
 TYPE lv_emp_typ IS
 TABLE OF cur_emp%rowtype;
 l_emp_tab lv_emp_typ := lv_emp_typ ();
 lb_update_warning BOOLEAN;
 ln_start_time NUMBER;
 ln_comment_id NUMBER;
 ln_api_error NUMBER;
 ld_effective_start_date DATE;
 ld_effective_end_date DATE;
 lv_full_name per_all_people_f.full_name%TYPE;
 lb_name_combination_warning BOOLEAN;
 lb_assign_payroll_warning BOOLEAN;
 lb_orig_hire_warning BOOLEAN;
 ln_cagr_grade_def_id NUMBER;
 lv_cagr_concatenated_segments VARCHAR2(240);
 lv_concatenated_segments VARCHAR2(240);
 ln_soft_coding_keyflex_id NUMBER;
 lb_no_managers_warning BOOLEAN;
 lb_other_manager_warning BOOLEAN;
 lb_hourly_salaried_warning BOOLEAN;
 lv_gsp_post_process_warning VARCHAR2(240);
 lv_group_name VARCHAR2(240);
 ln_people_group_id NUMBER;
 ln_rowcount NUMBER;
 lv_error EXCEPTION;
 lv_phone_type VARCHAR2(10);
 BEGIN
 ln_start_time := dbms_utility.get_time;
 ln_api_error := 0;
 ln_rowcount := 0;
 OPEN cur_emp;
 LOOP
 FETCH cur_emp BULK COLLECT INTO l_emp_tab LIMIT 1000;
 EXIT WHEN l_emp_tab.count = 0;
 FOR j IN 1..l_emp_tab.count LOOP
 BEGIN
 BEGIN
 FOR i IN ( SELECT papf.employee_number
 ,papf.object_version_number
 ,papf.effective_start_date
 FROM per_all_people_f papf
 WHERE papf.person_id = l_emp_tab(j).person_id
 GROUP BY papf.employee_number
 ,papf.object_version_number
 ,papf.effective_start_date
 ) LOOP
 ln_rowcount := ln_rowcount + 1;
 hr_person_api.update_gb_person(
 p_effective_date => TO_DATE(
 i.effective_start_date
 ,'DD-MON-RRRR HH24:MI:SS'
 )
 ,p_datetrack_update_mode => 'CORRECTION'
 ,p_person_id => l_emp_tab(j).person_id
 ,p_object_version_number => i.object_version_number
 ,p_date_of_birth => '01-JAN-1980'
 ,p_ethnic_origin => NULL
 ,p_ni_number => NULL
 ,p_marital_status => NULL
 ,p_attribute1 => NULL
 ,p_attribute2 => NULL
 ,p_attribute15 => NULL
 ,p_nationality => NULL
 ,p_registered_disabled_flag => NULL
 ,p_known_as => NULL
 ,p_attribute16 => NULL
 ,p_attribute10 => NULL
 ,p_employee_number => i.employee_number
 ,p_effective_start_date => i.effective_start_date
 ,p_effective_end_date => ld_effective_end_date
 ,p_full_name => lv_full_name
 ,p_comment_id => ln_comment_id
 ,p_name_combination_warning => lb_name_combination_warning
 ,p_assign_payroll_warning => lb_assign_payroll_warning
 ,p_orig_hire_warning => lb_orig_hire_warning
 );
 ld_effective_start_date := NULL;
 ld_effective_end_date := NULL;
 lv_full_name := NULL;
 ln_comment_id := NULL;
 lb_name_combination_warning := NULL;
 lb_assign_payroll_warning := NULL;
 lb_orig_hire_warning := NULL;
 END LOOP;
 EXCEPTION
 WHEN OTHERS THEN
 ln_api_error := ln_api_error + 1;
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'Error in hr_person_api.update_gb_person for employee '
 || l_emp_tab(j).employee_number
 || '. The error is '
 || sqlerrm
 );
 RAISE lv_error;
 END;
 BEGIN
 FOR i IN ( SELECT paaf.assignment_id
 ,paaf.object_version_number
 ,paaf.effective_start_date
 FROM per_all_assignments_f paaf
 WHERE 1 = 1
 AND paaf.person_id = l_emp_tab(j).person_id
 AND paaf.assignment_type = 'E'
 AND paaf.primary_flag = 'Y'
 AND paaf.assignment_status_type_id = 1
 GROUP BY paaf.assignment_id
 ,paaf.object_version_number
 ,paaf.effective_start_date
 ) LOOP
 ln_rowcount := ln_rowcount + 1;
 hr_assignment_api.update_gb_emp_asg(
 p_validate => false
 ,p_effective_date => TO_DATE(
 i.effective_start_date
 ,'DD-MON-RRRR HH24:MI:SS'
 )
 ,p_datetrack_update_mode => 'CORRECTION'
 ,p_assignment_id => i.assignment_id
 ,p_object_version_number => i.object_version_number
 ,p_normal_hours => 0
 ,p_frequency => 'W'
 ,p_ass_attribute6 => NULL
 ,p_ass_attribute28 => NULL
 ,p_ass_attribute1 => NULL
 ,p_ass_attribute4 => NULL
 ,p_ass_attribute5 => NULL
 ,p_ass_attribute7 => NULL
 ,p_ass_attribute14 => NULL
 ,p_ass_attribute18 => NULL
 ,p_ass_attribute19 => NULL
 ,p_ass_attribute23 => NULL
 ,p_ass_attribute24 => NULL
 ,p_ass_attribute2 => NULL
 ,p_ass_attribute17 => NULL
 ,p_ass_attribute25 => NULL
 ,p_ass_attribute26 => NULL
 ,p_ass_attribute27 => NULL
 ,p_ass_attribute29 => NULL
 ,p_ass_attribute11 => NULL
 ,p_cagr_grade_def_id => ln_cagr_grade_def_id
 ,p_cagr_concatenated_segments => lv_cagr_concatenated_segments
 ,p_concatenated_segments => lv_concatenated_segments
 ,p_soft_coding_keyflex_id => ln_soft_coding_keyflex_id
 ,p_comment_id => ln_comment_id
 ,p_effective_start_date => ld_effective_start_date
 ,p_effective_end_date => ld_effective_end_date
 ,p_no_managers_warning => lb_no_managers_warning
 ,p_other_manager_warning => lb_other_manager_warning
 ,p_hourly_salaried_warning => lb_hourly_salaried_warning
 );
 END LOOP;
 EXCEPTION
 WHEN OTHERS THEN
 ln_api_error := ln_api_error + 1;
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'Error in hr_assignment_api.update_gb_emp_asg for employee '
 || l_emp_tab(j).employee_number
 || '. The error is '
 || sqlerrm
 );
 RAISE lv_error;
 END;
 BEGIN
 FOR i IN ( SELECT paaf.assignment_id
 ,paaf.object_version_number
 ,paaf.effective_start_date
 ,paaf.effective_end_date
 FROM per_all_assignments_f paaf
 WHERE 1 = 1
 AND paaf.person_id = l_emp_tab(j).person_id
 AND paaf.assignment_type = 'A'
 GROUP BY paaf.assignment_id
 ,paaf.object_version_number
 ,paaf.effective_start_date
 ,paaf.effective_end_date
 ) LOOP
 ln_rowcount := ln_rowcount + 1;
 hr_assignment_api.update_apl_asg(
 p_validate => false
 ,p_effective_date => TO_DATE(
 i.effective_start_date
 ,'DD-MON-RRRR HH24:MI:SS'
 )
 ,p_datetrack_update_mode => 'CORRECTION'
 ,p_assignment_id => i.assignment_id
 ,p_object_version_number => i.object_version_number
 ,p_normal_hours => 0
 ,p_frequency => 'W'
 ,p_ass_attribute6 => NULL
 ,p_ass_attribute28 => NULL
 ,p_ass_attribute1 => NULL
 ,p_ass_attribute4 => NULL
 ,p_ass_attribute5 => NULL
 ,p_ass_attribute7 => NULL
 ,p_ass_attribute14 => NULL
 ,p_ass_attribute18 => NULL
 ,p_ass_attribute19 => NULL
 ,p_ass_attribute23 => NULL
 ,p_ass_attribute24 => NULL
 ,p_ass_attribute2 => NULL
 ,p_ass_attribute17 => NULL
 ,p_ass_attribute25 => NULL
 ,p_ass_attribute26 => NULL
 ,p_ass_attribute27 => NULL
 ,p_ass_attribute29 => NULL
 ,p_ass_attribute11 => NULL
 ,p_cagr_grade_def_id => ln_cagr_grade_def_id
 ,p_cagr_concatenated_segments => lv_cagr_concatenated_segments
 ,p_concatenated_segments => lv_concatenated_segments
 ,p_group_name => lv_group_name
 ,p_comment_id => ln_comment_id
 ,p_people_group_id => ln_people_group_id
 ,p_soft_coding_keyflex_id => ln_soft_coding_keyflex_id
 ,p_effective_start_date => ld_effective_start_date
 ,p_effective_end_date => ld_effective_end_date
 );
 ln_cagr_grade_def_id := NULL;
 lv_cagr_concatenated_segments := NULL;
 lv_concatenated_segments := NULL;
 ln_soft_coding_keyflex_id := NULL;
 lv_group_name := NULL;
 ln_comment_id := NULL;
 ln_people_group_id := NULL;
 ld_effective_start_date := NULL;
 ld_effective_end_date := NULL;
 END LOOP;
 EXCEPTION
 WHEN OTHERS THEN
 ln_api_error := ln_api_error + 1;
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'Error in hr_assignment_api.update_apl_asg for employee '
 || l_emp_tab(j).employee_number
 || '. The error is '
 || sqlerrm
 );
 RAISE lv_error;
 END;
 BEGIN
 FOR i IN ( SELECT pa.address_id
 ,pa.object_version_number
 FROM per_addresses pa
 WHERE pa.person_id = l_emp_tab(j).person_id
 GROUP BY pa.address_id
 ,pa.object_version_number
 ) LOOP
 ln_rowcount := ln_rowcount + 1;
 hr_person_address_api.update_person_address(
 p_effective_date => SYSDATE
 ,p_address_id => i.address_id
 ,p_object_version_number => i.object_version_number
 ,p_address_line1 => 'AAAAAA'
 ,p_address_line2 => 'BBBBBB'
 ,p_address_line3 => 'CCCCCC'
 ,p_postal_code => 'EC2N 1GJ'
 );
 END LOOP;
 EXCEPTION
 WHEN OTHERS THEN
 ln_api_error := ln_api_error + 1;
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'Error in hr_person_address_api.update_person_address for employee '
 || l_emp_tab(j).employee_number
 || '. The error is '
 || sqlerrm
 );
 END;
 BEGIN
 FOR i IN ( SELECT pp.phone_id
 ,pp.object_version_number
 ,pp.phone_type
 FROM per_phones pp
 WHERE 1 = 1
 AND pp.parent_table = 'PER_ALL_PEOPLE_F'
 AND pp.parent_id = l_emp_tab(j).person_id
 GROUP BY pp.phone_id
 ,pp.object_version_number
 ,pp.phone_type
 ) LOOP
 ln_rowcount := ln_rowcount + 1;
 IF
 i.phone_type = 'M'
 THEN
 lv_phone_type := 'M';
 ELSIF i.phone_type = 'H1' THEN
 lv_phone_type := 'H1';
 ELSE
 lv_phone_type := 'W1';
 END IF;
 hr_phone_api.update_phone(
 p_phone_id => i.phone_id
 ,p_phone_type => lv_phone_type
 ,p_phone_number => 9999999999
 ,p_object_version_number => i.object_version_number
 ,p_effective_date => SYSDATE
 );
 END LOOP;
 EXCEPTION
 WHEN OTHERS THEN
 ln_api_error := ln_api_error + 1;
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'Error in hr_phone_api.update_phone for employee '
 || l_emp_tab(j).employee_number
 || '. The error is '
 || sqlerrm
 );
 END;
 BEGIN
 FOR i IN ( SELECT pppmf.personal_payment_method_id
 ,pppmf.object_version_number
 ,pppmf.external_account_id
 ,pppmf.percentage
 ,pppmf.priority
 ,pppmf.effective_start_date
 ,pppmf.effective_end_date
 ,pea.territory_code
 FROM pay_personal_payment_methods_f pppmf
 ,pay_external_accounts pea
 ,per_all_assignments_f paaf
 WHERE paaf.assignment_id = pppmf.assignment_id
 AND pppmf.external_account_id = pea.external_account_id
 AND paaf.person_id = l_emp_tab(j).person_id
 GROUP BY pppmf.personal_payment_method_id
 ,pppmf.object_version_number
 ,pppmf.external_account_id
 ,pppmf.percentage
 ,pppmf.priority
 ,pppmf.effective_start_date
 ,pppmf.effective_end_date
 ,pea.territory_code
 ) LOOP
 ln_rowcount := ln_rowcount + 1;
 hr_personal_pay_method_api.update_gb_personal_pay_method(
 p_effective_date => TO_DATE(
 i.effective_start_date
 ,'DD-MON-RRRR HH24:MI:SS'
 )
 ,p_datetrack_update_mode => 'CORRECTION'
 ,p_personal_payment_method_id => i.personal_payment_method_id
 ,p_object_version_number => i.object_version_number
 ,p_account_name => 'BANK_ACC_ANON'
 ,p_account_number => 11111111
 ,p_sort_code => 999999
 ,p_bank_name => 'BANK_DATA_ANON'
 ,p_account_type => NULL
 ,p_bank_branch => 'BRANCH_DATA_ANON'
 ,p_bank_branch_location => NULL
 ,p_bldg_society_account_number => 'ABCD1234'
 ,p_segment10 => 'BANK_DATA_ANON'
 ,p_percentage => i.percentage
 ,p_priority => i.priority
 ,p_territory_code => i.territory_code
 ,p_comment_id => ln_comment_id
 ,p_external_account_id => i.external_account_id
 ,p_effective_start_date => i.effective_start_date
 ,p_effective_end_date => i.effective_start_date
 );
 ln_comment_id := NULL;
 END LOOP;
 EXCEPTION
 WHEN OTHERS THEN
 ln_api_error := ln_api_error + 1;
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'Error in hr_personal_pay_method_api.update_gb_personal_pay_method for employee '
 || l_emp_tab(j).employee_number
 || '. The error is '
 || sqlerrm
 );
 RAISE lv_error;
 END;
 BEGIN
 FOR i IN ( SELECT petf.element_name
 ,peef.element_entry_id
 ,peef.object_version_number
 ,peef.effective_start_date
 ,peef.effective_end_date
 ,pivf.input_value_id
 ,pivf.name
 FROM pay_element_types_f petf
 ,pay_element_links_f pelf
 ,pay_input_values_f pivf
 ,pay_element_entries_f peef
 ,per_all_assignments_f paaf
 WHERE 1 = 1
 AND pelf.element_type_id = petf.element_type_id
 AND pivf.element_type_id = petf.element_type_id
 AND peef.element_type_id = petf.element_type_id
 AND peef.element_link_id = pelf.element_link_id
 AND paaf.assignment_id = peef.assignment_id
 AND pivf.name IN (
 'Annual Amount'
 ,'Annual Pay Value'
 ,'Hourly Amount'
 ,'Hourly Rate'
 ,'Pay Value'
 ,'Periodic Amount'
 ,'Weekly Rate'
 )
 AND paaf.person_id = l_emp_tab(j).person_id
 GROUP BY petf.element_name
 ,peef.element_entry_id
 ,peef.object_version_number
 ,peef.effective_start_date
 ,peef.effective_end_date
 ,pivf.input_value_id
 ,pivf.name
 ORDER BY 4 ) LOOP
 ln_rowcount := ln_rowcount + 1;
 pay_element_entry_api.update_element_entry(
 p_validate => false
 ,p_datetrack_update_mode => 'CORRECTION'
 ,p_effective_date => TO_DATE(
 i.effective_start_date
 ,'DD-MON-RRRR HH24:MI:SS'
 )
 ,p_business_group_id => fnd_profile.value('PER_BUSINESS_GROUP_ID')
 ,p_element_entry_id => i.element_entry_id
 ,p_object_version_number => i.object_version_number
 ,p_input_value_id1 => i.input_value_id
 ,p_entry_value1 => 9999
 ,p_effective_start_date => ld_effective_start_date
 ,p_effective_end_date => ld_effective_end_date
 ,p_update_warning => lb_update_warning
 );
 ld_effective_start_date := NULL;
 ld_effective_end_date := NULL;
 lb_update_warning := NULL;
 END LOOP;
 EXCEPTION
 WHEN OTHERS THEN
 ln_api_error := ln_api_error + 1;
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'Error in pay_element_entry_api.update_element_entry for employee '
 || l_emp_tab(j).employee_number
 || '. The error is '
 || sqlerrm
 );
 RAISE lv_error;
 END;
 BEGIN
 FOR i IN ( SELECT petf.element_name
 ,peef.element_entry_id
 ,peef.object_version_number
 ,peef.effective_start_date
 ,peef.effective_end_date
 ,pivf.input_value_id
 ,pivf.name
 FROM pay_element_types_f petf
 ,pay_element_links_f pelf
 ,pay_input_values_f pivf
 ,pay_element_entries_f peef
 ,per_all_assignments_f paaf
 WHERE 1 = 1
 AND pelf.element_type_id = petf.element_type_id
 AND pivf.element_type_id = petf.element_type_id
 AND peef.element_type_id = petf.element_type_id
 AND peef.element_link_id = pelf.element_link_id
 AND paaf.assignment_id = peef.assignment_id
 AND pivf.name = 'Weekly Amount'
 AND paaf.person_id = l_emp_tab(j).person_id
 GROUP BY petf.element_name
 ,peef.element_entry_id
 ,peef.object_version_number
 ,peef.effective_start_date
 ,peef.effective_end_date
 ,pivf.input_value_id
 ,pivf.name
 ORDER BY 4 ) LOOP
 ln_rowcount := ln_rowcount + 1;
 pay_element_entry_api.update_element_entry(
 p_validate => false
 ,p_datetrack_update_mode => 'CORRECTION'
 ,p_effective_date => TO_DATE(
 i.effective_start_date
 ,'DD-MON-RRRR HH24:MI:SS'
 )
 ,p_business_group_id => fnd_profile.value('PER_BUSINESS_GROUP_ID')
 ,p_element_entry_id => i.element_entry_id
 ,p_object_version_number => i.object_version_number
 ,p_input_value_id1 => i.input_value_id
 ,p_entry_value1 => 9999
 ,p_effective_start_date => ld_effective_start_date
 ,p_effective_end_date => ld_effective_end_date
 ,p_update_warning => lb_update_warning
 );
 ld_effective_start_date := NULL;
 ld_effective_end_date := NULL;
 lb_update_warning := NULL;
 END LOOP;
 EXCEPTION
 WHEN OTHERS THEN
 ln_api_error := ln_api_error + 1;
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'Error in pay_element_entry_api.update_element_entry Weekly Amount for employee '
 || l_emp_tab(j).employee_number
 || '. The error is '
 || sqlerrm
 );
 RAISE lv_error;
 END;
 BEGIN
 FOR i IN ( SELECT ppos.object_version_number
 ,ppos.period_of_service_id
 FROM per_periods_of_service ppos
 WHERE ppos.actual_termination_date IS NOT NULL
 AND ppos.person_id = l_emp_tab(j).person_id
 GROUP BY ppos.object_version_number
 ,ppos.period_of_service_id
 ) LOOP
 ln_rowcount := ln_rowcount + 1;
 hr_ex_employee_api.update_term_details_emp(
 p_effective_date => SYSDATE
 ,p_period_of_service_id => i.period_of_service_id
 ,p_object_version_number => i.object_version_number
 ,p_leaving_reason => 50
 );
 END LOOP;
 EXCEPTION
 WHEN OTHERS THEN
 ln_api_error := ln_api_error + 1;
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'Error in hr_ex_employee_api.update_term_details_emp for employee '
 || l_emp_tab(j).employee_number
 || '. The error is '
 || sqlerrm
 );
 RAISE lv_error;
 END;
 BEGIN
 FOR i IN ( SELECT papf.employee_number
 ,papf.object_version_number
 ,papf.effective_start_date
 FROM per_all_people_f papf
 ,per_periods_of_service ppos
 WHERE papf.person_id = ppos.person_id
 AND ppos.actual_termination_date IS NOT NULL
 AND papf.person_id = l_emp_tab(j).person_id
 GROUP BY papf.employee_number
 ,papf.object_version_number
 ,papf.effective_start_date
 ) LOOP
 ln_rowcount := ln_rowcount + 1;
 hr_person_api.update_gb_person(
 p_effective_date => TO_DATE(
 i.effective_start_date
 ,'DD-MON-RRRR HH24:MI:SS'
 )
 ,p_datetrack_update_mode => 'CORRECTION'
 ,p_person_id => l_emp_tab(j).person_id
 ,p_object_version_number => i.object_version_number
 ,p_email_address => NULL
 ,p_employee_number => i.employee_number
 ,p_effective_start_date => i.effective_start_date
 ,p_effective_end_date => ld_effective_end_date
 ,p_full_name => lv_full_name
 ,p_comment_id => ln_comment_id
 ,p_name_combination_warning => lb_name_combination_warning
 ,p_assign_payroll_warning => lb_assign_payroll_warning
 ,p_orig_hire_warning => lb_orig_hire_warning
 );
 ld_effective_start_date := NULL;
 ld_effective_end_date := NULL;
 lv_full_name := NULL;
 ln_comment_id := NULL;
 lb_name_combination_warning := NULL;
 lb_assign_payroll_warning := NULL;
 lb_orig_hire_warning := NULL;
 END LOOP;
 EXCEPTION
 WHEN OTHERS THEN
 ln_api_error := ln_api_error + 1;
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'Error in hr_person_api.update_gb_person to update email address for terminated employee'
 || l_emp_tab(j).employee_number
 || '. The error is '
 || sqlerrm
 );
 RAISE lv_error;
 END;
 COMMIT;
 EXCEPTION
 WHEN lv_error THEN
 ROLLBACK;
 END;
 END LOOP;
 END LOOP;
 IF
 ln_api_error <> 0
 THEN
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'Total number of records for '
 || cur_emp%rowcount
 || ' employees are '
 || ln_rowcount
 );
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'Total number of processed records are '
 || (ln_rowcount - ln_api_error)
 );
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'Total number of error records are '
 || ln_api_error
 );
 ELSE
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'All records are anonymized successfully..!!'
 );
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'Total number of records for '
 || cur_emp%rowcount
 || ' employees are '
 || ln_rowcount
 );
 END IF;
 CLOSE cur_emp;
 COMMIT;
 EXCEPTION
 WHEN OTHERS THEN
 apps.fnd_file.put_line(
 apps.fnd_file.output
 ,'Error in xxab_hrms_data_scrambling procedure '
 || sqlerrm
 );
 END xxab_hrms_data_scrambling;
END xxab_hrms_data_anonymization;