Approved Supplier List: as the name suggest this is the list of approved suppliers for a particular item so that one of these suppliers can be used to create the purchase orders. Most probably you will need this script if you need to assign Approved Suppliers to 1 or multiple items or you already have ASL for an inventory org and business wants it to be copied over to another Inventory Org.
Below is the list of important tables behind the picture:
- po_approved_supplier_list –> This record type is mandatory and must be populated.
- po_asl_attributes –> This record type is optional
- po_asl_documents –> This record type is optional
- po_supplier_item_capacity –> This record type is optional
- po_supplier_item_tolerance –> This record type is optional
API Name: po_asl_api_pub.create_update_asl
Environment Details in which script is tested:
E Business Suit Version: 12.2.9
Database Version: 19.0.0.0.0
So as we discussed above, most probably scenario will be either of the 2: in first you just need to create the ASL fresh for item(s) or you need to copy it over for a new Inventory Org. For first scenario, you can use the script given below. In case requirement is to copy the ASL from one inventory org to another, click here. If you look at the script, we have a cursor named cur_get_vendor_data. You will have to alter this cursor to fetch the supplier and the site which you need to assign to the item. Furthermore, if the cursor fetches multiple Supplier- site combination, script will assign the ASL for all these records. One important thing to remember is that if the item is not marked as “Used Approved Supplier”, this script will also update the flag. Also please note that the API uses 5 record types. But for ASL creation only 1 Appr_Supp_Rec record type is must. so if you don’t want to use other 4 record types, just declare them but don’t instantiate it. If you do instantiate, give null value to each column.
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
Below is the script for “Approved Supplier List” creation.
DECLARE
l_v_dest_org_code VARCHAR2(100) := ‘179’;
l_n_dest_org_id NUMBER := NULL;
l_n_user_id NUMBER ;
l_n_appl_id NUMBER ;
l_n_resp_id NUMBER ;
l_msg VARCHAR2(2000) := NULL;
x_message_list Error_Handler.Error_Tbl_Type;
x_return_status VARCHAR2(100);
x_msg_count NUMBER := 0;
l_rowcnt NUMBER := 0;
l_n_asl_count NUMBER := 0;
x_session_key NUMBER;
x_return_msg VARCHAR2 (2000);
x_errors po_asl_api_error_rec;
l_n_purchasing_org_id NUMBER := NULL;
l_n_bec_org_Id NUMBER := NULL;
l_item_cnt NUMBER := 0;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := FND_API.G_TRUE;
l_commit VARCHAR2(2) := FND_API.G_TRUE;
--Table type to update the must_use_approved_vendor_flag
l_item_tbl_typ ego_item_pub.item_tbl_type;
x_item_table ego_item_pub.item_tbl_type;
x_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
x_organization_id mtl_system_items_b.organization_id%TYPE;
L_V_FINAL_ERROR_MSG VARCHAR2(4000) := NULL;
L_V_ERROR_MESSAGE VARCHAR2(4000) := NULL;
--Record types for ASL
Appr_Supp_Rec po_approved_supplier_list_rec := new po_approved_supplier_list_rec() ;
Appr_Supp_attr_Rec po_asl_attributes_rec ;
asl_doc_Rec po_asl_documents_rec ;
chv_auth_rec chv_authorizations_rec ;
item_capacity_rec po_supplier_item_capacity_rec ;
item_tolerance_rec po_supplier_item_tolerance_rec;
--Cursor to fetch the vendor data
CURSOR cur_get_vendor_data
IS
SELECT pv.VENDOR_NAME,
pv.VENDOR_id,
pvsa.vendor_site_code,
pvsa.vendor_site_id
FROM po_vendors pv,
po_vendor_sites_all pvsa
WHERE pv.vendor_id = pvsa.vendor_id
AND pv.vendor_name = '<YOur Vendor Name>'
AND pvsa.vendor_site_code = 'Your Vendor Site Code'
AND org_id = <Org Id to choose the Vendor Site Id Uniquely>
l_v_vendor_rec cur_get_vendor_data%ROWTYPE;
TYPE l_v_vendor_table IS TABLE OF l_v_vendor_rec%TYPE;
l_v_vendor_tab l_v_vendor_table := l_v_vendor_table();
CURSOR cur_get_item_data_asl ( p_in_n_org_id IN NUMBER)
IS
SELECT m.inventory_item_id,
m.must_use_approved_vendor_flag
FROM mtl_system_items_b m
WHERE m.organization_id = p_in_n_org_id
AND m.segment1 IN ('Item1', 'Item 2');
l_v_item_asl_rec cur_get_item_data_asl%ROWTYPE;
TYPE l_v_item_asl_table IS TABLE OF l_v_item_asl_rec%TYPE;
l_v_item_asl_tab l_v_item_asl_table := l_v_item_asl_table();BEGIN
dbms_output.put_line(‘Start’);
–Get the destination Org Id
SELECT organization_id
INTO l_n_dest_org_id
FROM mtl_parameters
WHERE organization_code = l_v_dest_org_code;
dbms_output.put_line(‘l_n_dest_org_id: ‘||l_n_dest_org_id);
–get the user name
SELECT USER_ID
INTO l_n_user_id
FROM FND_USER
WHERE user_name = ”;
dbms_output.put_line(‘l_n_user_id: ‘||l_n_user_id);
--get the essential info
SELECT application_id,
responsibility_id
INTO l_n_appl_id,
l_n_resp_id
FROM fnd_responsibility_tl
WHERE language = 'US'
AND responsibility_name = 'Inventory';
dbms_output.put_line('l_n_appl_id: '||l_n_appl_id);
dbms_output.put_line('l_n_resp_id: '||l_n_resp_id);
--Setting FND global variables.
fnd_global.APPS_INITIALIZE(user_id=>l_n_user_id,
resp_id=>l_n_resp_id,
resp_appl_id=>l_n_appl_id);
--Time to update the must_use_approved_vendor_flag if required
OPEN cur_get_item_data_asl( L_n_dest_org_id);
FETCH cur_get_item_data_asl BULK COLLECT INTO l_v_item_asl_tab;
CLOSE cur_get_item_data_asl;
-- Now that we have assigned the items, time to update the flag
l_item_cnt:= 0;
For i in l_v_item_asl_tab.FIRST..l_v_item_asl_tab.LAST
LOOP
IF NVL(l_v_item_asl_tab(i).must_use_approved_vendor_flag,'N') = 'N' -- it means that Item is yet to be marked for ASL
THEN
--Prepare for the item assignment
l_item_cnt:= l_item_cnt + 1;
l_item_tbl_typ (l_item_cnt).transaction_type := 'UPDATE';
l_item_tbl_typ (l_item_cnt).inventory_item_id := l_v_item_asl_tab(i).inventory_item_id;
l_item_tbl_typ (l_item_cnt).organization_id := l_n_dest_org_id;
l_item_tbl_typ (l_item_cnt).must_use_approved_vendor_flag := 'Y';
END IF ;
END LOOP ;
IF l_item_cnt>0
THEN
DBMS_OUTPUT.put_line ('Reords to be updated: '||l_item_cnt);
DBMS_OUTPUT.put_line ('=====================================');
DBMS_OUTPUT.put_line ('Calling EGO_ITEM_PUB.Process_Items API');
ego_item_pub.process_items (p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_true
, p_item_tbl => l_item_tbl_typ
, x_item_tbl => x_item_table
, x_return_status => x_return_status
, x_msg_count => x_msg_count
);
DBMS_OUTPUT.put_line ('==================================');
DBMS_OUTPUT.put_line ('Return Status ==>' || x_return_status);
IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line ('Out status : S');
ELSE
DBMS_OUTPUT.put_line ('Error Messages :');
error_handler.get_message_list (x_message_list => x_message_list);
FOR i IN 1 .. x_message_list.COUNT
LOOP
DBMS_OUTPUT.put_line (x_message_list (i).MESSAGE_TEXT);
END LOOP;
END IF;
DBMS_OUTPUT.put_line ('==================================');
END IF ;
--So by now we must have all the items in a proper state.. now we can create ASL
--First thing firs.. lets get all the suppliers for which ASL needs to be assigned
OPEN cur_get_vendor_data;
FETCH cur_get_vendor_data BULK COLLECT INTO l_v_vendor_tab ;
CLOSE cur_get_vendor_data;
IF l_v_vendor_tab.count>0
THEN
FOR j in l_v_vendor_tab.first..l_v_vendor_tab.last
LOOP
IF l_v_item_tab.count>0
THEN
dbms_output.put_line('Total Records Fetched: '||l_v_item_tab.count);
l_item_cnt:= 0;
For i in l_v_item_tab.FIRST..l_v_item_tab.LAST
LOOP
--Check if the ASL is not already existing
SELECT count(1)
INTO l_n_asl_count
FROM po_approved_supplier_list
WHERE item_id = l_v_item_tab(i).inventory_item_id
AND vendor_id = l_v_vendor_tab(j).vendor_id
AND vendor_site_id = l_v_vendor_tab(j).vendor_site_id
AND owning_organization_id = l_n_dest_org_id;
IF l_n_asl_count=0
THEN
/*############# code starts for Approved Supplier List creation ##########*/
--dbms_output.put_line('Item Id: '||l_v_item_tab(i).inventory_item_id||', Vendor Id: '||l_v_vendor_tab(j).vendor_id||', Vendor Site Id: '||l_v_vendor_tab(j).vendor_site_id);
Appr_Supp_Rec.item_id := po_tbl_number (l_v_item_tab(i).inventory_item_id);
Appr_Supp_Rec.vendor_id := po_tbl_number (l_v_vendor_tab(j).vendor_id);
Appr_Supp_Rec.vendor_site_id := po_tbl_number (l_v_vendor_tab(j).vendor_site_id);
Appr_Supp_Rec.owning_organization_id := po_tbl_number (l_n_dest_org_id);
Appr_Supp_Rec.vendor_business_type := po_tbl_varchar25 ('DIRECT');
Appr_Supp_Rec.user_key := po_tbl_number (1);
Appr_Supp_Rec.process_action := po_tbl_varchar30 ('CREATE');
Appr_Supp_Rec.global_flag := po_tbl_varchar1 ('N');
Appr_Supp_Rec.asl_status_id := po_tbl_number (2);
Appr_Supp_Rec.asl_status_dsp := po_tbl_varchar25 (NULL);
Appr_Supp_Rec.manufacturer_id := po_tbl_number (NULL);
Appr_Supp_Rec.manufacturer_dsp := po_tbl_varchar100 (NULL);
Appr_Supp_Rec.owning_organization_dsp := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.vendor_dsp := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.item_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_Rec.category_id := po_tbl_number (NULL);
Appr_Supp_Rec.category_dsp := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.vendor_site_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_Rec.primary_vendor_item := po_tbl_varchar25 (NULL);
Appr_Supp_Rec.manufacturer_asl_id := po_tbl_number (NULL);
Appr_Supp_Rec.manufacturer_asl_dsp := po_tbl_varchar50 (NULL);
Appr_Supp_Rec.review_by_date := po_tbl_date (NULL);
Appr_Supp_Rec.comments := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.attribute_category := po_tbl_varchar30 (NULL);
Appr_Supp_Rec.attribute1 := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.attribute2 := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.attribute3 := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.attribute4 := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.attribute5 := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.attribute6 := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.attribute7 := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.attribute8 := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.attribute9 := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.attribute10 := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.attribute11 := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.attribute12 := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.attribute13 := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.attribute14 := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.attribute15 := po_tbl_varchar240 (NULL);
Appr_Supp_Rec.request_id := po_tbl_number (NULL);
Appr_Supp_Rec.program_application_id := po_tbl_number (NULL);
Appr_Supp_Rec.program_id := po_tbl_number (NULL);
Appr_Supp_Rec.program_update_date := po_tbl_date (NULL);
Appr_Supp_Rec.disable_flag := po_tbl_varchar1 (NULL);
--Time to create the ASL
po_asl_api_pub.create_update_asl (p_asl_rec => Appr_Supp_Rec,
p_asl_attr_rec => Appr_Supp_attr_Rec,
p_asl_doc_rec => asl_doc_Rec,
p_chv_auth_rec => chv_auth_rec,
p_capacity_rec => item_capacity_rec,
p_tolerance_rec => item_tolerance_rec,
p_commit => 'Y',
x_session_key => x_session_key,
x_return_status => x_return_status,
x_return_msg => x_return_msg,
x_errors => x_errors
);
dbms_output.put_line ('Return Status>>>>' || x_return_status||' For Inventory item id: '||l_v_item_tab(i).inventory_item_id);
IF (x_return_status<> 'S')
THEN
dbms_output.put_line ('Session Key>>>> ' || x_session_key);
dbms_output.put_line ('Return Msg>>>> ' || x_return_msg);
l_v_final_error_msg := l_v_final_error_msg|| ' Few records failed while creating the ASL';
FOR i IN 1 .. x_errors.user_key.COUNT
LOOP
l_v_error_message := SUBSTR ( x_errors.rejection_reason(i),1,2000);
dbms_output.put_line ( l_v_error_message );
END LOOP;
END IF ;
END IF ;
END LOOP ;
END IF ;
END LOOP;
END IF ;EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line(‘Unexpected error while processing the items: ‘||SQLERRM);
END ;
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
Check out the Amazon Deals of the day based on your interests..
