[Home] [Help]
PACKAGE BODY: APPS.PQP_VAL_SHD
Source
1 Package Body pqp_val_shd as
2 /* $Header: pqvalrhi.pkb 120.4 2011/09/16 06:12:51 vepravee noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqp_val_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 = 'PQP_VEHICLE_ALLOCATIONS_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_vehicle_allocation_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 vehicle_allocation_id
49 ,effective_start_date
50 ,effective_end_date
51 ,assignment_id
52 ,business_group_id
53 ,across_assignments
54 ,vehicle_repository_id
55 ,usage_type
56 ,capital_contribution
57 ,private_contribution
58 ,default_vehicle
59 ,fuel_card
60 ,fuel_card_number
61 ,calculation_method
62 ,rates_table_id
63 ,element_type_id
64 ,private_use_flag
65 ,insurance_number
66 ,insurance_expiry_date
67 ,val_attribute_category
68 ,val_attribute1
69 ,val_attribute2
70 ,val_attribute3
71 ,val_attribute4
72 ,val_attribute5
73 ,val_attribute6
74 ,val_attribute7
75 ,val_attribute8
76 ,val_attribute9
77 ,val_attribute10
78 ,val_attribute11
79 ,val_attribute12
80 ,val_attribute13
81 ,val_attribute14
82 ,val_attribute15
83 ,val_attribute16
84 ,val_attribute17
85 ,val_attribute18
86 ,val_attribute19
87 ,val_attribute20
88 ,val_information_category
89 ,val_information1
90 ,val_information2
91 ,val_information3
92 ,val_information4
93 ,val_information5
94 ,val_information6
95 ,val_information7
96 ,val_information8
97 ,val_information9
98 ,val_information10
99 ,val_information11
100 ,val_information12
101 ,val_information13
102 ,val_information14
103 ,val_information15
104 ,val_information16
105 ,val_information17
106 ,val_information18
107 ,val_information19
108 ,val_information20
109 ,object_version_number
110 ,fuel_benefit
111 ,sliding_rates_info
112 from pqp_vehicle_allocations_f
113 where vehicle_allocation_id = p_vehicle_allocation_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
122 If (p_effective_date is null or
123 p_vehicle_allocation_id is null or
124 p_object_version_number is null) Then
125 --
126 -- One of the primary key arguments is null therefore we must
127 -- set the returning function value to false
128 --
129 l_fct_ret := false;
130 Else
131 If (p_vehicle_allocation_id =
132 pqp_val_shd.g_old_rec.vehicle_allocation_id and
133 p_object_version_number =
134 pqp_val_shd.g_old_rec.object_version_number
135 ) Then
136 --
137 -- The g_old_rec is current therefore we must
138 -- set the returning function to true
139 --
140 l_fct_ret := true;
141 Else
142 --
143 -- Select the current row
144 --
145 Open C_Sel1;
146 Fetch C_Sel1 Into pqp_val_shd.g_old_rec;
147 If C_Sel1%notfound Then
148 Close C_Sel1;
149 --
150 -- The primary key is invalid therefore we must error
151 --
152 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
153 fnd_message.raise_error;
154 End If;
155 Close C_Sel1;
156 If (p_object_version_number
157 <> pqp_val_shd.g_old_rec.object_version_number) Then
158 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
159 fnd_message.raise_error;
160 End If;
161 l_fct_ret := true;
162 End If;
163 End If;
164 Return (l_fct_ret);
165 --
166 End api_updating;
167 --
168 -----------------------------------------------------------------------------
169 Function pqp_get_global_msg_value return varchar2 is
170 begin
171 return pqp_val_shd.g_message ;
172 end ;
173
174
175 ------------------------------------------------------------------------------
176 -- ----------------------------------------------------------------------------
177 -- |---------------------------< find_dt_upd_modes >--------------------------|
178 -- ----------------------------------------------------------------------------
179 Procedure find_dt_upd_modes
180 (p_effective_date in date
181 ,p_base_key_value in number
182 ,p_correction out nocopy boolean
183 ,p_update out nocopy boolean
184 ,p_update_override out nocopy boolean
185 ,p_update_change_insert out nocopy boolean
186 ) is
187 --
188 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
189 --
190 Begin
191 hr_utility.set_location('Entering:'||l_proc, 5);
192 --
193 -- Call the corresponding datetrack api
194 --
195 dt_api.find_dt_upd_modes
196 (p_effective_date => p_effective_date
197 ,p_base_table_name => 'pqp_vehicle_allocations_f'
198 ,p_base_key_column => 'vehicle_allocation_id'
199 ,p_base_key_value => p_base_key_value
200 ,p_correction => p_correction
201 ,p_update => p_update
202 ,p_update_override => p_update_override
203 ,p_update_change_insert => p_update_change_insert
204 );
205 --
206 hr_utility.set_location(' Leaving:'||l_proc, 10);
207 End find_dt_upd_modes;
208 --
209 -- ----------------------------------------------------------------------------
213 (p_effective_date in date
210 -- |---------------------------< find_dt_del_modes >--------------------------|
211 -- ----------------------------------------------------------------------------
212 Procedure find_dt_del_modes
214 ,p_base_key_value in number
215 ,p_zap out nocopy boolean
216 ,p_delete out nocopy boolean
217 ,p_future_change out nocopy boolean
218 ,p_delete_next_change out nocopy boolean
219 ) is
220 --
221 l_proc varchar2(72) := g_package||'find_dt_del_modes';
222 --
223 --
224 Begin
225 hr_utility.set_location('Entering:'||l_proc, 5);
226 --
227 -- Call the corresponding datetrack api
228 --
229 dt_api.find_dt_del_modes
230 (p_effective_date => p_effective_date
231 ,p_base_table_name => 'pqp_vehicle_allocations_f'
232 ,p_base_key_column => 'vehicle_allocation_id'
233 ,p_base_key_value => p_base_key_value
234 ,p_zap => p_zap
235 ,p_delete => p_delete
236 ,p_future_change => p_future_change
237 ,p_delete_next_change => p_delete_next_change
238 );
239 --
240 hr_utility.set_location(' Leaving:'||l_proc, 10);
241 End find_dt_del_modes;
242 --
243 -- ----------------------------------------------------------------------------
244 -- |-----------------------< upd_effective_end_date >-------------------------|
245 -- ----------------------------------------------------------------------------
246 Procedure upd_effective_end_date
247 (p_effective_date in date
248 ,p_base_key_value in number
249 ,p_new_effective_end_date in date
250 ,p_validation_start_date in date
251 ,p_validation_end_date in date
252 ,p_object_version_number out nocopy number
253 ) is
254 --
255 l_proc varchar2(72) := g_package||'upd_effective_end_date';
256 l_object_version_number number;
257 --
258 Begin
259 hr_utility.set_location('Entering:'||l_proc, 5);
260 --
261 -- Because we are updating a row we must get the next object
262 -- version number.
263 --
264 l_object_version_number :=
265 dt_api.get_object_version_number
266 (p_base_table_name => 'pqp_vehicle_allocations_f'
267 ,p_base_key_column => 'vehicle_allocation_id'
268 ,p_base_key_value => p_base_key_value
269 );
270 --
271 hr_utility.set_location(l_proc, 10);
272 --
273 --
274 -- Update the specified datetrack row setting the effective
275 -- end date to the specified new effective end date.
276 --
277 update pqp_vehicle_allocations_f t
278 set t.effective_end_date = p_new_effective_end_date
279 , t.object_version_number = l_object_version_number
280 where t.vehicle_allocation_id = p_base_key_value
281 and p_effective_date
282 between t.effective_start_date and t.effective_end_date;
283 --
284 --
285 p_object_version_number := l_object_version_number;
286 hr_utility.set_location(' Leaving:'||l_proc, 15);
287 --
288 End upd_effective_end_date;
289 --
290 -- ----------------------------------------------------------------------------
291 -- |---------------------------------< lck >----------------------------------|
292 -- ----------------------------------------------------------------------------
293 Procedure lck
294 (p_effective_date in date
295 ,p_datetrack_mode in varchar2
296 ,p_vehicle_allocation_id in number
297 ,p_object_version_number in number
298 ,p_validation_start_date out nocopy date
299 ,p_validation_end_date out nocopy date
300 ) is
301 --
302 l_proc varchar2(72) := g_package||'lck';
303 l_validation_start_date date;
304 l_validation_end_date date;
305 l_argument varchar2(30);
306 --
307 -- Cursor C_Sel1 selects the current locked row as of session date
308 -- ensuring that the object version numbers match.
309 --
310 Cursor C_Sel1 is
311 select
312 vehicle_allocation_id
313 ,effective_start_date
314 ,effective_end_date
315 ,assignment_id
316 ,business_group_id
317 ,across_assignments
318 ,vehicle_repository_id
319 ,usage_type
320 ,capital_contribution
321 ,private_contribution
322 ,default_vehicle
323 ,fuel_card
324 ,fuel_card_number
325 ,calculation_method
326 ,rates_table_id
327 ,element_type_id
328 ,private_use_flag
329 ,insurance_number
330 ,insurance_expiry_date
331 ,val_attribute_category
332 ,val_attribute1
333 ,val_attribute2
334 ,val_attribute3
335 ,val_attribute4
336 ,val_attribute5
337 ,val_attribute6
338 ,val_attribute7
339 ,val_attribute8
340 ,val_attribute9
341 ,val_attribute10
342 ,val_attribute11
343 ,val_attribute12
344 ,val_attribute13
345 ,val_attribute14
346 ,val_attribute15
347 ,val_attribute16
348 ,val_attribute17
349 ,val_attribute18
350 ,val_attribute19
351 ,val_attribute20
352 ,val_information_category
353 ,val_information1
354 ,val_information2
355 ,val_information3
356 ,val_information4
357 ,val_information5
358 ,val_information6
359 ,val_information7
360 ,val_information8
361 ,val_information9
362 ,val_information10
363 ,val_information11
364 ,val_information12
365 ,val_information13
366 ,val_information14
367 ,val_information15
368 ,val_information16
369 ,val_information17
370 ,val_information18
371 ,val_information19
372 ,val_information20
373 ,object_version_number
374 ,fuel_benefit
375 ,sliding_rates_info
376 from pqp_vehicle_allocations_f
377 where vehicle_allocation_id = p_vehicle_allocation_id
378 and p_effective_date
379 between effective_start_date and effective_end_date
380 for update nowait;
381 --
382 --
383 --
384 Begin
385 hr_utility.set_location('Entering:'||l_proc, 5);
386 --
387 -- Ensure that all the mandatory arguments are not null
388 --
389 hr_api.mandatory_arg_error(p_api_name => l_proc
390 ,p_argument => 'effective_date'
391 ,p_argument_value => p_effective_date
392 );
393 --
394 hr_api.mandatory_arg_error(p_api_name => l_proc
395 ,p_argument => 'datetrack_mode'
396 ,p_argument_value => p_datetrack_mode
397 );
398 --
399 hr_api.mandatory_arg_error(p_api_name => l_proc
400 ,p_argument => 'vehicle_allocation_id'
401 ,p_argument_value => p_vehicle_allocation_id
402 );
403 --
404 hr_api.mandatory_arg_error(p_api_name => l_proc
405 ,p_argument => 'object_version_number'
406 ,p_argument_value => p_object_version_number
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 pqp_val_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 <> pqp_val_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 --
433 -- Validate the datetrack mode mode getting the validation start
434 -- and end dates for the specified datetrack operation.
435 --
436 dt_api.validate_dt_mode
437 (p_effective_date => p_effective_date
438 ,p_datetrack_mode => p_datetrack_mode
439 ,p_base_table_name => 'pqp_vehicle_allocations_f'
440 ,p_base_key_column => 'vehicle_allocation_id'
441 ,p_base_key_value => p_vehicle_allocation_id
442 ,p_enforce_foreign_locking => true
443 ,p_validation_start_date => l_validation_start_date
444 ,p_validation_end_date => l_validation_end_date
445 );
446 Else
447 --
448 -- We are doing a datetrack 'INSERT' which is illegal within this
449 -- procedure therefore we must error (note: to lck on insert the
450 -- private procedure ins_lck should be called).
451 --
452 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
453 fnd_message.set_token('PROCEDURE', l_proc);
454 fnd_message.set_token('STEP','20');
455 fnd_message.raise_error;
456 End If;
457 --
458 -- Set the validation start and end date OUT arguments
459 --
460 p_validation_start_date := l_validation_start_date;
461 p_validation_end_date := l_validation_end_date;
462 --
463 hr_utility.set_location(' Leaving:'||l_proc, 30);
464 --
465 -- We need to trap the ORA LOCK exception
466 --
467 Exception
468 When HR_Api.Object_Locked then
469 --
470 -- The object is locked therefore we need to supply a meaningful
471 -- error message.
472 --
473 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
474 fnd_message.set_token('TABLE_NAME', 'pqp_vehicle_allocations_f');
475 fnd_message.raise_error;
476 End lck;
477 --
478 -- ----------------------------------------------------------------------------
479 -- |-----------------------------< convert_args >-----------------------------|
480 -- ----------------------------------------------------------------------------
481 Function convert_args
482 (p_vehicle_allocation_id in number
483 ,p_effective_start_date in date
484 ,p_effective_end_date in date
485 ,p_assignment_id in number
486 ,p_business_group_id in number
487 ,p_across_assignments in varchar2
488 ,p_vehicle_repository_id in number
489 ,p_usage_type in varchar2
490 ,p_capital_contribution in number
491 ,p_private_contribution in number
492 ,p_default_vehicle in varchar2
493 ,p_fuel_card in varchar2
494 ,p_fuel_card_number in varchar2
495 ,p_calculation_method in varchar2
496 ,p_rates_table_id in number
497 ,p_element_type_id in number
498 ,p_private_use_flag in varchar2
499 ,p_insurance_number in varchar2
500 ,p_insurance_expiry_date in date
501 ,p_val_attribute_category in varchar2
502 ,p_val_attribute1 in varchar2
503 ,p_val_attribute2 in varchar2
504 ,p_val_attribute3 in varchar2
505 ,p_val_attribute4 in varchar2
506 ,p_val_attribute5 in varchar2
507 ,p_val_attribute6 in varchar2
508 ,p_val_attribute7 in varchar2
509 ,p_val_attribute8 in varchar2
510 ,p_val_attribute9 in varchar2
511 ,p_val_attribute10 in varchar2
512 ,p_val_attribute11 in varchar2
513 ,p_val_attribute12 in varchar2
514 ,p_val_attribute13 in varchar2
515 ,p_val_attribute14 in varchar2
516 ,p_val_attribute15 in varchar2
517 ,p_val_attribute16 in varchar2
518 ,p_val_attribute17 in varchar2
519 ,p_val_attribute18 in varchar2
520 ,p_val_attribute19 in varchar2
521 ,p_val_attribute20 in varchar2
522 ,p_val_information_category in varchar2
523 ,p_val_information1 in varchar2
524 ,p_val_information2 in varchar2
525 ,p_val_information3 in varchar2
526 ,p_val_information4 in varchar2
527 ,p_val_information5 in varchar2
528 ,p_val_information6 in varchar2
529 ,p_val_information7 in varchar2
530 ,p_val_information8 in varchar2
531 ,p_val_information9 in varchar2
532 ,p_val_information10 in varchar2
533 ,p_val_information11 in varchar2
534 ,p_val_information12 in varchar2
535 ,p_val_information13 in varchar2
536 ,p_val_information14 in varchar2
537 ,p_val_information15 in varchar2
538 ,p_val_information16 in varchar2
539 ,p_val_information17 in varchar2
540 ,p_val_information18 in varchar2
541 ,p_val_information19 in varchar2
542 ,p_val_information20 in varchar2
543 ,p_object_version_number in number
544 ,p_fuel_benefit in varchar2
545 ,p_sliding_rates_info in varchar2
546
547 )
548 Return g_rec_type is
549 --
550 l_rec g_rec_type;
551 --
552 Begin
553 --
554 -- Convert arguments into local l_rec structure.
555 --
556 l_rec.vehicle_allocation_id := p_vehicle_allocation_id;
557 l_rec.effective_start_date := p_effective_start_date;
558 l_rec.effective_end_date := p_effective_end_date;
559 l_rec.assignment_id := p_assignment_id;
560 l_rec.business_group_id := p_business_group_id;
561 l_rec.across_assignments := p_across_assignments;
562 l_rec.vehicle_repository_id := p_vehicle_repository_id;
563 l_rec.usage_type := p_usage_type;
564 l_rec.capital_contribution := p_capital_contribution;
565 l_rec.private_contribution := p_private_contribution;
566 l_rec.default_vehicle := p_default_vehicle;
567 l_rec.fuel_card := p_fuel_card;
568 l_rec.fuel_card_number := p_fuel_card_number;
569 l_rec.calculation_method := p_calculation_method;
570 l_rec.rates_table_id := p_rates_table_id;
571 l_rec.element_type_id := p_element_type_id;
572 l_rec.private_use_flag := p_private_use_flag;
573 l_rec.insurance_number := p_insurance_number;
574 l_rec.insurance_expiry_date := p_insurance_expiry_date;
575 l_rec.val_attribute_category := p_val_attribute_category;
576 l_rec.val_attribute1 := p_val_attribute1;
577 l_rec.val_attribute2 := p_val_attribute2;
578 l_rec.val_attribute3 := p_val_attribute3;
579 l_rec.val_attribute4 := p_val_attribute4;
580 l_rec.val_attribute5 := p_val_attribute5;
581 l_rec.val_attribute6 := p_val_attribute6;
582 l_rec.val_attribute7 := p_val_attribute7;
583 l_rec.val_attribute8 := p_val_attribute8;
584 l_rec.val_attribute9 := p_val_attribute9;
585 l_rec.val_attribute10 := p_val_attribute10;
586 l_rec.val_attribute11 := p_val_attribute11;
587 l_rec.val_attribute12 := p_val_attribute12;
588 l_rec.val_attribute13 := p_val_attribute13;
589 l_rec.val_attribute14 := p_val_attribute14;
590 l_rec.val_attribute15 := p_val_attribute15;
591 l_rec.val_attribute16 := p_val_attribute16;
592 l_rec.val_attribute17 := p_val_attribute17;
593 l_rec.val_attribute18 := p_val_attribute18;
594 l_rec.val_attribute19 := p_val_attribute19;
595 l_rec.val_attribute20 := p_val_attribute20;
596 l_rec.val_information_category := p_val_information_category;
597 l_rec.val_information1 := p_val_information1;
598 l_rec.val_information2 := p_val_information2;
599 l_rec.val_information3 := p_val_information3;
600 l_rec.val_information4 := p_val_information4;
601 l_rec.val_information5 := p_val_information5;
602 l_rec.val_information6 := p_val_information6;
603 l_rec.val_information7 := p_val_information7;
604 l_rec.val_information8 := p_val_information8;
605 l_rec.val_information9 := p_val_information9;
606 l_rec.val_information10 := p_val_information10;
607 l_rec.val_information11 := p_val_information11;
608 l_rec.val_information12 := p_val_information12;
609 l_rec.val_information13 := p_val_information13;
610 l_rec.val_information14 := p_val_information14;
611 l_rec.val_information15 := p_val_information15;
612 l_rec.val_information16 := p_val_information16;
613 l_rec.val_information17 := p_val_information17;
614 l_rec.val_information18 := p_val_information18;
615 l_rec.val_information19 := p_val_information19;
616 l_rec.val_information20 := p_val_information20;
617 l_rec.object_version_number := p_object_version_number;
618 l_rec.fuel_benefit := p_fuel_benefit;
619 l_rec.sliding_rates_info := p_sliding_rates_info;
620
621 --
622 -- Return the plsql record structure.
623 --
624 Return(l_rec);
625 --
626 End convert_args;
627 --
628 end pqp_val_shd;