Business Analysis, BI and Reporting, Project Management
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>%’;
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:
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]
In Syspro, it is very handy to add as many custom fields as you want – but then to report on these fields could be a pain.
I found it easiest to create a view based on AdmFormdata (where the custom field data are stored) – and then report using only the view.
For example, if you are storing and capturing stock dimensions (Length, Width, Height, etc) – your AdmFormdata might look like this:
– ‘FormType’ indicates the data is connected to Inventory (InvMaster) and will always be ‘STK’.
– ‘KeyField’ contains the InMaster primary key = StockCode (in this case two stock codes A100 and A201)
– ‘FieldName’ is the (user defined) fieldname e.g. DIM_L = Length, DIM_W = Width, DIM_H = Height and DIM_M = Weight (Mass)
– ‘AlphaValue’ not used in this case – but can be used for text/alphanumeric values.
– ‘NumericValue’ is the field value.
Now I would create the following simple view:
CREATE VIEW StockCodeDimensions
SELECT KeyField AS StockCode,
MAX(CASE WHEN FieldName = ‘DIM_L’
ELSE NULL END) AS Length,
MAX(CASE WHEN FieldName = ‘DIM_W’
ELSE NULL END) AS Width,
MAX(CASE WHEN FieldName = ‘DIM_H’
ELSE NULL END) AS Height,
MAX(CASE WHEN FieldName = ‘DIM_M’
ELSE NULL END) AS Weight
WHERE FormType = ‘STK’ AND (FieldName LIKE ‘DIM%’)
GROUP BY KeyField;
Resulting in the following table: (SELECT * FROM StockCodeDimensions)
Note that I used ” FieldName LIKE ‘DIM% ” in my query above. This can simply be modified/copied to create a view for (say) ‘ANA’ if analysis codes are used to specify (eg) stock code category, colour, age group, chemical restriction, etc.