1 Package Body pqp_val_bus 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_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 /* Commented the code as a part of bug fix#7716497
1067 AND p_effective_date BETWEEN
1068 pva.effective_start_date AND pva.effective_end_date ;*/
1069
1070 l_rowcount NUMBER ;
1071 l_veh_shared VARCHAR2(20);
1072
1073 BEGIN
1074 hr_utility.set_location('Entering pqp_config_shared_veh',32);
1075 OPEN c_person_alloc_count_cursor;
1076 FETCH c_person_alloc_count_cursor INTO l_rowcount ;
1077 CLOSE c_person_alloc_count_cursor ;
1078 hr_utility.set_location('alloc count persons :'||l_rowcount,35);
1079 IF l_rowcount > 0 THEN
1080
1081 --Checking the shared vehicle flag for this repositoryId
1082 IF p_shared_vehicle = 'N' THEN
1083 p_message := 'This vehicle is not Shared at '||
1084 'repository level.so User cannot assign to '||
1085 'multiple assignments to other personIds';
1086 RETURN -1;
1087 END IF;
1088
1089 /* --Check for Configuration Values veh shared flag
1090 l_veh_shared := pqp_vre_bus.PQP_GET_CONFIG_VALUE(
1091 p_rec.business_group_id,
1092 p_legislation_code,
1093 p_seg_col_name,
1094 p_table_name,
1095 p_information_category);
1096
1097
1098 --Yes means ,user can assign to differnt persons assignments
1099 IF l_veh_shared = 'N' THEN
1100 p_message := 'This vehicle is not Shared at '||
1101 'configuration level.so User cannot assign to '||
1102 'multiple assignments to other personIds';
1103 RETURN -1;
1104 END IF;
1105 */
1106 END IF;
1107
1108 --If count is zero ,so User can allocate this veh to his assignment
1109 RETURN 0;
1110 END pqp_config_shared_veh;
1111 -- end function
1112
1113 --------------------------------------------------------------------------
1114 ------------------Check reg exist for alloc------------------------------
1115 -------------------------------------------------------------------------
1116 FUNCTION chk_reg_exist_for_alloc
1117 (p_rec IN pqp_val_shd.g_rec_type
1118 ,p_effective_date IN DATE
1119 ,p_message out NOCOPY VARCHAR2
1120 ) RETURN NUMBER IS
1121
1122 CURSOR c_alloc_count_cursor IS
1123 SELECT COUNT(pva.vehicle_allocation_id)
1124 FROM pqp_vehicle_allocations_f pva
1125 WHERE pva.assignment_id = p_rec.assignment_id
1126 AND pva.vehicle_repository_id = p_rec.vehicle_repository_id
1127 AND pva.business_group_id = p_rec.business_group_id
1128 AND (p_effective_date
1129 BETWEEN pva.effective_start_date AND pva.effective_end_date
1130 OR p_effective_date <= pva.effective_start_date)
1131 AND NVL(pva.ACROSS_ASSIGNMENTS,'N') = 'N'; -- Added as a part of bug#12937050
1132
1133 l_rowcount NUMBER;
1134
1135 BEGIN
1136 hr_utility.set_location('Entering chk_reg_exist_for_alloc', 7);
1137 OPEN c_alloc_count_cursor;
1138 FETCH c_alloc_count_cursor INTO l_rowcount;
1139 CLOSE c_alloc_count_cursor;
1140 hr_utility.set_location('Vehicle Reg Exist:'||l_rowcount, 10);
1141 IF l_rowcount > 0 THEN
1142 p_message := 'Registration number is already exist in this date tracks';
1143 RETURN -1 ;
1144 END IF;
1145 RETURN 0;
1146 END chk_reg_exist_for_alloc;
1147 -- end function
1148 -----------------------------------------------------------------------------
1149 ----------------------End date delete----------------------------------------
1150 -----------------------------------------------------------------------------
1151 --
1152 --There are no pending claims that spans across this date
1153 --
1154 FUNCTION pqp_enddate_veh_alloc
1155 (p_rec IN pqp_val_shd.g_rec_type
1156 ,p_effective_date IN DATE
1157 ,p_message OUT NOCOPY VARCHAR2
1158 ) RETURN NUMBER IS
1159 --Get the claim count for future and current date tracks
1160 CURSOR c_claim_count_cursor(cp_registration_number VARCHAR2,
1161 cp_assignment_id NUMBER ) IS
1162 SELECT count(*)
1163 FROM pay_element_types_f pet
1164 ,pay_element_type_extra_info pete
1165 ,pay_element_entries_f pee
1166 ,pay_element_entry_values_f peev2
1167 ,pay_input_values_f piv2
1168 WHERE pete.EEI_INFORMATION_CATEGORY='PQP_VEHICLE_MILEAGE_INFO'
1169 AND pet.business_group_id=p_rec.business_group_id
1170 AND pete.element_type_id =pet.element_type_id
1171 AND substr(pete.EEI_INFORMATION1,0,1) in ('C','P')
1172 AND pee.assignment_id =cp_assignment_id
1173 AND peev2.element_entry_id=pee.element_entry_id
1174 AND piv2.element_type_id=pet.element_type_id
1175 AND piv2.name in ('Vehicle Reg Number')
1176 AND piv2.input_value_id=peev2.input_value_id
1177 AND peev2.SCREEN_ENTRY_VALUE =cp_registration_number
1178 AND p_effective_date < pee.effective_end_date;
1179
1180
1181 CURSOR c_clm_veh_det_cursor IS
1182 SELECT pvr.registration_number, pva.assignment_id
1183 FROM pqp_vehicle_allocations_f pva,
1184 pqp_vehicle_repository_f pvr
1185 WHERE pva.vehicle_allocation_id= p_rec.vehicle_allocation_id
1186 AND pva.vehicle_repository_id =pvr.vehicle_repository_id
1187 AND p_effective_date BETWEEN pva.effective_start_date
1188 AND pva.effective_end_date
1189 AND p_effective_date BETWEEN pvr.effective_start_date
1190 AND pvr.effective_end_date ;
1191
1192 l_alloc_count NUMBER ;
1193 l_registration_number pqp_vehicle_repository_f.registration_number%TYPE;
1194 l_assignment_id pqp_vehicle_allocations_f.assignment_id%TYPE;
1195
1196 BEGIN
1197 OPEN c_clm_veh_det_cursor;
1198 FETCH c_clm_veh_det_cursor INTO l_registration_number,l_assignment_id;
1199 CLOSE c_clm_veh_det_cursor;
1200
1201 OPEN c_claim_count_cursor(l_registration_number,l_assignment_id);
1202 FETCH c_claim_count_cursor INTO l_alloc_count;
1203 CLOSE c_claim_count_cursor;
1204 --Check claims existence check
1205 IF l_alloc_count > 0 THEN
1206 p_message := 'There are pending cliams in future,so we cannot enddated';
1207 RETURN -1 ;
1208 END IF;
1209 RETURN 0;
1210 END pqp_enddate_veh_alloc;
1211 -- end function
1212
1213 -----------------------------------------------------------------------------
1214 ---------------------Check the DefaultVehicle Allocation---------------------
1215 -----------------------------------------------------------------------------
1216 --
1217 --Default Vehicle:Checking only one default private vehicle is allocated at
1218 --any point of time to that assignment
1219 --
1220 FUNCTION chk_defult_private_veh
1221 ( p_rec IN pqp_val_shd.g_rec_type
1222 ,p_vehicle_ownership IN VARCHAR2
1223 ,p_effective_date IN DATE
1224 ,p_message OUT NOCOPY VARCHAR2
1225 ) RETURN NUMBER IS
1226
1227
1228 --Used to get the allocations count for private vehicle default count
1229 CURSOR c_alloc_count_cursor IS
1230 SELECT COUNT(pva.vehicle_allocation_id)
1231 FROM pqp_vehicle_repository_f pvr
1232 ,pqp_vehicle_allocations_f pva
1233 WHERE pvr.vehicle_repository_id = pva.vehicle_repository_id
1234 AND pvr.business_group_id = pva.business_group_id
1235 AND pvr.vehicle_ownership = p_vehicle_ownership
1236 AND pva.default_vehicle = 'Y'
1237 AND pva.assignment_id = p_rec.assignment_id
1238 AND pva.business_group_id=p_rec.business_group_id
1239 AND (p_effective_date between
1240 pva.effective_start_date and pva.effective_end_date
1241 OR p_effective_date <= pva.effective_start_date)
1242 AND (p_effective_date between
1243 pvr.effective_start_date and pvr.effective_end_date);
1244
1245 l_rowcount NUMBER ;
1246
1247 BEGIN
1248 hr_utility.set_location('Entering pqp_check_veh_alloc_process', 16);
1249 OPEN c_alloc_count_cursor;
1250 FETCH c_alloc_count_cursor INTO l_rowcount;
1251 CLOSE c_alloc_count_cursor ;
1252
1253 IF l_rowcount > 0 THEN
1254 --This is max default vehicle ,so user canot allocate
1255 hr_utility.set_location('Count for default:'||l_rowcount, 20);
1256 p_message :='Only one Default vehicle allocated to the user';
1257 RETURN -1;
1258 END IF;
1259 RETURN 0;
1260 END chk_defult_private_veh;
1261 -- end function
1262
1263 -----------------------------------------------------------------------------
1264 --Delete process for NI car Primary element entry
1265 ----------------------------------------------------------------------------
1266
1267 Procedure del_ni_car_entry (p_business_group_id IN NUMBER
1268 ,p_assignment_id IN NUMBER
1269 ,p_allocation_id IN NUMBER
1270 ,p_effective_date IN DATE
1271 )
1272 is
1273 CURSOR c_chk_ni_car_pri
1274 IS
1275 SELECT pee.element_entry_id element_entry_id
1276 FROM pay_element_entries_f PEE
1277 ,pay_element_links_f pel
1278 ,pay_element_types_f pet
1279 WHERE pee.assignment_id=p_assignment_id
1280 AND pel.business_group_id=p_business_group_id
1281 and p_effective_date BETWEEN pee.effective_start_date
1282 AND pee.effective_end_date
1283 and p_effective_date BETWEEN pel.effective_start_date
1284 AND pel.effective_end_date
1285 and p_effective_date BETWEEN pet.effective_start_date
1286 AND pet.effective_end_date
1287 and pee.element_link_id=pel.element_link_id
1288 AND pel.element_type_id=pet.element_type_id
1289 AND pet.element_name = 'NI Car Primary';
1290
1291 CURSOR c_chk_sec_car (cp_allocation_id IN NUMBER)
1292 is
1293 SELECT pee.element_entry_id element_entry_id
1294 FROM pay_element_entries_f PEE
1295 ,pay_element_links_f pel
1296 ,pay_element_types_f pet
1297 ,pay_input_values_f piv
1298 , pay_element_entry_values_f peev
1299 , pqp_vehicle_allocations_f pva
1300 ,pqp_vehicle_repository_f pvr
1301 WHERE pee.ASSIGNMENT_ID=p_assignment_id
1302 AND pel.business_group_id=p_business_group_id
1303 AND pee.element_link_id=pel.element_link_id
1304 AND pel.element_type_id=pet.element_type_id
1305 AND pet.element_name = 'NI Car Secondary'
1306 AND piv.element_type_id = pet.element_type_id
1307 AND piv.name = 'Registration Number'
1308 AND peev.input_value_id=piv.input_value_id
1309 AND peev.element_entry_id=pee.element_entry_id
1310 AND pva.vehicle_allocation_id=cp_allocation_id
1311 AND pva.vehicle_repository_id=pvr.VEHICLE_REPOSITORY_ID
1312 AND peev.screen_entry_value=pvr.registration_number
1313 AND pel.business_group_id=piv.business_group_id
1314 AND piv.business_group_id=pva.business_group_id
1315 AND piv.business_group_id=pvr.business_group_id
1316 AND pet.legislation_code='GB'
1317 AND p_effective_date BETWEEN pee.effective_start_date
1318 AND pee.effective_end_date
1319 AND p_effective_date BETWEEN pel.effective_start_date
1320 AND pel.effective_end_date
1321 AND p_effective_date BETWEEN pet.effective_start_date
1322 AND pet.effective_end_date
1323 AND p_effective_date BETWEEN piv.effective_start_date
1324 AND piv.effective_end_date
1325 AND p_effective_date BETWEEN peev.effective_start_date
1326 AND peev.effective_end_date
1327 AND p_effective_date BETWEEN pva.effective_start_date
1328 AND pva.effective_end_date
1329 AND p_effective_date BETWEEN pvr.effective_start_date
1330 AND pvr.effective_end_date;
1331 l_chk_ni_car_pri c_chk_ni_car_pri%ROWTYPE;
1332 l_chk_sec_car c_chk_sec_car%ROWTYPE;
1333
1334 Begin
1335
1336 OPEN c_chk_ni_car_pri;
1337 FETCH c_chk_ni_car_pri INTO l_chk_ni_car_pri;
1338 CLOSE c_chk_ni_car_pri;
1339
1340 IF l_chk_ni_car_pri.element_entry_id IS NOT NULL THEN
1341 hr_entry_api.delete_element_entry
1342 (
1343 p_dt_delete_mode => 'DELETE',
1344 p_session_date => p_effective_date,
1345 p_element_entry_id => l_chk_ni_car_pri.element_entry_id
1346 );
1347 END IF;
1348
1349 OPEN c_chk_sec_car (p_allocation_id);
1350 FETCH c_chk_sec_car INTO l_chk_sec_car;
1351 CLOSE c_chk_sec_car;
1352
1353 IF l_chk_sec_car.element_entry_id IS NOT NULL THEN
1354 hr_entry_api.delete_element_entry
1355 (
1356 p_dt_delete_mode => 'DELETE',
1357 p_session_date => p_effective_date,
1358 p_element_entry_id => l_chk_sec_car.element_entry_id
1359 );
1360 END IF;
1361
1362 EXCEPTION
1363 --------
1364 WHEN OTHERS THEN
1365 NULL;
1366
1367 End;
1368
1369 -- ----------------------------------------------------------------------------
1370 -- |---------------------------< insert_validate >----------------------------|
1371 -- ----------------------------------------------------------------------------
1372 Procedure insert_validate
1373 (p_rec in pqp_val_shd.g_rec_type
1374 ,p_effective_date in date
1375 ,p_datetrack_mode in varchar2
1376 ,p_validation_start_date in date
1377 ,p_validation_end_date in date
1378 ) is
1379
1380 --Used to get the ownership for repId at once
1381 CURSOR c_veh_det_cursor IS
1382 SELECT pvr.vehicle_ownership
1383 ,pvr.vehicle_status
1384 ,pvr.shared_vehicle
1385 ,pvr.initial_registration
1386 ,pvr.registration_number
1387 FROM pqp_vehicle_repository_f pvr
1388 WHERE pvr.vehicle_repository_id = p_rec.vehicle_repository_id
1389 AND pvr.business_group_id= p_rec.business_group_id
1390 AND p_effective_date BETWEEN
1391 pvr.effective_start_date AND
1392 pvr.effective_end_date;
1393 --
1394 l_proc varchar2(72) := g_package||'insert_validate';
1395 l_return_status NUMBER ;
1396 l_return_count NUMBER;
1397 l_number_value NUMBER;
1398 l_cmy_veh_alloc_count NUMBER;
1399 l_pri_veh_alloc_count NUMBER;
1400 l_message VARCHAR2(2500) ;
1401 l_max_conf_count VARCHAR2(10);
1402 l_vehicle_ownership pqp_vehicle_repository_f.vehicle_ownership%type;
1403 l_legislation_code varchar2(150);
1404 l_vehicle_status pqp_vehicle_repository_f.vehicle_status%type;
1405 l_shared_vehicle pqp_vehicle_repository_f.shared_vehicle%type;
1406 l_initial_registration pqp_vehicle_repository_f.initial_registration%type;
1407 l_registration_number pqp_vehicle_repository_f.registration_number%type;
1408 --
1409
1410 Begin
1411 hr_utility.set_location('Entering:'||l_proc, 5);
1412 --
1413 -- Call all supporting business operations
1414 --
1415 hr_api.validate_bus_grp_id
1416 (p_business_group_id => p_rec.business_group_id
1417 ,p_associated_column1 => pqp_val_shd.g_tab_nam
1418 || '.BUSINESS_GROUP_ID');
1419
1420 --Used to get the ownership for repId at once
1421 OPEN c_veh_det_cursor;
1422 FETCH c_veh_det_cursor INTO l_vehicle_ownership,
1423 l_vehicle_status,l_shared_vehicle,
1424 l_initial_registration,l_registration_number;
1425 CLOSE c_veh_det_cursor;
1426
1427
1428 --Checking the vehicle status ,if it Inactive then
1429 --vehicle cannot be allocate
1430 IF l_vehicle_status = 'I' THEN
1431 fnd_message.set_name('PQP', 'PQP_230925_INACTIVE_VEH_ASSIGN');
1432 fnd_message.raise_error;
1433 END IF;
1434
1435 --Fixing the bug #2864591
1436 --Checking the allocation effective start date is
1437 --greter than or equal to veh reg date
1438 IF l_initial_registration IS NOT NULL THEN
1439 IF l_initial_registration > p_effective_date THEN
1440 fnd_message.set_name('PQP', 'PQP_230926_REG_DATE_ASSIGN_ERR');
1441 fnd_message.set_token('TOKEN',l_registration_number);
1442 fnd_message.raise_error;
1443 END IF;
1444 END IF;
1445
1446
1447 --Checking Usage Type Mandatory
1448 IF p_rec.usage_type='P' OR p_rec.usage_type='S' THEN
1449 l_return_status := chk_mandatory(
1450 p_argument =>'Usage Type'
1451 ,p_argument_value => p_rec.usage_type
1452 ,p_message => l_message);
1453
1454 IF l_return_status = -1 THEN
1455 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1456 fnd_message.set_token('FEILD','Usage Type');
1457 fnd_message.raise_error;
1458 END IF;
1459 END IF;
1460
1461 --Checking usage Type lookup validation
1462 --Usage type lookup type will vary based on ownership
1463 --IF l_vehicle_ownership = 'C' THEN
1464 IF l_vehicle_ownership in ('C','PL_LEC','PL_LC') THEN
1465 l_return_status := chk_lookup(
1466 p_vehicle_allocation_id => p_rec.vehicle_allocation_id
1467 ,p_lookup_type =>'PQP_COMPANY_VEHICLE_USER'
1468 ,p_lookup_code => p_rec.usage_type
1469 ,p_effective_date => p_effective_date
1470 ,p_validation_start_date => p_validation_start_date
1471 ,p_validation_end_date => p_validation_end_date);
1472
1473 IF l_return_status = -1 THEN
1474 fnd_message.set_name('PQP','PQP_230722_VLD_USG_OWNRSHP');
1475 fnd_message.raise_error;
1476 END IF;
1477 ELSE
1478 /*
1479 l_return_status := chk_lookup(
1480 p_vehicle_allocation_id => p_rec.vehicle_allocation_id
1481 ,p_lookup_type =>'PQP_PRIVATE_VEHICLE_USER'
1482 ,p_lookup_code => p_rec.usage_type
1483 ,p_effective_date => p_effective_date
1484 ,p_validation_start_date => p_validation_start_date
1485 ,p_validation_end_date => p_validation_end_date);
1486
1487 IF l_return_status = -1 THEN
1488 fnd_message.set_name('PQP','PQP_230722_VLD_USG_OWNRSHP');
1489 fnd_message.raise_error;
1490 END IF;*/
1491 NULL;
1492 END IF;
1493
1494 --Checking calculation method lookup validation
1495 l_return_status := chk_lookup(
1496 p_vehicle_allocation_id => p_rec.vehicle_allocation_id
1497 ,p_lookup_type =>'PQP_VEHICLE_CALC_METHOD'
1498 ,p_lookup_code => p_rec.calculation_method
1499 ,p_effective_date => p_effective_date
1500 ,p_validation_start_date => p_validation_start_date
1501 ,p_validation_end_date => p_validation_end_date);
1502
1503 IF l_return_status = -1 THEN
1504 fnd_message.set_name('PQP','PQP_230824_VALID_CALC_METHOD');
1505 fnd_message.raise_error;
1506 END IF;
1507
1508 --Checking the regnumber exist
1509 l_return_status := chk_reg_exist_for_alloc(
1510 p_rec => p_rec
1511 ,p_effective_date => p_effective_date
1512 ,p_message => l_message );
1513
1514 IF l_return_status = -1 THEN
1515 fnd_message.set_name('PQP', 'PQP_230759_ALLOC_REG_EXIST');
1516 fnd_message.raise_error;
1517 END IF;
1518
1519
1520 --Checking the fuelcard/fuelNumber/fuelbenifit value
1521 --for cmy vehicle allocation
1522 IF l_vehicle_ownership in ('C','PL_LEC','PL_LC') THEN
1523
1524 l_return_status := pqp_check_cmyveh_fuel_card (
1525 p_rec => p_rec
1526 ,p_vehicle_ownership => l_vehicle_ownership
1527 ,p_effective_date => p_effective_date
1528 ,p_message => l_message );
1529
1530 IF l_return_status = -1 THEN
1531 fnd_message.set_name('PQP', 'PQP_230738_COMP_OWNR_MNDTRY');
1532 fnd_message.set_token('TOKEN','Fuel Card');
1533 fnd_message.raise_error;
1534 END IF;
1535 END IF;
1536
1537 --Getting the legislationId for business groupId
1538 l_legislation_code :=
1539 pqp_vre_bus.get_legislation_code(p_rec.business_group_id);
1540
1541
1542 --Checking the Primary vehicle allocation
1543 --If usage type is 'PRIMARY' then check is there any P vehicle
1544 --for this assignment
1545 IF p_rec.usage_type = 'P' THEN
1546 l_return_count := pqp_check_veh_alloc_process(
1547 p_rec =>p_rec
1548 ,p_vehicle_ownership =>l_vehicle_ownership
1549 ,p_effective_date =>p_effective_date
1550 ,p_message => l_message );
1551
1552 -- If there is already allocation then throw error
1553 -- because it should be only one entry for P/E
1554 IF l_return_count = -1 THEN
1555 fnd_message.set_name('PQP', 'PQP_230708_PMRY_RESTRICT');
1556 fnd_message.raise_error;
1557 END IF;
1558 END IF;
1559
1560
1561
1562 --If company vehcicle then check max limit is reached or not.
1563 IF l_vehicle_ownership in ('C','PL_LEC','PL_LC') THEN
1564 --call max allocations count for assignment for this ownership
1565 l_cmy_veh_alloc_count := pqp_get_max_allowed_veh(
1566 p_rec =>p_rec
1567 ,p_vehicle_ownership =>l_vehicle_ownership
1568 ,p_effective_date =>p_effective_date );
1569
1570
1571
1572 --call configuration max allowed cmy vehicles
1573 l_max_conf_count := pqp_vre_bus.PQP_GET_CONFIG_VALUE(
1574 p_business_group_id => p_rec.business_group_id,
1575 p_legislation_code => l_legislation_code,
1576 p_seg_col_name =>'MaxCmyVehAllow',
1577 p_table_name =>'p_table_name',
1578 p_information_category =>'PQP_VEHICLE_MILEAGE');
1579 hr_utility.set_location('Count conf maximum count:'||l_max_conf_count,23);
1580 --if null then no limit
1581 IF l_max_conf_count IS NOT NULL THEN
1582 Begin
1583 l_number_value := fnd_number.CANONICAL_TO_NUMBER(l_max_conf_count);
1584 End;
1585 IF l_cmy_veh_alloc_count >= l_number_value THEN
1586 l_message := 'person already reached the maximum limit';
1587 fnd_message.set_name('PQP', 'PQP_230709_MAX_COMP_ALLOC');
1588 fnd_message.raise_error;
1589 END IF;
1590 END IF;
1591
1592 --Checking the Share Company Car validation
1593 l_return_count := pqp_config_shared_veh(
1594 p_rec => p_rec,
1595 p_vehicle_ownership => l_vehicle_ownership,
1596 p_shared_vehicle => l_shared_vehicle,
1597 p_effective_date => p_effective_date,
1598 p_legislation_code => l_legislation_code,
1599 p_seg_col_name =>'ShareCmyCar',
1600 p_table_name =>'p_table_name',
1601 p_information_category =>'PQP_VEHICLE_MILEAGE',
1602 p_message => l_message );
1603
1604 IF l_return_count = -1 THEN
1605 fnd_message.set_name('PQP', 'PQP_230707_VEH_ALLOC_INFO');
1606 fnd_message.raise_error;
1607 END IF;
1608 END IF;
1609
1610
1611
1612 --If Private vehcicle then check max limit is reached or not.
1613 IF l_vehicle_ownership in ('P','PL_PC') THEN
1614
1615 --Checking default private Vehicle already exist or not
1616 --If no private default vehicle ,then user can allocate private vehicle
1617 --If there is already default vehicle allocated ,user cannot allocate
1618 --one more default vehicle.
1619
1620 IF p_rec.default_vehicle = 'Y' THEN
1621 l_return_count := chk_defult_private_veh(
1622 p_rec =>p_rec
1623 ,p_vehicle_ownership =>l_vehicle_ownership
1624 ,p_effective_date =>p_effective_date
1625 ,p_message =>l_message );
1626 -- If there is already allocation then throw error
1627 -- because it should be only one entry for default
1628 IF l_return_count = -1 THEN
1629 fnd_message.set_name('PQP', 'PQP_230746_ONE_ESS_RSTRICT');
1630 fnd_message.raise_error;
1631 END IF;
1632 END IF;
1633
1634
1635 --call max allocations count for assignment for this ownership
1636 l_pri_veh_alloc_count := pqp_get_max_allowed_veh(
1637 p_rec =>p_rec
1638 ,p_vehicle_ownership =>l_vehicle_ownership
1639 ,p_effective_date =>p_effective_date );
1640
1641 --call configuration max allowed Pri vehicles
1642 l_max_conf_count := pqp_vre_bus.PQP_GET_CONFIG_VALUE(
1643 p_business_group_id => p_rec.business_group_id ,
1644 p_legislation_code => l_legislation_code,
1645 p_seg_col_name =>'MaxPriVehAllow',
1646 p_table_name =>'p_table_name',
1647 p_information_category =>'PQP_VEHICLE_MILEAGE');
1648
1649 --if null then no limit
1650 IF l_max_conf_count IS NOT NULL THEN
1651 Begin
1652 l_number_value := fnd_number.CANONICAL_TO_NUMBER(l_max_conf_count);
1653 End;
1654
1655 IF l_pri_veh_alloc_count >= l_number_value THEN
1656 l_message := 'person already reached the maximum limit';
1657 fnd_message.set_name('PQP', 'PQP_230710_MAX_PVT_ALLOC');
1658 fnd_message.raise_error;
1659 END IF;
1660 END IF;
1661 -- cheking for share Private car
1662 l_return_count := pqp_config_shared_veh(
1663 p_rec => p_rec,
1664 p_vehicle_ownership => l_vehicle_ownership,
1665 p_shared_vehicle => l_shared_vehicle,
1666 p_effective_date => p_effective_date,
1667 p_legislation_code => l_legislation_code,
1668 p_seg_col_name =>'SharePriCar',
1669 p_table_name =>'p_table_name',
1670 p_information_category =>'PQP_VEHICLE_MILEAGE',
1671 p_message => l_message );
1672
1673 IF l_return_count = -1 THEN
1674 fnd_message.set_name('PQP', 'PQP_230707_VEH_ALLOC_INFO');
1675 fnd_message.raise_error;
1676 END IF;
1677 END IF;
1678 Exception
1679 when app_exception.application_exception then
1680 IF hr_multi_message.exception_add
1681 (
1682 p_same_associated_columns => 'Y'
1683 ) then
1684 raise;
1685 END IF;
1686
1687
1688 hr_multi_message.end_validation_set;
1689 --
1690 -- Validate Dependent Attributes
1691 --
1692 --
1693 pqp_val_bus.chk_ddf(p_rec);
1694 --
1695 pqp_val_bus.chk_df(p_rec);
1696 --
1697 hr_utility.set_location(' Leaving:'||l_proc, 10);
1698 End insert_validate;
1699 --
1700 -- ----------------------------------------------------------------------------
1701 -- |---------------------------< update_validate >----------------------------|
1702 -- ----------------------------------------------------------------------------
1703 Procedure update_validate
1704 (p_rec in pqp_val_shd.g_rec_type
1705 ,p_effective_date in date
1706 ,p_datetrack_mode in varchar2
1707 ,p_validation_start_date in date
1708 ,p_validation_end_date in date
1709 ) is
1710
1711 --Used to get the ownership for repId at once
1712 CURSOR c_veh_det_cursor IS
1713 SELECT pvr.vehicle_ownership
1714 ,pvr.vehicle_status
1715 ,pvr.initial_registration
1716 ,pvr.registration_number
1717 FROM pqp_vehicle_repository_f pvr
1718 WHERE pvr.vehicle_repository_id = p_rec.vehicle_repository_id
1719 AND pvr.business_group_id= p_rec.business_group_id
1720 AND p_effective_date BETWEEN
1721 pvr.effective_start_date
1722 AND pvr.effective_end_date;
1723
1724 --
1725 l_proc varchar2(72) := g_package||'update_validate';
1726 l_validation_start_date date;
1727 l_validation_end_date date;
1728 l_return_status NUMBER ;
1729 l_vehicle_status pqp_vehicle_repository_f.vehicle_status%type;
1730 l_return_count NUMBER;
1731 l_message VARCHAR2(2500) ;
1732 l_vehicle_ownership pqp_vehicle_repository_f.vehicle_ownership%TYPE;
1733 l_initial_registration pqp_vehicle_repository_f.initial_registration%type;
1734 l_registration_number pqp_vehicle_repository_f.registration_number%type;
1735
1736
1737 --
1738 Begin
1739 hr_utility.set_location('Entering:'||l_proc, 5);
1740 --
1741 -- Call all supporting business operations
1742 --
1743 hr_api.validate_bus_grp_id
1744 (p_business_group_id => p_rec.business_group_id
1745 ,p_associated_column1 => pqp_val_shd.g_tab_nam
1746 || '.BUSINESS_GROUP_ID');
1747 --
1748 -- After validating the set of important attributes,
1749 -- if Multiple Message detection is enabled and at least
1750 -- one error has been found then abort further validation.
1751 --
1752
1753 --
1754 -- Validate Dependent Attributes
1755 --
1756 -- Call the datetrack update integrity operation
1757 --
1758 dt_update_validate
1759 (p_datetrack_mode => p_datetrack_mode
1760 ,p_validation_start_date => p_validation_start_date
1761 ,p_validation_end_date => p_validation_end_date
1762 );
1763 --
1764 chk_non_updateable_args
1765 (p_effective_date => p_effective_date
1766 ,p_rec => p_rec
1767 );
1768 --
1769 chk_vehicle_exst ( p_rec =>p_rec
1770 ,p_effective_date =>p_effective_date
1771 ,p_datetrack_mode =>p_datetrack_mode
1772 ,p_validation_start_date =>p_validation_start_date
1773 ,p_validation_end_date =>p_validation_end_date
1774 );
1775
1776 chk_asg_exst ( p_rec =>p_rec
1777 ,p_effective_date =>p_effective_date
1778 ,p_datetrack_mode =>p_datetrack_mode
1779 ,p_validation_start_date =>p_validation_start_date
1780 ,p_validation_end_date =>p_validation_end_date
1781 );
1782
1783
1784 OPEN c_veh_det_cursor;
1785 FETCH c_veh_det_cursor INTO l_vehicle_ownership,l_vehicle_status,
1786 l_initial_registration,l_registration_number;
1787 CLOSE c_veh_det_cursor;
1788
1789 --Used to get the ownership for repId at once
1790 IF pqp_val_shd.g_old_rec.vehicle_repository_id <>
1791 p_rec.vehicle_repository_id THEN
1792
1793 IF l_vehicle_status = 'I' THEN
1794 l_message := 'Vehicle status is Inactive,so vehicle cannot update';
1795 fnd_message.set_name('PQP', 'PQP_230925_INACTIVE_VEH_ASSIGN');
1796 fnd_message.raise_error;
1797 END IF;
1798 END IF;
1799
1800 --Fixing the bug #2864591
1801 --Checking the allocation effective start date is
1802 --greter than or equal to veh reg date
1803 IF l_initial_registration IS NOT NULL THEN
1804 IF l_initial_registration > p_effective_date THEN
1805 fnd_message.set_name('PQP', 'PQP_230926_REG_DATE_ASSIGN_ERR');
1806 fnd_message.set_token('TOKEN',l_registration_number);
1807 fnd_message.raise_error;
1808 END IF;
1809 END IF;
1810
1811 --Checking for value change
1812 IF ( nvl(pqp_val_shd.g_old_rec.usage_type,hr_api.g_varchar2)
1813 <> nvl(p_rec.usage_type,hr_api.g_varchar2) ) THEN
1814
1815 --Checking usage Type lookup validation
1816 --Usage type lookup type will vary based on ownership
1817 IF l_vehicle_ownership in ('C','PL_LEC','PL_LC') THEN
1818 --If not equal then Checking usage_type lookup validation
1819 l_return_status := chk_lookup(
1820 p_vehicle_allocation_id => p_rec.vehicle_allocation_id
1821 ,p_lookup_type =>'PQP_COMPANY_VEHICLE_USER'
1822 ,p_lookup_code => p_rec.usage_type
1823 ,p_effective_date => p_effective_date
1824 ,p_validation_start_date => p_validation_start_date
1825 ,p_validation_end_date => p_validation_end_date);
1826 IF l_return_status = -1 THEN
1827 fnd_message.set_name('PQP','PQP_230722_VLD_USG_OWNRSHP');
1828 fnd_message.raise_error;
1829 END IF;
1830 ELSE
1831 /*
1832 l_return_status := chk_lookup(
1833 p_vehicle_allocation_id => p_rec.vehicle_allocation_id
1834 ,p_lookup_type =>'PQP_PRIVATE_VEHICLE_USER'
1835 ,p_lookup_code => p_rec.usage_type
1836 ,p_effective_date => p_effective_date
1837 ,p_validation_start_date => p_validation_start_date
1838 ,p_validation_end_date => p_validation_end_date);
1839 IF l_return_status = -1 THEN
1840 fnd_message.set_name('PQP','PQP_230722_VLD_USG_OWNRSHP');
1841 fnd_message.raise_error;
1842 END IF;*/
1843 NULL;
1844 END IF;
1845 END IF;
1846
1847 --Checking for value change
1848 IF ( nvl(pqp_val_shd.g_old_rec.calculation_method,hr_api.g_varchar2)
1849 <> nvl(p_rec.calculation_method,hr_api.g_varchar2) ) THEN
1850 --Checking calculation method lookup validation
1851 l_return_status := chk_lookup(
1852 p_vehicle_allocation_id => p_rec.vehicle_allocation_id
1853 ,p_lookup_type =>'PQP_VEHICLE_CALC_METHOD'
1854 ,p_lookup_code => p_rec.calculation_method
1855 ,p_effective_date => p_effective_date
1856 ,p_validation_start_date => p_validation_start_date
1857 ,p_validation_end_date => p_validation_end_date);
1858
1859 IF l_return_status = -1 THEN
1860 fnd_message.set_name('PQP','PQP_230824_VALID_CALC_METHOD');
1861 fnd_message.raise_error;
1862 END IF;
1863 END IF;
1864
1865
1866 --checking only if there is change in UsageType for update
1867 IF pqp_val_shd.g_old_rec.usage_type <> p_rec.usage_type THEN
1868
1869 --If usage type is 'PRIMARY' then check is there any
1870 --Private vehicles for this assignment
1871 IF p_rec.usage_type = 'P' THEN
1872 l_return_count := pqp_check_veh_alloc_process(
1873 p_rec =>p_rec
1874 ,p_vehicle_ownership =>l_vehicle_ownership
1875 ,p_effective_date =>p_effective_date
1876 ,p_message =>l_message );
1877
1878 -- If there is already allocation then throw error,because it
1879 --should be only one entry for P
1880 IF l_return_count = -1 THEN
1881 fnd_message.set_name('PQP', 'PQP_230708_PMRY_RESTRICT');
1882 fnd_message.raise_error;
1883 END IF;
1884 END IF;
1885 END IF;
1886
1887
1888
1889
1890 IF pqp_val_shd.g_old_rec.default_vehicle = 'N' THEN
1891
1892 IF p_rec.default_vehicle = 'Y' THEN
1893
1894 --Checking default private Vehicle already exist or not
1895 --If no private default vehicle ,then user can allocate private vehicle
1896 --If there is already default vehicle allocated ,user cannot allocate
1897 --one more default vehicle.
1898 l_return_count := chk_defult_private_veh(
1899 p_rec =>p_rec
1900 ,p_vehicle_ownership =>l_vehicle_ownership
1901 ,p_effective_date =>p_effective_date
1902 ,p_message =>l_message );
1903 --If there is already allocation then throw error
1904 --because it should be only one entry for default
1905 IF l_return_count = -1 THEN
1906 fnd_message.set_name('PQP', 'PQP_230746_ONE_ESS_RSTRICT');
1907 fnd_message.raise_error;
1908 END IF;
1909 END IF;
1910 END IF;
1911
1912 Exception
1913 when app_exception.application_exception then
1914 IF hr_multi_message.exception_add
1915 (
1916 p_same_associated_columns => 'Y'
1917 ) then
1918 raise;
1919 END IF;
1920 pqp_val_bus.chk_ddf(p_rec);
1921 --
1922 pqp_val_bus.chk_df(p_rec);
1923 --
1924 hr_multi_message.end_validation_set;
1925 hr_utility.set_location(' Leaving:'||l_proc, 10);
1926 End update_validate;
1927 --
1928 -- ----------------------------------------------------------------------------
1929 -- |---------------------------< delete_validate >----------------------------|
1930 -- ----------------------------------------------------------------------------
1931 Procedure delete_validate
1932 (p_rec in pqp_val_shd.g_rec_type
1933 ,p_effective_date in date
1934 ,p_datetrack_mode in varchar2
1935 ,p_validation_start_date in date
1936 ,p_validation_end_date in date
1937 ) is
1938 --
1939 l_proc varchar2(72) := g_package||'delete_validate';
1940 l_return_status NUMBER ;
1941 l_message VARCHAR2(2500) ;
1942
1943 --
1944 Begin
1945 hr_utility.set_location('Entering:'||l_proc, 5);
1946 --
1947 -- Call all supporting business operations
1948 --
1949 dt_delete_validate
1950 (p_datetrack_mode => p_datetrack_mode
1951 ,p_validation_start_date => p_validation_start_date
1952 ,p_validation_end_date => p_validation_end_date
1953 ,p_vehicle_allocation_id => p_rec.vehicle_allocation_id
1954 );
1955 --
1956
1957
1958 --Checking the vehicle availability before delete or purge.
1959 IF p_datetrack_mode = 'ZAP' THEN
1960 --This is for purge
1961 l_return_status := pqp_purge_veh_alloc
1962 (p_rec =>p_rec
1963 ,p_effective_date =>p_effective_date
1964 ,p_message => l_message );
1965 hr_utility.set_location('Purge delete status:'||l_return_status,40);
1966 IF l_return_status = -1 THEN
1967 fnd_message.set_name('PQP', 'PQP_230724_DEL_ALLOC_RESTRICT');
1968 fnd_message.raise_error;
1969 END IF;
1970
1971 ELSIF p_datetrack_mode = 'DELETE' THEN
1972 --This is for enddate
1973 l_return_status := pqp_enddate_veh_alloc
1974 (p_rec =>p_rec
1975 ,p_effective_date =>p_effective_date
1976 ,p_message => l_message );
1977 hr_utility.set_location('En date delete status :'||l_return_status,45);
1978 IF l_return_status = -1 THEN
1979 fnd_message.set_name('PQP', 'PQP_230700_CANCEL_INFO');
1980 fnd_message.raise_error;
1981 END IF;
1982 END IF;
1983
1984 hr_utility.set_location(' Leaving:'||l_proc, 10);
1985 End delete_validate;
1986 --
1987 end pqp_val_bus;
[Home] [Help]