Skip to main content
Skip table of contents

SQL - Trace from AP Payment to Owner Amounts

Description

Trace from AP Payment to Owner Amounts

Usage

Script will find all OA from a given AP Payment through the various db connections required to trace back the data.

SQL

CODE
SELECT DISTINCT
	t1.ap_payment_id,
	journal_results.owner_amount_id,
	ISNULL(journal_results.meter_id, 0) AS meter_id,
	oa.product_code_id,
	oa.production_date,
	CASE oa.row_code
	WHEN '' THEN
		'A'
	ELSE
		ISNULL(oa.row_code, 'A')
	END row_code,
	oa.upstream_line_of_interest_id,
	oa.adjustment_type,
	oa.interest_type,
	oa.volume_source,
	oa.royalty_role_anchor_id,
	oa.net_interest
FROM (
	SELECT
		summarization_trace_xrefs.source_trace_id,
		ap_payment_invoices.ap_payment_id
	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
	WHERE
		ap_payment_invoices.ap_payment_id = 3501 				-- AP Payment ID
		AND summarization_trace_xrefs.source_trace_type = 'JournalResult') t1
	JOIN journal_results ON journal_results.id = t1.source_trace_id
	JOIN owner_amounts oa ON journal_results.owner_amount_id = oa.id
JavaScript errors detected

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

If this problem persists, please contact our support.