Hi,

I am trying to create a sql query that returns a list of all inventory items with their available qty.

By "qty available" I mean:

Qty On Hand (all units in all warehouses, wether in open sales orders or not)
Qty Sales Order (units in open sales orders)
Qty Available (Qty On Hand - Qty Sales Orders)

My query works, but I sometimes get discrepancies between what Pastel's application says is available and what I get back from reading the actual database:

SELECT
mst.ItemCode as "sku",
(
(
(mst.OpeningQty + mst.QtyBuyLast + mst.QtyAdjustLast - mst.QtySellLast) +
(mst.QtyBuyThis01 + mst.QtyBuyThis02 + mst.QtyBuyThis03 + mst.QtyBuyThis04 + mst.QtyBuyThis05 + mst.QtyBuyThis06 + mst.QtyBuyThis07 + mst.QtyBuyThis08 + mst.QtyBuyThis09 + mst.QtyBuyThis10 + mst.QtyBuyThis11 + mst.QtyBuyThis12 + mst.QtyBuyThis13) +
(mst.QtyAdjustThis01 + mst.QtyAdjustThis02 + mst.QtyAdjustThis03 + mst.QtyAdjustThis04 + mst.QtyAdjustThis05 + mst.QtyAdjustThis06 + mst.QtyAdjustThis07 + mst.QtyAdjustThis08 + mst.QtyAdjustThis09 + mst.QtyAdjustThis10 + mst.QtyAdjustThis11 + mst.QtyAdjustThis12 + mst.QtyAdjustThis13) -
(mst.QtySellThis01 + mst.QtySellThis02 + mst.QtySellThis03 + mst.QtySellThis04 + mst.QtySellThis05 + mst.QtySellThis06 + mst.QtySellThis07 + mst.QtySellThis08 + mst.QtySellThis09 + mst.QtySellThis10 + mst.QtySellThis11 + mst.QtySellThis12 + mst.QtySellThis13)
) -
(
SELECT SUM(QtyLeft)
FROM HistoryLines where Documenttype = 102 and ItemCode = mst.ItemCode
)
) AS "qty",
mst.SellExcl10 as "price"
FROM MultiStoreTrn mst

It seems the discrepancies occur when the inventory item belongs to more than one inventory group.

Does this query look correct?

Can anyone help?

Views: 481

Reply to This

Replies to This Discussion

hey chris,

did you ever get this sorted?

No I have not yet got this sorted

Hi Chris, may i ask why you don't use the Inventory Master report for pastel partner ?  It should have the correct join syntax you would need to even base a new report of aswell. 

Hi Nigel,

I am not following this exactly?

What is the "inventory master" report?

Its a standard  inventory report that ships with pastel partner.  what version of pastel partner are you using? 

Version 11.

But I need to access it programatically?

you would need access to pastel BIC.

Can you explain what tables this would be and how I would generate the sql?

see below example of what it looks like

SELECT
IFNULL("MultiStoreTrn"."StoreCode",' '),
IFNULL("Multistore"."Description",' '),
IFNULL("MultiStoreTrn"."InvGroup",0),
IFNULL("InventoryGroups"."Description",'None'),
IFNULL("InventoryCategory"."ICCode",'000'),
IFNULL("InventoryCategory"."ICDesc",'None'),
"MultiStoreTrn"."Bin",
"MultiStoreTrn"."ItemCode",
RTRIM("Inventory"."Description"),
RTRIM("MultiStoreTrn"."ItemCode") + ' - ' + RTRIM("Inventory"."Description"),
ROUND(IFNULL("MultiStoreTrn"."OpeningQty",0) + IFNULL("MultiStoreTrn"."QtyBuyLast",0) + IFNULL("MultiStoreTrn"."QtyAdjustLast",0) - IFNULL("MultiStoreTrn"."QtySellLast",0),4),
ROUND(IFNULL("MultiStoreTrn"."QtyBuyThis01",0) + IFNULL("MultiStoreTrn"."QtyBuyThis02",0) + IFNULL("MultiStoreTrn"."QtyBuyThis03",0) + IFNULL("MultiStoreTrn"."QtyBuyThis04",0),4),
ROUND(IFNULL("MultiStoreTrn"."QtyBuyThis05",0) + IFNULL("MultiStoreTrn"."QtyBuyThis06",0) + IFNULL("MultiStoreTrn"."QtyBuyThis07",0) + IFNULL("MultiStoreTrn"."QtyBuyThis08",0),4),
ROUND(IFNULL("MultiStoreTrn"."QtyBuyThis09",0) + IFNULL("MultiStoreTrn"."QtyBuyThis10",0) + IFNULL("MultiStoreTrn"."QtyBuyThis11",0) + IFNULL("MultiStoreTrn"."QtyBuyThis12",0) +
IFNULL("MultiStoreTrn"."QtyBuyThis13",0),4),
'=Round(QtyBuyThis1to4+QtyBuyThis5to8+QtyBuyThis9to13,4)',
ROUND(IFNULL("MultiStoreTrn"."QtyAdjustThis01",0) + IFNULL("MultiStoreTrn"."QtyAdjustThis02",0) + IFNULL("MultiStoreTrn"."QtyAdjustThis03",0) + IFNULL("MultiStoreTrn"."QtyAdjustThis04",0),4),
ROUND(IFNULL("MultiStoreTrn"."QtyAdjustThis05",0) + IFNULL("MultiStoreTrn"."QtyAdjustThis06",0) + IFNULL("MultiStoreTrn"."QtyAdjustThis07",0) + IFNULL("MultiStoreTrn"."QtyAdjustThis08",0),4),
ROUND(IFNULL("MultiStoreTrn"."QtyAdjustThis09",0) + IFNULL("MultiStoreTrn"."QtyAdjustThis10",0) + IFNULL("MultiStoreTrn"."QtyAdjustThis11",0) +
IFNULL("MultiStoreTrn"."QtyAdjustThis12",0) +
IFNULL("MultiStoreTrn"."QtyAdjustThis13",0),4),
'=Round(QtyAdjustThis1to4+QtyAdjustThis5to8+QtyAdjustThis9to13,4)',
ROUND(IFNULL("MultiStoreTrn"."QtySellThis01",0) + IFNULL("MultiStoreTrn"."QtySellThis02",0) + IFNULL("MultiStoreTrn"."QtySellThis03",0) + IFNULL("MultiStoreTrn"."QtySellThis04",0),4),
ROUND(IFNULL("MultiStoreTrn"."QtySellThis05",0) + IFNULL("MultiStoreTrn"."QtySellThis06",0) + IFNULL("MultiStoreTrn"."QtySellThis07",0) + IFNULL("MultiStoreTrn"."QtySellThis08",0),4),
ROUND(IFNULL("MultiStoreTrn"."QtySellThis09",0) + IFNULL("MultiStoreTrn"."QtySellThis10",0) + IFNULL("MultiStoreTrn"."QtySellThis11",0) +
IFNULL("MultiStoreTrn"."QtySellThis12",0) +
IFNULL("MultiStoreTrn"."QtySellThis13",0),4),
'=Round(QtySellThis1to4+QtySellThis5to8+QtySellThis9to13,4)',
'=Round(OpenSOH+TotalBuyQty+TotalAdjQty-TotalSellQty,2)',
"MultiStoreTrn"."CostThis12",
"MultiStoreTrn"."LastPurchAmt",
'=Round(Final_SOH*CostThis12,2)',
'=Round(Final_SOH*LatestCost,2)',
"Inventory"."UnitSize",
"MultiStoreTrn"."MaximumLevel",
'=IF(Final_SOH-MaximumLevel>0,Final_SOH-MaximumLevel,0)',
'=Over_Stocked_Qty*CostThis12',
'=Over_Stocked_Qty*LatestCost',
"MultiStoreTrn"."ReorderLevel",
'=IF(ReorderLevel-Final_SOH<0,0,ReorderLevel-Final_SOH)'
FROM (((("MultiStoreTrn"
INNER JOIN "MultiStore"
ON "MultiStoreTrn"."StoreCode" = "MultiStore"."StoreCode")
LEFT JOIN "InventoryGroups"
ON "MultiStoreTrn"."InvGroup" = "InventoryGroups"."InvGroup")
LEFT JOIN "Inventory"
ON "MultiStoreTrn"."ItemCode" = "Inventory"."ItemCode")
LEFT JOIN "InventoryCategory"
ON "Inventory"."Category" = "InventoryCategory"."ICCode")

WHERE
(IFNULL("MultiStoreTrn"."StoreCode",' ') >= 'DBN'
AND IFNULL("MultiStoreTrn"."StoreCode",' ') <= 'DBN'
AND IFNULL("InventoryCategory"."ICCode",'000') >= '001'
AND IFNULL("InventoryCategory"."ICCode",'000') <= '001'
)

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service