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