SQL - Check mismatch - AR/AP tx vs GL

AP - Check by period where difference starts. Note: single AP control account only!

select p.idPeriod, p.dPeriodDate
	,(select sum(debit-credit) from postgl where accountlink = 751 and TxDate <= P.dPeriodDate) - (
	select sum(debit-credit) from PostAP where 1=1  and TxDate <= P.dPeriodDate)
	from _etblPeriod P


/*Tx that shouldn't be on the account: note to update with more tx Ids:*/
select p.idPeriod, p.dPeriodDate
    ,(select sum(debit-credit) from postgl where accountlink = 5389 and TxDate <= P.dPeriodDate) - (
    select sum(debit-credit) from PostAP where 1=1  and TxDate <= P.dPeriodDate)
	,NonAPTx = (select sum(debit-credit) from postgl where accountlink = 5389 and TxDate <= P.dPeriodDate and Id IN ('CB','JL','ARTx'))
    from _etblPeriod P

GO

CREATE FUNCTION _as_ARAPvsGL_Recon(@CheckDate date)

RETURNS TABLE 
AS
RETURN
/*RECONCILE - AR vs GL based on current configuration*/

/*NOTE: Shortcut on GL transactions - using only PostGL!*/

--DECLARE @CheckDate date = '2022-12-31' 

/*---------------------------POST AR-----------------------------*/
select 
	DB_NAME() as DBName , 
	'AR' Module,
	A.AccountLink 
	,A.Master_Sub_Account
	,A.Description
	,SUM(P.Debit-P.Credit) as AR_Total
	,(SELECT SUM(Debit-Credit) FROM PostGL p2 WHERE p2.AccountLink = A.AccountLink  AND p2.TxDate <= @CheckDate) as GLAmount
	,Diff = ROUND(COALESCE(SUM(P.Debit-P.Credit),0) - COALESCE((SELECT SUM(Debit-Credit) FROM PostGL p2 WHERE p2.AccountLink = A.AccountLink AND p2.TxDate <= @CheckDate ) ,0),2)
	--* 
	FROM PostAR P
	LEFT JOIN Client C ON P.AccountLink = C.DCLink
	LEFT JOIN CliClass CC ON C.iClassID = CC.IdCliClass AND CC.iAccountsIDControlAcc <> 0
	LEFT JOIN (SELECT TOP 1 iGLARAccID FROM 
				(SELECT iGLARAccID FROM _btblCbBatchDefs 
				UNION ALL 
				SELECT iGLARAccID FROM _btblCbBatches where COALESCE(iGLARAccID,0) <> 0
				) t
				) t2 ON 1=1
	LEFT JOIN Accounts A ON A.AccountLink = COALESCE(CC.iAccountsIDControlAcc,t2.iGLARAccID,-1)
	WHERE
	P.TxDate <= @CheckDate 


	GROUP BY 
	A.AccountLink 
	,A.Master_Sub_Account
	,A.Description
	

	UNION ALL

	/*---------------------------POST AP-----------------------------*/

select 
	DB_NAME() as DBName,
	'AP' Module,
	A.AccountLink 
	,A.Master_Sub_Account
	,A.Description
	,SUM(P.Debit-P.Credit) as AP_Total
	,(SELECT SUM(Debit-Credit) FROM PostGL p2 WHERE p2.AccountLink = A.AccountLink AND p2.TxDate <= @CheckDate) as GLAmount
	,Diff = ROUND(COALESCE(SUM(P.Debit-P.Credit),0) - COALESCE((SELECT SUM(Debit-Credit) FROM PostGL p2 WHERE p2.AccountLink = A.AccountLink AND p2.TxDate <= @CheckDate ) ,0),2)
	--* 
	FROM PostAP P
	LEFT JOIN Vendor V ON P.AccountLink = V.DCLink
	LEFT JOIN VenClass VC ON V.iClassID = VC.IdVenClass AND VC.iAccountsIDControlAcc <> 0
	LEFT JOIN (SELECT TOP 1 iGLAPAccID FROM 
				(SELECT iGLAPAccID FROM _btblCbBatchDefs 
				UNION ALL 
				SELECT iGLAPAccID FROM _btblCbBatches where COALESCE(iGLAPAccID,0) <> 0
				) t
				) t2 ON 1=1
	LEFT JOIN Accounts A ON A.AccountLink = COALESCE(VC.iAccountsIDControlAcc,t2.iGLAPAccID,-1)
	WHERE
	P.TxDate <= @CheckDate 


	GROUP BY 
	A.AccountLink 
	,A.Master_Sub_Account
	,A.Description

/*Tx which are not on the current correct account.*/
select A2.Master_Sub_Account,A2.AccountLink, P.AccountLink, A1.Master_Sub_Account, V.Account, VC.Code
	,* 
	from PostAP A
    LEFT JOIN PostGL P ON A.cAuditNumber = P.cAuditNumber AND A.debit = P.debit and A.Credit = P.Credit and A.Id = P.Id
	LEFT JOIN Accounts A1 ON P.AccountLink = A1.AccountLink
	LEFT JOIN Vendor V ON A.AccountLink = V.DCLink
	LEFT JOIN VenClass VC ON V.iClassID = VC.IdVenClass AND VC.iAccountsIDControlAcc <> 0
	LEFT JOIN (SELECT TOP 1 iGLAPAccID FROM 
				(SELECT iGLAPAccID FROM _btblCbBatchDefs 
				UNION ALL 
				SELECT iGLAPAccID FROM _btblCbBatches where COALESCE(iGLAPAccID,0) <> 0
				) t
				) t2 ON 1=1
	LEFT JOIN Accounts A2 ON A2.AccountLink = COALESCE(VC.iAccountsIDControlAcc,t2.iGLAPAccID,-1)
    where A2.AccountLink <> P.AccountLink

select * from PostAR A
	LEFT JOIN PostGL P ON A.cAuditNumber = P.cAuditNumber AND A.debit = P.debit and A.Credit = P.Credit and A.Id = P.Id 
	where P.AutoIdx is null




select * from PostAP A
	LEFT JOIN PostGL P ON A.cAuditNumber = P.cAuditNumber AND A.debit = P.debit and A.Credit = P.Credit and A.Id = P.Id 
	where P.AutoIdx is null

/*Code below, shows TRANSACTIONS which are on the INCORRECT account in the POSTGL, and gives an UPDATE STATEMENT to correct them.*/
select A2.Master_Sub_Account,A2.AccountLink, P.AccountLink, A1.Master_Sub_Account, V.Account, VC.Code
	,P.AutoIdx
	,CONCAT('update postgl set accountlink = ',A2.AccountLink,'  WHERE AutoIdx =',P.AutoIdx)
	,* 
	from PostAP A
    LEFT JOIN PostGL P ON A.cAuditNumber = P.cAuditNumber AND A.debit = P.debit and A.Credit = P.Credit and A.Id = P.Id
	LEFT JOIN Accounts A1 ON P.AccountLink = A1.AccountLink
	LEFT JOIN Vendor V ON A.AccountLink = V.DCLink
	LEFT JOIN VenClass VC ON V.iClassID = VC.IdVenClass AND VC.iAccountsIDControlAcc <> 0
	LEFT JOIN (SELECT TOP 1 iGLAPAccID FROM 
				(SELECT iGLAPAccID FROM _btblCbBatchDefs 
				UNION ALL 
				SELECT iGLAPAccID FROM _btblCbBatches where COALESCE(iGLAPAccID,0) <> 0
				) t
				) t2 ON 1=1
	LEFT JOIN Accounts A2 ON A2.AccountLink = COALESCE(VC.iAccountsIDControlAcc,t2.iGLAPAccID,-1)
    where A2.AccountLink <> P.AccountLink
	



	
select A2.Master_Sub_Account,A2.AccountLink, P.AccountLink, A1.Master_Sub_Account, C.Account, CC.Code
	,P.AutoIdx
	,CONCAT('update postgl set accountlink = ',A2.AccountLink,'  WHERE AutoIdx =',P.AutoIdx)
	,* 
	from PostAR A
    LEFT JOIN PostGL P ON A.cAuditNumber = P.cAuditNumber AND A.debit = P.debit and A.Credit = P.Credit and A.Id = P.Id
	LEFT JOIN Accounts A1 ON P.AccountLink = A1.AccountLink
	LEFT JOIN Client C ON A.AccountLink =C.DCLink
	LEFT JOIN CliClass CC ON C.iClassID = CC.IdCliClass AND CC.iAccountsIDControlAcc <> 0
	LEFT JOIN (SELECT TOP 1 iGLARAccID FROM 
				(SELECT iGLARAccID FROM _btblCbBatchDefs 
				UNION ALL 
				SELECT iGLARAccID FROM _btblCbBatches where COALESCE(iGLARAccID,0) <> 0
				) t
				) t2 ON 1=1
	LEFT JOIN Accounts A2 ON A2.AccountLink = COALESCE(CC.iAccountsIDControlAcc,t2.iGLARAccID,-1)
    where A2.AccountLink <> P.AccountLink

CHECK - PER AR CODE - MATCHING WITH GL

DECLARE @CheckDate date = '2022-12-31'
select A.Master_Sub_Account
	,P.DrCrAccount
	, SUM(P.Debit-P.Credit) as DMC_GL
	,t.Amount as ARAP_Amount
	,t.Account
	,t.GroupCode
	,Diff = t.Amount-SUM(P.Debit-P.Credit)

	from PostGL P
	LEFT JOIN Accounts A ON P.AccountLink = A.AccountLink
	RIGHT JOIN (
		select A.AccountLink,SUM(A.Debit-A.Credit) as Amount, C.Account,CC.Code as GroupCode
			/*P.AccountLink,
			A2.Master_Sub_Account,A2.AccountLink,  A1.Master_Sub_Account, C.Account, CC.Code
			,P.AutoIdx
			,CONCAT('update postgl set accountlink = ',A2.AccountLink,'  WHERE AutoIdx =',P.AutoIdx)
			,**/
			from PostAR A
			LEFT JOIN PostGL P ON A.cAuditNumber = P.cAuditNumber AND A.debit = P.debit and A.Credit = P.Credit and A.Id = P.Id
			LEFT JOIN Accounts A1 ON P.AccountLink = A1.AccountLink
			LEFT JOIN Client C ON A.AccountLink =C.DCLink
			LEFT JOIN CliClass CC ON C.iClassID = CC.IdCliClass AND CC.iAccountsIDControlAcc <> 0
			LEFT JOIN (SELECT TOP 1 iGLARAccID FROM
						(SELECT iGLARAccID FROM _btblCbBatchDefs
						UNION ALL
						SELECT iGLARAccID FROM _btblCbBatches where COALESCE(iGLARAccID,0) <> 0
						) t
						) t2 ON 1=1
			LEFT JOIN Accounts A2 ON A2.AccountLink = COALESCE(CC.iAccountsIDControlAcc,t2.iGLARAccID,-1)
			where A.TxDate <= @CheckDate
			GROUP BY A.AccountLink,C.Account,CC.Code
			/*end of inline view:*/
			) t ON P.DrCrAccount = t.AccountLink
	WHERE P.AccountLink IN (SELECT Account1Link FROM TrCodes T WHERE T.iModule = 5 UNION ALL SELECT iAccountsIDControlAcc FROM CliClass)
		AND P.TxDate <= @CheckDate
	GROUP BY P.AccountLink
		,P.DrCrAccount,t.Account,A.Master_Sub_Account, t.Amount,t.GroupCode





select A.Master_Sub_Account
	,P.DrCrAccount
	, SUM(P.Debit-P.Credit) as DMC_GL
	,t.Amount as ARAP_Amount
	,t.Account
	,t.GroupCode
	,Diff = t.Amount-SUM(P.Debit-P.Credit)

	from PostGL P
	LEFT JOIN Accounts A ON P.AccountLink = A.AccountLink
	RIGHT JOIN (
		select A.AccountLink,SUM(A.Debit-A.Credit) as Amount, v.Account,VC.Code as GroupCode
			/*P.AccountLink,
			A2.Master_Sub_Account,A2.AccountLink,  A1.Master_Sub_Account, C.Account, CC.Code
			,P.AutoIdx
			,CONCAT('update postgl set accountlink = ',A2.AccountLink,'  WHERE AutoIdx =',P.AutoIdx)
			,**/
			from PostAP A
			LEFT JOIN PostGL P ON A.cAuditNumber = P.cAuditNumber AND A.debit = P.debit and A.Credit = P.Credit and A.Id = P.Id
			LEFT JOIN Accounts A1 ON P.AccountLink = A1.AccountLink
			LEFT JOIN Vendor V ON A.AccountLink =V.DCLink
			LEFT JOIN VenClass VC ON V.iClassID = VC.IdVenClass AND VC.iAccountsIDControlAcc <> 0
			LEFT JOIN (SELECT TOP 1 iGLAPAccID FROM
						(SELECT iGLAPAccID FROM _btblCbBatchDefs
						UNION ALL
						SELECT iGLAPAccID FROM _btblCbBatches where COALESCE(iGLAPAccID,0) <> 0
						) t
						) t2 ON 1=1
			LEFT JOIN Accounts A2 ON A2.AccountLink = COALESCE(vC.iAccountsIDControlAcc,t2.iGLAPAccID,-1)
			where A.TxDate <= @CheckDate
			GROUP BY A.AccountLink,V.Account,VC.Code
			/*end of inline view:*/
			) t ON P.DrCrAccount = t.AccountLink
	WHERE P.AccountLink IN (SELECT Account1Link FROM TrCodes T WHERE T.iModule = 6 UNION ALL SELECT iAccountsIDControlAcc FROM VenClass)
		AND P.TxDate <= @CheckDate
	GROUP BY P.AccountLink
		,P.DrCrAccount,t.Account,A.Master_Sub_Account, t.Amount,t.GroupCode

Space shortcuts

Page tree