PeopleSoft Enterprise Order to Cash Common Information 9.1 PeopleBook

Updating List Prices

This chapter provides an overview of mass price maintenance, lists common elements, and discusses how to:

Run the Product Price Extract process (OM_PROD_COST).
Update product prices.
Set up price maintenance.

Understanding Mass Price Maintenance

Perform these steps to update product prices using mass price maintenance.

Note. Product price maintenance requires a windows client running Microsoft Excel.

The following diagram illustrates the process for performing mass product price updates:

Product price mass maintenance process

On the Product Price Extract page, create a request for data by specifying the exact search criteria and then submit the job for processing.

The application extracts the selected data and stores it in an extract record.
Launch the Order Management Product Price Microsoft Excel file (OMProductPrice.XLS).

Note. The Excel spreadsheet OMProductPrice.XLS must be able to run macros in order to function properly. If the macro security in Microsoft Excel is set to High, the spreadsheet will not operate properly. Verify that the security setting is at Medium or below by selecting Tools, Macro, Security and selecting Medium or Low as the Macro security setting for the machine.
If this is your first time working with the OMProductPrice.XLS file, you will need to set the connection properties by clicking the Connection Properties button.
Request the extracted data by selecting the Download Prices button and supplying logon data.

Microsoft Excel loads and formats the received data.
Modify the list prices as necessary using the Price Maintenance dialog box or by directly modifying the spreadsheet.

Modify the data as follows:
- Adjust the price for all items in the spreadsheet by amount or percent. When choosing which rows to modify, you can specify criteria such as current dated rows, future dated rows, or all rows. In addition, you can apply the changes to visible, hidden, or all rows, as needed. You can also specify the rounding decimals for the newly created prices.
- Adjust the start and end dates for the product price. When choosing which rows to modify, you can specify criteria such as current dated rows, future dated rows, or all rows. In addition, you can apply the changes to visible, hidden, or all rows, as needed.
- Set the Protect flag for all items in the spreadsheet. When choosing which rows to modify, you can specify criteria such as current dated rows, future dated rows, or all rows. In addition, you can apply the changes to visible, hidden, or all rows, as needed.
Upload the changes made to the product prices to PeopleSoft by clicking the Upload Prices button and providing logon data.

Confirmation of the update is sent back to Microsoft Excel. For each row that successfully applies, the status column turns green and reads Uploaded. If the a row in the spreadsheet does not post properly, the column turns red and reads Failed.
Modify any rows that failed to post and resubmit the rows. To do so, click the Upload Prices button again.

Note. Keep in mind that you can alter prices on the Product Price page between the export and import of pricing data. Those changes will be overridden when new pricing information is imported from Microsoft Excel.

Common Elements Used in This Chapter

Apply To

Apply changes to the Visible data on the spreadsheet (if you entered filtering criteria), to the Hidden rows (the complementary records that are hidden if you applied a filter criteria), or to All records in the spreadsheet.

Note. You can view only 30–50 rows of the spreadsheet at one time. Visible refers to rows listed on the spreadsheet and not rows that are visible on the current screen. If you download 1,000 list prices and apply a filter that selects 300 of those 1,000 records, then 300 records will be visible.

Running the Product Price Extract Process

This section provides an overview of product prices and discusses how to run the product price extract process.

Note. The process must be run before product list prices can be updated using the list price maintenance functionality.

Understanding Product Prices

Depending on the cost method used for the item cost of the product, the costs are retrieved from separate tables. This table outlines where item costs are stored. If the item is noncosted, then any product ID associated with that item is not downloaded.

Cost Method	Table Where Data is Stored
Actual	CM_ACTUAL_COST
Periodic	CM_PERDAVG_COST
Perpetual	CM_PERPAVG_COST
Standard	CM_PRODCOST
Retroactive Perpetual Average	CM_PERRAVG_COST

The Product Price Extract stores the product list price and cost information in the OM_PROD_COST table. The extract performs a unit of measure and a currency conversion. Cost data is stored in a standard unit of measure for that item. The products can have ordering units of measure that are different than the stocking units of measure. Cost data is adjusted according to the unit of measure conversion rate. You can determine the orderable unit of measure/conversion rate of an item by querying view INV_ORDR_UOM_VW, or you can view the item Unit of Measure page. In addition, products can be ordered in multiple currencies different than that of the item's cost, which is stored in the base currency of the PeopleSoft Inventory Business Unit. To have cost data that is meaningful, it is converted to the product list price currency. The product list price currency is stored in the PROD_PRICE table, or you can view it on the Product Price page.

For a product ID and its associated costs to be downloaded, these relationships must be present:

The product ID must have a product list price specified, and the price must fall within the Product Price As of Date supplied on the Product Price Extract page.
If the product ID is associated with an item, then that item must have a cost specified and the cost must fall within the Product Cost Effective Date supplied on the Product Price Extract page.

Note. Noncosted products are downloaded with a zero cost.
If the product ID is not associated with an item, then the product ID must have a product price with both a list price and unit cost specified, and it must fall within the Product Price As of Date supplied in the Product Price Extract page.

Example: Effective-Dated Price Records

Product 10001 has five active price records with these effective dates:

1/01/2001
1/01/2002
7/01/2002
1/01/2004
7/01/2004

The current date is used as the default. The system exports the price with the maximum effective date that is less than or equal to the Product Price As Of Date. In this case, it would be price number 3 (7/01/2002). If you want to export price number 4 (1/01/2004), you must change the Product Price As Of Date to any value between 1/012004 and 6/30/2004.

Note. You will never be able to export prices in the first and second scenario because the selected date must be greater than or equal to the current date.

Page Used to Run the Product Price Extract Process

Page Name	Definition Name	Navigation	Usage
Product Price Extract	OM_PPRC_RCNTL_PNL	Products, Request Processes, Product Price Extract	Establish the selection criteria and the effective dates for exporting price data, and the currency and unit of measure conversions. These values are needed to support product prices in multiple currencies and orderable units of measure when using price maintenance.

Running the Product Price Extract Process

Access the Product Price Extract page (Products, Request Processes, Product Price Extract).

All Inventory BUs (all Inventory business units)	Choose to update the prices for products in all warehouses. The system downloads price information for non-inventory products, such as services and product kits, in addition to those products stocked in warehouses.
Select Inventory BU (select Inventory business unit)	If you choose to run the process for individual business units, enter or click the Search button to select them.
Select Pricing Product Group	Use to further restrict the data downloaded by specifying a product group after selecting the option. The only groups available are those designated as Pricing product groups. Select All Products to include products assigned to product groups as well as those that are not. Note. Products may belong to multiple pricing product groups. You can only update the price of the product as part of a single product group, that is, the one that appears in bold on the spreadsheet. The system uses the group you designate as the primary pricing product group on the Product Group page. If you do not designate a primary group among multiple groups, then the system uses the first one it encounters.
Product Cost Effective Date	Select the date to use as the effective date for the new updated prices. The comparison of product cost to product price is a critical consideration when you update price information. If you are updating prices that will take effect at a future date, they should be compared to commensurate cost information. Consider the following example. The current date is 8/08/03. You want to download the current list prices as the basis for the calculation of new prices that will become effective on 1/01/04. To determine what those prices will be, you want to compare them to the product cost as of 1/01/03. You will enter a Product Cost Effective Date of 1/01/03. The system downloads current price information along with the product cost in effect on 1/01/03. Note. The system uploads rows with changes in the price, cost (non-inventory products only), MSRP (Manufacturers Suggested Retail Price), and effective date. Because the minimum New Eff. Date (new effective date) allowed on the price maintenance spreadsheet is the current date plus one day, selecting the current date as the Product Cost Effective date requires a new effective date for any rows in the spreadsheet that you want uploaded.
Product Price As Of Date	The current date is the default. You must select a date that is greater than or equal to the current date. Only prices with a status of Active may be exported. You can maintain several sets of list prices in the system keyed by effective date. Note. List prices will not be downloaded if an item cost and its associated product price record do not exist. In the case of a nonstock item, only a product price record must exist. The most common reasons for a nonexistent record are incorrect selection criteria or the record was not established in the database.
Rate Type	Established on the Market Rates Type page.
Currency Effective Date	The current date is the default. This date will determine what currency values to use when converting cost prices from their default value to the currency of the product list prices.
Run	Click the Run button after establishing selection criteria. After the cost extract has run successfully, product price information can be updated using the list price maintenance spreadsheet in Microsoft Excel.

Updating Product Prices

This section discuss how to:

Update list price and cost information in Microsoft Excel.
Filter data.
Use mass maintenance.
Reset list price data.
Customize display options.
Print list price changes.
Display list price data in a chart format.
Upload product price data.

Updating List Price and Cost Information in Microsoft Excel

The Calculator button acts directly on the data in the spreadsheet. The other buttons open dialog boxes wherein you make additional selections.

You are limited to one list price maintenance spreadsheet on your hard drive. If you have an existing spreadsheet from a previous maintenance session, then the system will prompt you to override it with the current download or not.

You can access the price maintenance spreadsheet you are working on by opening it directly in Microsoft Excel. Downloaded information appears in Microsoft Excel in three sections on the spreadsheet delineated by different column colors:

Key fields that identify the product appear in gray columns.
Fields that can be edited, such as New Cost, New Price, New MSRP, and New Eff. Date (new effective date), appear in light green columns. All other cells on the spreadsheet are locked.
Current price and cost information appears in beige columns.

Click the Download Prices button to view product information in the spreadsheet.

Click the Calculator button to toggle between the two views using the Price Maintenance toolbar. Differences are not uploaded, but the view affects the mass maintenance changes that are available when changes are based on the differences in values.

Key Field Columns

Prod ID (product ID) and Prd Grp (product group)

A product may be included in more than one product group. If this is the case, and you have downloaded data that encompasses more than one group, the product will appear multiple times on the spreadsheet. The only data changes that are uploaded are those made in rows where the key fields appear in bold. The system emphasizes the group you designate as the primary pricing product group on the Product Group page. If you do not designate a primary group among multiple groups, the system uses the first one it encounters.

Inv BU (inventory business unit)

If the product is not stocked in a warehouse (a service or product kit, for example), the value in the column will be Non Inv (non-inventory) and the Item ID field will be blank.

Editable Columns

These editable columns are affected by pricing, costing, and effective date changes. The data in these columns is uploaded to update PeopleSoft tables. You can edit each cell individually or apply Mass Maintenance.

New Cost	The spreadsheet displays only information for inventory products that are actual, periodic, perpetual, standard-costed, retroactive perpetual average, and non-inventory products. This column does not contain a value for other products. The system retrieves product costs for standard-costed stocked products from PeopleSoft Inventory, and the cost cannot be changed here. Unit costs for non-stocked products are established on the Product Price page in PeopleSoft Order Management, where you can update the cost. The data that appears preceding any changes reflects the costs as of the Product Cost Effective Date selected on the Product Price Maintenance page. Note. Stocked products that are not costed in PeopleSoft Inventory are not downloaded.
New Price	Matches the Active Price values until you make changes. These prices are active for the Product Price As Of Date selected in Product Price Maintenance changes.
New Eff. Date (new effective date)	Matches the Product Cost Effective Date selected on the Product Price Maintenance page until you make changes. To prevent inconsistent pricing for the current date, the minimum new effective date value allowed for uploaded rows is the current date plus one day. Note. If you selected the current date as the Product Cost Effective date on the Product Price Maintenance page, then change the value of New Eff. Date here for any rows you want uploaded.
New Diff. (new difference) and Diff. abs. (difference percent)	View the difference between the New Cost and the New Price as an absolute amount (New Diff. = New Price-New Cost) or as a percentage (New Diff. = (price – cost) / cost * 100).

Current Price and Cost Information Columns

The Active Cost, Active Price, Diff % (difference percent), MSRP (Manufacturer's Suggested Retail Price) and Eff. Date (effective date) reflect the status of the data at download. They are available for comparison, but you cannot change the values in these columns. The toggle between the two differences affects both the New Diff. column and the Diff % column.

Filtering Data

The spreadsheet provides auto filtering for all columns, which enables you to select criteria in one or more columns that limits the product rows that will appear to a subset of those downloaded. After selecting a subset of the data, you can apply changes just to that subset. This capability enables you to apply different adjustments to separate subsets of the downloaded data.

Click the arrow in the heading row for a column and select a value from the drop-down list to filter the parameters. When filtering criteria are in effect, the color of the arrow changes from black to blue.

In addition to the values in the column, you can apply five additional options to each column:

(All): After applying filters, this value resets the column to display all the data from the download.
Top 10: This standard Microsoft Excel filtering option is not used in price maintenance.
Custom: Set a particular range for column values.
Blanks: Displays only those rows without a value.
Non-Blanks: Displays only those rows with a value.

Using Mass Maintenance

Access the Price Maintenance dialog box to apply changes to multiple products in the editable columns:

Click the Mass Maintenance button to access the Mass Maintenance dialog box.

Type

The selection options and the label of the first field change depending on the type of change you are making. Options are unavailable when the selection criteria does not apply. You can make a Percent change, an Amount change, or a change to the Eff. Date.

Based On

You can base a change on the: List Price (New Price column), Differences between the cost and list price (New Diff. column), Non-Inv Cost (New Cost column for non-inventory products only), and MSRP (New MSRP column).

Note. The display mode for differences on the spreadsheet affects the selection criteria for changes. If differences appear as percentages, the Type must be Percent. If differences appear as absolute amounts, the Type must be Amount.

Preview

Click the Preview button to view an example of the current change before you apply it to the data. Keep in mind that prices are not limited to positive values.

Number of decimals for rounding

When a pricing change is applied, you can alter the number of decimals that the system uses to round to the new price or cost by changing the value in this field. Values are 0 to 4. You can round to a different number of decimals without changing any other data by keeping the default of 0 in the first field in the dialog box and altering the Number of decimals for rounding field.

Note. The default value that appears in the Number of decimals for rounding field is determined by the settings on the computer. To change the default, select Control Page, Regional Settings, Currency.

Resetting List Price Data

Access the Reset Data dialog box to reset the data to the original data from the download:

Click the Reset Data button on the Price Maintenance toolbar to reset the columns to the original settings from the download. You can apply resets selectively using the filtering function.

New Prices to Active Prices

Resets values in the New Price column to those that were downloaded. These are the same values as those in the Active Price column.

New Prices to New Cost

Resets values in the New price column to match the values in the New Cost column.

Effective Dates

Resets values in New Eff. Date column to those that were downloaded.

New Cost to Orig. Value (reset new cost to original value)

Resets the values in the New Cost column for non-inventory products to those that were downloaded. These values might not match those in the Active cost column if the Product Price As Of Date is different from the Product Cost Effective Date.

New MSRP to Active MSRP

Resets values in the New MSRP column to those that were downloaded. These values are the same as those in the Active MSRP column.

Customizing Display Options

Access the Customize Sheet dialog box:

Click the Customize Sheet button.

Current Settings

Select each column from the drop-down list box, and enter a New Heading and or a New Width if you prefer different headings or column widths.

Adjust Column Width to Best Fit

Select this option to set column widths to display the values entered and override any manually entered column widths.

Reset all Data Filters

Select this option to display all the downloaded data if you previously restricted the display of spreadsheet rows by using the filtering option on one or more columns.

Display primary pricing rows only

Select this option to view only the bold rows on the spreadsheet. To view all rows, deselect this option.

Display marked for upload rows only

Select this option to view only changed rows. To view all rows, deselect this option.

Redisplay this page when finished

Select this option if you change the heading or width of columns.

Apply

Click the Apply button to apply changes to each column one at a time.

Printing List Price Changes

Use this page to print the spreadsheet. The system includes auto-filter settings and prints only changed rows.

Click the Print button to print the spreadsheet data.

Displaying List Price Data in Chart Format

Click the View Charts button to create graphical charts of the list prices.

To change the display properties of the chart, use the standard Microsoft Excel menu Format options.

Click the View Charts button to create charts based on prices, differences, or both.

Uploading Product Pricing Data from Microsoft Excel

The system uploads only rows with changes (where the key fields appear in bold) in the New Price, New Cost (non-inventory products only), New MSRP, and New Eff. Date columns.

If you changed the new effective date, the system inserts a new row in the product price table. This action is comparable to entering a new row in the product price table for the price by inventory business unit with a new effective date. When the effective date of the spreadsheet row is unchanged, but you have altered the price, the current row in the product price table is updated.

Note. Keep in mind that at download the New Eff. Date column is populated with the Product Cost Effective Date you selected on the Product Price Maintenance page. Because the minimum new effective date allowed for upload is the current date plus one day, selecting the current date as the Product Cost Effective Date requires that you change the new effective date for all rows you want to upload.

Uploading Modified Product Price Data

Click the Upload Prices button on the price maintenance toolbar.

The prices are uploaded into the PeopleSoft system. If the row is applied successfully, the status in the row Status column changes to Uploaded. If the row is not applied successfully, the status in the Status column changes to Failed. Correct the errors for the row and click the Upload Prices button.

Setting Up Price Maintenance

This section discusses:

PeopleSoft Order Management workstation setup requirements.
How to configure connection properties.
List Price Maintenance user security requirements.

Workstation Setup Requirements

You must install several supporting applications to enable Microsoft Excel and the PeopleSoft system to communicate with one another. Additionally, these minimum system requirements must be met for the download and upload of data between Excel and the PeopleSoft system to function properly:

IBM Compatible PC running Windows Operating System.
Microsoft Office 2000 - Excel.
Microsoft XML Parser (MSXML.dll) Version 3.0 SP2 or greater.
Visual Basic 6.0 SP5: Run-Time Redistribution Pack.
Microsoft Office 9.0 Object Library or greater.

The system administrator should confirm these settings.

PeopleSoft Connection Configuration

Before downloading any data from a PeopleSoft database, you must enter connection information for the PeopleSoft system with which they want to communicate. If this spreadsheet is used on only one system, then you only need to complete this configuration step once. The information provided is saved to the spreadsheet for further connection attempts. We suggest that the system administrator fill in this information on a master copy of the OMProductPrice.xls file and distribute the modified spreadsheet to all users.

	Click the Connection Properties button to set up the OMProductPrice.xls file to communicate with PeopleSoft Order Management.
Web Server Machine Name	Enter the name of the PeopleSoft web server.
Protocol	Enter the web server protocol, either http or https.
HTTP Port	Enter the port the web server is using.
Portal	Enter the name of the portal to be used.
Site Name	Enter the site name that was defined when PeopleSoft Pure Internet Architecture was set up.
Node Name	Enter the node name of the PeopleSoft system.
Language Code	Enter the PeopleSoft Language Code to specify the data download language.
Chunking Factor	If data is uploaded and processed by a component interface, enter the number of transactions sent at a time to be processed by the component interface.

Security Requirements

The modified product price data is downloaded and uploaded using the OM_SOAPTOCIXML link (Security, Permission & Roles, Permission Lists, WEBLIB_OM:SOAPTOCI.FieldFormula.iScript_Connect). You must have permission for this web library if you plan to use the OMProductPrice.xls file to perform mass product price maintenance for Order Management.

See Enterprise 8.50 PeopleBook: Security Administration, "Setting Up Permission Lists."

	Click the Download Prices button to view product information in the spreadsheet.
	Click the Calculator button to toggle between the two views using the Price Maintenance toolbar. Differences are not uploaded, but the view affects the mass maintenance changes that are available when changes are based on the differences in values.

	Click the Mass Maintenance button to access the Mass Maintenance dialog box.
Type	The selection options and the label of the first field change depending on the type of change you are making. Options are unavailable when the selection criteria does not apply. You can make a Percent change, an Amount change, or a change to the Eff. Date.
Based On	You can base a change on the: List Price (New Price column), Differences between the cost and list price (New Diff. column), Non-Inv Cost (New Cost column for non-inventory products only), and MSRP (New MSRP column). Note. The display mode for differences on the spreadsheet affects the selection criteria for changes. If differences appear as percentages, the Type must be Percent. If differences appear as absolute amounts, the Type must be Amount.
Preview	Click the Preview button to view an example of the current change before you apply it to the data. Keep in mind that prices are not limited to positive values.
Number of decimals for rounding	When a pricing change is applied, you can alter the number of decimals that the system uses to round to the new price or cost by changing the value in this field. Values are 0 to 4. You can round to a different number of decimals without changing any other data by keeping the default of 0 in the first field in the dialog box and altering the Number of decimals for rounding field. Note. The default value that appears in the Number of decimals for rounding field is determined by the settings on the computer. To change the default, select Control Page, Regional Settings, Currency.

	Click the Reset Data button on the Price Maintenance toolbar to reset the columns to the original settings from the download. You can apply resets selectively using the filtering function.
New Prices to Active Prices	Resets values in the New Price column to those that were downloaded. These are the same values as those in the Active Price column.
New Prices to New Cost	Resets values in the New price column to match the values in the New Cost column.
Effective Dates	Resets values in New Eff. Date column to those that were downloaded.
New Cost to Orig. Value (reset new cost to original value)	Resets the values in the New Cost column for non-inventory products to those that were downloaded. These values might not match those in the Active cost column if the Product Price As Of Date is different from the Product Cost Effective Date.
New MSRP to Active MSRP	Resets values in the New MSRP column to those that were downloaded. These values are the same as those in the Active MSRP column.

	Click the Customize Sheet button.
Current Settings	Select each column from the drop-down list box, and enter a New Heading and or a New Width if you prefer different headings or column widths.
Adjust Column Width to Best Fit	Select this option to set column widths to display the values entered and override any manually entered column widths.
Reset all Data Filters	Select this option to display all the downloaded data if you previously restricted the display of spreadsheet rows by using the filtering option on one or more columns.
Display primary pricing rows only	Select this option to view only the bold rows on the spreadsheet. To view all rows, deselect this option.
Display marked for upload rows only	Select this option to view only changed rows. To view all rows, deselect this option.
Redisplay this page when finished	Select this option if you change the heading or width of columns.
Apply	Click the Apply button to apply changes to each column one at a time.