[Home] [Help]
PACKAGE BODY: APPS.BEN_SVA_SHD
Source
4 -- ----------------------------------------------------------------------------
1 Package Body ben_sva_shd as
2 /* $Header: besvarhi.pkb 120.1 2008/02/05 08:24:00 rtagarra noship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_sva_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_SVC_AREA_F_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_svc_area_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 svc_area_id,
66 effective_start_date,
67 effective_end_date,
68 name,
69 org_unit_prdct,
70 business_group_id,
71 sva_attribute_category,
72 sva_attribute1,
73 sva_attribute2,
74 sva_attribute3,
75 sva_attribute4,
76 sva_attribute5,
77 sva_attribute6,
78 sva_attribute7,
79 sva_attribute8,
80 sva_attribute9,
81 sva_attribute10,
82 sva_attribute11,
83 sva_attribute12,
84 sva_attribute13,
85 sva_attribute14,
86 sva_attribute15,
87 sva_attribute16,
88 sva_attribute17,
89 sva_attribute18,
90 sva_attribute19,
91 sva_attribute20,
92 sva_attribute21,
93 sva_attribute22,
97 sva_attribute26,
94 sva_attribute23,
95 sva_attribute24,
96 sva_attribute25,
98 sva_attribute27,
99 sva_attribute28,
100 sva_attribute29,
101 sva_attribute30,
102 object_version_number
103 from ben_svc_area_f
104 where svc_area_id = p_svc_area_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_svc_area_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_svc_area_id = g_old_rec.svc_area_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 Begin
173 hr_utility.set_location('Entering:'||l_proc, 5);
174 --
175 -- Call the corresponding datetrack api
176 --
177 dt_api.find_dt_del_modes
178 (p_effective_date => p_effective_date,
179 p_base_table_name => 'ben_svc_area_f',
180 p_base_key_column => 'svc_area_id',
181 p_base_key_value => p_base_key_value,
182 p_zap => p_zap,
183 p_delete => p_delete,
184 p_future_change => p_future_change,
185 p_delete_next_change => p_delete_next_change);
186 --
187 hr_utility.set_location(' Leaving:'||l_proc, 10);
188 End find_dt_del_modes;
189 --
193 Procedure find_dt_upd_modes
190 -- ----------------------------------------------------------------------------
191 -- |--------------------------< find_dt_upd_modes >---------------------------|
192 -- ----------------------------------------------------------------------------
194 (p_effective_date in date,
198 p_update_override out nocopy boolean,
195 p_base_key_value in number,
196 p_correction out nocopy boolean,
197 p_update out nocopy boolean,
199 p_update_change_insert out nocopy boolean) is
200 --
201 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
202 --
203 Begin
204 hr_utility.set_location('Entering:'||l_proc, 5);
205 --
206 -- Call the corresponding datetrack api
207 --
208 dt_api.find_dt_upd_modes
209 (p_effective_date => p_effective_date,
210 p_base_table_name => 'ben_svc_area_f',
211 p_base_key_column => 'svc_area_id',
212 p_base_key_value => p_base_key_value,
213 p_correction => p_correction,
214 p_update => p_update,
215 p_update_override => p_update_override,
216 p_update_change_insert => p_update_change_insert);
217 --
218 hr_utility.set_location(' Leaving:'||l_proc, 10);
219 End find_dt_upd_modes;
220 --
221 -- ----------------------------------------------------------------------------
222 -- |------------------------< upd_effective_end_date >------------------------|
223 -- ----------------------------------------------------------------------------
224 Procedure upd_effective_end_date
225 (p_effective_date in date,
226 p_base_key_value in number,
227 p_new_effective_end_date in date,
228 p_validation_start_date in date,
229 p_validation_end_date in date,
230 p_object_version_number out nocopy number) is
231 --
232 l_proc varchar2(72) := g_package||'upd_effective_end_date';
233 l_object_version_number number;
234 --
235 Begin
236 hr_utility.set_location('Entering:'||l_proc, 5);
237 --
238 -- Because we are updating a row we must get the next object
239 -- version number.
240 --
241 l_object_version_number :=
242 dt_api.get_object_version_number
243 (p_base_table_name => 'ben_svc_area_f',
244 p_base_key_column => 'svc_area_id',
245 p_base_key_value => p_base_key_value);
246 --
247 hr_utility.set_location(l_proc, 10);
248 g_api_dml := true; -- Set the api dml status
249 --
250 -- Update the specified datetrack row setting the effective
251 -- end date to the specified new effective end date.
252 --
253 update ben_svc_area_f t
254 set t.effective_end_date = p_new_effective_end_date,
255 t.object_version_number = l_object_version_number
256 where t.svc_area_id = p_base_key_value
257 and p_effective_date
258 between t.effective_start_date and t.effective_end_date;
259 --
260 g_api_dml := false; -- Unset the api dml status
261 p_object_version_number := l_object_version_number;
262 hr_utility.set_location(' Leaving:'||l_proc, 15);
263 --
264 Exception
265 When Others Then
266 g_api_dml := false; -- Unset the api dml status
267 Raise;
268 End upd_effective_end_date;
269 --
270 -- ----------------------------------------------------------------------------
271 -- |---------------------------------< lck >----------------------------------|
272 -- ----------------------------------------------------------------------------
273 Procedure lck
274 (p_effective_date in date,
275 p_datetrack_mode in varchar2,
276 p_svc_area_id in number,
277 p_object_version_number in number,
278 p_validation_start_date out nocopy date,
279 p_validation_end_date out nocopy date) is
280 --
281 l_proc varchar2(72) := g_package||'lck';
282 l_validation_start_date date;
283 l_validation_end_date date;
284 l_object_invalid exception;
285 l_argument varchar2(30);
286 --
287 -- Cursor C_Sel1 selects the current locked row as of session date
288 -- ensuring that the object version numbers match.
289 --
290 Cursor C_Sel1 is
291 select
292 svc_area_id,
293 effective_start_date,
294 effective_end_date,
295 name,
296 org_unit_prdct,
297 business_group_id,
298 sva_attribute_category,
299 sva_attribute1,
300 sva_attribute2,
301 sva_attribute3,
302 sva_attribute4,
303 sva_attribute5,
304 sva_attribute6,
305 sva_attribute7,
306 sva_attribute8,
307 sva_attribute9,
308 sva_attribute10,
309 sva_attribute11,
310 sva_attribute12,
311 sva_attribute13,
312 sva_attribute14,
313 sva_attribute15,
314 sva_attribute16,
315 sva_attribute17,
316 sva_attribute18,
317 sva_attribute19,
318 sva_attribute20,
319 sva_attribute21,
320 sva_attribute22,
321 sva_attribute23,
322 sva_attribute24,
323 sva_attribute25,
324 sva_attribute26,
325 sva_attribute27,
326 sva_attribute28,
327 sva_attribute29,
328 sva_attribute30,
329 object_version_number
330 from ben_svc_area_f
331 where svc_area_id = p_svc_area_id
332 and p_effective_date
333 between effective_start_date and effective_end_date
334 for update nowait;
335 --
336 --
337 --
338 Begin
339 hr_utility.set_location('Entering:'||l_proc, 5);
340 --
341 -- Ensure that all the mandatory arguments are not null
342 --
343 hr_api.mandatory_arg_error(p_api_name => l_proc,
344 p_argument => 'effective_date',
345 p_argument_value => p_effective_date);
346 --
347 hr_api.mandatory_arg_error(p_api_name => l_proc,
348 p_argument => 'datetrack_mode',
352 p_argument => 'svc_area_id',
349 p_argument_value => p_datetrack_mode);
350 --
351 hr_api.mandatory_arg_error(p_api_name => l_proc,
353 p_argument_value => p_svc_area_id);
354 --
355 hr_api.mandatory_arg_error(p_api_name => l_proc,
356 p_argument => 'object_version_number',
357 p_argument_value => p_object_version_number);
358 --
359 -- Check to ensure the datetrack mode is not INSERT.
360 --
361 If (p_datetrack_mode <> 'INSERT') then
362 --
363 -- We must select and lock the current row.
364 --
365 Open C_Sel1;
366 Fetch C_Sel1 Into g_old_rec;
367 If C_Sel1%notfound then
368 Close C_Sel1;
369 --
370 -- The primary key is invalid therefore we must error
371 --
375 Close C_Sel1;
372 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
373 fnd_message.raise_error;
374 End If;
376 If (p_object_version_number <> g_old_rec.object_version_number) Then
377 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
378 fnd_message.raise_error;
379 End If;
380 hr_utility.set_location(l_proc, 15);
381 --
382 --
383 -- Validate the datetrack mode mode getting the validation start
384 -- and end dates for the specified datetrack operation.
385 --
386 dt_api.validate_dt_mode
387 (p_effective_date => p_effective_date,
388 p_datetrack_mode => p_datetrack_mode,
389 p_base_table_name => 'ben_svc_area_f',
390 p_base_key_column => 'svc_area_id',
391 p_base_key_value => p_svc_area_id,
392 p_child_table_name1 => 'ben_elig_svc_area_prte_f',
393 p_child_key_column1 => 'elig_svc_area_prte_id',
394 p_child_table_name2 => 'ben_svc_area_pstl_zip_rng_f',
395 p_child_key_column2 => 'svc_area_pstl_zip_rng_id',
396 p_child_table_name3 => 'ben_svc_area_rt_f',
397 p_child_key_column3 => 'svc_area_rt_id',
398 p_enforce_foreign_locking => false, --true,
399 p_validation_start_date => l_validation_start_date,
400 p_validation_end_date => l_validation_end_date);
401 Else
402 --
403 -- We are doing a datetrack 'INSERT' which is illegal within this
404 -- procedure therefore we must error (note: to lck on insert the
405 -- private procedure ins_lck should be called).
406 --
407 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
408 fnd_message.set_token('PROCEDURE', l_proc);
409 fnd_message.set_token('STEP','20');
410 fnd_message.raise_error;
411 End If;
412 --
413 -- Set the validation start and end date OUT arguments
414 --
415 p_validation_start_date := l_validation_start_date;
416 p_validation_end_date := l_validation_end_date;
417 --
418 hr_utility.set_location(' Leaving:'||l_proc, 30);
419 --
420 -- We need to trap the ORA LOCK exception
421 --
422 Exception
423 When HR_Api.Object_Locked then
424 --
425 -- The object is locked therefore we need to supply a meaningful
426 -- error message.
427 --
428 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
429 fnd_message.set_token('TABLE_NAME', 'ben_svc_area_f');
430 fnd_message.raise_error;
431 When l_object_invalid then
432 --
433 -- The object doesn't exist or is invalid
434 --
435 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
436 fnd_message.set_token('TABLE_NAME', 'ben_svc_area_f');
437 fnd_message.raise_error;
438 End lck;
439 --
440 -- ----------------------------------------------------------------------------
441 -- |-----------------------------< convert_args >-----------------------------|
442 -- ----------------------------------------------------------------------------
443 Function convert_args
447 p_effective_end_date in date,
444 (
445 p_svc_area_id in number,
446 p_effective_start_date in date,
448 p_name in varchar2,
449 p_org_unit_prdct in varchar2,
450 p_business_group_id in number,
451 p_sva_attribute_category in varchar2,
452 p_sva_attribute1 in varchar2,
453 p_sva_attribute2 in varchar2,
454 p_sva_attribute3 in varchar2,
455 p_sva_attribute4 in varchar2,
456 p_sva_attribute5 in varchar2,
457 p_sva_attribute6 in varchar2,
458 p_sva_attribute7 in varchar2,
459 p_sva_attribute8 in varchar2,
460 p_sva_attribute9 in varchar2,
461 p_sva_attribute10 in varchar2,
462 p_sva_attribute11 in varchar2,
463 p_sva_attribute12 in varchar2,
464 p_sva_attribute13 in varchar2,
465 p_sva_attribute14 in varchar2,
466 p_sva_attribute15 in varchar2,
467 p_sva_attribute16 in varchar2,
468 p_sva_attribute17 in varchar2,
469 p_sva_attribute18 in varchar2,
470 p_sva_attribute19 in varchar2,
471 p_sva_attribute20 in varchar2,
472 p_sva_attribute21 in varchar2,
473 p_sva_attribute22 in varchar2,
474 p_sva_attribute23 in varchar2,
475 p_sva_attribute24 in varchar2,
476 p_sva_attribute25 in varchar2,
477 p_sva_attribute26 in varchar2,
478 p_sva_attribute27 in varchar2,
479 p_sva_attribute28 in varchar2,
480 p_sva_attribute29 in varchar2,
481 p_sva_attribute30 in varchar2,
482 p_object_version_number in number
483 )
484 Return g_rec_type is
485 --
486 l_rec g_rec_type;
487 l_proc varchar2(72) := g_package||'convert_args';
488 --
489 Begin
490 --
491 hr_utility.set_location('Entering:'||l_proc, 5);
492 --
493 -- Convert arguments into local l_rec structure.
494 --
495 l_rec.svc_area_id := p_svc_area_id;
496 l_rec.effective_start_date := p_effective_start_date;
497 l_rec.effective_end_date := p_effective_end_date;
498 l_rec.name := p_name;
499 l_rec.org_unit_prdct := p_org_unit_prdct;
500 l_rec.business_group_id := p_business_group_id;
501 l_rec.sva_attribute_category := p_sva_attribute_category;
502 l_rec.sva_attribute1 := p_sva_attribute1;
503 l_rec.sva_attribute2 := p_sva_attribute2;
504 l_rec.sva_attribute3 := p_sva_attribute3;
505 l_rec.sva_attribute4 := p_sva_attribute4;
506 l_rec.sva_attribute5 := p_sva_attribute5;
507 l_rec.sva_attribute6 := p_sva_attribute6;
508 l_rec.sva_attribute7 := p_sva_attribute7;
509 l_rec.sva_attribute8 := p_sva_attribute8;
510 l_rec.sva_attribute9 := p_sva_attribute9;
511 l_rec.sva_attribute10 := p_sva_attribute10;
512 l_rec.sva_attribute11 := p_sva_attribute11;
513 l_rec.sva_attribute12 := p_sva_attribute12;
514 l_rec.sva_attribute13 := p_sva_attribute13;
515 l_rec.sva_attribute14 := p_sva_attribute14;
516 l_rec.sva_attribute15 := p_sva_attribute15;
517 l_rec.sva_attribute16 := p_sva_attribute16;
518 l_rec.sva_attribute17 := p_sva_attribute17;
522 l_rec.sva_attribute21 := p_sva_attribute21;
519 l_rec.sva_attribute18 := p_sva_attribute18;
520 l_rec.sva_attribute19 := p_sva_attribute19;
521 l_rec.sva_attribute20 := p_sva_attribute20;
523 l_rec.sva_attribute22 := p_sva_attribute22;
524 l_rec.sva_attribute23 := p_sva_attribute23;
525 l_rec.sva_attribute24 := p_sva_attribute24;
526 l_rec.sva_attribute25 := p_sva_attribute25;
527 l_rec.sva_attribute26 := p_sva_attribute26;
528 l_rec.sva_attribute27 := p_sva_attribute27;
529 l_rec.sva_attribute28 := p_sva_attribute28;
530 l_rec.sva_attribute29 := p_sva_attribute29;
531 l_rec.sva_attribute30 := p_sva_attribute30;
532 l_rec.object_version_number := p_object_version_number;
533 --
534 -- Return the plsql record structure.
538 --
535 --
536 hr_utility.set_location(' Leaving:'||l_proc, 10);
537 Return(l_rec);
539 End convert_args;
540 --
541 end ben_sva_shd;