Reports and Concurrent Programs

This chapter covers the following topics:

• Overview of Reports and Concurrent Programs
• Accrual Details Report
• Attribute Mapping Rules Error Report
• Build Attribute Mapping Rules Program
• Build Formula Package Program
• Cross Order Volume Load
• Cross Order Volume Report
• Modifier Details Report
• Price Lists Report
• Pricing Formulas Report
• Purge Pricing Engine Requests Program
• QP: Bulk Import of Price List Program
• QP: Maintains the Denormalized Data in QP Qualifiers
• QP: Pattern Upgrade
• QP: Security Control with Views Conversion
• Qualifier Grouping report
• Update Price Lists with Multicurrency Conversion Criteria
• Update Promotional Limit Balances program

Overview of Reports and Concurrent Programs

Oracle Advanced Pricing provides reports and concurrent programs that enable you to administer, manage, and review your pricing operations and data to:

• Determine what pricing information has been established (for example, active price lists, formulas and qualifiers).

• Communicate pricing information internally and externally (for example, publishing price lists to internal and external users).

• Take action based on order or pricing activity (for example, issuing a credit memo based on reported accruals).

  Note: When Pricing Security is ON, security privileges determine which pricing entities you can view from the list of values. View or Maintain privileges determine the actions that you can do in various windows.

Accrual Details Report

The Accrual Details report enables you to run a report to view modifiers that have been set up for accruals or those with associated Coupon Issue information. Using this report, you can quickly see which coupons have been issued and the coupon numbers generated for a modifier.

Report Submission

In the Submit Request window, Name field, select Accrual Details.

Report Parameters

Accrual Details Report

• Modifier Name From/To: Select a range of modifier names to narrow the report to the names that fall between the specified range. For example, if you specified modifier names A to F, modifier names G and H will not be included in the report.

• Start/End Date Active: Select the start and end date range of the modifiers to be included in the report.

• Qualifier Context: Select a qualifier context to narrow the report to only those modifiers with the selected qualifier context.

• Qualifier Attribute Value From/To: Select specific qualifier attribute to further define the selected qualifier context.

Attribute Mapping Rules Error Report

This program provides status and error messages to advise you about the following:

• Invalid attribute mapping rules.

• Warnings if the program completes with Attribute Mapping errors.

  • Success: Program completes without Attribute Mapping errors.

  • Warning: Program completes with one or more Attribute Mapping errors.

  • Error: Program cannot complete due to some exception/runtime error.

    The report displays an entry in the file log for every attribute mapping rule that is invalid. The entry includes details, such as the context, attribute, pricing type, request type, attribute mapping rule, and error message produced by the invalid mapping.

You can run the report for a single Request Type, such as Order Management (ONT) or all request types. The number of errors appears on the log after the report is run.

Report Submission

In the Submit Request window, Name field, select Attribute Mapping Rules Error Report.

Report Parameters

Attribute Mapping Rules Error Report Parameters

Request Type: Select a single Request Type or leave it blank to run report for all request types.

Build Attribute Mapping Rules Program

This program generates entries into the BUILD_CONTEXTS application program interface (API). You pre-generate entries into this API so that the pricing engine evaluates only the qualifier and pricing attributes used in the price lists, modifiers, and formulas that are relevant to each call rather than all of the attribute mapping rules.

Note: The Build Attribute Mapping Rules program sources the attributes that are used only in the active pricing setup if the profile value QP: Build Attributes Mapping Options is set to Yes. If the profile value QP: Build Attributes Mapping Options is set to No, this program sources the attributes that are used in both the active and inactive setup. The default value of the profile QP: Build Attributes Mapping Options is No; you can update it only at the site level.

Recovery Process

If the process fails:

• The QP_BUILD_SOURCING_PVT package becomes invalid.

• All subsequent pricing calls fail.

To recover,

1. Check the concurrent program log to view any errors. If needed, log in to SQL/PLUS and check the errors associated with compiling the QP_BUILD_SOURCING_PVT body. Problems may include:

   • You reference an API in an attribute mapping rule and the API is invalid or does not exist.

   • You have a typographical error in an attribute mapping rule that refers to an API or global structure.

2. Fix the attribute mapping rule in the Attribute Mapping window.

3. Rerun this concurrent process.

Report Submission

Run this program after you:

• Make a change to the mapping rule for a specific qualifier attribute. For example, run this process after you map the Geography context to a qualifier attribute.

• First use a qualifier in either a modifier or a price list. For example, run this process after the first time that you use Customer Class as a qualifier for a price list or modifier.

In the Submit Request window, Name field, select Build Attribute Mapping Rules.

Run the process during a time when most users are not logged on to the system. You may encounter a record locking problem if this process runs at the same time that the pricing engine is processing calls.

Report Parameters

None

Build Formula Package Program

To generate the formula package, you should run the Build Formula Package concurrent program every time that you set up a new formula expression. Every time a new formula expression is created or updated, a Warning dialog box displays a message to run the Build Formula Package concurrent program.

This concurrent program builds the Formula Package containing statements that use bind variables for evaluating the formula value. This generated package is called from the main engine formula package at run time to calculate the formula value.

Note: If the Formula expression already exists in the current generated formula package, the Warning box does not appear.

Report Submission

In the Submit Request window, Name field, select Build Formula Package.

Report Parameters

None

Cross Order Volume Load

Cross order volume amount totals are based on the list price (not selling price) of the order lines. Run the Cross Order Volume Load program to populate the cross order volume tables. Then you can run the Cross Order Volume report to view the information from the cross order volume tables. The Cross Order Volume Load program uses the following criteria when computing cross order volume attributes:

• Accumulates orders for items in the Cross Order Item Amount/Quantity Summary that have modifiers set up with cross order volume attributes, such as Period 1 Amount, Period 1 Item Quantity, Period 1 Item Amount. Similarly, the pricing engine gives total Cross Order summaries only if a cross order qualifier has been used in a modifier. To ensure that all item totals are accumulated and loaded into the cross order tables, run the Cross Order Volume Load concurrent request after setting up any modifiers using either:

  • a cross order amount qualifier that has not been used previously or

  • a cross order item amount/quantity pricing attribute for an item that has not been used previously

• Has a total based on list price (not selling price).

• Considers orders and order lines only in Booked status.

• Includes recurring charges. (Order lines that have a charge periodicity attribute associated such as Monthly, Yearly are recurring charges.)

• Does not support across items in an item category.

  Note: Cross order item amount and cross order item quantity only accumulates order line totals per item, not for order lines across items in an item category.

Example of Cross Order Volume Period Processing

The cross order volume profile options define which order lines to include in the cross order volumes. For example, consider the following cross order volume period setups:

• Profile option QP: Cross Order Volume Period 1: 30

• Profile option QP: Cross Order Volume Period 2: 60

• Profile option QP: Cross Order Volume Period 3: 90

If you set the profile options as described, then run the Cross Order Volume Load program with an effective date of December 31, 2005, the pricing engine considers orders lines with request dates from the following periods:

• Cross order period 1: Order lines with a request date between 1-December and 31-December-2005

• Cross order period 2: Order lines with a request date between 1-November and 31-December-2005

• Cross order period 3: Order lines with a request date between 2-October and 31-December-2005

Report Submission

In the Submit Request window, Name field, select Cross Order Volume Load.

Report Parameters

Cross Order Volume Report Parameters

• Operating Unit: Enter either a single operating unit or leave this field blank for all operating units.

• Effective Date: Provide the load effective date; otherwise, the program uses the current date.

Cross Order Volume Report

Run the Cross Order Volume report to view the information from the cross order volume tables. The report displays the total order amount, total item amounts, and total item quantities for each customer and period. The report displays the cross order quantity values (for example, Period 1 Item Quantity) rounded to the nearest whole unit. Cross order amount values (for example, Period 1 Amount and; Period 1 Item Amount) are rounded to two decimal places.

Note: The Cross Order Volume report displays only totals for the operating units to which the responsibility has access. If you have implemented Multi-Org Access Control, data reported is for the list of operating units as restricted by MO: Security Profile.

Report Submission

In the Submit Request window, Name field, select Cross Order Volume Report.

Report Parameters

None

Related Topics

Cross Order Volume Load

Modifier Details Report

This report displays modifier information.

Report Submission

In the Submit Request window, Name field, select Modifier Details.

Report Parameters

Modifier Details Report Parameters

• Modifier Number From/To: Enter the range of modifier numbers for which you want the report generated.

• Modifier Name From/To: Enter the range of modifier names.

• Modifier Version From/To: Enter the range of modifier versions.

• Start Date Active/End Date Active: Enter the range of dates for the active modifiers.

• Active Modifier Only: Select Yes to display only active modifier lists and No to display all modifier lists.

• Active Modifier Lines Only: Select Yes to display only active modifier lines or No to display all modifier lines.

• Item Name: Select item name criteria to display modifiers for the item names that you specify.

• Item Category: Select item category criteria to display modifiers for the item categories listed.

• Customer Name: Select customer name criteria to display modifiers associated with the specified customer names.

• Customer Class: Select customer class criteria to display modifiers assigned to the specified customer classes.

• Price List: Select the price list criteria to display modifiers associated with the specified price list.

  Note: If pricing security is enabled, the report includes only modifiers for which you have View or Maintain access privileges.

Price Lists Report

This report displays the following price list information:

• Report by price list name.

• Options to print report for active and inactive price lists based on effectivity date. If multicurrency is enabled, the report prints other currency setup if the price list is multicurrency enabled.

• Display all other header information. If price list is multicurrency enabled, report includes header information from the associated multicurrency conversion list.

• Qualifiers attached to price list.

• Secondary price lists attached to price list.

• Detail of list lines attached to price list.

• Detail of item (for example, inventory item, service item).

• Pricing attributes for item.

• Price breaks for item.

• Price formulas attached to price list line.

Report Submission

In the Submit Request window, Name field, select Price Lists.

Report Parameters

Price List Report Parameters

• Price List From/Price List To: Enter a price list or a range of price lists.

• Start Date Active/End Date Active: Enter a date or a range of dates.

• Active Flag: Select Yes to display active price lists only, or select No to display all price lists. The default value is Yes.

• To Currency Code: Select one Currency To multi-currency conversion list setup information or None.

  If the multi-currency feature is enabled for a price lists, the names of the attached Currency Conversion Criteria, Base Markup Operator, Base Markup Value, and Base Markup Formula are returned.

Note: If pricing security is enabled, only price lists for which you have View or Maintain access privileges are included in the report.

Pricing Formulas Report

This report displays pricing formulas and their details, including factor lists.

Report Submission

In the Submit Request window, Name field, select Pricing Formulas.

Report Parameters

Pricing Formulas Report Parameters

• Formula From/To: Enter an individual formula or range of formulas.

• Price List From/To: Enter an individual price list or a range of price lists.

• Start Date Active/End Date Active: Enter an individual date or a range of dates.

Purge Pricing Engine Requests Program

This program purges all pricing engine requests or selected requests. Run this program on a regular basis to purge the historical data from the pricing debug tables. This improves the performance of the Pricing Engine Request Viewer window.

Report Submission

In the Submit Request window, Name field, select Purge Pricing Engine Requests.

Report Parameters

Purge Pricing Engine Requests Parameters

This program has two input parameters:

• Keep Last (N) Days of Requests: The pricing engine purges requests older than the number of days entered. For example, enter 30 to keep requests created in the last 30 days. Requests older than 30 days are purged.

• Request Name: Enter a Request Name to delete the pricing engine requests that fully or partially match the name that you enter.

Recovery

None

QP: Bulk Import of Price List Program

The Pricing Data Bulk Loader API is implemented as the concurrent program QP: Bulk Import of Price List. Using the concurrent program, you define the pricing data to be imported (bulk loaded), then run the concurrent program to import the price list data from interface tables into the Oracle Advanced Pricing tables. To improve processing efficiency, you can schedule the concurrent program to run at optimal times, such as when no active users are on the system.

The QP: Bulk Import of Price List program enables you to complete the following tasks:

• Do bulk validations and bulk loading.

• Provide all validation error messages in the same run for records that were not uploaded rather than returning errors one after another.

• Provide the ability to multi-thread the interface loader similar to Order Import with the ability to multi-thread within the same price list.

You can set the profile option QP: Batch size for Bulk Upload (QP_BATCH_SIZE_FOR_BULK_UPLOAD) to restrict the number of records that are read to the PL/SQL tables. This helps to improve the bulk import performance. The default value for the profile is 1,000, but you can change this value to suit your hardware configuration. For more information, see the Advanced Pricing Implementation Guide, Profile Options.

Scenarios for Using the QP: Bulk Import of Price List Program

Your business practices may require that you upload price list data on a regular basis, such as in the following scenarios:

• When a new price list must be created by uploading a price change file.

• When new products lines are created, new offerings to new channels, acquisitions of product lines or companies, new product rollout, and when new price lists are created.

• When you are implementing a new sales territory, to bulk load existing price lists or to upload pricing from regions that may have their pricing on spreadsheets.

Processes performed by the QP: Bulk Import of Price List Program

The QP: Bulk Import of Price List program completes the following processes:

• Creates and updates price list headers, lines, pricing attributes, qualifiers, and breaks using the interface tables.

• Deletes price list lines, pricing attributes, qualifiers, and breaks using the interface tables.

• (Optionally) Uses the Process Parent parameter in the concurrent program to load list lines even if the pricing attribute validation fails. If the Option is NO, and a pricing attribute fails, both parent and child lines are not uploaded.

• Stores the interface program ID in the main pricing tables so that you know which batch ID updated the records.

  This program updates only the IDs that are derived from Value To ID conversions back to the interface tables. If a record generates errors, the record remains in the interface tables where you can view the IDs.

• Marks a record as being processed so that multiple processes do not pick up the same record.

• Automatically deletes the successfully uploaded rows from the interface tables.

• Processes a specific set rather than the entire eligible data.

Note: For more information on populating the interface tables for bulk loader, see Oracle Advanced Pricing Implementation Guide.

Considerations for Running the QP: Bulk Import of Price List Program

Review the following considerations before running the QP: Bulk Import of Price List program:

• Source System/Pricing Transaction Entity (PTE): The concurrent program retrieves records only when the source system code and the PTE code match with the same profile values or null.

• Price lists that are attached to the following entities are not retrieved for bulk loader processing:

  • Sales Agreements (list source code of BSO)

  • Service Contracts (list source code of OKS)

  • Pricing Agreements (list type code of AGR)

• Importance of Log and Output file: The request output contains the following information:

  • Number of records processed

  • Number of successful records

  • Error messages (if applicable)

  The concurrent request log file provides the processing details. The log file also lists debug messages if Debug On parameter was set to Yes. If you enter an entity name that is not unique for the eligible records, none are selected for processing.

• Child lines, parents, and grandparent records must be populated to insert, update, and delete records: To insert, update, or delete records, ensure that the corresponding parent and grandparent records (if applicable) are also in the interface table so they are eligible for retrieval by the concurrent program.

• Errors before resubmit: For records with errors in the interface tables, set the request_id and process status flag columns to NULL so that these records are selected in the next run. When the Entity Name is specified as a Report parameter, you do not need to reset these values.

• Data for error messages in a QP interface table: The table QP_INTERFACE_ERRORS is populated with the processing error messages that are then written to the output file of the concurrent request. Occasionally, you must purge this table of this data.

Report Submission

In the Submit Request window, Name field, select QP: Bulk Import of Price List.

Report Parameters

QP: Bulk Import of Price List Parameters

• Entity: Indicates which pricing entity is to be processed. The default value is PRL (price list).

• Entity Name: This is an optional parameter. Enter the name of the price list to be processed. If this field is left blank, the system processes all entities of the selected entity type that are available for processing in the interface tables.

  If you enter a price list name, that price list is considered for import even if it failed in a previous import and regardless of the other flags (for example, even if PROCESS_ STATUS_FLAG not equal to P, or PROCESS_FLAG not equal to Y or REQUEST_ ID not equal to NULL in the QP_INTERFACE_LIST_HEADER record).

• Process ID: This value corresponds to the process_id in the interface tables. The Process ID groups data in the interface table. You can select criteria to specify which records to process.

  If this field is left blank, all available records for processing are considered subject to the restrictions entered in the Entity Name field.

• Process Type: This field corresponds to the process type that is listed in the interface tables such as XML. If specified, the bulk loader processes only the records with PROCESS_TYPE column matching the value entered here.

• Process Parent: Select Yes (the default) or No to determine if the loader should process a price list line when validation for a child pricing attribute fails. Valid values are Yes (the default) and No.

• No of Threads: Enter a number that indicates the total number of child processes or threads to achieve multithreading for list lines and their child records. The default value is 1.

  Multithreading occurs when the child concurrent processes are initiated after a user submits the Process Parent request. The parent process first processes the list header records and then the qualifiers records before spawning child processes. The child processes upload price list lines and attributes.

  Note: Multithreading can impact the length of processing time that is required; therefore, you should not exceed the number of Central Processing Units (CPUs) that are available when running the program. Ideally, you should schedule this process for a slow processing period or when few users are on the system.

• Turn Debug On: Select Yes or No (default):

  • Select No to improve loading performance; however error messages are not sent to the Debug log.

  • Select Yes to have all error messages sent to the Debug Log.

    The Debug On parameter controls only the log files. However, error messages, number of records processed, and other related information is written to the output file, regardless of the value set for Debug On.

• Enable Duplicate Line Check: The bulk loader can upload thousands of lines into the pricing (QP) lines table. If Enable Duplicate Line is set to Yes, then the QP: Bulk Import of Price List checks for duplicates lines and displays an error message if duplicate lines are found. If it is set to No, then the duplicate check is not done. The default value is Yes.

QP: Maintains the Denormalized Data in QP Qualifiers

This program populates the denormalized column data in the pricing tables and fixes data corruption specific to the denormalized columns that occur from a patch application or other set-up issues. You can run this program on a periodic basis or if the pricing engine performance decreases.

This program calculates and populates modifier statistics that the pricing engine search optimizer uses to find modifiers by the most direct method. For each modifier at the list and line level, the process calculates information related to its:

• Qualifiers: Indicates if the list or line has qualifiers.

• Directness: Indicates how specific (selective) or non-specific (non-selective) its qualifiers are. More specific qualifiers are more direct.

• Fastest search path: The qualifiers in each modifier list and line that are the most direct become primary qualifiers; the remainder become secondary qualifiers. The pricing engine search optimizer searches the primary qualifiers first.

This program automatically runs after you add or change each modifier and recalculates some statistics for that modifier. In addition, you should run the program periodically to recalculate all of the statistics for all the modifiers.

Note: The Request log for an automatic run is not saved and cannot be viewed in My Requests if the QP: Maintains the Denormalized Data in QP Qualifiers program runs successfully. The log file is saved; you can view the file only when an exception occurs.

Report Submission

In the Submit Request window, Name field, select QP: Maintains the Denormalized Data in QP Qualifiers.

Run the program during a time when most users are not logged on to the system. You may encounter a record locking problem if this program runs at the same time that the pricing engine is processing calls.

Report Parameters

QP: Maintains the Denormalized Data in QP Qualifiers parameters

• List Header ID (Low/High): Enter the range of values for the modifier lists. The two parameters can be the same value but neither can be null.

• Update Type: Select the update type such as All, Denormalized Columns, Factor Pricing Attributes, List Header Phases, Pricing Phases, or Qualification Indicator.

Recovery

None

QP: Pattern Upgrade

Use this program to upgrade the existing pricing setup to patterns. You must run this concurrent program with empty criteria to upgrade all the existing setup to pattern and to avoid any discrepancies during pattern creation.

Note: It is not advisable to change between pattern and non-pattern search algorithm frequently as pattern creation will not happen properly. This would lead to non-desired results of pattern search.

Report Submission

In the Submit Request window, Name field, select QP: Pattern Upgrade.

Report Parameters

None

QP: Security Control with Views Conversion

This program enables you to turn on pricing security for your entire installation. Before setting to the program to ON, ensure that you have completed the pricing security setup steps outlined in the Oracle Advanced Pricing Implementation Manual.

Note: QP: Security Control (read-only): This profile option displays the current setting of the security option for your entire installation (either on or off). You cannot directly update this profile option value - only the concurrent program QP: Security Control with Views Conversion can turn pricing security on or off.

Report Submission

In the Submit Request window, Name field, select QP: Security Control with Views Conversion.

Report Parameters

Security Control: Select ON or OFF.

• OFF (default): Once security is turned ON, it should not be turned OFF.

• ON: To turn security ON for your installation. If you are upgrading or freshly installing the security feature for the first time, ensure that you have completed the setup and implementation steps described in Oracle Advanced Pricing Implementation Manual, Pricing Security.

Qualifier Grouping report

This report shows details for qualifier groups including grouping conditions and effective dates within the group.

Report Submission

In the Submit Request window, Name field, select Qualifier Grouping.

Report Parameters

Qualifier Grouping report parameters

• Qualifier Context: Enter a qualifier context.

• Qualifier Attribute Value From and Qualifier Attribute Value To: Enter an individual qualifier attribute value or a range of qualifier attribute values.

• Start Date Active and End Date Active: Enter a date or a range of dates.

Update Price Lists with Multicurrency Conversion Criteria

This concurrent program enables you to maintain and use price lists in different currencies. Complete the following prerequisites before running this program:

1. Set the profile option QP: Multi-Currency-Installed to Yes (Y). The valid values for the profile are Yes (Y) or No (N). The default value is No.

2. Run the concurrent program Update Price Lists with Multi-Currency Conversion Criteria only once.

Running this program does the following:

• Adds a new Multi-currency Conversion field to the Advanced Pricing - Price Lists and Pricing Agreements windows. This field displays the name of the multicurrency list currently attached to the price list but you can select other multicurrency lists from this field; however, you can select only one at a time.

• Generates a default multicurrency conversion list based on the Base Currency, Currency-To, and Rounding Factor values from existing price lists. This default multicurrency conversion list does not have other currencies or conversion criteria, only the base currency from the base price list. You must add currencies to the newly created multicurrency lists or create new multicurrency conversion lists if you want to include different currencies. You can then attach the multicurrency conversion list to the respective price list.

The following example shows the setup for five price lists before conversion and the multicurrency list that is created after the conversion:

Currency	Rounding
USD	-2
USD	-2
USD	-3
FRF	-1
CAD	NULL

The concurrent program creates four currency conversion criteria based on the price list currency and rounding factor of the price lists.

Currency	Currency-To	Rounding
USD	USD	-2
USD	USD	-3
FRF	FRF	-1
CAD	CAD	NULL

Attach the multicurrency conversion list to the respective price list. You can then attach the multicurrency conversion list to the respective price list. You must add new currencies to the existing multicurrency lists or create new multicurrency lists with the currencies required for conversion.

Note: Attaching a multicurrency list to the respective price list is a mandatory step - without it the pricing engine cannot use the current price lists as multicurrency price lists. After you run the concurrent program, you can create additional multicurrency lists or add existing currencies to the created multicurrency lists.

Report Submission

In the Submit Request window, Name field, select Update Price Lists with Multi-Currency Conversion Criteria.

Report Parameters

Update Price Lists with Multicurrency Conversion Criteria

Do you want to enable Multi-Currency price lists?: Select Yes to enable multicurrency price lists.

Note: The program must be run initially only once to activate the multicurrency feature; otherwise, data corruption may result. After you have successfully run the concurrent program, all existing price list or agreement windows will be converted to multicurrency price lists. You should not return to non multicurrency price lists. Changing the profile back to N (no) may cause undesired results if conversion criteria have been used. Oracle does not support changing the setting back to No.

Recovery

None. Once you have successfully run the concurrent program , do not return to non-multicurrency price lists. All existing price list windows are converted to multicurrency price lists. Changing the profile option QP: Multi-Currency Installed back to N (no) may cause undesired results if conversion criteria have been used.

Update Promotional Limit Balances program

Use the Update Promotional Limit Balances program to update promotional limit balances for selected modifier lines.

Report Submission

In the Submit Request window, Name field, select Update Promotional Limit Balances.

Report Parameters

Update Promotional Limit Balances

• Modifier Number: To update limits for a selected modifier, enter the modifier number.

• Modifier Line Number: To update limits for a selected modifier line, enter the modifier line number.

• Limit Number: Enter a limit number.

• Limit balance Identifier: Enter the limit balance identifier.

Related Topics

Updating an Existing Limit Balance