view: fact_sales {
  sql_table_name: royalty_accounting.prod.workstation_fact_sales_unified_dbt;;


  # measure: count {
  #   type: count
  #   drill_fields: [detail*]
  # }

  dimension: artistid {
    hidden:  yes
    type: number
    sql: ${TABLE}.ARTISTID ;;
  }

  dimension: statement_detail_id {
    hidden:  yes
    view_label: "Xtra Advanced Fields"
    type: number
    sql: ${TABLE}.STATEMENT_DETAIL_ID ;;
  }

  dimension: accountingperiodid {
    hidden:  yes
    label: "Accounting Period ID"
    view_label: "Date"
    type: number
    sql: ${TABLE}.ACCOUNTINGPERIODID ;;
  }

  dimension: activityperiodid {
    hidden:  yes
    label: "Activity Period ID"
    view_label: "Date"
    type: number
    sql: ${TABLE}.ACTIVITYPERIODID ;;
  }

  dimension: labelid {
    hidden:  yes
    type: number
    sql: ${TABLE}.LABELID ;;
  }

  dimension: storeid {
    hidden:  yes
    type: number
    sql: ${TABLE}.STOREID ;;
  }

  dimension: genreid {
    hidden:  yes
    type: number
    sql: ${TABLE}.GENREID ;;
  }

  dimension: releaseid {
    hidden:  yes
    type: number
    sql: ${TABLE}.RELEASEID ;;
  }

  dimension: trackid {
    hidden:  yes
    type: number
    sql: ${TABLE}.TRACKID ;;
  }

  dimension: countryid {
    hidden:  yes
    type: number
    sql: ${TABLE}.COUNTRYID ;;
  }

  dimension: transactiontypeid {
    hidden:  yes
    type: number
    sql: ${TABLE}.TRANSACTIONTYPEID ;;
  }

  dimension: catalogid {
    hidden:  yes
    type: number
    sql: ${TABLE}.CATALOGID ;;
  }

  dimension: isrcid {
    hidden:  yes
    type: number
    sql: ${TABLE}.ISRCID ;;
  }

  dimension: imprintid {
    hidden:  yes
    type: number
    sql: ${TABLE}.IMPRINTID ;;
  }

  measure: gross_revenue_usd {
    view_label: "Sales in USD"
    type: sum
    sql: ${TABLE}.GROSS ;;
    value_format:"$#,##0.00"
  }

  measure: units {
    view_label: "Sales"
    type: sum
    sql: ${TABLE}.SALES ;;
  }

  measure: unit_price {
    hidden:  yes
    view_label: "Xtra Advanced Fields"
    type:  sum
    sql:
      CASE WHEN ${TABLE}.sales = 0 THEN NULL
      ELSE ${TABLE}.fx_gross/${TABLE}.sales
      END;;
  }

  measure: actual_net_revenue_usd {
    view_label: "Sales in USD"
    type: sum
    sql: ${TABLE}.ACTUAL_NET ;;
    value_format: "$#,##0.00"

  }

  measure: adjusted_gross_revenus_usd {
    view_label: "Sales in USD"
    type: sum
    sql: ${TABLE}.ADJUSTED_GROSS ;;
    value_format: "$#,##0.00"

  }

  measure: distribution_fees_usd {
    view_label: "Sales in USD"
    type: sum
    sql: ${TABLE}.DISTRIBUTION_FEES ;;
    value_format: "$#,##0.00"

  }

  measure: dpd_publishing_usd {
    view_label: "Sales in USD"
    type: sum
    sql: ${TABLE}.DPD_PUBLISHING ;;
    value_format: "$#,##0.00"

  }

  measure: net_revenue_usd {
    view_label: "Sales in USD"
    type: sum
    sql: ${TABLE}.NET_RECEIPT ;;
    value_format: "$#,##0.00"

  }

  measure: split_rate {
    hidden: yes
    view_label: "Sales in USD"
    type: sum
    sql:
      CASE WHEN ${TABLE}.fx_adjusted_gross = 0 THEN NULL
        ELSE ${TABLE}.fx_net_receipt/${TABLE}.fx_adjusted_gross
        END ;;
  }

  measure: oms_fees_usd {
    view_label: "Sales in USD"
    type: sum
    sql: ${TABLE}.OMS_FEES ;;
    value_format: "$#,##0.00"

  }

  dimension: partner_share {
    hidden:  yes
    view_label: "Xtra Advanced Fields"
    type: number
    sql: ${TABLE}.PARTNER_SHARE ;;
    value_format: "$#,##0.00"

  }

  measure: ringtone_publishing_usd {
    view_label: "Sales in USD"
    type: sum
    sql: ${TABLE}.RINGTONE_PUBLISHING ;;
    value_format: "$#,##0.00"

  }

  dimension: retail_price {
    hidden:  yes
    view_label: "Xtra Advanced Fields"
    type: number
    sql: ${TABLE}.RETAIL_PRICE ;;
  }

  dimension: subaccountid {
    hidden:  yes
    type: number
    sql: ${TABLE}.SUBACCOUNTID ;;
  }

  dimension: datecreated {
    hidden:  yes
    type: string
    sql: ${TABLE}.DATECREATED ;;
  }

  dimension: original_currency_id {
    hidden:  yes
    type: number
    sql: ${TABLE}.ORIGINAL_CURRENCY_ID ;;
  }

  dimension: fx_spread_fee {
    hidden:  yes
    view_label: "Xtra Advanced Fields"
    type: number
    sql: ${TABLE}.FX_SPREAD_FEE ;;
    value_format: "#,##0.00"

  }

  dimension: payout_currency_id {
    hidden:  yes
    type: number
    sql: ${TABLE}.PAYOUT_CURRENCY_ID ;;
  }

  dimension: activity_fx_rate {
    hidden:  yes
    view_label: "Xtra Advanced Fields"
    type: number
    sql: ${TABLE}.ACTIVITY_FX_RATE ;;
  }

  dimension: fx_adjusted_exchange_rate {
    hidden:  yes
    view_label: "Xtra Advanced Fields"
    type: number
    sql: ${TABLE}.FX_ADJUSTED_EXCHANGE_RATE ;;
  }

  measure: fx_gross_revenue {
    view_label: "Sales in Client Ccur"
    type: sum
    sql: ${TABLE}.FX_GROSS ;;
    value_format:"#,##0.00"
  }
  dimension: fx_gross_revenue_dimension {
    view_label: "Sales in Client Ccur"
    type: number
    hidden:  yes
    sql: ${TABLE}.FX_GROSS ;;
    value_format:"#,##0.00"
  }
  dimension: fx_net_receipt_revenue_dimension {
    view_label: "Sales in Client Ccur"
    type: number
    hidden:  yes
    sql: ${TABLE}.FX_NET_RECEIPT ;;
    value_format:"#,##0.00"
  }
  measure: fx_orchard_gross_marginr {
    view_label: "Sales in Client Ccur"
    label: "Orchard Gross Margin Ccur"
    description: "FX Gross Revenue - FX Net Receipt"
    hidden: yes
    sql: ${fx_gross_revenue_dimension}-${fx_net_receipt_revenue_dimension} ;;
    type: sum
    value_format: "#,##0.00"
  }
  measure: fx_actual_net_revenue {
    view_label: "Sales in Client Ccur"
    type: sum
    sql: ${TABLE}.FX_ACTUAL_NET ;;
    value_format: "#,##0.00"
  }

  measure: fx_adjusted_gross_revenue {
    view_label: "Sales in Client Ccur"
    type: sum
    sql: ${TABLE}.FX_ADJUSTED_GROSS ;;
    value_format: "#,##0.00"
  }

  measure: fx_distribution_fees {
    view_label: "Sales in Client Ccur"
    type: sum
    sql: ${TABLE}.FX_DISTRIBUTION_FEES ;;
    value_format:"#,##0.00"
  }

  measure: fx_dpd_publishing {
    view_label: "Sales in Client Ccur"
    type: sum
    sql: ${TABLE}.FX_DPD_PUBLISHING ;;
    value_format: "#,##0.00"
  }

  measure: fx_net_revenue {
    view_label: "Sales in Client Ccur"
    type: sum
    sql: ${TABLE}.FX_NET_RECEIPT ;;
    value_format: "#,##0.00"
  }

  measure: fx_oms_fees {
    view_label: "Sales in Client Ccur"
    type: sum
    sql: ${TABLE}.FX_OMS_FEES ;;
    value_format: "#,##0.00"
  }

  measure: fx_ringtone_publishing {
    view_label: "Sales in Client Ccur"
    type: sum
    sql: ${TABLE}.FX_RINGTONE_PUBLISHING ;;
    value_format: "#,##0.00"
  }

  dimension: accountingyear {
    hidden:  yes
    type: number
    sql: ${TABLE}.ACCOUNTINGYEAR ;;
  }

  dimension: accountingquarter {
    hidden:  yes
    type: number
    sql: ${TABLE}.ACCOUNTINGQUARTER ;;
  }

  dimension: accountingmonth {
    hidden:  yes
    type: number
    sql: ${TABLE}.ACCOUNTINGMONTH ;;
  }

  dimension: activityyear {
    hidden:  yes
    type: number
    sql: ${TABLE}.ACTIVITYYEAR ;;
  }

  dimension: activityquarter {
    hidden:  yes
    type: number
    sql: ${TABLE}.ACTIVITYQUARTER ;;
  }

  dimension: activitymonth {
    hidden:  yes
    type: number
    sql: ${TABLE}.ACTIVITYMONTH ;;
  }

  measure: cloud_publishing_usd {
    view_label: "Sales in USD"
    type: sum
    sql: ${TABLE}.CLOUD_PUBLISHING ;;
    value_format:"$#,##0.00"
  }

  measure: fx_cloud_publishing {
    view_label: "Sales in Client Ccur"
    type: sum
    sql: ${TABLE}.FX_CLOUD_PUBLISHING ;;
    value_format:"#,##0.00"
  }

  measure: original_price_measure {
    view_label: "Xtra Advanced Fields"
    hidden:  yes
    type: sum
    sql: ${TABLE}.ORIGINAL_PRICE ;;
  }

  measure: discount_measure {
    view_label: "Xtra Advanced Fields"
    hidden:  yes
    type: sum
    sql: ${TABLE}.DISCOUNT ;;
  }

  measure: actual_price_measure {
    hidden:  yes
    view_label: "Xtra Advanced Fields"
    type: sum
    sql: ${actual_price} ;;
  }

  dimension: original_price {
    view_label: "Sales"
    hidden:  no
    type: number
    sql: ${TABLE}.ORIGINAL_PRICE ;;
  }

  dimension: discount {
    view_label: "Sales"
    hidden: no
    type: number
    sql: ${TABLE}.DISCOUNT ;;
  }

  dimension: actual_price {
    view_label: "Sales"
    hidden: no
    type: number
    sql: ${original_price} - ${discount} ;;
  }

  dimension: months_since_release {
    view_label: "Date"
    type: number
    sql: GREATEST(DATEDIFF('month', ${dim_release.release_date_date}, ${dim_period_activity.activity_date_date}), 0) + 1 ;;
  }

  # set: detail {
  #   fields: [
  #     artistid,
  #     statement_detail_id,
  #     accountingperiodid,
  #     activityperiodid,
  #     labelid,
  #     storeid,
  #     genreid,
  #     releaseid,
  #     trackid,
  #     countryid,
  #     transactiontypeid,
  #     catalogid,
  #     isrcid,
  #     imprintid,
  #     gross,
  #     units,
  #     actual_net,
  #     adjusted_gross,
  #     distribution_fees,
  #     dpd_publishing,
  #     net_receipt,
  #     oms_fees,
  #     partner_share,
  #     ringtone_publishing,
  #     retail_price,
  #     subaccountid,
  #     datecreated,
  #     original_currency_id,
  #     fx_spread_fee,
  #     payout_currency_id,
  #     activity_fx_rate,
  #     fx_adjusted_exchange_rate,
  #     fx_gross,
  #     fx_actual_net,
  #     fx_adjusted_gross,
  #     fx_distribution_fees,
  #     fx_dpd_publishing,
  #     fx_net_receipt,
  #     fx_oms_fees,
  #     fx_ringtone_publishing,
  #     accountingyear,
  #     accountingquarter,
  #     accountingmonth,
  #     activityyear,
  #     activityquarter,
  #     activitymonth,
  #     cloud_publishing,
  #     fx_cloud_publishing,
  #     original_price,
  #     discount
  #   ]
  # }
}