Account Transcripts
The following SQL query generates an "account transcripts" report, primarily intended for reconciliation purposes. It provides insights into various financial transactions associated with accounts.
The primary objective of this query is to retrieve and present essential details related to financial line items, including their associated splits. It calculates and displays different types of splits, such as principal, interest, and fees, for each line item. The report assists in the reconciliation process by summarizing the financial activities within the accounts.
Fields
external_account_id
: External identifier of the account.effective_at
: Date when the line item became effective.line_item_type
: Type of the line item.line_item_status
: Status of the line item.amount
: Original amount, rounded to two decimal places.principal_cents
: Principal amount.fees_paid
: Sum of associated fees.interest_paid
: Sum of associated interest.principal_paid
: Sum of associated principal.description
: Description of the line item.
SQL Template
/*
This CTE computes the sum of principal, interest, and fee splits for each line item based
on their super types. It groups line items by their parent payment IDs.
*/
WITH payment_split_json_records AS (
select
jsonb_array_elements(line_item_relationships) field,
valid_at as effective_at,
external_line_item_id as payment_external_line_item_id,
line_item_id as internal_line_item_id,
account_id,
line_item_type
from
data_direct.line_items
where
line_item_status = 'VALID'
),
splits_as_line_items AS (
select
(psjr.field ->> 'type') as line_item_type,
payment_external_line_item_id,
(psjr.field ->> 'split_amount_cents') :: int as original_amount_cents,
(psjr.field ->> 'line_item_client_id') as transaction_id,
(psjr.field ->> 'paid_down_line_item_client_id') as paid_down_client_id,
(psjr.field ->> 'paid_down_line_item_type') as paid_down_type,
(
psjr.field ->> 'paid_down_line_item_parent_client_id'
) as paid_down_parent_external_line_item_id,
CASE
(psjr.field ->> 'paid_down_line_item_type')
WHEN 'AM_INTEREST' THEN (
psjr.field ->> 'paid_down_line_item_parent_client_id'
)
WHEN 'LOAN' THEN (psjr.field ->> 'paid_down_line_item_client_id')
END AS loan_id,
effective_at,
account_id,
line_item_type AS original_payment_type
from
payment_split_json_records psjr
),
splits_sum as (
select
payment_external_line_item_id,
SUM(
COALESCE(
CASE
WHEN splits.paid_down_type IN (
'LOAN',
'CHARGE',
'CREDIT_OFFSET',
'PAYMENT_REVERSAL_PRINCIPAL',
'PAYMENT_REVERSAL'
) THEN splits.original_amount_cents
END,
0
)
) AS "principal_split",
SUM(
COALESCE(
CASE
WHEN splits.paid_down_type IN (
'INTEREST',
'AM_INTEREST',
'AM_DI',
'DEFERRED_INTEREST',
'CREDIT_OFFSET_INTEREST',
'CREDIT_OFFSET_DEFERRED_INTEREST',
'PAYMENT_REVERSAL_INTEREST',
'PAYMENT_REVERSAL_DEFERRED_INTEREST'
) THEN splits.original_amount_cents
END,
0
)
) AS "interest_split",
SUM(
COALESCE(
CASE
WHEN splits.paid_down_type IN (
'YEAR_FEE',
'MONTH_FEE',
'RETURN_CHECK_FEE',
'LATE_FEE',
'FEE',
'MANUAL_FEE',
'CREDIT_OFFSET_FEE',
'PAYMENT_REVERSAL_FEE',
'FEE_FOR_AM',
'ORIG_FEE_FOR_AM',
'AM_FEE',
'RECURRING_FEE'
) THEN splits.original_amount_cents
END,
0
)
) AS "fee_split"
from
splits_as_line_items splits
group by
1
)
SELECT
a.external_account_id,
li.effective_at :: Date,
li.line_item_type,
li.line_item_status,
ROUND(li.original_amount_cents / 100 :: numeric, 2) AS "amount",
li.principal_cents,
ROUND(ss.fee_split / 100 :: numeric, 2) AS "fees_paid",
ROUND(ss.interest_split / 100 :: numeric, 2) "interest_paid",
ROUND(ss.principal_split / 100 :: numeric, 2) "principal_paid",
li.description
FROM
splits_sum ss
LEFT JOIN data_direct.line_items li ON ss.payment_external_line_item_id = li.external_line_item_id
LEFT JOIN data_direct.accounts a USING(account_id)
ORDER BY
a.external_account_id,
li.effective_at
Sample Data
external_account_id | effective_at | line_item_type | line_item_status | amount | principal_cents | fees_paid | interest_paid | principal_paid | description |
---|---|---|---|---|---|---|---|---|---|
00000-001 | 2023-02-08 | PAYMENT | VALID | -87.33 | 0 | 0.00 | 0.00 | -87.33 | upd payment principal_cents: -8733 + 8733 |
00000-002 | 2023-02-01 | PAYMENT | VALID | -500.00 | 0 | -75.00 | -62.34 | -362.66 | paid am loan: 36266, di 0 |
00000-003 | 2023-02-06 | PAYMENT | VALID | -580.00 | 0 | -67.52 | -12.48 | -500.00 | upd payment principal_cents: -58000 + 58000 |
00000-004 | 2023-02-07 | DEBIT_OFFSET | VALID | -15.00 | 0 | 0.00 | 0.00 | -15.00 | upd payment principal_cents: -1500 + 1500 |
00000-005 | 2023-02-07 | PAYMENT | VALID | -23.00 | 0 | 0.00 | 0.00 | -23.00 | upd payment principal_cents: -2300 + 2300 |
Updated 2 months ago