[Home] [Help]
PACKAGE BODY: APPS.BEN_BEP_SHD
Source
1 Package Body ben_bep_shd as
2 /* $Header: bebeprhi.pkb 120.0.12010000.2 2008/08/05 14:07:52 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_bep_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE
15 ) Is
16 --
17 l_proc varchar2(72) := g_package||'constraint_error';
18 --
19 Begin
20 --
21 If (p_constraint_name = 'BEN_ELIG_OBJ_ELIG_PROFL_PK') Then
22 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
23 fnd_message.set_token('PROCEDURE', l_proc);
24 fnd_message.set_token('STEP','5');
25 fnd_message.raise_error;
26 Else
27 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
28 fnd_message.set_token('PROCEDURE', l_proc);
29 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
30 fnd_message.raise_error;
31 End If;
32 --
33 End constraint_error;
34 --
35 -- ----------------------------------------------------------------------------
36 -- |-----------------------------< api_updating >-----------------------------|
37 -- ----------------------------------------------------------------------------
38 Function api_updating
39 (p_effective_date in date
40 ,p_elig_obj_elig_prfl_id in number
41 ,p_object_version_number in number
42 ) Return Boolean Is
43 --
44 -- Cursor selects the 'current' row from the HR Schema
45 --
46 Cursor C_Sel1 is
47 select
48 elig_obj_elig_prfl_id
49 ,effective_start_date
50 ,effective_end_date
51 ,elig_obj_id
52 ,elig_prfl_id
53 ,mndtry_flag
54 ,business_group_id
55 ,bep_attribute_category
56 ,bep_attribute1
57 ,bep_attribute2
58 ,bep_attribute3
59 ,bep_attribute4
60 ,bep_attribute5
61 ,bep_attribute6
62 ,bep_attribute7
63 ,bep_attribute8
64 ,bep_attribute9
65 ,bep_attribute10
66 ,bep_attribute11
67 ,bep_attribute12
71 ,bep_attribute16
68 ,bep_attribute13
69 ,bep_attribute14
70 ,bep_attribute15
72 ,bep_attribute17
73 ,bep_attribute18
74 ,bep_attribute19
75 ,bep_attribute20
76 ,object_version_number
77 from ben_elig_obj_elig_profl_f
78 where elig_obj_elig_prfl_id = p_elig_obj_elig_prfl_id
79 and p_effective_date
80 between effective_start_date and effective_end_date;
81 --
82 l_fct_ret boolean;
83 --
84 Begin
85 --
86 If (p_effective_date is null or
87 p_elig_obj_elig_prfl_id is null or
88 p_object_version_number is null) Then
89 --
90 -- One of the primary key arguments is null therefore we must
91 -- set the returning function value to false
92 --
93 l_fct_ret := false;
94 Else
95 If (p_elig_obj_elig_prfl_id =
96 ben_bep_shd.g_old_rec.elig_obj_elig_prfl_id and
97 p_object_version_number =
98 ben_bep_shd.g_old_rec.object_version_number
99 ) Then
100 --
101 -- The g_old_rec is current therefore we must
102 -- set the returning function to true
103 --
104 l_fct_ret := true;
105 Else
106 --
107 -- Select the current row
108 --
109 Open C_Sel1;
110 Fetch C_Sel1 Into ben_bep_shd.g_old_rec;
111 If C_Sel1%notfound Then
112 Close C_Sel1;
113 --
114 -- The primary key is invalid therefore we must error
115 --
116 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
117 fnd_message.raise_error;
118 End If;
119 Close C_Sel1;
120 If (p_object_version_number
121 <> ben_bep_shd.g_old_rec.object_version_number) Then
122 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
123 fnd_message.raise_error;
124 End If;
125 l_fct_ret := true;
126 End If;
127 End If;
128 Return (l_fct_ret);
129 --
130 End api_updating;
131 --
132 -- ----------------------------------------------------------------------------
133 -- |---------------------------< find_dt_upd_modes >--------------------------|
134 -- ----------------------------------------------------------------------------
135 Procedure find_dt_upd_modes
136 (p_effective_date in date
137 ,p_base_key_value in number
138 ,p_correction out nocopy boolean
139 ,p_update out nocopy boolean
140 ,p_update_override out nocopy boolean
141 ,p_update_change_insert out nocopy boolean
142 ) is
143 --
144 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
145 --
146 Begin
147 hr_utility.set_location('Entering:'||l_proc, 5);
148 --
149 -- Call the corresponding datetrack api
150 --
151 dt_api.find_dt_upd_modes
152 (p_effective_date => p_effective_date
153 ,p_base_table_name => 'ben_elig_obj_elig_profl_f'
154 ,p_base_key_column => 'elig_obj_elig_prfl_id'
155 ,p_base_key_value => p_base_key_value
156 ,p_correction => p_correction
157 ,p_update => p_update
158 ,p_update_override => p_update_override
159 ,p_update_change_insert => p_update_change_insert
160 );
161 --
162 hr_utility.set_location(' Leaving:'||l_proc, 10);
163 End find_dt_upd_modes;
164 --
165 -- ----------------------------------------------------------------------------
166 -- |---------------------------< find_dt_del_modes >--------------------------|
167 -- ----------------------------------------------------------------------------
168 Procedure find_dt_del_modes
169 (p_effective_date in date
170 ,p_base_key_value in number
171 ,p_zap out nocopy boolean
172 ,p_delete out nocopy boolean
173 ,p_future_change out nocopy boolean
174 ,p_delete_next_change out nocopy boolean
175 ) is
176 --
177 l_proc varchar2(72) := g_package||'find_dt_del_modes';
178 --
179 l_parent_key_value1 number;
180 l_parent_key_value2 number;
181 --
182 Cursor C_Sel1 Is
183 select
184 t.elig_prfl_id,
185 t.elig_obj_id
186 from ben_elig_obj_elig_profl_f t
187 where t.elig_obj_elig_prfl_id = p_base_key_value
188 and p_effective_date
189 between t.effective_start_date and t.effective_end_date;
190 --
191 Begin
192 hr_utility.set_location('Entering:'||l_proc, 5);
193 Open C_sel1;
194 Fetch C_Sel1 Into
195 l_parent_key_value1,
196 l_parent_key_value2;
197 If C_Sel1%NOTFOUND then
198 Close C_Sel1;
199 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
200 fnd_message.set_token('PROCEDURE',l_proc);
201 fnd_message.set_token('STEP','10');
202 fnd_message.raise_error;
203 End If;
204 Close C_Sel1;
205 --
206 -- Call the corresponding datetrack api
207 --
208 dt_api.find_dt_del_modes
209 (p_effective_date => p_effective_date
210 ,p_base_table_name => 'ben_elig_obj_elig_profl_f'
211 ,p_base_key_column => 'elig_obj_elig_prfl_id'
212 ,p_base_key_value => p_base_key_value
213 ,p_parent_table_name1 => 'ben_eligy_prfl_f'
214 ,p_parent_key_column1 => 'elig_prfl_id'
215 ,p_parent_key_value1 => l_parent_key_value1
216 ,p_parent_table_name2 => 'ben_elig_obj_f'
217 ,p_parent_key_column2 => 'elig_obj_id'
218 ,p_parent_key_value2 => l_parent_key_value2
222 ,p_delete_next_change => p_delete_next_change
219 ,p_zap => p_zap
220 ,p_delete => p_delete
221 ,p_future_change => p_future_change
223 );
224 --
225 hr_utility.set_location(' Leaving:'||l_proc, 10);
226 End find_dt_del_modes;
227 --
228 -- ----------------------------------------------------------------------------
229 -- |-----------------------< upd_effective_end_date >-------------------------|
230 -- ----------------------------------------------------------------------------
231 Procedure upd_effective_end_date
232 (p_effective_date in date
233 ,p_base_key_value in number
234 ,p_new_effective_end_date in date
235 ,p_validation_start_date in date
236 ,p_validation_end_date in date
237 ,p_object_version_number out nocopy number
238 ) is
239 --
240 l_proc varchar2(72) := g_package||'upd_effective_end_date';
241 l_object_version_number number;
242 --
243 Begin
244 hr_utility.set_location('Entering:'||l_proc, 5);
245 --
246 -- Because we are updating a row we must get the next object
247 -- version number.
248 --
249 l_object_version_number :=
250 dt_api.get_object_version_number
251 (p_base_table_name => 'ben_elig_obj_elig_profl_f'
252 ,p_base_key_column => 'elig_obj_elig_prfl_id'
253 ,p_base_key_value => p_base_key_value
254 );
255 --
256 hr_utility.set_location(l_proc, 10);
257 --
258 --
259 -- Update the specified datetrack row setting the effective
260 -- end date to the specified new effective end date.
261 --
262 update ben_elig_obj_elig_profl_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.elig_obj_elig_prfl_id = p_base_key_value
266 and p_effective_date
267 between t.effective_start_date and t.effective_end_date;
268 --
269 --
270 p_object_version_number := l_object_version_number;
271 hr_utility.set_location(' Leaving:'||l_proc, 15);
272 --
273 End upd_effective_end_date;
274 --
275 -- ----------------------------------------------------------------------------
276 -- |---------------------------------< lck >----------------------------------|
277 -- ----------------------------------------------------------------------------
278 Procedure lck
279 (p_effective_date in date
280 ,p_datetrack_mode in varchar2
281 ,p_elig_obj_elig_prfl_id in number
282 ,p_object_version_number in number
283 ,p_validation_start_date out nocopy date
284 ,p_validation_end_date out nocopy date
285 ) is
286 --
287 l_proc varchar2(72) := g_package||'lck';
288 l_validation_start_date date;
289 l_validation_end_date date;
290 l_argument varchar2(30);
291 --
292 -- Cursor C_Sel1 selects the current locked row as of session date
293 -- ensuring that the object version numbers match.
294 --
295 Cursor C_Sel1 is
296 select
297 elig_obj_elig_prfl_id
298 ,effective_start_date
299 ,effective_end_date
300 ,elig_obj_id
301 ,elig_prfl_id
302 ,mndtry_flag
303 ,business_group_id
304 ,bep_attribute_category
305 ,bep_attribute1
306 ,bep_attribute2
307 ,bep_attribute3
308 ,bep_attribute4
309 ,bep_attribute5
310 ,bep_attribute6
311 ,bep_attribute7
312 ,bep_attribute8
313 ,bep_attribute9
314 ,bep_attribute10
315 ,bep_attribute11
316 ,bep_attribute12
317 ,bep_attribute13
318 ,bep_attribute14
319 ,bep_attribute15
320 ,bep_attribute16
321 ,bep_attribute17
322 ,bep_attribute18
323 ,bep_attribute19
324 ,bep_attribute20
325 ,object_version_number
326 from ben_elig_obj_elig_profl_f
327 where elig_obj_elig_prfl_id = p_elig_obj_elig_prfl_id
328 and p_effective_date
329 between effective_start_date and effective_end_date
330 for update nowait;
331 --
332 --
333 --
334 Begin
335 hr_utility.set_location('Entering:'||l_proc, 5);
336 --
337 -- Ensure that all the mandatory arguments are not null
338 --
339 hr_api.mandatory_arg_error(p_api_name => l_proc
340 ,p_argument => 'effective_date'
341 ,p_argument_value => p_effective_date
342 );
343 --
344 hr_api.mandatory_arg_error(p_api_name => l_proc
345 ,p_argument => 'datetrack_mode'
346 ,p_argument_value => p_datetrack_mode
347 );
348 --
349 hr_api.mandatory_arg_error(p_api_name => l_proc
350 ,p_argument => 'elig_obj_elig_prfl_id'
351 ,p_argument_value => p_elig_obj_elig_prfl_id
352 );
353 --
354 hr_api.mandatory_arg_error(p_api_name => l_proc
355 ,p_argument => 'object_version_number'
356 ,p_argument_value => p_object_version_number
357 );
358 --
359 -- Check to ensure the datetrack mode is not INSERT.
360 --
361 If (p_datetrack_mode <> hr_api.g_insert) then
362 --
366 Fetch C_Sel1 Into ben_bep_shd.g_old_rec;
363 -- We must select and lock the current row.
364 --
365 Open C_Sel1;
367 If C_Sel1%notfound then
368 Close C_Sel1;
369 --
370 -- The primary key is invalid therefore we must error
371 --
372 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
373 fnd_message.raise_error;
374 End If;
375 Close C_Sel1;
376 If (p_object_version_number
377 <> ben_bep_shd.g_old_rec.object_version_number) Then
378 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
379 fnd_message.raise_error;
380 End If;
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_elig_obj_elig_profl_f'
390 ,p_base_key_column => 'elig_obj_elig_prfl_id'
391 ,p_base_key_value => p_elig_obj_elig_prfl_id
392 ,p_parent_table_name1 => 'ben_eligy_prfl_f'
393 ,p_parent_key_column1 => 'elig_prfl_id'
394 ,p_parent_key_value1 => ben_bep_shd.g_old_rec.elig_prfl_id
395 ,p_parent_table_name2 => 'ben_elig_obj_f'
396 ,p_parent_key_column2 => 'elig_obj_id'
397 ,p_parent_key_value2 => ben_bep_shd.g_old_rec.elig_obj_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 );
402 Else
403 --
404 -- We are doing a datetrack 'INSERT' which is illegal within this
405 -- procedure therefore we must error (note: to lck on insert the
406 -- private procedure ins_lck should be called).
407 --
408 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
409 fnd_message.set_token('PROCEDURE', l_proc);
410 fnd_message.set_token('STEP','20');
411 fnd_message.raise_error;
412 End If;
413 --
414 -- Set the validation start and end date OUT arguments
415 --
416 p_validation_start_date := l_validation_start_date;
417 p_validation_end_date := l_validation_end_date;
418 --
419 hr_utility.set_location(' Leaving:'||l_proc, 30);
420 --
421 -- We need to trap the ORA LOCK exception
422 --
423 Exception
424 When HR_Api.Object_Locked then
425 --
426 -- The object is locked therefore we need to supply a meaningful
427 -- error message.
428 --
429 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
430 fnd_message.set_token('TABLE_NAME', 'ben_elig_obj_elig_profl_f');
431 fnd_message.raise_error;
432 End lck;
433 --
434 -- ----------------------------------------------------------------------------
435 -- |-----------------------------< convert_args >-----------------------------|
436 -- ----------------------------------------------------------------------------
437 Function convert_args
438 (p_elig_obj_elig_prfl_id in number
439 ,p_effective_start_date in date
440 ,p_effective_end_date in date
441 ,p_elig_obj_id in number
442 ,p_elig_prfl_id in number
443 ,p_mndtry_flag in varchar2
444 ,p_business_group_id in number
445 ,p_bep_attribute_category in varchar2
446 ,p_bep_attribute1 in varchar2
447 ,p_bep_attribute2 in varchar2
448 ,p_bep_attribute3 in varchar2
449 ,p_bep_attribute4 in varchar2
450 ,p_bep_attribute5 in varchar2
451 ,p_bep_attribute6 in varchar2
452 ,p_bep_attribute7 in varchar2
453 ,p_bep_attribute8 in varchar2
454 ,p_bep_attribute9 in varchar2
455 ,p_bep_attribute10 in varchar2
456 ,p_bep_attribute11 in varchar2
457 ,p_bep_attribute12 in varchar2
458 ,p_bep_attribute13 in varchar2
459 ,p_bep_attribute14 in varchar2
460 ,p_bep_attribute15 in varchar2
461 ,p_bep_attribute16 in varchar2
462 ,p_bep_attribute17 in varchar2
463 ,p_bep_attribute18 in varchar2
464 ,p_bep_attribute19 in varchar2
465 ,p_bep_attribute20 in varchar2
466 ,p_object_version_number in number
467 )
468 Return g_rec_type is
469 --
470 l_rec g_rec_type;
471 --
472 Begin
473 --
474 -- Convert arguments into local l_rec structure.
475 --
476 l_rec.elig_obj_elig_prfl_id := p_elig_obj_elig_prfl_id;
477 l_rec.effective_start_date := p_effective_start_date;
478 l_rec.effective_end_date := p_effective_end_date;
479 l_rec.elig_obj_id := p_elig_obj_id;
480 l_rec.elig_prfl_id := p_elig_prfl_id;
481 l_rec.mndtry_flag := p_mndtry_flag;
482 l_rec.business_group_id := p_business_group_id;
483 l_rec.bep_attribute_category := p_bep_attribute_category;
484 l_rec.bep_attribute1 := p_bep_attribute1;
485 l_rec.bep_attribute2 := p_bep_attribute2;
486 l_rec.bep_attribute3 := p_bep_attribute3;
487 l_rec.bep_attribute4 := p_bep_attribute4;
488 l_rec.bep_attribute5 := p_bep_attribute5;
492 l_rec.bep_attribute9 := p_bep_attribute9;
489 l_rec.bep_attribute6 := p_bep_attribute6;
490 l_rec.bep_attribute7 := p_bep_attribute7;
491 l_rec.bep_attribute8 := p_bep_attribute8;
493 l_rec.bep_attribute10 := p_bep_attribute10;
494 l_rec.bep_attribute11 := p_bep_attribute11;
495 l_rec.bep_attribute12 := p_bep_attribute12;
496 l_rec.bep_attribute13 := p_bep_attribute13;
497 l_rec.bep_attribute14 := p_bep_attribute14;
498 l_rec.bep_attribute15 := p_bep_attribute15;
499 l_rec.bep_attribute16 := p_bep_attribute16;
500 l_rec.bep_attribute17 := p_bep_attribute17;
501 l_rec.bep_attribute18 := p_bep_attribute18;
502 l_rec.bep_attribute19 := p_bep_attribute19;
503 l_rec.bep_attribute20 := p_bep_attribute20;
504 l_rec.object_version_number := p_object_version_number;
505 --
506 -- Return the plsql record structure.
507 --
508 Return(l_rec);
509 --
510 End convert_args;
511 --
512 end ben_bep_shd;