label: "ABACUS"
connection: "abacus-looker"

include: "/views/vw_abacus_fact_sales_distro_staging.view.lkml"
include: "/views/orch_app_ar_releases.view.lkml"
include: "/views/facts_prod_dim_track.view.lkml"
include: "/views/facts_prod_dim_label.view.lkml"
include: "/views/facts_prod_dim_country.view.lkml"
include: "/views/facts_prod_dim_release.view.lkml"
include: "/views/orch_app_ar_customer_master_master.view.lkml"
include: "/views/facts_prod_dim_transactiontype.view.lkml"
include: "/views/orch_app_ar_royalty_accounting_period.view.lkml"
include: "/views/orch_app_ar_royalty_statement_period.view.lkml"
include: "/views/orch_app_ar_royalty_accounting_sales_file.view.lkml"
include: "/views/int_dbt_prod_awal_tier_groups.view.lkml"
include: "/views/facts_prod_dim_project.view.lkml"
include: "/views/orch_app_ar_royalty_accounting_contract_term.view.lkml"
include: "/views/orch_app_ar_royalty_accounting_contract_term_condition.view.lkml"
include: "/views/orch_app_ar_royalty_accounting_run_controller.view.lkml"
include: "/views/royalty_accounting_prod_vw_dim_abacus_account.view.lkml"
include: "/views/royalty_accounting_prod_vw_dim_abacus_contract.view.lkml"
include: "/views/facts_prod_dim_artist.view.lkml"
include: "/views/int_dbt_prod_track_writers.view.lkml"
include: "/views/int_dbt_prod_track_artist_by_type.view.lkml"
include: "/views/orch_app_ar_prod_royalty_accounting_exchange_rate.view.lkml"
include: "/views/royalty_accounting_prod_vw_contract_term_sax_schedule.view.lkml"
include: "/views/royalty_accounting_prod_vw_contract_term_sax_right_block.view.lkml"
include: "/views/int_dbt_prod_abacus_awal_client_segments.view.lkml"
include: "/views/int_dbt_seed_right_type_groupings.view.lkml"
include: "/views/orch_app_ar_royalty_accounting_accounting_run.view.lkml"
include: "/views/dt_dim_track.view.lkml"
include: "/views/dt_abacus_contract_lifecycle_prod.view.lkml"
include: "/views/royalty_accounting_prod_vw_dim_abacus_contract_abacus_fact_sales.view.lkml"

fiscal_month_offset: -9

explore: vw_abacus_fact_sales_distro_staging {
  label: "ABACUS Fact Sales Staging"
  view_label: "Fact Sales Staging"
  case_sensitive: no
  hidden: yes
#  sql_always_where:${vw_abacus_fact_sales_distro_staging.sale_currency_code} = ${orch_app_ar_prod_royalty_accounting_exchange_rate.from_currency_code}
#    and ${vw_abacus_fact_sales_distro_staging.statement_period_id} = ${orch_app_ar_prod_royalty_accounting_exchange_rate.statement_period_id}
#    and ${orch_app_ar_prod_royalty_accounting_exchange_rate.to_currency_code} = 'USD';;

  join: orch_app_ar_royalty_accounting_sales_file {
    view_label: "Earliest Sales File"
    sql_on: ${vw_abacus_fact_sales_distro_staging.sales_file_id} = ${orch_app_ar_royalty_accounting_sales_file.sales_file_id} ;;
    relationship: many_to_one
    fields: [sales_file_created_at, sales_file_name, sales_file_accounting_period_id]
  }

  join: facts_prod_dim_country {
    view_label: "Transaction Country"
    sql_on: ${vw_abacus_fact_sales_distro_staging.country_id} = ${facts_prod_dim_country.country_id} ;;
    relationship: many_to_one
    fields: [country_name, country_code, continent_name, us_vs_row]
  }

  join: facts_prod_dim_label {
    view_label: "OA Label"
    sql_on: ${vw_abacus_fact_sales_distro_staging.label_id} = ${facts_prod_dim_label.label_id} ;;
    relationship: many_to_one
    fields: [label_id, label_name]
  }

  join: orch_app_ar_customer_master_master {
    view_label: "Store"
    sql_on: ${vw_abacus_fact_sales_distro_staging.store_id} = ${orch_app_ar_customer_master_master.customer_master_master_id} ;;
    relationship: many_to_one
    fields: [customer_name]
  }

  join: orch_app_ar_releases {
    view_label: "Release"
    sql_on: ${vw_abacus_fact_sales_distro_staging.UPC} = ${orch_app_ar_releases.upc} ;;
    relationship: many_to_one
    fields: [release_name, display_upc, manufacturer_upc, format,
      release_date,
      release_week,
      release_month,
      release_year,
      sale_start_date,
      sale_start_week,
      sale_start_month,
      sale_start_year,
      number_of_releases,
      is_active,
      product_code]
  }

  join: dt_dim_track {
    view_label: "Track"
    sql_on: ${vw_abacus_fact_sales_distro_staging.ISRC}=${dt_dim_track.isrc}
          and ${vw_abacus_fact_sales_distro_staging.UPC}=${dt_dim_track.upc}
          and ${vw_abacus_fact_sales_distro_staging.track_id}=${dt_dim_track.cd_track_id} ;;
    relationship: many_to_one
    fields: [track_name, track_version]
  }

  join: facts_prod_dim_artist {
    view_label: "Artist"
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.artist_id} = ${facts_prod_dim_artist.artist_id} ;;
    fields: [artist_name]
  }

  join: facts_prod_dim_transactiontype {
    view_label: "Transaction"
    sql_on: ${vw_abacus_fact_sales_distro_staging.transaction_type} = ${facts_prod_dim_transactiontype.abbreviation} ;;
    relationship: many_to_one
    fields: [facts_prod_dim_transactiontype.description, facts_prod_dim_transactiontype.transaction_premium_free,
      facts_prod_dim_transactiontype.transaction_type_group, facts_prod_dim_transactiontype.transaction_type_id]
  }

  join: orch_app_ar_royalty_statement_period {
    view_label: "Statement Period"
    sql_on: ${vw_abacus_fact_sales_distro_staging.statement_period_id} = ${orch_app_ar_royalty_statement_period.statement_period_id} ;;
    relationship: many_to_one
    fields: [
      statement_year,
      statement_quarter,
      statement_month,
      statement_month_num,
      statement_month_name,
      statement_period_status,
      statement_period_name,
      closed_date_date,
      closed_date_year,
      closed_date_quarter,
      closed_date_month,
      closed_date_month_name,
      closed_date_month_num,
      closed_date_week,
      closed_date_week_of_year,
      closed_date_fiscal_year,
      closed_date_fiscal_quarter,
      closed_date_fiscal_quarter_of_year,
      closed_date_fiscal_month_num
    ]
  }


  join: orch_app_ar_royalty_accounting_period {
    view_label: "Accounting"
    sql_on: ${vw_abacus_fact_sales_distro_staging.accounting_period_id} = ${orch_app_ar_royalty_accounting_period.accounting_period_id} ;;
    relationship: many_to_one
    fields: [
      closed_date_date,
      closed_date_year,
      closed_date_quarter,
      closed_date_month,
      closed_date_month_name,
      closed_date_month_num,
      closed_date_week,
      closed_date_fiscal_year,
      closed_date_fiscal_month_num,
      closed_date_fiscal_quarter,
      closed_date_fiscal_quarter_of_year,
      accounting_period_name,
      accounting_period_status
    ]
  }

  join: sh_accounting_period {
    from: orch_app_ar_royalty_accounting_period
    view_label: "Earliest Sales File"
    sql_on: ${orch_app_ar_royalty_accounting_sales_file.sales_file_accounting_period_id} = ${sh_accounting_period.accounting_period_id};;
    relationship: one_to_one
    fields: [statement_period_id]
  }

  join: int_dbt_prod_awal_tier_groups {
    view_label: "Orchard/AWAL Tiers"
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.label_id}= ${int_dbt_prod_awal_tier_groups.label_id};;
    fields: [brand_name,parent_company,service_tier_name, owner, is_distributor]

  }

  join: facts_prod_dim_project {
    view_label: "Project"
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.project_id}= ${facts_prod_dim_project.project_id};;
  }

  join: orch_app_ar_royalty_accounting_run_controller {
    view_label: "Run Controller"
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.run_controller_id}= ${orch_app_ar_royalty_accounting_run_controller.run_controller_id};;

  }

  join: royalty_accounting_prod_vw_dim_abacus_account {
    view_label: "ABACUS Account"
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.account_id}= ${royalty_accounting_prod_vw_dim_abacus_account.account_id};;
  }

  join: royalty_accounting_prod_vw_dim_abacus_contract_abacus_fact_sales {
    view_label: "ABACUS Contract"
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.contract_id}= ${royalty_accounting_prod_vw_dim_abacus_contract_abacus_fact_sales.contract_id};;
  }

  join: dt_abacus_contract_lifecycle_prod {
    view_label: "ABACUS Contract Lifecycle"
    relationship: many_to_one
    sql_on: ${dt_abacus_contract_lifecycle_prod.contract_id}= ${royalty_accounting_prod_vw_dim_abacus_contract_abacus_fact_sales.contract_id};;
  }

  join: orch_app_ar_royalty_accounting_contract_term {
    view_label: "Contract Term"
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.contract_term_id}= ${orch_app_ar_royalty_accounting_contract_term.contract_term_id};;
    fields: [contract_term_name, term_type, attachments, is_base_term ]
  }

  join: orch_app_ar_royalty_accounting_contract_term_condition {
    view_label: "Contract Term Condition"
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.contract_term_condition_id}= ${orch_app_ar_royalty_accounting_contract_term_condition.contract_term_condition_id};;
    fields: [contract_term_condition_name, priority, conditions, term_rate]
  }

  join: int_dbt_prod_track_writers {
    view_label: "Track"
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.track_unique_id}= ${int_dbt_prod_track_writers.tuid};;
  }

  join: int_dbt_prod_track_artist_by_type {
    view_label: "Artist"
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.track_unique_id}= ${int_dbt_prod_track_artist_by_type.tuid};;
  }



  join: orch_app_ar_prod_royalty_accounting_exchange_rate {
    view_label: "USD Exchange Rate"
    type: left_outer
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.statement_period_id}= ${orch_app_ar_prod_royalty_accounting_exchange_rate.statement_period_id} and
    ${vw_abacus_fact_sales_distro_staging.sale_currency_code}=${orch_app_ar_prod_royalty_accounting_exchange_rate.from_currency_code}
    and ${orch_app_ar_prod_royalty_accounting_exchange_rate.to_currency_code} = 'USD';;
    fields: [from_currency_code, to_currency_code, rate]
    }

  join: account_currency_exchange_rate {
    view_label: "Sale to Payee Currency Exchange Rate"
    from:  orch_app_ar_prod_royalty_accounting_exchange_rate
    type: left_outer
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.statement_period_id}= ${account_currency_exchange_rate.statement_period_id} and
          ${vw_abacus_fact_sales_distro_staging.sale_currency_code}=${account_currency_exchange_rate.from_currency_code}
          and ${account_currency_exchange_rate.to_currency_code} = ${vw_abacus_fact_sales_distro_staging.account_payee_currency};;

    fields: [from_currency_code, to_currency_code, rate]
  }

  join: royalty_accounting_prod_vw_contract_term_sax_schedule {
    view_label: "Contract Term"
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.contract_term_id}= ${royalty_accounting_prod_vw_contract_term_sax_schedule.contract_term_id};;
  }

  join: royalty_accounting_prod_vw_contract_term_sax_right_block {
    view_label: "Contract Term Condition"
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.contract_term_condition_id}= ${royalty_accounting_prod_vw_contract_term_sax_right_block.contract_term_condition_id};;
  }


  join: int_dbt_prod_abacus_awal_client_segments {
    view_label: "Client Segments"
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.account_id}= ${int_dbt_prod_abacus_awal_client_segments.account_id};;

  }

  join: int_dbt_seed_right_type_groupings {
    view_label: "Right Type Groupings"
    relationship: many_to_one
    sql_on: ${vw_abacus_fact_sales_distro_staging.transaction_type}= ${int_dbt_seed_right_type_groupings.transaction_type};;
  }

  join: orch_app_ar_royalty_accounting_accounting_run {
    view_label: "Accounting"
    sql_on: ${vw_abacus_fact_sales_distro_staging.accounting_run_id} = ${orch_app_ar_royalty_accounting_accounting_run.ACCOUNTING_RUN_ID} ;;
    relationship: many_to_one
    fields: [ACCOUNTING_RUN_STATUS]
  }
}