1 Package Body pqp_val_bus as
2 /* $Header: pqvalrhi.pkb 120.0.12010000.3 2008/08/08 07:22:41 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqp_val_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_vehicle_allocation_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_vehicle_allocation_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id,
29 pbg.legislation_code
30 from per_business_groups_perf pbg
31 , pqp_vehicle_allocations_f val
32 where val.vehicle_allocation_id = p_vehicle_allocation_id
33 and pbg.business_group_id = val.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 l_legislation_code varchar2(150);
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'vehicle_allocation_id'
50 ,p_argument_value => p_vehicle_allocation_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id
55 , l_legislation_code;
56 --
57 if csr_sec_grp%notfound then
58 --
59 close csr_sec_grp;
60 --
61 -- The primary key is invalid therefore we must error
62 --
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 hr_multi_message.add
65 (p_associated_column1
66 => nvl(p_associated_column1,'VEHICLE_ALLOCATION_ID')
67 );
68 --
69 else
70 close csr_sec_grp;
71 --
72 -- Set the security_group_id in CLIENT_INFO
73 --
74 hr_api.set_security_group_id
75 (p_security_group_id => l_security_group_id
76 );
77 --
78 -- Set the sessions legislation context in HR_SESSION_DATA
79 --
80 hr_api.set_legislation_context(l_legislation_code);
81 end if;
82 --
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_vehicle_allocation_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 cursor csr_leg_code is
99 select pbg.legislation_code
100 from per_business_groups_perf pbg
101 , pqp_vehicle_allocations_f val
102 where val.vehicle_allocation_id = p_vehicle_allocation_id
103 and pbg.business_group_id = val.business_group_id;
104 --
105 -- Declare local variables
106 --
107 l_legislation_code varchar2(150);
108 l_proc varchar2(72) := g_package||'return_legislation_code';
109 --
110 Begin
111 --
112 hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Ensure that all the mandatory parameter are not null
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'vehicle_allocation_id'
119 ,p_argument_value => p_vehicle_allocation_id
120 );
121 --
122 if ( nvl(pqp_val_bus.g_vehicle_allocation_id, hr_api.g_number)
123 = p_vehicle_allocation_id) then
124 --
125 -- The legislation code has already been found with a previous
126 -- call to this function. Just return the value in the global
127 -- variable.
128 --
129 l_legislation_code := pqp_val_bus.g_legislation_code;
130 hr_utility.set_location(l_proc, 20);
131 else
132 --
133 -- The ID is different to the last call to this function
134 -- or this is the first call to this function.
135 --
136 open csr_leg_code;
137 fetch csr_leg_code into l_legislation_code;
138 --
139 if csr_leg_code%notfound then
140 --
141 -- The primary key is invalid therefore we must error
142 --
143 close csr_leg_code;
144 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
145 fnd_message.raise_error;
146 end if;
147 hr_utility.set_location(l_proc,30);
148 --
149 -- Set the global variables so the values are
150 -- available for the next call to this function.
151 --
152 close csr_leg_code;
153 pqp_val_bus.g_vehicle_allocation_id := p_vehicle_allocation_id;
154 pqp_val_bus.g_legislation_code := l_legislation_code;
155 end if;
156 hr_utility.set_location(' Leaving:'|| l_proc, 40);
157 return l_legislation_code;
158 end return_legislation_code;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |-----------------------------< chk_ddf >----------------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 -- Description:
165 -- Validates all the Developer Descriptive Flexfield values.
166 --
167 -- Prerequisites:
168 -- All other columns have been validated. Must be called as the
169 -- last step from insert_validate and update_validate.
170 --
171 -- In Arguments:
172 -- p_rec
173 --
174 -- Post Success:
175 -- If the Developer Descriptive Flexfield structure column and data values
176 -- are all valid this procedure will end normally and processing will
177 -- continue.
178 --
179 -- Post Failure:
180 -- If the Developer Descriptive Flexfield structure column value or any of
181 -- the data values are invalid then an application error is raised as
182 -- a PL/SQL exception.
183 --
184 -- Access Status:
185 -- Internal Row Handler Use Only.
186 --
187 -- ----------------------------------------------------------------------------
188 procedure chk_ddf
189 (p_rec in pqp_val_shd.g_rec_type
190 ) is
191 --
192 l_proc varchar2(72) := g_package || 'chk_ddf';
193 --
194 begin
195 hr_utility.set_location('Entering:'||l_proc,10);
196 --
197 if ((p_rec.vehicle_allocation_id is not null) and (
198 nvl(pqp_val_shd.g_old_rec.val_information_category, hr_api.g_varchar2) <>
199 nvl(p_rec.val_information_category, hr_api.g_varchar2) or
200 nvl(pqp_val_shd.g_old_rec.val_information1, hr_api.g_varchar2) <>
201 nvl(p_rec.val_information1, hr_api.g_varchar2) or
202 nvl(pqp_val_shd.g_old_rec.val_information2, hr_api.g_varchar2) <>
203 nvl(p_rec.val_information2, hr_api.g_varchar2) or
204 nvl(pqp_val_shd.g_old_rec.val_information3, hr_api.g_varchar2) <>
205 nvl(p_rec.val_information3, hr_api.g_varchar2) or
206 nvl(pqp_val_shd.g_old_rec.val_information4, hr_api.g_varchar2) <>
207 nvl(p_rec.val_information4, hr_api.g_varchar2) or
208 nvl(pqp_val_shd.g_old_rec.val_information5, hr_api.g_varchar2) <>
209 nvl(p_rec.val_information5, hr_api.g_varchar2) or
210 nvl(pqp_val_shd.g_old_rec.val_information6, hr_api.g_varchar2) <>
211 nvl(p_rec.val_information6, hr_api.g_varchar2) or
212 nvl(pqp_val_shd.g_old_rec.val_information7, hr_api.g_varchar2) <>
213 nvl(p_rec.val_information7, hr_api.g_varchar2) or
214 nvl(pqp_val_shd.g_old_rec.val_information8, hr_api.g_varchar2) <>
215 nvl(p_rec.val_information8, hr_api.g_varchar2) or
216 nvl(pqp_val_shd.g_old_rec.val_information9, hr_api.g_varchar2) <>
217 nvl(p_rec.val_information9, hr_api.g_varchar2) or
218 nvl(pqp_val_shd.g_old_rec.val_information10, hr_api.g_varchar2) <>
219 nvl(p_rec.val_information10, hr_api.g_varchar2) or
220 nvl(pqp_val_shd.g_old_rec.val_information11, hr_api.g_varchar2) <>
221 nvl(p_rec.val_information11, hr_api.g_varchar2) or
222 nvl(pqp_val_shd.g_old_rec.val_information12, hr_api.g_varchar2) <>
223 nvl(p_rec.val_information12, hr_api.g_varchar2) or
224 nvl(pqp_val_shd.g_old_rec.val_information13, hr_api.g_varchar2) <>
225 nvl(p_rec.val_information13, hr_api.g_varchar2) or
226 nvl(pqp_val_shd.g_old_rec.val_information14, hr_api.g_varchar2) <>
227 nvl(p_rec.val_information14, hr_api.g_varchar2) or
228 nvl(pqp_val_shd.g_old_rec.val_information15, hr_api.g_varchar2) <>
229 nvl(p_rec.val_information15, hr_api.g_varchar2) or
230 nvl(pqp_val_shd.g_old_rec.val_information16, hr_api.g_varchar2) <>
231 nvl(p_rec.val_information16, hr_api.g_varchar2) or
232 nvl(pqp_val_shd.g_old_rec.val_information17, hr_api.g_varchar2) <>
233 nvl(p_rec.val_information17, hr_api.g_varchar2) or
234 nvl(pqp_val_shd.g_old_rec.val_information18, hr_api.g_varchar2) <>
235 nvl(p_rec.val_information18, hr_api.g_varchar2) or
236 nvl(pqp_val_shd.g_old_rec.val_information19, hr_api.g_varchar2) <>
237 nvl(p_rec.val_information19, hr_api.g_varchar2) or
238 nvl(pqp_val_shd.g_old_rec.val_information20, hr_api.g_varchar2) <>
239 nvl(p_rec.val_information20, hr_api.g_varchar2) ))
240 or (p_rec.vehicle_allocation_id is null) then
241 --
242 -- Only execute the validation if absolutely necessary:
243 -- a) During update, the structure column value or any
244 -- of the attribute values have actually changed.
245 -- b) During insert.
246 --
247 hr_dflex_utility.ins_or_upd_descflex_attribs
248 (p_appl_short_name => 'PQP'
249 ,p_descflex_name => 'Vehicle Allocation Info DDF'
250 ,p_attribute_category => p_rec.val_information_category
251 ,p_attribute1_name => 'VAL_INFORMATION1'
252 ,p_attribute1_value => p_rec.val_information1
253 ,p_attribute2_name => 'VAL_INFORMATION2'
254 ,p_attribute2_value => p_rec.val_information2
255 ,p_attribute3_name => 'VAL_INFORMATION3'
256 ,p_attribute3_value => p_rec.val_information3
257 ,p_attribute4_name => 'VAL_INFORMATION4'
258 ,p_attribute4_value => p_rec.val_information4
259 ,p_attribute5_name => 'VAL_INFORMATION5'
260 ,p_attribute5_value => p_rec.val_information5
261 ,p_attribute6_name => 'VAL_INFORMATION6'
262 ,p_attribute6_value => p_rec.val_information6
263 ,p_attribute7_name => 'VAL_INFORMATION7'
264 ,p_attribute7_value => p_rec.val_information7
265 ,p_attribute8_name => 'VAL_INFORMATION8'
266 ,p_attribute8_value => p_rec.val_information8
267 ,p_attribute9_name => 'VAL_INFORMATION9'
268 ,p_attribute9_value => p_rec.val_information9
269 ,p_attribute10_name => 'VAL_INFORMATION10'
270 ,p_attribute10_value => p_rec.val_information10
271 ,p_attribute11_name => 'VAL_INFORMATION11'
272 ,p_attribute11_value => p_rec.val_information11
273 ,p_attribute12_name => 'VAL_INFORMATION12'
274 ,p_attribute12_value => p_rec.val_information12
275 ,p_attribute13_name => 'VAL_INFORMATION13'
276 ,p_attribute13_value => p_rec.val_information13
277 ,p_attribute14_name => 'VAL_INFORMATION14'
278 ,p_attribute14_value => p_rec.val_information14
279 ,p_attribute15_name => 'VAL_INFORMATION15'
280 ,p_attribute15_value => p_rec.val_information15
281 ,p_attribute16_name => 'VAL_INFORMATION16'
282 ,p_attribute16_value => p_rec.val_information16
283 ,p_attribute17_name => 'VAL_INFORMATION17'
284 ,p_attribute17_value => p_rec.val_information17
285 ,p_attribute18_name => 'VAL_INFORMATION18'
286 ,p_attribute18_value => p_rec.val_information18
287 ,p_attribute19_name => 'VAL_INFORMATION19'
288 ,p_attribute19_value => p_rec.val_information19
289 ,p_attribute20_name => 'VAL_INFORMATION20'
290 ,p_attribute20_value => p_rec.val_information20
291 );
292 end if;
293 --
294 hr_utility.set_location(' Leaving:'||l_proc,20);
295 end chk_ddf;
296 --
297 -- ----------------------------------------------------------------------------
298 -- |------------------------------< chk_df >----------------------------------|
299 -- ----------------------------------------------------------------------------
300 --
301 -- Description:
302 -- Validates all the Descriptive Flexfield values.
303 --
304 -- Prerequisites:
305 -- All other columns have been validated. Must be called as the
306 -- last step from insert_validate and update_validate.
307 --
308 -- In Arguments:
309 -- p_rec
310 --
311 -- Post Success:
312 -- If the Descriptive Flexfield structure column and data values are
313 -- all valid this procedure will end normally and processing will
314 -- continue.
315 --
316 -- Post Failure:
317 -- If the Descriptive Flexfield structure column value or any of
318 -- the data values are invalid then an application error is raised as
319 -- a PL/SQL exception.
320 --
321 -- Access Status:
322 -- Internal Row Handler Use Only.
323 --
324 -- ----------------------------------------------------------------------------
325 procedure chk_df
326 (p_rec in pqp_val_shd.g_rec_type
327 ) is
328 --
329 l_proc varchar2(72) := g_package || 'chk_df';
330 --
331 begin
332 hr_utility.set_location('Entering:'||l_proc,10);
333 --
334 if ((p_rec.vehicle_allocation_id is not null) and (
335 nvl(pqp_val_shd.g_old_rec.val_attribute_category, hr_api.g_varchar2) <>
336 nvl(p_rec.val_attribute_category, hr_api.g_varchar2) or
337 nvl(pqp_val_shd.g_old_rec.val_attribute1, hr_api.g_varchar2) <>
338 nvl(p_rec.val_attribute1, hr_api.g_varchar2) or
339 nvl(pqp_val_shd.g_old_rec.val_attribute2, hr_api.g_varchar2) <>
340 nvl(p_rec.val_attribute2, hr_api.g_varchar2) or
341 nvl(pqp_val_shd.g_old_rec.val_attribute3, hr_api.g_varchar2) <>
342 nvl(p_rec.val_attribute3, hr_api.g_varchar2) or
343 nvl(pqp_val_shd.g_old_rec.val_attribute4, hr_api.g_varchar2) <>
344 nvl(p_rec.val_attribute4, hr_api.g_varchar2) or
345 nvl(pqp_val_shd.g_old_rec.val_attribute5, hr_api.g_varchar2) <>
346 nvl(p_rec.val_attribute5, hr_api.g_varchar2) or
347 nvl(pqp_val_shd.g_old_rec.val_attribute6, hr_api.g_varchar2) <>
348 nvl(p_rec.val_attribute6, hr_api.g_varchar2) or
349 nvl(pqp_val_shd.g_old_rec.val_attribute7, hr_api.g_varchar2) <>
350 nvl(p_rec.val_attribute7, hr_api.g_varchar2) or
351 nvl(pqp_val_shd.g_old_rec.val_attribute8, hr_api.g_varchar2) <>
352 nvl(p_rec.val_attribute8, hr_api.g_varchar2) or
353 nvl(pqp_val_shd.g_old_rec.val_attribute9, hr_api.g_varchar2) <>
354 nvl(p_rec.val_attribute9, hr_api.g_varchar2) or
355 nvl(pqp_val_shd.g_old_rec.val_attribute10, hr_api.g_varchar2) <>
356 nvl(p_rec.val_attribute10, hr_api.g_varchar2) or
357 nvl(pqp_val_shd.g_old_rec.val_attribute11, hr_api.g_varchar2) <>
358 nvl(p_rec.val_attribute11, hr_api.g_varchar2) or
359 nvl(pqp_val_shd.g_old_rec.val_attribute12, hr_api.g_varchar2) <>
360 nvl(p_rec.val_attribute12, hr_api.g_varchar2) or
361 nvl(pqp_val_shd.g_old_rec.val_attribute13, hr_api.g_varchar2) <>
362 nvl(p_rec.val_attribute13, hr_api.g_varchar2) or
363 nvl(pqp_val_shd.g_old_rec.val_attribute14, hr_api.g_varchar2) <>
364 nvl(p_rec.val_attribute14, hr_api.g_varchar2) or
365 nvl(pqp_val_shd.g_old_rec.val_attribute15, hr_api.g_varchar2) <>
366 nvl(p_rec.val_attribute15, hr_api.g_varchar2) or
367 nvl(pqp_val_shd.g_old_rec.val_attribute16, hr_api.g_varchar2) <>
368 nvl(p_rec.val_attribute16, hr_api.g_varchar2) or
369 nvl(pqp_val_shd.g_old_rec.val_attribute17, hr_api.g_varchar2) <>
370 nvl(p_rec.val_attribute17, hr_api.g_varchar2) or
371 nvl(pqp_val_shd.g_old_rec.val_attribute18, hr_api.g_varchar2) <>
372 nvl(p_rec.val_attribute18, hr_api.g_varchar2) or
373 nvl(pqp_val_shd.g_old_rec.val_attribute19, hr_api.g_varchar2) <>
374 nvl(p_rec.val_attribute19, hr_api.g_varchar2) or
375 nvl(pqp_val_shd.g_old_rec.val_attribute20, hr_api.g_varchar2) <>
376 nvl(p_rec.val_attribute20, hr_api.g_varchar2) ))
377 or (p_rec.vehicle_allocation_id is null) then
378 --
379 -- Only execute the validation if absolutely necessary:
380 -- a) During update, the structure column value or any
381 -- of the attribute values have actually changed.
382 -- b) During insert.
383 --
384 hr_dflex_utility.ins_or_upd_descflex_attribs
385 (p_appl_short_name => 'PQP'
386 ,p_descflex_name => 'Vehicle Allocation Info DF'
387 ,p_attribute_category => p_rec.val_attribute_category
388 ,p_attribute1_name => 'VAL_ATTRIBUTE1'
389 ,p_attribute1_value => p_rec.val_attribute1
390 ,p_attribute2_name => 'VAL_ATTRIBUTE2'
391 ,p_attribute2_value => p_rec.val_attribute2
392 ,p_attribute3_name => 'VAL_ATTRIBUTE3'
393 ,p_attribute3_value => p_rec.val_attribute3
394 ,p_attribute4_name => 'VAL_ATTRIBUTE4'
395 ,p_attribute4_value => p_rec.val_attribute4
396 ,p_attribute5_name => 'VAL_ATTRIBUTE5'
397 ,p_attribute5_value => p_rec.val_attribute5
398 ,p_attribute6_name => 'VAL_ATTRIBUTE6'
399 ,p_attribute6_value => p_rec.val_attribute6
400 ,p_attribute7_name => 'VAL_ATTRIBUTE7'
401 ,p_attribute7_value => p_rec.val_attribute7
402 ,p_attribute8_name => 'VAL_ATTRIBUTE8'
403 ,p_attribute8_value => p_rec.val_attribute8
404 ,p_attribute9_name => 'VAL_ATTRIBUTE9'
405 ,p_attribute9_value => p_rec.val_attribute9
406 ,p_attribute10_name => 'VAL_ATTRIBUTE10'
407 ,p_attribute10_value => p_rec.val_attribute10
408 ,p_attribute11_name => 'VAL_ATTRIBUTE11'
409 ,p_attribute11_value => p_rec.val_attribute11
410 ,p_attribute12_name => 'VAL_ATTRIBUTE12'
411 ,p_attribute12_value => p_rec.val_attribute12
412 ,p_attribute13_name => 'VAL_ATTRIBUTE13'
413 ,p_attribute13_value => p_rec.val_attribute13
414 ,p_attribute14_name => 'VAL_ATTRIBUTE14'
415 ,p_attribute14_value => p_rec.val_attribute14
416 ,p_attribute15_name => 'VAL_ATTRIBUTE15'
417 ,p_attribute15_value => p_rec.val_attribute15
418 ,p_attribute16_name => 'VAL_ATTRIBUTE16'
419 ,p_attribute16_value => p_rec.val_attribute16
420 ,p_attribute17_name => 'VAL_ATTRIBUTE17'
421 ,p_attribute17_value => p_rec.val_attribute17
422 ,p_attribute18_name => 'VAL_ATTRIBUTE18'
423 ,p_attribute18_value => p_rec.val_attribute18
424 ,p_attribute19_name => 'VAL_ATTRIBUTE19'
425 ,p_attribute19_value => p_rec.val_attribute19
426 ,p_attribute20_name => 'VAL_ATTRIBUTE20'
427 ,p_attribute20_value => p_rec.val_attribute20
428 );
429 end if;
430 --
431 hr_utility.set_location(' Leaving:'||l_proc,20);
432 end chk_df;
433 --
434 -- ----------------------------------------------------------------------------
435 -- |-----------------------< chk_non_updateable_args >------------------------|
436 -- ----------------------------------------------------------------------------
437 -- {Start Of Comments}
438 --
439 -- Description:
440 -- This procedure is used to ensure that non updateable attributes have
441 -- not been updated. If an attribute has been updated an error is generated.
442 --
443 -- Pre Conditions:
444 -- g_old_rec has been populated with details of the values currently in
445 -- the database.
446 --
447 -- In Arguments:
448 -- p_rec has been populated with the updated values the user would like the
449 -- record set to.
450 --
451 -- Post Success:
452 -- Processing continues if all the non updateable attributes have not
453 -- changed.
454 --
455 -- Post Failure:
456 -- An application error is raised if any of the non updatable attributes
457 -- have been altered.
458 --
459 -- {End Of Comments}
460 -- ----------------------------------------------------------------------------
461 Procedure chk_non_updateable_args
462 (p_effective_date in date
463 ,p_rec in pqp_val_shd.g_rec_type
464 ) IS
465 --
466 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
467 --
468 Begin
469 --
470 -- Only proceed with the validation if a row exists for the current
471 -- record in the HR Schema.
472 --
473 IF NOT pqp_val_shd.api_updating
474 (p_vehicle_allocation_id => p_rec.vehicle_allocation_id
475 ,p_effective_date => p_effective_date
476 ,p_object_version_number => p_rec.object_version_number
477 ) THEN
478 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
479 fnd_message.set_token('PROCEDURE ', l_proc);
480 fnd_message.set_token('STEP ', '5');
481 fnd_message.raise_error;
482 END IF;
483 --
484 -- EDIT_HERE: Add checks to ensure non-updateable args have
485 -- not been updated.
486 --
487 End chk_non_updateable_args;
488 --
489 -- ----------------------------------------------------------------------------
490 -- |--------------------------< dt_update_validate >--------------------------|
491 -- ----------------------------------------------------------------------------
492 -- {Start Of Comments}
493 --
494 -- Description:
495 -- This procedure is used for referential integrity of datetracked
496 -- parent entities when a datetrack update operation is taking place
497 -- and where there is no cascading of update defined for this entity.
498 --
499 -- Prerequisites:
500 -- This procedure is called from the update_validate.
501 --
502 -- In Parameters:
503 --
504 -- Post Success:
505 -- Processing continues.
506 --
507 -- Post Failure:
508 --
509 -- Developer Implementation Notes:
510 -- This procedure should not need maintenance unless the HR Schema model
511 -- changes.
512 --
513 -- Access Status:
514 -- Internal Row Handler Use Only.
515 --
516 -- {End Of Comments}
517 -- ----------------------------------------------------------------------------
518 Procedure dt_update_validate
519 (p_datetrack_mode in varchar2
520 ,p_validation_start_date in date
521 ,p_validation_end_date in date
522 ) Is
523 --
524 l_proc varchar2(72) := g_package||'dt_update_validate';
525 --
526 Begin
527 --
528 -- Ensure that the p_datetrack_mode argument is not null
529 --
530 hr_api.mandatory_arg_error
531 (p_api_name => l_proc
532 ,p_argument => 'datetrack_mode'
533 ,p_argument_value => p_datetrack_mode
534 );
535 --
536 -- Mode will be valid, as this is checked at the start of the upd.
537 --
538 -- Ensure the arguments are not null
539 --
540 hr_api.mandatory_arg_error
541 (p_api_name => l_proc
542 ,p_argument => 'validation_start_date'
543 ,p_argument_value => p_validation_start_date
544 );
545 --
546 hr_api.mandatory_arg_error
547 (p_api_name => l_proc
548 ,p_argument => 'validation_end_date'
549 ,p_argument_value => p_validation_end_date
550 );
551 --
552 --
553 --
554 Exception
555 When Others Then
556 --
557 -- An unhandled or unexpected error has occurred which
558 -- we must report
559 --
560 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
561 fnd_message.set_token('PROCEDURE', l_proc);
562 fnd_message.set_token('STEP','15');
563 fnd_message.raise_error;
564 End dt_update_validate;
565 --
566 -- ----------------------------------------------------------------------------
567 -- |--------------------------< dt_delete_validate >--------------------------|
568 -- ----------------------------------------------------------------------------
569 -- {Start Of Comments}
570 --
571 -- Description:
572 -- This procedure is used for referential integrity of datetracked
573 -- child entities when either a datetrack DELETE or ZAP is in operation
574 -- and where there is no cascading of delete defined for this entity.
575 -- For the datetrack mode of DELETE or ZAP we must ensure that no
576 -- datetracked child rows exist between the validation start and end
577 -- dates.
578 --
579 -- Prerequisites:
580 -- This procedure is called from the delete_validate.
581 --
582 -- In Parameters:
583 --
584 -- Post Success:
585 -- Processing continues.
586 --
587 -- Post Failure:
588 -- If a row exists by determining the returning Boolean value from the
589 -- generic dt_api.rows_exist function then we must supply an error via
590 -- the use of the local exception handler l_rows_exist.
591 --
592 -- Developer Implementation Notes:
593 -- This procedure should not need maintenance unless the HR Schema model
594 -- changes.
595 --
596 -- Access Status:
597 -- Internal Row Handler Use Only.
598 --
599 -- {End Of Comments}
600 -- ----------------------------------------------------------------------------
601 Procedure dt_delete_validate
602 (p_vehicle_allocation_id in number
603 ,p_datetrack_mode in varchar2
604 ,p_validation_start_date in date
605 ,p_validation_end_date in date
606 ) Is
607 --
608 l_proc varchar2(72) := g_package||'dt_delete_validate';
609 --
610 Begin
611 --
612 -- Ensure that the p_datetrack_mode argument is not null
613 --
614 hr_api.mandatory_arg_error
615 (p_api_name => l_proc
616 ,p_argument => 'datetrack_mode'
617 ,p_argument_value => p_datetrack_mode
618 );
619 --
620 -- Only perform the validation if the datetrack mode is either
621 -- DELETE or ZAP
622 --
623 If (p_datetrack_mode = hr_api.g_delete or
624 p_datetrack_mode = hr_api.g_zap) then
625 --
626 --
627 -- Ensure the arguments are not null
628 --
629 hr_api.mandatory_arg_error
630 (p_api_name => l_proc
631 ,p_argument => 'validation_start_date'
632 ,p_argument_value => p_validation_start_date
633 );
634 --
635 hr_api.mandatory_arg_error
636 (p_api_name => l_proc
637 ,p_argument => 'validation_end_date'
638 ,p_argument_value => p_validation_end_date
639 );
640 --
641 hr_api.mandatory_arg_error
642 (p_api_name => l_proc
643 ,p_argument => 'vehicle_allocation_id'
644 ,p_argument_value => p_vehicle_allocation_id
645 );
646 --
647 --
648 --
649 End If;
650 --
651 Exception
652 When Others Then
653 --
654 -- An unhandled or unexpected error has occurred which
655 -- we must report
656 --
657 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
658 fnd_message.set_token('PROCEDURE', l_proc);
659 fnd_message.set_token('STEP','15');
660 fnd_message.raise_error;
661 --
662 End dt_delete_validate;
663 --
664 -----------------------------------------------------------------------------
665 ----------------------Fuel card/Fuel Card Number /Fuel Benifit Check--------
666 -----------------------------------------------------------------------------
667 --Fuel Card:This is a check box and this is available only for company vehicle
668 --and will be validated in the APIs for private vehicle for that legislation.
669 --
670 --Fuel Card Number: Non-validated and optional field must be entered only
671 --when fuel card is checked and must not error when fuel card is checked
672 --and fuel card number is not entered.
673 --
674 --Fuel Benefit:Check box available for only company vehicles and
675 --need to be validated in API.
676
677 FUNCTION pqp_check_cmyveh_fuel_card
678 (p_rec IN pqp_val_shd.g_rec_type,
679 p_vehicle_ownership IN VARCHAR2,
680 p_effective_date IN DATE ,
681 p_message OUT NOCOPY VARCHAR2
682 ) RETURN VARCHAR2 IS
683 BEGIN
684
685 --if vehicle ownership is company then fuelcard value should be
686 -- "Y/N"and fuelCard number ,fuel benifit is optional
687 --If vehicle ownership is private then fuelcard value should be
688 -- "NULL" and fuelCard number,fuel benifit should be null
689 IF p_vehicle_ownership = 'C' THEN
690 IF p_rec.fuel_card IS NULL THEN
691 --fuel card should be selected
692 p_message := 'Fuel card should be Y/N for company vehicle';
693 RETURN -1;
694 END IF;
695 END IF;
696 RETURN 0;
697 END pqp_check_cmyveh_fuel_card;
698 --End of pqp_check_cmyveh_fuel_card
699 -----------------------------------------------------------------------------
700 ----------------------- Get maximum company/Private allowed vehicle ---------
701 ------------------------------------------------------------------------------
702 --
703 --Maximum Company Vehicles Allowed: The limitations
704 --on number of company vehicles
705 --that can be associated to an assignment can be set up here.
706 --The default is null which means that there is no limit for
707 --the number of company cars that can be assigned to an assignment.
708 -- or
709 --Maximum Private Vehicles Allowed: The limitations
710 --on number of private vehicles
711 --that can be associated with an assignment.
712 --If the number is reached then a new vehicle can be associated only after
713 --one of the existing allocated vehicles is removed.
714 --The default value is null (no limit).
715 FUNCTION pqp_get_max_allowed_veh
716 ( p_rec IN pqp_val_shd.g_rec_type
717 ,p_vehicle_ownership IN VARCHAR2
718 ,p_effective_date IN DATE
719 ) RETURN NUMBER IS
720
721 --used to get the allocation count fr future and current date track
722 CURSOR c_alloc_count_cursor IS
723 SELECT COUNT(pva.vehicle_allocation_id)
724 FROM pqp_vehicle_repository_f pvr
725 ,pqp_vehicle_allocations_f pva
726 WHERE pvr.vehicle_repository_id = pva.vehicle_repository_id
727 AND pvr.business_group_id = pva.business_group_id
728 AND pvr.vehicle_ownership = p_vehicle_ownership
729 AND pva.assignment_id = p_rec.assignment_id
730 AND pva.business_group_id = p_rec.business_group_id
731 AND (p_effective_date BETWEEN
732 pva.effective_start_date AND pva.effective_end_date
733 OR p_effective_date <= pva.effective_start_date)
734 AND p_effective_date between
735 pvr.effective_start_date and pvr.effective_end_date;
736
737 l_rowcount NUMBER;
738 BEGIN
739 hr_utility.set_location('Entering pqp_get_max_allowed_veh', 21);
740 OPEN c_alloc_count_cursor;
741 FETCH c_alloc_count_cursor INTO l_rowcount;
742 CLOSE c_alloc_count_cursor;
743 hr_utility.set_location('maximum vehicles till now:'||l_rowcount, 25);
744 RETURN l_rowcount ;
745 END pqp_get_max_allowed_veh;
746 -- end function
747
748 ------------------------------------------------------------------------------
749 ---------------------------------------------------------------------------
750 ------------------------<Check Mandatory Fields>-------------------------
751 ---------------------------------------------------------------------------
752 FUNCTION chk_mandatory
753 (p_argument IN VARCHAR2,
754 p_argument_value IN VARCHAR2,
755 p_message OUT NOCOPY VARCHAR2
756 ) RETURN NUMBER IS
757 BEGIN
758
759 IF p_argument_value IS NULL THEN
760 p_message := p_argument || 'Value should be Mandatory';
761 RETURN -1;
762 END IF;
763 RETURN 0;
764 END chk_mandatory;
765 -- ---------------------------------------------------------------------------
766 -- |------------------------< Used to check the lookup codes >----------------
767 -- ---------------------------------------------------------------------------
768 --Used to check the passed lookup code is correct or not
769 FUNCTION chk_lookup
770 (p_vehicle_allocation_id IN NUMBER
771 ,p_lookup_type IN VARCHAR2
772 ,p_lookup_code IN VARCHAR2
773 ,p_effective_date IN DATE
774 ,p_validation_start_date IN DATE
775 ,p_validation_end_date IN DATE
776 ) RETURN NUMBER IS
777
778 --Local variables declaration
779 l_old_argument_value hr_lookups.lookup_code%TYPE;
780
781 BEGIN
782 --
783 -- If argument value is not null then
784 -- Check if the argument value exists in hr_lookups
785 -- where the lookup_type is passed lookuptype
786 --
787 IF p_lookup_code IS NOT NULL then
788 IF hr_api.not_exists_in_dt_hrstanlookups
789 (p_effective_date => p_effective_date
790 ,p_validation_start_date => p_validation_start_date
791 ,p_validation_end_date => p_validation_end_date
792 ,p_lookup_type => p_lookup_type
793 ,p_lookup_code => p_lookup_code
794 ) THEN
795 RETURN -1;
796 END IF;
797 END IF;
798 RETURN 0;
799 END;
800
801 ------------------------------------------------------------------------------
802 ---------------------------Validating the Vehicle Status-----------------------
803 -------------------------------------------------------------------------------
804 --
805 --The selected vehicle must be validated for the eligibility for that employee
806 --and also the vehicle must be checked if the vehicle can be shared or status
807 -- is inactive.
808 --
809 FUNCTION pqp_veh_eligibility_check
810 ( p_rec IN pqp_val_shd.g_rec_type
811 ,p_effective_date IN DATE
812 ,p_message OUT NOCOPY VARCHAR2
813 )RETURN NUMBER IS
814 --get the values for vehicleStatus and SharedVehicle
815 CURSOR c__veh_data_cursor IS
816 SELECT vehicle_status
817 FROM PQP_VEHICLE_REPOSITORY_F
818 WHERE vehicle_repository_id = p_rec.vehicle_repository_id
819 AND p_effective_date between effective_start_date and effective_end_date
820 AND business_group_id = p_rec.business_group_id ;
821
822 l_vehicle_status PQP_VEHICLE_REPOSITORY_F.vehicle_status%type;
823 BEGIN
824 OPEN c__veh_data_cursor;
825 FETCH c__veh_data_cursor INTO l_vehicle_status;
826 CLOSE c__veh_data_cursor;
827 -- check the vehicle status ,if it is InActive
828 IF l_vehicle_status = 'I' THEN
829 p_message := 'Vehicle status is Inactive,so vehicle cannot allocat';
830 RETURN -1; -- vehicle is inActive
831 END IF;
832 RETURN 0;
833 END pqp_veh_eligibility_check;
834 -- end function
835 ------------------------------------------------------------------------------
836 ----------------------------<Foreign key constraint check>-------------------
837 -----------------------------------------------------------------------------
838 Procedure chk_vehicle_exst (
839 p_rec in pqp_val_shd.g_rec_type
840 ,p_effective_date in date
841 ,p_datetrack_mode in varchar2
842 ,p_validation_start_date in date
843 ,p_validation_end_date in date
844 ) IS
845 l_exist VARCHAR2(1);
846 Begin
847 SELECT 'X'
848 INTO l_exist
849 FROM pqp_vehicle_repository_f pvr
850 WHERE pvr.vehicle_repository_id=p_rec.vehicle_repository_id
851 AND pvr.business_group_id=p_rec.business_group_id
852 AND p_effective_date BETWEEN pvr.effective_start_date
853 AND pvr.effective_end_date;
854
855 EXCEPTION
856 ---------
857 WHEN no_data_found then
858 fnd_message.raise_error;
859
860 WHEN others then
861 fnd_message.raise_error;
862
863
864 End;
865 ------------------------------------------------------------------------------
866 ----------------------------<Foreign key constraint check>-------------------
867 -----------------------------------------------------------------------------
868 Procedure chk_asg_exst
869 ( p_rec in pqp_val_shd.g_rec_type
870 ,p_effective_date in date
871 ,p_datetrack_mode in varchar2
872 ,p_validation_start_date in date
873 ,p_validation_end_date in date
874 ) IS
875 l_exist VARCHAR2(1);
876 Begin
877 SELECT 'X'
878 INTO l_exist
879 FROM per_all_assignments_f paa
880 WHERE paa.assignment_id =p_rec.assignment_id
881 AND paa.business_group_id=p_rec.business_group_id
882 AND p_effective_date BETWEEN paa.effective_start_date
883 AND paa.effective_end_date;
884
885 EXCEPTION
886 ---------
887 WHEN no_data_found then
888 fnd_message.raise_error;
889
890 WHEN others then
891 fnd_message.raise_error;
892 End;
893 -----------------------------------------------------------------------------
894 ---------------------Check the Primary Vehicle Allocation-
895 -----------------------------------------------------------------------------
896 --
897 --Primary:One Primary vehicle is allocated to the user.
898 --Secondary:Any additional vehicle is recorded as a secondary vehicle.
899 --Multiple secondary cars can be allocated based on business rules.
900 --
901 FUNCTION pqp_check_veh_alloc_process
902 ( p_rec IN pqp_val_shd.g_rec_type
903 ,p_vehicle_ownership IN VARCHAR2
904 ,p_effective_date IN DATE
905 ,p_message OUT NOCOPY VARCHAR2
906 ) RETURN NUMBER IS
907
908
909 --Used to get the allocations count for company/private vehicles of
910 --(primary/secondary)/(Essential/Casual) for assignment based current
911 --and future date tracks
912 CURSOR c_alloc_count_cursor IS
913 SELECT COUNT(pva.vehicle_allocation_id)
914 FROM pqp_vehicle_repository_f pvr
915 ,pqp_vehicle_allocations_f pva
916 WHERE pvr.vehicle_repository_id = pva.vehicle_repository_id
917 AND pvr.business_group_id = pva.business_group_id
918 AND pvr.vehicle_ownership = p_vehicle_ownership
919 AND pva.usage_type = p_rec.usage_type
920 AND pva.assignment_id = p_rec.assignment_id
921 AND pva.business_group_id=p_rec.business_group_id
922 AND (p_effective_date between
923 pva.effective_start_date and pva.effective_end_date
924 OR p_effective_date <= pva.effective_start_date)
925 AND (p_effective_date between
926 pvr.effective_start_date and pvr.effective_end_date);
927
928 l_rowcount NUMBER ;
929
930 BEGIN
931 hr_utility.set_location('Entering pqp_check_veh_alloc_process', 16);
932 OPEN c_alloc_count_cursor;
933 FETCH c_alloc_count_cursor INTO l_rowcount;
934 CLOSE c_alloc_count_cursor ;
935
936 --check usage type is p/e then max count should be one.
937 IF p_rec.usage_type = 'P' THEN
938 -- if 0 then allowedrec
939 IF l_rowcount > 0 THEN
940 --This is max ,so user canot allocate
941 hr_utility.set_location('Count for P or E:'||l_rowcount, 20);
942 p_message :='Only one Primary vehicle is allocated to the user';
943 RETURN -1;
944 END IF;
945
946 END IF;
947
948 RETURN 0;
949 END pqp_check_veh_alloc_process;
950 -- end function
951 -----------------------------------------------------------------------------
952 ----------------------purge delete function------------------------------
953 ----------------------------------------------------------------------------
954 --
955 --There are any claims that spans across any date cannot be purged.
956 --
957 FUNCTION pqp_purge_veh_alloc
958 (p_rec IN pqp_val_shd.g_rec_type
959 ,p_effective_date IN DATE
960 ,p_message OUT NOCOPY VARCHAR2
961 ) RETURN NUMBER IS
962
963 CURSOR c_claim_count_cursor
964 (cp_registration_number VARCHAR2,
965 cp_assignment_id NUMBER ) IS
966 SELECT count(*)
967 FROM pay_element_types_f pet
968 ,pay_element_type_extra_info pete
969 ,pay_element_entries_f pee
970 ,pay_element_entry_values_f peev2
971 ,pay_input_values_f piv2
972 WHERE pete.eei_information_category='PQP_VEHICLE_MILEAGE_INFO'
973 AND pet.business_group_id=p_rec.business_group_id
974 AND pete.element_type_id =pet.element_type_id
975 AND substr(pete.eei_information1,0,1) in ('C','P')
976 AND pee.assignment_id =cp_assignment_id
977 AND peev2.element_entry_id=pee.element_entry_id
978 AND piv2.element_type_id=pet.element_type_id
979 AND piv2.name in ('Vehicle Reg Number')
980 AND piv2.input_value_id=peev2.input_value_id
981 AND peev2.screen_entry_value =cp_registration_number;
982
983 CURSOR c_claim_veh_det_cursor IS
984 SELECT pvr.registration_number, pva.assignment_id
985 FROM pqp_vehicle_allocations_f pva,
986 pqp_vehicle_repository_f pvr
987 WHERE pva.vehicle_allocation_id= p_rec.vehicle_allocation_id
988 AND pva.vehicle_repository_id =pvr.vehicle_repository_id
989 AND p_effective_date BETWEEN pva.effective_start_date
990 AND pva.effective_end_date
991 AND p_effective_date BETWEEN pvr.effective_start_date
992 AND pvr.effective_end_date ;
993
994
995 l_alloc_count NUMBER ;
996 l_registration_number pqp_vehicle_repository_f.registration_number%TYPE;
997 l_assignment_id pqp_vehicle_allocations_f.assignment_id%TYPE;
998
999 BEGIN
1000
1001 --Curosr for getting the regnumber and assignment
1002 OPEN c_claim_veh_det_cursor;
1003 FETCH c_claim_veh_det_cursor INTO l_registration_number,l_assignment_id;
1004 CLOSE c_claim_veh_det_cursor;
1005
1006 OPEN c_claim_count_cursor(l_registration_number,l_assignment_id);
1007 FETCH c_claim_count_cursor INTO l_alloc_count;
1008 CLOSE c_claim_count_cursor;
1009 --Check claims existence check
1010 IF l_alloc_count > 0 THEN
1011 p_message := 'There are any claims that spans across any date cannot' ||
1012 'be purged';
1013 RETURN -1 ;
1014 END IF;
1015 RETURN 0;
1016 END pqp_purge_veh_alloc;
1017 -- end function
1018
1019
1020 -----------------------------------------------------------------------------
1021 -----------------Used to check the ShareCompany Car/Share Private -----------
1022 -----------------------------------------------------------------------------
1023 --
1024 --Share Company Car:This field has a list of values 'Yes' and 'No'.
1025 --'Yes' means the Primary vehicle can be shared across employees.
1026 --'No' would mean the car will not be assigned to other assignments.
1027 --Default value will be 'No'.
1028 -- or
1029 --Share Private Car: This field has a list of values 'Yes' and 'No'.
1030 --'Yes' means the Private vehicle can be shared across employees.
1031 --'No' would mean the car will not be assigned to assignments.
1032 --Default value will be 'No'.
1033
1034
1035 FUNCTION pqp_config_shared_veh
1036 (p_rec IN pqp_val_shd.g_rec_type,
1037 p_vehicle_ownership IN VARCHAR2 ,
1038 p_shared_vehicle IN VARCHAR2,
1039 p_effective_date IN DATE,
1040 p_legislation_code IN VARCHAR2,
1041 p_seg_col_name IN VARCHAR2,
1042 p_table_name IN VARCHAR2,
1043 p_information_category IN VARCHAR2,
1044 p_message OUT NOCOPY VARCHAR2
1045 )RETURN NUMBER IS
1046 --Used to get the allocation count for regId and not for this
1047 --assigment personId
1048 --because if we use this assignment ,user can allocate vehicle
1049 --irespective of shared
1050 --vehicle setting at configuration
1051
1052 CURSOR c_person_alloc_count_cursor IS
1053 SELECT COUNT(pva.vehicle_allocation_id)
1054 FROM pqp_vehicle_allocations_f pva,
1055 per_all_assignments_f paa
1056 WHERE paa.assignment_id = pva.assignment_id
1057 AND pva.assignment_id NOT IN ( SELECT assignment_id
1058 FROM per_all_assignments_f
1059 WHERE person_id = (SELECT DISTINCT person_id
1060 FROM per_all_assignments_f
1061 WHERE assignment_id=p_rec.assignment_id))
1062 AND pva.vehicle_repository_id = p_rec.vehicle_repository_id
1063 AND pva.business_group_id=p_rec.business_group_id
1064 AND p_effective_date BETWEEN
1065 paa.effective_start_date AND paa.effective_end_date
1066 AND p_effective_date BETWEEN
1067 pva.effective_start_date AND pva.effective_end_date ;
1068
1069 l_rowcount NUMBER ;
1070 l_veh_shared VARCHAR2(20);
1071
1072 BEGIN
1073 hr_utility.set_location('Entering pqp_config_shared_veh',32);
1074 OPEN c_person_alloc_count_cursor;
1075 FETCH c_person_alloc_count_cursor INTO l_rowcount ;
1076 CLOSE c_person_alloc_count_cursor ;
1077 hr_utility.set_location('alloc count persons :'||l_rowcount,35);
1078 IF l_rowcount > 0 THEN
1079
1080 --Checking the shared vehicle flag for this repositoryId
1081 IF p_shared_vehicle = 'N' THEN
1082 p_message := 'This vehicle is not Shared at '||
1083 'repository level.so User cannot assign to '||
1084 'multiple assignments to other personIds';
1085 RETURN -1;
1086 END IF;
1087
1088 /* --Check for Configuration Values veh shared flag
1089 l_veh_shared := pqp_vre_bus.PQP_GET_CONFIG_VALUE(
1090 p_rec.business_group_id,
1091 p_legislation_code,
1092 p_seg_col_name,
1093 p_table_name,
1094 p_information_category);
1095
1096
1097 --Yes means ,user can assign to differnt persons assignments
1098 IF l_veh_shared = 'N' THEN
1099 p_message := 'This vehicle is not Shared at '||
1100 'configuration level.so User cannot assign to '||
1101 'multiple assignments to other personIds';
1102 RETURN -1;
1103 END IF;
1104 */
1105 END IF;
1106
1107 --If count is zero ,so User can allocate this veh to his assignment
1108 RETURN 0;
1109 END pqp_config_shared_veh;
1110 -- end function
1111
1112 --------------------------------------------------------------------------
1113 ------------------Check reg exist for alloc------------------------------
1114 -------------------------------------------------------------------------
1115 FUNCTION chk_reg_exist_for_alloc
1116 (p_rec IN pqp_val_shd.g_rec_type
1117 ,p_effective_date IN DATE
1118 ,p_message out NOCOPY VARCHAR2
1119 ) RETURN NUMBER IS
1120
1121 CURSOR c_alloc_count_cursor IS
1122 SELECT COUNT(pva.vehicle_allocation_id)
1123 FROM pqp_vehicle_allocations_f pva
1124 WHERE pva.assignment_id = p_rec.assignment_id
1125 AND pva.vehicle_repository_id = p_rec.vehicle_repository_id
1126 AND pva.business_group_id = p_rec.business_group_id
1127 AND (p_effective_date
1128 BETWEEN pva.effective_start_date AND pva.effective_end_date
1129 OR p_effective_date <= pva.effective_start_date);
1130
1131 l_rowcount NUMBER;
1132
1133 BEGIN
1134 hr_utility.set_location('Entering chk_reg_exist_for_alloc', 7);
1135 OPEN c_alloc_count_cursor;
1136 FETCH c_alloc_count_cursor INTO l_rowcount;
1137 CLOSE c_alloc_count_cursor;
1138 hr_utility.set_location('Vehicle Reg Exist:'||l_rowcount, 10);
1139 IF l_rowcount > 0 THEN
1140 p_message := 'Registration number is already exist in this date tracks';
1141 RETURN -1 ;
1142 END IF;
1143 RETURN 0;
1144 END chk_reg_exist_for_alloc;
1145 -- end function
1146 -----------------------------------------------------------------------------
1147 ----------------------End date delete----------------------------------------
1148 -----------------------------------------------------------------------------
1149 --
1150 --There are no pending claims that spans across this date
1151 --
1152 FUNCTION pqp_enddate_veh_alloc
1153 (p_rec IN pqp_val_shd.g_rec_type
1154 ,p_effective_date IN DATE
1155 ,p_message OUT NOCOPY VARCHAR2
1156 ) RETURN NUMBER IS
1157 --Get the claim count for future and current date tracks
1158 CURSOR c_claim_count_cursor(cp_registration_number VARCHAR2,
1159 cp_assignment_id NUMBER ) IS
1160 SELECT count(*)
1161 FROM pay_element_types_f pet
1162 ,pay_element_type_extra_info pete
1163 ,pay_element_entries_f pee
1164 ,pay_element_entry_values_f peev2
1165 ,pay_input_values_f piv2
1166 WHERE pete.EEI_INFORMATION_CATEGORY='PQP_VEHICLE_MILEAGE_INFO'
1167 AND pet.business_group_id=p_rec.business_group_id
1168 AND pete.element_type_id =pet.element_type_id
1169 AND substr(pete.EEI_INFORMATION1,0,1) in ('C','P')
1170 AND pee.assignment_id =cp_assignment_id
1171 AND peev2.element_entry_id=pee.element_entry_id
1172 AND piv2.element_type_id=pet.element_type_id
1173 AND piv2.name in ('Vehicle Reg Number')
1174 AND piv2.input_value_id=peev2.input_value_id
1175 AND peev2.SCREEN_ENTRY_VALUE =cp_registration_number
1176 AND p_effective_date < pee.effective_end_date;
1177
1178
1179 CURSOR c_clm_veh_det_cursor IS
1180 SELECT pvr.registration_number, pva.assignment_id
1181 FROM pqp_vehicle_allocations_f pva,
1182 pqp_vehicle_repository_f pvr
1183 WHERE pva.vehicle_allocation_id= p_rec.vehicle_allocation_id
1184 AND pva.vehicle_repository_id =pvr.vehicle_repository_id
1185 AND p_effective_date BETWEEN pva.effective_start_date
1186 AND pva.effective_end_date
1187 AND p_effective_date BETWEEN pvr.effective_start_date
1188 AND pvr.effective_end_date ;
1189
1190 l_alloc_count NUMBER ;
1191 l_registration_number pqp_vehicle_repository_f.registration_number%TYPE;
1192 l_assignment_id pqp_vehicle_allocations_f.assignment_id%TYPE;
1193
1194 BEGIN
1195 OPEN c_clm_veh_det_cursor;
1196 FETCH c_clm_veh_det_cursor INTO l_registration_number,l_assignment_id;
1197 CLOSE c_clm_veh_det_cursor;
1198
1199 OPEN c_claim_count_cursor(l_registration_number,l_assignment_id);
1200 FETCH c_claim_count_cursor INTO l_alloc_count;
1201 CLOSE c_claim_count_cursor;
1202 --Check claims existence check
1203 IF l_alloc_count > 0 THEN
1204 p_message := 'There are pending cliams in future,so we cannot enddated';
1205 RETURN -1 ;
1206 END IF;
1207 RETURN 0;
1208 END pqp_enddate_veh_alloc;
1209 -- end function
1210
1211 -----------------------------------------------------------------------------
1212 ---------------------Check the DefaultVehicle Allocation---------------------
1213 -----------------------------------------------------------------------------
1214 --
1215 --Default Vehicle:Checking only one default private vehicle is allocated at
1216 --any point of time to that assignment
1217 --
1218 FUNCTION chk_defult_private_veh
1219 ( p_rec IN pqp_val_shd.g_rec_type
1220 ,p_vehicle_ownership IN VARCHAR2
1221 ,p_effective_date IN DATE
1222 ,p_message OUT NOCOPY VARCHAR2
1223 ) RETURN NUMBER IS
1224
1225
1226 --Used to get the allocations count for private vehicle default count
1227 CURSOR c_alloc_count_cursor IS
1228 SELECT COUNT(pva.vehicle_allocation_id)
1229 FROM pqp_vehicle_repository_f pvr
1230 ,pqp_vehicle_allocations_f pva
1231 WHERE pvr.vehicle_repository_id = pva.vehicle_repository_id
1232 AND pvr.business_group_id = pva.business_group_id
1233 AND pvr.vehicle_ownership = p_vehicle_ownership
1234 AND pva.default_vehicle = 'Y'
1235 AND pva.assignment_id = p_rec.assignment_id
1236 AND pva.business_group_id=p_rec.business_group_id
1237 AND (p_effective_date between
1238 pva.effective_start_date and pva.effective_end_date
1239 OR p_effective_date <= pva.effective_start_date)
1240 AND (p_effective_date between
1241 pvr.effective_start_date and pvr.effective_end_date);
1242
1243 l_rowcount NUMBER ;
1244
1245 BEGIN
1246 hr_utility.set_location('Entering pqp_check_veh_alloc_process', 16);
1247 OPEN c_alloc_count_cursor;
1248 FETCH c_alloc_count_cursor INTO l_rowcount;
1249 CLOSE c_alloc_count_cursor ;
1250
1251 IF l_rowcount > 0 THEN
1252 --This is max default vehicle ,so user canot allocate
1253 hr_utility.set_location('Count for default:'||l_rowcount, 20);
1254 p_message :='Only one Default vehicle allocated to the user';
1255 RETURN -1;
1256 END IF;
1257 RETURN 0;
1258 END chk_defult_private_veh;
1259 -- end function
1260
1261 -----------------------------------------------------------------------------
1262 --Delete process for NI car Primary element entry
1263 ----------------------------------------------------------------------------
1264
1265 Procedure del_ni_car_entry (p_business_group_id IN NUMBER
1266 ,p_assignment_id IN NUMBER
1267 ,p_allocation_id IN NUMBER
1268 ,p_effective_date IN DATE
1269 )
1270 is
1271 CURSOR c_chk_ni_car_pri
1272 IS
1273 SELECT pee.element_entry_id element_entry_id
1274 FROM pay_element_entries_f PEE
1275 ,pay_element_links_f pel
1276 ,pay_element_types_f pet
1277 WHERE pee.assignment_id=p_assignment_id
1278 AND pel.business_group_id=p_business_group_id
1279 and p_effective_date BETWEEN pee.effective_start_date
1280 AND pee.effective_end_date
1281 and p_effective_date BETWEEN pel.effective_start_date
1282 AND pel.effective_end_date
1283 and p_effective_date BETWEEN pet.effective_start_date
1284 AND pet.effective_end_date
1285 and pee.element_link_id=pel.element_link_id
1286 AND pel.element_type_id=pet.element_type_id
1287 AND pet.element_name = 'NI Car Primary';
1288
1289 CURSOR c_chk_sec_car (cp_allocation_id IN NUMBER)
1290 is
1291 SELECT pee.element_entry_id element_entry_id
1292 FROM pay_element_entries_f PEE
1293 ,pay_element_links_f pel
1294 ,pay_element_types_f pet
1295 ,pay_input_values_f piv
1296 , pay_element_entry_values_f peev
1297 , pqp_vehicle_allocations_f pva
1298 ,pqp_vehicle_repository_f pvr
1299 WHERE pee.ASSIGNMENT_ID=p_assignment_id
1300 AND pel.business_group_id=p_business_group_id
1301 AND pee.element_link_id=pel.element_link_id
1302 AND pel.element_type_id=pet.element_type_id
1303 AND pet.element_name = 'NI Car Secondary'
1304 AND piv.element_type_id = pet.element_type_id
1305 AND piv.name = 'Registration Number'
1306 AND peev.input_value_id=piv.input_value_id
1307 AND peev.element_entry_id=pee.element_entry_id
1308 AND pva.vehicle_allocation_id=cp_allocation_id
1309 AND pva.vehicle_repository_id=pvr.VEHICLE_REPOSITORY_ID
1310 AND peev.screen_entry_value=pvr.registration_number
1311 AND pel.business_group_id=piv.business_group_id
1312 AND piv.business_group_id=pva.business_group_id
1313 AND piv.business_group_id=pvr.business_group_id
1314 AND pet.legislation_code='GB'
1315 AND p_effective_date BETWEEN pee.effective_start_date
1316 AND pee.effective_end_date
1317 AND p_effective_date BETWEEN pel.effective_start_date
1318 AND pel.effective_end_date
1319 AND p_effective_date BETWEEN pet.effective_start_date
1320 AND pet.effective_end_date
1321 AND p_effective_date BETWEEN piv.effective_start_date
1322 AND piv.effective_end_date
1323 AND p_effective_date BETWEEN peev.effective_start_date
1324 AND peev.effective_end_date
1325 AND p_effective_date BETWEEN pva.effective_start_date
1326 AND pva.effective_end_date
1327 AND p_effective_date BETWEEN pvr.effective_start_date
1328 AND pvr.effective_end_date;
1329 l_chk_ni_car_pri c_chk_ni_car_pri%ROWTYPE;
1330 l_chk_sec_car c_chk_sec_car%ROWTYPE;
1331
1332 Begin
1333
1334 OPEN c_chk_ni_car_pri;
1335 FETCH c_chk_ni_car_pri INTO l_chk_ni_car_pri;
1336 CLOSE c_chk_ni_car_pri;
1337
1338 IF l_chk_ni_car_pri.element_entry_id IS NOT NULL THEN
1339 hr_entry_api.delete_element_entry
1340 (
1341 p_dt_delete_mode => 'DELETE',
1342 p_session_date => p_effective_date,
1343 p_element_entry_id => l_chk_ni_car_pri.element_entry_id
1344 );
1345 END IF;
1346
1347 OPEN c_chk_sec_car (p_allocation_id);
1348 FETCH c_chk_sec_car INTO l_chk_sec_car;
1349 CLOSE c_chk_sec_car;
1350
1351 IF l_chk_sec_car.element_entry_id IS NOT NULL THEN
1352 hr_entry_api.delete_element_entry
1353 (
1354 p_dt_delete_mode => 'DELETE',
1355 p_session_date => p_effective_date,
1356 p_element_entry_id => l_chk_sec_car.element_entry_id
1357 );
1358 END IF;
1359
1360 EXCEPTION
1361 --------
1362 WHEN OTHERS THEN
1363 NULL;
1364
1365 End;
1366
1367 -- ----------------------------------------------------------------------------
1368 -- |---------------------------< insert_validate >----------------------------|
1369 -- ----------------------------------------------------------------------------
1370 Procedure insert_validate
1371 (p_rec in pqp_val_shd.g_rec_type
1372 ,p_effective_date in date
1373 ,p_datetrack_mode in varchar2
1374 ,p_validation_start_date in date
1375 ,p_validation_end_date in date
1376 ) is
1377
1378 --Used to get the ownership for repId at once
1379 CURSOR c_veh_det_cursor IS
1380 SELECT pvr.vehicle_ownership
1381 ,pvr.vehicle_status
1382 ,pvr.shared_vehicle
1383 ,pvr.initial_registration
1384 ,pvr.registration_number
1385 FROM pqp_vehicle_repository_f pvr
1386 WHERE pvr.vehicle_repository_id = p_rec.vehicle_repository_id
1387 AND pvr.business_group_id= p_rec.business_group_id
1388 AND p_effective_date BETWEEN
1389 pvr.effective_start_date AND
1390 pvr.effective_end_date;
1391 --
1392 l_proc varchar2(72) := g_package||'insert_validate';
1393 l_return_status NUMBER ;
1394 l_return_count NUMBER;
1395 l_number_value NUMBER;
1396 l_cmy_veh_alloc_count NUMBER;
1397 l_pri_veh_alloc_count NUMBER;
1398 l_message VARCHAR2(2500) ;
1399 l_max_conf_count VARCHAR2(10);
1400 l_vehicle_ownership pqp_vehicle_repository_f.vehicle_ownership%type;
1401 l_legislation_code varchar2(150);
1402 l_vehicle_status pqp_vehicle_repository_f.vehicle_status%type;
1403 l_shared_vehicle pqp_vehicle_repository_f.shared_vehicle%type;
1404 l_initial_registration pqp_vehicle_repository_f.initial_registration%type;
1405 l_registration_number pqp_vehicle_repository_f.registration_number%type;
1406 --
1407
1408 Begin
1409 hr_utility.set_location('Entering:'||l_proc, 5);
1410 --
1411 -- Call all supporting business operations
1412 --
1413 hr_api.validate_bus_grp_id
1414 (p_business_group_id => p_rec.business_group_id
1415 ,p_associated_column1 => pqp_val_shd.g_tab_nam
1416 || '.BUSINESS_GROUP_ID');
1417
1418 --Used to get the ownership for repId at once
1419 OPEN c_veh_det_cursor;
1420 FETCH c_veh_det_cursor INTO l_vehicle_ownership,
1421 l_vehicle_status,l_shared_vehicle,
1422 l_initial_registration,l_registration_number;
1423 CLOSE c_veh_det_cursor;
1424
1425
1426 --Checking the vehicle status ,if it Inactive then
1427 --vehicle cannot be allocate
1428 IF l_vehicle_status = 'I' THEN
1429 fnd_message.set_name('PQP', 'PQP_230925_INACTIVE_VEH_ASSIGN');
1430 fnd_message.raise_error;
1431 END IF;
1432
1433 --Fixing the bug #2864591
1434 --Checking the allocation effective start date is
1435 --greter than or equal to veh reg date
1436 IF l_initial_registration IS NOT NULL THEN
1437 IF l_initial_registration > p_effective_date THEN
1438 fnd_message.set_name('PQP', 'PQP_230926_REG_DATE_ASSIGN_ERR');
1439 fnd_message.set_token('TOKEN',l_registration_number);
1440 fnd_message.raise_error;
1441 END IF;
1442 END IF;
1443
1444
1445 --Checking Usage Type Mandatory
1446 IF p_rec.usage_type='P' OR p_rec.usage_type='S' THEN
1447 l_return_status := chk_mandatory(
1448 p_argument =>'Usage Type'
1449 ,p_argument_value => p_rec.usage_type
1450 ,p_message => l_message);
1451
1452 IF l_return_status = -1 THEN
1453 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1454 fnd_message.set_token('FEILD','Usage Type');
1455 fnd_message.raise_error;
1456 END IF;
1457 END IF;
1458
1459 --Checking usage Type lookup validation
1460 --Usage type lookup type will vary based on ownership
1461 --IF l_vehicle_ownership = 'C' THEN
1462 IF l_vehicle_ownership in ('C','PL_LEC','PL_LC') THEN
1463 l_return_status := chk_lookup(
1464 p_vehicle_allocation_id => p_rec.vehicle_allocation_id
1465 ,p_lookup_type =>'PQP_COMPANY_VEHICLE_USER'
1466 ,p_lookup_code => p_rec.usage_type
1467 ,p_effective_date => p_effective_date
1468 ,p_validation_start_date => p_validation_start_date
1469 ,p_validation_end_date => p_validation_end_date);
1470
1471 IF l_return_status = -1 THEN
1472 fnd_message.set_name('PQP','PQP_230722_VLD_USG_OWNRSHP');
1473 fnd_message.raise_error;
1474 END IF;
1475 ELSE
1476 /*
1477 l_return_status := chk_lookup(
1478 p_vehicle_allocation_id => p_rec.vehicle_allocation_id
1479 ,p_lookup_type =>'PQP_PRIVATE_VEHICLE_USER'
1480 ,p_lookup_code => p_rec.usage_type
1481 ,p_effective_date => p_effective_date
1482 ,p_validation_start_date => p_validation_start_date
1483 ,p_validation_end_date => p_validation_end_date);
1484
1485 IF l_return_status = -1 THEN
1486 fnd_message.set_name('PQP','PQP_230722_VLD_USG_OWNRSHP');
1487 fnd_message.raise_error;
1488 END IF;*/
1489 NULL;
1490 END IF;
1491
1492 --Checking calculation method lookup validation
1493 l_return_status := chk_lookup(
1494 p_vehicle_allocation_id => p_rec.vehicle_allocation_id
1495 ,p_lookup_type =>'PQP_VEHICLE_CALC_METHOD'
1496 ,p_lookup_code => p_rec.calculation_method
1497 ,p_effective_date => p_effective_date
1498 ,p_validation_start_date => p_validation_start_date
1499 ,p_validation_end_date => p_validation_end_date);
1500
1501 IF l_return_status = -1 THEN
1502 fnd_message.set_name('PQP','PQP_230824_VALID_CALC_METHOD');
1503 fnd_message.raise_error;
1504 END IF;
1505
1506 --Checking the regnumber exist
1507 l_return_status := chk_reg_exist_for_alloc(
1508 p_rec => p_rec
1509 ,p_effective_date => p_effective_date
1510 ,p_message => l_message );
1511
1512 IF l_return_status = -1 THEN
1513 fnd_message.set_name('PQP', 'PQP_230759_ALLOC_REG_EXIST');
1514 fnd_message.raise_error;
1515 END IF;
1516
1517
1518 --Checking the fuelcard/fuelNumber/fuelbenifit value
1519 --for cmy vehicle allocation
1520 IF l_vehicle_ownership in ('C','PL_LEC','PL_LC') THEN
1521
1522 l_return_status := pqp_check_cmyveh_fuel_card (
1523 p_rec => p_rec
1524 ,p_vehicle_ownership => l_vehicle_ownership
1525 ,p_effective_date => p_effective_date
1526 ,p_message => l_message );
1527
1528 IF l_return_status = -1 THEN
1529 fnd_message.set_name('PQP', 'PQP_230738_COMP_OWNR_MNDTRY');
1530 fnd_message.set_token('TOKEN','Fuel Card');
1531 fnd_message.raise_error;
1532 END IF;
1533 END IF;
1534
1535 --Getting the legislationId for business groupId
1536 l_legislation_code :=
1537 pqp_vre_bus.get_legislation_code(p_rec.business_group_id);
1538
1539
1540 --Checking the Primary vehicle allocation
1541 --If usage type is 'PRIMARY' then check is there any P vehicle
1542 --for this assignment
1543 IF p_rec.usage_type = 'P' THEN
1544 l_return_count := pqp_check_veh_alloc_process(
1545 p_rec =>p_rec
1546 ,p_vehicle_ownership =>l_vehicle_ownership
1547 ,p_effective_date =>p_effective_date
1548 ,p_message => l_message );
1549
1550 -- If there is already allocation then throw error
1551 -- because it should be only one entry for P/E
1552 IF l_return_count = -1 THEN
1553 fnd_message.set_name('PQP', 'PQP_230708_PMRY_RESTRICT');
1554 fnd_message.raise_error;
1555 END IF;
1556 END IF;
1557
1558
1559
1560 --If company vehcicle then check max limit is reached or not.
1561 IF l_vehicle_ownership in ('C','PL_LEC','PL_LC') THEN
1562 --call max allocations count for assignment for this ownership
1563 l_cmy_veh_alloc_count := pqp_get_max_allowed_veh(
1564 p_rec =>p_rec
1565 ,p_vehicle_ownership =>l_vehicle_ownership
1566 ,p_effective_date =>p_effective_date );
1567
1568
1569
1570 --call configuration max allowed cmy vehicles
1571 l_max_conf_count := pqp_vre_bus.PQP_GET_CONFIG_VALUE(
1572 p_business_group_id => p_rec.business_group_id,
1573 p_legislation_code => l_legislation_code,
1574 p_seg_col_name =>'MaxCmyVehAllow',
1575 p_table_name =>'p_table_name',
1576 p_information_category =>'PQP_VEHICLE_MILEAGE');
1577 hr_utility.set_location('Count conf maximum count:'||l_max_conf_count,23);
1578 --if null then no limit
1579 IF l_max_conf_count IS NOT NULL THEN
1580 Begin
1581 l_number_value := fnd_number.CANONICAL_TO_NUMBER(l_max_conf_count);
1582 End;
1583 IF l_cmy_veh_alloc_count >= l_number_value THEN
1584 l_message := 'person already reached the maximum limit';
1585 fnd_message.set_name('PQP', 'PQP_230709_MAX_COMP_ALLOC');
1586 fnd_message.raise_error;
1587 END IF;
1588 END IF;
1589
1590 --Checking the Share Company Car validation
1591 l_return_count := pqp_config_shared_veh(
1592 p_rec => p_rec,
1593 p_vehicle_ownership => l_vehicle_ownership,
1594 p_shared_vehicle => l_shared_vehicle,
1595 p_effective_date => p_effective_date,
1596 p_legislation_code => l_legislation_code,
1597 p_seg_col_name =>'ShareCmyCar',
1598 p_table_name =>'p_table_name',
1599 p_information_category =>'PQP_VEHICLE_MILEAGE',
1600 p_message => l_message );
1601
1602 IF l_return_count = -1 THEN
1603 fnd_message.set_name('PQP', 'PQP_230707_VEH_ALLOC_INFO');
1604 fnd_message.raise_error;
1605 END IF;
1606 END IF;
1607
1608
1609
1610 --If Private vehcicle then check max limit is reached or not.
1611 IF l_vehicle_ownership in ('P','PL_PC') THEN
1612
1613 --Checking default private Vehicle already exist or not
1614 --If no private default vehicle ,then user can allocate private vehicle
1615 --If there is already default vehicle allocated ,user cannot allocate
1616 --one more default vehicle.
1617
1618 IF p_rec.default_vehicle = 'Y' THEN
1619 l_return_count := chk_defult_private_veh(
1620 p_rec =>p_rec
1621 ,p_vehicle_ownership =>l_vehicle_ownership
1622 ,p_effective_date =>p_effective_date
1623 ,p_message =>l_message );
1624 -- If there is already allocation then throw error
1625 -- because it should be only one entry for default
1626 IF l_return_count = -1 THEN
1627 fnd_message.set_name('PQP', 'PQP_230746_ONE_ESS_RSTRICT');
1628 fnd_message.raise_error;
1629 END IF;
1630 END IF;
1631
1632
1633 --call max allocations count for assignment for this ownership
1634 l_pri_veh_alloc_count := pqp_get_max_allowed_veh(
1635 p_rec =>p_rec
1636 ,p_vehicle_ownership =>l_vehicle_ownership
1637 ,p_effective_date =>p_effective_date );
1638
1639 --call configuration max allowed Pri vehicles
1640 l_max_conf_count := pqp_vre_bus.PQP_GET_CONFIG_VALUE(
1641 p_business_group_id => p_rec.business_group_id ,
1642 p_legislation_code => l_legislation_code,
1643 p_seg_col_name =>'MaxPriVehAllow',
1644 p_table_name =>'p_table_name',
1645 p_information_category =>'PQP_VEHICLE_MILEAGE');
1646
1647 --if null then no limit
1648 IF l_max_conf_count IS NOT NULL THEN
1649 Begin
1650 l_number_value := fnd_number.CANONICAL_TO_NUMBER(l_max_conf_count);
1651 End;
1652
1653 IF l_pri_veh_alloc_count >= l_number_value THEN
1654 l_message := 'person already reached the maximum limit';
1655 fnd_message.set_name('PQP', 'PQP_230710_MAX_PVT_ALLOC');
1656 fnd_message.raise_error;
1657 END IF;
1658 END IF;
1659 -- cheking for share Private car
1660 l_return_count := pqp_config_shared_veh(
1661 p_rec => p_rec,
1662 p_vehicle_ownership => l_vehicle_ownership,
1663 p_shared_vehicle => l_shared_vehicle,
1664 p_effective_date => p_effective_date,
1665 p_legislation_code => l_legislation_code,
1666 p_seg_col_name =>'SharePriCar',
1667 p_table_name =>'p_table_name',
1668 p_information_category =>'PQP_VEHICLE_MILEAGE',
1669 p_message => l_message );
1670
1671 IF l_return_count = -1 THEN
1672 fnd_message.set_name('PQP', 'PQP_230707_VEH_ALLOC_INFO');
1673 fnd_message.raise_error;
1674 END IF;
1675 END IF;
1676 Exception
1677 when app_exception.application_exception then
1678 IF hr_multi_message.exception_add
1679 (
1680 p_same_associated_columns => 'Y'
1681 ) then
1682 raise;
1683 END IF;
1684
1685
1686 hr_multi_message.end_validation_set;
1687 --
1688 -- Validate Dependent Attributes
1689 --
1690 --
1691 pqp_val_bus.chk_ddf(p_rec);
1692 --
1693 pqp_val_bus.chk_df(p_rec);
1694 --
1695 hr_utility.set_location(' Leaving:'||l_proc, 10);
1696 End insert_validate;
1697 --
1698 -- ----------------------------------------------------------------------------
1699 -- |---------------------------< update_validate >----------------------------|
1700 -- ----------------------------------------------------------------------------
1701 Procedure update_validate
1702 (p_rec in pqp_val_shd.g_rec_type
1703 ,p_effective_date in date
1704 ,p_datetrack_mode in varchar2
1705 ,p_validation_start_date in date
1706 ,p_validation_end_date in date
1707 ) is
1708
1709 --Used to get the ownership for repId at once
1710 CURSOR c_veh_det_cursor IS
1711 SELECT pvr.vehicle_ownership
1712 ,pvr.vehicle_status
1713 ,pvr.initial_registration
1714 ,pvr.registration_number
1715 FROM pqp_vehicle_repository_f pvr
1716 WHERE pvr.vehicle_repository_id = p_rec.vehicle_repository_id
1717 AND pvr.business_group_id= p_rec.business_group_id
1718 AND p_effective_date BETWEEN
1719 pvr.effective_start_date
1720 AND pvr.effective_end_date;
1721
1722 --
1723 l_proc varchar2(72) := g_package||'update_validate';
1724 l_validation_start_date date;
1725 l_validation_end_date date;
1726 l_return_status NUMBER ;
1727 l_vehicle_status pqp_vehicle_repository_f.vehicle_status%type;
1728 l_return_count NUMBER;
1729 l_message VARCHAR2(2500) ;
1730 l_vehicle_ownership pqp_vehicle_repository_f.vehicle_ownership%TYPE;
1731 l_initial_registration pqp_vehicle_repository_f.initial_registration%type;
1732 l_registration_number pqp_vehicle_repository_f.registration_number%type;
1733
1734
1735 --
1736 Begin
1737 hr_utility.set_location('Entering:'||l_proc, 5);
1738 --
1739 -- Call all supporting business operations
1740 --
1741 hr_api.validate_bus_grp_id
1742 (p_business_group_id => p_rec.business_group_id
1743 ,p_associated_column1 => pqp_val_shd.g_tab_nam
1744 || '.BUSINESS_GROUP_ID');
1745 --
1746 -- After validating the set of important attributes,
1747 -- if Multiple Message detection is enabled and at least
1748 -- one error has been found then abort further validation.
1749 --
1750
1751 --
1752 -- Validate Dependent Attributes
1753 --
1754 -- Call the datetrack update integrity operation
1755 --
1756 dt_update_validate
1757 (p_datetrack_mode => p_datetrack_mode
1758 ,p_validation_start_date => p_validation_start_date
1759 ,p_validation_end_date => p_validation_end_date
1760 );
1761 --
1762 chk_non_updateable_args
1763 (p_effective_date => p_effective_date
1764 ,p_rec => p_rec
1765 );
1766 --
1767 chk_vehicle_exst ( p_rec =>p_rec
1768 ,p_effective_date =>p_effective_date
1769 ,p_datetrack_mode =>p_datetrack_mode
1770 ,p_validation_start_date =>p_validation_start_date
1771 ,p_validation_end_date =>p_validation_end_date
1772 );
1773
1774 chk_asg_exst ( p_rec =>p_rec
1775 ,p_effective_date =>p_effective_date
1776 ,p_datetrack_mode =>p_datetrack_mode
1777 ,p_validation_start_date =>p_validation_start_date
1778 ,p_validation_end_date =>p_validation_end_date
1779 );
1780
1781
1782 OPEN c_veh_det_cursor;
1783 FETCH c_veh_det_cursor INTO l_vehicle_ownership,l_vehicle_status,
1784 l_initial_registration,l_registration_number;
1785 CLOSE c_veh_det_cursor;
1786
1787 --Used to get the ownership for repId at once
1788 IF pqp_val_shd.g_old_rec.vehicle_repository_id <>
1789 p_rec.vehicle_repository_id THEN
1790
1791 IF l_vehicle_status = 'I' THEN
1792 l_message := 'Vehicle status is Inactive,so vehicle cannot update';
1793 fnd_message.set_name('PQP', 'PQP_230925_INACTIVE_VEH_ASSIGN');
1794 fnd_message.raise_error;
1795 END IF;
1796 END IF;
1797
1798 --Fixing the bug #2864591
1799 --Checking the allocation effective start date is
1800 --greter than or equal to veh reg date
1801 IF l_initial_registration IS NOT NULL THEN
1802 IF l_initial_registration > p_effective_date THEN
1803 fnd_message.set_name('PQP', 'PQP_230926_REG_DATE_ASSIGN_ERR');
1804 fnd_message.set_token('TOKEN',l_registration_number);
1805 fnd_message.raise_error;
1806 END IF;
1807 END IF;
1808
1809 --Checking for value change
1810 IF ( nvl(pqp_val_shd.g_old_rec.usage_type,hr_api.g_varchar2)
1811 <> nvl(p_rec.usage_type,hr_api.g_varchar2) ) THEN
1812
1813 --Checking usage Type lookup validation
1814 --Usage type lookup type will vary based on ownership
1815 IF l_vehicle_ownership in ('C','PL_LEC','PL_LC') THEN
1816 --If not equal then Checking usage_type lookup validation
1817 l_return_status := chk_lookup(
1818 p_vehicle_allocation_id => p_rec.vehicle_allocation_id
1819 ,p_lookup_type =>'PQP_COMPANY_VEHICLE_USER'
1820 ,p_lookup_code => p_rec.usage_type
1821 ,p_effective_date => p_effective_date
1822 ,p_validation_start_date => p_validation_start_date
1823 ,p_validation_end_date => p_validation_end_date);
1824 IF l_return_status = -1 THEN
1825 fnd_message.set_name('PQP','PQP_230722_VLD_USG_OWNRSHP');
1826 fnd_message.raise_error;
1827 END IF;
1828 ELSE
1829 /*
1830 l_return_status := chk_lookup(
1831 p_vehicle_allocation_id => p_rec.vehicle_allocation_id
1832 ,p_lookup_type =>'PQP_PRIVATE_VEHICLE_USER'
1833 ,p_lookup_code => p_rec.usage_type
1834 ,p_effective_date => p_effective_date
1835 ,p_validation_start_date => p_validation_start_date
1836 ,p_validation_end_date => p_validation_end_date);
1837 IF l_return_status = -1 THEN
1838 fnd_message.set_name('PQP','PQP_230722_VLD_USG_OWNRSHP');
1839 fnd_message.raise_error;
1840 END IF;*/
1841 NULL;
1842 END IF;
1843 END IF;
1844
1845 --Checking for value change
1846 IF ( nvl(pqp_val_shd.g_old_rec.calculation_method,hr_api.g_varchar2)
1847 <> nvl(p_rec.calculation_method,hr_api.g_varchar2) ) THEN
1848 --Checking calculation method lookup validation
1849 l_return_status := chk_lookup(
1850 p_vehicle_allocation_id => p_rec.vehicle_allocation_id
1851 ,p_lookup_type =>'PQP_VEHICLE_CALC_METHOD'
1852 ,p_lookup_code => p_rec.calculation_method
1853 ,p_effective_date => p_effective_date
1854 ,p_validation_start_date => p_validation_start_date
1855 ,p_validation_end_date => p_validation_end_date);
1856
1857 IF l_return_status = -1 THEN
1858 fnd_message.set_name('PQP','PQP_230824_VALID_CALC_METHOD');
1859 fnd_message.raise_error;
1860 END IF;
1861 END IF;
1862
1863
1864 --checking only if there is change in UsageType for update
1865 IF pqp_val_shd.g_old_rec.usage_type <> p_rec.usage_type THEN
1866
1867 --If usage type is 'PRIMARY' then check is there any
1868 --Private vehicles for this assignment
1869 IF p_rec.usage_type = 'P' THEN
1870 l_return_count := pqp_check_veh_alloc_process(
1871 p_rec =>p_rec
1872 ,p_vehicle_ownership =>l_vehicle_ownership
1873 ,p_effective_date =>p_effective_date
1874 ,p_message =>l_message );
1875
1876 -- If there is already allocation then throw error,because it
1877 --should be only one entry for P
1878 IF l_return_count = -1 THEN
1879 fnd_message.set_name('PQP', 'PQP_230708_PMRY_RESTRICT');
1880 fnd_message.raise_error;
1881 END IF;
1882 END IF;
1883 END IF;
1884
1885
1886
1887
1888 IF pqp_val_shd.g_old_rec.default_vehicle = 'N' THEN
1889
1890 IF p_rec.default_vehicle = 'Y' THEN
1891
1892 --Checking default private Vehicle already exist or not
1893 --If no private default vehicle ,then user can allocate private vehicle
1894 --If there is already default vehicle allocated ,user cannot allocate
1895 --one more default vehicle.
1896 l_return_count := chk_defult_private_veh(
1897 p_rec =>p_rec
1898 ,p_vehicle_ownership =>l_vehicle_ownership
1899 ,p_effective_date =>p_effective_date
1900 ,p_message =>l_message );
1901 --If there is already allocation then throw error
1902 --because it should be only one entry for default
1903 IF l_return_count = -1 THEN
1904 fnd_message.set_name('PQP', 'PQP_230746_ONE_ESS_RSTRICT');
1905 fnd_message.raise_error;
1906 END IF;
1907 END IF;
1908 END IF;
1909
1910 Exception
1911 when app_exception.application_exception then
1912 IF hr_multi_message.exception_add
1913 (
1914 p_same_associated_columns => 'Y'
1915 ) then
1916 raise;
1917 END IF;
1918 pqp_val_bus.chk_ddf(p_rec);
1919 --
1920 pqp_val_bus.chk_df(p_rec);
1921 --
1922 hr_multi_message.end_validation_set;
1923 hr_utility.set_location(' Leaving:'||l_proc, 10);
1924 End update_validate;
1925 --
1926 -- ----------------------------------------------------------------------------
1927 -- |---------------------------< delete_validate >----------------------------|
1928 -- ----------------------------------------------------------------------------
1929 Procedure delete_validate
1930 (p_rec in pqp_val_shd.g_rec_type
1931 ,p_effective_date in date
1932 ,p_datetrack_mode in varchar2
1933 ,p_validation_start_date in date
1934 ,p_validation_end_date in date
1935 ) is
1936 --
1937 l_proc varchar2(72) := g_package||'delete_validate';
1938 l_return_status NUMBER ;
1939 l_message VARCHAR2(2500) ;
1940
1941 --
1942 Begin
1943 hr_utility.set_location('Entering:'||l_proc, 5);
1944 --
1945 -- Call all supporting business operations
1946 --
1947 dt_delete_validate
1948 (p_datetrack_mode => p_datetrack_mode
1949 ,p_validation_start_date => p_validation_start_date
1950 ,p_validation_end_date => p_validation_end_date
1951 ,p_vehicle_allocation_id => p_rec.vehicle_allocation_id
1952 );
1953 --
1954
1955
1956 --Checking the vehicle availability before delete or purge.
1957 IF p_datetrack_mode = 'ZAP' THEN
1958 --This is for purge
1959 l_return_status := pqp_purge_veh_alloc
1960 (p_rec =>p_rec
1961 ,p_effective_date =>p_effective_date
1962 ,p_message => l_message );
1963 hr_utility.set_location('Purge delete status:'||l_return_status,40);
1964 IF l_return_status = -1 THEN
1965 fnd_message.set_name('PQP', 'PQP_230724_DEL_ALLOC_RESTRICT');
1966 fnd_message.raise_error;
1967 END IF;
1968
1969 ELSIF p_datetrack_mode = 'DELETE' THEN
1970 --This is for enddate
1971 l_return_status := pqp_enddate_veh_alloc
1972 (p_rec =>p_rec
1973 ,p_effective_date =>p_effective_date
1974 ,p_message => l_message );
1975 hr_utility.set_location('En date delete status :'||l_return_status,45);
1976 IF l_return_status = -1 THEN
1977 fnd_message.set_name('PQP', 'PQP_230700_CANCEL_INFO');
1978 fnd_message.raise_error;
1979 END IF;
1980 END IF;
1981
1982 hr_utility.set_location(' Leaving:'||l_proc, 10);
1983 End delete_validate;
1984 --
1985 end pqp_val_bus;
[Home] [Help]