# View: dt_abacus_replace_double_book_transact

**View Name:** dt_abacus_replace_double_book_transact
**Table Source:** `Derived from: Doubled_Booked_Transactions, Orchard_App_Reporting_V2, Orchard_App_Reporting_V2.Prod_Royalty_Accounting_Royalty_Accounting, Prod.Vw_Abacus_Fact_Sales_Distro_Staging, Prod_Royalty_Accounting_Royalty_Accounting.Accounting_Run + 3 more`
**File Path:** `views/dt_abacus_replace_double_book_transact.view.lkml`

## Overview

- **File Size:** 1942 bytes
- **Lines of Code:** 66
- **Dimensions:** 5
- **Measures:** 1
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `statement_period_id` | number |
| `unique_detail_id` | number |
| `contract_ids` | string |
| `accounting_periods` | string |
| `run_controller_names` | string |

## Measures

| Name | Type |
|------|------|
| `booked_transactions` | number |

## Derived Table

```sql
sql:
      WITH doubled_booked_transactions AS (
        SELECT
          statement_period_id,
          unique_detail_id,
          LISTAGG(contract_id, ',') WITHIN GROUP (ORDER BY contract_id) AS contract_ids,
          LISTAGG(fs.ACCOUNTING_PERIOD_ID, ',') WITHIN GROUP (ORDER BY fs.ACCOUNTING_PERIOD_ID) AS accounting_periods,
          LISTAGG(rc.run_controller_name, ',') WITHIN GROUP (ORDER BY rc.run_controller_name) AS run_controller_names
        FROM
          ROYALTY_ACCOUNTING.PROD.VW_ABACUS_FACT_SALES_DISTRO_STAGING AS fs
        JOIN
          ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNTING_RUN ar ON fs.accounting_run_id = ar.accounting_run_id
        JOIN
          ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.RUN_CONTROLLER rc ON fs.run_controller_id = rc.run_controller_id
        WHERE
          RUN_STATUS IN ('Committed', 'Pending')
        GROUP BY
          statement_period_id, unique_detail_id
        HAVING
          COUNT(*) > 1
      )
      SELECT
        statement_period_id,
        unique_detail_id,
        contract_ids,
        accounting_periods,
        run_controller_names,
        count(*) as booked_transactions
      FROM
        doubled_booked_transactions
        group by 1,2,3,4,5
    ;;
```