# View: ph_audit_phonofile_rebates_from_ph_db

**View Name:** ph_audit_phonofile_rebates_from_ph_db
**Table Source:** `Derived from: Dev.Digital_Sale, Dev.Sale_Type, Dev.Usage_Type, Dev.Z_Company_Rollup_View, Prod.Vw_Dim_Abacus_Ar_Vendor + 2 more`
**File Path:** `ph_audit_phonofile_rebates_from_ph_db.view.lkml`

## Overview

- **File Size:** 4257 bytes
- **Lines of Code:** 70
- **Dimensions:** 6
- **Measures:** 1
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `vendor_id` | string |
| `company` | string |
| `yeards_sale_month` | string |
| `upc` | string |
| `iso2` | string |
| `amount` | number |

## Measures

| Name | Type |
|------|------|
| `count` | count |

## SQL Comments

- Exclude deleted salesreports
- Exclude deleted companies
- Exclude deleted releasese
- Exclude deleted tracks
- Mappig table of Phonofile Companies to Orchard Vendors

## Derived Table

```sql
sql: SELECT v.vendor_id,v.company, year(ds.sale_month), r.upc,ctry.iso2,sum((ds.total_amount - ds.tax_amount) * sr.to_usd)
      FROM phonofile.dev.digital_sale ds
       JOIN phonofile.dev.salesreport sr ON ds.salesreport_id = sr.id and sr.deleted=false --Exclude deleted salesreports
       JOIN phonofile.dev.company c ON ds.company_parent_id = c.id and c.deleted=false     --Exclude deleted companies
       JOIN phonofile.dev.service s ON ds.service_id = s.id
       JOIN phonofile.dev.sale_type st ON ds.sale_type_id = st.id
       JOIN phonofile.dev.usage_type ut ON ds.usage_type_id = ut.id
       JOIN phonofile.dev.release r ON ds.release_id = r.id  and r.deleted=false           --Exclude deleted releasese
       LEFT JOIN phonofile.dev.country ctry ON ds.country_id = ctry.id
       LEFT JOIN phonofile.dev.track t ON ds.track_id = t.id and t.deleted=false           --Exclude deleted tracks
       LEFT JOIN phonofile.dev.z_company_rollup_view cr ON ds.company_parent_id=cr.id      --Mappig table of Phonofile Companies to Orchard Vendors
       LEFT JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v on cr.vendor_id=v.vendor_id
      WHERE ds.deleted=false --Exclude deleted digital sales
      and  ds.company_id NOT IN (1217,6006,8018,4954) --Exclude test companies and finetunes as per Trond Stian
      and not (to_nok is null or to_sek is null or to_eur is null or to_usd is null or to_gbp is null or to_dkk is null) --Only include revenue remunerated to labels (If all rates populated then revenue remunerated to label)
      and ds.sale_month  >= '2013-01-01' and ds.sale_month < '2020-01-01'
      and s.name = 'Spotify' and v.vendor_id not in (29304,29447,29509,29912,29956,29079,29474,31858,29410,29443,28959,29002,29488,29405,29399,29241,29470,30027,30126,29205,29829,30042,29942,29819,29445,29598,29498,29360,29593,29510,29028,31398,29226,29617,29281,29812,32067,32083,31261,29308,31017,29777,29321,29407,30697,32084,31467,29047,30765,30080,30166,29330,31560,30999,31197,29692,29874,30830,29133,31381,29885,31301,29901,29072,30317,30617,29712,31209,30661,30181,29738,29329,31515,31712,29878,30264,30045,29629,30103,29302,29932,29118,30048,29520,31385,30128,31588,31288,30397,31562,31739,31714,31163,29963,29562,31010,29034,31154,29793,30614,30081,29940,32085,31392,29361,30253,31445,31312,29472,30254,32064,31663,31440,31258,29717,29897,29344,29826,31230,30211,32063,30652,30305,31401,30878,30137,31241,31365,28984,31465,29853,29925,29898,30140,31798,28950,29776,30286,30216,30306,31036,30316,31611,31054,29553,30914,29679,31331,30035,31537,31635,31531,31337,31266,31226,31631,30638,30374,30679,30138,31286,31272,30132,30953,31670,30879,31304,31660,31019,31389,30182,31581,30955,30623,31450,30769,31397,30852,30770,31256,30237,30729,31314,31190,31157,30876,31823,31656,30963,31176,30678,31849,30066,31400,30651,30844,30848,30640,29025,31893,30927,30991,30792,31790,30997,31285,30906,31252,30700,31031,31338,30670,31585,31259,31371,31379,30430,31050,30930,30831,30610,31063,29945,30727,31352,31890,31011,30634,31916,31268,30937,30632,30924,30667,31424,31084,30701,30751,30715,30814,31644,31643,31981,30304,30919,31507,30773,31203,32033,30960,30874,31318,30902,31262,31368,30917,30782,31222,32082,32016,31184,29459,30944,31967,31988,31065,31021,30609,31877,30737,31732,31625,30790,30781,30862,30864,30710,31700,30779,30939,31264)
        GROUP BY 1,2,3,4,5
       ;;
```