146 lines
6.4 KiB
SQL
146 lines
6.4 KiB
SQL
--
|
|
-- $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
|