Control SQL - AP Integrity Check

This query compares the A/P balance of bills in the GL with the balance in the TransHeader record.

None. This is a selection query and no data is modified in the running of it.

-- AP Integrity Check
SELECT 
    (SELECT DivisionName 
        FROM Division 
        WHERE COALESCE(GLDivisionID, TH.DivisionID) = Division.ID) AS Division,
    COALESCE(A.EffectiveDate, TH.OrderCreatedDate) AS PostingDate,
    TH.BillNumber AS BillNumber,
    TH.OrderNumber AS OrderNumber,
    A.GLAmount, 
    TH.BalanceDue AS THAmount,
    COALESCE (TH.BalanceDue, 0) - COALESCE (A.GLAmount, 0) AS Difference,
    Account.CompanyName AS CompanyName,
    TH.Description, 
    TH.StatusText,
    TH.StatusID,
    COALESCE(TH.ID, GLTransactionID) AS TransHeaderID,
    Account.ID AS AccountID,
    TH.BillNumber AS BillNumber,
    (SELECT MAX(EntryDateTime) 
        FROM GL GL2 
        WHERE GL2.TransactionID = TH.ID) AS LastGLEntryDate
FROM 
(
    SELECT    TransactionID AS GLTransactionID, 
        COALESCE(DivisionID, 10) AS GLDivisionID,
        AccountID AS GLAccountID,        
        -SUM(Amount) AS GLAmount,
        CAST(NULL AS DateTime) AS EffectiveDate
    FROM GL
    WHERE (GLAccountID = 22) AND (TransactionID IS NOT NULL)
    GROUP BY TransactionID, AccountID, COALESCE(DivisionID, 10)
    HAVING SUM(Amount)  0
    UNION 
    SELECT    NULL AS GLTransactionID, 
        COALESCE(DivisionID, 10) AS GLDivisionID,
        AccountID AS GLAccountID,        
        -SUM(Amount) AS GLAmount,
        EntryDateTime AS EffectiveDate
    FROM GL
    WHERE (GLAccountID = 22) AND (TransactionID IS NULL)
    GROUP BY AccountID, COALESCE(DivisionID, 10), EntryDateTime
    HAVING SUM(Amount)  0
) AS A
 
FULL OUTER JOIN 
( 
	SELECT ID AS BillTransactionID, BalanceDue, AccountID
    FROM TransHeader 
    WHERE TransactionType = 8 AND BalanceDue  0 AND StatusID  9
) AS B
ON B.BillTransactionID = A.GLTransactionID
LEFT JOIN TransHeader TH ON TH.ID = COALESCE(A.GLTransactionID, B.BillTransactionID)
 
LEFT JOIN Account ON Account.ID = COALESCE(A.GLAccountID, B.AccountID)
 
WHERE (COALESCE(B.BalanceDue,1)  0) AND (COALESCE (A.GLAmount, 0) - COALESCE (B.BalanceDue, 0) NOT BETWEEN -0.01 AND 0.01)
 
ORDER BY Division, TH.BillNumber, PostingDate

• Entered : 6/25/2009
• Version : Control 4.3