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

CREATE TABLE water_body (
    wbd_id integer NOT NULL,
    level_of_detail integer DEFAULT 10000 NOT NULL,
    water_body_subtype character varying(50) NOT NULL,
    rbd_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL,
    description text,
    is_article_7 boolean DEFAULT false NOT NULL,
    measure_comment character varying(2000),
    excemption_remarks text,
    doelen_toestand_remarks text
);


--
-- Name: COLUMN water_body.excemption_remarks; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN water_body.excemption_remarks IS 'Toelichting uitzonderingsbepalingen';


--
-- Name: COLUMN water_body.doelen_toestand_remarks; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN water_body.doelen_toestand_remarks IS 'Toelichting doelen toestand';


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

CREATE VIEW vw_waterlichamen 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,
    vw_competent_authority."Omschrijving" AS waterbeheerder_naam,
    hdo_names.name AS omschrijving,
    water_body.level_of_detail AS schaal,
    vw_sg_en_dsg_districten."Identificatie" AS stroomgebieddistrict,
    water_body.water_body_subtype AS waterlichaam_type,
    water_body.description AS karakterschets,
    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,
    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 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)));



--
-- Name: vw_toetsresultaat_csv; Type: VIEW; Schema: aquo_formaten; Owner: -
--

CREATE VIEW vw_toetsresultaat_csv AS
 SELECT vto."Waterlichaam_Identificatie" AS "Waterlichaam.identificatie",
    vwl.omschrijving AS "Waterlichaam.omschrijving",
    vto."Rapportagejaar",
    vto."Tpering_Code" AS "Typering.code",
    vto."Typering_Omschrijving" AS "Typering.omschrijving",
    vto."Grootheid_Code" AS "Grootheid.code",
    vto."Grootheid_Omschrijving" AS "Grootheid.omschrijving",
    vto."Hoedanigheid_Code" AS "Hoedanigheid.code",
    vto."Hoedanigheid_Omschrijving" AS "Hoedanigheid.omschrijving",
    vto."Eenheid_Code" AS "Eenheid.code",
    vto."Eenheid_Omschrijving" AS "Eenheid.omschrijving",
    vto."Waardebewerkingsmethode_Code" AS "Waardebewerkingsmethode.code",
    vto."Waardebewerkingsmethode_Omschrijving" AS "Waardebewerkingsmethode.omschrijving",
    vto."Waardebepalingsmethode_Code" AS "Waardebepalingsmethode.code",
    vto."Waardebepalingsmethode_Omschrijving" AS "Waardebepalingsmethode.omschrijving",
    vto."Begintijd_Toetsresultaat" AS "Begintijd.toetsresultaat",
    vto."Begintijd_Toetsreeks" AS "Begintijd.toetsreeks",
    vto."Eindtijd_Toetsreeks" AS "Eindtijd.toetsreeks",
    vto."Numerieke_waarde" AS "Numeriekewaarde",
    vto."Numerieke_waarde_Significantie" AS "Numeriekewaarde.significantie",
    vto."KwaliteitsOordeel_Code" AS "KwaliteitsOordeel.code",
    vto."KwaliteitsOordeel_Omschrijving" AS "KwaliteitsOordeel.omschrijving",
    vto."Oordeel_Type_Code" AS "Oordeeltype.code",
    vwl.waterbeheerder_code AS "Waterbeheerder.code",
    vwl.waterbeheerder_naam AS "Waterbeheerder.omschrijving",
    vwl.stroomgebieddistrict AS "Stroomgebieddistrict.code",
        CASE
            WHEN (vwl.drinkwateronttrekking_jn = true) THEN 'Ja'::text
            ELSE 'nee'::text
        END AS "Drinkwateronttrekking",
    vto."Waterlichaam_Versie" AS wkrv_id
   FROM (public.vw_toetsresultaat_owm vto
     JOIN public.vw_waterlichamen vwl ON (((vwl.identificatie)::text = (vto."Waterlichaam_Identificatie")::text)))
  WHERE (vto."Waterlichaam_Versie" = vwl.versie)
  ORDER BY vwl.waterbeheerder_code, vto."Waterlichaam_Identificatie", vto."Oordeel_Type_Code" DESC;


--
-- Name: vw_umaquo_meetpunten_shp; Type: VIEW; Schema: aquo_formaten; Owner: -
--

CREATE VIEW vw_umaquo_meetpunten_shp AS
 SELECT monitoring_object.inspire_id_local_id AS "MPNIDENT",
    ('NL'::text || (namespace_type.code)::text) AS "WBHCODE",
    monitoring_object.wkrv_id AS "VERSIE",
    monitoring_object.name AS "MPNOMSCH",
    monitoring_object.geometry,
    to_char(monitoring_object.begin_lifespan_version, 'YYYY-MM-DD'::text) AS "FL_BEGIN",
    to_char(monitoring_object.end_lifespan_version, 'YYYY-MM-DD'::text) AS "FL_EIND",
    monitoring_object.mno_id AS meetpunt_identificatie_db_id,
    monitoring_object.last_change_time
   FROM ((((((((public.monitoring_object
     LEFT JOIN public.namespace_type ON ((namespace_type.ns_id = monitoring_object.inspire_id_namespace_id)))
     LEFT JOIN public.monitoring_feature ON ((monitoring_feature.mnf_id = monitoring_object.mno_id)))
     LEFT JOIN public.monitoring_feature_positioning_method mfpm ON ((monitoring_feature.mnf_id = mfpm.mnf_id)))
     LEFT JOIN public.plaatsbepalingsapparaat_type pba ON ((pba.pba_id = mfpm.pba_id)))
     LEFT JOIN public.related_sampling_feature rsf ON ((rsf.mnf_id_source = monitoring_object.mno_id)))
     LEFT JOIN public.rel_samp_feat_role_type sf_rol ON ((sf_rol.rsfr_id = rsf.role_id)))
     LEFT JOIN public.monitoring_object mno_to ON ((rsf.mnf_id_target = mno_to.mno_id)))
     LEFT JOIN public.namespace_type ns2 ON ((ns2.ns_id = mno_to.inspire_id_namespace_id)))
  WHERE ((public.st_geometrytype(monitoring_object.geometry) = 'ST_Point'::text) AND ((monitoring_object.monitoring_object_type)::text ~~ 'MonitoringFeature%'::text));


--
-- Name: vw_umaquo_tijdwaarden_csv; Type: VIEW; Schema: aquo_formaten; Owner: -
--

CREATE VIEW vw_umaquo_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",
        CASE
            WHEN (observed_property_type.taxa_group_id IS NOT NULL) THEN 'Biotaxon'::text
            WHEN (observed_property_type.substance_group_id IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (observed_property_type.object_group_id IS NOT NULL) THEN 'Object'::text
            ELSE NULL::text
        END AS "Parameter.Groep",
        CASE
            WHEN (observed_property_type.taxa_group_id IS NOT NULL) THEN (NULL::text)::character varying
            WHEN (observed_property_type.substance_group_id IS NOT NULL) THEN chemische_stof_type.code
            WHEN (observed_property_type.object_group_id IS NOT NULL) THEN object_type.code
            ELSE NULL::character varying
        END AS "Parameter.Code",
        CASE
            WHEN (observed_property_type.taxa_group_id IS NOT NULL) THEN txn.omschrijving
            WHEN (observed_property_type.substance_group_id IS NOT NULL) THEN chemische_stof_type.naam
            WHEN (observed_property_type.object_group_id IS NOT NULL) THEN object_type.omschrijving
            ELSE NULL::character varying
        END AS "Parameter.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",
    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 (((((((((((((((((((((((public.observation
     JOIN public.observed_property_type ON ((observation.observed_property_type = observed_property_type.opt_id)))
     LEFT JOIN public.grootheid_type ON ((observed_property_type.quantity_id = grootheid_type.grh_id)))
     LEFT JOIN public.object_group ON ((object_group.ojg_id = observed_property_type.object_group_id)))
     LEFT JOIN public.object_group_element ON ((object_group.ojg_id = object_group_element.ojg_id)))
     LEFT JOIN public.object_type ON ((object_group_element.obj_id = object_type.obj_id)))
     LEFT JOIN public.typering_type ON ((typering_type.typ_id = observed_property_type.indicator_id)))
     LEFT JOIN public.monitored_compartiment ON ((monitored_compartiment.mno_id = observation.mno_id)))
     LEFT JOIN public.compartiment_type ON ((monitored_compartiment.cptm_id = compartiment_type.cptm_id)))
     LEFT JOIN public.substance_group_element ON ((observed_property_type.substance_group_id = substance_group_element.ssge_id)))
     LEFT JOIN public.chemische_stof_type ON ((chemische_stof_type.chs_id = substance_group_element.chs_id)))
     LEFT JOIN public.kwaliteitsoordeel_type ON ((observation.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN public.classification_group_type ON ((observation.classified_result_id = classification_group_type.clsg_id)))
     JOIN public.eenheid_type ON ((observation.numeric_value_uom_id = eenheid_type.eenh_id)))
     LEFT JOIN public.bepaling_grens_type ON ((observation.limit_symbol_id = bepaling_grens_type.bpg_id)))
     LEFT JOIN public.taxa_group txg ON ((txg.txg_id = observed_property_type.taxa_group_id)))
     LEFT JOIN public.taxa_group_element txe ON ((txe.txg_id = txg.txg_id)))
     LEFT JOIN public.taxon_type txn ON ((txn.txn_id = txe.txg_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type ON ((observation.value_processing_method_id = waarde_bewerkings_methode_type.wbm_id)))
     JOIN (public.namespace_type namespace_type_1
     JOIN public.monitoring_object ON ((namespace_type_1.ns_id = monitoring_object.inspire_id_namespace_id))) ON ((observation.mno_id = monitoring_object.mno_id)))
     LEFT JOIN public.hoedanigheid_type ON ((hoedanigheid_type.hdh_id = observed_property_type.hoedanigheid_id)))
     JOIN public.competentauthority_namespacetype cns ON ((cns.ns_id = namespace_type_1.ns_id)))
     JOIN public.organisatorische_eenheid ore ON ((ore.ore_id = cns.ore_id)))
     JOIN public.competent_authority ca ON ((ca.ore_id = ore.ore_id)));



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

CREATE TABLE logging (
    loggingid integer NOT NULL,
    severity character varying(25) NOT NULL,
    logtype character varying(256) NOT NULL,
    thema character varying(256) NOT NULL,
    wie character varying(256) NOT NULL,
    errormessage text,
    result text,
    status character varying(300),
    "timestamp" timestamp with time zone NOT NULL
);



--
-- Name: _test_vw_logging_login_wie_timestamp_last_24h; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW _test_vw_logging_login_wie_timestamp_last_24h AS
 SELECT DISTINCT
        CASE
            WHEN ((logging.wie)::text ~~ '%@%'::text) THEN logging.wie
            WHEN (logging.result ~~ '%@%'::text) THEN (logging.result)::character varying
            ELSE NULL::character varying
        END AS logins,
        CASE
            WHEN ((logging.wie)::text ~~ '%@%'::text) THEN 'WKP'::character varying
            WHEN (logging.result ~~ '%@%'::text) THEN 'KRM'::character varying
            ELSE NULL::character varying
        END AS systeem,
    logging."timestamp"
   FROM public.logging
  WHERE ((age(now(), logging."timestamp") < '1 day'::interval) AND (((logging.severity)::text = 'INFO'::text) OR ((logging.severity)::text = 'Info'::text)) AND (((logging.logtype)::text = 'Login'::text) OR ((logging.thema)::text = 'login'::text)))
  ORDER BY logging."timestamp" DESC;


--
-- Name: stat_user_tables_hist; Type: TABLE; Schema: beheer; Owner: -
--

CREATE TABLE stat_user_tables_hist (
    suth_id integer NOT NULL,
    "timestamp" timestamp with time zone,
    relid oid,
    schemaname name,
    relname name,
    seq_scan bigint,
    seq_tup_read bigint,
    idx_scan bigint,
    idx_tup_fetch bigint,
    n_tup_ins bigint,
    n_tup_upd bigint,
    n_tup_del bigint,
    n_tup_hot_upd bigint,
    n_live_tup bigint,
    n_dead_tup bigint,
    n_mod_since_analyze bigint,
    last_vacuum timestamp with time zone,
    last_autovacuum timestamp with time zone,
    last_analyze timestamp with time zone,
    last_autoanalyze timestamp with time zone,
    vacuum_count bigint,
    autovacuum_count bigint,
    analyze_count bigint,
    autoanalyze_count bigint,
    n_live_tup_real bigint
);


--
-- Name: stat_user_tables_hist_suth_id_seq; Type: SEQUENCE; Schema: beheer; Owner: -
--

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


--
-- Name: stat_user_tables_hist_suth_id_seq; Type: SEQUENCE OWNED BY; Schema: beheer; Owner: -
--

ALTER SEQUENCE stat_user_tables_hist_suth_id_seq OWNED BY stat_user_tables_hist.suth_id;


--
-- Name: vw_grp; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_grp AS
 SELECT 'txg'::text AS "grp.cat",
    txg.txg_id AS "grp.grp_id",
    txg.name AS "grp.name",
    txg.group_type AS "grp.group_type",
    txg.d_begin AS "grp.d_begin",
    txg.d_eind AS "grp.d_eind",
    txg.last_changed_date AS "grp.last_changed_date",
    txg.d_status AS "grp.d_status"
   FROM public.taxa_group txg
UNION ALL
 SELECT 'ojg'::text AS "grp.cat",
    ojg.ojg_id AS "grp.grp_id",
    ojg.name AS "grp.name",
    ojg.group_type AS "grp.group_type",
    ojg.d_begin AS "grp.d_begin",
    ojg.d_eind AS "grp.d_eind",
    ojg.last_changed_date AS "grp.last_changed_date",
    ojg.d_status AS "grp.d_status"
   FROM public.object_group ojg
UNION ALL
 SELECT 'ssg'::text AS "grp.cat",
    ssg.ssg_id AS "grp.grp_id",
    ssg.name AS "grp.name",
    ssg.group_type AS "grp.group_type",
    ssg.d_begin AS "grp.d_begin",
    ssg.d_eind AS "grp.d_eind",
    ssg.last_changed_date AS "grp.last_changed_date",
    ssg.d_status AS "grp.d_status"
   FROM public.substance_group ssg;


--
-- Name: vw_grp_elt; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_grp_elt AS
 SELECT 'txg'::text AS "grp.cat",
    txg.txg_id AS "grp.grp_id",
    txg.name AS "grp.name",
    txg.group_type AS "grp.group_type",
    txg.d_begin AS "grp.d_begin",
    txg.d_eind AS "grp.d_eind",
    txg.last_changed_date AS "grp.last_changed_date",
    txg.d_status AS "grp.d_status",
    'txn'::text AS "elt.cat",
    txn.txn_id AS "elt.elt_id",
    NULL::text AS "elt.elt_code",
    txn.code AS "elt.cas_nr",
    txn.omschrijving AS "elt.name",
    txn.auteur AS "elt.group_type",
    txn.d_begin AS "elt.d_begin",
    txn.d_eind AS "elt.d_eind",
    txn.last_changed_date AS "elt.last_changed_date",
    txn.d_status AS "elt.d_status",
    NULL::integer AS "elt.id"
   FROM ((public.taxa_group txg
     LEFT JOIN public.taxa_group_element tge ON ((txg.txg_id = tge.txg_id)))
     LEFT JOIN public.taxon_type txn ON ((tge.txn_id = txn.txn_id)))
UNION ALL
 SELECT 'ojg'::text AS "grp.cat",
    ojg.ojg_id AS "grp.grp_id",
    ojg.name AS "grp.name",
    ojg.group_type AS "grp.group_type",
    ojg.d_begin AS "grp.d_begin",
    ojg.d_eind AS "grp.d_eind",
    ojg.last_changed_date AS "grp.last_changed_date",
    ojg.d_status AS "grp.d_status",
    'obj'::text AS "elt.cat",
    obj.obj_id AS "elt.elt_id",
    NULL::text AS "elt.elt_code",
    obj.code AS "elt.cas_nr",
    obj.omschrijving AS "elt.name",
    NULL::character varying AS "elt.group_type",
    obj.d_begin AS "elt.d_begin",
    obj.d_eind AS "elt.d_eind",
    obj.last_changed_date AS "elt.last_changed_date",
    obj.d_status AS "elt.d_status",
    obj.id AS "elt.id"
   FROM ((public.object_group ojg
     LEFT JOIN public.object_group_element oge ON ((ojg.ojg_id = oge.ojg_id)))
     LEFT JOIN public.object_type obj ON ((oge.obj_id = obj.obj_id)))
UNION ALL
 SELECT 'ssg'::text AS "grp.cat",
    ssg.ssg_id AS "grp.grp_id",
    ssg.name AS "grp.name",
    ssg.group_type AS "grp.group_type",
    ssg.d_begin AS "grp.d_begin",
    ssg.d_eind AS "grp.d_eind",
    ssg.last_changed_date AS "grp.last_changed_date",
    ssg.d_status AS "grp.d_status",
    'chs'::text AS "elt.cat",
    chs.chs_id AS "elt.elt_id",
    chs.cas_nr AS "elt.elt_code",
    chs.code AS "elt.cas_nr",
    chs.naam AS "elt.name",
    NULL::text AS "elt.group_type",
    chs.d_begin AS "elt.d_begin",
    chs.d_eind AS "elt.d_eind",
    chs.last_changed_date AS "elt.last_changed_date",
    chs.d_status AS "elt.d_status",
    chs.id AS "elt.id"
   FROM ((public.substance_group ssg
     LEFT JOIN public.substance_group_element ssge ON ((ssg.ssg_id = ssge.ssge_id)))
     LEFT JOIN public.chemische_stof_type chs ON ((ssge.chs_id = chs.chs_id)));


--
-- Name: vw_logging_login_uniek_totaal_last_24h; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_logging_login_uniek_totaal_last_24h AS
 SELECT count(DISTINCT logging.wie) AS count
   FROM public.logging
  WHERE ((age(now(), logging."timestamp") < '1 day'::interval) AND ((logging.logtype)::text = 'Login'::text));


--
-- Name: VIEW vw_logging_login_uniek_totaal_last_24h; Type: COMMENT; Schema: beheer; Owner: -
--

COMMENT ON VIEW vw_logging_login_uniek_totaal_last_24h IS 'tbv funtioneel beheer';


--
-- Name: vw_logging_login_wie_last_24h; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_logging_login_wie_last_24h AS
 SELECT DISTINCT
        CASE
            WHEN ((logging.wie)::text ~~ '%@%'::text) THEN logging.wie
            WHEN (logging.result ~~ '%@%'::text) THEN (logging.result)::character varying
            ELSE NULL::character varying
        END AS logins
   FROM public.logging
  WHERE ((age(now(), logging."timestamp") < '1 day'::interval) AND (((logging.severity)::text = 'INFO'::text) OR ((logging.severity)::text = 'Info'::text)) AND (((logging.logtype)::text = 'Login'::text) OR ((logging.thema)::text = 'login'::text)))
  ORDER BY
        CASE
            WHEN ((logging.wie)::text ~~ '%@%'::text) THEN logging.wie
            WHEN (logging.result ~~ '%@%'::text) THEN (logging.result)::character varying
            ELSE NULL::character varying
        END;


--
-- Name: VIEW vw_logging_login_wie_last_24h; Type: COMMENT; Schema: beheer; Owner: -
--

COMMENT ON VIEW vw_logging_login_wie_last_24h IS 'users last 24 hrs tbv functioneel beheer';



--
-- Name: planned_observation_time; Type: TABLE; Schema: monprog; Owner: -
--

CREATE TABLE planned_observation_time (
    pot_id integer NOT NULL,
    qem_id integer NOT NULL,
    start_date date DEFAULT '2015-01-01'::date NOT NULL,
    end_date date DEFAULT '2021-12-31'::date NOT NULL,
    noof_samples integer DEFAULT 1,
    samples_bepalingsgrens_id integer,
    last_change_time timestamp without time zone NOT NULL
);



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

CREATE TABLE monitoring_object_quality_element (
    moqe_id integer NOT NULL,
    mno_id integer NOT NULL,
    qem_id integer NOT NULL
);


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

CREATE TABLE observing_capability (
    osc_id integer NOT NULL,
    observing_time_begin timestamp without time zone NOT NULL,
    observing_time_end timestamp without time zone,
    online_resource character varying(255),
    process_type_id integer,
    last_change_time timestamp without time zone NOT NULL,
    name character varying(255),
    is_sample boolean DEFAULT false NOT NULL,
    wkrv_id integer
);


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

CREATE TABLE quality_element (
    qem_id integer NOT NULL,
    osc_id integer NOT NULL,
    opt_id integer NOT NULL,
    parameter_use_id integer,
    cycle_value integer,
    cycle_uom_id integer,
    cycle_description text,
    frequency_value integer,
    frequency_uom_id integer,
    reason_deviation_program text,
    last_change_time timestamp without time zone NOT NULL,
    sampling_method_id integer,
    sampling_device_id integer,
    deviation_program_jn boolean DEFAULT false,
    opt_uom_id integer,
    wbm_id integer,
    remark text,
    CONSTRAINT chk_reason_deviation_program CHECK (((deviation_program_jn AND (reason_deviation_program IS NOT NULL)) OR (NOT deviation_program_jn)))
);



--
-- Name: vw_monprogs; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_monprogs AS
 SELECT txg.txg_id AS "txg.txg_id",
    txg.name AS "txg.name",
    txg.group_type AS "txg.group_type",
    txg.d_begin AS "txg.d_begin",
    txg.d_eind AS "txg.d_eind",
    txg.last_changed_date AS "txg.last_changed_date",
    txg.d_status AS "txg.d_status",
    ojg.ojg_id AS "ojg.ojg_id",
    ojg.name AS "ojg.name",
    ojg.group_type AS "ojg.group_type",
    ojg.d_begin AS "ojg.d_begin",
    ojg.d_eind AS "ojg.d_eind",
    ojg.last_changed_date AS "ojg.last_changed_date",
    ojg.d_status AS "ojg.d_status",
    ssg.ssg_id AS "ssg.ssg_id",
    ssg.name AS "ssg.name",
    ssg.group_type AS "ssg.group_type",
    ssg.d_begin AS "ssg.d_begin",
    ssg.d_eind AS "ssg.d_eind",
    ssg.last_changed_date AS "ssg.last_changed_date",
    ssg.d_status AS "ssg.d_status",
    opt.opt_id AS "opt.opt_id",
    opt.observed_property_type_class AS "opt.observed_property_type_class",
    opt.result_type AS "opt.result_type",
    opt.hoedanigheid_id AS "opt.hoedanigheid_id",
    opt.object_group_id AS "opt.object_group_id",
    opt.quantity_id AS "opt.quantity_id",
    opt.taxa_group_id AS "opt.taxa_group_id",
    opt.substance_group_id AS "opt.substance_group_id",
    opt.indicator_id AS "opt.indicator_id",
    opt.classification_group_id AS "opt.classification_group_id",
    opt.remarks AS "opt.remarks",
    opt.d_begin AS "opt.d_begin",
    opt.d_eind AS "opt.d_eind",
    opt.last_changed_date AS "opt.last_changed_date",
    opt.d_status AS "opt.d_status",
    hdh.hdh_id AS "hdh.hdh_id",
    hdh.code AS "hdh.code",
    hdh.omschrijving AS "hdh.omschrijving",
    hdh.d_begin AS "hdh.d_begin",
    hdh.d_eind AS "hdh.d_eind",
    hdh.last_changed_date AS "hdh.last_changed_date",
    hdh.d_status AS "hdh.d_status",
    hdh.id AS "hdh.id",
    grh.grh_id AS "grh.grh_id",
    grh.code AS "grh.code",
    grh.omschrijving AS "grh.omschrijving",
    grh.d_begin AS "grh.d_begin",
    grh.d_eind AS "grh.d_eind",
    grh.last_changed_date AS "grh.last_changed_date",
    grh.d_status AS "grh.d_status",
    grh.id AS "grh.id",
    typ.typ_id AS "typ.typ_id",
    typ.code AS "typ.code",
    typ.omschrijving AS "typ.omschrijving",
    typ.d_begin AS "typ.d_begin",
    typ.d_eind AS "typ.d_eind",
    typ.last_changed_date AS "typ.last_changed_date",
    typ.d_status AS "typ.d_status",
    typ.id AS "typ.id",
    clsg.clsg_id AS "clsg.clsg_id",
    clsg.code AS "clsg.code",
    clsg.description AS "clsg.description",
    clsg.d_begin AS "clsg.d_begin",
    clsg.d_eind AS "clsg.d_eind",
    clsg.last_changed_date AS "clsg.last_changed_date",
    clsg.d_status AS "clsg.d_status",
    qem.qem_id AS "qem.qem_id",
    qem.osc_id AS "qem.osc_id",
    qem.opt_id AS "qem.opt_id",
    qem.parameter_use_id AS "qem.parameter_use_id",
    qem.cycle_value AS "qem.cycle_value",
    qem.cycle_uom_id AS "qem.cycle_uom_id",
    qem.cycle_description AS "qem.cycle_description",
    qem.frequency_value AS "qem.frequency_value",
    qem.frequency_uom_id AS "qem.frequency_uom_id",
    qem.reason_deviation_program AS "qem.reason_deviation_program",
    qem.last_change_time AS "qem.last_change_time",
    qem.sampling_method_id AS "qem.sampling_method_id",
    qem.sampling_device_id AS "qem.sampling_device_id",
    qem.deviation_program_jn AS "qem.deviation_program_jn",
    qem.opt_uom_id AS "qem.opt_uom_id",
    qem.wbm_id AS "qem.wbm_id",
    qem.remark AS "qem.remark",
    ehd.eenh_id AS "ehd.eenh_id",
    ehd.code AS "ehd.code",
    ehd.omschrijving AS "ehd.omschrijving",
    ehd.d_begin AS "ehd.d_begin",
    ehd.d_eind AS "ehd.d_eind",
    ehd.last_changed_date AS "ehd.last_changed_date",
    ehd.d_status AS "ehd.d_status",
    ehd.id AS "ehd.id",
    var.var_id AS "var.var_id",
    var.code AS "var.code",
    var.description AS "var.description",
    var.d_begin AS "var.d_begin",
    var.d_eind AS "var.d_eind",
    var.last_changed_date AS "var.last_changed_date",
    var.d_status AS "var.d_status",
    var.id AS "var.id",
    var.codespace AS "var.codespace",
    var.groep AS "var.groep",
    wbm.wbm_id AS "wbm.wbm_id",
    wbm.code AS "wbm.code",
    wbm.omschrijving AS "wbm.omschrijving",
    wbm.d_begin AS "wbm.d_begin",
    wbm.d_eind AS "wbm.d_eind",
    wbm.last_changed_date AS "wbm.last_changed_date",
    wbm.d_status AS "wbm.d_status",
    wbm.id AS "wbm.id",
    bmm.bmm_id AS "bmm.bmm_id",
    bmm.code AS "bmm.code ",
    bmm.description AS "bmm.description",
    bmm.groep AS "bmm.groep",
    bmm.titel AS "bmm.titel",
    bmm.d_begin AS "bmm.d_begin",
    bmm.d_eind AS "bmm.d_eind ",
    bmm.last_changed_date AS "bmm.last_changed_date",
    bmm.d_status AS "bmm.d_status ",
    bmm.id AS "bmm.id ",
    osc.osc_id AS "osc.osc_id",
    osc.observing_time_begin AS "osc.observing_time_begin",
    osc.observing_time_end AS "osc.observing_time_end",
    osc.online_resource AS "osc.online_resource",
    osc.process_type_id AS "osc.process_type_id",
    osc.last_change_time AS "osc.last_change_time",
    osc.name AS "osc.name",
    osc.is_sample AS "osc.is_sample",
    osc.wkrv_id AS "osc.wkrv_id",
    moqe.moqe_id AS "moqe.moqe_id",
    moqe.mno_id AS "moqe.mno_id",
    moqe.qem_id AS "moqe.qem_id",
    mno.mno_id AS "mno.mno_id",
    mno.inspire_id_local_id AS "mno.inspire_id_local_id",
    mno.inspire_id_namespace_id AS "mno.inspire_id_namespace_id",
    mno.wkrv_id AS "mno.wkrv_id",
    mno.monitoring_object_type AS "mno.monitoring_object_type",
    mno.name AS "mno.name",
    mno.additional_description AS "mno.additional_description",
    mno.last_change_time AS "mno.last_change_time",
    mno.begin_lifespan_version AS "mno.begin_lifespan_version",
    mno.end_lifespan_version AS "mno.end_lifespan_version",
    mno.primary_geo_column AS "mno.primary_geo_column",
    mno.reason_change AS "mno.reason_change",
    mno.primary_geodetic_reference_id AS "mno.primary_geodetic_reference_id",
    pot.pot_id AS "pot.pot_id",
    pot.qem_id AS "pot.qem_id",
    pot.start_date AS "pot.start_date",
    pot.end_date AS "pot.end_date",
    pot.noof_samples AS "pot.noof_samples",
    pot.samples_bepalingsgrens_id AS "pot.samples_bepalingsgrens_id",
    pot.last_change_time AS "pot.last_change_time",
    bpg.bpg_id AS "bpg.bpg_id",
    bpg.code AS "bpg.code",
    bpg.omschrijving AS "bpg.omschrijving",
    bpg.d_begin AS "bpg.d_begin",
    bpg.d_eind AS "bpg.d_eind",
    bpg.last_changed_date AS "bpg.last_changed_date",
    bpg.d_status AS "bpg.d_status"
   FROM (((((((((((((((((public.observed_property_type opt
     LEFT JOIN public.taxa_group txg ON ((opt.taxa_group_id = txg.txg_id)))
     LEFT JOIN public.object_group ojg ON ((opt.object_group_id = ojg.ojg_id)))
     LEFT JOIN public.substance_group ssg ON ((opt.substance_group_id = ssg.ssg_id)))
     LEFT JOIN public.hoedanigheid_type hdh ON ((hdh.hdh_id = opt.hoedanigheid_id)))
     LEFT JOIN public.grootheid_type grh ON ((grh.grh_id = opt.quantity_id)))
     LEFT JOIN public.typering_type typ ON ((typ.typ_id = opt.indicator_id)))
     LEFT JOIN public.classification_group_type clsg ON ((clsg.clsg_id = opt.classification_group_id)))
     LEFT JOIN public.quality_element qem ON ((qem.opt_id = opt.opt_id)))
     LEFT JOIN public.eenheid_type ehd ON ((ehd.eenh_id = qem.opt_uom_id)))
     LEFT JOIN domains.veldapparaat var ON ((var.var_id = qem.sampling_device_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type wbm ON ((wbm.wbm_id = qem.wbm_id)))
     LEFT JOIN domains.bemonsteringsmethode bmm ON ((bmm.bmm_id = qem.sampling_method_id)))
     LEFT JOIN public.observing_capability osc ON ((osc.osc_id = qem.osc_id)))
     LEFT JOIN public.monitoring_object_quality_element moqe ON ((moqe.qem_id = qem.qem_id)))
     LEFT JOIN public.monitoring_object mno ON ((mno.mno_id = moqe.mno_id)))
     LEFT JOIN monprog.planned_observation_time pot ON ((pot.qem_id = qem.qem_id)))
     LEFT JOIN public.bepaling_grens_type bpg ON ((bpg.bpg_id = pot.samples_bepalingsgrens_id)));


--
-- Name: vw_opt; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_opt AS
 SELECT opt.opt_id AS "opt.opt_id",
    opt.observed_property_type_class AS "opt.observed_property_type_class",
    opt.result_type AS "opt.result_type",
    opt.hoedanigheid_id AS "opt.hoedanigheid_id",
        CASE
            WHEN (opt.object_group_id IS NOT NULL) THEN 'ojg'::text
            WHEN (opt.taxa_group_id IS NOT NULL) THEN 'txg'::text
            WHEN (opt.substance_group_id IS NOT NULL) THEN 'ssg'::text
            ELSE NULL::text
        END AS "opt.grp.cat",
        CASE
            WHEN (opt.object_group_id IS NOT NULL) THEN opt.object_group_id
            WHEN (opt.taxa_group_id IS NOT NULL) THEN opt.taxa_group_id
            WHEN (opt.substance_group_id IS NOT NULL) THEN opt.substance_group_id
            ELSE NULL::integer
        END AS "opt.grp_id",
    opt.quantity_id AS "opt.quantity_id",
    opt.indicator_id AS "opt.indicator_id",
    opt.classification_group_id AS "opt.classification_group_id",
    opt.remarks AS "opt.remarks",
    opt.d_begin AS "opt.d_begin",
    opt.d_eind AS "opt.d_eind",
    opt.last_changed_date AS "opt.last_changed_date",
    opt.d_status AS "opt.d_status",
    hdh.hdh_id AS "hdh.hdh_id",
    hdh.code AS "hdh.code",
    hdh.omschrijving AS "hdh.omschrijving",
    hdh.d_begin AS "hdh.d_begin",
    hdh.d_eind AS "hdh.d_eind",
    hdh.last_changed_date AS "hdh.last_changed_date",
    hdh.d_status AS "hdh.d_status",
    hdh.id AS "hdh.id",
    grh.grh_id AS "grh.grh_id",
    grh.code AS "grh.code",
    grh.omschrijving AS "grh.omschrijving",
    grh.d_begin AS "grh.d_begin",
    grh.d_eind AS "grh.d_eind",
    grh.last_changed_date AS "grh.last_changed_date",
    grh.d_status AS "grh.d_status",
    grh.id AS "grh.id",
    typ.typ_id AS "typ.typ_id",
    typ.code AS "typ.code",
    typ.omschrijving AS "typ.omschrijving",
    typ.d_begin AS "typ.d_begin",
    typ.d_eind AS "typ.d_eind",
    typ.last_changed_date AS "typ.last_changed_date",
    typ.d_status AS "typ.d_status",
    typ.id AS "typ.id",
    clsg.clsg_id AS "clsg.clsg_id",
    clsg.code AS "clsg.code",
    clsg.description AS "clsg.description",
    clsg.d_begin AS "clsg.d_begin",
    clsg.d_eind AS "clsg.d_eind",
    clsg.last_changed_date AS "clsg.last_changed_date",
    clsg.d_status AS "clsg.d_status"
   FROM ((((public.observed_property_type opt
     LEFT JOIN public.hoedanigheid_type hdh ON ((hdh.hdh_id = opt.hoedanigheid_id)))
     LEFT JOIN public.grootheid_type grh ON ((grh.grh_id = opt.quantity_id)))
     LEFT JOIN public.typering_type typ ON ((typ.typ_id = opt.indicator_id)))
     LEFT JOIN public.classification_group_type clsg ON ((clsg.clsg_id = opt.classification_group_id)));



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

CREATE TABLE observing_capability_responsible_party (
    ocrp_id integer NOT NULL,
    osc_id integer NOT NULL,
    ore_id integer NOT NULL
);



--
-- Name: vw_osc_qem_pot; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_osc_qem_pot AS
 SELECT osc.osc_id AS "osc.osc_id",
    osc.observing_time_begin AS "osc.observing_time_begin",
    osc.observing_time_end AS "osc.observing_time_end",
    osc.online_resource AS "osc.online_resource",
    osc.process_type_id AS "osc.process_type_id",
    osc.last_change_time AS "osc.last_change_time",
    osc.name AS "osc.name",
    osc.is_sample AS "osc.is_sample",
    osc.wkrv_id AS "osc.wkrv_id",
    ocrp.ocrp_id AS "ocrp.ocrp_id",
    ore.ore_id AS "ore.ore_id",
    ore.naam_verkort AS "ore.naam_verkort",
    qem.qem_id AS "qem.qem_id",
    qem.osc_id AS "qem.osc_id",
    qem.opt_id AS "qem.opt_id",
    qem.parameter_use_id AS "qem.parameter_use_id",
    qem.cycle_value AS "qem.cycle_value",
    qem.cycle_uom_id AS "qem.cycle_uom_id",
    qem.cycle_description AS "qem.cycle_description",
    qem.frequency_value AS "qem.frequency_value",
    qem.frequency_uom_id AS "qem.frequency_uom_id",
    qem.reason_deviation_program AS "qem.reason_deviation_program",
    qem.last_change_time AS "qem.last_change_time",
    qem.sampling_method_id AS "qem.sampling_method_id",
    qem.sampling_device_id AS "qem.sampling_device_id",
    qem.deviation_program_jn AS "qem.deviation_program_jn",
    qem.opt_uom_id AS "qem.opt_uom_id",
    qem.wbm_id AS "qem.wbm_id",
    qem.remark AS "qem.remark",
    opt.opt_id AS "qem.opt.opt_id",
    opt.observed_property_type_class AS "qem.opt.observed_property_type_class",
    opt.result_type AS "qem.opt.result_type",
    ehd.eenh_id AS "ehd.eenh_id",
    ehd.code AS "ehd.code",
    ehd.omschrijving AS "ehd.omschrijving",
    ehd.d_begin AS "ehd.d_begin",
    ehd.d_eind AS "ehd.d_eind",
    ehd.last_changed_date AS "ehd.last_changed_date",
    ehd.d_status AS "ehd.d_status",
    ehd.id AS "ehd.id",
    var.var_id AS "var.var_id",
    var.code AS "var.code",
    var.description AS "var.description",
    var.d_begin AS "var.d_begin",
    var.d_eind AS "var.d_eind",
    var.last_changed_date AS "var.last_changed_date",
    var.d_status AS "var.d_status",
    var.id AS "var.id",
    var.codespace AS "var.codespace",
    var.groep AS "var.groep",
    wbm.wbm_id AS "wbm.wbm_id",
    wbm.code AS "wbm.code",
    wbm.omschrijving AS "wbm.omschrijving",
    wbm.d_begin AS "wbm.d_begin",
    wbm.d_eind AS "wbm.d_eind",
    wbm.last_changed_date AS "wbm.last_changed_date",
    wbm.d_status AS "wbm.d_status",
    wbm.id AS "wbm.id",
    bmm.bmm_id AS "bmm.bmm_id",
    bmm.code AS "bmm.code ",
    bmm.description AS "bmm.description",
    bmm.groep AS "bmm.groep",
    bmm.titel AS "bmm.titel",
    bmm.d_begin AS "bmm.d_begin",
    bmm.d_eind AS "bmm.d_eind ",
    bmm.last_changed_date AS "bmm.last_changed_date",
    bmm.d_status AS "bmm.d_status ",
    bmm.id AS "bmm.id ",
    moqe.moqe_id AS "moqe.moqe_id",
    moqe.mno_id AS "moqe.mno_id",
    moqe.qem_id AS "moqe.qem_id",
    mno.mno_id AS "mno.mno_id",
    mno.inspire_id_local_id AS "mno.inspire_id_local_id",
    mno.inspire_id_namespace_id AS "mno.inspire_id_namespace_id",
    mno.wkrv_id AS "mno.wkrv_id",
    mno.monitoring_object_type AS "mno.monitoring_object_type",
    mno.name AS "mno.name",
    mno.additional_description AS "mno.additional_description",
    mno.last_change_time AS "mno.last_change_time",
    mno.begin_lifespan_version AS "mno.begin_lifespan_version",
    mno.end_lifespan_version AS "mno.end_lifespan_version",
    mno.primary_geo_column AS "mno.primary_geo_column",
    mno.reason_change AS "mno.reason_change",
    mno.primary_geodetic_reference_id AS "mno.primary_geodetic_reference_id",
    pot.pot_id AS "pot.pot_id",
    pot.qem_id AS "pot.qem_id",
    pot.start_date AS "pot.start_date",
    pot.end_date AS "pot.end_date",
    pot.noof_samples AS "pot.noof_samples",
    pot.samples_bepalingsgrens_id AS "pot.samples_bepalingsgrens_id",
    pot.last_change_time AS "pot.last_change_time",
    bpg.bpg_id AS "bpg.bpg_id",
    bpg.code AS "bpg.code",
    bpg.omschrijving AS "bpg.omschrijving",
    bpg.d_begin AS "bpg.d_begin",
    bpg.d_eind AS "bpg.d_eind",
    bpg.last_changed_date AS "bpg.last_changed_date",
    bpg.d_status AS "bpg.d_status"
   FROM (((((((((((((public.observing_capability osc
     LEFT JOIN public.observing_capability_responsible_party ocrp ON ((ocrp.osc_id = osc.osc_id)))
     LEFT JOIN public.organisatorische_eenheid ore ON ((ore.ore_id = ocrp.ore_id)))
     LEFT JOIN public.wkr_version wkrv ON ((wkrv.wkrv_id = osc.wkrv_id)))
     LEFT JOIN public.quality_element qem ON ((osc.osc_id = qem.osc_id)))
     LEFT JOIN public.observed_property_type opt ON ((opt.opt_id = qem.opt_id)))
     LEFT JOIN public.eenheid_type ehd ON ((ehd.eenh_id = qem.opt_uom_id)))
     LEFT JOIN domains.veldapparaat var ON ((var.var_id = qem.sampling_device_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type wbm ON ((wbm.wbm_id = qem.wbm_id)))
     LEFT JOIN domains.bemonsteringsmethode bmm ON ((bmm.bmm_id = qem.sampling_method_id)))
     LEFT JOIN public.monitoring_object_quality_element moqe ON ((moqe.qem_id = qem.qem_id)))
     LEFT JOIN public.monitoring_object mno ON ((mno.mno_id = moqe.mno_id)))
     LEFT JOIN monprog.planned_observation_time pot ON ((pot.qem_id = qem.qem_id)))
     LEFT JOIN public.bepaling_grens_type bpg ON ((bpg.bpg_id = pot.samples_bepalingsgrens_id)));


--
-- Name: vw_monprogs_grp; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_monprogs_grp AS
 SELECT opt."opt.opt_id",
    opt."opt.observed_property_type_class",
    opt."opt.result_type",
    opt."opt.hoedanigheid_id",
    opt."opt.quantity_id",
    opt."opt.grp.cat",
    opt."opt.grp_id",
    opt."opt.indicator_id",
    opt."opt.classification_group_id",
    opt."opt.remarks",
    opt."opt.d_begin",
    opt."opt.d_eind",
    opt."opt.last_changed_date",
    opt."opt.d_status",
    opt."hdh.hdh_id",
    opt."hdh.code",
    opt."hdh.omschrijving",
    opt."hdh.d_begin",
    opt."hdh.d_eind",
    opt."hdh.last_changed_date",
    opt."hdh.d_status",
    opt."hdh.id",
    opt."grh.grh_id",
    opt."grh.code",
    opt."grh.omschrijving",
    opt."grh.d_begin",
    opt."grh.d_eind",
    opt."grh.last_changed_date",
    opt."grh.d_status",
    opt."grh.id",
    opt."typ.typ_id",
    opt."typ.code",
    opt."typ.omschrijving",
    opt."typ.d_begin",
    opt."typ.d_eind",
    opt."typ.last_changed_date",
    opt."typ.d_status",
    opt."typ.id",
    opt."clsg.clsg_id",
    opt."clsg.code",
    opt."clsg.description",
    opt."clsg.d_begin",
    opt."clsg.d_eind",
    opt."clsg.last_changed_date",
    opt."clsg.d_status",
    grp."grp.cat",
    grp."grp.grp_id",
    grp."grp.name",
    grp."grp.group_type",
    grp."grp.d_begin",
    grp."grp.d_eind",
    grp."grp.last_changed_date",
    grp."grp.d_status",
    oqp."osc.osc_id",
    oqp."osc.observing_time_begin",
    oqp."osc.observing_time_end",
    oqp."osc.online_resource",
    oqp."osc.process_type_id",
    oqp."osc.last_change_time",
    oqp."osc.name",
    oqp."osc.is_sample",
    oqp."osc.wkrv_id",
    oqp."ocrp.ocrp_id",
    oqp."ore.ore_id",
    oqp."ore.naam_verkort",
    oqp."qem.qem_id",
    oqp."qem.osc_id",
    oqp."qem.opt_id",
    oqp."qem.parameter_use_id",
    oqp."qem.cycle_value",
    oqp."qem.cycle_uom_id",
    oqp."qem.cycle_description",
    oqp."qem.frequency_value",
    oqp."qem.frequency_uom_id",
    oqp."qem.reason_deviation_program",
    oqp."qem.last_change_time",
    oqp."qem.sampling_method_id",
    oqp."qem.sampling_device_id",
    oqp."qem.deviation_program_jn",
    oqp."qem.opt_uom_id",
    oqp."qem.wbm_id",
    oqp."qem.remark",
    oqp."qem.opt.opt_id",
    oqp."qem.opt.observed_property_type_class",
    oqp."qem.opt.result_type",
    oqp."ehd.eenh_id",
    oqp."ehd.code",
    oqp."ehd.omschrijving",
    oqp."ehd.d_begin",
    oqp."ehd.d_eind",
    oqp."ehd.last_changed_date",
    oqp."ehd.d_status",
    oqp."ehd.id",
    oqp."var.var_id",
    oqp."var.code",
    oqp."var.description",
    oqp."var.d_begin",
    oqp."var.d_eind",
    oqp."var.last_changed_date",
    oqp."var.d_status",
    oqp."var.id",
    oqp."var.codespace",
    oqp."var.groep",
    oqp."wbm.wbm_id",
    oqp."wbm.code",
    oqp."wbm.omschrijving",
    oqp."wbm.d_begin",
    oqp."wbm.d_eind",
    oqp."wbm.last_changed_date",
    oqp."wbm.d_status",
    oqp."wbm.id",
    oqp."bmm.bmm_id",
    oqp."bmm.code ",
    oqp."bmm.description",
    oqp."bmm.groep",
    oqp."bmm.titel",
    oqp."bmm.d_begin",
    oqp."bmm.d_eind ",
    oqp."bmm.last_changed_date",
    oqp."bmm.d_status ",
    oqp."bmm.id ",
    oqp."moqe.moqe_id",
    oqp."moqe.mno_id",
    oqp."moqe.qem_id",
    oqp."mno.mno_id",
    oqp."mno.inspire_id_local_id",
    oqp."mno.inspire_id_namespace_id",
    oqp."mno.wkrv_id",
    oqp."mno.monitoring_object_type",
    oqp."mno.name",
    oqp."mno.additional_description",
    oqp."mno.last_change_time",
    oqp."mno.begin_lifespan_version",
    oqp."mno.end_lifespan_version",
    oqp."mno.primary_geo_column",
    oqp."mno.reason_change",
    oqp."mno.primary_geodetic_reference_id",
    oqp."pot.pot_id",
    oqp."pot.qem_id",
    oqp."pot.start_date",
    oqp."pot.end_date",
    oqp."pot.noof_samples",
    oqp."pot.samples_bepalingsgrens_id",
    oqp."pot.last_change_time",
    oqp."bpg.bpg_id",
    oqp."bpg.code",
    oqp."bpg.omschrijving",
    oqp."bpg.d_begin",
    oqp."bpg.d_eind",
    oqp."bpg.last_changed_date",
    oqp."bpg.d_status"
   FROM ((vw_opt opt
     LEFT JOIN vw_grp grp ON (((opt."opt.grp.cat" = grp."grp.cat") AND (opt."opt.grp_id" = grp."grp.grp_id"))))
     LEFT JOIN vw_osc_qem_pot oqp ON ((opt."opt.opt_id" = oqp."qem.opt_id")));


--
-- Name: vw_monprogs_grp_elt; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_monprogs_grp_elt AS
 SELECT opt."opt.opt_id",
    opt."opt.observed_property_type_class",
    opt."opt.result_type",
    opt."opt.hoedanigheid_id",
    opt."opt.quantity_id",
    opt."opt.grp.cat",
    opt."opt.grp_id",
    opt."opt.indicator_id",
    opt."opt.classification_group_id",
    opt."opt.remarks",
    opt."opt.d_begin",
    opt."opt.d_eind",
    opt."opt.last_changed_date",
    opt."opt.d_status",
    opt."hdh.hdh_id",
    opt."hdh.code",
    opt."hdh.omschrijving",
    opt."hdh.d_begin",
    opt."hdh.d_eind",
    opt."hdh.last_changed_date",
    opt."hdh.d_status",
    opt."hdh.id",
    opt."grh.grh_id",
    opt."grh.code",
    opt."grh.omschrijving",
    opt."grh.d_begin",
    opt."grh.d_eind",
    opt."grh.last_changed_date",
    opt."grh.d_status",
    opt."grh.id",
    opt."typ.typ_id",
    opt."typ.code",
    opt."typ.omschrijving",
    opt."typ.d_begin",
    opt."typ.d_eind",
    opt."typ.last_changed_date",
    opt."typ.d_status",
    opt."typ.id",
    opt."clsg.clsg_id",
    opt."clsg.code",
    opt."clsg.description",
    opt."clsg.d_begin",
    opt."clsg.d_eind",
    opt."clsg.last_changed_date",
    opt."clsg.d_status",
    gre."grp.cat",
    gre."grp.grp_id",
    gre."grp.name",
    gre."grp.group_type",
    gre."grp.d_begin",
    gre."grp.d_eind",
    gre."grp.last_changed_date",
    gre."grp.d_status",
    gre."elt.cat",
    gre."elt.elt_id",
    gre."elt.elt_code",
    gre."elt.cas_nr",
    gre."elt.name",
    gre."elt.group_type",
    gre."elt.d_begin",
    gre."elt.d_eind",
    gre."elt.last_changed_date",
    gre."elt.d_status",
    gre."elt.id",
    oqp."osc.osc_id",
    oqp."osc.observing_time_begin",
    oqp."osc.observing_time_end",
    oqp."osc.online_resource",
    oqp."osc.process_type_id",
    oqp."osc.last_change_time",
    oqp."osc.name",
    oqp."osc.is_sample",
    oqp."osc.wkrv_id",
    oqp."ocrp.ocrp_id",
    oqp."ore.ore_id",
    oqp."ore.naam_verkort",
    oqp."qem.qem_id",
    oqp."qem.osc_id",
    oqp."qem.opt_id",
    oqp."qem.parameter_use_id",
    oqp."qem.cycle_value",
    oqp."qem.cycle_uom_id",
    oqp."qem.cycle_description",
    oqp."qem.frequency_value",
    oqp."qem.frequency_uom_id",
    oqp."qem.reason_deviation_program",
    oqp."qem.last_change_time",
    oqp."qem.sampling_method_id",
    oqp."qem.sampling_device_id",
    oqp."qem.deviation_program_jn",
    oqp."qem.opt_uom_id",
    oqp."qem.wbm_id",
    oqp."qem.remark",
    oqp."qem.opt.opt_id",
    oqp."qem.opt.observed_property_type_class",
    oqp."qem.opt.result_type",
    oqp."ehd.eenh_id",
    oqp."ehd.code",
    oqp."ehd.omschrijving",
    oqp."ehd.d_begin",
    oqp."ehd.d_eind",
    oqp."ehd.last_changed_date",
    oqp."ehd.d_status",
    oqp."ehd.id",
    oqp."var.var_id",
    oqp."var.code",
    oqp."var.description",
    oqp."var.d_begin",
    oqp."var.d_eind",
    oqp."var.last_changed_date",
    oqp."var.d_status",
    oqp."var.id",
    oqp."var.codespace",
    oqp."var.groep",
    oqp."wbm.wbm_id",
    oqp."wbm.code",
    oqp."wbm.omschrijving",
    oqp."wbm.d_begin",
    oqp."wbm.d_eind",
    oqp."wbm.last_changed_date",
    oqp."wbm.d_status",
    oqp."wbm.id",
    oqp."bmm.bmm_id",
    oqp."bmm.code ",
    oqp."bmm.description",
    oqp."bmm.groep",
    oqp."bmm.titel",
    oqp."bmm.d_begin",
    oqp."bmm.d_eind ",
    oqp."bmm.last_changed_date",
    oqp."bmm.d_status ",
    oqp."bmm.id ",
    oqp."moqe.moqe_id",
    oqp."moqe.mno_id",
    oqp."moqe.qem_id",
    oqp."mno.mno_id",
    oqp."mno.inspire_id_local_id",
    oqp."mno.inspire_id_namespace_id",
    oqp."mno.wkrv_id",
    oqp."mno.monitoring_object_type",
    oqp."mno.name",
    oqp."mno.additional_description",
    oqp."mno.last_change_time",
    oqp."mno.begin_lifespan_version",
    oqp."mno.end_lifespan_version",
    oqp."mno.primary_geo_column",
    oqp."mno.reason_change",
    oqp."mno.primary_geodetic_reference_id",
    oqp."pot.pot_id",
    oqp."pot.qem_id",
    oqp."pot.start_date",
    oqp."pot.end_date",
    oqp."pot.noof_samples",
    oqp."pot.samples_bepalingsgrens_id",
    oqp."pot.last_change_time",
    oqp."bpg.bpg_id",
    oqp."bpg.code",
    oqp."bpg.omschrijving",
    oqp."bpg.d_begin",
    oqp."bpg.d_eind",
    oqp."bpg.last_changed_date",
    oqp."bpg.d_status"
   FROM ((vw_opt opt
     LEFT JOIN vw_grp_elt gre ON (((opt."opt.grp.cat" = gre."grp.cat") AND (opt."opt.grp_id" = gre."grp.grp_id"))))
     LEFT JOIN vw_osc_qem_pot oqp ON ((opt."opt.opt_id" = oqp."qem.opt_id")));


--
-- Name: vw_obs_bob; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_obs_bob AS
 SELECT obs.obs_id,
    obs.phenomenon_time_begin,
    obs.phenomenon_time_end,
    obs.result_time,
    obs.valid_time_begin,
    obs.valid_time_end,
    obs.observed_property_type,
    obs.quality_indicator_id,
    obs.numeric_value_uom_id,
    bob.levensstadium_id,
    ehd.code
   FROM ((public.observation obs
     LEFT JOIN public.bio_observation bob ON ((obs.obs_id = bob.obs_id)))
     LEFT JOIN public.eenheid_type ehd ON ((obs.numeric_value_uom_id = ehd.eenh_id)));


--
-- Name: vw_opt_table; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_opt_table AS
 SELECT opt.opt_id,
    opt.observed_property_type_class,
    opt.result_type,
    opt.hoedanigheid_id,
    hdh.code AS "hoedanigheid.code",
    opt.object_group_id,
    ojg.name AS "object.code",
    opt.quantity_id,
    grh.code AS "grootheid.code",
    opt.taxa_group_id,
    txg.name AS "Taxagroup.omschrijving",
    opt.substance_group_id,
    chs.code AS "chemischestof.code",
    opt.indicator_id,
    typ.code AS "typering.code",
    opt.d_status,
    opt.last_changed_date AS last_change_date
   FROM ((((((((public.observed_property_type opt
     LEFT JOIN public.substance_group ssg ON ((ssg.ssg_id = opt.substance_group_id)))
     LEFT JOIN public.substance_group_element ssge ON ((ssge.ssge_id = ssg.ssg_id)))
     LEFT JOIN public.chemische_stof_type chs ON ((chs.chs_id = ssge.chs_id)))
     LEFT JOIN public.taxa_group txg ON ((txg.txg_id = opt.taxa_group_id)))
     LEFT JOIN public.hoedanigheid_type hdh ON ((hdh.hdh_id = opt.hoedanigheid_id)))
     LEFT JOIN public.object_group ojg ON ((ojg.ojg_id = opt.object_group_id)))
     LEFT JOIN public.grootheid_type grh ON ((grh.grh_id = opt.quantity_id)))
     LEFT JOIN public.typering_type typ ON ((typ.typ_id = opt.indicator_id)))
  ORDER BY txg.name, chs.code, opt.opt_id DESC;


--
-- Name: VIEW vw_opt_table; Type: COMMENT; Schema: beheer; Owner: -
--

COMMENT ON VIEW vw_opt_table IS 'Deze view toont de observed_property_type tabel met de codes voorafgaand aan de ''vertaling''';


--
-- Name: vw_stat_user_tables_hist_last; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_stat_user_tables_hist_last AS
 SELECT stat_user_tables_hist.suth_id,
    stat_user_tables_hist."timestamp",
    stat_user_tables_hist.relid,
    stat_user_tables_hist.schemaname,
    stat_user_tables_hist.relname,
    stat_user_tables_hist.seq_scan,
    stat_user_tables_hist.seq_tup_read,
    stat_user_tables_hist.idx_scan,
    stat_user_tables_hist.idx_tup_fetch,
    stat_user_tables_hist.n_tup_ins,
    stat_user_tables_hist.n_tup_upd,
    stat_user_tables_hist.n_tup_del,
    stat_user_tables_hist.n_tup_hot_upd,
    stat_user_tables_hist.n_live_tup,
    stat_user_tables_hist.n_dead_tup,
    stat_user_tables_hist.n_mod_since_analyze,
    stat_user_tables_hist.last_vacuum,
    stat_user_tables_hist.last_autovacuum,
    stat_user_tables_hist.last_analyze,
    stat_user_tables_hist.last_autoanalyze,
    stat_user_tables_hist.vacuum_count,
    stat_user_tables_hist.autovacuum_count,
    stat_user_tables_hist.analyze_count,
    stat_user_tables_hist.autoanalyze_count,
    stat_user_tables_hist.n_live_tup_real
   FROM stat_user_tables_hist
  WHERE (stat_user_tables_hist."timestamp" IN ( SELECT max(stat_user_tables_hist_1."timestamp") AS max
           FROM stat_user_tables_hist stat_user_tables_hist_1));


--
-- Name: vw_stat_user_tables_hist_prev; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_stat_user_tables_hist_prev AS
 SELECT stat_user_tables_hist.suth_id,
    stat_user_tables_hist."timestamp",
    stat_user_tables_hist.relid,
    stat_user_tables_hist.schemaname,
    stat_user_tables_hist.relname,
    stat_user_tables_hist.seq_scan,
    stat_user_tables_hist.seq_tup_read,
    stat_user_tables_hist.idx_scan,
    stat_user_tables_hist.idx_tup_fetch,
    stat_user_tables_hist.n_tup_ins,
    stat_user_tables_hist.n_tup_upd,
    stat_user_tables_hist.n_tup_del,
    stat_user_tables_hist.n_tup_hot_upd,
    stat_user_tables_hist.n_live_tup,
    stat_user_tables_hist.n_dead_tup,
    stat_user_tables_hist.n_mod_since_analyze,
    stat_user_tables_hist.last_vacuum,
    stat_user_tables_hist.last_autovacuum,
    stat_user_tables_hist.last_analyze,
    stat_user_tables_hist.last_autoanalyze,
    stat_user_tables_hist.vacuum_count,
    stat_user_tables_hist.autovacuum_count,
    stat_user_tables_hist.analyze_count,
    stat_user_tables_hist.autoanalyze_count,
    stat_user_tables_hist.n_live_tup_real
   FROM stat_user_tables_hist
  WHERE (stat_user_tables_hist."timestamp" IN ( SELECT max(max_but_one."timestamp") AS max
           FROM ( SELECT stat_user_tables_hist_1."timestamp"
                   FROM stat_user_tables_hist stat_user_tables_hist_1
                  WHERE (NOT (stat_user_tables_hist_1."timestamp" IN ( SELECT max(stat_user_tables_hist_2."timestamp") AS max
                           FROM stat_user_tables_hist stat_user_tables_hist_2)))) max_but_one));


--
-- Name: vw_stat_user_tables_hist_last_prev; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_stat_user_tables_hist_last_prev AS
 SELECT suth_last.suth_id AS last_suth_id,
    suth_last."timestamp" AS last_timestamp,
    suth_last.relid AS last_relid,
    suth_last.schemaname AS last_schemaname,
    suth_last.relname AS last_relname,
    suth_last.seq_scan AS last_seq_scan,
    suth_last.seq_tup_read AS last_seq_tup_read,
    suth_last.idx_scan AS last_idx_scan,
    suth_last.idx_tup_fetch AS last_idx_tup_fetch,
    suth_last.n_tup_ins AS last_n_tup_ins,
    suth_last.n_tup_upd AS last_n_tup_upd,
    suth_last.n_tup_del AS last_n_tup_del,
    suth_last.n_tup_hot_upd AS last_n_tup_hot_upd,
    suth_last.n_live_tup AS last_n_live_tup,
    suth_last.n_dead_tup AS last_n_dead_tup,
    suth_last.n_mod_since_analyze AS last_n_mod_since_analyze,
    suth_last.last_vacuum AS last_last_vacuum,
    suth_last.last_autovacuum AS last_last_autovacuum,
    suth_last.last_analyze AS last_last_analyze,
    suth_last.last_autoanalyze AS last_last_autoanalyze,
    suth_last.vacuum_count AS last_vacuum_count,
    suth_last.autovacuum_count AS last_autovacuum_count,
    suth_last.analyze_count AS last_analyze_count,
    suth_last.autoanalyze_count AS last_autoanalyze_count,
    suth_last.n_live_tup_real AS last_n_live_tup_real,
    suth_prev.suth_id AS prev_suth_id,
    suth_prev."timestamp" AS prev_timestamp,
    suth_prev.relid AS prev_relid,
    suth_prev.schemaname AS prev_schemaname,
    suth_prev.relname AS prev_relname,
    suth_prev.seq_scan AS prev_seq_scan,
    suth_prev.seq_tup_read AS prev_seq_tup_read,
    suth_prev.idx_scan AS prev_idx_scan,
    suth_prev.idx_tup_fetch AS prev_idx_tup_fetch,
    suth_prev.n_tup_ins AS prev_n_tup_ins,
    suth_prev.n_tup_upd AS prev_n_tup_upd,
    suth_prev.n_tup_del AS prev_n_tup_del,
    suth_prev.n_tup_hot_upd AS prev_n_tup_hot_upd,
    suth_prev.n_live_tup AS prev_n_live_tup,
    suth_prev.n_dead_tup AS prev_n_dead_tup,
    suth_prev.n_mod_since_analyze AS prev_n_mod_since_analyze,
    suth_prev.last_vacuum AS prev_last_vacuum,
    suth_prev.last_autovacuum AS prev_last_autovacuum,
    suth_prev.last_analyze AS prev_last_analyze,
    suth_prev.last_autoanalyze AS prev_last_autoanalyze,
    suth_prev.vacuum_count AS prev_vacuum_count,
    suth_prev.autovacuum_count AS prev_autovacuum_count,
    suth_prev.analyze_count AS prev_analyze_count,
    suth_prev.autoanalyze_count AS prev_autoanalyze_count,
    suth_prev.n_live_tup_real AS prev_n_live_tup_real
   FROM (vw_stat_user_tables_hist_last suth_last
     FULL JOIN vw_stat_user_tables_hist_prev suth_prev ON ((suth_prev.relid = suth_last.relid)));


--
-- Name: vw_stat_user_tables_hist_smpl_rpt; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_stat_user_tables_hist_smpl_rpt AS
 SELECT vwsuthlp.last_suth_id,
    vwsuthlp.last_timestamp,
    vwsuthlp.last_relid,
    vwsuthlp.last_schemaname,
    vwsuthlp.last_relname,
    vwsuthlp.prev_n_live_tup_real,
    vwsuthlp.last_n_live_tup_real,
    (COALESCE(vwsuthlp.last_n_live_tup_real, (0)::bigint) - COALESCE(vwsuthlp.prev_n_live_tup_real, (0)::bigint)) AS n_live_tup_real_chg,
        CASE
            WHEN ((COALESCE(vwsuthlp.prev_n_live_tup_real, (0)::bigint) = 0) AND (vwsuthlp.last_n_live_tup_real = 0)) THEN 0
            WHEN ((COALESCE(vwsuthlp.prev_n_live_tup_real, (0)::bigint) = 0) AND (vwsuthlp.last_n_live_tup_real > 0)) THEN 100
            WHEN (vwsuthlp.prev_n_live_tup_real > 0) THEN (round(((100.0 * ((COALESCE(vwsuthlp.last_n_live_tup_real, (0)::bigint))::numeric - (vwsuthlp.prev_n_live_tup_real)::numeric)) / (vwsuthlp.prev_n_live_tup_real)::numeric)))::integer
            ELSE 100
        END AS n_live_tup_real_chg_pct,
    vwsuthlp.prev_schemaname,
    vwsuthlp.prev_relname,
    vwsuthlp.prev_suth_id,
    vwsuthlp.prev_timestamp,
    vwsuthlp.prev_relid
   FROM vw_stat_user_tables_hist_last_prev vwsuthlp
  WHERE ((vwsuthlp.prev_n_live_tup_real <> vwsuthlp.last_n_live_tup_real) OR (vwsuthlp.prev_n_live_tup_real IS NULL) OR (vwsuthlp.last_n_live_tup_real IS NULL))
  ORDER BY vwsuthlp.last_schemaname, vwsuthlp.last_relname;


--
-- Name: vw_waterbeheerder_stroomgebieddistrict; Type: VIEW; Schema: beheer; Owner: -
--

CREATE VIEW vw_waterbeheerder_stroomgebieddistrict 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,
    competentauthority_namespacetype.ore_id,
    vw_competent_authority."Omschrijving" AS waterbeheerder_omschrijving,
        CASE
            WHEN ((vw_sg_en_dsg_districten."Identificatie")::text = 'NLRNNO'::text) THEN 'Rijn-Noord'::text
            ELSE ''::text
        END AS "Stroomgebieddistrict Rijn-Noord",
        CASE
            WHEN ((vw_sg_en_dsg_districten."Identificatie")::text = 'NLRNOO'::text) THEN 'Rijn-Oost'::text
            ELSE ''::text
        END AS "Stroomgebieddistrict Rijn-Oost",
        CASE
            WHEN ((vw_sg_en_dsg_districten."Identificatie")::text = 'NLRNWE'::text) THEN 'Rijn-West'::text
            ELSE ''::text
        END AS "Stroomgebieddistrict Rijn-West",
        CASE
            WHEN ((vw_sg_en_dsg_districten."Identificatie")::text = 'NLMS'::text) THEN 'Maas'::text
            ELSE ''::text
        END AS "Stroomgebied Maas",
        CASE
            WHEN ((vw_sg_en_dsg_districten."Identificatie")::text = 'NLEM'::text) THEN 'Eems'::text
            ELSE ''::text
        END AS "Stroomgebied Eems",
        CASE
            WHEN ((vw_sg_en_dsg_districten."Identificatie")::text = 'NLSC'::text) THEN 'Schelde'::text
            ELSE ''::text
        END AS "Stroomgebied Schelde",
    hydro_object.wkrv_id AS versie
   FROM (((((public.hydro_object
     LEFT JOIN public.management_regulation_or_restriction_zone ON ((management_regulation_or_restriction_zone.mrz_id = hydro_object.hdo_id)))
     LEFT JOIN public.competentauthority_namespacetype ON ((competentauthority_namespacetype.ns_id = hydro_object.inspire_id_namespace)))
     LEFT JOIN public.vw_competent_authority ON ((vw_competent_authority."DB_ID" = competentauthority_namespacetype.ore_id)))
     JOIN public.water_body ON ((water_body.wbd_id = hydro_object.hdo_id)))
     LEFT JOIN public.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: bemonsteringsmethode_bmm_id_seq; Type: SEQUENCE; Schema: domains; Owner: -
--

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


--
-- Name: bemonsteringsmethode_bmm_id_seq; Type: SEQUENCE OWNED BY; Schema: domains; Owner: -
--

ALTER SEQUENCE bemonsteringsmethode_bmm_id_seq OWNED BY bemonsteringsmethode.bmm_id;


--
-- Name: certificeringscode; Type: TABLE; Schema: domains; Owner: -
--

CREATE TABLE certificeringscode (
    ctf_id integer NOT NULL,
    code character varying(50) NOT NULL,
    description character varying(255) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: certificeringscode_ctf_id_seq; Type: SEQUENCE; Schema: domains; Owner: -
--

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


--
-- Name: certificeringscode_ctf_id_seq; Type: SEQUENCE OWNED BY; Schema: domains; Owner: -
--

ALTER SEQUENCE certificeringscode_ctf_id_seq OWNED BY certificeringscode.ctf_id;


--
-- Name: confidence_level_type; Type: TABLE; Schema: domains; Owner: -
--

CREATE TABLE confidence_level_type (
    clt_id integer NOT NULL,
    code character varying(1) NOT NULL,
    description character varying(50),
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    last_change_time timestamp without time zone
);


--
-- Name: confidence_level_type_clt_id_seq; Type: SEQUENCE; Schema: domains; Owner: -
--

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


--
-- Name: confidence_level_type_clt_id_seq; Type: SEQUENCE OWNED BY; Schema: domains; Owner: -
--

ALTER SEQUENCE confidence_level_type_clt_id_seq OWNED BY confidence_level_type.clt_id;


--
-- Name: drivers; Type: TABLE; Schema: domains; Owner: -
--

CREATE TABLE drivers (
    driver_id integer NOT NULL,
    naam character varying(255),
    order_id integer,
    last_changed_date timestamp without time zone,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying NOT NULL
);


--
-- Name: drivers_driver_id_seq; Type: SEQUENCE; Schema: domains; Owner: -
--

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


--
-- Name: drivers_driver_id_seq; Type: SEQUENCE OWNED BY; Schema: domains; Owner: -
--

ALTER SEQUENCE drivers_driver_id_seq OWNED BY drivers.driver_id;


--
-- Name: impacts; Type: TABLE; Schema: domains; Owner: -
--

CREATE TABLE impacts (
    impact_id integer NOT NULL,
    naam character varying(255),
    order_id integer,
    last_changed_date timestamp without time zone,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying NOT NULL,
    usedforgw boolean,
    usedforsw boolean
);


--
-- Name: impacts_impact_id_seq; Type: SEQUENCE; Schema: domains; Owner: -
--

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


--
-- Name: impacts_impact_id_seq; Type: SEQUENCE OWNED BY; Schema: domains; Owner: -
--

ALTER SEQUENCE impacts_impact_id_seq OWNED BY impacts.impact_id;


--
-- Name: indicator_descriptor; Type: TABLE; Schema: domains; Owner: -
--

CREATE TABLE indicator_descriptor (
    idd_id integer NOT NULL,
    opt_id integer NOT NULL,
    part_of_idd_id integer,
    additional_description character varying(1000),
    eu_identifier character varying(256),
    begin_lifespan_version timestamp without time zone NOT NULL,
    end_lifespan_version timestamp without time zone,
    last_change_time timestamp without time zone NOT NULL,
    name character varying(50) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: indicator_descriptor_idd_id_seq; Type: SEQUENCE; Schema: domains; Owner: -
--

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


--
-- Name: indicator_descriptor_idd_id_seq; Type: SEQUENCE OWNED BY; Schema: domains; Owner: -
--

ALTER SEQUENCE indicator_descriptor_idd_id_seq OWNED BY indicator_descriptor.idd_id;


--
-- Name: locatietype_waardebepaling; Type: TABLE; Schema: domains; Owner: -
--

CREATE TABLE locatietype_waardebepaling (
    ltwb_id integer NOT NULL,
    code character varying(50) NOT NULL,
    description character varying(255) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: locatietype_waardebepaling_ltwb_id_seq; Type: SEQUENCE; Schema: domains; Owner: -
--

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


--
-- Name: locatietype_waardebepaling_ltwb_id_seq; Type: SEQUENCE OWNED BY; Schema: domains; Owner: -
--

ALTER SEQUENCE locatietype_waardebepaling_ltwb_id_seq OWNED BY locatietype_waardebepaling.ltwb_id;


--
-- Name: meetapparaat_mar_id_seq; Type: SEQUENCE; Schema: domains; Owner: -
--

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


--
-- Name: meetapparaat_mar_id_seq; Type: SEQUENCE OWNED BY; Schema: domains; Owner: -
--

ALTER SEQUENCE meetapparaat_mar_id_seq OWNED BY meetapparaat.mar_id;


--
-- Name: monsterbewerkingsmethode_mbm_id_seq; Type: SEQUENCE; Schema: domains; Owner: -
--

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


--
-- Name: monsterbewerkingsmethode_mbm_id_seq; Type: SEQUENCE OWNED BY; Schema: domains; Owner: -
--

ALTER SEQUENCE monsterbewerkingsmethode_mbm_id_seq OWNED BY monsterbewerkingsmethode.mbm_id;


--
-- Name: monstervoorbehandeling; Type: TABLE; Schema: domains; Owner: -
--

CREATE TABLE monstervoorbehandeling (
    mvb_id integer NOT NULL,
    code character varying(50) NOT NULL,
    description character varying(255) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: monstervoorbehandeling_mvb_id_seq; Type: SEQUENCE; Schema: domains; Owner: -
--

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


--
-- Name: monstervoorbehandeling_mvb_id_seq; Type: SEQUENCE OWNED BY; Schema: domains; Owner: -
--

ALTER SEQUENCE monstervoorbehandeling_mvb_id_seq OWNED BY monstervoorbehandeling.mvb_id;


--
-- Name: orgaan_org_id_seq; Type: SEQUENCE; Schema: domains; Owner: -
--

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


--
-- Name: orgaan_org_id_seq; Type: SEQUENCE OWNED BY; Schema: domains; Owner: -
--

ALTER SEQUENCE orgaan_org_id_seq OWNED BY orgaan.org_id;


--
-- Name: status_change_motivation_type; Type: TABLE; Schema: domains; Owner: -
--

CREATE TABLE status_change_motivation_type (
    scm_id integer NOT NULL,
    description character varying(255) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying,
    last_change_time timestamp without time zone
);


--
-- Name: status_change_motivation_type_scm_id_seq; Type: SEQUENCE; Schema: domains; Owner: -
--

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


--
-- Name: status_change_motivation_type_scm_id_seq; Type: SEQUENCE OWNED BY; Schema: domains; Owner: -
--

ALTER SEQUENCE status_change_motivation_type_scm_id_seq OWNED BY status_change_motivation_type.scm_id;


--
-- Name: veldapparaat_var_id_seq; Type: SEQUENCE; Schema: domains; Owner: -
--

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


--
-- Name: veldapparaat_var_id_seq; Type: SEQUENCE OWNED BY; Schema: domains; Owner: -
--

ALTER SEQUENCE veldapparaat_var_id_seq OWNED BY veldapparaat.var_id;


--
-- Name: waardebepalingstechniek; Type: TABLE; Schema: domains; Owner: -
--

CREATE TABLE waardebepalingstechniek (
    wbpt_id integer NOT NULL,
    code character varying(50) NOT NULL,
    description character varying(255) NOT NULL,
    d_begin timestamp without time zone,
    d_eind timestamp without time zone,
    d_status character varying(50) DEFAULT 'Gepubliceerd'::character varying
);


--
-- Name: waardebepalingstechniek_wbpt_id_seq; Type: SEQUENCE; Schema: domains; Owner: -
--

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


--
-- Name: waardebepalingstechniek_wbpt_id_seq; Type: SEQUENCE OWNED BY; Schema: domains; Owner: -
--

ALTER SEQUENCE waardebepalingstechniek_wbpt_id_seq OWNED BY waardebepalingstechniek.wbpt_id;



--
-- Name: monprog_2015; Type: TABLE; Schema: eu_state_of_environment; Owner: -
--

CREATE TABLE monprog_2015 (
    mpnident character varying(100) NOT NULL,
    ligt_in_owm character varying(100),
    rd_x double precision,
    rd_y double precision,
    parameter character varying(24) NOT NULL,
    monfreq integer,
    moncyclus integer,
    monsoort character varying(10) NOT NULL,
    repr_owm character varying(100) NOT NULL,
    mpntype character varying(50),
    diepte character varying(50),
    montype character varying(50) DEFAULT 'ow'::character varying,
    naam character varying(100),
    sgb character varying(10),
    purpose character varying(25),
    sinds character varying(10),
    tot character varying(10),
    fract character varying(10)
);



--
-- Name: rapport_tekst; Type: TABLE; Schema: factsheets; Owner: -
--

CREATE TABLE rapport_tekst (
    rapport_tekst_id integer NOT NULL,
    wb_type_id integer,
    veld_id integer,
    uitvoering_tpe_id integer,
    tekst character varying(1000),
    begindatum timestamp without time zone NOT NULL,
    einddatum timestamp without time zone NOT NULL
);


--
-- Name: uitvoering_type; Type: TABLE; Schema: factsheets; Owner: -
--

CREATE TABLE uitvoering_type (
    uitvoering_type_id integer NOT NULL,
    omschrijving character varying(50)
);


--
-- Name: veld; Type: TABLE; Schema: factsheets; Owner: -
--

CREATE TABLE veld (
    veld_id integer NOT NULL,
    omschrijving character varying(50)
);



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

CREATE TABLE wb_status_change_motivation (
    wbsc_id integer NOT NULL,
    wbd_id integer NOT NULL,
    scm_id integer NOT NULL,
    opt_id integer NOT NULL,
    year_initial integer DEFAULT 2009 NOT NULL,
    year_status integer,
    last_change_time timestamp without time zone
);



--
-- Name: vw_ch3_toestand_achteruitgang; Type: VIEW; Schema: factsheets; Owner: -
--

CREATE VIEW vw_ch3_toestand_achteruitgang AS
 SELECT wscm.wbd_id,
    wscm.opt_id,
    wscm.year_status
   FROM public.wb_status_change_motivation wscm
  WHERE (wscm.scm_id = 4);



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

CREATE TABLE classification_type (
    cls_id integer NOT NULL,
    code character varying(50) NOT NULL,
    description character varying(90) NOT NULL,
    clsg_id integer 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: gw_chemical_status; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE gw_chemical_status (
    gwcs_id integer NOT NULL,
    gys_id integer NOT NULL,
    clst_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL,
    eumg_id integer NOT NULL,
    wbpm_id integer NOT NULL,
    opt_id integer NOT NULL,
    observation_start_date date,
    observation_end_date date,
    clt_id integer
);


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

CREATE TABLE gw_quantity_status (
    gwqs_id integer NOT NULL,
    gys_id integer NOT NULL,
    gwst_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL,
    wbpm_id integer NOT NULL,
    eumg_id integer NOT NULL,
    opt_id integer NOT NULL,
    clt_id integer DEFAULT 2
);


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

CREATE TABLE gwb_yearly_status (
    gys_id integer NOT NULL,
    gwb_id integer NOT NULL,
    year_of_status integer NOT NULL,
    comment_on_chemical_status character varying(500),
    comment_on_quantity_status character varying(500),
    motivation_4no_chemical_status text,
    motivation_4no_quantity_status text,
    last_change_time timestamp without time zone NOT NULL,
    additional_purification boolean DEFAULT false NOT NULL,
    motivation_4no_chemical_trends text,
    exceeds_drinkingwater boolean DEFAULT false NOT NULL
);


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

CREATE VIEW vw_waargenomen_eigenschap_db_id 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",
    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 typering_type.code, grootheid_type.code, chemische_stof_type.code, object_type.code, taxon_type.code, hoedanigheid_type.code;


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

CREATE TABLE wfd_qe_gwtype (
    opt_id integer NOT NULL,
    part_of_opt_id_eu integer,
    part_of_opt_id_nl integer,
    main_group boolean NOT NULL,
    is_applicable boolean NOT NULL,
    is_excemption boolean NOT NULL,
    name character varying(255) NOT NULL,
    qegw_id integer NOT NULL,
    clsg_id integer NOT NULL,
    is_trendsubstance boolean DEFAULT true NOT NULL,
    is_backgroundlevelsubstance boolean DEFAULT false NOT NULL,
    sortorder integer,
    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: vw_oordeel_totaal_gwl_for_crystal_reports; Type: VIEW; Schema: factsheets; Owner: -
--

CREATE VIEW vw_oordeel_totaal_gwl_for_crystal_reports AS
 WITH krw_kwal AS (
         SELECT wfd_qe_gwtype.name,
                CASE
                    WHEN (wfd_qe_gwtype.part_of_opt_id_nl = ANY (ARRAY[4495, 4494])) THEN 'LANDELIJK'::text
                    ELSE
                    CASE
                        WHEN (wfd_qe_gwtype.part_of_opt_id_eu = ANY (ARRAY[4494, 4495])) THEN 'REGIONAAL'::text
                        ELSE
                        CASE
                            WHEN (wfd_qe_gwtype.opt_id = ANY (ARRAY[4494, 4495, 4487])) THEN 'TOTAAL'::text
                            ELSE 'NVT'::text
                        END
                    END
                END AS "CATEGORIE",
            wfd_qe_gwtype.opt_id,
            wfd_qe_gwtype.is_applicable
           FROM public.wfd_qe_gwtype
        )
 SELECT gwb_yearly_status.gys_id AS "DB_ID",
    hdo_gwb.hdo_id AS "Waterlichaam_DB_ID",
    hdo_gwb.inspire_id_local_id AS "Waterlichaam_identificatie",
    hdo_gwb.wkrv_id AS "Waterlichaam_Versie",
    gwb_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.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",
    (gwb_yearly_status.comment_on_chemical_status)::character varying(5000) AS "Toelichting_ChemischeStatus",
    (gwb_yearly_status.comment_on_quantity_status)::character varying(5000) AS "Toelichting_EcologischeStatus",
    (gwb_yearly_status.motivation_4no_chemical_status)::character varying(5000) AS "Motivatie_Geen_ChemischeStatus",
    (gwb_yearly_status.motivation_4no_chemical_trends)::character varying(5000) AS "Motivatie_Geen_ChemischeTrend",
    (gwb_yearly_status.motivation_4no_quantity_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",
        CASE
            WHEN (wscm.scm_id IS NOT NULL) THEN 'A'::character varying(1)
            ELSE NULL::character varying
        END AS "Motivatie_Achteruitgang"
   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_db_id ON ((vw_waargenomen_eigenschap_db_id."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)))
     LEFT JOIN public.wb_status_change_motivation wscm ON (((wscm.wbd_id = hdo_gwb.hdo_id) AND (wscm.opt_id = vw_waargenomen_eigenschap_db_id."DB_ID") AND (wscm.year_status = gwb_yearly_status.year_of_status) AND (wscm.scm_id = 1))))
     FULL JOIN krw_kwal ON ((vw_waargenomen_eigenschap_db_id."DB_ID" = krw_kwal.opt_id)))
UNION ALL
 SELECT gwb_yearly_status.gys_id AS "DB_ID",
    hdo_gwb.hdo_id AS "Waterlichaam_DB_ID",
    hdo_gwb.inspire_id_local_id AS "Waterlichaam_identificatie",
    hdo_gwb.wkrv_id AS "Waterlichaam_Versie",
    gwb_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.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",
    (gwb_yearly_status.comment_on_chemical_status)::character varying(5000) AS "Toelichting_ChemischeStatus",
    (gwb_yearly_status.comment_on_quantity_status)::character varying(5000) AS "Toelichting_EcologischeStatus",
    (gwb_yearly_status.motivation_4no_chemical_status)::character varying(5000) AS "Motivatie_Geen_ChemischeStatus",
    (gwb_yearly_status.motivation_4no_chemical_trends)::character varying(5000) AS "Motivatie_Geen_ChemischeTrend",
    (gwb_yearly_status.motivation_4no_quantity_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",
        CASE
            WHEN (wscm.scm_id IS NOT NULL) THEN 'A'::character varying(1)
            ELSE NULL::character varying
        END AS "Motivatie_Achteruitgang"
   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_db_id ON ((vw_waargenomen_eigenschap_db_id."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)))
     LEFT JOIN public.wb_status_change_motivation wscm ON (((wscm.wbd_id = hdo_gwb.hdo_id) AND (wscm.opt_id = vw_waargenomen_eigenschap_db_id."DB_ID") AND (wscm.year_status = gwb_yearly_status.year_of_status) AND (wscm.scm_id = 1))))
     FULL JOIN krw_kwal ON ((vw_waargenomen_eigenschap_db_id."DB_ID" = krw_kwal.opt_id)))
  ORDER BY 1, 6, 12 DESC, 10 DESC;


--
-- Name: vw_oordeel_totaal_gwl_for_crystal_reports01; Type: VIEW; Schema: factsheets; Owner: -
--

CREATE VIEW vw_oordeel_totaal_gwl_for_crystal_reports01 AS
 WITH krw_kwal AS (
         SELECT wfd_qe_gwtype.name,
                CASE
                    WHEN (wfd_qe_gwtype.part_of_opt_id_nl = ANY (ARRAY[4495, 4494])) THEN 'LANDELIJK'::text
                    ELSE
                    CASE
                        WHEN (wfd_qe_gwtype.part_of_opt_id_eu = ANY (ARRAY[4494, 4495])) THEN 'REGIONAAL'::text
                        ELSE
                        CASE
                            WHEN (wfd_qe_gwtype.opt_id = ANY (ARRAY[4494, 4495, 4487])) THEN 'TOTAAL'::text
                            ELSE 'NVT'::text
                        END
                    END
                END AS "CATEGORIE",
            wfd_qe_gwtype.opt_id,
            wfd_qe_gwtype.is_applicable
           FROM public.wfd_qe_gwtype
        )
 SELECT gwb_yearly_status.gys_id AS "DB_ID",
    hdo_gwb.hdo_id AS "Waterlichaam_DB_ID",
    hdo_gwb.inspire_id_local_id AS "Waterlichaam_identificatie",
    hdo_gwb.wkrv_id AS "Waterlichaam_Versie",
    gwb_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.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",
    (gwb_yearly_status.comment_on_chemical_status)::character varying(5000) AS "Toelichting_ChemischeStatus",
    (gwb_yearly_status.comment_on_quantity_status)::character varying(5000) AS "Toelichting_EcologischeStatus",
    (gwb_yearly_status.motivation_4no_chemical_status)::character varying(5000) AS "Motivatie_Geen_ChemischeStatus",
    (gwb_yearly_status.motivation_4no_chemical_trends)::character varying(5000) AS "Motivatie_Geen_ChemischeTrend",
    (gwb_yearly_status.motivation_4no_quantity_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",
        CASE
            WHEN (wscm.scm_id IS NOT NULL) THEN 'A'::character varying(1)
            ELSE NULL::character varying
        END AS "Motivatie_Achteruitgang"
   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_db_id ON ((vw_waargenomen_eigenschap_db_id."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)))
     LEFT JOIN public.wb_status_change_motivation wscm ON (((wscm.wbd_id = hdo_gwb.hdo_id) AND (wscm.opt_id = vw_waargenomen_eigenschap_db_id."DB_ID") AND (wscm.year_status = gwb_yearly_status.year_of_status) AND (wscm.scm_id = 1))))
     FULL JOIN krw_kwal ON ((vw_waargenomen_eigenschap_db_id."DB_ID" = krw_kwal.opt_id)))
UNION ALL
 SELECT gwb_yearly_status.gys_id AS "DB_ID",
    hdo_gwb.hdo_id AS "Waterlichaam_DB_ID",
    hdo_gwb.inspire_id_local_id AS "Waterlichaam_identificatie",
    hdo_gwb.wkrv_id AS "Waterlichaam_Versie",
    gwb_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.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",
    (gwb_yearly_status.comment_on_chemical_status)::character varying(5000) AS "Toelichting_ChemischeStatus",
    (gwb_yearly_status.comment_on_quantity_status)::character varying(5000) AS "Toelichting_EcologischeStatus",
    (gwb_yearly_status.motivation_4no_chemical_status)::character varying(5000) AS "Motivatie_Geen_ChemischeStatus",
    (gwb_yearly_status.motivation_4no_chemical_trends)::character varying(5000) AS "Motivatie_Geen_ChemischeTrend",
    (gwb_yearly_status.motivation_4no_quantity_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",
        CASE
            WHEN (wscm.scm_id IS NOT NULL) THEN 'A'::character varying(1)
            ELSE NULL::character varying
        END AS "Motivatie_Achteruitgang"
   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_db_id ON ((vw_waargenomen_eigenschap_db_id."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)))
     LEFT JOIN public.wb_status_change_motivation wscm ON (((wscm.wbd_id = hdo_gwb.hdo_id) AND (wscm.opt_id = vw_waargenomen_eigenschap_db_id."DB_ID") AND (wscm.year_status = gwb_yearly_status.year_of_status) AND (wscm.scm_id = 1))))
     FULL JOIN krw_kwal ON ((vw_waargenomen_eigenschap_db_id."DB_ID" = krw_kwal.opt_id)))
  ORDER BY 1, 6, 12 DESC, 10 DESC;



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

CREATE TABLE surface_water_body (
    swb_id integer NOT NULL,
    is_artificial boolean NOT NULL,
    sw_typology_id integer NOT NULL,
    waterbody_description text,
    is_heaviliy_modified boolean,
    representative_point geometry(Point,28992),
    area_value double precision,
    area_uom_id integer,
    large boolean,
    length_value double precision,
    length_uom_id integer,
    main boolean,
    other_pressure_description character varying(3000),
    other_impact_description text,
    last_change_time timestamp without time zone NOT NULL,
    sw_target_typology_id integer NOT NULL,
    article_43_comment text,
    article_43_litref text,
    article_43_url character varying(255),
    article_43b_motivation text,
    significantlychanged boolean,
    reason_change_old text,
    is_reservoir boolean DEFAULT false NOT NULL,
    is_transboundary boolean DEFAULT false NOT NULL,
    rct_id integer
);


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

CREATE TABLE sw_chemical_substance_status (
    sys_id integer NOT NULL,
    opt_id integer NOT NULL,
    swcs_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL,
    eumg_id integer NOT NULL,
    wbpm_id integer NOT NULL,
    css_id integer NOT NULL,
    observation_start_date date,
    observation_end_date date,
    clt_id integer
);


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

CREATE TABLE sw_ecological_status (
    sys_id integer NOT NULL,
    opt_id integer NOT NULL,
    swes_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL,
    eumg_id integer NOT NULL,
    wbpm_id integer NOT NULL,
    ecs_id integer NOT NULL,
    observation_start_date date,
    observation_end_date date,
    clt_id integer
);


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

CREATE TABLE wfd_qe_swtype (
    opt_id integer NOT NULL,
    swt_id integer NOT NULL,
    part_of_opt_id integer,
    last_change_time time without time zone,
    main_group character varying(50) NOT NULL,
    is_applicable boolean NOT NULL,
    qesw_id integer NOT NULL,
    clsg_id integer NOT NULL,
    name character varying(255) NOT NULL,
    is_excemption boolean 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: vw_oordeel_totaal_owm_cr_allqe; Type: VIEW; Schema: factsheets; Owner: -
--

CREATE VIEW vw_oordeel_totaal_owm_cr_allqe 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",
    krw_kwal.opt_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"
                WHEN (vw_waargenomen_eigenschap_db_id."Grootheid_Omschrijving" IS NOT NULL) THEN vw_waargenomen_eigenschap_db_id."Grootheid_Omschrijving"
                ELSE krw_kwal.name
            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)))
     FULL JOIN krw_kwal ON ((swb.sw_target_typology_id = krw_kwal.swt_id)))
     LEFT JOIN public.sw_chemical_substance_status ON (((sw_chemical_substance_status.opt_id = krw_kwal.opt_id) AND (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))))
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",
    krw_kwal.opt_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"
                WHEN (vw_waargenomen_eigenschap_db_id."Grootheid_Omschrijving" IS NOT NULL) THEN vw_waargenomen_eigenschap_db_id."Grootheid_Omschrijving"
                ELSE krw_kwal.name
            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)))
     FULL JOIN krw_kwal ON ((swb.sw_target_typology_id = krw_kwal.swt_id)))
     LEFT JOIN public.sw_ecological_status ON (((sw_ecological_status.opt_id = krw_kwal.opt_id) AND (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))));


--
-- Name: vw_oordeel_totaal_owm_for_crystal_reports; Type: VIEW; Schema: factsheets; 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_oordeel_totaal_owm_for_crystal_reports01; Type: VIEW; Schema: factsheets; Owner: -
--

CREATE VIEW vw_oordeel_totaal_owm_for_crystal_reports01 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 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",
    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)::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",
    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)::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_oordeel_totaal_owm_for_crystal_reports02; Type: VIEW; Schema: factsheets; Owner: -
--

CREATE VIEW vw_oordeel_totaal_owm_for_crystal_reports02 AS
 WITH yearly_status AS (
         SELECT swb_yearly_status.sys_id,
            swb_yearly_status.swb_id,
            swb_yearly_status.year_of_status,
            swb_yearly_status.comment_on_chemical_status,
            swb_yearly_status.comment_on_ecological_status,
            swb_yearly_status.motivation_4no_chemical_status,
            swb_yearly_status.motivation_4no_ecological_status,
            swb_yearly_status.last_change_time,
            swb_yearly_status.additional_purification,
            swb_yearly_status.exceeds_drinkingwater,
            hdo_swb.hdo_id,
            hdo_swb.inspire_id_local_id,
            hdo_swb.inspire_id_namespace,
            hdo_swb.wkrv_id,
            hdo_swb.hydro_object_type,
            hdo_swb.related_hdo_id,
            hdo_swb.last_change_time,
            swb.swb_id,
            swb.is_artificial,
            swb.sw_typology_id,
            swb.waterbody_description,
            swb.is_heaviliy_modified,
            swb.representative_point,
            swb.area_value,
            swb.area_uom_id,
            swb.large,
            swb.length_value,
            swb.length_uom_id,
            swb.main,
            swb.other_pressure_description,
            swb.other_impact_description,
            swb.last_change_time,
            swb.sw_target_typology_id,
            swb.article_43_comment,
            swb.article_43_litref,
            swb.article_43_url,
            swb.article_43b_motivation,
            swb.significantlychanged,
            swb.reason_change_old,
            swb.is_reservoir,
            swb.is_transboundary,
            swb.rct_id
           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)))
        ), oordeel AS (
         SELECT sw_chemical_substance_status.sys_id,
            sw_chemical_substance_status.opt_id,
            vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving",
            vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID",
            vw_waargenomen_eigenschap_db_id."Grootheid_Omschrijving",
            classification_type.code,
            classification_type.description,
            eu_monitoring_goal_type.eumg_id,
            eu_monitoring_goal_type.description AS eumg_description,
            waarde_bepalings_methode_type.code AS wbpm_code,
            waarde_bepalings_methode_type.omschrijving
           FROM ((((public.sw_chemical_substance_status
             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)))
        UNION ALL
         SELECT sw_ecological_status.sys_id,
            sw_ecological_status.opt_id,
            vw_waargenomen_eigenschap_db_id."ChemischeStof_Omschrijving",
            vw_waargenomen_eigenschap_db_id."ChemischeStof_DB_ID",
            vw_waargenomen_eigenschap_db_id."Grootheid_Omschrijving",
            classification_type.code,
            classification_type.description,
            eu_monitoring_goal_type.eumg_id,
            eu_monitoring_goal_type.description,
            waarde_bepalings_methode_type.code AS wbpm_code,
            waarde_bepalings_methode_type.omschrijving
           FROM ((((public.sw_ecological_status
             LEFT JOIN public.classification_type ON ((sw_ecological_status.swes_id = classification_type.cls_id)))
             FULL 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)))
        ), 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 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 yearly_status.sys_id AS "DB_ID",
    yearly_status.hdo_id AS "Waterlichaam_DB_ID",
    yearly_status.inspire_id_local_id AS "Waterlichaam_identificatie",
    yearly_status.wkrv_id AS "Waterlichaam_Versie",
    yearly_status.year_of_status AS "Rapportagejaar",
    oordeel.opt_id AS "Waargenomen_Eigenschap_DB_ID",
        CASE
            WHEN (krw_kwal.name IS NOT NULL) THEN krw_kwal.name
            ELSE
            CASE
                WHEN (oordeel."ChemischeStof_Omschrijving" IS NOT NULL) THEN oordeel."ChemischeStof_Omschrijving"
                ELSE oordeel."Grootheid_Omschrijving"
            END
        END AS "Typering",
    oordeel.code AS "Oordeel_Code",
    oordeel.description AS "Oordeel",
    oordeel.eumg_id AS "Oordeel_Type_DB_ID",
    oordeel.eumg_description AS "Oordeel_Type_Omschrijving",
    oordeel.wbpm_code AS "WaardebepalingsMethode_Code",
    oordeel.omschrijving AS "WaardebepalingsMethode_Omschrijving",
    (yearly_status.comment_on_chemical_status)::character varying(5000) AS "Toelichting_ChemischeStatus",
    (yearly_status.comment_on_ecological_status)::character varying(5000) AS "Toelichting_EcologischeStatus",
    (yearly_status.motivation_4no_chemical_status)::character varying(5000) AS "Motivatie_Geen_ChemischeStatus",
    (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"
   FROM ((((oordeel
     FULL JOIN krw_kwal ON ((oordeel.opt_id = krw_kwal.opt_id)))
     LEFT JOIN yearly_status yearly_status(sys_id, swb_id, year_of_status, comment_on_chemical_status, comment_on_ecological_status, motivation_4no_chemical_status, motivation_4no_ecological_status, last_change_time, additional_purification, exceeds_drinkingwater, hdo_id, inspire_id_local_id, inspire_id_namespace, wkrv_id, hydro_object_type, related_hdo_id, last_change_time_1, swb_id_1, is_artificial, sw_typology_id, waterbody_description, is_heaviliy_modified, representative_point, area_value, area_uom_id, large, length_value, length_uom_id, main, other_pressure_description, other_impact_description, last_change_time_2, sw_target_typology_id, article_43_comment, article_43_litref, article_43_url, article_43b_motivation, significantlychanged, reason_change_old, is_reservoir, is_transboundary, rct_id) ON ((oordeel.sys_id = yearly_status.sys_id)))
     LEFT JOIN public.wb_status_change_motivation wscm ON (((wscm.wbd_id = yearly_status.hdo_id) AND (wscm.opt_id = oordeel.opt_id) AND (wscm.year_status = yearly_status.year_of_status) AND (wscm.scm_id = 1))))
     LEFT JOIN ssg ON ((oordeel."ChemischeStof_DB_ID" = ssg.chs_id)))
  WHERE ((krw_kwal.is_applicable IS NULL) OR (yearly_status.sw_target_typology_id = krw_kwal.swt_id))
  ORDER BY yearly_status.sys_id, oordeel.opt_id, oordeel.omschrijving DESC, oordeel.eumg_description DESC;


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

CREATE VIEW vw_toestand_achteruitgang_owl_dgrw AS
 SELECT wscm.wbd_id,
    wscm.opt_id,
    wscm.year_status,
    hdo.inspire_id_local_id AS "Waterlichaam.identificatie",
    hdo_names.name AS "Waterlichaam.omschrijving",
    hdo.inspire_id_namespace AS "Waterbeheerder.code",
    vca."Omschrijving" AS "Waterbeheerder.omschrijving",
    chs.code AS "ChemischeStof.code",
    ssg.name AS "ChemischeStof.omschrijving",
    chs.cas_nr AS "Cas.nr",
    typ.code AS "Typering.code",
    typ.omschrijving AS "Typering.omschrijving",
    grh.code AS "Grootheid.code",
    grh.omschrijving AS "Grootheid.omschrijving",
    obj.code AS "object.code",
    obj.omschrijving AS "Object.omschrijving",
    hdh.code AS "Hoedanigheid.code",
    hdh.omschrijving AS "Hoedanigheid.omschrijving"
   FROM ((((((((((((((public.wb_status_change_motivation wscm
     JOIN public.hydro_object hdo ON ((wscm.wbd_id = hdo.hdo_id)))
     JOIN public.observed_property_type opt ON ((opt.opt_id = wscm.opt_id)))
     LEFT JOIN public.substance_group ssg ON ((ssg.ssg_id = opt.substance_group_id)))
     LEFT JOIN public.substance_group_element ssge ON ((ssge.ssge_id = ssg.ssg_id)))
     LEFT JOIN public.chemische_stof_type chs ON ((chs.chs_id = ssge.chs_id)))
     LEFT JOIN public.object_group ojg ON ((ojg.ojg_id = opt.object_group_id)))
     LEFT JOIN public.object_group_element ojge ON ((ojg.ojg_id = ojge.ojg_id)))
     LEFT JOIN public.object_type obj ON ((obj.obj_id = ojge.ojg_id)))
     LEFT JOIN public.hoedanigheid_type hdh ON ((hdh.hdh_id = opt.hoedanigheid_id)))
     LEFT JOIN public.typering_type typ ON ((typ.typ_id = opt.indicator_id)))
     LEFT JOIN public.grootheid_type grh ON ((grh.grh_id = opt.quantity_id)))
     JOIN public.competentauthority_namespacetype can ON ((can.ns_id = hdo.inspire_id_namespace)))
     LEFT JOIN public.vw_competent_authority vca ON ((vca."DB_ID" = can.ore_id)))
     LEFT JOIN public.hdo_names ON ((hdo_names.hdo_id = hdo.hdo_id)))
  WHERE (wscm.scm_id = 1)
  ORDER BY hdo.inspire_id_namespace, hdo.inspire_id_local_id;


--
-- Name: wb_type; Type: TABLE; Schema: factsheets; Owner: -
--

CREATE TABLE wb_type (
    wb_type_id integer NOT NULL,
    code character varying(50) NOT NULL,
    omschrijving character varying(50)
);



--
-- Name: cycle_description; Type: TABLE; Schema: krm; Owner: -
--

CREATE TABLE cycle_description (
    cym_id integer NOT NULL,
    description character varying(256),
    last_change_time time without time zone
);


--
-- Name: cycle_description_cym_id_seq; Type: SEQUENCE; Schema: krm; Owner: -
--

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


--
-- Name: cycle_description_cym_id_seq; Type: SEQUENCE OWNED BY; Schema: krm; Owner: -
--

ALTER SEQUENCE cycle_description_cym_id_seq OWNED BY cycle_description.cym_id;



--
-- Name: quality_element_indicator_descriptor; Type: TABLE; Schema: monprog; Owner: -
--

CREATE TABLE quality_element_indicator_descriptor (
    qem_id integer NOT NULL,
    idd_id integer NOT NULL,
    last_change_time timestamp without time zone NOT NULL
);



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

CREATE TABLE quality_element_observation (
    qeo_id integer NOT NULL,
    obs_id integer NOT NULL,
    qem_id integer NOT NULL,
    is_approved boolean DEFAULT false NOT NULL,
    last_change_time timestamp without time zone NOT NULL
);


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

CREATE TABLE quality_element_sample (
    qes_id integer NOT NULL,
    smpl_id integer NOT NULL,
    qem_id integer NOT NULL,
    is_approved boolean DEFAULT false NOT NULL,
    last_change_time timestamp without time zone NOT NULL
);



--
-- Name: hj_vw_krm_metingen_1; Type: VIEW; Schema: krm; Owner: -
--

CREATE VIEW hj_vw_krm_metingen_1 AS
 WITH bio AS (
         SELECT bio.obs_id,
            gedrag.code AS gedrag_code,
            geslacht.code AS geslacht_code,
            lengte.code AS lengte_code,
            levensstadium.code AS levensstadium_code,
            levensvorm.code AS levensvorm_code,
            verschijning.code AS verschijning_code
           FROM ((((((public.bio_observation bio
             LEFT JOIN public.hoedanigheid_type gedrag ON (((bio.gedrag_id = gedrag.hdh_id) AND (bio.gedrag_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type geslacht ON (((bio.geslacht_id = geslacht.hdh_id) AND (bio.geslacht_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type lengte ON (((bio.lengte_klasse_id = lengte.hdh_id) AND (bio.lengte_klasse_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type levensstadium ON (((bio.levensstadium_id = levensstadium.hdh_id) AND (bio.levensstadium_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type levensvorm ON (((bio.levensvorm_id = levensvorm.hdh_id) AND (bio.levensvorm_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type verschijning ON (((bio.verschijningsvorm_id = verschijning.hdh_id) AND (bio.verschijningsvorm_id IS NOT NULL))))
          WHERE ((bio.gedrag_id IS NOT NULL) AND (bio.geslacht_id IS NOT NULL) AND (bio.lengte_klasse_id IS NOT NULL) AND (bio.levensstadium_id IS NOT NULL) AND (bio.levensvorm_id IS NOT NULL) AND (bio.verschijningsvorm_id IS NOT NULL))
        ), opt AS (
         SELECT opt.opt_id,
            grh.code AS grh_code,
            grh.omschrijving AS grh_omschrijving,
            typ.code AS typ_code,
            typ.omschrijving AS typ_omschrijving,
            txt.omschrijving AS txg_omschrijving,
            chs.code AS chs_code,
            chs.naam AS chs_naam,
            obj.code AS obj_code,
            obj.omschrijving AS obj_omschrijving,
            hdh.code AS hdh_code,
            hdh.omschrijving AS hdh_omschrijving
           FROM ((((((public.observed_property_type opt
             LEFT JOIN public.grootheid_type grh ON (((grh.grh_id = opt.quantity_id) AND (opt.quantity_id IS NOT NULL))))
             LEFT JOIN public.typering_type typ ON (((typ.typ_id = opt.indicator_id) AND (opt.indicator_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type hdh ON (((hdh.hdh_id = opt.hoedanigheid_id) AND (opt.hoedanigheid_id IS NOT NULL))))
             LEFT JOIN ((public.taxa_group txg
             JOIN public.taxa_group_element tge ON ((tge.txg_id = txg.txg_id)))
             JOIN public.taxon_type txt ON ((txt.txn_id = tge.txn_id))) ON (((txg.txg_id = opt.taxa_group_id) AND ((txg.group_type)::text = 'I'::text))))
             LEFT JOIN ((public.substance_group ssg
             JOIN public.substance_group_element ssge ON ((ssge.ssge_id = ssg.ssg_id)))
             JOIN public.chemische_stof_type chs ON ((chs.chs_id = ssge.chs_id))) ON (((ssg.ssg_id = opt.substance_group_id) AND ((ssg.group_type)::text = 'I'::text))))
             LEFT JOIN ((public.object_group ojg
             JOIN public.object_group_element ojge ON ((ojg.ojg_id = ojge.ojg_id)))
             JOIN public.object_type obj ON ((obj.obj_id = ojge.obj_id))) ON (((ojg.ojg_id = opt.object_group_id) AND ((ojg.group_type)::text = 'I'::text))))
        )
 SELECT ((qem.qem_id)::text || (obs.obs_id)::text) AS unique_key,
    ((('NL'::text || (mno_nst.code)::text) || '_'::text) || (mno.inspire_id_local_id)::text) AS "Meetpunt.Identificatie",
        CASE
            WHEN (obf.obs_id IS NOT NULL) THEN (('NL'::text || (ns_hdo.code)::text) || (hdo.inspire_id_local_id)::text)
            ELSE
            CASE
                WHEN ((mno.monitoring_object_type)::text = 'Sample'::text) THEN NULL::text
                ELSE ((('NL'::text || (mno_nst.code)::text) || '_AQUO_COMPARTIMENT:'::text) || (compartiment_type.code)::text)
            END
        END AS "Geoobject.Identificatie",
    NULL::text AS "Monster.Identificatie",
    NULL::text AS "Monster.Namespace",
    NULL::integer AS "Monster.Versie",
    NULL::text AS "Compartiment.Code",
    NULL::text AS "Compartiment.Omschrijving",
    NULL::text AS "Orgaan.Code",
    NULL::text AS "Orgaan.Omschrijving",
    NULL::text AS "Organisme.Naam",
    NULL::numeric AS "BeginDiepte_m",
    NULL::numeric AS "EindDiepte_m",
    NULL::text AS "ReferentieVlak.Code",
    NULL::text AS "Monsterbewerkingsmethode.Code",
    NULL::text AS "Monsterbewerkingsmethode.Omschrijving",
    NULL::text AS "Monsterbewerkingsmethode.codespace",
    NULL::text AS "Bemonsteringsmethode.Code",
    NULL::text AS "Bemonsteringsmethode.Omschrijving",
    NULL::text AS "Bemonsteringsmethode.codespace",
    NULL::text AS "Monstercriterium.Code",
    NULL::text AS "veldapparaat.Code",
    NULL::text AS "veldapparaat.Omschrijving",
    NULL::text AS "veldapparaat.Codespace",
    NULL::text AS "Monsternemingsdatum",
    NULL::text AS "Monsternemingstijd",
    NULL::integer AS "Monster.Tijd_UTCoffset",
    NULL::text AS "ReferentieHorizontaal.code",
    NULL::text AS "Monster.Opmerking",
    NULL::text AS "GerelateerdMonsterobject",
    NULL::text AS "GerelateerdMonsterRol.id",
    obs.obs_id AS "Meetwaarde.Identificatie",
    ('NL'::text || (mno_nst.code)::text) AS "Meetwaarde.Namespace",
    mno.wkrv_id AS "Meetwaarde.Versie",
    opt.grh_code AS "Grootheid.code",
    opt.grh_omschrijving AS "Grootheid.omschrijving",
    opt.typ_code AS "Typering.code",
    opt.typ_omschrijving AS "Typering.omschrijving",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN 'Biotaxon'::text
            WHEN (opt.chs_code IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (opt.obj_code IS NOT NULL) THEN 'Object'::text
            ELSE NULL::text
        END AS "Parameter.Groep",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN (NULL::text)::character varying
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_code
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_code
            ELSE NULL::character varying
        END AS "Parameter.Code",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN opt.txg_omschrijving
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_naam
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_omschrijving
            ELSE NULL::character varying
        END AS "Parameter.Omschrijving",
    eenh.code AS "Eenheid.code",
    eenh.omschrijving AS "Eenheid.omschrijving",
    opt.hdh_code AS "Hoedanigheid.code",
    opt.hdh_omschrijving AS "Hoedanigheid.omschrijving",
    wbm.code AS "Waardebewerkigngsmethode.code",
        CASE
            WHEN (wbm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebewerkigngsmethode.codespace",
    wbpm.code AS "Waardebepalingsmethode.code",
        CASE
            WHEN (wbpm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebepalingsmethode.codespace",
    to_char(obs.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(obs.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(obs.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(obs.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    NULL::integer AS "Meetwaarde.Tijd_UTCoffset",
    to_char(obs.result_time, 'YYYY-MM-DD'::text) AS "Resultaatdatum",
    NULL::integer AS "Resultaat_UTCoffset",
    obs.limit_symbol_id AS "Limietsymbool",
    obs.numeric_value AS "Numeriekewaarde",
    obs.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    mar.code AS "Meetapparaat.code",
        CASE
            WHEN (mar.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Meetapparaat.codespace",
    bio.levensstadium_code AS "Levensstadium.code",
    bio.lengte_code AS "Lengteklasse.code",
    bio.geslacht_code AS "Geslachtcode",
    bio.verschijning_code AS "Verschijningsvorm.code",
    bio.levensvorm_code AS "Levensvorm.code",
    bio.gedrag_code AS "Gedrag.code",
        CASE
            WHEN (obx.obs_id_target IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (mno_nst.code)::text) || '_'::text) || (obx.obs_id_target)::text)
        END AS "GerelateerdObservatieobject",
    ocr.code AS "GerelateerdObservatieobjectRol.id",
    obs.remarks AS "Meetwaarde.Opmerking",
    indicator.additional_description AS "Indicator.EUOmschrijving",
    typi.code AS "Indicator.Code",
    typd.code AS "Descriptor.Code",
    descriptor.additional_description AS "Descriptor.EUOmschrijving"
   FROM (((((((((((((((((((domains.indicator_descriptor indicator
     JOIN public.observed_property_type opti ON ((indicator.opt_id = opti.opt_id)))
     JOIN public.typering_type typi ON ((opti.indicator_id = typi.typ_id)))
     JOIN domains.indicator_descriptor descriptor ON (((indicator.part_of_idd_id = descriptor.idd_id) AND (descriptor.part_of_idd_id IS NULL))))
     JOIN public.observed_property_type optd ON ((descriptor.opt_id = optd.opt_id)))
     JOIN public.typering_type typd ON ((optd.indicator_id = typd.typ_id)))
     JOIN monprog.quality_element_indicator_descriptor qeid ON ((qeid.idd_id = indicator.idd_id)))
     JOIN public.quality_element qem ON ((qem.qem_id = qeid.qem_id)))
     JOIN public.monitoring_object_quality_element moqe ON ((moqe.qem_id = qem.qem_id)))
     JOIN public.quality_element_observation qeo ON ((qeo.qem_id = qem.qem_id)))
     JOIN public.observation obs ON ((qeo.obs_id = obs.obs_id)))
     JOIN ((public.monitoring_object mno
     LEFT JOIN (public.monitored_compartiment
     JOIN public.compartiment_type ON ((monitored_compartiment.cptm_id = compartiment_type.cptm_id))) ON ((monitored_compartiment.mno_id = mno.mno_id)))
     JOIN public.namespace_type mno_nst ON ((mno_nst.ns_id = mno.inspire_id_namespace_id))) ON (((mno.mno_id = obs.mno_id) AND ((mno.monitoring_object_type)::text <> 'Sample'::text) AND (mno.mno_id = moqe.mno_id))))
     JOIN public.eenheid_type eenh ON ((eenh.eenh_id = obs.numeric_value_uom_id)))
     JOIN opt ON ((opt.opt_id = obs.observed_property_type)))
     LEFT JOIN public.kwaliteitsoordeel_type ON ((obs.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN ((public.observation_process obp
     JOIN public.waarde_bepalings_methode_type wbpm ON ((wbpm.wbpm_id = obp.wbpm_id)))
     LEFT JOIN (public.obs_process_meetapparaat opm
     JOIN domains.meetapparaat mar ON ((mar.mar_id = opm.mar_id))) ON ((opm.obp_id = obp.obp_id))) ON ((obp.obp_id = obs.result_nature_id)))
     LEFT JOIN bio ON ((bio.obs_id = obs.obs_id)))
     LEFT JOIN (public.observation_context obx
     JOIN public.obs_ctx_role_type ocr ON ((ocr.ocr_id = obx.role_id))) ON ((obx.obs_id_source = obs.obs_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type wbm ON ((wbm.wbm_id = obs.value_processing_method_id)))
     LEFT JOIN ((public.observed_feature obf
     JOIN public.hydro_object hdo ON ((obf.hdo_id = hdo.hdo_id)))
     JOIN public.namespace_type ns_hdo ON ((hdo.inspire_id_namespace = ns_hdo.ns_id))) ON (((obs.obs_id = obf.obs_id) AND (mno.wkrv_id = hdo.wkrv_id))))
UNION ALL
 SELECT ((qem.osc_id)::text || (obs.obs_id)::text) AS unique_key,
    ((('NL'::text || (mpn_nst.code)::text) || '_'::text) || (mno_mpn.inspire_id_local_id)::text) AS "Meetpunt.Identificatie",
        CASE
            WHEN (obf.obs_id IS NOT NULL) THEN ((('NL'::text || (ns_hdo.code)::text) || '_'::text) || (hdo.inspire_id_local_id)::text)
            ELSE NULL::text
        END AS "Geoobject.Identificatie",
    mno_smpl.inspire_id_local_id AS "Monster.Identificatie",
    mpn_nst.code AS "Monster.Namespace",
    mno_smpl.wkrv_id AS "Monster.Versie",
    cmp.code AS "Compartiment.Code",
    cmp.description AS "Compartiment.Omschrijving",
    orgaan.code AS "Orgaan.Code",
    orgaan.description AS "Orgaan.Omschrijving",
    taxon_type.omschrijving AS "Organisme.Naam",
    smpl.upper_depth_value AS "BeginDiepte_m",
    smpl.lower_depth_value AS "EindDiepte_m",
        CASE
            WHEN (hdh_ud.hdh_id IS NULL) THEN
            CASE
                WHEN (hdh_ld.hdh_id IS NULL) THEN (NULL::text)::character varying
                ELSE hdh_ld.code
            END
            ELSE hdh_ud.code
        END AS "ReferentieVlak.Code",
    mbm.code AS "Monsterbewerkingsmethode.Code",
    mbm.description AS "Monsterbewerkingsmethode.Omschrijving",
    mbm.codespace AS "Monsterbewerkingsmethode.codespace",
    bmm.code AS "Bemonsteringsmethode.Code",
    bmm.description AS "Bemonsteringsmethode.Omschrijving",
        CASE
            WHEN (bmm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Bemonsteringsmethode.codespace",
        CASE
            WHEN (frac.code IS NULL) THEN biota.code
            ELSE frac.code
        END AS "Monstercriterium.Code",
    var.code AS "veldapparaat.Code",
    var.description AS "veldapparaat.Omschrijving",
    var.codespace AS "veldapparaat.Codespace",
    to_char(smpl.sampling_time, 'YYYY-MM-DD'::text) AS "Monsternemingsdatum",
    to_char(smpl.sampling_time, 'HH24:MM:SS'::text) AS "Monsternemingstijd",
    NULL::integer AS "Monster.Tijd_UTCoffset",
    'EPSG4258'::text AS "ReferentieHorizontaal.code",
    smpl.remarks AS "Monster.Opmerking",
        CASE
            WHEN (mno_smpl.wkrv_id IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (smpl_nst.code)::text) || '_'::text) || (mno_smpl.inspire_id_local_id)::text)
        END AS "GerelateerdMonsterobject",
        CASE
            WHEN (mno_smpl.wkrv_id IS NULL) THEN (NULL::text)::character varying
            ELSE rsfr.code
        END AS "GerelateerdMonsterRol.id",
    obs.obs_id AS "Meetwaarde.Identificatie",
    ('NL'::text || (mpn_nst.code)::text) AS "Meetwaarde.Namespace",
    mno_mpn.wkrv_id AS "Meetwaarde.Versie",
    opt.grh_code AS "Grootheid.code",
    opt.grh_omschrijving AS "Grootheid.omschrijving",
    opt.typ_code AS "Typering.code",
    opt.typ_omschrijving AS "Typering.omschrijving",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN 'Biotaxon'::text
            WHEN (opt.chs_code IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (opt.obj_code IS NOT NULL) THEN 'Object'::text
            ELSE NULL::text
        END AS "Parameter.Groep",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN (NULL::text)::character varying
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_code
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_code
            ELSE NULL::character varying
        END AS "Parameter.Code",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN opt.txg_omschrijving
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_naam
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_omschrijving
            ELSE NULL::character varying
        END AS "Parameter.Omschrijving",
    eenh.code AS "Eenheid.code",
    eenh.omschrijving AS "Eenheid.omschrijving",
    opt.hdh_code AS "Hoedanigheid.code",
    opt.hdh_omschrijving AS "Hoedanigheid.omschrijving",
    wbm.code AS "Waardebewerkigngsmethode.code",
        CASE
            WHEN (wbm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebewerkigngsmethode.codespace",
    wbpm.code AS "Waardebepalingsmethode.code",
        CASE
            WHEN (wbpm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebepalingsmethode.codespace",
    to_char(obs.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(obs.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(obs.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(obs.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    NULL::integer AS "Meetwaarde.Tijd_UTCoffset",
    to_char(obs.result_time, 'YYYY-MM-DD'::text) AS "Resultaatdatum",
    NULL::integer AS "Resultaat_UTCoffset",
    obs.limit_symbol_id AS "Limietsymbool",
    obs.numeric_value AS "Numeriekewaarde",
    obs.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    mar.code AS "Meetapparaat.code",
        CASE
            WHEN (mar.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Meetapparaat.codespace",
    bio.levensstadium_code AS "Levensstadium.code",
    bio.lengte_code AS "Lengteklasse.code",
    bio.geslacht_code AS "Geslachtcode",
    bio.verschijning_code AS "Verschijningsvorm.code",
    bio.levensvorm_code AS "Levensvorm.code",
    bio.gedrag_code AS "Gedrag.code",
        CASE
            WHEN (obx.obs_id_target IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (mpn_nst.code)::text) || '_'::text) || (obx.obs_id_target)::text)
        END AS "GerelateerdObservatieobject",
    ocr.code AS "GerelateerdObservatieobjectRol.id",
    obs.remarks AS "Meetwaarde.Opmerking",
    indicator.additional_description AS "Indicator.EUOmschrijving",
    typi.code AS "Indicator.Code",
    typd.code AS "Descriptor.Code",
    descriptor.additional_description AS "Descriptor.EUOmschrijving"
   FROM (((((((((((((((((((((((((((((((((domains.indicator_descriptor indicator
     JOIN public.observed_property_type opti ON ((indicator.opt_id = opti.opt_id)))
     JOIN public.typering_type typi ON ((opti.indicator_id = typi.typ_id)))
     JOIN domains.indicator_descriptor descriptor ON (((indicator.part_of_idd_id = descriptor.idd_id) AND (descriptor.part_of_idd_id IS NULL))))
     JOIN public.observed_property_type optd ON ((descriptor.opt_id = optd.opt_id)))
     JOIN public.typering_type typd ON ((optd.indicator_id = typd.typ_id)))
     JOIN monprog.quality_element_indicator_descriptor qeid ON ((qeid.idd_id = indicator.idd_id)))
     JOIN public.quality_element qem ON ((qem.qem_id = qeid.qem_id)))
     JOIN public.quality_element_sample qes ON ((qem.qem_id = qes.qem_id)))
     JOIN public.sample smpl ON ((smpl.smpl_id = qes.smpl_id)))
     JOIN public.observation obs ON ((smpl.smpl_id = obs.mno_id)))
     JOIN public.compartiment_type cmp ON ((smpl.material_class_id = cmp.cptm_id)))
     LEFT JOIN public.hoedanigheid_type hdh_ud ON ((hdh_ud.hdh_id = smpl.upper_depth_condition_id)))
     LEFT JOIN public.hoedanigheid_type hdh_ld ON ((hdh_ld.hdh_id = smpl.lower_depth_condition_id)))
     LEFT JOIN public.taxon_type ON ((smpl.txn_id = taxon_type.txn_id)))
     LEFT JOIN domains.orgaan ON ((orgaan.org_id = smpl.organ_id)))
     LEFT JOIN public.hoedanigheid_type frac ON ((frac.hdh_id = smpl.fraction_criterium_id)))
     LEFT JOIN public.hoedanigheid_type biota ON ((biota.hdh_id = smpl.biota_criterium_id)))
     LEFT JOIN public.eenheid_type eenh_ud ON ((eenh_ud.eenh_id = smpl.upper_depth_uom_id)))
     LEFT JOIN public.eenheid_type eenh_ld ON ((eenh_ld.eenh_id = smpl.lower_depth_uom_id)))
     LEFT JOIN (((public.sampling_proces smp
     LEFT JOIN domains.monsterbewerkingsmethode mbm ON ((mbm.mbm_id = smp.sampling_method_id)))
     LEFT JOIN domains.bemonsteringsmethode bmm ON ((bmm.bmm_id = smp.sampling_processing_method_id)))
     LEFT JOIN domains.veldapparaat var ON ((var.var_id = smp.var_id))) ON ((smp.smp_id = smpl.smp_id)))
     LEFT JOIN public.related_sampling_feature rel_smpl ON ((smpl.smpl_id = rel_smpl.mnf_id_source)))
     LEFT JOIN public.sample smpl2 ON ((smpl.smpl_id = rel_smpl.mnf_id_target)))
     LEFT JOIN (public.monitoring_object mno_mpn
     JOIN public.namespace_type mpn_nst ON ((mpn_nst.ns_id = mno_mpn.inspire_id_namespace_id))) ON (((mno_mpn.mno_id = rel_smpl.mnf_id_target) AND ((mno_mpn.monitoring_object_type)::text <> 'Sample'::text))))
     LEFT JOIN (public.monitoring_object mno_smpl
     JOIN public.namespace_type smpl_nst ON ((smpl_nst.ns_id = mno_smpl.inspire_id_namespace_id))) ON (((smpl2.smpl_id = mno_smpl.mno_id) AND ((mno_smpl.monitoring_object_type)::text = 'Sample'::text))))
     LEFT JOIN public.rel_samp_feat_role_type rsfr ON ((rsfr.rsfr_id = rel_smpl.role_id)))
     JOIN public.eenheid_type eenh ON ((eenh.eenh_id = obs.numeric_value_uom_id)))
     JOIN opt ON ((opt.opt_id = obs.observed_property_type)))
     LEFT JOIN public.kwaliteitsoordeel_type ON ((obs.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN ((public.observation_process obp
     LEFT JOIN public.waarde_bepalings_methode_type wbpm ON ((wbpm.wbpm_id = obp.wbpm_id)))
     LEFT JOIN (public.obs_process_meetapparaat opm
     JOIN domains.meetapparaat mar ON ((mar.mar_id = opm.mar_id))) ON ((opm.obp_id = obp.obp_id))) ON ((obp.obp_id = obs.result_nature_id)))
     LEFT JOIN bio ON ((bio.obs_id = obs.obs_id)))
     LEFT JOIN (public.observation_context obx
     JOIN public.obs_ctx_role_type ocr ON ((ocr.ocr_id = obx.role_id))) ON ((obx.obs_id_source = obs.obs_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type wbm ON ((wbm.wbm_id = obs.value_processing_method_id)))
     LEFT JOIN ((public.observed_feature obf
     JOIN public.hydro_object hdo ON ((obf.hdo_id = hdo.hdo_id)))
     JOIN public.namespace_type ns_hdo ON ((hdo.inspire_id_namespace = ns_hdo.ns_id))) ON ((obs.obs_id = obf.obs_id)));


--
-- Name: hj_vw_krm_metingen_1_g; Type: VIEW; Schema: krm; Owner: -
--

CREATE VIEW hj_vw_krm_metingen_1_g AS
 WITH bio AS (
         SELECT bio.obs_id,
            gedrag.code AS gedrag_code,
            geslacht.code AS geslacht_code,
            lengte.code AS lengte_code,
            levensstadium.code AS levensstadium_code,
            levensvorm.code AS levensvorm_code,
            verschijning.code AS verschijning_code
           FROM ((((((public.bio_observation bio
             LEFT JOIN public.hoedanigheid_type gedrag ON (((bio.gedrag_id = gedrag.hdh_id) AND (bio.gedrag_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type geslacht ON (((bio.geslacht_id = geslacht.hdh_id) AND (bio.geslacht_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type lengte ON (((bio.lengte_klasse_id = lengte.hdh_id) AND (bio.lengte_klasse_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type levensstadium ON (((bio.levensstadium_id = levensstadium.hdh_id) AND (bio.levensstadium_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type levensvorm ON (((bio.levensvorm_id = levensvorm.hdh_id) AND (bio.levensvorm_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type verschijning ON (((bio.verschijningsvorm_id = verschijning.hdh_id) AND (bio.verschijningsvorm_id IS NOT NULL))))
          WHERE ((bio.gedrag_id IS NOT NULL) AND (bio.geslacht_id IS NOT NULL) AND (bio.lengte_klasse_id IS NOT NULL) AND (bio.levensstadium_id IS NOT NULL) AND (bio.levensvorm_id IS NOT NULL) AND (bio.verschijningsvorm_id IS NOT NULL))
        ), opt AS (
         SELECT opt.opt_id,
            grh.code AS grh_code,
            grh.omschrijving AS grh_omschrijving,
            typ.code AS typ_code,
            typ.omschrijving AS typ_omschrijving,
            txt.omschrijving AS txg_omschrijving,
            chs.code AS chs_code,
            chs.naam AS chs_naam,
            obj.code AS obj_code,
            obj.omschrijving AS obj_omschrijving,
            hdh.code AS hdh_code,
            hdh.omschrijving AS hdh_omschrijving
           FROM ((((((public.observed_property_type opt
             LEFT JOIN public.grootheid_type grh ON (((grh.grh_id = opt.quantity_id) AND (opt.quantity_id IS NOT NULL))))
             LEFT JOIN public.typering_type typ ON (((typ.typ_id = opt.indicator_id) AND (opt.indicator_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type hdh ON (((hdh.hdh_id = opt.hoedanigheid_id) AND (opt.hoedanigheid_id IS NOT NULL))))
             LEFT JOIN ((public.taxa_group txg
             JOIN public.taxa_group_element tge ON ((tge.txg_id = txg.txg_id)))
             JOIN public.taxon_type txt ON ((txt.txn_id = tge.txn_id))) ON (((txg.txg_id = opt.taxa_group_id) AND ((txg.group_type)::text = 'I'::text))))
             LEFT JOIN ((public.substance_group ssg
             JOIN public.substance_group_element ssge ON ((ssge.ssge_id = ssg.ssg_id)))
             JOIN public.chemische_stof_type chs ON ((chs.chs_id = ssge.chs_id))) ON (((ssg.ssg_id = opt.substance_group_id) AND ((ssg.group_type)::text = 'I'::text))))
             LEFT JOIN ((public.object_group ojg
             JOIN public.object_group_element ojge ON ((ojg.ojg_id = ojge.ojg_id)))
             JOIN public.object_type obj ON ((obj.obj_id = ojge.obj_id))) ON (((ojg.ojg_id = opt.object_group_id) AND ((ojg.group_type)::text = 'I'::text))))
        )
 SELECT ((qem.qem_id)::text || (obs.obs_id)::text) AS unique_key,
    qem.osc_id AS "MonProg.Identificatie",
    (osc.name)::text AS "MonProg.Naam",
    ((('NL'::text || (mno_nst.code)::text) || '_'::text) || (mno.inspire_id_local_id)::text) AS "Meetpunt.Identificatie",
    public.st_x(public.st_transform(public.st_centroid(mno.geometry_etrs89), 4258)) AS "GeometriePunt.X",
    public.st_y(public.st_transform(public.st_centroid(mno.geometry_etrs89), 4258)) AS "GeometriePunt.Y",
        CASE
            WHEN (obf.obs_id IS NOT NULL) THEN (('NL'::text || (ns_hdo.code)::text) || (hdo.inspire_id_local_id)::text)
            ELSE
            CASE
                WHEN ((mno.monitoring_object_type)::text = 'Sample'::text) THEN NULL::text
                ELSE ((('NL'::text || (mno_nst.code)::text) || '_AQUO_COMPARTIMENT:'::text) || (compartiment_type.code)::text)
            END
        END AS "Geoobject.Identificatie",
    NULL::text AS "Monster.Identificatie",
    NULL::text AS "Monster.Namespace",
    NULL::integer AS "Monster.Versie",
    NULL::text AS "Compartiment.Code",
    NULL::text AS "Compartiment.Omschrijving",
    NULL::text AS "Orgaan.Code",
    NULL::text AS "Orgaan.Omschrijving",
    NULL::text AS "Organisme.Naam",
    NULL::numeric AS "BeginDiepte_m",
    NULL::numeric AS "EindDiepte_m",
    NULL::text AS "ReferentieVlak.Code",
    NULL::text AS "Monsterbewerkingsmethode.Code",
    NULL::text AS "Monsterbewerkingsmethode.Omschrijving",
    NULL::text AS "Monsterbewerkingsmethode.Codespace",
    NULL::text AS "Bemonsteringsmethode.Code",
    NULL::text AS "Bemonsteringsmethode.Omschrijving",
    NULL::text AS "Bemonsteringsmethode.Codespace",
    NULL::text AS "Monstercriterium.Code",
    NULL::text AS "Veldapparaat.Code",
    NULL::text AS "Veldapparaat.Omschrijving",
    NULL::text AS "Veldapparaat.Codespace",
    NULL::text AS "Monsternemingsdatum",
    NULL::text AS "Monsternemingstijd",
    NULL::integer AS "Monster.Tijd_UTCoffset",
    NULL::text AS "ReferentieHorizontaal.code",
    NULL::text AS "Monster.Opmerking",
    NULL::text AS "GerelateerdMonsterobject",
    NULL::text AS "GerelateerdMonsterRol.id",
    obs.obs_id AS "Meetwaarde.Identificatie",
    ('NL'::text || (mno_nst.code)::text) AS "Meetwaarde.Namespace",
    mno.wkrv_id AS "Meetwaarde.Versie",
    opt.grh_code AS "Grootheid.code",
    opt.grh_omschrijving AS "Grootheid.omschrijving",
    opt.typ_code AS "Typering.code",
    opt.typ_omschrijving AS "Typering.omschrijving",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN 'Biotaxon'::text
            WHEN (opt.chs_code IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (opt.obj_code IS NOT NULL) THEN 'Object'::text
            ELSE NULL::text
        END AS "Parameter.Groep",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN (NULL::text)::character varying
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_code
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_code
            ELSE NULL::character varying
        END AS "Parameter.Code",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN opt.txg_omschrijving
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_naam
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_omschrijving
            ELSE NULL::character varying
        END AS "Parameter.Omschrijving",
    eenh.code AS "Eenheid.Code",
    eenh.omschrijving AS "Eenheid.Omschrijving",
    opt.hdh_code AS "Hoedanigheid.Code",
    opt.hdh_omschrijving AS "Hoedanigheid.Omschrijving",
    wbm.code AS "Waardebewerkingsmethode.Code",
    (wbm.omschrijving)::text AS "Waardebewerkingsmethode.Omschrijving",
        CASE
            WHEN (wbm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebewerkingsmethode.Codespace",
    wbpm.code AS "Waardebepalingsmethode.Code",
    (wbpm.omschrijving)::text AS "Waardebepalingsmethode.Omschrijving",
        CASE
            WHEN (wbpm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebepalingsmethode.Codespace",
    to_char(obs.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(obs.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(obs.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(obs.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    NULL::integer AS "Meetwaarde.Tijd_UTCoffset",
    to_char(obs.result_time, 'YYYY-MM-DD'::text) AS "Resultaatdatum",
    NULL::integer AS "Resultaat_UTCoffset",
    bpg.omschrijving AS "Limietsymbool",
    obs.numeric_value AS "NumeriekeWaarde",
    obs.description AS "AlfanumeriekeWaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.Code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.Omschrijving",
    mar.code AS "Meetapparaat.Code",
        CASE
            WHEN (mar.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Meetapparaat.Codespace",
    bio.levensstadium_code AS "Levensstadium.Code",
    bio.lengte_code AS "Lengteklasse.Code",
    bio.geslacht_code AS "Geslacht.Code",
    bio.verschijning_code AS "Verschijningsvorm.Code",
    bio.levensvorm_code AS "Levensvorm.Code",
    bio.gedrag_code AS "Gedrag.Code",
        CASE
            WHEN (obx.obs_id_target IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (mno_nst.code)::text) || '_'::text) || (obx.obs_id_target)::text)
        END AS "GerelateerdObservatieobject",
    ocr.code AS "GerelateerdObservatieobjectRol.id",
    obs.remarks AS "Meetwaarde.Opmerking",
    indicator.additional_description AS "Indicator.EUOmschrijving",
    typi.code AS "Indicator.Code",
    typd.code AS "Descriptor.Code",
    descriptor.additional_description AS "Descriptor.EUOmschrijving"
   FROM (((((((((((((((((((((domains.indicator_descriptor indicator
     JOIN public.observed_property_type opti ON ((indicator.opt_id = opti.opt_id)))
     JOIN public.typering_type typi ON ((opti.indicator_id = typi.typ_id)))
     JOIN domains.indicator_descriptor descriptor ON (((indicator.part_of_idd_id = descriptor.idd_id) AND (descriptor.part_of_idd_id IS NULL))))
     JOIN public.observed_property_type optd ON ((descriptor.opt_id = optd.opt_id)))
     JOIN public.typering_type typd ON ((optd.indicator_id = typd.typ_id)))
     JOIN monprog.quality_element_indicator_descriptor qeid ON ((qeid.idd_id = indicator.idd_id)))
     JOIN public.quality_element qem ON ((qem.qem_id = qeid.qem_id)))
     JOIN public.observing_capability osc ON ((osc.osc_id = qem.osc_id)))
     JOIN public.monitoring_object_quality_element moqe ON ((moqe.qem_id = qem.qem_id)))
     JOIN public.quality_element_observation qeo ON ((qeo.qem_id = qem.qem_id)))
     JOIN public.observation obs ON ((qeo.obs_id = obs.obs_id)))
     LEFT JOIN public.bepaling_grens_type bpg ON ((bpg.bpg_id = obs.limit_symbol_id)))
     JOIN ((public.monitoring_object mno
     LEFT JOIN (public.monitored_compartiment
     JOIN public.compartiment_type ON ((monitored_compartiment.cptm_id = compartiment_type.cptm_id))) ON ((monitored_compartiment.mno_id = mno.mno_id)))
     JOIN public.namespace_type mno_nst ON ((mno_nst.ns_id = mno.inspire_id_namespace_id))) ON (((mno.mno_id = obs.mno_id) AND ((mno.monitoring_object_type)::text <> 'Sample'::text) AND (mno.mno_id = moqe.mno_id))))
     JOIN public.eenheid_type eenh ON ((eenh.eenh_id = obs.numeric_value_uom_id)))
     JOIN opt ON ((opt.opt_id = obs.observed_property_type)))
     LEFT JOIN public.kwaliteitsoordeel_type ON ((obs.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN ((public.observation_process obp
     JOIN public.waarde_bepalings_methode_type wbpm ON ((wbpm.wbpm_id = obp.wbpm_id)))
     LEFT JOIN (public.obs_process_meetapparaat opm
     JOIN domains.meetapparaat mar ON ((mar.mar_id = opm.mar_id))) ON ((opm.obp_id = obp.obp_id))) ON ((obp.obp_id = obs.result_nature_id)))
     LEFT JOIN bio ON ((bio.obs_id = obs.obs_id)))
     LEFT JOIN (public.observation_context obx
     JOIN public.obs_ctx_role_type ocr ON ((ocr.ocr_id = obx.role_id))) ON ((obx.obs_id_source = obs.obs_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type wbm ON ((wbm.wbm_id = obs.value_processing_method_id)))
     LEFT JOIN ((public.observed_feature obf
     JOIN public.hydro_object hdo ON ((obf.hdo_id = hdo.hdo_id)))
     JOIN public.namespace_type ns_hdo ON ((hdo.inspire_id_namespace = ns_hdo.ns_id))) ON (((obs.obs_id = obf.obs_id) AND (mno.wkrv_id = hdo.wkrv_id))))
UNION ALL
 SELECT ((qem.osc_id)::text || (obs.obs_id)::text) AS unique_key,
    qem.osc_id AS "MonProg.Identificatie",
    (osc.name)::text AS "MonProg.Naam",
    ((('NL'::text || (mpn_nst.code)::text) || '_'::text) || (mno_mpn.inspire_id_local_id)::text) AS "Meetpunt.Identificatie",
    public.st_x(public.st_transform(smpl.sampling_location, 4258)) AS "GeometriePunt.X",
    public.st_y(public.st_transform(smpl.sampling_location, 4258)) AS "GeometriePunt.Y",
        CASE
            WHEN (obf.obs_id IS NOT NULL) THEN ((('NL'::text || (ns_hdo.code)::text) || '_'::text) || (hdo.inspire_id_local_id)::text)
            ELSE NULL::text
        END AS "Geoobject.Identificatie",
    smpl.name AS "Monster.Identificatie",
    mpn_nst.code AS "Monster.Namespace",
    mno_smpl.wkrv_id AS "Monster.Versie",
    cmp.code AS "Compartiment.Code",
    cmp.description AS "Compartiment.Omschrijving",
    orgaan.code AS "Orgaan.Code",
    orgaan.description AS "Orgaan.Omschrijving",
    taxon_type.omschrijving AS "Organisme.Naam",
    smpl.upper_depth_value AS "BeginDiepte_m",
    smpl.lower_depth_value AS "EindDiepte_m",
        CASE
            WHEN (hdh_ud.hdh_id IS NULL) THEN
            CASE
                WHEN (hdh_ld.hdh_id IS NULL) THEN (NULL::text)::character varying
                ELSE hdh_ld.code
            END
            ELSE hdh_ud.code
        END AS "ReferentieVlak.Code",
    mbm.code AS "Monsterbewerkingsmethode.Code",
    mbm.description AS "Monsterbewerkingsmethode.Omschrijving",
    mbm.codespace AS "Monsterbewerkingsmethode.Codespace",
    bmm.code AS "Bemonsteringsmethode.Code",
    bmm.description AS "Bemonsteringsmethode.Omschrijving",
        CASE
            WHEN (bmm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Bemonsteringsmethode.Codespace",
        CASE
            WHEN (frac.code IS NULL) THEN biota.code
            ELSE frac.code
        END AS "Monstercriterium.Code",
    var.code AS "Veldapparaat.Code",
    var.description AS "Veldapparaat.Omschrijving",
    var.codespace AS "Veldapparaat.Codespace",
    to_char(smpl.sampling_time, 'YYYY-MM-DD'::text) AS "Monsternemingsdatum",
    to_char(smpl.sampling_time, 'HH24:MM:SS'::text) AS "Monsternemingstijd",
    NULL::integer AS "Monster.Tijd_UTCoffset",
    'EPSG4258'::text AS "ReferentieHorizontaal.code",
    smpl.remarks AS "Monster.Opmerking",
        CASE
            WHEN (mno_smpl.wkrv_id IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (smpl_nst.code)::text) || '_'::text) || (mno_smpl.inspire_id_local_id)::text)
        END AS "GerelateerdMonsterobject",
        CASE
            WHEN (mno_smpl.wkrv_id IS NULL) THEN (NULL::text)::character varying
            ELSE rsfr.code
        END AS "GerelateerdMonsterRol.id",
    obs.obs_id AS "Meetwaarde.Identificatie",
    ('NL'::text || (mpn_nst.code)::text) AS "Meetwaarde.Namespace",
    mno_mpn.wkrv_id AS "Meetwaarde.Versie",
    opt.grh_code AS "Grootheid.code",
    opt.grh_omschrijving AS "Grootheid.omschrijving",
    opt.typ_code AS "Typering.code",
    opt.typ_omschrijving AS "Typering.omschrijving",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN 'Biotaxon'::text
            WHEN (opt.chs_code IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (opt.obj_code IS NOT NULL) THEN 'Object'::text
            ELSE NULL::text
        END AS "Parameter.Groep",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN (NULL::text)::character varying
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_code
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_code
            ELSE NULL::character varying
        END AS "Parameter.Code",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN opt.txg_omschrijving
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_naam
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_omschrijving
            ELSE NULL::character varying
        END AS "Parameter.Omschrijving",
    eenh.code AS "Eenheid.Code",
    eenh.omschrijving AS "Eenheid.Omschrijving",
    opt.hdh_code AS "Hoedanigheid.Code",
    opt.hdh_omschrijving AS "Hoedanigheid.Omschrijving",
    wbm.code AS "Waardebewerkingsmethode.Code",
    (wbm.omschrijving)::text AS "Waardebewerkingsmethode.Omschrijving",
        CASE
            WHEN (wbm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebewerkingsmethode.Codespace",
    wbpm.code AS "Waardebepalingsmethode.Code",
    (wbpm.omschrijving)::text AS "Waardebepalingsmethode.Omschrijving",
        CASE
            WHEN (wbpm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebepalingsmethode.Codespace",
    to_char(obs.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(obs.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(obs.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(obs.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    NULL::integer AS "Meetwaarde.Tijd_UTCoffset",
    to_char(obs.result_time, 'YYYY-MM-DD'::text) AS "Resultaatdatum",
    NULL::integer AS "Resultaat_UTCoffset",
    bpg.omschrijving AS "Limietsymbool",
    obs.numeric_value AS "NumeriekeWaarde",
    obs.description AS "AlfanumeriekeWaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.Code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.Omschrijving",
    mar.code AS "Meetapparaat.Code",
        CASE
            WHEN (mar.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Meetapparaat.Codespace",
    bio.levensstadium_code AS "Levensstadium.Code",
    bio.lengte_code AS "Lengteklasse.Code",
    bio.geslacht_code AS "Geslacht.Code",
    bio.verschijning_code AS "Verschijningsvorm.Code",
    bio.levensvorm_code AS "Levensvorm.Code",
    bio.gedrag_code AS "Gedrag.Code",
        CASE
            WHEN (obx.obs_id_target IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (mpn_nst.code)::text) || '_'::text) || (obx.obs_id_target)::text)
        END AS "GerelateerdObservatieobject",
    ocr.code AS "GerelateerdObservatieobjectRol.id",
    obs.remarks AS "Meetwaarde.Opmerking",
    indicator.additional_description AS "Indicator.EUOmschrijving",
    typi.code AS "Indicator.Code",
    typd.code AS "Descriptor.Code",
    descriptor.additional_description AS "Descriptor.EUOmschrijving"
   FROM (((((((((((((((((((((((((((((((((((domains.indicator_descriptor indicator
     JOIN public.observed_property_type opti ON ((indicator.opt_id = opti.opt_id)))
     JOIN public.typering_type typi ON ((opti.indicator_id = typi.typ_id)))
     JOIN domains.indicator_descriptor descriptor ON (((indicator.part_of_idd_id = descriptor.idd_id) AND (descriptor.part_of_idd_id IS NULL))))
     JOIN public.observed_property_type optd ON ((descriptor.opt_id = optd.opt_id)))
     JOIN public.typering_type typd ON ((optd.indicator_id = typd.typ_id)))
     JOIN monprog.quality_element_indicator_descriptor qeid ON ((qeid.idd_id = indicator.idd_id)))
     JOIN public.quality_element qem ON ((qem.qem_id = qeid.qem_id)))
     JOIN public.observing_capability osc ON ((osc.osc_id = qem.osc_id)))
     JOIN public.quality_element_sample qes ON ((qem.qem_id = qes.qem_id)))
     JOIN public.sample smpl ON ((smpl.smpl_id = qes.smpl_id)))
     JOIN public.observation obs ON ((smpl.smpl_id = obs.mno_id)))
     LEFT JOIN public.bepaling_grens_type bpg ON ((bpg.bpg_id = obs.limit_symbol_id)))
     JOIN public.compartiment_type cmp ON ((smpl.material_class_id = cmp.cptm_id)))
     LEFT JOIN public.hoedanigheid_type hdh_ud ON ((hdh_ud.hdh_id = smpl.upper_depth_condition_id)))
     LEFT JOIN public.hoedanigheid_type hdh_ld ON ((hdh_ld.hdh_id = smpl.lower_depth_condition_id)))
     LEFT JOIN public.taxon_type ON ((smpl.txn_id = taxon_type.txn_id)))
     LEFT JOIN domains.orgaan ON ((orgaan.org_id = smpl.organ_id)))
     LEFT JOIN public.hoedanigheid_type frac ON ((frac.hdh_id = smpl.fraction_criterium_id)))
     LEFT JOIN public.hoedanigheid_type biota ON ((biota.hdh_id = smpl.biota_criterium_id)))
     LEFT JOIN public.eenheid_type eenh_ud ON ((eenh_ud.eenh_id = smpl.upper_depth_uom_id)))
     LEFT JOIN public.eenheid_type eenh_ld ON ((eenh_ld.eenh_id = smpl.lower_depth_uom_id)))
     LEFT JOIN (((public.sampling_proces smp
     LEFT JOIN domains.monsterbewerkingsmethode mbm ON ((mbm.mbm_id = smp.sampling_method_id)))
     LEFT JOIN domains.bemonsteringsmethode bmm ON ((bmm.bmm_id = smp.sampling_processing_method_id)))
     LEFT JOIN domains.veldapparaat var ON ((var.var_id = smp.var_id))) ON ((smp.smp_id = smpl.smp_id)))
     LEFT JOIN public.related_sampling_feature rel_smpl ON ((smpl.smpl_id = rel_smpl.mnf_id_source)))
     LEFT JOIN public.sample smpl2 ON ((smpl.smpl_id = rel_smpl.mnf_id_target)))
     LEFT JOIN (public.monitoring_object mno_mpn
     JOIN public.namespace_type mpn_nst ON ((mpn_nst.ns_id = mno_mpn.inspire_id_namespace_id))) ON (((mno_mpn.mno_id = rel_smpl.mnf_id_target) AND ((mno_mpn.monitoring_object_type)::text <> 'Sample'::text))))
     LEFT JOIN (public.monitoring_object mno_smpl
     JOIN public.namespace_type smpl_nst ON ((smpl_nst.ns_id = mno_smpl.inspire_id_namespace_id))) ON (((smpl2.smpl_id = mno_smpl.mno_id) AND ((mno_smpl.monitoring_object_type)::text = 'Sample'::text))))
     LEFT JOIN public.rel_samp_feat_role_type rsfr ON ((rsfr.rsfr_id = rel_smpl.role_id)))
     JOIN public.eenheid_type eenh ON ((eenh.eenh_id = obs.numeric_value_uom_id)))
     JOIN opt ON ((opt.opt_id = obs.observed_property_type)))
     LEFT JOIN public.kwaliteitsoordeel_type ON ((obs.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN ((public.observation_process obp
     LEFT JOIN public.waarde_bepalings_methode_type wbpm ON ((wbpm.wbpm_id = obp.wbpm_id)))
     LEFT JOIN (public.obs_process_meetapparaat opm
     JOIN domains.meetapparaat mar ON ((mar.mar_id = opm.mar_id))) ON ((opm.obp_id = obp.obp_id))) ON ((obp.obp_id = obs.result_nature_id)))
     LEFT JOIN bio ON ((bio.obs_id = obs.obs_id)))
     LEFT JOIN (public.observation_context obx
     JOIN public.obs_ctx_role_type ocr ON ((ocr.ocr_id = obx.role_id))) ON ((obx.obs_id_source = obs.obs_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type wbm ON ((wbm.wbm_id = obs.value_processing_method_id)))
     LEFT JOIN ((public.observed_feature obf
     JOIN public.hydro_object hdo ON ((obf.hdo_id = hdo.hdo_id)))
     JOIN public.namespace_type ns_hdo ON ((hdo.inspire_id_namespace = ns_hdo.ns_id))) ON ((obs.obs_id = obf.obs_id)));


--
-- Name: hj_vw_krm_metingen_1_g_backup; Type: VIEW; Schema: krm; Owner: -
--

CREATE VIEW hj_vw_krm_metingen_1_g_backup AS
 WITH bio AS (
         SELECT bio.obs_id,
            gedrag.code AS gedrag_code,
            geslacht.code AS geslacht_code,
            lengte.code AS lengte_code,
            levensstadium.code AS levensstadium_code,
            levensvorm.code AS levensvorm_code,
            verschijning.code AS verschijning_code
           FROM ((((((public.bio_observation bio
             LEFT JOIN public.hoedanigheid_type gedrag ON (((bio.gedrag_id = gedrag.hdh_id) AND (bio.gedrag_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type geslacht ON (((bio.geslacht_id = geslacht.hdh_id) AND (bio.geslacht_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type lengte ON (((bio.lengte_klasse_id = lengte.hdh_id) AND (bio.lengte_klasse_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type levensstadium ON (((bio.levensstadium_id = levensstadium.hdh_id) AND (bio.levensstadium_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type levensvorm ON (((bio.levensvorm_id = levensvorm.hdh_id) AND (bio.levensvorm_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type verschijning ON (((bio.verschijningsvorm_id = verschijning.hdh_id) AND (bio.verschijningsvorm_id IS NOT NULL))))
          WHERE ((bio.gedrag_id IS NOT NULL) AND (bio.geslacht_id IS NOT NULL) AND (bio.lengte_klasse_id IS NOT NULL) AND (bio.levensstadium_id IS NOT NULL) AND (bio.levensvorm_id IS NOT NULL) AND (bio.verschijningsvorm_id IS NOT NULL))
        ), opt AS (
         SELECT opt.opt_id,
            grh.code AS grh_code,
            grh.omschrijving AS grh_omschrijving,
            typ.code AS typ_code,
            typ.omschrijving AS typ_omschrijving,
            txt.omschrijving AS txg_omschrijving,
            chs.code AS chs_code,
            chs.naam AS chs_naam,
            obj.code AS obj_code,
            obj.omschrijving AS obj_omschrijving,
            hdh.code AS hdh_code,
            hdh.omschrijving AS hdh_omschrijving
           FROM ((((((public.observed_property_type opt
             LEFT JOIN public.grootheid_type grh ON (((grh.grh_id = opt.quantity_id) AND (opt.quantity_id IS NOT NULL))))
             LEFT JOIN public.typering_type typ ON (((typ.typ_id = opt.indicator_id) AND (opt.indicator_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type hdh ON (((hdh.hdh_id = opt.hoedanigheid_id) AND (opt.hoedanigheid_id IS NOT NULL))))
             LEFT JOIN ((public.taxa_group txg
             JOIN public.taxa_group_element tge ON ((tge.txg_id = txg.txg_id)))
             JOIN public.taxon_type txt ON ((txt.txn_id = tge.txn_id))) ON (((txg.txg_id = opt.taxa_group_id) AND ((txg.group_type)::text = 'I'::text))))
             LEFT JOIN ((public.substance_group ssg
             JOIN public.substance_group_element ssge ON ((ssge.ssge_id = ssg.ssg_id)))
             JOIN public.chemische_stof_type chs ON ((chs.chs_id = ssge.chs_id))) ON (((ssg.ssg_id = opt.substance_group_id) AND ((ssg.group_type)::text = 'I'::text))))
             LEFT JOIN ((public.object_group ojg
             JOIN public.object_group_element ojge ON ((ojg.ojg_id = ojge.ojg_id)))
             JOIN public.object_type obj ON ((obj.obj_id = ojge.obj_id))) ON (((ojg.ojg_id = opt.object_group_id) AND ((ojg.group_type)::text = 'I'::text))))
        )
 SELECT ((qem.qem_id)::text || (obs.obs_id)::text) AS unique_key,
    (qem.osc_id)::text AS "MonProgId",
    ((('NL'::text || (mno_nst.code)::text) || '_'::text) || (mno.inspire_id_local_id)::text) AS "Meetpunt.Identificatie",
    public.st_x(public.st_transform(public.st_centroid(mno.geometry_etrs89), 4258)) AS "GeometriePunt.X",
    public.st_y(public.st_transform(public.st_centroid(mno.geometry_etrs89), 4258)) AS "GeometriePunt.Y",
        CASE
            WHEN (obf.obs_id IS NOT NULL) THEN (('NL'::text || (ns_hdo.code)::text) || (hdo.inspire_id_local_id)::text)
            ELSE
            CASE
                WHEN ((mno.monitoring_object_type)::text = 'Sample'::text) THEN NULL::text
                ELSE ((('NL'::text || (mno_nst.code)::text) || '_AQUO_COMPARTIMENT:'::text) || (compartiment_type.code)::text)
            END
        END AS "Geoobject.Identificatie",
    NULL::text AS "Monster.Identificatie",
    NULL::text AS "Monster.Namespace",
    NULL::integer AS "Monster.Versie",
    NULL::text AS "Compartiment.Code",
    NULL::text AS "Compartiment.Omschrijving",
    NULL::text AS "Orgaan.Code",
    NULL::text AS "Orgaan.Omschrijving",
    NULL::text AS "Organisme.Naam",
    NULL::numeric AS "BeginDiepte_m",
    NULL::numeric AS "EindDiepte_m",
    NULL::text AS "ReferentieVlak.Code",
    NULL::text AS "Monsterbewerkingsmethode.Code",
    NULL::text AS "Monsterbewerkingsmethode.Omschrijving",
    NULL::text AS "Monsterbewerkingsmethode.codespace",
    NULL::text AS "Bemonsteringsmethode.Code",
    NULL::text AS "Bemonsteringsmethode.Omschrijving",
    NULL::text AS "Bemonsteringsmethode.codespace",
    NULL::text AS "Monstercriterium.Code",
    NULL::text AS "veldapparaat.Code",
    NULL::text AS "veldapparaat.Omschrijving",
    NULL::text AS "veldapparaat.Codespace",
    NULL::text AS "Monsternemingsdatum",
    NULL::text AS "Monsternemingstijd",
    NULL::integer AS "Monster.Tijd_UTCoffset",
    NULL::text AS "ReferentieHorizontaal.code",
    NULL::text AS "Monster.Opmerking",
    NULL::text AS "GerelateerdMonsterobject",
    NULL::text AS "GerelateerdMonsterRol.id",
    obs.obs_id AS "Meetwaarde.Identificatie",
    ('NL'::text || (mno_nst.code)::text) AS "Meetwaarde.Namespace",
    mno.wkrv_id AS "Meetwaarde.Versie",
    opt.grh_code AS "Grootheid.code",
    opt.grh_omschrijving AS "Grootheid.omschrijving",
    opt.typ_code AS "Typering.code",
    opt.typ_omschrijving AS "Typering.omschrijving",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN 'Biotaxon'::text
            WHEN (opt.chs_code IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (opt.obj_code IS NOT NULL) THEN 'Object'::text
            ELSE NULL::text
        END AS "Parameter.Groep",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN (NULL::text)::character varying
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_code
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_code
            ELSE NULL::character varying
        END AS "Parameter.Code",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN opt.txg_omschrijving
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_naam
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_omschrijving
            ELSE NULL::character varying
        END AS "Parameter.Omschrijving",
    eenh.code AS "Eenheid.code",
    eenh.omschrijving AS "Eenheid.omschrijving",
    opt.hdh_code AS "Hoedanigheid.code",
    opt.hdh_omschrijving AS "Hoedanigheid.omschrijving",
    wbm.code AS "Waardebewerkigngsmethode.code",
        CASE
            WHEN (wbm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebewerkigngsmethode.codespace",
    wbpm.code AS "Waardebepalingsmethode.code",
        CASE
            WHEN (wbpm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebepalingsmethode.codespace",
    to_char(obs.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(obs.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(obs.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(obs.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    NULL::integer AS "Meetwaarde.Tijd_UTCoffset",
    to_char(obs.result_time, 'YYYY-MM-DD'::text) AS "Resultaatdatum",
    NULL::integer AS "Resultaat_UTCoffset",
    obs.limit_symbol_id AS "Limietsymbool",
    obs.numeric_value AS "Numeriekewaarde",
    obs.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    mar.code AS "Meetapparaat.code",
        CASE
            WHEN (mar.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Meetapparaat.codespace",
    bio.levensstadium_code AS "Levensstadium.code",
    bio.lengte_code AS "Lengteklasse.code",
    bio.geslacht_code AS "Geslachtcode",
    bio.verschijning_code AS "Verschijningsvorm.code",
    bio.levensvorm_code AS "Levensvorm.code",
    bio.gedrag_code AS "Gedrag.code",
        CASE
            WHEN (obx.obs_id_target IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (mno_nst.code)::text) || '_'::text) || (obx.obs_id_target)::text)
        END AS "GerelateerdObservatieobject",
    ocr.code AS "GerelateerdObservatieobjectRol.id",
    obs.remarks AS "Meetwaarde.Opmerking",
    indicator.additional_description AS "Indicator.EUOmschrijving",
    typi.code AS "Indicator.Code",
    typd.code AS "Descriptor.Code",
    descriptor.additional_description AS "Descriptor.EUOmschrijving"
   FROM (((((((((((((((((((domains.indicator_descriptor indicator
     JOIN public.observed_property_type opti ON ((indicator.opt_id = opti.opt_id)))
     JOIN public.typering_type typi ON ((opti.indicator_id = typi.typ_id)))
     JOIN domains.indicator_descriptor descriptor ON (((indicator.part_of_idd_id = descriptor.idd_id) AND (descriptor.part_of_idd_id IS NULL))))
     JOIN public.observed_property_type optd ON ((descriptor.opt_id = optd.opt_id)))
     JOIN public.typering_type typd ON ((optd.indicator_id = typd.typ_id)))
     JOIN monprog.quality_element_indicator_descriptor qeid ON ((qeid.idd_id = indicator.idd_id)))
     JOIN public.quality_element qem ON ((qem.qem_id = qeid.qem_id)))
     JOIN public.monitoring_object_quality_element moqe ON ((moqe.qem_id = qem.qem_id)))
     JOIN public.quality_element_observation qeo ON ((qeo.qem_id = qem.qem_id)))
     JOIN public.observation obs ON ((qeo.obs_id = obs.obs_id)))
     JOIN ((public.monitoring_object mno
     LEFT JOIN (public.monitored_compartiment
     JOIN public.compartiment_type ON ((monitored_compartiment.cptm_id = compartiment_type.cptm_id))) ON ((monitored_compartiment.mno_id = mno.mno_id)))
     JOIN public.namespace_type mno_nst ON ((mno_nst.ns_id = mno.inspire_id_namespace_id))) ON (((mno.mno_id = obs.mno_id) AND ((mno.monitoring_object_type)::text <> 'Sample'::text) AND (mno.mno_id = moqe.mno_id))))
     JOIN public.eenheid_type eenh ON ((eenh.eenh_id = obs.numeric_value_uom_id)))
     JOIN opt ON ((opt.opt_id = obs.observed_property_type)))
     LEFT JOIN public.kwaliteitsoordeel_type ON ((obs.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN ((public.observation_process obp
     JOIN public.waarde_bepalings_methode_type wbpm ON ((wbpm.wbpm_id = obp.wbpm_id)))
     LEFT JOIN (public.obs_process_meetapparaat opm
     JOIN domains.meetapparaat mar ON ((mar.mar_id = opm.mar_id))) ON ((opm.obp_id = obp.obp_id))) ON ((obp.obp_id = obs.result_nature_id)))
     LEFT JOIN bio ON ((bio.obs_id = obs.obs_id)))
     LEFT JOIN (public.observation_context obx
     JOIN public.obs_ctx_role_type ocr ON ((ocr.ocr_id = obx.role_id))) ON ((obx.obs_id_source = obs.obs_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type wbm ON ((wbm.wbm_id = obs.value_processing_method_id)))
     LEFT JOIN ((public.observed_feature obf
     JOIN public.hydro_object hdo ON ((obf.hdo_id = hdo.hdo_id)))
     JOIN public.namespace_type ns_hdo ON ((hdo.inspire_id_namespace = ns_hdo.ns_id))) ON (((obs.obs_id = obf.obs_id) AND (mno.wkrv_id = hdo.wkrv_id))))
UNION ALL
 SELECT ((qem.osc_id)::text || (obs.obs_id)::text) AS unique_key,
    (qem.osc_id)::text AS "MonProgId",
    ((('NL'::text || (mpn_nst.code)::text) || '_'::text) || (mno_mpn.inspire_id_local_id)::text) AS "Meetpunt.Identificatie",
    public.st_x(public.st_transform(public.st_centroid(mno_mpn.geometry_etrs89), 4258)) AS "GeometriePunt.X",
    public.st_y(public.st_transform(public.st_centroid(mno_mpn.geometry_etrs89), 4258)) AS "GeometriePunt.Y",
        CASE
            WHEN (obf.obs_id IS NOT NULL) THEN ((('NL'::text || (ns_hdo.code)::text) || '_'::text) || (hdo.inspire_id_local_id)::text)
            ELSE NULL::text
        END AS "Geoobject.Identificatie",
    mno_smpl.inspire_id_local_id AS "Monster.Identificatie",
    mpn_nst.code AS "Monster.Namespace",
    mno_smpl.wkrv_id AS "Monster.Versie",
    cmp.code AS "Compartiment.Code",
    cmp.description AS "Compartiment.Omschrijving",
    orgaan.code AS "Orgaan.Code",
    orgaan.description AS "Orgaan.Omschrijving",
    taxon_type.omschrijving AS "Organisme.Naam",
    smpl.upper_depth_value AS "BeginDiepte_m",
    smpl.lower_depth_value AS "EindDiepte_m",
        CASE
            WHEN (hdh_ud.hdh_id IS NULL) THEN
            CASE
                WHEN (hdh_ld.hdh_id IS NULL) THEN (NULL::text)::character varying
                ELSE hdh_ld.code
            END
            ELSE hdh_ud.code
        END AS "ReferentieVlak.Code",
    mbm.code AS "Monsterbewerkingsmethode.Code",
    mbm.description AS "Monsterbewerkingsmethode.Omschrijving",
    mbm.codespace AS "Monsterbewerkingsmethode.codespace",
    bmm.code AS "Bemonsteringsmethode.Code",
    bmm.description AS "Bemonsteringsmethode.Omschrijving",
        CASE
            WHEN (bmm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Bemonsteringsmethode.codespace",
        CASE
            WHEN (frac.code IS NULL) THEN biota.code
            ELSE frac.code
        END AS "Monstercriterium.Code",
    var.code AS "veldapparaat.Code",
    var.description AS "veldapparaat.Omschrijving",
    var.codespace AS "veldapparaat.Codespace",
    to_char(smpl.sampling_time, 'YYYY-MM-DD'::text) AS "Monsternemingsdatum",
    to_char(smpl.sampling_time, 'HH24:MM:SS'::text) AS "Monsternemingstijd",
    NULL::integer AS "Monster.Tijd_UTCoffset",
    'EPSG4258'::text AS "ReferentieHorizontaal.code",
    smpl.remarks AS "Monster.Opmerking",
        CASE
            WHEN (mno_smpl.wkrv_id IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (smpl_nst.code)::text) || '_'::text) || (mno_smpl.inspire_id_local_id)::text)
        END AS "GerelateerdMonsterobject",
        CASE
            WHEN (mno_smpl.wkrv_id IS NULL) THEN (NULL::text)::character varying
            ELSE rsfr.code
        END AS "GerelateerdMonsterRol.id",
    obs.obs_id AS "Meetwaarde.Identificatie",
    ('NL'::text || (mpn_nst.code)::text) AS "Meetwaarde.Namespace",
    mno_mpn.wkrv_id AS "Meetwaarde.Versie",
    opt.grh_code AS "Grootheid.code",
    opt.grh_omschrijving AS "Grootheid.omschrijving",
    opt.typ_code AS "Typering.code",
    opt.typ_omschrijving AS "Typering.omschrijving",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN 'Biotaxon'::text
            WHEN (opt.chs_code IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (opt.obj_code IS NOT NULL) THEN 'Object'::text
            ELSE NULL::text
        END AS "Parameter.Groep",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN (NULL::text)::character varying
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_code
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_code
            ELSE NULL::character varying
        END AS "Parameter.Code",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN opt.txg_omschrijving
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_naam
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_omschrijving
            ELSE NULL::character varying
        END AS "Parameter.Omschrijving",
    eenh.code AS "Eenheid.code",
    eenh.omschrijving AS "Eenheid.omschrijving",
    opt.hdh_code AS "Hoedanigheid.code",
    opt.hdh_omschrijving AS "Hoedanigheid.omschrijving",
    wbm.code AS "Waardebewerkigngsmethode.code",
        CASE
            WHEN (wbm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebewerkigngsmethode.codespace",
    wbpm.code AS "Waardebepalingsmethode.code",
        CASE
            WHEN (wbpm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebepalingsmethode.codespace",
    to_char(obs.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(obs.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(obs.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(obs.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    NULL::integer AS "Meetwaarde.Tijd_UTCoffset",
    to_char(obs.result_time, 'YYYY-MM-DD'::text) AS "Resultaatdatum",
    NULL::integer AS "Resultaat_UTCoffset",
    obs.limit_symbol_id AS "Limietsymbool",
    obs.numeric_value AS "Numeriekewaarde",
    obs.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    mar.code AS "Meetapparaat.code",
        CASE
            WHEN (mar.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Meetapparaat.codespace",
    bio.levensstadium_code AS "Levensstadium.code",
    bio.lengte_code AS "Lengteklasse.code",
    bio.geslacht_code AS "Geslachtcode",
    bio.verschijning_code AS "Verschijningsvorm.code",
    bio.levensvorm_code AS "Levensvorm.code",
    bio.gedrag_code AS "Gedrag.code",
        CASE
            WHEN (obx.obs_id_target IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (mpn_nst.code)::text) || '_'::text) || (obx.obs_id_target)::text)
        END AS "GerelateerdObservatieobject",
    ocr.code AS "GerelateerdObservatieobjectRol.id",
    obs.remarks AS "Meetwaarde.Opmerking",
    indicator.additional_description AS "Indicator.EUOmschrijving",
    typi.code AS "Indicator.Code",
    typd.code AS "Descriptor.Code",
    descriptor.additional_description AS "Descriptor.EUOmschrijving"
   FROM (((((((((((((((((((((((((((((((((domains.indicator_descriptor indicator
     JOIN public.observed_property_type opti ON ((indicator.opt_id = opti.opt_id)))
     JOIN public.typering_type typi ON ((opti.indicator_id = typi.typ_id)))
     JOIN domains.indicator_descriptor descriptor ON (((indicator.part_of_idd_id = descriptor.idd_id) AND (descriptor.part_of_idd_id IS NULL))))
     JOIN public.observed_property_type optd ON ((descriptor.opt_id = optd.opt_id)))
     JOIN public.typering_type typd ON ((optd.indicator_id = typd.typ_id)))
     JOIN monprog.quality_element_indicator_descriptor qeid ON ((qeid.idd_id = indicator.idd_id)))
     JOIN public.quality_element qem ON ((qem.qem_id = qeid.qem_id)))
     JOIN public.quality_element_sample qes ON ((qem.qem_id = qes.qem_id)))
     JOIN public.sample smpl ON ((smpl.smpl_id = qes.smpl_id)))
     JOIN public.observation obs ON ((smpl.smpl_id = obs.mno_id)))
     JOIN public.compartiment_type cmp ON ((smpl.material_class_id = cmp.cptm_id)))
     LEFT JOIN public.hoedanigheid_type hdh_ud ON ((hdh_ud.hdh_id = smpl.upper_depth_condition_id)))
     LEFT JOIN public.hoedanigheid_type hdh_ld ON ((hdh_ld.hdh_id = smpl.lower_depth_condition_id)))
     LEFT JOIN public.taxon_type ON ((smpl.txn_id = taxon_type.txn_id)))
     LEFT JOIN domains.orgaan ON ((orgaan.org_id = smpl.organ_id)))
     LEFT JOIN public.hoedanigheid_type frac ON ((frac.hdh_id = smpl.fraction_criterium_id)))
     LEFT JOIN public.hoedanigheid_type biota ON ((biota.hdh_id = smpl.biota_criterium_id)))
     LEFT JOIN public.eenheid_type eenh_ud ON ((eenh_ud.eenh_id = smpl.upper_depth_uom_id)))
     LEFT JOIN public.eenheid_type eenh_ld ON ((eenh_ld.eenh_id = smpl.lower_depth_uom_id)))
     LEFT JOIN (((public.sampling_proces smp
     LEFT JOIN domains.monsterbewerkingsmethode mbm ON ((mbm.mbm_id = smp.sampling_method_id)))
     LEFT JOIN domains.bemonsteringsmethode bmm ON ((bmm.bmm_id = smp.sampling_processing_method_id)))
     LEFT JOIN domains.veldapparaat var ON ((var.var_id = smp.var_id))) ON ((smp.smp_id = smpl.smp_id)))
     LEFT JOIN public.related_sampling_feature rel_smpl ON ((smpl.smpl_id = rel_smpl.mnf_id_source)))
     LEFT JOIN public.sample smpl2 ON ((smpl.smpl_id = rel_smpl.mnf_id_target)))
     LEFT JOIN (public.monitoring_object mno_mpn
     JOIN public.namespace_type mpn_nst ON ((mpn_nst.ns_id = mno_mpn.inspire_id_namespace_id))) ON (((mno_mpn.mno_id = rel_smpl.mnf_id_target) AND ((mno_mpn.monitoring_object_type)::text <> 'Sample'::text))))
     LEFT JOIN (public.monitoring_object mno_smpl
     JOIN public.namespace_type smpl_nst ON ((smpl_nst.ns_id = mno_smpl.inspire_id_namespace_id))) ON (((smpl2.smpl_id = mno_smpl.mno_id) AND ((mno_smpl.monitoring_object_type)::text = 'Sample'::text))))
     LEFT JOIN public.rel_samp_feat_role_type rsfr ON ((rsfr.rsfr_id = rel_smpl.role_id)))
     JOIN public.eenheid_type eenh ON ((eenh.eenh_id = obs.numeric_value_uom_id)))
     JOIN opt ON ((opt.opt_id = obs.observed_property_type)))
     LEFT JOIN public.kwaliteitsoordeel_type ON ((obs.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN ((public.observation_process obp
     LEFT JOIN public.waarde_bepalings_methode_type wbpm ON ((wbpm.wbpm_id = obp.wbpm_id)))
     LEFT JOIN (public.obs_process_meetapparaat opm
     JOIN domains.meetapparaat mar ON ((mar.mar_id = opm.mar_id))) ON ((opm.obp_id = obp.obp_id))) ON ((obp.obp_id = obs.result_nature_id)))
     LEFT JOIN bio ON ((bio.obs_id = obs.obs_id)))
     LEFT JOIN (public.observation_context obx
     JOIN public.obs_ctx_role_type ocr ON ((ocr.ocr_id = obx.role_id))) ON ((obx.obs_id_source = obs.obs_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type wbm ON ((wbm.wbm_id = obs.value_processing_method_id)))
     LEFT JOIN ((public.observed_feature obf
     JOIN public.hydro_object hdo ON ((obf.hdo_id = hdo.hdo_id)))
     JOIN public.namespace_type ns_hdo ON ((hdo.inspire_id_namespace = ns_hdo.ns_id))) ON ((obs.obs_id = obf.obs_id)));


--
-- Name: hj_vw_krm_metingen_1a; Type: VIEW; Schema: krm; Owner: -
--

CREATE VIEW hj_vw_krm_metingen_1a AS
 WITH bio AS (
         SELECT bio.obs_id,
            gedrag.code AS gedrag_code,
            geslacht.code AS geslacht_code,
            lengte.code AS lengte_code,
            levensstadium.code AS levensstadium_code,
            levensvorm.code AS levensvorm_code,
            verschijning.code AS verschijning_code
           FROM ((((((public.bio_observation bio
             LEFT JOIN public.hoedanigheid_type gedrag ON (((bio.gedrag_id = gedrag.hdh_id) AND (bio.gedrag_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type geslacht ON (((bio.geslacht_id = geslacht.hdh_id) AND (bio.geslacht_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type lengte ON (((bio.lengte_klasse_id = lengte.hdh_id) AND (bio.lengte_klasse_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type levensstadium ON (((bio.levensstadium_id = levensstadium.hdh_id) AND (bio.levensstadium_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type levensvorm ON (((bio.levensvorm_id = levensvorm.hdh_id) AND (bio.levensvorm_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type verschijning ON (((bio.verschijningsvorm_id = verschijning.hdh_id) AND (bio.verschijningsvorm_id IS NOT NULL))))
          WHERE ((bio.gedrag_id IS NOT NULL) AND (bio.geslacht_id IS NOT NULL) AND (bio.lengte_klasse_id IS NOT NULL) AND (bio.levensstadium_id IS NOT NULL) AND (bio.levensvorm_id IS NOT NULL) AND (bio.verschijningsvorm_id IS NOT NULL))
        ), opt AS (
         SELECT opt.opt_id,
            grh.code AS grh_code,
            grh.omschrijving AS grh_omschrijving,
            typ.code AS typ_code,
            typ.omschrijving AS typ_omschrijving,
            txt.omschrijving AS txg_omschrijving,
            chs.code AS chs_code,
            chs.naam AS chs_naam,
            obj.code AS obj_code,
            obj.omschrijving AS obj_omschrijving,
            hdh.code AS hdh_code,
            hdh.omschrijving AS hdh_omschrijving
           FROM ((((((public.observed_property_type opt
             LEFT JOIN public.grootheid_type grh ON (((grh.grh_id = opt.quantity_id) AND (opt.quantity_id IS NOT NULL))))
             LEFT JOIN public.typering_type typ ON (((typ.typ_id = opt.indicator_id) AND (opt.indicator_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type hdh ON (((hdh.hdh_id = opt.hoedanigheid_id) AND (opt.hoedanigheid_id IS NOT NULL))))
             LEFT JOIN ((public.taxa_group txg
             JOIN public.taxa_group_element tge ON ((tge.txg_id = txg.txg_id)))
             JOIN public.taxon_type txt ON ((txt.txn_id = tge.txn_id))) ON (((txg.txg_id = opt.taxa_group_id) AND ((txg.group_type)::text = 'I'::text))))
             LEFT JOIN ((public.substance_group ssg
             JOIN public.substance_group_element ssge ON ((ssge.ssge_id = ssg.ssg_id)))
             JOIN public.chemische_stof_type chs ON ((chs.chs_id = ssge.chs_id))) ON (((ssg.ssg_id = opt.substance_group_id) AND ((ssg.group_type)::text = 'I'::text))))
             LEFT JOIN ((public.object_group ojg
             JOIN public.object_group_element ojge ON ((ojg.ojg_id = ojge.ojg_id)))
             JOIN public.object_type obj ON ((obj.obj_id = ojge.obj_id))) ON (((ojg.ojg_id = opt.object_group_id) AND ((ojg.group_type)::text = 'I'::text))))
        )
 SELECT ((qem.qem_id)::text || (obs.obs_id)::text) AS unique_key,
    ((('NL'::text || (mno_nst.code)::text) || '_'::text) || (mno.inspire_id_local_id)::text) AS "Meetpunt.Identificatie",
        CASE
            WHEN (obf.obs_id IS NOT NULL) THEN (('NL'::text || (ns_hdo.code)::text) || (hdo.inspire_id_local_id)::text)
            ELSE
            CASE
                WHEN ((mno.monitoring_object_type)::text = 'Sample'::text) THEN NULL::text
                ELSE ((('NL'::text || (mno_nst.code)::text) || '_AQUO_COMPARTIMENT:'::text) || (compartiment_type.code)::text)
            END
        END AS "Geoobject.Identificatie",
    NULL::text AS "Monster.Identificatie",
    NULL::text AS "Monster.Namespace",
    NULL::integer AS "Monster.Versie",
    NULL::text AS "Compartiment.Code",
    NULL::text AS "Compartiment.Omschrijving",
    NULL::text AS "Orgaan.Code",
    NULL::text AS "Orgaan.Omschrijving",
    NULL::text AS "Organisme.Naam",
    NULL::numeric AS "BeginDiepte_m",
    NULL::numeric AS "EindDiepte_m",
    NULL::text AS "ReferentieVlak.Code",
    NULL::text AS "Monsterbewerkingsmethode.Code",
    NULL::text AS "Monsterbewerkingsmethode.Omschrijving",
    NULL::text AS "Monsterbewerkingsmethode.codespace",
    NULL::text AS "Bemonsteringsmethode.Code",
    NULL::text AS "Bemonsteringsmethode.Omschrijving",
    NULL::text AS "Bemonsteringsmethode.codespace",
    NULL::text AS "Monstercriterium.Code",
    NULL::text AS "veldapparaat.Code",
    NULL::text AS "veldapparaat.Omschrijving",
    NULL::text AS "veldapparaat.Codespace",
    NULL::text AS "Monsternemingsdatum",
    NULL::text AS "Monsternemingstijd",
    NULL::integer AS "Monster.Tijd_UTCoffset",
    NULL::text AS "ReferentieHorizontaal.code",
    NULL::text AS "Monster.Opmerking",
    NULL::text AS "GerelateerdMonsterobject",
    NULL::text AS "GerelateerdMonsterRol.id",
    obs.obs_id AS "Meetwaarde.Identificatie",
    ('NL'::text || (mno_nst.code)::text) AS "Meetwaarde.Namespace",
    mno.wkrv_id AS "Meetwaarde.Versie",
    opt.grh_code AS "Grootheid.code",
    opt.grh_omschrijving AS "Grootheid.omschrijving",
    opt.typ_code AS "Typering.code",
    opt.typ_omschrijving AS "Typering.omschrijving",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN 'Biotaxon'::text
            WHEN (opt.chs_code IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (opt.obj_code IS NOT NULL) THEN 'Object'::text
            ELSE NULL::text
        END AS "Parameter.Groep",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN (NULL::text)::character varying
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_code
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_code
            ELSE NULL::character varying
        END AS "Parameter.Code",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN opt.txg_omschrijving
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_naam
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_omschrijving
            ELSE NULL::character varying
        END AS "Parameter.Omschrijving",
    eenh.code AS "Eenheid.code",
    eenh.omschrijving AS "Eenheid.omschrijving",
    opt.hdh_code AS "Hoedanigheid.code",
    opt.hdh_omschrijving AS "Hoedanigheid.omschrijving",
    wbm.code AS "Waardebewerkigngsmethode.code",
        CASE
            WHEN (wbm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebewerkigngsmethode.codespace",
    wbpm.code AS "Waardebepalingsmethode.code",
        CASE
            WHEN (wbpm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebepalingsmethode.codespace",
    to_char(obs.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(obs.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(obs.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(obs.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    NULL::integer AS "Meetwaarde.Tijd_UTCoffset",
    to_char(obs.result_time, 'YYYY-MM-DD'::text) AS "Resultaatdatum",
    NULL::integer AS "Resultaat_UTCoffset",
    obs.limit_symbol_id AS "Limietsymbool",
    obs.numeric_value AS "Numeriekewaarde",
    obs.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    mar.code AS "Meetapparaat.code",
        CASE
            WHEN (mar.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Meetapparaat.codespace",
    bio.levensstadium_code AS "Levensstadium.code",
    bio.lengte_code AS "Lengteklasse.code",
    bio.geslacht_code AS "Geslachtcode",
    bio.verschijning_code AS "Verschijningsvorm.code",
    bio.levensvorm_code AS "Levensvorm.code",
    bio.gedrag_code AS "Gedrag.code",
        CASE
            WHEN (obx.obs_id_target IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (mno_nst.code)::text) || '_'::text) || (obx.obs_id_target)::text)
        END AS "GerelateerdObservatieobject",
    ocr.code AS "GerelateerdObservatieobjectRol.id",
    obs.remarks AS "Meetwaarde.Opmerking",
    indicator.additional_description AS "Indicator.EUOmschrijving",
    typi.code AS "Indicator.Code",
    typd.code AS "Descriptor.Code",
    descriptor.additional_description AS "Descriptor.EUOmschrijving"
   FROM (((((((((((((((((((domains.indicator_descriptor indicator
     JOIN public.observed_property_type opti ON ((indicator.opt_id = opti.opt_id)))
     JOIN public.typering_type typi ON ((opti.indicator_id = typi.typ_id)))
     JOIN domains.indicator_descriptor descriptor ON (((indicator.part_of_idd_id = descriptor.idd_id) AND (descriptor.part_of_idd_id IS NULL))))
     JOIN public.observed_property_type optd ON ((descriptor.opt_id = optd.opt_id)))
     JOIN public.typering_type typd ON ((optd.indicator_id = typd.typ_id)))
     JOIN monprog.quality_element_indicator_descriptor qeid ON ((qeid.idd_id = indicator.idd_id)))
     JOIN public.quality_element qem ON ((qem.qem_id = qeid.qem_id)))
     JOIN public.monitoring_object_quality_element moqe ON ((moqe.qem_id = qem.qem_id)))
     JOIN public.quality_element_observation qeo ON ((qeo.qem_id = qem.qem_id)))
     JOIN public.observation obs ON ((qeo.obs_id = obs.obs_id)))
     JOIN ((public.monitoring_object mno
     LEFT JOIN (public.monitored_compartiment
     JOIN public.compartiment_type ON ((monitored_compartiment.cptm_id = compartiment_type.cptm_id))) ON ((monitored_compartiment.mno_id = mno.mno_id)))
     JOIN public.namespace_type mno_nst ON ((mno_nst.ns_id = mno.inspire_id_namespace_id))) ON (((mno.mno_id = obs.mno_id) AND ((mno.monitoring_object_type)::text <> 'Sample'::text) AND (mno.mno_id = moqe.mno_id))))
     JOIN public.eenheid_type eenh ON ((eenh.eenh_id = obs.numeric_value_uom_id)))
     JOIN opt ON ((opt.opt_id = obs.observed_property_type)))
     LEFT JOIN public.kwaliteitsoordeel_type ON ((obs.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN ((public.observation_process obp
     JOIN public.waarde_bepalings_methode_type wbpm ON ((wbpm.wbpm_id = obp.wbpm_id)))
     LEFT JOIN (public.obs_process_meetapparaat opm
     JOIN domains.meetapparaat mar ON ((mar.mar_id = opm.mar_id))) ON ((opm.obp_id = obp.obp_id))) ON ((obp.obp_id = obs.result_nature_id)))
     LEFT JOIN bio ON ((bio.obs_id = obs.obs_id)))
     LEFT JOIN (public.observation_context obx
     JOIN public.obs_ctx_role_type ocr ON ((ocr.ocr_id = obx.role_id))) ON ((obx.obs_id_source = obs.obs_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type wbm ON ((wbm.wbm_id = obs.value_processing_method_id)))
     LEFT JOIN ((public.observed_feature obf
     JOIN public.hydro_object hdo ON ((obf.hdo_id = hdo.hdo_id)))
     JOIN public.namespace_type ns_hdo ON ((hdo.inspire_id_namespace = ns_hdo.ns_id))) ON (((obs.obs_id = obf.obs_id) AND (mno.wkrv_id = hdo.wkrv_id))))
UNION ALL
 SELECT ((qem.osc_id)::text || (obs.obs_id)::text) AS unique_key,
    ((('NL'::text || (mpn_nst.code)::text) || '_'::text) || (mno_mpn.inspire_id_local_id)::text) AS "Meetpunt.Identificatie",
        CASE
            WHEN (obf.obs_id IS NOT NULL) THEN ((('NL'::text || (ns_hdo.code)::text) || '_'::text) || (hdo.inspire_id_local_id)::text)
            ELSE NULL::text
        END AS "Geoobject.Identificatie",
    mno_smpl.inspire_id_local_id AS "Monster.Identificatie",
    mpn_nst.code AS "Monster.Namespace",
    mno_smpl.wkrv_id AS "Monster.Versie",
    cmp.code AS "Compartiment.Code",
    cmp.description AS "Compartiment.Omschrijving",
    orgaan.code AS "Orgaan.Code",
    orgaan.description AS "Orgaan.Omschrijving",
    taxon_type.omschrijving AS "Organisme.Naam",
    smpl.upper_depth_value AS "BeginDiepte_m",
    smpl.lower_depth_value AS "EindDiepte_m",
        CASE
            WHEN (hdh_ud.hdh_id IS NULL) THEN
            CASE
                WHEN (hdh_ld.hdh_id IS NULL) THEN (NULL::text)::character varying
                ELSE hdh_ld.code
            END
            ELSE hdh_ud.code
        END AS "ReferentieVlak.Code",
    mbm.code AS "Monsterbewerkingsmethode.Code",
    mbm.description AS "Monsterbewerkingsmethode.Omschrijving",
    mbm.codespace AS "Monsterbewerkingsmethode.codespace",
    bmm.code AS "Bemonsteringsmethode.Code",
    bmm.description AS "Bemonsteringsmethode.Omschrijving",
        CASE
            WHEN (bmm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Bemonsteringsmethode.codespace",
        CASE
            WHEN (frac.code IS NULL) THEN biota.code
            ELSE frac.code
        END AS "Monstercriterium.Code",
    var.code AS "veldapparaat.Code",
    var.description AS "veldapparaat.Omschrijving",
    var.codespace AS "veldapparaat.Codespace",
    to_char(smpl.sampling_time, 'YYYY-MM-DD'::text) AS "Monsternemingsdatum",
    to_char(smpl.sampling_time, 'HH24:MM:SS'::text) AS "Monsternemingstijd",
    NULL::integer AS "Monster.Tijd_UTCoffset",
    'EPSG4258'::text AS "ReferentieHorizontaal.code",
    smpl.remarks AS "Monster.Opmerking",
        CASE
            WHEN (mno_smpl.wkrv_id IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (smpl_nst.code)::text) || '_'::text) || (mno_smpl.inspire_id_local_id)::text)
        END AS "GerelateerdMonsterobject",
        CASE
            WHEN (mno_smpl.wkrv_id IS NULL) THEN (NULL::text)::character varying
            ELSE rsfr.code
        END AS "GerelateerdMonsterRol.id",
    obs.obs_id AS "Meetwaarde.Identificatie",
    ('NL'::text || (mpn_nst.code)::text) AS "Meetwaarde.Namespace",
    mno_mpn.wkrv_id AS "Meetwaarde.Versie",
    opt.grh_code AS "Grootheid.code",
    opt.grh_omschrijving AS "Grootheid.omschrijving",
    opt.typ_code AS "Typering.code",
    opt.typ_omschrijving AS "Typering.omschrijving",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN 'Biotaxon'::text
            WHEN (opt.chs_code IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (opt.obj_code IS NOT NULL) THEN 'Object'::text
            ELSE NULL::text
        END AS "Parameter.Groep",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN (NULL::text)::character varying
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_code
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_code
            ELSE NULL::character varying
        END AS "Parameter.Code",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN opt.txg_omschrijving
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_naam
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_omschrijving
            ELSE NULL::character varying
        END AS "Parameter.Omschrijving",
    eenh.code AS "Eenheid.code",
    eenh.omschrijving AS "Eenheid.omschrijving",
    opt.hdh_code AS "Hoedanigheid.code",
    opt.hdh_omschrijving AS "Hoedanigheid.omschrijving",
    wbm.code AS "Waardebewerkigngsmethode.code",
        CASE
            WHEN (wbm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebewerkigngsmethode.codespace",
    wbpm.code AS "Waardebepalingsmethode.code",
        CASE
            WHEN (wbpm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebepalingsmethode.codespace",
    to_char(obs.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(obs.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(obs.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(obs.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    NULL::integer AS "Meetwaarde.Tijd_UTCoffset",
    to_char(obs.result_time, 'YYYY-MM-DD'::text) AS "Resultaatdatum",
    NULL::integer AS "Resultaat_UTCoffset",
    obs.limit_symbol_id AS "Limietsymbool",
    obs.numeric_value AS "Numeriekewaarde",
    obs.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    mar.code AS "Meetapparaat.code",
        CASE
            WHEN (mar.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Meetapparaat.codespace",
    bio.levensstadium_code AS "Levensstadium.code",
    bio.lengte_code AS "Lengteklasse.code",
    bio.geslacht_code AS "Geslachtcode",
    bio.verschijning_code AS "Verschijningsvorm.code",
    bio.levensvorm_code AS "Levensvorm.code",
    bio.gedrag_code AS "Gedrag.code",
        CASE
            WHEN (obx.obs_id_target IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (mpn_nst.code)::text) || '_'::text) || (obx.obs_id_target)::text)
        END AS "GerelateerdObservatieobject",
    ocr.code AS "GerelateerdObservatieobjectRol.id",
    obs.remarks AS "Meetwaarde.Opmerking",
    indicator.additional_description AS "Indicator.EUOmschrijving",
    typi.code AS "Indicator.Code",
    typd.code AS "Descriptor.Code",
    descriptor.additional_description AS "Descriptor.EUOmschrijving"
   FROM (((((((((((((((((((((((((((((((((domains.indicator_descriptor indicator
     JOIN public.observed_property_type opti ON ((indicator.opt_id = opti.opt_id)))
     JOIN public.typering_type typi ON ((opti.indicator_id = typi.typ_id)))
     JOIN domains.indicator_descriptor descriptor ON (((indicator.part_of_idd_id = descriptor.idd_id) AND (descriptor.part_of_idd_id IS NULL))))
     JOIN public.observed_property_type optd ON ((descriptor.opt_id = optd.opt_id)))
     JOIN public.typering_type typd ON ((optd.indicator_id = typd.typ_id)))
     JOIN monprog.quality_element_indicator_descriptor qeid ON ((qeid.idd_id = indicator.idd_id)))
     JOIN public.quality_element qem ON ((qem.qem_id = qeid.qem_id)))
     JOIN public.quality_element_sample qes ON ((qem.qem_id = qes.qem_id)))
     JOIN public.sample smpl ON ((smpl.smpl_id = qes.smpl_id)))
     JOIN public.observation obs ON ((smpl.smpl_id = obs.mno_id)))
     JOIN public.compartiment_type cmp ON ((smpl.material_class_id = cmp.cptm_id)))
     LEFT JOIN public.hoedanigheid_type hdh_ud ON ((hdh_ud.hdh_id = smpl.upper_depth_condition_id)))
     LEFT JOIN public.hoedanigheid_type hdh_ld ON ((hdh_ld.hdh_id = smpl.lower_depth_condition_id)))
     LEFT JOIN public.taxon_type ON ((smpl.txn_id = taxon_type.txn_id)))
     LEFT JOIN domains.orgaan ON ((orgaan.org_id = smpl.organ_id)))
     LEFT JOIN public.hoedanigheid_type frac ON ((frac.hdh_id = smpl.fraction_criterium_id)))
     LEFT JOIN public.hoedanigheid_type biota ON ((biota.hdh_id = smpl.biota_criterium_id)))
     LEFT JOIN public.eenheid_type eenh_ud ON ((eenh_ud.eenh_id = smpl.upper_depth_uom_id)))
     LEFT JOIN public.eenheid_type eenh_ld ON ((eenh_ld.eenh_id = smpl.lower_depth_uom_id)))
     LEFT JOIN (((public.sampling_proces smp
     LEFT JOIN domains.monsterbewerkingsmethode mbm ON ((mbm.mbm_id = smp.sampling_method_id)))
     LEFT JOIN domains.bemonsteringsmethode bmm ON ((bmm.bmm_id = smp.sampling_processing_method_id)))
     LEFT JOIN domains.veldapparaat var ON ((var.var_id = smp.var_id))) ON ((smp.smp_id = smpl.smp_id)))
     LEFT JOIN public.related_sampling_feature rel_smpl ON ((smpl.smpl_id = rel_smpl.mnf_id_source)))
     LEFT JOIN public.sample smpl2 ON ((smpl.smpl_id = rel_smpl.mnf_id_target)))
     LEFT JOIN (public.monitoring_object mno_mpn
     JOIN public.namespace_type mpn_nst ON ((mpn_nst.ns_id = mno_mpn.inspire_id_namespace_id))) ON (((mno_mpn.mno_id = rel_smpl.mnf_id_target) AND ((mno_mpn.monitoring_object_type)::text <> 'Sample'::text))))
     LEFT JOIN (public.monitoring_object mno_smpl
     JOIN public.namespace_type smpl_nst ON ((smpl_nst.ns_id = mno_smpl.inspire_id_namespace_id))) ON (((smpl2.smpl_id = mno_smpl.mno_id) AND ((mno_smpl.monitoring_object_type)::text = 'Sample'::text))))
     LEFT JOIN public.rel_samp_feat_role_type rsfr ON ((rsfr.rsfr_id = rel_smpl.role_id)))
     JOIN public.eenheid_type eenh ON ((eenh.eenh_id = obs.numeric_value_uom_id)))
     JOIN opt ON ((opt.opt_id = obs.observed_property_type)))
     LEFT JOIN public.kwaliteitsoordeel_type ON ((obs.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN ((public.observation_process obp
     LEFT JOIN public.waarde_bepalings_methode_type wbpm ON ((wbpm.wbpm_id = obp.wbpm_id)))
     LEFT JOIN (public.obs_process_meetapparaat opm
     JOIN domains.meetapparaat mar ON ((mar.mar_id = opm.mar_id))) ON ((opm.obp_id = obp.obp_id))) ON ((obp.obp_id = obs.result_nature_id)))
     LEFT JOIN bio ON ((bio.obs_id = obs.obs_id)))
     LEFT JOIN (public.observation_context obx
     JOIN public.obs_ctx_role_type ocr ON ((ocr.ocr_id = obx.role_id))) ON ((obx.obs_id_source = obs.obs_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type wbm ON ((wbm.wbm_id = obs.value_processing_method_id)))
     LEFT JOIN ((public.observed_feature obf
     JOIN public.hydro_object hdo ON ((obf.hdo_id = hdo.hdo_id)))
     JOIN public.namespace_type ns_hdo ON ((hdo.inspire_id_namespace = ns_hdo.ns_id))) ON ((obs.obs_id = obf.obs_id)));



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

CREATE TABLE observing_capability_observation (
    ocbs_id integer NOT NULL,
    obs_id integer NOT NULL,
    osc_id integer NOT NULL,
    is_approved boolean DEFAULT false NOT NULL,
    last_change_time timestamp without time zone NOT NULL
);



--
-- Name: hj_vw_krm_metingen_2; Type: VIEW; Schema: krm; Owner: -
--

CREATE VIEW hj_vw_krm_metingen_2 AS
 WITH bio AS (
         SELECT bio.obs_id,
            gedrag.code AS gedrag_code,
            geslacht.code AS geslacht_code,
            lengte.code AS lengte_code,
            levensstadium.code AS levensstadium_code,
            levensvorm.code AS levensvorm_code,
            verschijning.code AS verschijning_code
           FROM ((((((public.bio_observation bio
             LEFT JOIN public.hoedanigheid_type gedrag ON (((bio.gedrag_id = gedrag.hdh_id) AND (bio.gedrag_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type geslacht ON (((bio.geslacht_id = geslacht.hdh_id) AND (bio.geslacht_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type lengte ON (((bio.lengte_klasse_id = lengte.hdh_id) AND (bio.lengte_klasse_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type levensstadium ON (((bio.levensstadium_id = levensstadium.hdh_id) AND (bio.levensstadium_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type levensvorm ON (((bio.levensvorm_id = levensvorm.hdh_id) AND (bio.levensvorm_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type verschijning ON (((bio.verschijningsvorm_id = verschijning.hdh_id) AND (bio.verschijningsvorm_id IS NOT NULL))))
          WHERE ((bio.gedrag_id IS NOT NULL) AND (bio.geslacht_id IS NOT NULL) AND (bio.lengte_klasse_id IS NOT NULL) AND (bio.levensstadium_id IS NOT NULL) AND (bio.levensvorm_id IS NOT NULL) AND (bio.verschijningsvorm_id IS NOT NULL))
        ), opt AS (
         SELECT opt.opt_id,
            grh.code AS grh_code,
            grh.omschrijving AS grh_omschrijving,
            typ.code AS typ_code,
            typ.omschrijving AS typ_omschrijving,
            txt.omschrijving AS txg_omschrijving,
            chs.code AS chs_code,
            chs.naam AS chs_naam,
            obj.code AS obj_code,
            obj.omschrijving AS obj_omschrijving,
            hdh.code AS hdh_code,
            hdh.omschrijving AS hdh_omschrijving
           FROM ((((((public.observed_property_type opt
             LEFT JOIN public.grootheid_type grh ON (((grh.grh_id = opt.quantity_id) AND (opt.quantity_id IS NOT NULL))))
             LEFT JOIN public.typering_type typ ON (((typ.typ_id = opt.indicator_id) AND (opt.indicator_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type hdh ON (((hdh.hdh_id = opt.hoedanigheid_id) AND (opt.hoedanigheid_id IS NOT NULL))))
             LEFT JOIN ((public.taxa_group txg
             JOIN public.taxa_group_element tge ON ((tge.txg_id = txg.txg_id)))
             JOIN public.taxon_type txt ON ((txt.txn_id = tge.txn_id))) ON (((txg.txg_id = opt.taxa_group_id) AND ((txg.group_type)::text = 'I'::text))))
             LEFT JOIN ((public.substance_group ssg
             JOIN public.substance_group_element ssge ON ((ssge.ssge_id = ssg.ssg_id)))
             JOIN public.chemische_stof_type chs ON ((chs.chs_id = ssge.chs_id))) ON (((ssg.ssg_id = opt.substance_group_id) AND ((ssg.group_type)::text = 'I'::text))))
             LEFT JOIN ((public.object_group ojg
             JOIN public.object_group_element ojge ON ((ojg.ojg_id = ojge.ojg_id)))
             JOIN public.object_type obj ON ((obj.obj_id = ojge.obj_id))) ON (((ojg.ojg_id = opt.object_group_id) AND ((ojg.group_type)::text = 'I'::text))))
        )
 SELECT ((osc.osc_id)::text || (obs.obs_id)::text) AS unique_key,
    ((('NL'::text || (mno_nst.code)::text) || '_'::text) || (mno.inspire_id_local_id)::text) AS "Meetpunt.Identificatie",
        CASE
            WHEN (obf.obs_id IS NOT NULL) THEN (('NL'::text || (ns_hdo.code)::text) || (hdo.inspire_id_local_id)::text)
            ELSE
            CASE
                WHEN ((mno.monitoring_object_type)::text = 'Sample'::text) THEN NULL::text
                ELSE ((('NL'::text || (mno_nst.code)::text) || '_AQUO_COMPARTIMENT:'::text) || (compartiment_type.code)::text)
            END
        END AS "Geoobject.Identificatie",
    NULL::text AS "Monster.Identificatie",
    NULL::text AS "Monster.Namespace",
    NULL::integer AS "Monster.Versie",
    NULL::text AS "Compartiment.Code",
    NULL::text AS "Compartiment.Omschrijving",
    NULL::text AS "Orgaan.Code",
    NULL::text AS "Orgaan.Omschrijving",
    NULL::text AS "Organisme.Naam",
    NULL::numeric AS "BeginDiepte_m",
    NULL::numeric AS "EindDiepte_m",
    NULL::text AS "ReferentieVlak.Code",
    NULL::text AS "Monsterbewerkingsmethode.Code",
    NULL::text AS "Monsterbewerkingsmethode.Omschrijving",
    NULL::text AS "Monsterbewerkingsmethode.codespace",
    NULL::text AS "Bemonsteringsmethode.Code",
    NULL::text AS "Bemonsteringsmethode.Omschrijving",
    NULL::text AS "Bemonsteringsmethode.codespace",
    NULL::text AS "Monstercriterium.Code",
    NULL::text AS "veldapparaat.Code",
    NULL::text AS "veldapparaat.Omschrijving",
    NULL::text AS "veldapparaat.Codespace",
    NULL::text AS "Monsternemingsdatum",
    NULL::text AS "Monsternemingstijd",
    NULL::integer AS "Monster.Tijd_UTCoffset",
    NULL::text AS "ReferentieHorizontaal.code",
    NULL::text AS "Monster.Opmerking",
    NULL::text AS "GerelateerdMonsterobject",
    NULL::text AS "GerelateerdMonsterRol.id",
    obs.obs_id AS "Meetwaarde.Identificatie",
    ('NL'::text || (mno_nst.code)::text) AS "Meetwaarde.Namespace",
    mno.wkrv_id AS "Meetwaarde.Versie",
    opt.grh_code AS "Grootheid.code",
    opt.grh_omschrijving AS "Grootheid.omschrijving",
    opt.typ_code AS "Typering.code",
    opt.typ_omschrijving AS "Typering.omschrijving",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN 'Biotaxon'::text
            WHEN (opt.chs_code IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (opt.obj_code IS NOT NULL) THEN 'Object'::text
            ELSE NULL::text
        END AS "Parameter.Groep",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN (NULL::text)::character varying
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_code
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_code
            ELSE NULL::character varying
        END AS "Parameter.Code",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN opt.txg_omschrijving
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_naam
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_omschrijving
            ELSE NULL::character varying
        END AS "Parameter.Omschrijving",
    eenh.code AS "Eenheid.code",
    eenh.omschrijving AS "Eenheid.omschrijving",
    opt.hdh_code AS "Hoedanigheid.code",
    opt.hdh_omschrijving AS "Hoedanigheid.omschrijving",
    wbm.code AS "Waardebewerkigngsmethode.code",
        CASE
            WHEN (wbm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebewerkigngsmethode.codespace",
    wbpm.code AS "Waardebepalingsmethode.code",
        CASE
            WHEN (wbpm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebepalingsmethode.codespace",
    to_char(obs.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(obs.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(obs.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(obs.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    NULL::integer AS "Meetwaarde.Tijd_UTCoffset",
    to_char(obs.result_time, 'YYYY-MM-DD'::text) AS "Resultaatdatum",
    NULL::integer AS "Resultaat_UTCoffset",
    obs.limit_symbol_id AS "Limietsymbool",
    obs.numeric_value AS "Numeriekewaarde",
    obs.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    mar.code AS "Meetapparaat.code",
        CASE
            WHEN (mar.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Meetapparaat.codespace",
    bio.levensstadium_code AS "Levensstadium.code",
    bio.lengte_code AS "Lengteklasse.code",
    bio.geslacht_code AS "Geslachtcode",
    bio.verschijning_code AS "Verschijningsvorm.code",
    bio.levensvorm_code AS "Levensvorm.code",
    bio.gedrag_code AS "Gedrag.code",
        CASE
            WHEN (obx.obs_id_target IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (mno_nst.code)::text) || '_'::text) || (obx.obs_id_target)::text)
        END AS "GerelateerdObservatieobject",
    ocr.code AS "GerelateerdObservatieobjectRol.id",
    obs.remarks AS "Meetwaarde.Opmerking",
    indicator.additional_description AS "Indicator.EUOmschrijving",
    typi.code AS "Indicator.Code",
    typd.code AS "Descriptor.Code",
    descriptor.additional_description AS "Descriptor.EUOmschrijving"
   FROM ((((((((((((((((((((domains.indicator_descriptor indicator
     JOIN public.observed_property_type opti ON ((indicator.opt_id = opti.opt_id)))
     JOIN public.typering_type typi ON ((opti.indicator_id = typi.typ_id)))
     JOIN domains.indicator_descriptor descriptor ON (((indicator.part_of_idd_id = descriptor.idd_id) AND (descriptor.part_of_idd_id IS NULL))))
     JOIN public.observed_property_type optd ON ((descriptor.opt_id = optd.opt_id)))
     JOIN public.typering_type typd ON ((optd.indicator_id = typd.typ_id)))
     JOIN monprog.quality_element_indicator_descriptor qeid ON ((qeid.idd_id = indicator.idd_id)))
     JOIN public.quality_element qem ON ((qem.qem_id = qeid.qem_id)))
     JOIN public.monitoring_object_quality_element moqe ON ((moqe.qem_id = qem.qem_id)))
     JOIN public.observing_capability osc ON ((qem.osc_id = osc.osc_id)))
     JOIN public.observing_capability_observation oco ON ((oco.osc_id = osc.osc_id)))
     JOIN public.observation obs ON ((oco.obs_id = obs.obs_id)))
     JOIN ((public.monitoring_object mno
     LEFT JOIN (public.monitored_compartiment
     JOIN public.compartiment_type ON ((monitored_compartiment.cptm_id = compartiment_type.cptm_id))) ON ((monitored_compartiment.mno_id = mno.mno_id)))
     JOIN public.namespace_type mno_nst ON ((mno_nst.ns_id = mno.inspire_id_namespace_id))) ON (((mno.mno_id = obs.mno_id) AND ((mno.monitoring_object_type)::text <> 'Sample'::text) AND (moqe.mno_id = mno.mno_id))))
     JOIN public.eenheid_type eenh ON ((eenh.eenh_id = obs.numeric_value_uom_id)))
     JOIN opt ON ((opt.opt_id = obs.observed_property_type)))
     LEFT JOIN public.kwaliteitsoordeel_type ON ((obs.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN ((public.observation_process obp
     JOIN public.waarde_bepalings_methode_type wbpm ON ((wbpm.wbpm_id = obp.wbpm_id)))
     LEFT JOIN (public.obs_process_meetapparaat opm
     JOIN domains.meetapparaat mar ON ((mar.mar_id = opm.mar_id))) ON ((opm.obp_id = obp.obp_id))) ON ((obp.obp_id = obs.result_nature_id)))
     LEFT JOIN bio ON ((bio.obs_id = obs.obs_id)))
     LEFT JOIN (public.observation_context obx
     JOIN public.obs_ctx_role_type ocr ON ((ocr.ocr_id = obx.role_id))) ON ((obx.obs_id_source = obs.obs_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type wbm ON ((wbm.wbm_id = obs.value_processing_method_id)))
     LEFT JOIN ((public.observed_feature obf
     JOIN public.hydro_object hdo ON ((obf.hdo_id = hdo.hdo_id)))
     JOIN public.namespace_type ns_hdo ON ((hdo.inspire_id_namespace = ns_hdo.ns_id))) ON (((obs.obs_id = obf.obs_id) AND (mno.wkrv_id = hdo.wkrv_id))))
UNION ALL
 SELECT ((qem.osc_id)::text || (obs.obs_id)::text) AS unique_key,
    ((('NL'::text || (mpn_nst.code)::text) || '_'::text) || (mno_mpn.inspire_id_local_id)::text) AS "Meetpunt.Identificatie",
        CASE
            WHEN (obf.obs_id IS NOT NULL) THEN ((('NL'::text || (ns_hdo.code)::text) || '_'::text) || (hdo.inspire_id_local_id)::text)
            ELSE NULL::text
        END AS "Geoobject.Identificatie",
    mno_smpl.inspire_id_local_id AS "Monster.Identificatie",
    mpn_nst.code AS "Monster.Namespace",
    mno_smpl.wkrv_id AS "Monster.Versie",
    cmp.code AS "Compartiment.Code",
    cmp.description AS "Compartiment.Omschrijving",
    orgaan.code AS "Orgaan.Code",
    orgaan.description AS "Orgaan.Omschrijving",
    taxon_type.omschrijving AS "Organisme.Naam",
    smpl.upper_depth_value AS "BeginDiepte_m",
    smpl.lower_depth_value AS "EindDiepte_m",
        CASE
            WHEN (hdh_ud.hdh_id IS NULL) THEN
            CASE
                WHEN (hdh_ld.hdh_id IS NULL) THEN (NULL::text)::character varying
                ELSE hdh_ld.code
            END
            ELSE hdh_ud.code
        END AS "ReferentieVlak.Code",
    mbm.code AS "Monsterbewerkingsmethode.Code",
    mbm.description AS "Monsterbewerkingsmethode.Omschrijving",
    mbm.codespace AS "Monsterbewerkingsmethode.codespace",
    bmm.code AS "Bemonsteringsmethode.Code",
    bmm.description AS "Bemonsteringsmethode.Omschrijving",
        CASE
            WHEN (bmm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Bemonsteringsmethode.codespace",
        CASE
            WHEN (frac.code IS NULL) THEN biota.code
            ELSE frac.code
        END AS "Monstercriterium.Code",
    var.code AS "veldapparaat.Code",
    var.description AS "veldapparaat.Omschrijving",
    var.codespace AS "veldapparaat.Codespace",
    to_char(smpl.sampling_time, 'YYYY-MM-DD'::text) AS "Monsternemingsdatum",
    to_char(smpl.sampling_time, 'HH24:MM:SS'::text) AS "Monsternemingstijd",
    NULL::integer AS "Monster.Tijd_UTCoffset",
    'EPSG4258'::text AS "ReferentieHorizontaal.code",
    smpl.remarks AS "Monster.Opmerking",
        CASE
            WHEN (mno_smpl.wkrv_id IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (smpl_nst.code)::text) || '_'::text) || (mno_smpl.inspire_id_local_id)::text)
        END AS "GerelateerdMonsterobject",
        CASE
            WHEN (mno_smpl.wkrv_id IS NULL) THEN (NULL::text)::character varying
            ELSE rsfr.code
        END AS "GerelateerdMonsterRol.id",
    obs.obs_id AS "Meetwaarde.Identificatie",
    ('NL'::text || (mpn_nst.code)::text) AS "Meetwaarde.Namespace",
    mno_mpn.wkrv_id AS "Meetwaarde.Versie",
    opt.grh_code AS "Grootheid.code",
    opt.grh_omschrijving AS "Grootheid.omschrijving",
    opt.typ_code AS "Typering.code",
    opt.typ_omschrijving AS "Typering.omschrijving",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN 'Biotaxon'::text
            WHEN (opt.chs_code IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (opt.obj_code IS NOT NULL) THEN 'Object'::text
            ELSE NULL::text
        END AS "Parameter.Groep",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN (NULL::text)::character varying
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_code
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_code
            ELSE NULL::character varying
        END AS "Parameter.Code",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN opt.txg_omschrijving
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_naam
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_omschrijving
            ELSE NULL::character varying
        END AS "Parameter.Omschrijving",
    eenh.code AS "Eenheid.code",
    eenh.omschrijving AS "Eenheid.omschrijving",
    opt.hdh_code AS "Hoedanigheid.code",
    opt.hdh_omschrijving AS "Hoedanigheid.omschrijving",
    wbm.code AS "Waardebewerkigngsmethode.code",
        CASE
            WHEN (wbm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebewerkigngsmethode.codespace",
    wbpm.code AS "Waardebepalingsmethode.code",
        CASE
            WHEN (wbpm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebepalingsmethode.codespace",
    to_char(obs.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(obs.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(obs.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(obs.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    NULL::integer AS "Meetwaarde.Tijd_UTCoffset",
    to_char(obs.result_time, 'YYYY-MM-DD'::text) AS "Resultaatdatum",
    NULL::integer AS "Resultaat_UTCoffset",
    obs.limit_symbol_id AS "Limietsymbool",
    obs.numeric_value AS "Numeriekewaarde",
    obs.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    mar.code AS "Meetapparaat.code",
        CASE
            WHEN (mar.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Meetapparaat.codespace",
    bio.levensstadium_code AS "Levensstadium.code",
    bio.lengte_code AS "Lengteklasse.code",
    bio.geslacht_code AS "Geslachtcode",
    bio.verschijning_code AS "Verschijningsvorm.code",
    bio.levensvorm_code AS "Levensvorm.code",
    bio.gedrag_code AS "Gedrag.code",
        CASE
            WHEN (obx.obs_id_target IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (mpn_nst.code)::text) || '_'::text) || (obx.obs_id_target)::text)
        END AS "GerelateerdObservatieobject",
    ocr.code AS "GerelateerdObservatieobjectRol.id",
    obs.remarks AS "Meetwaarde.Opmerking",
    indicator.additional_description AS "Indicator.EUOmschrijving",
    typi.code AS "Indicator.Code",
    typd.code AS "Descriptor.Code",
    descriptor.additional_description AS "Descriptor.EUOmschrijving"
   FROM (((((((((((((((((((((((((((((((((((domains.indicator_descriptor indicator
     JOIN public.observed_property_type opti ON ((indicator.opt_id = opti.opt_id)))
     JOIN public.typering_type typi ON ((opti.indicator_id = typi.typ_id)))
     JOIN domains.indicator_descriptor descriptor ON (((indicator.part_of_idd_id = descriptor.idd_id) AND (descriptor.part_of_idd_id IS NULL))))
     JOIN public.observed_property_type optd ON ((descriptor.opt_id = optd.opt_id)))
     JOIN public.typering_type typd ON ((optd.indicator_id = typd.typ_id)))
     JOIN monprog.quality_element_indicator_descriptor qeid ON ((qeid.idd_id = indicator.idd_id)))
     JOIN public.quality_element qem ON ((qem.qem_id = qeid.qem_id)))
     JOIN public.monitoring_object_quality_element moqe ON ((moqe.qem_id = qem.qem_id)))
     JOIN public.observing_capability osc ON ((osc.osc_id = qem.osc_id)))
     JOIN public.observing_capability_observation oco ON ((oco.osc_id = osc.osc_id)))
     JOIN public.observation obs ON ((oco.obs_id = obs.obs_id)))
     JOIN public.sample smpl ON ((smpl.smpl_id = obs.mno_id)))
     JOIN public.compartiment_type cmp ON ((smpl.material_class_id = cmp.cptm_id)))
     LEFT JOIN public.hoedanigheid_type hdh_ud ON ((hdh_ud.hdh_id = smpl.upper_depth_condition_id)))
     LEFT JOIN public.hoedanigheid_type hdh_ld ON ((hdh_ld.hdh_id = smpl.lower_depth_condition_id)))
     LEFT JOIN public.taxon_type ON ((smpl.txn_id = taxon_type.txn_id)))
     LEFT JOIN domains.orgaan ON ((orgaan.org_id = smpl.organ_id)))
     LEFT JOIN public.hoedanigheid_type frac ON ((frac.hdh_id = smpl.fraction_criterium_id)))
     LEFT JOIN public.hoedanigheid_type biota ON ((biota.hdh_id = smpl.biota_criterium_id)))
     LEFT JOIN public.eenheid_type eenh_ud ON ((eenh_ud.eenh_id = smpl.upper_depth_uom_id)))
     LEFT JOIN public.eenheid_type eenh_ld ON ((eenh_ld.eenh_id = smpl.lower_depth_uom_id)))
     LEFT JOIN (((public.sampling_proces smp
     LEFT JOIN domains.monsterbewerkingsmethode mbm ON ((mbm.mbm_id = smp.sampling_method_id)))
     LEFT JOIN domains.bemonsteringsmethode bmm ON ((bmm.bmm_id = smp.sampling_processing_method_id)))
     LEFT JOIN domains.veldapparaat var ON ((var.var_id = smp.var_id))) ON ((smp.smp_id = smpl.smp_id)))
     LEFT JOIN public.related_sampling_feature rel_smpl ON ((smpl.smpl_id = rel_smpl.mnf_id_source)))
     LEFT JOIN public.sample smpl2 ON ((smpl.smpl_id = rel_smpl.mnf_id_target)))
     LEFT JOIN (public.monitoring_object mno_mpn
     JOIN public.namespace_type mpn_nst ON ((mpn_nst.ns_id = mno_mpn.inspire_id_namespace_id))) ON (((mno_mpn.mno_id = rel_smpl.mnf_id_target) AND ((mno_mpn.monitoring_object_type)::text <> 'Sample'::text) AND (moqe.mno_id = mno_mpn.mno_id))))
     LEFT JOIN (public.monitoring_object mno_smpl
     JOIN public.namespace_type smpl_nst ON ((smpl_nst.ns_id = mno_smpl.inspire_id_namespace_id))) ON (((smpl2.smpl_id = mno_smpl.mno_id) AND ((mno_smpl.monitoring_object_type)::text = 'Sample'::text))))
     LEFT JOIN public.rel_samp_feat_role_type rsfr ON ((rsfr.rsfr_id = rel_smpl.role_id)))
     JOIN public.eenheid_type eenh ON ((eenh.eenh_id = obs.numeric_value_uom_id)))
     JOIN opt ON ((opt.opt_id = obs.observed_property_type)))
     LEFT JOIN public.kwaliteitsoordeel_type ON ((obs.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN ((public.observation_process obp
     LEFT JOIN public.waarde_bepalings_methode_type wbpm ON ((wbpm.wbpm_id = obp.wbpm_id)))
     LEFT JOIN (public.obs_process_meetapparaat opm
     JOIN domains.meetapparaat mar ON ((mar.mar_id = opm.mar_id))) ON ((opm.obp_id = obp.obp_id))) ON ((obp.obp_id = obs.result_nature_id)))
     LEFT JOIN bio ON ((bio.obs_id = obs.obs_id)))
     LEFT JOIN (public.observation_context obx
     JOIN public.obs_ctx_role_type ocr ON ((ocr.ocr_id = obx.role_id))) ON ((obx.obs_id_source = obs.obs_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type wbm ON ((wbm.wbm_id = obs.value_processing_method_id)))
     LEFT JOIN ((public.observed_feature obf
     JOIN public.hydro_object hdo ON ((obf.hdo_id = hdo.hdo_id)))
     JOIN public.namespace_type ns_hdo ON ((hdo.inspire_id_namespace = ns_hdo.ns_id))) ON ((obs.obs_id = obf.obs_id)));


--
-- Name: hj_vw_krm_metingen_g; Type: VIEW; Schema: krm; Owner: -
--

CREATE VIEW hj_vw_krm_metingen_g AS
 WITH bio AS (
         SELECT bio.obs_id,
            gedrag.code AS gedrag_code,
            geslacht.code AS geslacht_code,
            lengte.code AS lengte_code,
            levensstadium.code AS levensstadium_code,
            levensvorm.code AS levensvorm_code,
            verschijning.code AS verschijning_code
           FROM ((((((public.bio_observation bio
             LEFT JOIN public.hoedanigheid_type gedrag ON (((bio.gedrag_id = gedrag.hdh_id) AND (bio.gedrag_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type geslacht ON (((bio.geslacht_id = geslacht.hdh_id) AND (bio.geslacht_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type lengte ON (((bio.lengte_klasse_id = lengte.hdh_id) AND (bio.lengte_klasse_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type levensstadium ON (((bio.levensstadium_id = levensstadium.hdh_id) AND (bio.levensstadium_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type levensvorm ON (((bio.levensvorm_id = levensvorm.hdh_id) AND (bio.levensvorm_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type verschijning ON (((bio.verschijningsvorm_id = verschijning.hdh_id) AND (bio.verschijningsvorm_id IS NOT NULL))))
          WHERE ((bio.gedrag_id IS NOT NULL) AND (bio.geslacht_id IS NOT NULL) AND (bio.lengte_klasse_id IS NOT NULL) AND (bio.levensstadium_id IS NOT NULL) AND (bio.levensvorm_id IS NOT NULL) AND (bio.verschijningsvorm_id IS NOT NULL))
        ), opt AS (
         SELECT opt.opt_id,
            grh.code AS grh_code,
            grh.omschrijving AS grh_omschrijving,
            typ.code AS typ_code,
            typ.omschrijving AS typ_omschrijving,
            txt.omschrijving AS txg_omschrijving,
            chs.code AS chs_code,
            chs.naam AS chs_naam,
            obj.code AS obj_code,
            obj.omschrijving AS obj_omschrijving,
            hdh.code AS hdh_code,
            hdh.omschrijving AS hdh_omschrijving
           FROM ((((((public.observed_property_type opt
             LEFT JOIN public.grootheid_type grh ON (((grh.grh_id = opt.quantity_id) AND (opt.quantity_id IS NOT NULL))))
             LEFT JOIN public.typering_type typ ON (((typ.typ_id = opt.indicator_id) AND (opt.indicator_id IS NOT NULL))))
             LEFT JOIN public.hoedanigheid_type hdh ON (((hdh.hdh_id = opt.hoedanigheid_id) AND (opt.hoedanigheid_id IS NOT NULL))))
             LEFT JOIN ((public.taxa_group txg
             JOIN public.taxa_group_element tge ON ((tge.txg_id = txg.txg_id)))
             JOIN public.taxon_type txt ON ((txt.txn_id = tge.txn_id))) ON (((txg.txg_id = opt.taxa_group_id) AND ((txg.group_type)::text = 'I'::text))))
             LEFT JOIN ((public.substance_group ssg
             JOIN public.substance_group_element ssge ON ((ssge.ssge_id = ssg.ssg_id)))
             JOIN public.chemische_stof_type chs ON ((chs.chs_id = ssge.chs_id))) ON (((ssg.ssg_id = opt.substance_group_id) AND ((ssg.group_type)::text = 'I'::text))))
             LEFT JOIN ((public.object_group ojg
             JOIN public.object_group_element ojge ON ((ojg.ojg_id = ojge.ojg_id)))
             JOIN public.object_type obj ON ((obj.obj_id = ojge.obj_id))) ON (((ojg.ojg_id = opt.object_group_id) AND ((ojg.group_type)::text = 'I'::text))))
        )
 SELECT ((qem.qem_id)::text || (obs.obs_id)::text) AS unique_key,
    ((('NL'::text || (mno_nst.code)::text) || '_'::text) || (mno.inspire_id_local_id)::text) AS "Meetpunt.Identificatie",
        CASE
            WHEN (obf.obs_id IS NOT NULL) THEN (('NL'::text || (ns_hdo.code)::text) || (hdo.inspire_id_local_id)::text)
            ELSE
            CASE
                WHEN ((mno.monitoring_object_type)::text = 'Sample'::text) THEN NULL::text
                ELSE ((('NL'::text || (mno_nst.code)::text) || '_AQUO_COMPARTIMENT:'::text) || (compartiment_type.code)::text)
            END
        END AS "Geoobject.Identificatie",
    NULL::text AS "Monster.Identificatie",
    NULL::text AS "Monster.Namespace",
    NULL::integer AS "Monster.Versie",
    NULL::text AS "Compartiment.Code",
    NULL::text AS "Compartiment.Omschrijving",
    NULL::text AS "Orgaan.Code",
    NULL::text AS "Orgaan.Omschrijving",
    NULL::text AS "Organisme.Naam",
    NULL::numeric AS "BeginDiepte_m",
    NULL::numeric AS "EindDiepte_m",
    NULL::text AS "ReferentieVlak.Code",
    NULL::text AS "Monsterbewerkingsmethode.Code",
    NULL::text AS "Monsterbewerkingsmethode.Omschrijving",
    NULL::text AS "Monsterbewerkingsmethode.codespace",
    NULL::text AS "Bemonsteringsmethode.Code",
    NULL::text AS "Bemonsteringsmethode.Omschrijving",
    NULL::text AS "Bemonsteringsmethode.codespace",
    NULL::text AS "Monstercriterium.Code",
    NULL::text AS "veldapparaat.Code",
    NULL::text AS "veldapparaat.Omschrijving",
    NULL::text AS "veldapparaat.Codespace",
    NULL::text AS "Monsternemingsdatum",
    NULL::text AS "Monsternemingstijd",
    NULL::integer AS "Monster.Tijd_UTCoffset",
    NULL::text AS "ReferentieHorizontaal.code",
    NULL::text AS "Monster.Opmerking",
    NULL::text AS "GerelateerdMonsterobject",
    NULL::text AS "GerelateerdMonsterRol.id",
    obs.obs_id AS "Meetwaarde.Identificatie",
    ('NL'::text || (mno_nst.code)::text) AS "Meetwaarde.Namespace",
    mno.wkrv_id AS "Meetwaarde.Versie",
    opt.grh_code AS "Grootheid.code",
    opt.grh_omschrijving AS "Grootheid.omschrijving",
    opt.typ_code AS "Typering.code",
    opt.typ_omschrijving AS "Typering.omschrijving",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN 'Biotaxon'::text
            WHEN (opt.chs_code IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (opt.obj_code IS NOT NULL) THEN 'Object'::text
            ELSE NULL::text
        END AS "Parameter.Groep",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN (NULL::text)::character varying
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_code
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_code
            ELSE NULL::character varying
        END AS "Parameter.Code",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN opt.txg_omschrijving
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_naam
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_omschrijving
            ELSE NULL::character varying
        END AS "Parameter.Omschrijving",
    eenh.code AS "Eenheid.code",
    eenh.omschrijving AS "Eenheid.omschrijving",
    opt.hdh_code AS "Hoedanigheid.code",
    opt.hdh_omschrijving AS "Hoedanigheid.omschrijving",
    wbm.code AS "Waardebewerkigngsmethode.code",
        CASE
            WHEN (wbm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebewerkigngsmethode.codespace",
    wbpm.code AS "Waardebepalingsmethode.code",
        CASE
            WHEN (wbpm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebepalingsmethode.codespace",
    to_char(obs.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(obs.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(obs.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(obs.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    NULL::integer AS "Meetwaarde.Tijd_UTCoffset",
    to_char(obs.result_time, 'YYYY-MM-DD'::text) AS "Resultaatdatum",
    NULL::integer AS "Resultaat_UTCoffset",
    obs.limit_symbol_id AS "Limietsymbool",
    obs.numeric_value AS "Numeriekewaarde",
    obs.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    mar.code AS "Meetapparaat.code",
        CASE
            WHEN (mar.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Meetapparaat.codespace",
    bio.levensstadium_code AS "Levensstadium.code",
    bio.lengte_code AS "Lengteklasse.code",
    bio.geslacht_code AS "Geslachtcode",
    bio.verschijning_code AS "Verschijningsvorm.code",
    bio.levensvorm_code AS "Levensvorm.code",
    bio.gedrag_code AS "Gedrag.code",
        CASE
            WHEN (obx.obs_id_target IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (mno_nst.code)::text) || '_'::text) || (obx.obs_id_target)::text)
        END AS "GerelateerdObservatieobject",
    ocr.code AS "GerelateerdObservatieobjectRol.id",
    obs.remarks AS "Meetwaarde.Opmerking",
    indicator.additional_description AS "Indicator.EUOmschrijving",
    typi.code AS "Indicator.Code",
    typd.code AS "Descriptor.Code",
    descriptor.additional_description AS "Descriptor.EUOmschrijving"
   FROM (((((((((((((((((((domains.indicator_descriptor indicator
     JOIN public.observed_property_type opti ON ((indicator.opt_id = opti.opt_id)))
     JOIN public.typering_type typi ON ((opti.indicator_id = typi.typ_id)))
     JOIN domains.indicator_descriptor descriptor ON (((indicator.part_of_idd_id = descriptor.idd_id) AND (descriptor.part_of_idd_id IS NULL))))
     JOIN public.observed_property_type optd ON ((descriptor.opt_id = optd.opt_id)))
     JOIN public.typering_type typd ON ((optd.indicator_id = typd.typ_id)))
     JOIN monprog.quality_element_indicator_descriptor qeid ON ((qeid.idd_id = indicator.idd_id)))
     JOIN public.quality_element qem ON ((qem.qem_id = qeid.qem_id)))
     JOIN public.monitoring_object_quality_element moqe ON ((moqe.qem_id = qem.qem_id)))
     JOIN public.quality_element_observation qeo ON ((qeo.qem_id = qem.qem_id)))
     JOIN public.observation obs ON ((qeo.obs_id = obs.obs_id)))
     JOIN ((public.monitoring_object mno
     LEFT JOIN (public.monitored_compartiment
     JOIN public.compartiment_type ON ((monitored_compartiment.cptm_id = compartiment_type.cptm_id))) ON ((monitored_compartiment.mno_id = mno.mno_id)))
     JOIN public.namespace_type mno_nst ON ((mno_nst.ns_id = mno.inspire_id_namespace_id))) ON (((mno.mno_id = obs.mno_id) AND ((mno.monitoring_object_type)::text <> 'Sample'::text) AND (mno.mno_id = moqe.mno_id))))
     JOIN public.eenheid_type eenh ON ((eenh.eenh_id = obs.numeric_value_uom_id)))
     JOIN opt ON ((opt.opt_id = obs.observed_property_type)))
     LEFT JOIN public.kwaliteitsoordeel_type ON ((obs.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN ((public.observation_process obp
     JOIN public.waarde_bepalings_methode_type wbpm ON ((wbpm.wbpm_id = obp.wbpm_id)))
     LEFT JOIN (public.obs_process_meetapparaat opm
     JOIN domains.meetapparaat mar ON ((mar.mar_id = opm.mar_id))) ON ((opm.obp_id = obp.obp_id))) ON ((obp.obp_id = obs.result_nature_id)))
     LEFT JOIN bio ON ((bio.obs_id = obs.obs_id)))
     LEFT JOIN (public.observation_context obx
     JOIN public.obs_ctx_role_type ocr ON ((ocr.ocr_id = obx.role_id))) ON ((obx.obs_id_source = obs.obs_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type wbm ON ((wbm.wbm_id = obs.value_processing_method_id)))
     LEFT JOIN ((public.observed_feature obf
     JOIN public.hydro_object hdo ON ((obf.hdo_id = hdo.hdo_id)))
     JOIN public.namespace_type ns_hdo ON ((hdo.inspire_id_namespace = ns_hdo.ns_id))) ON (((obs.obs_id = obf.obs_id) AND (mno.wkrv_id = hdo.wkrv_id))))
UNION ALL
 SELECT ((qem.osc_id)::text || (obs.obs_id)::text) AS unique_key,
    ((('NL'::text || (mpn_nst.code)::text) || '_'::text) || (mno_mpn.inspire_id_local_id)::text) AS "Meetpunt.Identificatie",
        CASE
            WHEN (obf.obs_id IS NOT NULL) THEN ((('NL'::text || (ns_hdo.code)::text) || '_'::text) || (hdo.inspire_id_local_id)::text)
            ELSE NULL::text
        END AS "Geoobject.Identificatie",
    mno_smpl.inspire_id_local_id AS "Monster.Identificatie",
    mpn_nst.code AS "Monster.Namespace",
    mno_smpl.wkrv_id AS "Monster.Versie",
    cmp.code AS "Compartiment.Code",
    cmp.description AS "Compartiment.Omschrijving",
    orgaan.code AS "Orgaan.Code",
    orgaan.description AS "Orgaan.Omschrijving",
    taxon_type.omschrijving AS "Organisme.Naam",
    smpl.upper_depth_value AS "BeginDiepte_m",
    smpl.lower_depth_value AS "EindDiepte_m",
        CASE
            WHEN (hdh_ud.hdh_id IS NULL) THEN
            CASE
                WHEN (hdh_ld.hdh_id IS NULL) THEN (NULL::text)::character varying
                ELSE hdh_ld.code
            END
            ELSE hdh_ud.code
        END AS "ReferentieVlak.Code",
    mbm.code AS "Monsterbewerkingsmethode.Code",
    mbm.description AS "Monsterbewerkingsmethode.Omschrijving",
    mbm.codespace AS "Monsterbewerkingsmethode.codespace",
    bmm.code AS "Bemonsteringsmethode.Code",
    bmm.description AS "Bemonsteringsmethode.Omschrijving",
        CASE
            WHEN (bmm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Bemonsteringsmethode.codespace",
        CASE
            WHEN (frac.code IS NULL) THEN biota.code
            ELSE frac.code
        END AS "Monstercriterium.Code",
    var.code AS "veldapparaat.Code",
    var.description AS "veldapparaat.Omschrijving",
    var.codespace AS "veldapparaat.Codespace",
    to_char(smpl.sampling_time, 'YYYY-MM-DD'::text) AS "Monsternemingsdatum",
    to_char(smpl.sampling_time, 'HH24:MM:SS'::text) AS "Monsternemingstijd",
    NULL::integer AS "Monster.Tijd_UTCoffset",
    'EPSG4258'::text AS "ReferentieHorizontaal.code",
    smpl.remarks AS "Monster.Opmerking",
        CASE
            WHEN (mno_smpl.wkrv_id IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (smpl_nst.code)::text) || '_'::text) || (mno_smpl.inspire_id_local_id)::text)
        END AS "GerelateerdMonsterobject",
        CASE
            WHEN (mno_smpl.wkrv_id IS NULL) THEN (NULL::text)::character varying
            ELSE rsfr.code
        END AS "GerelateerdMonsterRol.id",
    obs.obs_id AS "Meetwaarde.Identificatie",
    ('NL'::text || (mpn_nst.code)::text) AS "Meetwaarde.Namespace",
    mno_mpn.wkrv_id AS "Meetwaarde.Versie",
    opt.grh_code AS "Grootheid.code",
    opt.grh_omschrijving AS "Grootheid.omschrijving",
    opt.typ_code AS "Typering.code",
    opt.typ_omschrijving AS "Typering.omschrijving",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN 'Biotaxon'::text
            WHEN (opt.chs_code IS NOT NULL) THEN 'ChemischeStof'::text
            WHEN (opt.obj_code IS NOT NULL) THEN 'Object'::text
            ELSE NULL::text
        END AS "Parameter.Groep",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN (NULL::text)::character varying
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_code
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_code
            ELSE NULL::character varying
        END AS "Parameter.Code",
        CASE
            WHEN (opt.txg_omschrijving IS NOT NULL) THEN opt.txg_omschrijving
            WHEN (opt.chs_code IS NOT NULL) THEN opt.chs_naam
            WHEN (opt.obj_code IS NOT NULL) THEN opt.obj_omschrijving
            ELSE NULL::character varying
        END AS "Parameter.Omschrijving",
    eenh.code AS "Eenheid.code",
    eenh.omschrijving AS "Eenheid.omschrijving",
    opt.hdh_code AS "Hoedanigheid.code",
    opt.hdh_omschrijving AS "Hoedanigheid.omschrijving",
    wbm.code AS "Waardebewerkigngsmethode.code",
        CASE
            WHEN (wbm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebewerkigngsmethode.codespace",
    wbpm.code AS "Waardebepalingsmethode.code",
        CASE
            WHEN (wbpm.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Waardebepalingsmethode.codespace",
    to_char(obs.phenomenon_time_begin, 'YYYY-MM-DD'::text) AS "Begindatum",
    to_char(obs.phenomenon_time_begin, 'HH24:MM:SS'::text) AS "Begintijd",
    to_char(obs.phenomenon_time_end, 'YYYY-MM-DD'::text) AS "Einddatum",
    to_char(obs.phenomenon_time_end, 'HH24:MM:SS'::text) AS "Eindtijd",
    NULL::integer AS "Meetwaarde.Tijd_UTCoffset",
    to_char(obs.result_time, 'YYYY-MM-DD'::text) AS "Resultaatdatum",
    NULL::integer AS "Resultaat_UTCoffset",
    obs.limit_symbol_id AS "Limietsymbool",
    obs.numeric_value AS "Numeriekewaarde",
    obs.description AS "Alfanumeriekewaarde",
    kwaliteitsoordeel_type.code AS "Kwaliteitsoordeel.code",
    kwaliteitsoordeel_type.description AS "Kwaliteitsoordeel.omschrijving",
    mar.code AS "Meetapparaat.code",
        CASE
            WHEN (mar.code IS NULL) THEN NULL::text
            ELSE 'www.aquo.nl'::text
        END AS "Meetapparaat.codespace",
    bio.levensstadium_code AS "Levensstadium.code",
    bio.lengte_code AS "Lengteklasse.code",
    bio.geslacht_code AS "Geslachtcode",
    bio.verschijning_code AS "Verschijningsvorm.code",
    bio.levensvorm_code AS "Levensvorm.code",
    bio.gedrag_code AS "Gedrag.code",
        CASE
            WHEN (obx.obs_id_target IS NULL) THEN NULL::text
            ELSE ((('NL'::text || (mpn_nst.code)::text) || '_'::text) || (obx.obs_id_target)::text)
        END AS "GerelateerdObservatieobject",
    ocr.code AS "GerelateerdObservatieobjectRol.id",
    obs.remarks AS "Meetwaarde.Opmerking",
    indicator.additional_description AS "Indicator.EUOmschrijving",
    typi.code AS "Indicator.Code",
    typd.code AS "Descriptor.Code",
    descriptor.additional_description AS "Descriptor.EUOmschrijving"
   FROM (((((((((((((((((((((((((((((((((domains.indicator_descriptor indicator
     JOIN public.observed_property_type opti ON ((indicator.opt_id = opti.opt_id)))
     JOIN public.typering_type typi ON ((opti.indicator_id = typi.typ_id)))
     JOIN domains.indicator_descriptor descriptor ON (((indicator.part_of_idd_id = descriptor.idd_id) AND (descriptor.part_of_idd_id IS NULL))))
     JOIN public.observed_property_type optd ON ((descriptor.opt_id = optd.opt_id)))
     JOIN public.typering_type typd ON ((optd.indicator_id = typd.typ_id)))
     JOIN monprog.quality_element_indicator_descriptor qeid ON ((qeid.idd_id = indicator.idd_id)))
     JOIN public.quality_element qem ON ((qem.qem_id = qeid.qem_id)))
     JOIN public.quality_element_sample qes ON ((qem.qem_id = qes.qem_id)))
     JOIN public.sample smpl ON ((smpl.smpl_id = qes.smpl_id)))
     JOIN public.observation obs ON ((smpl.smpl_id = obs.mno_id)))
     JOIN public.compartiment_type cmp ON ((smpl.material_class_id = cmp.cptm_id)))
     LEFT JOIN public.hoedanigheid_type hdh_ud ON ((hdh_ud.hdh_id = smpl.upper_depth_condition_id)))
     LEFT JOIN public.hoedanigheid_type hdh_ld ON ((hdh_ld.hdh_id = smpl.lower_depth_condition_id)))
     LEFT JOIN public.taxon_type ON ((smpl.txn_id = taxon_type.txn_id)))
     LEFT JOIN domains.orgaan ON ((orgaan.org_id = smpl.organ_id)))
     LEFT JOIN public.hoedanigheid_type frac ON ((frac.hdh_id = smpl.fraction_criterium_id)))
     LEFT JOIN public.hoedanigheid_type biota ON ((biota.hdh_id = smpl.biota_criterium_id)))
     LEFT JOIN public.eenheid_type eenh_ud ON ((eenh_ud.eenh_id = smpl.upper_depth_uom_id)))
     LEFT JOIN public.eenheid_type eenh_ld ON ((eenh_ld.eenh_id = smpl.lower_depth_uom_id)))
     LEFT JOIN (((public.sampling_proces smp
     LEFT JOIN domains.monsterbewerkingsmethode mbm ON ((mbm.mbm_id = smp.sampling_method_id)))
     LEFT JOIN domains.bemonsteringsmethode bmm ON ((bmm.bmm_id = smp.sampling_processing_method_id)))
     LEFT JOIN domains.veldapparaat var ON ((var.var_id = smp.var_id))) ON ((smp.smp_id = smpl.smp_id)))
     LEFT JOIN public.related_sampling_feature rel_smpl ON ((smpl.smpl_id = rel_smpl.mnf_id_source)))
     LEFT JOIN public.sample smpl2 ON ((smpl.smpl_id = rel_smpl.mnf_id_target)))
     LEFT JOIN (public.monitoring_object mno_mpn
     JOIN public.namespace_type mpn_nst ON ((mpn_nst.ns_id = mno_mpn.inspire_id_namespace_id))) ON (((mno_mpn.mno_id = rel_smpl.mnf_id_target) AND ((mno_mpn.monitoring_object_type)::text <> 'Sample'::text))))
     LEFT JOIN (public.monitoring_object mno_smpl
     JOIN public.namespace_type smpl_nst ON ((smpl_nst.ns_id = mno_smpl.inspire_id_namespace_id))) ON (((smpl2.smpl_id = mno_smpl.mno_id) AND ((mno_smpl.monitoring_object_type)::text = 'Sample'::text))))
     LEFT JOIN public.rel_samp_feat_role_type rsfr ON ((rsfr.rsfr_id = rel_smpl.role_id)))
     JOIN public.eenheid_type eenh ON ((eenh.eenh_id = obs.numeric_value_uom_id)))
     JOIN opt ON ((opt.opt_id = obs.observed_property_type)))
     LEFT JOIN public.kwaliteitsoordeel_type ON ((obs.quality_indicator_id = kwaliteitsoordeel_type.kwod_id)))
     LEFT JOIN ((public.observation_process obp
     LEFT JOIN public.waarde_bepalings_methode_type wbpm ON ((wbpm.wbpm_id = obp.wbpm_id)))
     LEFT JOIN (public.obs_process_meetapparaat opm
     JOIN domains.meetapparaat mar ON ((mar.mar_id = opm.mar_id))) ON ((opm.obp_id = obp.obp_id))) ON ((obp.obp_id = obs.result_nature_id)))
     LEFT JOIN bio ON ((bio.obs_id = obs.obs_id)))
     LEFT JOIN (public.observation_context obx
     JOIN public.obs_ctx_role_type ocr ON ((ocr.ocr_id = obx.role_id))) ON ((obx.obs_id_source = obs.obs_id)))
     LEFT JOIN public.waarde_bewerkings_methode_type wbm ON ((wbm.wbm_id = obs.value_processing_method_id)))
     LEFT JOIN ((public.observed_feature obf
     JOIN public.hydro_object hdo ON ((obf.hdo_id = hdo.hdo_id)))
     JOIN public.namespace_type ns_hdo ON ((hdo.inspire_id_namespace = ns_hdo.ns_id))) ON ((obs.obs_id = obf.obs_id)));


--
