1 Package Body pqp_shp_bus as
2 /* $Header: pqshprhi.pkb 115.8 2003/02/17 22:14:48 tmehra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqp_shp_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_service_history_period_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_service_history_period_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_service_history_periods shp
30 where shp.service_history_period_id = p_service_history_period_id
31 and pbg.business_group_id = shp.business_group_id;
32 --
33 -- Declare local variables
34 --
35 l_security_group_id number;
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 => 'service_history_period_id'
47 ,p_argument_value => p_service_history_period_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_service_history_period_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_service_history_periods shp
90 where shp.service_history_period_id = p_service_history_period_id
91 and pbg.business_group_id = shp.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 => 'service_history_period_id'
107 ,p_argument_value => p_service_history_period_id
108 );
109 --
110 if ( nvl(pqp_shp_bus.g_service_history_period_id, hr_api.g_number)
111 = p_service_history_period_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_shp_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_shp_bus.g_service_history_period_id := p_service_history_period_id;
142 pqp_shp_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_shp_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.service_history_period_id is not null) and (
186 nvl(pqp_shp_shd.g_old_rec.shp_information_category, hr_api.g_varchar2) <>
187 nvl(p_rec.shp_information_category, hr_api.g_varchar2) or
188 nvl(pqp_shp_shd.g_old_rec.shp_information1, hr_api.g_varchar2) <>
189 nvl(p_rec.shp_information1, hr_api.g_varchar2) or
190 nvl(pqp_shp_shd.g_old_rec.shp_information2, hr_api.g_varchar2) <>
191 nvl(p_rec.shp_information2, hr_api.g_varchar2) or
192 nvl(pqp_shp_shd.g_old_rec.shp_information3, hr_api.g_varchar2) <>
193 nvl(p_rec.shp_information3, hr_api.g_varchar2) or
194 nvl(pqp_shp_shd.g_old_rec.shp_information4, hr_api.g_varchar2) <>
195 nvl(p_rec.shp_information4, hr_api.g_varchar2) or
196 nvl(pqp_shp_shd.g_old_rec.shp_information5, hr_api.g_varchar2) <>
197 nvl(p_rec.shp_information5, hr_api.g_varchar2) or
198 nvl(pqp_shp_shd.g_old_rec.shp_information6, hr_api.g_varchar2) <>
199 nvl(p_rec.shp_information6, hr_api.g_varchar2) or
200 nvl(pqp_shp_shd.g_old_rec.shp_information7, hr_api.g_varchar2) <>
201 nvl(p_rec.shp_information7, hr_api.g_varchar2) or
202 nvl(pqp_shp_shd.g_old_rec.shp_information8, hr_api.g_varchar2) <>
203 nvl(p_rec.shp_information8, hr_api.g_varchar2) or
204 nvl(pqp_shp_shd.g_old_rec.shp_information9, hr_api.g_varchar2) <>
205 nvl(p_rec.shp_information9, hr_api.g_varchar2) or
206 nvl(pqp_shp_shd.g_old_rec.shp_information10, hr_api.g_varchar2) <>
207 nvl(p_rec.shp_information10, hr_api.g_varchar2) or
208 nvl(pqp_shp_shd.g_old_rec.shp_information11, hr_api.g_varchar2) <>
209 nvl(p_rec.shp_information11, hr_api.g_varchar2) or
210 nvl(pqp_shp_shd.g_old_rec.shp_information12, hr_api.g_varchar2) <>
211 nvl(p_rec.shp_information12, hr_api.g_varchar2) or
212 nvl(pqp_shp_shd.g_old_rec.shp_information13, hr_api.g_varchar2) <>
213 nvl(p_rec.shp_information13, hr_api.g_varchar2) or
214 nvl(pqp_shp_shd.g_old_rec.shp_information14, hr_api.g_varchar2) <>
215 nvl(p_rec.shp_information14, hr_api.g_varchar2) or
216 nvl(pqp_shp_shd.g_old_rec.shp_information15, hr_api.g_varchar2) <>
217 nvl(p_rec.shp_information15, hr_api.g_varchar2) or
218 nvl(pqp_shp_shd.g_old_rec.shp_information16, hr_api.g_varchar2) <>
219 nvl(p_rec.shp_information16, hr_api.g_varchar2) or
220 nvl(pqp_shp_shd.g_old_rec.shp_information17, hr_api.g_varchar2) <>
221 nvl(p_rec.shp_information17, hr_api.g_varchar2) or
222 nvl(pqp_shp_shd.g_old_rec.shp_information18, hr_api.g_varchar2) <>
223 nvl(p_rec.shp_information18, hr_api.g_varchar2) or
224 nvl(pqp_shp_shd.g_old_rec.shp_information19, hr_api.g_varchar2) <>
225 nvl(p_rec.shp_information19, hr_api.g_varchar2) or
226 nvl(pqp_shp_shd.g_old_rec.shp_information20, hr_api.g_varchar2) <>
227 nvl(p_rec.shp_information20, hr_api.g_varchar2) ))
228 or (p_rec.service_history_period_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 => 'Employment History DDF'
238 ,p_attribute_category => p_rec.shp_information_category
239 ,p_attribute1_name => 'SHP_INFORMATION1'
240 ,p_attribute1_value => p_rec.shp_information1
241 ,p_attribute2_name => 'SHP_INFORMATION2'
242 ,p_attribute2_value => p_rec.shp_information2
243 ,p_attribute3_name => 'SHP_INFORMATION3'
244 ,p_attribute3_value => p_rec.shp_information3
245 ,p_attribute4_name => 'SHP_INFORMATION4'
246 ,p_attribute4_value => p_rec.shp_information4
247 ,p_attribute5_name => 'SHP_INFORMATION5'
248 ,p_attribute5_value => p_rec.shp_information5
249 ,p_attribute6_name => 'SHP_INFORMATION6'
250 ,p_attribute6_value => p_rec.shp_information6
251 ,p_attribute7_name => 'SHP_INFORMATION7'
252 ,p_attribute7_value => p_rec.shp_information7
253 ,p_attribute8_name => 'SHP_INFORMATION8'
254 ,p_attribute8_value => p_rec.shp_information8
255 ,p_attribute9_name => 'SHP_INFORMATION9'
256 ,p_attribute9_value => p_rec.shp_information9
257 ,p_attribute10_name => 'SHP_INFORMATION10'
258 ,p_attribute10_value => p_rec.shp_information10
259 ,p_attribute11_name => 'SHP_INFORMATION11'
260 ,p_attribute11_value => p_rec.shp_information11
261 ,p_attribute12_name => 'SHP_INFORMATION12'
262 ,p_attribute12_value => p_rec.shp_information12
263 ,p_attribute13_name => 'SHP_INFORMATION13'
264 ,p_attribute13_value => p_rec.shp_information13
265 ,p_attribute14_name => 'SHP_INFORMATION14'
266 ,p_attribute14_value => p_rec.shp_information14
267 ,p_attribute15_name => 'SHP_INFORMATION15'
268 ,p_attribute15_value => p_rec.shp_information15
269 ,p_attribute16_name => 'SHP_INFORMATION16'
270 ,p_attribute16_value => p_rec.shp_information16
271 ,p_attribute17_name => 'SHP_INFORMATION17'
272 ,p_attribute17_value => p_rec.shp_information17
273 ,p_attribute18_name => 'SHP_INFORMATION18'
274 ,p_attribute18_value => p_rec.shp_information18
275 ,p_attribute19_name => 'SHP_INFORMATION19'
276 ,p_attribute19_value => p_rec.shp_information19
277 ,p_attribute20_name => 'SHP_INFORMATION20'
278 ,p_attribute20_value => p_rec.shp_information20
279 );
280 end if;
281 --
282 hr_utility.set_location(' Leaving:'||l_proc,20);
283 end chk_ddf;
284 --
285 -- ----------------------------------------------------------------------------
286 -- |------------------------------< chk_df >----------------------------------|
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_shp_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.service_history_period_id is not null) and (
323 nvl(pqp_shp_shd.g_old_rec.shp_attribute_category, hr_api.g_varchar2) <>
324 nvl(p_rec.shp_attribute_category, hr_api.g_varchar2) or
325 nvl(pqp_shp_shd.g_old_rec.shp_attribute1, hr_api.g_varchar2) <>
326 nvl(p_rec.shp_attribute1, hr_api.g_varchar2) or
327 nvl(pqp_shp_shd.g_old_rec.shp_attribute2, hr_api.g_varchar2) <>
328 nvl(p_rec.shp_attribute2, hr_api.g_varchar2) or
329 nvl(pqp_shp_shd.g_old_rec.shp_attribute3, hr_api.g_varchar2) <>
330 nvl(p_rec.shp_attribute3, hr_api.g_varchar2) or
331 nvl(pqp_shp_shd.g_old_rec.shp_attribute4, hr_api.g_varchar2) <>
332 nvl(p_rec.shp_attribute4, hr_api.g_varchar2) or
333 nvl(pqp_shp_shd.g_old_rec.shp_attribute5, hr_api.g_varchar2) <>
334 nvl(p_rec.shp_attribute5, hr_api.g_varchar2) or
335 nvl(pqp_shp_shd.g_old_rec.shp_attribute6, hr_api.g_varchar2) <>
336 nvl(p_rec.shp_attribute6, hr_api.g_varchar2) or
337 nvl(pqp_shp_shd.g_old_rec.shp_attribute7, hr_api.g_varchar2) <>
338 nvl(p_rec.shp_attribute7, hr_api.g_varchar2) or
339 nvl(pqp_shp_shd.g_old_rec.shp_attribute8, hr_api.g_varchar2) <>
340 nvl(p_rec.shp_attribute8, hr_api.g_varchar2) or
341 nvl(pqp_shp_shd.g_old_rec.shp_attribute9, hr_api.g_varchar2) <>
342 nvl(p_rec.shp_attribute9, hr_api.g_varchar2) or
343 nvl(pqp_shp_shd.g_old_rec.shp_attribute10, hr_api.g_varchar2) <>
344 nvl(p_rec.shp_attribute10, hr_api.g_varchar2) or
345 nvl(pqp_shp_shd.g_old_rec.shp_attribute11, hr_api.g_varchar2) <>
346 nvl(p_rec.shp_attribute11, hr_api.g_varchar2) or
347 nvl(pqp_shp_shd.g_old_rec.shp_attribute12, hr_api.g_varchar2) <>
348 nvl(p_rec.shp_attribute12, hr_api.g_varchar2) or
349 nvl(pqp_shp_shd.g_old_rec.shp_attribute13, hr_api.g_varchar2) <>
350 nvl(p_rec.shp_attribute13, hr_api.g_varchar2) or
351 nvl(pqp_shp_shd.g_old_rec.shp_attribute14, hr_api.g_varchar2) <>
352 nvl(p_rec.shp_attribute14, hr_api.g_varchar2) or
353 nvl(pqp_shp_shd.g_old_rec.shp_attribute15, hr_api.g_varchar2) <>
354 nvl(p_rec.shp_attribute15, hr_api.g_varchar2) or
355 nvl(pqp_shp_shd.g_old_rec.shp_attribute16, hr_api.g_varchar2) <>
356 nvl(p_rec.shp_attribute16, hr_api.g_varchar2) or
357 nvl(pqp_shp_shd.g_old_rec.shp_attribute17, hr_api.g_varchar2) <>
361 nvl(pqp_shp_shd.g_old_rec.shp_attribute19, hr_api.g_varchar2) <>
358 nvl(p_rec.shp_attribute17, hr_api.g_varchar2) or
359 nvl(pqp_shp_shd.g_old_rec.shp_attribute18, hr_api.g_varchar2) <>
360 nvl(p_rec.shp_attribute18, hr_api.g_varchar2) or
362 nvl(p_rec.shp_attribute19, hr_api.g_varchar2) or
363 nvl(pqp_shp_shd.g_old_rec.shp_attribute20, hr_api.g_varchar2) <>
364 nvl(p_rec.shp_attribute20, hr_api.g_varchar2) ))
365 or (p_rec.service_history_period_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 => 'Employment History Details DF'
375 ,p_attribute_category => p_rec.shp_attribute_category
376 ,p_attribute1_name => 'SHP_ATTRIBUTE1'
377 ,p_attribute1_value => p_rec.shp_attribute1
378 ,p_attribute2_name => 'SHP_ATTRIBUTE2'
379 ,p_attribute2_value => p_rec.shp_attribute2
380 ,p_attribute3_name => 'SHP_ATTRIBUTE3'
381 ,p_attribute3_value => p_rec.shp_attribute3
382 ,p_attribute4_name => 'SHP_ATTRIBUTE4'
383 ,p_attribute4_value => p_rec.shp_attribute4
384 ,p_attribute5_name => 'SHP_ATTRIBUTE5'
385 ,p_attribute5_value => p_rec.shp_attribute5
386 ,p_attribute6_name => 'SHP_ATTRIBUTE6'
387 ,p_attribute6_value => p_rec.shp_attribute6
388 ,p_attribute7_name => 'SHP_ATTRIBUTE7'
389 ,p_attribute7_value => p_rec.shp_attribute7
390 ,p_attribute8_name => 'SHP_ATTRIBUTE8'
391 ,p_attribute8_value => p_rec.shp_attribute8
392 ,p_attribute9_name => 'SHP_ATTRIBUTE9'
393 ,p_attribute9_value => p_rec.shp_attribute9
394 ,p_attribute10_name => 'SHP_ATTRIBUTE10'
395 ,p_attribute10_value => p_rec.shp_attribute10
396 ,p_attribute11_name => 'SHP_ATTRIBUTE11'
397 ,p_attribute11_value => p_rec.shp_attribute11
398 ,p_attribute12_name => 'SHP_ATTRIBUTE12'
399 ,p_attribute12_value => p_rec.shp_attribute12
400 ,p_attribute13_name => 'SHP_ATTRIBUTE13'
401 ,p_attribute13_value => p_rec.shp_attribute13
402 ,p_attribute14_name => 'SHP_ATTRIBUTE14'
403 ,p_attribute14_value => p_rec.shp_attribute14
404 ,p_attribute15_name => 'SHP_ATTRIBUTE15'
405 ,p_attribute15_value => p_rec.shp_attribute15
406 ,p_attribute16_name => 'SHP_ATTRIBUTE16'
407 ,p_attribute16_value => p_rec.shp_attribute16
408 ,p_attribute17_name => 'SHP_ATTRIBUTE17'
409 ,p_attribute17_value => p_rec.shp_attribute17
410 ,p_attribute18_name => 'SHP_ATTRIBUTE18'
411 ,p_attribute18_value => p_rec.shp_attribute18
412 ,p_attribute19_name => 'SHP_ATTRIBUTE19'
413 ,p_attribute19_value => p_rec.shp_attribute19
414 ,p_attribute20_name => 'SHP_ATTRIBUTE20'
415 ,p_attribute20_value => p_rec.shp_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 (
451 p_rec in pqp_shp_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_shp_shd.api_updating
464 (p_service_history_period_id => p_rec.service_history_period_id
465 ,p_object_version_number => p_rec.object_version_number
466 ) THEN
470 fnd_message.raise_error;
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');
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 -- A new chk criteria has been added
487 -- PS Bug 2028104 for more details
488 --
489 -- ----------------------------------------------------------------------------
490 -- |---------------------------< chk_periods >--------------------------------|
491 -- ----------------------------------------------------------------------------
492 --
493 -- Description :
494 --
495 -- Validates that period years or period days is mandatory if start date and end date
496 -- doesn't hold a value
497 --
498 -- Pre-conditions :
499 --
500 -- In Arguments :
501 -- p_service_history_period_id
502 -- p_start_date
503 -- p_end_date
504 -- p_period_years
505 -- p_period_days
506 -- p_assignment_id
507 -- p_object_version_number
508 --
509 -- Post Success :
510 -- If the above business rules are satisfied then procesing continues.
511 --
512 -- Post Failure :
513 -- If the above business rules are violated then
514 -- an application error will be raised and processing is terminated
515 --
516 -- Access Status :
517 -- Internal Table Handler Use only.
518 --
519 -- {End of Comments}
520 -- ---------------------------------------------------------------------------
521 procedure chk_periods
522 (p_service_history_period_id in number
523 ,p_start_date in date
524 ,p_end_date in date
525 ,p_period_years in number
526 ,p_period_days in number
527 ,p_assignment_id in number
528 ,p_object_version_number in number )
529 is
530 --
531 l_exists varchar2(1) ;
532 l_proc varchar2(72) := g_package||'chk_periods';
533 l_api_updating boolean;
534 --
535
536 begin
537 hr_utility.set_location('Entering:'|| l_proc, 5);
538 --
539 -- Check mandatory parameters have been set
540 --
541 hr_api.mandatory_arg_error
542 (p_api_name => l_proc
543 ,p_argument => 'assignment_id'
544 ,p_argument_value => p_assignment_id
545 );
546 --
547 -- Only proceed with validation if :
548 -- a) The current g_old_rec is current and
549 -- b) The year value or day value has changed
550 --
551 --
552
553 l_api_updating := pqp_shp_shd.api_updating
554 (p_service_history_period_id => p_service_history_period_id
555 ,p_object_version_number => p_object_version_number);
556 --
557
558 if ((l_api_updating and ((nvl(pqp_shp_shd.g_old_rec.period_years, hr_api.g_number) <>
559 nvl(p_period_years, hr_api.g_number)) or
560 (nvl(pqp_shp_shd.g_old_rec.period_days, hr_api.g_number) <>
561 nvl(p_period_days, hr_api.g_number)))) or
562 (NOT l_api_updating)) then
563 --
564 -- Check that either year value or day value is not null
565 --
566
567 if p_period_years is null and
568 p_period_days is null and
569 p_start_date is null and
570 p_end_date is null then
571
572 --
573 hr_utility.set_message(8303, 'PQP_230543_SHP_REQ_VALUES');
574 hr_utility.raise_error;
575 --
576
577 end if; -- end if of period is null check...
578
579 end if; -- end if of api updatin check...
580 --
581 hr_utility.set_location(' Leaving:'|| l_proc, 10);
582 --
583 end chk_periods;
584 --
585 -- ----------------------------------------------------------------------------
586 -- |---------------------------< chk_start_date >----------------------------|
587 -- ----------------------------------------------------------------------------
588 --
589 -- Description :
590 -- Validates that start_date is less than or equal to end date
591 --
592 -- Validates that start_date does not overlap with those of another
593 -- service history record. (This is now validated on the forms side)
594 --
595 -- Pre-conditions :
596 -- Format of p_start_date and p_end_date must be correct
597 --
598 -- In Arguments :
599 -- p_service_history_period_id
600 -- p_start_date
601 -- p_end_date
602 -- p_assignment_id
603 -- p_object_version_number
604 --
605 -- Post Success :
606 -- If the above business rules are satisfied then procesing continues.
607 --
608 -- Post Failure :
609 -- If the above business rules are violated then
610 -- an application error will be raised and processing is terminated
611 --
612 -- Access Status :
613 -- Internal Table Handler Use only.
614 --
615 -- {End of Comments}
619 ,p_start_date in date
616 -- ---------------------------------------------------------------------------
617 procedure chk_start_date
618 (p_service_history_period_id in number
620 ,p_end_date in date
621 ,p_assignment_id in number
622 ,p_object_version_number in number )
623 is
624 --
625 l_exists varchar2(1) ;
626 l_proc varchar2(72) := g_package||'chk_start_date';
627 l_api_updating boolean;
628 --
629 begin
630 hr_utility.set_location('Entering:'|| l_proc, 5);
631 --
632 -- Check mandatory parameters have been set
633 --
634 hr_api.mandatory_arg_error
635 (p_api_name => l_proc
636 ,p_argument => 'assignment_id'
637 ,p_argument_value => p_assignment_id
638 );
639 --
640 -- Commented out the following lines of code
641 -- as start dates and end dates are no longer mandatory
642 -- PS Bug 2028104
643
644 -- hr_api.mandatory_arg_error
645 -- (p_api_name => l_proc
646 -- ,p_argument => 'start_date'
647 -- ,p_argument_value => p_start_date
648 -- );
649 --
650 -- hr_api.mandatory_arg_error
651 -- (p_api_name => l_proc
652 -- ,p_argument => 'end_date'
653 -- ,p_argument_value => p_end_date
654 -- );
655 --
656
657 --
658 -- Only proceed with validation if :
659 -- a) The current g_old_rec is current and
660 -- b) The start_date value has changed
661 --
662 l_api_updating := pqp_shp_shd.api_updating
663 (p_service_history_period_id => p_service_history_period_id
664 ,p_object_version_number => p_object_version_number);
665
666 --
667 if ((l_api_updating and nvl(pqp_shp_shd.g_old_rec.start_date, hr_api.g_date) <>
668 nvl(p_start_date, hr_api.g_date)) or
669 (NOT l_api_updating)) then
670 --
671 --
672 -- Check that the start_date value is less than or equal to the end_date
673 -- value for the current record
674 --
675 if p_start_date > p_end_date then
676 --
677 hr_utility.set_message(8303, 'PQP_230503_SHP_START_DT_GREAT');
678 hr_utility.raise_error;
679 --
680 end if;
681 --
682 end if;
683 --
684 hr_utility.set_location(' Leaving:'|| l_proc, 10);
685 --
686 end chk_start_date;
687 --
688 --
689 -- ----------------------------------------------------------------------------
690 -- |---------------------------< chk_end_date >----------------------------|
691 -- ----------------------------------------------------------------------------
692 --
693 -- Description :
694 -- Validates that an end_date is mandatory if start date has a value
695 -- otherwise it shouldn't hold a value
696 --
697 -- Validates that end_date is greater than or equal to start_date
698 --
699 -- Validates year or days to have a value if dates are null
700 --
701 -- Validates that end_date does not overlap with those of another
702 -- service history record for a given assignment_id. (This is now validated
703 -- on the forms side)
704 --
705 -- Pre-conditions :
706 -- Format of p_start_date and p_end_date must be correct
707 --
708 -- In Arguments :
709 -- p_service_history_period_id
710 -- p_start_date
711 -- p_end_date
712 -- p_period_years
713 -- p_period_days
714 -- p_assignment_id
715 -- p_object_version_number
716 --
717 -- Post Success :
718 -- If the above business rules are satisfied then procesing continues.
719 --
720 -- Post Failure :
721 -- If the above business rules are violated then
722 -- an application error will be raised and processing is terminated
723 --
724 -- Access Status :
725 -- Internal Table Handler Use only.
726 --
727 -- {End of Comments}
728 -- ---------------------------------------------------------------------------
729 procedure chk_end_date
730 (p_service_history_period_id in number
731 ,p_start_date in date
732 ,p_end_date in date
733 ,p_period_years in number
734 ,p_period_days in number
735 ,p_assignment_id in number
736 ,p_object_version_number in number )
737 is
738 --
739 l_exists varchar2(1) ;
740 l_proc varchar2(72) := g_package||'chk_end_date';
741 l_api_updating boolean;
742 --
743
744 begin
745 hr_utility.set_location('Entering:'|| l_proc, 5);
746 --
747 -- Check mandatory parameters have been set
748 --
749 hr_api.mandatory_arg_error
750 (p_api_name => l_proc
751 ,p_argument => 'assignment_id'
752 ,p_argument_value => p_assignment_id
753 );
754 --
755 -- Commented out the following lines of code
756 -- as start and end dates are no longer mandatory
757 -- but an end date becomes a mandatory input if a
758 -- start date is specified
759 -- PS Bug 2028104
760
761 -- hr_api.mandatory_arg_error
762 -- (p_api_name => l_proc
766
763 -- ,p_argument => 'start_date'
764 -- ,p_argument_value => p_start_date
765 -- );
767 --
768 -- Only proceed with validation if :
769 -- a) The current g_old_rec is current and
770 -- b) The end_date value has changed
771 --
772 --
773
774 l_api_updating := pqp_shp_shd.api_updating
775 (p_service_history_period_id => p_service_history_period_id
776 ,p_object_version_number => p_object_version_number);
777 --
778
779 if ((l_api_updating and nvl(pqp_shp_shd.g_old_rec.end_date, hr_api.g_date) <>
780 nvl(p_end_date, hr_api.g_date)) or
781 (NOT l_api_updating)) then
782 --
783 -- Check that the end date has a value if start date has a value
784 -- error out if it has a value when start date doesn't have one
785 --
786 if p_start_date is not null then
787
788 hr_api.mandatory_arg_error
789 (p_api_name => l_proc
790 ,p_argument => 'end_date'
791 ,p_argument_value => p_end_date
792 );
793
794 elsif p_end_date is not null then
795
796 hr_utility.set_message(8303, 'PQP_230504_SHP_END_DT_LESSER');
797 hr_utility.raise_error;
798
799 elsif p_period_years is null and
800 p_period_days is null then
801
802 hr_utility.set_message(8303, 'PQP_230543_SHP_REQ_VALUES');
803 hr_utility.raise_error;
804
805 --
806 end if; -- End if of start date is not null check..
807 --
808 --
809 -- Check that the end_date value is greater than or equal to the start_date
810 -- value for the current record
811 --
812 if p_end_date < p_start_date then
813 --
814 hr_utility.set_message(8303, 'PQP_230504_SHP_END_DT_LESSER');
815 hr_utility.raise_error;
816 --
817 end if;
818 --
819 end if;
820 --
821 hr_utility.set_location(' Leaving:'|| l_proc, 10);
822 --
823 end chk_end_date;
824 --
825 --
826 -- ----------------------------------------------------------------------------
827 -- |---------------------------< insert_validate >----------------------------|
828 -- ----------------------------------------------------------------------------
829 Procedure insert_validate
830 (
831 p_rec in pqp_shp_shd.g_rec_type
832 ) is
833 --
834 l_proc varchar2(72) := g_package||'insert_validate';
835 --
836 Begin
837 hr_utility.set_location('Entering:'||l_proc, 5);
838 --
839 -- Call all supporting business operations
840 --
841 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
842 --
843 --
844 pqp_shp_bus.chk_ddf(p_rec);
845 --
846 pqp_shp_bus.chk_df(p_rec);
847 --
848 --
849 -- Validate start date
850 -- Only if it has a value
851 --
852
853 if p_rec.start_date is not null then
854
855 chk_start_date
856 (p_service_history_period_id => p_rec.service_history_period_id
857 ,p_start_date => p_rec.start_date
858 ,p_end_date => p_rec.end_date
859 ,p_assignment_id => p_rec.assignment_id
860 ,p_object_version_number => p_rec.object_version_number);
861
862 end if; -- End if of start date is not null check...
863
864 --
865 hr_utility.set_location(l_proc, 7);
866 --
867 -- Validate end date
868 --
869 chk_end_date
870 (p_service_history_period_id => p_rec.service_history_period_id
871 ,p_start_date => p_rec.start_date
872 ,p_end_date => p_rec.end_date
873 ,p_period_years => p_rec.period_years
874 ,p_period_days => p_rec.period_days
875 ,p_assignment_id => p_rec.assignment_id
876 ,p_object_version_number => p_rec.object_version_number);
877 --
881 -- ----------------------------------------------------------------------------
878 hr_utility.set_location(' Leaving:'||l_proc, 10);
879 End insert_validate;
880 --
882 -- |---------------------------< update_validate >----------------------------|
883 -- ----------------------------------------------------------------------------
884 Procedure update_validate
885 (
886 p_rec in pqp_shp_shd.g_rec_type
887 ) is
888 --
889 l_proc varchar2(72) := g_package||'update_validate';
890 --
891 Begin
892 hr_utility.set_location('Entering:'||l_proc, 5);
893 --
894 -- Call all supporting business operations
895 --
896 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
897 --
898 chk_non_updateable_args
899 (
900 p_rec => p_rec
901 );
902 --
903 --
904 pqp_shp_bus.chk_ddf(p_rec);
905 --
906 pqp_shp_bus.chk_df(p_rec);
907 --
908 --
909 -- Validate start date
910 -- Only if it has a value
911 --
912
913 if p_rec.start_date is not null then
914
915 chk_start_date
916 (p_service_history_period_id => p_rec.service_history_period_id
917 ,p_start_date => p_rec.start_date
918 ,p_end_date => p_rec.end_date
919 ,p_assignment_id => p_rec.assignment_id
920 ,p_object_version_number => p_rec.object_version_number);
921
922 end if; -- end if of start date is not null check...
923 --
924 hr_utility.set_location(l_proc, 7);
925 --
926 -- Validate end date
927 --
928 chk_end_date
929 (p_service_history_period_id => p_rec.service_history_period_id
930 ,p_start_date => p_rec.start_date
931 ,p_end_date => p_rec.end_date
932 ,p_period_years => p_rec.period_years
933 ,p_period_days => p_rec.period_days
934 ,p_assignment_id => p_rec.assignment_id
935 ,p_object_version_number => p_rec.object_version_number);
936 --
937 hr_utility.set_location(l_proc, 8);
938 --
939 chk_periods
940 (p_service_history_period_id => p_rec.service_history_period_id
941 ,p_start_date => p_rec.start_date
942 ,p_end_date => p_rec.end_date
943 ,p_period_years => p_rec.period_years
944 ,p_period_days => p_rec.period_days
945 ,p_assignment_id => p_rec.assignment_id
946 ,p_object_version_number => p_rec.object_version_number);
947 --
948 hr_utility.set_location(' Leaving:'||l_proc, 10);
949 End update_validate;
950 --
951 -- ----------------------------------------------------------------------------
952 -- |---------------------------< delete_validate >----------------------------|
953 -- ----------------------------------------------------------------------------
954 Procedure delete_validate
955 (p_rec in pqp_shp_shd.g_rec_type
956 ) is
957 --
958 l_proc varchar2(72) := g_package||'delete_validate';
959 --
960 Begin
961 hr_utility.set_location('Entering:'||l_proc, 5);
962 --
963 -- Call all supporting business operations
964 --
965 hr_utility.set_location(' Leaving:'||l_proc, 10);
966 End delete_validate;
967 --
968 end pqp_shp_bus;
[Home] [Help]