--
-- $Id$
--
-- WOHW herstelacties
--
DEFINE thisfile = 'XXXX#12345.SQL'
DEFINE dbuser   = '^LOGC'

SET ECHO ON
SET DEFINE ON
COLUMN fcltlogfile NEW_VALUE fcltlogfile NOPRINT;
COLUMN fcltcusterr NEW_VALUE fcltcusterr NOPRINT;
WHENEVER SQLERROR CONTINUE;
SELECT adm.getscriptspoolfile('&thisfile') AS fcltlogfile FROM DUAL;
SPOOL &fcltlogfile
WHENEVER SQLERROR EXIT;
SELECT adm.checkscriptcust('&dbuser') AS fcltcusterr FROM DUAL;
WHENEVER SQLERROR CONTINUE;
PROMPT &fcltcusterr
SET DEFINE OFF

------ payload begin ------
-- Goed uitzoeken, is het wel fout of heeft WOHW gewoon een foute werkwijze gehanteerd?
-- WOHW#74177 -- WOHW#74696 -- En mail van Walter.

DECLARE
    CURSOR c1 IS
          SELECT xcp.ins_deel_key,
                 xcp.ins_srtcontrole_key,
                 (SELECT COUNT (idsc.ins_deel_key)
                    FROM ins_deelsrtcontrole idsc
                   WHERE     idsc.ins_deel_key = xcp.ins_deel_key
                         AND idsc.ins_srtcontrole_key = xcp.ins_srtcontrole_key
                         AND idsc.ins_scenario_key = 1
                         AND idsc.ins_deelsrtcontrole_status IN (0, 2))
                     aantal,
                 TO_CHAR (
                     (SELECT idsc.ins_deelsrtcontrole_freezedate
                        FROM ins_deelsrtcontrole idsc
                       WHERE     idsc.ins_deel_key = xcp.ins_deel_key
                             AND idsc.ins_srtcontrole_key =
                                 xcp.ins_srtcontrole_key
                             AND idsc.ins_scenario_key = 1
                             AND idsc.ins_deelsrtcontrole_status IN (2)),
                     'YYYY')
                     freezedate,
                 TO_CHAR (
                     (SELECT idsc.ins_deelsrtcontrole_plandatum
                        FROM ins_deelsrtcontrole idsc
                       WHERE     idsc.ins_deel_key = xcp.ins_deel_key
                             AND idsc.ins_srtcontrole_key =
                                 xcp.ins_srtcontrole_key
                             AND idsc.ins_scenario_key = 1
                             AND idsc.ins_deelsrtcontrole_status IN (0)),
                     'YYYY')
                     plandatum,
                 (SELECT ins_deelsrtcontrole_key
                    FROM ins_deelsrtcontrole idsc
                   WHERE     idsc.ins_deel_key = xcp.ins_deel_key
                         AND idsc.ins_srtcontrole_key = xcp.ins_srtcontrole_key
                         AND idsc.ins_scenario_key = 1
                         AND idsc.ins_deelsrtcontrole_status IN (2))
                     ins_deelsrtcontrole_key_freeze,
                 (SELECT ins_deelsrtcontrole_key
                    FROM ins_deelsrtcontrole idsc
                   WHERE     idsc.ins_deel_key = xcp.ins_deel_key
                         AND idsc.ins_srtcontrole_key = xcp.ins_srtcontrole_key
                         AND idsc.ins_scenario_key = 1
                         AND idsc.ins_deelsrtcontrole_status IN (0))
                     ins_deelsrtcontrole_key_gepland
            FROM ins_v_defined_inspect_xcp xcp
           WHERE     xcp.ins_scenario_key = 1
                 AND (SELECT COUNT (idsc.ins_deel_key)
                        FROM ins_deelsrtcontrole idsc
                       WHERE     idsc.ins_deel_key = xcp.ins_deel_key
                             AND idsc.ins_srtcontrole_key =
                                 xcp.ins_srtcontrole_key
                             AND idsc.ins_scenario_key = 1
                             AND idsc.ins_deelsrtcontrole_status IN (0, 2)) >
                     1
                 AND TO_CHAR (
                         (SELECT idsc.ins_deelsrtcontrole_freezedate
                            FROM ins_deelsrtcontrole idsc
                           WHERE     idsc.ins_deel_key = xcp.ins_deel_key
                                 AND idsc.ins_srtcontrole_key =
                                     xcp.ins_srtcontrole_key
                                 AND idsc.ins_scenario_key = 1
                                 AND idsc.ins_deelsrtcontrole_status = 2),
                         'YYYY') >=
                     TO_CHAR (
                         (SELECT idsc.ins_deelsrtcontrole_plandatum
                            FROM ins_deelsrtcontrole idsc
                           WHERE     idsc.ins_deel_key = xcp.ins_deel_key
                                 AND idsc.ins_srtcontrole_key =
                                     xcp.ins_srtcontrole_key
                                 AND idsc.ins_scenario_key = 1
                                 AND idsc.ins_deelsrtcontrole_status = 0),
                         'YYYY')
        ORDER BY 1;
BEGIN
    LOOP
        BEGIN
            DELETE FROM ins_deelsrtcontrole
                  WHERE ins_deelsrtcontrole_key = rec.ins_deelsrtcontrole_key_gepland;
        END;
    END LOOP;
END;

-- Wanneer de taak nog nooit is uitgevoerd moet ook de datum_org gevuld zijn, dus dat checken we nu even voor alle taken

UPDATE ins_deelsrtcontrole
   SET ins_deelsrtcontrole_datum_org = ins_deelsrtcontrole_datum
 WHERE     ins_deelsrtcontrole_datum_org IS NULL
       AND ins_deelsrtcontrole_datum IS NOT NULL;                  

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

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

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