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.
Assume you import a discount list named DISCOUNT_LIST_1.
-
Copy discount list data from your source system into the DiscountListImportTemplate.xlsm Excel workbook.
-
Click Generate CSV File in DiscountListImportTemplate.xlsm to create a zip file.
-
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.
-
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.
-
-
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
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.
-
In the Setup and Maintenance work area, go to the task.
-
Offering: Order Management
-
Functional Area: Organization Structures
-
Task: Manage Business Unit Set Assignment
-
-
In the dialog, select Manage Business Unit Set Assignment, set Business Unit to Select and Add, then click Apply and Go to Task.
-
On the Manage Business Units page, search for Vision Operations.
-
Click View > Columns, then add a check mark to BusinessUnitId.
-
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.
-
Go to the Setup and Maintenance work area, search for, then open the Manage Currencies task.
-
On the Manage Currencies page, leave the search attributes empty, then click Search.
-
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.
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.
-
Go to the Pricing Administration work area and create a discount list.
-
Set the business unit.
-
Click Access Sets.
-
Click Actions > Add Row.
-
Search the Set Code column for the set you need.
-
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.
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.
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.
|
CHARGE_TYPE_CODE |
Try this.
|
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.
|
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.
|
ADJUSTMENT_AMOUNT |
Enter any numeric value. |
ADJUSTMENT_BASIS ADJUSTMENT_BASIS_ID |
Identify the values you can select.
Use the basis or the basis ID. For example:
|
APPLY_TO_ROLLUP_FLAG |
Set a value.
For details, see Set Up Pricing for Configuration Models. |
Verify Your Import
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.
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
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
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 |