po approved supplier list query (ASL)

po approved supplier list query (ASL)

 Base Table: 

select * from po_asl_attributes

select * from po_approved_supplier_list

 Query : 

/* Formatted on 10/4/2013 1:18:12 PM (QP5 v5.114.809.3010) */
SELECT   msib.inventory_item_id,
         msib.segment1 "ITEM_NUMBER",
         msib.description "ITEM_DESCRIPTION",
         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 "VENDOR_SITE_CODE",
         pvsa.INACTIVE_DATE "VENDOR_SITE_INACTIVE_DATE",
         asl.PRIMARY_VENDOR_ITEM,
         asl.DISABLE_FLAG,
         pas.status "SOURCE_ASL_STATUS",
         ood.organization_code "ORGANIZATION CODE",
         asl.CREATION_DATE,
         asl.LAST_UPDATE_DATE,
         asl.ATTRIBUTE7 "VENDOR_PRICE",
         asl.ATTRIBUTE6 "PREFERENCE"
  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

       ==================  *********************  ====================

Approved Supplier List (ASL) in Oracle Purchasing

This Post is about Oracle Apps Purchasing and ASL ( Approved Supplier List) and I will explain how to define the APPROVE SUPPLIER list in Oracle Purchasing

In the last REQ TO PO RECEIPT cycle post we have noticed that I have define an Item(Test001) with “Use Approval Supplier Checked “ and when I try to approve the PO Release for this item system prompt with error message

“Item is restricted and Supplier is not Approve Supplier”. To Progress the Releases /PO with the item that are mark as “Use Approve Supplier” , we need to define the Item in the Approve Supplier List.
Note - In Approve Supplier List (ASL)we define all the Supplier that are Approved to Supply a particular Item.

ASL is very helpful and purpose of ASL is to automate the Identification of the Supplier to supply specific goods Or we cab say with ASL we are trying to automate the Source of Supply.

Oracle has made it very easy to define ASL in Oracle Apps Purchasing Module.

Supplier Base > Supplier Statuses

In ASL we store info. Like

• Ship- To
• Ship – From
• Also we store the status of the Supplier.

 Status of Supplier in ASL are

•  Approve
•  New
•  Debarred

Along with the Statues , for each Supplier/Item combination in ASL we define the Rules to indicate what action should allowed and Prevent

4 Action that we can assign to ASL are

• PO Allowed
• Sourcing
• Schedule Confirmation
• Manufacture Link

Attribute that we define in ASL are

• Item
• Business type
• Status
• Review By
• Global etc

Create Approved Supplier List for Item Test001 , and associate the Suppliet “Office , Supplies , Inc”

Create Quote and Create Blanket Agreement 

 Create Release for the Blanket Agreement. Please Note that in Release we have Item Test001 and since it is marked as “Use Approve Supplier” , with Supplier now in ASL we can now approve  PO release for this item.

PO Agreement

PO Release