--
-- $Id$
--
-- Script containing customer specific db-configuration for CND

DEFINE thisfile = 'CND.SQL'
DEFINE dbuser   = ''

SET ECHO ON
SET DEFINE ON
COLUMN fcltlogfile NEW_VALUE fcltlogfile NOPRINT;
WHENEVER SQLERROR EXIT;
SELECT adm.scriptspoolfile('&dbuser', '&thisfile') AS fcltlogfile FROM DUAL;
WHENEVER SQLERROR CONTINUE;
SPOOL &fcltlogfile
SET DEFINE OFF

------ payload begin ------



-- NEN2767 Conditiemeting

-- Package om de globale instellingen aan te leveren

CREATE OR REPLACE PACKAGE CND
AS
   FUNCTION get_usrdata_key (p_usrtab_omschrijving IN VARCHAR2, p_usrdata_omschr IN VARCHAR2)
      RETURN NUMBER;
   FUNCTION get_NEN2767_instelling (p_instelling IN VARCHAR2)
      RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY CND
AS
   FUNCTION get_usrdata_key (p_usrtab_omschrijving IN VARCHAR2, p_usrdata_omschr IN VARCHAR2)
      RETURN NUMBER
   AS
      v_usrdata_key   FAC_USRDATA.FAC_USRDATA_KEY%TYPE;
   BEGIN
      SELECT fac_usrdata_key
        INTO v_usrdata_key
        FROM fac_usrtab ut, fac_usrdata ud
       WHERE     ut.fac_usrtab_key = ud.fac_usrtab_key
             AND ut.fac_usrtab_omschrijving = p_usrtab_omschrijving
             AND ud.fac_usrdata_omschr = p_usrdata_omschr
             AND fac_usrdata_verwijder IS NULL;
      RETURN v_usrdata_key;
   END;

   FUNCTION get_NEN2767_instelling (p_instelling IN VARCHAR2)
      RETURN NUMBER
   AS
      v_usrdata_prijs NUMBER;
   BEGIN
	   SELECT ud.fac_usrdata_prijs
	     INTO v_usrdata_prijs
	     FROM fac_usrdata ud, fac_usrtab ut
	    WHERE     ud.fac_usrtab_key = ut.fac_usrtab_key
	          AND ut.fac_usrtab_naam = '$PlatoNEN2727'
	          AND ud.fac_usrdata_code = p_instelling
	          AND ud.fac_usrdata_verwijder IS NULL;

      RETURN v_usrdata_prijs;
   END;
END;
/



-- cnd_v_nen2767_gebreken: hierin zitten alle objecten waarbij een melding (GEEN gebrek) is aangemaakt, en van alle meldingen "GEEN gebrek" geldt de laatste (op basis van datum).
-- Let op: dat wil niet zeggen dat er geen bevindingen op dat object na het laatste gebrek zijn geweest.

-- Kortom: registratie van wanneer de laatste keer op een object de melding "geen gebrek" werd geregistreerd.
-- Resultaat: ins_deel_key, laatste datum geen gebrek (=meldingsdatum).
-- Hierin staan zowel bestaande (status = afgemeld 5/historie 6) als nieuwe (status = nieuw(2))
-- Een gebrek kan normaal gesproken alleen de status 2 (nieuw) of 5/6 (afgemeld 5/historie, oftewel afgehandeld) hebben.
CREATE OR REPLACE VIEW cnd_v_nen2767_tmp_GEEN_gebrek
(
    ins_deel_key,
    mld_melding_key
)
AS
      SELECT d.ins_deel_key, MAX (m.mld_melding_key)
        FROM mld_melding       m,
             mld_melding_object mo,
             ins_deel          d,
             mld_stdmelding    std
       WHERE     m.mld_stdmelding_key = std.mld_stdmelding_key
             AND std.mld_stdmelding_code = 'GEEN'
             AND mo.mld_melding_key = m.mld_melding_key
             AND mo.mld_melding_object_verwijder IS NULL
             AND d.ins_deel_key = mo.ins_deel_key
    GROUP BY d.ins_deel_key;

CREATE OR REPLACE VIEW cnd_v_nen2767_GEEN_gebrek
(
    ins_deel_key,
    mld_melding_key,
    mld_melding_status
)
AS
    SELECT gg.ins_deel_key, gg.mld_melding_key, m.mld_melding_status
      FROM cnd_v_nen2767_tmp_GEEN_gebrek gg, mld_melding m
     WHERE m.mld_melding_key = gg.mld_melding_key;


-- cnd_v_nen2767_alle_gebreken: hierin zitten alle meldingen (gebreken) die relevant voor de berekening zijn, dus gebreken sinds de laatste melding "geen gebrek", waarbij van eventueel dubbele stdmeldingen alleen de laatste geldt!
-- Dit zijn alleen de gebreken NA(!) het laatste gebrek (daarvoor maken we dan handig gebruik van de view cnd_v_nen2767_GEEN_gebrek), ongeacht de status!
-- We moeten de melding "GEEN GEBREK" (mld_stdmelding_code = 'GEEN') zelf wel uitsluiten, die doet uiteraard niet mee.
-- Merk op: alle 3 mld_kenmerkmelding zit geen outer join op, omdat ze overal verplicht zijn, zonder deze waarden is de conditiescore ook niet te berekenen overigens.

CREATE OR REPLACE VIEW cnd_v_nen2767_alle_gebreken
(
    ins_deel_key,
    mld_melding_key,
    mld_stdmelding_key,
    mld_melding_status,
    ernst,
    intensiteit,
    omvang
)
AS
    SELECT d.ins_deel_key,
           m.mld_melding_key,
           m.mld_stdmelding_key,
           m.mld_melding_status,
           km_ernst.mld_kenmerkmelding_waarde,
           km_intensiteit.mld_kenmerkmelding_waarde,
           ud_omvang.fac_usrdata_prijs
      FROM mld_melding         m,
           mld_stdmelding      sm,
           mld_discipline      disc,
           mld_melding_object  mo,
           ins_deel            d,
           mld_kenmerkmelding  km_ernst,
           mld_kenmerk         k_ernst,
           mld_srtkenmerk      sk_ernst,
           mld_kenmerkmelding  km_intensiteit,
           mld_kenmerk         k_intensiteit,
           mld_srtkenmerk      sk_intensiteit,
           mld_kenmerkmelding  km_omvang,
           mld_kenmerk         k_omvang,
           mld_srtkenmerk      sk_omvang,
           fac_usrdata         ud_omvang
     WHERE     disc.ins_srtdiscipline_key = FAC.getSetting('cnd_gebreken_srtdisc_key')
           AND sm.mld_stdmelding_code <> 'GEEN'
           AND disc.ins_discipline_verwijder IS NULL
           AND sm.mld_ins_discipline_key = disc.ins_discipline_key
           AND m.mld_stdmelding_key = sm.mld_stdmelding_key
           AND mo.mld_melding_key = m.mld_melding_key
           AND mo.mld_melding_object_verwijder IS NULL
           AND d.ins_deel_key = mo.ins_deel_key
           AND km_ernst.mld_melding_key = m.mld_melding_key
           AND k_ernst.mld_kenmerk_key = km_ernst.mld_kenmerk_key
           AND k_ernst.mld_srtkenmerk_key = sk_ernst.mld_srtkenmerk_key
           AND sk_ernst.mld_srtkenmerk_code = 'ERNST'
           AND km_intensiteit.mld_melding_key = m.mld_melding_key
           AND k_intensiteit.mld_kenmerk_key = km_intensiteit.mld_kenmerk_key
           AND k_intensiteit.mld_srtkenmerk_key = sk_intensiteit.mld_srtkenmerk_key
           AND sk_intensiteit.mld_srtkenmerk_code = 'INTENSITEIT'
           AND km_omvang.mld_melding_key = m.mld_melding_key
           AND k_omvang.mld_kenmerk_key = km_omvang.mld_kenmerk_key
           AND k_omvang.mld_srtkenmerk_key = sk_omvang.mld_srtkenmerk_key
           AND sk_omvang.mld_srtkenmerk_code = 'OMVANG'
           AND ud_omvang.fac_usrdata_key = fac.safe_to_number (km_omvang.mld_kenmerkmelding_waarde)
           AND m.mld_melding_key >= COALESCE ((SELECT MAX (gg.mld_melding_key)
                                                 FROM cnd_v_nen2767_GEEN_gebrek gg
                                                WHERE gg.ins_deel_key = d.ins_deel_key), m.mld_melding_key)
           AND m.mld_melding_datum >= SYSDATE - CND.get_NEN2767_instelling ('$AantalDagenConditieScore');

-- Ontdubbel eventuele dezelfde gebreken die op hetzelfde object is gemaakt, dus wanneer kort achter elkaar hetzelfde gebrek (stdmelding) is ingelegd, dat geldt de laatste (=hoogste melding_key)
CREATE OR REPLACE VIEW cnd_v_nen2767_per_srtgebrek
(
    ins_deel_key,
    mld_melding_key,
    mld_stdmelding_key,
    mld_melding_status,
    ernst,
    intensiteit,
    omvang
)
AS
    SELECT a.ins_deel_key,
           a.mld_melding_key,
           a.mld_stdmelding_key,
           a.mld_melding_status,
           a.ernst,
           a.intensiteit,
           a.omvang
      FROM cnd_v_nen2767_alle_gebreken  a,
           (  SELECT a2.mld_stdmelding_key,
                     a2.ins_deel_key,
                     MAX (a2.mld_melding_key) AS mld_melding_key
                FROM cnd_v_nen2767_alle_gebreken a2
            GROUP BY a2.mld_stdmelding_key, a2.ins_deel_key) b
     WHERE     a.mld_melding_key = b.mld_melding_key
           AND a.ins_deel_key = b.ins_deel_key;



CREATE OR REPLACE VIEW cnd_v_nen2767_prioriteit
AS
    SELECT d.ins_deel_key,
           m.mld_melding_key,
           m.mld_stdmelding_key,
           m.mld_melding_status,
           km_1.mld_kenmerkmelding_waarde a1,
           km_2.mld_kenmerkmelding_waarde a2,
           km_3.mld_kenmerkmelding_waarde a3,
           km_4.mld_kenmerkmelding_waarde a4,
           km_5.mld_kenmerkmelding_waarde a5,
           km_6.mld_kenmerkmelding_waarde a6,
           km_7.mld_kenmerkmelding_waarde a7
      FROM mld_melding         m,
           mld_stdmelding      sm,
           mld_discipline      disc,
           mld_melding_object  mo,
           ins_deel            d,
           mld_kenmerkmelding  km_1,
           mld_kenmerk         k_1,
           mld_srtkenmerk      sk_1,
           mld_kenmerkmelding  km_2,
           mld_kenmerk         k_2,
           mld_srtkenmerk      sk_2,
           mld_kenmerkmelding  km_3,
           mld_kenmerk         k_3,
           mld_srtkenmerk      sk_3,
           mld_kenmerkmelding  km_4,
           mld_kenmerk         k_4,
           mld_srtkenmerk      sk_4,
           mld_kenmerkmelding  km_5,
           mld_kenmerk         k_5,
           mld_srtkenmerk      sk_5,
           mld_kenmerkmelding  km_6,
           mld_kenmerk         k_6,
           mld_srtkenmerk      sk_6,
           mld_kenmerkmelding  km_7,
           mld_kenmerk         k_7,
           mld_srtkenmerk      sk_7
     WHERE     disc.ins_srtdiscipline_key =
               FAC.getsetting ('cnd_gebreken_srtdisc_key')
           AND sm.mld_stdmelding_code <> 'GEEN'
           AND disc.ins_discipline_verwijder IS NULL
           AND sm.mld_ins_discipline_key = disc.ins_discipline_key
           AND m.mld_stdmelding_key = sm.mld_stdmelding_key
           AND mo.mld_melding_key = m.mld_melding_key
           AND mo.mld_melding_object_verwijder IS NULL
           AND d.ins_deel_key = mo.ins_deel_key
           AND km_1.mld_melding_key = m.mld_melding_key
           AND k_1.mld_kenmerk_key = km_1.mld_kenmerk_key
           AND k_1.mld_srtkenmerk_key = sk_1.mld_srtkenmerk_key
           AND sk_1.mld_srtkenmerk_code = 'ASPECT1'
           AND km_2.mld_melding_key = m.mld_melding_key
           AND k_2.mld_kenmerk_key = km_2.mld_kenmerk_key
           AND k_2.mld_srtkenmerk_key = sk_2.mld_srtkenmerk_key
           AND sk_2.mld_srtkenmerk_code = 'ASPECT2'
           AND km_3.mld_melding_key = m.mld_melding_key
           AND k_3.mld_kenmerk_key = km_3.mld_kenmerk_key
           AND k_3.mld_srtkenmerk_key = sk_3.mld_srtkenmerk_key
           AND sk_3.mld_srtkenmerk_code = 'ASPECT3'
           AND km_4.mld_melding_key = m.mld_melding_key
           AND k_4.mld_kenmerk_key = km_4.mld_kenmerk_key
           AND k_4.mld_srtkenmerk_key = sk_4.mld_srtkenmerk_key
           AND sk_4.mld_srtkenmerk_code = 'ASPECT4'
           AND km_5.mld_melding_key = m.mld_melding_key
           AND k_5.mld_kenmerk_key = km_5.mld_kenmerk_key
           AND k_5.mld_srtkenmerk_key = sk_5.mld_srtkenmerk_key
           AND sk_5.mld_srtkenmerk_code = 'ASPECT5'
           AND km_6.mld_melding_key = m.mld_melding_key
           AND k_6.mld_kenmerk_key = km_6.mld_kenmerk_key
           AND k_6.mld_srtkenmerk_key = sk_6.mld_srtkenmerk_key
           AND sk_6.mld_srtkenmerk_code = 'ASPECT6'
           AND km_7.mld_melding_key = m.mld_melding_key
           AND k_7.mld_kenmerk_key = km_7.mld_kenmerk_key
           AND k_7.mld_srtkenmerk_key = sk_7.mld_srtkenmerk_key
           AND sk_7.mld_srtkenmerk_code = 'ASPECT7'
           AND m.mld_melding_key >=
               COALESCE ((SELECT MAX (gg.mld_melding_key)
                            FROM cnd_v_nen2767_GEEN_gebrek gg
                           WHERE gg.ins_deel_key = d.ins_deel_key),
                         m.mld_melding_key)
           AND m.mld_melding_datum >=
                 SYSDATE
               - CND.get_NEN2767_instelling ('$AantalDagenConditieScore');

CREATE OR REPLACE VIEW cnd_v_nen2767_gebrek
AS
    SELECT mo.ins_deel_key,
           m.mld_melding_key,
           m.mld_stdmelding_key,
           m.mld_melding_status,
           m.mld_melding_datum
      FROM mld_melding         m,
           mld_stdmelding      sm,
           mld_discipline      disc,
           mld_melding_object  mo
     WHERE     disc.ins_srtdiscipline_key =
               FAC.getsetting ('cnd_gebreken_srtdisc_key')
           AND sm.mld_stdmelding_code <> 'GEEN'
           AND disc.ins_discipline_verwijder IS NULL
           AND sm.mld_ins_discipline_key = disc.ins_discipline_key
           AND m.mld_stdmelding_key = sm.mld_stdmelding_key
           AND mo.mld_melding_key = m.mld_melding_key
           AND mo.mld_melding_object_verwijder IS NULL
           AND m.mld_melding_key >=
               COALESCE ((SELECT MAX (gg.mld_melding_key)
                            FROM cnd_v_nen2767_GEEN_gebrek gg
                           WHERE gg.ins_deel_key = mo.ins_deel_key),
                         m.mld_melding_key)
           AND m.mld_melding_datum >=
                 SYSDATE
               - CND.get_NEN2767_instelling ('$AantalDagenConditieScore');

CREATE OR REPLACE VIEW cnd_v_nen2767_srtgebrek
AS
    SELECT a.ins_deel_key,
           a.mld_melding_key,
           a.mld_stdmelding_key,
           a.mld_melding_status,
           a.mld_melding_datum
      FROM cnd_v_nen2767_gebrek  a,
           (  SELECT a2.mld_stdmelding_key,
                     a2.ins_deel_key,
                     MAX (a2.mld_melding_key) AS mld_melding_key
                FROM cnd_v_nen2767_gebrek a2
            GROUP BY a2.mld_stdmelding_key, a2.ins_deel_key) b
     WHERE     a.mld_melding_key = b.mld_melding_key
           AND a.ins_deel_key = b.ins_deel_key;


-- Ontdubbel eventuele dezelfde gebreken die op hetzelfde object is gemaakt, dus wanneer kort achter elkaar hetzelfde gebrek (stdmelding) is ingelegd, dat geldt de laatste (=hoogste melding_key)
CREATE OR REPLACE VIEW cnd_v_nen2767_prio_srtgebrek
AS
    SELECT a.ins_deel_key,
           a.mld_melding_key,
           a.mld_stdmelding_key,
           a.mld_melding_status,
           a.a1,
           a.a2,
           a.a3,
           a.a4,
           a.a5,
           a.a6,
           a.a7
      FROM cnd_v_nen2767_prioriteit  a,
           (  SELECT a2.mld_stdmelding_key,
                     a2.ins_deel_key,
                     MAX (a2.mld_melding_key) AS mld_melding_key
                FROM cnd_v_nen2767_prioriteit a2
            GROUP BY a2.mld_stdmelding_key, a2.ins_deel_key) b
     WHERE     a.mld_melding_key = b.mld_melding_key
           AND a.ins_deel_key = b.ins_deel_key;


CREATE OR REPLACE FUNCTION cnd_nen2767_meth12_cond_score (p_ernst IN NUMBER, p_intens IN NUMBER, p_omvang IN NUMBER) RETURN NUMBER
AS
  	v_result                    NUMBER;
  	v_ernstig_gebrek_key        NUMBER;
  	v_serieus_gebrek_key        NUMBER;
  	v_gering_gebrek_key         NUMBER;

  	v_begin_intensiteit_key     NUMBER;
  	v_gevorderd_intensiteit_key NUMBER;
  	v_eind_intensiteit_key      NUMBER;

BEGIN
   v_ernstig_gebrek_key := CND.get_usrdata_key ('NEN2767 Ernst van gebrek', 'E - Ernstig gebrek');
   v_serieus_gebrek_key := CND.get_usrdata_key ('NEN2767 Ernst van gebrek', 'S - Serieus gebrek');
   v_gering_gebrek_key  := CND.get_usrdata_key ('NEN2767 Ernst van gebrek', 'G - Gering gebrek');

   v_begin_intensiteit_key     := CND.get_usrdata_key ('NEN2767 Intensiteit van gebrek', '1 - Beginstadium');
   v_gevorderd_intensiteit_key := CND.get_usrdata_key ('NEN2767 Intensiteit van gebrek', '2 - Gevorderd stadium');
   v_eind_intensiteit_key      := CND.get_usrdata_key ('NEN2767 Intensiteit van gebrek', '3 - Eindstadium');

	v_result := NULL;
	CASE

		WHEN p_ernst = v_ernstig_gebrek_key  AND p_intens = v_begin_intensiteit_key     AND p_omvang < 2		THEN v_result := 1;
		WHEN p_ernst = v_ernstig_gebrek_key  AND p_intens = v_begin_intensiteit_key     AND p_omvang < 10		THEN v_result := 1;
		WHEN p_ernst = v_ernstig_gebrek_key  AND p_intens = v_begin_intensiteit_key     AND p_omvang < 30		THEN v_result := 2;
		WHEN p_ernst = v_ernstig_gebrek_key  AND p_intens = v_begin_intensiteit_key     AND p_omvang < 70		THEN v_result := 3;
		WHEN p_ernst = v_ernstig_gebrek_key  AND p_intens = v_begin_intensiteit_key     AND p_omvang <= 100	THEN v_result := 4;

		WHEN p_ernst = v_ernstig_gebrek_key  AND p_intens = v_gevorderd_intensiteit_key AND p_omvang < 2		THEN v_result := 1;
		WHEN p_ernst = v_ernstig_gebrek_key  AND p_intens = v_gevorderd_intensiteit_key AND p_omvang < 10		THEN v_result := 2;
		WHEN p_ernst = v_ernstig_gebrek_key  AND p_intens = v_gevorderd_intensiteit_key AND p_omvang < 30		THEN v_result := 3;
		WHEN p_ernst = v_ernstig_gebrek_key  AND p_intens = v_gevorderd_intensiteit_key AND p_omvang < 70		THEN v_result := 4;
		WHEN p_ernst = v_ernstig_gebrek_key  AND p_intens = v_gevorderd_intensiteit_key AND p_omvang <= 100	THEN v_result := 5;

		WHEN p_ernst = v_ernstig_gebrek_key  AND p_intens = v_eind_intensiteit_key      AND p_omvang < 2		THEN v_result := 2;
		WHEN p_ernst = v_ernstig_gebrek_key  AND p_intens = v_eind_intensiteit_key      AND p_omvang < 10		THEN v_result := 3;
		WHEN p_ernst = v_ernstig_gebrek_key  AND p_intens = v_eind_intensiteit_key      AND p_omvang < 30		THEN v_result := 4;
		WHEN p_ernst = v_ernstig_gebrek_key  AND p_intens = v_eind_intensiteit_key      AND p_omvang < 70		THEN v_result := 5;
		WHEN p_ernst = v_ernstig_gebrek_key  AND p_intens = v_eind_intensiteit_key      AND p_omvang <= 100	THEN v_result := 6;

		WHEN p_ernst = v_serieus_gebrek_key  AND p_intens = v_begin_intensiteit_key     AND p_omvang < 2		THEN v_result := 1;
		WHEN p_ernst = v_serieus_gebrek_key  AND p_intens = v_begin_intensiteit_key     AND p_omvang < 10		THEN v_result := 1;
		WHEN p_ernst = v_serieus_gebrek_key  AND p_intens = v_begin_intensiteit_key     AND p_omvang < 30		THEN v_result := 1;
		WHEN p_ernst = v_serieus_gebrek_key  AND p_intens = v_begin_intensiteit_key     AND p_omvang < 70		THEN v_result := 2;
		WHEN p_ernst = v_serieus_gebrek_key  AND p_intens = v_begin_intensiteit_key     AND p_omvang <= 100	THEN v_result := 3;

		WHEN p_ernst = v_serieus_gebrek_key  AND p_intens = v_gevorderd_intensiteit_key AND p_omvang < 2		THEN v_result := 1;
		WHEN p_ernst = v_serieus_gebrek_key  AND p_intens = v_gevorderd_intensiteit_key AND p_omvang < 10		THEN v_result := 2;
		WHEN p_ernst = v_serieus_gebrek_key  AND p_intens = v_gevorderd_intensiteit_key AND p_omvang < 30		THEN v_result := 3;
		WHEN p_ernst = v_serieus_gebrek_key  AND p_intens = v_gevorderd_intensiteit_key AND p_omvang < 70		THEN v_result := 3;
		WHEN p_ernst = v_serieus_gebrek_key  AND p_intens = v_gevorderd_intensiteit_key AND p_omvang <= 100	THEN v_result := 4;

		WHEN p_ernst = v_serieus_gebrek_key  AND p_intens = v_eind_intensiteit_key      AND p_omvang < 2		THEN v_result := 1;
		WHEN p_ernst = v_serieus_gebrek_key  AND p_intens = v_eind_intensiteit_key      AND p_omvang < 10		THEN v_result := 2;
		WHEN p_ernst = v_serieus_gebrek_key  AND p_intens = v_eind_intensiteit_key      AND p_omvang < 30		THEN v_result := 3;
		WHEN p_ernst = v_serieus_gebrek_key  AND p_intens = v_eind_intensiteit_key      AND p_omvang < 70		THEN v_result := 4;
		WHEN p_ernst = v_serieus_gebrek_key  AND p_intens = v_eind_intensiteit_key      AND p_omvang <= 100	THEN v_result := 5;

		WHEN p_ernst = v_gering_gebrek_key   AND p_intens = v_begin_intensiteit_key     AND p_omvang < 2		THEN v_result := 1;
		WHEN p_ernst = v_gering_gebrek_key   AND p_intens = v_begin_intensiteit_key     AND p_omvang < 10		THEN v_result := 1;
		WHEN p_ernst = v_gering_gebrek_key   AND p_intens = v_begin_intensiteit_key     AND p_omvang < 30		THEN v_result := 1;
		WHEN p_ernst = v_gering_gebrek_key   AND p_intens = v_begin_intensiteit_key     AND p_omvang < 70		THEN v_result := 1;
		WHEN p_ernst = v_gering_gebrek_key   AND p_intens = v_begin_intensiteit_key     AND p_omvang <= 100	THEN v_result := 2;

		WHEN p_ernst = v_gering_gebrek_key   AND p_intens = v_gevorderd_intensiteit_key AND p_omvang < 2		THEN v_result := 1;
		WHEN p_ernst = v_gering_gebrek_key   AND p_intens = v_gevorderd_intensiteit_key AND p_omvang < 10		THEN v_result := 1;
		WHEN p_ernst = v_gering_gebrek_key   AND p_intens = v_gevorderd_intensiteit_key AND p_omvang < 30		THEN v_result := 1;
		WHEN p_ernst = v_gering_gebrek_key   AND p_intens = v_gevorderd_intensiteit_key AND p_omvang < 70		THEN v_result := 2;
		WHEN p_ernst = v_gering_gebrek_key   AND p_intens = v_gevorderd_intensiteit_key AND p_omvang <= 100	THEN v_result := 3;

		WHEN p_ernst = v_gering_gebrek_key   AND p_intens = v_eind_intensiteit_key      AND p_omvang < 2		THEN v_result := 1;
		WHEN p_ernst = v_gering_gebrek_key   AND p_intens = v_eind_intensiteit_key      AND p_omvang < 10		THEN v_result := 1;
		WHEN p_ernst = v_gering_gebrek_key   AND p_intens = v_eind_intensiteit_key      AND p_omvang < 30		THEN v_result := 2;
		WHEN p_ernst = v_gering_gebrek_key   AND p_intens = v_eind_intensiteit_key      AND p_omvang < 70		THEN v_result := 3;
		WHEN p_ernst = v_gering_gebrek_key   AND p_intens = v_eind_intensiteit_key      AND p_omvang <= 100	THEN v_result := 4;
	END CASE;
   RETURN v_result;
EXCEPTION WHEN OTHERS THEN
   RETURN NULL;
END;
/

CREATE OR REPLACE FUNCTION cnd_nen2767_corr_cond_score (p_cond_score IN NUMBER) RETURN NUMBER
AS
  v_result   NUMBER(4,2);
BEGIN
	CASE
		WHEN p_cond_score = 1 THEN v_result := 1;
		WHEN p_cond_score = 2 THEN v_result := 1.02;
		WHEN p_cond_score = 3 THEN v_result := 1.1;
		WHEN p_cond_score = 4 THEN v_result := 1.3;
		WHEN p_cond_score = 5 THEN v_result := 1.7;
		WHEN p_cond_score = 6 THEN v_result := 2;
	END CASE;
   RETURN v_result;
END;
/

CREATE OR REPLACE FUNCTION cnd_nen2767_cond_herleiding (p_cond_tussen_score IN NUMBER) RETURN NUMBER
AS
  v_result   NUMBER(10);
BEGIN
	CASE
		WHEN p_cond_tussen_score <= 1.01 THEN v_result := 1;
		WHEN p_cond_tussen_score <= 1.04 THEN v_result := 2;
		WHEN p_cond_tussen_score <= 1.15 THEN v_result := 3;
		WHEN p_cond_tussen_score <= 1.4 THEN v_result := 4;
		WHEN p_cond_tussen_score <= 1.78 THEN v_result := 5;
		ELSE v_result := 6;
	END CASE;
   RETURN v_result;
END;
/


-- Bepaal de prioriteit aan de hand van de aspecten bij het gebrek en de configuratie in het srtgebouw.
CREATE OR REPLACE FUNCTION cnd_nen2767_prio_score (p_deel_key IN NUMBER) RETURN NUMBER
AS

  	v_result  NUMBER;
   v_srtgebouw_key NUMBER;

   v_a1  NUMBER;
   v_a2  NUMBER;
   v_a3  NUMBER;
   v_a4  NUMBER;
   v_a5  NUMBER;
   v_a6  NUMBER;
   v_a7  NUMBER;

   v_a1_max  NUMBER;
   v_a2_max  NUMBER;
   v_a3_max  NUMBER;
   v_a4_max  NUMBER;
   v_a5_max  NUMBER;
   v_a6_max  NUMBER;
   v_a7_max  NUMBER;

   FUNCTION getPrio (p_max IN NUMBER, p_usrdata_key IN NUMBER, p_cur_prio IN NUMBER)
      RETURN NUMBER
   AS
      v_prio_result       NUMBER;
      v_gering_aspect_key NUMBER(10);
      v_matig_aspect_key  NUMBER(10);
      v_sterk_aspect_key  NUMBER(10);
   BEGIN
      v_gering_aspect_key := CND.get_usrdata_key ('NEN2767 Aspectenmatrix', 'Gering');
	   v_matig_aspect_key  := CND.get_usrdata_key ('NEN2767 Aspectenmatrix', 'Matig');
	   v_sterk_aspect_key  := CND.get_usrdata_key ('NEN2767 Aspectenmatrix', 'Sterk');

	   CASE p_usrdata_key
   	   WHEN v_gering_aspect_key THEN v_prio_result := p_max + 2;
   	   WHEN v_matig_aspect_key  THEN v_prio_result := p_max + 1;
   	   WHEN v_sterk_aspect_key  THEN v_prio_result := p_max;
   	   ELSE v_prio_result := 10;
   	END CASE;
   	IF v_prio_result < p_cur_prio
   	THEN
   	   RETURN v_prio_result;
   	ELSE
   	   RETURN p_cur_prio;
   	END IF;
   END;

BEGIN
   SELECT alg_srtgebouw_key
     INTO v_srtgebouw_key
     FROM ins_v_deel_gegevens dg,
          alg_gebouw g
    WHERE dg.ins_deel_key = p_deel_key
      AND dg.alg_gebouw_key = g.alg_gebouw_key;

   SELECT alg_srtgebouw_aspect_max_vg,
          alg_srtgebouw_aspect_max_ch,
          alg_srtgebouw_aspect_max_gb,
          alg_srtgebouw_aspect_max_tv,
          alg_srtgebouw_aspect_max_tk,
          alg_srtgebouw_aspect_max_be,
          alg_srtgebouw_aspect_max_en
     INTO v_a1_max,
          v_a2_max,
          v_a3_max,
          v_a4_max,
          v_a5_max,
          v_a6_max,
          v_a7_max
     FROM alg_srtgebouw
    WHERE alg_srtgebouw_key = v_srtgebouw_key;

      SELECT MAX (
                 (SELECT km.mld_kenmerkmelding_waarde
                    FROM mld_kenmerkmelding km,
                         mld_kenmerk       k,
                         mld_srtkenmerk    sk
                   WHERE     km.mld_melding_key = g.mld_melding_key
                         AND k.mld_kenmerk_key = km.mld_kenmerk_key
                         AND k.mld_srtkenmerk_key = sk.mld_srtkenmerk_key
                         AND sk.mld_srtkenmerk_code = 'ASPECT1'))
                 a1,
             MAX (
                 (SELECT km.mld_kenmerkmelding_waarde
                    FROM mld_kenmerkmelding km,
                         mld_kenmerk       k,
                         mld_srtkenmerk    sk
                   WHERE     km.mld_melding_key = g.mld_melding_key
                         AND k.mld_kenmerk_key = km.mld_kenmerk_key
                         AND k.mld_srtkenmerk_key = sk.mld_srtkenmerk_key
                         AND sk.mld_srtkenmerk_code = 'ASPECT2'))
                 a2,
             MAX (
                 (SELECT km.mld_kenmerkmelding_waarde
                    FROM mld_kenmerkmelding km,
                         mld_kenmerk       k,
                         mld_srtkenmerk    sk
                   WHERE     km.mld_melding_key = g.mld_melding_key
                         AND k.mld_kenmerk_key = km.mld_kenmerk_key
                         AND k.mld_srtkenmerk_key = sk.mld_srtkenmerk_key
                         AND sk.mld_srtkenmerk_code = 'ASPECT3'))
                 a3,
             MAX (
                 (SELECT km.mld_kenmerkmelding_waarde
                    FROM mld_kenmerkmelding km,
                         mld_kenmerk       k,
                         mld_srtkenmerk    sk
                   WHERE     km.mld_melding_key = g.mld_melding_key
                         AND k.mld_kenmerk_key = km.mld_kenmerk_key
                         AND k.mld_srtkenmerk_key = sk.mld_srtkenmerk_key
                         AND sk.mld_srtkenmerk_code = 'ASPECT4'))
                 a4,
             MAX (
                 (SELECT km.mld_kenmerkmelding_waarde
                    FROM mld_kenmerkmelding km,
                         mld_kenmerk       k,
                         mld_srtkenmerk    sk
                   WHERE     km.mld_melding_key = g.mld_melding_key
                         AND k.mld_kenmerk_key = km.mld_kenmerk_key
                         AND k.mld_srtkenmerk_key = sk.mld_srtkenmerk_key
                         AND sk.mld_srtkenmerk_code = 'ASPECT5'))
                 a5,
             MAX (
                 (SELECT km.mld_kenmerkmelding_waarde
                    FROM mld_kenmerkmelding km,
                         mld_kenmerk       k,
                         mld_srtkenmerk    sk
                   WHERE     km.mld_melding_key = g.mld_melding_key
                         AND k.mld_kenmerk_key = km.mld_kenmerk_key
                         AND k.mld_srtkenmerk_key = sk.mld_srtkenmerk_key
                         AND sk.mld_srtkenmerk_code = 'ASPECT6'))
                 a6,
             MAX (
                 (SELECT km.mld_kenmerkmelding_waarde
                    FROM mld_kenmerkmelding km,
                         mld_kenmerk       k,
                         mld_srtkenmerk    sk
                   WHERE     km.mld_melding_key = g.mld_melding_key
                         AND k.mld_kenmerk_key = km.mld_kenmerk_key
                         AND k.mld_srtkenmerk_key = sk.mld_srtkenmerk_key
                         AND sk.mld_srtkenmerk_code = 'ASPECT7'))
                 a7
        INTO v_a1,
             v_a2,
             v_a3,
             v_a4,
             v_a5,
             v_a6,
             v_a7
        FROM cnd_v_nen2767_srtgebrek g
       WHERE g.ins_deel_key = p_deel_key
    GROUP BY g.ins_deel_key;


   v_result := getPrio (v_a1_max, v_a1, 10);
   v_result := getPrio (v_a2_max, v_a2, v_result);
   v_result := getPrio (v_a3_max, v_a3, v_result);
   v_result := getPrio (v_a4_max, v_a4, v_result);
   v_result := getPrio (v_a5_max, v_a5, v_result);
   v_result := getPrio (v_a6_max, v_a6, v_result);
   v_result := getPrio (v_a7_max, v_a7, v_result);

   IF v_result = 10
   THEN
      RETURN NULL;
   ELSE
      RETURN v_result;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END;
/

-- 2. NEN2767-2. - Export functie (dummy) die de conditiescore berekend.

CREATE OR REPLACE FUNCTION cnd_nen2767_meth3_cond_score (
    p_cond_score_corr_str   IN VARCHAR2,
    p_omvang_str            IN VARCHAR2)
    RETURN NUMBER
AS
    v_result                NUMBER (10, 4);
    v_cond_score_corr_str   VARCHAR2 (255);
    v_omvang_str            VARCHAR2 (255);
    v_fielddelimitor        VARCHAR2 (1);
    l_cond_score_corr       NUMBER (10, 2);
    l_omvang                NUMBER (10);
    l_som_omvang            NUMBER (10);
BEGIN
    v_result := 0;
    v_fielddelimitor := ';';
    l_som_omvang := 0;

    v_cond_score_corr_str := p_cond_score_corr_str;
    v_omvang_str := p_omvang_str;

    WHILE (v_cond_score_corr_str IS NOT NULL AND v_omvang_str IS NOT NULL)
    LOOP
        BEGIN
            fac.imp_getfield (v_cond_score_corr_str,
                              v_fielddelimitor,
                              l_cond_score_corr);
            fac.imp_getfield (v_omvang_str, v_fielddelimitor, l_omvang);

            v_result :=
                  v_result
                + (  fac.safe_to_number (l_cond_score_corr)
                   * fac.safe_to_number (l_omvang));
            l_som_omvang := l_som_omvang + fac.safe_to_number (l_omvang);
        END;
    END LOOP;

    v_result := ROUND (v_result / l_som_omvang, 4);
    v_result := cnd_nen2767_cond_herleiding (v_result);

    RETURN v_result;
EXCEPTION
    WHEN OTHERS
    THEN
        RETURN NULL;
END;
/

CREATE OR REPLACE VIEW cnd_v_aggr_cond_score
(
    alg_locatie_key,
    alg_gebouw_key,
    ins_deel_key,
    ins_deel_aantal,
    ins_deel_mjb_score1,
    kosten,
    correctiefactor,
    totale_vervangingswaarde,
    correctie_vervangingswaarde
)
AS
      SELECT l.alg_locatie_key,
             g.alg_gebouw_key,
             d.ins_deel_key,
             MAX (d.ins_deel_aantal),
             MAX (d.ins_deel_mjb_score1),
             MAX (
                   COALESCE (sc.INS_SRTCONTROLE_KOSTEN, 0)
                 + COALESCE (sc.INS_SRTCONTROLE_KOSTEN2, 0)
                 + COALESCE (sc.INS_SRTCONTROLE_KOSTEN3, 0)
                 + COALESCE (sc.INS_SRTCONTROLE_MATERIAAL, 0))
                 kosten,
             cnd_nen2767_corr_cond_score (MAX (d.ins_deel_mjb_score1))
                 correctiefactor,
               MAX (d.ins_deel_aantal)
             * MAX (
                     COALESCE (sc.INS_SRTCONTROLE_KOSTEN, 0)
                   + COALESCE (sc.INS_SRTCONTROLE_KOSTEN2, 0)
                   + COALESCE (sc.INS_SRTCONTROLE_KOSTEN3, 0)
                   + COALESCE (sc.INS_SRTCONTROLE_MATERIAAL, 0))
                 totale_vervangingswaarde,
               MAX (d.ins_deel_aantal)
             * MAX (
                     COALESCE (sc.INS_SRTCONTROLE_KOSTEN, 0)
                   + COALESCE (sc.INS_SRTCONTROLE_KOSTEN2, 0)
                   + COALESCE (sc.INS_SRTCONTROLE_KOSTEN3, 0)
                   + COALESCE (sc.INS_SRTCONTROLE_MATERIAAL, 0))
             * cnd_nen2767_corr_cond_score (MAX (d.ins_deel_mjb_score1))
                 correctie_vervangingswaarde
        FROM ins_deel       d,
             ins_v_defined_inspect_xcp sc,
             alg_ruimte     r,
             alg_verdieping v,
             alg_gebouw     g,
             alg_locatie    l
       WHERE     d.ins_deel_verwijder IS NULL
             AND ins_deel_mjb_score1 IS NOT NULL
             AND sc.ins_deel_key = d.ins_deel_key
             AND d.ins_alg_ruimte_type = 'R'
             AND d.ins_alg_ruimte_key = r.alg_ruimte_key
             AND r.alg_verdieping_key = v.alg_verdieping_key
             AND v.alg_gebouw_key = g.alg_gebouw_key
             AND g.alg_locatie_key = l.alg_locatie_key
             AND ins_scenario_key = 1
             AND ctr_ismjob = 1
    GROUP BY d.ins_deel_key, l.alg_locatie_key, g.alg_gebouw_key;

CREATE OR REPLACE PROCEDURE cnd_export_nen2767_cond_score (
    p_applname   IN VARCHAR2,
    p_applrun    IN VARCHAR2,
    p_filedir    IN VARCHAR2,
    p_filename   IN VARCHAR2)
AS
    -- Twee hoofdcursors: deze bevat nieuwe meldingen (status = nieuw), oftewel:
    -- 1. a) evt nieuwe "geen gebrek" melding, dit wordt 1e loop
    --    b) variant van vorig punt 1a): een groepering op mld_melding_key, tbv 2e loop

    -- 2. a) de nieuwe meldingsgebreken (na geen gebrek melding),dit wordt de 3e loop
    --    b) variant van vorig punt 2a): een groepering op mld_melding_key, tbv 4e loop

    -- Cursor 1a
    CURSOR c_nieuwe_GEEN_gebreken
    IS
        SELECT ins_deel_key, mld_melding_key
          FROM cnd_v_nen2767_GEEN_gebrek
         WHERE mld_melding_status = 2;

    -- Cursor 1b
    CURSOR c_GRP_nieuwe_GEEN_gebreken
    IS
          SELECT mld_melding_key
            FROM cnd_v_nen2767_GEEN_gebrek
           WHERE mld_melding_status = 2
        GROUP BY mld_melding_key;

    -- Cursor 2a
    CURSOR c_nieuwe_gebreken
    IS
          SELECT ins_deel_key
            FROM cnd_v_nen2767_per_srtgebrek
           WHERE mld_melding_status = 2
        GROUP BY ins_deel_key;

    -- Cursor 2b
    CURSOR c_GRP_nieuwe_gebreken
    IS
          SELECT mld_melding_key
            FROM cnd_v_nen2767_per_srtgebrek
           WHERE mld_melding_status = 2
        GROUP BY mld_melding_key;


    -- Bereken methode 1 en 2: de  gebreken op betreffende object hebben WEL dezelfde ernst en intensiteit;
    CURSOR c_gebreken_zelfde_ernst_intens (p_ins_deel_key NUMBER)
    IS
          SELECT ins_deel_key,
                 ernst,
                 intensiteit,
                 LEAST (100, SUM (omvang)) AS omvang
            FROM cnd_v_nen2767_per_srtgebrek
           WHERE ins_deel_key = p_ins_deel_key
          HAVING COUNT (*) = 1
        GROUP BY ins_deel_key, ernst, intensiteit;

    -- Bereken methode3: de gebreken op betreffende object hebben niet dezelfde ernst en intensiteit;
    -- Let op: deze methodiek écht pas activeren wanneer aan deze voorwaarden voldaan wordt (2 of meer gebreken en/of niet dezelfde ernst/intensiteit) want deze methodiek loslaten op 1 gebrek geeft niet altijd de goede uitkomst.
    CURSOR c_gebreken_delta_ernst_intens (p_ins_deel_key NUMBER)
    IS
          SELECT ins_deel_key,
                 ernst,
                 intensiteit,
                 LEAST (100, SUM (omvang)) AS omvang
            FROM cnd_v_nen2767_per_srtgebrek
           WHERE ins_deel_key = p_ins_deel_key
          HAVING COUNT (*) > 1
        GROUP BY ins_deel_key, ernst, intensiteit;

    l_gevonden                    NUMBER (10);
    l_facilitor_prs_perslid_key   NUMBER (10);
    l_cond_score                  NUMBER (10);
    l_cond_score_corr             NUMBER (10, 2);
    l_som_omvang                  NUMBER (10);
    l_cond_score_corr_str         VARCHAR2 (255);
    l_cond_omvang_str             VARCHAR2 (255);
    l_prio_score                  NUMBER (10);
BEGIN
    SELECT COUNT (*)
      INTO l_gevonden
      FROM prs_perslid
     WHERE     prs_perslid_oslogin = '_FACILITOR'
           AND prs_perslid_verwijder IS NULL;

    IF l_gevonden = 1
    THEN
        SELECT prs_perslid_key
          INTO l_facilitor_prs_perslid_key
          FROM prs_perslid
         WHERE     prs_perslid_oslogin = '_FACILITOR'
               AND prs_perslid_verwijder IS NULL;
    END IF;

    -- Hier de verzameling aan geen gebreken, elk object gaat conditiescore 1 krijgen.
    -- Mochten er toch nog gebreken na (dus op een tijdstip later) de melding "Geen gebrek" zijn ingevoerd, dan komen die in de volgende cursor/loop 2a (c_nieuwe_gebreken) aan de orde, en wordt de conditiescore dan alsnog bijgewerkt.
    -- LOOP 1A
    FOR rec_geen_gebrek IN c_nieuwe_GEEN_gebreken
    LOOP
        UPDATE ins_deel
           SET ins_deel_mjb_score1 = 1, ins_deel_mjb_score2 = NULL
         WHERE ins_deel_key = rec_geen_gebrek.ins_deel_key;

        IF l_facilitor_prs_perslid_key IS NOT NULL
        THEN
            fac.trackaction (
                '#INSUPD',
                rec_geen_gebrek.ins_deel_key,
                l_facilitor_prs_perslid_key,
                NULL,
                   'A.g.v. geen gebrek (nr. '
                || rec_geen_gebrek.mld_melding_key
                || ') is conditiescore op 1 gezet');
            fac.trackaction (
                '#INSUPD',
                rec_geen_gebrek.ins_deel_key,
                l_facilitor_prs_perslid_key,
                NULL,
                   'A.g.v. geen gebrek (nr. '
                || rec_geen_gebrek.mld_melding_key
                || ') is prioriteitsscore geleegd (NVT)');
        END IF;
    -- Omdat 1 melding (geen gebrek) meerdere objecten kan bevatten, kunnen we nog geen update van melding status doen, eerst moeten alle objecten ervan worden verwerkt.
    -- Mogelijk ook anders kan op foutmelding Mutating triggers oid.
    -- Dus update status in een loop hieronder...
    END LOOP;

    -- LOOP 1B
    FOR rec_geen_gebrek IN c_GRP_nieuwe_GEEN_gebreken
    LOOP
        UPDATE mld_melding
           SET mld_melding_status = 5
         WHERE mld_melding_key = rec_geen_gebrek.mld_melding_key;

        IF l_facilitor_prs_perslid_key IS NOT NULL
        THEN
            fac.trackaction (
                '#MLDAFM',
                rec_geen_gebrek.mld_melding_key,
                l_facilitor_prs_perslid_key,
                NULL,
                'Gebrek verwerkt in de conditiescore van het object');
        END IF;
    END LOOP;

    -- Nu de 'Geen gebrek' meldingen zijn verwerkt, gaan we nu over op de gebreken (sinds de laatste geen gebrek en rekening houdende met de instelling '$AantalDagenConditieScore').
    -- In deze loop staan alle ins_delen waarvan een nieuw gebrek is geregistreerd, en per ins_deel wordt de conditiescore bepaald.
    -- LOOP 2A
    FOR rec_gebrek IN c_nieuwe_gebreken
    LOOP
        l_cond_score := NULL;

        -- Object heeft hetzij alleen maar gebreken met zelfde_ernst_intens (hetzij gebreken met verschillende ernst en intensiteit, dat wordt hierna in een volgende lus afgewerkt)
        FOR rec_zelfde
            IN c_gebreken_zelfde_ernst_intens (rec_gebrek.ins_deel_key)
        LOOP
            -- Deze loop wordt feiltelijk 1x doorlopen...
            l_cond_score :=
                cnd_nen2767_meth12_cond_score (rec_zelfde.ernst,
                                               rec_zelfde.intensiteit,
                                               rec_zelfde.omvang);
        END LOOP;

        IF l_cond_score IS NULL
        THEN
            l_som_omvang := 0;
            l_cond_score_corr_str := '';
            l_cond_omvang_str := '';

            -- Het object heeft gebreken met verschillende (delta) ernst_intens, in de 1e lus hierboven is die niet langsgekomen.
            FOR rec_delta
                IN c_gebreken_delta_ernst_intens (rec_gebrek.ins_deel_key)
            LOOP
                -- Deze loop wordt minimaal 2 keer doorlopen...
                l_cond_score :=
                    cnd_nen2767_meth12_cond_score (rec_delta.ernst,
                                                   rec_delta.intensiteit,
                                                   100); -- Doorrekenen op 100%, zie par 4.3, pagina 12 van document 'Conditiemeting 1.0'
                l_cond_score_corr :=
                    cnd_nen2767_corr_cond_score (l_cond_score); -- Uitrekenen van daarvan de gecorrigeerde score is...
                l_cond_score_corr_str :=
                    l_cond_score_corr_str || ';' || l_cond_score; -- Een array voor de gecorrigeerde score
                l_cond_omvang_str :=
                    l_cond_omvang_str || ';' || rec_delta.omvang; -- En eveneens een 'array' voor de echte omvang ipv 100 van hierboven, die hebben we straks weer nodig
                l_som_omvang := l_som_omvang + rec_delta.omvang; -- En we gaan de omvang van alle gebreken bij elkaar optellen, om te kijken of de som < 100 is, want dan is er ook een gedeelte (100 - som) die geen gebreken heeft, dat doet namelijk ook mee in de berekening...
            END LOOP;

            IF l_som_omvang < 100
            THEN
                -- Er is nog een rest-gedeelte (omvang) die geen gebreken heeft, die doet met score 1 mee in de berekening.
                l_cond_score_corr_str := l_cond_score_corr_str || ';' || '1';
                l_cond_omvang_str :=
                    l_cond_omvang_str || ';' || (100 - l_som_omvang);
            END IF;

            l_cond_score_corr_str := SUBSTR (l_cond_score_corr_str, 2); -- eerste ; eraf strippen
            l_cond_omvang_str := SUBSTR (l_cond_omvang_str, 2); -- eerste ; eraf strippen

            l_cond_score :=
                cnd_nen2767_meth3_cond_score (l_cond_score_corr_str,
                                              l_cond_omvang_str);
        END IF;


        UPDATE ins_deel
           SET ins_deel_mjb_score1 = l_cond_score
         WHERE ins_deel_key = rec_gebrek.ins_deel_key;

        IF l_facilitor_prs_perslid_key IS NOT NULL
        THEN
            fac.trackaction (
                '#INSUPD',
                rec_gebrek.ins_deel_key,
                l_facilitor_prs_perslid_key,
                NULL,
                   'Conditiescore is op '
                || COALESCE (l_cond_score, 'leeg')
                || ' gezet');
        END IF;

        -- Nu nog de prioriteitsscore per object instellen
        l_prio_score := cnd_nen2767_prio_score (rec_gebrek.ins_deel_key);

        UPDATE ins_deel
           SET ins_deel_mjb_score2 = l_prio_score
         WHERE ins_deel_key = rec_gebrek.ins_deel_key;

        IF l_facilitor_prs_perslid_key IS NOT NULL
        THEN
            IF l_prio_score IS NULL
            THEN
                fac.trackaction ('#INSUPD',
                                 rec_gebrek.ins_deel_key,
                                 l_facilitor_prs_perslid_key,
                                 NULL,
                                 'Prioriteitscore is op leeg gezet');
            ELSE
                fac.trackaction (
                    '#INSUPD',
                    rec_gebrek.ins_deel_key,
                    l_facilitor_prs_perslid_key,
                    NULL,
                    'Prioriteitscore is op ' || l_prio_score || ' gezet');
            END IF;
        END IF;
    -- Omdat 1 melding meerdere objecten kan bevatten, kunnen we nog geen update van melding status doen, eerst moeten alle objecten van zo'n melding worden verwerkt.
    -- Mogelijk ook anders kans op foutmelding Mutating triggers oid.
    -- Dus update status in een nieuwe loop hieronder, nadat deze loop klaar is.
    END LOOP;

    -- LOOP 2B
    FOR rec_gebrek IN c_GRP_nieuwe_gebreken
    LOOP
        UPDATE mld_melding
           SET mld_melding_status = 5
         WHERE mld_melding_key = rec_gebrek.mld_melding_key;

        IF l_facilitor_prs_perslid_key IS NOT NULL
        THEN
            fac.trackaction (
                '#MLDAFM',
                rec_gebrek.mld_melding_key,
                l_facilitor_prs_perslid_key,
                NULL,
                'Gebrek verwerkt in de conditiescore van het object');
        END IF;
    END LOOP;

    COMMIT;
END;
/


CREATE OR REPLACE PROCEDURE cnd_export_nen2767_aggr_score (
    p_applname   IN VARCHAR2,
    p_applrun    IN VARCHAR2,
    p_filedir    IN VARCHAR2,
    p_filename   IN VARCHAR2)
AS
    -- mbmb TODO: aggregate functie op locatie en gebouw
    -- Gebruik hiervoor als basis: zie pagina 14 uit Word document....

    CURSOR c_locatie
    IS
          SELECT alg_locatie_key,
                 SUM (totale_vervangingswaarde)
                     totale_vervangingswaarde,
                 SUM (correctie_vervangingswaarde)
                     correctie_vervangingswaarde,
                 ROUND (
                       SUM (correctie_vervangingswaarde)
                     / SUM (COALESCE(totale_vervangingswaarde,1)),
                     4)
                     factor
            FROM cnd_v_aggr_cond_score
           WHERE totale_vervangingswaarde <> 0
        GROUP BY alg_locatie_key;

    CURSOR c_gebouw
    IS
          SELECT alg_gebouw_key,
                 SUM (totale_vervangingswaarde)
                     totale_vervangingswaarde,
                 SUM (correctie_vervangingswaarde)
                     correctie_vervangingswaarde,
                 ROUND (
                       SUM (correctie_vervangingswaarde)
                     / SUM (COALESCE(totale_vervangingswaarde,1)),
                     4)
                     factor
            FROM cnd_v_aggr_cond_score
           WHERE totale_vervangingswaarde <> 0
        GROUP BY alg_gebouw_key;

    l_gevonden                    NUMBER (10);
    l_facilitor_prs_perslid_key   NUMBER (10);
    l_cond_score_old              NUMBER (10);
    l_cond_score_new              NUMBER (10);
BEGIN
    SELECT COUNT (*)
      INTO l_gevonden
      FROM prs_perslid
     WHERE     prs_perslid_oslogin = '_FACILITOR'
           AND prs_perslid_verwijder IS NULL;

    IF l_gevonden = 1
    THEN
        SELECT prs_perslid_key
          INTO l_facilitor_prs_perslid_key
          FROM prs_perslid
         WHERE     prs_perslid_oslogin = '_FACILITOR'
               AND prs_perslid_verwijder IS NULL;
    END IF;


    FOR locatie IN c_locatie
    LOOP
        SELECT alg_locatie_mjb_score1
          INTO l_cond_score_old
          FROM alg_locatie
         WHERE alg_locatie_key = locatie.alg_locatie_key;

        l_cond_score_new := cnd_nen2767_cond_herleiding (locatie.factor);

        IF l_cond_score_old IS NULL OR l_cond_score_old <> l_cond_score_new
        THEN
            UPDATE alg_locatie
               SET alg_locatie_mjb_score1 = l_cond_score_new
             WHERE alg_locatie_key = locatie.alg_locatie_key;

            IF l_facilitor_prs_perslid_key IS NOT NULL
            THEN
                fac.trackaction (
                    '#ALGLUP',
                    locatie.alg_locatie_key,
                    l_facilitor_prs_perslid_key,
                    NULL,
                       'A.g.v. herberekening is de nieuwe conditiescore: '
                    || l_cond_score_new);
            END IF;
        END IF;
    END LOOP;

    FOR gebouw IN c_gebouw
    LOOP
        SELECT alg_gebouw_mjb_score1
          INTO l_cond_score_old
          FROM alg_gebouw
         WHERE alg_gebouw_key = gebouw.alg_gebouw_key;

        l_cond_score_new := cnd_nen2767_cond_herleiding (gebouw.factor);

        IF l_cond_score_old IS NULL OR l_cond_score_old <> l_cond_score_new
        THEN
            UPDATE alg_gebouw
               SET alg_gebouw_mjb_score1 = l_cond_score_new
             WHERE alg_gebouw_key = gebouw.alg_gebouw_key;

            IF l_facilitor_prs_perslid_key IS NOT NULL
            THEN
                fac.trackaction (
                    '#ALGGUP',
                    gebouw.alg_gebouw_key,
                    l_facilitor_prs_perslid_key,
                    NULL,
                       'A.g.v. herberekening is de nieuwe conditiescore: '
                    || l_cond_score_new);
            END IF;
        END IF;
    END LOOP;

    COMMIT;
END;
/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- PLAT#53170: Koppelen van soort gebrek (mld_stdmelding) aan elementen obv importsheet
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE cnd_import_srtdeel_mlddisc (
    p_import_key   IN NUMBER)
AS
    c_fielddelimitor                VARCHAR2 (1) := ';';
    v_newline                       VARCHAR2 (1000);             -- Input line
    v_aanduiding                    VARCHAR2 (200);
    v_errorhint                     VARCHAR2 (1000);
    v_errormsg                      VARCHAR2 (1000);
    oracle_err_num                  NUMBER;
    oracle_err_mes                  VARCHAR2 (200);
    header_is_valid                 NUMBER := 0;
    v_ongeldig                      NUMBER (1);
    v_count_tot                     NUMBER (10);
    v_count_import                  NUMBER (10);
    v_count                         NUMBER;

    -- De importvelden:
    v_ins_srtdeel_code              VARCHAR2 (1000);
    v_ins_srtgroep_omschrijving     VARCHAR2 (1000);
    v_ins_discipline_omschrijving   VARCHAR2 (1000);
    v_mld_discipline_omschrijving   VARCHAR2 (1000);

    CURSOR c1
    IS
          SELECT *
            FROM fac_imp_file
           WHERE fac_import_key = p_import_key
        ORDER BY fac_imp_file_index;
BEGIN
    v_errorhint := 'cnd_import_srtdeel_mlddisc';

    v_count_tot := 0;
    v_count_import := 0;

    FOR rec1 IN c1
    LOOP
        BEGIN
            v_newline := rec1.fac_imp_file_line;
            v_aanduiding := '';
            v_errorhint := 'Fout bij opvragen te importeren rij';
            v_ongeldig := 0;
            -- Lees alle veldwaarden

            -- 01 - v_ins_srtdeel_code
            v_aanduiding := 'Probleem bij v_ins_srtdeel_code: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 1,
                                 v_ins_srtdeel_code);
            v_ins_srtdeel_code := TRIM (v_ins_srtdeel_code);

            -- 03 - v_ins_discipline_omschrijving
            v_aanduiding := 'Probleem bij v_ins_discipline_omschrijving: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 3,
                                 v_ins_discipline_omschrijving);
            v_ins_discipline_omschrijving :=
                TRIM (v_ins_discipline_omschrijving);

            -- 04 - v_ins_srtgroep_omschrijving
            v_aanduiding := 'Probleem bij v_ins_srtgroep_omschrijving: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 4,
                                 v_ins_srtgroep_omschrijving);
            v_ins_srtgroep_omschrijving := TRIM (v_ins_srtgroep_omschrijving);

            -- 07 - v_mld_discipline_omschrijving
            v_aanduiding := 'Probleem bij v_mld_discipline_omschrijving: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 7,
                                 v_mld_discipline_omschrijving);
            v_mld_discipline_omschrijving :=
                TRIM (v_mld_discipline_omschrijving);

            --
            v_aanduiding :=
                   '['
                || v_ins_srtdeel_code
                || '-'
                || v_ins_discipline_omschrijving
                || '-'
                || v_ins_srtgroep_omschrijving
                || '-'
                || v_mld_discipline_omschrijving
                || '] ';

            -- Ik controleer of ik een geldige header heb, dat is: in de juiste kolommen
            -- de juiste kolomkop. Ik controleer daarbij de volgende kolommen:
            -- 1. Subrecept nummer
            -- 3. Hoofdrecept
            -- 4. Groep
            -- 7. Vakgroep
            -- Ik negeer alles totdat ik een geldige header ben gepasseerd.
            IF (header_is_valid = 0)
            THEN
                IF     UPPER (v_ins_srtdeel_code) = 'SUBRECEPT NUMMER'
                   AND UPPER (v_ins_discipline_omschrijving) = 'HOOFDRECEPT'
                   AND UPPER (v_ins_srtgroep_omschrijving) = 'GROEP'
                   AND UPPER (v_mld_discipline_omschrijving) = 'VAKGROEP'
                THEN
                    header_is_valid := 1;
                END IF;
            ELSE
                v_count_tot := v_count_tot + 1;

                v_errorhint := 'Ongeldig Subreceptnummer';

                IF (v_ins_srtdeel_code IS NOT NULL)
                THEN
                    IF LENGTH (v_ins_srtdeel_code) > 10
                    THEN
                        v_ins_srtdeel_code :=
                            SUBSTR (v_ins_srtdeel_code, 1, 10);
                        fac.imp_writelog (
                            p_import_key,
                            'W',
                            v_aanduiding || 'Subreceptnummer te lang',
                            'Afgebroken tot [' || v_ins_srtdeel_code || ']');
                    END IF;
                ELSE
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint := 'Ongeldig hoofdrecept';

                IF (v_ins_discipline_omschrijving IS NOT NULL)
                THEN
                    IF LENGTH (v_ins_discipline_omschrijving) > 60
                    THEN
                        v_ins_discipline_omschrijving :=
                            SUBSTR (v_ins_discipline_omschrijving, 1, 60);
                        fac.imp_writelog (
                            p_import_key,
                            'W',
                            v_aanduiding || 'Hoofdrecept te lang',
                               'Afgebroken tot ['
                            || v_ins_discipline_omschrijving
                            || ']');
                    END IF;
                ELSE
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint := 'Ongeldige groep';

                IF (v_ins_srtgroep_omschrijving IS NOT NULL)
                THEN
                    IF LENGTH (v_ins_srtgroep_omschrijving) > 60
                    THEN
                        v_ins_srtgroep_omschrijving :=
                            SUBSTR (v_ins_srtgroep_omschrijving, 1, 60);
                        fac.imp_writelog (
                            p_import_key,
                            'W',
                            v_aanduiding || 'Groep te lang',
                               'Afgebroken tot ['
                            || v_ins_srtgroep_omschrijving
                            || ']');
                    END IF;
                ELSE
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint := 'Ongeldige vakgroep';

                IF (v_mld_discipline_omschrijving IS NOT NULL)
                THEN
                    IF LENGTH (v_mld_discipline_omschrijving) > 60
                    THEN
                        v_mld_discipline_omschrijving :=
                            SUBSTR (v_mld_discipline_omschrijving, 1, 60);
                        fac.imp_writelog (
                            p_import_key,
                            'W',
                            v_aanduiding || 'Vakgroep te lang',
                               'Afgebroken tot ['
                            || v_mld_discipline_omschrijving
                            || ']');
                    END IF;
                ELSE
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                IF (v_ongeldig = 0)
                THEN
                    BEGIN
                        v_errorhint :=
                            'Fout bij toevoegen regel aan importtabel plat_imp_srtdeel_mlddiscipline';

                        INSERT INTO cnd_imp_srtdeel_mlddiscipline (
                                        fac_import_key,
                                        ins_srtdeel_code,
                                        ins_discipline_omschrijving,
                                        ins_srtgroep_omschrijving,
                                        mld_discipline_omschrijving)
                             VALUES (p_import_key,
                                     v_ins_srtdeel_code,
                                     v_ins_discipline_omschrijving,
                                     v_ins_srtgroep_omschrijving,
                                     v_mld_discipline_omschrijving);

                        v_count_import := v_count_import + 1;
                        COMMIT;
                    EXCEPTION
                        WHEN OTHERS
                        THEN
                            oracle_err_num := SQLCODE;
                            oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
                            v_errormsg :=
                                   v_errorhint
                                || ' ORACLE (error '
                                || oracle_err_num
                                || '/'
                                || oracle_err_mes
                                || ')';
                            fac.imp_writelog (
                                p_import_key,
                                'E',
                                v_aanduiding || v_errormsg,
                                'Ingelezen regel kan niet worden weggeschreven!');
                            COMMIT;
                    END;
                END IF;
            END IF;
        END;
    END LOOP;

    IF (header_is_valid = 0)
    THEN
        fac.imp_writelog (
            p_import_key,
            'E',
            'Ongeldig importbestand',
            'Geen header of header niet volgens specificatie! Header kolommen: 1. Subrecept nummer, 3. Hoofdrecept, 4. Groep en 7. Vakgroep');
    ELSE
        fac.imp_writelog (
            p_import_key,
            'S',
               'IMPORT SRTDEEL_MLDDISC: aantal ingelezen regels: '
            || TO_CHAR (v_count_tot)
            || ' / aantal geldige regels: '
            || TO_CHAR (v_count_import),
            '');
    END IF;


    COMMIT;
EXCEPTION
    WHEN OTHERS
    THEN
        oracle_err_num := SQLCODE;
        oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
        v_errormsg := 'Error ' || oracle_err_num || '/' || oracle_err_mes;
        fac.imp_writelog (p_import_key,
                          'E',
                          v_errormsg,
                          v_errorhint);
END cnd_import_srtdeel_mlddisc;
/



CREATE OR REPLACE PROCEDURE plat_update_srtdeel_mlddisc (
    p_import_key   IN NUMBER)
IS
    -- Alle ingelezen records
    CURSOR c1
    IS
        SELECT *
          FROM cnd_imp_srtdeel_mlddiscipline
         WHERE fac_import_key = p_import_key;

    v_aanduiding           VARCHAR2 (200);
    v_errorhint            VARCHAR2 (1000);
    v_errormsg             VARCHAR2 (1000);
    oracle_err_num         NUMBER;
    oracle_err_mes         VARCHAR2 (200);
    v_count_tot            NUMBER (10);
    v_count_import         NUMBER (10);

    v_ins_srtdeel_key      NUMBER (10);
    v_mld_discipline_key   NUMBER (10);
BEGIN
    v_count_tot := 0;
    v_count_import := 0;

    FOR rec IN c1
    LOOP
        BEGIN
            v_aanduiding :=
                   '['
                || rec.ins_srtdeel_code
                || '-'
                || rec.ins_srtgroep_omschrijving
                || '-'
                || rec.ins_srtgroep_omschrijving
                || '-'
                || rec.ins_srtgroep_omschrijving
                || '] ';

            v_errorhint := 'MAX(sd.ins_srtdeel_key) levert NULL op';

            SELECT MAX (sd.ins_srtdeel_key)
              INTO v_ins_srtdeel_key
              FROM ins_srtdeel sd, ins_srtgroep g, ins_discipline d
             WHERE     sd.ins_srtgroep_key = g.ins_srtgroep_key
                   AND g.ins_discipline_key = d.ins_discipline_key
                   AND sd.ins_srtdeel_code_upper =
                       UPPER (rec.ins_srtdeel_code)
                   AND g.ins_srtgroep_upper =
                       UPPER (ins_srtgroep_omschrijving)
                   AND d.ins_discipline_omschrijving =
                       rec.ins_discipline_omschrijving
                   AND d.ins_discipline_verwijder IS NULL
                   AND g.ins_srtgroep_verwijder IS NULL
                   AND sd.ins_srtdeel_verwijder IS NULL;

            v_errorhint := 'MAX(d.ins_discipline_key) levert NULL op';

            SELECT MAX (d.ins_discipline_key)
              INTO v_mld_discipline_key
              FROM mld_discipline d
             WHERE     d.ins_discipline_omschrijving =
                       rec.mld_discipline_omschrijving
                   AND d.ins_discipline_verwijder IS NULL;

            IF v_ins_srtdeel_key IS NULL
            THEN
                fac.imp_writelog (
                    p_import_key,
                    'E',
                    v_aanduiding || ' - Subreceptnummer niet gevonden',
                    v_errorhint);
            END IF;

            IF v_mld_discipline_key IS NULL
            THEN
                fac.imp_writelog (
                    p_import_key,
                    'E',
                    v_aanduiding || ' - Vakgroep niet gevonden',
                    v_errorhint);
            END IF;

            IF     v_ins_srtdeel_key IS NOT NULL
               AND v_mld_discipline_key IS NOT NULL
            THEN
                v_errorhint := 'INSERT INTO mld_stdmelding_srtinst faalt...';

                INSERT INTO mld_stdmelding_srtinst (
                                mld_stdmelding_key,
                                ins_srtinstallatie_key,
                                ins_srtinstallatie_niveau)
                    SELECT sm.mld_stdmelding_key, v_ins_srtdeel_key, 'S'
                      FROM mld_stdmelding sm
                     WHERE     sm.mld_ins_discipline_key =
                               v_mld_discipline_key
                           AND sm.mld_stdmelding_verwijder IS NULL
                           AND NOT EXISTS
                                   (SELECT 'x'
                                      FROM mld_stdmelding_srtinst smsi
                                     WHERE     smsi.mld_stdmelding_key =
                                               sm.mld_stdmelding_key
                                           AND smsi.ins_srtinstallatie_key =
                                               v_ins_srtdeel_key
                                           AND smsi.ins_srtinstallatie_niveau =
                                               'S');
            END IF;
        EXCEPTION
            WHEN OTHERS
            THEN
                oracle_err_num := SQLCODE;
                oracle_err_mes := SUBSTR (SQLERRM, 1, 200);
                v_errormsg :=
                       'OTHERS (error '
                    || oracle_err_num
                    || '/'
                    || oracle_err_mes
                    || ')';
                fac.imp_writelog (p_import_key,
                                  'E',
                                  v_aanduiding || v_errormsg,
                                  v_errorhint);
                COMMIT;
        END;
    END LOOP;
EXCEPTION
    WHEN OTHERS
    THEN
        oracle_err_num := SQLCODE;
        oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
        v_errormsg :=
               'OTHERS (error '
            || oracle_err_num
            || '/'
            || oracle_err_mes
            || ')';
        fac.imp_writelog (p_import_key,
                          'E',
                          v_aanduiding || v_errormsg,
                          v_errorhint);
        COMMIT;
END plat_update_srtdeel_mlddisc;
/


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--PLAT#53608: NEN2767 inspectie/gebreken import
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


CREATE OR REPLACE PROCEDURE cnd_import_nen2767_results (
    p_import_key   IN NUMBER)
AS
    c_fielddelimitor             VARCHAR2 (1) := ';';
    v_newline                    VARCHAR2 (1000);                -- Input line
    v_aanduiding                 VARCHAR2 (200);
    v_errorhint                  VARCHAR2 (1000);
    v_errormsg                   VARCHAR2 (1000);
    oracle_err_num               NUMBER;
    oracle_err_mes               VARCHAR2 (200);
    header_is_valid              NUMBER := 0;
    v_ongeldig                   NUMBER (1);
    v_count_tot                  NUMBER (10);
    v_count_import               NUMBER (10);
    v_count                      NUMBER;


    v_alg_gebouw_code            VARCHAR2 (1000);
    v_ins_deel_omschrijving      VARCHAR2 (1000);
    v_mld_melding_datum          VARCHAR2 (1000);
    v_mld_melding_omschrijving   VARCHAR2 (1000);
    v_prs_perslid_email          VARCHAR2 (1000);
    v_gebrekcode                 VARCHAR2 (1000);
    v_ernst                      VARCHAR2 (1000);
    v_omvang                     VARCHAR2 (1000);
    v_intensiteit                VARCHAR2 (1000);
    v_aspect1                    VARCHAR2 (1000);
    v_aspect2                    VARCHAR2 (1000);
    v_aspect3                    VARCHAR2 (1000);
    v_aspect4                    VARCHAR2 (1000);
    v_aspect5                    VARCHAR2 (1000);
    v_aspect6                    VARCHAR2 (1000);
    v_aspect7                    VARCHAR2 (1000);
    v_foto                       VARCHAR2 (1000);
    v_kosten                     VARCHAR2 (1000);
    v_plandatum                  VARCHAR2 (1000);
    v_condscore                  VARCHAR2 (1000);
    v_prioscore                  VARCHAR2 (1000);
    v_gebouwcode                 NUMBER(1) := 0;

    CURSOR c1
    IS
          SELECT *
            FROM fac_imp_file
           WHERE fac_import_key = p_import_key
        ORDER BY fac_imp_file_index;

    FUNCTION get_aspect_value (p_aspect IN VARCHAR2)
        RETURN VARCHAR2
    IS
        v_result   VARCHAR2 (100);
    BEGIN
        v_result := p_aspect;

        IF p_aspect IS NULL
        THEN
            v_result := 'NVT';
        END IF;

        IF p_aspect = '1'
        THEN
            v_result := 'Gering';
        END IF;

        IF p_aspect = '2'
        THEN
            v_result := 'Matig';
        END IF;

        IF p_aspect = '3'
        THEN
            v_result := 'Sterk';
        END IF;

        RETURN v_result;
    END;
BEGIN
    v_errorhint := 'cnd_import_nen2767_results';

    v_count_tot := 0;
    v_count_import := 0;


    FOR rec1 IN c1
    LOOP
        BEGIN
            v_newline := rec1.fac_imp_file_line;
            v_aanduiding := '';
            v_errorhint := 'Fout bij opvragen te importeren rij';
            v_ongeldig := 0;
            -- Lees alle veldwaarden

            -- Voor niet alle klanten is de gebouwcode relevant. Daarom is deze optioneel.
            IF (UPPER(v_aanduiding) LIKE '%GEBOUWCODE%' AND header_is_valid = 0) OR v_gebouwcode = 1
            THEN
               -- 01
               v_aanduiding := 'Probleem bij v_alg_gebouw_code: ';
               fac.imp_getfield_nr (v_newline,
                                    c_fielddelimitor,
                                    1,
                                    v_alg_gebouw_code);
               v_alg_gebouw_code := TRIM (v_alg_gebouw_code);
            ELSE
               v_newline := c_fielddelimitor || v_newline;
               IF header_is_valid = 1
               THEN
                  v_alg_gebouw_code := NULL;
               ELSE
                  v_alg_gebouw_code := 'GEBOUWCODE';
               END IF;
            END IF;

            -- 02
            v_aanduiding := 'Probleem bij v_ins_deel_omschrijving: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 2,
                                 v_ins_deel_omschrijving);
            v_ins_deel_omschrijving := TRIM (v_ins_deel_omschrijving);

            -- 04
            v_aanduiding := 'Probleem bij v_mld_melding_datum: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 4,
                                 v_mld_melding_datum);
            v_mld_melding_datum := TRIM (v_mld_melding_datum);


            -- 06
            v_aanduiding := 'Probleem bij v_gebrekcode: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 6,
                                 v_gebrekcode);
            v_gebrekcode := TRIM (v_gebrekcode);

            -- 08
            v_aanduiding := 'Probleem bij v_ernst: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 8,
                                 v_ernst);
            v_ernst := TRIM (v_ernst);

            -- 09
            v_aanduiding := 'Probleem bij v_omvang: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 9,
                                 v_omvang);
            v_omvang := TRIM (v_omvang);

            -- 10
            v_aanduiding := 'Probleem bij v_intensiteit: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 10,
                                 v_intensiteit);
            v_intensiteit := TRIM (v_intensiteit);

            -- 11
            v_aanduiding := 'Probleem bij v_aspect1: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 11,
                                 v_aspect1);
            v_aspect1 := TRIM (v_aspect1);

            -- 12
            v_aanduiding := 'Probleem bij v_aspect2: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 12,
                                 v_aspect2);
            v_aspect2 := TRIM (v_aspect2);

            -- 13
            v_aanduiding := 'Probleem bij v_aspect3: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 13,
                                 v_aspect3);
            v_aspect3 := TRIM (v_aspect3);

            -- 14
            v_aanduiding := 'Probleem bij v_aspect4: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 14,
                                 v_aspect4);
            v_aspect4 := TRIM (v_aspect4);

            -- 15
            v_aanduiding := 'Probleem bij v_aspect5: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 15,
                                 v_aspect5);
            v_aspect5 := TRIM (v_aspect5);

            -- 16
            v_aanduiding := 'Probleem bij v_aspect6: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 16,
                                 v_aspect6);
            v_aspect6 := TRIM (v_aspect6);

            -- 17
            v_aanduiding := 'Probleem bij v_aspect7: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 17,
                                 v_aspect7);
            v_aspect7 := TRIM (v_aspect7);

            -- 18
            v_aanduiding := 'Probleem bij v_mld_melding_omschrijving: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 18,
                                 v_mld_melding_omschrijving);
            v_mld_melding_omschrijving := TRIM (v_mld_melding_omschrijving);

            -- 19
            v_aanduiding := 'Probleem bij v_condscore: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 19,
                                 v_condscore);
            v_condscore := TRIM (v_condscore);

            -- 20
            v_aanduiding := 'Probleem bij v_prioscore: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 20,
                                 v_prioscore);
            v_prioscore := TRIM (v_prioscore);

            -- 21
            v_aanduiding := 'Probleem bij v_prs_perslid_email: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 21,
                                 v_prs_perslid_email);
            v_prs_perslid_email := TRIM (v_prs_perslid_email);

            -- 22
            v_aanduiding := 'Probleem bij v_foto: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 22,
                                 v_foto);
            v_foto := TRIM (v_foto);

            -- 23
            v_aanduiding := 'Probleem bij v_kosten: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 23,
                                 v_kosten);
            v_kosten := TRIM (v_kosten);

            -- 25
            v_aanduiding := 'Probleem bij v_plandatum: ';
            fac.imp_getfield_nr (v_newline,
                                 c_fielddelimitor,
                                 25,
                                 v_plandatum);
            v_plandatum := TRIM (v_plandatum);

            v_aanduiding :=
                   '['
                || v_alg_gebouw_code
                || '-'
                || v_ins_deel_omschrijving
                || '-'
                || v_gebrekcode
                || '] ';

            -- Ik controleer of ik een geldige header heb, dat is: in de juiste kolommen
            -- de juiste kolomkop. Ik controleer daarbij de volgende kolommen:

            -- 1. Gebouwcode
            -- 2. Objectcode
            -- 4. Datum
            -- 6. Gebrekcode
            -- 8. Ernst
            -- 9. Omvang
            -- 10. Intensiteit
            -- 11. Aspectwaarde1
            -- 12. Aspectwaarde2
            -- 13. Aspectwaarde3
            -- 14. Aspectwaarde4
            -- 15. Aspectwaarde5
            -- 16. Aspectwaarde6
            -- 17. Aspectwaarde7
            -- 18. Opmerking
            -- 19. Conditiescore
            -- 20. Prio
            -- 21. Inspecteur
            -- 22. Foto
            -- 23. Kosten
            -- 24. Vervaldatum
            -- 25. Datum gepland

            -- Ik negeer alles totdat ik een geldige header ben gepasseerd.
            IF (header_is_valid = 0)
            THEN
                IF     UPPER (v_alg_gebouw_code) = 'GEBOUWCODE'
                   AND UPPER (v_ins_deel_omschrijving) = 'OBJECTOMSCHRIJVING'
                   AND UPPER (v_gebrekcode) = 'STDMELDING'
                   AND UPPER (v_ernst) = 'ERNST'
                   AND UPPER (v_omvang) = 'OMVANG'
                   AND UPPER (v_intensiteit) = 'INTENSITEIT'
                   AND UPPER (v_mld_melding_omschrijving) = 'OPMERKING'
                   AND UPPER (v_prs_perslid_email) = 'INSPECTEUR'
                   AND UPPER (v_foto) = 'FOTO'
                THEN
                    header_is_valid := 1;
                ELSE
                    IF UPPER (v_alg_gebouw_code) = 'GEBOUWCODE'
                    THEN
                        fac.imp_writelog (
                            p_import_key,
                            'I',
                               'Header: '
                            || UPPER (v_alg_gebouw_code)
                            || '-'
                            || UPPER (v_ins_deel_omschrijving)
                            || '-'
                            || UPPER (v_gebrekcode)
                            || '-'
                            || UPPER (v_ernst)
                            || '-'
                            || UPPER (v_omvang)
                            || '-'
                            || UPPER (v_intensiteit)
                            || '-'
                            || UPPER (v_mld_melding_omschrijving)
                            || '-'
                            || UPPER (v_prs_perslid_email)
                            || '-'
                            || UPPER (v_foto),
                            '');
                    END IF;
                END IF;
            ELSE
                v_count_tot := v_count_tot + 1;

                v_errorhint :=
                    'Ongeldig kolom GEBOUWCODE (mag maximaal 12 tekens zijn)';

                IF    (v_alg_gebouw_code IS NOT NULL)
                   AND (LENGTH (v_alg_gebouw_code) > 12)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldige kolom OBJECTOMSCHRIJVING (moet gevuld en maximaal 60 tekens zijn)';

                IF    (v_ins_deel_omschrijving IS NULL)
                   OR (LENGTH (v_ins_deel_omschrijving) > 60)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldige kolom DATUM (moet leeg of geldige datum in formaat dd-mm-jjjj zijn)';

                IF     (v_mld_melding_datum IS NOT NULL)
                   AND (fac.safe_to_date (v_mld_melding_datum, 'dd-mm-yyyy')
                            IS NULL)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldig kolom STDMELDING (moet gevuld en maximaal 20 tekens zijn)';

                IF (v_gebrekcode IS NULL) OR (LENGTH (v_gebrekcode) > 20)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldig kolom ERNST (moet gevuld en maximaal 50 tekens zijn)';

                -- Als de codering ingevuld wordt gaan we zelf op zoek naar de omschrijving.
                IF v_ernst IN (1, 2, 3)
                THEN
                    CASE v_ernst
                        WHEN 1
                        THEN
                            v_ernst := 'G - Gering gebrek';
                        WHEN 2
                        THEN
                            v_ernst := 'S - Serieus gebrek';
                        WHEN 3
                        THEN
                            v_ernst := 'E - Ernstig gebrek';
                    END CASE;
                END IF;

                IF (v_ernst IS NULL) OR (LENGTH (v_ernst) > 50)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldig kolom OMVANG (moet 1,2,3,4 of 5 zijn)';

                -- Als de codering ingevuld wordt gaan we zelf op zoek naar de omschrijving.
                IF v_omvang IN (1,
                                2,
                                3,
                                4,
                                5)
                THEN
                    CASE v_omvang
                        WHEN 1
                        THEN
                            v_omvang := '< 2% Incidenteel';
                        WHEN 2
                        THEN
                            v_omvang := '2% tot 10% Plaatselijk';
                        WHEN 3
                        THEN
                            v_omvang := '10% tot 30% Regelmatig';
                        WHEN 4
                        THEN
                            v_omvang := '30% tot 70% Aanzienlijk';
                        WHEN 5
                        THEN
                            v_omvang := '> 70% Algemeen';
                    END CASE;
                END IF;

                IF (v_omvang IS NULL) OR (LENGTH (v_omvang) > 50)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                       'Ongeldig kolom INTENSITEIT (moet 1,2 of 3 zijn): '
                    || v_intensiteit;

                -- Als de codering ingevuld wordt gaan we zelf op zoek naar de omschrijving.
                IF v_intensiteit IN (1, 2, 3)
                THEN
                    CASE v_intensiteit
                        WHEN 1
                        THEN
                            v_intensiteit := '1 - Beginstadium';
                        WHEN 2
                        THEN
                            v_intensiteit := '2 - Gevorderd stadium';
                        WHEN 3
                        THEN
                            v_intensiteit := '3 - Eindstadium';
                    END CASE;
                END IF;

                IF (v_intensiteit IS NULL) OR (LENGTH (v_intensiteit) > 50)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldig kolom ASPECT1 (moet gevuld en maximaal 20 tekens zijn)';
                v_aspect1 := get_aspect_value (v_aspect1);

                IF (v_aspect1 IS NULL) OR (LENGTH (v_aspect1) > 20)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldig kolom ASPECT2 (moet gevuld en maximaal 20 tekens zijn)';
                v_aspect2 := get_aspect_value (v_aspect2);

                IF (v_aspect2 IS NULL) OR (LENGTH (v_aspect2) > 20)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldig kolom ASPECT3 (moet gevuld en maximaal 20 tekens zijn)';
                v_aspect3 := get_aspect_value (v_aspect3);

                IF (v_aspect3 IS NULL) OR (LENGTH (v_aspect3) > 20)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldig kolom ASPECT4 (moet gevuld en maximaal 20 tekens zijn)';
                v_aspect4 := get_aspect_value (v_aspect4);

                IF (v_aspect4 IS NULL) OR (LENGTH (v_aspect4) > 20)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldig kolom ASPECT5 (moet gevuld en maximaal 20 tekens zijn)';
                v_aspect5 := get_aspect_value (v_aspect5);

                IF (v_aspect5 IS NULL) OR (LENGTH (v_aspect5) > 20)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldig kolom ASPECT6 (moet gevuld en maximaal 20 tekens zijn)';
                v_aspect6 := get_aspect_value (v_aspect6);

                IF (v_aspect6 IS NULL) OR (LENGTH (v_aspect6) > 20)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldig kolom ASPECT7 (moet gevuld en maximaal 20 tekens zijn)';
                v_aspect7 := get_aspect_value (v_aspect7);

                IF (v_aspect7 IS NULL) OR (LENGTH (v_aspect7) > 20)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldig kolom OPMERKING (mag maximaal 2000 tekens zijn)';

                IF (LENGTH (v_mld_melding_omschrijving) > 2000)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                           v_errorhint
                        || ' - Opmerking wordt afgekapt op 2000 tekens');
                    v_mld_melding_omschrijving :=
                        SUBSTR (v_mld_melding_omschrijving, 1, 2000);
                END IF;

                --
                v_errorhint :=
                    'Ongeldig kolom INSPECTEUR (moet ingevuld en mag maximaal 200 tekens zijn)';

                IF    (v_prs_perslid_email IS NULL)
                   OR (LENGTH (v_prs_perslid_email) > 200)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldig kolom FOTO (mag maximaal 50 tekens zijn)';

                IF (LENGTH (v_foto) > 50)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldig kolom CONDITIESCORE (indien gevuld dan een cijfer van 1 t/m 6)';

                IF     (v_condscore IS NOT NULL)
                   AND (   (fac.safe_to_number (v_condscore) < 0)
                        OR (fac.safe_to_number (v_condscore) > 6))
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                --
                v_errorhint :=
                    'Ongeldig kolom PRIORITEITSSCORE (indien gevuld dan een cijfer van 1 t/m 9)';

                IF     (v_prioscore IS NOT NULL)
                   AND (   (fac.safe_to_number (v_prioscore) < 0)
                        OR (fac.safe_to_number (v_prioscore) > 9))
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                v_kosten := REPLACE (v_kosten, ',', '.');
                v_errorhint :=
                    'Ongeldig kolom KOSTEN (indien gevuld dan een cijfer met max 2 decimalen)';

                IF     (v_kosten IS NOT NULL)
                   AND (fac.safe_to_number (v_kosten) IS NULL)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                v_errorhint := 'Ongeldig kolom DATUM GEPLAND (Geldige datum)';

                IF     (v_plandatum IS NOT NULL)
                   AND (fac.safe_to_date (v_plandatum, 'dd-mm-yyyy') IS NULL)
                THEN
                    fac.imp_writelog (
                        p_import_key,
                        'W',
                        v_aanduiding,
                        v_errorhint || ' - Regel wordt overgeslagen');
                    v_ongeldig := 1;
                END IF;

                IF (v_ongeldig = 0)
                THEN
                    BEGIN
                        v_errorhint :=
                            'Fout bij toevoegen regel aan importtabel plat_imp_nen2767_results';

                        INSERT INTO cnd_imp_nen2767_results (
                                        fac_import_key,
                                        alg_gebouw_code,
                                        ins_deel_omschrijving,
                                        mld_melding_datum,
                                        mld_melding_omschrijving,
                                        prs_perslid_email,
                                        gebrekcode,
                                        ernst,
                                        omvang,
                                        intensiteit,
                                        aspect1,
                                        aspect2,
                                        aspect3,
                                        aspect4,
                                        aspect5,
                                        aspect6,
                                        aspect7,
                                        kosten,
                                        plandatum,
                                        foto,
                                        condscore,
                                        prioscore)
                             VALUES (p_import_key,
                                     v_alg_gebouw_code,
                                     v_ins_deel_omschrijving,
                                     v_mld_melding_datum,
                                     v_mld_melding_omschrijving,
                                     v_prs_perslid_email,
                                     v_gebrekcode,
                                     v_ernst,
                                     v_omvang,
                                     v_intensiteit,
                                     v_aspect1,
                                     v_aspect2,
                                     v_aspect3,
                                     v_aspect4,
                                     v_aspect5,
                                     v_aspect6,
                                     v_aspect7,
                                     v_kosten,
                                     fac.safe_to_date (v_plandatum, 'dd-mm-yyyy'),
                                     v_foto,
                                     v_condscore,
                                     v_prioscore);

                        v_count_import := v_count_import + 1;
                        COMMIT;
                    EXCEPTION
                        WHEN OTHERS
                        THEN
                            oracle_err_num := SQLCODE;
                            oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
                            v_errormsg :=
                                   v_errorhint
                                || ' ORACLE (error '
                                || oracle_err_num
                                || '/'
                                || oracle_err_mes
                                || ')';
                            fac.imp_writelog (
                                p_import_key,
                                'E',
                                v_aanduiding || v_errormsg,
                                'Ingelezen regel kan niet worden weggeschreven!');
                            COMMIT;
                    END;
                END IF;
            END IF;
        END;
    END LOOP;

    IF (header_is_valid = 0)
    THEN
        fac.imp_writelog (
            p_import_key,
            'E',
            'Ongeldig importbestand',
            'Geen header of header niet volgens specificatie: A. GEBOUWCODE, B. OBJECTOMSCHRIJVING, F. STDMELDING, H.ERNST, I. OMVANG, J. INTENSITEIT, R. OPMERKING, U. INSPECTEUR, V. FOTO');
    ELSE
        fac.imp_writelog (
            p_import_key,
            'S',
               'IMPORT NEN2767 RESULTS: aantal ingelezen regels: '
            || TO_CHAR (v_count_tot)
            || ' / aantal geldige regels: '
            || TO_CHAR (v_count_import),
            '');
    END IF;


    COMMIT;
EXCEPTION
    WHEN OTHERS
    THEN
        oracle_err_num := SQLCODE;
        oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
        v_errormsg := 'Error ' || oracle_err_num || '/' || oracle_err_mes;
        fac.imp_writelog (p_import_key,
                          'E',
                          v_errormsg,
                          v_errorhint);
END cnd_import_nen2767_results;
/


CREATE OR REPLACE PROCEDURE cnd_update_nen2767_results (
    p_import_key   IN NUMBER)
IS
    CURSOR c1
    IS
        SELECT *
          FROM cnd_imp_nen2767_results
         WHERE fac_import_key = p_import_key;



    v_aanduiding                   VARCHAR2 (200);
    v_errorhint                    VARCHAR2 (1000);
    v_errormsg                     VARCHAR2 (1000);
    oracle_err_num                 NUMBER;
    oracle_err_mes                 VARCHAR2 (200);
    v_count_tot                    NUMBER (10);
    v_count_import                 NUMBER (10);

    v_mld_melding_key              NUMBER (10);

    v_alg_gebouw_key               NUMBER (10);
    v_ins_deel_key                 NUMBER (10);
    v_alg_ruimte_key               NUMBER (10);
    v_prs_perslid_key              NUMBER (10);
    v_mld_stdmelding_key           NUMBER (10);

    v_ernst_kenmerk_key            NUMBER (10);
    v_ernst_key                    NUMBER (10);
    v_omvang_kenmerk_key           NUMBER (10);
    v_omvang_key                   NUMBER (10);
    v_intensiteit_kenmerk_key      NUMBER (10);
    v_intensiteit_key              NUMBER (10);

    v_foto_kenmerk_key             NUMBER (10);
    v_kosten_kenmerk_key           NUMBER (10) := NULL;
    v_kosten_srtkenmerk_key        NUMBER (10);

    v_ernst_srtkenmerk_key         NUMBER (10);
    v_omvang_srtkenmerk_key        NUMBER (10);
    v_intensiteit_srtkenmerk_key   NUMBER (10);

    PROCEDURE set_aspect (p_aspect            IN VARCHAR2,
                          p_aspect_waarde     IN VARCHAR2,
                          p_mld_melding_key   IN NUMBER)
    IS
        v_key              NUMBER (10);
        v_kenmerk_key      NUMBER (10);
        v_srtkenmerk_key   NUMBER (10);
    BEGIN
        SELECT MAX (mld_srtkenmerk_key)
          INTO v_srtkenmerk_key
          FROM mld_srtkenmerk
         WHERE mld_srtkenmerk_code = p_aspect;

        IF v_srtkenmerk_key IS NOT NULL
        THEN
            v_errorhint :=
                   p_aspect
                || ' (eigen tabel) niet gevonden (bestaat niet), regel wordt overgeslagen';

            SELECT ud.fac_usrdata_key, k.mld_kenmerk_key
              INTO v_key, v_kenmerk_key
              FROM mld_srtkenmerk     sk,
                   fac_kenmerkdomein  kd,
                   fac_usrtab         ut,
                   fac_usrdata        ud,
                   mld_kenmerk        k
             WHERE     sk.mld_srtkenmerk_key = v_srtkenmerk_key
                   AND kd.fac_kenmerkdomein_key = sk.fac_kenmerkdomein_key
                   AND ut.fac_usrtab_key = kd.fac_usrtab_key
                   AND ud.fac_usrtab_key = kd.fac_usrtab_key
                   AND ud.fac_usrdata_upper = UPPER (p_aspect_waarde)
                   AND k.mld_srtkenmerk_key = sk.mld_srtkenmerk_key;

            mld.upsertmeldingkenmerk (v_kenmerk_key,
                                      p_mld_melding_key,
                                      v_key);
        END IF;
    END;
BEGIN
    v_count_tot := 0;
    v_count_import := 0;


    v_errorhint :=
        'Ernst kenmerk bij gebreken niet gevonden (bestaat niet), import wordt niet uitgevoerd';

    SELECT mld_srtkenmerk_key
      INTO v_ernst_srtkenmerk_key
      FROM mld_srtkenmerk
     WHERE mld_srtkenmerk_code = 'ERNST';

    v_errorhint :=
        'Omvang kenmerk bij gebreken niet gevonden (bestaat niet), import wordt niet uitgevoerd';

    SELECT mld_srtkenmerk_key
      INTO v_omvang_srtkenmerk_key
      FROM mld_srtkenmerk
     WHERE mld_srtkenmerk_code = 'OMVANG';

    v_errorhint :=
        'Intensiteit kenmerk bij gebreken niet gevonden (bestaat niet), import wordt niet uitgevoerd';

    SELECT mld_srtkenmerk_key
      INTO v_intensiteit_srtkenmerk_key
      FROM mld_srtkenmerk
     WHERE mld_srtkenmerk_code = 'INTENSITEIT';

    v_errorhint :=
        'Foto kenmerk bij gebreken niet gevonden (bestaat niet), import wordt niet uitgevoerd';

    SELECT k.mld_kenmerk_key
      INTO v_foto_kenmerk_key
      FROM mld_kenmerk k, mld_srtkenmerk sk
     WHERE     k.mld_srtkenmerk_key = sk.mld_srtkenmerk_key
           AND sk.mld_srtkenmerk_code = 'FOTO';

    v_errorhint :=
        'Kosten kenmerk bij gebreken niet gevonden (bestaat niet), import wordt niet uitgevoerd';

    SELECT MAX (mld_srtkenmerk_key)
      INTO v_kosten_srtkenmerk_key
      FROM mld_srtkenmerk
     WHERE mld_srtkenmerk_code = 'KOSTEN';

    IF v_kosten_srtkenmerk_key IS NOT NULL
    THEN
        SELECT mld_kenmerk_key
          INTO v_kosten_kenmerk_key
          FROM mld_kenmerk
         WHERE     mld_srtkenmerk_key = v_kosten_srtkenmerk_key;
    END IF;

    FOR rec IN c1
    LOOP
        BEGIN
            v_count_tot := v_count_tot + 1;
            v_aanduiding :=
                   '['
                || rec.alg_gebouw_code
                || '-'
                || rec.ins_deel_omschrijving
                || '-'
                || rec.gebrekcode
                || '] ';

            v_errorhint :=
                'Gebouwcode niet gevonden (bestaat niet), regel wordt overgeslagen';

            v_alg_gebouw_key := -1;

            IF rec.alg_gebouw_code IS NOT NULL
            THEN
                SELECT alg_gebouw_key
                  INTO v_alg_gebouw_key
                  FROM alg_v_aanweziggebouw
                 WHERE alg_gebouw_upper = UPPER (rec.alg_gebouw_code);
            END IF;

            v_errorhint :=
                'Element (object) niet gevonden (bestaat niet), regel wordt overgeslagen';

            -- Uitgangspunt hierbij is dat objectnaam uniek is per gebouw, op zich mag in Facilitor de objectnaam per objectsoort per locatie uniek zijn.
            SELECT ins_deel_key, r.alg_ruimte_key
              INTO v_ins_deel_key, v_alg_ruimte_key
              FROM ins_v_aanwezigdeel, alg_ruimte r, alg_verdieping v
             WHERE     ins_deel_upper = UPPER (rec.ins_deel_omschrijving)
                   AND ins_alg_ruimte_key = r.alg_ruimte_key
                   AND r.alg_verdieping_key = v.alg_verdieping_key
                   AND (   v_alg_gebouw_key = -1
                        OR v.alg_gebouw_key = v_alg_gebouw_key)
                   AND ins_alg_ruimte_type = 'R';

            v_errorhint :=
                'Mailadres van inspecteur (persoon) niet gevonden (bestaat niet), regel wordt overgeslagen';

            SELECT prs_perslid_key
              INTO v_prs_perslid_key
              FROM prs_v_aanwezigperslid
             WHERE UPPER (prs_perslid_email) = UPPER (rec.prs_perslid_email);


            v_errorhint :=
                'Gebrek (melding) niet gevonden (bestaat niet), regel wordt overgeslagen';

            SELECT sm.mld_stdmelding_key
              INTO v_mld_stdmelding_key
              FROM mld_stdmelding sm
             WHERE UPPER(mld_stdmelding_code) = UPPER (rec.gebrekcode);


            v_errorhint :=
                'Ernst (eigen tabel) niet gevonden (bestaat niet), regel wordt overgeslagen';

            SELECT ud.fac_usrdata_key, k.mld_kenmerk_key
              INTO v_ernst_key, v_ernst_kenmerk_key
              FROM mld_srtkenmerk     sk,
                   fac_kenmerkdomein  kd,
                   fac_usrtab         ut,
                   fac_usrdata        ud,
                   mld_kenmerk        k
             WHERE     sk.mld_srtkenmerk_key = v_ernst_srtkenmerk_key
                   AND kd.fac_kenmerkdomein_key = sk.fac_kenmerkdomein_key
                   AND ut.fac_usrtab_key = kd.fac_usrtab_key
                   AND ud.fac_usrtab_key = kd.fac_usrtab_key
                   AND ud.fac_usrdata_upper = UPPER (rec.ernst)
                   AND k.mld_srtkenmerk_key = sk.mld_srtkenmerk_key
                   AND k.mld_stdmelding_key = v_mld_stdmelding_key
                   AND k.mld_kenmerk_niveau = 'S';

            v_errorhint :=
                'Omvang (eigen tabel) niet gevonden (bestaat niet), regel wordt overgeslagen';

            SELECT ud.fac_usrdata_key, k.mld_kenmerk_key
              INTO v_omvang_key, v_omvang_kenmerk_key
              FROM mld_srtkenmerk     sk,
                   fac_kenmerkdomein  kd,
                   fac_usrtab         ut,
                   fac_usrdata        ud,
                   mld_kenmerk        k
             WHERE     sk.mld_srtkenmerk_key = v_omvang_srtkenmerk_key
                   AND kd.fac_kenmerkdomein_key = sk.fac_kenmerkdomein_key
                   AND ut.fac_usrtab_key = kd.fac_usrtab_key
                   AND ud.fac_usrtab_key = kd.fac_usrtab_key
                   AND ud.fac_usrdata_upper = UPPER (rec.omvang)
                   AND k.mld_srtkenmerk_key = sk.mld_srtkenmerk_key
                   AND k.mld_stdmelding_key = v_mld_stdmelding_key
                   AND k.mld_kenmerk_niveau = 'S';

            v_errorhint :=
                'Intensiteit (eigen tabel) niet gevonden (bestaat niet), regel wordt overgeslagen';

            SELECT ud.fac_usrdata_key, k.mld_kenmerk_key
              INTO v_intensiteit_key, v_intensiteit_kenmerk_key
              FROM mld_srtkenmerk     sk,
                   fac_kenmerkdomein  kd,
                   fac_usrtab         ut,
                   fac_usrdata        ud,
                   mld_kenmerk        k
             WHERE     sk.mld_srtkenmerk_key = v_intensiteit_srtkenmerk_key
                   AND kd.fac_kenmerkdomein_key = sk.fac_kenmerkdomein_key
                   AND ut.fac_usrtab_key = kd.fac_usrtab_key
                   AND ud.fac_usrtab_key = kd.fac_usrtab_key
                   AND ud.fac_usrdata_upper = UPPER (rec.intensiteit)
                   AND k.mld_srtkenmerk_key = sk.mld_srtkenmerk_key
                   AND k.mld_stdmelding_key = v_mld_stdmelding_key
                   AND k.mld_kenmerk_niveau = 'S';

            v_errorhint :=
                'Toevoegen melding';
            INSERT INTO mld_melding (mld_melding_module,
                                     mld_meldbron_key,
                                     mld_melding_datum,
                                     mld_melding_omschrijving,
                                     mld_stdmelding_key,
                                     mld_alg_onroerendgoed_keys,
                                     mld_melding_einddatum,
                                     prs_perslid_key,
                                     prs_perslid_key_voor,
                                     mld_melding_status,
                                     mld_melding_spoed)
                 VALUES (
                            'MLD',
                            6,
                            fac.safe_to_date (rec.mld_melding_datum,
                                              'dd-mm-yyyy'),
                            rec.mld_melding_omschrijving,
                            v_mld_stdmelding_key,
                            v_alg_ruimte_key,
                            rec.plandatum,
                            v_prs_perslid_key,
                            v_prs_perslid_key,
                            2,
                            3)
              RETURNING mld_melding_key
                   INTO v_mld_melding_key;

            v_errorhint := 'Koppelen object';
            INSERT INTO mld_melding_object (mld_melding_key, ins_deel_key)
                 VALUES (v_mld_melding_key, v_ins_deel_key);

            v_errorhint := 'Toevoegen Ernst kenmerk';
            mld.upsertmeldingkenmerk (v_ernst_kenmerk_key,
                                      v_mld_melding_key,
                                      v_ernst_key);
            v_errorhint := 'Toevoegen Omvang kenmerk';
            mld.upsertmeldingkenmerk (v_omvang_kenmerk_key,
                                      v_mld_melding_key,
                                      v_omvang_key);
            v_errorhint := 'Toevoegen Intensiteit kenmerk';
            mld.upsertmeldingkenmerk (v_intensiteit_kenmerk_key,
                                      v_mld_melding_key,
                                      v_intensiteit_key);
            set_aspect ('ASPECT1', rec.aspect1, v_mld_melding_key);
            set_aspect ('ASPECT2', rec.aspect2, v_mld_melding_key);
            set_aspect ('ASPECT3', rec.aspect3, v_mld_melding_key);
            set_aspect ('ASPECT4', rec.aspect4, v_mld_melding_key);
            set_aspect ('ASPECT5', rec.aspect5, v_mld_melding_key);
            set_aspect ('ASPECT6', rec.aspect6, v_mld_melding_key);
            set_aspect ('ASPECT7', rec.aspect7, v_mld_melding_key);

            v_errorhint := 'Toevoegen Foto kenmerk';
            IF rec.foto IS NOT NULL
            THEN
                mld.upsertmeldingkenmerk (v_foto_kenmerk_key,
                                          v_mld_melding_key,
                                          rec.foto);
            END IF;

            v_errorhint := 'Toevoegen Kosten kenmerk';
            IF rec.kosten IS NOT NULL AND v_kosten_kenmerk_key IS NOT NULL
            THEN
                mld.upsertmeldingkenmerk (v_kosten_kenmerk_key,
                                          v_mld_melding_key,
                                          TO_CHAR(rec.kosten));
            END IF;

            v_count_import := v_count_import + 1;
            COMMIT;
        EXCEPTION
            WHEN OTHERS
            THEN
                oracle_err_num := SQLCODE;
                oracle_err_mes := SUBSTR (SQLERRM, 1, 200);
                v_errormsg :=
                       'OTHERS (error '
                    || oracle_err_num
                    || '/'
                    || oracle_err_mes
                    || ')';
                fac.imp_writelog (p_import_key,
                                  'E',
                                  v_aanduiding || v_errormsg,
                                  v_errorhint);
                COMMIT;
        END;
    END LOOP;

    fac.imp_writelog (
        p_import_key,
        'S',
           'UPDATE NEN2767_RESULTS: aantal ingelezen regels: '
        || TO_CHAR (v_count_tot)
        || ' / aantal geldige regels: '
        || TO_CHAR (v_count_import),
        '');
EXCEPTION
    WHEN OTHERS
    THEN
        oracle_err_num := SQLCODE;
        oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
        v_errormsg :=
               'OTHERS (error '
            || oracle_err_num
            || '/'
            || oracle_err_mes
            || ')';
        fac.imp_writelog (p_import_key,
                          'E',
                          v_aanduiding || v_errormsg,
                          v_errorhint);
        COMMIT;
END cnd_update_nen2767_results;
/



------ payload end   ------

SET DEFINE OFF
BEGIN adm.systrackscriptId ('$Id$', 0); END;
/

COMMIT;
SET ECHO OFF
SPOOL OFF
SET DEFINE ON
PROMPT Logfile of this upgrade is: &fcltlogfile