[Home] [Help]
PACKAGE BODY: APPS.BEN_CCT_SHD
Source
1 Package Body ben_cct_shd as
2 /* $Header: becctrhi.pkb 120.0.12020000.4 2012/07/03 11:57:28 amnaraya ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cct_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 >---------------------------|
27 -- ----------------------------------------------------------------------------
28 Procedure constraint_error
29 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
30 --
31 l_proc varchar2(72) := g_package||'constraint_error';
32 --
33 Begin
34 hr_utility.set_location('Entering:'||l_proc, 5);
35 hr_utility.set_location('Entering:'||l_proc, 6);
36 --
37 If (p_constraint_name = 'AVCON_BEN_C_WHNVR_000') Then
38 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
39 hr_utility.set_message_token('PROCEDURE', l_proc);
40 hr_utility.set_message_token('STEP','5');
41 hr_utility.raise_error;
42 ElsIf (p_constraint_name = 'BEN_CM_TYP_F_PK') Then
43 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
44 hr_utility.set_message_token('PROCEDURE', l_proc);
45 hr_utility.set_message_token('STEP','10');
46 hr_utility.raise_error;
47 Else
48 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
49 hr_utility.set_message_token('PROCEDURE', l_proc);
50 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
51 hr_utility.raise_error;
52 End If;
53 --
54 hr_utility.set_location(' Leaving:'||l_proc, 10);
55 End constraint_error;
56 --
57 -- ----------------------------------------------------------------------------
58 -- |-----------------------------< api_updating >-----------------------------|
59 -- ----------------------------------------------------------------------------
60 Function api_updating
61 (p_effective_date in date,
62 p_cm_typ_id in number,
63 p_object_version_number in number
64 ) Return Boolean Is
65 --
66 --
67 -- Cursor selects the 'current' row from the HR Schema
68 --
69 Cursor C_Sel1 is
70 select
71 cm_typ_id,
72 effective_start_date,
73 effective_end_date,
74 name,
75 desc_txt,
76 inactive_flag,
77 cm_typ_rl,
78 cm_usg_cd,
79 whnvr_trgrd_flag,
80 shrt_name,
81 pc_kit_cd,
82 trk_mlg_flag,
83 mx_num_avlbl_val,
84 to_be_sent_dt_cd,
85 to_be_sent_dt_rl,
86 inspn_rqd_flag,
87 inspn_rqd_rl,
88 rcpent_cd,
89 parnt_cm_typ_id,
90 business_group_id,
91 cct_attribute_category,
92 cct_attribute1,
93 cct_attribute10,
94 cct_attribute11,
95 cct_attribute12,
96 cct_attribute13,
97 cct_attribute14,
98 cct_attribute15,
99 cct_attribute16,
100 cct_attribute17,
101 cct_attribute18,
102 cct_attribute19,
103 cct_attribute2,
104 cct_attribute20,
105 cct_attribute21,
106 cct_attribute22,
107 cct_attribute23,
108 cct_attribute24,
109 cct_attribute25,
110 cct_attribute26,
111 cct_attribute27,
112 cct_attribute28,
113 cct_attribute29,
114 cct_attribute3,
115 cct_attribute30,
116 cct_attribute4,
117 cct_attribute5,
118 cct_attribute6,
119 cct_attribute7,
120 cct_attribute8,
121 cct_attribute9,
122 object_version_number
123 from ben_cm_typ_f
124 where cm_typ_id = p_cm_typ_id
125 and p_effective_date
129 l_fct_ret boolean;
126 between effective_start_date and effective_end_date;
127 --
128 l_proc varchar2(72) := g_package||'api_updating';
130 --
131 Begin
132 hr_utility.set_location('Entering:'||l_proc, 5);
133 --
134 If (p_effective_date is null or
135 p_cm_typ_id is null or
136 p_object_version_number is null) Then
137 --
138 -- One of the primary key arguments is null therefore we must
139 -- set the returning function value to false
140 --
141 l_fct_ret := false;
142 Else
143 If (p_cm_typ_id = g_old_rec.cm_typ_id and
144 p_object_version_number = g_old_rec.object_version_number) Then
145 hr_utility.set_location(l_proc, 10);
146 --
147 -- The g_old_rec is current therefore we must
148 -- set the returning function to true
149 --
150 l_fct_ret := true;
151 Else
152 --
153 -- Select the current row
154 --
155 Open C_Sel1;
156 Fetch C_Sel1 Into g_old_rec;
157 If C_Sel1%notfound Then
158 Close C_Sel1;
159 --
160 -- The primary key is invalid therefore we must error
161 --
162 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
163 hr_utility.raise_error;
164 End If;
165 Close C_Sel1;
166 If (p_object_version_number <> g_old_rec.object_version_number) Then
167 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
168 hr_utility.raise_error;
169 End If;
170 hr_utility.set_location(l_proc, 15);
171 l_fct_ret := true;
172 End If;
173 End If;
174 hr_utility.set_location(' Leaving:'||l_proc, 20);
175 Return (l_fct_ret);
176 --
177 End api_updating;
178 --
179 -- ----------------------------------------------------------------------------
180 -- |--------------------------< find_dt_del_modes >---------------------------|
181 -- ----------------------------------------------------------------------------
182 Procedure find_dt_del_modes
183 (p_effective_date in date,
184 p_base_key_value in number,
185 p_zap out nocopy boolean,
186 p_delete out nocopy boolean,
187 p_future_change out nocopy boolean,
188 p_delete_next_change out nocopy boolean) is
189 --
190 l_proc varchar2(72) := g_package||'find_dt_del_modes';
191 --
192 l_parent_key_value1 number;
193 --
194 Cursor C_Sel1 Is
195 select t.parnt_cm_typ_id
196 from ben_cm_typ_f t
197 where t.cm_typ_id = p_base_key_value
198 and p_effective_date
199 between t.effective_start_date and t.effective_end_date;
200 --
201 Begin
202 hr_utility.set_location('Entering:'||l_proc, 5);
203 Open C_Sel1;
204 Fetch C_Sel1 Into l_parent_key_value1;
205 If C_Sel1%notfound then
206 Close C_Sel1;
207 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
208 hr_utility.set_message_token('PROCEDURE', l_proc);
209 hr_utility.set_message_token('STEP','10');
210 hr_utility.raise_error;
211 End If;
212 Close C_Sel1;
213 --
214 -- Call the corresponding datetrack api
215 --
216 dt_api.find_dt_del_modes
217 (p_effective_date => p_effective_date,
218 p_base_table_name => 'ben_cm_typ_f',
219 p_base_key_column => 'cm_typ_id',
220 p_base_key_value => p_base_key_value,
221 p_parent_table_name1 => 'ben_cm_typ_f',
222 p_parent_key_column1 => 'cm_typ_id',
223 p_parent_key_value1 => l_parent_key_value1,
224 p_zap => p_zap,
225 p_delete => p_delete,
226 p_future_change => p_future_change,
227 p_delete_next_change => p_delete_next_change);
228 --
229 hr_utility.set_location(' Leaving:'||l_proc, 10);
230 End find_dt_del_modes;
231 --
232 -- ----------------------------------------------------------------------------
233 -- |--------------------------< find_dt_upd_modes >---------------------------|
234 -- ----------------------------------------------------------------------------
235 Procedure find_dt_upd_modes
236 (p_effective_date in date,
237 p_base_key_value in number,
238 p_correction out nocopy boolean,
239 p_update out nocopy boolean,
240 p_update_override out nocopy boolean,
241 p_update_change_insert out nocopy boolean) is
242 --
243 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
244 --
245 Begin
246 hr_utility.set_location('Entering:'||l_proc, 5);
247 --
248 -- Call the corresponding datetrack api
249 --
250 dt_api.find_dt_upd_modes
251 (p_effective_date => p_effective_date,
252 p_base_table_name => 'ben_cm_typ_f',
253 p_base_key_column => 'cm_typ_id',
254 p_base_key_value => p_base_key_value,
255 p_correction => p_correction,
256 p_update => p_update,
257 p_update_override => p_update_override,
258 p_update_change_insert => p_update_change_insert);
259 --
260 hr_utility.set_location(' Leaving:'||l_proc, 10);
261 End find_dt_upd_modes;
262 --
263 -- ----------------------------------------------------------------------------
264 -- |------------------------< upd_effective_end_date >------------------------|
265 -- ----------------------------------------------------------------------------
266 Procedure upd_effective_end_date
267 (p_effective_date in date,
268 p_base_key_value in number,
269 p_new_effective_end_date in date,
270 p_validation_start_date in date,
271 p_validation_end_date in date,
272 p_object_version_number out nocopy number) is
273 --
274 l_proc varchar2(72) := g_package||'upd_effective_end_date';
275 l_object_version_number number;
276 --
277 Begin
278 hr_utility.set_location('Entering:'||l_proc, 5);
279 --
280 -- Because we are updating a row we must get the next object
281 -- version number.
282 --
283 l_object_version_number :=
284 dt_api.get_object_version_number
285 (p_base_table_name => 'ben_cm_typ_f',
286 p_base_key_column => 'cm_typ_id',
287 p_base_key_value => p_base_key_value);
288 --
289 hr_utility.set_location(l_proc, 10);
290 g_api_dml := true; -- Set the api dml status
291 --
292 -- Update the specified datetrack row setting the effective
293 -- end date to the specified new effective end date.
294 --
295 update ben_cm_typ_f t
296 set t.effective_end_date = p_new_effective_end_date,
297 t.object_version_number = l_object_version_number
298 where t.cm_typ_id = p_base_key_value
299 and p_effective_date
300 between t.effective_start_date and t.effective_end_date;
301 --
302 update ben_cm_typ_f_tl t
303 set t.effective_end_date = p_new_effective_end_date,
304 t.last_update_date = sysdate,
305 t.last_updated_by = fnd_global.user_id,
306 t.last_update_login = fnd_global.login_id
307 where t.cm_typ_id = p_base_key_value
308 and p_effective_date
309 between t.effective_start_date and t.effective_end_date;
310 --
311 g_api_dml := false; -- Unset the api dml status
312 p_object_version_number := l_object_version_number;
313 hr_utility.set_location(' Leaving:'||l_proc, 15);
314 --
315 Exception
316 When Others Then
317 g_api_dml := false; -- Unset the api dml status
318 Raise;
319 End upd_effective_end_date;
320 --
321 -- ----------------------------------------------------------------------------
322 -- |---------------------------------< lck >----------------------------------|
323 -- ----------------------------------------------------------------------------
324 Procedure lck
325 (p_effective_date in date,
326 p_datetrack_mode in varchar2,
327 p_cm_typ_id in number,
328 p_object_version_number in number,
329 p_validation_start_date out nocopy date,
330 p_validation_end_date out nocopy date) is
331 --
332 l_proc varchar2(72) := g_package||'lck';
333 l_validation_start_date date;
334 l_validation_end_date date;
335 l_object_invalid exception;
336 l_argument varchar2(30);
337 --
338 -- Cursor C_Sel1 selects the current locked row as of session date
339 -- ensuring that the object version numbers match.
340 --
341 Cursor C_Sel1 is
342 select
343 cm_typ_id,
344 effective_start_date,
345 effective_end_date,
346 name,
347 desc_txt,
348 inactive_flag,
349 cm_typ_rl,
350 cm_usg_cd,
351 whnvr_trgrd_flag,
352 shrt_name,
353 pc_kit_cd,
354 trk_mlg_flag,
355 mx_num_avlbl_val,
356 to_be_sent_dt_cd,
357 to_be_sent_dt_rl,
358 inspn_rqd_flag,
359 inspn_rqd_rl,
360 rcpent_cd,
361 parnt_cm_typ_id,
362 business_group_id,
363 cct_attribute_category,
364 cct_attribute1,
365 cct_attribute10,
366 cct_attribute11,
367 cct_attribute12,
368 cct_attribute13,
369 cct_attribute14,
370 cct_attribute15,
371 cct_attribute16,
372 cct_attribute17,
373 cct_attribute18,
374 cct_attribute19,
375 cct_attribute2,
376 cct_attribute20,
377 cct_attribute21,
378 cct_attribute22,
379 cct_attribute23,
380 cct_attribute24,
381 cct_attribute25,
382 cct_attribute26,
383 cct_attribute27,
384 cct_attribute28,
385 cct_attribute29,
386 cct_attribute3,
387 cct_attribute30,
388 cct_attribute4,
389 cct_attribute5,
390 cct_attribute6,
391 cct_attribute7,
392 cct_attribute8,
393 cct_attribute9,
394 object_version_number
395 from ben_cm_typ_f
396 where cm_typ_id = p_cm_typ_id
397 and p_effective_date
398 between effective_start_date and effective_end_date
399 for update nowait;
400 --
401 --
402 --
403 Begin
404 hr_utility.set_location('Entering:'||l_proc, 5);
405 --
406 -- Ensure that all the mandatory arguments are not null
407 --
408 hr_api.mandatory_arg_error(p_api_name => l_proc,
409 p_argument => 'effective_date',
410 p_argument_value => p_effective_date);
411 --
412 hr_api.mandatory_arg_error(p_api_name => l_proc,
413 p_argument => 'datetrack_mode',
414 p_argument_value => p_datetrack_mode);
415 --
416 hr_api.mandatory_arg_error(p_api_name => l_proc,
417 p_argument => 'cm_typ_id',
418 p_argument_value => p_cm_typ_id);
419 --
420 hr_api.mandatory_arg_error(p_api_name => l_proc,
421 p_argument => 'object_version_number',
422 p_argument_value => p_object_version_number);
423 --
424 -- Check to ensure the datetrack mode is not INSERT.
425 --
426 If (p_datetrack_mode <> 'INSERT') then
427 --
428 -- We must select and lock the current row.
429 --
430 Open C_Sel1;
431 Fetch C_Sel1 Into g_old_rec;
432 If C_Sel1%notfound then
433 Close C_Sel1;
434 --
435 -- The primary key is invalid therefore we must error
436 --
437 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
438 hr_utility.raise_error;
439 End If;
440 Close C_Sel1;
441 If (p_object_version_number <> g_old_rec.object_version_number) Then
442 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
443 hr_utility.raise_error;
444 End If;
445 hr_utility.set_location(l_proc, 15);
446 --
447 --
448 -- Validate the datetrack mode mode getting the validation start
449 -- and end dates for the specified datetrack operation.
450 --
451 dt_api.validate_dt_mode
452 (p_effective_date => p_effective_date,
453 p_datetrack_mode => p_datetrack_mode,
454 p_base_table_name => 'ben_cm_typ_f',
455 p_base_key_column => 'cm_typ_id',
456 p_base_key_value => p_cm_typ_id,
457 p_parent_table_name1 => 'ben_cm_typ_f',
458 p_parent_key_column1 => 'cm_typ_id',
459 p_parent_key_value1 => g_old_rec.parnt_cm_typ_id,
460 -- p_child_table_name1 => 'ben_cm_typ_f',
461 -- p_child_key_column1 => 'parnt_cm_typ_id',
462 -- p_child_table_name2 => 'ben_cm_typ_trgr_f',
463 -- p_child_key_column2 => 'cm_typ_trgr_id',
464 -- p_child_table_name3 => 'ben_cm_typ_usg_f',
465 -- p_child_key_column3 => 'cm_typ_usg_id',
466 -- p_child_table_name4 => 'ben_per_cm_f',
467 -- p_child_key_column4 => 'per_cm_id',
468 p_enforce_foreign_locking => true,
469 p_validation_start_date => l_validation_start_date,
470 p_validation_end_date => l_validation_end_date);
471 Else
472 --
473 -- We are doing a datetrack 'INSERT' which is illegal within this
474 -- procedure therefore we must error (note: to lck on insert the
475 -- private procedure ins_lck should be called).
476 --
477 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
478 hr_utility.set_message_token('PROCEDURE', l_proc);
479 hr_utility.set_message_token('STEP','20');
480 hr_utility.raise_error;
481 End If;
482 --
483 -- Set the validation start and end date OUT arguments
484 --
485 p_validation_start_date := l_validation_start_date;
486 p_validation_end_date := l_validation_end_date;
487 --
488 hr_utility.set_location(' Leaving:'||l_proc, 30);
489 --
490 -- We need to trap the ORA LOCK exception
491 --
492 Exception
493 When HR_Api.Object_Locked then
494 --
495 -- The object is locked therefore we need to supply a meaningful
496 -- error message.
497 --
498 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
499 hr_utility.set_message_token('TABLE_NAME', 'ben_cm_typ_f');
500 hr_utility.raise_error;
501 When l_object_invalid then
502 --
503 -- The object doesn't exist or is invalid
504 --
505 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
506 hr_utility.set_message_token('TABLE_NAME', 'ben_cm_typ_f');
507 hr_utility.raise_error;
508 End lck;
509 --
510 -- ----------------------------------------------------------------------------
511 -- |-----------------------------< convert_args >-----------------------------|
512 -- ----------------------------------------------------------------------------
513 Function convert_args
514 (
515 p_cm_typ_id in number,
516 p_effective_start_date in date,
517 p_effective_end_date in date,
518 p_name in varchar2,
519 p_desc_txt in varchar2,
520 p_inactive_flag in varchar2,
521 p_cm_typ_rl in number,
522 p_cm_usg_cd in varchar2,
523 p_whnvr_trgrd_flag in varchar2,
524 p_shrt_name in varchar2,
525 p_pc_kit_cd in varchar2,
526 p_trk_mlg_flag in varchar2,
527 p_mx_num_avlbl_val in number,
528 p_to_be_sent_dt_cd in varchar2,
529 p_to_be_sent_dt_rl in number,
530 p_inspn_rqd_flag in varchar2,
531 p_inspn_rqd_rl in number,
532 p_rcpent_cd in varchar2,
533 p_parnt_cm_typ_id in number,
534 p_business_group_id in number,
535 p_cct_attribute_category in varchar2,
536 p_cct_attribute1 in varchar2,
537 p_cct_attribute10 in varchar2,
538 p_cct_attribute11 in varchar2,
539 p_cct_attribute12 in varchar2,
540 p_cct_attribute13 in varchar2,
541 p_cct_attribute14 in varchar2,
542 p_cct_attribute15 in varchar2,
543 p_cct_attribute16 in varchar2,
544 p_cct_attribute17 in varchar2,
545 p_cct_attribute18 in varchar2,
546 p_cct_attribute19 in varchar2,
547 p_cct_attribute2 in varchar2,
548 p_cct_attribute20 in varchar2,
549 p_cct_attribute21 in varchar2,
550 p_cct_attribute22 in varchar2,
551 p_cct_attribute23 in varchar2,
552 p_cct_attribute24 in varchar2,
553 p_cct_attribute25 in varchar2,
554 p_cct_attribute26 in varchar2,
555 p_cct_attribute27 in varchar2,
556 p_cct_attribute28 in varchar2,
557 p_cct_attribute29 in varchar2,
558 p_cct_attribute3 in varchar2,
559 p_cct_attribute30 in varchar2,
560 p_cct_attribute4 in varchar2,
561 p_cct_attribute5 in varchar2,
562 p_cct_attribute6 in varchar2,
563 p_cct_attribute7 in varchar2,
564 p_cct_attribute8 in varchar2,
565 p_cct_attribute9 in varchar2,
566 p_object_version_number in number
567 )
568 Return g_rec_type is
569 --
570 l_rec g_rec_type;
571 l_proc varchar2(72) := g_package||'convert_args';
572 --
573 Begin
574 --
575 hr_utility.set_location('Entering:'||l_proc, 5);
576 --
577 -- Convert arguments into local l_rec structure.
578 --
579 l_rec.cm_typ_id := p_cm_typ_id;
580 l_rec.effective_start_date := p_effective_start_date;
581 l_rec.effective_end_date := p_effective_end_date;
582 l_rec.name := p_name;
583 l_rec.desc_txt := p_desc_txt;
584 l_rec.inactive_flag := p_inactive_flag;
585 l_rec.cm_typ_rl := p_cm_typ_rl;
586 l_rec.cm_usg_cd := p_cm_usg_cd;
587 l_rec.whnvr_trgrd_flag := p_whnvr_trgrd_flag;
588 l_rec.shrt_name := p_shrt_name;
589 l_rec.pc_kit_cd := p_pc_kit_cd;
590 l_rec.trk_mlg_flag := p_trk_mlg_flag;
591 l_rec.mx_num_avlbl_val := p_mx_num_avlbl_val;
592 l_rec.to_be_sent_dt_cd := p_to_be_sent_dt_cd;
593 l_rec.to_be_sent_dt_rl := p_to_be_sent_dt_rl;
594 l_rec.inspn_rqd_flag := p_inspn_rqd_flag;
595 l_rec.inspn_rqd_rl := p_inspn_rqd_rl;
596 l_rec.rcpent_cd := p_rcpent_cd;
597 l_rec.parnt_cm_typ_id := p_parnt_cm_typ_id;
598 l_rec.business_group_id := p_business_group_id;
599 l_rec.cct_attribute_category := p_cct_attribute_category;
600 l_rec.cct_attribute1 := p_cct_attribute1;
601 l_rec.cct_attribute10 := p_cct_attribute10;
602 l_rec.cct_attribute11 := p_cct_attribute11;
603 l_rec.cct_attribute12 := p_cct_attribute12;
604 l_rec.cct_attribute13 := p_cct_attribute13;
605 l_rec.cct_attribute14 := p_cct_attribute14;
606 l_rec.cct_attribute15 := p_cct_attribute15;
607 l_rec.cct_attribute16 := p_cct_attribute16;
608 l_rec.cct_attribute17 := p_cct_attribute17;
609 l_rec.cct_attribute18 := p_cct_attribute18;
610 l_rec.cct_attribute19 := p_cct_attribute19;
611 l_rec.cct_attribute2 := p_cct_attribute2;
612 l_rec.cct_attribute20 := p_cct_attribute20;
613 l_rec.cct_attribute21 := p_cct_attribute21;
614 l_rec.cct_attribute22 := p_cct_attribute22;
615 l_rec.cct_attribute23 := p_cct_attribute23;
616 l_rec.cct_attribute24 := p_cct_attribute24;
617 l_rec.cct_attribute25 := p_cct_attribute25;
618 l_rec.cct_attribute26 := p_cct_attribute26;
619 l_rec.cct_attribute27 := p_cct_attribute27;
620 l_rec.cct_attribute28 := p_cct_attribute28;
621 l_rec.cct_attribute29 := p_cct_attribute29;
622 l_rec.cct_attribute3 := p_cct_attribute3;
623 l_rec.cct_attribute30 := p_cct_attribute30;
624 l_rec.cct_attribute4 := p_cct_attribute4;
625 l_rec.cct_attribute5 := p_cct_attribute5;
626 l_rec.cct_attribute6 := p_cct_attribute6;
627 l_rec.cct_attribute7 := p_cct_attribute7;
628 l_rec.cct_attribute8 := p_cct_attribute8;
629 l_rec.cct_attribute9 := p_cct_attribute9;
630 l_rec.object_version_number := p_object_version_number;
631 --
632 -- Return the plsql record structure.
633 --
634 hr_utility.set_location(' Leaving:'||l_proc, 10);
635 Return(l_rec);
636 --
637 End convert_args;
638 --
639 --
640 -- ----------------------------------------------------------------------------
641 -- |--------------------------<add_language>----------------------------------|
642 -- ----------------------------------------------------------------------------
643 Procedure add_language
644 is
645 begin
646 delete from ben_cm_typ_f_tl t
647 where not exists
648 (select null
649 from ben_cm_typ_f_tl b
650 where b.cm_typ_id = t.cm_typ_id
651 and b.effective_start_date = t.effective_start_date
652 );
653
654 update ben_cm_typ_f_tl t set (
655 shrt_name,
656 name
657 ) = (select
658 b.shrt_name,
659 b.name
660 from ben_cm_typ_f_tl b
661 where b.cm_typ_id = t.cm_typ_id
662 and b.effective_start_date = t.effective_start_date
663 and b.language = t.source_lang)
664 where (
665 t.cm_typ_id,
666 t.effective_start_date,
667 t.language
668 ) in (select
669 subt.cm_typ_id,
670 subt.effective_start_date,
671 subt.language
672 from ben_cm_typ_f_tl subb, ben_cm_typ_f_tl subt
673 where subb.cm_typ_id = subt.cm_typ_id
674 and subb.effective_start_date = subt.effective_start_date
675 and subb.language = subt.source_lang
676 and (subb.name <> subt.name
677 or subb.shrt_name <> subt.shrt_name
678 ));
679 --
680 insert into ben_cm_typ_f_tl (
681 cm_typ_id,
682 effective_start_date,
683 effective_end_date,
684 shrt_name,
685 name,
686 language,
687 source_lang,
688 last_update_date,
689 last_updated_by,
690 last_update_login,
691 created_by,
692 creation_date
693 ) select
694 b.cm_typ_id,
695 b.effective_start_date,
696 b.effective_end_date,
697 b.shrt_name,
698 b.name,
699 l.language_code,
700 b.source_lang,
701 b.last_update_date,
702 b.last_updated_by,
703 b.last_update_login,
704 b.created_by,
705 b.creation_date
706 from ben_cm_typ_f_tl b, fnd_languages l
707 where l.installed_flag in ('I', 'B')
708 and b.language = userenv('LANG')
709 and not exists
710 (select null
711 from ben_cm_typ_f_tl t
712 where t.cm_typ_id = b.cm_typ_id
713 and t.effective_start_date = b.effective_start_date
714 and t.language = l.language_code);
715 end add_language;
716 --
717 end ben_cct_shd;