[Home] [Help]
PACKAGE BODY: APPS.BEN_ECD_LER
Source
1 package body ben_ecd_ler as
2 /* $Header: beecdtrg.pkb 120.2.12010000.2 2008/08/05 14:21:41 ubhat ship $*/
3 procedure ler_chk(p_old IN g_ecd_ler_rec
4 ,p_new IN g_ecd_ler_rec
5 ,p_effective_date in date default NULL ) is
6
7 l_procedure varchar2(60) := 'ben_ecd_ler.ler_chk';
8 --
9 l_session_date DATE;
10 l_person_id NUMBER;
11 l_system_date DATE;
12 --
13 cursor get_person_id (p_prtt_enrt_rslt_id number
14 ,p_business_group_id number)IS
15 select person_id
16 from ben_prtt_enrt_rslt_f
17 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
18 and business_group_id = p_business_group_id
19 and prtt_enrt_rslt_stat_cd is null;
20 --
21 cursor get_session_date IS
22 select effective_date
23 from fnd_sessions
24 where session_id = userenv('SESSIONID');
25 --
26 cursor get_system_date IS
27 select trunc(sysdate)
28 from dual;
29 --
30 cursor get_ler(l_status varchar2) is
31 select ler.ler_id
32 , ler.typ_cd
33 , ler.ocrd_dt_det_cd
34 from ben_ler_f ler
35 where ler.business_group_id = p_new.business_group_id
36 and l_session_date between ler.effective_start_date
37 and ler.effective_end_date -- For Bug 3299709
38 and ( l_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
39 and ((exists
40 (select 1
41 from ben_per_info_chg_cs_ler_f psl
42 , ben_ler_per_info_cs_ler_f lpl
43 where source_table = 'BEN_ELIG_CVRD_DPNT_F'
44 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
45 and lpl.business_group_id = psl.business_group_id
46 and lpl.business_group_id = ler.business_group_id
47 and l_session_date between psl.effective_start_date
48 and psl.effective_end_date
49 and l_session_date between lpl.effective_start_date
50 and lpl.effective_end_date -- For Bug 3299709
51 and lpl.ler_id = ler.ler_id)
52 )
53 OR (exists
54 (select 1
55 from ben_rltd_per_chg_cs_ler_f rpc
56 , ben_ler_rltd_per_cs_ler_f lrp
57 where source_table = 'BEN_ELIG_CVRD_DPNT_F'
58 and lrp.business_group_id = rpc.business_group_id
59 and lrp.business_group_id = ler.business_group_id
60 and l_session_date between rpc.effective_start_date
61 and rpc.effective_end_date
62 and l_session_date between lrp.effective_start_date
63 and lrp.effective_end_date -- For Bug 3299709
64 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
65 and lrp.ler_id = ler.ler_id)
66 ))
67 order by ler.ler_id;
68 --
69 cursor get_ler_col(p_ler_id NUMBER) is
70 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
71 from ben_ler_per_info_cs_ler_f lpl, ben_per_info_chg_cs_ler_f psl
72 where lpl.ler_id = p_ler_id and
73 lpl.business_group_id = p_new.business_group_id
74 and lpl.business_group_id = psl.business_group_id
75 and l_session_date between psl.effective_start_date
76 and psl.effective_end_date
77 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
78 and source_table = 'BEN_ELIG_CVRD_DPNT_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 = p_new.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 rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
88 and source_table = 'BEN_ELIG_CVRD_DPNT_F'
89 order by 1;
90 --
91 cursor le_exists(p_person_id in number
92 ,p_ler_id in number
93 ,p_lf_evt_ocrd_dt in date) is
94 select 'Y'
95 from ben_ptnl_ler_for_per
96 where person_id = p_person_id
97 and ler_id = p_ler_id
98 and ptnl_ler_for_per_stat_cd = 'DTCTD'
99 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
100 --
101
102 cursor get_contacts(p_contact_person_id in number) is
103 select person_id
104 from per_contact_relationships
105 where contact_person_id = p_contact_person_id
106 and business_group_id = p_new.business_group_id
107 and l_session_date between nvl(date_start,l_session_date)
108 and nvl(date_end,l_session_date)
109 and personal_flag = 'Y'
110 order by person_id;
111 --
112 l_changed BOOLEAN;
113 l_ler_id NUMBER;
114 l_typ_cd ben_ler_f.typ_cd%type ;
115 l_column ben_rltd_per_chg_cs_ler_f.source_column%type; -- VARCHAR2(30);
116 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type; -- VARCHAR2(30);
117 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type; -- VARCHAR2(30);
118 l_ocrd_dt_cd VARCHAR2(30);
119 l_per_info_chg_cs_ler_rl number;
120 l_rule_output VARCHAR2(1);
121 l_ovn NUMBER;
122 l_ptnl_id NUMBER;
123 l_effective_end_date DATE := to_date('31-12-4712','DD-MM-YYYY');
124 l_effective_start_date DATE ;
125 l_lf_evt_ocrd_date DATE ;
126 l_le_exists VARCHAR2(1);
127 l_mnl_dt date;
128 l_dtctd_dt date;
129 l_procd_dt date;
130 l_unprocd_dt date;
131 l_voidd_dt date;
132 l_type VARCHAR2(1);
133 l_hld_person_id NUMBER;
134
135 l_cvg_strt_dt date;
136 l_cvg_thru_dt date;
137 --
138 l_bool BOOLEAN;
139 l_status VARCHAR2(1);
140 l_industry VARCHAR2(1);
141 l_col_new_val VARCHAR2(1000);
142 l_col_old_val varchar2(1000);
143 --
144 l_rule_overrides_flag VARCHAR2(1);
145 l_chg_mandatory_cd VARCHAR2(1);
146 l_trigger boolean := TRUE;
147 --
148 --
149 begin
150
151 -- Bug 3320133
152 benutils.set_data_migrator_mode;
153 if hr_general.g_data_migrator_mode in ( 'Y','P') then
154 --
155 return;
156 --
157 end if;
158 --
159 -- End of Bug 3320133
160
161 l_bool :=fnd_installation.get(appl_id => 805
162 ,dep_appl_id =>805
163 ,status => l_status
164 ,industry => l_industry);
165
166 hr_utility.set_location('Entering:'||l_procedure, 10);
167 l_changed := FALSE;
168 --
169 --Bug 4390330 We need to get the dependent person here.
170 --
171 --l_person_id := p_new.dpnt_person_id ;
172 --
173
174 --Bug 5523824 : Reverted the changes made for Bug 4390330
175 -- l_person_id should hold the person_id instead of the dpnt_person_id
176
177 -- in certain situations, the result id on the dependent is cleared out
178 -- if this is so, we need to look at the old dpnt record to get it.
179 if p_new.prtt_enrt_rslt_id is not null then
180 open get_person_id(p_prtt_enrt_rslt_id => p_new.prtt_enrt_rslt_id
181 ,p_business_group_id => p_new.business_group_id);
182 fetch get_person_id into l_person_id;
183 close get_person_id;
184 elsif p_old.prtt_enrt_rslt_id is not null then
185 open get_person_id(p_prtt_enrt_rslt_id => p_old.prtt_enrt_rslt_id
186 ,p_business_group_id => p_old.business_group_id);
187 fetch get_person_id into l_person_id;
188 close get_person_id;
189 end if;
190
191 hr_utility.set_location('RKG l_person_id :'|| l_person_id, 10);
192
193 /*
194 open get_session_date;
195 fetch get_session_date into l_session_date;
196 close get_session_date;
197 */
198 --
199 if p_effective_date is not null then
200 --
201 l_session_date := p_effective_date ;
202 --
203 else
204 --
205 open get_session_date;
206 fetch get_session_date into l_session_date;
207 close get_session_date;
208 --
209 end if;
210 --
211 open get_system_date;
212 fetch get_system_date into l_system_date;
213 close get_system_date;
214 l_effective_start_date := l_session_date;
215
216 -- in some situations the date we use for occured on date is null,
217 -- use session date instead.
218 if p_new.cvg_strt_dt is null then
219 l_cvg_strt_dt := l_session_date;
220 else
221 l_cvg_strt_dt := p_new.cvg_strt_dt;
222 end if;
223 if p_new.cvg_thru_dt is null then
224 l_cvg_thru_dt := l_session_date;
225 else
226 l_cvg_thru_dt := p_new.cvg_thru_dt;
227 end if;
228
229 hr_utility.set_location(' l_system_date:'||to_char(l_system_date), 20);
230 hr_utility.set_location(' l_session_date:'||to_char(l_session_date), 20);
231 open get_ler(l_status);
232
233 loop
234 fetch get_ler into l_ler_id, l_typ_cd, l_ocrd_dt_cd;
235 exit when get_ler%notfound;
236 l_trigger := TRUE;
237
238 if l_ocrd_dt_cd is null then
239 l_lf_evt_ocrd_date := p_new.effective_start_date;
240 else
241 --
242 -- Call the common date procedure.
243 --
244 ben_determine_date.main
245 (p_date_cd => l_ocrd_dt_cd
246 ,p_effective_date => p_new.effective_start_date
247 ,p_lf_evt_ocrd_dt => p_new.effective_start_date
248 ,p_returned_date => l_lf_evt_ocrd_date
249 );
250 end if;
251 --
252 open get_ler_col(l_ler_id);
253 loop
254 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;
255 exit when get_ler_col%NOTFOUND;
256 l_changed := TRUE;
257 if get_ler_col%ROWCOUNT = 1 then
258 l_changed := TRUE;
259 end if;
260 hr_utility.set_location(' l_column:'||l_column, 30);
261 --
262 -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
263 -- If it returns Y, then see the applicability of the data
264 -- changes based on new and old values.
265 --
266 l_rule_output := 'Y';
267 --
268 if l_per_info_chg_cs_ler_rl is not null then
269 --
270 if l_column = 'PRTT_ENRT_RSLT_ID' then
271 l_col_old_val := to_char(p_old.PRTT_ENRT_RSLT_ID);
272 l_col_new_val := to_char(p_new.PRTT_ENRT_RSLT_ID);
273 end if;
274 --
275 if l_column = 'EFFECTIVE_START_DATE' then
276 l_col_old_val := to_char(p_old.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS');
277 l_col_new_val := to_char(p_new.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS');
278 end if;
279 --
280 if l_column = 'EFFECTIVE_END_DATE' then
281 l_col_old_val := to_char(p_old.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS');
282 l_col_new_val := to_char(p_new.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS');
283 end if;
284 --
285 if l_column = 'CVG_STRT_DT' then
286 l_col_old_val := to_char(p_old.CVG_STRT_DT, 'YYYY/MM/DD HH24:MI:SS');
287 l_col_new_val := to_char(p_new.CVG_STRT_DT, 'YYYY/MM/DD HH24:MI:SS');
288 end if;
289 --
290 if l_column = 'CVG_THRU_DT' then
291 l_col_old_val := to_char(p_old.CVG_THRU_DT, 'YYYY/MM/DD HH24:MI:SS');
292 l_col_new_val := to_char(p_new.CVG_THRU_DT, 'YYYY/MM/DD HH24:MI:SS');
293 end if;
294 --
295 if l_column = 'OVRDN_FLAG' then
296 l_col_old_val := p_old.OVRDN_FLAG;
297 l_col_new_val := p_new.OVRDN_FLAG;
298 end if;
299 --
300 if l_column = 'OVRDN_THRU_DT' then
301 l_col_old_val := to_char(p_old.OVRDN_THRU_DT, 'YYYY/MM/DD HH24:MI:SS');
302 l_col_new_val := to_char(p_new.OVRDN_THRU_DT, 'YYYY/MM/DD HH24:MI:SS');
303 end if;
304 --
305 --
306 benutils.exec_rule(
307 p_formula_id => l_per_info_chg_cs_ler_rl,
308 p_effective_date => l_session_date,
309 p_lf_evt_ocrd_dt => null,
310 p_business_group_id => nvl(p_new.business_group_id, p_old.business_group_id),
311 p_person_id => l_person_id, -- nvl(p_new.person_id, p_old.person_id),
312 p_new_value => l_new_val,
313 p_old_value => l_old_val,
314 p_column_name => l_column,
315 p_param5 => 'BEN_PDP_IN_PRTT_ENRT_RSLT_ID',
316 p_param5_value => to_char(p_new.PRTT_ENRT_RSLT_ID),
317 p_param6 => 'BEN_PDP_IO_PRTT_ENRT_RSLT_ID',
318 p_param6_value => to_char(p_old.PRTT_ENRT_RSLT_ID),
319 p_param7 => 'BEN_PDP_IN_EFFECTIVE_START_DATE',
320 p_param7_value => to_char(p_new.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS'),
321 p_param8 => 'BEN_PDP_IO_EFFECTIVE_START_DATE',
322 p_param8_value => to_char(p_old.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS'),
323 p_param9 => 'BEN_PDP_IN_EFFECTIVE_END_DATE',
324 p_param9_value => to_char(p_new.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS'),
325 p_param10 => 'BEN_PDP_IO_EFFECTIVE_END_DATE',
326 p_param10_value => to_char(p_old.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS'),
327 p_param11 => 'BEN_PDP_IN_CVG_STRT_DT',
328 p_param11_value => to_char(p_new.CVG_STRT_DT, 'YYYY/MM/DD HH24:MI:SS'),
329 p_param12 => 'BEN_PDP_IO_CVG_STRT_DT',
330 p_param12_value => to_char(p_old.CVG_STRT_DT, 'YYYY/MM/DD HH24:MI:SS'),
331 p_param13 => 'BEN_PDP_IN_CVG_THRU_DT',
332 p_param13_value => to_char(p_new.CVG_THRU_DT, 'YYYY/MM/DD HH24:MI:SS'),
333 p_param14 => 'BEN_PDP_IO_CVG_THRU_DT',
334 p_param14_value => to_char(p_old.CVG_THRU_DT, 'YYYY/MM/DD HH24:MI:SS'),
335 p_param15 => 'BEN_PDP_IN_OVRDN_FLAG',
336 p_param15_value => p_new.OVRDN_FLAG,
337 p_param16 => 'BEN_PDP_IO_OVRDN_FLAG',
338 p_param16_value => p_old.OVRDN_FLAG,
339 p_param17 => 'BEN_PDP_IN_OVRDN_THRU_DT',
340 p_param17_value => to_char(p_new.OVRDN_THRU_DT, 'YYYY/MM/DD HH24:MI:SS'),
341 p_param18 => 'BEN_PDP_IO_OVRDN_THRU_DT',
342 p_param18_value => to_char(p_old.OVRDN_THRU_DT, 'YYYY/MM/DD HH24:MI:SS'),
343 p_param19 => 'BEN_IV_LER_ID', /* Bug 3891096 */
344 p_param19_value => to_char(l_ler_id),
345 p_pk_id => to_char(p_new.ELIG_CVRD_DPNT_ID),
346 p_ret_val => l_rule_output);
347 --
348 end if;
349 --
350
351 --
352 if l_column = 'CVG_STRT_DT' then
353 l_changed := (benutils.column_changed(p_old.cvg_strt_dt
354 ,p_new.cvg_strt_dt,l_new_val) AND
355 benutils.column_changed(p_new.cvg_strt_dt
356 ,p_old.cvg_strt_dt,l_old_val) AND
357 (l_changed));
358 hr_utility.set_location(' CVG_STRT_DT',40);
359 --
360 if l_ocrd_dt_cd is null then
361 l_lf_evt_ocrd_date := l_cvg_strt_dt;
362 else
363 --
364 -- Call the common date procedure.
365 --
366 ben_determine_date.main
367 (p_date_cd => l_ocrd_dt_cd
368 ,p_effective_date => l_cvg_strt_dt
369 ,p_lf_evt_ocrd_dt => p_new.cvg_strt_dt
370 ,p_returned_date => l_lf_evt_ocrd_date
371 );
372 end if;
373 --
374 end if;
375 --
376 if l_column = 'CVG_THRU_DT' then
377 if p_new.cvg_thru_dt = hr_api.g_eot then
378 l_changed := FALSE;
379 end if;
380 l_changed := (benutils.column_changed(p_old.cvg_thru_dt
381 ,p_new.cvg_thru_dt,l_new_val) AND
382 benutils.column_changed(p_new.cvg_thru_dt
383 ,p_old.cvg_thru_dt,l_old_val) AND
384 (l_changed));
385 hr_utility.set_location(' CVG_THRU_DT',41);
386 hr_utility.set_location(' p_new.cvg_thru_dt:'||to_char(p_new.cvg_thru_dt),41);
387 if l_ocrd_dt_cd is null then
388 l_lf_evt_ocrd_date := l_cvg_thru_dt;
389 else
390 --
391 -- Call the common date procedure.
392 --
393 ben_determine_date.main
394 (p_date_cd => l_ocrd_dt_cd
395 ,p_effective_date => l_cvg_thru_dt
396 ,p_lf_evt_ocrd_dt => p_new.cvg_thru_dt
397 ,p_returned_date => l_lf_evt_ocrd_date
398 );
399 end if;
400 --
401 end if;
402 --
403 if l_column = 'EFFECTIVE_START_DATE' then
404 l_changed := (benutils.column_changed(p_old.effective_start_date
405 ,p_new.effective_start_date,l_new_val) AND
406 benutils.column_changed(p_new.effective_start_date
407 ,p_old.effective_start_date,l_old_val) AND
408 (l_changed));
409 hr_utility.set_location(' EFFECTIVE_START_DATE',42);
410 end if;
411 --
412 if l_column = 'EFFECTIVE_END_DATE' then
413 if p_new.effective_end_date = hr_api.g_eot then
414 l_changed := FALSE;
415 end if;
416 l_changed := (benutils.column_changed(p_old.effective_end_date
417 ,p_new.effective_end_date,l_new_val) AND
418 benutils.column_changed(p_new.effective_end_date
419 ,p_old.effective_end_date,l_old_val) AND
420 (l_changed));
421 hr_utility.set_location(' EFFECTIVE_END_DATE',43);
422 --
423 if l_ocrd_dt_cd is null then
424 l_lf_evt_ocrd_date := p_new.effective_end_date;
425 else
426 --
427 -- Call the common date procedure.
428 --
429 ben_determine_date.main
430 (p_date_cd => l_ocrd_dt_cd
431 ,p_effective_date => p_new.effective_end_date
432 ,p_lf_evt_ocrd_dt => p_new.effective_end_date
433 ,p_returned_date => l_lf_evt_ocrd_date
434 );
435 end if;
436 --
437 end if;
438 --
439 if l_column = 'OVRDN_FLAG' then
440 l_changed := (benutils.column_changed(p_old.ovrdn_flag
441 ,p_new.ovrdn_flag,l_new_val) AND
442 benutils.column_changed(p_new.ovrdn_flag
443 ,p_old.ovrdn_flag,l_old_val) AND
444 (l_changed));
445 hr_utility.set_location(' OVRDN_FLAG ',44);
446 end if;
447 --
448 if l_column = 'OVRDN_THRU_DT' then
449 l_changed := (benutils.column_changed(p_old.ovrdn_thru_dt
450 ,p_new.ovrdn_thru_dt,l_new_val) AND
451 benutils.column_changed(p_new.ovrdn_thru_dt
452 ,p_old.ovrdn_thru_dt,l_old_val) AND
453 (l_changed));
454 hr_utility.set_location(' OVRDN_THRU_DT ',45);
455 end if;
456 --
457
458 --
459 -- Checking the rule output and the rule override flag.
460 -- Whether the rule is mandatory or not, rule output should return 'Y'
461 -- Rule Mandatory flag is just to override the column data change.
462
463 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
464 l_changed := TRUE ;
465 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
466 l_changed := l_changed AND TRUE;
467 elsif l_rule_output = 'N' then
468 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
469 l_changed := FALSE;
470 end if;
471
472 hr_utility.set_location('After the rule Check ',20.05);
473 if l_changed then
474 hr_utility.set_location(' l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
475 else
476 hr_utility.set_location(' l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
477 end if;
478 -- Check for Column Mandatory Change
479 -- If column change is mandatory and data change has failed then dont trigger
480 -- If column change is non-mandatory and the data change has passed, then trigger.
481
482 if l_chg_mandatory_cd = 'Y' and not l_changed then
483 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
484 l_changed := FALSE;
485 l_trigger := FALSE;
486 exit;
487 elsif l_chg_mandatory_cd = 'Y' and l_changed then
488 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
489 l_changed := TRUE;
490 -- exit; */
491 elsif l_chg_mandatory_cd = 'N' and l_changed then
492 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
493 l_changed := TRUE;
494 l_trigger := TRUE;
495 exit;
496 end if;
497
498 hr_utility.set_location('After the Mandatory code check ',20.05);
499 if l_changed then
500 hr_utility.set_location(' l_change TRUE ', 20.1);
501 else
502 hr_utility.set_location(' l_change FALSE ', 20.1);
503 end if;
504 --
505 /* if not l_changed then
506 exit;
507 end if; */
508 --
509 end loop;
510 hr_utility.set_location(' out nocopy of loop', 60);
511 l_ptnl_id := 0;
512 l_ovn :=null;
513 if l_trigger then
514 hr_utility.set_location('RKG l_person_id :'|| l_person_id, 10);
515 if l_type = 'P' then
516 open le_exists(l_person_id,l_ler_id,l_lf_evt_ocrd_date);
517 fetch le_exists into l_le_exists;
518 if le_exists%notfound then
519
520 hr_utility.set_location(' about to create ptnl-ler', 70);
521 hr_utility.set_location(' l_person_id:'||to_char(l_person_id)||
522 ' l_lf_evt_ocrd_date:'||to_char(l_lf_evt_ocrd_date), 70);
523 hr_utility.set_location(' l_ler_id:'||to_char(l_ler_id)||
524 ' l_effective_start_date:'||to_char(l_effective_start_date), 70);
525 hr_utility.set_location(' about to create ptnl-ler', 70);
526
527 if l_person_id is null then
528 fnd_message.set_name('BEN','BEN_92299_CANNOT_CREATE_PIL');
529 fnd_message.raise_error;
530 end if;
531 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
532 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
533 (p_validate => false
534 ,p_ptnl_ler_for_per_id => l_ptnl_id
535 ,p_ntfn_dt => l_system_date
536 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
537 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
538 ,p_ler_id => l_ler_id
539 ,p_ler_typ_cd => l_typ_cd
540 ,p_person_id => l_person_id
541 ,p_business_group_Id =>p_new.business_group_id
542 ,p_object_version_number => l_ovn
543 ,p_effective_date => l_effective_start_date
544 ,p_dtctd_dt => l_effective_start_date);
545 end if;
546 close le_exists;
547 elsif l_type = 'R' then
548 hr_utility.set_location(' l_type = R', 75);
549 open get_contacts(l_person_id);
550 loop
551 fetch get_contacts into l_hld_person_id;
552 exit when get_contacts%notfound;
553
554 hr_utility.set_location('RKG l_hld_person_id :'|| l_hld_person_id, 10);
555
556 open le_exists(l_hld_person_id,l_ler_id,l_lf_evt_ocrd_date);
557 fetch le_exists into l_le_exists;
558 if le_exists%notfound then
559 hr_utility.set_location(' le_exists%notfound', 76);
560
561 if l_hld_person_id is null then
562 fnd_message.set_name('BEN','BEN_92299_CANNOT_CREATE_PIL');
563 fnd_message.raise_error;
564 end if;
565
566 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
567 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
568 (p_validate => false
569 ,p_ptnl_ler_for_per_id => l_ptnl_id
570 ,p_ntfn_dt => l_system_date
571 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
572 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
573 ,p_ler_id => l_ler_id
574 ,p_ler_typ_cd => l_typ_cd
575 ,p_person_id => l_hld_person_id
576 ,p_business_group_Id =>p_new.business_group_id
577 ,p_object_version_number => l_ovn
578 ,p_effective_date => l_effective_start_date
579 ,p_dtctd_dt => l_effective_start_date);
580 end if;
581 l_ptnl_id := 0;
582 l_ovn :=null;
583 close le_exists;
584 end loop;
585 close get_contacts;
586 end if;
587 --
588 -- reset the variables.
589 --
590 hr_utility.set_location(' reset the variables', 80);
591 l_changed := FALSE;
592 l_trigger := TRUE;
593 l_ovn := NULL;
594 --l_effective_start_date := p_new.effective_start_date;
595 end if;
596 close get_ler_col;
597 end loop;
598
599 close get_ler;
600 hr_utility.set_location('Leaving:'||l_procedure, 99);
601
602 end;
603 end ben_ecd_ler;