API Supplier Site Creation

API For supplier/vendor site creation in oracle r12 

DECLARE

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;

CURSOR C1 IS

--select * from XXPCL_SUPPLIER_LIB_PRE_ACT_V
--where 1=1
--and org_id = 141
----and  VENDOR_TYPE = '3RD PARTY'
----and  VENDOR_TYPE = 'CONSULTANT'
----and  VENDOR_TYPE  = 'VENDOR'
----and  VENDOR_TYPE  = 'EMPLOYEE'
--and  VENDOR_TYPE = 'TAX AUTHORITY'


/* 3RD PARTY   and  VENDOR  */
select '451075' VENDOR_ID,'PFCCL' SITE_CODE ,
'.'SITE_ADDRESS_LINE_1,
NULL SITE_ADDRESS_LINE_2,
NULL SITE_ADDRESS_LINE_3,
NULL SITE_CITY_TOWN,
NULL SITE_STATE,
NULL  SITE_PIN,
'141' OPERATING_UNIT,
'385665' LIABILITY_ACCOUNT,
'385633' PREPAYMENT_ACCOUNT
from dual;

/* EMPLOYEE */
--select '3763' VENDOR_ID,'OFFICE' SITE_CODE ,
--'.'SITE_ADDRESS_LINE_1,
--NULL SITE_ADDRESS_LINE_2,
--NULL SITE_ADDRESS_LINE_3,
--NULL SITE_CITY_TOWN,
--NULL SITE_STATE,
--NULL  SITE_PIN,
--'141' OPERATING_UNIT,
--'385660' LIABILITY_ACCOUNT,
--'385889' PREPAYMENT_ACCOUNT
--from dual;

/* CONSULTANT */
--select '401998' VENDOR_ID,'PFCCL' SITE_CODE ,
--'.'SITE_ADDRESS_LINE_1,
--NULL SITE_ADDRESS_LINE_2,
--NULL SITE_ADDRESS_LINE_3,
--NULL SITE_CITY_TOWN,
--NULL SITE_STATE,
--NULL  SITE_PIN,
--'141' OPERATING_UNIT,
--'385071' LIABILITY_ACCOUNT,
--'385633' PREPAYMENT_ACCOUNT
--from dual;

--select *
--from XXPCL_SUP_STAGING_TBL A
--where  1=1
----AND A.SUPPLIER_NAME= 'BSES Rajdhani Power Limited.'
--and exists ( select * from ap_suppliers Z where  Z.VENDOR_ID =  A.VENDOR_ID)
--and not exists ( select * from ap_supplier_sites_all X where X.VENDOR_ID =  A.VENDOR_ID and X.VENDOR_SITE_ID = A.VENDOR_SITE_ID) ;


BEGIN

  fnd_global.apps_initialize(0, 51572, 200);

  FOR I IN C1 LOOP

--Required
l_vendor_site_rec.vendor_id :=I.VENDOR_ID;
l_vendor_site_rec.vendor_site_code := I.SITE_CODE;
l_vendor_site_rec.address_line1 :=I.SITE_ADDRESS_LINE_1;
l_vendor_site_rec.address_line2 := I.SITE_ADDRESS_LINE_2;
l_vendor_site_rec.address_line3 :=I.SITE_ADDRESS_LINE_3;
--l_vendor_site_rec.address_line4 :=I.SITE_ADDRESS_LINE_4;
--l_vendor_site_rec.ADDRESS_LINES_ALT :=I.SITE_ROOM;
l_vendor_site_rec.CITY := I.SITE_CITY_TOWN;
l_vendor_site_rec.STATE :=I.SITE_STATE;
l_vendor_site_rec.ZIP :=I.SITE_PIN;
l_vendor_site_rec.country := 'IN';
l_vendor_site_rec.org_id := I.OPERATING_UNIT;
l_vendor_site_rec.ATTRIBUTE_CATEGORY:='Vendor Site Ref';
l_vendor_site_rec.ATTRIBUTE10 := I.SITE_CODE;
l_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID:= I.LIABILITY_ACCOUNT; -- Libility--
l_vendor_site_rec.PREPAY_CODE_COMBINATION_ID :=I.PREPAYMENT_ACCOUNT; -- Prepayment--

--Optional

l_vendor_site_rec.purchasing_site_flag:='Y';
l_vendor_site_rec.pay_site_flag :='Y';
l_vendor_site_rec.rfq_only_site_flag :='N';
l_vendor_site_rec.MATCH_OPTION :='R';
--l_vendor_site_rec.phone := '9876543210';

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
);

COMMIT;

dbms_output.put_line('return_status: '||l_return_status);
dbms_output.put_line('msg_data: '||l_msg_data);
dbms_output.put_line('vendor_site_id: '||l_vendor_site_id);
dbms_output.put_line('party_site_id: '||l_party_site_id);
dbms_output.put_line('location_id: '||l_location_id);

--UPDATE XXPCL_SUP_STAGING_TBL
--set VENDOR_SITE_ID = l_vendor_site_id,
--PARTY_SITE_ID = l_party_site_id,
--LOCATION_ID = l_location_id
--where VENDOR_ID = I.VENDOR_ID
--and VENDOR_STAGING_ID = I.VENDOR_STAGING_ID;

COMMIT;

END LOOP;

END;


  --POS_VENDOR_PUB_PKG.Update_Vendor_Site
  --POS_VENDOR_PUB_PKG.Create_Vendor_Contact