PLSQL Programs
1. Pseudo Code
2. AR Invoice creation
3. Supplier creation
4. Customer creation
5. HRMS API’s for data anonymization/ masking/ scrambling
6. Send output of Standard PO Concurrent program via Email
7. Creation of Value Set Values Anonymous Block
8. Insert data into table using FORALL
9. UTL_FILE – Outbound
10. API to create table type value set, delete concurrent program, executable and create new executable, program, parameters and attach to request group
1. Pseudo Code
SET SERVEROUTPUT ON;
CLEAR SCREEN;
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
dbms_output.put_line(sqlerrm);
END;
/
SHOW ERROR;
2. 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
/*Applications context set. 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;
3. 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_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;
------------------------------
-- * 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
) 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;
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*/
4. Customer creation
Customer creation: Create Organization >> Loction >> Party Site >> Party Site Use >> Contact Point >> Org Contact >> Customer Account >> Customer Account Site >> Customer Account Site Use
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;
5. HRMS API’s for data anonymization/ masking/ scrambling
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;
6. Send output of Standard PO Concurrent program via Email
SET SERVEROUTPUT ON;
CLEAR SCREEN;
CREATE OR REPLACE PACKAGE BODY xxaa_po_output_email AS
/************************************************************************
* TYPE : Package *
* NAME : xxaa_po_output_email *
* INPUT Parameters : p_from_address, p_to_address *
* OUTPUT Parametrs : p_errbuf, p_retcode *
* PURPOSE : PO Output for Communication *
************************************************************************/
------------------------------------------
-- * Procedure Name : xxaa_po_output
-- * Description : This Procedure fetches approved POs
-- * and sends PDF output through email to
-- * supplier site email address
------------------------------------------
PROCEDURE xxaa_po_output (
p_errbuf OUT NOCOPY VARCHAR2
,p_retcode OUT NOCOPY VARCHAR2
,p_from_address IN VARCHAR2
,p_to_address IN VARCHAR2
) IS
l_request_id NUMBER;
l_bol_delivery BOOLEAN;
l_last_run_date DATE;
l_curr_run_date DATE;
l_creation_date DATE;
ln_responsibility_id NUMBER;
ln_application_id NUMBER;
lv_db_name VARCHAR2(50);
l_to_address VARCHAR2(200);
l_rowcount NUMBER;
lv_resp_name VARCHAR2(100);
ln_org_id NUMBER;
lv_org_name VARCHAR2(100);
ln_resp_id NUMBER;
BEGIN
lv_org_name := upper(fnd_profile.value('ORG_NAME') );
IF
lv_org_name = 'ABC'
THEN
lv_resp_name := fnd_profile.value('RESP_NAME');
ln_resp_id := fnd_profile.value('RESP_ID');
ln_org_id := fnd_profile.value('ORG_ID');
l_to_address := p_to_address;
ln_responsibility_id := NULL;
ln_application_id := NULL;
BEGIN
SELECT frt.responsibility_id
,frt.application_id INTO
ln_responsibility_id,ln_application_id
FROM fnd_responsibility_tl frt
WHERE 1 = 1
AND frt.responsibility_name = lv_resp_name;
EXCEPTION
WHEN OTHERS THEN
ln_responsibility_id := NULL;
ln_application_id := NULL;
apps.fnd_file.put_line(
apps.fnd_file.log
,'Error while fetching Responsibility ID and Application ID: ' || sqlerrm
);
END;
fnd_global.apps_initialize(
user_id => fnd_profile.value('USER_ID')
,resp_id => ln_responsibility_id
,resp_appl_id => ln_application_id
);
/*Query to fetch concurrent program last run date*/
BEGIN
SELECT actual_completion_date INTO
l_last_run_date
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = ( SELECT MAX(request_id)
FROM fnd_concurrent_requests fcr1
,fnd_concurrent_programs_tl fcpt
WHERE 1 = 1
AND fcpt.concurrent_program_id = fcr1.concurrent_program_id
AND fcr1.responsibility_id = ln_resp_id
AND fcpt.user_concurrent_program_name = 'ABC PO Output for Communication'
AND fcr1.status_code = 'C'
);
EXCEPTION
WHEN OTHERS THEN
l_last_run_date := NULL;
apps.fnd_file.put_line(
apps.fnd_file.log
,'Error while fetching Concurrent program last run date: ' || sqlerrm
);
END;
/*Query to fetch concurrent program current run date*/
BEGIN
SELECT actual_start_date INTO
l_curr_run_date
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = ( SELECT MAX(request_id)
FROM fnd_concurrent_requests fcr1
,fnd_concurrent_programs_tl fcpt
WHERE 1 = 1
AND fcpt.concurrent_program_id = fcr1.concurrent_program_id
AND fcr1.responsibility_id = ln_resp_id
AND fcpt.user_concurrent_program_name = 'ABC PO Output for Communication'
AND fcr1.status_code = 'R'
AND fcr1.phase_code = 'R'
);
EXCEPTION
WHEN OTHERS THEN
l_curr_run_date := NULL;
apps.fnd_file.put_line(
apps.fnd_file.log
,'Error while fetching Concurrent program current run date: ' || sqlerrm
);
END;
BEGIN
SELECT name INTO
lv_db_name
FROM v$database;
EXCEPTION
WHEN OTHERS THEN
lv_db_name := NULL;
apps.fnd_file.put_line(
apps.fnd_file.log
,'Error while fetching Database Name: ' || sqlerrm
);
END;
BEGIN
SELECT creation_date INTO
l_creation_date
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name = 'ABC PO Output for Communication';
EXCEPTION
WHEN OTHERS THEN
l_creation_date := NULL;
apps.fnd_file.put_line(
apps.fnd_file.log
,'Error while fetching Concurrent program creation date: ' || sqlerrm
);
END;
FOR i IN ( SELECT a.segment1
,b.email_address
,c.vendor_name
,ROWNUM
FROM po_headers_all a
,ap_supplier_sites_all b
,ap_suppliers c
WHERE 1 = 1
AND a.vendor_site_id = b.vendor_site_id
AND c.vendor_id = b.vendor_id
AND b.supplier_notif_method = 'PRINT'
AND a.authorization_status = 'APPROVED'
AND a.type_lookup_code = 'STANDARD'
AND a.org_id = ln_org_id
AND a.last_update_date BETWEEN ( nvl(
l_last_run_date
,l_creation_date
) ) AND ( l_curr_run_date )
ORDER BY a.segment1 ) LOOP
l_rowcount := i.rownum;
apps.fnd_file.put_line(
apps.fnd_file.log
,'Fetching PO: ' || i.segment1 || ' for Supplier: ' || i.vendor_name || ' having email ' || i.email_address
);
IF
lv_db_name = 'PROD'
THEN
l_to_address := i.email_address;
ELSIF lv_db_name != 'PROD' AND upper(
i.email_address
) LIKE upper(
'%@test.com'
) THEN
l_to_address := i.email_address;
ELSIF upper(p_to_address) LIKE upper(
'%@test.com'
) THEN
apps.fnd_file.put_line(
apps.fnd_file.log
,'Please Change the email address at Supplier site as @test.com for Supplier: ' || i.segment1 || ' in NON-PROD instance'
);
apps.fnd_file.put_line(
apps.fnd_file.log
,'Purchase Order sent to Email Address: ' || p_to_address
);
l_to_address := p_to_address;
ELSE
apps.fnd_file.put_line(
apps.fnd_file.log
,'Please change the Email address at Supplier site or Input Parameter as @test.com for NON-PROD instance'
);
l_to_address := NULL;
END IF;
IF
i.segment1 IS NOT NULL AND l_to_address IS NOT NULL
THEN
l_bol_delivery := fnd_request.add_delivery_option(
type => 'E'
, -- EMAIL
p_argument1 => 'FYI: For Your Review -- Standard Purchase Order ' || i.segment1
, -- Email Subject
p_argument2 => p_from_address
, -- From Address
p_argument3 => l_to_address
, -- To Address
p_argument4 => NULL -- CC
);
l_request_id := fnd_request.submit_request(
application => 'PO'
, --v_conc_prog_appl_short_name,
program => 'POXPOPDF'
, --v_conc_prog_short_name,
description => NULL
, --Description
start_time => NULL
, --Time to start the program
sub_request => false
, -- sub program
argument1 => 'R'
,argument2 => NULL
,argument3 => i.segment1
,argument4 => i.segment1
,argument5 => NULL
,argument6 => NULL
,argument7 => NULL
,argument8 => NULL
,argument9 => NULL
,argument10 => 'N'
,argument11 => 'Y'
,argument12 => NULL
,argument13 => NULL
,argument14 => NULL
,argument15 => NULL
,argument16 => NULL
,argument17 => 'Communicate'
,argument18 => 'T'
,argument19 => 'N'
,argument20 => 'Y'
,argument21 => NULL
,argument22 => NULL
,argument23 => NULL
,argument24 => NULL
,argument25 => NULL
,argument26 => NULL
,argument27 => NULL
,argument28 => NULL
,argument29 => 'N'
);
COMMIT;
IF
l_request_id = 0
THEN
apps.fnd_file.put_line(
apps.fnd_file.log
,'Request not submitted error ' || fnd_message.get
);
ELSE
apps.fnd_file.put_line(
apps.fnd_file.log
,'Request submitted successfully request id ' || l_request_id
);
END IF;
END IF;
END LOOP;
IF
l_rowcount IS NULL
THEN
apps.fnd_file.put_line(
apps.fnd_file.log
,'No Approved Purchase Orders found between previous run and current run..!'
);
END IF;
ELSE
p_retcode := 2;
apps.fnd_file.put_line(
apps.fnd_file.log
,'Please select valid responsibility to execute Supplier conversion'
);
END IF;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(
apps.fnd_file.log
,'Unexpected error in ABC PO Output for Communication' || sqlerrm
);
END xxaa_po_output;
END xxaa_po_output_email;
/
SHOW ERROR
7. Creation of Value Set Values Anonymous Block
SET SERVEROUTPUT ON;
CLEAR SCREEN;
DECLARE
x VARCHAR2(200);
v_msg VARCHAR2(2000);
lv_flex_value_meaning VARCHAR2(2000);
BEGIN
FOR i IN ( SELECT
*
FROM
sample_coa
) LOOP
BEGIN
SELECT
ffvv.flex_value_meaning
INTO lv_flex_value_meaning
FROM
fnd_flex_values_vl ffvv,
fnd_flex_value_sets ffvs
WHERE
ffvv.flex_value_set_id = ffvs.flex_value_set_id
AND ffvv.enabled_flag = 'Y'
AND ffvv.flex_value_meaning = i.as_is
AND ffvs.flex_value_set_name = 'XXCOA_COMPANY_MAPPING';
dbms_output.put_line('Value already exists ' || lv_flex_value_meaning);
EXCEPTION
WHEN OTHERS THEN
fnd_flex_val_api.create_independent_vset_value(
'XXCOA_COMPANY_MAPPING',
i.as_is,
i.to_be,
'Y',
SYSDATE,
NULL,
'N',
NULL,
NULL,
x
);
dbms_output.put_line('Value created ' || x);
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_msg := fnd_flex_val_api.message;
dbms_output.put_line('Error is: ' || sqlerrm);
END;
/
8. Insert data into table using FORALL
CREATE TABLE xxap_vendor_master_stg_tab (
vendornumber VARCHAR2(500),
vendorname VARCHAR2(500)
);
SELECT* FROM xxap_vendor_master_stg_tab;--No records
------------------
--Anonymous Block
------------------
DECLARE
l_rec_count NUMBER;
CURSOR cur_vendor_master IS
SELECT /*+ PARALLEL */ aps.segment1 vendornumber,
aps.vendor_name vendorname
FROM ap_suppliers aps
WHERE ROWNUM<5;
TYPE vendor_master_tbl_type IS TABLE OF cur_vendor_master%rowtype;
l_vendor_master_tab vendor_master_tbl_type:=vendor_master_tbl_type();
TYPE vendor_master_stg_tbl_type IS TABLE OF xxap_vendor_master_stg_tab%rowtype;
l_vendor_master_stg_tab vendor_master_stg_tbl_type:=vendor_master_stg_tbl_type();
BEGIN
OPEN cur_vendor_master;
LOOP
l_vendor_master_tab.DELETE;
l_vendor_master_stg_tab.DELETE;
l_rec_count :=0;
FETCH cur_vendor_master BULK COLLECT INTO l_vendor_master_tab;
FOR i IN 1..l_vendor_master_tab.count LOOP
l_vendor_master_stg_tab.extend;
l_rec_count :=l_rec_count+1;
l_vendor_master_stg_tab(l_rec_count).vendornumber :=l_vendor_master_tab(i).vendornumber;
l_vendor_master_stg_tab(l_rec_count).vendorname :=l_vendor_master_tab(i).vendorname;
END LOOP;
FORALL i IN 1..l_vendor_master_stg_tab.count
INSERT INTO xxap_vendor_master_stg_tab VALUES l_vendor_master_stg_tab ( i );
COMMIT;
EXIT WHEN l_vendor_master_tab.count=0;
END LOOP;
CLOSE cur_vendor_master;
END;
------------------------------------------------------------
SELECT*
FROM xxap_vendor_master_stg_tab; --4 records appear
9. UTL_FILE – Outbound
CREATE OR REPLACE PROCEDURE outbound_extract (
p_out_errbuf OUT VARCHAR2,
p_out_retcode OUT VARCHAR2
) AS
------------------------------------------------------------------------------------------------------------------------------------------
v_utlfile utl_file.file_type;
v_directory_name dba_directories.directory_path%TYPE:='HR_OUTPUT'; --'/u01/oraout/instance_name/outbound';
l_column_name VARCHAR2(20000);
l_column_values VARCHAR2(20000);
v_count NUMBER;
BEGIN
--------------------------------------------------------------------------------------------------------------
--PO_LINE_TYPES_TL TABLE
--------------------------------------------------------------------------------------------------------------
BEGIN
v_count :=0;
v_utlfile :=utl_file.fopen(
v_directory_name,
'PO_LINE_TYPES_TL.csv',
'W',
32767
);
l_column_name :='LINE_TYPE_ID,LANGUAGE,SOURCE_LANG,DESCRIPTION,LINE_TYPE,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,CREATION_DATE,CREATED_BY';
utl_file.put_line(
v_utlfile,
l_column_name
);
FOR i IN ( SELECT*
FROM po_line_types_tl
WHERE trunc(creation_date) BETWEEN TO_DATE('01-JUL-21 00:00:00','DD-MON-YY HH24:MI:SS') AND TO_DATE('09-OCT-21 00:00:00','DD-MON-YY HH24:MI:SS')
) LOOP
v_count :=v_count+1;
l_column_values :=i.line_type_id
||','
||i.language
||','
||i.source_lang
||','
||i.description
||','
||i.line_type
||','
||i.last_update_date
||','
||i.last_updated_by
||','
||i.last_update_login
||','
||i.creation_date
||','
||i.created_by;
utl_file.put_line(
v_utlfile,
l_column_values
);
END LOOP;
utl_file.fclose(v_utlfile);
fnd_file.put_line(
fnd_file.log,
'Total records fetched PO_LINE_TYPES_TL '||v_count
);
END;
END;
10. API to create table type value set, delete concurrent program, executable and create new executable, program, parameters and attach to request group
DECLARE
v_conc_program_id NUMBER;
v_conc_program_name VARCHAR2 (200);
v_conc_prog_short_name VARCHAR2 (200);
v_ccp_application_id NUMBER;
v_ccp_application_name VARCHAR2 (200);
v_conc_program_count NUMBER;
v_conc_executable_id NUMBER;
v_conc_executable_name VARCHAR2 (200);
v_conc_exec_short_name VARCHAR2 (200);
v_cce_application_id NUMBER;
v_cce_application_name VARCHAR2 (200);
v_conc_executable_count NUMBER;
TYPE tab_ccp_name IS TABLE OF VARCHAR2 (200)
INDEX BY BINARY_INTEGER;
vtab_ccp_name tab_ccp_name;
BEGIN
--API to create table type value set
BEGIN
FND_FLEX_VAL_API.SET_SESSION_MODE ('customer_data'); /*Mandatory*/
FND_FLEX_VAL_API.CREATE_VALUESET_TABLE (
value_set_name => 'XX_ORG_NAME_VS',
description => 'Inventory Organization Value Set',
security_available => 'N',
enable_longlist => 'N',
format_type => 'Char',
maximum_size => 20,
PRECISION => NULL,
numbers_only => 'N',
uppercase_only => 'N',
right_justify_zero_fill => 'N',
min_value => NULL,
max_value => NULL,
table_application => NULL,
table_appl_short_name => NULL,
table_name => 'org_organization_definitions',
allow_parent_values => 'N',
value_column_name => 'organization_name',
value_column_type => 'Char',
value_column_size => 240,
meaning_column_name => 'organization_code',
meaning_column_type => Char,
meaning_column_size => 3,
id_column_name => 'organization_id',
id_column_type => 'Number',
id_column_size => 15,
where_order_by => 'operating_unit = NVL(fnd_profile.value(''ORG_ID''),operating_unit)',
additional_columns => NULL);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
--API to delete concurrent program and executable
BEGIN
vtab_ccp_name (1) := 'Concurrent Program Full Name';
--vtab_ccp_name(2) := 'Program Name';
--vtab_ccp_name(3) := 'Program Name';
--vtab_ccp_name(4) := 'Program Name';
v_conc_program_count := 0;
v_conc_executable_count := 0;
FOR i IN 1 .. vtab_ccp_name.COUNT
LOOP
BEGIN
SELECT concurrent_program_id,
concurrent_program_name,
application_id,
executable_id
INTO v_conc_program_id,
v_conc_prog_short_name,
v_ccp_application_id,
v_conc_executable_id
FROM apps.fnd_concurrent_programs_vl
WHERE user_concurrent_program_name =
vtab_ccp_name (i);
SELECT application_name
INTO v_ccp_application_name
FROM apps.fnd_application_vl
WHERE application_id = v_ccp_application_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Caught in Exception while fetching the details of concurrent program : '
|| vtab_ccp_name (i)
|| ' : '
|| SQLERRM);
END;
IF v_conc_executable_id IS NOT NULL
THEN
BEGIN
SELECT executable_name, application_id
INTO v_conc_exec_short_name,
v_cce_application_id
FROM apps.fnd_executables
WHERE executable_id =
v_conc_executable_id;
SELECT application_name
INTO v_cce_application_name
FROM apps.fnd_application_vl
WHERE application_id =
v_cce_application_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Caught in Exception while fetching the details of concurrent program Executable : '
|| SQLERRM);
END;
ELSE
DBMS_OUTPUT.put_line (
'Executable not existing for concurrent program : '
|| vtab_ccp_name (i));
END IF;
IF v_conc_program_id > 0
THEN
/*-- API to Delete Concurrent Program ---- */
BEGIN
apps.fnd_program.delete_program (
program_short_name => v_conc_prog_short_name,
application => v_ccp_application_name);
COMMIT;
DBMS_OUTPUT.put_line (
'Concurrent Program has been deleted : '
|| vtab_ccp_name (i));
v_conc_program_count :=
v_conc_program_count + 1;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Caught in Exception while deleting the Concurrent Program : '
|| vtab_ccp_name (i)
|| ' : '
|| SQLERRM);
END;
/*-- API to Delete Concurrent Program Executable ---- */
BEGIN
apps.fnd_program.delete_executable (
executable_short_name => v_conc_exec_short_name,
application => v_cce_application_name);
COMMIT;
DBMS_OUTPUT.put_line (
'Concurrent Program Executable has been deleted : '
|| v_conc_exec_short_name);
v_conc_executable_count :=
v_conc_executable_count + 1;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Caught in Exception while deleting the Concurrent Program Executable : '
|| v_conc_exec_short_name
|| ' : '
|| SQLERRM);
END;
ELSE
DBMS_OUTPUT.put_line (
'Concurrent program Not Existing, Please create : '
|| vtab_ccp_name (i));
END IF;
DBMS_OUTPUT.put_line (
'Total Count of deleted Concurrent Program/s : '
|| v_conc_program_count);
DBMS_OUTPUT.put_line (
'Total Count of deleted Concurrent Program Executable : '
|| v_conc_executable_count);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Caught in Main Exception :' || SQLERRM);
END;
--API to create new executable
BEGIN
fnd_program.executable (
executable => 'Executable Full Name', -- Executable Name
application => 'XX', -- Application Short Name
short_name => 'XX_EXECUTABLE', -- Executable Short Name
description => 'Executable Description', -- Description,DEFAULT NULL
execution_method => 'PL/SQL Stored Procedure', -- Execution Method
execution_file_name => 'pkg_name.prc_name', -- Execution File Name,DEFAULT NULL
subroutine_name => NULL, -- Subroutine Name,DEFAULT NULL
icon_name => NULL, -- Icon Name,DEFAULT NULL
language_code => 'US', -- Language Code,DEFAULT 'US'
execution_file_path => NULL -- Execution File Path, DEFAULT NULL
);
END;
--API to create new concurrent program
BEGIN
fnd_program.register (
program => 'Concurrent Program Full Name', -- CP Name
application => 'XX', -- Application Short Name
enabled => 'Y', -- Flag to Enable/Disable a CP
short_name => 'XX_CP_SHORT_NAME', -- CP Short Name
description => 'Program Description', -- Description,DEFAULT NULL
executable_short_name => 'XX_EXECUTABLE', -- Executable Short Name
executable_application => 'XX', -- Executable Application Short Name
execution_options => NULL, -- Execution Options,DEFAULT NULL,
priority => NULL, -- Priority,DEFAULT NULL,
save_output => 'Y', -- Save Output,DEFAULT 'Y',
PRINT => 'Y', -- Print,DEFAULT 'Y',
cols => NULL, -- DEFAULT NULL,
rows => NULL, -- DEFAULT NULL,
style => NULL, -- DEFAULT NULL,
style_required => 'N', -- DEFAULT 'N',
printer => NULL, -- DEFAULT NULL,
request_type => NULL, -- DEFAULT NULL,
request_type_application => NULL, -- DEFAULT NULL,
use_in_srs => 'Y', -- DEFAULT 'N',
allow_disabled_values => 'N', -- DEFAULT 'N',
run_alone => 'N', -- DEFAULT 'N',
output_type => 'TEXT', -- DEFAULT 'TEXT'
enable_trace => 'N', -- DEFAULT 'N',
restart => 'Y', -- DEFAULT 'Y',
nls_compliant => 'Y', -- DEFAULT 'Y',
icon_name => NULL, -- DEFAULT NULL,
language_code => 'US', -- DEFAULT 'US',
mls_function_short_name => NULL, -- DEFAULT NULL,
mls_function_application => NULL, -- DEFAULT NULL,
incrementor => NULL, -- DEFAULT NULL,
refresh_portlet => NULL -- DEFAULT NULL,
);
END;
--API to attach progrm to request group
BEGIN
fnd_program.add_to_group ('XX_CP_SHORT_NAME', -- Concurrent Program Short Name
'XX', -- Application Short Name
'All Inclusive GUI', -- Report Group Name
'INV'); -- Report Group Application
END;
--API to attach parameters to concurrrent program. Only new parameters will be added. Existing cant be amended and throw error
BEGIN
fnd_program.parameter (
program_short_name => 'XX_CP_SHORT_NAME', --l_program_short_name,
application => 'XX',
sequence => 10,
parameter => 'P_ORGANIZATION_CODE',
description => NULL,
enabled => 'Y',
value_set => 'INV_SRS_NUMBER',
default_type => 'Profile',
DEFAULT_VALUE => 'mfg_organization_id',
required => 'Y',
enable_security => 'N',
range => NULL,
display => 'N',
display_size => 22,
description_size => 50,
concatenated_description_size => 25,
prompt => 'Organization Code',
token => NULL,
cd_parameter => NULL);
fnd_program.parameter (
program_short_name => 'XX_CP_SHORT_NAME', --l_program_short_name,
application => 'XX',
sequence => 20,
parameter => 'P_CYCLE_COUNT_HEADER',
description => NULL,
enabled => 'Y',
value_set => 'INV_SRS_CYCLE_COUNT_HDR',
default_type => NULL,
DEFAULT_VALUE => NULL,
required => 'N',
enable_security => 'N',
range => NULL,
display => 'Y',
display_size => 30,
description_size => 50,
concatenated_description_size => 25,
prompt => 'Cycle Count Name',
token => NULL,
cd_parameter => NULL);
fnd_program.parameter (
program_short_name => 'XX_CP_SHORT_NAME', --l_program_short_name,
application => 'XX',
sequence => 30,
parameter => 'P_COUNT_LIST_SEQUENCE',
description => NULL,
enabled => 'Y',
value_set => '20 Characters',
default_type => NULL,
DEFAULT_VALUE => NULL,
required => 'N',
enable_security => 'N',
range => NULL,
display => 'Y',
display_size => 10,
description_size => 50,
concatenated_description_size => 25,
prompt => 'Count List Sequence',
token => NULL,
cd_parameter => NULL);
fnd_program.parameter (
program_short_name => 'XX_CP_SHORT_NAME', --l_program_short_name,
application => 'XX',
sequence => 40,
parameter => 'REPROCESS_FLAG',
description => NULL,
enabled => 'Y',
value_set => 'INV_SRS_YES_NO',
default_type => 'Constant',
DEFAULT_VALUE => 'No',
required => 'N',
enable_security => 'N',
range => NULL,
display => 'Y',
display_size => 10,
description_size => 50,
concatenated_description_size => 25,
prompt => 'Reprocess Flag',
token => NULL,
cd_parameter => NULL);
END;
COMMIT;
END;