[Home] [Help]
PACKAGE BODY: APPS.BEN_PTP_SHD
Source
1 Package Body ben_ptp_shd as
2 /* $Header: beptprhi.pkb 120.1 2005/06/02 03:22:51 bmanyam noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ptp_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 --
39 fnd_message.set_token('STEP','5');
36 If (p_constraint_name = 'BEN_PL_TYP_F_FK1') Then
37 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
38 fnd_message.set_token('PROCEDURE', l_proc);
40 fnd_message.raise_error;
41 ElsIf (p_constraint_name = 'BEN_PL_TYP_F_PK') Then
42 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
43 fnd_message.set_token('PROCEDURE', l_proc);
44 fnd_message.set_token('STEP','10');
45 fnd_message.raise_error;
46 ElsIf (p_constraint_name = 'NN_OPT_TYP_CD') Then
47 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
48 fnd_message.set_token('PROCEDURE', l_proc);
49 fnd_message.set_token('STEP','15');
50 fnd_message.raise_error;
51 ElsIf (p_constraint_name = 'BEN_PL_TYP_F_UK1') Then
52 fnd_message.set_name('BEN', 'BEN_91009_NAME_NOT_UNIQUE');
53 fnd_message.raise_error;
54 Else
55 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
56 fnd_message.set_token('PROCEDURE', l_proc);
57 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
58 fnd_message.raise_error;
59 End If;
60 --
61 hr_utility.set_location(' Leaving:'||l_proc, 10);
62 End constraint_error;
63 --
64 -- ----------------------------------------------------------------------------
65 -- |-----------------------------< api_updating >-----------------------------|
66 -- ----------------------------------------------------------------------------
67 Function api_updating
68 (p_effective_date in date,
69 p_pl_typ_id in number,
70 p_object_version_number in number
71 ) Return Boolean Is
72 --
73 --
74 -- Cursor selects the 'current' row from the HR Schema
75 --
76 Cursor C_Sel1 is
77 select
78 pl_typ_id,
79 effective_start_date,
80 effective_end_date,
81 name,
82 mx_enrl_alwd_num,
83 mn_enrl_rqd_num,
84 pl_typ_stat_cd,
85 opt_typ_cd,
86 opt_dsply_fmt_cd,
87 comp_typ_cd,
88 ivr_ident,
89 no_mx_enrl_num_dfnd_flag,
90 no_mn_enrl_num_dfnd_flag,
91 business_group_id,
92 ptp_attribute_category,
93 ptp_attribute1,
94 ptp_attribute2,
95 ptp_attribute3,
96 ptp_attribute4,
97 ptp_attribute5,
98 ptp_attribute6,
99 ptp_attribute7,
100 ptp_attribute8,
101 ptp_attribute9,
102 ptp_attribute10,
103 ptp_attribute11,
104 ptp_attribute12,
105 ptp_attribute13,
106 ptp_attribute14,
107 ptp_attribute15,
108 ptp_attribute16,
109 ptp_attribute17,
110 ptp_attribute18,
111 ptp_attribute19,
112 ptp_attribute20,
113 ptp_attribute21,
114 ptp_attribute22,
115 ptp_attribute23,
116 ptp_attribute24,
117 ptp_attribute25,
118 ptp_attribute26,
119 ptp_attribute27,
120 ptp_attribute28,
121 ptp_attribute29,
122 ptp_attribute30,
123 short_name, /*FHR*/
124 short_code, /*FHR*/
125 legislation_code,
126 legislation_subgroup,
127 object_version_number
128 from ben_pl_typ_f
129 where pl_typ_id = p_pl_typ_id
130 and p_effective_date
131 between effective_start_date and effective_end_date;
132 --
133 l_proc varchar2(72) := g_package||'api_updating';
134 l_fct_ret boolean;
135 --
136 Begin
137 hr_utility.set_location('Entering:'||l_proc, 5);
138 --
139 If (p_effective_date is null or
140 p_pl_typ_id is null or
141 p_object_version_number is null) Then
142 --
143 -- One of the primary key arguments is null therefore we must
144 -- set the returning function value to false
145 --
146 l_fct_ret := false;
147 Else
148 If (p_pl_typ_id = g_old_rec.pl_typ_id and
149 p_object_version_number = g_old_rec.object_version_number) Then
150 hr_utility.set_location(l_proc, 10);
151 --
152 -- The g_old_rec is current therefore we must
153 -- set the returning function to true
154 --
155 l_fct_ret := true;
156 Else
157 --
158 -- Select the current row
159 --
160 Open C_Sel1;
161 Fetch C_Sel1 Into g_old_rec;
162 If C_Sel1%notfound Then
163 Close C_Sel1;
164 --
165 -- The primary key is invalid therefore we must error
166 --
167 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
168 fnd_message.raise_error;
169 End If;
170 Close C_Sel1;
171 If (p_object_version_number <> g_old_rec.object_version_number) Then
172 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
173 fnd_message.raise_error;
174 End If;
175 hr_utility.set_location(l_proc, 15);
176 l_fct_ret := true;
177 End If;
178 End If;
179 hr_utility.set_location(' Leaving:'||l_proc, 20);
180 Return (l_fct_ret);
181 --
182 End api_updating;
183 --
184 -- ----------------------------------------------------------------------------
185 -- |--------------------------< find_dt_del_modes >---------------------------|
186 -- ----------------------------------------------------------------------------
187 Procedure find_dt_del_modes
188 (p_effective_date in date,
189 p_base_key_value in number,
190 p_zap out nocopy boolean,
191 p_delete out nocopy boolean,
192 p_future_change out nocopy boolean,
193 p_delete_next_change out nocopy boolean) is
194 --
198 --
195 l_proc varchar2(72) := g_package||'find_dt_del_modes';
196 --
197 --
199 --
200 Begin
201 hr_utility.set_location('Entering:'||l_proc, 5);
202 --
203 --
204 -- Call the corresponding datetrack api
205 --
206 dt_api.find_dt_del_modes
207 (p_effective_date => p_effective_date,
208 p_base_table_name => 'ben_pl_typ_f',
209 p_base_key_column => 'pl_typ_id',
210 p_base_key_value => p_base_key_value,
211 p_zap => p_zap,
212 p_delete => p_delete,
213 p_future_change => p_future_change,
214 p_delete_next_change => p_delete_next_change);
215 --
216 hr_utility.set_location(' Leaving:'||l_proc, 10);
217 End find_dt_del_modes;
218 --
219 -- ----------------------------------------------------------------------------
220 -- |--------------------------< find_dt_upd_modes >---------------------------|
221 -- ----------------------------------------------------------------------------
222 Procedure find_dt_upd_modes
223 (p_effective_date in date,
224 p_base_key_value in number,
225 p_correction out nocopy boolean,
226 p_update out nocopy boolean,
227 p_update_override out nocopy boolean,
228 p_update_change_insert out nocopy boolean) is
229 --
230 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
231 --
232 Begin
233 hr_utility.set_location('Entering:'||l_proc, 5);
234 --
235 -- Call the corresponding datetrack api
236 --
237 dt_api.find_dt_upd_modes
238 (p_effective_date => p_effective_date,
239 p_base_table_name => 'ben_pl_typ_f',
240 p_base_key_column => 'pl_typ_id',
241 p_base_key_value => p_base_key_value,
242 p_correction => p_correction,
243 p_update => p_update,
244 p_update_override => p_update_override,
245 p_update_change_insert => p_update_change_insert);
246 --
247 hr_utility.set_location(' Leaving:'||l_proc, 10);
248 End find_dt_upd_modes;
249 --
250 -- ----------------------------------------------------------------------------
251 -- |------------------------< upd_effective_end_date >------------------------|
252 -- ----------------------------------------------------------------------------
253 Procedure upd_effective_end_date
254 (p_effective_date in date,
255 p_base_key_value in number,
256 p_new_effective_end_date in date,
257 p_validation_start_date in date,
258 p_validation_end_date in date,
259 p_object_version_number out nocopy number) is
260 --
261 l_proc varchar2(72) := g_package||'upd_effective_end_date';
262 l_object_version_number number;
263 --
264 Begin
265 hr_utility.set_location('Entering:'||l_proc, 5);
266 --
267 -- Because we are updating a row we must get the next object
268 -- version number.
269 --
270 l_object_version_number :=
271 dt_api.get_object_version_number
272 (p_base_table_name => 'ben_pl_typ_f',
273 p_base_key_column => 'pl_typ_id',
274 p_base_key_value => p_base_key_value);
275 --
276 hr_utility.set_location(l_proc, 10);
277 g_api_dml := true; -- Set the api dml status
278 --
279 -- Update the specified datetrack row setting the effective
280 -- end date to the specified new effective end date.
281 --
282 update ben_pl_typ_f t
283 set t.effective_end_date = p_new_effective_end_date,
284 t.object_version_number = l_object_version_number
285 where t.pl_typ_id = p_base_key_value
286 and p_effective_date
287 between t.effective_start_date and t.effective_end_date;
288 --
289 g_api_dml := false; -- Unset the api dml status
290 p_object_version_number := l_object_version_number;
291 hr_utility.set_location(' Leaving:'||l_proc, 15);
292 --
293 Exception
294 When Others Then
295 g_api_dml := false; -- Unset the api dml status
296 Raise;
297 End upd_effective_end_date;
298 --
299 -- ----------------------------------------------------------------------------
300 -- |---------------------------------< lck >----------------------------------|
301 -- ----------------------------------------------------------------------------
302 Procedure lck
303 (p_effective_date in date,
304 p_datetrack_mode in varchar2,
305 p_pl_typ_id in number,
306 p_object_version_number in number,
307 p_validation_start_date out nocopy date,
308 p_validation_end_date out nocopy date) is
309 --
310 l_proc varchar2(72) := g_package||'lck';
311 l_validation_start_date date;
312 l_validation_end_date date;
313 l_object_invalid exception;
314 l_argument varchar2(30);
315 --
316 -- Cursor C_Sel1 selects the current locked row as of session date
317 -- ensuring that the object version numbers match.
318 --
319 Cursor C_Sel1 is
320 select
321 pl_typ_id,
322 effective_start_date,
323 effective_end_date,
324 name,
325 mx_enrl_alwd_num,
326 mn_enrl_rqd_num,
327 pl_typ_stat_cd,
328 opt_typ_cd,
329 opt_dsply_fmt_cd,
330 comp_typ_cd,
331 ivr_ident,
332 no_mx_enrl_num_dfnd_flag,
333 no_mn_enrl_num_dfnd_flag,
334 business_group_id,
335 ptp_attribute_category,
336 ptp_attribute1,
337 ptp_attribute2,
338 ptp_attribute3,
339 ptp_attribute4,
340 ptp_attribute5,
341 ptp_attribute6,
342 ptp_attribute7,
343 ptp_attribute8,
344 ptp_attribute9,
345 ptp_attribute10,
346 ptp_attribute11,
347 ptp_attribute12,
348 ptp_attribute13,
349 ptp_attribute14,
353 ptp_attribute18,
350 ptp_attribute15,
351 ptp_attribute16,
352 ptp_attribute17,
354 ptp_attribute19,
355 ptp_attribute20,
356 ptp_attribute21,
357 ptp_attribute22,
358 ptp_attribute23,
359 ptp_attribute24,
360 ptp_attribute25,
361 ptp_attribute26,
362 ptp_attribute27,
363 ptp_attribute28,
364 ptp_attribute29,
365 ptp_attribute30,
366 short_name, /*FHR*/
367 short_code, /*FHR*/
368 legislation_code,
369 legislation_subgroup,
370 object_version_number
371 from ben_pl_typ_f
372 where pl_typ_id = p_pl_typ_id
373 and p_effective_date
374 between effective_start_date and effective_end_date
375 for update nowait;
376 --
377 --
378 --
379 Begin
380 hr_utility.set_location('Entering:'||l_proc, 5);
381 --
382 -- Ensure that all the mandatory arguments are not null
383 --
384 hr_api.mandatory_arg_error(p_api_name => l_proc,
385 p_argument => 'effective_date',
386 p_argument_value => p_effective_date);
387 --
388 hr_api.mandatory_arg_error(p_api_name => l_proc,
389 p_argument => 'datetrack_mode',
390 p_argument_value => p_datetrack_mode);
391 --
392 hr_api.mandatory_arg_error(p_api_name => l_proc,
393 p_argument => 'pl_typ_id',
394 p_argument_value => p_pl_typ_id);
395 --
396 hr_api.mandatory_arg_error(p_api_name => l_proc,
397 p_argument => 'object_version_number',
398 p_argument_value => p_object_version_number);
399 --
400 -- Check to ensure the datetrack mode is not INSERT.
401 --
402 If (p_datetrack_mode <> 'INSERT') then
403 --
404 -- We must select and lock the current row.
405 --
406 Open C_Sel1;
407 Fetch C_Sel1 Into g_old_rec;
408 If C_Sel1%notfound then
409 Close C_Sel1;
410 --
411 -- The primary key is invalid therefore we must error
412 --
413 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
414 fnd_message.raise_error;
415 End If;
416 Close C_Sel1;
417 If (p_object_version_number <> g_old_rec.object_version_number) Then
418 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
419 fnd_message.raise_error;
420 End If;
421 hr_utility.set_location(l_proc, 15);
422 --
423 --
424 -- Validate the datetrack mode mode getting the validation start
425 -- and end dates for the specified datetrack operation.
426 --
427 dt_api.validate_dt_mode
428 (p_effective_date => p_effective_date,
429 p_datetrack_mode => p_datetrack_mode,
430 p_base_table_name => 'ben_pl_typ_f',
431 p_base_key_column => 'pl_typ_id',
432 p_base_key_value => p_pl_typ_id,
433 p_child_table_name1 => 'ben_ptip_f',
434 p_child_key_column1 => 'ptip_id',
435 p_child_table_name2 => 'ben_pl_f',
436 p_child_key_column2 => 'pl_id',
437 -- p_enforce_foreign_locking => true, Bug 3198808
438 p_enforce_foreign_locking => false,
439 p_validation_start_date => l_validation_start_date,
440 p_validation_end_date => l_validation_end_date);
441 Else
442 --
443 -- We are doing a datetrack 'INSERT' which is illegal within this
444 -- procedure therefore we must error (note: to lck on insert the
445 -- private procedure ins_lck should be called).
446 --
447 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
448 fnd_message.set_token('PROCEDURE', l_proc);
449 fnd_message.set_token('STEP','20');
450 fnd_message.raise_error;
451 End If;
452 --
453 -- Set the validation start and end date OUT arguments
454 --
455 p_validation_start_date := l_validation_start_date;
456 p_validation_end_date := l_validation_end_date;
457 --
458 hr_utility.set_location(' Leaving:'||l_proc, 30);
459 --
460 -- We need to trap the ORA LOCK exception
461 --
462 Exception
463 When HR_Api.Object_Locked then
464 --
465 -- The object is locked therefore we need to supply a meaningful
466 -- error message.
467 --
468 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
469 fnd_message.set_token('TABLE_NAME', 'ben_pl_typ_f');
470 fnd_message.raise_error;
471 When l_object_invalid then
472 --
473 -- The object doesn't exist or is invalid
474 --
475 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
476 fnd_message.set_token('TABLE_NAME', 'ben_pl_typ_f');
477 fnd_message.raise_error;
478 End lck;
479 --
480 -- ----------------------------------------------------------------------------
481 -- |-----------------------------< convert_args >-----------------------------|
482 -- ----------------------------------------------------------------------------
483 Function convert_args
484 (
485 p_pl_typ_id in number,
486 p_effective_start_date in date,
487 p_effective_end_date in date,
488 p_name in varchar2,
489 p_mx_enrl_alwd_num in number,
490 p_mn_enrl_rqd_num in number,
491 p_pl_typ_stat_cd in varchar2,
492 p_opt_typ_cd in varchar2,
493 p_opt_dsply_fmt_cd in varchar2,
494 p_comp_typ_cd in varchar2,
495 p_ivr_ident in varchar2,
496 p_no_mx_enrl_num_dfnd_flag in varchar2,
497 p_no_mn_enrl_num_dfnd_flag in varchar2,
501 p_ptp_attribute2 in varchar2,
498 p_business_group_id in number,
499 p_ptp_attribute_category in varchar2,
500 p_ptp_attribute1 in varchar2,
502 p_ptp_attribute3 in varchar2,
503 p_ptp_attribute4 in varchar2,
504 p_ptp_attribute5 in varchar2,
505 p_ptp_attribute6 in varchar2,
506 p_ptp_attribute7 in varchar2,
507 p_ptp_attribute8 in varchar2,
508 p_ptp_attribute9 in varchar2,
509 p_ptp_attribute10 in varchar2,
510 p_ptp_attribute11 in varchar2,
511 p_ptp_attribute12 in varchar2,
512 p_ptp_attribute13 in varchar2,
513 p_ptp_attribute14 in varchar2,
514 p_ptp_attribute15 in varchar2,
515 p_ptp_attribute16 in varchar2,
516 p_ptp_attribute17 in varchar2,
517 p_ptp_attribute18 in varchar2,
518 p_ptp_attribute19 in varchar2,
519 p_ptp_attribute20 in varchar2,
520 p_ptp_attribute21 in varchar2,
521 p_ptp_attribute22 in varchar2,
522 p_ptp_attribute23 in varchar2,
523 p_ptp_attribute24 in varchar2,
524 p_ptp_attribute25 in varchar2,
525 p_ptp_attribute26 in varchar2,
526 p_ptp_attribute27 in varchar2,
527 p_ptp_attribute28 in varchar2,
528 p_ptp_attribute29 in varchar2,
529 p_ptp_attribute30 in varchar2,
530 p_short_name in varchar2, --FHR
531 p_short_code in varchar2, --FHR
532 p_legislation_code in varchar2,
533 p_legislation_subgroup in varchar2,
534 p_object_version_number in number
535 )
536 Return g_rec_type is
537 --
538 l_rec g_rec_type;
539 l_proc varchar2(72) := g_package||'convert_args';
540 --
541 Begin
542 --
543 hr_utility.set_location('Entering:'||l_proc, 5);
544 --
545 -- Convert arguments into local l_rec structure.
546 --
547 l_rec.pl_typ_id := p_pl_typ_id;
548 l_rec.effective_start_date := p_effective_start_date;
549 l_rec.effective_end_date := p_effective_end_date;
550 l_rec.name := p_name;
551 l_rec.mx_enrl_alwd_num := p_mx_enrl_alwd_num;
552 l_rec.mn_enrl_rqd_num := p_mn_enrl_rqd_num;
553 l_rec.pl_typ_stat_cd := p_pl_typ_stat_cd;
554 l_rec.opt_typ_cd := p_opt_typ_cd;
555 l_rec.opt_dsply_fmt_cd := p_opt_dsply_fmt_cd;
556 l_rec.comp_typ_cd := p_comp_typ_cd;
557 l_rec.ivr_ident := p_ivr_ident;
558 l_rec.no_mx_enrl_num_dfnd_flag := p_no_mx_enrl_num_dfnd_flag;
559 l_rec.no_mn_enrl_num_dfnd_flag := p_no_mn_enrl_num_dfnd_flag;
560 l_rec.business_group_id := p_business_group_id;
561 l_rec.ptp_attribute_category := p_ptp_attribute_category;
562 l_rec.ptp_attribute1 := p_ptp_attribute1;
563 l_rec.ptp_attribute2 := p_ptp_attribute2;
564 l_rec.ptp_attribute3 := p_ptp_attribute3;
565 l_rec.ptp_attribute4 := p_ptp_attribute4;
566 l_rec.ptp_attribute5 := p_ptp_attribute5;
567 l_rec.ptp_attribute6 := p_ptp_attribute6;
568 l_rec.ptp_attribute7 := p_ptp_attribute7;
569 l_rec.ptp_attribute8 := p_ptp_attribute8;
570 l_rec.ptp_attribute9 := p_ptp_attribute9;
571 l_rec.ptp_attribute10 := p_ptp_attribute10;
572 l_rec.ptp_attribute11 := p_ptp_attribute11;
573 l_rec.ptp_attribute12 := p_ptp_attribute12;
574 l_rec.ptp_attribute13 := p_ptp_attribute13;
575 l_rec.ptp_attribute14 := p_ptp_attribute14;
576 l_rec.ptp_attribute15 := p_ptp_attribute15;
577 l_rec.ptp_attribute16 := p_ptp_attribute16;
578 l_rec.ptp_attribute17 := p_ptp_attribute17;
579 l_rec.ptp_attribute18 := p_ptp_attribute18;
580 l_rec.ptp_attribute19 := p_ptp_attribute19;
581 l_rec.ptp_attribute20 := p_ptp_attribute20;
582 l_rec.ptp_attribute21 := p_ptp_attribute21;
583 l_rec.ptp_attribute22 := p_ptp_attribute22;
584 l_rec.ptp_attribute23 := p_ptp_attribute23;
585 l_rec.ptp_attribute24 := p_ptp_attribute24;
586 l_rec.ptp_attribute25 := p_ptp_attribute25;
587 l_rec.ptp_attribute26 := p_ptp_attribute26;
588 l_rec.ptp_attribute27 := p_ptp_attribute27;
589 l_rec.ptp_attribute28 := p_ptp_attribute28;
590 l_rec.ptp_attribute29 := p_ptp_attribute29;
591 l_rec.ptp_attribute30 := p_ptp_attribute30;
592 l_rec.short_name := p_short_name; --FHR
593 l_rec.short_code := p_short_code; --FHR
594 l_rec.legislation_code := p_legislation_code;
595 l_rec.legislation_subgroup := p_legislation_subgroup;
596 l_rec.object_version_number := p_object_version_number;
597 --
598 -- Return the plsql record structure.
599 --
600 hr_utility.set_location(' Leaving:'||l_proc, 10);
601 Return(l_rec);
602 --
603 End convert_args;
604 --
605 end ben_ptp_shd;