Friday 4 October 2013

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;

6 comments:

  1. Hi, the content is really good. I have a query here if we can use ASL for PO Requisition creation. What setup will be required.
    I created on Commodity ASL with one Category and assigned a supplier and site to it. When I go to Requisition page and select the category for which I created ASL, it should show only supplier I have attached, but it is showing all the Suppliers. Please help me to get this done.

    ReplyDelete
  2. Hi Elangovan

    do you have plsql wrapper pgm to load ASL into API table?

    ReplyDelete
  3. thank you for sharing this script

    ReplyDelete
  4. direct base table update without any hassle

    ReplyDelete
  5. Quality. Regardless of whether you buy a segment, completed item, or administration, suppliers can emphatically or adversely influence the nature of your item. Better expands consumer loyalty and diminishes returns, which add money to your main concern. https://besteenergieleverancier.com/

    ReplyDelete
  6. Hi sir I am doing this ASL conversion in which table we can find the 'global flag' that we see in the front-end

    ReplyDelete