1. Administering Pricing
  2. Guidelines for Importing Batches of Discount Lists

Guidelines for Importing Batches of Discount Lists

Save time and work more efficiently. Import a batch of discount lists instead importing them individually.

Assume you maintain pricing details in a source system that resides outside of Oracle Pricing. You need to periodically import discount lists through data files into Pricing. You prefer to use an automated process that accepts your pricing data files, validates them, then stores your data in Oracle Pricing.

You can.

  • Manage data for more than one discount list in a single batch.

  • Reuse your batch during a subsequent import.

  • Import entities for more than one discount list.

    • Headers and access sets

    • Items

    • Pricing terms for simple rules

    • Pricing terms, matrix dimensions, and matrix rules for attributes

    • Descriptive flexfields

  • Use output files and error logs to examine details about the number of records imported, records that are in error, and to get suggestions on how to fix errors.

Use an Established Import Procedure

Here's a summary of how you import.

summary of how you import

Assume you import a discount list named DISCOUNT_LIST_1.

  1. Copy discount list data from your source system into the DiscountListImportTemplate.xlsm Excel workbook.

  2. Click Generate CSV File in DiscountListImportTemplate.xlsm to create a zip file.

  3. Run the Load Interface File for Import scheduled process.

    Each worksheet in DiscountListImportTemplate represents an interface table. For example, the QP_DISCOUNT_LISTS_INT sheet uses the same structure that the QP_DISCOUNT_LISTS_INT interface table on the Oracle server uses. The Load Interface File for Import process imports data from your zip file into the interface tables.

  4. Run the Import Discount Lists scheduled process. This parent process creates two child scheduled processes.

    • Import Discount List Headers and Access Sets

    • Import Discount List Items and Rules

    They import your pricing data from the interface tables into the Oracle database.

  5. Go to the Pricing Administration work area and verify the import.

    The work area displays pricing data from the Oracle database. DISCOUNT_LIST_1 includes any child objects that you imported, such as items, pricing terms, and pricing matrixes.

Interface Tables That the Discount List Import Template Uses

  • QP_DISCOUNT_LISTS_INT

  • QP_DISCOUNT_LIST_SETS_INT

  • QP_DISCOUNT_LIST_ITEMS_INT

  • QP_PRICING_TERMS_INT

  • QP_MATRIX_DIMENSIONS_INT

  • QP_MATRIX_RULES_INT

where

  • QP is a code for pricing.

  • INT is an abbreviation for the word interface.

Add Discount Lists

Add Discount Lists

Note

  • Use the DiscountListImportTemplate.xlsm template.

  • Use the QP_DISCOUNT_LISTS_INT worksheet.

  • Read the name and description to clarify the data you must add.

  • Add one row for each discount list.

  • An asterisk ( * ) in the worksheet means you must include a value in the column. For example, you must include a value in the BATCH_NAME column for each list you import.

  • A double asterisk ( ** ) indicates a set of required columns. You must include a value for at least one column in each set. For example, the template uses double asterisks for BUSINESS_UNIT_ID and BUSINESS_UNIT_NAME on sheet QP_DISCOUNT_LISTS_INT to indicate they constitute one set. You must include a value for at least one of these columns.

  • Set the values.

    Attribute

    Value

    Batch Name

    Enter any alphanumeric value. The import uses the value that you enter as a unique identifier for the data that you import.

    You select this batch name when you run the Import Discount Lists scheduled process.

    You can reuse a batch over and over.

    Operation Code

    Set a value.

    • CREATE. Create a new record for your discount list.

    • UPDATE. Update a discount list that already exists.

    • NO-OP. It means NO OPeration, or don't do any operation. Use it to create a relationship with another worksheet.

    • For example, assume you have an existing price list and charges for that list. If you want to update only the charges, use No-Op for the price list, and use Update for the charges.

    • Use NO-OP on the discount list header and CREATE for an entity in the list. For example, use NO-OP on the QP_DISCOUNT_LISTS_INT worksheet for discount list 1001, and use CREATE for items on the QP_DISCOUNT_LIST_ITEMS_INT worksheet for discount list 1001.

    • You use the same set of operation codes on each worksheet.

    SOURCE_DISCOUNT_LIST_ID

    Enter any numeric value.

    Make sure the value is unique across worksheets.

    NAME

    DESCRIPTION

    Enter any alphanumeric value.

    The Pricing Administration work area will display these values.

    BUSINESS_UNIT_ID

    Enter the Id for the business unit you want to use for the discount list.

    For example, here's how you get the Id for the Vision Operations business unit.

    1. In the Setup and Maintenance work area, go to the task.

      • Offering: Order Management

      • Functional Area: Organization Structures

      • Task: Manage Business Unit Set Assignment

    2. In the dialog, select Manage Business Unit Set Assignment, set Business Unit to Select and Add, then click Apply and Go to Task.

    3. On the Manage Business Units page, search for Vision Operations.

    4. Click View > Columns, then add a check mark to BusinessUnitId.

    5. Notice the value in the BusinessUnitId column. Its 204 for Vision Operations.

    BUSINESS_UNIT_NAME

    Enter the name of the business unit.

    CURRENCY_CODE

    Here's how you get the currency code.

    1. Go to the Setup and Maintenance work area, search for, then open the Manage Currencies task.

    2. On the Manage Currencies page, leave the search attributes empty, then click Search.

    3. Examine the search results. Each row contains the Currency Name, such as US Dollar, and the Currency Code, such as USD.

      If necessary, use View > Columns to display the columns you need.

    START_DATE

    Enter a date for each of your discount lists.

    yyyy/mm/dd hh:mm:ss

    For example:

    2019/01/15 10:15:20

    where

    • 2019 is the year

    • 01 is the month

    • 15 is the day

    • 10 is the hour, using a 24 hour clock. For example, 10 means 10 AM. 23 means 11 PM.

    • 15 is the minutes

    • 20 is the seconds

    STATUS_CODE

    You must enter APPROVED or IN_PROGRESS.

    If you import with APPROVED, then make sure the access set you import exists in Oracle Financials and its assigned to a business unit.

    ATTRIBUTE_CATEGORY

    ATTRIBUTE_CHARx

    ATTRIBUTE_NUMBERx

    ATTRIBUTE_DATEx

    ATTRIBUTE_TIMESTAMPx

    Use these columns only if you use a flexfield with your discount list.

Create Relationships Between Entities

Use identifiers to create relationships between the parent entity and each child entity.

Use identifiers to create relationships between the parent entity and each child entity.

Note

  • The SOURCE_DISCOUNT_LIST_ID on the QP_DISCOUNT_LISTS_INT worksheet is the root parent of all other entities. We use the term root when a hierarchy includes several levels, such as parent, child, and grandchild.

  • QP_DISCOUNT_LISTS_INT is the only sheet that's required. You use other sheets depending on the data you import.

    This Worksheet

    Is a Parent of This Worksheet

    QP_DISCOUNT_LISTS_INT

    QP_DISCOUNT_LIST_SETS_INT

    QP_DISCOUNT_LISTS_INT

    QP_DISCOUNT_LIST_ITEMS_INT

    QP_DISCOUNT_LIST_ITEMS_INT

    QP_PRICING_TERMS_INT

    QP_PRICING_TERMS_INT

    QP_MATRIX_DIMENSIONS_INT

    QP_MATRIX_DIMENSIONS_INT

    QP_MATRIX_RULES_INT

Create relationships between entities.

Use Column

In Child Worksheet

To Reference Column

In Parent Worksheet

SOURCE_ROOT_PARENT_ID

For example, 1001.

QP_MATRIX_RULES_INT

SOURCE_DISCOUNT_LIST_ID

QP_DISCOUNT_LISTS_INT

SOURCE_MATRIX_ID

For example, 7001.

QP_MATRIX_RULES_INT

SOURCE_MATRIX_ID

QP_MATRIX_DIMENSIONS_INT

SOURCE_ROOT_PARENT_ID

For example, 1001.

QP_MATRIX_DIMENSIONS_INT

SOURCE_DISCOUNT_LIST_ID

QP_DISCOUNT_LISTS_INT

SOURCE_PARENT_ID

For example, 100001.

QP_MATRIX_DIMENSIONS_INT

SOURCE_TERM_ID

QP_PRICING_TERMS_INT

SOURCE_ROOT_PARENT_ID

For example, 1001.

QP_PRICING_TERMS_INT

SOURCE_DISCOUNT_LIST_ID

QP_DISCOUNT_LISTS_INT

SOURCE_PARENT_ID

For example, 10001.

QP_PRICING_TERMS_INT

SOURCE_DISCOUNT_LIST_ITEM_ID

QP_DISCOUNT_LIST_ITEMS_INT

SOURCE_DISCOUNT_LIST_ID

For example, 1001.

QP_DISCOUNT_LIST_ITEMS_INT

SOURCE_DISCOUNT_LIST_ID

QP_DISCOUNT_LISTS_INT

SOURCE_DISCOUNT_LIST_ID

For example, 1001.

QP_DISCOUNT_LIST_SETS_INT

SOURCE_DISCOUNT_LIST_ID

QP_DISCOUNT_LISTS_INT

Create a relationship only when necessary. For example:

If You Don't Add Any Rows In

Then You Don't Create a Relationship Between

QP_MATRIX_RULES_INT

QP_MATRIX_RULES_INT and QP_MATRIX_DIMENSIONS_INT

QP_MATRIX_RULES_INT and QP_DISCOUNT_LISTS_INT.

QP_MATRIX_DIMENSIONS_INT

QP_MATRIX_DIMENSIONS_INT and QP_PRICING_TERMS_INT

QP_MATRIX_DIMENSIONS_INT and QP_DISCOUNT_LISTS_INT

Add Access Sets

Use QP_DISCOUNT_LIST_SETS_INT to specify the access set. In most situation, you use COMMON.

OPERATION_CODE

SET_CODE

CREATE

COMMON

The CREATE command doesn't create a new access set. It adds an access set that already exists to your discount list.

Here's how to see what other access sets are available.

  1. Go to the Pricing Administration work area and create a discount list.

  2. Set the business unit.

  3. Click Access Sets.

  4. Click Actions > Add Row.

  5. Search the Set Code column for the set you need.

  6. Use the value in the Set Code column in the work area for the SET_CODE column in the worksheet.

Add Items

  • Use the QP_DISCOUNT_LIST_ITEMS_INT worksheet.

  • Add one row for each item.

  • Set the values.

Tip: Use the Pricing Administration work area to determine the values you can use for some attributes. For example, to determine the values you can use for the item level, create a discount list in the Pricing Administration work area. On the Discount Lines tab, examine the values that are available in the Item Level attribute.

Attribute

Value

SOURCE_ROOT_PARENT_ID

The rule is a grandchild of the parent discount list.

Enter the same value that you enter in SOURCE_DISCOUNT_LIST_ID on sheet QP_DISCOUNT_LISTS_INT.

SOURCE_PARENT_ID

The rule is a child of the parent item.

Enter the same value that you enter in SOURCE_DISCOUNT_LIST_ITEM_ID on sheet QP_DISCOUNT_LIST_ITEMS_INT.

SOURCE_TERM_ID

Enter any numeric value.

Make sure the value is unique across worksheets.

NAME

Enter any alphanumeric value.

PRICING_RULE_TYPE_CODE

Enter one of.

  • SIMPLE. Create a simple rule.

  • ATTRIBUTE_PRICING. Create a rule that evaluates according to an attribute.

These values correspond to the same actions you can select in the Pricing Administration work area when you click Actions > Create in the Discount Rules area.

PRICE_TYPE_CODE

Enter one of.

  • ALL

  • ONE_TIME

  • RECURRING

CHARGE_TYPE_CODE

Try this.

  1. Go to the Setup and Maintenance work area, search for, then open the Manage Pricing Lookups task.

  2. On the Manage Pricing Lookups page, search Lookup Type for ORA_QP_CHARGE_TYPES.

    You can use any value that displays in the Lookup Code column, such as ORA_SALE, ORA_SERVICE, and so on.

CHARGE_SUBTYPE_CODE

On the Manage Pricing Lookups page, search Lookup Type for ORA_QP_CHARGE_SUBTYPES.

You can use any value that displays in the Lookup Code column, such as ORA_PRICE, ORA_FEE, and so on.

PRICE_PERIODICITY

PRICE_PERIODICITY_CODE

Include a value only if you set PRICE_TYPE_CODE to RECURRING.

Try this.

  1. Go to the Setup and Maintenance work area, search for, then open the Manage Units of Measure task.

  2. On the Manage Units of Measure page, search Class Name for TIME.

  3. Notice the values in the search results. You can use any value in the.

    • UOM Name column for PRICE_PERIODICITY. For example, Year, Month, Week, Day, Hour, Minute, Second, and so on.

    • UOM Code column for PRICE_PERIODICITY_CODE. For example, YR, MNTH, WK, HR, MIN, SEC, and so on.

ADJUSTMENT_TYPE_CODE

On the Manage Pricing Lookups page, search Lookup Type for ORA_QP_LINE_ADJ_TYPES.

You can use any value that displays in the Lookup Code column, such as.

  • DISCOUNT_AMOUNT

  • DISCOUNT_PERCENT

  • MARKUP_AMOUNT

  • MARKUP_PERCENT

  • PRICE_OVERRIDE

ADJUSTMENT_AMOUNT

Enter any numeric value.

ADJUSTMENT_BASIS

ADJUSTMENT_BASIS_ID

Identify the values you can select.

  1. Create a discount list in the Pricing Administration work area.

  2. In the Discount Rules area, click one of.

    • Actions > Create > Tier Based Rule

    • Actions > Create > Attribute Based Rule

  3. In the Create Discount Rule dialog, set the values, then, in the Attribute Based Rule area, click Actions > Add Row.

  4. Set the values in the row, then examine values you can select in the Adjustment Basis attribute.

    For example, here's one entry from the list.

    QP_AdjBasisforListPrc 3000100071623810

Use the basis or the basis ID. For example:

  • Use QP_AdjBasisforListPrc for ADJUSTMENT_BASIS.

    or

  • Use 3000100071623810 for ADJUSTMENT_BASIS_ID.

APPLY_TO_ROLLUP_FLAG

Set a value.

  • Y. Apply rolled up charge.

  • N. Don't apply rolled up charge.

For details, see Set Up Pricing for Configuration Models.

Verify Your Import

Verify That Your Scheduled Processes Finished Successfully

Verify That Your Scheduled Processes Finished Successfully

Note

  • Run your scheduled processes, then click View Log to examine the log to verify the records you import.

  • Select the Hierarchy option to help visualize parent and children.

  • Examine the log for the parent Import Discount Lists process and each child process.

  • Verify the scheduled processes finished successfully.

    For example, if you import one batch that includes 10 discount lists, and these 10 lists include 1,000 items and 1,000 pricing terms, then the log for the parent should include values like these.

    Entity

    Values

    Headers

    Total 10

    Imported 10

    Items

    Total 1000

    Imported 1000

    Terms

    Total 1000

    Imported 1000

  • The process stamps data with the UTC time zone from the server. You can't change the time zone.

  • The process uses English for translation. You can't use more than one language.

  • The log includes the total number of records it imports, even records that include a NO-OP operation.

  • If the status for a scheduled process is Error, then.

    • Click Error in the Status column.

    • In the Log and Output area, next to Attachment, click the link, such as ESS_O_112369, then examine the log. Click more to examine the log and a text file that includes more detailed instructions.

    • Examine the log for the parent and each child scheduled process. The logs for child processes typically contain more helpful details.

Verify Header Values

Verify you successfully imported header values.

Verify you successfully imported header values.

Finish your import, then go to the Pricing Administration work area and verify that the import imported your discount list and header values from the QP_DISCOUNT_LISTS_INT worksheet, such as Name, Status, Currency, Business Unit, Start Date, and so on.

Verify Items

Search for your item in the Discount Lines area.

Note

  • Search for your item in the Discount Lines area.

  • Verify that the import imported values from the QP_DISCOUNT_LIST_ITEMS_INT worksheet, such as Item Level, Name, Pricing UOM, Line Type, and so on.

  • Some values don't import but instead come from Product Information Management, such as Description.

  • If you import more than one item, then search for and verify each item.

Verify Rules

Examine rules that you import in the Discount Rules area.

Note

  • Examine rules that you import in the Discount Rules area.

  • Verify that the import imported values from the QP_PRICING_TERMS_INT worksheet, such as Rule Name, Rule Type, Rule Start Date, Price Type, and so on.

  • If you import more than one rule, then verify each rule.

Update Your Discount List

Use the UPDATE operation to update a discount list that already exists. You must include values in the BATCH_NAME, SOURCE_DISCOUNT_LIST_ID, and NAME columns.

For example, assume the header on your current discount list includes values.

SOURCE_DISCOUNT_LIST_ID

NAME

DESCRIPTION

BUSINESS_UNIT_ID

BUSINESS_UNIT_NAME

CURRENCY_CODE

START_DATE

END_DATE

STATUS_CODE

1001

DISCOUNT_LIST_1

DISCOUNT_LIST_1

204

Vision Operations

USD

2014/01/15 10:10:10

2020/01/15 10:10:10

APPROVED

If you include a value in the template for the END_DATE attribute but leave the other attributes that currently contain values in the list empty, then UPDATE updates END_DATE, doesn't update any other attributes in the row, and doesn't replace the discount list.

If you include a value for all attributes that the current list contains, then the update replaces the discount list with a new list that includes your new values. In this example, to replace the current list, you would need to add a value for each of these attributes.

  • NAME

  • DESCRIPTION

  • BUSINESS_UNIT_ID

  • BUSINESS_UNIT_NAME

  • CURRENCY_CODE

  • START_DATE

  • END_DATE

  • STATUS_CODE

Here's how your update or replace would look in the template.

Type of Update

BATCH_NAME

OPERATION_CODE

SOURCE_DISCOUNT_LIST_ID

NAME

DESCRIPTION

BUSINESS_UNIT_ID

BUSINESS_UNIT_NAME

CURRENCY_CODE

START_DATE

END_DATE

STATUS_CODE

Update

DL_IMPORT_2

UPDATE

1001

DISCOUNT_LIST_1

-

-

-

-

-

2020/01/15 10:10:10

-

Replace

DL_IMPORT_2

UPDATE

1001

DISCOUNT_LIST_1

DISCOUNT_LIST_1

204

Vision Operations

USD

2014/01/15 10:10:10

2020/01/15 10:10:10

APPROVED

Note

  • There are some attributes you can't update, such as Business Unit.

  • You can update the currency code only if the discount list doesn't contain a pricing terms.

  • You can update the status code from In Progress to Approved but not from Approved to In Progress.

Attributes You Can Update

Worksheet

Attributes You Can Update

QP_DISCOUNT_LISTS_INT

Status Code

Currency Code

Start Date

End Date

Descriptive Flexfields

QP_DISCOUNT_LIST_ITEMS_INT

Descriptive Flexfields

QP_PRICING_TERMS_INT

Price Type

Charge Type

Charge Subtype

Price Periodicity

Price Periodicity Code

Adjustment Basis

Adjustment Basis Id

Adjustment Type

Adjustment Amount

Start Date

End Date

Descriptive Flexfields

QP_MATRIX_DIMENSIONS_INT

-

QP_MATRIX_RULES_INT

Value Strings 1 through 10

Start Date

End Date

Related Topics