view: dt_potential_double_booked_transactions_nr {
  derived_table: {
    sql:
      WITH duplicate_txn AS (
          SELECT arrns.TXN_ID, COUNT(arrns.TXN_ID)
          FROM ROYALTY_ACCOUNTING.PROD.ACCOUNTING_RUN_RESULTS_NR_STAGING arrns
          JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNTING_RUN ar
              ON ar.ACCOUNTING_RUN_ID = arrns.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 = 'neighbouring_rights'
              AND ar.RUN_STATUS IN ('Complete', 'Committed')
          GROUP BY arrns.TXN_ID
          HAVING COUNT(arrns.TXN_ID) > 1
      )
      SELECT
          ctns.ACCOUNT_ID,
          ctns.CONTRACT_ID,
          arrns.*,
          sdsn.*
      FROM ROYALTY_ACCOUNTING.PROD.ACCOUNTING_RUN_RESULTS_NR_STAGING arrns
      JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNTING_RUN ar
          ON ar.ACCOUNTING_RUN_ID = arrns.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_NR_STAGING ctns
          ON ctns.CONTRACT_TXN_ID = arrns.CONTRACT_TXN_ID
      JOIN ROYALTY_ACCOUNTING.PROD.STMT_DB_SALES_NR sdsn
          ON sdsn.STMT_DB_SALES_NR_TXN_ID = arrns.TXN_ID
      WHERE ap.STATEMENT_PERIOD_ID = {% parameter statement_period_id %}
          AND ap.CONTRACT_TYPE = 'neighbouring_rights'
          AND ar.RUN_STATUS IN ('Complete', 'Committed')
          AND arrns.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: royalty_rate {
    view_label: "Potential Double Booked Transactions"
    label: "Royalty Rate"
    type: string
    sql: ${TABLE}.royalty_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_share_sale_currency {
    view_label: "Potential Double Booked Transactions"
    label: "Net Share Sale Currency"
    type: string
    sql: ${TABLE}.net_share_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_share_payee_currency {
    view_label: "Potential Double Booked Transactions"
    label: "Net Share Payee Currency"
    type: string
    sql: ${TABLE}.net_share_payee_currency;;
  }

  dimension: stmt_db_sales_nr_txn_id {
    view_label: "Potential Double Booked Transactions"
    label: "Statement DB Sales NR Transaction ID"
    type: number
    sql: ${TABLE}.stmt_db_sales_nr_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: end_date {
    view_label: "Potential Double Booked Transactions"
    label: "End Date"
    type: string
    sql: ${TABLE}.end_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: contributor_only {
    view_label: "Potential Double Booked Transactions"
    label: "Contributor Only"
    type: string
    sql: ${TABLE}.contributor_only;;
  }

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

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

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

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

  dimension: sound_recording_id {
    view_label: "Potential Double Booked Transactions"
    label: "Sound Recording ID"
    type: string
    sql: ${TABLE}.sound_recording_id;;
  }

  dimension: sound_recording_name {
    view_label: "Potential Double Booked Transactions"
    label: "Sound Recording Name"
    type: number
    sql: ${TABLE}.sound_recording_name;;
  }

  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;;
  }
}