Sage Evolution
Space shortcuts
Page tree
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 10 Next » 

USE [Global Connect License B.V.]
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 AR_Amount
	,t.Account
	,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
			/*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
			/*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
  • No labels