Consolidating several Syspro (V6) companies into one Syspro (V7) company.

I was tasked to project manage the consolidation of 13 x Syspro (V6) companies into one Syspro (V7) company in 2015.

The first phase was a master file cleanup, standardisation and setup check.  The following areas were covered:

Inventory Master  (InvMaster)

I had to ensure each (unique) StockCode had the same description, Unit of Measure, Alternate Unit Of Measure and conversion factor, etc. – and that it represented the same item in reality – for all companies.  I found the easiest way to do this, was to create a single ‘consolidated’ InvMaster – and compare each companies’ data to it.

Customer and Supplier Master (ArCustomer and ApSupplier)

Each companies’ Customer and Supplier accounts remained unique – but Area, Class, Branch, Terms Codes, etc. had to be standardised across all companies.  Price lists and Contracts were also made unique.

General Ledger (GenMaster)

There was no time for a complete re-code of all GlCodes – so I prefixed each companies’ GlCodes to make it unique.

Other data and setup

Banks, Tax Codes, Gl Integration, Document Numbering, additional fields*, were just some of the list of areas that were covered.

*V6 uses AdmFormData, whereas V7 makes use of the (MUCH more convenient) ‘plus’ tables.  e.g. [InvMaster+], [ArCustomer+], etc.


A staggered approach was used for implementation (as opposed to a ‘big bang’) – and this proved to be very wise.  Learnings from each go-live were incorporated for the next consolidation run.

I wrote SQL Server 2014 scripts to handle the extraction, transformation and loading (ETL) of each companies’ Syspro data into the Consolidated environment.  For the very first implementation, I ran the (list of 70!) manually – but the rest was done via SQL Server Jobs.

How to age stock value (Aged Inventory Valuation) in Syspro with Costing method = Average

I recently wrote a report for a customer with Average costing switched ON, so there were no dated lots/batches to use in the aging.

I made the assumption that FIFO was used.

I first calculated the receipts (POs, Jobs, Adjustments and Stock Receipts) quantities and values for the last 3, 6, 9, 12 (etc.) months, at Actual Costs (from InvMovements).
I then systematically calculated backward from the InvWarehouse.QtyOnHand, by deducting each period’s receipts, until InvWarehouse.QtyOnHand was ‘depleted’. The remaining periods’ receipts were then cleared/zeroed.

Let me know if you need assistance with something similar.

Syspro Stock Take System – find Sales Orders that will result in a negative ShipQty

When a stock take result will cause a Sales Order’s Ship Qty to go to negative or zero – the Stock Take Confirmation process will halt – until all the relevant Sales Order detail lines are de-allocated (‘available’).  Syspro’s standard report shows a list of StockCodes – so one would need to find the relating Sales Order(s) (via Inventory Enquiry) – and de-allocate accordingly.  Quite a lengthy process!

But here’s a SQL query that will produce a (crude) list of Sales Order numbers that are preventing the Stock Take Confirmation to complete:

 FROM SorDetail d, SorMaster h
 WHERE h.CancelledFlag <> 'Y'
 AND h.OrderStatus <> '9'
 AND h.OrderStatus <> '*'
 AND h.OrderStatus <> '\'
 AND d.SalesOrderDetStat <> 'C'
 AND d.SalesOrder = h.SalesOrder
 AND h.DocumentType <> 'C'
 AND d.LineType <> '6'
 AND (d.MShipQty + d.MBackOrderQty) > 0
 AND CONCAT(d.MWarehouse, d.MStockCode) IN (
 SELECT CONCAT(w.Warehouse, w.StockCode)
 FROM InvWarehouse w, InvStockTake s
 WHERE w.Warehouse = s.Warehouse
 AND w.StockCode = s.StockCode 
 AND (s.OrigQtyOnHand - s.QtyCaptured) > 0 -- Only interested in REDUCTIONS
 AND ((w.QtyOnHand-(w.QtyAllocated-w.QtyOnBackOrder)) - (s.OrigQtyOnHand - s.QtyCaptured)) < 0 -- Only where ShipQtys will be affected
 ORDER BY h.SalesOrder

This might speed this up!

PS:  It might even speed things up more – if you run this BEFORE the stock take – and reduce allocations where more than 90% of the stock holding is allocated to Sales Orders.



How Syspro’s GenJournalDetail Comment is made up 1 : AP Invoice posting

Sure one can identify an AP Invoice posting in GenJournalDetail by looking for Source = ‘AP’ and Type = ‘EXP’ – but how is the ‘Comment’ compiled – or how do you search for ‘your’ AP Invoice?

I think the image below is self explanatory.  I’d search for WHERE GenJournalDetail.Comment LIKE ‘Jnl:%Inv:%<your Inv No or part of>%’;


How to make a string out of a list (or a set of rows) MS SQL

You’ve got table #t1:

InvoiceNumber StockCode
Inv001 Stk001
Inv001 Stk002
Inv001 Stk003
Inv002 Stk001
Inv002 Stk003

But you want:

InvoiceNumber StockList
Inv001 Stk001,Stk002,Stk003
Inv002 Stk001,Stk003


Then use:


DISTINCT t2.InvoiceNumber,


FROM #t1 t1

WHERE t1.InvoiceNumber = t2.InvoiceNumber

ORDER BY t1.InvoiceNumber,StockCode

For XML PATH ()),2, 1000)[StockList]

FROM  #t1 t2;


How to get the most out of your ERP training

1.  I’ve said it before – keep your staff training as close as possible to go-live.  People forget.  Quickly.

2.  Have the training facility load your data!   If the training is done off-site, people will be overwhelmed by the new environment – they’ll be even more lost if they do not see ‘their’ Stock Code, Suppliers, Customers, etc.

3.  Have the training facility print and copy a few screenshots.  Picture = 1000 words.  Trainees can then make notes on these and take them back to the office.  Save the cost of having a (traditional) training manual printed – nobody looks at them.. ever.

4.  Ensure that the trainer show you how to reverse an entry.  (Or fix a punching error).  This causes 90% of go-live chaos:  users do not know how to fix their own mistakes.

5.  It is not often that a company gets the chance to investigate their business processes as thuroughly as what usually happens during a ERP implementation and training sessions.  Make use of this opportunity to improve and streamline your business!


Most handy MS SQL query – ever

Find any field name in a MS SQL db – matching your search string (e.g. ‘perator’, as in ‘Operator’ or ‘operator’):

FROM sysobjects
JOIN syscolumns ON =
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype=’U’ and like ‘%perator%’

ORDER BY,syscolumns.colid

Change the ‘’ to ‘’ to search on table names.


Handy when learning a new system!

Pastel Partner Table names, Descriptions and File names

Herewith a list of the most commonly used Pastel Partner Table names, a short description of each and the corresponding file name.

Table Name Description  Filename
AccountUser Passwords, User Settings accuser.dat
ActivityMaster Time & Billing Activities accactiv.dat
AnalysisCodes Receipt Analysis Codes accranal.dat
BankDetails Receipting Bank Details accbnk.dat
BICUsers BI Centre users ACCBIC.dat
BillingParameters Time & Billing Parameters accprmts.dat
BOMHeader Bill Of Material Header accbomh.dat
BOMLines Bill Of Material Lines accboml.dat
CashBookCurrency Foreign Cash Books / Currency Balances acccbcur.dat
CostCodes Cost Codes accjcode.dat
CurrencyFile Foreign Currency accfcurr.dat
CustomerCategories Customer Categories accdcat.dat
CustomerDiscount Volume Based prices accdisc.dat
CustomerMaster Customer Master AccMasD.dat
CustSuppParameters Customer / Supplier Parameters accprmdc.dat
DeliveryAddresses Customer Delivery Address accdeliv.dat
EmployeeActivities Time & Billing Employee Activities accempac.dat
EmployeeMaster Time & Billing Employees accemp.dat
Euro Euro Code acceuro.dat
FinancialCategories Financial Categories accfcat.dat
Fonts Printer / Font Setup accfont.dat
HistoryHeader History of Document (e.g. Inv/Crn) Headers acchisth.dat
HistoryLines Document Lines, Inventory History acchistl.dat
HistoryTax Tax History acchtax.dat
HistoryUserDesc User (Transaction) History Descriptions acchistu.dat
HistoryUserSetup User History Settings acchistd.dat
InternetBank Internet Banking Cashbook accbank.dat
InternetBenf Internet Banking Beneficiaries accbenf.dat
Inventory Inventory Master accprd.dat
InventoryCategory Inventory Categories accicat.dat
InventoryGroups Inventory Group accgrp.dat
InventoryLink Store Linking accstklk.dat
InventoryParameters Inventory Parameters accprmst.dat
JumpUserAccess JumpUserAccess ACCUSERACCESS.DAT
LedgerBalances General Ledger Balances ACCMASB.DAT
LedgerMaster General Ledger Master accmas.dat
LedgerParameters General Ledger Parameters accprmgl.dat
LedgerTransactions GL, Customer, Supplier Transactions acctrn.dat
LinkHeader Link Header acclinkh.dat
LinkLines Link Lines acclinkl.dat
MenuAccess Menu Access ACCMACCESS.dat
MultiStore Multi Store accstore.dat
MultiStoreTrn Multi Store Transactions accstkst.dat
Notes Notes accnote.dat
OpenItem Open Items accoi.dat
POSAudit Point of Sale Audit posaudit.dat
POSCashUp Point of Sale Cashup poscash.dat
POSPaidOuts Point of Sale Paid Outs pospo.dat
POSPayments Point of Sale Payments pospaymt.dat
POSSetup Point of Sale Setup accprmps.dat
PreferredSupplier Preffered Supplier accstkps.dat
ProcessOptions Process Options ACCUSROPT.DAT
ReceiptTransactions Receipt Transactions accrecpt.dat
ReportCategories Report Categories accrcat.dat
ReportExport Report Export accrwexp.dat
ReportWriter Report Writer accrepw.dat
SalesmanMaster Salesman Master accsale.dat
SerialMaster Serial Master accmassn.dat
SerialTransaction Serial Transactions acctrnsn.dat
SupplierCategories Supplier Categories ACCCCAT.DAT
SupplierMaster Supplier Master accmasc.dat
SystemParameters System Parameters accprmsy.dat
TaxBox Tax Box acctbox.dat
TaxDescription Tax Description acctdesc.dat
TaxParameters Tax Parameters accprmtx.dat
TimesheetHead Timesheet Header acctimeh.dat
TimesheetLines Timesheet Lines acctimel.dat
Unposted Unposted accprdq.dat
UserGroups User Groups ACCUSRGRP.DAT
UserStore User Store accusstr.dat

Let me know if you need to link these or do a custom Pastel report from them!