# View: OA_Ingested_Content_With_Collections

**View Name:** oa_ingested_content_with_collections
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Artist_Info, Art_Relations_Prod_Art_Relations.Country, Art_Relations_Prod_Art_Relations.Releases, Art_Relations_Prod_Art_Relations.Track, Art_Relations_Prod_Art_Relations.Track_Artist + 3 more`
**File Path:** `views/OA_Ingested_Content_With_Collections.view.lkml`

## Overview

- **File Size:** 14705 bytes
- **Lines of Code:** 545
- **Dimensions:** 55
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `vendor_id` | string |
| `Product_Primary_Artists` | string |
| `Product_Featured_Artists` | string |
| `Product_Display_Artist` | string |
| `Product_Name` | string |
| `Imprint` | string |
| `P_Line_Year` | number |
| `P_Label` | string |
| `content_type` | string |
| `product_type` | string |
| `product_format` | string |
| `product_subtype` | string |
| `Product_version` | string |
| `UPC` | string |
| `Product_Code` | string |
| `sales_start_date` | date |
| `Disc_Number` | number |
| `Track_Number` | number |
| `Track_Primary_Artist` | string |
| `Track_Featuring_Artist` | string |
| `Display_Artist_Name` | string |
| `Display_Composer` | string |
| `Song_Name` | string |
| `song_version` | string |
| `ISRC` | string |
| `Associated_ISRC` | string |
| `Duration` | number |
| `Explicit` | string |
| `genre` | string |
| `audio_language` | string |
| `Country_of_Label` | string |
| `Country_of_Recording` | string |
| `Year_of_Recording` | number |
| `Release_Year` | number |
| `Start_Date` | date |
| `end_date` | date |
| `percentage` | string |
| `Collection_Ownership_Country` | string |
| `Rights_Notes` | string |
| `contributor_name` | string |
| `contributor_type` | string |
| `contribtuor_role` | string |
| `track_type` | string |
| `sale_start_date` | date |
| `ingestion_completed` | date |
| `owner` | string |
| `account_name` | string |
| `release_status` | string |
| `track_id` | string |
| `subaccount_id` | string |
| `royalty_collection_territory` | string |
| `subaccount_royalty_collection_territory` | string |
| `content_account_id` | string |
| `content_start_date` | date |
| `content_end_dated` | date |

## SQL Comments

- Performer --
- Digital/Physical
- A single selection for example one of the following: CD, Vinyl, Cassette, DVD, Blu-Ray, Official Music Video, Lyric Video, Pseudo Video, Behind the Scenes, Promo, Live Performance, VHS, Other
- where r.sale_start_date between '2024-10-01' and '2024-12-01'

## Derived Table

```sql
sql:
select
content.vendor_id,
content.Product_Primary_Artists,
content.Product_Featured_Artists,
  iff(content.Product_Featured_Artists is null,content.Product_Primary_Artists||' ft. '||content.Product_Featured_Artists,content.Product_Primary_Artists) Product_Display_Artist,
content.Product_Name,
'' Imprint,
content.P_Line_Year,
content.P_Label,
content.content_type,
content.product_type,
content.product_format,
content.product_subtype,
content.version Product_version,
content.UPC,
content.Product_Code,
content.sales_start_date,
content.Disc_Number,
content.Track_Number,
content.Track_Primary_Artist,
content.Track_Featuring_Artist,
  iff(content.Track_Featuring_Artist is not null , content.Track_Primary_Artist||' ft. '||content.Track_Featuring_Artist, content.Track_Primary_Artist) Display_Artist_Name,
'' Display_Composer,
content.Song_Name,
content.song_version,
content.ISRC,
'' Associated_ISRC,
content.Duration,
content.Explicit,
content.genre,
content.audio_language,
content.Country_of_Label,
content.Country_of_Recording,
content.Year_of_Recording,
content.Release_Year,
content.Start_Date,
content.end_date,
content.percentage,
'' Collection_Ownership_Country,
'' Rights_Notes,
content.contributor_name,
content.contributor_type,
content.contribtuor_role,
content.track_type,
content.sale_start_date,
content.ingestion_completed,
content.owner,
content.account_name,
content.release_status,
content.track_id,
content.subaccount_id,
vc.royalty_collection_territory,
srct.subaccount_royalty_collection_territory,
nror.account_id content_account_id,
nror.start_date content_start_date,
nror.end_date content_end_date


from (
select
i.owner,
i.account_name,
i.vendor_id,
i.Product_Primary_Artists,
i.Product_Featured_Artists,
i.Product_Name,
i.P_Line_Year,
i.P_Label,
i.UPC,
i.Product_Code,
i.product_format,
i.product_type,
i.sales_start_date,
i.Disc_Number,
i.Track_Number,
i.Track_Primary_Artist,
i.Track_Featuring_Artist,
i.Song_Name,
i.song_version,
i.ISRC,
i.Duration,
i.Explicit,
i.Country_of_Label,
i.Country_of_Recording,
i.Year_of_Recording,
i.Release_Year,
i.Start_Date,
i.end_date,
i.percentage,
listagg(i.contributor_name,' | ') contributor_name,
listagg(i.contributor_type,' | ') contributor_type,
listagg(i.contributor_role,' | ') contribtuor_role,
i.version,
i.track_type,
i.sale_start_date,
i.ingestion_completed,
i.content_type,
i.product_subtype,
i.genre,
i.audio_language,
i.release_status,
i.track_id,
i.subaccount_id

from (

select
v.owner,
v.name as account_name,
v.vendor_id as Vendor_ID,
ai.name as Product_Primary_Artists,
'' as Product_Featured_Artists,
r.release_name as Product_Name,
LEFT(tr.p_line,4) as P_Line_Year,
RIGHT(rtrim(tr.p_line),
len (tr.p_line)-5) as P_Label,
case when try_to_number(r.MANUFACTURER_UPC) is null then r.UPC else r.MANUFACTURER_UPC end as UPC,
r.product_code as Product_Code,
r.format as product_Format,
to_varchar(r.release_date,'dd-mon-yy') as Sales_Start_Date ,
tr.cd as Disc_Number,
tr.track_id as Track_Number,
(select listagg (ta.name,', ') from orchard_app_reporting_v2.art_relations_prod_art_relations.track_artist ta where tr.id = ta.track_id and ta.type = 'performer')  as Track_Primary_Artist,
(select listagg (ta.name,', ') from orchard_app_reporting_v2.art_relations_prod_art_relations.track_artist ta where tr.id = ta.track_id and ta.type = 'featuring') as Track_Featuring_Artist,
tr.track_name as Song_Name,
case when tr.version is null then '' else tr.version end as song_Version,
tr.ISRC as ISRC,
CONCAT_WS(':',tr.length_minute,tr.length_seconds) as Duration,
tr.explicit_lyrics as Explicit,
c3.country_code as Country_of_Label,
c2.country_code as Country_of_Recording,
case when tr.recording_year is null then (to_varchar(r.release_date,'yyyy')) else tr.recording_year end as Year_of_Recording,
to_varchar(iff(r.original_release_date is null, r.release_date,r.original_release_date), 'yyyy') as Release_Year,
to_varchar(r.ingestion_completed, 'dd-mon-yy') as Start_Date,
'' as end_date,
'100' as percentage,
'' as collection_territory,
-- Performer --
p.birth_name contributor_name,
replace(replace(replace(p.performer_type,'non-featured','Session Musician'),'featured','Featuring Artist'),'primary','Main Artist') contributor_type,
trim(left(performer_role,charindex('-',performer_role)-1)) contributor_role,
r.version,
tr.track_type,
r.sale_start_date as sale_start_date,
r.ingestion_completed,
df.context_type as content_type, --Digital/Physical
replace(replace(replace(replace(replace(pt.product_type,'Music','Audio'),'TV Show', 'Video'),'Movie', 'Video'), 'Video Etc','Video'), 'Web Videos','Video') as product_type,
pst.subtype as product_subtype, -- A single selection for example one of the following: CD, Vinyl, Cassette, DVD, Blu-Ray, Official Music Video, Lyric Video, Pseudo Video, Behind the Scenes, Promo, Live Performance, VHS, Other
g.genre as genre,
l.language as audio_language,
r.release_status,
tr.track_id,
r.subaccount_id
FROM orchard_app_reporting_v2.art_relations_prod_art_relations.track tr
inner join orchard_app_reporting_v2.art_relations_prod_art_relations.track_artist ta on tr.id = ta.track_id
INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases r ON tr.release_id = r.release_id
INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai ON r.artist_id = ai.artist_id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c2 ON tr.recording_country = c2.id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.track_producer_nationality pn ON tr.id = pn.track_id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c3 ON pn.nationality_country_id = c3.id
INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v ON ai.vendor_id = v.vendor_id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country AS c1 ON v.country = c1.id
LEFT JOIN orchard_app_reporting_v2.prod_ows_track_ows_track.performer as p ON tr.id = p.unique_track_id
LEFT JOIN orchard_app_reporting_v2.prod_ows_track_ows_track.performer_role AS pr on p.performer_role_id = pr.performer_role_id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.RELEASE_ARTIST RA ON R.ARTIST_ID = RA.RELEASE_ARTIST_ID
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.distribution_format df on r.distribution_format_id = df.distribution_format_ID
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.product_type pt on r.product_type_id = pt.id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.product_subtype pst on r.product_subtype_id = pst.id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.genre g on r.genre_id = g.genre_id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.language l on r.language_id = l.language_code
--where r.sale_start_date between '2024-10-01' and '2024-12-01'
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

) i

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,33,34,35,36,37,38,39,40,41,42,43) content

join royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v ON content.vendor_id = v.vendor_id
left join royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor_contract vc on vc.vendor_id = v.vendor_id
LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT s ON content.subaccount_id = s.subaccount_id
left join ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT_ROYALTY_COLLECTION src on s.subaccount_id = src.subaccount_id
LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT_ROYALTY_COLLECTION_TERRITORIES srct on src.subaccount_royalty_collection_id = srct.subaccount_royalty_collection_territories_id
left join FACTS.QA.NR_OWNERSHIP_TRACK nrot on nrot.isrc = content.isrc
left join FACTS.QA.NR_TRACK_IS_PLACEMENT_OF_NR_SOUND_RECORDING nrtp on nrot.id = nrtp.nr_track_id
left join FACTS.QA.NR_OWNERSHIP_RULES nror on nrtp.nr_sound_recording_id = nror.nr_sound_recording_id and nror.is_deleted not like true


where v.owner IN ('AWL-ORC-UK','AWL-ORC-US','1091 FT','AAvaness','AB-DEF-UK','AB-REV-UK',
'AB-UK',
'AGarcia',
'Alina',
'Altafonet-ESP-DEF',
'Altafonet-US-DEF',
'amdl',
'amped',
'Ashley',
'avelasquez',
'AWALrev-DE',
'AWALrev-NY',
'AWALrev-UK',
'BCB',
'BFM-DEF',
'BFM-Rever',
'Big Mac',
'bobby',
'CALZ',
'carmel',
'CLC',
'Columbia US',
'CRML2014',
'CSoto',
'CSussman',
'Digger',
'DINAHP',
'DJ GREEK',
'DKASTLE',
'DMGI',
'DMGI_Chan',
'DMGI_GL',
'DMGI_GP',
'don',
'DRA',
'DWASH',
'EastWest',
'ecco',
'emd',
'EMI France',
'epsa',
'Federico',
'Fedi Driss',
'Finetunes',
'FKLG',
'Forte',
'FT-NY',
'GCam',
'HRS-ODD',
'INSTANT',
'Integral',
'IODA-DEF',
'IODA-REVER',
'IRIS-DEF',
'IRIS-REV',
'Jacob New',
'janet',
'Jay',
'JCM',
'JeffS',
'JJK',
'JK New',
'JNegron',
'JT',
'Kai',
'kdigital',
'KKnake',
'Lia',
'MA-India',
'MarcOff',
'MA-US',
'MC',
'Metin ExMu',
'Metin R',
'MHF',
'MS-DEF',
'MS-NY',
'Neil',
'NewSound',
'NextMusic',
'odd',
'OLEKSANDR',
'Olivier',
'oop',
'orch_japan',
'orchard',
'Orch-GmbH',
'Orlando',
'PH-NY',
'Phonofile',
'PLT',
'Rad',
'Rafik',
'RED',
'RED/SME JV',
'RED-DEF',
'REDESSENT',
'REDMusic',
'RMasio',
'RUSSIA',
'SC',
'Scott',
'simon',
'SLAIT-NEWD',
'SMEAU',
'SMEBE',
'SMECAN',
'SMEDE',
'SMEDEN',
'SMEFR',
'SMEIN',
'SMEINTL',
'SMELAT',
'SMELAT-P',
'SMEMX',
'SMENO',
'SMESP',
'SMEUK',
'SMEUS',
'SOH',
'Sony Latin Colombia',
'Susana',
'TCFI',
'TULLY',
'VR')
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,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55;;
```