Yadul Oracle Duniya: February 2020

create or replace package XX_SS_WEBADI_PKG is

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_organization varchar2,
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_name varchar2,
x_vendor_site_id out nocopy number);

end XX_SS_WEBADI_PKG;

======================================================================================================

create or replace package body XX_SS_WEBADI_PKG is

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_organization varchar2,
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_name varchar2,
x_vendor_site_id out nocopy number) as

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
lv_validation_exc EXCEPTION;
lv_error_message VARCHAR2(200);
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;
l_org_id NUMBER;
l_vendor_id NUMBER;

BEGIN

BEGIN
SELECT hou.organization_id
INTO l_org_id
FROM apps.hr_operating_units hou
WHERE UPPER(name) = UPPER(p_organization) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
XXEG_DEBUG_PKG.putLog(11, 'NO data Found for provided Organization ' || p_organization);
lv_error_message := 'NO data Found for provided Organization ' || p_organization ;
RAISE lv_validation_exc ;
WHEN OTHERS THEN
XXEG_DEBUG_PKG.putLog(11, 'Unhandled Exception for provided Organization ' || p_organization);
lv_error_message := 'Unhandled Exception for provided Organization ' || p_organization ;
RAISE lv_validation_exc ;
END;
BEGIN
SELECT aps.vendor_id
INTO l_vendor_id
FROM apps.ap_suppliers aps
WHERE UPPER(vendor_name) = UPPER(p_vendor_name) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
XXEG_DEBUG_PKG.putLog(11, 'NO data Found for provided Vendor ' || p_vendor_name);
lv_error_message := 'NO data Found for provided Vendor ' || p_vendor_name ;
RAISE lv_validation_exc ;
WHEN OTHERS THEN
XXEG_DEBUG_PKG.putLog(11, 'Unhandled Exception for provided Organization ' || p_vendor_name);
lv_error_message := 'Unhandled Exception for provided Organization ' || p_vendor_name ;
RAISE lv_validation_exc ;
END;

--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 := l_vendor_id;
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 := l_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;
l_vendor_site_rec.pay_site_flag := p_pay_site_flag;
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;
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;
XXEG_DEBUG_PKG.putLog(11, 'return_status ' || l_return_status);
XXEG_DEBUG_PKG.putLog(11, 'msg_data ' || l_msg_data);
XXEG_DEBUG_PKG.putLog(11, 'msg_count ' || TO_CHAR(l_msg_count));
XXEG_DEBUG_PKG.putLog(11, 'vendor_site_id ' || x_vendor_site_id);
XXEG_DEBUG_PKG.putLog(11, 'party_site_id ' || l_party_site_id);
XXEG_DEBUG_PKG.putLog(11, 'location_id ' || l_location_id);
XXEG_DEBUG_PKG.putLog(11, '');

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;

EXCEPTION
WHEN lv_validation_exc THEN
fnd_message.set_name('BNE', 'WEBADI_ERROR');
fnd_message.set_token('ERROR_MESSAGE', lv_error_message);
raise_application_error(-20228, lv_error_message);
WHEN OTHERS THEN
lv_error_message := 'Oracle ErrMsg: ' || sqlerrm;
raise_application_error(-20228, lv_error_message);
END XX_SUPPLIER_SITE_CREATION;

end XX_SS_WEBADI_PKG;

Yadul Oracle Duniya

Tuesday, February 11, 2020

Supplier Interface with APIs (Custom Interface)

SQL Query to find Customer, Customer Account and Customer Sites Information

Labels

Report Abuse

Pages