# View: dt_abacus_physical_reserves

**View Name:** dt_abacus_physical_reserves
**Table Source:** `Derived from: Prod_Royalty_Accounting_Royalty_Accounting.Abacus_Event, Prod_Royalty_Accounting_Royalty_Accounting.Account_Contract, Prod_Royalty_Accounting_Royalty_Accounting.Account_Payment_Term, Prod_Royalty_Accounting_Royalty_Accounting.Contract, Prod_Royalty_Accounting_Royalty_Accounting.Ledger_Account_Contract + 3 more`
**File Path:** `views/dt_abacus_physical_reserves.view.lkml`

## Overview

- **File Size:** 16136 bytes
- **Lines of Code:** 343
- **Dimensions:** 3
- **Measures:** 5
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `statement_period_id` | number |
| `account_id` | number |
| `contract_id` | number |

## Measures

| Name | Type |
|------|------|
| `taken_amount_in_current_period` | sum |
| `total_released_in_current_period` | sum |
| `remaining_amount` | sum |
| `total_released_in_prev_period` | sum |
| `total_reserves_held` | sum |

## SQL Comments

- ============================================================================
- CONTRACT BALANCES CTE
- ============================================================================
- Sub-CTE: Get the most recent balance for each contract in each period
- Rank by most recent transaction to get latest balance

## Derived Table

```sql
sql:
        WITH
        -- ============================================================================
        -- CONTRACT BALANCES CTE
        -- ============================================================================
        contract_bals AS (
            -- Sub-CTE: Get the most recent balance for each contract in each period
            WITH closing_balances AS (
                SELECT
                    sp.statement_period_id,
                    lac.account_id,
                    lac.contract_id,
                    lac.current_balance AS amount,
                    -- Rank by most recent transaction to get latest balance
                    ROW_NUMBER() OVER (
                        PARTITION BY
                            sp.statement_period_id,
                            lac.account_id,
                            lac.contract_id
                        ORDER BY
                            lac.created_at DESC,
                            lac.abacus_event_id DESC
                    ) AS ranking
                FROM
                    orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period sp
                    JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.abacus_event ae
                        ON ae.statement_period_id <= sp.statement_period_id
                    JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_account_contract lac
                        ON ae.abacus_event_id = lac.abacus_event_id
                WHERE
                    sp.statement_period_status IN ('closed', 'current')
                QUALIFY ranking = 1  -- Only keep the most recent balance
            )

            -- Main contract balances with opening/closing amounts
            SELECT
                sp.statement_period_id,
                ac.account_id,
                ac.contract_id,
                c.contract_name,
                apt.currency_code AS account_payee_currency,
                NVL(cb2.amount, 0) AS opening_bal,    -- Previous period closing = current opening
                NVL(cb1.amount, 0) AS closing_bal,    -- Current period closing
                rcc.run_controller_id
            FROM
                orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account_contract ac
                JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account_payment_term apt
                    ON apt.account_id = ac.account_id
                JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract c
                    ON c.contract_id = ac.contract_id
                JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.run_controller_contract rcc
                    ON rcc.contract_id = c.contract_id
                JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period sp
                -- Current period closing balance
                LEFT JOIN closing_balances cb1
                    ON cb1.account_id = ac.account_id
                    AND cb1.contract_id = ac.contract_id
                    AND cb1.statement_period_id = sp.statement_period_id
                -- Previous period closing balance (becomes opening balance)
                LEFT JOIN closing_balances cb2
                    ON cb2.account_id = ac.account_id
                    AND cb2.contract_id = ac.contract_id
                    AND cb2.statement_period_id = sp.statement_period_id - 1
                where sp.statement_period_status IN ('closed', 'current')
        ),

        -- ============================================================================
        -- CONTRACT EVENTS CTE
        -- ============================================================================
        -- Extract all reserve-related events (take_reserves, release_reserves)
        contract_events AS (
            SELECT
                ae.statement_period_id,
                ae.abacus_event_id,
                lac.ledger_account_contract_id,
                lac.account_id,
                lac.contract_id,
                ae.event_name,
                lac.currency_amount AS amount
            FROM
                orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_account_contract lac
                JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.abacus_event ae
                    ON ae.abacus_event_id = lac.abacus_event_id
        ),

        -- ============================================================================
        -- RESERVE AGGREGATIONS
        -- ============================================================================
        -- Total reserves taken per period
        contract_reserves_taken AS (
            SELECT
                ce.account_id,
                ce.contract_id,
                ce.statement_period_id,
                SUM(ce.amount) AS amount
            FROM contract_events ce
            WHERE event_name = 'take_reserves'
            GROUP BY 1, 2, 3
        ),

        -- Total reserves released per period
        contract_reserves_released AS (
            SELECT
                ce.account_id,
                ce.contract_id,
                ce.statement_period_id,
                SUM(ce.amount) AS amount
            FROM contract_events ce
            WHERE event_name = 'release_reserves'
            GROUP BY 1, 2, 3
        ),

        -- ============================================================================
        -- CUMULATIVE RESERVE CALCULATIONS
        -- ============================================================================
        -- Cumulative releases up to each period (for closed periods only)
        contract_released_released_till_period AS (
            SELECT
                account_id,
                contract_id,
                sp.statement_period_id,
                SUM(amount) AS amount
            FROM contract_events crr
            JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period sp
                ON sp.statement_period_id >= crr.statement_period_id
                AND statement_period_status = 'closed'
            WHERE event_name = 'release_reserves'
            GROUP BY 1, 2, 3
        ),

        -- Cumulative reserves taken up to each period (for closed periods only)
        contract_released_held_till_period AS (
            SELECT
                account_id,
                contract_id,
                sp.statement_period_id,
                SUM(amount) AS amount
            FROM contract_events crr
            JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period sp
                ON sp.statement_period_id >= crr.statement_period_id
                AND statement_period_status = 'closed'
            WHERE event_name = 'take_reserves'
            GROUP BY 1, 2, 3
        ),

        -- Calculate remaining reserves (held - released)
        contract_reserves_remaining AS (
            SELECT
                crh.account_id,
                crh.contract_id,
                crh.statement_period_id,
                ABS(ABS(crh.amount) - ABS(crr.amount)) AS amount
            FROM contract_released_held_till_period crh
            JOIN contract_released_released_till_period crr
                ON crh.account_id = crr.account_id
                AND crh.contract_id = crr.contract_id
                AND crh.statement_period_id = crr.statement_period_id
        ),

        -- ============================================================================
        -- DETAILED RELEASE SCHEDULES
        -- ============================================================================
        -- Current period releases based on release schedule
        released_amount AS (
            SELECT
                ac.account_id,
                cr.contract_id,
                lrrs.release_statement_period_id AS statement_period_id,
                lrrs.taken_statement_period_id,
                SUM(lrr.installment_amount) AS amount
            FROM orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account_contract ac
            JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_reserve_release_schedule lrrs
                ON lrrs.account_id = ac.account_id
            JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period per
                ON per.statement_period_id = lrrs.release_statement_period_id
                AND per.statement_period_status IN ('closed', 'current')
            JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_reserve_release lrr
                ON lrr.ledger_reserve_release_schedule_id = lrrs.ledger_reserve_release_schedule_id
            JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_reserve_taken lrt
                ON lrt.ledger_reserve_taken_id = lrrs.ledger_reserve_taken_id
            JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract_reserve cr
                ON cr.contract_reserve_id = lrt.contract_reserve_id
                AND cr.contract_id = ac.contract_id
            GROUP BY 1, 2, 3, 4
        ),

        -- Previous period releases (released before current period being analyzed)
        released_amount_prev AS (
            SELECT
                ac.account_id,
                cr.contract_id,
                per.statement_period_id,  -- Period being analyzed
                SUM(lrr.installment_amount) AS amount
            FROM orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account_contract ac
            JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_reserve_release_schedule lrrs
                ON lrrs.account_id = ac.account_id
            JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period per
                ON per.statement_period_id > lrrs.release_statement_period_id  -- Releases before this period
                AND per.statement_period_status IN ('closed', 'current')
            JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_reserve_release lrr
                ON lrr.ledger_reserve_release_schedule_id = lrrs.ledger_reserve_release_schedule_id
            JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_reserve_taken lrt
                ON lrt.ledger_reserve_taken_id = lrrs.ledger_reserve_taken_id
            JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract_reserve cr
                ON cr.contract_reserve_id = lrt.contract_reserve_id
                AND cr.contract_id = ac.contract_id
            GROUP BY 1, 2, 3
        ),

        -- ============================================================================
        -- RESERVE TAKEN CALCULATIONS
        -- ============================================================================
        -- Reserves taken by period
        taken_amount AS (
            SELECT
                ac.account_id,
                cr.contract_id,
                taken_statement_period_id AS statement_period_id,
                reserve_amount AS amount
            FROM orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_reserve_release_schedule lrrs
            INNER JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_reserve_taken lrt
                ON lrrs.ledger_reserve_taken_id = lrt.ledger_reserve_taken_id
            INNER JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract_reserve cr
                ON cr.contract_reserve_id = lrt.contract_reserve_id
            INNER JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account_contract ac
                ON cr.contract_id = ac.contract_id
            GROUP BY ALL
        ),

        -- Total reserves held as of each period (cumulative)
        taken_amount_total AS (
            SELECT
                account_id,
                contract_id,
                per.statement_period_id,
                SUM(amount) AS amount
            FROM taken_amount ta
            JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period per
                ON per.statement_period_id >= ta.statement_period_id
                AND per.statement_period_status IN ('closed', 'current')
            GROUP BY ALL
        )

        -- ============================================================================
        -- MAIN QUERY: RESERVE ANALYSIS RESULTS
        -- ============================================================================
        SELECT
            ce.statement_period_id,
            ce.account_id,
            ce.contract_id,
            crt.amount AS taken_amount_in_current_period,                              -- Reserves taken in this specific period
            crr_total.amount AS total_released_in_current_period,    -- Released in current period
            ABS(tat.amount) - crr_prev.amount - crr_total.amount AS remaining_amount, -- Calculated remaining reserves
            crr_prev.amount AS total_released_in_prev_period,       -- Released in all previous periods
            tat.amount AS total_reserves_held                                -- Total reserves held (cumulative)

        FROM contract_bals ce
        -- Join reserve taken amounts for specific period
        LEFT JOIN taken_amount crt
            ON ce.account_id = crt.account_id
            AND ce.contract_id = crt.contract_id
            AND ce.statement_period_id = crt.statement_period_id

        -- Join total cumulative reserves held
        LEFT JOIN taken_amount_total tat
            ON ce.account_id = tat.account_id
            AND ce.contract_id = tat.contract_id
            AND ce.statement_period_id = tat.statement_period_id

        -- Join current period releases
        LEFT JOIN contract_reserves_released crr_total
            ON ce.account_id = crr_total.account_id
            AND ce.contract_id = crr_total.contract_id
            AND ce.statement_period_id = crr_total.statement_period_id

        -- Join previous period releases
        LEFT JOIN released_amount_prev crr_prev
            ON ce.account_id = crr_prev.account_id
            AND ce.contract_id = crr_prev.contract_id
            AND ce.statement_period_id = crr_prev.statement_period_id

        -- Join calculated remaining reserves
        LEFT JOIN contract_reserves_remaining crrr
            ON ce.account_id = crrr.account_id
            AND ce.contract_id = crrr.contract_id
            AND ce.statement_period_id = crrr.statement_period_id

        WHERE ce.statement_period_id >= 284
      --      ce.account_id = 38818                    -- Filter for specific account
      --      AND ce.statement_period_id = 318         -- Filter for specific statement period


        ORDER BY statement_period_id DESC;;
```