[Home] [Help]
PACKAGE BODY: APPS.BEN_PAC_LER
Source
1 package body ben_pac_ler as
2 /* $Header: bepactrg.pkb 120.1 2005/06/23 02:09:02 nhunur noship $ */
3 g_package varchar2(30) := 'ben_pac_ler' ;
4 --
5 -- This procedure is used to execute the rule : per_info_chg_cs_ler_rl
6 --
7 procedure exec_rule(
8 p_formula_id in number,
9 p_effective_date in date,
10 p_lf_evt_ocrd_dt in date,
11 p_business_group_id in number,
12 p_person_id in number ,
13 p_new_value in varchar2 ,
14 p_old_value in varchar2 ,
15 p_column_name in varchar2 ,
16 p_pk_id in varchar2 ,
17 p_param5 in varchar2 ,
18 p_param5_value in varchar2 ,
19 p_param6 in varchar2 ,
20 p_param6_value in varchar2 ,
21 p_param7 in varchar2 ,
22 p_param7_value in varchar2 ,
23 p_param8 in varchar2 ,
24 p_param8_value in varchar2 ,
25 p_param9 in varchar2 ,
26 p_param9_value in varchar2 ,
27 p_param10 in varchar2 ,
28 p_param10_value in varchar2 ,
29 p_param11 in varchar2 ,
30 p_param11_value in varchar2 ,
31 p_param12 in varchar2 ,
32 p_param12_value in varchar2 ,
33 p_param13 in varchar2 ,
34 p_param13_value in varchar2 ,
35 p_param14 in varchar2 ,
36 p_param14_value in varchar2 ,
37 p_param15 in varchar2 ,
38 p_param15_value in varchar2 ,
39 p_param16 in varchar2 ,
40 p_param16_value in varchar2 ,
41 p_param17 in varchar2 ,
42 p_param17_value in varchar2 ,
43 p_param18 in varchar2 ,
44 p_param18_value in varchar2 ,
45 p_param19 in varchar2 ,
46 p_param19_value in varchar2 ,
47 p_param20 in varchar2 ,
48 p_param20_value in varchar2 ,
49 p_param21 in varchar2 ,
50 p_param21_value in varchar2 ,
51 p_param22 in varchar2 ,
52 p_param22_value in varchar2 ,
53 p_param23 in varchar2 ,
54 p_param23_value in varchar2 ,
55 p_param24 in varchar2 ,
56 p_param24_value in varchar2 ,
57 p_param25 in varchar2 ,
58 p_param25_value in varchar2 ,
59 p_param26 in varchar2 ,
60 p_param26_value in varchar2 ,
61 p_param27 in varchar2 ,
62 p_param27_value in varchar2 ,
63 p_param28 in varchar2 ,
64 p_param28_value in varchar2 ,
65 p_param29 in varchar2 ,
66 p_param29_value in varchar2 ,
67 p_param30 in varchar2 ,
68 p_param30_value in varchar2 ,
69 p_param31 in varchar2 ,
70 p_param31_value in varchar2 ,
71 p_param32 in varchar2 ,
72 p_param32_value in varchar2 ,
73 p_param33 in varchar2 ,
74 p_param33_value in varchar2 ,
75 p_param34 in varchar2 ,
76 p_param34_value in varchar2 ,
77 p_param35 in varchar2 ,
78 p_param35_value in varchar2 ,
79 p_param_tab in ff_exec.outputs_t ,
80 p_ret_val out nocopy varchar2 ,
81 p_ret_date out nocopy date) is
82 --
83
84 l_package varchar2(80) := g_package||'.exec_rule';
85 l_proc varchar2(80) := g_package||'.exec_rule';
86 l_outputs ff_exec.outputs_t;
87 l_loc_rec hr_locations_all%rowtype;
88 l_ass_rec per_all_assignments_f%rowtype;
89 l_jurisdiction_code varchar2(30);
90 l_env ben_env_object.g_global_env_rec_type;
91
92 --
93 begin
94 --
95 hr_utility.set_location ('Entering '||l_package,10);
96 --
97 if fnd_global.conc_request_id = -1 then
98 --
99 -- This makes sense for the calls made from the forms.
100 --
101 ben_env_object.init(p_business_group_id => p_business_group_id,
102 p_effective_date => p_effective_date,
103 p_thread_id => 1,
104 p_chunk_size => 1,
105 p_threads => 1,
106 p_max_errors => 1,
107 p_benefit_action_id => null);
108 --
109 end if;
110 --
111 -- Call formula initialise routine
112 --
113 if p_person_id is not null then
114 ben_person_object.get_object(p_person_id => p_person_id,
115 p_rec => l_ass_rec);
116 end if;
117 --
118 if p_person_id is not null and l_ass_rec.assignment_id is null then
119 --
120 ben_person_object.get_benass_object(p_person_id => p_person_id,
121 p_rec => l_ass_rec);
122 --
123 end if;
124 --
125 --
126 hr_utility.set_location('assignment_id '||l_ass_rec.assignment_id , 13);
127 l_outputs := benutils.formula
128 (p_formula_id => p_formula_id,
129 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
130 p_assignment_id => l_ass_rec.assignment_id,
131 p_organization_id => l_ass_rec.organization_id,
132 p_business_group_id => p_business_group_id,
133 p_param1 => 'NEW_VAL',
134 p_param1_value => p_new_value,
135 p_param2 => 'OLD_VAL',
136 p_param2_value => p_old_value,
137 p_param3 => 'COLUMN',
138 p_param3_value => p_column_name,
139 p_param4 => 'PK_ID',
140 p_param4_value => p_pk_id,
141 p_param5 => p_param5,
142 p_param5_value => p_param5_value,
143 p_param6 => p_param6,
144 p_param6_value => p_param6_value,
145 p_param7 => p_param7,
146 p_param7_value => p_param7_value,
147 p_param8 => p_param8,
148 p_param8_value => p_param8_value,
149 p_param9 => p_param9,
150 p_param9_value => p_param9_value,
151 p_param10 => p_param10,
152 p_param10_value => p_param10_value,
153 p_param11 => p_param11,
154 p_param11_value => p_param11_value,
155 p_param12 => p_param12,
156 p_param12_value => p_param12_value,
157 p_param13 => p_param13,
158 p_param13_value => p_param13_value,
159 p_param14 => p_param14,
160 p_param14_value => p_param14_value,
161 p_param15 => p_param15,
162 p_param15_value => p_param15_value,
163 p_param16 => p_param16,
164 p_param16_value => p_param16_value,
165 p_param17 => p_param17,
166 p_param17_value => p_param17_value,
167 p_param18 => p_param18,
168 p_param18_value => p_param18_value,
169 p_param19 => p_param19,
170 p_param19_value => p_param19_value,
171 p_param20 => p_param20,
172 p_param20_value => p_param20_value,
173 p_param21 => p_param21,
174 p_param21_value => p_param21_value,
175 p_param22 => p_param22,
176 p_param22_value => p_param22_value,
177 p_param23 => p_param23,
178 p_param23_value => p_param23_value,
179 p_param24 => p_param24,
180 p_param24_value => p_param24_value,
181 p_param25 => p_param25,
182 p_param25_value => p_param25_value,
183 p_param26 => p_param26,
184 p_param26_value => p_param26_value,
185 p_param27 => p_param27,
186 p_param27_value => p_param27_value,
187 p_param28 => p_param28,
188 p_param28_value => p_param28_value,
189 p_param29 => p_param29,
190 p_param29_value => p_param29_value,
191 p_param30 => p_param30,
192 p_param30_value => p_param30_value,
193 p_param31 => p_param31,
194 p_param31_value => p_param31_value,
195 p_param32 => p_param32,
196 p_param32_value => p_param32_value,
197 p_param33 => p_param33,
198 p_param33_value => p_param33_value,
199 p_param34 => p_param34,
200 p_param34_value => p_param34_value,
201 p_param35 => 'PERSON_ID',
202 p_param35_value => to_char(p_person_id),
203 p_param_tab => p_param_tab,
204 p_jurisdiction_code => l_jurisdiction_code);
205 --
206
207 for l_count in l_outputs.first..l_outputs.last loop
208 --
209 declare
210 invalid_param exception;
211 begin
212 --
213 if l_outputs(l_count).name = 'RETURN_FLAG' then
214 --
215 hr_utility.set_location ('RETURN_FLAG '|| l_outputs(l_count).value,10);
216 p_ret_val := l_outputs(l_count).value;
217 -- defensive coding
218 if ( p_ret_val not in ('Y' , 'N' , 'y' , 'n' )
219 OR p_ret_val is null )
220 then
221 p_ret_val := 'Y' ;
222 end if ;
223 --
224 elsif l_outputs(l_count).name = 'LE_OCCURED_DATE' then
225 --
226 hr_utility.set_location ('LE_OCCURED_DATE '|| l_outputs(l_count).value,10);
227 if l_outputs(l_count).value is not null
228 then
229 p_ret_date := fnd_date.canonical_to_date(l_outputs(l_count).value);
230 else
231 p_ret_date := NULL ;
232 end if ;
233 --
234 else
235 --
236 hr_utility.set_location ('INVALID RULE VALUE '|| l_outputs(l_count).value,10);
237 -- Account for cases where formula returns an unknown
238 -- variable name
239 --
240 fnd_message.set_name('BEN','BEN_92310_FORMULA_RET_PARAM_');
241 fnd_message.set_token('PROC',l_proc);
242 fnd_message.set_token('FORMULA', p_formula_id);
243 fnd_message.set_token('PARAMETER',l_outputs(l_count).name);
244 -- Handling this particular exception seperately.
245 raise invalid_param;
246 --
247 end if;
248 --
249 -- Code for type casting errors from formula return variables
250 --
251 exception
252 --
253 when invalid_param then
254 fnd_message.raise_error;
255 when others then
256 --
257 fnd_message.set_name('BEN','BEN_92311_FORMULA_VAL_PARAM');
258 fnd_message.set_token('PROC',l_proc);
259 fnd_message.set_token('FORMULA', p_formula_id);
260 fnd_message.set_token('PARAMETER',l_outputs(l_count).name);
261 fnd_message.raise_error;
262 --
263 end;
264 end loop;
265 hr_utility.set_location ('Leaving '||l_package,10);
266 --
267 end exec_rule;
268 --
269 procedure ler_chk(p_old IN g_pac_ler_rec
270 ,p_new IN g_pac_ler_rec
271 ,p_effective_date in date default null ) is
272 --
273 l_session_date DATE;
274 l_system_date DATE;
275 --
276 cursor get_session_date IS
277 select effective_date
278 from fnd_sessions
279 where session_id = userenv('SESSIONID');
280 --
281 cursor get_ler(l_status varchar2) is
282 select ler.ler_id
283 , ler.typ_cd
284 , ler.ocrd_dt_det_cd
285 from ben_ler_f ler
286 where ler.business_group_id = p_new.business_group_id
287 and l_session_date between ler.effective_start_date and ler.effective_end_date
288 and ( l_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
289 and ((exists
290 (select 1
291 from ben_per_info_chg_cs_ler_f psl
292 , ben_ler_per_info_cs_ler_f lpl
293 where source_table like 'PER_PERSON_ANALYSES%'
294 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
295 and lpl.business_group_id = psl.business_group_id
296 and lpl.business_group_id = ler.business_group_id
297 and l_session_date between psl.effective_start_date and psl.effective_end_date
298 and l_session_date between lpl.effective_start_date and lpl.effective_end_date
299 and lpl.ler_id = ler.ler_id)
300 )
301 OR (exists
302 (select 1
303 from ben_rltd_per_chg_cs_ler_f rpc
304 , ben_ler_rltd_per_cs_ler_f lrp
305 where source_table like 'PER_PERSON_ANALYSES%'
306 and lrp.business_group_id = rpc.business_group_id
307 and lrp.business_group_id = ler.business_group_id
308 and l_session_date between rpc.effective_start_date and rpc.effective_end_date
309 and l_session_date between lrp.effective_start_date and lrp.effective_end_date
310 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
311 and lrp.ler_id = ler.ler_id)
312 ))
313 order by ler.ler_id;
314
315 cursor get_ler_col(p_ler_id IN NUMBER) is
316 select psl.source_column, psl.new_val, psl.old_val,
317 'P', psl.per_info_chg_cs_ler_rl, psl.rule_overrides_flag,
318 lpl.chg_mandatory_cd, psl.source_table ,
319 substr(psl.source_table, instr(psl.source_table, '-')+2 ,length(psl.source_table)) l_context
320 from ben_ler_per_info_cs_ler_f lpl,
321 ben_per_info_chg_cs_ler_f psl
322 where lpl.ler_id = p_ler_id
323 and lpl.business_group_id = p_new.business_group_id
324 and lpl.business_group_id = psl.business_group_id
325 and l_session_date between psl.effective_start_date and psl.effective_end_date
326 and l_session_date between lpl.effective_start_date and lpl.effective_end_date
327 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
328 and source_table like 'PER_PERSON_ANALYSES%'
329 UNION
330 select rpc.source_column, rpc.new_val, rpc.old_val,
331 'R', rpc.rltd_per_chg_cs_ler_rl per_info_chg_cs_ler, rpc.rule_overrides_flag,
332 lrp.chg_mandatory_cd, rpc.source_table,
333 substr(rpc.source_table, instr(rpc.source_table, '-')+2 ,length(rpc.source_table)) l_context
334 from ben_ler_rltd_per_cs_ler_f lrp,
335 ben_rltd_per_chg_cs_ler_f rpc
336 where lrp.ler_id = p_ler_id
337 and lrp.business_group_id = p_new.business_group_id
338 and lrp.business_group_id = rpc.business_group_id
339 and l_session_date between rpc.effective_start_date and rpc.effective_end_date
340 and l_session_date between lrp.effective_start_date and lrp.effective_end_date
341 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
342 and source_table like 'PER_PERSON_ANALYSES%'
343 order by 1;
344 --
345 cursor le_exists(p_person_id in number
346 ,p_ler_id in number
347 ,p_lf_evt_ocrd_dt in date) is
348 select 'Y'
349 from ben_ptnl_ler_for_per
350 where person_id = p_person_id
351 and ler_id = p_ler_id
352 and ptnl_ler_for_per_stat_cd = 'DTCTD'
353 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
354 --
355 cursor get_contacts(p_person_id in number) is
356 select person_id
357 from per_contact_relationships
358 where contact_person_id = p_person_id
359 and business_group_id = p_new.business_group_id
360 and l_session_date between nvl(date_start,l_session_date)
361 and nvl(date_end,l_session_date)
362 and personal_flag = 'Y'
363 order by person_id;
364 --
365 cursor c_get_pac(p_id in number) is
366 select *
367 from per_analysis_criteria
368 where ANALYSIS_CRITERIA_ID = p_id;
369 --
370 l_param_tab ff_exec.outputs_t ;
371 l_old_pac c_get_pac%rowtype ;
372 l_new_pac c_get_pac%rowtype ;
373 l_source_table ben_per_info_chg_cs_ler_f.source_table%TYPE ;
374 l_context varchar2(30);
375 --
376 l_changed BOOLEAN;
377 l_ler_id NUMBER;
378 l_typ_cd ben_ler_f.typ_cd%type ;
379 l_ocrd_dt_cd VARCHAR2(30);
380 l_column ben_rltd_per_chg_cs_ler_f.source_column%type;
381 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type;
382 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type;
383 l_per_info_chg_cs_ler_rl number;
384 l_ovn NUMBER;
385 l_ptnl_id NUMBER;
386 l_effective_end_date DATE := to_date('31-12-4712','DD-MM-YYYY');
387 l_effective_start_date DATE ;
388 l_lf_evt_ocrd_date DATE ;
389 l_le_exists VARCHAR2(1);
390 l_mnl_dt date;
391 l_dtctd_dt date;
392 l_procd_dt date;
393 l_unprocd_dt date;
394 l_voidd_dt date;
395 l_type VARCHAR2(1);
396 l_hld_person_id NUMBER;
397 l_rule_output varchar2(30) ;
398 --
399 l_bool BOOLEAN;
400 l_status VARCHAR2(1);
401 l_industry VARCHAR2(1);
402 l_col_new_val VARCHAR2(1000);
403 l_col_old_val varchar2(1000);
404 --
405 l_rule_overrides_flag VARCHAR2(1);
406 l_chg_mandatory_cd VARCHAR2(1);
407 l_trigger boolean := TRUE;
408 --
409 cursor c_get_context_num ( p_name varchar2 ) is
410 select id_flex_num
411 from FND_ID_FLEX_STRUCTURES_TL
412 where id_flex_structure_name = p_name
413 and language = userenv('LANG');
414 --
415 l_context_num number;
416 l_ret_date date;
417 --
418 begin
419 --
420 benutils.set_data_migrator_mode;
421 if hr_general.g_data_migrator_mode in ( 'Y','P') then
422 --
423 return;
424 --
425 end if;
426 --
427 l_bool := fnd_installation.get(appl_id => 805
428 ,dep_appl_id =>805
429 ,status => l_status
430 ,industry => l_industry);
431
432 hr_utility.set_location(' Entering: ben_pac_trigger', 10);
433 l_changed := FALSE;
434 --
435 l_system_date := trunc(sysdate);
436 --
437 open get_session_date;
438 fetch get_session_date into l_session_date;
439 close get_session_date;
440 --
441 -- in case it is null
442 --
443 l_session_date := nvl(l_session_date , l_system_date) ;
444 --
445 l_effective_start_date := nvl(l_session_date,l_system_date);
446 --
447 hr_utility.set_location(' ben_pac_trigger - after l_session_date' || to_char(l_session_date), 20);
448 hr_utility.set_location(' ben_pac_trigger - after l_effective_start_date', 20);
449 --
450 open get_ler(l_status);
451 loop
452 fetch get_ler into l_ler_id,l_typ_cd, l_ocrd_dt_cd;
453 exit when get_ler%notfound;
454 l_trigger := TRUE;
455 --
456 if l_ocrd_dt_cd is null
457 then
458 l_lf_evt_ocrd_date := nvl(p_new.date_from,l_effective_start_date);
459 else
460 --
461 -- Call the common date procedure.
462 --
463 ben_determine_date.main
464 (p_date_cd => l_ocrd_dt_cd
465 ,p_effective_date => l_effective_start_date
466 ,p_lf_evt_ocrd_dt => l_effective_start_date
467 ,p_returned_date => l_lf_evt_ocrd_date
468 );
469 end if;
470 --
471 hr_utility.set_location(' ben_pac_trigger - after l_lf_evt_ocrd_date', 30);
472 open get_ler_col(l_ler_id);
473 loop
474 fetch get_ler_col into l_column,l_new_val, l_old_val, l_type, l_per_info_chg_cs_ler_rl,
475 l_rule_overrides_flag, l_chg_mandatory_cd ,l_source_table, l_context;
476 exit when get_ler_col%NOTFOUND;
477
478 -- check if the context set on data changes matches that of
479 -- the new record being inserted / updated
480 -- if it matches only then we are interested in proceeding
481 open c_get_context_num (l_context );
482 fetch c_get_context_num into l_context_num ;
483 if c_get_context_num%NOTFOUND then
484 -- cant verify and proceed
485 close c_get_context_num ;
486 exit ;
487 else
488 close c_get_context_num ;
489 end if;
490 hr_utility.set_location(' ben_pac_trigger - context num '||l_context_num , 50);
491 hr_utility.set_location(' ben_pac_trigger - p_newid_flex_num '||p_new.id_flex_num , 50);
492 --
493 -- continue if context num is same as new value for new.id_flex_num
494 -- else exit
495 --
496 if l_context_num <> p_new.id_flex_num
497 then
498 l_trigger := FALSE;
499 l_changed := FALSE;
500 exit;
501 end if;
502 --
503 l_changed := TRUE;
504 --
505 hr_utility.set_location('LER '||l_ler_id, 20);
506 hr_utility.set_location('COLUMN '||l_column, 20);
507 hr_utility.set_location('NEWVAL '||l_new_val, 20);
508 hr_utility.set_location('OLDVAL '||l_old_val, 20);
509 hr_utility.set_location('TYPE '||l_type, 20);
510 hr_utility.set_location('leod '||l_lf_evt_ocrd_date, 20);
511 --
512 -- get the master table details using the old and new ANALYSIS_CRITERIA_ID
513 -- if its insert then its null to any value
514 -- if its update then its any value to any value
515 -- This is possible as per_analysis_criteria is a KFF
516 --
517 if p_old.analysis_criteria_id is NULL
518 then
519 l_old_pac := NULL ;
520 else
521 open c_get_pac ( p_old.analysis_criteria_id );
522 fetch c_get_pac into l_old_pac ;
523 close c_get_pac ;
524 end if;
525 --
526 hr_utility.set_location(' ben_pac_trigger - after l_old_pac', 40);
527 --
528 open c_get_pac ( p_new.analysis_criteria_id );
529 fetch c_get_pac into l_new_pac ;
530 close c_get_pac ;
531 --
532 hr_utility.set_location(' ben_pac_trigger - after l_new_pac', 40);
533 -- Call the formula now to evaluate per_info_chg_cs_ler_rl.
534 -- If it returns Y, then see the applicability of the data
535 -- changes based on new and old values.
536 --
537 l_rule_output := 'Y';
538 --
539 if l_per_info_chg_cs_ler_rl is not null then
540 --
541 if l_column = 'DATE_FROM' then
542 l_col_old_val := p_old.DATE_FROM;
543 l_col_new_val := p_new.DATE_FROM;
544 end if;
545 --
546 if l_column = 'DATE_TO' then
547 l_col_old_val := p_old.DATE_TO;
548 l_col_new_val := p_new.DATE_TO;
549 end if;
550 --
551 if l_column = 'ANALYSIS_CRITERIA_ID' then
552 l_col_old_val := p_old.ANALYSIS_CRITERIA_ID;
553 l_col_new_val := p_new.ANALYSIS_CRITERIA_ID;
554 end if;
555 --
556 if l_column = 'SEGMENT1' then
557 l_col_old_val := l_old_pac.SEGMENT1;
558 l_col_new_val := l_new_pac.SEGMENT1;
559 end if;
560 --
561 if l_column = 'SEGMENT2' then
562 l_col_old_val := l_old_pac.SEGMENT2;
563 l_col_new_val := l_new_pac.SEGMENT2;
564 end if;
565 --
566 if l_column = 'SEGMENT3' then
567 l_col_old_val := l_old_pac.SEGMENT3;
568 l_col_new_val := l_new_pac.SEGMENT3;
569 end if;
570 --
571 if l_column = 'SEGMENT4' then
575 --
572 l_col_old_val := l_old_pac.SEGMENT4;
573 l_col_new_val := l_new_pac.SEGMENT4;
574 end if;
576 if l_column = 'SEGMENT5' then
577 l_col_old_val := l_old_pac.SEGMENT5;
578 l_col_new_val := l_new_pac.SEGMENT5;
579 end if;
580 --
581 if l_column = 'SEGMENT6' then
582 l_col_old_val := l_old_pac.SEGMENT6;
583 l_col_new_val := l_new_pac.SEGMENT6;
584 end if;
585 --
586 if l_column = 'SEGMENT7' then
587 l_col_old_val := l_old_pac.SEGMENT7;
588 l_col_new_val := l_new_pac.SEGMENT7;
589 end if;
590 --
591 if l_column = 'SEGMENT8' then
592 l_col_old_val := l_old_pac.SEGMENT8;
593 l_col_new_val := l_new_pac.SEGMENT8;
594 end if;
595 --
596 if l_column = 'SEGMENT9' then
597 l_col_old_val := l_old_pac.SEGMENT9;
598 l_col_new_val := l_new_pac.SEGMENT9;
599 end if;
600 --
601 if l_column = 'SEGMENT10' then
602 l_col_old_val := l_old_pac.SEGMENT10;
603 l_col_new_val := l_new_pac.SEGMENT10;
604 end if;
605 --
606 if l_column = 'SEGMENT11' then
607 l_col_old_val := l_old_pac.SEGMENT11;
608 l_col_new_val := l_new_pac.SEGMENT11;
609 end if;
610 --
611 if l_column = 'SEGMENT12' then
612 l_col_old_val := l_old_pac.SEGMENT12;
613 l_col_new_val := l_new_pac.SEGMENT12;
614 end if;
615 --
616 if l_column = 'SEGMENT13' then
617 l_col_old_val := l_old_pac.SEGMENT13;
618 l_col_new_val := l_new_pac.SEGMENT13;
619 end if;
620 --
621 if l_column = 'SEGMENT14' then
622 l_col_old_val := l_old_pac.SEGMENT14;
623 l_col_new_val := l_new_pac.SEGMENT14;
624 end if;
625 --
626 if l_column = 'SEGMENT15' then
627 l_col_old_val := l_old_pac.SEGMENT15;
628 l_col_new_val := l_new_pac.SEGMENT15;
629 end if;
630 --
631 if l_column = 'SEGMENT16' then
632 l_col_old_val := l_old_pac.SEGMENT16;
633 l_col_new_val := l_new_pac.SEGMENT16;
634 end if;
635 --
636 if l_column = 'SEGMENT17' then
637 l_col_old_val := l_old_pac.SEGMENT17;
638 l_col_new_val := l_new_pac.SEGMENT17;
639 end if;
640 --
641 if l_column = 'SEGMENT18' then
642 l_col_old_val := l_old_pac.SEGMENT18;
643 l_col_new_val := l_new_pac.SEGMENT18;
644 end if;
645 --
646 if l_column = 'SEGMENT19' then
647 l_col_old_val := l_old_pac.SEGMENT19;
648 l_col_new_val := l_new_pac.SEGMENT19;
649 end if;
650 --
651 if l_column = 'SEGMENT20' then
652 l_col_old_val := l_old_pac.SEGMENT20;
653 l_col_new_val := l_new_pac.SEGMENT20;
654 end if;
655 --
656 if l_column = 'SEGMENT21' then
657 l_col_old_val := l_old_pac.SEGMENT21;
658 l_col_new_val := l_new_pac.SEGMENT21;
659 end if;
660 --
661 if l_column = 'SEGMENT22' then
662 l_col_old_val := l_old_pac.SEGMENT22;
663 l_col_new_val := l_new_pac.SEGMENT22;
664 end if;
665 --
666 if l_column = 'SEGMENT23' then
667 l_col_old_val := l_old_pac.SEGMENT23;
668 l_col_new_val := l_new_pac.SEGMENT23;
669 end if;
670 --
671 if l_column = 'SEGMENT24' then
672 l_col_old_val := l_old_pac.SEGMENT24;
673 l_col_new_val := l_new_pac.SEGMENT24;
674 end if;
675 --
676 if l_column = 'SEGMENT25' then
677 l_col_old_val := l_old_pac.SEGMENT25;
678 l_col_new_val := l_new_pac.SEGMENT25;
679 end if;
680 --
681 if l_column = 'SEGMENT26' then
682 l_col_old_val := l_old_pac.SEGMENT26;
683 l_col_new_val := l_new_pac.SEGMENT26;
684 end if;
685 --
686 if l_column = 'SEGMENT27' then
687 l_col_old_val := l_old_pac.SEGMENT27;
688 l_col_new_val := l_new_pac.SEGMENT27;
689 end if;
690 --
691 if l_column = 'SEGMENT28' then
692 l_col_old_val := l_old_pac.SEGMENT28;
693 l_col_new_val := l_new_pac.SEGMENT28;
694 end if;
695 --
696 if l_column = 'SEGMENT29' then
697 l_col_old_val := l_old_pac.SEGMENT29;
698 l_col_new_val := l_new_pac.SEGMENT29;
699 end if;
700 --
701 if l_column = 'SEGMENT30' then
702 l_col_old_val := l_old_pac.SEGMENT30;
703 l_col_new_val := l_new_pac.SEGMENT30;
704 end if;
705 --
706 hr_utility.set_location(' ben_pac_trigger - before exec_rule', 40);
707
708 exec_rule(
712 p_business_group_id => nvl(p_new.business_group_id, p_old.business_group_id),
709 p_formula_id => l_per_info_chg_cs_ler_rl,
710 p_effective_date => l_session_date,
711 p_lf_evt_ocrd_dt => null,
713 p_person_id => nvl(p_new.person_id, p_old.person_id),
714 p_new_value => l_col_new_val,
715 p_old_value => l_col_old_val,
716 p_column_name => l_column,
717 p_param5 => 'BEN_PAC_IN_SEGMENT1',
718 p_param5_value => l_new_pac.SEGMENT1,
719 p_param6 => 'BEN_PAC_IO_SEGMENT1',
720 p_param6_value => l_old_pac.SEGMENT1,
721 p_param7 => 'BEN_PAC_IN_SEGMENT2',
722 p_param7_value => l_new_pac.SEGMENT2,
723 p_param8 => 'BEN_PAC_IO_SEGMENT2',
724 p_param8_value => l_old_pac.SEGMENT2,
725 p_param9 => 'BEN_PAC_IN_SEGMENT3',
726 p_param9_value => l_new_pac.SEGMENT3,
727 p_param10 => 'BEN_PAC_IO_SEGMENT3',
728 p_param10_value => l_old_pac.SEGMENT3,
729 p_param11 => 'BEN_PAC_IN_SEGMENT4',
730 p_param11_value => l_new_pac.SEGMENT4,
731 p_param12 => 'BEN_PAC_IO_SEGMENT4',
732 p_param12_value => l_old_pac.SEGMENT4,
733 p_param13 => 'BEN_PAC_IN_SEGMENT5',
734 p_param13_value => l_new_pac.SEGMENT5,
735 p_param14 => 'BEN_PAC_IO_SEGMENT5',
736 p_param14_value => l_old_pac.SEGMENT5,
737 p_param15 => 'BEN_PAC_IN_SEGMENT6',
738 p_param15_value => l_new_pac.SEGMENT6,
739 p_param16 => 'BEN_PAC_IO_SEGMENT6',
740 p_param16_value => l_old_pac.SEGMENT6,
741 p_param17 => 'BEN_PAC_IN_SEGMENT7',
742 p_param17_value => l_new_pac.SEGMENT7,
743 p_param18 => 'BEN_PAC_IO_SEGMENT7',
744 p_param18_value => l_old_pac.SEGMENT7,
745 p_param19 => 'BEN_PAC_IN_SEGMENT8',
746 p_param19_value => l_new_pac.SEGMENT8,
747 p_param20 => 'BEN_PAC_IO_SEGMENT8',
748 p_param20_value => l_old_pac.SEGMENT8,
749 p_param21 => 'BEN_PAC_IN_SEGMENT9',
750 p_param21_value => l_new_pac.SEGMENT9,
751 p_param22 => 'BEN_PAC_IO_SEGMENT9',
752 p_param22_value => l_old_pac.SEGMENT9,
753 p_param23 => 'BEN_PAC_IN_SEGMENT10',
754 p_param23_value => l_new_pac.SEGMENT10,
755 p_param24 => 'BEN_PAC_IO_SEGMENT10',
756 p_param24_value => l_old_pac.SEGMENT10,
757 p_param25 => 'BEN_PAC_IN_SEGMENT11',
758 p_param25_value => l_new_pac.SEGMENT11,
759 p_param26 => 'BEN_PAC_IO_SEGMENT11',
760 p_param26_value => l_old_pac.SEGMENT11,
761 p_param27 => 'BEN_PAC_IN_SEGMENT12',
762 p_param27_value => l_new_pac.SEGMENT12,
763 p_param28 => 'BEN_IV_LER_ID',
764 p_param28_value => to_char(l_ler_id),
765 p_param29 => 'BEN_PAC_IN_DATE_FROM',
766 p_param29_value => to_char(p_new.DATE_FROM, 'YYYY/MM/DD HH24:MI:SS'),
767 p_param30 => 'BEN_PAC_IO_DATE_FROM',
768 p_param30_value => to_char(p_old.DATE_FROM, 'YYYY/MM/DD HH24:MI:SS'),
769 p_param31 => 'BEN_PAC_IN_DATE_TO',
770 p_param31_value => to_char(p_new.DATE_TO, 'YYYY/MM/DD HH24:MI:SS'),
771 p_param32 => 'BEN_PAC_IO_DATE_TO',
772 p_param32_value => to_char(p_old.DATE_TO, 'YYYY/MM/DD HH24:MI:SS'),
773 p_param33 => 'BEN_PAC_IN_ANALYSIS_CRITERIA_ID',
774 p_param33_value => to_char(p_new.ANALYSIS_CRITERIA_ID),
775 p_param34 => 'BEN_PAC_IO_ANALYSIS_CRITERIA_ID',
776 p_param34_value => to_char(p_old.ANALYSIS_CRITERIA_ID),
777 p_param35 => NULL,
778 p_param35_value => NULL,
779 p_pk_id => to_char(p_new.PERSON_ANALYSIS_ID),
780 p_param_tab => l_param_tab ,
781 p_ret_val => l_rule_output,
782 p_ret_date => l_ret_date );
783 --
784 hr_utility.set_location(' ben_pac_trigger - after benutils.exec_rule' || l_rule_output, 40);
785 hr_utility.set_location(' ben_pac_trigger - after benutils.exec_rule' || to_char(l_ret_date), 40);
786 --
787 -- rule date overrides all dates
788 l_lf_evt_ocrd_date := nvl(l_ret_date , l_lf_evt_ocrd_date ) ;
789 --
790 end if;
791 --
792 --
793 if l_column = 'DATE_FROM' then
794 l_changed := (benutils.column_changed(p_old.DATE_FROM
795 ,p_new.DATE_FROM,l_new_val) AND
796 benutils.column_changed(p_new.DATE_FROM
797 ,p_old.DATE_FROM,l_old_val) AND
798 (l_changed));
799 --
800 end if;
801 --
802 if l_column = 'DATE_TO' then
803 l_changed := (benutils.column_changed(p_old.DATE_TO
804 ,p_new.DATE_TO,l_new_val) AND
805 benutils.column_changed(p_new.DATE_TO
806 ,p_old.DATE_TO,l_old_val) AND
810 if l_column = 'ANALYSIS_CRITERIA_ID' then
807 (l_changed));
808 end if;
809 --
811 l_changed := (benutils.column_changed(p_old.ANALYSIS_CRITERIA_ID
812 ,p_new.ANALYSIS_CRITERIA_ID,l_new_val) AND
813 benutils.column_changed(p_new.ANALYSIS_CRITERIA_ID
814 ,p_old.ANALYSIS_CRITERIA_ID,l_old_val) AND
815 (l_changed));
816 end if;
817 --
818 if l_column = 'SEGMENT1' then
819 l_changed := (benutils.column_changed(l_old_pac.SEGMENT1
820 ,l_new_pac.SEGMENT1,l_new_val) AND
821 benutils.column_changed(l_new_pac.SEGMENT1
822 ,l_old_pac.SEGMENT1,l_old_val) AND
823 (l_changed));
824 end if;
825 --
826 if l_column = 'SEGMENT2' then
827 l_changed := (benutils.column_changed(l_old_pac.SEGMENT2
828 ,l_new_pac.SEGMENT2,l_new_val) AND
829 benutils.column_changed(l_new_pac.SEGMENT2
830 ,l_old_pac.SEGMENT2,l_old_val) AND
831 (l_changed));
832 end if;
833 --
834 if l_column = 'SEGMENT3' then
835 l_changed := (benutils.column_changed(l_old_pac.SEGMENT3
836 ,l_new_pac.SEGMENT3,l_new_val) AND
837 benutils.column_changed(l_new_pac.SEGMENT3
838 ,l_old_pac.SEGMENT3,l_old_val) AND
839 (l_changed));
840 end if;
841 --
842 if l_column = 'SEGMENT4' then
843 l_changed := (benutils.column_changed(l_old_pac.SEGMENT4
844 ,l_new_pac.SEGMENT4,l_new_val) AND
845 benutils.column_changed(l_new_pac.SEGMENT4
846 ,l_old_pac.SEGMENT4,l_old_val) AND
847 (l_changed));
848 end if;
849 --
850 if l_column = 'SEGMENT5' then
851 l_changed := (benutils.column_changed(l_old_pac.SEGMENT5
852 ,l_new_pac.SEGMENT5,l_new_val) AND
853 benutils.column_changed(l_new_pac.SEGMENT5
854 ,l_old_pac.SEGMENT5,l_old_val) AND
855 (l_changed));
856 end if;
857 --
858 if l_column = 'SEGMENT6' then
859 l_changed := (benutils.column_changed(l_old_pac.SEGMENT6
860 ,l_new_pac.SEGMENT6,l_new_val) AND
861 benutils.column_changed(l_new_pac.SEGMENT6
862 ,l_old_pac.SEGMENT6,l_old_val) AND
863 (l_changed));
864 end if;
865 --
866 if l_column = 'SEGMENT7' then
867 l_changed := (benutils.column_changed(l_old_pac.SEGMENT7
868 ,l_new_pac.SEGMENT7,l_new_val) AND
869 benutils.column_changed(l_new_pac.SEGMENT7
870 ,l_old_pac.SEGMENT7,l_old_val) AND
871 (l_changed));
872 end if;
873 --
874 if l_column = 'SEGMENT8' then
875 l_changed := (benutils.column_changed(l_old_pac.SEGMENT8
876 ,l_new_pac.SEGMENT8,l_new_val) AND
877 benutils.column_changed(l_new_pac.SEGMENT8
878 ,l_old_pac.SEGMENT8,l_old_val) AND
879 (l_changed));
880 end if;
881 --
882 if l_column = 'SEGMENT9' then
883 l_changed := (benutils.column_changed(l_old_pac.SEGMENT9
884 ,l_new_pac.SEGMENT9,l_new_val) AND
885 benutils.column_changed(l_new_pac.SEGMENT9
886 ,l_old_pac.SEGMENT9,l_old_val) AND
887 (l_changed));
888 end if;
889 --
890 if l_column = 'SEGMENT10' then
891 l_changed := (benutils.column_changed(l_old_pac.SEGMENT10
892 ,l_new_pac.SEGMENT10,l_new_val) AND
893 benutils.column_changed(l_new_pac.SEGMENT10
894 ,l_old_pac.SEGMENT10,l_old_val) AND
895 (l_changed));
896 end if;
897 --
898 if l_column = 'SEGMENT11' then
899 l_changed := (benutils.column_changed(l_old_pac.SEGMENT11
900 ,l_new_pac.SEGMENT11,l_new_val) AND
901 benutils.column_changed(l_new_pac.SEGMENT11
902 ,l_old_pac.SEGMENT11,l_old_val) AND
903 (l_changed));
904 end if;
905 --
906 if l_column = 'SEGMENT12' then
907 l_changed := (benutils.column_changed(l_old_pac.SEGMENT12
908 ,l_new_pac.SEGMENT12,l_new_val) AND
909 benutils.column_changed(l_new_pac.SEGMENT12
910 ,l_old_pac.SEGMENT12,l_old_val) AND
911 (l_changed));
912 end if;
913 --
914 if l_column = 'SEGMENT13' then
915 l_changed := (benutils.column_changed(l_old_pac.SEGMENT13
916 ,l_new_pac.SEGMENT13,l_new_val) AND
917 benutils.column_changed(l_new_pac.SEGMENT13
918 ,l_old_pac.SEGMENT13,l_old_val) AND
919 (l_changed));
920 end if;
921 --
922 if l_column = 'SEGMENT14' then
923 l_changed := (benutils.column_changed(l_old_pac.SEGMENT14
924 ,l_new_pac.SEGMENT14,l_new_val) AND
925 benutils.column_changed(l_new_pac.SEGMENT14
926 ,l_old_pac.SEGMENT14,l_old_val) AND
927 (l_changed));
928 end if;
929 --
930 if l_column = 'SEGMENT15' then
931 l_changed := (benutils.column_changed(l_old_pac.SEGMENT15
932 ,l_new_pac.SEGMENT15,l_new_val) AND
933 benutils.column_changed(l_new_pac.SEGMENT15
934 ,l_old_pac.SEGMENT15,l_old_val) AND
935 (l_changed));
936 end if;
937 --
938 if l_column = 'SEGMENT16' then
939 l_changed := (benutils.column_changed(l_old_pac.SEGMENT16
940 ,l_new_pac.SEGMENT16,l_new_val) AND
941 benutils.column_changed(l_new_pac.SEGMENT16
945 --
942 ,l_old_pac.SEGMENT16,l_old_val) AND
943 (l_changed));
944 end if;
946 if l_column = 'SEGMENT17' then
947 l_changed := (benutils.column_changed(l_old_pac.SEGMENT17
948 ,l_new_pac.SEGMENT17,l_new_val) AND
949 benutils.column_changed(l_new_pac.SEGMENT17
950 ,l_old_pac.SEGMENT17,l_old_val) AND
951 (l_changed));
952 end if;
953 --
954 if l_column = 'SEGMENT18' then
955 l_changed := (benutils.column_changed(l_old_pac.SEGMENT18
956 ,l_new_pac.SEGMENT18,l_new_val) AND
957 benutils.column_changed(l_new_pac.SEGMENT18
958 ,l_old_pac.SEGMENT18,l_old_val) AND
959 (l_changed));
960 end if;
961 --
962 if l_column = 'SEGMENT19' then
963 l_changed := (benutils.column_changed(l_old_pac.SEGMENT19
964 ,l_new_pac.SEGMENT19,l_new_val) AND
965 benutils.column_changed(l_new_pac.SEGMENT19
966 ,l_old_pac.SEGMENT19,l_old_val) AND
967 (l_changed));
968 end if;
969 --
970 if l_column = 'SEGMENT20' then
971 l_changed := (benutils.column_changed(l_old_pac.SEGMENT20
972 ,l_new_pac.SEGMENT20,l_new_val) AND
973 benutils.column_changed(l_new_pac.SEGMENT20
974 ,l_old_pac.SEGMENT20,l_old_val) AND
975 (l_changed));
976 end if;
977 --
978 if l_column = 'SEGMENT21' then
979 l_changed := (benutils.column_changed(l_old_pac.SEGMENT21
980 ,l_new_pac.SEGMENT21,l_new_val) AND
981 benutils.column_changed(l_new_pac.SEGMENT21
982 ,l_old_pac.SEGMENT21,l_old_val) AND
983 (l_changed));
984 end if;
985 --
986 if l_column = 'SEGMENT22' then
987 l_changed := (benutils.column_changed(l_old_pac.SEGMENT22
988 ,l_new_pac.SEGMENT22,l_new_val) AND
989 benutils.column_changed(l_new_pac.SEGMENT22
990 ,l_old_pac.SEGMENT22,l_old_val) AND
991 (l_changed));
992 end if;
993 --
994 if l_column = 'SEGMENT23' then
995 l_changed := (benutils.column_changed(l_old_pac.SEGMENT23
996 ,l_new_pac.SEGMENT23,l_new_val) AND
997 benutils.column_changed(l_new_pac.SEGMENT23
998 ,l_old_pac.SEGMENT23,l_old_val) AND
999 (l_changed));
1000 end if;
1001 --
1002 if l_column = 'SEGMENT24' then
1003 l_changed := (benutils.column_changed(l_old_pac.SEGMENT24
1004 ,l_new_pac.SEGMENT24,l_new_val) AND
1005 benutils.column_changed(l_new_pac.SEGMENT24
1006 ,l_old_pac.SEGMENT24,l_old_val) AND
1007 (l_changed));
1008 end if;
1009 --
1010 if l_column = 'SEGMENT25' then
1011 l_changed := (benutils.column_changed(l_old_pac.SEGMENT25
1012 ,l_new_pac.SEGMENT25,l_new_val) AND
1013 benutils.column_changed(l_new_pac.SEGMENT25
1014 ,l_old_pac.SEGMENT25,l_old_val) AND
1015 (l_changed));
1016 end if;
1017 --
1018 if l_column = 'SEGMENT26' then
1019 l_changed := (benutils.column_changed(l_old_pac.SEGMENT26
1020 ,l_new_pac.SEGMENT26,l_new_val) AND
1021 benutils.column_changed(l_new_pac.SEGMENT26
1022 ,l_old_pac.SEGMENT26,l_old_val) AND
1023 (l_changed));
1024 end if;
1025 --
1026 if l_column = 'SEGMENT27' then
1027 l_changed := (benutils.column_changed(l_old_pac.SEGMENT27
1028 ,l_new_pac.SEGMENT27,l_new_val) AND
1029 benutils.column_changed(l_new_pac.SEGMENT27
1030 ,l_old_pac.SEGMENT27,l_old_val) AND
1031 (l_changed));
1032 end if;
1033 --
1034 if l_column = 'SEGMENT28' then
1035 l_changed := (benutils.column_changed(l_old_pac.SEGMENT28
1036 ,l_new_pac.SEGMENT28,l_new_val) AND
1037 benutils.column_changed(l_new_pac.SEGMENT28
1038 ,l_old_pac.SEGMENT28,l_old_val) AND
1039 (l_changed));
1040 end if;
1041 --
1042 if l_column = 'SEGMENT29' then
1043 l_changed := (benutils.column_changed(l_old_pac.SEGMENT29
1044 ,l_new_pac.SEGMENT29,l_new_val) AND
1045 benutils.column_changed(l_new_pac.SEGMENT29
1046 ,l_old_pac.SEGMENT29,l_old_val) AND
1047 (l_changed));
1048 end if;
1049 --
1050 if l_column = 'SEGMENT30' then
1051 l_changed := (benutils.column_changed(l_old_pac.SEGMENT30
1052 ,l_new_pac.SEGMENT30,l_new_val) AND
1053 benutils.column_changed(l_new_pac.SEGMENT30
1054 ,l_old_pac.SEGMENT30,l_old_val) AND
1055 (l_changed));
1056 end if;
1057
1058 hr_utility.set_location(' ben_pac_trigger - after l_changed', 40);
1059 --
1060 -- Checking the rule output and the rule override flag.
1061 -- Whether the rule is mandatory or not, rule output should return 'Y'
1062 -- Rule Mandatory flag is just to override the column data change.
1063
1064 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
1065 l_changed := l_changed AND TRUE ;
1066 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
1067 l_changed := l_changed AND TRUE;
1068 elsif l_rule_output = 'N' then
1069 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
1070 l_changed := FALSE;
1071 end if;
1072
1073 hr_utility.set_location('After the rule Check ',20.05);
1074 if l_changed then
1075 hr_utility.set_location('l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
1076 else
1080 -- Check for Column Mandatory Change
1077 hr_utility.set_location('l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
1078 end if;
1079
1081 -- If column change is mandatory and data change has failed then dont trigger
1082 -- If column change is non-mandatory and the data change has passed, then trigger.
1083
1084 if l_chg_mandatory_cd = 'Y' and not l_changed then
1085 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
1086 l_changed := FALSE;
1087 l_trigger := FALSE;
1088 exit;
1089 elsif l_chg_mandatory_cd = 'Y' and l_changed then
1090 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
1091 l_changed := TRUE;
1092 elsif l_chg_mandatory_cd = 'N' and l_changed then
1093 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
1094 l_changed := TRUE;
1095 l_trigger := TRUE;
1096 exit;
1097 end if;
1098
1099 hr_utility.set_location('After the Mandatory code check ',20.05);
1100 if l_changed then
1101 hr_utility.set_location('l_change TRUE ', 20.1);
1102 else
1103 hr_utility.set_location('l_change FALSE ', 20.1);
1104 end if;
1105 end loop;
1106 --
1107 hr_utility.set_location(' ben_pac_trigger', 30);
1108 --
1109 if l_changed then
1110 hr_utility.set_location(' Change detected', 30);
1111 end if;
1112 --
1113 l_ptnl_id := 0;
1114 l_ovn :=null;
1115 if l_trigger then
1116 if l_type = 'P' then
1117 open le_exists(p_new.person_id,l_ler_id,l_lf_evt_ocrd_date);
1118 fetch le_exists into l_le_exists;
1119 if le_exists%notfound then
1120 hr_utility.set_location(' Entering: ben_pac_trigger ', 60);
1121
1122 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
1123 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
1124 (p_validate => false
1125 ,p_ptnl_ler_for_per_id => l_ptnl_id
1126 ,p_ntfn_dt => l_system_date
1127 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
1128 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
1129 ,p_ler_id => l_ler_id
1130 ,p_ler_typ_cd => l_typ_cd
1131 ,p_person_id => p_new.person_id
1132 ,p_business_group_Id =>p_new.business_group_id
1133 ,p_object_version_number => l_ovn
1134 ,p_effective_date => l_effective_start_date
1135 ,p_dtctd_dt => l_effective_start_date);
1136 end if;
1137 close le_exists;
1138 --
1139 elsif l_type = 'R' then
1140 open get_contacts(p_new.person_id);
1141 loop
1142 fetch get_contacts into l_hld_person_id;
1143 exit when get_contacts%notfound;
1144 open le_exists(l_hld_person_id,l_ler_id,l_lf_evt_ocrd_date);
1145 fetch le_exists into l_le_exists;
1146 if le_exists%notfound then
1147 hr_utility.set_location(' Entering: ben_pac_trigger5', 60);
1148
1149 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
1150 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
1151 (p_validate => false
1152 ,p_ptnl_ler_for_per_id => l_ptnl_id
1153 ,p_ntfn_dt => l_system_date
1154 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
1155 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
1156 ,p_ler_id => l_ler_id
1157 ,p_ler_typ_cd => l_typ_cd
1158 ,p_person_id => l_hld_person_id
1159 ,p_business_group_Id =>p_new.business_group_id
1160 ,p_object_version_number => l_ovn
1161 ,p_effective_date => l_effective_start_date
1162 ,p_dtctd_dt => l_effective_start_date);
1163 end if;
1164 l_ptnl_id := 0;
1165 l_ovn :=null;
1166 close le_exists;
1167 end loop;
1168 close get_contacts;
1169 end if;
1170 --
1171 -- reset the variables.
1172 --
1173 hr_utility.set_location(' ben_pac_trigger', 100);
1174 l_changed := FALSE;
1175 l_trigger := TRUE;
1176 l_ovn := NULL;
1177 end if;
1178 close get_ler_col;
1179 end loop;
1180 hr_utility.set_location(' ben_pac_trigger', 110);
1181 close get_ler;
1182 hr_utility.set_location(' Leaving: ben_pac_trigger', 120);
1183 end;
1184 --
1185 end ben_pac_ler;