[Home] [Help]
PACKAGE BODY: APPS.HR_CLE_SHD
Source
1 Package Body hr_cle_shd as
2 /* $Header: hrclerhi.pkb 115.6 2002/12/03 09:27:16 hjonnala noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_cle_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 = 'HR_DE_SOC_INS_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_soc_ins_contr_lvls_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 soc_ins_contr_lvls_id
49 ,organization_id
50 ,normal_percentage
51 ,normal_amount
52 ,increased_percentage
53 ,increased_amount
54 ,reduced_percentage
55 ,reduced_amount
56 ,effective_start_date
57 ,effective_end_date
58 ,attribute_category
59 ,attribute1
60 ,attribute2
61 ,attribute3
62 ,attribute4
63 ,attribute5
64 ,attribute6
65 ,attribute7
66 ,attribute8
67 ,attribute9
68 ,attribute10
69 ,attribute11
70 ,attribute12
71 ,attribute13
72 ,attribute14
73 ,attribute15
74 ,attribute16
75 ,attribute17
76 ,attribute18
77 ,attribute19
78 ,attribute20
79 ,object_version_number
80 ,attribute21
81 ,attribute22
82 ,attribute23
83 ,attribute24
84 ,attribute25
85 ,attribute26
86 ,attribute27
87 ,attribute28
88 ,attribute29
89 ,attribute30
90 ,flat_tax_limit_per_month
91 ,flat_tax_limit_per_year
92 ,min_increased_contribution
93 ,max_increased_contribution
94 ,month1
95 ,month1_min_contribution
96 ,month1_max_contribution
97 ,month2
98 ,month2_min_contribution
99 ,month2_max_contribution
100 ,employee_contribution
101 ,contribution_level_type
102 from hr_de_soc_ins_contr_lvls_f
103 where soc_ins_contr_lvls_id = p_soc_ins_contr_lvls_id
104 and p_effective_date
105 between effective_start_date and effective_end_date;
106 --
107 l_fct_ret boolean;
108 --
109 Begin
110 --
111 If (p_effective_date is null or
112 p_soc_ins_contr_lvls_id is null or
113 p_object_version_number is null) Then
114 --
115 -- One of the primary key arguments is null therefore we must
116 -- set the returning function value to false
117 --
118 l_fct_ret := false;
119 Else
120 If (p_soc_ins_contr_lvls_id =
121 hr_cle_shd.g_old_rec.soc_ins_contr_lvls_id and
122 p_object_version_number =
123 hr_cle_shd.g_old_rec.object_version_number
124 ) Then
125 --
126 -- The g_old_rec is current therefore we must
127 -- set the returning function to true
128 --
129 l_fct_ret := true;
130 Else
131 --
132 -- Select the current row
133 --
134 Open C_Sel1;
135 Fetch C_Sel1 Into hr_cle_shd.g_old_rec;
136 If C_Sel1%notfound Then
137 Close C_Sel1;
138 --
139 -- The primary key is invalid therefore we must error
140 --
141 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
142 fnd_message.raise_error;
143 End If;
144 Close C_Sel1;
145 If (p_object_version_number
146 <> hr_cle_shd.g_old_rec.object_version_number) Then
147 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
148 fnd_message.raise_error;
149 End If;
150 l_fct_ret := true;
151 End If;
152 End If;
153 Return (l_fct_ret);
154 --
155 End api_updating;
156 --
157 -- ----------------------------------------------------------------------------
158 -- |---------------------------< find_dt_upd_modes >--------------------------|
159 -- ----------------------------------------------------------------------------
160 Procedure find_dt_upd_modes
161 (p_effective_date in date
162 ,p_base_key_value in number
163 ,p_correction out nocopy boolean
164 ,p_update out nocopy boolean
165 ,p_update_override out nocopy boolean
166 ,p_update_change_insert out nocopy boolean
167 ) is
168 --
169 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
170 --
171 Begin
172 hr_utility.set_location('Entering:'||l_proc, 5);
173 --
174 -- Call the corresponding datetrack api
175 --
176 dt_api.find_dt_upd_modes
177 (p_effective_date => p_effective_date
178 ,p_base_table_name => 'hr_de_soc_ins_contr_lvls_f'
179 ,p_base_key_column => 'soc_ins_contr_lvls_id'
180 ,p_base_key_value => p_base_key_value
181 ,p_correction => p_correction
182 ,p_update => p_update
183 ,p_update_override => p_update_override
184 ,p_update_change_insert => p_update_change_insert
185 );
186 --
187 hr_utility.set_location(' Leaving:'||l_proc, 10);
188 End find_dt_upd_modes;
189 --
190 -- ----------------------------------------------------------------------------
191 -- |---------------------------< find_dt_del_modes >--------------------------|
192 -- ----------------------------------------------------------------------------
193 Procedure find_dt_del_modes
194 (p_effective_date in date
195 ,p_base_key_value in number
196 ,p_zap out nocopy boolean
197 ,p_delete out nocopy boolean
198 ,p_future_change out nocopy boolean
199 ,p_delete_next_change out nocopy boolean
200 ) is
201 --
202 l_proc varchar2(72) := g_package||'find_dt_del_modes';
203 --
204 --
205 Begin
206 hr_utility.set_location('Entering:'||l_proc, 5);
207 --
208 -- Call the corresponding datetrack api
209 --
210 dt_api.find_dt_del_modes
211 (p_effective_date => p_effective_date
212 ,p_base_table_name => 'hr_de_soc_ins_contr_lvls_f'
213 ,p_base_key_column => 'soc_ins_contr_lvls_id'
214 ,p_base_key_value => p_base_key_value
215 ,p_zap => p_zap
216 ,p_delete => p_delete
217 ,p_future_change => p_future_change
218 ,p_delete_next_change => p_delete_next_change
219 );
220 --
221 hr_utility.set_location(' Leaving:'||l_proc, 10);
222 End find_dt_del_modes;
223 --
224 -- ----------------------------------------------------------------------------
225 -- |-----------------------< upd_effective_end_date >-------------------------|
226 -- ----------------------------------------------------------------------------
227 Procedure upd_effective_end_date
228 (p_effective_date in date
229 ,p_base_key_value in number
230 ,p_new_effective_end_date in date
231 ,p_validation_start_date in date
232 ,p_validation_end_date in date
233 ,p_object_version_number out nocopy number
234 ) is
235 --
236 l_proc varchar2(72) := g_package||'upd_effective_end_date';
237 l_object_version_number number;
238 --
239 Begin
240 hr_utility.set_location('Entering:'||l_proc, 5);
241 --
242 -- Because we are updating a row we must get the next object
243 -- version number.
244 --
245 l_object_version_number :=
246 dt_api.get_object_version_number
247 (p_base_table_name => 'hr_de_soc_ins_contr_lvls_f'
248 ,p_base_key_column => 'soc_ins_contr_lvls_id'
249 ,p_base_key_value => p_base_key_value
250 );
251 --
252 hr_utility.set_location(l_proc, 10);
253 --
254 --
255 -- Update the specified datetrack row setting the effective
256 -- end date to the specified new effective end date.
257 --
258 update hr_de_soc_ins_contr_lvls_f t
259 set t.effective_end_date = p_new_effective_end_date
260 , t.object_version_number = l_object_version_number
261 where t.soc_ins_contr_lvls_id = p_base_key_value
262 and p_effective_date
263 between t.effective_start_date and t.effective_end_date;
264 --
265 --
266 p_object_version_number := l_object_version_number;
267 hr_utility.set_location(' Leaving:'||l_proc, 15);
268 --
269 End upd_effective_end_date;
270 --
271 -- ----------------------------------------------------------------------------
272 -- |---------------------------------< lck >----------------------------------|
273 -- ----------------------------------------------------------------------------
274 Procedure lck
275 (p_effective_date in date
276 ,p_datetrack_mode in varchar2
277 ,p_soc_ins_contr_lvls_id in number
278 ,p_object_version_number in number
279 ,p_validation_start_date out nocopy date
280 ,p_validation_end_date out nocopy date
281 ) is
282 --
283 l_proc varchar2(72) := g_package||'lck';
284 l_validation_start_date date;
285 l_validation_end_date date;
286 l_argument varchar2(30);
287 --
288 -- Cursor C_Sel1 selects the current locked row as of session date
289 -- ensuring that the object version numbers match.
290 --
291 Cursor C_Sel1 is
292 select
293 soc_ins_contr_lvls_id
294 ,organization_id
295 ,normal_percentage
296 ,normal_amount
297 ,increased_percentage
298 ,increased_amount
299 ,reduced_percentage
300 ,reduced_amount
301 ,effective_start_date
302 ,effective_end_date
303 ,attribute_category
304 ,attribute1
305 ,attribute2
306 ,attribute3
307 ,attribute4
308 ,attribute5
309 ,attribute6
310 ,attribute7
311 ,attribute8
312 ,attribute9
313 ,attribute10
314 ,attribute11
315 ,attribute12
316 ,attribute13
317 ,attribute14
318 ,attribute15
319 ,attribute16
320 ,attribute17
321 ,attribute18
322 ,attribute19
323 ,attribute20
324 ,object_version_number
325 ,attribute21
326 ,attribute22
327 ,attribute23
328 ,attribute24
329 ,attribute25
330 ,attribute26
331 ,attribute27
332 ,attribute28
333 ,attribute29
334 ,attribute30
335 ,flat_tax_limit_per_month
336 ,flat_tax_limit_per_year
337 ,min_increased_contribution
338 ,max_increased_contribution
339 ,month1
340 ,month1_min_contribution
341 ,month1_max_contribution
342 ,month2
343 ,month2_min_contribution
344 ,month2_max_contribution
345 ,employee_contribution
346 ,contribution_level_type
347 from hr_de_soc_ins_contr_lvls_f
348 where soc_ins_contr_lvls_id = p_soc_ins_contr_lvls_id
349 and p_effective_date
350 between effective_start_date and effective_end_date
351 for update nowait;
352 --
353 --
354 --
355 Begin
356 hr_utility.set_location('Entering:'||l_proc, 5);
357 --
358 -- Ensure that all the mandatory arguments are not null
359 --
360 hr_api.mandatory_arg_error(p_api_name => l_proc
361 ,p_argument => 'effective_date'
362 ,p_argument_value => p_effective_date
363 );
364 --
365 hr_api.mandatory_arg_error(p_api_name => l_proc
366 ,p_argument => 'datetrack_mode'
367 ,p_argument_value => p_datetrack_mode
368 );
369 --
370 hr_utility.trace('soc_id' || p_soc_ins_contr_lvls_id);
371
372 hr_api.mandatory_arg_error(p_api_name => l_proc
373 ,p_argument => 'soc_ins_contr_lvls_id'
374 ,p_argument_value => p_soc_ins_contr_lvls_id
375 );
376 hr_utility.trace('object' || p_object_version_number);
377 --
378 hr_api.mandatory_arg_error(p_api_name => l_proc
379 ,p_argument => 'object_version_number'
380 ,p_argument_value => p_object_version_number
381 );
382
383 hr_utility.trace('object' || p_object_version_number);
384 --
385 -- Check to ensure the datetrack mode is not INSERT.
386 --
387 If (p_datetrack_mode <> hr_api.g_insert) then
388 --
389 -- We must select and lock the current row.
390 --
391 Open C_Sel1;
392 Fetch C_Sel1 Into hr_cle_shd.g_old_rec;
393 If C_Sel1%notfound then
394 Close C_Sel1;
395 --
396 -- The primary key is invalid therefore we must error
397 --
398 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
399 fnd_message.raise_error;
400 End If;
401 Close C_Sel1;
402 If (p_object_version_number
403 <> hr_cle_shd.g_old_rec.object_version_number) Then
404 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
405 fnd_message.raise_error;
406 End If;
407 --
408 --
409 -- Validate the datetrack mode mode getting the validation start
410 -- and end dates for the specified datetrack operation.
411 --
412 dt_api.validate_dt_mode
413 (p_effective_date => p_effective_date
414 ,p_datetrack_mode => p_datetrack_mode
415 ,p_base_table_name => 'hr_de_soc_ins_contr_lvls_f'
416 ,p_base_key_column => 'soc_ins_contr_lvls_id'
417 ,p_base_key_value => p_soc_ins_contr_lvls_id
418 ,p_enforce_foreign_locking => true
419 ,p_validation_start_date => l_validation_start_date
420 ,p_validation_end_date => l_validation_end_date
421 );
422 Else
423 --
424 -- We are doing a datetrack 'INSERT' which is illegal within this
425 -- procedure therefore we must error (note: to lck on insert the
426 -- private procedure ins_lck should be called).
427 --
428 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
429 fnd_message.set_token('PROCEDURE', l_proc);
430 fnd_message.set_token('STEP','20');
431 fnd_message.raise_error;
432 End If;
433 --
434 -- Set the validation start and end date OUT arguments
435 --
436 p_validation_start_date := l_validation_start_date;
437 p_validation_end_date := l_validation_end_date;
438 --
439 hr_utility.set_location(' Leaving:'||l_proc, 30);
440 --
441 -- We need to trap the ORA LOCK exception
442 --
443 Exception
444 When HR_Api.Object_Locked then
445 --
446 -- The object is locked therefore we need to supply a meaningful
447 -- error message.
448 --
449 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
450 fnd_message.set_token('TABLE_NAME', 'hr_de_soc_ins_contr_lvls_f');
451 fnd_message.raise_error;
452 End lck;
453 --
454 -- ----------------------------------------------------------------------------
455 -- |-----------------------------< convert_args >-----------------------------|
456 -- ----------------------------------------------------------------------------
457 Function convert_args
458 (p_soc_ins_contr_lvls_id in number
459 ,p_organization_id in number
460 ,p_normal_percentage in number
461 ,p_normal_amount in number
462 ,p_increased_percentage in number
463 ,p_increased_amount in number
464 ,p_reduced_percentage in number
465 ,p_reduced_amount in number
466 ,p_effective_start_date in date
467 ,p_effective_end_date in date
468 ,p_attribute_category in varchar2
469 ,p_attribute1 in varchar2
470 ,p_attribute2 in varchar2
471 ,p_attribute3 in varchar2
472 ,p_attribute4 in varchar2
473 ,p_attribute5 in varchar2
474 ,p_attribute6 in varchar2
475 ,p_attribute7 in varchar2
476 ,p_attribute8 in varchar2
477 ,p_attribute9 in varchar2
478 ,p_attribute10 in varchar2
479 ,p_attribute11 in varchar2
480 ,p_attribute12 in varchar2
481 ,p_attribute13 in varchar2
482 ,p_attribute14 in varchar2
483 ,p_attribute15 in varchar2
484 ,p_attribute16 in varchar2
485 ,p_attribute17 in varchar2
486 ,p_attribute18 in varchar2
487 ,p_attribute19 in varchar2
488 ,p_attribute20 in varchar2
489 ,p_object_version_number in number
490 ,p_attribute21 in varchar2
491 ,p_attribute22 in varchar2
492 ,p_attribute23 in varchar2
493 ,p_attribute24 in varchar2
494 ,p_attribute25 in varchar2
495 ,p_attribute26 in varchar2
496 ,p_attribute27 in varchar2
497 ,p_attribute28 in varchar2
498 ,p_attribute29 in varchar2
499 ,p_attribute30 in varchar2
500 ,p_flat_tax_limit_per_month in number
501 ,p_flat_tax_limit_per_year in number
502 ,p_min_increased_contribution in number
503 ,p_max_increased_contribution in number
504 ,p_month1 in varchar2
505 ,p_month1_min_contribution in number
506 ,p_month1_max_contribution in number
507 ,p_month2 in varchar2
508 ,p_month2_min_contribution in number
509 ,p_month2_max_contribution in number
510 ,p_employee_contribution in number
511 ,p_contribution_level_type in varchar2
512 )
513 Return g_rec_type is
514 --
515 l_rec g_rec_type;
516 --
517 Begin
518 --
519 -- Convert arguments into local l_rec structure.
520 --
521 l_rec.soc_ins_contr_lvls_id := p_soc_ins_contr_lvls_id;
522 l_rec.organization_id := p_organization_id;
523 l_rec.normal_percentage := p_normal_percentage;
524 l_rec.normal_amount := p_normal_amount;
525 l_rec.increased_percentage := p_increased_percentage;
526 l_rec.increased_amount := p_increased_amount;
527 l_rec.reduced_percentage := p_reduced_percentage;
528 l_rec.reduced_amount := p_reduced_amount;
529 l_rec.effective_start_date := p_effective_start_date;
530 l_rec.effective_end_date := p_effective_end_date;
531 l_rec.attribute_category := p_attribute_category;
532 l_rec.attribute1 := p_attribute1;
533 l_rec.attribute2 := p_attribute2;
534 l_rec.attribute3 := p_attribute3;
535 l_rec.attribute4 := p_attribute4;
536 l_rec.attribute5 := p_attribute5;
537 l_rec.attribute6 := p_attribute6;
538 l_rec.attribute7 := p_attribute7;
539 l_rec.attribute8 := p_attribute8;
540 l_rec.attribute9 := p_attribute9;
541 l_rec.attribute10 := p_attribute10;
542 l_rec.attribute11 := p_attribute11;
543 l_rec.attribute12 := p_attribute12;
544 l_rec.attribute13 := p_attribute13;
545 l_rec.attribute14 := p_attribute14;
546 l_rec.attribute15 := p_attribute15;
547 l_rec.attribute16 := p_attribute16;
548 l_rec.attribute17 := p_attribute17;
549 l_rec.attribute18 := p_attribute18;
550 l_rec.attribute19 := p_attribute19;
551 l_rec.attribute20 := p_attribute20;
552 l_rec.object_version_number := p_object_version_number;
553 l_rec.attribute21 := p_attribute21;
554 l_rec.attribute22 := p_attribute22;
555 l_rec.attribute23 := p_attribute23;
556 l_rec.attribute24 := p_attribute24;
557 l_rec.attribute25 := p_attribute25;
558 l_rec.attribute26 := p_attribute26;
559 l_rec.attribute27 := p_attribute27;
560 l_rec.attribute28 := p_attribute28;
561 l_rec.attribute29 := p_attribute29;
562 l_rec.attribute30 := p_attribute30;
563 l_rec.flat_tax_limit_per_month := p_flat_tax_limit_per_month;
564 l_rec.flat_tax_limit_per_year := p_flat_tax_limit_per_year;
565 l_rec.min_increased_contribution := p_min_increased_contribution;
566 l_rec.max_increased_contribution := p_max_increased_contribution;
567 l_rec.month1 := p_month1;
568 l_rec.month1_min_contribution := p_month1_min_contribution;
569 l_rec.month1_max_contribution := p_month1_max_contribution;
570 l_rec.month2 := p_month2;
571 l_rec.month2_min_contribution := p_month2_min_contribution;
572 l_rec.month2_max_contribution := p_month2_max_contribution;
573 l_rec.employee_contribution := p_employee_contribution;
574 l_rec.contribution_level_type := p_contribution_level_type;
575
576 --
577 -- Return the plsql record structure.
578 --
579 Return(l_rec);
580 --
581 End convert_args;
582 --
583 end hr_cle_shd;