1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
| CREATE OR ALTER PROCEDURE CALCUL_AMORT (
datedebut date,
datefin date)
returns (
libelle varchar(30),
valeur_inv numeric(15,2),
date_serv date,
mois_amort integer,
taux_jour numeric(15,8),
nbre_jours integer,
dotation numeric(15,2))
as
begin
for
select LIB_MATERIEL, Montant_Inv, Date_Mise_Serv, Duree_Amort_Mois, AmortJours, DATE_FIN_AMORT - Date_Mise_Serv + 1,
case WHEN Date_Mise_Serv <= :DateDebut AND :DateFin <= DATE_FIN_AMORT THEN AmortJours * (:datefin - :DateDebut + 1)
WHEN Date_Mise_Serv >= :DateDebut AND :DateFin <= DATE_FIN_AMORT THEN AmortJours * (:datefin - Date_Mise_Serv + 1)
WHEN Date_Mise_Serv <= :DateDebut AND :DateFin >= DATE_FIN_AMORT THEN AmortJours * (DATE_FIN_AMORT - :DateDebut + 1)
END
from AMORTISS A0
inner join (SELECT NUMERO, ROUND(Montant_Inv * 1.00000000 / (DATE_FIN_AMORT - Date_Mise_Serv + 1), 8) AS AmortJours FROM AMORTISS) A1
on A1.NUMERO = A0.NUMERO
WHERE :DateDebut <= DATE_FIN_AMORT AND :DateFin >= DATE_MISE_SERV
INTO :libelle, :valeur_inv, :date_serv, :mois_amort, :taux_jour, :Nbre_Jours, :Dotation
do
begin
suspend;
end
end^ |
Partager