-- Script containing customer specific configuration sql statements for SKAF
-- (c) 2009 SG|facilitor bv
-- $Revision$
-- $Id$
--
-- Support: +31 53 4800710

DEFINE thisfile = 'SKAF.SQL'
DEFINE dbuser   = 'SKAF'

SET ECHO ON
SET DEFINE ON
COLUMN fcltlogfile NEW_VALUE fcltlogfile NOPRINT;
COLUMN fcltcusttxt NEW_VALUE fcltcusttxt NOPRINT;
WHENEVER SQLERROR CONTINUE;
SELECT adm.getscriptspoolfile('&thisfile') AS fcltlogfile FROM DUAL;
SPOOL &fcltlogfile
WHENEVER SQLERROR EXIT;
SELECT adm.checkscriptcust('&dbuser') AS fcltcusttxt FROM DUAL;
WHENEVER SQLERROR CONTINUE;
---------------------------------------
PROMPT &fcltcusttxt
---------------------------------------
SET DEFINE OFF

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


CREATE OR REPLACE VIEW skaf_v_import_log
AS
   SELECT imp_log_status fclt_f_status,
          imp_log_omschrijving omschrijving,
          imp_log_hint opmerking
   FROM imp_log
   WHERE imp_log_applicatie = 'PRS';


-- Mail2Melding - Basis uit SVRZ overgenomen:
--- IT SKAF -: key= 861= Melding ICT Overig/Restmail (ict@skaf.facilitor.nl)
CREATE OR REPLACE PROCEDURE SKAF_processemail (
   pfrom        IN VARCHAR2,
   pto          IN VARCHAR2,
   psubject     IN VARCHAR2,
   pbody        IN VARCHAR2,
   psessionid   IN VARCHAR2,
   pemailkey    IN NUMBER)
AS
   c_onbekend_key       NUMBER (10) := 10001 ;  --  PROD = 10001;           -- Melder onbekend
   v_errormsg           VARCHAR2 (1000);
   oracle_err_num       NUMBER;
   oracle_err_mes       VARCHAR2 (200);
   v_exist_ticket       VARCHAR2 (255) := '';
   v_perslid_key        NUMBER (10);
   v_kostenplaats_key   NUMBER (10);
   v_locatie_key        NUMBER (10);
   v_discipline_key     NUMBER (10);
   v_stdmelding_key     NUMBER (10);
   v_melding_key        NUMBER (10);
   v_folder_kkey        NUMBER (10);
   v_count              NUMBER (10);
   v_count_wp           NUMBER (10);
   v_fac_result_from    VARCHAR2 (250);
   v_fac_result_fromaddr VARCHAR2 (250);
   v_fw_from            VARCHAR2 (1000) := '';
   v_from_string        VARCHAR2 (1000) := '';
   v_voor_at            VARCHAR2 (1000) := '';
   v_na_at              VARCHAR2 (1000) := '';

  
BEGIN
   CASE
      WHEN UPPER (pto) LIKE 'ICT@%'
      THEN
      
-- 1. HANDMATIG doorgestuurd vanuit ICT@ska.nl - gaan we eerst de van-regel eruit halen
-----  From: Brandon van Gelder | Ska <b.vangelder@ska.nl<mailto:b.vangelder@ska.nl>>
IF INSTR (pbody, '@',1,1)>0 AND REGEXP_INSTR(pbody,'Van:|From:|E-mail:',1,1)>0 AND (INSTR (pbody, '@',1,1)-REGEXP_INSTR(pbody,'Van:|From:|E-mail:',1,1)) >0   -- Er moet een Van-regel inclusief een @ in staan  
   THEN
   v_from_string := 
        SUBSTR(pbody,
                REGEXP_INSTR(pbody,'Van:|From:|E-mail:',1,1),
                INSTR(pbody,CHR(10),REGEXP_INSTR(pbody,'Van:|From:|E-mail:',1,1),1)-REGEXP_INSTR(pbody,'Van:|From:|E-mail:',1,1)) ;

    IF INSTR(v_from_string, '<',1,1)>0
            THEN
            v_na_at :=
                SUBSTR(v_from_string,
                    INSTR(v_from_string,'@',1,1)+1,
                    INSTR(v_from_string,'.nl')+3 - INSTR(v_from_string,'@',1,1)-1) ;

            v_voor_at :=
                SUBSTR (v_from_string,
                    INSTR (v_from_string, '<',1,1) + 1,
                    INSTR (v_from_string, '@',1,1) - INSTR (v_from_string, '<',1,1) -1);

            v_fw_from := v_voor_at || '@' || v_na_at;

            ELSE
            v_na_at :=
                SUBSTR(v_from_string,
                    INSTR(v_from_string,'@',1,1)+1,
                    REGEXP_INSTR(v_from_string,'$')-INSTR(v_from_string,'@',1,1)-1) ;

            v_voor_at :=
                SUBSTR (v_from_string,
                    INSTR (v_from_string, ':',1,1) + 2,
                    INSTR (v_from_string, '@',1,1) - INSTR (v_from_string, ':',1,1) -2);

            v_fw_from := v_voor_at || '@' || v_na_at;

    END IF;
                    
    ELSE
    -- 2. AUTOMATISCH doorgestuurde mail  OF geen "Van: met email-adres" in de body. Dan dus maar terugvallen op de v_fromaddr...
         SELECT fac_result_waarde 
         INTO v_fac_result_fromaddr
         FROM fac_result WHERE fac_result_sessionid = psessionid AND fac_result_naam = 'fromaddr' ;    
         
         v_fac_result_fromaddr := REPLACE(REPLACE(v_fac_result_fromaddr,'>',''),'<','') ;
         
         v_fw_from := v_fac_result_fromaddr;
    
END IF;

        v_fw_from := SUBSTR (v_fw_from, 1, 50);
      
         -- Bepaal de melder/noteur op basis van e-mail adres zoals in pfrom?
         v_errormsg := 'Fout bepalen melder ' || COALESCE (pfrom, '???');
         SELECT COUNT (*), max(prs_perslid_key)
           INTO v_count, v_perslid_key
           FROM prs_v_aanwezigperslid
          WHERE UPPER (prs_perslid_email) LIKE UPPER (v_fw_from) || '%';

         -- Als er persoon gevonden is, dan ff checken of er ook werkplekkoppelingen onder zijn aangemaakt 
         IF v_count = 1
         THEN
         SELECT COUNT (*)
           INTO v_count_wp
           FROM prs_perslidwerkplek
          WHERE prs_perslid_key = v_perslid_key;
         END IF; 

         -- Als er persoon gevonden is, dan ff checken of er op gekoppelde Afdeling ook een kostenplaats is gekoppeld 
         IF v_count = 1
         THEN
         SELECT MIN(a.prs_kostenplaats_key)
           INTO v_kostenplaats_key
           FROM prs_v_aanwezigperslid p, prs_afdeling a
          WHERE prs_perslid_key = v_perslid_key AND p.prs_afdeling_key = a.prs_afdeling_key ;
         END IF; 

         -- Locatie erbij halen
         IF v_count = 1 AND v_count_wp > 0
         THEN
              SELECT p.prs_perslid_key, MIN (wpg.alg_locatie_key)
                INTO v_perslid_key, v_locatie_key
                FROM prs_v_aanwezigperslid p,
                     (SELECT pw.prs_perslid_key, pw.prs_werkplek_key
                        FROM prs_perslidwerkplek pw
                       WHERE NOT EXISTS
                                (SELECT 1
                                   FROM prs_perslidwerkplek
                                  WHERE prs_perslid_key = pw.prs_perslid_key
                                        AND prs_perslidwerkplek_key > pw.prs_perslidwerkplek_key)) pw,
                     prs_v_werkplekperslid_gegevens wpg
               WHERE p.prs_perslid_key = v_perslid_key
                     AND p.prs_perslid_key = pw.prs_perslid_key(+)
                     AND pw.prs_werkplek_key = wpg.prs_werkplek_key(+)
            GROUP BY p.prs_perslid_key;
         END IF;
         
         -- Geen werkplek of Melder onbekend - Dan melding op locatie_key 922 (Servicecentrum Amsterdamseweg) 
         IF (v_count = 1 AND v_count_wp = 0) OR v_count = 0
            THEN
              v_locatie_key := 922; 
         END IF;

         -- Melder kan niet bepaald worden, dus inlezen onder 'Melder onbekend (M2M)'
         -- Op deze 'Melder onbekend' is aan voorkant wel afdeling ICT en bijbehorende kostenplaats gekoppeld. Deze kp halen we er daarom nu ook bij
         IF v_count = 0 
            THEN
            SELECT p.prs_perslid_key, a.prs_kostenplaats_key
              INTO v_perslid_key, v_kostenplaats_key
              FROM prs_v_aanwezigperslid p, prs_afdeling a
             WHERE p.prs_afdeling_key = a.prs_afdeling_key
                   AND p.prs_perslid_key = c_onbekend_key;
        END IF;

         v_errormsg := 'Fout bepalen bestaande melding';
         v_exist_ticket := psubject;
         IF REGEXP_INSTR (v_exist_ticket, '\d') > 0
         THEN
            v_exist_ticket := SUBSTR (v_exist_ticket, REGEXP_INSTR (v_exist_ticket, '\d')) || 'x'; -- Gedeelte vanaf eerste cijfer in v_exist_ticket plus 'x'!
            v_exist_ticket := SUBSTR (v_exist_ticket, 1, REGEXP_INSTR (v_exist_ticket, '\D') - 1); -- Gedeelte tot eerste niet-cijfer in v_exist_ticket!
         ELSE
            v_exist_ticket := 'x';
         END IF;

         SELECT COUNT (*)
           INTO v_count
           FROM mld_melding
          WHERE mld_melding_key = fac.safe_to_number (v_exist_ticket);

         -- Om te voorkomen dat huisnummers in het Onderwerp resulteren in het
         -- toevoegen van Notities, dit alleen doen voor nummers > 16000!
         IF v_count = 1 AND fac.safe_to_number (v_exist_ticket) > 16000
         THEN                                -- Notitie bij bestaande melding!
            SELECT mld_stdmelding_key, mld_melding_key
              INTO v_stdmelding_key, v_melding_key
              FROM mld_melding
             WHERE mld_melding_key = fac.safe_to_number (v_exist_ticket);

            v_errormsg := 'Fout toevoegen notitie';
            INSERT INTO mld_melding_note (mld_melding_key,
                                          prs_perslid_key,
                                          mld_melding_note_omschrijving,
                                          mld_melding_note_flag)
                VALUES (v_melding_key,
                        v_perslid_key,
                        pfrom || CHR (13) || CHR (10) || psubject || CHR (13) || CHR (10) || SUBSTR (pbody, 1, 2000),
                        0);

            INSERT INTO fac_result (fac_result_sessionid, fac_result_naam, fac_result_waarde)
                VALUES (psessionid, 'maillog', 'Notitie onder exist-melding ' || v_melding_key);

            -- Vlaggetjes zetten zoals mld_edit_note.asp dat doet!
               UPDATE mld_melding
                  SET mld_melding_actiecode = 1 + 128         -- BO + Attentie
                WHERE mld_melding_key = v_melding_key;
         ELSE                                               -- Nieuwe melding!

            -- Bepaal de afgesproken soortmelding met key= 861 Overig - Postbus ICT
            v_errormsg := 'Fout bepalen soortmelding';
            SELECT mld_ins_discipline_key, mld_stdmelding_key
              INTO v_discipline_key, v_stdmelding_key
              FROM mld_stdmelding
             WHERE mld_stdmelding_key = 861;     -- PROD-KEY 861

            -- Suggested extensions:
            -- - Check for MLDUSE-write autorisations
            -- - Parse the subject to find the appropriate stdmelding, if uniquely possible
            -- - Append (as a note?) to an existing melding if #key is found in the subject
            v_errormsg := 'Fout toevoegen melding';
            INSERT INTO mld_melding (mld_melding_module,
                                     mld_meldbron_key,
                                     mld_alg_locatie_key,
                                     mld_alg_onroerendgoed_keys,
                                     mld_melding_datum,
                                     mld_melding_omschrijving,
                                     mld_stdmelding_key,
                                     mld_melding_t_uitvoertijd,
                                     prs_kostenplaats_key,
                                     prs_perslid_key,
                                     prs_perslid_key_voor,
                                     mld_melding_status,
                                     mld_melding_spoed,
                                     mld_melding_onderwerp,
                                     mld_melding_behandelaar2_key,
                                     mld_ins_discipline_key)
                VALUES ('MLD',
                        4,                                            -- email
                        v_locatie_key,
                        NULL,                                --v_onrgoed_keys,
                        SYSDATE,
                        'From-adres: ' || v_fw_from || CHR (13) || CHR (10) ||
                         SUBSTR (pbody, 1, 2000),
                        v_stdmelding_key,
                        NULL,
                        v_kostenplaats_key,
                        v_perslid_key,
                        v_perslid_key,
                        NULL,
                        3,                                     -- prio normaal
                        SUBSTR (psubject, 1, 80),
                        NULL,
                        NULL)                               -- BT Helpdesk I+A
             RETURNING mld_melding_key
                  INTO v_melding_key;

            mld.setmeldingstatus (v_melding_key, 2, v_perslid_key);

            INSERT INTO fac_result (fac_result_sessionid, fac_result_naam, fac_result_waarde)
                VALUES (psessionid, 'maillog', 'Geregistreerd onder melding ' || v_melding_key);

         END IF;


 	     -- Find the lowest volgnummer of the Folder-flexfield.
 	     SELECT MIN (k1.mld_kenmerk_key)
           INTO v_folder_kkey
           FROM mld_stdmelding sm1, ins_tab_discipline td1, mld_kenmerk k1, mld_srtkenmerk sk1
  	      WHERE sm1.mld_stdmelding_key = v_stdmelding_key
                AND sm1.mld_ins_discipline_key = td1.ins_discipline_key
                AND k1.mld_kenmerk_verwijder IS NULL
                AND k1.mld_srtkenmerk_key = sk1.mld_srtkenmerk_key
                AND sk1.mld_srtkenmerk_kenmerktype = 'M'
                AND ((k1.mld_stdmelding_key = sm1.mld_stdmelding_key AND k1.mld_kenmerk_niveau = 'S')
                      OR (k1.mld_stdmelding_key = td1.ins_discipline_key AND k1.mld_kenmerk_niveau = 'D')
                      OR (k1.mld_stdmelding_key = td1.ins_srtdiscipline_key AND k1.mld_kenmerk_niveau = 'T')
                      OR (k1.mld_kenmerk_niveau = 'A')                      
                      )
                AND NOT EXISTS
                       (SELECT 1
                          FROM mld_stdmelding sm2, ins_tab_discipline td2, mld_kenmerk k2, mld_srtkenmerk sk2
                         WHERE sm2.mld_stdmelding_key = v_stdmelding_key
                               AND sm2.mld_ins_discipline_key = td2.ins_discipline_key
                               AND k2.mld_kenmerk_verwijder IS NULL
                               AND k2.mld_srtkenmerk_key = sk2.mld_srtkenmerk_key
                               AND sk2.mld_srtkenmerk_kenmerktype = 'M'
                               AND ((k2.mld_stdmelding_key = sm2.mld_stdmelding_key AND k2.mld_kenmerk_niveau = 'S')
                                    OR (k2.mld_stdmelding_key = td2.ins_discipline_key AND k2.mld_kenmerk_niveau = 'D')
                                    OR (k2.mld_stdmelding_key = td2.ins_srtdiscipline_key AND k2.mld_kenmerk_niveau = 'T')
                                    OR (k1.mld_kenmerk_niveau = 'A')                                    
                                    )
                               AND k2.mld_kenmerk_volgnummer < k1.mld_kenmerk_volgnummer);

 	     IF v_folder_kkey IS NOT NULL
 	     THEN
            INSERT INTO fac_result (fac_result_sessionid, fac_result_naam, fac_result_waarde)
               VALUES (psessionid, 'kenmerkpath', 'MLD\M' || TO_CHAR (TRUNC (v_melding_key / 1000), 'FM0000') || '___\M' || v_melding_key || '\' || v_folder_kkey || '\');
 	     END IF;

      ELSE
         INSERT INTO fac_result (fac_result_sessionid, fac_result_naam, fac_result_waarde)
             VALUES (psessionid, 'errormsg', 'Ontvanger ongeldig - Neem contact op met uw systeembeheerder ');
   END CASE;
EXCEPTION
   WHEN OTHERS
   THEN
      oracle_err_num := SQLCODE;
      oracle_err_mes := SUBSTR (SQLERRM, 1, 200);
      v_errormsg := v_errormsg || ' ORACLE (error ' || oracle_err_num || '/' || oracle_err_mes || ')';
      fac.writelog ('PROCESSEMAIL', 'E', 'BLOS_processemail afgebroken!', '[' || pfrom || '] ' || v_errormsg);
      INSERT INTO fac_result (fac_result_sessionid, fac_result_naam, fac_result_waarde)
          VALUES (psessionid, 'errormsg', 'Database fout - Neem contact op met uw systeembeheerder ');
      COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE skaf_import_prs (
   p_import_key IN NUMBER
)
IS
   c_fielddelimitor                VARCHAR2 (1)    := ';';
   -- LET OP: moet idem zijn als declaratie bij skaf_UPDATE_PRS
   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_prs_perslid_nr                VARCHAR2 (256);
   v_prs_perslid_naam              VARCHAR2 (256);
   v_prs_perslid_tussenvoegsel     VARCHAR2 (256);
   v_prs_perslid_voorletters       VARCHAR2 (256);
   v_prs_perslid_voornaam          VARCHAR2 (256);
   v_prs_afdeling_naam             VARCHAR2 (256); -- Versie 1 was op afdelingscode. Versie 2 is op afdelings_kostenplaats
   v_prs_srtperslid_omschrijving   VARCHAR2 (256);
   v_prs_perslid_telefoonnr        VARCHAR2 (256);
   v_prs_perslid_email             VARCHAR2 (256);
   v_prs_perslid_oslogin           VARCHAR2 (256);
   v_prs_perslid_wachtwoord        VARCHAR2 (256);

   -- Overig:

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

   COMMIT;

   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
         fac.imp_getfield (v_newline, c_fielddelimitor, v_prs_afdeling_naam);
         -- 02
         fac.imp_getfield (v_newline, c_fielddelimitor, v_prs_perslid_naam);
         -- 03
         fac.imp_getfield (v_newline, c_fielddelimitor, v_prs_perslid_tussenvoegsel);
         -- 04
         fac.imp_getfield (v_newline, c_fielddelimitor, v_prs_perslid_voorletters);
         -- 05
         fac.imp_getfield (v_newline, c_fielddelimitor, v_prs_perslid_voornaam);
         -- 06
         fac.imp_getfield (v_newline, c_fielddelimitor, v_prs_perslid_telefoonnr);
         -- 07
         fac.imp_getfield (v_newline, c_fielddelimitor, v_prs_perslid_email);
         -- 08
         fac.imp_getfield (v_newline, c_fielddelimitor, v_prs_srtperslid_omschrijving);
         -- 09
         fac.imp_getfield (v_newline, c_fielddelimitor, v_prs_perslid_nr);
		 -- 10
         fac.imp_getfield (v_newline, c_fielddelimitor, v_prs_perslid_oslogin);
         -- 11
         -- fac.imp_getfield (v_newline, c_fielddelimitor, v_prs_perslid_wachtwoord);



         --
         v_aanduiding :=
               '['
            || v_prs_perslid_nr
            || '|'
            || v_prs_perslid_naam
            || '|'
            || v_prs_perslid_tussenvoegsel
            || '|'
            || v_prs_perslid_voornaam
            || '] ';

         -- Ik controleer of ik een geldige header heb, dat is: in de juiste kolommen
         -- de juiste kolomkop. Ik controleer daarbij ALLE kolommen!
         -- Ik negeer alles totdat ik een geldige header ben gepasseerd.
         IF (header_is_valid = 0)
         THEN
            IF UPPER (v_prs_afdeling_naam) = 'AFDELINGSCODE'
            	 AND UPPER (v_prs_perslid_naam) = 'PERSOONACHTERNAAM'
               AND UPPER (v_prs_perslid_tussenvoegsel) = 'TUSSENVOEGSEL'
               AND UPPER (v_prs_perslid_voorletters) = 'VOORLETTERS'
               AND UPPER (v_prs_perslid_voornaam) = 'VOORNAAM'
               AND UPPER (v_prs_perslid_telefoonnr) = 'TELEFOONNUMMER'
               AND UPPER (v_prs_perslid_email) = 'EMAIL'
               AND UPPER (v_prs_srtperslid_omschrijving) = 'FUNCTIE'
               AND UPPER (v_prs_perslid_nr) = 'PERSONEELSNUMMER'
               AND UPPER (v_prs_perslid_oslogin) = 'LOGINNAAM'
            THEN
               header_is_valid := 1;
            END IF;
         ELSE
            v_count_tot := v_count_tot + 1;
            -- Controleer alle veldwaarden
            v_errorhint := 'Ongeldig personeelsnummer';
            v_prs_perslid_nr := TRIM (v_prs_perslid_nr);

            IF LENGTH (v_prs_perslid_nr) > 16
            THEN
               v_prs_perslid_nr := SUBSTR (v_prs_perslid_nr, 1, 16);
               fac.imp_writelog (p_import_key,
                                 'W',
                                 v_aanduiding || 'Personeelsnummer te lang',
                                    'Personeelsnummer wordt afgebroken tot ['
                                 || v_prs_perslid_nr
                                 || ']'
                                );
            ELSE
               IF (v_prs_perslid_nr IS NULL)
               THEN
                  v_ongeldig := 1;
                  fac.imp_writelog
                     (p_import_key,
                      'E',
                      v_aanduiding || 'Personeelsnummer onbekend',
                      'Personeelsnummer is verplicht; regel wordt overgeslagen!'
                     );
               END IF;
            END IF;
            COMMIT;

            --
            v_errorhint := 'Ongeldige naam';
            v_prs_perslid_naam := TRIM (v_prs_perslid_naam);

            IF LENGTH (v_prs_perslid_naam) > 30
            THEN
               v_prs_perslid_naam := SUBSTR (v_prs_perslid_naam, 1, 30);
               fac.imp_writelog (p_import_key,
                                 'W',
                                 v_aanduiding || 'Naam te lang',
                                    'Naam wordt afgebroken tot ['
                                 || v_prs_perslid_naam
                                 || ']'
                                );
            ELSE
               IF (v_prs_perslid_naam IS NULL)
               THEN
                  v_ongeldig := 1;
                  fac.imp_writelog
                              (p_import_key,
                               'E',
                               v_aanduiding || 'Naam onbekend',
                               'Naam is verplicht; regel wordt overgeslagen!'
                              );
               END IF;
            END IF;
            COMMIT;

            --
            v_errorhint := 'Ongeldig tussenvoegsel';
            v_prs_perslid_tussenvoegsel := TRIM (v_prs_perslid_tussenvoegsel);

            IF LENGTH (v_prs_perslid_tussenvoegsel) > 10
            THEN
               v_prs_perslid_tussenvoegsel :=
                                  SUBSTR (v_prs_perslid_tussenvoegsel, 1, 10);
               fac.imp_writelog (p_import_key,
                                 'W',
                                 v_aanduiding || 'Tussenvoegsel te lang',
                                    'Tussenvoegsel wordt afgebroken tot ['
                                 || v_prs_perslid_tussenvoegsel
                                 || ']'
                                );
               COMMIT;
            END IF;

            --
            v_errorhint := 'Ongeldige voorletters';
            v_prs_perslid_voorletters := TRIM (v_prs_perslid_voorletters);

            IF LENGTH (v_prs_perslid_voorletters) > 10
            THEN
               v_prs_perslid_voorletters :=
                                    SUBSTR (v_prs_perslid_voorletters, 1, 10);
               fac.imp_writelog (p_import_key,
                                 'W',
                                 v_aanduiding || 'Voorletters te lang',
                                    'Voorletters worden afgebroken tot ['
                                 || v_prs_perslid_voorletters
                                 || ']'
                                );
               COMMIT;
            END IF;

            --
            v_errorhint := 'Ongeldige voornaam';
            v_prs_perslid_voornaam := TRIM (v_prs_perslid_voornaam);

            IF LENGTH (v_prs_perslid_voornaam) > 30
            THEN
               v_prs_perslid_voornaam :=
                                       SUBSTR (v_prs_perslid_voornaam, 1, 30);
               fac.imp_writelog (p_import_key,
                                 'W',
                                 v_aanduiding || 'Voornaam is te lang',
                                    'Voornaam wordt afgebroken tot ['
                                 || v_prs_perslid_voornaam
                                 || ']'
                                );
            ELSE
               IF (v_prs_perslid_voornaam IS NULL)
               THEN
                  v_prs_perslid_voornaam := '-';
                  fac.imp_writelog
                          (p_import_key,
                           'W',
                           v_aanduiding || 'Voornaam onbekend',
                           'Voornaam wordt gezet op [-]'
                          );
               END IF;
            END IF;
            COMMIT;

            --
            v_errorhint := 'Ongeldige afdelingscode';
            v_prs_afdeling_naam := TRIM (v_prs_afdeling_naam);

            IF LENGTH (v_prs_afdeling_naam) > 15
            THEN
               v_prs_afdeling_naam := SUBSTR (v_prs_afdeling_naam, 1, 15);
               fac.imp_writelog (p_import_key,
                                 'W',
                                 v_aanduiding || 'Afdelingscode te lang',
                                    'Afdelingscode wordt afgebroken tot ['
                                 || v_prs_afdeling_naam
                                 || ']'
                                );
            ELSE
               IF (v_prs_afdeling_naam IS NULL)
               THEN
                  v_ongeldig := 1;
                  fac.imp_writelog
                     (p_import_key,
                      'E',
                      v_aanduiding || 'Afdelingscode onbekend',
                      'Afdelingscode is verplicht; regel wordt overgeslagen!'
                     );
               END IF;
            END IF;
            COMMIT;

            --
            v_errorhint := 'Ongeldige functie';
            v_prs_srtperslid_omschrijving := TRIM (v_prs_srtperslid_omschrijving);

            IF LENGTH (v_prs_srtperslid_omschrijving) > 60
            THEN
               v_prs_srtperslid_omschrijving :=
                                SUBSTR (v_prs_srtperslid_omschrijving, 1, 60);
               fac.imp_writelog (p_import_key,
                                 'W',
                                 v_aanduiding || 'Functie te lang',
                                    'Functie wordt afgebroken tot ['
                                 || v_prs_srtperslid_omschrijving
                                 || ']'
                                );
            ELSE
               IF (v_prs_srtperslid_omschrijving IS NULL)
               THEN
                  v_ongeldig := 1;
                  fac.imp_writelog
                           (p_import_key,
                            'E',
                            v_aanduiding || 'Functie onbekend',
                            'Functie is verplicht; regel wordt overgeslagen!'
                           );
               END IF;
            END IF;
            COMMIT;

            --
            v_errorhint := 'Ongeldig telefoonnummer';
            v_prs_perslid_telefoonnr := TRIM (v_prs_perslid_telefoonnr);

            IF LENGTH (v_prs_perslid_telefoonnr) > 15
            THEN
               v_prs_perslid_telefoonnr :=
                                     SUBSTR (v_prs_perslid_telefoonnr, 1, 15);
               fac.imp_writelog (p_import_key,
                                 'W',
                                 v_aanduiding || 'Telefoonnummer te lang',
                                    'Telefoonnummer wordt afgebroken tot ['
                                 || v_prs_perslid_telefoonnr
                                 || ']'
                                );
               COMMIT;
            END IF;

            --
            v_errorhint := 'Ongeldig e-mailadres';
            v_prs_perslid_email := TRIM (v_prs_perslid_email);

            IF LENGTH (v_prs_perslid_email) > 150
            THEN
               v_prs_perslid_email := SUBSTR (v_prs_perslid_email, 1, 150);
               fac.imp_writelog (p_import_key,
                                 'W',
                                 v_aanduiding || 'E-mailadres te lang',
                                    'E-mailadres wordt afgebroken tot ['
                                 || v_prs_perslid_email
                                 || ']'
                                );
               COMMIT;
            END IF;


            --
            v_errorhint := 'Ongeldig loginnaam';
            v_prs_perslid_oslogin := TRIM(SUBSTR (v_prs_perslid_oslogin, 1, INSTR (v_prs_perslid_oslogin, '@', 1, 1) - 1));

            IF LENGTH (v_prs_perslid_oslogin) > 30
            THEN
               v_prs_perslid_oslogin := SUBSTR (v_prs_perslid_oslogin, 1, 30);
               fac.imp_writelog (p_import_key,
                                 'W',
                                 v_aanduiding || 'Loginnaam te lang',
                                    'Loginnaam wordt afgebroken tot ['
                                 || v_prs_perslid_oslogin
                                 || ']'
                                );
               COMMIT;
            END IF;

            -- Insert geformatteerde import record
            IF v_ongeldig = 0
            THEN
               BEGIN
                  v_errorhint :=
                      'Fout bij toevoegen regel aan importtabel skaf_IMP_PRS';

                  INSERT INTO skaf_imp_prs
                          (prs_perslid_nr, prs_perslid_naam,
                           prs_perslid_tussenvoegsel,
                           prs_perslid_voorletters,
                           prs_perslid_voornaam, prs_afdeling_naam,
                           prs_srtperslid_omschrijving,
                           alg_locatie_code, alg_gebouw_code,
                           alg_verdieping_volgnr, alg_ruimte_nr,
                           prs_perslid_telefoonnr,
                           prs_perslid_email,
                           prs_perslid_oslogin,
                           prs_perslid_wachtwoord
                          )
                   VALUES (v_prs_perslid_nr, v_prs_perslid_naam,
                           v_prs_perslid_tussenvoegsel,
                           v_prs_perslid_voorletters,
                           v_prs_perslid_voornaam, v_prs_afdeling_naam,
                           v_prs_srtperslid_omschrijving,
                           NULL, NULL,
                           NULL, NULL,
                           v_prs_perslid_telefoonnr,
                           v_prs_perslid_email,
                           v_prs_perslid_oslogin,
                           NULL
                          );
                  COMMIT;
                  v_count_import := v_count_import + 1;
               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!'
                       );
   ELSE
      fac.imp_writelog (p_import_key,
                        'S',
                           'Persoon: aantal ingelezen regels: '
                        || TO_CHAR (v_count_tot),
                        ''
                       );
      fac.imp_writelog (p_import_key,
                        'S',
                           'Persoon: aantal ongeldige importregels: '
                        || TO_CHAR (v_count_tot - v_count_import),
                        ''
                       );
   END IF;
   COMMIT;
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 skaf_import_prs;
/

CREATE OR REPLACE PROCEDURE skaf_update_prs (
   p_import_key IN NUMBER
)
IS
   -- LET OP: moet idem zijn als declaratie bij SKAF_IMPORT_PRS
   c_persoon_inactief       VARCHAR2 (30)   := 'INACTIEF: ';
   -- Maximaal percentage aan nieuwe medewerkers tov. actieve medewerkers met een nummer
   c_max_delta_percentage   NUMBER (10)     := 50;   -- Eenmalig 25-10-2020 op 50 gezet... Moet/kan daarna weer op 20
   
   -- Groep waar personen in geplaatst worden (indien nog geen groep)
   c_key_default_rol        NUMBER(10) := 1;

   v_aanduiding             VARCHAR2 (200);
   v_errorhint              VARCHAR2 (1000);
   v_errormsg               VARCHAR2 (1000);
   oracle_err_num           NUMBER;
   oracle_err_mes           VARCHAR2 (200);
   v_count_prs_actual       NUMBER (10);
   v_count_prs_import       NUMBER (10);          -- Matching actual persons!
   v_count_tot              NUMBER (10);
   v_count_error            NUMBER (10);
   v_count                  NUMBER (10);
   --
   v_perslid_key            NUMBER (10);
   v_afdeling_key           NUMBER (10);
   v_srtperslid_key         NUMBER (10);
   v_oslogin                VARCHAR2 (30);
   v_groep_defaultnaam      VARCHAR2 (30);

   -- Let op! Met eindatum zijn personen die niet meer in dienst zijn.
   -- Dubbele nummers? Dan wordt iemand mogelijk niet verwijderd/INACTIEF!
   CURSOR cdelprs
   IS
      SELECT p.prs_perslid_key, p.prs_perslid_nr, p.prs_perslid_naam,
             p.prs_perslid_voornaam
        FROM prs_v_aanwezigperslid p
       WHERE p.prs_perslid_nr IS NOT NULL
         AND NOT EXISTS (
                SELECT 1
                  FROM skaf_imp_prs ip
                 WHERE ip.prs_perslid_nr = p.prs_perslid_nr);

   -- Let op! Met eindatum zijn personen die niet meer in dienst zijn.
   CURSOR csperslid
   IS
      SELECT UPPER(prs_srtperslid_omschrijving) prs_srtperslid_omschrijving
      FROM skaf_imp_prs
      GROUP BY UPPER (prs_srtperslid_omschrijving);

   CURSOR cperslid
   IS
      SELECT *
        FROM skaf_imp_prs;

   -- Alle aanwezige personen met personeelsnummer (ie. de import-doelgroep) niet in een groep
   CURSOR cnogroup
   IS
      SELECT p.prs_perslid_key, p.prs_perslid_nr, p.prs_perslid_naam,
             p.prs_perslid_voornaam
        FROM prs_v_aanwezigperslid p
       WHERE p.prs_perslid_nr IS NOT NULL
         AND NOT EXISTS (SELECT 1
                           FROM fac_gebruikersgroep g
                          WHERE g.prs_perslid_key = p.prs_perslid_key);


BEGIN
   SELECT DECODE (COUNT (*), 0, 1, COUNT (*))
     INTO v_count_prs_actual
     FROM prs_perslid
    WHERE prs_perslid_verwijder IS NULL AND prs_perslid_nr IS NOT NULL;

   SELECT DECODE (COUNT (*), 0, 1, COUNT (*))
     INTO v_count_prs_import
     FROM prs_perslid p, skaf_imp_prs ip
    WHERE p.prs_perslid_nr IS NOT NULL
      AND p.prs_perslid_verwijder IS NULL
      AND p.prs_perslid_nr = ip.prs_perslid_nr;

   IF (   (TRUNC ((v_count_prs_import / v_count_prs_actual) * 100) >=
                                               (100 - c_max_delta_percentage
                                               )
          )
      )
   THEN
      -- Geldig importbestand wat betreft aantal personen
      FOR recdelprs IN cdelprs
      LOOP
         BEGIN
            -- Heeft persoon actuele/toekomstige verplichtingen?
            SELECT COUNT (*)
              INTO v_count
              FROM prs_perslid p, prs_v_verplichting_keys r
             WHERE p.prs_perslid_key = r.prs_perslid_key
               AND p.prs_perslid_key = recdelprs.prs_perslid_key;

            IF (v_count = 0)
            THEN
               v_errorhint := 'Fout bij verwijderen persoon';

               UPDATE prs_perslid
                  SET prs_perslid_verwijder = SYSDATE
                WHERE prs_perslid_key = recdelprs.prs_perslid_key;

               fac.imp_writelog (p_import_key,
                                 'I',
                                    'Persoon met nummer ['
                                 || recdelprs.prs_perslid_nr
                                 || '] en naam ['
                                 || recdelprs.prs_perslid_naam
                                 || ', '
                                 || recdelprs.prs_perslid_voornaam
                                 || '] is verwijderd',
                                 'Persoon is verwijderd'
                                );
               COMMIT;
            ELSE
               -- Staat persoon INACTIEF?
               SELECT COUNT (*)
                 INTO v_count
                 FROM prs_perslid p
                WHERE p.prs_perslid_naam LIKE c_persoon_inactief || '%'
                  AND p.prs_perslid_key = recdelprs.prs_perslid_key;

               IF (v_count = 0)
               THEN
                  v_errorhint := 'Fout bij INACTIEF zetten persoon';

                  -- Persoon INACTIEF zetten (update naam + reset oslogin)
                  UPDATE prs_perslid
                     SET prs_perslid_naam =
                            SUBSTR (c_persoon_inactief || prs_perslid_naam,
                                    1,
                                    30
                                   ),
                         prs_perslid_oslogin = NULL
                   WHERE prs_perslid_key = recdelprs.prs_perslid_key;

                  fac.imp_writelog (p_import_key,
                                    'I',
                                       'Persoon met nummer ['
                                    || recdelprs.prs_perslid_nr
                                    || '] en naam ['
                                    || recdelprs.prs_perslid_naam
                                    || ', '
                                    || recdelprs.prs_perslid_voornaam
                                    || '] is INACTIEF gezet',
                                    'Persoon is INACTIEF gezet'
                                   );
               ELSE
                  v_errorhint := 'Fout bij INACTIEF maken persoon';

                  -- Persoon INACTIEF maken (reset oslogin; indien handmatig INACTIEF gezet)
                  UPDATE prs_perslid
                     SET prs_perslid_oslogin = NULL
                   WHERE prs_perslid_naam LIKE c_persoon_inactief || '%'
                     AND prs_perslid_oslogin IS NOT NULL;

                  fac.imp_writelog (p_import_key,
                                    'I',
                                       'Persoon met nummer ['
                                    || recdelprs.prs_perslid_nr
                                    || '] en naam ['
                                    || recdelprs.prs_perslid_naam
                                    || ', '
                                    || recdelprs.prs_perslid_voornaam
                                    || '] was en blijft INACTIEF',
                                    'Persoon was en blijft INACTIEF'
                                   );
               END IF;
               COMMIT;
            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_errormsg,
                                 v_errorhint
                                );
               COMMIT;
         END;
      END LOOP;

      v_count_tot := 0;
      v_count_error := 0;

      FOR recsperslid IN csperslid
      LOOP
         BEGIN
            v_count_tot := v_count_tot + 1;
            v_aanduiding := recsperslid.prs_srtperslid_omschrijving || ' - ';
            v_errorhint := 'Fout bij bepalen functie';

            SELECT COUNT (*)
              INTO v_count
              FROM prs_srtperslid
             WHERE UPPER (prs_srtperslid_omschrijving) =
                               UPPER (recsperslid.prs_srtperslid_omschrijving)
               AND prs_srtperslid_verwijder IS NULL;

            IF (v_count = 0)
            THEN
               v_errorhint := 'Fout bij toevoegen functie';

               INSERT INTO prs_srtperslid
                           (prs_srtperslid_omschrijving
                           )
                    VALUES (recsperslid.prs_srtperslid_omschrijving
                           );
               COMMIT;
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               v_count_error := v_count_error + 1;
               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',
                           'Functie: verwerkte regels zonder foutmelding: '
                        || TO_CHAR (v_count_tot - v_count_error),
                        ''
                       );
      fac.imp_writelog (p_import_key,
                        'S',
                           'Functie: verwerkte regels met foutmelding: '
                        || TO_CHAR (v_count_error),
                        ''
                       );
      COMMIT;

      v_count_tot := 0;
      v_count_error := 0;

      FOR recperslid IN cperslid
      LOOP
         BEGIN
            v_count_tot := v_count_tot + 1;
            v_aanduiding :=
                  recperslid.prs_perslid_nr
               || '|'
               || recperslid.prs_perslid_naam
               || '|'
               || recperslid.prs_perslid_voornaam
               || ' - ';
            v_errorhint :=
                  'Fout bij bepalen functie ['
               || recperslid.prs_srtperslid_omschrijving
               || ']';

            SELECT prs_srtperslid_key
              INTO v_srtperslid_key
              FROM prs_srtperslid
             WHERE prs_srtperslid_verwijder IS NULL
               AND UPPER (prs_srtperslid_omschrijving) =
                                UPPER (recperslid.prs_srtperslid_omschrijving);

            v_errorhint :=
                  'Fout bij bepalen afdeling (via kostenplaatsnr) ['
               || recperslid.prs_afdeling_naam
               || ']';

            SELECT prs_afdeling_key
              INTO v_afdeling_key
              FROM prs_afdeling a, prs_kostenplaats k
             WHERE a.prs_kostenplaats_key = k.prs_kostenplaats_key
               AND prs_afdeling_verwijder IS NULL
               AND UPPER (k.prs_kostenplaats_nr) =
                                          UPPER (recperslid.prs_afdeling_naam);

            v_errorhint := 'Fout bij bepalen persoon';

            SELECT COUNT (*), MAX (prs_perslid_key)
              INTO v_count, v_perslid_key
              FROM prs_perslid
             WHERE prs_perslid_verwijder IS NULL
               AND UPPER (prs_perslid_nr) = UPPER (recperslid.prs_perslid_nr);

			-- Login is afgeleid uit e-mail (alles voor @) en max 30 posities
            -- Dit is al in import-deel geregeld
            -- v_oslogin := SUBSTR (recperslid.prs_perslid_email, 1, INSTR (recperslid.prs_perslid_email, '@', 1, 1) - 1);
            v_oslogin := recperslid.prs_perslid_oslogin;

            IF (v_count = 0)
            THEN
               v_errorhint := 'Fout bij toevoegen persoon';

               INSERT INTO prs_perslid
                           (prs_perslid_module, prs_srtperslid_key,
                            prs_afdeling_key, prs_perslid_naam,
                            prs_perslid_voorletters,
                            prs_perslid_tussenvoegsel,
                            prs_perslid_voornaam,
                            prs_perslid_telefoonnr,
                            prs_perslid_email,
                            prs_perslid_nr,
                            prs_perslid_oslogin
                           )
                    VALUES ('PRS', v_srtperslid_key,
                            v_afdeling_key, recperslid.prs_perslid_naam,
                            recperslid.prs_perslid_voorletters,
                            recperslid.prs_perslid_tussenvoegsel,
                            recperslid.prs_perslid_voornaam,
                            recperslid.prs_perslid_telefoonnr,
                            recperslid.prs_perslid_email,
                            recperslid.prs_perslid_nr,
                            v_oslogin
                           ) RETURNING prs_perslid_key INTO v_perslid_key;

               fac.imp_writelog (p_import_key,
                                 'I',
                                    'Persoon met nummer ['
                                 || recperslid.prs_perslid_nr
                                 || '] en naam ['
                                 || recperslid.prs_perslid_naam
                                 || ', '
                                 || recperslid.prs_perslid_voornaam
                                 || '] is toegevoegd',
                                 'Persoon is toegevoegd'
                                );

      fac.trackaction ('PRSUPD', v_perslid_key, 4,sysdate, v_aanduiding || ' is toegevoegd uit afas-prs-import') ;

               COMMIT;
            ELSE
               IF (v_count > 1)
               THEN
                  fac.imp_writelog (p_import_key,
                                    'E',
                                       v_aanduiding
                                    || ' kan persoon niet 1-duidig bepalen',
                                    v_errorhint
                                   );
                  COMMIT;
               END IF;

               v_errorhint := 'Fout bij wijzigen persoon';

               UPDATE prs_perslid
                  SET prs_srtperslid_key = v_srtperslid_key,
                      prs_afdeling_key = v_afdeling_key,
                      prs_perslid_naam = recperslid.prs_perslid_naam,
                      prs_perslid_voorletters = recperslid.prs_perslid_voorletters,
                      prs_perslid_tussenvoegsel = recperslid.prs_perslid_tussenvoegsel,
                      prs_perslid_voornaam = recperslid.prs_perslid_voornaam,
                      prs_perslid_telefoonnr = recperslid.prs_perslid_telefoonnr,
                      prs_perslid_email = recperslid.prs_perslid_email,
                      prs_perslid_nr = recperslid.prs_perslid_nr,
                      prs_perslid_oslogin = COALESCE (v_oslogin, prs_perslid_oslogin)
                  WHERE prs_perslid_key = v_perslid_key;

               COMMIT;
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               v_count_error := v_count_error + 1;
               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',
                           'Persoon: verwerkte regels zonder foutmelding: '
                        || TO_CHAR (v_count_tot - v_count_error),
                        ''
                       );
      fac.imp_writelog (p_import_key,
                        'S',
                           'Persoon: verwerkte regels met foutmelding: '
                        || TO_CHAR (v_count_error),
                        ''
                       );
      COMMIT;

      -- Iedereen krijgt _default-rol die nog geen groep heeft.
      FOR recnogroup IN cnogroup
      LOOP
         BEGIN
            v_aanduiding :=
                  recnogroup.prs_perslid_nr
               || '|'
               || recnogroup.prs_perslid_naam
               || '|'
               || recnogroup.prs_perslid_voornaam
               || ' - ';
            v_errorhint := 'Fout bij bepalen groep met key [' || c_key_default_rol || ']';

						SELECT fac_groep_upper
              INTO v_groep_defaultnaam
              FROM fac_groep
             WHERE fac_groep_key = c_key_default_rol;


            v_errorhint :=
               'Fout bij koppelen persoon aan groep met key [' || c_key_default_rol || ']';

            INSERT INTO fac_gebruikersgroep
                        (fac_groep_key, prs_perslid_key
                        )
                 VALUES (c_key_default_rol, recnogroup.prs_perslid_key
                        );
            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_errormsg,
                                 v_errorhint
                                );
               COMMIT;
         END;
      END LOOP;
   

   ELSE
      fac.imp_writelog
         (p_import_key,
          'E',
          'Verschil tussen het actuele aantal en het te importeren aantal personen te groot; zie specificatie!',
             '- max. verschilpercentage = ['
          || TO_CHAR (c_max_delta_percentage)
          || '%] <br> '
          || '- #personen importbestand = ['
          || TO_CHAR (v_count_prs_import)
          || '] <br> '
          || '- #personen Facilitor = ['
          || TO_CHAR (v_count_prs_actual)
          || ']'
         );
      COMMIT;
   END IF;                  -- Geldig importbestand wat betreft aantal inserts
   DELETE FROM skaf_imp_prs; -- Ivm IB maken we de personentabel weer leeg
   COMMIT;
END skaf_update_prs;
/

-- Import voor Viking - catalogus Boodschappen - Beperkt Assortiment
CREATE OR REPLACE PROCEDURE skaf_import_catalogus (p_import_key IN NUMBER)
AS

   CURSOR c
   IS
      SELECT ins_srtdeel_nr, ins_srtdeel_prijs, ins_srtdeel_btw
      FROM fac_imp_catalogus
      WHERE ins_srtdeel_prijs IS NOT NULL and ins_srtdeel_btw IS NOT NULL;

   v_ins_srtdeel_prijs   NUMBER(8,2);


BEGIN
-- standaard catalogus-import aanroepen
fac_import_catalogus(p_import_key) ;


-- updaten prijzen: Wordt door Viking exclusief btw aangeleverd en zetten we hier om naar prijzen inclusief btw
      FOR rec IN c
      LOOP
         BEGIN
            v_ins_srtdeel_prijs := rec.ins_srtdeel_prijs * ((100 + rec.ins_srtdeel_btw)/100);
            
            UPDATE fac_imp_catalogus
               SET ins_srtdeel_prijs = v_ins_srtdeel_prijs
             WHERE ins_srtdeel_nr = rec.ins_srtdeel_nr ;

         END;
      END LOOP;

END skaf_import_catalogus;
/

CREATE OR REPLACE PROCEDURE skaf_update_catalogus (p_import_key IN NUMBER)
AS
BEGIN
-- standaard catalogus-update aanroepen
fac_update_catalogus(p_import_key);

END skaf_update_catalogus;
/


CREATE OR REPLACE VIEW skaf_v_factuur_bestand (fin_factuur_key, fin_factuur_bestand)
AS
SELECT kf.fin_factuur_key, kf.fin_kenmerkfactuur_waarde
FROM fin_kenmerkfactuur kf
WHERE kf.fin_kenmerkfactuur_verwijder IS NULL
AND fin_kenmerk_key =
    (select fac.safe_to_number(fac_usrdata_omschr)
     from fac_usrdata
     where fac_usrtab_key =
        (select fac_usrtab_key
         from fac_usrtab
         where fac_usrtab_naam = '$SYSTEM')
	 and fac_usrdata_code='$FIN_KENMERK_KEY_BESTAND'
	 and fac_usrdata_verwijder is null
    );



-- view tbv export naar exact
CREATE OR REPLACE VIEW skaf_v_factuur_mldcnt_gegevens (
   fin_factuur_key,
   opdracht_id,
   fin_factuur_datum,
   fin_factuur_boekmaand,
   fin_factuur_nr,
   fin_factuur_bestand,
   prs_bedrijf_key,
   fin_factuurregel_totaal,
   fin_factuurregel_btw,
   fin_factuurregel_nr,
   fin_factuurregel_omschrijving,
   fin_factuur_debiteur_nr,
   project_nummer,
   prs_kostensoort_refcode,
   prs_kostenplaats_nr,
   bes_opdr_key,
   mld_opdr_key,
   cnt_contract_key,
   fin_factuur_statuses_key
   )
AS
   SELECT DISTINCT f.fin_factuur_key,
          COALESCE (TO_CHAR (cnt_contract_nummer),
                    (SELECT    sd.ins_srtdiscipline_prefix
                            || m.mld_melding_key
                            || '/'
                            || o.mld_opdr_bedrijfopdr_volgnr
                     FROM mld_melding m,
                          mld_stdmelding std,
                          ins_tab_discipline d,
                          ins_srtdiscipline sd
                     WHERE m.mld_melding_key = o.mld_melding_key
                           AND m.mld_stdmelding_key = std.mld_stdmelding_key
                           AND std.mld_ins_discipline_key = d.ins_discipline_key
                           AND d.ins_srtdiscipline_key =
                                 sd.ins_srtdiscipline_key)
          )
             opdracht_id,
          f.fin_factuur_datum,
          f.fin_factuur_boekmaand,
          f.fin_factuur_nr,
          fb.fin_factuur_bestand,
          COALESCE (b_c.prs_bedrijf_key, b_o.prs_bedrijf_key) prs_bedrijf_key,
          fr.fin_factuurregel_totaal,
          fr.fin_factuurregel_btw,
          fr.fin_factuurregel_nr,
          fr.fin_factuurregel_omschrijving,
          f.fin_factuur_debiteur_nr,
          NULL projectnummer,
          ks.prs_kostensoort_refcode,
          COALESCE (kp_c.prs_kostenplaats_nr, kp_o.prs_kostenplaats_nr) prs_kostenplaats_nr,
          NULL bes_opdr_key,
          o.mld_opdr_key,
          c.cnt_contract_key,
          f.fin_factuur_statuses_key
   FROM fin_factuur f,
        skaf_v_factuur_bestand fb,
        fin_factuurregel fr,
        mld_opdr o,
        mld_typeopdr ot,
        cnt_contract c,
        prs_bedrijf b_o,
        prs_bedrijf b_c,
        prs_kostensoort ks,
        prs_kostenplaats kp_c,
        prs_kostenplaats kp_o
   WHERE f.fin_factuur_key = fr.fin_factuur_key
   AND f.fin_factuur_key = fb.fin_factuur_key(+)
   AND f.bes_bestelopdr_key is null
   AND f.mld_opdr_key = o.mld_opdr_key(+)
   AND o.prs_kostenplaats_key = kp_o.prs_kostenplaats_key (+)
   AND o.mld_typeopdr_key = ot.mld_typeopdr_key(+)
   AND f.cnt_contract_key = c.cnt_contract_key(+)
   AND o.mld_uitvoerende_keys = b_o.prs_bedrijf_key(+)
   AND c.cnt_prs_bedrijf_key = b_c.prs_bedrijf_key(+)
   AND c.prs_kostenplaats_key = kp_c.prs_kostenplaats_key (+)
   AND f.prs_kostensoort_key = ks.prs_kostensoort_key(+);


CREATE OR REPLACE VIEW skaf_v_factuur_bes_gegevens (
   fin_factuur_key,
   opdracht_id,
   fin_factuur_datum,
   fin_factuur_boekmaand,
   fin_factuur_nr,
   fin_factuur_bestand,
   prs_bedrijf_key,
   fin_factuurregel_totaal,
   fin_factuurregel_btw,
   fin_factuurregel_nr,
   fin_factuurregel_omschrijving,
   fin_factuur_debiteur_nr,
   project_nummer,
   prs_kostensoort_refcode,
   prs_kostenplaats_nr,
   bes_opdr_key,
   mld_opdr_key,
   cnt_contract_key,
   fin_factuur_statuses_key
   )
AS
   SELECT DISTINCT f.fin_factuur_key,
          TO_CHAR (bo.bes_bestelopdr_id) opdracht_id,
          f.fin_factuur_datum,
          f.fin_factuur_boekmaand,
          f.fin_factuur_nr,
          fb.fin_factuur_bestand,
          b.prs_bedrijf_key,
          fr.fin_factuurregel_totaal,
          fr.fin_factuurregel_btw,
          fr.fin_factuurregel_nr,
          fr.fin_factuurregel_omschrijving,
          f.fin_factuur_debiteur_nr,
          NULL projectnummer,
          ks.prs_kostensoort_refcode,
          kp.prs_kostenplaats_nr,
          bo.bes_bestelopdr_key bes_opdr_key,
          NULL mld_opdr_key,
          NULL cnt_contract_key,
          f.fin_factuur_statuses_key
		 FROM  bes_bestelopdr bo,
           prs_bedrijf b,
           bes_bestelling bes,
           bes_bestelopdr_item boi,
           bes_bestelling_item bi,
           fin_factuur f,
           skaf_v_factuur_bestand fb,
           fin_factuurregel fr,
           prs_kostensoort ks,
           prs_kostenplaats kp
      WHERE b.prs_bedrijf_key = bo.prs_bedrijf_key
        AND bo.bes_bestelopdr_key = boi.bes_bestelopdr_key
        AND bi.bes_bestelopdr_item_key = boi.bes_bestelopdr_item_key
        AND bes.bes_bestelling_key = bi.bes_bestelling_key
        AND kp.prs_kostenplaats_key = bes.prs_kostenplaats_key
        AND f.prs_kostensoort_key = ks.prs_kostensoort_key(+)
        AND f.bes_bestelopdr_key = bo.bes_bestelopdr_key
        AND f.fin_factuur_key = fr.fin_factuur_key
        AND f.fin_factuur_key = fb.fin_factuur_key(+);


CREATE OR REPLACE VIEW skaf_v_factuur_geg
AS
SELECT * from skaf_v_factuur_mldcnt_gegevens
UNION
SELECT * from skaf_v_factuur_bes_gegevens;


CREATE OR REPLACE VIEW skaf_v_factuur_geg_akkoord
AS
SELECT * from skaf_v_factuur_mldcnt_gegevens
WHERE fin_factuur_statuses_key = 6
UNION
SELECT * from skaf_v_factuur_bes_gegevens
WHERE fin_factuur_statuses_key = 6;



-- De OUDE ascii-rapportage van het exportbestand die voor Exact zou worden gegenereerd.
CREATE OR REPLACE VIEW skaf_v_rap_exact
(regelnummer,dagb_type, dagbknr, periode, bkjcode, bkstnr, oms25, datum, empty9, debnr,
                   crdnr, empty12, bedrag, drbk_in_val, valcode, koers, kredbep, bdrkredbep, vervdatfak, vervdatkrd,
                   empty21, empty22, weeknummer, betaalref, betwijze, grek_bdr, empty27, empty28, empty29, empty30,
                   empty31, transsubtype, empty33, empty34, empty35, empty36, empty37, empty38, projectnr, field40)
AS
   SELECT regelnummer,dagb_type, dagbknr, periode, bkjcode, bkstnr, oms25, datum, empty9, debnr,
   				crdnr, empty12, bedrag, drbk_in_val, valcode, koers, kredbep, bdrkredbep, vervdatfak, vervdatkrd,
   				empty21, empty22, weeknummer, betaalref, betwijze, grek_bdr, empty27, empty28, empty29, empty30,
   				empty31, transsubtype, empty33, empty34, empty35, empty36, empty37, empty38, projectnr, '!'
   FROM (SELECT 0 regelnummer,
                'I' dagb_type,
                '42' dagbknr,
                '' periode,
                '' bkjcode,
                TO_CHAR (fin_factuur_key) bkstnr,
                opdracht_id oms25,
                TO_CHAR (fin_factuur_datum, 'ddmmyyyy') datum,
                '' empty9,
                '' debnr,
                (SELECT prs_leverancier_nr
                 FROM prs_bedrijf b
                 WHERE b.prs_bedrijf_key = fg.prs_bedrijf_key)
                   crdnr,
                '' empty12,
                REPLACE (TO_CHAR (bedrag), ',', '.') bedrag,
                '' drbk_in_val,
                '' valcode,
                '' koers,
                '' kredbep,
                '' bdrkredbep,
                '' vervdatfak,
                '' vervdatkrd,
                '' empty21,
                '' empty22,
                '' weeknummer,
                fin_factuur_nr || '/' || fin_factuur_debiteur_nr betaalref,
                '' betwijze,
                '' grek_bdr,
                '' empty27,
                '' empty28,
                '' empty29,
                '' empty30,
                '' empty31,
                '' transsubtype,
                '' empty33,
                '' empty34,
                '' empty35,
                '' empty36,
                '' empty37,
                '' empty38,
                '' projectnr,
                '!' empty40
         FROM (SELECT fin_factuur_key,
                      opdracht_id,
                      fin_factuur_datum,
                      fin_factuur_nr,
                      prs_bedrijf_key,
                      fin_factuur_debiteur_nr,
                      SUM (fin_factuurregel_totaal + fin_factuurregel_btw)
                         bedrag
               FROM skaf_v_factuur_geg_akkoord
               GROUP BY fin_factuur_key,
                        opdracht_id,
                        fin_factuur_datum,
                        fin_factuur_nr,
                        prs_bedrijf_key,
                        fin_factuur_debiteur_nr) fg
         UNION ALL
         SELECT fin_factuurregel_nr regelnummer,
                'I' dagb_type,
                '42' dagbknr,
                '' periode,
                '' bkjcode,
                TO_CHAR (fin_factuur_key) boekstuknummer,
                fin_factuur_nr || '/' || fin_factuur_debiteur_nr oms25,
                TO_CHAR (fin_factuur_datum, 'ddmmyyyy') datum,
                prs_kostensoort_refcode reknr,
                '' debnr,
                (SELECT prs_leverancier_nr
                 FROM prs_bedrijf b
                 WHERE b.prs_bedrijf_key = fg.prs_bedrijf_key)
                   crdnr,
                '' empty12,
                REPLACE (TO_CHAR (fin_factuurregel_totaal), ',', '.') bedrag,
                '' drbk_in_val,
                '' val_code,
                '' koers,
                '' kredbep,
                '' bdrkredbep,
                '' vervdatfak,
                '' vervdatkrd,
                DECODE (ROUND( (fin_factuurregel_btw / decode(fin_factuurregel_totaal,0,0.000000001,fin_factuurregel_totaal))
                              * 100),
                        19,
                        '4',
                        6,
                        '2',
                        0,
                        '0',
                        '-1'
                )
                   btw_code,
                REPLACE (TO_CHAR (fin_factuurregel_btw), ',', '.') btw_bdr,
                '' weeknummer,
                '' empty24,
                '' empty25,
                '' empty26,
                prs_kostenplaats_nr kstplcode,
                '' kstdrcode,
                '' empty29,
                '' empty30,
                '' empty31,
                '' transsubtype,
                '' empty33,
                '' empty34,
                '' emtpy35,
                '' empty36,
                '' empty37,
                '' empty38,
                project_nummer projectnr,
                '!' empty40
         FROM skaf_v_factuur_geg_akkoord fg);


---------------------------------------------------------------------------------------------------------------
---                             notificatiejobs                                                             ---
---------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE VIEW skaf_v_noti_cntreminder
(
   code,
   sender,
   receiver,
   text,
   key,
   xkey,
   xemail,
   xmobile
)
AS 
   SELECT 'CNTMAI',
          NULL,
          c.prs_perslid_key_beh,
          'Rappel: Contract ' || c.cnt_contract_nummer_intern
          || DECODE (c.cnt_contract_versie, NULL, '', '.' || c.cnt_contract_versie)
          || ' (' || c.cnt_contract_omschrijving || ' ' || b.prs_bedrijf_naam || ')'
          || ' moet uiterlijk per ' || TO_CHAR (cnt.cnt_getopzegdatum (c.cnt_contract_key), 'dd-mm-yyyy')
          || ' worden verlengd of opgezegd.',
          c.cnt_contract_key,
          NULL,
          NULL,
          NULL
     FROM cnt_v_aanwezigcontract c, prs_bedrijf b
    WHERE c.cnt_prs_bedrijf_key = b.prs_bedrijf_key
          AND cnt_contract_status = 0
          AND NOT EXISTS 
          (SELECT km.cnt_contract_key
             FROM cnt_kenmerkcontract km, cnt_kenmerk k
            WHERE km.cnt_contract_key = c.cnt_contract_key AND km.cnt_kenmerk_key = k.cnt_kenmerk_key AND k.cnt_srtkenmerk_key = 61)
          AND (SYSDATE BETWEEN cnt.cnt_getrappeldatum (c.cnt_contract_key) AND cnt.cnt_getopzegdatum (c.cnt_contract_key)) ;


---------------------------------------------------------------------------------------------------------------
--- 																						Facilitor Graphics / CAD 																		---
---------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE VIEW skaf_v_thema_set_res_wp
AS
    SELECT ins_deel_key, DECODE (rd.res_deel_key, NULL, 0, 1) waarde
      FROM ins_deel  d,
           (SELECT *
              FROM res_deel
             WHERE res_deel_verwijder IS NULL) rd
     WHERE     d.ins_deel_key = rd.res_ins_deel_key(+);


CREATE OR REPLACE VIEW skaf_v_thema_vloersoort
(
    ALG_RUIMTE_KEY,
    WAARDE,
    WAARDE_KEY
)
AS
    SELECT r.alg_ruimte_key,
           COALESCE (va.fac_usrdata_omschr, 'Onbekend'),
           va.fac_usrdata_key
      FROM alg_ruimte  r,
           (SELECT rr.alg_ruimte_key,
                   ud.fac_usrdata_omschr,
                   ud.fac_usrdata_key
              FROM alg_onrgoedkenmerk    ok,
                   fac_usrdata           ud,
                   fac_kenmerkdomein     rk,
                   alg_kenmerk           k,
                   alg_v_aanwezigruimte  rr
             WHERE     rk.fac_usrtab_key = ud.fac_usrtab_key
                   AND k.fac_kenmerkdomein_key = rk.fac_kenmerkdomein_key
                   AND TO_CHAR (ud.fac_usrdata_key) =
                       ok.alg_onrgoedkenmerk_waarde
                   AND ok.alg_kenmerk_key = 1020
                   AND k.alg_kenmerk_niveau = 'R'
                   AND k.alg_kenmerk_verwijder IS NULL
                   AND rr.alg_ruimte_key = ok.alg_onrgoed_key) va
     WHERE r.alg_ruimte_key = va.alg_ruimte_key(+);


CREATE OR REPLACE VIEW skaf_v_thema_typeopvang
(
    ALG_RUIMTE_KEY,
    WAARDE,
    WAARDE_KEY
)
AS
SELECT
v.alg_ruimte_key,
CASE 
    WHEN v.kdv is not null and v.bso is not null and v.slg is not null then 'KDV/BSO/SLG'
    WHEN v.kdv is not null and v.bso is not null and v.slg is null then 'KDV/BSO'
    WHEN v.kdv is not null and v.bso is null and v.slg is not null then 'KDV/SLG'
    WHEN v.kdv is null and v.bso is not null and v.slg is not null then 'BSO/SLG'
    WHEN v.kdv is not null and v.bso is null and v.slg is null then 'KDV'
    WHEN v.kdv is null and v.bso is not null and v.slg is null then 'BSO'
    WHEN v.kdv is null and v.bso is null and v.slg is not null then 'SLG'
    ELSE '<geen intern gebruik>'
    END as waarde,
'1' waarde_key    
FROM
(SELECT r.alg_ruimte_key,
       kdv.bestaat KDV,
       bso.bestaat BSO,
       slg.bestaat SLG
  FROM alg_ruimte  r,
       (SELECT ra.alg_ruimte_key, a.prs_afdeling_key, 'Ja' AS bestaat
          FROM prs_ruimteafdeling ra, prs_afdeling a
         WHERE     ra.prs_afdeling_key = a.prs_afdeling_key
               AND INSTR (UPPER (a.prs_afdeling_omschrijving), 'KDV') > 0
               AND ra.prs_ruimteafdeling_verwijder IS NULL
               AND a.prs_afdeling_verwijder IS NULL) kdv,
       (SELECT ra.alg_ruimte_key, a.prs_afdeling_key, 'Ja' AS bestaat
          FROM prs_ruimteafdeling ra, prs_afdeling a
         WHERE     ra.prs_afdeling_key = a.prs_afdeling_key
               AND INSTR (UPPER (a.prs_afdeling_omschrijving), 'BSO') > 0
               AND ra.prs_ruimteafdeling_verwijder IS NULL
               AND a.prs_afdeling_verwijder IS NULL) bso,
       (SELECT ra.alg_ruimte_key, a.prs_afdeling_key, 'Ja' AS bestaat
          FROM prs_ruimteafdeling ra, prs_afdeling a
         WHERE     ra.prs_afdeling_key = a.prs_afdeling_key
               AND INSTR (UPPER (a.prs_afdeling_omschrijving), 'SLG') > 0
               AND ra.prs_ruimteafdeling_verwijder IS NULL
               AND a.prs_afdeling_verwijder IS NULL) slg
 WHERE     r.alg_ruimte_key = kdv.alg_ruimte_key(+)
       AND r.alg_ruimte_key = bso.alg_ruimte_key(+)
       AND r.alg_ruimte_key = slg.alg_ruimte_key(+)
       AND r.alg_ruimte_verwijder IS NULL) v
;

CREATE OR REPLACE VIEW skaf_v_thema_typeopvang_spec
(
    ALG_RUIMTE_KEY,
    WAARDE,
    WAARDE_KEY
)
AS
SELECT alg_ruimte_key, 'KDV' AS waarde, '1' AS waarde_key
  FROM skaf_v_thema_typeopvang
 WHERE INSTR (waarde, 'KDV') > 0
UNION
SELECT alg_ruimte_key, 'BSO' AS waarde, '2' AS waarde_key
  FROM skaf_v_thema_typeopvang
 WHERE INSTR (waarde, 'BSO') > 0
UNION
SELECT alg_ruimte_key, 'SLG' AS waarde, '3' AS waarde_key
  FROM skaf_v_thema_typeopvang
 WHERE INSTR (waarde, 'SLG') > 0;

CREATE OR REPLACE VIEW skaf_v_label_ruimteomschr
(
    ALG_RUIMTE_KEY,
    WAARDE
)
AS
    SELECT r.alg_ruimte_key,
              r.alg_ruimte_omschrijving
      FROM alg_v_aanwezigruimte r, alg_srtruimte sr
     WHERE r.alg_srtruimte_key = sr.alg_srtruimte_key;

CREATE OR REPLACE VIEW skaf_v_label_ruimteopp
(
    ALG_RUIMTE_KEY,
    WAARDE
)
AS
    SELECT r.alg_ruimte_key,
           r.alg_ruimte_bruto_vloeropp
           || ' m2'
      FROM alg_v_aanwezigruimte r, alg_srtruimte sr
     WHERE r.alg_srtruimte_key = sr.alg_srtruimte_key;
     
CREATE OR REPLACE VIEW SKAF_V_LABEL_RMOMS_ROPP
(
    ALG_RUIMTE_KEY,
    WAARDE
)
AS
    SELECT r.alg_ruimte_key,
              r.alg_ruimte_omschrijving
           || ' - '
           || r.alg_ruimte_bruto_vloeropp
           || ' m2'
      FROM alg_v_aanwezigruimte r, alg_srtruimte sr
     WHERE r.alg_srtruimte_key = sr.alg_srtruimte_key;
     
     
-- Stap 5b: Actie/trigger bij sleur+pleur van thema HV-divisie
CREATE OR REPLACE TRIGGER skaf_t_thema_vloersoort_i_iu
   INSTEAD OF INSERT OR UPDATE
   ON skaf_v_thema_vloersoort
DECLARE

BEGIN

   			DELETE FROM alg_onrgoedkenmerk ok
   			WHERE ok.alg_onrgoed_key = :new.alg_ruimte_key
   			AND ok.alg_kenmerk_key = 1020
   			AND ok.alg_onrgoed_niveau='R';

   				--UPDATE alg_onrgoedkenmerk ok
   				--SET ok.alg_onrgoedkenmerk_waarde = TO_CHAR(:new.waarde_key)
   				--WHERE ok.alg_onrgoed_key = :new.alg_ruimte_key
   				--AND ok.alg_kenmerk_key = 1020
   				--AND ok.alg_onrgoed_niveau='R';

			INSERT INTO alg_onrgoedkenmerk (alg_onrgoed_key, alg_onrgoed_niveau, alg_kenmerk_key, alg_onrgoedkenmerk_waarde)
			VALUES (:new.alg_ruimte_key, 'R', 1020, to_char(:new.waarde_key));
	 EXCEPTION
	    WHEN OTHERS
	    THEN NULL;

END;
/

--- 																				EINDE - Facilitor Graphics / CAD 																		---

---                                         Rapportage                                                          ---

CREATE OR REPLACE VIEW skaf_v_rap_sch_ruimtenivo
(fclt_f_locatie, fclt_f_gebouw, fclt_f_verdieping, ruimte, ruimte_omschrijving, fclt_f_ruimtesoort, fclt_f_vloer, oppervlak, KDV, BSO, SLG)
AS
   SELECT l.alg_locatie_code || '-' || l.alg_locatie_omschrijving, g.alg_gebouw_naam, to_char(v.alg_verdieping_volgnr),
          r.alg_ruimte_nr, r.alg_ruimte_omschrijving, sr.alg_srtruimte_omschrijving, vloer.fac_usrdata_omschr, r.alg_ruimte_bruto_vloeropp,
          COALESCE(kdv.bestaat,'Nee') KDV, COALESCE(bso.bestaat,'Nee') BSO, COALESCE(slg.bestaat,'Nee') SLG
     FROM alg_v_aanwezigruimte r, alg_srtruimte sr, alg_verdieping v, alg_gebouw g, alg_locatie l,
          (SELECT rr.alg_ruimte_key, ud.fac_usrdata_omschr, ud.fac_usrdata_key
             FROM alg_onrgoedkenmerk ok,
                  fac_usrdata ud,
                  fac_kenmerkdomein rk,
                  alg_kenmerk k,
                  alg_v_aanwezigruimte rr
            WHERE rk.fac_usrtab_key = ud.fac_usrtab_key
              AND k.fac_kenmerkdomein_key = rk.fac_kenmerkdomein_key
              AND TO_CHAR (ud.fac_usrdata_key) = ok.alg_onrgoedkenmerk_waarde
              AND k.alg_kenmerk_key = 1020
              AND rr.alg_ruimte_key = ok.alg_onrgoed_key) vloer,
          (SELECT ra.alg_ruimte_key, a.prs_afdeling_key, 'Ja' AS bestaat
                    FROM prs_ruimteafdeling ra, prs_afdeling a
                    WHERE     ra.prs_afdeling_key = a.prs_afdeling_key
                    AND INSTR (UPPER (a.prs_afdeling_omschrijving), 'KDV') > 0
                    AND ra.prs_ruimteafdeling_verwijder IS NULL
                    AND a.prs_afdeling_verwijder IS NULL) kdv,
          (SELECT ra.alg_ruimte_key, a.prs_afdeling_key, 'Ja' AS bestaat
                    FROM prs_ruimteafdeling ra, prs_afdeling a
                    WHERE     ra.prs_afdeling_key = a.prs_afdeling_key
                    AND INSTR (UPPER (a.prs_afdeling_omschrijving), 'BSO') > 0
                    AND ra.prs_ruimteafdeling_verwijder IS NULL
                    AND a.prs_afdeling_verwijder IS NULL) bso,
          (SELECT ra.alg_ruimte_key, a.prs_afdeling_key, 'Ja' AS bestaat
                    FROM prs_ruimteafdeling ra, prs_afdeling a
                    WHERE     ra.prs_afdeling_key = a.prs_afdeling_key
                    AND INSTR (UPPER (a.prs_afdeling_omschrijving), 'SLG') > 0
                    AND ra.prs_ruimteafdeling_verwijder IS NULL
                    AND a.prs_afdeling_verwijder IS NULL) slg                                
    WHERE r.alg_srtruimte_key = sr.alg_srtruimte_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   r.alg_ruimte_key = vloer.alg_ruimte_key (+)
    AND   r.alg_ruimte_key = kdv.alg_ruimte_key(+)
    AND   r.alg_ruimte_key = bso.alg_ruimte_key(+)
    AND   r.alg_ruimte_key = slg.alg_ruimte_key(+)  ;  



CREATE OR REPLACE VIEW skaf_v_rap_sch_locatie_rsoort
(fclt_f_locatie, fclt_f_ruimtesoort, oppervlak)
AS
   SELECT fclt_f_locatie, fclt_f_ruimtesoort, SUM(oppervlak)
     FROM skaf_v_rap_sch_ruimtenivo
     GROUP BY fclt_f_locatie, fclt_f_ruimtesoort;

CREATE OR REPLACE VIEW skaf_v_rap_sch_locatie_vloer
(fclt_f_locatie, fclt_f_vloer, oppervlak)
AS
   SELECT fclt_f_locatie, fclt_f_vloer, SUM(oppervlak)
     FROM skaf_v_rap_sch_ruimtenivo
     GROUP BY fclt_f_locatie, fclt_f_vloer;

CREATE OR REPLACE VIEW skaf_v_rap_sch_locatie
(fclt_f_locatie, oppervlak)
AS
   SELECT fclt_f_locatie, SUM(oppervlak)
     FROM skaf_v_rap_sch_ruimtenivo
     GROUP BY fclt_f_locatie;



-- Alle waarden (zijn evt meer records) uit eigen tabel '$SYSTEM' waarvan de waarde_code met een
-- zekere prefix begint (volgens de systeem-setting PREFIX_FCLT_FACTUUR_XML_HEADER), komen in de header vd xml terecht.
CREATE OR REPLACE VIEW skaf_v_fclt_factuur_header (
   result,
   result_order
   )
AS
SELECT '<' || fac_usrdata_code || '>' || fac_usrdata_omschr || '</' || fac_usrdata_code || '>', 1
FROM fac_usrdata
WHERE  fac_usrtab_key =
    (SELECT fac_usrtab_key
     FROM fac_usrtab
     WHERE fac_usrtab_naam = '$SYSTEM')
AND UPPER(fac_usrdata_code) LIKE (SELECT UPPER(fac_usrdata_omschr) || '%'
                           FROM fac_usrdata
                           WHERE  fac_usrtab_key =
                                (SELECT fac_usrtab_key
                                 FROM fac_usrtab
                                 WHERE fac_usrtab_naam = '$SYSTEM')
                            AND fac_usrdata_code = '$PREFIX_FCLT_FACTUUR_XML_HEADER'
                            AND fac_usrdata_verwijder is null)
AND fac_usrdata_verwijder is null;


CREATE OR REPLACE VIEW skaf_v_fclt_factuur_body (
   result,
   result_order
   )
AS
   SELECT DECODE(regelnummer,0,'<fin_factuur>','<fin_factuurregel>')
       || '<fin_factuur_key>'                || fin_factuur_key                           || '</fin_factuur_key>'
       || '<fin_factuur_nr>'                 || xml.char_to_html(fin_factuur_nr)          || '</fin_factuur_nr>'
       || '<fin_factuur_bestand>'            || xml.char_to_html(fin_factuur_bestand)     || '</fin_factuur_bestand>'
       || '<fin_factuur_debiteur_nr>'        || xml.char_to_html(fin_factuur_debiteur_nr) || '</fin_factuur_debiteur_nr>'
       || '<bes_mld_cnt_opdracht_id>'        || xml.char_to_html(bes_mld_cnt_opdracht_id) || '</bes_mld_cnt_opdracht_id>'
       || '<fin_factuur_datum>'              || datum                                     || '</fin_factuur_datum>'
       || '<fin_factuur_boekmaand>'          || fin_factuur_boekmaand                     || '</fin_factuur_boekmaand>'
       || '<prs_kostensoort_refcode>'        || xml.char_to_html(grootboekrekening)       || '</prs_kostensoort_refcode>'
       || '<prs_leverancier_nr>'             || xml.char_to_html(crediteur_nr)            || '</prs_leverancier_nr>'
       || '<prs_bedrijf_naam>'               || xml.char_to_html(crediteur_naam)          || '</prs_bedrijf_naam>'
       || '<fin_factuur_totaal_exbtw>'       || fin_factuur_totaal_exbtw                  || '</fin_factuur_totaal_exbtw>'
       || '<fin_factuur_totaal_incbtw>'      || fin_factuur_totaal_incbtw                 || '</fin_factuur_totaal_incbtw>'
       || '<fin_factuurregel_omschrijving>'  || xml.char_to_html(fin_factuurregel_omschrijving)  || '</fin_factuurregel_omschrijving>'
       || '<fin_factuurregel_totaal>'        || fin_factuurregel_totaal                   || '</fin_factuurregel_totaal>'
       || '<fin_factuurregel_btw_perc>'      || btw_percentage                            || '</fin_factuurregel_btw_perc>'
       || '<fin_factuurregel_btw_bedrag>'    || btw_bedrag                                || '</fin_factuurregel_btw_bedrag>'
       || '<prs_kostenplaats_nr>'            || xml.char_to_html(prs_kostenplaats_nr)     || '</prs_kostenplaats_nr>'
       || DECODE(regelnummer, 0, '', DECODE(aant_factuur_regels - regelnummer,0,'</fin_factuurregel></fin_factuur>','</fin_factuurregel>'))
          ,
          (10000000 + fin_factuur_key) * 100 + regelnummer
   FROM (SELECT 0 regelnummer,
                aant_factuur_regels,
                fin_factuur_key,
                fin_factuur_nr,
                fin_factuur_bestand,
                fin_factuur_debiteur_nr,
                opdracht_id bes_mld_cnt_opdracht_id,
                TO_CHAR (fin_factuur_datum, 'ddmmyyyy') datum,
                fin_factuur_boekmaand,
                '' grootboekrekening,
                (SELECT prs_leverancier_nr
                 FROM prs_bedrijf b
                 WHERE b.prs_bedrijf_key = fg.prs_bedrijf_key) crediteur_nr,
                 (SELECT prs_bedrijf_naam
                 FROM prs_bedrijf b
                 WHERE b.prs_bedrijf_key = fg.prs_bedrijf_key) crediteur_naam,
                REPLACE (TO_CHAR (ROUND(bedrag_exbtw,2)), ',', '.') fin_factuur_totaal_exbtw,
                REPLACE (TO_CHAR (ROUND(bedrag_incbtw,2)), ',', '.') fin_factuur_totaal_incbtw,
                '' fin_factuurregel_omschrijving,
                '' fin_factuurregel_totaal,
                '' btw_percentage,
                '' btw_bedrag,
                '' prs_kostenplaats_nr
         FROM (SELECT fin_factuur_key,
                      opdracht_id,
                      fin_factuur_datum,
                      fin_factuur_boekmaand,
                      fin_factuur_nr,
                      fin_factuur_bestand,
                      prs_bedrijf_key,
                      fin_factuur_debiteur_nr,
                      COUNT(fin_factuur_key) AS aant_factuur_regels,
                      SUM (fin_factuurregel_totaal) bedrag_exbtw,
                      SUM (fin_factuurregel_totaal + fin_factuurregel_btw) bedrag_incbtw
               FROM skaf_v_factuur_geg_akkoord
               GROUP BY fin_factuur_key,
                        opdracht_id,
                        fin_factuur_datum,
                        fin_factuur_boekmaand,
                        fin_factuur_nr,
                        fin_factuur_bestand,
                        prs_bedrijf_key,
                        fin_factuur_debiteur_nr) fg
         UNION ALL
         SELECT fin_factuurregel_nr regelnummer,
                (SELECT COUNT(fin_factuur_key)
                 FROM skaf_v_factuur_geg_akkoord fc
                 WHERE fc.fin_factuur_key = fg.fin_factuur_key
                 GROUP BY fin_factuur_key,
                        opdracht_id,
                        fin_factuur_datum,
                        fin_factuur_nr,
                        prs_bedrijf_key,
                        fin_factuur_debiteur_nr) AS aant_factuur_regels,
                fin_factuur_key,
                fin_factuur_nr,
                '' fin_factuur_bestand,
                fin_factuur_debiteur_nr,
                '' bes_mld_cnt_opdracht_id,
                TO_CHAR (fin_factuur_datum, 'ddmmyyyy') datum,
                fin_factuur_boekmaand,
                prs_kostensoort_refcode grootboekrekening,
                (SELECT prs_leverancier_nr
                 FROM prs_bedrijf b
                 WHERE b.prs_bedrijf_key = fg.prs_bedrijf_key) crediteur_nr,
                 (SELECT prs_bedrijf_naam
                 FROM prs_bedrijf b
                 WHERE b.prs_bedrijf_key = fg.prs_bedrijf_key) crediteur_naam,
                 '' fin_factuur_totaal_exbtw,
                '' fin_factuur_totaal_incbtw,
                fin_factuurregel_omschrijving,
                REPLACE (TO_CHAR (ROUND(fin_factuurregel_totaal,2)), ',', '.') fin_factuurregel_totaal,
                DECODE (ROUND( (fin_factuurregel_btw / decode(fin_factuurregel_totaal,0,0.000000001,fin_factuurregel_totaal))
                              * 100)
                        , 19,'19', 6, '6', 0, '0', '-1') btw_percentage,
                REPLACE (TO_CHAR (ROUND(fin_factuurregel_btw,2)), ',', '.') btw_bedrag,
                prs_kostenplaats_nr
         FROM skaf_v_factuur_geg_akkoord fg
         ORDER BY fin_factuur_key, regelnummer);



CREATE OR REPLACE VIEW skaf_v_export_fclt_factuur (
   result,
   result_order
   )
AS
SELECT '<xml>',0 FROM DUAL
UNION
SELECT result, result_order FROM skaf_v_fclt_factuur_header
UNION
SELECT result, result_order FROM skaf_v_fclt_factuur_body
UNION
SELECT '</xml>',99999999999999999999 FROM DUAL;



CREATE OR REPLACE PROCEDURE skaf_export_fclt_factuur (
   p_applname   IN   VARCHAR2,
   p_applrun    IN   VARCHAR2,
   p_filedir    IN   VARCHAR2,
   p_filename   IN   VARCHAR2
)
AS

   -- In cursor cfactuur alle facturen die worden geexporteerd...
    CURSOR cfactuur
    IS
        SELECT fin_factuur_key
        FROM skaf_v_factuur_geg_akkoord
        GROUP BY fin_factuur_key;

BEGIN
  -- DE EXPORT IS OP BASIS VAN EEN VIEW GEREALISEERD, WAARNA NU ALS LAATSTE STAP NOG WAT ADMINISTRATIE VOLGT...
  FOR cfact IN cfactuur
  LOOP
      -- Zowel de factuur in tracking zetten als de status op verwerkt (=7) zetten
      fin.setfactuurstatus (cfact.fin_factuur_key, 7, NULL);
  END LOOP;
END;
/



CREATE OR REPLACE VIEW skaf_v_export_undo_exact (
   result,
   result_order
   )
AS
SELECT REPLACE(REPLACE(f.fin_factuur_key
       || ';'
       || opdracht_id
       || ';'
       || fin_factuur_datum
       || ';'
       || fin_factuur_nr
       || ';'
       || prs_bedrijf_key
       || ';'
       || fin_factuurregel_totaal
       || ';'
       || fin_factuurregel_btw
       || ';'
       || fin_factuurregel_nr
       || ';'
       || fin_factuur_debiteur_nr
       || ';'
       || project_nummer
       || ';'
       || prs_kostensoort_refcode
       || ';'
       || bes_opdr_key
       || ';'
       || mld_opdr_key
       || ';'
       || cnt_contract_key
      ,CHR (13), ''), CHR (10), '<ret>'), f.fin_factuur_key
 FROM skaf_v_factuur_geg f, fac_tracking t, fac_srtnotificatie sn
 WHERE sn.fac_srtnotificatie_code ='FINVER'
 AND sn.fac_srtnotificatie_key = t.fac_srtnotificatie_key
 AND t.fac_tracking_datum >= (SELECT MAX(t1.fac_tracking_datum) - (1 / (24*60))
                              FROM fac_tracking t1
                              WHERE t1.fac_srtnotificatie_key = t.fac_srtnotificatie_key)
 AND f.fin_factuur_key = t.fac_tracking_refkey
 AND f.fin_factuur_statuses_key = 7;



CREATE OR REPLACE PROCEDURE skaf_select_undo_exact (
   p_applname   IN   VARCHAR2,
   p_applrun    IN   VARCHAR2
)
AS
  v_errormsg       VARCHAR (200);
BEGIN
  v_errormsg := 'Geen akties';

END;
/

-- Procedure om alle geexporteerde facturen naar status verwerkt te zetten.
CREATE OR REPLACE PROCEDURE skaf_export_undo_exact (
   p_applname   IN   VARCHAR2,
   p_applrun    IN   VARCHAR2,
   p_filedir    IN   VARCHAR2,
   p_filename   IN   VARCHAR2
)
AS
   CURSOR cfactuur
    IS
      SELECT DISTINCT f.fin_factuur_key
      FROM fac_tracking t, fac_srtnotificatie sn, fin_factuur f
      WHERE sn.fac_srtnotificatie_code ='FINVER'
      AND sn.fac_srtnotificatie_key = t.fac_srtnotificatie_key
      AND t.fac_tracking_datum >= (SELECT MAX(t1.fac_tracking_datum) - (1 / (24*60))
                                   FROM fac_tracking t1
                                   WHERE t1.fac_srtnotificatie_key = t.fac_srtnotificatie_key)
      AND f.fin_factuur_key = t.fac_tracking_refkey
      AND fin_factuur_statuses_key = 7;

   v_errormsg       VARCHAR (200);
   oracle_err_num   NUMBER;
   oracle_err_mes   VARCHAR2 (200);
BEGIN
  v_errormsg := 'Geen akties';

  FOR cfact IN cfactuur
  LOOP
      -- Eerst de factuur in tracking zetten...
      -- Omdat van 7 naar 6 niet mag/geoorloofd is, lukt het niet via
      -- de reguliere functie fin.setfactuurstatus (cfact.fin_factuur_key, 6, NULL);

      -- Dan maar zelf en custom-made:
      fac.trackaction ('FINFOK', cfact.fin_factuur_key, NULL, NULL, 'Factuur uit archief teruggezet');
      UPDATE fin_factuur
      SET fin_factuur_statuses_key = 6
      WHERE fin_factuur_key = cfact.fin_factuur_key;

  END LOOP;
END;
/



-- Views voor Graphs / Dashboard

CREATE OR REPLACE FORCE VIEW skaf_v_rap_mld_status
(key,  mld_melding_behandelaar_key, [REDACTED], [REDACTED], alg_district_key, code)
AS
    SELECT mld_melding_key,
           mld_melding_behandelaar_key,
           d.ins_discipline_key,
           m.mld_alg_locatie_key,
           l.alg_district_key,
           'MLD_NEW'
    FROM mld_melding m, mld_stdmelding std, ins_tab_discipline d, alg_locatie l
    WHERE   m.mld_stdmelding_key = std.mld_stdmelding_key
    AND   m.mld_alg_locatie_key = l.alg_locatie_key
    AND mld_melding_status IN (2, 3, 0) -- 2-ingevoerd, 3-ingezien/te accepteren, 0-pending EN NIET 4-geaccepteerd, 7-uitgegeven, 99- niet opgelost
    AND std.mld_ins_discipline_key = d.ins_discipline_key
UNION ALL
    SELECT mld_melding_key,
           mld_melding_behandelaar_key,
           d.ins_discipline_key,
           m.mld_alg_locatie_key,
            l.alg_district_key,
           'MLD_READY2CLOSE'
    FROM mld_melding m, mld_stdmelding std, ins_tab_discipline d, alg_locatie l
    WHERE   m.mld_stdmelding_key = std.mld_stdmelding_key
    AND   m.mld_alg_locatie_key = l.alg_locatie_key
    AND mld_melding_status IN (4, 7, 99) -- 4-geaccepteerd, 7-uitgegeven, 99- niet opgelost EN NIET 2-ingevoerd, 3-ingezien, , 0-pending
    AND std.mld_ins_discipline_key = d.ins_discipline_key;

CREATE OR REPLACE FORCE VIEW skaf_v_rap_mld_status_overall
(key,  FCLT_3D_DISCIPLINE_KEY, FCLT_3D_LOCATIE_KEY, code)
AS SELECT key,  FCLT_3D_DISCIPLINE_KEY, FCLT_3D_LOCATIE_KEY, code
FROM skaf_v_rap_mld_status;


CREATE OR REPLACE FORCE VIEW skaf_v_rap_bes_status
(key,  FCLT_3D_DISCIPLINE_KEY, FCLT_3D_LOCATIE_KEY, code)
AS
    SELECT DISTINCT b.bes_bestelopdr_key,
                    bid.ins_discipline_key ,
                    l.alg_locatie_key,
                    'BES_READY2CLOSE'
    FROM bes_bestelopdr b, mld_adres a, alg_locatie l,
          bes_bestelopdr_item boi,
           bes_bestelling_item bi,
           bes_bestelling bst,
           bes_srtdeel isd,
           bes_srtgroep isg,
           ins_tab_discipline bid
    WHERE l.alg_locatie_key = A.ALG_LOCATIE_KEY
    and A.MLD_ADRES_KEY = b.mld_adres_key_lev
    and bes_bestelopdr_status = 4
    AND bi.bes_bestelopdr_item_key = boi.bes_bestelopdr_item_key
    AND bst.bes_bestelling_key = bi.bes_bestelling_key
    AND boi.bes_bestelopdr_key = b.bes_bestelopdr_key
    AND bi.bes_srtdeel_key = isd.bes_srtdeel_key
    AND isd.bes_srtgroep_key = isg.bes_srtgroep_key
    AND isg.ins_discipline_key = bid.ins_discipline_key;



CREATE OR REPLACE VIEW SKAF_V_GRAPH_MLD_BEHAND (FCLT_XAS_, FCLT_YAS_, FCLT_URL)
AS
SELECT prs_perslid_voornaam, count(*),  '/appl/mld/mld_search.asp?urole=bo' || '&' || 'behandel_key=' || MIN(prs_perslid_key) || '&' || 'autosearch=1'
FROM skaf_v_rap_mld_status, prs_perslid
WHERE mld_melding_behandelaar_key IS NOT NULL
AND prs_perslid_key = mld_melding_behandelaar_key
GROUP BY prs_perslid_voornaam;

CREATE OR REPLACE VIEW SKAF_V_GRAPH_MLD_PER_WIJK (FCLT_XAS_, FCLT_YAS_, FCLT_URL)
AS
SELECT SUBSTR(alg_district_omschrijving,1,10), count(*),  '/appl/mld/mld_search.asp?urole=mi' || '&' || 'districtkey=' || MIN(d.alg_district_key)
                                                           || '&' || 'mldstatus_str=4, 7, 99' || '&' || 'autosearch=1'
FROM skaf_v_rap_mld_status r, alg_district d
WHERE r.alg_district_key = d.alg_district_key
GROUP BY alg_district_omschrijving;


-- QRC-codes voor meldingen op objecten
-- Twee varianten (arai + ykpn) - Doorspreken op Acceptatie..keuze maken..
-- 1 - ARAI
CREATE OR REPLACE VIEW skaf_v_ins_qrc_mld
AS
   SELECT i.ins_deel_omschrijving hide_f_sort,
          l.alg_locatie_code||' '||l.alg_locatie_omschrijving fclt_f_locatie,
          o.alg_gebouw_omschrijving fclt_f_gebouw,
          o.alg_plaatsaanduiding fclt_f_plaats,
          d.ins_discipline_omschrijving fclt_f_discipline,
          g.ins_srtgroep_omschrijving fclt_f_objectgroep,
          s.ins_srtdeel_omschrijving fclt_f_objectsoort,
          i.ins_deel_omschrijving fclt_f_identificatie,
          d.ins_discipline_omschrijving||' / '||sm.mld_stdmelding_omschrijving fclt_f_melding,
          i.ins_deel_key,
          s.ins_srtdeel_key,
          o.alg_gebouw_code,
          o.alg_verdieping_code,
          i.ins_discipline_key fclt_3d_discipline_key,
          l.alg_locatie_key fclt_3d_locatie_key,
          i.ins_alg_ruimte_type,
          i.ins_alg_ruimte_key,
           (SELECT km.ins_kenmerkdeel_waarde
              FROM ins_kenmerkdeel km, ins_kenmerk k
             WHERE     km.ins_deel_key = i.ins_deel_key
                   AND km.ins_kenmerk_key = k.ins_kenmerk_key
                   AND k.ins_srtkenmerk_key = 62
                   AND km.ins_kenmerkdeel_verwijder IS NULL)
               merk,
           (SELECT km.ins_kenmerkdeel_waarde
              FROM ins_kenmerkdeel km, ins_kenmerk k
             WHERE     km.ins_deel_key = i.ins_deel_key
                   AND km.ins_kenmerk_key = k.ins_kenmerk_key
                   AND k.ins_srtkenmerk_key = 61
                   AND km.ins_kenmerkdeel_verwijder IS NULL)
               serienummer,
           (SELECT d.fac_usrdata_omschr
              FROM ins_kenmerkdeel km, ins_kenmerk k, fac_usrtab t, fac_usrdata d
             WHERE     km.ins_deel_key = i.ins_deel_key
                   AND km.ins_kenmerk_key = k.ins_kenmerk_key
                   AND k.ins_srtkenmerk_key = 63
                   AND km.ins_kenmerkdeel_verwijder IS NULL
                   AND t.fac_usrtab_key = d.fac_usrtab_key
                   AND t.fac_usrtab_key = 161
                   AND fac.safe_to_number(km.ins_kenmerkdeel_waarde) = d.fac_usrdata_key)
               type_opvang,
          b.fac_bookmark_id hide_f_bookmark_id
     FROM ins_deel i,
          ins_v_alg_overzicht o,
          ins_srtdeel s,
          ins_srtgroep g,
          ins_discipline d,
          alg_locatie l,
          fac_bookmark b,
          mld_stdmelding sm,
          mld_stdmelding_srtinst si
    WHERE b.fac_bookmark_naam = 'QRC_MELDING_STORING'
      AND (b.fac_bookmark_expire IS NULL OR b.fac_bookmark_expire > SYSDATE)
      AND ins_deel_verwijder IS NULL
      AND i.ins_deel_module = 'INS'
      AND i.ins_deel_parent_key IS NULL
      AND s.ins_srtdeel_key = i.ins_srtdeel_key
      AND g.ins_srtgroep_key = s.ins_srtgroep_key
      AND d.ins_discipline_key = g.ins_discipline_key
      AND o.alg_onroerendgoed_keys = i.ins_alg_ruimte_key
      AND o.alg_onroerendgoed_type = i.ins_alg_ruimte_type
      AND i.ins_alg_ruimte_type IN ('T', 'R', 'W')
      AND o.alg_locatie_key = i.ins_alg_locatie_key
      AND i.ins_alg_locatie_key = l.alg_locatie_key
      AND sm.mld_stdmelding_key = si.mld_stdmelding_key
      AND si.ins_srtinstallatie_key = DECODE(si.ins_srtinstallatie_niveau,
                                      'S', i.ins_srtdeel_key,
                                      'G', s.ins_srtgroep_key,
                                      'D', g.ins_discipline_key)
                                      ;

--2. YKPN  
CREATE OR REPLACE VIEW skaf_v_rap_mld_qrc2
(
   omgeving,
   fclt_3d_locatie_key,
   fclt_3d_discipline_key,
   fclt_f_discipline,
   mld_stdmelding_omschrijving,
   stdmelding_key,
   alg_locatie_omschrijving,
   fclt_f_gebouw,
   fclt_f_verdieping,
   plaats,
   ruimte_key,
   ruimtefunctie,
   fclt_f_objectdiscipline,
   fclt_f_objectgroep,
   objectsoort_key,
   fclt_f_objectsoort,
   objectsoort_code,
   ins_deel_key,
   ins_deel_omschrijving,
   ins_deel_upper,
   merk,
   serienummer,
   type_opvang,
   hide_f_bookmark_id,
   hide_f_bookmark_path,
   hide_f_bookmark_query,
   variabele
)
AS
   SELECT   user omgeving,
            l.alg_locatie_key,
            disc.ins_discipline_key,
            disc.ins_discipline_omschrijving,
            msd.mld_stdmelding_omschrijving,
            msd.mld_stdmelding_key,
            l.alg_locatie_omschrijving,
            o.alg_gebouw_omschrijving,
            o.alg_verdieping_omschrijving,
            o.alg_plaatsaanduiding plaats,
            d.ins_alg_ruimte_key ruimte_key,
            sr.alg_srtruimte_omschrijving,
            disc.ins_discipline_omschrijving,
            sg.ins_srtgroep_omschrijving,
            sd.ins_srtdeel_key,
            sd.ins_srtdeel_omschrijving,
            sd.ins_srtdeel_code,
            d.ins_deel_key,
            d.ins_deel_omschrijving,
            d.ins_deel_upper,
           (SELECT km.ins_kenmerkdeel_waarde
              FROM ins_kenmerkdeel km, ins_kenmerk k
             WHERE     km.ins_deel_key = d.ins_deel_key
                   AND km.ins_kenmerk_key = k.ins_kenmerk_key
                   AND k.ins_srtkenmerk_key = 62
                   AND km.ins_kenmerkdeel_verwijder IS NULL)
               merk,
           (SELECT km.ins_kenmerkdeel_waarde
              FROM ins_kenmerkdeel km, ins_kenmerk k
             WHERE     km.ins_deel_key = d.ins_deel_key
                   AND km.ins_kenmerk_key = k.ins_kenmerk_key
                   AND k.ins_srtkenmerk_key = 61
                   AND km.ins_kenmerkdeel_verwijder IS NULL)
               serienummer,
           (SELECT d.fac_usrdata_omschr
              FROM ins_kenmerkdeel km, ins_kenmerk k, fac_usrtab t, fac_usrdata d
             WHERE     km.ins_deel_key = d.ins_deel_key
                   AND km.ins_kenmerk_key = k.ins_kenmerk_key
                   AND k.ins_srtkenmerk_key = 63
                   AND km.ins_kenmerkdeel_verwijder IS NULL
                   AND t.fac_usrtab_key = d.fac_usrtab_key
                   AND t.fac_usrtab_key = 161
                   AND fac.safe_to_number(km.ins_kenmerkdeel_waarde) = d.fac_usrdata_key)
               type_opvang,
            b.fac_bookmark_id,
            b.fac_bookmark_path,
            b.fac_bookmark_query,
            'INS_KEY'
     FROM   (SELECT fac_bookmark_id, fac_bookmark_path, fac_bookmark_query 
              FROM   fac_bookmark
              WHERE   UPPER (fac_bookmark_path) = 'APPL/PDA/MELDING.ASP'
                      AND fac_bookmark_naam = 'QRC_MELDING_STORING' )
            b,                                           
            (SELECT   sm.mld_stdmelding_key,
                      sm.mld_stdmelding_omschrijving,
                      si.ins_srtinstallatie_key ins_srtdeel_key
               FROM   mld_stdmelding sm,
                      mld_stdmelding_srtinst si
              WHERE       sm.mld_stdmelding_key = si.mld_stdmelding_key
                      AND sm.mld_stdmelding_verwijder IS NULL
                      AND si.ins_srtinstallatie_niveau = 'S'
             UNION ALL
             SELECT   sm.mld_stdmelding_key,
                      sm.mld_stdmelding_omschrijving,
                      sd.ins_srtdeel_key
               FROM   mld_stdmelding sm,
                      mld_stdmelding_srtinst si,
                      ins_srtdeel sd
              WHERE       sm.mld_stdmelding_key = si.mld_stdmelding_key
                      AND sm.mld_stdmelding_verwijder IS NULL
                      AND si.ins_srtinstallatie_niveau = 'G'
                      AND si.ins_srtinstallatie_key = sd.ins_srtgroep_key
             UNION ALL
             SELECT   sm.mld_stdmelding_key,
                      sm.mld_stdmelding_omschrijving,
                      sd.ins_srtdeel_key
               FROM   mld_stdmelding sm,
                      mld_stdmelding_srtinst si,
                      ins_srtgroep sg,
                      ins_srtdeel sd
              WHERE       sm.mld_stdmelding_key = si.mld_stdmelding_key
                      AND sm.mld_stdmelding_verwijder IS NULL
                      AND si.ins_srtinstallatie_niveau = 'D'
                      AND si.ins_srtinstallatie_key = sg.ins_discipline_key
                      AND sg.ins_srtgroep_key = sd.ins_srtgroep_key) msd, -- object-meldingen
            ins_v_aanwezigdeel d,
            ins_srtdeel sd,
            ins_srtgroep sg,
            ins_discipline disc,
            ins_v_alg_overzicht o,
            alg_locatie l,
            alg_ruimte r,
            alg_srtruimte sr
    WHERE   
            msd.ins_srtdeel_key = d.ins_srtdeel_key
            AND d.ins_alg_ruimte_type IN ('T', 'R', 'W')
            AND d.ins_deel_parent_key IS NULL
            AND d.ins_srtdeel_key = sd.ins_srtdeel_key
            AND sd.ins_srtgroep_key = sg.ins_srtgroep_key
            AND sg.ins_discipline_key = disc.ins_discipline_key
            AND d.ins_alg_ruimte_key = o.alg_onroerendgoed_keys
            AND d.ins_alg_ruimte_type = o.alg_onroerendgoed_type
            AND d.ins_alg_locatie_key = o.alg_locatie_key        -- redundant?
            AND o.alg_locatie_key = l.alg_locatie_key
            AND d.ins_alg_ruimte_key = r.alg_ruimte_key
            AND r.alg_srtruimte_key = sr.alg_srtruimte_key(+)
;

-- Keuzelijst Kostenplaats bij locaties
CREATE OR REPLACE VIEW SKAF_V_KOSTENPLAATS
(
    PRS_KOSTENPLAATS_KEY,
    OMSCHRIJVING,
    PRS_KOSTENPLAATS_VERWIJDER
)
AS
      SELECT prs_kostenplaats_key,
             prs_kostenplaats_upper || ' ' || prs_kostenplaats_omschrijving,
             prs_kostenplaats_verwijder
        FROM prs_kostenplaats
       WHERE prs_kostenplaats_verwijder IS NULL
    ORDER BY prs_kostenplaats_upper DESC;


-- view tbv widget met bol bestellingen die handmatig als verzonden gemarkeerd moeten    
CREATE OR REPLACE VIEW skaf_v_bolbes
(
    aantal
)
AS
    SELECT COUNT (*)
      FROM bes_bestelopdr
     WHERE prs_bedrijf_key = 12921 -- bol.com
       AND bes_bestelopdr_status = 3 -- in behandeling; 

-- SKAF#86147: Nieuwe, uitgegeven en afgemelde meldingen per week (incl. op
--             tijd volgens afspraak/SLA).
CREATE OR REPLACE VIEW SKAF_V_UDR_MLD_SLA
AS
    SELECT weeknr,
           mldnew, ordnew, mldafm,
           afs_optijd,
           TO_CHAR (ROUND (DECODE (mldafm, 0, TO_NUMBER (NULL), afs_optijd / mldafm) * 100, 0)) || '%'
               afs_optijd_pct,
           sla_optijd,
           TO_CHAR (ROUND (DECODE (mldafm, 0, TO_NUMBER (NULL), sla_optijd / mldafm) * 100, 0)) || '%'
               sla_optijd_pct
      FROM (  SELECT weeknr,
                     SUM (mldnew) mldnew, SUM (ordnew) ordnew, SUM (mldafm) mldafm,
                     SUM (afs_optijd) afs_optijd,
                     SUM (sla_optijd) sla_optijd
                FROM (SELECT melding_key,
                             TO_CHAR (melding_datum, 'iyyy-iw') weeknr,
                             1 mldnew, 0 ordnew, 0 mldafm,
                             0 afs_optijd,
                             0 sla_optijd
                        FROM mld_v_udr_melding
                       WHERE melding_datum > TRUNC (ADD_MONTHS (SYSDATE, -12), 'iyyy')
                      UNION ALL
                      SELECT melding_key,
                             TO_CHAR (opdracht_datum, 'iyyy-iw') weeknr,
                             0 mldnew, 1 ordnew, 0 mldafm,
                             0 afs_optijd,
                             0 sla_optijd
                        FROM mld_v_udr_opdracht
                       WHERE opdracht_datum > TRUNC (ADD_MONTHS (SYSDATE, -12), 'iyyy')
                      UNION ALL
                      SELECT melding_key,
                             TO_CHAR (melding_afgemeld, 'iyyy-iw') weeknr,
                             0 mldnew, 0 ordnew, 1 mldafm,
                             DECODE (UPPER (afspraak_sla_optijd), 'JA', 1, 0) afs_optijd,
                             DECODE (UPPER (uitvoering_sla_optijd), 'JA', 1, 0) sla_optijd
                        FROM mld_v_udr_melding
                       WHERE melding_afgemeld > TRUNC (ADD_MONTHS (SYSDATE, -12), 'iyyy'))
            GROUP BY weeknr);

-- SKAF#86147: Staafdiagram met lopende meldingen per gekozen groepering met
--             kleuren (volgens SLA): [a] groen=op tijd en [b] rood=te laat.
CREATE OR REPLACE VIEW SKAF_V_UDR_MLD_OPEN
(
   mld_melding_key,
   meldingid,
   vakgroeptype,
   --vakgroep_key,
   fclt_3d_discipline_key,
   vakgroep,
   standaardmelding,
   --locatie_key,
   [REDACTED],
   locatiecode,
   locatie,
   behandelteam_key,
   behandelteam,
   prio,
   status,
   mldnew,
   melding_datum,
   geplande_einddatum,
   uitvoertijd,
   optijd_telaat
)
AS
   SELECT x.mld_melding_key,
          x.ins_srtdiscipline_prefix || TO_CHAR (x.mld_melding_key) meldingid,
          x.ins_srtdiscipline_omschrijving vakgroeptype,
          x.ins_discipline_key vakgroep_key,
          x.ins_discipline_omschrijving vakgroep,
          x.mld_stdmelding_omschrijving standaardmelding,
          x.alg_locatie_key,
          x.alg_locatie_code,
          x.alg_locatie_omschrijving,
          x.behandelteam_key,
          COALESCE (x.behandelteam, '<Geen>'),
          x.mld_melding_spoed,
          x.mld_statuses_omschrijving,
          x.mldnew,
          x.mld_melding_datum,
          x.mld_melding_einddatum,
          TO_CHAR (x.mld_melding_t_uitvoertijd.tijdsduur) || x.mld_melding_t_uitvoertijd.eenheid uitvoertijd,
          --DECODE (SIGN (x.gepland_over_2wd - SYSDATE),
          --        -1,
          --        DECODE (SIGN (x.mld_melding_einddatum - SYSDATE),
          --                -1, 'Te laat',                               -- Rood
          --                'Over 2 dagen te laat'),                   -- Oranje
          --        DECODE (BITAND (x.mld_melding_actiecode, 1),
          --                1, 'Actie bij behandelaar',  -- Groen en "alle tijd"
          --                'Actie bij aanvrager'))      -- Blauw en "alle tijd"
          DECODE (SIGN (mld_melding_einddatum_std - SYSDATE),    
                  -1, 'Te laat',                                       -- Rood
                  'Op tijd')                                          -- Groen
             optijd_telaat
     FROM (SELECT m.mld_melding_key,
                  sd.ins_srtdiscipline_prefix,
                  sd.ins_srtdiscipline_omschrijving,
                  md.ins_discipline_key,
                  md.ins_discipline_omschrijving,
                  sm.mld_stdmelding_key,
                  sm.mld_stdmelding_omschrijving,
                  l.alg_locatie_key,
                  l.alg_locatie_code,
                  l.alg_locatie_omschrijving,
                  bt.ins_discipline_key behandelteam_key,
                  bt.ins_discipline_omschrijving behandelteam,
                  m.mld_melding_spoed,
                  ms.mld_statuses_omschrijving,
                  fac.gettrackingdate ('MLDNEW', m.mld_melding_key) mldnew,
                  m.mld_melding_datum,
                  m.mld_melding_einddatum,
                  m.mld_melding_t_uitvoertijd,
                  m.mld_melding_einddatum_std,
                  --fac.datumtijdplusuitvoertijd (
                  --   m.mld_melding_einddatum  - 14,
                  --   mld.getactualuitvoer (m.mld_melding_einddatum - 14, m.mld_melding_einddatum, sm.mld_stdmelding_key, -1, -1, -1, 'D').tijdsduur - 2,
                  --   'D')
                  --   gepland_over_2wd, -- Twee werkdagen voor geplande einddatum (niet triviaal, maar best slim bedacht, al zeg ik het zelf)
                  m.mld_melding_actiecode -- 1=bij behandelaar/2=bij aanvrager/129=reactie aanvrager/257=info aanvrager/258=actie aanvrager
             FROM mld_melding m,
                  mld_stdmelding sm,
                  mld_discipline md,
                  ins_srtdiscipline sd,
                  alg_locatie l,
                  mld_statuses ms,
                  mld_discipline bt
            WHERE 1 = 1
                  --AND m.mld_melding_datum > TRUNC (ADD_MONTHS (SYSDATE, -24), 'yyyy')
                  --AND fac.gettrackingdate ('MLDNEW', m.mld_melding_key) > TRUNC (ADD_MONTHS (SYSDATE, -24), 'yyyy')
                  AND m.fac_activiteit_key IS NULL
                  AND m.mld_melding_status IN (0, 2, 3, 4, 7, 99) -- Open meldingen
                  AND m.mld_stdmelding_key = sm.mld_stdmelding_key
                  --AND sm.mld_stdmelding_key NOT IN (-1)         -- Uitsluiten?
                  AND sm.mld_ins_discipline_key = md.ins_discipline_key
                  --AND md.ins_discipline_key NOT IN (-1)         -- Uitsluiten?
                  AND md.ins_srtdiscipline_key = sd.ins_srtdiscipline_key
                  --AND sd.ins_srtdiscipline_key NOT IN (-1)      -- Uitsluiten?
                  AND m.mld_alg_locatie_key = l.alg_locatie_key(+)
                  AND m.mld_melding_status = ms.mld_statuses_key
                  AND m.mld_ins_discipline_key = bt.ins_discipline_key(+)) x;
------ payload end   ------

SET DEFINE OFF
BEGIN adm.systrackscriptId ('$Id$', 0); END;
/
/*
BEGIN fac.registercustversion ('SKAF', 11); END;
/
*/
COMMIT;
SET ECHO OFF
SPOOL OFF
SET DEFINE ON
PROMPT Logfile of this upgrade is: &fcltlogfile
