[Home] [Help]
PACKAGE BODY: APPS.BEN_PEN_LER
Source
1 package body ben_pen_ler as
2 /* $Header: bepentrg.pkb 120.0 2005/05/28 10:37:16 appldev noship $*/
3 procedure ler_chk
4 (p_old IN g_pen_ler_rec
5 ,p_new IN g_pen_ler_rec
6 ,p_effective_date IN date default NULL
7 )
8 is
9 --
10 l_lertrg_set ben_letrg_cache.g_egdlertrg_inst_tbl;
11 --
12 l_session_date DATE;
13 l_system_date DATE;
14 --
15 cursor get_session_date IS
16 select effective_date
17 from fnd_sessions
18 where session_id = userenv('SESSIONID');
19 --
20 cursor get_system_date IS
21 select trunc(sysdate)
22 from dual;
23 --
24 cursor get_ler(l_status varchar2) is
25 select ler.ler_id
26 , ler.ocrd_dt_det_cd
27 from ben_ler_f ler
28 where ler.business_group_id = p_new.business_group_id
29 and l_session_date between ler.effective_start_date
30 and ler.effective_end_date -- For Bug 3299709
31 and ((exists
32 (select 1
33 from ben_per_info_chg_cs_ler_f psl
34 , ben_ler_per_info_cs_ler_f lpl
35 where source_table = 'BEN_PRTT_ENRT_RSLT_F'
36 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
37 and lpl.business_group_id = psl.business_group_id
38 and lpl.business_group_id = ler.business_group_id
39 and l_session_date between psl.effective_start_date
40 and psl.effective_end_date
41 and l_session_date between lpl.effective_start_date
42 and lpl.effective_end_date -- For Bug 3299709
43 and lpl.ler_id = ler.ler_id)
44 )
45 OR (exists
46 (select 1
47 from ben_rltd_per_chg_cs_ler_f rpc
48 , ben_ler_rltd_per_cs_ler_f lrp
49 where source_table = 'BEN_PRTT_ENRT_RSLT_F'
50 and lrp.business_group_id = rpc.business_group_id
51 and lrp.business_group_id = ler.business_group_id
52 and l_session_date between rpc.effective_start_date
53 and rpc.effective_end_date
54 and l_session_date between lrp.effective_start_date
55 and lrp.effective_end_date -- For Bug 3299709
56 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
57 and lrp.ler_id = ler.ler_id)
58 ))
59 order by ler.ler_id;
60 --
61 cursor get_ler_col(p_ler_id IN NUMBER) is
62 select psl.source_column, psl.new_val, psl.old_val, 'P', psl.per_info_chg_cs_ler_rl, psl.rule_overrides_flag, lpl.chg_mandatory_cd
63 from ben_ler_per_info_cs_ler_f lpl, ben_per_info_chg_cs_ler_f psl
64 where lpl.ler_id = p_ler_id and
65 lpl.business_group_id = p_new.business_group_id
66 and lpl.business_group_id = psl.business_group_id
67 and l_session_date between psl.effective_start_date
68 and psl.effective_end_date
69 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
70 and source_table = 'BEN_PRTT_ENRT_RSLT_F'
71 UNION
72 select rpc.source_column, rpc.new_val, rpc.old_val, 'R', rpc.rltd_per_chg_cs_ler_rl per_info_chg_cs_ler, rpc.rule_overrides_flag, lrp.chg_mandatory_cd
73 from ben_ler_rltd_per_cs_ler_f lrp, ben_rltd_per_chg_cs_ler_f rpc
74 where lrp.ler_id = p_ler_id and
75 lrp.business_group_id = p_new.business_group_id
76 and lrp.business_group_id = rpc.business_group_id
77 and l_session_date between rpc.effective_start_date
78 and rpc.effective_end_date
79 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
80 and source_table = 'BEN_PRTT_ENRT_RSLT_F'
81 order by 1;
82 --
83 cursor le_exists(p_person_id in number
84 ,p_ler_id in number
85 ,p_lf_evt_ocrd_dt in date) is
86 select 'Y'
87 from ben_ptnl_ler_for_per
88 where person_id = p_person_id
89 and ler_id = p_ler_id
90 and ptnl_ler_for_per_stat_cd = 'DTCTD'
91 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
92 --
93 cursor get_contacts(p_person_id in number) is
94 select contact_person_id
95 from per_contact_relationships
96 where person_id = p_person_id
97 and business_group_id = p_new.business_group_id
98 and l_session_date between nvl(date_start,l_session_date)
99 and nvl(date_end,l_session_date)
100 order by person_id;
101 --
102 l_changed BOOLEAN;
103 l_column ben_rltd_per_chg_cs_ler_f.source_column%type; -- VARCHAR2(30);
104 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type; -- VARCHAR2(30);
105 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type; -- VARCHAR2(30);
106 l_per_info_chg_cs_ler_rl number;
107 l_rule_output VARCHAR2(1);
108 l_ovn NUMBER;
109 l_ptnl_id NUMBER;
110 l_effective_end_date DATE := to_date('31-12-4712','DD-MM-YYYY');
111 l_effective_start_date DATE ;
112 --l_session_date DATE ;
113 l_lf_evt_ocrd_date DATE ;
114 l_le_exists VARCHAR2(1);
115 l_mnl_dt date;
116 l_dtctd_dt date;
117 l_type VARCHAR2(1);
118 l_hld_person_id NUMBER;
119 --
120 l_bool BOOLEAN;
121 l_status VARCHAR2(1);
122 l_industry VARCHAR2(1);
123 l_col_new_val VARCHAR2(1000);
124 l_col_old_val varchar2(1000);
125 --
126 l_rule_overrides_flag VARCHAR2(1);
127 l_chg_mandatory_cd VARCHAR2(1);
128 l_trigger boolean := TRUE;
129 --
130 --
131 begin
132
133 -- Bug 3320133
134 benutils.set_data_migrator_mode;
135 if hr_general.g_data_migrator_mode in ( 'Y','P') then
136 --
137 return;
138 --
139 end if;
140 --
141 -- End of Bug 3320133
142
143 hr_utility.set_location(' Entering: ben_pen_trigger', 10);
144 l_changed := FALSE;
145 /*
146 open get_session_date;
147 fetch get_session_date into l_session_date;
148 close get_session_date;
149 */
150 --
151 if p_effective_date is not null then
152 --
153 l_session_date := p_effective_date ;
154 --
155 else
156 --
157 open get_session_date;
158 fetch get_session_date into l_session_date;
159 close get_session_date;
160 --
161 end if;
162 --
163 open get_system_date;
164 fetch get_system_date into l_system_date;
165 close get_system_date;
166 l_effective_start_date := l_session_date;
167 --
168 -- Get the ler details list
169 --
170 ben_letrg_cache.get_penlertrg_dets
171 (p_business_group_id => p_new.business_group_id
172 ,p_effective_date => l_session_date
173 ,p_inst_set => l_lertrg_set
174 );
175 hr_utility.set_location(' ben_pen_trigger', 20);
176 --
177 if l_lertrg_set.count > 0 then
178 --
179 for ler_row in l_lertrg_set.first..l_lertrg_set.last loop
180 /*
181 open get_ler(l_status);
182 loop
183 fetch get_ler into l_ler_id, l_ocrd_dt_cd;
184 exit when get_ler%notfound;
185
186 */
187 --
188 l_trigger := TRUE;
189 if l_lertrg_set(ler_row).ocrd_dt_det_cd is null then
190 l_lf_evt_ocrd_date := p_new.enrt_cvg_strt_dt;
191 else
192 --
193 -- Call the common date procedure.
194 --
195 ben_determine_date.main
196 (p_date_cd => l_lertrg_set(ler_row).ocrd_dt_det_cd
197 ,p_effective_date => p_new.enrt_cvg_strt_dt
198 ,p_lf_evt_ocrd_dt => p_new.enrt_cvg_strt_dt
199 ,p_returned_date => l_lf_evt_ocrd_date
200 );
201 end if;
202 --
203 open get_ler_col(l_lertrg_set(ler_row).ler_id);
204 loop
205 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;
206 exit when get_ler_col%NOTFOUND;
207 hr_utility.set_location('LER '||l_lertrg_set(ler_row).ler_id, 20);
208 hr_utility.set_location('COLUMN '||l_column, 20);
209 hr_utility.set_location('NEWVAL '||l_new_val, 20);
210 hr_utility.set_location('OLDVAL '||l_old_val, 20);
211 hr_utility.set_location('TYPE '||l_type, 20);
212 hr_utility.set_location('CD '||l_lertrg_set(ler_row).ocrd_dt_det_cd, 20);
213 hr_utility.set_location('enrt cvg strt dt'||p_new.enrt_cvg_strt_dt, 20);
214 hr_utility.set_location('enrt cvg thru dt '||p_new.enrt_cvg_thru_dt, 20);
215 l_changed := TRUE;
216 if get_ler_col%ROWCOUNT = 1 then
217 l_changed := TRUE;
218 end if;
219 hr_utility.set_location(' ben_pen_trigger', 50);
220 --
221 -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
222 -- If it returns Y, then see the applicability of the data
223 -- changes based on new and old values.
224 --
225 l_rule_output := 'Y';
226 --
227 if l_per_info_chg_cs_ler_rl is not null then
228 --
229 if l_column = 'PERSON_ID' then
230 l_col_old_val := to_char(p_old.PERSON_ID);
231 l_col_new_val := to_char(p_new.PERSON_ID);
232 end if;
233 --
234 if l_column = 'BNFT_AMT' then
235 l_col_old_val := to_char(p_old.BNFT_AMT);
236 l_col_new_val := to_char(p_new.BNFT_AMT);
237 end if;
238 --
239 if l_column = 'ENRT_CVG_STRT_DT' then
240 l_col_old_val := to_char(p_old.ENRT_CVG_STRT_DT, 'YYYY/MM/DD HH24:MI:SS');
241 l_col_new_val := to_char(p_new.ENRT_CVG_STRT_DT, 'YYYY/MM/DD HH24:MI:SS');
242 end if;
243 --
244 if l_column = 'ENRT_CVG_THRU_DT' then
245 l_col_old_val := to_char(p_old.ENRT_CVG_THRU_DT, 'YYYY/MM/DD HH24:MI:SS');
246 l_col_new_val := to_char(p_new.ENRT_CVG_THRU_DT, 'YYYY/MM/DD HH24:MI:SS');
247 end if;
248 --
249 if l_column = 'EFFECTIVE_START_DATE' then
250 l_col_old_val := to_char(p_old.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS');
251 l_col_new_val := to_char(p_new.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS');
252 end if;
253 --
254 if l_column = 'EFFECTIVE_END_DATE' then
255 l_col_old_val := to_char(p_old.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS');
256 l_col_new_val := to_char(p_new.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS');
257 end if;
258 --
259 benutils.exec_rule(
260 p_formula_id => l_per_info_chg_cs_ler_rl,
261 p_effective_date => l_session_date,
262 p_lf_evt_ocrd_dt => null,
263 p_business_group_id => nvl(p_new.business_group_id, p_old.business_group_id),
264 p_person_id => nvl(p_new.person_id, p_old.person_id),
265 p_new_value => l_col_new_val,
266 p_old_value => l_col_old_val,
267 p_column_name => l_column,
268 p_param5 => 'BEN_PEN_IN_ENRT_CVG_STRT_DT',
269 p_param5_value => to_char(p_new.ENRT_CVG_STRT_DT, 'YYYY/MM/DD HH24:MI:SS'),
270 p_param6 => 'BEN_PEN_IO_ENRT_CVG_STRT_DT',
271 p_param6_value => to_char(p_old.ENRT_CVG_STRT_DT, 'YYYY/MM/DD HH24:MI:SS'),
272 p_param7 => 'BEN_PEN_IN_ENRT_CVG_THRU_DT',
273 p_param7_value => to_char(p_new.ENRT_CVG_THRU_DT, 'YYYY/MM/DD HH24:MI:SS'),
274 p_param8 => 'BEN_PEN_IO_ENRT_CVG_THRU_DT',
275 p_param8_value => to_char(p_old.ENRT_CVG_THRU_DT, 'YYYY/MM/DD HH24:MI:SS'),
276 p_param9 => 'BEN_PEN_IN_EFFECTIVE_START_DATE',
277 p_param9_value => to_char(p_new.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS'),
278 p_param10 => 'BEN_PEN_IO_EFFECTIVE_START_DATE',
279 p_param10_value => to_char(p_old.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS'),
280 p_param11 => 'BEN_PEN_IN_EFFECTIVE_END_DATE',
281 p_param11_value => to_char(p_new.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS'),
282 p_param12 => 'BEN_PEN_IO_EFFECTIVE_END_DATE',
283 p_param12_value => to_char(p_old.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS'),
284 p_param13 => 'BEN_IV_LER_ID', /* Bug 3891096 */
285 p_param13_value => to_char(l_lertrg_set(ler_row).ler_id),
286 p_pk_id => to_char(p_new.prtt_enrt_rslt_id),
287 p_ret_val => l_rule_output);
288 --
289 end if;
290 --
291
292 --
293 if l_column = 'ENRT_CVG_STRT_DT' then
294 l_changed := (benutils.column_changed(p_old.enrt_cvg_strt_dt
295 ,p_new.enrt_cvg_strt_dt,l_new_val) AND
296 benutils.column_changed(p_new.enrt_cvg_strt_dt
297 ,p_old.enrt_cvg_strt_dt,l_old_val) AND
298 (l_changed));
299 hr_utility.set_location(' l_changed:',40);
300 end if;
301 --
302 if l_column = 'ENRT_CVG_THRU_DT' then
303 if p_new.enrt_cvg_thru_dt = hr_api.g_eot then
304 l_changed := FALSE;
305 end if;
306 l_changed := (benutils.column_changed(p_old.enrt_cvg_thru_dt
307 ,p_new.enrt_cvg_thru_dt,l_new_val) AND
308 benutils.column_changed(p_new.enrt_cvg_thru_dt
309 ,p_old.enrt_cvg_thru_dt,l_old_val) AND
310 (l_changed));
311 --
312 if l_lertrg_set(ler_row).ocrd_dt_det_cd is null then
313 l_lf_evt_ocrd_date := p_new.enrt_cvg_thru_dt;
314 else
315 --
316 -- Call the common date procedure.
317 --
318 ben_determine_date.main
319 (p_date_cd => l_lertrg_set(ler_row).ocrd_dt_det_cd
320 ,p_effective_date => p_new.enrt_cvg_thru_dt
321 ,p_lf_evt_ocrd_dt => p_new.enrt_cvg_thru_dt
322 ,p_returned_date => l_lf_evt_ocrd_date
323 );
324 end if;
325 --
326 end if;
327 --
328 if l_column = 'BNFT_AMT' then
329 hr_utility.set_location('Old bnft_amt '||p_old.bnft_amt, 20);
330 hr_utility.set_location('New bnft_amt '||p_new.bnft_amt, 20);
331 hr_utility.set_location('lodt '||l_lf_evt_ocrd_date, 20);
332 l_changed := (benutils.column_changed(p_old.bnft_amt
333 ,p_new.bnft_amt,l_new_val) AND
334 benutils.column_changed(p_new.bnft_amt
335 ,p_old.bnft_amt,l_old_val) AND
336 (l_changed));
337 end if;
338 -- Checking the rule output and the rule override flag.
339 -- Whether the rule is mandatory or not, rule output should return 'Y'
340 -- Rule Mandatory flag is just to override the column data change.
341
342 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
343 l_changed := TRUE ;
344 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
345 l_changed := l_changed AND TRUE;
346 elsif l_rule_output = 'N' then
347 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
348 l_changed := FALSE;
349 end if;
350
351 hr_utility.set_location('After the rule Check ',20.05);
352 if l_changed then
353 hr_utility.set_location(' l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
354 else
355 hr_utility.set_location(' l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
356 end if;
357 -- Check for Column Mandatory Change
358 -- If column change is mandatory and data change has failed then dont trigger
359 -- If column change is non-mandatory and the data change has passed, then trigger.
360
361 if l_chg_mandatory_cd = 'Y' and not l_changed then
362 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
363 l_changed := FALSE;
364 l_trigger := FALSE;
365 exit;
366 elsif l_chg_mandatory_cd = 'Y' and l_changed then
367 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
368 l_changed := TRUE;
369 -- exit; */
370 elsif l_chg_mandatory_cd = 'N' and l_changed then
371 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
372 l_changed := TRUE;
373 l_trigger := TRUE;
374 exit;
375 end if;
376
377 hr_utility.set_location('After the Mandatory code check ',20.05);
378 if l_changed then
379 hr_utility.set_location(' l_change TRUE ', 20.1);
380 else
381 hr_utility.set_location(' l_change FALSE ', 20.1);
382 end if;
383 --
384
385 /* if not l_changed then
386 exit;
387 end if; */
388 --
389 end loop;
390 hr_utility.set_location(' ben_pen_trigger', 50);
391 l_ptnl_id := 0;
392 l_ovn :=null;
393 if l_trigger then
394 if l_type = 'P' then
395 open le_exists(p_new.person_id,l_lertrg_set(ler_row).ler_id,l_lf_evt_ocrd_date);
396 fetch le_exists into l_le_exists;
397 if le_exists%notfound then
398 hr_utility.set_location(' Entering: ben_pen_trigger5', 60);
399
400 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
401 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
402 (p_validate => false
403 ,p_ptnl_ler_for_per_id => l_ptnl_id
404 ,p_ntfn_dt => l_system_date
405 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
406 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
407 ,p_ler_id => l_lertrg_set(ler_row).ler_id
408 ,p_ler_typ_cd => l_lertrg_set(ler_row).typ_cd
409 ,p_person_id => p_new.person_id
410 ,p_business_group_Id =>p_new.business_group_id
411 ,p_object_version_number => l_ovn
412 ,p_effective_date => l_effective_start_date
413 ,p_dtctd_dt => l_effective_start_date);
414 end if;
415 close le_exists;
416 elsif l_type = 'R' then
417 hr_utility.set_location(' Entering: ben_pen_trigger5-', 65);
418 open get_contacts(p_new.person_id);
419 loop
420 fetch get_contacts into l_hld_person_id;
421 exit when get_contacts%notfound;
422 open le_exists(l_hld_person_id,l_lertrg_set(ler_row).ler_id,l_lf_evt_ocrd_date);
423 fetch le_exists into l_le_exists;
424 if le_exists%notfound then
425 hr_utility.set_location(' Entering: ben_pen_trigger5', 60);
426 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
427 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
428 (p_validate => false
429 ,p_ptnl_ler_for_per_id => l_ptnl_id
430 ,p_ntfn_dt => l_system_date
431 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
432 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
433 ,p_ler_id => l_lertrg_set(ler_row).ler_id
434 ,p_ler_typ_cd => l_lertrg_set(ler_row).typ_cd
435 ,p_person_id => l_hld_person_id
436 ,p_business_group_Id =>p_new.business_group_id
437 ,p_object_version_number => l_ovn
438 ,p_effective_date => l_effective_start_date
439 ,p_dtctd_dt => l_effective_start_date);
440 end if;
441 l_ptnl_id := 0;
442 l_ovn :=null;
443 close le_exists;
444 end loop;
445 close get_contacts;
446 end if;
447 --
448 -- reset the variables.
449 --
450 hr_utility.set_location(' ben_pen_trigger', 40);
451 l_changed := FALSE;
452 l_trigger := TRUE;
453 l_ovn := NULL;
454 l_effective_start_date := l_session_date;
455 -- l_lf_evt_ocrd_date := l_session_date;
456 end if;
457 close get_ler_col;
458 end loop;
459 hr_utility.set_location(' ben_pen_trigger', 50);
460 end if;
461
462 hr_utility.set_location(' leaving ben_pen_trigger', 60);
463
464 end;
465 end ben_pen_ler;