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
| CREATE OR REPLACE PROCEDURE SP_Absence_employe(matricule IN varchar2, date_deb IN date, date_fin IN date,
p_cursor IN OUT TYPES.cursortype)
IS
cursor c1 (matr varchar2, debut date, fin date) IS
select t2.chp1, t2.chp2, t1.chp3, t3.chp4, sum(t1.chp5/3600) hres
from ((t2 inner join t1 on t2.id = t1.id)
inner join t3 on t1.codeid = t3.codeid)
where (t2.chp1 = matr) and
(t1.date between debut and fin) and
(t1.codeid in(302,703,1104,2502,2503))
group by t2.chp1, t2.chp2, t1.chp3, t3.chp4
order by t1.date;
v_matr varchar2(15);
v_nom varchar2(64);
v_deb date;
v_date date;
v_fin date;
v_code varchar2(50);
v_hres number(16,6);
b4_matr varchar2(15);
b4_nom varchar(64);
b4_date date;
b4_code varchar2(50);
tothr number(16,6);
BEGIN
OPEN c1(matricule, date_deb, date_fin);
FETCH c1
INTO v_matr, v_nom, v_date, v_code, v_hres;
b4_matr := v_matr;
b4_date := v_date;
v_deb := v_date;
b4_code := v_code;
b4_nom := v_nom;
tothr := 0;
LOOP
IF v_matr = b4_matr and
v_code = b4_code then
IF (v_date - b4_date) <= 3 then
tothr := tothr + v_hres;
ELSE
INSERT INTO tx_absence
(MATRICULE, NOM, DATE_DEBUT, DATE_FIN, CODE_PAIE, HEURES)
VALUES
(b4_matr, b4_nom, v_deb, b4_date, b4_code, tothr);
v_deb := v_date;
tothr := v_hres;
END IF;
ELSE
INSERT INTO tx_absence
(MATRICULE, NOM, DATE_DEBUT, DATE_FIN, CODE_PAIE, HEURES)
VALUES
(b4_matr, v_nom, v_deb, b4_date, b4_code, tothr);
v_deb := v_date;
tothr := v_hres;
END IF;
b4_matr := v_matr;
b4_date := v_date;
b4_code := v_code;
b4_nom := v_nom;
FETCH c1
INTO v_matr, v_nom, v_date, v_code, v_hres;
EXIT WHEN c1%NOTFOUND;
END LOOP;
INSERT INTO tx_absence
(MATRICULE, NOM, DATE_DEBUT, DATE_FIN, CODE_PAIE, HEURES)
VALUES
(b4_matr, v_nom, v_deb, b4_date, b4_code, tothr);
CLOSE c1;
OPEN p_cursor FOR
SELECT MATRICULE, NOM, DATE_DEBUT, DATE_FIN, CODE_PAIE, HEURES
FROM TX_ABSENCE;
END SP_Absence_employe; |
Partager