-- Name: vw_ow_onttrekking_shape; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_ow_onttrekking_shape AS
 SELECT DISTINCT ON (hydro_object.inspire_id_local_id) hydro_object.inspire_id_local_id AS "IDENT",
    hdo_names.name AS "NAAM",
    abstraction_point.geometry,
    sw_abstraction_point.waterabstraction_from AS "ONTTRPNT",
    sw_abstraction_point.swat_id AS "ONTRSRTCOD",
    sw_abstraction_type.description AS "ONTTRSRT"
   FROM ((((((hydro_object
     LEFT JOIN hdo_names ON ((hdo_names.hdo_id = hydro_object.hdo_id)))
     JOIN abstraction_point ON ((hydro_object.hdo_id = abstraction_point.asp_id)))
     JOIN sw_abstraction_point ON ((hydro_object.hdo_id = sw_abstraction_point.sasp_id)))
     JOIN rewab_type ON ((rewab_type.rwb_id = sw_abstraction_point.rewab_id)))
     JOIN sw_abstraction_type ON ((sw_abstraction_type.swat_id = sw_abstraction_point.swat_id)))
     JOIN wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
  ORDER BY hydro_object.inspire_id_local_id, wkr_version.wkrv_id DESC;


--
-- Name: vw_ow_oordelen_chemisch_verontreinigd; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_ow_oordelen_chemisch_verontreinigd AS
 SELECT hydro_object.hdo_id AS "Waterlichaam_DB_ID",
    swb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID",
    vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving",
    classification_type.description AS "Oordeel",
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code",
    waarde_bepalings_methode_type.code AS "WaardebepalingsMethode_Code",
    ssg.ssge_id AS "ChemischeToestandGroep"
   FROM (((((((swb_yearly_status
     LEFT JOIN hydro_object ON ((hydro_object.hdo_id = swb_yearly_status.swb_id)))
     LEFT JOIN sw_chemical_substance_status ON ((sw_chemical_substance_status.sys_id = swb_yearly_status.sys_id)))
     LEFT JOIN classification_type ON ((sw_chemical_substance_status.swcs_id = classification_type.cls_id)))
     LEFT JOIN vw_waargenomen_eigenschap_db_id ON ((vw_waargenomen_eigenschap_db_id."DB_ID" = sw_chemical_substance_status.opt_id)))
     LEFT JOIN waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = sw_chemical_substance_status.wbpm_id)))
     LEFT JOIN eu_monitoring_goal_type ON ((eu_monitoring_goal_type.eumg_id = sw_chemical_substance_status.eumg_id)))
     LEFT JOIN ( SELECT substance_group_element.chs_id,
            substance_group_element.ssge_id
           FROM substance_group_element
          WHERE (substance_group_element.ssge_id = ANY (ARRAY[2907, 2908]))) ssg ON ((ssg.chs_id = vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID")));


--
-- Name: vw_ow_toetswaarden_chemisch_verontreinigd; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_ow_toetswaarden_chemisch_verontreinigd AS
 SELECT observation.obs_id AS "Toetswaarde_DB_ID",
    swb_yearly_status.sys_id AS "Status_DB_ID",
    hydro_object.hdo_id AS "Waterlichaam_DB_ID",
    swb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap_db_id."DB_ID" AS "Waargenomen_Eigenschap_DB_ID",
    vw_waargenomen_eigenschap_db_id."ChemischeStof_code",
    vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving",
    observation.numeric_value AS "Numerieke_waarde",
    bepaling_grens_type.omschrijving AS "Bepalingsgrens",
    eenheid_type.code AS "Eenheid_Code",
    eenheid_type.omschrijving AS "Eenheid_Omschrijving",
    eu_monitoring_goal_type.eumg_id AS "Oordeel_Type_DB_ID",
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code",
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving",
    waarde_bepalings_methode_type.code AS "Waardebepalingsmethode_Code",
    waarde_bepalings_methode_type.omschrijving AS "Waardebepalingsmethode_Omschrijving",
    ssg_2907.ssge_id AS "ChemischeToestandGroep",
    ssg_2908.ssge_id AS "OverigeVerontreinigendeStoffen"
   FROM (((((((((((swb_yearly_status
     LEFT JOIN sw_testing_conclusion ON ((swb_yearly_status.sys_id = sw_testing_conclusion.sys_id)))
     LEFT JOIN observation ON ((sw_testing_conclusion.stcc_id = observation.obs_id)))
     LEFT JOIN vw_waargenomen_eigenschap_db_id ON ((vw_waargenomen_eigenschap_db_id."DB_ID" = observation.observed_property_type)))
     LEFT JOIN eenheid_type ON ((eenheid_type.eenh_id = observation.numeric_value_uom_id)))
     LEFT JOIN bepaling_grens_type ON ((bepaling_grens_type.bpg_id = observation.limit_symbol_id)))
     LEFT JOIN observation_process ON ((observation_process.obp_id = observation.result_nature_id)))
     LEFT JOIN waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = observation_process.wbpm_id)))
     LEFT JOIN hydro_object ON ((hydro_object.hdo_id = swb_yearly_status.swb_id)))
     LEFT JOIN eu_monitoring_goal_type ON ((eu_monitoring_goal_type.eumg_id = sw_testing_conclusion.eumg_id)))
     LEFT JOIN ( SELECT substance_group_element.chs_id,
            substance_group_element.ssge_id
           FROM substance_group_element
          WHERE (substance_group_element.ssge_id = 2907)) ssg_2907 ON ((ssg_2907.chs_id = vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID")))
     LEFT JOIN ( SELECT substance_group_element.chs_id,
            substance_group_element.ssge_id
           FROM substance_group_element
          WHERE (substance_group_element.ssge_id = 2908)) ssg_2908 ON ((ssg_2908.chs_id = vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID")))
  ORDER BY vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving", vw_waargenomen_eigenschap_db_id."DB_ID",
        CASE
            WHEN ((eu_monitoring_goal_type.code)::text = 'TT_OM'::text) THEN 1
            WHEN ((eu_monitoring_goal_type.code)::text = 'OM'::text) THEN 2
            WHEN ((eu_monitoring_goal_type.code)::text = 'TT'::text) THEN 3
            ELSE 4
        END;


--
-- Name: vw_parameter; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_parameter AS
 SELECT typering_type.typ_id AS db_id,
    typering_type.code,
    typering_type.omschrijving,
    'typering'::text AS type
   FROM typering_type
UNION ALL
 SELECT object_type.obj_id AS db_id,
    object_type.code,
    object_type.omschrijving,
    'object'::text AS type
   FROM object_type
UNION ALL
 SELECT grootheid_type.grh_id AS db_id,
    grootheid_type.code,
    grootheid_type.omschrijving,
    'grootheid'::text AS type
   FROM grootheid_type
UNION ALL
 SELECT chemische_stof_type.chs_id AS db_id,
    chemische_stof_type.code,
    chemische_stof_type.naam AS omschrijving,
    'chemischeStof'::text AS type
   FROM chemische_stof_type
  ORDER BY 4;


--
-- Name: vw_prognose; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_prognose AS
 SELECT wb_prognosis.wbpr_id AS "DB_ID",
    hydro_object.hdo_id AS "Waterlichaam_DB_ID",
    hydro_object.inspire_id_local_id AS "Waterlichaam_Identificatie",
    wb_prognosis.year AS "PrognoseJaar",
    vw_waargenomen_eigenschap."DB_ID" AS "Waargenomen_Eigenschap_DB_ID",
    vw_waargenomen_eigenschap."Tpering_Code",
    vw_waargenomen_eigenschap."Typering_Omschrijving",
    vw_waargenomen_eigenschap."Grootheid_Code",
    vw_waargenomen_eigenschap."Grootheid_Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_code",
    vw_waargenomen_eigenschap."ChemischeStof_Omschrijving",
    vw_waargenomen_eigenschap."Hoedanigheid_Code",
    vw_waargenomen_eigenschap."Hoedanigheid_Omschrijving",
    classification_type.cls_id AS "Prognose_DB_ID",
    classification_type.description AS "Prognose_Omschrijving"
   FROM (((wb_prognosis
     JOIN hydro_object ON ((wb_prognosis.swb_id = hydro_object.hdo_id)))
     JOIN vw_waargenomen_eigenschap ON ((vw_waargenomen_eigenschap."DB_ID" = wb_prognosis.opt_id)))
     JOIN classification_type ON ((classification_type.cls_id = wb_prognosis.cls_id)))
  ORDER BY hydro_object.inspire_id_local_id, wb_prognosis.year, vw_waargenomen_eigenschap."Tpering_Code", vw_waargenomen_eigenschap."Grootheid_Code", vw_waargenomen_eigenschap."ChemischeStof_code";


--
-- Name: vw_prognose_csv; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_prognose_csv AS
 SELECT hydro_object.inspire_id_local_id AS "Waterlichaam.Identificatie",
    ('NL'::text || hydro_object.inspire_id_namespace) AS "Namespace",
    hydro_object.wkrv_id AS "Versie",
    wb_prognosis.year AS "PrognoseJaar",
    vw_waargenomen_eigenschap."Tpering_Code" AS "Typering.Code",
    vw_waargenomen_eigenschap."Typering_Omschrijving" AS "Typering.Omschrijving",
    vw_waargenomen_eigenschap."Grootheid_Code" AS "Grootheid.Code",
    vw_waargenomen_eigenschap."Grootheid_Omschrijving" AS "Grootheid.Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_code" AS "ChemischeStof.Code",
    vw_waargenomen_eigenschap."ChemischeStof_Omschrijving" AS "ChemischeStof.Omschrijving",
    vw_waargenomen_eigenschap."Hoedanigheid_Code" AS "Hoedanigheid.Code",
    vw_waargenomen_eigenschap."Hoedanigheid_Omschrijving" AS "Hoedanigheid.Omschrijving",
    classification_type.description AS "Prognose"
   FROM (((wb_prognosis
     JOIN hydro_object ON ((wb_prognosis.swb_id = hydro_object.hdo_id)))
     JOIN vw_waargenomen_eigenschap ON ((vw_waargenomen_eigenschap."DB_ID" = wb_prognosis.opt_id)))
     JOIN classification_type ON ((classification_type.cls_id = wb_prognosis.cls_id)))
  ORDER BY hydro_object.inspire_id_local_id, wb_prognosis.year, vw_waargenomen_eigenschap."Tpering_Code", vw_waargenomen_eigenschap."Grootheid_Code", vw_waargenomen_eigenschap."ChemischeStof_code";


--
-- Name: vw_register_beschermdegebieden_aquo; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_register_beschermdegebieden_aquo AS
 SELECT hydro_object.inspire_id_local_id AS identificatie,
    hdo_names.name AS omschrijving,
    protected_area_type.description AS soort,
    wfd_protected_area.comment_value_status_prot_area AS richtlijn,
    hydro_object.wkrv_id AS versie
   FROM (((((hydro_object
     LEFT JOIN hdo_names ON ((hdo_names.hdo_id = hydro_object.hdo_id)))
     JOIN wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     LEFT JOIN management_regulation_or_restriction_zone ON ((management_regulation_or_restriction_zone.mrz_id = hydro_object.hdo_id)))
     JOIN wfd_protected_area ON ((wfd_protected_area.wpa_id = hydro_object.hdo_id)))
     JOIN protected_area_type ON ((protected_area_type.pat_id = wfd_protected_area.protected_area_type_id)))
UNION ALL
 SELECT hydro_object.inspire_id_local_id AS identificatie,
    hdo_names.name AS omschrijving,
    'OnttrekkingMenselijkeConsumptie'::text AS soort,
    'Kaderrichtlijn Water (2000/60/EC), Artikel 7'::text AS richtlijn,
    hydro_object.wkrv_id AS versie
   FROM ((((hydro_object
     LEFT JOIN hdo_names ON ((hdo_names.hdo_id = hydro_object.hdo_id)))
     JOIN wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     LEFT JOIN management_regulation_or_restriction_zone ON ((management_regulation_or_restriction_zone.mrz_id = hydro_object.hdo_id)))
     JOIN water_body ON ((water_body.wbd_id = hydro_object.hdo_id)))
  WHERE (water_body.is_article_7 IS TRUE);


--
-- Name: vw_register_beschermdegebieden_shape; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_register_beschermdegebieden_shape AS
 SELECT hydro_object.inspire_id_local_id AS besident,
    hdo_names.name AS besnaam,
    protected_area_type.description AS bessoort,
    wfd_protected_area.comment_value_status_prot_area AS richtlijn,
    mrz.geometry,
    hydro_object.wkrv_id AS versie,
    geometrytype(mrz.geometry) AS geometrytype,
    concat(row_number() OVER (ORDER BY hydro_object.inspire_id_local_id), hydro_object.hdo_id, hydro_object.inspire_id_local_id) AS "Unique_Key"
   FROM (((((hydro_object
     LEFT JOIN hdo_names ON ((hdo_names.hdo_id = hydro_object.hdo_id)))
     JOIN wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     LEFT JOIN management_regulation_or_restriction_zone mrz ON ((mrz.mrz_id = hydro_object.hdo_id)))
     JOIN wfd_protected_area ON ((wfd_protected_area.wpa_id = hydro_object.hdo_id)))
     JOIN protected_area_type ON ((protected_area_type.pat_id = wfd_protected_area.protected_area_type_id)))
UNION ALL
 SELECT hydro_object.inspire_id_local_id AS besident,
    hdo_names.name AS besnaam,
    (('OnttrekkingMenselijkeConsumptie ('::text ||
        CASE
            WHEN ((hydro_object.hydro_object_type)::text = 'SurfaceWaterBody'::text) THEN 'Oppervlaktewater'::text
            ELSE 'Grondwater'::text
        END) || ')'::text) AS bessoort,
    'Kaderrichtlijn Water (2000/60/EC), Artikel 7'::text AS richtlijn,
    mrz.geometry,
    hydro_object.wkrv_id AS versie,
    geometrytype(mrz.geometry) AS geometrytype,
    concat(row_number() OVER (ORDER BY hydro_object.inspire_id_local_id), hydro_object.hdo_id, hydro_object.inspire_id_local_id) AS "Unique_Key"
   FROM ((((hydro_object
     LEFT JOIN hdo_names ON ((hdo_names.hdo_id = hydro_object.hdo_id)))
     JOIN wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     LEFT JOIN management_regulation_or_restriction_zone mrz ON ((mrz.mrz_id = hydro_object.hdo_id)))
     JOIN water_body ON ((water_body.wbd_id = hydro_object.hdo_id)))
  WHERE (water_body.is_article_7 IS TRUE);


--
-- Name: vw_reportversion_for_crystal_reports; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_reportversion_for_crystal_reports AS
 SELECT report_version.rpv_id AS db_id,
    (object_state_type.decsription)::character varying AS rapportagestatus,
    (reporting_period.description)::character varying AS rapportageperiode,
    reporting_period.period_start AS rapportageperiodebegin,
    reporting_period.period_end AS rapportageperiodeeind,
    legislation_reference_type.lgr_id AS rapportagebasis_id,
    legislation_reference_type.legal_name AS rapportagebasis_omschrijving,
    report_version.begin_lifespan_version AS beginrapportage,
    report_version.end_lifespan_version AS eindrapportage,
    report_version.is_public_data AS publiektoegankelijk,
    wkr_version.wkrv_id AS wkr_versie,
    wkr_version.begin_lifespan_version AS wkr_beginversie,
    wkr_version.end_lifespan_version AS wkr_eindversie,
    (((((reporting_period.description || ' ('::text) || object_state_type.decsription) || ') - '::text) || wkr_version.wkrv_id))::character varying AS version_details
   FROM ((legislation_reference_type
     JOIN reporting_period ON ((legislation_reference_type.lgr_id = reporting_period.leg_ref_id)))
     FULL JOIN ((report_version
     JOIN object_state_type ON ((report_version.ost_id = object_state_type.ost_id)))
     FULL JOIN wkr_version ON ((report_version.wkrv_id = wkr_version.wkrv_id))) ON ((reporting_period.rpp_id = report_version.rpp_id)))
  ORDER BY report_version.rpv_id, reporting_period.period_start;


--
-- Name: vw_status_motivering_waterlichaam; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_status_motivering_waterlichaam AS
 SELECT scm.wbsc_id,
    scm.wbd_id,
    scm.scm_id,
    scm.opt_id,
    scm.year_initial,
    scm.year_status,
    owl.identificatie,
    owl.omschrijving,
    owl.waterbeheerder_code,
    scm.last_change_time
   FROM (wb_status_change_motivation scm
     JOIN vw_oppervlaktewaterlichamen owl ON ((owl.db_id = scm.wbd_id)))
  ORDER BY scm.wbsc_id;


--
-- Name: zone_type_code; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE zone_type_code (
    ztc_id integer NOT NULL,
    code character varying(50) NOT NULL,
    description character varying(90) NOT NULL
);


--
-- Name: vw_stroomgebieddistricten; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_stroomgebieddistricten AS
 SELECT hydro_object.hdo_id AS "DB_ID",
    hydro_object.inspire_id_local_id AS "Identificatie",
    hdo_names.name AS omschrijving,
    river_basin_disrict.area_value AS "Oppervlakte_NumeriekeWaarde",
    eenheid_type.code AS "Oppervlakte_Eenheid_Code",
    eenheid_type.omschrijving AS "Oppervlakte_Eenheid_Omschrijving",
    zone_type_code.code AS "StroomgebiedType_Code",
    zone_type_code.description AS "StroomgebiedType_Omschrijving",
    wkr_version.wkrv_id AS wkr_versie,
    wkr_version.begin_lifespan_version AS wkr_beginversie,
    wkr_version.end_lifespan_version AS wkr_eindversie,
    hydro_object.related_hdo_id AS "Gerelateerd_HydroObject"
   FROM ((((((hydro_object
     LEFT JOIN hdo_names ON ((hdo_names.hdo_id = hydro_object.hdo_id)))
     JOIN management_regulation_or_restriction_zone ON ((hydro_object.hdo_id = management_regulation_or_restriction_zone.mrz_id)))
     JOIN river_basin_disrict ON ((hydro_object.hdo_id = river_basin_disrict.rbd_id)))
     JOIN eenheid_type ON ((eenheid_type.eenh_id = river_basin_disrict.area_uom_id)))
     JOIN wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     JOIN zone_type_code ON ((zone_type_code.ztc_id = management_regulation_or_restriction_zone.specialised_zone_type_id)))
  WHERE (river_basin_disrict.international_rbd IS NOT NULL);


--
-- Name: vw_stroomgebieddistricten_g; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_stroomgebieddistricten_g AS
 SELECT hydro_object.hdo_id AS "DB_ID",
    hydro_object.inspire_id_local_id AS "Identificatie",
    hdo_names.name AS omschrijving,
    management_regulation_or_restriction_zone.geometry,
    zone_type_code.code AS "StroomgebiedType_Code",
    zone_type_code.description AS "StroomgebiedType_Omschrijving",
    hydro_object.wkrv_id AS wkr_versie
   FROM ((((hydro_object
     LEFT JOIN hdo_names ON ((hdo_names.hdo_id = hydro_object.hdo_id)))
     JOIN management_regulation_or_restriction_zone ON ((hydro_object.hdo_id = management_regulation_or_restriction_zone.mrz_id)))
     JOIN river_basin_disrict ON ((hydro_object.hdo_id = river_basin_disrict.rbd_id)))
     JOIN zone_type_code ON ((zone_type_code.ztc_id = management_regulation_or_restriction_zone.specialised_zone_type_id)))
  WHERE (river_basin_disrict.international_rbd IS NOT NULL);


--
-- Name: vw_stroomgebieddistricten_inspire; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_stroomgebieddistricten_inspire AS
 SELECT ((((('NL.WBHCODE.'::text || hydro_object.inspire_id_namespace) || '.'::text) || (hydro_object.inspire_id_local_id)::text) || '.'::text) || hydro_object.wkrv_id) AS "InspireId",
    ('NL.WBHCODE.'::text || hydro_object.inspire_id_namespace) AS "inspireId.namespace",
    hydro_object.inspire_id_local_id AS "inspireId.localId",
    hydro_object.wkrv_id AS "inspireId.Version",
    hydro_object.inspire_id_local_id AS "thematicId",
    hdo_names.name AS "name.text",
    hdo_names.language AS "name.languate",
    'http://inspire.ec.europa.eu/codelist/NativenessValue/endonym'::text AS "name.nativeness",
    'http://inspire.ec.europa.eu/codelist/NameStatusValue/official'::text AS "name.nameStatus",
    namespace_type.descrption AS "name.sourceOfName",
    mrz.geometry_etrs89 AS st_transform,
    geometrytype(mrz.geometry_etrs89) AS geometry_type,
    'http://inspire.ec.europa.eu/codelist/ZoneTypeCode/waterBodyForWFD'::text AS "zoneType",
    hydro_object.hydro_object_type AS "specialisedZoneType",
    'http://inspire.ec.europa.eu/codelist/EnvironmentalDomain/water'::text AS "environmentalDomain",
    'Ministry of Infrastruture and Environment'::text AS "competentAuthority.organisationName",
    'http://inspire.ec.europa.eu/codelist/RelatedPartyRoleValue/authority'::text AS "competentAuthority.role",
    wkr_version.begin_lifespan_version AS "beginLifeSpanVersion",
    wkr_version.end_lifespan_version AS "endLifeSpanVersion",
    'Directive 2000/60/EC of the European Parliament and of the Council establishing a framework for the Community action in the field of water policy'::text AS "legalBasis.name",
    'Water Framework Directive'::text AS "legalBasis.shortName",
    '2000-10-23'::text AS "legalBasis.date",
    'http://eur-lex.europa.eu/LexUriServ/LexUriServ.do?uri=CELEX:32000L0060:EN:NOT'::text AS "legalBasis.link",
    '2000/60/EC'::text AS "legalBasis.identificationNumber",
    'European'::text AS "legalBasis.level"
   FROM (((((management_regulation_or_restriction_zone mrz
     JOIN river_basin_disrict ON ((mrz.mrz_id = river_basin_disrict.rbd_id)))
     JOIN hydro_object ON ((hydro_object.hdo_id = mrz.mrz_id)))
     LEFT JOIN hdo_names ON ((hdo_names.hdo_id = mrz.mrz_id)))
     JOIN wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     JOIN namespace_type ON ((namespace_type.ns_id = hydro_object.inspire_id_namespace)))
  WHERE (river_basin_disrict.international_rbd IS NOT NULL);


--
-- Name: vw_stroomgebieddistricten_shape; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_stroomgebieddistricten_shape AS
 SELECT DISTINCT ON (hydro_object.inspire_id_local_id) hydro_object.inspire_id_local_id AS "IDENT",
    hdo_names.name AS "NAAM",
    management_regulation_or_restriction_zone.geometry,
    river_basin_disrict.area_value AS "OPP",
    eenheid_type.code AS "OPP_EENH"
   FROM (((((hydro_object
     LEFT JOIN hdo_names ON ((hdo_names.hdo_id = hydro_object.hdo_id)))
     JOIN management_regulation_or_restriction_zone ON ((hydro_object.hdo_id = management_regulation_or_restriction_zone.mrz_id)))
     JOIN river_basin_disrict ON ((hydro_object.hdo_id = river_basin_disrict.rbd_id)))
     JOIN eenheid_type ON ((eenheid_type.eenh_id = river_basin_disrict.area_uom_id)))
     JOIN wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
  WHERE (river_basin_disrict.international_rbd IS NOT NULL)
  ORDER BY hydro_object.inspire_id_local_id, wkr_version.wkrv_id DESC;


--
-- Name: vw_tijdwaarden_bestrijdingsmiddelen_csv; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_tijdwaarden_bestrijdingsmiddelen_csv AS
 SELECT monitoring_object.inspire_id_local_id AS "Meetpunt.identificatie",
    monitoring_object.name AS "Meetpunt.omschrijving",
    namespace_type_1.code AS "Waterbeheerder.code",
    namespace_type_1.descrption AS "Waterbeheerder.omschrijving",
    typering_type.code AS "Typering.code",
    typering_type.omschrijving AS "Typering.omschrijving",
    grootheid_type.code AS "Grootheid.code",
    grootheid_type.omschrijving AS "Grootheid.omschrijving",
    chemische_stof_type.code AS "Parameter.code",
    chemische_stof_type.naam AS "Parameter.omschrijving",
    chemische_stof_type.cas_nr AS "Chemischestof.casnr",
    object_type.code AS "Object.code",
    object_type.omschrijving AS "Object.omschrijving",
    eenheid_type.code AS "Eenheid.code",
    eenheid_type.omschrijving AS "Eenheid.omschrijving",
    hoedanigheid_type.code AS "Hoedanigheid.code",
    hoedanigheid_type.omschrijving AS "Hoedanigheid.omschrijving",
    compartiment_type.code AS "Compartiment.code",
    compartiment_type.description AS "Compartiment.omschrijving",
    waarde_bewerkings_methode_type.code AS "Waardebewerkingsmethode.code",
    waarde_bewerkings_methode_type.omschrijving AS "Waardebewerkingsmethode.omschrijving",
    classification_group_type.code AS "Classificatie.waarde",
    classification_group_type.description AS "Classificatie.groep",
    to_char(observation.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(observation.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(observation.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(observation.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    bepaling_grens_type.omschrijving AS "Limietsymbool",
    observation.numeric_value AS "Numeriekewaarde",
    observation.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    observation.remarks AS "Opmerking",
    monitoring_object.mno_id AS "Meetpunt.DB_ID",
    observation.last_change_time,
    date_part('year'::text, observation.phenomenon_time_begin) AS "Rapportagejaar",
    monitoring_object.wkrv_id AS "Versie"
   FROM (((((((((((((((((observed_property_type
     JOIN observation ON ((observation.observed_property_type = observed_property_type.opt_id)))
     LEFT JOIN object_group ON ((object_group.ojg_id = observed_property_type.object_group_id)))
     LEFT JOIN typering_type ON ((typering_type.typ_id = observed_property_type.indicator_id)))
     LEFT JOIN grootheid_type ON ((observed_property_type.quantity_id = grootheid_type.grh_id)))
     JOIN monitored_compartiment ON ((monitored_compartiment.mno_id = observation.mno_id)))
     JOIN compartiment_type ON ((monitored_compartiment.cptm_id = compartiment_type.cptm_id)))
     LEFT JOIN substance_group_element ON ((observed_property_type.substance_group_id = substance_group_element.ssge_id)))
     LEFT JOIN chemische_stof_type ON ((chemische_stof_type.chs_id = substance_group_element.chs_id)))
     JOIN kwaliteitsoordeel_type ON ((observation.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN classification_group_type ON ((observation.classified_result_id = classification_group_type.clsg_id)))
     JOIN eenheid_type ON ((observation.numeric_value_uom_id = eenheid_type.eenh_id)))
     LEFT JOIN bepaling_grens_type ON ((observation.limit_symbol_id = bepaling_grens_type.bpg_id)))
     LEFT JOIN waarde_bewerkings_methode_type ON ((observation.value_processing_method_id = waarde_bewerkings_methode_type.wbm_id)))
     LEFT JOIN object_group_element ON ((object_group.ojg_id = object_group_element.ojg_id)))
     LEFT JOIN object_type ON ((object_group_element.obj_id = object_type.obj_id)))
     JOIN (namespace_type namespace_type_1
     LEFT JOIN monitoring_object ON ((namespace_type_1.ns_id = monitoring_object.inspire_id_namespace_id))) ON ((observation.mno_id = monitoring_object.mno_id)))
     JOIN hoedanigheid_type ON (((hoedanigheid_type.hdh_id = observed_property_type.hoedanigheid_id) AND ((chemische_stof_type.code)::text IN ( SELECT vw_chemischestof_groepen."ChemischeStof_Code"
           FROM vw_chemischestof_groepen
          WHERE ((vw_chemischestof_groepen."Groepsnaam")::text = 'Bestrijdingsmiddelen CML'::text))) AND (observation.numeric_value IS NOT NULL))))
  ORDER BY namespace_type_1.code, monitoring_object.inspire_id_local_id;


--
-- Name: vw_tijdwaarden_bestrijdingsmiddelen_g; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_tijdwaarden_bestrijdingsmiddelen_g AS
 SELECT observation.obs_id AS db_id,
    monitoring_object.mno_id AS meetpunt_identificatie_db_id,
    monitoring_object.inspire_id_local_id AS "Meetpunt.identificatie",
    monitoring_object.name AS "Meetpunt.omschrijving",
    st_x(st_transform(monitoring_object.geometry, 28992)) AS "GeometriePunt.X_RD",
    st_y(st_transform(monitoring_object.geometry, 28992)) AS "GeometriePunt.Y_RD",
    monitoring_object.geometry,
    namespace_type_1.code AS "Waterbeheerder.code",
    namespace_type_1.descrption AS "Waterbeheerder.omschrijving",
    typering_type.code AS "Typering.code",
    typering_type.omschrijving AS "Typering.omschrijving",
    grootheid_type.code AS "Grootheid.code",
    grootheid_type.omschrijving AS "Grootheid.omschrijving",
    chemische_stof_type.code AS "Parameter.code",
    chemische_stof_type.naam AS "Parameter.omschrijving",
    chemische_stof_type.cas_nr AS "Chemischestof.casnr",
    object_type.code AS "Object.code",
    object_type.omschrijving AS "Object.omschrijving",
    eenheid_type.code AS "Eenheid.code",
    eenheid_type.omschrijving AS "Eenheid.omschrijving",
    hoedanigheid_type.code AS "Hoedanigheid.code",
    hoedanigheid_type.omschrijving AS "Hoedanigheid.omschrijving",
    compartiment_type.code AS "Compartiment.code",
    compartiment_type.description AS "Compartiment.omschrijving",
    waarde_bewerkings_methode_type.code AS "Waardebewerkingsmethode.code",
    waarde_bewerkings_methode_type.omschrijving AS "Waardebewerkingsmethode.omschrijving",
    ''::text AS "Waardebepalingsmethode.code",
    ''::text AS "Waardebepalingsmethode.omschrijving",
    classification_group_type.code AS "Classificatie.waarde",
    classification_group_type.description AS "Classificatie.groep",
    to_char(observation.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(observation.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(observation.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(observation.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    bepaling_grens_type.omschrijving AS "Limietsymbool",
    observation.numeric_value AS "Numeriekewaarde",
    observation.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    observation.remarks AS "Opmerking",
    sg."Groepsnaam" AS "SubstanceGroup.omschrijving",
    sg."Type" AS "SubstanceGroup.type",
    observation.last_change_time,
    monitoring_object.wkrv_id
   FROM (((((((((((((((((((observed_property_type
     JOIN observation ON ((observation.observed_property_type = observed_property_type.opt_id)))
     LEFT JOIN object_group ON ((object_group.ojg_id = observed_property_type.object_group_id)))
     LEFT JOIN typering_type ON ((typering_type.typ_id = observed_property_type.indicator_id)))
     LEFT JOIN grootheid_type ON ((observed_property_type.quantity_id = grootheid_type.grh_id)))
     JOIN monitored_compartiment ON ((monitored_compartiment.mno_id = observation.mno_id)))
     JOIN compartiment_type ON ((monitored_compartiment.cptm_id = compartiment_type.cptm_id)))
     JOIN (chemische_stof_type
     LEFT JOIN substance_group_element ON ((chemische_stof_type.chs_id = substance_group_element.chs_id))) ON ((observed_property_type.substance_group_id = substance_group_element.ssge_id)))
     LEFT JOIN kwaliteitsoordeel_type ON ((observation.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN classification_group_type ON ((observation.classified_result_id = classification_group_type.clsg_id)))
     JOIN eenheid_type ON ((observation.numeric_value_uom_id = eenheid_type.eenh_id)))
     LEFT JOIN bepaling_grens_type ON ((observation.limit_symbol_id = bepaling_grens_type.bpg_id)))
     JOIN waarde_bewerkings_methode_type ON ((observation.value_processing_method_id = waarde_bewerkings_methode_type.wbm_id)))
     LEFT JOIN object_group_element ON ((object_group.ojg_id = object_group_element.ojg_id)))
     LEFT JOIN object_type ON ((object_group_element.obj_id = object_type.obj_id)))
     JOIN (namespace_type namespace_type_1
     LEFT JOIN monitoring_object ON ((namespace_type_1.ns_id = monitoring_object.inspire_id_namespace_id))) ON ((observation.mno_id = monitoring_object.mno_id)))
     LEFT JOIN taxa_group_element ON ((observed_property_type.taxa_group_id = taxa_group_element.txg_id)))
     LEFT JOIN taxon_type ON ((observed_property_type.taxa_group_id = taxa_group_element.txg_id)))
     JOIN hoedanigheid_type ON ((hoedanigheid_type.hdh_id = observed_property_type.hoedanigheid_id)))
     JOIN vw_chemischestof_groepen sg ON (((sg."ChemischeStof_Code")::text = (chemische_stof_type.code)::text)))
  WHERE (((sg."Groepsnaam")::text = 'Bestrijdingsmiddelen CML'::text) AND (monitoring_object.wkrv_id = 7) AND (st_geometrytype(monitoring_object.geometry) = 'ST_Point'::text))
  ORDER BY namespace_type_1.code, monitoring_object.inspire_id_local_id, chemische_stof_type.code, (to_char(observation.phenomenon_time_begin, 'YYYY-MM-DD'::text)) DESC;


--
-- Name: vw_tijdwaarden_csv; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_tijdwaarden_csv AS
 SELECT monitoring_object.inspire_id_local_id AS "Meetpunt.identificatie",
    monitoring_object.name AS "Meetpunt.omschrijving",
    ca.namespace_id AS "Waterbeheerder.code",
    ore.naam AS "Waterbeheerder.omschrijving",
    typering_type.code AS "Typering.code",
    typering_type.omschrijving AS "Typering.omschrijving",
    grootheid_type.code AS "Grootheid.code",
    grootheid_type.omschrijving AS "Grootheid.omschrijving",
    chemische_stof_type.code AS "Parameter.code",
    chemische_stof_type.naam AS "Parameter.omschrijving",
    chemische_stof_type.cas_nr AS "Chemischestof.casnr",
    txn.omschrijving AS "Taxon.omschrijving",
    object_type.code AS "Object.code",
    object_type.omschrijving AS "Object.omschrijving",
    eenheid_type.code AS "Eenheid.code",
    eenheid_type.omschrijving AS "Eenheid.omschrijving",
    hoedanigheid_type.code AS "Hoedanigheid.code",
    hoedanigheid_type.omschrijving AS "Hoedanigheid.omschrijving",
    compartiment_type.code AS "Compartiment.code",
    compartiment_type.description AS "Compartiment.omschrijving",
    waarde_bewerkings_methode_type.code AS "Waardebewerkingsmethode.code",
    waarde_bewerkings_methode_type.omschrijving AS "Waardebewerkingsmethode.omschrijving",
    classification_group_type.code AS "Classificatie.waarde",
    classification_group_type.description AS "Classificatie.groep",
    to_char(observation.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(observation.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(observation.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(observation.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    bepaling_grens_type.omschrijving AS "Limietsymbool",
    observation.numeric_value AS "Numeriekewaarde",
    observation.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    observation.remarks AS "Opmerking",
    monitoring_object.mno_id AS "Meetpunt.DB_ID",
    date_part('year'::text, observation.phenomenon_time_begin) AS "Rapportagejaar",
    observation.last_change_time,
    monitoring_object.wkrv_id AS "Versie"
   FROM (((((((((((((((((((((((observation
     JOIN observed_property_type ON ((observation.observed_property_type = observed_property_type.opt_id)))
     LEFT JOIN grootheid_type ON ((observed_property_type.quantity_id = grootheid_type.grh_id)))
     LEFT JOIN object_group ON ((object_group.ojg_id = observed_property_type.object_group_id)))
     LEFT JOIN object_group_element ON ((object_group.ojg_id = object_group_element.ojg_id)))
     LEFT JOIN object_type ON ((object_group_element.obj_id = object_type.obj_id)))
     LEFT JOIN typering_type ON ((typering_type.typ_id = observed_property_type.indicator_id)))
     JOIN monitored_compartiment ON ((monitored_compartiment.mno_id = observation.mno_id)))
     JOIN compartiment_type ON ((monitored_compartiment.cptm_id = compartiment_type.cptm_id)))
     LEFT JOIN substance_group_element ON ((observed_property_type.substance_group_id = substance_group_element.ssge_id)))
     LEFT JOIN chemische_stof_type ON ((chemische_stof_type.chs_id = substance_group_element.chs_id)))
     LEFT JOIN kwaliteitsoordeel_type ON ((observation.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN classification_group_type ON ((observation.classified_result_id = classification_group_type.clsg_id)))
     JOIN eenheid_type ON ((observation.numeric_value_uom_id = eenheid_type.eenh_id)))
     LEFT JOIN bepaling_grens_type ON ((observation.limit_symbol_id = bepaling_grens_type.bpg_id)))
     LEFT JOIN taxa_group txg ON ((txg.txg_id = observed_property_type.taxa_group_id)))
     LEFT JOIN taxa_group_element txe ON ((txe.txg_id = txg.txg_id)))
     LEFT JOIN taxon_type txn ON ((txn.txn_id = txe.txg_id)))
     LEFT JOIN waarde_bewerkings_methode_type ON ((observation.value_processing_method_id = waarde_bewerkings_methode_type.wbm_id)))
     JOIN (namespace_type namespace_type_1
     JOIN monitoring_object ON ((namespace_type_1.ns_id = monitoring_object.inspire_id_namespace_id))) ON ((observation.mno_id = monitoring_object.mno_id)))
     JOIN hoedanigheid_type ON ((hoedanigheid_type.hdh_id = observed_property_type.hoedanigheid_id)))
     JOIN competentauthority_namespacetype cns ON ((cns.ns_id = namespace_type_1.ns_id)))
     JOIN organisatorische_eenheid ore ON ((ore.ore_id = cns.ore_id)))
     JOIN competent_authority ca ON ((ca.ore_id = ore.ore_id)));


--
-- Name: vw_tijdwaarden_csv_test; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_tijdwaarden_csv_test AS
 SELECT monitoring_object.inspire_id_local_id AS "Meetpunt.identificatie",
    monitoring_object.name AS "Meetpunt.omschrijving",
    ca.namespace_id AS "Waterbeheerder.code",
    ore.naam AS "Waterbeheerder.omschrijving",
    typering_type.code AS "Typering.code",
    typering_type.omschrijving AS "Typering.omschrijving",
    grootheid_type.code AS "Grootheid.code",
    grootheid_type.omschrijving AS "Grootheid.omschrijving",
    chemische_stof_type.code AS "Parameter.code",
    chemische_stof_type.naam AS "Parameter.omschrijving",
    chemische_stof_type.cas_nr AS "Chemischestof.casnr",
    txn.omschrijving AS "Taxon.omschrijving",
    object_type.code AS "Object.code",
    object_type.omschrijving AS "Object.omschrijving",
    eenheid_type.code AS "Eenheid.code",
    eenheid_type.omschrijving AS "Eenheid.omschrijving",
    hoedanigheid_type.code AS "Hoedanigheid.code",
    hoedanigheid_type.omschrijving AS "Hoedanigheid.omschrijving",
    compartiment_type.code AS "Compartiment.code",
    compartiment_type.description AS "Compartiment.omschrijving",
    waarde_bewerkings_methode_type.code AS "Waardebewerkingsmethode.code",
    waarde_bewerkings_methode_type.omschrijving AS "Waardebewerkingsmethode.omschrijving",
    classification_group_type.code AS "Classificatie.waarde",
    classification_group_type.description AS "Classificatie.groep",
    to_char(observation.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(observation.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(observation.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(observation.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    bepaling_grens_type.omschrijving AS "Limietsymbool",
    observation.numeric_value AS "Numeriekewaarde",
    to_char(observation.numeric_value, '99999D99999999'::text) AS "Numerieke_waarde",
    observation.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    observation.remarks AS "Opmerking",
    monitoring_object.mno_id AS "Meetpunt.DB_ID",
    date_part('year'::text, observation.phenomenon_time_begin) AS "Rapportagejaar",
    observation.last_change_time,
    monitoring_object.wkrv_id AS "Versie"
   FROM (((((((((((((((((((((((observation
     JOIN observed_property_type ON ((observation.observed_property_type = observed_property_type.opt_id)))
     LEFT JOIN grootheid_type ON ((observed_property_type.quantity_id = grootheid_type.grh_id)))
     LEFT JOIN object_group ON ((object_group.ojg_id = observed_property_type.object_group_id)))
     LEFT JOIN object_group_element ON ((object_group.ojg_id = object_group_element.ojg_id)))
     LEFT JOIN object_type ON ((object_group_element.obj_id = object_type.obj_id)))
     LEFT JOIN typering_type ON ((typering_type.typ_id = observed_property_type.indicator_id)))
     JOIN monitored_compartiment ON ((monitored_compartiment.mno_id = observation.mno_id)))
     JOIN compartiment_type ON ((monitored_compartiment.cptm_id = compartiment_type.cptm_id)))
     LEFT JOIN substance_group_element ON ((observed_property_type.substance_group_id = substance_group_element.ssge_id)))
     LEFT JOIN chemische_stof_type ON ((chemische_stof_type.chs_id = substance_group_element.chs_id)))
     LEFT JOIN kwaliteitsoordeel_type ON ((observation.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN classification_group_type ON ((observation.classified_result_id = classification_group_type.clsg_id)))
     JOIN eenheid_type ON ((observation.numeric_value_uom_id = eenheid_type.eenh_id)))
     LEFT JOIN bepaling_grens_type ON ((observation.limit_symbol_id = bepaling_grens_type.bpg_id)))
     LEFT JOIN taxa_group txg ON ((txg.txg_id = observed_property_type.taxa_group_id)))
     LEFT JOIN taxa_group_element txe ON ((txe.txg_id = txg.txg_id)))
     LEFT JOIN taxon_type txn ON ((txn.txn_id = txe.txg_id)))
     LEFT JOIN waarde_bewerkings_methode_type ON ((observation.value_processing_method_id = waarde_bewerkings_methode_type.wbm_id)))
     JOIN (namespace_type namespace_type_1
     JOIN monitoring_object ON ((namespace_type_1.ns_id = monitoring_object.inspire_id_namespace_id))) ON ((observation.mno_id = monitoring_object.mno_id)))
     JOIN hoedanigheid_type ON ((hoedanigheid_type.hdh_id = observed_property_type.hoedanigheid_id)))
     JOIN competentauthority_namespacetype cns ON ((cns.ns_id = namespace_type_1.ns_id)))
     JOIN organisatorische_eenheid ore ON ((ore.ore_id = cns.ore_id)))
     JOIN competent_authority ca ON ((ca.ore_id = ore.ore_id)));


--
-- Name: vw_tijdwaarden_g; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_tijdwaarden_g AS
 SELECT monitoring_object.inspire_id_local_id AS "Meetpunt.identificatie",
    monitoring_object.name AS "Meetpunt.omschrijving",
    namespace_type_1.code AS "Waterbeheerder.code",
    namespace_type_1.descrption AS "Waterbeheerder.omschrijving",
    typering_type.code AS "Typering.code",
    typering_type.omschrijving AS "Typering.omschrijving",
    grootheid_type.code AS "Grootheid.code",
    grootheid_type.omschrijving AS "Grootheid.omschrijving",
    chemische_stof_type.code AS "Parameter.code",
    chemische_stof_type.naam AS "Parameter.omschrijving",
    chemische_stof_type.cas_nr AS "Chemischestof.casnr",
    object_type.code AS "Object.code",
    object_type.omschrijving AS "Object.omschrijving",
    eenheid_type.code AS "Eenheid.code",
    eenheid_type.omschrijving AS "Eenheid.omschrijving",
    hoedanigheid_type.code AS "Hoedanigheid.code",
    hoedanigheid_type.omschrijving AS "Hoedanigheid.omschrijving",
    compartiment_type.code AS "Compartiment.code",
    compartiment_type.description AS "Compartiment.omschrijving",
    waarde_bewerkings_methode_type.code AS "Waardebewerkingsmethode.code",
    waarde_bewerkings_methode_type.omschrijving AS "Waardebewerkingsmethode.omschrijving",
    classification_group_type.code AS "Classificatie.waarde",
    classification_group_type.description AS "Classificatie.groep",
    to_char(observation.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(observation.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(observation.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(observation.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    bepaling_grens_type.omschrijving AS "Limietsymbool",
    observation.numeric_value AS "Numeriekewaarde",
    observation.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    observation.remarks AS "Opmerking",
    observation.last_change_time,
    monitoring_object.wkrv_id
   FROM (((((((((((((((((observation
     JOIN observed_property_type ON ((observation.observed_property_type = observed_property_type.opt_id)))
     LEFT JOIN grootheid_type ON ((observed_property_type.quantity_id = grootheid_type.grh_id)))
     LEFT JOIN object_group ON ((object_group.ojg_id = observed_property_type.object_group_id)))
     LEFT JOIN object_group_element ON ((object_group.ojg_id = object_group_element.ojg_id)))
     LEFT JOIN object_type ON ((object_group_element.obj_id = object_type.obj_id)))
     LEFT JOIN typering_type ON ((typering_type.typ_id = observed_property_type.indicator_id)))
     JOIN monitored_compartiment ON ((monitored_compartiment.mno_id = observation.mno_id)))
     JOIN compartiment_type ON ((monitored_compartiment.cptm_id = compartiment_type.cptm_id)))
     LEFT JOIN substance_group_element ON ((observed_property_type.substance_group_id = substance_group_element.ssge_id)))
     LEFT JOIN chemische_stof_type ON ((chemische_stof_type.chs_id = substance_group_element.chs_id)))
     JOIN kwaliteitsoordeel_type ON ((observation.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN classification_group_type ON ((observation.classified_result_id = classification_group_type.clsg_id)))
     JOIN eenheid_type ON ((observation.numeric_value_uom_id = eenheid_type.eenh_id)))
     LEFT JOIN bepaling_grens_type ON ((observation.limit_symbol_id = bepaling_grens_type.bpg_id)))
     LEFT JOIN waarde_bewerkings_methode_type ON ((observation.value_processing_method_id = waarde_bewerkings_methode_type.wbm_id)))
     JOIN (namespace_type namespace_type_1
     JOIN monitoring_object ON ((namespace_type_1.ns_id = monitoring_object.inspire_id_namespace_id))) ON ((observation.mno_id = monitoring_object.mno_id)))
     JOIN hoedanigheid_type ON ((hoedanigheid_type.hdh_id = observed_property_type.hoedanigheid_id)))
  WHERE (monitoring_object.wkrv_id <> 3);


--
-- Name: vw_tijdwaarden_mnlso_csv; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_tijdwaarden_mnlso_csv AS
 SELECT monitoring_object.inspire_id_local_id AS "Meetpunt.identificatie",
    monitoring_object.name AS "Meetpunt.omschrijving",
    namespace_type_1.code AS "Waterbeheerder.code",
    namespace_type_1.descrption AS "Waterbeheerder.omschrijving",
    typering_type.code AS "Typering.code",
    typering_type.omschrijving AS "Typering.omschrijving",
    grootheid_type.code AS "Grootheid.code",
    grootheid_type.omschrijving AS "Grootheid.omschrijving",
    chemische_stof_type.code AS "Parameter.code",
    chemische_stof_type.naam AS "Parameter.omschrijving",
    chemische_stof_type.cas_nr AS "Chemischestof.casnr",
    object_type.code AS "Object.code",
    object_type.omschrijving AS "Object.omschrijving",
    eenheid_type.code AS "Eenheid.code",
    eenheid_type.omschrijving AS "Eenheid.omschrijving",
    hoedanigheid_type.code AS "Hoedanigheid.code",
    hoedanigheid_type.omschrijving AS "Hoedanigheid.omschrijving",
    compartiment_type.code AS "Compartiment.code",
    compartiment_type.description AS "Compartiment.omschrijving",
    waarde_bewerkings_methode_type.code AS "Waardebewerkingsmethode.code",
    waarde_bewerkings_methode_type.omschrijving AS "Waardebewerkingsmethode.omschrijving",
    classification_group_type.code AS "Classificatie.waarde",
    classification_group_type.description AS "Classificatie.groep",
    to_char(observation.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(observation.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(observation.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(observation.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    bepaling_grens_type.omschrijving AS "Limietsymbool",
    observation.numeric_value AS "Numeriekewaarde",
    observation.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    observation.remarks AS "Opmerking",
    observation.last_change_time,
    monitoring_object.wkrv_id
   FROM ((((((((((((((((((observed_property_type
     JOIN observation ON ((observation.observed_property_type = observed_property_type.opt_id)))
     LEFT JOIN object_group ON ((object_group.ojg_id = observed_property_type.object_group_id)))
     LEFT JOIN typering_type ON ((typering_type.typ_id = observed_property_type.indicator_id)))
     LEFT JOIN grootheid_type ON ((observed_property_type.quantity_id = grootheid_type.grh_id)))
     JOIN monitored_compartiment ON ((monitored_compartiment.mno_id = observation.mno_id)))
     JOIN compartiment_type ON ((monitored_compartiment.cptm_id = compartiment_type.cptm_id)))
     LEFT JOIN substance_group_element ON ((observed_property_type.substance_group_id = substance_group_element.ssge_id)))
     LEFT JOIN chemische_stof_type ON ((chemische_stof_type.chs_id = substance_group_element.chs_id)))
     JOIN kwaliteitsoordeel_type ON ((observation.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN classification_group_type ON ((observation.classified_result_id = classification_group_type.clsg_id)))
     JOIN eenheid_type ON ((observation.numeric_value_uom_id = eenheid_type.eenh_id)))
     LEFT JOIN bepaling_grens_type ON ((observation.limit_symbol_id = bepaling_grens_type.bpg_id)))
     LEFT JOIN waarde_bewerkings_methode_type ON ((observation.value_processing_method_id = waarde_bewerkings_methode_type.wbm_id)))
     LEFT JOIN object_group_element ON ((object_group.ojg_id = object_group_element.ojg_id)))
     LEFT JOIN object_type ON ((object_group_element.obj_id = object_type.obj_id)))
     JOIN (namespace_type namespace_type_1
     LEFT JOIN monitoring_object ON ((namespace_type_1.ns_id = monitoring_object.inspire_id_namespace_id))) ON ((observation.mno_id = monitoring_object.mno_id)))
     JOIN hoedanigheid_type ON (((hoedanigheid_type.hdh_id = observed_property_type.hoedanigheid_id) AND ((chemische_stof_type.code)::text IN ( SELECT vw_chemischestof_groepen."ChemischeStof_Code"
           FROM vw_chemischestof_groepen
          WHERE (vw_chemischestof_groepen."DB_ID" = 3127))) AND ((kwaliteitsoordeel_type.code)::text = '00'::text) AND ((observation.numeric_value IS NOT NULL) OR (observation.description IS NOT NULL)))))
     JOIN network_facility ON (((network_facility.emf_id = monitoring_object.mno_id) AND (network_facility.emn_id = ANY (ARRAY[111773, 111768, 20002])))))
  ORDER BY namespace_type_1.code, monitoring_object.inspire_id_local_id, chemische_stof_type.code, (to_char(observation.phenomenon_time_begin, 'YYYY-MM-DD'::text)) DESC;


--
-- Name: vw_toestand_achteruitgang_owl_dgrw; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_toestand_achteruitgang_owl_dgrw AS
 WITH ssg AS (
         SELECT substance_group_element.chs_id,
            substance_group_element.ssge_id,
                CASE
                    WHEN (substance_group_element.ssge_id = 2908) THEN 'SVS_STOF'::text
                    ELSE
                    CASE
                        WHEN (substance_group_element.ssge_id = 3149) THEN 'UBIQUITAIR'::text
                        ELSE 'NIET_UBIQUITAIR'::text
                    END
                END AS "CATEGORIE"
           FROM substance_group_element
          WHERE (substance_group_element.ssge_id = ANY (ARRAY[2908, 3150, 3149]))
        ), krw_kwal AS (
         SELECT ((wfd_qe_swtype.opt_id || '_'::text) || wfd_qe_swtype.swt_id) AS opt_swt,
            wfd_qe_swtype.name,
                CASE
                    WHEN (wfd_qe_swtype.part_of_opt_id = 4477) THEN 'CHEMT'::text
                    ELSE
                    CASE
                        WHEN (wfd_qe_swtype.part_of_opt_id = ANY (ARRAY[4480, 4514])) THEN 'FYSCHEM'::text
                        ELSE
                        CASE
                            WHEN (wfd_qe_swtype.part_of_opt_id = 4476) THEN 'BIOLT'::text
                            ELSE 'TOTAAL'::text
                        END
                    END
                END AS "CATEGORIE"
           FROM wfd_qe_swtype
          WHERE (wfd_qe_swtype.is_applicable = true)
        )
 SELECT swb_yearly_status.sys_id AS "DB_ID",
    hdo_swb.hdo_id AS "Waterlichaam_DB_ID",
    hdo_swb.inspire_id_local_id AS "Waterlichaam_identificatie",
    hdo_swb.wkrv_id AS "Waterlichaam_Versie",
    swb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap_db_id."DB_ID" AS "Waargenomen_Eigenschap_DB_ID",
        CASE
            WHEN (krw_kwal.name IS NOT NULL) THEN krw_kwal.name
            ELSE
            CASE
                WHEN (vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving" IS NOT NULL) THEN vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving"
                ELSE vw_waargenomen_eigenschap_db_id."Grootheid_Omschrijving"
            END
        END AS "Typering",
    classification_type.code AS "Oordeel_Code",
    classification_type.description AS "Oordeel",
    eu_monitoring_goal_type.eumg_id AS "Oordeel_Type_DB_ID",
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code",
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving",
    waarde_bepalings_methode_type.code AS "WaardebepalingsMethode_Code",
    waarde_bepalings_methode_type.omschrijving AS "WaardebepalingsMethode_Omschrijving",
    swb_yearly_status.comment_on_chemical_status AS "Toelichting_ChemischeStatus",
    swb_yearly_status.comment_on_ecological_status AS "Toelichting_EcologischeStatus",
    swb_yearly_status.motivation_4no_chemical_status AS "Motivatie_Geen_ChemischeStatus",
    swb_yearly_status.motivation_4no_ecological_status AS "Motivatie_Geen_EcologischeStatus",
    krw_kwal."CATEGORIE" AS "KWAL_ELEMENT",
    ssg."CATEGORIE" AS "STOF_CATEGORIE",
        CASE
            WHEN (wscm.scm_id IS NOT NULL) THEN 'A'::text
            ELSE NULL::text
        END AS "Motivatie_Achteruitgang"
   FROM ((((((((((swb_yearly_status
     LEFT JOIN hydro_object hdo_swb ON ((hdo_swb.hdo_id = swb_yearly_status.swb_id)))
     LEFT JOIN surface_water_body swb ON ((swb.swb_id = hdo_swb.hdo_id)))
     LEFT JOIN sw_chemical_substance_status ON ((sw_chemical_substance_status.sys_id = swb_yearly_status.sys_id)))
     LEFT JOIN classification_type ON ((sw_chemical_substance_status.swcs_id = classification_type.cls_id)))
     LEFT JOIN vw_waargenomen_eigenschap_db_id ON ((vw_waargenomen_eigenschap_db_id."DB_ID" = sw_chemical_substance_status.opt_id)))
     LEFT JOIN waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = sw_chemical_substance_status.wbpm_id)))
     LEFT JOIN eu_monitoring_goal_type ON ((eu_monitoring_goal_type.eumg_id = sw_chemical_substance_status.eumg_id)))
     LEFT JOIN ssg ON ((vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID" = ssg.chs_id)))
     LEFT JOIN krw_kwal ON ((((vw_waargenomen_eigenschap_db_id."DB_ID" || '_'::text) || swb.sw_target_typology_id) = krw_kwal.opt_swt)))
     LEFT JOIN wb_status_change_motivation wscm ON (((wscm.wbd_id = hdo_swb.hdo_id) AND (wscm.opt_id = vw_waargenomen_eigenschap_db_id."DB_ID") AND (wscm.year_status = swb_yearly_status.year_of_status) AND (wscm.scm_id = 4))))
  WHERE (((eu_monitoring_goal_type.code)::text = 'TT_OM'::text) AND (wscm.scm_id IS NOT NULL))
UNION ALL
 SELECT swb_yearly_status.sys_id AS "DB_ID",
    hdo_swb.hdo_id AS "Waterlichaam_DB_ID",
    hdo_swb.inspire_id_local_id AS "Waterlichaam_identificatie",
    hdo_swb.wkrv_id AS "Waterlichaam_Versie",
    swb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap_db_id."DB_ID" AS "Waargenomen_Eigenschap_DB_ID",
        CASE
            WHEN (krw_kwal.name IS NOT NULL) THEN krw_kwal.name
            ELSE
            CASE
                WHEN (vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving" IS NOT NULL) THEN vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving"
                ELSE vw_waargenomen_eigenschap_db_id."Grootheid_Omschrijving"
            END
        END AS "Typering",
    classification_type.code AS "Oordeel_Code",
    classification_type.description AS "Oordeel",
    eu_monitoring_goal_type.eumg_id AS "Oordeel_Type_DB_ID",
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code",
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving",
    waarde_bepalings_methode_type.code AS "WaardebepalingsMethode_Code",
    waarde_bepalings_methode_type.omschrijving AS "WaardebepalingsMethode_Omschrijving",
    swb_yearly_status.comment_on_chemical_status AS "Toelichting_ChemischeStatus",
    swb_yearly_status.comment_on_ecological_status AS "Toelichting_EcologischeStatus",
    swb_yearly_status.motivation_4no_chemical_status AS "Motivatie_Geen_ChemischeStatus",
    swb_yearly_status.motivation_4no_ecological_status AS "Motivatie_Geen_EcologischeStatus",
    krw_kwal."CATEGORIE" AS "KWAL_ELEMENT",
    NULL::text AS "STOF_CATEGORIE",
        CASE
            WHEN (wscm.scm_id IS NOT NULL) THEN 'A'::text
            ELSE NULL::text
        END AS "Motivatie_Achteruitgang"
   FROM (((((((((swb_yearly_status
     LEFT JOIN hydro_object hdo_swb ON ((hdo_swb.hdo_id = swb_yearly_status.swb_id)))
     LEFT JOIN surface_water_body swb ON ((swb.swb_id = hdo_swb.hdo_id)))
     LEFT JOIN sw_ecological_status ON ((sw_ecological_status.sys_id = swb_yearly_status.sys_id)))
     LEFT JOIN classification_type ON ((sw_ecological_status.swes_id = classification_type.cls_id)))
     LEFT JOIN vw_waargenomen_eigenschap_db_id ON ((vw_waargenomen_eigenschap_db_id."DB_ID" = sw_ecological_status.opt_id)))
     LEFT JOIN waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = sw_ecological_status.wbpm_id)))
     LEFT JOIN eu_monitoring_goal_type ON ((eu_monitoring_goal_type.eumg_id = sw_ecological_status.eumg_id)))
     LEFT JOIN krw_kwal ON ((((vw_waargenomen_eigenschap_db_id."DB_ID" || '_'::text) || swb.sw_target_typology_id) = krw_kwal.opt_swt)))
     LEFT JOIN wb_status_change_motivation wscm ON (((wscm.wbd_id = hdo_swb.hdo_id) AND (wscm.opt_id = vw_waargenomen_eigenschap_db_id."DB_ID") AND (wscm.year_status = swb_yearly_status.year_of_status) AND (wscm.scm_id = 4))))
  WHERE (((eu_monitoring_goal_type.code)::text = 'TT_OM'::text) AND (wscm.scm_id IS NOT NULL))
  ORDER BY 1, 6, 13 DESC, 11 DESC;


--
-- Name: vw_toetsresultaat_gwl; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_toetsresultaat_gwl AS
 SELECT observation.obs_id AS "DB_ID",
    hydro_object.hdo_id AS "Waterlichaam_DB_ID",
    hydro_object.inspire_id_local_id AS "Waterlichaam_Identificatie",
    gwb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap."DB_ID" AS "Waargenomen_Eigenschap_DB_ID",
    vw_waargenomen_eigenschap."Tpering_Code",
    vw_waargenomen_eigenschap."Typering_Omschrijving",
    vw_waargenomen_eigenschap."Grootheid_Code",
    vw_waargenomen_eigenschap."Grootheid_Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_code",
    vw_waargenomen_eigenschap."ChemischeStof_Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_CASnr",
    waarde_bewerkings_methode_type.code AS "Waardebewerkingsmethode_Code",
    waarde_bewerkings_methode_type.omschrijving AS "Waardebewerkingsmethode_Omschrijving",
    observation.result_time AS "Begintijd_Toetsresultaat",
    observation.phenomenon_time_end AS "Eindtijd_Toetsreeks",
    observation.phenomenon_time_begin AS "Begintijd_Toetsreeks",
    observation.numeric_value AS "Numerieke_waarde",
    observation.numeric_value_significance AS "Numerieke_waarde_Significantie",
    bepaling_grens_type.omschrijving AS "Bepalingsgrens",
    eenheid_type.code AS "Eenheid_Code",
    eenheid_type.omschrijving AS "Eenheid_Omschrijving",
    vw_waargenomen_eigenschap."Hoedanigheid_Code",
    vw_waargenomen_eigenschap."Hoedanigheid_Omschrijving",
    kwaliteitsoordeel_type.code AS "KwaliteitsOordeel_Code",
    kwaliteitsoordeel_type.description AS "KwaliteitsOordeel_Omschrijving",
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code",
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving",
    waarde_bepalings_methode_type.code AS "Waardebepalingsmethode_Code",
    waarde_bepalings_methode_type.omschrijving AS "Waardebepalingsmethode_Omschrijving"
   FROM (((((((((((observation
     LEFT JOIN vw_waargenomen_eigenschap ON ((vw_waargenomen_eigenschap."DB_ID" = observation.observed_property_type)))
     LEFT JOIN waarde_bewerkings_methode_type ON ((waarde_bewerkings_methode_type.wbm_id = observation.value_processing_method_id)))
     LEFT JOIN eenheid_type ON ((eenheid_type.eenh_id = observation.numeric_value_uom_id)))
     LEFT JOIN bepaling_grens_type ON ((bepaling_grens_type.bpg_id = observation.limit_symbol_id)))
     LEFT JOIN kwaliteitsoordeel_type ON ((kwaliteitsoordeel_type.kwod_id = observation.quality_indicator_id)))
     LEFT JOIN observation_process ON ((observation_process.obp_id = observation.result_nature_id)))
     LEFT JOIN waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = observation_process.wbpm_id)))
     LEFT JOIN gw_testing_conclusion ON ((gw_testing_conclusion.gtcc_id = observation.obs_id)))
     LEFT JOIN gwb_yearly_status ON ((gwb_yearly_status.gys_id = gw_testing_conclusion.gys_id)))
     LEFT JOIN hydro_object ON ((hydro_object.hdo_id = gwb_yearly_status.gwb_id)))
     LEFT JOIN eu_monitoring_goal_type ON ((eu_monitoring_goal_type.eumg_id = gw_testing_conclusion.eumg_id)))
  WHERE (gwb_yearly_status.year_of_status IS NOT NULL)
  ORDER BY hydro_object.hdo_id, vw_waargenomen_eigenschap."Tpering_Code", vw_waargenomen_eigenschap."Grootheid_Code", vw_waargenomen_eigenschap."ChemischeStof_code", gwb_yearly_status.year_of_status;


--
-- Name: vw_toetsresultaat_owm_aquo; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_toetsresultaat_owm_aquo AS
 SELECT DISTINCT ON (vw_toetsresultaat_owm."Waterlichaam_Identificatie", vw_toetsresultaat_owm."Tpering_Code", vw_toetsresultaat_owm."Grootheid_Code", vw_toetsresultaat_owm."ChemischeStof_code", vw_toetsresultaat_owm."Rapportagejaar", vw_toetsresultaat_owm."Waterlichaam_Versie", vw_toetsresultaat_owm."Begintijd_Toetsresultaat") vw_toetsresultaat_owm."Waterlichaam_Identificatie" AS "Waterlichaam.Identificatie",
    vw_toetsresultaat_owm."Rapportagejaar",
    vw_toetsresultaat_owm."Waterlichaam_Versie" AS "Versie",
    ('NL'::text || (nt.code)::text) AS "Namespace",
    nt.descrption AS "waterbeheerder.Omschrijving",
    vw_toetsresultaat_owm."Tpering_Code" AS "Typering.Code",
    vw_toetsresultaat_owm."Typering_Omschrijving" AS "Typering.Omschrijving",
    vw_toetsresultaat_owm."Grootheid_Code" AS "Grootheid.Code",
    vw_toetsresultaat_owm."Grootheid_Omschrijving" AS "Grootheid.Omschrijving",
    vw_toetsresultaat_owm."ChemischeStof_code" AS "ChemischeStof.Code",
    vw_toetsresultaat_owm."ChemischeStof_Omschrijving" AS "ChemischeStof.Omschrijving",
    vw_toetsresultaat_owm."ChemischeStof_CASnr" AS "ChemischeStof.CASnr",
    vw_toetsresultaat_owm."Waardebewerkingsmethode_Code" AS "Waterbewerkingsmethode.Code",
    vw_toetsresultaat_owm."Waardebewerkingsmethode_Omschrijving" AS "Waardebewerkingsmethode.Omschrijving",
    (vw_toetsresultaat_owm."Begintijd_Toetsresultaat")::date AS "Toetsresultaat.Begindatum",
    (vw_toetsresultaat_owm."Begintijd_Toetsresultaat")::time without time zone AS "Toetsresultaat.Begintijd",
    (vw_toetsresultaat_owm."Begintijd_Toetsreeks")::date AS "Toetsreeks.Begindatum",
    (vw_toetsresultaat_owm."Begintijd_Toetsreeks")::time without time zone AS "Toetsreeks.Begintijd",
    (vw_toetsresultaat_owm."Eindtijd_Toetsreeks")::date AS "Toetsreeks.Einddatum",
    (vw_toetsresultaat_owm."Eindtijd_Toetsreeks")::time without time zone AS "Toetsreeks.Eindtijd",
    vw_toetsresultaat_owm."Numerieke_waarde" AS "NumeriekeWaarde",
    vw_toetsresultaat_owm."Numerieke_waarde_Significantie" AS "NumeriekeWaarde.Significantie",
    vw_toetsresultaat_owm."Bepalingsgrens" AS "Limietsymbool",
    vw_toetsresultaat_owm."Eenheid_Code" AS "Eenheid.Code",
    vw_toetsresultaat_owm."Eenheid_Omschrijving" AS "Eenheid.Omschrijving",
    vw_toetsresultaat_owm."Hoedanigheid_Code" AS "Hoedanigheid.Code",
    vw_toetsresultaat_owm."Hoedanigheid_Omschrijving" AS "Hoedanigheid.Omschrijving",
    vw_toetsresultaat_owm."KwaliteitsOordeel_Code" AS "Kwaliteitsoordeel.Code",
    vw_toetsresultaat_owm."KwaliteitsOordeel_Omschrijving" AS "KwaliteitsOordeel.Omschrijving",
    vw_toetsresultaat_owm."Oordeel_Type_Code" AS "Oordeelsoort.Code",
        CASE vw_toetsresultaat_owm."Oordeel_Type_Code"
            WHEN 'TT_OM'::text THEN '1'::text
            WHEN 'TT'::text THEN '2'::text
            WHEN 'OM'::text THEN '3'::text
            ELSE NULL::text
        END AS volgordebepaling,
    vw_toetsresultaat_owm."Oordeel_Type_Omschrijving" AS "Oordeelsoort.Omschrijving",
    vw_toetsresultaat_owm."Waardebepalingsmethode_Code" AS "Waardebepalingsmethode.Code",
    vw_toetsresultaat_owm."Waardebepalingsmethode_Omschrijving" AS "Waardebepalingsmethode.Omschrijving"
   FROM (((vw_toetsresultaat_owm
     JOIN hydro_object hdo ON ((hdo.hdo_id = vw_toetsresultaat_owm."Waterlichaam_DB_ID")))
     JOIN competentauthority_namespacetype con ON ((con.ns_id = hdo.inspire_id_namespace)))
     JOIN namespace_type nt ON ((nt.ns_id = con.ns_id)))
  WHERE ((vw_toetsresultaat_owm."Numerieke_waarde" > (0)::double precision) AND ((vw_toetsresultaat_owm."KwaliteitsOordeel_Code")::text < '50'::text))
  ORDER BY vw_toetsresultaat_owm."Waterlichaam_Identificatie", vw_toetsresultaat_owm."Tpering_Code", vw_toetsresultaat_owm."Grootheid_Code", vw_toetsresultaat_owm."ChemischeStof_code", vw_toetsresultaat_owm."Rapportagejaar" DESC;


--
-- Name: vw_trends_gwl; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_trends_gwl AS
 SELECT pollutant_trend.pltr_id AS "DB_ID",
    hdo_gwb.hdo_id AS "Grondwaterlichaam_DB_ID",
    hdo_gwb.inspire_id_local_id AS "Grondwaterlichaam_Identificatie",
    hdon_gwb.name AS "Grondwaterlichaam_Omschrijving",
    gwb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap."DB_ID" AS "Waargenomen_Eigenschap_DB_ID",
    vw_waargenomen_eigenschap."Grootheid_Code",
    vw_waargenomen_eigenschap."Grootheid_Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_code",
    vw_waargenomen_eigenschap."ChemischeStof_Omschrijving",
    vw_waargenomen_eigenschap."Hoedanigheid_Code",
    vw_waargenomen_eigenschap."Hoedanigheid_Omschrijving",
    classification_type.description AS "TypeTrend",
    pollutant_trend.comment AS "Opmerking"
   FROM (((((pollutant_trend
     LEFT JOIN gwb_yearly_status ON ((gwb_yearly_status.gys_id = pollutant_trend.gwys_id)))
     LEFT JOIN hydro_object hdo_gwb ON ((hdo_gwb.hdo_id = gwb_yearly_status.gwb_id)))
     LEFT JOIN hdo_names hdon_gwb ON ((hdo_gwb.hdo_id = hdon_gwb.hdo_id)))
     LEFT JOIN vw_waargenomen_eigenschap ON ((vw_waargenomen_eigenschap."DB_ID" = pollutant_trend.opt_id)))
     LEFT JOIN classification_type ON ((pollutant_trend.cls_id = classification_type.cls_id)));


--
-- Name: vw_voortgang_maatregelen_for_download; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_voortgang_maatregelen_for_download AS
 SELECT sub3.mtm_id,
    sub3.periodeid,
    sub3.waterbeheerderid,
    sub3.sgbpperiodeomschrijving,
    sub3.hoortbijmaatregel,
    sub3.naam,
    sub3.krwstroomgebieddistrictcode,
    sub3.waterbeheerdercode,
    sub3.rapportagejaar,
    sub3.omvangnumeriekewaarde,
    sub3.motivering,
    sub3.maatregelstatusomschrijving,
    sub3.omvangeenheidcode,
    sub3.mst_id
   FROM ( SELECT sub1.mtm_id,
            sub1.periodeid,
            sub1.waterbeheerderid,
            sub1.sgbpperiodeomschrijving,
            sub1.hoortbijmaatregel,
            sub1.naam,
            sub1.krwstroomgebieddistrictcode,
            sub1.waterbeheerdercode,
            sub1.rapportagejaar,
            sub1.omvangnumeriekewaarde,
            sub1.motivering,
            measure_status_type.omschrijving AS maatregelstatusomschrijving,
            sub1.omvangeenheidcode,
            measure_status_type.mst_id
           FROM (( SELECT DISTINCT monitored_measure.mtm_id,
                    rpp.rpp_id AS periodeid,
                    competent_authority.ore_id AS waterbeheerderid,
                    rpp.description AS sgbpperiodeomschrijving,
                    supp_add_measure_type.local_id AS hoortbijmaatregel,
                    supp_add_measure_type.measure_name AS naam,
                    stroomgebieddistrict.inspire_id_local_id AS krwstroomgebieddistrictcode,
                    ('NL'::text || (wb_nst.code)::text) AS waterbeheerdercode,
                    measure_progress.year AS rapportagejaar,
                    measure_progress.amount_value AS omvangnumeriekewaarde,
                    measure_progress.reason_substanstial_delay AS motivering,
                    measure_status_type_1.omschrijving AS maatregelstatusomschrijving,
                    eenheid_type.code AS omvangeenheidcode,
                    measure_status_type_1.mst_id
                   FROM ((((((((((((monitored_measure
                     LEFT JOIN measure_progress ON ((monitored_measure.mtm_id = measure_progress.mtm_id)))
                     JOIN supp_add_measure_type ON ((supp_add_measure_type.smnl_id = monitored_measure.mtm_id)))
                     JOIN namespace_type wb_nst ON ((wb_nst.ns_id = supp_add_measure_type.namespace_id)))
                     JOIN competentauthority_namespacetype ON ((competentauthority_namespacetype.ns_id = wb_nst.ns_id)))
                     JOIN competent_authority ON ((competent_authority.ore_id = competentauthority_namespacetype.ore_id)))
                     LEFT JOIN measure_status_type measure_status_type_1 ON ((measure_status_type_1.mst_id = measure_progress.mst_id)))
                     JOIN sw_supp_measure_eu_type smeu ON ((smeu.smeu_id = supp_add_measure_type.smeu_id)))
                     JOIN eenheid_type ON ((eenheid_type.eenh_id = smeu.eenh_id)))
                     JOIN river_basin_management_plan ON ((river_basin_management_plan.rbmp_id = supp_add_measure_type.rbmp_id)))
                     JOIN hydro_object stroomgebieddistrict ON ((stroomgebieddistrict.hdo_id = river_basin_management_plan.rbd_id)))
                     JOIN report_version rv2 ON ((rv2.wkrv_id = river_basin_management_plan.wkrv_id)))
                     JOIN reporting_period rpp ON (((rpp.rpp_id = rv2.rpp_id) AND (rv2.begin_lifespan_version <= now()) AND ((rv2.end_lifespan_version > now()) OR (rv2.end_lifespan_version IS NULL)))))) sub1
             CROSS JOIN measure_status_type)
          WHERE (sub1.mst_id IS NULL)
        UNION
         SELECT sub2.mtm_id,
            sub2.periodeid,
            sub2.waterbeheerderid,
            sub2.sgbpperiodeomschrijving,
            sub2.hoortbijmaatregel,
            sub2.naam,
            sub2.krwstroomgebieddistrictcode,
            sub2.waterbeheerdercode,
            sub2.rapportagejaar,
            sub2.omvangnumeriekewaarde,
            sub2.motivering,
            sub2.maatregelstatusomschrijving,
            sub2.omvangeenheidcode,
            sub2.mst_id
           FROM ( SELECT DISTINCT monitored_measure.mtm_id,
                    rpp.rpp_id AS periodeid,
                    competent_authority.ore_id AS waterbeheerderid,
                    rpp.description AS sgbpperiodeomschrijving,
                    supp_add_measure_type.local_id AS hoortbijmaatregel,
                    supp_add_measure_type.measure_name AS naam,
                    stroomgebieddistrict.inspire_id_local_id AS krwstroomgebieddistrictcode,
                    ('NL'::text || (wb_nst.code)::text) AS waterbeheerdercode,
                    measure_progress.year AS rapportagejaar,
                    measure_progress.amount_value AS omvangnumeriekewaarde,
                    measure_progress.reason_substanstial_delay AS motivering,
                    measure_status_type.omschrijving AS maatregelstatusomschrijving,
                    eenheid_type.code AS omvangeenheidcode,
                    measure_status_type.mst_id
                   FROM ((((((((((((monitored_measure
                     LEFT JOIN measure_progress ON ((monitored_measure.mtm_id = measure_progress.mtm_id)))
                     JOIN supp_add_measure_type ON ((supp_add_measure_type.smnl_id = monitored_measure.mtm_id)))
                     JOIN namespace_type wb_nst ON ((wb_nst.ns_id = supp_add_measure_type.namespace_id)))
                     JOIN competentauthority_namespacetype ON ((competentauthority_namespacetype.ns_id = wb_nst.ns_id)))
                     JOIN competent_authority ON ((competent_authority.ore_id = competentauthority_namespacetype.ore_id)))
                     LEFT JOIN measure_status_type ON ((measure_status_type.mst_id = measure_progress.mst_id)))
                     JOIN sw_supp_measure_eu_type smeu ON ((smeu.smeu_id = supp_add_measure_type.smeu_id)))
                     JOIN eenheid_type ON ((eenheid_type.eenh_id = smeu.eenh_id)))
                     JOIN river_basin_management_plan ON ((river_basin_management_plan.rbmp_id = supp_add_measure_type.rbmp_id)))
                     JOIN hydro_object stroomgebieddistrict ON ((stroomgebieddistrict.hdo_id = river_basin_management_plan.rbd_id)))
                     JOIN report_version rv2 ON ((rv2.wkrv_id = river_basin_management_plan.wkrv_id)))
                     JOIN reporting_period rpp ON (((rpp.rpp_id = rv2.rpp_id) AND (rv2.begin_lifespan_version <= now()) AND ((rv2.end_lifespan_version > now()) OR (rv2.end_lifespan_version IS NULL)))))) sub2
          WHERE (sub2.mst_id IS NOT NULL)) sub3
  ORDER BY sub3.periodeid, sub3.waterbeheerderid, sub3.rapportagejaar, sub3.hoortbijmaatregel, sub3.mst_id;


--
-- Name: vw_waargenomen_eigenschap_aquo; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_waargenomen_eigenschap_aquo AS
 SELECT observed_property_type.opt_id AS "WaargenomenEigenschap.ID",
    typering_type.code AS "Typering.Code",
    typering_type.omschrijving AS "Typering.Omschrijving",
    grootheid_type.code AS "Grootheid.Code",
    grootheid_type.omschrijving AS "Grootheid.Omschrijving",
    object_type.code AS "Object.Code",
    object_type.omschrijving AS "Object.Omschrijving",
        CASE
            WHEN (chemische_stof_type.code IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (object_type.omschrijving IS NOT NULL) THEN 'Object'::text
            WHEN (taxon_type.code IS NOT NULL) THEN 'Taxon'::text
            ELSE NULL::text
        END AS "Parameter.Groep",
        CASE
            WHEN (chemische_stof_type.code IS NOT NULL) THEN chemische_stof_type.code
            WHEN (object_type.omschrijving IS NOT NULL) THEN object_type.code
            WHEN (taxon_type.code IS NOT NULL) THEN taxon_type.code
            ELSE NULL::character varying
        END AS "Parameter.Code",
        CASE
            WHEN (chemische_stof_type.code IS NOT NULL) THEN chemische_stof_type.naam
            WHEN (object_type.omschrijving IS NOT NULL) THEN object_type.omschrijving
            WHEN (taxon_type.code IS NOT NULL) THEN taxon_type.auteur
            ELSE NULL::character varying
        END AS "Parameter.Omschrijving",
    chemische_stof_type.cas_nr AS "ChemischeStof.CASnr",
    hoedanigheid_type.code AS "Hoedanigheid.Code",
    hoedanigheid_type.omschrijving AS "Hoedanigheid.Omschrijving"
   FROM (((((((observed_property_type
     LEFT JOIN (object_type
     JOIN object_group_element ON ((object_type.obj_id = object_group_element.obj_id))) ON ((object_group_element.ojg_id = observed_property_type.object_group_id)))
     LEFT JOIN hoedanigheid_type ON ((hoedanigheid_type.hdh_id = observed_property_type.hoedanigheid_id)))
     LEFT JOIN grootheid_type ON ((grootheid_type.grh_id = observed_property_type.quantity_id)))
     LEFT JOIN (taxon_type
     JOIN taxa_group_element ON ((taxon_type.txn_id = taxa_group_element.txn_id))) ON ((taxa_group_element.txg_id = observed_property_type.taxa_group_id)))
     LEFT JOIN (chemische_stof_type
     JOIN substance_group_element ON ((chemische_stof_type.chs_id = substance_group_element.chs_id))) ON ((substance_group_element.ssge_id = observed_property_type.substance_group_id)))
     LEFT JOIN typering_type ON ((typering_type.typ_id = observed_property_type.indicator_id)))
     LEFT JOIN classification_group_type ON ((classification_group_type.clsg_id = observed_property_type.classification_group_id)))
  WHERE ((grootheid_type.code IS NOT NULL) OR (typering_type.code IS NOT NULL))
  ORDER BY typering_type.code,
        CASE
            WHEN (chemische_stof_type.code IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (object_type.omschrijving IS NOT NULL) THEN 'Object'::text
            WHEN (taxon_type.code IS NOT NULL) THEN 'Taxon'::text
            ELSE NULL::text
        END DESC, grootheid_type.code,
        CASE
            WHEN (chemische_stof_type.code IS NOT NULL) THEN chemische_stof_type.code
            WHEN (object_type.omschrijving IS NOT NULL) THEN object_type.code
            WHEN (taxon_type.code IS NOT NULL) THEN taxon_type.code
            ELSE NULL::character varying
        END;


--
-- Name: vw_waargenomen_eigenschap_db_id_sander; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_waargenomen_eigenschap_db_id_sander AS
 SELECT observed_property_type.opt_id AS "DB_ID",
    typering_type.typ_id AS "Typering_DB_ID",
    typering_type.code AS "Tpering_Code",
    typering_type.omschrijving AS "Typering_Omschrijving",
    grootheid_type.grh_id AS "Grootheid_DB_ID",
    grootheid_type.code AS "Grootheid_Code",
    grootheid_type.omschrijving AS "Grootheid_Omschrijving",
    grootheid_type.id AS "Grootheid_ext_id",
    chemische_stof_type.chs_id AS "ChemischeStof_DB_ID",
    chemische_stof_type.code AS "ChemischeStof_code",
    chemische_stof_type.naam AS "ChemischeStof_Omschrijving",
    chemische_stof_type.cas_nr AS "ChemischeStof_CASnr",
    classification_group_type.code AS "ClassificatieType_Code",
    classification_group_type.description AS "ClassificatieType_Omschrijving",
    object_type.obj_id AS "Object_DB_ID",
    object_type.omschrijving AS "Object_Omschrijving",
    object_type.code AS "Object_Code",
    txg.txg_id AS "taxa_group_DB_ID",
    txg.group_type AS taxa_group_type,
    txg.name AS taxa_group_name,
    taxon_type.txn_id AS "Taxon_DB_ID",
    taxon_type.code AS "Taxon_Naam",
    taxon_type.auteur AS "Taxon_Auteur",
    hoedanigheid_type.hdh_id AS "Hoedanigheid_DB_ID",
    hoedanigheid_type.code AS "Hoedanigheid_Code",
    hoedanigheid_type.omschrijving AS "Hoedanigheid_Omschrijving",
    hoedanigheid_type.id AS "Hoedanigheid_ext_id",
    observed_property_type.observed_property_type_class AS "Parameter_Type",
    observed_property_type.result_type AS "Parameter_Resultaat",
    observed_property_type.remarks AS "Opmerkingen"
   FROM ((((((((observed_property_type
     LEFT JOIN (object_type
     JOIN object_group_element ON ((object_type.obj_id = object_group_element.obj_id))) ON ((object_group_element.ojg_id = observed_property_type.object_group_id)))
     LEFT JOIN hoedanigheid_type ON ((hoedanigheid_type.hdh_id = observed_property_type.hoedanigheid_id)))
     LEFT JOIN grootheid_type ON ((grootheid_type.grh_id = observed_property_type.quantity_id)))
     LEFT JOIN taxa_group txg ON ((txg.txg_id = observed_property_type.taxa_group_id)))
     LEFT JOIN (taxon_type
     JOIN taxa_group_element ON ((taxon_type.txn_id = taxa_group_element.txn_id))) ON ((taxa_group_element.txg_id = observed_property_type.taxa_group_id)))
     LEFT JOIN (chemische_stof_type
     JOIN substance_group_element ON ((chemische_stof_type.chs_id = substance_group_element.chs_id))) ON ((substance_group_element.ssge_id = observed_property_type.substance_group_id)))
     LEFT JOIN typering_type ON ((typering_type.typ_id = observed_property_type.indicator_id)))
     LEFT JOIN classification_group_type ON ((classification_group_type.clsg_id = observed_property_type.classification_group_id)))
  ORDER BY typering_type.code, grootheid_type.code, chemische_stof_type.code, object_type.code, taxon_type.code, hoedanigheid_type.code;


--
-- Name: vw_waargenomen_eigenschap_db_id_work-around; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW "vw_waargenomen_eigenschap_db_id_work-around" AS
 SELECT DISTINCT ON (chemische_stof_type.chs_id) 'incomplete lijst' AS "WORK-AROUND",
    observed_property_type.opt_id AS "DB_ID",
    typering_type.typ_id AS "Typering_DB_ID",
    typering_type.code AS "Tpering_Code",
    typering_type.omschrijving AS "Typering_Omschrijving",
    grootheid_type.grh_id AS "Grootheid_DB_ID",
    grootheid_type.code AS "Grootheid_Code",
    grootheid_type.omschrijving AS "Grootheid_Omschrijving",
    chemische_stof_type.chs_id AS "ChemischeStof_DB_ID",
    chemische_stof_type.code AS "ChemischeStof_code",
    chemische_stof_type.naam AS "ChemischeStof_Omschrijving",
    chemische_stof_type.cas_nr AS "ChemischeStof_CASnr",
    classification_group_type.code AS "ClassificatieType_Code",
    classification_group_type.description AS "ClassificatieType_Omschrijving",
    object_type.obj_id AS "Object_DB_ID",
    object_type.omschrijving AS "Object_Omschrijving",
    object_type.code AS "Object_Code",
    taxon_type.txn_id AS "Taxon_DB_ID",
    taxon_type.code AS "Taxon_Naam",
    taxon_type.auteur AS "Taxon_Auteur",
    hoedanigheid_type.hdh_id AS "Hoedanigheid_DB_ID",
    hoedanigheid_type.code AS "Hoedanigheid_Code",
    hoedanigheid_type.omschrijving AS "Hoedanigheid_Omschrijving",
    observed_property_type.observed_property_type_class AS "Parameter_Type",
    observed_property_type.result_type AS "Parameter_Resultaat",
    observed_property_type.remarks AS "Opmerkingen"
   FROM (((((((observed_property_type
     LEFT JOIN (object_type
     JOIN object_group_element ON ((object_type.obj_id = object_group_element.obj_id))) ON ((object_group_element.ojg_id = observed_property_type.object_group_id)))
     LEFT JOIN hoedanigheid_type ON ((hoedanigheid_type.hdh_id = observed_property_type.hoedanigheid_id)))
     LEFT JOIN grootheid_type ON ((grootheid_type.grh_id = observed_property_type.quantity_id)))
     LEFT JOIN (taxon_type
     JOIN taxa_group_element ON ((taxon_type.txn_id = taxa_group_element.txn_id))) ON ((taxa_group_element.txg_id = observed_property_type.taxa_group_id)))
     LEFT JOIN (chemische_stof_type
     JOIN substance_group_element ON ((chemische_stof_type.chs_id = substance_group_element.chs_id))) ON ((substance_group_element.ssge_id = observed_property_type.substance_group_id)))
     LEFT JOIN typering_type ON ((typering_type.typ_id = observed_property_type.indicator_id)))
     LEFT JOIN classification_group_type ON ((classification_group_type.clsg_id = observed_property_type.classification_group_id)))
  ORDER BY chemische_stof_type.chs_id, typering_type.code, grootheid_type.code, chemische_stof_type.code, object_type.code, taxon_type.code, hoedanigheid_type.code;


--
-- Name: vw_waterbeheerder_deelstroomgebied; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_waterbeheerder_deelstroomgebied AS
 SELECT DISTINCT ON (vw_competent_authority.waterbeheerder_db_id, vw_sg_en_dsg_districten."Identificatie", hydro_object.wkrv_id) vw_competent_authority.waterbeheerder_db_id AS waterbeheerder_code,
    vw_competent_authority."Omschrijving" AS waterbeheerder_omschrijving,
    vw_sg_en_dsg_districten."Identificatie" AS stroomgebieddistrict,
    hydro_object.wkrv_id AS versie
   FROM (((((hydro_object
     LEFT JOIN management_regulation_or_restriction_zone ON ((management_regulation_or_restriction_zone.mrz_id = hydro_object.hdo_id)))
     LEFT JOIN competentauthority_namespacetype ON ((competentauthority_namespacetype.ns_id = hydro_object.inspire_id_namespace)))
     LEFT JOIN vw_competent_authority ON ((vw_competent_authority."DB_ID" = competentauthority_namespacetype.ore_id)))
     JOIN water_body ON ((water_body.wbd_id = hydro_object.hdo_id)))
     LEFT JOIN vw_sg_en_dsg_districten ON ((vw_sg_en_dsg_districten."DB_ID" = water_body.rbd_id)))
  ORDER BY vw_competent_authority.waterbeheerder_db_id, vw_sg_en_dsg_districten."Identificatie", hydro_object.wkrv_id;


--
-- Name: vw_waterdelen_g; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_waterdelen_g AS
 SELECT hydro_object.hdo_id AS "DB_ID",
    hydro_object.inspire_id_local_id AS "Identificatie",
    hydro_object.inspire_id_namespace AS "Waterbeheerder_DB_ID",
    hdo_names.name AS "Naam",
    watercourse.length AS "Lengte",
    watercourse.width_upper AS "Breedte_ondergrens",
    watercourse.width_lower AS "Breedte_bovengrens",
    watercourse."geometryLine",
    watercourse."geometrySurface",
    hydro_object.wkrv_id AS "Versie",
    hydro_object.related_hdo_id AS "Gerelateerd_HydroObject"
   FROM ((watercourse
     JOIN hydro_object ON ((hydro_object.hdo_id = watercourse.wtc_id)))
     LEFT JOIN hdo_names ON ((hdo_names.hdo_id = hydro_object.hdo_id)))
  ORDER BY hydro_object.related_hdo_id;


--
-- Name: vw_waterlichaam_organisatie_relatie; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_waterlichaam_organisatie_relatie AS
 SELECT relation_ore_wbd.ore_wbd_id AS "DB_ID",
    vw_waterlichamen.db_id AS "Waterlichaam_DB_ID",
    vw_waterlichamen.identificatie AS "Waterlichaam.Identificatie",
    vw_waterlichamen.waterbeheerder_code AS "Bron_WaterbeheerderCode",
    vw_waterlichamen.waterbeheerder_naam AS "Bron_WaterbeheerderNaam",
    vw_waterlichamen.omschrijving AS "Waterlichaam_Naam",
    vw_waterlichamen.stroomgebieddistrict AS "StroomgebiedDistrict",
    organisatorische_eenheid.ore_id AS "Organisatie_DB_ID",
    organisatorische_eenheid.organisatie_identificatie AS "Organisatie_Identificatie",
    organisatorische_eenheid.datum_ontstaan AS "Datum_Onstaan",
    organisatorische_eenheid.datum_opheffing AS "Datum_Opheffing",
    organisatorische_eenheid.naam AS "Organisatie_Naam",
    organisatorische_eenheid.naam_verkort AS "Organisatie_KorteNaam",
    organisation_category_type.ocat_id AS "OrganisatieType_DB_ID",
    organisation_category_type.omschrijving AS "OrganisatieType",
    vw_waterlichamen.versie AS "Versie"
   FROM (((relation_ore_wbd
     LEFT JOIN organisatorische_eenheid ON ((organisatorische_eenheid.ore_id = relation_ore_wbd.ore_id)))
     LEFT JOIN vw_waterlichamen ON ((vw_waterlichamen.db_id = relation_ore_wbd.wbd_id)))
     LEFT JOIN organisation_category_type ON ((organisation_category_type.ocat_id = organisatorische_eenheid.organisation_category_id)))
  ORDER BY vw_waterlichamen.identificatie, organisation_category_type.ocat_id;


--
-- Name: vw_waterlichaam_prognose_kwaliteitselement; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_waterlichaam_prognose_kwaliteitselement AS
 SELECT template.wbd_id,
    vw_krw_kwaliteitselement."Watertype_Code",
    vw_krw_kwaliteitselement."Waargenomen_Eigenschap_DB_ID",
    (concat_ws(' '::text, vw_krw_kwaliteitselement."Typering_Omschrijving", vw_krw_kwaliteitselement."Grootheid_Omschrijving", vw_krw_kwaliteitselement."ChemischeStof_Omschrijving"))::character varying(255) AS "Kwaliteitselement_Naam",
    ((lower((classification_type.description)::text) = ANY (ARRAY['matig'::text, 'ontoereikend'::text, 'slecht'::text, 'voldoet niet'::text])) OR (wb_prognosis.swb_id IS NULL)) AS nietgoed
   FROM (((((( SELECT DISTINCT surface_water_body_1.swb_id AS wbd_id,
            vw_krw_kwaliteitselement_1."Waargenomen_Eigenschap_DB_ID" AS opt_id
           FROM (surface_water_body surface_water_body_1
             CROSS JOIN vw_krw_kwaliteitselement vw_krw_kwaliteitselement_1)) template
     JOIN surface_water_body ON ((surface_water_body.swb_id = template.wbd_id)))
     JOIN sw_typology_type ON ((sw_typology_type.styp_id = surface_water_body.sw_target_typology_id)))
     JOIN vw_krw_kwaliteitselement ON (((vw_krw_kwaliteitselement."Waargenomen_Eigenschap_DB_ID" = template.opt_id) AND ((vw_krw_kwaliteitselement."Watertype_Code")::text = (sw_typology_type.code)::text))))
     JOIN wb_prognosis ON (((wb_prognosis.swb_id = surface_water_body.swb_id) AND (wb_prognosis.year = 2021) AND (wb_prognosis.opt_id = template.opt_id))))
     JOIN classification_type ON ((classification_type.cls_id = wb_prognosis.cls_id)))
  WHERE (vw_krw_kwaliteitselement."Is_van_toepassing" AND vw_krw_kwaliteitselement."Is_artikel_4" AND (vw_krw_kwaliteitselement."Waargenomen_Eigenschap_DB_ID" <> ALL (ARRAY[4476, 4479, 4480, 4520])))
UNION ALL
 SELECT template.wbd_id,
    NULL::character varying AS "Watertype_Code",
    vw_krw_kwaliteitselement_gw."Waargenomen_Eigenschap_DB_ID",
    vw_krw_kwaliteitselement_gw."Kwaliteitselement_Naam",
    ((lower((classification_type.description)::text) = ANY (ARRAY['matig'::text, 'ontoereikend'::text, 'slecht'::text, 'voldoet niet'::text])) OR (wb_prognosis.swb_id IS NULL)) AS nietgoed
   FROM ((((( SELECT DISTINCT ground_water_body_1.gwb_id AS wbd_id,
            vw_krw_kwaliteitselement_gw_1."Waargenomen_Eigenschap_DB_ID" AS opt_id
           FROM (vw_krw_kwaliteitselement_gw vw_krw_kwaliteitselement_gw_1
             CROSS JOIN ground_water_body ground_water_body_1)
          WHERE (vw_krw_kwaliteitselement_gw_1."Is_van_toepassing" AND vw_krw_kwaliteitselement_gw_1."Is_artikel_4")) template
     JOIN ground_water_body ON ((ground_water_body.gwb_id = template.wbd_id)))
     JOIN vw_krw_kwaliteitselement_gw ON ((vw_krw_kwaliteitselement_gw."Waargenomen_Eigenschap_DB_ID" = template.opt_id)))
     JOIN wb_prognosis ON (((wb_prognosis.swb_id = ground_water_body.gwb_id) AND (wb_prognosis.year = 2021) AND (wb_prognosis.opt_id = template.opt_id))))
     JOIN classification_type ON ((classification_type.cls_id = wb_prognosis.cls_id)));


--
-- Name: waterbody_protected_area_intersection; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE waterbody_protected_area_intersection (
    wbpa_id integer NOT NULL,
    wbd_id integer NOT NULL,
    wpa_id integer NOT NULL,
    relation_type integer,
    geometry geometry(Geometry,28992),
    last_change_time timestamp without time zone NOT NULL
);


--
-- Name: vw_waterlichamen_beschermdgebied_relatie; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_waterlichamen_beschermdgebied_relatie AS
 SELECT waterbody_protected_area_intersection.wbpa_id AS "DB_ID",
    vw_waterlichamen.db_id AS "Waterlichaam_DB_ID",
    vw_waterlichamen.identificatie AS "Waterlichaam_Identificatie",
    vw_waterlichamen.omschrijving AS "Waterlichaam_Omschrijving",
    vw_waterlichamen.stroomgebieddistrict AS "Waterlichaam_Stroomgebieddistrict",
    vw_waterlichamen.versie AS "Waterlichaam_Versie",
    vw_beschermdegebieden."DB_ID" AS "BeschermdGebied_DB_ID",
    vw_beschermdegebieden."Identificatie" AS "BeschermdGebied_Identificatie",
    vw_beschermdegebieden."Omschrijving" AS "BeschermdGebied_Omschrijving",
    vw_beschermdegebieden."Soort_Beschermd_Gebied",
    vw_beschermdegebieden."Soort_Beschermd_Gebied_Code",
    vw_beschermdegebieden."Versie_ID" AS "BeschermdGebied_Versie",
    waterbody_protected_area_intersection.geometry,
    relation_type.rlt_id AS "RelatieType_DB_ID",
    relation_type.description
   FROM (((waterbody_protected_area_intersection
     LEFT JOIN vw_waterlichamen ON ((vw_waterlichamen.db_id = waterbody_protected_area_intersection.wbd_id)))
     LEFT JOIN vw_beschermdegebieden ON ((vw_beschermdegebieden."DB_ID" = waterbody_protected_area_intersection.wpa_id)))
     JOIN relation_type ON ((relation_type.rlt_id = waterbody_protected_area_intersection.relation_type)))
  ORDER BY vw_waterlichamen.identificatie, vw_beschermdegebieden."Identificatie";


--
-- Name: vw_waterlichamen_g; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_waterlichamen_g AS
 SELECT hydro_object.hdo_id AS db_id,
    hydro_object.inspire_id_local_id AS identificatie,
    hydro_object.inspire_id_namespace AS waterbeheerder_code,
    water_body.is_article_7 AS drinkwateronttrekking_jn,
    hdo_names.name AS omschrijving,
    management_regulation_or_restriction_zone.geometry,
    hdo_rbd.inspire_id_local_id AS stroomgebieddistrict,
    hydro_object.wkrv_id AS versie
   FROM (((((((hydro_object
     LEFT JOIN hdo_names ON ((hdo_names.hdo_id = hydro_object.hdo_id)))
     JOIN wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     JOIN management_regulation_or_restriction_zone ON ((management_regulation_or_restriction_zone.mrz_id = hydro_object.hdo_id)))
     JOIN water_body ON ((water_body.wbd_id = hydro_object.hdo_id)))
     JOIN river_basin_disrict rbd ON ((rbd.rbd_id = water_body.rbd_id)))
     JOIN hydro_object hdo_rbd ON ((rbd.rbd_id = hdo_rbd.hdo_id)))
     JOIN vw_sg_en_dsg_districten ON ((vw_sg_en_dsg_districten."DB_ID" = water_body.rbd_id)));


--
-- Name: vw_waterlichamen_inspire; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_waterlichamen_inspire AS
 SELECT ((((('NL.WBHCODE.'::text || hydro_object.inspire_id_namespace) || '.'::text) || (hydro_object.inspire_id_local_id)::text) || '.'::text) || hydro_object.wkrv_id) AS "InspireId",
    ('NL.WBHCODE.'::text || hydro_object.inspire_id_namespace) AS "inspireId.namespace",
    hydro_object.inspire_id_local_id AS "inspireId.localId",
    hydro_object.wkrv_id AS "inspireId.Version",
    hydro_object.inspire_id_local_id AS "thematicId",
    hdo_names.name AS "name.text",
    hdo_names.language AS "name.languate",
    'http://inspire.ec.europa.eu/codelist/NativenessValue/endonym'::text AS "name.nativeness",
    'http://inspire.ec.europa.eu/codelist/NameStatusValue/official'::text AS "name.nameStatus",
    namespace_type.descrption AS "name.sourceOfName",
    mrz.geometry_etrs89 AS st_transform,
    geometrytype(mrz.geometry_etrs89) AS geometry_type,
    'http://inspire.ec.europa.eu/codelist/ZoneTypeCode/waterBodyForWFD'::text AS "zoneType",
    ('WFD'::text || (hydro_object.hydro_object_type)::text) AS "specialisedZoneType",
    'http://inspire.ec.europa.eu/codelist/EnvironmentalDomain/water'::text AS "environmentalDomain",
    namespace_type.descrption AS "competentAuthority.organisationName",
    'http://inspire.ec.europa.eu/codelist/RelatedPartyRoleValue/authority'::text AS "competentAuthority.role",
    wkr_version.begin_lifespan_version AS "beginLifeSpanVersion",
    wkr_version.end_lifespan_version AS "endLifeSpanVersion",
    'Directive 2000/60/EC of the European Parliament and of the Council establishing a framework for the Community action in the field of water policy'::text AS "legalBasis.name",
    'Water Framework Directive'::text AS "legalBasis.shortName",
    '2000-10-23'::text AS "legalBasis.date",
    'http://eur-lex.europa.eu/LexUriServ/LexUriServ.do?uri=CELEX:32000L0060:EN:NOT'::text AS "legalBasis.link",
    '2000/60/EC'::text AS "legalBasis.identificationNumber",
    'European'::text AS "legalBasis.level"
   FROM (((((management_regulation_or_restriction_zone mrz
     JOIN water_body ON ((water_body.wbd_id = mrz.mrz_id)))
     JOIN hydro_object ON ((mrz.mrz_id = hydro_object.hdo_id)))
     LEFT JOIN hdo_names ON ((hdo_names.hdo_id = mrz.mrz_id)))
     JOIN wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     JOIN namespace_type ON ((namespace_type.ns_id = hydro_object.inspire_id_namespace)));


--
-- Name: vw_waterlichamen_lzr; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_waterlichamen_lzr AS
 SELECT hydro_object.inspire_id_local_id AS "Waterlichaam.identificatie",
    hdo_names.name AS "Waterlichaam.omschrijving",
    hydro_object.inspire_id_namespace AS "Waterbeheerder.code",
        CASE hydro_object.inspire_id_namespace
            WHEN 2 THEN 'NLBW02'::text
            WHEN 4 THEN 'NLBW04'::text
            WHEN 5 THEN 'NLBW05'::text
            WHEN 7 THEN 'NLBW07'::text
            WHEN 8 THEN 'NLBW43'::text
            WHEN 9 THEN 'NLBW09'::text
            WHEN 10 THEN 'NLBW10'::text
            WHEN 11 THEN 'NLBW11'::text
            WHEN 12 THEN 'NLBW12'::text
            WHEN 13 THEN 'NLBW13'::text
            WHEN 14 THEN 'NLBW14'::text
            WHEN 15 THEN 'NLBW15'::text
            WHEN 16 THEN 'NLBW16'::text
            WHEN 17 THEN 'NLBW17'::text
            WHEN 18 THEN 'NLBW18'::text
            WHEN 19 THEN 'NLBW19'::text
            WHEN 20 THEN 'NLBW20'::text
            WHEN 21 THEN 'NLBW21'::text
            WHEN 22 THEN 'NLBW22'::text
            WHEN 23 THEN 'NLBW23'::text
            WHEN 24 THEN 'NLBW24'::text
            WHEN 25 THEN 'NLBW25'::text
            WHEN 26 THEN 'NLBW26'::text
            WHEN 27 THEN 'NLBW27'::text
            WHEN 28 THEN 'NLBW28'::text
            WHEN 29 THEN 'NLBW29'::text
            WHEN 30 THEN 'NLBW30'::text
            WHEN 31 THEN 'NLBW31'::text
            WHEN 32 THEN 'NLBW32'::text
            WHEN 33 THEN 'NLBW33'::text
            WHEN 34 THEN 'NLBW34'::text
            WHEN 35 THEN 'NLBW35'::text
            WHEN 36 THEN 'NLBW36'::text
            WHEN 37 THEN 'NLBW37'::text
            WHEN 38 THEN 'NLBW38'::text
            WHEN 39 THEN 'NLBW39'::text
            WHEN 40 THEN 'NLBW40'::text
            WHEN 41 THEN 'NLBW41'::text
            WHEN 42 THEN 'NLBW42'::text
            WHEN 43 THEN 'NLBW43'::text
            WHEN 44 THEN 'NLBW44'::text
            WHEN 45 THEN 'NLBW45'::text
            WHEN 46 THEN 'NLBW46'::text
            WHEN 47 THEN 'NLBW47'::text
            WHEN 48 THEN 'NLBW48'::text
            WHEN 49 THEN 'NLBW49'::text
            WHEN 50 THEN 'NLBW50'::text
            WHEN 51 THEN 'NLBW51'::text
            WHEN 52 THEN 'NLBW52'::text
            WHEN 53 THEN 'NLBW53'::text
            WHEN 54 THEN 'NLBW54'::text
            WHEN 55 THEN 'NLBW55'::text
            WHEN 56 THEN 'NLBW56'::text
            WHEN 57 THEN 'NLBW57'::text
            WHEN 58 THEN 'NLBW58'::text
            WHEN 59 THEN 'NLBW59'::text
            WHEN 60 THEN 'NLBW60'::text
            WHEN 61 THEN 'NLBW61'::text
            WHEN 62 THEN 'NLBW62'::text
            WHEN 63 THEN 'NLBW63'::text
            WHEN 64 THEN 'NLBW64'::text
            WHEN 65 THEN 'NLBW65'::text
            WHEN 66 THEN 'NLBW66'::text
            WHEN 67 THEN 'NLBW67'::text
            WHEN 68 THEN 'NLBW68'::text
            WHEN 69 THEN 'NLBW69'::text
            WHEN 70 THEN 'NLBW70'::text
            WHEN 71 THEN 'NLBW71'::text
            WHEN 72 THEN 'NLBW72'::text
            WHEN 73 THEN 'NLBW73'::text
            WHEN 74 THEN 'NLBW74'::text
            WHEN 75 THEN 'NLBW75'::text
            WHEN 76 THEN 'NLBW76'::text
            WHEN 77 THEN 'NLBW77'::text
            WHEN 78 THEN 'NLBW78'::text
            WHEN 79 THEN 'NLBW79'::text
            WHEN 80 THEN 'NLBW80'::text
            WHEN 81 THEN 'NLBW81'::text
            WHEN 82 THEN 'NLBW82'::text
            WHEN 83 THEN 'NLBW83'::text
            WHEN 84 THEN 'NLBW84'::text
            WHEN 85 THEN 'NLBW85'::text
            WHEN 86 THEN 'NLBW86'::text
            WHEN 87 THEN 'NLBW87'::text
            WHEN 88 THEN 'NLBW88'::text
            WHEN 89 THEN 'NLBW89'::text
            WHEN 90 THEN 'NLBW90'::text
            WHEN 91 THEN 'NLBW91'::text
            WHEN 92 THEN 'NLBW92'::text
            WHEN 93 THEN 'NLBW93'::text
            WHEN 94 THEN 'NLBW94'::text
            WHEN 95 THEN 'NLBW95'::text
            WHEN 96 THEN 'NLBW96'::text
            WHEN 97 THEN 'NLBW97'::text
            WHEN 98 THEN 'NLBW98'::text
            WHEN 99 THEN 'NLBW99'::text
            ELSE 'other'::text
        END AS "Zwemwaterbeheerder.code",
    vw_competent_authority."Omschrijving" AS "Waterbeheerder.omschrijving",
        CASE vw_sg_en_dsg_districten."Identificatie"
            WHEN 'NLRNNO'::text THEN 'NLRN'::text
            WHEN 'NLRNOO'::text THEN 'NLRN'::text
            WHEN 'NLRNMI'::text THEN 'NLRN'::text
            WHEN 'NLRNWE'::text THEN 'NLRN'::text
            WHEN 'NLMS'::text THEN 'NLMS'::text
            WHEN 'NLSC'::text THEN 'NLSC'::text
            WHEN 'NLEM'::text THEN 'NLEM'::text
            ELSE 'Other'::text
        END AS "Stroomgebied.code",
    water_body.is_article_7 AS drinkwateronttrekking_jn,
    hydro_object.wkrv_id AS versie,
    wkr_version.begin_lifespan_version AS versie_begintijd,
    wkr_version.end_lifespan_version AS versie_eindtijd
   FROM (((((((hydro_object
     LEFT JOIN hdo_names ON ((hdo_names.hdo_id = hydro_object.hdo_id)))
     JOIN wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     LEFT JOIN management_regulation_or_restriction_zone ON ((management_regulation_or_restriction_zone.mrz_id = hydro_object.hdo_id)))
     LEFT JOIN competentauthority_namespacetype ON ((competentauthority_namespacetype.ns_id = hydro_object.inspire_id_namespace)))
     LEFT JOIN vw_competent_authority ON ((vw_competent_authority."DB_ID" = competentauthority_namespacetype.ore_id)))
     JOIN water_body ON ((water_body.wbd_id = hydro_object.hdo_id)))
     LEFT JOIN vw_sg_en_dsg_districten ON ((vw_sg_en_dsg_districten."DB_ID" = water_body.rbd_id)))
  WHERE ((hydro_object.inspire_id_local_id)::text !~~ 'NLGW%'::text)
  ORDER BY hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id;


--
-- Name: waarde_bepalings_methode_type_wbpm_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE waarde_bepalings_methode_type_wbpm_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: waarde_bepalings_methode_type_wbpm_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE waarde_bepalings_methode_type_wbpm_id_seq OWNED BY waarde_bepalings_methode_type.wbpm_id;


--
-- Name: waarde_bewerkings_methode_type_wbm_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE waarde_bewerkings_methode_type_wbm_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: waarde_bewerkings_methode_type_wbm_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE waarde_bewerkings_methode_type_wbm_id_seq OWNED BY waarde_bewerkings_methode_type.wbm_id;


--
-- Name: waterbody_protected_area_intersection_wbpa_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE waterbody_protected_area_intersection_wbpa_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: waterbody_protected_area_intersection_wbpa_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE waterbody_protected_area_intersection_wbpa_id_seq OWNED BY waterbody_protected_area_intersection.wbpa_id;


--
-- Name: watercourse_local_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE watercourse_local_type (
    wtc_id integer NOT NULL,
    value character varying(90) NOT NULL,
    locale character varying(50) DEFAULT 'nl-nl'::text NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: wb_status_change_motivation_wbsc_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE wb_status_change_motivation_wbsc_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: wb_status_change_motivation_wbsc_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE wb_status_change_motivation_wbsc_id_seq OWNED BY wb_status_change_motivation.wbsc_id;


--
-- Name: wfd_qe_gwtype_qegw_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE wfd_qe_gwtype_qegw_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: wfd_qe_gwtype_qegw_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE wfd_qe_gwtype_qegw_id_seq OWNED BY wfd_qe_gwtype.qegw_id;


--
-- Name: wfd_qe_swtype_qesw_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE wfd_qe_swtype_qesw_id_seq
    START WITH 1277
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: wfd_qe_swtype_qesw_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE wfd_qe_swtype_qesw_id_seq OWNED BY wfd_qe_swtype.qesw_id;


--
-- Name: wfd_status_code; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE wfd_status_code (
    wsc_id integer NOT NULL,
    status_code integer NOT NULL,
    status_description character varying(50) NOT NULL
);


--
-- Name: wfd_status_code_wsc_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE wfd_status_code_wsc_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: wfd_status_code_wsc_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE wfd_status_code_wsc_id_seq OWNED BY wfd_status_code.wsc_id;


--
-- Name: wkpuser; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE wkpuser (
    wkpuserid integer NOT NULL,
    applicationid integer NOT NULL,
    username character varying(50) NOT NULL,
    isanonymous boolean NOT NULL,
    lastactivitydate timestamp with time zone NOT NULL
);


--
-- Name: wkpuser_importtype; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE wkpuser_importtype (
    wkpuser_importtypeid integer NOT NULL,
    wkpuserid integer NOT NULL,
    importtypeid integer NOT NULL
);


--
-- Name: wkpuser_importtype_wkpuser_importtypeid_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE wkpuser_importtype_wkpuser_importtypeid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: wkpuser_importtype_wkpuser_importtypeid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE wkpuser_importtype_wkpuser_importtypeid_seq OWNED BY wkpuser_importtype.wkpuser_importtypeid;


--
-- Name: wkpuser_pagegroup; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE wkpuser_pagegroup (
    wupg_id integer NOT NULL,
    pg_id integer NOT NULL,
    wkpuserid integer NOT NULL
);


--
-- Name: wkpuser_pagegroup_wupg_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE wkpuser_pagegroup_wupg_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: wkpuser_pagegroup_wupg_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE wkpuser_pagegroup_wupg_id_seq OWNED BY wkpuser_pagegroup.wupg_id;


--
-- Name: wkpuser_settings; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE wkpuser_settings (
    wkpuserid integer NOT NULL,
    showwaterlichaamcodefirst boolean NOT NULL,
    griditemsperpage integer DEFAULT 10 NOT NULL
);


--
-- Name: wkpuser_wkpusergroup; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE wkpuser_wkpusergroup (
    wkpuser_wkpusergroup_id integer NOT NULL,
    wkpuserid integer NOT NULL,
    wkpusergroupid integer NOT NULL
);


--
-- Name: wkpuser_wkpusergroup_wkpuser_wkpusergroup_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE wkpuser_wkpusergroup_wkpuser_wkpusergroup_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: wkpuser_wkpusergroup_wkpuser_wkpusergroup_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE wkpuser_wkpusergroup_wkpuser_wkpusergroup_id_seq OWNED BY wkpuser_wkpusergroup.wkpuser_wkpusergroup_id;


--
-- Name: wkpuser_wkpuserid_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE wkpuser_wkpuserid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: wkpuser_wkpuserid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE wkpuser_wkpuserid_seq OWNED BY wkpuser.wkpuserid;


--
-- Name: wkpusergroup; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE wkpusergroup (
    wkpusergroupid integer NOT NULL,
    name character varying(75) NOT NULL,
    description text
);


--
-- Name: wkpusergroup_wkpusergroupid_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE wkpusergroup_wkpusergroupid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: wkpusergroup_wkpusergroupid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE wkpusergroup_wkpusergroupid_seq OWNED BY wkpusergroup.wkpusergroupid;


--
-- Name: wkpuserinrole; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE wkpuserinrole (
    wkpuserinroleid integer NOT NULL,
    wkpuserid integer NOT NULL,
    roleid integer NOT NULL
);


--
-- Name: wkpuserinrole_wkpuserinroleid_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE wkpuserinrole_wkpuserinroleid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: wkpuserinrole_wkpuserinroleid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE wkpuserinrole_wkpuserinroleid_seq OWNED BY wkpuserinrole.wkpuserinroleid;


--
-- Name: wkr_version_wkrv_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE wkr_version_wkrv_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: wkr_version_wkrv_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE wkr_version_wkrv_id_seq OWNED BY wkr_version.wkrv_id;


--
-- Name: yes_no_not_applicable_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE yes_no_not_applicable_type (
    ynna_id integer NOT NULL,
    code character varying(12) NOT NULL,
    omschrijving character varying(50) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    last_changed_date timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: yes_no_not_applicable_type_ynna_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE yes_no_not_applicable_type_ynna_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: yes_no_not_applicable_type_ynna_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE yes_no_not_applicable_type_ynna_id_seq OWNED BY yes_no_not_applicable_type.ynna_id;


--
-- Name: zone_type_code_ztc_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE zone_type_code_ztc_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: zone_type_code_ztc_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE zone_type_code_ztc_id_seq OWNED BY zone_type_code.ztc_id;



--
-- Name: vw_achtergrondwaarden_gwl_dgrw; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_achtergrondwaarden_gwl_dgrw AS
 SELECT natural_background_levels.nbl_id AS "DB_ID",
    vw_grondwaterlichamen.identificatie AS "Waterlichaam_Identificatie",
    vw_grondwaterlichamen.versie AS "Waterlichaam_Versie",
    vw_grondwaterlichamen.is_buiten_deelstroomgebied AS "Buiten_StroomgebiedJN",
    vw_grondwaterlichamen.is_grensoverschrijdend AS "GrensoverschrijdendJN",
    vw_grondwaterlichamen.horizon AS "Waterlichaam_Horizon",
    vw_grondwaterlichamen.substraat AS "Waterlichaam_Substraat",
    vw_waargenomen_eigenschap."DB_ID" AS "WaargenomenEigenschap_DB_ID",
    vw_waargenomen_eigenschap."Grootheid_Code",
    vw_waargenomen_eigenschap."Grootheid_Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_code",
    vw_waargenomen_eigenschap."ChemischeStof_Omschrijving",
    concat(vw_waargenomen_eigenschap."Grootheid_Omschrijving", ' ', vw_waargenomen_eigenschap."ChemischeStof_Omschrijving") AS "Bepaalde_parameter",
    natural_background_levels.priority AS "PrioritaireStof_JN",
    vw_waargenomen_eigenschap."Hoedanigheid_Code",
    vw_waargenomen_eigenschap."Hoedanigheid_Omschrijving",
    natural_background_levels.concentration_value AS "Achtergrondwaarde",
    eenheid_type.code AS "Eenheid_Code",
    eenheid_type.omschrijving AS "Eenheid_Omschrijving"
   FROM (((public.natural_background_levels
     LEFT JOIN public.vw_grondwaterlichamen ON ((vw_grondwaterlichamen.db_id = natural_background_levels.gwb_id)))
     LEFT JOIN public.vw_waargenomen_eigenschap ON ((vw_waargenomen_eigenschap."DB_ID" = natural_background_levels.opt_id)))
     LEFT JOIN public.eenheid_type ON ((eenheid_type.eenh_id = natural_background_levels.concentration_uom_id)));


--
-- Name: vw_belastingen_gw_dgrw_nieuw; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_belastingen_gw_dgrw_nieuw AS
 SELECT vw_grondwaterlichamen.identificatie AS "Waterlichaam_Identificatie",
    vw_grondwaterlichamen.versie AS "Waterlichaam_Versie",
    vw_grondwaterlichamen.is_buiten_deelstroomgebied AS "Buiten_StroomgebiedJN",
    vw_grondwaterlichamen.is_grensoverschrijdend AS "GrensoverschrijdendJN",
    vw_grondwaterlichamen.horizon AS "Waterlichaam_Horizon",
    vw_grondwaterlichamen.substraat AS "Waterlichaam_Substraat",
    namespace_type.descrption AS "Waterbeheerder_omschrijving",
    vw_grondwaterlichamen.omschrijving AS "Waterlichaam_naam",
    "left"((vw_grondwaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    gw_pressure_type.description AS "Belasting",
    pressure_category_type.omschrijving AS "Categorie",
    gwb_pressure.is_significant AS "SignificantJN",
    pressure_relevance_type.omschrijving AS "Urgentie",
    gwb_pressure.effect AS "Effecten",
    gwb_pressure.remark AS "Opmerking",
    ( SELECT array_to_string(ARRAY( SELECT drivers.naam
                   FROM (domains.drivers
                     JOIN public.gw_pressure_type_driver ON (((drivers.driver_id = gw_pressure_type_driver.driver_id) AND (gw_pressure_type_driver.gprs_id = gw_pressure_type.gprs_id))))), ','::text) AS array_to_string) AS "Functie",
    ( SELECT array_to_string(ARRAY( SELECT impacts.naam
                   FROM (domains.impacts
                     JOIN public.gwb_pressure_impact ON (((impacts.impact_id = gwb_pressure_impact.impact_id) AND (gwb_pressure_impact.gwbp_id = gwb_pressure.gwbp_id))))), ','::text) AS array_to_string) AS "Impact"
   FROM (((((public.vw_grondwaterlichamen
     JOIN public.gwb_pressure ON ((vw_grondwaterlichamen.db_id = gwb_pressure.gwb_id)))
     JOIN public.gw_pressure_type ON ((gw_pressure_type.gprs_id = gwb_pressure.gprs_id)))
     JOIN public.pressure_category_type ON ((pressure_category_type.pcat_id = gw_pressure_type.pressure_category_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = vw_grondwaterlichamen.waterbeheerder_code)))
     JOIN public.pressure_relevance_type ON ((gwb_pressure.prr_id = pressure_relevance_type.prr_id)))
  WHERE ((vw_grondwaterlichamen.versie = 13) AND ((pressure_category_type.d_begin IS NULL) OR (pressure_category_type.d_begin <= to_date('2015-12-22'::text, 'YYYY-MM-DD'::text))) AND ((pressure_category_type.d_eind IS NULL) OR (pressure_category_type.d_eind >= to_date('2015-12-22'::text, 'YYYY-MM-DD'::text))) AND ((gw_pressure_type.d_begin IS NULL) OR (gw_pressure_type.d_begin <= to_date('2015-12-22'::text, 'YYYY-MM-DD'::text))) AND ((gw_pressure_type.d_eind IS NULL) OR (gw_pressure_type.d_eind >= to_date('2015-12-22'::text, 'YYYY-MM-DD'::text))));


--
-- Name: vw_belastingen_ow_dgrw_nieuw; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_belastingen_ow_dgrw_nieuw AS
 SELECT vw_oppervlaktewaterlichamen.identificatie AS "Oppervlaktewaterlichaam_Identificatie",
    vw_oppervlaktewaterlichamen.versie AS "Waterlichaam_Versie",
    vw_oppervlaktewaterlichamen.type AS "Waterlichaam_Type",
    vw_oppervlaktewaterlichamen.referentietype AS "Waterlichaam_DoelType",
    vw_oppervlaktewaterlichamen.waterlichaam_status AS "Waterlichaam_Status",
    namespace_type.descrption AS "Waterbeheerder_omschrijving",
    vw_oppervlaktewaterlichamen.omschrijving AS "Waterlichaam_naam",
    "left"((vw_oppervlaktewaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    sw_pressure_type.description AS "Belasting",
    pressure_category_type.omschrijving AS "Categorie",
    swb_pressure.is_significant AS "SignificantJN",
    swb_pressure.effect AS "Effecten",
    swb_pressure.remark AS "Opmerking",
    ( SELECT array_to_string(ARRAY( SELECT drivers.naam
                   FROM (domains.drivers
                     JOIN public.sw_pressure_type_driver ON (((drivers.driver_id = sw_pressure_type_driver.driver_id) AND (sw_pressure_type_driver.sprs_id = sw_pressure_type.sprs_id))))), ','::text) AS array_to_string) AS "Functie",
    ( SELECT array_to_string(ARRAY( SELECT impacts.naam
                   FROM (domains.impacts
                     JOIN public.swb_pressure_impact ON (((impacts.impact_id = swb_pressure_impact.impact_id) AND (swb_pressure_impact.swbp_id = swb_pressure.swbp_id))))), ','::text) AS array_to_string) AS "Impact"
   FROM public.vw_oppervlaktewaterlichamen,
    public.swb_pressure,
    public.sw_pressure_type,
    public.pressure_category_type,
    public.namespace_type
  WHERE ((vw_oppervlaktewaterlichamen.db_id = swb_pressure.swb_id) AND (sw_pressure_type.sprs_id = swb_pressure.sprs_id) AND (pressure_category_type.pcat_id = sw_pressure_type.pressure_category_id) AND (namespace_type.ns_id = vw_oppervlaktewaterlichamen.waterbeheerder_code) AND (namespace_type.ns_id >= 99) AND (vw_oppervlaktewaterlichamen.versie = 13) AND ((pressure_category_type.d_begin IS NULL) OR (pressure_category_type.d_begin <= to_date('2015-12-22'::text, 'YYYY-MM-DD'::text))) AND ((pressure_category_type.d_eind IS NULL) OR (pressure_category_type.d_eind >= to_date('2015-12-22'::text, 'YYYY-MM-DD'::text))) AND ((sw_pressure_type.d_begin IS NULL) OR (sw_pressure_type.d_begin <= to_date('2015-12-22'::text, 'YYYY-MM-DD'::text))) AND ((sw_pressure_type.d_eind IS NULL) OR (sw_pressure_type.d_eind >= to_date('2015-12-22'::text, 'YYYY-MM-DD'::text))))
UNION ALL
 SELECT vw_oppervlaktewaterlichamen.identificatie AS "Oppervlaktewaterlichaam_Identificatie",
    vw_oppervlaktewaterlichamen.versie AS "Waterlichaam_Versie",
    vw_oppervlaktewaterlichamen.type AS "Waterlichaam_Type",
    vw_oppervlaktewaterlichamen.referentietype AS "Waterlichaam_DoelType",
    vw_oppervlaktewaterlichamen.waterlichaam_status AS "Waterlichaam_Status",
    vw_competent_authority."Omschrijving" AS "Waterbeheerder_omschrijving",
    vw_oppervlaktewaterlichamen.omschrijving AS "Waterlichaam_naam",
    "left"((vw_oppervlaktewaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    sw_pressure_type.description AS "Belasting",
    pressure_category_type.omschrijving AS "Categorie",
    swb_pressure.is_significant AS "SignificantJN",
    swb_pressure.effect AS "Effecten",
    swb_pressure.remark AS "Opmerking",
    ( SELECT array_to_string(ARRAY( SELECT drivers.naam
                   FROM (domains.drivers
                     JOIN public.sw_pressure_type_driver ON (((drivers.driver_id = sw_pressure_type_driver.driver_id) AND (sw_pressure_type_driver.sprs_id = sw_pressure_type.sprs_id))))), ','::text) AS array_to_string) AS "Functie",
    ( SELECT array_to_string(ARRAY( SELECT impacts.naam
                   FROM (domains.impacts
                     JOIN public.swb_pressure_impact ON (((impacts.impact_id = swb_pressure_impact.impact_id) AND (swb_pressure_impact.swbp_id = swb_pressure.swbp_id))))), ','::text) AS array_to_string) AS "Impact"
   FROM public.vw_oppervlaktewaterlichamen,
    public.swb_pressure,
    public.sw_pressure_type,
    public.pressure_category_type,
    public.competentauthority_namespacetype,
    public.vw_competent_authority
  WHERE ((vw_oppervlaktewaterlichamen.db_id = swb_pressure.swb_id) AND (sw_pressure_type.sprs_id = swb_pressure.sprs_id) AND (pressure_category_type.pcat_id = sw_pressure_type.pressure_category_id) AND (vw_oppervlaktewaterlichamen.waterbeheerder_code < 99) AND (competentauthority_namespacetype.ns_id = vw_oppervlaktewaterlichamen.waterbeheerder_code) AND (vw_competent_authority."DB_ID" = competentauthority_namespacetype.ore_id) AND (vw_oppervlaktewaterlichamen.versie = 13) AND ((pressure_category_type.d_begin IS NULL) OR (pressure_category_type.d_begin <= to_date('2015-12-22'::text, 'YYYY-MM-DD'::text))) AND ((pressure_category_type.d_eind IS NULL) OR (pressure_category_type.d_eind >= to_date('2015-12-22'::text, 'YYYY-MM-DD'::text))) AND ((sw_pressure_type.d_begin IS NULL) OR (sw_pressure_type.d_begin <= to_date('2015-12-22'::text, 'YYYY-MM-DD'::text))) AND ((sw_pressure_type.d_eind IS NULL) OR (sw_pressure_type.d_eind >= to_date('2015-12-22'::text, 'YYYY-MM-DD'::text))));


--
-- Name: vw_grondwaterlichamen_dgrw; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_grondwaterlichamen_dgrw AS
 SELECT hydro_object.inspire_id_local_id AS identificatie,
    namespace_type.descrption AS "Waterbeheerder_Omschrijving",
        CASE
            WHEN (water_body.is_article_7 = true) THEN 'Aanwezig'::text
            ELSE 'Afwezig'::text
        END AS drinkwateronttrekking_jn,
    hdo_names.name AS omschrijving,
    "left"((hdo_rbd.inspire_id_local_id)::text, 4) AS stroomgebieddistrict,
    hydro_object.wkrv_id AS versie,
    ground_water_body.is_out_of_rbd AS is_buiten_deelstroomgebied,
    ground_water_body.is_transboundary AS is_grensoverschrijdend,
    ground_water_body.horizon_id AS horizon,
    substrate_type.omschrijving AS substraat,
    ground_water_body.is_layered AS is_gelaagd
   FROM ((((((((public.management_regulation_or_restriction_zone
     JOIN public.hydro_object ON ((management_regulation_or_restriction_zone.mrz_id = hydro_object.hdo_id)))
     LEFT JOIN public.hdo_names ON ((hdo_names.hdo_id = management_regulation_or_restriction_zone.mrz_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = hydro_object.inspire_id_namespace)))
     JOIN public.water_body ON ((water_body.wbd_id = management_regulation_or_restriction_zone.mrz_id)))
     JOIN public.ground_water_body ON ((ground_water_body.gwb_id = management_regulation_or_restriction_zone.mrz_id)))
     JOIN public.river_basin_disrict rbd ON ((rbd.rbd_id = water_body.rbd_id)))
     JOIN public.hydro_object hdo_rbd ON ((rbd.rbd_id = hdo_rbd.hdo_id)))
     JOIN public.substrate_type ON ((substrate_type.sub_id = ground_water_body.substrate_id)))
  WHERE ((management_regulation_or_restriction_zone.mrz_type)::text = 'GroundWaterBody'::text);


--
-- Name: vw_gw_onttrekking_dgrw; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_gw_onttrekking_dgrw AS
 SELECT namespace_type.code AS "Namespace",
    hydro_object.inspire_id_local_id AS "LokaalID",
    hdo_names.name AS "Omschrijving",
    gw_abstraction_point.company AS "Bedrijf",
    rewab_type.description AS "Onttrekking.Type",
    public.st_x((public.st_astext((public.st_dump(abstraction_point.geometry)).geom))::public.geometry) AS "RD_X",
    public.st_y((public.st_astext((public.st_dump(abstraction_point.geometry)).geom))::public.geometry) AS "RD_Y",
    vw_grondwaterlichamen.identificatie AS "Waterlichaam_Identificatie",
    vw_grondwaterlichamen.versie AS "Waterlichaam_Versie",
    vw_grondwaterlichamen.is_buiten_deelstroomgebied AS "Buiten_StroomgebiedJN",
    vw_grondwaterlichamen.is_grensoverschrijdend AS "GrensoverschrijdendJN",
    vw_grondwaterlichamen.horizon AS "Waterlichaam_Horizon",
    vw_grondwaterlichamen.substraat AS "Waterlichaam_Substraat",
    namespace_type.descrption AS "Waterbeheerder_omschrijving",
    vw_grondwaterlichamen.omschrijving AS "Waterlichaam_naam",
    "left"((vw_grondwaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict"
   FROM ((((((public.hydro_object
     LEFT JOIN public.hdo_names ON ((hdo_names.hdo_id = hydro_object.hdo_id)))
     JOIN public.abstraction_point ON ((hydro_object.hdo_id = abstraction_point.asp_id)))
     JOIN public.gw_abstraction_point ON ((hydro_object.hdo_id = gw_abstraction_point.gasp_id)))
     JOIN public.rewab_type ON ((rewab_type.rwb_id = gw_abstraction_point.rewab_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = hydro_object.inspire_id_namespace)))
     LEFT JOIN public.vw_grondwaterlichamen ON ((vw_grondwaterlichamen.db_id = abstraction_point.wbd_id)));


--
-- Name: vw_maatregelen_dgrw_totaal; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_maatregelen_dgrw_totaal AS
 SELECT maatregelen.mtm_id AS "DB_ID",
    (vw_competent_authority.waterbeheerder_db_id)::text AS "Namespace",
        CASE
            WHEN (supp_add_measure_type.namespace_id >= 99) THEN wb_nst.descrption
            ELSE vw_competent_authority."Omschrijving"
        END AS "Waterbeheerder_Naam",
    regexp_replace((supp_add_measure_type.local_id)::text, '\r|\n'::text, ''::text, 'g'::text) AS "LokaalID",
    regexp_replace((supp_add_measure_type.measure_name)::text, '\r|\n'::text, ''::text, 'g'::text) AS "Naam",
    rpp.description AS "SGBPPeriode.Omschrijving",
    ((date_part('year'::text, rpp.period_start) || '-'::text) || date_part('year'::text, rpp.period_end)) AS "UitvoeringIn",
    regexp_replace((sw_supp_measure_eu_type.code)::text, '\r|\n'::text, ''::text, 'g'::text) AS "KRWmaatregel.Code",
    sw_supp_measure_eu_type.subcategorie AS "KRWmaatregel.Naam",
    sw_supp_measure_eu_type.hoofdcategorie AS "Hoofdcategorie",
    sw_supp_measure_eu_type.index AS "Index",
    sw_supp_measure_eu_type.klasse AS "SGBP_Klasse",
    sw_supp_measure_eu_type.harmonisatie AS "SGBP_Harmonisatie",
    sw_supp_measure_eu_type.code_sgbp AS "SGBP_code",
    sw_supp_measure_eu_type.artikel_sgbp AS "SGBP_artikel",
    sw_supp_measure_eu_type.tablkop_sgbp AS "SGBP_Hoofdcategorie",
    regexp_replace((level_of_area_type.code)::text, '\r|\n'::text, ''::text, 'g'::text) AS "KRWMaatregelNiveau.Code",
    regexp_replace((level_of_area_type.description)::text, '\r|\n'::text, ''::text, 'g'::text) AS "KRWMaatregelNiveau.Omschrijving",
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace((ho.inspire_id_local_id)::text, '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM (public.hydro_object ho
             LEFT JOIN public.measure_per_man_reg_or_restr_zone mz ON ((mz.mrz_id = ho.hdo_id)))
          WHERE (mz.mtm_id = maatregelen.mtm_id)), '|'::text) AS "HoortbijKRWwaterlichaam",
    stroomgebieddistrict.inspire_id_local_id AS "KRWstroomgebiedDistrict.Code",
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace((ho.inspire_id_local_id)::text, '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM ((public.hydro_object ho
             LEFT JOIN public.water_body wbd ON ((wbd.rbd_id = ho.hdo_id)))
             LEFT JOIN public.measure_per_man_reg_or_restr_zone mz ON ((mz.mrz_id = wbd.wbd_id)))
          WHERE (mz.mtm_id = maatregelen.mtm_id)), '|'::text) AS "KRWdeelstroomgebiedDistrict.Code",
    supp_add_measure_type.is_additional_measure AS "OpnameInSGBPJN",
    regexp_replace((organisation_category_type.code)::text, '\r|\n'::text, ''::text, 'g'::text) AS "Initiatiefnemer.Code",
    regexp_replace(supp_add_measure_type.initiative_party, '\r|\n'::text, ''::text, 'g'::text) AS "Initiatiefnemer.Naam",
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace((t.code)::text, '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM (public.monitored_measure_eu_fund m
             JOIN public.eu_fund_type t ON ((t.euf_id = m.euf_id)))
          WHERE (m.mtm_id = maatregelen.mtm_id)), '|'::text) AS "EUFonds.Code",
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace((vbg."Identificatie")::text, '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM (public.vw_beschermdegebieden vbg
             JOIN public.measure_per_man_protected_area mpmpa ON ((mpmpa.wpa_id = vbg."DB_ID")))
          WHERE ((mpmpa.mtm_id = maatregelen.mtm_id) AND (vbg."Versie_ID" = river_basin_management_plan.wkrv_id))), '|'::text) AS "RelatieMetNatura2000Gebied",
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace((vgo."Identificatie")::text, '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM (public.vw_gw_onttrekking vgo
             JOIN public.measure_per_abstraction_point mpap ON ((mpap.asp_id = vgo."DB_ID")))
          WHERE ((mpap.mtm_id = maatregelen.mtm_id) AND (vgo.wkr_versie = river_basin_management_plan.wkrv_id))), '|'::text) AS "RelatieMetWaterwinning",
    river_basin_management_plan.wkrv_id AS versie,
    supp_add_measure_type.amount_value AS "OmvangNumeriekeWaarde",
    regexp_replace((eenheid_type.code)::text, '\r|\n'::text, ''::text, 'g'::text) AS omvangeenheidcode,
    mp1.amount_value AS voortgang,
    mp1.year AS "Jaar_voortgang",
    mst.code AS "Type_voortgang",
    concat(sw_supp_measure_eu_type.subcategorie, ' [', eenheid_type.code, ']') AS "Naam_Eenheid"
   FROM ((((((((((((((public.monitored_measure maatregelen
     JOIN public.supp_add_measure_type ON ((supp_add_measure_type.smnl_id = maatregelen.mtm_id)))
     JOIN public.sw_supp_measure_eu_type ON ((sw_supp_measure_eu_type.smeu_id = supp_add_measure_type.smeu_id)))
     JOIN public.eenheid_type ON ((eenheid_type.eenh_id = sw_supp_measure_eu_type.eenh_id)))
     JOIN public.namespace_type wb_nst ON ((wb_nst.ns_id = supp_add_measure_type.namespace_id)))
     JOIN public.competentauthority_namespacetype ON ((competentauthority_namespacetype.ns_id = supp_add_measure_type.namespace_id)))
     JOIN public.vw_competent_authority ON ((vw_competent_authority."DB_ID" = competentauthority_namespacetype.ore_id)))
     JOIN public.report_version ON ((report_version.rpv_id = maatregelen.rpv_id)))
     JOIN public.reporting_period rpp ON ((rpp.rpp_id = report_version.rpp_id)))
     JOIN public.level_of_area_type ON ((level_of_area_type.loa_id = maatregelen.level_of_measure_id)))
     JOIN public.organisation_category_type ON ((organisation_category_type.ocat_id = supp_add_measure_type.initiative_party_ocat_id)))
     JOIN public.river_basin_management_plan ON ((river_basin_management_plan.rbmp_id = supp_add_measure_type.rbmp_id)))
     JOIN public.hydro_object stroomgebieddistrict ON ((stroomgebieddistrict.hdo_id = river_basin_management_plan.rbd_id)))
     LEFT JOIN public.measure_progress mp1 ON ((mp1.mtm_id = maatregelen.mtm_id)))
     LEFT JOIN public.measure_status_type mst ON ((mst.mst_id = mp1.mst_id)))
  ORDER BY maatregelen.mtm_id;


--
-- Name: vw_maatregelen_dgrw_totaal_sgb; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_maatregelen_dgrw_totaal_sgb AS
 SELECT maatregelen.mtm_id AS "DB_ID",
    (vw_competent_authority.waterbeheerder_db_id)::text AS "Namespace",
        CASE
            WHEN (supp_add_measure_type.namespace_id >= 99) THEN wb_nst.descrption
            ELSE vw_competent_authority."Omschrijving"
        END AS "Waterbeheerder_Naam",
    regexp_replace((supp_add_measure_type.local_id)::text, '\r|\n'::text, ''::text, 'g'::text) AS "LokaalID",
    regexp_replace((supp_add_measure_type.measure_name)::text, '\r|\n'::text, ''::text, 'g'::text) AS "Naam",
    rpp.description AS "SGBPPeriode.Omschrijving",
    ((date_part('year'::text, rpp.period_start) || '-'::text) || date_part('year'::text, rpp.period_end)) AS "UitvoeringIn",
    regexp_replace((sw_supp_measure_eu_type.code)::text, '\r|\n'::text, ''::text, 'g'::text) AS "KRWmaatregel.Code",
    sw_supp_measure_eu_type.subcategorie AS "KRWmaatregel.Naam",
    sw_supp_measure_eu_type.hoofdcategorie AS "Hoofdcategorie",
    sw_supp_measure_eu_type.index AS "Index",
    sw_supp_measure_eu_type.klasse AS "SGBP_Klasse",
    sw_supp_measure_eu_type.harmonisatie AS "SGBP_Harmonisatie",
    sw_supp_measure_eu_type.code_sgbp AS "SGBP_code",
    sw_supp_measure_eu_type.artikel_sgbp AS "SGBP_artikel",
    sw_supp_measure_eu_type.tablkop_sgbp AS "SGBP_Hoofdcategorie",
    regexp_replace((level_of_area_type.code)::text, '\r|\n'::text, ''::text, 'g'::text) AS "KRWMaatregelNiveau.Code",
    regexp_replace((level_of_area_type.description)::text, '\r|\n'::text, ''::text, 'g'::text) AS "KRWMaatregelNiveau.Omschrijving",
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace((ho.inspire_id_local_id)::text, '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM ((public.hydro_object ho
             LEFT JOIN public.water_body wbd ON ((wbd.rbd_id = ho.hdo_id)))
             LEFT JOIN public.measure_per_man_reg_or_restr_zone mz ON ((mz.mrz_id = wbd.wbd_id)))
          WHERE (mz.mtm_id = maatregelen.mtm_id)), '|'::text) AS "KRWdeelstroomgebiedDistrict.Code",
    stroomgebieddistrict.inspire_id_local_id AS "KRWstroomgebiedDistrict.Code",
    supp_add_measure_type.is_additional_measure AS "OpnameInSGBPJN",
    regexp_replace((organisation_category_type.code)::text, '\r|\n'::text, ''::text, 'g'::text) AS "Initiatiefnemer.Code",
    regexp_replace(supp_add_measure_type.initiative_party, '\r|\n'::text, ''::text, 'g'::text) AS "Initiatiefnemer.Naam",
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace((t.code)::text, '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM (public.monitored_measure_eu_fund m
             JOIN public.eu_fund_type t ON ((t.euf_id = m.euf_id)))
          WHERE (m.mtm_id = maatregelen.mtm_id)), '|'::text) AS "EUFonds.Code",
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace((vbg."Identificatie")::text, '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM (public.vw_beschermdegebieden vbg
             JOIN public.measure_per_man_protected_area mpmpa ON ((mpmpa.wpa_id = vbg."DB_ID")))
          WHERE ((mpmpa.mtm_id = maatregelen.mtm_id) AND (vbg."Versie_ID" = river_basin_management_plan.wkrv_id))), '|'::text) AS "RelatieMetNatura2000Gebied",
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace((vgo."Identificatie")::text, '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM (public.vw_gw_onttrekking vgo
             JOIN public.measure_per_abstraction_point mpap ON ((mpap.asp_id = vgo."DB_ID")))
          WHERE ((mpap.mtm_id = maatregelen.mtm_id) AND (vgo.wkr_versie = river_basin_management_plan.wkrv_id))), '|'::text) AS "RelatieMetWaterwinning",
    river_basin_management_plan.wkrv_id AS versie,
    supp_add_measure_type.amount_value AS "OmvangNumeriekeWaarde",
    regexp_replace((eenheid_type.code)::text, '\r|\n'::text, ''::text, 'g'::text) AS omvangeenheidcode,
    mp1.amount_value AS voortgang,
    mp1.year AS "Jaar_voortgang",
    mst.code AS "Type_voortgang",
    concat(sw_supp_measure_eu_type.subcategorie, ' [', eenheid_type.code, ']') AS "Naam_Eenheid"
   FROM ((((((((((((((public.monitored_measure maatregelen
     JOIN public.supp_add_measure_type ON ((supp_add_measure_type.smnl_id = maatregelen.mtm_id)))
     JOIN public.sw_supp_measure_eu_type ON ((sw_supp_measure_eu_type.smeu_id = supp_add_measure_type.smeu_id)))
     JOIN public.eenheid_type ON ((eenheid_type.eenh_id = sw_supp_measure_eu_type.eenh_id)))
     JOIN public.namespace_type wb_nst ON ((wb_nst.ns_id = supp_add_measure_type.namespace_id)))
     JOIN public.competentauthority_namespacetype ON ((competentauthority_namespacetype.ns_id = supp_add_measure_type.namespace_id)))
     JOIN public.vw_competent_authority ON ((vw_competent_authority."DB_ID" = competentauthority_namespacetype.ore_id)))
     JOIN public.report_version ON ((report_version.rpv_id = maatregelen.rpv_id)))
     JOIN public.reporting_period rpp ON ((rpp.rpp_id = report_version.rpp_id)))
     JOIN public.level_of_area_type ON ((level_of_area_type.loa_id = maatregelen.level_of_measure_id)))
     JOIN public.organisation_category_type ON ((organisation_category_type.ocat_id = supp_add_measure_type.initiative_party_ocat_id)))
     JOIN public.river_basin_management_plan ON ((river_basin_management_plan.rbmp_id = supp_add_measure_type.rbmp_id)))
     JOIN public.hydro_object stroomgebieddistrict ON ((stroomgebieddistrict.hdo_id = river_basin_management_plan.rbd_id)))
     LEFT JOIN public.measure_progress mp1 ON ((mp1.mtm_id = maatregelen.mtm_id)))
     LEFT JOIN public.measure_status_type mst ON ((mst.mst_id = mp1.mst_id)));


--
-- Name: vw_milieudoelstellingen_gw_dgrw; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_milieudoelstellingen_gw_dgrw AS
 SELECT vw_grondwaterlichamen.identificatie AS "Waterlichaam_Identificatie",
    vw_grondwaterlichamen.versie AS "Waterlichaam_Versie",
    vw_grondwaterlichamen.is_buiten_deelstroomgebied AS "Buiten_StroomgebiedJN",
    vw_grondwaterlichamen.is_grensoverschrijdend AS "GrensoverschrijdendJN",
    vw_grondwaterlichamen.horizon AS "Waterlichaam_Horizon",
    vw_grondwaterlichamen.substraat AS "Waterlichaam_Substraat",
    namespace_type.descrption AS "Waterbeheerder_omschrijving",
    vw_grondwaterlichamen.omschrijving AS "Waterlichaam_naam",
    "left"((vw_grondwaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    objectives_exemption_type.article_nr AS "Artikel",
    objectives_exemption_type.obex_id AS "Motiveringsgrond_Functie_DB_ID",
    objectives_exemption_type.name AS "Motiveringsgrond_of_Functie"
   FROM ((((public.gwb_chemical_exemption
     LEFT JOIN public.objectives_exemption_type ON ((objectives_exemption_type.obex_id = gwb_chemical_exemption.obex_id)))
     LEFT JOIN public.hydro_object hdo_gwb ON ((hdo_gwb.hdo_id = gwb_chemical_exemption.gwb_id)))
     LEFT JOIN public.vw_grondwaterlichamen ON ((vw_grondwaterlichamen.db_id = hdo_gwb.hdo_id)))
     LEFT JOIN public.namespace_type ON ((namespace_type.ns_id = hdo_gwb.inspire_id_namespace)));


--
-- Name: vw_milieudoelstellingen_ow_dgrw; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_milieudoelstellingen_ow_dgrw AS
 SELECT vw_oppervlaktewaterlichamen.identificatie AS "Waterlichaam_identificatie",
    vw_oppervlaktewaterlichamen.versie AS "Waterlichaam_Versie",
    vw_oppervlaktewaterlichamen.type AS "Waterlichaam_Type",
    "left"((vw_oppervlaktewaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    objectives_exemption_type.article_nr AS "Artikel",
    objectives_exemption_type.obex_id AS "Motiveringsgrond_Functie_DB_ID",
    objectives_exemption_type.name AS "Motiveringsgrond_of_Functie",
        CASE
            WHEN (hdo_swb.inspire_id_namespace >= 99) THEN namespace_type.descrption
            ELSE vw_competent_authority."Omschrijving"
        END AS "Waterbeheerder_omschrijving",
    vw_oppervlaktewaterlichamen.omschrijving AS "Waterlichaam_naam",
    vw_oppervlaktewaterlichamen.referentietype AS "Waterlichaam_DoelType",
    vw_oppervlaktewaterlichamen.waterlichaam_status AS "Waterlichaam_Status"
   FROM ((((((public.swb_exemption
     LEFT JOIN public.objectives_exemption_type ON ((objectives_exemption_type.obex_id = swb_exemption.obex_id)))
     LEFT JOIN public.hydro_object hdo_swb ON ((hdo_swb.hdo_id = swb_exemption.swb_id)))
     LEFT JOIN public.vw_oppervlaktewaterlichamen ON ((vw_oppervlaktewaterlichamen.db_id = hdo_swb.hdo_id)))
     LEFT JOIN public.namespace_type ON ((namespace_type.ns_id = hdo_swb.inspire_id_namespace)))
     JOIN public.competentauthority_namespacetype ON ((competentauthority_namespacetype.ns_id = hdo_swb.inspire_id_namespace)))
     JOIN public.vw_competent_authority ON ((vw_competent_authority."DB_ID" = competentauthority_namespacetype.ore_id)))
UNION ALL
 SELECT vw_oppervlaktewaterlichamen.identificatie AS "Waterlichaam_identificatie",
    vw_oppervlaktewaterlichamen.versie AS "Waterlichaam_Versie",
    vw_oppervlaktewaterlichamen.type AS "Waterlichaam_Type",
    "left"((vw_oppervlaktewaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    '4.3a'::character varying AS "Artikel",
    eu_function_43a_type.fu43_id AS "Motiveringsgrond_Functie_DB_ID",
    eu_function_43a_type.function AS "Motiveringsgrond_of_Functie",
        CASE
            WHEN (hdo_swb.inspire_id_namespace >= 99) THEN namespace_type.descrption
            ELSE vw_competent_authority."Omschrijving"
        END AS "Waterbeheerder_omschrijving",
    vw_oppervlaktewaterlichamen.omschrijving AS "Waterlichaam_naam",
    vw_oppervlaktewaterlichamen.referentietype AS "Waterlichaam_DoelType",
    vw_oppervlaktewaterlichamen.waterlichaam_status AS "Waterlichaam_Status"
   FROM ((((((public.swb_measures_43a
     LEFT JOIN public.hydro_object hdo_swb ON ((hdo_swb.hdo_id = swb_measures_43a.swb_id)))
     LEFT JOIN public.vw_oppervlaktewaterlichamen ON ((vw_oppervlaktewaterlichamen.db_id = hdo_swb.hdo_id)))
     LEFT JOIN public.eu_function_43a_type ON ((eu_function_43a_type.fu43_id = swb_measures_43a.fu43_id)))
     LEFT JOIN public.namespace_type ON ((namespace_type.ns_id = hdo_swb.inspire_id_namespace)))
     JOIN public.competentauthority_namespacetype ON ((competentauthority_namespacetype.ns_id = hdo_swb.inspire_id_namespace)))
     JOIN public.vw_competent_authority ON ((vw_competent_authority."DB_ID" = competentauthority_namespacetype.ore_id)))
  WHERE (vw_oppervlaktewaterlichamen.waterlichaam_status = 'Sterk veranderd'::text)
UNION ALL
 SELECT vw_oppervlaktewaterlichamen.identificatie AS "Waterlichaam_identificatie",
    vw_oppervlaktewaterlichamen.versie AS "Waterlichaam_Versie",
    vw_oppervlaktewaterlichamen.type AS "Waterlichaam_Type",
    "left"((vw_oppervlaktewaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    '4.3b'::character varying AS "Artikel",
    eu_article_43b_type.a43b_id AS "Motiveringsgrond_Functie_DB_ID",
    eu_article_43b_type.omschrijving AS "Motiveringsgrond_of_Functie",
        CASE
            WHEN (hdo_swb.inspire_id_namespace >= 99) THEN namespace_type.descrption
            ELSE vw_competent_authority."Omschrijving"
        END AS "Waterbeheerder_omschrijving",
    vw_oppervlaktewaterlichamen.omschrijving AS "Waterlichaam_naam",
    vw_oppervlaktewaterlichamen.referentietype AS "Waterlichaam_DoelType",
    vw_oppervlaktewaterlichamen.waterlichaam_status AS "Waterlichaam_Status"
   FROM ((((((public.swb_art43b_excemption
     LEFT JOIN public.hydro_object hdo_swb ON ((hdo_swb.hdo_id = swb_art43b_excemption.swb_id)))
     LEFT JOIN public.vw_oppervlaktewaterlichamen ON ((vw_oppervlaktewaterlichamen.db_id = hdo_swb.hdo_id)))
     LEFT JOIN public.eu_article_43b_type ON ((eu_article_43b_type.a43b_id = swb_art43b_excemption.a43b_id)))
     LEFT JOIN public.namespace_type ON ((namespace_type.ns_id = hdo_swb.inspire_id_namespace)))
     JOIN public.competentauthority_namespacetype ON ((competentauthority_namespacetype.ns_id = hdo_swb.inspire_id_namespace)))
     JOIN public.vw_competent_authority ON ((vw_competent_authority."DB_ID" = competentauthority_namespacetype.ore_id)))
  WHERE (vw_oppervlaktewaterlichamen.waterlichaam_status = 'Sterk veranderd'::text);


--
-- Name: vw_oordeel_totaal_gwl_dgrw; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_oordeel_totaal_gwl_dgrw AS
 SELECT gwb_yearly_status.gys_id AS "DB_ID",
    vw_grondwaterlichamen.identificatie AS "Waterlichaam_Identificatie",
    gwb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap."DB_ID" AS "Waargenomen_Eigenschap_DB_ID",
    vw_waargenomen_eigenschap."Tpering_Code",
    vw_waargenomen_eigenschap."Typering_Omschrijving",
    vw_waargenomen_eigenschap."Grootheid_Code",
    vw_waargenomen_eigenschap."Grootheid_Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_code",
    vw_waargenomen_eigenschap."ChemischeStof_Omschrijving",
    vw_waargenomen_eigenschap."Hoedanigheid_Code",
    vw_waargenomen_eigenschap."Hoedanigheid_Omschrijving",
    concat(vw_waargenomen_eigenschap."Typering_Omschrijving", vw_waargenomen_eigenschap."Grootheid_Omschrijving", ' ', vw_waargenomen_eigenschap."ChemischeStof_Omschrijving") AS concat,
    classification_type.description AS "Oordeel",
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code",
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving",
    waarde_bepalings_methode_type.code AS "WaardebepalingsMethode_Code",
    waarde_bepalings_methode_type.omschrijving AS "WaardebepalingsMethode_Omschrijving",
        CASE
            WHEN (krw_kwal."Is_van_toepassing" IS TRUE) THEN true
            ELSE false
        END AS "KWAL_ELEMENT",
    krw_kwal."Onderdeel_van_EU_Typering_Omschrijving",
    krw_kwal."Onderdeel_van_NL_Typering_Omschrijving",
    vw_grondwaterlichamen.versie AS "Waterlichaam_Versie",
    vw_grondwaterlichamen.is_buiten_deelstroomgebied AS "Buiten_StroomgebiedJN",
    vw_grondwaterlichamen.is_grensoverschrijdend AS "GrensoverschrijdendJN",
    vw_grondwaterlichamen.horizon AS "Waterlichaam_Horizon",
    vw_grondwaterlichamen.substraat AS "Waterlichaam_Substraat",
    namespace_type.descrption AS "Waterbeheerder_omschrijving",
    vw_grondwaterlichamen.omschrijving AS "Waterlichaam_naam",
    "left"((vw_grondwaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict"
   FROM ((((((((public.gwb_yearly_status
     LEFT JOIN public.vw_grondwaterlichamen ON ((vw_grondwaterlichamen.db_id = gwb_yearly_status.gwb_id)))
     JOIN public.gw_chemical_status ON ((gw_chemical_status.gys_id = gwb_yearly_status.gys_id)))
     LEFT JOIN public.classification_type ON ((gw_chemical_status.clst_id = classification_type.cls_id)))
     LEFT JOIN public.vw_waargenomen_eigenschap ON ((vw_waargenomen_eigenschap."DB_ID" = gw_chemical_status.opt_id)))
     LEFT JOIN public.waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = gw_chemical_status.wbpm_id)))
     LEFT JOIN public.eu_monitoring_goal_type ON ((eu_monitoring_goal_type.eumg_id = gw_chemical_status.eumg_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = vw_grondwaterlichamen.waterbeheerder_code)))
     LEFT JOIN public.vw_krw_kwaliteitselement_gw krw_kwal ON ((krw_kwal."Waargenomen_Eigenschap_DB_ID" = vw_waargenomen_eigenschap."DB_ID")))
UNION ALL
 SELECT gwb_yearly_status.gys_id AS "DB_ID",
    vw_grondwaterlichamen.identificatie AS "Waterlichaam_Identificatie",
    gwb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap."DB_ID" AS "Waargenomen_Eigenschap_DB_ID",
    vw_waargenomen_eigenschap."Tpering_Code",
    vw_waargenomen_eigenschap."Typering_Omschrijving",
    vw_waargenomen_eigenschap."Grootheid_Code",
    vw_waargenomen_eigenschap."Grootheid_Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_code",
    vw_waargenomen_eigenschap."ChemischeStof_Omschrijving",
    vw_waargenomen_eigenschap."Hoedanigheid_Code",
    vw_waargenomen_eigenschap."Hoedanigheid_Omschrijving",
    concat(vw_waargenomen_eigenschap."Typering_Omschrijving", vw_waargenomen_eigenschap."Grootheid_Omschrijving", ' ', vw_waargenomen_eigenschap."ChemischeStof_Omschrijving") AS concat,
    classification_type.description AS "Oordeel",
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code",
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving",
    waarde_bepalings_methode_type.code AS "WaardebepalingsMethode_Code",
    waarde_bepalings_methode_type.omschrijving AS "WaardebepalingsMethode_Omschrijving",
        CASE
            WHEN (krw_kwal."Is_van_toepassing" IS TRUE) THEN true
            ELSE false
        END AS "KWAL_ELEMENT",
    krw_kwal."Onderdeel_van_EU_Typering_Omschrijving",
    krw_kwal."Onderdeel_van_NL_Typering_Omschrijving",
    vw_grondwaterlichamen.versie AS "Waterlichaam_Versie",
    vw_grondwaterlichamen.is_buiten_deelstroomgebied AS "Buiten_StroomgebiedJN",
    vw_grondwaterlichamen.is_grensoverschrijdend AS "GrensoverschrijdendJN",
    vw_grondwaterlichamen.horizon AS "Waterlichaam_Horizon",
    vw_grondwaterlichamen.substraat AS "Waterlichaam_Substraat",
    namespace_type.descrption AS "Waterbeheerder_omschrijving",
    vw_grondwaterlichamen.omschrijving AS "Waterlichaam_naam",
    "left"((vw_grondwaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict"
   FROM ((((((((public.gwb_yearly_status
     LEFT JOIN public.vw_grondwaterlichamen ON ((vw_grondwaterlichamen.db_id = gwb_yearly_status.gwb_id)))
     JOIN public.gw_quantity_status ON ((gw_quantity_status.gys_id = gwb_yearly_status.gys_id)))
     LEFT JOIN public.classification_type ON ((gw_quantity_status.gwst_id = classification_type.cls_id)))
     LEFT JOIN public.vw_waargenomen_eigenschap ON ((vw_waargenomen_eigenschap."DB_ID" = gw_quantity_status.opt_id)))
     LEFT JOIN public.waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = gw_quantity_status.wbpm_id)))
     LEFT JOIN public.eu_monitoring_goal_type ON ((eu_monitoring_goal_type.eumg_id = gw_quantity_status.eumg_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = vw_grondwaterlichamen.waterbeheerder_code)))
     LEFT JOIN public.vw_krw_kwaliteitselement_gw krw_kwal ON ((krw_kwal."Waargenomen_Eigenschap_DB_ID" = vw_waargenomen_eigenschap."DB_ID")));


--
-- Name: vw_oordeel_totaal_owm_dgrw_nieuw; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_oordeel_totaal_owm_dgrw_nieuw AS
 SELECT swb_yearly_status.sys_id AS "Status_DB_ID",
    vw_oppervlaktewaterlichamen.identificatie AS "Waterlichaam_identificatie",
    vw_oppervlaktewaterlichamen.versie AS "Waterlichaam_Versie",
    vw_oppervlaktewaterlichamen.type AS "Waterlichaam_Type",
    "left"((vw_oppervlaktewaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    swb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap_db_id."DB_ID" AS "Waargenomen_Eigenschap_DB_ID",
    vw_waargenomen_eigenschap_db_id."Tpering_Code",
    vw_waargenomen_eigenschap_db_id."Typering_Omschrijving",
    vw_waargenomen_eigenschap_db_id."Grootheid_Code",
    vw_waargenomen_eigenschap_db_id."Grootheid_Omschrijving",
    vw_waargenomen_eigenschap_db_id."ChemischeStof_code",
    vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving",
    vw_waargenomen_eigenschap_db_id."Hoedanigheid_Code",
    vw_waargenomen_eigenschap_db_id."Hoedanigheid_Omschrijving",
    classification_type.description AS "Oordeel",
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code",
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving",
    waarde_bepalings_methode_type.code AS "WaardebepalingsMethode_Code",
    waarde_bepalings_methode_type.omschrijving AS "WaardebepalingsMethode_Omschrijving",
        CASE
            WHEN (namespace_type.ns_id >= 99) THEN namespace_type.descrption
            ELSE vw_competent_authority."Omschrijving"
        END AS "Waterbeheerder_omschrijving",
    vw_oppervlaktewaterlichamen.omschrijving AS "Waterlichaam_naam",
        CASE
            WHEN (ssg_2907.ssge_id = 2907) THEN true
            ELSE false
        END AS "CHEMT_stof",
        CASE
            WHEN (ssg_3124.ssge_id = 3124) THEN true
            ELSE false
        END AS "PRIO2008_Norm2012",
        CASE
            WHEN (ssg_3148.ssge_id = 3148) THEN true
            ELSE false
        END AS "PRIOSTOF 34-45",
        CASE
            WHEN (ssg_3150.ssge_id = 3150) THEN true
            ELSE false
        END AS "UBIQUITAIR",
        CASE
            WHEN (ssg_3149.ssge_id = 3149) THEN true
            ELSE false
        END AS "NIET-UBIQUITAIR",
        CASE
            WHEN (krw_kwal."Is_van_toepassing" IS TRUE) THEN true
            ELSE false
        END AS "KWAL_ELEMENT",
    vw_oppervlaktewaterlichamen.referentietype AS "Waterlichaam_DoelType",
    vw_oppervlaktewaterlichamen.waterlichaam_status AS "Waterlichaam_Status",
    concat(vw_waargenomen_eigenschap_db_id."Typering_Omschrijving", vw_waargenomen_eigenschap_db_id."Grootheid_Omschrijving", ' ', vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving") AS "BeoordeeldeParameter",
        CASE
            WHEN (ssg_2908.ssge_id = 3155) THEN true
            ELSE false
        END AS "SVS_stof"
   FROM ((((((((((((((((public.swb_yearly_status
     LEFT JOIN public.vw_oppervlaktewaterlichamen ON ((vw_oppervlaktewaterlichamen.db_id = swb_yearly_status.swb_id)))
     LEFT JOIN public.sw_chemical_substance_status ON ((sw_chemical_substance_status.sys_id = swb_yearly_status.sys_id)))
     LEFT JOIN public.classification_type ON ((sw_chemical_substance_status.swcs_id = classification_type.cls_id)))
     LEFT JOIN public.vw_waargenomen_eigenschap_db_id ON ((vw_waargenomen_eigenschap_db_id."DB_ID" = sw_chemical_substance_status.opt_id)))
     LEFT JOIN public.waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = sw_chemical_substance_status.wbpm_id)))
     LEFT JOIN public.eu_monitoring_goal_type ON ((eu_monitoring_goal_type.eumg_id = sw_chemical_substance_status.eumg_id)))
     LEFT JOIN public.namespace_type ON ((namespace_type.ns_id = vw_oppervlaktewaterlichamen.waterbeheerder_code)))
     LEFT JOIN ( SELECT substance_group_element.chs_id,
            substance_group_element.ssge_id
           FROM public.substance_group_element
          WHERE (substance_group_element.ssge_id = 2907)) ssg_2907 ON ((ssg_2907.chs_id = vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID")))
     LEFT JOIN ( SELECT substance_group_element.chs_id,
            substance_group_element.ssge_id
           FROM public.substance_group_element
          WHERE (substance_group_element.ssge_id = 3155)) ssg_2908 ON ((ssg_2908.chs_id = vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID")))
     LEFT JOIN ( SELECT substance_group_element.chs_id,
            substance_group_element.ssge_id
           FROM public.substance_group_element
          WHERE (substance_group_element.ssge_id = 3148)) ssg_3148 ON ((ssg_3148.chs_id = vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID")))
     LEFT JOIN ( SELECT substance_group_element.chs_id,
            substance_group_element.ssge_id
           FROM public.substance_group_element
          WHERE (substance_group_element.ssge_id = 3124)) ssg_3124 ON ((ssg_3124.chs_id = vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID")))
     LEFT JOIN ( SELECT substance_group_element.chs_id,
            substance_group_element.ssge_id
           FROM public.substance_group_element
          WHERE (substance_group_element.ssge_id = 3150)) ssg_3150 ON ((ssg_3150.chs_id = vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID")))
     LEFT JOIN ( SELECT substance_group_element.chs_id,
            substance_group_element.ssge_id
           FROM public.substance_group_element
          WHERE (substance_group_element.ssge_id = 3149)) ssg_3149 ON ((ssg_3149.chs_id = vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID")))
     LEFT JOIN public.vw_krw_kwaliteitselement krw_kwal ON ((((krw_kwal."Watertype_Code")::text = (vw_oppervlaktewaterlichamen.referentietype)::text) AND (krw_kwal."Waargenomen_Eigenschap_DB_ID" = vw_waargenomen_eigenschap_db_id."DB_ID"))))
     JOIN public.competentauthority_namespacetype ON ((competentauthority_namespacetype.ns_id = vw_oppervlaktewaterlichamen.waterbeheerder_code)))
     JOIN public.vw_competent_authority ON ((vw_competent_authority."DB_ID" = competentauthority_namespacetype.ore_id)))
  WHERE ((eu_monitoring_goal_type.code)::text = 'TT_OM'::text)
UNION ALL
 SELECT swb_yearly_status.sys_id AS "Status_DB_ID",
    vw_oppervlaktewaterlichamen.identificatie AS "Waterlichaam_identificatie",
    vw_oppervlaktewaterlichamen.versie AS "Waterlichaam_Versie",
    vw_oppervlaktewaterlichamen.type AS "Waterlichaam_Type",
    "left"((vw_oppervlaktewaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    swb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap_db_id."DB_ID" AS "Waargenomen_Eigenschap_DB_ID",
    vw_waargenomen_eigenschap_db_id."Tpering_Code",
    vw_waargenomen_eigenschap_db_id."Typering_Omschrijving",
    vw_waargenomen_eigenschap_db_id."Grootheid_Code",
    vw_waargenomen_eigenschap_db_id."Grootheid_Omschrijving",
    vw_waargenomen_eigenschap_db_id."ChemischeStof_code",
    vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving",
    vw_waargenomen_eigenschap_db_id."Hoedanigheid_Code",
    vw_waargenomen_eigenschap_db_id."Hoedanigheid_Omschrijving",
    classification_type.description AS "Oordeel",
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code",
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving",
    waarde_bepalings_methode_type.code AS "WaardebepalingsMethode_Code",
    waarde_bepalings_methode_type.omschrijving AS "WaardebepalingsMethode_Omschrijving",
        CASE
            WHEN (namespace_type.ns_id >= 99) THEN namespace_type.descrption
            ELSE vw_competent_authority."Omschrijving"
        END AS "Waterbeheerder_omschrijving",
    vw_oppervlaktewaterlichamen.omschrijving AS "Waterlichaam_naam",
    false AS "CHEMT_stof",
    false AS "PRIO2008_Norm2012",
    false AS "PRIOSTOF 34-45",
    false AS "UBIQUITAIR",
    false AS "NIET-UBIQUITAIR",
        CASE
            WHEN (krw_kwal."Is_van_toepassing" IS TRUE) THEN true
            ELSE false
        END AS "KWAL_ELEMENT",
    vw_oppervlaktewaterlichamen.referentietype AS "Waterlichaam_DoelType",
    vw_oppervlaktewaterlichamen.waterlichaam_status AS "Waterlichaam_Status",
    concat(vw_waargenomen_eigenschap_db_id."Typering_Omschrijving", vw_waargenomen_eigenschap_db_id."Grootheid_Omschrijving", ' ', vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving") AS "BeoordeeldeParameter",
    false AS "SVS_stof"
   FROM ((((((((((public.swb_yearly_status
     LEFT JOIN public.vw_oppervlaktewaterlichamen ON ((vw_oppervlaktewaterlichamen.db_id = swb_yearly_status.swb_id)))
     LEFT JOIN public.sw_ecological_status ON ((sw_ecological_status.sys_id = swb_yearly_status.sys_id)))
     LEFT JOIN public.classification_type ON ((sw_ecological_status.swes_id = classification_type.cls_id)))
     LEFT JOIN public.vw_waargenomen_eigenschap_db_id ON ((vw_waargenomen_eigenschap_db_id."DB_ID" = sw_ecological_status.opt_id)))
     LEFT JOIN public.waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = sw_ecological_status.wbpm_id)))
     LEFT JOIN public.eu_monitoring_goal_type ON ((eu_monitoring_goal_type.eumg_id = sw_ecological_status.eumg_id)))
     LEFT JOIN public.namespace_type ON ((namespace_type.ns_id = vw_oppervlaktewaterlichamen.waterbeheerder_code)))
     LEFT JOIN public.vw_krw_kwaliteitselement krw_kwal ON ((((krw_kwal."Watertype_Code")::text = (vw_oppervlaktewaterlichamen.referentietype)::text) AND (krw_kwal."Waargenomen_Eigenschap_DB_ID" = vw_waargenomen_eigenschap_db_id."DB_ID"))))
     JOIN public.competentauthority_namespacetype ON ((competentauthority_namespacetype.ns_id = vw_oppervlaktewaterlichamen.waterbeheerder_code)))
     JOIN public.vw_competent_authority ON ((vw_competent_authority."DB_ID" = competentauthority_namespacetype.ore_id)))
  WHERE ((eu_monitoring_goal_type.code)::text = 'TT_OM'::text);


--
-- Name: vw_oordeel_totaal_owm_for_crystal_reports; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_oordeel_totaal_owm_for_crystal_reports AS
 WITH ssg AS (
         SELECT substance_group_element.chs_id,
            substance_group_element.ssge_id,
                CASE
                    WHEN (substance_group_element.ssge_id = 2908) THEN 'SVS_STOF'::text
                    ELSE
                    CASE
                        WHEN (substance_group_element.ssge_id = 3150) THEN 'UBIQUITAIR'::text
                        ELSE 'NIET_UBIQUITAIR'::text
                    END
                END AS "CATEGORIE"
           FROM public.substance_group_element
          WHERE (substance_group_element.ssge_id = ANY (ARRAY[2908, 3150, 3149]))
        ), krw_kwal AS (
         SELECT wfd_qe_swtype.name,
                CASE
                    WHEN (wfd_qe_swtype.part_of_opt_id = 4477) THEN 'CHEMT'::text
                    ELSE
                    CASE
                        WHEN (wfd_qe_swtype.part_of_opt_id = ANY (ARRAY[4480, 4514])) THEN 'FYSCHEM'::text
                        ELSE
                        CASE
                            WHEN (wfd_qe_swtype.part_of_opt_id = 4476) THEN 'BIOLT'::text
                            ELSE 'TOTAAL'::text
                        END
                    END
                END AS "CATEGORIE",
            wfd_qe_swtype.opt_id,
            wfd_qe_swtype.swt_id,
            wfd_qe_swtype.is_applicable
           FROM public.wfd_qe_swtype
        )
 SELECT swb_yearly_status.sys_id AS "DB_ID",
    hdo_swb.hdo_id AS "Waterlichaam_DB_ID",
    hdo_swb.inspire_id_local_id AS "Waterlichaam_identificatie",
    hdo_swb.wkrv_id AS "Waterlichaam_Versie",
    swb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap_db_id."DB_ID" AS "Waargenomen_Eigenschap_DB_ID",
        CASE
            WHEN (krw_kwal.name IS NOT NULL) THEN krw_kwal.name
            ELSE
            CASE
                WHEN (vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving" IS NOT NULL) THEN vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving"
                ELSE vw_waargenomen_eigenschap_db_id."Grootheid_Omschrijving"
            END
        END AS "Typering",
    classification_type.code AS "Oordeel_Code",
    classification_type.description AS "Oordeel",
        CASE
            WHEN ((eu_monitoring_goal_type.code)::text = 'TT_OM'::text) THEN 1
            WHEN ((eu_monitoring_goal_type.code)::text = 'OM'::text) THEN 2
            WHEN ((eu_monitoring_goal_type.code)::text = 'TT'::text) THEN 3
            WHEN (eu_monitoring_goal_type.code IS NULL) THEN 5
            ELSE 4
        END AS oordeel_type_sortorder,
    eu_monitoring_goal_type.eumg_id AS "Oordeel_Type_DB_ID",
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code",
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving",
        CASE
            WHEN ((waarde_bepalings_methode_type.code)::text = 'DESK'::text) THEN 1
            WHEN ((waarde_bepalings_methode_type.code)::text = 'AQUOKIT'::text) THEN 2
            WHEN (waarde_bepalings_methode_type.code IS NULL) THEN 4
            ELSE 3
        END AS waardebepalingsmethode_sortorder,
    waarde_bepalings_methode_type.code AS "WaardebepalingsMethode_Code",
    waarde_bepalings_methode_type.omschrijving AS "WaardebepalingsMethode_Omschrijving",
    (swb_yearly_status.comment_on_chemical_status)::character varying(5000) AS "Toelichting_ChemischeStatus",
    (swb_yearly_status.comment_on_ecological_status)::character varying(5000) AS "Toelichting_EcologischeStatus",
    (swb_yearly_status.motivation_4no_chemical_status)::character varying(5000) AS "Motivatie_Geen_ChemischeStatus",
    (swb_yearly_status.motivation_4no_ecological_status)::character varying(5000) AS "Motivatie_Geen_EcologischeStatus",
        CASE
            WHEN (krw_kwal.is_applicable = true) THEN (krw_kwal."CATEGORIE")::character varying(24)
            ELSE 'NVT'::character varying
        END AS "KWAL_ELEMENT",
    (ssg."CATEGORIE")::character varying(24) AS "STOF_CATEGORIE",
        CASE
            WHEN (wscm.scm_id IS NOT NULL) THEN 'A'::character varying(1)
            ELSE NULL::character varying
        END AS "Motivatie_Achteruitgang",
    krw_kwal.is_applicable
   FROM ((((((((((public.swb_yearly_status
     LEFT JOIN public.hydro_object hdo_swb ON ((hdo_swb.hdo_id = swb_yearly_status.swb_id)))
     LEFT JOIN public.surface_water_body swb ON ((swb.swb_id = hdo_swb.hdo_id)))
     LEFT JOIN public.sw_chemical_substance_status ON ((sw_chemical_substance_status.sys_id = swb_yearly_status.sys_id)))
     LEFT JOIN public.classification_type ON ((sw_chemical_substance_status.swcs_id = classification_type.cls_id)))
     LEFT JOIN public.vw_waargenomen_eigenschap_db_id ON ((vw_waargenomen_eigenschap_db_id."DB_ID" = sw_chemical_substance_status.opt_id)))
     LEFT JOIN public.waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = sw_chemical_substance_status.wbpm_id)))
     LEFT JOIN public.eu_monitoring_goal_type ON ((eu_monitoring_goal_type.eumg_id = sw_chemical_substance_status.eumg_id)))
     LEFT JOIN ssg ON ((vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID" = ssg.chs_id)))
     LEFT JOIN public.wb_status_change_motivation wscm ON (((wscm.wbd_id = hdo_swb.hdo_id) AND (wscm.opt_id = sw_chemical_substance_status.opt_id) AND (wscm.year_status = swb_yearly_status.year_of_status) AND (wscm.scm_id = 1))))
     FULL JOIN krw_kwal ON (((sw_chemical_substance_status.opt_id = krw_kwal.opt_id) AND (swb.sw_target_typology_id = krw_kwal.swt_id))))
UNION ALL
 SELECT swb_yearly_status.sys_id AS "DB_ID",
    hdo_swb.hdo_id AS "Waterlichaam_DB_ID",
    hdo_swb.inspire_id_local_id AS "Waterlichaam_identificatie",
    hdo_swb.wkrv_id AS "Waterlichaam_Versie",
    swb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap_db_id."DB_ID" AS "Waargenomen_Eigenschap_DB_ID",
        CASE
            WHEN (krw_kwal.name IS NOT NULL) THEN krw_kwal.name
            ELSE
            CASE
                WHEN (vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving" IS NOT NULL) THEN vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving"
                ELSE vw_waargenomen_eigenschap_db_id."Grootheid_Omschrijving"
            END
        END AS "Typering",
    classification_type.code AS "Oordeel_Code",
    classification_type.description AS "Oordeel",
        CASE
            WHEN ((eu_monitoring_goal_type.code)::text = 'TT_OM'::text) THEN 1
            WHEN ((eu_monitoring_goal_type.code)::text = 'OM'::text) THEN 2
            WHEN ((eu_monitoring_goal_type.code)::text = 'TT'::text) THEN 3
            WHEN (eu_monitoring_goal_type.code IS NULL) THEN 5
            ELSE 4
        END AS oordeel_type_sortorder,
    eu_monitoring_goal_type.eumg_id AS "Oordeel_Type_DB_ID",
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code",
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving",
        CASE
            WHEN ((waarde_bepalings_methode_type.code)::text = 'DESK'::text) THEN 1
            WHEN ((waarde_bepalings_methode_type.code)::text = 'AQUOKIT'::text) THEN 2
            WHEN (waarde_bepalings_methode_type.code IS NULL) THEN 4
            ELSE 3
        END AS waardebepalingsmethode_sortorder,
    waarde_bepalings_methode_type.code AS "WaardebepalingsMethode_Code",
    waarde_bepalings_methode_type.omschrijving AS "WaardebepalingsMethode_Omschrijving",
    (swb_yearly_status.comment_on_chemical_status)::character varying(5000) AS "Toelichting_ChemischeStatus",
    (swb_yearly_status.comment_on_ecological_status)::character varying(5000) AS "Toelichting_EcologischeStatus",
    (swb_yearly_status.motivation_4no_chemical_status)::character varying(5000) AS "Motivatie_Geen_ChemischeStatus",
    (swb_yearly_status.motivation_4no_ecological_status)::character varying(5000) AS "Motivatie_Geen_EcologischeStatus",
        CASE
            WHEN (krw_kwal.is_applicable = true) THEN (krw_kwal."CATEGORIE")::character varying(24)
            ELSE 'NVT'::character varying
        END AS "KWAL_ELEMENT",
    NULL::character varying(24) AS "STOF_CATEGORIE",
        CASE
            WHEN (wscm.scm_id IS NOT NULL) THEN 'A'::character varying(1)
            ELSE NULL::character varying
        END AS "Motivatie_Achteruitgang",
    krw_kwal.is_applicable
   FROM (((((((((public.swb_yearly_status
     LEFT JOIN public.hydro_object hdo_swb ON ((hdo_swb.hdo_id = swb_yearly_status.swb_id)))
     LEFT JOIN public.surface_water_body swb ON ((swb.swb_id = hdo_swb.hdo_id)))
     LEFT JOIN public.sw_ecological_status ON ((sw_ecological_status.sys_id = swb_yearly_status.sys_id)))
     LEFT JOIN public.classification_type ON ((sw_ecological_status.swes_id = classification_type.cls_id)))
     LEFT JOIN public.vw_waargenomen_eigenschap_db_id ON ((vw_waargenomen_eigenschap_db_id."DB_ID" = sw_ecological_status.opt_id)))
     LEFT JOIN public.waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = sw_ecological_status.wbpm_id)))
     LEFT JOIN public.eu_monitoring_goal_type ON ((eu_monitoring_goal_type.eumg_id = sw_ecological_status.eumg_id)))
     LEFT JOIN public.wb_status_change_motivation wscm ON (((wscm.wbd_id = hdo_swb.hdo_id) AND (wscm.opt_id = sw_ecological_status.opt_id) AND (wscm.year_status = swb_yearly_status.year_of_status) AND (wscm.scm_id = 1))))
     FULL JOIN krw_kwal ON (((sw_ecological_status.opt_id = krw_kwal.opt_id) AND (swb.sw_target_typology_id = krw_kwal.swt_id))))
  ORDER BY 1, 6, 13 DESC, 11 DESC;


--
-- Name: vw_oppervlaktewaterlichamen_dgrw; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_oppervlaktewaterlichamen_dgrw AS
 SELECT vw_oppervlaktewaterlichamen.identificatie AS "Waterlichaam_identificatie",
    vw_oppervlaktewaterlichamen.versie AS "Waterlichaam_Versie",
    vw_oppervlaktewaterlichamen.type AS "Waterlichaam_Type",
    vw_oppervlaktewaterlichamen.referentietype AS "Waterlichaam_DoelType",
    vw_oppervlaktewaterlichamen.waterlichaam_status AS "Waterlichaam_Status",
    "left"((vw_oppervlaktewaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    namespace_type.descrption AS "Waterbeheerder_omschrijving",
    vw_oppervlaktewaterlichamen.omschrijving AS "Waterlichaam_naam",
    surface_water_body.area_value AS oppervlakte,
    et1.omschrijving AS "oppervlakte.eenheid",
    surface_water_body.length_value AS lengte,
    et2.omschrijving AS "lengte.eenheid",
        CASE
            WHEN (vw_oppervlaktewaterlichamen.drinkwateronttrekking_jn = true) THEN 'Aanwezig'::text
            ELSE 'Afwezig'::text
        END AS "Drinkwateronttrekking"
   FROM ((((((public.surface_water_body
     LEFT JOIN public.vw_oppervlaktewaterlichamen ON ((surface_water_body.swb_id = vw_oppervlaktewaterlichamen.db_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = vw_oppervlaktewaterlichamen.waterbeheerder_code)))
     JOIN public.sw_typology_type type ON ((type.styp_id = surface_water_body.sw_typology_id)))
     JOIN public.eenheid_type et1 ON ((et1.eenh_id = surface_water_body.area_uom_id)))
     JOIN public.eenheid_type et2 ON ((et2.eenh_id = surface_water_body.length_uom_id)))
     JOIN public.sw_typology_type typer ON ((typer.styp_id = surface_water_body.sw_target_typology_id)))
  WHERE (vw_oppervlaktewaterlichamen.waterbeheerder_code >= 99)
UNION ALL
 SELECT vw_oppervlaktewaterlichamen.identificatie AS "Waterlichaam_identificatie",
    vw_oppervlaktewaterlichamen.versie AS "Waterlichaam_Versie",
    vw_oppervlaktewaterlichamen.type AS "Waterlichaam_Type",
    vw_oppervlaktewaterlichamen.referentietype AS "Waterlichaam_DoelType",
    vw_oppervlaktewaterlichamen.waterlichaam_status AS "Waterlichaam_Status",
    "left"((vw_oppervlaktewaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    vw_competent_authority."Omschrijving" AS "Waterbeheerder_omschrijving",
    vw_oppervlaktewaterlichamen.omschrijving AS "Waterlichaam_naam",
    surface_water_body.area_value AS oppervlakte,
    et1.omschrijving AS "oppervlakte.eenheid",
    surface_water_body.length_value AS lengte,
    et2.omschrijving AS "lengte.eenheid",
        CASE
            WHEN (vw_oppervlaktewaterlichamen.drinkwateronttrekking_jn = true) THEN 'Aanwezig'::text
            ELSE 'Afwezig'::text
        END AS "Drinkwateronttrekking"
   FROM (((((((public.surface_water_body
     LEFT JOIN public.vw_oppervlaktewaterlichamen ON ((surface_water_body.swb_id = vw_oppervlaktewaterlichamen.db_id)))
     JOIN public.competentauthority_namespacetype ON ((competentauthority_namespacetype.ns_id = vw_oppervlaktewaterlichamen.waterbeheerder_code)))
     JOIN public.vw_competent_authority ON ((vw_competent_authority."DB_ID" = competentauthority_namespacetype.ore_id)))
     JOIN public.sw_typology_type type ON ((type.styp_id = surface_water_body.sw_typology_id)))
     JOIN public.eenheid_type et1 ON ((et1.eenh_id = surface_water_body.area_uom_id)))
     JOIN public.eenheid_type et2 ON ((et2.eenh_id = surface_water_body.length_uom_id)))
     JOIN public.sw_typology_type typer ON ((typer.styp_id = surface_water_body.sw_target_typology_id)))
  WHERE (vw_oppervlaktewaterlichamen.waterbeheerder_code < 99);


--
-- Name: vw_ow_onttrekking_dgrw; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_ow_onttrekking_dgrw AS
 SELECT namespace_type.code AS "Namespace",
    hydro_object.inspire_id_local_id AS "LokaalID",
    hdo_names.name AS "Omschrijving",
    sw_abstraction_point.waterabstraction_from AS "OntrekkingsPunt",
    sw_abstraction_type.description AS "Onttrekking_Type",
    public.st_x((public.st_astext((public.st_dump(abstraction_point.geometry)).geom))::public.geometry) AS "RD_X",
    public.st_y((public.st_astext((public.st_dump(abstraction_point.geometry)).geom))::public.geometry) AS "RD_Y",
    hdo.identificatie AS "Waterlichaam_identificatie",
    hdo.omschrijving AS "Waterlichaam_naam",
    "left"((hdo.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    hdo.versie AS "Versie"
   FROM (((((((public.hydro_object
     LEFT JOIN public.hdo_names ON ((hdo_names.hdo_id = hydro_object.hdo_id)))
     JOIN public.abstraction_point ON ((hydro_object.hdo_id = abstraction_point.asp_id)))
     JOIN public.sw_abstraction_point ON ((hydro_object.hdo_id = sw_abstraction_point.sasp_id)))
     JOIN public.rewab_type ON ((rewab_type.rwb_id = sw_abstraction_point.rewab_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = hydro_object.inspire_id_namespace)))
     JOIN public.sw_abstraction_type ON ((sw_abstraction_type.swat_id = sw_abstraction_point.swat_id)))
     LEFT JOIN public.vw_oppervlaktewaterlichamen hdo ON ((hdo.db_id = abstraction_point.wbd_id)))
  ORDER BY hydro_object.inspire_id_local_id DESC;


--
-- Name: vw_prognose_gw_dgrw; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_prognose_gw_dgrw AS
 SELECT vw_grondwaterlichamen.identificatie AS "Waterlichaam_Identificatie",
    vw_grondwaterlichamen.versie AS "Waterlichaam_Versie",
    vw_grondwaterlichamen.is_buiten_deelstroomgebied AS "Buiten_StroomgebiedJN",
    vw_grondwaterlichamen.is_grensoverschrijdend AS "GrensoverschrijdendJN",
    vw_grondwaterlichamen.horizon AS "Waterlichaam_Horizon",
    vw_grondwaterlichamen.substraat AS "Waterlichaam_Substraat",
    namespace_type.descrption AS "Waterbeheerder_omschrijving",
    vw_grondwaterlichamen.omschrijving AS "Waterlichaam_naam",
    "left"((vw_grondwaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    wb_prognosis.year AS "PrognoseJaar",
    concat(vw_waargenomen_eigenschap."Typering_Omschrijving", vw_waargenomen_eigenschap."Grootheid_Omschrijving", ' ', vw_waargenomen_eigenschap."ChemischeStof_Omschrijving") AS "BeoordeeldeParameter",
    vw_waargenomen_eigenschap."Tpering_Code" AS "Typering.Code",
    vw_waargenomen_eigenschap."Typering_Omschrijving" AS "Typering.Omschrijving",
    vw_waargenomen_eigenschap."Grootheid_Code" AS "Grootheid.Code",
    vw_waargenomen_eigenschap."Grootheid_Omschrijving" AS "Grootheid.Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_code" AS "ChemischeStof.Code",
    vw_waargenomen_eigenschap."ChemischeStof_Omschrijving" AS "ChemischeStof.Omschrijving",
    vw_waargenomen_eigenschap."Hoedanigheid_Code" AS "Hoedanigheid.Code",
    vw_waargenomen_eigenschap."Hoedanigheid_Omschrijving" AS "Hoedanigheid.Omschrijving",
    classification_type.description AS "Prognose"
   FROM ((((public.wb_prognosis
     LEFT JOIN public.vw_grondwaterlichamen ON ((wb_prognosis.swb_id = vw_grondwaterlichamen.db_id)))
     LEFT JOIN public.vw_waargenomen_eigenschap ON ((vw_waargenomen_eigenschap."DB_ID" = wb_prognosis.opt_id)))
     LEFT JOIN public.classification_type ON ((classification_type.cls_id = wb_prognosis.cls_id)))
     LEFT JOIN public.namespace_type ON ((namespace_type.ns_id = vw_grondwaterlichamen.waterbeheerder_code)))
  WHERE (vw_grondwaterlichamen.versie IS NOT NULL);


--
-- Name: vw_prognose_ow_dgrw; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_prognose_ow_dgrw AS
 SELECT hydro_object.inspire_id_local_id AS "Waterlichaam.Identificatie",
    vw_oppervlaktewaterlichamen.versie AS "Waterlichaam_Versie",
    vw_oppervlaktewaterlichamen.type AS "Waterlichaam_Type",
    vw_oppervlaktewaterlichamen.referentietype AS "Waterlichaam_DoelType",
    vw_oppervlaktewaterlichamen.waterlichaam_status AS "Waterlichaam_Status",
    namespace_type.descrption AS "Waterbeheerder_omschrijving",
    vw_oppervlaktewaterlichamen.omschrijving AS "Waterlichaam_naam",
    "left"((vw_oppervlaktewaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    wb_prognosis.year AS "PrognoseJaar",
    concat(vw_waargenomen_eigenschap."Typering_Omschrijving", vw_waargenomen_eigenschap."Grootheid_Omschrijving", ' ', vw_waargenomen_eigenschap."ChemischeStof_Omschrijving") AS "BeoordeeldeParameter",
    vw_waargenomen_eigenschap."Tpering_Code" AS "Typering.Code",
    vw_waargenomen_eigenschap."Typering_Omschrijving" AS "Typering.Omschrijving",
    vw_waargenomen_eigenschap."Grootheid_Code" AS "Grootheid.Code",
    vw_waargenomen_eigenschap."Grootheid_Omschrijving" AS "Grootheid.Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_code" AS "ChemischeStof.Code",
    vw_waargenomen_eigenschap."ChemischeStof_Omschrijving" AS "ChemischeStof.Omschrijving",
    vw_waargenomen_eigenschap."Hoedanigheid_Code" AS "Hoedanigheid.Code",
    vw_waargenomen_eigenschap."Hoedanigheid_Omschrijving" AS "Hoedanigheid.Omschrijving",
    classification_type.description AS "Prognose"
   FROM (((((public.wb_prognosis
     LEFT JOIN public.hydro_object ON ((wb_prognosis.swb_id = hydro_object.hdo_id)))
     LEFT JOIN public.vw_waargenomen_eigenschap ON ((vw_waargenomen_eigenschap."DB_ID" = wb_prognosis.opt_id)))
     LEFT JOIN public.classification_type ON ((classification_type.cls_id = wb_prognosis.cls_id)))
     LEFT JOIN public.vw_oppervlaktewaterlichamen ON ((vw_oppervlaktewaterlichamen.db_id = hydro_object.hdo_id)))
     LEFT JOIN public.namespace_type ON ((namespace_type.ns_id = vw_oppervlaktewaterlichamen.waterbeheerder_code)))
  WHERE ((vw_oppervlaktewaterlichamen.versie IS NOT NULL) AND (vw_oppervlaktewaterlichamen.waterbeheerder_code >= 99))
UNION ALL
 SELECT hydro_object.inspire_id_local_id AS "Waterlichaam.Identificatie",
    vw_oppervlaktewaterlichamen.versie AS "Waterlichaam_Versie",
    vw_oppervlaktewaterlichamen.type AS "Waterlichaam_Type",
    vw_oppervlaktewaterlichamen.referentietype AS "Waterlichaam_DoelType",
    vw_oppervlaktewaterlichamen.waterlichaam_status AS "Waterlichaam_Status",
    vw_competent_authority."Omschrijving" AS "Waterbeheerder_omschrijving",
    vw_oppervlaktewaterlichamen.omschrijving AS "Waterlichaam_naam",
    "left"((vw_oppervlaktewaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    wb_prognosis.year AS "PrognoseJaar",
    concat(vw_waargenomen_eigenschap."Typering_Omschrijving", vw_waargenomen_eigenschap."Grootheid_Omschrijving", ' ', vw_waargenomen_eigenschap."ChemischeStof_Omschrijving") AS "BeoordeeldeParameter",
    vw_waargenomen_eigenschap."Tpering_Code" AS "Typering.Code",
    vw_waargenomen_eigenschap."Typering_Omschrijving" AS "Typering.Omschrijving",
    vw_waargenomen_eigenschap."Grootheid_Code" AS "Grootheid.Code",
    vw_waargenomen_eigenschap."Grootheid_Omschrijving" AS "Grootheid.Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_code" AS "ChemischeStof.Code",
    vw_waargenomen_eigenschap."ChemischeStof_Omschrijving" AS "ChemischeStof.Omschrijving",
    vw_waargenomen_eigenschap."Hoedanigheid_Code" AS "Hoedanigheid.Code",
    vw_waargenomen_eigenschap."Hoedanigheid_Omschrijving" AS "Hoedanigheid.Omschrijving",
    classification_type.description AS "Prognose"
   FROM ((((((public.wb_prognosis
     LEFT JOIN public.hydro_object ON ((wb_prognosis.swb_id = hydro_object.hdo_id)))
     LEFT JOIN public.vw_waargenomen_eigenschap ON ((vw_waargenomen_eigenschap."DB_ID" = wb_prognosis.opt_id)))
     LEFT JOIN public.classification_type ON ((classification_type.cls_id = wb_prognosis.cls_id)))
     LEFT JOIN public.vw_oppervlaktewaterlichamen ON ((vw_oppervlaktewaterlichamen.db_id = hydro_object.hdo_id)))
     JOIN public.competentauthority_namespacetype ON ((competentauthority_namespacetype.ns_id = vw_oppervlaktewaterlichamen.waterbeheerder_code)))
     JOIN public.vw_competent_authority ON ((vw_competent_authority."DB_ID" = competentauthority_namespacetype.ore_id)))
  WHERE ((vw_oppervlaktewaterlichamen.versie IS NOT NULL) AND (vw_oppervlaktewaterlichamen.waterbeheerder_code < 99));


--
-- Name: vw_toetsresultaat_owm; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_toetsresultaat_owm AS
 SELECT observation.obs_id AS "DB_ID",
    hydro_object.hdo_id AS "Waterlichaam_DB_ID",
    hydro_object.inspire_id_local_id AS "Waterlichaam_Identificatie",
    hydro_object.wkrv_id AS "Waterlichaam_Versie",
    swb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap."DB_ID" AS "Waargenomen_Eigenschap_DB_ID",
    vw_waargenomen_eigenschap."Tpering_Code",
    vw_waargenomen_eigenschap."Typering_Omschrijving",
    vw_waargenomen_eigenschap."Grootheid_Code",
    vw_waargenomen_eigenschap."Grootheid_Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_code",
    vw_waargenomen_eigenschap."ChemischeStof_Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_CASnr",
    waarde_bewerkings_methode_type.code AS "Waardebewerkingsmethode_Code",
    waarde_bewerkings_methode_type.omschrijving AS "Waardebewerkingsmethode_Omschrijving",
    observation.result_time AS "Begintijd_Toetsresultaat",
    observation.phenomenon_time_end AS "Eindtijd_Toetsreeks",
    observation.phenomenon_time_begin AS "Begintijd_Toetsreeks",
    observation.numeric_value AS "Numerieke_waarde",
    observation.numeric_value_significance AS "Numerieke_waarde_Significantie",
    bepaling_grens_type.omschrijving AS "Bepalingsgrens",
    eenheid_type.code AS "Eenheid_Code",
    eenheid_type.omschrijving AS "Eenheid_Omschrijving",
    vw_waargenomen_eigenschap."Hoedanigheid_Code",
    vw_waargenomen_eigenschap."Hoedanigheid_Omschrijving",
    kwaliteitsoordeel_type.code AS "KwaliteitsOordeel_Code",
    kwaliteitsoordeel_type.description AS "KwaliteitsOordeel_Omschrijving",
    eu_monitoring_goal_type.eumg_id AS "Oordeel_Type_DB_ID",
    eu_monitoring_goal_type.code AS "Oordeel_Type_Code",
    eu_monitoring_goal_type.description AS "Oordeel_Type_Omschrijving",
    waarde_bepalings_methode_type.code AS "Waardebepalingsmethode_Code",
    waarde_bepalings_methode_type.omschrijving AS "Waardebepalingsmethode_Omschrijving"
   FROM (((((((((((public.observation
     LEFT JOIN public.vw_waargenomen_eigenschap ON ((vw_waargenomen_eigenschap."DB_ID" = observation.observed_property_type)))
     LEFT JOIN public.waarde_bewerkings_methode_type ON ((waarde_bewerkings_methode_type.wbm_id = observation.value_processing_method_id)))
     LEFT JOIN public.eenheid_type ON ((eenheid_type.eenh_id = observation.numeric_value_uom_id)))
     LEFT JOIN public.bepaling_grens_type ON ((bepaling_grens_type.bpg_id = observation.limit_symbol_id)))
     LEFT JOIN public.kwaliteitsoordeel_type ON ((kwaliteitsoordeel_type.kwod_id = observation.quality_indicator_id)))
     LEFT JOIN public.observation_process ON ((observation_process.obp_id = observation.result_nature_id)))
     LEFT JOIN public.waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = observation_process.wbpm_id)))
     LEFT JOIN public.sw_testing_conclusion ON ((sw_testing_conclusion.stcc_id = observation.obs_id)))
     LEFT JOIN public.swb_yearly_status ON ((swb_yearly_status.sys_id = sw_testing_conclusion.sys_id)))
     LEFT JOIN public.hydro_object ON ((hydro_object.hdo_id = swb_yearly_status.swb_id)))
     LEFT JOIN public.eu_monitoring_goal_type ON ((eu_monitoring_goal_type.eumg_id = sw_testing_conclusion.eumg_id)))
  WHERE (swb_yearly_status.year_of_status IS NOT NULL)
  ORDER BY hydro_object.hdo_id, vw_waargenomen_eigenschap."Tpering_Code", vw_waargenomen_eigenschap."Grootheid_Code", vw_waargenomen_eigenschap."ChemischeStof_code", swb_yearly_status.year_of_status;


--
-- Name: vw_trends_gwl_dgrw; Type: VIEW; Schema: sgbp_dgrw; Owner: -
--

CREATE VIEW vw_trends_gwl_dgrw AS
 SELECT vw_grondwaterlichamen.identificatie AS "Waterlichaam_Identificatie",
    vw_grondwaterlichamen.versie AS "Waterlichaam_Versie",
    vw_grondwaterlichamen.is_buiten_deelstroomgebied AS "Buiten_StroomgebiedJN",
    vw_grondwaterlichamen.is_grensoverschrijdend AS "GrensoverschrijdendJN",
    vw_grondwaterlichamen.horizon AS "Waterlichaam_Horizon",
    vw_grondwaterlichamen.substraat AS "Waterlichaam_Substraat",
    namespace_type.descrption AS "Waterbeheerder_omschrijving",
    vw_grondwaterlichamen.omschrijving AS "Waterlichaam_naam",
    "left"((vw_grondwaterlichamen.stroomgebieddistrict)::text, 4) AS "Stroomgebieddistrict",
    gwb_yearly_status.year_of_status AS "Rapportagejaar",
    vw_waargenomen_eigenschap."DB_ID" AS "Waargenomen_Eigenschap_DB_ID",
    vw_waargenomen_eigenschap."Grootheid_Code",
    vw_waargenomen_eigenschap."Grootheid_Omschrijving",
    vw_waargenomen_eigenschap."ChemischeStof_code",
    vw_waargenomen_eigenschap."ChemischeStof_Omschrijving",
    vw_waargenomen_eigenschap."Hoedanigheid_Code",
    vw_waargenomen_eigenschap."Hoedanigheid_Omschrijving",
    concat(vw_waargenomen_eigenschap."Typering_Omschrijving", vw_waargenomen_eigenschap."Grootheid_Omschrijving", ' ', vw_waargenomen_eigenschap."ChemischeStof_Omschrijving") AS "BeoordeeldeParameter",
    classification_type.description AS "TypeTrend",
    pollutant_trend.depth_m AS "TrendDiepte"
   FROM (((((public.pollutant_trend
     LEFT JOIN public.gwb_yearly_status ON ((gwb_yearly_status.gys_id = pollutant_trend.gwys_id)))
     LEFT JOIN public.vw_grondwaterlichamen ON ((vw_grondwaterlichamen.db_id = gwb_yearly_status.gwb_id)))
     LEFT JOIN public.vw_waargenomen_eigenschap ON ((vw_waargenomen_eigenschap."DB_ID" = pollutant_trend.opt_id)))
     LEFT JOIN public.classification_type ON ((pollutant_trend.cls_id = classification_type.cls_id)))
     LEFT JOIN public.namespace_type ON ((namespace_type.ns_id = vw_grondwaterlichamen.waterbeheerder_code)));



--
-- Name: eu_stoffenlijst; Type: TABLE; Schema: wfd_reporting; Owner: -
--

CREATE TABLE eu_stoffenlijst (
    domeinnaam character varying,
    value character varying,
    grootheidcode character varying,
    parametercode character varying,
    "swFailingRBSPOther" character varying
);


--
-- Name: geological_formation_enum; Type: TABLE; Schema: wfd_reporting; Owner: -
--

CREATE TABLE geological_formation_enum (
    nl_id integer NOT NULL,
    eu_xsd text
);


--
-- Name: ktm_enum; Type: TABLE; Schema: wfd_reporting; Owner: -
--

CREATE TABLE ktm_enum (
    smeu_id integer,
    eu_xsd character varying
);


--
-- Name: missing_qe; Type: TABLE; Schema: wfd_reporting; Owner: -
--

CREATE TABLE missing_qe (
    qetype character varying
);


--
-- Name: significant_impact_enum; Type: TABLE; Schema: wfd_reporting; Owner: -
--

CREATE TABLE significant_impact_enum (
    eu_xsd text,
    i_id integer
);


--
-- Name: significant_pressure_enum; Type: TABLE; Schema: wfd_reporting; Owner: -
--

CREATE TABLE significant_pressure_enum (
    eu_xsd text NOT NULL,
    gw_p_id integer,
    sw_p_id integer
);


--
-- Name: swb_unknown_ps; Type: TABLE; Schema: wfd_reporting; Owner: -
--

CREATE TABLE swb_unknown_ps (
    local_id character varying(100)
);


--
-- Name: vw__monitoring_site; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw__monitoring_site AS
 WITH mpn AS (
         SELECT DISTINCT ON (monprog_2015.mpnident, monprog_2015.montype) monprog_2015.mpnident,
            monprog_2015.ligt_in_owm,
                CASE
                    WHEN ((monprog_2015.rd_x IS NULL) OR (monprog_2015.rd_x = (0)::double precision)) THEN ((155000)::numeric)::double precision
                    ELSE monprog_2015.rd_x
                END AS rd_x,
                CASE
                    WHEN ((monprog_2015.rd_y IS NULL) OR (monprog_2015.rd_x = (0)::double precision)) THEN ((463000)::numeric)::double precision
                    ELSE monprog_2015.rd_y
                END AS rd_y,
            monprog_2015.parameter,
            monprog_2015.monfreq,
            monprog_2015.moncyclus,
            monprog_2015.monsoort,
            monprog_2015.repr_owm,
            monprog_2015.mpntype,
            monprog_2015.diepte,
            monprog_2015.montype,
            monprog_2015.sinds,
            monprog_2015.tot,
            monprog_2015.naam
           FROM eu_state_of_environment.monprog_2015
          ORDER BY monprog_2015.mpnident
        ), mpn_eu AS (
         SELECT public.st_transform(public.st_geomfromtext((((('POINT('::text || mpn.rd_x) || ' '::text) || mpn.rd_y) || ')'::text), 28992), 4258) AS geometry,
                CASE
                    WHEN ("substring"((mpn.mpnident)::text, 1, 2) <> 'NL'::text) THEN 'NL.WBHCODE.99'::text
                    ELSE ('NL.WBHCODE.'::text || "substring"((mpn.mpnident)::text, 3, 2))
                END AS namespace,
                CASE
                    WHEN ("substring"((mpn.mpnident)::text, 1, 2) <> 'NL'::text) THEN (mpn.mpnident)::text
                    ELSE "substring"((mpn.mpnident)::text, 6)
                END AS localid,
            '13'::text AS version,
                CASE
                    WHEN ("substring"((mpn.mpnident)::text, 1, 2) <> 'NL'::text) THEN replace((('NL0-'::text || upper(('NL_'::text || (mpn.mpnident)::text))) || '-M'::text), '.'::text, '_'::text)
                    ELSE replace((('NL0-'::text || upper((mpn.mpnident)::text)) || '-M'::text), '.'::text, '_'::text)
                END AS thematicid,
            'euMonitoringSiteCode'::text AS themaidsch,
            ((mpn.sinds)::text || '-01-01'::text) AS beginlife,
            ((mpn.tot)::text || '-12-31'::text) AS endlife,
            NULL::text AS predecesid,
            NULL::text AS predeidsch,
            NULL::text AS successoid,
            NULL::text AS succeidsch,
            'noChange'::text AS wevolution,
            NULL::text AS nametxtint,
            mpn.naam AS nametext,
            'dut'::text AS nametxtlan,
            ((mpn.sinds)::text || '-01-01'::text) AS opactbegin,
            ((mpn.tot)::text || '-12-31'::text) AS opactend,
            NULL::text AS rsiteid,
            NULL::text AS rsiteidsch,
                CASE
                    WHEN ((mpn.montype)::text = 'ow'::text) THEN (('NL0-'::text || upper((mpn.repr_owm)::text)) || '-O'::text)
                    ELSE (('NL0-'::text || upper((mpn.repr_owm)::text)) || '-G'::text)
                END AS foiid,
                CASE
                    WHEN ((mpn.montype)::text = 'ow'::text) THEN 'euSurfaceWaterBodyCode'::text
                    ELSE 'euGroundWaterBodyCode'::text
                END AS foidsch,
            'FALSE'::text AS mediabiota,
            'TRUE'::text AS mediawater,
            'FALSE'::text AS mediasedim,
            array_to_string(ARRAY( SELECT DISTINCT
                        CASE mp.monsoort
                            WHEN 'OM'::text THEN 'OPE'::text
                            WHEN 'TT'::text THEN 'SUR'::text
                            ELSE NULL::text
                        END AS regexp_replace
                   FROM mpn mp
                  WHERE ((mp.mpnident)::text = (mpn.mpnident)::text)), ','::text) AS purpose,
            'F'::text AS confstatus,
            'http://www.waterkwaliteitsportaal.nl'::text AS link,
            0.01 AS catcharea
           FROM mpn
        )
 SELECT DISTINCT ON (mpn_eu.namespace, mpn_eu.localid, mpn_eu.version) mpn_eu.geometry,
    mpn_eu.namespace,
    mpn_eu.localid,
    mpn_eu.version,
    mpn_eu.thematicid,
    mpn_eu.themaidsch,
    mpn_eu.beginlife,
    mpn_eu.endlife,
    mpn_eu.predecesid,
    mpn_eu.predeidsch,
    mpn_eu.successoid,
    mpn_eu.succeidsch,
    mpn_eu.wevolution,
    mpn_eu.nametxtint,
    mpn_eu.nametext,
    mpn_eu.nametxtlan,
    mpn_eu.opactbegin,
    mpn_eu.opactend,
    mpn_eu.rsiteid,
    mpn_eu.rsiteidsch,
    mpn_eu.foiid,
    mpn_eu.foidsch,
    mpn_eu.mediabiota,
    mpn_eu.mediawater,
    mpn_eu.mediasedim,
    mpn_eu.purpose,
    mpn_eu.confstatus,
    mpn_eu.link,
    mpn_eu.catcharea
   FROM mpn_eu
  ORDER BY mpn_eu.namespace, mpn_eu.localid, mpn_eu.version, mpn_eu.foiid;


--
-- Name: vw__river_basin_district; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw__river_basin_district AS
 SELECT DISTINCT ON (hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id) mrz.geometry_etrs89 AS geometry,
    ('NL.WBHCODE.'::text || hydro_object.inspire_id_namespace) AS namespace,
    hydro_object.inspire_id_local_id AS localid,
    hydro_object.wkrv_id AS inspireid_version_id,
    "left"((hydro_object.inspire_id_local_id)::text, 4) AS thematicid,
    'euRBDCode'::text AS themaidsch,
    to_char(wkr_version.begin_lifespan_version, 'YYYY-MM-DD'::text) AS beginlife,
    to_char(wkr_version.end_lifespan_version, 'YYYY-MM-DD'::text) AS endlife,
    "left"((hydro_object.inspire_id_local_id)::text, 4) AS predecesid,
    'euRBDCode'::text AS predeidsch,
    NULL::text AS successoid,
    'euRBDCode'::text AS succeidsch,
    'noChange'::text AS wevolution,
    NULL::text AS nametxtint,
    hdo_names.name AS nametext,
    'dut'::text AS nametxtlan,
    to_char(mrz.begin_designation, 'YYYY-MM-DD'::text) AS desigbegin,
        CASE
            WHEN (mrz.end_designation IS NULL) THEN NULL::text
            ELSE to_char(mrz.end_designation, 'YYYY-MM-DD'::text)
        END AS desigend,
    'riverBasinDistrict'::text AS zonetype,
    'http://www.waterkwaliteitsportaal.nl'::text AS link
   FROM (((((((public.management_regulation_or_restriction_zone mrz
     LEFT JOIN public.river_basin_disrict ON (((mrz.mrz_id = river_basin_disrict.rbd_id) AND (river_basin_disrict.international_rbd IS NOT NULL))))
     JOIN public.hydro_object ON ((hydro_object.hdo_id = mrz.mrz_id)))
     LEFT JOIN public.hdo_names ON ((hdo_names.hdo_id = mrz.mrz_id)))
     JOIN public.wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = hydro_object.inspire_id_namespace)))
     LEFT JOIN public.competentauthority_namespacetype ON ((namespace_type.ns_id = competentauthority_namespacetype.ns_id)))
     LEFT JOIN public.organisatorische_eenheid ON ((organisatorische_eenheid.ore_id = competentauthority_namespacetype.ore_id)))
  WHERE (((hydro_object.hydro_object_type)::text = 'RiverBasinDistrict'::text) AND (hydro_object.wkrv_id = 13) AND ((hydro_object.inspire_id_local_id)::text ~~ 'NL%'::text) AND (river_basin_disrict.international_rbd IS NOT NULL))
  ORDER BY hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id DESC;


--
-- Name: vw__shape_ground_water_body; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw__shape_ground_water_body AS
 SELECT DISTINCT ON (hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id) mrz.geometry_etrs89 AS geometry,
    ('NL.WBHCODE.'::text || hydro_object.inspire_id_namespace) AS namespace,
    hydro_object.inspire_id_local_id AS localid,
    hydro_object.wkrv_id AS inspireid_version_id,
    (('NL0-'::text || upper((hydro_object.inspire_id_local_id)::text)) || '-G'::text) AS thematicid,
    'euGroundWaterBodyCode'::text AS themaidsch,
    to_char(wkr_version.begin_lifespan_version, 'YYYY-MM-DD'::text) AS beginlife,
    to_char(wkr_version.end_lifespan_version, 'YYYY-MM-DD'::text) AS endlife,
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace((ho2009.inspire_id_local_id)::text, '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM ((public.hydro_object ho
             JOIN public.related_hydro_object rho ON (((rho.from_hdo = ho.hdo_id) AND (rho.rrt_id = 2) AND (ho.wkrv_id = 13))))
             JOIN public.hydro_object ho2009 ON (((rho.to_hdo = ho2009.hdo_id) AND (ho2009.wkrv_id = 2))))
          WHERE (ho.hdo_id = hydro_object.hdo_id)), ','::text) AS predecesid,
    'euGroundWaterBodyCode'::text AS predeidsch,
    NULL::text AS successoid,
    NULL::text AS succeidsch,
    'noChange'::text AS wevolution,
    NULL::text AS nametxtint,
    hdo_names.name AS nametext,
    'dut'::text AS nametxtlan,
    to_char(mrz.begin_designation, 'YYYY-MM-DD'::text) AS desigbegin,
        CASE
            WHEN (mrz.end_designation IS NULL) THEN NULL::text
            ELSE to_char(mrz.end_designation, 'YYYY-MM-DD'::text)
        END AS desigend,
    'waterBody'::text AS zonetype,
    'groundWaterBody'::text AS spzonetype,
    (('NL0-'::text || "left"((hdo_rbd.inspire_id_local_id)::text, 4)) || '-S'::text) AS rzoneid,
    'NotApplicable'::text AS rtrnsid,
    'euRBDCode'::text AS rzoneidsch,
    'NotApplicable'::text AS rtrnsidsch,
    'http://www.waterkwaliteitsportaal.nl'::text AS link,
    ground_water_body.horizon_id AS horizon,
    ground_water_body.area AS sizevalue,
    'km2'::text AS sizeuom
   FROM (((((((((((public.management_regulation_or_restriction_zone mrz
     LEFT JOIN public.river_basin_disrict ON ((mrz.mrz_id = river_basin_disrict.rbd_id)))
     JOIN public.hydro_object ON ((hydro_object.hdo_id = mrz.mrz_id)))
     LEFT JOIN public.hdo_names ON ((hdo_names.hdo_id = mrz.mrz_id)))
     JOIN public.wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = hydro_object.inspire_id_namespace)))
     LEFT JOIN public.competentauthority_namespacetype ON ((namespace_type.ns_id = competentauthority_namespacetype.ns_id)))
     LEFT JOIN public.organisatorische_eenheid ON ((organisatorische_eenheid.ore_id = competentauthority_namespacetype.ore_id)))
     LEFT JOIN public.water_body ON ((water_body.wbd_id = hydro_object.hdo_id)))
     LEFT JOIN public.river_basin_disrict rbd ON ((rbd.rbd_id = water_body.rbd_id)))
     LEFT JOIN public.hydro_object hdo_rbd ON ((rbd.rbd_id = hdo_rbd.hdo_id)))
     LEFT JOIN public.ground_water_body ON ((ground_water_body.gwb_id = hydro_object.hdo_id)))
  WHERE (((hydro_object.hydro_object_type)::text = 'GroundWaterBody'::text) AND (hydro_object.wkrv_id = 13))
  ORDER BY hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id DESC;


--
-- Name: vw__shape_ground_water_body_horizon; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw__shape_ground_water_body_horizon AS
 SELECT DISTINCT ON (hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id) mrz.geometry_etrs89 AS geometry,
    (('NL0-'::text || upper((hydro_object.inspire_id_local_id)::text)) || '-G'::text) AS thematicid,
    'euGroundWaterBodyCode'::text AS themaidsch,
    ground_water_body.horizon_id AS horizon
   FROM (((public.management_regulation_or_restriction_zone mrz
     LEFT JOIN public.river_basin_disrict ON ((mrz.mrz_id = river_basin_disrict.rbd_id)))
     JOIN public.hydro_object ON ((hydro_object.hdo_id = mrz.mrz_id)))
     LEFT JOIN public.ground_water_body ON ((ground_water_body.gwb_id = hydro_object.hdo_id)))
  WHERE (((hydro_object.hydro_object_type)::text = 'GroundWaterBody'::text) AND (hydro_object.wkrv_id = 13))
  ORDER BY hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id DESC;


--
-- Name: vw__shape_protected_area_lijn; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw__shape_protected_area_lijn AS
 SELECT DISTINCT ON (hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id) mrz.geometry_etrs89 AS geometry,
    ('NL.WBHCODE.'::text || hydro_object.inspire_id_namespace) AS namespace,
    hydro_object.inspire_id_local_id AS localid,
    hydro_object.wkrv_id AS version,
    (('NL0-'::text || upper((hydro_object.inspire_id_local_id)::text)) || '-B'::text) AS thematicid,
    'euProtectedAreaCode'::text AS themaidsch,
    to_char(wkr_version.begin_lifespan_version, 'YYYY-MM-DD'::text) AS beginlife,
    to_char(wkr_version.end_lifespan_version, 'YYYY-MM-DD'::text) AS endlife,
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace(upper((ho2009.inspire_id_local_id)::text), '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM ((public.hydro_object ho
             JOIN public.related_hydro_object rho ON (((rho.from_hdo = ho.hdo_id) AND (rho.rrt_id = 2) AND (ho.wkrv_id = 13))))
             JOIN public.hydro_object ho2009 ON (((rho.to_hdo = ho2009.hdo_id) AND (ho2009.wkrv_id = 2))))
          WHERE (ho.hdo_id = hydro_object.hdo_id)), ','::text) AS predecesid,
    'euProtectedAreaCode'::text AS predeidsch,
    NULL::text AS successoid,
    'euProtectedAreaCode'::text AS succeidsch,
    'noChange'::text AS wevolution,
    NULL::text AS nametxtint,
    hdo_names.name AS nametext,
    'dut'::text AS nametxtlan,
    to_char(mrz.begin_designation, 'YYYY-MM-DD'::text) AS desigbegin,
        CASE
            WHEN (mrz.end_designation IS NULL) THEN NULL::text
            ELSE to_char(mrz.end_designation, 'YYYY-MM-DD'::text)
        END AS desigend,
    'designatedWaters'::text AS zonetype,
        CASE wfd_protected_area.protected_area_type_id
            WHEN 2 THEN 'freshwaterFishDesignatedWater'::text
            WHEN 3 THEN 'shellfishDesignatedWater'::text
            ELSE 'oeps'::text
        END AS spzonetype,
    'national'::text AS legislevel,
    'Directive EC/2000/60 of the European Parliament'::text AS legisname,
    'http://wetten.overheid.nl/zoeken'::text AS legislink,
    round(((public.st_length(mrz.geometry) / (1000000)::double precision))::numeric, 2) AS sizevalue,
    'km'::text AS sizeuom
   FROM ((((((((public.management_regulation_or_restriction_zone mrz
     LEFT JOIN public.river_basin_disrict ON ((mrz.mrz_id = river_basin_disrict.rbd_id)))
     JOIN public.hydro_object ON ((hydro_object.hdo_id = mrz.mrz_id)))
     LEFT JOIN public.hdo_names ON ((hdo_names.hdo_id = mrz.mrz_id)))
     JOIN public.wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = hydro_object.inspire_id_namespace)))
     LEFT JOIN public.competentauthority_namespacetype ON ((namespace_type.ns_id = competentauthority_namespacetype.ns_id)))
     LEFT JOIN public.organisatorische_eenheid ON ((organisatorische_eenheid.ore_id = competentauthority_namespacetype.ore_id)))
     JOIN public.wfd_protected_area ON ((wfd_protected_area.wpa_id = hydro_object.hdo_id)))
  WHERE (((hydro_object.hydro_object_type)::text = 'ProtectedArea'::text) AND (hydro_object.wkrv_id = 13) AND (public.geometrytype(mrz.geometry_etrs89) ~~ '%STRING'::text) AND (wfd_protected_area.protected_area_type_id = ANY (ARRAY[2, 3])))
UNION ALL
 SELECT DISTINCT ON (hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id) mrz.geometry_etrs89 AS geometry,
    ('NL.WBHCODE.'::text || hydro_object.inspire_id_namespace) AS namespace,
    hydro_object.inspire_id_local_id AS localid,
    hydro_object.wkrv_id AS version,
    (('NL0-'::text || upper((hydro_object.inspire_id_local_id)::text)) || '-D'::text) AS thematicid,
    'euProtectedAreaCode'::text AS themaidsch,
    to_char(wkr_version.begin_lifespan_version, 'YYYY-MM-DD'::text) AS beginlife,
    to_char(wkr_version.end_lifespan_version, 'YYYY-MM-DD'::text) AS endlife,
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace(upper((ho2009.inspire_id_local_id)::text), '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM ((public.hydro_object ho
             JOIN public.related_hydro_object rho ON (((rho.from_hdo = ho.hdo_id) AND (rho.rrt_id = 2) AND (ho.wkrv_id = 13))))
             JOIN public.hydro_object ho2009 ON (((rho.to_hdo = ho2009.hdo_id) AND (ho2009.wkrv_id = 2))))
          WHERE (ho.hdo_id = hydro_object.hdo_id)), ','::text) AS predecesid,
    'euProtectedAreaCode'::text AS predeidsch,
    NULL::text AS successoid,
    NULL::text AS succeidsch,
    'noChange'::text AS wevolution,
    NULL::text AS nametxtint,
    hdo_names.name AS nametext,
    'dut'::text AS nametxtlan,
    to_char(mrz.begin_designation, 'YYYY-MM-DD'::text) AS desigbegin,
        CASE
            WHEN (mrz.end_designation IS NULL) THEN NULL::text
            ELSE to_char(mrz.end_designation, 'YYYY-MM-DD'::text)
        END AS desigend,
    'drinkingWaterProtectionArea'::text AS zonetype,
    'riverWaterBody'::text AS spzonetype,
    'national'::text AS legislevel,
    'Directive EC/2000/60 of the European Parliament'::text AS legisname,
    'http://wetten.overheid.nl/zoeken'::text AS legislink,
    round(((public.st_length(mrz.geometry) / (1000000)::double precision))::numeric, 4) AS sizevalue,
    'km2'::text AS sizeuom
   FROM ((((((((public.management_regulation_or_restriction_zone mrz
     LEFT JOIN public.river_basin_disrict ON ((mrz.mrz_id = river_basin_disrict.rbd_id)))
     JOIN public.hydro_object ON ((hydro_object.hdo_id = mrz.mrz_id)))
     LEFT JOIN public.hdo_names ON ((hdo_names.hdo_id = mrz.mrz_id)))
     JOIN public.wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = hydro_object.inspire_id_namespace)))
     LEFT JOIN public.competentauthority_namespacetype ON ((namespace_type.ns_id = competentauthority_namespacetype.ns_id)))
     LEFT JOIN public.organisatorische_eenheid ON ((organisatorische_eenheid.ore_id = competentauthority_namespacetype.ore_id)))
     JOIN public.water_body ON ((water_body.wbd_id = hydro_object.hdo_id)))
  WHERE (((hydro_object.hydro_object_type)::text = ANY (ARRAY['GroundWaterBody'::text, 'SurfaceWaterBody'::text])) AND (water_body.is_article_7 IS TRUE) AND (hydro_object.wkrv_id = 13) AND (public.geometrytype(mrz.geometry_etrs89) ~~ '%STRING'::text))
  ORDER BY 2, 3, 4 DESC;


--
-- Name: vw__shape_protected_area_punt; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw__shape_protected_area_punt AS
 SELECT DISTINCT ON (hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id) mrz.geometry_etrs89 AS geometry,
    ('NL.WBHCODE.'::text || hydro_object.inspire_id_namespace) AS namespace,
    replace((hydro_object.inspire_id_local_id)::text, ' '::text, '_'::text) AS localid,
    hydro_object.wkrv_id AS version,
    replace((('NL0-'::text || upper((hydro_object.inspire_id_local_id)::text)) || '-Z'::text), ' '::text, '_'::text) AS thematicid,
    'euProtectedAreaCode'::text AS themaidsch,
    to_char(wkr_version.begin_lifespan_version, 'YYYY-MM-DD'::text) AS beginlife,
    to_char(wkr_version.end_lifespan_version, 'YYYY-MM-DD'::text) AS endlife,
    NULL::text AS predecesid,
    NULL::text AS predeidsch,
    NULL::text AS successoid,
    NULL::text AS succeidsch,
    'noChange'::text AS wevolution,
    NULL::text AS nametxtint,
    replace(replace((hdo_names.name)::text, '''t'::text, 'het'::text), '''T'::text, 'Het'::text) AS nametext,
    'dut'::text AS nametxtlan,
    to_char(mrz.begin_designation, 'YYYY-MM-DD'::text) AS desigbegin,
        CASE
            WHEN (mrz.end_designation IS NULL) THEN NULL::text
            ELSE to_char(mrz.end_designation, 'YYYY-MM-DD'::text)
        END AS desigend,
    'bathingWaters'::text AS zonetype,
    'otherProtectedArea'::text AS spzonetype,
    'national'::text AS legislevel,
    'Directive EC/2000/60 of the European Parliament'::text AS legisname,
    'http://www.wetten.nl'::text AS legislink,
    0.01 AS sizevalue,
    'km2'::text AS sizeuom
   FROM ((((((((public.management_regulation_or_restriction_zone mrz
     LEFT JOIN public.river_basin_disrict ON ((mrz.mrz_id = river_basin_disrict.rbd_id)))
     JOIN public.hydro_object ON ((hydro_object.hdo_id = mrz.mrz_id)))
     LEFT JOIN public.hdo_names ON ((hdo_names.hdo_id = mrz.mrz_id)))
     JOIN public.wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = hydro_object.inspire_id_namespace)))
     LEFT JOIN public.competentauthority_namespacetype ON ((namespace_type.ns_id = competentauthority_namespacetype.ns_id)))
     LEFT JOIN public.organisatorische_eenheid ON ((organisatorische_eenheid.ore_id = competentauthority_namespacetype.ore_id)))
     JOIN public.wfd_protected_area ON ((wfd_protected_area.wpa_id = hydro_object.hdo_id)))
  WHERE (((hydro_object.hydro_object_type)::text = 'ProtectedArea'::text) AND (hydro_object.wkrv_id = 13) AND (public.geometrytype(mrz.geometry_etrs89) ~~ '%POINT'::text) AND (wfd_protected_area.protected_area_type_id = 7))
  ORDER BY hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id DESC;


--
-- Name: vw__shape_protected_area_vlak; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw__shape_protected_area_vlak AS
 SELECT DISTINCT ON (hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id) mrz.geometry_etrs89 AS geometry,
    ('NL.WBHCODE.'::text || hydro_object.inspire_id_namespace) AS namespace,
    hydro_object.inspire_id_local_id AS localid,
    hydro_object.wkrv_id AS version,
    (('NL0-'::text || upper((hydro_object.inspire_id_local_id)::text)) || '-B'::text) AS thematicid,
    'euProtectedAreaCode'::text AS themaidsch,
    to_char(wkr_version.begin_lifespan_version, 'YYYY-MM-DD'::text) AS beginlife,
    to_char(wkr_version.end_lifespan_version, 'YYYY-MM-DD'::text) AS endlife,
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace((ho2009.inspire_id_local_id)::text, '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM ((public.hydro_object ho
             JOIN public.related_hydro_object rho ON (((rho.from_hdo = ho.hdo_id) AND (rho.rrt_id = 2) AND (ho.wkrv_id = 13))))
             JOIN public.hydro_object ho2009 ON (((rho.to_hdo = ho2009.hdo_id) AND (ho2009.wkrv_id = 2))))
          WHERE (ho.hdo_id = hydro_object.hdo_id)), ','::text) AS predecesid,
    'euProtectedAreaCode'::text AS predeidsch,
    NULL::text AS successoid,
    NULL::text AS succeidsch,
    'noChange'::text AS wevolution,
    NULL::text AS nametxtint,
    hdo_names.name AS nametext,
    'dut'::text AS nametxtlan,
    to_char(mrz.begin_designation, 'YYYY-MM-DD'::text) AS desigbegin,
        CASE
            WHEN (mrz.end_designation IS NULL) THEN NULL::text
            ELSE to_char(mrz.end_designation, 'YYYY-MM-DD'::text)
        END AS desigend,
    'designatedWaters'::text AS zonetype,
        CASE wfd_protected_area.protected_area_type_id
            WHEN 2 THEN 'freshwaterFishDesignatedWater'::text
            WHEN 3 THEN 'shellfishDesignatedWater'::text
            ELSE 'oeps'::text
        END AS spzonetype,
    'national'::text AS legislevel,
    'Directive EC/2000/60 of the European Parliament'::text AS legisname,
    'http://wetten.overheid.nl/zoeken'::text AS legislink,
    round(((public.st_area(mrz.geometry) / (1000000)::double precision))::numeric, 2) AS sizevalue,
    'km2'::text AS sizeuom
   FROM ((((((((public.management_regulation_or_restriction_zone mrz
     LEFT JOIN public.river_basin_disrict ON ((mrz.mrz_id = river_basin_disrict.rbd_id)))
     JOIN public.hydro_object ON ((hydro_object.hdo_id = mrz.mrz_id)))
     LEFT JOIN public.hdo_names ON ((hdo_names.hdo_id = mrz.mrz_id)))
     JOIN public.wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = hydro_object.inspire_id_namespace)))
     LEFT JOIN public.competentauthority_namespacetype ON ((namespace_type.ns_id = competentauthority_namespacetype.ns_id)))
     LEFT JOIN public.organisatorische_eenheid ON ((organisatorische_eenheid.ore_id = competentauthority_namespacetype.ore_id)))
     JOIN public.wfd_protected_area ON ((wfd_protected_area.wpa_id = hydro_object.hdo_id)))
  WHERE (((hydro_object.hydro_object_type)::text = 'ProtectedArea'::text) AND (hydro_object.wkrv_id = 13) AND (public.geometrytype(mrz.geometry_etrs89) ~~ '%POLYGON'::text) AND (wfd_protected_area.protected_area_type_id = ANY (ARRAY[2, 3])))
UNION ALL
 SELECT DISTINCT ON (hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id) mrz.geometry_etrs89 AS geometry,
    ('NL.WBHCODE.'::text || hydro_object.inspire_id_namespace) AS namespace,
    hydro_object.inspire_id_local_id AS localid,
    hydro_object.wkrv_id AS version,
    (('NL0-'::text || upper((hydro_object.inspire_id_local_id)::text)) || '-D'::text) AS thematicid,
    'euProtectedAreaCode'::text AS themaidsch,
    to_char(wkr_version.begin_lifespan_version, 'YYYY-MM-DD'::text) AS beginlife,
    to_char(wkr_version.end_lifespan_version, 'YYYY-MM-DD'::text) AS endlife,
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace((ho2009.inspire_id_local_id)::text, '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM ((public.hydro_object ho
             JOIN public.related_hydro_object rho ON (((rho.from_hdo = ho.hdo_id) AND (rho.rrt_id = 2) AND (ho.wkrv_id = 13))))
             JOIN public.hydro_object ho2009 ON (((rho.to_hdo = ho2009.hdo_id) AND (ho2009.wkrv_id = 2))))
          WHERE (ho.hdo_id = hydro_object.hdo_id)), ','::text) AS predecesid,
    'euProtectedAreaCode'::text AS predeidsch,
    NULL::text AS successoid,
    NULL::text AS succeidsch,
    'noChange'::text AS wevolution,
    NULL::text AS nametxtint,
    hdo_names.name AS nametext,
    'dut'::text AS nametxtlan,
    to_char(mrz.begin_designation, 'YYYY-MM-DD'::text) AS desigbegin,
        CASE
            WHEN (mrz.end_designation IS NULL) THEN NULL::text
            ELSE to_char(mrz.end_designation, 'YYYY-MM-DD'::text)
        END AS desigend,
    'drinkingWaterProtectionArea'::text AS zonetype,
        CASE
            WHEN ((hydro_object.hydro_object_type)::text = 'GroundWaterBody'::text) THEN 'groundWaterBody'::text
            ELSE 'lakeWaterBody'::text
        END AS spzonetype,
    'national'::text AS legislevel,
    'Directive EC/2000/60 of the European Parliament'::text AS legisname,
    'http://wetten.overheid.nl/zoeken'::text AS legislink,
    round(((public.st_area(mrz.geometry) / (1000000)::double precision))::numeric, 2) AS sizevalue,
    'km2'::text AS sizeuom
   FROM ((((((((public.management_regulation_or_restriction_zone mrz
     LEFT JOIN public.river_basin_disrict ON ((mrz.mrz_id = river_basin_disrict.rbd_id)))
     JOIN public.hydro_object ON ((hydro_object.hdo_id = mrz.mrz_id)))
     LEFT JOIN public.hdo_names ON ((hdo_names.hdo_id = mrz.mrz_id)))
     JOIN public.wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = hydro_object.inspire_id_namespace)))
     LEFT JOIN public.competentauthority_namespacetype ON ((namespace_type.ns_id = competentauthority_namespacetype.ns_id)))
     LEFT JOIN public.organisatorische_eenheid ON ((organisatorische_eenheid.ore_id = competentauthority_namespacetype.ore_id)))
     JOIN public.water_body ON ((water_body.wbd_id = hydro_object.hdo_id)))
  WHERE (((hydro_object.hydro_object_type)::text = ANY (ARRAY['GroundWaterBody'::text, 'SurfaceWaterBody'::text])) AND (water_body.is_article_7 IS TRUE) AND (hydro_object.wkrv_id = 13) AND (public.geometrytype(mrz.geometry_etrs89) ~~ '%POLYGON'::text))
  ORDER BY 2, 3, 4 DESC;


--
-- Name: vw__shape_surface_water_body_centerlijn; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw__shape_surface_water_body_centerlijn AS
 SELECT DISTINCT ON (hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id) mrz.geometry_etrs89 AS geometry,
    (('NL0-'::text || upper((hydro_object.inspire_id_local_id)::text)) || '-C'::text) AS thematicid,
    'euSurfaceWaterBodyCode'::text AS themaidsch,
    ('NL.WBHCODE.'::text || hydro_object.inspire_id_namespace) AS namespace,
    upper((hydro_object.inspire_id_local_id)::text) AS hydroid,
    hdo_names.name AS geonametxt,
    'dut'::text AS genonamtxtl,
    'realSurfaceWaterSegment'::text AS coninua
   FROM ((((((((((public.management_regulation_or_restriction_zone mrz
     LEFT JOIN public.river_basin_disrict ON ((mrz.mrz_id = river_basin_disrict.rbd_id)))
     JOIN public.hydro_object ON ((hydro_object.hdo_id = mrz.mrz_id)))
     LEFT JOIN public.hdo_names ON ((hdo_names.hdo_id = mrz.mrz_id)))
     JOIN public.wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = hydro_object.inspire_id_namespace)))
     LEFT JOIN public.competentauthority_namespacetype ON ((namespace_type.ns_id = competentauthority_namespacetype.ns_id)))
     LEFT JOIN public.organisatorische_eenheid ON ((organisatorische_eenheid.ore_id = competentauthority_namespacetype.ore_id)))
     LEFT JOIN public.surface_water_body ON ((surface_water_body.swb_id = hydro_object.hdo_id)))
     LEFT JOIN public.sw_typology_type stt ON ((stt.styp_id = surface_water_body.sw_typology_id)))
     LEFT JOIN public.water_body ON ((water_body.wbd_id = hydro_object.hdo_id)))
  WHERE (((hydro_object.hydro_object_type)::text = 'SurfaceWaterBody'::text) AND (hydro_object.wkrv_id = 13) AND (public.geometrytype(mrz.geometry_etrs89) ~~ '%STRING'::text) AND (hydro_object.inspire_id_namespace >= 80) AND (hydro_object.inspire_id_namespace < 99))
  ORDER BY hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id DESC;


--
-- Name: vw__shape_surface_water_body_lijn; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw__shape_surface_water_body_lijn AS
 SELECT DISTINCT ON (hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id) mrz.geometry_etrs89 AS geometry,
    ('NL.WBHCODE.'::text || hydro_object.inspire_id_namespace) AS namespace,
    hydro_object.inspire_id_local_id AS localid,
    hydro_object.wkrv_id AS inspireid_version_id,
    (('NL0-'::text || substr(upper((hydro_object.inspire_id_local_id)::text), 1, 34)) || '-O'::text) AS thematicid,
    'euSurfaceWaterBodyCode'::text AS themaidsch,
    to_char(wkr_version.begin_lifespan_version, 'YYYY-MM-DD'::text) AS beginlife,
    to_char(wkr_version.end_lifespan_version, 'YYYY-MM-DD'::text) AS endlife,
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace(upper((ho2009.inspire_id_local_id)::text), '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM ((public.hydro_object ho
             JOIN public.related_hydro_object rho ON (((rho.from_hdo = ho.hdo_id) AND (rho.rrt_id = 2) AND (ho.wkrv_id = 13))))
             JOIN public.hydro_object ho2009 ON (((rho.to_hdo = ho2009.hdo_id) AND (ho2009.wkrv_id = 2))))
          WHERE (ho.hdo_id = hydro_object.hdo_id)), ','::text) AS predecesid,
        CASE
            WHEN (array_to_string(ARRAY( SELECT DISTINCT regexp_replace(upper((ho2009.inspire_id_local_id)::text), '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
               FROM ((public.hydro_object ho
                 JOIN public.related_hydro_object rho ON (((rho.from_hdo = ho.hdo_id) AND (rho.rrt_id = 2) AND (ho.wkrv_id = 13))))
                 JOIN public.hydro_object ho2009 ON (((rho.to_hdo = ho2009.hdo_id) AND (ho2009.wkrv_id = 2))))
              WHERE (ho.hdo_id = hydro_object.hdo_id)), ','::text) = ''::text) THEN NULL::text
            ELSE 'euSurfaceWaterBodyCode'::text
        END AS predeidsch,
    NULL::text AS successoid,
    NULL::text AS succeidsch,
    'noChange'::text AS wevolution,
    NULL::text AS nametxtint,
    hdo_names.name AS nametext,
    'dut'::text AS nametxtlan,
    to_char(mrz.begin_designation, 'YYYY-MM-DD'::text) AS desigbegin,
        CASE
            WHEN (mrz.end_designation IS NULL) THEN NULL::text
            ELSE to_char(mrz.end_designation, 'YYYY-MM-DD'::text)
        END AS desigend,
    'waterBody'::text AS zonetype,
        CASE "left"((stt.code)::text, 1)
            WHEN 'K'::text THEN 'coastalWaterBody'::text
            WHEN 'O'::text THEN 'transitionalWaterBody'::text
            WHEN 'M'::text THEN 'lakeWaterBody'::text
            WHEN 'R'::text THEN 'riverWaterBody'::text
            ELSE 'oeps'::text
        END AS spzonetype,
    (('NL0-'::text || "left"((hdo_rbd.inspire_id_local_id)::text, 4)) || '-S'::text) AS rzoneid,
    'NotApplicable'::text AS rtrnsid,
    'euRBDCode'::text AS rzoneidsch,
    'NotApplicable'::text AS rtrnsidsch,
        CASE
            WHEN ("left"((stt.code)::text, 1) = ANY (ARRAY['K'::text, 'O'::text, 'M'::text])) THEN surface_water_body.area_value
            ELSE surface_water_body.length_value
        END AS sizevalue,
        CASE
            WHEN (((hydro_object.hydro_object_type)::text = 'SurfaceWaterBody'::text) AND ("left"((stt.code)::text, 1) <> ALL (ARRAY['K'::text, 'O'::text, 'M'::text]))) THEN 'km2'::text
            ELSE 'km'::text
        END AS sizeuom,
    'http://www.waterkwaliteitsportaal.nl'::text AS link,
    (1)::numeric AS meandepth
   FROM ((((((((((((public.management_regulation_or_restriction_zone mrz
     LEFT JOIN public.river_basin_disrict ON ((mrz.mrz_id = river_basin_disrict.rbd_id)))
     JOIN public.hydro_object ON ((hydro_object.hdo_id = mrz.mrz_id)))
     LEFT JOIN public.hdo_names ON ((hdo_names.hdo_id = mrz.mrz_id)))
     JOIN public.wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = hydro_object.inspire_id_namespace)))
     LEFT JOIN public.competentauthority_namespacetype ON ((namespace_type.ns_id = competentauthority_namespacetype.ns_id)))
     LEFT JOIN public.organisatorische_eenheid ON ((organisatorische_eenheid.ore_id = competentauthority_namespacetype.ore_id)))
     LEFT JOIN public.surface_water_body ON ((surface_water_body.swb_id = hydro_object.hdo_id)))
     LEFT JOIN public.sw_typology_type stt ON ((stt.styp_id = surface_water_body.sw_typology_id)))
     LEFT JOIN public.water_body ON ((water_body.wbd_id = hydro_object.hdo_id)))
     LEFT JOIN public.river_basin_disrict rbd ON ((rbd.rbd_id = water_body.rbd_id)))
     LEFT JOIN public.hydro_object hdo_rbd ON ((rbd.rbd_id = hdo_rbd.hdo_id)))
  WHERE (((hydro_object.hydro_object_type)::text = 'SurfaceWaterBody'::text) AND (hydro_object.wkrv_id = 13) AND (public.geometrytype(mrz.geometry_etrs89) ~~ '%STRING'::text))
  ORDER BY hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id DESC;


--
-- Name: vw__shape_surface_water_body_vlak; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw__shape_surface_water_body_vlak AS
 SELECT DISTINCT ON (hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id) mrz.geometry_etrs89 AS geometry,
    ('NL.WBHCODE.'::text || hydro_object.inspire_id_namespace) AS namespace,
    hydro_object.inspire_id_local_id AS localid,
    hydro_object.wkrv_id AS inspireid_version_id,
    (('NL0-'::text || substr(upper((hydro_object.inspire_id_local_id)::text), 1, 34)) || '-O'::text) AS thematicid,
    'euSurfaceWaterBodyCode'::text AS themaidsch,
    to_char(wkr_version.begin_lifespan_version, 'YYYY-MM-DD'::text) AS beginlife,
    to_char(wkr_version.end_lifespan_version, 'YYYY-MM-DD'::text) AS endlife,
    array_to_string(ARRAY( SELECT DISTINCT regexp_replace(upper((ho2009.inspire_id_local_id)::text), '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
           FROM ((public.hydro_object ho
             JOIN public.related_hydro_object rho ON (((rho.from_hdo = ho.hdo_id) AND (rho.rrt_id = 2) AND (ho.wkrv_id = 13))))
             JOIN public.hydro_object ho2009 ON (((rho.to_hdo = ho2009.hdo_id) AND (ho2009.wkrv_id = 2))))
          WHERE (ho.hdo_id = hydro_object.hdo_id)), ','::text) AS predecesid,
        CASE
            WHEN (array_to_string(ARRAY( SELECT DISTINCT regexp_replace(upper((ho2009.inspire_id_local_id)::text), '\r|\n'::text, ''::text, 'g'::text) AS regexp_replace
               FROM ((public.hydro_object ho
                 JOIN public.related_hydro_object rho ON (((rho.from_hdo = ho.hdo_id) AND (rho.rrt_id = 2) AND (ho.wkrv_id = 13))))
                 JOIN public.hydro_object ho2009 ON (((rho.to_hdo = ho2009.hdo_id) AND (ho2009.wkrv_id = 2))))
              WHERE (ho.hdo_id = hydro_object.hdo_id)), ','::text) = ''::text) THEN NULL::text
            ELSE 'euSurfaceWaterBodyCode'::text
        END AS predeidsch,
    NULL::text AS successoid,
    NULL::text AS succeidsch,
    'noChange'::text AS wevolution,
    NULL::text AS nametxtint,
    hdo_names.name AS nametext,
    'dut'::text AS nametxtlan,
    to_char(mrz.begin_designation, 'YYYY-MM-DD'::text) AS desigbegin,
        CASE
            WHEN (mrz.end_designation IS NULL) THEN NULL::text
            ELSE to_char(mrz.end_designation, 'YYYY-MM-DD'::text)
        END AS desigend,
    'waterBody'::text AS zonetype,
        CASE "left"((stt.code)::text, 1)
            WHEN 'K'::text THEN 'coastalWaterBody'::text
            WHEN 'O'::text THEN 'transitionalWaterBody'::text
            WHEN 'M'::text THEN 'lakeWaterBody'::text
            WHEN 'R'::text THEN 'riverWaterBody'::text
            ELSE 'oeps'::text
        END AS spzonetype,
    (('NL0-'::text || "left"((hdo_rbd.inspire_id_local_id)::text, 4)) || '-S'::text) AS rzoneid,
    'NotApplicable'::text AS rtrnsid,
    'euRBDCode'::text AS rzoneidsch,
    'NotApplicable'::text AS rtrnsidsch,
        CASE
            WHEN ("left"((stt.code)::text, 1) = ANY (ARRAY['K'::text, 'O'::text, 'M'::text])) THEN surface_water_body.area_value
            ELSE surface_water_body.length_value
        END AS sizevalue,
        CASE
            WHEN (((hydro_object.hydro_object_type)::text = 'SurfaceWaterBody'::text) AND ("left"((stt.code)::text, 1) <> ALL (ARRAY['K'::text, 'O'::text, 'M'::text]))) THEN 'km2'::text
            ELSE 'km'::text
        END AS sizeuom,
    'http://www.waterkwaliteitsportaal.nl'::text AS link,
    (1)::numeric AS meandepth
   FROM ((((((((((((public.management_regulation_or_restriction_zone mrz
     LEFT JOIN public.river_basin_disrict ON ((mrz.mrz_id = river_basin_disrict.rbd_id)))
     JOIN public.hydro_object ON ((hydro_object.hdo_id = mrz.mrz_id)))
     LEFT JOIN public.hdo_names ON ((hdo_names.hdo_id = mrz.mrz_id)))
     JOIN public.wkr_version ON ((wkr_version.wkrv_id = hydro_object.wkrv_id)))
     JOIN public.namespace_type ON ((namespace_type.ns_id = hydro_object.inspire_id_namespace)))
     LEFT JOIN public.competentauthority_namespacetype ON ((namespace_type.ns_id = competentauthority_namespacetype.ns_id)))
     LEFT JOIN public.organisatorische_eenheid ON ((organisatorische_eenheid.ore_id = competentauthority_namespacetype.ore_id)))
     LEFT JOIN public.surface_water_body ON ((surface_water_body.swb_id = hydro_object.hdo_id)))
     LEFT JOIN public.sw_typology_type stt ON ((stt.styp_id = surface_water_body.sw_typology_id)))
     LEFT JOIN public.water_body ON ((water_body.wbd_id = hydro_object.hdo_id)))
     LEFT JOIN public.river_basin_disrict rbd ON ((rbd.rbd_id = water_body.rbd_id)))
     LEFT JOIN public.hydro_object hdo_rbd ON ((rbd.rbd_id = hdo_rbd.hdo_id)))
  WHERE (((hydro_object.hydro_object_type)::text = 'SurfaceWaterBody'::text) AND (hydro_object.wkrv_id = 13) AND (public.geometrytype(mrz.geometry_etrs89) ~~ '%POLYGON'::text))
  ORDER BY hydro_object.inspire_id_namespace, hydro_object.inspire_id_local_id, hydro_object.wkrv_id DESC;


--
-- Name: vw_chemicalmonitoring; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw_chemicalmonitoring AS
 SELECT DISTINCT ON (mpn.mpnident, mpn.parameter) eu_stoffenlijst.value AS "chemicalSubstanceCode",
    (eu_stoffenlijst."swFailingRBSPOther")::text AS "chemicalSubstanceOther",
    'Water'::text AS "chemicalMatrix",
        CASE
            WHEN ((mpn.mpntype)::text = 'gw'::text) THEN 'Both'::text
            ELSE 'Status'::text
        END AS "chemicalPurpose",
    mpn.monfreq AS "chemicalFrequency",
    mpn.moncyclus AS "chemicalCycle",
    mpn.tot AS "chemicalLastMonitored",
        CASE
            WHEN ("substring"((mpn.mpnident)::text, 1, 2) <> 'NL'::text) THEN ('NL_'::text || (mpn.mpnident)::text)
            ELSE (mpn.mpnident)::text
        END AS "euMonitoringSiteCode",
    "left"((mpn.sgb)::text, 4) AS sgb
   FROM (eu_state_of_environment.monprog_2015 mpn
     JOIN eu_stoffenlijst ON ((((mpn.parameter)::text = (eu_stoffenlijst.parametercode)::text) AND ((eu_stoffenlijst.domeinnaam)::text = ANY (ARRAY[('PS_Enum'::character varying)::text, ('AdditionalPollutant_Enum'::character varying)::text, ('RBSP_Enum'::character varying)::text])))))
  WHERE ((mpn.purpose)::text = 'chemie'::text)
  ORDER BY mpn.mpnident, mpn.parameter, mpn.purpose, mpn.monsoort DESC;


--
-- Name: vw_competentauthority; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw_competentauthority AS
 SELECT DISTINCT ON (organisatorische_eenheid.organisatie_identificatie, ("left"((hdo_rbd.inspire_id_local_id)::text, 4))) organisatorische_eenheid.ore_id AS wkp_db_id,
    organisatorische_eenheid.organisatie_identificatie AS "euCACode",
    competent_authority.name_uk AS "competentAuthorityName",
    organisatorische_eenheid.naam AS "competentAuthorityNameNL",
    'Dutch'::text AS "competentAuthorityNameNLLanguage",
    competent_authority.url AS "linkToCompetentAuthority",
    organisatorische_eenheid.naam_verkort AS acronym,
    organisatorische_eenheid.adres AS street,
    organisatorische_eenheid.plaatsnaam AS "cityNL",
    'the Netherlands'::text AS country,
    organisatorische_eenheid.postcode,
    "left"((hdo_rbd.inspire_id_local_id)::text, 4) AS sgb,
        CASE
            WHEN (rbd.part_of_district IS NULL) THEN rbd.rbd_id
            ELSE rbd.part_of_district
        END AS "rBDSUCAID"
   FROM (((((((public.competent_authority
     JOIN public.organisatorische_eenheid ON ((organisatorische_eenheid.ore_id = competent_authority.ore_id)))
     LEFT JOIN public.competentauthority_namespacetype ca_ns ON ((ca_ns.ore_id = competent_authority.ore_id)))
     LEFT JOIN public.namespace_type ns ON ((ca_ns.ns_id = ns.ns_id)))
     LEFT JOIN public.hydro_object hdo ON (((ns.ns_id = hdo.inspire_id_namespace) AND (hdo.wkrv_id = 13))))
     JOIN public.water_body wbd ON ((wbd.wbd_id = hdo.hdo_id)))
     JOIN public.hydro_object hdo_rbd ON ((wbd.rbd_id = hdo_rbd.hdo_id)))
     JOIN public.river_basin_disrict rbd ON ((rbd.rbd_id = hdo_rbd.hdo_id)))
  ORDER BY organisatorische_eenheid.organisatie_identificatie;


--
-- Name: vw_competentauthority_mainrole; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw_competentauthority_mainrole AS
 SELECT organisatorische_eenheid.ore_id AS wkp_db_id,
    organisatorische_eenheid.organisatie_identificatie AS "euCACode",
    ca_role_code.description AS "mainRole"
   FROM public.competent_authority,
    public.organisatorische_eenheid,
    public.competent_authority_roles,
    public.ca_role_code
  WHERE ((organisatorische_eenheid.ore_id = competent_authority.ore_id) AND (organisatorische_eenheid.ore_id = competent_authority_roles.ore_id) AND (ca_role_code.crc_id = competent_authority_roles.crc_id) AND (competent_authority_roles.main = true) AND (ca_role_code.lgr_id = 1));


--
-- Name: vw_competentauthority_otherrole; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw_competentauthority_otherrole AS
 SELECT organisatorische_eenheid.ore_id AS wkp_db_id,
    organisatorische_eenheid.organisatie_identificatie AS "euCACode",
    ca_role_code.description AS "otherRole"
   FROM public.competent_authority,
    public.organisatorische_eenheid,
    public.competent_authority_roles,
    public.ca_role_code
  WHERE ((organisatorische_eenheid.ore_id = competent_authority.ore_id) AND (organisatorische_eenheid.ore_id = competent_authority_roles.ore_id) AND (ca_role_code.crc_id = competent_authority_roles.crc_id) AND (competent_authority_roles.main = false) AND (ca_role_code.lgr_id = 1));


--
-- Name: vw_failingrbsp; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw_failingrbsp AS
 WITH oordeel AS (
         SELECT DISTINCT ON (sys.sys_id, swcs.opt_id) sys.sys_id,
            sys.swb_id,
            hydro_object.inspire_id_local_id,
            swcs.opt_id,
            swcs.swcs_id,
            sge.chs_id
           FROM ((((public.swb_yearly_status sys
             JOIN public.sw_chemical_substance_status swcs ON ((swcs.sys_id = sys.sys_id)))
             JOIN public.observed_property_type ON ((observed_property_type.opt_id = swcs.opt_id)))
             JOIN public.substance_group_element sge ON ((sge.ssge_id = observed_property_type.substance_group_id)))
             JOIN public.hydro_object ON ((hydro_object.hdo_id = sys.swb_id)))
          WHERE ((sge.chs_id IS NOT NULL) AND (swcs.eumg_id = 3) AND (sys.year_of_status = 2015) AND (hydro_object.wkrv_id = 13))
          ORDER BY sys.sys_id, swcs.opt_id, swcs.wbpm_id DESC
        ), stofgroep AS (
         SELECT substance_group_element.chs_id,
            substance_group_element.ssge_id,
                CASE substance_group_element.ssge_id
                    WHEN 3155 THEN 'STOFOV'::text
                    WHEN 3149 THEN 'STOFPR_UBQN'::text
                    WHEN 3150 THEN 'STOFPR_UBQJ'::text
                    WHEN 2907 THEN 'CHEMT'::text
                    WHEN 3148 THEN 'STOFPR_34-45'::text
                    ELSE NULL::text
                END AS categorie
           FROM public.substance_group_element
          WHERE (substance_group_element.ssge_id = ANY (ARRAY[3155, 3150, 3149, 3148, 2907]))
        ), eu_stoffenlijst AS (
         SELECT chemische_stof_type.chs_id,
            chemische_stof_type.cas_nr,
            chemische_stof_type.naam,
            chemische_stof_type.code,
            chemische_stof_type.d_begin,
            chemische_stof_type.d_eind,
            chemische_stof_type.last_changed_date,
            chemische_stof_type.d_status,
            chemische_stof_type.id,
            eu_stoffenlijst_1.domeinnaam,
            eu_stoffenlijst_1.value,
            eu_stoffenlijst_1.grootheidcode,
            eu_stoffenlijst_1.parametercode,
            eu_stoffenlijst_1."swFailingRBSPOther"
           FROM public.chemische_stof_type,
            wfd_reporting.eu_stoffenlijst eu_stoffenlijst_1
          WHERE (((eu_stoffenlijst_1.parametercode)::text = (chemische_stof_type.code)::text) AND ((eu_stoffenlijst_1.domeinnaam)::text = 'RBSP_Enum'::text))
        )
 SELECT oordeel.inspire_id_local_id AS "euSurfaceWaterBodyCode",
    eu_stoffenlijst.value AS "swFailingRBSP",
    rb.inspire_id_local_id AS stroomgebieddistrict
   FROM (((((oordeel
     JOIN stofgroep ON ((stofgroep.chs_id = oordeel.chs_id)))
     JOIN public.chemische_stof_type chs ON ((chs.chs_id = oordeel.chs_id)))
     JOIN public.water_body ON ((oordeel.swb_id = water_body.wbd_id)))
     JOIN public.hydro_object rb ON ((rb.hdo_id = water_body.rbd_id)))
     LEFT JOIN eu_stoffenlijst ON (((chs.code)::text = (eu_stoffenlijst.parametercode)::text)))
  WHERE ((oordeel.swcs_id = 2) AND (stofgroep.categorie = 'STOFOV'::text) AND (eu_stoffenlijst.value IS NOT NULL))
  ORDER BY rb.inspire_id_local_id, oordeel.inspire_id_local_id, eu_stoffenlijst.value;


--
-- Name: vw_failingrbsp2; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw_failingrbsp2 AS
 WITH oordeel AS (
         SELECT DISTINCT ON (sys.sys_id, swcs.opt_id) sys.sys_id,
            sys.swb_id,
            hydro_object.inspire_id_local_id,
            swcs.opt_id,
            swcs.swcs_id,
            sge.chs_id
           FROM ((((public.swb_yearly_status sys
             JOIN public.sw_chemical_substance_status swcs ON ((swcs.sys_id = sys.sys_id)))
             JOIN public.observed_property_type ON ((observed_property_type.opt_id = swcs.opt_id)))
             JOIN public.substance_group_element sge ON ((sge.ssge_id = observed_property_type.substance_group_id)))
             JOIN public.hydro_object ON ((hydro_object.hdo_id = sys.swb_id)))
          WHERE ((sge.chs_id IS NOT NULL) AND (swcs.eumg_id = 3) AND (sys.year_of_status = 2015) AND (hydro_object.wkrv_id = 13))
          ORDER BY sys.sys_id, swcs.opt_id, swcs.wbpm_id DESC
        ), stofgroep AS (
         SELECT substance_group_element.chs_id,
            substance_group_element.ssge_id,
                CASE substance_group_element.ssge_id
                    WHEN 3155 THEN 'STOFOV'::text
                    WHEN 3149 THEN 'STOFPR_UBQN'::text
                    WHEN 3150 THEN 'STOFPR_UBQJ'::text
                    WHEN 2907 THEN 'CHEMT'::text
                    WHEN 3148 THEN 'STOFPR_34-45'::text
                    ELSE NULL::text
                END AS categorie
           FROM public.substance_group_element
          WHERE (substance_group_element.ssge_id = ANY (ARRAY[3155, 3150, 3149, 3148, 2907]))
        )
 SELECT oordeel.inspire_id_local_id AS "euSurfaceWaterBodyCode",
    chs.cas_nr AS "swFailingRBSP_Casnr",
    chs.code AS "swFailingRBSP_code",
    chs.naam AS "swFailingRBSP_naam",
    rb.inspire_id_local_id AS stroomgebieddistrict
   FROM ((((oordeel
     JOIN stofgroep ON ((stofgroep.chs_id = oordeel.chs_id)))
     JOIN public.chemische_stof_type chs ON ((chs.chs_id = oordeel.chs_id)))
     JOIN public.water_body ON ((oordeel.swb_id = water_body.wbd_id)))
     JOIN public.hydro_object rb ON ((rb.hdo_id = water_body.rbd_id)))
  WHERE ((oordeel.swcs_id = 2) AND (stofgroep.categorie = 'STOFOV'::text))
  ORDER BY rb.inspire_id_local_id, oordeel.inspire_id_local_id, chs.code;


--
-- Name: vw_groundwaterbody; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw_groundwaterbody AS
 WITH gwb_prot AS (
         SELECT DISTINCT ON (wpi.wbd_id) wpi.wbd_id
           FROM public.waterbody_protected_area_intersection wpi,
            public.wfd_protected_area,
            public.hydro_object hydro_object_1
          WHERE ((wfd_protected_area.wpa_id = wpi.wpa_id) AND (wfd_protected_area.protected_area_type_id = ANY (ARRAY[1, 4])) AND (hydro_object_1.hdo_id = wpi.wbd_id) AND (hydro_object_1.wkrv_id = 13) AND ((hydro_object_1.hydro_object_type)::text = 'GroundWaterBody'::text))
          ORDER BY wpi.wbd_id
        ), gwb_swb AS (
         SELECT DISTINCT ON (relation_gwb_swb.gwb_id) relation_gwb_swb.gwb_id
           FROM public.relation_gwb_swb,
            public.hydro_object hydro_object_1
          WHERE ((hydro_object_1.hdo_id = relation_gwb_swb.gwb_id) AND (hydro_object_1.wkrv_id = 13) AND ((hydro_object_1.hydro_object_type)::text = 'GroundWaterBody'::text))
          ORDER BY relation_gwb_swb.gwb_id
        ), gwb_oordeel AS (
         SELECT hdo_gwb.hdo_id AS gwb_id,
            gwb_yearly_status.year_of_status AS jaar,
            gw_chemical_status.opt_id,
            classification_type.description AS oordeel,
            waarde_bepalings_methode_type.code AS wbpm
           FROM (((((public.gwb_yearly_status
             LEFT JOIN public.hydro_object hdo_gwb ON ((hdo_gwb.hdo_id = gwb_yearly_status.gwb_id)))
             JOIN public.gw_chemical_status ON ((gw_chemical_status.gys_id = gwb_yearly_status.gys_id)))
             LEFT JOIN public.classification_type ON ((gw_chemical_status.clst_id = classification_type.cls_id)))
             LEFT JOIN public.vw_waargenomen_eigenschap ON ((vw_waargenomen_eigenschap."DB_ID" = gw_chemical_status.opt_id)))
             LEFT JOIN public.waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = gw_chemical_status.wbpm_id)))
          WHERE ((hdo_gwb.wkrv_id = 13) AND (gwb_yearly_status.year_of_status = ANY (ARRAY[2014, 2015])) AND ((waarde_bepalings_methode_type.code)::text = 'GWPROTNL'::text) AND (gw_chemical_status.opt_id = ANY (ARRAY[4495, 4494])))
        UNION ALL
         SELECT hdo_gwb.hdo_id AS gwb_id,
            gwb_yearly_status.year_of_status AS jaar,
            gw_quantity_status.opt_id,
            classification_type.description AS oordeel,
            waarde_bepalings_methode_type.code AS wbpm
           FROM (((((public.gwb_yearly_status
             LEFT JOIN public.hydro_object hdo_gwb ON ((hdo_gwb.hdo_id = gwb_yearly_status.gwb_id)))
             JOIN public.gw_quantity_status ON ((gw_quantity_status.gys_id = gwb_yearly_status.gys_id)))
             LEFT JOIN public.classification_type ON ((gw_quantity_status.gwst_id = classification_type.cls_id)))
             LEFT JOIN public.vw_waargenomen_eigenschap ON ((vw_waargenomen_eigenschap."DB_ID" = gw_quantity_status.opt_id)))
             LEFT JOIN public.waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = gw_quantity_status.wbpm_id)))
          WHERE ((hdo_gwb.wkrv_id = 13) AND (gwb_yearly_status.year_of_status = ANY (ARRAY[2014, 2015])) AND ((waarde_bepalings_methode_type.code)::text = 'GWPROTNL'::text) AND (gw_quantity_status.opt_id = ANY (ARRAY[4495, 4494])))
        ), gwb_prognose_2021_4494 AS (
         SELECT hydro_object_1.hdo_id AS gwb_id,
                CASE
                    WHEN ((wp4486.cls_id = 9) OR (wp4491.cls_id = 9) OR (wp4492.cls_id = 9) OR (wp4493.cls_id = 9) OR (wp4498.cls_id = 9)) THEN 'Ontoereikend'::text
                    ELSE 'Goed'::text
                END AS prognose
           FROM (((((public.hydro_object hydro_object_1
             JOIN public.wb_prognosis wp4486 ON (((wp4486.swb_id = hydro_object_1.hdo_id) AND (wp4486.year = 2021) AND (wp4486.opt_id = 4486))))
             JOIN public.wb_prognosis wp4491 ON (((wp4491.swb_id = hydro_object_1.hdo_id) AND (wp4491.year = 2021) AND (wp4491.opt_id = 4491))))
             JOIN public.wb_prognosis wp4492 ON (((wp4492.swb_id = hydro_object_1.hdo_id) AND (wp4492.year = 2021) AND (wp4492.opt_id = 4492))))
             JOIN public.wb_prognosis wp4493 ON (((wp4493.swb_id = hydro_object_1.hdo_id) AND (wp4493.year = 2021) AND (wp4493.opt_id = 4493))))
             JOIN public.wb_prognosis wp4498 ON (((wp4498.swb_id = hydro_object_1.hdo_id) AND (wp4498.year = 2021) AND (wp4498.opt_id = 4498))))
          WHERE (hydro_object_1.wkrv_id = 13)
        ), gwb_prognose_2027_4494 AS (
         SELECT hydro_object_1.hdo_id AS gwb_id,
                CASE
                    WHEN ((wp4486.cls_id = 9) OR (wp4491.cls_id = 9) OR (wp4492.cls_id = 9) OR (wp4493.cls_id = 9) OR (wp4498.cls_id = 9)) THEN 'Ontoereikend'::text
                    ELSE 'Goed'::text
                END AS prognose
           FROM (((((public.hydro_object hydro_object_1
             JOIN public.wb_prognosis wp4486 ON (((wp4486.swb_id = hydro_object_1.hdo_id) AND (wp4486.year = 2027) AND (wp4486.opt_id = 4486))))
             JOIN public.wb_prognosis wp4491 ON (((wp4491.swb_id = hydro_object_1.hdo_id) AND (wp4491.year = 2027) AND (wp4491.opt_id = 4491))))
             JOIN public.wb_prognosis wp4492 ON (((wp4492.swb_id = hydro_object_1.hdo_id) AND (wp4492.year = 2027) AND (wp4492.opt_id = 4492))))
             JOIN public.wb_prognosis wp4493 ON (((wp4493.swb_id = hydro_object_1.hdo_id) AND (wp4493.year = 2027) AND (wp4493.opt_id = 4493))))
             JOIN public.wb_prognosis wp4498 ON (((wp4498.swb_id = hydro_object_1.hdo_id) AND (wp4498.year = 2027) AND (wp4498.opt_id = 4498))))
          WHERE (hydro_object_1.wkrv_id = 13)
        ), gwb_prognose_2021_4495 AS (
         SELECT hydro_object_1.hdo_id AS gwb_id,
                CASE
                    WHEN ((wp4488.cls_id = 9) OR (wp4489.cls_id = 9) OR (wp4490.cls_id = 9) OR (wp4497.cls_id = 9)) THEN 'Ontoereikend'::text
                    ELSE 'Goed'::text
                END AS prognose
           FROM ((((public.hydro_object hydro_object_1
             JOIN public.wb_prognosis wp4488 ON (((wp4488.swb_id = hydro_object_1.hdo_id) AND (wp4488.year = 2021) AND (wp4488.opt_id = 4488))))
             JOIN public.wb_prognosis wp4489 ON (((wp4489.swb_id = hydro_object_1.hdo_id) AND (wp4489.year = 2021) AND (wp4489.opt_id = 4489))))
             JOIN public.wb_prognosis wp4490 ON (((wp4490.swb_id = hydro_object_1.hdo_id) AND (wp4490.year = 2021) AND (wp4490.opt_id = 4490))))
             JOIN public.wb_prognosis wp4497 ON (((wp4497.swb_id = hydro_object_1.hdo_id) AND (wp4497.year = 2021) AND (wp4497.opt_id = 4497))))
          WHERE (hydro_object_1.wkrv_id = 13)
        ), gwb_prognose_2027_4495 AS (
         SELECT hydro_object_1.hdo_id AS gwb_id,
                CASE
                    WHEN ((wp4488.cls_id = 9) OR (wp4489.cls_id = 9) OR (wp4490.cls_id = 9) OR (wp4497.cls_id = 9)) THEN 'Ontoereikend'::text
                    ELSE 'Goed'::text
                END AS prognose
           FROM ((((public.hydro_object hydro_object_1
             JOIN public.wb_prognosis wp4488 ON (((wp4488.swb_id = hydro_object_1.hdo_id) AND (wp4488.year = 2027) AND (wp4488.opt_id = 4488))))
             JOIN public.wb_prognosis wp4489 ON (((wp4489.swb_id = hydro_object_1.hdo_id) AND (wp4489.year = 2027) AND (wp4489.opt_id = 4489))))
             JOIN public.wb_prognosis wp4490 ON (((wp4490.swb_id = hydro_object_1.hdo_id) AND (wp4490.year = 2027) AND (wp4490.opt_id = 4490))))
             JOIN public.wb_prognosis wp4497 ON (((wp4497.swb_id = hydro_object_1.hdo_id) AND (wp4497.year = 2027) AND (wp4497.opt_id = 4497))))
          WHERE (hydro_object_1.wkrv_id = 13)
        ), gwb_oordeel_sub AS (
         SELECT hdo_gwb.hdo_id AS gwb_id,
            gwb_yearly_status.year_of_status AS jaar,
            gw_chemical_status.opt_id,
            classification_type.description AS oordeel,
            waarde_bepalings_methode_type.code AS wbpm
           FROM ((((public.gwb_yearly_status
             LEFT JOIN public.hydro_object hdo_gwb ON ((hdo_gwb.hdo_id = gwb_yearly_status.gwb_id)))
             JOIN public.gw_chemical_status ON ((gw_chemical_status.gys_id = gwb_yearly_status.gys_id)))
             LEFT JOIN public.classification_type ON ((gw_chemical_status.clst_id = classification_type.cls_id)))
             LEFT JOIN public.waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = gw_chemical_status.wbpm_id)))
          WHERE ((hdo_gwb.wkrv_id = 13) AND (gwb_yearly_status.year_of_status = ANY (ARRAY[2014, 2015])) AND (gw_chemical_status.opt_id > 4400) AND (gw_chemical_status.opt_id <> ALL (ARRAY[4495, 4494, 4487])))
        UNION ALL
         SELECT hdo_gwb.hdo_id AS gwb_id,
            gwb_yearly_status.year_of_status AS jaar,
            gw_quantity_status.opt_id,
            classification_type.description AS oordeel,
            waarde_bepalings_methode_type.code AS wbpm
           FROM ((((public.gwb_yearly_status
             LEFT JOIN public.hydro_object hdo_gwb ON ((hdo_gwb.hdo_id = gwb_yearly_status.gwb_id)))
             JOIN public.gw_quantity_status ON ((gw_quantity_status.gys_id = gwb_yearly_status.gys_id)))
             LEFT JOIN public.classification_type ON ((gw_quantity_status.gwst_id = classification_type.cls_id)))
             LEFT JOIN public.waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = gw_quantity_status.wbpm_id)))
          WHERE ((hdo_gwb.wkrv_id = 13) AND (gwb_yearly_status.year_of_status = ANY (ARRAY[2014, 2015])) AND (gw_quantity_status.opt_id > 4400) AND (gw_quantity_status.opt_id <> ALL (ARRAY[4495, 4494, 4487])))
  ORDER BY 1, 2, 3
        ), gwb_oordeel_subt AS (
         SELECT DISTINCT ON (gwb_oordeel_sub.gwb_id, gwb_oordeel_sub.opt_id) gwb_oordeel_sub.gwb_id,
            gwb_oordeel_sub.jaar,
            gwb_oordeel_sub.opt_id,
            gwb_oordeel_sub.oordeel
           FROM gwb_oordeel_sub
          ORDER BY gwb_oordeel_sub.gwb_id, gwb_oordeel_sub.opt_id, gwb_oordeel_sub.jaar DESC, gwb_oordeel_sub.wbpm
        ), gwb_oordeel_subt_q AS (
         SELECT hydro_object_1.hdo_id AS gwb_id,
                CASE
                    WHEN ((((gwb_oordeel_subt_4488.oordeel)::text = 'Ontoereikend'::text) OR ((gwb_oordeel_subt_4489.oordeel)::text = 'Ontoereikend'::text)) AND (((gwb_oordeel_subt_4490.oordeel)::text = 'Ontoereikend'::text) OR ((gwb_oordeel_subt_4497.oordeel)::text = 'Ontoereikend'::text))) THEN 'Both'::text
                    ELSE
                    CASE
                        WHEN (((gwb_oordeel_subt_4488.oordeel)::text = 'Ontoereikend'::text) OR ((gwb_oordeel_subt_4489.oordeel)::text = 'Ontoereikend'::text)) THEN 'Surface waters / terrestial ecosystems'::text
                        ELSE 'Uses of functions'::text
                    END
                END AS oordeel
           FROM ((((public.hydro_object hydro_object_1
             LEFT JOIN gwb_oordeel_subt gwb_oordeel_subt_4488 ON (((gwb_oordeel_subt_4488.gwb_id = hydro_object_1.hdo_id) AND ((gwb_oordeel_subt_4488.oordeel)::text = 'Ontoereikend'::text) AND (gwb_oordeel_subt_4488.opt_id = 4888))))
             LEFT JOIN gwb_oordeel_subt gwb_oordeel_subt_4489 ON (((gwb_oordeel_subt_4489.gwb_id = hydro_object_1.hdo_id) AND ((gwb_oordeel_subt_4489.oordeel)::text = 'Ontoereikend'::text) AND (gwb_oordeel_subt_4489.opt_id = 4489))))
             LEFT JOIN gwb_oordeel_subt gwb_oordeel_subt_4490 ON (((gwb_oordeel_subt_4490.gwb_id = hydro_object_1.hdo_id) AND ((gwb_oordeel_subt_4490.oordeel)::text = 'Ontoereikend'::text) AND (gwb_oordeel_subt_4490.opt_id = 4490))))
             LEFT JOIN gwb_oordeel_subt gwb_oordeel_subt_4497 ON (((gwb_oordeel_subt_4497.gwb_id = hydro_object_1.hdo_id) AND ((gwb_oordeel_subt_4497.oordeel)::text = 'Ontoereikend'::text) AND (gwb_oordeel_subt_4497.opt_id = 4497))))
          WHERE (((hydro_object_1.hydro_object_type)::text = 'GroundWaterBody'::text) AND (hydro_object_1.wkrv_id = 13))
        ), gwb_oordeel_subt_c AS (
         SELECT hydro_object_1.hdo_id AS gwb_id,
                CASE
                    WHEN ((((gwb_oordeel_subt_4492.oordeel)::text = 'Ontoereikend'::text) OR ((gwb_oordeel_subt_4493.oordeel)::text = 'Ontoereikend'::text)) AND (((gwb_oordeel_subt_4491.oordeel)::text = 'Ontoereikend'::text) OR ((gwb_oordeel_subt_4486.oordeel)::text = 'Ontoereikend'::text) OR ((gwb_oordeel_subt_4498.oordeel)::text = 'Ontoereikend'::text))) THEN 'Both'::text
                    ELSE
                    CASE
                        WHEN (((gwb_oordeel_subt_4492.oordeel)::text = 'Ontoereikend'::text) OR ((gwb_oordeel_subt_4493.oordeel)::text = 'Ontoereikend'::text)) THEN 'Surface waters / terrestial ecosystems'::text
                        ELSE 'Uses of functions'::text
                    END
                END AS oordeel
           FROM (((((public.hydro_object hydro_object_1
             LEFT JOIN gwb_oordeel_subt gwb_oordeel_subt_4486 ON (((gwb_oordeel_subt_4486.gwb_id = hydro_object_1.hdo_id) AND ((gwb_oordeel_subt_4486.oordeel)::text = 'Ontoereikend'::text) AND (gwb_oordeel_subt_4486.opt_id = 4886))))
             LEFT JOIN gwb_oordeel_subt gwb_oordeel_subt_4491 ON (((gwb_oordeel_subt_4491.gwb_id = hydro_object_1.hdo_id) AND ((gwb_oordeel_subt_4491.oordeel)::text = 'Ontoereikend'::text) AND (gwb_oordeel_subt_4491.opt_id = 4491))))
             LEFT JOIN gwb_oordeel_subt gwb_oordeel_subt_4492 ON (((gwb_oordeel_subt_4492.gwb_id = hydro_object_1.hdo_id) AND ((gwb_oordeel_subt_4492.oordeel)::text = 'Ontoereikend'::text) AND (gwb_oordeel_subt_4492.opt_id = 4492))))
             LEFT JOIN gwb_oordeel_subt gwb_oordeel_subt_4493 ON (((gwb_oordeel_subt_4493.gwb_id = hydro_object_1.hdo_id) AND ((gwb_oordeel_subt_4493.oordeel)::text = 'Ontoereikend'::text) AND (gwb_oordeel_subt_4493.opt_id = 4493))))
             LEFT JOIN gwb_oordeel_subt gwb_oordeel_subt_4498 ON (((gwb_oordeel_subt_4498.gwb_id = hydro_object_1.hdo_id) AND ((gwb_oordeel_subt_4498.oordeel)::text = 'Ontoereikend'::text) AND (gwb_oordeel_subt_4498.opt_id = 4498))))
          WHERE (((hydro_object_1.hydro_object_type)::text = 'GroundWaterBody'::text) AND (hydro_object_1.wkrv_id = 13))
        ), pressure AS (
         SELECT DISTINCT ON (hydro_object_1.hdo_id, spe.eu_xsd) hydro_object_1.hdo_id AS db_id,
            hydro_object_1.inspire_id_local_id AS "euGroundWaterBodyCode",
                CASE
                    WHEN (spe.eu_xsd IS NULL) THEN 'No significant pressure'::text
                    ELSE spe.eu_xsd
                END AS pressure_type
           FROM ((public.hydro_object hydro_object_1
             LEFT JOIN public.gwb_pressure ON ((hydro_object_1.hdo_id = gwb_pressure.gwb_id)))
             LEFT JOIN significant_pressure_enum spe ON (((spe.gw_p_id = gwb_pressure.gprs_id) AND (spe.eu_xsd ~~ '7 - %'::text))))
          WHERE ((gwb_pressure.is_significant = true) AND (hydro_object_1.wkrv_id = 13))
          ORDER BY hydro_object_1.hdo_id, spe.eu_xsd
        ), gwb_oordeel_i AS (
         SELECT DISTINCT ON (gwb_oordeel.gwb_id) gwb_oordeel.gwb_id,
            gwb_oordeel.jaar,
            gwb_oordeel.oordeel,
            gwb_oordeel.wbpm
           FROM gwb_oordeel
          ORDER BY gwb_oordeel.gwb_id, gwb_oordeel.oordeel DESC
        ), impact AS (
         SELECT DISTINCT ON (hydro_object_1.hdo_id, ipe.eu_xsd) hydro_object_1.hdo_id AS db_id,
                CASE
                    WHEN (ipe.eu_xsd IS NULL) THEN
                    CASE
                        WHEN ((gwb_oordeel_i.oordeel)::text = 'Goed'::text) THEN 'NOSI - No significant impact'::text
                        ELSE 'UNKN - Unknown impact type'::text
                    END
                    ELSE ipe.eu_xsd
                END AS impact_type
           FROM ((((public.hydro_object hydro_object_1
             LEFT JOIN public.gwb_pressure ON (((hydro_object_1.hdo_id = gwb_pressure.gwb_id) AND (gwb_pressure.is_significant = true))))
             LEFT JOIN public.gwb_pressure_impact ON ((gwb_pressure.gwbp_id = gwb_pressure_impact.gwbp_id)))
             LEFT JOIN significant_impact_enum ipe ON (((ipe.i_id = gwb_pressure_impact.impact_id) AND (ipe.eu_xsd ~~ 'OTHE%'::text))))
             LEFT JOIN gwb_oordeel_i ON ((gwb_oordeel_i.gwb_id = hydro_object_1.hdo_id)))
          WHERE ((hydro_object_1.wkrv_id = 13) AND ((hydro_object_1.hydro_object_type)::text = 'GroundWaterBody'::text))
          ORDER BY hydro_object_1.hdo_id, ipe.eu_xsd
        )
 SELECT hydro_object.hdo_id AS db_id,
    hydro_object.inspire_id_local_id AS "euGroundWaterBodyCode",
    "left"((hdo_rbd.inspire_id_local_id)::text, 4) AS "euRBDCode",
    hdo_names.name AS "groundwaterBodyName",
        CASE ground_water_body.is_layered
            WHEN true THEN 'Yes'::text
            ELSE 'No'::text
        END AS layered,
        CASE
            WHEN (gsw.gwb_id IS NULL) THEN 'No'::text
            ELSE 'Yes'::text
        END AS "linkSurfaceWaterBody",
        CASE ground_water_body.is_linked_to_terrestrial_ecosystems
            WHEN true THEN 'Yes'::text
            ELSE 'No'::text
        END AS "linkTerrestrialEcosystem",
    gfe.eu_xsd AS "geologicalFormation",
        CASE ground_water_body.is_transboundary
            WHEN true THEN 'Yes'::text
            ELSE 'No'::text
        END AS "groundwaterBodyTransboundary",
        CASE
            WHEN (gpb.wbd_id IS NULL) THEN 'False'::text
            ELSE 'True'::text
        END AS "gwAssociatedProtectedArea",
        CASE
            WHEN (pressure.pressure_type ~~ '7 - %'::text) THEN ground_water_body.other_pressure_description
            ELSE NULL::character varying
        END AS "gwSignificantPressureOther",
        CASE
            WHEN (impact.impact_type ~~ 'OTHE%'::text) THEN 'No further details available'::text
            ELSE NULL::text
        END AS "gwSignificantImpactOther",
        CASE
            WHEN ((goq.oordeel)::text = 'Goed'::text) THEN 'No'::text
            ELSE 'Yes'::text
        END AS "gwAtRiskQuantitative",
        CASE
            WHEN ((goq.oordeel)::text = 'Goed'::text) THEN 'No'::text
            ELSE
            CASE
                WHEN ((gpq_2021.prognose = 'Goed'::text) OR (gpq_2027.prognose = 'Goed'::text)) THEN 'No'::text
                ELSE 'Yes'::text
            END
        END AS "gwEORiskQuantitative",
        CASE
            WHEN ((goq.oordeel)::text = 'Goed'::text) THEN 2
            ELSE 3
        END AS "gwQuantitativeStatusValue",
    '2007-2014'::text AS "gwQuantitativeAssessmentYear",
    3 AS "gwQuantitativeAssessmentConfidence",
        CASE
            WHEN ((goq.oordeel)::text = 'Goed'::text) THEN 'Yes'::text
            ELSE 'No'::text
        END AS "gwQuantitativeStatusExpectedGoodIn2015",
        CASE
            WHEN ((goq.oordeel)::text = 'Goed'::text) THEN NULL::text
            ELSE
            CASE
                WHEN (gpq_2021.prognose = 'Goed'::text) THEN '2016-2021'::text
                ELSE
                CASE
                    WHEN ((gpq_2027.prognose = 'Goed'::text) OR (gpq_2027.prognose IS NULL)) THEN '2022-2027'::text
                    ELSE 'Beyond 2027'::text
                END
            END
        END AS "gwQuantitativeStatusExpectedAchievementDate",
        CASE
            WHEN ((goc.oordeel)::text = 'Goed'::text) THEN 'No'::text
            ELSE
            CASE
                WHEN ((gpc_2021.prognose = 'Goed'::text) OR (gpc_2027.prognose = 'Goed'::text)) THEN 'No'::text
                ELSE 'Yes'::text
            END
        END AS "gwAtRiskChemical",
        CASE
            WHEN ((goc.oordeel)::text = 'Ontoereikend'::text) THEN gwb_oordeel_subt_c.oordeel
            ELSE NULL::text
        END AS "gwEORiskChemical",
        CASE
            WHEN ((goc.oordeel)::text = 'Goed'::text) THEN 2
            ELSE 3
        END AS "gwChemicalStatusValue",
    '2007-2013'::text AS "gwChemicalAssessmentYear",
    3 AS "gwChemicalAssessmentConfidence",
        CASE
            WHEN ((goc.oordeel)::text = 'Goed'::text) THEN 'Yes'::text
            ELSE 'No'::text
        END AS "gwChemicalStatusExpectedGoodIn2015",
        CASE
            WHEN ((goc.oordeel)::text = 'Goed'::text) THEN NULL::text
            ELSE
            CASE
                WHEN (gpc_2021.prognose = 'Goed'::text) THEN '2016-2021'::text
                ELSE
                CASE
                    WHEN ((gpc_2027.prognose = 'Goed'::text) OR (gpc_2027.prognose IS NULL)) THEN '2022-2027'::text
                    ELSE 'Beyond 2027'::text
                END
            END
        END AS "gwChemicalStatusExpectedAchievementDate"
   FROM ((((((((((((((((((public.hydro_object
     LEFT JOIN public.hdo_names ON ((hdo_names.hdo_id = hydro_object.hdo_id)))
     JOIN public.water_body ON ((water_body.wbd_id = hydro_object.hdo_id)))
     JOIN public.ground_water_body ON ((ground_water_body.gwb_id = hydro_object.hdo_id)))
     JOIN public.river_basin_disrict rbd ON ((rbd.rbd_id = water_body.rbd_id)))
     JOIN public.hydro_object hdo_rbd ON ((rbd.rbd_id = hdo_rbd.hdo_id)))
     JOIN geological_formation_enum gfe ON ((ground_water_body.geological_formation_id = gfe.nl_id)))
     LEFT JOIN gwb_prot gpb ON ((gpb.wbd_id = hydro_object.hdo_id)))
     LEFT JOIN gwb_swb gsw ON ((gsw.gwb_id = hydro_object.hdo_id)))
     LEFT JOIN gwb_oordeel goc ON (((goc.gwb_id = hydro_object.hdo_id) AND (goc.opt_id = 4494))))
     LEFT JOIN gwb_oordeel goq ON (((goq.gwb_id = hydro_object.hdo_id) AND (goq.opt_id = 4495))))
     LEFT JOIN gwb_prognose_2021_4494 gpc_2021 ON ((gpc_2021.gwb_id = hydro_object.hdo_id)))
     LEFT JOIN gwb_prognose_2027_4494 gpc_2027 ON ((gpc_2027.gwb_id = hydro_object.hdo_id)))
     LEFT JOIN gwb_prognose_2021_4495 gpq_2021 ON ((gpq_2021.gwb_id = hydro_object.hdo_id)))
     LEFT JOIN gwb_prognose_2027_4495 gpq_2027 ON ((gpq_2027.gwb_id = hydro_object.hdo_id)))
     LEFT JOIN gwb_oordeel_subt_q ON ((gwb_oordeel_subt_q.gwb_id = hydro_object.hdo_id)))
     LEFT JOIN gwb_oordeel_subt_c ON ((gwb_oordeel_subt_c.gwb_id = hydro_object.hdo_id)))
     LEFT JOIN pressure ON ((pressure.db_id = hydro_object.hdo_id)))
     LEFT JOIN impact ON ((impact.db_id = hydro_object.hdo_id)))
  WHERE (((hydro_object.hydro_object_type)::text = 'GroundWaterBody'::text) AND (hydro_object.wkrv_id = 13))
  ORDER BY hydro_object.inspire_id_local_id;


--
-- Name: vw_groundwaterbody_groundwaterbody_gwreasonsforriskquantitative; Type: VIEW; Schema: wfd_reporting; Owner: -
--

CREATE VIEW vw_groundwaterbody_groundwaterbody_gwreasonsforriskquantitative AS
 WITH gwb_oordeel AS (
         SELECT hdo_gwb.hdo_id AS gwb_id,
            gwb_yearly_status.year_of_status AS jaar,
            gw_chemical_status.opt_id,
            classification_type.description AS oordeel,
            waarde_bepalings_methode_type.code AS wbpm
           FROM ((((public.gwb_yearly_status
             LEFT JOIN public.hydro_object hdo_gwb ON ((hdo_gwb.hdo_id = gwb_yearly_status.gwb_id)))
             JOIN public.gw_chemical_status ON ((gw_chemical_status.gys_id = gwb_yearly_status.gys_id)))
             LEFT JOIN public.classification_type ON ((gw_chemical_status.clst_id = classification_type.cls_id)))
             LEFT JOIN public.waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = gw_chemical_status.wbpm_id)))
          WHERE ((hdo_gwb.wkrv_id = 13) AND (gwb_yearly_status.year_of_status = ANY (ARRAY[2014, 2015])) AND ((waarde_bepalings_methode_type.code)::text = 'GWPROTNL'::text) AND (gw_chemical_status.opt_id = ANY (ARRAY[4495, 4494])))
        UNION ALL
         SELECT hdo_gwb.hdo_id AS gwb_id,
            gwb_yearly_status.year_of_status AS jaar,
            gw_quantity_status.opt_id,
            classification_type.description AS oordeel,
            waarde_bepalings_methode_type.code AS wbpm
           FROM ((((public.gwb_yearly_status
             LEFT JOIN public.hydro_object hdo_gwb ON ((hdo_gwb.hdo_id = gwb_yearly_status.gwb_id)))
             JOIN public.gw_quantity_status ON ((gw_quantity_status.gys_id = gwb_yearly_status.gys_id)))
             LEFT JOIN public.classification_type ON ((gw_quantity_status.gwst_id = classification_type.cls_id)))
             LEFT JOIN public.waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = gw_quantity_status.wbpm_id)))
          WHERE ((hdo_gwb.wkrv_id = 13) AND (gwb_yearly_status.year_of_status = ANY (ARRAY[2014, 2015])) AND ((waarde_bepalings_methode_type.code)::text = 'GWPROTNL'::text) AND (gw_quantity_status.opt_id = ANY (ARRAY[4495, 4494])))
        ), gwb_oordeel_sub AS (
         SELECT hdo_gwb.hdo_id AS gwb_id,
            gwb_yearly_status.year_of_status AS jaar,
            gw_chemical_status.opt_id,
            classification_type.description AS oordeel,
            waarde_bepalings_methode_type.code AS wbpm
           FROM ((((public.gwb_yearly_status
             LEFT JOIN public.hydro_object hdo_gwb ON ((hdo_gwb.hdo_id = gwb_yearly_status.gwb_id)))
             JOIN public.gw_chemical_status ON ((gw_chemical_status.gys_id = gwb_yearly_status.gys_id)))
             LEFT JOIN public.classification_type ON ((gw_chemical_status.clst_id = classification_type.cls_id)))
             LEFT JOIN public.waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = gw_chemical_status.wbpm_id)))
          WHERE ((hdo_gwb.wkrv_id = 13) AND (gwb_yearly_status.year_of_status = ANY (ARRAY[2014, 2015])) AND (gw_chemical_status.opt_id > 4400) AND (gw_chemical_status.opt_id <> ALL (ARRAY[4495, 4494, 4487])))
        UNION ALL
         SELECT hdo_gwb.hdo_id AS gwb_id,
            gwb_yearly_status.year_of_status AS jaar,
            gw_quantity_status.opt_id,
            classification_type.description AS oordeel,
            waarde_bepalings_methode_type.code AS wbpm
           FROM ((((public.gwb_yearly_status
             LEFT JOIN public.hydro_object hdo_gwb ON ((hdo_gwb.hdo_id = gwb_yearly_status.gwb_id)))
             JOIN public.gw_quantity_status ON ((gw_quantity_status.gys_id = gwb_yearly_status.gys_id)))
             LEFT JOIN public.classification_type ON ((gw_quantity_status.gwst_id = classification_type.cls_id)))
             LEFT JOIN public.waarde_bepalings_methode_type ON ((waarde_bepalings_methode_type.wbpm_id = gw_quantity_status.wbpm_id)))
          WHERE ((hdo_gwb.wkrv_id = 13) AND (gwb_yearly_status.year_of_status = ANY (ARRAY[2014, 2015])) AND (gw_quantity_status.opt_id > 4400) AND (gw_quantity_status.opt_id <> ALL (ARRAY[4495, 4494, 4487])))
  ORDER BY 1, 2, 3
        ), gwb_oordeel_subt AS (
         SELECT DISTINCT ON (gwb_oordeel_sub.gwb_id, gwb_oordeel_sub.opt_id) gwb_oordeel_sub.gwb_id,
            gwb_oordeel_sub.jaar,
            gwb_oordeel_sub.opt_id,
            gwb_oordeel_sub.oordeel
           FROM gwb_oordeel_sub
          ORDER BY gwb_oordeel_sub.gwb_id, gwb_oordeel_sub.opt_id, gwb_oordeel_sub.jaar DESC, gwb_oordeel_sub.wbpm
        )
 SELECT hydro_object.hdo_id AS db_id,
    hydro_object.inspire_id_local_id AS "euGroundWaterBodyCode",
        CASE gwb_oordeel_subt.opt_id
            WHEN 4490 THEN 'Saline or intrusions'::text
            WHEN 4488 THEN 'Surface water'::text
            WHEN 4489 THEN 'Groundwater dependent terrestial ecosystems'::text
            WHEN 4497 THEN 'Water balance'::text
            ELSE 'oeps, foutje'::text
        END AS "gwQuantitativeReasonsForFailure"
   FROM ((public.hydro_object
     JOIN gwb_oordeel ON (((gwb_oordeel.gwb_id = hydro_object.hdo_id) AND ((gwb_oordeel.oordeel)::text = 'Ontoereikend'::text) AND (gwb_oordeel.opt_id = 4495))))
     JOIN gwb_oordeel_subt ON (((gwb_oordeel_subt.gwb_id = hydro_object.hdo_id) AND ((gwb_oordeel_subt.oordeel)::text = 'Ontoereikend'::text) AND (gwb_oordeel_subt.opt_id = ANY (ARRAY[4488, 4489, 4490, 4497])))))
  WHERE (((hydro_object.hydro_object_type)::text = 'GroundWaterBody'::text) AND (hydro_object.wkrv_id = 13))
  ORDER BY hydro_object.inspire_id_local_id, gwb_oordeel_subt.opt_id;


--
