create or replace FUNCTION LCR(pdate DATE)
RETURN number
is
tempdate date;
m1 number;
m2 number;
m3 number;
resultat number;
begin
m1:=0;
m2:=0;
m3:=0;
resultat:=0;
tempdate:=pdate;
SELECT add_months(pdate, -1) INTO tempdate FROM dual;
select sum(f.Prop_30j*f.Prix_TTC) INTO m1 from facture f where f.Mode_Paiement = 'LCR'
										AND to_char(f.date_commande,'MM/YYYY')=to_char(tempdate,'MM/YYYY');
SELECT add_months(pdate, -1) INTO tempdate FROM dual;
select sum(f.Prop_60j*f.Prix_TTC) INTO m2 from facture f where f.Mode_Paiement = 'LCR'
										AND to_char(f.date_commande,'MM/YYYY')=to_char(tempdate,'MM/YYYY');

SELECT add_months(pdate, -1) INTO tempdate FROM dual;
select sum(f.Prop_90j*f.Prix_TTC) INTO m3 from facture f where f.Mode_Paiement = 'LCR'
										AND to_char(f.date_commande,'MM/YYYY')=to_char(tempdate,'MM/YYYY');
if m1 IS NULL then
  m1 := 0;
end if;
if m2 IS NULL then
  m2 := 0;
end if;
if m3 IS NULL then
  m3 := 0;
end if;

resultat:=m1+m2+m3;
return (resultat);
END;
/