[Home] [Help]
PACKAGE BODY: APPS.PQH_TCT_WIZARD_PKG
Source
1 Package Body pqh_tct_wizard_pkg as
2 /* $Header: pqtctwiz.pkb 120.3 2011/04/28 09:33:15 sidsaxen ship $ */
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 --
2499 -- Added the following code as a part of Zero Downtime Patching Project.
2500 -- Code Starts Here.
2501 --
2502
2503 PER_RIC_PKG.chk_integrity (
2504 p_entity_name=>'PQH_ATTRIBUTE_RANGES',
2505 p_ref_entity_info=>
2506 PER_RIC_PKG.ref_entity_tbl(
2507 PER_RIC_PKG.ref_info_rec('PQH_ATTRIBUTES',PER_RIC_PKG.column_info_tbl(
2508 PER_RIC_PKG.col_info_rec('ATTRIBUTE_ID', NULL,rng_rec.attribute_id, NULL))),
2509 PER_RIC_PKG.ref_info_rec('PQH_ROUTING_LIST_MEMBERS', PER_RIC_PKG.column_info_tbl(
2510 PER_RIC_PKG.col_info_rec('ROUTING_LIST_MEMBER_ID',NULL,rng_rec.routing_list_member_id,NULL))),
2511 PER_RIC_PKG.ref_info_rec('PQH_ROUTING_CATEGORIES',PER_RIC_PKG.column_info_tbl(
2512 PER_RIC_PKG.col_info_rec('ROUTING_CATEGORY_ID',NULL,rct_id, NULL)))),
2513 p_ref_type=>'INS');
2514
2515 --
2516 -- Code Ends Here
2517 --
2518
2519 insert into pqh_attribute_ranges(
2520 attribute_range_id,
2521 approver_flag,
2522 enable_flag,
2523 assignment_id,
2524 attribute_id,
2525 from_char,
2526 from_date,
2527 from_number,
2528 position_id,
2529 range_name,
2530 routing_category_id,
2531 routing_list_member_id,
2532 to_char,
2533 to_date,
2534 to_number,
2535 object_version_number)
2536
2537 Values(
2538 rng_id,
2539 rng_rec.approver_flag,
2540 rng_rec.enable_flag,
2541 rng_rec.assignment_id,
2542 rng_rec.attribute_id,
2543 rng_rec.from_char,
2544 rng_rec.from_date,
2545 rng_rec.from_number,
2546 rng_rec.position_id,
2547 rng_rec.range_name,
2548 rct_id,
2549 rng_rec.routing_list_member_id,
2550 rng_rec.to_char,
2551 rng_rec.to_date,
2552 rng_rec.to_number,
2553 1);
2554
2555
2556 end loop;
2557
2558 end loop;
2559
2560 close csr_routing_category;
2561 --
2562 p_transaction_category_id := tct_id;
2563 --
2564 --
2565 hr_utility.set_location('Leaving:'||l_proc, 10);
2566 --
2567 END;
2568 -----------------------------------------------------------------------------
2569 PROCEDURE freeze_category (p_transaction_category_id in number,
2570 p_setup_type_cd in varchar2,
2571 p_freeze_status_cd in varchar2) is
2572 --
2573 l_proc varchar2(72) := 'freeze_category';
2574 --
2575 BEGIN
2576 --
2577 hr_utility.set_location('Entering:'||l_proc, 5);
2578 --
2579 --
2580 Update pqh_transaction_categories
2581 set freeze_status_cd = p_freeze_status_cd
2582 ,setup_type_cd = p_setup_type_cd
2583 where transaction_category_id = p_transaction_category_id;
2584 --
2585 --
2586 --
2587 hr_utility.set_location('Leaving:'||l_proc, 10);
2588 --
2589 END;
2590 -----------------------------------------------------------------------------
2591
2592 FUNCTION chk_range_name_unique (p_routing_category_id in number,
2593 p_range_name in varchar2,
2594 p_attribute_id_list in varchar2,
2595 p_primary_flag in varchar2)
2596 RETURN BOOLEAN is
2597 type cur_type IS REF CURSOR;
2598 range_name_cur cur_type;
2599 sql_stmt varchar2(1000);
2600 exist_range_name pqh_attribute_ranges.range_name%type;
2601 --
2602 l_proc varchar2(72) := 'chk_range_name_unique';
2603 --
2604 Begin
2605 --
2606 hr_utility.set_location('Entering:'||l_proc, 5);
2607 --
2608
2609 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 || ') ';
2610
2611 if p_primary_flag = 'Y' then
2612 sql_stmt := sql_stmt ||' AND routing_list_member_id is NULL AND position_id is NULL AND assignment_id is NULL';
2613 else
2614 sql_stmt := sql_stmt ||' AND (routing_list_member_id is NOT NULL OR position_id is NOT NULL OR assignment_id is NOT NULL)';
2615 end if;
2616
2617 open range_name_cur for sql_stmt using p_routing_category_id;
2618
2619 Loop
2620 Fetch range_name_cur into exist_range_name;
2621 exit when range_name_cur%notfound;
2622 if exist_range_name = p_range_name then
2623 Return FALSE;
2624 end if;
2625 End loop;
2626 Return TRUE;
2627 --
2628 hr_utility.set_location('Leaving:'||l_proc, 10);
2629 --
2630 End;
2631 --------------------------------------------------------------------------------------------
2632
2633 PROCEDURE load_row (
2634 p_canvas_name in varchar2,
2635 p_form_name in varchar2,
2636 p_current_item in varchar2,
2637 p_previous_item in varchar2,
2638 p_next_item in varchar2,
2639 p_enable_finish_flag in varchar2,
2640 p_post_flag in varchar2,
2641 p_seq_no in number,
2642 p_finish_item in varchar2,
2643 p_refresh_msg_flag in varchar2,
2644 p_image_name in varchar2,
2645 p_warning_item in varchar2,
2646 p_image_item in varchar2,
2647 p_line_size in number,
2648 p_owner in varchar2,
2649 p_last_update_date in varchar2 ) IS
2650
2651 --
2652 l_language varchar2(30) ;
2653 --
2654 l_created_by pqh_wizard_canvases.created_by%TYPE;
2655 l_last_updated_by pqh_wizard_canvases.last_updated_by%TYPE;
2656 l_creation_date pqh_wizard_canvases.creation_date%TYPE;
2657 l_last_update_date pqh_wizard_canvases.last_update_date%TYPE;
2658 l_last_update_login pqh_wizard_canvases.last_update_login%TYPE;
2659 --
2660 --
2661
2662 l_rowid ROWID;
2663 l_wizard_canvas_id NUMBER;
2664
2665 cursor c1 is select userenv('LANG') from dual ;
2666
2667 cursor csr_wiz_canvas is
2668 select rowid
2669 from pqh_wizard_canvases
2670 where canvas_name = p_canvas_name
2671 and form_name = p_form_name;
2672
2673 --and current_item = p_current_item;
2674 l_data_migrator_mode varchar2(1);
2675 --
2676
2677 begin
2678
2679 --
2680 l_data_migrator_mode := hr_general.g_data_migrator_mode ;
2681 hr_general.g_data_migrator_mode := 'Y';
2682 open c1;
2683 fetch c1 into l_language ;
2684 close c1;
2685 --
2686
2687 --
2688 -- populate WHO columns
2689 --
2690 /**
2691 if p_owner = 'SEED' then
2692 l_created_by := 1;
2693 l_last_updated_by := 1;
2694 else
2695 l_created_by := 0;
2696 l_last_updated_by := 0;
2697 end if;
2698 **/
2699 l_created_by := fnd_load_util.owner_id(p_owner);
2700 l_last_updated_by := fnd_load_util.owner_id(p_owner);
2701 --
2702 /**
2703 l_creation_date := sysdate;
2704 l_last_update_date := sysdate;
2705 **/
2706 l_creation_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
2707 l_last_update_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
2708 l_last_update_login := 0;
2709
2710 OPEN csr_wiz_canvas;
2711 FETCH csr_wiz_canvas INTO l_rowid;
2712 CLOSE csr_wiz_canvas;
2713
2714 if ( l_rowid is null ) THEN
2715 select pqh_wizard_canvases_s.NEXTVAL into l_wizard_canvas_id from dual;
2716
2717 insert into pqh_wizard_canvases (
2718 WIZARD_CANVAS_ID ,
2719 CANVAS_NAME ,
2720 CURRENT_ITEM ,
2721 PREVIOUS_ITEM ,
2722 NEXT_ITEM ,
2723 ENABLE_FINISH_FLAG ,
2724 POST_FLAG ,
2725 SEQ_NO ,
2726 FINISH_ITEM ,
2727 REFRESH_MSG_FLAG ,
2728 FORM_NAME ,
2729 IMAGE_NAME ,
2730 WARNING_ITEM ,
2731 IMAGE_ITEM ,
2732 LINE_SIZE ,
2733 LAST_UPDATE_DATE,
2734 LAST_UPDATED_BY,
2735 LAST_UPDATE_LOGIN,
2736 CREATED_BY,
2737 CREATION_DATE )
2738 values (
2739 l_wizard_canvas_id ,
2740 P_CANVAS_NAME ,
2741 P_CURRENT_ITEM ,
2742 P_PREVIOUS_ITEM ,
2743 P_NEXT_ITEM ,
2744 P_ENABLE_FINISH_FLAG ,
2745 P_POST_FLAG ,
2746 P_SEQ_NO ,
2747 P_FINISH_ITEM ,
2748 P_REFRESH_MSG_FLAG ,
2749 P_FORM_NAME ,
2750 P_IMAGE_NAME ,
2751 P_WARNING_ITEM ,
2752 P_IMAGE_ITEM ,
2753 P_LINE_SIZE ,
2754 l_last_update_date,
2755 l_last_updated_by,
2756 l_last_update_login,
2757 l_created_by,
2758 l_creation_date );
2759 else
2760 update pqh_wizard_canvases
2761 set
2762 CANVAS_NAME = P_CANVAS_NAME ,
2763 CURRENT_ITEM = P_CURRENT_ITEM ,
2764 PREVIOUS_ITEM = P_PREVIOUS_ITEM ,
2765 NEXT_ITEM = P_NEXT_ITEM ,
2766 ENABLE_FINISH_FLAG = P_ENABLE_FINISH_FLAG ,
2767 POST_FLAG = P_POST_FLAG ,
2768 SEQ_NO = P_SEQ_NO ,
2769 FINISH_ITEM = P_FINISH_ITEM ,
2770 REFRESH_MSG_FLAG = P_REFRESH_MSG_FLAG ,
2771 FORM_NAME = P_FORM_NAME ,
2772 IMAGE_NAME = P_IMAGE_NAME ,
2773 WARNING_ITEM = P_WARNING_ITEM ,
2774 IMAGE_ITEM = P_IMAGE_ITEM ,
2775 LINE_SIZE = P_LINE_SIZE ,
2776 LAST_UPDATE_DATE = l_LAST_UPDATE_DATE ,
2777 LAST_UPDATED_BY = l_LAST_UPDATED_BY ,
2778 LAST_UPDATE_LOGIN = l_LAST_UPDATE_LOGIN ,
2779 CREATED_BY = l_CREATED_BY ,
2780 CREATION_DATE = l_CREATION_DATE
2781 where ROWID = l_rowid ;
2782
2783 end if;
2784 hr_general.g_data_migrator_mode := l_data_migrator_mode;
2785 end load_row;
2786 --------------------------------------------------------------------------------------------
2787 --
2788 -- This function checks if there are any errors in standard setup and returns false in case
2789 -- there are errors.
2790 --
2791 Function check_errors_in_std_setup(p_transaction_category_id in number,
2792 p_error_messages out nocopy warnings_tab)
2793 RETURN boolean IS
2794 --
2795 Cursor csr_member_cd(p_transaction_category_id in number) is
2796 Select member_cd
2797 from pqh_transaction_categories
2798 Where transaction_category_id = p_transaction_category_id;
2799 --
2800 Cursor csr_def_hier (p_transaction_category_id in number,
2801 p_member_cd in varchar2) is
2802 Select routing_category_id
2803 from pqh_routing_categories_v
2804 where member_cd = p_member_cd
2805 and transaction_category_id = p_transaction_category_id
2806 and nvl(enable_flag,'Y') = 'Y'
2807 and nvl(default_flag,'N') = 'Y';
2808 --
2809 Cursor csr_def_approvers (p_routing_category_id in number) is
2810 Select null
2811 from pqh_attribute_ranges_v3
2812 Where routing_category_id = p_routing_category_id
2813 and nvl(approver_flag,'N') = 'Y'
2814 and nvl(enable_flag,'Y') = 'Y';
2815 --
2816 l_routing_category_id pqh_routing_categories.routing_category_id%type;
2817 l_member_cd pqh_transaction_categories.member_cd%type;
2818 --
2819 l_dummy varchar2(1);
2820 l_error_status boolean := TRUE;
2821 --
2822 l_error_index number(10) := 0;
2823 Begin
2824 --
2825 -- There can be two type of errors in standard setup.
2826 -- 1) There is no default hierarchy
2827 -- 2) No enabled default approvers.
2828 --
2829 -- Obtain the routing style of the transcation category.
2830 --
2831 Open csr_member_cd(p_transaction_category_id => p_transaction_category_id);
2832 Fetch csr_member_cd into l_member_cd;
2833 Close csr_member_cd;
2834 --
2835 Open csr_def_hier(p_transaction_category_id => p_transaction_category_id,
2836 p_member_cd => l_member_cd);
2837 Fetch csr_def_hier into l_routing_category_id;
2838 --
2839 -- Check if any default hierarchy is marked
2840 --
2841 If csr_def_hier%notfound then
2842 --
2843 -- set error
2844 --
2845 l_error_index := l_error_index + 1;
2846 hr_utility.set_message(8302,'PQH_TCW_STD_ERROR1');
2847 p_error_messages(l_error_index).message_text := hr_utility.get_message;
2848 --
2849 l_error_index := l_error_index + 1;
2850 hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2851 p_error_messages(l_error_index).message_text := hr_utility.get_message;
2852 --
2853 l_error_status := FALSE;
2854 Else
2855 --
2856 -- Check if there are any enable default approvers.
2857 --
2858 Open csr_def_approvers(p_routing_category_id => l_routing_category_id);
2859 Fetch csr_def_approvers into l_dummy;
2860 If csr_def_approvers%notfound then
2861 --
2862 l_error_index := l_error_index + 1;
2863 hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2864 p_error_messages(l_error_index).message_text := hr_utility.get_message;
2865 l_error_status := FALSE;
2866 --
2867 End if;
2868 Close csr_def_approvers;
2869 --
2870 End if;
2871 --
2872 Close csr_def_hier;
2873 --
2874 RETURN l_error_status;
2875 --
2876 End;
2877 ----------------------------------------------------------------------------------------------
2878 Function chk_valid_rout_hier_exists(p_transaction_category_id in number,
2879 p_routing_type in varchar2,
2880 p_error_messages out nocopy warnings_tab,
2881 p_no_errors out nocopy varchar2)
2882 RETURN BOOLEAN is
2883 --
2884 TYPE cur_type IS REF CURSOR;
2885 csr_routing cur_type;
2886 sql_stmt varchar2(1000);
2887 --
2888 l_rec_count number(10) := 0;
2889 l_no_of_rules number(10) := 0;
2890 l_no_of_errors number(10) := 0;
2891 --
2892 l_routing_category_id pqh_routing_categories.routing_category_id%type;
2893 l_list_name varchar2(200);
2894 --
2895 type rct_rec is record(routing_category_id pqh_routing_categories.routing_category_id%type,
2896 default_flag pqh_routing_categories.default_flag%type,
2897 delete_flag pqh_routing_categories.delete_flag%type);
2898 type rct_tab is table of rct_rec index by binary_integer;
2899 --
2900 l_rct_tab rct_tab;
2901 l_cnt number(15) := 0;
2902 l_dummy number(15) := 0;
2903 l_x varchar2(10);
2904 --
2905 l_proc varchar2(72) := 'chk_valid_rout_hier_exists';
2906 --
2907
2908 --Perf changes
2909 Cursor csr_ph is
2910 Select rct.routing_category_id,rct.default_flag, rct.delete_flag
2911 from pqh_routing_categories rct
2912 Where rct.transaction_category_id = p_transaction_category_id
2913 and rct.enable_flag = 'Y'
2914 and rct.position_structure_id IS NOT NULL;
2915 --
2916 Cursor csr_sh is
2917 Select rct.routing_category_id,rct.default_flag, rct.delete_flag
2918 from pqh_routing_categories rct
2919 Where rct.transaction_category_id = p_transaction_category_id
2920 and rct.enable_flag = 'Y'
2921 and rct.routing_list_id IS NULL and rct.position_structure_id IS NULL;
2922 --
2923 Cursor csr_rl is
2924 Select rct.routing_category_id,rct.default_flag, rct.delete_flag
2925 from pqh_routing_categories rct
2926 Where rct.transaction_category_id = p_transaction_category_id
2927 and rct.enable_flag = 'Y'
2928 and rct.routing_list_id IS NOT NULL;
2929 --
2930 Cursor csr_rules(p_routing_category_id in number) is
2931 Select 'x' from pqh_attribute_ranges rng
2932 Where rng.routing_category_id = p_routing_category_id
2933 and rng.enable_flag = 'Y'
2934 and nvl(delete_flag,'N') <> 'Y'
2935 and rng.routing_list_member_id IS NULL
2936 and rng.position_id IS NULL
2937 and rng.assignment_id IS NULL;
2938 --
2939 Begin
2940 --
2941 hr_utility.set_location('Entering:'||l_proc, 5);
2942 --
2943 --
2944 -- The foll cursor selects the no of enabled routing categories exist for a
2945 -- transaction category,and how many routing rules exists under each routing
2946 -- category.
2947 --
2948 If p_routing_type = 'R' then
2949 For rl_rec in csr_rl loop
2950 If nvl(rl_rec.default_flag,'X') <> 'Y' and nvl(rl_rec.delete_flag,'X') <> 'Y' then
2951 l_cnt := l_cnt + 1;
2952 l_rct_tab(l_cnt).routing_category_id := rl_rec.routing_category_id;
2953 End if;
2954 End loop;
2955 Elsif p_routing_type = 'P' then
2956 For ph_rec in csr_ph loop
2957 If nvl(ph_rec.default_flag,'X') <> 'Y' and nvl(ph_rec.delete_flag,'X') <> 'Y' then
2958 l_cnt := l_cnt + 1;
2959 l_rct_tab(l_cnt).routing_category_id := ph_rec.routing_category_id;
2960 End if;
2961 End loop;
2962 Else
2963 For sh_rec in csr_sh loop
2964 If nvl(sh_rec.default_flag,'X') <> 'Y' and nvl(sh_rec.delete_flag,'X') <> 'Y' then
2965 l_cnt := l_cnt + 1;
2966 l_rct_tab(l_cnt).routing_category_id := sh_rec.routing_category_id;
2967 End if;
2968 End loop;
2969 End if;
2970 --
2971 --
2972 l_rec_count := 0;
2973 --
2974 If l_cnt > 0 then
2975 For l_dummy in 1..l_cnt loop
2976 --
2977 --
2978 hr_utility.set_location('Getting rules for'||to_char(l_rct_tab(l_dummy).routing_category_id), 100);
2979 Open csr_rules(l_rct_tab(l_dummy).routing_category_id);
2980 Fetch csr_rules into l_x;
2981 If csr_rules%notfound then
2982 hr_utility.set_location('No routing rules ', 100);
2983 l_no_of_rules := 0;
2984 else
2985 hr_utility.set_location('Exist routing rules ', 100);
2986 l_no_of_rules := 1;
2987 End if;
2988 Close csr_rules;
2989 --
2990 l_rec_count := l_rec_count + 1;
2991 --
2992 -- No rules were defined for this routing category.
2993 --
2994 If l_no_of_rules = 0 then
2995 --
2996 hr_utility.set_location('rules =0', 100);
2997 l_no_of_errors := l_no_of_errors + 1;
2998 pqh_tct_bus.get_routing_category_name
2999 (p_routing_category_id => l_rct_tab(l_dummy).routing_category_id,
3000 p_routing_category_name => l_list_name);
3001 --
3002 hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
3003 hr_utility.set_message_token('LIST_NAME', l_list_name);
3004 p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3005 --
3006 p_no_errors := l_no_of_errors;
3007 --
3008 RETURN FALSE;
3009 --
3010 End if;
3011 --
3012 End loop;
3013 --
3014 End if;
3015 --
3016 -- The transaction category must have at least one routing category though
3017 --
3018 If l_rec_count = 0 then
3019 --
3020 l_no_of_errors := l_no_of_errors + 1;
3021 hr_utility.set_message(8302,'PQH_NO_ROUTING_CAT_IN_TCT');
3022 p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3023 --
3024 p_no_errors := l_no_of_errors;
3025 --
3026 RETURN FALSE;
3027 End if;
3028 --
3029 p_no_errors := l_no_of_errors;
3030 RETURN TRUE;
3031 --
3032 /**
3033 sql_stmt := 'Select rct.routing_category_id, count(rng.range_name)'
3034 || ' from pqh_routing_categories rct,pqh_attribute_ranges rng'
3035 || ' Where rct.transaction_category_id = :p_transaction_category_id'
3036 || ' and rct.enable_flag = :p_enable_flag'
3037 || ' and nvl(rct.default_flag,:null1) <> :p_default_flag'
3038 || ' and nvl(rct.delete_flag,:null2) <> :p_delete_flag';
3039 --
3040 If p_routing_type = 'R' then
3041 sql_stmt := sql_stmt || ' and rct.routing_list_id IS NOT NULL';
3042 Elsif p_routing_type = 'P' then
3043 sql_stmt := sql_stmt || ' and rct.position_structure_id IS NOT NULL';
3044 Else
3045 sql_stmt := sql_stmt || ' and rct.routing_list_id IS NULL and rct.position_structure_id IS NULL';
3046 End if;
3047 --
3048 sql_stmt := sql_stmt || ' and rct.routing_category_id = rng.routing_category_id(+)'
3049 || ' and rng.enable_flag(+) = :p_rule_enable'
3050 || ' and nvl(rng.delete_flag(+),:null3) <> :p_rule_delete'
3051 || ' and rng.routing_list_member_id(+) IS NULL'
3052 || ' and rng.position_id(+) IS NULL'
3053 || ' and rng.assignment_id(+) IS NULL'
3054 || ' group by rct.routing_category_id'
3055 || ' order by rct.routing_category_id';
3056 --
3057 -- Select the no of routing categories and no of rules under the routing
3058 -- category.
3059 --
3060 --
3061 Open csr_routing for sql_stmt using p_transaction_category_id,'Y','N','Y',
3062 'N','Y','Y','N','Y';
3063 --
3064 l_rec_count := 0;
3065 --
3066 Loop
3067 --
3068 Fetch csr_routing into l_routing_category_id,l_no_of_rules;
3069 --
3070 If csr_routing%notfound then
3071 exit;
3072 End if;
3073 --
3074 l_rec_count := l_rec_count + 1;
3075 --
3076 -- No rules were defined for this routing category.
3077 --
3078 If l_no_of_rules = 0 then
3079 --
3080 Close csr_routing;
3081 --
3082 l_no_of_errors := l_no_of_errors + 1;
3083 pqh_tct_bus.get_routing_category_name
3084 (p_routing_category_id =>l_routing_category_id,
3085 p_routing_category_name=> l_list_name);
3086 --
3087 hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
3088 hr_utility.set_message_token('LIST_NAME', l_list_name);
3089 p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3090 --
3091 p_no_errors := l_no_of_errors;
3092 --
3093 RETURN FALSE;
3094 --
3095 End if;
3096 --
3097 End loop;
3098 --
3099 Close csr_routing;
3100 --
3101 -- The transaction category must have at least one routing category though
3102 --
3103 If l_rec_count = 0 then
3104 --
3105 l_no_of_errors := l_no_of_errors + 1;
3106 hr_utility.set_message(8302,'PQH_NO_ROUTING_CAT_IN_TCT');
3107 p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3108 --
3109 p_no_errors := l_no_of_errors;
3110 --
3111 RETURN FALSE;
3112 End if;
3113 --
3114 p_no_errors := l_no_of_errors;
3115 RETURN TRUE;
3116 --
3117 **/
3118 End;
3119 --
3120 ------------------------------------------------------------------------------------
3121 --
3122 Function chk_rout_overlap_on_freeze(p_transaction_category_id in number,
3123 p_routing_type in varchar2)
3124 RETURN BOOLEAN is
3125 --
3126 l_routing_category_id pqh_routing_categories.routing_category_id%type;
3127 --
3128 type cur_type IS REF CURSOR;
3129 csr_chk_rule_overlap cur_type;
3130 sql_stmt varchar2(2000);
3131 --
3132 l_error_code number(10);
3133 --
3134 --
3135 l_overlap_range_name pqh_attribute_ranges.range_name%type;
3136 l_error_range_name pqh_attribute_ranges.range_name%type;
3137 l_error_routing_category varchar2(200);
3138 l_proc varchar2(72) := 'chk_overlap_on_freeze_cat';
3139 --
3140 /** Perf changes
3141 Select rct.routing_category_id,default_flag,delete_flag
3142 From pqh_routing_categories rct
3143 Where rct.transaction_category_id = :p_transaction_category_id
3144 and rct.enable_flag = 'Y'
3145 and rct.routing_list_id is not null
3146 **/
3147
3148 Begin
3149 --
3150 hr_utility.set_location('Entering:'||l_proc, 5);
3151 --
3152 -- Select all routing categories under the transaction category that
3153 -- belong to the current routing type
3154 --
3155 sql_stmt := 'Select rct.routing_category_id From pqh_routing_categories rct ';
3156 --
3157 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 ';
3158 --
3159 If p_routing_type = 'R' then
3160 --
3161 sql_stmt := sql_stmt || ' and rct.routing_list_id is not null';
3162 --
3163 Elsif p_routing_type = 'P' then
3164 --
3165 sql_stmt := sql_stmt || ' and rct.position_structure_id is not null';
3166 --
3167 Else
3168 --
3169 sql_stmt := sql_stmt || ' and rct.routing_list_id is null and rct.position_structure_id is null';
3170 --
3171 End if;
3172
3173 sql_stmt := sql_stmt || ' order by rct.routing_category_id ';
3174 --
3175 --
3176 -- We have the sql_stmt that we can execute.
3177 --
3178 Open csr_chk_rule_overlap for sql_stmt using p_transaction_category_id,'Y','N','Y','N','Y';
3179 --
3180 loop
3181 --
3182 l_error_code := 0;
3183 --
3184 Fetch csr_chk_rule_overlap into l_routing_category_id ;
3185 --
3186 If csr_chk_rule_overlap%notfound then
3187 Close csr_chk_rule_overlap;
3188 exit;
3189 End if;
3190 --
3191 l_error_code := pqh_attribute_ranges_pkg.chk_enable_routing_category
3192 (p_routing_category_id => l_routing_category_id,
3193 p_transaction_category_id => p_transaction_category_id,
3194 p_overlap_range_name => l_overlap_range_name,
3195 p_error_routing_category => l_error_routing_category,
3196 p_error_range_name => l_error_range_name);
3197 --
3198 hr_utility.set_location('Leaving:'||l_proc, 10);
3199 --
3200 If l_error_code = 1 then
3201 --
3202 Close csr_chk_rule_overlap;
3203 RETURN FALSE;
3204 --
3205 End if;
3206 --
3207 End loop;
3208 --
3209 RETURN TRUE;
3210 --
3211 End;
3212 --
3213 ----------------------------------------------------------------------------------------------
3214 --
3215 FUNCTION chk_mem_overlap_on_freeze(
3216 p_transaction_category_id in number,
3217 p_routing_type in varchar2,
3218 p_routing_category_id in number default NULL,
3219 p_error_routing_cat out nocopy varchar2,
3220 p_member_name out nocopy varchar2,
3221 p_overlap_range_1 out nocopy varchar2,
3222 p_overlap_range_2 out nocopy varchar2)
3223 --
3224 RETURN BOOLEAN is
3225 --
3226 l_error_routing_category varchar2(200);
3227 l_member_name varchar2(300);
3228 l_overlap_range_name pqh_attribute_ranges.range_name%type;
3229 l_error_range_name pqh_attribute_ranges.range_name%type;
3230 --
3231 l_prev_range_name pqh_attribute_ranges.range_name%type;
3232 l_prev_routing_category_id pqh_routing_categories.routing_category_id%type;
3233 l_prev_member_id number(30);
3234 --
3235 cnt number(10);
3236 l_attribute_range_id_list varchar2(2000);
3237 l_no_mem_identifiers number(10);
3238 --
3239 l_routing_category_id pqh_routing_categories.routing_category_id%type;
3240 l_range_name pqh_attribute_ranges.range_name%type;
3241 l_member_id number(30);
3242 l_attribute_range_id pqh_attribute_ranges.attribute_range_id %type;
3243 l_attribute_id pqh_attribute_ranges.attribute_id%type;
3244 l_column_type pqh_attributes.column_type%type;
3245 l_from_char pqh_attribute_ranges.from_char%type;
3246 l_to_char pqh_attribute_ranges.to_char%type;
3247 l_from_date pqh_attribute_ranges.from_date%type;
3248 l_to_date pqh_attribute_ranges.to_date%type;
3249 l_from_number pqh_attribute_ranges.from_number%type;
3250 l_to_number pqh_attribute_ranges.to_number%type;
3251 --
3252 l_error_code number(10) := NULL;
3253 --
3254 type cur_type IS REF CURSOR;
3255 csr_mem_overlap cur_type;
3256 sql_stmt varchar2(2000);
3257 --
3258 all_routing_rules pqh_attribute_ranges_pkg.rule_attr_tab;
3259 all_attributes_tab pqh_attribute_ranges_pkg.rule_attr_tab;
3260 --
3261 Cursor csr_mem_ident_cnt is
3262 Select count(*)
3263 from pqh_txn_category_attributes
3264 Where transaction_category_id = p_transaction_category_id
3265 AND member_identifying_flag = 'Y';
3266 --
3267 l_proc varchar2(72) := 'chk_mem_overlap_on_freeze';
3268 --
3269 Begin
3270 --
3271 hr_utility.set_location('Entering:'||l_proc, 5);
3272 --
3273 Open csr_mem_ident_cnt;
3274 Fetch csr_mem_ident_cnt into l_no_mem_identifiers;
3275 Close csr_mem_ident_cnt;
3276 --
3277 sql_stmt := 'Select rct.routing_category_id, rng.range_name ,';
3278 --
3279 If p_routing_type = 'R' then
3280 --
3281 sql_stmt := sql_stmt || ' rng.routing_list_member_id,';
3282 --
3283 Elsif p_routing_type = 'P' then
3284 --
3285 sql_stmt := sql_stmt || ' rng.position_id,';
3286 --
3287 Else
3288 --
3289 sql_stmt := sql_stmt || ' rng.assignment_id,';
3290 --
3291 End if;
3292 --
3293 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 ';
3294 --
3295 sql_stmt := sql_stmt ||' From pqh_routing_categories rct,pqh_attribute_ranges rng,pqh_attributes att ';
3296 --
3297 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 ';
3298 --
3299 -- If a routing category is passed, process only this routing category.
3300 --
3301 If p_routing_category_id IS NOT NULL then
3302 sql_stmt := sql_stmt ||' and rct.routing_category_id = :routing_category_id';
3303 End if;
3304 --
3305 sql_stmt := sql_stmt ||' and rng.routing_category_id = rct.routing_category_id and rng.attribute_id = att.attribute_id';
3306 --
3307 If p_routing_type = 'R' then
3308 --
3309 sql_stmt := sql_stmt || ' and rct.routing_list_id is not null';
3310 --
3311 Elsif p_routing_type = 'P' then
3312 --
3313 sql_stmt := sql_stmt || ' and rct.position_structure_id is not null';
3314 --
3315 Else
3316 --
3317 sql_stmt := sql_stmt || ' and rct.routing_list_id is null and rct.position_structure_id is null';
3318 --
3319 End if;
3320 --
3321 sql_stmt := sql_stmt || ' and rng.enable_flag = :rule_enable and nvl(rng.delete_flag,:null3) <> :delete_flag ';
3322 --
3323 If p_routing_type = 'R' then
3324 --
3325 sql_stmt := sql_stmt || ' and rng.routing_list_member_id is not null';
3326 --
3327 Elsif p_routing_type = 'P' then
3328 --
3329 sql_stmt := sql_stmt || ' and rng.position_id is not null';
3330 --
3331 Else
3332 --
3333 sql_stmt := sql_stmt || ' and rng.assignment_id is not null ';
3334 --
3335 End if;
3336 --
3337 sql_stmt := sql_stmt || ' order by rct.routing_category_id,rng.range_name,rng.attribute_id';
3338 --
3339 --
3340 -- We have the sql_stmt that we can execute.
3341 --
3342 If p_routing_category_id IS NOT NULL then
3343 Open csr_mem_overlap for sql_stmt using p_transaction_category_id,'Y','N','Y','N','Y',p_routing_category_id,'Y','N','Y';
3344 Else
3345 Open csr_mem_overlap for sql_stmt using p_transaction_category_id,'Y','N','Y','N','Y','Y','N','Y';
3346 End if;
3347 --
3348 cnt := 0;
3349 l_prev_range_name := NULL;
3350 l_prev_routing_category_id := NULL;
3351 l_prev_member_id := NULL;
3352 --
3353
3354 loop
3355 --
3356 Fetch csr_mem_overlap into l_routing_category_id, l_range_name,
3357 l_member_id,
3358 l_attribute_range_id,l_attribute_id,
3359 l_column_type,
3360 l_from_char,l_to_char,
3361 l_from_number,l_to_number,
3362 l_from_date,l_to_date;
3363 If csr_mem_overlap%notfound then
3364 hr_utility.set_location('Closing cursor',100);
3365 Close csr_mem_overlap;
3366 exit;
3367 End if;
3368 --
3369 --
3370 -- Check if there is a change in rule name
3371 --
3372 If l_routing_category_id <> l_prev_routing_category_id OR
3373 nvl(l_range_name,'xXx') <> nvl(l_prev_range_name,hr_api.g_varchar2) then
3374 --
3375 hr_utility.set_location('New rule:'||l_range_name ||l_proc, 6);
3376 --
3377 If cnt > 0 then
3378 hr_utility.set_location('Rules exist '||l_proc, 6);
3379 --
3380 -- call chk_routing_range_overlap procedure to check if this rule
3381 -- overlaps with any other routing rules under that
3382 -- transaction category.
3383 --
3384 hr_utility.set_location('Calling chk_member_range_overlap:'||l_proc, 6);
3385 l_error_code := pqh_attribute_ranges_pkg.chk_member_range_overlap
3386 (tab1 => all_routing_rules ,
3387 tab2 => all_attributes_tab,
3388 p_transaction_category_id => p_transaction_category_id,
3389 p_routing_category_id => l_prev_routing_category_id,
3390 p_range_name => l_prev_range_name,
3391 p_routing_type => p_routing_type,
3392 p_member_id => l_prev_member_id,
3393 p_attribute_range_id_list => l_attribute_range_id_list,
3394 p_no_attributes => l_no_mem_identifiers,
3395 p_error_range => l_error_range_name);
3396 --
3397 If l_error_code = 1 then
3398 --
3399 Close csr_mem_overlap;
3400 --
3401 p_overlap_range_1 := l_prev_range_name;
3402 p_overlap_range_2 := l_error_range_name;
3403 --
3404 pqh_tct_bus.get_routing_category_name(
3405 p_routing_category_id => l_prev_routing_category_id,
3406 p_routing_category_name => l_error_routing_category);
3407 --
3408 pqh_attribute_ranges_pkg.get_member_name
3409 (p_member_id => l_prev_member_id,
3410 p_routing_type => p_routing_type,
3411 p_member_name => l_member_name);
3412 --
3413 p_error_routing_cat := l_error_routing_category;
3414 p_member_name := l_member_name;
3415 --
3416 RETURN FALSE;
3417 --
3418 End if;
3419 --
3420 End if;
3421 -- Reset counters
3422 hr_utility.set_location('Reset counter'||l_proc, 6);
3423 --
3424 cnt := 1;
3425 l_prev_routing_category_id := l_routing_category_id;
3426 l_prev_range_name := l_range_name;
3427 l_prev_member_id := l_member_id;
3428 --
3429 l_error_code := NULL;
3430 l_error_routing_category := NULL;
3431 l_error_range_name := NULL;
3432 l_attribute_range_id_list := NULL;
3433 --
3434 Else
3435 hr_utility.set_location('Increment counter'||l_proc, 6);
3436 -- If we are processing same rule , increment counter
3437 cnt := cnt + 1;
3438 l_attribute_range_id_list := l_attribute_range_id_list || ',';
3439
3440 End if;
3441 --
3442 all_routing_rules(cnt).attribute_id := l_attribute_id;
3443 all_attributes_tab(cnt).attribute_id := l_attribute_id;
3444 all_routing_rules(cnt).datatype := l_column_type;
3445 all_attributes_tab(cnt).datatype := l_column_type;
3446 all_routing_rules(cnt).from_char := l_from_char;
3447 all_routing_rules(cnt).to_char := l_to_char;
3448 all_routing_rules(cnt).from_number := l_from_number;
3449 all_routing_rules(cnt).to_number := l_to_number;
3450 all_routing_rules(cnt).from_date := l_from_date;
3451 all_routing_rules(cnt).to_date := l_to_date;
3452 --
3453 l_attribute_range_id_list := l_attribute_range_id_list || to_char(l_attribute_range_id);
3454 --
3455 End loop;
3456 --
3457 If cnt > 0 then
3458 --
3459 hr_utility.set_location('Rules exist '||l_proc, 6);
3460 --
3461 -- call chk_routing_range_overlap procedure to check if this rule
3462 -- overlaps with any other routing rules under that
3463 -- transaction category.
3464 --
3465 hr_utility.set_location('Calling chk_routing_range_overlap:'||l_proc, 6);
3466 --
3467 l_error_code := pqh_attribute_ranges_pkg.chk_member_range_overlap
3468 (tab1 => all_routing_rules ,
3469 tab2 => all_attributes_tab,
3470 p_transaction_category_id => p_transaction_category_id,
3471 p_routing_category_id => l_prev_routing_category_id,
3472 p_range_name => l_prev_range_name,
3473 p_routing_type => p_routing_type,
3474 p_member_id => l_prev_member_id,
3475 p_attribute_range_id_list => l_attribute_range_id_list,
3476 p_no_attributes => l_no_mem_identifiers,
3477 p_error_range => l_error_range_name);
3478 --
3479 If l_error_code = 1 then
3480 --
3481 --
3482 -- Get the name of the routing category and member for
3483 -- whom there is a overlap.
3484 --
3485 --
3486 p_overlap_range_1 := l_prev_range_name;
3487 p_overlap_range_2 := l_error_range_name;
3488 --
3489 pqh_tct_bus.get_routing_category_name(
3490 p_routing_category_id => l_prev_routing_category_id,
3491 p_routing_category_name => l_error_routing_category);
3492 --
3493 pqh_attribute_ranges_pkg.get_member_name
3494 (p_member_id => l_prev_member_id,
3495 p_routing_type => p_routing_type,
3496 p_member_name => l_member_name);
3497 --
3498 p_error_routing_cat := l_error_routing_category;
3499 p_member_name := l_member_name;
3500 --
3501 RETURN FALSE;
3502 --
3503 End if;
3504 --
3505 End if;
3506 --
3507 hr_utility.set_location('Leaving'||l_proc, 10);
3508 --
3509 RETURN TRUE;
3510 --
3511 End;
3512 --
3513 --------------------------------------------------------------------------------------------------
3514 --
3515 FUNCTION check_errors_in_adv_setup(p_transaction_category_id in number,
3516 p_error_messages out nocopy warnings_tab)
3517 RETURN boolean IS
3518 --
3519 l_setup_status boolean := TRUE;
3520 l_status boolean := TRUE;
3521 --
3522 l_error_index number(10) := 0;
3523 l_error_messages warnings_tab;
3524 --
3525 l_routing_type pqh_transaction_categories.member_cd%type;
3526 --
3527 l_error_routing_category varchar2(200);
3528 l_member_name varchar2(300);
3529 l_overlap_range_name pqh_attribute_ranges.range_name%type;
3530 l_error_range_name pqh_attribute_ranges.range_name%type;
3531 --
3532 --
3533 Cursor csr_routing_type is
3534 Select member_Cd
3535 From pqh_transaction_categories
3536 Where transaction_category_id = p_transaction_category_id;
3537 --
3538 Begin
3539 --
3540 -- Obtain the routing type of the transaction category
3541 --
3542 open csr_routing_type;
3543 Fetch csr_routing_type into l_routing_type;
3544 Close csr_routing_type;
3545 --
3546 -- The Advanced setup performs 3 validations. If these 3 validations are successful , then
3547 -- The Advanced setup is considered to be completed sucessfully.
3548 -- The Validations are :
3549 -- 1. There must be at least 1 enabled routing category , with enabled routing
3550 -- rules.
3551 --
3552 l_status := chk_valid_rout_hier_exists
3553 (p_transaction_category_id => p_transaction_category_id,
3554 p_routing_type => l_routing_type,
3555 p_error_messages => l_error_messages,
3556 p_no_errors => l_error_index);
3557 If NOT l_status then
3558 --
3559 l_setup_status := FALSE;
3560 --
3561 End if;
3562 --
3563 -- 2. Routing rules must not overlap with other routing rules in the transaction category.
3564 --
3565 p_error_messages := l_error_messages;
3566 --
3567 l_status := chk_rout_overlap_on_freeze
3568 (p_transaction_category_id => p_transaction_category_id,
3569 p_routing_type => l_routing_type);
3570 --
3571 If NOT l_status then
3572 --
3573 l_error_index := l_error_index + 1;
3574 hr_utility.set_message(8302,'PQH_TCW_ADV_ERROR1');
3575 p_error_messages(l_error_index).message_text := hr_utility.get_message;
3576 --
3577 l_setup_status := FALSE;
3578 --
3579 End if;
3580 --
3581 -- 3. Authorization rules should not overlap for the same approver, within a routing category.
3582 --
3583 l_status := chk_mem_overlap_on_freeze
3584 (p_transaction_category_id => p_transaction_category_id,
3585 p_routing_type => l_routing_type,
3586 p_error_routing_cat => l_error_routing_category,
3587 p_member_name => l_member_name,
3588 p_overlap_range_1 => l_overlap_range_name,
3589 p_overlap_range_2 => l_error_range_name);
3590 --
3591 If NOT l_status then
3592 --
3593 l_error_index := l_error_index + 1;
3594 hr_utility.set_message(8302,'PQH_TCW_ADV_ERROR2');
3595 p_error_messages(l_error_index).message_text := hr_utility.get_message;
3596 --
3597 l_setup_status := FALSE;
3598 --
3599 End if;
3600 --
3601 RETURN l_setup_status;
3602 --
3603 End;
3604 --
3605 -----------------------------------------------------------------------------------------------
3606 --
3607 FUNCTION check_if_adv_setup_started(p_transaction_category_id in number)
3608 RETURN BOOLEAN is
3609 --
3610 Cursor csr_member_cd(p_transaction_category_id in number) is
3611 Select member_cd
3612 from pqh_transaction_categories
3613 Where transaction_category_id = p_transaction_category_id;
3614 --
3615 Cursor csr_hier (p_transaction_category_id in number,
3616 p_member_cd in varchar2) is
3617 Select routing_category_id
3618 from pqh_routing_categories_v a
3619 where a.transaction_category_id = p_transaction_category_id
3620 and a.member_cd = p_member_cd
3621 and nvl(a.enable_flag,'Y') = 'Y'
3622 and nvl(a.delete_flag,'N') <> 'Y'
3623 and nvl(a.default_flag,'N') <> 'Y';
3624 --
3625 l_routing_category_id pqh_routing_categories.routing_category_id%type;
3626 l_member_cd pqh_transaction_categories.member_cd%type;
3627 --
3628 l_error_status boolean := TRUE;
3629 l_proc varchar2(72) := 'check_if_adv_setup_started';
3630 --
3631 --
3632 Begin
3633 --
3634 hr_utility.set_location('Entering'||l_proc, 5);
3635 --
3636 -- Obtain the routing style of the transcation category.
3637 --
3638 Open csr_member_cd(p_transaction_category_id => p_transaction_category_id);
3639 Fetch csr_member_cd into l_member_cd;
3640 Close csr_member_cd;
3641 --
3642 Open csr_hier(p_transaction_category_id => p_transaction_category_id,
3643 p_member_cd => l_member_cd);
3644 Fetch csr_hier into l_routing_category_id;
3645 --
3646 -- Check if any default hierarchy is marked
3647 --
3648 If csr_hier%notfound then
3649 --
3650 l_error_status := FALSE;
3651 --
3652 Else
3653 --
3654 l_error_status := TRUE;
3655 --
3656 End if;
3657 --
3658 Close csr_hier;
3659 --
3660 hr_utility.set_location('Leaving'||l_proc, 10);
3661 --
3662 RETURN l_error_status;
3663 --
3664 END;
3665 --
3666 ---------------------------------------------------------------------------------------------
3667 Procedure delete_hierarchy_and_rules(p_transaction_category_id in number,
3668 p_routing_style in varchar2) is
3669 --
3670 --
3671 type cur_type IS REF CURSOR;
3672 csr_del_all cur_type;
3673 sql_stmt1 varchar2(2000);
3674 --
3675 l_proc varchar2(72) := 'delete_hierarchy_and_rules';
3676 --
3677 BEGIN
3678 --
3679 --
3680 hr_utility.set_location('Entering'||l_proc, 5);
3681 --
3682 -- The following cursor deletes the rules under the routing hierarchies
3683 -- that were selected for deletion for the passed transaction category
3684 -- and its current routing style.
3685 --
3686 sql_stmt1 := 'Delete From pqh_attribute_ranges rng Where';
3687 --
3688 sql_stmt1 := sql_stmt1 || ' rng.routing_category_id in ('
3689 || ' Select routing_category_id '
3690 || ' from pqh_routing_categories rct '
3691 ||' 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 ';
3692 --
3693 If p_routing_style = 'R' then
3694 --
3695 sql_stmt1 := sql_stmt1 || ' and rct.routing_list_id is not null)';
3696 --
3697 Elsif p_routing_style = 'P' then
3698 --
3699 sql_stmt1 := sql_stmt1 || ' and rct.position_structure_id is not null)';
3700 --
3701 Else
3702 --
3703 sql_stmt1 := sql_stmt1 || ' and rct.routing_list_id is null and rct.position_structure_id is null)';
3704 --
3705 End if;
3706 --
3707 Execute immediate sql_stmt1 using p_transaction_category_id,'N','Y','N','Y';
3708 --
3709 -- The following cursor selects all routing hierarchies that were selected for
3710 -- deletion for the passed transaction category and its current routing style.
3711 --
3712 sql_stmt1 := 'Delete From pqh_routing_categories rct';
3713 --
3714 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 ';
3715 --
3716 If p_routing_style = 'R' then
3717 --
3718 sql_stmt1 := sql_stmt1 || ' and rct.routing_list_id is not null';
3719 --
3720 Elsif p_routing_style = 'P' then
3721 --
3722 sql_stmt1 := sql_stmt1 || ' and rct.position_structure_id is not null';
3723 --
3724 Else
3725 --
3726 sql_stmt1 := sql_stmt1 || ' and rct.routing_list_id is null and rct.position_structure_id is null';
3727 --
3728 End if;
3729 --
3730 Execute immediate sql_stmt1 using p_transaction_category_id,'N','Y','N','Y';
3731 --
3732 -- Finally we need to delete just the rules that were selected for deletion
3733 -- under this transaction category and routing style.
3734 --
3735 sql_stmt1 := 'Delete From pqh_attribute_ranges rng '
3736 ||' Where nvl(rng.delete_flag,:null1) = :delete_flag ';
3737 --
3738 sql_stmt1 := sql_stmt1 ||' and rng.routing_category_id in ('
3739 ||' Select routing_category_id '
3740 ||' from pqh_routing_categories rct '
3741 ||' Where rct.transaction_category_id = :p_transaction_category_id ';
3742 --
3743 If p_routing_style = 'R' then
3744 --
3745 sql_stmt1 := sql_stmt1 || ' and rct.routing_list_id is not null)';
3746 --
3747 Elsif p_routing_style = 'P' then
3748 --
3749 sql_stmt1 := sql_stmt1 || ' and rct.position_structure_id is not null)';
3750 --
3751 Else
3752 --
3753 sql_stmt1 := sql_stmt1 || ' and rct.routing_list_id is null and rct.position_structure_id is null)';
3754 --
3755 End if;
3756 --
3757 Execute immediate sql_stmt1 using 'N','Y',p_transaction_category_id;
3758 --
3759 hr_utility.set_location('Leaving'||l_proc, 10);
3760 --
3761 END delete_hierarchy_and_rules;
3762 ---------------------------------------------------------------------------------------------
3763 FUNCTION return_approver_status(p_routing_category_id in number,
3764 p_approver_id in number,
3765 p_routing_style in varchar2)
3766 RETURN varchar2 is
3767 --
3768 type cur_type IS REF CURSOR;
3769 csr_appr cur_type;
3770 sql_stmt1 varchar2(2000);
3771 --
3772 l_approver_flag pqh_attribute_ranges.approver_flag%type;
3773 --
3774 l_proc varchar2(72) := 'return_approver_status';
3775 --
3776 BEGIN
3777 --
3778 --
3779 hr_utility.set_location('Entering'||l_proc, 5);
3780 --
3781 -- The following cursor selects the approver flag on all the rules for the
3782 -- approver under the passed routing category.
3783 --
3784 sql_stmt1 := 'Select approver_flag From pqh_attribute_ranges rng '
3785 || ' Where rng.routing_category_id = :routing_category_id';
3786 --
3787 If p_routing_style = 'R' then
3788 --
3789 sql_stmt1 := sql_stmt1 || ' and rng.routing_list_member_id IS NOT NULL and rng.routing_list_member_id = :approver_id';
3790 --
3791 Elsif p_routing_style = 'P' then
3792 --
3793 sql_stmt1 := sql_stmt1 || ' and rng.position_id IS NOT NULL and rng.position_id = :approver_id';
3794 --
3795 Else
3796 --
3797 sql_stmt1 := sql_stmt1 || ' and rng.assignment_id IS NOT NULL and rng.assignment_id = :approver_id';
3798 --
3799 End if;
3800 --
3801 --
3802 Open csr_appr for sql_stmt1 using p_routing_category_id,p_approver_id;
3803 --
3804 Loop
3805 --
3806 Fetch csr_appr into l_approver_flag;
3807 If csr_appr%notfound then
3808 exit;
3809 End if;
3810
3811 If nvl(l_approver_flag,'N') = 'Y' then
3812 Close csr_appr;
3813 RETURN 'Y';
3814 End if;
3815 --
3816 End loop;
3817
3818 --
3819 hr_utility.set_location('Leaving'||l_proc, 10);
3820 --
3821 RETURN 'N';
3822 --
3823 END return_approver_status;
3824 ----------------------------------------------------------------------------------------
3825 FUNCTION return_person_name(p_assignment_id in number)
3826 RETURN varchar2 is
3827 --
3828 --
3829 Cursor csr_name is
3830 Select full_name
3831 from per_all_assignments_f ASG, per_all_people_f PPL, fnd_sessions ses
3832 Where asg.assignment_id = p_assignment_id
3833 AND asg.person_id = PPL.person_id
3834 AND SES.SESSION_ID = USERENV('sessionid')
3835 AND SES.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
3836 AND SES.EFFECTIVE_DATE BETWEEN PPL.EFFECTIVE_START_DATE AND PPL.EFFECTIVE_END_DATE;
3837 --
3838 l_name per_all_people_f.full_name%type;
3839 l_proc varchar2(72) := 'return_person_name';
3840 --
3841 BEGIN
3842 --
3843 hr_utility.set_location('Entering'||l_proc, 5);
3844 --
3845 Open csr_name;
3846 Fetch csr_name into l_name;
3847 Close csr_name;
3848 --
3849 hr_utility.set_location('Leaving'||l_proc, 10);
3850 --
3851 RETURN l_name;
3852 --
3853 End return_person_name;
3854
3855 END pqh_tct_wizard_pkg;