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.

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.

Try It

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

Finish the import.

End Insert will use the values that you import to end date the old charge and create a new one.
Go to the Pricing Administration work area, open the price list that contains the AS54111 item, then open the charge for the AS54111.

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.

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.
Wait for the scheduled process to finish.
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