Oracle EBS, Fusion and OIC: September 2014

DECLARE

l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
l_return_status VARCHAR2(30);

l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_vendor_site_id NUMBER;
l_party_site_id NUMBER;
l_location_id NUMBER;

BEGIN
----Required field for API for ap_vendor_pub_pkg.create_vendor_site

l_vendor_site_rec.vendor_id := 51171;
l_vendor_site_rec.vendor_site_code := 'DEMO_SITE';
l_vendor_site_rec.address_line1 := '3RD STREET LA';
l_vendor_site_rec.city := 'New York';
l_vendor_site_rec.country := 'US';
l_vendor_site_rec.org_id := 204;

----Optional field

l_vendor_site_rec.purchasing_site_flag := 'N';
l_vendor_site_rec.pay_site_flag := 'N';
l_vendor_site_rec.rfq_only_site_flag := 'N';

--

pos_vendor_pub_pkg.create_vendor_site
(--input parameter
p_vendor_site_rec => l_vendor_site_rec,
--output parameter
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);

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

output
-------------------------------------------------------------------------------------------------------------------------------

return_status: S
msg_data:
vendor_site_id: 21911
party_site_id: 246632
location_id: 27339
---------------------------------------------------------------------------------------------------------------------------------

You will get data in base table using below query.

select * from ap_supplier_sites_all

where vendor_site_id=21911
---------------------------------------------------------------------------------------------
select * from hz_party_siteswhere party_site_id=246632
-------------------------------------------------------------------------------------------------
select * from hz_locationswhere location_id=27339
-----------------------------------------------------------------------------------------------

The following sample scripts illustrate APIs for integrating third party applications

with Oracle Supplier Hub:

• Create Supplier

• Create Supplier Site

• Create Supplier Contact

• Update Supplier

• Update Supplier Site

• Validate Supplier

• Create User-defined Attribute

• Query User-defined Attribute

• Get Supplier Publication History

• Create Supplier Publication Event Response

Create Supplier

DECLARE

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;

BEGIN

-- Required

l_vendor_rec.segment1 := '12345001'; -- Supplier Number

l_vendor_rec.vendor_name := 'Demo001';

-- Optional

l_vendor_rec.vendor_name_alt := 'DEMO001';

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 Site

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;

BEGIN

-- Required

SELECT vendor_id

INTO l_vendor_site_rec.vendor_id

FROM pos_po_vendors_v

WHERE vendor_name = 'Demo001';

l_vendor_site_rec.vendor_site_code := 'Site001';

l_vendor_site_rec.address_line1 := '300 Oracle Parkway';

l_vendor_site_rec.city := 'Redwood City';

l_vendor_site_rec.state := 'CA';

l_vendor_site_rec.country := 'US';

l_vendor_site_rec.org_id := '204';

-- Optional

l_vendor_site_rec.phone := '6505066486';

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

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

END;

/

-----------------------------------------------------------------------------------------------

Create Supplier Contact

DECLARE

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;

BEGIN

-- Required

SELECT vendor_id

INTO l_vendor_contact_rec.vendor_id

FROM pos_po_vendors_v

WHERE vendor_name = 'Demo001';

l_vendor_contact_rec.org_id := '204';

l_vendor_contact_rec.person_first_name := 'B';

l_vendor_contact_rec.person_last_name := 'Smith';

-- Optional

l_vendor_contact_rec.phone := '6505066486';

l_vendor_contact_rec.email_address := 'bso@us.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);

COMMIT;

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

END;

/

----------------------------------------------------------------------------------------------------

Update Supplier

DECLARE

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;

BEGIN

-- Required

SELECT vendor_id

INTO l_vendor_rec.vendor_id

FROM pos_po_vendors_v

WHERE vendor_name = 'Demo001';

-- _______________________________________________________

-- | |

-- | NOTE: Name and Alt Name Cannot Be Updated By This API |

-- |_______________________________________________________|

-- Optional

l_vendor_rec.customer_num := '101';

-- etc.. --

pos_vendor_pub_pkg.update_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);

COMMIT;

dbms_output.put_line('return_status: '||l_return_status);

dbms_output.put_line('msg_data: '||l_msg_data);

END;

/

--------------------------------------------------------------------------------------------

Update Supplier Site

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

BEGIN

-- Required

SELECT vendor_id

INTO l_vendor_site_rec.vendor_id

FROM pos_po_vendors_v

WHERE vendor_name = 'Office Supplies, Inc.';

SELECT vendor_site_id

INTO l_vendor_site_rec.vendor_site_id

FROM ap_supplier_sites

WHERE vendor_id = l_vendor_site_rec.vendor_id AND

vendor_site_code = 'NEW YORK';

-- l_vendor_site_rec.org_id := '204';

l_vendor_site_rec.address_line1 := '3605 Center Road Changed';

l_vendor_site_rec.address_line2 := 'Line Two Changed';

l_vendor_site_rec.address_line3 := 'Line Three Changed';

l_vendor_site_rec.address_line4 := 'Line Four Changed';

l_vendor_site_rec.zip := '12345';

-- etc... --

pos_vendor_pub_pkg.update_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);

COMMIT;

dbms_output.put_line('return_status: '||l_return_status);

dbms_output.put_line('msg_data: '||l_msg_data);

END;

/

-------------------------------------------------------------------------------------------------------

Validate Supplier

DECLARE

l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;

l_mode VARCHAR2(1);

l_party_valid VARCHAR2(1);

l_return_status VARCHAR2(10);

l_msg_count NUMBER;

l_msg_data VARCHAR2(1000);

BEGIN

l_mode := 'U';

SELECT vendor_id

INTO l_vendor_rec.vendor_id

FROM pos_po_vendors_v

WHERE vendor_name = 'Demo001';

-- l_vendor_rec.segment1 := '12345001'; -- Supplier Number

-- l_vendor_rec.vendor_name := 'Demo001';

-- l_vendor_rec.vendor_name_alt := 'DEMO001';

-- etc... --

pos_vendor_pub_pkg.validate_vendor(

p_vendor_rec => l_vendor_rec,

p_mode => l_mode,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

x_party_valid => l_party_valid);

dbms_output.put_line('return_status: '||l_return_status);

dbms_output.put_line('msg_data: '||l_msg_data);

dbms_output.put_line('party_valid: '||l_party_valid);

END;

/

---------------------------------------------------------------------------------------------------

Create User-defined Attribute

DECLARE

l_party_id NUMBER;

l_attr_group_id NUMBER;

l_previous NUMBER := 0;

l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;

l_request_table EGO_ATTR_GROUP_REQUEST_TABLE :=

EGO_ATTR_GROUP_REQUEST_TABLE();

l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;

l_attributes_data_table EGO_USER_ATTR_DATA_TABLE;

l_return_status VARCHAR2(100);

l_errorcode NUMBER;

l_msg_count NUMBER;

l_msg_data VARCHAR2(4000);

BEGIN

SELECT party_id

INTO l_party_id

FROM ap_suppliers

WHERE vendor_name = 'Office Supplies, Inc.';

SELECT attr_group_id

INTO l_attr_group_id

FROM ego_attr_groups_v

WHERE attr_group_disp_name = 'Business Information';

-- Primary key value pairs

l_pk_column_values :=

EGO_COL_NAME_VALUE_PAIR_ARRAY(

EGO_COL_NAME_VALUE_PAIR_OBJ('PARTY_ID', l_party_id)

);

-- Attribute group object

l_request_table.extend;

l_request_table(l_request_table.LAST) := EGO_ATTR_GROUP_REQUEST_OBJ(

l_attr_group_id --ATTR_GROUP_ID

,NULL -- application id

,NULL -- group type

,NULL -- group name

,'SUPP_LEVEL' -- data level

,'''N''' -- DATA_LEVEL_1

,NULL -- DATA_LEVEL_2

,NULL -- DATA_LEVEL_3

,NULL -- DATA_LEVEL_4

,NULL -- DATA_LEVEL_5

,NULL -- ATTR_NAME_LIST

);

-- Get the user attribute data

POS_VENDOR_PUB_PKG.Get_User_Attrs_Data (

p_api_version => 1.0

,p_pk_column_name_value_pairs => l_pk_column_values

,p_attr_group_request_table => l_request_table

,x_attributes_row_table => l_attributes_row_table

,x_attributes_data_table => l_attributes_data_table

,x_return_status => l_return_status

,x_errorcode => l_errorcode

,x_msg_count => l_msg_count

Sample Scripts for Integrating Third Party Applications

C-11

,x_msg_data => l_msg_data);

dbms_output.put_line('return_status: ' || l_return_status);

dbms_output.put_line('msg_count: ' || l_msg_count);

dbms_output.put_line('msg_data: ' || l_msg_data);

IF l_attributes_row_table IS NOT NULL THEN

dbms_output.put_line('row_table count: ' ||

l_attributes_row_table.COUNT);

END IF;

IF l_attributes_data_table IS NOT NULL THEN

dbms_output.put_line('data_table count: ' ||

l_attributes_data_table.COUNT);

FOR i IN 1 .. l_attributes_data_table.COUNT LOOP

IF l_attributes_data_table(i).row_identifier <> l_previous

THEN

-- Add a line to separate each UDA row.

dbms_output.put_line('-----------------------------');

l_previous := l_attributes_data_table(i).row_identifier;

END IF;

dbms_output.put_line(

'Row ' || l_attributes_data_table(i).row_identifier ||

' Attr: '||l_attributes_data_table(i).attr_name ||

' Value String:

'||l_attributes_data_table(i).attr_value_str ||

' Value Number:

'||l_attributes_data_table(i).attr_value_num);

dbms_output.new_line;

END LOOP;

END IF;

END;

Oracle EBS, Fusion and OIC

Friday 19 September 2014

API for Supplier/ Vendor Site creation in R12

API for supplier/ vendor creation in R12

Supplier Hub API Sample Scripts for Integrating Third Party Applications

How to resolve issue for BIP RTF template XML tags showing value as <?ref: 0000xx?>