# View: dt_abacus_tap_payable_balances_export_qa

**View Name:** dt_abacus_tap_payable_balances_export_qa
**Table Source:** `Derived from: Qa_Royalty_Accounting_Royalty_Accounting.Abacus_State, Qa_Royalty_Accounting_Royalty_Accounting.Account, Qa_Royalty_Accounting_Royalty_Accounting.Account_Contract, Qa_Royalty_Accounting_Royalty_Accounting.Account_Payee, Qa_Royalty_Accounting_Royalty_Accounting.Account_Payment_Term + 3 more`
**File Path:** `views/dt_abacus_tap_payable_balances_export_qa.view.lkml`

## Overview

- **File Size:** 7582 bytes
- **Lines of Code:** 163
- **Dimensions:** 8
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `account_id` | string |
| `contract_id` | string |
| `most_recent_closing_balance` | string |
| `most_recent_closing_balance_statement_period` | string |
| `payment_group_name` | string |
| `sum_of_wht_corrections` | string |
| `sum_of_vat_corrections` | string |
| `projected_payment_amount` | string |

## SQL Comments

- no previous payment exists
- the payment was never sent
- valid payoneer response
- the last payment failed before the account became payment eligible

## Derived Table

```sql
sql:
      WITH eligible_accounts as (
        SELECT
            DISTINCT a.account_id,
            a.account_name,
            ati.country_of_tax_residence,
            apt.currency_code,
            apt.payment_entity_id,
            apt.payment_minimum,
            apt.payment_schedule,
            arrctr.contract_id,
            ap.payoneer_program_id,
            pg.payment_group_id,
            pg.group_name,
            pgp.statement_period_id
        FROM
            orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.account AS a
            INNER JOIN orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.account_payee AS ap ON a.account_id = ap.account_id
            INNER JOIN orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.account_tax_info AS ati ON a.account_id = ati.account_id
            INNER JOIN orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.account_payment_term AS apt ON a.account_id = apt.account_id
            INNER JOIN orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.account_contract AS arrctr ON a.account_id = arrctr.account_id
            INNER JOIN orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.payment_minimum AS pm ON apt.currency_code = pm.currency_code
            INNER JOIN orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.abacus_state AS ap_st_1 ON ap_st_1.parent_table_name = 'account_payee'
            AND ap_st_1.parent_table_id = ap.account_payee_id
            AND ap_st_1.action_name = 'payment_eligibility'
            INNER JOIN orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.abacus_state AS ap_st_2 ON ap_st_2.parent_table_name = 'account_payee'
            AND ap_st_2.parent_table_id = ap.account_payee_id
            AND ap_st_2.action_name = 'tax_eligibility'
            LEFT OUTER JOIN orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.payment_hold AS ph ON a.account_id = ph.account_id
            LEFT OUTER JOIN orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.payment_group_payment_account AS pa ON pa.account_id = a.account_id
            AND pa.prior_payment_group_payment_id IS NULL
            AND pa.deleted_at IS NULL
            AND pa.deleted_by IS NULL
            LEFT JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.payment_group_payment pgp ON pa.payment_group_payment_id = pgp.payment_group_payment_id
            JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.payment_group pg on pg.payment_group_id = pgp.payment_group_id
            LEFT OUTER JOIN orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.abacus_state AS pa_st ON pa_st.parent_table_name = 'payment_group_payment_account'
            AND pa_st.parent_table_id = pa.payment_group_payment_account_id
            AND pa_st.action_name = 'send_payments'
        WHERE
            ap_st_1.action_status = 'approved'
            AND ap_st_2.action_status = 'complete'
            AND (
                pa.payment_group_payment_account_id IS NULL -- no previous payment exists
                OR pa_st.abacus_state_id IS NULL -- the payment was never sent
                OR pa_st.action_status IN ('complete', 'rejected') -- valid payoneer response
                OR (
                    pa_st.action_status = 'error'
                    AND pa_st.last_modified < ap_st_1.last_modified
                ) -- the last payment failed before the account became payment eligible
            )
            AND ap.payoneer_program_id IS NOT NULL
            AND ap.payoneer_payee_id IS NOT NULL
            AND (
                GROUP_CRITERIA:currency_codes [0]::STRING IS NULL
                OR GROUP_CRITERIA:currency_codes [0]::STRING LIKE CONCAT('%%', apt.currency_code, '%%')
            )
            AND (
                GROUP_CRITERIA:reference_payment_entities [0]::STRING IS NULL
                OR GROUP_CRITERIA:reference_payment_entities [0] = CAST(apt.payment_entity_id as CHAR)
            )
            AND (
                GROUP_CRITERIA:payment_schedules [0]::STRING IS NULL
                OR GROUP_CRITERIA:payment_schedules [0]::STRING LIKE CONCAT('%%', apt.payment_schedule, '%%')
            )
            AND (
                GROUP_CRITERIA:account_id [0]::STRING IS NULL
                OR COALESCE(
                    CAST(
                        pg.group_criteria:account_id [0]::STRING AS NUMBER
                    ),
                    0
                ) = a.account_id
            )
            AND (
                ph.payment_hold_id IS NULL
                OR (
                    ph.start_date <= DATE(CURRENT_TIMESTAMP())
                    AND ph.is_on_hold = 0
                )
            )
            AND pa.deleted_at is NULL AND pa.deleted_by is NULL
            AND pgp.deleted_at is NULL AND pgp.deleted_by is NULL
            AND pg.deleted_at is NULL AND pg.deleted_by is NULL
    )
    select
        cb.account_id,
        cb.contract_id,
        cb.amount as most_recent_closing_balance,
        cb.statement_period_id as most_recent_closing_balance_statement_period,
        ea.group_name as payment_group_name,
        dense_rank() over (
            partition by cb.contract_id
            order by
                cb.statement_period_id desc
        ) as rn,
        sum(coalesce(wtc.amount, 0)) as sum_of_wht_corrections,
        sum(coalesce(wtcv.vat_amount_payee_currency, 0)) as sum_of_vat_corrections,
        (
            cb.amount + sum(coalesce(wtcv.vat_amount_payee_currency, 0)) + sum(coalesce(wtc.amount, 0))
        ) as projected_payment_amount
    FROM
        eligible_accounts ea
        JOIN orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.worksheet_account_contract_closing_balance cb ON ea.account_id = cb.account_id
        and ea.contract_id = cb.contract_id
        LEFT JOIN orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.worksheet_tax_correction wtc ON wtc.contract_id = cb.contract_id
        and wtc.correction_statement_period_id = cb.statement_period_id
        LEFT JOIN orchard_app_reporting_v2.qa_royalty_accounting_royalty_accounting.worksheet_tax_correction_vat wtcv ON wtcv.contract_id = cb.contract_id
        and wtcv.correction_statement_period_id = cb.statement_period_id
    WHERE
        cb.deleted_at is NULL AND cb.deleted_by is NULL
        AND wtc.deleted_at is NULL AND wtc.deleted_by is NULL
        AND wtcv.deleted_at is NULL AND wtcv.deleted_by is NULL
    group by
        1,
        2,
        3,
        4,
        5
        QUALIFY rn = 1;;
```