[Home] [Help]
PACKAGE BODY: APPS.BEN_CTY_SHD
Source
1 Package Body ben_cty_shd as
2 /* $Header: bectyrhi.pkb 120.2 2006/03/30 23:42:52 gsehgal noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cty_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE
15 ) Is
16 --
17 l_proc varchar2(72) := g_package||'constraint_error';
18 --
19 Begin
20 --
21 If (p_constraint_name = 'BEN_COMPTNCY_RT_F_PK') Then
22 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
23 fnd_message.set_token('PROCEDURE', l_proc);
24 fnd_message.set_token('STEP','5');
25 fnd_message.raise_error;
26 Else
27 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
28 fnd_message.set_token('PROCEDURE', l_proc);
29 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
30 fnd_message.raise_error;
31 End If;
32 --
33 End constraint_error;
34 --
35 -- ----------------------------------------------------------------------------
36 -- |-----------------------------< api_updating >-----------------------------|
37 -- ----------------------------------------------------------------------------
38 Function api_updating
39 (p_effective_date in date
40 ,p_comptncy_rt_id in number
41 ,p_object_version_number in number
42 ) Return Boolean Is
43 --
44 -- Cursor selects the 'current' row from the HR Schema
45 --
46 Cursor C_Sel1 is
47 select
48 comptncy_rt_id
49 ,effective_start_date
50 ,effective_end_date
51 ,competence_id
52 ,rating_level_id
53 ,excld_flag
54 ,business_group_id
55 ,vrbl_rt_prfl_id
56 ,object_version_number
57 ,ordr_num
58 ,cty_attribute_category
59 ,cty_attribute1
60 ,cty_attribute2
61 ,cty_attribute3
62 ,cty_attribute4
63 ,cty_attribute5
64 ,cty_attribute6
65 ,cty_attribute7
66 ,cty_attribute8
67 ,cty_attribute9
68 ,cty_attribute10
69 ,cty_attribute11
70 ,cty_attribute12
71 ,cty_attribute13
72 ,cty_attribute14
73 ,cty_attribute15
74 ,cty_attribute16
75 ,cty_attribute17
76 ,cty_attribute18
77 ,cty_attribute19
78 ,cty_attribute20
79 ,cty_attribute21
80 ,cty_attribute22
81 ,cty_attribute23
82 ,cty_attribute24
83 ,cty_attribute25
84 ,cty_attribute26
85 ,cty_attribute27
86 ,cty_attribute28
87 ,cty_attribute29
88 ,cty_attribute30
89 from ben_comptncy_rt_f
90 where comptncy_rt_id = p_comptncy_rt_id
91 and p_effective_date
92 between effective_start_date and effective_end_date;
93 --
94 l_fct_ret boolean;
95 --
96 Begin
97 --
98 If (p_effective_date is null or
99 p_comptncy_rt_id is null or
100 p_object_version_number is null) Then
101 --
102 -- One of the primary key arguments is null therefore we must
103 -- set the returning function value to false
104 --
105 l_fct_ret := false;
106 Else
107 If (p_comptncy_rt_id =
108 ben_cty_shd.g_old_rec.comptncy_rt_id and
109 p_object_version_number =
110 ben_cty_shd.g_old_rec.object_version_number
111 ) Then
112 --
113 -- The g_old_rec is current therefore we must
114 -- set the returning function to true
115 --
116 l_fct_ret := true;
117 Else
118 --
119 -- Select the current row
120 --
121 Open C_Sel1;
122 Fetch C_Sel1 Into ben_cty_shd.g_old_rec;
123 If C_Sel1%notfound Then
124 Close C_Sel1;
125 --
126 -- The primary key is invalid therefore we must error
127 --
128 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
129 fnd_message.raise_error;
130 End If;
131 Close C_Sel1;
132 If (p_object_version_number
133 <> ben_cty_shd.g_old_rec.object_version_number) Then
134 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
135 fnd_message.raise_error;
136 End If;
137 l_fct_ret := true;
138 End If;
139 End If;
140 Return (l_fct_ret);
141 --
142 End api_updating;
143 --
144 -- ----------------------------------------------------------------------------
145 -- |---------------------------< find_dt_upd_modes >--------------------------|
146 -- ----------------------------------------------------------------------------
147 Procedure find_dt_upd_modes
148 (p_effective_date in date
149 ,p_base_key_value in number
150 ,p_correction out nocopy boolean
151 ,p_update out nocopy boolean
152 ,p_update_override out nocopy boolean
153 ,p_update_change_insert out nocopy boolean
154 ) is
155 --
156 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
157 --
158 Begin
159 hr_utility.set_location('Entering:'||l_proc, 5);
160 --
161 -- Call the corresponding datetrack api
162 --
163 dt_api.find_dt_upd_modes
164 (p_effective_date => p_effective_date
165 ,p_base_table_name => 'ben_comptncy_rt_f'
166 ,p_base_key_column => 'comptncy_rt_id'
167 ,p_base_key_value => p_base_key_value
168 ,p_correction => p_correction
169 ,p_update => p_update
170 ,p_update_override => p_update_override
171 ,p_update_change_insert => p_update_change_insert
172 );
173 --
174 hr_utility.set_location(' Leaving:'||l_proc, 10);
175 End find_dt_upd_modes;
176 --
177 -- ----------------------------------------------------------------------------
178 -- |---------------------------< find_dt_del_modes >--------------------------|
179 -- ----------------------------------------------------------------------------
180 Procedure find_dt_del_modes
181 (p_effective_date in date
182 ,p_base_key_value in number
183 ,p_zap out nocopy boolean
184 ,p_delete out nocopy boolean
185 ,p_future_change out nocopy boolean
186 ,p_delete_next_change out nocopy boolean
187 ) is
188 --
189 l_proc varchar2(72) := g_package||'find_dt_del_modes';
190 --
191 l_parent_key_value1 number;
192 --
193 Cursor C_Sel1 Is
194 select t.vrbl_rt_prfl_id
195 from ben_comptncy_rt_f t
196 where t.comptncy_rt_id = p_base_key_value
197 and p_effective_date
198 between t.effective_start_date and t.effective_end_date;
199 --
200 Begin
201 hr_utility.set_location('Entering:'||l_proc, 5);
202 Open C_sel1;
203 Fetch C_Sel1 Into
204 l_parent_key_value1;
205 If C_Sel1%NOTFOUND then
206 Close C_Sel1;
207 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
208 fnd_message.set_token('PROCEDURE',l_proc);
209 fnd_message.set_token('STEP','10');
210 fnd_message.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_comptncy_rt_f'
219 ,p_base_key_column => 'comptncy_rt_id'
220 ,p_base_key_value => p_base_key_value
221 ,p_parent_table_name1 => 'ben_vrbl_rt_prfl_f'
222 ,p_parent_key_column1 => 'vrbl_rt_prfl_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 --
230 hr_utility.set_location(' Leaving:'||l_proc, 10);
231 End find_dt_del_modes;
232 --
233 -- ----------------------------------------------------------------------------
234 -- |-----------------------< upd_effective_end_date >-------------------------|
235 -- ----------------------------------------------------------------------------
236 Procedure upd_effective_end_date
237 (p_effective_date in date
238 ,p_base_key_value in number
239 ,p_new_effective_end_date in date
240 ,p_validation_start_date in date
241 ,p_validation_end_date in date
242 ,p_object_version_number out nocopy number
243 ) is
244 --
245 l_proc varchar2(72) := g_package||'upd_effective_end_date';
246 l_object_version_number number;
247 --
248 Begin
249 hr_utility.set_location('Entering:'||l_proc, 5);
250 --
251 -- Because we are updating a row we must get the next object
252 -- version number.
253 --
254 l_object_version_number :=
255 dt_api.get_object_version_number
256 (p_base_table_name => 'ben_comptncy_rt_f'
257 ,p_base_key_column => 'comptncy_rt_id'
258 ,p_base_key_value => p_base_key_value
259 );
260 --
261 hr_utility.set_location(l_proc, 10);
262 --
263 --
264 -- Update the specified datetrack row setting the effective
265 -- end date to the specified new effective end date.
266 --
267 update ben_comptncy_rt_f t
268 set t.effective_end_date = p_new_effective_end_date
269 , t.object_version_number = l_object_version_number
270 where t.comptncy_rt_id = p_base_key_value
271 and p_effective_date
272 between t.effective_start_date and t.effective_end_date;
273 --
274 --
275 p_object_version_number := l_object_version_number;
276 hr_utility.set_location(' Leaving:'||l_proc, 15);
277 --
278 End upd_effective_end_date;
279 --
280 -- ----------------------------------------------------------------------------
281 -- |---------------------------------< lck >----------------------------------|
282 -- ----------------------------------------------------------------------------
283 Procedure lck
284 (p_effective_date in date
285 ,p_datetrack_mode in varchar2
286 ,p_comptncy_rt_id in number
287 ,p_object_version_number in number
288 ,p_validation_start_date out nocopy date
289 ,p_validation_end_date out nocopy date
290 ) is
291 --
292 l_proc varchar2(72) := g_package||'lck';
293 l_validation_start_date date;
294 l_validation_end_date date;
295 l_argument varchar2(30);
296 --
297 -- Cursor C_Sel1 selects the current locked row as of session date
298 -- ensuring that the object version numbers match.
299 --
300 Cursor C_Sel1 is
301 select
302 comptncy_rt_id
303 ,effective_start_date
304 ,effective_end_date
305 ,competence_id
306 ,rating_level_id
307 ,excld_flag
308 ,business_group_id
309 ,vrbl_rt_prfl_id
310 ,object_version_number
311 ,ordr_num
312 ,cty_attribute_category
313 ,cty_attribute1
314 ,cty_attribute2
315 ,cty_attribute3
316 ,cty_attribute4
317 ,cty_attribute5
318 ,cty_attribute6
319 ,cty_attribute7
320 ,cty_attribute8
321 ,cty_attribute9
322 ,cty_attribute10
323 ,cty_attribute11
324 ,cty_attribute12
325 ,cty_attribute13
326 ,cty_attribute14
327 ,cty_attribute15
328 ,cty_attribute16
329 ,cty_attribute17
330 ,cty_attribute18
331 ,cty_attribute19
332 ,cty_attribute20
333 ,cty_attribute21
334 ,cty_attribute22
335 ,cty_attribute23
336 ,cty_attribute24
337 ,cty_attribute25
338 ,cty_attribute26
339 ,cty_attribute27
340 ,cty_attribute28
341 ,cty_attribute29
342 ,cty_attribute30
343 from ben_comptncy_rt_f
344 where comptncy_rt_id = p_comptncy_rt_id
345 and p_effective_date
346 between effective_start_date and effective_end_date
347 for update nowait;
348 --
349 --
350 --
351 Begin
352 hr_utility.set_location('Entering:'||l_proc, 5);
353 --
354 -- Ensure that all the mandatory arguments are not null
355 --
356 hr_api.mandatory_arg_error(p_api_name => l_proc
357 ,p_argument => 'effective_date'
358 ,p_argument_value => p_effective_date
359 );
360 --
361 hr_api.mandatory_arg_error(p_api_name => l_proc
362 ,p_argument => 'datetrack_mode'
363 ,p_argument_value => p_datetrack_mode
364 );
365 --
366 hr_api.mandatory_arg_error(p_api_name => l_proc
367 ,p_argument => 'comptncy_rt_id'
368 ,p_argument_value => p_comptncy_rt_id
369 );
370 --
371 hr_api.mandatory_arg_error(p_api_name => l_proc
372 ,p_argument => 'object_version_number'
373 ,p_argument_value => p_object_version_number
374 );
375 --
376 -- Check to ensure the datetrack mode is not INSERT.
377 --
378 If (p_datetrack_mode <> hr_api.g_insert) then
379 --
380 -- We must select and lock the current row.
381 --
382 Open C_Sel1;
383 Fetch C_Sel1 Into ben_cty_shd.g_old_rec;
384 If C_Sel1%notfound then
385 Close C_Sel1;
386 --
387 -- The primary key is invalid therefore we must error
388 --
389 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
390 fnd_message.raise_error;
391 End If;
392 Close C_Sel1;
393 If (p_object_version_number
394 <> ben_cty_shd.g_old_rec.object_version_number) Then
395 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
396 fnd_message.raise_error;
397 End If;
398 --
399 --
400 -- Validate the datetrack mode mode getting the validation start
401 -- and end dates for the specified datetrack operation.
402 --
403 dt_api.validate_dt_mode
404 (p_effective_date => p_effective_date
405 ,p_datetrack_mode => p_datetrack_mode
406 ,p_base_table_name => 'ben_comptncy_rt_f'
407 ,p_base_key_column => 'comptncy_rt_id'
408 ,p_base_key_value => p_comptncy_rt_id
409 ,p_parent_table_name1 => 'ben_vrbl_rt_prfl_f'
410 ,p_parent_key_column1 => 'vrbl_rt_prfl_id'
411 ,p_parent_key_value1 => ben_cty_shd.g_old_rec.vrbl_rt_prfl_id
412 ,p_enforce_foreign_locking => true
413 ,p_validation_start_date => l_validation_start_date
414 ,p_validation_end_date => l_validation_end_date
415 );
416 Else
417 --
418 -- We are doing a datetrack 'INSERT' which is illegal within this
419 -- procedure therefore we must error (note: to lck on insert the
420 -- private procedure ins_lck should be called).
421 --
422 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
423 fnd_message.set_token('PROCEDURE', l_proc);
424 fnd_message.set_token('STEP','20');
428 -- Set the validation start and end date OUT arguments
425 fnd_message.raise_error;
426 End If;
427 --
429 --
430 p_validation_start_date := l_validation_start_date;
431 p_validation_end_date := l_validation_end_date;
432 --
433 hr_utility.set_location(' Leaving:'||l_proc, 30);
434 --
435 -- We need to trap the ORA LOCK exception
436 --
437 Exception
438 When HR_Api.Object_Locked then
439 --
440 -- The object is locked therefore we need to supply a meaningful
441 -- error message.
442 --
443 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
444 fnd_message.set_token('TABLE_NAME', 'ben_comptncy_rt_f');
445 fnd_message.raise_error;
446 End lck;
447 --
448 -- ----------------------------------------------------------------------------
449 -- |-----------------------------< convert_args >-----------------------------|
450 -- ----------------------------------------------------------------------------
451 Function convert_args
452 (p_comptncy_rt_id in number
453 ,p_effective_start_date in date
454 ,p_effective_end_date in date
455 ,p_competence_id in number
456 ,p_rating_level_id in number
457 ,p_excld_flag in varchar2
458 ,p_business_group_id in number
459 ,p_vrbl_rt_prfl_id in number
460 ,p_object_version_number in number
461 ,p_ordr_num in number
462 ,p_cty_attribute_category in varchar2
463 ,p_cty_attribute1 in varchar2
464 ,p_cty_attribute2 in varchar2
465 ,p_cty_attribute3 in varchar2
466 ,p_cty_attribute4 in varchar2
467 ,p_cty_attribute5 in varchar2
468 ,p_cty_attribute6 in varchar2
469 ,p_cty_attribute7 in varchar2
470 ,p_cty_attribute8 in varchar2
471 ,p_cty_attribute9 in varchar2
472 ,p_cty_attribute10 in varchar2
473 ,p_cty_attribute11 in varchar2
474 ,p_cty_attribute12 in varchar2
475 ,p_cty_attribute13 in varchar2
476 ,p_cty_attribute14 in varchar2
477 ,p_cty_attribute15 in varchar2
478 ,p_cty_attribute16 in varchar2
479 ,p_cty_attribute17 in varchar2
480 ,p_cty_attribute18 in varchar2
481 ,p_cty_attribute19 in varchar2
482 ,p_cty_attribute20 in varchar2
483 ,p_cty_attribute21 in varchar2
484 ,p_cty_attribute22 in varchar2
485 ,p_cty_attribute23 in varchar2
486 ,p_cty_attribute24 in varchar2
487 ,p_cty_attribute25 in varchar2
488 ,p_cty_attribute26 in varchar2
489 ,p_cty_attribute27 in varchar2
490 ,p_cty_attribute28 in varchar2
491 ,p_cty_attribute29 in varchar2
492 ,p_cty_attribute30 in varchar2
493 )
494 Return g_rec_type is
495 --
496 l_rec g_rec_type;
497 --
498 Begin
499 --
500 -- Convert arguments into local l_rec structure.
501 --
502 l_rec.comptncy_rt_id := p_comptncy_rt_id;
503 l_rec.effective_start_date := p_effective_start_date;
504 l_rec.effective_end_date := p_effective_end_date;
505 l_rec.competence_id := p_competence_id;
506 l_rec.rating_level_id := p_rating_level_id ;
507 l_rec.excld_flag := p_excld_flag;
508 l_rec.business_group_id := p_business_group_id;
509 l_rec.vrbl_rt_prfl_id := p_vrbl_rt_prfl_id;
510 l_rec.object_version_number := p_object_version_number;
511 l_rec.ordr_num := p_ordr_num;
512 l_rec.cty_attribute_category := p_cty_attribute_category;
513 l_rec.cty_attribute1 := p_cty_attribute1;
514 l_rec.cty_attribute2 := p_cty_attribute2;
515 l_rec.cty_attribute3 := p_cty_attribute3;
516 l_rec.cty_attribute4 := p_cty_attribute4;
517 l_rec.cty_attribute5 := p_cty_attribute5;
518 l_rec.cty_attribute6 := p_cty_attribute6;
519 l_rec.cty_attribute7 := p_cty_attribute7;
520 l_rec.cty_attribute8 := p_cty_attribute8;
521 l_rec.cty_attribute9 := p_cty_attribute9;
522 l_rec.cty_attribute10 := p_cty_attribute10;
523 l_rec.cty_attribute11 := p_cty_attribute11;
524 l_rec.cty_attribute12 := p_cty_attribute12;
525 l_rec.cty_attribute13 := p_cty_attribute13;
526 l_rec.cty_attribute14 := p_cty_attribute14;
527 l_rec.cty_attribute15 := p_cty_attribute15;
528 l_rec.cty_attribute16 := p_cty_attribute16;
529 l_rec.cty_attribute17 := p_cty_attribute17;
530 l_rec.cty_attribute18 := p_cty_attribute18;
531 l_rec.cty_attribute19 := p_cty_attribute19;
532 l_rec.cty_attribute20 := p_cty_attribute20;
533 l_rec.cty_attribute21 := p_cty_attribute21;
534 l_rec.cty_attribute22 := p_cty_attribute22;
535 l_rec.cty_attribute23 := p_cty_attribute23;
536 l_rec.cty_attribute24 := p_cty_attribute24;
537 l_rec.cty_attribute25 := p_cty_attribute25;
538 l_rec.cty_attribute26 := p_cty_attribute26;
539 l_rec.cty_attribute27 := p_cty_attribute27;
540 l_rec.cty_attribute28 := p_cty_attribute28;
541 l_rec.cty_attribute29 := p_cty_attribute29;
542 l_rec.cty_attribute30 := p_cty_attribute30;
543 --
544 -- Return the plsql record structure.
545 --
546 Return(l_rec);
547 --
548 End convert_args;
549 --
550 end ben_cty_shd;