create or replace package XX_SUP_DTL_CRT_PKG is
procedure XX_SUPPLIER_CREATION(p_vendor_name varchar2,
p_type_lookup_code varchar2,
p_vendor_name_alt varchar2,
x_vendor_id out nocopy number);
procedure XX_SUPPLIER_SITE_CREATION(p_vendor_site_code varchar2,
p_address_line1 varchar2,
p_address_line2 varchar2,
p_address_line3 varchar2,
p_address_line4 varchar2,
p_country varchar2,
p_org_id number,
p_city varchar2,
p_state varchar2,
p_province varchar2,
p_zip varchar2,
p_pay_site_flag varchar2,
p_purchasing_site_flag varchar2,
p_area_code varchar2,
p_site_phone varchar2,
p_fax_area_code varchar2,
p_fax varchar2,
p_site_email_add varchar2,
p_allow_awt_flag varchar2,
p_vat_registration_num number,
p_vat_code varchar2,
p_hold_all_payments_flag varchar2,
p_hold_unmatch_inv_flag varchar2,
p_hold_reason varchar2,
p_pay_group_lookup_code varchar2,
p_chque_type varchar2,
p_vendor_id number,
x_vendor_site_id out nocopy number);
Procedure XX_SUPPLIER_CONTACT_CREATION(p_org_id number,
p_prefix varchar2,
p_person_title varchar2,
p_person_first_name varchar2,
p_person_middle_name varchar2,
p_person_last_name varchar2,
p_phone varchar2,
p_email_address varchar2,
p_department varchar2,
p_fax_phone varchar2,
p_alt_phone varchar2,
p_vendor_id number,
p_vendor_site_id number,
x_vendor_contact_id out nocopy number);
procedure UPLOAD_MAIN( -- header details
p_vendor_name varchar2,
p_type_lookup_code varchar2,
p_vendor_name_alt varchar2,
--site details
p_vendor_site_code varchar2,
p_address_line1 varchar2,
p_address_line2 varchar2,
p_address_line3 varchar2,
p_address_line4 varchar2,
p_country varchar2,
p_org_id number,
p_purchasing_site_flag varchar2,
p_area_code varchar2,
p_site_phone varchar2,
p_fax_area_code varchar2,
p_fax varchar2,
p_site_email_add varchar2,
p_allow_awt_flag varchar2,
p_vat_registration_num number,
p_vat_code varchar2,
p_hold_all_payments_flag varchar2,
p_hold_unmatch_inv_flag varchar2,
p_hold_reason varchar2,
p_pay_group_lookup_code varchar2,
p_city varchar2,
p_state varchar2,
p_province varchar2,
p_zip varchar2,
p_pay_site_flag varchar2,
p_chque_type varchar2,
-- contact details
p_prefix varchar2,
p_person_title varchar2,
p_person_first_name varchar2,
p_person_middle_name varchar2,
p_person_last_name varchar2,
p_phone varchar2,
p_email_address varchar2,
p_department varchar2,
p_fax_phone varchar2,
p_alt_phone varchar2,
x_vendor_id out nocopy number,
x_vendor_site_id out nocopy number,
x_vendor_contact_id out nocopy number);
end XX_SUP_DTL_CRT_PKG ;
========================== X X ================================
create or replace package body XX_SUP_DTL_CRT_PKG is
procedure XX_SUPPLIER_CREATION(p_vendor_name varchar2,
p_type_lookup_code varchar2,
p_vendor_name_alt varchar2,
x_vendor_id out nocopy number) as
p_api_version NUMBER;
p_init_msg_list VARCHAR2(200);
p_commit VARCHAR2(200);
p_validation_level NUMBER;
x_return_status VARCHAR2(200);
x_msg_count NUMBER;
x_msg_data VARCHAR2(200);
l_vendor_rec apps.ap_vendor_pub_pkg.r_vendor_rec_type;
x_party_id NUMBER;
v_msg_index_out NUMBER;
BEGIN
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 := NULL;
x_msg_count := NULL;
x_msg_data := NULL;
l_vendor_rec.vendor_name := p_vendor_name;
l_vendor_rec.vendor_type_lookup_code := p_type_lookup_code;
l_vendor_rec.vendor_name_alt := p_vendor_name_alt;
l_vendor_rec.women_owned_flag := 'N';
l_vendor_rec.small_business_flag := 'Y';
x_vendor_id := NULL;
x_party_id := NULL;
-- Attribute Details
/*l_vendor_rec.attribute_category := p_attribute_category;
l_vendor_rec.attribute1 := p_attribute1;
l_vendor_rec.attribute2 := p_attribute2;
l_vendor_rec.attribute3 := p_attribute3;
l_vendor_rec.attribute4 := p_attribute4;
l_vendor_rec.attribute5 := p_attribute5;
l_vendor_rec.attribute6 := p_attribute6;
l_vendor_rec.attribute7 := p_attribute7;
l_vendor_rec.attribute8 := p_attribute8;
l_vendor_rec.attribute9 := p_attribute9;
l_vendor_rec.attribute10 := p_attribute10;
l_vendor_rec.attribute11 := p_attribute11;
l_vendor_rec.attribute12 := p_attribute12;
l_vendor_rec.attribute13 := p_attribute13;
l_vendor_rec.attribute14 := p_attribute14;
l_vendor_rec.attribute15 := p_attribute15;*/
-- Other values
ap_vendor_pub_pkg.create_vendor(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
-- p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_rec => l_vendor_rec,
x_vendor_id => x_vendor_id,
x_party_id => x_party_id);
--commit;
dbms_output.put_line('X_RETURN_STATUS = ' || x_return_status);
dbms_output.put_line('X_MSG_COUNT = ' || to_char(x_msg_count));
dbms_output.put_line('X_MSG_DATA = ' || x_msg_data);
dbms_output.put_line('X_VENDOR_ID = ' || to_char(x_vendor_id));
dbms_output.put_line('X_PARTY_ID = ' || to_char(x_party_id));
dbms_output.put_line('');
IF x_msg_count > 0 THEN
FOR v_index IN 1 .. x_msg_count LOOP
fnd_msg_pub.get(p_msg_index => v_index,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => v_msg_index_out);
x_msg_data := SUBSTR(x_msg_data, 1, 200);
dbms_output.put_line(x_msg_data);
END LOOP;
END IF;
END XX_SUPPLIER_CREATION;
Procedure XX_SUPPLIER_SITE_CREATION(p_vendor_site_code varchar2,
p_address_line1 varchar2,
p_address_line2 varchar2,
p_address_line3 varchar2,
p_address_line4 varchar2,
p_country varchar2,
p_org_id number,
p_city varchar2,
p_state varchar2,
p_province varchar2,
p_zip varchar2,
p_pay_site_flag varchar2,
p_purchasing_site_flag varchar2,
p_area_code varchar2,
p_site_phone varchar2,
p_fax_area_code varchar2,
p_fax varchar2,
p_site_email_add varchar2,
p_allow_awt_flag varchar2,
p_vat_registration_num number,
p_vat_code varchar2,
p_hold_all_payments_flag varchar2,
p_hold_unmatch_inv_flag varchar2,
p_hold_reason varchar2,
p_pay_group_lookup_code varchar2,
p_chque_type varchar2,
p_vendor_id number,
x_vendor_site_id out nocopy number) as
l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_return_status VARCHAR2(100);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_party_site_id NUMBER;
l_location_id NUMBER;
l_msg_index_out NUMBER;
p_api_version NUMBER;
p_init_msg_list VARCHAR2(200);
p_commit VARCHAR2(200);
p_validation_level NUMBER;
BEGIN
--Required
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_vendor_site_id := null;
l_vendor_site_rec.vendor_id := p_vendor_id; -- '10013890';
l_vendor_site_rec.vendor_site_code := p_vendor_site_code;
l_vendor_site_rec.address_line1 := p_address_line1;
--Optional
l_vendor_site_rec.address_line2 := p_address_line2;
l_vendor_site_rec.address_line3 := p_address_line3;
l_vendor_site_rec.address_line4 := p_address_line4;
l_vendor_site_rec.country := p_country;
l_vendor_site_rec.org_id := p_org_id;
l_vendor_site_rec.city := p_city;
l_vendor_site_rec.state := p_state;
l_vendor_site_rec.province := p_province;
l_vendor_site_rec.zip := p_zip;
l_vendor_site_rec.purchasing_site_flag := p_purchasing_site_flag; --'Y';
l_vendor_site_rec.pay_site_flag := p_pay_site_flag; --'Y';
l_vendor_site_rec.rfq_only_site_flag := 'N';
l_vendor_site_rec.area_code := p_area_code;
l_vendor_site_rec.phone := p_site_phone; --'0567072954';
l_vendor_site_rec.fax_area_code := p_fax_area_code;
l_vendor_site_rec.fax := p_fax;
l_vendor_site_rec.email_address := p_site_email_add;
l_vendor_site_rec.allow_awt_flag := p_allow_awt_flag;
l_vendor_site_rec.vat_registration_num := p_vat_registration_num;
l_vendor_site_rec.vat_code := p_vat_code;
l_vendor_site_rec.hold_all_payments_flag := p_hold_all_payments_flag; --
l_vendor_site_rec.hold_unmatched_invoices_flag := p_hold_unmatch_inv_flag;
l_vendor_site_rec.hold_reason := p_hold_reason;
l_vendor_site_rec.pay_group_lookup_code := p_pay_group_lookup_code;
l_vendor_site_rec.attribute2 := p_chque_type;
ap_vendor_pub_pkg.create_vendor_site(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
-- p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_vendor_site_rec => l_vendor_site_rec,
x_vendor_site_id => x_vendor_site_id,
x_party_site_id => l_party_site_id,
x_location_id => l_location_id);
--commit;
dbms_output.put_line('return_status: ' || l_return_status);
dbms_output.put_line('msg_data: ' || l_msg_data);
dbms_output.put_line('msg_count = ' || TO_CHAR(l_msg_count));
dbms_output.put_line('vendor_site_id: ' || x_vendor_site_id);
dbms_output.put_line('party_site_id: ' || l_party_site_id);
dbms_output.put_line('location_id: ' || l_location_id);
dbms_output.put_line('');
IF l_msg_count > 0 THEN
FOR v_index IN 1 .. l_msg_count LOOP
fnd_msg_pub.get(p_msg_index => v_index,
p_encoded => 'F',
p_data => l_msg_data,
p_msg_index_out => l_msg_index_out);
l_msg_data := SUBSTR(l_msg_data, 1, 200);
DBMS_OUTPUT.put_line(l_msg_data);
END LOOP;
END IF;
END XX_SUPPLIER_SITE_CREATION;
Procedure XX_SUPPLIER_CONTACT_CREATION(p_org_id number,
p_prefix varchar2,
p_person_title varchar2,
p_person_first_name varchar2,
p_person_middle_name varchar2,
p_person_last_name varchar2,
p_phone varchar2,
p_email_address varchar2,
p_department varchar2,
p_fax_phone varchar2,
p_alt_phone varchar2,
p_vendor_id number,
p_vendor_site_id number,
x_vendor_contact_id out nocopy number) as
l_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_per_party_id NUMBER;
l_rel_party_id NUMBER;
l_rel_id NUMBER;
l_org_contact_id NUMBER;
l_party_site_id NUMBER;
l_msg_index_out NUMBER;
p_api_version NUMBER;
p_init_msg_list VARCHAR2(200);
p_commit VARCHAR2(200);
p_validation_level NUMBER;
BEGIN
-- Required
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_vendor_contact_id := null;
l_vendor_contact_rec.vendor_site_id := p_vendor_site_id;
l_vendor_contact_rec.vendor_id := p_vendor_id;
l_vendor_contact_rec.org_id := p_org_id;
l_vendor_contact_rec.person_first_name := p_person_first_name;
l_vendor_contact_rec.person_last_name := p_person_last_name;
-- Optional
l_vendor_contact_rec.prefix := p_prefix;
l_vendor_contact_rec.person_title := p_person_title;
l_vendor_contact_rec.person_middle_name := p_person_middle_name;
l_vendor_contact_rec.email_address := p_email_address;
l_vendor_contact_rec.department := p_department;
l_vendor_contact_rec.fax_phone := p_fax_phone;
l_vendor_contact_rec.phone := p_phone;
l_vendor_contact_rec.alt_phone := p_alt_phone;
ap_vendor_pub_pkg.create_vendor_contact(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
-- p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_vendor_contact_rec => l_vendor_contact_rec,
x_vendor_contact_id => x_vendor_contact_id,
x_per_party_id => l_per_party_id,
x_rel_party_id => l_rel_party_id,
x_rel_id => l_rel_id,
x_org_contact_id => l_org_contact_id,
x_party_site_id => l_party_site_id);
--commit;
dbms_output.put_line('return_status: ' || l_return_status);
dbms_output.put_line('msg_data: ' || l_msg_data);
dbms_output.put_line('vendor_contact_id: ' || x_vendor_contact_id);
dbms_output.put_line('party_site_id: ' || l_party_site_id);
dbms_output.put_line('per_party_id: ' || l_per_party_id);
dbms_output.put_line('rel_party_id: ' || l_rel_party_id);
dbms_output.put_line('rel_id: ' || l_rel_id);
dbms_output.put_line('org_contact_id: ' || l_org_contact_id);
dbms_output.put_line('p_vendor_site_id: ' || p_vendor_site_id);
IF l_msg_count > 0 THEN
FOR v_index IN 1 .. l_msg_count LOOP
fnd_msg_pub.get(p_msg_index => v_index,
p_encoded => 'F',
p_data => l_msg_data,
p_msg_index_out => l_msg_index_out);
l_msg_data := SUBSTR(l_msg_data, 1, 200);
DBMS_OUTPUT.put_line(l_msg_data);
END LOOP;
END IF;
END XX_SUPPLIER_CONTACT_CREATION;
procedure UPLOAD_MAIN( -- header details
p_vendor_name varchar2,
p_type_lookup_code varchar2,
p_vendor_name_alt varchar2,
--site details
p_vendor_site_code varchar2,
p_address_line1 varchar2,
p_address_line2 varchar2,
p_address_line3 varchar2,
p_address_line4 varchar2,
p_country varchar2,
p_org_id number,
p_purchasing_site_flag varchar2,
p_area_code varchar2,
p_site_phone varchar2,
p_fax_area_code varchar2,
p_fax varchar2,
p_site_email_add varchar2,
p_allow_awt_flag varchar2,
p_vat_registration_num number,
p_vat_code varchar2,
p_hold_all_payments_flag varchar2,
p_hold_unmatch_inv_flag varchar2,
p_hold_reason varchar2,
p_pay_group_lookup_code varchar2,
p_city varchar2,
p_state varchar2,
p_province varchar2,
p_zip varchar2,
p_pay_site_flag varchar2,
p_chque_type varchar2,
-- contact details
p_prefix varchar2,
p_person_title varchar2,
p_person_first_name varchar2,
p_person_middle_name varchar2,
p_person_last_name varchar2,
p_phone varchar2,
p_email_address varchar2,
p_department varchar2,
p_fax_phone varchar2,
p_alt_phone varchar2,
x_vendor_id out nocopy number,
x_vendor_site_id out nocopy number,
x_vendor_contact_id out nocopy number) as
BEGIN
XX_SUP_DTL_CRT_PKG.XX_SUPPLIER_CREATION(p_vendor_name,
p_type_lookup_code,
p_vendor_name_alt,
x_vendor_id);
XX_SUP_DTL_CRT_PKG.XX_SUPPLIER_SITE_CREATION(p_vendor_site_code,
p_address_line1,
p_address_line2,
p_address_line3,
p_address_line4,
p_country,
p_org_id,
p_city,
p_state,
p_province,
p_zip,
p_pay_site_flag,
p_purchasing_site_flag,
p_area_code,
p_site_phone,
p_fax_area_code,
p_fax,
p_site_email_add,
p_allow_awt_flag,
p_vat_registration_num,
p_vat_code,
p_hold_all_payments_flag,
p_hold_unmatch_inv_flag,
p_hold_reason,
p_pay_group_lookup_code,
p_chque_type,
x_vendor_id,
x_vendor_site_id);
XX_SUP_DTL_CRT_PKG.XX_SUPPLIER_CONTACT_CREATION(p_org_id,
p_prefix,
p_person_title,
p_person_first_name,
p_person_middle_name,
p_person_last_name,
p_phone,
p_email_address,
p_department,
p_fax_phone,
p_alt_phone,
x_vendor_id,
x_vendor_site_id,
x_vendor_contact_id);
END UPLOAD_MAIN;
end XX_SUP_DTL_CRT_PKG;