view: royalty_accounting_prod_vw_abacus_fact_sales_publishing {
  sql_table_name: ROYALTY_ACCOUNTING.PROD.VW_ABACUS_FACT_SALES_PUBLISHING ;;

  dimension: statement_detail_id {
    view_label: "Statement"
    label: "Statement Detail ID"
    type: number
    primary_key: yes
    sql: ${TABLE}.statement_detail_id ;;
  }

  dimension: period_id {
    view_label: "Statement"
    label: "Period ID"
    type: number
    sql: ${TABLE}.period_id ;;
  }

  dimension: song_no {
    view_label: "Track"
    label: "Song No."
    type: number
    sql: ${TABLE}.song_no ;;
  }

  dimension: song {
    view_label: "Composition"
    label: "Composition Name"
    type: string
    sql: ${TABLE}.song ;;
  }

  dimension: writer {
    view_label: "Track"
    label: "Writer"
    type: string
    sql: ${TABLE}.writer ;;
  }

  dimension: source1_name {
    view_label: "Source"
    label: "Source1 Name"
    type: string
    hidden: yes
    sql: ${TABLE}.source_name ;;
  }

  dimension: source1_country {
    view_label: "Source"
    label: "Source1 Country"
    type: string
    hidden: yes
    sql: ${TABLE}.source_country ;;
  }

  dimension: source2_name {
    view_label: "Source"
    label: "Source 2 Name"
    type: string
    hidden: yes
    sql: ${TABLE}.source2_name ;;
  }

  dimension: source2_country {
    view_label: "Source"
    label: "Source 2 Country"
    type: string
    hidden: yes
    sql: ${TABLE}.source2_country ;;
  }

  dimension: source3_name {
    view_label: "Source"
    label: "Source 3 Name"
    type: string
    hidden: yes
    sql: ${TABLE}.source3_name ;;
  }

  dimension: source3_country {
    view_label: "Source"
    label: "Source 3 Country"
    type: string
    hidden: yes
    sql: ${TABLE}.source3_country ;;
  }

  dimension: source4_name {
    view_label: "Source"
    label: "Source 4 Name"
    type: string
    hidden: yes
    sql: ${TABLE}.source4_name ;;
  }

  dimension: source4_country {
    view_label: "Source"
    label: "Source 4 Country"
    type: string
    hidden: yes
    sql: ${TABLE}.source4_country ;;
  }

  dimension: source_country {
    view_label: "Source"
    label: "Source Country"
    sql:IFNULL(INITCAP(${source4_country}),IFNULL(INITCAP(${source3_country}),IFNULL(INITCAP(${source2_country}),INITCAP(${source1_country})))) ;;
    map_layer_name: countries
  }

  dimension: source_name {
    view_label: "Source"
    label: "Source Name"
    sql: CASE
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Youtube%'
  THEN 'YouTube'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Spotify%'
  THEN 'Spotify'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Amazon%'
  THEN 'Amazon'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Tiktok%'
  THEN 'TikTok'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Facebook%'
  THEN 'Facebook'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Meta%'
  THEN 'Facebook'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Instagram%'
  THEN 'Facebook'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Apple%'
  THEN 'iTunes/Apple'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Itunes%'
  THEN 'iTunes/Apple'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Soundcloud%'
  THEN 'Soundcloud'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Deezer%'
  THEN 'Deezer'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Tidal%'
  THEN 'TIDAl'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Pandora%'
  THEN 'Pandora'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Audiomack%'
  THEN 'Audiomack'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Napster%'
  THEN 'Napster'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Musecore%'
  THEN 'Musescore'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Musescore%'
  THEN 'Musescore'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Qobuz%'
  THEN 'Qobuz'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Mxm%'
  THEN 'Mxm'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Google%'
  THEN 'Google Play'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Beatport%'
  THEN 'Beatport Digital Download Network'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Azlyrics%'
  THEN 'Azlyrics'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Microsoft%'
  THEN 'Microsoft Corporation'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Lyrictranslate%'
  THEN 'Lyrictranslate'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Peloton%'
  THEN 'Peloton'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Iheart%'
  THEN 'iHeartRadio'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Snap%'
  THEN 'Snap'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Sirius%'
  THEN 'SiriusXM'
  WHEN IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name})))) LIKE '%Joox%'
  THEN 'JOOX'
  ELSE IFNULL(INITCAP(${source4_name}),IFNULL(INITCAP(${source3_name}),IFNULL(INITCAP(${source2_name}),INITCAP(${source1_name}))))
  END;;
    }

  dimension: income_type {
    view_label: "Income"
    label: "Income Type"
    type: string
    sql: ${TABLE}.income_type ;;
  }

  dimension: sh_id {
    view_label: "ID"
    label: "SH ID"
    type: number
    sql: ${TABLE}.sh_id ;;
  }

  dimension: rptg_pd {
    view_label: "PD"
    label: "RPTG PD"
    type: string
    sql: ${TABLE}.rptg_pd ;;
  }

  dimension: sales_pd {
    view_label: "PD"
    label: "Sales PD"
    type: string
    sql: ${TABLE}.sales_pd ;;
  }

  dimension: product_number {
    view_label: "Product"
    label: "Product Number"
    type: string
    sql: ${TABLE}.product_number ;;
  }

  dimension: artist_product_number {
    view_label: "Product"
    label: "Artist Product Number"
    type: string
    sql: ${TABLE}.artist_product_number ;;
  }

  measure: units {
    view_label: "Revenue"
    label: "Units"
    type: sum
    sql: ${TABLE}.units ;;
  }

  dimension: song_share_percent {
    view_label: "Revenue"
    label: "Song Share %"
    type: number
    value_format: "0.0\%"
    sql: ${TABLE}.song_share_percent ;;
  }

  dimension: control_percent {
    view_label: "Revenue"
    label: "Control %"
    type: number
    value_format: "0.0\%"
    sql: ${TABLE}.control_percent ;;
  }

  measure: amount {
    view_label: "Revenue"
    label: "Gross"
    value_format: "#,##0.00"
    type: sum
    sql: ${TABLE}.amount ;;
  }

  measure: royalty_amount {
    view_label: "Revenue"
    label: "Royalty Amount"
    type: sum
    sql: ${TABLE}.royalty_amount ;;
  }

  dimension: royalty_percent {
    view_label: "Revenue"
    label: "Royalty %"
    type: number
    value_format: "0.0\%"
    sql: ${TABLE}.royalty_percent ;;
  }

  dimension: df {
    view_label: "Revenue"
    label: "DF"
    type: string
    sql: ${TABLE}.df ;;
  }

  dimension: source_product {
    view_label: "Source"
    label: "Source Product"
    type: string
    sql: ${TABLE}.source_product ;;
  }

  dimension: iswc {
    view_label: "Track"
    label: "ISWC"
    type: string
    sql: ${TABLE}.iswc ;;
  }

  dimension: external_song_id {
    view_label: "Composition"
    label: "Composition ID"
    type: number
    sql: ${TABLE}.external_song_id ;;
  }

  dimension: artist {
    view_label: "Artist"
    label: "Artst Name"
    type: string
    sql: ${TABLE}.artist ;;
  }

  dimension: source_song {
    view_label: "Source"
    label: "Source Song"
    type: string
    sql: ${TABLE}.source_song ;;
  }

  dimension: isrc {
    view_label: "Track"
    label: "ISRC"
    type: string
    sql: ${TABLE}.isrc ;;
  }

  dimension: transaction_id {
    view_label: "Transaction"
    label: "Transaction ID"
    type: number
    sql: ${TABLE}.transaction_id ;;
  }

  dimension: account_id {
    view_label: "Account"
    label: "Account ID"
    type: number
    sql: ${TABLE}.account_id ;;
  }

  dimension: has_multiple_owners {
    view_label: "Account"
    label: "Has Multiple Owners (Yes/ No)"
    type: yesno
    sql: ${TABLE}.has_multiple_owners ;;
  }

  dimension: vendor_controlled_share {
    view_label: "Account"
    label: "Vendor Controlled Share"
    type: number
    sql: ${TABLE}.vendor_controlled_share ;;
  }

  measure: adjusted_share {
    view_label: "Account"
    label: "Adjusted Share"
    type: sum
    sql: ${TABLE}.adjusted_share ;;
  }

  measure: adjusted_gross {
    view_label: "Revenue"
    label: "Adjusted Gross"
    type: sum
    sql: ${TABLE}.adjusted_gross ;;
  }

  dimension: fee_percent {
    view_label: "Revenue"
    label: "Fee %"
    type: number
    value_format: "0.0%"
    sql: ${TABLE}.fee_percent ;;
  }

  measure: orchard_fee {
    view_label: "Revenue"
    label: "Orchard Fee"
    type: sum
    value_format: "#,##0.00"
    sql: ${TABLE}.orchard_fee ;;
  }

  measure: net_revenue {
    view_label: "Revenue"
    label: "Net Revenue to Account"
    type: sum
    value_format: "#,##0.00"
    sql: ${TABLE}.net_revenue ;;
  }

  dimension: preferred_currency {
    view_label: "Currency"
    label: "Account Currency"
    type: string
    sql: ${TABLE}.preferred_currency ;;
  }

  dimension: currency_conversion_rate {
    view_label: "Currency"
    label: "Currency Conversion rate"
    type: number
    sql: ${TABLE}.currency_conversion_rate ;;
  }

  measure: net_revenue_preferred_currency {
    view_label: "Revenue"
    label: "Net Revenue in Preffered Currency"
    type: sum
    value_format: "#,##0.00"
    sql: ${TABLE}.net_revenue_preferred_currency ;;
  }

}