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 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149
| CREATE OR REPLACE PROCEDURE IHR.PROCEDURE_INSERT_INTO_ZYAG_Q IS
CURSOR c_zyag_all IS
SELECT *
FROM IHR.HRA_ZYAG A
ORDER BY ZY00_SOCCLE ASC, ZY00_MATCLE ASC, ZYAG_NULIGN ASC, ZYAG_DATDEB ASC;
CUR_ZYAG_ALL c_zyag_all%ROWTYPE;
CURSOR c_zyag_q_all IS
SELECT *
FROM IHR.HRA_ZYAG_Q A
ORDER BY ZY00_SOCCLE ASC, ZY00_MATCLE ASC, ZYAG_NULIGN ASC, ZYAG_DATDEB ASC;
CUR_ZYAG_Q_ALL c_zyag_q_all%ROWTYPE;
CURSOR c_zyag IS
SELECT *
FROM IHR.HRA_ZYAG A
WHERE A.ZYAG_PROLON IS NULL OR A.ZYAG_PROLON = ''
ORDER BY ZY00_SOCCLE ASC, ZY00_MATCLE ASC, ZYAG_NULIGN ASC, ZYAG_DATDEB ASC;
CUR_ZYAG c_zyag%ROWTYPE;
CURSOR c_zyag2 IS
SELECT *
FROM IHR.HRA_ZYAG A
WHERE A.ZYAG_PROLON = 'X'
ORDER BY ZY00_SOCCLE ASC, ZY00_MATCLE ASC, ZYAG_NULIGN ASC, ZYAG_DATDEB ASC;
CUR_ZYAG2 c_zyag2%ROWTYPE;
BEGIN
--mise à jour des témoins de prolongation oubliés
FOR CUR_ZYAG_ALL IN c_zyag_all
LOOP
UPDATE IHR.HRA_ZYAG A SET A.ZYAG_PROLON = 'X'
WHERE A.ZY00_SOCCLE = CUR_ZYAG_ALL.ZY00_SOCCLE
AND A.ZY00_MATCLE = CUR_ZYAG_ALL.ZY00_MATCLE
AND A.ZYAG_MOTIFA = CUR_ZYAG_ALL.ZYAG_MOTIFA
AND A.ZYAG_DATDEB = CUR_ZYAG_ALL.ZYAG_DATFIN + 1
AND (A.ZYAG_PROLON IS NULL OR A.ZYAG_PROLON = '');
COMMIT;
END LOOP;
--insertion des enregistrements débutant chaque période d'abscence
FOR CUR_ZYAG IN c_zyag
LOOP
INSERT INTO IHR.HRA_ZYAG_Q
(ZY00_SOCCLE,
ZY00_MATCLE,
ZY00_NUDOSS,
ZY00_SOCDOS,
ZD00_CDRG03,
ZD00_CDRG04,
ZYAG_NULIGN,
ZYAG_DATDEB,
ZYAG_MOTIFA,
ZYAG_MOTIFA_S,
ZYAG_DATFIN,
ZYAG_TEMDEB,
ZYAG_TEMFIN,
ZYAG_GESTIO,
ZYAG_PROLON,
ZYAG_NBRPRO,
ZYAG_DTABS01,
ZYAG_MOTAB1,
ZYAG_UNITE1,
ZYAG_TEMTR1,
ZYAG_UNITE2,
ZYAG_TEMTR2,
ZYAG_ELEMAT,
ZYAG_ELEMAT_S,
ZYAG_DATUT1,
ZYAG_DATUT2,
ZYAG_SUITUT,
ZYAG_IDRTST,
ZYAG_IDRTST_S)
VALUES (CUR_ZYAG.ZY00_SOCCLE,
CUR_ZYAG.ZY00_MATCLE,
CUR_ZYAG.ZY00_NUDOSS,
CUR_ZYAG.ZY00_SOCDOS,
CUR_ZYAG.ZD00_CDRG03,
CUR_ZYAG.ZD00_CDRG04,
CUR_ZYAG.ZYAG_NULIGN,
CUR_ZYAG.ZYAG_DATDEB,
CUR_ZYAG.ZYAG_MOTIFA,
CUR_ZYAG.ZYAG_MOTIFA_S,
CUR_ZYAG.ZYAG_DATFIN,
CUR_ZYAG.ZYAG_TEMDEB,
CUR_ZYAG.ZYAG_TEMFIN,
CUR_ZYAG.ZYAG_GESTIO,
CUR_ZYAG.ZYAG_PROLON,
0,
null,
null,
CUR_ZYAG.ZYAG_UNITE1,
CUR_ZYAG.ZYAG_TEMTR1,
CUR_ZYAG.ZYAG_UNITE2,
CUR_ZYAG.ZYAG_TEMTR2,
CUR_ZYAG.ZYAG_ELEMAT,
CUR_ZYAG.ZYAG_ELEMAT_S,
CUR_ZYAG.ZYAG_DATUT1,
CUR_ZYAG.ZYAG_DATUT2,
CUR_ZYAG.ZYAG_SUITUT,
CUR_ZYAG.ZYAG_IDRTST,
CUR_ZYAG.ZYAG_IDRTST_S);
COMMIT;
END LOOP;
--mise à jour des dates de fin d'abscence et du nombre de prolongation selon les témoins de prolongation
FOR CUR_ZYAG2 IN c_zyag2
LOOP
UPDATE IHR.HRA_ZYAG_Q SET ZYAG_DATFIN = CUR_ZYAG2.ZYAG_DATFIN , ZYAG_PROLON = ZYAG_PROLON + 1
WHERE ZY00_SOCCLE = CUR_ZYAG2.ZY00_SOCCLE
AND ZY00_MATCLE = CUR_ZYAG2.ZY00_MATCLE
AND ZYAG_MOTIFA = CUR_ZYAG2.ZYAG_MOTIFA
AND ZYAG_DATDEB = CUR_ZYAG2.ZYAG_DATFIN + 1;
COMMIT;
END LOOP;
--mise à jour des dates de début contigue lors des abscences consécutives avec un motif différent
FOR CUR_ZYAG_Q_ALL IN c_zyag_q_all
LOOP
IF CUR_ZYAG_Q_ALL.ZYAG_DTABS01 IS NULL OR CUR_ZYAG_Q_ALL.ZYAG_DTABS01 = '' THEN
UPDATE IHR.HRA_ZYAG_Q A SET A.ZYAG_DTABS01 = CUR_ZYAG_Q_ALL.ZYAG_DATDEB , A.ZYAG_MOTAB1 = CUR_ZYAG_Q_ALL.ZYAG_MOTAB1
WHERE A.ZY00_SOCCLE = CUR_ZYAG_Q_ALL.ZY00_SOCCLE
AND A.ZY00_MATCLE = CUR_ZYAG_Q_ALL.ZY00_MATCLE
AND A.ZYAG_DATDEB = (CUR_ZYAG_Q_ALL.ZYAG_DATFIN + 1);
COMMIT;
ELSE
UPDATE IHR.HRA_ZYAG_Q A SET A.ZYAG_DTABS01 = CUR_ZYAG_Q_ALL.ZYAG_DTABS01 , A.ZYAG_MOTAB1 = CUR_ZYAG_Q_ALL.ZYAG_MOTAB1
WHERE A.ZY00_SOCCLE = CUR_ZYAG_Q_ALL.ZY00_SOCCLE
AND A.ZY00_MATCLE = CUR_ZYAG_Q_ALL.ZY00_MATCLE
AND A.ZYAG_DATDEB = CUR_ZYAG_Q_ALL.ZYAG_DATFIN + 1;
COMMIT;
END IF;
END LOOP;
END; |
Partager