CREATE OR REPLACE FUNCTION "FS_CALC_MNT_PROP_DOSSIER"   
(p_ann_dossier varchar2, p_num_dossier varchar2)
  return number is
  integrity_error exception;
  errno           integer;
  errmsg          char(200);
  sann            varchar2(4);
  snum            varchar2(6);
  r_mnt_prop_dossier    number(15, 2);
  sidf_comp        varchar2(3);
  sidf_nat varchar2(6);
  smnt_max number(15,2);
  smnt_retenu_ventil number(15,2) :=0;
begin
smnt_retenu_ventil:=fs_calc_mnt_ventil_sum(p_ann_dossier ,p_num_dossier);
select gide.regles.idf_compet,
       gide.regles.idf_nattrav,
       gide.dossier.ann_dossier,
       gide.dossier.num_dossier,
       gide.regles.mnt_max_paye
  into
  sidf_comp,
  sidf_nat,
  sann,
  snum,
  smnt_max
  from gide.regles, gide.dossier
  where
  gide.regles.idf_compet = gide.dossier.idf_compet
  and gide.regles.idf_nattrav = gide.dossier.idf_nattrav 
  and gide.dossier.ann_dossier=p_ann_dossier
  and gide.dossier.num_dossier=p_num_dossier; 
  if smnt_max is not null and smnt_retenu_ventil>smnt_max then
  r_mnt_prop_dossier:=smnt_max;
  else
  r_mnt_prop_dossier:=smnt_retenu_ventil;
  end if;
  return r_mnt_prop_dossier;

EXCEPTION
  WHEN NO_DATA_FOUND then
    errno  := -20005;
    errmsg := 'PAS D ENREGISTREMENT TROUVE POUR CES CRITERES';
    raise_application_error(errno, errmsg);
end;

dec{2} ldec_mnt_prop_dossier

//Appel fonction stockée lb_ret =FS_CALC_MNT_PROP_DOSSIER (as_identifiant,as_annee) -- A coder
Declare fs_calc_mnt_prop_dossier Procedure For FS_CALC_MNT_PROP_DOSSIER(:as_annee,&
                                                                                                 :as_identifiant) using sqlca;
  
  execute fs_calc_mnt_prop_dossier;
  
  FETCH  fs_calc_mnt_prop_dossier INTO :ldec_mnt_prop_dossier;
    CLOSE fs_calc_mnt_prop_dossier;