Skip to main content
Skip table of contents

SQL - Validate Direct Cost Results versus GLT

Description

Validate Direct Cost Results versus GLT

Usage

This script will compare GLT with a JIB bill code and not suppressed for a given period that should have been pulled into JIB to the direct cost results which were created. It includes JIB GL Unbilled results to capture those that were pulled in but did not created DCR for various reasons.

Input is accounting date and defaults to only show differences.

SQL

CODE
DECLARE @accounting_date DATE = '02-28-2023' -- pick an accounting date

SELECT glt.coa_account_id AS account_id
    , glt.activity_date AS activity_date
    , SUM(glt.amount) AS glt_amount
INTO #glt_total
FROM gl_transactions glt
JOIN gl_distributions gld
    ON gld.id = glt.line_id
JOIN accounting_periods acp
    ON acp.id = glt.accounting_period_id
JOIN chart_of_accounts coa
    ON coa.id = glt.coa_account_id
WHERE acp.period_date = @accounting_date
    AND ISNULL(coa.jib_bill_code_id, 0) <> 0
    AND ISNULL(gld.jib_suppress, 0) = 0
GROUP BY glt.coa_account_id
    , glt.activity_date

SELECT dcr.coa_account_id AS account_id
    , dcr.activity_date AS activity_date
    , sum(dcr.amount) AS jib_amount
INTO #jib_total
FROM direct_cost_results dcr
WHERE dcr.moment_id = (
        SELECT id
        FROM moments
        WHERE company_code_id = dcr.company_code_id
            AND accounting_date = @accounting_date
            AND asset_id IS NULL
            AND timeline_id IS NULL
        )
GROUP BY dcr.coa_account_id
    , dcr.activity_date

SELECT juc.coa_account_id AS account_id
    , juc.activity_date AS activity_date
    , sum(juc.functional_amount) AS unbilled_amount
INTO #gl_unbilled
FROM jib_gl_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 #jib_unbilled
FROM jib_unbilled_charges juc
WHERE juc.accounting_date = @accounting_date
GROUP BY juc.coa_account_id
    , juc.activity_date

SELECT coa.account_code AS account_code
    , glt.activity_date AS activity_date
    , glt.glt_amount AS glt_amount
	, isnull(jib.jib_amount, 0) AS dcr_amount
    , isnull(unb.unbilled_amount, 0) AS gl_unbilled_amount
    , isnull(jub.unbilled_amount, 0) AS jib_unbilled_amount
    , glt.glt_amount - isnull(jib.jib_amount, 0) - isnull(unb.unbilled_amount, 0) - isnull(jub.unbilled_amount, 0) AS DIFFERENCE
FROM #glt_total glt
LEFT JOIN #jib_total jib
    ON jib.account_id = glt.account_id
        AND jib.activity_date = glt.activity_date
LEFT JOIN #gl_unbilled unb
    ON unb.account_id = glt.account_id
        AND unb.activity_date = glt.activity_date
LEFT JOIN #jib_unbilled jub
    ON jub.account_id = glt.account_id
        AND jub.activity_date = glt.activity_date
JOIN chart_of_accounts coa
    ON coa.id = glt.account_id
WHERE glt.glt_amount - isnull(jib.jib_amount, 0) - isnull(unb.unbilled_amount, 0) - isnull(jub.unbilled_amount, 0) <> 0 -- only show difference

DROP TABLE #glt_total

DROP TABLE #jib_total

DROP TABLE #gl_unbilled

DROP TABLE #jib_unbilled
JavaScript errors detected

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

If this problem persists, please contact our support.