view: dt_potential_double_booked_transactions_distro {
  derived_table: {
    sql:
      WITH duplicate_txn AS (
          SELECT arrds.TXN_ID, COUNT(arrds.TXN_ID)
          FROM ROYALTY_ACCOUNTING.PROD.ACCOUNTING_RUN_RESULTS_DISTRO_STAGING arrds
          JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNTING_RUN ar
              ON ar.ACCOUNTING_RUN_ID = arrds.ACCOUNTING_RUN_ID
          JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNTING_PERIOD ap
              ON ap.ACCOUNTING_PERIOD_ID = ar.ACCOUNTING_PERIOD_ID
          WHERE ap.STATEMENT_PERIOD_ID = {% parameter statement_period_id %}
              AND ap.CONTRACT_TYPE = 'distribution'
              AND ar.RUN_STATUS IN ('Complete', 'Committed')
          GROUP BY arrds.TXN_ID
          HAVING COUNT(arrds.TXN_ID) > 1
      )
      SELECT
          cts.ACCOUNT_ID,
          cts.CONTRACT_ID,
          arrds.*,
          sdsd.*
      FROM ROYALTY_ACCOUNTING.PROD.ACCOUNTING_RUN_RESULTS_DISTRO_STAGING arrds
      JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNTING_RUN ar
          ON ar.ACCOUNTING_RUN_ID = arrds.ACCOUNTING_RUN_ID
      JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNTING_PERIOD ap
          ON ap.ACCOUNTING_PERIOD_ID = ar.ACCOUNTING_PERIOD_ID
      JOIN ROYALTY_ACCOUNTING.PROD.CONTRACT_TRANSACTION_STAGING cts
          ON cts.CONTRACT_TXN_ID = arrds.CONTRACT_TXN_ID
      JOIN ROYALTY_ACCOUNTING.PROD.STMT_DB_SALES_DISTRO sdsd
          ON sdsd.STMT_DB_SALES_DISTRO_TXN_ID = arrds.TXN_ID
      WHERE ap.STATEMENT_PERIOD_ID = {% parameter statement_period_id %}
          AND ap.CONTRACT_TYPE = 'distribution'
          AND ar.RUN_STATUS IN ('Complete', 'Committed')
          AND arrds.TXN_ID IN (SELECT TXN_ID FROM duplicate_txn)
      ;;
  }

  parameter: statement_period_id {
    view_label: "Filter Fields"
    label: "Statement Period ID"
    type: number
  }

  dimension: account_id {
    view_label: "Potential Double Booked Transactions"
    label: "Account ID"
    type: number
    sql: ${TABLE}.account_id;;
  }

  dimension: contract_id {
    view_label: "Potential Double Booked Transactions"
    label: "Contract ID"
    type: number
    sql: ${TABLE}.contract_id;;
  }

  dimension: contract_txn_id {
    view_label: "Potential Double Booked Transactions"
    label: "Contract Transaction ID"
    type: number
    sql: ${TABLE}.contract_txn_id;;
  }

  dimension: txn_id {
    view_label: "Potential Double Booked Transactions"
    label: "Transaction ID"
    type: number
    sql: ${TABLE}.txn_id;;
  }

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

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

  dimension: term_rate {
    view_label: "Potential Double Booked Transactions"
    label: "Term Rate"
    type: string
    sql: ${TABLE}.term_rate;;
  }

  dimension: unit_price_sale_currency {
    view_label: "Potential Double Booked Transactions"
    label: "Unit Price Sale Currency"
    type: string
    sql: ${TABLE}.unit_price_sale_currency;;
  }

  dimension: gross_revenue_sale_currency {
    view_label: "Potential Double Booked Transactions"
    label: "Gross Revenue Sale Currency"
    type: string
    sql: ${TABLE}.gross_revenue_sale_currency;;
  }

  dimension: withholding_tax_sale_currency {
    view_label: "Potential Double Booked Transactions"
    label: "Withholding Tax Sale Currency"
    type: string
    sql: ${TABLE}.withholding_tax_sale_currency;;
  }

  dimension: gross_revenue_after_withholding_tax_sale_currency {
    view_label: "Potential Double Booked Transactions"
    label: "Gross Revenue After Withholding Tax Sale Currency"
    type: string
    sql: ${TABLE}.gross_revenue_after_withholding_tax_sale_currency;;
  }

  dimension: net_revenue_sale_currency {
    view_label: "Potential Double Booked Transactions"
    label: "Net Revenue Sale Currency"
    type: string
    sql: ${TABLE}.net_revenue_sale_currency;;
  }

  dimension: distribution_fee_sale_currency {
    view_label: "Potential Double Booked Transactions"
    label: "Distribution Fee Sale Currency"
    type: string
    sql: ${TABLE}.distribution_fee_sale_currency;;
  }

  dimension: unit_price_payee_currency {
    view_label: "Potential Double Booked Transactions"
    label: "Unit Price Payee Currency"
    type: string
    sql: ${TABLE}.unit_price_payee_currency;;
  }

  dimension: gross_revenue_payee_currency {
    view_label: "Potential Double Booked Transactions"
    label: "Gross Revenue Payee Currency"
    type: string
    sql: ${TABLE}.gross_revenue_payee_currency;;
  }

  dimension: withholding_tax_payee_currency {
    view_label: "Potential Double Booked Transactions"
    label: "Withholding Tax Payee Currency"
    type: string
    sql: ${TABLE}.withholding_tax_payee_currency;;
  }

  dimension: gross_revenue_after_withholding_tax_payee_currency {
    view_label: "Potential Double Booked Transactions"
    label: "Gross Revenue After Withholding Tax Payee Currency"
    type: string
    sql: ${TABLE}.gross_revenue_after_withholding_tax_payee_currency;;
  }

  dimension: net_revenue_payee_currency {
    view_label: "Potential Double Booked Transactions"
    label: "Net Revenue Payee Currency"
    type: string
    sql: ${TABLE}.net_revenue_payee_currency;;
  }

  dimension: distribution_fee_payee_currency {
    view_label: "Potential Double Booked Transactions"
    label: "Distribution Fee Payee Currency"
    type: string
    sql: ${TABLE}.distribution_fee_payee_currency;;
  }

  dimension: stmt_db_sales_distro_txn_id {
    view_label: "Potential Double Booked Transactions"
    label: "Statement DB Sales Distro Transaction ID"
    type: number
    sql: ${TABLE}.stmt_db_sales_distro_txn_id;;
  }

  dimension: unique_detail_id {
    view_label: "Potential Double Booked Transactions"
    label: "Unique Detail ID"
    type: number
    sql: ${TABLE}.unique_detail_id;;
  }

  dimension: sales_file_id {
    view_label: "Potential Double Booked Transactions"
    label: "Sales File ID"
    type: number
    sql: ${TABLE}.sales_file_id;;
  }

  dimension: batch_id {
    view_label: "Potential Double Booked Transactions"
    label: "Batch ID"
    type: string
    sql: ${TABLE}.batch_id;;
  }

  dimension: statement_id {
    view_label: "Potential Double Booked Transactions"
    label: "Statement ID"
    type: number
    sql: ${TABLE}.statement_id;;
  }

  dimension: start_date {
    view_label: "Potential Double Booked Transactions"
    label: "Start Date"
    type: string
    sql: ${TABLE}.start_date;;
  }

  dimension: transaction_date {
    view_label: "Potential Double Booked Transactions"
    label: "Transaction Date"
    type: string
    sql: ${TABLE}.transaction_date;;
  }

  dimension: store_id {
    view_label: "Potential Double Booked Transactions"
    label: "Store ID"
    type: string
    sql: ${TABLE}.store_id;;
  }

  dimension: subdistributor {
    view_label: "Potential Double Booked Transactions"
    label: "Subdistributor"
    type: string
    sql: ${TABLE}.subdistributor;;
  }

  dimension: sale_currency_code {
    view_label: "Potential Double Booked Transactions"
    label: "Sale Currency Code"
    type: string
    sql: ${TABLE}.sale_currency_code;;
  }

  dimension: sale_currency_code_id {
    view_label: "Potential Double Booked Transactions"
    label: "Sale Currency Code ID"
    type: number
    sql: ${TABLE}.sale_currency_code_id;;
  }

  dimension: activity_rate {
    view_label: "Potential Double Booked Transactions"
    label: "Activity Rate"
    type: string
    sql: ${TABLE}.activity_rate;;
  }

  dimension: country_id {
    view_label: "Potential Double Booked Transactions"
    label: "Country ID"
    type: number
    sql: ${TABLE}.country_id;;
  }

  dimension: configuration {
    view_label: "Potential Double Booked Transactions"
    label: "Configuration"
    type: string
    sql: ${TABLE}.configuration;;
  }

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

  dimension: transaction_type_id {
    view_label: "Potential Double Booked Transactions"
    label: "Transaction Type ID"
    type: number
    sql: ${TABLE}.transaction_type_id;;
  }

  dimension: transaction_subtype_id {
    view_label: "Potential Double Booked Transactions"
    label: "Transaction Subtype ID"
    type: number
    sql: ${TABLE}.transaction_subtype_id;;
  }

  dimension: label_id {
    view_label: "Potential Double Booked Transactions"
    label: "Label ID"
    type: number
    sql: ${TABLE}.label_id;;
  }

  dimension: upc {
    view_label: "Potential Double Booked Transactions"
    label: "UPC"
    type: string
    sql: ${TABLE}.upc;;
  }

  dimension: cd {
    view_label: "Potential Double Booked Transactions"
    label: "CD"
    type: number
    sql: ${TABLE}.cd;;
  }

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

  dimension: isrc {
    view_label: "Potential Double Booked Transactions"
    label: "ISRC"
    type: string
    sql: ${TABLE}.isrc;;
  }

  dimension: track_name {
    view_label: "Potential Double Booked Transactions"
    label: "Track Name"
    type: string
    sql: ${TABLE}.track_name;;
  }

  dimension: video_id {
    view_label: "Potential Double Booked Transactions"
    label: "Video ID"
    type: number
    sql: ${TABLE}.video_id;;
  }

  dimension: quantity {
    view_label: "Potential Double Booked Transactions"
    label: "Quantity"
    type: number
    sql: ${TABLE}.quantity;;
  }

  dimension: unit_price_usd {
    view_label: "Potential Double Booked Transactions"
    label: "Unit Price USD"
    type: string
    sql: ${TABLE}.unit_price_usd;;
  }

  dimension: total_usd {
    view_label: "Potential Double Booked Transactions"
    label: "Total USD"
    type: string
    sql: ${TABLE}.total_usd;;
  }

  dimension: withholding_tax_usd {
    view_label: "Potential Double Booked Transactions"
    label: "Withholding Tax USD"
    type: string
    sql: ${TABLE}.withholding_tax_usd;;
  }

  dimension: retail_price_usd {
    view_label: "Potential Double Booked Transactions"
    label: "Retail Price USD"
    type: string
    sql: ${TABLE}.retail_price_usd;;
  }

  dimension: original_price_usd {
    view_label: "Potential Double Booked Transactions"
    label: "Original Price USD"
    type: string
    sql: ${TABLE}.original_price_usd;;
  }

  dimension: discount_usd {
    view_label: "Potential Double Booked Transactions"
    label: "Discount USD"
    type: string
    sql: ${TABLE}.discount_usd;;
  }

  dimension: phys_ppd_usd {
    view_label: "Potential Double Booked Transactions"
    label: "Phys PPD USD"
    type: string
    sql: ${TABLE}.phys_ppd_usd;;
  }

  dimension: is_excluded_from_sap {
    view_label: "Potential Double Booked Transactions"
    label: "Is Excluded From SAP"
    type: string
    sql: ${TABLE}.is_excluded_from_sap;;
  }

  dimension: actual_statement_number {
    view_label: "Potential Double Booked Transactions"
    label: "Actual Statement Number"
    type: string
    sql: ${TABLE}.actual_statement_number;;
  }
}