view: client_emails {
  derived_table: {
    sql:
    WITH CLIENTS AS (          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'
                                /*AND DECODE(IP.ID,                  -- For old agreements with non-standard set up
                                           'IP98853', 'AGR316757',
                                           'IP98854', 'AGR316737',
                                           'IP300576', 'AGR293783',
                                           'IP300577', 'AGR296337',
                                           'IP210353', 'AGR296220',
                                           'IP246238', 'AGR297039',
                                           'IP71436', 'AGR225801',
                                           'IP72396', 'AGR225806',
                                           'IP72394', 'AGR225804',
                                           'IP71507', 'AGR225802',
                                           'IP58428', 'AGR225805',
                                           CONNECT_BY_ROOT A.ID) = CONNECT_BY_ROOT A.ID*/
                         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')

                        SELECT   ACQUIRERS.NAME                                                                 "ACQUIRER",
                                 C.CLIENT_NAME                                                                  "CLIENT_NAME",
                                 C.CLIENT_ID                                                                    "CLIENT_ID",
                                 C.CLIENT_AGR_ID                                                                "CLIENT_AGR_ID",
                                 IP.PERSON_FORENAME                                                             "FIRST_NAME",
                                 IP.PERSON_SURNAME                                                              "LAST_NAME",
                                 IP.EMAIL_ADDRESS                                                               "EMAIL_ADDRESS",
                                 CLIENT_OWNERS.NAME                                                             "CLIENT_OWNER"
                        FROM CLIENTS C
                        LEFT OUTER JOIN awal_sax.sax.INTERESTED_PARTIES    IP                ON IP.ID = C.CONTR_ID
                        LEFT OUTER JOIN awal_sax.sax.IP_ARTIST_ALIASES     IP_ALIASES        ON IP.ID = IP_ALIASES.PARTY_ID
                        LEFT OUTER JOIN awal_sax.sax.RECORDING_ARTISTS     REC_ART           ON IP_ALIASES.ARTIST_ID = REC_ART.ID
                        LEFT OUTER JOIN awal_sax.sax.TERRITORIES           BIRTH_COUNTRY     ON IP.COUNTRY_OF_BIRTH_ID = BIRTH_COUNTRY.ID
                        LEFT OUTER JOIN awal_sax.sax.IP_CITIZENSHIPS       IP_CITI           ON IP.ID = IP_CITI.PARTY_ID
                        LEFT OUTER JOIN awal_sax.sax.TERRITORIES           CITIZENSHIP       ON IP_CITI.TERRITORY_ID = CITIZENSHIP.ID
                        LEFT OUTER JOIN awal_sax.sax.IP_RESIDENCIES        IP_RES            ON IP.ID = IP_RES.PARTY_ID
                        LEFT OUTER JOIN awal_sax.sax.TERRITORIES           RESIDENCY         ON IP_RES.TERRITORY_ID = RESIDENCY.ID
                        LEFT OUTER JOIN awal_sax.sax.AGREEMENTS            AGGR              ON AGGR.ID = C.CLIENT_AGR_ID
                        LEFT OUTER JOIN awal_sax.sax.AGREEMENT_PERIODS     AGGR_PERDS        ON AGGR.ID = AGGR_PERDS.AGREEMENT_ID
                        LEFT OUTER JOIN awal_sax.sax.AGREEMENT_PERIODS     AGGR_COL          ON AGGR.ID = AGGR_COL.AGREEMENT_ID AND AGGR_COL.PERIOD_TYPE = 'Collection'
                        LEFT OUTER JOIN awal_sax.sax.INTERESTED_PARTIES    ACQUIRERS         ON AGGR.ACQUIRER_ID = ACQUIRERS.ID
                        LEFT OUTER JOIN awal_sax.sax.SAX_USERS             CLIENT_OWNERS     ON CLIENT_OWNERS.ID = C.PRIMARY_ADMIN_OWNER_ID
                        WHERE   C.CLIENT_AGR_ID IS NOT NULL
                        AND     C.CONTR_ID IS NOT NULL
                        AND     C.CLIENT_AGR_ID IS NOT NULL
                        GROUP BY IP.PERSON_SURNAME,
                                 IP.PERSON_FORENAME,
                                 IP.PERSON_MIDDLE_1,
                                 IP.PERSON_MIDDLE_2,
                                 IP.GROUP_NAME,
                                 IP.EMAIL_ADDRESS,
                                 IP.PERFORMER_NAME,
                                 CASE
                                    WHEN UPPER(REC_ART.ALIAS_BAND) = 'ALIAS' THEN REC_ART.NAME
                                 END,
                                 CASE
                                    WHEN UPPER(REC_ART.ALIAS_BAND) = 'BAND' THEN REC_ART.NAME
                                 END,
                                 IP.SEX,
                                 IP.PERSON_DOB,
                                 IP.PERSON_DOD,
                                 BIRTH_COUNTRY.NAME,
                                 CITIZENSHIP.NAME,
                                 RESIDENCY.NAME,
                                 IP.IPDA_IPN,
                                 IP.FEATURED,
                                 AGGR.TERRITORY_SUMMARY,
                                 AGGR.AGREEMENT_SIGNED_DATE,
                                 CLIENT_OWNERS.NAME,
                                 C.SECONDARY_ADMIN_OWNER_ID,
                                 C.CLIENT_NAME,
                                 C.CLIENT_ID,
                                 C.CLIENT_AGR_ID,
                                 C.CONTR_ID,
                                 C.CONTR_AGR_ID,
                                 C.SERVICE_LEVEL,
                                 ACQUIRERS.NAME,
                                 AGGR.IS_TERMINATED,
                                 AGGR.TERMINATED_DATE,
                                 AGGR.NR_AGREEMENT_TYPE}

  dimension: ACQUIRER {
    type: string
    sql: ${TABLE}.ACQUIRERS.NAME ;;
  }

  dimension: CLIENT_NAME {
    type:  string
    sql: ${TABLE}.CLIENT_NAME ;;
  }

  dimension: CLIENT_ID {
    type: string
    sql: ${TABLE}.CLIENT_ID ;;
  }

  dimension: CLIENT_AGR_ID {
    type: string
    sql: ${TABLE}.CLIENT_AGR_ID ;;
  }

  dimension: First_Name {
    type:  string
    sql: ${TABLE}.PERSON_FORENAME ;;
  }

  dimension: Last_Name {
    type:  string
    sql: ${TABLE}.PERSON_SURNAME ;;
  }

  dimension: EMAIL_ADDRESS {
    type: string
    sql: ${TABLE}.EMAIL_ADDRESS ;;
  }

  dimension: CLIENT_OWNER {
    type:  string
    sql: ${TABLE}.CLIENT_OWNER ;;
  }
}