--
-- $Id$
--
-- Contractpartij omzetten van bedrijf naar student (perslid)

DEFINE thisfile = 'NYBU#51605.SQL'
DEFINE dbuser   = '^NYBU'

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


DECLARE
   CURSOR c
   IS
      SELECT c.cnt_contract_key, tenant.prs_perslid_key
        FROM cnt_contract c,
             (SELECT kc.cnt_contract_key, p.*
                FROM cnt_kenmerkcontract kc, prs_perslid p
               WHERE kc.cnt_kenmerk_key = 81
                     AND kc.cnt_kenmerkcontract_waarde = p.prs_perslid_key)
             tenant
       WHERE     c.ins_discipline_key = 55
         --    AND c.cnt_prs_bedrijf_key = 1643
             AND tenant.cnt_contract_key = c.cnt_contract_key;
BEGIN
   FOR rec IN c
   LOOP
      BEGIN
         UPDATE cnt_contract c
            SET cnt_prs_bedrijf_key = '',
                cnt_prs_perslid_key = rec.prs_perslid_key
          WHERE cnt_contract_key = rec.cnt_contract_key;
               
      END;
   END LOOP;

   COMMIT;
END;
/

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