# View: dt_abacus_tap_advances

**View Name:** dt_abacus_tap_advances
**Table Source:** `Derived from: Prod.Currency_Exchange_Rates, Prod.Dim_Currency, Prod.Vw_Dim_Abacus_Contract, Prod_Royalty_Accounting_Royalty_Accounting.Abacus_Event, Prod_Royalty_Accounting_Royalty_Accounting.Abacus_State + 3 more`
**File Path:** `views/dt_abacus_tap_advances.view.lkml`

## Overview

- **File Size:** 13778 bytes
- **Lines of Code:** 437
- **Dimensions:** 26
- **Measures:** 12
- **Dimension Groups:** 1
- **Filters:** 0

## Comments & Notes

- primary_key: yes
- dimension: label_client_name {
- type: string

## Dimensions

| Name | Type |
|------|------|
| `owner` | string |
| `paid_by` | string |
| `payment_type` | string |
| `company` | string |
| `payment_method_id` | string |
| `statement_period_id` | string |
| `contract_id` | string |
| `contract_advance_id` | string |
| `advance_currency_code` | string |
| `payment_currency_code` | string |
| `us_source_income_rate` | string |
| `fx_rate` | string |
| `advance_status` | string |
| `payment_method` | string |
| `payment_method_notes` | string |
| `fx_rate_usd_paid` | string |
| `payment_release_ts` | date_time |
| `milestone` | string |
| `milestone_description` | string |
| `advance_description` | string |
| `is_returned` | string |
| `advance_return_message` | string |
| `advance_created_at` | date |
| `created_by` | string |
| `abacus_event_id` | string |
| `primary_key` | string |

## Measures

| Name | Type |
|------|------|
| `gross_amount` | sum |
| `withholding_tax_amount` | sum |
| `net_amount` | sum |
| `vat_amount` | sum |
| `gross_amount_payment_currency` | sum |
| `withholding_tax_amount_payment_currency` | sum |
| `net_amount_payment_currency` | sum |
| `vat_amount_payment_currency` | sum |
| `gross_amount_usd_currency` | sum |
| `withholding_tax_amount_usd_currency` | sum |
| `net_amount_usd_currency` | sum |
| `vat_amount_usd_currency` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `payment_date` | time |

## SQL Comments

- QUERY PURPOSE:
- This query generates a comprehensive report of contract advance payments, including:
- - Payment release information and tracking
- - Multi-currency calculations (original, payment currency, USD)
- - Tax calculations (VAT and withholding tax)

## Derived Table

```sql
sql:

-- QUERY PURPOSE:
-- This query generates a comprehensive report of contract advance payments, including:
-- - Payment release information and tracking
-- - Multi-currency calculations (original, payment currency, USD)
-- - Tax calculations (VAT and withholding tax)
-- - Return/rejection status monitoring
-- - Integration with Salesforce payment requests

-- BUSINESS CONTEXT:
-- Contract advances are upfront payments made to artists/labels before royalties are earned.
-- This report tracks when these advances are paid out, in what currencies, their status,
-- and provides standardized reporting across different currencies.

-- DATA SOURCES:
-- - Contract advance data from royalty accounting system
-- - Historical exchange rates from facts database
-- - Payment requests from Salesforce CRM
-- - Account and contract dimension tables
-- - Advance return/rejection tracking system

-- CURRENCY CALCULATION LOGIC:
-- - Original Currency: As recorded in the contract advance
-- - Payment Currency: Currency used by the paying account
-- - USD: Standardized reporting currency using locked exchange rates
-- */
/*
================================================================================
FINAL OUTPUT: CONTRACT ADVANCE PAYMENT REPORT
================================================================================

This section produces the final report with all calculated fields across three currencies:
1. Original advance currency (as recorded in contract)
2. Payment currency (currency used by paying account)
3. USD (standardized reporting currency)

CALCULATION LOGIC:
- Net Amount = Gross Amount + VAT + Withholding Tax
- Payment currency conversions use exchange rate from payment date
- USD conversions use exchange rate locked when worksheet was created
- is_returned = TRUE when payment processor rejected the payment

OUTPUT COLUMNS:
- Account/ownership information
- Payment execution details and timestamps
- Multi-currency financial calculations
- Status tracking and audit information
*/
    WITH exchange_rates AS (
        SELECT
            COALESCE(er.exchange_rate, 0) AS rate,
            dc_to.currency_code AS to_currency_code,
            dc_from.currency_code AS from_currency_code,
            statement_month,
            statement_year,
            er.period_id AS statement_period_id
        FROM facts.prod.currency_exchange_rates er
        JOIN facts.prod.dim_currency dc_from ON dc_from.currencyid = er.currency_from_id
        JOIN facts.prod.dim_currency dc_to ON dc_to.currencyid = er.currency_to_id
        JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.statement_period sp
            ON er.period_id = sp.statement_period_id
    ),
    advance_returns AS (
        SELECT DISTINCT
            ae.abacus_event_id,
            wpca.worksheet_payment_contract_advance_id,
            wpca.contract_advance_id,
            ast.action_status,
            ast.message
        FROM orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.worksheet_payment_contract_advance wpca
        JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.abacus_state ast
            ON ast.parent_table_name = 'worksheet_payment_contract_advance'
            AND ast.parent_table_id = wpca.worksheet_payment_contract_advance_id
            AND ast.action_name = 'send_payments'
        JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.abacus_event ae
            ON ae.target_id = wpca.worksheet_payment_contract_advance_id
            AND ae.target_type = 'worksheet_payment_contract_advance'
            AND ae.event_name = 'commit_reversal_to_subledger'
    ),
    base_data AS (
        SELECT
            a.owner,
            a.paid_by,
            lcaa.abacus_event_id,
            TO_VARCHAR(lcaa.created_at, 'MM/DD/YYYY') AS payment_release_date,
            lcaa.created_at AS payment_release_ts,
            pr.company_c AS company,
            pr.payment_type_c AS payment_type,
            ca.REFERENCE_ADVANCE_PAYMENT_METHOD_ID AS payment_method_id,
            rpt.payment_service AS payment_method,
            rpt.notes AS payment_method_notes,
            pr.label_client_id_c AS label_client_id,
            pr.label_client_name_c AS label_client_name,
            ca.CONTRACT_ID,
            ca.CONTRACT_ADVANCE_ID,
            ca.CURRENCY_CODE AS advance_currency_code,
            ca.AMOUNT AS GROSS_AMOUNT,
            ca.US_SOURCE_INCOME_RATE,
            a.currency_code AS payment_currency_code,
            ca.advance_status,
            ca.milestone,
            ca.milestone_description,
            ca.advance_description,
            ar.action_status,
            ar.message AS advance_return_message,
            ca.created_at,
            ca.created_by,
            lcaa.statement_period_id,

            -- Normalized tax amounts
            COALESCE(ca.vat_amount, 0) AS vat_amount,
            COALESCE(ca.withholding_tax_amount, 0) AS withholding_tax_amount,

            -- Exchange rates
            COALESCE(er_payments.rate, 1) AS fx_rate_payment,
            CASE WHEN ca.currency_code = 'USD' THEN 1 ELSE COALESCE(er_usd.rate, 0) END AS fx_rate_usd

        FROM orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.CONTRACT_ADVANCE ca
        JOIN royalty_accounting.prod.vw_dim_abacus_contract c
            ON c.contract_id = ca.contract_id
        JOIN royalty_accounting.prod.vw_dim_abacus_account a
            ON a.account_id = c.account_id
        LEFT JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.worksheet_payment_contract_advance wpca
            ON ca.contract_advance_id = wpca.contract_advance_id
            AND wpca.deleted_at IS NULL
        LEFT JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.ledger_contract_advance_applied lcaa
            ON lcaa.contract_advance_id = ca.contract_advance_id
        LEFT JOIN exchange_rates er_payments
            ON er_payments.statement_month = MONTH(lcaa.created_at)
            AND er_payments.statement_year = YEAR(lcaa.created_at)
            AND er_payments.from_currency_code = ca.currency_code
            AND er_payments.to_currency_code = a.currency_code
        LEFT JOIN exchange_rates er_usd
            ON er_usd.statement_period_id = wpca.exchange_rate_statement_period_id
            AND er_usd.statement_month = MONTH(lcaa.created_at)
            AND er_usd.statement_year = YEAR(lcaa.created_at)
            AND er_usd.from_currency_code = ca.currency_code
            AND er_usd.to_currency_code = 'USD'
        LEFT JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.reference_payment_type rpt
            ON rpt.reference_payment_type_id = ca.reference_payment_type_id
        LEFT JOIN BUSINESS_SYSTEMS_SALESFORCE_DATA.FINANCE.PAYMENT_REQUESTS pr
            ON TRY_TO_NUMBER(ca.contract_id) = TRY_TO_NUMBER(pr.contract_id_c)
            AND TRY_TO_NUMBER(ca.contract_advance_id) = TRY_TO_NUMBER(pr.abacus_advance_id_c)
            AND pr.contract_id_c NOT IN ('n/a', 'N/A', 'https:')
            and NOT is_canceled_c
        LEFT JOIN advance_returns ar
            ON ar.contract_advance_id = ca.contract_advance_id
            AND lcaa.worksheet_payment_contract_advance_id = ar.worksheet_payment_contract_advance_id
        WHERE ca.deleted_at is NULL
    )
    SELECT DISTINCT
        owner,
        paid_by,
        abacus_event_id,
        payment_release_date,
        payment_release_ts,
        company,
        payment_type,
        payment_method_id,
        payment_method,
        payment_method_notes,
        label_client_id,
        label_client_name,
        CONTRACT_ID,
        CONTRACT_ADVANCE_ID,
        advance_currency_code,
        GROSS_AMOUNT,
        vat_amount,
        WITHHOLDING_TAX_AMOUNT,
        GROSS_AMOUNT + vat_amount + WITHHOLDING_TAX_AMOUNT AS NET_AMOUNT,
        US_SOURCE_INCOME_RATE,
        payment_currency_code,
        fx_rate_payment AS fx_rate,
        fx_rate_usd AS fx_rate_usd_paid,

        -- Payment currency calculations
        GROSS_AMOUNT * fx_rate_payment AS GROSS_AMOUNT_PAYMENT_CURRENCY,
        (GROSS_AMOUNT + vat_amount + WITHHOLDING_TAX_AMOUNT) * fx_rate_payment AS NET_AMOUNT_PAYMENT_CURRENCY,
        WITHHOLDING_TAX_AMOUNT * fx_rate_payment AS WITHHOLDING_TAX_AMOUNT_PAYMENT_CURRENCY,
        vat_amount * fx_rate_payment AS vat_amount_PAYMENT_CURRENCY,

        -- USD currency calculations
        GROSS_AMOUNT * fx_rate_usd AS GROSS_AMOUNT_USD_CURRENCY,
        (GROSS_AMOUNT + vat_amount + WITHHOLDING_TAX_AMOUNT) * fx_rate_usd AS NET_AMOUNT_USD_CURRENCY,
        WITHHOLDING_TAX_AMOUNT * fx_rate_usd AS WITHHOLDING_TAX_AMOUNT_USD_CURRENCY,
        vat_amount * fx_rate_usd AS vat_amount_USD_CURRENCY,

        advance_status,
        milestone,
        milestone_description,
        advance_description,
        CASE WHEN action_status = 'rejected' THEN TRUE ELSE FALSE END AS is_returned,
        advance_return_message,
        created_at as advance_created_at,
        created_by,
        statement_period_id
    FROM base_data;;
```