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
|