Skip to main content
Skip table of contents

SQL - Compare DCR Totals to OBR Totals

Description

Compare DCR Totals to OBR Totals

Usage

This script will compare direct cost results gross to owner billing results cutback account to validate all values were billed. It includes JIB unbilled to capture those that were excluded from billing but initially part of direct cost results.

Input is accounting date and company and defaults to only show differences.

SQL

CODE
DECLARE @accounting_date DATE = '03-31-2023' -- pick an accounting date
DECLARE @company_code INT = (
        SELECT id
        FROM company_codes
        WHERE number = '200' -- pick a company
        )
DECLARE @moment INT = (
        SELECT id
        FROM moments
        WHERE company_code_id = @company_code
            AND accounting_date = @accounting_date
            AND asset_id IS NULL
            AND timeline_id IS NULL
        )

SELECT dcr.coa_account_id AS account_id
    , dcr.activity_date AS activity_date
    , sum(dcr.amount) AS jib_amount
INTO #dcr
FROM direct_cost_results dcr
WHERE dcr.moment_id = @moment
GROUP BY dcr.coa_account_id
    , dcr.activity_date

SELECT cut.gross_coa_account_id AS gross_account_id
    , cut.cutback_coa_account_id AS cut_coa
    , obr.activity_date AS activity_date
    , sum(obr.amount) AS obr_amount
INTO #cut
FROM owner_billing_results obr
JOIN jib_cutback_controls cut
    ON cut.cutback_coa_account_id = obr.coa_account_id
WHERE obr.moment_id = @moment
GROUP BY cut.gross_coa_account_id
    , cut.cutback_coa_account_id
    , obr.activity_date

SELECT juc.coa_account_id AS account_id
    , juc.activity_date AS activity_date
    , sum(juc.amount) AS unbilled_amount
INTO #unbilled
FROM jib_unbilled_charges juc
WHERE juc.accounting_date = @accounting_date
GROUP BY juc.coa_account_id
    , juc.activity_date

SELECT juc.coa_account_id AS account_id
    , juc.activity_date AS activity_date
    , sum(juc.amount) AS unbilled_amount
INTO #rebilled
FROM jib_unbilled_charges juc
WHERE juc.billed_moment_id = @moment
GROUP BY juc.coa_account_id
    , juc.activity_date

SELECT coa.account_code AS gross_account_code
    , coac.account_code AS cutback_account_code
    , d.activity_date AS activity_date
    , d.jib_amount AS dcr_amount
    , c.obr_amount AS cutback_amount
    , ub.unbilled_amount AS unbilled_amount
    , rb.unbilled_amount AS rebilled_amount
    , d.jib_amount + isnull(c.obr_amount, 0) - isnull(ub.unbilled_amount, 0) + isnull(rb.unbilled_amount, 0) AS difference
FROM #dcr d
LEFT JOIN #cut c
    ON d.account_id = c.gross_account_id
        AND d.activity_date = c.activity_date
LEFT JOIN #unbilled ub
    ON d.account_id = ub.account_id
        AND d.activity_date = ub.activity_date
LEFT JOIN #rebilled rb
    ON d.account_id = rb.account_id
        AND d.activity_date = rb.activity_date
JOIN chart_of_accounts coa
    ON coa.id = d.account_id
LEFT JOIN chart_of_accounts coac
    ON coac.id = c.cut_coa
WHERE d.jib_amount + isnull(c.obr_amount, 0) - isnull(ub.unbilled_amount, 0) + isnull(rb.unbilled_amount, 0) <> 0 -- limit to only differences

DROP TABLE #dcr

DROP TABLE #cut

DROP TABLE #unbilled

DROP TABLE #rebilled
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.