Supplier & Supplier Site creation using API

Tables Populated By API:

Supplier
HZ_PARTIES

AP_SUPPLIERS

Supplier Sites

HZ_LOCATIONS

HZ_PARTY_SITES

AP_SUPPLIER_SITES_ALL

HZ_PARTY_USG_ASSIGNMENTS

IBY_EXTERNAL_PAYEES_ALL

IBY_EXT_PARTY_PMT_MTHDS

Validations Required in Supplier and Supplier Sitr Creation

Check for Vendor Existance: ap_suppliers.vendor_id
Check for Supplier Site Existance: ap_supplier_sites_all.vendor_site_id
Check for Invoice and Payment Currency: fnd_currencies.currency_code
Check for Code Combination Id: gl_code_combinations_kfv.code_combination_id
Check for Payment Terms: ap_terms_tl.term_id
Check for Payment Method: ap_lookup_codes.lookup_type = 'PAYMENT METHOD'
Check for Employee Id: per_all_people_f.person_id
Check for Country Code: fnd_territories.territory_code

Procedure:

PROCEDURE create_supplier

AS

CURSOR lcu_supplier

IS

SELECT supplier_name, supplier_alternate_name, supplier_number,

supplier_classification, employee_id, wht, country, region,

city, po_box, postal_code, supplier_site, address_line2,

address_line3, pay_term, term_base, pay_method, si_p_curr,

con_last_name, con_middle_name, con_first_name, phone, fax,

email_add

FROM staging_table;

lr_supplier_rec ap_vendor_pub_pkg.r_vendor_rec_type;

lr_supplier_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;

lr_supplier_contact ap_vendor_pub_pkg.r_vendor_contact_rec_type;

BEGIN

FOR lcsr_supp IN lcu_supplier

LOOP
--Validate according to the above validation

ln_msg_count := 0; --for error message count

lr_supplier_rec.segment1 := lcsr_supp.supplier_number;

lr_supplier_rec.vendor_name := lcsr_supp.supplier_name;

lr_supplier_rec.vendor_type_lookup_code := --supplier type

lr_supplier_rec.terms_id := --term id

lr_supplier_rec.set_of_books_id := --set of books id;

lr_supplier_rec.payment_currency_code := --currency code;

lr_supplier_rec.invoice_currency_code := --currency code

ap_vendor_pub_pkg.create_vendor

(p_api_version => 1.0,

p_init_msg_list => fnd_api.g_false,

p_commit => fnd_api.g_false,

p_validation_level => fnd_api.g_valid_level_full,

x_return_status => lc_api_return_status,

x_msg_count => ln_msg_count, --error message count

x_msg_data => lc_msg_data, --error message

p_vendor_rec => lr_supplier_rec,

x_vendor_id => ln_vendor_id, --vendor id created

x_party_id => ln_party_id --party id created

);

IF ( lc_api_return_status = fnd_api.g_ret_sts_success

AND lc_msg_data IS NULL

)

THEN

DBMS_OUTPUT.put_line ('Successful creation Of Supplier: ');

ELSE

IF ln_msg_count >= 1

THEN

FOR v_n_i IN 1 .. ln_msg_count

LOOP

pa_interface_utils_pub.get_messages

(p_msg_data => lc_msg_data,

p_encoded => 'F',

p_msg_index => ln_msg_count,

p_data => lc_msg_data,

p_msg_count => ln_msg_count,

p_msg_index_out => v_n_msg_index_out

);

DBMS_OUTPUT.put_line ( 'ln_msg_count '

|| ln_msg_count

|| '. lc_msg_data '

|| lc_msg_data

);

END LOOP;

END IF;

END IF;

--call api for vendor site

IF lc_error_flag = 'N'

THEN

ln_msg_count := 0;

lc_api_return_status := NULL;

lc_msg_data := NULL;

lr_supplier_site_rec.phone := lcsr_supp.phone;

lr_supplier_site_rec.fax := lcsr_supp.fax;

lr_supplier_site_rec.terms_id := ln_term_id;

lr_supplier_site_rec.vendor_id := ln_vendor_id_api;

lr_supplier_site_rec.vendor_site_code := lcsr_supp.supplier_site;

lr_supplier_site_rec.org_id := ln_org_id;

lr_supplier_site_rec.country := lc_country_code;

lr_supplier_site_rec.address_line1 := lcsr_supp.address_line2;

lr_supplier_site_rec.address_line2 := lcsr_supp.address_line3;

lr_supplier_site_rec.county := lc_country_code;

lr_supplier_site_rec.city := lcsr_supp.city;

lr_supplier_site_rec.default_terms_id := ln_term_id;

lc_api_return_status := NULL;

v_n_msg_index_out := NULL;

ap_vendor_pub_pkg.create_vendor_site

(p_api_version => 1.0,

p_init_msg_list => fnd_api.g_false,

p_commit => fnd_api.g_false,

p_validation_level => fnd_api.g_valid_level_full,

x_return_status => lc_api_return_status,

x_msg_count => ln_msg_count,

x_msg_data => lc_msg_data,

p_vendor_site_rec => lr_supplier_site_rec,

x_vendor_site_id => ln_vendor_site_id,

x_party_site_id => ln_party_site_id,

x_location_id => ln_location_id

);

IF ( lc_api_return_status = fnd_api.g_ret_sts_success

AND lc_msg_data IS NULL

)

THEN

DBMS_OUTPUT.put_line

('Successful creation Of Supplier Site: ln_vendor_site_id: ');

ELSE

IF ln_msg_count >= 1

THEN

FOR v_n_i IN 1 .. ln_msg_count

LOOP

pa_interface_utils_pub.get_messages

(p_msg_data => lc_msg_data,

p_encoded => 'F',

p_msg_index => ln_msg_count,

p_data => lc_msg_data,

p_msg_count => ln_msg_count,

p_msg_index_out => v_n_msg_index_out

);

END LOOP;

END IF;

END IF;

END IF;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

NULL;

--display message you want

END create_supplier;