[Home] [Help]
PACKAGE BODY: APPS.BEN_LER_SHD
Source
1 Package Body ben_ler_shd as
2 /* $Header: belerrhi.pkb 120.2 2006/11/03 10:34:58 vborkar noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ler_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 Function return_api_dml_status Return Boolean Is
14 --
15 l_proc varchar2(72) := g_package||'return_api_dml_status';
16 --
17 Begin
18 hr_utility.set_location('Entering:'||l_proc, 5);
19 --
20 Return (nvl(g_api_dml, false));
21 --
22 hr_utility.set_location(' Leaving:'||l_proc, 10);
23 End return_api_dml_status;
24 --
25 -- ----------------------------------------------------------------------------
26 -- |---------------------------< constraint_error >---------------------------|
30 --
27 -- ----------------------------------------------------------------------------
28 Procedure constraint_error
29 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
31 l_proc varchar2(72) := g_package||'constraint_error';
32 --
33 Begin
34 hr_utility.set_location('Entering:'||l_proc, 5);
35 --
36 If (p_constraint_name = 'BEN_LER_FK1') Then
37 fnd_message.set_name('BEN', 'BEN_91000_INVALID_BUS_GROUP');
38 fnd_message.raise_error;
39 ElsIf (p_constraint_name = 'BEN_LER_PK') Then
40 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
41 fnd_message.raise_error;
42 ElsIf (p_constraint_name = 'BEN_LER_UK1') Then
43 fnd_message.set_name('BEN', 'BEN_91009_NAME_NOT_UNIQUE');
44 fnd_message.raise_error;
45 ElsIf (p_constraint_name = 'BEN_LER_UK2') Then
46 fnd_message.set_name('BEN', 'BEN_91001_SEQ_NOT_UNIQUE');
47 fnd_message.raise_error;
48 Else
49 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
50 fnd_message.set_token('PROCEDURE', l_proc);
51 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
52 fnd_message.raise_error;
53 End If;
54 --
55 hr_utility.set_location(' Leaving:'||l_proc, 10);
56 End constraint_error;
57 --
58 -- ----------------------------------------------------------------------------
59 -- |-----------------------------< api_updating >-----------------------------|
60 -- ----------------------------------------------------------------------------
61 Function api_updating
62 (p_effective_date in date,
63 p_ler_id in number,
64 p_object_version_number in number
65 ) Return Boolean Is
66 --
67 --
68 -- Cursor selects the 'current' row from the HR Schema
69 --
70 Cursor C_Sel1 is
71 select
72 ler_id,
73 effective_start_date,
74 effective_end_date,
75 name,
76 business_group_id,
77 typ_cd,
78 lf_evt_oper_cd,
79 short_name,
80 short_code,
81 ptnl_ler_trtmt_cd,
82 ck_rltd_per_elig_flag,
83 ler_eval_rl,
84 cm_aply_flag,
85 ovridg_le_flag,
86 qualg_evt_flag,
87 whn_to_prcs_cd,
88 desc_txt,
89 tmlns_eval_cd,
90 tmlns_perd_cd,
91 tmlns_dys_num,
92 tmlns_perd_rl,
93 ocrd_dt_det_cd,
94 ler_stat_cd,
95 slctbl_slf_svc_cd,
96 ss_pcp_disp_cd,
97 ler_attribute_category,
98 ler_attribute1,
99 ler_attribute2,
100 ler_attribute3,
101 ler_attribute4,
102 ler_attribute5,
103 ler_attribute6,
104 ler_attribute7,
105 ler_attribute8,
106 ler_attribute9,
107 ler_attribute10,
108 ler_attribute11,
109 ler_attribute12,
110 ler_attribute13,
111 ler_attribute14,
112 ler_attribute15,
113 ler_attribute16,
114 ler_attribute17,
115 ler_attribute18,
116 ler_attribute19,
117 ler_attribute20,
118 ler_attribute21,
119 ler_attribute22,
120 ler_attribute23,
121 ler_attribute24,
122 ler_attribute25,
123 ler_attribute26,
124 ler_attribute27,
125 ler_attribute28,
126 ler_attribute29,
127 ler_attribute30,
128 object_version_number
129 from ben_ler_f
130 where ler_id = p_ler_id
131 and p_effective_date
132 between effective_start_date and effective_end_date;
133 --
134 l_proc varchar2(72) := g_package||'api_updating';
135 l_fct_ret boolean;
136 --
137 Begin
138 hr_utility.set_location('Entering:'||l_proc, 5);
139 --
140 If (p_effective_date is null or
141 p_ler_id is null or
142 p_object_version_number is null) Then
143 --
144 -- One of the primary key arguments is null therefore we must
145 -- set the returning function value to false
146 --
147 l_fct_ret := false;
148 Else
149 If (p_ler_id = g_old_rec.ler_id and
150 p_object_version_number = g_old_rec.object_version_number) Then
151 hr_utility.set_location(l_proc, 10);
152 --
153 -- The g_old_rec is current therefore we must
154 -- set the returning function to true
155 --
156 l_fct_ret := true;
157 Else
158 --
159 -- Select the current row
160 --
161 Open C_Sel1;
162 Fetch C_Sel1 Into g_old_rec;
163 If C_Sel1%notfound Then
164 Close C_Sel1;
165 --
166 -- The primary key is invalid therefore we must error
167 --
168 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
169 fnd_message.raise_error;
170 End If;
171 Close C_Sel1;
172 If (p_object_version_number <> g_old_rec.object_version_number) Then
173 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
174 fnd_message.raise_error;
175 End If;
176 hr_utility.set_location(l_proc, 15);
177 l_fct_ret := true;
178 End If;
179 End If;
180 hr_utility.set_location(' Leaving:'||l_proc, 20);
181 Return (l_fct_ret);
182 --
183 End api_updating;
184 --
185 -- ----------------------------------------------------------------------------
186 -- |--------------------------< find_dt_del_modes >---------------------------|
187 -- ----------------------------------------------------------------------------
188 Procedure find_dt_del_modes
189 (p_effective_date in date,
190 p_base_key_value in number,
191 p_zap out nocopy boolean,
192 p_delete out nocopy boolean,
196 l_proc varchar2(72) := g_package||'find_dt_del_modes';
193 p_future_change out nocopy boolean,
194 p_delete_next_change out nocopy boolean) is
195 --
197 --
198 l_parent_key_value1 number;
199 --
200 Cursor C_Sel1 Is
201 select t.ler_eval_rl
202 from ben_ler_f t
203 where t.ler_id = p_base_key_value
204 and p_effective_date
205 between t.effective_start_date and t.effective_end_date;
206 --
207 Begin
208 hr_utility.set_location('Entering:'||l_proc, 5);
209 Open C_Sel1;
210 Fetch C_Sel1 Into l_parent_key_value1;
211 If C_Sel1%notfound then
212 Close C_Sel1;
213 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
214 fnd_message.set_token('PROCEDURE', l_proc);
215 fnd_message.set_token('STEP','10');
216 fnd_message.raise_error;
217 End If;
218 Close C_Sel1;
219 --
220 -- Call the corresponding datetrack api
221 --
222 dt_api.find_dt_del_modes
223 (p_effective_date => p_effective_date,
224 p_base_table_name => 'ben_ler_f',
225 p_base_key_column => 'ler_id',
226 p_base_key_value => p_base_key_value,
227 p_parent_table_name1 => 'ff_formulas_f',
228 p_parent_key_column1 => 'formula_id',
229 p_parent_key_value1 => l_parent_key_value1,
230 p_zap => p_zap,
231 p_delete => p_delete,
232 p_future_change => p_future_change,
233 p_delete_next_change => p_delete_next_change);
234 --
235 hr_utility.set_location(' Leaving:'||l_proc, 10);
236 End find_dt_del_modes;
237 --
238 -- ----------------------------------------------------------------------------
239 -- |--------------------------< find_dt_upd_modes >---------------------------|
240 -- ----------------------------------------------------------------------------
241 Procedure find_dt_upd_modes
242 (p_effective_date in date,
243 p_base_key_value in number,
244 p_correction out nocopy boolean,
245 p_update out nocopy boolean,
246 p_update_override out nocopy boolean,
247 p_update_change_insert out nocopy boolean) is
248 --
249 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
250 --
251 Begin
252 hr_utility.set_location('Entering:'||l_proc, 5);
253 --
254 -- Call the corresponding datetrack api
255 --
256 dt_api.find_dt_upd_modes
257 (p_effective_date => p_effective_date,
258 p_base_table_name => 'ben_ler_f',
259 p_base_key_column => 'ler_id',
260 p_base_key_value => p_base_key_value,
261 p_correction => p_correction,
262 p_update => p_update,
263 p_update_override => p_update_override,
264 p_update_change_insert => p_update_change_insert);
265 --
266 hr_utility.set_location(' Leaving:'||l_proc, 10);
267 End find_dt_upd_modes;
268 --
269 -- ----------------------------------------------------------------------------
270 -- |------------------------< upd_effective_end_date >------------------------|
271 -- ----------------------------------------------------------------------------
272 Procedure upd_effective_end_date
273 (p_effective_date in date,
274 p_base_key_value in number,
275 p_new_effective_end_date in date,
276 p_validation_start_date in date,
277 p_validation_end_date in date,
278 p_object_version_number out nocopy number) is
279 --
280 l_proc varchar2(72) := g_package||'upd_effective_end_date';
281 l_object_version_number number;
282 --
283 Begin
284 hr_utility.set_location('Entering:'||l_proc, 5);
285 --
286 -- Because we are updating a row we must get the next object
287 -- version number.
288 --
289 l_object_version_number :=
290 dt_api.get_object_version_number
291 (p_base_table_name => 'ben_ler_f',
292 p_base_key_column => 'ler_id',
293 p_base_key_value => p_base_key_value);
294 --
295 hr_utility.set_location(l_proc, 10);
296 g_api_dml := true; -- Set the api dml status
297 --
298 -- Update the specified datetrack row setting the effective
299 -- end date to the specified new effective end date.
300 --
301 update ben_ler_f t
302 set t.effective_end_date = p_new_effective_end_date,
303 t.object_version_number = l_object_version_number
304 where t.ler_id = p_base_key_value
305 and p_effective_date
306 between t.effective_start_date and t.effective_end_date;
307 --
308 update ben_ler_f_tl t
309 set t.effective_end_date = p_new_effective_end_date,
310 t.last_update_date = sysdate,
311 t.last_updated_by = fnd_global.user_id,
312 t.last_update_login = fnd_global.login_id
313 where t.ler_id = p_base_key_value
314 and p_effective_date
315 between t.effective_start_date and t.effective_end_date;
316 --
317 g_api_dml := false; -- Unset the api dml status
318 p_object_version_number := l_object_version_number;
319 hr_utility.set_location(' Leaving:'||l_proc, 15);
320 --
321 Exception
322 When Others Then
323 g_api_dml := false; -- Unset the api dml status
324 Raise;
325 End upd_effective_end_date;
326 --
327 -- ----------------------------------------------------------------------------
328 -- |---------------------------------< lck >----------------------------------|
329 -- ----------------------------------------------------------------------------
330 Procedure lck
331 (p_effective_date in date,
332 p_datetrack_mode in varchar2,
333 p_ler_id in number,
334 p_object_version_number in number,
335 p_validation_start_date out nocopy date,
336 p_validation_end_date out nocopy date) is
340 l_validation_end_date date;
337 --
338 l_proc varchar2(72) := g_package||'lck';
339 l_validation_start_date date;
341 l_val_end_date1 date;
342 l_val_start_date1 date;
343 l_val_end_date2 date;
344 l_val_start_date2 date;
345 l_object_invalid exception;
346 l_argument varchar2(30);
347 --
348 -- Cursor C_Sel1 selects the current locked row as of session date
349 -- ensuring that the object version numbers match.
350 --
351 Cursor C_Sel1 is
352 select
353 ler_id,
354 effective_start_date,
355 effective_end_date,
356 name,
357 business_group_id,
358 typ_cd,
359 lf_evt_oper_cd,
360 short_name,
361 short_code,
362 ptnl_ler_trtmt_cd,
363 ck_rltd_per_elig_flag,
364 ler_eval_rl,
365 cm_aply_flag,
366 ovridg_le_flag,
367 qualg_evt_flag,
368 whn_to_prcs_cd,
369 desc_txt,
370 tmlns_eval_cd,
371 tmlns_perd_cd,
372 tmlns_dys_num,
373 tmlns_perd_rl,
374 ocrd_dt_det_cd,
375 ler_stat_cd,
376 slctbl_slf_svc_cd,
377 ss_pcp_disp_cd,
378 ler_attribute_category,
379 ler_attribute1,
380 ler_attribute2,
381 ler_attribute3,
382 ler_attribute4,
383 ler_attribute5,
384 ler_attribute6,
385 ler_attribute7,
386 ler_attribute8,
387 ler_attribute9,
388 ler_attribute10,
389 ler_attribute11,
390 ler_attribute12,
391 ler_attribute13,
392 ler_attribute14,
393 ler_attribute15,
394 ler_attribute16,
395 ler_attribute17,
396 ler_attribute18,
397 ler_attribute19,
398 ler_attribute20,
399 ler_attribute21,
400 ler_attribute22,
401 ler_attribute23,
402 ler_attribute24,
403 ler_attribute25,
404 ler_attribute26,
405 ler_attribute27,
406 ler_attribute28,
407 ler_attribute29,
408 ler_attribute30,
409 object_version_number
410 from ben_ler_f
411 where ler_id = p_ler_id
412 and p_effective_date
413 between effective_start_date and effective_end_date
414 for update nowait;
415 --
416 -- Cursor for special locking code :
417 --
418 -- Do not select rows where the rsltg_ler_id = ler_id because
419 -- those rows would have already been locked by the call to
420 -- dt_api.validate_dt_mode.
421 --
422 Cursor C_lock (p_ler_id in number) is
423 select t1.effective_end_date effective_end_date
424 from ben_css_rltd_per_per_in_ler_f t1
425 where (t1.css_rltd_per_per_in_ler_id,
426 t1.effective_start_date,
427 t1.effective_end_date) in
428 (select t2.css_rltd_per_per_in_ler_id,
429 max(t2.effective_start_date),
430 max(t2.effective_end_date)
431 from ben_css_rltd_per_per_in_ler_f t2
432 where t2.rsltg_ler_id = p_ler_id
433 and t2.ler_id <> p_ler_id
434 group by t2.css_rltd_per_per_in_ler_id)
435 order by t1.css_rltd_per_per_in_ler_id
436 for update nowait;
437 l_lck_date date; -- locked date
438 --
439 --
440 Begin
441 hr_utility.set_location('Entering:'||l_proc, 5);
442 --
443 -- Ensure that all the mandatory arguments are not null
444 --
445 hr_api.mandatory_arg_error(p_api_name => l_proc,
446 p_argument => 'effective_date',
447 p_argument_value => p_effective_date);
448 --
449 hr_api.mandatory_arg_error(p_api_name => l_proc,
450 p_argument => 'datetrack_mode',
451 p_argument_value => p_datetrack_mode);
452 --
453 hr_api.mandatory_arg_error(p_api_name => l_proc,
454 p_argument => 'ler_id',
455 p_argument_value => p_ler_id);
456 --
457 hr_api.mandatory_arg_error(p_api_name => l_proc,
458 p_argument => 'object_version_number',
459 p_argument_value => p_object_version_number);
460 --
461 -- Check to ensure the datetrack mode is not INSERT.
462 --
463 If (p_datetrack_mode <> 'INSERT') then
464 --
465 -- We must select and lock the current row.
466 --
467 Open C_Sel1;
468 Fetch C_Sel1 Into g_old_rec;
469 If C_Sel1%notfound then
470 Close C_Sel1;
471 --
472 -- The primary key is invalid therefore we must error
473 --
474 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
475 fnd_message.raise_error;
476 End If;
477 Close C_Sel1;
478 If (p_object_version_number <> g_old_rec.object_version_number) Then
479 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
480 fnd_message.raise_error;
481 End If;
482 hr_utility.set_location(l_proc, 15);
483 --
484 --
485 -- Validate the datetrack mode mode getting the validation start
486 -- and end dates for the specified datetrack operation.
487 --
488 dt_api.validate_dt_mode
489 (p_effective_date => p_effective_date,
490 p_datetrack_mode => p_datetrack_mode,
491 p_base_table_name => 'ben_ler_f',
492 p_base_key_column => 'ler_id',
493 p_base_key_value => p_ler_id,
494 p_parent_table_name1 => 'ff_formulas_f',
495 p_parent_key_column1 => 'formula_id',
496 p_parent_key_value1 => g_old_rec.ler_eval_rl,
497 p_child_table_name1 => 'ben_lee_rsn_f',
498 p_child_key_column1 => 'lee_rsn_id',
499 p_child_table_name2 => 'ben_ler_chg_plip_enrt_f',
503 p_child_table_name4 => 'ben_ler_chg_oipl_enrt_f',
500 p_child_key_column2 => 'ler_chg_plip_enrt_id',
501 p_child_table_name3 => 'ben_ler_chg_pl_nip_enrt_f',
502 p_child_key_column3 => 'ler_chg_pl_nip_enrt_id',
504 p_child_key_column4 => 'ler_chg_oipl_enrt_id',
505 p_child_table_name5 => 'ben_elig_to_prte_rsn_f',
506 p_child_key_column5 => 'elig_to_prte_rsn_id',
507 p_child_table_name6 => 'ben_ler_chg_dpnt_cvg_f',
508 p_child_key_column6 => 'ler_chg_dpnt_cvg_id',
509 p_child_table_name8 => 'ben_elig_per_f',
510 p_child_key_column8 => 'elig_per_id',
511 p_child_table_name9 => 'ben_ler_per_info_cs_ler_f',
512 p_child_key_column9 => 'ler_per_info_cs_ler_id',
513 p_enforce_foreign_locking => false, --true, -- 3301171
514 p_validation_start_date => l_val_start_date1,
515 p_validation_end_date => l_val_end_date1);
516
517 dt_api.validate_dt_mode
518 (p_effective_date => p_effective_date,
519 p_datetrack_mode => p_datetrack_mode,
520 p_base_table_name => 'ben_ler_f',
521 p_base_key_column => 'ler_id',
522 p_base_key_value => p_ler_id,
523 p_child_table_name1 => 'ben_ler_rltd_per_cs_ler_f',
524 p_child_key_column1 => 'ler_rltd_per_cs_ler_id',
525 p_child_table_name2 => 'ben_css_rltd_per_per_in_ler_f',
526 p_child_key_column2 => 'css_rltd_per_per_in_ler_id',
527 p_enforce_foreign_locking => false, --true, -- 3301171
528 p_validation_start_date => l_val_start_date2,
529 p_validation_end_date => l_val_end_date2);
530
531 --
532 -- because we call dt_api.validate_dt_mode twice, we must check
533 -- which set of validation dates we should use.
534 --
535 -- PETER, DO WE REALLY NEED TO DO THIS HERE, SINCE WE ARE ONLY CALLING
536 -- IT TWICE DUE TO ADDITIONAL CHILD TABLES, NOT ADDITIONAL PARENT
537 -- RECORDS??
538 --
539 if l_val_start_date1 > l_val_start_date2 then
540 l_validation_start_date := l_val_start_date1;
541 else
542 l_validation_start_date := l_val_start_date2;
543 end if;
544
545 if l_val_end_date1 < l_val_end_date2 then
546 l_validation_end_date := l_val_end_date1;
547 else
548 l_validation_end_date := l_val_end_date2;
549 end if;
550
551
552 -- Since we have two foreign keys from child ben_css_rltd_per_per_in_ler_f
553 -- to parent ben_ler_f, we must manually lock the rows on the child
554 -- where the foreign key name is not ler_id.
555 --
556 begin -- special locking code
557 if p_datetrack_mode = hr_api.g_delete and p_ler_id is not null then
558 Open C_lock(p_ler_id => p_ler_id);
559 <<Outer1>>
560 loop
561 Fetch C_lock Into l_lck_date;
562 If C_lock%notfound or C_lock%notfound is null then
563 exit Outer1;
564 elsif (l_lck_date > l_validation_start_date) then
565 --
566 -- The maximum end date is greater than the validation start date
567 -- therefore we must error
568 --
569 Close C_lock;
570 fnd_message.set_name('PAY', 'HR_7201_DT_NO_DELETE_CHILD');
571 fnd_message.raise_error;
572 end if;
573 end loop;
574 Close C_lock;
575 end if;
576 Exception
577 When HR_Api.Object_Locked then
578 --
579 -- The object is locked therefore we need to supply a meaningful
580 -- error message.
581 --
582 if C_lock%isopen then
583 Close C_lock;
584 end if;
585 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
586 fnd_message.set_token('TABLE_NAME', 'ben_css_rltd_per_per_in_ler_f');
587 fnd_message.raise_error;
588 end; -- special locking code
589
590 Else
591 --
592 -- We are doing a datetrack 'INSERT' which is illegal within this
593 -- procedure therefore we must error (note: to lck on insert the
594 -- private procedure ins_lck should be called).
595 --
596 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
597 fnd_message.set_token('PROCEDURE', l_proc);
598 fnd_message.set_token('STEP','20');
599 fnd_message.raise_error;
600 End If;
601 --
602 -- Set the validation start and end date OUT arguments
603 --
604 p_validation_start_date := l_validation_start_date;
605 p_validation_end_date := l_validation_end_date;
606 --
607 hr_utility.set_location(' Leaving:'||l_proc, 30);
608 --
609 -- We need to trap the ORA LOCK exception
610 --
611 Exception
612 When HR_Api.Object_Locked then
613 --
614 -- The object is locked therefore we need to supply a meaningful
615 -- error message.
616 --
617 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
618 fnd_message.set_token('TABLE_NAME', 'ben_ler_f');
619 fnd_message.raise_error;
620 When l_object_invalid then
621 --
622 -- The object doesn't exist or is invalid
623 --
624 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
625 fnd_message.set_token('TABLE_NAME', 'ben_ler_f');
626 fnd_message.raise_error;
627 End lck;
628 --
629 -- ----------------------------------------------------------------------------
630 -- |-----------------------------< convert_args >-----------------------------|
631 -- ----------------------------------------------------------------------------
632 Function convert_args
633 (
634 p_ler_id in number,
635 p_effective_start_date in date,
636 p_effective_end_date in date,
640 p_lf_evt_oper_cd in varchar2,
637 p_name in varchar2,
638 p_business_group_id in number,
639 p_typ_cd in varchar2,
641 p_short_name in varchar2,
642 p_short_code in varchar2,
643 p_ptnl_ler_trtmt_cd in varchar2,
644 p_ck_rltd_per_elig_flag in varchar2,
645 p_ler_eval_rl in number,
646 p_cm_aply_flag in varchar2,
647 p_ovridg_le_flag in varchar2,
648 p_qualg_evt_flag in varchar2,
649 p_whn_to_prcs_cd in varchar2,
650 p_desc_txt in varchar2,
651 p_tmlns_eval_cd in varchar2,
652 p_tmlns_perd_cd in varchar2,
653 p_tmlns_dys_num in number,
654 p_tmlns_perd_rl in number,
655 p_ocrd_dt_det_cd in varchar2,
656 p_ler_stat_cd in varchar2,
657 p_slctbl_slf_svc_cd in varchar2,
658 p_ss_pcp_disp_cd in varchar2,
659 p_ler_attribute_category in varchar2,
660 p_ler_attribute1 in varchar2,
661 p_ler_attribute2 in varchar2,
662 p_ler_attribute3 in varchar2,
663 p_ler_attribute4 in varchar2,
664 p_ler_attribute5 in varchar2,
665 p_ler_attribute6 in varchar2,
666 p_ler_attribute7 in varchar2,
667 p_ler_attribute8 in varchar2,
668 p_ler_attribute9 in varchar2,
669 p_ler_attribute10 in varchar2,
670 p_ler_attribute11 in varchar2,
671 p_ler_attribute12 in varchar2,
672 p_ler_attribute13 in varchar2,
673 p_ler_attribute14 in varchar2,
674 p_ler_attribute15 in varchar2,
675 p_ler_attribute16 in varchar2,
676 p_ler_attribute17 in varchar2,
677 p_ler_attribute18 in varchar2,
678 p_ler_attribute19 in varchar2,
679 p_ler_attribute20 in varchar2,
680 p_ler_attribute21 in varchar2,
681 p_ler_attribute22 in varchar2,
682 p_ler_attribute23 in varchar2,
683 p_ler_attribute24 in varchar2,
684 p_ler_attribute25 in varchar2,
685 p_ler_attribute26 in varchar2,
686 p_ler_attribute27 in varchar2,
687 p_ler_attribute28 in varchar2,
688 p_ler_attribute29 in varchar2,
689 p_ler_attribute30 in varchar2,
690 p_object_version_number in number
691 )
692 Return g_rec_type is
693 --
694 l_rec g_rec_type;
695 l_proc varchar2(72) := g_package||'convert_args';
696 --
697 Begin
698 --
699 hr_utility.set_location('Entering:'||l_proc, 5);
700 --
701 -- Convert arguments into local l_rec structure.
702 --
703 l_rec.ler_id := p_ler_id;
704 l_rec.effective_start_date := p_effective_start_date;
705 l_rec.effective_end_date := p_effective_end_date;
706 l_rec.name := p_name;
707 l_rec.lf_evt_oper_cd := p_lf_evt_oper_cd;
708 l_rec.short_name := p_short_name;
709 l_rec.short_code := p_short_code;
710 l_rec.business_group_id := p_business_group_id;
711 l_rec.typ_cd := p_typ_cd;
712 l_rec.ptnl_ler_trtmt_cd := p_ptnl_ler_trtmt_cd;
713 l_rec.ck_rltd_per_elig_flag := p_ck_rltd_per_elig_flag;
714 l_rec.ler_eval_rl := p_ler_eval_rl;
715 l_rec.cm_aply_flag := p_cm_aply_flag;
716 l_rec.ovridg_le_flag := p_ovridg_le_flag;
717 l_rec.qualg_evt_flag := p_qualg_evt_flag;
718 l_rec.whn_to_prcs_cd := p_whn_to_prcs_cd;
719 l_rec.desc_txt := p_desc_txt;
720 l_rec.tmlns_eval_cd := p_tmlns_eval_cd;
721 l_rec.tmlns_perd_cd := p_tmlns_perd_cd;
722 l_rec.tmlns_dys_num := p_tmlns_dys_num;
723 l_rec.tmlns_perd_rl := p_tmlns_perd_rl;
724 l_rec.ocrd_dt_det_cd := p_ocrd_dt_det_cd;
725 l_rec.ler_stat_cd := p_ler_stat_cd;
726 l_rec.slctbl_slf_svc_cd := p_slctbl_slf_svc_cd;
727 l_rec.ss_pcp_disp_cd := p_ss_pcp_disp_cd;
728 l_rec.ler_attribute_category := p_ler_attribute_category;
729 l_rec.ler_attribute1 := p_ler_attribute1;
730 l_rec.ler_attribute2 := p_ler_attribute2;
731 l_rec.ler_attribute3 := p_ler_attribute3;
732 l_rec.ler_attribute4 := p_ler_attribute4;
733 l_rec.ler_attribute5 := p_ler_attribute5;
734 l_rec.ler_attribute6 := p_ler_attribute6;
735 l_rec.ler_attribute7 := p_ler_attribute7;
736 l_rec.ler_attribute8 := p_ler_attribute8;
737 l_rec.ler_attribute9 := p_ler_attribute9;
738 l_rec.ler_attribute10 := p_ler_attribute10;
739 l_rec.ler_attribute11 := p_ler_attribute11;
740 l_rec.ler_attribute12 := p_ler_attribute12;
741 l_rec.ler_attribute13 := p_ler_attribute13;
742 l_rec.ler_attribute14 := p_ler_attribute14;
743 l_rec.ler_attribute15 := p_ler_attribute15;
744 l_rec.ler_attribute16 := p_ler_attribute16;
745 l_rec.ler_attribute17 := p_ler_attribute17;
746 l_rec.ler_attribute18 := p_ler_attribute18;
747 l_rec.ler_attribute19 := p_ler_attribute19;
748 l_rec.ler_attribute20 := p_ler_attribute20;
749 l_rec.ler_attribute21 := p_ler_attribute21;
750 l_rec.ler_attribute22 := p_ler_attribute22;
751 l_rec.ler_attribute23 := p_ler_attribute23;
752 l_rec.ler_attribute24 := p_ler_attribute24;
753 l_rec.ler_attribute25 := p_ler_attribute25;
754 l_rec.ler_attribute26 := p_ler_attribute26;
755 l_rec.ler_attribute27 := p_ler_attribute27;
756 l_rec.ler_attribute28 := p_ler_attribute28;
757 l_rec.ler_attribute29 := p_ler_attribute29;
758 l_rec.ler_attribute30 := p_ler_attribute30;
759 l_rec.object_version_number := p_object_version_number;
760 --
761 -- Return the plsql record structure.
762 --
763 hr_utility.set_location(' Leaving:'||l_proc, 10);
764 Return(l_rec);
765 --
766 End convert_args;
767 --
768 -- ----------------------------------------------------------------------------
769 -- |--------------------------<add_language>----------------------------------|
770 -- ----------------------------------------------------------------------------
771 Procedure add_language
772 is
773 begin
774 delete from ben_ler_f_tl t
775 where not exists
776 (select null
777 from ben_ler_f_tl b
778 where b.ler_id = t.ler_id
779 and b.effective_start_date = t.effective_start_date
780 );
781
782 update ben_ler_f_tl t set (
783 typ_cd,
784 name
785 ) = (select
786 b.typ_cd,
787 b.name
788 from ben_ler_f_tl b
789 where b.ler_id = t.ler_id
790 and b.effective_start_date = t.effective_start_date
791 and b.language = t.source_lang)
792 where (
793 t.ler_id,
794 t.effective_start_date,
795 t.language
796 ) in (select
797 subt.ler_id,
798 subt.effective_start_date,
799 subt.language
800 from ben_ler_f_tl subb, ben_ler_f_tl subt
801 where subb.ler_id = subt.ler_id
802 and subb.effective_start_date = subt.effective_start_date
803 and subb.language = subt.source_lang
804 and (subb.name <> subt.name
805 or subb.typ_cd <> subt.typ_cd
806 ));
807
808 insert into ben_ler_f_tl (
809 ler_id,
810 effective_start_date,
811 effective_end_date,
812 typ_cd,
813 -- lf_evt_oper_cd,
814 name,
815 language,
816 source_lang,
817 last_update_date,
818 last_updated_by,
819 last_update_login,
820 created_by,
821 creation_date
822 ) select
823 b.ler_id,
824 b.effective_start_date,
825 b.effective_end_date,
826 b.typ_cd,
827 -- b.lf_evt_oper_cd,
828 b.name,
829 l.language_code,
830 b.source_lang,
831 b.last_update_date,
832 b.last_updated_by,
833 b.last_update_login,
834 b.created_by,
835 b.creation_date
836 from ben_ler_f_tl b, fnd_languages l
837 where l.installed_flag in ('I', 'B')
838 and b.language = userenv('LANG')
839 and not exists
840 (select null
841 from ben_ler_f_tl t
842 where t.ler_id = b.ler_id
843 and t.effective_start_date = b.effective_start_date
844 and t.language = l.language_code);
845 end add_language;
846 --
847 end ben_ler_shd;