API and Script to Create Supplier Site


Below is the API and Script to Create Supplier Site in Oracle R12

— API to Create Supplier Site

DECLARE
l_vendor_site_rec      ap_vendor_pub_pkg.r_vendor_site_rec_type;
lc_return_status         VARCHAR2(10);
ln_msg_count            NUMBER;
lc_msg_data               VARCHAR2(1000);
ln_vendor_site_id     NUMBER;
ln_party_site_id         NUMBER;
ln_location_id            NUMBER;

cursor c is

select asup.vendor_id, assa.VENDOR_SITE_CODE,  hl.address1, hl.city, hl.country from ap_suppliers asup,  ap_supplier_sites_all assa, hz_parties hp,
hz_party_sites hps,  hz_locations hl where
asup.party_id =  hp.party_id
and assa.vendor_id = asup.vendor_id and
hp.party_id = hps.party_id and  hps.location_id = hl.location_id
and asup.vendor_id not in ( select vendor_id from ap_supplier_sites_all where org_id  in (564));       –29027

BEGIN
MO_GLOBAL.SET_POLICY_CONTEXT (‘S’, 567);

FND_GLOBAL.APPS_INITIALIZE ( USER_ID => 10835, RESP_ID => 51469, RESP_APPL_ID => 200 );
for i in c loop
— —————
— Required
— —————
l_vendor_site_rec.vendor_id               := i.vendor_id;
l_vendor_site_rec.vendor_site_code      := i.vendor_site_code;
l_vendor_site_rec.address_line1         := i.address1;
l_vendor_site_rec.city                           := i.city;
l_vendor_site_rec.country                    := i.country;
l_vendor_site_rec.org_id                      := 564;

— ————–
— Optional
— ————–
l_vendor_site_rec.purchasing_site_flag  :=’N’;
l_vendor_site_rec.pay_site_flag         :=’Y’;
l_vendor_site_rec.rfq_only_site_flag    :=’N’;

pos_vendor_pub_pkg.create_vendor_site
(
— ——————————
— Input data elements
— ——————————
p_vendor_site_rec    => l_vendor_site_rec,
— ———————————
— Output data elements
— ———————————
x_return_status         => lc_return_status,
x_msg_count             => ln_msg_count,
x_msg_data                => lc_msg_data,
x_vendor_site_id      => ln_vendor_site_id,
x_party_site_id         => ln_party_site_id,
x_location_id            => ln_location_id
);

—    insert into oaf_debug values(sysdate, 1, i.vendor_id||’Vendor ID and Return Status’||lc_return_status, ln_msg_count);

if (lc_return_status <> ‘S’) then
—                    dbms_output.put_line(‘Encountered ERROR in supplier site creation!!!’);
—                    dbms_output.put_line(‘————————————–‘);
—                    dbms_output.put_line(x_msg_data);
IF ln_msg_count > 1 THEN
FOR j IN 1..ln_msg_count LOOP
insert into oaf_debug values(sysdate, 1, i.vendor_id||’Vendor ID and Return Status’||lc_return_status, (substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255)));
END LOOP;
END IF;
ELSE
null;
—      dbms_output.put_line(‘Supplier Site Created!!!’);

end if;

end loop;
COMMIT;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Sometime you may end up in a Error like this : Operating unit access is not allowed.  Please check that you have access to the operating unit:

Operating Unit Info is null

In Case you get this Error check the Profile Option MO:Security Profile, MO: Operating Unit and dont forget to include fnd_global.apps_initialize.

 

About pacesettergraam

A good and realistic person

Posted on March 20, 2016, in Oracle, oracle apps and tagged , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: