# View: client_overview_full **View Name:** client_overview_full **Table Source:** `Derived from: Sax.Agreement_Outgoing_Types, Sax.Agreement_Period_Length_Defs, Sax.Agreement_Periods, Sax.Interested_Parties, Sax.Ip_Artist_Aliases + 3 more` **File Path:** `views/client_overview_full.view.lkml` ## Overview - **File Size:** 19615 bytes - **Lines of Code:** 339 - **Dimensions:** 32 - **Measures:** 0 - **Dimension Groups:** 0 - **Filters:** 0 ## Dimensions | Name | Type | |------|------| | `Client_ID` | string | | `Client_Agreement_ID` | string | | `Contributor_ID` | string | | `Contributor_Agreement_ID` | string | | `Acquirer` | string | | `Full_Name` | string | | `Full_Name_CSV` | string | | `Last_Name` | string | | `First_Name` | string | | `Second_Name` | string | | `Third_Name` | string | | `Alias` | string | | `Bands` | string | | `Gender` | string | | `Date_of_Birth` | date | | `Date_of_Death` | date | | `Coutry_of_Birth` | string | | `Citizenship` | string | | `Residency` | string | | `IPN_Number` | string | | `Status` | string | | `Controlled_Territory` | string | | `Rights_Start_Date` | date | | `Terminated` | string | | `End_of_Term` | date | | `Collection_End_Date` | date | | `Signed_Date` | date | | `Repertoire_Owner` | string | | `Client_Owner` | string | | `Client_Service_Level` | string | | `Client_Name` | string | | `Type` | string | ## SQL Comments - WHERE IP.IP_TYPE = 'Person' - for labels to show up - For old agreements with non-standard set up - client agreements ## Derived Table ```sql 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' - for labels to show up /*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'), CLIENT_INFO AS ( SELECT IP.PERSON_SURNAME "LAST_NAME", IP.PERSON_FORENAME "FIRST_NAME", IP.PERSON_MIDDLE_1 "SECOND_NAME", IP.PERSON_MIDDLE_2 "THIRD_NAME", IP.GROUP_NAME "BAND", IP.PERFORMER_NAME "ARTIST", CASE WHEN UPPER(REC_ART.ALIAS_BAND) = 'ALIAS' THEN REC_ART.NAME END "ALIAS", CASE WHEN UPPER(REC_ART.ALIAS_BAND) = 'BAND' THEN REC_ART.NAME END "BANDS", IP.SEX "GENDER", IP.PERSON_DOB "DOB", IP.PERSON_DOD "DOD", BIRTH_COUNTRY.NAME "BIRTH_COUNTRY", CITIZENSHIP.NAME "CITIZENSHIP", RESIDENCY.NAME "RESIDENCY", IP.IPDA_IPN "IPDA_IPN", IP.FEATURED "STATUS", AGGR.TERRITORY_SUMMARY "CONTROLLED_TERRITORY", MIN(AGGR_PERDS.START_DATE) "START_DATE", AGGR.AGREEMENT_SIGNED_DATE "SIGNED_DATE", C.PRIMARY_ADMIN_OWNER_ID "CLIENT_OWNER", C.SECONDARY_ADMIN_OWNER_ID "REP_OWNER", C.CLIENT_NAME "CLIENT_NAME", C.CLIENT_ID "CLIENT_ID", C.CLIENT_AGR_ID "CLIENT_AGR_ID", C.CONTR_ID "CONTR_ID", C.CONTR_AGR_ID "CONTR_AGR_ID", MIN(C.AP_START_DATE) "AP_START_DATE", C.SERVICE_LEVEL "CLIENT_SERVICE_LEVEL", ACQUIRERS.NAME "ACQUIRER", AGGR.IS_TERMINATED "TERMINATED", AGGR.TERMINATED_DATE "TERMINATED_DATE", MAX(AGGR_COL.END_DATE) "COLLECTION_END", AGGR.NR_AGREEMENT_TYPE "TYPE" 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 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.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, C.PRIMARY_ADMIN_OWNER_ID, 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) SELECT CLIENT_ID, CLIENT_AGR_ID, CONTR_ID, CONTR_AGR_ID, ACQUIRER, CONCAT(IFNULL(RTRIM(FIRST_NAME),' ')||' '|| IFNULL(RTRIM(SECOND_NAME),' ')||' '||IFNULL(RTRIM(THIRD_NAME), ' ')||' '||IFNULL(RTRIM(LAST_NAME), ' ')) "FULL_NAME", CONCAT(IFNULL(RTRIM(LAST_NAME),' ')||', '|| IFNULL(RTRIM(FIRST_NAME),' ')||' '||IFNULL(RTRIM(SECOND_NAME), ' ')||' '||IFNULL(RTRIM(THIRD_NAME), ' ')) "FULL_NAME_CSV", RTRIM(LAST_NAME) "LAST_NAME", RTRIM(FIRST_NAME) "FIRST_NAME", RTRIM(SECOND_NAME) "SECOND_NAME", RTRIM(THIRD_NAME) "THIRD_NAME", RTRIM(REGEXP_REPLACE( LISTAGG(ALIAS, ', ') WITHIN GROUP (ORDER BY ALIAS DESC), '([^,]*)(, \1)+($|,)', '\1\3'), ',') "ALIAS", RTRIM(REGEXP_REPLACE( LISTAGG(BANDS, ', ') WITHIN GROUP (ORDER BY BANDS DESC), '([^,]*)(, \1)+($|,)', '\1\3'), ',') "BANDS", GENDER, DOB, DOD, BIRTH_COUNTRY, RTRIM(REGEXP_REPLACE( LISTAGG(distinct CITIZENSHIP, ', ') WITHIN GROUP (ORDER BY CITIZENSHIP DESC), '([^,]*)(, \1)+($|,)', '\1\3'), ',') "CITIZENSHIP", RTRIM(REGEXP_REPLACE( LISTAGG(distinct RESIDENCY, ', ') WITHIN GROUP (ORDER BY RESIDENCY DESC), '([^,]*)(, \1)+($|,)', '\1\3'), ',') "RESIDENCY", IPDA_IPN, STATUS, CONTROLLED_TERRITORY, CLIENT_INFO.START_DATE "START_DATE", TERMINATED, TERMINATED_DATE, COLLECTION_END, SIGNED_DATE, listagg (distinct rep_owners.name, '|') rep_owners, listagg (distinct client_owners.name, '|') client_owners, CASE WHEN CLIENT_SERVICE_LEVEL = 1 THEN 'Bronze' WHEN CLIENT_SERVICE_LEVEL = 2 THEN 'Silver' WHEN CLIENT_SERVICE_LEVEL = 3 THEN 'Gold' WHEN CLIENT_SERVICE_LEVEL = 4 THEN 'Platinum' END AS SERVICE, CLIENT_NAME, CLIENT_INFO.TYPE "TYPE" FROM CLIENT_INFO left join awal_sax.sax.SAX_USERS rep_owners on rep_owners.ID = CLIENT_INFO.REP_OWNER left join awal_sax.sax.SAX_USERS client_owners on client_owners.ID = CLIENT_INFO.CLIENT_OWNER GROUP BY RTRIM(LAST_NAME), LAST_NAME, RTRIM(FIRST_NAME), RTRIM(SECOND_NAME), RTRIM(THIRD_NAME), FIRST_NAME, GENDER, DOB, DOD, BIRTH_COUNTRY, IPDA_IPN, STATUS, CONTROLLED_TERRITORY, CLIENT_INFO.START_DATE, SIGNED_DATE, CLIENT_OWNER, REP_OWNER, CLIENT_NAME, CLIENT_ID, CLIENT_AGR_ID, CONTR_ID, CONTR_AGR_ID, CASE WHEN CLIENT_SERVICE_LEVEL = 1 THEN 'Bronze' WHEN CLIENT_SERVICE_LEVEL = 2 THEN 'Silver' WHEN CLIENT_SERVICE_LEVEL = 3 THEN 'Gold' WHEN CLIENT_SERVICE_LEVEL = 4 THEN 'Platinum' END, ACQUIRER, TERMINATED, TERMINATED_DATE, COLLECTION_END, CLIENT_INFO.TYPE, REGEXP_REPLACE((RTRIM(FIRST_NAME)||' '|| RTRIM(SECOND_NAME)||' '|| RTRIM(THIRD_NAME)||' '|| RTRIM(LAST_NAME)),' *',' '), REGEXP_REPLACE((RTRIM(LAST_NAME)||', '|| RTRIM(FIRST_NAME)||' '|| RTRIM(SECOND_NAME)||' '|| RTRIM(THIRD_NAME)),' *',' ');; ```