Stock valuation - comparing with WH valuation
/*Now check the avg cost, against the inventory valuation*/
-- by WH.
Select
ItemCode =S.Code,
WHCode = W.Code,
ItemDescription1 =S.Description_1,
ItemDescription2 =S.Description_2,
ItemGroupCode =G.StGroup,
ItemGroupDesc = G.Description,
ItemWHGroupCode = WS.WHStockGroup,
WS.fAverageCost as WHAvgCost,
WS.WHQtyOnHand as WHQtyOnHand,
Qty =
SUM(CASE WHEN P.Id = 'JCI' THEN 0 --JCI invoices directly invoice out, so doesn't affect the available stock qty
WHEN P.Id = 'JCS' THEN 0 --JCS is always 0 anyway on the quantity column, so this shouldn't affect anything.
/*JCM -> standard rule applies on qty */
WHEN P.Id NOT IN ('JCI','JCS') AND Debit > 0 THEN Quantity
ELSE -Quantity END)
--,StockValue =SUM( ROUND(Debit,2)-ROUND(Credit,2))
,SV = SUM(CASE
WHEN P.Id NOT IN ('OInv', 'Crn','JCS','JCM','OGrv','JCI') THEN ROUND(P.Debit,2)-ROUND(P.Credit,2)
WHEN P.Id = 'JCM' THEN P.Debit --note:exclude any credits! they are crediting the WIP acc, not the Stock acc.
WHEN P.Id = 'JCS' THEN ROUND(P.Credit,2)-ROUND(P.Debit,2)
WHEN P.Id = 'OGrv' AND P.JobCodeLink = 0 THEN ROUND(P.Debit,2)-ROUND(P.Credit,2)
WHEN P.Id = 'OGrv' AND P.JobCodeLink > 0 THEN 0 --if job code link is not 0, then the stock goes straight to WIP (so not stock account).
WHEN P.Debit > 0 AND P.Id IN ('OInv', 'Crn') THEN P.Quantity*P.Cost
WHEN P.Credit > 0 AND P.Id IN ('OInv', 'Crn') THEN -P.Quantity*P.Cost
WHEN P.Id = 'JCI' THEN 0 -- JCI invoices get their cost from the WIP account, therefore 0 influence on stock account
ELSE 0 END)
, WS.fAverageCost*WS.WHQtyOnHand as WHValuation
,[Difference] = WS.fAverageCost*WS.WHQtyOnHand -
SUM(CASE
WHEN P.Id NOT IN ('OInv', 'Crn','JCS','JCM','OGrv','JCI') THEN ROUND(P.Debit,2)-ROUND(P.Credit,2)
WHEN P.Id = 'JCM' THEN P.Debit --note:exclude any credits! they are crediting the WIP acc, not the Stock acc.
WHEN P.Id = 'JCS' THEN ROUND(P.Credit,2)-ROUND(P.Debit,2)
WHEN P.Id = 'OGrv' AND P.JobCodeLink = 0 THEN ROUND(P.Debit,2)-ROUND(P.Credit,2)
WHEN P.Id = 'OGrv' AND P.JobCodeLink > 0 THEN 0 --if job code link is not 0, then the stock goes straight to WIP (so not stock account).
WHEN P.Debit > 0 AND P.Id IN ('OInv', 'Crn') THEN P.Quantity*P.Cost
WHEN P.Credit > 0 AND P.Id IN ('OInv', 'Crn') THEN -P.Quantity*P.Cost
WHEN P.Id = 'JCI' THEN 0 -- JCI invoices get their cost from the WIP account, therefore 0 influence on stock account
ELSE 0 END)
from PostST P
LEFT JOIN StkItem S ON P.AccountLink = S.StockLink
LEFT JOIN GrpTbl G ON S.ItemGroup = G.StGroup
LEFT JOIN WhseMst W on WhseLink = P.WarehouseID
LEFT JOIN WhseStk WS ON WS.WHStockLink = P.AccountLink AND WS.WHWhseID = P.WarehouseID
WHERE P.TxDate <='2019/12/31'
AND S.ServiceItem = 0
GROUP BY
S.Code,
W.Code,
S.Description_1,
S.Description_2,
S.ItemGroup,
G.StGroup,
G.Description
,WS.fAverageCost,WS.WHQtyOnHand, WS.WHStockGroup
HAVING
ABS(WS.fAverageCost*WS.WHQtyOnHand -
SUM(CASE
WHEN P.Id NOT IN ('OInv', 'Crn','JCS','JCM','OGrv','JCI') THEN ROUND(P.Debit,2)-ROUND(P.Credit,2)
WHEN P.Id = 'JCM' THEN P.Debit --note:exclude any credits! they are crediting the WIP acc, not the Stock acc.
WHEN P.Id = 'JCS' THEN ROUND(P.Credit,2)-ROUND(P.Debit,2)
WHEN P.Id = 'OGrv' AND P.JobCodeLink = 0 THEN ROUND(P.Debit,2)-ROUND(P.Credit,2)
WHEN P.Id = 'OGrv' AND P.JobCodeLink > 0 THEN 0 --if job code link is not 0, then the stock goes straight to WIP (so not stock account).
WHEN P.Debit > 0 AND P.Id IN ('OInv', 'Crn') THEN P.Quantity*P.Cost
WHEN P.Credit > 0 AND P.Id IN ('OInv', 'Crn') THEN -P.Quantity*P.Cost
WHEN P.Id = 'JCI' THEN 0 -- JCI invoices get their cost from the WIP account, therefore 0 influence on stock account
ELSE 0 END)
) > 0.50
Order by S.Code
Total stock valuation - at a date - by GL (from PostST)
-----------------------------------------------------------------
/*TOTAL STOCK VALUATION - BY STOCK GL ACCOUNT - AT A DATE...*/
-----------------------------------------------------------------
GO
DECLARE @ValuationDate datetime = '2019-12-31'
DECLARE @DefaultStockAccID int
SELECT @DefaultStockAccID = (SELECT Account2Link FROM TrCodes WHERE idTrCodes = 37)
Select
A.Master_Sub_Account as GLAccountCode,
A.Description as GLAccountDesc,
SV = SUM(CASE
WHEN P.Id NOT IN ('OInv', 'Crn','JCS','JCM','OGrv','JCI') THEN ROUND(P.Debit,2)-ROUND(P.Credit,2)
WHEN P.Id = 'JCM' THEN P.Debit --note:exclude any credits! they are crediting the WIP acc, not the Stock acc.
WHEN P.Id = 'JCS' THEN ROUND(P.Credit,2)-ROUND(P.Debit,2)
WHEN P.Id = 'OGrv' AND P.JobCodeLink = 0 THEN ROUND(P.Debit,2)-ROUND(P.Credit,2)
WHEN P.Id = 'OGrv' AND P.JobCodeLink > 0 THEN 0 --if job code link is not 0, then the stock goes straight to WIP (so not stock account).
WHEN P.Debit > 0 AND P.Id IN ('OInv', 'Crn') THEN P.Quantity*P.Cost
WHEN P.Credit > 0 AND P.Id IN ('OInv', 'Crn') THEN -P.Quantity*P.Cost
WHEN P.Id = 'JCI' THEN 0 -- JCI invoices get their cost from the WIP account, therefore 0 influence on stock account
ELSE 0 END)
from PostST P
LEFT JOIN StkItem S ON P.AccountLink = S.StockLink
LEFT JOIN GrpTbl G2 ON S.ItemGroup = G2.StGroup
LEFT JOIN WhseMst W on WhseLink = P.WarehouseID
LEFT JOIN WhseStk WS ON WS.WHStockLink = P.AccountLink AND WS.WHWhseID = P.WarehouseID
LEFT JOIN GrpTbl G ON WS.WHStockGroup = G.StGroup
LEFT JOIN Accounts A ON COALESCE(G.StockAccLink,@DefaultStockAccID) = A.AccountLink
LEFT JOIN _etblGLAccountTypes ATY ON A.iAccountType = ATY.idGLAccountType
WHERE P.TxDate <= @ValuationDate
AND S.ServiceItem = 0
GROUP BY
A.Master_Sub_Account,
A.Description
Order by A.Master_Sub_Account
Check for non-stock transactions on stock account(s)
/*CHECK for non-stock transactions on stock account*/
GO
DECLARE @StockAccID TABLE (ID int); INSERT INTO @StockAccID (ID) VALUES (4379);
SELECT * FROM PostGL P
WHERE P.AccountLink IN (SELECT ID FROM @StockAccID)
AND Id IN ('APTx','TaxAP','JL')
Stock valuation - updated with job costing crap
Select
ItemCode =S.Code,
ItemDescription1 =S.Description_1,
ItemDescription2 =S.Description_2,
ItemGroupCode =G.StGroup,
ItemGroupDesc = G.Description,
Qty =
SUM(CASE
WHEN Debit > 0 THEN Quantity
ELSE -Quantity
END)
--,StockValue =SUM( ROUND(Debit,2)-ROUND(Credit,2))
,SV = SUM(CASE
WHEN PST.Id NOT IN ('OInv', 'Crn','JCS','JCM','OGrv','JCI') THEN ROUND(PST.Debit,2)-ROUND(PST.Credit,2)
WHEN PST.Id = 'JCM' THEN PST.Debit --note:exclude any credits! they are crediting the WIP acc, not the Stock acc.
WHEN PST.Id = 'JCS' THEN ROUND(PST.Credit,2)-ROUND(PST.Debit,2)
WHEN PST.Id = 'OGrv' AND PST.JobCodeLink = 0 THEN ROUND(PST.Debit,2)-ROUND(PST.Credit,2)
WHEN PST.Id = 'OGrv' AND PST.JobCodeLink > 0 THEN 0 --if job code link is not 0, then the stock goes straight to WIP (so not stock account).
WHEN PST.Debit > 0 AND PST.Id IN ('OInv', 'Crn') THEN PST.Quantity*PST.Cost
WHEN PST.Credit > 0 AND PST.Id IN ('OInv', 'Crn') THEN -PST.Quantity*PST.Cost
WHEN PST.Id = 'JCI' THEN 0 -- JCI invoices get their cost from the WIP account, therefore 0 influence on stock account
ELSE 0 END)
from PostST P
LEFT JOIN StkItem S ON P.AccountLink = S.StockLink
LEFT JOIN GrpTbl G ON S.ItemGroup = G.StGroup
LEFT JOIN WhseMst W on WhseLink = P.WarehouseID
WHERE P.TxDate <='2015/12/31'
AND S.ServiceItem = 0
GROUP BY
S.Code,
S.Description_1,
S.Description_2,
S.ItemGroup,
G.StGroup,
G.Description
Order by S.Code
Stock valuation - query v1
Select
ItemCode =S.Code,
ItemDescription1 =S.Description_1,
ItemDescription2 =S.Description_2,
ItemGroupCode =G.StGroup,
ItemGroupDesc = G.Description,
Qty =
SUM(CASE
WHEN Debit > 0 THEN Quantity
ELSE -Quantity
END)
--,StockValue =SUM( ROUND(Debit,2)-ROUND(Credit,2))
,SV = SUM(CASE
WHEN P.Id NOT IN ('OInv', 'Crn') THEN ROUND(P.Debit,2)-ROUND(P.Credit,2)
WHEN Debit > 0 AND P.Id IN ('OInv', 'Crn') THEN P.Quantity*P.Cost
WHEN Credit > 0 AND P.Id IN ('OInv', 'Crn') THEN -P.Quantity*P.Cost
ELSE 0 END)
from PostST P
LEFT JOIN StkItem S ON P.AccountLink = S.StockLink
LEFT JOIN GrpTbl G ON S.ItemGroup = G.StGroup
LEFT JOIN WhseMst W on WhseLink = P.WarehouseID
WHERE P.TxDate <='2015/12/31'
AND S.ServiceItem = 0
GROUP BY
S.Code,
S.Description_1,
S.Description_2,
S.ItemGroup,
G.StGroup,
G.Description
Order by S.Code
Stock valuation - incl avg cost and by WH
Select
ItemCode =S.Code,
ItemDescription1 =S.Description_1,
ItemDescription2 =S.Description_2,
ItemGroupCode =G.StGroup,
ItemGroupDesc = G.Description,
W.Code as WHCode,
Qty =
SUM(CASE
WHEN Debit > 0 THEN Quantity
ELSE -Quantity
END)
--,StockValue =SUM( ROUND(Debit,2)-ROUND(Credit,2))
,SV = SUM(CASE
WHEN PST.Id NOT IN ('OInv', 'Crn','JCS','JCM','OGrv','JCI') THEN ROUND(PST.Debit,2)-ROUND(PST.Credit,2)
WHEN PST.Id = 'JCM' THEN PST.Debit --note:exclude any credits! they are crediting the WIP acc, not the Stock acc.
WHEN PST.Id = 'JCS' THEN ROUND(PST.Credit,2)-ROUND(PST.Debit,2)
WHEN PST.Id = 'OGrv' AND PST.JobCodeLink = 0 THEN ROUND(PST.Debit,2)-ROUND(PST.Credit,2)
WHEN PST.Id = 'OGrv' AND PST.JobCodeLink > 0 THEN 0 --if job code link is not 0, then the stock goes straight to WIP (so not stock account).
WHEN PST.Debit > 0 AND PST.Id IN ('OInv', 'Crn') THEN PST.Quantity*PST.Cost
WHEN PST.Credit > 0 AND PST.Id IN ('OInv', 'Crn') THEN -PST.Quantity*PST.Cost
WHEN PST.Id = 'JCI' THEN 0 -- JCI invoices get their cost from the WIP account, therefore 0 influence on stock account
ELSE 0 END)
, SVAvgCost = CASE WHEN SUM(CASE WHEN Debit > 0 THEN Quantity ELSE -Quantity END) <> 0 THEN
SUM(CASE
WHEN PST.Id NOT IN ('OInv', 'Crn','JCS','JCM','OGrv','JCI') THEN ROUND(PST.Debit,2)-ROUND(PST.Credit,2)
WHEN PST.Id = 'JCM' THEN PST.Debit --note:exclude any credits! they are crediting the WIP acc, not the Stock acc.
WHEN PST.Id = 'JCS' THEN ROUND(PST.Credit,2)-ROUND(PST.Debit,2)
WHEN PST.Id = 'OGrv' AND PST.JobCodeLink = 0 THEN ROUND(PST.Debit,2)-ROUND(PST.Credit,2)
WHEN PST.Id = 'OGrv' AND PST.JobCodeLink > 0 THEN 0 --if job code link is not 0, then the stock goes straight to WIP (so not stock account).
WHEN PST.Debit > 0 AND PST.Id IN ('OInv', 'Crn') THEN PST.Quantity*PST.Cost
WHEN PST.Credit > 0 AND PST.Id IN ('OInv', 'Crn') THEN -PST.Quantity*PST.Cost
WHEN PST.Id = 'JCI' THEN 0 -- JCI invoices get their cost from the WIP account, therefore 0 influence on stock account
ELSE 0 END) /
SUM(CASE WHEN Debit > 0 THEN Quantity ELSE -Quantity END)
ELSE 0 END
, WS.fAverageCost as WHAvgCost
from PostST P
LEFT JOIN StkItem S ON P.AccountLink = S.StockLink
LEFT JOIN GrpTbl G ON S.ItemGroup = G.StGroup
LEFT JOIN WhseMst W on WhseLink = P.WarehouseID
LEFT JOIN WhseStk WS ON P.WarehouseID = WS.WHWhseID AND P.AccountLink = WS.WHStockLink
WHERE P.TxDate <='2018-12-31'
AND S.ServiceItem = 0
AND S.StockLink = 53
GROUP BY
S.Code,
W.Code,
S.Description_1,
WS.fAverageCost,
S.Description_2,
S.ItemGroup,
G.StGroup,
G.Description
Order by S.Code