fbpx

KIQEBP – Enterprise of Business Partners / Consultants
Inventory Control (Stock)
Stock Price Update
STOCK PRICE UPDATE

The IQ Stock Price Update module has been designed to enable users to import their own stock master
file details (stock details) without having to enter each stock item one by one under Stock
Maintenance.

This module provides the functionality to import a CSV (comma delimited) text file of predetermined
format, into the IQ system.

NOTE: No Style Management Stock Items can be imported. They have to be created by using the Style
Management option in the Stock Maintenance module.
SUGGESTION: For fields where the user is not sure what the import value should be, a stock item
that needs to be updated can be changed manually in Stock Maintenance. The user can then under
Stock Maintenance select the specific field to be visible and see what the new value has to be. A
CSV file can then be created to change the value in all the other selected stock items.
Some of these values are calculated according to the specific modules selected.

Example: The user has realised that there is a problem with the discount allowed, the system allows
discount where it should not. Stock Price Update allows the user to import a CSV file to correct
the problem, instead of editing each stock item and correcting the module selection.

Functionality
The Stock Price Update functionality can be found from the Main menu → Stock → Utilities → Stock Price Update.

The following screen will be displayed:

Sort OrderThe Sort Order allows the user to change the sequence in which the data will be displayed on the screen.

Status Legend
Implementing the import of the selected CSV file, the data will be tested for integrity. All the imported items will be displayed on the display grid and each will be flagged with a status, indicating its suitability for the update process. The status of the import details is colour coded to enable easy identification of items with problems on, as well as the type of error.Only items that have a “No Error” status will be considered for the update process.NOTE: Stock Items with problems have to be corrected before the .CSV file can be imported into IQ again. No corrections can be made on the items that are listed in the Imported Details.
Available FiltersOnce a filter has been created and saved, it will appear in the top right corner of the screen. Double click on a filter to activate it and double click on “Clear Filter” to see all the imported items.

Import Data

Once the import of the CSV file is done, all the imported stock items will be displayed on the
Stock CSV Import / Update screen.


In addition to the stock master information fields, there are error descriptions and error code
fields.

These columns will be colour coded according to the colour legend. When an item has a colour coded
error, the item will not be imported during the import process.
Only valid items will be imported.


After the import process has been completed, all the successfully imported items will be removed
from the display grid. All items that contained errors have to be rectified in Excel or Notepad and
have to be imported again.


There are two options under the Load Import File Button:


Import

The Import option will be used to import the CSV text file with the predetermined format. Only
fields determined in the Example File can be imported.

Generate Example File


An example CSV file will be created which will assist in the creation of the new stock items to be
imported.

Once the user ha clicked on the Generate Example File option, the user will be prompted for an
Export location where the newly created CSV file should be saved.

Create a CSV File
The .csv file which was created as an example, can be imported into Excel, Wordpad or Notepad.
NOTE: It is easier to manipulate data in an Excel spreadsheet.

The format of the import file is defined as follows:


First Line: Header Information. If the import file is opened in WordPad or Notepad, each field is
displayed and separated by a comma – e.g. Code,Barcode,SellPrice1.
All the subsequent lines are relevant information for each defined field for each stock item.- e.g.
ABC,600412343332,47.50,,0.00.


Example of a correct Stock CSV Import file:

When new stock items are being imported, three are four (4) mandatory fields required for the file to import correctly:


Code
Description
Department (Major)
Vat Rate


NOTE: Depending on the links set up on Stock in the Module Parameters, more fields might become mandatory.
If the major departments are linked to sub-departments (minor departments) in the Module Parameters, the sub-department also becomes mandatoryand should be included in the import CSV file.


NOTE: It is not compulsory to import all the fields Generated in the Example File to create or maintain stock items. In other words, if the user doesn’t use all the fields, the ones that are not necessary can be removed or deleted.


The user also has the option to make changes to existing stock items, by importing the item code and only the fields of the stock items which need to be changed.


NOTE: Ensure that the header information for each import is correct, as provided in the Generated Example File.


NOTE: In Excel, if any code in anyone of the stock item fields needs to start with a zero (0), set the field format for those columns to be text. Excel doesn’t except leading zeros (0).


It is advisable not to use departments, etc. that start with zeros (001), because if the CSV file is opened in Excel the zeros will disappear, unless they are specified as TEXT columns.

VERY IMPORTANT: The created file has to be saved in the CSV (comma delimited) file format.
VERY IMPORTANT: The field format should never be changed. When the file is opened in WordPad or Notepad, the field separator has to be a comma (,) and should not have any double quotation marks (“ “).


Below is the screen that will be displayed when the exported Example File is opened with WordPad.

When updating the stock master details, a lot of the fields can be updated by entering a new value which will overwrite the already existing value in the field.
NOTE: If changes are made on some of the fields in the stock master details, it may have major implications in other modules, for example the vat rate and ItemCategory of an item.


SUGGESTION: Edit an existing stock item in Stock Maintenance and make the changes where required. Add those fields that were changed to the select visible fields.


The values in those selected fields can then be used in the Stock Price Update module to be imported for all stock items with the same scenario.


Code
The Stock Code is a mandatory field (may not be BLANK). The user will not be able to update or add any stock item, without the stock item code. The code is limited to 15 alpha-numeric characters.

Descript
The Stock Description will always be updated if provided. If a new item is created and you do not complete this fields (i.e. leave it blank) the new stock description will be updated with a blank value. Thus, always provide the required description for any new item to be created. The Stock item description is limited to 50 characters.If the item already exist, the newly imported description will overwrite the old description.
Alt_DescriptAny additional information about the item and an extended description may be entered. The field will be updated with the provided value.

Department
The Stock Major department is a mandatory field for new stock items and may not be BLANK. The values added in this field must be as they were created in the Stock Major Department Table in Company Details. If the value of this field doesn’t exist on the Major department table or is blank when creating a new item, an error will be generated.

SubDepartm
If the Stock Sub Department is provided, the value must also exist in the Stock Sub Department Table (Groups Table). If the Major and Minor Departments are linked in the Company Details, the field is required (may not be BLANK) for any new items.
If the Sub-departments (minor departments) are linked to stock categories in the Module Parameters, both these fields need to be included in the import CSV file.If the Sub-departments (minor departments) are linked to stock ranges in the Module Parameters, both these fields need to be included in the import CSV file.

Gencode
The Stock General Code will be updated if provided. The general code can be used as a secondary stock item code or barcode for use when integrating stock files. An example of a general code is the ISBN numbers of books.

Barcode

Duplicate Stock Barcodes will not be allowed. The stock Master file AND the temporary import table is checked for duplicated barcodes during the Import process. A barcode is an optical machine-readable representation of the data relating to the stock item to which it is attached.NOTE: It is suggested that if there are no specific barcodes on the item that can be entered or scanned in, to use the stock item code as a replacement barcode.

Colormatrix
The ColorMatrix is a number identifier for a Colour in the colours table, as created in Module Parameters. This field will be updated if the ColorMatrix is provided. An error will be generated if the identifying number is not found in the colours table.

Sizematrix
The SizeMatrix is a number identifier for a Size in the Sizes table, as created in Module Parameters. This field will be updated if the SizeMatrix is provided. An error will be generated if the identifying number is not found in the Sizes table.
CycleThe Cycle field will be updated if provided (even if BLANK). Example: Summer, Autumn, Winter and Spring.
LinecolourtypeThe Stock Line colour Indicator field also has an INTEGER value. This value will only be updatedif Stock Custom Line Colours have been enabled in Default Settings (1) and if a value is provided. If the value is BLANK or equal to “0” (zero), it will be ignored. In this way items on special, cascading items, non-stock items, serial number items, etc. can be “visually” distinguished from other stock items.
To add / edit the line Colours select Utilities à Setup àModule Parameters à Stock à Item Line Colour.

Packsize
The Packsize field will be updated if provided, even if it is BLANK.Example: When cascading items are used for Cokes, there are different stock codes for the single Cokes and the case of Cokes. The pack size for the single will be 1 and for a case of Cokes it will be 24.

Packdes
The Pack Description field will be updated if provided, even if it is BLANK. Example: Coke Single Can 330ml.

Avrgcost
The Average Cost field will be updated if provided and when adding a new stock item. When editing, these values will be adjusted by the system via a Stock Adjustment transaction based on the AVRGCOST provided in the import file. When importing average costs for items, the system will prompt the user to enter an order number.

Sellprice1 – 10
The SellPrice 1 – 10 are the current exclusive selling prices of the stock items. They will be updated if they are provided.
NOTE: When importing Sell Prices, the system will prompt the user to select if the prices are inclusive or exclusive of VAT.
Regular_SUThe Regular Supplier will only be updated if provided. If provided, the value has to match a Creditor’s Account in the Creditors Master File.

Supplierco
The Supplier Stock Code will always be updated if provided. The Supplier Code is the item code used by the supplier for the item.
NOTE: If the supplier’s code for a stock item is different from the users stock code but there is only one main supplier, the code can be added to the system in this field.
MemoThe Memo Field will be updated if provided, even if it is BLANK. One of the examples of using the Memo field, is to use it for an extended description for the stock item.

Status
The Item’s Status is a descriptive text field and will be updated if provided. This field is mainly used for filtering and reporting purposes.Examples could be:U – Unrestricted-use stock Q – Quality inspection stock B – Blocked stockS – Slow Moving

Vatrate
The VATRATE is an Integer field which specifies the Vat Rate of the Stock Item. It will be updated if it is provided. If the VatRate doesn’t exist for the selected item on the system, an error message will be generated when importing.
BinLThe BinL (bin location) will be updated if provided, even if it is BLANK.
Min_lvlThe Min_Lvl (Minimum Level) will be updated if it is provided.
Max_lvlThe Max_Lvl (Maximum Level) will be updated if it is provided.
Ord_lvlThe Ord_Lvl (Order Level) will be updated if it is provided.
Ord_quanThe Ord_Quan (Order Quantity) will be updated if it is provided.

Ordermethod
The Ordermethod value will only be updated if provided. If the value is BLANK it will be ignored and no changes will be made to the Stock Master.The user can use one of the three (3) available ordering methods: 0 – Normal Ordering Method1 – Replenishment Ordering Method 2 – Customized Ordering Method

Orderformula
The Orderformula specifies the Ordering formula to use if the Order Method has been set to Custom Ordering. This is the number value of the Ordering Formula as it was created in the Module Parameters. An error message will be generated if the Ordering Formula doesn’t exist in the Ordering Formula table.
The MaxDiscount field is used to specify the maximum discount percentage allowed on the
Maxdiscountselected stock item. The MaxDiscount field will be updated if a value is provided.

Reportitem
Reporting Item is the item code of the main item, in other words the selected item reports to the main item. It will be updated if the Cascading items were enabled in Default Settings (1) and if it is provided. An error message will be generated if the Stock Item does not already exist in the Stock Master.
ReportcfactorThe ReportCFactor is the conversion factor for Cascading Items. It will be updated if the Cascading Items were enabled in Default Settings (1) and if it is provided.

Newprice1 – 10
New Selling Prices will update if provided. New selling Prices can be imported for items and the system will automatically start on the indicated Pricechange1 date to use these new prices as the new Retail Prices. The system will prompt the user to select if these selling prices are VAT excluded or included.
Priceghg1The Price Change-1 field will be updated if provided. This date will indicate when the system should start charging the new Selling prices, as they were imported.

Futurecost
The FutureCostDate is the day in future when the newly imported future cost per item will be implemented. The FutureCostDate must be imported with the Future Cost and the stock item code.
GenerictypeThe GenericType can be used to Auto Generate Item descriptions if it is enabled in Module Parameters. This field will update if it the correct data is provided.
BrandThe Brand can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
ModelThe Model can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
TechnicalspecThe TechnicalSpec can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
SupphierarchyThe SuppHierarchy can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
SuppdepartmentThe SuppDepartment can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
ImporttypeThe ImportType can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
TariffcodeUsed for imports and will be discussed in External Charges.
MaterialThe Material can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
AssemblytypeThe AssemblyType can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
GuaranteetermThe GuaranteeTerm can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
ColordetailedThe ColorDetailed can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
IncotermsThe IncoTerms can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
MemoThe Memo can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
BuyerThe Buyer can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.

Category
If the Sub-Departments (minor departments) are linked to Stock Categories in the Module Parameters, both these fields need to be included in the import CSV file. If the Stock Categories are linked to Stock Ranges in the Module Parameters, both these fields need to be included in the import CSV file.
RangeIf the Stock Categories are linked to Stock Ranges in the Module Parameters, both these fields need to be included in the import CSV file.

ABCClass
Specifies the A, B, C class of stock items. The field will be updated if provided. 1 – A – Top Product2 – B – Top KVI Lines Per Category 3 – C – Non Sensitive Lines0 – Not Defined
ABCClassGPThe GP percentage is used in conjunction with one of the above-mentioned ABC Classes. The field will be updated if provided.
AllowdiscountbitsThis field “Allow Discount Bits” specifies the modules in which discount has been allowed for this stock item. The field will be updated if the correct values are provided.0 – Not Defined (No Discount Allowed) 1 – Invoices and/or Recurring Charges 2 – Credit Note4 – Goods Receiving 8 – Returns16 – Purchase Orders
32 – Sales Orders64 – Quotes 128 – Job Cards256 – Point of SaleEach of the codes above represent a module in the system. The user can select one or a combination of the codes above to allow discount. When a combination is used, the codes for each of the modules must be added together. In other words if discount will be allowed on the stock item in the Point of Sales, the Sales Orders and the Quotes modules, the code to use will be 256 + 32 + 64 = 352. If all the modules are selected the code will be 511.

Disallowdecimals

Import the DisallowDecimals field as “True” for all solid or pre-packed products, which needs to be sold as a whole unit.Change the DisallowDecimals field as “False” when the units to measure are in metres, litres or kilograms.NOTE: If the field is not provided, the system will default the field to FALSE.

Enterser
This field specifies if Serial Numbers are enabled for this stock item. In other words, should we store serial numbers for this stock item. The “Enter serial number” will be updated if provided. If serial numbers are enabled, the user will use True for the Serial Number items and False for all other items.

Volumetrics
The Volumetrics values will be updated if the correct values are provided.NOTE: The Volumetrics quantities and rounding figures needs to be entered manually via the Stock Maintenance module.The user has the option of one of each of the following volumes per item: 0 – Units (Default Setting)- Square Meters – Length x Width in meters- Cubes Meters – Length x Width x Height in meters- Linear – used to measure the perimeter of a rectangular (2 x Length) + (2 x Width) + (2 x Width)- Grams – the item’s weight in grams- Kilograms – the item’s weight in kilograms 6 – Millimeters – length in millimeters- Meters – length in meters- Milliliters – volume in milliliters 9 – Liters – volume in liters- Tons – weight in tons- Linear Square Millimeter – Length x Width in millimeters 12 – Linear Square Meters – the perimeter in meters- Square Millimeters – Length x Width in millimeters- Cubes Millimeters – Length x Width x Height in millimeters 15 Linear Millimeters – the perimeter in millimeters

Scaleitem
The ScaleItem field specifies if the stock item is a scale item or not. In other words, if a True is entered for an item, it means that this is a scale item. The value for all the non-scale items will be false. This field will be updated if a value “True”/”False” is provided.
NOTE: For all scale items, the check in the DisAllowDecimals field has to be removed and for importing purposes the field has to imported with the value “False”.
StocktypeThe StockType field will be updated if the correct value is provided. 0 – Normal Stock Item (Default Value)- Cellphone- Airtime- Television
– Voucher- Sundry Item

Onholdtype
This field “OnHold Type” specifies the modules in which this stock item has been set to ONHOLD. The field will be updated if the correct values are provided.- Units (Default setting)- Invoices and/or Recurring Charges 2 – Credit Note4 – Goods Receiving 8 – Returns16 – Purchase Orders 32 – Sales Orders64 – Quotes 128 – Job Cards256 – Point of Sale 512 – Stock Transfers1024 – Stock Transfer Requests 2048 – Bill of Quantities 4194304 – Agent Transactions 134217728 – LaybyesEach of the codes above represent a module in the system. The user can select one or a combination of the codes above to be Onhold for the selected module. When a combination is used, the codes for each of the modules have to be added together. In other words if the selected stock item should be Onhold in the Point of Sales, the Sales Orders and the Quotes modules, the code to use will be 256 + 32 + 64 = 352. If all the modules are selected the code will be 138416127.

Excludesellingvalue
The ExcludeSellingValue field specifies if the stock item should be excluded from Ordering and then which modules it should be excluded from.The field will be updated if the correct values are provided:0 – The item should not be excluded from ordering.4 – Exclude item from ordering in Goods Receiving (GRV). 16 – Exclude item from ordering in Purchase Orders.20 – Exclude item from ordering in Goods Receiving (GRV) and from Purchase Orders.

Defaultwarehouse
The DefaultWarehouse field is the default warehouse which will be updated when the stock item is sold. This field will update if the correct number for a warehouse is provided, as it was created in Module Parameters.

Defaultpurchasingwarehouse
The DefaultPurchasingWarehouse field is the default warehouse which will be updated when the stock item is purchased. This field will update if the correct number for a warehouse is provided, as it was created in Module Parameters.

Defoutstatuscode
The Default Out Status Code field is the default Status Code which will be used when the stock item is sold or taken out of the system. This field will update if the correct status code, as it was created in Module Parameters.

Definstatuscode
The Default In Status Code field is the default Status Code which will be used when the stock item is bought or taken into the system. This field will update if the correct status code, as it was created in Module Parameters.
MDR_Supplier_IDThe MDR_Supplier_ID is the Master Data Repository Supplier Id number which will identify the same items between different branches which use different Stock item codes for the same item.
MultiplebarcodeThe system allows the user to add multiple barcodes to items. In other words, a selected item can have more than one barcode to identify the item.
MultiplebinThe system allows the user to store an item in multiple bin locations in a warehouse. In other words, a selected item can have more than one bin location where the stock is stored.

Binwarehouse
The system allows the user to link specific bin locations to specific warehouses. If the bin locations have been linked to a specific warehouse, for example Warehouse 1, the warehouse linked to the bin location can also be imported.

Importing of .csv File

The Stock Price Update functionality can be found under IQ Enterprise → Stock → Utilities → Stock Price Update.

Click on Load Import File


Click Import

Select the .CSV (comma delimited) file from where it was saved and click on the Open button.

The display grid will display all the imported items or items that have been read from the import file.


In addition to the usual field, the grid also shows an Error Column in the most left column.


This column is colour coded according to the colour legend. When an item has a colour coded error, the item will NOT be imported during the import process.


Such items will simply be skipped. Only valid items will be imported.


After the import process has been completed, all successfully imported items will be removed from the display grid. All items that still contain errors (or that have been re-evaluated during the import process and gained new errors), will remain in the display grid for further review and can be imported in the next go.


Process
On processing the data, IQ performs integrity checks for each item, to cater for changes to the Stock Master file that has happened since the time of import until the time of processing.

The user will be prompted to indicate if the Imported Sell Prices are Inclusive or Exclusive of VAT. Select either Inclusive or Exclusive and click on the Accept button.

An information message will appear to indicate that the import is completed and that items that did not import successfully will remain in the Import Details grid for review.


After the Import Process, the user will be able to Filter / Sort the information to determine the origins of the Import problems.


These problems have to be rectified before the import is done again. IQ doesn’t allow any changes to the information on the imported information.

The course material in this lesson is used under permission of Kerridge Commercial Systems.