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 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238
|
PROCEDURE m4sph_dadsu_s41_g02_00(organization IN VARCHAR2,id_hr IN VARCHAR2,datedeb IN DATE,datefin IN DATE, dt_aa_exe IN NUMBER, num_sec_soc IN VARCHAR2) IS
--Si 1, on continue ; sinon, on stoppe tout :
booleen VARCHAR(1);
separateur VARCHAR(1):=NULL;
periode_ignoree number;
id_sub_leg_ent varchar2(9):=organization;
dt_deb_per_act DATE;
nom_vendredi varchar2(50);
dernier_vendredi date;
date_fin_recherche date;
mon_debper DATE;
mon_finper DATE;
max_slice DATE;
statut m4sco_ac_hr_perio4.sph_cod_statut%TYPE;
grade m4sco_ac_hr_perio4.sph_cod_grad_paie%TYPE;
pos_statut m4sph_h_hr_pos_sta.sph_id_cposta%TYPE;
modrec m4sco_ac_hr_perio6.SPH_COD_MOD_RECR%TYPE;
s41_g02_00_008 varchar(2);
s41_g02_00_009 varchar(2):='01';
s41_g02_00_010 varchar(2):='04';
BEGIN
---- dbms_output.enable();
--BEGIN
--Récupération du nom du vendredi dans la langue installée sur le serveur
--select TO_CHAR (TO_DATE('01-07-2005', 'DD-MM-YYYY'),'DAY')
--INTO nom_vendredi
--from dual;
execute immediate 'SELECT trim(TO_CHAR (TO_DATE(''01-07-2005'', ''DD-MM-YYYY''),''DAY''))' into nom_vendredi;
-- --Recherche du dernier vendredi de l'année
-- SELECT next_day(to_date('2412'||to_char(dt_aa_exe),'DDMMYYYY'),nom_vendredi)
-- INTO dernier_vendredi
-- FROM DUAL;
-- --EXCEPTION
-- -- WHEN OTHERS THEN
-- dernier_vendredi:=to_date('30122005','DDMMYYYY');
-- END;
execute immediate 'SELECT next_day(to_date(''2412''||to_char('||to_char(dt_aa_exe)||'),''DDMMYYYY''),'''||nom_vendredi||''')' into dernier_vendredi;
SELECT max(debper), max (finper)
INTO mon_debper,mon_finper
FROM m4sph_tfa_per_dads
WHERE sph_dt_aa_exe=dt_aa_exe
AND id_organization=organization
AND sco_id_hr=id_hr
AND sph_num_sec_soc=num_sec_soc;
BEGIN
SELECT sfr_id_s_leg_ent_d
INTO id_sub_leg_ent
FROM m4sph_tfa_par_dads
WHERE sph_dt_aa_exe=dt_aa_exe
AND id_organization=organization
AND std_id_leg_ent=organization
AND sfr_id_s_leg_ent=
( select sfr_id_sub_leg_ent
from m4sco_ac_hr_period A
where id_organization=organization
AND sco_id_hr=id_hr
AND sco_or_hr_period=1
AND sco_dt_alloc=to_date('26'||to_char(sco_dt_start_slice,'MMYYYY'),'DDMMYYYY')
AND sco_pay_freq_pay='004'
AND sco_pay_freq_alloc='004'
AND id_currency='EUR'
AND sco_dt_start_slice=mon_debper
AND sco_dt_pay=
(
SELECT max(sco_dt_pay)
FROM m4sco_ac_hr_period B
WHERE
id_organization=organization AND
sco_id_hr=id_hr
and sco_or_hr_period=1
AND B.sco_pay_freq_pay='004'
AND B.sco_pay_freq_alloc='004'
AND B.id_currency='EUR'
AND a.sco_dt_alloc=B.sco_dt_alloc
AND A.sco_dt_start_slice=B.sco_dt_start_slice
AND sco_dt_pay<=datefin
AND sco_dt_pay>=datedeb
)
);
EXCEPTION
WHEN no_data_found THEN
periode_ignoree:=1;
END;
IF periode_ignoree=0 THEN
dt_deb_per_act:=mon_debper;
IF mon_finper<dernier_vendredi THEN
date_fin_recherche:=mon_finper;
s41_g02_00_008:='02';
ELSE
date_fin_recherche:=dernier_vendredi;
s41_g02_00_008:='01';
END IF;
SELECT max(sco_dt_start_slice)
INTO max_slice
FROM m4sco_ac_hr_perio4 C
WHERE sco_id_hr=id_hr
and id_organization=organization
and sco_or_hr_period=1
AND sco_pay_freq_pay='004'
AND sco_pay_freq_alloc='004'
AND id_currency='EUR'
AND sco_dt_alloc=to_date('26'||to_char(sco_dt_start_slice,'MMYYYY'),'DDMMYYYY')
AND sco_dt_start_slice<=date_fin_recherche
AND sco_dt_pay =(SELECT max (sco_dt_pay)
FROM m4sco_ac_hr_perio4 B
WHERE sco_id_hr=C.sco_id_hr
and id_organization=organization
and sco_or_hr_period=C.sco_or_hr_period
AND sco_pay_freq_pay=C.sco_pay_freq_pay
AND sco_pay_freq_alloc=C.sco_pay_freq_alloc
AND id_currency=C.id_currency
and sco_dt_pay >=datedeb
and sco_dt_pay<datefin
AND sco_dt_alloc=to_date('26'||to_char(sco_dt_start_slice,'MMYYYY'),'DDMMYYYY')
And C.sco_dt_start_slice=B.sco_dt_start_slice);
SELECT distinct sph_cod_statut, sph_cod_grad_paie
INTO statut, grade
FROM m4sco_ac_hr_perio4 A
WHERE sco_id_hr=id_hr
and id_organization=organization
and sco_or_hr_period=1
AND sco_pay_freq_pay='004'
AND sco_pay_freq_alloc='004'
AND id_currency='EUR'
AND sco_dt_alloc=to_date('26'||to_char(sco_dt_start_slice,'MMYYYY'),'DDMMYYYY')
AND sco_dt_start_slice=max_slice
AND sco_dt_pay=(SELECT max (sco_dt_pay)
FROM m4sco_ac_hr_perio4 B
WHERE sco_id_hr=A.sco_id_hr
and id_organization=organization
and sco_or_hr_period=A.sco_or_hr_period
AND sco_pay_freq_pay='004'
AND sco_pay_freq_alloc='004'
AND id_currency='EUR'
and sco_dt_pay >=datedeb
and sco_dt_pay<datefin
AND sco_dt_alloc=to_date('26'||to_char(sco_dt_start_slice,'MMYYYY'),'DDMMYYYY')
And A.sco_dt_start_slice=B.sco_dt_start_slice);
BEGIN
SELECT sph_id_cposta
INTO pos_statut
FROM m4sph_h_hr_pos_sta
WHERE std_id_hr=id_hr
AND std_or_hr_period=1
AND dt_start <=date_fin_recherche
AND dt_end >= date_fin_recherche
AND id_organization=organization;
EXCEPTION
WHEN no_data_found THEN
pos_statut:=NULL;
END;
IF statut IN('28','29','86','87','88','89', '8A','8B') OR pos_statut='H8' THEN
--
-- IF max_slice>=dernier_vendredi THEN
-- S41_g02_00_008:='01';
-- ELSE
-- S41_g02_00_008:='02';
-- END IF;
INSERT INTO m4sph_s41_g02_00 (
sph_dt_aa_exe,
id_organization,
sfr_id_sub_leg_ent,
sco_id_hr,
sph_num_sec_soc,
sph_dt_deb_per_act,
s41_g02_00_008,
s41_g02_00_009,
s41_g02_00_010,
id_approle,
id_secuser,
dt_last_update
)
VALUES
(
dt_aa_exe,
organization,
id_sub_leg_ent,
id_hr,
num_sec_soc,
dt_deb_per_act,
s41_g02_00_008,
s41_g02_00_009,
s41_g02_00_010,
'ADMMCKPH',
'ADMMCKPH',
sysdate
);
COMMIT;
END IF;
END IF;
END m4sph_dadsu_s41_g02_00; |
Partager