--
-- $Id$
--
-- Customer specific once-script UWVA#85676.
DEFINE thisfile = 'UWVA#85676.SQL'
DEFINE dbuser   = '^UWVA'

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

-- UWVA#85676: Telefoonnummers terugtoveren uit VINDERDUMP na CSV-import!
SET SERVEROUTPUT ON size 1000000;

DECLARE
   CURSOR c
   IS
        SELECT p.prs_perslid_key,
               p.prs_perslid_nr,
               x.fac_imp_csv_col04 telefoonnr,
               x.fac_imp_csv_col05 mobiel
          FROM prs_v_aanwezigperslid p, fac_imp_csv x
         WHERE p.prs_perslid_nr != '000000'
               AND (p.prs_perslid_telefoonnr IS NULL AND p.prs_perslid_mobiel IS NULL)
               AND x.fac_import_key = 77214
               AND (x.fac_imp_csv_col04 IS NOT NULL OR x.fac_imp_csv_col05 IS NOT NULL)
               AND p.prs_perslid_nr = SUBSTR (x.fac_imp_csv_col01, 3)
      ORDER BY p.prs_perslid_key;

   v_count   NUMBER := 0;
BEGIN
   FOR rec IN c
   LOOP
      BEGIN
         UPDATE prs_perslid
            SET prs_perslid_telefoonnr = rec.telefoonnr,
                prs_perslid_mobiel = rec.mobiel
          WHERE prs_perslid_key = rec.prs_perslid_key;

         fac.trackaction ('PRSUPD', rec.prs_perslid_key, NULL, NULL, 'Telefoonnummer(s) hersteld');
         v_count := v_count + 1;
      END;
   END LOOP;

   DBMS_OUTPUT.PUT_LINE ('S: ' || TO_CHAR (v_count) || ' personen/telefoonnummers bijgewerkt');
END;
/

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