[Home] [Help]
PACKAGE BODY: APPS.PQH_REI_BUS
Source
1 Package Body pqh_rei_bus as
2 /* $Header: pqreirhi.pkb 115.3 2002/12/03 20:42:51 rpasapul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rei_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_role_extra_info_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_role_extra_info_id in number
22 ,p_associated_column1 in varchar2
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id
29 from per_business_groups pbg
30 , pqh_role_extra_info rei
31 , pqh_roles rls
32 where rei.role_extra_info_id = p_role_extra_info_id
33 and rei.role_id = rls.role_id
34 and pbg.business_group_id = rls.business_group_id;
35
36 -- Declare local variables
37 --
38 l_security_group_id number;
39 l_proc varchar2(72) := g_package||'set_security_group_id';
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'role_extra_info_id'
50 ,p_argument_value => p_role_extra_info_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id;
55 --
56 if csr_sec_grp%notfound then
57 --
58 close csr_sec_grp;
59 --
60 -- The primary key is invalid therefore we must error
61 --
62 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
63 hr_multi_message.add
64 (p_associated_column1
65 => nvl(p_associated_column1,'ROLE_EXTRA_INFO_ID')
66 );
67 --
68 else
69 close csr_sec_grp;
70 --
71 -- Set the security_group_id in CLIENT_INFO
72 --
73 hr_api.set_security_group_id
74 (p_security_group_id => l_security_group_id
75 );
76 end if;
77 --
78 hr_utility.set_location(' Leaving:'|| l_proc, 20);
79 --
80 end set_security_group_id;
81 --
82 -- ---------------------------------------------------------------------------
83 -- |---------------------< return_legislation_code >-------------------------|
84 -- ---------------------------------------------------------------------------
85 --
86 Function return_legislation_code
87 (p_role_extra_info_id in number
88 )
89 Return Varchar2 Is
90 --
91 -- Declare cursor
92 --
93 cursor csr_leg_code is
94 select pbg.legislation_code
95 from per_business_groups pbg
96 , pqh_role_extra_info rei
97 , pqh_roles rls
98 where rei.role_extra_info_id = p_role_extra_info_id
99 and rei.role_id = rls.role_id
100 and pbg.business_group_id = rls.business_group_id;
101 --
102
103 -- Declare local variables
104 --
105 l_legislation_code varchar2(150);
106 l_proc varchar2(72) := g_package||'return_legislation_code';
107 --
108 Begin
109 --
110 hr_utility.set_location('Entering:'|| l_proc, 10);
111 --
112 -- Ensure that all the mandatory parameter are not null
113 --
114 hr_api.mandatory_arg_error
115 (p_api_name => l_proc
116 ,p_argument => 'role_extra_info_id'
117 ,p_argument_value => p_role_extra_info_id
118 );
119 --
120 if ( nvl(pqh_rei_bus.g_role_extra_info_id, hr_api.g_number)
121 = p_role_extra_info_id) then
122 --
123 -- The legislation code has already been found with a previous
124 -- call to this function. Just return the value in the global
125 -- variable.
126 --
127 l_legislation_code := pqh_rei_bus.g_legislation_code;
128 hr_utility.set_location(l_proc, 20);
129 else
130 --
131 -- The ID is different to the last call to this function
132 -- or this is the first call to this function.
133 --
134 open csr_leg_code;
135 fetch csr_leg_code into l_legislation_code;
136 --
137 if csr_leg_code%notfound then
138 --
139 -- The primary key is invalid therefore we must error
140 --
141 close csr_leg_code;
142 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
143 fnd_message.raise_error;
144 end if;
145 hr_utility.set_location(l_proc,30);
146 --
147 -- Set the global variables so the values are
148 -- available for the next call to this function.
149 --
150 close csr_leg_code;
151 pqh_rei_bus.g_role_extra_info_id := p_role_extra_info_id;
152 pqh_rei_bus.g_legislation_code := l_legislation_code;
153 end if;
154 hr_utility.set_location(' Leaving:'|| l_proc, 40);
155 return l_legislation_code;
156 end return_legislation_code;
157 --
158 -- ----------------------------------------------------------------------------
159 -- |--------------------------<chk_role_info_type >-----------------------|
160 -- ----------------------------------------------------------------------------
161 -- {Start Of Comments}
162 --
163 -- Description:
164 -- Validates that the role information type exists in table
165 -- pqh_role_info_types where active_inactive_flag is 'Y'.
166 --
167 -- Pre Conditions:
168 -- Data must be existed in table pqh_role_info_types.
169 --
170 -- In Parameters:
171 -- p_information_type
172 --
173 -- Post Success:
174 -- Processing continues.
175 --
176 -- Post Failure:
177 -- An application error will be raised and processing is terminated.
178 --
179 -- Developer Implementation Notes:
180 -- For insert, your business rules should be executed from this procedure and
181 -- should ideally (unless really necessary) just be straight procedure or
182 -- function calls. Try and avoid using conditional branching logic.
183 --
184 -- Access Status:
185 -- Internal Table Handler Use Only.
186 --
187 -- {End Of Comments}
188 -- ----------------------------------------------------------------------------
189
190 Procedure chk_role_info_type
191 (p_information_type in pqh_role_info_types.information_type%type
192 ) is
193 --
194 l_proc varchar2(72) := g_package||'chk_role_info_type';
195 l_flag pqh_role_info_types.active_inactive_flag%type;
196 --
197 cursor c_role_info_type (code varchar2) is
198 select reit.active_inactive_flag
199 from pqh_role_info_types reit
200 where reit.information_type = code;
201 --
202 Begin
203 hr_utility.set_location('Entering:'||l_proc, 1);
204 --
205 -- Check mandatory parameters have been set
206 --
207 hr_api.mandatory_arg_error
208 (
209 p_api_name => l_proc,
210 p_argument => 'information_type',
211 p_argument_value => p_information_type
212 );
213 --
214 hr_utility.set_location(l_proc, 2);
215 --
216 -- Check that the ACTIVE_INACTIVE_FLAG of role
217 -- Information type is active.
218 --
219 open c_role_info_type (p_information_type);
220 fetch c_role_info_type into l_flag;
221 if c_role_info_type%notfound then
222 close c_role_info_type;
223 hr_utility.set_message(800, 'HR_INV_INFO_TYPE');
224 hr_utility.raise_error;
225 end if;
226 close c_role_info_type;
227 --
228 if l_flag = 'N' then
229 hr_utility.set_message(800, 'HR_INACTIVE_INFO_TYPE');
230 hr_utility.raise_error;
231 end if;
232 --
233 hr_utility.set_location(' Leaving:'||l_proc, 3);
234 --
235 End chk_role_info_type;
236 --
237 --
238 -- ----------------------------------------------------------------------------
239 -- |---------------------------< chk_role_id >----------------------------|
240 -- ----------------------------------------------------------------------------
241 -- {Start Of Comments}
242 --
243 -- Description:
244 -- Verify that the value in role_ID is in the pqh_roles Table.
245 --
246 -- Pre Conditions:
247 --
248 --
249 -- In Parameters:
250 -- p_role_id
251 --
252 -- Post Success:
253 -- Processing continues.
254 --
255 -- Post Failure:
256 -- An application error will be raised and processing is terminated.
257 --
258 -- Developer Implementation Notes:
259 -- For insert, your business rules should be executed from this procedure and
260 -- should ideally (unless really necessary) just be straight procedure or
261 -- function calls. Try and avoid using conditional branching logic.
262 --
263 -- Access Status:
264 -- Internal Table Handler Use Only.
265 --
266 -- {End Of Comments}
267 -- ----------------------------------------------------------------------------
268
269 Procedure chk_role_id
270 (
271 p_role_id in pqh_role_extra_info.role_id%type
272 ) is
273 --
274 l_proc varchar2(72) := g_package||'chk_role_id';
275 l_dummy varchar2(1);
276
277 cursor c_valid_role (id number) is
278 select 'x'
279 from pqh_roles
280 where role_id = id;
281 --
282 Begin
283 hr_utility.set_location('Entering:'||l_proc, 1);
284 --
285 -- Check mandatory parameters have been set
286 --
287 hr_api.mandatory_arg_error
288 (
289 p_api_name => l_proc,
290 p_argument => 'role_id',
291 p_argument_value => p_role_id
292 );
293 hr_utility.set_location(l_proc, 2);
294 --
295 -- Check that the role_id is in the pqh_roles table.
296 --
297 open c_valid_role (p_role_id);
298 fetch c_valid_role into l_dummy;
299 if c_valid_role%notfound then
300 close c_valid_role;
301 hr_utility.set_message(8302, 'PQH_INV_ROLE_ID');
302 hr_utility.raise_error;
303 end if;
304 close c_valid_role;
305 --
306 hr_utility.set_location(' Leaving:'||l_proc, 3);
307 --
308 End chk_role_id;
309 --
310 -- ----------------------------------------------------------------------------
311 -- |--------------------< chk_multiple_occurences_flag >----------------------|
312 -- ----------------------------------------------------------------------------
313 -- {Start Of Comments)
314 --
315 -- Description:
316 -- Verify that the number of rows should not exceed one when
317 -- multiple_occurences_flag = 'N'.
318 --
319 -- Pre Conditions:
320 -- This procedure should execute after procedure chk_information_type.
321 --
322 -- In Parameters:
323 -- p_information_type
324 -- p_role_id
325 --
326 -- Post Success:
327 -- Processing continues.
328 --
329 -- Post Failure:
330 -- An application error will be raised and processing is terminated.
331 --
332 -- Developer Implementation Notes:
333 -- For insert, your business rules should be executed from this procedure and
334 -- should ideally (unless really necessary) just be straight procedure or
335 -- function calls. Try and avoid using conditional branching logic.
336 --
337 -- Access Status:
338 -- Internal Table Handler Use Only.
339 --
340 -- {End Of Comments}
341 -- ----------------------------------------------------------------------------
342 Procedure chk_multiple_occurences_flag
343 (p_information_type in pqh_role_extra_info.information_type%type
344 ,p_role_id in pqh_role_extra_info.role_id%type
345 ) is
346
347 l_proc varchar2(72) := g_package||'chk_multiple_occurences_flag';
348 l_multi_occur_flag pqh_role_info_types.multiple_occurences_flag%type;
349 l_dummy varchar2(1);
350 l_found_rei boolean;
351 --
352 cursor c_multi_occur_flag (code varchar2) is
353 select multiple_occurences_flag
354 from pqh_role_info_types
355 where information_type = code;
356 --
357 cursor c_get_row (code varchar2, id number) is
358 select 'x'
359 from pqh_role_extra_info
360 where information_type = code
361 and role_id = id;
362 --
363 Begin
364 hr_utility.set_location('Entering:'||l_proc, 5);
365 --
366 open c_multi_occur_flag (p_information_type);
367 fetch c_multi_occur_flag into l_multi_occur_flag;
368 --
369 -- The following case should not happen since procedure
370 -- chk_information_type should capture this error.
371 --
372 if c_multi_occur_flag%notfound then
373 close c_multi_occur_flag;
374 hr_utility.set_message(800, 'HR_INV_INFO_TYPE');
375 hr_utility.raise_error;
376 end if;
377 --
378 close c_multi_occur_flag;
379 --
380 hr_utility.set_location(l_proc, 10);
381 --
382 open c_get_row(p_information_type, p_role_id);
383 fetch c_get_row into l_dummy;
384 if c_get_row%notfound then
385 l_found_rei := FALSE;
386 else
387 l_found_rei := TRUE;
388 end if;
389 close c_get_row;
390 --
391 if l_found_rei and l_multi_occur_flag = 'N' then
392 hr_utility.set_message(800, 'HR_MORE_THAN_1_EXTRA_INFO');
393 hr_utility.raise_error;
394 end if;
395 --
396 hr_utility.set_location(' Leaving:'||l_proc, 15);
397 --
398 End chk_multiple_occurences_flag;
399 --
400 -- ----------------------------------------------------------------------------
401 -- |-----------------------< chk_non_updateable_args >------------------------|
402 -- ----------------------------------------------------------------------------
403 -- {Start Of Comments}
404 --
405 -- Description:
406 -- Verify that the non updateable arguments not changed.
407 --
408 -- Pre Conditions:
409 --
410 --
411 -- In Parameters:
412 -- p_rec
413 --
414 -- Post Success:
415 -- Processing continues.
416 --
417 -- Post Failure:
418 -- An application error will be raised and processing is terminated.
419 --
420 -- Developer Implementation Notes:
421 -- For insert, your business rules should be executed from this procedure and
425 -- Access Status:
422 -- should ideally (unless really necessary) just be straight procedure or
423 -- function calls. Try and avoid using conditional branching logic.
424 --
426 -- Internal Table Handler Use Only.
427 --
428 -- {End Of Comments}
429
430 -- ----------------------------------------------------------------------------
431 Procedure chk_non_updateable_args (p_rec in pqh_rei_shd.g_rec_type) is
432
433 --
434 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
435 l_error exception;
436 l_argument varchar2(30);
437 --
438 Begin
439 hr_utility.set_location('Entering:'||l_proc, 10);
440 --
441 -- Only proceed with validation if a row exists for
442 -- the current record in the HR Schema
443 --
444 if not pqh_rei_shd.api_updating
445 (p_role_extra_info_id => p_rec.role_extra_info_id
446 ,p_object_version_number => p_rec.object_version_number) then
447 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
448 hr_utility.set_message_token('PROCEDURE', l_proc);
449 hr_utility.set_message_token('STEP', '20');
450 end if;
451 --
452 hr_utility.set_location(l_proc, 30);
453 --
454 if nvl(p_rec.information_type, hr_api.g_varchar2) <>
455 nvl(pqh_rei_shd.g_old_rec.information_type, hr_api.g_varchar2) then
456 l_argument := 'information_type';
457 raise l_error;
458 end if;
459 --
460 hr_utility.set_location(l_proc, 40);
461 --
462 if nvl(p_rec.role_id, hr_api.g_number) <>
463 nvl(pqh_rei_shd.g_old_rec.role_id, hr_api.g_number) then
464 l_argument := 'role_id';
465 raise l_error;
466 end if;
467 --
468 hr_utility.set_location(' Leaving:'||l_proc, 50);
469 exception
470 when l_error then
471 hr_api.argument_changed_error
472 (p_api_name => l_proc
473 ,p_argument => l_argument
474 );
475 hr_utility.set_location(l_proc, 60);
476 when others then
477 hr_utility.set_location(l_proc, 70);
478 raise;
479 end chk_non_updateable_args;
480
481
482 -- ----------------------------------------------------------------------------
483 -- |-----------------------------< chk_ddf >----------------------------------|
484 -- ----------------------------------------------------------------------------
485 --
486 -- Description:
487 -- Validates all the Developer Descriptive Flexfield values.
488 --
489 -- Prerequisites:
490 -- All other columns have been validated. Must be called as the
491 -- last step from insert_validate and update_validate.
492 --
493 -- In Arguments:
494 -- p_rec
495 --
496 -- Post Success:
497 -- If the Developer Descriptive Flexfield structure column and data values
498 -- are all valid this procedure will end normally and processing will
499 -- continue.
500 --
501 -- Post Failure:
502 -- If the Developer Descriptive Flexfield structure column value or any of
503 -- the data values are invalid then an application error is raised as
504 -- a PL/SQL exception.
505 --
506 -- Access Status:
507 -- Internal Row Handler Use Only.
508 --
509
510 procedure chk_ddf
511 (p_rec in pqh_rei_shd.g_rec_type
512 ) is
513 --
514 l_proc varchar2(72) := g_package || 'chk_ddf';
515 --
516 begin
520 nvl(pqh_rei_shd.g_old_rec.information_type, hr_api.g_varchar2) <>
517 hr_utility.set_location('Entering:'||l_proc,10);
518 --
519 if ((p_rec.role_extra_info_id is not null) and (
521 nvl(p_rec.information_type, hr_api.g_varchar2) or
522 nvl(pqh_rei_shd.g_old_rec.information_category, hr_api.g_varchar2) <>
523 nvl(p_rec.information_category, hr_api.g_varchar2) or
524 nvl(pqh_rei_shd.g_old_rec.information1, hr_api.g_varchar2) <>
525 nvl(p_rec.information1, hr_api.g_varchar2) or
526 nvl(pqh_rei_shd.g_old_rec.information2, hr_api.g_varchar2) <>
527 nvl(p_rec.information2, hr_api.g_varchar2) or
528 nvl(pqh_rei_shd.g_old_rec.information3, hr_api.g_varchar2) <>
529 nvl(p_rec.information3, hr_api.g_varchar2) or
530 nvl(pqh_rei_shd.g_old_rec.information4, hr_api.g_varchar2) <>
531 nvl(p_rec.information4, hr_api.g_varchar2) or
532 nvl(pqh_rei_shd.g_old_rec.information5, hr_api.g_varchar2) <>
533 nvl(p_rec.information5, hr_api.g_varchar2) or
534 nvl(pqh_rei_shd.g_old_rec.information6, hr_api.g_varchar2) <>
535 nvl(p_rec.information6, hr_api.g_varchar2) or
536 nvl(pqh_rei_shd.g_old_rec.information7, hr_api.g_varchar2) <>
537 nvl(p_rec.information7, hr_api.g_varchar2) or
538 nvl(pqh_rei_shd.g_old_rec.information8, hr_api.g_varchar2) <>
539 nvl(p_rec.information8, hr_api.g_varchar2) or
540 nvl(pqh_rei_shd.g_old_rec.information9, hr_api.g_varchar2) <>
541 nvl(p_rec.information9, hr_api.g_varchar2) or
542 nvl(pqh_rei_shd.g_old_rec.information10, hr_api.g_varchar2) <>
543 nvl(p_rec.information10, hr_api.g_varchar2) or
544 nvl(pqh_rei_shd.g_old_rec.information11, hr_api.g_varchar2) <>
545 nvl(p_rec.information11, hr_api.g_varchar2) or
546 nvl(pqh_rei_shd.g_old_rec.information12, hr_api.g_varchar2) <>
547 nvl(p_rec.information12, hr_api.g_varchar2) or
548 nvl(pqh_rei_shd.g_old_rec.information13, hr_api.g_varchar2) <>
549 nvl(p_rec.information13, hr_api.g_varchar2) or
550 nvl(pqh_rei_shd.g_old_rec.information14, hr_api.g_varchar2) <>
551 nvl(p_rec.information14, hr_api.g_varchar2) or
552 nvl(pqh_rei_shd.g_old_rec.information15, hr_api.g_varchar2) <>
553 nvl(p_rec.information15, hr_api.g_varchar2) or
554 nvl(pqh_rei_shd.g_old_rec.information16, hr_api.g_varchar2) <>
555 nvl(p_rec.information16, hr_api.g_varchar2) or
556 nvl(pqh_rei_shd.g_old_rec.information17, hr_api.g_varchar2) <>
557 nvl(p_rec.information17, hr_api.g_varchar2) or
558 nvl(pqh_rei_shd.g_old_rec.information18, hr_api.g_varchar2) <>
559 nvl(p_rec.information18, hr_api.g_varchar2) or
560 nvl(pqh_rei_shd.g_old_rec.information19, hr_api.g_varchar2) <>
561 nvl(p_rec.information19, hr_api.g_varchar2) or
562 nvl(pqh_rei_shd.g_old_rec.information20, hr_api.g_varchar2) <>
563 nvl(p_rec.information20, hr_api.g_varchar2) or
564 nvl(pqh_rei_shd.g_old_rec.information21, hr_api.g_varchar2) <>
565 nvl(p_rec.information21, hr_api.g_varchar2) or
566 nvl(pqh_rei_shd.g_old_rec.information22, hr_api.g_varchar2) <>
567 nvl(p_rec.information22, hr_api.g_varchar2) or
568 nvl(pqh_rei_shd.g_old_rec.information23, hr_api.g_varchar2) <>
569 nvl(p_rec.information23, hr_api.g_varchar2) or
570 nvl(pqh_rei_shd.g_old_rec.information24, hr_api.g_varchar2) <>
571 nvl(p_rec.information24, hr_api.g_varchar2) or
572 nvl(pqh_rei_shd.g_old_rec.information25, hr_api.g_varchar2) <>
573 nvl(p_rec.information25, hr_api.g_varchar2) or
574 nvl(pqh_rei_shd.g_old_rec.information26, hr_api.g_varchar2) <>
575 nvl(p_rec.information26, hr_api.g_varchar2) or
576 nvl(pqh_rei_shd.g_old_rec.information27, hr_api.g_varchar2) <>
577 nvl(p_rec.information27, hr_api.g_varchar2) or
578 nvl(pqh_rei_shd.g_old_rec.information28, hr_api.g_varchar2) <>
579 nvl(p_rec.information28, hr_api.g_varchar2) or
580 nvl(pqh_rei_shd.g_old_rec.information29, hr_api.g_varchar2) <>
581 nvl(p_rec.information29, hr_api.g_varchar2) or
582 nvl(pqh_rei_shd.g_old_rec.information30, hr_api.g_varchar2) <>
583 nvl(p_rec.information30, hr_api.g_varchar2) )) or
584 (p_rec.role_extra_info_id is null) then
585 --
586 -- Only execute the validation if absolutely necessary:
587 -- a) During update, the structure column value or any
588 -- of the attribute values have actually changed.
589 -- b) During insert.
590 --
591 hr_dflex_utility.ins_or_upd_descflex_attribs
595 ,p_attribute1_name => 'INFORMATION1'
592 (p_appl_short_name => 'PQH'
593 ,p_descflex_name => 'Extra Role Info DDF'
594 ,p_attribute_category => p_rec.information_category
596 ,p_attribute1_value => p_rec.information1
597 ,p_attribute2_name => 'INFORMATION2'
598 ,p_attribute2_value => p_rec.information2
599 ,p_attribute3_name => 'INFORMATION3'
600 ,p_attribute3_value => p_rec.information3
601 ,p_attribute4_name => 'INFORMATION4'
602 ,p_attribute4_value => p_rec.information4
603 ,p_attribute5_name => 'INFORMATION5'
604 ,p_attribute5_value => p_rec.information5
605 ,p_attribute6_name => 'INFORMATION6'
606 ,p_attribute6_value => p_rec.information6
607 ,p_attribute7_name => 'INFORMATION7'
608 ,p_attribute7_value => p_rec.information7
609 ,p_attribute8_name => 'INFORMATION8'
610 ,p_attribute8_value => p_rec.information8
611 ,p_attribute9_name => 'INFORMATION9'
612 ,p_attribute9_value => p_rec.information9
613 ,p_attribute10_name => 'INFORMATION10'
614 ,p_attribute10_value => p_rec.information10
615 ,p_attribute11_name => 'INFORMATION11'
616 ,p_attribute11_value => p_rec.information11
617 ,p_attribute12_name => 'INFORMATION12'
618 ,p_attribute12_value => p_rec.information12
619 ,p_attribute13_name => 'INFORMATION13'
620 ,p_attribute13_value => p_rec.information13
621 ,p_attribute14_name => 'INFORMATION14'
622 ,p_attribute14_value => p_rec.information14
623 ,p_attribute15_name => 'INFORMATION15'
624 ,p_attribute15_value => p_rec.information15
625 ,p_attribute16_name => 'INFORMATION16'
626 ,p_attribute16_value => p_rec.information16
627 ,p_attribute17_name => 'INFORMATION17'
628 ,p_attribute17_value => p_rec.information17
629 ,p_attribute18_name => 'INFORMATION18'
630 ,p_attribute18_value => p_rec.information18
631 ,p_attribute19_name => 'INFORMATION19'
632 ,p_attribute19_value => p_rec.information19
633 ,p_attribute20_name => 'INFORMATION20'
634 ,p_attribute20_value => p_rec.information20
635 ,p_attribute21_name => 'INFORMATION21'
636 ,p_attribute21_value => p_rec.information21
637 ,p_attribute22_name => 'INFORMATION22'
638 ,p_attribute22_value => p_rec.information22
639 ,p_attribute23_name => 'INFORMATION23'
640 ,p_attribute23_value => p_rec.information23
641 ,p_attribute24_name => 'INFORMATION24'
642 ,p_attribute24_value => p_rec.information24
643 ,p_attribute25_name => 'INFORMATION25'
644 ,p_attribute25_value => p_rec.information25
645 ,p_attribute26_name => 'INFORMATION26'
646 ,p_attribute26_value => p_rec.information26
647 ,p_attribute27_name => 'INFORMATION27'
648 ,p_attribute27_value => p_rec.information27
649 ,p_attribute28_name => 'INFORMATION28'
650 ,p_attribute28_value => p_rec.information28
651 ,p_attribute29_name => 'INFORMATION29'
652 ,p_attribute29_value => p_rec.information29
653 ,p_attribute30_name => 'INFORMATION30'
654 ,p_attribute30_value => p_rec.information30
655 );
656 end if;
657 --
658 hr_utility.set_location(' Leaving:'||l_proc,20);
659 end chk_ddf;
660 --
661 -- ----------------------------------------------------------------------------
662 -- |------------------------------< chk_df >----------------------------------|
663 -- ----------------------------------------------------------------------------
664 --
665 -- Description:
666 -- Validates all the Descriptive Flexfield values.
667 --
668 -- Prerequisites:
669 -- All other columns have been validated. Must be called as the
670 -- last step from insert_validate and update_validate.
671 --
672 -- In Arguments:
673 -- p_rec
674 --
675 -- Post Success:
676 -- If the Descriptive Flexfield structure column and data values are
677 -- all valid this procedure will end normally and processing will
678 -- continue.
679 --
680 -- Post Failure:
681 -- If the Descriptive Flexfield structure column value or any of
682 -- the data values are invalid then an application error is raised as
683 -- a PL/SQL exception.
684 --
685 -- Access Status:
686 -- Internal Row Handler Use Only.
687 --
688 -- ----------------------------------------------------------------------------
689 procedure chk_df
690 (p_rec in pqh_rei_shd.g_rec_type
691 ) is
692 --
693 l_proc varchar2(72) := g_package || 'chk_df';
694 --
695 begin
696 hr_utility.set_location('Entering:'||l_proc,10);
697 --
698 if ((p_rec.role_extra_info_id is not null) and (
699 nvl(pqh_rei_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
700 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
701 nvl(pqh_rei_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
702 nvl(p_rec.attribute1, hr_api.g_varchar2) or
703 nvl(pqh_rei_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
704 nvl(p_rec.attribute2, hr_api.g_varchar2) or
708 nvl(p_rec.attribute4, hr_api.g_varchar2) or
705 nvl(pqh_rei_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
706 nvl(p_rec.attribute3, hr_api.g_varchar2) or
707 nvl(pqh_rei_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
709 nvl(pqh_rei_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
710 nvl(p_rec.attribute5, hr_api.g_varchar2) or
711 nvl(pqh_rei_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
712 nvl(p_rec.attribute6, hr_api.g_varchar2) or
713 nvl(pqh_rei_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
714 nvl(p_rec.attribute7, hr_api.g_varchar2) or
715 nvl(pqh_rei_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
716 nvl(p_rec.attribute8, hr_api.g_varchar2) or
717 nvl(pqh_rei_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
718 nvl(p_rec.attribute9, hr_api.g_varchar2) or
719 nvl(pqh_rei_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
720 nvl(p_rec.attribute10, hr_api.g_varchar2) or
721 nvl(pqh_rei_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
722 nvl(p_rec.attribute11, hr_api.g_varchar2) or
723 nvl(pqh_rei_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
724 nvl(p_rec.attribute12, hr_api.g_varchar2) or
725 nvl(pqh_rei_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
726 nvl(p_rec.attribute13, hr_api.g_varchar2) or
727 nvl(pqh_rei_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
728 nvl(p_rec.attribute14, hr_api.g_varchar2) or
729 nvl(pqh_rei_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
730 nvl(p_rec.attribute15, hr_api.g_varchar2) or
731 nvl(pqh_rei_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
732 nvl(p_rec.attribute16, hr_api.g_varchar2) or
733 nvl(pqh_rei_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
734 nvl(p_rec.attribute17, hr_api.g_varchar2) or
735 nvl(pqh_rei_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
736 nvl(p_rec.attribute18, hr_api.g_varchar2) or
737 nvl(pqh_rei_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
738 nvl(p_rec.attribute19, hr_api.g_varchar2) or
739 nvl(pqh_rei_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
740 nvl(p_rec.attribute20, hr_api.g_varchar2) or
741 nvl(pqh_rei_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
742 nvl(p_rec.attribute21, hr_api.g_varchar2) or
743 nvl(pqh_rei_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
744 nvl(p_rec.attribute22, hr_api.g_varchar2) or
745 nvl(pqh_rei_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
746 nvl(p_rec.attribute23, hr_api.g_varchar2) or
747 nvl(pqh_rei_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
748 nvl(p_rec.attribute24, hr_api.g_varchar2) or
749 nvl(pqh_rei_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
750 nvl(p_rec.attribute25, hr_api.g_varchar2) or
751 nvl(pqh_rei_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
752 nvl(p_rec.attribute26, hr_api.g_varchar2) or
753 nvl(pqh_rei_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
754 nvl(p_rec.attribute27, hr_api.g_varchar2) or
755 nvl(pqh_rei_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
756 nvl(p_rec.attribute28, hr_api.g_varchar2) or
757 nvl(pqh_rei_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
758 nvl(p_rec.attribute29, hr_api.g_varchar2) or
759 nvl(pqh_rei_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
760 nvl(p_rec.attribute30, hr_api.g_varchar2) ))
761 or (p_rec.role_extra_info_id is null) then
762 --
763 -- Only execute the validation if absolutely necessary:
764 -- a) During update, the structure column value or any
765 -- of the attribute values have actually changed.
766 -- b) During insert.
767 --
768 hr_dflex_utility.ins_or_upd_descflex_attribs
769 (p_appl_short_name => 'PQH'
770 ,p_descflex_name => 'PQH_ROLE_EXTRA_INFO'
771 ,p_attribute_category => p_rec.attribute_category
772 ,p_attribute1_name => 'ATTRIBUTE1'
773 ,p_attribute1_value => p_rec.attribute1
774 ,p_attribute2_name => 'ATTRIBUTE2'
775 ,p_attribute2_value => p_rec.attribute2
776 ,p_attribute3_name => 'ATTRIBUTE3'
777 ,p_attribute3_value => p_rec.attribute3
778 ,p_attribute4_name => 'ATTRIBUTE4'
779 ,p_attribute4_value => p_rec.attribute4
780 ,p_attribute5_name => 'ATTRIBUTE5'
781 ,p_attribute5_value => p_rec.attribute5
782 ,p_attribute6_name => 'ATTRIBUTE6'
783 ,p_attribute6_value => p_rec.attribute6
784 ,p_attribute7_name => 'ATTRIBUTE7'
785 ,p_attribute7_value => p_rec.attribute7
786 ,p_attribute8_name => 'ATTRIBUTE8'
787 ,p_attribute8_value => p_rec.attribute8
788 ,p_attribute9_name => 'ATTRIBUTE9'
789 ,p_attribute9_value => p_rec.attribute9
790 ,p_attribute10_name => 'ATTRIBUTE10'
791 ,p_attribute10_value => p_rec.attribute10
792 ,p_attribute11_name => 'ATTRIBUTE11'
793 ,p_attribute11_value => p_rec.attribute11
794 ,p_attribute12_name => 'ATTRIBUTE12'
795 ,p_attribute12_value => p_rec.attribute12
796 ,p_attribute13_name => 'ATTRIBUTE13'
797 ,p_attribute13_value => p_rec.attribute13
798 ,p_attribute14_name => 'ATTRIBUTE14'
799 ,p_attribute14_value => p_rec.attribute14
800 ,p_attribute15_name => 'ATTRIBUTE15'
801 ,p_attribute15_value => p_rec.attribute15
802 ,p_attribute16_name => 'ATTRIBUTE16'
803 ,p_attribute16_value => p_rec.attribute16
804 ,p_attribute17_name => 'ATTRIBUTE17'
805 ,p_attribute17_value => p_rec.attribute17
809 ,p_attribute19_value => p_rec.attribute19
806 ,p_attribute18_name => 'ATTRIBUTE18'
807 ,p_attribute18_value => p_rec.attribute18
808 ,p_attribute19_name => 'ATTRIBUTE19'
810 ,p_attribute20_name => 'ATTRIBUTE20'
811 ,p_attribute20_value => p_rec.attribute20
812 ,p_attribute21_name => 'ATTRIBUTE21'
813 ,p_attribute21_value => p_rec.attribute21
814 ,p_attribute22_name => 'ATTRIBUTE22'
815 ,p_attribute22_value => p_rec.attribute22
816 ,p_attribute23_name => 'ATTRIBUTE23'
817 ,p_attribute23_value => p_rec.attribute23
818 ,p_attribute24_name => 'ATTRIBUTE24'
819 ,p_attribute24_value => p_rec.attribute24
820 ,p_attribute25_name => 'ATTRIBUTE25'
821 ,p_attribute25_value => p_rec.attribute25
822 ,p_attribute26_name => 'ATTRIBUTE26'
823 ,p_attribute26_value => p_rec.attribute26
824 ,p_attribute27_name => 'ATTRIBUTE27'
825 ,p_attribute27_value => p_rec.attribute27
826 ,p_attribute28_name => 'ATTRIBUTE28'
827 ,p_attribute28_value => p_rec.attribute28
828 ,p_attribute29_name => 'ATTRIBUTE29'
829 ,p_attribute29_value => p_rec.attribute29
830 ,p_attribute30_name => 'ATTRIBUTE30'
831 ,p_attribute30_value => p_rec.attribute30
832 );
833 end if;
834 --
835 hr_utility.set_location(' Leaving:'||l_proc,20);
836 end chk_df;
837 --
838 -- ----------------------------------------------------------------------------
839 -- |---------------------------< insert_validate >----------------------------|
840 -- ----------------------------------------------------------------------------
841 Procedure insert_validate
842 (p_rec in pqh_rei_shd.g_rec_type
843 ) is
844 --
845 l_proc varchar2(72) := g_package||'insert_validate';
846 --
847 Begin
848 hr_utility.set_location('Entering:'||l_proc, 5);
849 --
850 -- Validate Role ID
851 --
852 chk_role_id
853 (p_role_id => p_rec.role_id
854 );
855 --
856 -- Validate Role Info Type
857 --
858 chk_role_info_type
859 (p_information_type => p_rec.information_type);
860 --
861 -- Validate Multiple Occurence Flag
862 --
863 chk_multiple_occurences_flag
864 (p_information_type => p_rec.information_type
865 ,p_role_id => p_rec.role_id
866 );
867 --
868 pqh_rei_bus.chk_ddf(p_rec);
869 hr_utility.set_location('Entering:'||l_proc, 10);
870
871
872 pqh_rei_bus.chk_df(p_rec);
873 hr_utility.set_location('Entering:'||l_proc, 15);
874
875 End insert_validate;
876 --
877 -- ----------------------------------------------------------------------------
878 -- |---------------------------< update_validate >----------------------------|
879 -- ----------------------------------------------------------------------------
880 Procedure update_validate
881 (p_rec in pqh_rei_shd.g_rec_type
882 ) is
883 --
884 l_proc varchar2(72) := g_package||'update_validate';
885 --
886 Begin
887 hr_utility.set_location('Entering:'||l_proc, 5);
888 --
889 chk_non_updateable_args
890 (p_rec => p_rec
891 );
892 --
893 -- Call ddf procedure to validate Developer Descipptive Flexfields
894 pqh_rei_bus.chk_ddf(p_rec);
895 hr_utility.set_location('Entering:'||l_proc, 10);
896
897 -- Call df procedure to validate Descipptive Flexfields
898 pqh_rei_bus.chk_df(p_rec);
899 --
900 hr_utility.set_location(' Leaving:'||l_proc, 15);
901 End update_validate;
902 --
903 -- ----------------------------------------------------------------------------
904 -- |---------------------------< delete_validate >----------------------------|
905 -- ----------------------------------------------------------------------------
906 Procedure delete_validate
907 (p_rec in pqh_rei_shd.g_rec_type
908 ) is
909 --
910 l_proc varchar2(72) := g_package||'delete_validate';
911 --
912 Begin
913 hr_utility.set_location('Entering:'||l_proc, 5);
914 --
915 -- Call all supporting business operations
916 --
917 hr_utility.set_location(' Leaving:'||l_proc, 10);
918 End delete_validate;
919 --
920 end pqh_rei_bus;