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