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

DEFINE thisfile = 'TEAR.SQL'
DEFINE dbuser   = '^TEAR'
DEFINE custid   = 'TEAR'

SET ECHO ON
SET DEFINE ON
COLUMN fcltlogfile NEW_VALUE fcltlogfile NOPRINT;
WHENEVER SQLERROR EXIT;

SELECT adm.scriptspoolfile ('&dbuser', '&thisfile') AS fcltlogfile FROM DUAL;

WHENEVER SQLERROR CONTINUE;
SPOOL &fcltlogfile
SET DEFINE OFF

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

--- Notificatie naar objectbeheerders voor periodieke taken die de komende maand gedaan moeten worden
CREATE OR REPLACE VIEW TEAR_V_NOTI_OBJBEH
(
   METHODE,
   TO_KEY,
   TO_OMSCHR,
   REF_KEY,
   REF_OMSCHR,
   KPN_KEY,
   AANTAL,
   BETREFT
)
AS
WITH taken
        AS (SELECT fac.nextcyclusdatedeel (d.ins_deel_key,
                                           d.ins_srtcontrole_key,
                                           1)
                      volgende_cyclus,
                   p.ins_deelsrtcontrole_plandatum datum_gepland,
                   COALESCE (
                      p.ins_deelsrtcontrole_plandatum,
                      fac.nextcyclusdatedeel (d.ins_deel_key,
                                              d.ins_srtcontrole_key,
                                              1))
                      volgende_ctr,
                   d.*                
              FROM (SELECT sc.ins_srtcontrole_key,
                           d.ins_deel_key,
                           d.ins_deel_omschrijving,
                           sc.ins_srtcontrole_periode,
                           sd.ins_srtdeel_omschrijving,
                           a.prs_kostenplaats_key,
                           pfa.prs_perslid_key,
                           pfa.prs_perslid_naam_full
                      FROM ins_srtcontrole sc,
                           ins_discipline di,
                           ins_v_aanwezigsrtgroep sg,
                           ins_v_aanwezigsrtdeel sd,
                           ins_v_aanwezigdeel d,
                           prs_perslid p,
                           prs_afdeling a,
                           prs_v_perslid_fullnames_all pfa
                     WHERE     sd.ins_srtdeel_key = d.ins_srtdeel_key
                           AND sg.ins_srtgroep_key = sd.ins_srtgroep_key
                           AND di.ins_discipline_key = sg.ins_discipline_key
                           AND COALESCE (d.ins_deel_vervaldatum, SYSDATE + 1) >
                                 SYSDATE
                           AND p.prs_perslid_key = d.prs_perslid_key_beh
                           AND a.prs_afdeling_key(+) = p.prs_afdeling_key
                           AND pfa.prs_perslid_key = d.prs_perslid_key_beh
                           AND sc.ins_srtinstallatie_key =
                                 DECODE (ins_srtcontrole_niveau,
                                         'D', di.ins_discipline_key,
                                         'G', sg.ins_srtgroep_key,
                                         'S', sd.ins_srtdeel_key)) d,
                   ins_srtcontroledl_xcp xcp,
                   (SELECT *
                      FROM ins_v_udr_deelinspect ins
                     WHERE deelsrtcontrole_status = 'Gepland'
                           AND ins_deelsrtcontrole_plandatum IS NOT NULL) p
             WHERE     xcp.ins_deel_key (+) = d.ins_deel_key 
                   AND xcp.ins_srtcontrole_key (+) = d.ins_srtcontrole_key 
                   AND p.deel_key(+) = d.ins_deel_key
                   AND p.ins_srtcontrole_key(+) = d.ins_srtcontrole_key
                   AND COALESCE (xcp.ins_srtcontroledl_xcp_periode,
                                 d.ins_srtcontrole_periode) > 0
                   -- De eerste 5 dagen van volgende maand ook vast meepakken, dat scheelt verrassingen
                   AND COALESCE (
                         p.ins_deelsrtcontrole_plandatum,
                         fac.nextcyclusdatedeel (d.ins_deel_key,
                                                 d.ins_srtcontrole_key,
                                                 1)) <
                         ADD_MONTHS (SYSDATE, 1) + 5)
  SELECT 'mail' methode,
         prs_perslid_key to_key,
         prs_perslid_naam_full to_omschr,
         NULL ref_key,
         NULL ref_omschr,
         NULL kpn_key,
         COUNT ( * ) aantal,
         LISTAGG (ins_deel_omschrijving, ', ')
            WITHIN GROUP (ORDER BY ins_deel_omschrijving)
            AS betreft
    FROM taken
GROUP BY prs_perslid_key, prs_perslid_naam_full;

--- deze notificatie moet aan het begin van elke maand aangemaakt / verstuurd worden
CREATE OR REPLACE PROCEDURE tear_export_task_monthly (
   p_applname   IN VARCHAR2,
   p_applrun    IN VARCHAR2,
   p_filedir    IN VARCHAR2,
   p_filename   IN VARCHAR2)
AS
   oracle_err_num   NUMBER;
   oracle_err_mes   VARCHAR2 (200);
   v_errorhint      VARCHAR2 (100);

   v_aanduiding     VARCHAR2 (100);
   v_melding_key    NUMBER (10);

   CURSOR c
   IS
      SELECT * FROM tear_v_noti_objbeh;
BEGIN
   v_errorhint := 'init';

   DELETE imp_log
    WHERE imp_log_applicatie = p_applname;

   FOR rec IN c
   LOOP
      BEGIN
         IF (rec.methode = 'mail')
         THEN
            v_aanduiding := rec.ref_omschr || ' (' || rec.methode || ') ';
         ELSE
            v_aanduiding :=
                  rec.to_omschr
               || '/'
               || rec.ref_omschr
               || ' ('
               || rec.methode
               || ') ';
         END IF;

         v_errorhint := 'Add mail';

         INSERT INTO fac_notificatie (fac_srtnotificatie_key,
                                      fac_notificatie_status,
                                      prs_perslid_key_receiver,
                                      fac_notificatie_receiver_email,
                                      fac_notificatie_oms,
                                      fac_notificatie_refkey,
                                      prs_perslid_key_sender,
                                      fac_notificatie_prioriteit,
                                      fac_notificatie_lang)
            SELECT fac_srtnotificatie_key,
                   2,
                   rec.to_key,
                   prs_perslid_email,
                   rec.aantal || ' uit te voeren taken deze maand',
                   rec.to_key,
                   3,
                   2,
                   'NL'
              FROM fac_srtnotificatie, prs_perslid
             WHERE fac_srtnotificatie_code = 'CUST01'
                   AND prs_perslid_key = rec.to_key;

         fac.writelog (p_applname,
                       'I',
                       'Mail naar ' || rec.to_omschr,
                       rec.aantal || ' objecten');
      EXCEPTION
         WHEN OTHERS
         THEN
            oracle_err_num := SQLCODE;
            oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
            fac.writelog (
               p_applname,
               'E',
                  'Error '
               || v_aanduiding
               || oracle_err_num
               || '/'
               || oracle_err_mes,
               v_errorhint);
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      oracle_err_num := SQLCODE;
      oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
      fac.writelog (p_applname,
                    'E',
                    'Error ' || oracle_err_num || '/' || oracle_err_mes,
                    v_errorhint);
END tear_export_task_monthly;
/
COMMIT;

BEGIN
   DBMS_UTILITY.COMPILE_SCHEMA (USER, FALSE);
END;
/

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