Opening Stock Balances can only be added to Items that currently exist in Ostendo therefore the first step is to create the Items by keying them into Inventory>Items or Importing using either Ostendo’s Data Importing function (File>Data Importing) or your own Custom Script (File>Custom Scripts). You should note that the Item Master record contains a field called ONHANDQTY. This field is updated by Ostendo whenever a stock movement is made therefore, if you are importing the Item Master records this should contain zero.
It is recommended that you use the Inventory Adjustment routine to create the opening balances by going through the following steps
| · | Create the Inventory Adjustment Batch header |
| · | Create the Opening Balance Lines in a Spreadsheet |
| · | Ensure that ‘Look Up’ table values are present in Ostendo |
| · | Import the Spreadsheet data as Adjustment Batch Lines |
| · | ‘Post’ the Adjustment Batch |
1. Create an Adjustment Batch header
In Ostendo go into Inventory>Inventory Adjustments and create an Adjustment Batch header. Make a note of the generated Adjustment Number.
2. Create a Spreadsheet
Create a Spreadsheet containing the following fields:
| · | Adjustment Number |
| · | Warehouse Code |
| · | Location |
| · | Item Code |
| · | Unit |
| · | Adjustment Type |
| · | Adjustment Quantity |
| · | Serial Number |
| · | Expiry Date |
| · | Batch Number |
| · | Revision Number |
| · | Item Grade |
| · | Item Colour |
| · | Item Size |
2.1. You should now populate the spreadsheet by creating one record for each unique combination of
| · | Adjustment Number (I.e. The Number you noted down above) |
| · | Warehouse |
| · | Location |
| · | Item Code |
| · | Unit |
| · | Expiry Date |
| · | Batch Number |
| · | Revision Number |
| · | Item Grade |
| · | Item Colour |
| · | Item Size |
You can populate the spreadsheet manually or you could use Ostendo’s Data Exporting function (File>Data Exporting) using the current Item Master table as the basis for generating the Lines in the Spreadsheet.
3. Look-Up Table Checks.
Having created the Spreadsheet lines you now need to ensure that certain field values exist in Ostendo as Ostendo validates these during data entry. These are:
3.1. Always Validated
| · | Warehouse Code; Must exist in table WAREHOUSEMASTER (Inventory>Warehouses) |
| · | Location: Must exist in LOCATIONMASTER for the Warehouse/Location combination (Inventory>Locations) |
| · | Unit: Must exist in ITEMUNITS for the Item Code (Inventory>Items>Item Units) |
| · | Adjustment Type: Must exist in ADJUSTMENTTYPES (Inventory>Settings>Adjustment Types). It is suggested that – because this is an opening balance – you create one called ‘Opening Stock’ |
3.2. Conditional Validation
If your ‘sub-level’ item variants covering Batches, Colour, Grade or Size are restricted to pre-defined values then you should firstly create these in the following Tables:
| · | Batch Number: Must exist in ITEMBATCHES. Click on the ‘Batches’ button on the Item’s Detail screen |
| · | Item Grade: Must exist in ITEMGRADES. Click on the ‘Grades’ button on the Item’s Detail screen |
| · | Item Colour: Must exist in ITEMCOLOURS. Click on the ‘Colours’ button on the Item’s Detail screen |
| · | Item Size: Must exist in ITEMSIZES. Click on the ‘Sizes’ button on the Item’s Detail screen |
3.3. Mandatory ‘sub-level’
Revision Levels. If you are using Revision Numbers then this is validated against Table ITEMREVISIONS. You can maintain these by either clicking on the ‘Item Revisions’ Icon in the Item’s Detail screen or going to the Revisions Maintenance screen via Inventory>Settings>Item Traceability>Item Revisions
3.4. Distinct Value Check.
You may wish to use the following script to determine the distinct values under each of the above columns in your spreadsheet
Go into File>Custom Scripts and create a ‘Standard’ script and add the following under the ‘Script’ tab.
var
OpenDlg: TOpenDialog;
x,ColumnNumber: Integer;
ImportFile: String;
OutputList: TStringlist;
TempList: TStringList;
function GetImportFile: string;
var
OpenDlg: TOpenDialog;
begin
OpenDlg := TOpenDialog.Create(Nil);
try
OpenDlg.InitialDir := 'C:\';
OpenDlg.Filter := 'Excel Files (*.csv)|*.csv|All Files (*.*)|*.*';
if OpenDlg.Execute then
Result := OpenDlg.filename
else
Result := '';
finally
OpenDlg.Free;
end;
end;
{Main Code Section}
begin
OutputList := TStringList.Create;
TempList := TStringList.create;
ImportFile := GetImportFile;
if trim(ImportFile) = '' then exit;
try
ColumnNumber := AskQuestion('Column Number','INTEGER','Please enter the Column Number','');
TempList.LoadFromFile(ImportFile);
for x := 0 to TempList.Count - 1 do
begin
if OutputList.IndexOf(trim(ParseString(TempList.Strings[x],',',(ColumnNumber - 1)))) < 0 then
begin
OutputList.Add(trim(ParseString(TempList.Strings[x],',',(ColumnNumber - 1))));
end;
end;
OutputList.SaveToFile('c:\DistinctValues.txt');
run('c:\DistinctValues.txt');
finally
TempList.free;
OutputList.free;
end;
end.
Click on the ‘Run’ Button in the Script tab and point the script to the above Spreadsheet. Tell it which column you are assessing and a display will be returned showing the distinct values in that column. You can then print it so that you can key the results into Ostendo or alternatively ‘save’ the file and create a Spreadsheet then import from the spreadsheet into Ostendo.
4. Import the records
Having ensured that the above data now exist in Ostendo you should go to File>Data Importing and import the Spreadsheet into table INVENTORYADJUSTLINES.
5. Final validation and update
Having imported the data go into Inventory>Inventory Adjustments where you will see the imported records against the Inventory Adjustment Batch. You can make any final adjustments as required before going to the ‘Detail’ tab and clicking to ‘Post all Adjustments’ button to generate the opening balances.