# View: failed_exports

**View Name:** failed_exports
**Table Source:** `Derived from: Awal_Sax, Awal_Sax.Sax, Sax.Agreement_Outgoing_Types, Sax.Agreement_Period_Length_Defs, Sax.Agreement_Periods + 1 more`
**File Path:** `views/failed_exports.view.lkml`

## Overview

- **File Size:** 4530 bytes
- **Lines of Code:** 125
- **Dimensions:** 10
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `society` | string |
| `exporter_date` | date |
| `description` | string |
| `performer_id` | string |
| `performer_name` | string |
| `service_level` | string |
| `recording_id` | string |
| `recording_title` | string |
| `artist` | string |
| `nr_priority` | string |

## SQL Comments

- client agreements

## Derived Table

```sql
sql:
      select
      society.name as society,
      rh.created_date as exporter_date,
      rs.description,
      rc.party_id as performer_id,
      perf.name as performer_name,
      max(CASE WHEN clients.service_level = 1 THEN 'Bronze' WHEN clients.service_level = 2 THEN 'Silver'
      WHEN clients.service_level = 3 THEN 'Gold' WHEN clients.service_level = 4 THEN 'Platinum' END) as service_level,
      rc.recording_id,
      wrc.title as recording_title,
      ra.name as artist,
      wrc.nr_priority
from
      "AWAL_SAX"."SAX"."RECORDING_CONTRIBUTORS" rc
inner join
      "AWAL_SAX"."SAX"."RECORD_NR_REGISTRATIONS" rlr on
      rlr.recording_contributor_id = rc.id
inner join
      "AWAL_SAX"."SAX"."RECORD_NR_REG_HISTORY" rh on
      rh.recording_registration_id = rlr.id
inner join
      "AWAL_SAX"."SAX"."RECORD_NR_REG_STATUSES" rs on
      rs.id = rh.registration_status_id and rs.id = 'RNRRS161'
inner join
      "AWAL_SAX"."SAX"."AGREEMENTS" agr on agr.id = rlr.agreement_id
inner join
      "AWAL_SAX"."SAX"."INTERESTED_PARTIES" society on society.id = agr.acquirer_id
inner join
      "AWAL_SAX"."SAX"."INTERESTED_PARTIES" perf on rc.party_id = perf.id
inner join
      "AWAL_SAX"."SAX"."RECORDINGS" wrc on wrc.id = rc.recording_id
inner join
      "AWAL_SAX"."SAX"."RECORDING_ARTISTS" ra on ra.id = wrc.recording_artist_id
inner join
      (SELECT   IP.ID CONTR_ID,
                                    IP.NAME CONTR_NAME,
                                    NVL(IP.GROUP_NAME, IP.PERFORMER_NAME) GROUP_NAME,
                                    A.ID CONTR_AGR_ID,
                                    CONNECT_BY_ROOT IP.ID CLIENT_ID,
                                    CONNECT_BY_ROOT IP.NAME CLIENT_NAME,
                                    CONNECT_BY_ROOT A.ID CLIENT_AGR_ID,
                                    CONNECT_BY_ROOT A.TERRITORY_SUMMARY CLIENT_TERR_SUM,
                                    CONNECT_BY_ROOT A.PRIMARY_ADMIN_OWNER_ID PRIMARY_ADMIN_OWNER_ID,
                                    CONNECT_BY_ROOT A.SECONDARY_ADMIN_OWNER_ID SECONDARY_ADMIN_OWNER_ID,
                                    TO_CHAR(AP.START_DATE, 'DD/MM/YYYY') AP_START_DATE,
                                    CONNECT_BY_ROOT A.TASK_SERVICE_LEVEL SERVICE_LEVEL
                               FROM awal_sax.sax.AGREEMENTS A
                         INNER JOIN awal_sax.sax.INTERESTED_PARTIES IP ON A.ASSIGNOR_ID = IP.ID
                    LEFT OUTER JOIN awal_sax.sax.AGREEMENT_PERIODS AP
                                 ON AP.AGREEMENT_ID = A.ID
                                AND PERIOD_LENGTH_DEFINITION_ID IN (SELECT ID FROM awal_sax.sax.AGREEMENT_PERIOD_LENGTH_DEFS WHERE CODE = 'FIXED_DATES')
                              WHERE IP.IP_TYPE = 'Person'
                         START WITH A.ID IN (SELECT A.ID
                                               FROM awal_sax.sax.AGREEMENTS A
                                         INNER JOIN awal_sax.sax.AGREEMENT_OUTGOING_TYPES AOT ON A.AGREEMENT_OUTGOING_TYPE_ID = AOT.ID
                                              WHERE AOT.CODE = 'CLIENT'
                                                AND A.CLIENT_RIGHTS = 'Neighbouring Rights') -- client agreements
                   CONNECT BY PRIOR A.ASSIGNOR_ID = A.ACQUIRER_ID
                                AND A.AGREEMENT_TYPE_ID IS NULL
                                AND A.USE_FOR_NEIGHBOURING = 'Y') clients on clients.contr_id = perf.id
group by
      society.name,
      rh.created_date,
      rs.description,
      rc.party_id,
      perf.name,
      rc.recording_id,
      wrc.title,
      ra.name,
      wrc.nr_priority;;
```