Import Price Lists

Use the Price List Import Template to import price lists from your source system into Oracle Pricing.

This template contains a structure that the Oracle database requires.

It includes a tab for each database table, and it displays tabs in a specific sequence. It includes columns on each tab, each of these columns represents a column in the database table, and it specifies the data type that Oracle requires for each database column.

You:

• Must use the Excel template that Oracle provides.

• Convert your price list into a CSV (comma separate values) file that uses the same structure that the template uses.

• Use a scheduled process to upload data from the template into Pricing Administration.

Summary of the Steps

1. Prepare your price list.

2. Create the import file.

3. Upload your price list.

4. Import your price list.

Note

• Use this procedure to create a price list that includes child objects. You can also use the Pricing Administration work area to create a price list, but you must manually add each child object.

• The tools, guidelines, and procedures that you use are similar to importing sales orders. For details, see Overview of Importing Orders Into Order Management.

• For a demonstration, see Oracle Pricing Cloud Enhancements, Import or Update a Large Number of Price Lists. The demonstration starts at 08:11.

Oracle introduced PriceListsImportBatchTemplate.xlsm to replace PriceListImportTemplate.xlsm. We recommend that you use PriceListsImportBatchTemplate.xlsm instead of PriceListImportTemplate.xlsm.

• You can use the Update operation with PriceListsImportBatchTemplate.xlsm but not with PriceListImportTemplate.xlsm.
• PriceListsImportBatchTemplate.xlsm is faster, even if you are importing a single price list.
• Oracle might no longer support PriceListImportTemplate.xlsm in a future update.

For details, see Import Batches of Price Lists.

Prepare Your Price List

Convert your price list into a CSV (comma separate values) file that uses the same structure that the Price List Import Template uses.

Using the template helps to make sure that your converted price list uses the same structure that the Oracle database requires. This topic describes one way to convert your price list. The details of your conversion might require a different way. If you can't use the Price List Import Template for some reason, then consult Oracle resources about how to use an open interface when importing data.

Convert your price list into a CSV file.

1. Download the Price List Import template.

   • Go to File-Based Data Import for Oracle Supply Chain Management Cloud.

   • Expand Order Management, then click Price List.

   • In the Price List area, click PriceListImportTemplate.xlsm, then notice that the file automatically downloads to your local computer.

2. Use a spreadsheet editor that can read a CSV file, such as Excel, to open the Price List Import Template, then familiarize yourself with the structure it uses.

3. Use a data manipulation tool to structure your price list so it mirrors the structure that the Price List Import Template contains, then save your data to a CSV file.

   Use SQL (Structured Query Language), ODI (Oracle Data Integrator), or some other tool to convert your price list into a CSV file.

Guidelines for Converting Your Price List Into a CSV File

Create a CSV file that includes data for interface tables and columns when you do the conversion.

Here are some guidelines that you can use to make sure the structure that the CSV file contains mirrors the structure that the Price List Import Template contains.

• Include the same table names and column names in your CSV file that the templates uses.

• Sequence the tables in your CSV file in the same sequential order that the templates uses. The tab sequence that the template uses determines the sequential order.

• Include the same columns in each table in your CSV file that the templates uses, and arrange these columns in the same sequential order inside each table.

• Use the same data type for each column.

• Include data for all required columns. The Price List Import Template uses an asterisk ( * ) to indicate required columns. For example, the Calculation Method column on the PRICE_LIST_CHARGES_INT tab is required.

  Import might require some other columns depending on the conditions that apply to your usage.

  The Price List Import Template uses double asterisks ( ** ) to indicate 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 with UOM Code and UOM Name to indicate that they constitute one set. You must include a value for at least one of these columns.

View an example of the CSV structure.

1. Open the Price List Import Template in Microsoft Excel.

2. Click the PRICE_LISTS_INT tab.

3. Select File > Save As, then click Excel Workbook.

4. Save the file as a csv file, then use a text editor to examine the output.

Create the Import File

1. Prepare the Price List Import Template.

   • Use a spreadsheet editor that can read a CSV file, such as Excel, to open the Price List Import Template.

   • Delete the example data from the Price List Import Template.

     This template comes predefined with example data that helps you to determine the type of data you must include. For example, row five of the PRICE_LISTS_INT tab includes example data. Make sure you delete all example data from all tables in the spreadsheet, even from tables that you don't need.

2. Copy and paste your price list into each of the tables in the Price List Import Template.

   • Use a spreadsheet editor to open the CSV file that contains your price list.

   • Copy your line data to the clipboard.

   • In the Price List Import Template, click the PRICE_LISTS_INT tab.

   • Click cell A5, then paste your data.

   • Examine the results to make sure you correctly pasted the data.

     For example, make sure the Name column contains VARCHAR data, and that the Source Price List ID column contains NUMBER data.

   • Continue copying data for each table until you finish copying all your price lists into the Price List Import Template.

   • Don't modify the tab sequence in the template or the column sequence on each worksheet. Modifying the template in this way will cause errors.

   • Don't delete any tabs.

   Save your work after each copy.

3. Create the import file.

   • Click the Create CSV tab, then click Generate CSV File.

     If the Generate CSV File button isn't active, then click Developer in the menu bar, then click Macros. In the Macro dialog, select GenCSV, then click Run.

   • Wait for the macro in Excel to finish.

     When the macro finishes, Excel displays a dialog that you can use to save a zip file.

   • In the save dialog, select a location to save your zip file, then click Save.

     The macro creates a zip file that includes a separate file for each table that the template contains.

Upload Your Price List

1. Sign into Oracle Applications. Make sure you have the Import Price Lists privilege or the Import Approved Price Lists privilege.

2. Go to the Scheduled Processes work area.

3. On the Scheduled Process page, click Schedule New Process.

4. In the Schedule New Process dialog, set the value, then click OK.

   Attribute	Value
   Name	Load Interface File for Import

5. In the Process Details dialog, set the parameters.

   Parameter	Description
   Import Process	Select Import Price List.
   Data File	Do these steps. Click the down arrow in the Data File attribute. Scroll down, then click Upload a New File. In the Upload File dialog, click Select File. In your Windows Explorer window, locate and select the zip file that you created when you used the Price List Import Template, then click Open. In the Upload file dialog, click OK. In the Process Details dialog, make sure the Data File attribute displays the name of the zip before you continue.

   The Import Process parameter and the Data File parameter are required. For important details, see Guidelines for Using Scheduled Processes in Order Management.

6. Click Submit.

7. In the Confirmation dialog, note the value of the Process ID, click OK, then click Close.

8. Click Actions > Refresh.

9. Use the Process ID that you noted earlier to locate your scheduled process, then make sure the Status attribute for the process displays Succeeded.

   The Succeeded status indicates that the scheduled process successfully uploaded your price list.

   • If the upload fails on any row, then the status displays Error.

   • If the Search Results list doesn't display your process, then click Refresh until it does.

10. Correct errors, if necessary.

    • If the scheduled process ends in an error, then click the Error status in the Search Results list for your scheduled process, and examine the log and output files to get details about the data that caused the error.

    • Use Excel to open the Price List Import Template that contains your price list, then correct the price list that's causing the error.

    • In the Price List Import Template, click Generate CSV File, then run the scheduled process again.

    Repeat this step until the scheduled process successfully uploads your price list.

Import Your Price List

1. Run the Import Price List scheduled process. In the Process Details dialog, set the parameters.

   Parameter	Description
   Price List Name	Enter the name of the price list.
   Import Request ID	Enter the identification number that you noted in step 8 of the Upload Your Price List section of this topic.
   Commit Point	Accept the default value of 100, or enter an integer that sets the maximum number of records to import at one time. For example, if Commit Point is 100, then the import will import 100 records at a time.
   Number of Child Processes	Enter an integer that sets the maximum number of child processes to run during the import. If the number of records to import is: Less than one hundred. The import runs two child processes, by default. More than one hundred. The import runs four child processes, by default.

   The Price List Name parameter and the Import Request ID parameter are required.

2. Make sure the Status for the Import Price List scheduled process that you submitted is Succeeded.

   The Succeeded status indicates that the scheduled process successfully imported your price list. If the import fails on any row, then the status displays Error.

3. Verify your import.

   • Sign into Oracle Pricing with administrative privileges.

   • Go to the Pricing Administration work area, then click Tasks > Manage Price Lists.

   • Search for the price list that you imported.

   • Verify that the Manage Price Lists page displays the price lists you imported.

Interface Tables That the Price List Import Template Uses

The Price List Import Template includes a tab for each of the interface tables.

Table Name	The Table Must Contain Data About The
QP_PRICE_LISTS_INT	Price list header
QP_PRICE_LIST_SETS_INT	Access set
QP_PRICE_LIST_ITEMS_INT	Item line
QP_PRICE_LIST_CHARGES_INT	Pricing charges defined for the item.
QP_TIER_HEADERS_INT	Tier header defined for the charge.
QP_TIER_LINES_INT	Tier lines defined for the tier header.
QP_PL_COMP_ITEMS_INT	Component item line.
QP_MATRIX_DIMENSIONS_INT	Attribute matrix defined for the charge.
QP_MATRIX_RULES_INT	Attribute matrix rules defined for the matrix.
QP_PL_COVERED_ITEMS_INT	Covered item.

Troubleshoot

See:

• Troubleshoot Importing Price Lists
• The Import section in Troubleshoot Your Pricing Setups