[Home] [Help]
PACKAGE BODY: APPS.BEN_NOC_SHD
Source
4 -- ----------------------------------------------------------------------------
1 Package Body ben_noc_shd as
2 /* $Header: benocrhi.pkb 120.0 2005/05/28 09:10:24 appldev noship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_noc_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_NO_OTHR_CVG_RT_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 --
51 -- ----------------------------------------------------------------------------
48 hr_utility.set_location(' Leaving:'||l_proc, 10);
49 End constraint_error;
50 --
52 -- |-----------------------------< api_updating >-----------------------------|
53 -- ----------------------------------------------------------------------------
54 Function api_updating
55 (p_effective_date in date,
56 p_no_othr_cvg_rt_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 no_othr_cvg_rt_id,
66 effective_start_date,
67 effective_end_date,
68 coord_ben_no_cvg_flag,
69 vrbl_rt_prfl_id,
70 business_group_id,
71 noc_attribute_category,
72 noc_attribute1,
73 noc_attribute2,
74 noc_attribute3,
75 noc_attribute4,
76 noc_attribute5,
77 noc_attribute6,
78 noc_attribute7,
79 noc_attribute8,
80 noc_attribute9,
81 noc_attribute10,
82 noc_attribute11,
83 noc_attribute12,
84 noc_attribute13,
85 noc_attribute14,
86 noc_attribute15,
87 noc_attribute16,
88 noc_attribute17,
89 noc_attribute18,
90 noc_attribute19,
91 noc_attribute20,
92 noc_attribute21,
93 noc_attribute22,
94 noc_attribute23,
95 noc_attribute24,
96 noc_attribute25,
97 noc_attribute26,
98 noc_attribute27,
99 noc_attribute28,
100 noc_attribute29,
101 noc_attribute30,
102 object_version_number
103 from ben_no_othr_cvg_rt_f
104 where no_othr_cvg_rt_id = p_no_othr_cvg_rt_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_no_othr_cvg_rt_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_no_othr_cvg_rt_id = g_old_rec.no_othr_cvg_rt_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;
139 --
140 -- The primary key is invalid therefore we must error
141 --
142 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
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 l_parent_key_value1 number;
173 --
174 Cursor C_Sel1 Is
175 select t.vrbl_rt_prfl_id
176 from ben_no_othr_cvg_rt_f t
177 where t.no_othr_cvg_rt_id = p_base_key_value
178 and p_effective_date
179 between t.effective_start_date and t.effective_end_date;
180 --
181 Begin
182 hr_utility.set_location('Entering:'||l_proc, 5);
183 Open C_Sel1;
184 Fetch C_Sel1 Into l_parent_key_value1;
185 If C_Sel1%notfound then
186 Close C_Sel1;
187 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
188 fnd_message.set_token('PROCEDURE', l_proc);
189 fnd_message.set_token('STEP','10');
190 fnd_message.raise_error;
191 End If;
192 Close C_Sel1;
193 --
194 -- Call the corresponding datetrack api
195 --
196 dt_api.find_dt_del_modes
197 (p_effective_date => p_effective_date,
198 p_base_table_name => 'ben_no_othr_cvg_rt_f',
199 p_base_key_column => 'no_othr_cvg_rt_id',
200 p_base_key_value => p_base_key_value,
201 p_parent_table_name1 => 'ben_vrbl_rt_prfl_f',
202 p_parent_key_column1 => 'vrbl_rt_prfl_id',
203 p_parent_key_value1 => l_parent_key_value1,
204 p_zap => p_zap,
205 p_delete => p_delete,
206 p_future_change => p_future_change,
207 p_delete_next_change => p_delete_next_change);
208 --
209 hr_utility.set_location(' Leaving:'||l_proc, 10);
210 End find_dt_del_modes;
211 --
212 -- ----------------------------------------------------------------------------
213 -- |--------------------------< find_dt_upd_modes >---------------------------|
214 -- ----------------------------------------------------------------------------
215 Procedure find_dt_upd_modes
216 (p_effective_date in date,
217 p_base_key_value in number,
218 p_correction out nocopy boolean,
219 p_update out nocopy boolean,
220 p_update_override out nocopy boolean,
221 p_update_change_insert out nocopy boolean) is
222 --
223 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
224 --
225 Begin
226 hr_utility.set_location('Entering:'||l_proc, 5);
227 --
228 -- Call the corresponding datetrack api
229 --
230 dt_api.find_dt_upd_modes
231 (p_effective_date => p_effective_date,
232 p_base_table_name => 'ben_no_othr_cvg_rt_f',
233 p_base_key_column => 'no_othr_cvg_rt_id',
234 p_base_key_value => p_base_key_value,
235 p_correction => p_correction,
236 p_update => p_update,
237 p_update_override => p_update_override,
238 p_update_change_insert => p_update_change_insert);
239 --
240 hr_utility.set_location(' Leaving:'||l_proc, 10);
241 End find_dt_upd_modes;
242 --
243 -- ----------------------------------------------------------------------------
244 -- |------------------------< upd_effective_end_date >------------------------|
245 -- ----------------------------------------------------------------------------
246 Procedure upd_effective_end_date
247 (p_effective_date in date,
248 p_base_key_value in number,
249 p_new_effective_end_date in date,
250 p_validation_start_date in date,
251 p_validation_end_date in date,
252 p_object_version_number out nocopy number) is
253 --
254 l_proc varchar2(72) := g_package||'upd_effective_end_date';
255 l_object_version_number number;
256 --
257 Begin
258 hr_utility.set_location('Entering:'||l_proc, 5);
259 --
260 -- Because we are updating a row we must get the next object
261 -- version number.
262 --
263 l_object_version_number :=
264 dt_api.get_object_version_number
265 (p_base_table_name => 'ben_no_othr_cvg_rt_f',
266 p_base_key_column => 'no_othr_cvg_rt_id',
267 p_base_key_value => p_base_key_value);
268 --
269 hr_utility.set_location(l_proc, 10);
270 g_api_dml := true; -- Set the api dml status
271 --
272 -- Update the specified datetrack row setting the effective
273 -- end date to the specified new effective end date.
274 --
275 update ben_no_othr_cvg_rt_f t
276 set t.effective_end_date = p_new_effective_end_date,
277 t.object_version_number = l_object_version_number
278 where t.no_othr_cvg_rt_id = p_base_key_value
279 and p_effective_date
280 between t.effective_start_date and t.effective_end_date;
281 --
282 g_api_dml := false; -- Unset the api dml status
283 p_object_version_number := l_object_version_number;
284 hr_utility.set_location(' Leaving:'||l_proc, 15);
285 --
286 Exception
287 When Others Then
288 g_api_dml := false; -- Unset the api dml status
289 Raise;
290 End upd_effective_end_date;
291 --
292 -- ----------------------------------------------------------------------------
293 -- |---------------------------------< lck >----------------------------------|
294 -- ----------------------------------------------------------------------------
295 Procedure lck
296 (p_effective_date in date,
297 p_datetrack_mode in varchar2,
298 p_no_othr_cvg_rt_id in number,
299 p_object_version_number in number,
300 p_validation_start_date out nocopy date,
301 p_validation_end_date out nocopy date) is
302 --
303 l_proc varchar2(72) := g_package||'lck';
304 l_validation_start_date date;
305 l_validation_end_date date;
306 l_object_invalid exception;
307 l_argument varchar2(30);
308 --
309 -- Cursor C_Sel1 selects the current locked row as of session date
310 -- ensuring that the object version numbers match.
311 --
312 Cursor C_Sel1 is
313 select
314 no_othr_cvg_rt_id,
315 effective_start_date,
316 effective_end_date,
317 coord_ben_no_cvg_flag,
318 vrbl_rt_prfl_id,
319 business_group_id,
320 noc_attribute_category,
321 noc_attribute1,
322 noc_attribute2,
323 noc_attribute3,
324 noc_attribute4,
325 noc_attribute5,
326 noc_attribute6,
327 noc_attribute7,
328 noc_attribute8,
329 noc_attribute9,
330 noc_attribute10,
331 noc_attribute11,
332 noc_attribute12,
333 noc_attribute13,
334 noc_attribute14,
335 noc_attribute15,
336 noc_attribute16,
337 noc_attribute17,
338 noc_attribute18,
339 noc_attribute19,
340 noc_attribute20,
341 noc_attribute21,
342 noc_attribute22,
343 noc_attribute23,
344 noc_attribute24,
345 noc_attribute25,
346 noc_attribute26,
347 noc_attribute27,
348 noc_attribute28,
349 noc_attribute29,
350 noc_attribute30,
351 object_version_number
352 from ben_no_othr_cvg_rt_f
353 where no_othr_cvg_rt_id = p_no_othr_cvg_rt_id
354 and p_effective_date
355 between effective_start_date and effective_end_date
356 for update nowait;
357 --
358 --
359 --
360 Begin
361 hr_utility.set_location('Entering:'||l_proc, 5);
362 --
363 -- Ensure that all the mandatory arguments are not null
364 --
365 hr_api.mandatory_arg_error(p_api_name => l_proc,
366 p_argument => 'effective_date',
367 p_argument_value => p_effective_date);
368 --
369 hr_api.mandatory_arg_error(p_api_name => l_proc,
370 p_argument => 'datetrack_mode',
371 p_argument_value => p_datetrack_mode);
372 --
373 hr_api.mandatory_arg_error(p_api_name => l_proc,
374 p_argument => 'no_othr_cvg_rt_id',
375 p_argument_value => p_no_othr_cvg_rt_id);
376 --
377 hr_api.mandatory_arg_error(p_api_name => l_proc,
378 p_argument => 'object_version_number',
379 p_argument_value => p_object_version_number);
380 --
381 -- Check to ensure the datetrack mode is not INSERT.
382 --
383 If (p_datetrack_mode <> 'INSERT') then
384 --
385 -- We must select and lock the current row.
386 --
387 Open C_Sel1;
388 Fetch C_Sel1 Into g_old_rec;
389 If C_Sel1%notfound then
390 Close C_Sel1;
391 --
392 -- The primary key is invalid therefore we must error
393 --
394 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
395 fnd_message.raise_error;
396 End If;
397 Close C_Sel1;
398 If (p_object_version_number <> g_old_rec.object_version_number) Then
399 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
400 fnd_message.raise_error;
401 End If;
402 hr_utility.set_location(l_proc, 15);
403 --
404 --
405 -- Validate the datetrack mode mode getting the validation start
406 -- and end dates for the specified datetrack operation.
407 --
408 dt_api.validate_dt_mode
409 (p_effective_date => p_effective_date,
410 p_datetrack_mode => p_datetrack_mode,
411 p_base_table_name => 'ben_no_othr_cvg_rt_f',
412 p_base_key_column => 'no_othr_cvg_rt_id',
413 p_base_key_value => p_no_othr_cvg_rt_id,
414 p_parent_table_name1 => 'ben_vrbl_rt_prfl_f',
415 p_parent_key_column1 => 'vrbl_rt_prfl_id',
416 p_parent_key_value1 => g_old_rec.vrbl_rt_prfl_id,
417 p_enforce_foreign_locking => true,
418 p_validation_start_date => l_validation_start_date,
419 p_validation_end_date => l_validation_end_date);
420 Else
421 --
422 -- We are doing a datetrack 'INSERT' which is illegal within this
423 -- procedure therefore we must error (note: to lck on insert the
424 -- private procedure ins_lck should be called).
425 --
426 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
427 fnd_message.set_token('PROCEDURE', l_proc);
431 --
428 fnd_message.set_token('STEP','20');
429 fnd_message.raise_error;
430 End If;
432 -- Set the validation start and end date OUT arguments
433 --
434 p_validation_start_date := l_validation_start_date;
435 p_validation_end_date := l_validation_end_date;
436 --
437 hr_utility.set_location(' Leaving:'||l_proc, 30);
438 --
439 -- We need to trap the ORA LOCK exception
440 --
441 Exception
442 When HR_Api.Object_Locked then
443 --
444 -- The object is locked therefore we need to supply a meaningful
445 -- error message.
446 --
447 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
448 fnd_message.set_token('TABLE_NAME', 'ben_no_othr_cvg_rt_f');
449 fnd_message.raise_error;
450 When l_object_invalid then
451 --
452 -- The object doesn't exist or is invalid
453 --
454 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
455 fnd_message.set_token('TABLE_NAME', 'ben_no_othr_cvg_rt_f');
456 fnd_message.raise_error;
457 End lck;
458 --
459 -- ----------------------------------------------------------------------------
460 -- |-----------------------------< convert_args >-----------------------------|
461 -- ----------------------------------------------------------------------------
462 Function convert_args
463 (
464 p_no_othr_cvg_rt_id in number,
465 p_effective_start_date in date,
466 p_effective_end_date in date,
467 p_coord_ben_no_cvg_flag in varchar2,
468 p_vrbl_rt_prfl_id in number,
469 p_business_group_id in number,
470 p_noc_attribute_category in varchar2,
471 p_noc_attribute1 in varchar2,
472 p_noc_attribute2 in varchar2,
473 p_noc_attribute3 in varchar2,
474 p_noc_attribute4 in varchar2,
475 p_noc_attribute5 in varchar2,
476 p_noc_attribute6 in varchar2,
477 p_noc_attribute7 in varchar2,
478 p_noc_attribute8 in varchar2,
479 p_noc_attribute9 in varchar2,
480 p_noc_attribute10 in varchar2,
481 p_noc_attribute11 in varchar2,
482 p_noc_attribute12 in varchar2,
483 p_noc_attribute13 in varchar2,
484 p_noc_attribute14 in varchar2,
485 p_noc_attribute15 in varchar2,
486 p_noc_attribute16 in varchar2,
487 p_noc_attribute17 in varchar2,
488 p_noc_attribute18 in varchar2,
489 p_noc_attribute19 in varchar2,
490 p_noc_attribute20 in varchar2,
491 p_noc_attribute21 in varchar2,
492 p_noc_attribute22 in varchar2,
493 p_noc_attribute23 in varchar2,
494 p_noc_attribute24 in varchar2,
495 p_noc_attribute25 in varchar2,
496 p_noc_attribute26 in varchar2,
497 p_noc_attribute27 in varchar2,
498 p_noc_attribute28 in varchar2,
499 p_noc_attribute29 in varchar2,
500 p_noc_attribute30 in varchar2,
501 p_object_version_number in number
502 )
503 Return g_rec_type is
504 --
505 l_rec g_rec_type;
506 l_proc varchar2(72) := g_package||'convert_args';
507 --
508 Begin
509 --
510 hr_utility.set_location('Entering:'||l_proc, 5);
511 --
512 -- Convert arguments into local l_rec structure.
513 --
514 l_rec.no_othr_cvg_rt_id := p_no_othr_cvg_rt_id;
515 l_rec.effective_start_date := p_effective_start_date;
516 l_rec.effective_end_date := p_effective_end_date;
517 l_rec.coord_ben_no_cvg_flag := p_coord_ben_no_cvg_flag;
518 l_rec.vrbl_rt_prfl_id := p_vrbl_rt_prfl_id;
519 l_rec.business_group_id := p_business_group_id;
520 l_rec.noc_attribute_category := p_noc_attribute_category;
521 l_rec.noc_attribute1 := p_noc_attribute1;
522 l_rec.noc_attribute2 := p_noc_attribute2;
523 l_rec.noc_attribute3 := p_noc_attribute3;
524 l_rec.noc_attribute4 := p_noc_attribute4;
525 l_rec.noc_attribute5 := p_noc_attribute5;
526 l_rec.noc_attribute6 := p_noc_attribute6;
527 l_rec.noc_attribute7 := p_noc_attribute7;
528 l_rec.noc_attribute8 := p_noc_attribute8;
529 l_rec.noc_attribute9 := p_noc_attribute9;
530 l_rec.noc_attribute10 := p_noc_attribute10;
531 l_rec.noc_attribute11 := p_noc_attribute11;
532 l_rec.noc_attribute12 := p_noc_attribute12;
533 l_rec.noc_attribute13 := p_noc_attribute13;
534 l_rec.noc_attribute14 := p_noc_attribute14;
535 l_rec.noc_attribute15 := p_noc_attribute15;
536 l_rec.noc_attribute16 := p_noc_attribute16;
537 l_rec.noc_attribute17 := p_noc_attribute17;
538 l_rec.noc_attribute18 := p_noc_attribute18;
539 l_rec.noc_attribute19 := p_noc_attribute19;
540 l_rec.noc_attribute20 := p_noc_attribute20;
541 l_rec.noc_attribute21 := p_noc_attribute21;
542 l_rec.noc_attribute22 := p_noc_attribute22;
543 l_rec.noc_attribute23 := p_noc_attribute23;
544 l_rec.noc_attribute24 := p_noc_attribute24;
545 l_rec.noc_attribute25 := p_noc_attribute25;
546 l_rec.noc_attribute26 := p_noc_attribute26;
547 l_rec.noc_attribute27 := p_noc_attribute27;
548 l_rec.noc_attribute28 := p_noc_attribute28;
549 l_rec.noc_attribute29 := p_noc_attribute29;
550 l_rec.noc_attribute30 := p_noc_attribute30;
551 l_rec.object_version_number := p_object_version_number;
552 --
553 -- Return the plsql record structure.
554 --
555 hr_utility.set_location(' Leaving:'||l_proc, 10);
556 Return(l_rec);
557 --
558 End convert_args;
559 --
560 end ben_noc_shd;