-- Customer specific once-script UWVA86.
--
-- (c) 2014 SG|facilitor bv
SET ECHO ON
SPOOL xuwva86.lst
SET DEFINE OFF

-- UWVA#27483 E-mail adres volgens schrijfwijzer!
/* Formatted on 17-2-2014 11:48:12 (QP5 v5.136.908.31019) */
CREATE OR REPLACE VIEW uwva_v_email_schrijfwijzer
(
   prs_key,
   fac_id,
   imp_id,
   imp_empnum,
   imp_nickname,
   imp_lastname,
   imp_spousename,
   fac_email,
   imp_email,
   sw_email
)
AS
   SELECT DISTINCT
          p.prs_perslid_key,
          kl.prs_kenmerklink_waarde,
          ip.id,
          ip.empnum,
          ip.nickname,
          ip.lastname,
          ip.spousename,
          p.prs_perslid_email,
          ip.email,
          DECODE (
             INSTR (COALESCE (p.prs_perslid_email, ip.email), '@'),
             NULL,                         -- geen email bekend -> ongewijzigd
             COALESCE (p.prs_perslid_email, ip.email),
             0,                            -- geen '@' in email -> ongewijzigd
             COALESCE (p.prs_perslid_email, ip.email),
             CASE                                         -- email met een '@'
                WHEN INSTR (COALESCE (p.prs_perslid_email, ip.email) || '.', '.') <
                        INSTR (COALESCE (p.prs_perslid_email, ip.email), '@')
                THEN                         -- . voor @ (dus meeste gevallen)
                   REPLACE (
                      REPLACE (
                         REPLACE (
                            REPLACE (
                               INITCAP(SUBSTR (LOWER(COALESCE (p.prs_perslid_email, ip.email)),
                                          1,
                                          INSTR (COALESCE (p.prs_perslid_email, ip.email), '.', -1, 2) - 1))
                               || SUBSTR (LOWER(COALESCE (p.prs_perslid_email, ip.email)),
                                     INSTR (COALESCE (p.prs_perslid_email, ip.email), '.', -1, 2)),
                               INITCAP(REPLACE (COALESCE (nickname, vnaam), ' ', '') || '.'),
                               REPLACE (INITCAP (COALESCE (nickname, vnaam)), ' ', '') || '.'),
                            LOWER(SUBSTR (TRIM (COALESCE (lastname, anaam)),
                                     1,
                                     INSTR (TRIM (COALESCE (lastname, anaam)), ' ') - 1)),
                            SUBSTR (INITCAP (TRIM (COALESCE (lastname, anaam))),
                               1,
                               INSTR (TRIM (COALESCE (lastname, anaam)), ' ') - 1)),
                         LOWER(SUBSTR (TRIM (COALESCE (lastname, anaam)),
                                  INSTR (TRIM (COALESCE (lastname, anaam)), ' ', -1) + 1)),
                         SUBSTR (INITCAP (TRIM (COALESCE (lastname, anaam))),
                            INSTR (TRIM (COALESCE (lastname, anaam)), ' ', -1) + 1)),
                      LOWER (REPLACE (spousename, ' ', '')),
                      REPLACE (INITCAP (spousename), ' ', ''))
                ELSE -- geen . voor @ (bkwi.nl/uwv.local; <30!) -> ongewijzigd
                   COALESCE (p.prs_perslid_email, ip.email)
             END)
             email_schrijfwijzer
     FROM (SELECT prs_perslid_key,
                  REPLACE (prs_perslid_email, '$', '@') prs_perslid_email,
                  prs_perslid_voornaam vnaam,
                  prs_perslid_naam anaam
             FROM prs_v_aanwezigperslid) p
          LEFT JOIN prs_v_aanwezigkenmerklink kl
             ON p.prs_perslid_key = kl.prs_link_key
                AND kl.prs_kenmerk_key = 1232                        -- ABS-ID
          FULL JOIN (SELECT *
                       FROM uwva_imp_abs
                      WHERE SUBSTR (empnum, 1, 1) IN ('C', 'I', 'E', 'F', 'D', 'K')) ip
             ON kl.prs_kenmerklink_waarde = ip.id;

COMMIT;

/* Formatted on 17-2-2014 13:05:55 (QP5 v5.136.908.31019) */
DECLARE
   c_commitbuffer   NUMBER := 1000;
   oracle_err_num   NUMBER;
   oracle_err_mes   VARCHAR2 (255);
   v_errormsg       VARCHAR2 (1024);
   v_errorhint      VARCHAR2 (1024);
   v_buffercount    NUMBER;
   v_updates        NUMBER;
BEGIN
   v_buffercount := 0;
   v_updates := 0;

   -- Loop over alle lowercase e-mail adressen in FACILITOR en formatteer die
   -- volgens schrijfwijzer!
   FOR rc IN (SELECT prs_key, fac_email, sw_email
                FROM uwva_v_email_schrijfwijzer
               WHERE fac_email = LOWER (fac_email)) -- Lowercase in FACILITOR!
   LOOP
      BEGIN
         v_errormsg := 'Fout bij bijwerken email';

         UPDATE prs_perslid
            SET prs_perslid_email = rc.sw_email
          WHERE prs_perslid_key = rc.prs_key;

         v_updates := v_updates + 1;
         v_buffercount := v_buffercount + 1;

         IF (v_buffercount >= c_commitbuffer)
         THEN
            COMMIT;
            v_buffercount := 0;
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            oracle_err_num := SQLCODE;
            oracle_err_mes := SUBSTR (SQLERRM, 1, 200);
            v_errorhint := v_errormsg;
            v_errormsg :=
                  '['
               || TO_CHAR (rc.prs_key)
               || '] '
               || '(ORACLE error '
               || oracle_err_num
               || '/'
               || oracle_err_mes
               || ')';
            DBMS_OUTPUT.PUT_LINE (
               'Error: ' || v_errormsg || '; Hint: ' || v_errorhint);
      END;
   END LOOP;

   DBMS_OUTPUT.PUT_LINE ('#emails updated: ' || TO_CHAR (v_updates));
END;
/

COMMIT;

--UWVA#28572: MLD: rapport op behandelgroepen, vakgroeptype, vakgroep en mld (24 = WEB_MLDBOF)
CREATE OR REPLACE VIEW uwva_v_rap_mld_behandelgroep AS SELECT NULL x FROM DUAL;
INSERT INTO FAC_USRRAP (fac_usrrap_omschrijving,
                        fac_usrrap_view_name,
                        fac_usrrap_in_huidige_locatie,
                        fac_usrrap_template,
                        fac_usrrap_macro,
                        fac_usrrap_vraagbegindatum,
                        fac_usrrap_vraageinddatum,
                        fac_usrrap_functie,
                        fac_usrrap_info,
                        fac_functie_key,
                        fac_usrrap_autorefresh)
    VALUES ('MLD Behandelgroepen',
            'uwva_v_rap_mld_behandelgroep',
            NULL,
            NULL,
            NULL,
            0,
            0,
            0,
            'Overzicht van vakgroepen en meldingen per behandelgroep',
            24,
            0);

COMMIT;

-- UWVA#28625: Voortzetting KTO na pilot!
UPDATE ins_tab_discipline td
   SET ins_discipline_ktopercentage = 20, td.ins_discipline_ktodrempel = 0
 WHERE     ins_discipline_verwijder IS NULL
       AND ins_discipline_module = 'MLD'
       AND ins_srtdiscipline_key IN (1, 2, 3, 9, 10)
       AND (COALESCE (ins_discipline_ktopercentage, -1) != 20
            OR COALESCE (ins_discipline_ktodrempel, -1) != 0);

UPDATE mld_stdmelding sm
   SET sm.mld_stdmelding_kto = 1
 WHERE sm.mld_stdmelding_verwijder IS NULL
       AND COALESCE (sm.mld_stdmelding_vervaldatum, SYSDATE) >= TRUNC (SYSDATE)
       --AND sm.mld_stdmelding_notfrontend = 0
       AND COALESCE (sm.mld_stdmelding_kto, -1) != 1
       AND EXISTS
             (SELECT 1
                FROM ins_tab_discipline
               WHERE     ins_discipline_verwijder IS NULL
                     AND ins_discipline_module = 'MLD'
                     AND ins_srtdiscipline_key IN (1, 2, 3, 9, 10)
                     AND ins_discipline_ktopercentage = 20
                     AND ins_discipline_ktodrempel = 0
                     AND ins_discipline_key = sm.mld_ins_discipline_key);

COMMIT;

-- UWVA#28494: FDO CNT noti reminder naar LFM
INSERT INTO fac_notificatie_job (fac_notificatie_job_view,
                                 fac_notificatie_job_oms,
                                 fac_notificatie_job_mode,
                                 fac_notificatie_job_interval,
                                 fac_notificatie_job_nextrun,
                                 fac_notificatie_job_flags)
  VALUES   ('UWVA_V_NOTI_CNT_LFM_REMINDER',
            'Jaarlijkse LFM reminder tbv FDO',
            3,
            1,
            null,
            0); 
            
INSERT INTO fac_srtnotificatie (fac_srtnotificatie_code,
                                fac_srtnotificatie_mode,
                                fac_srtnotificatie_oms,
                                fac_srtnotificatie_xmlnode,
                                fac_srtnotificatie_groep,
                                fac_srtnotificatie_usermode,
                                fac_srtnotificatie_delay)
  VALUES   ('CUST23',
            2,
            'LFM FDO reminder',
            'contract',
            0,
            0,
            0); 
            
COMMIT;                        

BEGIN adm.systrackscriptId('$Id$'); END;
/
SPOOL OFF

@@uwva_prs.sql
@@uwva_fin.sql
--@@uwva_hv.sql
@@uwva_misc.sql

BEGIN fac.registercustversion('UWVA', 86); END;
/
COMMIT;
