SET ECHO ON;
/*Ce script maj_b4.043_bxxxx.sql ne doit s'appliquer qu'à des bases de niveau b4.043 et supérieur*/
SET ECHO OFF;
SET NAMES ISO8859_1;
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

SET ECHO ON^
/* Cette partie doit  provoquer des erreurs si la base a déjà été modifiée
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX*/
CREATE PROCEDURE PROC_ETAT_EVENEMENTS_SANS_TRI (
    I_CLEF INTEGER,
    S_MODE VARCHAR(1))
RETURNS (
    EV_IND_KLE_FICHE INTEGER,
    EV_IND_TYPE VARCHAR(7),
    EV_IND_DATE_WRITEN VARCHAR(100),
    EV_IND_DATE_YEAR INTEGER,
    EV_IND_DATE_MOIS INTEGER,
    EV_IND_DATE DATE,
    EV_IND_ADRESSE VARCHAR(50),
    EV_IND_CP VARCHAR(10),
    EV_IND_VILLE VARCHAR(50),
    EV_IND_DEPT VARCHAR(30),
    EV_IND_PAYS VARCHAR(30),
    EV_IND_CAUSE VARCHAR(90),
    EV_IND_SOURCE BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET ISO8859_1,
    EV_IND_COMMENT BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET ISO8859_1,
    EV_IND_DESCRIPTION VARCHAR(90),
    EV_IND_REGION VARCHAR(50),
    EV_IND_SUBD VARCHAR(50),
    EV_LIBELLE VARCHAR(30),
    EV_IND_INSEE VARCHAR(6),
    NOM VARCHAR(40),
    PRENOM VARCHAR(60),
    NOM_COMPLET VARCHAR(160),
    SEXE INTEGER,
    TYPE_EVENT VARCHAR(3),
    SOURCE_VIDE INTEGER,
    COMMENT_VIDE INTEGER)
AS
BEGIN suspend; END ^
COMMIT WORK ^

CREATE PROCEDURE "PROC_SEPARATION_PRENOMS" 
(
  "I_DOSSIER" INTEGER,
  "I_VIRGULE" INTEGER
)
AS
BEGIN EXIT; END ^
COMMIT WORK ^

CREATE PROCEDURE "PROC_VILLES_RAYON" 
(
  "LIMITE" DECIMAL(15, 8),
  "LATITUDEA" DECIMAL(15, 8),
  "LONGITUDEA" NUMERIC(15, 8)
)
RETURNS
(
  "CP_CODE" INTEGER,
  "CP_CP" VARCHAR(8) CHARACTER SET ISO8859_1,
  "CP_PREFIXE" VARCHAR(4) CHARACTER SET ISO8859_1,
  "CP_VILLE" VARCHAR(50) CHARACTER SET ISO8859_1,
  "CP_INDIC_TEL" VARCHAR(2) CHARACTER SET ISO8859_1,
  "CP_DEPT" INTEGER,
  "CP_REGION" INTEGER,
  "CP_PAYS" INTEGER,
  "CP_INSEE" VARCHAR(6) CHARACTER SET ISO8859_1,
  "CP_HABITANTS" DOUBLE PRECISION,
  "CP_DENSITE" DOUBLE PRECISION,
  "CP_DIVERS" VARCHAR(90) CHARACTER SET ISO8859_1,
  "CP_LATITUDE" DECIMAL(15, 8),
  "CP_LONGITUDE" NUMERIC(15, 8),
  "CP_MAJ_INSEE" INTEGER,
  "CP_VILLE_MAJ" VARCHAR(50) CHARACTER SET ISO8859_1,
  "DISTANCE" DECIMAL(15, 2)
)
AS
BEGIN suspend; END ^
COMMIT WORK ^

ALTER TABLE ADRESSES_IND 
  ADD "ADR_LATITUDE" DECIMAL(15,8),
  ADD "ADR_LONGITUDE" NUMERIC(15,8)^
COMMIT WORK^

ALTER TABLE ADRESSES_IND 
  ADD "ADR_SUBD" VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE FR_FR^
COMMIT WORK^

ALTER TABLE EVENEMENTS_IND 
  ADD "EV_IND_LATITUDE" DECIMAL(15,8),
  ADD "EV_IND_LONGITUDE" NUMERIC(15,8)^
COMMIT WORK^

ALTER TABLE EVENEMENTS_FAM 
  ADD "EV_FAM_LATITUDE" DECIMAL(15,8),
  ADD "EV_FAM_LONGITUDE" NUMERIC(15,8)^
COMMIT WORK^

CREATE TRIGGER "AUI_REF_CP_VILLE" FOR "REF_CP_VILLE" 
ACTIVE AFTER UPDATE OR INSERT POSITION 0
as
begin
exit;
end ^
COMMIT WORK^

ALTER PROCEDURE "PROC_VILLES_FAVORIS" 
(
  "I_DOSSIER" INTEGER
)
RETURNS
(
  "FAV_ID" INTEGER,
  "EV_IND_VILLE" VARCHAR(50) CHARACTER SET ISO8859_1,
  "EV_IND_CP" VARCHAR(10) CHARACTER SET ISO8859_1,
  "EV_IND_DEPT" VARCHAR(30) CHARACTER SET ISO8859_1,
  "EV_IND_REGION" VARCHAR(50) CHARACTER SET ISO8859_1,
  "EV_IND_PAYS" VARCHAR(30) CHARACTER SET ISO8859_1,
  "EV_IND_INSEE" VARCHAR(6) CHARACTER SET ISO8859_1,
  "EV_IND_SUBD" VARCHAR(50) CHARACTER SET ISO8859_1,
  "EV_IND_LATITUDE" DECIMAL(15, 8),
  "EV_IND_LONGITUDE" NUMERIC(15, 8)
)
AS
BEGIN suspend; END ^
COMMIT WORK^

CREATE PROCEDURE "PROC_PREP_VILLES_FAVORIS" 
(
  "I_DOSSIER" INTEGER
)
RETURNS
(
  "EV_IND_VILLE" VARCHAR(50) CHARACTER SET ISO8859_1,
  "EV_IND_CP" VARCHAR(10) CHARACTER SET ISO8859_1,
  "EV_IND_DEPT" VARCHAR(30) CHARACTER SET ISO8859_1,
  "EV_IND_REGION" VARCHAR(50) CHARACTER SET ISO8859_1,
  "EV_IND_PAYS" VARCHAR(30) CHARACTER SET ISO8859_1,
  "EV_IND_INSEE" VARCHAR(6) CHARACTER SET ISO8859_1,
  "EV_IND_SUBD" VARCHAR(50) CHARACTER SET ISO8859_1,
  "EV_IND_LATITUDE" DECIMAL(15, 8),
  "EV_IND_LONGITUDE" NUMERIC(15, 8)
)
AS
BEGIN suspend; END ^
COMMIT WORK^

CREATE PROCEDURE "PROC_MAJ_COORDONNEES" 
(
  "I_DOSSIER" INTEGER,
  "I_MODE" INTEGER
)
AS
BEGIN EXIT; END ^
COMMIT WORK^


/*Fin des erreurs normales
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX*/
SET ECHO OFF^

ALTER PROCEDURE "F_MAJ" 
(
  "S_IN" VARCHAR(255) CHARACTER SET ISO8859_1
)
RETURNS
(
  "S_OUT" VARCHAR(255) CHARACTER SET ISO8859_1
)
AS
/* Créé par André le 20/01/2006 modifiée le 24/08/2006*/
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE L INTEGER;
DECLARE VARIABLE CHR CHAR(1) CHARACTER SET ISO8859_1;
begin
  S_IN=UPPER(S_IN);
  S_OUT='';
  L=STRLEN(S_IN);
  I=1;
  WHILE (I<=L) DO
    begin
      CHR=SUBSTR(S_IN,I,I);
      S_OUT=S_OUT||CASE 
	                    WHEN CHR <= 'z' THEN CHR
						WHEN CHR='' THEN ''
                        WHEN CHR='÷' THEN '÷'
                        WHEN CHR='ÿ' THEN ''
                        WHEN CHR between 'à' AND 'þ' THEN ASCII_CHAR(ASCII_VAL(CHR)-32)
                        ELSE CHR
                    END;
      I=I+1;
    end
  suspend;
end ^
COMMIT WORK ^

ALTER PROCEDURE "F_MAJ_SANS_ACCENT" 
(
  "S_IN" VARCHAR(255) CHARACTER SET ISO8859_1
)
RETURNS
(
  "S_OUT" VARCHAR(255) CHARACTER SET ISO8859_1
)
AS
begin
  suspend;
end ^
COMMIT WORK ^

ALTER PROCEDURE "PROC_LR_MODIF_CASSE_PRENOM" 
(
  "I_DOSSIER" INTEGER,
  "I_MODE" INTEGER
)
AS
begin
exit;
end ^
COMMIT WORK ^

ALTER PROCEDURE "PROC_EVE_IND" 
(
  "I_CLE" INTEGER
)
RETURNS
(
  "EV_IND_CLEF" INTEGER,
  "EV_IND_KLE_FICHE" INTEGER,
  "EV_IND_KLE_DOSSIER" INTEGER,
  "EV_IND_TYPE" VARCHAR(7) CHARACTER SET ISO8859_1,
  "EV_IND_DATE_WRITEN" VARCHAR(100) CHARACTER SET ISO8859_1,
  "EV_IND_DATE_YEAR" INTEGER,
  "EV_IND_DATE" DATE,
  "EV_IND_ADRESSE" VARCHAR(50) CHARACTER SET ISO8859_1,
  "EV_IND_CP" VARCHAR(10) CHARACTER SET ISO8859_1,
  "EV_IND_VILLE" VARCHAR(50) CHARACTER SET ISO8859_1,
  "EV_IND_DEPT" VARCHAR(30) CHARACTER SET ISO8859_1,
  "EV_IND_PAYS" VARCHAR(30) CHARACTER SET ISO8859_1,
  "EV_IND_CAUSE" VARCHAR(90) CHARACTER SET ISO8859_1,
  "EV_IND_SOURCE" BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET ISO8859_1,
  "EV_IND_COMMENT" BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET ISO8859_1,
  "EV_IND_TYPEANNEE" INTEGER,
  "EV_IND_DESCRIPTION" VARCHAR(90) CHARACTER SET ISO8859_1,
  "EV_IND_REGION" VARCHAR(50) CHARACTER SET ISO8859_1,
  "EV_IND_SUBD" VARCHAR(50) CHARACTER SET ISO8859_1,
  "EV_LIBELLE" VARCHAR(30) CHARACTER SET ISO8859_1,
  "EV_IND_ACTE" INTEGER,
  "EV_IND_INSEE" VARCHAR(6) CHARACTER SET ISO8859_1,
  "EV_IND_HEURE" TIME,
  "EV_IND_ORDRE" INTEGER,
  "EV_IND_TITRE_EVENT" VARCHAR(40) CHARACTER SET ISO8859_1,
  "EV_IND_LATITUDE" DECIMAL(15, 8),
  "EV_IND_LONGITUDE" NUMERIC(15, 8)
)
AS
BEGIN
    SUSPEND;
END ^
COMMIT WORK ^

ALTER PROCEDURE "PROC_PREP_VILLES_FAVORIS" 
(
  "I_DOSSIER" INTEGER
)
RETURNS
(
  "EV_IND_VILLE" VARCHAR(50) CHARACTER SET ISO8859_1,
  "EV_IND_CP" VARCHAR(10) CHARACTER SET ISO8859_1,
  "EV_IND_DEPT" VARCHAR(30) CHARACTER SET ISO8859_1,
  "EV_IND_REGION" VARCHAR(50) CHARACTER SET ISO8859_1,
  "EV_IND_PAYS" VARCHAR(30) CHARACTER SET ISO8859_1,
  "EV_IND_INSEE" VARCHAR(6) CHARACTER SET ISO8859_1,
  "EV_IND_SUBD" VARCHAR(50) CHARACTER SET ISO8859_1,
  "EV_IND_LATITUDE" DECIMAL(15, 8),
  "EV_IND_LONGITUDE" NUMERIC(15, 8)
)
AS
BEGIN
    SUSPEND;
END ^
COMMIT WORK ^

ALTER TRIGGER "AUI_REF_CP_VILLE"
ACTIVE AFTER UPDATE OR INSERT POSITION 0
as
begin
update EVENEMENTS_IND e
  set  e.EV_IND_LATITUDE=new.CP_LATITUDE,
       e.EV_IND_LONGITUDE=new.CP_LONGITUDE
  where coalesce(e.EV_IND_CP,'')=coalesce(new.CP_CP,'')
    and e.EV_IND_VILLE=new.CP_VILLE
    and coalesce(e.EV_IND_INSEE,'')=coalesce(new.CP_INSEE,'')
    and e.EV_IND_SUBD is null;
update EVENEMENTS_FAM e
  set  e.EV_FAM_LATITUDE=new.CP_LATITUDE,
       e.EV_FAM_LONGITUDE=new.CP_LONGITUDE
  where coalesce(e.EV_FAM_CP,'')=coalesce(new.CP_CP,'')
    and e.EV_FAM_VILLE=new.CP_VILLE
    and coalesce(e.EV_FAM_INSEE,'')=coalesce(new.CP_INSEE,'')
    and e.EV_FAM_SUBD is null;
update ADRESSES_IND e
  set  e.ADR_LATITUDE=new.CP_LATITUDE,
       e.ADR_LONGITUDE=new.CP_LONGITUDE
  where coalesce(e.ADR_CP,'')=coalesce(new.CP_CP,'')
    and e.ADR_VILLE=new.CP_VILLE
    and coalesce(e.ADR_INSEE,'')=coalesce(new.CP_INSEE,'')
    and e.ADR_SUBD is null;
end ^
COMMIT WORK^

update EVENEMENTS_IND e
  set  e.EV_IND_LATITUDE=(select first 1 r.CP_LATITUDE from REF_CP_VILLE r
                         where coalesce(r.CP_CP,'')=coalesce(e.EV_IND_CP,'')
                           and r.CP_VILLE=e.EV_IND_VILLE
                           and coalesce(r.CP_INSEE,'')=coalesce(e.EV_IND_INSEE,'')),
       e.EV_IND_LONGITUDE=(select first 1 r.CP_LONGITUDE from REF_CP_VILLE r
                         where coalesce(r.CP_CP,'')=coalesce(e.EV_IND_CP,'')
                           and r.CP_VILLE=e.EV_IND_VILLE
                           and coalesce(r.CP_INSEE,'')=coalesce(e.EV_IND_INSEE,''))
  where e.EV_IND_LATITUDE is NULL
    and e.EV_IND_LONGITUDE is NULL^
COMMIT WORK ^

update EVENEMENTS_FAM e
  set  e.EV_FAM_LATITUDE=(select first 1 r.CP_LATITUDE from REF_CP_VILLE r
                         where coalesce(r.CP_CP,'')=coalesce(e.EV_FAM_CP,'')
                           and r.CP_VILLE=e.EV_FAM_VILLE
                           and coalesce(r.CP_INSEE,'')=coalesce(e.EV_FAM_INSEE,'')),
       e.EV_FAM_LONGITUDE=(select first 1 r.CP_LONGITUDE from REF_CP_VILLE r
                         where coalesce(r.CP_CP,'')=coalesce(e.EV_FAM_CP,'')
                           and r.CP_VILLE=e.EV_FAM_VILLE
                           and coalesce(r.CP_INSEE,'')=coalesce(e.EV_FAM_INSEE,''))
  where e.EV_FAM_LATITUDE is NULL
    and e.EV_FAM_LONGITUDE is NULL^
COMMIT WORK ^

update ADRESSES_IND e
  set  e.ADR_LATITUDE=(select first 1 r.CP_LATITUDE from REF_CP_VILLE r
                         where coalesce(r.CP_CP,'')=coalesce(e.ADR_CP,'')
                           and r.CP_VILLE=e.ADR_VILLE
                           and coalesce(r.CP_INSEE,'')=coalesce(e.ADR_INSEE,'')),
       e.ADR_LONGITUDE=(select first 1 r.CP_LONGITUDE from REF_CP_VILLE r
                         where coalesce(r.CP_CP,'')=coalesce(e.ADR_CP,'')
                           and r.CP_VILLE=e.ADR_VILLE
                           and coalesce(r.CP_INSEE,'')=coalesce(e.ADR_INSEE,''))
  where e.ADR_LATITUDE is NULL
    and e.ADR_LONGITUDE is NULL^
COMMIT WORK ^

ALTER PROCEDURE "PROC_LISTE_PRENOM" 
(
  "I_DOSSIER" INTEGER
)
RETURNS
(
  "PRENOM" VARCHAR(60) CHARACTER SET ISO8859_1,
  "SEXE" INTEGER,
  "CLE_FICHE" INTEGER,
  "NOM" VARCHAR(40) CHARACTER SET ISO8859_1,
  "PRENOM_COMPLET" VARCHAR(60) CHARACTER SET ISO8859_1,
  "CLE_PERE" INTEGER,
  "CLE_MERE" INTEGER
)
AS
begin
        SUSPEND;
end ^
COMMIT WORK ^

ALTER PROCEDURE "PROC_VIDE_BASE" 
(
  "I_CLEF" INTEGER
)
AS
begin
  exit;
end ^
COMMIT WORK ^

ALTER PROCEDURE "PROC_VIDE_TABLE_TECHNIQUE" 
AS
begin
exit; end ^
COMMIT WORK ^

ALTER PROCEDURE "PROC_VIDE_TABLE" 
(
  "I_CLEF" INTEGER
)
AS
begin
exit; end ^
COMMIT WORK ^

ALTER PROCEDURE "PROC_VIDE_DOSSIER" 
(
  "I_CLEF" INTEGER
)
AS
begin
  exit;
end ^
COMMIT WORK ^

ALTER PROCEDURE "PROC_MAJ_COORDONNEES" 
(
  "I_DOSSIER" INTEGER,
  "I_MODE" INTEGER
)
AS
/*Créée par André le 6/9/2006 pour mettre à jour les coordonnées des lieux
dans les évènements et la table des adresses.
I_MODE=1 permet de transférer le contenu du champ lieux des évènements dans le
champ SUBD s'il est vide*/
declare variable VILLE varchar(50);
declare variable CP varchar(10);
declare variable INSEE varchar(6);
declare variable SUBD varchar(50);
declare variable LATITUDE decimal(15,8);
declare variable LONGITUDE numeric(15,8);
begin
If (I_MODE=1) then
begin
  update evenements_ind set
         ev_ind_subd=ev_ind_adresse,
         ev_ind_adresse=null
         where ev_ind_subd is null
           and EV_IND_KLE_DOSSIER=:I_DOSSIER;
  update evenements_fam set
         ev_fam_subd=ev_fam_adresse,
         ev_fam_adresse=null
         where ev_fam_subd is null
           and EV_FAM_KLE_DOSSIER=:I_DOSSIER;
end
for select
       EV_IND_VILLE
      ,EV_IND_CP
      ,EV_IND_INSEE
      ,EV_IND_SUBD
      ,EV_IND_LATITUDE
      ,EV_IND_LONGITUDE
    from PROC_VILLES_FAVORIS(:I_DOSSIER)
    where EV_IND_LATITUDE is not null
      and EV_IND_LONGITUDE is not null
      and (EV_IND_LATITUDE<>0 or EV_IND_LONGITUDE<>0)
    into
          :ville
         ,:cp
         ,:insee
         ,:subd
         ,:latitude
         ,:longitude
do
  begin
    update evenements_ind set
       EV_IND_LATITUDE=:latitude,
       EV_IND_LONGITUDE=:longitude
       where EV_IND_VILLE=:ville
         and coalesce(EV_IND_CP,'')=coalesce(:cp,'')
         and coalesce(EV_IND_INSEE,'')=coalesce(:insee,'')
         and coalesce(EV_IND_SUBD,'')=coalesce(:subd,'')
         and (EV_IND_LATITUDE is null
              or EV_IND_LONGITUDE is null
              or (EV_IND_LATITUDE=0 and EV_IND_LONGITUDE=0))
         and EV_IND_KLE_DOSSIER=:I_DOSSIER;
    update evenements_fam set
       EV_FAM_LATITUDE=:latitude,
       EV_FAM_LONGITUDE=:longitude
       where EV_FAM_VILLE=:ville
         and coalesce(EV_FAM_CP,'')=coalesce(:cp,'')
         and coalesce(EV_FAM_INSEE,'')=coalesce(:insee,'')
         and coalesce(EV_FAM_SUBD,'')=coalesce(:subd,'')
         and (EV_FAM_LATITUDE is null
              or EV_FAM_LONGITUDE is null
              or (EV_FAM_LATITUDE=0 and EV_FAM_LONGITUDE=0))
         and EV_FAM_KLE_DOSSIER=:I_DOSSIER;
    update adresses_ind set
       ADR_LATITUDE=:latitude,
       ADR_LONGITUDE=:longitude
       where ADR_VILLE=:ville
         and coalesce(ADR_CP,'')=coalesce(:cp,'')
         and coalesce(ADR_INSEE,'')=coalesce(:insee,'')
         and coalesce(ADR_SUBD,'')=coalesce(:subd,'')
         and (ADR_LATITUDE is null
           or ADR_LONGITUDE is null
           or (ADR_LATITUDE=0 and ADR_LONGITUDE=0))
         and ADR_KLE_DOSSIER=:I_DOSSIER;
  end
update EVENEMENTS_IND e
  set  e.EV_IND_LATITUDE=(select first 1 r.CP_LATITUDE from REF_CP_VILLE r
                         where coalesce(r.CP_CP,'')=coalesce(e.EV_IND_CP,'')
                           and r.CP_VILLE=e.EV_IND_VILLE
                           and coalesce(r.CP_INSEE,'')=coalesce(e.EV_IND_INSEE,'')),
       e.EV_IND_LONGITUDE=(select first 1 r.CP_LONGITUDE from REF_CP_VILLE r
                         where coalesce(r.CP_CP,'')=coalesce(e.EV_IND_CP,'')
                           and r.CP_VILLE=e.EV_IND_VILLE
                           and coalesce(r.CP_INSEE,'')=coalesce(e.EV_IND_INSEE,''))
  where e.EV_IND_LATITUDE is NULL
     or e.EV_IND_LONGITUDE is NULL
     or (e.EV_IND_LATITUDE=0 and e.EV_IND_LONGITUDE=0)
    and e.EV_IND_KLE_DOSSIER=:I_DOSSIER;
update EVENEMENTS_FAM e
  set  e.EV_FAM_LATITUDE=(select first 1 r.CP_LATITUDE from REF_CP_VILLE r
                         where coalesce(r.CP_CP,'')=coalesce(e.EV_FAM_CP,'')
                           and r.CP_VILLE=e.EV_FAM_VILLE
                           and coalesce(r.CP_INSEE,'')=coalesce(e.EV_FAM_INSEE,'')),
       e.EV_FAM_LONGITUDE=(select first 1 r.CP_LONGITUDE from REF_CP_VILLE r
                         where coalesce(r.CP_CP,'')=coalesce(e.EV_FAM_CP,'')
                           and r.CP_VILLE=e.EV_FAM_VILLE
                           and coalesce(r.CP_INSEE,'')=coalesce(e.EV_FAM_INSEE,''))
  where e.EV_FAM_LATITUDE is NULL
     or e.EV_FAM_LONGITUDE is NULL
     or (e.EV_FAM_LATITUDE=0 and e.EV_FAM_LONGITUDE=0)
    and e.EV_FAM_KLE_DOSSIER=:I_DOSSIER;
update ADRESSES_IND e
  set  e.ADR_LATITUDE=(select first 1 r.CP_LATITUDE from REF_CP_VILLE r
                         where coalesce(r.CP_CP,'')=coalesce(e.ADR_CP,'')
                           and r.CP_VILLE=e.ADR_VILLE
                           and coalesce(r.CP_INSEE,'')=coalesce(e.ADR_INSEE,'')),
       e.ADR_LONGITUDE=(select first 1 r.CP_LONGITUDE from REF_CP_VILLE r
                         where coalesce(r.CP_CP,'')=coalesce(e.ADR_CP,'')
                           and r.CP_VILLE=e.ADR_VILLE
                           and coalesce(r.CP_INSEE,'')=coalesce(e.ADR_INSEE,''))
  where e.ADR_LATITUDE is NULL
     or e.ADR_LONGITUDE is NULL
     or (e.ADR_LATITUDE=0 and e.ADR_LONGITUDE=0)
    and e.ADR_KLE_DOSSIER=:I_DOSSIER;
end ^
COMMIT WORK ^

ALTER PROCEDURE "PROC_COMPTE_ONGLETS" 
(
  "IDOSSIER" INTEGER,
  "ICLEF" INTEGER
)
RETURNS
(
  "COMBIEN" INTEGER,
  "TITRE" VARCHAR(5) CHARACTER SET ISO8859_1
)
AS
BEGIN
    SUSPEND;
END ^
COMMIT WORK ^

ALTER PROCEDURE "PROC_TROUVE_INDI_SUR_FAVORIS" 
(
  "I_DOSSIER" INTEGER,
  "I_VILLE" VARCHAR(50) CHARACTER SET ISO8859_1,
  "I_CP" VARCHAR(10) CHARACTER SET ISO8859_1,
  "I_INSEE" VARCHAR(6) CHARACTER SET ISO8859_1
)
RETURNS
(
  "CLE_FICHE" INTEGER,
  "NOM" VARCHAR(40) CHARACTER SET ISO8859_1,
  "PRENOM" VARCHAR(60) CHARACTER SET ISO8859_1,
  "DATE_NAISSANCE" VARCHAR(100) CHARACTER SET ISO8859_1,
  "EV_IND_CP" VARCHAR(10) CHARACTER SET ISO8859_1,
  "EV_IND_VILLE" VARCHAR(50) CHARACTER SET ISO8859_1,
  "EV_IND_PAYS" VARCHAR(30) CHARACTER SET ISO8859_1,
  "SEXE" INTEGER,
  "VILLE_NAISS" VARCHAR(50) CHARACTER SET ISO8859_1,
  "TYPE_EVENT" VARCHAR(7) CHARACTER SET ISO8859_1
)
AS
BEGIN
    SUSPEND;
END ^
COMMIT WORK^

SET ECHO ON^
/* peut provoquer une erreur
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX*/
DROP TABLE "TQ_LIEUX" ^
COMMIT WORK^
/*Passage en version 4.051.*/
alter table EVENEMENTS_FAM
  ADD EV_FAM_DESCRIPTION VARCHAR(90) CHARACTER SET ISO8859_1 COLLATE FR_FR^
COMMIT WORK^
alter table EVENEMENTS_FAM
  ADD EV_FAM_CAUSE VARCHAR(90) CHARACTER SET ISO8859_1 COLLATE FR_FR^
COMMIT WORK^
/*fin des erreurs normales 
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX*/
SET ECHO OFF^

ALTER PROCEDURE "PROC_TROUVE_UNIONS" 
(
  "I_DOSSIER" INTEGER,
  "I_CLEF" INTEGER
)
RETURNS
(
  "CLE_FICHE" INTEGER,
  "NOM" VARCHAR(40) CHARACTER SET ISO8859_1,
  "PRENOM" VARCHAR(60) CHARACTER SET ISO8859_1,
  "DATE_NAISSANCE" VARCHAR(100) CHARACTER SET ISO8859_1,
  "ANNEE_NAISSANCE" INTEGER,
  "DATE_DECES" VARCHAR(100) CHARACTER SET ISO8859_1,
  "ANNEE_DECES" INTEGER,
  "SEXE" INTEGER,
  "CLE_PERE" INTEGER,
  "CLE_MERE" INTEGER,
  "NUM_SOSA" DOUBLE PRECISION,
  "TYPE_UNION" INTEGER,
  "UNION_CLEF" INTEGER,
  "ANNEE_MARIAGE" INTEGER
)
AS
BEGIN
        SUSPEND;
END ^
COMMIT WORK^

ALTER PROCEDURE "PROC_ETAT_ASCENDANCE" 
(
  "I_CLEF" INTEGER,
  "I_GENERATION" INTEGER,
  "I_DOSSIER" INTEGER,
  "I_PARQUI" INTEGER
)
RETURNS
(
  "GENERATTION" INTEGER,
  "SOSA" DOUBLE PRECISION,
  "NOM" VARCHAR(101) CHARACTER SET ISO8859_1,
  "DATE_NAISSANCE" VARCHAR(100) CHARACTER SET ISO8859_1,
  "CP_NAISSANCE" VARCHAR(10) CHARACTER SET ISO8859_1,
  "VILLE_NAISSANCE" VARCHAR(50) CHARACTER SET ISO8859_1,
  "LIEU_NAISSANCE" VARCHAR(100) CHARACTER SET ISO8859_1,
  "DATE_DECES" VARCHAR(100) CHARACTER SET ISO8859_1,
  "CP_DECES" VARCHAR(10) CHARACTER SET ISO8859_1,
  "VILLE_DECES" VARCHAR(50) CHARACTER SET ISO8859_1,
  "LIEU_DECES" VARCHAR(100) CHARACTER SET ISO8859_1,
  "SEXE" INTEGER,
  "AGE" INTEGER,
  "CLE_FICHE" INTEGER,
  "IMPLEXE" DOUBLE PRECISION,
  "DATE_MARIAGE" VARCHAR(100) CHARACTER SET ISO8859_1,
  "CP_MARIAGE" VARCHAR(10) CHARACTER SET ISO8859_1,
  "VILLE_MARIAGE" VARCHAR(50) CHARACTER SET ISO8859_1
)
AS
begin
     suspend;
end ^
COMMIT WORK^

ALTER PROCEDURE PROC_ETAT_FICHE (
    I_CLEF INTEGER)
RETURNS (
    CLE_FICHE INTEGER,
    NOM VARCHAR(40),
    PRENOM VARCHAR(60),
    DATE_NAISSANCE VARCHAR(100),
    LIEU_NAISSANCE VARCHAR(210),
    DATE_DECES VARCHAR(100),
    LIEU_DECES VARCHAR(210),
    SEXE INTEGER,
    FILLIATION VARCHAR(30),
    COMMENT BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    PERE_NOM VARCHAR(120),
    PERE_NAISSANCE VARCHAR(100),
    PERE_LIEU_NAISSANCE VARCHAR(210),
    PERE_DECES VARCHAR(100),
    PERE_LIEU_DECES VARCHAR(210),
    MERE_NOM VARCHAR(120),
    MERE_NAISSANCE VARCHAR(100),
    MERE_LIEU_NAISSANCE VARCHAR(210),
    MERE_DECES VARCHAR(100),
    MERE_LIEU_DECES VARCHAR(210),
    PHOTO BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    PREFIXE VARCHAR(30),
    SUFFIXE VARCHAR(30),
    SURNOM VARCHAR(120),
    NUM_SOSA DOUBLE PRECISION,
    SOSA1_NOMPRENOM VARCHAR(105))
AS
begin
suspend;
end ^
COMMIT WORK^

ALTER PROCEDURE PROC_ETAT_FICHE_FAMILIALE (
    ACLE_UNION INTEGER)
RETURNS (
    A_TYPE_UNION VARCHAR(40),
    A_EPOUX_CLE INTEGER,
    A_EPOUX_NOMPRENOM VARCHAR(105),
    A_EPOUX_NAISSANCE VARCHAR(100),
    A_EPOUX_LIEU_NAISSANCE VARCHAR(166),
    A_EPOUX_DECES VARCHAR(100),
    A_EPOUX_LIEU_DECES VARCHAR(166),
    A_EPOUSE_CLE INTEGER,
    A_EPOUSE_NOMPRENOM VARCHAR(105),
    A_EPOUSE_NAISSANCE VARCHAR(100),
    A_EPOUSE_LIEU_NAISSANCE VARCHAR(166),
    A_EPOUSE_DECES VARCHAR(100),
    A_EPOUSE_LIEU_DECES VARCHAR(166),
    A_EPOUX_PHOTO BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    A_EPOUSE_PHOTO BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    A_EPOUX_PERE_NOM VARCHAR(40),
    A_EPOUX_PERE_PRENOM VARCHAR(60),
    A_EPOUX_MERE_NOM VARCHAR(40),
    A_EPOUX_MERE_PRENOM VARCHAR(60),
    A_EPOUSE_PERE_NOM VARCHAR(40),
    A_EPOUSE_PERE_PRENOM VARCHAR(60),
    A_EPOUSE_MERE_NOM VARCHAR(40),
    A_EPOUSE_MERE_PRENOM VARCHAR(60),
    A_EPOUX_ANNEE INTEGER,
    A_EPOUSE_ANNEE INTEGER,
    A_EPOUX_AGE_DECES INTEGER,
    A_EPOUSE_AGE_DECES INTEGER,
    A_EPOUX_SOSA DOUBLE PRECISION,
    A_EPOUSE_SOSA DOUBLE PRECISION,
    A_SOSA1_NOMPRENOM VARCHAR(105),
    A_EPOUX_NOM VARCHAR(40),
    A_EPOUSE_NOM VARCHAR(40))
AS
begin
     suspend;
end ^
COMMIT WORK^

ALTER PROCEDURE "PROC_ANNIVERSAIRES" 
(
  "A_MOIS" INTEGER,
  "I_MODE" INTEGER,
  "I_DOSSIER" INTEGER
)
RETURNS
(
  "CLE_FICHE" INTEGER,
  "NOM" VARCHAR(60) CHARACTER SET ISO8859_1,
  "PRENOM" VARCHAR(100) CHARACTER SET ISO8859_1,
  "DATE_NAISSANCE" DATE,
  "DATE_DECES" VARCHAR(100) CHARACTER SET ISO8859_1,
  "SEXE" INTEGER,
  "JOUR" VARCHAR(2) CHARACTER SET ISO8859_1,
  "AGE" INTEGER
)
AS
begin
   SUSPEND; 
end ^
COMMIT WORK^

ALTER TRIGGER "EVENEMENTS_FAM_BI"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
exit;
end^
COMMIT WORK^

ALTER TRIGGER "EVENEMENTS_IND_AI"
ACTIVE AFTER INSERT POSITION 0
as
begin
exit; end ^
COMMIT WORK^

ALTER TRIGGER "TAD_EVENEMENTS_IND"
ACTIVE AFTER DELETE POSITION 0
as
BEGIN
END ^
COMMIT WORK^

ALTER TRIGGER "T_BI_INDIVIDU"
ACTIVE BEFORE INSERT POSITION 0
as
begin
exit; end ^
COMMIT WORK^

ALTER PROCEDURE "PROC_MAJ_DATE_UN_IND" 
(
  "CLE_IND" INTEGER,
  "S_MODE" VARCHAR(5) CHARACTER SET ISO8859_1,
  "S_DATE" VARCHAR(100) CHARACTER SET ISO8859_1,
  "I_YEAR" INTEGER
)
AS
begin
   /*---------------------------------------------------------------------------
   Copyright Philippe Cazaux-Moutou. Tout droits réservés.
   Créé le : 31/07/2001
   à : 19:22:11
   Modifiée le : 4/10/2006 par André, ajout decede
   à : :
   par :
   Description : 
   Usage       :
   ---------------------------------------------------------------------------*/
   if (s_Mode = 'BIRT') then
      update individu
          set
          date_naissance = :S_DATE,
          annee_naissance = :I_YEAR,
          age_au_deces = annee_deces - annee_naissance
       where  (individu.cle_fiche = :CLE_IND) ;
   else
       update individu
           set
           date_deces = :S_DATE,
           annee_deces = :I_YEAR,
           age_au_deces = annee_deces - annee_naissance,
           decede=1
        where  (individu.cle_fiche = :CLE_IND) ;
end ^
COMMIT WORK^


SET ECHO ON^
/*Passage en version 4.052
Les instructions suivantes ne devraient pas provoquer d'erreurs.*/
SET ECHO OFF^

ALTER PROCEDURE "PROC_EVT_DISPO" 
(
  "I_CLEF" INTEGER,
  "A_MODE" CHAR(1) CHARACTER SET ISO8859_1
)
RETURNS
(
  "REF_EVE_CODE" INTEGER,
  "REF_EVE_LIB_COURT" VARCHAR(5) CHARACTER SET ISO8859_1,
  "REF_EVE_LIB_LONG" VARCHAR(30) CHARACTER SET ISO8859_1,
  "REF_EVE_CAT" INTEGER,
  "REF_EVE_VISIBLE" INTEGER
)
AS
begin
   /*---------------------------------------------------------------------------
   Copyright Philippe Cazaux-Moutou. Tout droits réservés.
   Créé le : 31/07/2001
   à : 19:10:18
   Modifiée le :7/10/2006 par André pour autoriser REF_EVE_TYPE='D'
   Description : Donne tous les evenemenst disponibles pour un individu
   Usage       :
   ---------------------------------------------------------------------------*/
   for
     select  REF_EVE_CODE,
             REF_EVE_LIB_COURT,
             REF_EVE_LIB_LONG,
             REF_EVE_CAT,
             REF_EVE_VISIBLE
        from ref_evenements
        where REF_EVE_VISIBLE = 1
          AND ((:A_MODE='I' AND REF_EVE_TYPE in('I','D')
                AND REF_EVE_LIB_COURT NOT IN (SELECT TYPE_EVE
                    FROM proc_evt_ind(:I_CLEF) WHERE UNE_FOIS = 1))
                OR (:A_MODE='U' AND REF_EVE_TYPE in('U','D')))
        ORDER BY REF_EVE_CAT, REF_EVE_LIB_LONG
        INTO :REF_EVE_CODE,
             :REF_EVE_LIB_COURT,
             :REF_EVE_LIB_LONG,
             :REF_EVE_CAT,
             :REF_EVE_VISIBLE
     do  
  suspend;
end ^
COMMIT WORK^

ALTER PROCEDURE "PROC_TROUVE_CONJOINTS" 
(
  "I_DOSSIER" INTEGER,
  "I_CLEF" INTEGER
)
RETURNS
(
  "CLE_FICHE" INTEGER,
  "NOM" VARCHAR(60) CHARACTER SET ISO8859_1,
  "PRENOM" VARCHAR(100) CHARACTER SET ISO8859_1,
  "DATE_NAISSANCE" VARCHAR(100) CHARACTER SET ISO8859_1,
  "ANNEE_NAISSANCE" INTEGER,
  "DATE_DECES" VARCHAR(100) CHARACTER SET ISO8859_1,
  "ANNEE_DECES" INTEGER,
  "SEXE" INTEGER,
  "TYPE_UNION" INTEGER,
  "UNION_CLEF" INTEGER,
  "SOSA" DOUBLE PRECISION,
  "DATE_MARIAGE" VARCHAR(100) CHARACTER SET ISO8859_1,
  "ANNEE_MARIAGE" INTEGER
)
AS
begin
     suspend;
end ^
COMMIT WORK^

update REF_EVENEMENTS set REF_EVE_TYPE='D' where REF_EVE_LIB_COURT in('EVEN','CENS')^
COMMIT WORK^


SET ECHO ON^
/*Passage en version 4.053
Les instructions suivantes peuvent provoquer des erreurs.
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX*/

CREATE PROCEDURE "PROC_AGE_EVENT" 
(
  "I_CLEF_EVE" INTEGER,
  "TYPE_EVE" VARCHAR(1) CHARACTER SET ISO8859_1,
  "I_CLEF_INDI" INTEGER
)
RETURNS
(
  "AGE" INTEGER
)
AS
BEGIN suspend; END ^
COMMIT WORK^

CREATE INDEX EVENEMENTS_FAM_TYPE
ON EVENEMENTS_FAM (EV_FAM_TYPE)^
COMMIT WORK^

DROP INDEX EVENEMENTS_FAM_IDX1^
COMMIT WORK^

CREATE PROCEDURE "PROC_PREP_VILLES_RAYON" 
(
  "LIMITE" DECIMAL(15, 8),
  "LATITUDEA" DECIMAL(15, 8),
  "LONGITUDEA" NUMERIC(15, 8),
  "I_DOSSIER" INTEGER
)
RETURNS
(
  "CP_CODE" INTEGER,
  "CP_CP" VARCHAR(8) CHARACTER SET ISO8859_1,
  "CP_PREFIXE" VARCHAR(4) CHARACTER SET ISO8859_1,
  "CP_VILLE" VARCHAR(103) CHARACTER SET ISO8859_1,
  "CP_INDIC_TEL" VARCHAR(2) CHARACTER SET ISO8859_1,
  "CP_DEPT" INTEGER,
  "CP_REGION" INTEGER,
  "CP_PAYS" INTEGER,
  "CP_INSEE" VARCHAR(6) CHARACTER SET ISO8859_1,
  "CP_HABITANTS" DOUBLE PRECISION,
  "CP_DENSITE" DOUBLE PRECISION,
  "CP_DIVERS" VARCHAR(90) CHARACTER SET ISO8859_1,
  "CP_LATITUDE" DECIMAL(15, 8),
  "CP_LONGITUDE" NUMERIC(15, 8),
  "CP_MAJ_INSEE" INTEGER,
  "CP_VILLE_MAJ" VARCHAR(50) CHARACTER SET ISO8859_1,
  "DISTANCE" DECIMAL(15, 2)
)
AS
BEGIN suspend; END ^
COMMIT WORK^

CREATE PROCEDURE "PROC_AGE_UNIONS" 
(
  "I_CLEF" INTEGER
)
RETURNS
(
  "AGE" INTEGER
)
AS
BEGIN suspend; END ^
COMMIT WORK^

/*Fin des instructions pouvant provoquer des erreurs normales
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX*/
SET ECHO OFF^

ALTER PROCEDURE "PROC_PREP_VILLES_RAYON" 
(
  "LIMITE" DECIMAL(15, 8),
  "LATITUDEA" DECIMAL(15, 8),
  "LONGITUDEA" NUMERIC(15, 8),
  "I_DOSSIER" INTEGER
)
RETURNS
(
  "CP_CODE" INTEGER,
  "CP_CP" VARCHAR(8) CHARACTER SET ISO8859_1,
  "CP_PREFIXE" VARCHAR(4) CHARACTER SET ISO8859_1,
  "CP_VILLE" VARCHAR(103) CHARACTER SET ISO8859_1,
  "CP_INDIC_TEL" VARCHAR(2) CHARACTER SET ISO8859_1,
  "CP_DEPT" INTEGER,
  "CP_REGION" INTEGER,
  "CP_PAYS" INTEGER,
  "CP_INSEE" VARCHAR(6) CHARACTER SET ISO8859_1,
  "CP_HABITANTS" DOUBLE PRECISION,
  "CP_DENSITE" DOUBLE PRECISION,
  "CP_DIVERS" VARCHAR(90) CHARACTER SET ISO8859_1,
  "CP_LATITUDE" DECIMAL(15, 8),
  "CP_LONGITUDE" NUMERIC(15, 8),
  "CP_MAJ_INSEE" INTEGER,
  "CP_VILLE_MAJ" VARCHAR(50) CHARACTER SET ISO8859_1,
  "DISTANCE" DECIMAL(15, 2)
)
AS
begin
  suspend;
end ^
COMMIT WORK^

ALTER PROCEDURE "PROC_VILLES_RAYON" 
(
  "LIMITE" DECIMAL(15, 8),
  "LATITUDEA" DECIMAL(15, 8),
  "LONGITUDEA" NUMERIC(15, 8),
  "I_DOSSIER" INTEGER
)
RETURNS
(
  "CP_CODE" INTEGER,
  "CP_CP" VARCHAR(8) CHARACTER SET ISO8859_1,
  "CP_PREFIXE" VARCHAR(4) CHARACTER SET ISO8859_1,
  "CP_VILLE" VARCHAR(103) CHARACTER SET ISO8859_1,
  "CP_INDIC_TEL" VARCHAR(2) CHARACTER SET ISO8859_1,
  "CP_DEPT" INTEGER,
  "CP_REGION" INTEGER,
  "CP_PAYS" INTEGER,
  "CP_INSEE" VARCHAR(6) CHARACTER SET ISO8859_1,
  "CP_HABITANTS" DOUBLE PRECISION,
  "CP_DENSITE" DOUBLE PRECISION,
  "CP_DIVERS" VARCHAR(90) CHARACTER SET ISO8859_1,
  "CP_LATITUDE" DECIMAL(15, 8),
  "CP_LONGITUDE" NUMERIC(15, 8),
  "CP_MAJ_INSEE" INTEGER,
  "CP_VILLE_MAJ" VARCHAR(50) CHARACTER SET ISO8859_1,
  "DISTANCE" DECIMAL(15, 2)
)
AS
begin
  suspend;
end ^
COMMIT WORK^

ALTER PROCEDURE "PROC_AGE_INDIVIDU" 
(
  "I_CLEF" INTEGER
)
RETURNS
(
  "DATE_DECES" INTEGER,
  "DATE_NAISSANCE" INTEGER,
  "AGE" INTEGER
)
AS
BEGIN
  SUSPEND;
END ^
COMMIT WORK^

ALTER PROCEDURE "PROC_AGE_EVENT" 
(
  "I_CLEF_EVE" INTEGER,
  "TYPE_EVE" VARCHAR(1) CHARACTER SET ISO8859_1,
  "I_CLEF_INDI" INTEGER
)
RETURNS
(
  "AGE" INTEGER
)
AS
begin
  suspend;
end ^
COMMIT WORK^

SET ECHO ON^
/*Passage en version 4.054
Les instructions suivantes peuvent provoquer des erreurs.
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX*/

CREATE INDEX "ADRESSES_IND_KLE_IND" ON "ADRESSES_IND"("ADR_KLE_IND")^
COMMIT WORK^

CREATE PROCEDURE "PROC_TROUVE_PETITS_ENFANTS" 
(
  "I_CLEF" INTEGER
)
RETURNS
(
  "CLE_FICHE" INTEGER,
  "NOM" VARCHAR(40) CHARACTER SET ISO8859_1,
  "PRENOM" VARCHAR(60) CHARACTER SET ISO8859_1,
  "SEXE" INTEGER,
  "DATE_NAISSANCE" VARCHAR(100) CHARACTER SET ISO8859_1,
  "DATE_DECES" VARCHAR(100) CHARACTER SET ISO8859_1,
  "CLE_PERE" INTEGER,
  "CLE_MERE" INTEGER,
  "SOSA" DOUBLE PRECISION
)
AS
BEGIN suspend; END ^
COMMIT WORK^

CREATE PROCEDURE "PROC_TROUVE_NEVEUX_NIECES" 
(
  "I_CLEF" INTEGER
)
RETURNS
(
  "CLE_FICHE" INTEGER,
  "NOM" VARCHAR(40) CHARACTER SET ISO8859_1,
  "PRENOM" VARCHAR(60) CHARACTER SET ISO8859_1,
  "SEXE" INTEGER,
  "DATE_NAISSANCE" VARCHAR(100) CHARACTER SET ISO8859_1,
  "DATE_DECES" VARCHAR(100) CHARACTER SET ISO8859_1,
  "CLE_PERE" INTEGER,
  "CLE_MERE" INTEGER,
  "SOSA" DOUBLE PRECISION
)
AS
BEGIN suspend; END ^
COMMIT WORK^

/*Fin des instructions pouvant provoquer des erreurs normales
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX*/
SET ECHO OFF^

ALTER PROCEDURE "PROC_TROUVE_PETITS_ENFANTS" 
(
  "I_CLEF" INTEGER
)
RETURNS
(
  "CLE_FICHE" INTEGER,
  "NOM" VARCHAR(40) CHARACTER SET ISO8859_1,
  "PRENOM" VARCHAR(60) CHARACTER SET ISO8859_1,
  "SEXE" INTEGER,
  "DATE_NAISSANCE" VARCHAR(100) CHARACTER SET ISO8859_1,
  "DATE_DECES" VARCHAR(100) CHARACTER SET ISO8859_1,
  "CLE_PERE" INTEGER,
  "CLE_MERE" INTEGER,
  "SOSA" DOUBLE PRECISION
)
AS
BEGIN
           suspend;
END ^
COMMIT WORK^

ALTER PROCEDURE "PROC_TROUVE_NEVEUX_NIECES" 
(
  "I_CLEF" INTEGER
)
RETURNS
(
  "CLE_FICHE" INTEGER,
  "NOM" VARCHAR(40) CHARACTER SET ISO8859_1,
  "PRENOM" VARCHAR(60) CHARACTER SET ISO8859_1,
  "SEXE" INTEGER,
  "DATE_NAISSANCE" VARCHAR(100) CHARACTER SET ISO8859_1,
  "DATE_DECES" VARCHAR(100) CHARACTER SET ISO8859_1,
  "CLE_PERE" INTEGER,
  "CLE_MERE" INTEGER,
  "SOSA" DOUBLE PRECISION
)
AS
BEGIN
           suspend;
END ^
COMMIT WORK^

ALTER PROCEDURE PROC_TROUVE_ENFANTS (
    A_CLE_PERE INTEGER,
    A_CLE_MERE INTEGER,
    I_DOSSIER INTEGER)
RETURNS (
    CLE_FICHE INTEGER,
    NOM VARCHAR(40),
    PRENOM VARCHAR(60),
    SEXE INTEGER,
    DATE_NAISSANCE VARCHAR(100),
    DATE_DECES VARCHAR(100),
    KLE_DOSSIER INTEGER,
    SOSA DOUBLE PRECISION,
    PHOTO BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    VILLE_NAISS VARCHAR(166),
    VILLE_DECES VARCHAR(166),
    ANNEE INTEGER,
    ANNEE_DECES INTEGER,
    AGE_DECES INTEGER,
    CLE_PERE INTEGER,
    CLE_MERE INTEGER)
AS
BEGIN
    SUSPEND;
END ^
COMMIT WORK^

ALTER PROCEDURE "PROC_TROUVE_ONCLES_TANTES" 
(
  "I_CLEF" INTEGER,
  "I_DOSSIER" INTEGER,
  "I_MAX" INTEGER
)
RETURNS
(
  "CLE_FICHE" INTEGER,
  "NOM" VARCHAR(40) CHARACTER SET ISO8859_1,
  "PRENOM" VARCHAR(60) CHARACTER SET ISO8859_1,
  "SEXE" INTEGER,
  "DATE_NAISSANCE" VARCHAR(100) CHARACTER SET ISO8859_1,
  "DATE_DECES" VARCHAR(100) CHARACTER SET ISO8859_1,
  "CLE_PERE" INTEGER,
  "CLE_MERE" INTEGER,
  "SOSA" DOUBLE PRECISION
)
AS
BEGIN
         suspend;
END ^
COMMIT WORK^

ALTER PROCEDURE "PROC_TROUVE_COUSINS_COUSINES" 
(
  "I_CLEF" INTEGER,
  "I_DOSSIER" INTEGER,
  "I_MAX" INTEGER
)
RETURNS
(
  "CLE_FICHE" INTEGER,
  "NOM" VARCHAR(40) CHARACTER SET ISO8859_1,
  "PRENOM" VARCHAR(60) CHARACTER SET ISO8859_1,
  "SEXE" INTEGER,
  "DATE_NAISSANCE" VARCHAR(100) CHARACTER SET ISO8859_1,
  "DATE_DECES" VARCHAR(100) CHARACTER SET ISO8859_1,
  "CLE_PERE" INTEGER,
  "CLE_MERE" INTEGER,
  "SOSA" DOUBLE PRECISION
)
AS
BEGIN
         suspend;
END ^
COMMIT WORK^

ALTER PROCEDURE "PROC_INFOS_INDI" 
(
  "IDOSSIER" INTEGER,
  "ICLEF" INTEGER
)
RETURNS
(
  "COMBIEN" INTEGER,
  "TITRE" VARCHAR(5) CHARACTER SET ISO8859_1
)
AS
BEGIN
    SUSPEND;
END ^
COMMIT WORK^

ALTER PROCEDURE "PROC_NAVIGATION" 
(
  "I_CLEF" INTEGER,
  "I_DOSSIER" INTEGER,
  "I_MAX" INTEGER
)
RETURNS
(
  "NIVEAU" INTEGER,
  "CLE_FICHE" INTEGER,
  "NOM" VARCHAR(60) CHARACTER SET ISO8859_1,
  "PRENOM" VARCHAR(100) CHARACTER SET ISO8859_1,
  "DATE_NAISSANCE" VARCHAR(100) CHARACTER SET ISO8859_1,
  "DATE_DECES" VARCHAR(100) CHARACTER SET ISO8859_1,
  "SEXE" INTEGER,
  "CLE_PERE" INTEGER,
  "CLE_MERE" INTEGER,
  "SOSA" DOUBLE PRECISION
)
AS
begin
     suspend;
end ^
COMMIT WORK^
/*maj PROC_TQ_ASCENDANCE annoncée en b4.054 mais oubliée */
ALTER PROCEDURE "PROC_TQ_ASCENDANCE" 
(
  "I_CLEF" INTEGER,
  "I_NIVEAU" INTEGER,
  "I_PARQUI" INTEGER,
  "I_MODE" INTEGER
)
AS
begin
  exit;
end ^
COMMIT WORK^

SET ECHO ON^
/*Passage en version 4.055
Les instructions suivantes peuvent provoquer des erreurs.
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX*/

CREATE TABLE "PRENOMS" 
( "PRENOM"	 VARCHAR(60) CHARACTER SET ISO8859_1 NOT NULL COLLATE FR_FR,
  "PRENOM_MAJ"	 VARCHAR(60) CHARACTER SET ISO8859_1 NOT NULL COLLATE FR_FR,
  "SEXE"	 INTEGER NOT NULL,
CONSTRAINT "PK_PRENOMS_PRENOM" PRIMARY KEY ("PRENOM"))^
COMMIT WORK^

CREATE INDEX "IDX_PRENOMS_PRENOM_MAJ" ON "PRENOMS"("PRENOM_MAJ")^
COMMIT WORK^

CREATE TRIGGER "TRIG_PRENOMS_BIBU" FOR "PRENOMS" 
ACTIVE BEFORE UPDATE OR INSERT POSITION 0
as
begin
select s_out from F_MAJ_SANS_ACCENT(new.prenom)
into new.prenom_maj;
if (new.sexe<>old.sexe) then new.sexe=0;
end ^
COMMIT WORK^

CREATE PROCEDURE "PROC_ECLATE_PRENOM" 
(
  "S_PRENOM" VARCHAR(60) CHARACTER SET ISO8859_1
)
RETURNS
(
  "PRENOM" VARCHAR(60) CHARACTER SET ISO8859_1
)
AS
BEGIN suspend; END ^
COMMIT WORK^

CREATE PROCEDURE "PROC_NEW_PRENOMS" 
(
  "I_DOSSIER" INTEGER
)
AS
BEGIN EXIT; END ^
COMMIT WORK^

CREATE TRIGGER "T_AIU_INDIVIDU" FOR "INDIVIDU" 
ACTIVE AFTER INSERT OR UPDATE POSITION 0
as
begin
exit; end ^
COMMIT WORK^

CREATE TABLE TQ_RECH_DOUBLONS (
    CLE_FICHE           INTEGER NOT NULL,
    DATE_NAISSANCE      DATE,
    DATE_NAISSANCE_FIN  DATE,
    VILLE_NAISSANCE     VARCHAR(50),
    DATE_DECES          DATE,
    DATE_DECES_FIN      DATE,
    VILLE_DECES         VARCHAR(50)
)^
COMMIT WORK^

ALTER TABLE TQ_RECH_DOUBLONS ADD CONSTRAINT PK_TQ_RECH_DOUBLONS PRIMARY KEY (CLE_FICHE)^
COMMIT WORK^

CREATE INDEX TQ_PRENOMS_COMBIEN ON TQ_PRENOMS (COMBIEN)^
COMMIT WORK^

CREATE INDEX TQ_PRENOMS_PRENOM ON TQ_PRENOMS (PRENOM)^
COMMIT WORK^

CREATE PROCEDURE PROC_TEST_PARENTE (
    INDI1 INTEGER,
    INDI2 INTEGER,
    NIVEAU_MAX INTEGER)
RETURNS (
    OUI INTEGER)
AS
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE IDXD INTEGER;
DECLARE VARIABLE IDXF INTEGER;
DECLARE VARIABLE IDT INTEGER;
DECLARE VARIABLE IPERE INTEGER;
DECLARE VARIABLE IMERE INTEGER;
DECLARE VARIABLE IMEMO INTEGER;
DECLARE VARIABLE NIVEAU INTEGER;
begin
  delete from tq_consang;
  if (niveau_max=0) then niveau_max=10;
  insert into tq_consang (id,indi) values (1,:indi1);
  i=1;
  idxd=0;
  idxf=i;
  niveau=0;
  while (idxf>idxd and niveau<niveau_max) do
  begin
    for select indi from tq_consang where id>:idxd and id<=:idxf
      into :idt
      do
      begin
        select cle_pere,cle_mere from individu
        where cle_fiche=:idt
        into :ipere,:imere;
        if (ipere>0) then
        begin
          i=i+1;
          insert into tq_consang (id,indi) values (:i,:ipere);
        end
        if (imere>0) then
        begin
          i=i+1;
          insert into tq_consang (id,indi) values (:i,:imere);
        end
      end
    idxd=idxf;
    idxf=i;
    niveau=niveau+1;
  end
  imemo=i;
  i=i+1;
  insert into tq_consang (id,indi) values (:i,:indi2);
  idxf=i;
  niveau=0;
  while (idxf>idxd and niveau<niveau_max) do
  begin
    for select indi from tq_consang where id>:idxd and id<=:idxf
      into :idt
      do
      begin
        select cle_pere,cle_mere from individu
        where cle_fiche=:idt
        into :ipere,:imere;
        if (ipere>0) then
        begin
          i=i+1;
          insert into tq_consang (id,indi) values (:i,:ipere);
        end
        if (imere>0) then
        begin
          i=i+1;
          insert into tq_consang (id,indi) values (:i,:imere);
        end
      end
    idxd=idxf;
    idxf=i;
    niveau=niveau+1;
  end
  select 1 from rdb$database where exists(
  select * from tq_consang i1,tq_consang i2
  where i1.id>0 and i1.id<=:imemo
    and i2.id>:imemo
    and i1.indi=i2.indi)
  into oui;
  suspend;
end^
COMMIT WORK^

/*Fin des instructions pouvant provoquer des erreurs normales
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX*/
SET ECHO OFF^

ALTER PROCEDURE "PROC_NEW_PRENOMS" 
(
  "I_DOSSIER" INTEGER
)
AS
begin
exit; end ^
COMMIT WORK^

ALTER PROCEDURE "PROC_LISTE_PRENOM" 
(
  "I_DOSSIER" INTEGER
)
RETURNS
(
  "PRENOM" VARCHAR(60) CHARACTER SET ISO8859_1,
  "SEXE" INTEGER,
  "CLE_FICHE" INTEGER,
  "NOM" VARCHAR(40) CHARACTER SET ISO8859_1,
  "PRENOM_COMPLET" VARCHAR(60) CHARACTER SET ISO8859_1,
  "CLE_PERE" INTEGER,
  "CLE_MERE" INTEGER
)
AS
begin
 suspend;
end ^
COMMIT WORK^

ALTER PROCEDURE "PROC_ETAT_ECLAIR" 
(
  "I_DOSSIER" INTEGER,
  "I_SOSA" INTEGER,
  "A_VILLE" VARCHAR(50) CHARACTER SET ISO8859_1
)
RETURNS
(
  "NOM" VARCHAR(40) CHARACTER SET ISO8859_1,
  "CP" VARCHAR(10) CHARACTER SET ISO8859_1,
  "VILLE" VARCHAR(50) CHARACTER SET ISO8859_1,
  "PAYS" VARCHAR(30) CHARACTER SET ISO8859_1,
  "DATE_DEBUT" INTEGER,
  "DATE_FIN" INTEGER,
  "NAISSANCE" INTEGER,
  "BAPTEME" INTEGER,
  "MARIAGE" INTEGER,
  "DECES" INTEGER,
  "SEP" INTEGER,
  "INSEE" VARCHAR(6) CHARACTER SET ISO8859_1,
  "DEPT" VARCHAR(30) CHARACTER SET ISO8859_1,
  "REGION" VARCHAR(50) CHARACTER SET ISO8859_1
)
AS
begin
          SUSPEND;
end ^
COMMIT WORK^

ALTER PROCEDURE "PROC_MAJ_CONSANG" 
(
  "I_DOSSIER" INTEGER,
  "SOSA" INTEGER,
  "I_NIVEAU" INTEGER
)
AS
DECLARE VARIABLE ID INTEGER;
begin
  DELETE FROM TQ_CONSANG;
  ID=GEN_ID(GEN_TQ_ID,-GEN_ID(GEN_TQ_ID,0));
  UPDATE INDIVIDU SET CONSANGUINITE=NULL WHERE KLE_DOSSIER=:I_DOSSIER;
  IF (SOSA=1) THEN
    UPDATE INDIVIDU i
    SET i.CONSANGUINITE=(SELECT CONSANGUINITE FROM PROC_CONSANG(i.CLE_FICHE,0,:I_NIVEAU))
    WHERE i.KLE_DOSSIER=:I_DOSSIER AND i.NUM_SOSA>0;
  ELSE
    UPDATE INDIVIDU i
    SET i.CONSANGUINITE=(SELECT CONSANGUINITE FROM PROC_CONSANG(i.CLE_FICHE,0,:I_NIVEAU))
    WHERE i.KLE_DOSSIER=:I_DOSSIER;
  DELETE FROM TQ_CONSANG;
  ID=GEN_ID(GEN_TQ_ID,-GEN_ID(GEN_TQ_ID,0));
end ^
COMMIT WORK^

ALTER PROCEDURE PROC_TROUVE_MULTIMEDIA (
    I_CLEF INTEGER,
    S_TYPE CHAR(1))
RETURNS (
    MULTI_INFOS VARCHAR(53),
    MULTI_MEDIA BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    MULTI_MEDIA_NORMALE BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    MULTI_MEMO BLOB SUB_TYPE 1 SEGMENT SIZE 80)
AS
BEGIN
 SUSPEND;
END^
COMMIT WORK^

ALTER PROCEDURE PROC_ETAT_EVENEMENTS (
    I_DOSSIER INTEGER)
RETURNS (
    DATE_EVE VARCHAR(100),
    CP VARCHAR(10),
    VILLE VARCHAR(50),
    S_TYPE VARCHAR(7),
    NOM VARCHAR(40),
    PRENOM VARCHAR(60),
    NOM_COMPLET VARCHAR(160),
    SEXE INTEGER)
AS
begin suspend; end ^
COMMIT WORK ^

ALTER PROCEDURE PROC_ETAT_EVENEMENTS_SANS_TRI (
    I_CLEF INTEGER,
    S_MODE VARCHAR(1))
RETURNS (
    EV_IND_KLE_FICHE INTEGER,
    EV_IND_TYPE VARCHAR(7),
    EV_IND_DATE_WRITEN VARCHAR(100),
    EV_IND_DATE_YEAR INTEGER,
    EV_IND_DATE_MOIS INTEGER,
    EV_IND_DATE DATE,
    EV_IND_ADRESSE VARCHAR(50),
    EV_IND_CP VARCHAR(10),
    EV_IND_VILLE VARCHAR(50),
    EV_IND_DEPT VARCHAR(30),
    EV_IND_PAYS VARCHAR(30),
    EV_IND_CAUSE VARCHAR(90),
    EV_IND_SOURCE BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    EV_IND_COMMENT BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    EV_IND_DESCRIPTION VARCHAR(90),
    EV_IND_REGION VARCHAR(50),
    EV_IND_SUBD VARCHAR(50),
    EV_LIBELLE VARCHAR(30),
    EV_IND_INSEE VARCHAR(6),
    NOM VARCHAR(40),
    PRENOM VARCHAR(60),
    NOM_COMPLET VARCHAR(160),
    SEXE INTEGER,
    TYPE_EVENT VARCHAR(3),
    SOURCE_VIDE INTEGER,
    COMMENT_VIDE INTEGER)
AS
begin
     suspend;
end ^
COMMIT WORK ^

ALTER PROCEDURE PROC_DATE_WRITEN (
    DATE_WRITEN VARCHAR(100))
RETURNS (
    IMOIS INTEGER,
    IAN INTEGER,
    DDATE DATE,
    IMOIS_FIN INTEGER,
    IAN_FIN INTEGER,
    DDATE_FIN DATE,
    DATE_WRITEN_S VARCHAR(100),
    TYPE_TOKEN1 INTEGER,
    TYPE_TOKEN2 INTEGER)
AS
begin
  SUSPEND;
end^
COMMIT WORK ^

ALTER PROCEDURE PROC_DOUBLONS_FIND (
    I_DOSSIER INTEGER,
    CLE_IND INTEGER,
    PRENOM_VARIABLE INTEGER)
RETURNS (
    CLE_FICHE INTEGER,
    NOM VARCHAR(40),
    PRENOM VARCHAR(60),
    DATE_NAISS VARCHAR(100),
    LIEU_NAISS VARCHAR(50),
    DATE_DECES VARCHAR(100),
    LIEU_DECES VARCHAR(50))
AS
BEGIN
        suspend;
END^
COMMIT WORK ^

ALTER TRIGGER "T_AIU_INDIVIDU"
ACTIVE AFTER INSERT OR UPDATE POSITION 0
as
begin
exit; end ^
COMMIT WORK^

ALTER PROCEDURE PROC_AFTER_IMPORT (
    I_DOSSIER INTEGER,
    I_MODE INTEGER)
AS
begin
  exit;
end^
COMMIT WORK^

alter procedure proc_trouve_notes (
    idossier integer,
    ssource varchar(20))
returns (
    ev_ind_source blob sub_type 1 segment size 80,
    nom varchar(40),
    prenom varchar(60),
    ev_ind_kle_fiche integer,
    ev_ind_cp varchar(10),
    ev_ind_ville varchar(50),
    ou_ca varchar(7),
    sexe integer)
as
begin
suspend; end^
commit^

alter procedure proc_trouve_source (
    idossier integer,
    ssource varchar(20))
returns (
    ev_ind_source blob sub_type 1 segment size 80,
    nom varchar(40),
    prenom varchar(60),
    ev_ind_kle_fiche integer,
    ev_ind_cp varchar(10),
    ev_ind_ville varchar(50),
    ou_ca varchar(7),
    sexe integer)
as
begin
suspend; end^
commit^

alter PROCEDURE PROC_CONJOINTS_ENFANTS (
    ICLEF INTEGER,
    ISEXE INTEGER)
RETURNS (
    NOM VARCHAR(40),
    PRENOM VARCHAR(60),
    ANNEE_NAISSANCE INTEGER,
    ANNEE_DECES INTEGER,
    DATE_MARRIAGE DATE,
    VILLE_MARRIAGE VARCHAR(50),
    DATE_DIVORCE DATE,
    VILLE_DIVORCE VARCHAR(50),
    CLE_FICHE INTEGER,
    CLE_PERE INTEGER,
    CLE_MERE INTEGER,
    PREFIXE VARCHAR(30),
    SURNOM VARCHAR(120),
    SUFFIXE VARCHAR(30),
    SEXE INTEGER,
    SOURCE BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    COMMENT BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    ENFANT_CLE INTEGER,
    ENFANT_NOM VARCHAR(40),
    ENFANT_ENFANT VARCHAR(60),
    ENFANT_DATE_NAISSANCE INTEGER,
    ENFANT_DATE_DECES INTEGER,
    ENFANT_SEXE INTEGER)
AS
BEGIN 
suspend; END^
commit^

ALTER PROCEDURE "PROC_ASCEND_DESCEND" 
(
  "I_CLEF" INTEGER,
  "I_NIVEAU" INTEGER,
  "I_DOSSIER" INTEGER,
  "I_PARQUI" INTEGER
)
RETURNS
(
  "MODE" CHAR(1) CHARACTER SET ISO8859_1,
  "CLE_FICHE" INTEGER,
  "PREFIXE" VARCHAR(30) CHARACTER SET ISO8859_1,
  "NOM" VARCHAR(40) CHARACTER SET ISO8859_1,
  "PRENOM" VARCHAR(60) CHARACTER SET ISO8859_1,
  "SURNOM" VARCHAR(120) CHARACTER SET ISO8859_1,
  "SUFFIXE" VARCHAR(30) CHARACTER SET ISO8859_1,
  "SEXE" INTEGER,
  "CLE_PARENTS" INTEGER,
  "SOURCE" BLOB,
  "COMMENT" BLOB,
  "NUM_SOSA" VARCHAR(120) CHARACTER SET ISO8859_1,
  "NIVEAU" INTEGER,
  "FILLIATION" VARCHAR(30) CHARACTER SET ISO8859_1,
  "CLE_MERE" INTEGER,
  "CLE_PERE" INTEGER,
  "NCHI" INTEGER,
  "NMR" INTEGER,
  "CLE_FIXE" INTEGER
)
AS
begin   suspend; end^
COMMIT WORK^

SET ECHO ON^
/*Passage en version 4.060
L'utilisation de la base modifiée avec une version du logiciel inférieure à V605 peut entraîner des disfonctonnements.*/
SET ECHO OFF^

SET TERM ; ^
SET AUTODDL ON;
UPDATE T_VERSION_BASE SET VER_VERSION='4.060';
OUTPUT;
exit;
