view: dt_abacus_remaps { derived_table: { sql: WITH stmt_db_prod_remaps AS (select * from STMT_DB.PROD.REMAPS where territory is null or territory = '') SELECT royalty_accounting_prod_vw_dim_abacus_contract.account_id AS account_id, royalty_accounting_prod_vw_dim_abacus_contract.contract_id AS contract_id, facts_prod_dim_artist.artistname AS artist_name, vw_abacus_fact_sales_distro.upc AS orchard_upc, case when orch_app_ar_releases.display_upc = '' then null else orch_app_ar_releases.display_upc end AS display_upc, orch_app_ar_releases.manufacturer_upc AS manufacturer_upc, orch_app_ar_releases.release_name AS release_name, orch_app_ar_releases.vendor_catalog_number as vendor_catalog_number, orch_app_ar_releases.product_code as product_code, royalty_accounting_prod_vw_dim_abacus_contract.contract_name AS contract_name, orch_app_ar_royalty_accounting_period.accounting_period_name AS accounting_period_name, orch_app_ar_royalty_accounting_period.accounting_period_status AS accounting_period_status, royalty_accounting_prod_vw_dim_abacus_account.account_name AS account_name, royalty_accounting_prod_vw_dim_abacus_account.owner AS owner, royalty_accounting_prod_vw_dim_abacus_contract.contract_type AS contract_type, orch_app_ar_royalty_statement_period.statement_period_name AS statement_period_name, (TO_CHAR(DATE_TRUNC('month', DATE_FROM_PARTS(orch_app_ar_royalty_statement_period.statement_year, orch_app_ar_royalty_statement_period.statement_month, '01') ), 'YYYY-MM')) AS statement_month, stmt_db_prod_remaps_physical.new_upc AS physical_new_upc, stmt_db_prod_remaps_physical.old_upc AS physical_old_upc, stmt_db_prod_remaps.new_upc AS digital_new_upc, stmt_db_prod_remaps.old_upc AS digital_old_upc, IFF(pp.release_id is NULL,'N','Y') as is_physical FROM ROYALTY_ACCOUNTING.PROD.VW_ABACUS_FACT_SALES_DISTRO_V2 AS vw_abacus_fact_sales_distro LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases AS orch_app_ar_releases ON vw_abacus_fact_sales_distro.upc = orch_app_ar_releases.upc LEFT JOIN facts.prod.dim_artist AS facts_prod_dim_artist ON vw_abacus_fact_sales_distro.artist_id = facts_prod_dim_artist.artistid LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD AS orch_app_ar_royalty_statement_period ON vw_abacus_fact_sales_distro.statement_period_id = orch_app_ar_royalty_statement_period.statement_period_id LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNTING_PERIOD AS orch_app_ar_royalty_accounting_period ON vw_abacus_fact_sales_distro.accounting_period_id = orch_app_ar_royalty_accounting_period.accounting_period_id LEFT JOIN ROYALTY_ACCOUNTING.PROD.VW_DIM_ABACUS_ACCOUNT AS royalty_accounting_prod_vw_dim_abacus_account ON vw_abacus_fact_sales_distro.account_id= royalty_accounting_prod_vw_dim_abacus_account.account_id LEFT JOIN ROYALTY_ACCOUNTING.PROD.VW_DIM_ABACUS_CONTRACT AS royalty_accounting_prod_vw_dim_abacus_contract ON vw_abacus_fact_sales_distro.contract_id= royalty_accounting_prod_vw_dim_abacus_contract.contract_id LEFT JOIN stmt_db_prod_remaps ON vw_abacus_fact_sales_distro.upc = stmt_db_prod_remaps.new_upc LEFT JOIN STMT_DB.PROD.REMAPS_PHYSICAL AS stmt_db_prod_remaps_physical ON vw_abacus_fact_sales_distro.upc = stmt_db_prod_remaps_physical.new_upc left join orchard_app_reporting_v2.art_relations_prod_art_relations.product_physical pp on orch_app_ar_releases.release_id = pp.release_id WHERE ((( DATE_FROM_PARTS(orch_app_ar_royalty_statement_period.statement_year, orch_app_ar_royalty_statement_period.statement_month, '01') ) >= ((DATEADD('month', -2, DATE_TRUNC('month', CURRENT_DATE())))) AND ( DATE_FROM_PARTS(orch_app_ar_royalty_statement_period.statement_year, orch_app_ar_royalty_statement_period.statement_month, '01') ) < ((DATEADD('month', 3, DATEADD('month', -2, DATE_TRUNC('month', CURRENT_DATE()))))))) GROUP BY (DATE_TRUNC('month', DATE_FROM_PARTS(orch_app_ar_royalty_statement_period.statement_year, orch_app_ar_royalty_statement_period.statement_month, '01') )), 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 18, 19, 20, 21, 22 ORDER BY 11 DESC ;; } dimension: account_id { label: "Account Id" type: string sql: ${TABLE}.account_id ;; } dimension: contract_id { label: "Contract Id" type: string sql: ${TABLE}.contract_id ;; } dimension: artist_name { label: "Artist Name" type: string sql: ${TABLE}.artist_name ;; } dimension: orchard_upc { label: "Orchard Upc" type: string sql: ${TABLE}.orchard_upc ;; } dimension: display_upc { label: "Display Upc" type: string sql: ${TABLE}.display_upc ;; } dimension: manufacturer_upc { label: "Manufacturer Upc" type: string sql: ${TABLE}.manufacturer_upc ;; } dimension: release_name { label: "Release Name" type: string sql: ${TABLE}.release_name ;; } dimension: contract_name { label: "Contract Name" type: string sql: ${TABLE}.contract_name ;; } dimension: accounting_period_name { label: "Accounting Period Name" type: string sql: ${TABLE}.accounting_period_name ;; } dimension: accounting_period_status { label: "Accounting Period Status" type: string sql: ${TABLE}.accounting_period_status ;; } dimension: account_name { label: "Account Name" type: string sql: ${TABLE}.account_name ;; } dimension: owner { label: "Owner" type: string sql: ${TABLE}.owner ;; } dimension: contract_type { label: "Contract Type" type: string sql: ${TABLE}.contract_type ;; } dimension: statement_period_name { label: "Statement Period Name" type: string sql: ${TABLE}.statement_period_name ;; } # dimension_group: statement_month { # label: "statement_month" # type: time # sql: ${TABLE}.statement_month ;; # timeframes: [ date, # month, # month_num, # month_name, # quarter, # quarter_of_year, # year, # fiscal_month_num, # fiscal_quarter, # fiscal_quarter_of_year, # fiscal_year] # } dimension: statement_month { label: "Statement Month" type: string sql: ${TABLE}.statement_month ;; } dimension: physical_new_upc { label: "Physical New Upc" type: string sql: ${TABLE}.physical_new_upc ;; } dimension: physical_old_upc { label: "Physical Old Upc" type: string sql: ${TABLE}.physical_old_upc ;; } dimension: digital_new_upc { label: "Digital New Upc" type: string sql: ${TABLE}.digital_new_upc ;; } dimension: digital_old_upc { label: "Digital Old Upc" type: string sql: ${TABLE}.digital_old_upc ;; } dimension: is_physical { label: "Is Physical (Y/N)" type: string sql: ${TABLE}.is_physical ;; } dimension: upc_lookup_digital { label: "UPC Lookup Digital" type: number sql: CAST(coalesce(${TABLE}.digital_old_upc, ${TABLE}.display_upc) AS NUMBER) ;; } dimension: upc_lookup_physical { label: "UPC Lookup Physical" type: number sql: CAST(coalesce(${TABLE}.physical_old_upc, ${TABLE}.display_upc) AS NUMBER) ;; } dimension: vendor_catalog_number { label: "Catalog Number" type: string sql: ${TABLE}.vendor_catalog_number ;; } dimension: product_code { type: string sql: ${TABLE}.product_code ;; } }