Import Discount List Batches that Include Pricing Matrices

Add a pricing matrix to a discount list that already exists.

Assume you must import the DISCOUNT_LIST_1 discount list. It already exists, but you must add pricing terms and a pricing matrix to it. You must add the Size condition and the Color condition. For details, see Import Batches of Discount Lists.

Summary of the Setup

Add your discount list data.
Set up matrix class.
Upload and verify data.

Add Your Discount List Data

Get a copy of the Excel file for this example.
- Go to Technical Reference for Oracle Order Management (Doc ID 2051639.1).
- Download the Payloads and Files attachment.
- Unzip the attachment and open DiscountListImportTemplate_example_2.xlsm. This file already includes the values you use in this example.
Open the DiscountListImportTemplate.xlsm file in Excel.

Add the discount list header.

Click the QP_DISCOUNT_LISTS_INT tab, then set the values.

Attribute	Value
BATCH_NAME	Import Batch 1
OPERATION_CODE	NO-OP The discount lists already exists in the Pricing Administration work area. You aren't adding it, so you use NO-OP (no operation) to indicate to not do an operation, but you still use this row to identify the discount list where you're adding the pricing matrix.
SOURCE_DISCOUNT_LIST_ID	1001 This value identifies the discount list. You use it to create a relationship between the parent list DISCOUNT_LIST_1 on sheet QP_DISCOUNT_LISTS_INT and other entities on other child sheets. For example, you also use 1001 in the SOURCE_DISCOUNT_LIST_ID column of the child QP_DISCOUNT_LIST_ITEMS_INT sheet.

Attribute

Value

BATCH_NAME

Import Batch 1

OPERATION_CODE

NO-OP

The discount lists already exists in the Pricing Administration work area. You aren't adding it, so you use NO-OP (no operation) to indicate to not do an operation, but you still use this row to identify the discount list where you're adding the pricing matrix.

SOURCE_DISCOUNT_LIST_ID

1001

This value identifies the discount list. You use it to create a relationship between the parent list DISCOUNT_LIST_1 on sheet QP_DISCOUNT_LISTS_INT and other entities on other child sheets.

For example, you also use 1001 in the SOURCE_DISCOUNT_LIST_ID column of the child QP_DISCOUNT_LIST_ITEMS_INT sheet.

In your web browser, go to the Pricing Administration work area, then click Tasks > Manage Discount Lists.
On the Manage Discount Lists page, search for the DISCOUNT_LIST_1 discount list, then open it for editing.
Expand Show Detail.

Map values from the Edit Discount List page of the work area to the QP_DISCOUNT_LISTS_INT worksheet in Excel.

Attribute	Value in Work Area	Value in QP_DISCOUNT_LISTS_INT
Name	DISCOUNT_LIST_1	Set NAME to DISCOUNT_LIST_1.
Business Unit	Vision Operations	Set BUSINESS_UNIT_NAME to Vision Operations.
Currency Code	USD	Set CURRENCY_CODE to USD.
Start Date	1/15/19 10:10 AM	Set START_DATE to 2019/01/15 10:10:10.
End Date	Empty	Leave END_DATE empty.
Status	Approved	Set STATUS_CODE to Approved.
Description	DISCOUNT_LIST_1	Set DESCRIPTION to DISCOUNT_LIST_1.

Make sure you map the exact value, including upper case and lower case letters. For example, use Vision Operations. Don't use other values, such as Vision operations, vision operations, visionOperations, Vision Ops, and so on.

For example:

Map values from the Edit Discount List page of the work area to the QP_DISCOUNT_LISTS_INT worksheet in Excel

Ignore the access sets in Excel.

This discount list doesn't have any access sets other than the common set, so ignore the QP_DISCOUNT_LIST_SETS_INT worksheet.

Add your item.

Click the QP_DISCOUNT_LIST_ITEMS_INT tab in Excel, then set the values.

Attribute	Value
OPERATION_CODE	NO-OP The item already exists in the Pricing Administration work area. You aren't adding it, so you use NO-OP to indicate to not do an operation. You use this row to identify the item where you're adding the pricing matrix.
SOURCE_DISCOUNT_LIST_ID	1001
SOURCE_DISCOUNT_LIST_ITEM_ID	10001 This value creates a relationship between the parent AS54888 item on the QP_DISCOUNT_LIST_ITEMS_INT worksheet and the. Child pricing term on worksheet QP_PRICING_TERMS_INT. Child matrix dimension on worksheet QP_MATRIX_DIMENSIONS_INT.

Attribute

Value

OPERATION_CODE

NO-OP

The item already exists in the Pricing Administration work area. You aren't adding it, so you use NO-OP to indicate to not do an operation. You use this row to identify the item where you're adding the pricing matrix.

SOURCE_DISCOUNT_LIST_ID

1001

SOURCE_DISCOUNT_LIST_ITEM_ID

10001

This value creates a relationship between the parent AS54888 item on the QP_DISCOUNT_LIST_ITEMS_INT worksheet and the.

Child pricing term on worksheet QP_PRICING_TERMS_INT.
Child matrix dimension on worksheet QP_MATRIX_DIMENSIONS_INT.

In Pricing Administration, on the Edit Discount List page, on the Discount Lines tab, search for your item.

Attribute	Value
Name	AS54888

Map values from the search results in Pricing Administration to the QP_DISCOUNT_LIST_ITEMS_INT worksheet in Excel.

Attribute	Value in Work Area	Column in QP_DISCOUNT_LIST_ITEMS_INT
Item Level	Item	Set ITEM_LEVEL_CODE to ITEM.
Name	AS54888	Set ITEM_NUMBER to AS54888.
-	-	Leave ITEM_ID empty.
Pricing UOM	Each	Set PRICING_UOM to Each.
-	-	Set PRICING_UOM_CODE to Ea.
Line Type	Buy	Set LINE_TYPE_CODE to ORA_BUY.

Service Duration Period, Service Duration, and Associated Items are empty in Pricing Administration, so leave all other columns in the worksheet empty, such as SERVICE_DURATION_PERIOD, SERVICE_DURATION_PERIOD_CODE, SERVICE_DURATION, ATTRIBUTE_CATEGORY, ATTRIBUTE_CHAR1, and so on.

Create a new pricing term.

Click the QP_PRICING_TERMS_INT tab in Excel, then set the values.

Attribute	Value
OPERATION_CODE	CREATE
SOURCE_ROOT_PARENT_ID	1001 This value creates a relationship to the DISCOUNT_LIST_1 discount list that you specify in column SOURCE_DISCOUNT_LIST_ID on sheet QP_DISCOUNT_LISTS_INT. The discount list is the grandparent, and the AS54888 item is the parent of the pricing term.
SOURCE_PARENT_ID	10001 This value creates a relationship to the parent AS54888 item that you specify in column SOURCE_DISCOUNT_LIST_ITEM_ID on sheet QP_DISCOUNT_LIST_ITEMS_INT.
SOURCE_TERM_ID	100001 The pricing term is a parent of the pricing matrix, so you will use this value in the next step to create a relationship between term and matrix.
NAME	AS54888-AR1
PRICING_RULE_TYPE_CODE	ATTRIBUTE_PRICING
PRICE_TYPE_CODE	ONE_TIME
CHARGE_TYPE_CODE	ORA_SALE
CHARGE_SUBTYPE_CODE	ORA_PRICE
APPLY_TO_ROLLUP_FLAG	N
START_DATE	2019/01/15 10:10:10

Leave all other values in the sheet empty.

Create new matrix dimensions.

Click the QP_MATRIX_DIMENSIONS_INT tab, then set the values.

OPERATION_CODE	SOURCE_ROOT_PARENT_ID	SOURCE_PARENT_ID	SOURCE_MATRIX_ID	DIMENSION_NAME	DIMENSION_TYPE	MAP_TO_RULE_COLUMN
CREATE	1001	100001	7001	Color	Condition	VALUE_STRING1
CREATE	1001	100001	7001	Size	Condition	VALUE_STRING2
CREATE	1001	100001	7001	Adjustment Type	Result	VALUE_STRING3
CREATE	1001	100001	7001	Adjustment Amount	Result	VALUE_STRING4
CREATE	1001	100001	7001	Adjustment Basis	Result	VALUE_STRING5

Note

Use a separate row for each CREATE action.
SOURCE_ROOT_PARENT_ID maps to the SOURCE_DISCOUNT_LIST_ID column on the QP_DISCOUNT_LISTS_INT sheet. It identifies DISCOUNT_LIST_1 as the root parent of each matrix dimension.
SOURCE_PARENT_ID maps to the SOURCE_TERM_ID column on the QP_PRICING_TERMS_INT sheet. It identifies AS54888-AR1 as the parent pricing term of each matrix dimension.
SOURCE_MATRIX_ID is a unique value you add to identify the pricing matrix.
DIMENSION_NAME and DIMENSION_TYPE must equal the same value you set up in the matrix class. You will verify these later in this topic.
Use a different value for each row in MAP_TO_RULE_COLUMN. You must use the name of a VALUE_STRING column from sheet QP_MATRIX_RULES_INT. You will see why next.

Create new matrix rules.

Click the QP_MATRIX_RULES_INT tab, then set the values.

OPERATION_CODE	SOURCE_ROOT_PARENT_ID	SOURCE_MATRIX_ID	SOURCE_RULE_ID
CREATE	1001	7001	1500001
CREATE	1001	7001	1500002

Note

Use a separate row for each CREATE action.
SOURCE_ROOT_PARENT_ID maps to the SOURCE_DISCOUNT_LIST_ID column on the QP_DISCOUNT_LISTS_INT sheet. It identifies DISCOUNT_LIST_1 as the root parent of each matrix rule.
SOURCE_MATRIX_ID maps to the SOURCE_ MATRIX_ID column on the QP_MATRIX_DIMENSIONS_INT sheet. It identifies 7001 as the parent dimension for the matrix rule.
SOURCE_RULE_ID is a unique value you add to identify the rule.

Here's a continuation of the same rows. You add the value strings.

VALUE_STRING1	VALUE_STRING2	VALUE_STRING3	VALUE_STRING4	VALUE_STRING5
Blue	Leave empty	DISCOUNT_AMOUNT	50	Leave empty
Blue	Large	DISCOUNT_PERCENT	10	QP_AdjBasisforBaseListPrc

Use the value strings columns to create relationships between QP_MATRIX_DIMENSIONS_INT and QP_MATRIX_RULES_INT.

Note

You map value strings from the QP_MATRIX_DIMENSIONS_INT sheet to the QP_MATRIX_RULES_INT sheet.
The MAP_TO_RULE_COLUMN on sheet QP_MATRIX_DIMENSIONS_INT identifies the column on sheet QP_MATRIX_RULES_INT.
For example, VALUE_STRING1 in column MAP_TO_RULE_COLUMN maps to column VALUE_STRING1 on sheet QP_MATRIX_RULES_INT. The dimension on sheet QP_MATRIX_DIMENSIONS_INT is Color, and it maps to VALUE_STRING1 on QP_MATRIX_RULES_INT, which contain Blue.
The value string column on QP_MATRIX_RULES_INT identifies the value you will be able to select in Pricing Administration. If you leave the value empty in a value string column on QP_MATRIX_RULES_INT, then you can select any value Pricing Administration. If you leave it empty, make sure you enable Allow Null for the matrix class, which you will do later in this topic.
The value for VALUE_ STRING5 is QP_AdjBasisforBaseListPrc. Its chopped off in the screen print.

Set Up Matrix Class

You must set up the class so it supports the pricing matrix that you're adding to the discount list. You must set it up before you upload your data.

Note

Add condition columns in the matrix class that support the conditions you add on the QP_MATRIX_DIMENSIONS_INT sheet.
Add result columns in the matrix class that support the results you add on the QP_MATRIX_DIMENSIONS_INT sheet.
Enable the Allow Null attribute on the condition in the matrix class so you can use an empty value in the QP_MATRIX_RULES_INT sheet. Use null so you can enter any value when you set up the discount list in the Pricing Administration work area. If you don't use null, then you must select from the values that you specify on the sheet.
Do this set up only if your import includes conditions and results that are different from the matrix class in the Pricing Administration work area.

For this example, you add your own attributes to the predefined Pricing Term Adjustment matrix class.

Try it.

Go to the Pricing Administration work area, then click Tasks > Manage Matrix Classes.
On the Manage Matrix Classes page, in the Name column, click Pricing Term Adjustment.

On the Edit Matrix Class page, in the Condition Columns area, add rows.

Name	Comparison	Compare to Attribute	Allow Null
Size	=	itemExtensibleAttribute.VarcharValue	Contains a check mark
Color	=	itemExtensibleAttribute.VarcharValue	Contains a check mark

Recall that you added these in the worksheet and specified them as Condition dimension types.

Learn how to set the compare to attribute and domain in the condition and result. For details, see Add Your Own Attributes to Items in Pricing.

Verify that the results support your new pricing matrix.

In the Result Columns area, if these rows don't exist, add them. Recall that you added these in the worksheet and specified them as Result dimension types. Notice that they're all required.

Name	Required	Allow Null
Adjustment Type	Contains a check mark	Empty
Adjustment Amount	Contains a check mark	Empty
Adjustment Basis	Contains a check mark	Contains a check mark

Click Save and Close.

Upload and Verify Data

Do it.

Create the zip file, upload your data, and run the scheduled processes. For details, see Import Batches of Discount Lists.
In the Pricing Administration work area, click Tasks > Manage Discount Lists.
Search for and DISCOUNT_LIST_1 and open it for editing.

Verify that the header contains the values you specified on sheet QP_DISCOUNT_LISTS_INT.

Attribute	Value
Name	DISCOUNT_LIST_1
Currency	USD
Business Unit	Vision Operations

In the Discount Lines area, search for the item you added from sheet QP_DISCOUNT_LIST_ITEMS_INT.

Attribute	Value
Name	AS54888

Verify that the search results contain the values you specified on sheet QP_DISCOUNT_LIST_ITEMS_INT.

Attribute	Value
Pricing UOM	Each
Line Type	Buy

Note that Pricing gets other values for the item from Product Information Management, such as Standard Desktop in the Description.

Expand section Item AS54888 Each Buy: Discount Rules, then verify that it contains the values you specified in sheet QP_PRICING_TERMS_INT.

Attribute	Value
Rule Name	AS54888-AR1
Rule Type	Attribute Pricing
Price Type	One Time
Charge Type	Sale
Charge Subtype	Price

Expand section Attribute Based Rule, then verify that it contains the values you specified in sheet QP_MATRIX_RULES_INT.

Size	Color	Adjustment Type	Adjustment Amount	Adjustment Basis
Empty	Blue	Discount Amount	50	Empty
Large	Blue	Discount Percent	10	QP_AdjBasisforBaseListPrc