--
-- $Id$
--
-- Customer specific once-script WEHK#39464_CNT.
DEFINE thisfile = 'WEHK#39464_CNT.sql'
DEFINE dbuser   = '^WEHK'
DEFINE custid   = 'WEHK'

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

-- Opschuiven naar kenmerkvelden naar zichtbaar met CNTUSE (>100)!
UPDATE cnt_kenmerk
   SET cnt_kenmerk_volgnummer = cnt_kenmerk_volgnummer + 100
 WHERE cnt_kenmerk_verwijder IS NULL
       AND cnt_kenmerk_key BETWEEN 1018 AND 1230;

COMMIT;

-- Kopieren Omschrijving- en Opmerking-veld van oude naar nieuwe contract!
UPDATE cnt_contract x
   SET x.cnt_contract_document =
          (SELECT c.cnt_contract_document
             FROM cnt_kenmerkcontract kc, cnt_kenmerk k, cnt_v_aanwezigcontract c
            WHERE     kc.cnt_kenmerkcontract_verwijder IS NULL
                  AND kc.cnt_kenmerk_key = k.cnt_kenmerk_key
                  AND k.cnt_srtkenmerk_key = 361
                  AND SUBSTR (kc.cnt_kenmerkcontract_waarde, 1, INSTR (kc.cnt_kenmerkcontract_waarde, '/') - 1) = TO_CHAR (c.cnt_contract_key)
                  AND kc.cnt_contract_key = x.cnt_contract_key),
       x.cnt_contract_opmerking =
          (SELECT c.cnt_contract_opmerking
             FROM cnt_kenmerkcontract kc, cnt_kenmerk k, cnt_v_aanwezigcontract c
            WHERE     kc.cnt_kenmerkcontract_verwijder IS NULL
                  AND kc.cnt_kenmerk_key = k.cnt_kenmerk_key
                  AND k.cnt_srtkenmerk_key = 361
                  AND SUBSTR (kc.cnt_kenmerkcontract_waarde, 1, INSTR (kc.cnt_kenmerkcontract_waarde, '/') - 1) = TO_CHAR (c.cnt_contract_key)
                  AND kc.cnt_contract_key = x.cnt_contract_key)
 WHERE EXISTS
          (SELECT 1
             FROM cnt_kenmerkcontract kc, cnt_kenmerk k
            WHERE     kc.cnt_kenmerkcontract_verwijder IS NULL
                  AND kc.cnt_kenmerk_key = k.cnt_kenmerk_key
                  AND k.cnt_srtkenmerk_key = 361
                  AND kc.cnt_contract_key = x.cnt_contract_key);

COMMIT;

-- Converteren/terugbrengen van Rappel- en Opzegtermijn naar kleinere set!
UPDATE cnt_contract x
   SET x.cnt_contract_rappeltermijn =
          (SELECT DECODE (c.cnt_contract_rappeltermijn, 46, 2, t_new.cnt_termijn_key) -- 7 Dag(en)=> 1 Week
             FROM cnt_kenmerkcontract kc, cnt_kenmerk k, cnt_v_aanwezigcontract c, cnt_termijn t_old, cnt_termijn t_new
            WHERE     kc.cnt_kenmerkcontract_verwijder IS NULL
                  AND kc.cnt_kenmerk_key = k.cnt_kenmerk_key
                  AND k.cnt_srtkenmerk_key = 361
                  AND kc.cnt_contract_key = c.cnt_contract_key
                  AND c.cnt_contract_rappeltermijn = t_old.cnt_termijn_key
                  AND t_old.cnt_termijn_type = 'D'
                  AND ROUND (t_old.cnt_termijn_aantal / 30) = t_new.cnt_termijn_aantal
                  AND t_new.cnt_termijn_type = 'M'
                  AND c.cnt_contract_key = x.cnt_contract_key)
 WHERE EXISTS
          (SELECT 1
             FROM cnt_kenmerkcontract kc, cnt_kenmerk k, cnt_v_aanwezigcontract c, cnt_termijn t_old, cnt_termijn t_new
            WHERE     kc.cnt_kenmerkcontract_verwijder IS NULL
                  AND kc.cnt_kenmerk_key = k.cnt_kenmerk_key
                  AND k.cnt_srtkenmerk_key = 361
                  AND kc.cnt_contract_key = c.cnt_contract_key
                  AND c.cnt_contract_rappeltermijn = t_old.cnt_termijn_key
                  AND t_old.cnt_termijn_type = 'D'
                  AND ROUND (t_old.cnt_termijn_aantal / 30) = t_new.cnt_termijn_aantal
                  AND t_new.cnt_termijn_type = 'M'
                  AND c.cnt_contract_key = x.cnt_contract_key);

COMMIT;

-- Converteren/mappen van Contractafdeling van oude naar nieuwe structuur!
UPDATE cnt_contract x
   SET x.cnt_contract_opzegtermijn =
          (SELECT DECODE (c.cnt_contract_opzegtermijn, 46, 2, t_new.cnt_termijn_key) -- 7 Dag(en)=> 1 Week
             FROM cnt_kenmerkcontract kc, cnt_kenmerk k, cnt_v_aanwezigcontract c, cnt_termijn t_old, cnt_termijn t_new
            WHERE     kc.cnt_kenmerkcontract_verwijder IS NULL
                  AND kc.cnt_kenmerk_key = k.cnt_kenmerk_key
                  AND k.cnt_srtkenmerk_key = 361
                  AND kc.cnt_contract_key = c.cnt_contract_key
                  AND c.cnt_contract_opzegtermijn = t_old.cnt_termijn_key
                  AND t_old.cnt_termijn_type = 'D'
                  AND ROUND (t_old.cnt_termijn_aantal / 30) = t_new.cnt_termijn_aantal
                  AND t_new.cnt_termijn_type = 'M'
                  AND c.cnt_contract_key = x.cnt_contract_key)
 WHERE EXISTS
          (SELECT 1
             FROM cnt_kenmerkcontract kc, cnt_kenmerk k, cnt_v_aanwezigcontract c, cnt_termijn t_old, cnt_termijn t_new
            WHERE     kc.cnt_kenmerkcontract_verwijder IS NULL
                  AND kc.cnt_kenmerk_key = k.cnt_kenmerk_key
                  AND k.cnt_srtkenmerk_key = 361
                  AND kc.cnt_contract_key = c.cnt_contract_key
                  AND c.cnt_contract_opzegtermijn = t_old.cnt_termijn_key
                  AND t_old.cnt_termijn_type = 'D'
                  AND ROUND (t_old.cnt_termijn_aantal / 30) = t_new.cnt_termijn_aantal
                  AND t_new.cnt_termijn_type = 'M'
                  AND c.cnt_contract_key = x.cnt_contract_key);

COMMIT;

UPDATE cnt_termijn t
   SET t.cnt_termijn_omschrijving = 'DO NOT USE:' || t.cnt_termijn_omschrijving
 WHERE t.cnt_termijn_key > 10
       AND NOT EXISTS
             (SELECT 1
                FROM cnt_v_aanwezigcontract c
               WHERE c.ins_discipline_key > 1560
                     AND (c.cnt_contract_rappeltermijn = t.cnt_termijn_key OR c.cnt_contract_opzegtermijn = t.cnt_termijn_key));

COMMIT;

DELETE FROM cnt_termijn t
      WHERE t.cnt_termijn_key > 10
            AND t.cnt_termijn_volgnummer IS NULL
            AND NOT EXISTS
                  (SELECT 1
                     FROM cnt_contract c
                    WHERE c.cnt_contract_rappeltermijn = t.cnt_termijn_key OR c.cnt_contract_opzegtermijn = t.cnt_termijn_key);

COMMIT;

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

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

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