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