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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133
|
CREATE PROCEDURE BALCN (
DEBUT DATE,
FIN DATE)
RETURNS (
COMPTE VARCHAR (10),
INTT VARCHAR (80),
ANVD NUMERIC (15, 2),
ANVC NUMERIC (15, 2),
ANV NUMERIC (15, 2),
DEBIT NUMERIC (15, 2),
CREDIT NUMERIC (15, 2),
SOLDE NUMERIC (15, 2),
SDEBIT NUMERIC (15, 2),
SCREDIT NUMERIC (15, 2),
SSOLDE NUMERIC (15, 2),
FCLE INTEGER,
CPT3 VARCHAR (3),
CPT4 VARCHAR (4),
CPT5 VARCHAR (5),
CPT6 VARCHAR (6),
CPT7 VARCHAR (7))
AS
Begin
FOR
select
m.collectif,
r.intitule,
sum(m.debit) DEBIT_N,
sum(m.credit) CREDIT_N,
sum(m.debit)-sum(m.credit) TOTALN
from mouv M
INNER JOIN compte r ON ( M.collectif=R.compte)
where journal<>'ANV' AND JOURNAL<>'ANF' AND JOURNAL<>'ANC' AND M.datemvt BETWEEN :debut AND :fin
group by m.collectif,R.intitule,r.debit_n_1,R.credit_n_1
into :COMPTE, :INTT,:DEBIT , :CREDIT, :SOLDE
DO
Begin
ANVD=NULL;
ANVC=NULL;
if ((substr(:compte,1,1)='6') or (substr(:compte,1,1)='7')) then
begin
Select Debit, Credit from BALA WHERE compte=:Compte
into :ANVD,:ANVC;
end
else
begin
Select Debit, Credit from MOUV WHERE collectif=:Compte
AND JOURNAL='ANV'
into :ANVD,:ANVC;
end
if (ANVD IS NULL) then ANVD=0;
if (ANVC IS NULL) then ANVC=0;
ANV=:ANVD-:ANVC;
if ((substr(:compte,1,1)=6) or (substr(:compte,1,1)=7)) then
Begin
SDEBIT=:DEBIT;
SCREDIT=:CREDIT;
SSOLDE=:SOLDE;
end
else
begin
SDEBIT=:ANVD+:DEBIT;
SCREDIT=:ANVC+:CREDIT;
SSOLDE=:ANV+:SOLDE;
end
FCLE=1;
CPT3=SUBSTR(:COMPTE,1,3);
CPT4=SUBSTR(:COMPTE,1,4);
CPT5=SUBSTR(:COMPTE,1,5);
CPT6=SUBSTR(:COMPTE,1,6);
CPT7=SUBSTR(:COMPTE,1,7);
SUSPEND;
end
FOR
select
m.compte,
r.intitule,
m.debit,
m.credit,
m.debit-m.credit
from BALA M
INNER JOIN compte r ON ( M.coMPTE=R.compte)
where COMPTE NOT IN (SELECT DISTINCT COLLECTIF FROM MOUV WHERE JOURNAL<>'ANV' AND JOURNAL<>'ANF' AND JOURNAL<>'ANC' )
into :COMPTE, :INTT, :ANVD , :ANVC, :ANV
DO
Begin
DEBIT=0;
CREDIT=0;
SOLDE=0;
if (substr(:compte,1,4)='1181') then
Begin
select
m.debit,
m.credit,
m.debit-m.credit
from mouv M
INNER JOIN compte r ON ( M.coMPTE=R.compte)
where JOURNAL='ANV' AND COMPTE=:COMPTE
into :ANVD , :ANVC, :ANV;
end
SDEBIT=:ANVD+:DEBIT;
SCREDIT=:ANVC+:CREDIT;
SSOLDE=:ANV+:SOLDE;
if (substr(:compte,1,4)='1181') then
Begin
select
m.debit,
m.credit,
m.debit-m.credit
from BALA M
INNER JOIN compte r ON ( M.coMPTE=R.compte)
where COMPTE=:COMPTE
into :ANVD , :ANVC, :ANV;
end
FCLE=0;
CPT3=SUBSTR(:COMPTE,1,3);
CPT4=SUBSTR(:COMPTE,1,4);
CPT5=SUBSTR(:COMPTE,1,5);
CPT6=SUBSTR(:COMPTE,1,6);
CPT7=SUBSTR(:COMPTE,1,7);
SUSPEND;
end
end |
Partager