1. Administering Pricing
  2. Import Batches of Price Lists

Import Batches of Price Lists

Use an Excel spreadsheet to import a batch of price lists into Oracle Pricing.

Here's a decision tree that you can use to help determine whether you can import price lists in a batch.

decision tree that you can use to help determine whether you can import price lists in a batch

Note

  • If any of your price lists include a coverage item or a configure option of a configured item, then don't batch. Instead, import your price lists one at a time, or import the price lists that don't have a coverage item or a configure option separately in a batch.

  • If you have many price lists to import, and none of them reference a coverage item or configure option, then import them in a batch.

  • You can use FBDI to import pricing details on the Microsoft Windows or on the Apple macOS operating systems.

The tools and guidelines that you use and the procedure you do are very similar to importing a batch of discount lists, but with a few important differences.

Difference

Discount List

Price List

Name of the FBDI Template

DiscountListImportTemplate.xlsm

PriceListsImportBatchTemplate.xlsm

Scheduled processes that you run
  • Set the Import Process parameter to Import Price Lists Batch when you run the Load Interface File for Import scheduled process
  • Import Discount Lists
  • Import Discount List Headers and Access Sets
  • Import Discount List Items and Rules
  • Set the Import Process parameter to Import Price Lists Batch when you run the Load Interface File for Import scheduled process
  • Import Price Lists Batch
  • Import Price List Headers and Access Sets
  • Import Price List Items and Charges

Interface tables

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

QP_PRICE_LISTS_INT

QP_PRICE_LIST_SETS_INT

QP_PRICE_LIST_ITEMS_INT

QP_PRICING_TERMS_INT

QP_MATRIX_DIMENSIONS_INT

QP_MATRIX_RULES_INT

QP_TIER_HEADERS_INT

QP_TIER_LINES_INT

Operations that you can use

Create, Update, or No-Op.

Create, Update, No-Op, or End Insert.

Attribute that identifies the list

SOURCE_DISCOUNT_LIST_ID

SOURCE_PRICE_LIST_ID

Attribute that identifies the item

SOURCE_DISCOUNT_LIST_ITEM_ID

SOURCE_PRICE_LIST_ITEM_ID

Page in Pricing Administration that you use to verify your import

Edit Discount List

Edit Price List

For details, see Guidelines for Importing Batches of Discount Lists.

Create a Charge

Use the Source Charge Id to identify your price list charge. The import maps the Source Charge Id attribute in the template to the External System Reference Id attribute in the Price List Charges table in the database.

Here's an example that creates two charges.

Row

OPERATION_CODE

SOURCE_PRICE_LIST_ID

SOURCE_CHARGE_ID

PARENT_ENTITY_TYPE_CODE

PARENT_SOURCE_ID

CHARGE_DEFINITION_CODE

BASE_PRICE

1

CREATE

1001

6001

PRICE_LIST_ITEM

10001

QP_SALE_PRICE

100.00

2

CREATE

1001

6002

PRICE_LIST_ITEM

10001

QP_SALE_PRICE

120.00

Note

  • Row 1 creates a charge for Id 6001 with a base price of 100, and maps 6001 to the External System Reference Id attribute.

  • Row 2 creates a charge for Id 6002 with a base price of 120, and maps 6002 to the External System Reference Id attribute.

You can also update charges. For example:

Row

OPERATION_CODE

SOURCE_PRICE_LIST_ID

SOURCE_CHARGE_ID

PARENT_ENTITY_TYPE_CODE

PARENT_SOURCE_ID

CHARGE_DEFINITION_CODE

BASE_PRICE

1

UPDATE

1001

6001

PRICE_LIST_ITEM

10001

-

110.00

2

UPDATE

1001

6002

PRICE_LIST_ITEM

10001

-

132.00

3

UPDATE

1001

6003

PRICE_LIST_ITEM

10003

1

249.95

Note

  • Rows 1 and 2 update charges from the first example.

  • Row 1 updates charge Id 6001 to a base price of 110.

  • Row 2 updates charge Id 6002 to a base price of 132.

  • Use the same value in SOURCE_CHARGE_ID to update the charge.

  • Row 3 updates charge 6003 to a base price of 249.95. If you don't use the template to create the charge, then you can use the CHARGE_LINE_NUMBER attribute to update it instead of SOURCE_CHARGE_ID.

    For example, if you use the Pricing Administration work area, REST API, Application Development Framework Desktop Integration (ADFDI), or an older version of the FBDI template, then you can use CHARGE_LINE_NUMBER.

    If you use CHARGE_LINE_NUMBER, then you must also update Source Charge Id, then use Source Charge Id in all future update operations. Assume you used REST API to create charge 6003, so you set CHARGE_LINE_NUMBER to 1.

For more, go to REST API for Oracle Supply Chain Management Cloud, expand Order Management > Document Prices, then click Price Sales Transaction.

End a Charge and Create a New One

Use the End Insert operation to end date a charge that already exists, then create a new one.

Assume you need to change the base price of the sale price for the AS54111 item.

change the base price of the sale price for the AS54111 item.

Try It

  1. Set the values for these attributes on the QP_PRICE_LIST_CHARGES_INT worksheet.

    Attribute

    Description

    SOURCE_PRICE_LIST_ID

    Identify the price list that contains the current charge.

    SOURCE_CHARGE_ID

    Identify the current charge that you're ending. Use the item-line format.

    where

    • item is the name of the item, such as A54111.

    • - (dash) separates the name of the item from the line.

    • line is the line number of the charge that you're ending, such as 1.

    For example, AS54111-1 specifies to end the charge that's on line 1 of the charges for the AS54111 item.

    NEW_SOURCE_CHARGE_ID

    Identify the new charge that you're creating.

    Use the same format that you use for SOURCE_CHARGE_ID, except increment the line by one.

    For example, AS54111-2 specifies to create line 2 in the charges for the AS54111 item.

    PARENT_ENTITY_TYPE_CODE

    Set it to the type of entity where the charge resides.

    The charge in this example is for an item on a price list, so set it to PRICE_LIST_ITEM.

    PARENT_SOURCE_ID

    Identify the item that contains the charges that you're modifying.

    For example, AS54111 identifies the AS54111 item.

    BASE_PRICE

    Specify the base price to use for the new charge you're creating, such as 550.

    START_DATE

    Set the date to start the new charge.

    End Insert uses the value in START_DATE to set the end date for the old charge.

    If START_DATE happens before the end date of the old charge, then End Insert sets the end date of the old charge to START_DATE minus one second. End Insert does this to avoid a date overlap.

    END_DATE

    As an option, set the date to end the new charge.

    If you don't include a value in END_DATE, then the import will set the end date of the charge that you identify in SOURCE_CHARGE_ID to START_DATE minus 1 second.

    For example:

    OPERATION_CODE SOURCE_PRICE_LIST_ID SOURCE_CHARGE_ID NEW_SOURCE_CHARGE_ID PARENT_ENTITY_TYPE_CODE PARENT_SOURCE_ID BASE_PRICE START_DATE
    END-INSERT 1001 AS54111-1 AS54111-2 PRICE_LIST_ITEM AS54111 550 2020/02/01 00:00:00

  2. Finish the import.

    End Insert will use the values that you import to end date the old charge and create a new one.

  3. Go to the Pricing Administration work area, open the price list that contains the AS54111 item, then open the charge for the AS54111.

  4. Verify that your import end dated the old charge, added the new charge, and set values for the new charge to the values that you specified.

    Line Number Pricing Charge Definition Base Price (USD) Calculation Type Start Date End Date
    1 Sale Price 500.00 - 1/1/20 12:00 AM 1/31/20 11:59 PM
    2 Sale Price 550.00 - 2/1/20 12:00 AM -

If you use the:

  • Create operation. The value in SOURCE_CHARGE_ID in your worksheet will replace the value in the External System Ref ID attribute in the Oracle database.
  • Update operation or the End Insert operation. The import uses SOURCE_CHARGE_ID to locate the charge to update or end date.

If you don't include a value in the END_DATE attribute, then the import will set the end date of the charge that you identify in SOURCE_CHARGE_ID to START_DATE minus 1 second.

Guidelines for End Insert

  • You can use End Insert only on the QP_PRICE_LIST_CHARGES_INT worksheet.
  • Use End Insert to end an existing charge and create a new one on a single row in the QP_PRICE_LIST_CHARGES_INT worksheet. This way, you don't have to end the charge on one row and then create a new charge on another row.
  • End Insert will create a new charge, and the new charge will contain the values that you enter in NEW_SOURCE_CHARGE_ID, BASE_PRICE, and START_DATE.
  • If you use End Insert for a charge, then you don't need to set the OPERATION_CODE attribute to NO-OP on the QP_PRICE_LISTS_INT worksheet or on the QP_PRICE_LIST_ITEMS_INT worksheet for that charge.
You must include values for these attributes when you use End Insert:
  • SOURCE_CHARGE_ID
  • NEW_SOURCE_CHARGE_ID
  • BASE_PRICE
  • START_DATE

End Insert will get the values for other attributes from the charge that you specify in SOURCE_CHARGE_ID, such as the charge definition, price type, price periodicity, and so on.

Guidelines for Charge Line Number

If you use the:

  • Create operation. The import automatically assigns the Charge Line Number and you don't need to specify a value in the CHARGE_LINE_NUMBER attribute on the QP_PRICE_LIST_CHARGES_INT worksheet.
  • Update operation. You must set the CHARGE_LINE_NUMBER attribute on the QP_PRICE_LIST_CHARGES_INT worksheet to the same value that the Charge Line Number attribute in the Oracle database already contains for this charge on the price list. The import typically uses the Source Charge Id attribute to identify the record. If the import can't identify the record, then it uses the Charge Line Number to identify it.

Import or Update a Large Number of Pricing Tiers

You can periodically import tier data for your price lists in batches. Assume you update your tier data during each quarter of the year, and you need to periodically import this data into Pricing Administration. You can use batch import to import tier data.

You can:

  • Manage tiered adjustments for price list charges.
  • Create, append, or update tier headers and tier lines, including data that you store in a flexfield.
  • Monitor the import and use error messages to troubleshoot problems that happen during import.

Realize these benefits.

  • Migrate data from your source system into Pricing Administration.
  • Efficiently create and update pricing tiers on your price lists.
  • Quickly update a large number of tier headers and lines.
  • Periodically maintain the data that you migrate.
  • Use a stable, repeatable, and efficient process.
Guidelines
  • Use the Tier Headers and Tier Lines worksheets in the PriceListsImportBatchTemplate.xlsm import template.
  • Set the Operation Code attribute on each worksheet to CREATE, UPDATE, or NO-OP.
  • Use the Tier Line Number attribute to update a tier line that already exists.
  • Use the output file from the scheduled process that you use during import to get details about the tier headers and tier lines that you imported.
  • Update tier data that you create through some other technology, such as through the Pricing Administration work area, ADFDI, REST API, or another File-Based Data Import template.

Delete Pricing Data from Interface Tables

If you use file-based data import to periodically import pricing and discount data, then the interface tables might grow in size when you process new batches. You might notice a gradual decrease in performance when you do your imports. To avoid this problem, you can use the Delete Pricing Data from Interface Tables scheduled process to delete data from the interface tables after you import each batch.

Try it.

  1. Go to the Scheduled Processes work area, then run the scheduled process that you use to import your pricing data, such as Import Price Lists Batch.
  2. Wait for the scheduled process to finish.
  3. Run the Delete Pricing Data from Interface Tables scheduled process.

Note

  • Set up a schedule when you run this process so it runs at regular intervals. For example, run it one time every morning at 2AM. This will help prevent the tables from becoming really big with lots of old data. Big tables with old data degrade performance.
  • You can set the Delete Option parameter only to one value, which is to delete all import data from the interface tables. This process deletes all data from all interface tables regardless of your import's status.
  • You can run the Delete Pricing Data from Interface Tables scheduled process only after you finish the import. You can't run it at the same time that you run these scheduled processes:
    • Import Price Lists Batch
    • Import Price List
    • Import Discount Lists

Related Topics