1 Package Body pqh_rng_bus as
2 /* $Header: pqrngrhi.pkb 115.18 2004/06/24 16:51:43 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rng_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_attribute_range_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- attribute_range_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_attribute_range_id(p_attribute_range_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_attribute_range_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := pqh_rng_shd.api_updating
47 (p_attribute_range_id => p_attribute_range_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_attribute_range_id,hr_api.g_number)
52 <> pqh_rng_shd.g_old_rec.attribute_range_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_rng_shd.constraint_error('PQH_ATTRIBUTE_RANGES_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_attribute_range_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_rng_shd.constraint_error('PQH_ATTRIBUTE_RANGES_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_attribute_range_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_position_id >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure checks that a referenced foreign key actually exists
82 -- in the referenced table.
83 --
84 -- Pre-Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_attribute_range_id PK
89 -- p_position_id ID of FK column
90 -- p_object_version_number object version number
91 --
92 -- Post Success
93 -- Processing continues
94 --
95 -- Post Failure
96 -- Error raised.
97 --
98 -- Access Status
99 -- Internal table handler use only.
100 --
101 Procedure chk_position_id (p_attribute_range_id in number,
102 p_position_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_position_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from per_all_positions a
112 where a.position_id = p_position_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_rng_shd.api_updating
119 (p_attribute_range_id => p_attribute_range_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_position_id,hr_api.g_number)
124 <> nvl(pqh_rng_shd.g_old_rec.position_id,hr_api.g_number)
125 or not l_api_updating) and
126 p_position_id is not null then
127 --
128 -- check if position_id value exists in per_all_positions table
129 --
130 open c1;
131 --
132 fetch c1 into l_dummy;
133 if c1%notfound then
134 --
135 close c1;
136 --
137 -- raise error as FK does not relate to PK in per_all_positions
138 -- table.
139 --
140 pqh_rng_shd.constraint_error('PQH_ATTRIBUTE_RANGES_FK4');
141 --
142 end if;
143 --
144 close c1;
145 --
146 end if;
147 --
148 hr_utility.set_location('Leaving:'||l_proc,10);
149 --
150 End chk_position_id;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------< chk_routing_category_id >------|
154 -- ----------------------------------------------------------------------------
155 --
156 -- Description
157 -- This procedure checks that a referenced foreign key actually exists
158 -- in the referenced table.
159 --
160 -- Pre-Conditions
161 -- None.
162 --
163 -- In Parameters
164 -- p_attribute_range_id PK
165 -- p_routing_category_id ID of FK column
166 -- p_object_version_number object version number
167 --
168 -- Post Success
169 -- Processing continues
170 --
171 -- Post Failure
172 -- Error raised.
173 --
174 -- Access Status
175 -- Internal table handler use only.
176 --
177 Procedure chk_routing_category_id (p_attribute_range_id in number,
178 p_routing_category_id in number,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_routing_category_id';
182 l_api_updating boolean;
183 l_dummy varchar2(1);
184 l_tcat pqh_routing_categories.transaction_category_id%type;
185 l_freeze_status_cd pqh_transaction_categories.freeze_status_cd%type;
186 --
187 cursor c1 is
188 -- select null
189 select transaction_category_id
190 from pqh_routing_categories a
191 where a.routing_category_id = p_routing_category_id;
192 --
193 cursor c2 is
194 select nvl(freeze_status_cd,hr_api.g_varchar2)
195 from pqh_transaction_categories a
196 where a.transaction_category_id = l_tcat;
197 Begin
198 --
199 hr_utility.set_location('Entering:'||l_proc,5);
200 --
201 l_api_updating := pqh_rng_shd.api_updating
202 (p_attribute_range_id => p_attribute_range_id,
203 p_object_version_number => p_object_version_number);
204 --
205 if (l_api_updating
206 and nvl(p_routing_category_id,hr_api.g_number)
207 <> nvl(pqh_rng_shd.g_old_rec.routing_category_id,hr_api.g_number)
208 or not l_api_updating) then
209 --
210 -- check if routing_category_id value exists in pqh_routing_categories table
211 --
212 open c1;
213 --
214 -- fetch c1 into l_dummy;
215 fetch c1 into l_tcat;
216 if c1%notfound then
217 --
218 close c1;
219 --
220 -- raise error as FK does not relate to PK in pqh_routing_categories
221 -- table.
222 --
223 pqh_rng_shd.constraint_error('PQH_ATTRIBUTE_RANGES_FK3');
224 --
225 end if;
226 --
227 close c1;
228 --
229 end if;
230 --
231 open c2;
232 Fetch c2 into l_freeze_status_cd;
233 close c2;
234
235 if l_freeze_status_cd = 'FREEZE_CATEGORY' then
236 hr_utility.set_message(8302, 'PQH_INVALID_RNG_OPERATION');
237 hr_utility.raise_error;
238 End if;
239 hr_utility.set_location('Leaving:'||l_proc,10);
240 --
241 End chk_routing_category_id;
242 --
243
244 -- ----------------------------------------------------------------------------
245 -- |------< chk_routing_list_member_id >------|
246 -- ----------------------------------------------------------------------------
247 --
248 -- Description
249 -- This procedure checks that a referenced foreign key actually exists
250 -- in the referenced table.
251 --
252 -- Pre-Conditions
253 -- None.
254 --
255 -- In Parameters
256 -- p_attribute_range_id PK
257 -- p_routing_list_member_id ID of FK column
258 -- p_object_version_number object version number
259 --
260 -- Post Success
261 -- Processing continues
262 --
263 -- Post Failure
264 -- Error raised.
265 --
266 -- Access Status
267 -- Internal table handler use only.
268 --
269 Procedure chk_routing_list_member_id (p_attribute_range_id in number,
270 p_routing_list_member_id in number,
271 p_object_version_number in number) is
272 --
273 l_proc varchar2(72) := g_package||'chk_routing_list_member_id';
274 l_api_updating boolean;
275 l_dummy varchar2(1);
276 --
277 cursor c1 is
278 select null
279 from pqh_routing_list_members a
280 where a.routing_list_member_id = p_routing_list_member_id;
281 --
282 Begin
283 --
284 hr_utility.set_location('Entering:'||l_proc,5);
285 --
286 l_api_updating := pqh_rng_shd.api_updating
287 (p_attribute_range_id => p_attribute_range_id,
288 p_object_version_number => p_object_version_number);
289 --
290 if (l_api_updating
291 and nvl(p_routing_list_member_id,hr_api.g_number)
292 <> nvl(pqh_rng_shd.g_old_rec.routing_list_member_id,hr_api.g_number)
293 or not l_api_updating) and
294 p_routing_list_member_id is not null then
295 --
296 -- check if routing_list_member_id value exists in pqh_routing_list_members table
297 --
298 open c1;
299 --
300 fetch c1 into l_dummy;
301 if c1%notfound then
302 --
303 close c1;
304 --
305 -- raise error as FK does not relate to PK in pqh_routing_list_members
306 -- table.
307 --
308 pqh_rng_shd.constraint_error('PQH_ATTRIBUTE_RANGES_FK2');
309 --
310 end if;
311 --
312 close c1;
313 --
314 end if;
315 --
316 hr_utility.set_location('Leaving:'||l_proc,10);
317 --
318 End chk_routing_list_member_id;
319 --
320 -- ----------------------------------------------------------------------------
321 -- |------< chk_valid_list_member_id >------|
322 -- ----------------------------------------------------------------------------
323 --
324 -- Description
325 -- This procedure checks if only either the routing_list_member_id /
326 -- position_id/ assignment_id is entered .
327 -- It also makes sure that the routing_list_member_id entered belongs
328 -- to the routing list associated with its routing category.
329 -- It also makes sure that the position_id entered belongs
330 -- to the position_structure associated with its routing category.
331 --
332 -- Pre-Conditions
333 -- None.
334 --
335 -- In Parameters
336 -- p_attribute_range_id PK
337 -- p_routing_list_member_id ID of FK column
338 -- p_position_id ID of FK column
339 -- p_assignment_id ID of FK column
340 -- p_routing_category_id ID of FK column
341 -- p_object_version_number object version number
342 --
343 -- Post Success
344 -- Processing continues
345 --
346 -- Post Failure
347 -- Error raised.
348 --
349 -- Access Status
350 -- Internal table handler use only.
351 --
352 Procedure chk_valid_list_member_id (p_attribute_range_id in number,
353 p_attribute_id in number,
354 p_range_name in varchar2,
355 p_routing_list_member_id in number,
356 p_position_id in number,
357 p_assignment_id in number,
358 p_routing_category_id in number,
359 p_object_version_number in number) is
360 --
361 l_rlist_id1 pqh_routing_categories.routing_list_id%type;
362 l_rlist_id2 pqh_routing_list_members.routing_list_id%type;
363 l_enable_flag pqh_routing_list_members.enable_flag%type;
364 l_position_structure_id pqh_routing_categories.position_structure_id%type;
365 --
366 Cursor csr_list_id is
367 select nvl(routing_list_id,hr_api.g_number),
368 nvl(position_structure_id,hr_api.g_number)
369 from pqh_routing_categories a
370 where a.routing_category_id = p_routing_category_id;
371 --
372 Cursor csr_routing_list_member is
373 Select nvl(routing_list_id,hr_api.g_number),nvl(enable_flag,'N')
374 from pqh_routing_list_members a
375 where a.routing_list_member_id = p_routing_list_member_id;
376 --
377 Cursor csr_pos_in_pos_hier(p_position_structure_id in number) is
378 select null
379 from per_pos_structure_versions v, per_pos_structure_elements e
380 where v.position_structure_id = p_position_structure_id
381 and sysdate between v.date_from and
382 nvl(v.date_to,to_date('31-12-4712','dd-mm-RRRR'))
383 and v.pos_structure_version_id = e.pos_structure_version_id
384 and (e.subordinate_position_id = p_position_id or
385 e.parent_position_id = p_position_id);
386 --
387 Cursor csr_new_attr_in_rule is
388 Select 'x'
389 From pqh_attribute_ranges
390 Where attribute_id = p_attribute_id
391 and range_name = p_range_name
392 and routing_category_id = p_routing_category_id
393 and routing_list_member_id = p_routing_list_member_id;
394 --
395 l_api_updating boolean;
396 l_dummy varchar2(1);
397 l_proc varchar2(72) := g_package||'chk_valid_list_member_id';
398 --
399 Begin
400 --
401 hr_utility.set_location('Entering:'||l_proc,5);
402 --
403 -- Select the routing list / position structure associated with the
404 -- routingcategory.
405 --
406 Open csr_list_id;
407 Fetch csr_list_id into l_rlist_id1,l_position_structure_id;
408 Close csr_list_id;
409 --
410 l_api_updating := pqh_rng_shd.api_updating
411 (p_attribute_range_id => p_attribute_range_id,
412 p_object_version_number => p_object_version_number);
413 --
414 if (l_api_updating
415 and nvl(p_routing_list_member_id,hr_api.g_number)
416 <> nvl(pqh_rng_shd.g_old_rec.routing_list_member_id,hr_api.g_number)
417 or not l_api_updating) and
418 p_routing_list_member_id is not null then
419 --
420 -- Get the routing list to which the member belongs
421 --
422 Open csr_routing_list_member;
423 Fetch csr_routing_list_member into l_rlist_id2,l_enable_flag;
424 Close csr_routing_list_member;
425 --
426 -- Raise error if the member does not belong to the same routing list
427 -- as the one attached to the routing category.
428 --
429 If l_rlist_id1 <> l_rlist_id2 then
430 hr_utility.set_message(8302,'PQH_INVALID_ROUTING_LIST_MEM');
431 hr_utility.raise_error;
432 End if;
433
434 If l_enable_flag <> 'Y' then
435 If not l_api_updating then
436
437 open csr_new_attr_in_rule;
438 fetch csr_new_attr_in_rule into l_dummy;
439 If csr_new_attr_in_rule%found then
440 hr_utility.set_message(8302,'PQH_AUTHORIZER_NOT_ENABLED');
441 hr_utility.raise_error;
442 End if;
443 Close csr_new_attr_in_rule;
444 Else
445 hr_utility.set_message(8302,'PQH_AUTHORIZER_NOT_ENABLED');
446 hr_utility.raise_error;
447 End if;
448 End if;
449
450 If p_position_id IS NOT NULL OR p_assignment_id IS NOT NULL then
451 hr_utility.set_message(8302,'PQH_MULTIPLE_MEMBER_TYPES');
452 hr_utility.raise_error;
453 End if;
454 --
455 end if;
456 --
457 -- Check if this position belong to the position structure associated
458 -- with its routing category.
459 -- Check if only position_id is not null
460 --
461 if (l_api_updating
462 and nvl(p_position_id,hr_api.g_number)
463 <> nvl(pqh_rng_shd.g_old_rec.position_id,hr_api.g_number)
464 or not l_api_updating) and
465 p_position_id is not null then
466 --
467 -- Get the position structure to which the position belongs
468 --
469 open csr_pos_in_pos_hier(p_position_structure_id=>l_position_structure_id);
470 --
471 fetch csr_pos_in_pos_hier into l_dummy;
472 --
473 if csr_pos_in_pos_hier%notfound then
474 --
475 -- raise error if the position does not belong to the same position
476 -- structure as the one associated with the routing category.
477 --
478 close csr_pos_in_pos_hier;
479 hr_utility.set_message(8302,'PQH_POS_NOT_IN_POS_HIER');
480 hr_utility.raise_error;
481 --
482 end if;
483 --
484 Close csr_pos_in_pos_hier;
485 --
486 If p_routing_list_member_id IS NOT NULL OR
487 p_assignment_id IS NOT NULL then
488 --
489 hr_utility.set_message(8302,'PQH_MULTIPLE_MEMBER_TYPES');
490 hr_utility.raise_error;
491 --
492 End if;
493 --
494 end if;
495 --
496 -- Check if only assignment_id is not null
497 --
498 if (l_api_updating
499 and nvl(p_assignment_id,hr_api.g_number)
500 <> nvl(pqh_rng_shd.g_old_rec.assignment_id,hr_api.g_number)
501 or not l_api_updating) and
502 p_assignment_id is not null then
503 --
504 If p_routing_list_member_id IS NOT NULL OR p_position_id IS NOT NULL then
505 hr_utility.set_message(8302,'PQH_MULTIPLE_MEMBER_TYPES');
506 hr_utility.raise_error;
507 End if;
508 --
509 end if;
510 --
511 --
512 hr_utility.set_location('Leaving:'||l_proc,10);
513 --
514 End chk_valid_list_member_id;
515 --
516 -- ----------------------------------------------------------------------------
517 -- |------< chk_attribute_id >------|
518 -- ----------------------------------------------------------------------------
519 --
520 -- Description
521 -- This procedure checks that a referenced foreign key actually exists
522 -- in the referenced table.
523 --
524 -- Pre-Conditions
525 -- None.
526 --
527 -- In Parameters
528 -- p_attribute_range_id PK
529 -- p_attribute_id ID of FK column
530 -- p_object_version_number object version number
531 --
532 -- Post Success
533 -- Processing continues
534 --
535 -- Post Failure
536 -- Error raised.
537 --
538 -- Access Status
539 -- Internal table handler use only.
540 --
541 Procedure chk_attribute_id (p_attribute_range_id in number,
542 p_attribute_id in number,
543 p_object_version_number in number) is
544 --
545 l_proc varchar2(72) := g_package||'chk_attribute_id';
546 l_api_updating boolean;
547 l_dummy varchar2(1);
548 --
549 cursor c1 is
550 select null
551 from pqh_attributes a
552 where a.attribute_id = p_attribute_id;
553 --
554 Begin
555 --
556 hr_utility.set_location('Entering:'||l_proc,5);
557 --
558 l_api_updating := pqh_rng_shd.api_updating
559 (p_attribute_range_id => p_attribute_range_id,
560 p_object_version_number => p_object_version_number);
561 --
562 if (l_api_updating
563 and nvl(p_attribute_id,hr_api.g_number)
564 <> nvl(pqh_rng_shd.g_old_rec.attribute_id,hr_api.g_number)
565 or not l_api_updating)
566 and p_attribute_id is not null then
567 --
568 -- check if attribute_id value exists in pqh_attributes table
569 --
570 open c1;
571 --
572 fetch c1 into l_dummy;
573 if c1%notfound then
574 --
575 close c1;
576 --
577 -- raise error as FK does not relate to PK in pqh_attributes
578 -- table.
579 --
580 pqh_rng_shd.constraint_error('PQH_ATTRIBUTE_RANGES_FK1');
581 --
582 end if;
583 --
584 close c1;
585 --
586 end if;
587 --
588 hr_utility.set_location('Leaving:'||l_proc,10);
589 --
590 End chk_attribute_id;
591 --
592 -- ----------------------------------------------------------------------------
593 -- |------< chk_if_valid_identifiers >------|
594 -- ----------------------------------------------------------------------------
595 --
596 -- Description
597 -- This procedure checks if the attribute id enetered is a valid
598 -- list / member identifier
599 -- Also checks if at least 1 range values are entered.
600 -- Also checks if the from and to values entered match the column type
601 -- if the attribute id.
602 --
603 -- Pre-Conditions
604 -- None.
605 --
606 -- In Parameters
607 -- p_attribute_range_id PK
608 -- p_attribute_id ID of FK column
609 -- p_object_version_number object version number
610 --
611 -- Post Success
612 -- Processing continues
613 --
614 -- Post Failure
615 -- Error raised.
616 --
617 -- Access Status
618 -- Internal table handler use only.
619 --
620 Procedure chk_if_valid_identifiers
621 (
622 p_attribute_range_id in number,
623 p_routing_category_id in number,
624 p_attribute_id in number,
625 p_routing_list_member_id in number,
626 p_position_id in number,
627 p_assignment_id in number,
628 p_from_char in varchar2,
629 p_to_char in varchar2,
630 p_from_number in number,
631 p_to_number in number,
632 p_from_date in date,
633 p_to_date in date,
634 p_object_version_number in number) is
635 --
636 l_col_type pqh_attributes.column_type%type;
637 l_attribute_name pqh_attributes.attribute_name%type;
638 l_dummy_name pqh_attributes.attribute_name%type;
639 l_list_identifier pqh_txn_category_attributes.list_identifying_flag%type;
640 l_member_identifier pqh_txn_category_attributes.member_identifying_flag%type;
641 l_tcat_id pqh_txn_category_attributes.transaction_category_id%type;
642 r_tcat_id pqh_routing_categories.transaction_category_id%type;
643 l_attribute_found boolean := FALSE;
644 --
645 l_proc varchar2(72) := g_package||'chk_if_valid_identifiers';
646 l_api_updating boolean;
647 l_dummy varchar2(1);
648 --
649 cursor c1 is
650 select transaction_category_id
651 from pqh_routing_categories a
652 where a.routing_category_id = p_routing_category_id;
653
654 cursor c2 (p_transaction_category_id in number) is
655 select att.attribute_name,nvl(att.column_type,hr_api.g_varchar2),
656 tca.transaction_category_id,
657 nvl(tca.list_identifying_flag,hr_api.g_varchar2),
658 nvl(tca.member_identifying_flag,hr_api.g_varchar2)
659 from pqh_txn_category_attributes tca,pqh_attributes att
660 where att.attribute_id = p_attribute_id
661 AND tca.attribute_id = att.attribute_id
662 AND tca.transaction_category_id = p_transaction_category_id;
663 --
664 Begin
665 --
666 hr_utility.set_location('Entering:'||l_proc,5);
667 --
668 l_api_updating := pqh_rng_shd.api_updating
669 (p_attribute_range_id => p_attribute_range_id,
670 p_object_version_number => p_object_version_number);
671 --
672 -- Attribute id may be null in case of rules for default hiearchy.
673 -- Then we do no need to perform the foll checks.
674 --
675 If p_attribute_id is NOT NULL then
676 --
677 -- Obtain the transaction category of the routing category.
678 --
679 Open c1;
680 Fetch c1 into r_tcat_id;
681 Close c1;
682 --
683 -- check if attribute_id also belongs to this transaction category .
684 -- Else there is a transaction category mismatch.
685 --
686 Open c2 (p_transaction_category_id => r_tcat_id);
687 --
688 fetch c2 into l_attribute_name,l_col_type,
689 l_tcat_id,l_list_identifier,l_member_identifier;
690 --
691 if c2%notfound then
692 --
693 Close c2;
694 --
695 -- raise error as this attribute does not belong to the same transaction
696 -- category id as the routing category.
697 --
698 hr_utility.set_message(8302,'PQH_RNG_TCT_MISMATCH');
699 hr_utility.set_message_token('ATTRIBUTE_NAME', l_attribute_name);
700 hr_utility.raise_error;
701 --
702 End if;
703 --
704 Close c2;
705 --
706 --
707 -- Check if this attribute is a valid list / member identifier
708 --
709 If p_routing_list_member_id IS NULL AND
710 p_position_id IS NULL AND
711 p_assignment_id IS NULL then
712 if l_list_identifier <> 'Y' then
713 hr_utility.set_message(8302,'PQH_NOT_LIST_IDENTIFIER');
714 hr_utility.raise_error;
715 End if;
716 Else
717 if l_member_identifier <> 'Y' then
718 hr_utility.set_message(8302,'PQH_NOT_MEMBER_IDENTIFIER');
719 hr_utility.raise_error;
720 End if;
721 End if;
722 --
723 -- Check if valid From and To range values are entered.
724 --
725 If l_col_type = 'V' then
726 --
727 if p_from_char IS NOT NULL and p_to_char IS NOT NULL and
728 p_to_char < p_from_char then
729 hr_utility.set_message(8302,'PQH_INVALID_TO_RANGE');
730 hr_utility.raise_error;
731 End if;
732 --
733 if p_from_date IS NOT NULL OR p_to_date IS NOT NULL
734 OR p_from_number IS NOT NULL OR p_to_number IS NOT NULL then
735 hr_utility.set_message(8302,'PQH_INVALID_RANGE_VALUES');
736 hr_utility.raise_error;
737 End if;
738 --
739 Elsif l_col_type = 'N' then
740 --
741 if p_from_number IS NOT NULL and p_to_number IS NOT NULL and
742 p_to_number < p_from_number then
743
744 hr_utility.set_message(8302,'PQH_INVALID_TO_RANGE');
745 hr_utility.raise_error;
746 End if;
747 --
748 if p_from_date IS NOT NULL OR p_to_date IS NOT NULL
749 OR p_from_char IS NOT NULL OR p_to_char IS NOT NULL then
750 hr_utility.set_message(8302,'PQH_INVALID_RANGE_VALUES');
751 hr_utility.raise_error;
752 End if;
753 --
754 Elsif l_col_type = 'D' then
755 --
756 --
757 if p_from_date IS NOT NULL and p_to_date IS NOT NULL and
758 p_to_date < p_from_date then
759 hr_utility.set_message(8302,'PQH_INVALID_TO_RANGE');
760 hr_utility.raise_error;
761 End if;
762 --
763 if p_from_char IS NOT NULL OR p_to_char IS NOT NULL
764 OR p_from_number IS NOT NULL OR p_to_number IS NOT NULL then
765 hr_utility.set_message(8302,'PQH_INVALID_RANGE_VALUES');
766 hr_utility.raise_error;
767 End if;
768 --
769 End if;
770 --
771 End if; -- p_attribute_id IS NOT NULL
772 --
773 hr_utility.set_location('Leaving:'||l_proc,10);
774 --
775 End chk_if_valid_identifiers;
776 --
777 -- ----------------------------------------------------------------------------
778 -- |------< chk_approver_flag >------|
779 -- ----------------------------------------------------------------------------
780 --
781 -- Description
782 -- This procedure is used to check that the lookup value is valid.
783 --
784 -- Pre Conditions
785 -- None.
786 --
787 -- In Parameters
788 -- attribute_range_id PK of record being inserted or updated.
789 -- approver_flag Value of lookup code.
790 -- effective_date effective date
791 -- object_version_number Object version number of record being
792 -- inserted or updated.
793 --
794 -- Post Success
795 -- Processing continues
796 --
797 -- Post Failure
798 -- Error handled by procedure
799 --
800 -- Access Status
801 -- Internal table handler use only.
802 --
803 Procedure chk_approver_flag(p_attribute_range_id in number,
804 p_approver_flag in varchar2,
805 p_effective_date in date,
806 p_object_version_number in number) is
807 --
808 l_proc varchar2(72) := g_package||'chk_approver_flag';
809 l_api_updating boolean;
810 --
811 Begin
812 --
813 hr_utility.set_location('Entering:'||l_proc, 5);
814 --
815 l_api_updating := pqh_rng_shd.api_updating
816 (p_attribute_range_id => p_attribute_range_id,
817 p_object_version_number => p_object_version_number);
818 --
819 if (l_api_updating
820 and p_approver_flag
821 <> nvl(pqh_rng_shd.g_old_rec.approver_flag,hr_api.g_varchar2)
822 or not l_api_updating)
823 and p_approver_flag is not null then
824 --
825 -- check if value of lookup falls within lookup type.
826 --
827 if hr_api.not_exists_in_hr_lookups
828 (p_lookup_type => 'YES_NO',
829 p_lookup_code => p_approver_flag,
830 p_effective_date => p_effective_date) then
831 --
832 -- raise error as does not exist as lookup
833 --
834 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
835 hr_utility.raise_error;
836 --
837 end if;
838 --
839 end if;
840 --
841 hr_utility.set_location('Leaving:'||l_proc,10);
842 --
843 end chk_approver_flag;
844 --
845 --
846 --
847 FUNCTION chk_if_member_enabled(p_routing_list_id in number,
848 p_routing_list_member_id in number)
849 RETURN NUMBER is
850 --
851 Cursor csr_member_enabled is
852 Select nvl(enable_flag,'N')
853 From pqh_routing_list_members
854 Where routing_list_id = p_routing_list_id
855 And routing_list_member_id = p_routing_list_member_id;
856 --
857 l_enable_flag pqh_routing_list_members.enable_flag%type;
858 l_proc varchar2(72) := g_package||'chk_if_member_enabled';
859 --
860 Begin
861 --
862 hr_utility.set_location('Entering:'||l_proc,5);
863 --
864 Open csr_member_enabled;
865 --
866 Fetch csr_member_enabled into l_enable_flag;
867 --
868 Close csr_member_enabled;
869 --
870 If l_enable_flag <> 'Y' then
871 RETURN 1;
872 End if;
873 --
874 hr_utility.set_location('Leaving:'||l_proc,10);
875 --
876 RETURN 0;
877 --
878 End;
879 --
880 -- ----------------------------------------------------------------------------
881 -- |------< chk_enable_flag >------|
882 -- ----------------------------------------------------------------------------
883 --
884 -- Description
885 -- This procedure is used to check that the lookup value is valid.
886 --
887 -- Pre Conditions
888 -- None.
889 --
890 -- In Parameters
891 -- attribute_range_id PK of record being inserted or updated.
892 -- enable_flag Value of lookup code.
893 -- effective_date effective date
894 -- object_version_number Object version number of record being
895 -- inserted or updated.
896 --
897 -- Post Success
898 -- Processing continues
899 --
900 -- Post Failure
901 -- Error handled by procedure
902 --
903 -- Access Status
904 -- Internal table handler use only.
905 --
906 Procedure chk_enable_flag(p_attribute_range_id in number,
907 p_enable_flag in varchar2,
908 p_routing_list_member_id in number,
909 p_routing_category_id in number,
910 p_effective_date in date,
911 p_object_version_number in number) is
912 --
913 --
914 Cursor csr_list_id is
915 select nvl(routing_list_id,hr_api.g_number)
916 from pqh_routing_categories a
917 where a.routing_category_id = p_routing_category_id;
918 --
919 l_routing_list_id pqh_routing_categories.routing_list_id%type;
920 l_error_code number(10) := NULL;
921 --
922 l_proc varchar2(72) := g_package||'chk_enable_flag';
923 l_api_updating boolean;
924 --
925 Begin
926 --
927 hr_utility.set_location('Entering:'||l_proc, 5);
928 --
929 l_api_updating := pqh_rng_shd.api_updating
930 (p_attribute_range_id => p_attribute_range_id,
931 p_object_version_number => p_object_version_number);
932 --
933 if (l_api_updating
934 and p_enable_flag
935 <> nvl(pqh_rng_shd.g_old_rec.enable_flag,hr_api.g_varchar2)
936 or not l_api_updating)
937 and p_enable_flag is not null then
938 --
939 -- check if value of lookup falls within lookup type.
940 --
941 if hr_api.not_exists_in_hr_lookups
942 (p_lookup_type => 'YES_NO',
943 p_lookup_code => p_enable_flag,
944 p_effective_date => p_effective_date) then
945 --
946 -- raise error as does not exist as lookup
947 --
948 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
949 hr_utility.raise_error;
950 --
951 end if;
952 --
953 end if;
954 --
955 if l_api_updating
956 and p_enable_flag
957 <> nvl(pqh_rng_shd.g_old_rec.enable_flag,hr_api.g_varchar2)
958 and p_enable_flag is not null then
959 --
960 -- check if value of lookup falls within lookup type.
961 --
962 if p_enable_flag = 'Y' and p_routing_list_member_id IS NOT NULL then
963 --
964 Open csr_list_id;
965 Fetch csr_list_id into l_routing_list_id;
966 Close csr_list_id;
967 --
968 l_error_code := chk_if_member_enabled
969 (p_routing_list_id => l_routing_list_id,
970 p_routing_list_member_id => p_routing_list_member_id);
971 --
972 If l_error_code = 1 then
973 --
974 hr_utility.set_message(8302,'PQH_CANNOT_ENABLE_AUTH_RULE');
975 hr_utility.raise_error;
976 --
977 End if;
978 --
979 end if;
980 --
981 end if;
982 --
983 hr_utility.set_location('Leaving:'||l_proc,10);
984 --
985 end chk_enable_flag;
986 --
987 --
988 -- ----------------------------------------------------------------------------
989 -- |------< chk_delete_flag >------|
990 -- ----------------------------------------------------------------------------
991 --
992 -- Description
993 -- This procedure is used to check that the lookup value is valid.
994 --
995 -- Pre Conditions
996 -- None.
997 --
998 -- In Parameters
999 -- attribute_range_id PK of record being inserted or updated.
1000 -- delete_flag Value of lookup code.
1001 -- effective_date effective date
1002 -- object_version_number Object version number of record being
1003 -- inserted or updated.
1004 --
1005 -- Post Success
1006 -- Processing continues
1007 --
1008 -- Post Failure
1009 -- Error handled by procedure
1010 --
1011 -- Access Status
1012 -- Internal table handler use only.
1013 --
1014 Procedure chk_delete_flag(p_attribute_range_id in number,
1015 p_delete_flag in varchar2,
1016 p_effective_date in date,
1017 p_object_version_number in number) is
1018 --
1019 l_proc varchar2(72) := g_package||'chk_delete_flag';
1020 l_api_updating boolean;
1021 --
1022 Begin
1023 --
1024 hr_utility.set_location('Entering:'||l_proc, 5);
1025 --
1026 l_api_updating := pqh_rng_shd.api_updating
1027 (p_attribute_range_id => p_attribute_range_id,
1028 p_object_version_number => p_object_version_number);
1029 --
1030 if (l_api_updating
1031 and p_delete_flag
1032 <> nvl(pqh_rng_shd.g_old_rec.delete_flag,hr_api.g_varchar2)
1033 or not l_api_updating)
1034 and p_delete_flag is not null then
1035 --
1036 -- check if value of lookup falls within lookup type.
1037 --
1038 if hr_api.not_exists_in_hr_lookups
1039 (p_lookup_type => 'YES_NO',
1040 p_lookup_code => p_delete_flag,
1041 p_effective_date => p_effective_date) then
1042 --
1043 -- raise error as does not exist as lookup
1044 --
1045 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1046 hr_utility.raise_error;
1047 --
1048 end if;
1049 --
1050 end if;
1051 --
1052 --
1053 hr_utility.set_location('Leaving:'||l_proc,10);
1054 --
1055 end chk_delete_flag;
1056 --
1057 -- ----------------------------------------------------------------------------
1058 -- |---------------------------< insert_validate >----------------------------|
1059 -- ----------------------------------------------------------------------------
1060 Procedure insert_validate(p_rec in pqh_rng_shd.g_rec_type
1061 ,p_effective_date in date) is
1062 --
1063 l_proc varchar2(72) := g_package||'insert_validate';
1064 --
1065 Begin
1066 hr_utility.set_location('Entering:'||l_proc, 5);
1067 --
1068 -- Call all supporting business operations
1069 --
1070 chk_attribute_range_id
1071 (p_attribute_range_id => p_rec.attribute_range_id,
1072 p_object_version_number => p_rec.object_version_number);
1073 --
1074 chk_position_id
1075 (p_attribute_range_id => p_rec.attribute_range_id,
1076 p_position_id => p_rec.position_id,
1077 p_object_version_number => p_rec.object_version_number);
1078 --
1079 chk_routing_category_id
1080 (p_attribute_range_id => p_rec.attribute_range_id,
1081 p_routing_category_id => p_rec.routing_category_id,
1082 p_object_version_number => p_rec.object_version_number);
1083 --
1084 chk_routing_list_member_id
1085 (p_attribute_range_id => p_rec.attribute_range_id,
1086 p_routing_list_member_id => p_rec.routing_list_member_id,
1087 p_object_version_number => p_rec.object_version_number);
1088 --
1089 chk_valid_list_member_id
1090 (p_attribute_range_id => p_rec.attribute_range_id,
1091 p_attribute_id => p_rec.attribute_id,
1092 p_range_name => p_rec.range_name,
1093 p_routing_list_member_id => p_rec.routing_list_member_id,
1094 p_position_id => p_rec.position_id,
1095 p_assignment_id => p_rec.assignment_id,
1096 p_routing_category_id => p_rec.routing_category_id,
1097 p_object_version_number => p_rec.object_version_number);
1098 --
1099 chk_attribute_id
1100 (p_attribute_range_id => p_rec.attribute_range_id,
1101 p_attribute_id => p_rec.attribute_id,
1102 p_object_version_number => p_rec.object_version_number);
1103 --
1104 chk_approver_flag
1105 (p_attribute_range_id => p_rec.attribute_range_id,
1106 p_approver_flag => p_rec.approver_flag,
1107 p_effective_date => p_effective_date,
1108 p_object_version_number => p_rec.object_version_number);
1109 --
1110 chk_enable_flag
1111 (p_attribute_range_id => p_rec.attribute_range_id,
1112 p_enable_flag => p_rec.enable_flag,
1113 p_routing_category_id => p_rec.routing_category_id,
1114 p_routing_list_member_id => p_rec.routing_list_member_id,
1115 p_effective_date => p_effective_date,
1116 p_object_version_number => p_rec.object_version_number);
1117 --
1118 chk_delete_flag
1119 (p_attribute_range_id => p_rec.attribute_range_id,
1120 p_delete_flag => p_rec.delete_flag,
1121 p_effective_date => p_effective_date,
1122 p_object_version_number => p_rec.object_version_number);
1123 --
1124 chk_if_valid_identifiers
1125 (p_attribute_range_id => p_rec.attribute_range_id,
1126 p_routing_category_id => p_rec.routing_category_id,
1127 p_attribute_id => p_rec.attribute_id,
1128 p_routing_list_member_id => p_rec.routing_list_member_id,
1129 p_position_id => p_rec.position_id,
1130 p_assignment_id => p_rec.assignment_id,
1131 p_from_char => p_rec.from_char,
1132 p_to_char => p_rec.to_char,
1133 p_from_number => p_rec.from_number,
1134 p_to_number => p_rec.to_number,
1135 p_from_date => p_rec.from_date,
1136 p_to_date => p_rec.to_date,
1137 p_object_version_number => p_rec.object_version_number);
1138 --
1139 --
1140 --
1141 hr_utility.set_location(' Leaving:'||l_proc, 10);
1142 End insert_validate;
1143 --
1144 -- ----------------------------------------------------------------------------
1145 -- |---------------------------< update_validate >----------------------------|
1146 -- ----------------------------------------------------------------------------
1147 Procedure update_validate(p_rec in pqh_rng_shd.g_rec_type
1148 ,p_effective_date in date) is
1149 --
1150 l_proc varchar2(72) := g_package||'update_validate';
1151 --
1152 Begin
1153 hr_utility.set_location('Entering:'||l_proc, 5);
1154 --
1155 -- Call all supporting business operations
1156 --
1157 chk_attribute_range_id
1158 (p_attribute_range_id => p_rec.attribute_range_id,
1159 p_object_version_number => p_rec.object_version_number);
1160 --
1161 chk_position_id
1162 (p_attribute_range_id => p_rec.attribute_range_id,
1163 p_position_id => p_rec.position_id,
1164 p_object_version_number => p_rec.object_version_number);
1165 --
1166 chk_routing_category_id
1167 (p_attribute_range_id => p_rec.attribute_range_id,
1168 p_routing_category_id => p_rec.routing_category_id,
1169 p_object_version_number => p_rec.object_version_number);
1170 --
1171 chk_routing_list_member_id
1172 (p_attribute_range_id => p_rec.attribute_range_id,
1173 p_routing_list_member_id => p_rec.routing_list_member_id,
1174 p_object_version_number => p_rec.object_version_number);
1175 --
1176 chk_valid_list_member_id
1177 (p_attribute_range_id => p_rec.attribute_range_id,
1178 p_attribute_id => p_rec.attribute_id,
1179 p_range_name => p_rec.range_name,
1180 p_routing_list_member_id => p_rec.routing_list_member_id,
1181 p_position_id => p_rec.position_id,
1182 p_assignment_id => p_rec.assignment_id,
1183 p_routing_category_id => p_rec.routing_category_id,
1184 p_object_version_number => p_rec.object_version_number);
1185 --
1186 chk_attribute_id
1187 (p_attribute_range_id => p_rec.attribute_range_id,
1188 p_attribute_id => p_rec.attribute_id,
1189 p_object_version_number => p_rec.object_version_number);
1190 --
1191 chk_approver_flag
1192 (p_attribute_range_id => p_rec.attribute_range_id,
1193 p_approver_flag => p_rec.approver_flag,
1194 p_effective_date => p_effective_date,
1195 p_object_version_number => p_rec.object_version_number);
1196 --
1197 --
1198 chk_enable_flag
1199 (p_attribute_range_id => p_rec.attribute_range_id,
1200 p_enable_flag => p_rec.enable_flag,
1201 p_routing_category_id => p_rec.routing_category_id,
1202 p_routing_list_member_id => p_rec.routing_list_member_id,
1203 p_effective_date => p_effective_date,
1204 p_object_version_number => p_rec.object_version_number);
1205 --
1206 chk_delete_flag
1207 (p_attribute_range_id => p_rec.attribute_range_id,
1208 p_delete_flag => p_rec.delete_flag,
1209 p_effective_date => p_effective_date,
1210 p_object_version_number => p_rec.object_version_number);
1211 --
1212 chk_if_valid_identifiers
1213 (p_attribute_range_id => p_rec.attribute_range_id,
1214 p_routing_category_id => p_rec.routing_category_id,
1215 p_attribute_id => p_rec.attribute_id,
1216 p_routing_list_member_id => p_rec.routing_list_member_id,
1217 p_position_id => p_rec.position_id,
1218 p_assignment_id => p_rec.assignment_id,
1219 p_from_char => p_rec.from_char,
1220 p_to_char => p_rec.to_char,
1221 p_from_number => p_rec.from_number,
1222 p_to_number => p_rec.to_number,
1223 p_from_date => p_rec.from_date,
1224 p_to_date => p_rec.to_date,
1225 p_object_version_number => p_rec.object_version_number);
1226 --
1227 --
1228 hr_utility.set_location(' Leaving:'||l_proc, 10);
1229 End update_validate;
1230 --
1231 -- ----------------------------------------------------------------------------
1232 -- |---------------------------< delete_validate >----------------------------|
1233 -- ----------------------------------------------------------------------------
1234 Procedure delete_validate(p_rec in pqh_rng_shd.g_rec_type
1235 ,p_effective_date in date) is
1236 --
1237 l_proc varchar2(72) := g_package||'delete_validate';
1238 --
1239 Begin
1240 hr_utility.set_location('Entering:'||l_proc, 5);
1241 --
1242 -- Call all supporting business operations
1243 --
1244 hr_utility.set_location(' Leaving:'||l_proc, 10);
1245 End delete_validate;
1246 --
1247 end pqh_rng_bus;
[Home] [Help]