[Home] [Help]
PACKAGE BODY: APPS.BEN_ADD_LER
Source
4 ,p_new IN g_add_ler_rec
1 package body ben_add_ler as
2 /* $Header: beaddtrg.pkb 120.0 2005/05/28 00:20:58 appldev noship $*/
3 procedure ler_chk(p_old IN g_add_ler_rec
5 ,p_effective_date in date ) is
6 --
7 l_session_date 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
26 between ler.effective_start_date
27 and ler.effective_end_date
28 and ( l_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
29 and ((exists
30 (select 1
31 from ben_per_info_chg_cs_ler_f psl
32 , ben_ler_per_info_cs_ler_f lpl
33 where source_table = 'PER_ADDRESSES'
34 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
35 and lpl.business_group_id = psl.business_group_id
36 and lpl.business_group_id = ler.business_group_id
37 and l_session_date between psl.effective_start_date
38 and psl.effective_end_date
39 and l_session_date between lpl.effective_start_date
40 and lpl.effective_end_date
41 and lpl.ler_id = ler.ler_id)
42 )
43 OR (exists
44 (select 1
45 from ben_rltd_per_chg_cs_ler_f rpc
46 , ben_ler_rltd_per_cs_ler_f lrp
47 where source_table = 'PER_ADDRESSES'
48 and lrp.business_group_id = rpc.business_group_id
49 and lrp.business_group_id = ler.business_group_id
53 and lrp.effective_end_date
50 and l_session_date between rpc.effective_start_date
51 and rpc.effective_end_date
52 and l_session_date between lrp.effective_start_date
54 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
55 and lrp.ler_id = ler.ler_id)
56 ))
57 order by ler.ler_id;
58 --
59 cursor get_ler_col(p_ler_id IN NUMBER) is
60 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
61 from ben_ler_per_info_cs_ler_f lpl, ben_per_info_chg_cs_ler_f psl
62 where lpl.ler_id = p_ler_id and
63 lpl.business_group_id = p_new.business_group_id
64 and lpl.business_group_id = psl.business_group_id
65 and l_session_date between psl.effective_start_date
66 and psl.effective_end_date
67 and l_session_date between lpl.effective_start_date
68 and lpl.effective_end_date
69 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
70 and source_table = 'PER_ADDRESSES'
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 l_session_date between lrp.effective_start_date
80 and lrp.effective_end_date
81 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
82 and source_table = 'PER_ADDRESSES'
83 order by 1;
84 --
85 cursor le_exists(p_person_id in number
86 ,p_ler_id in number
87 ,p_lf_evt_ocrd_dt in date) is
88 select 'Y'
89 from ben_ptnl_ler_for_per
90 where person_id = p_person_id
91 and ler_id = p_ler_id
92 and ptnl_ler_for_per_stat_cd = 'DTCTD'
93 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
94 --
95 cursor get_contacts(p_person_id in number) is
96 select person_id
97 from per_contact_relationships
98 where contact_person_id = p_person_id
99 and business_group_id = p_new.business_group_id
100 and l_session_date between nvl(date_start,l_session_date)
101 and nvl(date_end,l_session_date)
102 and personal_flag = 'Y'
103 order by person_id;
104 --
105
106 l_changed BOOLEAN;
107 l_ler_id NUMBER;
108 l_typ_cd ben_ler_f.typ_cd%type ;
109 l_column ben_rltd_per_chg_cs_ler_f.source_column%type; -- VARCHAR2(30);
110 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type; -- VARCHAR2(30);
111 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type; -- VARCHAR2(30);
112 l_ocrd_dt_cd VARCHAR2(30);
113 l_per_info_chg_cs_ler_rl number;
114 l_ovn NUMBER;
115 l_ptnl_id NUMBER;
116 l_effective_end_date DATE := to_date('31-12-4712','DD-MM-YYYY');
117 l_effective_start_date DATE ;
118 --l_session_date DATE ;
119 l_lf_evt_ocrd_date DATE ;
120 l_le_exists VARCHAR2(1);
121 l_mnl_dt date;
122 l_dtctd_dt date;
123 l_procd_dt date;
124 l_unprocd_dt date;
125 l_voidd_dt date;
126 l_type VARCHAR2(1);
127 l_hld_person_id NUMBER;
128 l_date_to date;
129 l_rule_output VARCHAR2(1);
130 --
131 l_bool BOOLEAN;
132 l_status VARCHAR2(1);
133 l_industry VARCHAR2(1);
134 l_col_new_val VARCHAR2(240); --UTF8
135 l_col_old_val VARCHAR2(240); --UTF8
136 --
137 --
138 l_rule_overrides_flag VARCHAR2(1);
139 l_chg_mandatory_cd VARCHAR2(1);
140 l_trigger boolean := TRUE;
141 --
142 begin
143 -- Bug 2016857
144 benutils.set_data_migrator_mode;
145 --
146 if hr_general.g_data_migrator_mode in ( 'Y','P') then
147 --
148 return;
149 --
150 end if;
151 --
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_add_trigger', 10);
158 l_changed := FALSE;
159 if p_effective_date is not null then
160 l_session_date := p_effective_Date ;
161 Else
162 open get_session_date;
163 fetch get_session_date into l_session_date;
164 close get_session_date;
165 End if ;
166 open get_system_date;
167 fetch get_system_date into l_system_date;
168 close get_system_date;
169 l_effective_start_date := l_session_date;
170 -- l_lf_evt_ocrd_date := l_session_date;
171 hr_utility.set_location(' ben_add_trigger', 20);
172
173 if p_new.date_to is null then
174 l_date_to := l_session_date;
175 else
179 hr_utility.set_location(' l_system_date:'||to_char(l_system_date), 20);
176 l_date_to := p_new.date_to;
177 end if;
178
180 hr_utility.set_location(' l_session_date:'||to_char(l_session_date), 20);
181
182 open get_ler(l_status);
183 loop
184 --
185 fetch get_ler into l_ler_id,l_typ_cd, l_ocrd_dt_cd;
186 exit when get_ler%notfound;
187 l_trigger := TRUE;
188
189 hr_utility.set_location('ler '||l_ler_id, 20);
190 hr_utility.set_location('det_cd '||l_ocrd_dt_cd, 20);
191 hr_utility.set_location('DR system date '||l_system_date, 20);
192 hr_utility.set_location('date from '||p_new.date_from, 20);
193 --
194 if l_ocrd_dt_cd is null then
195 l_lf_evt_ocrd_date := p_new.date_from;
196 else
197 --
198 -- Call the common date procedure.
199 --
200 ben_determine_date.main
201 (p_date_cd => l_ocrd_dt_cd
202 ,p_effective_date => p_new.date_from
203 ,p_lf_evt_ocrd_dt => p_new.date_from
204 ,p_returned_date => l_lf_evt_ocrd_date
205 );
206 end if;
207 --
208 open get_ler_col(l_ler_id);
209 loop
210 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;
211 exit when get_ler_col%NOTFOUND;
212
213 l_changed := TRUE;
214 if get_ler_col%ROWCOUNT = 1 then
215 l_changed := TRUE;
216 end if;
217 l_changed := TRUE;
218 hr_utility.set_location(' ben_add_trigger', 50);
219 --
220 -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
221 -- If it returns Y, then see the applicability of the data
222 -- changes based on new and old values.
223 --
224 l_rule_output := 'Y';
225 --
226 if l_per_info_chg_cs_ler_rl is not null then
227 --
228 if l_column = 'DATE_FROM' then
229 l_col_old_val := to_char(p_old.date_from, 'YYYY/MM/DD HH24:MI:SS');
230 l_col_new_val := to_char(p_new.date_from, 'YYYY/MM/DD HH24:MI:SS');
231 end if;
232 --
233 if l_column = 'DATE_TO' then
234 l_col_old_val := to_char(p_old.date_to, 'YYYY/MM/DD HH24:MI:SS');
235 l_col_new_val := to_char(p_new.date_to, 'YYYY/MM/DD HH24:MI:SS');
236 end if;
237 --
238 if l_column = 'PRIMARY_FLAG' then
239 l_col_old_val := p_old.PRIMARY_FLAG;
240 l_col_new_val := p_new.PRIMARY_FLAG;
241 end if;
242 --
243 --
244 if l_column = 'POSTAL_CODE' then
245 l_col_old_val := p_old.postal_code;
246 l_col_new_val := p_new.postal_code;
247 end if;
248 --
249 if l_column = 'REGION_2' then
250 l_col_old_val := p_old.region_2;
251 l_col_new_val := p_new.region_2;
252 end if;
253 --
254 if l_column = 'ADDRESS_TYPE' then
255 l_col_old_val := p_old.address_type;
256 l_col_new_val := p_new.address_type;
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_pk_id => to_char(p_new.address_id),
269 p_param18 => 'BEN_ADR_IN_DATE_FROM',
270 p_param18_value => to_char(p_new.DATE_FROM, 'YYYY/MM/DD HH24:MI:SS'),
271 p_param5 => 'BEN_ADR_IN_DATE_TO',
272 p_param5_value => to_char(p_new.DATE_TO,'YYYY/MM/DD HH24:MI:SS'),
273 p_param6 => 'BEN_ADR_IN_PRIMARY_FLAG',
274 p_param6_value => p_new.PRIMARY_FLAG,
275 p_param7 => 'BEN_ADR_IN_POSTAL_CODE',
276 p_param7_value => p_new.POSTAL_CODE,
277 p_param8 => 'BEN_ADR_IN_REGION_2',
278 p_param8_value => p_new.REGION_2,
279 p_param9 => 'BEN_ADR_IN_ADDRESS_TYPE',
280 p_param9_value => p_new.ADDRESS_TYPE,
281 p_param10 => 'BEN_ADR_IO_POSTAL_CODE',
282 p_param10_value => p_old.POSTAL_CODE,
283 p_param11 => 'BEN_ADR_IO_DATE_FROM',
284 p_param11_value => to_char(p_old.DATE_FROM, 'YYYY/MM/DD HH24:MI:SS'),
285 p_param12 => 'BEN_ADR_IO_DATE_TO',
286 p_param12_value => to_char(p_old.DATE_TO,'YYYY/MM/DD HH24:MI:SS'),
287 p_param13 => 'BEN_ADR_IO_PRIMARY_FLAG',
288 p_param13_value => p_old.PRIMARY_FLAG,
289 p_param14 => 'BEN_ADR_IO_REGION_2',
290 p_param14_value => p_old.REGION_2,
291 p_param15 => 'BEN_ADR_IO_ADDRESS_TYPE',
292 p_param15_value => p_old.ADDRESS_TYPE,
293 p_param16 => 'BEN_ADR_IN_PERSON_ID',
294 p_param16_value => to_char(p_new.PERSON_ID),
295 p_param17 => 'BEN_ADR_IO_PERSON_ID',
296 p_param17_value => to_char(p_old.PERSON_ID),
297 p_param19 => 'BEN_IV_LER_ID', /* Bug 3891096 */
301 end if;
298 p_param19_value => to_char(l_ler_id),
299 p_ret_val => l_rule_output);
300 --
302 --
303
304 --
305 if l_column = 'DATE_FROM' then
306 l_changed := (benutils.column_changed(p_old.date_from
307 ,p_new.date_from,l_new_val) AND
308 benutils.column_changed(p_new.date_from
309 ,p_old.date_from,l_old_val) AND
310 (l_changed));
311 hr_utility.set_location(' l_changed:',40);
312 end if;
313 --
314 if l_column = 'DATE_TO' then
315 l_changed := (benutils.column_changed(p_old.date_to
316 ,p_new.date_to,l_new_val) AND
317 benutils.column_changed(p_new.date_to
318 ,p_old.date_to,l_old_val) AND
319 (l_changed));
320 --
321 if l_ocrd_dt_cd is null then
322 l_lf_evt_ocrd_date := l_date_to;
323 else
324 --
325 -- Call the common date procedure.
326 --
327 ben_determine_date.main
328 (p_date_cd => l_ocrd_dt_cd
329 ,p_effective_date => l_date_to
330 ,p_lf_evt_ocrd_dt => p_new.date_to
331 ,p_returned_date => l_lf_evt_ocrd_date
332 );
333 end if;
334 --
335 end if;
336 --
337 if l_column = 'PRIMARY_FLAG' then
338 l_changed := (benutils.column_changed(p_old.primary_flag
339 ,p_new.primary_flag,l_new_val) AND
340 benutils.column_changed(p_new.primary_flag
341 ,p_old.primary_flag,l_old_val) AND
342 (l_changed));
343 end if;
344 --
345 if l_column = 'POSTAL_CODE' then
346 l_changed := (benutils.column_changed(p_old.postal_code
347 ,p_new.postal_code,l_new_val) AND
348 benutils.column_changed(p_new.postal_code
349 ,p_old.postal_code,l_old_val) );
350 end if;
351 --
352 if l_column = 'REGION_2' then
353 l_changed := (benutils.column_changed(p_old.region_2
354 ,p_new.region_2,l_new_val) AND
355 benutils.column_changed(p_new.region_2
356 ,p_old.region_2,l_old_val) );
357 end if;
358 --
359 if l_column = 'ADDRESS_TYPE' then
360 l_changed := (benutils.column_changed(p_old.address_type
361 ,p_new.address_type,l_new_val) AND
362 benutils.column_changed(p_new.address_type
363 ,p_old.address_type,l_old_val) );
364 end if;
365 --
366
367 --
368 -- Checking the rule output and the rule override flag.
369 -- Whether the rule is mandatory or not, rule output should return 'Y'
370 -- Rule Mandatory flag is just to override the column data change.
371
372 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
373 l_changed := TRUE ;
374 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
375 l_changed := l_changed AND TRUE;
376 elsif l_rule_output = 'N' then
377 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
378 l_changed := FALSE;
379 end if;
380
381 hr_utility.set_location('After the rule Check ',20.05);
382 if l_changed then
383 hr_utility.set_location(' l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
384 else
385 hr_utility.set_location(' l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
386 end if;
387 -- Check for Column Mandatory Change
388 -- If column change is mandatory and data change has failed then dont trigger
389 -- If column change is non-mandatory and the data change has passed, then trigger.
390
391 if l_chg_mandatory_cd = 'Y' and not l_changed then
392 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
393 l_changed := FALSE;
394 l_trigger := FALSE;
395 exit;
396 elsif l_chg_mandatory_cd = 'Y' and l_changed then
397 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
398 l_changed := TRUE;
399 -- exit; */
400 elsif l_chg_mandatory_cd = 'N' and l_changed then
401 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
402 l_changed := TRUE;
403 l_trigger := TRUE;
404 exit;
405 end if;
406 hr_utility.set_location('After the Mandatory code check ',20.05);
407 if l_changed then
408 hr_utility.set_location(' l_change TRUE ', 20.1);
409 else
410 hr_utility.set_location(' l_change FALSE ', 20.1);
411 end if;
412 --
413 /* if not l_changed then
414 exit;
415 end if; */
416 --
417 end loop;
418 hr_utility.set_location(' ben_add_trigger', 50);
419 l_ptnl_id := 0;
420 l_ovn :=null;
421 --
422 if l_changed then
423 hr_utility.set_location(' l_changed = TRUE' || l_type, 9999);
424 else
425 hr_utility.set_location(' l_changed = TRUE' || l_type, 9999);
426 end if;
427 if l_trigger then
428 if l_type = 'P' then
432 hr_utility.set_location(' Entering: ben_add_trigger5', 60);
429 open le_exists(p_new.person_id,l_ler_id,l_lf_evt_ocrd_date);
430 fetch le_exists into l_le_exists;
431 if le_exists%notfound then
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 => p_new.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_effective_start_date);
448 end if;
449 close le_exists;
450 elsif l_type = 'R' then
451 hr_utility.set_location(' Entering: ben_add_trigger5-', 65);
452 open get_contacts(p_new.person_id);
453 loop
454 fetch get_contacts into l_hld_person_id;
455 exit when get_contacts%notfound;
456 open le_exists(l_hld_person_id,l_ler_id,l_lf_evt_ocrd_date);
457 fetch le_exists into l_le_exists;
458 if le_exists%notfound then
459 hr_utility.set_location(' Entering: ben_add_trigger5', 60);
460
461 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
462 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
463 (p_validate => false
464 ,p_ptnl_ler_for_per_id => l_ptnl_id
465 ,p_ntfn_dt => l_system_date
466 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
467 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
468 ,p_ler_id => l_ler_id
469 ,p_ler_typ_cd => l_typ_cd
470 ,p_person_id => l_hld_person_id
471 ,p_business_group_Id =>p_new.business_group_id
472 ,p_object_version_number => l_ovn
473 ,p_effective_date => l_effective_start_date
474 ,p_dtctd_dt => l_effective_start_date);
475 end if;
476 l_ptnl_id := 0;
477 l_ovn :=null;
478 close le_exists;
479 end loop;
480 close get_contacts;
481 end if;
482 --
483 -- reset the variables.
484 --
485 hr_utility.set_location(' ben_add_trigger', 40);
486 l_changed := FALSE;
487 l_trigger := TRUE;
488 l_ovn := NULL;
489 l_effective_start_date := l_session_date;
490 -- l_lf_evt_ocrd_date := l_session_date;
491 end if;
492 close get_ler_col;
493 end loop;
494 hr_utility.set_location(' ben_add_trigger', 50);
495 close get_ler;
496 hr_utility.set_location(' leaving ben_add_trigger', 60);
497
498 end;
499 end ben_add_ler;