[Home] [Help]
PACKAGE BODY: APPS.PQH_CPD_BUS
Source
1 Package Body pqh_cpd_bus as
2 /* $Header: pqcpdrhi.pkb 120.0 2005/05/29 01:44:39 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_cpd_bus.'; -- Global package name
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 -- The following two global variables are only to be
12 -- used by the return_legislation_code function.
13 --
14 g_legislation_code varchar2(150) default null;
15 g_corps_definition_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_corps_definition_id in number
23 ,p_associated_column1 in varchar2 default null
24 ) is
25 --
26 -- Declare cursor
27 --
28 cursor csr_sec_grp is
29 select pbg.security_group_id,
30 pbg.legislation_code
31 from per_business_groups_perf pbg
32 , pqh_corps_definitions cpd
33 where cpd.corps_definition_id = p_corps_definition_id
34 and pbg.business_group_id = cpd.business_group_id;
35 --
36 -- Declare local variables
37 --
38 l_security_group_id number;
39 l_proc varchar2(72) ;
40 l_legislation_code varchar2(150);
41 --
42 begin
43 --
44 if g_debug then
45 l_proc := g_package||'set_security_group_id';
46 hr_utility.set_location('Entering:'|| l_proc, 10);
47 end if;
48 --
49 -- Ensure that all the mandatory parameter are not null
50 --
51 hr_api.mandatory_arg_error
52 (p_api_name => l_proc
53 ,p_argument => 'corps_definition_id'
54 ,p_argument_value => p_corps_definition_id
55 );
56 --
57 open csr_sec_grp;
58 fetch csr_sec_grp into l_security_group_id
59 , l_legislation_code;
60 --
61 if csr_sec_grp%notfound then
62 --
63 close csr_sec_grp;
64 --
65 -- The primary key is invalid therefore we must error
66 --
67 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
68 hr_multi_message.add
69 (p_associated_column1
70 => nvl(p_associated_column1,'CORPS_DEFINITION_ID')
71 );
72 --
73 else
74 close csr_sec_grp;
75 --
76 -- Set the security_group_id in CLIENT_INFO
77 --
78 hr_api.set_security_group_id
79 (p_security_group_id => l_security_group_id
80 );
81 --
82 -- Set the sessions legislation context in HR_SESSION_DATA
83 --
84 hr_api.set_legislation_context(l_legislation_code);
85 end if;
86 --
87 if g_debug then
88 hr_utility.set_location(' Leaving:'|| l_proc, 20);
89 end if;
90 --
91 end set_security_group_id;
92 --
93 -- ---------------------------------------------------------------------------
94 -- |---------------------< return_legislation_code >-------------------------|
95 -- ---------------------------------------------------------------------------
96 --
97 Function return_legislation_code
98 (p_corps_definition_id in number
99 )
100 Return Varchar2 Is
101 --
102 -- Declare cursor
103 --
104 cursor csr_leg_code is
105 select pbg.legislation_code
106 from per_business_groups_perf pbg
107 , pqh_corps_definitions cpd
108 where cpd.corps_definition_id = p_corps_definition_id
109 and pbg.business_group_id = cpd.business_group_id;
110 --
111 -- Declare local variables
112 --
113 l_legislation_code varchar2(150);
114 l_proc varchar2(72);
115 --
116 Begin
117 --
118 if g_debug then
119 l_proc := g_package||'return_legislation_code';
120 hr_utility.set_location('Entering:'|| l_proc, 10);
121 end if;
122 --
123 -- Ensure that all the mandatory parameter are not null
124 --
125 hr_api.mandatory_arg_error
126 (p_api_name => l_proc
127 ,p_argument => 'corps_definition_id'
128 ,p_argument_value => p_corps_definition_id
129 );
130 --
131 if ( nvl(pqh_cpd_bus.g_corps_definition_id, hr_api.g_number)
132 = p_corps_definition_id) then
133 --
134 -- The legislation code has already been found with a previous
135 -- call to this function. Just return the value in the global
136 -- variable.
137 --
138 l_legislation_code := pqh_cpd_bus.g_legislation_code;
139 if g_debug then
140 hr_utility.set_location(l_proc, 20);
141 end if;
142 else
143 --
144 -- The ID is different to the last call to this function
145 -- or this is the first call to this function.
146 --
147 open csr_leg_code;
148 fetch csr_leg_code into l_legislation_code;
149 --
150 if csr_leg_code%notfound then
151 --
152 -- The primary key is invalid therefore we must error
153 --
154 close csr_leg_code;
155 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
156 fnd_message.raise_error;
157 end if;
158 if g_debug then
159 hr_utility.set_location(l_proc,30);
160 end if;
161 --
162 -- Set the global variables so the values are
163 -- available for the next call to this function.
164 --
165 close csr_leg_code;
166 pqh_cpd_bus.g_corps_definition_id := p_corps_definition_id;
167 pqh_cpd_bus.g_legislation_code := l_legislation_code;
168 end if;
169 if g_debug then
170 hr_utility.set_location(' Leaving:'|| l_proc, 40);
171 end if;
172 return l_legislation_code;
173 end return_legislation_code;
174 --
175 -- ----------------------------------------------------------------------------
176 -- |------------------------------< chk_df >----------------------------------|
177 -- ----------------------------------------------------------------------------
178 --
179 -- Description:
180 -- Validates all the Descriptive Flexfield values.
181 --
182 -- Prerequisites:
183 -- All other columns have been validated. Must be called as the
184 -- last step from insert_validate and update_validate.
185 --
186 -- In Arguments:
187 -- p_rec
188 --
189 -- Post Success:
190 -- If the Descriptive Flexfield structure column and data values are
191 -- all valid this procedure will end normally and processing will
192 -- continue.
193 --
194 -- Post Failure:
195 -- If the Descriptive Flexfield structure column value or any of
196 -- the data values are invalid then an application error is raised as
197 -- a PL/SQL exception.
198 --
199 -- Access Status:
200 -- Internal Row Handler Use Only.
201 --
202 -- ----------------------------------------------------------------------------
203 procedure chk_df
204 (p_rec in pqh_cpd_shd.g_rec_type
205 ) is
206 --
207 l_proc varchar2(72);
208 --
209 begin
210 if g_debug then
211 l_proc := g_package || 'chk_df';
212 hr_utility.set_location('Entering:'||l_proc,10);
213 end if;
214 --
215 if ((p_rec.corps_definition_id is not null) and (
216 nvl(pqh_cpd_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
217 nvl(p_rec.attribute1, hr_api.g_varchar2) or
218 nvl(pqh_cpd_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
219 nvl(p_rec.attribute2, hr_api.g_varchar2) or
220 nvl(pqh_cpd_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
221 nvl(p_rec.attribute3, hr_api.g_varchar2) or
222 nvl(pqh_cpd_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
223 nvl(p_rec.attribute4, hr_api.g_varchar2) or
224 nvl(pqh_cpd_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
225 nvl(p_rec.attribute5, hr_api.g_varchar2) or
226 nvl(pqh_cpd_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
227 nvl(p_rec.attribute6, hr_api.g_varchar2) or
228 nvl(pqh_cpd_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
229 nvl(p_rec.attribute7, hr_api.g_varchar2) or
230 nvl(pqh_cpd_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
231 nvl(p_rec.attribute8, hr_api.g_varchar2) or
232 nvl(pqh_cpd_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
233 nvl(p_rec.attribute9, hr_api.g_varchar2) or
234 nvl(pqh_cpd_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
235 nvl(p_rec.attribute10, hr_api.g_varchar2) or
236 nvl(pqh_cpd_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
237 nvl(p_rec.attribute11, hr_api.g_varchar2) or
238 nvl(pqh_cpd_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
239 nvl(p_rec.attribute12, hr_api.g_varchar2) or
240 nvl(pqh_cpd_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
241 nvl(p_rec.attribute13, hr_api.g_varchar2) or
242 nvl(pqh_cpd_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
243 nvl(p_rec.attribute14, hr_api.g_varchar2) or
244 nvl(pqh_cpd_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
245 nvl(p_rec.attribute15, hr_api.g_varchar2) or
246 nvl(pqh_cpd_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
247 nvl(p_rec.attribute16, hr_api.g_varchar2) or
248 nvl(pqh_cpd_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
249 nvl(p_rec.attribute17, hr_api.g_varchar2) or
250 nvl(pqh_cpd_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
251 nvl(p_rec.attribute18, hr_api.g_varchar2) or
252 nvl(pqh_cpd_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
253 nvl(p_rec.attribute19, hr_api.g_varchar2) or
254 nvl(pqh_cpd_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
255 nvl(p_rec.attribute20, hr_api.g_varchar2) or
256 nvl(pqh_cpd_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
257 nvl(p_rec.attribute21, hr_api.g_varchar2) or
258 nvl(pqh_cpd_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
259 nvl(p_rec.attribute22, hr_api.g_varchar2) or
260 nvl(pqh_cpd_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
261 nvl(p_rec.attribute23, hr_api.g_varchar2) or
262 nvl(pqh_cpd_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
263 nvl(p_rec.attribute24, hr_api.g_varchar2) or
264 nvl(pqh_cpd_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
265 nvl(p_rec.attribute25, hr_api.g_varchar2) or
266 nvl(pqh_cpd_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
267 nvl(p_rec.attribute26, hr_api.g_varchar2) or
268 nvl(pqh_cpd_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
269 nvl(p_rec.attribute27, hr_api.g_varchar2) or
270 nvl(pqh_cpd_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
271 nvl(p_rec.attribute28, hr_api.g_varchar2) or
272 nvl(pqh_cpd_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
273 nvl(p_rec.attribute29, hr_api.g_varchar2) or
274 nvl(pqh_cpd_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
275 nvl(p_rec.attribute30, hr_api.g_varchar2) or
276 nvl(pqh_cpd_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
277 nvl(p_rec.attribute_category, hr_api.g_varchar2) ))
278 or (p_rec.corps_definition_id is null) then
279 --
280 -- Only execute the validation if absolutely necessary:
281 -- a) During update, the structure column value or any
282 -- of the attribute values have actually changed.
283 -- b) During insert.
284 --
285 hr_dflex_utility.ins_or_upd_descflex_attribs
286 (p_appl_short_name => 'PQH'
287 ,p_descflex_name => 'Additional Corps Info'
288 ,p_attribute1_name => 'ATTRIBUTE1'
289 ,p_attribute1_value => p_rec.attribute1
290 ,p_attribute2_name => 'ATTRIBUTE2'
291 ,p_attribute2_value => p_rec.attribute2
292 ,p_attribute3_name => 'ATTRIBUTE3'
293 ,p_attribute3_value => p_rec.attribute3
294 ,p_attribute4_name => 'ATTRIBUTE4'
295 ,p_attribute4_value => p_rec.attribute4
296 ,p_attribute5_name => 'ATTRIBUTE5'
297 ,p_attribute5_value => p_rec.attribute5
298 ,p_attribute6_name => 'ATTRIBUTE6'
299 ,p_attribute6_value => p_rec.attribute6
300 ,p_attribute7_name => 'ATTRIBUTE7'
301 ,p_attribute7_value => p_rec.attribute7
302 ,p_attribute8_name => 'ATTRIBUTE8'
303 ,p_attribute8_value => p_rec.attribute8
304 ,p_attribute9_name => 'ATTRIBUTE9'
305 ,p_attribute9_value => p_rec.attribute9
306 ,p_attribute10_name => 'ATTRIBUTE10'
307 ,p_attribute10_value => p_rec.attribute10
308 ,p_attribute11_name => 'ATTRIBUTE11'
309 ,p_attribute11_value => p_rec.attribute11
310 ,p_attribute12_name => 'ATTRIBUTE12'
311 ,p_attribute12_value => p_rec.attribute12
312 ,p_attribute13_name => 'ATTRIBUTE13'
313 ,p_attribute13_value => p_rec.attribute13
314 ,p_attribute14_name => 'ATTRIBUTE14'
315 ,p_attribute14_value => p_rec.attribute14
316 ,p_attribute15_name => 'ATTRIBUTE15'
317 ,p_attribute15_value => p_rec.attribute15
318 ,p_attribute16_name => 'ATTRIBUTE16'
319 ,p_attribute16_value => p_rec.attribute16
320 ,p_attribute17_name => 'ATTRIBUTE17'
321 ,p_attribute17_value => p_rec.attribute17
322 ,p_attribute18_name => 'ATTRIBUTE18'
323 ,p_attribute18_value => p_rec.attribute18
324 ,p_attribute19_name => 'ATTRIBUTE19'
325 ,p_attribute19_value => p_rec.attribute19
326 ,p_attribute20_name => 'ATTRIBUTE20'
327 ,p_attribute20_value => p_rec.attribute20
328 ,p_attribute21_name => 'ATTRIBUTE21'
329 ,p_attribute21_value => p_rec.attribute21
330 ,p_attribute22_name => 'ATTRIBUTE22'
331 ,p_attribute22_value => p_rec.attribute22
332 ,p_attribute23_name => 'ATTRIBUTE23'
333 ,p_attribute23_value => p_rec.attribute23
334 ,p_attribute24_name => 'ATTRIBUTE24'
335 ,p_attribute24_value => p_rec.attribute24
336 ,p_attribute25_name => 'ATTRIBUTE25'
337 ,p_attribute25_value => p_rec.attribute25
338 ,p_attribute26_name => 'ATTRIBUTE26'
339 ,p_attribute26_value => p_rec.attribute26
340 ,p_attribute27_name => 'ATTRIBUTE27'
341 ,p_attribute27_value => p_rec.attribute27
342 ,p_attribute28_name => 'ATTRIBUTE28'
343 ,p_attribute28_value => p_rec.attribute28
344 ,p_attribute29_name => 'ATTRIBUTE29'
345 ,p_attribute29_value => p_rec.attribute29
346 ,p_attribute30_name => 'ATTRIBUTE30'
347 ,p_attribute30_value => p_rec.attribute30
348 ,p_attribute_category => 'ATTRIBUTE_CATEGORY'
349 );
350 end if;
351 --
352 if g_debug then
353 hr_utility.set_location(' Leaving:'||l_proc,20);
354 end if;
355 end chk_df;
356 --
357 -- ----------------------------------------------------------------------------
358 -- |-----------------------< chk_non_updateable_args >------------------------|
359 -- ----------------------------------------------------------------------------
360 -- {Start Of Comments}
361 --
362 -- Description:
363 -- This procedure is used to ensure that non updateable attributes have
364 -- not been updated. If an attribute has been updated an error is generated.
365 --
366 -- Pre Conditions:
367 -- g_old_rec has been populated with details of the values currently in
368 -- the database.
369 --
370 -- In Arguments:
371 -- p_rec has been populated with the updated values the user would like the
372 -- record set to.
373 --
374 -- Post Success:
375 -- Processing continues if all the non updateable attributes have not
376 -- changed.
377 --
378 -- Post Failure:
379 -- An application error is raised if any of the non updatable attributes
380 -- have been altered.
381 --
382 -- {End Of Comments}
383 -- ----------------------------------------------------------------------------
384 Procedure chk_non_updateable_args
385 (p_effective_date in date
386 ,p_rec in pqh_cpd_shd.g_rec_type
387 ) IS
388 --
389 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
390 --
391 Begin
392 --
393 -- Only proceed with the validation if a row exists for the current
394 -- record in the HR Schema.
395 --
396 IF NOT pqh_cpd_shd.api_updating
397 (p_corps_definition_id => p_rec.corps_definition_id
398 ,p_object_version_number => p_rec.object_version_number
399 ) THEN
400 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
401 fnd_message.set_token('PROCEDURE ', l_proc);
402 fnd_message.set_token('STEP ', '5');
403 fnd_message.raise_error;
404 END IF;
405
406 End chk_non_updateable_args;
407 --
408 -- ---------------------------------------------------------------------------
409 -- |----------------------< chk_corps_name >--------------------------|
410 -- ---------------------------------------------------------------------------
411 --
412 Procedure chk_corps_name
413 (p_name in varchar2,
414 p_corps_definition_id in number default null,
415 p_business_group_id in number
416 ) is
417 --
418 -- Declare cursor
419 --
420 CURSOR csr_cpd_name is
421 SELECT 'X'
422 FROM pqh_corps_definitions
423 WHERE name = p_name
424 AND corps_definition_id <> nvl(p_corps_definition_id,-1)
425 AND business_group_id = p_business_group_id;
426 --
427 -- Declare local variables
428 --
429 l_proc varchar2(72) ;
430 l_name varchar2(30);
431 --
432 begin
433 --
434 if g_debug then
435 l_proc := g_package||'chk_corps_name';
436 hr_utility.set_location('Entering:'|| l_proc, 10);
437 end if;
438 --
439 -- Ensure that all the mandatory parameter are not null
440 --
441 hr_api.mandatory_arg_error
442 (p_api_name => l_proc
443 ,p_argument => 'corps_name'
444 ,p_argument_value => p_name
445 );
446 hr_api.mandatory_arg_error
447 (p_api_name => l_proc
448 ,p_argument => 'business_group_id'
449 ,p_argument_value => p_business_group_id
450 );
451 --
452 open csr_cpd_name;
453 fetch csr_cpd_name into l_name;
454 --
455 if csr_cpd_name%found then
456 close csr_cpd_name;
457 fnd_message.set_name('PQH', 'PQH_DUPLICATE_CORPS_NAME');
458 hr_multi_message.add(p_associated_column1 => 'NAME');
459 else
460 close csr_cpd_name;
461 end if;
462 --
463 if g_debug then
464 hr_utility.set_location(' Leaving:'|| l_proc, 20);
465 end if;
466 --
467 end chk_corps_name;
468 --
469 -- ---------------------------------------------------------------------------
470 -- |----------------------< chk_type_of_ps >--------------------------|
471 -- ---------------------------------------------------------------------------
472 --
473 PROCEDURE chk_type_of_ps (p_effective_date DATE, p_type_of_ps VARCHAR2)
474 IS
475 l_proc VARCHAR2 (72);
476 l_value varchar2(1000);
477 CURSOR csr_type_of_ps
478 IS
479 SELECT NULL
480 FROM per_shared_types_vl
481 WHERE shared_type_id = TO_NUMBER (p_type_of_ps);
482 BEGIN
483 IF g_debug
484 THEN
485 l_proc := g_package || 'chk_type_of_ps';
486 hr_utility.set_location ('Entering: ' || l_proc, 10);
487 END IF;
488
489 hr_api.mandatory_arg_error (p_api_name => l_proc,
490 p_argument => 'type_of_ps',
491 p_argument_value => p_type_of_ps
492 );
493
494 OPEN csr_type_of_ps;
495
496 --
497 FETCH csr_type_of_ps
498 INTO l_value;
499
500 IF csr_type_of_ps%NOTFOUND
501 THEN
502 --
503 fnd_message.set_name ('PQH', 'PQH_CORPS_INVALID_TYPE_OF_PS');
504 hr_multi_message.ADD (p_associated_column1 => 'TYPE_OF_PS');
505 END IF;
506
507 CLOSE csr_type_of_ps;
508
509 IF g_debug
510 THEN
511 hr_utility.set_location ('Leaving: ' || l_proc, 20);
512 END IF;
513 END chk_type_of_ps;
514
515
516 --
517 -- ---------------------------------------------------------------------------
518 -- |----------------------< chk_primary_prof_field_id >--------------------------|
519 -- ---------------------------------------------------------------------------
520 --
521 Procedure chk_primary_prof_field_id(p_primary_prof_field_id IN NUMBER) IS
522
523 CURSOR csr_primary_prof_field IS
524 SELECT 'Y'
525 FROM per_shared_types
526 WHERE shared_type_id = p_primary_prof_field_id;
527 l_proc Varchar2(72);
528 l_valid Varchar2(1) := 'N';
529 begin
530 if g_debug then
531 l_proc := g_package||'chk_primary_prof_field_id';
532 hr_utility.set_location('Entering: '||l_proc,10);
533 end if;
534 if p_primary_prof_field_id IS NOT NULL THEN
535 OPEN csr_primary_prof_field;
536 FETCH csr_primary_prof_field INTO l_valid;
537 CLOSE csr_primary_prof_field;
538 if l_valid = 'N' then
539 fnd_message.set_name('PQH','PQH_CORPS_INVALID_PRIM_FIELD');
540 hr_multi_message.add(p_associated_column1 => 'PRIMARY_PROF_FIELD_ID');
541 end if;
542 end if;
543 if g_debug then
544 hr_utility.set_location('Leaving: '||l_proc,20);
545 end if;
546 end chk_primary_prof_field_id;
547
548 -- ---------------------------------------------------------------------------
549 -- |----------------------< chk_category_cd >--------------------------|
550 -- ---------------------------------------------------------------------------
551 --
552 Procedure chk_category_cd(p_effective_date DATE,
553 p_category_cd VARCHAR2) IS
554
555 l_proc Varchar2(72);
556 begin
557 if g_debug then
558 l_proc := g_package||'chk_category_cd';
559 hr_utility.set_location('Entering: '||l_proc,10);
560 end if;
561 hr_api.mandatory_arg_error
562 (p_api_name => l_proc
563 ,p_argument => 'category_cd'
564 ,p_argument_value => p_category_cd
565 );
566 if hr_api.not_exists_in_hr_lookups(p_effective_date,'PQH_CORPS_CATEGORY',p_category_cd) then
567 fnd_message.set_name('PQH','PQH_CORPS_INVALID_CATEGORY');
568 hr_multi_message.add(p_associated_column1 => 'CATEGORY_CD');
569 end if;
570 if g_debug then
571 hr_utility.set_location('Leaving: '||l_proc,20);
572 end if;
573 end chk_category_cd;
574 -- ---------------------------------------------------------------------------
575 -- |----------------------< chk_corps_type_cd >--------------------------|
576 -- ---------------------------------------------------------------------------
577 --
578 Procedure chk_corps_type_cd(p_effective_date DATE,
579 p_corps_type_cd VARCHAR2) IS
580
581 l_proc Varchar2(72);
582 begin
583 if g_debug then
584 l_proc := g_package||'chk_corps_type_cd';
585 hr_utility.set_location('Entering: '||l_proc,10);
586 end if;
587 if p_corps_type_cd IS NOT NULL AND hr_api.not_exists_in_hr_lookups(p_effective_date,'PQH_CORPS_TYPE',p_corps_type_cd) then
588 fnd_message.set_name('PQH','PQH_CORPS_INVALID_TYPE_CD');
589 hr_multi_message.add(p_associated_column1 => 'CORPS_TYPE_CD');
590 end if;
591 if g_debug then
592 hr_utility.set_location('Leaving: '||l_proc,20);
593 end if;
594 end chk_corps_type_cd;
595 -- ---------------------------------------------------------------------------
596 -- |----------------------< chk_starting_grade_id >--------------------------|
597 -- ---------------------------------------------------------------------------
598 --
599 Procedure chk_starting_grade_id(p_starting_grade_id NUMBER,
600 p_effective_date DATE) IS
601 CURSOR csr_valid_grade IS
602 SELECT 'Y'
603 FROM per_grades
604 WHERE grade_id = p_starting_grade_id
605 AND p_effective_date BETWEEN date_from AND nvl(date_to,to_date('31/12/4712','DD/MM/RRRR'));
606 l_valid varchar2(1) := 'N';
607 l_proc varchar2(72);
608 begin
609 if g_debug then
610 l_proc := g_package||'chk_starting_grade_id';
611 hr_utility.set_location('Entering: '||l_proc,10);
612 end if;
613 if p_starting_grade_id IS NOT NULL THEN
614 OPEN csr_valid_grade;
615 FETCH csr_valid_grade INTO l_valid;
616 CLOSE csr_valid_grade;
617 IF l_valid = 'N' THEN
618 fnd_message.set_name('PQH','PQH_CORPS_INVALID_STR_GRD');
619 hr_multi_message.add(p_associated_column1 => 'STARTING_GRADE_ID');
620 END IF;
621 end if;
622 if g_debug then
623 hr_utility.set_location('Leaving: '||l_proc,20);
624 end if;
625 end chk_starting_grade_id;
626 -- ---------------------------------------------------------------------------
627 -- |----------------------< chk_starting_grade_step_id >--------------------------|
628 -- ---------------------------------------------------------------------------
629 --
630 Procedure chk_starting_grade_step_id(p_starting_grade_step_id NUMBER,
631 p_starting_grade_id NUMBER,
632 p_effective_date DATE) IS
633 CURSOR csr_valid_step IS
634 SELECT 'Y'
635 FROM per_grade_spines_f gs,
636 per_spinal_point_steps_f sps
637 WHERE gs.grade_id = p_starting_grade_id
638 AND p_effective_date BETWEEN gs.effective_start_date and gs.effective_end_date
639 AND gs.grade_spine_id = sps.grade_spine_id
640 AND sps.step_id = p_starting_grade_step_id
641 AND p_effective_date BETWEEN sps.effective_start_date and sps.effective_end_date;
642 l_valid varchar2(1) := 'N';
643 l_proc varchar2(72);
644 begin
645 if g_debug then
646 l_proc := g_package||'chk_starting_grade_id';
647 hr_utility.set_location('Entering: '||l_proc,10);
648 end if;
649 if hr_multi_message.no_error_message
650 (p_check_message_name1 => 'PQH_CORPS_INVALID_STR_GRD') then
651 if p_starting_grade_id IS NOT NULL AND p_starting_grade_step_id IS NOT NULL THEN
652 OPEN csr_valid_step;
653 FETCH csr_valid_step INTO l_valid;
654 CLOSE csr_valid_step;
655 IF l_valid = 'N' THEN
656 fnd_message.set_name('PQH','PQH_CORPS_INVALID_STR_STEP');
657 hr_multi_message.add(p_associated_column1 => 'STARTING_GRADE_STEP_ID');
658 END IF;
659 end if;
660 end if;
661 if g_debug then
662 hr_utility.set_location('Leaving: '||l_proc,20);
663 end if;
664 end chk_starting_grade_step_id;
665 --
666 -- ---------------------------------------------------------------------------
667 -- |----------------------< chk_corps_dates >--------------------------|
668 -- ---------------------------------------------------------------------------
669 --
670 Procedure chk_corps_dates
671 (p_date_from in date,
672 p_date_to in date default null,
673 p_recruitment_end_date in date default null
674 ) is
675 l_proc varchar2(72) ;
676 l_date_to date;
677 l_eot date := to_date('31/12/4712','dd/mm/RRRR');
678 --
679 begin
680 --
681 if g_debug then
682 l_proc := g_package||'chk_corps_dates';
683 hr_utility.set_location('Entering:'|| l_proc, 10);
684 end if;
685 --
686 -- Ensure that all the mandatory parameter are not null
687 --
688 hr_api.mandatory_arg_error
689 (p_api_name => l_proc
690 ,p_argument => 'date_from'
691 ,p_argument_value => p_date_from
692 );
693
694 if p_date_to is null then
695 l_date_to := l_eot;
696 else
697 l_date_to := p_date_to;
698 end if;
699 --
700 if p_date_from > l_date_to then
701 fnd_message.set_name('PQH', 'PQH_CORPS_ESD_GREAT_EED');
702 hr_multi_message.add(p_associated_column1=> 'DATE_FROM');
703 end if;
704 if nvl(p_recruitment_end_date,l_date_to) > l_date_to then
705 fnd_message.set_name('PQH', 'PQH_CORPS_RED_GREAT_EED');
706 hr_multi_message.add(p_associated_column1=>'RECRUITMENT_END_DATE');
707 end if;
708 if p_date_from > nvl(p_recruitment_end_date,p_date_from) then
709 fnd_message.set_name('PQH','PQH_CORPS_ESD_GREAT_RED');
710 hr_multi_message.add(p_associated_column1=>'RECRUITMENT_END_DATE');
711 end if;
712 if g_debug then
713 hr_utility.set_location(' Leaving:'|| l_proc, 20);
714 end if;
715 --
716 end chk_corps_dates;
717 --
718 --
719 -- ---------------------------------------------------------------------------
720 -- |----------------------< chk_corps_work_hours >--------------------------|
721 -- ---------------------------------------------------------------------------
722 --
723 Procedure chk_corps_work_hours (p_rec in pqh_cpd_shd.g_rec_type ) is
724 l_proc varchar2(72) ;
725 --
726 begin
727 --
728 if g_debug then
729 l_proc := g_package||'chk_corps_work_hours';
730 hr_utility.set_location('Entering:'|| l_proc, 10);
731 end if;
732
733 if p_rec.normal_hours is not null and p_rec.normal_hours_frequency is null then
734 fnd_message.set_name('PQH', 'PQH_CORPS_NOR_FRQ_MISSING');
735 hr_multi_message.add(p_associated_column1 => 'NORMAL_HOURS_FREQUENCY');
736 end if;
737 if p_rec.minimum_hours is not null and p_rec.minimum_hours_frequency is null then
738 fnd_message.set_name('PQH', 'PQH_CORPS_MIN_FRQ_MISSING');
739 hr_multi_message.add(p_associated_column1 => 'MINIMUM_HOURS_FREQUENCY');
740 end if;
741 if nvl(p_rec.MINIMUM_HOURS,-1) > nvl(p_rec.NORMAL_HOURS,0) then
742 fnd_message.set_name('PQH', 'PQH_CORPS_MIN_MORE_NOR');
743 hr_multi_message.add(p_associated_column1 => 'MINIMUM_HOURS');
744 end if;
745
746 if g_debug then
747 hr_utility.set_location('Leaving:'|| l_proc, 10);
748 end if;
749
750 end chk_corps_work_hours;
751 --
752 --
753 -- ---------------------------------------------------------------------------
754 -- |----------------------< chk_corps_other_proc_info >--------------------------|
755 -- ---------------------------------------------------------------------------
756 --
757 PROCEDURE chk_corps_other_proc_info(p_rec in pqh_cpd_shd.g_rec_type ) is
758 --
759 l_proc varchar2(72);
760 --
761 begin
762 --
763 if g_debug then
764 l_proc := g_package||'chk_corps_other_proc_info';
765 hr_utility.set_location('Entering:'|| l_proc, 10);
766 end if;
767 if p_rec.RETIREMENT_AGE is not null then
768 if p_rec.RETIREMENT_AGE not between 40 and 80 then
769 fnd_message.set_name('PQH', 'PQH_CORPS_RETIREMENT_AGE');
770 hr_multi_message.add(p_associated_column1 => 'RETIREMENT_AGE');
771 end if;
772 end if;
773 if nvl(p_rec.secondment_threshold,0) not between 0 and 100 then
774 fnd_message.set_name('PQH', 'PQH_CORPS_INVALID_SECOND');
775 hr_multi_message.add(p_associated_column1 => 'SECONDMENT_THRESHOLD');
776 end if;
777 if nvl(p_rec.probation_period,0) < 0 then
778 fnd_message.set_name('PQH','PQH_PROB_PERIOD_NEGATIVE');
779 hr_multi_message.add(p_associated_column1=>'PROBATION_PERIOD');
780 end if;
781 if p_rec.probation_period IS NOT NULL AND p_rec.probation_units IS NULL then
782 fnd_message.set_name('PQH','PQH_PROB_UNITS_MISSING');
783 hr_multi_message.add(p_associated_column1 => 'PROBATION_UNITS');
784 end if;
785 if g_debug then
786 hr_utility.set_location('Leaving:'|| l_proc, 20);
787 end if;
788 end chk_corps_other_proc_info;
789 --
790 -- ---------------------------------------------------------------------------
791 -- |----------------------< chk_ben_pgm_id >--------------------------|
792 -- ---------------------------------------------------------------------------
793 --
794 PROCEDURE chk_ben_pgm_id(p_ben_pgm_id NUMBER,
795 p_effective_date DATE) is
796 CURSOR csr_ben_pgm_exists IS
797 SELECT 'Y'
798 FROM ben_pgm_f
799 WHERE pgm_id = p_ben_pgm_id
800 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
801
802 l_valid varchar2(1) := 'N';
803 l_proc varchar2(72);
804 begin
805 if g_debug then
806 l_proc := g_package||'chk_ben_pgm_id';
807 hr_utility.set_location('Entering: '||l_proc,10);
808 end if;
809 if p_ben_pgm_id IS NOT NULL then
810 OPEN csr_ben_pgm_exists;
811 FETCH csr_ben_pgm_exists INTO l_valid;
812 CLOSE csr_ben_pgm_exists;
813 if l_valid = 'N' then
814 fnd_message.set_name('PQH','PQH_CORPS_INVALID_PGM');
815 hr_multi_message.add(p_associated_column1=>'BEN_PGM_ID');
816 end if;
817 end if;
818 if g_debug then
819 hr_utility.set_location('Leaving:'|| l_proc, 20);
820 end if;
821 end chk_ben_pgm_id;
822
823 --
824 -- ----------------------------------------------------------------------------
825 -- |---------------------------< insert_validate >----------------------------|
826 -- ----------------------------------------------------------------------------
827 Procedure insert_validate
828 (p_effective_date in date
829 ,p_rec in pqh_cpd_shd.g_rec_type
830 ) is
831 --
832 l_proc varchar2(72) ;
833 --
834 Begin
835 g_debug := hr_utility.debug_enabled;
836 if g_debug then
837 l_proc := g_package||'insert_validate';
838 hr_utility.set_location('Entering:'||l_proc, 5);
839 end if;
840 --
841 -- Call all supporting business operations
842 --
843 hr_api.validate_bus_grp_id
844 (p_business_group_id => p_rec.business_group_id
845 ,p_associated_column1 => pqh_cpd_shd.g_tab_nam
846 || '.BUSINESS_GROUP_ID');
847
848
849 --
850 -- After validating the set of important attributes,
851 -- if Multiple Message detection is enabled and at least
852 -- one error has been found then abort further validation.
853 --
854 hr_multi_message.end_validation_set;
855 --
856 chk_corps_name(p_name => p_rec.name,
857 p_corps_definition_id => p_rec.corps_definition_id,
858 p_business_group_id => p_rec.business_group_id);
859
860 chk_type_of_ps(p_effective_date => p_effective_date,
861 p_type_of_ps => p_rec.type_of_ps);
862
863 chk_primary_prof_field_id(p_primary_prof_field_id => p_rec.primary_prof_field_id);
864
865 chk_category_cd(p_effective_date => p_effective_date,
866 p_category_cd => p_rec.category_cd);
867
868 chk_corps_type_cd(p_effective_date => p_effective_date,
869 p_corps_type_cd => p_rec.corps_type_cd);
870
871 chk_starting_grade_id(p_starting_grade_id => p_rec.starting_grade_id,
872 p_effective_date => p_effective_date);
873
874 chk_starting_grade_step_id(p_starting_grade_step_id => p_rec.starting_grade_step_id,
875 p_starting_grade_id => p_rec.starting_grade_id,
876 p_effective_date => p_effective_date);
877
878 chk_corps_work_hours (p_rec => p_rec );
879 chk_corps_other_proc_info(p_rec => p_rec);
880 chk_ben_pgm_id(p_effective_date => p_effective_date,
881 p_ben_pgm_id => p_rec.ben_pgm_id);
882 -- Validate Dependent Attributes
883 --
884 chk_corps_dates(p_date_from => p_rec.date_from,
885 p_date_to => p_rec.date_to,
886 p_recruitment_end_date => p_rec.recruitment_end_date) ;
887 -- removing the call to chk_df as at the moment the DFF is not enabled from the OA Page
888 -- pqh_cpd_bus.chk_df(p_rec);
889 --
890 if g_debug then
891 hr_utility.set_location(' Leaving:'||l_proc, 10);
892 end if;
893 End insert_validate;
894 --
895 -- ----------------------------------------------------------------------------
896 -- |---------------------------< update_validate >----------------------------|
897 -- ----------------------------------------------------------------------------
898 Procedure update_validate
899 (p_effective_date in date
900 ,p_rec in pqh_cpd_shd.g_rec_type
901 ) is
902 --
903 l_proc varchar2(72);
904 --
905 Begin
906 g_debug := hr_utility.debug_enabled;
907 if g_debug then
908 l_proc := g_package||'update_validate';
909 hr_utility.set_location('Entering:'||l_proc, 5);
910 end if;
911 --
912 -- Call all supporting business operations
913 --
914 hr_api.validate_bus_grp_id
915 (p_business_group_id => p_rec.business_group_id
916 ,p_associated_column1 => pqh_cpd_shd.g_tab_nam
917 || '.BUSINESS_GROUP_ID');
918 --
919 -- After validating the set of important attributes,
920 -- if Multiple Message detection is enabled and at least
921 -- one error has been found then abort further validation.
922 --
923 hr_multi_message.end_validation_set;
924 --
925 chk_corps_name(p_name => p_rec.name,
926 p_corps_definition_id => p_rec.corps_definition_id,
927 p_business_group_id => p_rec.business_group_id);
928
929 chk_type_of_ps(p_effective_date => p_effective_date,
930 p_type_of_ps => p_rec.type_of_ps);
931
932 chk_primary_prof_field_id(p_primary_prof_field_id => p_rec.primary_prof_field_id);
933
934 chk_category_cd(p_effective_date => p_effective_date,
935 p_category_cd => p_rec.category_cd);
936
937 chk_corps_type_cd(p_effective_date => p_effective_date,
938 p_corps_type_cd => p_rec.corps_type_cd);
939
940 chk_starting_grade_id(p_starting_grade_id => p_rec.starting_grade_id,
941 p_effective_date => p_effective_date);
942
943 chk_starting_grade_step_id(p_starting_grade_step_id => p_rec.starting_grade_step_id,
944 p_starting_grade_id => p_rec.starting_grade_id,
945 p_effective_date => p_effective_date);
946
947 chk_corps_work_hours (p_rec => p_rec );
948 chk_corps_other_proc_info(p_rec => p_rec);
949 chk_ben_pgm_id(p_effective_date => p_effective_date,
950 p_ben_pgm_id => p_rec.ben_pgm_id);
951 --
952 -- Validate Dependent Attributes
953 --
954 chk_corps_dates(p_date_from => p_rec.date_from,
955 p_date_to => p_rec.date_to,
956 p_recruitment_end_date => p_rec.recruitment_end_date) ;
957 chk_non_updateable_args
958 (p_effective_date => p_effective_date
959 ,p_rec => p_rec
960 );
961 --
962 --
963 -- removing the call to chk_df as at the moment the DFF is not enabled from the OA Page
964 -- pqh_cpd_bus.chk_df(p_rec);
965 --
966 if g_debug then
967 hr_utility.set_location(' Leaving:'||l_proc, 10);
968 end if;
969 End update_validate;
970 --
971 -- ----------------------------------------------------------------------------
972 -- |---------------------------< delete_validate >----------------------------|
973 -- ----------------------------------------------------------------------------
974 Procedure delete_validate
975 (p_rec in pqh_cpd_shd.g_rec_type
976 ) is
977 --
978 l_proc varchar2(72);
979 --
980 Begin
981 g_debug := hr_utility.debug_enabled;
982 if g_debug then
983 l_proc := g_package||'delete_validate';
984 hr_utility.set_location('Entering:'||l_proc, 5);
985 end if;
986 --
987 -- Call all supporting business operations
988 --
989 if g_debug then
990 hr_utility.set_location(' Leaving:'||l_proc, 10);
991 end if;
992 End delete_validate;
993 --
994 end pqh_cpd_bus;