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