This query can be used to get the Approved Supplier List information for the items.
Query is tested in R12 instance.
pv.vendor_name "SUPPLIER NAME",
pv.segment1 "VENDOR NUMBER",
pvsa.vendor_site_code "SUPPLIER SITE CODE",
pas.status "SOURCE_ASL_STATUS",
paa.consigned_from_supplier_flag "CONSIGNED FROM SUPPLIER?"
FROM APPS.po_approved_supplier_list asl,
APPS.po_vendors pv,
APPS.po_vendor_sites_all pvsa,
APPS.org_organization_definitions ood,
APPS.mtl_system_items_b msib,
APPS.po_asl_attributes paa,
APPS.po_asl_statuses pas
WHERE pv.vendor_id = asl.vendor_id
AND ood.operating_unit = pvsa.org_id
AND asl.item_id = msib.inventory_item_id
AND asl.using_organization_id = msib.organization_id
AND ood.organization_id = msib.organization_id
AND asl.asl_id = paa.asl_id
AND asl.using_organization_id = paa.using_organization_id
AND msib.organization_id = paa.using_organization_id
AND ood.organization_id = paa.using_organization_id
AND asl.asl_status_id = pas.status_id
AND msib.segment1 = :ITEM NUMBER
Query is tested in R12 instance.
SELECT ood.organization_code "ORGANIZATION CODE",
msib.segment1 "ITEM_NUMBER",pv.vendor_name "SUPPLIER NAME",
pv.segment1 "VENDOR NUMBER",
pvsa.vendor_site_code "SUPPLIER SITE CODE",
pas.status "SOURCE_ASL_STATUS",
paa.consigned_from_supplier_flag "CONSIGNED FROM SUPPLIER?"
FROM APPS.po_approved_supplier_list asl,
APPS.po_vendors pv,
APPS.po_vendor_sites_all pvsa,
APPS.org_organization_definitions ood,
APPS.mtl_system_items_b msib,
APPS.po_asl_attributes paa,
APPS.po_asl_statuses pas
WHERE pv.vendor_id = asl.vendor_id
AND pvsa.vendor_site_id = asl.vendor_site_id
AND ood.organization_id = asl.using_organization_idAND ood.operating_unit = pvsa.org_id
AND asl.item_id = msib.inventory_item_id
AND asl.using_organization_id = msib.organization_id
AND ood.organization_id = msib.organization_id
AND asl.asl_id = paa.asl_id
AND asl.using_organization_id = paa.using_organization_id
AND msib.organization_id = paa.using_organization_id
AND ood.organization_id = paa.using_organization_id
AND asl.asl_status_id = pas.status_id
AND msib.segment1 = :ITEM NUMBER