Approved supplier list query in oracle apps r12

Approved supplier list query in oracle apps r12

In this post , We will be discuss about approved supplier list query in oracle apps r12. This query will help to extract the list of suppliers who are approved for creating purchase orders. Approved supplier list helps to create the automatic PO creating in oracle apps r12. Oracle system automatically select the supplier list from approved supplier list based on the purchased item for creating purchase orders. Here below is the detail 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';

Approved supplier list query in oracle apps r12