[Home] [Help]
PACKAGE BODY: APPS.PER_SPS_SHD
Source
1 Package Body per_sps_shd as
2 /* $Header: pespsrhi.pkb 120.5.12000000.1 2007/01/22 04:39:24 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_sps_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 Function return_api_dml_status Return Boolean Is
14 --
15 Begin
16 --
17 Return (nvl(g_api_dml, false));
18 --
19 End return_api_dml_status;
20 --
21 -- ----------------------------------------------------------------------------
22 -- |---------------------------< constraint_error >---------------------------|
23 -- ----------------------------------------------------------------------------
24 Procedure constraint_error
25 (p_constraint_name in all_constraints.constraint_name%TYPE
26 ) Is
27 --
28 l_proc varchar2(72) := g_package||'constraint_error';
29 --
30 Begin
31 --
32 If (p_constraint_name = 'PER_SPINAL_POINT_STEPS_F_FK1') Then
33 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
34 fnd_message.set_token('PROCEDURE', l_proc);
35 fnd_message.set_token('STEP','5');
36 fnd_message.raise_error;
37 ElsIf (p_constraint_name = 'PER_SPINAL_POINT_STEPS_F_FK2') Then
38 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
39 fnd_message.set_token('PROCEDURE', l_proc);
40 fnd_message.set_token('STEP','10');
41 fnd_message.raise_error;
42 ElsIf (p_constraint_name = 'PER_SPINAL_POINT_STEPS_F_PK') Then
43 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
44 fnd_message.set_token('PROCEDURE', l_proc);
45 fnd_message.set_token('STEP','15');
46 fnd_message.raise_error;
47 Else
48 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
49 fnd_message.set_token('PROCEDURE', l_proc);
50 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
51 fnd_message.raise_error;
52 End If;
53 --
54 End constraint_error;
55 --
56 -- ----------------------------------------------------------------------------
57 -- |-----------------------------< api_updating >-----------------------------|
58 -- ----------------------------------------------------------------------------
59 Function api_updating
60 (p_effective_date in date
61 ,p_step_id in number
62 ,p_object_version_number in number
63 ) Return Boolean Is
64 --
65 -- Cursor selects the 'current' row from the HR Schema
66 --
67 Cursor C_Sel1 is
68 select
69 step_id
70 ,effective_start_date
71 ,effective_end_date
72 ,business_group_id
73 ,spinal_point_id
74 ,grade_spine_id
75 ,sequence
76 ,request_id
77 ,program_application_id
78 ,program_id
79 ,program_update_date
80 ,information1
81 ,information2
82 ,information3
83 ,information4
84 ,information5
85 ,information6
86 ,information7
87 ,information8
88 ,information9
89 ,information10
90 ,information11
91 ,information12
92 ,information13
93 ,information14
94 ,information15
95 ,information16
96 ,information17
97 ,information18
98 ,information19
99 ,information20
100 ,information21
101 ,information22
102 ,information23
103 ,information24
104 ,information25
105 ,information26
106 ,information27
107 ,information28
108 ,information29
109 ,information30
110 ,information_category
111 ,object_version_number
112 from per_spinal_point_steps_f
113 where step_id = p_step_id
114 and p_effective_date
115 between effective_start_date and effective_end_date;
116 --
117 l_fct_ret boolean;
118 --
119 Begin
120 --
121 If (p_effective_date is null or
122 p_step_id is null or
123 p_object_version_number is null) Then
124 --
125 -- One of the primary key arguments is null therefore we must
126 -- set the returning function value to false
127 --
128 l_fct_ret := false;
129 Else
130 If (p_step_id =
131 per_sps_shd.g_old_rec.step_id and
132 p_object_version_number =
133 per_sps_shd.g_old_rec.object_version_number
134 ) Then
135 --
136 -- The g_old_rec is current therefore we must
137 -- set the returning function to true
138 --
139 l_fct_ret := true;
140 Else
141 --
142 -- Select the current row
143 --
144 Open C_Sel1;
145 Fetch C_Sel1 Into per_sps_shd.g_old_rec;
146 If C_Sel1%notfound Then
147 Close C_Sel1;
148 --
149 -- The primary key is invalid therefore we must error
150 --
151 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
152 fnd_message.raise_error;
153 End If;
154 Close C_Sel1;
155 If (p_object_version_number
156 <> per_sps_shd.g_old_rec.object_version_number) Then
157 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
158 fnd_message.raise_error;
159 End If;
160 l_fct_ret := true;
161 End If;
162 End If;
163 Return (l_fct_ret);
164 --
165 End api_updating;
166 --
167 -- ----------------------------------------------------------------------------
168 -- |---------------------------< find_dt_upd_modes >--------------------------|
169 -- ----------------------------------------------------------------------------
170 Procedure find_dt_upd_modes
171 (p_effective_date in date
172 ,p_base_key_value in number
173 ,p_correction out nocopy boolean
174 ,p_update out nocopy boolean
175 ,p_update_override out nocopy boolean
176 ,p_update_change_insert out nocopy boolean
177 ) is
178 --
179 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
180 --
181 Begin
182 hr_utility.set_location('Entering:'||l_proc, 5);
183 --
184 -- Call the corresponding datetrack api
185 --
186 dt_api.find_dt_upd_modes
187 (p_effective_date => p_effective_date
188 ,p_base_table_name => 'per_spinal_point_steps_f'
189 ,p_base_key_column => 'step_id'
190 ,p_base_key_value => p_base_key_value
191 ,p_correction => p_correction
192 ,p_update => p_update
193 ,p_update_override => p_update_override
194 ,p_update_change_insert => p_update_change_insert
195 );
196 --
197 hr_utility.set_location(' Leaving:'||l_proc, 10);
198 End find_dt_upd_modes;
199 --
200 -- ----------------------------------------------------------------------------
201 -- |---------------------------< find_dt_del_modes >--------------------------|
202 -- ----------------------------------------------------------------------------
203 Procedure find_dt_del_modes
204 (p_effective_date in date
205 ,p_base_key_value in number
206 ,p_zap out nocopy boolean
207 ,p_delete out nocopy boolean
208 ,p_future_change out nocopy boolean
209 ,p_delete_next_change out nocopy boolean
210 ) is
211 --
212 l_proc varchar2(72) := g_package||'find_dt_del_modes';
213 --
214 l_parent_key_value1 number;
215 --
216 Cursor C_Sel1 Is
217 select
218 t.grade_spine_id
219 from per_spinal_point_steps_f t
220 where t.step_id = p_base_key_value
221 and p_effective_date
222 between t.effective_start_date and t.effective_end_date;
223 --
224 Begin
225 hr_utility.set_location('Entering:'||l_proc, 5);
226 Open C_sel1;
227 Fetch C_Sel1 Into
228 l_parent_key_value1;
229 If C_Sel1%NOTFOUND then
230 Close C_Sel1;
231 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
232 fnd_message.set_token('PROCEDURE',l_proc);
233 fnd_message.set_token('STEP','10');
234 fnd_message.raise_error;
235 End If;
236 Close C_Sel1;
237 --
238 -- Call the corresponding datetrack api
239 --
240 dt_api.find_dt_del_modes
241 (p_effective_date => p_effective_date
242 ,p_base_table_name => 'per_spinal_point_steps_f'
243 ,p_base_key_column => 'step_id'
244 ,p_base_key_value => p_base_key_value
245 ,p_parent_table_name1 => 'per_grade_spines_f'
246 ,p_parent_key_column1 => 'grade_spine_id'
247 ,p_parent_key_value1 => l_parent_key_value1
248 ,p_zap => p_zap
249 ,p_delete => p_delete
250 ,p_future_change => p_future_change
251 ,p_delete_next_change => p_delete_next_change
252 );
253 --
254 hr_utility.set_location(' Leaving:'||l_proc, 10);
255 End find_dt_del_modes;
256 --
257 -- ----------------------------------------------------------------------------
258 -- |-----------------------< upd_effective_end_date >-------------------------|
259 -- ----------------------------------------------------------------------------
260 Procedure upd_effective_end_date
261 (p_effective_date in date
262 ,p_base_key_value in number
263 ,p_new_effective_end_date in date
264 ,p_validation_start_date in date
265 ,p_validation_end_date in date
266 ,p_object_version_number out nocopy number
267 ) is
268 --
269 l_proc varchar2(72) := g_package||'upd_effective_end_date';
270 l_object_version_number number;
271 --
272 Begin
273 hr_utility.set_location('Entering:'||l_proc, 5);
274 --
275 -- Because we are updating a row we must get the next object
276 -- version number.
277 --
278 l_object_version_number :=
279 dt_api.get_object_version_number
280 (p_base_table_name => 'per_spinal_point_steps_f'
281 ,p_base_key_column => 'step_id'
282 ,p_base_key_value => p_base_key_value
283 );
284 --
285 hr_utility.set_location(l_proc, 10);
286 per_sps_shd.g_api_dml := true; -- Set the api dml status
287 --
288 -- Update the specified datetrack row setting the effective
289 -- end date to the specified new effective end date.
290 --
291 update per_spinal_point_steps_f t
292 set t.effective_end_date = p_new_effective_end_date
293 , t.object_version_number = l_object_version_number
294 where t.step_id = p_base_key_value
295 and p_effective_date
296 between t.effective_start_date and t.effective_end_date;
297 --
298 per_sps_shd.g_api_dml := false; -- Unset the api dml status
299 p_object_version_number := l_object_version_number;
300 hr_utility.set_location(' Leaving:'||l_proc, 15);
301 --
302 Exception
303 When Others Then
304 per_sps_shd.g_api_dml := false; -- Unset the api dml status
305 Raise;
306 --
307 End upd_effective_end_date;
308 --
309 -- ----------------------------------------------------------------------------
310 -- |---------------------------------< lck >----------------------------------|
311 -- ----------------------------------------------------------------------------
312 Procedure lck
313 (p_effective_date in date
314 ,p_datetrack_mode in varchar2
315 ,p_step_id in number
316 ,p_object_version_number in number
317 ,p_validation_start_date out nocopy date
318 ,p_validation_end_date out nocopy date
319 ) is
320 --
321 l_proc varchar2(72) := g_package||'lck';
322 l_validation_start_date date;
323 l_validation_end_date date;
324 l_argument varchar2(30);
325 --
326 -- Cursor C_Sel1 selects the current locked row as of session date
327 -- ensuring that the object version numbers match.
328 --
329 Cursor C_Sel1 is
330 select
331 step_id
332 ,effective_start_date
333 ,effective_end_date
334 ,business_group_id
335 ,spinal_point_id
336 ,grade_spine_id
337 ,sequence
338 ,request_id
339 ,program_application_id
340 ,program_id
341 ,program_update_date
342 ,information1
343 ,information2
344 ,information3
345 ,information4
346 ,information5
347 ,information6
348 ,information7
349 ,information8
350 ,information9
351 ,information10
352 ,information11
353 ,information12
354 ,information13
355 ,information14
356 ,information15
357 ,information16
358 ,information17
359 ,information18
360 ,information19
361 ,information20
362 ,information21
363 ,information22
364 ,information23
365 ,information24
366 ,information25
367 ,information26
368 ,information27
369 ,information28
370 ,information29
371 ,information30
372 ,information_category
373 ,object_version_number
374 from per_spinal_point_steps_f
375 where step_id = p_step_id
376 and p_effective_date
377 between effective_start_date and effective_end_date
378 for update nowait;
379 --
380 --
381 --
382 Begin
383 hr_utility.set_location('Entering:'||l_proc, 5);
384 --
385 -- Ensure that all the mandatory arguments are not null
386 --
387 hr_api.mandatory_arg_error(p_api_name => l_proc
388 ,p_argument => 'effective_date'
389 ,p_argument_value => p_effective_date
390 );
391 --
392 hr_api.mandatory_arg_error(p_api_name => l_proc
393 ,p_argument => 'datetrack_mode'
394 ,p_argument_value => p_datetrack_mode
395 );
396 --
397 hr_api.mandatory_arg_error(p_api_name => l_proc
398 ,p_argument => 'step_id'
399 ,p_argument_value => p_step_id
400 );
401 --
402 hr_api.mandatory_arg_error(p_api_name => l_proc
403 ,p_argument => 'object_version_number'
404 ,p_argument_value => p_object_version_number
405 );
406 hr_utility.set_location(l_proc, 10);
407
408 --
409 -- Check to ensure the datetrack mode is not INSERT.
410 --
411 If (p_datetrack_mode <> hr_api.g_insert) then
412 --
413 -- We must select and lock the current row.
414 --
415 Open C_Sel1;
416 Fetch C_Sel1 Into per_sps_shd.g_old_rec;
417 If C_Sel1%notfound then
418 Close C_Sel1;
419 --
420 -- The primary key is invalid therefore we must error
421 --
422 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
423 fnd_message.raise_error;
424 End If;
425 Close C_Sel1;
426 If (p_object_version_number
427 <> per_sps_shd.g_old_rec.object_version_number) Then
428 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
429 fnd_message.raise_error;
430 End If;
431
432 hr_utility.set_location(l_proc, 20);
433
434 --
435 --
436 -- Validate the datetrack mode mode getting the validation start
437 -- and end dates for the specified datetrack operation.
438 --
439 dt_api.validate_dt_mode
440 (p_effective_date => p_effective_date
441 ,p_datetrack_mode => p_datetrack_mode
442 ,p_base_table_name => 'per_spinal_point_steps_f'
443 ,p_base_key_column => 'step_id'
444 ,p_base_key_value => p_step_id
445 ,p_parent_table_name1 => 'per_grade_spines_f'
446 ,p_parent_key_column1 => 'grade_spine_id'
447 ,p_parent_key_value1 => per_sps_shd.g_old_rec.grade_spine_id
448 ,p_child_table_name1 => 'per_cagr_entitlement_lines_f'
449 ,p_child_key_column1 => 'cagr_entitlement_line_id'
450 ,p_child_table_name2 => 'hr_all_positions_f'
451 ,p_child_key_column2 => 'position_id'
452 ,p_child_alt_base_key_column2 => 'entry_step_id'
453 ,p_child_table_name3 => 'per_all_assignments_f'
457 ,p_validation_start_date => l_validation_start_date
454 ,p_child_key_column3 => 'assignment_id'
455 ,p_child_alt_base_key_column3 => 'special_ceiling_step_id'
456 ,p_enforce_foreign_locking => true
458 ,p_validation_end_date => l_validation_end_date
459 );
460 Else
461 hr_utility.set_location(l_proc, 30);
462 --
463 -- We are doing a datetrack 'INSERT' which is illegal within this
464 -- procedure therefore we must error (note: to lck on insert the
465 -- private procedure ins_lck should be called).
466 --
467 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
468 fnd_message.set_token('PROCEDURE', l_proc);
469 fnd_message.set_token('STEP','20');
470 fnd_message.raise_error;
471 End If;
472 --
473 -- Set the validation start and end date OUT arguments
474 --
475 p_validation_start_date := l_validation_start_date;
476 p_validation_end_date := l_validation_end_date;
477 --
478 hr_utility.set_location(' Leaving:'||l_proc, 40);
479 --
480 -- We need to trap the ORA LOCK exception
481 --
482 Exception
483 When HR_Api.Object_Locked then
484 --
485 -- The object is locked therefore we need to supply a meaningful
486 -- error message.
487 --
488 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
489 fnd_message.set_token('TABLE_NAME', 'per_spinal_point_steps_f');
490 fnd_message.raise_error;
491 End lck;
492 --
493 -- ----------------------------------------------------------------------------
494 -- |-----------------------------< convert_args >-----------------------------|
495 -- ----------------------------------------------------------------------------
496 Function convert_args
497 (p_step_id in number
498 ,p_effective_start_date in date
499 ,p_effective_end_date in date
500 ,p_business_group_id in number
501 ,p_spinal_point_id in number
502 ,p_grade_spine_id in number
503 ,p_sequence in number
504 ,p_request_id in number
505 ,p_program_application_id in number
506 ,p_program_id in number
507 ,p_program_update_date in date
508 ,p_information1 in varchar2
509 ,p_information2 in varchar2
510 ,p_information3 in varchar2
511 ,p_information4 in varchar2
512 ,p_information5 in varchar2
513 ,p_information6 in varchar2
514 ,p_information7 in varchar2
515 ,p_information8 in varchar2
516 ,p_information9 in varchar2
517 ,p_information10 in varchar2
518 ,p_information11 in varchar2
519 ,p_information12 in varchar2
520 ,p_information13 in varchar2
521 ,p_information14 in varchar2
522 ,p_information15 in varchar2
523 ,p_information16 in varchar2
524 ,p_information17 in varchar2
525 ,p_information18 in varchar2
526 ,p_information19 in varchar2
527 ,p_information20 in varchar2
528 ,p_information21 in varchar2
529 ,p_information22 in varchar2
530 ,p_information23 in varchar2
531 ,p_information24 in varchar2
532 ,p_information25 in varchar2
533 ,p_information26 in varchar2
534 ,p_information27 in varchar2
535 ,p_information28 in varchar2
536 ,p_information29 in varchar2
537 ,p_information30 in varchar2
538 ,p_information_category in varchar2
539 ,p_object_version_number in number
540 )
541 Return g_rec_type is
542 --
543 l_rec g_rec_type;
544 --
545 Begin
546 --
547 -- Convert arguments into local l_rec structure.
548 --
549 l_rec.step_id := p_step_id;
550 l_rec.effective_start_date := p_effective_start_date;
551 l_rec.effective_end_date := p_effective_end_date;
552 l_rec.business_group_id := p_business_group_id;
553 l_rec.spinal_point_id := p_spinal_point_id;
554 l_rec.grade_spine_id := p_grade_spine_id;
555 l_rec.sequence := p_sequence;
556 l_rec.request_id := p_request_id;
557 l_rec.program_application_id := p_program_application_id;
558 l_rec.program_id := p_program_id;
559 l_rec.program_update_date := p_program_update_date;
560 l_rec.information1 := p_information1;
561 l_rec.information2 := p_information2;
562 l_rec.information3 := p_information3;
563 l_rec.information4 := p_information4;
564 l_rec.information5 := p_information5;
565 l_rec.information6 := p_information6;
566 l_rec.information7 := p_information7;
567 l_rec.information8 := p_information8;
568 l_rec.information9 := p_information9;
569 l_rec.information10 := p_information10;
570 l_rec.information11 := p_information11;
571 l_rec.information12 := p_information12;
572 l_rec.information13 := p_information13;
573 l_rec.information14 := p_information14;
574 l_rec.information15 := p_information15;
575 l_rec.information16 := p_information16;
576 l_rec.information17 := p_information17;
577 l_rec.information18 := p_information18;
578 l_rec.information19 := p_information19;
579 l_rec.information20 := p_information20;
580 l_rec.information21 := p_information21;
581 l_rec.information22 := p_information22;
582 l_rec.information23 := p_information23;
583 l_rec.information24 := p_information24;
584 l_rec.information25 := p_information25;
585 l_rec.information26 := p_information26;
586 l_rec.information27 := p_information27;
587 l_rec.information28 := p_information28;
588 l_rec.information29 := p_information29;
589 l_rec.information30 := p_information30;
590 l_rec.information_category := p_information_category;
591 l_rec.object_version_number := p_object_version_number;
592 --
593 -- Return the plsql record structure.
594 --
595 Return(l_rec);
596 --
597 End convert_args;
598 --
599 end per_sps_shd;