[Home] [Help]
PACKAGE BODY: APPS.BEN_JRT_SHD
Source
1 Package Body ben_jrt_shd as
2 /* $Header: bejrtrhi.pkb 120.2 2006/03/30 23:48:52 gsehgal noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_jrt_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_JOB_RT_F_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_job_rt_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 job_rt_id
49 ,effective_start_date
50 ,effective_end_date
51 ,job_id
52 ,excld_flag
53 ,business_group_id
54 ,vrbl_rt_prfl_id
55 ,object_version_number
56 ,ordr_num
57 ,jrt_attribute_category
58 ,jrt_attribute1
59 ,jrt_attribute2
60 ,jrt_attribute3
61 ,jrt_attribute4
62 ,jrt_attribute5
63 ,jrt_attribute6
64 ,jrt_attribute7
65 ,jrt_attribute8
66 ,jrt_attribute9
67 ,jrt_attribute10
68 ,jrt_attribute11
69 ,jrt_attribute12
70 ,jrt_attribute13
71 ,jrt_attribute14
72 ,jrt_attribute15
73 ,jrt_attribute16
74 ,jrt_attribute17
75 ,jrt_attribute18
76 ,jrt_attribute19
77 ,jrt_attribute20
78 ,jrt_attribute21
79 ,jrt_attribute22
80 ,jrt_attribute23
81 ,jrt_attribute24
82 ,jrt_attribute25
83 ,jrt_attribute26
84 ,jrt_attribute27
85 ,jrt_attribute28
86 ,jrt_attribute29
87 ,jrt_attribute30
88 from ben_job_rt_f
89 where job_rt_id = p_job_rt_id
90 and p_effective_date
91 between effective_start_date and effective_end_date;
92 --
93 l_fct_ret boolean;
94 --
95 Begin
96 --
97 If (p_effective_date is null or
98 p_job_rt_id is null or
99 p_object_version_number is null) Then
100 --
101 -- One of the primary key arguments is null therefore we must
102 -- set the returning function value to false
103 --
104 l_fct_ret := false;
105 Else
106 If (p_job_rt_id =
107 ben_jrt_shd.g_old_rec.job_rt_id and
108 p_object_version_number =
109 ben_jrt_shd.g_old_rec.object_version_number
110 ) Then
111 --
112 -- The g_old_rec is current therefore we must
113 -- set the returning function to true
114 --
115 l_fct_ret := true;
116 Else
117 --
118 -- Select the current row
119 --
120 Open C_Sel1;
121 Fetch C_Sel1 Into ben_jrt_shd.g_old_rec;
122 If C_Sel1%notfound Then
123 Close C_Sel1;
124 --
125 -- The primary key is invalid therefore we must error
126 --
127 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
128 fnd_message.raise_error;
129 End If;
130 Close C_Sel1;
131 If (p_object_version_number
132 <> ben_jrt_shd.g_old_rec.object_version_number) Then
133 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
134 fnd_message.raise_error;
135 End If;
136 l_fct_ret := true;
137 End If;
138 End If;
139 Return (l_fct_ret);
140 --
141 End api_updating;
142 --
143 -- ----------------------------------------------------------------------------
144 -- |---------------------------< find_dt_upd_modes >--------------------------|
145 -- ----------------------------------------------------------------------------
146 Procedure find_dt_upd_modes
147 (p_effective_date in date
148 ,p_base_key_value in number
149 ,p_correction out nocopy boolean
150 ,p_update out nocopy boolean
151 ,p_update_override out nocopy boolean
152 ,p_update_change_insert out nocopy boolean
153 ) is
154 --
155 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
156 --
157 Begin
158 hr_utility.set_location('Entering:'||l_proc, 5);
159 --
160 -- Call the corresponding datetrack api
161 --
162 dt_api.find_dt_upd_modes
163 (p_effective_date => p_effective_date
164 ,p_base_table_name => 'ben_job_rt_f'
165 ,p_base_key_column => 'job_rt_id'
166 ,p_base_key_value => p_base_key_value
167 ,p_correction => p_correction
168 ,p_update => p_update
169 ,p_update_override => p_update_override
170 ,p_update_change_insert => p_update_change_insert
171 );
172 --
173 hr_utility.set_location(' Leaving:'||l_proc, 10);
174 End find_dt_upd_modes;
175 --
176 -- ----------------------------------------------------------------------------
177 -- |---------------------------< find_dt_del_modes >--------------------------|
178 -- ----------------------------------------------------------------------------
179 Procedure find_dt_del_modes
180 (p_effective_date in date
181 ,p_base_key_value in number
182 ,p_zap out nocopy boolean
183 ,p_delete out nocopy boolean
184 ,p_future_change out nocopy boolean
185 ,p_delete_next_change out nocopy boolean
186 ) is
187 --
188 l_proc varchar2(72) := g_package||'find_dt_del_modes';
189 --
190 l_parent_key_value1 number;
191 --
192 Cursor C_Sel1 Is
193 select
194 t.vrbl_rt_prfl_id
195 from ben_job_rt_f t
196 where t.job_rt_id = p_base_key_value
197 and p_effective_date
198 between t.effective_start_date and t.effective_end_date;
199 --
200 Begin
201 hr_utility.set_location('Entering:'||l_proc, 5);
202 Open C_sel1;
203 Fetch C_Sel1 Into
204 l_parent_key_value1;
205 If C_Sel1%NOTFOUND then
206 Close C_Sel1;
207 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
208 fnd_message.set_token('PROCEDURE',l_proc);
209 fnd_message.set_token('STEP','10');
210 fnd_message.raise_error;
211 End If;
212 Close C_Sel1;
213 --
214 -- Call the corresponding datetrack api
215 --
216 dt_api.find_dt_del_modes
217 (p_effective_date => p_effective_date
218 ,p_base_table_name => 'ben_job_rt_f'
219 ,p_base_key_column => 'job_rt_id'
220 ,p_base_key_value => p_base_key_value
221 ,p_parent_table_name1 => 'ben_vrbl_rt_prfl_f'
222 ,p_parent_key_column1 => 'vrbl_rt_prfl_id'
223 ,p_parent_key_value1 => l_parent_key_value1
224 ,p_zap => p_zap
225 ,p_delete => p_delete
226 ,p_future_change => p_future_change
227 ,p_delete_next_change => p_delete_next_change
228 );
229 --
230 hr_utility.set_location(' Leaving:'||l_proc, 10);
231 End find_dt_del_modes;
232 --
233 -- ----------------------------------------------------------------------------
234 -- |-----------------------< upd_effective_end_date >-------------------------|
235 -- ----------------------------------------------------------------------------
236 Procedure upd_effective_end_date
237 (p_effective_date in date
238 ,p_base_key_value in number
239 ,p_new_effective_end_date in date
240 ,p_validation_start_date in date
241 ,p_validation_end_date in date
242 ,p_object_version_number out nocopy number
243 ) is
244 --
245 l_proc varchar2(72) := g_package||'upd_effective_end_date';
246 l_object_version_number number;
247 --
248 Begin
249 hr_utility.set_location('Entering:'||l_proc, 5);
250 --
251 -- Because we are updating a row we must get the next object
252 -- version number.
253 --
254 l_object_version_number :=
255 dt_api.get_object_version_number
256 (p_base_table_name => 'ben_job_rt_f'
257 ,p_base_key_column => 'job_rt_id'
258 ,p_base_key_value => p_base_key_value
259 );
260 --
261 hr_utility.set_location(l_proc, 10);
262 --
263 --
264 -- Update the specified datetrack row setting the effective
265 -- end date to the specified new effective end date.
266 --
267 update ben_job_rt_f t
268 set t.effective_end_date = p_new_effective_end_date
269 , t.object_version_number = l_object_version_number
270 where t.job_rt_id = p_base_key_value
271 and p_effective_date
272 between t.effective_start_date and t.effective_end_date;
273 --
274 --
275 p_object_version_number := l_object_version_number;
276 hr_utility.set_location(' Leaving:'||l_proc, 15);
277 --
278 End upd_effective_end_date;
279 --
280 -- ----------------------------------------------------------------------------
281 -- |---------------------------------< lck >----------------------------------|
282 -- ----------------------------------------------------------------------------
283 Procedure lck
284 (p_effective_date in date
285 ,p_datetrack_mode in varchar2
286 ,p_job_rt_id in number
287 ,p_object_version_number in number
288 ,p_validation_start_date out nocopy date
289 ,p_validation_end_date out nocopy date
290 ) is
291 --
292 l_proc varchar2(72) := g_package||'lck';
293 l_validation_start_date date;
294 l_validation_end_date date;
295 l_argument varchar2(30);
296 --
297 -- Cursor C_Sel1 selects the current locked row as of session date
298 -- ensuring that the object version numbers match.
299 --
300 Cursor C_Sel1 is
301 select
302 job_rt_id
303 ,effective_start_date
304 ,effective_end_date
305 ,job_id
306 ,excld_flag
307 ,business_group_id
308 ,vrbl_rt_prfl_id
309 ,object_version_number
310 ,ordr_num
311 ,jrt_attribute_category
312 ,jrt_attribute1
313 ,jrt_attribute2
314 ,jrt_attribute3
315 ,jrt_attribute4
316 ,jrt_attribute5
317 ,jrt_attribute6
318 ,jrt_attribute7
319 ,jrt_attribute8
320 ,jrt_attribute9
321 ,jrt_attribute10
322 ,jrt_attribute11
323 ,jrt_attribute12
324 ,jrt_attribute13
325 ,jrt_attribute14
326 ,jrt_attribute15
327 ,jrt_attribute16
328 ,jrt_attribute17
329 ,jrt_attribute18
330 ,jrt_attribute19
331 ,jrt_attribute20
332 ,jrt_attribute21
333 ,jrt_attribute22
334 ,jrt_attribute23
335 ,jrt_attribute24
336 ,jrt_attribute25
337 ,jrt_attribute26
338 ,jrt_attribute27
339 ,jrt_attribute28
340 ,jrt_attribute29
341 ,jrt_attribute30
342 from ben_job_rt_f
343 where job_rt_id = p_job_rt_id
344 and p_effective_date
345 between effective_start_date and effective_end_date
346 for update nowait;
347 --
348 --
349 --
350 Begin
351 hr_utility.set_location('Entering:'||l_proc, 5);
352 --
353 -- Ensure that all the mandatory arguments are not null
354 --
355 hr_api.mandatory_arg_error(p_api_name => l_proc
356 ,p_argument => 'effective_date'
357 ,p_argument_value => p_effective_date
358 );
359 --
360 hr_api.mandatory_arg_error(p_api_name => l_proc
361 ,p_argument => 'datetrack_mode'
362 ,p_argument_value => p_datetrack_mode
363 );
364 --
365 hr_api.mandatory_arg_error(p_api_name => l_proc
366 ,p_argument => 'job_rt_id'
367 ,p_argument_value => p_job_rt_id
368 );
369 --
370 hr_api.mandatory_arg_error(p_api_name => l_proc
371 ,p_argument => 'object_version_number'
372 ,p_argument_value => p_object_version_number
373 );
374 --
375 -- Check to ensure the datetrack mode is not INSERT.
376 --
377 If (p_datetrack_mode <> hr_api.g_insert) then
378 --
379 -- We must select and lock the current row.
380 --
381 Open C_Sel1;
382 Fetch C_Sel1 Into ben_jrt_shd.g_old_rec;
383 If C_Sel1%notfound then
384 Close C_Sel1;
385 --
386 -- The primary key is invalid therefore we must error
387 --
388 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
389 fnd_message.raise_error;
390 End If;
391 Close C_Sel1;
392 If (p_object_version_number
393 <> ben_jrt_shd.g_old_rec.object_version_number) Then
394 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
398 --
395 fnd_message.raise_error;
396 End If;
397 --
399 -- Validate the datetrack mode mode getting the validation start
400 -- and end dates for the specified datetrack operation.
401 --
402 dt_api.validate_dt_mode
403 (p_effective_date => p_effective_date
404 ,p_datetrack_mode => p_datetrack_mode
405 ,p_base_table_name => 'ben_job_rt_f'
406 ,p_base_key_column => 'job_rt_id'
407 ,p_base_key_value => p_job_rt_id
408 ,p_parent_table_name1 => 'ben_vrbl_rt_prfl_f'
409 ,p_parent_key_column1 => 'vrbl_rt_prfl_id'
410 ,p_parent_key_value1 => ben_jrt_shd.g_old_rec.vrbl_rt_prfl_id
411 ,p_enforce_foreign_locking => true
412 ,p_validation_start_date => l_validation_start_date
413 ,p_validation_end_date => l_validation_end_date
414 );
415 Else
416 --
417 -- We are doing a datetrack 'INSERT' which is illegal within this
418 -- procedure therefore we must error (note: to lck on insert the
419 -- private procedure ins_lck should be called).
420 --
421 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
422 fnd_message.set_token('PROCEDURE', l_proc);
423 fnd_message.set_token('STEP','20');
424 fnd_message.raise_error;
425 End If;
426 --
427 -- Set the validation start and end date OUT arguments
428 --
429 p_validation_start_date := l_validation_start_date;
430 p_validation_end_date := l_validation_end_date;
431 --
432 hr_utility.set_location(' Leaving:'||l_proc, 30);
433 --
434 -- We need to trap the ORA LOCK exception
435 --
436 Exception
437 When HR_Api.Object_Locked then
438 --
439 -- The object is locked therefore we need to supply a meaningful
440 -- error message.
441 --
442 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
443 fnd_message.set_token('TABLE_NAME', 'BEN_JOB_RT_F');
444 fnd_message.raise_error;
445 End lck;
446 --
447 -- ----------------------------------------------------------------------------
448 -- |-----------------------------< convert_args >-----------------------------|
449 -- ----------------------------------------------------------------------------
450 Function convert_args
451 (p_job_rt_id in number
452 ,p_effective_start_date in date
453 ,p_effective_end_date in date
454 ,p_job_id in number
455 ,p_excld_flag in varchar2
456 ,p_business_group_id in number
457 ,p_vrbl_rt_prfl_id in number
458 ,p_object_version_number in number
459 ,p_ordr_num in number
460 ,p_jrt_attribute_category in varchar2
461 ,p_jrt_attribute1 in varchar2
462 ,p_jrt_attribute2 in varchar2
463 ,p_jrt_attribute3 in varchar2
464 ,p_jrt_attribute4 in varchar2
465 ,p_jrt_attribute5 in varchar2
466 ,p_jrt_attribute6 in varchar2
467 ,p_jrt_attribute7 in varchar2
468 ,p_jrt_attribute8 in varchar2
469 ,p_jrt_attribute9 in varchar2
470 ,p_jrt_attribute10 in varchar2
471 ,p_jrt_attribute11 in varchar2
472 ,p_jrt_attribute12 in varchar2
473 ,p_jrt_attribute13 in varchar2
474 ,p_jrt_attribute14 in varchar2
475 ,p_jrt_attribute15 in varchar2
476 ,p_jrt_attribute16 in varchar2
477 ,p_jrt_attribute17 in varchar2
478 ,p_jrt_attribute18 in varchar2
479 ,p_jrt_attribute19 in varchar2
480 ,p_jrt_attribute20 in varchar2
481 ,p_jrt_attribute21 in varchar2
482 ,p_jrt_attribute22 in varchar2
483 ,p_jrt_attribute23 in varchar2
484 ,p_jrt_attribute24 in varchar2
485 ,p_jrt_attribute25 in varchar2
486 ,p_jrt_attribute26 in varchar2
487 ,p_jrt_attribute27 in varchar2
488 ,p_jrt_attribute28 in varchar2
489 ,p_jrt_attribute29 in varchar2
490 ,p_jrt_attribute30 in varchar2
491 )
492 Return g_rec_type is
493 --
494 l_rec g_rec_type;
495 --
496 Begin
497 --
498 -- Convert arguments into local l_rec structure.
499 --
500 l_rec.job_rt_id := p_job_rt_id;
501 l_rec.effective_start_date := p_effective_start_date;
502 l_rec.effective_end_date := p_effective_end_date;
503 l_rec.job_id := p_job_id;
504 l_rec.excld_flag := p_excld_flag;
505 l_rec.business_group_id := p_business_group_id;
506 l_rec.vrbl_rt_prfl_id := p_vrbl_rt_prfl_id;
507 l_rec.object_version_number := p_object_version_number;
508 l_rec.ordr_num := p_ordr_num;
509 l_rec.jrt_attribute_category := p_jrt_attribute_category;
510 l_rec.jrt_attribute1 := p_jrt_attribute1;
511 l_rec.jrt_attribute2 := p_jrt_attribute2;
512 l_rec.jrt_attribute3 := p_jrt_attribute3;
513 l_rec.jrt_attribute4 := p_jrt_attribute4;
514 l_rec.jrt_attribute5 := p_jrt_attribute5;
515 l_rec.jrt_attribute6 := p_jrt_attribute6;
516 l_rec.jrt_attribute7 := p_jrt_attribute7;
517 l_rec.jrt_attribute8 := p_jrt_attribute8;
518 l_rec.jrt_attribute9 := p_jrt_attribute9;
519 l_rec.jrt_attribute10 := p_jrt_attribute10;
520 l_rec.jrt_attribute11 := p_jrt_attribute11;
521 l_rec.jrt_attribute12 := p_jrt_attribute12;
522 l_rec.jrt_attribute13 := p_jrt_attribute13;
523 l_rec.jrt_attribute14 := p_jrt_attribute14;
524 l_rec.jrt_attribute15 := p_jrt_attribute15;
525 l_rec.jrt_attribute16 := p_jrt_attribute16;
526 l_rec.jrt_attribute17 := p_jrt_attribute17;
527 l_rec.jrt_attribute18 := p_jrt_attribute18;
528 l_rec.jrt_attribute19 := p_jrt_attribute19;
529 l_rec.jrt_attribute20 := p_jrt_attribute20;
530 l_rec.jrt_attribute21 := p_jrt_attribute21;
531 l_rec.jrt_attribute22 := p_jrt_attribute22;
532 l_rec.jrt_attribute23 := p_jrt_attribute23;
533 l_rec.jrt_attribute24 := p_jrt_attribute24;
534 l_rec.jrt_attribute25 := p_jrt_attribute25;
535 l_rec.jrt_attribute26 := p_jrt_attribute26;
536 l_rec.jrt_attribute27 := p_jrt_attribute27;
537 l_rec.jrt_attribute28 := p_jrt_attribute28;
538 l_rec.jrt_attribute29 := p_jrt_attribute29;
539 l_rec.jrt_attribute30 := p_jrt_attribute30;
540 --
541 -- Return the plsql record structure.
542 --
543 Return(l_rec);
544 --
545 End convert_args;
546 --
547 end ben_jrt_shd;