QP Price List Upload

➜ Back to Library
XMLXML import file

Description

QP Price List Upload creates and updates Oracle Advanced Pricing standard price lists from Excel – the price list header with its item and item category prices, price breaks, line pricing attributes, header qualifiers and secondary price list assignments. Individual lines, price breaks, pricing attributes, qualifiers and secondary price list assignments can also be deleted.

When to use it

  • Load new standard price lists with many item or item category prices in one pass.
  • Mass-update list prices, application methods or effective dates on existing price lists.
  • Add or maintain price breaks (quantity or value based pricing tiers) across many lines.
  • Attach header qualifiers in bulk, including copying a whole qualifier group onto a price list.
  • Assign secondary price lists to many price lists at once.
  • Introduce new prices from a future date while end-dating the prices they replace.

Before you start

  • Use a responsibility with access to Blitz Report whose profile options QP: Pricing Transaction Entity and QP: Source System Code match the price lists you maintain (for example Order Fulfillment and Oracle Pricing), and with access to the operating units of non-global price lists.
  • The items, item categories, units of measure, formulas, qualifier groups and secondary price lists you reference already exist.
  • Currency, payment terms, freight terms and ship methods used on the header are set up and enabled.

Step 1 – Set the parameters

Open QP Price List Upload in Blitz Report and set the parameters:

ParameterMeaning
Upload ModeCreate opens an empty sheet; Create, Update (default) downloads existing price lists for editing and lets you add new ones.
End Date Matching List Lines?Yes end-dates the active line with the same product and unit of measure when a new line is created, on the day before the new line’s start date. Downloaded lines then come down with Action Create, so you can use the current prices as the basis for the new ones.
Price List / Price List LikeThe price list(s) to download, or all price lists whose name starts with this text.
Product Attribute / Product Value (Item/Item Cat) / Product Value (Other)Downloads only lines for this product, for example a specific item number or item category.
Item Category Set / Item CategoryDownloads the lines of all items assigned to this item category.
Effective DateDownloads the lines active on this date (default today).
Effective Date From / Effective Date ToDownloads the lines active at any time within this date range.
Download Price List LinesYes (default) includes the price list lines and their price breaks; clear it to download the headers only.
Line TypeDownloads only lines of this type, for example Price List Line or Price Break Header.
Download Pricing Attributes, Download Qualifiers, Download Secondary Price ListsYes also downloads the lines’ pricing attributes, the header qualifiers or the secondary price list assignments, each on separate rows. Download Pricing Attributes defaults to the End Date Matching List Lines? value.

Enter a price list or product parameter so the download contains only the prices you want to work on.

Blitz Report run screen for QP Price List Upload with the Default template, Upload Mode Create, Update and Price List Counter Sales Price LIst

Step 2 – Run to download the Excel file

Click Run. The Excel file downloads and opens with one row per price list line and further rows for price breaks, pricing attributes, qualifiers and secondary price lists when they are included. In Create mode the sheet is empty.

Excel file with the price list line of Counter Sales Price LIst for item AS54888 at a list price of 1920

Step 3 – Enter or change the price lists

Change a downloaded row’s columns, such as Line Value (the list price), Line Application Method, Line Start Date or Line End Date, or header columns such as Description, Effective To or Payment Term. Header columns changed on one row apply to the whole price list.

To add a line to a price list, add a row with the header columns of the price list (Name, Currency, Global, or Operating Unit for a non-global list) and the line columns: Line Product Context, Line Product Attribute, Line Product Value, Line Product UOM, Line Type, Line Application Method and Line Value (or a Line Dynamic Formula or Line Static Formula), and optionally a Line Start Date. A row with a new price list name creates the price list.

  • Line matching: rows with the same product, unit of measure and start date belong to the same line. If several lines share these values but have different pricing attributes, give each line its own Line No; the Line No is only used to group the rows and is not stored in Oracle.
  • Price breaks: use Line Type Price Break Header with a Line Price Break Type, and repeat the line on one row per break with Price Break Value From / To and Price Break Price (or Price Break Formula).
  • Pricing attributes: enter Pricing Attribute Context, Pricing Attribute, Pricing Attribute Operator and Pricing Attribute Value From (Value To only with the BETWEEN operator).
  • Qualifiers: enter Qualifier Grouping Number, Context, Attribute, Operator and Value From. To copy all qualifiers of a qualifier group, select only the Qualifier Group on a row without other qualifier details; to copy single qualifiers, select the Qualifier Group Qualifier ID on one row per qualifier.
  • Secondary price lists: enter the Secondary Price List and its precedence.
  • Deleting: set Delete Line, Delete Price Break, Delete Pricing Attribute, Delete Qualifier or Delete Secondary Price List to Yes to delete that record only. The price list itself is not deleted; make it inactive or end-date it instead.

A header, a line, a price break, a pricing attribute, a qualifier and a secondary price list can all be entered on the same row.

Excel file with the Line Value of item AS54888 changed to 1950, marked Update, and a new line for item AS92689 at 2450 starting 6-Oct-26, marked Create

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and the upload’s own rules (for example a complete line, price break or qualifier, Global and Operating Unit consistency, price break columns only on Price Break Header lines) and saves the file. Correct any rows it flags before uploading.

Both edited rows show status Valid after Validate and Save

Step 5 – Upload the file

In Blitz Report click Upload and select the saved file. This submits the upload request, which creates or updates the price lists and their lines and child records through the Oracle Advanced Pricing price list API.

File Upload page with the saved Excel file selected for upload

Step 6 – Review the result report

When the request completes, a result report opens listing every uploaded row with its status and a message showing what was done, for example Line: Updated, Line: Created, Price Break: Created or List Qual: Created. For new lines, the Line No column shows the line ID created in Oracle.

Result report showing Line: Updated for item AS54888 and Line: Created for item AS92689 with its new line ID, both with status Success

What’s produced

  • Standard price lists created or updated, with their lines, price breaks, pricing attributes, qualifiers and secondary price list assignments.
  • Active matching lines end-dated when End Date Matching List Lines? is Yes.
  • A result report listing every row with a status (success or error) and a message.

Common questions

How does the upload decide between create and update?
Downloaded rows update the price list and line they were downloaded from. Other rows are matched on the price list name and, for lines, on product, unit of measure, start date, Line No and pricing attributes. A match is updated; otherwise a new record is created.

What is the Line No column for?
Downloaded rows show the line ID. On new rows, Line No only separates lines that share the same product, unit of measure and start date but have different pricing attributes. It is not stored in Oracle.

How do I introduce new prices from a date?
Set End Date Matching List Lines? to Yes and download the current lines: they come down with Action Create. Enter the new Line Value and a Line Start Date; on upload the new lines are created and the active matching lines are end-dated on the day before.

Can I delete a whole price list?
No. Lines, price breaks, pricing attributes, qualifiers and secondary price list assignments can be deleted; the price list itself can be made inactive or end-dated.

Does this upload maintain modifiers or GSA price lists?
No. Use the QP Modifier Upload for modifier lists and the QP GSA Pricing Upload for GSA pricing.

Can I maintain intercompany transfer price lists?
Yes. Run the upload from an Inventory responsibility. It then works with the pricing transaction entity Intercompany Transaction and the source system INV, and qualifiers can use the contexts Intercompany Invoicing and Inter Organization Transfer.

Troubleshooting

MessageCauseWhat to do
A List Header with this Name already exists with a different List Type, PTE Code, or Source System CodeThe name is used by a modifier list or by a price list of another pricing transaction entity or source system.Use a different name, or a responsibility whose QP profile options match the existing list.
Operating Unit must be specified for non Global Price ListsGlobal is No and the Operating Unit is empty.Enter the Operating Unit, or set Global to Yes.
Operating Unit can not be specified gor Global Price ListsGlobal is Yes and an Operating Unit is entered.Clear the Operating Unit, or set Global to No.
MOAC Security is not enabled, only Global Price Lists are supportedPricing security is off, so operating unit specific price lists are not available.Set Global to Yes.
Product Context, Product Attribute, Product Attribute Value, UOM and Line Type are required on Price List LinesAn incomplete line.Complete the line columns, or clear them.
Line Value or Dynamic Formula or Static Formula is requiredA Price List Line without a price.Enter the Line Value or a formula.
Line Product Value is invalidThe product value does not exist for the Line Product Attribute.Pick the value from the list.
Price Break columns are not applicable to standard Price List LinesPrice break values on a line that is not a Price Break Header.Set Line Type to Price Break Header, or clear the price break columns.
Price Break Value From/To is required on Price Break LinesA price break row without its range.Enter Price Break Value From and Value To.
Qualifier Grouping Number, Context, Attribute, Operator and Value From are required when specifying a qualifierAn incomplete qualifier.Complete the qualifier columns, or clear them.
Cannot override active Price List Line. Active Line Start Date (…) is equal to or later than the new Price List Line Start Date (…)End Date Matching List Lines? is Yes and the active line does not start before the new line’s start date.Use a later Line Start Date for the new line.
Cannot uniquely identify the Price List Line to be end datedSeveral active lines match the new line.Download the active lines and end-date the right one by updating its Line End Date.
Could not uniquely identify the Price List Line to UpdateSeveral lines match the row’s product, unit of measure and start date.Download the price list and update the line from its downloaded row, or use Line No.
Price List Line cannot be created when Delete Line Flag is set to YesDelete Line is Yes on a line that does not exist (the other Delete columns give similar messages).Check the line values, or clear the Delete column.
Price List Header cannot be updated due to Function Security settingsYour user is not allowed to update this price list.Ask your pricing administrator for update access to the price list.
Error in prior row for this list lineAn earlier row of the same line failed.Fix the first failing row of the line and upload again.
Upload Failed: followed by an Oracle messageOracle Advanced Pricing rejected the record.Correct the value named in the message and upload again.

SQL 788 lines

Parameters 17