[Home] [Help]
PACKAGE BODY: APPS.BEN_OTP_SHD
Source
1 Package Body ben_otp_shd as
2 /* $Header: beotprhi.pkb 115.3 2003/09/25 00:30:57 rpgupta noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_otp_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 Function return_api_dml_status Return Boolean Is
14 --
15 Begin
16 --
17 Return (nvl(g_api_dml, false));
18 --
19 End return_api_dml_status;
20 --
21 -- ----------------------------------------------------------------------------
22 -- |---------------------------< constraint_error >---------------------------|
23 -- ----------------------------------------------------------------------------
24 Procedure constraint_error
25 (p_constraint_name in all_constraints.constraint_name%TYPE
26 ) Is
27 --
28 l_proc varchar2(72) := g_package||'constraint_error';
29 --
30 Begin
31 --
32 If (p_constraint_name = 'BEN_OPTIP_F_PK') Then
33 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
34 fnd_message.set_token('PROCEDURE', l_proc);
35 fnd_message.set_token('STEP','5');
36 fnd_message.raise_error;
37 Else
38 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
39 fnd_message.set_token('PROCEDURE', l_proc);
40 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
41 fnd_message.raise_error;
42 End If;
43 --
44 End constraint_error;
45 --
46 -- ----------------------------------------------------------------------------
47 -- |-----------------------------< api_updating >-----------------------------|
48 -- ----------------------------------------------------------------------------
49 Function api_updating
50 (p_effective_date in date
51 ,p_optip_id in number
52 ,p_object_version_number in number
53 ) Return Boolean Is
54 --
55 -- Cursor selects the 'current' row from the HR Schema
56 --
57 Cursor C_Sel1 is
58 select
59 optip_id
60 ,effective_start_date
61 ,effective_end_date
62 ,business_group_id
63 ,pgm_id
64 ,ptip_id
65 ,pl_typ_id
66 ,opt_id
67 ,cmbn_ptip_opt_id
68 ,legislation_code
69 ,legislation_subgroup
70 ,otp_attribute_category
71 ,otp_attribute1
72 ,otp_attribute2
73 ,otp_attribute3
74 ,otp_attribute4
75 ,otp_attribute5
76 ,otp_attribute6
77 ,otp_attribute7
78 ,otp_attribute8
79 ,otp_attribute9
80 ,otp_attribute10
81 ,otp_attribute11
82 ,otp_attribute12
83 ,otp_attribute13
84 ,otp_attribute14
85 ,otp_attribute15
86 ,otp_attribute16
87 ,otp_attribute17
88 ,otp_attribute18
89 ,otp_attribute19
90 ,otp_attribute20
91 ,otp_attribute21
92 ,otp_attribute22
93 ,otp_attribute23
94 ,otp_attribute24
95 ,otp_attribute25
96 ,otp_attribute26
97 ,otp_attribute27
98 ,otp_attribute28
99 ,otp_attribute29
100 ,otp_attribute30
101 ,object_version_number
102 from ben_optip_f
103 where optip_id = p_optip_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_optip_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_optip_id =
121 ben_otp_shd.g_old_rec.optip_id and
122 p_object_version_number =
123 ben_otp_shd.g_old_rec.object_version_number) Then
124 --
125 -- The g_old_rec is current therefore we must
126 -- set the returning function to true
127 --
128 l_fct_ret := true;
129 Else
130 --
131 -- Select the current row
132 --
133 Open C_Sel1;
134 Fetch C_Sel1 Into ben_otp_shd.g_old_rec;
135 If C_Sel1%notfound Then
136 Close C_Sel1;
137 --
138 -- The primary key is invalid therefore we must error
139 --
140 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
141 fnd_message.raise_error;
142 End If;
143 Close C_Sel1;
144 If (p_object_version_number
145 <> ben_otp_shd.g_old_rec.object_version_number) Then
146 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
147 fnd_message.raise_error;
148 End If;
149 l_fct_ret := true;
150 End If;
151 End If;
152 Return (l_fct_ret);
153 --
154 End api_updating;
155 --
156 -- ----------------------------------------------------------------------------
157 -- |---------------------------< find_dt_upd_modes >--------------------------|
158 -- ----------------------------------------------------------------------------
159 Procedure find_dt_upd_modes
160 (p_effective_date in date
161 ,p_base_key_value in number
162 ,p_correction out nocopy boolean
163 ,p_update out nocopy boolean
164 ,p_update_override out nocopy boolean
165 ,p_update_change_insert out nocopy boolean
166 ) is
167 --
168 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
169 --
170 Begin
171 hr_utility.set_location('Entering:'||l_proc, 5);
172 --
173 -- Call the corresponding datetrack api
174 --
175 dt_api.find_dt_upd_modes
176 (p_effective_date => p_effective_date
177 ,p_base_table_name => 'ben_optip_f'
178 ,p_base_key_column => 'optip_id'
179 ,p_base_key_value => p_base_key_value
180 ,p_correction => p_correction
181 ,p_update => p_update
182 ,p_update_override => p_update_override
183 ,p_update_change_insert => p_update_change_insert
184 );
185 --
186 hr_utility.set_location(' Leaving:'||l_proc, 10);
187 End find_dt_upd_modes;
188 --
189 -- ----------------------------------------------------------------------------
190 -- |---------------------------< find_dt_del_modes >--------------------------|
191 -- ----------------------------------------------------------------------------
192 Procedure find_dt_del_modes
193 (p_effective_date in date
194 ,p_base_key_value in number
195 ,p_zap out nocopy boolean
196 ,p_delete out nocopy boolean
197 ,p_future_change out nocopy boolean
198 ,p_delete_next_change out nocopy boolean
199 ) is
200 --
201 l_proc varchar2(72) := g_package||'find_dt_del_modes';
202 --
203 l_parent_key_value1 number;
204 l_parent_key_value2 number;
205 l_parent_key_value3 number;
206 --
207 Cursor C_Sel1 Is
208 select
209 t.pgm_id
210 ,t.ptip_id
211 ,t.opt_id
212 from ben_optip_f t
213 where t.optip_id = p_base_key_value
214 and p_effective_date
215 between t.effective_start_date and t.effective_end_date;
216 --
217 Begin
218 hr_utility.set_location('Entering:'||l_proc, 5);
219 Open C_sel1;
220 Fetch C_Sel1 Into
221 l_parent_key_value1
222 ,l_parent_key_value2
223 ,l_parent_key_value3;
224 If C_Sel1%NOTFOUND then
225 Close C_Sel1;
226 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
227 fnd_message.set_token('PROCEDURE',l_proc);
228 fnd_message.set_token('STEP','10');
229 fnd_message.raise_error;
230 End If;
231 Close C_Sel1;
232 --
233 -- Call the corresponding datetrack api
234 --
235 dt_api.find_dt_del_modes
236 (p_effective_date => p_effective_date
237 ,p_base_table_name => 'ben_optip_f'
238 ,p_base_key_column => 'optip_id'
239 ,p_base_key_value => p_base_key_value
240 ,p_parent_table_name1 => 'ben_pgm_f'
241 ,p_parent_key_column1 => 'pgm_id'
242 ,p_parent_key_value1 => l_parent_key_value1
243 ,p_parent_table_name2 => 'ben_ptip_f'
244 ,p_parent_key_column2 => 'ptip_id'
245 ,p_parent_key_value2 => l_parent_key_value2
246 ,p_parent_table_name3 => 'ben_opt_f'
247 ,p_parent_key_column3 => 'opt_id'
248 ,p_parent_key_value3 => l_parent_key_value3
249 ,p_zap => p_zap
250 ,p_delete => p_delete
251 ,p_future_change => p_future_change
252 ,p_delete_next_change => p_delete_next_change
253 );
254 --
255 hr_utility.set_location(' Leaving:'||l_proc, 10);
256 End find_dt_del_modes;
257 --
258 -- ----------------------------------------------------------------------------
259 -- |-----------------------< upd_effective_end_date >-------------------------|
260 -- ----------------------------------------------------------------------------
261 Procedure upd_effective_end_date
262 (p_effective_date in date
263 ,p_base_key_value in number
264 ,p_new_effective_end_date in date
265 ,p_validation_start_date in date
266 ,p_validation_end_date in date
267 ,p_object_version_number out nocopy number
268 ) is
269 --
270 l_proc varchar2(72) := g_package||'upd_effective_end_date';
271 l_object_version_number number;
272 --
273 Begin
274 hr_utility.set_location('Entering:'||l_proc, 5);
275 --
276 -- Because we are updating a row we must get the next object
277 -- version number.
278 --
279 l_object_version_number :=
280 dt_api.get_object_version_number
281 (p_base_table_name => 'ben_optip_f'
282 ,p_base_key_column => 'optip_id'
283 ,p_base_key_value => p_base_key_value
284 );
285 --
286 hr_utility.set_location(l_proc, 10);
287 ben_otp_shd.g_api_dml := true; -- Set the api dml status
288 --
289 -- Update the specified datetrack row setting the effective
290 -- end date to the specified new effective end date.
291 --
292 update ben_optip_f t
293 set t.effective_end_date = p_new_effective_end_date
294 , t.object_version_number = l_object_version_number
295 where t.optip_id = p_base_key_value
296 and p_effective_date
297 between t.effective_start_date and t.effective_end_date;
298 --
299 ben_otp_shd.g_api_dml := false; -- Unset the api dml status
300 p_object_version_number := l_object_version_number;
301 hr_utility.set_location(' Leaving:'||l_proc, 15);
302 --
303 Exception
304 When Others Then
305 ben_otp_shd.g_api_dml := false; -- Unset the api dml status
306 Raise;
307 --
308 End upd_effective_end_date;
309 --
310 -- ----------------------------------------------------------------------------
311 -- |---------------------------------< lck >----------------------------------|
312 -- ----------------------------------------------------------------------------
313 Procedure lck
314 (p_effective_date in date
315 ,p_datetrack_mode in varchar2
316 ,p_optip_id in number
317 ,p_object_version_number in number
318 ,p_validation_start_date out nocopy date
319 ,p_validation_end_date out nocopy date
320 ) is
321 --
322 l_proc varchar2(72) := g_package||'lck';
323 l_validation_start_date date;
324 l_validation_end_date date;
325 l_argument varchar2(30);
326 --
327 -- Cursor C_Sel1 selects the current locked row as of session date
328 -- ensuring that the object version numbers match.
329 --
330 Cursor C_Sel1 is
331 select
332 optip_id
333 ,effective_start_date
334 ,effective_end_date
335 ,business_group_id
336 ,pgm_id
337 ,ptip_id
338 ,pl_typ_id
339 ,opt_id
340 ,cmbn_ptip_opt_id
341 ,legislation_code
342 ,legislation_subgroup
343 ,otp_attribute_category
344 ,otp_attribute1
345 ,otp_attribute2
346 ,otp_attribute3
347 ,otp_attribute4
348 ,otp_attribute5
349 ,otp_attribute6
350 ,otp_attribute7
351 ,otp_attribute8
352 ,otp_attribute9
353 ,otp_attribute10
354 ,otp_attribute11
355 ,otp_attribute12
356 ,otp_attribute13
357 ,otp_attribute14
358 ,otp_attribute15
359 ,otp_attribute16
360 ,otp_attribute17
361 ,otp_attribute18
362 ,otp_attribute19
363 ,otp_attribute20
364 ,otp_attribute21
365 ,otp_attribute22
366 ,otp_attribute23
367 ,otp_attribute24
368 ,otp_attribute25
369 ,otp_attribute26
370 ,otp_attribute27
371 ,otp_attribute28
372 ,otp_attribute29
373 ,otp_attribute30
374 ,object_version_number
375 from ben_optip_f
376 where optip_id = p_optip_id
377 and p_effective_date
378 between effective_start_date and effective_end_date
379 for update nowait;
380 --
381 --
382 --
383 Begin
384 hr_utility.set_location('Entering:'||l_proc, 5);
385 --
386 -- Ensure that all the mandatory arguments are not null
387 --
388 hr_api.mandatory_arg_error(p_api_name => l_proc
389 ,p_argument => 'effective_date'
390 ,p_argument_value => p_effective_date
391 );
392 --
393 hr_api.mandatory_arg_error(p_api_name => l_proc
394 ,p_argument => 'datetrack_mode'
395 ,p_argument_value => p_datetrack_mode
396 );
397 --
398 hr_api.mandatory_arg_error(p_api_name => l_proc
399 ,p_argument => 'optip_id'
400 ,p_argument_value => p_optip_id
401 );
402 --
403 hr_api.mandatory_arg_error(p_api_name => l_proc
404 ,p_argument => 'object_version_number'
405 ,p_argument_value => p_object_version_number
406 );
407 --
408 -- Check to ensure the datetrack mode is not INSERT.
409 --
410 If (p_datetrack_mode <> hr_api.g_insert) then
411 --
412 -- We must select and lock the current row.
413 --
414 Open C_Sel1;
415 Fetch C_Sel1 Into ben_otp_shd.g_old_rec;
416 If C_Sel1%notfound then
417 Close C_Sel1;
418 --
419 -- The primary key is invalid therefore we must error
420 --
421 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
422 fnd_message.raise_error;
423 End If;
424 Close C_Sel1;
425 If (p_object_version_number
426 <> ben_otp_shd.g_old_rec.object_version_number) Then
427 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
428 fnd_message.raise_error;
429 End If;
430 --
431 --
432 -- Validate the datetrack mode mode getting the validation start
436 (p_effective_date => p_effective_date
433 -- and end dates for the specified datetrack operation.
434 --
435 dt_api.validate_dt_mode
437 ,p_datetrack_mode => p_datetrack_mode
438 ,p_base_table_name => 'ben_optip_f'
439 ,p_base_key_column => 'optip_id'
440 ,p_base_key_value => p_optip_id
441 ,p_parent_table_name1 => 'ben_pgm_f'
442 ,p_parent_key_column1 => 'pgm_id'
443 ,p_parent_key_value1 => ben_otp_shd.g_old_rec.pgm_id
444 ,p_parent_table_name2 => 'ben_ptip_f'
445 ,p_parent_key_column2 => 'ptip_id'
446 ,p_parent_key_value2 => ben_otp_shd.g_old_rec.ptip_id
447 ,p_parent_table_name3 => 'ben_opt_f'
448 ,p_parent_key_column3 => 'opt_id'
449 ,p_parent_key_value3 => ben_otp_shd.g_old_rec.opt_id
450 ,p_enforce_foreign_locking => true
451 ,p_validation_start_date => l_validation_start_date
452 ,p_validation_end_date => l_validation_end_date
453 );
454 Else
455 --
456 -- We are doing a datetrack 'INSERT' which is illegal within this
457 -- procedure therefore we must error (note: to lck on insert the
458 -- private procedure ins_lck should be called).
459 --
460 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
461 fnd_message.set_token('PROCEDURE', l_proc);
462 fnd_message.set_token('STEP','20');
463 fnd_message.raise_error;
464 End If;
465 --
466 -- Set the validation start and end date OUT arguments
467 --
468 p_validation_start_date := l_validation_start_date;
469 p_validation_end_date := l_validation_end_date;
470 --
471 hr_utility.set_location(' Leaving:'||l_proc, 30);
472 --
473 -- We need to trap the ORA LOCK exception
474 --
475 Exception
476 When HR_Api.Object_Locked then
477 --
478 -- The object is locked therefore we need to supply a meaningful
479 -- error message.
480 --
481 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
482 fnd_message.set_token('TABLE_NAME', 'ben_optip_f');
483 fnd_message.raise_error;
484 End lck;
485 --
486 -- ----------------------------------------------------------------------------
487 -- |-----------------------------< convert_args >-----------------------------|
488 -- ----------------------------------------------------------------------------
489 Function convert_args
490 (p_optip_id in number
491 ,p_effective_start_date in date
492 ,p_effective_end_date in date
493 ,p_business_group_id in number
494 ,p_pgm_id in number
495 ,p_ptip_id in number
496 ,p_pl_typ_id in number
497 ,p_opt_id in number
498 ,p_cmbn_ptip_opt_id in number
499 ,p_legislation_code in varchar2
500 ,p_legislation_subgroup in varchar2
501 ,p_otp_attribute_category in varchar2
502 ,p_otp_attribute1 in varchar2
503 ,p_otp_attribute2 in varchar2
504 ,p_otp_attribute3 in varchar2
505 ,p_otp_attribute4 in varchar2
506 ,p_otp_attribute5 in varchar2
507 ,p_otp_attribute6 in varchar2
508 ,p_otp_attribute7 in varchar2
509 ,p_otp_attribute8 in varchar2
510 ,p_otp_attribute9 in varchar2
511 ,p_otp_attribute10 in varchar2
512 ,p_otp_attribute11 in varchar2
513 ,p_otp_attribute12 in varchar2
514 ,p_otp_attribute13 in varchar2
515 ,p_otp_attribute14 in varchar2
516 ,p_otp_attribute15 in varchar2
517 ,p_otp_attribute16 in varchar2
518 ,p_otp_attribute17 in varchar2
519 ,p_otp_attribute18 in varchar2
520 ,p_otp_attribute19 in varchar2
521 ,p_otp_attribute20 in varchar2
522 ,p_otp_attribute21 in varchar2
523 ,p_otp_attribute22 in varchar2
524 ,p_otp_attribute23 in varchar2
525 ,p_otp_attribute24 in varchar2
526 ,p_otp_attribute25 in varchar2
527 ,p_otp_attribute26 in varchar2
528 ,p_otp_attribute27 in varchar2
529 ,p_otp_attribute28 in varchar2
530 ,p_otp_attribute29 in varchar2
531 ,p_otp_attribute30 in varchar2
532 ,p_object_version_number in number
533 )
534 Return g_rec_type is
535 --
536 l_rec g_rec_type;
537 --
538 Begin
539 --
540 -- Convert arguments into local l_rec structure.
541 --
542 l_rec.optip_id := p_optip_id;
543 l_rec.effective_start_date := p_effective_start_date;
544 l_rec.effective_end_date := p_effective_end_date;
545 l_rec.business_group_id := p_business_group_id;
546 l_rec.pgm_id := p_pgm_id;
547 l_rec.ptip_id := p_ptip_id;
548 l_rec.pl_typ_id := p_pl_typ_id;
549 l_rec.opt_id := p_opt_id;
550 l_rec.cmbn_ptip_opt_id := p_cmbn_ptip_opt_id;
551 l_rec.legislation_code := p_legislation_code;
552 l_rec.legislation_subgroup := p_legislation_subgroup;
553 l_rec.otp_attribute_category := p_otp_attribute_category;
554 l_rec.otp_attribute1 := p_otp_attribute1;
555 l_rec.otp_attribute2 := p_otp_attribute2;
556 l_rec.otp_attribute3 := p_otp_attribute3;
557 l_rec.otp_attribute4 := p_otp_attribute4;
558 l_rec.otp_attribute5 := p_otp_attribute5;
559 l_rec.otp_attribute6 := p_otp_attribute6;
560 l_rec.otp_attribute7 := p_otp_attribute7;
561 l_rec.otp_attribute8 := p_otp_attribute8;
562 l_rec.otp_attribute9 := p_otp_attribute9;
563 l_rec.otp_attribute10 := p_otp_attribute10;
564 l_rec.otp_attribute11 := p_otp_attribute11;
565 l_rec.otp_attribute12 := p_otp_attribute12;
566 l_rec.otp_attribute13 := p_otp_attribute13;
567 l_rec.otp_attribute14 := p_otp_attribute14;
568 l_rec.otp_attribute15 := p_otp_attribute15;
569 l_rec.otp_attribute16 := p_otp_attribute16;
570 l_rec.otp_attribute17 := p_otp_attribute17;
571 l_rec.otp_attribute18 := p_otp_attribute18;
572 l_rec.otp_attribute19 := p_otp_attribute19;
573 l_rec.otp_attribute20 := p_otp_attribute20;
574 l_rec.otp_attribute21 := p_otp_attribute21;
575 l_rec.otp_attribute22 := p_otp_attribute22;
576 l_rec.otp_attribute23 := p_otp_attribute23;
577 l_rec.otp_attribute24 := p_otp_attribute24;
578 l_rec.otp_attribute25 := p_otp_attribute25;
579 l_rec.otp_attribute26 := p_otp_attribute26;
580 l_rec.otp_attribute27 := p_otp_attribute27;
581 l_rec.otp_attribute28 := p_otp_attribute28;
582 l_rec.otp_attribute29 := p_otp_attribute29;
583 l_rec.otp_attribute30 := p_otp_attribute30;
584 l_rec.object_version_number := p_object_version_number;
585 --
586 -- Return the plsql record structure.
587 --
588 Return(l_rec);
589 --
590 End convert_args;
591 --
592 end ben_otp_shd;