[Home] [Help]
PACKAGE BODY: APPS.BEN_PPF_LER
Source
1 package body ben_ppf_ler as
2 /* $Header: beppftrg.pkb 120.0 2005/05/28 10:58:08 appldev noship $*/
3 procedure ler_chk(p_old IN g_ppf_ler_rec
4 ,p_new IN g_ppf_ler_rec
5 ,p_effective_date in date default null
6 ) is
7 --
8 l_session_date DATE;
9 l_system_date DATE;
10 --
11 cursor get_session_date IS
12 select effective_date
13 from fnd_sessions
14 where session_id = userenv('SESSIONID');
15 --
16 cursor get_system_date IS
17 select trunc(sysdate)
18 from dual;
19 --
20 cursor get_ler(l_status varchar2) is
21 select ler.ler_id
22 , ler.typ_cd
23 , ler.ocrd_dt_det_cd
24 from ben_ler_f ler
25 where ler.business_group_id = p_new.business_group_id
26 --3289596
27 and l_session_date between ler.effective_start_date
28 and ler.effective_end_date
29 and ( l_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
30 and ( (exists
31 (select 1
32 from ben_per_info_chg_cs_ler_f psl
33 , ben_ler_per_info_cs_ler_f lpl
34 where source_table = 'PER_ALL_PEOPLE_F'
35 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
36 and lpl.business_group_id = psl.business_group_id
37 and lpl.business_group_id = ler.business_group_id
38 and l_session_date between psl.effective_start_date
39 and psl.effective_end_date
40 and l_session_date between lpl.effective_start_date
41 and lpl.effective_end_date
42 and lpl.ler_id = ler.ler_id)
43 )
44 OR (exists
45 (select 1
46 from ben_rltd_per_chg_cs_ler_f rpc
47 , ben_ler_rltd_per_cs_ler_f lrp
48 where source_table = 'PER_ALL_PEOPLE_F'
49 and lrp.business_group_id = rpc.business_group_id
50 and lrp.business_group_id = ler.business_group_id
51 and l_session_date between rpc.effective_start_date
52 and rpc.effective_end_date
53 and l_session_date between lrp.effective_start_date
54 and lrp.effective_end_date
55 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
56 and lrp.ler_id = ler.ler_id)
57 )
58 )
59 order by ler.ler_id;
60
61 --
62 cursor get_ler_col(p_ler_id IN NUMBER) is
63 select psl.source_column
64 , psl.new_val
65 , psl.old_val
66 , 'P', psl.per_info_chg_cs_ler_rl, psl.rule_overrides_flag, lpl.chg_mandatory_cd
67 from ben_ler_per_info_cs_ler_f lpl
68 , ben_per_info_chg_cs_ler_f psl
69 where lpl.ler_id = p_ler_id
70 and lpl.business_group_id = p_new.business_group_id
71 and lpl.business_group_id = psl.business_group_id
72 and l_session_date between
73 psl.effective_start_date
74 and psl.effective_end_date
75 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
76 and source_table = 'PER_ALL_PEOPLE_F'
77 and l_session_date between psl.effective_start_date
78 and psl.effective_end_date
79 and l_session_date between lpl.effective_start_date
80 and lpl.effective_end_date
81 UNION
82 select rpc.source_column
83 , rpc.new_val
84 , rpc.old_val
85 , 'R', rpc.rltd_per_chg_cs_ler_rl per_info_chg_cs_ler, rpc.rule_overrides_flag, lrp.chg_mandatory_cd
86 from ben_ler_rltd_per_cs_ler_f lrp
87 , ben_rltd_per_chg_cs_ler_f rpc
88 where lrp.ler_id = p_ler_id
89 and lrp.business_group_id = p_new.business_group_id
90 and lrp.business_group_id = rpc.business_group_id
91 and l_session_date between
92 rpc.effective_start_date
93 and rpc.effective_end_date
94 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
95 and source_table = 'PER_ALL_PEOPLE_F'
96 and l_session_date between rpc.effective_start_date
97 and rpc.effective_end_date
98 and l_session_date between lrp.effective_start_date
99 and lrp.effective_end_date
100 order by 1;
101 --
102 cursor le_exists(p_person_id in number
103 ,p_ler_id in number
104 ,p_lf_evt_ocrd_dt in date) is
105 select 'Y'
106 from ben_ptnl_ler_for_per
107 where person_id = p_person_id
108 and ler_id = p_ler_id
109 and ptnl_ler_for_per_stat_cd = 'DTCTD'
110 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
111 --
112 cursor get_contacts(p_person_id in number) is
113 select person_id
114 from per_contact_relationships
115 where contact_person_id = p_person_id
116 and business_group_id = p_new.business_group_id
117 and l_session_date between nvl(date_start,l_session_date)
118 and nvl(date_end,l_session_date)
119 and personal_flag = 'Y'
120 order by person_id;
121 --
122 l_changed BOOLEAN;
123 l_effective_end_date DATE := to_date('31/12/4712','DD/MM/YYYY');
124 l_effective_start_date DATE;
125 l_lf_evt_ocrd_date DATE;
126 l_mnl_dt DATE;
127 l_dtctd_dt DATE;
128 l_procd_dt DATE;
129 l_unprocd_dt DATE;
130 l_voidd_dt DATE;
131 l_ntfn_dt DATE;
132 l_ler_id NUMBER;
133 l_typ_cd ben_ler_f.typ_cd%Type ;
134 l_ovn NUMBER;
135 l_ptnl_id NUMBER;
136 l_hld_person_id NUMBER;
137 l_ocrd_dt_cd VARCHAR2(30);
138 l_column ben_rltd_per_chg_cs_ler_f.source_column%type; -- VARCHAR2(30);
139 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type; -- VARCHAR2(30);
140 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type; -- VARCHAR2(30);
141 l_per_info_chg_cs_ler_rl number;
142 l_rule_output VARCHAR2(1);
143 l_type VARCHAR2(1);
144 l_le_exists VARCHAR2(1);
145 --
146 l_bool BOOLEAN;
147 l_status VARCHAR2(1);
148 l_industry VARCHAR2(1);
149 --
150 l_col_new_val varchar2(1000);
151 l_col_old_val varchar2(1000);
152 --
153 l_rule_overrides_flag VARCHAR2(1);
154 l_chg_mandatory_cd VARCHAR2(1);
155 l_trigger boolean := TRUE;
156 --
157 begin
158 -- Bug 3320133
159 benutils.set_data_migrator_mode;
160 if hr_general.g_data_migrator_mode in ( 'Y','P') then
161 --
162 return;
163 --
164 end if;
165 --
166 -- End of Bug 3320133
167
168 l_bool :=fnd_installation.get(appl_id => 805
169 ,dep_appl_id =>805
170 ,status => l_status
171 ,industry => l_industry);
172
173 hr_utility.set_location(' Entering: ben_ppf_trigger', 10);
174 l_changed := FALSE;
175 --bug 2215549
176 if p_effective_date is not null then
177 --
178 l_session_date := p_effective_date ;
179 --
180 else
181 --
182 open get_session_date;
183 fetch get_session_date into l_session_date;
184 close get_session_date;
185 --
186 end if;
187 --
188 l_effective_start_date := l_session_date;
189
190 open get_system_date;
191 fetch get_system_date into l_system_date;
192 close get_system_date;
193
194 hr_utility.set_location(' ben_ppf_trigger', 20);
195 hr_utility.set_location('sess '||l_session_date, 20);
196 hr_utility.set_location('PERSON '||p_new.person_id, 20);
197 hr_utility.set_location('BG '||p_new.business_group_id, 20);
198 open get_ler(l_status);
199 loop
200 fetch get_ler into l_ler_id,l_typ_cd,l_ocrd_dt_cd;
201 exit when get_ler%notfound;
202 l_trigger := TRUE;
203 --
204 open get_ler_col(l_ler_id);
205 loop
206 fetch get_ler_col into l_column,l_new_val, l_old_val, l_type, l_per_info_chg_cs_ler_rl, l_rule_overrides_flag, l_chg_mandatory_cd;
207 exit when get_ler_col%NOTFOUND;
208
209 hr_utility.set_location('LER '||l_ler_id, 20);
210 hr_utility.set_location('COLUMN '||l_column, 20);
211 hr_utility.set_location('NEWVAL '||l_new_val, 20);
212 hr_utility.set_location('OLDVAL '||l_old_val, 20);
213 hr_utility.set_location('TYPE '||l_type, 20);
214 hr_utility.set_location(' l_per_info_chg_cs_ler_rl '|| l_per_info_chg_cs_ler_rl, 20);
215 hr_utility.set_location('l_rule_overrides_flag '||l_rule_overrides_flag, 20);
216 hr_utility.set_location('l_chg_mandatory_cd '||l_chg_mandatory_cd, 20);
217
218
219 l_changed := TRUE;
220 if get_ler_col%ROWCOUNT = 1 then
221 hr_utility.set_location('rowcount 1 ', 20);
222 l_changed := TRUE;
223 end if;
224
225 hr_utility.set_location(' ben_ppf_trigger', 50);
226 -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
227 -- If it returns Y, then see the applicability of the data
228 -- changes based on new and old values.
229 --
230 l_rule_output := 'Y';
231 --
232 if l_per_info_chg_cs_ler_rl is not null then
233 --
234 if l_column = 'EFFECTIVE_START_DATE' then
235 l_col_old_val := to_char(p_old.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS');
236 l_col_new_val := to_char(p_new.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS');
237 end if;
238 --
239 if l_column = 'EFFECTIVE_END_DATE' then
240 l_col_old_val := to_char(p_old.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS');
241 l_col_new_val := to_char(p_new.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS');
242 end if;
243 --
244 if l_column = 'DATE_OF_BIRTH' then
245 l_col_old_val := to_char(p_old.DATE_OF_BIRTH, 'YYYY/MM/DD HH24:MI:SS');
246 l_col_new_val := to_char(p_new.DATE_OF_BIRTH, 'YYYY/MM/DD HH24:MI:SS');
247 end if;
248 --
249 if l_column = 'DATE_OF_DEATH' then
250 l_col_old_val := to_char(p_old.DATE_OF_DEATH, 'YYYY/MM/DD HH24:MI:SS');
251 l_col_new_val := to_char(p_new.DATE_OF_DEATH, 'YYYY/MM/DD HH24:MI:SS');
252 end if;
253 --
254 if l_column = 'ON_MILITARY_SERVICE' then
255 l_col_old_val := p_old.ON_MILITARY_SERVICE;
256 l_col_new_val := p_new.ON_MILITARY_SERVICE;
257 end if;
258 --
259 if l_column = 'MARITAL_STATUS' then
260 l_col_old_val := p_old.MARITAL_STATUS;
261 l_col_new_val := p_new.MARITAL_STATUS;
262 end if;
263 --
264 if l_column = 'REGISTERED_DISABLED_FLAG' then
265 l_col_old_val := p_old.REGISTERED_DISABLED_FLAG;
266 l_col_new_val := p_new.REGISTERED_DISABLED_FLAG;
267 end if;
268 --
269 if l_column = 'SEX' then
270 l_col_old_val := p_old.SEX;
271 l_col_new_val := p_new.SEX;
272 end if;
273 --
274
275 if l_column = 'STUDENT_STATUS' then
276 l_col_old_val := p_old.STUDENT_STATUS;
277 l_col_new_val := p_new.STUDENT_STATUS;
278 end if;
279 --
280 if l_column = 'COORD_BEN_NO_CVG_FLAG' then
281 l_col_old_val := p_old.COORD_BEN_NO_CVG_FLAG;
282 l_col_new_val := p_new.COORD_BEN_NO_CVG_FLAG;
283 end if;
284 --
285 if l_column = 'USES_TOBACCO_FLAG' then
286 l_col_old_val := p_old.USES_TOBACCO_FLAG;
287 l_col_new_val := p_new.USES_TOBACCO_FLAG;
288 end if;
289 --
290 if l_column = 'COORD_BEN_MED_PLN_NO' then
291 l_col_old_val := p_old.COORD_BEN_MED_PLN_NO;
292 l_col_new_val := p_new.COORD_BEN_MED_PLN_NO;
293 end if;
294 --
295 if l_column = 'PER_INFORMATION10' then
296 l_col_old_val := p_old.PER_INFORMATION10;
297 l_col_new_val := p_new.PER_INFORMATION10;
298 end if;
299 --
300 if l_column = 'DPDNT_VLNTRY_SVCE_FLAG' then
301 l_col_old_val := p_old.DPDNT_VLNTRY_SVCE_FLAG;
302 l_col_new_val := p_new.DPDNT_VLNTRY_SVCE_FLAG;
303 end if;
304 --
305 if l_column = 'RECEIPT_OF_DEATH_CERT_DATE' then
306 l_col_old_val := to_char(p_old.RECEIPT_OF_DEATH_CERT_DATE, 'YYYY/MM/DD HH24:MI:SS');
307 l_col_new_val := to_char(p_new.RECEIPT_OF_DEATH_CERT_DATE, 'YYYY/MM/DD HH24:MI:SS');
308 end if;
309 if l_column = 'ATTRIBUTE1' then
310 l_col_old_val := p_old.ATTRIBUTE1;
311 l_col_new_val := p_new.ATTRIBUTE1;
312 end if;
313 --
314 if l_column = 'ATTRIBUTE2' then
315 l_col_old_val := p_old.ATTRIBUTE2;
316 l_col_new_val := p_new.ATTRIBUTE2;
317 end if;
318 --
319 if l_column = 'ATTRIBUTE3' then
320 l_col_old_val := p_old.ATTRIBUTE3;
321 l_col_new_val := p_new.ATTRIBUTE3;
322 end if;
323 --
324 if l_column = 'ATTRIBUTE4' then
325 l_col_old_val := p_old.ATTRIBUTE4;
326 l_col_new_val := p_new.ATTRIBUTE4;
327 end if;
328 --
329 if l_column = 'ATTRIBUTE5' then
330 l_col_old_val := p_old.ATTRIBUTE5;
331 l_col_new_val := p_new.ATTRIBUTE5;
332 end if;
333 --
334 if l_column = 'ATTRIBUTE6' then
335 l_col_old_val := p_old.ATTRIBUTE6;
336 l_col_new_val := p_new.ATTRIBUTE6;
337 end if;
338 --
339 if l_column = 'ATTRIBUTE7' then
340 l_col_old_val := p_old.ATTRIBUTE7;
341 l_col_new_val := p_new.ATTRIBUTE7;
342 end if;
343 --
344 if l_column = 'ATTRIBUTE8' then
345 l_col_old_val := p_old.ATTRIBUTE8;
346 l_col_new_val := p_new.ATTRIBUTE8;
347 end if;
348 --
349 if l_column = 'ATTRIBUTE9' then
350 l_col_old_val := p_old.ATTRIBUTE9;
351 l_col_new_val := p_new.ATTRIBUTE9;
352 end if;
353 --
354 if l_column = 'ATTRIBUTE10' then
355 l_col_old_val := p_old.ATTRIBUTE10;
356 l_col_new_val := p_new.ATTRIBUTE10;
357 end if;
358 --
359 if l_column = 'ATTRIBUTE11' then
360 l_col_old_val := p_old.ATTRIBUTE11;
361 l_col_new_val := p_new.ATTRIBUTE11;
362 end if;
363 --
364 if l_column = 'ATTRIBUTE12' then
365 l_col_old_val := p_old.ATTRIBUTE12;
366 l_col_new_val := p_new.ATTRIBUTE12;
367 end if;
368 --
369 if l_column = 'ATTRIBUTE13' then
370 l_col_old_val := p_old.ATTRIBUTE13;
371 l_col_new_val := p_new.ATTRIBUTE13;
372 end if;
373 --
374 if l_column = 'ATTRIBUTE14' then
375 l_col_old_val := p_old.ATTRIBUTE14;
376 l_col_new_val := p_new.ATTRIBUTE14;
377 end if;
378 --
379 if l_column = 'ATTRIBUTE15' then
380 l_col_old_val := p_old.ATTRIBUTE15;
381 l_col_new_val := p_new.ATTRIBUTE15;
382 end if;
383 --
384 if l_column = 'ATTRIBUTE16' then
385 l_col_old_val := p_old.ATTRIBUTE16;
386 l_col_new_val := p_new.ATTRIBUTE16;
387 end if;
388 --
389 if l_column = 'ATTRIBUTE17' then
390 l_col_old_val := p_old.ATTRIBUTE17;
391 l_col_new_val := p_new.ATTRIBUTE17;
392 end if;
393 --
394 if l_column = 'ATTRIBUTE18' then
395 l_col_old_val := p_old.ATTRIBUTE18;
396 l_col_new_val := p_new.ATTRIBUTE18;
397 end if;
398 --
399 if l_column = 'ATTRIBUTE19' then
400 l_col_old_val := p_old.ATTRIBUTE19;
401 l_col_new_val := p_new.ATTRIBUTE19;
402 end if;
403 --
404 if l_column = 'ATTRIBUTE20' then
405 l_col_old_val := p_old.ATTRIBUTE20;
406 l_col_new_val := p_new.ATTRIBUTE20;
407 end if;
408 --
409 if l_column = 'ATTRIBUTE21' then
410 l_col_old_val := p_old.ATTRIBUTE21;
411 l_col_new_val := p_new.ATTRIBUTE21;
412 end if;
413 --
414 if l_column = 'ATTRIBUTE22' then
415 l_col_old_val := p_old.ATTRIBUTE22;
416 l_col_new_val := p_new.ATTRIBUTE22;
417 end if;
418 --
419 if l_column = 'ATTRIBUTE23' then
420 l_col_old_val := p_old.ATTRIBUTE23;
421 l_col_new_val := p_new.ATTRIBUTE23;
422 end if;
423 --
424 if l_column = 'ATTRIBUTE24' then
425 l_col_old_val := p_old.ATTRIBUTE24;
426 l_col_new_val := p_new.ATTRIBUTE24;
427 end if;
428 --
429 if l_column = 'ATTRIBUTE25' then
430 l_col_old_val := p_old.ATTRIBUTE25;
431 l_col_new_val := p_new.ATTRIBUTE25;
432 end if;
433 --
434 if l_column = 'ATTRIBUTE26' then
435 l_col_old_val := p_old.ATTRIBUTE26;
436 l_col_new_val := p_new.ATTRIBUTE26;
437 end if;
438 --
439 if l_column = 'ATTRIBUTE27' then
440 l_col_old_val := p_old.ATTRIBUTE27;
441 l_col_new_val := p_new.ATTRIBUTE27;
442 end if;
443 --
444 if l_column = 'ATTRIBUTE28' then
445 l_col_old_val := p_old.ATTRIBUTE28;
446 l_col_new_val := p_new.ATTRIBUTE28;
447 end if;
448 --
449 if l_column = 'ATTRIBUTE29' then
450 l_col_old_val := p_old.ATTRIBUTE29;
451 l_col_new_val := p_new.ATTRIBUTE29;
452 end if;
453 --
454 if l_column = 'ATTRIBUTE30' then
455 l_col_old_val := p_old.ATTRIBUTE30;
456 l_col_new_val := p_new.ATTRIBUTE30;
457 end if;
458 --
459 if l_column = 'BENEFIT_GROUP_ID' then
460 l_col_old_val := p_old.BENEFIT_GROUP_ID;
461 l_col_new_val := p_new.BENEFIT_GROUP_ID;
462 end if;
463 --
464 if l_column = 'ORIGINAL_DATE_OF_HIRE' then
465 l_col_old_val := to_char(p_old.ORIGINAL_DATE_OF_HIRE, 'YYYY/MM/DD HH24:MI:SS');
466 l_col_new_val := to_char(p_new.ORIGINAL_DATE_OF_HIRE, 'YYYY/MM/DD HH24:MI:SS');
467 end if;
468
469 --
470 benutils.exec_rule(
471 p_formula_id => l_per_info_chg_cs_ler_rl,
472 p_effective_date => l_session_date,
473 p_lf_evt_ocrd_dt => null,
474 p_business_group_id => nvl(p_new.business_group_id, p_old.business_group_id),
475 p_person_id => nvl(p_new.person_id, p_old.person_id),
476 p_new_value => l_col_new_val,
477 p_old_value => l_col_old_val,
478 p_column_name => l_column,
479 p_param5 => 'BEN_PPF_IN_EFFECTIVE_START_DATE',
480 p_param5_value => to_char(p_new.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS'),
481 p_param6 => 'BEN_PPF_IO_EFFECTIVE_START_DATE',
482 p_param6_value => to_char(p_old.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS'),
483 p_param7 => 'BEN_PPF_IN_EFFECTIVE_END_DATE',
484 p_param7_value => to_char(p_new.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS'),
485 p_param8 => 'BEN_PPF_IO_EFFECTIVE_END_DATE',
486 p_param8_value => to_char(p_old.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS'),
487 p_param9 => 'BEN_PPF_IN_DATE_OF_BIRTH',
488 p_param9_value => to_char(p_new.DATE_OF_BIRTH, 'YYYY/MM/DD HH24:MI:SS'),
489 p_param10 => 'BEN_PPF_IO_DATE_OF_BIRTH',
490 p_param10_value => to_char(p_old.DATE_OF_BIRTH, 'YYYY/MM/DD HH24:MI:SS'),
491 p_param11 => 'BEN_PPF_IN_DATE_OF_DEATH',
492 p_param11_value => to_char(p_new.DATE_OF_DEATH, 'YYYY/MM/DD HH24:MI:SS'),
493 p_param12 => 'BEN_PPF_IO_DATE_OF_DEATH',
494 p_param12_value => to_char(p_old.DATE_OF_DEATH, 'YYYY/MM/DD HH24:MI:SS'),
495 p_param13 => 'BEN_PPF_IN_MARITAL_STATUS',
496 p_param13_value => p_new.MARITAL_STATUS,
497 p_param14 => 'BEN_PPF_IO_MARITAL_STATUS',
498 p_param14_value => p_old.MARITAL_STATUS,
499 p_param15 => 'BEN_PPF_IN_STUDENT_STATUS',
500 p_param15_value => p_new.STUDENT_STATUS,
501 p_param16 => 'BEN_PPF_IO_STUDENT_STATUS',
502 p_param16_value => p_old.STUDENT_STATUS,
503 p_param17 => 'BEN_PPF_IN_BENEFIT_GROUP_ID',
504 p_param17_value => to_char(p_new.BENEFIT_GROUP_ID),
505 p_param18 => 'BEN_PPF_IO_BENEFIT_GROUP_ID',
506 p_param18_value => to_char(p_old.BENEFIT_GROUP_ID),
507 p_param19 => 'BEN_PPF_IN_ORIGINAL_DATE_OF_HIRE',
508 p_param19_value => to_char(p_new.ORIGINAL_DATE_OF_HIRE, 'YYYY/MM/DD HH24:MI:SS'),
509 p_param20 => 'BEN_PPF_IO_ORIGINAL_DATE_OF_HIRE',
510 p_param20_value => to_char(p_old.ORIGINAL_DATE_OF_HIRE, 'YYYY/MM/DD HH24:MI:SS'),
511 p_param21 => 'BEN_IV_LER_ID', /* Bug 3891096 */
512 p_param21_value => to_char(l_ler_id),
513 p_pk_id => to_char(p_new.person_id),
514 p_ret_val => l_rule_output);
515 --
516 end if;
517 --
518 hr_utility.set_location(' ben_ppf_trigger'|| ' l_rule_output = ' || l_rule_output, 9999);
519 --
520 if l_column = 'DATE_OF_BIRTH' then
521 l_changed := (benutils.column_changed(p_old.date_of_birth
522 ,p_new.date_of_birth,l_new_val) AND
523 benutils.column_changed(p_new.date_of_birth
524 ,p_old.date_of_birth,l_old_val) AND
525 (l_changed));
526 hr_utility.set_location('NEW DOB'||p_new.date_of_birth, 50);
527 hr_utility.set_location('OLD DOB'||p_old.date_of_birth, 50);
528 hr_utility.set_location(' ben_ppf_trigger', 60);
529 end if;
530 --
531 if l_column = 'DATE_OF_DEATH' then
532 l_changed := (benutils.column_changed(p_old.date_of_death
533 ,p_new.date_of_death,l_new_val) AND
534 benutils.column_changed(p_new.date_of_death
535 ,p_old.date_of_death,l_old_val) AND
536 (l_changed));
537 --
538 -- If date of death set and is different than old value
539 -- Life evt ocurred date MUST be the day they died
540 -- NOT system or Effective date (unless date of death is null)
541 --
542 if l_changed then
543 if p_new.date_of_death is null then
544 l_lf_evt_ocrd_date := l_session_date;
545 else
546 l_lf_evt_ocrd_date := p_new.date_of_death;
547 end if;
548 end if;
549 hr_utility.set_location(' ben_ppf_trigger', 70);
550 end if;
551
552 if l_column = 'MARITAL_STATUS' then
553 l_changed := (benutils.column_changed(p_old.marital_status
554 ,p_new.marital_status,l_new_val) AND
555 benutils.column_changed(p_new.marital_status
556 ,p_old.marital_status,l_old_val) AND
557 (l_changed));
558 hr_utility.set_location(' ben_ppf_trigger', 90);
559 hr_utility.set_location('NEW MS'||p_new.marital_status, 50);
560 hr_utility.set_location('OLD MS'||p_old.marital_status, 50);
561 end if;
562 --
563 if l_column = 'ON_MILITARY_SERVICE' then
564 l_changed := (benutils.column_changed(p_old.on_military_service
565 ,p_new.on_military_service,l_new_val) AND
566 benutils.column_changed(p_new.on_military_service
567 ,p_old.on_military_service,l_old_val) AND
568 (l_changed));
569 hr_utility.set_location(' ben_ppf_trigger', 100);
570 end if;
571 --
572 if l_column = 'REGISTERED_DISABLED_FLAG' then
573 l_changed := (benutils.column_changed(p_old.registered_disabled_flag
574 ,p_new.registered_disabled_flag,l_new_val) AND
575 benutils.column_changed(p_new.registered_disabled_flag
576 ,p_old.registered_disabled_flag,l_old_val) AND
577 (l_changed));
578 hr_utility.set_location(' ben_ppf_trigger', 110);
579 end if;
580 --
581 if l_column = 'SEX' then
582 l_changed := (benutils.column_changed(p_old.sex
583 ,p_new.sex,l_new_val) AND
584 benutils.column_changed(p_new.sex
585 ,p_old.sex,l_old_val) AND
586 (l_changed));
587 hr_utility.set_location(' ben_ppf_trigger', 120);
588 end if;
589 --
590 if l_column = 'STUDENT_STATUS' then
591 l_changed := (benutils.column_changed(p_old.student_status
592 ,p_new.student_status,l_new_val) AND
593 benutils.column_changed(p_new.student_status
594 ,p_old.student_status,l_old_val) AND
595 (l_changed));
596 hr_utility.set_location(' ben_ppf_trigger', 130);
597 end if;
598 --
599 if l_column = 'COORD_BEN_MED_PLN_NO' then
600 l_changed := (benutils.column_changed(p_old.coord_ben_med_pln_no
601 ,p_new.coord_ben_med_pln_no,l_new_val) AND
602 benutils.column_changed(p_new.coord_ben_med_pln_no
603 ,p_old.coord_ben_med_pln_no,l_old_val) AND
604 (l_changed));
605 hr_utility.set_location(' ben_ppf_trigger', 140);
606 end if;
607 --
608 if l_column = 'COORD_BEN_NO_CVG_FLAG' then
609 l_changed := (benutils.column_changed(p_old.coord_ben_no_cvg_flag
610 ,p_new.coord_ben_no_cvg_flag,l_new_val) AND
611 benutils.column_changed(p_new.coord_ben_no_cvg_flag
612 ,p_old.coord_ben_no_cvg_flag,l_old_val) AND
613 (l_changed));
614 hr_utility.set_location(' ben_ppf_trigger', 150);
615 end if;
616 --
617 if l_column = 'USES_TOBACCO_FLAG' then
618 l_changed := (benutils.column_changed(p_old.uses_tobacco_flag
619 ,p_new.uses_tobacco_flag,l_new_val) AND
620 benutils.column_changed(p_new.uses_tobacco_flag
621 ,p_old.uses_tobacco_flag,l_old_val) AND
622 (l_changed));
623 hr_utility.set_location(' ben_ppf_trigger', 160);
624 end if;
625 --
626 if l_column = 'BENEFIT_GROUP_ID' then
627 l_changed := (benutils.column_changed(p_old.benefit_group_id
628 ,p_new.benefit_group_id,l_new_val) AND
629 benutils.column_changed(p_new.benefit_group_id
630 ,p_old.benefit_group_id,l_old_val) AND
631 (l_changed));
632 hr_utility.set_location(' ben_ppf_trigger', 170);
633 end if;
634 --
635 if l_column = 'PER_INFORMATION10' then
636 l_changed := (benutils.column_changed(p_old.per_information10
637 ,p_new.per_information10,l_new_val) AND
638 benutils.column_changed(p_new.per_information10
639 ,p_old.per_information10,l_old_val) AND
640 (l_changed));
641 hr_utility.set_location(' ben_ppf_trigger',180);
642 end if;
643 --
644 if l_column = 'DPDNT_VLNTRY_SVCE_FLAG' then
645 hr_utility.set_location(' ben_ppf_trigger', 190);
646 l_changed := (benutils.column_changed(p_old.dpdnt_vlntry_svce_flag
647 ,p_new.dpdnt_vlntry_svce_flag,l_new_val) AND
648 benutils.column_changed(p_new.dpdnt_vlntry_svce_flag
649 ,p_old.dpdnt_vlntry_svce_flag,l_old_val) AND
650 (l_changed));
651 hr_utility.set_location(' ben_ppf_trigger', 190);
652 end if;
653 --
654 if l_column = 'RECEIPT_OF_DEATH_CERT_DATE' then
655 l_changed := (benutils.column_changed(p_old.receipt_of_death_cert_date
656 ,p_new.receipt_of_death_cert_date,l_new_val) AND
657 benutils.column_changed(p_new.receipt_of_death_cert_date
658 ,p_old.receipt_of_death_cert_date,l_old_val) AND
659 (l_changed));
660 hr_utility.set_location(' ben_ppf_trigger', 200);
661 end if;
662 --
663 if l_column = 'ATTRIBUTE1' then
664 l_changed := (benutils.column_changed(p_old.attribute1
665 ,p_new.attribute1,l_new_val) AND
666 benutils.column_changed(p_new.attribute1
667 ,p_old.attribute1,l_old_val) AND
668 (l_changed));
669 hr_utility.set_location(' ben_ppf_trigger', 210);
670 hr_utility.set_location('NEW A1'||p_new.attribute1, 50);
671 hr_utility.set_location('OLD A1'||p_old.attribute1, 50);
672 end if;
673 --
674 if l_column = 'ATTRIBUTE2' then
675 l_changed := (benutils.column_changed(p_old.attribute2
676 ,p_new.attribute2,l_new_val) AND
677 benutils.column_changed(p_new.attribute2
678 ,p_old.attribute2,l_old_val) AND
679 (l_changed));
680 hr_utility.set_location(' ben_ppf_trigger', 220);
681 end if;
682 --
683 if l_column = 'ATTRIBUTE3' then
684 l_changed := (benutils.column_changed(p_old.attribute3
685 ,p_new.attribute3,l_new_val) AND
686 benutils.column_changed(p_new.attribute3
687 ,p_old.attribute3,l_old_val) AND
688 (l_changed));
689 hr_utility.set_location(' ben_ppf_trigger', 230);
690 end if;
691 --
692 if l_column = 'ATTRIBUTE4' then
693 l_changed := (benutils.column_changed(p_old.attribute4
694 ,p_new.attribute4,l_new_val) AND
695 benutils.column_changed(p_new.attribute4
696 ,p_old.attribute4,l_old_val) AND
697 (l_changed));
698 hr_utility.set_location(' ben_ppf_trigger', 240);
699 end if;
700 --
701 if l_column = 'ATTRIBUTE5' then
702 l_changed := (benutils.column_changed(p_old.attribute5
703 ,p_new.attribute5,l_new_val) AND
704 benutils.column_changed(p_new.attribute5
705 ,p_old.attribute5,l_old_val) AND
706 (l_changed));
707 hr_utility.set_location(' ben_ppf_trigger', 250);
708 end if;
709 --
710 if l_column = 'ATTRIBUTE6' then
711 l_changed := (benutils.column_changed(p_old.attribute6
712 ,p_new.attribute6,l_new_val) AND
713 benutils.column_changed(p_new.attribute6
714 ,p_old.attribute6,l_old_val) AND
715 (l_changed));
716 hr_utility.set_location(' ben_ppf_trigger', 260);
717 end if;
718 --
719 if l_column = 'ATTRIBUTE7' then
720 l_changed := (benutils.column_changed(p_old.attribute7
721 ,p_new.attribute7,l_new_val) AND
722 benutils.column_changed(p_new.attribute7
723 ,p_old.attribute7,l_old_val) AND
724 (l_changed));
725 hr_utility.set_location(' ben_ppf_trigger', 270);
726 end if;
727 --
728 if l_column = 'ATTRIBUTE8' then
729 l_changed := (benutils.column_changed(p_old.attribute8
730 ,p_new.attribute8,l_new_val) AND
731 benutils.column_changed(p_new.attribute8
732 ,p_old.attribute8,l_old_val) AND
733 (l_changed));
734 hr_utility.set_location(' ben_ppf_trigger', 280);
735 end if;
736 --
737 if l_column = 'ATTRIBUTE9' then
738 l_changed := (benutils.column_changed(p_old.attribute9
739 ,p_new.attribute9,l_new_val) AND
740 benutils.column_changed(p_new.attribute9
741 ,p_old.attribute9,l_old_val) AND
742 (l_changed));
743 hr_utility.set_location(' ben_ppf_trigger', 290);
744 end if;
745 --
746 if l_column = 'ATTRIBUTE10' then
747 l_changed := (benutils.column_changed(p_old.attribute10
748 ,p_new.attribute10,l_new_val) AND
749 benutils.column_changed(p_new.attribute10
750 ,p_old.attribute10,l_old_val) AND
751 (l_changed));
752 hr_utility.set_location(' ben_ppf_trigger', 300);
753 end if;
754 --
755 if l_column = 'ATTRIBUTE11' then
756 l_changed := (benutils.column_changed(p_old.attribute11
757 ,p_new.attribute11,l_new_val) AND
758 benutils.column_changed(p_new.attribute11
759 ,p_old.attribute11,l_old_val) AND
760 (l_changed));
761 hr_utility.set_location(' ben_ppf_trigger', 310);
762 end if;
763 --
764 if l_column = 'ATTRIBUTE12' then
765 l_changed := (benutils.column_changed(p_old.attribute12
766 ,p_new.attribute12,l_new_val) AND
767 benutils.column_changed(p_new.attribute12
768 ,p_old.attribute12,l_old_val) AND
769 (l_changed));
770 hr_utility.set_location(' ben_ppf_trigger', 320);
771 end if;
772 --
773 if l_column = 'ATTRIBUTE13' then
774 l_changed := (benutils.column_changed(p_old.attribute13
775 ,p_new.attribute13,l_new_val) AND
776 benutils.column_changed(p_new.attribute13
777 ,p_old.attribute13,l_old_val) AND
778 (l_changed));
779 hr_utility.set_location(' ben_ppf_trigger', 330);
780 end if;
781 --
782 if l_column = 'ATTRIBUTE14' then
783 l_changed := (benutils.column_changed(p_old.attribute14
784 ,p_new.attribute14,l_new_val) AND
785 benutils.column_changed(p_new.attribute14
786 ,p_old.attribute14,l_old_val) AND
787 (l_changed));
788 hr_utility.set_location(' ben_ppf_trigger', 340);
789 end if;
790 --
791 if l_column = 'ATTRIBUTE15' then
792 l_changed := (benutils.column_changed(p_old.attribute15
793 ,p_new.attribute15,l_new_val) AND
794 benutils.column_changed(p_new.attribute15
795 ,p_old.attribute15,l_old_val) AND
796 (l_changed));
797 hr_utility.set_location(' ben_ppf_trigger', 350);
798 end if;
799 --
800 if l_column = 'ATTRIBUTE16' then
801 l_changed := (benutils.column_changed(p_old.attribute16
802 ,p_new.attribute16,l_new_val) AND
803 benutils.column_changed(p_new.attribute16
804 ,p_old.attribute16,l_old_val) AND
805 (l_changed));
806 hr_utility.set_location(' ben_ppf_trigger', 360);
807 end if;
808 --
809 if l_column = 'ATTRIBUTE17' then
810 l_changed := (benutils.column_changed(p_old.attribute17
811 ,p_new.attribute17,l_new_val) AND
812 benutils.column_changed(p_new.attribute17
813 ,p_old.attribute17,l_old_val) AND
814 (l_changed));
815 hr_utility.set_location(' ben_ppf_trigger', 370);
816 end if;
817 --
818 if l_column = 'ATTRIBUTE18' then
819 l_changed := (benutils.column_changed(p_old.attribute18
820 ,p_new.attribute18,l_new_val) AND
821 benutils.column_changed(p_new.attribute18
822 ,p_old.attribute18,l_old_val) AND
823 (l_changed));
824 hr_utility.set_location(' ben_ppf_trigger', 380);
825 end if;
826 --
827 if l_column = 'ATTRIBUTE19' then
828 l_changed := (benutils.column_changed(p_old.attribute19
829 ,p_new.attribute19,l_new_val) AND
830 benutils.column_changed(p_new.attribute19
831 ,p_old.attribute19,l_old_val) AND
832 (l_changed));
833 hr_utility.set_location(' ben_ppf_trigger', 390);
834 end if;
835 --
836 if l_column = 'ATTRIBUTE20' then
837 l_changed := (benutils.column_changed(p_old.attribute20
838 ,p_new.attribute20,l_new_val) AND
839 benutils.column_changed(p_new.attribute20
840 ,p_old.attribute20,l_old_val) AND
841 (l_changed));
842 hr_utility.set_location(' ben_ppf_trigger', 400);
843 end if;
844 --
845 if l_column = 'ATTRIBUTE21' then
846 l_changed := (benutils.column_changed(p_old.attribute21
847 ,p_new.attribute21,l_new_val) AND
848 benutils.column_changed(p_new.attribute21
849 ,p_old.attribute21,l_old_val) AND
850 (l_changed));
851 hr_utility.set_location(' ben_ppf_trigger', 410);
852 end if;
853 --
854 if l_column = 'ATTRIBUTE22' then
855 l_changed := (benutils.column_changed(p_old.attribute22
856 ,p_new.attribute22,l_new_val) AND
857 benutils.column_changed(p_new.attribute22
858 ,p_old.attribute22,l_old_val) AND
859 (l_changed));
860 hr_utility.set_location(' ben_ppf_trigger', 420);
861 end if;
862 --
863 if l_column = 'ATTRIBUTE23' then
864 l_changed := (benutils.column_changed(p_old.attribute23
865 ,p_new.attribute23,l_new_val) AND
866 benutils.column_changed(p_new.attribute23
867 ,p_old.attribute23,l_old_val) AND
868 (l_changed));
869 hr_utility.set_location(' ben_ppf_trigger', 430);
870 end if;
871 --
872 if l_column = 'ATTRIBUTE24' then
873 l_changed := (benutils.column_changed(p_old.attribute24
874 ,p_new.attribute24,l_new_val) AND
875 benutils.column_changed(p_new.attribute24
876 ,p_old.attribute24,l_old_val) AND
877 (l_changed));
878 hr_utility.set_location(' ben_ppf_trigger', 440);
879 end if;
880 --
881 if l_column = 'ATTRIBUTE25' then
882 l_changed := (benutils.column_changed(p_old.attribute25
883 ,p_new.attribute25,l_new_val) AND
884 benutils.column_changed(p_new.attribute25
885 ,p_old.attribute25,l_old_val) AND
886 (l_changed));
887 hr_utility.set_location(' ben_ppf_trigger', 450);
888 end if;
889 --
890 if l_column = 'ATTRIBUTE26' then
891 l_changed := (benutils.column_changed(p_old.attribute26
892 ,p_new.attribute26,l_new_val) AND
893 benutils.column_changed(p_new.attribute26
894 ,p_old.attribute26,l_old_val) AND
895 (l_changed));
896 hr_utility.set_location(' ben_ppf_trigger', 460);
897 end if;
898 --
899 if l_column = 'ATTRIBUTE27' then
900 l_changed := (benutils.column_changed(p_old.attribute27
901 ,p_new.attribute27,l_new_val) AND
902 benutils.column_changed(p_new.attribute27
903 ,p_old.attribute27,l_old_val) AND
904 (l_changed));
905 hr_utility.set_location(' ben_ppf_trigger', 470);
906 end if;
907 --
908 if l_column = 'ATTRIBUTE28' then
909 l_changed := (benutils.column_changed(p_old.attribute28
910 ,p_new.attribute28,l_new_val) AND
911 benutils.column_changed(p_new.attribute28
912 ,p_old.attribute28,l_old_val) AND
913 (l_changed));
914 hr_utility.set_location(' ben_ppf_trigger', 480);
915 end if;
916 --
917 if l_column = 'ATTRIBUTE29' then
918 l_changed := (benutils.column_changed(p_old.attribute29
919 ,p_new.attribute29,l_new_val) AND
920 benutils.column_changed(p_new.attribute29
921 ,p_old.attribute29,l_old_val) AND
922 (l_changed));
923 hr_utility.set_location(' ben_ppf_trigger', 490);
924 end if;
925 --
926 if l_column = 'ATTRIBUTE30' then
927 l_changed := (benutils.column_changed(p_old.attribute30
928 ,p_new.attribute30,l_new_val) AND
929 benutils.column_changed(p_new.attribute30
930 ,p_old.attribute30,l_old_val) AND
931 (l_changed));
932 hr_utility.set_location(' ben_ppf_trigger', 500);
933 end if;
934 --
935 --
936 -- Checking the rule output and the rule override flag.
937 -- Whether the rule is mandatory or not, rule output should return 'Y'
938 -- Rule Mandatory flag is just to override the column data change.
939
940 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
941 l_changed := TRUE ;
942 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
943 l_changed := l_changed AND TRUE;
944 elsif l_rule_output = 'N' then
945 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
946 l_changed := FALSE;
947 end if;
948
949 hr_utility.set_location('After the rule Check ',20.05);
950 if l_changed then
951 hr_utility.set_location(' l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
952 else
953 hr_utility.set_location(' l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
954 end if;
955 -- Check for Column Mandatory Change
956 -- If column change is mandatory and data change has failed then dont trigger
957 -- If column change is non-mandatory and the data change has passed, then trigger.
958
959 if l_chg_mandatory_cd = 'Y' and not l_changed then
960 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
961 l_changed := FALSE;
962 l_trigger := FALSE;
963 exit;
964 elsif l_chg_mandatory_cd = 'Y' and l_changed then
965 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
966 l_changed := TRUE;
967 -- exit; */
968 elsif l_chg_mandatory_cd = 'N' and l_changed then
969 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
970 l_changed := TRUE;
971 l_trigger := TRUE;
972 exit;
973 end if;
974 hr_utility.set_location('After the Mandatory code check ',20.05);
975 if l_changed then
976 hr_utility.set_location(' l_change TRUE ', 20.1);
977 else
978 hr_utility.set_location(' l_change FALSE ', 20.1);
979 end if;
980 --
981 /* if not l_changed then
982 exit;
983 end if;
984 */
985 end loop;
986 hr_utility.set_location(' ben_ppf_trigger', 30);
987 l_ptnl_id := 0;
988 l_ovn :=null;
989 if l_trigger then
990 --
991 -- RCHASE Bug#4920 - Calling point to determine date correctly
992 --
993 if l_ocrd_dt_cd is null then
994 l_lf_evt_ocrd_date := p_new.effective_start_date;
995 else
996 --
997 -- Call the common date procedure.
998 --
999 ben_determine_date.main
1000 (p_date_cd => l_ocrd_dt_cd
1001 ,p_effective_date => p_new.effective_start_date
1002 ,p_lf_evt_ocrd_dt => nvl(l_lf_evt_ocrd_date,p_new.effective_start_date)
1003 ,p_returned_date => l_lf_evt_ocrd_date
1004 );
1005 end if;
1006 --
1007 -- RCHASE Bug#4920 - End
1008 --
1009 if l_type = 'P' then
1010 --
1011 -- Life event has occured for Participant
1012 --
1013 open le_exists(p_new.person_id,l_ler_id,l_lf_evt_ocrd_date);
1014 fetch le_exists into l_le_exists;
1015 --
1016 -- If an already existing life event of this
1017 -- type exists do nothing.
1018 --
1019 if le_exists%notfound then
1020 hr_utility.set_location(' Entering: ben_ppf_trigger5', 60);
1021 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
1022 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
1023 (p_validate => false
1024 ,p_ptnl_ler_for_per_id => l_ptnl_id
1025 ,p_ntfn_dt => l_system_date
1026 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
1027 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
1028 ,p_ler_id => l_ler_id
1029 ,p_ler_typ_cd => l_typ_cd
1030 ,p_person_id => p_new.person_id
1031 ,p_business_group_Id => p_new.business_group_id
1032 ,p_object_version_number => l_ovn
1033 ,p_effective_date => l_effective_start_date
1034 ,p_dtctd_dt => l_effective_start_date);
1035 end if;
1036 close le_exists;
1037 elsif l_type = 'R' then
1038 hr_utility.set_location(' Entering: ben_ppf_trigger5-rp', 65);
1039 --
1040 -- Related Life event has occured for Participant contacts
1041 --
1042 open get_contacts(p_new.person_id);
1043 loop
1044 fetch get_contacts into l_hld_person_id;
1045 exit when get_contacts%notfound;
1046 open le_exists(l_hld_person_id,l_ler_id,l_lf_evt_ocrd_date);
1047 fetch le_exists into l_le_exists;
1048 --
1049 -- If potential life event does not already exist
1050 -- create it.
1051 if le_exists%notfound then
1052 hr_utility.set_location(' Entering: ben_ppf_trigger5', 60);
1053
1054 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
1055 ---ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
1056 (p_validate => false
1057 ,p_ptnl_ler_for_per_id => l_ptnl_id
1058 ,p_ntfn_dt => l_system_date
1059 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
1060 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
1061 ,p_ler_id => l_ler_id
1062 ,p_ler_typ_cd => l_typ_cd
1063 ,p_person_id => l_hld_person_id
1064 ,p_business_group_Id => p_new.business_group_id
1065 ,p_object_version_number => l_ovn
1066 ,p_effective_date => l_effective_start_date
1067 ,p_dtctd_dt => l_effective_start_date);
1068 end if;
1069 l_ptnl_id := 0;
1070 l_ovn :=null;
1071 close le_exists;
1072 end loop;
1073 close get_contacts;
1074 end if;
1075 --
1076 -- reset the variables.
1077 --
1078 hr_utility.set_location(' ben_ppf_trigger', 40);
1079 l_changed := FALSE;
1080 l_trigger := TRUE;
1081 l_ovn := NULL;
1082 -- l_effective_start_date := p_new.effective_start_date;
1083 end if;
1084 close get_ler_col;
1085 end loop;
1086 hr_utility.set_location(' ben_ppf_trigger', 180);
1087 close get_ler;
1088 hr_utility.set_location(' Leaving: ben_ppf_trigger', 200);
1089
1090 end;
1091 end ben_ppf_ler;