Syspro 7 Assets: Tips and tricks

I’ve recently implemented Assets in Syspro 7 and would like to share some of what I learnt:

 

Start with Branches, Cost Centers and Asset Groups

If you need to produce financial statements for various Branches to Cost Center level, make sure your (structured) GlCodes cater for this level of granularity.  You’ll use these GlCodes in the integration setup.

 

Bulk create GlCodes

I used the  AssetIntegDet table, which I designed in Excel first, as the basis to see which (new) GlCodes were required.  Since I had to create over 860 new GlCodes, I uploaded the GlCode and Description into a SQL Server temp table and bulk inserted the GlCodes using a ‘standard’ Expense GlCode as template.

 

Coding of Assets

Of the various Asset numbering/coding methodologies available, I decided to use:  Asset Type  + Acquisition year + Acquisition month + a sequential number.   e.g. ‘PM201703001’ for the 1st ‘Plant and Machinery’ item acquired in March 2017.  This way, one may move an asset to any Branch and/or Cost Center and/or Location – without changing the asset number.

 

Excel Asset master and acquisition book

Initially I thought I’d write SQL code to ‘transfer’ asset master data (including acquisitions) from the ‘test’ to ‘live’ environment (ensuring a quick take-on) – but since a whole set of tables are touched during asset creation and acquisition, I decided to rather use the Syspro V7 ‘copy-and-paste’ method.  It turned out to be a very quick and audit-able method.   Even if you’re doing over 2500 assets in one go.  (It took me around 40 minutes from start to end, basically just waiting for Syspro to write amendment journals, etc.)  I suggest keeping the asset master and acquisition sheets in exactly the same (sorted by Asset number) order.

 

Method to ‘reset’ Assets

During the test phase, it helped a lot to have a method to ‘reset’ everything and start from scratch.  I used a SQL SP that deletes the relevant Asset and GL tables.

 

Use SQL SPs for reporting

I had to report (in Excel) asset transactions and values on around 35 x Branches, 38 x Cost Centers and 242 Locations.  Instead of ‘hard coding’ these, using a parameterised SP saved several hours.

 

Best time:  start of new financial year

Its just best for balances and change management.

 

Getting the first day of the next six months – using DATEFROMPARTS

It is always tempting just to MONTH(GETDATE())+1 – but then there’s the end of the year to consider.

DECLARE @ThisMonth DATE = (SELECT DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1));

DECLARE @NextMonth DATE = (SELECT
CASE
WHEN MONTH(@ThisMonth) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@ThisMonth)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@ThisMonth),MONTH(@ThisMonth)+1,1))
END);

DECLARE @NextMonth1 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth),MONTH(@NextMonth)+1,1))
END);

DECLARE @NextMonth2 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth1) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth1)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth1),MONTH(@NextMonth1)+1,1))
END);

DECLARE @NextMonth3 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth2) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth2)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth2),MONTH(@NextMonth2)+1,1))
END);

DECLARE @NextMonth4 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth3) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth3)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth3),MONTH(@NextMonth3)+1,1))
END);

SELECT @ThisMonth
UNION
SELECT @NextMonth
UNION
SELECT @NextMonth1
UNION
SELECT @NextMonth2
UNION
SELECT @NextMonth3
UNION
SELECT @NextMonth4

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 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:

SELECT

DISTINCT t2.InvoiceNumber,

SUBSTRING((Select‘,’+RTRIM(t1.StockCode)AS[text()]

FROM #t1 t1

WHERE t1.InvoiceNumber = t2.InvoiceNumber

ORDER BY t1.InvoiceNumber,StockCode

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

FROM  #t1 t2;

 

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’):

SELECT table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype=’U’ and syscolumns.name like ‘%perator%’

ORDER BY sysobjects.name,syscolumns.colid

Change the ‘syscolumns.name’ to ‘sysobjects.name’ to search on table names.

 

Handy when learning a new system!