# View: dt_abacus_transaction_level_uat_report

**View Name:** dt_abacus_transaction_level_uat_report
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Currencies, Prod.Dim_Currency, Prod.Dim_Label, Prod.Dim_Transactiontype, Prod.Fact_Sales + 3 more`
**File Path:** `views/dt_abacus_transaction_level_uat_report.view.lkml`

## Overview

- **File Size:** 9379 bytes
- **Lines of Code:** 289
- **Dimensions:** 22
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `from_id` | string |
| `labelid` | string |
| `labelname` | string |
| `label_tier` | string |
| `txns` | number |
| `gross_payee` | number |
| `gross_usd` | number |
| `net_payee` | number |
| `net_usd` | number |
| `admin_fee` | number |
| `publishing` | string |
| `spread_fee` | string |
| `payout_currency` | string |
| `country_id` | string |
| `country_code` | string |
| `upc` | string |
| `isrc` | string |
| `source` | string |
| `store_id` | number |
| `store_name` | string |
| `statement_period_id` | number |
| `transaction_type` | string |

## Derived Table

```sql
sql:
      WITH exchange_rates AS (
          SELECT
              cur.id currency_id,
              er.from_currency_code,
              statement_period_id,
              rate
          FROM
              orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.exchange_rate AS er
              LEFT OUTER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.currencies AS cur
                  ON er.from_currency_code = cur.iso_4217_code
          WHERE
              to_currency_code IN ('USD')
      )

      , workstation_fact_sales AS (
          SELECT
              fs.labelid,
              st.display_name as label_tier,
              dl.labelname,
              1 AS w_txns,
              fx_gross AS w_gross_payee,
              fx_adjusted_gross AS w_adjusted_gross_payee,
              gross AS w_gross_usd,
              fx_ACTUAL_NET AS w_net_payee,
              actual_net AS w_net_usd,
              fx_oms_fees AS w_admin_fee,
              fx_dpd_publishing + fx_ringtone_publishing AS w_publishing,
              FX_SPREAD_FEE AS w_spread_fee,
              fscur.currency_code AS w_payout_currency,
              fs.transactiontypeid AS w_transaction_type_id,
              fs.countryid AS w_country_id,
              dc.country_code AS w_country_code,
              fs.releaseid AS w_upc,
              dtt.isrc AS w_isrc,
              st.display_name,
              dtt.source AS w_source,
              dmsst.storeid AS w_store_id,
              dmsst.storename AS w_store_name,
              fs.accountingperiodid AS w_statement_period_id,
              dt.transactiontypeabbr as transaction_type
          FROM facts.prod.fact_sales AS fs
              INNER JOIN facts.prod.dim_transactiontype dt
                  ON fs.transactiontypeid = dt.transactiontypeid
              LEFT OUTER JOIN FACTS.PROD.VENDOR_IN_SERVICE_TIER_SERVICE_TIER vst
                  ON vst.vendor_id = labelid
              LEFT OUTER JOIN FACTS.PROD.SERVICE_TIER st
                  ON st.uuid = vst.service_tier_uuid
              LEFT OUTER JOIN facts.prod.dim_label dl
                  ON dl.labelid = fs.labelid
              LEFT OUTER JOIN facts.prod.dim_currency fscur
                  ON fs.PAYOUT_CURRENCY_ID = fscur.currencyid
              LEFT OUTER JOIN facts.prod.dim_country dc
                  ON fs.countryid = dc.countryid
              LEFT OUTER JOIN facts.prod.dim_track_clean_mv dtt
                  ON fs.releaseid = dtt.upc
                  AND dtt.isrcid = fs.isrcid
              LEFT OUTER JOIN facts.prod.dim_store AS dmsst
                  ON fs.storeid = dmsst.storeid
      )

      , abacus_fact_sales AS (
          SELECT
              cts.account_id AS labelid,
              a.account_name as labelname,
              1 AS a_txns,
              ROUND(ars.gross_revenue_payee_currency,6) AS a_gross_payee,
              round(gross_revenue_sale_currency * ifnull(er.rate, 1), 6) AS a_gross_usd,
              ROUND(NVL(arms.net_revenue_after_mechanical_payee_currency, ars.NET_REVENUE_PAYEE_CURRENCY),6) AS a_net_payee,
              ROUND(NVL(arms.net_revenue_after_mechanical_sale_currency, ars.net_revenue_sale_currency) * IFNULL(er.rate, 1),6) AS a_net_usd,
              ROUND(publisher_admin_fee_payee_currency,6) AS a_admin_fee,
              ROUND(mechanical_deduction_amount_payee_currency,6) AS a_publishing,
              abcur.iso_code AS a_payout_currency,
              sds.country_id AS a_country_id,
              dc.country_code AS a_country_code,
              sds.upc AS a_upc,
              sds.isrc AS a_isrc,
              dtt.source AS a_source,
              sds.store_id AS a_store_id,
              dmsst.store_name AS a_store_name,
              ap.statement_period_id,
              sds.transaction_type
          FROM
              royalty_accounting.uat.accounting_run_results_distro_staging AS ars
              INNER JOIN royalty_accounting.uat.contract_transaction_staging AS cts
                  ON ars.contract_txn_id = cts.contract_txn_id
              INNER JOIN ORCHARD_APP_REPORTING_V2.UAT_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.accounting_run AS ar ON ars.accounting_run_id = ar.accounting_run_id
          INNER JOIN ORCHARD_APP_REPORTING_V2.UAT_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.accounting_period AS ap ON ar.accounting_period_id = ap.accounting_period_id
          JOIN ORCHARD_APP_REPORTING_V2.UAT_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.account a on a.account_id=cts.account_id
              LEFT OUTER JOIN royalty_accounting.uat.accounting_run_results_distro_mech_staging AS arms
                  ON arms.contract_txn_id = ars.contract_txn_id
              LEFT OUTER JOIN ROYALTY_ACCOUNTING.uat.STMT_DB_SALES_DISTRO AS sds
                  ON cts.txn_id = sds.stmt_db_sales_distro_txn_id
              LEFT OUTER JOIN exchange_rates er
                  ON sds.sale_currency_code = er.from_currency_code and ap.statement_period_id=er.statement_period_id
              LEFT OUTER JOIN royalty_accounting.prod.currency_map abcur
                  ON cts.payee_currency_id = abcur.iso_number
              LEFT OUTER JOIN SONY_INTERNAL.prod.ORCHARD_DMS_TO_TCODE AS dmsst
                  ON sds.store_id = dmsst.store_id
              LEFT OUTER JOIN facts.prod.dim_country dc
                  ON sds.country_id = dc.countryid
              LEFT OUTER JOIN facts.prod.dim_track dtt
                  ON sds.upc = dtt.upc
                  AND sds.isrc = dtt.isrc
              WHERE  ap.accounting_period_id = {% parameter accounting_period_id %
```