1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| SELECT F.CLIENT,SUBSTRING(F.ANNEE FROM 1 FOR 4) A,F.NUMERO,a.ID,F.ANNEE,0.00 AS MNT_TOT,F.MONTANT_PAYE*-1 MONTANT_PAYE,
MAXVALUE(F.DATE_ECHEANCE,COALESCE(F.ECH2,F.DATE_ECHEANCE),COALESCE(F.ECH3,F.DATE_ECHEANCE)) ECHEANCE,
0.00 AS SOLDE,
NULL AS DATE_FACTURE,
a.DATE_ACTION, a.MONTANT,a.COMMENTAIRE,
F.CLIENT||' '||C.NOM||', '||C.PAYS NOM,
TRIM(F.ANNEE)||'-'||F.NUMERO IDFACTURE
FROM Factures f JOIN Factures_PAIEMENT a ON f.ANNEE=a.ANNEE_facture and f.NUMERO=a.NUMERO_facture
JOIN CLIENTS C ON F.CLIENT=C.NUM_CLIENT
AND TRIM(a.COMMENTAIRE)<>''
&Where
AND CLIENT=:C
UNION
SELECT F.CLIENT,SUBSTRING(F.ANNEE FROM 1 FOR 4),F.NUMERO,0,F.ANNEE,F.MNT_TOT,F.MONTANT_PAYE,
MAXVALUE(F.DATE_ECHEANCE,COALESCE(F.ECH2,F.DATE_ECHEANCE),COALESCE(F.ECH3,F.DATE_ECHEANCE)) ECHEANCE,
MAXVALUE(0,F.MNT_TOT-F.MONTANT_PAYE),
F.DATE_FACTURE,
NULL,0,P.LIBELLE_PAIEMENT,
F.CLIENT||' '||C.NOM||', '||C.PAYS NOM,
TRIM(F.ANNEE)||'-'||F.NUMERO IDFACTURE
FROM FACTURES F JOIN CLIENTS C ON F.CLIENT=C.NUM_CLIENT
JOIN PAIEMENT P ON F.MODE_PAIEMENT=P.CODE_PAIEMENT
&Where
AND CLIENT=:C |
Partager