Skip to main content
Skip table of contents

SQL - Find Gross from Payable

Description

SQL used to find the gross quantities in the revenue remittance file and revenue_remittance_details table

Usage

Used when config rev_remit_gross_up_well_level: false
Determines the gross volume and value by looking at the associated payable_results for selected owner_amounts

SQL

CODE
DECLARE @meter_number NVARCHAR(255) = 'CO25792'
DECLARE @payment_id INT = (
        SELECT id
        FROM ap_payments
        WHERE voucher_number = 'PY 38606'
        )
DECLARE @product_code_id INT = (
        SELECT id
        FROM product_codes
        WHERE name = 'CON'
        )

SELECT journal_results.production_date AS production_date
    , journal_results.accounting_date AS accounting_date
    , journal_results.meter_id AS meter_id
    , journal_results.owner_number AS owner_number
    , journal_results.row_code AS row_code
    , summarization_trace_xrefs.source_trace_id
    , ap_payment_invoices.ap_payment_id
INTO #trace
FROM ap_payment_invoices
JOIN ap_invoices
    ON ap_payment_invoices.ap_invoice_id = ap_invoices.id
JOIN gl_distributions
    ON ap_invoices.id = gl_distributions.object_id
        AND gl_distributions.object_type = 'ApInvoice'
JOIN financial_integrations
    ON financial_integrations.id = gl_distributions.source_trace_id
        AND gl_distributions.source_trace_type = 'FinancialIntegration'
JOIN summarization_trace_xrefs
    ON summarization_trace_xrefs.financial_integration_id = financial_integrations.id
JOIN journal_results
    ON journal_results.id = summarization_trace_xrefs.source_trace_id
WHERE ap_payment_invoices.ap_payment_id = @payment_id -- Limit to a Rev Payment
    AND summarization_trace_xrefs.source_trace_type = 'JournalResult'

SELECT DISTINCT CASE 
        WHEN own.prior_owner_amount_id IS NOT NULL
            THEN (
                    SELECT accounting_date
                    FROM owner_amounts
                    WHERE id = own.prior_owner_amount_id
                    )
        WHEN own.prior_period_date IS NOT NULL
            THEN own.prior_period_date
        ELSE own.accounting_date
        END AS accounting_date
    , own.production_date
    , own.meter_id
    , own.row_code
    , own.product_code_id
INTO #oa_data
FROM owner_amounts OWN
JOIN #trace t1
    ON own.accounting_date = t1.accounting_date
        AND own.production_date = t1.production_date
        AND own.meter_id = t1.meter_id
        AND own.owner_number = t1.owner_number
        AND own.row_code = t1.row_code

SELECT DISTINCT own.revenue_type_id
    , own.product_code_id
    , own.meter_id
    , own.accounting_date
    , own.production_date
    , CASE own.row_code
        WHEN ''
            THEN 'A'
        ELSE ISNULL(own.row_code, 'A')
        END AS row_code
INTO #oa_group
FROM owner_amounts OWN
JOIN #oa_data oa
    ON own.accounting_date = oa.accounting_date
        AND own.production_date = oa.production_date
        AND own.meter_id = oa.meter_id
        AND own.row_code = oa.row_code
        AND own.product_code_id = oa.product_code_id
WHERE own.active = 1

SELECT m.number meter_number
    , pay.production_date
    , CASE pay.row_code
        WHEN ''
            THEN 'A'
        ELSE ISNULL(pay.row_code, 'A')
        END AS row_code
    , pc.name product_code
    , pay.tax_type_id
    , pay.volume_source
    , pay.fee_category_id
    , SUM(pay.quantity) AS gross_volume
    , SUM(pay.value) AS gross_value
    , SUM(pay.fee) AS gross_fee
    , SUM(pay.tax) AS gross_tax
    , SUM(pay.energy) AS gross_energy
    , SUM(pay.tax) AS gross_tax_from_net
FROM payable_results pay
JOIN #oa_group mflink
    ON ISNULL(mflink.revenue_type_id, 0) = ISNULL(pay.revenue_type_id, 0)
        AND mflink.product_code_id = pay.product_code_id
        AND mflink.meter_id = pay.meter_id
        AND mflink.accounting_date = pay.accounting_date
        AND mflink.production_date = pay.production_date
        AND CASE mflink.row_code
            WHEN ''
                THEN 'A'
            ELSE isnull(mflink.row_code, 'A')
            END = CASE pay.row_code
            WHEN ''
                THEN 'A'
            ELSE isnull(pay.row_code, 'A')
            END
JOIN meters m
    ON m.id = pay.meter_id
        AND from_date < pay.production_date
        AND to_date > pay.production_date
JOIN product_codes pc
    ON pc.id = pay.product_code_id
WHERE pay.meter_id IS NOT NULL
    AND m.number = @meter_number -- added to focus on meter number for debugging, this is not part of the product's query
    --    AND pay.product_code_id = @product_code_id              -- added to liimit to a product
    AND pay.value <> 0
GROUP BY m.number
    , pc.name
    , pay.tax_type_id
    , pay.fee_category_id
    , pay.production_date
    , pay.volume_source
    , CASE pay.row_code
        WHEN ''
            THEN 'A'
        ELSE ISNULL(pay.row_code, 'A')
        END
ORDER BY m.number
    , production_date
    , pc.name -- added for debugging

DROP TABLE #trace

DROP TABLE #oa_data

DROP TABLE #oa_group

JavaScript errors detected

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

If this problem persists, please contact our support.