SQL - Inventory Valuation - investigation queries

--CHECK THESE ADDITIONAL LINES. NOT SURE IF THEY ARE CORRECT, AS CQuential WAS MAKING THEM! AND NOT SAGE!
            WHEN PST.Id NOT IN ('OInv', 'Crn','JCS','JCM','OGrv','JCI','MFMF','MFDR') THEN ROUND(PST.Debit,2)-ROUND(PST.Credit,2)
			WHEN PST.Id IN ('MFDR') AND PST.Quantity = 0 THEN 0 --doing the draw actions onto WIP
			WHEN PST.Id IN ('MFDR') AND PST.Quantity <> 0 THEN ROUND(PST.Debit,2)-ROUND(PST.Credit,2)
			WHEN PST.Id IN ('MFMF') THEN ROUND(PST.Debit,2)-ROUND(PST.Credit,2)

--FINDING INVENTORY DIFFERENCES! GL vs Valuation

DECLARE @FromDate datetime = '2019-04-04'
DECLARE @ToDate datetime = '2019-04-05'

DECLARE @StockGLAccountIDs TABLE (ID int)
DECLARE @DefaultStockAccountID int

--get all stock account codes, which are used for STOCK items.
SELECT @DefaultStockAccountID = (SELECT Account2Link FROM TrCodes WHERE idTrCodes = 37)
INSERT INTO @StockGLAccountIDs (ID)
	(SELECT Account2Link FROM TrCodes WHERE idTrCodes = 37
	UNION
	SELECT distinct G.StockAccLink FROM StkItem S
		LEFT JOIN GrpTbl G ON G.StGroup = S.ItemGroup
		WHERE S.ServiceItem = 0
	UNION
	SELECT distinct G.StockAccLink
		FROM WhseStk W
		LEFT JOIN StkItem S ON W.WHStockLink = S.StockLink
		LEFT JOIN GrpTbl G ON W.WHStockGroup = G.StGroup
		--WHERE S.ServiceItem = 0
		)

--just output the stock accounts in use:
/*
SELECT * FROM @StockGLAccountIDs S
	LEFT JOIN Accounts A ON S.ID = A.AccountLink
*/

/*--Get Transactions,
	 in the defined period
	 grouped by auditnumber
	 on the stock accounts
	 from PostGL
	 and from PostST
	 and sum the debit/credit value
	 */
SELECT cAuditNumber, AccountLink, SUM(DmC_GL) as DmC_GL,  SUM(DmC_ST) as DmC_ST
FROM (
	SELECT cAuditNumber, AccountLink, Debit-Credit as DmC_GL, 0 as DmC_ST FROM PostGL PGL
		WHERE PGL.AccountLink IN (SELECT ID FROM @StockGLAccountIDs)
			AND PGL.TxDate >= @FromDate AND PGL.TxDate <= @ToDate
	UNION ALL
	SELECT cAuditNumber, COALESCE(G.StockAccLink,@DefaultStockAccountID), 0 as DmC_GL, DmC_ST = 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 PST
		LEFT JOIN WhseStk W ON PST.AccountLink = W.WHStockLink AND PST.WarehouseID = W.WHWhseID
		LEFT JOIN StkItem S ON W.WHStockLink = S.StockLink
		LEFT JOIN GrpTbl G ON W.WHStockGroup = G.StGroup
		LEFT JOIN Accounts A ON G.StockAccLink = A.AccountLink
		LEFT JOIN _etblGLAccountTypes ATY ON A.iAccountType = ATY.idGLAccountType
		WHERE 1=1 --and S.ServiceItem = 0
			AND PST.TxDate >= @FromDate AND PST.TxDate <= @ToDate
	) t
	GROUP BY cAuditNumber, AccountLink
	HAVING ABS(SUM(DmC_GL) - SUM(DmC_ST)) > 0.5
	ORDER BY cAuditNumber

	/*Checking transactions by audit number, postST vs postGL*/
	DECLARE @cAN varchar(100) = '71651.0001'
	SELECT * FROM PostGL WHERE cAuditNumber = @cAN
	SELECT Quantity, Cost, G.StockAccLink, 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 PST 
				LEFT JOIN WhseStk W ON PST.AccountLink = W.WHStockLink AND PST.WarehouseID = W.WHWhseID
				LEFT JOIN StkItem S ON W.WHStockLink = S.StockLink
				LEFT JOIN GrpTbl G ON W.WHStockGroup = G.StGroup
				LEFT JOIN Accounts A ON G.StockAccLink = A.AccountLink
				LEFT JOIN _etblGLAccountTypes ATY ON A.iAccountType = ATY.idGLAccountType
			WHERE cAuditNumber = @cAN 

/* VARIOUS OTHER CHECKS*/

	--check for FUCKUPS with groups: (items with a group, which does not exist (in GrpTbl))
	select * from GrpTbl where StockAccLink is null
	select * from GrpTbl where StockAccLink not in (select AccountLink from Accounts A where A.ActiveAccount = 1 AND AccountLevel <> 1)
	select * from GrpTbl where SalesAccLink not in (select AccountLink from Accounts A where A.ActiveAccount = 1 AND AccountLevel <> 1)
	select distinct ItemGroup from StkItem where ItemGroup  not in (select StGroup FROM GrpTbl)
	select * from StkItem where ItemGroup  not in (select StGroup FROM GrpTbl)
	select distinct WHStockGroup from WhseStk where WHStockGroup not in (select StGroup FROM GrpTbl) order by WHStockGroup



--Show items which have a non-balancesheet stock acc link and are not service item:
	--based on groups which are linked through warehouses.
SELECT S.Code ItemCode, S.Description_1, S.StockLink, W.WHWhseID, W.WHStockGroup, A.Master_Sub_Account, A.Description as AccountDesc
	FROM WhseStk W
	LEFT JOIN StkItem S ON W.WHStockLink = S.StockLink
	LEFT JOIN GrpTbl G ON W.WHStockGroup = G.StGroup
	LEFT JOIN Accounts A ON G.StockAccLink = A.AccountLink
	LEFT JOIN _etblGLAccountTypes ATY ON A.iAccountType = ATY.idGLAccountType
	WHERE ATY.bIsBalanceSheet = 0 AND S.ServiceItem = 0

--CHECK, for ServiceItems, with a balancesheet stock item (based on groups linked through warehouses)
SELECT S.Code ItemCode, S.Description_1, S.StockLink, W.WHWhseID, W.WHStockGroup, A.Master_Sub_Account, A.Description as AccountDesc
	FROM WhseStk W
	LEFT JOIN StkItem S ON W.WHStockLink = S.StockLink
	LEFT JOIN GrpTbl G ON W.WHStockGroup = G.StGroup
	LEFT JOIN Accounts A ON G.StockAccLink = A.AccountLink
	LEFT JOIN _etblGLAccountTypes ATY ON A.iAccountType = ATY.idGLAccountType
	WHERE ATY.bIsBalanceSheet = 1 AND S.ServiceItem = 1