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.

Note the blank Cheque and Bank fields – this is unique to a Contra transaction.

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:

 SELECT DISTINCT h.SalesOrder,
 h.ShipAddress1,
 d.MWarehouse,
 d.MStockCode,
 d.MStockDes,
 d.MOrderQty,
 d.MShipQty
 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.

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>%’;

You’ve got table #t1:

But you want:

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;

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!

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!

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

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

I linked a IBM Informix Dynamic Server Version 9.40.UC4 on SuSE SLES 8 server – with Microsoft SQL Server Standard (64-bit) on Windows Server 2008 R2 Standard:

1.  Get the latest IBM Informix Client SDK  from here (I used clientsdk.3.70.FC7DE.WIN) and install on your Microsoft Server.

If you are installing the 32 bit version onto a 64 bit machine, you might need to change the SysWOW64 path:   open a comand prompt from the folder where the Informix SDK install is and enter:

set PATH=C:\WINDOWS\SysWOW64\;%PATH%

2.  Add your host in C:/Windows/System32/drivers/etc/hosts (at the very bottom), e.g.  <your server IP>  <your server name>

3.  Similarly, add “sqlexec  1526/tcp#” at the very bottom of C:/Windows/System32/drivers/etc/services.  Note the # at the end.

Now simply call your remote db – from the MS SQL side – as follows:

SELECT * FROM [LinkedServer].[RemoteDatabase].[User/Owner].[Table]