Account Transcripts

/*
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