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
Use the create_vendor.sql sample script to create suppliers.
create_vendor.sql
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;
/
Use the create_vendor_site.sql sample script to create supplier sites.
create_vendor_site.sql
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;
/
Use the create_vendor_contact.sql sample script to create a supplier contact.
create_vendor_contact.sql
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;
/
Use the update_vendor.sql sample script to update a supplier.
update_vendor.sql
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;
/
Use the update_vendor_site.sql sample script to update a supplier site.
update_vendor_site.sql
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;
/
Use the validate_vendor.sql sample script to validate a supplier.
validate_vendor.sql
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;
/
Use the create_uda.sql sample script to create a user defined attribute.
create_uda.sql
DECLARE
l_party_id NUMBER;
l_attr_group_id NUMBER;
l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
l_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
l_class_code EGO_COL_NAME_VALUE_PAIR_ARRAY;
l_failed_row_id_list VARCHAR2(100);
x_return_status VARCHAR2(100);
x_errorcode NUMBER;
x_msg_count NUMBER;
x_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)
);
l_class_code :=
EGO_COL_NAME_VALUE_PAIR_ARRAY(
EGO_COL_NAME_VALUE_PAIR_OBJ('CLASSIFICATION_CODE', 'BS:BASE')
);
-- Attribute group object
l_attributes_data_table := ego_user_attr_data_table(
ego_user_attr_data_obj(
row_identifier => 1,
attr_name => 'TITLE',
attr_value_str => 'Demo 4',
attr_value_num => NULL,
attr_value_date => NULL,
attr_disp_value => NULL,
attr_unit_of_measure => NULL,
user_row_identifier => 1),
ego_user_attr_data_obj(
row_identifier => 1,
attr_name => 'ID',
attr_value_str => NULL,
attr_value_num => 13579,
attr_value_date => NULL,
attr_disp_value => NULL,
attr_unit_of_measure => NULL,
user_row_identifier => 1)
);
l_attributes_row_table := ego_user_attr_row_table(
ego_user_attr_row_obj(
row_identifier => 1,
attr_group_id => NULL,
attr_group_app_id => 177,
attr_group_type => 'POS_SUPP_PROFMGMT_GROUP',
attr_group_name => 'BUSINESS',
data_level => 'SUPP_LEVEL',
data_level_1 => 'N',
data_level_2 => NULL,
data_level_3 => NULL,
data_level_4 => NULL,
data_level_5 => NULL,
transaction_type => 'CREATE')
);
-- Get the user attribute data
POS_VENDOR_PUB_PKG.Process_User_Attrs_Data (
p_api_version => 1.0
,p_attributes_row_table => l_attributes_row_table
,p_attributes_data_table => l_attributes_data_table
,p_pk_column_name_value_pairs => l_pk_column_values
,p_class_code_name_value_pairs => l_class_code
,x_failed_row_id_list => l_failed_row_id_list
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
dbms_output.put_line('return status: ' || x_return_status);
dbms_output.put_line('fail list: ' || l_failed_row_id_list);
dbms_output.put_line('msg_count: ' || x_msg_count);
dbms_output.put_line('msg_data: ' || x_msg_data);
COMMIT;
end;
/
Use the query_uda.sql sample script to query a user defined attribute.
query_uda.sql
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
,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;
/
Use the pos_pub_history_bo_pkg.get_published_suppliers API to get the supplier business objects for a given supplier publication event.
pos_pub_history_bo_pkg.get_published_suppliers
/*#
* @param p_api_version Standard API version. Use 1.0.
* @param p_init_msg_list Standard API initialize message list flag. Default is fnd_api.g_true
* @param p_event_id Supplier publication event ID
* @param p_party_id Party ID of a particular supplier to be retrieved if known.
* @param p_orig_system If party ID is not known, supply the original system name.
* @param p_orig_system_reference If party ID is not known, supply the original system reference. Party ID and original system references can be NULL in which case, all suppliers published in the given event will be returned.
* @param x_suppliers Return value. A table of objects, each containing a supplier business object in XML format.
* @param x_return_status Standard API return status
* @param x_msg_count Standard API message count
* @param x_msg_data Standard API message data
*/
pos_pub_history_bo_pkg.get_published_suppliers
(
p_api_version IN NUMBER DEFAULT NULL,
p_init_msg_list IN VARCHAR2 DEFAULT NULL,
p_event_id IN NUMBER,
p_party_id IN NUMBER,
p_orig_system IN VARCHAR2,
p_orig_system_reference IN VARCHAR2,
x_suppliers OUT NOCOPY pos_pub_history_bo_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
Spoke systems use the pos_pub_history_bo_pkg.create_publication_response API to respond to a supplier publication event. The API writes a message to the Supplier Hub's event response table.
pos_pub_history_bo_pkg.create_publication_response
/*#
* @param p_api_version Standard API version. Use 1.0.
* @param p_init_msg_list Standard API initialize message list flag. Default is fnd_api.g_true
* @param p_commit Commit flag. Perform commit if value is fnd_api.g_true. Default is fnd_api.g_false.
* @param p_target_system The spoke system's identity. Required.
* @param p_response_process_id Response process ID. Response ID required to uniquely identify a response for a target system. Required.
* @param p_response_process_status Response process status code. Stored in response table with open interpretation.
* @param p_request_process_id Request process ID. Stored in response table with open interpretation.
* @param p_request_process_status Request process status code. Stored in response table with open interpretation.
* @param p_event_id Supplier publication event ID this response refers to. Required.
* @param p_party_id Party ID of a particular supplier of the event this response refers to. Required.
* @param p_message Response message. Stored in response table with open interpretation.
* @param x_return_status Standard API return status
* @param x_msg_count Standard API message count
* @param x_msg_data Standard API message data
*/
pos_pub_history_bo_pkg.create_publication_response
(
p_api_version IN NUMBER DEFAULT NULL,
p_init_msg_list IN VARCHAR2 DEFAULT NULL,
p_commit IN VARCHAR2 DEFAULT NULL,
p_target_system IN VARCHAR2,
p_response_process_id IN NUMBER,
p_response_process_status IN VARCHAR2,
p_request_process_id IN NUMBER,
p_request_process_status IN VARCHAR2,
p_event_id IN NUMBER,
p_party_id IN NUMBER,
p_message IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);