Oracle Apps R12 API to create Supplier, Supplier Site, Contact in Payables Package

Oracle Apps R12 API to create Supplier, Supplier Site, Contact in Payables for single Supplier in detail.

CREATE OR REPLACE package APPS.fuj_crt_supp_suppsite as
--parameters can be find more from below package, as the api is calling below package.
--ap_vendor_pub_pkg.create_vendor_contact
procedure create_supplier(l_segment1 VARCHAR2,l_vendor_name VARCHAR2,l_vendor_name_alt VARCHAR2 default null);
procedure create_supplier_site
(l_segment1 VARCHAR2,
l_supp_site_code VARCHAR2, --default value 'Fujairah'
l_address_line1 VARCHAR2 default null,
l_country VARCHAR2  ,
l_operating_unit VARCHAR2 ,
l_prepay_code_combination_id VARCHAR2,
l_email_address VARCHAR2  default null,
l_phone VARCHAR2  default null,
l_fax VARCHAR2  default null
) ;
procedure create_supp_contact
(
l_segment1 VARCHAR2,
l_person_first_name VARCHAR2,
l_person_last_name VARCHAR2,
l_operating_unit VARCHAR2,
l_email_address VARCHAR2,
l_phone VARCHAR2,
l_fax_phone VARCHAR2
);
end;
/


CREATE OR REPLACE package body APPS.fuj_crt_supp_suppsite as
procedure create_supplier(l_segment1 VARCHAR2,l_vendor_name VARCHAR2,l_vendor_name_alt VARCHAR2 default null)
AS
l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_vendor_id NUMBER;
l_party_id NUMBER;
--------------------------------local variables ----------------
--l_segment1 VARCHAR2(240) := 'Test4';
--l_vendor_name VARCHAR2(240) := 'Test4';
--l_vendor_name_alt VARCHAR2(240) := 'Test4';
--------------------------------------------------
BEGIN
--
-- Required
--
l_vendor_rec.segment1 := l_segment1;
l_vendor_rec.vendor_name := l_vendor_name;
--
-- Optional
--
l_vendor_rec.vendor_name_alt := l_vendor_name_alt;
l_vendor_rec.start_date_active := sysdate - 1;
-- etc.. --
pos_vendor_pub_pkg.create_vendor(
p_vendor_rec => l_vendor_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_id => l_vendor_id,
x_party_id => l_party_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_id: '||l_vendor_id);
dbms_output.put_line('party_id: '||l_party_id);
END create_supplier;


--Verify
/* select * from ap_suppliers where segment1 = 'Test1'
select * from hz_parties where party_id = 10167490
*/

procedure create_supplier_site
(l_segment1 VARCHAR2,
l_supp_site_code VARCHAR2, --default value 'Fujairah'
l_address_line1 VARCHAR2 default null,
l_country VARCHAR2  ,
l_operating_unit VARCHAR2 ,
l_prepay_code_combination_id VARCHAR2,
l_email_address VARCHAR2  default null,
l_phone VARCHAR2  default null,
l_fax VARCHAR2  default null
)
as
l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_vendor_site_id NUMBER;
l_party_site_id NUMBER;
l_location_id NUMBER;
--------------------------local variables--------
--l_segment1 varchar2(200) := 'Test4';
--l_supp_site_code varchar2(200):= 'Fujairah'; --dont change this
--l_address_line1 varchar2(200):= 'Address line 1, Fujairah';
--city varchar2(200):= 'Fujairah'; --no need
--state varchar2(200):= 'Fujairah'; --no need
--l_country varchar2(200):= 'AE';  --dont change this
--purchasing_site_flag varchar2(200):= 'Y'; --no need
--pay_site_flag varchar2(200):= 'Y'; --no need
--l_operating_unit varchar2(200) := 'Fujairah National Catering Est. FNCE'; --1770
--l_prepay_code_combination_id number := 3351157;
--l_email_address varchar2(200):= 'contact1@test.com';
--l_phone varchar2(200):= '+971555265290';
--l_fax varchar2(200):= '+971555265292';

BEGIN
--
-- Required
--

SELECT vendor_id
INTO l_vendor_site_rec.vendor_id
FROM ap_suppliers
WHERE
segment1 = l_segment1
;

l_vendor_site_rec.vendor_site_code := l_supp_Site_Code;
l_vendor_site_rec.vendor_site_code_alt := l_supp_Site_Code;
l_vendor_site_rec.address_line1 := l_address_line1;
--l_vendor_site_rec.city := City;
--l_vendor_site_rec.state := State;
l_vendor_site_rec.country := l_country;

select organization_id
INTO l_vendor_site_rec.org_id
from hr_operating_units
where upper(name) = upper(l_operating_unit)
;

l_vendor_site_rec.purchasing_site_flag := 'Y';
--l_vendor_site_rec.rfq_only_site_flag := 'N';
l_vendor_site_rec.pay_site_flag := 'Y';
l_vendor_site_rec.primary_pay_site_flag  := 'Y';
l_vendor_site_rec.prepay_code_combination_id := l_prepay_code_combination_id ;
l_vendor_site_rec.email_address   := l_email_address;
l_vendor_site_rec.phone   := l_phone;
l_vendor_site_rec.FAX   := l_fax;
--
-- Optional
--
--l_vendor_site_rec.phone := '123123123';
-- etc... --
pos_vendor_pub_pkg.create_vendor_site(
p_vendor_site_rec => l_vendor_site_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_site_id => l_vendor_site_id,
x_party_site_id => l_party_site_id,
x_location_id => l_location_id);
DBMS_OUTPUT.put_line(l_vendor_site_id);
DBMS_OUTPUT.put_line('message count: '||l_msg_count);
DBMS_OUTPUT.put_line('return status: '||l_return_status);
DBMS_OUTPUT.put_line(l_msg_data);
IF l_msg_count >1 THEN FOR I IN 1..l_msg_count LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP; END IF;
if (l_return_status = 'S') then
commit;
end if;
exception when others then
DBMS_OUTPUT.put_line('exception-'||sqlerrm);
end create_supplier_site;




procedure create_supp_contact
(
l_segment1 VARCHAR2,
l_person_first_name VARCHAR2,
l_person_last_name VARCHAR2,
l_operating_unit VARCHAR2,
l_email_address VARCHAR2,
l_phone VARCHAR2,
l_fax_phone VARCHAR2
)
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_vendor_contact_id NUMBER;
l_per_party_id NUMBER;
l_rel_party_id NUMBER;
l_rel_id NUMBER;
l_org_contact_id NUMBER;
l_party_site_id NUMBER;
--Variables:
/*l_vendor_name varchar2(200) := 'Test4';
l_person_first_name varchar2(200):= 'Afzal';
l_person_last_name varchar2(200):= 'Abdur Rahman';
l_operating_unit varchar2(200) := 'Fujairah National Catering Est. FNCE'; --1770
l_email_address varchar2(200):= 'contact1@test.com';
l_phone varchar2(200):= '+971555265290';
l_fax_phone varchar2(200):= '+971555265292';
*/
BEGIN
--
-- Required
--
SELECT vendor_id
INTO l_vendor_contact_rec.vendor_id
FROM ap_suppliers
WHERE segment1 = l_segment1;
select organization_id
INTO l_vendor_contact_rec.org_id
from hr_operating_units
where upper(name) = upper(l_operating_unit) ;
l_vendor_contact_rec.person_first_name := l_person_first_name;
l_vendor_contact_rec.person_last_name := l_person_last_name;
l_vendor_contact_rec.email_address   := l_email_address;
l_vendor_contact_rec.phone   := l_phone;
l_vendor_contact_rec.FAX_PHONE   := l_fax_phone;
--
-- Optional
--
--l_vendor_contact_rec.phone := '12343210';
--l_vendor_contact_rec.email_address := 'axyz@oracle.com';
-- etc... --
pos_vendor_pub_pkg.create_vendor_contact(
p_vendor_contact_rec => l_vendor_contact_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_contact_id => l_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);
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: '||l_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);
IF l_msg_count >=1 THEN FOR I IN 1..l_msg_count LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP; END IF;
if (l_return_status = 'S') then
commit;
end if;
exception when others then
dbms_output.put_line('exception-'||sqlerrm);
end create_supp_contact;


END fuj_crt_supp_suppsite;
/
---------------------------------------Interface Tables no ther info------------------------------------------

create table fuj_upload_int
(segment1 varchar2(30),
vendor_name varchar2(200),
address_line1 varchar2(200),
prepay_code_combination_id varchar2(200),
contact varchar2(200),
phone  varchar2(200),
fax varchar2(200),
email_address varchar2(200),
attribute1 varchar2(200),
attribute2 varchar2(200),
attribute3 varchar2(200),
attribute4 varchar2(200),
attribute5 varchar2(200))

Prepayment code combinationa:

001.0000.1110.00005.0000.0000  - 3351157
001.0000.1110.00000.0000.0000  - 3349192


INSERT INTO
fuj_upload_int
values('Test9'
,'Test9'
,'Address line 1'
,3349192
,'Afzal'
,'+971555'
,'+971555'
,'afzalbaig@xx.fuja.ae'
,null
,null
,null
,null
,null)

commit

--run each procedure at once to validate each and all
declare
cursor c1 is
select * from fuj_upload_int;
begin
for i in c1 loop
fuj_crt_supp_suppsite.create_supplier(i.segment1 ,i.vendor_name ,i.vendor_name  );
--fuj_crt_supp_suppsite.create_supplier_site(i.segment1,'Fujairah',i.address_line1,'AE',' National Est. FNCE',i.PREPAY_CODE_COMBINATION_ID,i.email_address,i.PHONE,i.FAX);
--fuj_crt_supp_suppsite.create_supp_contact(i.segment1, i.contact,null,'Est. FNCE',i.email_address,i.PHONE,i.FAX);
end loop;
exception when others then
dbms_output.put_line('exception-'||sqlerrm);
end;