How To Diagnose Issues With Approved Supplier List And Sourcing Rules In Purchasing [ID 557825.1]
Modified: Jul 3, 2012 Type: TROUBLESHOOTING Status: PUBLISHED Priority: 3 In this Document Purpose Troubleshooting Steps INTRODUCTION TESTCASE INFORMATION DOCUMENTATION PROCESSING OVERVIEW SETUP DIAGNOSTICS IMPORTANT CHECKLIST
GENERAL ISSUES AND RELEVANT NOTES LOGGING
TRACING
DATA AND QUERIES
CODE FILES References Applies to:
Oracle Purchasing - Version 11.5.4 to 12.0.4 [Release 11.5 to 12] Information in this document applies to any platform.
***Checked for relevance on 12-Apr-2011*** Form:POXSCASL.FMB - Define Approved Supplier List Form:MRPFDDSR.FMB - Define Sourcing Rules Form:MRPFDSRA.FMB - Define Bills of Distribution
Purpose
This document briefly discusses the following: 1- Sourcing Rules setup.
2- Approved Supplier List (ASL) setup.
3- How to troubleshoot and debug the Sourcing Rules and ASL issues. 4- Debugging techniques and references to the important notes.
5- Reference to diagnostics and patches which will be helpful to solve the issues.
Troubleshooting Steps INTRODUCTION
Oracle Purchasing allows users to automatically source items from suppliers based on the sourcing rules and purchase agreements. The business needs of sourcing are;
· Automatically default supplier and pricing information onto requisition lines · Source the item from negotiated purchase agreements
TESTCASE INFORMATION
A reproducible testcase is an ideal for understanding the issue fully.Following testcases and questions will help in understanding the issue thoroughly or will provide hints so that those points can be addressed primarily while investigating the issue.
Does the issue occur every time or is this with only a specific few orders (intermittent)? Is the Problem reproducible at will or is it specific to only few suppliers' organizations ? Does the issue occur across all organizations?
Is the issue exists for new supplier and then trying to implement it first time ? Has this worked before if so what has changed?
Is the issue only with requisition created via Requisition Import or with requisition created manually ? Is the issue with Customization or issue exists with core code also ?
DOCUMENTATION
Oracle Purchasing User's Guide covers the functionality discussed here. Following are the pointers to the documentation available in the Metalink.
Oracle Purchasing User's Guide, Section-Overview of Automatic Sourcing, Page 5-30 to 5-59 Oracle Manufacturing APIs and Open Interfaces Manual, Volume 1 Release 11i
Note 285470.1 ASL Sourcing Whitepaper
Note 417966.1 Auto Sourcing And Auto Create/Release Checklist
Note 175935.1 ASL Sourcing to Requisitions Setup and Usage Guide
PROCESSING OVERVIEW
Sourcing rule describes which supplier and supplier site while approved supplier list describes pricing details from document and line details of that supplier and supplier site combination.
Sourcing rule is mandatory which identifies the supplier and supplier site for the sourcing to happen. Sourcing rule and approved supplier list can be local and global. Once the sourcing rule is identified by sourcing functionality next is to find the source document for that supplier and supplier site combination.
It first checks for the Profile option "PO: Automatic Document Sourcing".
Consider if the value is set to 'Yes'. This enables Purchasing to default the most recent source documents
automatically. If more than one source document is entered, Purchasing uses a blanket purchase agreement over contract and quotation, even if they were created more recently. If there are only blanket purchase agreements or only quotations, Purchasing uses the one that was created most recently.
If the Profile PO: Automatic Document Sourcing is set to 'No' then it checks for the ASL setup.
Global ASL entries are valid for all inventory organizations. Local ASL entries are specific to a single inventory organization.
If two ASL entries exist for the same item or commodity, one Global entry that applies to all organizations and one local entry that applies only to the local inventory organization, the local entry takes precedence. Also if ASL entries exist for the item and category (for which the same item is assigned), item ASL takes precedence over commodity ASL.
Below is the matrix, which shows the resulting document, created from the requisitions when it has the following source document.
Source Document Resulting Document Quotation Standard purchase order Contract purchase order Standard purchase order Local BPA Blanket release
Global BPA Standard purchase order
One can also accomplish an automatic creation of Sourcing Rules, Sourcing Rule Assignments and Approved Supplier List entries during Blanket Agreement approval Submission. The system automatically creates the relevant entries, once the Blanket Agreement is approved.
SETUP
1.Create a Sourcing Rule for the Supplier and Supplier Site combination. Mention the % allocation and Rank.
Note: Summation of % allocation against each rank should be 100%, and then only planning Check-box is enabled automatically.
2. Create an Assignment Set for this Organization or Item and list this Sourcing Rule in that Assignment Set (One Assignment Set can be used that will include all of the desired Assignments)
3. Set the Profile - MRP: Default Sourcing Assignment Set to the Assignment Set created in Step 2
4. Create an ASL for the item and add the Supplier and Supplier Site. The Note 285470.1 ASL Sourcing Whitepaper explains the complete setup required for sourcing rule and ASL creation.
Matrix showing the precedence detail when multiple ASL records exists for an Item.
Precedence ASL entries with followingcombination
First Supplier,Supplier site ,Local ASL
Second Supplier,Supplier site
,Global ASL
Third Supplier ,Local ASL
Fourth Supplier ,Global ASL
5.Enter the following documents under attribute window i.e Blanket, Quotation and Contract
6. Enter following release generation method. The Note 175935.1 ASL Sourcing to Requisitions Setup and Usage Guide briefly discusses about the ASL setup.
a) Automatic Release/Review: Automatically generates releases, but requires a separate step for approval. 'Create Release Program' is used to create releases in this case.
b) Automatic Release: Automatically generates approved releases. This choice is not available if encumbrance is turned on. 'Create Release Program' is used to create release in this case.
c) Release Using Auto Create: Release is created by the workflow. If requisition line(s) are for a one-time item then the release generation method is not considered and the requisition is a candidate for automatic creation by the CREATEPO workflow.
7. Now cross check the setup manually entering the item in the requisition form and tab out. If the Supplier, Supplier Site and Source details are populated then sourcing setup is working properly. Also refer the Note 187054.1 ASL Sourcing Rule Upgrade Troubleshooting which will help in understanding the setup issue.
The Note 275828.1 - First Supplier or Supplier Site Gets Picked up When Using The Same Rank Value On Sourcing Rules -explains the limitations in core purchasing.
This ranks and allocation percentages are used by Master Scheduling/MRP to allocate planned orders for their processing.
10/16/12 Document Display
8. Setup to automatically create ASL and Sourcing Rules during Blanket Purchase Order approval .More details are covered in Note 273983.1 Ext/Pub Steps Required For Creating ASL and Sourcing Rules From Blanket Po's .Following are the important details to check for.
Set the following Profile and PO Approval workflow attributes. Profile Options:
PO: Allow Auto-generate Sourcing Rules => CREATE AND UPDATE
PO: Allow Autocreation of Oracle Sourcing Documents => Yes
MRP: Default Assignment Set => Define with desired Assignment Set PO Approval Workflow attribute setup through Workflow Builder:
- Create Sourcing Rule Flag: "Y" - Update Sourcing Rule Flag: "Y"
- PO SR Rule Name Prefix: "PURCH" - Default Value - PO Assignment Type ID for SR: "3 - Item level "
Create a BPA and Submit for approval, in approval window in additional options details choose the appropriate value and submit for approval. Once BPA is approved, check whether ASL and Sourcing Rule created.
DIAGNOSTICS
Following notes are helpful in diagnosing issues, since it shows both processing information and all related data and setup.
In order to validate the data which is required to automatically source Item, Supplier, and Source Document details from a Blanket PO or Quotation onto a Requisition refer the Note 296191.1 ASL/Sourcing Diagnostic Guide. This Note also gives the data about setup which is required to automatically create Blanket Releases or Standard POs upon requisition approval.
Note 361859.1 Oracle Purchasing Requisition Data Collection Test will provide the details of the requisition which can be analysed for supplier, supplier site and ship to location details.
Note 374428.1 How To Troubleshoot a Problem In Procurement Workflows Using Scripts , will provide the diagnostic details for createPO workflow related setup and processing details.
IMPORTANT CHECKLIST
The Note 417966.1 Auto Sourcing And Auto Create/Release Checklist, explains complete checklist for the sourcing and ASL related issues. Following are the important scenario's or the issues with the important checkpoints.
1.When Supplier and Supplier Site not defaulting in requisition, check
a. Is the Profile option ' MRP: Default Sourcing Assignment Set ' populated with Correct value? b. What is the value populated in Assign Sourcing Rule for Item, Organization etc correct one? c. Sourcing Effective date is valid?
a. What is the Profile option Value set 'PO: Automatic Document Sourcing'?
b. Is there an approved Global BPA and the Global BPA is enabled in the requesting operating unit? c. Is the sysdate inside source document effective date range?
3.When Create Release program not creating Release,check a. Is the requisition approved?
b. Is there a valid BPA assigned to the requisition line(i.e in Source detail)?
c. Is the ASL attribute 'Release Generation Method' set to 'Automatic Release/Review' or 'Automatic Release'?
4.When CREATEPO workflow is not creating the desired resulting document, check
a. Is the Requisition Approval workflow attribute 'Send PO Autocreation to Background' set to Yes and Workflow Background Process concurrent program run to process the deferred Requisition Approval workflows. or Is the Requisition Approval workflow attribute 'Send PO Autocreation to Background' set to No?
b. Is the Create Documents workflow attribute 'Is Automatic Creation Allowed?' set to 'Yes'?
c. Is the Create Documents workflow attribute 'Should Contract be used to autocreate Doc?' set to 'Yes'? 5. Automatically Sourcing Rules, Assignments and ASL are not created, once the BPA is approved, check
a. Check the value for profile PO: Allow Auto-generate Sourcing Rules and ASL
b. If BPA is set as "Archive on Print", this feature cannot be used, one must select "Archive on Approve" to use this feature.
c. Process will update existing Sourcing rules only if:
-The Blanket purchase order Start and End Dates are not null.
-The blanket effective period do not overlap multiple effective periods in the Sourcing Rule. -The process will try to create or update Approved Supplier List entries even if Sourcing Rule creation/update failed for the item
-For Exception reporting, run the Purchasing Interface Errors report with a source type of 'ASR - Automatic Sourcing Rule Builder'
6.Attributes in CREATEPO workflow for Generating Release from a BPA: -Is Automatic Creation Allowed?
-Should Workflow Create The Release? -Is Automatic Approval Allowed?
7.When approved requisitions are imported through Requisition Import no purchase orders created,check -Autosource_flag value set to 'Y' in PO_REQUISITIONS_INTERFACE_ALL tables
- Is Automatic Creation Allowed? (CREATEPO attribute)
GENERAL ISSUES AND RELEVANT NOTES
Check any errors or logs related to the issue, and note the code objects and files mentioned. Check the version of these files in the instance exhibiting the problem.
Search the patches available for later releases of these files, and match the patch descriptions to the issue encountered. Apply as relevant.
To find patches: Visit Metalink > Patches and Updates > Advanced Search > Complete: Product / Release / Platform / Patch / Includes File [Enter file] > Click Go.
Some other common known issues and relevant notes: Setup:
Note 357432.1 Requisitions are Not Automatically Autocreated into Purchase Orders
Note 308470.1 Create and Approve a Standard PO Automatically From an Approved Requisition
Note 175935.1 ASL Sourcing to Requisitions Setup and Usage Guide
Note 273983.1 Steps Required For Creating ASL and Sourcing Rules From Blanket PO's
Note 465860.1 Supplier Item Number Does Not Get Updated On ASL
Note 332686.1 Purchase Order Entry Form Not Enforcing Use Approved Supplier Item Attribute Process:
Note 420433.1 What Are The Setups Necessary To Release An Item From The ASCP in purchasing ?
Note 420354.1 Automatically Create an Approved Release from Min-Max Planning Release 11.5.10
Note 237804.1 ASL not created when Existing Blanket Lines are updated and Blanket is re approved
Note 276315.1 Unable to Update or Create ASL/Sourcing Rules in Create Document Workflow
Note 331239.1 Contract Defined As Source Document in ASL is Not Defaulting On Requisition Line
Note 262458.1 Sourcing Fails When NeedByDate Is After The Effective Date Of Sourcing Rule
Note 414624.1 Troubleshooting MRP Imported Requisitions That Not Created Into Releases Error:
Note 371868.1 Creating an ASL gets the Message Duplicate Supplier/Supplier Site Being Defined For Item
Note 434234.1 BPA Cannot Be Submitted For Approval When There Is Multiple ASL Status For Supplier
Note 216444.1 POXPOEPO - Receive Error When Trying to Approve a Purchase Order With Items on an ASL FAQ:
Note 409053.1 FAQ Auto Document Sourcing Rules Update Options
LOGGING
For CREATEPO related issues Wfstatus and workflow Debug file can be further analyzed to find the cause. How to run wfstatus.sql for CREATEPO workflow?
Refer to Note 134960.1 Running WFSTATUS and WFRETRY For Oracle Purchasing Workflow How to Workflow debug and trace CREATEPO workflow?
Refer to Note 374428.1 How To Troubleshoot a Problem In Procurement Workflows Using Scripts
TRACING
For performance and data related issues, the note Note 170223.1: Trace 11i Bind Variables - Profile Option: Initialization, provides the detailed steps to get the trace with binds. Use the output to check tables and queries mentioned below for performance data and activities performed during execution.
DATA AND QUERIES
Table name Information Stored
PO_APPROVED_SUPPLIER_LIST Stores the relationship between an item or Commodity; a supplier, ship-to
organizations; and approval/certification status.
PO_ASL_ATTRIBUTES Stores all information for the supplier /item /organization relationship defined in
PO_APPROVED_SUPPLIER_LIST. This
information is maintained separately to allow each organization to define its own
attributes.
PO_ASL_DOCUMENTS Stores sourcing references to supply
agreements,blanket agreements, and catalog quotations associated with particular
suppliers and items in
PO_APPROVED_SUPPLIER_LIST
PO_ASL_STATUSES Stores the user-defined approval/certification statuses for the ASL.
MRP_SOURCING_RULES Stores sourcing rule names and descriptions.Define Sourcing Rule form populates this table.
MRP_ASSIGNMENT_SETS and
MRP_SR_ASSIGNMENTS Stores Sourcing Assignment Sets and storesthe assignment of sourcing rules respectively
The following script provides the CREATEPO workflow attributes check for relevant attributes referring to 'IMPORTANT CHECKLIST' section.
SELECT NAME, TEXT_DEFAULT,ITEM_TYPE FROM WF_ITEM_ATTRIBUTES
WHERE NAME = 'AUTOCREATE_DOC';
The following script provides the following ASL details for a Supplier.To find out PO_ASL_ATTRIBUTES.ASL_ID go to 'Approved Supplier List' window,
Help->Diagnostics-> Examine Block: ASL_SUPPLIERS
Field : ASL_ID
SELECT ALL PO_ASL_ATTRIBUTES.ASL_ID, PO_ASL_ATTRIBUTES.USING_ORGANIZATION_ID, PO_ASL_ATTRIBUTES.CREATION_DATE, PO_ASL_ATTRIBUTES.DOCUMENT_SOURCING_METHOD, PO_ASL_ATTRIBUTES.RELEASE_GENERATION_METHOD, PO_ASL_ATTRIBUTES.PURCHASING_UNIT_OF_MEASURE, PO_ASL_ATTRIBUTES.VENDOR_SITE_ID, PO_ASL_ATTRIBUTES.PRICE_UPDATE_TOLERANCE, PO_VENDORS.VENDOR_NAME, PO_APPROVED_SUPPLIER_LIST.ASL_STATUS_ID, PO_APPROVED_SUPPLIER_LIST.DISABLE_FLAG
FROM PO_ASL_ATTRIBUTES, PO_VENDORS, PO_APPROVED_SUPPLIER_LIST WHERE PO_ASL_ATTRIBUTES.ASL_ID=&ASL_ID
AND ((PO_ASL_ATTRIBUTES.VENDOR_ID=PO_VENDORS.VENDOR_ID)
AND (PO_ASL_ATTRIBUTES.ASL_ID=PO_APPROVED_SUPPLIER_LIST.ASL_ID));
The following script will show the following Supplier-Item attributes for an ASL entry.Check if the document is valid and the correct source document is used.
10/16/12 Document Display
SELECT ALL PO_ASL_DOCUMENTS.ASL_ID, PO_ASL_DOCUMENTS.USING_ORGANIZATION_ID, PO_ASL_DOCUMENTS.DOCUMENT_TYPE_CODE, PO_HEADERS_ALL.SEGMENT1,
PO_LINES_ALL.LINE_NUM, PO_HEADERS_ALL.STATUS_LOOKUP_CODE FROM PO_ASL_DOCUMENTS, PO_HEADERS_ALL, PO_LINES_ALL WHERE PO_ASL_DOCUMENTS.ASL_ID=&ASL_ID AND
((PO_ASL_DOCUMENTS.DOCUMENT_HEADER_ID=PO_HEADERS_ALL.PO_HEADER_ID) AND (PO_ASL_DOCUMENTS.DOCUMENT_LINE_ID=PO_LINES_ALL.PO_LINE_ID));
For the same ASL_ID (i.e for the same Supplier and Site combination, there are two ASL entries i.e Global (USING_ORGANIZATION_ID = -1) and Local (USING_ORGANIZATION_ID = XXX).
The following script provides the following Sourcing Rule details.Check for the sourcing effective date and sourcing status. To find out SOURCING_RULE_ID go to "Sourcing Rules" window ,
Help->Diagnostics-> Examine Block: MRP_SOURCING_RULES Field : SOURCING_RULE_ID
SELECT ALL MRP_SOURCING_RULES.SOURCING_RULE_ID, MRP_SOURCING_RULES.SOURCING_RULE_NAME, MRP_SOURCING_RULES.ORGANIZATION_ID, MRP_SOURCING_RULES.DESCRIPTION, MRP_SOURCING_RULES.STATUS, MRP_SOURCING_RULES.SOURCING_RULE_TYPE, MRP_SR_RECEIPT_ORG.EFFECTIVE_DATE, MRP_SR_RECEIPT_ORG.DISABLE_DATE, MRP_SR_SOURCE_ORG.SOURCE_ORGANIZATION_ID, MRP_SR_SOURCE_ORG.VENDOR_ID, MRP_SR_SOURCE_ORG.VENDOR_SITE_ID, MRP_SR_SOURCE_ORG.SOURCE_TYPE, MRP_SR_SOURCE_ORG.ALLOCATION_PERCENT, MRP_SR_SOURCE_ORG.RANK FROM MRP_SOURCING_RULES, MRP_SR_RECEIPT_ORG, MRP_SR_SOURCE_ORG WHERE MRP_SOURCING_RULES.SOURCING_RULE_ID=2
AND ((MRP_SOURCING_RULES.SOURCING_RULE_ID=MRP_SR_RECEIPT_ORG.SOURCING_RULE_ID) AND (MRP_SR_RECEIPT_ORG.SR_RECEIPT_ID=MRP_SR_SOURCE_ORG.SR_RECEIPT_ID));
The following script provides the details of an Assignment Set .Check if the assignment of the sourcing rule to the item is correct and the same sourcing rule is defined in the 'MRP: Default Assignement Set'.To find out
ASSIGNMENT_SET_ID go to 'Sourcing Rules' window, Help->Diagnostics-> Examine Block: ASSIGNMENT_SETS Field : ASSIGNMENT_SET_ID
SELECT ALL MRP_ASSIGNMENT_SETS.ASSIGNMENT_SET_NAME,
MRP_SR_ASSIGNMENTS.ASSIGNMENT_TYPE, MTL_SYSTEM_ITEMS_B.SEGMENT1,
MTL_SYSTEM_ITEMS_B.DESCRIPTION, MRP_SR_ASSIGNMENTS.SOURCING_RULE_TYPE, MRP_SOURCING_RULES.SOURCING_RULE_NAME FROM MRP_SOURCING_RULES,
MRP_SR_ASSIGNMENTS, MTL_SYSTEM_ITEMS_B, MRP_ASSIGNMENT_SETS WHERE MRP_ASSIGNMENT_SETS.ASSIGNMENT_SET_ID=21 AND ((MRP_ASSIGNMENT_SETS.ASSIGNMENT_SET_ID=MRP_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID) AND (MRP_SR_ASSIGNMENTS.SOURCING_RULE_ID=MRP_SOURCING_RULES.SOURCING_RULE_ID) AND (MRP_SR_ASSIGNMENTS.INVENTORY_ITEM_ID=MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID) AND (MRP_SR_ASSIGNMENTS.ORGANIZATION_ID=MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID)); CODE FILES
This process utilizes the following code files which can be checked for versions and later fixes;
poxwfatc.wft
Concurrent Program: PORELGEN - Create Releases
POXSRCDB.pls is an important procedure which performs automatic document sourcing based on item/item category, supplier/supplier site, and profile option settings
Other Important Files need to be checked: PORELGES.pls, POXWATCB.pls , POXVDCKB.pls, POXSCASL.fmb, MRPFDSRA.fmb, MRPFDDSR.fmb
To find patches: Visit Metalink > Patches and Updates > Advanced Search > Complete: Product / Release / Platform / Patch / Includes File [Enter file] > Click Go.
References
NOTE:175935.1 - ASL Sourcing to Requisitions Setup and Usage Guide
NOTE:262093.1 - Unable To Create Local Approved Supplier List Through PDOI
NOTE:285470.1 - ASL Sourcing Whitepaper
NOTE:417966.1 - Auto Sourcing And Auto Create/Release Checklist
