Area Price Updater
Introduction
The Area Price Updater provides a streamlined process to update Area Pricing aimed at eliminating the need for manually keying of Area Pricing or time spent preparing data externally for import.
The Area Price Updater contains the following features:
User defined Worksheets to determine the nature and scope of the update required.
User defined Area and Item Criteria Ranges per worksheet to determine to which Areas and Items the update will apply.
The ability to Copy Areas and Items from other worksheets to save time creating new worksheets before generating worksheet lines.
A Generation process to quickly carry out calculations based on user defined worksheet parameters.
A Worksheet Lines screen that enables the results of the generation process to be reviewed, check for any missing prices, manually update specific item prices for which a formula based approach is not suitable or to determine if the generation process needs to be run again.
A Worksheet Approval process (Generated, Ready, Approved, Completed) with optional comments for each status per worksheet. Completed worksheets can be re-opened.
Full integration to other BusinessCraft Module, particularly Inventory, Area Price Maintenance and the Inventory Report Writer.
Process Flow
It is important to understanding the Area Price Updater Process Flow before covering the functionality in detail. The typical steps involved are as follows:
| Step | Status |
| Create worksheet defining default parameters if required | New |
| Add Areas and Area Price Parameters to be included in the update | New |
| Add Items to be updated (Copy Items/Copy Worksheet or manually) | Items Copied |
| Generate Area Price Update | Generated |
| Review results of Generation process, edit or re-generate as required | Generated |
| When satisfied with generate results, change status to Ready | Ready |
| Check Generated results and Approve or Reject | Approved/Rejected |
| If Rejected, revise as necessary, reset to Ready and submit for Approval | Ready |
| If Approved, then Publish | Completed |
| At any point during the process other than if Completed, the Worksheet can be cancelled | Cancelled |
| If Worksheet is Completed and Update Status is activated an option is provided to re-open the Worksheet. | Generated |
Area Price Book Worksheet
The first step in using the Area Price Updater is to create an Area Price Book Worksheet. Select the Inventory > Price Maintenance > Area Price Updater menu option:
| Worksheet Id | A system generated sequence number used to identify the worksheet. When adding a new worksheet, press Enter/Return and the next number will be assigned. |
| Description | A user defined description of the purpose of the worksheet. |
| Old Effective Date | The effective date for existing Area Pricing that will be updated by this worksheet. |
| New Effective Date | The effective date for the Area Pricing that will be created by this worksheet. |
| Comments | Additional user defined comments describing the nature/purpose for this worksheet. |
| Creator | The BusinessCraft user who created the worksheet. |
| Approver | The BusinessCraft user responsible for approving the worksheet. |
Defaults
The defaults area in the Area Price Book Worksheet provides the ability to define the default parameters for the Worksheet Lines which are in turn used to generate the Area Price Worksheet with the results of the Price Update process.
Recording defaults is optional, however it can be beneficial to use the defaults to automatically create the first worksheet line as well as being able to see the nature of the worksheet without having to go to the Worksheet Lines.
Please note that only one increase method (Increase Amount, Increase Percent, Target Margin) is permitted per product category, per area, per worksheet
| Base Area | The area code for the primary area to be updated by this worksheet. Please note that Worksheet Lines provide the ability to record multiple area to which the update will apply. |
| Increase Amount | The amount by which existing Area Prices with the defined old effective date for the Base Area are to be updated to create new Area Prices for the Base Area with a new effective date. |
| Increase Percent | The percent by which existing Area Prices with the defined old effective date for the Base Area are to be updated to create new Area Prices for the Base Area with a new effective date. |
| Target Margin | The target margin required for the Area Prices to be created for the Base Area with a new effective date. Please note that reliable Cost Prices are required in Area Price Maintenance for the Area for the old effective date. |
| Rounding | In calculating Area Prices for the new effective date, this option provides the ability to select a rounding method from a system specified list. |
| GST Opt | Set this option to I for Area Prices calculated to be inclusive of GST or E to be exclusive of GST. |
Lines
The Lines Function Key (F5) in the Area Price Book Worksheet screen provides access to a range of options to:
Specify the scope of the update to be carried out beyond the Area Price Book Worksheet Defaults (Areas and Items)
Review and edit price update results prior to approval and publishing (Worksheet Lines)
Review status changes for work carried out on the Worksheet (Status History)
Specify Inventory Report Writer Reports to be run prior to Publishing a Worksheet (Reports)
Attach files in relation to the Area Price Book Worksheet (Document Register)
View the results of the generation process in a standard BusinessCraft screen as distinct from the more extensive Worksheet Lines screen (Traditional Lines)
Areas
The Price Book Worksheet - Areas screen is accessed from the Areas menu selection and provides the ability to specify multiple Areas to which the Area Price Update is to apply. Options are provided to specify the source data to be used for the update, how the data is updated and whether Missing Prices are included in the Are Price Update process.
If defaults were recorded in the Area Price Book Worksheet screen, then that information is populated on the first line of the Areas screen.
Additional areas can be specified as required with different data sources, different increase methods, different rounding options and different missing price settings.
| Area Code | The nominated area code to be updated by this worksheet. |
| Description | The Description for the Area Code (display only) |
| Src Opt | Provides the ability to specify whether the source data to be updated is either the old effective date amount for the Item in the nominated Area (C) or the old effective date amount for the item in a specified Source Area (S) |
| Src Area | If the Source Option field is set to Source Area (S), then this field becomes accessible so that a Source Area can be specified. |
| Clc Opt | The Calculation option to be used – Amount Increase (A), Percentage Increase (P), Target Margin (T). This setting determines which of the Increase Amount, Increase % or Target Margin % is accessible. |
| Increase Amount | The amount by which existing Area Prices with the defined old effective date for the Area (nominated or source) are to be updated to create new Area Prices for the nominated Area with a new effective date. |
| Increase % | The percentage by which existing Area Prices with the defined old effective date for the Area (nominated or source) are to be updated to create new Area Prices for the nominated Area with a new effective date. |
| Target Margin % | The target margin required for the Area Prices to be created for the nominated Area with a new effective date. Please note that reliable Cost Prices are required in Area Price Maintenance for the Area (nominate or source) for the old effective date. |
| Rnd Opt | In calculating Area Prices for the new effective date, this option provides the ability to select a rounding method from a system specified list. |
| Inc MP ? | Setting this option to Y ensures that missing prices are included in the Generation process. |
Product Categories
Within the Area screen is a Prod Cats (F5) function key that provides the ability to nominate product category specific Area Price Update parameters for each area. The Area Price Updater will use the Product Category Area Price Update Parameters where available otherwise the Area Price Update Parameters for the Area will apply.
Items
The Price Book Worksheet - Items screen is accessed from the Items menu selection and provides the ability to add, insert, change, delete and mass delete items that will be used in the generation process. Generally, this screen will not be accessed, other than on an exception basis, as there is a Copy function to populate this screen with the items to be included in the Area Price Update process.
Worksheet Lines
The Price Book Lines screen is accessed from the Worksheet Lines menu selection and provides the ability to review the results of the Area Price Update Process. Until the generation process is run at least once, then the screen will not show any data.
Explanation of the Worksheet Lines screen is covered in the Price Book Lines Screen section of this guide after the Generate section.
Status History
The Price Book Worksheet – Status History screen is accessed from the Status History menu selection and provides the ability to review the status changes for the worksheet. (New, Items Copied, Generated, Ready, Approved, Rejected, Completed, Cancelled).
At different stages of the Area Price Update process when the Status changes, the user is optionally provided the ability to record a status description. In other cases, the system populates the description automatically and does not provide the option.
| Seq | A system generated sequence number. |
| Src | The source of the status change. |
| Old Val | The status prior to the status change. |
| New Val | The status after the status change. |
| User | The logged in user who triggered the status change. |
| Date | The date the status change occurred. |
| Description | The system or user description for the status change. |
Reports
The Price Book Worksheet – Reports screen is accessed from the Reports menu selection and provides the ability to specify the Inventory Report Writer Reports that will run when using Print Price Book from the Options (F9) Function Key.
If no reports have been specified, then accessing the Reports screen will prompt for a report.
Type the report name or press F10, select the required report. Additional reports can be added.
Document Register
The Worksheet Document Register screen is accessed from the Document Register menu selection and provides the ability to link any required supporting or reference files and/or documents to the worksheet.
A Document Type is required with a Module Code of IM and a sub area of PB to add a document in the Document Register for the Area Price Updater. The default document type can be defined in Inventory > Setup > Inventory Setup > Page 3.
Traditional Lines
The Price Book Worksheet – Details screen is accessed from the Traditional Lines menu selection and provides to view the results of the generation process in a standard BusinessCraft screen as distinct from the more extensive Worksheet Lines screen.
| Unnamed Column | The unnamed column to the left of the Item Column is used to select lines. When a line is selected the line selector icon will appear against the line in this column. |
| Item | The Item code for the pricing details displayed. |
| Area | The Area Code for the pricing details displayed. |
| Src Cost | The cost of the item for the source data used for the Area Price Update. |
| Src Sell | The sell price of the item for the source data used for the Area Price Update. |
| Src Opt | Indicates whether the update used the old effective date amount for the Item in the nominated Area (C) or the old effective date amount for the item in the specified Source Area (S) |
| Src Area | Where the Source Option field is set to Source Area (S), then this field indicates the Source Area used for the update. |
| Clc Opt | The Calculation option used – Amount Increase (A), Percentage Increase (P), Target Margin (T). |
| Calc Sell | The Calculated Sell Price based on the Area Price Update Parameters. |
| New Sell | Provides the ability to record a revised Sell Price to be used instead of the Calculated Sell Price. |
| N | Indicates if there are Notes recorded against the line (Y) |
In the bottom right hand corner of the Price Book Wooksheet – Details screen is the Notes (F5) Function Key which provides the ability record additional notes in regards to any specifc line dispalyed.
Copy
The Copy Function Key (F6) in the Area Price Book Worksheet screen provides the ability to use range criteria to determine the items to be included in the Area Price Update Process. The results of the Copy process are displayed in the Items screen within the Lines (F5) menu and the status of the worksheet changes to Items Copied.
Two options are provided:
Copy Items
When the Copy Items menu selection is chosen, a Range Criteria screen is presented, providing the ability to limit the Area Price Update process to certain ranges of items. Clicking OK will activate the process and the Items selected will appear in the Items screen within the Lines (F5) menu.
Copy Worksheet
When the Copy Worksheet menu selection is chosen, a pop up window prompts for the worksheet to copy from. Click on the magnifying glass to open the Finder from which the worksheet can be chosen:
After selecting the worksheet to copy from, a prompt is provided to update the current worksheet header details. This prompt defaults to No.
If there are already Area and/or Item Lines, a further prompt provides the ability to update Are and Item Lines including Product Category settings where applicable.
Responding Yes to this prompt activates the process and the Area/Product Category settings will be updated in the Area Screen within the Lines (F5) menu and the items selected will appear in the Items screen within the Lines (F5) menu.
Generate
The Generate Function Key (F7) in the Area Price Book Worksheet screen is used to calculate the Area Price Update based on the Parameters defined for the Worksheet, Areas, Product Categories and Items.
Activating the Generate Function Key (F7) will commence processing immediately and on completion, the Price Book Lines screen is displayed and the worksheet status is changed to Generated.
If you close the Price Book Lines screen, and need to access it again, there is no need to run the Generate Process again. Just activate the Lines (F5) function key for the worksheet and choose the Worksheet Lines menu selection to open the Price Book Lines screen again.
If the results are not what were expected, then the Area Price Update Parameters should be revised as necessary and the generate process run again.
Price Book Lines Screen
The Price Book Lines screen is used to review and where required revise specific Sell Prices before publishing. There are a variety of navigation features in the Price Book Lines screen that need to be explained to ensure effective use of the screen.
Price Book Lines Header
The Price Book Lines Header provides no function other than the large x on the right-hand side that is used to close the screen:
Price Book Lines Filter Bar
The Price Book Lines Filter Bar provides the ability to Filter the Price Book Lines Grid based on Status or Areas by clicking on the Tabs.
After clicking on the Tab, Filter Buttons appear underneath the Tab showing the number of records that exist for that filter. Click on a Filter Button to show only those records in the Price Book Lines Grid for that Filter.
Price Book Lines Tool Bar
The Price Book Lines Tool Bar provides icons to edit and delete records.
Price Book Lines Group Bar
The Price Book Lines Group Bar provides the ability to group the records displayed in the Price Book Lines Grid to enable easier review if data.
Drag and drop a column heading on the grid to the Price Book Lines Group Bar. As you hover over the Group Bar a double headed blue arrow will appear. One or more fields can be added to the Group Bar in the order of grouping required. The Price Book Lines Grid reflects the grouping immediately. Note that fields used for grouping no longer appear in the grid.
Price Book Lines Grid Column Headings
The Price Book Lines Grid Column Heading appear underneath the Group Bar. Please note that unless you have a larger screen or a very high resolution display, then it is likely that not all fields will appear on screen and there will be a scroll bar to move to fields not shown.
Standard BusinessCraft finder navigation options are available for the Column Headings in the Price Lines Grid. For detailed understnading of htose options please refer to the BusinessCraft Finder User Guide.
In summary, clicking on a column will sort the grid by that column, columns can be dragged from their current position to a different location on the grid, column widths can be adjusted. Columns can be hidden or shown using the Column Chooser by right clicking anywhere on the Column Heading.
The important difference to note in regards to the Price Book Lines Grid is that the Area Code, Area Description, Item Code and Item Description columns have a preserved area on the grid to the left of a double line divider bar. Other fields cannot be dragged to that area and only those four fields can be moved around within that preserved area.
The Price Book Lines Grid Column Headings also provide in line filters for each column indicated by a small funnel icon in the colum heading. When a filter is applied, the filter criteria are disaplyed at the botom of the grid, just above the Status Bar. Multiple filters can be applied.
The Filter can be toggled on/off by clearing/ticking the check box.
To the right of the Filter is an Edit Filter icon which opens the Filter Editor screen that can be used to further extend the filter without needing to use the icons on the Column Headings.
Next to the Edit Filter icon is a Clear Filter icon which clears the filter completely.
Price Book Lines Grid
The Price Book Lines Grid appears underneath the Column Headings and contains the Area Price Update data.
If grouping has been set, then the grouped field headings will appear and can be expanded or collapsed using the small triangles to the left of the group name or using the Expand All Groups or Collapse All Groups icon in the Status Bar.
Where applicable totals are displayed for groupings.
The fields available in the Price Book Lines Grid and their purpose are explained in the following table.
Please note that the majority of the fields are informational and the only fields that can be changed relate to the New Sell Price and are highlighted in a different colour to indicate they are editable fields.
| Area Code | The Area Code for which new prices have been generated. |
| Area Description | The Area Description for which new prices have been generated. |
| Item Code | The Item Code for which new prices have been generated. |
| Item Description | The Item Description for which new prices have been generated. |
| Product Category | The Product Category for which new prices have been generated. |
| GST Opt | Indicates whether prices calculated include or exclude GST. |
| Source Opt | Indicates whether the Source Price for the calculation is the current price for that item in the area being calculated or is the current price of the item from another area. |
| Source Area | If the Source Option is Source Area (S), the Source Area code. |
| Effective Date | The effective date for the Price Update. |
| Source Cost | The source cost of the item as determined by the Source Option and Source Area. |
| GST Code | The GST Code for the item. |
| Source Sell | The source sell price for the item as determined by the Source Option and Source Area. |
| Source GST | The source GST for the item as determined by the Source Option and Source Area. |
| Source Sell (inc GST) | The source sell price (including GST) for the item as determined by the Source Option and Source Area. |
| Calc Sell Opt | The Calculation option used – Amount Increase (A), Percentage Increase (P), Target Margin (T). This setting determines which of the Call Sell Increase $, Calc Sell Increase % or Calc Sell Margin % fields are populated. |
| Calc Sell Increase $ | The calculated increase if the Calc Sell Opt field is set to A. |
| Calc Sell Increase % | The calculated increase if the Calc Sell Opt field is set to P. |
| Calc Sell Margin % | The calculated increase if the Calc Sell Opt field is set to T. |
| Calc Sell Price | The new calculated sell price for the item |
| Calc Sell GST | The new calculated GST component for the item |
| Calc Sell (Inc GST) | The new calculated sell price including GST for the item |
|
The New Sell fields that follow provide the ability to override the calculated values on an exception basis and be used when prices are published instead of the calculated values. |
|
| New Sell Opt | The Calculation option used – Amount Increase (A), Percentage Increase (P), Target Margin (T). This value is populated depending on whether an increase is keyed into the New Sell Increase $, New Sell Increase % or New Sell Margin % field. |
| New Sell Increase $ | The new increase amount to be used instead of the calculated increase when the generation process is re-run. |
| New Sell Increase % | The new increase % to be used instead of the calculated increase when the generation process is re-run. |
| New Sell Margin % | The new increase margin % to be used instead of the calculated increase when the generation process is re-run. |
| New GST Code | The GST Code to be used when the generation is re-run |
| New Sell Price | The sell price determined by changes made in the preceding New parameter fields and will be used when publishing area prices instead of the calculated value. |
| New Sell GST | The GST determined by changes made in the preceding New parameter fields and will be used when publishing area prices instead of the calculated value. |
| New Sell (inc GST) | The sell price (incl GST) determined by changes made in the preceding New parameter fields and will be used when publishing area prices instead of the calculated value. |
| New Sell Status | This field indicates whether pricing was calculated (C) or Missing Prices to enable a calculation (M) when the generation process was run. When a change is made to any of the New Sell Fields, that line is recalculated and the New Sell Status is changed to Determined (D). This prevents the generation process from overriding the New Sell Prices and flags these lines when Publishing to use the New Prices not the Calculated Prices. |
| Comments | A Comments field provided for user defined notes. |
| Product Group | The Product Group for the item. |
Price Book Lines Grid Total
Besides providing total of applicable columns for groupings, grand totals are provided at the bottom of the grid.
Price Book Lines Status Bar
The Price Book Lines Status Bar provides:
A record counter
A Print Icon which prints the grid to screen and then can be printed, emailed or exported in a wide range of formats.
An Export icon that exports the grid to Excel
An Expand All Groups icon
A Collapse All Groups icon
Publish
The Publish process creates new Area Price Maintenance records for the records contained within the worksheet. Please note that there is no warning prior to Publishing – the process will run if pre-requisites are satisfied.
The worksheet must be set to the Approved status before the Publish process can be run and if this is not the case a notiifcation is provided advising that only Approved Worksheets can be published.
When the Publish process runs, a confirmation message is provided. Please note that if there are existing prices for the area/effective date/item then existing prices will be updated rather than creating new records. The confirmation message indicates the number of created and updated records as well the worksheet ID.
The status of the worksheet changes to Completed and Area Price Maintenance will contain the created/updated records – go to Inventory > Price Maintenance > Area Price Maintenance.
There are various ways of looking up the prices but one method is to click the View icon in the menu sidebar and in the Range Criteria enter the Worksheet ID in the Sheet ID field, limiting the prices listed to those updated by that worksheet.
Enquiring on any of the records shows the full details including the Worksheet Description in the comment field and the Worksheet Id in the Sheet ID field that created the record:
Options
The Options Function Key (F9) in the Area Price Book Worksheet screen provides access to the Print Price Book, Update Status and Transfer Files options.
Print Price Book
Print Price Book enables the generation of the Inventory Report Writer Reports listed in the Reports menu selection within the Lines (F5) Function Key. No prompt is provided; the reports run automatically and will run in accordance with the Output Option defined for each report.
Update Status
The Update Status option provides the ability to change the status of the worksheet as part of the Area Price Update Process.
In some cases, (New, Items Copied, Generated, Completed), the status changes when carrying out an action within the Area Price Update process. In other cases, the process reaches a point which requires the user to change the Status and optionally provide a status change comment using the Update Status option prior to being able to proceed further in the process (Ready, Approved, Rejected, Cancelled).
When the Update Status option is selected, the options that will appear will depend on the existing status of the worksheet.
In the example below, the worksheet currently has a status of Generated:
In this example, the Worksheet currently has the status of Ready:
Transfer Files
The Transfer Files option provides the ability to transfer the contents of the Documents Register for the worksheet to a specified location.
The location is defined by entering a user defined environment variable name in the Price Book Transfer Location field found in Inventory > Setup > Inventory Setup > Page 3.
The file path then needs to be recorded against that Environment Variable name in Security Setup > General > System Registry. Once in the System Registry the Environment Variable can be recorded at System Level or one of the other levels provides by the Registry Type field. Regardless of Registry Type chosen the environment variable needs to be recorded in Environment > Extended.
Once this setup is completed and this option is selected, the system will copy files from the Document Register for the worksheet to the location specified.
Revision 1
6th February 2017