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

 

Navigation for Approved Supplier List:

PO Super User -> Supply base -> Approved Supplier List

Select the organization, then define the supplier for category or Item for which
you want to define, and status to approved.


API's used to insert the Approved Supplier List's are as follows:

                                      po_asl_ths.insert_row
                              po_asl_attributes_ths.insert_row

base tables:
                                        po_asl_attributes,

                                  po_approved_supplier_list

used columns:

         ORGANIZATION_NAME          ,
         VENDOR_BUSINESS_TYPE       ,
         STATUS                     ,
         ITEM                       ,
         VENDOR_NAME                ,
         VENDOR_SITE_CODE           ,
         COMMENTS                   ,
         CATEGORY_NAME              ,
         PRIMARY_VENDOR_ITEM        ,
         MANUFACTURERS_NAME         ,
         attribute1 to attribute 12 if required,
         COUNTRY_OF_ORIGIN_CODE     ,
         RELEASE_GENERATION_METHOD  ,
         PURCHASING_UNIT_OF_MEASURE ,
         SUPPLIER_CALENDAR

mandatory columns:      

ORGANIZATION_NAME
VENDOR_BUSINESS_TYPE
STATUS                  
ITEM                    
VENDOR_NAME              
VENDOR_SITE_CODE        
SUPPLIER_CALENDAR


validations:
.organization validation from org_organization_definitions
.item validation based on org from mtl_system_items_b
.supplir/vendor validation from ap_suppliers
.vendor site code validation based on vendor number from ap_supplier_sites_all
.calendar validation from BOM_CALENDARS

we can use api/direct base table..
using api:

BEGIN

     po_asl_ths.insert_row
                  (x_row_id                           => l_row_id,
                   x_asl_id                           => l_asl_id,
                   x_item_id                          => REC_po_asl_valid.inventory_item_id,
                   x_vendor_id                        => REC_po_asl_valid.vendor_id,
                   x_vendor_site_id                   => REC_po_asl_valid.vendor_site_id,
                   x_vendor_business_type             => REC_po_asl_valid.vendor_business_type,
                   x_asl_status_id                    => 1,
                   x_created_by                       => l_user_id,
                   x_creation_date                    => SYSDATE,
                   x_last_updated_by                  => l_user_id,
                   x_last_update_login                => NULL,
                   x_last_update_date                 => SYSDATE,
                   x_using_organization_id            => -1 ,
                   x_owning_organization_id           => REC_po_asl_valid.organization_id,
                   x_manufacturer_id                  => NULL,
                   x_category_id                      => NULL,
                   x_primary_vendor_item              => REC_po_asl_valid.primary_vendor_item,
                   x_manufacturer_asl_id              => NULL,
                   x_comments                         => NULL,
                   x_review_by_date                   => NULL,
                   x_attribute_category               => NULL,
                   x_attribute1                       => REC_po_asl_valid.attribute1,
                   x_attribute2                       => REC_po_asl_valid.attribute2,
                   x_attribute3                       => NULL,
                   x_attribute4                       => NULL,
                   x_attribute5                       => REC_po_asl_valid.attribute5,
                   x_attribute6                       => REC_po_asl_valid.attribute6,
                   x_attribute7                       => REC_po_asl_valid.attribute7,
                   x_attribute8                       => REC_po_asl_valid.attribute8,
                   x_attribute9                       => REC_po_asl_valid.attribute9,
                   x_attribute10                      => REC_po_asl_valid.attribute10,
                   x_attribute11                      => REC_po_asl_valid.attribute11,
                   x_attribute12                      => REC_po_asl_valid.attribute12,
                   x_attribute13                      => NULL,
                   x_attribute14                      => NULL,
                   x_attribute15                      => NULL,
                   x_disable_flag                     => NULL
                  );
 --write exception
end;    

using base table directly:

here we have 2tables to insert i.e

begin
 BEGIN
   SELECT PO_APPROVED_SUPPLIER_LIST_S.nextval
   INTO v_appr_supp_list
   FROM DUAL;
 EXCEPTION
   WHEN OTHERS THEN
     FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in generating the sequence number');
 END;

insert into po_asl_attributes
      (
        asl_id,
        using_organization_id,
        document_sourcing_method,
        release_generation_method,
        enable_plan_schedule_flag,
        enable_ship_schedule_flag,
        enable_autoschedule_flag,
        enable_authorizations_flag,
        enable_vmi_flag,
        vendor_id,
        vendor_site_id,
        item_id,
        country_of_origin_code,
        delivery_calendar,
        last_update_date,
        last_updated_by,
        creation_date,
        created_by,
        last_update_login
      )
      values
      (v_appr_supp_list,
        -1,
        'ASL',
        'CREATE_AND_APPROVE',
        'N',
        'N',
        'N',
        'N',
        'N',
        v_vendor_id,--vendor id
        v_vendor_site_id,--vendor site id
        v_inv_item,--inventory item
        REC_po_asl_valid.country_of_origin_code,
        REC_po_asl_valid.supplier_calendar ,
        sysdate,
        FND_GLOBAL.USER_ID,
        sysdate,
        FND_GLOBAL.USER_ID,
        FND_GLOBAL.LOGIN_ID
      );

insert into po_approved_supplier_list
      (
        asl_id,
        using_organization_id,
        owning_organization_id,
        vendor_business_type,
        asl_status_id,
        vendor_id,
        vendor_site_id,
        primary_vendor_item,
        item_id,
        attribute1,
        attribute2,
        attribute5,
        attribute6 ,
        attribute7 ,
        attribute8,
        attribute9 ,
        attribute10,
        attribute11,
        attribute12,
       last_update_date,
       last_updated_by,
      creation_date,
      created_by,
      last_update_login
      )
      values
      (
        PO_APPROVED_SUPPLIER_LIST_S.currval,
        -1,--DECODE(trim(cal.inventory_org), 'AMS', 85, 'LVD', 86, 'SSO', 88),   --- Prayas on 04-Dec-2005 -- -1,
        v_using_org_id,--org id
        'DIRECT',
        2,  --2-Approved
        v_vendor_id,--vendor id
        v_vendor_site_id,--vendor site id
        REC_po_asl_valid.primary_vendor_item,--REC_po_asl_valid.item, -- changed by sanjay
        v_inv_item,--inventory item
        REC_po_asl_valid.attribute1,
        REC_po_asl_valid.attribute2,
        REC_po_asl_valid.attribute5,
        REC_po_asl_valid.attribute6 ,
        REC_po_asl_valid.attribute7 ,
        REC_po_asl_valid.attribute8,
        REC_po_asl_valid.attribute9 ,
        REC_po_asl_valid.attribute10,
        REC_po_asl_valid.attribute11,
        REC_po_asl_valid.attribute12,
        sysdate,
        FND_GLOBAL.USER_ID,
        sysdate,
        FND_GLOBAL.USER_ID,
        FND_GLOBAL.LOGIN_ID
      );

--exception
end;