# View: dt_abacus_payment_approval_file

**View Name:** dt_abacus_payment_approval_file
**Table Source:** `Derived from: Prod_Royalty_Accounting_Royalty_Accounting.Abacus_Event, Prod_Royalty_Accounting_Royalty_Accounting.Abacus_State, Prod_Royalty_Accounting_Royalty_Accounting.Account, Prod_Royalty_Accounting_Royalty_Accounting.Account_Payee, Prod_Royalty_Accounting_Royalty_Accounting.Payment_Group_Payment_Account + 3 more`
**File Path:** `views/dt_abacus_payment_approval_file.view.lkml`

## Overview

- **File Size:** 11164 bytes
- **Lines of Code:** 302
- **Dimensions:** 33
- **Measures:** 0
- **Dimension Groups:** 1
- **Filters:** 0

## Comments & Notes

- primary_key: yes

## Dimensions

| Name | Type |
|------|------|
| `payment_group_payment_id` | string |
| `payment_name` | string |
| `account_id` | string |
| `account_name` | string |
| `account_payee_id` | string |
| `account_balance_after_tax` | string |
| `contracts_payable` | string |
| `currency_code` | string |
| `current_balance` | string |
| `last_payment` | string |
| `note` | string |
| `payoneer_payee_id` | string |
| `payoneer_program_id` | string |
| `tax_withholding` | string |
| `vat_amount` | string |
| `payment_difference` | string |
| `percent_difference` | string |
| `payment_group_payment_account_id` | string |
| `prior_payment_group_payment_id` | string |
| `current_statement_period_id` | string |
| `current_statement_period_name` | string |
| `last_statement_period_id` | string |
| `last_statement_period_name` | string |
| `payment_status` | string |
| `payment_error_code` | string |
| `group_name` | string |
| `payoneer_program_name` | string |
| `last_payment_usd` | string |
| `current_balance_usd` | string |
| `account_balance_after_tax_usd` | string |
| `contract_id` | string |
| `contract_payable_current_balance` | string |
| `contract_payable_currency_code` | string |

## Dimension Groups

| Name | Type |
|------|------|
| `payment_accepted_date` | time |

## SQL Comments

- and ae_date.statement_period_id = pgpa.current_statement_period_id
- Payment Difference Calculation
- Percentage Difference Calculation
- Payment Status Calculation
- Payment Error Code

## Derived Table

```sql
sql:
      WITH cte_payment_group_payment_account as (
        SELECT
          CAST(
            GET_PATH (f.value, 'contract_id') AS NUMBER
          ) AS contract_id,
          CAST(GET_PATH (f.value, 'currency_code') AS VARCHAR) AS contract_payable_currency_code,
          CAST(GET_PATH (f.value, 'current_balance') AS VARCHAR) AS contract_payable_current_balance,
          pgpa.*,
          max(ae_date.event_date)as payment_accepted_date
        FROM
           orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.payment_group_payment_account AS pgpa

        LEFT JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.abacus_event ae_date ON ae_date.target_id = pgpa.payment_group_payment_account_id
        -- and ae_date.statement_period_id = pgpa.current_statement_period_id
              AND ae_date.target_type = 'payment_group_payment_account'
              AND ae_date.event_name = 'payment_accepted'
         LEFT JOIN LATERAL FLATTEN (INPUT => contracts_payable) AS f (SEQ, KEY, PATH, INDEX, VALUE, THIS)
        WHERE
          pgpa.deleted_at IS NULL
          group by all
        )
      SELECT
          pa.payment_group_payment_id,
          pa.payment_accepted_date,
          pgp.payment_name,
          pg.group_name,
          pa.account_id AS account_id,
          a.account_name AS account_name,
          ap.account_payee_id AS account_payee_id,
          pa.balance_after_tax AS account_balance_after_tax,
          pa.contracts_payable AS contracts_payable,
          pa.currency_code AS currency_code,
          pa.current_balance AS current_balance,
          pa.last_payment AS last_payment,
          pa.note AS note,
          ap.payoneer_payee_id AS payoneer_payee_id,
          pa.payoneer_program_id AS payoneer_program_id,
          rpp.payoneer_program_name,
          pa.tax_withholding AS tax_withholding,
          pa.vat_amount AS vat_amount,
          CASE WHEN pa.currency_code = 'USD' THEN pa.current_balance
          ELSE round(pa.current_balance * er_current.rate, 2) END
          AS current_balance_usd,
          CASE WHEN pa.currency_code = 'USD' THEN pa.last_payment
          ELSE round(pa.last_payment * er_last.rate, 2) END
          AS last_payment_usd,
          CASE WHEN pa.currency_code = 'USD' THEN pa.balance_after_tax
          ELSE round(pa.balance_after_tax * er_current.rate, 2) END
          AS account_balance_after_tax_usd,
          -- Payment Difference Calculation
          CASE
              WHEN pa.prior_payment_group_payment_id IS NULL
              AND pa.last_payment > 0 THEN pa.balance_after_tax - pa.last_payment
          END AS payment_difference,
          -- Percentage Difference Calculation
          CASE
              WHEN pa.prior_payment_group_payment_id IS NULL
              AND pa.last_payment > 0 THEN (pa.balance_after_tax - pa.last_payment) / pa.last_payment * 100
          END AS percent_difference,
          pa.payment_group_payment_account_id AS payment_group_payment_account_id,
          pa.prior_payment_group_payment_id AS prior_payment_group_payment_id,
          pa.current_statement_period_id AS current_statement_period_id,
          sp_c.statement_period_name AS current_statement_period_name,
          pa.last_statement_period_id AS last_statement_period_id,
          sp_l.statement_period_name AS last_statement_period_name,
          -- Payment Status Calculation
          CASE
              WHEN st_pgpb.action_status = 'error' THEN 'Batch Failure'
              WHEN st_pgpb.action_status = 'complete'
              AND st_pgpba.action_status IN ('init', 'running') THEN 'Pending'
              WHEN st_pgpb.action_status = 'complete'
              AND st_pgpba.action_status = 'rejected' THEN 'Canceled'
              WHEN st_pgpb.action_status = 'complete'
              AND st_pgpba.action_status = 'error' THEN 'Failed'
              WHEN st_pgpb.action_status = 'complete'
              AND st_pgpba.action_status = 'complete' THEN 'Successful'
          END AS payment_status,
          -- Payment Error Code
          CASE
              WHEN st_pgpb.action_status = 'error' THEN CONCAT(
                  'Batch ',
                  LPAD(pgpb.batch_num::STRING, 3, '0'),
                  ' Has Failed'
              )
              WHEN st_pgpb.action_status = 'complete'
              AND st_pgpba.action_status IN ('error', 'rejected') THEN st_pgpba.message
          END AS payment_error_code,
          pa.contract_id,
          pa.contract_payable_currency_code,
          pa.contract_payable_current_balance
      FROM
          cte_payment_group_payment_account pa
          LEFT JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period sp_l ON pa.last_statement_period_id = sp_l.statement_period_id
          LEFT JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.payment_group_payment_batch_account pgpba ON pa.payment_group_payment_account_id = pgpba.payment_group_payment_account_id
          LEFT JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.payment_group_payment_batch pgpb ON pgpb.payment_group_payment_batch_id = pgpba.payment_group_payment_batch_id
          LEFT JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.abacus_state st_pgpba ON st_pgpba.parent_table_id = pgpba.payment_group_payment_account_id
          AND st_pgpba.parent_table_name = 'payment_group_payment_account'
          AND st_pgpba.action_name = 'send_payments'
          LEFT JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.abacus_state st_pgpb ON st_pgpb.parent_table_id = pgpb.payment_group_payment_batch_id
          AND st_pgpb.parent_table_name = 'payment_group_payment_batch'
          AND st_pgpb.action_name = 'send_payment'
          JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period sp_c ON pa.current_statement_period_id = sp_c.statement_period_id
          JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account a ON pa.account_id = a.account_id
          JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.account_payee ap ON pa.account_id = ap.account_id
          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
          LEFT JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.payment_group pg ON pg.payment_group_id = pgp.payment_group_id
          LEFT JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.REFERENCE_PAYONEER_PROGRAM rpp ON rpp.payoneer_program_id = pa.payoneer_program_id
          LEFT JOIN orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.exchange_rate er_current on er_current.from_currency_code = pa.currency_code and er_current.statement_period_id = pa.current_statement_period_id and er_current.to_currency_code='USD'
          LEFT JOIN orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.exchange_rate er_last on er_last.from_currency_code = pa.currency_code and er_last.statement_period_id = pa.last_statement_period_id and er_last.to_currency_code='USD'
      WHERE
          pa.deleted_at IS NULL
          AND pgp.deleted_at is NULL
          AND pg.deleted_at is NULL
          AND pa.prior_payment_group_payment_id IS NULL;;
```