Skip to main content
Skip table of contents

SQL - Validate Cost Center Allocations

Description

Validate Cost Center Allocations

Usage

This script will compare direct cost results gross for a given cost center to the allocated amount in cost allocation results.

Input is accounting date and defaults to only show differences.

SQL

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

SELECT ccai.cost_center_allocation_id AS allocation_id
    , coa.id AS coa_id
    , dcr.activity_date AS activity_date
    , cc.name AS cc_number
    , cc.id AS cc_id
    , sum(dcr.amount) AS amount
INTO #alloc_from
FROM direct_cost_results dcr
JOIN chart_of_accounts coa
    ON coa.id = dcr.coa_account_id
JOIN cost_centers cc
    ON cc.name = dcr.cost_center
JOIN cost_center_allocation_items ccai
    ON ccai.cost_center_allocated_to_id = cc.id
        AND ccai.type_of = 'from'
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 ccai.cost_center_allocation_id
    , cc.name
    , cc.id
    , coa.id
    , dcr.activity_date

SELECT af.cc_number
    , coa.account_code AS account_code
    , car.activity_date AS activity_date
    , af.amount AS total_amount_to_allocate
    , sum(car.amount) AS allocted_amount
FROM cost_allocation_results car
JOIN cost_centers cc
    ON cc.name = car.cost_center
JOIN chart_of_accounts coa
    ON coa.id = car.coa_account_id
JOIN #alloc_from af
    ON af.allocation_id = car.cost_center_allocation_id
        AND af.coa_id = car.coa_account_id
        AND af.activity_date = car.activity_date
WHERE car.moment_id = (
        SELECT id
        FROM moments
        WHERE company_code_id = car.company_code_id
            AND accounting_date = @accounting_date
            AND asset_id IS NULL
            AND timeline_id IS NULL
        )
    AND cc.id <> af.cc_id
GROUP BY af.cc_number
    , coa.account_code
    , car.activity_date
    , af.amount
HAVING af.amount <> sum(car.amount) -- determines if you show only invalid results or all results
ORDER BY coa.account_code
    , car.activity_date

DROP TABLE #alloc_from

JavaScript errors detected

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

If this problem persists, please contact our support.