[Home] [Help]
PACKAGE BODY: APPS.BEN_EGD_LER
Source
1 PACKAGE BODY ben_egd_ler AS
2 /* $Header: beegdtrg.pkb 120.2 2006/11/02 15:46:53 rgajula noship $*/
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);
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 BEGIN
167 --
168 -- Bug 3320133
169 benutils.set_data_migrator_mode;
170 -- End of Bug 3320133
171
172 -- Not to be called when Data Migrator is in progress
173 --
174 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
175 --
176 hr_utility.set_location(' No DM ' || l_proc, 10);
177 --
178 -- Check if truncated sysdate global has previously been set
179 -- if not then set it
180 --
181 if g_trunc_sysdate is null then
182 --
183 g_trunc_sysdate := TRUNC(SYSDATE);
184 --
185 end if;
186 --
187 l_system_date := g_trunc_sysdate;
188 --
189 /*
190 -- Check if oab install status global has previously been set
191 -- if not then set it
192 --
193 if g_oabinstall_status is null then
194 --
195 l_bool := fnd_installation.get
196 (appl_id => 805
197 ,dep_appl_id => 805
198 ,status => l_status
199 ,industry => l_industry
200 );
201 --
202 g_oabinstall_status := l_status;
203 --
204 end if;
205 --
206 l_status := g_oabinstall_status;
207 --
208 IF l_status = 'I' THEN
209 */
210 -- commented since the L_status check is available in the beltrgch cursor itself.
211
212 hr_utility.set_location(' Entering: ben_egd_trigger', 10);
213 --
214 -- Check if oab install status global has previously been set
215 -- if not then set it
216 --
217 l_session_date := p_effective_date;
218 --
219 l_changed := FALSE;
220 l_effective_start_date := l_session_date;
221 -- in some situations the date we use for occured on date is null,
222 -- use session date instead.
223 l_elig_strt_dt := NVL(p_new.elig_strt_dt, l_session_date);
224 l_elig_thru_dt := NVL(p_new.elig_thru_dt, l_session_date);
225 l_create_dt := NVL(p_new.create_dt, l_session_date);
226 hr_utility.set_location(' ben_egd_trigger', 20);
227 --
228 -- Get the ler details list
229 --
230 hr_utility.set_location(' LE EGD Cac ' || l_proc, 10);
231 ben_letrg_cache.get_egdlertrg_dets
232 (p_business_group_id => p_new.business_group_id
233 ,p_effective_date => l_session_date
234 ,p_inst_set => l_egdlertrg_set
235 );
236 hr_utility.set_location(' Dn LE EGD Cac ' || l_proc, 10);
237 --
238 if l_egdlertrg_set.count > 0 then
239 --
240 for ler_row in l_egdlertrg_set.first..l_egdlertrg_set.last loop
241 --
242 l_ler_id := l_egdlertrg_set(ler_row).ler_id;
243 l_typ_cd := l_egdlertrg_set(ler_row).typ_cd;
244 --
245 l_trigger := TRUE;
246 IF l_egdlertrg_set(ler_row).ocrd_dt_det_cd IS NULL THEN
247 l_lf_evt_ocrd_date := l_elig_strt_dt;
248 ELSE
249 --
250 -- Call the common date procedure.
251 --
252 ben_determine_date.main(
253 p_date_cd => l_egdlertrg_set(ler_row).ocrd_dt_det_cd,
254 p_effective_date => l_elig_strt_dt,
255 p_lf_evt_ocrd_dt => p_new.elig_strt_dt,
256 p_returned_date => l_lf_evt_ocrd_date);
257 END IF;
258 --
259 OPEN get_ler_col(l_egdlertrg_set(ler_row).ler_id);
260 <<get_ler_col_loop>>
261 LOOP
262 FETCH get_ler_col INTO l_column,
263 l_new_val,
264 l_old_val,
265 l_type,
266 l_per_info_chg_cs_ler_rl, l_rule_overrides_flag, l_chg_mandatory_cd;
267 EXIT get_ler_col_loop WHEN get_ler_col%NOTFOUND;
268 hr_utility.set_location('LER ' || l_egdlertrg_set(ler_row).ler_id, 20);
269 hr_utility.set_location('COLUMN ' || l_column, 20);
270 hr_utility.set_location('NEWVAL ' || l_new_val, 20);
271 hr_utility.set_location('OLDVAL ' || l_old_val, 20);
272 hr_utility.set_location('TYPE ' || l_type, 20);
273 hr_utility.set_location('CD ' || l_egdlertrg_set(ler_row).ocrd_dt_det_cd, 20);
274 hr_utility.set_location('create dt' || l_create_dt, 20);
275 hr_utility.set_location('elig strt dt ' || l_elig_strt_dt, 20);
276 hr_utility.set_location('elig thru dt ' || l_elig_thru_dt, 20);
277 l_changed := TRUE;
278 if get_ler_col%ROWCOUNT = 1 then
279 l_changed := TRUE;
280 END IF;
281 hr_utility.set_location(' ben_egd_trigger', 50);
282 --
283 -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
284 -- If it returns Y, then see the applicability of the data
285 -- changes based on new and old values.
286 --
287 l_rule_output := 'Y';
288 --
289 IF l_per_info_chg_cs_ler_rl IS NOT NULL THEN
290 --
291 IF l_column = 'DPNT_PERSON_ID' THEN
292 l_col_old_val := TO_CHAR(p_old.dpnt_person_id);
293 l_col_new_val := TO_CHAR(p_new.dpnt_person_id);
294 ELSIF l_column = 'ELIG_STRT_DT' THEN
295 l_col_old_val :=
296 TO_CHAR(p_old.elig_strt_dt, 'YYYY/MM/DD HH24:MI:SS');
297 l_col_new_val :=
298 TO_CHAR(p_new.elig_strt_dt, 'YYYY/MM/DD HH24:MI:SS');
299 ELSIF l_column = 'ELIG_THRU_DT' THEN
300 l_col_old_val :=
301 TO_CHAR(p_old.elig_thru_dt, 'YYYY/MM/DD HH24:MI:SS');
302 l_col_new_val :=
303 TO_CHAR(p_new.elig_thru_dt, 'YYYY/MM/DD HH24:MI:SS');
304 ELSIF l_column = 'DPNT_INELIG_FLAG' THEN
305 l_col_old_val := p_old.dpnt_inelig_flag;
306 l_col_new_val := p_new.dpnt_inelig_flag;
307 ELSIF l_column = 'OVRDN_FLAG' THEN
308 l_col_old_val := p_old.ovrdn_flag;
309 l_col_new_val := p_new.ovrdn_flag;
310 ELSIF l_column = 'CREATE_DT' THEN
311 l_col_old_val :=
312 TO_CHAR(p_old.create_dt, 'YYYY/MM/DD HH24:MI:SS');
313 l_col_new_val :=
314 TO_CHAR(p_new.create_dt, 'YYYY/MM/DD HH24:MI:SS');
315 ELSIF l_column = 'OVRDN_THRU_DT' THEN
316 l_col_old_val :=
317 TO_CHAR(p_old.ovrdn_thru_dt, 'YYYY/MM/DD HH24:MI:SS');
318 l_col_new_val :=
319 TO_CHAR(p_new.ovrdn_thru_dt, 'YYYY/MM/DD HH24:MI:SS');
320 ELSIF l_column = 'INELG_RSN_CD' THEN
321 l_col_old_val := p_old.inelg_rsn_cd;
322 l_col_new_val := p_new.inelg_rsn_cd;
323 ELSIF l_column = 'ELIG_PER_ELCTBL_CHC_ID' THEN
324 l_col_old_val := TO_CHAR(p_old.elig_per_elctbl_chc_id);
325 l_col_new_val := TO_CHAR(p_new.elig_per_elctbl_chc_id);
326 ELSIF l_column = 'PER_IN_LER_ID' THEN
327 l_col_old_val := TO_CHAR(p_old.per_in_ler_id);
328 l_col_new_val := TO_CHAR(p_new.per_in_ler_id);
329 ELSIF l_column = 'ELIG_PER_ID' THEN
330 l_col_old_val := TO_CHAR(p_old.elig_per_id);
331 l_col_new_val := TO_CHAR(p_new.elig_per_id);
332 ELSIF l_column = 'ELIG_PER_OPT_ID' THEN
333 l_col_old_val := TO_CHAR(p_old.elig_per_opt_id);
334 l_col_new_val := TO_CHAR(p_new.elig_per_opt_id);
335 ELSIF l_column = 'ELIG_CVRD_DPNT_ID' THEN
336 l_col_old_val := TO_CHAR(p_old.elig_cvrd_dpnt_id);
337 l_col_new_val := TO_CHAR(p_new.elig_cvrd_dpnt_id);
338 END IF;
339 --
340 benutils.exec_rule(
341 p_formula_id => l_per_info_chg_cs_ler_rl,
342 p_effective_date => l_session_date,
343 p_lf_evt_ocrd_dt => NULL,
344 p_business_group_id => l_rec_business_group_id,
345 p_person_id => l_rec_dpnt_person_id,
346 p_new_value => l_col_new_val,
347 p_old_value => l_col_old_val,
348 p_column_name => l_column,
349 p_param5 => 'BEN_EGD_IN_DPNT_PERSON_ID',
350 p_param5_value => TO_CHAR(p_new.dpnt_person_id),
351 p_param6 => 'BEN_EGD_IO_DPNT_PERSON_ID',
352 p_param6_value => TO_CHAR(p_old.dpnt_person_id),
353 p_param7 => 'BEN_EGD_IN_ELIG_STRT_DT',
354 p_param7_value => TO_CHAR(
355 p_new.elig_strt_dt,
356 'YYYY/MM/DD HH24:MI:SS'),
357 p_param8 => 'BEN_EGD_IO_ELIG_STRT_DT',
358 p_param8_value => TO_CHAR(
359 p_old.elig_strt_dt,
360 'YYYY/MM/DD HH24:MI:SS'),
361 p_param9 => 'BEN_EGD_IN_ELIG_THRU_DT',
362 p_param9_value => TO_CHAR(
363 p_new.elig_thru_dt,
364 'YYYY/MM/DD HH24:MI:SS'),
365 p_param10 => 'BEN_EGD_IO_ELIG_THRU_DT',
366 p_param10_value => TO_CHAR(
367 p_old.elig_thru_dt,
368 'YYYY/MM/DD HH24:MI:SS'),
369 p_param11 => 'BEN_EGD_IN_DPNT_INELIG_FLAG',
370 p_param11_value => p_new.dpnt_inelig_flag,
371 p_param12 => 'BEN_EGD_IO_DPNT_INELIG_FLAG',
372 p_param12_value => p_old.dpnt_inelig_flag,
373 p_param13 => 'BEN_EGD_IN_INELG_RSN_CD',
374 p_param13_value => p_new.inelg_rsn_cd,
375 p_param14 => 'BEN_EGD_IO_INELG_RSN_CD',
376 p_param14_value => p_old.inelg_rsn_cd,
377 p_param15 => 'BEN_EGD_IN_ELIG_PER_ELCTBL_CHC_ID',
378 p_param15_value => TO_CHAR(p_new.elig_per_elctbl_chc_id),
379 p_param16 => 'BEN_EGD_IO_ELIG_PER_ELCTBL_CHC_ID',
380 p_param16_value => TO_CHAR(p_old.elig_per_elctbl_chc_id),
381 p_param17 => 'BEN_EGD_IN_PER_IN_LER_ID',
382 p_param17_value => TO_CHAR(p_new.per_in_ler_id),
383 p_param18 => 'BEN_EGD_IO_PER_IN_LER_ID',
384 p_param18_value => TO_CHAR(p_old.per_in_ler_id),
385 p_param19 => 'BEN_EGD_IN_ELIG_PER_ID',
386 p_param19_value => TO_CHAR(p_new.elig_per_id),
387 p_param20 => 'BEN_EGD_IO_ELIG_PER_ID',
388 p_param20_value => TO_CHAR(p_old.elig_per_id),
389 p_param21 => 'BEN_EGD_IN_ELIG_PER_OPT_ID',
390 p_param21_value => TO_CHAR(p_new.elig_per_opt_id),
391 p_param22 => 'BEN_EGD_IO_ELIG_PER_OPT_ID',
392 p_param22_value => TO_CHAR(p_old.elig_per_opt_id),
393 p_param23 => 'BEN_EGD_IN_ELIG_CVRD_DPNT_ID',
394 p_param23_value => TO_CHAR(p_new.elig_cvrd_dpnt_id),
395 p_param24 => 'BEN_EGD_IO_ELIG_CVRD_DPNT_ID',
396 p_param24_value => TO_CHAR(p_old.elig_cvrd_dpnt_id),
397 p_param25 => 'BEN_IV_LER_ID', /* Bug 3891096 */
398 p_param25_value => to_char(l_ler_id),
399 p_pk_id => TO_CHAR(p_new.elig_dpnt_id),
400 p_ret_val => l_rule_output);
401 --
402 END IF;
403 --
404 --
405 IF l_column = 'ELIG_STRT_DT' THEN
406 l_changed :=
407 (
408 benutils.column_changed(
409 p_old.elig_strt_dt,
410 p_new.elig_strt_dt,
411 l_new_val)
412 AND benutils.column_changed(
413 p_new.elig_strt_dt,
414 p_old.elig_strt_dt,
415 l_old_val)
416 AND (l_changed));
417 hr_utility.set_location(' l_changed:', 40);
418 ELSIF l_column = 'ELIG_THRU_DT' THEN
419 l_changed :=
420 (
421 benutils.column_changed(
422 p_old.elig_thru_dt,
423 p_new.elig_thru_dt,
424 l_new_val)
425 AND benutils.column_changed(
426 p_new.elig_thru_dt,
427 p_old.elig_thru_dt,
428 l_old_val)
429 AND (l_changed));
430 --
431 IF l_egdlertrg_set(ler_row).ocrd_dt_det_cd IS NULL THEN
432 l_lf_evt_ocrd_date := l_elig_thru_dt;
433 ELSE
434 --
435 -- Call the common date procedure.
436 --
437 ben_determine_date.main(
438 p_date_cd => l_egdlertrg_set(ler_row).ocrd_dt_det_cd,
439 p_effective_date => l_elig_thru_dt,
440 p_lf_evt_ocrd_dt => p_new.elig_thru_dt,
441 p_returned_date => l_lf_evt_ocrd_date);
442 END IF;
443 --
444 ELSIF l_column = 'DPNT_INELIG_FLAG' THEN
445 l_changed :=
446 (
447 benutils.column_changed(
448 p_old.dpnt_inelig_flag,
449 p_new.dpnt_inelig_flag,
450 l_new_val)
451 AND benutils.column_changed(
452 p_new.dpnt_inelig_flag,
453 p_old.dpnt_inelig_flag,
454 l_old_val)
455 AND (l_changed));
456 --
457 IF l_egdlertrg_set(ler_row).ocrd_dt_det_cd IS NULL THEN
458 l_lf_evt_ocrd_date := l_elig_thru_dt;
459 ELSE
460 --
461 -- Call the common date procedure.
462 --
463 ben_determine_date.main(
464 p_date_cd => l_egdlertrg_set(ler_row).ocrd_dt_det_cd,
465 p_effective_date => l_elig_thru_dt,
466 p_lf_evt_ocrd_dt => p_new.elig_thru_dt,
467 p_returned_date => l_lf_evt_ocrd_date);
468 END IF;
469 --
470 ELSIF l_column = 'OVRDN_THRU_DT' THEN
471 l_changed :=
472 (
473 benutils.column_changed(
474 p_old.ovrdn_thru_dt,
475 p_new.ovrdn_thru_dt,
476 l_new_val)
477 AND benutils.column_changed(
478 p_new.ovrdn_thru_dt,
479 p_old.ovrdn_thru_dt,
480 l_old_val)
481 AND (l_changed));
482 --
483 IF l_egdlertrg_set(ler_row).ocrd_dt_det_cd IS NULL THEN
484 l_lf_evt_ocrd_date := l_create_dt;
485 ELSE
486 --
487 -- Call the common date procedure.
488 --
489 ben_determine_date.main(
490 p_date_cd => l_egdlertrg_set(ler_row).ocrd_dt_det_cd,
491 p_effective_date => l_create_dt,
492 p_lf_evt_ocrd_dt => p_new.create_dt,
493 p_returned_date => l_lf_evt_ocrd_date);
494 END IF;
495 --
496 ELSIF l_column = 'OVRDN_FLAG' THEN
497 hr_utility.set_location(
498 'Old ovrdn_flag ' || p_old.ovrdn_flag,
499 20);
500 hr_utility.set_location(
501 'New ovrdn_flag ' || p_new.ovrdn_flag,
502 20);
503 hr_utility.set_location('lodt ' || l_lf_evt_ocrd_date, 20);
504 l_changed :=
505 (
506 benutils.column_changed(
507 p_old.ovrdn_flag,
508 p_new.ovrdn_flag,
509 l_new_val)
510 AND benutils.column_changed(
511 p_new.ovrdn_flag,
512 p_old.ovrdn_flag,
513 l_old_val)
514 AND (l_changed));
515 --
516 IF l_egdlertrg_set(ler_row).ocrd_dt_det_cd IS NULL THEN
517 l_lf_evt_ocrd_date := l_create_dt;
518 ELSE
519 --
520 -- Call the common date procedure.
521 --
522 ben_determine_date.main(
523 p_date_cd => l_egdlertrg_set(ler_row).ocrd_dt_det_cd,
524 p_effective_date => l_create_dt,
525 p_lf_evt_ocrd_dt => p_new.create_dt,
526 p_returned_date => l_lf_evt_ocrd_date);
527 END IF;
528 END IF;
529 -- End of all Column checkings
530
531 --
532 --
533 -- Checking the rule output and the rule override flag.
534 -- Whether the rule is mandatory or not, rule output should return 'Y'
535 -- Rule Mandatory flag is just to override the column data change.
536
537 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
538 l_changed := TRUE ;
539 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
540 l_changed := l_changed AND TRUE;
541 elsif l_rule_output = 'N' then
542 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
543 l_changed := FALSE;
544 end if;
545
546 hr_utility.set_location('After the rule Check ',20.05);
547 if l_changed then
548 hr_utility.set_location(' l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
549 else
550 hr_utility.set_location(' l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
551 end if;
552 -- Check for Column Mandatory Change
553 -- If column change is mandatory and data change has failed then dont trigger
554 -- If column change is non-mandatory and the data change has passed, then trigger.
555
556 if l_chg_mandatory_cd = 'Y' and not l_changed then
557 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
558 l_changed := FALSE;
559 l_trigger := FALSE;
560 exit;
561 elsif l_chg_mandatory_cd = 'Y' and l_changed then
562 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
563 l_changed := TRUE;
564 -- exit; */
565 elsif l_chg_mandatory_cd = 'N' and l_changed then
566 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
567 l_changed := TRUE;
568 l_trigger := TRUE;
569 exit;
570 end if;
571
572 hr_utility.set_location('After the Mandatory code check ',20.05);
573 if l_changed then
574 hr_utility.set_location(' l_change TRUE ', 20.1);
575 else
576 hr_utility.set_location(' l_change FALSE ', 20.1);
577 end if;
578 --
579 /* if not l_changed then
580 exit;
581 end if; */
582
583 END LOOP get_ler_col_loop;
584 hr_utility.set_location(' ben_egd_trigger', 50);
585 l_ptnl_id := 0;
586 l_ovn := NULL;
587
588 --Bug 5630251
589 open get_person;
590 fetch get_person into l_person_id;
591
592 if get_person%notfound then
593 l_person_id := -999999999;
594 end if;
595
596 hr_utility.set_location('l_person_id' || l_person_id , 50);
597 hr_utility.set_location('per_in_ler_id' || p_new.per_in_ler_id, 50);
598
599 close get_person;
600 --End Bug 5630251
601
602 if l_trigger and (l_person_id <> -999999999) then --Bug 5630251
603 IF l_type = 'P' THEN
604 OPEN le_exists(l_person_id, l_ler_id, l_lf_evt_ocrd_date); --Bug 5630251
605 FETCH le_exists INTO l_le_exists;
606 IF le_exists%NOTFOUND THEN
607 hr_utility.set_location(' Entering: ben_egd_trigger5', 60);
608
609 ben_create_ptnl_ler_for_per.create_ptnl_ler_event(
610 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per(
611 p_validate => FALSE,
612 p_ptnl_ler_for_per_id => l_ptnl_id,
613 p_ntfn_dt => l_system_date,
614 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date,
615 p_ptnl_ler_for_per_stat_cd => 'DTCTD',
616 p_ler_id => l_ler_id,
617 p_ler_typ_cd => l_typ_cd,
618 p_person_id => l_person_id, --Bug 5630251
619 p_business_group_id => p_new.business_group_id,
620 p_object_version_number => l_ovn,
621 p_effective_date => l_effective_start_date,
622 p_dtctd_dt => l_effective_start_date);
623 END IF;
624 CLOSE le_exists;
625 ELSIF l_type = 'R' THEN
626 hr_utility.set_location(' Entering: ben_egd_trigger5-', 65);
627 OPEN get_contacts(l_person_id); --Bug 5630251
628 <<get_contacts_loop>>
629 LOOP
630 FETCH get_contacts INTO l_hld_person_id;
631 EXIT get_contacts_loop WHEN get_contacts%NOTFOUND;
632 OPEN le_exists(l_hld_person_id, l_ler_id, l_lf_evt_ocrd_date);
633 FETCH le_exists INTO l_le_exists;
634 IF le_exists%NOTFOUND THEN
635 hr_utility.set_location(' Entering: ben_egd_trigger5', 60);
636
637 ben_create_ptnl_ler_for_per.create_ptnl_ler_event(
638 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per(
639 p_validate => FALSE,
640 p_ptnl_ler_for_per_id => l_ptnl_id,
641 p_ntfn_dt => l_system_date,
642 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date,
643 p_ptnl_ler_for_per_stat_cd => 'DTCTD',
644 p_ler_id => l_ler_id,
645 p_ler_typ_cd => l_typ_cd,
646 p_person_id => l_hld_person_id,
647 p_business_group_id => p_new.business_group_id,
648 p_object_version_number => l_ovn,
649 p_effective_date => l_effective_start_date,
650 p_dtctd_dt => l_effective_start_date);
651 END IF;
652 l_ptnl_id := 0;
653 l_ovn := NULL;
654 CLOSE le_exists;
655 END LOOP get_contacts_loop;
656 CLOSE get_contacts;
657 END IF;
658 --
659 -- reset the variables.
660 --
661 l_changed := FALSE;
662 l_ovn := NULL;
663 l_trigger := TRUE;
664 l_effective_start_date := l_session_date;
665 -- l_lf_evt_ocrd_date := l_session_date;
666 END IF;
667 CLOSE get_ler_col;
668 END LOOP get_ler_loop;
669 hr_utility.set_location(' Dn get_ler loop ' || l_proc, 10);
670 -- END IF;
671 END IF;
672 END IF;
673 END ler_chk;
674
675 END ben_egd_ler;