Maya McLeod wrote:

>OK, OK, that one might scare you away for life:
>
>

Really? How about this one then? :-)

/* Procedure DOSSIERS_FACTURER */
CREATE PROCEDURE DOSSIERS_FACTURER (
CODE VARCHAR(20),
CODE_FACTURE VARCHAR(20),
NUM_DEVIS INTEGER,
DATE_FACT DATE,
LOGCODE INTEGER,
NOM VARCHAR(50),
ADR1 VARCHAR(50),
ADR2 VARCHAR(50),
CP VARCHAR(10),
VILLE VARCHAR(40),
LFIRST CHAR(1)
) AS
DECLARE VARIABLE COMPTE VARCHAR ( 10 ) ;
DECLARE VARIABLE DEFUNT_NOM VARCHAR ( 50 ) ;
DECLARE VARIABLE INITIALES VARCHAR ( 3 ) ;
DECLARE VARIABLE DEFUNT_PRENOMS VARCHAR ( 50 ) ;
DECLARE VARIABLE DEFUNT_ADRESSE1 VARCHAR ( 50 ) ;
DECLARE VARIABLE DEFUNT_ADRESSE2 VARCHAR ( 50 ) ;
DECLARE VARIABLE DEFUNT_CP VARCHAR ( 10 ) ;
DECLARE VARIABLE DEFUNT_VILLE VARCHAR ( 40 ) ;
DECLARE VARIABLE DEFUNT_DENOM VARCHAR ( 10 ) ;
DECLARE VARIABLE NEND INTEGER ;
DECLARE VARIABLE HT0 NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE HT1 NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE HT2 NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE TVA0 NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE TVA1 NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE TVA2 NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE TTC0 NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE TTC1 NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE TTC2 NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE HT NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE TVA NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE TTC NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE ACOMPTE NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE NET NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE TAUX0 NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE TAUX1 NUMERIC ( 15 , 2 ) ;
DECLARE VARIABLE TAUX2 NUMERIC ( 15 , 2 ) ;
declare variable fake_code varchar(20);
declare variable dNow date;
declare variable lFactureExiste integer;
BEGIN
initiales = substr(code, 3, 5);
if (substr(initiales, 3, 3) = '/') then
initiales = substr(initiales, 1, 2);
dNow = cast('now' as date);
select extract(year from :dNow) || extract(month from :dNow) ||
extract(day from :dNow)
|| extract(hour from :dNow) || extract(minute from :dNow) ||
extract(second from :dNow)
from rdb$database into :fake_code;
select count(*) from factures where code = :code_facture into
:lFactureExiste;
/*Si la facture n'existe pas, il faut la cr#er*/
if (lFactureExiste = 0) then
begin
INSERT INTO FACTURES ( initiales, DATE_FACT , TYPE_FACT , MARBRERIE,
SITE, CODE , ETAT , OBSERV , AVOIR , DOSSIER , UNIQUE_CODE , RPL$LOCAL,
FAKE_CODE)
SELECT :initiales, :DATE_FACT , 'D' , 'N', SITE, :CODE_FACTURE , 1
, OBSERV , 1 , :CODE , SUBSTR ( :CODE_FACTURE , STRLEN ( :CODE_FACTURE )
-6 , STRLEN ( :CODE_FACTURE ) ) , 'N', :fake_code FROM DOSSIERS WHERE
CODE = :CODE;
/*Si n#c#ssaire, on cr#e le client, qui correspond au d#funt du
dossier*/
SELECT CLIENT, DEFUNT_NOM , CODE , DEFUNT_PRENOMS , DEFUNT_DOMICILE
, DECES_ADRESSE2 , DECES_CP , DECES_VILLE , DEFUNT_DESIGNATION FROM
DOSSIERS WHERE CODE = :CODE INTO :COMPTE, :DEFUNT_NOM , :CODE ,
:DEFUNT_PRENOMS , :DEFUNT_ADRESSE1 , :DEFUNT_ADRESSE2 , :DEFUNT_CP ,
:DEFUNT_VILLE , :DEFUNT_DENOM ;
IF ( COMPTE IS NULL ) THEN
SELECT CODE FROM CLIENTS WHERE NOM = :DEFUNT_NOM AND PRENOM = :NOM
INTO :COMPTE ;
IF ( COMPTE IS NULL ) THEN
BEGIN
SELECT COMPTE FROM RECALC_CLIENT_CODE ( :DEFUNT_NOM ) INTO :COMPTE ;
UPDATE CLIENTS SET NOM = :DEFUNT_NOM , PRENOM = :DEFUNT_PRENOMS ,
ADRESSE1 = :DEFUNT_ADRESSE1 , ADRESSE2 = :DEFUNT_ADRESSE2 , CP =
:DEFUNT_CP , VILLE = :DEFUNT_VILLE , COMPTA = 'Non' , DENOM =
:DEFUNT_DENOM WHERE CODE = :COMPTE ;
END
/*Ensuite, on affecte le code client, ainsi que le nom et l'adresse
du client, sur la facture
Ces renseignements sont ceux qui #taient pass#s en param#tre # la
proc#dure.
*/
UPDATE FACTURES SET CLIENT = :COMPTE , DEST = :NOM , DEST_ADR2 =
:ADR2 , DEST_VILLE = :VILLE , DEST_ADR1 = :ADR1 , DEST_CP = :CP WHERE
CODE = :CODE_FACTURE ;
end
else /*Si la facture existe d#j#, il faut supprimer les lignes de
corps, pour ensuite les r#ins#rer*/
delete from factures_corps where facture = :code_facture;
INSERT INTO FACTURES_CORPS ( ARTICLE , CODE , FACTURE , LIBELLE ,
MONTANTTTC , MONTANTTTCREM , PRIXTTC , QUANTITE , REMISE , TVA_CODE ,
TVA_TAUX , LOGCODE , SITE, FIELD )
SELECT ARTICLE , GEN_ID ( GEN_FACT_CORPS , 1 ) , :CODE_FACTURE ,
LIBELLE , MONTANTTTC , MONTANTTTCREM , PRIXTTC , QUANTITE , REMISE ,
TVA_CODE , TVA_TAUX , :LOGCODE , SITE, FIELD
FROM DOSSIERS_DEVIS DD
WHERE DOSSIER = :CODE
AND NUM_DEVIS = :NUM_DEVIS
AND ( ( DD.FIELD <> 'CERCUEIL_CACHE_VIS' AND DD.FIELD <>
'CERCUEIL_TIRE_FOND' ) OR DD.FIELD IS NULL ) ;
UPDATE DOSSIERS SET ETAT = 1 WHERE CODE = :CODE ;
IF ( LFIRST = 'Y' ) THEN
BEGIN
SELECT ACOMPTE FROM DOSSIERS WHERE CODE = :CODE INTO :ACOMPTE ;
IF ( CODE <> CODE_FACTURE ) THEN
begin
delete from acomptes where code = :code_facture; /*Au cas o# il y
en aurait d#j#*/
INSERT INTO ACOMPTES ( CODE , NUMERO , MONTANT , LIBELLE ,
TYPE_ACOMPTE , MUTUELLE ) SELECT :CODE_FACTURE , GEN_ID ( GEN_ACOMPTES ,
1 ) , MONTANT , LIBELLE , 'F' , MUTUELLE FROM ACOMPTES WHERE CODE = :CODE ;
end
ELSE UPDATE FACTURES SET ACOMPTE = :ACOMPTE WHERE CODE = :CODE_FACTURE ;
END
ELSE ACOMPTE = 0 ;
SELECT SUM ( FC.MONTANTTTCREM ) , FC.TVA_TAUX FROM FACTURES_CORPS FC
WHERE FC.FACTURE = :CODE_FACTURE AND FC.TVA_CODE = 0 GROUP BY
FC.TVA_CODE , FC.TVA_TAUX INTO :TTC0 , :TAUX0 ;
SELECT SUM ( FC.MONTANTTTCREM ) , FC.TVA_TAUX FROM FACTURES_CORPS FC
WHERE FC.FACTURE = :CODE_FACTURE AND FC.TVA_CODE = 1 GROUP BY
FC.TVA_CODE , FC.TVA_TAUX INTO :TTC1 , :TAUX1 ;
SELECT SUM ( FC.MONTANTTTCREM ) , FC.TVA_TAUX FROM FACTURES_CORPS FC
WHERE FC.FACTURE = :CODE_FACTURE AND FC.TVA_CODE = 2 GROUP BY
FC.TVA_CODE , FC.TVA_TAUX INTO :TTC2 , :TAUX2 ;
IF ( TTC0 IS NULL ) THEN TTC0 = 0 ;
IF ( TTC1 IS NULL ) THEN TTC1 = 0 ;
IF ( TTC2 IS NULL ) THEN TTC2 = 0 ;
IF ( TAUX0 IS NULL ) THEN SELECT TAUX FROM TVA WHERE CODE = 0 INTO
:TAUX0 ;
IF ( TAUX1 IS NULL ) THEN SELECT TAUX FROM TVA WHERE CODE = 1 INTO
:TAUX1 ;
IF ( TAUX2 IS NULL ) THEN SELECT TAUX FROM TVA WHERE CODE = 2 INTO
:TAUX2 ;
SELECT ROUND FROM PR_ROUND ( ( ( :TTC0 / ( ( :TAUX0 / 100 ) + 1 ) ) *
( :TAUX0 / 100 ) ) , 2 ) INTO :TVA0 ;
SELECT ROUND FROM PR_ROUND ( ( ( :TTC1 / ( ( :TAUX1 / 100 ) + 1 ) ) *
( :TAUX1 / 100 ) ) , 2 ) INTO :TVA1 ;
SELECT ROUND FROM PR_ROUND ( ( ( :TTC2 / ( ( :TAUX2 / 100 ) + 1 ) ) *
( :TAUX2 / 100 ) ) , 2 ) INTO :TVA2 ;
HT0 = TTC0 -TVA0 ;
HT1 = TTC1 -TVA1 ;
HT2 = TTC2 -TVA2 ;
HT = HT0 + HT1 + HT2 ;
TVA = TVA0 + TVA1 + TVA2 ;
TTC = HT + TVA ;
NET = TTC -ACOMPTE ;
UPDATE FACTURES SET HT0 = :HT0 , HT1 = :HT1 , HT2 = :HT2 , TVA0 =
:TVA0 , TVA1 = :TVA1 , TVA2 = :TVA2 , TTC0 = :TTC0 , TTC1 = :TTC1 , TTC2
= :TTC2 , HT = :HT , TVA = :TVA , TTC = :TTC , NET = :NET WHERE CODE =
:CODE_FACTURE ;
END

:-)
Jonathan Neve.