[Home] [Help]
PACKAGE BODY: APPS.PQH_ASA_SHD
Source
1 Package Body pqh_asa_shd as
2 /* $Header: pqasarhi.pkb 115.3 2002/11/27 00:35:21 rpasapul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_asa_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 = 'PQH_ASSIGN_ACCOMMODATIONS_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_assignment_acco_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 assignment_acco_id
49 ,effective_start_date
50 ,effective_end_date
51 ,business_group_id
52 ,assignment_id
53 ,accommodation_given
54 ,temporary_assignment
55 ,accommodation_id
56 ,acceptance_date
57 ,moving_date
58 ,refusal_date
59 ,comments
60 ,indemnity_entitlement
61 ,indemnity_amount
62 ,type_of_payment
63 ,information_category
64 ,information1
65 ,information2
66 ,information3
67 ,information4
68 ,information5
69 ,information6
70 ,information7
71 ,information8
72 ,information9
73 ,information10
74 ,information11
75 ,information12
76 ,information13
77 ,information14
78 ,information15
79 ,information16
80 ,information17
81 ,information18
82 ,information19
83 ,information20
84 ,information21
85 ,information22
86 ,information23
87 ,information24
88 ,information25
89 ,information26
90 ,information27
91 ,information28
92 ,information29
93 ,information30
94 ,attribute_category
95 ,attribute1
96 ,attribute2
97 ,attribute3
98 ,attribute4
99 ,attribute5
100 ,attribute6
101 ,attribute7
102 ,attribute8
103 ,attribute9
104 ,attribute10
105 ,attribute11
106 ,attribute12
107 ,attribute13
108 ,attribute14
109 ,attribute15
110 ,attribute16
111 ,attribute17
112 ,attribute18
113 ,attribute19
114 ,attribute20
115 ,attribute21
116 ,attribute22
117 ,attribute23
118 ,attribute24
119 ,attribute25
120 ,attribute26
121 ,attribute27
122 ,attribute28
123 ,attribute29
124 ,attribute30
125 ,object_version_number
126 ,reason_for_no_acco
127 ,indemnity_currency
128 from pqh_assign_accommodations_f
129 where assignment_acco_id = p_assignment_acco_id
130 and p_effective_date
131 between effective_start_date and effective_end_date;
132 --
133 l_fct_ret boolean;
134 --
135 Begin
136 --
137 If (p_effective_date is null or
138 p_assignment_acco_id is null or
139 p_object_version_number is null) Then
140 --
141 -- One of the primary key arguments is null therefore we must
142 -- set the returning function value to false
143 --
144 l_fct_ret := false;
145 Else
146 If (p_assignment_acco_id =
147 pqh_asa_shd.g_old_rec.assignment_acco_id and
148 p_object_version_number =
149 pqh_asa_shd.g_old_rec.object_version_number
150 ) Then
151 --
152 -- The g_old_rec is current therefore we must
153 -- set the returning function to true
154 --
155 l_fct_ret := true;
156 Else
157 --
158 -- Select the current row
159 --
160 Open C_Sel1;
161 Fetch C_Sel1 Into pqh_asa_shd.g_old_rec;
162 If C_Sel1%notfound Then
163 Close C_Sel1;
164 --
165 -- The primary key is invalid therefore we must error
166 --
167 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
168 fnd_message.raise_error;
169 End If;
170 Close C_Sel1;
171 If (p_object_version_number
172 <> pqh_asa_shd.g_old_rec.object_version_number) Then
173 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
174 fnd_message.raise_error;
175 End If;
176 l_fct_ret := true;
177 End If;
178 End If;
179 Return (l_fct_ret);
180 --
181 End api_updating;
182 --
183 -- ----------------------------------------------------------------------------
184 -- |---------------------------< find_dt_upd_modes >--------------------------|
185 -- ----------------------------------------------------------------------------
186 Procedure find_dt_upd_modes
187 (p_effective_date in date
188 ,p_base_key_value in number
189 ,p_correction out nocopy boolean
190 ,p_update out nocopy boolean
191 ,p_update_override out nocopy boolean
192 ,p_update_change_insert out nocopy boolean
193 ) is
194 --
195 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
196 --
197 Begin
198 hr_utility.set_location('Entering:'||l_proc, 5);
199 --
200 -- Call the corresponding datetrack api
201 --
202 dt_api.find_dt_upd_modes
203 (p_effective_date => p_effective_date
204 ,p_base_table_name => 'pqh_assign_accommodations_f'
208 ,p_update => p_update
205 ,p_base_key_column => 'assignment_acco_id'
206 ,p_base_key_value => p_base_key_value
207 ,p_correction => p_correction
209 ,p_update_override => p_update_override
210 ,p_update_change_insert => p_update_change_insert
211 );
212 --
213 hr_utility.set_location(' Leaving:'||l_proc, 10);
214 End find_dt_upd_modes;
215 --
216 -- ----------------------------------------------------------------------------
217 -- |---------------------------< find_dt_del_modes >--------------------------|
218 -- ----------------------------------------------------------------------------
219 Procedure find_dt_del_modes
220 (p_effective_date in date
221 ,p_base_key_value in number
222 ,p_zap out nocopy boolean
223 ,p_delete out nocopy boolean
224 ,p_future_change out nocopy boolean
225 ,p_delete_next_change out nocopy boolean
226 ) is
227 --
228 l_proc varchar2(72) := g_package||'find_dt_del_modes';
229 --
230 l_parent_key_value1 number;
231 --
232 Cursor C_Sel1 Is
233 select
234 t.accommodation_id
235 from pqh_assign_accommodations_f t
236 where t.assignment_acco_id = p_base_key_value
237 and p_effective_date
238 between t.effective_start_date and t.effective_end_date;
239 --
240 Begin
241 hr_utility.set_location('Entering:'||l_proc, 5);
242 Open C_sel1;
243 Fetch C_Sel1 Into
244 l_parent_key_value1;
245 If C_Sel1%NOTFOUND then
246 Close C_Sel1;
247 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
248 fnd_message.set_token('PROCEDURE',l_proc);
249 fnd_message.set_token('STEP','10');
250 fnd_message.raise_error;
251 End If;
252 Close C_Sel1;
253 --
254 -- Call the corresponding datetrack api
255 --
256 dt_api.find_dt_del_modes
257 (p_effective_date => p_effective_date
258 ,p_base_table_name => 'pqh_assign_accommodations_f'
259 ,p_base_key_column => 'assignment_acco_id'
260 ,p_base_key_value => p_base_key_value
261 ,p_parent_table_name1 => 'pqh_accommodations_f'
262 ,p_parent_key_column1 => 'accommodation_id'
263 ,p_parent_key_value1 => l_parent_key_value1
264 ,p_zap => p_zap
265 ,p_delete => p_delete
266 ,p_future_change => p_future_change
267 ,p_delete_next_change => p_delete_next_change
268 );
269 --
270 hr_utility.set_location(' Leaving:'||l_proc, 10);
271 End find_dt_del_modes;
272 --
273 -- ----------------------------------------------------------------------------
274 -- |-----------------------< upd_effective_end_date >-------------------------|
275 -- ----------------------------------------------------------------------------
276 Procedure upd_effective_end_date
277 (p_effective_date in date
278 ,p_base_key_value in number
279 ,p_new_effective_end_date in date
280 ,p_validation_start_date in date
281 ,p_validation_end_date in date
282 ,p_object_version_number out nocopy number
283 ) is
284 --
285 l_proc varchar2(72) := g_package||'upd_effective_end_date';
286 l_object_version_number number;
287 --
288 Begin
289 hr_utility.set_location('Entering:'||l_proc, 5);
290 --
291 -- Because we are updating a row we must get the next object
292 -- version number.
293 --
294 l_object_version_number :=
295 dt_api.get_object_version_number
296 (p_base_table_name => 'pqh_assign_accommodations_f'
297 ,p_base_key_column => 'assignment_acco_id'
298 ,p_base_key_value => p_base_key_value
299 );
300 --
301 hr_utility.set_location(l_proc, 10);
302 --
303 --
304 -- Update the specified datetrack row setting the effective
305 -- end date to the specified new effective end date.
306 --
307 update pqh_assign_accommodations_f t
308 set t.effective_end_date = p_new_effective_end_date
309 , t.object_version_number = l_object_version_number
310 where t.assignment_acco_id = p_base_key_value
311 and p_effective_date
312 between t.effective_start_date and t.effective_end_date;
313 --
314 --
315 p_object_version_number := l_object_version_number;
316 hr_utility.set_location(' Leaving:'||l_proc, 15);
317 --
318 End upd_effective_end_date;
319 --
320 -- ----------------------------------------------------------------------------
321 -- |---------------------------------< lck >----------------------------------|
322 -- ----------------------------------------------------------------------------
323 Procedure lck
324 (p_effective_date in date
325 ,p_datetrack_mode in varchar2
326 ,p_assignment_acco_id in number
327 ,p_object_version_number in number
328 ,p_validation_start_date out nocopy date
329 ,p_validation_end_date out nocopy date
330 ) is
331 --
332 l_proc varchar2(72) := g_package||'lck';
333 l_validation_start_date date;
334 l_validation_end_date date;
335 l_argument varchar2(30);
336 --
340 Cursor C_Sel1 is
337 -- Cursor C_Sel1 selects the current locked row as of session date
338 -- ensuring that the object version numbers match.
339 --
341 select
342 assignment_acco_id
343 ,effective_start_date
344 ,effective_end_date
345 ,business_group_id
346 ,assignment_id
347 ,accommodation_given
348 ,temporary_assignment
349 ,accommodation_id
350 ,acceptance_date
351 ,moving_date
352 ,refusal_date
353 ,comments
354 ,indemnity_entitlement
355 ,indemnity_amount
356 ,type_of_payment
357 ,information_category
358 ,information1
359 ,information2
360 ,information3
361 ,information4
362 ,information5
363 ,information6
364 ,information7
365 ,information8
366 ,information9
370 ,information13
367 ,information10
368 ,information11
369 ,information12
371 ,information14
372 ,information15
373 ,information16
374 ,information17
375 ,information18
376 ,information19
377 ,information20
378 ,information21
379 ,information22
380 ,information23
381 ,information24
382 ,information25
383 ,information26
384 ,information27
385 ,information28
386 ,information29
387 ,information30
388 ,attribute_category
389 ,attribute1
390 ,attribute2
391 ,attribute3
392 ,attribute4
393 ,attribute5
394 ,attribute6
395 ,attribute7
396 ,attribute8
397 ,attribute9
398 ,attribute10
399 ,attribute11
400 ,attribute12
401 ,attribute13
402 ,attribute14
403 ,attribute15
404 ,attribute16
405 ,attribute17
406 ,attribute18
407 ,attribute19
408 ,attribute20
409 ,attribute21
410 ,attribute22
411 ,attribute23
412 ,attribute24
413 ,attribute25
414 ,attribute26
415 ,attribute27
416 ,attribute28
417 ,attribute29
418 ,attribute30
419 ,object_version_number
420 ,reason_for_no_acco
421 ,indemnity_currency
422 from pqh_assign_accommodations_f
423 where assignment_acco_id = p_assignment_acco_id
424 and p_effective_date
425 between effective_start_date and effective_end_date
426 for update nowait;
427 --
428 --
429 --
430 Begin
431 hr_utility.set_location('Entering:'||l_proc, 5);
432 --
433 -- Ensure that all the mandatory arguments are not null
434 --
435 hr_api.mandatory_arg_error(p_api_name => l_proc
436 ,p_argument => 'effective_date'
437 ,p_argument_value => p_effective_date
438 );
439 --
440 hr_api.mandatory_arg_error(p_api_name => l_proc
441 ,p_argument => 'datetrack_mode'
442 ,p_argument_value => p_datetrack_mode
443 );
444 --
445 hr_api.mandatory_arg_error(p_api_name => l_proc
446 ,p_argument => 'assignment_acco_id'
447 ,p_argument_value => p_assignment_acco_id
448 );
449 --
450 hr_api.mandatory_arg_error(p_api_name => l_proc
451 ,p_argument => 'object_version_number'
452 ,p_argument_value => p_object_version_number
453 );
454 --
455 -- Check to ensure the datetrack mode is not INSERT.
456 --
457 If (p_datetrack_mode <> hr_api.g_insert) then
458 --
459 -- We must select and lock the current row.
460 --
461 Open C_Sel1;
462 Fetch C_Sel1 Into pqh_asa_shd.g_old_rec;
463 If C_Sel1%notfound then
464 Close C_Sel1;
465 --
466 -- The primary key is invalid therefore we must error
467 --
468 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
469 fnd_message.raise_error;
470 End If;
471 Close C_Sel1;
472 If (p_object_version_number
473 <> pqh_asa_shd.g_old_rec.object_version_number) Then
474 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
475 fnd_message.raise_error;
476 End If;
477 --
478 --
479 -- Validate the datetrack mode mode getting the validation start
480 -- and end dates for the specified datetrack operation.
481 --
482 dt_api.validate_dt_mode
486 ,p_base_key_column => 'assignment_acco_id'
483 (p_effective_date => p_effective_date
484 ,p_datetrack_mode => p_datetrack_mode
485 ,p_base_table_name => 'pqh_assign_accommodations_f'
487 ,p_base_key_value => p_assignment_acco_id
488 ,p_parent_table_name1 => 'pqh_accommodations_f'
489 ,p_parent_key_column1 => 'accommodation_id'
490 ,p_parent_key_value1 => pqh_asa_shd.g_old_rec.accommodation_id
491 ,p_enforce_foreign_locking => true
492 ,p_validation_start_date => l_validation_start_date
493 ,p_validation_end_date => l_validation_end_date
494 );
495 Else
496 --
497 -- We are doing a datetrack 'INSERT' which is illegal within this
498 -- procedure therefore we must error (note: to lck on insert the
499 -- private procedure ins_lck should be called).
500 --
501 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
502 fnd_message.set_token('PROCEDURE', l_proc);
503 fnd_message.set_token('STEP','20');
504 fnd_message.raise_error;
505 End If;
506 --
507 -- Set the validation start and end date OUT arguments
508 --
509 p_validation_start_date := l_validation_start_date;
510 p_validation_end_date := l_validation_end_date;
511 --
512 hr_utility.set_location(' Leaving:'||l_proc, 30);
513 --
514 -- We need to trap the ORA LOCK exception
515 --
516 Exception
517 When HR_Api.Object_Locked then
518 --
519 -- The object is locked therefore we need to supply a meaningful
520 -- error message.
521 --
522 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
523 fnd_message.set_token('TABLE_NAME', 'pqh_assign_accommodations_f');
524 fnd_message.raise_error;
525 End lck;
526 --
527 -- ----------------------------------------------------------------------------
528 -- |-----------------------------< convert_args >-----------------------------|
529 -- ----------------------------------------------------------------------------
530 Function convert_args
531 (p_assignment_acco_id in number
532 ,p_effective_start_date in date
533 ,p_effective_end_date in date
534 ,p_business_group_id in number
535 ,p_assignment_id in number
536 ,p_accommodation_given in varchar2
537 ,p_temporary_assignment in varchar2
538 ,p_accommodation_id in number
539 ,p_acceptance_date in date
540 ,p_moving_date in date
541 ,p_refusal_date in date
542 ,p_comments in varchar2
543 ,p_indemnity_entitlement in varchar2
544 ,p_indemnity_amount in number
548 ,p_information2 in varchar2
545 ,p_type_of_payment in varchar2
546 ,p_information_category in varchar2
547 ,p_information1 in varchar2
549 ,p_information3 in varchar2
550 ,p_information4 in varchar2
551 ,p_information5 in varchar2
552 ,p_information6 in varchar2
553 ,p_information7 in varchar2
554 ,p_information8 in varchar2
555 ,p_information9 in varchar2
556 ,p_information10 in varchar2
557 ,p_information11 in varchar2
558 ,p_information12 in varchar2
559 ,p_information13 in varchar2
560 ,p_information14 in varchar2
561 ,p_information15 in varchar2
562 ,p_information16 in varchar2
563 ,p_information17 in varchar2
564 ,p_information18 in varchar2
565 ,p_information19 in varchar2
566 ,p_information20 in varchar2
567 ,p_information21 in varchar2
568 ,p_information22 in varchar2
569 ,p_information23 in varchar2
570 ,p_information24 in varchar2
571 ,p_information25 in varchar2
572 ,p_information26 in varchar2
573 ,p_information27 in varchar2
574 ,p_information28 in varchar2
575 ,p_information29 in varchar2
576 ,p_information30 in varchar2
577 ,p_attribute_category in varchar2
578 ,p_attribute1 in varchar2
579 ,p_attribute2 in varchar2
580 ,p_attribute3 in varchar2
581 ,p_attribute4 in varchar2
582 ,p_attribute5 in varchar2
583 ,p_attribute6 in varchar2
584 ,p_attribute7 in varchar2
585 ,p_attribute8 in varchar2
586 ,p_attribute9 in varchar2
587 ,p_attribute10 in varchar2
588 ,p_attribute11 in varchar2
589 ,p_attribute12 in varchar2
590 ,p_attribute13 in varchar2
591 ,p_attribute14 in varchar2
592 ,p_attribute15 in varchar2
593 ,p_attribute16 in varchar2
594 ,p_attribute17 in varchar2
595 ,p_attribute18 in varchar2
596 ,p_attribute19 in varchar2
600 ,p_attribute23 in varchar2
597 ,p_attribute20 in varchar2
598 ,p_attribute21 in varchar2
599 ,p_attribute22 in varchar2
601 ,p_attribute24 in varchar2
602 ,p_attribute25 in varchar2
603 ,p_attribute26 in varchar2
604 ,p_attribute27 in varchar2
605 ,p_attribute28 in varchar2
606 ,p_attribute29 in varchar2
607 ,p_attribute30 in varchar2
608 ,p_object_version_number in number
609 ,p_reason_for_no_acco in varchar2
610 ,p_indemnity_currency in varchar2
611 )
612 Return g_rec_type is
613 --
614 l_rec g_rec_type;
615 --
616 Begin
617 --
618 -- Convert arguments into local l_rec structure.
619 --
620 l_rec.assignment_acco_id := p_assignment_acco_id;
621 l_rec.effective_start_date := p_effective_start_date;
622 l_rec.effective_end_date := p_effective_end_date;
623 l_rec.business_group_id := p_business_group_id;
624 l_rec.assignment_id := p_assignment_id;
625 l_rec.accommodation_given := p_accommodation_given;
626 l_rec.temporary_assignment := p_temporary_assignment;
627 l_rec.accommodation_id := p_accommodation_id;
628 l_rec.acceptance_date := p_acceptance_date;
629 l_rec.moving_date := p_moving_date;
630 l_rec.refusal_date := p_refusal_date;
631 l_rec.comments := p_comments;
632 l_rec.indemnity_entitlement := p_indemnity_entitlement;
633 l_rec.indemnity_amount := p_indemnity_amount;
634 l_rec.type_of_payment := p_type_of_payment;
635 l_rec.information_category := p_information_category;
636 l_rec.information1 := p_information1;
637 l_rec.information2 := p_information2;
638 l_rec.information3 := p_information3;
639 l_rec.information4 := p_information4;
640 l_rec.information5 := p_information5;
641 l_rec.information6 := p_information6;
642 l_rec.information7 := p_information7;
643 l_rec.information8 := p_information8;
644 l_rec.information9 := p_information9;
645 l_rec.information10 := p_information10;
646 l_rec.information11 := p_information11;
647 l_rec.information12 := p_information12;
648 l_rec.information13 := p_information13;
649 l_rec.information14 := p_information14;
650 l_rec.information15 := p_information15;
651 l_rec.information16 := p_information16;
652 l_rec.information17 := p_information17;
653 l_rec.information18 := p_information18;
654 l_rec.information19 := p_information19;
655 l_rec.information20 := p_information20;
656 l_rec.information21 := p_information21;
657 l_rec.information22 := p_information22;
658 l_rec.information23 := p_information23;
659 l_rec.information24 := p_information24;
660 l_rec.information25 := p_information25;
661 l_rec.information26 := p_information26;
662 l_rec.information27 := p_information27;
663 l_rec.information28 := p_information28;
664 l_rec.information29 := p_information29;
665 l_rec.information30 := p_information30;
666 l_rec.attribute_category := p_attribute_category;
667 l_rec.attribute1 := p_attribute1;
668 l_rec.attribute2 := p_attribute2;
669 l_rec.attribute3 := p_attribute3;
670 l_rec.attribute4 := p_attribute4;
671 l_rec.attribute5 := p_attribute5;
672 l_rec.attribute6 := p_attribute6;
673 l_rec.attribute7 := p_attribute7;
674 l_rec.attribute8 := p_attribute8;
675 l_rec.attribute9 := p_attribute9;
676 l_rec.attribute10 := p_attribute10;
677 l_rec.attribute11 := p_attribute11;
678 l_rec.attribute12 := p_attribute12;
679 l_rec.attribute13 := p_attribute13;
680 l_rec.attribute14 := p_attribute14;
681 l_rec.attribute15 := p_attribute15;
682 l_rec.attribute16 := p_attribute16;
683 l_rec.attribute17 := p_attribute17;
684 l_rec.attribute18 := p_attribute18;
685 l_rec.attribute19 := p_attribute19;
686 l_rec.attribute20 := p_attribute20;
687 l_rec.attribute21 := p_attribute21;
688 l_rec.attribute22 := p_attribute22;
689 l_rec.attribute23 := p_attribute23;
690 l_rec.attribute24 := p_attribute24;
691 l_rec.attribute25 := p_attribute25;
692 l_rec.attribute26 := p_attribute26;
693 l_rec.attribute27 := p_attribute27;
694 l_rec.attribute28 := p_attribute28;
695 l_rec.attribute29 := p_attribute29;
696 l_rec.attribute30 := p_attribute30;
697 l_rec.object_version_number := p_object_version_number;
698 l_rec.reason_for_no_acco := p_reason_for_no_acco;
699 l_rec.indemnity_currency := p_indemnity_currency;
700 --
701 -- Return the plsql record structure.
702 --
703 Return(l_rec);
704 --
705 End convert_args;
706 --
707 end pqh_asa_shd;