view: vw_abacus_fact_sales_distro_staging {
  sql_table_name: ROYALTY_ACCOUNTING.PROD.VW_ABACUS_FACT_SALES_DISTRO_STAGING ;;

  dimension: sales_file_id {
    view_label: "Stmt DB (SH)"
    label: "Earliest Sales File ID"
    type: number
    sql: ${TABLE}.sales_file_id ;;
  }

  dimension: batch_id {
    view_label: "Stmt DB (SH)"
    label: "Earliest Batch ID"
    type: number
    sql: ${TABLE}.batch_id ;;
  }

  dimension: SH_Exchange_Rate {
    view_label: "Stmt DB (SH)"
    label: "SH Exchange Rate"
    type: number
    sql: ${TABLE}.sale_activity_rate ;;
  }

  dimension: SH_Unite_Price_USD {
    view_label: "Stmt DB (SH)"
    label: "SH Unit Price USD"
    type: number
    sql: ${TABLE}.unit_price_usd ;;
  }

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

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

  dimension: contract_id {
    hidden: yes
    view_label: "Contract"
    label: "Contract ID"
    type: number
    sql: ${TABLE}.contract_id ;;
  }

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

  dimension: sale_currency_code {
    view_label: "Accounting"
    label: "Sale Currency"
    type: string
    sql: ${TABLE}.sale_currency_code ;;
  }

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

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

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

  dimension: track_id {
    view_label: "Track"
    label: "Track ID"
    type: number
    sql: ${TABLE}.track_id ;;
  }

  dimension: cd_id {
    view_label: "Track"
    label: "CD ID"
    type: number
    sql: ${TABLE}.cd_id ;;
  }

  dimension: store_id {
    view_label: "Store"
    label: "Store ID"
    type: number
    sql: ${TABLE}.store_id ;;
  }

  dimension: video_id {
    view_label: "Video"
    label: "Video ID"
    type: string
    sql: ${TABLE}.video_id ;;
  }

  dimension: UPC {
    view_label: "Release"
    label: "UPC"
    type: string
    sql: ${TABLE}.upc ;;
  }

  dimension: statement_id {
    view_label: "Stmt DB (SH)"
    label: "Statement ID"
    type: number
    sql: ${TABLE}.statement_id ;;
  }

  dimension_group: start_date {
    hidden:  yes
    view_label: "Statement"
    label: "Start"
    type: time
    sql: ${TABLE}.start_date ;;
    timeframes: [year,
      quarter,
      month,
      week,
      date,
      day_of_year,
      day_of_week,
      week_of_year,
      month_name,
      month_num,
      fiscal_month_num,
      fiscal_quarter,
      fiscal_quarter_of_year,
      fiscal_year]
  }

  dimension_group: transaction_date {
    view_label: "Transaction Date"
    label: "Transaction"
    type: time
    sql: ${TABLE}.transaction_date ;;
    timeframes: [year,
      quarter,
      month,
      week,
      date,
      day_of_year,
      day_of_week,
      week_of_year,
      month_name,
      month_num,
      fiscal_month_num,
      fiscal_quarter,
      fiscal_quarter_of_year,
      fiscal_year]
  }

  dimension: transaction_type {
    view_label: "Transaction"
    label: "Transaction Type"
    type: string
    sql: ${TABLE}.transaction_type ;;
  }

  dimension: transaction_subtype {
    view_label: "Transaction"
    label: "Transaction Sub Type"
    type: string
    sql: ${TABLE}.transaction_subtype ;;
  }

  dimension: label_id {
    view_label: "Label"
    label: "Label ID"
    type: number
    hidden: yes
    sql: ${TABLE}.label_id ;;
  }

  measure: quantity {
    view_label: "Accounting"
    label: "Quantity"
    type: sum
    sql: ${TABLE}.quantity ;;
  }

  measure: phys_ppd_usd {
    view_label: "Accounting"
    label: "Phys_PPD_USD (SH)"
    type: sum
    sql: ${TABLE}.phys_ppd_usd ;;
  }

  dimension: unique_detail_id {
    view_label: "Stmt DB (SH)"
    label: "Unique Detail ID"
    type: number
    sql: ${TABLE}.unique_detail_id ;;
  }

  dimension: txn_id {
    view_label: "Stmt DB (SH)"
    label: "TXN ID"
    type: number
    primary_key: yes
    sql: ${TABLE}.txn_id ;;
  }

  dimension: accounting_run_id {
    view_label: "Accounting Run"
    label: "Accounting Run ID"
    type: number
    sql: ${TABLE}.accounting_run_id ;;
  }

  dimension: accounting_run_name {
    view_label: "Accounting Run"
    label: "Accounting Run Name"
    type: string
    sql: ${TABLE}.accounting_run_name ;;
  }

  dimension: royalty_rate {
    view_label: "Accounting"
    label: "Royalty Rate"
    type: number
    value_format_name: decimal_2
    sql: ${TABLE}.royalty_rate ;;
  }

  dimension: unit_price_sale_currency {
    type: number
    view_label: "Accounting"
    group_label: "Accounting Sales Currency"
    label: "Unit Price (Sales Currency)"
    value_format: "#,##0.00"
    sql: ${TABLE}.unit_price_sale_currency ;;
  }

  measure: gross_revenue_sale_currency {
    view_label: "Accounting"
    group_label: "Accounting Sales Currency"
    label: "Gross Revenue (Sales Currency)"
    type: sum
    value_format: "#,##0.00"
    sql: ${TABLE}.gross_revenue_sale_currency ;;
  }

  measure: gross_revenue_sale_currency_usd {
    view_label: "Accounting"
    group_label: "Accounting Currency USD"
    label: "Gross Revenue USD"
    type: sum
    value_format: "#,##0.00"
    sql:
    case
      when ${TABLE}.sale_currency_code = 'USD' then ${TABLE}.gross_revenue_sale_currency
      when ${TABLE}.sale_currency_code != 'USD' and ${orch_app_ar_prod_royalty_accounting_exchange_rate.to_currency_code} = 'USD' then ${TABLE}.gross_revenue_sale_currency * ${orch_app_ar_prod_royalty_accounting_exchange_rate.rate}
    else 0
    end;;
  }


  measure: withholding_tax_sale_currency {
    view_label: "Accounting"
    group_label: "Accounting Sales Currency"
    label: "Withholding Tax (Sales Currency)"
    type: sum
    value_format: "#,##0.00"
    sql: ${TABLE}.withholding_tax_sale_currency ;;
  }

  measure: withholding_tax_sale_currency_usd {
    view_label: "Accounting"
    group_label: "Accounting Currency USD"
    label: "Withholding Tax USD"
    type: sum
    value_format: "#,##0.00"
    sql:
    case
      when ${TABLE}.sale_currency_code = 'USD' then ${TABLE}.withholding_tax_sale_currency
      when ${TABLE}.sale_currency_code != 'USD' and ${orch_app_ar_prod_royalty_accounting_exchange_rate.to_currency_code} = 'USD' then ${TABLE}.withholding_tax_sale_currency * ${orch_app_ar_prod_royalty_accounting_exchange_rate.rate}
    else 0
    end;;
  }

  measure: gross_revenue_after_withholding_tax_sale_currency {
    view_label: "Accounting"
    group_label: "Accounting Sales Currency"
    label: "Gross Revenue after Withholding Tax (Sales Currency)"
    type: sum
    value_format: "#,##0.00"
    sql: ${TABLE}.gross_revenue_after_withholding_tax_sale_currency ;;
  }

  measure: gross_revenue_after_withholding_tax_sale_currency_usd {
    view_label: "Accounting"
    group_label: "Accounting Currency USD"
    label: "Gross Revenue after Withholding Tax USD"
    type: sum
    value_format: "#,##0.00"
    sql:
    case
      when ${TABLE}.sale_currency_code = 'USD' then ${TABLE}.gross_revenue_after_withholding_tax_sale_currency
      when ${TABLE}.sale_currency_code != 'USD' and ${orch_app_ar_prod_royalty_accounting_exchange_rate.to_currency_code} = 'USD' then ${TABLE}.gross_revenue_after_withholding_tax_sale_currency * ${orch_app_ar_prod_royalty_accounting_exchange_rate.rate}
    else 0
    end;;
  }

  measure: net_share_sale_currency {
    view_label: "Accounting"
    group_label: "Accounting Sales Currency"
    label: "Net Share (Sales Currency)"
    type: sum
    value_format: "#,##0.00"
    sql: ${TABLE}.net_share_sale_currency ;;
  }

  measure: net_share_sale_currency_usd {
    view_label: "Accounting"
    group_label: "Accounting Currency USD"
    label: "Net Share USD"
    type: sum
    value_format: "#,##0.00"
    sql:
    case
      when ${TABLE}.sale_currency_code = 'USD' then ${TABLE}.net_share_sale_currency
      when ${TABLE}.sale_currency_code != 'USD' and ${orch_app_ar_prod_royalty_accounting_exchange_rate.to_currency_code} = 'USD' then ${TABLE}.net_share_sale_currency * ${orch_app_ar_prod_royalty_accounting_exchange_rate.rate}
    else 0
    end;;
  }

  measure: distribution_fee_sale_currency {
    view_label: "Accounting"
    group_label: "Accounting Sales Currency"
    label: "Distribution Fee (Sales Currency)"
    type: sum
    value_format: "#,##0.00"
    sql: ${TABLE}.distribution_fee_sale_currency ;;
  }

  measure: distribution_fee_sale_currency_usd {
    view_label: "Accounting"
    group_label: "Accounting Currency USD"
    label: "Distribution Fee USD"
    type: sum
    value_format: "#,##0.00"
    sql:
    case
      when ${TABLE}.sale_currency_code = 'USD' then ${TABLE}.distribution_fee_sale_currency
      when ${TABLE}.sale_currency_code != 'USD' and ${orch_app_ar_prod_royalty_accounting_exchange_rate.to_currency_code} = 'USD' then ${TABLE}.distribution_fee_sale_currency * ${orch_app_ar_prod_royalty_accounting_exchange_rate.rate}
    else 0
    end;;
  }

  measure: distribution_fee_payee_currency {
    view_label: "Accounting"
    group_label: "Accounting Payee Currency"
    label: "Distribution Fee (Pyee Currency)"
    type: sum
    value_format: "#,##0.00"
    sql: ${TABLE}.distribution_fee_payee_currency ;;
  }

  dimension: unit_price_payee_currency {
    view_label: "Accounting"
    group_label: "Accounting Payee Currency"
    label: "Unit Price (Payee Currency)"
    type: number
    value_format: "#,##0.00"
    sql: ${TABLE}.unit_price_payee_currency ;;
  }

  measure: gross_revenue_payee_currency {
    view_label: "Accounting"
    group_label: "Accounting Payee Currency"
    label: "Gross Revenue (Payee Currency)"
    type: sum
    value_format: "#,##0.00"
    sql: ${TABLE}.gross_revenue_payee_currency ;;
  }

  measure: withholding_tax_payee_currency {
    view_label: "Accounting"
    group_label: "Accounting Payee Currency"
    label: "Withholding Tax (Payee Currency)"
    type: sum
    value_format: "#,##0.00"
    sql: ${TABLE}.withholding_tax_payee_currency ;;
  }

  measure: gross_revenue_after_withholding_tax_Payee_currency {
    view_label: "Accounting"
    group_label: "Accounting Payee Currency"
    label: "Gross Revenue after Withholding Tax (Payee Currency)"
    type: sum
    value_format: "#,##0.00"
    sql: ${TABLE}.gross_revenue_after_withholding_tax_payee_currency ;;
  }

  measure: net_share_payee_currency {
    view_label: "Accounting"
    group_label: "Accounting Payee Currency"
    label: "Net Share (Payee Currency)"
    type: sum
    value_format: "#,##0.00"
    sql: ${TABLE}.net_share_payee_currency ;;
  }

  dimension: run_controller_id {
    view_label: "Run Controller"
    label: "Run Controller ID"
    type: number
    sql: ${TABLE}.run_controller_id ;;
  }

  dimension: contract_term_id {
    view_label: "Contract Term"
    label: "Contract Term ID"
    type: number
    sql: ${TABLE}.contract_term_id ;;
  }

  dimension: contract_term_condition_id {
    view_label: "Contract Term Condition"
    label: "Contract Term Condition ID"
    type: number
    sql: ${TABLE}.contract_term_condition_id ;;
  }

  dimension: project_id {
    view_label: "Project"
    label: "Project ID"
    type: number
    sql: ${TABLE}.project_id ;;
  }

  dimension: artist_id {
    view_label: "Artist"
    label: "Artist ID"
    type: number
    sql: ${TABLE}.artist_id ;;
  }

  dimension: track_unique_id {
    view_label: "Track"
    label: "Track Unique ID"
    type: number
    sql: ${TABLE}.track_unique_id ;;
  }

  measure: mechanical_deduction_amount_payee_currency {
    view_label: "Accounting"
    group_label: "Accounting Payee Currency"
    label: "US Mech Deduction (Payee Currency)"
    type: sum
    value_format: "#,##0.00"
    sql: ${TABLE}.mechanical_deduction_amount_payee_currency ;;
  }

  measure: publisher_admin_fee_payee_currency {
    view_label: "Accounting"
    group_label: "Accounting Payee Currency"
    label: "US Mech Admin Fee (Payee Currency)"
    type: sum
    value_format: "#,##0.00"
    sql: ${TABLE}.publisher_admin_fee_payee_currency ;;
  }
}