[Home] [Help]
PACKAGE BODY: APPS.BEN_EGD_LER
Source
1 PACKAGE BODY ben_egd_ler AS
2 /* $Header: beegdtrg.pkb 120.4 2011/02/03 13:10:01 amnaraya ship $*/
3 --
4 -- Cached static values
5 --
6 g_trunc_sysdate DATE;
7 g_oabinstall_status VARCHAR2(1);
8 --
9 PROCEDURE ler_chk(p_old in g_egd_ler_rec,
10 p_new in g_egd_ler_rec,
11 p_effective_date in date) IS
12 --
13 l_proc varchar2(72) := 'ben_egd_ler.ler_chk';
14 --
15 l_egdlertrg_set ben_letrg_cache.g_egdlertrg_inst_tbl;
16 --
17 l_session_date DATE;
18 l_system_date DATE;
19 --
20 CURSOR get_session_date IS
21 SELECT fs.effective_date
22 FROM fnd_sessions fs
23 WHERE fs.session_id = USERENV('SESSIONID');
24 --
25 cursor get_ler(l_status varchar2) is
26 SELECT ler.ler_id,
27 ler.ocrd_dt_det_cd
28 FROM ben_ler_f ler
29 WHERE ler.business_group_id = p_new.business_group_id
30 AND l_session_date BETWEEN ler.effective_start_date
31 AND ler.effective_end_date
32 AND (
33 (
34 EXISTS
35 (SELECT 1
36 FROM ben_per_info_chg_cs_ler_f psl,
37 ben_ler_per_info_cs_ler_f lpl
38 WHERE psl.source_table = 'BEN_ELIG_DPNT'
39 AND psl.per_info_chg_cs_ler_id =
40 lpl.per_info_chg_cs_ler_id
41 AND l_session_date BETWEEN psl.effective_start_date
42 AND psl.effective_end_date
43 AND lpl.ler_id = ler.ler_id
44 AND l_session_date BETWEEN lpl.effective_start_date
45 AND lpl.effective_end_date)
46 )
47 OR (
48 EXISTS
49 (SELECT 1
50 FROM ben_ler_rltd_per_cs_ler_f lrp,
51 ben_rltd_per_chg_cs_ler_f rpc
52 WHERE rpc.source_table = 'BEN_ELIG_DPNT'
53 AND l_session_date BETWEEN rpc.effective_start_date
54 AND rpc.effective_end_date
55 AND rpc.rltd_per_chg_cs_ler_id =
56 lrp.rltd_per_chg_cs_ler_id
57 AND lrp.ler_id = ler.ler_id
58 AND l_session_date BETWEEN lrp.effective_start_date
59 AND lrp.effective_end_date)
60 )
61 );
62 --
63
64 CURSOR get_ler_col(p_ler_id IN ben_ler_f.ler_id%TYPE) IS
65 SELECT psl.source_column,
66 psl.new_val,
67 psl.old_val,
68 'P',
69 psl.per_info_chg_cs_ler_rl, psl.rule_overrides_flag, lpl.chg_mandatory_cd
70 FROM ben_ler_per_info_cs_ler_f lpl, ben_per_info_chg_cs_ler_f psl
71 WHERE lpl.ler_id = p_ler_id
72 AND lpl.business_group_id = p_new.business_group_id
73 AND lpl.business_group_id = psl.business_group_id
74 AND l_session_date BETWEEN psl.effective_start_date
75 AND psl.effective_end_date
76 AND l_session_date BETWEEN lpl.effective_start_date
77 AND lpl.effective_end_date
78 AND psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
79 AND source_table = 'BEN_ELIG_DPNT'
80 UNION ALL
81 SELECT rpc.source_column,
82 rpc.new_val,
83 rpc.old_val,
84 'R',
85 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, ben_rltd_per_chg_cs_ler_f rpc
87 WHERE lrp.ler_id = p_ler_id
88 AND lrp.business_group_id = p_new.business_group_id
89 AND lrp.business_group_id = rpc.business_group_id
90 AND l_session_date BETWEEN rpc.effective_start_date
91 AND rpc.effective_end_date
92 AND l_session_date BETWEEN lrp.effective_start_date
93 AND lrp.effective_end_date
94 AND rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
95 AND source_table = 'BEN_ELIG_DPNT'
96 order by 1;
97 --
98 CURSOR le_exists(
99 p_person_id IN per_all_people_f.person_id%TYPE,
100 p_ler_id IN ben_ler_f.ler_id%TYPE,
101 p_lf_evt_ocrd_dt IN DATE) IS
102 SELECT 'Y'
103 FROM ben_ptnl_ler_for_per bp
104 WHERE bp.person_id = p_person_id
105 AND bp.ler_id = p_ler_id
106 AND bp.ptnl_ler_for_per_stat_cd = 'DTCTD'
107 AND bp.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
108 --
109 CURSOR get_contacts(p_person_id IN per_all_people_f.person_id%TYPE) IS
110 SELECT pcr.contact_person_id
111 FROM per_contact_relationships pcr
112 WHERE pcr.person_id = p_person_id
113 AND pcr.business_group_id = p_new.business_group_id;/*
114 AND l_session_date BETWEEN NVL(pcr.date_start, l_session_date)
115 AND NVL(pcr.date_end, l_session_date);*/--Bug 10100651
116
117 --Bug 5630251 Fetch person_id from the p_new.per_in_ler_id
118 cursor get_person IS
119 select person_id
120 from ben_per_in_ler
121 where per_in_ler_id = p_new.per_in_ler_id
122 and per_in_ler_stat_cd not in ('VOIDD','BCKDT')
123 and business_Group_id = p_new.business_group_id;
124
125 l_person_id number := -999999999;
126
127 --End Bug 5630251
128 --
129 l_changed BOOLEAN;
130 l_ler_id NUMBER;
131 l_typ_cd ben_ler_f.typ_cd%type ;
132 l_column ben_rltd_per_chg_cs_ler_f.source_column%type; -- VARCHAR2(30);
133 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type; -- VARCHAR2(30);
134 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type; -- VARCHAR2(30);
135 l_ocrd_dt_cd VARCHAR2(30);
136 l_per_info_chg_cs_ler_rl NUMBER;
137 l_rule_output VARCHAR2(1);
138 l_ovn NUMBER;
139 l_ptnl_id NUMBER;
140 l_effective_start_date DATE;
141 l_lf_evt_ocrd_date DATE;
142 l_le_exists VARCHAR2(1);
143 l_elig_strt_dt DATE;
144 l_elig_thru_dt DATE;
145 l_create_dt DATE;
146 l_type VARCHAR2(1);
147 l_hld_person_id NUMBER;
148 --
149 l_bool BOOLEAN;
150 l_status VARCHAR2(1);
151 l_industry VARCHAR2(1);
152 --
153 l_col_old_val VARCHAR2(1000);
154 l_col_new_val VARCHAR2(1000);
155 --
156 l_rule_overrides_flag VARCHAR2(1);
157 l_chg_mandatory_cd VARCHAR2(1);
158 l_trigger boolean := TRUE;
159 --
160 --
161 l_rec_business_group_id ben_ler_f.business_group_id%TYPE
162 := NVL(p_new.business_group_id, p_old.business_group_id);
163 l_rec_dpnt_person_id per_all_people_f.person_id%TYPE
164 := NVL(p_new.dpnt_person_id, p_old.dpnt_person_id);
165
166
167 /*Bug 9500422: Add the dpnt_person_id to the list if there is a change in BEN_ELIG_DPNT data of a dependent.
168 Before creating the potential check whether the dpnt_person_id is in the list,if present then
169 create the potential*/
170 TYPE g_dpnt_person_id_tab IS TABLE OF NUMBER;
171 g_dpnt_person_id_list g_dpnt_person_id_tab := g_dpnt_person_id_tab();
172
173 function is_present(p_dpnt_person_id number) return boolean is
174 begin
175 if(g_dpnt_person_id_list.FIRST is not NULL and g_dpnt_person_id_list.LAST is not NULL) then
176 FOR i IN g_dpnt_person_id_list.FIRST..g_dpnt_person_id_list.LAST LOOP
177 IF g_dpnt_person_id_list(i) = p_dpnt_person_id THEN
178 return true;
179 EXIT;
180 END IF;
181 END LOOP;
182 end if;
183 return false;
184 end is_present;
185 /*End Bug 9500422*/
186
187 --
188 BEGIN
189 --
190 -- Bug 3320133
191 benutils.set_data_migrator_mode;
192 -- End of Bug 3320133
193
194 -- Not to be called when Data Migrator is in progress
195 --
196 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
197 --
198 hr_utility.set_location(' No DM ' || l_proc, 10);
199 --
200 -- Check if truncated sysdate global has previously been set
201 -- if not then set it
202 --
203 if g_trunc_sysdate is null then
204 --
205 g_trunc_sysdate := TRUNC(SYSDATE);
206 --
207 end if;
208 --
209 l_system_date := g_trunc_sysdate;
210 --
211 /*
212 -- Check if oab install status global has previously been set
213 -- if not then set it
214 --
215 if g_oabinstall_status is null then
216 --
217 l_bool := fnd_installation.get
218 (appl_id => 805
219 ,dep_appl_id => 805
220 ,status => l_status
221 ,industry => l_industry
222 );
223 --
224 g_oabinstall_status := l_status;
225 --
226 end if;
227 --
228 l_status := g_oabinstall_status;
229 --
230 IF l_status = 'I' THEN
231 */
232 -- commented since the L_status check is available in the beltrgch cursor itself.
233
234 hr_utility.set_location(' Entering: ben_egd_trigger', 10);
235 --
236 -- Check if oab install status global has previously been set
237 -- if not then set it
238 --
239 l_session_date := p_effective_date;
240 --
241 l_changed := FALSE;
242 l_effective_start_date := l_session_date;
243 -- in some situations the date we use for occured on date is null,
244 -- use session date instead.
245 l_elig_strt_dt := NVL(p_new.elig_strt_dt, l_session_date);
246 l_elig_thru_dt := NVL(p_new.elig_thru_dt, l_session_date);
247 l_create_dt := NVL(p_new.create_dt, l_session_date);
248 hr_utility.set_location(' ben_egd_trigger', 20);
249 --
250 -- Get the ler details list
251 --
252 hr_utility.set_location(' LE EGD Cac ' || l_proc, 10);
253 ben_letrg_cache.get_egdlertrg_dets
254 (p_business_group_id => p_new.business_group_id
255 ,p_effective_date => l_session_date
256 ,p_inst_set => l_egdlertrg_set
257 );
258 hr_utility.set_location(' Dn LE EGD Cac ' || l_proc, 10);
259 --
260 if l_egdlertrg_set.count > 0 then
261 --
262 for ler_row in l_egdlertrg_set.first..l_egdlertrg_set.last loop
263 --
264 l_ler_id := l_egdlertrg_set(ler_row).ler_id;
265 l_typ_cd := l_egdlertrg_set(ler_row).typ_cd;
266 --
267 l_trigger := TRUE;
268 IF l_egdlertrg_set(ler_row).ocrd_dt_det_cd IS NULL THEN
269 l_lf_evt_ocrd_date := l_elig_strt_dt;
270 ELSE
271 --
272 -- Call the common date procedure.
273 --
274 ben_determine_date.main(
275 p_date_cd => l_egdlertrg_set(ler_row).ocrd_dt_det_cd,
276 p_effective_date => l_elig_strt_dt,
277 p_lf_evt_ocrd_dt => p_new.elig_strt_dt,
278 p_returned_date => l_lf_evt_ocrd_date);
279 END IF;
280 --
281 OPEN get_ler_col(l_egdlertrg_set(ler_row).ler_id);
282 <<get_ler_col_loop>>
283 LOOP
284 FETCH get_ler_col INTO l_column,
285 l_new_val,
286 l_old_val,
287 l_type,
288 l_per_info_chg_cs_ler_rl, l_rule_overrides_flag, l_chg_mandatory_cd;
289 EXIT get_ler_col_loop WHEN get_ler_col%NOTFOUND;
290 hr_utility.set_location('LER ' || l_egdlertrg_set(ler_row).ler_id, 20);
291 hr_utility.set_location('COLUMN ' || l_column, 20);
292 hr_utility.set_location('NEWVAL ' || l_new_val, 20);
293 hr_utility.set_location('OLDVAL ' || l_old_val, 20);
294 hr_utility.set_location('TYPE ' || l_type, 20);
295 hr_utility.set_location('CD ' || l_egdlertrg_set(ler_row).ocrd_dt_det_cd, 20);
296 hr_utility.set_location('create dt' || l_create_dt, 20);
297 hr_utility.set_location('elig strt dt ' || l_elig_strt_dt, 20);
298 hr_utility.set_location('elig thru dt ' || l_elig_thru_dt, 20);
299 l_changed := TRUE;
300 if get_ler_col%ROWCOUNT = 1 then
301 l_changed := TRUE;
302 END IF;
303 hr_utility.set_location(' ben_egd_trigger', 50);
304 --
305 -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
306 -- If it returns Y, then see the applicability of the data
307 -- changes based on new and old values.
308 --
309 l_rule_output := 'Y';
310 --
311 IF l_per_info_chg_cs_ler_rl IS NOT NULL THEN
312 --
313 IF l_column = 'DPNT_PERSON_ID' THEN
314 l_col_old_val := TO_CHAR(p_old.dpnt_person_id);
315 l_col_new_val := TO_CHAR(p_new.dpnt_person_id);
316 ELSIF l_column = 'ELIG_STRT_DT' THEN
317 l_col_old_val :=
318 TO_CHAR(p_old.elig_strt_dt, 'YYYY/MM/DD HH24:MI:SS');
319 l_col_new_val :=
320 TO_CHAR(p_new.elig_strt_dt, 'YYYY/MM/DD HH24:MI:SS');
321 ELSIF l_column = 'ELIG_THRU_DT' THEN
322 l_col_old_val :=
323 TO_CHAR(p_old.elig_thru_dt, 'YYYY/MM/DD HH24:MI:SS');
324 l_col_new_val :=
325 TO_CHAR(p_new.elig_thru_dt, 'YYYY/MM/DD HH24:MI:SS');
326 ELSIF l_column = 'DPNT_INELIG_FLAG' THEN
327 l_col_old_val := p_old.dpnt_inelig_flag;
328 l_col_new_val := p_new.dpnt_inelig_flag;
329 ELSIF l_column = 'OVRDN_FLAG' THEN
330 l_col_old_val := p_old.ovrdn_flag;
331 l_col_new_val := p_new.ovrdn_flag;
332 ELSIF l_column = 'CREATE_DT' THEN
333 l_col_old_val :=
334 TO_CHAR(p_old.create_dt, 'YYYY/MM/DD HH24:MI:SS');
335 l_col_new_val :=
336 TO_CHAR(p_new.create_dt, 'YYYY/MM/DD HH24:MI:SS');
337 ELSIF l_column = 'OVRDN_THRU_DT' THEN
338 l_col_old_val :=
339 TO_CHAR(p_old.ovrdn_thru_dt, 'YYYY/MM/DD HH24:MI:SS');
340 l_col_new_val :=
341 TO_CHAR(p_new.ovrdn_thru_dt, 'YYYY/MM/DD HH24:MI:SS');
342 ELSIF l_column = 'INELG_RSN_CD' THEN
343 l_col_old_val := p_old.inelg_rsn_cd;
344 l_col_new_val := p_new.inelg_rsn_cd;
345 ELSIF l_column = 'ELIG_PER_ELCTBL_CHC_ID' THEN
346 l_col_old_val := TO_CHAR(p_old.elig_per_elctbl_chc_id);
347 l_col_new_val := TO_CHAR(p_new.elig_per_elctbl_chc_id);
348 ELSIF l_column = 'PER_IN_LER_ID' THEN
349 l_col_old_val := TO_CHAR(p_old.per_in_ler_id);
350 l_col_new_val := TO_CHAR(p_new.per_in_ler_id);
351 ELSIF l_column = 'ELIG_PER_ID' THEN
352 l_col_old_val := TO_CHAR(p_old.elig_per_id);
353 l_col_new_val := TO_CHAR(p_new.elig_per_id);
354 ELSIF l_column = 'ELIG_PER_OPT_ID' THEN
355 l_col_old_val := TO_CHAR(p_old.elig_per_opt_id);
356 l_col_new_val := TO_CHAR(p_new.elig_per_opt_id);
357 ELSIF l_column = 'ELIG_CVRD_DPNT_ID' THEN
358 l_col_old_val := TO_CHAR(p_old.elig_cvrd_dpnt_id);
359 l_col_new_val := TO_CHAR(p_new.elig_cvrd_dpnt_id);
360 END IF;
361 --
362 benutils.exec_rule(
363 p_formula_id => l_per_info_chg_cs_ler_rl,
364 p_effective_date => l_session_date,
365 p_lf_evt_ocrd_dt => NULL,
366 p_business_group_id => l_rec_business_group_id,
367 p_person_id => l_rec_dpnt_person_id,
368 p_new_value => l_col_new_val,
369 p_old_value => l_col_old_val,
370 p_column_name => l_column,
371 p_param5 => 'BEN_EGD_IN_DPNT_PERSON_ID',
372 p_param5_value => TO_CHAR(p_new.dpnt_person_id),
373 p_param6 => 'BEN_EGD_IO_DPNT_PERSON_ID',
374 p_param6_value => TO_CHAR(p_old.dpnt_person_id),
375 p_param7 => 'BEN_EGD_IN_ELIG_STRT_DT',
376 p_param7_value => TO_CHAR(
377 p_new.elig_strt_dt,
378 'YYYY/MM/DD HH24:MI:SS'),
379 p_param8 => 'BEN_EGD_IO_ELIG_STRT_DT',
380 p_param8_value => TO_CHAR(
381 p_old.elig_strt_dt,
382 'YYYY/MM/DD HH24:MI:SS'),
383 p_param9 => 'BEN_EGD_IN_ELIG_THRU_DT',
384 p_param9_value => TO_CHAR(
385 p_new.elig_thru_dt,
386 'YYYY/MM/DD HH24:MI:SS'),
387 p_param10 => 'BEN_EGD_IO_ELIG_THRU_DT',
388 p_param10_value => TO_CHAR(
389 p_old.elig_thru_dt,
390 'YYYY/MM/DD HH24:MI:SS'),
391 p_param11 => 'BEN_EGD_IN_DPNT_INELIG_FLAG',
392 p_param11_value => p_new.dpnt_inelig_flag,
393 p_param12 => 'BEN_EGD_IO_DPNT_INELIG_FLAG',
394 p_param12_value => p_old.dpnt_inelig_flag,
395 p_param13 => 'BEN_EGD_IN_INELG_RSN_CD',
396 p_param13_value => p_new.inelg_rsn_cd,
397 p_param14 => 'BEN_EGD_IO_INELG_RSN_CD',
398 p_param14_value => p_old.inelg_rsn_cd,
399 p_param15 => 'BEN_EGD_IN_ELIG_PER_ELCTBL_CHC_ID',
400 p_param15_value => TO_CHAR(p_new.elig_per_elctbl_chc_id),
401 p_param16 => 'BEN_EGD_IO_ELIG_PER_ELCTBL_CHC_ID',
402 p_param16_value => TO_CHAR(p_old.elig_per_elctbl_chc_id),
403 p_param17 => 'BEN_EGD_IN_PER_IN_LER_ID',
404 p_param17_value => TO_CHAR(p_new.per_in_ler_id),
405 p_param18 => 'BEN_EGD_IO_PER_IN_LER_ID',
406 p_param18_value => TO_CHAR(p_old.per_in_ler_id),
407 p_param19 => 'BEN_EGD_IN_ELIG_PER_ID',
408 p_param19_value => TO_CHAR(p_new.elig_per_id),
409 p_param20 => 'BEN_EGD_IO_ELIG_PER_ID',
410 p_param20_value => TO_CHAR(p_old.elig_per_id),
411 p_param21 => 'BEN_EGD_IN_ELIG_PER_OPT_ID',
412 p_param21_value => TO_CHAR(p_new.elig_per_opt_id),
413 p_param22 => 'BEN_EGD_IO_ELIG_PER_OPT_ID',
414 p_param22_value => TO_CHAR(p_old.elig_per_opt_id),
415 p_param23 => 'BEN_EGD_IN_ELIG_CVRD_DPNT_ID',
416 p_param23_value => TO_CHAR(p_new.elig_cvrd_dpnt_id),
417 p_param24 => 'BEN_EGD_IO_ELIG_CVRD_DPNT_ID',
418 p_param24_value => TO_CHAR(p_old.elig_cvrd_dpnt_id),
419 p_param25 => 'BEN_IV_LER_ID', /* Bug 3891096 */
420 p_param25_value => to_char(l_ler_id),
421 p_pk_id => TO_CHAR(p_new.elig_dpnt_id),
422 p_ret_val => l_rule_output);
423 --
424 END IF;
425 --
426 --
427 IF l_column = 'ELIG_STRT_DT' THEN
428 l_changed :=
429 (
430 benutils.column_changed(
431 p_old.elig_strt_dt,
432 p_new.elig_strt_dt,
433 l_new_val)
434 AND benutils.column_changed(
435 p_new.elig_strt_dt,
436 p_old.elig_strt_dt,
437 l_old_val)
438 AND (l_changed));
439 hr_utility.set_location(' l_changed:', 40);
440 ELSIF l_column = 'ELIG_THRU_DT' THEN
441 l_changed :=
442 (
443 benutils.column_changed(
444 p_old.elig_thru_dt,
445 p_new.elig_thru_dt,
446 l_new_val)
447 AND benutils.column_changed(
448 p_new.elig_thru_dt,
449 p_old.elig_thru_dt,
450 l_old_val)
451 AND (l_changed));
452 --
453 IF l_egdlertrg_set(ler_row).ocrd_dt_det_cd IS NULL THEN
454 l_lf_evt_ocrd_date := l_elig_thru_dt;
455 ELSE
456 --
457 -- Call the common date procedure.
458 --
459 ben_determine_date.main(
460 p_date_cd => l_egdlertrg_set(ler_row).ocrd_dt_det_cd,
461 p_effective_date => l_elig_thru_dt,
462 p_lf_evt_ocrd_dt => p_new.elig_thru_dt,
463 p_returned_date => l_lf_evt_ocrd_date);
464 END IF;
465 --
466 ELSIF l_column = 'DPNT_INELIG_FLAG' THEN
467 l_changed :=
468 (
469 benutils.column_changed(
470 p_old.dpnt_inelig_flag,
471 p_new.dpnt_inelig_flag,
472 l_new_val)
473 AND benutils.column_changed(
474 p_new.dpnt_inelig_flag,
475 p_old.dpnt_inelig_flag,
476 l_old_val)
477 AND (l_changed));
478 --
479 IF l_egdlertrg_set(ler_row).ocrd_dt_det_cd IS NULL THEN
480 l_lf_evt_ocrd_date := l_elig_thru_dt;
481 ELSE
482 --
483 -- Call the common date procedure.
484 --
485 ben_determine_date.main(
486 p_date_cd => l_egdlertrg_set(ler_row).ocrd_dt_det_cd,
487 p_effective_date => l_elig_thru_dt,
488 p_lf_evt_ocrd_dt => p_new.elig_thru_dt,
489 p_returned_date => l_lf_evt_ocrd_date);
490 END IF;
491 --
492 ELSIF l_column = 'OVRDN_THRU_DT' THEN
493 l_changed :=
494 (
495 benutils.column_changed(
496 p_old.ovrdn_thru_dt,
497 p_new.ovrdn_thru_dt,
498 l_new_val)
499 AND benutils.column_changed(
500 p_new.ovrdn_thru_dt,
501 p_old.ovrdn_thru_dt,
502 l_old_val)
503 AND (l_changed));
504 --
505 IF l_egdlertrg_set(ler_row).ocrd_dt_det_cd IS NULL THEN
506 l_lf_evt_ocrd_date := l_create_dt;
507 ELSE
508 --
509 -- Call the common date procedure.
510 --
511 ben_determine_date.main(
512 p_date_cd => l_egdlertrg_set(ler_row).ocrd_dt_det_cd,
513 p_effective_date => l_create_dt,
514 p_lf_evt_ocrd_dt => p_new.create_dt,
515 p_returned_date => l_lf_evt_ocrd_date);
516 END IF;
517 --
518 ELSIF l_column = 'OVRDN_FLAG' THEN
519 hr_utility.set_location(
520 'Old ovrdn_flag ' || p_old.ovrdn_flag,
521 20);
522 hr_utility.set_location(
523 'New ovrdn_flag ' || p_new.ovrdn_flag,
524 20);
525 hr_utility.set_location('lodt ' || l_lf_evt_ocrd_date, 20);
526 l_changed :=
527 (
528 benutils.column_changed(
529 p_old.ovrdn_flag,
530 p_new.ovrdn_flag,
531 l_new_val)
532 AND benutils.column_changed(
533 p_new.ovrdn_flag,
534 p_old.ovrdn_flag,
535 l_old_val)
536 AND (l_changed));
537 --
538 IF l_egdlertrg_set(ler_row).ocrd_dt_det_cd IS NULL THEN
539 l_lf_evt_ocrd_date := l_create_dt;
540 ELSE
541 --
542 -- Call the common date procedure.
543 --
544 ben_determine_date.main(
545 p_date_cd => l_egdlertrg_set(ler_row).ocrd_dt_det_cd,
546 p_effective_date => l_create_dt,
547 p_lf_evt_ocrd_dt => p_new.create_dt,
548 p_returned_date => l_lf_evt_ocrd_date);
549 END IF;
550 END IF;
551 -- End of all Column checkings
552
553 --
554 --
555 -- Checking the rule output and the rule override flag.
556 -- Whether the rule is mandatory or not, rule output should return 'Y'
557 -- Rule Mandatory flag is just to override the column data change.
558
559 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
560 l_changed := TRUE ;
561 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
562 l_changed := l_changed AND TRUE;
563 elsif l_rule_output = 'N' then
564 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
565 l_changed := FALSE;
566 end if;
567
568 hr_utility.set_location('After the rule Check ',20.05);
569 if l_changed then
570 hr_utility.set_location(' l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
571 else
572 hr_utility.set_location(' l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
573 end if;
574 -- Check for Column Mandatory Change
575 -- If column change is mandatory and data change has failed then dont trigger
576 -- If column change is non-mandatory and the data change has passed, then trigger.
577
578 /*Bug 9500422: if there is a change, add the dpnt_person_id to the list*/
579 if(l_changed) then
580 hr_utility.set_location('Adding p_new.DPNT_PERSON_ID '||p_new.DPNT_PERSON_ID,855);
581 g_dpnt_person_id_list.extend;
582 g_dpnt_person_id_list(g_dpnt_person_id_list.last) := p_new.DPNT_PERSON_ID;
583 end if;
584 /*End Bug 9500422*/
585
586
587 if l_chg_mandatory_cd = 'Y' and not l_changed then
588 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
589 l_changed := FALSE;
590 l_trigger := FALSE;
591 exit;
592 elsif l_chg_mandatory_cd = 'Y' and l_changed then
593 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
594 l_changed := TRUE;
595 -- exit; */
596 elsif l_chg_mandatory_cd = 'N' and l_changed then
597 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
598 l_changed := TRUE;
599 l_trigger := TRUE;
600 exit;
601 end if;
602
603 hr_utility.set_location('After the Mandatory code check ',20.05);
604 if l_changed then
605 hr_utility.set_location(' l_change TRUE ', 20.1);
606 else
607 hr_utility.set_location(' l_change FALSE ', 20.1);
608 end if;
609 --
610 /* if not l_changed then
611 exit;
612 end if; */
613
614 END LOOP get_ler_col_loop;
615 hr_utility.set_location(' ben_egd_trigger', 50);
616 l_ptnl_id := 0;
617 l_ovn := NULL;
618
619 --Bug 5630251
620 open get_person;
621 fetch get_person into l_person_id;
622
623 if get_person%notfound then
624 l_person_id := -999999999;
625 end if;
626
627 hr_utility.set_location('l_person_id' || l_person_id , 50);
628 hr_utility.set_location('per_in_ler_id' || p_new.per_in_ler_id, 50);
629
630 close get_person;
631 --End Bug 5630251
632
633 if l_trigger and (l_person_id <> -999999999) then --Bug 5630251
634 IF l_type = 'P' THEN
635 OPEN le_exists(l_person_id, l_ler_id, l_lf_evt_ocrd_date); --Bug 5630251
636 FETCH le_exists INTO l_le_exists;
637 IF le_exists%NOTFOUND THEN
638 hr_utility.set_location(' Entering: ben_egd_trigger5', 60);
639
640 ben_create_ptnl_ler_for_per.create_ptnl_ler_event(
641 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per(
642 p_validate => FALSE,
643 p_ptnl_ler_for_per_id => l_ptnl_id,
644 p_ntfn_dt => l_system_date,
645 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date,
646 p_ptnl_ler_for_per_stat_cd => 'DTCTD',
647 p_ler_id => l_ler_id,
648 p_ler_typ_cd => l_typ_cd,
649 p_person_id => l_person_id, --Bug 5630251
650 p_business_group_id => p_new.business_group_id,
651 p_object_version_number => l_ovn,
652 p_effective_date => l_effective_start_date,
653 p_dtctd_dt => l_effective_start_date);
654 END IF;
655 CLOSE le_exists;
656 ELSIF l_type = 'R' THEN
657 hr_utility.set_location(' Entering: ben_egd_trigger5-', 65);
658 OPEN get_contacts(l_person_id); --Bug 5630251
659 <<get_contacts_loop>>
660 LOOP
661 FETCH get_contacts INTO l_hld_person_id;
662 EXIT get_contacts_loop WHEN get_contacts%NOTFOUND;
663 /*Bug 9500422: Added below if.else condition. If dpnt_person_id is present in the list,
664 then create the potential*/
665 if(is_present(l_hld_person_id)) then
666 hr_utility.set_location(' Creating ptnl for dep', 60);
667 OPEN le_exists(l_person_id, l_ler_id, l_lf_evt_ocrd_date);--Bug 10100651
668 FETCH le_exists INTO l_le_exists;
669 IF le_exists%NOTFOUND THEN
670 hr_utility.set_location(' Entering: ben_egd_trigger5', 60);
671
672 ben_create_ptnl_ler_for_per.create_ptnl_ler_event(
673 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per(
674 p_validate => FALSE,
675 p_ptnl_ler_for_per_id => l_ptnl_id,
676 p_ntfn_dt => l_system_date,
677 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date,
678 p_ptnl_ler_for_per_stat_cd => 'DTCTD',
679 p_ler_id => l_ler_id,
680 p_ler_typ_cd => l_typ_cd,
681 p_person_id => l_person_id,--Bug 10100651
682 p_business_group_id => p_new.business_group_id,
683 p_object_version_number => l_ovn,
684 p_effective_date => l_effective_start_date,
685 p_dtctd_dt => l_effective_start_date);
686 END IF;
687 l_ptnl_id := 0;
688 l_ovn := NULL;
689 CLOSE le_exists;
690 end if;
691 END LOOP get_contacts_loop;
692 CLOSE get_contacts;
693 END IF;
694 --
695 -- reset the variables.
696 --
697 l_changed := FALSE;
698 l_ovn := NULL;
699 l_trigger := TRUE;
700 l_effective_start_date := l_session_date;
701 -- l_lf_evt_ocrd_date := l_session_date;
702 END IF;
703 CLOSE get_ler_col;
704 END LOOP get_ler_loop;
705 hr_utility.set_location(' Dn get_ler loop ' || l_proc, 10);
706 -- END IF;
707 END IF;
708 END IF;
709 END ler_chk;
710
711 END ben_egd_ler;