[Home] [Help]
PACKAGE BODY: APPS.BEN_DEC_SHD
Source
4 -- ----------------------------------------------------------------------------
1 Package Body ben_dec_shd as
2 /* $Header: bedecrhi.pkb 115.4 2002/12/10 15:17:38 bmanyam ship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_dec_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 --
36 If (p_constraint_name = 'BEN_DSGNTR_ENRLD_CVG_PK') Then
37 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
38 fnd_message.set_token('PROCEDURE', l_proc);
39 fnd_message.set_token('STEP','5');
40 fnd_message.raise_error;
41 Else
42 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
43 fnd_message.set_token('PROCEDURE', l_proc);
44 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
45 fnd_message.raise_error;
46 End If;
47 --
48 hr_utility.set_location(' Leaving:'||l_proc, 10);
49 End constraint_error;
50 --
51 -- ----------------------------------------------------------------------------
52 -- |-----------------------------< api_updating >-----------------------------|
53 -- ----------------------------------------------------------------------------
54 Function api_updating
55 (p_effective_date in date,
56 p_dsgntr_enrld_cvg_id in number,
57 p_object_version_number in number
58 ) Return Boolean Is
59 --
60 --
61 -- Cursor selects the 'current' row from the HR Schema
62 --
63 Cursor C_Sel1 is
64 select
65 dsgntr_enrld_cvg_id,
66 effective_start_date,
67 effective_end_date,
68 dsgntr_crntly_enrld_flag,
69 dpnt_cvg_eligy_prfl_id,
70 business_group_id,
71 dec_attribute_category,
72 dec_attribute1,
73 dec_attribute2,
74 dec_attribute3,
75 dec_attribute4,
76 dec_attribute5,
77 dec_attribute6,
78 dec_attribute7,
79 dec_attribute8,
80 dec_attribute9,
81 dec_attribute10,
82 dec_attribute11,
83 dec_attribute12,
84 dec_attribute13,
85 dec_attribute14,
86 dec_attribute15,
87 dec_attribute16,
88 dec_attribute17,
89 dec_attribute18,
90 dec_attribute19,
91 dec_attribute20,
92 dec_attribute21,
93 dec_attribute22,
94 dec_attribute23,
95 dec_attribute24,
96 dec_attribute25,
97 dec_attribute26,
98 dec_attribute27,
99 dec_attribute28,
100 dec_attribute29,
101 dec_attribute30,
102 object_version_number
103 from ben_dsgntr_enrld_cvg_f
104 where dsgntr_enrld_cvg_id = p_dsgntr_enrld_cvg_id
105 and p_effective_date
106 between effective_start_date and effective_end_date;
107 --
108 l_proc varchar2(72) := g_package||'api_updating';
109 l_fct_ret boolean;
110 --
111 Begin
112 hr_utility.set_location('Entering:'||l_proc, 5);
113 --
114 If (p_effective_date is null or
115 p_dsgntr_enrld_cvg_id is null or
116 p_object_version_number is null) Then
117 --
118 -- One of the primary key arguments is null therefore we must
119 -- set the returning function value to false
120 --
121 l_fct_ret := false;
122 Else
123 If (p_dsgntr_enrld_cvg_id = g_old_rec.dsgntr_enrld_cvg_id and
124 p_object_version_number = g_old_rec.object_version_number) Then
125 hr_utility.set_location(l_proc, 10);
126 --
127 -- The g_old_rec is current therefore we must
128 -- set the returning function to true
129 --
130 l_fct_ret := true;
131 Else
132 --
133 -- Select the current row
134 --
135 Open C_Sel1;
136 Fetch C_Sel1 Into g_old_rec;
137 If C_Sel1%notfound Then
138 Close C_Sel1;
142 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
139 --
140 -- The primary key is invalid therefore we must error
141 --
143 fnd_message.raise_error;
144 End If;
145 Close C_Sel1;
146 If (p_object_version_number <> 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 hr_utility.set_location(l_proc, 15);
151 l_fct_ret := true;
152 End If;
153 End If;
154 hr_utility.set_location(' Leaving:'||l_proc, 20);
155 Return (l_fct_ret);
156 --
157 End api_updating;
158 --
159 -- ----------------------------------------------------------------------------
160 -- |--------------------------< find_dt_del_modes >---------------------------|
161 -- ----------------------------------------------------------------------------
162 Procedure find_dt_del_modes
163 (p_effective_date in date,
164 p_base_key_value in number,
165 p_zap out nocopy boolean,
166 p_delete out nocopy boolean,
167 p_future_change out nocopy boolean,
168 p_delete_next_change out nocopy boolean) is
169 --
170 l_proc varchar2(72) := g_package||'find_dt_del_modes';
171 --
172 --
173 --
174 --
175 Begin
176 hr_utility.set_location('Entering:'||l_proc, 5);
177 --
178 --
179 -- Call the corresponding datetrack api
180 --
181 dt_api.find_dt_del_modes
182 (p_effective_date => p_effective_date,
183 p_base_table_name => 'ben_dsgntr_enrld_cvg_f',
184 p_base_key_column => 'dsgntr_enrld_cvg_id',
185 p_base_key_value => p_base_key_value,
186 p_zap => p_zap,
187 p_delete => p_delete,
188 p_future_change => p_future_change,
189 p_delete_next_change => p_delete_next_change);
190 --
191 hr_utility.set_location(' Leaving:'||l_proc, 10);
192 End find_dt_del_modes;
193 --
194 -- ----------------------------------------------------------------------------
195 -- |--------------------------< find_dt_upd_modes >---------------------------|
196 -- ----------------------------------------------------------------------------
197 Procedure find_dt_upd_modes
198 (p_effective_date in date,
199 p_base_key_value in number,
200 p_correction out nocopy boolean,
201 p_update out nocopy boolean,
202 p_update_override out nocopy boolean,
203 p_update_change_insert out nocopy boolean) is
204 --
205 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
206 --
207 Begin
208 hr_utility.set_location('Entering:'||l_proc, 5);
209 --
210 -- Call the corresponding datetrack api
211 --
212 dt_api.find_dt_upd_modes
213 (p_effective_date => p_effective_date,
214 p_base_table_name => 'ben_dsgntr_enrld_cvg_f',
215 p_base_key_column => 'dsgntr_enrld_cvg_id',
216 p_base_key_value => p_base_key_value,
217 p_correction => p_correction,
218 p_update => p_update,
219 p_update_override => p_update_override,
220 p_update_change_insert => p_update_change_insert);
221 --
222 hr_utility.set_location(' Leaving:'||l_proc, 10);
223 End find_dt_upd_modes;
224 --
225 -- ----------------------------------------------------------------------------
226 -- |------------------------< upd_effective_end_date >------------------------|
227 -- ----------------------------------------------------------------------------
228 Procedure upd_effective_end_date
229 (p_effective_date in date,
230 p_base_key_value in number,
231 p_new_effective_end_date in date,
232 p_validation_start_date in date,
233 p_validation_end_date in date,
234 p_object_version_number out nocopy number) 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 => 'ben_dsgntr_enrld_cvg_f',
248 p_base_key_column => 'dsgntr_enrld_cvg_id',
249 p_base_key_value => p_base_key_value);
250 --
251 hr_utility.set_location(l_proc, 10);
252 g_api_dml := true; -- Set the api dml status
253 --
254 -- Update the specified datetrack row setting the effective
255 -- end date to the specified new effective end date.
256 --
257 update ben_dsgntr_enrld_cvg_f t
261 and p_effective_date
258 set t.effective_end_date = p_new_effective_end_date,
259 t.object_version_number = l_object_version_number
260 where t.dsgntr_enrld_cvg_id = p_base_key_value
262 between t.effective_start_date and t.effective_end_date;
263 --
264 g_api_dml := false; -- Unset the api dml status
265 p_object_version_number := l_object_version_number;
266 hr_utility.set_location(' Leaving:'||l_proc, 15);
267 --
268 Exception
269 When Others Then
270 g_api_dml := false; -- Unset the api dml status
271 Raise;
272 End upd_effective_end_date;
273 --
274 -- ----------------------------------------------------------------------------
275 -- |---------------------------------< lck >----------------------------------|
276 -- ----------------------------------------------------------------------------
277 Procedure lck
278 (p_effective_date in date,
279 p_datetrack_mode in varchar2,
280 p_dsgntr_enrld_cvg_id in number,
281 p_object_version_number in number,
282 p_validation_start_date out nocopy date,
283 p_validation_end_date out nocopy date) is
284 --
285 l_proc varchar2(72) := g_package||'lck';
286 l_validation_start_date date;
287 l_validation_end_date date;
288 l_object_invalid exception;
289 l_argument varchar2(30);
290 --
291 -- Cursor C_Sel1 selects the current locked row as of session date
292 -- ensuring that the object version numbers match.
293 --
294 Cursor C_Sel1 is
295 select
296 dsgntr_enrld_cvg_id,
297 effective_start_date,
298 effective_end_date,
299 dsgntr_crntly_enrld_flag,
300 dpnt_cvg_eligy_prfl_id,
301 business_group_id,
302 dec_attribute_category,
303 dec_attribute1,
304 dec_attribute2,
305 dec_attribute3,
306 dec_attribute4,
307 dec_attribute5,
308 dec_attribute6,
309 dec_attribute7,
310 dec_attribute8,
311 dec_attribute9,
312 dec_attribute10,
313 dec_attribute11,
314 dec_attribute12,
315 dec_attribute13,
316 dec_attribute14,
317 dec_attribute15,
318 dec_attribute16,
319 dec_attribute17,
320 dec_attribute18,
321 dec_attribute19,
322 dec_attribute20,
323 dec_attribute21,
324 dec_attribute22,
325 dec_attribute23,
326 dec_attribute24,
327 dec_attribute25,
328 dec_attribute26,
329 dec_attribute27,
330 dec_attribute28,
331 dec_attribute29,
332 dec_attribute30,
333 object_version_number
334 from ben_dsgntr_enrld_cvg_f
335 where dsgntr_enrld_cvg_id = p_dsgntr_enrld_cvg_id
336 and p_effective_date
337 between effective_start_date and effective_end_date
338 for update nowait;
339 --
340 --
341 --
342 Begin
343 hr_utility.set_location('Entering:'||l_proc, 5);
344 --
345 -- Ensure that all the mandatory arguments are not null
346 --
347 hr_api.mandatory_arg_error(p_api_name => l_proc,
348 p_argument => 'effective_date',
349 p_argument_value => p_effective_date);
350 --
351 hr_api.mandatory_arg_error(p_api_name => l_proc,
352 p_argument => 'datetrack_mode',
353 p_argument_value => p_datetrack_mode);
354 --
355 hr_api.mandatory_arg_error(p_api_name => l_proc,
356 p_argument => 'dsgntr_enrld_cvg_id',
357 p_argument_value => p_dsgntr_enrld_cvg_id);
358 --
359 hr_api.mandatory_arg_error(p_api_name => l_proc,
360 p_argument => 'object_version_number',
361 p_argument_value => p_object_version_number);
362 --
363 -- Check to ensure the datetrack mode is not INSERT.
364 --
365 If (p_datetrack_mode <> 'INSERT') then
366 --
367 -- We must select and lock the current row.
368 --
369 Open C_Sel1;
370 Fetch C_Sel1 Into g_old_rec;
371 If C_Sel1%notfound then
372 Close C_Sel1;
373 --
374 -- The primary key is invalid therefore we must error
375 --
376 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
377 fnd_message.raise_error;
378 End If;
379 Close C_Sel1;
380 If (p_object_version_number <> g_old_rec.object_version_number) Then
381 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
382 fnd_message.raise_error;
383 End If;
384 hr_utility.set_location(l_proc, 15);
385 --
386 --
387 -- Validate the datetrack mode mode getting the validation start
388 -- and end dates for the specified datetrack operation.
389 --
390 dt_api.validate_dt_mode
391 (p_effective_date => p_effective_date,
392 p_datetrack_mode => p_datetrack_mode,
393 p_base_table_name => 'ben_dsgntr_enrld_cvg_f',
394 p_base_key_column => 'dsgntr_enrld_cvg_id',
395 p_base_key_value => p_dsgntr_enrld_cvg_id,
396
397 p_enforce_foreign_locking => true,
398 p_validation_start_date => l_validation_start_date,
399 p_validation_end_date => l_validation_end_date);
400 Else
401 --
402 -- We are doing a datetrack 'INSERT' which is illegal within this
403 -- procedure therefore we must error (note: to lck on insert the
404 -- private procedure ins_lck should be called).
405 --
406 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
407 fnd_message.set_token('PROCEDURE', l_proc);
408 fnd_message.set_token('STEP','20');
409 fnd_message.raise_error;
410 End If;
411 --
412 -- Set the validation start and end date OUT arguments
416 --
413 --
414 p_validation_start_date := l_validation_start_date;
415 p_validation_end_date := l_validation_end_date;
417 hr_utility.set_location(' Leaving:'||l_proc, 30);
418 --
419 -- We need to trap the ORA LOCK exception
420 --
421 Exception
422 When HR_Api.Object_Locked then
423 --
424 -- The object is locked therefore we need to supply a meaningful
425 -- error message.
426 --
427 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
428 fnd_message.set_token('TABLE_NAME', 'ben_dsgntr_enrld_cvg_f');
429 fnd_message.raise_error;
430 When l_object_invalid then
431 --
432 -- The object doesn't exist or is invalid
433 --
434 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
435 fnd_message.set_token('TABLE_NAME', 'ben_dsgntr_enrld_cvg_f');
436 fnd_message.raise_error;
437 End lck;
438 --
439 -- ----------------------------------------------------------------------------
440 -- |-----------------------------< convert_args >-----------------------------|
441 -- ----------------------------------------------------------------------------
442 Function convert_args
443 (
444 p_dsgntr_enrld_cvg_id in number,
445 p_effective_start_date in date,
446 p_effective_end_date in date,
447 p_dsgntr_crntly_enrld_flag in varchar2,
448 p_dpnt_cvg_eligy_prfl_id in number,
449 p_business_group_id in number,
450 p_dec_attribute_category in varchar2,
451 p_dec_attribute1 in varchar2,
452 p_dec_attribute2 in varchar2,
453 p_dec_attribute3 in varchar2,
454 p_dec_attribute4 in varchar2,
455 p_dec_attribute5 in varchar2,
456 p_dec_attribute6 in varchar2,
457 p_dec_attribute7 in varchar2,
458 p_dec_attribute8 in varchar2,
459 p_dec_attribute9 in varchar2,
460 p_dec_attribute10 in varchar2,
461 p_dec_attribute11 in varchar2,
462 p_dec_attribute12 in varchar2,
463 p_dec_attribute13 in varchar2,
464 p_dec_attribute14 in varchar2,
465 p_dec_attribute15 in varchar2,
466 p_dec_attribute16 in varchar2,
467 p_dec_attribute17 in varchar2,
468 p_dec_attribute18 in varchar2,
469 p_dec_attribute19 in varchar2,
470 p_dec_attribute20 in varchar2,
471 p_dec_attribute21 in varchar2,
472 p_dec_attribute22 in varchar2,
473 p_dec_attribute23 in varchar2,
474 p_dec_attribute24 in varchar2,
475 p_dec_attribute25 in varchar2,
476 p_dec_attribute26 in varchar2,
477 p_dec_attribute27 in varchar2,
478 p_dec_attribute28 in varchar2,
479 p_dec_attribute29 in varchar2,
480 p_dec_attribute30 in varchar2,
481 p_object_version_number in number
482 )
483 Return g_rec_type is
484 --
485 l_rec g_rec_type;
486 l_proc varchar2(72) := g_package||'convert_args';
487 --
488 Begin
489 --
490 hr_utility.set_location('Entering:'||l_proc, 5);
491 --
492 -- Convert arguments into local l_rec structure.
493 --
494 l_rec.dsgntr_enrld_cvg_id := p_dsgntr_enrld_cvg_id;
495 l_rec.effective_start_date := p_effective_start_date;
496 l_rec.effective_end_date := p_effective_end_date;
497 l_rec.dsgntr_crntly_enrld_flag := p_dsgntr_crntly_enrld_flag;
498 l_rec.dpnt_cvg_eligy_prfl_id := p_dpnt_cvg_eligy_prfl_id;
499 l_rec.business_group_id := p_business_group_id;
500 l_rec.dec_attribute_category := p_dec_attribute_category;
501 l_rec.dec_attribute1 := p_dec_attribute1;
502 l_rec.dec_attribute2 := p_dec_attribute2;
503 l_rec.dec_attribute3 := p_dec_attribute3;
504 l_rec.dec_attribute4 := p_dec_attribute4;
505 l_rec.dec_attribute5 := p_dec_attribute5;
506 l_rec.dec_attribute6 := p_dec_attribute6;
507 l_rec.dec_attribute7 := p_dec_attribute7;
508 l_rec.dec_attribute8 := p_dec_attribute8;
509 l_rec.dec_attribute9 := p_dec_attribute9;
510 l_rec.dec_attribute10 := p_dec_attribute10;
511 l_rec.dec_attribute11 := p_dec_attribute11;
512 l_rec.dec_attribute12 := p_dec_attribute12;
513 l_rec.dec_attribute13 := p_dec_attribute13;
514 l_rec.dec_attribute14 := p_dec_attribute14;
515 l_rec.dec_attribute15 := p_dec_attribute15;
516 l_rec.dec_attribute16 := p_dec_attribute16;
517 l_rec.dec_attribute17 := p_dec_attribute17;
518 l_rec.dec_attribute18 := p_dec_attribute18;
519 l_rec.dec_attribute19 := p_dec_attribute19;
520 l_rec.dec_attribute20 := p_dec_attribute20;
521 l_rec.dec_attribute21 := p_dec_attribute21;
522 l_rec.dec_attribute22 := p_dec_attribute22;
523 l_rec.dec_attribute23 := p_dec_attribute23;
524 l_rec.dec_attribute24 := p_dec_attribute24;
525 l_rec.dec_attribute25 := p_dec_attribute25;
526 l_rec.dec_attribute26 := p_dec_attribute26;
527 l_rec.dec_attribute27 := p_dec_attribute27;
528 l_rec.dec_attribute28 := p_dec_attribute28;
529 l_rec.dec_attribute29 := p_dec_attribute29;
530 l_rec.dec_attribute30 := p_dec_attribute30;
531 l_rec.object_version_number := p_object_version_number;
532 --
533 -- Return the plsql record structure.
537 --
534 --
535 hr_utility.set_location(' Leaving:'||l_proc, 10);
536 Return(l_rec);
538 End convert_args;
539 --
540 end ben_dec_shd;