# View: dt_abacus_contract_lifecycle_prod

**View Name:** dt_abacus_contract_lifecycle_prod
**Table Source:** `Derived from: Contract_Lifecycle, Orchard_App_Reporting_V2, Orchard_App_Reporting_V2.Prod_Royalty_Accounting_Royalty_Accounting, Prod.Vw_Dim_Abacus_Contract, Prod_Royalty_Accounting_Royalty_Accounting.Contract_Lifecycle + 2 more`
**File Path:** `views/dt_abacus_contract_lifecycle_prod.view.lkml`

## Overview

- **File Size:** 5185 bytes
- **Lines of Code:** 206
- **Dimensions:** 19
- **Measures:** 0
- **Dimension Groups:** 2
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `collection_start` | string |
| `renewal_effective` | date_time |
| `account_id` | string |
| `deleted_by` | string |
| `created_at` | string |
| `contract_id` | string |
| `collection_end` | string |
| `contract_lifecycle_schedule_id` | string |
| `created_by` | string |
| `termination_notice_received` | string |
| `contract_lifecycle_id` | string |
| `termination_effective` | string |
| `lifecycle_status` | string |
| `last_modified_by` | string |
| `last_modified` | date_time |
| `deleted_at` | date_time |
| `termination_notice_deadline` | date_time |
| `total_contracts_on_account` | string |
| `has_active_contract_post_termination` | string |

## Dimension Groups

| Name | Type |
|------|------|
| `lifecycle_term_start` | time |
| `lifecycle_term_end` | time |

## Derived Table

```sql
sql:
     WITH contract_lifecycle as (
          select
              cl.*,
              dac.account_id
          from
              orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract_lifecycle cl
              JOIN royalty_accounting.prod.vw_dim_abacus_contract dac on dac.contract_id = cl.contract_id
          where
              cl.deleted_at is null
              and cl.lifecycle_status != 'inactive'
      ),
      total_contract_on_account_cte AS (
          select
              account_id,
              count(distinct contract_id) as total_contracts_on_account
          from
              royalty_accounting.prod.vw_dim_abacus_contract
          group by
              1
      ),
      has_active_contract_cte AS (
          select
              cl.account_id,
              cl.contract_id,
              CASE
                  WHEN cl.lifecycle_status != 'to_be_terminated' then NULL
                  WHEN cl.lifecycle_status = 'to_be_terminated' THEN (
                      CASE
                          WHEN MAX (
                              CASE
                                  WHEN cl2.lifecycle_status = 'active' then 1
                                  else 0
                              end
                          ) = 1 THEN True
                          WHEN MAX (
                              CASE
                                  WHEN cl2.lifecycle_status = 'init'
                                  and cl2.lifecycle_term_start <= cl.lifecycle_term_end THEN 1
                                  ELSE 0
                              END
                          ) = 1 THEN True
                          ELSE False
                      END
                  )
                  ELSE False
              END as has_active_contract_post_termination
          from
              contract_lifecycle cl
              left join contract_lifecycle cl2 on cl2.contract_id != cl.contract_id
              and cl2.account_id = cl.account_id
          group by
              1,
              2,
              cl.lifecycle_status
      )
      select
          cl.*,
          total_contracts_on_account,
          has_active_contract_post_termination
      from
          contract_lifecycle cl
          left join total_contract_on_account_cte tca on tca.account_id = cl.account_id
          left join has_active_contract_cte hac on hac.account_id = cl.account_id
          and cl.contract_id = hac.contract_id;;
```