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