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