[Home] [Help]
PACKAGE BODY: APPS.BEN_RZR_SHD
Source
1 Package Body ben_rzr_shd as
2 /* $Header: berzrrhi.pkb 120.0.12010000.1 2008/07/29 13:03:04 appldev ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_rzr_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_PSTL_ZIP_RNG_FK1') 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 ElsIf (p_constraint_name = 'BEN_PSTL_ZIP_RNG_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 Else
47 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
48 fnd_message.set_token('PROCEDURE', l_proc);
49 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
50 fnd_message.raise_error;
51 End If;
52 --
53 hr_utility.set_location(' Leaving:'||l_proc, 10);
54 End constraint_error;
55 --
56 -- ----------------------------------------------------------------------------
57 -- |-----------------------------< api_updating >-----------------------------|
58 -- ----------------------------------------------------------------------------
59 Function api_updating
60 (p_effective_date in date,
61 p_pstl_zip_rng_id in number,
62 p_object_version_number in number
63 ) Return Boolean Is
64 --
65 --
66 -- Cursor selects the 'current' row from the HR Schema
67 --
68 Cursor C_Sel1 is
69 select
70 pstl_zip_rng_id,
71 effective_start_date,
72 effective_end_date,
73 from_value,
74 to_value,
75 business_group_id,
76 rzr_attribute_category,
77 rzr_attribute1,
78 rzr_attribute10,
79 rzr_attribute11,
80 rzr_attribute12,
81 rzr_attribute13,
82 rzr_attribute14,
83 rzr_attribute15,
84 rzr_attribute16,
85 rzr_attribute17,
86 rzr_attribute18,
87 rzr_attribute19,
88 rzr_attribute2,
89 rzr_attribute20,
90 rzr_attribute21,
91 rzr_attribute22,
92 rzr_attribute23,
93 rzr_attribute24,
94 rzr_attribute25,
95 rzr_attribute26,
96 rzr_attribute27,
97 rzr_attribute28,
98 rzr_attribute29,
99 rzr_attribute3,
100 rzr_attribute30,
101 rzr_attribute4,
102 rzr_attribute5,
103 rzr_attribute6,
104 rzr_attribute7,
105 rzr_attribute8,
106 rzr_attribute9,
107 object_version_number
108 from ben_pstl_zip_rng_f
109 where pstl_zip_rng_id = p_pstl_zip_rng_id
110 and p_effective_date
111 between effective_start_date and effective_end_date;
112 --
113 l_proc varchar2(72) := g_package||'api_updating';
114 l_fct_ret boolean;
115 --
116 Begin
117 hr_utility.set_location('Entering:'||l_proc, 5);
118 --
119 If (p_effective_date is null or
120 p_pstl_zip_rng_id is null or
121 p_object_version_number is null) Then
122 --
123 -- One of the primary key arguments is null therefore we must
124 -- set the returning function value to false
125 --
126 l_fct_ret := false;
127 Else
128 If (p_pstl_zip_rng_id = g_old_rec.pstl_zip_rng_id and
129 p_object_version_number = g_old_rec.object_version_number) Then
130 hr_utility.set_location(l_proc, 10);
131 --
132 -- The g_old_rec is current therefore we must
133 -- set the returning function to true
134 --
135 l_fct_ret := true;
136 Else
137 --
138 -- Select the current row
139 --
140 Open C_Sel1;
141 Fetch C_Sel1 Into g_old_rec;
142 If C_Sel1%notfound Then
143 Close C_Sel1;
144 --
145 -- The primary key is invalid therefore we must error
146 --
147 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
148 fnd_message.raise_error;
149 End If;
150 Close C_Sel1;
151 If (p_object_version_number <> g_old_rec.object_version_number) Then
152 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
153 fnd_message.raise_error;
154 End If;
155 hr_utility.set_location(l_proc, 15);
156 l_fct_ret := true;
157 End If;
158 End If;
159 hr_utility.set_location(' Leaving:'||l_proc, 20);
160 Return (l_fct_ret);
161 --
162 End api_updating;
163 --
164 -- ----------------------------------------------------------------------------
165 -- |--------------------------< find_dt_del_modes >---------------------------|
166 -- ----------------------------------------------------------------------------
167 Procedure find_dt_del_modes
168 (p_effective_date in date,
169 p_base_key_value in number,
170 p_zap out nocopy boolean,
171 p_delete out nocopy boolean,
172 p_future_change out nocopy boolean,
173 p_delete_next_change out nocopy boolean) is
174 --
175 l_proc varchar2(72) := g_package||'find_dt_del_modes';
176 --
177 --
178 --
179 --
180 Begin
181 hr_utility.set_location('Entering:'||l_proc, 5);
182 --
183 --
184 -- Call the corresponding datetrack api
185 --
186 dt_api.find_dt_del_modes
187 (p_effective_date => p_effective_date,
188 p_base_table_name => 'ben_pstl_zip_rng_f',
189 p_base_key_column => 'pstl_zip_rng_id',
190 p_base_key_value => p_base_key_value,
191 p_zap => p_zap,
192 p_delete => p_delete,
193 p_future_change => p_future_change,
194 p_delete_next_change => p_delete_next_change);
195 --
196 hr_utility.set_location(' Leaving:'||l_proc, 10);
197 End find_dt_del_modes;
198 --
199 -- ----------------------------------------------------------------------------
200 -- |--------------------------< find_dt_upd_modes >---------------------------|
201 -- ----------------------------------------------------------------------------
202 Procedure find_dt_upd_modes
203 (p_effective_date in date,
204 p_base_key_value in number,
205 p_correction out nocopy boolean,
206 p_update out nocopy boolean,
207 p_update_override out nocopy boolean,
208 p_update_change_insert out nocopy boolean) is
209 --
210 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
211 --
212 Begin
213 hr_utility.set_location('Entering:'||l_proc, 5);
214 --
215 -- Call the corresponding datetrack api
216 --
217 dt_api.find_dt_upd_modes
218 (p_effective_date => p_effective_date,
219 p_base_table_name => 'ben_pstl_zip_rng_f',
220 p_base_key_column => 'pstl_zip_rng_id',
221 p_base_key_value => p_base_key_value,
222 p_correction => p_correction,
223 p_update => p_update,
224 p_update_override => p_update_override,
225 p_update_change_insert => p_update_change_insert);
226 --
227 hr_utility.set_location(' Leaving:'||l_proc, 10);
228 End find_dt_upd_modes;
229 --
230 -- ----------------------------------------------------------------------------
231 -- |------------------------< upd_effective_end_date >------------------------|
232 -- ----------------------------------------------------------------------------
233 Procedure upd_effective_end_date
234 (p_effective_date in date,
235 p_base_key_value in number,
236 p_new_effective_end_date in date,
237 p_validation_start_date in date,
238 p_validation_end_date in date,
239 p_object_version_number out nocopy number) is
240 --
241 l_proc varchar2(72) := g_package||'upd_effective_end_date';
242 l_object_version_number number;
243 --
244 Begin
245 hr_utility.set_location('Entering:'||l_proc, 5);
246 --
247 -- Because we are updating a row we must get the next object
248 -- version number.
249 --
250 l_object_version_number :=
251 dt_api.get_object_version_number
252 (p_base_table_name => 'ben_pstl_zip_rng_f',
253 p_base_key_column => 'pstl_zip_rng_id',
254 p_base_key_value => p_base_key_value);
255 --
256 hr_utility.set_location(l_proc, 10);
257 g_api_dml := true; -- Set the api dml status
258 --
259 -- Update the specified datetrack row setting the effective
260 -- end date to the specified new effective end date.
261 --
262 update ben_pstl_zip_rng_f t
263 set t.effective_end_date = p_new_effective_end_date,
264 t.object_version_number = l_object_version_number
265 where t.pstl_zip_rng_id = p_base_key_value
266 and p_effective_date
267 between t.effective_start_date and t.effective_end_date;
268 --
269 g_api_dml := false; -- Unset the api dml status
270 p_object_version_number := l_object_version_number;
271 hr_utility.set_location(' Leaving:'||l_proc, 15);
272 --
273 Exception
274 When Others Then
275 g_api_dml := false; -- Unset the api dml status
276 Raise;
277 End upd_effective_end_date;
278 --
279 -- ----------------------------------------------------------------------------
280 -- |---------------------------------< lck >----------------------------------|
281 -- ----------------------------------------------------------------------------
282 Procedure lck
283 (p_effective_date in date,
284 p_datetrack_mode in varchar2,
285 p_pstl_zip_rng_id in number,
286 p_object_version_number in number,
287 p_validation_start_date out nocopy date,
288 p_validation_end_date out nocopy date) is
289 --
290 l_proc varchar2(72) := g_package||'lck';
291 l_validation_start_date date;
292 l_validation_end_date date;
293 l_object_invalid exception;
294 l_argument varchar2(30);
295 --
296 -- Cursor C_Sel1 selects the current locked row as of session date
297 -- ensuring that the object version numbers match.
298 --
299 Cursor C_Sel1 is
300 select
301 pstl_zip_rng_id,
302 effective_start_date,
303 effective_end_date,
304 from_value,
305 to_value,
306 business_group_id,
307 rzr_attribute_category,
308 rzr_attribute1,
309 rzr_attribute10,
310 rzr_attribute11,
311 rzr_attribute12,
312 rzr_attribute13,
313 rzr_attribute14,
314 rzr_attribute15,
315 rzr_attribute16,
316 rzr_attribute17,
317 rzr_attribute18,
318 rzr_attribute19,
319 rzr_attribute2,
320 rzr_attribute20,
321 rzr_attribute21,
322 rzr_attribute22,
323 rzr_attribute23,
324 rzr_attribute24,
325 rzr_attribute25,
326 rzr_attribute26,
327 rzr_attribute27,
328 rzr_attribute28,
329 rzr_attribute29,
330 rzr_attribute3,
331 rzr_attribute30,
332 rzr_attribute4,
333 rzr_attribute5,
334 rzr_attribute6,
335 rzr_attribute7,
336 rzr_attribute8,
337 rzr_attribute9,
338 object_version_number
339 from ben_pstl_zip_rng_f
340 where pstl_zip_rng_id = p_pstl_zip_rng_id
341 and p_effective_date
342 between effective_start_date and effective_end_date
343 for update nowait;
344 --
345 --
346 --
347 Begin
348 hr_utility.set_location('Entering:'||l_proc, 5);
349 --
350 -- Ensure that all the mandatory arguments are not null
351 --
352 hr_api.mandatory_arg_error(p_api_name => l_proc,
353 p_argument => 'effective_date',
354 p_argument_value => p_effective_date);
355 --
356 hr_api.mandatory_arg_error(p_api_name => l_proc,
357 p_argument => 'datetrack_mode',
358 p_argument_value => p_datetrack_mode);
359 --
360 hr_api.mandatory_arg_error(p_api_name => l_proc,
361 p_argument => 'pstl_zip_rng_id',
362 p_argument_value => p_pstl_zip_rng_id);
363 --
364 hr_api.mandatory_arg_error(p_api_name => l_proc,
365 p_argument => 'object_version_number',
366 p_argument_value => p_object_version_number);
367 --
368 -- Check to ensure the datetrack mode is not INSERT.
369 --
370 If (p_datetrack_mode <> 'INSERT') then
371 --
372 -- We must select and lock the current row.
373 --
374 Open C_Sel1;
375 Fetch C_Sel1 Into g_old_rec;
376 If C_Sel1%notfound then
377 Close C_Sel1;
378 --
379 -- The primary key is invalid therefore we must error
380 --
381 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
382 fnd_message.raise_error;
383 End If;
384 Close C_Sel1;
385 If (p_object_version_number <> g_old_rec.object_version_number) Then
386 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
387 fnd_message.raise_error;
388 End If;
389 hr_utility.set_location(l_proc, 15);
390 --
391 --
392 -- Validate the datetrack mode mode getting the validation start
393 -- and end dates for the specified datetrack operation.
394 --
395 dt_api.validate_dt_mode
396 (p_effective_date => p_effective_date,
397 p_datetrack_mode => p_datetrack_mode,
398 p_base_table_name => 'ben_pstl_zip_rng_f',
399 p_base_key_column => 'pstl_zip_rng_id',
400 p_base_key_value => p_pstl_zip_rng_id,
401 p_child_table_name1 => 'ben_elig_pstl_cd_r_rng_prte_f',
402 p_child_key_column1 => 'elig_pstl_cd_r_rng_prte_id',
403 p_child_table_name2 => 'ben_elig_pstl_cd_r_rng_cvg_f',
404 p_child_key_column2 => 'elig_pstl_cd_r_rng_cvg_id',
405 p_child_table_name3 => 'ben_pstl_zip_rt_f',
406 p_child_key_column3 => 'pstl_zip_rt_id',
407 p_child_table_name4 => 'ben_svc_area_pstl_zip_rng_f',
408 p_child_key_column4 => 'svc_area_pstl_zip_rng_id',
409 p_enforce_foreign_locking => false, -- Bug 2488652
410 p_validation_start_date => l_validation_start_date,
411 p_validation_end_date => l_validation_end_date);
412 Else
413 --
414 -- We are doing a datetrack 'INSERT' which is illegal within this
415 -- procedure therefore we must error (note: to lck on insert the
416 -- private procedure ins_lck should be called).
417 --
418 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
419 fnd_message.set_token('PROCEDURE', l_proc);
420 fnd_message.set_token('STEP','20');
421 fnd_message.raise_error;
422 End If;
423 --
424 -- Set the validation start and end date OUT arguments
425 --
426 p_validation_start_date := l_validation_start_date;
430 --
427 p_validation_end_date := l_validation_end_date;
428 --
429 hr_utility.set_location(' Leaving:'||l_proc, 30);
431 -- We need to trap the ORA LOCK exception
432 --
433 Exception
434 When HR_Api.Object_Locked then
435 --
436 -- The object is locked therefore we need to supply a meaningful
437 -- error message.
438 --
439 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
440 fnd_message.set_token('TABLE_NAME', 'ben_pstl_zip_rng_f');
441 fnd_message.raise_error;
442 When l_object_invalid then
443 --
444 -- The object doesn't exist or is invalid
445 --
446 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
447 fnd_message.set_token('TABLE_NAME', 'ben_pstl_zip_rng_f');
448 fnd_message.raise_error;
449 End lck;
450 --
451 -- ----------------------------------------------------------------------------
452 -- |-----------------------------< convert_args >-----------------------------|
453 -- ----------------------------------------------------------------------------
454 Function convert_args
455 (
456 p_pstl_zip_rng_id in number,
457 p_effective_start_date in date,
458 p_effective_end_date in date,
459 p_from_value in varchar2,
460 p_to_value in varchar2,
461 p_business_group_id in number,
462 p_rzr_attribute_category in varchar2,
463 p_rzr_attribute1 in varchar2,
464 p_rzr_attribute10 in varchar2,
465 p_rzr_attribute11 in varchar2,
466 p_rzr_attribute12 in varchar2,
467 p_rzr_attribute13 in varchar2,
468 p_rzr_attribute14 in varchar2,
469 p_rzr_attribute15 in varchar2,
470 p_rzr_attribute16 in varchar2,
471 p_rzr_attribute17 in varchar2,
472 p_rzr_attribute18 in varchar2,
473 p_rzr_attribute19 in varchar2,
474 p_rzr_attribute2 in varchar2,
475 p_rzr_attribute20 in varchar2,
476 p_rzr_attribute21 in varchar2,
477 p_rzr_attribute22 in varchar2,
478 p_rzr_attribute23 in varchar2,
479 p_rzr_attribute24 in varchar2,
480 p_rzr_attribute25 in varchar2,
481 p_rzr_attribute26 in varchar2,
482 p_rzr_attribute27 in varchar2,
483 p_rzr_attribute28 in varchar2,
484 p_rzr_attribute29 in varchar2,
485 p_rzr_attribute3 in varchar2,
486 p_rzr_attribute30 in varchar2,
487 p_rzr_attribute4 in varchar2,
488 p_rzr_attribute5 in varchar2,
489 p_rzr_attribute6 in varchar2,
490 p_rzr_attribute7 in varchar2,
491 p_rzr_attribute8 in varchar2,
492 p_rzr_attribute9 in varchar2,
493 p_object_version_number in number
494 )
495 Return g_rec_type is
496 --
497 l_rec g_rec_type;
498 l_proc varchar2(72) := g_package||'convert_args';
499 --
500 Begin
501 --
502 hr_utility.set_location('Entering:'||l_proc, 5);
503 --
504 -- Convert arguments into local l_rec structure.
505 --
506 l_rec.pstl_zip_rng_id := p_pstl_zip_rng_id;
507 l_rec.effective_start_date := p_effective_start_date;
508 l_rec.effective_end_date := p_effective_end_date;
509 l_rec.from_value := p_from_value;
510 l_rec.to_value := p_to_value;
511 l_rec.business_group_id := p_business_group_id;
512 l_rec.rzr_attribute_category := p_rzr_attribute_category;
513 l_rec.rzr_attribute1 := p_rzr_attribute1;
514 l_rec.rzr_attribute10 := p_rzr_attribute10;
515 l_rec.rzr_attribute11 := p_rzr_attribute11;
516 l_rec.rzr_attribute12 := p_rzr_attribute12;
517 l_rec.rzr_attribute13 := p_rzr_attribute13;
518 l_rec.rzr_attribute14 := p_rzr_attribute14;
519 l_rec.rzr_attribute15 := p_rzr_attribute15;
520 l_rec.rzr_attribute16 := p_rzr_attribute16;
521 l_rec.rzr_attribute17 := p_rzr_attribute17;
522 l_rec.rzr_attribute18 := p_rzr_attribute18;
523 l_rec.rzr_attribute19 := p_rzr_attribute19;
524 l_rec.rzr_attribute2 := p_rzr_attribute2;
525 l_rec.rzr_attribute20 := p_rzr_attribute20;
526 l_rec.rzr_attribute21 := p_rzr_attribute21;
527 l_rec.rzr_attribute22 := p_rzr_attribute22;
528 l_rec.rzr_attribute23 := p_rzr_attribute23;
529 l_rec.rzr_attribute24 := p_rzr_attribute24;
530 l_rec.rzr_attribute25 := p_rzr_attribute25;
531 l_rec.rzr_attribute26 := p_rzr_attribute26;
532 l_rec.rzr_attribute27 := p_rzr_attribute27;
533 l_rec.rzr_attribute28 := p_rzr_attribute28;
534 l_rec.rzr_attribute29 := p_rzr_attribute29;
535 l_rec.rzr_attribute3 := p_rzr_attribute3;
536 l_rec.rzr_attribute30 := p_rzr_attribute30;
537 l_rec.rzr_attribute4 := p_rzr_attribute4;
538 l_rec.rzr_attribute5 := p_rzr_attribute5;
539 l_rec.rzr_attribute6 := p_rzr_attribute6;
540 l_rec.rzr_attribute7 := p_rzr_attribute7;
541 l_rec.rzr_attribute8 := p_rzr_attribute8;
542 l_rec.rzr_attribute9 := p_rzr_attribute9;
543 l_rec.object_version_number := p_object_version_number;
544 --
545 -- Return the plsql record structure.
546 --
547 hr_utility.set_location(' Leaving:'||l_proc, 10);
548 Return(l_rec);
549 --
550 End convert_args;
551 --
552 end ben_rzr_shd;