[Home] [Help]
PACKAGE BODY: APPS.PQH_TCT_WIZARD_PKG
Source
1 Package Body pqh_tct_wizard_pkg as
2 /* $Header: pqtctwiz.pkb 120.2 2005/10/12 20:20:12 srajakum noship $ */
3 --
4 --
5 -----------------------------------------------------------------------------
6 --
7 -- This function checks if standard setup is already complete for the
8 -- transaction category and returns TRUE if standard setup is complete .
9 -- It returns FALSE if standard setup has not yet been done.
10 --
11 Function chk_if_setup_finish(p_transaction_category_id in number,
12 p_setup_type out nocopy varchar2)
13 Return Boolean
14 IS
15 --
16 -- Set_up flag is a new field in pqh_transaction_categories which tells us what
17 -- part of the setup is complete. Can have values of STANDARD/ADVANCED/NULL
18 --
19 l_freeze_status_cd pqh_transaction_categories.freeze_status_cd%type;
20 l_set_up_flag pqh_transaction_categories.setup_type_cd%type;
21 --
22 l_proc varchar2(72) := 'chk_if_setup_finish';
23 --
24 Cursor csr_setup is
25 --
26 Select nvl(setup_type_cd,'INCOMPLETE') , nvl(freeze_status_cd,'X')
27 from pqh_transaction_categories_vl
28 where transaction_category_id = p_transaction_category_id;
29 --
30 Begin
31 --
32 hr_utility.set_location('Entering:'||l_proc, 5);
33 --
34 Open csr_setup;
35 Fetch csr_setup into l_set_up_flag,l_freeze_status_cd;
36 Close csr_setup;
37 --
38 p_setup_type := l_set_up_flag;
39 --
40 -- if the category is frozen , it means that the setup was
41 -- successful.
42 -- Check if the Standard or Advanced Setup was completed.
43 --
44 If l_freeze_status_cd = 'FREEZE_CATEGORY' AND
45 (l_set_up_flag = 'STANDARD' or l_set_up_flag = 'ADVANCED') then
46 return TRUE;
47 --
48 End if;
49 --
50 return FALSE;
51 --
52 --
53 hr_utility.set_location('Leaving:'||l_proc, 10);
54 --
55 exception when others then
56 p_setup_type := null;
57 raise;
58 End;
59 --
60 -----------------------------------------------------------------------------
61 --
62 Function generate_rule_name
63 Return VARCHAR2 is
64 --
65 Cursor csr_next_rule is
66 Select pqh_system_rule_s.nextval
67 from dual;
68 --
69 l_range_name pqh_attribute_ranges.range_name%type;
70 l_next_sequence number;
71 --
72 l_proc varchar2(72) := 'generate_rule_name';
73 --
74 Begin
75 --
76 hr_utility.set_location('Entering:'||l_proc, 5);
77 --
78 --
79 l_range_name := 'PQH_$$SYS$$_';
80 --
81 Open csr_next_rule;
82 Fetch csr_next_rule into l_next_sequence;
83 Close csr_next_rule;
84 --
85 l_range_name := l_range_name || to_char(l_next_sequence);
86 --
87 Return l_range_name;
88 --
89 --
90 hr_utility.set_location('Leaving:'||l_proc, 10);
91 --
92 End;
93 --
94 -----------------------------------------------------------------------------
95 --
96 PROCEDURE create_default_hierarchy
97 ( p_validate in boolean default false
98 ,p_routing_category_id out nocopy number
99 ,p_transaction_category_id in number default null
100 ,p_enable_flag in varchar2 default 'Y'
101 ,p_default_flag in varchar2 default null
102 ,p_routing_list_id in number default null
103 ,p_position_structure_id in number default null
104 ,p_override_position_id in number default null
105 ,p_override_assignment_id in number default null
106 ,p_override_role_id in number default null
107 ,p_override_user_id in number default null
108 ,p_object_version_number out nocopy number
109 ,p_effective_date in date
110 ) is
111 l_rule_name varchar2(200);
112 l_attribute_range_id number;
113 l_proc varchar2(72) := 'create_default_hierarchy';
114 --
115 Begin
116 --
117 hr_utility.set_location('Entering:'||l_proc, 5);
118 --
119 --
120 -- Create a routing category and with default_flag 'Y'
121 -- File : pqrctapi.pkh/pkb
122 --
123 pqh_routing_categories_api.create_routing_category
124 (
125 p_validate => p_validate
126 ,p_routing_category_id => p_routing_category_id
127 ,p_transaction_category_id => p_transaction_category_id
128 ,p_enable_flag => p_enable_flag
129 ,p_default_flag => p_default_flag
130 ,p_delete_flag => NULL
131 ,p_routing_list_id => p_routing_list_id
132 ,p_position_structure_id => p_position_structure_id
133 ,p_override_position_id => p_override_position_id
134 ,p_override_assignment_id => p_override_assignment_id
135 ,p_override_role_id => p_override_role_id
136 ,p_override_user_id => p_override_user_id
137 ,p_object_version_number => p_object_version_number
138 ,p_effective_date => p_effective_date);
139 --
140 -- Generate a system rule_name
141 --
142 l_rule_name := generate_rule_name;
143 --
144 -- Create a rule with the above generated rule name and attribute
145 -- ranges value null
146 -- File : pqrngapi.pkh/pkb
147 --
148 pqh_attribute_ranges_api.create_attribute_range(
149 p_validate => p_validate
150 ,p_attribute_range_id => l_attribute_range_id
151 ,p_approver_flag => NULL
152 ,p_enable_flag => p_enable_flag
153 ,p_delete_flag => NULL
154 ,p_assignment_id => NULL
155 ,p_attribute_id => NULL
156 ,p_from_char => NULL
157 ,p_from_date => NULL
158 ,p_from_number => NULL
159 ,p_position_id => NULL
160 ,p_range_name => l_rule_name
161 ,p_routing_category_id => p_routing_category_id
162 ,p_routing_list_member_id => NULL
163 ,p_to_char => NULL
164 ,p_to_date => NULL
165 ,p_to_number => NULL
166 ,p_object_version_number => p_object_version_number
167 ,p_effective_date => p_effective_date);
168 --
169 --
170 hr_utility.set_location('Leaving:'||l_proc, 10);
171 --
172 exception when others then
173 p_routing_category_id := null;
174 p_object_version_number := null;
175 raise;
176 End;
177 --
178 -----------------------------------------------------------------------------
179 --
180 PROCEDURE update_default_hierarchy
181 (
182 p_validate in boolean default false
183 ,p_old_routing_category_id in number
184 ,p_routing_category_id in out nocopy number
185 ,p_transaction_category_id in number default null
186 ,p_enable_flag in varchar2 default 'Y'
187 ,p_default_flag in varchar2 default null
188 ,p_routing_list_id in number default null
189 ,p_position_structure_id in number default null
190 ,p_override_position_id in number default null
191 ,p_override_assignment_id in number default null
192 ,p_override_role_id in number default null
193 ,p_override_user_id in number default null
194 ,p_object_version_number in out nocopy number
195 ,p_effective_date in date
196 )
197 is
198 Cursor csr_get_attribute_range(p_routing_category_id number) is
199 Select *
200 from pqh_attribute_ranges
201 where routing_category_id = p_routing_category_id;
202 --
203 l_rule_name varchar2(200);
204 l_attribute_range_id number;
205 --
206 l_rng_record pqh_attribute_ranges%ROWTYPE;
207 --
208 Cursor csr_old_def_hierarchy is
209 Select * from pqh_routing_categories
210 Where routing_category_id = p_old_routing_category_id;
211 --
212 l_rct_record pqh_routing_categories%ROWTYPE;
213 --
214 l_proc varchar2(72) := 'update_default_hierarchy';
215 l_routing_category_id number := p_routing_category_id;
216 l_object_version_number number := p_object_version_number;
217 --
218 --
219 Begin
220 --
221 hr_utility.set_location('Entering:'||l_proc, 5);
222 --
223 -- Disable default approver and routing rules.
224 --
225 If p_old_routing_category_id IS NOT NULL then
226 --
227 /**
228 open csr_get_attribute_range(p_routing_category_id => p_old_routing_category_id);
229 loop
230 fetch csr_get_attribute_range into l_rng_record;
231 exit when csr_get_attribute_range%notfound;
232 pqh_attribute_ranges_api.update_attribute_range
233 (
234 p_validate => p_validate
235 ,p_attribute_range_id => l_rng_record.attribute_range_id
236 ,p_approver_flag => l_rng_record.approver_flag
237 ,p_enable_flag => 'N'
238 ,p_delete_flag => l_rng_record.delete_flag
239 ,p_assignment_id => l_rng_record.assignment_id
240 ,p_attribute_id => l_rng_record.attribute_id
241 ,p_from_char => l_rng_record.from_char
242 ,p_from_date => l_rng_record.from_date
243 ,p_from_number => l_rng_record.from_number
244 ,p_position_id => l_rng_record.position_id
245 ,p_range_name => l_rng_record.range_name
246 ,p_routing_category_id => l_rng_record.routing_category_id
247 ,p_routing_list_member_id => l_rng_record.routing_list_member_id
248 ,p_to_char => l_rng_record.to_char
249 ,p_to_date => l_rng_record.to_date
250 ,p_to_number => l_rng_record.to_number
251 ,p_object_version_number => l_rng_record.object_version_number
252 ,p_effective_date => p_effective_date);
253
254 end loop;
255
256 close csr_get_attribute_range;
257 **/
258 --
259 -- Added by Stella.
260 -- Get details of the previous routing category , as the passed
261 -- information is the new default hierarchy and approvers.
262 --
263 Open csr_old_def_hierarchy;
264 Fetch csr_old_def_hierarchy into l_rct_record;
265 Close csr_old_def_hierarchy;
266 --
267 -- Disable previously selected default hierarcy
268 --
269 pqh_routing_categories_api.update_routing_category
270 (
271 p_validate => p_validate
272 ,p_routing_category_id => p_old_routing_category_id
273 ,p_transaction_category_id => l_rct_record.transaction_category_id
274 ,p_enable_flag => 'N'
275 ,p_delete_flag => l_rct_record.delete_flag
276 ,p_default_flag => l_rct_record.default_flag
277 ,p_routing_list_id => l_rct_record.routing_list_id
278 ,p_position_structure_id => l_rct_record.position_structure_id
279 ,p_override_position_id => l_rct_record.override_position_id
280 ,p_override_assignment_id => l_rct_record.override_assignment_id
281 ,p_override_role_id => l_rct_record.override_role_id
282 ,p_override_user_id => l_rct_record.override_user_id
283 ,p_object_version_number => l_rct_record.object_version_number
284 ,p_effective_date => p_effective_date);
285 --
286 -- End of change by Stella.
287 --
288 End if;
289
290 if (p_routing_category_id <> nvl(p_old_routing_category_id,-999)) and (p_routing_category_id is not null) then
291 --
292 -- Enable the already existing default_hierarchy
293 --
294 /**
295 open csr_get_attribute_range(p_routing_category_id => p_routing_category_id);
296 loop
297 fetch csr_get_attribute_range into l_rng_record;
298 exit when csr_get_attribute_range%notfound;
299 pqh_attribute_ranges_api.update_attribute_range
300 (
301 p_validate => p_validate
302 ,p_attribute_range_id => l_rng_record.attribute_range_id
303 ,p_approver_flag => l_rng_record.approver_flag
304 ,p_enable_flag => 'Y'
305 ,p_delete_flag => l_rng_record.delete_flag
306 ,p_assignment_id => l_rng_record.assignment_id
307 ,p_attribute_id => l_rng_record.attribute_id
308 ,p_from_char => l_rng_record.from_char
309 ,p_from_date => l_rng_record.from_date
310 ,p_from_number => l_rng_record.from_number
311 ,p_position_id => l_rng_record.position_id
312 ,p_range_name => l_rng_record.range_name
313 ,p_routing_category_id => p_routing_category_id
314 ,p_routing_list_member_id => l_rng_record.routing_list_member_id
315 ,p_to_char => l_rng_record.to_char
316 ,p_to_date => l_rng_record.to_date
317 ,p_to_number => l_rng_record.to_number
318 ,p_object_version_number => l_rng_record.object_version_number
319 ,p_effective_date => p_effective_date);
320 end loop;
321
322 close csr_get_attribute_range;
323 **/
324 --
325 --
326 -- Enable previously selected default hierarcy
327 --
328 pqh_routing_categories_api.update_routing_category
329 (
330 p_validate => p_validate
331 ,p_routing_category_id => p_routing_category_id
332 ,p_transaction_category_id => p_transaction_category_id
333 ,p_enable_flag => 'Y'
334 ,p_delete_flag => NULL
335 ,p_default_flag => p_default_flag
336 ,p_routing_list_id => p_routing_list_id
337 ,p_position_structure_id => p_position_structure_id
338 ,p_override_position_id => p_override_position_id
339 ,p_override_assignment_id => p_override_assignment_id
340 ,p_override_role_id => p_override_role_id
341 ,p_override_user_id => p_override_user_id
342 ,p_object_version_number => p_object_version_number
343 ,p_effective_date => p_effective_date);
344 --
345 --
346 --
347
348 else
349
350 --
351 -- Create a routing category and with default_flag 'Y'
352 --
353 pqh_routing_categories_api.create_routing_category
354 (
355 p_validate => p_validate
356 ,p_routing_category_id => p_routing_category_id
357 ,p_transaction_category_id => p_transaction_category_id
358 ,p_enable_flag => p_enable_flag
359 ,p_default_flag => p_default_flag
360 ,p_delete_flag => NULL
361 ,p_routing_list_id => p_routing_list_id
362 ,p_position_structure_id => p_position_structure_id
363 ,p_override_position_id => p_override_position_id
364 ,p_override_assignment_id => p_override_assignment_id
365 ,p_override_role_id => p_override_role_id
366 ,p_override_user_id => p_override_user_id
367 ,p_object_version_number => p_object_version_number
368 ,p_effective_date => p_effective_date);
369 --
370 -- Generate a system rule_name
371 --
372 l_rule_name := generate_rule_name;
373 --
374 -- Create a rule with the above generated rule name and attribute
375 -- ranges value null
376 --
377 pqh_attribute_ranges_api.create_attribute_range
378 (p_validate => p_validate
379 ,p_attribute_range_id => l_attribute_range_id
380 ,p_approver_flag => NULL
381 ,p_enable_flag => p_enable_flag
382 ,p_delete_flag => NULL
383 ,p_assignment_id => NULL
384 ,p_attribute_id => NULL
385 ,p_from_char => NULL
386 ,p_from_date => NULL
387 ,p_from_number => NULL
388 ,p_position_id => NULL
389 ,p_range_name => l_rule_name
390 ,p_routing_category_id => p_routing_category_id
391 ,p_routing_list_member_id => NULL
392 ,p_to_char => NULL
393 ,p_to_date => NULL
394 ,p_to_number => NULL
395 ,p_object_version_number => p_object_version_number
396 ,p_effective_date => p_effective_date);
397
398 end if;
399 --
400 --
401 hr_utility.set_location('Leaving:'||l_proc, 10);
402 --
403 exception when others then
404
405 p_routing_category_id := l_routing_category_id;
406 p_object_version_number := l_object_version_number;
407 raise;
408 End;
409 --
410 -----------------------------------------------------------------------------
411 --
412 PROCEDURE create_default_approver
413 ( p_validate in boolean default false
414 ,p_attribute_range_id out nocopy number
415 ,p_approver_flag in varchar2 default null
416 ,p_enable_flag in varchar2 default 'Y'
417 ,p_assignment_id in number default null
418 ,p_attribute_id in number default null
419 ,p_position_id in number default null
420 ,p_range_name in out nocopy varchar2
421 ,p_routing_category_id in number
422 ,p_routing_list_member_id in number default null
423 ,p_object_version_number out nocopy number
424 ,p_effective_date in date
425 ) is
426 --
427 l_proc varchar2(72) := 'create_default_approver';
428 l_range_name varchar2(200) := p_range_name;
429 --
430 --
431 Cursor csr_chk_already_approver is
432 Select attribute_range_id,object_version_number
433 from pqh_attribute_ranges
434 Where routing_category_id = p_routing_category_id
435 And attribute_id is null
436 And nvl(routing_list_member_id,-99) = nvl(p_routing_list_member_id,-99)
437 And nvl(position_id,-99) = nvl(p_position_id,-99)
438 And nvl(p_assignment_id,-99) = nvl(p_assignment_id,-99)
439 And enable_flag = 'N';
440 --
441 Begin
442 --
443 hr_utility.set_location('Entering:'||l_proc, 5);
444 --
445 -- Check if the member was previously selected as approver and then disabled.
446 --
447 Open csr_chk_already_approver;
448 Fetch csr_chk_already_approver into p_attribute_range_id,p_object_version_number;
449 If csr_chk_already_approver%notfound then
450 --
451 --
452 -- Generate a system rule_name
453 --
454 p_range_name := generate_rule_name;
455 --
456 -- Create a rule with the above generated rule name and attribute
457 -- ranges value null
458 pqh_attribute_ranges_api.create_attribute_range(
459 p_validate => p_validate
460 ,p_attribute_range_id => p_attribute_range_id
461 ,p_approver_flag => p_approver_flag
462 ,p_enable_flag => p_enable_flag
463 ,p_delete_flag => NULL
464 ,p_assignment_id => p_assignment_id
465 ,p_attribute_id => p_attribute_id
466 ,p_from_char => NULL
467 ,p_from_date => NULL
468 ,p_from_number => NULL
469 ,p_position_id => p_position_id
470 ,p_range_name => p_range_name
471 ,p_routing_category_id => p_routing_category_id
472 ,p_routing_list_member_id => p_routing_list_member_id
473 ,p_to_char => NULL
474 ,p_to_date => NULL
475 ,p_to_number => NULL
476 ,p_object_version_number => p_object_version_number
477 ,p_effective_date => p_effective_date);
478 --
479 Else
480 --
481 pqh_attribute_ranges_api.update_attribute_range(
482 p_validate => p_validate
483 ,p_attribute_range_id => p_attribute_range_id
484 ,p_approver_flag => 'Y'
485 ,p_enable_flag => 'Y'
486 ,p_delete_flag => NULL
487 ,p_object_version_number => p_object_version_number
488 ,p_effective_date => p_effective_date);
489 --
490 End if;
491 --
492 Close csr_chk_already_approver;
493 --
494 hr_utility.set_location('Leaving:'||l_proc, 10);
495 --
496 exception when others then
497 p_range_name := l_range_name;
498 p_attribute_range_id := null;
499 p_object_version_number := null;
500 raise;
501 End;
502 --
503 -----------------------------------------------------------------------------
504 --
505 PROCEDURE update_default_approver
506 ( p_validate in boolean default false
507 ,p_attribute_range_id in number
508 ,p_approver_flag in varchar2 default null
509 ,p_enable_flag in varchar2 default 'Y'
510 ,p_assignment_id in number default null
511 ,p_attribute_id in number default null
512 ,p_position_id in number default null
513 ,p_range_name in varchar2
514 ,p_routing_category_id in number
515 ,p_routing_list_member_id in number default null
516 ,p_object_version_number in out nocopy number
517 ,p_effective_date in date
518 ) is
519 --
520 l_proc varchar2(72) := 'update_default_approver';
521 l_object_version_number number := p_object_version_number;
522 --
523 Begin
524 --
525 hr_utility.set_location('Entering:'||l_proc, 5);
526 --
527 --
528 --
529 pqh_attribute_ranges_api.update_attribute_range
530 (
531 p_validate => p_validate
532 ,p_attribute_range_id => p_attribute_range_id
533 ,p_approver_flag => p_approver_flag
534 ,p_enable_flag => p_enable_flag
535 ,p_delete_flag => NULL
536 ,p_assignment_id => p_assignment_id
537 ,p_attribute_id => p_attribute_id
538 ,p_from_char => NULL
539 ,p_from_date => NULL
540 ,p_from_number => NULL
541 ,p_position_id => p_position_id
542 ,p_range_name => p_range_name
543 ,p_routing_category_id => p_routing_category_id
544 ,p_routing_list_member_id => p_routing_list_member_id
545 ,p_to_char => NULL
546 ,p_to_date => NULL
547 ,p_to_number => NULL
548 ,p_object_version_number => p_object_version_number
549 ,p_effective_date => p_effective_date);
550 --
551 hr_utility.set_location('Leaving:'||l_proc, 10);
552 --
553 exception when others then
554 p_object_version_number := l_object_version_number;
555 raise;
556 End;
557 --
558 --------------------------------------------------------------------------
559 --
560 PROCEDURE delete_default_approver
561 ( p_validate in boolean default false
562 ,p_attribute_range_id in number
563 ,p_object_version_number in number
564 ,p_effective_date in date
565 ) is
566 l_proc varchar2(72) := 'delete_default_approver';
567 --
568 Begin
569 --
570 hr_utility.set_location('Entering:'||l_proc, 5);
571 --
572 --
573 -- Delete the authorization rule for the default approver.
574 --
575 pqh_attribute_ranges_api.delete_attribute_range
576 (
577 p_validate => p_validate
578 ,p_attribute_range_id => p_attribute_range_id
579 ,p_object_version_number => p_object_version_number
580 ,p_effective_date => p_effective_date
581 );
582 --
583 hr_utility.set_location('Leaving:'||l_proc, 10);
584 --
585 End;
586 --
587 --
588 -----------------------------------------------------------------------------
589 --
590 -- The following procedure sets the chosen attribute as routing attribute.
591 -- Any child attributes it may have are also set as routing attributes.
592 --
593 -- Parameters
594 -- -----------
595 -- p_txn_category_attribute_id Primary key of selected routing attribute
596 -- p_attribute_id Attribute id of selected routing attribute
597 -- p_transaction_category_id Transaction category to which the routing
598 -- attribute belongs to
599 --
600 --
601 --
602 PROCEDURE select_routing_attribute
603 (p_txn_category_attribute_id in number,
604 p_attribute_id in number,
605 p_transaction_category_id in number) is
606 --
607 -- The following cursor determines the ovn of the master attribute id
608 --
609 Cursor csr_master_attribute is
610 Select tca.object_version_number
611 From pqh_txn_category_attributes tca
612 Where txn_category_attribute_id = p_txn_category_attribute_id
613 for update nowait;
614 --
615 -- The foll cursor selects the txn_category_attribute_id of all the child
616 -- attributes of the passed master attribute
617 --
618 Cursor csr_child_attributes is
619 Select tca.txn_category_attribute_id,tca.object_version_number
620 From pqh_txn_category_attributes tca
621 Where tca.transaction_category_id = p_transaction_category_id
622 and tca.attribute_id in
623 (Select attribute_id
624 From pqh_attributes
625 Where master_attribute_id = p_attribute_id)
626 for update nowait;
627 --
628 l_ovn pqh_txn_category_attributes.object_version_number%type;
629 --
630 l_child_id pqh_txn_category_attributes.txn_category_attribute_id%type;
631 l_child_ovn pqh_txn_category_attributes.object_version_number%type;
632 --
633 l_proc varchar2(72) := 'select_routing_attribute';
634 --
635 Begin
636 --
637 hr_utility.set_location('Entering:'||l_proc, 5);
638 --
639 --
640 --
641 -- Mark the master as a routing attribute
642 --
643 Open csr_master_attribute;
644 Fetch csr_master_attribute into l_ovn;
645 Close csr_master_attribute;
646 --
647 pqh_txn_cat_attributes_api.update_TXN_CAT_ATTRIBUTE
648 (
649 p_validate => false
650 ,p_txn_category_attribute_id => p_txn_category_attribute_id
651 ,p_object_version_number => l_ovn
652 ,p_list_identifying_flag => 'Y'
653 ,p_value_style_cd => 'RANGE'
654 ,p_effective_date => sysdate
655 ,p_delete_attr_ranges_flag => 'N'
656 );
657
658 --
659 -- Mark each of the child attributes as a routing attribute
660 --
661
662 For child_rec in csr_child_attributes loop
663 --
664 pqh_txn_cat_attributes_api.update_TXN_CAT_ATTRIBUTE
665 (
666 p_validate => false
667 ,p_txn_category_attribute_id => child_rec.txn_category_attribute_id
668 ,p_object_version_number => child_rec.object_version_number
669 ,p_list_identifying_flag => 'Y'
670 ,p_value_style_cd => 'RANGE'
671 ,p_effective_date => sysdate
672 ,p_delete_attr_ranges_flag => 'N'
673 );
674 --
675 End loop;
676 --
677
678 --
679 hr_utility.set_location('Leaving:'||l_proc, 10);
680 --
681 End;
682 --
683 -----------------------------------------------------------------------------
684 --
685 -- Parameters
686 -- -----------
687 -- p_txn_category_attribute_id Primary key of un-selected routing attribute
688 -- p_attribute_id Attribute id of un-selected routing attribute
689 -- p_transaction_category_id Transaction category to which the routing
690 -- attribute belongs to
691 --
692 PROCEDURE unselect_routing_attribute
693 (p_txn_category_attribute_id in number,
694 p_attribute_id in number,
695 p_transaction_category_id in number) is
696 --
697 -- The following cursor determines the master attribute of the passed attribute
698 --
699 Cursor csr_master_attribute is
700 Select master_attribute_id
701 From pqh_attributes
702 Where attribute_id = p_attribute_id;
703 --
704 -- The foll cursor selects the txn_category_attribute_id of all the child
705 -- attributes of the selected master attribute
706 --
707 Cursor csr_child_attributes(p_master_attribute in number) is
708 Select tca.txn_category_attribute_id,tca.object_version_number
709 From pqh_txn_category_attributes tca
710 Where (tca.transaction_category_id = p_transaction_category_id and
711 tca.attribute_id = p_master_attribute
712 ) OR
713 tca.txn_category_attribute_id = p_txn_category_attribute_id
714 for update nowait;
715 --
716 l_master_attribute pqh_attributes.master_attribute_id%type;
717 --
718 l_proc varchar2(72) := 'unselect_routing_attribute';
719 --
720 Begin
721 --
722 hr_utility.set_location('Entering:'||l_proc, 5);
723 --
724 --
725 --
726 -- Select the master of the current routing attribute
727 --
728 Open csr_master_attribute;
729 Fetch csr_master_attribute into l_master_attribute;
730 Close csr_master_attribute;
731
732 --
733 -- UnMark the routing attributes
734 --
735
736 For child_rec in csr_child_attributes(l_master_attribute) loop
737 --
738 pqh_txn_cat_attributes_api.update_TXN_CAT_ATTRIBUTE
739 (
740 p_validate => false
741 ,p_txn_category_attribute_id => child_rec.txn_category_attribute_id
742 ,p_object_version_number => child_rec.object_version_number
743 ,p_list_identifying_flag => 'N'
744 ,p_value_style_cd => 'RANGE'
745 ,p_effective_date => sysdate
746 ,p_delete_attr_ranges_flag => 'I'
747 );
748 --
749 End loop;
750 --
751
752 --
753 hr_utility.set_location('Leaving:'||l_proc, 10);
754 --
755 End;
756 --
757 -----------------------------------------------------------------------------
758 --
759 --
760 -- Parameters
761 -- -----------
762 -- p_txn_category_attribute_id Primary key of selected authorization attribute
763 -- p_attribute_id Attribute id of selected authorization attribut
764 -- p_transaction_category_id Transaction category to which the authorization
765 -- attribute belongs to
766 --
767 PROCEDURE select_authorization_attribute
768 (p_txn_category_attribute_id in number,
769 p_attribute_id in number,
770 p_transaction_category_id in number) is
771 --
772 -- The following cursor determines the ovn of the master attribute id
773 --
774 Cursor csr_master_attribute is
775 Select tca.object_version_number
776 From pqh_txn_category_attributes tca
777 Where txn_category_attribute_id = p_txn_category_attribute_id
778 for update nowait;
779 --
780 -- The foll cursor selects the txn_category_attribute_id of all the child
781 -- attributes of the passed master attribute
782 --
783 Cursor csr_child_attributes is
784 Select tca.txn_category_attribute_id,tca.object_version_number
785 From pqh_txn_category_attributes tca
786 Where tca.transaction_category_id = p_transaction_category_id
787 and tca.attribute_id in
788 (Select attribute_id
789 From pqh_attributes
790 Where master_attribute_id = p_attribute_id)
791 for update nowait;
792 --
793 l_ovn pqh_txn_category_attributes.object_version_number%type;
794 --
795 l_child_id pqh_txn_category_attributes.txn_category_attribute_id%type;
796 l_child_ovn pqh_txn_category_attributes.object_version_number%type;
797 --
798 l_proc varchar2(72) := 'select_authorization_attribute';
799 --
800 Begin
801 --
802 hr_utility.set_location('Entering:'||l_proc, 5);
803 --
804 --
805 --
806 -- Mark the master as a authorization attribute
807 --
808 Open csr_master_attribute;
809 Fetch csr_master_attribute into l_ovn;
810 Close csr_master_attribute;
811 --
812 pqh_txn_cat_attributes_api.update_TXN_CAT_ATTRIBUTE
813 (
814 p_validate => false
815 ,p_txn_category_attribute_id => p_txn_category_attribute_id
816 ,p_object_version_number => l_ovn
817 ,p_member_identifying_flag => 'Y'
818 ,p_value_style_cd => 'RANGE'
819 ,p_effective_date => sysdate
820 ,p_delete_attr_ranges_flag => 'N'
821 );
822
823 --
824 -- Mark each of the child attributes as a authorization attribute
825 --
826
827 For child_rec in csr_child_attributes loop
828 --
829 pqh_txn_cat_attributes_api.update_TXN_CAT_ATTRIBUTE
830 (
831 p_validate => false
832 ,p_txn_category_attribute_id => child_rec.txn_category_attribute_id
833 ,p_object_version_number => child_rec.object_version_number
834 ,p_member_identifying_flag => 'Y'
835 ,p_value_style_cd => 'RANGE'
836 ,p_effective_date => sysdate
837 ,p_delete_attr_ranges_flag => 'N'
838 );
839 --
840 End loop;
841 --
842 --
843 hr_utility.set_location('Leaving:'||l_proc, 10);
844 --
845 End;
846 --
847 -----------------------------------------------------------------------------
848 --
849 --
850 -- Parameters
851 -- -----------
852 -- p_txn_category_attribute_id Primary key of un-selected auth attribute
853 -- p_attribute_id Attribute id of un-selected auth attribute
854 -- p_transaction_category_id Transaction category to which the authorization
855 -- attribute belongs to
856 --
857 PROCEDURE unselect_auth_attribute
858 (p_txn_category_attribute_id in number,
859 p_attribute_id in number,
860 p_transaction_category_id in number) is
861 --
862 -- The following cursor determines the master attribute of the passed attribute
863 --
864 Cursor csr_master_attribute is
865 Select master_attribute_id
866 From pqh_attributes
867 Where attribute_id = p_attribute_id;
868 --
869 -- The foll cursor selects the txn_category_attribute_id of all the child
870 -- attributes of the selected master attribute
871 --
872 Cursor csr_child_attributes(p_master_attribute in number) is
873 Select tca.txn_category_attribute_id,tca.object_version_number
874 From pqh_txn_category_attributes tca
875 Where (tca.transaction_category_id = p_transaction_category_id and
876 tca.attribute_id = p_master_attribute
877 ) OR
878 tca.txn_category_attribute_id = p_txn_category_attribute_id
879 for update nowait;
880 --
881 l_master_attribute pqh_attributes.master_attribute_id%type;
882 --
883 l_proc varchar2(72) := 'unselect_auth_attribute';
884 --
885 Begin
886 --
887 hr_utility.set_location('Entering:'||l_proc, 5);
888 --
889 --
890 --
891 -- Select the master of the current authorization attribute
892 --
893 Open csr_master_attribute;
894 Fetch csr_master_attribute into l_master_attribute;
895 Close csr_master_attribute;
896
897 --
898 -- UnMark the authorization attributes
899 --
900
901 For child_rec in csr_child_attributes(l_master_attribute) loop
902 --
903 pqh_txn_cat_attributes_api.update_TXN_CAT_ATTRIBUTE
904 (
905 p_validate => false
906 ,p_txn_category_attribute_id => child_rec.txn_category_attribute_id
907 ,p_object_version_number => child_rec.object_version_number
908 ,p_member_identifying_flag => 'N'
909 ,p_value_style_cd => 'RANGE'
910 ,p_effective_date => sysdate
911 ,p_delete_attr_ranges_flag => 'I'
912 );
913 --
914 End loop;
915 --
916 --
917 hr_utility.set_location('Leaving:'||l_proc, 10);
918 --
919 End;
920 --
921 ----------------------------------------------------------------------------
922 --
923 -- The foll function adds a null criteria to rules. This is a local function.
924 --
925 PROCEDURE Add_criteria_to_rules
926 (p_transaction_category_id in number,
927 p_identifier_type in varchar2) is
928 --
929 Cursor csr_adv_rct is
930 Select rct.routing_category_id
931 From pqh_routing_categories rct
932 Where rct.transaction_category_id = p_transaction_category_id
933 and nvl(rct.default_flag,'N') <> 'Y' ;
934 --
935 -- Cursor to return newly selected routing attributes
936 --
937 Cursor new_rout_attr is
938 Select attribute_id
939 from pqh_txn_category_attributes ptca
940 Where transaction_category_id = p_transaction_category_id
941 and list_identifying_flag = 'Y'
942 and not exists
943 (Select null
944 From pqh_attribute_ranges rng,pqh_routing_categories rct
945 Where rct.transaction_category_id = p_transaction_category_id
946 and nvl(rct.default_flag,'N') <> 'Y'
947 and rct.routing_category_id = rng.routing_category_id
948 and routing_list_member_id IS NULL
949 and position_id IS NULL
950 and assignment_id IS NULL
951 and ptca.attribute_id = rng.attribute_id);
952 --
953 -- Cursor to return all routing rule names
954 --
955 Cursor csr_rout_rule(p_routing_category_id in number) is
956 Select distinct rng.range_name,rng.enable_flag, nvl(rng.delete_flag,'N') delete_flag
957 From pqh_attribute_ranges rng
958 Where rng.routing_category_id = p_routing_category_id
959 and routing_list_member_id IS NULL
960 and position_id IS NULL
961 and assignment_id IS NULL
962 and attribute_id IS NOT NULL;
963 --
964 -- Cursor to return newly selected authorization attributes
965 --
966 Cursor new_auth_attr is
967 Select attribute_id
968 from pqh_txn_category_attributes tca
969 Where transaction_category_id = p_transaction_category_id
970 and member_identifying_flag = 'Y'
971 and not exists
972 (Select null
973 From pqh_attribute_ranges rng,pqh_routing_categories rct
974 Where rct.transaction_category_id = p_transaction_category_id
975 and nvl(rct.default_flag,'N') <> 'Y'
976 and rct.routing_category_id = rng.routing_category_id
977 and (routing_list_member_id IS NOT NULL or
978 position_id IS NOT NULL or
979 assignment_id IS NOT NULL)
980 and tca.attribute_id = rng.attribute_id);
981 --
982 -- Cursor to return all authorization rule names.
983 --
984 Cursor csr_auth_rule(p_routing_category_id in number) is
985 Select distinct
986 decode(routing_list_member_id,NULL,decode(position_id,NULL,'S','P'),'R') routing_style ,
987 nvl(routing_list_member_id,nvl(position_id,assignment_id)) member_id,
988 rng.range_name,
989 rng.approver_flag,
990 rng.enable_flag,
991 nvl(rng.delete_flag,'N') delete_flag
992 From pqh_attribute_ranges rng
993 Where rng.routing_category_id = p_routing_category_id
994 and (routing_list_member_id IS NOT NULL or
995 position_id IS NOT NULL or
996 assignment_id IS NOT NULL)
997 and attribute_id IS NOT NULL;
998 --
999 l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
1000 l_ovn pqh_attribute_ranges.object_version_number%type;
1001 l_routing_list_member_id pqh_attribute_ranges.routing_list_member_id%type;
1002 l_position_id pqh_attribute_ranges.position_id%type;
1003 l_assignment_id pqh_attribute_ranges.assignment_id%type;
1004 l_routing_category_id pqh_attribute_ranges.routing_category_id%type;
1005 --
1006 --
1007 l_proc varchar2(72) := 'Add_criteria_to_rules';
1008 --
1009 Begin
1010 --
1011 hr_utility.set_location('Entering:'||l_proc, 5);
1012 --
1013 --
1014 If p_identifier_type = 'ROUTING' then
1015 --
1016 For attr_rec in new_rout_attr loop
1017 --
1018 For rct_rec in csr_adv_rct loop
1019 --
1020 For rule_rec in csr_rout_rule(rct_rec.routing_category_id) loop
1021 --
1022 pqh_attribute_ranges_api.create_ATTRIBUTE_RANGE
1023 (
1024 p_validate => false
1025 ,p_attribute_range_id => l_attribute_range_id
1026 ,p_enable_flag => rule_rec.enable_flag
1027 ,p_delete_flag => rule_rec.delete_flag
1028 ,p_attribute_id => attr_rec.attribute_id
1029 ,p_range_name => rule_rec.range_name
1030 ,p_routing_category_id => rct_rec.routing_category_id
1031 ,p_object_version_number => l_ovn
1032 ,p_effective_date => sysdate
1033 );
1034
1035 End loop;
1036 End loop;
1037 End loop;
1038 --
1039 --
1040 Elsif p_identifier_type = 'AUTHORIZATION' then
1041 --
1042 --
1043 For attr_rec in new_auth_attr loop
1044 --
1045 For rct_rec in csr_adv_rct loop
1046 --
1047 For rule_rec in csr_auth_rule(rct_rec.routing_category_id) loop
1048 --
1049 If rule_rec.routing_style = 'R' then
1050 l_routing_list_member_id := rule_rec.member_id;
1051 l_position_id := NULL;
1052 l_assignment_id := NULL;
1053 ElsIf rule_rec.routing_style = 'P' then
1054 l_routing_list_member_id := NULL;
1055 l_position_id := rule_rec.member_id;
1056 l_assignment_id := NULL;
1057 ElsIf rule_rec.routing_style = 'S' then
1058 l_routing_list_member_id := NULL;
1059 l_position_id := NULL;
1060 l_assignment_id := rule_rec.member_id;
1061 End if;
1062 --
1063 pqh_attribute_ranges_api.create_ATTRIBUTE_RANGE
1064 (
1065 p_validate => false
1066 ,p_attribute_range_id => l_attribute_range_id
1067 ,p_enable_flag => rule_rec.enable_flag
1068 ,p_delete_flag => rule_rec.delete_flag
1069 ,p_attribute_id => attr_rec.attribute_id
1070 ,p_range_name => rule_rec.range_name
1071 ,p_routing_category_id => rct_rec.routing_category_id
1072 ,p_routing_list_member_id => l_routing_list_member_id
1073 ,p_position_id => l_position_id
1074 ,p_assignment_id => l_assignment_id
1075 ,p_approver_flag => rule_rec.approver_flag
1076 ,p_object_version_number => l_ovn
1077 ,p_effective_date => sysdate
1078 );
1079
1080 End loop;
1081 End loop;
1082 End loop;
1083 --
1084 --
1085 --
1086 End if;
1087 --
1088 hr_utility.set_location('Leaving:'||l_proc, 10);
1089 --
1090 End;
1091 --
1092 ----------------------------------------------------------------------------
1093 --
1094 --The following function does 2 things. If an existing routing attribute was
1095 -- de-selected , it removes from all rules under the transaction category,
1096 -- the part of the criteria containing the routing attribute that was now
1097 -- de-selected.
1098 -- When a new routing attribute is added, to all existing routing rules , a
1099 -- new criteria is added containing the selected routing attribute and with
1100 -- range values as null.
1101 --
1102 --
1103 -- Parameters
1104 -- -----------
1105 -- p_transaction_category_id Transaction category to which the routing
1106 -- attributes belong to
1107 --
1108 PROCEDURE Refresh_routing_rules(p_transaction_category_id in number)
1109 is
1110 --
1111 -- The foll cursor returns the part of routing rules containing the
1112 -- routing attributes which were de-selected.
1113 --
1114 Cursor csr_rct is
1115 Select rct.routing_category_id
1116 From pqh_routing_categories rct
1117 Where rct.transaction_category_id = p_transaction_category_id;
1118 --
1119 /**
1120 Cursor csr_old (p_routing_category_id in number) is
1121 Select rng.attribute_range_id,rng.object_version_number
1122 From pqh_attribute_ranges rng
1123 Where rng.routing_category_id = p_routing_category_id
1124 and routing_list_member_id IS NULL
1125 and position_id IS NULL
1126 and assignment_id IS NULL
1127 and attribute_id IS NOT NULL
1128 and attribute_id not in
1129 (Select attribute_id
1130 from pqh_txn_category_attributes
1131 Where transaction_category_id = p_transaction_category_id
1132 and list_identifying_flag = 'Y');
1133 **/
1134 --
1135 -- Perf changes
1136 Cursor csr_list_attr is
1137 SELECT ATTRIBUTE_ID FROM PQH_TXN_CATEGORY_ATTRIBUTES
1138 WHERE TRANSACTION_CATEGORY_ID = p_transaction_category_id AND LIST_IDENTIFYING_FLAG = 'Y';
1139 --
1140 Cursor csr_old(p_routing_category_id in number) is
1141 SELECT RNG.ATTRIBUTE_RANGE_ID,RNG.OBJECT_VERSION_NUMBER,RNG.ATTRIBUTE_ID
1142 FROM PQH_ATTRIBUTE_RANGES RNG
1143 WHERE RNG.ROUTING_CATEGORY_ID = p_routing_category_id
1144 and routing_list_member_id IS NULL
1145 and position_id IS NULL
1146 and assignment_id IS NULL
1147 AND ATTRIBUTE_ID IS NOT NULL ;
1148 --
1149 type attr_rec is record(attribute_id pqh_attributes.attribute_id%type);
1150 type attr_tab is table of attr_rec index by binary_integer;
1151 --
1152 l_attr_tab attr_tab;
1153 l_cnt number(15) := 0;
1154 l_dummy number(15) := 0;
1155 l_found boolean := false;
1156 l_proc varchar2(72) := 'Refresh_routing_rules';
1157 --
1158 Begin
1159 --
1160 hr_utility.set_location('Entering:'||l_proc, 5);
1161 --
1162 --
1163 -- Remove from all routing rules under the transaction category, the part of
1164 -- the criteria containing the routing attribute that was now de-selected.
1165 --
1166 For attr_rec in csr_list_attr loop
1167 l_cnt := l_cnt + 1;
1168 l_attr_tab(l_cnt).attribute_id := attr_rec.attribute_id;
1169 hr_utility.set_location('List Identifier:'||to_char(l_attr_tab(l_cnt).attribute_id), 5);
1170 End loop;
1171 --
1172 For rct_rec in csr_rct loop
1173 hr_utility.set_location('Getting routing category', 5);
1174 For old_rec in csr_old(p_routing_category_id => rct_rec.routing_category_id) loop
1175 --
1176 hr_utility.set_location('List Identifier:'||to_char(old_rec.attribute_range_id), 5);
1177 l_found := false;
1178 For l_dummy in 1..l_cnt loop
1179 If old_rec.attribute_id = l_attr_tab(l_dummy).attribute_id then
1180 hr_utility.set_location('Inside If', 5);
1181 l_found := true;
1182 End if;
1183 End loop;
1184 --
1185 If not l_found then
1186 hr_utility.set_location('calling delete_ATTRIBUTE_RANGE', 5);
1187 pqh_ATTRIBUTE_RANGES_api.delete_ATTRIBUTE_RANGE
1188 (p_validate => false
1189 ,p_attribute_range_id => old_rec.attribute_range_id
1190 ,p_object_version_number => old_rec.object_version_number
1191 ,p_effective_date => sysdate);
1192 End if;
1193 --
1194 End Loop;
1195 End Loop;
1196 --
1197 -- To all existing routing rules , add a new criteria containing
1198 -- the newly selected routing attribute and with range values as null.
1199 --
1200 Add_criteria_to_rules(p_transaction_category_id => p_transaction_category_id,
1201 p_identifier_type => 'ROUTING');
1202 --
1203 --
1204 disable_rout_hier_if_no_attr
1205 (p_transaction_category_id => p_transaction_category_id);
1206 --
1207 hr_utility.set_location('Leaving:'||l_proc, 10);
1208 --
1209 End;
1210 --
1211 -----------------------------------------------------------------------------
1212 --
1213 --The following function does 2 things. If an existing authorization attribute
1214 --was de-selected , it removes from all rules under the transaction category,
1215 --the part of the criteria containing the authorization attribute that was now
1216 --de-selected.
1217 --When a new authorization attribute is added, to all existing authorization
1218 --rules, a new criteria is added containing the selected authorization
1219 --attribute and with range values as null.
1220 --
1221 -- Parameters
1222 -- -----------
1223 -- p_transaction_category_id Transaction category to which the authorization
1224 -- attributes belong to
1225 --
1226
1227 PROCEDURE Refresh_authorization_rules(p_transaction_category_id in number) is
1228 --
1229 -- The foll cursor returns the part of authorization rules containing the
1230 -- authorization attributes which were de-selected.
1231 --
1232 Cursor csr_rct is
1233 Select rct.routing_category_id
1234 From pqh_routing_categories rct
1235 Where rct.transaction_category_id = p_transaction_category_id;
1236 --
1237 /**
1238 Cursor csr_old (p_routing_category_id in number) is
1239 Select rng.attribute_range_id,rng.object_version_number
1240 From pqh_attribute_ranges rng
1241 Where rng.routing_category_id = p_routing_category_id
1242 and (routing_list_member_id IS NOT NULL or
1243 position_id IS NOT NULL or
1244 assignment_id IS NOT NULL)
1245 and attribute_id IS NOT NULL
1246 and attribute_id not in
1247 (Select attribute_id
1248 from pqh_txn_category_attributes
1249 Where transaction_category_id = p_transaction_category_id
1250 and member_identifying_flag = 'Y');
1251 **/
1252 -- Perf changes
1253 Cursor csr_mem_attr is
1254 SELECT ATTRIBUTE_ID FROM PQH_TXN_CATEGORY_ATTRIBUTES
1255 WHERE TRANSACTION_CATEGORY_ID = p_transaction_category_id AND MEMBER_IDENTIFYING_FLAG = 'Y';
1256 --
1257 Cursor csr_auth(p_routing_category_id in number) is
1258 SELECT RNG.ATTRIBUTE_RANGE_ID,RNG.OBJECT_VERSION_NUMBER,RNG.ATTRIBUTE_ID
1259 FROM PQH_ATTRIBUTE_RANGES RNG
1260 WHERE RNG.ROUTING_CATEGORY_ID = p_routing_category_id
1261 AND (ROUTING_LIST_MEMBER_ID IS NOT NULL OR POSITION_ID IS NOT NULL OR ASSIGNMENT_ID IS NOT NULL)
1262 AND ATTRIBUTE_ID IS NOT NULL ;
1263 --
1264 type attr_rec is record(attribute_id pqh_attributes.attribute_id%type);
1265 type attr_tab is table of attr_rec index by binary_integer;
1266 --
1267 l_attr_tab attr_tab;
1268 l_cnt number(15) := 0;
1269 l_dummy number(15) := 0;
1270 l_found boolean := false;
1271 l_proc varchar2(72) := 'Refresh_authorization_rules';
1272 --
1273 Begin
1274 --
1275 hr_utility.set_location('Entering:'||l_proc, 5);
1276 --
1277 For attr_rec in csr_mem_attr loop
1278 l_cnt := l_cnt + 1;
1279 l_attr_tab(l_cnt).attribute_id := attr_rec.attribute_id;
1280 End loop;
1281 --
1282 -- Remove from all authorization rules under the transaction category,
1283 -- the part of the criteria containing the authorization attribute
1284 -- that was now de-selected.
1285 --
1286 For rct_rec in csr_rct loop
1287 For old_rec in csr_auth(p_routing_category_id => rct_rec.routing_category_id) loop
1288 --
1289 l_found := false;
1290 For l_dummy in 1..l_cnt loop
1291 If old_rec.attribute_id = l_attr_tab(l_dummy).attribute_id then
1292 l_found := true;
1293 End if;
1294 End loop;
1295 --
1296 If not l_found then
1297 pqh_ATTRIBUTE_RANGES_api.delete_ATTRIBUTE_RANGE
1298 (p_validate => false
1299 ,p_attribute_range_id => old_rec.attribute_range_id
1300 ,p_object_version_number => old_rec.object_version_number
1301 ,p_effective_date => sysdate);
1302 End if;
1303
1304 End Loop;
1305 End Loop;
1306 --
1307 --
1308 -- To all existing authorization rules, add a new criteria containing
1309 -- the newly selected authorization attribute and with range values as null.
1310 --
1311 Add_criteria_to_rules(p_transaction_category_id => p_transaction_category_id,
1312 p_identifier_type => 'AUTHORIZATION');
1313 --
1314 hr_utility.set_location('Leaving:'||l_proc, 10);
1315 --
1316 End;
1317 --
1318 --------------------------------------------------------------------------------
1319 --
1320 PROCEDURE disable_rout_hier_if_no_attr(p_transaction_category_id in number) is
1321 --
1322 -- The foll cursor returns any routing or authorization attributes setup for the
1323 -- passed transaction category.
1324 --
1325 Cursor csr_attr is
1326 Select attribute_id
1327 from pqh_txn_category_attributes
1328 Where transaction_category_id = p_transaction_category_id
1329 and nvl(identifier_flag,'N') = 'Y'
1330 and list_identifying_flag = 'Y';
1331 --
1332 -- The foll cursor returns all non-default routing hierarchies under the current
1333 -- transaction category.
1334 --
1335 Cursor csr_rout_hier is
1336 Select routing_category_id,object_version_number
1337 from pqh_routing_categories
1338 Where transaction_category_id = p_transaction_category_id
1339 and decode(routing_list_id,NULL,decode( Position_structure_id,NULL,'S','P'),'R') = (Select member_cd
1340 from pqh_transaction_categories
1341 Where transaction_category_id = p_transaction_category_id)
1342 and nvl(default_flag,'N') <> 'Y'
1343 and nvl(enable_flag,'Y') = 'Y';
1344 --
1345 l_attribute_id pqh_txn_category_attributes.attribute_id%type;
1346 l_proc varchar2(72) := 'disable_rout_hier_if_no_attr';
1347 --
1348 Begin
1349 --
1350 hr_utility.set_location('Entering:'||l_proc, 5);
1351 --
1352 Open csr_attr;
1353 Fetch csr_attr into l_attribute_id;
1354 If csr_attr%notfound then
1355 --
1356 For old_rec in csr_rout_hier loop
1357 --
1358 pqh_ROUTING_CATEGORIES_api.update_ROUTING_CATEGORY
1359 (
1360 p_validate => false
1361 ,p_routing_category_id => old_rec.routing_category_id
1362 ,p_enable_flag => 'N'
1363 ,p_object_version_number => old_rec.object_version_number
1364 ,p_effective_date => sysdate
1365 );
1366 --
1367 End Loop;
1368 End if;
1369 Close csr_attr;
1370 --
1371 hr_utility.set_location('Leaving:'||l_proc, 10);
1372 --
1373 End;
1374 --
1375 ------------------------------------------------------------------------------
1376 --
1377 --This foll function returns TRUE if any rules have been set up for the
1378 --routing hierarchy
1379 --
1380 FUNCTION chk_rules_exist (p_routing_category_id in number)
1381 RETURN BOOLEAN is
1382 --
1383 -- Foll cursor checks if there are any rules for the passed routing category
1384 -- It returns true if there are any rules. Else it returns False.
1385 -- Only non-default rules are taken into consideration.
1386 --
1387 Cursor csr_rules_exist is
1388 Select null
1389 From pqh_attribute_ranges
1390 Where routing_category_id = p_routing_category_id
1391 and attribute_range_id IS NOT NULL;
1392 --
1393 l_dummy varchar2(1);
1394 l_proc varchar2(72) := 'chk_rules_exist';
1395 --
1396 Begin
1397 --
1398 hr_utility.set_location('Entering:'||l_proc, 5);
1399 --
1400 --
1401 Open csr_rules_exist;
1402 --
1403 Fetch csr_rules_exist into l_dummy;
1404 --
1405 -- If there are no rules return FALSE;
1406 --
1407 If csr_rules_exist%notfound then
1408 Close csr_rules_exist;
1409 RETURN FALSE;
1410 End if;
1411 --
1412 Close csr_rules_exist;
1413 --
1414 -- If there is at least 1 routing / authorization rule, then return TRUE.
1415 --
1416 RETURN TRUE;
1417 --
1418 --
1419 hr_utility.set_location('Leaving:'||l_proc, 10);
1420 --
1421 End;
1422 --
1423 --
1424 -----------------------------------------------------------------------------
1425 --
1426 -- The foll function checks if there are any routing history for the
1427 -- input routing category and returns TRUE if there is any routing history.
1428 --
1429 FUNCTION chk_routing_history_exists (p_routing_category_id in number)
1430 RETURN BOOLEAN is
1431 --
1432 -- Foll cursor checks if there is any routing history for the passed routing category
1433 --
1434 Cursor csr_hist_exist is
1435 Select null
1436 From pqh_routing_history
1437 Where routing_category_id = p_routing_category_id;
1438 --
1439 l_dummy varchar2(1);
1440 --
1441 l_proc varchar2(72) := 'chk_routing_history_exists';
1442 --
1443 Begin
1444 --
1445 hr_utility.set_location('Entering:'||l_proc, 5);
1446 --
1447 --
1448 Open csr_hist_exist;
1449 --
1450 Fetch csr_hist_exist into l_dummy;
1451 --
1452 -- If there is no routing history return FALSE;
1453 --
1454 If csr_hist_exist%notfound then
1455 Close csr_hist_exist;
1456 RETURN FALSE;
1457 End if;
1458 --
1459 Close csr_hist_exist;
1460 --
1461 -- If there is routing history, then return TRUE.
1462 --
1463 RETURN TRUE;
1464 --
1465 --
1466 hr_utility.set_location('Leaving:'||l_proc, 10);
1467 --
1468 End;
1469 -----------------------------------------------------------------------------
1470 PROCEDURE get_all_attribute_range_id(p_routing_category_id in number,
1471 p_range_name in varchar2,
1472 p_rule_type in varchar2,
1473 p_all_attribute_range_id out nocopy varchar2) is
1474 --
1475 Cursor csr_rout_rule is
1476 Select attribute_range_id
1477 From pqh_attribute_ranges
1478 Where routing_category_id = p_routing_category_id
1479 and range_name = p_range_name
1480 and routing_list_member_id is NULL
1481 and position_id IS NULL
1482 and assignment_id IS NULL;
1483 --
1484 Cursor csr_auth_rule is
1485 Select attribute_range_id
1486 From pqh_attribute_ranges
1487 Where routing_category_id = p_routing_category_id
1488 and range_name = p_range_name
1489 and (routing_list_member_id is NOT NULL or
1490 position_id IS NOT NULL or
1491 assignment_id IS NOT NULL);
1492 --
1493 l_proc varchar2(72) := 'get_all_attribute_range_id';
1494 --
1495 Begin
1496 --
1497 hr_utility.set_location('Entering:'||l_proc, 5);
1498 --
1499 --
1500 p_all_attribute_range_id := NULL;
1501 --
1502 If p_rule_type = 'ROUTING' then
1503 --
1504 for id_rec in csr_rout_rule loop
1505 --
1506 p_all_attribute_range_id := p_all_attribute_range_id || id_rec.attribute_range_id||',';
1507 --
1508 End loop;
1509 --
1510 p_all_attribute_range_id := substr(p_all_attribute_range_id,1,length(p_all_attribute_range_id) - 1);
1511 --
1512 Elsif p_rule_type = 'AUTHORIZATION' then
1513 --
1514 for id_rec in csr_auth_rule loop
1515 --
1516 p_all_attribute_range_id := p_all_attribute_range_id || id_rec.attribute_range_id||',';
1517 --
1518 End loop;
1519 --
1520 p_all_attribute_range_id := substr(p_all_attribute_range_id,1,length(p_all_attribute_range_id) - 1);
1521 --
1522 End if;
1523 --
1524 --
1525 hr_utility.set_location('Leaving:'||l_proc, 10);
1526 --
1527 exception when others then
1528 p_all_attribute_range_id := null;
1529 raise;
1530 End;
1531 --
1532 -----------------------------------------------------------------------------
1533 --
1534 -- The foll procedure creates a rule with the passed range name and using all
1535 -- the selected routing attributes.
1536 --
1537 -- Parameters
1538 -- ----------
1539 -- p_transaction_category_id Transaction category id
1540 -- p_routing_category_id Primary key
1541 -- p_range_name Rule name to be created
1542 -- p_all_attribute_range_id Concatenated attribute_range_id's
1543 --
1544 PROCEDURE create_routing_rule(p_transaction_category_id in number,
1545 p_routing_category_id in number,
1546 p_range_name in varchar2,
1547 p_delete_flag in varchar2,
1548 p_enable_flag in varchar2,
1549 p_all_attribute_range_id out nocopy varchar2) is
1550 --
1551 Cursor csr_rout_attr is
1552 Select attribute_id
1553 from pqh_txn_category_attributes
1554 Where transaction_category_id = p_transaction_category_id
1555 and list_identifying_flag = 'Y';
1556 --
1557 --
1558 l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
1559 l_ovn pqh_attribute_ranges.object_version_number%type;
1560 --
1561 l_proc varchar2(72) := 'create_routing_rule';
1562 --
1563 Begin
1564 --
1565 hr_utility.set_location('Entering:'||l_proc, 5);
1566 --
1567 --
1568 -- For the input a routing category and rule,insert all the selected
1569 -- routing attributes with attribute ranges value null
1570 --
1571 p_all_attribute_range_id := NULL;
1572 --
1573 For attr_rec in csr_rout_attr loop
1574 --
1575 pqh_attribute_ranges_api.create_ATTRIBUTE_RANGE
1576 (
1577 p_validate => false
1578 ,p_attribute_range_id => l_attribute_range_id
1579 ,p_enable_flag => p_enable_flag
1580 ,p_delete_flag => p_delete_flag
1581 ,p_attribute_id => attr_rec.attribute_id
1582 ,p_range_name => p_range_name
1583 ,p_routing_category_id => p_routing_category_id
1584 ,p_object_version_number => l_ovn
1585 ,p_effective_date => sysdate
1586 );
1587 --
1588 p_all_attribute_range_id := p_all_attribute_range_id ||to_char(l_attribute_range_id)||',';
1589 --
1590 End loop;
1591 --
1592 -- Returning the attribute range id's for this rule in a string.
1593 --
1594 p_all_attribute_range_id := substr(p_all_attribute_range_id,1,length(p_all_attribute_range_id) - 1);
1595 --
1596
1597 --
1598 hr_utility.set_location('Leaving:'||l_proc, 10);
1599 --
1600 exception when others then
1601 p_all_attribute_range_id := null;
1602 raise;
1603 End;
1604 --
1605 --
1606 -----------------------------------------------------------------------------
1607 --
1608 -- This is a local function . It is called both of update_routing_rule and
1609 -- update_authorization_rule procedures.
1610 --
1611 --
1612 PROCEDURE update_rule(p_routing_category_id in number,
1613 p_range_name in varchar2,
1614 p_enable_flag in varchar2,
1615 p_delete_flag in varchar2 default NULL,
1616 p_approver_flag in varchar2 default NULL,
1617 p_all_attribute_range_id in varchar2) is
1618 --
1619 type cur_type IS REF CURSOR;
1620 csr_update_rule cur_type;
1621 sql_stmt varchar2(2000);
1622 --
1623 l_all_attribute_range_id varchar2(2000);
1624 att_range_rec pqh_attribute_ranges%ROWTYPE;
1625 --
1626 l_proc varchar2(72) := 'update_rule';
1627 --
1628 Begin
1629 --
1630 hr_utility.set_location('Entering:'||l_proc, 5);
1631 --
1632 l_all_attribute_range_id := p_all_attribute_range_id;
1633 --
1634 if l_all_attribute_range_id IS NULL then
1635 l_all_attribute_range_id := '-999';
1636 End if;
1637 --
1638 --
1639 sql_stmt := 'Select * from pqh_attribute_ranges where attribute_range_id in ('
1640 || l_all_attribute_range_id
1641 ||') for update nowait';
1642 --
1643 -- We have the sql_stmt that we can execute.
1644 --
1645 Open csr_update_rule for sql_stmt;
1646 --
1647 --
1648 Loop
1649 --
1650 Fetch csr_update_rule into att_range_rec;
1651 --
1652 If csr_update_rule%NOTFOUND then
1653 Exit;
1654 End if;
1655 --
1656 pqh_attribute_ranges_api.update_ATTRIBUTE_RANGE
1657 (
1658 p_validate => false
1659 ,p_attribute_range_id => att_range_rec.attribute_range_id
1660 ,p_approver_flag => p_approver_flag
1661 ,p_enable_flag => p_enable_flag
1662 ,p_delete_flag => p_delete_flag
1663 ,p_range_name => p_range_name
1664 ,p_routing_category_id => p_routing_category_id
1665 ,p_object_version_number => att_range_rec.object_version_number
1666 ,p_effective_date => sysdate
1667 );
1668 --
1669 End loop;
1670 --
1671 Close csr_update_rule;
1672 --
1673 --
1674 hr_utility.set_location('Leaving:'||l_proc, 10);
1675 --
1676 End;
1677 --
1678 -------------------------------------------------------------------------------------
1679 --
1680 --
1681 -- The foll procedure updates the rule name , enable_flag and approver flag on all
1682 -- the attribute range records belonging to this rule
1683 --
1684 -- Parameters
1685 -- ----------
1686 -- p_routing_category_id Primary key
1687 -- p_range_name Rule name to be created
1688 -- p_enable_flag 'Y' means enable
1689 -- p_approver_flag NULL
1690 -- p_all_attribute_range_id Concatenated attribute_range_id's
1691 --
1692 PROCEDURE update_routing_rule(p_routing_category_id in number,
1693 p_range_name in varchar2,
1694 p_enable_flag in varchar2,
1695 p_approver_flag in varchar2 default NULL,
1696 p_delete_flag in varchar2 default NULL,
1697 p_all_attribute_range_id in varchar2) is
1698 --
1699 l_proc varchar2(72) := 'update_routing_rule';
1700 --
1701 Begin
1702 --
1703 hr_utility.set_location('Entering:'||l_proc, 5);
1704 --
1705 --
1706 update_rule(p_routing_category_id => p_routing_category_id,
1707 p_range_name => p_range_name,
1708 p_enable_flag => p_enable_flag,
1709 p_delete_flag => p_delete_flag,
1710 p_approver_flag => p_approver_flag,
1711 p_all_attribute_range_id => p_all_attribute_range_id);
1712 --
1713 --
1714 hr_utility.set_location('Leaving:'||l_proc, 10);
1715 --
1716 End;
1717 --
1718 --------------------------------------------------------------------------------------
1719 --
1720 -- This is a local function . It is called both of delete_routing_rule and
1721 -- delete_authorization_rule procedures.
1722 --
1723 PROCEDURE delete_rule(p_routing_category_id in number,
1724 p_all_attribute_range_id in varchar2) is
1725 --
1726 type cur_type IS REF CURSOR;
1727 csr_delete_rule cur_type;
1728 sql_stmt varchar2(2000);
1729 --
1730 l_id pqh_attribute_ranges.attribute_range_id%TYPE;
1731 l_ovn pqh_attribute_ranges.object_version_number%TYPE;
1732 --
1733 l_all_attribute_range_id varchar2(2000);
1734 --
1735 l_proc varchar2(72) := 'delete_rule';
1736 --
1737 Begin
1738 --
1739 hr_utility.set_location('Entering:'||l_proc, 5);
1740 --
1741 l_all_attribute_range_id := p_all_attribute_range_id;
1742 --
1743 if l_all_attribute_range_id IS NULL then
1744 l_all_attribute_range_id := '-999';
1745 End if;
1746 --
1747 sql_stmt := 'Select attribute_range_id,object_version_number from pqh_attribute_ranges where routing_category_id = :routing_category_id and attribute_range_id in ('
1748 || l_all_attribute_range_id
1749 ||') for update nowait';
1750 --
1751 -- We have the sql_stmt that we can execute.
1752 --
1753
1754 Open csr_delete_rule for sql_stmt using p_routing_category_id;
1755 --
1756 Loop
1757 --
1758 Fetch csr_delete_rule into l_id,l_ovn;
1759 --
1760 If csr_delete_rule%NOTFOUND then
1761 Exit;
1762 End if;
1763 --
1764 pqh_attribute_ranges_api.delete_ATTRIBUTE_RANGE
1765 (
1766 p_validate => false
1767 ,p_attribute_range_id => l_id
1768 ,p_object_version_number => l_ovn
1769 ,p_effective_date => sysdate
1770 );
1771 --
1772 End loop;
1773 --
1774 Close csr_delete_rule;
1775 --
1776 --
1777 hr_utility.set_location('Leaving:'||l_proc, 10);
1778 --
1779 End;
1780 --
1781 ----------------------------------------------------------------------------------
1782 --
1783 -- The foll procedure deletes all the attribute range records belonging to the
1784 -- passed rule
1785 --
1786 -- Parameters
1787 -- ----------
1788 -- p_routing_category_id Primary key
1789 -- p_all_attribute_range_id Concatenated attribute_range_id's
1790 --
1791 --
1792 PROCEDURE delete_routing_rule(p_routing_category_id in number,
1793 p_all_attribute_range_id in varchar2) is
1794 --
1795 l_proc varchar2(72) := 'delete_routing_rule';
1796 --
1797 Begin
1798 --
1799 hr_utility.set_location('Entering:'||l_proc, 5);
1800 --
1801 --
1802 delete_rule(p_routing_category_id => p_routing_category_id,
1803 p_all_attribute_range_id => p_all_attribute_range_id);
1804 --
1805 --
1806 hr_utility.set_location('Leaving:'||l_proc, 10);
1807 --
1808 End;
1809 --
1810 -----------------------------------------------------------------------------
1811 --
1812 -- The following procedure creates a system rule of the approver , using all the
1813 -- authorization attributes previously selected.
1814 -- If routing_list_member_id is available pass this as input. NULL should
1815 -- passed to the position id and assignment_id.
1816 -- Similarly , if position is available, pass this as input. NULL should
1817 -- be passed to routing_list_member_id and assignment id.
1818 -- The same holds good if assignment id is available. NULL should
1819 -- be passed to routing_list_member_id and position.
1820 --
1821 -- It returns the system generated rule name.
1822 -- NOTE !! When displaying the rules for the approver, display all rules
1823 -- except the system generated rule.
1824 --
1825 --
1826 PROCEDURE create_approver ( p_transaction_category_id in number,
1827 p_routing_category_id in number,
1828 p_routing_list_member_id in number,
1829 p_position_id in number,
1830 p_assignment_id in number,
1831 p_approver_flag in varchar2 ,
1832 p_gen_sys_rule_name out nocopy varchar2) is
1833 --
1834 Cursor csr_auth_attr is
1835 Select attribute_id
1836 from pqh_txn_category_attributes
1837 Where transaction_category_id = p_transaction_category_id
1838 and member_identifying_flag = 'Y';
1839 --
1840 Cursor csr_chk_already_approver is
1841 Select attribute_range_id,object_version_number
1842 from pqh_attribute_ranges
1843 Where routing_category_id = p_routing_category_id
1844 And nvl(routing_list_member_id,-99) = nvl(p_routing_list_member_id,-99)
1845 And nvl(position_id,-99) = nvl(p_position_id,-99)
1846 And nvl(p_assignment_id,-99) = nvl(p_assignment_id,-99)
1847 And attribute_id is not null
1848 And enable_flag = 'N';
1849 --
1850 l_gen_sys_rule_name pqh_attribute_ranges.range_name%type;
1851 l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
1852 l_ovn pqh_attribute_ranges.object_version_number%type;
1853 l_create_appr boolean := true;
1854 --
1855 l_proc varchar2(72) := 'create_approver';
1856 --
1857 Begin
1858 --
1859 hr_utility.set_location('Entering:'||l_proc, 5);
1860 --
1861 For exist_appr_rec in csr_chk_already_approver loop
1862 --
1863 l_create_appr := false;
1864 --
1865 pqh_attribute_ranges_api.update_attribute_range(
1866 p_validate => false
1867 ,p_attribute_range_id => exist_appr_rec.attribute_range_id
1868 ,p_approver_flag => 'Y'
1869 ,p_enable_flag => 'Y'
1870 ,p_delete_flag => NULL
1871 ,p_object_version_number => exist_appr_rec.object_version_number
1872 ,p_effective_date => trunc(sysdate));
1873 --
1874 End loop;
1875 --
1876 If l_create_appr then
1877 --
1878 --
1879 -- Generate system rule name
1880 --
1881 p_gen_sys_rule_name := generate_rule_name;
1882 --
1883 -- For the input a routing category and rule,insert all the selected
1884 -- authorization attributes with attribute ranges value null
1885 --
1886 For attr_rec in csr_auth_attr loop
1887 --
1888 pqh_attribute_ranges_api.create_ATTRIBUTE_RANGE
1889 (
1890 p_validate => false
1891 ,p_attribute_range_id => l_attribute_range_id
1892 ,p_routing_category_id => p_routing_category_id
1893 ,p_range_name => p_gen_sys_rule_name
1894 ,p_attribute_id => attr_rec.attribute_id
1895 ,p_routing_list_member_id => p_routing_list_member_id
1896 ,p_position_id => p_position_id
1897 ,p_assignment_id => p_assignment_id
1898 ,p_enable_flag => 'Y'
1899 ,p_delete_flag => NULL
1900 ,p_approver_flag => p_approver_flag
1901 ,p_object_version_number => l_ovn
1902 ,p_effective_date => sysdate
1903 );
1904 --
1905 --
1906 End loop;
1907 --
1908 --
1909 End if;
1910 --
1911 hr_utility.set_location('Leaving:'||l_proc, 10);
1912 --
1913 exception when others then
1914 p_gen_sys_rule_name := null;
1915 raise;
1916 End;
1917 --
1918 -----------------------------------------------------------------------------
1919 --
1920 -- The procedure updates the member as approver as in all the authorization rules
1921 -- except the default authorization rules.
1922 -- If routing_list_member_id is available pass this as input. NULL should
1923 -- passed to the position id and assignment_id.
1924 -- Similarly , if position is available, pass this as input. NULL should
1925 -- be passed to routing_list_member_id and assignment id.
1926 -- The same holds good if assignment id is available. NULL should
1927 -- be passed to routing_list_member_id and position.
1928 --
1929 --
1930 --
1931 PROCEDURE update_approver (p_routing_category_id in number,
1932 p_routing_style in varchar2,
1933 p_routing_list_member_id in number,
1934 p_position_id in number,
1935 p_assignment_id in number,
1936 p_approver_flag in varchar2 ) is
1937 --
1938 type cur_type IS REF CURSOR;
1939 csr_update_approver cur_type;
1940 sql_stmt varchar2(2000);
1941 --
1942 l_id pqh_attribute_ranges.attribute_range_id%TYPE;
1943 l_ovn pqh_attribute_ranges.object_version_number%TYPE;
1944 --
1945 l_dummy_id number(10);
1946 l_proc varchar2(72) := 'update_approver';
1947 --
1948 Begin
1949 --
1950 hr_utility.set_location('Entering:'||l_proc, 5);
1951 --
1952 --
1953 -- For the input a routing category , and authorizer
1954 -- Delete all non-default rules for the authorizer under the
1955 -- routing category
1956 --
1957 sql_stmt := 'Select attribute_range_id,object_version_number from pqh_attribute_ranges where routing_category_id = :routing_category_id and attribute_id is not null and ';
1958 --
1959 If p_routing_style = 'R' then
1960 --
1961 l_dummy_id := p_routing_list_member_id;
1962 sql_stmt := sql_stmt || 'routing_list_member_id = :approver_id';
1963 --
1964 Elsif p_routing_style = 'P' then
1965 --
1966 l_dummy_id := p_position_id;
1967 sql_stmt := sql_stmt || 'position_id = :approver_id';
1968 --
1969 Elsif p_routing_style = 'S' then
1970 --
1971 l_dummy_id := p_assignment_id;
1972 sql_stmt := sql_stmt || 'assignment_id = :approver_id';
1973 --
1974 End if;
1975 --
1976 sql_stmt := sql_stmt || ' For update nowait';
1977 --
1978 --
1979 Open csr_update_approver for sql_stmt using p_routing_category_id,l_dummy_id;
1980 --
1981 Loop
1982 --
1983 Fetch csr_update_approver into l_id,l_ovn;
1984 --
1985 If csr_update_approver%notfound then
1986 exit;
1987 End if;
1988 --
1989 pqh_attribute_ranges_api.update_ATTRIBUTE_RANGE
1990 (
1991 p_validate => false
1992 ,p_attribute_range_id => l_id
1993 ,p_approver_flag => p_approver_flag
1994 ,p_enable_flag => 'Y'
1995 ,p_delete_flag => NULL
1996 ,p_routing_category_id => p_routing_category_id
1997 ,p_object_version_number => l_ovn
1998 ,p_effective_date => sysdate
1999 );
2000 --
2001 End loop;
2002 --
2003 Close csr_update_approver;
2004 --
2005 --
2006 hr_utility.set_location('Leaving:'||l_proc, 10);
2007 --
2008 End;
2009 --
2010 --------------------------------------------------------------------------
2011 -- The foll procedure deletes all the authorization rules for the given
2012 -- approver.
2013 -- Pass the routing Style as Input - 'R'/'P'/'S'
2014 -- If routing_list_member_id is available pass this as input. NULL should
2015 -- passed to the position id and assignment_id.
2016 -- Similarly , if position is available, pass this as input. NULL should
2017 -- be passed to routing_list_member_id and assignment id.
2018 -- The same holds good if assignment id is available. NULL should
2019 -- be passed to routing_list_member_id and position.
2020 --
2021 --
2022 --
2023 PROCEDURE delete_approver (p_routing_category_id in number,
2024 p_routing_style in varchar2,
2025 p_routing_list_member_id in number,
2026 p_position_id in number,
2027 p_assignment_id in number ) is
2028 --
2029 type cur_type IS REF CURSOR;
2030 csr_delete_approver cur_type;
2031 sql_stmt varchar2(2000);
2032 --
2033 l_id pqh_attribute_ranges.attribute_range_id%TYPE;
2034 l_ovn pqh_attribute_ranges.object_version_number%TYPE;
2035 --
2036 l_dummy_id number(10);
2037 l_proc varchar2(72) := 'delete_approver';
2038 --
2039 Begin
2040 --
2041 hr_utility.set_location('Entering:'||l_proc, 5);
2042 --
2043 --
2044 -- For the input a routing category , and authorizer
2045 -- Delete all non-default rules for the authorizer under the
2046 -- routing category
2047 --
2048 sql_stmt := 'Select attribute_range_id,object_version_number from pqh_attribute_ranges where routing_category_id = :routing_category_id and attribute_id is not null and ';
2049 --
2050 If p_routing_style = 'R' then
2051 --
2052 l_dummy_id := p_routing_list_member_id;
2053 sql_stmt := sql_stmt || 'routing_list_member_id = :approver_id';
2054 --
2055 Elsif p_routing_style = 'P' then
2056 --
2057 l_dummy_id := p_position_id;
2058 sql_stmt := sql_stmt || 'position_id = :approver_id';
2059 --
2060 Elsif p_routing_style = 'S' then
2061 --
2062 l_dummy_id := p_assignment_id;
2063 sql_stmt := sql_stmt || 'assignment_id = :approver_id';
2064 --
2065 End if;
2066 --
2067 sql_stmt := sql_stmt || ' For update nowait';
2068 --
2069 Open csr_delete_approver for sql_stmt using p_routing_category_id,l_dummy_id;
2070 --
2071 Loop
2072 --
2073 Fetch csr_delete_approver into l_id,l_ovn;
2074 --
2075 If csr_delete_approver%notfound then
2076 exit;
2077 End if;
2078 --
2079 pqh_attribute_ranges_api.delete_ATTRIBUTE_RANGE
2080 (
2081 p_validate => false
2082 ,p_attribute_range_id => l_id
2083 ,p_object_version_number => l_ovn
2084 ,p_effective_date => sysdate
2085 );
2086 --
2087 End loop;
2088 --
2089 Close csr_delete_approver;
2090 --
2091 --
2092 hr_utility.set_location('Leaving:'||l_proc, 10);
2093 --
2094 End;
2095 --
2096 PROCEDURE update_approver_flag(p_routing_category_id in number,
2097 p_routing_style in varchar2,
2098 p_routing_list_member_id in number,
2099 p_position_id in number,
2100 p_assignment_id in number,
2101 p_approver_flag in varchar2 ) is
2102 --
2103 type cur_type IS REF CURSOR;
2104 csr_delete_approver cur_type;
2105 sql_stmt varchar2(2000);
2106 --
2107 l_id pqh_attribute_ranges.attribute_range_id%TYPE;
2108 l_ovn pqh_attribute_ranges.object_version_number%TYPE;
2109 l_appr_flag pqh_attribute_ranges.approver_flag%type;
2110 --
2111 l_dummy_id number(10);
2112 l_proc varchar2(72) := 'update_approver_flag';
2113 --
2114 Begin
2115 --
2116 hr_utility.set_location('Entering:'||l_proc, 5);
2117 --
2118 --
2119 -- For the input a routing category , and authorizer
2120 -- Delete all non-default rules for the authorizer under the
2121 -- routing category
2122 --
2123 sql_stmt := 'Select attribute_range_id,object_version_number,approver_flag from pqh_attribute_ranges where routing_category_id = :routing_category_id and attribute_id is not null and ';
2124 --
2125 --
2126 If p_routing_style = 'R' then
2127 --
2128 l_dummy_id := p_routing_list_member_id;
2129 sql_stmt := sql_stmt || 'routing_list_member_id = :approver_id';
2130 --
2131 Elsif p_routing_style = 'P' then
2132 --
2133 l_dummy_id := p_position_id;
2134 sql_stmt := sql_stmt || 'position_id = :approver_id';
2135 --
2136 Elsif p_routing_style = 'S' then
2137 --
2138 l_dummy_id := p_assignment_id;
2139 sql_stmt := sql_stmt || 'assignment_id = :approver_id';
2140 --
2141 End if;
2142 --
2143 sql_stmt := sql_stmt || ' For update nowait';
2144 --
2145 Open csr_delete_approver for sql_stmt using p_routing_category_id,l_dummy_id;
2146 --
2147 Loop
2148 --
2149 Fetch csr_delete_approver into l_id,l_ovn,l_appr_flag;
2150 --
2151 If csr_delete_approver%notfound then
2152 exit;
2153 End if;
2154 --
2155 --
2156 pqh_attribute_ranges_api.update_ATTRIBUTE_RANGE
2157 (p_validate => false
2158 ,p_attribute_range_id => l_id
2159 ,p_approver_flag => p_approver_flag
2160 ,p_object_version_number => l_ovn
2161 ,p_effective_date => sysdate
2162 );
2163 --
2164 --
2165 End loop;
2166 --
2167 Close csr_delete_approver;
2168 --
2169 --
2170 hr_utility.set_location('Leaving:'||l_proc, 10);
2171 --
2172 End;
2173 --
2174 --
2175 -----------------------------------------------------------------------------
2176 --
2177 -- The foll procedure creates a authorization rule using the auth
2178 -- attributes previously selected.
2179 -- If routing_list_member_id is available pass this as input. NULL should
2180 -- passed to the position id and assignment_id.
2181 -- Similarly , if position is available, pass this as input. NULL should
2182 -- be passed to routing_list_member_id and assignment id.
2183 -- The same holds good if assignment id is available. NULL should
2184 -- be passed to routing_list_member_id and position.
2185 --
2186 --
2187 PROCEDURE create_authorization_rule (
2188 p_transaction_category_id in number,
2189 p_routing_category_id in number,
2190 p_routing_list_member_id in number,
2191 p_position_id in number,
2192 p_assignment_id in number,
2193 p_approver_flag in varchar2,
2194 p_delete_flag in varchar2,
2195 p_enable_flag in varchar2,
2196 p_range_name in varchar2,
2197 p_all_attribute_range_id out nocopy varchar2) is
2198 --
2199 Cursor csr_auth_attr is
2200 Select attribute_id
2201 from pqh_txn_category_attributes
2202 Where transaction_category_id = p_transaction_category_id
2203 and member_identifying_flag = 'Y';
2204 --
2205 Cursor csr_sys_rule is
2206 Select attribute_range_id,object_version_number
2207 From pqh_attribute_ranges
2208 Where routing_category_id = p_routing_category_id
2209 and range_name like 'PQH_$$SYS$$%'
2210 and attribute_id is NOT NULL
2211 and nvl(routing_list_member_id,-99) = nvl(p_routing_list_member_id,-99)
2212 and nvl(position_id,-99) = nvl(p_position_id,-99)
2213 and nvl(assignment_id ,-99) = nvl(p_assignment_id,-99)
2214 For update nowait;
2215 --
2216 l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
2217 l_ovn pqh_attribute_ranges.object_version_number%type;
2218 l_proc varchar2(72) := 'create_authorization_rule';
2219 --
2220 --
2221 Begin
2222 --
2223 hr_utility.set_location('Entering:'||l_proc, 5);
2224 --
2225 --
2226 -- For the input a routing category and rule,insert all the selected
2227 -- authorization attributes with attribute ranges value null
2228 --
2229 p_all_attribute_range_id := NULL;
2230 --
2231 For attr_rec in csr_auth_attr loop
2232 --
2233 pqh_attribute_ranges_api.create_ATTRIBUTE_RANGE
2234 (
2235 p_validate => false
2236 ,p_attribute_range_id => l_attribute_range_id
2237 ,p_routing_category_id => p_routing_category_id
2238 ,p_range_name => p_range_name
2239 ,p_attribute_id => attr_rec.attribute_id
2240 ,p_routing_list_member_id => p_routing_list_member_id
2241 ,p_position_id => p_position_id
2242 ,p_assignment_id => p_assignment_id
2243 ,p_enable_flag => p_enable_flag
2244 ,p_delete_flag => p_delete_flag
2245 ,p_approver_flag => p_approver_flag
2246 ,p_object_version_number => l_ovn
2247 ,p_effective_date => sysdate
2248 );
2249 --
2250 p_all_attribute_range_id := p_all_attribute_range_id ||to_char(l_attribute_range_id)||',';
2251 --
2252 End loop;
2253 --
2254 -- Returning the attribute range id's for this rule in a string.
2255 --
2256 p_all_attribute_range_id := substr(p_all_attribute_range_id,1,length(p_all_attribute_range_id) - 1);
2257 --
2258 -- If there were any system rules for this approver , that we created to save approver
2259 -- information , we can delete those rules.
2260 --
2261 For sys_rec in csr_sys_rule loop
2262 --
2263 pqh_attribute_ranges_api.delete_ATTRIBUTE_RANGE
2264 (
2265 p_validate => false
2266 ,p_attribute_range_id => sys_rec.attribute_range_id
2267 ,p_object_version_number => sys_rec.object_version_number
2268 ,p_effective_date => sysdate
2269 );
2270 --
2271 End loop;
2272 --
2273 --
2274 hr_utility.set_location('Leaving:'||l_proc, 10);
2275 --
2276 exception when others then
2277 p_all_attribute_range_id := null;
2278 raise;
2279 End;
2280 --
2281 --
2282 -----------------------------------------------------------------------------
2283 -- The following function updates all the attribute range records
2284 -- for the authoriztaion rule.
2285 --
2286 PROCEDURE update_authorization_rule
2287 (p_routing_category_id in number,
2288 p_range_name in varchar2,
2289 p_enable_flag in varchar2,
2290 p_approver_flag in varchar2 default NULL,
2291 p_delete_flag in varchar2 default NULL,
2292 p_all_attribute_range_id in varchar2) is
2293 --
2294 l_proc varchar2(72) := 'update_authorization_rule';
2295 --
2296 Begin
2297 --
2298 hr_utility.set_location('Entering:'||l_proc, 5);
2299 --
2300 --
2301 update_rule(p_routing_category_id => p_routing_category_id,
2302 p_range_name => p_range_name,
2303 p_enable_flag => p_enable_flag,
2304 p_approver_flag => p_approver_flag,
2305 p_delete_flag => p_delete_flag,
2306 p_all_attribute_range_id => p_all_attribute_range_id);
2307 --
2308 --
2309 hr_utility.set_location('Leaving:'||l_proc, 10);
2310 --
2311 End;
2312 --
2313 --
2314 -----------------------------------------------------------------------------
2315 -- The following function deletes all the attribute range records
2316 -- for the authoriztaion rule.
2317 --
2318 PROCEDURE delete_authorization_rule (p_routing_category_id in number,
2319 p_all_attribute_range_id in varchar2) is
2320 --
2321 --
2322 l_proc varchar2(72) := 'delete_authorization_rule';
2323 --
2324 Begin
2325 --
2326 hr_utility.set_location('Entering:'||l_proc, 5);
2327 --
2328 --
2329 delete_rule(p_routing_category_id => p_routing_category_id,
2330 p_all_attribute_range_id => p_all_attribute_range_id);
2331 --
2332 --
2333 hr_utility.set_location('Leaving:'||l_proc, 10);
2334 --
2335 End;
2336 --
2337 --
2338 -----------------------------------------------------------------------------
2339 PROCEDURE create_local_setup(p_transaction_category_id in out nocopy NUMBER,
2340 p_language in varchar2,
2341 p_business_group_id in number) IS
2342
2343 --- Cursor to copy transaction category detail
2344 CURSOR csr_transaction_category IS
2345 SELECT *
2346 FROM PQH_TRANSACTION_CATEGORIES
2347 WHERE TRANSACTION_CATEGORY_ID = p_transaction_category_id
2348 AND BUSINESS_GROUP_ID IS NULL;
2349
2350 --- Cursor to copy default hierarchy detail
2351 CURSOR csr_routing_category IS
2352 SELECT *
2353 FROM PQH_ROUTING_CATEGORIES
2354 WHERE TRANSACTION_CATEGORY_ID = p_transaction_category_id
2355 AND NVL(ENABLE_FLAG, 'N') = 'Y'
2356 AND NVL(DEFAULT_FLAG,'N') = 'Y';
2357
2358 --- Cursor to copy default approver detail
2359 CURSOR csr_default_approver(p_routing_category_id NUMBER) IS
2360 SELECT *
2361 FROM pqh_attribute_ranges
2362 WHERE ROUTING_CATEGORY_ID = p_routing_category_id
2363 AND NVL(ENABLE_FLAG, 'N')= 'Y'
2364 AND NVL(APPROVER_FLAG,'N')= 'Y';
2365 --
2366 Cursor csr_attr is
2367 Select *
2368 from pqh_txn_category_attributes
2369 Where transaction_category_id = p_transaction_category_id;
2370 --
2371 attr_rec pqh_txn_category_Attributes%ROWTYPE;
2372 l_id pqh_txn_category_Attributes.txn_category_attribute_id%type;
2373 l_ovn pqh_txn_category_Attributes.object_version_number%type;
2374 --
2375 tct_rec pqh_transaction_categories%ROWTYPE;
2376 tct_id pqh_transaction_categories.transaction_category_id%type;
2377 tct_ovn pqh_transaction_categories.object_version_number%type;
2378 --
2379 rct_rec pqh_routing_categories%ROWTYPE;
2380 rct_id pqh_routing_categories.transaction_category_id%type;
2381 rct_ovn pqh_routing_categories.object_version_number%type;
2382 --
2383 rng_id pqh_attribute_ranges.attribute_range_id%type;
2384 rng_ovn pqh_attribute_ranges.object_version_number%type;
2385 --
2386 l_proc varchar2(72) := 'create_local_setup';
2387 --
2388 BEGIN
2389 --
2390 hr_utility.set_location('Entering:'||l_proc, 5);
2391 --
2392 --
2393 -- Copying the transaction category details
2394 --
2395 open csr_transaction_category;
2396 fetch csr_transaction_category into tct_rec;
2397 close csr_transaction_category;
2398 --
2399 pqh_tran_category_api.create_tran_category
2400 (p_validate => false
2401 ,p_member_cd => tct_rec.member_cd
2402 ,p_post_style_cd => tct_rec.post_style_cd
2403 ,p_timeout_days => tct_rec.timeout_days
2404 ,p_post_txn_function => tct_rec.post_txn_function
2405 ,p_transaction_category_id => tct_id
2406 ,p_name => tct_rec.name
2407 ,p_short_name => tct_rec.short_name
2408 ,p_custom_workflow_name => tct_rec.custom_workflow_name
2409 ,p_form_name => tct_rec.form_name
2410 ,p_object_version_number => tct_ovn
2411 ,p_future_action_cd => tct_rec.future_action_cd
2412 ,p_custom_wf_process_name => tct_rec.custom_wf_process_name
2413 ,p_freeze_status_cd => NULL
2414 ,p_route_validated_txn_flag => tct_rec.route_validated_txn_flag
2415 ,p_workflow_enable_flag => tct_rec.workflow_enable_flag
2416 ,p_enable_flag => 'Y'
2417 ,p_consolidated_table_route_id => tct_rec.consolidated_table_route_id
2418 ,p_master_table_route_id => tct_rec.master_table_route_id
2419 ,p_effective_date => sysdate
2420 ,p_language_code => p_language
2421 ,p_business_Group_id => p_business_group_id
2422 ,p_setup_type_cd => NULL);
2423 --
2424 -- Copy txn category attributes
2425 --
2426 --
2427 --
2428 Open csr_attr;
2429 Loop
2430
2431 Fetch csr_attr into attr_rec;
2432 Exit when csr_attr%notfound;
2433 --
2434 pqh_txn_cat_attributes_api.create_TXN_CAT_ATTRIBUTE
2435 (
2436 p_validate => false
2437 ,p_txn_category_attribute_id => l_id
2438 ,p_attribute_id => attr_rec.attribute_id
2439 ,p_transaction_category_id => tct_id
2440 ,p_value_set_id => attr_rec.value_set_id
2441 ,p_object_version_number => l_ovn
2442 ,p_transaction_table_route_id => attr_rec.transaction_table_route_id
2443 ,p_form_column_name => attr_rec.form_column_name
2444 ,p_identifier_flag => attr_rec.identifier_flag
2445 ,p_list_identifying_flag => NULL
2446 ,p_member_identifying_flag => NULL
2447 ,p_refresh_flag => attr_rec.refresh_flag
2448 ,p_select_flag => attr_rec.select_flag
2449 ,p_value_style_cd => attr_rec.value_style_cd
2450 ,p_effective_date => sysdate
2451 );
2452
2453 End loop;
2454 --
2455 --
2456 -- Copy default hierarchy
2457 --
2458
2459 open csr_routing_category;
2460 loop
2461 fetch csr_routing_category into rct_rec;
2462
2463 exit when csr_routing_category%notfound;
2464
2465 Select pqh_routing_categories_s.nextval into rct_id from dual;
2466
2467 insert into pqh_routing_categories (
2468 routing_category_id,
2469 transaction_category_id,
2470 enable_flag,
2471 default_flag,
2472 routing_list_id,
2473 position_structure_id,
2474 override_position_id,
2475 override_assignment_id,
2476 override_role_id,
2477 object_version_number)
2478 Values (
2479 rct_id,
2480 tct_id,
2481 rct_rec.enable_flag,
2482 rct_rec.default_flag,
2483 rct_rec.routing_list_id,
2484 rct_rec.position_structure_id,
2485 rct_rec.override_position_id,
2486 rct_rec.override_assignment_id,
2487 rct_rec.override_role_id,
2488 1);
2489
2490 --
2491 -- Copy approver
2492 --
2493 for rng_rec in csr_default_approver(p_routing_category_id => rct_rec.routing_category_id)
2494 loop
2495
2496 Select pqh_attribute_ranges_s.nextval into rng_id from dual;
2497
2498 insert into pqh_attribute_ranges(
2499 attribute_range_id,
2500 approver_flag,
2501 enable_flag,
2502 assignment_id,
2503 attribute_id,
2504 from_char,
2505 from_date,
2506 from_number,
2507 position_id,
2508 range_name,
2509 routing_category_id,
2510 routing_list_member_id,
2511 to_char,
2512 to_date,
2513 to_number,
2514 object_version_number)
2515
2516 Values(
2517 rng_id,
2518 rng_rec.approver_flag,
2519 rng_rec.enable_flag,
2520 rng_rec.assignment_id,
2521 rng_rec.attribute_id,
2522 rng_rec.from_char,
2523 rng_rec.from_date,
2524 rng_rec.from_number,
2525 rng_rec.position_id,
2526 rng_rec.range_name,
2527 rct_id,
2528 rng_rec.routing_list_member_id,
2529 rng_rec.to_char,
2530 rng_rec.to_date,
2531 rng_rec.to_number,
2532 1);
2533
2534
2535 end loop;
2536
2537 end loop;
2538
2539 close csr_routing_category;
2540 --
2541 p_transaction_category_id := tct_id;
2542 --
2543 --
2544 hr_utility.set_location('Leaving:'||l_proc, 10);
2545 --
2546 END;
2547 -----------------------------------------------------------------------------
2548 PROCEDURE freeze_category (p_transaction_category_id in number,
2549 p_setup_type_cd in varchar2,
2550 p_freeze_status_cd in varchar2) is
2551 --
2552 l_proc varchar2(72) := 'freeze_category';
2553 --
2554 BEGIN
2555 --
2556 hr_utility.set_location('Entering:'||l_proc, 5);
2557 --
2558 --
2559 Update pqh_transaction_categories
2560 set freeze_status_cd = p_freeze_status_cd
2561 ,setup_type_cd = p_setup_type_cd
2562 where transaction_category_id = p_transaction_category_id;
2563 --
2564 --
2565 --
2566 hr_utility.set_location('Leaving:'||l_proc, 10);
2567 --
2568 END;
2569 -----------------------------------------------------------------------------
2570
2571 FUNCTION chk_range_name_unique (p_routing_category_id in number,
2572 p_range_name in varchar2,
2573 p_attribute_id_list in varchar2,
2574 p_primary_flag in varchar2)
2575 RETURN BOOLEAN is
2576 type cur_type IS REF CURSOR;
2577 range_name_cur cur_type;
2578 sql_stmt varchar2(1000);
2579 exist_range_name pqh_attribute_ranges.range_name%type;
2580 --
2581 l_proc varchar2(72) := 'chk_range_name_unique';
2582 --
2583 Begin
2584 --
2585 hr_utility.set_location('Entering:'||l_proc, 5);
2586 --
2587
2588 sql_stmt := 'Select distinct range_name from pqh_attribute_ranges where routing_category_id = :r AND attribute_id IS NOT NULL AND attribute_range_id not in ( ' || p_attribute_id_list || ') ';
2589
2590 if p_primary_flag = 'Y' then
2591 sql_stmt := sql_stmt ||' AND routing_list_member_id is NULL AND position_id is NULL AND assignment_id is NULL';
2592 else
2593 sql_stmt := sql_stmt ||' AND (routing_list_member_id is NOT NULL OR position_id is NOT NULL OR assignment_id is NOT NULL)';
2594 end if;
2595
2596 open range_name_cur for sql_stmt using p_routing_category_id;
2597
2598 Loop
2599 Fetch range_name_cur into exist_range_name;
2600 exit when range_name_cur%notfound;
2601 if exist_range_name = p_range_name then
2602 Return FALSE;
2603 end if;
2604 End loop;
2605 Return TRUE;
2606 --
2607 hr_utility.set_location('Leaving:'||l_proc, 10);
2608 --
2609 End;
2610 --------------------------------------------------------------------------------------------
2611
2612 PROCEDURE load_row (
2613 p_canvas_name in varchar2,
2614 p_form_name in varchar2,
2615 p_current_item in varchar2,
2616 p_previous_item in varchar2,
2617 p_next_item in varchar2,
2618 p_enable_finish_flag in varchar2,
2619 p_post_flag in varchar2,
2620 p_seq_no in number,
2621 p_finish_item in varchar2,
2622 p_refresh_msg_flag in varchar2,
2623 p_image_name in varchar2,
2624 p_warning_item in varchar2,
2625 p_image_item in varchar2,
2626 p_line_size in number,
2627 p_owner in varchar2,
2628 p_last_update_date in varchar2 ) IS
2629
2630 --
2631 l_language varchar2(30) ;
2632 --
2633 l_created_by pqh_wizard_canvases.created_by%TYPE;
2634 l_last_updated_by pqh_wizard_canvases.last_updated_by%TYPE;
2635 l_creation_date pqh_wizard_canvases.creation_date%TYPE;
2636 l_last_update_date pqh_wizard_canvases.last_update_date%TYPE;
2637 l_last_update_login pqh_wizard_canvases.last_update_login%TYPE;
2638 --
2639 --
2640
2641 l_rowid ROWID;
2642 l_wizard_canvas_id NUMBER;
2643
2644 cursor c1 is select userenv('LANG') from dual ;
2645
2646 cursor csr_wiz_canvas is
2647 select rowid
2648 from pqh_wizard_canvases
2649 where canvas_name = p_canvas_name
2650 and form_name = p_form_name;
2651
2652 --and current_item = p_current_item;
2653 l_data_migrator_mode varchar2(1);
2654 --
2655
2656 begin
2657
2658 --
2659 l_data_migrator_mode := hr_general.g_data_migrator_mode ;
2660 hr_general.g_data_migrator_mode := 'Y';
2661 open c1;
2662 fetch c1 into l_language ;
2663 close c1;
2664 --
2665
2666 --
2667 -- populate WHO columns
2668 --
2669 /**
2670 if p_owner = 'SEED' then
2671 l_created_by := 1;
2672 l_last_updated_by := 1;
2673 else
2674 l_created_by := 0;
2675 l_last_updated_by := 0;
2676 end if;
2677 **/
2678 l_created_by := fnd_load_util.owner_id(p_owner);
2679 l_last_updated_by := fnd_load_util.owner_id(p_owner);
2680 --
2681 /**
2682 l_creation_date := sysdate;
2683 l_last_update_date := sysdate;
2684 **/
2685 l_creation_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
2686 l_last_update_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
2687 l_last_update_login := 0;
2688
2689 OPEN csr_wiz_canvas;
2690 FETCH csr_wiz_canvas INTO l_rowid;
2691 CLOSE csr_wiz_canvas;
2692
2693 if ( l_rowid is null ) THEN
2694 select pqh_wizard_canvases_s.NEXTVAL into l_wizard_canvas_id from dual;
2695
2696 insert into pqh_wizard_canvases (
2697 WIZARD_CANVAS_ID ,
2698 CANVAS_NAME ,
2699 CURRENT_ITEM ,
2700 PREVIOUS_ITEM ,
2701 NEXT_ITEM ,
2702 ENABLE_FINISH_FLAG ,
2703 POST_FLAG ,
2704 SEQ_NO ,
2705 FINISH_ITEM ,
2706 REFRESH_MSG_FLAG ,
2707 FORM_NAME ,
2708 IMAGE_NAME ,
2709 WARNING_ITEM ,
2710 IMAGE_ITEM ,
2711 LINE_SIZE ,
2712 LAST_UPDATE_DATE,
2713 LAST_UPDATED_BY,
2714 LAST_UPDATE_LOGIN,
2715 CREATED_BY,
2716 CREATION_DATE )
2717 values (
2718 l_wizard_canvas_id ,
2719 P_CANVAS_NAME ,
2720 P_CURRENT_ITEM ,
2721 P_PREVIOUS_ITEM ,
2722 P_NEXT_ITEM ,
2723 P_ENABLE_FINISH_FLAG ,
2724 P_POST_FLAG ,
2725 P_SEQ_NO ,
2726 P_FINISH_ITEM ,
2727 P_REFRESH_MSG_FLAG ,
2728 P_FORM_NAME ,
2729 P_IMAGE_NAME ,
2730 P_WARNING_ITEM ,
2731 P_IMAGE_ITEM ,
2732 P_LINE_SIZE ,
2733 l_last_update_date,
2734 l_last_updated_by,
2735 l_last_update_login,
2736 l_created_by,
2737 l_creation_date );
2738 else
2739 update pqh_wizard_canvases
2740 set
2741 CANVAS_NAME = P_CANVAS_NAME ,
2742 CURRENT_ITEM = P_CURRENT_ITEM ,
2743 PREVIOUS_ITEM = P_PREVIOUS_ITEM ,
2744 NEXT_ITEM = P_NEXT_ITEM ,
2745 ENABLE_FINISH_FLAG = P_ENABLE_FINISH_FLAG ,
2746 POST_FLAG = P_POST_FLAG ,
2747 SEQ_NO = P_SEQ_NO ,
2748 FINISH_ITEM = P_FINISH_ITEM ,
2749 REFRESH_MSG_FLAG = P_REFRESH_MSG_FLAG ,
2750 FORM_NAME = P_FORM_NAME ,
2751 IMAGE_NAME = P_IMAGE_NAME ,
2752 WARNING_ITEM = P_WARNING_ITEM ,
2753 IMAGE_ITEM = P_IMAGE_ITEM ,
2754 LINE_SIZE = P_LINE_SIZE ,
2755 LAST_UPDATE_DATE = l_LAST_UPDATE_DATE ,
2756 LAST_UPDATED_BY = l_LAST_UPDATED_BY ,
2757 LAST_UPDATE_LOGIN = l_LAST_UPDATE_LOGIN ,
2758 CREATED_BY = l_CREATED_BY ,
2759 CREATION_DATE = l_CREATION_DATE
2760 where ROWID = l_rowid ;
2761
2762 end if;
2763 hr_general.g_data_migrator_mode := l_data_migrator_mode;
2764 end load_row;
2765 --------------------------------------------------------------------------------------------
2766 --
2767 -- This function checks if there are any errors in standard setup and returns false in case
2768 -- there are errors.
2769 --
2770 Function check_errors_in_std_setup(p_transaction_category_id in number,
2771 p_error_messages out nocopy warnings_tab)
2772 RETURN boolean IS
2773 --
2774 Cursor csr_member_cd(p_transaction_category_id in number) is
2775 Select member_cd
2776 from pqh_transaction_categories
2777 Where transaction_category_id = p_transaction_category_id;
2778 --
2779 Cursor csr_def_hier (p_transaction_category_id in number,
2780 p_member_cd in varchar2) is
2781 Select routing_category_id
2782 from pqh_routing_categories_v
2783 where member_cd = p_member_cd
2784 and transaction_category_id = p_transaction_category_id
2785 and nvl(enable_flag,'Y') = 'Y'
2786 and nvl(default_flag,'N') = 'Y';
2787 --
2788 Cursor csr_def_approvers (p_routing_category_id in number) is
2789 Select null
2790 from pqh_attribute_ranges_v3
2791 Where routing_category_id = p_routing_category_id
2792 and nvl(approver_flag,'N') = 'Y'
2793 and nvl(enable_flag,'Y') = 'Y';
2794 --
2795 l_routing_category_id pqh_routing_categories.routing_category_id%type;
2796 l_member_cd pqh_transaction_categories.member_cd%type;
2797 --
2798 l_dummy varchar2(1);
2799 l_error_status boolean := TRUE;
2800 --
2801 l_error_index number(10) := 0;
2802 Begin
2803 --
2804 -- There can be two type of errors in standard setup.
2805 -- 1) There is no default hierarchy
2806 -- 2) No enabled default approvers.
2807 --
2808 -- Obtain the routing style of the transcation category.
2809 --
2810 Open csr_member_cd(p_transaction_category_id => p_transaction_category_id);
2811 Fetch csr_member_cd into l_member_cd;
2812 Close csr_member_cd;
2813 --
2814 Open csr_def_hier(p_transaction_category_id => p_transaction_category_id,
2815 p_member_cd => l_member_cd);
2816 Fetch csr_def_hier into l_routing_category_id;
2817 --
2818 -- Check if any default hierarchy is marked
2819 --
2820 If csr_def_hier%notfound then
2821 --
2822 -- set error
2823 --
2824 l_error_index := l_error_index + 1;
2825 hr_utility.set_message(8302,'PQH_TCW_STD_ERROR1');
2826 p_error_messages(l_error_index).message_text := hr_utility.get_message;
2827 --
2828 l_error_index := l_error_index + 1;
2829 hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2830 p_error_messages(l_error_index).message_text := hr_utility.get_message;
2831 --
2832 l_error_status := FALSE;
2833 Else
2834 --
2835 -- Check if there are any enable default approvers.
2836 --
2837 Open csr_def_approvers(p_routing_category_id => l_routing_category_id);
2838 Fetch csr_def_approvers into l_dummy;
2839 If csr_def_approvers%notfound then
2840 --
2841 l_error_index := l_error_index + 1;
2842 hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2843 p_error_messages(l_error_index).message_text := hr_utility.get_message;
2844 l_error_status := FALSE;
2845 --
2846 End if;
2847 Close csr_def_approvers;
2848 --
2849 End if;
2850 --
2851 Close csr_def_hier;
2852 --
2853 RETURN l_error_status;
2854 --
2855 End;
2856 ----------------------------------------------------------------------------------------------
2857 Function chk_valid_rout_hier_exists(p_transaction_category_id in number,
2858 p_routing_type in varchar2,
2859 p_error_messages out nocopy warnings_tab,
2860 p_no_errors out nocopy varchar2)
2861 RETURN BOOLEAN is
2862 --
2863 TYPE cur_type IS REF CURSOR;
2864 csr_routing cur_type;
2865 sql_stmt varchar2(1000);
2866 --
2867 l_rec_count number(10) := 0;
2868 l_no_of_rules number(10) := 0;
2869 l_no_of_errors number(10) := 0;
2870 --
2871 l_routing_category_id pqh_routing_categories.routing_category_id%type;
2872 l_list_name varchar2(200);
2873 --
2874 type rct_rec is record(routing_category_id pqh_routing_categories.routing_category_id%type,
2875 default_flag pqh_routing_categories.default_flag%type,
2876 delete_flag pqh_routing_categories.delete_flag%type);
2877 type rct_tab is table of rct_rec index by binary_integer;
2878 --
2879 l_rct_tab rct_tab;
2880 l_cnt number(15) := 0;
2881 l_dummy number(15) := 0;
2882 l_x varchar2(10);
2883 --
2884 l_proc varchar2(72) := 'chk_valid_rout_hier_exists';
2885 --
2886
2887 --Perf changes
2888 Cursor csr_ph is
2889 Select rct.routing_category_id,rct.default_flag, rct.delete_flag
2890 from pqh_routing_categories rct
2891 Where rct.transaction_category_id = p_transaction_category_id
2892 and rct.enable_flag = 'Y'
2893 and rct.position_structure_id IS NOT NULL;
2894 --
2895 Cursor csr_sh is
2896 Select rct.routing_category_id,rct.default_flag, rct.delete_flag
2897 from pqh_routing_categories rct
2898 Where rct.transaction_category_id = p_transaction_category_id
2899 and rct.enable_flag = 'Y'
2900 and rct.routing_list_id IS NULL and rct.position_structure_id IS NULL;
2901 --
2902 Cursor csr_rl is
2903 Select rct.routing_category_id,rct.default_flag, rct.delete_flag
2904 from pqh_routing_categories rct
2905 Where rct.transaction_category_id = p_transaction_category_id
2906 and rct.enable_flag = 'Y'
2907 and rct.routing_list_id IS NOT NULL;
2908 --
2909 Cursor csr_rules(p_routing_category_id in number) is
2910 Select 'x' from pqh_attribute_ranges rng
2911 Where rng.routing_category_id = p_routing_category_id
2912 and rng.enable_flag = 'Y'
2913 and nvl(delete_flag,'N') <> 'Y'
2914 and rng.routing_list_member_id IS NULL
2915 and rng.position_id IS NULL
2916 and rng.assignment_id IS NULL;
2917 --
2918 Begin
2919 --
2920 hr_utility.set_location('Entering:'||l_proc, 5);
2921 --
2922 --
2923 -- The foll cursor selects the no of enabled routing categories exist for a
2924 -- transaction category,and how many routing rules exists under each routing
2925 -- category.
2926 --
2927 If p_routing_type = 'R' then
2928 For rl_rec in csr_rl loop
2929 If nvl(rl_rec.default_flag,'X') <> 'Y' and nvl(rl_rec.delete_flag,'X') <> 'Y' then
2930 l_cnt := l_cnt + 1;
2931 l_rct_tab(l_cnt).routing_category_id := rl_rec.routing_category_id;
2932 End if;
2933 End loop;
2934 Elsif p_routing_type = 'P' then
2935 For ph_rec in csr_ph loop
2936 If nvl(ph_rec.default_flag,'X') <> 'Y' and nvl(ph_rec.delete_flag,'X') <> 'Y' then
2937 l_cnt := l_cnt + 1;
2938 l_rct_tab(l_cnt).routing_category_id := ph_rec.routing_category_id;
2939 End if;
2940 End loop;
2941 Else
2942 For sh_rec in csr_sh loop
2943 If nvl(sh_rec.default_flag,'X') <> 'Y' and nvl(sh_rec.delete_flag,'X') <> 'Y' then
2944 l_cnt := l_cnt + 1;
2945 l_rct_tab(l_cnt).routing_category_id := sh_rec.routing_category_id;
2946 End if;
2947 End loop;
2948 End if;
2949 --
2950 --
2951 l_rec_count := 0;
2952 --
2953 If l_cnt > 0 then
2954 For l_dummy in 1..l_cnt loop
2955 --
2956 --
2957 hr_utility.set_location('Getting rules for'||to_char(l_rct_tab(l_dummy).routing_category_id), 100);
2958 Open csr_rules(l_rct_tab(l_dummy).routing_category_id);
2959 Fetch csr_rules into l_x;
2960 If csr_rules%notfound then
2961 hr_utility.set_location('No routing rules ', 100);
2962 l_no_of_rules := 0;
2963 else
2964 hr_utility.set_location('Exist routing rules ', 100);
2965 l_no_of_rules := 1;
2966 End if;
2967 Close csr_rules;
2968 --
2969 l_rec_count := l_rec_count + 1;
2970 --
2971 -- No rules were defined for this routing category.
2972 --
2973 If l_no_of_rules = 0 then
2974 --
2975 hr_utility.set_location('rules =0', 100);
2976 l_no_of_errors := l_no_of_errors + 1;
2977 pqh_tct_bus.get_routing_category_name
2978 (p_routing_category_id => l_rct_tab(l_dummy).routing_category_id,
2979 p_routing_category_name => l_list_name);
2980 --
2981 hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
2982 hr_utility.set_message_token('LIST_NAME', l_list_name);
2983 p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
2984 --
2985 p_no_errors := l_no_of_errors;
2986 --
2987 RETURN FALSE;
2988 --
2989 End if;
2990 --
2991 End loop;
2992 --
2993 End if;
2994 --
2995 -- The transaction category must have at least one routing category though
2996 --
2997 If l_rec_count = 0 then
2998 --
2999 l_no_of_errors := l_no_of_errors + 1;
3000 hr_utility.set_message(8302,'PQH_NO_ROUTING_CAT_IN_TCT');
3001 p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3002 --
3003 p_no_errors := l_no_of_errors;
3004 --
3005 RETURN FALSE;
3006 End if;
3007 --
3008 p_no_errors := l_no_of_errors;
3009 RETURN TRUE;
3010 --
3011 /**
3012 sql_stmt := 'Select rct.routing_category_id, count(rng.range_name)'
3013 || ' from pqh_routing_categories rct,pqh_attribute_ranges rng'
3014 || ' Where rct.transaction_category_id = :p_transaction_category_id'
3015 || ' and rct.enable_flag = :p_enable_flag'
3016 || ' and nvl(rct.default_flag,:null1) <> :p_default_flag'
3017 || ' and nvl(rct.delete_flag,:null2) <> :p_delete_flag';
3018 --
3019 If p_routing_type = 'R' then
3020 sql_stmt := sql_stmt || ' and rct.routing_list_id IS NOT NULL';
3021 Elsif p_routing_type = 'P' then
3022 sql_stmt := sql_stmt || ' and rct.position_structure_id IS NOT NULL';
3023 Else
3024 sql_stmt := sql_stmt || ' and rct.routing_list_id IS NULL and rct.position_structure_id IS NULL';
3025 End if;
3026 --
3027 sql_stmt := sql_stmt || ' and rct.routing_category_id = rng.routing_category_id(+)'
3028 || ' and rng.enable_flag(+) = :p_rule_enable'
3029 || ' and nvl(rng.delete_flag(+),:null3) <> :p_rule_delete'
3030 || ' and rng.routing_list_member_id(+) IS NULL'
3031 || ' and rng.position_id(+) IS NULL'
3032 || ' and rng.assignment_id(+) IS NULL'
3033 || ' group by rct.routing_category_id'
3034 || ' order by rct.routing_category_id';
3035 --
3036 -- Select the no of routing categories and no of rules under the routing
3037 -- category.
3038 --
3039 --
3040 Open csr_routing for sql_stmt using p_transaction_category_id,'Y','N','Y',
3041 'N','Y','Y','N','Y';
3042 --
3043 l_rec_count := 0;
3044 --
3045 Loop
3046 --
3047 Fetch csr_routing into l_routing_category_id,l_no_of_rules;
3048 --
3049 If csr_routing%notfound then
3050 exit;
3051 End if;
3052 --
3053 l_rec_count := l_rec_count + 1;
3054 --
3055 -- No rules were defined for this routing category.
3056 --
3057 If l_no_of_rules = 0 then
3058 --
3059 Close csr_routing;
3060 --
3061 l_no_of_errors := l_no_of_errors + 1;
3062 pqh_tct_bus.get_routing_category_name
3063 (p_routing_category_id =>l_routing_category_id,
3064 p_routing_category_name=> l_list_name);
3065 --
3066 hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
3067 hr_utility.set_message_token('LIST_NAME', l_list_name);
3068 p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3069 --
3070 p_no_errors := l_no_of_errors;
3071 --
3072 RETURN FALSE;
3073 --
3074 End if;
3075 --
3076 End loop;
3077 --
3078 Close csr_routing;
3079 --
3080 -- The transaction category must have at least one routing category though
3081 --
3082 If l_rec_count = 0 then
3083 --
3084 l_no_of_errors := l_no_of_errors + 1;
3085 hr_utility.set_message(8302,'PQH_NO_ROUTING_CAT_IN_TCT');
3086 p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3087 --
3088 p_no_errors := l_no_of_errors;
3089 --
3090 RETURN FALSE;
3091 End if;
3092 --
3093 p_no_errors := l_no_of_errors;
3094 RETURN TRUE;
3095 --
3096 **/
3097 End;
3098 --
3099 ------------------------------------------------------------------------------------
3100 --
3101 Function chk_rout_overlap_on_freeze(p_transaction_category_id in number,
3102 p_routing_type in varchar2)
3103 RETURN BOOLEAN is
3104 --
3105 l_routing_category_id pqh_routing_categories.routing_category_id%type;
3106 --
3107 type cur_type IS REF CURSOR;
3108 csr_chk_rule_overlap cur_type;
3109 sql_stmt varchar2(2000);
3110 --
3111 l_error_code number(10);
3112 --
3113 --
3114 l_overlap_range_name pqh_attribute_ranges.range_name%type;
3115 l_error_range_name pqh_attribute_ranges.range_name%type;
3116 l_error_routing_category varchar2(200);
3117 l_proc varchar2(72) := 'chk_overlap_on_freeze_cat';
3118 --
3119 /** Perf changes
3120 Select rct.routing_category_id,default_flag,delete_flag
3121 From pqh_routing_categories rct
3122 Where rct.transaction_category_id = :p_transaction_category_id
3123 and rct.enable_flag = 'Y'
3124 and rct.routing_list_id is not null
3125 **/
3126
3127 Begin
3128 --
3129 hr_utility.set_location('Entering:'||l_proc, 5);
3130 --
3131 -- Select all routing categories under the transaction category that
3132 -- belong to the current routing type
3133 --
3134 sql_stmt := 'Select rct.routing_category_id From pqh_routing_categories rct ';
3135 --
3136 sql_stmt := sql_stmt ||' Where rct.transaction_category_id = :p_transaction_category_id and rct.enable_flag = :p_enable_flag and nvl(rct.default_flag,:null_value) <> :default_flag and nvl(rct.delete_flag,:null2) <> :delete_flag ';
3137 --
3138 If p_routing_type = 'R' then
3139 --
3140 sql_stmt := sql_stmt || ' and rct.routing_list_id is not null';
3141 --
3142 Elsif p_routing_type = 'P' then
3143 --
3144 sql_stmt := sql_stmt || ' and rct.position_structure_id is not null';
3145 --
3146 Else
3147 --
3148 sql_stmt := sql_stmt || ' and rct.routing_list_id is null and rct.position_structure_id is null';
3149 --
3150 End if;
3151
3152 sql_stmt := sql_stmt || ' order by rct.routing_category_id ';
3153 --
3154 --
3155 -- We have the sql_stmt that we can execute.
3156 --
3157 Open csr_chk_rule_overlap for sql_stmt using p_transaction_category_id,'Y','N','Y','N','Y';
3158 --
3159 loop
3160 --
3161 l_error_code := 0;
3162 --
3163 Fetch csr_chk_rule_overlap into l_routing_category_id ;
3164 --
3165 If csr_chk_rule_overlap%notfound then
3166 Close csr_chk_rule_overlap;
3167 exit;
3168 End if;
3169 --
3170 l_error_code := pqh_attribute_ranges_pkg.chk_enable_routing_category
3171 (p_routing_category_id => l_routing_category_id,
3172 p_transaction_category_id => p_transaction_category_id,
3173 p_overlap_range_name => l_overlap_range_name,
3174 p_error_routing_category => l_error_routing_category,
3175 p_error_range_name => l_error_range_name);
3176 --
3177 hr_utility.set_location('Leaving:'||l_proc, 10);
3178 --
3179 If l_error_code = 1 then
3180 --
3181 Close csr_chk_rule_overlap;
3182 RETURN FALSE;
3183 --
3184 End if;
3185 --
3186 End loop;
3187 --
3188 RETURN TRUE;
3189 --
3190 End;
3191 --
3192 ----------------------------------------------------------------------------------------------
3193 --
3194 FUNCTION chk_mem_overlap_on_freeze(
3195 p_transaction_category_id in number,
3196 p_routing_type in varchar2,
3197 p_routing_category_id in number default NULL,
3198 p_error_routing_cat out nocopy varchar2,
3199 p_member_name out nocopy varchar2,
3200 p_overlap_range_1 out nocopy varchar2,
3201 p_overlap_range_2 out nocopy varchar2)
3202 --
3203 RETURN BOOLEAN is
3204 --
3205 l_error_routing_category varchar2(200);
3206 l_member_name varchar2(300);
3207 l_overlap_range_name pqh_attribute_ranges.range_name%type;
3208 l_error_range_name pqh_attribute_ranges.range_name%type;
3209 --
3210 l_prev_range_name pqh_attribute_ranges.range_name%type;
3211 l_prev_routing_category_id pqh_routing_categories.routing_category_id%type;
3212 l_prev_member_id number(30);
3213 --
3214 cnt number(10);
3215 l_attribute_range_id_list varchar2(2000);
3216 l_no_mem_identifiers number(10);
3217 --
3218 l_routing_category_id pqh_routing_categories.routing_category_id%type;
3219 l_range_name pqh_attribute_ranges.range_name%type;
3220 l_member_id number(30);
3221 l_attribute_range_id pqh_attribute_ranges.attribute_range_id %type;
3222 l_attribute_id pqh_attribute_ranges.attribute_id%type;
3223 l_column_type pqh_attributes.column_type%type;
3224 l_from_char pqh_attribute_ranges.from_char%type;
3225 l_to_char pqh_attribute_ranges.to_char%type;
3226 l_from_date pqh_attribute_ranges.from_date%type;
3227 l_to_date pqh_attribute_ranges.to_date%type;
3228 l_from_number pqh_attribute_ranges.from_number%type;
3229 l_to_number pqh_attribute_ranges.to_number%type;
3230 --
3231 l_error_code number(10) := NULL;
3232 --
3233 type cur_type IS REF CURSOR;
3234 csr_mem_overlap cur_type;
3235 sql_stmt varchar2(2000);
3236 --
3237 all_routing_rules pqh_attribute_ranges_pkg.rule_attr_tab;
3238 all_attributes_tab pqh_attribute_ranges_pkg.rule_attr_tab;
3239 --
3240 Cursor csr_mem_ident_cnt is
3241 Select count(*)
3242 from pqh_txn_category_attributes
3243 Where transaction_category_id = p_transaction_category_id
3244 AND member_identifying_flag = 'Y';
3245 --
3246 l_proc varchar2(72) := 'chk_mem_overlap_on_freeze';
3247 --
3248 Begin
3249 --
3250 hr_utility.set_location('Entering:'||l_proc, 5);
3251 --
3252 Open csr_mem_ident_cnt;
3253 Fetch csr_mem_ident_cnt into l_no_mem_identifiers;
3254 Close csr_mem_ident_cnt;
3255 --
3256 sql_stmt := 'Select rct.routing_category_id, rng.range_name ,';
3257 --
3258 If p_routing_type = 'R' then
3259 --
3260 sql_stmt := sql_stmt || ' rng.routing_list_member_id,';
3261 --
3262 Elsif p_routing_type = 'P' then
3263 --
3264 sql_stmt := sql_stmt || ' rng.position_id,';
3265 --
3266 Else
3267 --
3268 sql_stmt := sql_stmt || ' rng.assignment_id,';
3269 --
3270 End if;
3271 --
3272 sql_stmt := sql_stmt ||' rng.attribute_range_id, rng.attribute_id, att.column_type, rng.from_char, rng.to_char, rng.from_number, rng.to_number, rng.from_date, rng.to_date ';
3273 --
3274 sql_stmt := sql_stmt ||' From pqh_routing_categories rct,pqh_attribute_ranges rng,pqh_attributes att ';
3275 --
3276 sql_stmt := sql_stmt ||' Where rct.transaction_category_id = :p_transaction_category_id and rct.enable_flag = :enable_flag and nvl(rct.default_flag,:null_value) <> :default_flag and nvl(rct.delete_flag,:null2) <> :delete_flag ';
3277 --
3278 -- If a routing category is passed, process only this routing category.
3279 --
3280 If p_routing_category_id IS NOT NULL then
3281 sql_stmt := sql_stmt ||' and rct.routing_category_id = :routing_category_id';
3282 End if;
3283 --
3284 sql_stmt := sql_stmt ||' and rng.routing_category_id = rct.routing_category_id and rng.attribute_id = att.attribute_id';
3285 --
3286 If p_routing_type = 'R' then
3287 --
3288 sql_stmt := sql_stmt || ' and rct.routing_list_id is not null';
3289 --
3290 Elsif p_routing_type = 'P' then
3291 --
3292 sql_stmt := sql_stmt || ' and rct.position_structure_id is not null';
3293 --
3294 Else
3295 --
3296 sql_stmt := sql_stmt || ' and rct.routing_list_id is null and rct.position_structure_id is null';
3297 --
3298 End if;
3299 --
3300 sql_stmt := sql_stmt || ' and rng.enable_flag = :rule_enable and nvl(rng.delete_flag,:null3) <> :delete_flag ';
3301 --
3302 If p_routing_type = 'R' then
3303 --
3304 sql_stmt := sql_stmt || ' and rng.routing_list_member_id is not null';
3305 --
3306 Elsif p_routing_type = 'P' then
3307 --
3308 sql_stmt := sql_stmt || ' and rng.position_id is not null';
3309 --
3310 Else
3311 --
3312 sql_stmt := sql_stmt || ' and rng.assignment_id is not null ';
3313 --
3314 End if;
3315 --
3316 sql_stmt := sql_stmt || ' order by rct.routing_category_id,rng.range_name,rng.attribute_id';
3317 --
3318 --
3319 -- We have the sql_stmt that we can execute.
3320 --
3321 If p_routing_category_id IS NOT NULL then
3322 Open csr_mem_overlap for sql_stmt using p_transaction_category_id,'Y','N','Y','N','Y',p_routing_category_id,'Y','N','Y';
3323 Else
3324 Open csr_mem_overlap for sql_stmt using p_transaction_category_id,'Y','N','Y','N','Y','Y','N','Y';
3325 End if;
3326 --
3327 cnt := 0;
3328 l_prev_range_name := NULL;
3329 l_prev_routing_category_id := NULL;
3330 l_prev_member_id := NULL;
3331 --
3332
3333 loop
3334 --
3335 Fetch csr_mem_overlap into l_routing_category_id, l_range_name,
3336 l_member_id,
3337 l_attribute_range_id,l_attribute_id,
3338 l_column_type,
3339 l_from_char,l_to_char,
3340 l_from_number,l_to_number,
3341 l_from_date,l_to_date;
3342 If csr_mem_overlap%notfound then
3343 hr_utility.set_location('Closing cursor',100);
3344 Close csr_mem_overlap;
3345 exit;
3346 End if;
3347 --
3348 --
3349 -- Check if there is a change in rule name
3350 --
3351 If l_routing_category_id <> l_prev_routing_category_id OR
3352 nvl(l_range_name,'xXx') <> nvl(l_prev_range_name,hr_api.g_varchar2) then
3353 --
3354 hr_utility.set_location('New rule:'||l_range_name ||l_proc, 6);
3355 --
3356 If cnt > 0 then
3357 hr_utility.set_location('Rules exist '||l_proc, 6);
3358 --
3359 -- call chk_routing_range_overlap procedure to check if this rule
3360 -- overlaps with any other routing rules under that
3361 -- transaction category.
3362 --
3363 hr_utility.set_location('Calling chk_member_range_overlap:'||l_proc, 6);
3364 l_error_code := pqh_attribute_ranges_pkg.chk_member_range_overlap
3365 (tab1 => all_routing_rules ,
3366 tab2 => all_attributes_tab,
3367 p_transaction_category_id => p_transaction_category_id,
3368 p_routing_category_id => l_prev_routing_category_id,
3369 p_range_name => l_prev_range_name,
3370 p_routing_type => p_routing_type,
3371 p_member_id => l_prev_member_id,
3372 p_attribute_range_id_list => l_attribute_range_id_list,
3373 p_no_attributes => l_no_mem_identifiers,
3374 p_error_range => l_error_range_name);
3375 --
3376 If l_error_code = 1 then
3377 --
3378 Close csr_mem_overlap;
3379 --
3380 p_overlap_range_1 := l_prev_range_name;
3381 p_overlap_range_2 := l_error_range_name;
3382 --
3383 pqh_tct_bus.get_routing_category_name(
3384 p_routing_category_id => l_prev_routing_category_id,
3385 p_routing_category_name => l_error_routing_category);
3386 --
3387 pqh_attribute_ranges_pkg.get_member_name
3388 (p_member_id => l_prev_member_id,
3389 p_routing_type => p_routing_type,
3390 p_member_name => l_member_name);
3391 --
3392 p_error_routing_cat := l_error_routing_category;
3393 p_member_name := l_member_name;
3394 --
3395 RETURN FALSE;
3396 --
3397 End if;
3398 --
3399 End if;
3400 -- Reset counters
3401 hr_utility.set_location('Reset counter'||l_proc, 6);
3402 --
3403 cnt := 1;
3404 l_prev_routing_category_id := l_routing_category_id;
3405 l_prev_range_name := l_range_name;
3406 l_prev_member_id := l_member_id;
3407 --
3408 l_error_code := NULL;
3409 l_error_routing_category := NULL;
3410 l_error_range_name := NULL;
3411 l_attribute_range_id_list := NULL;
3412 --
3413 Else
3414 hr_utility.set_location('Increment counter'||l_proc, 6);
3415 -- If we are processing same rule , increment counter
3416 cnt := cnt + 1;
3417 l_attribute_range_id_list := l_attribute_range_id_list || ',';
3418
3419 End if;
3420 --
3421 all_routing_rules(cnt).attribute_id := l_attribute_id;
3422 all_attributes_tab(cnt).attribute_id := l_attribute_id;
3423 all_routing_rules(cnt).datatype := l_column_type;
3424 all_attributes_tab(cnt).datatype := l_column_type;
3425 all_routing_rules(cnt).from_char := l_from_char;
3426 all_routing_rules(cnt).to_char := l_to_char;
3427 all_routing_rules(cnt).from_number := l_from_number;
3428 all_routing_rules(cnt).to_number := l_to_number;
3429 all_routing_rules(cnt).from_date := l_from_date;
3430 all_routing_rules(cnt).to_date := l_to_date;
3431 --
3432 l_attribute_range_id_list := l_attribute_range_id_list || to_char(l_attribute_range_id);
3433 --
3434 End loop;
3435 --
3436 If cnt > 0 then
3437 --
3438 hr_utility.set_location('Rules exist '||l_proc, 6);
3439 --
3440 -- call chk_routing_range_overlap procedure to check if this rule
3441 -- overlaps with any other routing rules under that
3442 -- transaction category.
3443 --
3444 hr_utility.set_location('Calling chk_routing_range_overlap:'||l_proc, 6);
3445 --
3446 l_error_code := pqh_attribute_ranges_pkg.chk_member_range_overlap
3447 (tab1 => all_routing_rules ,
3448 tab2 => all_attributes_tab,
3449 p_transaction_category_id => p_transaction_category_id,
3450 p_routing_category_id => l_prev_routing_category_id,
3451 p_range_name => l_prev_range_name,
3452 p_routing_type => p_routing_type,
3453 p_member_id => l_prev_member_id,
3454 p_attribute_range_id_list => l_attribute_range_id_list,
3455 p_no_attributes => l_no_mem_identifiers,
3456 p_error_range => l_error_range_name);
3457 --
3458 If l_error_code = 1 then
3459 --
3460 --
3461 -- Get the name of the routing category and member for
3462 -- whom there is a overlap.
3463 --
3464 --
3465 p_overlap_range_1 := l_prev_range_name;
3466 p_overlap_range_2 := l_error_range_name;
3467 --
3468 pqh_tct_bus.get_routing_category_name(
3469 p_routing_category_id => l_prev_routing_category_id,
3470 p_routing_category_name => l_error_routing_category);
3471 --
3472 pqh_attribute_ranges_pkg.get_member_name
3473 (p_member_id => l_prev_member_id,
3474 p_routing_type => p_routing_type,
3475 p_member_name => l_member_name);
3476 --
3477 p_error_routing_cat := l_error_routing_category;
3478 p_member_name := l_member_name;
3479 --
3480 RETURN FALSE;
3481 --
3482 End if;
3483 --
3484 End if;
3485 --
3486 hr_utility.set_location('Leaving'||l_proc, 10);
3487 --
3488 RETURN TRUE;
3489 --
3490 End;
3491 --
3492 --------------------------------------------------------------------------------------------------
3493 --
3494 FUNCTION check_errors_in_adv_setup(p_transaction_category_id in number,
3495 p_error_messages out nocopy warnings_tab)
3496 RETURN boolean IS
3497 --
3498 l_setup_status boolean := TRUE;
3499 l_status boolean := TRUE;
3500 --
3501 l_error_index number(10) := 0;
3502 l_error_messages warnings_tab;
3503 --
3504 l_routing_type pqh_transaction_categories.member_cd%type;
3505 --
3506 l_error_routing_category varchar2(200);
3507 l_member_name varchar2(300);
3508 l_overlap_range_name pqh_attribute_ranges.range_name%type;
3509 l_error_range_name pqh_attribute_ranges.range_name%type;
3510 --
3511 --
3512 Cursor csr_routing_type is
3513 Select member_Cd
3514 From pqh_transaction_categories
3515 Where transaction_category_id = p_transaction_category_id;
3516 --
3517 Begin
3518 --
3519 -- Obtain the routing type of the transaction category
3520 --
3521 open csr_routing_type;
3522 Fetch csr_routing_type into l_routing_type;
3523 Close csr_routing_type;
3524 --
3525 -- The Advanced setup performs 3 validations. If these 3 validations are successful , then
3526 -- The Advanced setup is considered to be completed sucessfully.
3527 -- The Validations are :
3528 -- 1. There must be at least 1 enabled routing category , with enabled routing
3529 -- rules.
3530 --
3531 l_status := chk_valid_rout_hier_exists
3532 (p_transaction_category_id => p_transaction_category_id,
3533 p_routing_type => l_routing_type,
3534 p_error_messages => l_error_messages,
3535 p_no_errors => l_error_index);
3536 If NOT l_status then
3537 --
3538 l_setup_status := FALSE;
3539 --
3540 End if;
3541 --
3542 -- 2. Routing rules must not overlap with other routing rules in the transaction category.
3543 --
3544 p_error_messages := l_error_messages;
3545 --
3546 l_status := chk_rout_overlap_on_freeze
3547 (p_transaction_category_id => p_transaction_category_id,
3548 p_routing_type => l_routing_type);
3549 --
3550 If NOT l_status then
3551 --
3552 l_error_index := l_error_index + 1;
3553 hr_utility.set_message(8302,'PQH_TCW_ADV_ERROR1');
3554 p_error_messages(l_error_index).message_text := hr_utility.get_message;
3555 --
3556 l_setup_status := FALSE;
3557 --
3558 End if;
3559 --
3560 -- 3. Authorization rules should not overlap for the same approver, within a routing category.
3561 --
3562 l_status := chk_mem_overlap_on_freeze
3563 (p_transaction_category_id => p_transaction_category_id,
3564 p_routing_type => l_routing_type,
3565 p_error_routing_cat => l_error_routing_category,
3566 p_member_name => l_member_name,
3567 p_overlap_range_1 => l_overlap_range_name,
3568 p_overlap_range_2 => l_error_range_name);
3569 --
3570 If NOT l_status then
3571 --
3572 l_error_index := l_error_index + 1;
3573 hr_utility.set_message(8302,'PQH_TCW_ADV_ERROR2');
3574 p_error_messages(l_error_index).message_text := hr_utility.get_message;
3575 --
3576 l_setup_status := FALSE;
3577 --
3578 End if;
3579 --
3580 RETURN l_setup_status;
3581 --
3582 End;
3583 --
3584 -----------------------------------------------------------------------------------------------
3585 --
3586 FUNCTION check_if_adv_setup_started(p_transaction_category_id in number)
3587 RETURN BOOLEAN is
3588 --
3589 Cursor csr_member_cd(p_transaction_category_id in number) is
3590 Select member_cd
3591 from pqh_transaction_categories
3592 Where transaction_category_id = p_transaction_category_id;
3593 --
3594 Cursor csr_hier (p_transaction_category_id in number,
3595 p_member_cd in varchar2) is
3596 Select routing_category_id
3597 from pqh_routing_categories_v a
3598 where a.transaction_category_id = p_transaction_category_id
3599 and a.member_cd = p_member_cd
3600 and nvl(a.enable_flag,'Y') = 'Y'
3601 and nvl(a.delete_flag,'N') <> 'Y'
3602 and nvl(a.default_flag,'N') <> 'Y';
3603 --
3604 l_routing_category_id pqh_routing_categories.routing_category_id%type;
3605 l_member_cd pqh_transaction_categories.member_cd%type;
3606 --
3607 l_error_status boolean := TRUE;
3608 l_proc varchar2(72) := 'check_if_adv_setup_started';
3609 --
3610 --
3611 Begin
3612 --
3613 hr_utility.set_location('Entering'||l_proc, 5);
3614 --
3615 -- Obtain the routing style of the transcation category.
3616 --
3617 Open csr_member_cd(p_transaction_category_id => p_transaction_category_id);
3618 Fetch csr_member_cd into l_member_cd;
3619 Close csr_member_cd;
3620 --
3621 Open csr_hier(p_transaction_category_id => p_transaction_category_id,
3622 p_member_cd => l_member_cd);
3623 Fetch csr_hier into l_routing_category_id;
3624 --
3625 -- Check if any default hierarchy is marked
3626 --
3627 If csr_hier%notfound then
3628 --
3629 l_error_status := FALSE;
3630 --
3631 Else
3632 --
3633 l_error_status := TRUE;
3634 --
3635 End if;
3636 --
3637 Close csr_hier;
3638 --
3639 hr_utility.set_location('Leaving'||l_proc, 10);
3640 --
3641 RETURN l_error_status;
3642 --
3643 END;
3644 --
3645 ---------------------------------------------------------------------------------------------
3646 Procedure delete_hierarchy_and_rules(p_transaction_category_id in number,
3647 p_routing_style in varchar2) is
3648 --
3649 --
3650 type cur_type IS REF CURSOR;
3651 csr_del_all cur_type;
3652 sql_stmt1 varchar2(2000);
3653 --
3654 l_proc varchar2(72) := 'delete_hierarchy_and_rules';
3655 --
3656 BEGIN
3657 --
3658 --
3659 hr_utility.set_location('Entering'||l_proc, 5);
3660 --
3661 -- The following cursor deletes the rules under the routing hierarchies
3662 -- that were selected for deletion for the passed transaction category
3663 -- and its current routing style.
3664 --
3665 sql_stmt1 := 'Delete From pqh_attribute_ranges rng Where';
3666 --
3667 sql_stmt1 := sql_stmt1 || ' rng.routing_category_id in ('
3668 || ' Select routing_category_id '
3669 || ' from pqh_routing_categories rct '
3670 ||' Where rct.transaction_category_id = :p_transaction_category_id and nvl(rct.default_flag,:null_value) <> :default_flag and nvl(rct.delete_flag,:null2) = :delete_flag ';
3671 --
3672 If p_routing_style = 'R' then
3673 --
3674 sql_stmt1 := sql_stmt1 || ' and rct.routing_list_id is not null)';
3675 --
3676 Elsif p_routing_style = 'P' then
3677 --
3678 sql_stmt1 := sql_stmt1 || ' and rct.position_structure_id is not null)';
3679 --
3680 Else
3681 --
3682 sql_stmt1 := sql_stmt1 || ' and rct.routing_list_id is null and rct.position_structure_id is null)';
3683 --
3684 End if;
3685 --
3686 Execute immediate sql_stmt1 using p_transaction_category_id,'N','Y','N','Y';
3687 --
3688 -- The following cursor selects all routing hierarchies that were selected for
3689 -- deletion for the passed transaction category and its current routing style.
3690 --
3691 sql_stmt1 := 'Delete From pqh_routing_categories rct';
3692 --
3693 sql_stmt1 := sql_stmt1 ||' Where rct.transaction_category_id = :p_transaction_category_id and nvl(rct.default_flag,:null_value) <> :default_flag and nvl(rct.delete_flag,:null2) = :delete_flag ';
3694 --
3695 If p_routing_style = 'R' then
3696 --
3697 sql_stmt1 := sql_stmt1 || ' and rct.routing_list_id is not null';
3698 --
3699 Elsif p_routing_style = 'P' then
3700 --
3701 sql_stmt1 := sql_stmt1 || ' and rct.position_structure_id is not null';
3702 --
3703 Else
3704 --
3705 sql_stmt1 := sql_stmt1 || ' and rct.routing_list_id is null and rct.position_structure_id is null';
3706 --
3707 End if;
3708 --
3709 Execute immediate sql_stmt1 using p_transaction_category_id,'N','Y','N','Y';
3710 --
3711 -- Finally we need to delete just the rules that were selected for deletion
3712 -- under this transaction category and routing style.
3713 --
3714 sql_stmt1 := 'Delete From pqh_attribute_ranges rng '
3715 ||' Where nvl(rng.delete_flag,:null1) = :delete_flag ';
3716 --
3717 sql_stmt1 := sql_stmt1 ||' and rng.routing_category_id in ('
3718 ||' Select routing_category_id '
3719 ||' from pqh_routing_categories rct '
3720 ||' Where rct.transaction_category_id = :p_transaction_category_id ';
3721 --
3722 If p_routing_style = 'R' then
3723 --
3724 sql_stmt1 := sql_stmt1 || ' and rct.routing_list_id is not null)';
3725 --
3726 Elsif p_routing_style = 'P' then
3727 --
3728 sql_stmt1 := sql_stmt1 || ' and rct.position_structure_id is not null)';
3729 --
3730 Else
3731 --
3732 sql_stmt1 := sql_stmt1 || ' and rct.routing_list_id is null and rct.position_structure_id is null)';
3733 --
3734 End if;
3735 --
3736 Execute immediate sql_stmt1 using 'N','Y',p_transaction_category_id;
3737 --
3738 hr_utility.set_location('Leaving'||l_proc, 10);
3739 --
3740 END delete_hierarchy_and_rules;
3741 ---------------------------------------------------------------------------------------------
3742 FUNCTION return_approver_status(p_routing_category_id in number,
3743 p_approver_id in number,
3744 p_routing_style in varchar2)
3745 RETURN varchar2 is
3746 --
3747 type cur_type IS REF CURSOR;
3748 csr_appr cur_type;
3749 sql_stmt1 varchar2(2000);
3750 --
3751 l_approver_flag pqh_attribute_ranges.approver_flag%type;
3752 --
3753 l_proc varchar2(72) := 'return_approver_status';
3754 --
3755 BEGIN
3756 --
3757 --
3758 hr_utility.set_location('Entering'||l_proc, 5);
3759 --
3760 -- The following cursor selects the approver flag on all the rules for the
3761 -- approver under the passed routing category.
3762 --
3763 sql_stmt1 := 'Select approver_flag From pqh_attribute_ranges rng '
3764 || ' Where rng.routing_category_id = :routing_category_id';
3765 --
3766 If p_routing_style = 'R' then
3767 --
3768 sql_stmt1 := sql_stmt1 || ' and rng.routing_list_member_id IS NOT NULL and rng.routing_list_member_id = :approver_id';
3769 --
3770 Elsif p_routing_style = 'P' then
3771 --
3772 sql_stmt1 := sql_stmt1 || ' and rng.position_id IS NOT NULL and rng.position_id = :approver_id';
3773 --
3774 Else
3775 --
3776 sql_stmt1 := sql_stmt1 || ' and rng.assignment_id IS NOT NULL and rng.assignment_id = :approver_id';
3777 --
3778 End if;
3779 --
3780 --
3781 Open csr_appr for sql_stmt1 using p_routing_category_id,p_approver_id;
3782 --
3783 Loop
3784 --
3785 Fetch csr_appr into l_approver_flag;
3786 If csr_appr%notfound then
3787 exit;
3788 End if;
3789
3790 If nvl(l_approver_flag,'N') = 'Y' then
3791 Close csr_appr;
3792 RETURN 'Y';
3793 End if;
3794 --
3795 End loop;
3796
3797 --
3798 hr_utility.set_location('Leaving'||l_proc, 10);
3799 --
3800 RETURN 'N';
3801 --
3802 END return_approver_status;
3803 ----------------------------------------------------------------------------------------
3804 FUNCTION return_person_name(p_assignment_id in number)
3805 RETURN varchar2 is
3806 --
3807 --
3808 Cursor csr_name is
3809 Select full_name
3810 from per_all_assignments_f ASG, per_all_people_f PPL, fnd_sessions ses
3811 Where asg.assignment_id = p_assignment_id
3812 AND asg.person_id = PPL.person_id
3813 AND SES.SESSION_ID = USERENV('sessionid')
3814 AND SES.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
3815 AND SES.EFFECTIVE_DATE BETWEEN PPL.EFFECTIVE_START_DATE AND PPL.EFFECTIVE_END_DATE;
3816 --
3817 l_name per_all_people_f.full_name%type;
3818 l_proc varchar2(72) := 'return_person_name';
3819 --
3820 BEGIN
3821 --
3822 hr_utility.set_location('Entering'||l_proc, 5);
3823 --
3824 Open csr_name;
3825 Fetch csr_name into l_name;
3826 Close csr_name;
3827 --
3828 hr_utility.set_location('Leaving'||l_proc, 10);
3829 --
3830 RETURN l_name;
3831 --
3832 End return_person_name;
3833
3834 END pqh_tct_wizard_pkg;