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