# View: detailed_catalogue_report

**View Name:** detailed_catalogue_report
**Table Source:** `Derived from: Sax.Exp_Version_Owners, Sax.Exp_Versions, Sax.Expv_Configuration_Types, Sax.Expv_Configurations, Sax.Expv_Delivery_Priorities + 3 more`
**File Path:** `DOSC/Detailed_catalogue_report/views/detailed_catalogue_report.view.lkml`

## Overview

- **File Size:** 10506 bytes
- **Lines of Code:** 363
- **Dimensions:** 38
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `owner_id` | string |
| `client_tier` | string |
| `expv_id` | string |
| `label_name` | string |
| `catalogue_number` | string |
| `upc` | string |
| `nod` | string |
| `product_artist` | string |
| `product_title` | string |
| `product_version` | string |
| `product_description` | string |
| `genre` | string |
| `cdate` | string |
| `cline` | string |
| `pdate` | string |
| `pline` | string |
| `original_release_date` | string |
| `format` | string |
| `engineer_email` | string |
| `included_territories` | string |
| `awal_release_date` | string |
| `disc_number` | number |
| `side` | string |
| `track_number` | number |
| `isrc` | string |
| `track_title` | string |
| `track_title_version` | string |
| `track_artist` | string |
| `num_recversions` | string |
| `audio_video` | string |
| `parental_warning` | string |
| `audio_language` | string |
| `track_pdate` | string |
| `track_pline` | string |
| `duration` | string |
| `preview_start_index` | string |
| `contributors` | string |
| `additional_parties` | string |

## Derived Table

```sql
sql:
      SELECT
        EXP_VERSION_OWNERS.OWNER_ID AS OWNER_ID,
        RECORDINGS_PRIORITY_RANKS.DESCRIPTION AS CLIENT_TIER,
        EXP_VERSIONS.ID AS EXP_VERSION_ID,
        AWAL_LABELS.NAME AS LABEL,
        EXPV_CONFIGURATIONS.CATALOGUE_NUMBER AS CATALOGUE_NUMBER,
        EAN AS UPC,
        EXP_VERSIONS.NOD AS NOD,
        RECORDING_ARTISTS.NAME AS PRODUCT_ARTIST,
        EXPLOITATION_TITLE AS PRODUCT_TITLE,
        EXP_VERSIONS.SUBTITLE AS PRODUCT_VERSION,
        EXP_VERSIONS.DESCRIPTION AS PRODUCT_DESCRIPTION,
        RECORDING_GENRES.DESCRIPTION AS GENRE,
        EXP_VERSIONS.CDATE AS CDATE,
        EXP_VERSIONS.CLINE AS CLINE,
        EXP_VERSIONS.PDATE AS PDATE,
        EXP_VERSIONS.PLINE AS PLINE,
        TO_CHAR(ORIGINAL_RELEASE_DATE, 'YYYY-MM-DD') ORIGINAL_RELEASE_DATE,
        EXPV_CONFIGURATION_TYPES.NAME AS FORMAT,
        EXP_VERSIONS.ENGINEER_EMAIL AS ENGINEER_EMAIL,
        INCLUDED_TERRITORIES.territories AS INCLUDED_TERRITORIES,
          (SELECT TO_CHAR(MIN(DEALS.SALES_START_DATE), 'YYYY-MM-DD')
          FROM AWAL_SAX.SAX.DEALS
          WHERE  DEALS.EXP_VERSION_ID = EXP_VERSIONS.ID) AS AWAL_RELEASE_DATE,
        EXP_VERSION_RECORDINGS.DISC_NUMBER AS DISC_NUMBER,
        EXP_VERSION_RECORDINGS.SIDE AS SIDE,
        EXP_VERSION_RECORDINGS.TRACK_NUMBER AS TRACK_NUMBER,
        RECORDINGS.ISRC AS ISRC,
        RECORDINGS.TITLE AS TRACK_TITLE,
        CASE WHEN RECORDINGS.SUB_TITLE_3 IS NOT NULL THEN RECORDINGS.SUB_TITLE || ' ' || RECORDINGS.SUB_TITLE_2 || ' ' || RECORDINGS.SUB_TITLE_3
          WHEN RECORDINGS.SUB_TITLE_2 IS NOT NULL THEN RECORDINGS.SUB_TITLE || ' ' || RECORDINGS.SUB_TITLE_2
          ELSE RECORDINGS.SUB_TITLE
        END AS TRACK_TITLE_VERSION,
        TRACK_RECORDING_ARTISTS.NAME AS TRACK_ARTIST,
          (SELECT COUNT (*)
          FROM AWAL_SAX.SAX.RECORDING_VERSIONS
          WHERE RECORDING_ID = RECORDINGS.ID) AS NUM_RECVERSIONS,
        RECORDINGS.AUDIO_VIDEO        AS AUDIO_VIDEO,
        DDEX_PARENTAL_WARN_TYPES.ITUNES_VALUE   AS PARENTAL_WARNING,
        LANGUAGE_FORMS.DESCRIPTION        AS AUDIO_LANGUAGE,
        RECORDINGS.PHONOGRAPHIC_DATE      AS TRACK_PDATE,
        RECORDINGS.PHONOGRAPHIC_OWNER       AS TRACK_PLINE,
        RECORDINGS.DURATION         AS DURATION,
        RECORDINGS.PREVIEW_START_INDEX      AS PREVIEW_START_INDEX,
          (SELECT LISTAGG(CONTRIBUTOR, '|')
              WITHIN GROUP (ORDER BY CONTRIBUTOR)
          FROM  (
            SELECT RECORDING_ARTISTS.NAME || ' - ' || ADDITIONAL_ARTIST_ROLES.DESCRIPTION AS CONTRIBUTOR
              FROM AWAL_SAX.SAX.RECORDING_ARTISTS, AWAL_SAX.SAX.ADDITIONAL_ARTIST_ROLES, AWAL_SAX.SAX.RECORD_ADDITIONAL_ARTISTS
              WHERE RECORD_ADDITIONAL_ARTISTS.ADDITIONAL_ARTIST_ROLE_ID = ADDITIONAL_ARTIST_ROLES.ID
               AND  RECORD_ADDITIONAL_ARTISTS.RECORDING_ARTIST_ID = RECORDING_ARTISTS.ID
               AND RECORD_ADDITIONAL_ARTISTS.RECORDING_ID = RECORDINGS.ID)
          ) AS CONTRIBUTORS,
          (SELECT LISTAGG(ADDITONAL_IP, '|')
              WITHIN GROUP (ORDER BY ADDITONAL_IP)
          FROM  (
            SELECT INTERESTED_PARTIES.NAME || ' - ' || ADDITIONAL_IP_ROLES.DESCRIPTION AS ADDITONAL_IP
              FROM AWAL_SAX.SAX.INTERESTED_PARTIES, AWAL_SAX.SAX.ADDITIONAL_IP_ROLES, AWAL_SAX.SAX.RECORD_ADDITIONAL_IPS
              WHERE RECORD_ADDITIONAL_IPS.ADDITIONAL_IP_ROLE_ID = ADDITIONAL_IP_ROLES.ID
               AND  RECORD_ADDITIONAL_IPS.IP_ID = INTERESTED_PARTIES.ID
               AND RECORD_ADDITIONAL_IPS.RECORDING_ID = RECORDINGS.ID)
          ) AS ADDITIONAL_PARTIES
      FROM
        AWAL_SAX.SAX.EXPV_CONFIGURATIONS INNER JOIN AWAL_SAX.SAX.EXP_VERSIONS ON EXP_VERSIONS.ID = EXPV_CONFIGURATIONS.EXP_VERSION_ID
        INNER JOIN AWAL_SAX.SAX.EXPV_CONFIGURATION_TYPES ON EXPV_CONFIGURATIONS.CONFIGURATION_TYPE_ID = EXPV_CONFIGURATION_TYPES.ID
        INNER JOIN AWAL_SAX.SAX.EXPLOITATIONS ON EXPLOITATIONS.ID = EXP_VERSIONS.EXPLOITATION_ID
        INNER JOIN AWAL_SAX.SAX.RECORDING_ARTISTS ON EXPLOITATIONS.EXPLOITATION_ARTIST_ID = RECORDING_ARTISTS.ID
        INNER JOIN AWAL_SAX.SAX.EXPV_DELIVERY_PRIORITIES ON EXPV_DELIVERY_PRIORITIES.ID = EXP_VERSIONS.DELIVERY_PRIORITY_ID
        LEFT JOIN AWAL_SAX.SAX.EXP_VERSION_OWNERS ON EXP_VERSION_OWNERS.EXP_VERSION_ID = EXP_VERSIONS.ID
        LEFT JOIN AWAL_SAX.SAX.AWAL_LABELS ON AWAL_LABELS.ID = EXP_VERSIONS.AWAL_LABEL_ID
        LEFT JOIN AWAL_SAX.SAX.RECORDING_GENRES ON RECORDING_GENRES.ID = EXP_VERSIONS.DDEX_GENRE_1
        LEFT JOIN AWAL_SAX.SAX.EXPLOITATION_FORMATS ON EXPLOITATION_FORMATS.ID = EXPLOITATIONS.EXPLOITATION_FORMAT_ID
        LEFT JOIN AWAL_SAX.SAX.EXP_VERSION_RECORDINGS ON EXP_VERSION_RECORDINGS.EXP_VERSION_ID = EXP_VERSIONS.ID
        LEFT JOIN AWAL_SAX.SAX.RECORDINGS ON  RECORDINGS.ID = EXP_VERSION_RECORDINGS.RECORDING_ID
        LEFT JOIN AWAL_SAX.SAX.DDEX_PARENTAL_WARN_TYPES ON DDEX_PARENTAL_WARN_TYPES.ID = NVL(RECORDINGS.PARENTAL_WARNING_TYPE_ID, 'RPWT4')
        LEFT JOIN AWAL_SAX.SAX.LANGUAGE_FORMS ON LANGUAGE_FORMS.ID = RECORDINGS.LANGUAGE_ID
        LEFT JOIN AWAL_SAX.SAX.RECORDING_ARTISTS TRACK_RECORDING_ARTISTS ON TRACK_RECORDING_ARTISTS.ID = RECORDINGS.RECORDING_ARTIST_ID
        INNER JOIN AWAL_SAX.SAX.INTERESTED_PARTIES ON INTERESTED_PARTIES.ID = EXP_VERSION_OWNERS.OWNER_ID
        LEFT JOIN AWAL_SAX.SAX.RECORDINGS_PRIORITY_RANKS ON INTERESTED_PARTIES.RECORDINGS_PRIORITY_RANK_ID = RECORDINGS_PRIORITY_RANKS.ID
        LEFT JOIN (SELECT EXP_VERSION_ID, LISTAGG(TERRITORY_NAME, '|')
              WITHIN GROUP (ORDER BY TERRITORY_NAME) AS territories
              FROM  (
                SELECT DEALS.EXP_VERSION_ID, TERRITORIES.NAME AS TERRITORY_NAME
                  FROM AWAL_SAX.SAX.DEALS, AWAL_SAX.SAX.DEAL_TERRITORIES, AWAL_SAX.SAX.TERRITORIES
                  WHERE TERRITORIES.ID = DEAL_TERRITORIES.TERRITORY_ID
                    AND DEALS.ID = DEAL_TERRITORIES.DEAL_ID
                    AND (DEALS.SALES_END_DATE IS NULL OR DEALS.SALES_END_DATE > current_date)
                    GROUP BY EXP_VERSION_ID, TERRITORIES.NAME)
                  GROUP BY EXP_VERSION_ID) AS INCLUDED_TERRITORIES ON INCLUDED_TERRITORIES.EXP_VERSION_ID = EXP_VERSIONS.ID
      ORDER BY OWNER_ID, LABEL, PRODUCT_ARTIST, EAN, DISC_NUMBER, SIDE, TRACK_NUMBER;;
```