/*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 - 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*/
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')

 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