Import Batches of Discount Lists
Use an Excel spreadsheet to import a batch of discount lists into Oracle Pricing.
In this example, you import two discount lists in one batch. Each list contains one item.
List |
Item |
---|---|
DISCOUNT_LIST_1 |
AS54888 |
DISCOUNT_LIST_2 |
CN92777 |
You use the same procedure that you use when you import a price list, but with a few important differences. For details, see Import Price Lists.
Summary of the Setup
-
Prepare your data.
-
Upload your data.
-
Import your data.
-
Verify your import.
You can use FBDI to import pricing details on the Microsoft Windows or on the Apple macOS operating systems.
Prepare Your Data
-
Download the DiscountListImportTemplate.xlsm file.
-
Select your update and click Books.
-
In the Development area, under File Based Data Import for Oracle Supply Chain Management Cloud, click HTML.
-
Expand Order Management, then click Discount List Import.
-
In the Discount List Import area, click DiscountListImportTemplate.xlsm.
To get a file that already includes the values that you use in this example, go to Technical Reference for Order Management (Doc ID 2051639.1), download the Payloads and Files attachment, open it, then open DiscountListImportTemplate_example_1.xlsm.
You must download and use DiscountListImportTemplate.xlsm from File-Based Data Import (FBDI) for Oracle SCM. Don't use ManageDiscountLists.xlsx that you can download from the Pricing Administration work area.
-
Add discount lists.
Click QP_DISCOUNT_LISTS_INT, then add your discount lists to the template. Add two rows, one row for each discount list.
BATCH_NAME
OPERATION_CODE
SOURCE_DISCOUNT_LIST_ID
NAME
DESCRIPTION
BUSINESS_UNIT_ID
BUSINESS_UNIT_NAME
CURRENCY_CODE
START_DATE
STATUS_CODE
DL_IMPORT_1
CREATE
1001
DISCOUNT_LIST_1
DISCOUNT_LIST_1
204
Vision Operations
USD
1/15/2019 10:10:10 AM
APPROVED
DL_IMPORT_1
CREATE
1002
DISCOUNT_LIST_2
DISCOUNT_LIST_2
204
Vision Operations
USD
1/15/2019 10:10:10 AM
IN_PROGRESS
Here's more detail.
Attribute
Value
Batch Name
The example uses DL_IMPORT_1 as the Batch Name in rows 5, and 6, so the batch will include two different discount lists.
-
DISCOUNT_LIST_1
-
DISCOUNT_LIST_2
Operation Code
CREATE
SOURCE_DISCOUNT_LIST_ID
Enter:
-
1001 for DISCOUNT_LIST_1
-
1002 for DISCOUNT_LIST_2
NAME
DESCRIPTION
Enter:
-
DISCOUNT_LIST_1
-
DISCOUNT_LIST_2
The Pricing Administration work area will display these values.
BUSINESS_UNIT_ID
204
204 is the Business Unit ID for Vision Operations.
BUSINESS_UNIT_NAME
Vision Operations
CURRENCY_CODE
USD
START_DATE
2019/01/15 10:15:20
STATUS_CODE
Enter two different values:
-
APPROVED for DISCOUNT_LIST_1
-
IN_PROGRESS for DISCOUNT_LIST_2
Leave all other columns empty.
-
-
Add access sets.
Click QP_DISCOUNT_LIST_SETS_INT, then add one access set for each discount list.
OPERATION_CODE
SOURCE_DISCOUNT_LIST_ID
SOURCE_DISCOUNT_LIST_SET_ID
SET_ID
SET_CODE
CREATE
1001
100
Leave this empty
COMMON
CREATE
1002
101
Leave this empty
COMMON
-
Add items.
Click QP_DISCOUNT_LIST_ITEMS_INT, then add your items. Add two rows, one row for each item.
OPERATION_CODE
SOURCE_DISCOUNT_LIST_ID
SOURCE_DISCOUNT_LIST_ITEM_ID
ITEM_LEVEL_CODE
ITEM_NUMBER
ITEM_ID
PRICING_UOM
PRICING_UOM_CODE
LINE_TYPE_CODE
CREATE
1001
10001
ITEM
AS54888
Leave this empty
Each
Ea
ORA_BUY
CREATE
1002
10002
ITEM
CN92777
Leave this empty
Each
Ea
ORA_BUY
-
Add a discount rule.
Click QP_PRICING_TERMS_INT, then add a rule.
OPERATION_CODE
SOURCE_ROOT_PARENT_ID
SOURCE_PARENT_ID
SOURCE_TERM_ID
NAME
PRICE_TYPE_CODE
CHARGE_TYPE_CODE
CHARGE_SUBTYPE_CODE
ADJUSTMENT_TYPE_CODE
ADJUSTMENT_AMOUNT
ADJUSTMENT_BASIS
APPLY_TO_ROLLUP_FLAG
START_DATE
CREATE
1001
10001
100001
AS54888-SR1
ONE_TIME
ORA_SALE
ORA_PRICE
DISCOUNT_PERCENT
5
QP_AdjBasisforBaseListPrc
N
2019/01/15 10:15:20
You aren't adding a pricing matrix, so leave these worksheets empty.
-
QP_MATRIX_DIMENSIONS_INT
-
QP_MATRIX_RULES_INT
How to Set the Value for SOURCE_DISCOUNT_LIST_ID
Create a relationship.
Note
-
Use SOURCE_DISCOUNT_LIST_ID to create a relationship between the parent discount list and other child entities. For example:
-
Set SOURCE_DISCOUNT_LIST_ID to 1001 on the parent QP_DISCOUNT_LISTS_INT sheet.
-
Set SOURCE_DISCOUNT_LIST_ID to 1001 on the child QP_DISCOUNT_LIST_ITEMS_INT sheet.
-
A discount list contains entities, such as items. The worksheet uses this reference to create a relationship between each child entity and the parent discount list.
The example includes items AS54888 and CN92777. Each of them reference list 1001. You can use 1001 or whatever numeric value you prefer. It doesn't have to be 1001, but you must use the same number across worksheets. For example, if you use 1001 on QP_DISCOUNT_LISTS_INT, then you must use 1001 on QP_DISCOUNT_LIST_ITEMS_INT.
Upload Your Data
-
Click the Instructions and CSV Generation tab, then click Generate CSV File.
-
In the dialog that displays, save the file as DiscountListInterface.zip.
-
Sign into Oracle Applications. Make sure you have the Import Price Lists privilege or the Import Approved Price Lists privilege.
-
Go to the Scheduled Processes work area, then run the Load Interface File for Import scheduled process to upload the zip file.
Parameter
Value
Import Process
Import Discount Lists
Data File
Click Upload a New File, browse to the DiscountListInterface.zip file that you saved earlier, then click OK.
-
Click Submit, then notice the process number that the dialog displays, such as 163199.
-
Click Actions > Refresh on the Overview page until you can see that the status for your scheduled process is Succeeded.
Import Your Data
-
Click Schedule New Process, then run the Import Discount Lists scheduled process.
Parameter
Description
Batch Name
Select the same value that you entered in the Batch Name column of sheet QP_DISCOUNT_LISTS_INT in the import template. For example, DL_IMPORT_1.
Commit Point
Optional. Specify at what point to commit data to the transaction tables while the scheduled process processes your records.
If you leave this parameter empty, then the scheduled process will automatically commit data after it processes 1,000 records, but you can specify when to do it. For example, if you specify 1 as the commit point, then the scheduled process will commit data after it processes one record. If you specify 50, then the scheduled process doesn't commit data until after it processes the 50th record.
Each discount list is one record.
Set the commit point to improve performance. Each commit requires a round trip to the database. Each round trip takes time and consumes computing resources. If you import 2,000 records and you set Commit Point to 1, that's 2,000 round trips, but if you set it to 100, that's only 20 round trips.
If you set a high commit point, then make sure your server has sufficient cache. The import uses the cache until the next commit point. For example, if you set Commit Point to 2,000, then the import will load data into the cache until it processes 2,000 records. If you set a high commit point and find that the import fails during testing because of a processing error or resources not available error, then reduce the commit point.
Number of Child Processes
Optional. Specify how many child scheduled processes to run for the items and rules that each discount list contains.
The scheduled process automatically creates the optimal number of child scheduled processes that it needs for each batch, but you can specify how many. For example, if you specify 16, then the scheduled process will create no more than 16 child processes. The child processes run in parallel with each other.
The typical range of values is 1 through 16.
The scheduled process typically uses 4, by default.
Assume you need to process 100 records. If you set Number of Child Processes to:
-
1. One child process will process all 100 records consecutively. It takes a lot longer.
-
10. Ten child processes will process 10 records each, and each of the 10 child processes run concurrently. Its a lot faster, but make sure you have sufficient computing power and the servers that you need to accommodate this parallel processing.
Adjust Commit Point and Number of Child Processes during testing until you find the optimal balance between speed and your investment in computing resources.
Batch Name is required. The other parameters are optional.
For important details, see Guidelines for Using Scheduled Processes in Order Management.
-
-
Click Submit, then notice the process number that the dialog displays.
The parent Import Discount Lists scheduled process creates child scheduled processes.Child Scheduled Process Description Import Discount List Headers and Access Sets Processes the headers and the access set for each discount list. Import Discount List Items and Rules Processes the items, pricing terms, and matrix rules for each discount list. Use the search result area of the Overview page to refresh the page and monitor your parent and child processes. Continue to refresh until the status for each process is Succeeded.
-
Click View Log to examine the log and verify the records that you imported.
Verify Your Import
-
Go to the Pricing Administration work area.
-
Click Tasks > Manage Discount Lists.
-
On the Manage Discount Lists page, search for the first list that you imported.
Attribute
Value
Name
DISCOUNT_LIST_1
-
Click DISCOUNT_LIST_1 in the Name column of the search results.
-
On the Edit Discount List page, verify that the header attributes match the values from your worksheet.
Attribute
Value
Status
Approved
Currency
USD
Business Unit
Vision Operations
-
Verify the item that you imported for the DISCOUNT_LIST_1 list.
-
On the Discount Lines tab, search for the item.
Attribute
Value
Name
AS54888
-
On the Discount Rules area, verify the rule that you imported.
Attribute
Value
Rule Name
AS54888-SR1
Rule Type
Simple
Rule Start Date
1/15/19 10:10 AM
Rule End Date
Empty
Price Type
One Time
Charge Type
Sale
Charge Subtype
Price
Price Periodicity
Empty
Adjustment Type
Discount Percent
Adjustment Basis
QP_AdjBasisforBaseListPrc
Adjustment Amount
5
-
Click Access Sets, then verify the access set that you imported.
Attribute
Value
Set Code
COMMON
Set Name
Common Set
-
-
Repeat these steps for the DISCOUNT_LIST_2 discount list.
Verify that the values for DISCOUNT_LIST_2 are the same as DISCOUNT_LIST_1 but with these differences.
-
Status is In Progress.
-
The item is CN92777.
-
The Discount Rules area doesn't contain a rule.
-
Troubleshoot
See: