Approved supplier list query in oracle apps r12

 

Most Important Table Used by approved supplier list query in oracle apps r12

 1.po_approved_supplier_list asl,
 2.po_vendors pv,
 3.po_vendor_sites_all pvsa,
 4.org_organization_definitions ood,
 5.mtl_system_items_b msib,
 6.po_asl_attributes paa,
 7.po_asl_statuses pas

Approved supplier list query in oracle apps r12

Here below is the Detail Approved supplier list query in oracle apps r12

Query 1: -

SELECT  
         msib.segment1 "ITEM_NUMBER",
         msib.description "ITEM_DESCRIPTION",

         ood.organization_code "ORGANIZATION CODE",
         msib.primary_uom_code "PRIMARY_UNIT_OF_MEASURE",
         DECODE (MSIB.enabled_flag, 'Y', 'Active', 'Inactive') "ITEM_STATUS",
         msib.CREATION_DATE "ITEM_CREATION_DATE",
         pv.segment1 "VENDOR_NUMBER",
         pv.vendor_name "VENDOR_NAME",
         pvsa.vendor_site_code,

         pvsa.INACTIVE_DATE " INACTIVE_DATE",
         asl.PRIMARY_VENDOR_ITEM,
         asl.DISABLE_FLAG,
         pas.status " ASL_STATUS"
  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_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 ood.operating_unit = pvsa.org_Id

         AND msib.segment1 = :P_ITEM_CODE
         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.asl_status_id = pas.status_id

Query 2:- 

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

This query can be used to get the Approved Supplier List information for the items.

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_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