I was tasked to project manage the incorporation of a newly acquired company into our group.

Apart from arranging the infrastructure changes, systems setups and training required, I wrote the SQL (Jobs) to migrate data from Pastel Evolution to Syspro.

Herewith a link between some of the main Syspro tables and their counterparts in Pastel Evolution:

A very handy Evolution table worth mentioning is TrCodes. Link this with PostAP and PostAR to provide you with the whole spectrum of AP and AR postings.

I’ve come to know these table names by heart (by using them over the last 13 years) –  and (still) frequently use them in sentences..

This is more for me to remember.

Informix > Sage Line 500 name:

bmhstm > BOM History
bmwodm > BOM Detail
bmwohm > BOM Header
cbbankm > Cash Bank file
cbbatchm > Cash Batches file
cbtranm > Cash Transactions file
ceaddrm > Customer (shipper_code1) Addresses
cecaldrm > (Financial) Calendar
cenatypem > Narrative Types
cevatregm > VAT Register
nlaccm > Nominal Account File
nlmastm > Nominal Master File
nltranm > Nominal (GL) Transactions
opaudm > Sales Order Audit File
opcosm > Cost Of Sales Matrix
opdetm > Sales Order Detail
opheadm > Sales Order Header
oplastprm > Sales Order Last Price
oplistm > (Sales Order) Price Lists
opsahistm > Sales History Transactions
opservm > Sales Order Services
plitemm > Purchase Ledger Items
plpaym > AP Payments
plsuppm > AP Suppliers (Creditors)
podetm > Purchase Order Detail
poheadm > Purchase Order Header
porecp2m > Purchase Order Receipts
porecpm > Purchase Order Receipts
slbankm > Customers (Debtors) Bank
slcashm > Customers (Debtors) Cash
slcustm > Customers (Debtors) Master File
slitemm > Customers (Debtors) Transactions
stallocm > Stock Allocation (to Sales Orders)
stkhstm > Stock Transactions
stkwhm > Stock Warehouse Master File
stockm > Stock Master File
stquem > Stock Batches
sysdirm > System Keys

Let me know if you need to write a report on these.

Make a note of the output of onstat -d to monitor the effect of your actions.

Ensure exclusive access – preferably with Informix transaction logging off.

Informix transaction logging off:  ondblog nolog <database name>            then      onbar -b -F

As always, make sure you’ve got a decent backup before you start.

Log into Sage – into the Company you need to reduce the space of.  Exit to shell.  You should now be in the company directory by default (check System Manager > System Administration > Company Management > Working Directories).

Test a small table first, e.g.:

unload st_warehouse > stkwhm.unl

Now make sure the unload file (in this case stkwhm.unl) contains the field names at the top and data below it.  Then do

load -cD -x 10000 st_warehouse < stkwhm.unl

The ‘10000’ can be adjusted according the the number of lines loaded at a time – increase for larger tables.

Be sure to update statistics once done!  E.g:

update statistics for table stkwhm;

And lastly, remember to switch Informix transaction logging back on.

Informix transaction logging on:  ondblog buf <database name>            then      onbar -b -F

Here’s the first test:

SELECT transaction_group, SUM(amount) amnt FROM slitemm WHERE slyear = “2013” AND period = “01” INTO TEMP slitemtmp;

SELECT transaction_group, SUM(journal_amount) amnt FROM nltranm WHERE nlyear = “13” AND trans_period = “01”  AND posting_code = <your Debtors Control Account here> INTO TEMP nltranmtmp;

SELECT s.transaction_group, s.amnt, n.amnt FROM slitemtmp s, nltrantmp n WHERE s.transaction_group = n.transaction_group AND s.amnt <> n.amnt;

Use the resulting transaction group to identify which transactions in slitemm doesn’t match the corresponding transaction in nltranm.  If the above returns nothing, then use slxrefm to check if the allocations was done correctly.

The two should rarely differ.  I had an instance where the system key LOCALDEC was (temporarily) set to 1, then changed back to 2 – resulting in a decimal difference.

While going through the General ledger Year End procedure – I came across the following error while running ‘Close Adjustment Period’:

“Number of Accounts with incorrect balances : 7056 see System log”

The system log shows the following (sample):

%% invalid_nldirect %% posting <your posting_code or account_code here>

%% invalid_nlsumm %% posting <your posting_code or account_code here>

%% invalid_nlstatus %% posting <your posting_code or account_code here>

Turns out that the fields direct_posting, summary_posting and status in tables nl_account (nlaccm) and nl_post_code (nlmastm) only likes Y/N in caps, not lower case – or blanks.

Today I found that the Sage Line 500 table plpaym (P/L Proposed Payments) uses the item and our_reference field in the first line of the table (where seq = ” “) as date fields (must be dd/mm/yy format) to validate posting to the right Calendar period.. (!)

The following is a useful query for extracting the current (active) promotional prices from a Sage Line 500 price list table (oplistm) – where (say) ‘MN’ is the main selling Finished Goods warehouse in stockm and all prices should be shown as inclusive of VAT (14%):

select p.price_list, p.product_code, s.long_description, round(s.price*1.14,2) normal_price,
CASE
WHEN p.vat_inclusive_flag = “Y” THEN p.price
WHEN p.vat_inclusive_flag = “N” THEN round(p.price*1.14,2)
END promo_price,
p.price_start_date,
p.price_end_date
FROM oplistm p, stockm s
WHERE p.product_code = s.product AND s.warehouse = “MN”
AND p.sequence_number <> ”  ”
AND p.price_start_date <=today
AND p.price_end_date >= today
ORDER BY p.product_code, p.price_start_date;

You’re busy with a Cash Batch and your PC goes off or you’re disconnected from the server for some reason.  Now when you want to re-access the batch – it is locked?

From the database side:  DELETE FROM cblockm WHERE batch_no = <your_batch_no>;

For years I’ve had trouble finding the correct Informix table (name) used by Sage Line 500’s Data Dictionary – until I discovered this nifty trick:

Note:  Requires one to have Demo/Test company installed – accessing a database differently named to the live/production db.  I work in a SuSE Linux Enterpise Server (SLES) environment.

1.  Access the desired Sage screen/window.  (e.g.   if you’d like to know which table stores Sales Order Processing order detail – then go into any example order’s detail screen/window). 

2.  Open another session, log in as Infomix and run ‘onstat  -g sql’.  This will show all current database(s) threads.  Most of these will (might) relate to the production db’s processes – so look for the process with the Demo/Test db’s name in it.  Make a note of the process number (integer number on the left).

3.  As Informix, run ‘onstat -g sql <process number>’  (e.g.  ‘onstat -g sql 16342’) and look for the table name in the output (usually after ‘from’). 

Let me know if problems!