Interface to Upload Approved supplier List(ASL) in Oracle Apps R12 & SQL Query

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;

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