Diagnostics and Troubleshooting

This chapter covers the following topics:

Overview of Diagnostics and Troubleshooting

This section contains information about the diagnosing and troubleshooting of problems in Oracle Advanced Pricing. The following table provides a summary of various methods of diagnosing and troubleshooting the results of the pricing engine.

Important: For additional troubleshooting information, see the Oracle Advanced Pricing Troubleshooting White Paper (docNote 373269.1) available from My Oracle Support.

Diagnostics and troubleshooting
Diagnosing Method When to Use
Pricing Engine Request Viewer window in Sales Order Pad.
How to use: The Pricing Engine Request Viewer window is available from within Oracle Order Management. The navigation path is: Sales Order window > Tools > Pricing Engine Request Viewer.		 This method provides a subset of information as compared to the debug output file. It provides information such as the list line selected and deleted by the engine during processing, and the reason for deletion. It does not provide information on why the list line is not selected by the engine (qp_list_line_detail.sql is a useful mechanism for this).
This is a quick method to verify the data passed to the pricing engine and the data that was returned by the pricing engine.
This method determines whether the expected qualifiers and pricing attribute are sourced.
Since the Pricing Engine Request Viewer requests are stored in the permanent pricing debug tables, users can query previous pricing debug requests.
Debug output
How to for Oracle Order Management users: Set OM: Debug Directory to directory listed in util_file_dir in init.ora. Set OM: Debug_Level to at least 5.
How to for other users: Set profile QP:Debug to Yes. Set OM:Debug Directory to directory listed in util_file_dir in init.ora. Search the output file in the directory mentioned in above profile based on time-stamp.		 Use this method when diagnosing why a line is not selected by the engine. This is done to compare the output of the debug with the output of qp_list_line_detail.sql. This is also useful if developers do not have access to the online windows. This also provides Oracle Order Management Integration debug messages, and provides extended debug messages for development.
Warning: Because the Pricing Engine Request Viewer consumes a large amount of system resources, ensure it is turned off in the production system. For Oracle Order Management users make sure that the Debug Level is set to 0 and QP:Debug is No to turn it off.
script: qp_list_line_detail.sql
How to Use:
Get the price list line ID/modifier line ID from the Price List/ Modifier setup window.
Open the Price List/Modifiers window. Select the price list line/modifier line:
Help > Diagnostics > Examine > Pick the LIST_LINE_ID field from the Field LOV
Note the value. This list_line_id value must be provided as input to the script qp_list_line_detail.sql.
Login to apps/apps@sid.
Run the script $qp/patch/115/sql/qp_list_line_detail.sql to get all the price list line/modifier line information, which takes list line ID as input.
Make sure that the script outputs a <list line id>.lst file.
Alternately, you can run this script as a concurrent program and view the output in the concurrent request output file:
From the Oracle Pricing Manager responsibility, navigate to Reports, submit a request for the concurrent program Diagnostics: List Line Details, and enter the list line ID for the parameter.		 Use this method when the price list line or modifier is not selected by the engine. The script provides information about how the price list line or modifier is setup.
Use this method when the user knows the specific price list line or modifier line that should be selected by the engine, but it is not selected.
Use this method when the possibility exists that the denormalized columns are not being properly updated due to unusual user exceptions.
Verify setup using Oracle Advanced Pricing set up windows Certain setup information affects the pricing engine process. Verify that the following columns have appropriate values:
Incompatibility Resolve Code in event phase
Search_flag in event phase
Automatic_flag, active_flag in modifiers and price lists
Pricing Phase in modifier
Certain profile values affect pricing engine processes. Verify that the following profiles are appropriately set:

  • QP: Get Custom Price Customized

  • QP: Blind Discount Option

  • QP: Verify GSA Violations

  • QP: Return Manual Discounts
Verify engine control record and other record structure For power users: If you call the pricing engine directly, refer to the Oracle Manufacturing Suite APIs and Open Interfaces Manual for examples of a pricing engine call.

Summary of Pricing Engine Messages and Diagnosis

Price Lists Messages and Errors

The follow table summarizes pricing engine messages and provides an explanation and potential solutions.

Price not found on price list for item and UOM

Probable Cause How to Debug
The price list header is inactive. Select Active box on price list header.
The order line initially prices when you enter the item, UOM, and quantity. However, when you click Save, the unit selling price is set to NULL and an error message advises that the item is not available on the price list.

  • Select the Enforce List Price on the order type so the error does not occur.

  • Alternatively, creating an active modifier also resolves the issue.
The price list header is ineffective as of the pricing date. Select active dates on the price list header window. Blank dates mean no restriction (pricing effectivity date can be checked in the Pricing tab of Sales Order window).
The source system code on the price list is not correct. Verify request type, source system code mapping to make sure that appropriate source system codes are attached to the request type code.
The price list line is ineffective as of the pricing date. Select active dates on the Price List Lines window. Blank dates mean no restriction (pricing effectivity date can be checked in the Pricing tab of Sales Order window).
The qualifiers for the price list are not met or not passed to the pricing engine (attributes mapping). Occasionally, a qualifier is unintentionally created for a price list, which prevents the use of that price list and results in an error. If the price list has a qualifier, verify that the qualifier is passed to the engine.
The pricing attributes for price list are not met. Using the Pricing Engine Request Viewer window, verify that the pricing attributes are mapped.
The price break conditions are not met based on item quantity and item amount. Make sure that context volume and attribute line quantity is sourced properly for the descriptive flexfield Pricing Contexts. Use Pricing Engine Request Viewer window to verify this.
The product UOMs do not match. Check the Pricing Engine Request Viewer window to make sure that the correct UOM is passed.
Pricing engine call is made before the pricing attribute in the database is saved. Verify that the line is saved before making a call to the pricing engine. You may get an item not found on the price list error if the engine is not able to find the price list due to unavailability of pricing attribute.
An unusual error causes pricing performance related columns to be out of sync.

  1. Qp_list_line_detail.sql shows that the columns are not properly updated. Run the QP: Maintain de-normalized data concurrent program to correct this situation.

  2. Price list header currency is different from the order currency. Check currency.

Cannot Resolve Incompatibility Between Price List X and Price list Y

Probable Cause How to Debug
Engine could not use the passed-in Price List and found multiple matching price list lines while attempting to search other price lists. In the price list window, determine if the passed-in price lists have qualifiers. Also, verify that the price list is active. If the user intends for the engine to use the passed in price list, then debug this issue based on suggestions in the previous table.
Engine found multiple matching price list lines with the same precedence. Using the Pricing Engine Request Viewer window or the debug script, find the list line information of the selected list lines. Determine if one of the lines is selected unnecessarily due to missing pricing attributes. If not, update the precedence appropriately.

UOM is invalid

Probable Cause How to Debug
The pricing engine can not find the price list in the ordered UOM. Check the UOM on the order. Open the price list window and search the price list to find out if the price is defined in the ordered UOM.
No other matching price list line has the primary UOM flag selected. If user intends to define the price list in primary UOM and expects the engine to convert the pricing quantity, then verify that the primary flag of the price list line is selected.

Invalid UOM Conversion

Probable Cause How to Debug
The ordered UOM does not match the UOM on the price list line. If the user does not expect the pricing engine to do the conversion, then verify that the primary flag on the price list line is not set. Evaluate the reasons why the price list line with the ordered UOM is not being selected based on the previous table: Item and UOM not on Price List.
No conversion is defined in mtl_uom_conversions between the ordered UOM and the primary UOM. If the user expects pricing to convert the pricing quantity from ordered UOM to the pricing UOM, verify that the correct conversion is defined in Oracle Inventory.

Invalid Formula, Error Returned by QP_FORMULA_PRICE_CALC_PVT.Calculate

Probable Cause How to Debug
User does not use NVL in the expression and a step number (formula line) has null value. Verify that the pricing engine is selecting the expected price list line by using the debug window or debug script. Verify that the required pricing attributes are passed to the engine. If you use get_custom_price, verify that the that the function does not return a null value. If you use a factor list, verify that appropriate pricing attributes are being sourced, and that a matching factor line exists.
Formula is not a valid mathematical expression supported by the database sql. Verify that the formula is a valid expression.
Pricing engine call is made without passing all relevant pricing attributes. Dynamic formula is attached to the price list line. However, the pricing attributes are not entered before making the pricing engine call. Verify that the relevant pricing attributes are entered.
Formula is either not effective for the specified date or does not exist. Verify that the formula exists and is effective as of the pricing date.
Formula does not have at least one component. Verify that the formula has at least one component.
QP_CUSTOM.Get_Custom_Price( ) function does not exist or is invalid in database. Verify that function Get_Custom_Price( ) has been custom-coded in the package body of QP_CUSTOM and compiled successfully.
One of the pricing attributes is expecting a numeric value but is receiving a non-numeric value. a) Ensure that the pricing attributes used in the formula calculation always have the number valueset attached to it. The steps to verify are:

  1. Go to Oracle Pricing Responsibility.

  2. Go to Setup > Attribute Management > Context and Attributes.

  3. Query the context and attribute used in the formula setup.

  4. For that attribute, select if any number valueset is attached.

  5. 5. If none is attached, attach any number valueset to it.


Another way to check is to change the formula by making use of the TO_NUMBER function.
This method works only if the attribute value returned has only numeric characters. It will fail if any alphabetic characters are present in the attribute value.
The formula could contain undefined step numbers. Verify that all of the step numbers in the formula are defined.

Modifier Messages and Errors

Expected Modifier Not Selected by the Engine

Probable Cause How to Debug
Similar to message: Item and UOM not found. Refer to previous table: Item and UOM not on Price List.
Qualifiers for discount list and line are not met. Qualifiers in -1 group are added to all groups. Make sure that qualifiers in the -1 group are satisfied.
Modifier is eliminated in incompatibility. As a test, temporarily remove the incompatibility group from the modifier line, and then run the engine call and verify that the modifier is selected. If yes, then check which other modifier is selected from the same incompatibility group. Determine if the precedence must be changed. Also, determine if the exclusive group contains a modifier.
Modifier UOM is different from Pricing UOM. Check modifier UOM. Blank UOM means that any UOM is allowed.
Modifier header currency is different from the order currency. Check modifier currency.
Modifier is manual; it is not automatically set. Check automatic flag on the modifier.
Asked_For flag is Y and the modifier is not asked for. Check Asked For flag on the modifier. If the user has asked for the modifier then use debug window/output to verify that Asked For is passed to the engine. If passed, then determine whether Asked For was validated. If not passed, determine whether the qualifiers are matched. Refer to the incompatibility processing flowchart for more details.
Pricing phase for the modifier line is not attached to the appropriate pricing event. Verify that the pricing phase on the modifier is attached to the appropriate event. Use caution with the event-phase setup because it can impact the pricing of the entire organization.
The qualifier, sourced item attribute, sourced pricing attribute are setup recently, however, QP Build Sourcing concurrent program does not run. Determine in the Pricing Engine Request Viewer window/debug file whether the qualifier/pricing/item attributes are sourced. If this is a new type of attribute then run the concurrent program.
There is no record in the qp_list_header_phases. An unusual user error can cause the qp_list_header_phases to populate incorrectly to include all phases. Run the QP: Maintain Denormalized Data concurrent program for this header to resolve the issue.

Modifiers: Incompatibility does not consider best price/precedence

Probable Cause How to Debug
Incompatibility resolution code is set incorrectly. Refer to previous table: Item and UOM not on Price List.
Customer has not licensed Oracle Advanced Pricing. Oracle Advanced Pricing customers can choose to resolve the incompatibility processing by best price or by precedence. Oracle Order Management (basic pricing) customers only have the best price option.

Modifiers: Unable to override selling price/manual adjustments

Probable Cause How to Debug
Manual discounts are not available. Save the order line or move the cursor out of the line and back; this action causes Oracle Order Management to fire the pricing engine modifier phase.
Order level adjustments are not applied. Order level adjustments are not applied if any lines in an order has a calculate price flag of partial price or freeze price.
The unit selling price and modifier LOVs only show unapplied manual adjustments. Overtype the unit selling price and increase the price to apply overrideable surcharges. Decreasing the price applies overrideable discounts.

Calculation: Back Calculation Error

This error happens when user tries to override the selling price on a quote and the pricing engine is not able to find a suitable manual overrideable adjustment to give the overridden selling price. Refer to the Integration chapter under Manual adjustments for more details.

Probable Cause How to Debug
No overrideable Manual adjustments available. Check if there are any active manual overrideable adjustments.

Concurrent Program: QP: Maintains the denormalized data in QP qualifiers

Probable Cause How to Debug
FDPSTP failed due to ORA-06502: PL/SQL: numeric or value error: character to number conversion error. This error occurs because of a mismatch in the parameters sequence. Check with Support for an ARU to correct this.
Program has been running for a long time. This program updates rows in the qp_qualifiers table. If the user has selected ALL headers, then the program requires time to run.

Integration and Attributes Mapping Messages and Errors

Unexpected Error In Calculate_adjustments#130 User_defined Exception

Probable Cause How to Debug
QP_Attr_Mapping_PUB.Build_Contexts package is invalid due to incorrect sourcing data attributes mapping. Check dba_errors for this package in or to determine which attribute sourcing API is causing the error. If this is a custom API, then correct the API. If this is the seeded API, then determine whether a correction patch is available.
Concurrent Program Build Sourcing Rules failed with error. Run the following statement and examine the output:
select text from dba_errors where name ='QP_BUILD_SOURCING_PVT'
Verify that custom sourcing causes the error.
Getting error while running Build Sourcing Rules concurrent program ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at APPS.QP_ATTR_MAPPING_PUB, line 1445 ORA-20000: ORA-04021: timeout occurred while waiting. This error occurs if you make a pricing call while the concurrent program is running. Do not run the Build Sourcing Rules concurrent program when active users are calling pricing engine.
While entering the order line in sales order PAD receives an error: FND_AS_UNEXPECTED_ERROR (PKG_NAME=oe_order_adj_pvt) (PROCEDURE_NAME=oe_line_adj.calculate_adjustments) (ERROR_TEXT=calculate_adjustments#130 ORA-06508: PL/SQL: could not find program unit being called). Run the following statement and examine the output:
select text from dba_errors where name ='QP_BUILD_SOURCING_PVT';
Determine whether any custom sourcing causes the errors.
If the seeded sourcing rule causes this error, determine whether a patch is available to correct the seeded rule.
If the error is “Encountered the symbol ‘_' when expecting…” then determine which patch to apply.

Freight Charges Integration Messages and Errors

Cost to Charge Conversion is not Returned by the Engine

Probable Cause How to Debug
Qualifiers may be attached to the freight charge header or line. Run qp_list_line_detail.sql script for the expected freight modifier and verify that all the qualifiers are being passed to the engine.
Oracle Shipping Execution passed a different charge type than that set up in the engine. Verify that the same charge type/cost type is used in Oracle Shipping Execution and Oracle Advanced Pricing.
Pricing engine does not return a specific freight charge Pricing engine now returns only the maximum freight charge modifier for every charge name. Make sure that the freight charge that you expect is the maximum freight charge, deactivate other freight charges higher than this freight charge, or see if you can put this freight charge in a different charge name.

Common Troubleshooting Problems in Pricing Windows

Price List Problems

Potential price list problems and tips about how to solve them are outlined below.

Problem 1

The price lists have a NULL value in the Multi-Currency Conversion field after the upgrade.

Suggested Action:

Ensure that the concurrent program Update Price Lists with Multi-Currency Conversion Criteria has been run. Before running the program, set the profile QP: Multi Currency Installed to Y (Yes). If the concurrent program is not run, the value of the Multi-Currency Conversion field will be NULL.

When the profile QP: Multi Currency Installed is set to Y and the Price List window is open, a default multi-currency conversion record is created if no record is found for the defaulted currency code and rounding factor. This occurs when the defaulted currency is USD and the rounding factor is -2.

Problem 2

Problems such as no values in LOVs occur while running pricing reports in Oracle Order Management.

Suggested Action:

Determine if obsolete reports are running. There are currently five reports related to Oracle Advanced Pricing:

All other reports related to pricing are no longer used.

To add a pricing report to the Oracle Order Management responsibility request group, login under System Administrator responsibility. Navigate to:

Query your request group.

To determine which request group is attached to the Oracle Order Management responsibility, navigate to:

Query the Oracle Order Management responsibility. Add the request from the Application Oracle Advanced Pricing.

Problem 3

Problems occur while running copy price list, adjust price list, add items to price list, or update formula prices through Standard Request Submission.

Suggested Action: Each of the mentioned operations is a concurrent program that has its own window that submits a request. Requests must be submitted through those forms and not Standard Request Submission. The forms have checks for mandatory parameters which are not found in the Standard Request Submission window. You can locate these forms in the sub menus for Oracle Advanced Pricing.

Problem 4

LOV for product attribute value on the price list window does not display any values (items).

Suggested Action:

Verify that the value for profile option QP: Item Validation Organization (Oracle Order Management SuperUser responsibility, Setup > Profiles) is Vision Operations. Also verify under Setup > Parameters that organization Vision Operations.

Problem 5

Product attribute values (when product attribute is item category) change to X automatically on list line in price list/modifier window after querying a price list/modifier window.

Suggested Action:

The view mtl_categories_kfv is not properly regenerated. Re-compile the flex field for item categories.

Promotional Limits Troubleshooting

Problem

Limit Balance record not created.

Suggested Action:

Check if the limit setup has the each organization check box selected. This feature is not currently supported and therefore, balance records are not created. The Limit with Limit Id as indicated in the message has multiple balances records. Keep the Organization box selected or change the limit setup. Check if the modifier list for which the limit is setup is active and automatic.

For any other issues, please look at the engine debug file and investigate in the limits engine code.

Promotional Limits Integration Messages

Problem 1

Limit Exceeded for Promotion Number &PROMOTION_NUMBER and Limit Number &LIMIT_NUMBER by the amount of &LIMIT_EXCEEDED_BY units.

Probable Cause:

The soft limit setup for the Modifier List as indicated in the message has been exceeded and current limit balance is negative. This is an informational message.

How to Debug:

User can increase the Limit available for the Modifier List in the Limits setup window.

Problem 2

Limit Exceeded for Modifier Number &MODIFIER_NUMBER and Limit Number &LIMIT_NUMBER by the amount of &LIMIT_EXCEEDED_BY units.

Probable Cause:

The soft limit setup for the Modifier as indicated in the message has been exceeded and current limit balance is negative. This is an informational message.

How to Debug:

User can increase the Limit available for the Modifier in the Limits setup window.

Problem 3

Modifier &OPERATOR &OPERAND for Limit Number &LIMIT_NUMBER and Promotion Number &PROMOTION_NUMBER adjusted to &PERCENT.

Probable Cause:

The hard limit setup for the Modifier List as indicated in the message has been adjusted and current limit balance is zero. This is an informational message. This means that the modifier to which this limit is attached will no longer be available unless the limit is increased.

How to Debug:

User can increase the Limit available for the Modifier List in the Limits setup window.

Problem 4

Modifier &OPERATOR &OPERAND for Limit Number &LIMIT_NUMBER and Modifier Number &MODIFIER_NUMBER adjusted to &PERCENT.

Probable Cause:

The hard limit setup for the Modifier as indicated in the message has been adjusted and current limit balance is zero. This is an informational message. This means that the modifier to which this limit is attached will no longer be available unless the limit is increased.

How to Debug:

User can increase the Limit available for the Modifier in the Limits setup window.

Problem 5

Limit Id &LIMIT has multiple balance records. A limit with no limit attributes or specific limit attributes (not 'Each' type) must have one and only one limit balance record.

Probable Cause:

The Limit with Limit Id as indicated in the message has multiple balances records.

How to Debug:

User must delete dubious or duplicate balance records and leaving only the correct balance record in database.

Problem 6

The variable QP_PREQ_GRP.G_ORDER_PRICE_REQUEST_CODE cannot be null. This can corrupt Promotional Limit Balances. So limits will not be consumed. Please investigate.

Probable Cause:

The variable QP_PREQ_GRP.G_ORDER_PRICE_REQUEST_CODE is being passed null value by the OM Integration code.

How to Debug:

Please investigate OM/QP integration code with the help of the engine debug file.

Pricing Formula Problems

Problem 1

Get custom price function in a pricing formula returns null.

Suggested Action:

Add customized code for the get custom price function in the QP_CUSTOM package body and not in another package. The profile option QP: Get Custom Price Customized must be set to yes (using System Administrator responsibility) if get custom price function has been customized and used in any formula.

Problem 2

When setting up a formula with price list line formula line, querying all values in the LOV for price list lines and scrolling through it causes the server to disconnect.

Suggested Action:

This LOV has a large number of values. Issuing a query for all values in the LOV and scrolling through the values causes the server to disconnect. Use a more specific or reduced search.

Problem 3

When entering an order line with an item that has a formula-based price, an error message is received: use NVL around potential null formula components.

Suggested Action:

This error occurs if any component in a formula evaluates to a null when the pricing engine determines the price of an item during order entry. A formula component may have a null value when it is a pricing attribute type and the value for the pricing attribute must be entered on the sales order. In this case, enter pricing attributes on the order and save. Always enter pricing attributes that are expected in the formula before proceeding to use NVL.

Pricing Organizer Problems

Scenario 1

User wants to query on modifiers that are effective from March 1, 2002 and enters the following query criteria:

Field Name in Header tab Value
Type Discount List
Exact Effective Date Match Selected
Exact Effective Date Match Selected
Effective From 01-MAR-2002
Effective To Blank

Problem

The modifier lists on the Headers tab in the Modifiers Organizer are not returned.

Suggested Action:

Since Exact Effective Date Match is checked, the query will match those modifiers with the exact effectivity dates as those in the modifier setup. The query will only return the modifiers lists (defined in the Modifier setup) which have Start Date =01-MAR-2002 and End Date = <Blank>. For Modifier Lines and Qualifiers, the Exact Effective Date Match will compare the query criteria to the modifier setup.

Scenario 2

User wants to query on line level modifiers in the Lines tab and enters the following query criteria:

Field Name in Lines tab Value
Level Line
Formula <ANY FORMULA>

Problem

The modifiers are not returned in query results even though there are modifier lines of Level 'Line'.

Suggested Action:

By adding the query criteria of Formula=Any Formula, the query will only return those modifier lines that have any formula attached to the line AND is of Modifier Level =Line. These are 'AND' conditions. To query on just line level modifier lines, leave the Formula field blank in the query.

Scenario 3

User wants to query on modifiers in USD currency and has entered Product Attributes=Products as in the following example:

Tab Name in Pricing Organizer window Field Name Value
Header tab Currency USD
Product Attributes tab Product Attributes Products

Problem

The modifiers returned in query results include all modifier lines that have a product attribute value.

Suggested Action:

By giving Products Attribute=Product, this becomes an additional query criteria and the results will include modifier lines that have a product attribute value. If Product Attributes=Not Specified, then the query will return all modifier lists with Currency=USD.

Scenario 4

User wants to query only on Promotions but has entered Qualifiers=No Qualifiers.

Tab Name in Pricing Organizer window Field Name Value
Header tab Type Promotion
Qualifiers tab Qualifiers No Qualifiers

Problem

The modifiers returned in query results are those promotions that do not have qualifiers attached.

Suggested Action:

If 'No Qualifiers' is selected for Qualifiers in the Qualifiers tab, only modifier lists that do not have header level qualifiers will be returned ( on the Headers Tab of the Modifier Organizer.) The query will also return modifier lines that do not have any line level qualifiers attached (on the Lines Tab of the Modifier Organizer). If Qualifiers=Not Specified, then the query will return all modifier lists that are promotions, and not modifier lines.

Scenario 5

A customer wants to query on discount where General Technologies is used as a customer name qualifier

Tab Name in Pricing Organizer window Field Name Value
Header tab Type Discount
Qualifiers tab Qualifiers Qualifiers
Qualifiers tab Qualifier Context1 Customer
Qualifiers tab Qualifier Attribute1 Customer Name
Qualifiers tab Operator1 =
Qualifiers tab Value From1 General Technologies

Problem

The query results show only Modifier Lists and not Modifier Lines.

Suggested Action:

The qualifier Customer Name=General Technologies is only attached as a list level qualifier, thus the query will return modifiers list with Modifier Lists=Discount. If this qualifier is used as a line level qualifier, then the query will show all the modifier lines that have this qualifier (in the Lines tab of the Modifier Organizer).

Scenario 6

When viewing an order level charge in Order Management, the Charges window shows only the Charge Name, Type, and the charge value. The Modifier Name or modifier number is not on the window. The following example demonstrates a query for modifier lines with this Charge Name:

Tab Name in Pricing Organizer window Field Name Value
Header tab Type Freight and Special Charges

The query returns all modifier list=Freight and Special Charges and no modifier lines. The User still cannot find the modifier line.

Suggested Action:

In order to get the specific modifier lines (in the Lines tab of the Modifier Organizer), you have to include Charge Name as a additional query criteria.

Multi-Currency Scenarios

Scenario 1: Resolving Error "For TRANSACTION conversion type, Base Currency and Functional Currency are not same"

For conversion type as 'TRANSACTION', Base Currency of the Price List and Functional Currency must be same.

In Price List window:

In Multi-Currency Conversion window:

In Order Management Sales Order pad:

Problem

While placing an order, user is getting the error - For TRANSACTION conversion type, Base Currency AUD and Functional Currency USD are not same.

Suggested Action:

Use a price list that has currency as USD and the multi-currency conversion attached to it has a record for To Currency Code CAD and Conversion Type TRANSACTION. This occurs because the Base currency of the price list and functional currency must be same for conversion type 'TRANSACTION'.

Scenario 2: Resolving Error "No conversion rate found"

Set up the currency conversion rate in Oracle General Ledger before using conversion type of Oracle General Ledger.

In Price List window:

In Multi-Currency Conversion window:

In Order Management Sales Order pad:

Problem

While placing an order, user is getting the error - No conversion rate found: From Currency USD, To Currency CAD, Conversion Date 25-JUN-2002, Conversion Type Corporate.

Suggested Action:

Set up the conversion rate in Oracle General Ledger for the From Currency USD, To Currency CAD, Conversion Date 25-JUN-2002 and Conversion Type Corporate. As the conversion type "Corporate" is being used in Sales Order pad, which is one of conversion types defined in Oracle General Ledger, the necessary set up must be done before placing an order.

Scenario 3: No conversion type is passed from OM

Conversion type must be passed from Sales Order pad to use multi-currency conversion of type TRANSACTION .

In Price List window:

In Multi-Currency Conversion window:

In Order Management Sales Order pad:

Problem

While placing an order, user is getting the error. No conversion type is passed from OM.

Suggested Action:

Provide the value for Conversion Type/Conversion Rate in Sales Order pad. As the order currency CAD is set up as conversion type TRANSACTION in multi-currency conversion list MC2, it is mandatory to pass either conversion type or conversion rate from Sales order pad.

Scenario 4: Formula Calculation Failure

In Price List window:

In Multi-Currency Conversion window:

In Pricing Formulas window:

In Order Management Sales Order pad:

Problem

While placing an order, user is getting the error - Formula calculation failure.

Suggested Action:

Pass a numeric value for Pricing Attribute "Export Cost". While using formula, make sure all the necessary information is available to correctly evaluate the formula.

Scenario 5: Formula Calculation Failure

How the effective dates work for multi-currency setup.

In Price List window:

In Multi-Currency Conversion window:

In Order Management Sales Order pad:

Problem

How the effective dates work for multi-currency setup?

Suggested Action:

As per scenario V above, the unit price returned by pricing engine for item AS54888 will be 667 (1000 * 0.667) because pricing effective date is 25-JUN-2002. Conversion type .7 passed from Sales Order pad is ignored by pricing engine because the effective dates of TRANSACTION conversion type in multi-currency setup does not satisfy the pricing effective date passed from Sales order pad. Instead, the pricing engine chooses the FIXED conversion type record from multi-currency setup as it satisfies the pricing effective date.

Scenario 6: Markup with Multi-currency setup

The sequence of conversion, markup, and rounding operations.

In Price List window:

In Multi-Currency Conversion window:

In Order Management Sales Order pad:

Problem

How the markup works for multi-currency setup?

Suggested Action:

From the preceding scenario, the unit price returned by pricing engine for item AS54888 will be 673 ((1000 * 0.667) + 6) because the markup will also be applied after conversion. In the process of multi-currency conversion, the list price is first converted to the order currency, then the markup is applied (if applicable), and finally, the rounding is done.

Other Technical Considerations

Pricing Engine

Verify that all the prerequisite (including server technology) patches are applied.

Because the pricing engine uses temporary tables, on-line patching may create a deadlock and the patch may fail.

Temporary tables are created in TEMP table space, hence TEMP table space must be sized based on the size of the largest sales order data. Temporary tables are not sharable.

Troubleshooting ADPATCH Errors

Log files are written to APPL_TOP/admin/<db_name>/log, where <db_name> is the value of your ORACLE_SID or TWO_TASK variable.

For NT, the file is placed in%APPL_TOP%\admin\<db_name>\log, where <db_name> is the value of your local variable.

Review the log file for error messages after you run the utility. There may be one or more worker files if you are running steps that operate in parallel mode.

Review these adwork<number>.log files (adwork01.log, adwork02.log) for more detailed information about the errors.

Oracle 8i Temporary Table Locking/Patching Issues

Note: While running adpatch, an attempt to alter, create, or drop an index on a temporary table already in use results in an error. If any Oracle Order Management or Oracle iStore user is pricing a line, the temporary tables are in use and adpatch encounters this error. Apply these patches only when users are not in Oracle Advanced Pricing.

Oracle Advanced Pricing patches attempt to drop and create Oracle8i temporary tables. Refer to the following instructions to verify that there are no processes accessing these tables before starting to apply the patch. The following script reveals which temporary tables are in use or locked (although the database session does not exist).

Before starting to apply the patch, the following two SQL statements should return no rows. Run the following statement:

select a.sid,a.serial#,c.object_name
from all_objects c , v$lock b, v$session a
where c.object_name in
('QP_PREQ_LINES_TMP','QP_PREQ_LDETS_TMP','QP_PREQ_LINE_ATTRS_TMP',
'QP_PREQ_RLTD_LINES_TMP','QP_PREQ_QUAL_TMP')
and c.object_type = 'TABLE'
and c.object_id = b.id1
and b.sid = a.sid;

If the above SQL returns rows, the sessions must be ended. It is possible that the session is ended but reference to that session still exists in v$lock table. Run the following statement:

select a.sid
from  v$lock a
where a.id1 in ( select b.object_id
from all_objects b
where b.object_name in
('QP_PREQ_LINES_TMP','QP_PREQ_LDETS_TMP','QP_PREQ_LINE_ATTRS_TMP',
QP_PREQ_RLTD_LINES_TMP','QP_PREQ_QUAL_TMP'))
and not exists (select 'x'
from v$session c
where a.sid = c.sid);

If the above statement returns rows, the database must be brought down. Once the database is brought up, run the above SQL statements and verify that no rows are selected before you apply the patch.

Attribute Management Troubleshooting

The following table lists some potential attribute management troubleshooting problems and their solutions:

Problem Description Solution
QP_Attr_Mapping_PUB package is invalid due to incorrect attributes mapping setup. Check dba_errors for this package to determine which attribute mapping API is causing the error. If this is a custom API then correct the API. If this is the seeded API then determine whether a correction patch is available.
The concurrent program Build Attribute Mapping Rules failed with error. Run the following statement and examine the results:

  1. Select line || ‘/’ position POS, text from dba_errors where name=’QP_BUILD_SOURCING_PVT_TMP’.

  2. Determine which attribute mapping API is causing this error.

  3. If this is a custom API then correct the API. If this is the seeded API then determine whether a correction patch is available. Please refer to Point 14 for Patches available for upgrade issues in seeded attribute sourcing rules.

  4. Run the following SQL to get QP_BUILD_SOURCING_PVT_TMP package body and check the attribute mapping rule that might be causing syntax failure:

    > set head off    
> set pagesize 100   
> spool pkg_body     
> select text from all_source        
> where name= 'QP_BUILD_SOURCING_PVT_TMP'    
> and  type = 'PACKAGE BODY' 
> and owner = 'APPS' 
> order by line;         
> spool off
The following error occurs while running Build Attribute Mapping Rules concurrent program: ORA-04021: timeout occurred while waiting. This error occurs when someone makes a pricing call while the concurrent program is running. Do not run the Build Attribute Mapping Rules concurrent program when active users are calling the pricing engine.
While entering the order line in sales order pad receives an error: END_AS_UNEXPECTED_ERROR (PKG_NAME=oe_order_adj_pvt) (PROCEDURE_NAME=oe_line_adj.calculate_adjustments) (ERROR_TEXT=calculate_adjustments#130ORA-06508: PL/SQL: could not find program unit being called). Run the following statement and examine the output: 
select line || ’/’ || position POS, text from dba_errors where name=’QP_BUILD_SOURCING_PVT’;

Determine whether any custom sourcing API causes the errors. If the seeded sourcing rule causes this error, determine whether a patch is available to correct the seeded rule. If the error is “Encountered the symbol _ when expecting…,” then determine which patch should be applied.
After you run Build Attribute Mapping rules, a message confirms that the Build Attribute Mapping program ran successfully; however, the Attribute Mapping status box of the attribute that you just linked is still deselected. Verify that the following prerequisities were completed:

  1. The Attribute Mapping Enabled check box was not selected.

  2. Attribute is used in pricing setup. Please verify that the Used in Setup check box was not selected.

  3. The Attribute Mapping Method was not ATTRIBUTE MAPPING. Attribute Mapping Methods other than ATTRIBUTE MAPPING do not need to be mapped. If the attribute that is linked was a new one, it must be used in at least one valid pricing setup.

  4. Attribute Sourcing Rules have been defined.
Attribute Mapping setup is correct and the flags are set, but still Attribute is not mapped. Verify that Attribute Mapping Method is set to ATTRIBUTE MAPPING. Please note that USER ENTERED and CUSTOM SOURCED attributes will not be sourced by Build Mapping Rules program and need to be passed by the calling application and custom package, respectively.
If Attribute Mapping Method=’ATTRIBUTE MAPPING’, please verify that:

  1. Used in Setup is set to yes.

  2. Attribute Mapping Enabled is set to yes.

  3. Attribute Mapping Status is set to yes.

  4. Mapping rules have been defined for the request type (such as ONT). If Level is BOTH, mapping rules should be defined for both the Header and Line levels.

  5. For the request type, verify that:

    • the Enabled flag is selected for attribute mapping rules

    • the seeded value string and user value string are valid at Header and Line levels (depends on the mapping level defined).

  6. Run Attribute Mapping Rules Error Report to check the Errors in mapping rules.


If Attribute Mapping Method is CUSTOM SOURCED, please verify that the Custom Sourcing Package and API call defined in the sourcing rule is valid and that profile QP: Custom Sourced is set to Yes.
While pricing setup, the attribute is mapped dynamically, but still Attribute Sourcing Status check box is not selected. Dynamic attribute mapping sets the used_in_setup flag for the attribute in attribute mapping, if the attribute is used in active pricing setup. That is, if Active Checkbox is selected for Price list/Modifier/Fomula setup. User will need to run the Build Attribute Mapping Rules concurrent program to set the Attribute Mapping Status flag.
I removed the attribute from the pricing setup, but the Used in Setup flag is still set. You need to run the Build Attribute Mapping Rules concurrent program. This program will clear used in setup and attribute source status flags for the attributes that are not used in the setup.
Inactivated the pricing setup, but the Used in Setup and Attribute Source status flags are still selected for the attribute. Run Build Attribute Mapping Rules concurrent program. It will clear Used in Setup and Attribute Source status flags, if attribute is not used in active pricing setup, and if profile option QP: Build Attribute Mapping Options is set to Map attributes used in active pricing setup.
Running Build Attribute Mapping Rules concurrent program only sets attribute sourcing status flag for the attribute used in setup. How do I source all attributes even if they are not used in active setup? Build Attribute Mapping Rules uses the profile option QP: Build Attributes Mapping Options. If the value is set to "Map all attributes," it will source all the attributes regardless of whether the attribute is used in active pricing setup or not. If value is set to "Map attributes used in active pricing setup," Build Attribute Mapping Rules program will source only attributes that are used in the active setup. This profile can be controlled only at the SITE level.
Attribute is used in pricing setup, but the Used in Setup flag is not selected. Verify that the profile option QP: Build Attribute Mapping Options value is set to "Map attributes used in active pricing setup." If so, please check Active flag is checked for the Pricing Setup (Price list/Modifier/Formula, etc) and run Build Attribute Mapping Rules concurrent program.
Attribute Mapping Method is set to ‘CUSTOM SOURCED’ and Build Contexts failed during call to pricing engine. Please verify that the Custom attribute sourcing procedure QP_CUSTOM_SOURCE.Get_Custom_Attribute_Values is defined and is not throwing any exception/error. Please also check OM debug log with level 5 and check that any exception is thrown while invoking a call to the custom procedure.
Attribute Mapping and Sourcing Rules setup are correct and the Build Attribute Mapping Rules program ran. However, the attribute is still not sourced. Please confirm the following:

  • Verify QP_BUILD_SOURCING_PVT package body source and confirm the problematic attribute is sourced.

  • In calling application, please verify the attributes have been passed to the pricing engine through Pricing Engine Request Viewer and the value passed for the attribute is correct and NOT NULL.

  • Please verify if sourcing rule is valid and the API returns the expected value for the input value passed from calling application in SQL Plus* prompt. For PL/SQL Multi-record API type, please use the anonymous PL/SQL block to check API as follows:

//Block starts.
        SET serveroutput ON 
       DECLARE 
       l_bg_tbl  qp_attr_mapping_pub.t_multirecord; 
       BEGIN 
        - Please input appropriate values. 
       FND_GLOBAL.apps_initialize(l_user_id, l_resp_id,l_appl_id); 
       /*The following is sample API call.
           Please replace it with the API call you want to
           Validate and input appropriate values.
       */
       l_bg_tbl :=ams_qp_qual_pvt.get_buying_groups
                   (&party_id, &order_line_sold_to_org_id);
       IF l_bg_tbl.count > 0 THEN
            FOR i in l_bg_tbl.first..l_bg_tbl.last LOOP
             -  Check the value(s) returned from the API.
            END LOOP;
       ELSE
            -  API does not return any value for the input
              Value. If it is custom API, please check API,
              If it is seeded, please log bug against appropriate product who owns the API. 
       END IF;
END;
//Block ends.
Need to check seeded/custom defined attribute mapping and sourcing rules are valid after upgrade. Please run the following SQL to get attribute mapping and sourcing rule details of attributes defined in Qualifier/Pricing Attribute contexts and the Product context. Please check Enabled_flag is set to Y for all the seeded sourcing rules, and seeded_value_string and user_value_string values. 
SELECT       A.PRC_CONTEXT_TYPE, A.PRC_CONTEXT_CODE, 
        A.SEEDED_FLAG SEEDED_CONTEXT, 
        A.ENABLED_FLAG CONTEXT_ENABLED,
        B.SEGMENT_ID, B.SEGMENT_CODE CODE, 
        B.USER_PRECEDENCE,
                  B.SEEDED_FLAG SEEDED_ATTRIBUTE,
        B.SEGMENT_MAPPING_COLUMN COLUMN_MAPPED,
        C.SEGMENT_LEVEL LEVEL_CODE, 
        C.SEEDED_SOURCING_METHOD,
        C.USER_SOURCING_METHOD SOURCING_METHOD, 
        C.SOURCING_ENABLED SOURCING_ENABLED,
        C.USED_IN_SETUP USED_IN_SETUP,
        C.SOURCING_STATUS SOURCE_STATUS,
        C.LOV_ENABLED LOV_ENABLED,
        C.LIMITS_ENABLED LIMITS_ENABLED,
        B.AVAILABILITY_IN_BASIC AVAIL_IN_BASIC
FROM    QP_PRC_CONTEXTS_B A,
        QP_SEGMENTS_B B,
        QP_PTE_SEGMENTS C
WHERE   A.PRC_CONTEXT_TYPE IN
                 ('PRODUCT', 'QUALIFIER', 'PRICING_ATTRIBUTE')
AND     B.PRC_CONTEXT_ID = A.PRC_CONTEXT_ID
AND     C.SEGMENT_ID = B.SEGMENT_ID
AND     C.PTE_CODE = &pte_code
ORDER BY
        PRC_CONTEXT_TYPE,  A.PRC_CONTEXT_CODE;
Attribute Sourcing Rules details :-
SELECT  A.PRC_CONTEXT_TYPE, A.PRC_CONTEXT_CODE, 
        A.ENABLED_FLAG,
        B.SEGMENT_CODE, B.SEGMENT_MAPPING_COLUMN,
C.REQUEST_TYPE_CODE, C.ATTRIBUTE_SOURCING_LEVEL,
C.SEEDED_SOURCING_TYPE, C.SEEDED_VALUE_STRING,
C.USER_SOURCING_TYPE, C.USER_VALUE_STRING,
C.ENABLED_FLAG
FROM    QP_PRC_CONTEXTS_B A,
QP_SEGMENTS_B B,
QP_ATTRIBUTE_SOURCING C
WHERE   A.PRC_CONTEXT_TYPE IN
                ('PRODUCT', 'QUALIFIER', 'PRICING_ATTRIBUTE')
AND     B.PRC_CONTEXT_ID = A.PRC_CONTEXT_ID
AND     C.SEGMENT_ID = B.SEGMENT_ID
ORDER BY
                A.PRC_CONTEXT_TYPE, A.PRC_CONTEXT_CODE, 
                B.SEGMENT_CODE, C.REQUEST_TYPE_CODE;
The context of an attribute that was successfully mapped did not show up in the Order management Sales Pad Pricing Context list of values. One of the following solutions may resolve the issue:

  • The only contexts that will show up in the OM Sales Order Pad are the ones that have at least one attribute that is USER ENTERED. If the context has all its attributes as mapping method ATTRIBUTE MAPPING, this context will not show up Pricing Context List of values.

  • You must create all new attributes using the Attribute Manager Context and Attributes window. Using this method, all attributes of type Pricing Attribute will be created in the OM Flexfield and the flexfield will be registered (if required); its definitions will freeze and then are compiled automatically. Check the Concurrent Manager requests to see if the request was completed as Normal. Remember, the converse is not true. An attribute created using the Flexfield windows will not be created in the Attribute Manager tables. Columns updated in Flexfield window are also not supported in Pricing.
PL/SQL: could not find program unit error message is recorded in OM Debug log file and no attributes sourced.

  • Please verify if all the database objects are valid. If any INVALID Objects found, please recompile all INVALID objects

  • Please check dba_errors table for errors logged in one of the packages used in sourcing rules. Check the error, and recompile the package. If package does not recompile (and it is an Oracle provided package), please check if any patch is available to resolve the package error.

  • Verify if you are using custom attributes and custom sourcing rules. If QP: Custom Sourced profile is set to Yes and if the error occurs in Custom Procedure, please correct the error.
Attribute Mapping of an attribute is ‘CUSTOM SOURCED’, but this attribute is not sourced correctly.

  • Ensure Profile QP: Customer Sourced is set to Yes for custom sourcing attributes.

  • Please check Custom Sourcing Procedure is valid and attributes have been sourced correctly. Please refer to Pricing Implementation Guide on how to custom source attributes. See Using Custom Sourced Attributes section for a sample custom sourcing procedure implementation.
Is there any report that identifies errors in attribute mapping rules? Run the Attribute Mapping Rules Error Report and specify request type optionally. Review the report output for errors from attribute mapping rules. This report will show errors in mapping rules only for the attributes that have attribute mapping set to ATTRIBUTE MAPPING. For releases earlier than 11.5.9, see the diagnostic script provided in Attribute Management Diagnostic scripts section.
For troubleshooting Intercompany invoicing with Advanced Pricing issues, where can I find the document details? Please set QP: Pricing Transaction Entity and QP: Source System code correctly before creating pricelist/modifier so that the contexts/attributes linked to the Intercompany Transaction PTE will be available in LOVs in Pricing setup forms.
Build Attribute Mapping Rules concurrent program does not complete and generates User Defined Exception: 
Invalid HVOP Attribute:QP_BULK_PREQ_GRP.G_LINE_REC.INVOICE_TO_PARTY_ID 

Invalid HVOP Attribute:QP_BULK_PREQ_GRP.G_LINE_REC.INVOICE_TO_PARTY_ID 

Call AD_DDL to create BODY of packageQP_BUILD_SOURCING_PVT_TMP

ERROR in creating PACKAGE BODY: QP_BUILD_SOURCING_PVT_TMP

  • Please run Attribute Mapping Rules Error report and verify if there are any errors. This report verifies the packages used in sourcing rules are valid in database and if any file dependencies are missing.

  • Please verify the custom sourcing rules are valid and the parameters passed to the custom souring rules. Parameters should be valid columns and not the entire structure.
Problem in QP_Attr_Mapping_PUB.Build_Contexts API in Custom Contexts attached to the PTE. Please check if there are custom contexts attached to the PTE and verify the sourcing rules of the custom attributes are valid. For troubleshooting, please deselect the Attribute Mapping Enabled check box for the custom attributes in the custom contexts attached to the PTE and run Build Attribute Mapping Rules concurrent program. Verify if the issue still exists. See bug# 4709496 and 4887583 for more details.

Checklist for Building Attribute Mapping Rules

Sometimes the message Attribute Mapping Rule Generation is Successful may display, even though the Attribute Mapping box for that attribute remains deselected when it should be selected. This may occur when you create a new attribute, link it to a Pricing Transaction Entity successfully and then map it using the Build Attribute Mapping Rules from the Tools menu. To ensure a successful mapping, confirm the following:

QP: Build Attributes Mapping Options profile option

This profile option enables to set attribute mapping rules for attributes in both the active and inactive setups.

Values

Please set this profile value to Map attributes used in active pricing setup to map only those attributes used in the active pricing setup to improve the performance of Build Attribute Mapping Rules program.

Using Custom Sourced Attributes

Attributes can also be passed to the pricing engine directly, without the need for an attribute mapping rule. In such cases, the Attribute Manager API calls a custom API, QP_CUSTOM_SOURCE, where the user has manually defined the attributes being passed and coded the mapping of their values.

The user code is written in the package procedure QP_CUSTOM_SOURCE.Get_Custom_Attribute_Values. The Attribute Manager API program (Build_Contexts), calls this procedure to pick up custom-sourced attributes if the profile option QP_CUSTOM_SOURCED is set to yes. The input parameters to QP_CUSTOM_SOURCE are Request Type code and Pricing Type.

Typical values of Request Type Codes that can be passed are ONT, ASO, OKC, IC FTE, or MSD. By using the Request_Type_Code, you can control how the attributes are mapped based on the PTE of the calling application. The Pricing Type can be H (Header) or L (Line), which defines the level of the attribute mapping rule. These attributes and their values are passed to the pricing engine in the same manner as the attributes sourced through attribute mapping rules.

QP: Custom Sourced profile option

Profile option QP: Custom Sourced is Yes. Files:

Procedure Get_Custom_Attribute_Values

QPXCSOUS.pls: QP_CUSTOM_SOURCE package specification contains the following procedure declaration.

PROCEDURE Get_Custom_Attribute_Values
( p_req_type_code                    IN VARCHAR2
 , p_pricing_type_code             IN VARCHAR2
,  x_qual_ctxts_result_tbl         OUT QP_ATTR_MAPPING_PUB.CONTEXTS_RESULT_TBL_TYPE
,  x_price_ctxts_result_tbl        OUT QP_ATTR_MAPPING_PUB.CONTEXTS_RESULT_TBL_TYPE
);

Parameters:
p_req_type_code
     Request Type Code. ex, ONT.
p_pricing_type_code
     - 'L' for Line and 'H' for Header Level attribute mapping rule.
x_qual_ctxts_result_tbl
     - Qualifier attributes result table.
x_price_ctxts_result_tbl
     - Pricing attributes result table.

If profile option QP: Custom Sourced is set to Yes, Attribute Manager API Build_Contexts will call QP_CUSTOM_SOURCE.Get_Custom_Attribute_Values procedure to source custom mapped attributes (for example, attributes with Attribute Mapping Method=CUSTOM SOURCED).

Example code

The following example explains how you could the body of QP_CUSTOM_SOURCE for a particular case. In this case, two segment mapping columns, QUALIFIER_ATTRIBUTE31 and PRICING_ATTRIBUTE31 that belong to contexts CUST_SOURCE_QUAL_CON and CUST_SOURCE_PRIC_CON respectively and linked to PTE Order Fulfillment, will have Custom Sourced values as 10 for ORDER as well as LINE Attribute Mapping levels. You must ensure that the Attribute Mapping method for both these PTE-Attribute links is CUSTOM SOURCED in the Attribute Linking and Mapping setup window.

CREATE or REPLACE PACKAGE body QP_CUSTOM_SOURCE AS
/*Customizable Public Procedure*/

PROCEDURE Get_Custom_Attribute_Values
( p_req_type_code            IN    VARCHAR2
 ,p_pricing_type_code      IN    VARCHAR2
 ,x_qual_ctxts_result_tbl  OUT QP_ATTR_MAPPING_PUB.CONTEXTS_RESULT_TBL_TYPE
 ,x_price_ctxts_result_tbl OUT QP_ATTR_MAPPING_PUB.CONTEXTS_RESULT_TBL_TYPE
) is
Begin
   /* Note:
       a. Assign Context Code to context_name parameter and not the name of the context.
       b. Assign Column Mapped for the attribute to attribute_name parameter and not the
           attribute name.
       c. Ensure that attribute_value is assigned to NOT NULL value, otherwise the attribute will not
           get sourced and not used by pricing engine for calculation.
   */

-- The statements below help the user in turning debug on
-- The user needs to set the oe_debug_pub.G_DIR value.
-- This value can be found by executing the following statement
--     select value
--     from   v$parameter
--     where name like 'utl_file_dir%';
-- This might return multiple values , and any one of the values can be taken
-- Make sure that the value of the directory specified , actually exists

   -- Sample debug message.
   -- oe_debug_pub.add ('In Get_Custom_Attribute_Values');

   If p_req_type_code = 'ONT' and p_pricing_type_code in ('L','H') then   
     -- Sourcing qualifier attributes.
     x_qual_ctxts_result_tbl(1).context_name    := 'CUST_SOURCE_QUAL_CON';
     x_qual_ctxts_result_tbl(1).attribute_name  := 'QUALIFIER_ATTRIBUTE31';
     x_qual_ctxts_result_tbl(1).attribute_value := '10';
     
     -- Sourcing pricing attributes.
     x_price_ctxts_result_tbl(1).context_name   := 'CUST_SOURCE_PRIC_CON';
     x_price_ctxts_result_tbl(1).attribute_name := 'PRICING_ATTRIBUTE31';
     x_price_ctxts_result_tbl(1).attribute_value:= '10';   
   end if;
End Get_Custom_Attribute_Values;
END QP_CUSTOM_SOURCE;
/

Please note that, context_name is actually the context code and not the name of the context. Also, attribute_name is the column mapped for the attribute and not the attribute name.

Troubleshooting Custom sourcing attributes

Pricing Engine Request Viewer

The Pricing Engine Request Viewer window captures and displays the inputs and outputs of the pricing call. It captures the pricing engine call from any calling application, such as OM, iStore, Order Capture, or Oracle Contracts Core. The information displayed by the Pricing Engine Request Viewer enables you to diagnose which lines were selected or rejected by the pricing engine to determine why certain prices and adjustments were or were not applied. The Pricing Engine Request Viewer window displays the latest pricing request and updates the display information each time the pricing engine captures a new transaction. Previous pricing requests are saved in pricing tables. Using the Pricing Engine Request Viewer window, you can do the following:

Setting up the user profiles

Attribute Sourcing Issues in Pricing Engine Request Viewer window

Pricing Engine Request Viewer tables
Step # Table Name Description
1 QP_DEBUG_REQ This table contains information about the pricing requests.
2 QP_DEBUG_REQ_LINES This table contains details about the lines being priced.
3 QP_DEBUG_REQ_LDETS This table contains information about the line details being priced. It has adjustment and discount details for lines, including adjustments and discounts that the pricing engine eliminates.
4 QP_DEBUG_REQ_LINE_ATTRS This table contains information on the pricing attributes that the attribute mapping functionality passed to the pricing engine.
5 QP_DEBUG_REQ_RLTD_LINES This table contains information about the related lines.
6 QP_FORMULA_STEP_VALUES This table contains information about the formula step values that are inserted into the table QP_FORMULA_STEP_VALUES. The step values are inserted into the table during the evaluation of the formula attached to the price list or modifier.

Attribute Management Diagnostic scripts

1. Script: qp_attribute_mapping_detail.sql

Script to get attribute, context, attribute mapping and sourcing rules details to diagnose attribute sourcing related issues.

SELECT       A.PRC_CONTEXT_CODE, A.PRC_CONTEXT_TYPE, 
            A.SEEDED_FLAG SEEDED_CONTEXT, A.ENABLED_FLAG CONTEXT_ENABLED,
            B.SEGMENT_ID, B.SEGMENT_CODE CODE, B.USER_PRECEDENCE,
            B.SEEDED_FLAG SEEDED_ATTRIBUTE, B.SEGMENT_MAPPING_COLUMN COLUMN_MAPPED, 
            C.PTE_CODE, C.SEGMENT_LEVEL LEVEL_CODE, C.SEEDED_SOURCING_METHOD,
            C.USER_SOURCING_METHOD SOURCING_METHOD, C.USED_IN_SETUP USED_IN_SETUP,
            C.SOURCING_ENABLED SOURCING_ENABLED, C.LOV_ENABLED LOV_ENABLED,
            C.LIMITS_ENABLED LIMITS_ENABLED, C.SOURCING_STATUS SOURCE_STATUS,
            B.AVAILABILITY_IN_BASIC AVAIL_IN_BASIC
FROM    QP_PRC_CONTEXTS_B A, 
        QP_SEGMENTS_B B,
        QP_PTE_SEGMENTS C
WHERE   B.SEGMENT_CODE = &segment_code
AND     A.PRC_CONTEXT_ID = B.PRC_CONTEXT_ID
AND     C.SEGMENT_ID = B.SEGMENT_ID;

b.      Attribute Sourcing Rules details:

SELECT  D.REQUEST_TYPE_CODE, D.ATTRIBUTE_SOURCING_LEVEL, 
        D.SEEDED_SOURCING_TYPE, D.USER_SOURCING_TYPE, 
        D.SEEDED_VALUE_STRING, D.USER_VALUE_STRING,
        D.SEEDED_FLAG, D.ENABLED_FLAG
FROM    QP_SEGMENTS_B B,
        QP_ATTRIBUTE_SOURCING D
WHERE   B.SEGMENT_CODE = &segment_code
AND     D.SEGMENT_ID = B.SEGMENT_ID
ORDER BY REQUEST_TYPE_CODE, ATTRIBUTE_SOURCING_LEVEL;

2. Script to diagnose errors in attribute mapping rules setup earlier than release 11.5.9: qp_attr_mapping_error.sql

Script: qp_attr_mapping_error.sql

REM /* $Header: qp_attr_mapping_error.sql */

REM FILETYPE NOEXEC
REM dbdrv: none

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

CLEAR BUFFER;

SET ARRAYSIZE 4;
SET PAGESIZE 58;
SET TERM ON;
SET LINESIZE 145;
SET UNDERLINE =;
SET VERIFY OFF;
SET FEED OFF;
SET SERVEROUTPUT ON SIZE 100000;
SET FEEDBACK OFF;
SET HEADING OFF;

SPOOL qp_attr_mapping_error.lst



DECLARE

TYPE l_cursor_type IS REF CURSOR;
  
l_attribute_value               VARCHAR2(240);
l_attribute_mvalue      QP_Attr_Mapping_PUB.t_MultiRecord;
l_context_name          qp_prc_contexts_b.prc_context_code%TYPE;
l_attribute_name                qp_segments_b.segment_code%TYPE;
l_attribute_map         qp_segments_b.segment_mapping_column%TYPE;
l_src_type              qp_attribute_sourcing.user_sourcing_type%TYPE;
l_pricing_type          qp_attribute_sourcing.attribute_sourcing_level%TYPE;
l_value_string          qp_attribute_sourcing.user_value_string%TYPE;
l_request_type          qp_attribute_sourcing.request_type_code%TYPE;
l_pte_name              qp_pte_request_types_b.pte_code%TYPE;
l_context_type          qp_prc_contexts_b.prc_context_type%TYPE;
l_err_msg               VARCHAR2(2000);
l_err_count             BINARY_INTEGER := 0;
l_sql_stmt              VARCHAR2(2500);
l_cursor                        l_cursor_type;
l_request_type_code     qp_attribute_sourcing.request_type_code%TYPE := 'l_request_type_code';
l_successful            BOOLEAN;

BEGIN

IF l_request_type_code IS NULL THEN
      OPEN l_cursor FOR
        SELECT qpseg.segment_mapping_column,
          qpsour.user_sourcing_type src_type,
          qpsour.user_value_string value_string,
          qpcon.prc_context_code context_code,
          qpsour.attribute_sourcing_level,
          qpsour.request_type_code,
          qpreq.pte_code,
          qpcon.prc_context_type,
          qpseg.segment_code
        FROM
          qp_segments_b qpseg,
          qp_attribute_sourcing qpsour,
          qp_prc_contexts_b qpcon,
          qp_pte_request_types_b qpreq,
          qp_pte_segments qppseg
        WHERE
          qpsour.segment_id = qpseg.segment_id
          AND qppseg.user_sourcing_method = 'ATTRIBUTE MAPPING'
          AND qpseg.prc_context_id = qpcon.prc_context_id
          AND qpreq.request_type_code = qpsour.request_type_code
          AND qppseg.pte_code = qpreq.pte_code
          AND qppseg.segment_id = qpsour.segment_id
          AND qppseg.sourcing_enabled = 'Y'
          AND qpcon.prc_context_type in ('PRICING_ATTRIBUTE', 'PRODUCT','QUALIFIER');
    ELSE
      OPEN l_cursor FOR
        SELECT qpseg.segment_mapping_column,
          qpsour.user_sourcing_type src_type,
          qpsour.user_value_string value_string,
          qpcon.prc_context_code context_code,
          qpsour.attribute_sourcing_level,
          qpsour.request_type_code,
          qpreq.pte_code,
          qpcon.prc_context_type,
          qpseg.segment_code
        FROM
          qp_segments_b qpseg,
          qp_attribute_sourcing qpsour,
          qp_prc_contexts_b qpcon,
          qp_pte_request_types_b qpreq,
          qp_pte_segments qppseg
        WHERE
          qpsour.segment_id = qpseg.segment_id
          AND qppseg.user_sourcing_method = 'ATTRIBUTE MAPPING'
          AND qpsour.request_type_code = l_request_type_code
          AND qpseg.prc_context_id = qpcon.prc_context_id
          AND qpreq.request_type_code = qpsour.request_type_code
          AND qppseg.pte_code = qpreq.pte_code
          AND qppseg.segment_id = qpsour.segment_id
          AND qppseg.sourcing_enabled = 'Y'
          AND qpcon.prc_context_type in ('PRICING_ATTRIBUTE', 'PRODUCT','QUALIFIER');
    END IF;

    LOOP
      FETCH l_cursor INTO
        l_attribute_map,
        l_src_type,
        l_value_string,
        l_context_name,
        l_pricing_type,
        l_request_type,
        l_pte_name,
        l_context_type,
        l_attribute_name;
      
      EXIT WHEN l_cursor%NOTFOUND;    

      l_successful := TRUE; 
      IF l_src_type = 'API' THEN
        BEGIN
          l_sql_stmt := 'BEGIN QP_Attr_Mapping_PUB.G_Temp_Value := ' || l_value_string || ';' || ' END;';
          EXECUTE IMMEDIATE l_sql_stmt;
          l_attribute_value := QP_Attr_Mapping_PUB.G_Temp_Value;
        EXCEPTION
          WHEN VALUE_ERROR THEN
            NULL;
          WHEN NO_DATA_FOUND THEN
            NULL;
          WHEN OTHERS THEN
            l_err_msg := SQLERRM;
            l_successful := FALSE; 
        END;
      ELSIF l_src_type = 'API_MULTIREC' THEN
        BEGIN
          l_sql_stmt := 'BEGIN QP_Attr_Mapping_PUB.G_Temp_MultiValue := ' || l_value_string || ';'
                                 || ' END;';
          EXECUTE IMMEDIATE l_sql_stmt;
          l_attribute_mvalue := QP_Attr_Mapping_PUB.G_Temp_MultiValue;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            NULL;
          WHEN OTHERS THEN
            l_err_msg := SQLERRM;
            l_successful := FALSE;
        END;
      ELSIF l_src_type = 'PROFILE_OPTION' THEN
        BEGIN
          l_attribute_value := fnd_profile.value(l_value_string);
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            NULL;
          WHEN OTHERS THEN
            l_err_msg := SQLERRM;
            l_successful := FALSE;
        END; 
      ELSIF l_src_type = 'SYSTEM' THEN
        BEGIN
          l_sql_stmt := 'SELECT ' ||l_value_string|| ' FROM DUAL';
          EXECUTE IMMEDIATE l_sql_stmt INTO l_attribute_value;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            NULL;
          WHEN OTHERS THEN
            l_err_msg := SQLERRM;
            l_successful := FALSE;
        END;
      ELSIF l_src_type = 'CONSTANT' THEN
        l_attribute_value := l_value_string;
      ELSE
        dbms_output.put_line('Invalid source type: '||l_src_type);
      END IF;

      IF l_successful = FALSE THEN
        IF l_err_count = 0 THEN
          DBMS_OUTPUT.PUT_LINE('ATTRIBUTE MAPPING RULES ERRORS FOUND');
          DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
        END IF;        

        DBMS_OUTPUT.PUT_LINE('PTE:           ' || l_pte_name);
        DBMS_OUTPUT.PUT_LINE('Context Type:  ' || l_context_type);
        DBMS_OUTPUT.PUT_LINE('Context:       ' || l_context_name);
        DBMS_OUTPUT.PUT_LINE('Attribute:     ' || l_attribute_name || ' (' || l_attribute_map || ')');
        DBMS_OUTPUT.PUT_LINE('Request Type:  ' || l_request_type);
        DBMS_OUTPUT.PUT_LINE('Level:         ' || l_pricing_type);
        DBMS_OUTPUT.PUT_LINE('Source Type:   ' || l_src_type);
        DBMS_OUTPUT.PUT_LINE('Sourcing Rule: ' || l_value_string);
        DBMS_OUTPUT.PUT_LINE('Error Message: ' || l_err_msg);
        DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
        l_err_count := l_err_count + 1;
      END IF;
    END LOOP;
    CLOSE l_cursor;
  
    IF l_err_count = 0 THEN
      DBMS_OUTPUT.PUT_LINE('NO ATTRIBUTE MAPPING RULES ERROR(S) FOUND');
    ELSE
      DBMS_OUTPUT.PUT_LINE(l_err_count || ' ATTRIBUTE MAPPING RULES ERROR(S) FOUND');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('*** ERROR IN RUNNING THE SCRIPT ***');
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      DBMS_OUTPUT.PUT_LINE('*******************************************************');
END;
/
SPOOL OFF;