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