[Home] [Help]
PACKAGE BODY: APPS.PQH_CEI_BUS
Source
1 Package Body pqh_cei_bus as
2 /* $Header: pqceirhi.pkb 115.6 2002/12/05 19:31:08 rpasapul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_cei_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_corps_extra_info_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< chk_corps_grades_dup >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure chk_corps_grades_dup
21 (p_rec in pqh_cei_shd.g_rec_type
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_cpd_grade is
27 select 'X'
28 from pqh_corps_extra_info
29 where information_type ='GRADE'
30 and corps_definition_id = p_rec.corps_definition_id
31 and information3 = p_rec.information3;
32 --
33 -- Declare local variables
34 --
35 l_proc varchar2(72) := g_package||'chk_corps_grades_dup';
36 l_grade varchar2(30);
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 --
45 open csr_cpd_grade;
46 fetch csr_cpd_grade into l_grade;
47 --
48 if csr_cpd_grade%found then
49 close csr_cpd_grade;
50 hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_GRADE');
51 hr_utility.raise_error;
52 else
53 close csr_cpd_grade;
54 end if;
55 --
56 hr_utility.set_location(' Leaving:'|| l_proc, 20);
57 --
58 end chk_corps_grades_dup;
59 --
60 -- ---------------------------------------------------------------------------
61 -- |----------------------< chk_corps_org >--------------------------|
62 -- ---------------------------------------------------------------------------
63 --
64 Procedure chk_corps_org
65 (p_rec in pqh_cei_shd.g_rec_type
66 ) is
67 --
68 -- Declare cursor
69 --
70 cursor csr_cpd_org is
71 select 'X'
72 from pqh_corps_extra_info
73 where information_type ='ORGANIZATION'
74 and corps_definition_id = p_rec.corps_definition_id
75 and information3 = p_rec.information3;
76 --
77 -- Declare local variables
78 --
79 l_proc varchar2(72) := g_package||'chk_corps_org';
80 l_org varchar2(30);
81 --
82 begin
83 --
84 hr_utility.set_location('Entering:'|| l_proc, 10);
85 --
86 -- Ensure that all the mandatory parameter are not null
87 --
88 --
89 open csr_cpd_org;
90 fetch csr_cpd_org into l_org;
91 --
92 if csr_cpd_org%found then
93 close csr_cpd_org;
94 hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_ORG');
95 hr_utility.raise_error;
96 else
97 close csr_cpd_org;
98 end if;
99 --
100 hr_utility.set_location(' Leaving:'|| l_proc, 20);
101 --
102 end chk_corps_org;
103 --
104 -- ---------------------------------------------------------------------------
105 -- |----------------------< chk_corps_grades >--------------------------|
106 -- ---------------------------------------------------------------------------
107 --
108 Procedure chk_corps_grades
109 (p_rec in pqh_cei_shd.g_rec_type
110 ) is
111 l_proc varchar2(72) := g_package||'chk_corps_grades';
112 l_sum_quota number;
113 l_grade_quota number;
114 l_information4 number;
115 l_information5 number;
116 l_information6 number;
117 l_information7 number;
118 --
119 begin
120 --
121 hr_utility.set_location('Entering:'|| l_proc, 10);
122 if p_rec.information_type ='GRADE' then
123 --
124 l_information4 := to_number(p_rec.information4);
125 l_information5 := to_number(p_rec.information5);
126 l_information6 := to_number(p_rec.information6);
127 l_information7 := to_number(p_rec.information7);
128 --
129 if l_information4 not between 0 and 100 then
130 hr_utility.set_message(8302, 'PQH_CORPS_GRADE_QUOTA');
131 hr_utility.raise_error;
132 end if;
133 if l_information5 not between 0 and 100 then
134 hr_utility.set_message(8302, 'PQH_CORPS_MIN_QUOTA');
135 hr_utility.raise_error;
136 end if;
137 if l_information6 not between 0 and 100 then
138 hr_utility.set_message(8302, 'PQH_CORPS_AVG_QUOTA');
139 hr_utility.raise_error;
140 end if;
141 if l_information7 not between 0 and 100 then
142 hr_utility.set_message(8302, 'PQH_CORPS_MAX_QUOTA');
143 hr_utility.raise_error;
144 end if;
145 l_sum_quota := nvl(l_information5,0)+nvl(l_information6,0)+nvl(l_information7,0);
146 if l_sum_quota > 100 then
147 hr_utility.set_message(8302, 'PQH_CORPS_TOTAL_QUOTA');
148 hr_utility.raise_error;
149 end if;
150 select sum(information4)
151 into l_grade_quota
152 from pqh_corps_extra_info
153 where corps_definition_id = p_rec.corps_definition_id
154 and information_type ='GRADE'
155 and corps_extra_info_id <> nvl(p_rec.corps_extra_info_id,corps_extra_info_id);
156 if l_grade_quota + l_information4 > 100 then
157 hr_utility.set_message(8302, 'PQH_CORPS_OCCU_QUOTA');
158 hr_utility.raise_error;
159 end if;
160 end if;
161 --
162 hr_utility.set_location(' Leaving:'|| l_proc, 20);
163 --
164 end chk_corps_grades;
165 --
166 -- ---------------------------------------------------------------------------
167 -- |----------------------< chk_corps_training >--------------------------|
168 -- ---------------------------------------------------------------------------
169 --
170 Procedure chk_corps_training
171 (p_rec in pqh_cei_shd.g_rec_type
172 ) is
173 --
174 -- Declare cursor
175 --
176 cursor csr_cpd_training is
177 select 'X'
178 from pqh_corps_extra_info
179 where information_type ='TRAINING'
180 and corps_definition_id = p_rec.corps_definition_id
181 and corps_extra_info_id <> nvl(p_rec.corps_extra_info_id,-1)
182 and information3 = p_rec.information3
183 and information4 = p_rec.information4
184 and information5 = p_rec.information5
185 and information6 = p_rec.information6
186 and information7 = p_rec.information7;
187 --
188 -- Declare local variables
189 --
190 l_proc varchar2(72) := g_package||'chk_corps_training';
191 l_training varchar2(30);
192 --
193 begin
194 --
195 hr_utility.set_location('Entering:'|| l_proc, 10);
196 --
197 -- Ensure that all the mandatory parameter are not null
198 --
199 --
200 open csr_cpd_training;
201 fetch csr_cpd_training into l_training;
202 --
203 if csr_cpd_training%found then
204 close csr_cpd_training;
205 hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_TRAINING');
206 hr_utility.raise_error;
207 else
208 close csr_cpd_training;
209 end if;
210 --
211 hr_utility.set_location(' Leaving:'|| l_proc, 20);
212 --
213 end chk_corps_training;
214 --
215 -- ---------------------------------------------------------------------------
216 -- |----------------------< chk_corps_exam >--------------------------|
217 -- ---------------------------------------------------------------------------
218 --
219 Procedure chk_corps_exam
220 (p_rec in pqh_cei_shd.g_rec_type
221 ) is
222 --
223 -- Declare cursor
224 --
225 cursor csr_cpd_exam is
226 select 'X'
227 from pqh_corps_extra_info
228 where information_type ='EXAM'
229 and corps_definition_id = p_rec.corps_definition_id
230 and corps_extra_info_id <> nvl(p_rec.corps_extra_info_id,-1)
231 and information3 = p_rec.information3
232 and information4 = p_rec.information4
233 and information5 = p_rec.information5
234 and information6 = p_rec.information6;
235 --
236 -- Declare local variables
237 --
238 l_proc varchar2(72) := g_package||'chk_corps_exam';
239 l_exam varchar2(30);
240 --
241 begin
242 --
243 hr_utility.set_location('Entering:'|| l_proc, 10);
244 --
245 -- Ensure that all the mandatory parameter are not null
246 --
247 --
248 open csr_cpd_exam;
249 fetch csr_cpd_exam into l_exam;
250 --
251 if csr_cpd_exam%found then
252 close csr_cpd_exam;
253 hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_EXAM');
254 hr_utility.raise_error;
255 else
256 close csr_cpd_exam;
257 end if;
258 --
259 hr_utility.set_location(' Leaving:'|| l_proc, 20);
260 --
261 end chk_corps_exam;
262 --
263 -- ---------------------------------------------------------------------------
264 -- |----------------------< chk_corps_rules >--------------------------|
265 -- ---------------------------------------------------------------------------
266 --
267 Procedure chk_corps_rules
268 (p_rec in pqh_cei_shd.g_rec_type
269 ) is
270 --
271 -- Declare cursor
272 --
273 cursor csr_cpd_rules is
274 select 'X'
275 from pqh_corps_extra_info
276 where information_type ='RULES'
277 and corps_definition_id = p_rec.corps_definition_id
278 and corps_extra_info_id <> nvl(p_rec.corps_extra_info_id,-1)
279 and information3 = p_rec.information3
280 and information4 = p_rec.information4
281 and information5 = p_rec.information5
282 and information6 = p_rec.information6
283 and information7 = p_rec.information7;
284 --
285 -- Declare local variables
286 --
287 l_proc varchar2(72) := g_package||'chk_corps_rules';
288 l_rules varchar2(30);
289 --
290 begin
291 --
292 hr_utility.set_location('Entering:'|| l_proc, 10);
293 --
294 -- Ensure that all the mandatory parameter are not null
295 --
296 --
297 open csr_cpd_rules;
298 fetch csr_cpd_rules into l_rules;
299 --
300 if csr_cpd_rules%found then
301 close csr_cpd_rules;
302 hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_RULES');
303 hr_utility.raise_error;
304 else
305 close csr_cpd_rules;
306 end if;
307 if p_rec.information_type ='RULES' then
308 if p_rec.information4 ='DIPLOMA' then
309 null;
310 elsif p_rec.information4 ='MAX_AGE' then
311 if p_rec.information5 is null then
312 hr_utility.set_message(8302, 'PQH_CORPS_MAX_AGE_NULL');
313 hr_utility.raise_error;
314 end if;
315 if p_rec.information5 not between 0 and 80 then
316 hr_utility.set_message(8302, 'PQH_CORPS_JOINING_MAX_AGE');
317 hr_utility.raise_error;
318 end if;
319 if nvl(p_rec.information6,'Y') <> 'Y' then
320 hr_utility.set_message(8302, 'PQH_CORPS_MAX_AGE_YEARS');
321 hr_utility.raise_error;
322 end if;
323 elsif p_rec.information4 ='MIN_AGE' then
324 if p_rec.information5 is null then
325 hr_utility.set_message(8302, 'PQH_CORPS_MIN_AGE_NULL');
326 hr_utility.raise_error;
327 end if;
328 if p_rec.information5 not between 0 and 80 then
329 hr_utility.set_message(8302, 'PQH_CORPS_JOINING_MIN_AGE');
330 hr_utility.raise_error;
331 end if;
332 if nvl(p_rec.information6,'Y') <> 'Y' then
333 hr_utility.set_message(8302, 'PQH_CORPS_MIN_AGE_YEARS');
334 hr_utility.raise_error;
335 end if;
336 elsif p_rec.information4 ='NATIONAL' then
337 null;
338 elsif p_rec.information4 ='PROB_PERIOD' then
339 if p_rec.information6 is null then
340 hr_utility.set_message(8302, 'PQH_CORPS_PROB_PRD_UOM_NULL');
341 hr_utility.raise_error;
342 end if;
343 elsif p_rec.information4 ='SERVICE_LEN' then
344 null;
345 elsif p_rec.information4 ='START_STEP' then
346 null;
347 end if;
348 end if;
349 --
350 hr_utility.set_location(' Leaving:'|| l_proc, 20);
351 --
352 end chk_corps_rules;
353 --
354 -- ---------------------------------------------------------------------------
355 -- |----------------------< chk_corps_doc >--------------------------|
356 -- ---------------------------------------------------------------------------
357 --
358 Procedure chk_corps_doc
359 (p_rec in pqh_cei_shd.g_rec_type
360 ) is
361 --
362 -- Declare cursor
363 --
364 cursor csr_cpd_doc is
365 select 'X'
366 from pqh_corps_extra_info
367 where information_type ='DOCUMENT'
368 and corps_definition_id = p_rec.corps_definition_id
369 and corps_extra_info_id <> nvl(p_rec.corps_extra_info_id, -1)
370 and information3 = p_rec.information3
371 and information4 = p_rec.information4
372 and information5 = p_rec.information5
373 and information6 = p_rec.information6;
374 --
375 -- Declare local variables
376 --
377 l_proc varchar2(72) := g_package||'chk_corps_doc';
378 l_doc varchar2(30);
379 --
380 begin
381 --
382 hr_utility.set_location('Entering:'|| l_proc, 10);
383 --
384 -- Ensure that all the mandatory parameter are not null
385 --
386 --
387 open csr_cpd_doc;
388 fetch csr_cpd_doc into l_doc;
389 --
390 if csr_cpd_doc%found then
391 close csr_cpd_doc;
392 hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_DOC');
393 hr_utility.raise_error;
394 else
395 close csr_cpd_doc;
396 end if;
397 --
398 hr_utility.set_location(' Leaving:'|| l_proc, 20);
399 --
400 end chk_corps_doc;
401 --
402 -- ---------------------------------------------------------------------------
403 -- |----------------------< set_security_group_id >--------------------------|
404 -- ---------------------------------------------------------------------------
405 --
406 Procedure set_security_group_id
407 (p_corps_extra_info_id in number
408 ,p_associated_column1 in varchar2 default null
409 ) is
410 --
411 -- Declare cursor
412 --
413 -- EDIT_HERE In the following cursor statement add join(s) between
414 -- pqh_corps_extra_info and PER_BUSINESS_GROUPS
415 -- so that the security_group_id for
416 -- the current business group context can be derived.
417 -- Remove this comment when the edit has been completed.
418 cursor csr_sec_grp is
419 select pbg.security_group_id
420 from per_business_groups pbg
421 , pqh_corps_extra_info cei
422 -- , EDIT_HERE table_name(s) 333
423 where cei.corps_extra_info_id = p_corps_extra_info_id;
424 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
425 --
426 -- Declare local variables
427 --
428 l_security_group_id number;
429 l_proc varchar2(72) := g_package||'set_security_group_id';
430 --
431 begin
432 --
433 hr_utility.set_location('Entering:'|| l_proc, 10);
434 --
435 -- Ensure that all the mandatory parameter are not null
436 --
437 hr_api.mandatory_arg_error
438 (p_api_name => l_proc
439 ,p_argument => 'corps_extra_info_id'
443 open csr_sec_grp;
440 ,p_argument_value => p_corps_extra_info_id
441 );
442 --
444 fetch csr_sec_grp into l_security_group_id;
445 --
446 if csr_sec_grp%notfound then
447 --
448 close csr_sec_grp;
449 --
450 -- The primary key is invalid therefore we must error
451 --
452 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
453 hr_multi_message.add
454 (p_associated_column1
455 => nvl(p_associated_column1,'CORPS_EXTRA_INFO_ID')
456 );
457 --
458 else
459 close csr_sec_grp;
460 --
461 -- Set the security_group_id in CLIENT_INFO
462 --
463 hr_api.set_security_group_id
464 (p_security_group_id => l_security_group_id
465 );
466 end if;
467 --
468 hr_utility.set_location(' Leaving:'|| l_proc, 20);
469 --
470 end set_security_group_id;
471 --
472 -- ---------------------------------------------------------------------------
473 -- |---------------------< return_legislation_code >-------------------------|
474 -- ---------------------------------------------------------------------------
475 --
476 Function return_legislation_code
477 (p_corps_extra_info_id in number
478 )
479 Return Varchar2 Is
480 --
481 -- Declare cursor
482 --
483 -- EDIT_HERE In the following cursor statement add join(s) between
484 -- pqh_corps_extra_info and PER_BUSINESS_GROUPS
485 -- so that the legislation_code for
486 -- the current business group context can be derived.
487 -- Remove this comment when the edit has been completed.
488 cursor csr_leg_code is
489 select pbg.legislation_code
490 from per_business_groups pbg
491 , pqh_corps_extra_info cei
492 -- , EDIT_HERE table_name(s) 333
493 where cei.corps_extra_info_id = p_corps_extra_info_id;
494 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
495 --
496 -- Declare local variables
497 --
498 l_legislation_code varchar2(150);
499 l_proc varchar2(72) := g_package||'return_legislation_code';
500 --
501 Begin
502 --
503 hr_utility.set_location('Entering:'|| l_proc, 10);
504 --
505 -- Ensure that all the mandatory parameter are not null
506 --
507 hr_api.mandatory_arg_error
508 (p_api_name => l_proc
509 ,p_argument => 'corps_extra_info_id'
510 ,p_argument_value => p_corps_extra_info_id
511 );
512 --
513 if ( nvl(pqh_cei_bus.g_corps_extra_info_id, hr_api.g_number)
514 = p_corps_extra_info_id) then
515 --
516 -- The legislation code has already been found with a previous
517 -- call to this function. Just return the value in the global
518 -- variable.
519 --
520 l_legislation_code := pqh_cei_bus.g_legislation_code;
521 hr_utility.set_location(l_proc, 20);
522 else
523 --
524 -- The ID is different to the last call to this function
525 -- or this is the first call to this function.
526 --
527 open csr_leg_code;
528 fetch csr_leg_code into l_legislation_code;
529 --
530 if csr_leg_code%notfound then
531 --
532 -- The primary key is invalid therefore we must error
533 --
534 close csr_leg_code;
535 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
536 fnd_message.raise_error;
537 end if;
538 hr_utility.set_location(l_proc,30);
539 --
540 -- Set the global variables so the values are
541 -- available for the next call to this function.
542 --
543 close csr_leg_code;
544 pqh_cei_bus.g_corps_extra_info_id := p_corps_extra_info_id;
545 pqh_cei_bus.g_legislation_code := l_legislation_code;
546 end if;
547 hr_utility.set_location(' Leaving:'|| l_proc, 40);
548 return l_legislation_code;
549 end return_legislation_code;
550 --
551 -- ----------------------------------------------------------------------------
552 -- |-----------------------------< chk_ddf >----------------------------------|
553 -- ----------------------------------------------------------------------------
554 --
555 -- Description:
556 -- Validates all the Developer Descriptive Flexfield values.
557 --
558 -- Prerequisites:
559 -- All other columns have been validated. Must be called as the
560 -- last step from insert_validate and update_validate.
561 --
562 -- In Arguments:
563 -- p_rec
564 --
565 -- Post Success:
566 -- If the Developer Descriptive Flexfield structure column and data values
567 -- are all valid this procedure will end normally and processing will
568 -- continue.
569 --
570 -- Post Failure:
571 -- If the Developer Descriptive Flexfield structure column value or any of
572 -- the data values are invalid then an application error is raised as
573 -- a PL/SQL exception.
574 --
575 -- Access Status:
576 -- Internal Row Handler Use Only.
577 --
578 -- ----------------------------------------------------------------------------
579 procedure chk_ddf
580 (p_rec in pqh_cei_shd.g_rec_type
581 ) is
582 --
583 l_proc varchar2(72) := g_package || 'chk_ddf';
584 --
585 begin
586 hr_utility.set_location('Entering:'||l_proc,10);
587 --
591 nvl(pqh_cei_shd.g_old_rec.information1, hr_api.g_varchar2) <>
588 if ((p_rec.corps_extra_info_id is not null) and (
589 nvl(pqh_cei_shd.g_old_rec.information_type, hr_api.g_varchar2) <>
590 nvl(p_rec.information_type, hr_api.g_varchar2) or
592 nvl(p_rec.information1, hr_api.g_varchar2) or
593 nvl(pqh_cei_shd.g_old_rec.information2, hr_api.g_varchar2) <>
594 nvl(p_rec.information2, hr_api.g_varchar2) or
595 nvl(pqh_cei_shd.g_old_rec.information3, hr_api.g_varchar2) <>
596 nvl(p_rec.information3, hr_api.g_varchar2) or
597 nvl(pqh_cei_shd.g_old_rec.information4, hr_api.g_varchar2) <>
598 nvl(p_rec.information4, hr_api.g_varchar2) or
599 nvl(pqh_cei_shd.g_old_rec.information5, hr_api.g_varchar2) <>
600 nvl(p_rec.information5, hr_api.g_varchar2) or
601 nvl(pqh_cei_shd.g_old_rec.information6, hr_api.g_varchar2) <>
602 nvl(p_rec.information6, hr_api.g_varchar2) or
603 nvl(pqh_cei_shd.g_old_rec.information7, hr_api.g_varchar2) <>
604 nvl(p_rec.information7, hr_api.g_varchar2) or
605 nvl(pqh_cei_shd.g_old_rec.information8, hr_api.g_varchar2) <>
606 nvl(p_rec.information8, hr_api.g_varchar2) or
607 nvl(pqh_cei_shd.g_old_rec.information9, hr_api.g_varchar2) <>
608 nvl(p_rec.information9, hr_api.g_varchar2) or
609 nvl(pqh_cei_shd.g_old_rec.information10, hr_api.g_varchar2) <>
610 nvl(p_rec.information10, hr_api.g_varchar2) or
611 nvl(pqh_cei_shd.g_old_rec.information11, hr_api.g_varchar2) <>
612 nvl(p_rec.information11, hr_api.g_varchar2) or
613 nvl(pqh_cei_shd.g_old_rec.information12, hr_api.g_varchar2) <>
614 nvl(p_rec.information12, hr_api.g_varchar2) or
615 nvl(pqh_cei_shd.g_old_rec.information13, hr_api.g_varchar2) <>
616 nvl(p_rec.information13, hr_api.g_varchar2) or
617 nvl(pqh_cei_shd.g_old_rec.information14, hr_api.g_varchar2) <>
618 nvl(p_rec.information14, hr_api.g_varchar2) or
619 nvl(pqh_cei_shd.g_old_rec.information15, hr_api.g_varchar2) <>
620 nvl(p_rec.information15, hr_api.g_varchar2) or
621 nvl(pqh_cei_shd.g_old_rec.information16, hr_api.g_varchar2) <>
622 nvl(p_rec.information16, hr_api.g_varchar2) or
623 nvl(pqh_cei_shd.g_old_rec.information17, hr_api.g_varchar2) <>
624 nvl(p_rec.information17, hr_api.g_varchar2) or
625 nvl(pqh_cei_shd.g_old_rec.information18, hr_api.g_varchar2) <>
626 nvl(p_rec.information18, hr_api.g_varchar2) or
627 nvl(pqh_cei_shd.g_old_rec.information19, hr_api.g_varchar2) <>
628 nvl(p_rec.information19, hr_api.g_varchar2) or
629 nvl(pqh_cei_shd.g_old_rec.information20, hr_api.g_varchar2) <>
630 nvl(p_rec.information20, hr_api.g_varchar2) or
631 nvl(pqh_cei_shd.g_old_rec.information21, hr_api.g_varchar2) <>
632 nvl(p_rec.information21, hr_api.g_varchar2) or
633 nvl(pqh_cei_shd.g_old_rec.information22, hr_api.g_varchar2) <>
634 nvl(p_rec.information22, hr_api.g_varchar2) or
635 nvl(pqh_cei_shd.g_old_rec.information23, hr_api.g_varchar2) <>
636 nvl(p_rec.information23, hr_api.g_varchar2) or
637 nvl(pqh_cei_shd.g_old_rec.information24, hr_api.g_varchar2) <>
638 nvl(p_rec.information24, hr_api.g_varchar2) or
639 nvl(pqh_cei_shd.g_old_rec.information25, hr_api.g_varchar2) <>
640 nvl(p_rec.information25, hr_api.g_varchar2) or
641 nvl(pqh_cei_shd.g_old_rec.information26, hr_api.g_varchar2) <>
642 nvl(p_rec.information26, hr_api.g_varchar2) or
643 nvl(pqh_cei_shd.g_old_rec.information27, hr_api.g_varchar2) <>
644 nvl(p_rec.information27, hr_api.g_varchar2) or
645 nvl(pqh_cei_shd.g_old_rec.information28, hr_api.g_varchar2) <>
646 nvl(p_rec.information28, hr_api.g_varchar2) or
647 nvl(pqh_cei_shd.g_old_rec.information29, hr_api.g_varchar2) <>
648 nvl(p_rec.information29, hr_api.g_varchar2) or
649 nvl(pqh_cei_shd.g_old_rec.information30, hr_api.g_varchar2) <>
650 nvl(p_rec.information30, hr_api.g_varchar2) or
651 nvl(pqh_cei_shd.g_old_rec.information_category, hr_api.g_varchar2) <>
652 nvl(p_rec.information_category, hr_api.g_varchar2) ))
653 or (p_rec.corps_extra_info_id is null) then
654 --
655 -- Only execute the validation if absolutely necessary:
656 -- a) During update, the structure column value or any
657 -- of the attribute values have actually changed.
658 -- b) During insert.
659 --
660 /*
661 hr_dflex_utility.ins_or_upd_descflex_attribs
662 (p_appl_short_name => 'PQH'
663 ,p_descflex_name => 'Further Corps Extra Information'
664 ,p_attribute1_name => 'INFORMATION_TYPE'
665 ,p_attribute1_value => p_rec.information_type
666 ,p_attribute2_name => 'INFORMATION1'
667 ,p_attribute2_value => p_rec.information1
668 ,p_attribute3_name => 'INFORMATION2'
669 ,p_attribute3_value => p_rec.information2
670 ,p_attribute4_name => 'INFORMATION3'
671 ,p_attribute4_value => p_rec.information3
672 ,p_attribute5_name => 'INFORMATION4'
673 ,p_attribute5_value => p_rec.information4
674 ,p_attribute6_name => 'INFORMATION5'
675 ,p_attribute6_value => p_rec.information5
676 ,p_attribute7_name => 'INFORMATION6'
677 ,p_attribute7_value => p_rec.information6
678 ,p_attribute8_name => 'INFORMATION7'
679 ,p_attribute8_value => p_rec.information7
683 ,p_attribute10_value => p_rec.information9
680 ,p_attribute9_name => 'INFORMATION8'
681 ,p_attribute9_value => p_rec.information8
682 ,p_attribute10_name => 'INFORMATION9'
684 ,p_attribute11_name => 'INFORMATION10'
685 ,p_attribute11_value => p_rec.information10
686 ,p_attribute12_name => 'INFORMATION11'
687 ,p_attribute12_value => p_rec.information11
688 ,p_attribute13_name => 'INFORMATION12'
689 ,p_attribute13_value => p_rec.information12
690 ,p_attribute14_name => 'INFORMATION13'
691 ,p_attribute14_value => p_rec.information13
692 ,p_attribute15_name => 'INFORMATION14'
693 ,p_attribute15_value => p_rec.information14
694 ,p_attribute16_name => 'INFORMATION15'
695 ,p_attribute16_value => p_rec.information15
696 ,p_attribute17_name => 'INFORMATION16'
697 ,p_attribute17_value => p_rec.information16
698 ,p_attribute18_name => 'INFORMATION17'
699 ,p_attribute18_value => p_rec.information17
700 ,p_attribute19_name => 'INFORMATION18'
701 ,p_attribute19_value => p_rec.information18
702 ,p_attribute20_name => 'INFORMATION19'
703 ,p_attribute20_value => p_rec.information19
704 ,p_attribute21_name => 'INFORMATION20'
705 ,p_attribute21_value => p_rec.information20
706 ,p_attribute22_name => 'INFORMATION21'
707 ,p_attribute22_value => p_rec.information21
708 ,p_attribute23_name => 'INFORMATION22'
709 ,p_attribute23_value => p_rec.information22
710 ,p_attribute24_name => 'INFORMATION23'
711 ,p_attribute24_value => p_rec.information23
712 ,p_attribute25_name => 'INFORMATION24'
713 ,p_attribute25_value => p_rec.information24
714 ,p_attribute26_name => 'INFORMATION25'
715 ,p_attribute26_value => p_rec.information25
716 ,p_attribute27_name => 'INFORMATION26'
717 ,p_attribute27_value => p_rec.information26
718 ,p_attribute28_name => 'INFORMATION27'
719 ,p_attribute28_value => p_rec.information27
720 ,p_attribute29_name => 'INFORMATION28'
721 ,p_attribute29_value => p_rec.information28
722 ,p_attribute30_name => 'INFORMATION29'
723 ,p_attribute30_value => p_rec.information29
724 ,p_attribute31_name => 'INFORMATION30'
725 ,p_attribute31_value => p_rec.information30
726 ,p_attribute_category => 'INFORMATION_CATEGORY'
727 );
728 */
729 null;
730 end if;
731 --
732 hr_utility.set_location(' Leaving:'||l_proc,20);
733 end chk_ddf;
734 --
735 -- ----------------------------------------------------------------------------
736 -- |------------------------------< chk_df >----------------------------------|
737 -- ----------------------------------------------------------------------------
738 --
739 -- Description:
740 -- Validates all the Descriptive Flexfield values.
741 --
742 -- Prerequisites:
743 -- All other columns have been validated. Must be called as the
744 -- last step from insert_validate and update_validate.
745 --
746 -- In Arguments:
747 -- p_rec
748 --
749 -- Post Success:
750 -- If the Descriptive Flexfield structure column and data values are
751 -- all valid this procedure will end normally and processing will
752 -- continue.
753 --
754 -- Post Failure:
755 -- If the Descriptive Flexfield structure column value or any of
756 -- the data values are invalid then an application error is raised as
757 -- a PL/SQL exception.
758 --
759 -- Access Status:
760 -- Internal Row Handler Use Only.
761 --
762 -- ----------------------------------------------------------------------------
763 procedure chk_df
764 (p_rec in pqh_cei_shd.g_rec_type
765 ) is
766 --
767 l_proc varchar2(72) := g_package || 'chk_df';
768 --
769 begin
770 hr_utility.set_location('Entering:'||l_proc,10);
771 --
772 if ((p_rec.corps_extra_info_id is not null) and (
773 nvl(pqh_cei_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
774 nvl(p_rec.attribute1, hr_api.g_varchar2) or
775 nvl(pqh_cei_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
776 nvl(p_rec.attribute2, hr_api.g_varchar2) or
777 nvl(pqh_cei_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
778 nvl(p_rec.attribute3, hr_api.g_varchar2) or
779 nvl(pqh_cei_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
780 nvl(p_rec.attribute4, hr_api.g_varchar2) or
781 nvl(pqh_cei_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
782 nvl(p_rec.attribute5, hr_api.g_varchar2) or
783 nvl(pqh_cei_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
784 nvl(p_rec.attribute6, hr_api.g_varchar2) or
785 nvl(pqh_cei_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
786 nvl(p_rec.attribute7, hr_api.g_varchar2) or
790 nvl(p_rec.attribute9, hr_api.g_varchar2) or
787 nvl(pqh_cei_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
788 nvl(p_rec.attribute8, hr_api.g_varchar2) or
789 nvl(pqh_cei_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
791 nvl(pqh_cei_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
792 nvl(p_rec.attribute10, hr_api.g_varchar2) or
793 nvl(pqh_cei_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
794 nvl(p_rec.attribute11, hr_api.g_varchar2) or
795 nvl(pqh_cei_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
796 nvl(p_rec.attribute12, hr_api.g_varchar2) or
797 nvl(pqh_cei_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
798 nvl(p_rec.attribute13, hr_api.g_varchar2) or
799 nvl(pqh_cei_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
800 nvl(p_rec.attribute14, hr_api.g_varchar2) or
801 nvl(pqh_cei_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
802 nvl(p_rec.attribute15, hr_api.g_varchar2) or
803 nvl(pqh_cei_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
804 nvl(p_rec.attribute16, hr_api.g_varchar2) or
805 nvl(pqh_cei_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
806 nvl(p_rec.attribute17, hr_api.g_varchar2) or
807 nvl(pqh_cei_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
808 nvl(p_rec.attribute18, hr_api.g_varchar2) or
809 nvl(pqh_cei_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
810 nvl(p_rec.attribute19, hr_api.g_varchar2) or
811 nvl(pqh_cei_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
812 nvl(p_rec.attribute20, hr_api.g_varchar2) or
813 nvl(pqh_cei_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
814 nvl(p_rec.attribute21, hr_api.g_varchar2) or
815 nvl(pqh_cei_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
816 nvl(p_rec.attribute22, hr_api.g_varchar2) or
817 nvl(pqh_cei_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
818 nvl(p_rec.attribute23, hr_api.g_varchar2) or
819 nvl(pqh_cei_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
820 nvl(p_rec.attribute24, hr_api.g_varchar2) or
821 nvl(pqh_cei_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
822 nvl(p_rec.attribute25, hr_api.g_varchar2) or
823 nvl(pqh_cei_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
824 nvl(p_rec.attribute26, hr_api.g_varchar2) or
825 nvl(pqh_cei_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
826 nvl(p_rec.attribute27, hr_api.g_varchar2) or
827 nvl(pqh_cei_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
828 nvl(p_rec.attribute28, hr_api.g_varchar2) or
829 nvl(pqh_cei_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
830 nvl(p_rec.attribute29, hr_api.g_varchar2) or
831 nvl(pqh_cei_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
832 nvl(p_rec.attribute30, hr_api.g_varchar2) or
833 nvl(pqh_cei_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
834 nvl(p_rec.attribute_category, hr_api.g_varchar2) ))
835 or (p_rec.corps_extra_info_id is null) then
836 --
837 -- Only execute the validation if absolutely necessary:
838 -- a) During update, the structure column value or any
839 -- of the attribute values have actually changed.
840 -- b) During insert.
841 --
842 /*
843 hr_dflex_utility.ins_or_upd_descflex_attribs
844 (p_appl_short_name => 'PQH'
845 ,p_descflex_name => 'EDIT_HERE: Enter descflex name'
846 ,p_attribute1_name => 'ATTRIBUTE1'
847 ,p_attribute1_value => p_rec.attribute1
848 ,p_attribute2_name => 'ATTRIBUTE2'
849 ,p_attribute2_value => p_rec.attribute2
850 ,p_attribute3_name => 'ATTRIBUTE3'
851 ,p_attribute3_value => p_rec.attribute3
852 ,p_attribute4_name => 'ATTRIBUTE4'
853 ,p_attribute4_value => p_rec.attribute4
854 ,p_attribute5_name => 'ATTRIBUTE5'
855 ,p_attribute5_value => p_rec.attribute5
856 ,p_attribute6_name => 'ATTRIBUTE6'
857 ,p_attribute6_value => p_rec.attribute6
858 ,p_attribute7_name => 'ATTRIBUTE7'
859 ,p_attribute7_value => p_rec.attribute7
860 ,p_attribute8_name => 'ATTRIBUTE8'
861 ,p_attribute8_value => p_rec.attribute8
862 ,p_attribute9_name => 'ATTRIBUTE9'
863 ,p_attribute9_value => p_rec.attribute9
864 ,p_attribute10_name => 'ATTRIBUTE10'
865 ,p_attribute10_value => p_rec.attribute10
866 ,p_attribute11_name => 'ATTRIBUTE11'
867 ,p_attribute11_value => p_rec.attribute11
868 ,p_attribute12_name => 'ATTRIBUTE12'
869 ,p_attribute12_value => p_rec.attribute12
870 ,p_attribute13_name => 'ATTRIBUTE13'
871 ,p_attribute13_value => p_rec.attribute13
872 ,p_attribute14_name => 'ATTRIBUTE14'
873 ,p_attribute14_value => p_rec.attribute14
874 ,p_attribute15_name => 'ATTRIBUTE15'
875 ,p_attribute15_value => p_rec.attribute15
876 ,p_attribute16_name => 'ATTRIBUTE16'
877 ,p_attribute16_value => p_rec.attribute16
878 ,p_attribute17_name => 'ATTRIBUTE17'
879 ,p_attribute17_value => p_rec.attribute17
883 ,p_attribute19_value => p_rec.attribute19
880 ,p_attribute18_name => 'ATTRIBUTE18'
881 ,p_attribute18_value => p_rec.attribute18
882 ,p_attribute19_name => 'ATTRIBUTE19'
884 ,p_attribute20_name => 'ATTRIBUTE20'
885 ,p_attribute20_value => p_rec.attribute20
886 ,p_attribute21_name => 'ATTRIBUTE21'
887 ,p_attribute21_value => p_rec.attribute21
888 ,p_attribute22_name => 'ATTRIBUTE22'
889 ,p_attribute22_value => p_rec.attribute22
890 ,p_attribute23_name => 'ATTRIBUTE23'
891 ,p_attribute23_value => p_rec.attribute23
892 ,p_attribute24_name => 'ATTRIBUTE24'
893 ,p_attribute24_value => p_rec.attribute24
894 ,p_attribute25_name => 'ATTRIBUTE25'
895 ,p_attribute25_value => p_rec.attribute25
896 ,p_attribute26_name => 'ATTRIBUTE26'
897 ,p_attribute26_value => p_rec.attribute26
898 ,p_attribute27_name => 'ATTRIBUTE27'
899 ,p_attribute27_value => p_rec.attribute27
900 ,p_attribute28_name => 'ATTRIBUTE28'
901 ,p_attribute28_value => p_rec.attribute28
902 ,p_attribute29_name => 'ATTRIBUTE29'
903 ,p_attribute29_value => p_rec.attribute29
904 ,p_attribute30_name => 'ATTRIBUTE30'
905 ,p_attribute30_value => p_rec.attribute30
906 ,p_attribute_category => 'ATTRIBUTE_CATEGORY'
907 );
908 */
909 null;
910 end if;
911 --
912 hr_utility.set_location(' Leaving:'||l_proc,20);
913 end chk_df;
914 --
915 -- ----------------------------------------------------------------------------
916 -- |-----------------------< chk_non_updateable_args >------------------------|
917 -- ----------------------------------------------------------------------------
918 -- {Start Of Comments}
919 --
920 -- Description:
921 -- This procedure is used to ensure that non updateable attributes have
922 -- not been updated. If an attribute has been updated an error is generated.
923 --
924 -- Pre Conditions:
925 -- g_old_rec has been populated with details of the values currently in
926 -- the database.
927 --
928 -- In Arguments:
929 -- p_rec has been populated with the updated values the user would like the
930 -- record set to.
931 --
932 -- Post Success:
933 -- Processing continues if all the non updateable attributes have not
934 -- changed.
935 --
936 -- Post Failure:
937 -- An application error is raised if any of the non updatable attributes
938 -- have been altered.
939 --
940 -- {End Of Comments}
941 -- ----------------------------------------------------------------------------
942 Procedure chk_non_updateable_args
943 (p_effective_date in date
944 ,p_rec in pqh_cei_shd.g_rec_type
945 ) IS
946 --
947 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
948 --
949 Begin
950 --
951 -- Only proceed with the validation if a row exists for the current
952 -- record in the HR Schema.
953 --
954 IF NOT pqh_cei_shd.api_updating
955 (p_corps_extra_info_id => p_rec.corps_extra_info_id
956 ,p_object_version_number => p_rec.object_version_number
957 ) THEN
958 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
959 fnd_message.set_token('PROCEDURE ', l_proc);
960 fnd_message.set_token('STEP ', '5');
961 fnd_message.raise_error;
962 END IF;
963 --
964 -- EDIT_HERE: Add checks to ensure non-updateable args have
965 -- not been updated.
966 --
967 End chk_non_updateable_args;
968 --
969 -- ----------------------------------------------------------------------------
970 -- |---------------------------< insert_validate >----------------------------|
971 -- ----------------------------------------------------------------------------
972 Procedure insert_validate
973 (p_effective_date in date
974 ,p_rec in pqh_cei_shd.g_rec_type
975 ) is
976 --
977 l_proc varchar2(72) := g_package||'insert_validate';
978 --
979 Begin
980 hr_utility.set_location('Entering:'||l_proc, 5);
981 --
982 -- Call all supporting business operations
983 --
984 --
985 -- EDIT_HERE: As this table does not have a mandatory business_group_id
986 -- column, ensure client_info is populated by calling a suitable
987 -- ???_???_bus.set_security_group_id procedure, or add one of the following
988 -- comments:
989 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
990 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
991 --
992 -- Validate Dependent Attributes
993 --
994 --
995 chk_corps_org(p_rec) ;
996 --
997 chk_corps_grades_dup(p_rec) ;
998 --
999 chk_corps_grades(p_rec) ;
1000 --
1001 chk_corps_rules(p_rec) ;
1002 --
1003 chk_corps_training(p_rec) ;
1004 --
1005 chk_corps_exam(p_rec) ;
1006 --
1007 chk_corps_doc(p_rec) ;
1008 --
1009 -- pqh_cei_bus.chk_ddf(p_rec);
1010 --
1014 End insert_validate;
1011 -- pqh_cei_bus.chk_df(p_rec);
1012 --
1013 hr_utility.set_location(' Leaving:'||l_proc, 10);
1015 --
1016 -- ----------------------------------------------------------------------------
1017 -- |---------------------------< update_validate >----------------------------|
1018 -- ----------------------------------------------------------------------------
1019 Procedure update_validate
1020 (p_effective_date in date
1021 ,p_rec in pqh_cei_shd.g_rec_type
1022 ) is
1023 --
1024 l_proc varchar2(72) := g_package||'update_validate';
1025 --
1026 Begin
1027 hr_utility.set_location('Entering:'||l_proc, 5);
1028 --
1029 -- Call all supporting business operations
1030 --
1031 --
1032 -- EDIT_HERE: As this table does not have a mandatory business_group_id
1033 -- column, ensure client_info is populated by calling a suitable
1034 -- ???_???_bus.set_security_group_id procedure, or add one of the following
1035 -- comments:
1036 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
1037 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
1038 --
1039 -- Validate Dependent Attributes
1040 --
1041 chk_non_updateable_args
1042 (p_effective_date => p_effective_date
1043 ,p_rec => p_rec
1044 );
1045 --
1046 --
1047 chk_corps_org (p_rec) ;
1048 --
1049 chk_corps_grades (p_rec) ;
1050 --
1051 chk_corps_rules(p_rec) ;
1052 --
1053 chk_corps_training(p_rec) ;
1054 --
1055 chk_corps_exam(p_rec) ;
1056 --
1057 chk_corps_doc(p_rec) ;
1058 --
1059 -- pqh_cei_bus.chk_ddf(p_rec);
1060 --
1061 -- pqh_cei_bus.chk_df(p_rec);
1062 --
1063 hr_utility.set_location(' Leaving:'||l_proc, 10);
1064 End update_validate;
1065 --
1066 -- ----------------------------------------------------------------------------
1067 -- |---------------------------< delete_validate >----------------------------|
1068 -- ----------------------------------------------------------------------------
1069 Procedure delete_validate
1070 (p_rec in pqh_cei_shd.g_rec_type
1071 ) is
1072 --
1073 l_proc varchar2(72) := g_package||'delete_validate';
1074 --
1075 Begin
1076 hr_utility.set_location('Entering:'||l_proc, 5);
1077 --
1078 -- Call all supporting business operations
1079 --
1080 hr_utility.set_location(' Leaving:'||l_proc, 10);
1081 End delete_validate;
1082 --
1083 end pqh_cei_bus;