PLSQL ORA-01422 & ORA-06512 fetch to many rows I NEED HELP PLS!!!

From: iko <iko_at_iko.demon.nl>
Date: 18 Jun 2002 02:53:22 -0700
Message-ID: <ca270eb5.0206180153.33475013_at_posting.google.com>

I've looked at the code 4 a week now and cant find the prob. IT returnes the ORA 01422 error. Pls help me out here im stuck !!! :( The error is in the two fetch statements or in the declare!

*xxxxx Hier stonden de uitzonderingenxxxxxxxxxxx Import wordt gestart.
.
Controleren Point afdelingen op aanwezigheid in Copie. declare
*
ERROR at line 1:

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 30
ORA-06512: at line 87
ORA-06512: at line 614

//Script start
set serveroutput on
set feedback off
break on Spoolfile
col Spoolfile new_value _Spoolfile
select
'd:\oracle\admin\pkan\log\point\pointcop2.log' Spoolfile from dual;
set termout on
spool &_Spoolfile

---

-------Nu: spool file:

pointcop2.log-------------------------------------
-------------------------------------------------------------------------

set heading off
select '' from dual;
execute dbms_output.put_line ('Nu volgen nadere bijzonderheden per persoon');
select '' from dual;
select decode(0,count(*),
' Telefoonnrs ok, d.w.z. niet te lang', count(*)||
' X Telefoonnr te lang. (-> Telefoonnr leeg in COPIE)')
from point where
  length(point.PHONE) > 12;
set heading on
select ' ' ||
  emplid EMPLID, phone PHONE
from point where
  length(point.PHONE) > 12;
update point p set p.PHONE = NULL
where
  length(p.PHONE) > 12;
execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

set heading off
select decode(0,count(*),
' Huisadressen ok, d.w.z. niet te lang.',count(*)||
' X Huisadres te lang. (-> Aanpassing in COPIE)')
from point where
  LENGTH(ADDRESS1||
' '||

  NL_POST_NR||
  NL_NUMHS_ADD) > 35;
select decode(0,count(*),
' ----', ' Adres/adressen volgens POINT:')
from point where
  LENGTH(ADDRESS1||
' '||

  NL_POST_NR||
  NL_NUMHS_ADD) > 35;
set heading on
select ' '||
  emplid EMPLID, ADDRESS1||
' '||

  NL_POST_NR||
  NL_NUMHS_ADD POINT_ADRES
from point where
  LENGTH(ADDRESS1||
' '||

  NL_POST_NR||
  NL_NUMHS_ADD) > 35;
set heading off
select decode(0,count(*),
' ----', ' Adres/adressen wordt/worden in COPIE:')
from point where
  LENGTH(ADDRESS1||
' '||

  NL_POST_NR||
  NL_NUMHS_ADD) > 35;
set heading on
select ' '||
  emplid EMPLID,
  substr(ADDRESS1,1,35-LENGTH(' '||
  NL_POST_NR||
  NL_NUMHS_ADD))||
' '||

  NL_POST_NR||
  NL_NUMHS_ADD COPIE_ADRES
from point where
  LENGTH(ADDRESS1||
' '||

  NL_POST_NR||
  NL_NUMHS_ADD) > 35;
update point
  set ADDRESS1 = substr(ADDRESS1,1,35-LENGTH(' '||   NL_POST_NR||
  NL_NUMHS_ADD))
where
  LENGTH(ADDRESS1||
' '||

  NL_POST_NR||
  NL_NUMHS_ADD) > 35;
execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
-- Personen, met spaties in registernummer (emplid);

-- select tbv log-file, delete.
-- Opm.: in Point zijn A en T codes voor aankomende en tijdelijke emplid's;

-- Deze emplids worden in Copie niet gebruikt.
-- Bij zulke codes is de eerste positie van het emplid een A of een T;
-- bij de weergave in 6 posities verdwijnt het minst significante

cijfer.
set heading off
select decode(0,count(*),
' Registernummers ok: zonder Spatie, A of T.', count(*)||
' X Spatie, A of T in registernummer (emplid). (-> Persoon niet naar
COPIE)')
from point where

  instr(emplid, ' ') <> 0 OR
  instr(emplid, 'A') <> 0 OR
  instr(emplid, 'T') <> 0;

set heading on
select ' '||
  emplid EMPLID
from point where

  instr(emplid, ' ') <> 0 OR
  instr(emplid, 'A') <> 0 OR
  instr(emplid, 'T') <> 0;

delete
from point where

  instr(emplid, ' ') <> 0 OR
  instr(emplid, 'A') <> 0 OR
  instr(emplid, 'T') <> 0;

execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

set heading off
select decode(0,count(*),
' Naamvolgorde-codes: ok.', count(*)||
' X Onbekende naamvolgorde (nl_name_preference). (-> Persoon niet
naar COPIE)')
from point where
  point.nl_name_preference not in ('1','2','3','4'); set heading on
select ' '||
  emplid EMPLID, nl_name_preference NL_NAME_PREFERENCE from point where
  point.nl_name_preference not in ('1','2','3','4'); delete
from point where
  point.nl_name_preference not in ('1','2','3','4'); execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

set heading off
select decode(0,count(*),
' Geslacht-codes ok', count(*)||
' X Onbekende sex code (nl. niet 1 of 2). (-> Persoon niet naar
COPIE)')
from point where
  point.sex not in ('1','2');
set heading on
select ' '||
  emplid EMPLID, sex SEX_CODE
from point where
  point.sex not in ('1','2');
delete
from point where
  point.sex not in ('1','2');
execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

set heading off
select decode(0,count(*),
' Geboortedata ok, d.w.z. zonder spaties.', count(*) ||
' X Spatie(s) in birthdate. (-> Persoon niet naar COPIE)')
from point where
  instr(birthdate, ' ') <> 0 ;
set heading on
select ' '||
  emplid EMPLID
from point where
  instr(birthdate, ' ') <> 0 ;
delete
from point where
  instr(birthdate, ' ') <> 0 ;
execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

set heading off
select decode(0,count(*),
' Geboortedata ok, d.w.z. allen > 14 jaar.', count(*) ||
' X Jonger dan 14 jaar (-> Persoon niet naar COPIE)')
from point where
  to_date(birthdate, 'yyyymmdd') > sysdate - 14 * 365; set heading on
select ' '||
  emplid EMPLID, to_date(birthdate, 'yyyymmdd') GEBDAT from point where
  to_date(birthdate, 'yyyymmdd') > sysdate - 14 * 365; delete
from point where
  to_date(birthdate, 'yyyymmdd') > sysdate - 14 * 365; execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

set heading off
select decode(0,count(*),
' Geboortedata ok, d.w.z. allen < 75 jaar.', count(*) ||
' X Ouder dan 75 jaar. (-> Persoon niet naar COPIE)')
from point where
  to_date(birthdate, 'yyyymmdd') < sysdate - 75 * 365; set heading on
select ' '||
  emplid EMPLID, to_date(birthdate, 'yyyymmdd') GEBDAT from point where
  to_date(birthdate, 'yyyymmdd') < sysdate - 75 * 365; delete
from point where
  to_date(birthdate, 'yyyymmdd') < sysdate - 75 * 365; execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

--11-01-2000 DG
--MAAK VAN DIT onderstaand DEEL COMMENTAAR OM NIEUWE AFDELINGEN TOE TE LATEN

-- -set heading off
-- -select decode(0,count(*),
-- -  '    Afdcodes (deptid s) ok, d.w.z. bestaan in COPIE.', count(*)

||
-- - ' X Afdcode (deptid) niet in COPIE. (-> Persoon niet naar COPIE)')

-- -from point where
-- -  point.deptid not in (select afdcode from copie.afdeling);
-- -set heading on
-- -select '    '||
-- -  emplid EMPLID, deptid DEPTID, nl_family_name ACHTERNAAM,

nl_first_names VOORNAAM

-- -from point where
-- -  point.deptid not in (select afdcode from copie.afdeling);
-- -delete 
-- -from point where
-- -  point.deptid not in (select afdcode from copie.afdeling);
-- -execute dbms_output.put_line
('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

-- MAAK VAN DIT bovenstaand DEEL COMMENTAAR OM NIEUWE AFDELINGEN TOE TE LATEN
set heading off
select decode(0,count(*),
' Data in dienst (hire_dt) ok, d.w.z. zonder spaties.', count(*)
||
' X Spatie(s) in hire_dt. (-> Persoon niet naar COPIE)')
from point where
  instr(hire_dt, ' ') <> 0;
set heading on
select ' '||
  emplid EMPLID, hire_dt HIRE_DT
from point where
  instr(hire_dt, ' ') <> 0;
delete
from point where
  instr(hire_dt, ' ') <> 0;
execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

set heading off
select decode(0,count(*),
' Data in dienst (hire_dt) ok, binnen 1 jaar in dienst.',
count(*) ||
' X Over meer dan 1 jaar in dienst (hire_dt). (-> Persoon niet naar
COPIE)')
from point where
  to_date(hire_dt, 'yyyymmdd') > sysdate + 1 * 365; set heading on
select ' '||
  emplid EMPLID, to_date(hire_dt, 'yyyymmdd') HIRE_DT from point where
  to_date(hire_dt, 'yyyymmdd') > sysdate + 1 * 365; delete
from point where
  to_date(hire_dt, 'yyyymmdd') > sysdate + 1 * 365; execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

set heading off
select decode(0,count(*),
' Data in dienst (hire_dt) ok, niemand meer dan 60 jaar.',
count(*) ||
' X Meer dan 60 Jaar in dienst (hire_dt). (-> Persoon niet naar
COPIE)')
from point where
  to_date(hire_dt, 'yyyymmdd') < sysdate - 60 * 365; set heading on
select ' '||
  emplid EMPLID, to_date(hire_dt, 'yyyymmdd') HIREDAT from point where
  to_date(hire_dt, 'yyyymmdd') < sysdate - 60 * 365; delete
from point where
  to_date(hire_dt, 'yyyymmdd') < sysdate - 60 * 365; execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

set heading off
select decode(0,count(*),
' Data uit dienst ok, d.w.z. zonder spaties.', count(*) ||
' X Spatie(s) in termination_dt. (-> Persoon niet naar COPIE)')
from point where
  instr(TERMINATION_DT, ' ') <> 0;
set heading on
select ' '||
  emplid EMPLID, termination_dt TERMINATION_DT from point where
  instr(TERMINATION_DT, ' ') <> 0;
delete
from point where
  instr(TERMINATION_DT, ' ') <> 0;
execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

set heading off
select decode(0,count(*),
' Data In/Uit dienst ok, d.w.z. In- voor Uit-dienst.', count(*)
||
' X Uit-dienst voor In-dienst (term.date). (-> Persoon niet naar
COPIE)')
from point where
  to_date(TERMINATION_DT, 'yyyymmdd') < to_date(ORIG_HIRE_DT, 'yyyymmdd');
set heading on
select ' '||
  emplid EMPLID, to_date(TERMINATION_DT, 'yyyymmdd') TERMDATE from point where
  to_date(TERMINATION_DT, 'yyyymmdd') < to_date(ORIG_HIRE_DT, 'yyyymmdd');
delete
from point where
  to_date(TERMINATION_DT, 'yyyymmdd') < to_date(ORIG_HIRE_DT, 'yyyymmdd');
execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

set heading off
select decode(0,count(*),
' Paygroup codes: alle ok, d.w.z. bestaand.', count(*) ||
' X Ongeldige paygroup uit POINT. (-> Persoon niet naar COPIE)')
from point where
  point.paygroup not in
('11','12','13','14','31','41','42','43','44','51','52','57','81','82','PAO'); set heading on
select ' '||
  emplid EMPLID, paygroup PAYGROUP
from point where
  point.paygroup not in
('11','12','13','14','31','41','42','43','44','51','52','57','81','82','PAO'); delete
from point where
  point.paygroup not in
('11','12','13','14','31','41','42','43','44','51','52','57','81','82','PAO'); execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

set heading off
select decode(0,count(*),
' Data in dienst (orig_hire_dt) ok, d.w.z. zonder spaties.',
count(*) ||
' X Personen, met spaties in orig_hire_dt. (-> Persoon niet naar
COPIE)')
from point where
  instr(orig_hire_dt, ' ') <> 0 ;
set heading on
select ' '||
  emplid EMPLID
from point where
  instr(orig_hire_dt, ' ') <> 0 ;
delete
from point where
  instr(orig_hire_dt, ' ') <> 0 ;
execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

set heading off
select decode(0,count(*),
' Data in dienst (orig_hire_dt) ok, niemand over > 1 jaar.',
count(*) ||
' X Over > 1 jaar in dienst (orig_hire_dt). (-> Persoon niet naar
COPIE)')
from point where
  to_date(hire_dt, 'yyyymmdd') > sysdate + 1 * 365; set heading on
select ' '||
  emplid EMPLID, to_date(orig_hire_dt, 'yyyymmdd') ORIGHIREDAT from point where
  to_date(hire_dt, 'yyyymmdd') > sysdate + 1 * 365; delete
from point where
  to_date(orig_hire_dt, 'yyyymmdd') > sysdate + 1 * 365; execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

set heading off
select decode(0,count(*),
' Data in dienst (orig_hire_dt) ok, niemand langer dan 60 jaar.',
count(*) ||
' X > 60 jaar in dienst (orig_hire_dt). (-> Persoon niet naar
COPIE)')
from point where
  to_date(orig_hire_dt, 'yyyymmdd') < sysdate - 60 * 365; set heading on
select ' '||
  emplid EMPLID, to_date(orig_hire_dt, 'yyyymmdd') ORIGHIREDAT from point where
  to_date(orig_hire_dt, 'yyyymmdd') < sysdate - 60 * 365; delete
from point where
  to_date(orig_hire_dt, 'yyyymmdd') < sysdate - 60 * 365; execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

set heading off
select decode(0,count(*),
' Partnernames ok, d.w.z. aanwezig bij naamvoorkeur-codes <> 1.',
count(*) ||
' X nl_name_preference <> 1 zonder Partnername. (-> Persoon niet
naar COPIE)')
from point where
  point.NL_NAME_PREFERENCE in ('2','3','4') and length(replace(point.NL_FAMNAME_PARTNER,' ') ||
'x') = 1;

set heading on
select ' '||
  emplid EMPLID, NL_NAME_PREFERENCE NPREF from point where
  point.NL_NAME_PREFERENCE in ('2','3','4') and length(replace(point.NL_FAMNAME_PARTNER,' ') ||
'x') = 1;

delete
from point where
  point.NL_NAME_PREFERENCE in ('2','3','4') and length(replace(point.NL_FAMNAME_PARTNER,' ') ||
'x') = 1;

execute dbms_output.put_line

('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

spool off
delete
from point where
  emplid = '578849';
spool off

---

-------Nu: andere spool file:

datum.log----------------------------------
-------------------------------------------------------------------------
set feedback on
/*** Opties om output en loggen goed te laten verlopen              

***/
set serveroutput on
set feedback off
break on Spoolfile
col Spoolfile new_value _Spoolfile
select
'd:\oracle\admin\pkan\log\point\' ||
  to_char (sysdate, 'YYMMDD') ||
'.log' Spoolfile

from dual;
set termout on
spool &_Spoolfile

/***********************************************************************/
/***  Verwerp bij voorbaat uit tabel Point                          

***/

/***********************************************************************/

set heading off
select ('*xxxxx Hier stonden de uitzonderingenxxxxxxxxxxx ') from dual;

--
/***********************************************************************/
/***  Declaratieblok Import Point                                   
***/
/***********************************************************************/
declare
  Lege_Regel               varchar2 (3);
  MaxRegisternummer        varchar2 (11) default '999999';
  Point_Medewerker         point%rowtype;
  PointMedewerkers         number (4);
   /*** Cursor met Point medewerkers                                 
***/
  cursor cPoint_Medewerker is  
    select * from point
    where
      emplid < MaxRegisternummer
    AND ROWNUM = 1
order by 
      emplid;
    
  Personeelsnummer number (6);
/***********************************************************************/
/***  Zoekt aan de hand van een opgegeven Afdelingscode (bijv.      
***/
/***  692040) of de afdeling al bestaat in COPIE.                   
***/
/***  Indien de afdeling al bestaat, wordt het COPIE afdelingsnummer
***/
/***  teruggegeven.  Indien de afdeling nog niet bestaat, wordt een 
***/
/***  0 geretourneerd.                                              
***/
/***********************************************************************/
  function fZoekAfdelingsnummer 
    (Afdelingscode in varchar2) return number 
  is  
    /*** Variabelen voor Copie afdelingsnummers                     
***/
    Afdelingsnummer    number (6);
  begin
    /*** Zoek naar Copie Afdelingsnummer via Afdelingscode          
***/
    Afdelingsnummer := 0;
    begin
      select
        afd_key
      into
        Afdelingsnummer
      from
        copie.afdeling
      where
        afdcode = Afdelingscode;
      /*** Exception kan voorkomen als Afdelingscode onbekend is    
***/
      exception
        when no_data_found then
          Afdelingsnummer := 0;    
    end;    
    /*** Retourneer het Copie Afdelingsnummer                      
***/
    return (Afdelingsnummer);
  end;
/***********************************************************************/
/***  Leest via een cursor alle unieke afdeling - werkveld combi-   
***/
/***  naties in en controleert of deze voorkomen in de AFDELING     
***/
/***  tabel.                                                        
***/
/***  Als de afdeling al voorkomt, wordt deze niet geupdate.        
***/
/***  Als de afdeling nog niet bestaat, wordt een nieuw COPIE       
***/
/***  afdelingsnummer berekend, de afdelingsnaam en werkveld op     
***/
/***  tekst gecontroleerd, en wordt de nieuwe afdeling in de        
***/
/***  AFDELING tabel toegevoegd.                                    
***/
/***********************************************************************/
  procedure pControleerAfdelingen 
  is  
    /*** Cursor met Point afdelingen in Copie formaat           ***/
    cursor cAfdeling is  
      select distinct 
        decode (instr (deptname, ' '), 
          0, deptname, 
          substr (deptname, 1, instr (deptname, ' ') - 1)) "NAAM",
        decode (instr (deptname, ' '),
          0, ' ',
          substr (deptname, instr (deptname, ' ') + 1)) "WERKVELD1",
        kp_dept_descr50, 
        deptid
      from
        point
      where
        emplid < MaxRegisternummer
      order by 
        deptid;
    Afdeling            cAfdeling%rowtype;
    Afdelingsnummer     number (6);
  begin
    /*** Maak een loop met alle afdelingen uit Point                
***/
    open cAfdeling;
    loop
      fetch 
        cAfdeling
      into
        Afdeling;
      exit when cAfdeling%notfound;
      /*** Zoek het Copie afdelingsnummer bij de Pointmedewerker    
***/
      Afdelingsnummer := fZoekAfdelingsnummer (Afdeling.deptid);
      /*** Afdeling uit Point: bestaat niet (=0) of wel (>0)        
***/
      if Afdelingsnummer = 0 then
        /*** Eerst het afdelingsnummer voor COPIE berekenen         
***/
        select
          nvl (max (afd_key) + 1, 1)
        into
          Afdelingsnummer
        from
          copie.afdeling;
        /*** Controleer de nieuwe waarden op not null               
***/
        if Afdeling.naam is null then
          Afdeling.naam := 'NIET';
        end if;
        if Afdeling.werkveld1 is null then
          Afdeling.werkveld1 := 'BEKEND';
        end if;
        if Afdeling.kp_dept_descr50 is null then
          Afdeling.kp_dept_descr50 := 'Onbekende afdeling';
        end if;
        /*** insert de nieuwe afdeling dan met de waarden           
***/
        dbms_output.put_line 
          ('** Nieuwe afdeling toegevoegd:  afdcode ' ||
            Afdeling.deptid ||
            ' ' ||
            substr (Afdeling.naam, 1, 6)||
            ', ' ||
            substr (Afdeling.Werkveld1, 1, 7) ||
            '.');
        insert into copie.afdeling
          ( afd_key, 
            naam, werkveld1, 
            werkveld2, afdcode )
        values
          ( Afdelingsnummer, 
            substr (Afdeling.naam, 1, 6), substr (Afdeling.werkveld1,
1, 7),
            Afdeling.kp_dept_descr50, to_number (Afdeling.deptid) );
        commit work;
      else
        dbms_output.put_line 
          ('Bestaande afdeling:  afdcode '||
  Afdeling.deptid);
      end if;
    /*** Einde loop, alle Pointafdelingen zijn verwerkt             
***/
    end loop;
    close cAfdeling;
  end;
/***********************************************************************/
/***  Reserveert een nieuw personeelsnummer in COPIE.               
***/
/***  Ieder personeelslid heeft een COPIE personeelsnummer.  Dit    
***/
/***  is een nieuw uniek nummer, en wordt alleen door COPIE
gebruikt.***/
/***  Het wordt gegenereerd door een update + 1 te doen op een      
***/
/***  speciale PERSONEEL_KEY tabel.  Hierin bevindt zich maar 1 rij,
***/
/***  waarin het laatst gebruikte COPIE personeelsnummer in staat.  
***/
/***********************************************************************/
  function fNieuwPersoneelsnummer 
    return number 
  is  
    /*** Variabelen voor Copie Personeelsnummers                    
***/
    Personeelsnummer    number (6);
  begin
    /*** Update de personeel_key tabel met de gereserveerde pers_key
***/
    update 
      copie.personeel_key
    set
      pers_key = pers_key + 1;
    /*** Voer direct een commit uit zodat tabel gereleased wordt    
***/
    commit work;
    /*** Selecteer de nieuwe pers_key van de tabel                  
***/
    select pers_key 
      into Personeelsnummer
    from
      copie.personeel_key;
    /*** En retourneer dit nieuwe Copie Personeelsnummer            
***/
    return (Personeelsnummer);
  end;
/***********************************************************************/
/***  Zoekt aan de hand van een opgegeven Registernummer (intern    
***/
/***  nummer van KPN) het bijbehorende COPIE personeelsnummer.      
***/
/***  Als het registernummer niet kan worden gevonden, wordt een nul
***/
/***  geretourneerd.                                                
***/
/***********************************************************************/
  function fZoekPersoneelsnummer 
    (Registernummer in varchar2) return number 
  is  
    /*** Variabelen voor Copie Personeelsnummers                    
***/
    Personeelsnummer    number (6);
  begin
    /*** Beginwaarde 0, als niets wordt gevonden, return dan 0      
***/
    Personeelsnummer := 0;
    begin
    /*** Zoek naar Copie personeelsnummer via Registernummer        
***/
      select
        pers_key
      into
        Personeelsnummer
      from
        copie.personeel_kpn
      where
        regnr = Point_Medewerker.emplid;
      /*** Exception kan voorkomen als personeelsnummer onbekend is 
***/
      exception
        when no_data_found then
          Personeelsnummer := 0;    
    end;    
    /*** Retourneer het Copie personeelsnummer                      
***/
    return (Personeelsnummer);
  end;
/***********************************************************************/
/***  Retourneert de voorletters van de Pointmedewerker zonder      
***/
/***  spaties aan de hand van de opgegeven voorletters.             
***/
/***********************************************************************/
  function fBepaalVoorletters
    (PointVoorletters in varchar2) return varchar2
  is  
    /*** Variabelen voor filteren Copie Voorletters (zonder puntjes)
***/
    CopieVoorletters       varchar2 (5);
  begin
    CopieVoorletters := replace (PointVoorletters, '.', '');
    /*** Retourneer de Copie voorletters (zonder puntjes)           
***/
    return (CopieVoorletters);
  end;
/***********************************************************************/
/***  Bepaalt aan de hand van de opgegeven waarden de achternaam van
***/
/***  de Pointmedewerker.  In Point worden achternaam en            
***/
/***  tussenvoegsel, plus meisjesnaam en tussenvoegsel, allen apart 
***/
/***  opgeslagen.  Daarnaast wordt een indicatie aangegeven wat     
***/
/***  de voorkeursnaam van de persoon in kwestie is.                
***/
/***  Als de voorkeur de combinatie achternaam-meisjesnaam is,      
***/
/***  dan dient precies andersom geredeneerd te worden dan normaal. 
***/
/***********************************************************************/
  function fBepaalAchternaam
    ( PointAchternaam in varchar2,
      PointTussenvAchternaam in varchar2,
      PointAchternaamPartner in varchar2,
      PointTussenvPartner in varchar2,
      PointVoorkeur in number ) return varchar2
  is  
    /*** Variabelen voor samenstellen Copie achternaam              
***/
    CopieAchternaam       varchar2 (93);
  begin
    /*** 1 Volledige achternaam wordt: PointAchternaam              
***/
    if PointVoorkeur = 1 then
      CopieAchternaam := rtrim (PointAchternaam, ' ');
    /*** 2 Volledige achternaam wordt: PointAchternaamPartner       
***/
    elsif PointVoorkeur = 2 then
      CopieAchternaam := rtrim (PointAchternaamPartner, ' ');
    /*** 3 Achternaam wordt: Partner-van Eigen        ***/
    elsif PointVoorkeur = 3 then
      CopieAchternaam := rtrim (PointAchternaamPartner, ' ') ||
      '-';
      CopieAchternaam := CopieAchternaam ||
  rtrim (PointTussenvAchternaam, ' ');
      if length(rtrim (PointTussenvAchternaam, ' ') ||
        'x') > 1 then
        CopieAchternaam := CopieAchternaam ||
        ' ';
      end if;
      CopieAchternaam := CopieAchternaam ||
  rtrim (PointAchternaam, ' ');
    /*** 4 Volledige achternaam wordt: Eigen-van Partner            
***/
    elsif PointVoorkeur = 4 then
      CopieAchternaam := rtrim (PointAchternaam, ' ') ||
      '-';
      CopieAchternaam := CopieAchternaam ||
  rtrim (PointTussenvPartner, ' ');
      if length(rtrim (PointTussenvPartner, ' ') ||
      'x') > 1 then
        CopieAchternaam := CopieAchternaam ||
        ' ';
      end if;
      CopieAchternaam := CopieAchternaam ||
      rtrim (PointAchternaamPartner, ' ');
    else
      /*** Bij voorkeur-code onvoorzien                             
***/
      CopieAchternaam := rtrim (PointAchternaam, ' ');
    end if;
    /*** Retourneer de Copie achternaam                             
***/
    return (substr (CopieAchternaam, 1,40));
  end;
/***********************************************************************/
/***  Bepaalt het tussenvoegsel van een Pointmedewerk in Copie.     
***/
/***  Als de voorkeur de combinatie achternaam-meisjesnaam is,      
***/
/***  dan dient precies andersom geredeneerd te worden dan normaal. 
***/
/***********************************************************************/
  function fBepaalTussenvoegsel
    ( PointTussenvAchternaam in varchar2,
      PointTussenvPartner in varchar2,
      PointVoorkeur in number ) return varchar2
  is  
    /*** Variabelen voor samenstellen Copie tussenvoegsel           
***/
    CopieTussenvoegsel       varchar2 (10);
  begin
    /*** Tussenvoegsel afhankelijk van voorkeur meisjes/mannaam     
***/
    if PointVoorkeur in (2,3) then
      /*** Meisjesnaam, dus tussenvoegsel van partner               
***/
      CopieTussenvoegsel := PointTussenvPartner;
    elsif PointVoorkeur in (1,4) then
      /*** Normale achternaam, dus normaal tussenvoegsel            
***/
      CopieTussenvoegsel := PointTussenvAchternaam;
    else
      /*** Bij voorkeur-code onvoorzien                             
***/
      CopieTussenvoegsel := PointTussenvAchternaam;
    end if;
    /*** Retourneer de Copie tussenvoegsel                          
***/
    return (substr (CopieTussenvoegsel, 1,10));
  end;
/***********************************************************************/
/***  Bepaalt en retourneert het geslacht in M/V formaat aan de hand
***/
/***  het opgegeven Pointgeslacht (0 of 1).                         
***/
/***********************************************************************/
  function fBepaalGeslacht
    (PointGeslacht in varchar2) return varchar2
  is  
    /*** Variabelen voor samenstellen Copie Geslacht                
***/
    CopieGeslacht       varchar2 (1);
  begin
    /*** 1 geeft Mannelijk geslacht aan, 2 Vrouwelijk geslacht      
***/
    if PointGeslacht = 'M' then
      CopieGeslacht := 'M';
    elsif PointGeslacht = 'F' then
      CopieGeslacht = 'V';
    else
      CopieGeslacht := 'V';
    end if;
    /*** Retourneer het Copie geslacht                              
***/
    return (CopieGeslacht);
  end;
/***********************************************************************/
/***  De velden DATUMUIT, DATUMIN en GEBDATUM in de COPIE tabel     
***/
/***  PERSONEEL zijn van het datatype DATE. De waardes voor deze    
***/
/***  velden worden door POINT aangeleverd met het datatype varchar 
***/
/***  met formaat YYYYMMDD. De functie fDatumConversie converteert  
***/
/***  de varchars naar dates.                                        
***/
/***********************************************************************/
  function fDatumConversie
    (PointDatum in varchar) return date
  is  
    /*** Variabelen voor samenstellen Copie Datum                 ***/
    CopieDatum       date;
  begin
    CopieDatum := to_date(Pointdatum,'YYYYMMDD');
    return (CopieDatum);
  end;
/***********************************************************************/
/***  Bepaalt aan de hand van de opgegeven Pointgegevens de         
***/
/***  soort van de medewerker zoals deze in Copie wordt gehanteerd. 
***/
/***  Paygroup geeft aan of medewerker vast, stagiair, onbepaald of 
***/
/***  pao'er is.                                                    
***/
/***  Acctcd houdt in of medewerker declarabel (direct, per uur     
***/
/***  schrijven) is of niet-declarabel (niet direct, per maand      
***/
/***  schrijven).                                                   
***/
/***  Type wordt op dit moment nog niet gebruikt, dit zou betekenen:
***/
/***  stagaire HBO, WO etc.  Wordt echter al in Paygroup gevonden.  
***/
/***  9-9-99 DG gewijzigd: vertaling 00010-11 van 3 naar 5          
***/
/***  9-9-99 DG gewijzigd: vertaling 00060-11 van 1 naar 7          
***/
/***  23-9-99 DG gewijzigd: vertaling 00010-11 van 5 naar 7         
***/
/***  23-9-99 DG gewijzigd: vertaling 00060-11 van 7 naar 5         
***/
/***  30-1-01 DG toegevoegd: code paygroup 14 met 8                 
***/
/***  30-1-01 DG gewijzigd: code paygroup 14 van 8 naar 5           
***/
/***  14-1-2002 JK toegevoegd paygroups 41,42,43 en 44, ter         
***/
/***  vervanging van 11,12,13 en 31, die alleen nog in bestaande    
***/
/***  arbeidscontracten gebruikt worden. Ze blijven dus wel bestaan.
***/
/***  De vertaling naar Copie soorten is hetzelfde.                 
***/
/***********************************************************************/
  function fBepaalSoort
    ( PointPaygroup in varchar2) return number
  is  
    CopieSoort          number (1);
  begin
       if PointPaygroup    = '11'  then CopieSoort := 7;
       elsif PointPaygroup = '12'  then CopieSoort := 8;
       elsif PointPaygroup = '13'  then CopieSoort := 8;
       elsif PointPaygroup = '14'  then CopieSoort := 7;
       elsif PointPaygroup = '31'  then CopieSoort := 8;       
       elsif PointPaygroup = '41'  then CopieSoort := 7;
       elsif PointPaygroup = '42'  then CopieSoort := 8;
       elsif PointPaygroup = '43'  then CopieSoort := 8;
       elsif PointPaygroup = '44'  then CopieSoort := 8;
       elsif PointPaygroup = '51'  then CopieSoort := 6;
       elsif PointPaygroup = '52'  then CopieSoort := 6;
       elsif PointPaygroup = '57'  then CopieSoort := 6;
       elsif PointPaygroup = '81'  then CopieSoort := 7;
       elsif PointPaygroup = '82'  then CopieSoort := 6;
       elsif PointPaygroup = 'PAO' then CopieSoort := 6;
       else CopieSoort := 0;
       end if;
       return (CopieSoort);
  end;
/***********************************************************************/
/***  Deze procedure update van het opgegeven Copie personeelsnummer
***/
/***  alle persoonsgegevens die uit Point komen.  Hiervoor          
***/
/***  worden de juiste gegevens in het juiste formaat gezet door    
***/
/***  aanroepen van bovenstaande procedures en functies.            
***/
/***  Daarna wordt het update commando uitgevoerd en wordt direct   
***/
/***  een commit gegenereerd, zodat de gegevens direct bewaard zijn.
***/
/***********************************************************************/
  procedure pUpdateBestaandPersoneel 
    (Personeelsnummer in number)
  is  
    /*** Variabelen voor samenstellen Copie gegevens medewerker     
***/
    UpdateVoorletters            varchar2 (5);
    UpdateAchternaam             varchar2 (40);
    UpdateTussenvoegsel          varchar2 (10);
    UpdateGeslacht               varchar2 (1);
    UpdateAfdelingsnummer        number (3);
    UpdateDatumuitDienst         date;
    UpdateDatuminDienst          date;
    UpdateGeboorteDatum          date;
    UpdateSoortnummer            number (1);
    UpdatePriveadres             varchar2 (35);
  begin
    /*** Bepaal de voorletters van de Pointmedewerker               
***/
    UpdateVoorletters := 
      fBepaalVoorletters
        (Point_Medewerker.kp_initials);
    /*** Bepaal de achternaam van de Pointmedewerker                
***/
    UpdateAchternaam := 
      fBepaalAchternaam (
        Point_Medewerker.nl_family_name,
        Point_Medewerker.nl_family_name_pfx,
        Point_Medewerker.nl_famname_partner,
        Point_Medewerker.nl_partner_prefix,
        Point_Medewerker.nl_name_preference );
    /*** Bepaal het tussenvoegsel van de Pointmedewerker            
***/
    UpdateTussenvoegsel :=
      fBepaalTussenvoegsel (
        Point_Medewerker.nl_family_name_pfx,
        Point_Medewerker.nl_partner_prefix,
        Point_Medewerker.nl_name_preference ); 
    /*** Bepaal het geslacht van de Pointmedewerker                 
***/
    UpdateGeslacht := 
      fBepaalGeslacht (Point_Medewerker.sex);
    /*** Bepaal de afdeling van de Pointmedewerker                  
***/
    UpdateAfdelingsnummer := 
      fZoekAfdelingsnummer (Point_Medewerker.deptid);
    /*** Bepaal de datumuit van de Pointmedewerker                  
***/
    UpdateDatumuitDienst :=
      fDatumConversie (Point_Medewerker.termination_dt);
    /*** Bepaal de datumin van de Pointmedewerker                   
***/
    UpdateDatuminDienst :=
      fDatumConversie (Point_Medewerker.hire_dt);
    /*** Bepaal de geboorte datum van de Pointmedewerker            
***/
    UpdateGeboorteDatum :=
      fDatumConversie (Point_Medewerker.birthdate);
    /*** Bepaal de soort van de Pointmedewerker                     
***/
    UpdateSoortnummer := 
      fBepaalSoort (Point_Medewerker.paygroup);
    /*** Bepaal het priveadres van de Pointmedewerker               
***/
    UpdatePriveadres := 
      Point_Medewerker.address1 ||
      ' ' ||
      Point_Medewerker.nl_post_nr ||
      Point_Medewerker.nl_numhs_add;
    /*** Update nu de samengestelde gegevens in de eerste tabel     
***/
    update copie.personeel
      set
      --naam = ' ',
      voorletters = UpdateVoorletters,
      tussenvoeg = UpdateTussenvoegsel,
      achternaam = UpdateAchternaam,
      afd_key = UpdateAfdelingsnummer,
      geslacht = UpdateGeslacht,
      gebdatum = UpdateGeboorteDatum,
      datumin = UpdateDatuminDienst,
      datumuit = UpdateDatumuitDienst,
      uren = Point_Medewerker.kp_monthly_hrs,
      standplaats = Point_Medewerker.gebouw_code,
      soort_key = UpdateSoortnummer,
      adres = UpdatePriveadres,
      telnr = Point_Medewerker.phone,
      postcode = Point_Medewerker.zip,
      plaats = Point_Medewerker.city
where
  pers_key = Personeelsnummer;
    /*** Direct opslaan toegevoegde medewerker                      
***/
    commit work;
    /*** Laat resultaat in logfile zien                             
***/
    dbms_output.put_line 
      ('Bijwerken van:  regnr ' ||
        Point_Medewerker.emplid ||
        ', ' ||
        Point_Medewerker.nl_family_name ||
        ' ' ||
        Point_Medewerker.nl_first_names);
  end;
/***********************************************************************/
/***  Deze procedure insert een nieuwe persoon in de Copie database.
***/
/***  Eerst wordt een nieuw intern Copie personeelsnummer           
***/
/***  gegenereerd.  Vervolgens worden de juiste gegevens in het     
***/
/***  juiste formaat gezet door aanroepen van bovenstaande          
***/
/***  procedures en functies.                                       
***/
/***  Daarna wordt het insert commando uitgevoerd en wordt direct   
***/
/***  een commit gegenereerd, zodat de gegevens direct bewaard zijn.
***/
/***********************************************************************/
  procedure pInsertBestaandPersoneel 
  is  
    /*** Variabelen voor samenstellen Copie gegevens medewerker     
***/
    Personeelsnummer          number (6);
    InsertVoorletters         varchar2 (5);
    InsertAchternaam          varchar2 (40);
    InsertTussenvoegsel       varchar2 (10);
    InsertGeslacht            varchar2 (1);
    InsertAfdelingsnummer     number (3);
    InsertDatumuitDienst      date;
    InsertDatuminDienst       date;
    InsertGeboorteDatum       date;
    InsertSoortnummer         number (1);
    InsertPriveadres          varchar2 (35);
  begin
    /*** Bepaal een nieuw uniek Copie personeelsnummer              
***/
    Personeelsnummer := 
      fNieuwPersoneelsnummer;
    /*** Bepaal de voorletters van de Pointmedewerker           ***/
    InsertVoorletters := fBepaalVoorletters
      (Point_Medewerker.kp_initials);
    /*** Bepaal de achternaam van de Pointmedewerker            ***/
    InsertAchternaam := 
      fBepaalAchternaam (
        Point_Medewerker.nl_family_name,
        Point_Medewerker.nl_family_name_pfx,
        Point_Medewerker.nl_famname_partner,
        Point_Medewerker.nl_partner_prefix,
        Point_Medewerker.nl_name_preference );
    /*** Bepaal het tussenvoegsel van de Pointmedewerker           
***/
    InsertTussenvoegsel :=
      fBepaalTussenvoegsel (
        Point_Medewerker.nl_family_name_pfx,
        Point_Medewerker.nl_partner_prefix,
        Point_Medewerker.nl_name_preference );
    /*** Bepaal het geslacht van de Pointmedewerker             ***/
    InsertGeslacht := 
      fBepaalGeslacht (Point_Medewerker.sex);
    /*** Bepaal de afdeling van de Pointmedewerker              ***/
    InsertAfdelingsnummer := 
      fZoekAfdelingsnummer (Point_Medewerker.deptid);
    /*** Bepaal de datumuit van de Pointmedewerker              ***/
    InsertDatumuitDienst :=
      fDatumConversie (Point_Medewerker.termination_dt);
    /*** Bepaal de datumin van de Pointmedewerker                   
***/
    InsertDatuminDienst :=
      fDatumConversie (Point_Medewerker.hire_dt);
    /*** Bepaal de geboorte datum van de Pointmedewerker        ***/
    InsertGeboorteDatum :=
      fDatumConversie (Point_Medewerker.birthdate);
    /*** Bepaal de soort van de Pointmedewerker                 ***/
    InsertSoortnummer := 
      fBepaalSoort (Point_Medewerker.paygroup);
    /*** Bepaal het priveadres van de Pointmedewerker           ***/
    InsertPriveadres := 
      Point_Medewerker.address1 ||
      ' ' ||
      Point_Medewerker.nl_post_nr ||
      Point_Medewerker.nl_numhs_add;
    /*** Insert nu de samengestelde gegevens in de eerste tabel     
***/
    insert into copie.personeel
      ( pers_key, 
        naam, 
        voorletters, 
        tussenvoeg, 
        achternaam, 
        geslacht,
        gebdatum,
        afd_key,
        datumin, 
        datumuit, 
        uren, 
        standplaats, 
        soort_key,
        adres, 
        telnr, 
        postcode, 
        plaats )
    values
      ( Personeelsnummer,
        ' ', 
        InsertVoorletters,
        InsertTussenvoegsel, 
        InsertAchternaam,
/***Verwijderd zijn de titulatuur inserts ***/
        InsertGeslacht, InsertGeboorteDatum,
        InsertAfdelingsnummer,
        InsertDatuminDienst, 
        InsertDatumuitDienst,
        Point_Medewerker.kp_monthly_hrs,
        Point_Medewerker.gebouw_code,
        InsertSoortnummer,
        InsertPriveadres,
        Point_Medewerker.phone,
        Point_Medewerker.zip,
        Point_Medewerker.city );
    /*** Insert nu de samengestelde gegevens in de tweede tabel     
***/
    insert into copie.personeel_kpn
      ( pers_key, regnr )
    values
      ( Personeelsnummer, Point_Medewerker.emplid );
    /*** Direct opslaan toegevoegde medewerker                      
***/
    commit work;
    /*** Laat resultaat in logfile zien                             
***/
    dbms_output.put_line 
      ('** Nieuwe medewerker toegevoegd:  regnr ' ||
        Point_Medewerker.emplid ||
        ' ' ||
        Point_Medewerker.nl_family_name ||
        ', ' ||
        Point_Medewerker.nl_first_names);
  end;
/***********************************************************************/
/***  Begin hoofdprogramma Import Point.                            
***/
/***  Hier begint het werkelijk hoofdprogramma van de Import.       
***/
/***      Hiervoor worden eerst de afdelingen                       
***/
/***      gecontroleerd en eventueel toegevoegd, zodat deze directe 
***/
/***      afhankelijkheid gewaarborgd is.                           
***/
/***      Vervolgens worden met een cursor aan de hand van het      
***/
/***      emplid (registernummer) de medewerkers gecontroleerd op   
***/
/***      aanwezigheid in de Copie database.                        
***/
/***      - Indien aanwezig, wordt de medewerker bijgewerkt (update)
***/
/***      - Indien afwezig, wordt de medewerker toegevoegd (insert) 
***/
/***********************************************************************/
begin
  dbms_output.enable (100000);
  Lege_Regel := '.';
    dbms_output.put_line ('Import wordt gestart.');
    dbms_output.put_line (Lege_Regel);
/*** Controleer of Pointafdelingen in Copie voorkomen               
***/
    dbms_output.put_line 
      ('Controleren Point afdelingen op aanwezigheid in Copie.');
    pControleerAfdelingen;
    dbms_output.put_line 
      ('Alle Point afdelingen gecontroleerd en in Copie aanwezig.');
    dbms_output.put_line (Lege_Regel);
    /*** Maak een loop waarin alle Pointers voorkomen               
***/
    open cPoint_Medewerker;
    dbms_output.put_line 
      ('Controleren Point medewerkers op aanwezigheid in Copie.');
    PointMedewerkers := 0;
    loop
      fetch
        cPoint_Medewerker 
      into
        Point_Medewerker;
      exit when cPoint_Medewerker%notfound;
      PointMedewerkers := PointMedewerkers + 1;
      /*** Zoek het Copie personeelsnummer bij de Pointer           
***/
      Personeelsnummer := 
        fZoekPersoneelsnummer (Point_Medewerker.emplid);
      /*** Personeel uit Point: bestaat (>0) of niet (=0)           
***/
      if ( Personeelsnummer > 0) then
        pUpdateBestaandPersoneel (Personeelsnummer);
      else 
        pInsertBestaandPersoneel;
      end if;
    /*** Einde loop, alle Point medewerkers zijn verwerkt           
***/
    end loop;
    dbms_output.put_line (
      to_char (PointMedewerkers) ||
      ' Point medewerkers gecontroleerd en in Copie aanwezig.');
    dbms_output.put_line (Lege_Regel);
    close cPoint_Medewerker;
    dbms_output.put_line 
      ('Einde import van Point medewerkers via dumptabel.');
end;
/
spool off
exit

Received on Tue Jun 18 2002 - 11:53:22 CEST