select 
	c.creation                                          as dri_objidentifier,
	r.title                                             as dc_title,
	array_agg(DISTINCT lpad('0'||aut.rank,5,'0')||'§§§'||ltrim(replace(per.surname||', '||per.name,'UNKNOWN, ',''), ', ')) as dc_creator,
	r.description                                       as dc_description,
	r.publisher                                         as dc_publisher,
	ARRAY(select trim(s) from unnest(string_to_array(r.keywords, ';')) as s) as dc_subject,
	r.language                                          as dc_language,
	array_agg(DISTINCT ws.file_url)                     as dc_identifier,
	r.publication_date                                  as "dc_dateAccepted",
	extract(year from r.publication_date)::text         as oaf_publicationyear,
	r.embargo_end_date                                  as oaf_embargoenddate,
	extract(year from r.embargo_end_date)::text         as oaf_embargoendyear,
	r.access_mode                                       as oaf_accessrights,
	r.haskind                                           as oaf_haskind,
	r.hastype                                           as "dr_CobjCategory",
	r.storagedate										as oaf_storagedate,
	r.createdfrom                                       as temp_createdfrom, 
	c.creator                                           as action_user,
	c.date_of_creation                                  as action_date_of_creation,
	c.last_update_date                                  as action_update_date,

	array_agg(DISTINCT o.country_of_origin)	            as oaf_countryaffiliation,
	array_agg(DISTINCT o.legal_short_name)              as oaf_affiliationname,
	array_agg(DISTINCT o.organizationid)                as oaf_affiliationid,
	array_agg(DISTINCT p.projectid)						as temp_projectid,

	array_agg(DISTINCT d.datasourceid)                  as temp_hostedbyid,
	array_agg(DISTINCT d.official_name)                 as temp_hostedbyname,

	array_agg(DISTINCT df.datasourceid)                 as temp_collectedfromid,
	array_agg(DISTINCT df.official_name)                as temp_collectedfromname

from
	creations c
	left outer join results r on (r.resultid = c.creation) 
	left outer join results_projects rp on (r.resultid = rp.result)
	left outer join projects p on (p.projectid = rp.project)
	left outer join authorships aut on (r.resultid = aut.result)
	left outer join persons per on (per.personid = aut.author)
	left outer join organizations o on (o.organizationid = aut.affiliation)
	left outer join instances i on (r.resultid = i.resource)
	left outer join webresources_instances wsi on (wsi.instanceid = i.instanceid)
	left outer join webresources ws on (ws.webresourceid = wsi.webresourceid)
	left outer join datasources d on (i.hostedby = d.datasourceid)
	left outer join datasources df on (i.collectedFrom = df.datasourceid)
where
	r.pendingforclaim = false
	
group by
	c.creation, c.creator,
	r.resultid, r.title, r.description, r.publisher, r.keywords, r.language,
	r.publication_date, r.embargo_end_date, r.access_mode, r.haskind, r.hastype, 
	r.createdfrom , r.storagedate, c.creator, c.last_update_date, c.date_of_creation
