1 Package Body pqp_vre_bus as
2 /* $Header: pqvrerhi.pkb 120.0.12010000.2 2008/08/08 07:23:09 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqp_vre_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_repository_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 PROCEDURE set_security_group_id
21 (p_vehicle_repository_id IN NUMBER
22 ,p_associated_column1 IN VARCHAR2
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_repository_f vre
32 WHERE vre.vehicle_repository_id = p_vehicle_repository_id
33 AND pbg.business_group_id = vre.business_group_id;
34
35 --
36 -- Declare local variables
37 --
38 l_security_group_id NUMBER;
39 l_proc VARCHAR2(72) := g_package||'set_security_group_id';
40 l_legislation_code VARCHAR2(150);
41 --
42 BEGIN
43 --
44 hr_utility.set_location('Entering:'|| l_proc, 10);
45 --
46 -- Ensure that all the mandatory parameter are not null
47 --
48 hr_api.mandatory_arg_error
49 (p_api_name => l_proc
50 ,p_argument =>'vehicle_repository_id'
51 ,p_argument_value => p_vehicle_repository_id
52 );
53 --
54 OPEN csr_sec_grp;
55 FETCH csr_sec_grp INTO l_security_group_id,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_REPOSITORY_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 -- used to get legislation code for Vehicle repository Id
91 --
92 FUNCTION return_legislation_code
93 (
94 p_vehicle_repository_id IN NUMBER
95 ) RETURN VARCHAR2 IS
96 --
97 -- Declare cursor
98 --
99 CURSOR csr_leg_code IS
100 SELECT pbg.legislation_code
101 FROM per_business_groups_perf pbg
102 ,pqp_vehicle_repository_f vre
103 WHERE vre.vehicle_repository_id = p_vehicle_repository_id
104 AND pbg.business_group_id = vre.business_group_id;
105 --
106 -- Declare local variables
107 --
108 l_legislation_code varchar2(150);
109 l_proc varchar2(72) := g_package||'return_legislation_code';
110 --
111 Begin
112 --
113 hr_utility.set_location('Entering:'|| l_proc, 10);
114 --
115 -- Ensure that all the mandatory parameter are not null
116 --
117 hr_api.mandatory_arg_error
118 (p_api_name => l_proc
119 ,p_argument => 'vehicle_repository_id'
120 ,p_argument_value => p_vehicle_repository_id
121 );
122 --
123 IF ( nvl(pqp_vre_bus.g_vehicle_repository_id, hr_api.g_number)
124 = p_vehicle_repository_id) THEN
125 --
126 -- The legislation code has already been found with a previous
127 -- call to this function. Just return the value in the global
128 -- variable.
129 --
130 l_legislation_code := pqp_vre_bus.g_legislation_code;
131 hr_utility.set_location(l_proc, 20);
132 ELSE
133 --
134 -- The ID is different to the last call to this function
135 -- or this is the first call to this function.
136 --
137 OPEN csr_leg_code;
138 FETCH csr_leg_code into l_legislation_code;
139 --
140 IF csr_leg_code%notfound THEN
141 --
142 -- The primary key is invalid therefore we must error
143 --
144 CLOSE csr_leg_code;
145 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
146 fnd_message.raise_error;
147 END IF;
148 hr_utility.set_location(l_proc,30);
149 --
150 -- Set the global variables so the values are
151 -- available for the next call to this function.
152 --
153 CLOSE csr_leg_code;
154 pqp_vre_bus.g_vehicle_repository_id := p_vehicle_repository_id;
155 pqp_vre_bus.g_legislation_code := l_legislation_code;
156 END IF;
157 hr_utility.set_location(' Leaving:'|| l_proc, 40);
158 return l_legislation_code;
159 END return_legislation_code;
160 --
161 -- ----------------------------------------------------------------------------
162 -- |-----------------------------< chk_ddf >----------------------------------|
163 -- ----------------------------------------------------------------------------
164 --
165 -- Description:
166 -- Validates all the Developer Descriptive Flexfield values.
167 --
168 -- Prerequisites:
169 -- All other columns have been validated. Must be called as the
170 -- last step from insert_validate and update_validate.
171 --
172 -- In Arguments:
173 -- p_rec
174 --
175 -- Post Success:
176 -- If the Developer Descriptive Flexfield structure column and data values
177 -- are all valid this procedure will end normally and processing will
178 -- continue.
179 --
180 -- Post Failure:
181 -- If the Developer Descriptive Flexfield structure column value or any of
182 -- the data values are invalid then an application error is raised as
183 -- a PL/SQL exception.
184 --
185 -- Access Status:
186 -- Internal Row Handler Use Only.
187 --
188 -- ----------------------------------------------------------------------------
189 PROCEDURE chk_ddf
190 (p_rec in pqp_vre_shd.g_rec_type
191 ) IS
192 --
193 l_proc varchar2(72) := g_package || 'chk_ddf';
194 --
195 BEGIN
196 hr_utility.set_location('Entering:'||l_proc,10);
197 --
198 IF ((p_rec.vehicle_repository_id is not null) and (
199 nvl(pqp_vre_shd.g_old_rec.vre_information_category, hr_api.g_varchar2) <>
200 nvl(p_rec.vre_information_category, hr_api.g_varchar2) or
201 nvl(pqp_vre_shd.g_old_rec.vre_information1, hr_api.g_varchar2) <>
202 nvl(p_rec.vre_information1, hr_api.g_varchar2) or
203 nvl(pqp_vre_shd.g_old_rec.vre_information2, hr_api.g_varchar2) <>
204 nvl(p_rec.vre_information2, hr_api.g_varchar2) or
205 nvl(pqp_vre_shd.g_old_rec.vre_information3, hr_api.g_varchar2) <>
206 nvl(p_rec.vre_information3, hr_api.g_varchar2) or
207 nvl(pqp_vre_shd.g_old_rec.vre_information4, hr_api.g_varchar2) <>
208 nvl(p_rec.vre_information4, hr_api.g_varchar2) or
209 nvl(pqp_vre_shd.g_old_rec.vre_information5, hr_api.g_varchar2) <>
210 nvl(p_rec.vre_information5, hr_api.g_varchar2) or
211 nvl(pqp_vre_shd.g_old_rec.vre_information6, hr_api.g_varchar2) <>
212 nvl(p_rec.vre_information6, hr_api.g_varchar2) or
213 nvl(pqp_vre_shd.g_old_rec.vre_information7, hr_api.g_varchar2) <>
214 nvl(p_rec.vre_information7, hr_api.g_varchar2) or
215 nvl(pqp_vre_shd.g_old_rec.vre_information8, hr_api.g_varchar2) <>
216 nvl(p_rec.vre_information8, hr_api.g_varchar2) or
217 nvl(pqp_vre_shd.g_old_rec.vre_information9, hr_api.g_varchar2) <>
218 nvl(p_rec.vre_information9, hr_api.g_varchar2) or
219 nvl(pqp_vre_shd.g_old_rec.vre_information10, hr_api.g_varchar2) <>
220 nvl(p_rec.vre_information10, hr_api.g_varchar2) or
221 nvl(pqp_vre_shd.g_old_rec.vre_information11, hr_api.g_varchar2) <>
222 nvl(p_rec.vre_information11, hr_api.g_varchar2) or
223 nvl(pqp_vre_shd.g_old_rec.vre_information12, hr_api.g_varchar2) <>
224 nvl(p_rec.vre_information12, hr_api.g_varchar2) or
225 nvl(pqp_vre_shd.g_old_rec.vre_information13, hr_api.g_varchar2) <>
226 nvl(p_rec.vre_information13, hr_api.g_varchar2) or
227 nvl(pqp_vre_shd.g_old_rec.vre_information14, hr_api.g_varchar2) <>
228 nvl(p_rec.vre_information14, hr_api.g_varchar2) or
229 nvl(pqp_vre_shd.g_old_rec.vre_information15, hr_api.g_varchar2) <>
230 nvl(p_rec.vre_information15, hr_api.g_varchar2) or
231 nvl(pqp_vre_shd.g_old_rec.vre_information16, hr_api.g_varchar2) <>
232 nvl(p_rec.vre_information16, hr_api.g_varchar2) or
233 nvl(pqp_vre_shd.g_old_rec.vre_information17, hr_api.g_varchar2) <>
234 nvl(p_rec.vre_information17, hr_api.g_varchar2) or
235 nvl(pqp_vre_shd.g_old_rec.vre_information18, hr_api.g_varchar2) <>
236 nvl(p_rec.vre_information18, hr_api.g_varchar2) or
237 nvl(pqp_vre_shd.g_old_rec.vre_information19, hr_api.g_varchar2) <>
238 nvl(p_rec.vre_information19, hr_api.g_varchar2) or
239 nvl(pqp_vre_shd.g_old_rec.vre_information20, hr_api.g_varchar2) <>
240 nvl(p_rec.vre_information20, hr_api.g_varchar2) ))
241 or (p_rec.vehicle_repository_id is null) THEN
242 --
243 -- Only execute the validation if absolutely necessary:
244 -- a) During update, the structure column value or any
245 -- of the attribute values have actually changed.
246 -- b) During insert.
247 --
248 hr_dflex_utility.ins_or_upd_descflex_attribs
249 (p_appl_short_name =>'PQP'
250 ,p_descflex_name =>'Vehicle Repository Info DDF'
251 ,p_attribute_category => p_rec.vre_information_category
252 ,p_attribute1_name =>'VRE_INFORMATION1'
253 ,p_attribute1_value => p_rec.vre_information1
254 ,p_attribute2_name =>'VRE_INFORMATION2'
255 ,p_attribute2_value => p_rec.vre_information2
256 ,p_attribute3_name =>'VRE_INFORMATION3'
257 ,p_attribute3_value => p_rec.vre_information3
258 ,p_attribute4_name =>'VRE_INFORMATION4'
259 ,p_attribute4_value => p_rec.vre_information4
260 ,p_attribute5_name =>'VRE_INFORMATION5'
261 ,p_attribute5_value => p_rec.vre_information5
262 ,p_attribute6_name =>'VRE_INFORMATION6'
263 ,p_attribute6_value => p_rec.vre_information6
264 ,p_attribute7_name =>'VRE_INFORMATION7'
265 ,p_attribute7_value => p_rec.vre_information7
266 ,p_attribute8_name =>'VRE_INFORMATION8'
267 ,p_attribute8_value => p_rec.vre_information8
268 ,p_attribute9_name =>'VRE_INFORMATION9'
269 ,p_attribute9_value => p_rec.vre_information9
270 ,p_attribute10_name =>'VRE_INFORMATION10'
271 ,p_attribute10_value => p_rec.vre_information10
272 ,p_attribute11_name =>'VRE_INFORMATION11'
273 ,p_attribute11_value => p_rec.vre_information11
274 ,p_attribute12_name =>'VRE_INFORMATION12'
275 ,p_attribute12_value => p_rec.vre_information12
276 ,p_attribute13_name =>'VRE_INFORMATION13'
277 ,p_attribute13_value => p_rec.vre_information13
278 ,p_attribute14_name =>'VRE_INFORMATION14'
279 ,p_attribute14_value => p_rec.vre_information14
280 ,p_attribute15_name =>'VRE_INFORMATION15'
281 ,p_attribute15_value => p_rec.vre_information15
282 ,p_attribute16_name =>'VRE_INFORMATION16'
283 ,p_attribute16_value => p_rec.vre_information16
284 ,p_attribute17_name =>'VRE_INFORMATION17'
285 ,p_attribute17_value => p_rec.vre_information17
286 ,p_attribute18_name =>'VRE_INFORMATION18'
287 ,p_attribute18_value => p_rec.vre_information18
288 ,p_attribute19_name =>'VRE_INFORMATION19'
289 ,p_attribute19_value => p_rec.vre_information19
290 ,p_attribute20_name =>'VRE_INFORMATION20'
291 ,p_attribute20_value => p_rec.vre_information20
292 );
293 END IF;
294 --
295 hr_utility.set_location(' Leaving:'||l_proc,20);
296 END chk_ddf;
297 --
298 -- ----------------------------------------------------------------------------
299 -- |------------------------------< chk_df >----------------------------------|
300 -- ----------------------------------------------------------------------------
301 --
302 -- Description:
303 -- Validates all the Descriptive Flexfield values.
304 --
305 -- Prerequisites:
306 -- All other columns have been validated. Must be called as the
307 -- last step from insert_validate and update_validate.
308 --
309 -- In Arguments:
310 -- p_rec
311 --
312 -- Post Success:
313 -- If the Descriptive Flexfield structure column and data values are
314 -- all valid this procedure will end normally and processing will
315 -- continue.
316 --
317 -- Post Failure:
318 -- If the Descriptive Flexfield structure column value or any of
319 -- the data values are invalid then an application error is raised as
320 -- a PL/SQL exception.
321 --
322 -- Access Status:
323 -- Internal Row Handler Use Only.
324 --
325 -- ----------------------------------------------------------------------------
326 PROCEDURE chk_df
327 (p_rec in pqp_vre_shd.g_rec_type
328 ) IS
329 --
330 l_proc varchar2(72) := g_package || 'chk_df';
331 --
332 BEGIN
333 hr_utility.set_location('Entering:'||l_proc,10);
334 --
335 IF ((p_rec.vehicle_repository_id is not null) and (
336 nvl(pqp_vre_shd.g_old_rec.vre_attribute_category, hr_api.g_varchar2) <>
337 nvl(p_rec.vre_attribute_category, hr_api.g_varchar2) or
338 nvl(pqp_vre_shd.g_old_rec.vre_attribute1, hr_api.g_varchar2) <>
339 nvl(p_rec.vre_attribute1, hr_api.g_varchar2) or
340 nvl(pqp_vre_shd.g_old_rec.vre_attribute2, hr_api.g_varchar2) <>
341 nvl(p_rec.vre_attribute2, hr_api.g_varchar2) or
342 nvl(pqp_vre_shd.g_old_rec.vre_attribute3, hr_api.g_varchar2) <>
343 nvl(p_rec.vre_attribute3, hr_api.g_varchar2) or
344 nvl(pqp_vre_shd.g_old_rec.vre_attribute4, hr_api.g_varchar2) <>
345 nvl(p_rec.vre_attribute4, hr_api.g_varchar2) or
346 nvl(pqp_vre_shd.g_old_rec.vre_attribute5, hr_api.g_varchar2) <>
347 nvl(p_rec.vre_attribute5, hr_api.g_varchar2) or
348 nvl(pqp_vre_shd.g_old_rec.vre_attribute6, hr_api.g_varchar2) <>
349 nvl(p_rec.vre_attribute6, hr_api.g_varchar2) or
350 nvl(pqp_vre_shd.g_old_rec.vre_attribute7, hr_api.g_varchar2) <>
351 nvl(p_rec.vre_attribute7, hr_api.g_varchar2) or
352 nvl(pqp_vre_shd.g_old_rec.vre_attribute8, hr_api.g_varchar2) <>
353 nvl(p_rec.vre_attribute8, hr_api.g_varchar2) or
354 nvl(pqp_vre_shd.g_old_rec.vre_attribute9, hr_api.g_varchar2) <>
355 nvl(p_rec.vre_attribute9, hr_api.g_varchar2) or
356 nvl(pqp_vre_shd.g_old_rec.vre_attribute10, hr_api.g_varchar2) <>
357 nvl(p_rec.vre_attribute10, hr_api.g_varchar2) or
358 nvl(pqp_vre_shd.g_old_rec.vre_attribute11, hr_api.g_varchar2) <>
359 nvl(p_rec.vre_attribute11, hr_api.g_varchar2) or
360 nvl(pqp_vre_shd.g_old_rec.vre_attribute12, hr_api.g_varchar2) <>
361 nvl(p_rec.vre_attribute12, hr_api.g_varchar2) or
362 nvl(pqp_vre_shd.g_old_rec.vre_attribute13, hr_api.g_varchar2) <>
363 nvl(p_rec.vre_attribute13, hr_api.g_varchar2) or
364 nvl(pqp_vre_shd.g_old_rec.vre_attribute14, hr_api.g_varchar2) <>
365 nvl(p_rec.vre_attribute14, hr_api.g_varchar2) or
366 nvl(pqp_vre_shd.g_old_rec.vre_attribute15, hr_api.g_varchar2) <>
367 nvl(p_rec.vre_attribute15, hr_api.g_varchar2) or
368 nvl(pqp_vre_shd.g_old_rec.vre_attribute16, hr_api.g_varchar2) <>
369 nvl(p_rec.vre_attribute16, hr_api.g_varchar2) or
370 nvl(pqp_vre_shd.g_old_rec.vre_attribute17, hr_api.g_varchar2) <>
371 nvl(p_rec.vre_attribute17, hr_api.g_varchar2) or
372 nvl(pqp_vre_shd.g_old_rec.vre_attribute18, hr_api.g_varchar2) <>
373 nvl(p_rec.vre_attribute18, hr_api.g_varchar2) or
374 nvl(pqp_vre_shd.g_old_rec.vre_attribute19, hr_api.g_varchar2) <>
375 nvl(p_rec.vre_attribute19, hr_api.g_varchar2) or
376 nvl(pqp_vre_shd.g_old_rec.vre_attribute20, hr_api.g_varchar2) <>
377 nvl(p_rec.vre_attribute20, hr_api.g_varchar2) ))
378 or (p_rec.vehicle_repository_id is null) then
379 --
380 -- Only execute the validation if absolutely necessary:
381 -- a) During update, the structure column value or any
382 -- of the attribute values have actually changed.
383 -- b) During insert.
384 --
385 hr_dflex_utility.ins_or_upd_descflex_attribs
386 (p_appl_short_name => 'PQP'
387 ,p_descflex_name => 'Vehicle Repository Info DF'
388 ,p_attribute_category => p_rec.vre_attribute_category
389 ,p_attribute1_name => 'VRE_ATTRIBUTE1'
390 ,p_attribute1_value => p_rec.vre_attribute1
391 ,p_attribute2_name => 'VRE_ATTRIBUTE2'
392 ,p_attribute2_value => p_rec.vre_attribute2
393 ,p_attribute3_name => 'VRE_ATTRIBUTE3'
394 ,p_attribute3_value => p_rec.vre_attribute3
395 ,p_attribute4_name => 'VRE_ATTRIBUTE4'
396 ,p_attribute4_value => p_rec.vre_attribute4
397 ,p_attribute5_name => 'VRE_ATTRIBUTE5'
398 ,p_attribute5_value => p_rec.vre_attribute5
399 ,p_attribute6_name => 'VRE_ATTRIBUTE6'
400 ,p_attribute6_value => p_rec.vre_attribute6
401 ,p_attribute7_name => 'VRE_ATTRIBUTE7'
402 ,p_attribute7_value => p_rec.vre_attribute7
403 ,p_attribute8_name => 'VRE_ATTRIBUTE8'
404 ,p_attribute8_value => p_rec.vre_attribute8
405 ,p_attribute9_name => 'VRE_ATTRIBUTE9'
406 ,p_attribute9_value => p_rec.vre_attribute9
407 ,p_attribute10_name => 'VRE_ATTRIBUTE10'
408 ,p_attribute10_value => p_rec.vre_attribute10
409 ,p_attribute11_name => 'VRE_ATTRIBUTE11'
410 ,p_attribute11_value => p_rec.vre_attribute11
411 ,p_attribute12_name => 'VRE_ATTRIBUTE12'
412 ,p_attribute12_value => p_rec.vre_attribute12
413 ,p_attribute13_name => 'VRE_ATTRIBUTE13'
414 ,p_attribute13_value => p_rec.vre_attribute13
415 ,p_attribute14_name => 'VRE_ATTRIBUTE14'
416 ,p_attribute14_value => p_rec.vre_attribute14
417 ,p_attribute15_name => 'VRE_ATTRIBUTE15'
418 ,p_attribute15_value => p_rec.vre_attribute15
419 ,p_attribute16_name => 'VRE_ATTRIBUTE16'
420 ,p_attribute16_value => p_rec.vre_attribute16
421 ,p_attribute17_name => 'VRE_ATTRIBUTE17'
422 ,p_attribute17_value => p_rec.vre_attribute17
423 ,p_attribute18_name => 'VRE_ATTRIBUTE18'
424 ,p_attribute18_value => p_rec.vre_attribute18
425 ,p_attribute19_name => 'VRE_ATTRIBUTE19'
426 ,p_attribute19_value => p_rec.vre_attribute19
427 ,p_attribute20_name => 'VRE_ATTRIBUTE20'
428 ,p_attribute20_value => p_rec.vre_attribute20
429 );
430 END IF;
431 --
432 hr_utility.set_location(' Leaving:'||l_proc,20);
433 END chk_df;
434 --
435 -- ----------------------------------------------------------------------------
436 -- |-----------------------< chk_non_updateable_args >------------------------|
437 -- ----------------------------------------------------------------------------
438 -- {Start Of Comments}
439 --
440 -- Description:
441 -- This procedure is used to ensure that non updateable attributes have
442 -- not been updated. If an attribute has been updated an error is generated.
443 --
444 -- Pre Conditions:
445 -- g_old_rec has been populated with details of the values currently in
446 -- the database.
447 --
448 -- In Arguments:
449 -- p_rec has been populated with the updated values the user would like the
450 -- record set to.
451 --
452 -- Post Success:
453 -- Processing continues if all the non updateable attributes have not
454 -- changed.
455 --
456 -- Post Failure:
457 -- An application error is raised if any of the non updatable attributes
458 -- have been altered.
459 --
460 -- {End Of Comments}
461 -- ----------------------------------------------------------------------------
462 PROCEDURE chk_non_updateable_args
463 (p_effective_date IN DATE
464 ,p_rec IN pqp_vre_shd.g_rec_type
465 ) IS
466 --
467 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
468 --
469 BEGIN
470 --
471 -- Only proceed with the validation if a row exists for the current
472 -- record in the HR Schema.
473 --
474 IF NOT pqp_vre_shd.api_updating
475 (p_vehicle_repository_id => p_rec.vehicle_repository_id
476 ,p_effective_date => p_effective_date
477 ,p_object_version_number => p_rec.object_version_number
478 ) THEN
479 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
480 fnd_message.set_token('PROCEDURE ', l_proc);
481 fnd_message.set_token('STEP ', '5');
482 fnd_message.raise_error;
483 END IF;
484 --
485 -- EDIT_HERE: Add checks to ensure non-updateable args have
486 -- not been updated.
487 --
488 End chk_non_updateable_args;
489 --
490 -- ----------------------------------------------------------------------------
491 -- |--------------------------< dt_update_validate >--------------------------|
492 -- ----------------------------------------------------------------------------
493 -- {Start Of Comments}
494 --
495 -- Description:
496 -- This procedure is used for referential integrity of datetracked
497 -- parent entities when a datetrack update operation is taking place
498 -- and where there is no cascading of update defined for this entity.
499 --
500 -- Prerequisites:
501 -- This procedure is called from the update_validate.
502 --
503 -- In Parameters:
504 --
505 -- Post Success:
506 -- Processing continues.
507 --
508 -- Post Failure:
509 --
510 -- Developer Implementation Notes:
511 -- This procedure should not need maintenance unless the HR Schema model
512 -- changes.
513 --
514 -- Access Status:
515 -- Internal Row Handler Use Only.
516 --
517 -- {End Of Comments}
518 -- ----------------------------------------------------------------------------
519 PROCEDURE dt_update_validate
520 (p_datetrack_mode IN VARCHAR2
521 ,p_validation_start_date IN DATE
522 ,p_validation_end_date IN DATE
523 ) IS
524 --
525 l_proc varchar2(72) := g_package||'dt_update_validate';
526 --
527 BEGIN
528 --
529 -- Ensure that the p_datetrack_mode argument is not null
530 --
531 hr_api.mandatory_arg_error
532 (p_api_name => l_proc
533 ,p_argument => 'datetrack_mode'
534 ,p_argument_value => p_datetrack_mode
535 );
536 --
537 -- Mode will be valid, as this is checked at the start of the upd.
538 --
539 -- Ensure the arguments are not null
540 --
541 hr_api.mandatory_arg_error
542 (p_api_name => l_proc
543 ,p_argument => 'validation_start_date'
544 ,p_argument_value => p_validation_start_date
545 );
546 --
547 hr_api.mandatory_arg_error
548 (p_api_name => l_proc
549 ,p_argument => 'validation_end_date'
550 ,p_argument_value => p_validation_end_date
551 );
552 --
553 --
554 --
555 EXCEPTION
556 WHEN Others THEN
557 --
558 -- An unhandled or unexpected error has occurred which
559 -- we must report
560 --
561 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
562 fnd_message.set_token('PROCEDURE', l_proc);
563 fnd_message.set_token('STEP','15');
564 fnd_message.raise_error;
565 END dt_update_validate;
566 --
567 -- ----------------------------------------------------------------------------
568 -- |--------------------------< dt_delete_validate >--------------------------|
569 -- ----------------------------------------------------------------------------
570 -- {Start Of Comments}
571 --
572 -- Description:
573 -- This procedure is used for referential integrity of datetracked
574 -- child entities when either a datetrack DELETE or ZAP is in operation
575 -- and where there is no cascading of delete defined for this entity.
576 -- For the datetrack mode of DELETE or ZAP we must ensure that no
577 -- datetracked child rows exist between the validation start and end
578 -- dates.
579 --
580 -- Prerequisites:
581 -- This procedure is called from the delete_validate.
582 --
583 -- In Parameters:
584 --
585 -- Post Success:
586 -- Processing continues.
587 --
588 -- Post Failure:
589 -- If a row exists by determining the returning Boolean value from the
590 -- generic dt_api.rows_exist function then we must supply an error via
591 -- the use of the local exception handler l_rows_exist.
592 --
593 -- Developer Implementation Notes:
594 -- This procedure should not need maintenance unless the HR Schema model
595 -- changes.
596 --
597 -- Access Status:
598 -- Internal Row Handler Use Only.
599 --
600 -- {End Of Comments}
601 -- ----------------------------------------------------------------------------
602 PROCEDURE dt_delete_validate
603 (p_vehicle_repository_id IN NUMBER
604 ,p_datetrack_mode IN VARCHAR2
605 ,p_validation_start_date IN DATE
606 ,p_validation_end_date IN DATE
607 ) IS
608 --
609 l_proc varchar2(72) := g_package||'dt_delete_validate';
610 --
611 BEGIN
612 --
613 -- Ensure that the p_datetrack_mode argument is not null
614 --
615 hr_api.mandatory_arg_error
616 (p_api_name => l_proc
617 ,p_argument => 'datetrack_mode'
618 ,p_argument_value => p_datetrack_mode
619 );
620 --
621 -- Only perform the validation if the datetrack mode is either
622 -- DELETE or ZAP
623 --
624 IF (p_datetrack_mode = hr_api.g_delete or
625 p_datetrack_mode = hr_api.g_zap) THEN
626 --
627 --
628 -- Ensure the arguments are not null
629 --
630 hr_api.mandatory_arg_error
631 (p_api_name => l_proc
632 ,p_argument => 'validation_start_date'
633 ,p_argument_value => p_validation_start_date
634 );
635 --
636 hr_api.mandatory_arg_error
637 (p_api_name => l_proc
638 ,p_argument => 'validation_end_date'
639 ,p_argument_value => p_validation_end_date
640 );
641 --
642 hr_api.mandatory_arg_error
643 (p_api_name => l_proc
644 ,p_argument => 'vehicle_repository_id'
645 ,p_argument_value => p_vehicle_repository_id
646 );
647 --
648 --
649 --
650 END IF;
651 --
652 EXCEPTION
653 WHEN OTHERS THEN
654 --
655 -- An unhandled or unexpected error has occurred which
656 -- we must report
657 --
658 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
659 fnd_message.set_token('PROCEDURE', l_proc);
660 fnd_message.set_token('STEP','15');
661 fnd_message.raise_error;
662 --
663 END dt_delete_validate;
664 --
665 -- ----------------------------------------------------------------------------
666 -- |--------------------< Chk Unique Veh Identification Number ---------------|
667 -- ----------------------------------------------------------------------------
668 --Checking the Vehicle Identification existence ,if same id exist
669 --then user cannot enter same Identification number once again.
670 PROCEDURE chk_unique_idennum
671 (p_rec IN pqp_vre_shd.g_rec_type
672 ,p_effective_date IN DATE
673 ,p_datetrack_mode IN VARCHAR2
674 ,p_validation_start_date IN DATE
675 ,p_validation_end_date IN DATE
676 ,p_update_flag IN VARCHAR2
677 ) IS
678
679 --Declare the cursor to get the registration number count
680 CURSOR c_iden_exist_cursor IS
681 SELECT COUNT(pvr.vehicle_id_number)
682 FROM pqp_vehicle_repository_f pvr
683 WHERE pvr.vehicle_id_number=p_rec.vehicle_id_number
684 AND pvr.business_group_id=p_rec.business_group_id
685 AND (p_effective_date BETWEEN pvr.effective_start_date
686 AND pvr.effective_end_date
687 OR p_effective_date < pvr.effective_start_date);
688
689 --Cursor to get the previous vehicle_id_number
690 CURSOR c_chk_previous_value_cur IS
691 SELECT pvr.vehicle_id_number
692 FROM pqp_vehicle_repository_f pvr
693 WHERE pvr.registration_number=p_rec.registration_number
694 AND pvr.business_group_id=p_rec.business_group_id
695 AND (p_effective_date BETWEEN pvr.effective_start_date
696 AND pvr.effective_end_date
697 OR p_effective_date < pvr.effective_start_date);
698
699
700 --Declare local variable
701 l_count number;
702 l_previous_id_number pqp_vehicle_repository_f.vehicle_id_number%TYPE;
703 BEGIN
704 IF p_update_flag = 'Y' THEN
705 OPEN c_chk_previous_value_cur;
706 FETCH c_chk_previous_value_cur INTO l_previous_id_number;
707 CLOSE c_chk_previous_value_cur;
708 IF nvl(l_previous_id_number,-1) <> p_rec.vehicle_id_number THEN
709 OPEN c_iden_exist_cursor;
710 FETCH c_iden_exist_cursor INTO l_count;
711 CLOSE c_iden_exist_cursor;
712 IF l_count>0 THEN
713 fnd_message.set_name('PQP','PQP_230150_INDEN_EXISTS');
714 fnd_message.raise_error;
715 END IF;
716 END IF;
717 ELSE
718 OPEN c_iden_exist_cursor;
719 FETCH c_iden_exist_cursor INTO l_count;
720 CLOSE c_iden_exist_cursor;
721 IF l_count>0 THEN
722 fnd_message.set_name('PQP','PQP_230150_INDEN_EXISTS');
723 fnd_message.raise_error;
724 END IF;
725 END IF;
726 EXCEPTION
727 --------
728 WHEN no_data_found THEN
729 NULL;
730 End chk_unique_idennum;
731
732 -- ----------------------------------------------------------------------------
733 -- |------------------------< Chk Unique Reg Number >------------------------|
734 -- ----------------------------------------------------------------------------
735 --Checking th RegNumber existence ,if same registration number exist
736 --then user cannot enter same registration number once again.
737 PROCEDURE chk_unique_regnum
738 (p_rec IN pqp_vre_shd.g_rec_type
739 ,p_effective_date IN DATE
740 ,p_datetrack_mode IN VARCHAR2
741 ,p_validation_start_date IN DATE
742 ,p_validation_end_date IN DATE
743 ) IS
744
745 --Declare the cursor to get the registration number count
746 CURSOR reg_exist_cursor IS
747 SELECT COUNT(pvr.registration_number)
748 FROM pqp_vehicle_repository_f pvr
749 WHERE pvr.registration_number=p_rec.registration_number
750 AND pvr.business_group_id=p_rec.business_group_id
751 AND (p_effective_date BETWEEN pvr.effective_start_date
752 AND pvr.effective_end_date
753 OR p_effective_date < pvr.effective_start_date);
754
755 --Declare local variable
756 l_count number;
757 BEGIN
758 OPEN reg_exist_cursor;
759 FETCH reg_exist_cursor INTO l_count;
760 CLOSE reg_exist_cursor;
761 IF l_count>0 THEN
762 fnd_message.set_name('PQP','PQP_230728_VEH_EXISTS');
763 fnd_message.raise_error;
764 END IF;
765 EXCEPTION
766 --------
767 WHEN no_data_found THEN
768 NULL;
769 End;
770
771 ------------------------------------------------------------------------------
772 ----------------------Used to get Columnname for Configuration table----------
773 ------------------------------------------------------------------------------
774 --This function will be used to get column Name for SegmentName
775 --Why we used this function is,If there will be any future changes in
776 --configuration table column structure then we will have to change the column
777 --names only with in this function.
778 --It always will call from pqp_get_config_value function.
779
780 FUNCTION pqp_get_colname
781 (p_segment_name IN VARCHAR2,--Segment Name
782 p_information_category IN VARCHAR2 --Information category
783 ) RETURN VARCHAR2 IS
784
785 l_column_name VARCHAR2(30) ;
786
787 BEGIN
788 IF p_segment_name = 'CalculationMethod'
789 AND p_information_category = 'PQP_VEHICLE_MILEAGE' THEN
790 l_column_name := 'PCV_INFORMATION1';
791 ELSIF p_segment_name = 'MaxCmyVehAllow'
792 AND p_information_category = 'PQP_VEHICLE_MILEAGE' THEN
793 l_column_name := 'PCV_INFORMATION2';
794 ELSIF p_segment_name = 'MaxPriVehAllow'
795 AND p_information_category = 'PQP_VEHICLE_MILEAGE' THEN
796 l_column_name := 'PCV_INFORMATION3';
797 ELSIF p_segment_name = 'ShareCmyCar'
798 AND p_information_category = 'PQP_VEHICLE_MILEAGE' THEN
799 l_column_name := 'PCV_INFORMATION4';
800 ELSIF p_segment_name = 'SharePriCar'
801 AND p_information_category = 'PQP_VEHICLE_MILEAGE' THEN
802 l_column_name := 'PCV_INFORMATION5';
803 ELSIF p_segment_name = 'PreTaxYearClmVldUntil'
804 AND p_information_category = 'PQP_VEHICLE_MILEAGE' THEN
805 l_column_name := 'PCV_INFORMATION6';
806 ELSIF p_segment_name = 'AllowCmyPriVehClms'
807 AND p_information_category = 'PQP_VEHICLE_MILEAGE' THEN
808 l_column_name := 'PCV_INFORMATION7';
809 ELSIF p_segment_name = 'SrchCriteriaRtTbl'
810 AND p_information_category = 'PQP_VEHICLE_MILEAGE' THEN
811 l_column_name := 'PCV_INFORMATION8';
812 ELSIF p_segment_name = 'ValidatePriVehClmsInRep'
813 AND p_information_category = 'PQP_VEHICLE_MILEAGE' THEN
814 l_column_name := 'PCV_INFORMATION9';
815 ELSIF p_segment_name = 'VehClmsCrectionPrdInDays'
816 AND p_information_category = 'PQP_VEHICLE_MILEAGE' THEN
817 l_column_name := 'PCV_INFORMATION10';
818 ELSIF p_segment_name = 'OwnerShip'
819 AND p_information_category = 'GB_VEHICLE_CALC_INFO' THEN
820 l_column_name := 'PCV_INFORMATION1';
821 ELSIF p_segment_name = 'UsageType'
822 AND p_information_category = 'GB_VEHICLE_CALC_INFO' THEN
823 l_column_name := 'PCV_INFORMATION2';
824 ELSIF p_segment_name = 'VehicleType'
825 AND p_information_category = 'GB_VEHICLE_CALC_INFO' THEN
826 l_column_name := 'PCV_INFORMATION3';
827 ELSIF p_segment_name = 'Fueltype'
828 AND p_information_category = 'GB_VEHICLE_CALC_INFO' THEN
829 l_column_name := 'PCV_INFORMATION4';
830 ELSIF p_segment_name = 'RatesTable'
831 AND p_information_category = 'GB_VEHICLE_CALC_INFO' THEN
832 l_column_name := 'PCV_INFORMATION5';
833 ELSIF p_segment_name = 'ClaimElement'
834 AND p_information_category = 'GB_VEHICLE_CALC_INFO' THEN
835 l_column_name := 'PCV_INFORMATION6';
836 END IF;
837 RETURN l_column_name ;
838 END pqp_get_colname;
839 --End of pqp_get_colname
840 --------------------------------------------------------------------------------
841 -------------------This is used to get the Configuration value-----------------
842 --------------------------------------------------------------------------------
843 --Used to get the configuration value based on either business groupId
844 --or legislation Id
845 --If there is value at business groupId ,then it will return that value
846 --otherwise it will returns legistation specific value
847
848 FUNCTION pqp_get_config_value
849 (p_business_group_id IN NUMBER,
850 p_legislation_code IN VARCHAR2,
851 p_seg_col_name IN VARCHAR2, -- Col Value to be found
852 p_table_name IN VARCHAR2, -- Table Name
853 p_information_category IN VARCHAR2
854 ) RETURN VARCHAR2 IS
855
856 --Local variable declaration
857 l_column_value VARCHAR(50);
858 l_column_name VARCHAR(50);
859 TYPE ref_csr_typ IS REF CURSOR;
860 c_column_cursor ref_csr_typ;
861 l_temp_str VARCHAR2(1000);
862 BEGIN
863 -- Call funtion to get the specific columnName for segment and category
864 BEGIN
865 --Used to get the column name for Segment name .
866 l_column_name := pqp_get_colname(p_seg_col_name,p_information_category);
867 END;
868
869 l_temp_str := 'SELECT '|| l_column_name ||'
870 FROM (SELECT '|| l_column_name ||'
871 FROM pqp_configuration_values
872 WHERE ((business_group_id = ' ||p_business_group_id ||'
873 AND legislation_code IS NULL )
874 OR (business_group_id IS NULL
875 AND legislation_code =
876 '||''''||p_legislation_code ||''''||')
877 OR (business_group_id IS NULL
878 AND legislation_code IS NULL))
879 AND PCV_INFORMATION_CATEGORY =
880 '|| ''''||p_information_category ||''''||'
881 ORDER BY business_group_id,legislation_code )
882 WHERE ROWNUM=1' ;
883
884 OPEN c_column_cursor FOR l_temp_str;
885 FETCH c_column_cursor INTO l_column_value;
886 CLOSE c_column_cursor;
887 RETURN l_column_value;
888 END pqp_get_config_value;
889 -- end function
890 ----------------------------------------------------------------------------
891 ---------------Used to get the fiscal ratings-------------------------------
892 ----------------------------------------------------------------------------
893 --Used to get the fiscal ratings UOM value for business groupId
894 PROCEDURE get_uom_fiscal_ratings
895 (p_business_group_id IN NUMBER
896 ,p_meaning OUT NOCOPY VARCHAR2
897 ) IS
898 CURSOR fiscal_cursor IS
899 SELECT meaning
900 FROM hr_lookups
901 WHERE lookup_type = 'PQP_FISCAL_RATINGS_UOM'
902 AND enabled_flag = 'Y';
903
904 --Local variables
905 l_meaning hr_lookups.meaning%TYPE;
906 l_legislation_code pqp_configuration_values.legislation_code%TYPE;
907 Begin
908
909 --Getting the legislationId for business groupId
910 l_legislation_code :=
911 pqp_vre_bus.get_legislation_code(p_business_group_id);
912 --setting the lg context
913 hr_api.set_legislation_context(l_legislation_code);
914 OPEN fiscal_cursor;
915 FETCH fiscal_cursor INTO l_meaning;
916 CLOSE fiscal_cursor;
917 p_meaning := NVL(l_meaning,'NONE');
918 EXCEPTION
919 WHEN no_data_found THEN
920 p_meaning := 'NONE';
921 NULL;
922 End ;
923
924 -- ----------------------------------------------------------------------------
925 -- |------------------------< Used to check the lookup codes >-----------------
926 -- ----------------------------------------------------------------------------
927 --Used to check the passed lookup code is correct or not
928 FUNCTION chk_lookup
929 (p_vehicle_repository_id IN NUMBER
930 ,p_lookup_type IN VARCHAR2
931 ,p_lookup_code IN VARCHAR2
932 ,p_effective_date IN DATE
933 ,p_validation_start_date IN DATE
934 ,p_validation_end_date IN DATE
935 ) RETURN NUMBER IS
936 BEGIN
937 --
938 -- If argument value is not null then
939 -- Check if the argument value exists in hr_lookups
940 -- where the lookup_type is passed lookuptype
941 --
942 IF p_lookup_code IS NOT NULL then
943 IF hr_api.not_exists_in_dt_hrstanlookups
944 (p_effective_date => p_effective_date
945 ,p_validation_start_date => p_validation_start_date
946 ,p_validation_end_date => p_validation_end_date
947 ,p_lookup_type => p_lookup_type
948 ,p_lookup_code => p_lookup_code
949 ) THEN
950 RETURN -1;
951 END IF;
952 END IF;
953 RETURN 0;
954 END;
955
956 /* CURSOR csr_lookup(cp_argument_value VARCHAR2,cp_lookup_type VARCHAR2) IS
957 SELECT lookup_code
958 FROM hr_lookups hrl
959 WHERE hrl.lookup_type = cp_lookup_type
960 AND hrl.lookup_code = cp_argument_value
961 AND enabled_flag = 'Y';
962 BEGIN
963 --
964 -- Validation of the lookup value based on the lookup type
965 --
966 OPEN csr_lookup(p_argument_value,p_lookup_type);
967 FETCH csr_lookup INTO l_lookup_code;
968 IF csr_lookup%NOTFOUND THEN
969 p_message := p_argument || 'Value is wrong ';
970 END IF;
971 CLOSE csr_lookup;
972 IF p_message IS NULL THEN
973 RETURN 0;
974 ELSE
975 RETURN -1;
976 END IF;
977 END check_lookup;
978 */
979
980 /*
981 This is used for checking all lookup types at once ,but now we removed this
982 because individual calls to
983 FUNCTION check_lookup
984 (p_rec IN pqp_vre_shd.g_rec_type
985 ,p_message OUT NOCOPY VARCHAR2
986 ) RETURN NUMBER IS
987
988 CURSOR csr_lookup(cp_argument_value VARCHAR2,cp_lookup_type VARCHAR2) IS
989 SELECT COUNT(rowid)
990 FROM hr_lookups hrl
991 WHERE hrl.lookup_type = cp_lookup_type
992 AND hrl.lookup_code = cp_argument_value
993 AND enabled_flag = 'Y';
994
995 l_lookup_code hr_lookups.lookup_code%TYPE;
996 l_lookup_count NUMBER;
997 BEGIN
998 --
999 -- Validation of the lookup value based on the lookup type
1000 --
1001
1002 IF p_rec.vehicle_ownership IS NOT NULL THEN
1003 OPEN csr_lookup(p_rec.vehicle_ownership,'PQP_VEHICLE_OWNERSHIP_TYPE');
1004 FETCH csr_lookup INTO l_lookup_count;
1005 CLOSE csr_lookup;
1006 IF l_lookup_count = 0 THEN
1007 p_message := 'Vehicle Ownership value is wrong ';
1008 RETURN -1;
1009 END IF;
1010 END IF;
1011 --This is for Vehicle Status lookup
1012 IF p_rec.vehicle_status IS NOT NULL THEN
1013 OPEN csr_lookup(p_rec.vehicle_status,'PQP_VEHICLE_STATUS');
1014 FETCH csr_lookup INTO l_lookup_count;
1015 CLOSE csr_lookup;
1016 IF l_lookup_count = 0 THEN
1017 p_message := 'Vehicle Status value is wrong';
1018 RETURN -1;
1019 END IF;
1020 END IF;
1021 --This is for Fuel Type lookup
1022 IF p_rec.fuel_type IS NOT NULL THEN
1023 OPEN csr_lookup(p_rec.fuel_type,'PQP_FUEL_TYPE');
1024 FETCH csr_lookup INTO l_lookup_count;
1025 CLOSE csr_lookup;
1026 IF l_lookup_count = 0 THEN
1027 p_message := 'Fuel Type value is wrong';
1028 RETURN -1;
1029 END IF;
1030 END IF;
1031 --Vehicle Type lookup check
1032 IF p_rec.vehicle_type IS NOT NULL THEN
1033 --Check if it is pedal Cycle then user cannot creat repository
1034 IF p_rec.vehicle_type = 'P' THEN
1035 p_message := 'Pedal Cycle for vehicle type is not' ||
1036 || 'allowed for Repository';
1037 RETURN -1;
1038 END IF;
1039 OPEN csr_lookup(p_rec.vehicle_type, 'PQP_VEHICLE_TYPE');
1040 FETCH csr_lookup INTO l_lookup_count;
1041 CLOSE csr_lookup;
1042 IF l_lookup_count = 0 THEN
1043 p_message := 'Vehicle Type value is wrong';
1044 RETURN -1;
1045 END IF;
1046 END IF;
1047 RETURN 0;
1048 END check_lookup;
1049 */
1050
1051 -----------------------------------------------------------------------------
1052 ------------------------<Update Validate for Regnum>-------------------------
1053 ----------------------------------------------------------------------------
1054 --Used to check is there any ragistraion number change at update time.
1055 --If there is any change in reg number then throw error
1056 PROCEDURE validate_regnum
1057 (p_rec in pqp_vre_shd.g_rec_type
1058 ,p_effective_date in date
1059 ,p_datetrack_mode in varchar2
1060 ,p_validation_start_date in date
1061 ,p_validation_end_date in date
1062 ) IS
1063 BEGIN
1064 IF p_rec.registration_number<> pqp_vre_shd.g_old_rec.registration_number THEN
1065 fnd_message.set_name('PQP', 'PQP_230727_REGNUM_UPD_RSTRICT');
1066 fnd_message.raise_error;
1067 END IF;
1068 END;
1069 --
1070 ---------------------------------------------------------------------------
1071 -----------------Ownership Change Check-----------------------------------
1072 ---------------------------------------------------------------------------
1073 --The change in the Ownership at update time must pop a warning message
1074 --is given to the user if the vehicle is assigned to employee to
1075 --indicate the change has to be done in the assignment on the usage type.
1076
1077 FUNCTION pqp_check_ownership_change
1078 (p_rec IN pqp_vre_shd.g_rec_type
1079 ,p_effective_date IN DATE
1080 ,p_message OUT NOCOPY VARCHAR2
1081 ) RETURN NUMBER IS
1082
1083 --Getting the allocation count for repositoryId
1084 CURSOR c_alloc_count_cursor IS
1085 SELECT COUNT(vehicle_allocation_id)
1086 FROM pqp_vehicle_allocations_f
1087 WHERE vehicle_repository_id = p_rec.vehicle_repository_id
1088 AND (p_effective_date between effective_start_date and effective_end_date
1089 OR p_effective_date <= effective_start_date)
1090 AND business_group_id = p_rec.business_group_id;
1091
1092 l_rowcount NUMBER;
1093
1094 BEGIN
1095 hr_utility.set_location('Entering pqp_check_ownership_change',45);
1096 OPEN c_alloc_count_cursor;
1097 FETCH c_alloc_count_cursor INTO l_rowcount;
1098 CLOSE c_alloc_count_cursor;
1099
1100 IF l_rowcount > 0 THEN
1101
1102 IF pqp_vre_shd.g_old_rec.vehicle_ownership
1103 <> p_rec.vehicle_ownership THEN
1104 p_message :='There is allocation for this vehicle, '||
1105 ' Please change the allocation usage type';
1106 RETURN -1;
1107 END IF;
1108
1109 END IF ;
1110 RETURN 0;
1111 END pqp_check_ownership_change;
1112 -- end pqp_ownership_check
1113
1114 ------------------------------------------------------------------------------
1115 -----------------Vehicle Status Change from Active to Inactive----------------
1116 ------------------------------------------------------------------------------
1117 --Updating a status from 'Active' to 'Inactive' must show a warning message
1118 --if the vehicle is being used by employees,indicating that
1119 --the vehicle cannot be claimed for mileage by an employee during Inactive
1120 --period
1121 --
1122 FUNCTION pqp_check_veh_status
1123 ( p_vehicle_repository_id IN NUMBER
1124 ,p_business_group_id IN NUMBER
1125 ,p_vehicle_status IN VARCHAR2
1126 ,p_effective_date IN DATE
1127 ,p_message OUT NOCOPY VARCHAR2
1128 ) RETURN NUMBER IS
1129 --Getting the vehicle allocation count for repositoryId
1130 CURSOR c_alloc_count_cursor IS
1131 SELECT pvr.vehicle_status ,1 test
1132 FROM PQP_VEHICLE_ALLOCATIONS_F pva,pqp_vehicle_repository_f pvr
1133 WHERE pva.vehicle_repository_id= p_vehicle_repository_id
1134 AND pva.vehicle_repository_id =pvr.vehicle_repository_id
1135 AND pva.business_group_id = pvr.business_group_id
1136 AND (p_effective_date between pva.effective_start_date
1137 AND pva.effective_end_date
1138 OR p_effective_date <= pva.effective_start_date)
1139 AND (p_effective_date between pvr.effective_start_date
1140 AND pvr.effective_end_date
1141 OR p_effective_date <= pvr.effective_start_date)
1142 AND pva.business_group_id = p_business_group_id;
1143
1144 --Declare local variables
1145 l_vehicle_status pqp_vehicle_repository_f.vehicle_status%TYPE;
1146 l_test_number NUMBER;
1147
1148 BEGIN
1149 OPEN c_alloc_count_cursor;
1150 FETCH c_alloc_count_cursor INTO l_vehicle_status,l_test_number;
1151 CLOSE c_alloc_count_cursor ;
1152 --check for original vehicle status ,if it is Active then check
1153 --current status
1154 IF l_vehicle_status = 'A' THEN
1155 IF p_vehicle_status = 'I' THEN
1156 IF l_test_number = 1 THEN
1157 --If record exist then returns -1
1158 p_message :='There is allocations for this Vehicle,'||
1159 ' So User cannot be change the vehicle status from Active '||
1160 ' to InActive';
1161 RETURN -1;
1162 END IF ;
1163 END IF ;
1164 END IF;
1165 RETURN 0;
1166 END pqp_check_veh_status;
1167 -- end function
1168 -----------------------------------------------------------------------------
1169 ----------------------Share Vehicle Across Employees-------------------------
1170 -----------------------------------------------------------------------------
1171 --Share Across Employees Field has marked the car as shared car,
1172 --then the user updates it as not a shared car at this point a check
1173 --need to be given to see if the car has been shared between the different
1174 --persons and if it has been shared then an error message to be given to
1175 --user indicating the car has been shared and need to go and
1176 --unallocated the car for employees and make this change.
1177
1178 FUNCTION pqp_check_shared_veh
1179 (p_rec IN pqp_vre_shd.g_rec_type,
1180 p_effective_date IN DATE ,
1181 p_message OUT NOCOPY VARCHAR2
1182 ) RETURN NUMBER IS
1183
1184 --Getting the all personIds which are allocated to this
1185 --vehicle repositoryId
1186 CURSOR c_alloc_count_cursor IS
1187 SELECT paa.person_id
1188 FROM pqp_vehicle_allocations_f pva
1189 ,per_all_assignments_f paa
1190 WHERE pva.vehicle_repository_id=p_rec.vehicle_repository_id
1191 AND paa.assignment_id=pva.assignment_id
1192 AND pva.business_group_id=p_rec.business_group_id
1193 AND p_effective_date
1194 BETWEEN paa.effective_start_date
1195 AND paa.effective_end_date
1196 AND (p_effective_date
1197 BETWEEN pva.effective_start_date
1198 AND pva.effective_end_date
1199 OR p_effective_date < pva.effective_start_date);
1200
1201 --Local variables declaration
1202 l_person_id NUMBER;
1203 l_temp_person_id NUMBER;
1204 l_count NUMBER := 0;
1205
1206 BEGIN
1207 hr_utility.set_location('Entering pqp_check_shared_veh',45);
1208
1209 --If ShareVehicle is 'N' then check the value of original
1210 --shared vehicle value
1211 IF p_rec.shared_vehicle = 'N' THEN
1212
1213 IF pqp_vre_shd.g_old_rec.shared_vehicle = 'Y' THEN
1214
1215 --Check value existence in allcations table
1216 OPEN c_alloc_count_cursor;
1217 LOOP
1218 FETCH c_alloc_count_cursor INTO l_person_id;
1219 EXIT when c_alloc_count_cursor%NOTFOUND ;
1220 --If it is first iteration
1221
1222 IF l_count = 0 THEN
1223 --if count is zero then assign personId to TempPersonId
1224 l_temp_person_id := l_person_id;
1225 l_count :=l_count+1;
1226 ELSE
1227 --If Vehicle is assigned to multiple personId's
1228 --then user cannot chage the shared status
1229 IF l_temp_person_id <> l_person_id THEN
1230 hr_utility.set_location('Assigned to two different persons',45);
1231 close c_alloc_count_cursor ;
1232 p_message := 'Vehicle is allocated to multilpe personIds ' ||
1233 ' So user cannot change the Shared Status ';
1234 RETURN -1;
1235 END IF;
1236 -- increse the count by 1
1237 l_count :=l_count+1;
1238 END IF;
1239 END LOOP ;
1240
1241 close c_alloc_count_cursor ;
1242 END IF ;
1243 END IF;
1244 RETURN 0;
1245 END pqp_check_shared_veh;
1246 -- end function
1247 ---------------------------------------------------------------------------
1248 ------------------------<Check Mandatory Fields>-------------------------
1249 ---------------------------------------------------------------------------
1250 FUNCTION chk_mandatory
1251 (p_argument IN VARCHAR2,
1252 p_argument_value IN VARCHAR2,
1253 p_message OUT NOCOPY VARCHAR2
1254 ) RETURN NUMBER IS
1255 BEGIN
1256
1257 IF p_argument_value IS NULL THEN
1258 p_message := p_argument || 'Value should be Mandatory';
1259 RETURN -1;
1260 END IF;
1261 RETURN 0;
1262 END chk_mandatory;
1263
1264 /*
1265 This is used checking all mandatory values at once
1266 removed this functin and made individual calls
1267 FUNCTION chk_mandatory
1268 ( p_rec IN pqp_vre_shd.g_rec_type
1269 ,p_message OUT NOCOPY VARCHAR2
1270 ) RETURN NUMBER IS
1271 BEGIN
1272 IF p_rec.vehicle_ownership IS NULL THEN
1273 p_message := 'Vehicle ownership is mandatory';
1274 RETURN -1;
1275 ELSIF p_rec.vehicle_type IS NULL THEN
1276 p_message := 'Vehicle type is mandatory';
1277 RETURN -1;
1278 ELSIF p_rec.registration_number IS NULL THEN
1279 p_message := 'Registration Number is mandatory';
1280 RETURN -1;
1281 ELSIF p_rec.make IS NULL THEN
1282 p_message := 'Make is mandatory';
1283 RETURN -1;
1284 ELSIF p_rec.model IS NULL THEN
1285 p_message := 'Model is mandatory';
1286 RETURN -1;
1287 ELSIF p_rec.engine_capacity_in_cc IS NULL THEN
1288 p_message := 'Engine Capacity in CC is mandatory';
1289 RETURN -1;
1290 ELSIF p_rec.fuel_type IS NULL THEN
1291 p_message := 'Fuel Type is mandatory';
1292 RETURN -1;
1293 ELSIF p_rec.vehicle_status IS NULL THEN
1294 p_message := 'Vehicle Status is mandatory';
1295 RETURN -1;
1296 END IF;
1297 RETURN 0;
1298 END chk_mandatory; */
1299 --------------------------------------------------------------------------
1300 ------------------Purge delete validation---------------------------------
1301 ---------------------------------------------------------------------------
1302 --Purge means the data is completely zapped from the database
1303 --but again the error message is given if the vehicle has been assigned to
1304 --an employee
1305 FUNCTION pqp_purge_delete_veh
1306 (p_rec IN pqp_vre_shd.g_rec_type,
1307 p_effective_date IN DATE ,
1308 p_message OUT NOCOPY VARCHAR2
1309 ) RETURN VARCHAR2 IS
1310 --Getting the allocation count for past ,future and current date tracks
1311 /*CURSOR c_pesron_names_cursor IS
1312 SELECT distinct papf.title ||' '||papf.first_name ||' '|| papf.last_name
1313 FROM pqp_vehicle_allocations_f pva
1314 ,per_all_assignments_f paa
1315 ,per_people_f papf
1316 WHERE pva.vehicle_repository_id=p_rec.vehicle_repository_id
1317 AND paa.assignment_id=pva.assignment_id
1318 AND papf.person_id=paa.person_id
1319 AND (p_effective_date
1320 BETWEEN papf.effective_start_date AND papf.effective_end_date
1321 OR p_effective_date <= papf.effective_start_date
1322 OR p_effective_date >= papf.effective_start_date )
1323 AND (p_effective_date
1324 BETWEEN paa.effective_start_date AND paa.effective_end_date
1325 OR p_effective_date <= paa.effective_start_date
1326 OR p_effective_date >= paa.effective_start_date )
1327 AND (p_effective_date
1328 BETWEEN pva.effective_start_date AND pva.effective_end_date
1329 OR p_effective_date <= pva.effective_start_date
1330 OR p_effective_date >= pva.effective_start_date ); */
1331 CURSOR c_pesron_names_cursor IS
1332 SELECT distinct hl.meaning ||' '||papf.first_name ||' '|| papf.last_name
1333 FROM pqp_vehicle_allocations_f pva
1334 ,per_all_assignments_f paa
1335 ,per_people_f papf
1336 ,hr_lookups hl
1337 WHERE pva.vehicle_repository_id=p_rec.vehicle_repository_id
1338 AND paa.assignment_id=pva.assignment_id
1339 AND papf.person_id=paa.person_id
1340 and hl.lookup_code=papf.title
1341 and hl.lookup_type = 'TITLE'
1342 and enabled_flag = 'Y'
1343 AND (p_effective_date
1344 BETWEEN papf.effective_start_date AND papf.effective_end_date
1345 OR p_effective_date <= papf.effective_start_date
1346 OR p_effective_date >= papf.effective_start_date )
1347 AND (p_effective_date
1348 BETWEEN paa.effective_start_date AND paa.effective_end_date
1349 OR p_effective_date <= paa.effective_start_date
1350 OR p_effective_date >= paa.effective_start_date );
1351
1352
1353 --local variables declaration
1354 l_person_name per_All_people_f.full_name%TYPE ;
1355 temp_name_str varchar2(2000);
1356
1357 BEGIN
1358 OPEN c_pesron_names_cursor;
1359 LOOP
1360 FETCH c_pesron_names_cursor INTO l_person_name;
1361 EXIT when c_pesron_names_cursor%NOTFOUND ;
1362 --Append all allocated personNames in string to display on UI.
1363 IF temp_name_str IS NOT NULL THEN
1364 temp_name_str := temp_name_str ||', ' ||l_person_name;
1365 ELSE
1366 temp_name_str := l_person_name;
1367 END IF;
1368 END LOOP ;
1369 CLOSE c_pesron_names_cursor ;
1370 p_message := 'This vehicle has been assigned to an employee,so please '||
1371 ' delete that allocation entry';
1372 RETURN temp_name_str;
1373 END pqp_purge_delete_veh;
1374 -- end function
1375 -------------------------------------------------------------------------------
1376 -------------Delete End date -------------------------------------------------
1377 ------------------------------------------------------------------------------
1378 --End date: This end dates a record in repository but an error
1379 --message will be given if the user is still using the vehicle.
1380 --The user must end date all the allocations and then end date repository data.
1381 --
1382 FUNCTION pqp_enddate_delete_veh
1383 (p_rec IN pqp_vre_shd.g_rec_type,
1384 p_effective_date IN DATE ,
1385 p_message OUT NOCOPY VARCHAR2
1386 ) RETURN VARCHAR2 IS
1387
1388 --Getting the allocation count for current and future date tracks
1389 CURSOR c_pesron_names_cursor IS
1390 SELECT distinct hl.meaning ||' '||papf.first_name ||' '|| papf.last_name
1391 FROM pqp_vehicle_allocations_f pva
1392 ,per_all_assignments_f paa
1393 ,per_people_f papf
1394 ,hr_lookups hl
1395 WHERE pva.vehicle_repository_id=p_rec.vehicle_repository_id
1396 AND paa.assignment_id=pva.assignment_id
1397 AND papf.person_id=paa.person_id
1398 and hl.lookup_code=papf.title
1399 AND hl.lookup_type = 'TITLE'
1400 and enabled_flag = 'Y'
1401 AND (p_effective_date
1402 BETWEEN papf.effective_start_date AND papf.effective_end_date
1403 OR p_effective_date <= papf.effective_start_date )
1404 AND (p_effective_date
1405 BETWEEN paa.effective_start_date AND paa.effective_end_date
1406 OR p_effective_date <= paa.effective_start_date )
1407 AND (p_effective_date
1408 BETWEEN pva.effective_start_date AND pva.effective_end_date
1409 OR p_effective_date <= pva.effective_start_date );
1410
1411 --Declare slocal variables
1412 l_person_name per_All_people_f.full_name%TYPE ;
1413 temp_name_str varchar2(2000);
1414
1415 BEGIN
1416 OPEN c_pesron_names_cursor;
1417 LOOP
1418 FETCH c_pesron_names_cursor INTO l_person_name;
1419 EXIT when c_pesron_names_cursor%NOTFOUND ;
1420
1421 IF temp_name_str IS NOT NULL THEN
1422 temp_name_str := temp_name_str ||', ' ||l_person_name;
1423 ELSE
1424 temp_name_str := l_person_name;
1425 END IF;
1426
1427 END LOOP ;
1428 CLOSE c_pesron_names_cursor ;
1429 p_message := 'This vehicle has been assigned to an employee,so please '||
1430 ' delete that allocation entry';
1431 RETURN temp_name_str;
1432 END pqp_enddate_delete_veh;
1433 -- end function
1434 -----------------------------------------------------------------------------
1435 -----------------------Get the legistionId for BusinessGroupId---------------
1436 -----------------------------------------------------------------------------
1437 FUNCTION get_legislation_code
1438 (p_business_group_id IN NUMBER
1439 ) RETURN VARCHAR2 IS
1440 --declare local variables
1441 l_legislation_code per_business_groups.legislation_code%TYPE;
1442
1443 CURSOR c_get_leg_code IS
1444 SELECT legislation_code
1445 FROM per_business_groups_perf
1446 WHERE business_group_id =p_business_group_id;
1447
1448 BEGIN
1449 OPEN c_get_leg_code;
1450 LOOP
1451 FETCH c_get_leg_code INTO l_legislation_code;
1452 EXIT WHEN c_get_leg_code%NOTFOUND;
1453 END LOOP;
1454 CLOSE c_get_leg_code;
1455 RETURN (l_legislation_code);
1456 EXCEPTION
1457 ---------
1458 WHEN OTHERS THEN
1459 RETURN(NULL);
1460 END;
1461 -- ----------------------------------------------------------------------------
1462 -- |---------------------------< insert_validate >----------------------------|
1463 -- ----------------------------------------------------------------------------
1464 PROCEDURE insert_validate
1465 (p_rec IN pqp_vre_shd.g_rec_type
1466 ,p_effective_date IN DATE
1467 ,p_datetrack_mode IN VARCHAR2
1468 ,p_validation_start_date IN DATE
1469 ,p_validation_end_date IN DATE
1470 ) is
1471
1472 --
1473 l_proc varchar2(72) := g_package||'insert_validate';
1474 l_return_status NUMBER ;
1475 l_message VARCHAR2(2500) ;
1476 l_currency_code pqp_vehicle_repository_f.currency_code%TYPE;
1477 l_share_conf_value pqp_vehicle_repository_f.shared_vehicle%TYPE;
1478 l_legislation_code varchar2(150);
1479 --
1480
1481 BEGIN
1482 hr_utility.set_location('Entering:'||l_proc, 5);
1483
1484 --
1485 -- Call all supporting business operations
1486 --
1487 hr_api.validate_bus_grp_id
1488 (p_business_group_id => p_rec.business_group_id
1489 ,p_associated_column1 => pqp_vre_shd.g_tab_nam
1490 || '.BUSINESS_GROUP_ID');
1491
1492 --Checking the unique regNumber
1493 chk_unique_regnum
1494 (p_rec =>p_rec
1495 ,p_effective_date =>p_effective_date
1496 ,p_datetrack_mode =>p_datetrack_mode
1497 ,p_validation_start_date =>p_validation_start_date
1498 ,p_validation_end_date =>p_validation_end_date
1499 );
1500
1501 --Checking the unique Iden Number
1502 --Fix #3693656
1503 IF p_rec.vehicle_id_number is not null THEN
1504 chk_unique_idennum
1505 (p_rec =>p_rec
1506 ,p_effective_date =>p_effective_date
1507 ,p_datetrack_mode =>p_datetrack_mode
1508 ,p_validation_start_date =>p_validation_start_date
1509 ,p_validation_end_date =>p_validation_end_date
1510 ,p_update_flag =>'N'
1511 );
1512 END IF;
1513
1514
1515 --Getting the legislationId for business groupId
1516 l_legislation_code :=
1517 get_legislation_code(p_rec.business_group_id);
1518
1519 --Added by sshetty as the registration number is
1520 --non mandatory for global company vehicles
1521 --but mandatory for UK leg
1522 --for both company and private vehicles.
1523
1524 --Global requirement to make Model mandatory.
1525 l_return_status := chk_mandatory(
1526 p_argument =>'Model'
1527 ,p_argument_value =>p_rec.registration_number
1528 ,p_message =>l_message);
1529
1530 IF l_return_status = -1 THEN
1531 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1532 fnd_message.set_token('FEILD','Model');
1533 fnd_message.raise_error;
1534 END IF;
1535 -- Added by gattu for phase 2
1536 IF p_rec.vehicle_ownership in ('C','PL_LEC','PL_LC') THEN
1537 --Added to check the listprice is mandatory for Irish leg
1538 IF l_legislation_code in ('IE','GB','PL') THEN
1539 l_return_status := chk_mandatory(
1540 p_argument =>'ListPrice'
1541 ,p_argument_value =>p_rec.list_price
1542 ,p_message =>l_message);
1543
1544 IF l_return_status = -1 THEN
1545 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1546 fnd_message.set_token('FEILD','List Price');
1547 fnd_message.raise_error;
1548 END IF;
1549 END IF;
1550
1551 --Taxation Method is Mandatory for German Leg and should have values
1552 --Flate rate and Mileage Book
1553 IF l_legislation_code = 'DE' THEN
1554 l_return_status := chk_mandatory(
1555 p_argument =>'Taxation Method'
1556 ,p_argument_value =>p_rec.taxation_method
1557 ,p_message =>l_message);
1558
1559 IF l_return_status = -1 THEN
1560 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1561 fnd_message.set_token('FEILD','Taxation Method');
1562 fnd_message.raise_error;
1563 END IF;
1564 --If taxation method value exist then check for value validation for german
1565 l_return_status := chk_lookup(
1566 p_vehicle_repository_id => p_rec.vehicle_repository_id
1567 ,p_lookup_type =>'PQP_VEHICLE_TAXATION_METHOD'
1568 ,p_lookup_code => p_rec.taxation_method
1569 ,p_effective_date => p_effective_date
1570 ,p_validation_start_date => p_validation_start_date
1571 ,p_validation_end_date => p_validation_end_date);
1572
1573 IF l_return_status = -1 THEN
1574 fnd_message.set_name('PQP','PQP_230114_VLD_TAXATION_CDE');
1575 fnd_message.raise_error;
1576 END IF;
1577 END IF;
1578 END IF;
1579
1580 --Checking vehicle_ownership Mandatory
1581 l_return_status := chk_mandatory(
1582 p_argument =>'Vehicle Ownership'
1583 ,p_argument_value => p_rec.vehicle_ownership
1584 ,p_message => l_message);
1585
1586 IF l_return_status = -1 THEN
1587 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1588 fnd_message.set_token('FEILD','Vehicle Ownership');
1589 fnd_message.raise_error;
1590 END IF;
1591
1592 --Checking Vehicle Type Mandatory
1593 l_return_status := chk_mandatory(
1594 p_argument =>'vehicle_type'
1595 ,p_argument_value => p_rec.vehicle_type
1596 ,p_message => l_message);
1597 IF l_return_status = -1 THEN
1598 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1599 fnd_message.set_token('FEILD','Vehicle Type');
1600 fnd_message.raise_error;
1601 END IF;
1602
1603
1604 --Checking Registration Number Mandatory
1605 --Added by sshetty as the registration number is
1606 --non mandatory for global company vehicles
1607 --but mandatory for UK leg
1608 --for both company and private vehicles.
1609 IF l_legislation_code = 'GB' THEN
1610 l_return_status := chk_mandatory(
1611 p_argument =>'Registration Number'
1612 ,p_argument_value =>p_rec.registration_number
1613 ,p_message =>l_message);
1614
1615 IF l_return_status = -1 THEN
1616 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1617 fnd_message.set_token('FEILD','Registration Number');
1618 fnd_message.raise_error;
1619 END IF;
1620 END IF;
1621
1622 --Checking Make Mandatory
1623 l_return_status := chk_mandatory(
1624 p_argument =>'make'
1625 ,p_argument_value => p_rec.make
1626 ,p_message => l_message);
1627 IF l_return_status = -1 THEN
1628 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1629 fnd_message.set_token('FEILD','Make');
1630 fnd_message.raise_error;
1631 END IF;
1632
1633 --Checking Model Mandatory
1634 l_return_status := chk_mandatory(
1635 p_argument =>'Model'
1636 ,p_argument_value => p_rec.model
1637 ,p_message => l_message);
1638 IF l_return_status = -1 THEN
1639 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1640 fnd_message.set_token('FEILD','Model');
1641 fnd_message.raise_error;
1642 END IF;
1643
1644 --Checking EngineCapacity Mandatory
1645 IF l_legislation_code = 'GB' OR l_legislation_code = 'PL' THEN
1646 l_return_status := chk_mandatory(
1647 p_argument =>'Engine Capacity'
1648 ,p_argument_value => p_rec.engine_capacity_in_cc
1649 ,p_message => l_message);
1650 IF l_return_status = -1 THEN
1651 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1652 fnd_message.set_token('FEILD','Engine Capacity');
1653 fnd_message.raise_error;
1654 END IF;
1655
1656 --Checking Fueltype Mandatory
1657 l_return_status := chk_mandatory(
1658 p_argument =>'fuelType'
1659 ,p_argument_value => p_rec.fuel_type
1660 ,p_message => l_message);
1661 IF l_return_status = -1 THEN
1662 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1663 fnd_message.set_token('FEILD','Fuel Type');
1664 fnd_message.raise_error;
1665 END IF;
1666 END IF;
1667 --Checking vehicleStatus Mandatory
1668 l_return_status := chk_mandatory(
1669 p_argument =>'VehicleStatus'
1670 ,p_argument_value => p_rec.vehicle_status
1671 ,p_message => l_message);
1672 IF l_return_status = -1 THEN
1673 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1674 fnd_message.set_token('FEILD','Vehicle Status');
1675 fnd_message.raise_error;
1676 END IF;
1677
1678
1679 --Checking Ownership lookup validation
1680 l_return_status := chk_lookup(
1681 p_vehicle_repository_id => p_rec.vehicle_repository_id
1682 ,p_lookup_type =>'PQP_VEHICLE_OWNERSHIP_TYPE'
1683 ,p_lookup_code => p_rec.vehicle_ownership
1684 ,p_effective_date => p_effective_date
1685 ,p_validation_start_date => p_validation_start_date
1686 ,p_validation_end_date => p_validation_end_date);
1687
1688 IF l_return_status = -1 THEN
1689 fnd_message.set_name('PQP','PQP_230741_VLD_OWNRSHP_CDE');
1690 fnd_message.raise_error;
1691 END IF;
1692
1693 --Checking vehicle_status lookup validation
1694 l_return_status := chk_lookup(
1695 p_vehicle_repository_id => p_rec.vehicle_repository_id
1696 ,p_lookup_type =>'PQP_VEHICLE_STATUS'
1697 ,p_lookup_code => p_rec.vehicle_status
1698 ,p_effective_date => p_effective_date
1699 ,p_validation_start_date => p_validation_start_date
1700 ,p_validation_end_date => p_validation_end_date);
1701
1702 IF l_return_status = -1 THEN
1703 fnd_message.set_name('PQP','PQP_230742_VLD_STATUS_CDE');
1704 fnd_message.raise_error;
1705 END IF;
1706
1707 --Checking Fuel Type lookup validation
1708 l_return_status := chk_lookup(
1709 p_vehicle_repository_id => p_rec.vehicle_repository_id
1710 ,p_lookup_type =>'PQP_FUEL_TYPE'
1711 ,p_lookup_code => p_rec.fuel_type
1712 ,p_effective_date => p_effective_date
1713 ,p_validation_start_date => p_validation_start_date
1714 ,p_validation_end_date => p_validation_end_date);
1715 IF l_return_status = -1 THEN
1716 fnd_message.set_name('PQP','PQP_230743_VLD_FUEL_TYP');
1717 fnd_message.raise_error;
1718 END IF;
1719
1720 --Checking Vehicle Type lookup validation
1721 l_return_status := chk_lookup(
1722 p_vehicle_repository_id => p_rec.vehicle_repository_id
1723 ,p_lookup_type =>'PQP_VEHICLE_TYPE'
1724 ,p_lookup_code => p_rec.vehicle_type
1725 ,p_effective_date => p_effective_date
1726 ,p_validation_start_date => p_validation_start_date
1727 ,p_validation_end_date => p_validation_end_date);
1728 IF l_return_status = -1 THEN
1729 fnd_message.set_name('PQP','PQP_230744_VLD_VEH_TYP');
1730 fnd_message.raise_error;
1731 END IF;
1732
1733 --Checking If Vehicle Status is Inactive then inactive reason with lookup
1734 IF p_rec.vehicle_status = 'I' THEN
1735 --Checking Vehicle reason lookup validation
1736 l_return_status := chk_lookup(
1737 p_vehicle_repository_id=> p_rec.vehicle_repository_id
1738 ,p_lookup_type =>'PQP_VEHICLE_INACTIVE_REASONS'
1739 ,p_lookup_code => p_rec.vehicle_inactivity_reason
1740 ,p_effective_date => p_effective_date
1741 ,p_validation_start_date=> p_validation_start_date
1742 ,p_validation_end_date => p_validation_end_date);
1743 IF l_return_status = -1 THEN
1744 fnd_message.set_name('PQP','PQP_230852_VEH_INACTIVE_REASON');
1745 fnd_message.raise_error;
1746 END IF;
1747 ELSE
1748 --If vehicle status is active then Inactive Reason should be NULL
1749 IF p_rec.vehicle_inactivity_reason IS NOT NULL THEN
1750 fnd_message.set_name('PQP','PQP_230853_INACTIVE_REASON_ERR');
1751 fnd_message.raise_error;
1752 END IF;
1753 END IF;
1754
1755 --Checking the mandatory fields for company vehicle
1756 IF p_rec.vehicle_ownership in ('C','PL_LEC','PL_LC') THEN
1757 --Check for initial_registration
1758 l_return_status := chk_mandatory(
1759 p_argument =>'Registration Number'
1760 ,p_argument_value =>p_rec.initial_registration
1761 ,p_message =>l_message);
1762
1763 IF l_return_status = -1 THEN
1764 fnd_message.set_name('PQP', 'PQP_230738_COMP_OWNR_MNDTRY');
1765 fnd_message.set_token('TOKEN','Registration Number');
1766 fnd_message.raise_error;
1767 END IF;
1768 --Check for list_price
1769 END IF;
1770
1771
1772 --Getting the cmy veh Share Across Emp value from configuration table
1773 IF p_rec.vehicle_ownership in ('C','PL_LEC','PL_LC') THEN
1774 l_share_conf_value := PQP_GET_CONFIG_VALUE(
1775 p_business_group_id =>p_rec.business_group_id,
1776 p_legislation_code =>l_legislation_code,
1777 p_seg_col_name =>'ShareCmyCar',
1778 p_table_name =>'p_table_name',
1779 p_information_category=>'PQP_VEHICLE_MILEAGE');
1780
1781 hr_utility.set_location('Config cmy veh share val:'||l_share_conf_value,40);
1782
1783 --If configuration value is 'N' then user shouldnot select the checkbox
1784 --If user selects then raise error
1785 IF l_share_conf_value = 'N' THEN
1786 IF p_rec.shared_vehicle = 'Y' THEN
1787 fnd_message.set_name('PQP','PQP_230720_COMP_CAR_NT_SHARED');
1788 fnd_message.raise_error;
1789 END IF;
1790 END IF;
1791
1792 ELSE
1793 l_share_conf_value := PQP_GET_CONFIG_VALUE(
1794 p_business_group_id => p_rec.business_group_id,
1795 p_legislation_code => l_legislation_code,
1796 p_seg_col_name => 'SharePriCar',
1797 p_table_name => 'p_table_name',
1798 p_information_category =>'PQP_VEHICLE_MILEAGE');
1799
1800 hr_utility.set_location('Config pri veh share val:'||l_share_conf_value,40);
1801
1802 IF l_share_conf_value = 'N' THEN
1803 IF p_rec.shared_vehicle = 'Y' THEN
1804 fnd_message.set_name('PQP', 'PQP_230721_PVT_CAR_NT_SHARED');
1805 fnd_message.raise_error;
1806 END IF;
1807 END IF;
1808 END IF;
1809 --handling multiple messages
1810 --catching all errors and adding to multi message package.
1811 EXCEPTION
1812 WHEN app_exception.application_exception THEN
1813 IF hr_multi_message.exception_add
1814 (p_same_associated_columns => 'Y') THEN
1815 RAISE;
1816 END IF;
1817 -- After validating the set of important attributes
1818 -- if Multiple Message detection is enabled and at least
1819 -- one error has been found then abort further validation.
1820 hr_multi_message.end_validation_set;
1821 --
1822 -- Validate Dependent Attributes
1823 --
1824 --
1825 pqp_vre_bus.chk_ddf(p_rec);
1826 --
1827 pqp_vre_bus.chk_df(p_rec);
1828 --
1829 hr_utility.set_location(' Leaving:'||l_proc, 10);
1830 End insert_validate;
1831 --
1832 -- ----------------------------------------------------------------------------
1833 -- |---------------------------< update_validate >----------------------------|
1834 -- ----------------------------------------------------------------------------
1835 PROCEDURE update_validate
1836 (p_rec in pqp_vre_shd.g_rec_type
1837 ,p_effective_date in date
1838 ,p_datetrack_mode in varchar2
1839 ,p_validation_start_date in date
1840 ,p_validation_end_date in date
1841 ) IS
1842 --
1843 l_proc VARCHAR2(72) := g_package||'update_validate';
1844 l_return_status NUMBER ;
1845 l_message VARCHAR2(2500) ;
1846 l_currency_code pqp_vehicle_repository_f.currency_code%TYPE;
1847 l_legislation_code varchar2(150);
1848 l_return_message varchar2(2000);
1849
1850 --
1851 BEGIN
1852 hr_utility.set_location('Entering:'||l_proc, 5);
1853 --
1854 -- Call all supporting business operations
1855 --
1856
1857
1858 hr_api.validate_bus_grp_id
1859 (p_business_group_id => p_rec.business_group_id
1860 ,p_associated_column1 => pqp_vre_shd.g_tab_nam
1861 || '.BUSINESS_GROUP_ID');
1862
1863
1864 dt_update_validate
1865 (p_datetrack_mode => p_datetrack_mode
1866 ,p_validation_start_date => p_validation_start_date
1867 ,p_validation_end_date => p_validation_end_date
1868 );
1869
1870
1871 chk_non_updateable_args
1872 (p_effective_date => p_effective_date
1873 ,p_rec => p_rec
1874 );
1875
1876
1877 --cheking the regnumber change at update
1878 validate_regnum
1879 (p_rec =>p_rec
1880 ,p_effective_date =>p_effective_date
1881 ,p_datetrack_mode =>p_datetrack_mode
1882 ,p_validation_start_date =>p_validation_start_date
1883 ,p_validation_end_date =>p_validation_end_date
1884 );
1885
1886 --Checking the unique Iden Number
1887 --First check null ot not null
1888 --If not null ,then check if there is any change in update
1889 --If change then check if there is already exist
1890 --Fix #3693656
1891 IF p_rec.vehicle_id_number is not null THEN
1892 chk_unique_idennum
1893 (p_rec =>p_rec
1894 ,p_effective_date =>p_effective_date
1895 ,p_datetrack_mode =>p_datetrack_mode
1896 ,p_validation_start_date =>p_validation_start_date
1897 ,p_validation_end_date =>p_validation_end_date
1898 ,p_update_flag =>'Y'
1899 );
1900 END IF;
1901
1902
1903 --Getting the legislationId for business groupId
1904 l_legislation_code :=
1905 get_legislation_code(p_rec.business_group_id);
1906
1907 --Added by gattu for phase 2
1908 IF p_rec.vehicle_ownership in ('C','PL_LEC','PL_LC') THEN
1909 --Added to check the listprice is mandatory for Irish leg
1910 IF l_legislation_code = 'IE' THEN
1911 l_return_status := chk_mandatory(
1912 p_argument =>'ListPrice'
1913 ,p_argument_value =>p_rec.list_price
1914 ,p_message =>l_message);
1915
1916 IF l_return_status = -1 THEN
1917 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1918 fnd_message.set_token('FEILD','List Price');
1919 fnd_message.raise_error;
1920 END IF;
1921 END IF;
1922
1923 --Taxation Method is Mandatory for German Leg and should have values
1924 --Flate rate and Mileage Book
1925 IF l_legislation_code = 'DE' THEN
1926 l_return_status := chk_mandatory(
1927 p_argument =>'Taxation Method'
1928 ,p_argument_value =>p_rec.taxation_method
1929 ,p_message =>l_message);
1930
1931 IF l_return_status = -1 THEN
1932 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1933 fnd_message.set_token('FEILD','Taxation Method');
1934 fnd_message.raise_error;
1935 END IF;
1936 --If taxation method value exist then check for value validation for german
1937 l_return_status := chk_lookup(
1938 p_vehicle_repository_id => p_rec.vehicle_repository_id
1939 ,p_lookup_type =>'PQP_VEHICLE_TAXATION_METHOD'
1940 ,p_lookup_code => p_rec.taxation_method
1941 ,p_effective_date => p_effective_date
1942 ,p_validation_start_date => p_validation_start_date
1943 ,p_validation_end_date => p_validation_end_date);
1944
1945 IF l_return_status = -1 THEN
1946 fnd_message.set_name('PQP','PQP_230114_VLD_TAXATION_CDE');
1947 fnd_message.raise_error;
1948 END IF;
1949 END IF;
1950 END IF;
1951
1952
1953 /* This check is removed from API....and calling from UI.
1954 Because this is warning not a error.
1955 --Checking the Vehicle Status change
1956 l_return_status := pqp_check_veh_status
1957 (p_vehicle_repository_id =>p_rec.vehicle_repository_id
1958 ,p_business_group_id =>p_rec.business_group_id
1959 ,p_vehicle_status => p_rec.vehicle_status
1960 ,p_effective_date =>p_effective_date
1961 ,p_message =>l_message
1962 );
1963
1964 IF l_return_status = -1 THEN
1965 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1966 fnd_message.set_token('PROCEDURE',l_message);
1967 fnd_message.raise_error;
1968 END IF;
1969 */
1970
1971 --Checking the Ownership Change validation
1972 l_return_status := pqp_check_ownership_change
1973 (p_rec =>p_rec
1974 ,p_effective_date =>p_effective_date
1975 ,p_message => l_message
1976 );
1977 IF l_return_status = -1 THEN
1978 -- added gattu to fix 3448070
1979 l_return_message := pqp_purge_delete_veh
1980 (p_rec =>p_rec
1981 ,p_effective_date =>p_effective_date
1982 ,p_message =>l_message
1983 );
1984 fnd_message.set_name('PQP', 'PQP_230731_OWNRSHP_CHG_RSTRICT');
1985 fnd_message.set_token('NAME',l_return_message);
1986 fnd_message.raise_error;
1987 END IF;
1988
1989 --Checking vehicle_ownership Mandatory
1990 l_return_status := chk_mandatory(
1991 p_argument =>'OwnerShip'
1992 ,p_argument_value => p_rec.vehicle_ownership
1993 ,p_message => l_message);
1994
1995 IF l_return_status = -1 THEN
1996 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
1997 fnd_message.set_token('FEILD','OwnerShip');
1998 fnd_message.raise_error;
1999 END IF;
2000
2001 --Checking Vehicle Type Mandatory
2002 l_return_status := chk_mandatory(
2003 p_argument =>'vehicle_type'
2004 ,p_argument_value => p_rec.vehicle_type
2005 ,p_message => l_message);
2006 IF l_return_status = -1 THEN
2007 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2008 fnd_message.set_token('FEILD','Vehicle Type');
2009 fnd_message.raise_error;
2010 END IF;
2011
2012
2013 --Checking Registration Number Mandatory
2014 IF l_legislation_code ='GB' OR l_legislation_code ='PL' THEN
2015 l_return_status := chk_mandatory(
2016 p_argument =>'registration_number'
2017 ,p_argument_value => p_rec.registration_number
2018 ,p_message => l_message);
2019 IF l_return_status = -1 THEN
2020 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2021 fnd_message.set_token('FEILD','Registration Number');
2022 fnd_message.raise_error;
2023 END IF;
2024 END IF;
2025
2026 --Checking Make Mandatory
2027 l_return_status := chk_mandatory(
2028 p_argument =>'make'
2029 ,p_argument_value => p_rec.make
2030 ,p_message => l_message);
2031 IF l_return_status = -1 THEN
2032 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2033 fnd_message.set_token('FEILD','Make');
2034 fnd_message.raise_error;
2035 END IF;
2036
2037 --Checking Model Mandatory
2038 l_return_status := chk_mandatory(
2039 p_argument =>'Model'
2040 ,p_argument_value => p_rec.model
2041 ,p_message => l_message);
2042 IF l_return_status = -1 THEN
2043 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2044 fnd_message.set_token('FEILD','Model');
2045 fnd_message.raise_error;
2046 END IF;
2047
2048 --Checking EngineCapacity Mandatory
2049 --Not required now to be mandatory as this is non mandatory
2050 --for global module and this is mandatory only for GB.
2051 IF l_legislation_code ='GB' OR l_legislation_code ='PL'THEN
2052 l_return_status := chk_mandatory(
2053 p_argument =>'Engine Capacity'
2054 ,p_argument_value => p_rec.engine_capacity_in_cc
2055 ,p_message => l_message);
2056 IF l_return_status = -1 THEN
2057 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2058 fnd_message.set_token('FEILD','Engine Capacity');
2059 fnd_message.raise_error;
2060 END IF;
2061 END IF;
2062 --Checking Fueltype Mandatory
2063 l_return_status := chk_mandatory(
2064 p_argument =>'fuelType'
2065 ,p_argument_value => p_rec.fuel_type
2066 ,p_message => l_message);
2067 IF l_return_status = -1 THEN
2068 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2069 fnd_message.set_token('FEILD','Fuel Type');
2070 fnd_message.raise_error;
2071 END IF;
2072
2073 --Checking vehicleStatus Mandatory
2074 l_return_status := chk_mandatory(
2075 p_argument =>'VehicleStatus'
2076 ,p_argument_value => p_rec.vehicle_status
2077 ,p_message => l_message);
2078 IF l_return_status = -1 THEN
2079 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2080 fnd_message.set_token('FEILD','Vehicle Status');
2081 fnd_message.raise_error;
2082 END IF;
2083
2084
2085 --Checking for value change
2086 IF ( nvl(pqp_vre_shd.g_old_rec.vehicle_status,hr_api.g_varchar2)
2087 <> nvl(p_rec.vehicle_status,hr_api.g_varchar2) ) THEN
2088
2089 --Checking If Vehicle Status is Inactive then inactive reason is mandatory
2090 --and should check with lookup
2091 IF p_rec.vehicle_status = 'I' THEN
2092 --Checking Vehicle Type lookup validation
2093 l_return_status := chk_lookup(
2094 p_vehicle_repository_id=> p_rec.vehicle_repository_id
2095 ,p_lookup_type =>'PQP_VEHICLE_INACTIVE_REASONS'
2096 ,p_lookup_code => p_rec.vehicle_inactivity_reason
2097 ,p_effective_date => p_effective_date
2098 ,p_validation_start_date=> p_validation_start_date
2099 ,p_validation_end_date => p_validation_end_date);
2100 IF l_return_status = -1 THEN
2101 fnd_message.set_name('PQP','PQP_230852_VEH_INACTIVE_REASON');
2102 fnd_message.raise_error;
2103 END IF;
2104 ELSE
2105 --If vehicle status is active then Inactive Reason should be NULL
2106 IF p_rec.vehicle_inactivity_reason IS NOT NULL THEN
2107 fnd_message.set_name('PQP','PQP_230853_INACTIVE_REASON_ERR');
2108 fnd_message.raise_error;
2109 END IF;
2110
2111 END IF;
2112 END IF;
2113
2114 --Checking for value change
2115 IF ( nvl(pqp_vre_shd.g_old_rec.vehicle_ownership,hr_api.g_varchar2)
2116 <> nvl(p_rec.vehicle_ownership,hr_api.g_varchar2) ) THEN
2117 --If not equal then Checking Ownership lookup validation
2118 l_return_status := chk_lookup(
2119 p_vehicle_repository_id => p_rec.vehicle_repository_id
2120 ,p_lookup_type =>'PQP_VEHICLE_OWNERSHIP_TYPE'
2121 ,p_lookup_code => p_rec.vehicle_ownership
2122 ,p_effective_date => p_effective_date
2123 ,p_validation_start_date => p_validation_start_date
2124 ,p_validation_end_date => p_validation_end_date);
2125
2126 IF l_return_status = -1 THEN
2127 fnd_message.set_name('PQP','PQP_230741_VLD_OWNRSHP_CDE');
2128 fnd_message.raise_error;
2129 END IF;
2130 END IF;
2131
2132 --Checking for value change
2133 IF ( nvl(pqp_vre_shd.g_old_rec.vehicle_status,hr_api.g_varchar2)
2134 <> nvl(p_rec.vehicle_status,hr_api.g_varchar2) ) THEN
2135 --If not equal then Checking vehicle_status lookup validation
2136 l_return_status := chk_lookup(
2137 p_vehicle_repository_id => p_rec.vehicle_repository_id
2138 ,p_lookup_type =>'PQP_VEHICLE_STATUS'
2139 ,p_lookup_code => p_rec.vehicle_status
2140 ,p_effective_date => p_effective_date
2141 ,p_validation_start_date => p_validation_start_date
2142 ,p_validation_end_date => p_validation_end_date);
2143
2144 IF l_return_status = -1 THEN
2145 fnd_message.set_name('PQP','PQP_230742_VLD_STATUS_CDE');
2146 fnd_message.raise_error;
2147 END IF;
2148 END IF;
2149
2150 --Checking for value change
2151 IF ( nvl(pqp_vre_shd.g_old_rec.fuel_type,hr_api.g_varchar2)
2152 <> nvl(p_rec.fuel_type,hr_api.g_varchar2) ) THEN
2153 --Checking Fuel Type lookup validation
2154 l_return_status := chk_lookup(
2155 p_vehicle_repository_id => p_rec.vehicle_repository_id
2156 ,p_lookup_type =>'PQP_FUEL_TYPE'
2157 ,p_lookup_code => p_rec.fuel_type
2158 ,p_effective_date => p_effective_date
2159 ,p_validation_start_date => p_validation_start_date
2160 ,p_validation_end_date => p_validation_end_date);
2161 IF l_return_status = -1 THEN
2162 fnd_message.set_name('PQP','PQP_230743_VLD_FUEL_TYP');
2163 fnd_message.raise_error;
2164 END IF;
2165 END IF;
2166
2167 --Checking for value change
2168 IF ( nvl(pqp_vre_shd.g_old_rec.vehicle_type,hr_api.g_varchar2)
2169 <> nvl(p_rec.vehicle_type,hr_api.g_varchar2) ) THEN
2170 --Checking Vehicle Type lookup validation
2171 l_return_status := chk_lookup(
2172 p_vehicle_repository_id => p_rec.vehicle_repository_id
2173 ,p_lookup_type =>'PQP_VEHICLE_TYPE'
2174 ,p_lookup_code => p_rec.vehicle_type
2175 ,p_effective_date => p_effective_date
2176 ,p_validation_start_date => p_validation_start_date
2177 ,p_validation_end_date => p_validation_end_date);
2178 IF l_return_status = -1 THEN
2179 fnd_message.set_name('PQP','PQP_230744_VLD_VEH_TYP');
2180 fnd_message.raise_error;
2181 END IF;
2182 END IF;
2183
2184
2185 --Checking the mandatory fields for company vehicle
2186 IF p_rec.vehicle_ownership in ('C','PL_LEC','PL_LC') THEN
2187 --Check for initial_registration
2188 l_return_status := chk_mandatory(
2189 p_argument =>'Intial Registration Number'
2190 ,p_argument_value => p_rec.initial_registration
2191 ,p_message => l_message);
2192
2193 IF l_return_status = -1 THEN
2194 fnd_message.set_name('PQP', 'PQP_230738_COMP_OWNR_MNDTRY');
2195 fnd_message.set_token('FEILD','Initial Registration');
2196 fnd_message.raise_error;
2197 END IF;
2198 --Check for list_price
2199 l_return_status := chk_mandatory(
2200 p_argument =>'ListPrice'
2201 ,p_argument_value => p_rec.list_price
2202 ,p_message => l_message);
2203
2204 IF l_return_status = -1 THEN
2205 fnd_message.set_name('PQP', 'PQP_230738_COMP_OWNR_MNDTRY');
2206 fnd_message.set_token('FEILD','List Price');
2207 fnd_message.raise_error;
2208 END IF;
2209 END IF;
2210
2211
2212 -- Share Across Employees Field updation check
2213 l_return_status := pqp_check_shared_veh
2214 (p_rec =>p_rec
2215 ,p_effective_date =>p_effective_date
2216 ,p_message =>l_message
2217 );
2218 IF l_return_status = -1 THEN
2219 fnd_message.set_name('PQP', 'PQP_230758_SHARE_EMP_CHG');
2220 fnd_message.raise_error;
2221 END IF;
2222 EXCEPTION
2223 WHEN app_exception.application_exception THEN
2224 IF hr_multi_message.exception_add
2225 (
2226 p_same_associated_columns => 'Y'
2227 ) THEN
2228 RAISE;
2229 END IF;
2230
2231 --
2232 -- After validating the set of important attributes,
2233 -- if Multiple Message detection is enabled and at least
2234 -- one error has been found then abort further validation.
2235 --
2236 hr_multi_message.end_validation_set;
2237 --
2238 -- Validate Dependent Attributes
2239 --
2240 -- Call the datetrack update integrity operation
2241 --
2242
2243 pqp_vre_bus.chk_ddf(p_rec);
2244 --
2245 pqp_vre_bus.chk_df(p_rec);
2246 --
2247 hr_utility.set_location(' Leaving:'||l_proc, 10);
2248 End update_validate;
2249 --
2250 -- ----------------------------------------------------------------------------
2251 -- |---------------------------< delete_validate >----------------------------|
2252 -- ----------------------------------------------------------------------------
2253 PROCEDURE delete_validate
2254 (p_rec IN pqp_vre_shd.g_rec_type
2255 ,p_effective_date IN DATE
2256 ,p_datetrack_mode IN VARCHAR2
2257 ,p_validation_start_date IN DATE
2258 ,p_validation_end_date IN DATE
2259 ) IS
2260 --
2261 l_proc varchar2(72) := g_package||'delete_validate';
2262 l_validation_start_date date;
2263 l_validation_end_date date;
2264 l_return_status NUMBER ;
2265 l_message VARCHAR2(2500) ;
2266 l_return_message varchar2(2000);
2267 --
2268 BEGIN
2269 hr_utility.set_location('Entering:'||l_proc, 5);
2270 --
2271 -- Call all supporting business operations
2272 --
2273
2274 dt_delete_validate
2275 (p_datetrack_mode => p_datetrack_mode
2276 ,p_validation_start_date => p_validation_start_date
2277 ,p_validation_end_date => p_validation_end_date
2278 ,p_vehicle_repository_id => p_rec.vehicle_repository_id
2279 );
2280
2281
2282 --Checking the vehicle availability before delete or purge.
2283 IF p_datetrack_mode = 'ZAP' THEN
2284
2285 --This is for purge
2286 l_return_message := pqp_purge_delete_veh
2287 (p_rec =>p_rec
2288 ,p_effective_date =>p_effective_date
2289 ,p_message =>l_message
2290 );
2291
2292 hr_utility.set_location('Veh purge Delete Status :'||l_return_message,50);
2293
2294 IF l_return_message IS NOT NULL THEN
2295 fnd_message.set_name('PQP', 'PQP_230730_VEH_DEL_RSTRICT');
2296 fnd_message.set_token('NAME',l_return_message);
2297 fnd_message.raise_error;
2298 END IF;
2299 ELSIF p_datetrack_mode = 'DELETE' THEN
2300 --This is for enddate
2301 l_return_message := pqp_enddate_delete_veh
2302 ( p_rec =>p_rec
2303 ,p_effective_date =>p_effective_date
2304 ,p_message =>l_message
2305 );
2306
2307 hr_utility.set_location('Veh enddate Delete Status :'||l_return_message,55);
2308
2309 IF l_return_message IS NOT NULL THEN
2310 fnd_message.set_name('PQP', 'PQP_230729_VEH_ENDDT_RSTRICT');
2311 fnd_message.set_token('NAME',l_return_message);
2312 fnd_message.raise_error;
2313 END IF;
2314
2315 END IF;
2316
2317 hr_utility.set_location(' Leaving:'||l_proc, 10);
2318 --handling multiple messages
2319 --catching all errors and adding to multi message package.
2320 Exception
2321 when app_exception.application_exception then
2322 IF hr_multi_message.exception_add
2323 (p_same_associated_columns => 'Y') THEN
2324 RAISE;
2325 END IF;
2326 -- After validating the set of important attributes
2327 -- if Multiple Message detection is enabled and at least
2328 -- one error has been found then abort further validation.
2329 hr_multi_message.end_validation_set;
2330 End delete_validate;
2331 --
2332 end pqp_vre_bus;
[Home] [Help]