--
-- $Id$
--
-- Script containing customer specific db-configuration for ASTE.
DEFINE thisfile = 'DPHM.SQL'
DEFINE dbuser   = '^DPHM'

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 dphm_v_rap_import_log
(
   fclt_f_applicatie,
   datum,
   fclt_f_status,
   omschrijving,
   hint
)
AS
   SELECT COALESCE (fac_import_app_code, imp_log_applicatie),
          TO_CHAR (imp_log_datum, 'dd-mm-yyyy hh24:mi'),
          imp_log_status,
          imp_log_omschrijving,
          imp_log_hint
     FROM imp_log il1, fac_import fi1, fac_import_app fia1
    WHERE il1.fac_import_key = fi1.fac_import_key(+)
          AND fi1.fac_import_app_key = fia1.fac_import_app_key(+)
          AND NOT EXISTS
                (SELECT 1
                   FROM imp_log il2, fac_import fi2
                  WHERE     il2.fac_import_key = fi2.fac_import_key
                        AND fi2.fac_import_app_key = fi1.fac_import_app_key
                        AND il2.fac_import_key > il1.fac_import_key);


CREATE OR REPLACE PROCEDURE dphm_import_prs (p_import_key IN NUMBER)
AS
   v_errormsg       VARCHAR2 (1000) := '-';
   v_errorhint      VARCHAR2 (1000) := '-';
   oracle_err_num   NUMBER;
   oracle_err_mes   VARCHAR2 (200);
BEGIN
   v_errorhint := 'Generieke import';

   -- Achteraan elke regel ';' toevoegen, dan zou er altijd een 6e ';' moeten zijn!
   UPDATE fac_imp_file i
      SET i.fac_imp_file_line = fac_imp_file_line || ';'
    WHERE fac_import_key = p_import_key;

  
   -- De sequence array staat beschreven in PRS_PAC.SRC bij de import_perslid-PROCEDURE.
   --  1            2       3               4           5           6               7       8
   --"First Name;Last Name;E-mail address;Windows ID;Organisation;OrganisationCode;Functie;Leidinggevende"
   prs.import_perslid (
      p_import_key,
         '0;0;0;0;0;0;6;2;0;1;'
      || '0;0;0;0;0;0;0;4;0;4;'
      || '7;3;0;0;0;0;8;0;0;0;'
      || '0;0;0;0;0;0;0;0;0;0;'
      || '0;0;0;0;0;0',	
		'%Voornaam;Achternaam;Mailadres;Personeelscode;Afdeling;Afdelingscode;Functie;Verant.LG;%');
      

   -- Klantspecifieke aanpassingen.

   COMMIT;
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.imp_writelog (p_import_key,
                        'E',
                        v_errormsg || v_errorhint,
                        'Inleesproces personen afgebroken!');
END dphm_import_prs;
/

CREATE OR REPLACE PROCEDURE dphm_update_prs (p_import_key IN NUMBER)
AS
   -- Maximaal percentage aan nieuwe medewerkers tov. actieve medewerkers met een e-mail
   c_max_delta_percentage   NUMBER (10) := 50;                         -- 50%!
   v_count_prs_import       NUMBER (10);     -- #actieve personen  na  import!
   v_count_prs_actual       NUMBER (10);     -- #actieve personen voor import!
   v_prs_perslid_key       NUMBER;

   -- Let op! Personen die niet meer in dienst zijn -> niet in fac_imp_perslid!
   -- Dubbele oslogins? Dan wordt iemand mogelijk niet verwijderd/INACTIEF!
   CURSOR c_del
   IS
        SELECT p.prs_perslid_key, p.prs_perslid_oslogin
          FROM prs_v_aanwezigperslid p
         WHERE (p.prs_perslid_oslogin IS NULL
                OR p.prs_perslid_oslogin NOT LIKE '\_%' ESCAPE '\') -- ' tbv. opmaak
               AND p.prs_perslid_oslogin2 IS NULL
               AND NOT EXISTS
                     (SELECT 1
                        FROM fac_imp_perslid i
                       WHERE UPPER (i.prs_perslid_oslogin) =
                                UPPER (p.prs_perslid_oslogin))
      ORDER BY 2, 1;
      
   CURSOR c_verantw
   IS
    SELECT pp.prs_perslid_key,
           p.prs_perslid_email,
           p.prs_kenmerk1
      FROM fac_imp_perslid p, 
           prs_perslid pp
     WHERE     p.fac_import_key=p_import_key
           AND UPPER(pp.prs_perslid_email)=UPPER(p.prs_perslid_email);
      
BEGIN
   -- Bepaal oslogin-match tussen FACILITOR en RECENT importbestand ofwel het aantal
   -- actieve personen na import.
   -- RECENT betekent niet meer dan een kwartiertje oud (14,4 minuten); dit voorkomt
   -- dat - als er geen nieuw importbestand is aangeleverd - telkens dezelfde fouten
   -- worden gerapporteerd (nl. op basis van een oud bestand in FAC_IMP_PERSLID).
   SELECT COUNT ( * )
     INTO v_count_prs_import
     FROM prs_v_aanwezigperslid p, fac_imp_perslid i
    WHERE p.prs_perslid_oslogin IS NOT NULL
          AND UPPER (p.prs_perslid_oslogin) = UPPER (i.prs_perslid_oslogin);
   SELECT COUNT (DISTINCT prs_perslid_oslogin)
     INTO v_count_prs_import
     FROM fac_imp_perslid;

   -- Bepaal huidig aantal actieve personen in FACILITOR.
   SELECT DECODE (COUNT ( * ), 0, 1, COUNT ( * ))
     INTO v_count_prs_actual
     FROM prs_v_aanwezigperslid
    WHERE prs_perslid_oslogin IS NOT NULL;

   /* ASTE#82955 Afsplitsing Astellas Meppel --> Delpharm het aantal zal veel minder zijn, dus deze controle tijdelijk eruit! */
   IF (TRUNC ( (v_count_prs_import / v_count_prs_actual) * 100) <
          (100 - c_max_delta_percentage))
   THEN
      fac.imp_writelog (
         p_import_key,
         'E',
         'Verschil tussen huidig aantal en te importeren aantal personen te groot; zie specificatie!',
            '- max. verschilpercentage = ['
         || TO_CHAR (c_max_delta_percentage)
         || '%]'
         || CHR (13)
         || CHR (10)
         || '- #personen/import = ['
         || TO_CHAR (v_count_prs_import)
         || ']'
         || CHR (13)
         || CHR (10)
         || '- #personen/huidig = ['
         || TO_CHAR (v_count_prs_actual)
         || ']');

      RETURN;
   END IF;
      
   -- Generieke update.
   -- Match op 'e-mail'
    prs.update_perslid (p_import_key, 'EMAIL', NULL);
    
    -- Gaan we nu de verantwoordelijke ophalen en toevoegen aan de prs_perslid
    FOR rec_verantw IN c_verantw
    LOOP
        BEGIN
           SELECT MIN(prs_perslid_key)       -- voorkomen dat we een fout krijgen als er perosnen zijn met hetzelfde email adres
             INTO v_prs_perslid_key
             FROM prs_perslid
            WHERE     UPPER(prs_perslid_email)=UPPER(rec_verantw.prs_kenmerk1)
                  AND prs_perslid_verwijder IS NULL
                  AND (prs_perslid_inactief IS NULL OR prs_perslid_inactief>SYSDATE);
        EXCEPTION
        WHEN NO_DATA_FOUND 
        THEN
          v_prs_perslid_key:=-1;
        END;
        
        IF v_prs_perslid_key>0
        THEN
                UPDATE prs_perslid 
                   SET prs_perslid_key_verantw=v_prs_perslid_key 
                 WHERE prs_perslid_key=rec_verantw.prs_perslid_key;
        END IF;
         
    END LOOP;
  
  
   -- Verwijder personen die niet meer in de import voorkomen.
   FOR rec IN c_del
  LOOP
     prs.delete_perslid (p_import_key, rec.prs_perslid_key);
   END LOOP;
END dphm_update_prs;
/

CREATE OR REPLACE VIEW dphm_v_rap_meldingenbeh
(
   call_number,
   mld_melding_datum,
   call_date,
   call_time,
   end_date,
   place,
   building,
   FLOOR,
   room_descr,
   room_nr,
   discipline_type,
   fclt_3d_discipline_key,
   discipline,
   call,
   description,
   progress,
   requestor,
   status,
   update_dat,
   update_name,
   update_desc
)
AS
   SELECT m.mld_melding_key,
          mld_melding_datum,
          TO_CHAR (mld_melding_datum, 'DD-MM-YYYY') mld_datum,
          TO_CHAR (mld_melding_datum, 'HH24:MI') mld_time,
          mld_melding_einddatum,
          aog.alg_plaatsaanduiding,
          aog.alg_gebouw_naam,
          aog.alg_verdieping_code,
          aog.alg_ruimte_omschrijving,
          aog.alg_ruimte_nr,
          ins_srtdiscipline_omschrijving,
          ins_discipline_key,
          ins_discipline_omschrijving,
          mld_stdmelding_omschrijving,
          mld_melding_omschrijving,
          mld_melding_opmerking,
          pf.prs_perslid_naam_full,
          mld_statuses_omschrijving,
          TO_CHAR (behand.datum, 'DD-MM-YYYY HH24:MI') dat,
          behand.naam,
          COALESCE (behand.omschr, behand.def_track)
     FROM mld_melding m,
          mld_stdmelding std,
          mld_discipline d,
          ins_srtdiscipline sd,
          mld_statuses st,
          prs_v_perslid_fullnames_all pf,
          alg_v_allonrgoed_gegevens aog,
          (SELECT n.mld_melding_note_aanmaak datum,
                  n.mld_melding_note_key,
                  pf.prs_perslid_naam_friendly naam,
                  n.mld_melding_note_omschrijving omschr,
                  NULL def_track,
                  mld_melding_key
             FROM mld_melding_note n, prs_v_perslid_fullnames_all pf
            WHERE n.prs_perslid_key = pf.prs_perslid_key(+)
           UNION
           SELECT tr.fac_tracking_datum,
                  tr.fac_tracking_key,
                  pf.prs_perslid_naam_friendly,
                  tr.fac_tracking_oms,
                  DECODE (str.fac_srtnotificatie_code,
                          'MLDNEW', lcl.l ('lcl_mld_is_mldnew'),
                          'MLDING', lcl.l ('lcl_mld_is_mlding'),
                          'MLDDOO', lcl.l ('lcl_mld_is_mlddoo'),
                          'MLDDFWD', lcl.l ('lcl_mld_is_mldfwd'),
                          'MLDDBWD', lcl.l ('lcl_mld_is_mldbwd'),
                          'MLDUPD', lcl.l ('lcl_mld_is_mldupd'),
                          'MLDACP', lcl.l ('lcl_mld_is_mldacp'),
                          'MLDBEH', lcl.l ('lcl_mld_is_mldbeh'),
                          'MLDREJ', lcl.l ('lcl_mld_is_mldrej'),
                          'MLDAFM', lcl.l ('lcl_mld_is_mldafm'),
                          'MLDer', lcl.l ('lcl_mld_is_mldver'),
                          'MLDNOT', lcl.l ('lcl_mld_is_mldnot'))
                     def_track,
                  fac_tracking_refkey
             FROM fac_tracking tr,
                  fac_srtnotificatie str,
                  prs_v_perslid_fullnames_all pf
            WHERE     tr.fac_srtnotificatie_key = str.fac_srtnotificatie_key
                  AND tr.prs_perslid_key = pf.prs_perslid_key(+)
                  AND str.fac_srtnotificatie_code <> 'MLDNOT'
                  AND str.fac_srtnotificatie_xmlnode IN ('melding')) behand
    WHERE     m.mld_stdmelding_key = std.mld_stdmelding_key
          AND d.ins_discipline_key = std.mld_ins_discipline_key
          AND d.ins_srtdiscipline_key = sd.ins_srtdiscipline_key
          AND m.prs_perslid_key = pf.prs_perslid_key
          AND aog.alg_onroerendgoed_keys = m.mld_alg_onroerendgoed_keys
          AND behand.mld_melding_key(+) = m.mld_melding_key
          AND st.mld_statuses_key = m.mld_melding_status
          AND fac_activiteit_key IS NULL;

CREATE OR REPLACE VIEW dphm_v_rap_meldingenbeh_last90
AS
   SELECT *
     FROM dphm_v_rap_meldingenbeh
    WHERE mld_melding_datum > SYSDATE - 93;

CREATE OR REPLACE VIEW dphm_v_noti_res_gastheer
(
    CODE,
    SENDER,
    RECEIVER,
    TEXT,
    KEY,
    XKEY
)
AS
    SELECT DISTINCT
           'CUST01',
           NULL,
           rrr.res_rsv_ruimte_host_key,
           'Uw reservering '
                   || rrr.res_reservering_key
                   || '/'
                   || MIN(rrr.res_rsv_ruimte_volgnr)
                   || ' ('
                   || rrr.res_rsv_ruimte_omschrijving
                   || ') is gewijzigd.',
           rrr.res_reservering_key,
           MIN(rrr.res_rsv_ruimte_key)
      FROM fac_tracking t, fac_srtnotificatie sn, res_rsv_ruimte rrr
     WHERE     t.fac_srtnotificatie_key = sn.fac_srtnotificatie_key
           -- AND sn.fac_srtnotificatie_code IN ('RESNEW', 'RESUPD') ---DPHM#76774 Aanpassen notificatie reserveringen, voor RESUPDATE vallen we terug op de default
           AND sn.fac_srtnotificatie_code = 'RESNEW' 
           AND fac_tracking_refkey = rrr.res_rsv_ruimte_key
           AND t.fac_tracking_datum BETWEEN (SELECT   fac_notificatie_job_nextrun
                                                    -   fac_notificatie_job_interval
                                                      / 24
                                               FROM fac_notificatie_job
                                              WHERE fac_notificatie_job_view =
                                                    'DPHM_V_NOTI_RES_GASTHEER')
                                        AND (SELECT fac_notificatie_job_nextrun
                                               FROM fac_notificatie_job
                                              WHERE fac_notificatie_job_view =
                                                    'DPHM_V_NOTI_RES_GASTHEER')
     GROUP BY rrr.res_rsv_ruimte_host_key,
              rrr.res_reservering_key,
              rrr.res_rsv_ruimte_omschrijving,
              sn.fac_srtnotificatie_code;

CREATE OR REPLACE VIEW DPHM_V_RAP_UIT_IN_OBJ
(
    DATUM,
    PERSLID_KEY,
    PERSLID_NAAM,
    OBJECTSOORT,
    OBJECT_ID,
    OBJECT_OMSCHRIJVING,
    SELECTIE,
    TEKST
)
AS
    SELECT TO_CHAR (SYSDATE, 'dd-mm-yyyy'),
           p.prs_perslid_key,
           p.prs_perslid_naam_friendly,
           sd.ins_srtdeel_omschrijving,
           d.ins_deel_omschrijving,
           d.ins_deel_opmerking,
           s.selectie,
           s.tekst
      FROM ins_deel                 d,
           prs_v_perslid_fullnames  p,
           ins_srtdeel              sd,
           (SELECT DECODE (unpivot_row,  1, 'uitgifte',  2, 'inname')
                       AS selectie,
                   DECODE (unpivot_row,  1, 'ontvangen',  2, 'ingeleverd')
                       AS tekst
              FROM DUAL,
                   (    SELECT LEVEL     AS unpivot_row
                          FROM DUAL
                    CONNECT BY LEVEL <= 2)) s
     WHERE     d.ins_alg_ruimte_type = 'P'
           AND d.ins_alg_ruimte_key = p.prs_perslid_key
           AND d.ins_srtdeel_key = sd.ins_srtdeel_key
    UNION ALL
    SELECT TO_CHAR (SYSDATE, 'dd-mm-yyyy'),
           p.prs_perslid_key,
           p.prs_perslid_naam_friendly,
           sd.ins_srtdeel_omschrijving,
           d.ins_deel_omschrijving,
           d.ins_deel_opmerking,
           s.selectie,
           s.tekst
      FROM ins_deel                 d,
           prs_v_perslid_fullnames  p,
           ins_srtdeel              sd,
           (SELECT DECODE (unpivot_row,  1, 'uitgifte',  2, 'inname')
                       AS selectie,
                   DECODE (unpivot_row,  1, 'ontvangen',  2, 'ingeleverd')
                       AS tekst
              FROM DUAL,
                   (    SELECT LEVEL     AS unpivot_row
                          FROM DUAL
                    CONNECT BY LEVEL <= 2)) s
     WHERE     d.ins_alg_ruimte_type_org = 'P'
           AND d.ins_alg_ruimte_key_org = p.prs_perslid_key
           AND d.ins_srtdeel_key = sd.ins_srtdeel_key;
           
------ 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