# View: Deliver_Owner_SENA **View Name:** deliver_owner_sena **Table Source:** `Derived from: Prod.Nr_Contribution_Contributed_To_Nr_Sound_Recording, Prod.Nr_Ownership_Product_Has_Featured_Artist_Nr_Participant, Prod.Nr_Ownership_Product_Has_Primary_Artist_Nr_Participant, Prod.Nr_Ownership_Rules, Prod.Nr_Sound_Recording_Has_Featured_Artist_Nr_Participant + 3 more` **File Path:** `views/Deliver_Owner_SENA.view.lkml` ## Overview - **File Size:** 18456 bytes - **Lines of Code:** 377 - **Dimensions:** 43 - **Measures:** 0 - **Dimension Groups:** 0 - **Filters:** 0 ## Dimensions | Name | Type | |------|------| | `SENA_NUMBER` | string | | `ARTIST` | string | | `TITLE` | string | | `VERSION` | string | | `ISRC` | string | | `TRACKID` | string | | `CATEGORY` | string | | `LABEL` | string | | `GENRE` | string | | `COMPOSER` | string | | `DURATION` | number | | `LANGUAGE_CODE_PERFORMANCE` | string | | `ALBUM_TITLE` | string | | `NO_ALBUM_TRACKS` | number | | `IS_COMFON` | string | | `COMFON_URL` | string | | `RECORDING_YEAR` | number | | `RECORDING_COUNTRY` | string | | `FIRST_RELEASE_YEAR` | number | | `CTY_CODE_PRODUCER` | string | | `NAME_PRODUCER` | string | | `PERCENTAGE_RIGHTS` | number | | `DATE_START` | date | | `DATE_END` | date | | `JOB_STATUS` | string | | `DELIVERY_COMPLETED` | string | | `TYPE` | string | | `ORDER_ID` | string | | `ENTITY_NAME` | string | | `job_ID` | string | | `INSTANCE_COUNT` | string | | `job_order_id` | string | | `cmm_id` | string | | `CREATED_BY` | string | | `SUMMARY` | string | | `LAST_UPDATED` | date | | `ORDER_STATUS` | string | | `CREATED_AT` | date | | `TERRITORY` | string | | `MESSAGE` | string | | `SOUND_RECORDING_ID` | string | | `job_contents_job_ib` | string | | `TRACK_ID` | string | ## SQL Comments - Jobs Table - rights-- - to be added to snowflake by tech -- NR_TRACK_HAS_primary_ARTIST_NR_PARTICIPANT & NR_TRACK_HAS_featured_ARTIST_NR_PARTICIPANT - product - subaccount id ## Derived Table ```sql sql: -- Jobs Table select '' SENA_NUMBER, rights.sr_display_artist_name ARTIST, rights.sr_recording_title TITLE, rights.sr_version VERSION, rights.sr_isrc ISRC, '' TRACKID, 'M' CATEGORY, rights.rights_account_vendor_name LABEL, 'P' GENRE, rights.sr_display_composer COMPOSER, rights.sr_duration DURATION, rights.sr_audio_language LANGUAGE_CODE_PERFORMANCE, rights.product_name ALBUM_TITLE, rights.TRACK_NUMBER NO_ALBUM_TRACKS, 'N' IS_COMFON, '' COMFON_URL, rights.sr_first_recording_year RECORDING_YEAR, rights.sr_country_of_recording RECORDING_COUNTRY, rights.sr_first_release_year FIRST_RELEASE_YEAR, rights.sr_country_of_label CTY_CODE_PRODUCER, rights.product_p_line_label NAME_PRODUCER, rights.rights_percentage PERCENTAGE_RIGHTS, rights.rights_start_date DATE_START, rights.rights_end_date DATE_END, j.STATUS job_status, j.DELIVERY_COMPLETED, j.TYPE, j.ORDER_ID job_order_id, j.ENTITY_NAME, j.ID job_ID, j.INSTANCE_COUNT, o.ID order_id, o.CMM_ID, o.CREATED_BY, o.SUMMARY, o.LAST_UPDATED, o.STATUS order_status, o.CREATED_AT, o.TERRITORY, o.MESSAGE, jc.SOUND_RECORDING_ID, jc.JOB_ID job_contents_job_ib, jc.TRACK_ID from (select -- rights-- pr_tr_data.Product_Primary_Artist,pr_tr_data.Product_featured_artist, iff(pr_tr_data.product_display_artist_name is null and pr_tr_data.Product_featured_artist is null, pr_tr_data.Product_Primary_Artist||' ft. '||pr_tr_data.Product_featured_artist, pr_tr_data.Product_Primary_Artist) product_display_artist_name, pr_tr_data.product_name,pr_tr_data.product_imprint,pr_tr_data.product_p_line_label,pr_tr_data.product_p_line_year,pr_tr_data.product_content_type,pr_tr_data.product_format,pr_tr_data.product_sub_type, pr_tr_data.product_version, pr_tr_data.product_upc, pr_tr_data.product_code, pr_tr_data.product_sales_start_date, pr_tr_data.track_id, pr_tr_data.track_submitted_by_vendor_id, vtr.name track_submitted_by_vendor_name, vtr.owner track_submitted_by_vendor_owner, pr_tr_data.TRACK_NUMBER, pr_tr_data.DISC_NUMBER, pr_tr_data.track_recording_type, pr_tr_data.track_isrc, pr_tr_data.track_title, pr_tr_data.track_version, pr_tr_data.track_notes, pr_tr_data.track_associated_audio_ISRC, REGEXP_REPLACE(REGEXP_REPLACE(TO_CHAR(pr_tr_data.track_external_id), '[""\\[|\\]]', ''),',',', ') track_external_id, pr_tr_data.track_SR_reference, pr_tr_data.track_created_at, pr_tr_data.track_created_by, pr_tr_data.track_last_modified_at, pr_tr_data.track_last_modified_by, pr_tr_data.track_deteled_at, pr_tr_data.track_is_deleted, -- to be added to snowflake by tech -- NR_TRACK_HAS_primary_ARTIST_NR_PARTICIPANT & NR_TRACK_HAS_featured_ARTIST_NR_PARTICIPANT pr_tr_data.sr_id, pr_tr_data.sr_audio_language, pr_tr_data.sr_duration, pr_tr_data.sr_explicit, pr_tr_data.sr_first_recording_year, pr_tr_data.sr_first_release_year, pr_tr_data.sr_genre, pr_tr_data.sr_isrc, pr_tr_data.sr_recording_title, pr_tr_data.sr_version, iff(pr_tr_data.sr_display_artist_name is null and pr_tr_data.sr_featured_artist is null, pr_tr_data.sr_primary_artist||' ft. '||pr_tr_data.sr_featured_artist, pr_tr_data.sr_primary_artist) sr_display_artist_name, pr_tr_data.sr_display_composer, pr_tr_data.sr_created_at, pr_tr_data.sr_created_by, pr_tr_data.sr_created_by_identity_id, pr_tr_data.sr_last_modified_at, pr_tr_data.sr_last_modified_by, pr_tr_data.sr_last_modified_by_identiy_id, pr_tr_data.sr_deleted_at, pr_tr_data.sr_is_deleted, pr_tr_data.sr_primary_artist, pr_tr_data.sr_featured_artist, pr_tr_data.sr_country_of_label, pr_tr_data.sr_country_of_recording, pr_tr_data.sr_cont_legal_name,pr_tr_data.sr_cont_type,pr_tr_data.sr_cont_roles, iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, nror.nr_sound_recording_id, '') rights_sr_id, iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, nror.start_date,null) rights_start_date, iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, nror.end_date, null) rights_end_date, iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, nror.percentage, null) rights_percentage, iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, nror.account_id, null) rights_account_id, iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, vsr.name, null) rights_account_vendor_name, iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, vsr.owner, null) rights_account_vendor_owner, iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, (select listagg(distinct(nror.territory),', ') from FACTS.PROD.NR_OWNERSHIP_RULES nror where pr_tr_data.sr_id = nror.NR_SOUND_RECORDING_ID and nror.ownership_rule_type = 'COLLECT'), '') rights_COLLECT, iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, (select listagg(distinct(nror.territory),', ') from FACTS.PROD.NR_OWNERSHIP_RULES nror where pr_tr_data.sr_id = nror.NR_SOUND_RECORDING_ID and nror.ownership_rule_type = 'CARVEOUT'), '') rights_CARVEOUT from (select --product (select listagg(p.name,' | ') from FACTS.PROD.NR_OWNERSHIP_PRODUCT_HAS_PRIMARY_ARTIST_NR_PARTICIPANT poa left join FACTS.PROD.ownership_nr_participant p on poa.NR_PARTICIPANT_id = p.id where poa.NR_product_ID = nrpr.id) Product_Primary_Artist, (select listagg (p.name,' | ') from FACTS.PROD.NR_OWNERSHIP_PRODUCT_HAS_featured_ARTIST_NR_PARTICIPANT pof left join FACTS.PROD.ownership_nr_participant p on pof.NR_PARTICIPANT_id = p.id where pof.NR_product_ID = nrpr.id) Product_featured_artist, nrpr.display_artist_name product_display_artist_name, nrpr.name product_name, nrpr.imprint product_imprint, nrpr.P_LINE_LABEL product_p_line_label, nrpr.P_LINE_YEAR product_p_line_year, nrpr.content_type product_content_type, nrpr.format product_format, nrpr.sub_type product_sub_type, nrpr.version product_version, nrpr.UPC product_upc, nrpr.code product_code, nrpr.sales_start_date product_sales_start_date, track_data.track_id, track_data.track_submitted_by_vendor_id, -- subaccount id track_data.TRACK_NUMBER, track_data.DISC_NUMBER, track_data.track_recording_type, track_data.track_isrc, track_data.track_title, track_data.track_version, track_data.track_notes, track_data.track_associated_audio_ISRC, track_data.track_external_id, track_data.track_SR_reference, track_data.track_created_at, track_data.track_created_by, track_data.track_last_modified_at, track_data.track_last_modified_by, track_data.track_deteled_at, track_data.track_is_deleted, -- to be added to snowflake by tech -- NR_TRACK_HAS_primary_ARTIST_NR_PARTICIPANT & NR_TRACK_HAS_featured_ARTIST_NR_PARTICIPANT track_data.sr_id, track_data.sr_audio_language, track_data.sr_duration, track_data.sr_explicit, track_data.sr_first_recording_year, track_data.sr_first_release_year, track_data.sr_genre, track_data.sr_isrc, track_data.sr_recording_title, track_data.sr_version, track_data.sr_display_artist_name, track_data.sr_display_composer, track_data.sr_created_at, track_data.sr_created_by, track_data.sr_created_by_identity_id, track_data.sr_last_modified_at, track_data.sr_last_modified_by, track_data.sr_last_modified_by_identiy_id, track_data.sr_deleted_at, track_data.sr_is_deleted, track_data.sr_primary_artist, track_data.sr_featured_artist, track_data.sr_country_of_label, track_data.sr_country_of_recording, track_data.sr_cont_legal_name,track_data.sr_cont_type,track_data.sr_cont_roles from (select -- track data nrot.ID track_id, nrot.SUBMITTED_BY_VENDOR_ID track_submitted_by_vendor_id, -- subaccount id nrot.TRACK_NUMBER, nrot.DISC_NUMBER, nrot.RECORDING_TYPE track_recording_type, nrot.ISRC track_isrc, nrot.TITLE track_title, nrot.VERSION track_version, nrot.NOTES track_notes, nrot.ASSOCIATED_AUDIO_ISRC track_associated_audio_ISRC, nrot.EXTERNAL_ID track_external_id, nrot.REFERENCE track_SR_reference, nrot.CREATED_AT track_created_at, nrot.CREATED_BY track_created_by, nrot.LAST_MODIFIED_AT track_last_modified_at, nrot.LAST_MODIFIED_BY track_last_modified_by, nrot.DELETED_AT track_deteled_at, nrot.IS_DELETED track_is_deleted, -- to be added to snowflake by tech -- NR_TRACK_HAS_primary_ARTIST_NR_PARTICIPANT & NR_TRACK_HAS_featured_ARTIST_NR_PARTICIPANT recordings.ID sr_id, recordings.AUDIO_LANGUAGE sr_audio_language, recordings.DURATION sr_duration, recordings.EXPLICIT sr_explicit, recordings.FIRST_RECORDING_YEAR sr_first_recording_year, recordings.FIRST_RELEASE_YEAR sr_first_release_year, recordings.GENRE sr_genre, recordings.ISRC sr_isrc, recordings.RECORDING_TITLE sr_recording_title, recordings.VERSION sr_version, recordings.DISPLAY_ARTIST_NAME sr_display_artist_name, recordings.DISPLAY_COMPOSER sr_display_composer, recordings.CREATED_AT sr_created_at, recordings.CREATED_BY sr_created_by, recordings.CREATED_BY_IDENTITY_ID sr_created_by_identity_id, recordings.LAST_MODIFIED_AT sr_last_modified_at, recordings.LAST_MODIFIED_BY sr_last_modified_by, recordings.LAST_MODIFIED_BY_IDENTITY_ID sr_last_modified_by_identiy_id, recordings.DELETED_AT sr_deleted_at, recordings.IS_DELETED sr_is_deleted, recordings.primary_artist sr_primary_artist, recordings.featured_artist sr_featured_artist, recordings.Country_of_Label sr_country_of_label, recordings.Country_of_Recording sr_country_of_recording, recordings.cont_legal_name sr_cont_legal_name,recordings.cont_type sr_cont_type,recordings.cont_roles sr_cont_roles from -- soundrecording data (select osr.ID, osr.AUDIO_LANGUAGE, osr.DURATION, osr.EXPLICIT, osr.FIRST_RECORDING_YEAR, osr.FIRST_RELEASE_YEAR, osr.GENRE, osr.ISRC, osr.RECORDING_TITLE, osr.VERSION, osr.DISPLAY_ARTIST_NAME, osr.DISPLAY_COMPOSER, osr.CREATED_AT, osr.CREATED_BY, osr.CREATED_BY_IDENTITY_ID, osr.LAST_MODIFIED_AT, osr.LAST_MODIFIED_BY, osr.LAST_MODIFIED_BY_IDENTITY_ID, osr.DELETED_AT, osr.IS_DELETED, (select listagg(p.name,' | ') from FACTS.PROD.NR_SOUND_RECORDING_HAS_primary_ARTIST_NR_PARTICIPANT pa left join FACTS.PROD.ownership_nr_participant p on pa.NR_PARTICIPANT_id = p.id where pa.NR_SOUND_RECORDING_ID = osr.id) Primary_Artist, (select listagg (p.name,' | ') from FACTS.PROD.NR_SOUND_RECORDING_HAS_featured_ARTIST_NR_PARTICIPANT fa left join FACTS.PROD.ownership_nr_participant p on fa.NR_PARTICIPANT_id = p.id where fa.NR_SOUND_RECORDING_ID = osr.id) featured_artist, listagg(distinct(cyf.abbrivation),', ') within group (order by 1) Country_of_Label, listagg(distinct(cy.abbrivation),', ') within group (order by 1) Country_of_Recording, grouped_conts.nr_sound_recording_id, grouped_conts.cont_legal_name, grouped_conts.cont_type, grouped_conts.cont_roles from -- contribution lists (select contributions.nr_sound_recording_id, listagg(contributions.contributor_name,' | ') cont_legal_name, listagg(contributions.contribution_type,' | ') cont_type, listagg(contributions.instruments, ' | ' ) cont_roles from -- contributor per row -- (select csr.nr_sound_recording_id, oc.name contributor_name, ct.contribution_type , listagg(distinct(inst.value),', ') within group (order by 1) instruments from FACTS.PROD.NR_CONTRIBUTION_CONTRIBUTED_TO_NR_SOUND_RECORDING csr join FACTS.PROD.OWNERSHIP_NR_CONTRIBUTION c on csr.nr_contribution_id = c.id join FACTS.PROD.NR_CONTRIBUTION_TYPE ct on c.id = ct.nr_contribution_id join FACTS.PROD.OWNERSHIP_NR_CONTRIBUTOR oc on ct.nr_contributor_id = oc.id join table(flatten (input => c.instruments, outer => TRUE)) inst group by 1,2,3) contributions group by 1) grouped_conts right join FACTS.PROD.OWNERSHIP_NR_SOUND_RECORDING osr on grouped_conts.nr_sound_recording_id = osr.id join table(flatten (input => osr.countries_of_funding, outer => TRUE)) cf join orchard_app_reporting_v2.art_relations_prod_art_relations.country cyf on cyf.country_code=cf.value join table(flatten (input => osr.countries_of_recording, outer => TRUE)) cr join orchard_app_reporting_v2.art_relations_prod_art_relations.country cy on cy.country_code=cr.value group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,25,26,27,28--,29,30,31,32,33,34,35 ) recordings join FACTS.PROD.NR_TRACK_IS_PLACEMENT_OF_NR_SOUND_RECORDING nrotsr on recordings.id = nrotsr.nr_sound_recording_id join FACTS.PROD.NR_OWNERSHIP_TRACK nrot on nrotsr.nr_track_id = nrot.id group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45) track_data left join FACTS.PROD.NR_PRODUCT_INCLUDES_NR_TRACK nrprtr on track_data.track_id = nrprtr.nr_track_id left join FACTS.PROD.NR_OWNERSHIP_PRODUCT nrpr on nrprtr.nr_product_id = nrpr.id --where track_data.track_id = '07dc9151-9479-4d60-830e-0659f2620835' group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59) pr_tr_data join FACTS.PROD.NR_OWNERSHIP_RULES nror on pr_tr_data.sr_id = nror.nr_sound_recording_id JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor vtr ON pr_tr_data.track_submitted_by_vendor_id = vtr.vendor_id JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor vsr ON nror.account_id = vsr.vendor_id where nror.is_deleted = 'FALSE' and nror.percentage is not null --and pr_tr_data.sr_id in ('935adf10-d20d-4029-bd74-4d4069102b9e','b9c678ba-7c3b-451f-9b17-2ed99f334f4e','e9f59419-57be-415b-bb5c-074fe0dacb1c','281e66c5-3ee2-42aa-a889-d49383e2f883','7d293141-10b4-4935-9cf7-6946e5925b3e') --and pr_tr_data.track_isrc in ('DEVP22300004', group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70) rights join ORCHARD_APP_REPORTING_V2.PROD_NR_OWNERSHIP_DELIVERY_NEIGHBOURING_RIGHTS_OWNERSHIP_DELIVERY.JOB_CONTENTS jc on rights.track_id = jc.track_id left join ORCHARD_APP_REPORTING_V2.PROD_NR_OWNERSHIP_DELIVERY_NEIGHBOURING_RIGHTS_OWNERSHIP_DELIVERY.JOBS j on jc.job_id = j.id left join ORCHARD_APP_REPORTING_V2.PROD_NR_OWNERSHIP_DELIVERY_NEIGHBOURING_RIGHTS_OWNERSHIP_DELIVERY.ORDERS o on j.order_id = o.id LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER AS cmm ON cmm.CUSTOMER_MASTER_MASTER_ID=o.cmm_id --where j.ORDER_ID = 194 group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,35,36,37,38,39,40,41,42,43;; ```