view: us_physical_retail_view_with_release_grid {
  derived_table: {
    sql: select
              row_number() OVER (ORDER BY release_id DESC) AS row_id,
              *
        from sony_internal.prod.us_physical_retail_view us
        inner join orchard_app_reporting_v2.art_relations_prod_art_relations.release_grid rg
        on us.gras_prod_no = rg.product_no
 ;;
  }


  dimension: row_id {
    type: number
    sql: ${TABLE}.row_id ;;
    hidden: yes
    primary_key: yes
  }

#   dimension: compound_primary_key {
#     primary_key: yes
#     hidden: yes
#     sql: CONCAT(${TABLE}.release_id, '  ', ${TABLE}."Retailer") ;;
#   }

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

  dimension: retailer {
    type: string
    sql: ${TABLE}."Retailer" ;;
  }

  dimension: retailer_code {
    type: string
    label: "Retailer Code"
    sql: ${TABLE}."Retailer Code" ;;
  }

  dimension: artist {
    type: string
    sql: ${TABLE}."Artist" ;;
  }

  dimension: product_name {
    type: string
    label: "Product Name"
    sql: ${TABLE}."Product Name" ;;
  }

  dimension: label_name {
    type: string
    label: "Label Name"
    sql: ${TABLE}."Label Name" ;;
  }

  dimension: label_code {
    type: string
    label: "Label Code"
    sql: ${TABLE}."Label Code" ;;
  }

  dimension: sub_label {
    type: string
    label: "Sub Label"
    sql: ${TABLE}."Sub Label" ;;
  }

  dimension: product_code {
    type: string
    label: "Product Code"
    sql: ${TABLE}."Product Code" ;;
  }

  dimension: upcean {
    label: "UPC/EAN"
    type: string
    sql: ${TABLE}."UPC/EAN" ;;
  }

  dimension: release_date {
    type: date
    label: "Release Date"
    sql: ${TABLE}."Release Date" ;;
  }

  dimension: product_type {
    type: string
    label: "Product Type"
    sql: ${TABLE}."Product Type" ;;
  }

  dimension: format {
    type: string
    sql: ${TABLE}."Format" ;;
  }

  dimension: units_per_set {
    type: number
    label: "Units Per Set"
    sql: ${TABLE}."Units Per Set" ;;
  }

  dimension: display_configuration {
    type: string
    label: "Display Configuration"
    sql: ${TABLE}."Display Configuration" ;;
  }

  dimension: product_status_code {
    type: string
    label: "Product Status Code"
    sql: ${TABLE}."Product Status Code" ;;
  }

  dimension: product_status {
    type: string
    label: "Product Status"
    sql: ${TABLE}."Product Status" ;;
  }

  dimension: cutout_date {
    type: date
    label: "Cutout Date"
    sql: ${TABLE}."Cutout Date" ;;
  }

  dimension: returnability {
    type: string
    sql: ${TABLE}."Returnability" ;;
  }

  dimension: exclusive {
    type: string
    sql: ${TABLE}."Exclusive" ;;
  }

  measure: open_orders {
    type: sum
    label: "Open Orders"
    sql: ${TABLE}."Open Orders" ;;
  }

  measure: backorders {
    type: sum
    sql: ${TABLE}."Backorders" ;;
  }

  measure: first4_week_s {
    label:"First4WeekS#"
    type: sum
    sql: ${TABLE}."First4WeekS#" ;;
  }

  measure: 5_day_s {
    label: "5DayS#"
    type: sum
    sql: ${TABLE}."5DayS#" ;;
  }

  measure: 5_day_r {
    label: "5DayR#"
    type: sum
    sql: ${TABLE}."5DayR#" ;;
  }

  measure: last4_wk_s {
    label: "Last4WkS#"
    type: sum
    sql: ${TABLE}."Last4WkS#" ;;
  }

  measure: last4_wk_r {
    label: "Last4WkR#"
    type: sum
    sql: ${TABLE}."Last4WkR#" ;;
  }

  measure: mtds {
    label: "MTDS#"
    type: sum
    sql: ${TABLE}."MTDS#" ;;
  }

  measure: mtdr {
    label: "MTDR#"
    type: sum
    sql: ${TABLE}."MTDR#" ;;
  }

  measure: cytds {
    label: "CYTDS#"
    type: sum
    sql: ${TABLE}."CYTDS#" ;;
  }

  measure: cytdr {
    label:"CYTDR#"
    type: sum
    sql: ${TABLE}."CYTDR#" ;;
  }

  measure: cum_tds {
    label: "Orders to Date (CumTDS#)"
    type: sum
    sql: ${TABLE}."CumTDS#" ;;
  }

  measure: cum_tdr {
    label: "CumTDR#"
    type: sum
    sql: ${TABLE}."CumTDR#" ;;
  }

  dimension: gras_prod_no {
    type: string
    sql: ${TABLE}."GRAS_PROD_NO" ;;
  }

  dimension: inventory_category_cd {
    type: string
    sql: ${TABLE}."INVENTORY_CATEGORY_CD" ;;
  }

  dimension: article_no {
    type: string
    sql: ${TABLE}."ARTICLE_NO" ;;
  }

  dimension: fin_label_cd {
    type: string
    sql: ${TABLE}."FIN_LABEL_CD" ;;
  }

  dimension: fin_label_profit_center {
    type: string
    sql: ${TABLE}."FIN_LABEL_PROFIT_CENTER" ;;
  }

  dimension: profit_center {
    type: string
    sql: ${TABLE}."PROFIT_CENTER" ;;
  }

  dimension: sap_company_cd {
    type: string
    sql: ${TABLE}."SAP_COMPANY_CD" ;;
  }

  dimension: vendor_id {
    type: number
    sql: ${TABLE}."VENDOR_ID" ;;
  }

  dimension: subacct_id {
    type: number
    sql: ${TABLE}."SUBACCT_ID";;
  }

  dimension: release_id {
    type: number
    sql: ${TABLE}."RELEASE_ID" ;;
  }

  dimension: upc {
    hidden:  yes
    type: number
    sql: ${TABLE}."UPC" ;;
  }

  dimension: grid {
    type: string
    sql: ${TABLE}."GRID" ;;
  }

  dimension: product_no {
    type: string
    sql: ${TABLE}."PRODUCT_NO" ;;
  }

  dimension: create_time {
    type: string
    sql: ${TABLE}."CREATE_TIME" ;;
  }

  dimension: gras_status {
    type: string
    sql: ${TABLE}."GRAS_STATUS" ;;
  }

  set: detail {
    fields: [
      retailer,
      retailer_code,
      artist,
      product_name,
      label_name,
      label_code,
      sub_label,
      product_code,
      upcean,
      release_date,
      product_type,
      format,
      units_per_set,
      display_configuration,
      product_status_code,
      product_status,
      cutout_date,
      returnability,
      exclusive,
      open_orders,
      backorders,
      first4_week_s,
      5_day_s,
      5_day_r,
      last4_wk_s,
      last4_wk_r,
      mtds,
      mtdr,
      cytds,
      cytdr,
      cum_tds,
      cum_tdr,
      gras_prod_no,
      inventory_category_cd,
      article_no,
      fin_label_cd,
      fin_label_profit_center,
      profit_center,
      sap_company_cd,
      vendor_id,
      subacct_id,
      release_id,
      upc,
      grid,
      product_no,
      create_time,
      gras_status
    ]
  }
}