1 Package Body pqp_pvd_bus as
2 /* $Header: pqpvdrhi.pkb 115.6 2003/02/17 22:14:43 tmehra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqp_pvd_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_details_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_vehicle_details_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , pqp_vehicle_details pvd
30 where pvd.vehicle_details_id = p_vehicle_details_id
31 and pbg.business_group_id = pvd.business_group_id;
35 l_security_group_id number;
32 --
33 -- Declare local variables
34 --
36 l_proc varchar2(72) := g_package||'set_security_group_id';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Ensure that all the mandatory parameter are not null
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'vehicle_details_id'
47 ,p_argument_value => p_vehicle_details_id
48 );
49 --
50 open csr_sec_grp;
51 fetch csr_sec_grp into l_security_group_id;
52 --
53 if csr_sec_grp%notfound then
54 --
55 close csr_sec_grp;
56 --
57 -- The primary key is invalid therefore we must error
58 --
59 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
60 fnd_message.raise_error;
61 --
62 end if;
63 close csr_sec_grp;
64 --
65 -- Set the security_group_id in CLIENT_INFO
66 --
67 hr_api.set_security_group_id
68 (p_security_group_id => l_security_group_id
69 );
70 --
71 hr_utility.set_location(' Leaving:'|| l_proc, 20);
72 --
73 end set_security_group_id;
74 --
75 -- ---------------------------------------------------------------------------
76 -- |---------------------< return_legislation_code >-------------------------|
77 -- ---------------------------------------------------------------------------
78 --
79 Function return_legislation_code
80 (p_vehicle_details_id in number
81 )
82 Return Varchar2 Is
83 --
84 -- Declare cursor
85 --
86 cursor csr_leg_code is
87 select pbg.legislation_code
88 from per_business_groups pbg
89 , pqp_vehicle_details pvd
90 where pvd.vehicle_details_id = p_vehicle_details_id
91 and pbg.business_group_id (+) = pvd.business_group_id;
92 --
93 -- Declare local variables
94 --
95 l_legislation_code varchar2(150);
96 l_proc varchar2(72) := g_package||'return_legislation_code';
97 --
98 Begin
99 --
100 hr_utility.set_location('Entering:'|| l_proc, 10);
101 --
102 -- Ensure that all the mandatory parameter are not null
103 --
104 hr_api.mandatory_arg_error
105 (p_api_name => l_proc
106 ,p_argument => 'vehicle_details_id'
107 ,p_argument_value => p_vehicle_details_id
108 );
109 --
110 if ( nvl(pqp_pvd_bus.g_vehicle_details_id, hr_api.g_number)
111 = p_vehicle_details_id) then
112 --
113 -- The legislation code has already been found with a previous
114 -- call to this function. Just return the value in the global
115 -- variable.
116 --
117 l_legislation_code := pqp_pvd_bus.g_legislation_code;
118 hr_utility.set_location(l_proc, 20);
119 else
120 --
121 -- The ID is different to the last call to this function
122 -- or this is the first call to this function.
123 --
124 open csr_leg_code;
125 fetch csr_leg_code into l_legislation_code;
126 --
127 if csr_leg_code%notfound then
128 --
129 -- The primary key is invalid therefore we must error
130 --
131 close csr_leg_code;
132 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
133 fnd_message.raise_error;
134 end if;
135 hr_utility.set_location(l_proc,30);
136 --
137 -- Set the global variables so the values are
138 -- available for the next call to this function.
139 --
140 close csr_leg_code;
141 pqp_pvd_bus.g_vehicle_details_id:= p_vehicle_details_id;
142 pqp_pvd_bus.g_legislation_code := l_legislation_code;
143 end if;
144 hr_utility.set_location(' Leaving:'|| l_proc, 40);
145 return l_legislation_code;
146 end return_legislation_code;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |-----------------------------< chk_ddf >----------------------------------|
150 -- ----------------------------------------------------------------------------
151 --
152 -- Description:
153 -- Validates all the Developer Descriptive Flexfield values.
154 --
155 -- Prerequisites:
156 -- All other columns have been validated. Must be called as the
157 -- last step from insert_validate and update_validate.
158 --
159 -- In Arguments:
160 -- p_rec
161 --
162 -- Post Success:
163 -- If the Developer Descriptive Flexfield structure column and data values
164 -- are all valid this procedure will end normally and processing will
165 -- continue.
166 --
167 -- Post Failure:
168 -- If the Developer Descriptive Flexfield structure column value or any of
169 -- the data values are invalid then an application error is raised as
170 -- a PL/SQL exception.
171 --
172 -- Access Status:
173 -- Internal Row Handler Use Only.
174 --
175 -- ----------------------------------------------------------------------------
176 procedure chk_ddf
177 (p_rec in pqp_pvd_shd.g_rec_type
178 ) is
179 --
180 l_proc varchar2(72) := g_package || 'chk_ddf';
181 --
182 begin
183 hr_utility.set_location('Entering:'||l_proc,10);
184 --
185 if ((p_rec.vehicle_details_id is not null) and (
186 nvl(pqp_pvd_shd.g_old_rec.vhd_information_category, hr_api.g_varchar2) <>
187 nvl(p_rec.vhd_information_category, hr_api.g_varchar2) or
191 nvl(p_rec.vhd_information2, hr_api.g_varchar2) or
188 nvl(pqp_pvd_shd.g_old_rec.vhd_information1, hr_api.g_varchar2) <>
189 nvl(p_rec.vhd_information1, hr_api.g_varchar2) or
190 nvl(pqp_pvd_shd.g_old_rec.vhd_information2, hr_api.g_varchar2) <>
192 nvl(pqp_pvd_shd.g_old_rec.vhd_information3, hr_api.g_varchar2) <>
193 nvl(p_rec.vhd_information3, hr_api.g_varchar2) or
194 nvl(pqp_pvd_shd.g_old_rec.vhd_information4, hr_api.g_varchar2) <>
195 nvl(p_rec.vhd_information4, hr_api.g_varchar2) or
196 nvl(pqp_pvd_shd.g_old_rec.vhd_information5, hr_api.g_varchar2) <>
197 nvl(p_rec.vhd_information5, hr_api.g_varchar2) or
198 nvl(pqp_pvd_shd.g_old_rec.vhd_information6, hr_api.g_varchar2) <>
199 nvl(p_rec.vhd_information6, hr_api.g_varchar2) or
200 nvl(pqp_pvd_shd.g_old_rec.vhd_information7, hr_api.g_varchar2) <>
201 nvl(p_rec.vhd_information7, hr_api.g_varchar2) or
202 nvl(pqp_pvd_shd.g_old_rec.vhd_information8, hr_api.g_varchar2) <>
203 nvl(p_rec.vhd_information8, hr_api.g_varchar2) or
204 nvl(pqp_pvd_shd.g_old_rec.vhd_information9, hr_api.g_varchar2) <>
205 nvl(p_rec.vhd_information9, hr_api.g_varchar2) or
206 nvl(pqp_pvd_shd.g_old_rec.vhd_information10, hr_api.g_varchar2) <>
207 nvl(p_rec.vhd_information10, hr_api.g_varchar2) or
208 nvl(pqp_pvd_shd.g_old_rec.vhd_information11, hr_api.g_varchar2) <>
209 nvl(p_rec.vhd_information11, hr_api.g_varchar2) or
210 nvl(pqp_pvd_shd.g_old_rec.vhd_information12, hr_api.g_varchar2) <>
211 nvl(p_rec.vhd_information12, hr_api.g_varchar2) or
212 nvl(pqp_pvd_shd.g_old_rec.vhd_information13, hr_api.g_varchar2) <>
213 nvl(p_rec.vhd_information13, hr_api.g_varchar2) or
214 nvl(pqp_pvd_shd.g_old_rec.vhd_information14, hr_api.g_varchar2) <>
215 nvl(p_rec.vhd_information14, hr_api.g_varchar2) or
216 nvl(pqp_pvd_shd.g_old_rec.vhd_information15, hr_api.g_varchar2) <>
217 nvl(p_rec.vhd_information15, hr_api.g_varchar2) or
218 nvl(pqp_pvd_shd.g_old_rec.vhd_information16, hr_api.g_varchar2) <>
219 nvl(p_rec.vhd_information16, hr_api.g_varchar2) or
220 nvl(pqp_pvd_shd.g_old_rec.vhd_information17, hr_api.g_varchar2) <>
221 nvl(p_rec.vhd_information17, hr_api.g_varchar2) or
222 nvl(pqp_pvd_shd.g_old_rec.vhd_information18, hr_api.g_varchar2) <>
223 nvl(p_rec.vhd_information18, hr_api.g_varchar2) or
224 nvl(pqp_pvd_shd.g_old_rec.vhd_information19, hr_api.g_varchar2) <>
225 nvl(p_rec.vhd_information19, hr_api.g_varchar2) or
226 nvl(pqp_pvd_shd.g_old_rec.vhd_information20, hr_api.g_varchar2) <>
227 nvl(p_rec.vhd_information20, hr_api.g_varchar2) ))
228 or (p_rec.vehicle_details_id is null) then
229 --
230 -- Only execute the validation if absolutely necessary:
231 -- a) During update, the structure column value or any
232 -- of the attribute values have actually changed.
233 -- b) During insert.
234 --
235 hr_dflex_utility.ins_or_upd_descflex_attribs
236 (p_appl_short_name => 'PQP'
237 ,p_descflex_name => 'Vehicle Details DDF'
238 ,p_attribute_category => p_rec.vhd_information_category
239 ,p_attribute1_name => 'VHD_INFORMATION1'
240 ,p_attribute1_value => p_rec.vhd_information1
241 ,p_attribute2_name => 'VHD_INFORMATION2'
242 ,p_attribute2_value => p_rec.vhd_information2
243 ,p_attribute3_name => 'VHD_INFORMATION3'
244 ,p_attribute3_value => p_rec.vhd_information3
245 ,p_attribute4_name => 'VHD_INFORMATION4'
246 ,p_attribute4_value => p_rec.vhd_information4
247 ,p_attribute5_name => 'VHD_INFORMATION5'
248 ,p_attribute5_value => p_rec.vhd_information5
249 ,p_attribute6_name => 'VHD_INFORMATION6'
250 ,p_attribute6_value => p_rec.vhd_information6
251 ,p_attribute7_name => 'VHD_INFORMATION7'
252 ,p_attribute7_value => p_rec.vhd_information7
253 ,p_attribute8_name => 'VHD_INFORMATION8'
254 ,p_attribute8_value => p_rec.vhd_information8
255 ,p_attribute9_name => 'VHD_INFORMATION9'
256 ,p_attribute9_value => p_rec.vhd_information9
257 ,p_attribute10_name => 'VHD_INFORMATION10'
258 ,p_attribute10_value => p_rec.vhd_information10
259 ,p_attribute11_name => 'VHD_INFORMATION11'
260 ,p_attribute11_value => p_rec.vhd_information11
261 ,p_attribute12_name => 'VHD_INFORMATION12'
262 ,p_attribute12_value => p_rec.vhd_information12
263 ,p_attribute13_name => 'VHD_INFORMATION13'
264 ,p_attribute13_value => p_rec.vhd_information13
265 ,p_attribute14_name => 'VHD_INFORMATION14'
266 ,p_attribute14_value => p_rec.vhd_information14
267 ,p_attribute15_name => 'VHD_INFORMATION15'
268 ,p_attribute15_value => p_rec.vhd_information15
269 ,p_attribute16_name => 'VHD_INFORMATION16'
270 ,p_attribute16_value => p_rec.vhd_information16
271 ,p_attribute17_name => 'VHD_INFORMATION17'
272 ,p_attribute17_value => p_rec.vhd_information17
273 ,p_attribute18_name => 'VHD_INFORMATION18'
274 ,p_attribute18_value => p_rec.vhd_information18
275 ,p_attribute19_name => 'VHD_INFORMATION19'
276 ,p_attribute19_value => p_rec.vhd_information19
277 ,p_attribute20_name => 'VHD_INFORMATION20'
278 ,p_attribute20_value => p_rec.vhd_information20
279 );
280 end if;
281 --
282 hr_utility.set_location(' Leaving:'||l_proc,20);
286 -- |------------------------------< chk_df >----------------------------------|
283 end chk_ddf;
284 --
285 -- ----------------------------------------------------------------------------
287 -- ----------------------------------------------------------------------------
288 --
289 -- Description:
290 -- Validates all the Descriptive Flexfield values.
291 --
292 -- Prerequisites:
293 -- All other columns have been validated. Must be called as the
294 -- last step from insert_validate and update_validate.
295 --
296 -- In Arguments:
297 -- p_rec
298 --
299 -- Post Success:
300 -- If the Descriptive Flexfield structure column and data values are
301 -- all valid this procedure will end normally and processing will
302 -- continue.
303 --
304 -- Post Failure:
305 -- If the Descriptive Flexfield structure column value or any of
306 -- the data values are invalid then an application error is raised as
307 -- a PL/SQL exception.
308 --
309 -- Access Status:
310 -- Internal Row Handler Use Only.
311 --
312 -- ----------------------------------------------------------------------------
313 procedure chk_df
314 (p_rec in pqp_pvd_shd.g_rec_type
315 ) is
316 --
317 l_proc varchar2(72) := g_package || 'chk_df';
318 --
319 begin
320 hr_utility.set_location('Entering:'||l_proc,10);
321 --
322 if ((p_rec.vehicle_details_id is not null) and (
323 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute_category, hr_api.g_varchar2) <>
324 nvl(p_rec.vhd_attribute_category, hr_api.g_varchar2) or
325 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute1, hr_api.g_varchar2) <>
326 nvl(p_rec.vhd_attribute1, hr_api.g_varchar2) or
327 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute2, hr_api.g_varchar2) <>
328 nvl(p_rec.vhd_attribute2, hr_api.g_varchar2) or
329 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute3, hr_api.g_varchar2) <>
330 nvl(p_rec.vhd_attribute3, hr_api.g_varchar2) or
331 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute4, hr_api.g_varchar2) <>
332 nvl(p_rec.vhd_attribute4, hr_api.g_varchar2) or
333 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute5, hr_api.g_varchar2) <>
334 nvl(p_rec.vhd_attribute5, hr_api.g_varchar2) or
335 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute6, hr_api.g_varchar2) <>
336 nvl(p_rec.vhd_attribute6, hr_api.g_varchar2) or
337 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute7, hr_api.g_varchar2) <>
338 nvl(p_rec.vhd_attribute7, hr_api.g_varchar2) or
339 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute8, hr_api.g_varchar2) <>
340 nvl(p_rec.vhd_attribute8, hr_api.g_varchar2) or
341 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute9, hr_api.g_varchar2) <>
342 nvl(p_rec.vhd_attribute9, hr_api.g_varchar2) or
343 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute10, hr_api.g_varchar2) <>
344 nvl(p_rec.vhd_attribute10, hr_api.g_varchar2) or
345 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute11, hr_api.g_varchar2) <>
346 nvl(p_rec.vhd_attribute11, hr_api.g_varchar2) or
347 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute12, hr_api.g_varchar2) <>
348 nvl(p_rec.vhd_attribute12, hr_api.g_varchar2) or
349 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute13, hr_api.g_varchar2) <>
350 nvl(p_rec.vhd_attribute13, hr_api.g_varchar2) or
351 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute14, hr_api.g_varchar2) <>
352 nvl(p_rec.vhd_attribute14, hr_api.g_varchar2) or
353 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute15, hr_api.g_varchar2) <>
354 nvl(p_rec.vhd_attribute15, hr_api.g_varchar2) or
355 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute16, hr_api.g_varchar2) <>
356 nvl(p_rec.vhd_attribute16, hr_api.g_varchar2) or
357 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute17, hr_api.g_varchar2) <>
358 nvl(p_rec.vhd_attribute17, hr_api.g_varchar2) or
359 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute18, hr_api.g_varchar2) <>
360 nvl(p_rec.vhd_attribute18, hr_api.g_varchar2) or
361 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute19, hr_api.g_varchar2) <>
362 nvl(p_rec.vhd_attribute19, hr_api.g_varchar2) or
363 nvl(pqp_pvd_shd.g_old_rec.vhd_attribute20, hr_api.g_varchar2) <>
364 nvl(p_rec.vhd_attribute20, hr_api.g_varchar2) ))
365 or (p_rec.vehicle_details_id is null) then
366 --
367 -- Only execute the validation if absolutely necessary:
368 -- a) During update, the structure column value or any
369 -- of the attribute values have actually changed.
370 -- b) During insert.
371 --
372 hr_dflex_utility.ins_or_upd_descflex_attribs
373 (p_appl_short_name => 'PQP'
374 ,p_descflex_name => 'Vehicle Details DF'
375 ,p_attribute_category => p_rec.vhd_attribute_category
376 ,p_attribute1_name => 'VHD_ATTRIBUTE1'
377 ,p_attribute1_value => p_rec.vhd_attribute1
378 ,p_attribute2_name => 'VHD_ATTRIBUTE2'
379 ,p_attribute2_value => p_rec.vhd_attribute2
380 ,p_attribute3_name => 'VHD_ATTRIBUTE3'
381 ,p_attribute3_value => p_rec.vhd_attribute3
382 ,p_attribute4_name => 'VHD_ATTRIBUTE4'
383 ,p_attribute4_value => p_rec.vhd_attribute4
384 ,p_attribute5_name => 'VHD_ATTRIBUTE5'
385 ,p_attribute5_value => p_rec.vhd_attribute5
386 ,p_attribute6_name => 'VHD_ATTRIBUTE6'
387 ,p_attribute6_value => p_rec.vhd_attribute6
388 ,p_attribute7_name => 'VHD_ATTRIBUTE7'
389 ,p_attribute7_value => p_rec.vhd_attribute7
390 ,p_attribute8_name => 'VHD_ATTRIBUTE8'
391 ,p_attribute8_value => p_rec.vhd_attribute8
392 ,p_attribute9_name => 'VHD_ATTRIBUTE9'
393 ,p_attribute9_value => p_rec.vhd_attribute9
397 ,p_attribute11_value => p_rec.vhd_attribute11
394 ,p_attribute10_name => 'VHD_ATTRIBUTE10'
395 ,p_attribute10_value => p_rec.vhd_attribute10
396 ,p_attribute11_name => 'VHD_ATTRIBUTE11'
398 ,p_attribute12_name => 'VHD_ATTRIBUTE12'
399 ,p_attribute12_value => p_rec.vhd_attribute12
400 ,p_attribute13_name => 'VHD_ATTRIBUTE13'
401 ,p_attribute13_value => p_rec.vhd_attribute13
402 ,p_attribute14_name => 'VHD_ATTRIBUTE14'
403 ,p_attribute14_value => p_rec.vhd_attribute14
404 ,p_attribute15_name => 'VHD_ATTRIBUTE15'
405 ,p_attribute15_value => p_rec.vhd_attribute15
406 ,p_attribute16_name => 'VHD_ATTRIBUTE16'
407 ,p_attribute16_value => p_rec.vhd_attribute16
408 ,p_attribute17_name => 'VHD_ATTRIBUTE17'
409 ,p_attribute17_value => p_rec.vhd_attribute17
410 ,p_attribute18_name => 'VHD_ATTRIBUTE18'
411 ,p_attribute18_value => p_rec.vhd_attribute18
412 ,p_attribute19_name => 'VHD_ATTRIBUTE19'
413 ,p_attribute19_value => p_rec.vhd_attribute19
414 ,p_attribute20_name => 'VHD_ATTRIBUTE20'
415 ,p_attribute20_value => p_rec.vhd_attribute20
416 );
417 end if;
418 --
419 hr_utility.set_location(' Leaving:'||l_proc,20);
420 end chk_df;
421 --
422 -- ----------------------------------------------------------------------------
423 -- |-----------------------< chk_non_updateable_args >------------------------|
424 -- ----------------------------------------------------------------------------
425 -- {Start Of Comments}
426 --
427 -- Description:
428 -- This procedure is used to ensure that non updateable attributes have
429 -- not been updated. If an attribute has been updated an error is generated.
430 --
431 -- Pre Conditions:
432 -- g_old_rec has been populated with details of the values currently in
433 -- the database.
434 --
435 -- In Arguments:
436 -- p_rec has been populated with the updated values the user would like the
437 -- record set to.
438 --
439 -- Post Success:
440 -- Processing continues if all the non updateable attributes have not
441 -- changed.
442 --
443 -- Post Failure:
444 -- An application error is raised if any of the non updatable attributes
445 -- have been altered.
446 --
447 -- {End Of Comments}
448 -- ----------------------------------------------------------------------------
449 Procedure chk_non_updateable_args
450 (p_effective_date in date
451 ,p_rec in pqp_pvd_shd.g_rec_type
452 ) IS
453 --
454 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
455 l_error EXCEPTION;
456 l_argument varchar2(30);
457 --
458 Begin
459 --
460 -- Only proceed with the validation if a row exists for the current
461 -- record in the HR Schema.
462 --
463 IF NOT pqp_pvd_shd.api_updating
464 (p_vehicle_details_id => p_rec.vehicle_details_id
465 ,p_object_version_number => p_rec.object_version_number
466 ) THEN
467 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
468 fnd_message.set_token('PROCEDURE ', l_proc);
469 fnd_message.set_token('STEP ', '5');
470 fnd_message.raise_error;
471 END IF;
472 --
473 -- EDIT_HERE: Add checks to ensure non-updateable args have
474 -- not been updated.
475 --
476 EXCEPTION
477 WHEN l_error THEN
478 hr_api.argument_changed_error
479 (p_api_name => l_proc
480 ,p_argument => l_argument);
481 WHEN OTHERS THEN
482 RAISE;
483 End chk_non_updateable_args;
484 --
485 -- ----------------------------------------------------------------------------
486 -- |---------------------------< check_fuel_type_code >-----------------------|
487 -- ----------------------------------------------------------------------------
488 Procedure check_fuel_type_code
489 (p_vehicle_details_id IN PQP_VEHICLE_DETAILS.Vehicle_Details_Id%TYPE,
490 p_fuel_type_code IN VARCHAR2,
491 p_effective_date IN DATE) is
492
493 l_proc varchar2(72) := g_package||' check_fuel_type_code';
494
495 BEGIN
496
497 hr_utility.set_location('Entering:'||l_proc, 5);
498
499 IF hr_api.not_exists_in_hr_lookups
500 (p_lookup_type => 'PQP_FUEL_TYPE' ,
501 p_lookup_code => p_fuel_type_code ,
502 p_effective_date => p_effective_date ) THEN
503
504 -- Raise error as the value does not exist as a lookup
505
506 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP' );
507 fnd_message.set_token('COLUMN','FUEL_TYPE' );
508 fnd_message.set_token('LOOKUP_TYPE', 'PQP_FUEL_TYPE' );
509 fnd_message.raise_error;
510
511 END IF;
512
513
514 END check_fuel_type_code;
515
516 -- ----------------------------------------------------------------------------
517 -- |-------------------------< check_vehicle_ownership >----------------------|
518 -- ----------------------------------------------------------------------------
519 Procedure check_vehicle_ownership
520 (p_vehicle_ownership IN PQP_VEHICLE_DETAILS.Vehicle_Ownership%TYPE
521 ) is
522
523 l_proc varchar2(72) := g_package||' check_vehicle_ownership';
524
525 BEGIN
526
527 hr_utility.set_location('Entering:'||l_proc, 5);
528
532 fnd_message.raise_error;
529 IF p_vehicle_ownership NOT IN ('COMPANY','PRIVATE') THEN
530 -- Raise error as the allowed values are company,private and NULL
531 fnd_message.set_name('PQP','PQP_230521_INVALID_VEH_OWNSHP');
533 END IF;
534
535 END check_vehicle_ownership;
536
537 -- ----------------------------------------------------------------------------
538 -- |-------------------------< check_assignment_exists >----------------------|
539 -- ----------------------------------------------------------------------------
540 Procedure check_assignment_exists
541 (p_vehicle_details_id IN PQP_VEHICLE_DETAILS.vehicle_details_id%TYPE
542 ) is
543
544 l_proc varchar2(72) := g_package||' check_assignment_exists';
545 CURSOR veh_cur IS
546 SELECT 'x'
547 FROM PQP_ASSIGNMENT_ATTRIBUTES_F
548 WHERE primary_company_car = p_vehicle_details_id
549 OR secondary_company_car = p_vehicle_details_id
550 OR private_car = p_vehicle_details_id;
551 l_dummy VARCHAR2(1);
552
553 BEGIN
554
555 hr_utility.set_location('Entering:'||l_proc, 5);
556 OPEN veh_cur;
557 FETCH veh_cur INTO l_dummy;
558 IF veh_cur%FOUND THEN
559 -- Raise error as there is an assignment for the vehicle
560 -- that is being deleted
561 CLOSE veh_cur;
562 fnd_message.set_name('PQP','PQP_230522_ASSIGNMENT_EXISTS' );
563 fnd_message.raise_error;
564 END IF;
565 CLOSE veh_cur;
566
567 END check_assignment_exists;
568
569 -- ----------------------------------------------------------------------------
570 -- |-------------------------< check_currency_code >---------------------------|
571
572 -- ----------------------------------------------------------------------------
573 Procedure check_currency_code
574 (p_currency_code IN PQP_VEHICLE_DETAILS.currency_code%TYPE,
575 p_in_business_group_id IN NUMBER
576 ) IS
577
578 l_proc varchar2(72) := g_package||' check_currency_code';
579 l_default_currency_code VARCHAR2(10);
580
581 BEGIN
582
583 hr_utility.set_location('Entering:'||l_proc, 5);
584 l_default_currency_code := hr_general.default_currency_code (p_business_group_id
585 => p_in_business_group_id);
586
587 IF p_currency_code <> l_default_currency_code THEN
588 -- Raise error as the input currency code is not equal
589 -- to the default currency for the BG
590 fnd_message.set_name('PQP','PQP_230520_CUR_CODE_MISMATCH' );
591 fnd_message.raise_error;
592 END IF;
593
594 END check_currency_code;
595
596 -- ----------------------------------------------------------------------------
597 -- |---------------------------< check_vehicle_type_code >--------------------|
598 -- ----------------------------------------------------------------------------
599 Procedure check_vehicle_type_code
600 (p_vehicle_details_id IN PQP_VEHICLE_DETAILS.Vehicle_Details_Id%TYPE,
601 p_vehicle_type_code IN VARCHAR2,
602 p_effective_date IN DATE) is
603
604 l_proc varchar2(72) := g_package||' check_vehicle_type_code';
605
606 BEGIN
607
608 hr_utility.set_location('Entering:'||l_proc, 5);
609
610 IF hr_api.not_exists_in_hr_lookups
611 (p_lookup_type => 'PQP_VEHICLE_TYPE' ,
612 p_lookup_code => p_vehicle_type_code ,
613 p_effective_date => p_effective_date ) THEN
614
615 -- Raise error as the value does not exist as a lookup
616
617 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP' );
618 fnd_message.set_token('COLUMN','VEHICLE_TYPE' );
619 fnd_message.set_token('LOOKUP_TYPE', 'PQP_VEHICLE_TYPE' );
620 fnd_message.raise_error;
621
622 END IF;
623
624
625 END check_vehicle_type_code;
626 -- ----------------------------------------------------------------------------
627 -- |---------------------------< check_negative >-----------------------|
628 -- ----------------------------------------------------------------------------
629 Procedure check_negative
630 (p_number_to_check IN NUMBER) IS
631
632 l_proc varchar2(72) := g_package||' check_negative';
633
634 BEGIN
635
636 hr_utility.set_location('Entering:'||l_proc, 5);
637 IF p_number_to_check < 0 THEN
638 fnd_message.set_name ('PAY','HR_7355_PPM_AMOUNT_NEGATIVE' );
639 fnd_message.set_token('ERROR','Value Cannot Be Negative');
640 fnd_message.raise_error;
641 END IF;
642
643 END check_negative;
644 --
645 -- ----------------------------------------------------------------------------
646 -- |---------------------------< insert_validate >----------------------------|
647 -- ----------------------------------------------------------------------------
648 Procedure insert_validate
649 (p_effective_date in date
650 ,p_rec in pqp_pvd_shd.g_rec_type
651 ) is
652 --
653 l_proc varchar2(72) := g_package||'insert_validate';
654 --
655 Begin
656 hr_utility.set_location('Entering:'||l_proc, 5);
657 --
658 -- Call all supporting business operations
659 --
660 -- EDIT_HERE: The following call to hr_api.validate_bus_grp_id
661 -- will only be valid when the business_group_id is not null.
662 -- As this column is defined as optional on the table then
663 -- different logic will be required to handle the null case.
664 -- If this is a start-up data entity then:
665 -- a) add code to stop null values being processed by this
666 -- row handler
667 -- If this is not a start-up data entity then either:
668 -- b) ignore the security_group_id value held in
669 -- client_info. This includes performing lookup
673 -- Remove this comment when the edit has been completed.
670 -- validation against the HR_STANDARD_LOOKUPS view.
671 -- or c) (less likely) ensure the correct security_group_id
672 -- value is set in client_info.
674 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
675 --
676 -- Check for the validity of the fuel code
677 pqp_pvd_bus.check_fuel_type_code(p_rec.vehicle_details_id,
678 p_rec.fuel_type,
679 p_effective_date );
680 -- Check for the validity of the vehicle type code
681 pqp_pvd_bus.check_vehicle_type_code(p_rec.vehicle_details_id,
682 p_rec.vehicle_type,
683 p_effective_date );
684
685 -- These are checks for values that cannot be negative
686 pqp_pvd_bus.check_negative(p_rec.engine_capacity_in_cc);
687 pqp_pvd_bus.check_negative(p_rec.list_price);
688 pqp_pvd_bus.check_negative(p_rec.accessory_value_at_startdate);
689 pqp_pvd_bus.check_negative(p_rec.accessory_value_added_later);
690 --pqp_pvd_bus.check_negative(p_rec.capital_contributions);
691 --pqp_pvd_bus.check_negative(p_rec.private_use_contributions);
692 pqp_pvd_bus.check_negative(p_rec.market_value_classic_car);
693
694 -- Vehicle Ownership check
695 IF p_rec.vehicle_ownership IS NOT NULL THEN
696 pqp_pvd_bus.check_vehicle_ownership(p_rec.vehicle_ownership);
697 END IF;
698 -- Check if the Input Currency matches the BG Currency
699 pqp_pvd_bus.check_currency_code(p_rec.currency_code,p_rec.business_group_id);
700 --
701 pqp_pvd_bus.chk_ddf(p_rec);
702 --
703 pqp_pvd_bus.chk_df(p_rec);
704 --
705 hr_utility.set_location(' Leaving:'||l_proc, 10);
706 End insert_validate;
707 --
708 -- ----------------------------------------------------------------------------
709 -- |---------------------------< update_validate >----------------------------|
710 -- ----------------------------------------------------------------------------
711 Procedure update_validate
712 (p_effective_date in date
713 ,p_rec in pqp_pvd_shd.g_rec_type
714 ) is
715 --
716 l_proc varchar2(72) := g_package||'update_validate';
717 --
718 Begin
719 hr_utility.set_location('Entering:'||l_proc, 5);
720 --
721 -- Call all supporting business operations
722 --
723 -- EDIT_HERE: The following call to hr_api.validate_bus_grp_id
724 -- will only be valid when the business_group_id is not null.
725 -- As this column is defined as optional on the table then
726 -- different logic will be required to handle the null case.
727 -- If this is a start-up data entity then:
728 -- a) add code to stop null values being processed by this
729 -- row handler
730 -- If this is not a start-up data entity then either:
731 -- b) ignore the security_group_id value held in
732 -- client_info. This includes performing lookup
733 -- validation against the HR_STANDARD_LOOKUPS view.
734 -- or c) (less likely) ensure the correct security_group_id
735 -- value is set in client_info.
736 -- Remove this comment when the edit has been completed.
737 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
738 --
739 --Validate FUEL_TYPE
740 pqp_pvd_bus.check_fuel_type_code(p_rec.vehicle_details_id,
741 p_rec.fuel_type,
742 p_effective_date);
743
744 -- Check for the validity of the vehicle type code
745 pqp_pvd_bus.check_vehicle_type_code(p_rec.vehicle_details_id,
746 p_rec.vehicle_type,
747 p_effective_date );
748 -- These are checks for values that cannot be negative
749 pqp_pvd_bus.check_negative(p_rec.engine_capacity_in_cc);
750 pqp_pvd_bus.check_negative(p_rec.list_price);
751 pqp_pvd_bus.check_negative(p_rec.accessory_value_at_startdate);
752 pqp_pvd_bus.check_negative(p_rec.accessory_value_added_later);
753 --pqp_pvd_bus.check_negative(p_rec.capital_contributions);
754 --pqp_pvd_bus.check_negative(p_rec.private_use_contributions);
755 pqp_pvd_bus.check_negative(p_rec.market_value_classic_car);
756 -- Vehicle Ownership check
757 IF p_rec.vehicle_ownership IS NOT NULL THEN
758 pqp_pvd_bus.check_vehicle_ownership(p_rec.vehicle_ownership);
759 END IF;
760 -- Check if the Input currency matches the BG currency
761 pqp_pvd_bus.check_currency_code(p_rec.currency_code,p_rec.business_group_id);
762 --
763 chk_non_updateable_args
764 (p_effective_date => p_effective_date
765 ,p_rec => p_rec
766 );
767 --
768 --
769 pqp_pvd_bus.chk_ddf(p_rec);
770 --
771 pqp_pvd_bus.chk_df(p_rec);
772 --
773 hr_utility.set_location(' Leaving:'||l_proc, 10);
774 End update_validate;
775 --
776 -- ----------------------------------------------------------------------------
777 -- |---------------------------< delete_validate >----------------------------|
778 -- ----------------------------------------------------------------------------
779 Procedure delete_validate
780 (p_rec in pqp_pvd_shd.g_rec_type
781 ) is
782 --
783 l_proc varchar2(72) := g_package||'delete_validate';
784 --
785 Begin
786 hr_utility.set_location('Entering:'||l_proc, 5);
787 --
788 -- Call all supporting business operations
789 --
790 pqp_pvd_bus.check_assignment_exists(p_rec.vehicle_details_id);
791 --
792 hr_utility.set_location(' Leaving:'||l_proc, 10);
793 End delete_validate;
794 --
795 end pqp_pvd_bus;
[Home] [Help]