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 replace procedure proc_pays_maj AS
datemois CHAR(6);
code Article.ARKTCODART%TYPE;
codecomp Article.ARKTCOMART%TYPE;
pays Client.CLCTCPAYS%TYPE;
somme Integer;
CURSOR C1 IS select TO_CHAR(TO_DATE(ECCJCRE,'yyyymmdd'),'mmyyyy'),
LCCTCODART, LCCTCOMART, ECCTCPAYS, SUM(LCCNQTEEXP)
from EEXPCLI,LEXPCLI
where EEXPCLI.ECKTSOC=LEXPCLI.LCKTSOC
and EEXPCLI.ECKTNUMERO=LEXPCLI.LCKTNUMERO
and EEXPCLI.ECKTINDICE=LEXPCLI.LCKTPSF
and ECKTSOC='110'
and ECCJCRE>=to_char(sysdate,'yyyymm')-1 || '01'
and ECCJCRE<=to_char(sysdate,'yyyymm')-1 || '31'
group by TO_CHAR(TO_DATE(ECCJCRE,'yyyymmdd'),'mmyyyy'),LCCTCODART, LCCTCOMART,ECCTCPAYS;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO datemois,code,codecomp,pays,somme;
EXIT WHEN C1%NOTFOUND;
INSERT INTO STATPAYS Values(datemois,code,codecomp,pays,somme);
END LOOP;
CLOSE C1;
commit;
end;
/ |
Partager