--
-- $Id$
--
-- Script containing customer specific sql statements for the FACILITOR database

DEFINE thisfile = 'CVGZ.SQL'
DEFINE dbuser   = '^CVGZ'

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 PACKAGE CVGZ
AS
   FUNCTION change_delimitor (p_string      VARCHAR2,
                              p_delimitor   VARCHAR2)
     RETURN VARCHAR2;

   PROCEDURE update_organisatie (p_import_key IN NUMBER);

   PROCEDURE anonimiseer_prs;

   PROCEDURE anonimiseer_bezoeker;

   PROCEDURE anonimiseer_mld;
   
END;
/

CREATE OR REPLACE PACKAGE BODY CVGZ
AS

   FUNCTION change_delimitor (p_string      VARCHAR2,
                              p_delimitor   VARCHAR2)
     RETURN VARCHAR2
   AS
       v_field    VARCHAR2 (1000) := 'QueQuLeQue';
       v_result   VARCHAR2 (2000);
       v_line     VARCHAR2 (2000);
   BEGIN
       v_line := p_string;

       WHILE v_line IS NOT NULL
       LOOP
           fac.imp_getfield (v_line, p_delimitor, v_field);
           v_result := v_result || '"' || v_field || '";';
       END LOOP;

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


   PROCEDURE update_organisatie (
      p_import_key IN NUMBER
   ) IS

     c_fielddelimitor            VARCHAR2 (1) := ';';

     v_newline                   fac_imp_file.fac_imp_file_line%TYPE;    -- Input line
     v_errormsg                  VARCHAR2 (1000);
     v_errorhint                 VARCHAR2 (1000);
     oracle_err_num              NUMBER;
     oracle_err_mes              VARCHAR2 (200);
     v_aanduiding                VARCHAR2 (200);

     v_count                     NUMBER(10);
     v_ongeldig                  NUMBER(1);
     v_count_update              NUMBER(10);
     v_count_error               NUMBER(10);
     v_count_tot                 NUMBER(10);

     --
     v_bedrijf_key               NUMBER(10);
     v_kostenplaats_key          NUMBER(10);
     v_prs_afdeling_parentkey    NUMBER(10);
     v_afdeling_key              NUMBER(10);

     CURSOR cBedrijf
     IS
       SELECT  min(prs_bedrijf_naam) prs_bedrijf_naam
         FROM  fac_imp_organisatie
         WHERE prs_bedrijf_naam is not null
        GROUP BY upper(prs_bedrijf_naam);

     -- Bij 1-op-1 relatie tussen kostenplaats en afdeling de kostenplaatsomschrijving zetten op
     -- de afdelingsomschrijving, ander de kostenplaatsomschrijving leeg laten.
     CURSOR cKPN
     IS
       SELECT  MIN(prs_kostenplaats_nr)                              prs_kostenplaats_nr,
               DECODE(COUNT(*),1,MAX(prs_afdeling_omschrijving),'')  prs_kostenplaats_omschrijving,
               'PRS'                                                 prs_kostenplaats_module
         FROM  FAC_IMP_ORGANISATIE
         WHERE prs_kostenplaats_nr IS NOT NULL
        GROUP BY UPPER(prs_kostenplaats_nr);

     CURSOR cAfdeling1
     IS
       SELECT    MIN(prs_bedrijf_naam)                 prs_bedrijf_naam
                ,UPPER(prs_bedrijf_naam)               prs_bedrijf_naam_upper
                ,MIN(prs_afdeling_naam)                prs_afdeling_naam
                ,UPPER(prs_afdeling_naam)              prs_afdeling_upper
                ,MAX(prs_afdeling_omschrijving)        prs_afdeling_omschrijving
                ,MIN(prs_afdeling_naam_parent)         prs_afdeling_naam_parent
                ,MIN(UPPER(prs_afdeling_naam_parent))  prs_afdeling_naam_parent_upper
                ,MAX(prs_kostenplaats_nr)              prs_kostenplaats_nr
                ,MAX(UPPER(prs_kostenplaats_nr))       prs_kostenplaats_upper
                ,NIVEAU
           FROM  (SELECT 1  NIVEAU,
                         a1.prs_bedrijf_naam,
                         a1.prs_afdeling_naam,
                         a1.prs_afdeling_omschrijving,
                         a1.prs_kostenplaats_nr,
                         '' PRS_AFDELING_NAAM_PARENT
                    FROM FAC_IMP_ORGANISATIE a1
                   WHERE a1.prs_afdeling_naam_parent IS NULL
                 UNION ALL
                  SELECT 2,
                         a2.prs_bedrijf_naam,
                         a2.prs_afdeling_naam,
                         a2.prs_afdeling_omschrijving,
                         a2.prs_kostenplaats_nr,
                         a1.prs_afdeling_naam
                    FROM FAC_IMP_ORGANISATIE a1, FAC_IMP_ORGANISATIE a2
                   WHERE a2.prs_afdeling_naam_parent = a1.prs_afdeling_naam
                     AND a1.prs_afdeling_naam_parent IS NULL
                 UNION ALL
                  SELECT 3,
                         a3.prs_bedrijf_naam,
                         a3.prs_afdeling_naam,
                         a3.prs_afdeling_omschrijving,
                         a3.prs_kostenplaats_nr,
                         a2.prs_afdeling_naam
                    FROM FAC_IMP_ORGANISATIE a1, FAC_IMP_ORGANISATIE a2, FAC_IMP_ORGANISATIE a3
                   WHERE a3.prs_afdeling_naam_parent = a2.prs_afdeling_naam
                     AND a2.prs_afdeling_naam_parent = a1.prs_afdeling_naam
                     AND a1.prs_afdeling_naam_parent IS NULL
                 UNION ALL
                  SELECT 4,
                         a4.prs_bedrijf_naam,
                         a4.prs_afdeling_naam,
                         a4.prs_afdeling_omschrijving,
                         a4.prs_kostenplaats_nr,
                         a3.prs_afdeling_naam
                    FROM FAC_IMP_ORGANISATIE a1, FAC_IMP_ORGANISATIE a2, FAC_IMP_ORGANISATIE a3, FAC_IMP_ORGANISATIE a4
                   WHERE a4.prs_afdeling_naam_parent = a3.prs_afdeling_naam
                     AND a3.prs_afdeling_naam_parent = a2.prs_afdeling_naam
                     AND a2.prs_afdeling_naam_parent = a1.prs_afdeling_naam
                     AND a1.prs_afdeling_naam_parent IS NULL
                 UNION ALL
                  SELECT 5,
                         a5.prs_bedrijf_naam,
                         a5.prs_afdeling_naam,
                         a5.prs_afdeling_omschrijving,
                         a5.prs_kostenplaats_nr,
                         a4.prs_afdeling_naam
                    FROM FAC_IMP_ORGANISATIE a1, FAC_IMP_ORGANISATIE a2, FAC_IMP_ORGANISATIE a3, FAC_IMP_ORGANISATIE a4, FAC_IMP_ORGANISATIE a5
                   WHERE a5.prs_afdeling_naam_parent = a4.prs_afdeling_naam
                     AND a4.prs_afdeling_naam_parent = a3.prs_afdeling_naam
                     AND a3.prs_afdeling_naam_parent = a2.prs_afdeling_naam
                     AND a2.prs_afdeling_naam_parent = a1.prs_afdeling_naam
                     AND a1.prs_afdeling_naam_parent IS NULL)
          WHERE ((prs_bedrijf_naam IS NOT NULL) AND (prs_afdeling_naam IS NOT NULL)) -- geen lege regels
       GROUP BY UPPER(prs_bedrijf_naam), UPPER(prs_afdeling_naam), NIVEAU
       ORDER BY NIVEAU;

   BEGIN

     v_count_update := 0;
     v_count_tot := 0;
     v_count_error := 0;
     FOR recBedrijf IN cBedrijf
       LOOP
         BEGIN

           v_count_tot := v_count_tot + 1;

           v_aanduiding := recBedrijf.PRS_BEDRIJF_NAAM||'| ';
           v_errormsg := '';
           v_errorhint := '';

           v_errorhint := 'Kijken of bedrijf al bestaat';
           SELECT count(*)
           INTO   v_count
           FROM   prs_bedrijf
           WHERE  upper(prs_bedrijf_naam_upper) = upper(recBedrijf.prs_bedrijf_naam)
           AND    prs_bedrijf_verwijder is null;

           IF (v_count = 0) THEN

             v_errorhint := 'Fout bij toevoegen bedrijf';
             INSERT INTO PRS_BEDRIJF (
              prs_bedrijf_naam
             ,prs_bedrijf_intern
             ) VALUES (
             recBedrijf.prs_bedrijf_naam
             ,1
             );

             commit;
             v_count_update := v_count_update + 1;

           END IF;

           EXCEPTION WHEN OTHERS THEN
             v_count_error := v_count_error + 1;
             oracle_err_num := SQLCODE;
             oracle_err_mes := SUBSTR(SQLERRM, 1, 200);
             IF (v_errormsg IS NULL) THEN
               v_errormsg := 'Fout tijdens verwerken van bedrijf ['||recBedrijf.PRS_BEDRIJF_NAAM||']';
             END IF;
             v_errorhint := v_errorhint||': ' || oracle_err_mes;

             fac.imp_writelog (p_import_key,
                           'E',
                           v_aanduiding||v_errormsg,
                           v_errorhint
                          );

             commit; -- tbv logging

         END;
       END LOOP;

       fac.imp_writelog (p_import_key,
                     'S',
                     'Bedrijf: verwerkte regels zonder foutmelding: '||to_char(v_count_tot-v_count_error),
                     ''
                    );

       fac.imp_writelog (p_import_key,
                     'S',
                     'Bedrijf: verwerkte regels met foutmelding: <b>'||to_char(v_count_error),
                     ''
                    );


     v_count_update := 0;
     v_count_tot := 0;
     v_count_error := 0;
     FOR recKPN IN cKPN
       LOOP
         BEGIN
           v_aanduiding := recKPN.PRS_KOSTENPLAATS_NR||'| ';
           v_errormsg := '';
           v_errorhint := '';
           v_count_tot := v_count_tot + 1;

           v_errorhint := 'Kijken of de kostenplaats al bestaat';
           SELECT count(*)
           INTO   v_count
           FROM   prs_kostenplaats
           WHERE  prs_kostenplaats_verwijder is null
           AND    upper(prs_kostenplaats_upper) = upper(recKPN.prs_kostenplaats_nr);

           IF (v_count = 0) THEN

             v_errorhint := 'Fout bij toevoegen kostenplaats';
             INSERT INTO PRS_KOSTENPLAATS
             (PRS_KOSTENPLAATS_NR
             ,PRS_KOSTENPLAATS_OMSCHRIJVING
             ,PRS_KOSTENPLAATS_MODULE)
             VALUES
             (recKPN.prs_kostenplaats_nr
             ,recKPN.prs_kostenplaats_omschrijving
             ,recKPN.prs_kostenplaats_module);

             commit;
             v_count_update := v_count_update + 1;

           END IF;

           EXCEPTION WHEN OTHERS THEN
             v_count_error := v_count_error + 1;
             oracle_err_num := SQLCODE;
             oracle_err_mes := SUBSTR(SQLERRM, 1, 200);
             IF (v_errormsg IS NULL) THEN
               v_errormsg := 'Fout tijdens verwerken van kostenplaats ['||recKPN.PRS_KOSTENPLAATS_NR||']';
             END IF;
             v_errorhint := v_errorhint||': ' || oracle_err_mes;

             fac.imp_writelog (p_import_key,
                           'E',
                           v_aanduiding||v_errormsg,
                           v_errorhint
                          );

             commit; -- tbv logging

         END;
       END LOOP;

       fac.imp_writelog (p_import_key,
                     'S',
                     'Kostenplaats: verwerkte regels zonder foutmelding: '||to_char(v_count_tot-v_count_error),
                     ''
                    );

       fac.imp_writelog (p_import_key,
                     'S',
                     'Kostenplaats: verwerkte regels met foutmelding: <b>'||to_char(v_count_error),
                     ''
                    );



     v_count_update := 0;
     v_count_tot := 0;
     v_count_error := 0;
     FOR recAfdeling1 IN cAfdeling1
       LOOP
         BEGIN
           v_count_tot := v_count_tot + 1;

           v_aanduiding := recAfdeling1.prs_bedrijf_naam||'|'||recAfdeling1.prs_afdeling_naam||'|'||recAfdeling1.prs_afdeling_naam_parent ||'|'||recAfdeling1.prs_kostenplaats_upper ||'| ';
           v_errormsg := '';
           v_errorhint := '';

           IF (recAfdeling1.niveau = 1) THEN
             v_errorhint := 'Bedrijf niet gevonden bij afdeling';
             v_prs_afdeling_parentkey := to_number(NULL);
             SELECT prs_bedrijf_key
             INTO   v_bedrijf_key
             FROM   prs_bedrijf
             WHERE  prs_bedrijf_verwijder IS NULL
             AND    prs_bedrijf_naam_upper = recAfdeling1.prs_bedrijf_naam_upper;
           ELSE
             v_errorhint := 'Parent van de afdeling niet gevonden';
             v_bedrijf_key := to_number(NULL);
             SELECT prs_afdeling_key
             INTO   v_prs_afdeling_parentkey
             FROM   prs_afdeling
             WHERE  prs_afdeling_verwijder IS NULL
             AND    prs_afdeling_upper = recAfdeling1.prs_afdeling_naam_parent_upper;
           END IF;

           v_errorhint := 'Kostenplaats bij afdeling bepalen';
           SELECT  COUNT(*), NVL(MAX(prs_kostenplaats_key),to_number(NULL))
           INTO    v_count, v_kostenplaats_key
           FROM    PRS_KOSTENPLAATS
           WHERE   prs_kostenplaats_verwijder IS NULL
           AND     prs_kostenplaats_upper = UPPER(trim(recAfdeling1.prs_kostenplaats_upper))
           AND     UPPER(prs_kostenplaats_module) = 'PRS';

           v_errorhint := 'Bepalen of afdeling bestaat';
           SELECT count(*), MAX(prs_afdeling_key)
           INTO   v_count, v_afdeling_key
           FROM   prs_afdeling
           WHERE  upper(rtrim(prs_afdeling_naam)) = upper(rtrim(recAfdeling1.prs_afdeling_naam))
           AND    prs_afdeling_verwijder is null;

           IF (v_count = 0) THEN
             v_errorhint := 'Fout bij toevoegen afdeling';
             insert into PRS_AFDELING
             (prs_afdeling_naam
             ,prs_afdeling_omschrijving
             ,prs_bedrijf_key
             ,prs_afdeling_parentkey
             ,prs_kostenplaats_key
             )
              values
             (recAfdeling1.prs_afdeling_naam
             ,recAfdeling1.prs_afdeling_omschrijving
             ,v_bedrijf_key
             ,v_prs_afdeling_parentkey
             ,v_kostenplaats_key
             );
             v_count_update := v_count_update + 1;

           ELSIF v_count = 1 THEN
             v_errorhint := 'Fout bij aanpassen afdeling';
             UPDATE prs_afdeling
                SET prs_afdeling_omschrijving = recAfdeling1.prs_afdeling_omschrijving,
                    prs_afdeling_parentkey = v_prs_afdeling_parentkey,
                    prs_kostenplaats_key = v_kostenplaats_key,
                    prs_bedrijf_key = v_bedrijf_key
              WHERE prs_afdeling_key = v_afdeling_key;
             v_count_update := v_count_update + 1;
           ELSE
              fac.imp_writelog (p_import_key,
                                'E',
                                v_aanduiding|| 'Fout bij het aanmaken / bijwerken van afdeling',
                                'Meerdere afdelingen gevonden.'
                          );
           END IF;

           EXCEPTION WHEN OTHERS THEN

             v_count_error := v_count_error + 1;
             oracle_err_num := SQLCODE;
             oracle_err_mes := SUBSTR(SQLERRM, 1, 200);
             IF (v_errormsg IS NULL) THEN
               v_errormsg := 'Fout tijdens verwerken van afdeling ['||recAfdeling1.PRS_AFDELING_NAAM||']';
             END IF;
             v_errorhint := v_errorhint||': ' || oracle_err_mes;

             fac.imp_writelog (p_import_key,
                           'E',
                           v_aanduiding||v_errormsg,
                           v_errorhint
                          );

             commit; -- tbv logging
         END;
       END LOOP;

       fac.imp_writelog (p_import_key,
                     'S',
                     'Afdeling: verwerkte regels zonder foutmelding: '||to_char(v_count_tot-v_count_error),
                     ''
                    );

       fac.imp_writelog (p_import_key,
                     'S',
                     'Afdeling: verwerkte regels met foutmelding: <b>'||to_char(v_count_error),
                     ''
                    );

   END;

   PROCEDURE anonimiseer_prs
   AS
        v_van   DATE;
        v_tot   DATE;
        anonymize_period   NUMBER := fac.safe_to_number (fac.getsetting ('anonymize_period'));

   BEGIN
        -- Als een persoon minimaal een jaar geleden verwijderd is anonimiseren we de gegevens
        SELECT datum_van
          INTO v_van
          FROM (SELECT MIN (p.prs_perslid_verwijder)     datum_van
                  FROM prs_perslid p
                 WHERE    p.prs_perslid_verwijder < (TRUNC (SYSDATE) - anonymize_period)
                       );

        SELECT TRUNC (SYSDATE - anonymize_period)
          INTO v_tot
          FROM DUAL;

        IF v_van IS NOT NULL 
        THEN
          ano.anonymizeprs (v_van, v_tot);
        END IF;

   END;

   PROCEDURE anonimiseer_mld
   AS
        v_van   DATE;
        v_tot   DATE;
        anonymize_period   NUMBER := fac.safe_to_number (fac.getsetting ('anonymize_period'));

   BEGIN
        SELECT datum_van
          INTO v_van
          FROM (SELECT MIN (TRUNC (m.mld_melding_einddatum))     datum_van
                  FROM mld_melding m
                 WHERE     m.mld_melding_status IN (1, 5, 6) -- melding is afgewezen, afgemeld of historisch
                       AND m.mld_melding_einddatum < (TRUNC (SYSDATE) - anonymize_period) -- de melding was gereed voor de anonimiseer periode
                       AND NOT EXISTS              --- nog niet geanonimiseerd
                               (SELECT ft.fac_tracking_key
                                  FROM fac_tracking ft
                                 WHERE     ft.fac_tracking_refkey =
                                           m.mld_melding_key
                                       AND ft.fac_srtnotificatie_key =
                                           (SELECT fac_srtnotificatie_key
                                              FROM fac_srtnotificatie
                                             WHERE fac_srtnotificatie_code =
                                                   'MLDANO')));

        SELECT TRUNC (SYSDATE - anonymize_period)
          INTO v_tot
          FROM DUAL;

        IF v_van IS NOT NULL 
        THEN
          ano.anonymizemld (v_van, v_tot);
        END IF;

   END;

   PROCEDURE anonimiseer_bezoeker
   AS
        v_van   DATE;
        v_tot   DATE;
        anonymize_period   NUMBER := fac.safe_to_number (fac.getsetting ('anonymize_period'));
   
   BEGIN
        SELECT datum_van
          INTO v_van
          FROM (SELECT MIN (TRUNC (a.bez_afspraak_datum))     datum_van
                  FROM bez_afspraak a
                 WHERE a.bez_afspraak_datum < (TRUNC (SYSDATE) - anonymize_period)  -- de afspraak vindt plaats voor de anonimiseer periode
                       AND NOT EXISTS              --- nog niet geanonimiseerd
                               (SELECT ft.fac_tracking_key
                                  FROM fac_tracking ft
                                 WHERE     ft.fac_tracking_refkey =
                                           a.bez_afspraak_key
                                       AND ft.fac_srtnotificatie_key =
                                           (SELECT fac_srtnotificatie_key
                                              FROM fac_srtnotificatie
                                             WHERE fac_srtnotificatie_code =
                                                   'BEZANO')));

        SELECT TRUNC (SYSDATE - anonymize_period)
          INTO v_tot
          FROM DUAL;

        IF v_van IS NOT NULL 
        THEN
          ano.anonymizebez (v_van, v_tot);
        END IF;
   
   END;

END;
/

-- Organisatie import
-- Aangeleverde structuur en mapping
-- Bedrijfsnaam --> wordt niet gebruikt
-- Divisiecode --> afdeling_naam niveau 1
-- Divisieomschrijving --> afdeling_omschrijving niveau 1
-- Bedrijfsonderdeelcode --> afdeling_naam niveau 2
-- Bedrijfsonderdeelomschrijving --> afdeling_omschrijving niveau 2
-- Afdelingscode --> afdeling_naam niveau 3
-- Afdelingsomschrijving --> afdeling_omschrijving niveau 3
-- Teamcode --> afdeling_naam niveau 4
-- Teamomschrijving --> afdeling_omschrijving niveau 4
-- Kostenplaatscode --> kostenplaats nummer
-- Kostenplaatsomschrijving --> kostenplaats omschrijving

CREATE OR REPLACE PROCEDURE cvgz_import_organisatie (p_import_key IN NUMBER)
AS
   c_fielddelimitor      VARCHAR2 (1) := ',';
   v_newline             VARCHAR2 (1000);                                              -- Input line
   v_errormsg            VARCHAR2 (1000);
   oracle_err_num        NUMBER;
   oracle_err_mes        VARCHAR2 (200);
   header_is_valid       NUMBER;
   v_count               NUMBER;
   v_ongeldig            NUMBER (1);
   v_mode                NUMBER (1);
   v_aanduiding          VARCHAR2 (200);
   v_all_null            BOOLEAN;
   v_count_error         NUMBER (10);
   v_count_tot           NUMBER (10);
   v_count_import        NUMBER (10);
   -- De importvelden:
   v_bedrijf_naam               VARCHAR2 (100);
   v_afdeling_naam              VARCHAR2 (100);
   v_afdeling_oms               VARCHAR2 (100);
   v_afdeling_naam_parent       VARCHAR2 (100);
   v_kostenplaats_omschrijving  VARCHAR2 (100);
   v_kostenplaats_nr            VARCHAR2 (100);
   i                             NUMBER;

   CURSOR c
   IS
        SELECT *
          FROM fac_imp_file
         WHERE fac_import_key = p_import_key
      ORDER BY fac_imp_file_index;
BEGIN
   v_bedrijf_naam := 'VGZ';
   v_count_error := 0;
   header_is_valid := 0;
   v_count_tot := 0;
   v_count_import := 0;

   FOR i IN 1..4 LOOP

      FOR rec IN c
      LOOP
         BEGIN

            v_errormsg := 'Fout FETCH te importeren rij';

            v_newline := rec.fac_imp_file_line;
            v_errormsg := 'Fout opvragen te importeren rij';
            v_aanduiding := '';

            -- Insert geformatteerde import record
            IF header_is_valid = 0
            THEN
               IF i = 1 AND rec.fac_imp_file_index = 1
               THEN
                  IF REPLACE(v_newline, '"') LIKE 'Bedrijfsnaam,Divisiecode,Divisieomschrijving,Bedrijfsonderdeelcode,Bedrijfsonderdeelomschrijving,Afdelingscode,Afdelingsomschrijving,Teamcode,Teamomschrijving,Kostenplaatscode,Kostenplaatsomschrijving%'
                  THEN
                     header_is_valid := 1;
                     DELETE FROM fac_imp_organisatie;
                  ELSE
                     fac.imp_writelog (p_import_key,
                                       'E',
                                       'Geen geldige header aangetroffen',
                                       v_newline);
                  END IF;
               END IF;
            ELSIF rec.fac_imp_file_index = 1
            THEN
               -- De header slaan we na de eerste keer over
               NULL;
            ELSE
               -- Lees alle veldwaarden
               fac.imp_getfield_nr (v_newline, c_fielddelimitor, (i*2), v_afdeling_naam);
               fac.imp_getfield_nr (v_newline, c_fielddelimitor, (i*2)+1, v_afdeling_oms);
               fac.imp_getfield_nr (v_newline, c_fielddelimitor, ((i-1)*2), v_afdeling_naam_parent);
               IF i = 4
               THEN
                  fac.imp_getfield_nr (v_newline, c_fielddelimitor, 11, v_kostenplaats_omschrijving);
                  fac.imp_getfield_nr (v_newline, c_fielddelimitor, 10, v_kostenplaats_nr);
               END IF;

               v_aanduiding := v_afdeling_naam || '|' || v_afdeling_oms || '|' || v_kostenplaats_nr || '| ';

               -- soms komt dezelfde afdeling op meerdere niveau's voor. Vandaar dat bij de insert een conditie staat
               IF v_afdeling_naam IS NOT NULL
               THEN

                  v_count_tot := v_count_tot + 1;
                  v_errormsg :=
                     'Fout bij toevoegen te impoteren bedrijf/afdeling [' || v_aanduiding || ']';

                  INSERT INTO fac_imp_organisatie (prs_bedrijf_naam,
                                                   prs_afdeling_omschrijving,
                                                   prs_afdeling_naam,
                                                   prs_afdeling_naam_parent,
                                                   prs_kostenplaats_nr)
                      SELECT v_bedrijf_naam,
                             v_afdeling_oms,
                             v_afdeling_naam,
                             v_afdeling_naam_parent,
                             v_kostenplaats_nr
                        FROM DUAL
                       WHERE NOT EXISTS
                             ( SELECT 1 FROM fac_imp_organisatie WHERE prs_afdeling_naam = v_afdeling_naam);

                  -- Het koppelen van de kostenplaats aan de organisatie gaan we doen bij de personen import
               END IF;
               IF i = 4 AND v_kostenplaats_nr IS NOT NULL AND v_kostenplaats_omschrijving IS NOT NULL
               THEN
                   INSERT INTO fac_imp_kpn (prs_kostenplaats_nr,
                                            prs_kostenplaats_omschrijving,
                                            prs_kostenplaats_module)
                      SELECT v_kostenplaats_nr,
                             v_kostenplaats_omschrijving,
                             'PRS'
                        FROM DUAL
                       WHERE NOT EXISTS
                             ( SELECT 1 FROM fac_imp_kpn WHERE prs_kostenplaats_nr = v_kostenplaats_nr);
               END IF;
            END IF;
         END;
      END LOOP;
   END LOOP;
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      oracle_err_num := SQLCODE;
      oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
      v_errormsg := 'error ' || oracle_err_num || '/' || oracle_err_mes;
      fac.imp_writelog (p_import_key,
                        'E',
                        v_errormsg,
                        '');
END cvgz_import_organisatie;
/


CREATE OR REPLACE PROCEDURE cvgz_update_organisatie (p_import_key IN NUMBER)
AS
   CURSOR c_del_kpn
   IS
      SELECT k.prs_kostenplaats_key, k.prs_kostenplaats_nr, k.prs_kostenplaats_omschrijving
        FROM prs_kostenplaats k
       WHERE prs_kostenplaats_module = 'PRS'
         AND prs_kostenplaats_eind IS NULL
         AND NOT EXISTS (SELECT 1 FROM fac_imp_kpn i WHERE i.prs_kostenplaats_nr = k.prs_kostenplaats_nr);

   CURSOR c_del_afd
   IS
      SELECT prs_afdeling_key, prs_afdeling_naam, prs_afdeling_omschrijving
        FROM prs_v_afdeling
       WHERE prs_afdeling_verwijder IS NULL
         AND prs_bedrijf_key = 1  -- Alleen afdelingen onder het bedrijf VGZ worden verwijderd.
         AND prs_afdeling_upper NOT IN
                 (SELECT prs_afdeling_naam FROM fac_imp_organisatie)
         AND prs_afdeling_upper NOT IN
                 (SELECT prs_afdeling_naam FROM fac_imp_perslid)
    ORDER BY niveau DESC;

   v_errormsg               VARCHAR (200);
   v_errorhint              VARCHAR (200);
   oracle_err_mes           VARCHAR2 (150);
   oracle_err_num           NUMBER;
   v_aanduiding             VARCHAR (100);
   v_bedrijf_key            NUMBER;
   v_kostenplaats_key       NUMBER;
   v_afdeling_key           NUMBER;
   v_count                  NUMBER;
   v_status                 VARCHAR2 (1);

BEGIN

   fac_update_kpn (p_import_key);
   CVGZ.update_organisatie (p_import_key);

   FOR rec IN c_del_kpn
   LOOP
      BEGIN
         v_aanduiding   := rec.prs_kostenplaats_nr || '-' || rec.prs_kostenplaats_omschrijving;
         v_errorhint   := 'Kijk we de kostenplaats kunnen verwijderen';

         UPDATE prs_kostenplaats
            SET prs_kostenplaats_eind = SYSDATE
          WHERE prs_kostenplaats_key = rec.prs_kostenplaats_key;

      EXCEPTION WHEN OTHERS
      THEN
         oracle_err_mes   := SUBSTR (SQLERRM, 1, 100);
         v_errormsg       := 'error ' || SQLCODE || '/' || oracle_err_mes;
         fac.imp_writelog (p_import_key,
                           'E',
                           v_errormsg,
                           'Het lukt niet om de kostenplaats te verwijderen:' || '-' || v_aanduiding
                           );
      END;
   END LOOP;

   FOR rec IN c_del_afd
   LOOP
      BEGIN
         v_aanduiding   := rec.prs_afdeling_naam || '-' || rec.prs_afdeling_omschrijving;
         v_errorhint   := 'Kijk we de afdeling kunnen verwijderen';

         UPDATE prs_afdeling
            SET prs_afdeling_verwijder = SYSDATE
          WHERE prs_afdeling_key = rec.prs_afdeling_key;

      EXCEPTION WHEN OTHERS
      THEN
         oracle_err_mes   := SUBSTR (SQLERRM, 1, 100);
         IF INSTR(oracle_err_mes, 'prs_m015') > 0
         THEN
            v_status := 'W';
            v_errormsg := 'Afdeling kan niet verwijderd worden. Er zijn nog personen aanwezig';
         ELSE
            v_errormsg       := 'error ' || SQLCODE || '/' || oracle_err_mes;
            v_status := 'E';
         END IF;
         fac.imp_writelog (p_import_key,
                           v_status,
                           v_errormsg,
                           'Het lukt niet om de afdeling te verwijderen:' || '-' || v_aanduiding
                           );
      END;
   END LOOP;

END cvgz_update_organisatie;
/


CREATE OR REPLACE PROCEDURE cvgz_import_perslid (p_import_key IN NUMBER)
AS
    v_seq_of_columns   VARCHAR (255);
    oracle_err_num     NUMBER;
    oracle_err_mes     VARCHAR2 (200);
    v_errormsg         VARCHAR2 (200);
    v_errorhint        VARCHAR2 (200);
BEGIN
  -- Import formaat:
  -- Bedrijfsnaam 1
  -- Loginnaam 2
  -- Weergavenaam 3
  -- Achternaam 4
  -- Tussenvoegsel 5
  -- Voorletters 6
  -- Voornaam 7
  -- Afdelingscode 8
  -- Kostenplaatscode 9
  -- Functie 10
  -- Email 11
  -- Mobiel 12


  -- Achternaam;1
  -- Department code;2
  -- E-mail;3
  -- Functie;4
  -- Gebruikersnaam;5
  -- Personeelsnummer;6
  -- Voornaam;7
  -- Weergavenaam;8
    v_seq_of_columns := '0;0;0;0;0;0;8;4;5;7;'
  -- v_alg_locatie_code 1
  -- v_alg_gebouw_code 2
  -- v_alg_verdieping_volgnr 3
  -- v_alg_ruimte_nr 4
  -- v_prs_werkplek_volgnr 5
  -- v_prs_werkplek_omschrijving 6
  -- v_prs_afdeling_naam 7
  -- v_prs_perslid_naam 8
  -- v_prs_perslid_tussenvoegsel 9
  -- v_prs_perslid_voornaam 10
                     || '6;0;0;0;0;0;0;11;0;0;'
  -- v_prs_perslid_voorletters 11
  -- v_prs_perslid_partner_naam 12
  -- v_prs_perslid_partner_tussenv 13
  -- v_prs_naamgebruik_code 14             0 (default) perslid_naam en perslid_tussenvoegsel worden gebruikt
  --                                       1 personen die de geboortenaam van de partner icm de eigen naam willen gebruiken
  --                                       2 personen die de geboortenaam van de partner willen gebruiken
  --                                       3 personen die de eigen naam icm de geboortenaam van de partner willen gebruiken
  -- v_prs_perslid_geslacht 15             Gebruik 'man', 'm' of 1 voor man en 'vrouw', 'v' of 0 voor vrouw case insensitive geen default
  -- v_prs_perslid_telefoonnr 16
  -- v_prs_perslid_mobiel 17
  -- v_prs_perslid_email 18
  -- v_prs_perslid_dienstverband 19
  -- v_prs_perslid_nr 20
                     || '10;2;0;0;0;0;3;9;12;0;'
  -- v_prs_srtperslid_omschrijving 21
  -- v_prs_perslid_oslogin 22
  -- v_prs_perslid_wachtwoord 23
  -- v_prs_perslid_titel 24
  -- v_prs_perslid_apikey 25
  -- v_dummy 26
  -- v_prs_kenmerk1 27
  -- v_prs_kenmerk2 28
  -- v_prs_kenmerk3 29
  -- v_prs_kenmerk4 30
                     || '0;0;0;0;0;0;0;0;0;0;'
                     || '0;0;0;0;0;0';

    --  Het aangeleverde bestand heeft een komma als scheidingsteken. Deze wordt hier maar even vervangen.
    UPDATE fac_imp_file
       SET fac_imp_file_line = cvgz.change_delimitor(fac_imp_file_line, ',')
     WHERE fac_import_key = p_import_key;

    prs.import_perslid(p_import_key, v_seq_of_columns, '"Bedrijfsnaam";"Loginnaam";"Weergavenaam";"Achternaam";"Tussenvoegsel";"Voorletters";"Voornaam";"Afdelingscode";"Kostenplaatscode";"Functie";"Email";"Mobiel"%');

    UPDATE fac_imp_perslid
       SET prs_perslid_oslogin = UPPER(prs_perslid_oslogin);
END cvgz_import_perslid;
/


CREATE OR REPLACE PROCEDURE cvgz_update_perslid (p_import_key IN NUMBER)
IS
   CURSOR c_del IS
      SELECT p.prs_perslid_key, p.prs_perslid_nr, pf.prs_perslid_naam_full, p.prs_perslid_oslogin
        FROM fac_imp_perslid i, prs_perslid p, prs_v_perslid_fullnames_all pf, prs_v_afdeling a
       WHERE p.prs_perslid_oslogin = i.prs_perslid_oslogin(+)
         AND p.prs_afdeling_key = a.prs_afdeling_key
         AND a.prs_bedrijf_key = 1
         AND INSTR(UPPER(p.prs_perslid_email), '@VGZ.NL') > 0
         AND pf.prs_perslid_key = p.prs_perslid_key
         AND (
               SUBSTR(p.prs_perslid_oslogin,1,1) <> '_'
               OR
               (p.prs_perslid_oslogin IS NULL AND p.prs_perslid_inactief IS NOT NULL)
             ) 
         AND i.prs_perslid_oslogin IS NULL
         AND p.prs_perslid_verwijder IS NULL;

   CURSOR c_tel IS
       SELECT p.prs_perslid_oslogin, pp.prs_perslid_telefoonnr, pp.prs_perslid_mobiel
         FROM fac_imp_perslid p, prs_perslid pp
          WHERE     p.prs_perslid_oslogin = pp.prs_perslid_oslogin
                 AND (   pp.prs_perslid_telefoonnr IS NOT NULL
                             OR pp.prs_perslid_mobiel IS NOT NULL);

   CURSOR c_man IS
       SELECT v.prs_perslid_key, v.prs_perslid_naam || ', ' || v.prs_perslid_voorletters || ' (' || v.prs_perslid_voornaam || ')' as prs_perslid_naam_full, v.prs_perslid_nr, v.prs_perslid_oslogin
          FROM fac_imp_perslid i, prs_v_aanwezigperslid v, prs_afdeling a
         WHERE i.prs_perslid_key = v.prs_perslid_key
          AND v.prs_afdeling_key = a.prs_afdeling_key
          AND a.prs_bedrijf_key = 1 -- VGZ
          AND INSTR(v.prs_perslid_naam,'INACTIEF') = 0 ;

    CURSOR c_org IS
        SELECT DISTINCT i.prs_afdeling_naam, prs_kenmerk2
          FROM fac_imp_perslid i
         WHERE NOT EXISTS
                   (SELECT 1
                      FROM prs_afdeling a
                     WHERE     i.prs_afdeling_naam || '-' || prs_kenmerk2 =
                               a.prs_afdeling_naam
                           AND a.prs_afdeling_verwijder IS NULL);

    v_count_mandaat           NUMBER (10);
    v_count_man_prs           NUMBER (10) := 0;
    v_afdeling_key            PRS_AFDELING.PRS_AFDELING_KEY%TYPE;
    v_afdeling_omschrijving   PRS_AFDELING.PRS_AFDELING_OMSCHRIJVING%TYPE;
    v_kostenplaats_key        PRS_KOSTENPLAATS.PRS_KOSTENPLAATS_KEY%TYPE;

    v_aanduiding         VARCHAR2 (100);
    v_errorhint          VARCHAR2 (1000);
    v_errormsg           VARCHAR2 (1000);
    v_count              NUMBER;
    oracle_err_num       NUMBER;
    oracle_err_mes       VARCHAR2 (200);
BEGIN

     SELECT COUNT(*)
       INTO v_count
       FROM fac_imp_perslid
      WHERE fac_import_key = p_import_key;

    IF v_count > 2000
    THEN
       FOR rec IN c_del LOOP
          BEGIN
             v_errorhint := 'Persoon verwijderen: ' || rec.prs_perslid_naam_full || '-' || rec.prs_perslid_nr || '-' || rec.prs_perslid_oslogin;
             prs.delete_perslid (p_import_key, rec.prs_perslid_key);
          END;
       END LOOP;
    END IF;

    FOR rec IN c_tel
    LOOP
    -- Telefoonnummers voert men zelf in Facilitor handmatig in en zitten dus niet in importbestand.
    -- We vullen daarom deze aan in de fac_imp_perslid
    v_errorhint := 'Telefoonnummer toevoegen aan import-tabel' || '-' || '-' || rec.prs_perslid_oslogin;

    IF rec.prs_perslid_telefoonnr IS NOT NULL
    THEN

       UPDATE fac_imp_perslid p
         SET p.prs_perslid_telefoonnr = rec.prs_perslid_telefoonnr
         WHERE p.prs_perslid_oslogin = rec.prs_perslid_oslogin;

    END IF;

    IF rec.prs_perslid_mobiel IS NOT NULL
    THEN

       UPDATE fac_imp_perslid p
         SET p.prs_perslid_mobiel = rec.prs_perslid_mobiel
         WHERE p.prs_perslid_oslogin = rec.prs_perslid_oslogin;

    END IF;

    END LOOP;

    FOR rec IN c_org
    LOOP
        BEGIN
            v_errorhint := 'Zoeken afdeling: ' || rec.prs_afdeling_naam;
            SELECT prs_afdeling_key, prs_afdeling_omschrijving
              INTO v_afdeling_key, v_afdeling_omschrijving
              FROM prs_afdeling
             WHERE     prs_afdeling_naam = rec.prs_afdeling_naam
                   AND prs_afdeling_verwijder IS NULL;

            v_errorhint := 'Zoeken kostenplaats: ' || rec.prs_kenmerk2;
            SELECT prs_kostenplaats_key
              INTO v_kostenplaats_key
              FROM prs_kostenplaats
             WHERE     prs_kostenplaats_nr = rec.prs_kenmerk2
                   AND prs_kostenplaats_verwijder IS NULL;

            v_errorhint := 'Toevoegen afdeling/kostenplaats: ' || rec.prs_afdeling_naam || '/' ||rec.prs_kenmerk2;
            INSERT INTO prs_afdeling (prs_afdeling_naam,
                                      prs_afdeling_omschrijving,
                                      prs_afdeling_parentkey,
                                      prs_kostenplaats_key)
                     VALUES (
                         rec.prs_afdeling_naam || '-' || rec.prs_kenmerk2,
                         rec.prs_kenmerk2 || ' - ' || v_afdeling_omschrijving,
                         v_afdeling_key,
                         v_kostenplaats_key);
        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);
        END;
    END LOOP;

    -- Pas de afdelingscode aan aan de nieuw aangemaakte structuur.
    UPDATE fac_imp_perslid
       SET prs_afdeling_naam = prs_afdeling_naam || '-' || prs_kenmerk2;


    -- Nu kunnen we de standaard facilitor-update doorvoeren
    prs.update_perslid (p_import_key, 'LOGIN', NULL);

    -- Gaan nu de mandetering toevoegen indien nodig
    FOR rec IN c_man
    LOOP

     v_errorhint := 'Mandatering toevoegen || ' || rec.prs_perslid_naam_full || '-' || rec.prs_perslid_nr || '-' || rec.prs_perslid_oslogin;

     -- Is er al mandatering voor die persoon vastgelegd
     SELECT COUNT(*)
       INTO v_count_mandaat
       FROM prs_perslidkostenplaats
       WHERE prs_perslid_key = rec.prs_perslid_key
        -- AND prs_kostenplaats_key IS NULL -- volledig mandaat
        ;
     IF v_count_mandaat = 0
     THEN

       INSERT INTO prs_perslidkostenplaats (prs_perslidkostenplaats_boeken,
                                     prs_perslidkostenplaats_inzage,
                                     prs_perslid_key,
                                     prs_kostenplaats_key)
               VALUES (1,
                       1,
                       rec.prs_perslid_key,
                       NULL);

       v_count_man_prs := v_count_man_prs + 1 ;

     END IF;

    END LOOP;

    fac.imp_writelog (
        p_import_key,
        'S',
           'Mandatering - doorgevoerd op '
        || TO_CHAR (v_count_man_prs)
        || ' personen'
        ,
        '');

    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);

END cvgz_update_perslid;
/

-- Via CUST07 om styling te kunnen opmaken
CREATE OR REPLACE VIEW cvgz_v_noti_leider_mldnew
(
   sender,
   receiver,
   text,
   code,
   key,
   xkey
)
AS
SELECT NULL,
       pk.prs_perslid_key,
          'Registratie '
       || sm.mld_stdmelding_omschrijving
       || ' van/door '
       || pm.prs_perslid_voornaam
       || ' '
       || pm.prs_perslid_naam
       || ' (nr. '
       || m.mld_melding_key
       || ')',
       'CUST07',
       m.mld_melding_key,
       NULL
  FROM mld_melding         m,
       prs_perslid         pm,
       mld_stdmelding      sm,
       mld_kenmerkmelding  km,
       mld_kenmerk         k,
       prs_perslid         pk,
       (  SELECT fac_tracking_refkey,
                 MAX (fac_tracking_datum) fac_tracking_datum
            FROM fac_tracking ft, fac_srtnotificatie fsn
           WHERE     fsn.fac_srtnotificatie_key = ft.fac_srtnotificatie_key
                 AND fac_srtnotificatie_code = 'MLDNEW'
        GROUP BY fac_tracking_refkey) ft
 WHERE     m.mld_stdmelding_key = sm.mld_stdmelding_key
       AND m.mld_melding_key = km.mld_melding_key
       AND km.mld_kenmerk_key = k.mld_kenmerk_key
       AND k.mld_srtkenmerk_key = 246
       AND m.prs_perslid_key_voor = pm.prs_perslid_key
       AND fac.safe_to_number (km.mld_kenmerkmelding_waarde) =
           pk.prs_perslid_key
       AND m.mld_melding_key = ft.fac_tracking_refkey
       AND ft.fac_tracking_datum >
           (SELECT   fac_notificatie_job_nextrun
                   - fac_notificatie_job_interval / 24
              FROM fac_notificatie_job
             WHERE fac_notificatie_job_view = 'CVGZ_V_NOTI_LEIDER_MLDNEW')
       AND ft.fac_tracking_datum <
           (SELECT fac_notificatie_job_nextrun
              FROM fac_notificatie_job
             WHERE fac_notificatie_job_view = 'CVGZ_V_NOTI_LEIDER_MLDNEW') ;

-- View om het mogelijk te maken om de badges voor de bezoekers in bulk te kunnen printen
CREATE OR REPLACE VIEW cvgz_v_rap_bezoek_bulk
AS
    SELECT bez_afspraak_datum,
           a.bez_afspraak_key,
           bez_afspraak_naam,
           bez_afspraak_bedrijf,
           TO_CHAR (bez_afspraak_datum, 'dd-mm-yyyy')
               bez_afspraak_datum_datum,
           bez_afspraak_gastheer,
           bez_afspraak_ruimte
      FROM bez_bezoekers b, bez_afspraak a
     WHERE b.bez_afspraak_key = a.bez_afspraak_key;


--de daily task draait elke ochtend om 5 uur.
CREATE OR REPLACE PROCEDURE cvgz_daily
AS
   v_errormsg          VARCHAR2 (1000);
   oracle_err_num      NUMBER;
   oracle_err_mes      VARCHAR2 (200);
BEGIN

    -- anonimiseer personen
    cvgz.anonimiseer_prs ();

    -- anonimiseer bezoekers
    cvgz.anonimiseer_bezoeker ();

    -- anonimiseer meldingen
    cvgz.anonimiseer_mld ();

EXCEPTION
   WHEN OTHERS
   THEN
      oracle_err_num := SQLCODE;
      oracle_err_mes := SUBSTR (SQLERRM, 1, 200);
      v_errormsg := 'ORACLE (error ' || oracle_err_num || '/' || oracle_err_mes || ')';
      fac.writelog ('DAILY', 'E', 'Proces afgebroken!', v_errormsg);
END cvgz_daily;
/

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

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



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

