Interface to Upload Approved supplier List in Oracle Apps R12
DECLARE
x_row_id VARCHAR2(20);
x_row_id1 VARCHAR2(20);
x_asl_id NUMBER;
x_row_id VARCHAR2(20);
x_row_id1 VARCHAR2(20);
x_asl_id NUMBER;
BEGIN
apps.po_asl_ths.insert_row
(x_row_id,
x_asl_id,
87, --using_organization_id
87, --owning_organization_id,
'DIRECT', --vendor_business_type,
2, --status_id,
SYSDATE, --last_updated_date
0, --last_updated_by,
SYSDATE, --creation_date,
0, --created_by,
NULL,
4408, --vendor_id,
484915, --inventory_item_id,
NULL,
7888, --vendor_site_id,
null, --primary_vendor_item,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL);
apps.po_asl_attributes_ths.insert_row
(x_row_id1,
x_asl_id, --asl_id
87, --using_organization_id,
SYSDATE, --last_updated_date
0, --last_updated_by,
SYSDATE, --creation_date
0, --created_by,
'ASL', --document_sourcing_method
'CREATE_AND_APPROVE', --release_generation_method
NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,
4408, --vendor_id,
7888, --vendor_site_id,
484915, --inventory_item_id,
NULL,
null, --attribute_category
null, --state(attribute1),
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,
null, --country_of_origin_code,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL);
COMMIT;
END;
SQL Query to fetch Approved Supplier List in Oracle
SELECT hou.NAME operating_unit, asl.vendor_business_type,
pov.segment1 vendor_code, pov.vendor_name, sites.vendor_site_code,
msi.segment1 item_code, msi.description item_desc,
using_organization_id, owning_organization_id,plc.displayed_field , past.status Supplier_Status
FROM po_approved_supplier_list asl,
po_vendors pov,
po_vendor_sites_all sites,
mtl_system_items_b msi,
hr_operating_units hou,
po_lookup_codes plc,
po_asl_statuses past
WHERE asl.vendor_id = pov.vendor_id
AND asl.vendor_site_id = sites.vendor_site_id
AND msi.inventory_item_id = asl.item_id
AND msi.organization_id = asl.using_organization_id
AND sites.org_id = hou.organization_id
and asl.vendor_business_type = plc.lookup_code
AND asl.asl_status_id = past.status_id
AND plc.lookup_type = 'ASL_VENDOR_BUSINESS_TYPE'
and past.status='Approved';
4 comments:
for mass upload you can pass a cursor and run the procedure anonymously as below shown:
--create or replace PROCEDURE xx_NBK__LOAD_ASLIST AS
DECLARE
x_row_id VARCHAR2(20);
x_row_id1 VARCHAR2(20);
x_asl_id NUMBER;
CURSOR c1 IS select INVENTORY_ITEM_ID from mtl_system_items_b
where ORGANIZATION_ID='101' and SEGMENT1 in ('SP0078')
-- AND INVENTORY_ITEM_ID NOT IN (SELECT ITEM_ID FROM po_approved_supplier_list)
;
-- CURSOR c2 IS
-- select ITEM_ID from po_approved_supplier_list
--where USING_ORGANIZATION_ID='101' AND ITEM_ID --='298' --
--NOT IN (SELECT ITEM_ID FROM PO_ASL_SUPPLIERS_V)
--;
l_INVENTORY_ITEM_ID NUMBER;
--l_ITEM_ID NUMBER;
l_error VARCHAR2(300);
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO
l_INVENTORY_ITEM_ID --,
--l_transaction_step_id
;
EXIT WHEN c1%notfound;
IF
l_INVENTORY_ITEM_ID IS NOT NULL
THEN
apps.po_asl_ths.insert_row --insert ASL HEADER attributes
(x_row_id,
x_asl_id,
101, --using_organization_id
101, --owning_organization_id,
'DIRECT', --vendor_business_type,
2, --status_id,
SYSDATE, --last_updated_date
0, --last_updated_by,
SYSDATE, --creation_date,
0, --created_by,
NULL,
1173, --vendor_id,
l_inventory_item_id,--inventory_item_id
--'367', --inventory_item_id,
NULL,
1050, --vendor_site_id,
null, --primary_vendor_item,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL);
END IF;
IF
l_INVENTORY_ITEM_ID IS NOT NULL
THEN
apps.po_asl_attributes_ths.insert_row
(x_row_id1,
x_asl_id, --asl_id
101, --using_organization_id,
SYSDATE, --last_updated_date
0, --last_updated_by,
SYSDATE, --creation_date
0, --created_by,
'ASL', --document_sourcing_method
'Null', --'CREATE_AND_APPROVE' --release_generation_method
NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,
1173, --vendor_id,
1050, --vendor_site_id,
l_INVENTORY_ITEM_ID, --inventory_item_id,
NULL,
null, --attribute_category
null, --state(attribute1),
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,null, --country_of_origin_code,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL);
END IF;
END LOOP;
CLOSE c1;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
l_error := substr(sqlerrm,1,200);
dbms_output.put_line('Other Error in updating item in ASL- ' || l_error)
;
END; -- xx_NBK__LOAD_ASLIST;
Thanks for sharing this informative article ,It would be helpful to all
Oracle Fusion HCM Online Training
Very good thing to learn about, When I run this query in Apps schema it is working, when I run in user schema it is showing some error. Grants have been given. what could be the problem can anybody help me on this? thanks in advance
Post a Comment