supplier creation API – Duh! Microsoft did it again

Hi guys

I’m posting a sample script for creating suppliers, sites and contacts. I’ve referred multiple sample scripts and believe the below code block is a fine tuned one, however standing refinement at all levels. Please note, I haven’t added the API block for creating banks for suppliers. Will, and update the scripts as I make advancements.

[code language=”sql” gutter=”false”]
/* Formatted on 10/5/2015 11:12:16 AM (QP5 v5.163.1008.3004) */
SET DEFINE OFF;
SET SERVEROUTPUT ON;

DECLARE
–For supplier parameters

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);
p_vendor_rec apps.ap_vendor_pub_pkg.r_vendor_rec_type;
x_vendor_id NUMBER;
x_party_id NUMBER;
V_MSG_INDEX_OUT NUMBER;

–Site parameters

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;

–Contact parameters

p_vendor_contact_rec apps.ap_vendor_pub_pkg.r_vendor_contact_rec_type;
x_vendor_contact_id NUMBER;
x_per_party_id NUMBER;
x_rel_party_id NUMBER;
x_rel_id NUMBER;
x_org_contact_id NUMBER;
x_party_site_id NUMBER;

–General exception

local_exception EXCEPTION;
local_failed_at VARCHAR2 (10);

p_vendor_number VARCHAR2(30) := NULL;

BEGIN
–Please note: This API was tested against Release 12 (12.0.6)
–You are warned against undesired results, if tried against unsupported application releases

–Initialize application
–"Master Data" responsibility details
mo_global.init (‘SQLAP’);
fnd_global.apps_initialize (user_id => 1353,
resp_id => 50997,
resp_appl_id => 200);
fnd_global.set_nls_context (‘AMERICAN’);

mo_global.set_policy_context (‘S’, 101);

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;
p_vendor_rec.vendor_name := ‘WINDOWS7BUGS BLOG’;
p_vendor_rec.vendor_type_lookup_code := ‘VENDOR’; –Vendor type supplier
p_vendor_rec.SUMMARY_FLAG := ‘N’;
p_vendor_rec.ENABLED_FLAG := ‘Y’;
— p_vendor_rec.women_owned_flag := ‘N’;
— p_vendor_rec.small_business_flag := ‘Y’;

— Supplier MUST have a global level payment method
— So that individual companies can defer the default payment method while sites are created
— I have tried the following @ site levels, didn’t work until at supplier level assigned. You may post corrections with
— Comments section

p_vendor_rec.ext_payee_rec.Exclusive_Pay_Flag:=’N’;
p_vendor_rec.ext_payee_rec.default_pmt_method := ‘CHECK’;

— if the Payable System setup is set automatic numbering for the suppliers (table ->AP_PRODUCT_SETUP Column -> SUPPLIER_NUMBERING_METHOD = ‘AUTOMATIC’)
— You can get the next number from column NEXT_AUTO_SUPPLIER_NUM
— if you are following manual numbering (Alpha Numeric )
— p_vendor_rec.segment1 :=’865′; –(insert non duplicate number, in case if the supplier numbers are not fetched from a sequence, check your setups)

— We do have an automatic numbering for suppliers, hence the below block is used
— If your setups are not as explained above
— Comment from BEING until p_vendor_rec.segment1 := p_vendor_number;

BEGIN
Select NEXT_AUTO_SUPPLIER_NUM into p_vendor_number from AP_PRODUCT_SETUP
where SUPPLIER_NUMBERING_METHOD= ‘AUTOMATIC’;
EXCEPTION
WHEN NO_DATA_FOUND then
local_failed_at := ‘NUMBER’;
RAISE local_exception;
END;

p_vendor_rec.segment1 := p_vendor_number;

x_vendor_id := NULL;
x_party_id := NULL;
apps.ap_vendor_pub_pkg.create_vendor (p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
x_return_status,
x_msg_count,
x_msg_data,
p_vendor_rec,
x_vendor_id,
x_party_id);
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 (‘Supplier Number = ‘ || p_vendor_number);
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_return_status <> ‘S’
THEN
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;

local_failed_at := ‘SUPPLIER’;
RAISE local_exception;
END IF;

–Create Site
l_vendor_site_rec.vendor_id := x_vendor_id; — 1117549;
l_vendor_site_rec.vendor_site_code := ‘Kuwait’;
l_vendor_site_rec.address_line1 := ‘Office Address line 1’;
l_vendor_site_rec.city := ‘Kuwait’;
l_vendor_site_rec.country := ‘KW’;
l_vendor_site_rec.org_id := 101;

l_vendor_site_rec.ext_payee_rec.default_pmt_method := ‘CHECK’;

— ————–
— 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’;

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

IF (lc_return_status <> ‘S’)
THEN
IF ln_msg_count > 1
THEN
FOR i IN 1 .. ln_msg_count
LOOP
DBMS_OUTPUT.put_line (
SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255));
END LOOP;
END IF;

local_failed_at := ‘SITE’;
RAISE local_exception;
ELSE
DBMS_OUTPUT.put_line (‘Vendor Site Id: ‘ || ln_vendor_site_id);
DBMS_OUTPUT.put_line (‘Party Site Id: ‘ || ln_party_site_id);
DBMS_OUTPUT.put_line (‘Location Id: ‘ || ln_location_id);
END IF;

–Create Contact

p_api_version := 1.0;
p_init_msg_list := ‘T’;
p_commit := ‘T’;
p_validation_level := FND_API.G_VALID_LEVEL_FULL;
x_return_status := NULL;
x_msg_count := NULL;
x_msg_data := NULL;

— p_vendor_contact_rec.vendor_contact_id := po_vendor_contacts_s.NEXTVAL;
— DBMS_OUTPUT.put_line (‘po_vendor_contacts_s.NEXTVAL = ‘ || po_vendor_contacts_s.NEXTVAL);

— P_VENDOR_CONTACT_REC.vendor_site_id := ln_vendor_site_id; –OPTIONAL If you want to attach the contact to a particular site
P_VENDOR_CONTACT_REC.PERSON_FIRST_NAME := ‘windows7bugs’;
P_VENDOR_CONTACT_REC.PERSON_LAST_NAME := ‘blog’; — Mandatory
P_VENDOR_CONTACT_REC.PHONE := ‘22445566’;
P_VENDOR_CONTACT_REC.EMAIL_ADDRESS := ‘admin@nocom.com.kw’;
P_VENDOR_CONTACT_REC.URL := ‘http://windows7bugs.wordpress.com’;
P_VENDOR_CONTACT_REC.org_id := 101; — Security Organization Id
p_vendor_contact_rec.party_site_id := ln_party_site_id;
— p_vendor_contact_rec.org_party_site_id := 2273595; –optional, system autofills the column with party_site_id used
p_vendor_contact_rec.VENDOR_ID := x_vendor_id;
p_vendor_contact_rec.prefix := ‘MR.’;
x_vendor_contact_id := NULL;
x_per_party_id := NULL;
x_rel_party_id := NULL;
x_rel_id := NULL;
x_org_contact_id := NULL;
x_party_site_id := NULL;
apps.ap_vendor_pub_pkg.create_vendor_contact (p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
x_return_status,
x_msg_count,
x_msg_data,
p_vendor_contact_rec,
x_vendor_contact_id,
x_per_party_id,
x_rel_party_id,
x_rel_id,
x_org_contact_id,
x_party_site_id);

local_failed_at := ‘CONTACT’;
RAISE local_exception;
ELSE
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_CONTACT_ID = ‘ || TO_CHAR (x_vendor_contact_id));
DBMS_OUTPUT.put_line (‘X_PER_PARTY_ID = ‘ || TO_CHAR (x_per_party_id));
DBMS_OUTPUT.put_line (‘X_REL_PARTY_ID = ‘ || TO_CHAR (x_rel_party_id));
DBMS_OUTPUT.put_line (‘X_REL_ID = ‘ || TO_CHAR (x_rel_id));
DBMS_OUTPUT.put_line (
‘X_ORG_CONTACT_ID = ‘ || TO_CHAR (x_org_contact_id));
DBMS_OUTPUT.put_line (
‘X_PARTY_SITE_ID = ‘ || TO_CHAR (x_party_site_id));
DBMS_OUTPUT.put_line (”);
END IF;

COMMIT;

EXCEPTION
WHEN local_exception
THEN
IF local_failed_at = ‘SUPPLIER’
THEN
DBMS_OUTPUT.put_line (‘API failed at Supplier Creation’);
ELSIF local_failed_at = ‘SITE’
THEN
DBMS_OUTPUT.put_line (‘API failed at Site Creation’);
ELSIF local_failed_at = ‘CONTACT’
THEN
DBMS_OUTPUT.put_line (‘API failed at Contact Creation’);
ELSIF local_failed_at = ‘NUMBER’
THEN
DBMS_OUTPUT.put_line (‘API failed at getting Supplier Number’);
END IF;

ROLLBACK;
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
ROLLBACK;
END;
[/code]

You can download the .sql file from here

Please post your comments, if you come across issues.

regards,

	windows7bugs on Oracle 19c \| Install sample sc…
	windows7bugs on Oracle 19c \| Install sample sc…
	Bin Wang on Oracle 19c \| Install sample sc…
	windows7bugs on Install Oracle forms & rep…
	sanjay on Install Oracle forms & rep…

Duh! Microsoft did it again

Tag: supplier creation API

Oracle Application TCA | Supplier API | Sample