Skip to main content
Skip table of contents

SQL - Compare OBR Totals to JFR Totals

Description

Compare OBR Totals to JFR Totals

Usage

This script will compare owner billing results to JIB financial results to validate all values were billed and created financial records.

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')

SELECT jfr.coa_account_id AS account_id
    , jfr.activity_date AS activity_date
    , sum(jfr.amount) AS jfr_amount
INTO #jfr
FROM jib_financial_results jfr
WHERE jfr.moment_id = (
        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
        )
    AND jfr.result_type = 'OwnerBillingResult'
GROUP BY jfr.coa_account_id
    , jfr.activity_date

SELECT obr.coa_account_id AS account_id
    , obr.activity_date AS activity_date
    , sum(obr.amount) AS obr_amount
INTO #obr
FROM owner_billing_results obr
WHERE obr.moment_id = (
        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
        )
GROUP BY obr.coa_account_id
    , obr.activity_date

SELECT coa.account_code AS account_code
    , obr.activity_date AS activity_date
    , obr.obr_amount AS obr_amount
    , jfr.jfr_amount AS jfr_amount
    , obr.obr_amount - jfr.jfr_amount AS DIFFERENCE
FROM #obr obr
JOIN chart_of_accounts coa
    ON coa.id = obr.account_id
JOIN #jfr jfr
    ON jfr.account_id = obr.account_id
        AND jfr.activity_date = obr.activity_date
WHERE obr.obr_amount - jfr.jfr_amount <> 0 -- limit to only differences

DROP TABLE #obr

DROP TABLE #jfr
JavaScript errors detected

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

If this problem persists, please contact our support.