Control SQL -

This SQL compares the A/R balance in the Order (TransHeader) database with the GL database.

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

-- AR Integrity Check
SELECT 
    (SELECT DivisionName 
        FROM Division 
        WHERE COALESCE(A.GLDivisionID, TH.DivisionID) = Division.ID) AS Division,
    COALESCE(A.EffectiveDate, TH.SaleDate) AS PostingDate,
    TH.OrderNumber AS OrderNumber,
    A.GLAmount, 
    (BalanceDue* CASE WHEN StatusID >=3 THEN 1 ELSE TH.PercentComplete/100 END) AS THAmount,
    COALESCE ( (BalanceDue* CASE WHEN StatusID >=3 THEN 1 ELSE TH.PercentComplete/100 END) , 0) - COALESCE (A.GLAmount, 0) AS Difference,
    Account.CompanyName AS CompanyName,
    TH.Description, 
    TH.StatusText,
    TH.ID 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 = 14) 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 = 14) AND (TransactionID IS NULL)
    GROUP BY AccountID, COALESCE(DivisionID, 10), EntryDateTime
    HAVING SUM(Amount) <> 0
 
) AS A
 
FULL OUTER JOIN 
( 
    SELECT * 
    FROM TransHeader 
    WHERE TransactionType IN (1,6) AND BalanceDue <> 0 AND StatusID BETWEEN 1 AND 4
) AS TH
 
ON TH.ID = A.GLTransactionID
 
LEFT JOIN Account ON Account.ID = COALESCE(A.GLAccountID, TH.AccountID)
 
WHERE (COALESCE( (BalanceDue* CASE WHEN StatusID >=3 THEN 1 ELSE TH.PercentComplete/100 END),1) <> 0) 
  AND (COALESCE (A.GLAmount, 0) - COALESCE ( (BalanceDue* CASE WHEN StatusID >=3 THEN 1 ELSE TH.PercentComplete/100 END), 0) NOT BETWEEN -0.01 AND 0.01)
 
ORDER BY Division, OrderNumber
  • Entered : 6/25/2009
  • Version : Control 4.3
You could leave a comment if you were logged in.