D-Net

CREATE OR REPLACE VIEW orgs AS (

	SELECT

		o.id                                                                    AS organizationid,

		o.legalshortname                                                        AS legalshortname,

		o.legalname                                                             AS legalname,

		o.websiteurl                                                            AS websiteurl,

		o.logourl                                                               AS logourl,

		o.ec_legalbody                                                          AS eclegalbody,

		o.ec_legalperson                                                        AS eclegalperson,

		o.ec_nonprofit                                                          AS ecnonprofit,

		o.ec_researchorganization                                               AS ecresearchorganization,

		o.ec_highereducation                                                    AS echighereducation,

		o.ec_internationalorganizationeurinterests                              AS ecinternationalorganizationeurinterests,

		o.ec_internationalorganization                                          AS ecinternationalorganization,

		o.ec_enterprise                                                         AS ecenterprise,

		o.ec_smevalidated                                                       AS ecsmevalidated,

		o.ec_nutscode                                                           AS ecnutscode,

		o.dateofcollection                                                      AS dateofcollection,

		o.inferred                                                              AS inferred,

		o.deletedbyinference                                                    AS deletedbyinference,

		o.trust                                                                 AS trust,

		o.inferenceprovenance                                                   AS inferenceprovenance,

		dc.id                                                                   AS collectedfromid,

		dc.officialname                                                         AS collectedfromname,

		cc.code || '@@@' || cc.name || '@@@' || cs.code || '@@@' || cs.name     AS country,

		pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name AS provenanceaction,

		array_agg(DISTINCT i.pid || '###' || i.issuertypeclass)                 AS pid

	FROM organizations o

		LEFT OUTER JOIN class cc ON (cc.code = o.countryclass)

		LEFT OUTER JOIN scheme cs ON (cs.code = o.countryscheme)

		LEFT OUTER JOIN class pac ON (pac.code = o.provenanceactionclass)

		LEFT OUTER JOIN scheme pas ON (pas.code = o.provenanceactionscheme)

		LEFT OUTER JOIN organizationpids op ON (op.organization = o.id)

		LEFT OUTER JOIN identities i ON (i.pid = op.pid)

		LEFT OUTER JOIN datasources dc ON (dc.id = o.collectedfrom)

	GROUP BY

		o.id,

		o.legalshortname,

		o.legalname,

		o.websiteurl,

		o.logourl,

		o.ec_legalbody,

		o.ec_legalperson,

		o.ec_nonprofit,

		o.ec_researchorganization,

		o.ec_highereducation,

		o.ec_internationalorganizationeurinterests,

		o.ec_internationalorganization,

		o.ec_enterprise,

		o.ec_smevalidated,

		o.ec_nutscode,

		o.dateofcollection,

		o.inferred,

		o.deletedbyinference,

		o.trust,

		o.inferenceprovenance,

		dc.id,

		dc.officialname,

		cc.code, cc.name, cs.code, cs.name,

		pac.code, pac.name, pas.code, pas.name

);

SELECT * FROM orgs

UNION

		o.id                                                      AS organizationid,

		o.legalshortname                                          AS legalshortname,

		o.legalname                                               AS legalname,

		o.websiteurl                                              AS websiteurl,

		o.logourl                                                 AS logourl,

		null                                                      AS eclegalbody,

		null                                                      AS eclegalperson,

		null                                                      AS ecnonprofit,

		null                                                      AS ecresearchorganization,

		null                                                      AS echighereducation,

		null                                                      AS ecinternationalorganizationeurinterests,

		null                                                      AS ecinternationalorganization,

		null                                                      AS ecenterprise,

		null                                                      AS ecsmevalidated,

		null                                                      AS ecnutscode,

		o.dateofcollection                                        AS dateofcollection,

		false                                                     AS inferred,

		false                                                     AS deletedbyinference,

		0.9                                                       AS trust,

		''                                                        AS inferenceprovenance,

		d.id                                                      AS collectedfromid,

		d.officialname                                            AS collectedfromname,

		o.country || '@@@' || o.country || '@@@dnet:countries@@@dnet:countries' AS country,

		'sysimport:crosswalk:entityregistry@@@sysimport:crosswalk:entityregistry@@@dnet:provenance_actions@@@dnet:provenance_actions' AS provenanceaction,

		ARRAY[]::text[]                                              AS pid

FROM dsm_organizations o

	LEFT OUTER JOIN dsm_datasources d ON (d.id = o.collectedfrom);