[Home] [Help]
PACKAGE BODY: APPS.PQH_ATTRIBUTE_RANGES_PKG
Source
1 package body pqh_ATTRIBUTE_RANGES_pkg as
2 /* $Header: pqrngchk.pkb 115.16 2002/12/12 22:47:59 rpasapul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rng_chk.'; -- Global package name
9 --
10 ------------------------------------------------------------------------------
11 -- | Procedure Specifications
12 ------------------------------------------------------------------------------
13 --
14 PROCEDURE select_other_routing_rules
15 (p_transaction_category_id IN number,
16 p_attribute_range_id_list in varchar2,
17 p_routing_type in varchar2,
18 p_db_ranges OUT NOCOPY other_ranges_tab,
19 p_db_rows OUT NOCOPY number);
20 --
21 --
22 PROCEDURE select_other_member_rules(
23 p_transaction_category_id in number,
24 p_routing_category_id in number,
25 p_routing_type in varchar2,
26 p_member_id in number,
27 p_attribute_range_id_list in varchar2,
28 p_db_ranges out nocopy other_ranges_tab,
29 p_db_rows out nocopy number);
30 --
31 FUNCTION form_db_table(p_form_table IN rule_attr_tab,
32 p_db_table IN rule_attr_tab,
33 p_db_ranges IN other_ranges_tab,
34 p_no_attr IN number,
35 p_db_rows IN number,
36 p_error_routing_cat_id OUT NOCOPY number,
37 p_error_range_name OUT NOCOPY varchar2)
38 RETURN number;
39 --
40 FUNCTION check_unique_rules(p_table1 IN rule_attr_tab ,
41 p_table2 IN rule_attr_tab,
42 p_no_attributes IN number)
43 RETURN number ;
44 --
45 PROCEDURE assign_right_values(p_dest_from_char OUT NOCOPY varchar2,
46 p_dest_to_char OUT NOCOPY varchar2,
47 p_dest_from_number OUT NOCOPY number,
48 p_dest_to_number OUT NOCOPY number,
49 p_dest_from_date OUT NOCOPY date,
50 p_dest_to_date OUT NOCOPY date,
51 p_src_from_char IN varchar2,
52 p_src_to_char IN varchar2,
53 p_src_from_number IN number,
54 p_src_to_number IN number,
55 p_src_from_date IN date,
56 p_src_to_date IN date);
57
58 PROCEDURE chk_unique_range_name(p_routing_category_id IN number,
59 p_range_name IN varchar2,
60 p_attribute_id_list IN varchar2,
61 p_primary_flag IN varchar2);
62 --
63 -- ----------------------------------------------------------------------------
64 -- | fetch_attributes |
65 -- ----------------------------------------------------------------------------
66 --
67 -- Description : This procedure returns the List / member identifying
68 -- attributes for a transaction_category_id.
69 --
70 PROCEDURE fetch_attributes(p_transaction_category_id IN number,
71 p_att_tab IN OUT NOCOPY att_tab,
72 no_attr OUT NOCOPY number,
73 primary_flag IN varchar2) is
74 --
75 Cursor c1 is
76 select att.attribute_name,tca.attribute_id,
77 att.column_type,tca.value_style_cd,tca.value_set_id
78 from pqh_txn_category_attributes tca,pqh_attributes_vl att
79 where tca.transaction_category_id = p_transaction_category_id
80 and tca.attribute_id = att.attribute_id
81 and tca.list_identifying_flag='Y'
82 order by tca.attribute_id;
83 --
84 Cursor c2 is
85 select att.attribute_name,tca.attribute_id,
86 att.column_type,tca.value_style_cd,tca.value_set_id
87 from pqh_txn_category_attributes tca,pqh_attributes_vl att
88 where tca.transaction_category_id = p_transaction_category_id
89 and tca.attribute_id = att.attribute_id
90 and tca.member_identifying_flag='Y'
91 order by tca.attribute_id;
92 --
93 l_proc varchar2(72) := g_package||'fetch_attributes';
94 l_att_tab att_tab := p_att_tab;
95 --
96 Begin
97 --
98 hr_utility.set_location('Entering:'||l_proc, 5);
99 --
100 -- If primary flag is 'Y' , return list identifiers
101 --
102 if primary_flag = 'Y' then
103 --
104 open c1;
105 --
106 no_attr := 1;
107 loop
108 --
109 fetch c1 into p_att_tab(no_attr).attribute_name,
110 p_att_tab(no_attr).attribute_id,
111 p_att_tab(no_attr).column_type,
112 p_att_tab(no_attr).value_style_cd,
113 p_att_tab(no_attr).value_set_id;
114 --
115 exit when c1%notfound;
116 --
117 no_attr := no_attr + 1;
118 --
119 End loop;
120 --
121 Close c1;
122 --
123 else -- Return member identifiers
124 --
125 open c2;
126 --
127 no_attr := 1;
128 --
129 loop
130 --
131 fetch c2 into p_att_tab(no_attr).attribute_name,
132 p_att_tab(no_attr).attribute_id,
133 p_att_tab(no_attr).column_type,
134 p_att_tab(no_attr).value_style_cd,
135 p_att_tab(no_attr).value_set_id;
136 --
137 exit when c2%notfound;
138 --
139 no_attr := no_attr + 1;
140 --
141 End loop;
142 --
143 Close c2;
144 --
145 end if;
146 --
147 -- Decrement no_attr by 1 to account for the last fetch that failed.
148 --
149 no_attr := no_attr - 1;
150 --
151 hr_utility.set_location('Leaving:'||l_proc, 10);
152 --
153 exception when others then
154 p_att_tab := l_att_tab;
155 no_attr := null;
156 raise;
157 end fetch_attributes;
158 --
159 --
160 -- ----------------------------------------------------------------------------
161 -- | Fetches the list and member ranges for a transaction category |
162 -- ----------------------------------------------------------------------------
163 -- This procedure cannot be recoded with outer join as it is not possible to
164 -- use the outer join while using OR operand.
165 --
166 PROCEDURE fetch_ranges(p_routing_category_id in number,
167 p_range_name in varchar2,
168 p_att_ranges_tab in out nocopy att_ranges,
169 p_no_attributes in number,
170 p_primary_flag in varchar2) is
171 --
172 type cur_type IS REF CURSOR;
173 ranges_cur cur_type;
174 sql_stmt varchar2(1000);
175 --
176 temp_rec att_ranges_rec;
177 cnt number;
178 l_att_ranges_tab att_ranges := p_att_ranges_tab;
179 --
180 l_proc varchar2(72) := g_package||'fetch_ranges';
181 --
182 Begin
183 --
184 hr_utility.set_location('Entering:'||l_proc, 5);
185 --
186 sql_stmt := 'select attribute_range_id,attribute_id,from_char,to_char,from_date,to_date,from_number,to_number,object_version_number from pqh_attribute_ranges where routing_category_id = :p and range_name = :r and ';
187
188 if p_primary_flag = 'Y' then
189 sql_stmt := sql_stmt || ' attribute_id IS NOT NULL and routing_list_member_id IS NULL and position_id IS NULL and assignment_id IS NULL order by attribute_id';
190
191 else
192 sql_stmt := sql_stmt || ' attribute_id IS NOT NULL and (routing_list_member_id IS NOT NULL OR position_id IS NOT NULL OR assignment_id IS NOT NULL) order by attribute_id';
193 end if;
194 --
195 open ranges_cur for sql_stmt using p_routing_category_id,p_range_name;
196 --
197 loop
198 --
199 fetch ranges_cur into temp_rec.attribute_range_id,temp_rec.attribute_id,
200 temp_rec.from_char,temp_rec.to_char,
201 temp_rec.from_date,temp_rec.to_date,
202 temp_rec.from_number,temp_rec.to_number,
203 temp_rec.ovn;
204 --
205 exit when ranges_cur%notfound;
206 --
207 for cnt in 1..p_no_attributes loop
208 --
209 if p_att_ranges_tab(cnt).attribute_id = temp_rec.attribute_id then
210 p_att_ranges_tab(cnt).attribute_range_id := temp_rec.attribute_range_id;
211 p_att_ranges_tab(cnt).from_date := temp_rec.from_date;
212 p_att_ranges_tab(cnt).to_date := temp_rec.to_date;
213 p_att_ranges_tab(cnt).from_char := temp_rec.from_char;
214 p_att_ranges_tab(cnt).to_char := temp_rec.to_char;
215 p_att_ranges_tab(cnt).from_number := temp_rec.from_number;
216 p_att_ranges_tab(cnt).to_number := temp_rec.to_number;
217 p_att_ranges_tab(cnt).ovn := temp_rec.ovn;
218 exit;
219 end if;
220 --
221 end loop;
222 --
223 end loop;
224 --
225 close ranges_cur;
226 --
227 cnt := cnt - 1;
228 --
229 hr_utility.set_location('Leaving:'||l_proc, 10);
230 --
231 exception when others then
232 p_att_ranges_tab := l_att_ranges_tab;
233 raise;
234 end fetch_ranges;
235 --
236 --
237 -------------------------------------------------------------------------------
238 -- The following procedures are used to validate that attribute ranges
239 -- entered for a routing rule are unique.
240 --
241 -- ----------------------------------------------------------------------------
242 -- | chk_routing_range_overlap |
243 -- ----------------------------------------------------------------------------
244 --
245 PROCEDURE chk_routing_range_overlap
246 (tab1 in rule_attr_tab,
247 tab2 in rule_attr_tab,
248 p_routing_type in varchar2,
249 p_transaction_category_id in number,
250 p_attribute_range_id_list in varchar2,
251 p_no_attributes in number,
252 p_error_code out nocopy number,
253 p_error_routing_category out nocopy varchar2,
254 p_error_range_name out nocopy varchar2) is
255
256 p_attr_tab1 rule_attr_tab;
257 p_attr_tab2 rule_attr_tab;
258 --
259 exist_db_ranges other_ranges_tab;
260 no_db_rows number;
261 --
262 err_rcat number := NULL;
263 ret_val number := NULL;
264 --
265 --
266 l_proc varchar2(72) := g_package||'chk_routing_range_overlap';
267 --
268 Begin
269 --
270 hr_utility.set_location('Entering:'||l_proc, 5);
271 --
272 p_attr_tab1 := tab1;
273 p_attr_tab2 := tab2;
274 --
275 p_error_code := NULL;
276 p_error_routing_category := NULL;
277 p_error_range_name := NULL;
278 --
279 -- Select all other rules in the transaction category except the
280 -- current one.
281 --
282 select_other_routing_rules
283 (p_transaction_category_id => p_transaction_category_id,
284 p_attribute_range_id_list => p_attribute_range_id_list,
285 p_routing_type => p_routing_type,
286 p_db_ranges => exist_db_ranges,
287 p_db_rows => no_db_rows);
288 --
289 -- If there are other rules existing , then check for overlap.
290 --
291 if no_db_rows > 0 then
292 --
293 --
294 ret_val := form_db_table
295 (p_form_table => p_attr_tab1,
296 p_db_table => p_attr_tab2,
297 p_db_ranges => exist_db_ranges,
298 p_no_attr => p_no_attributes,
299 p_db_rows => no_db_rows,
300 p_error_routing_cat_id => err_rcat,
301 p_error_range_name => p_error_range_name);
302 --
303 if ret_val = 1 then
304 --
305 pqh_tct_bus.get_routing_category_name
306 (p_routing_category_id => err_rcat,
307 p_routing_category_name => p_error_routing_category);
308 --
309 p_error_code := 1;
310 --
311 End if;
312 --
313 End if;
314 --
315 hr_utility.set_location('Leaving:'||l_proc, 10);
316 --
317 End chk_routing_range_overlap;
318 --
319 --
320 ------------------------------------------------------------------------------
321 -- chk_member_range_overlap
322 ------------------------------------------------------------------------------
323 --
324 FUNCTION chk_member_range_overlap
325 (tab1 in rule_attr_tab,
326 tab2 in rule_attr_tab,
327 p_transaction_category_id in number,
328 p_routing_category_id in number,
329 p_range_name in varchar2,
330 p_routing_type in varchar2,
331 p_member_id in number,
332 p_attribute_range_id_list in varchar2,
333 p_no_attributes in number,
334 p_error_range out nocopy varchar2)
335 RETURN number is
336
337 p_attr_tab1 rule_attr_tab;
338 p_attr_tab2 rule_attr_tab;
339 exist_db_ranges other_ranges_tab;
340 no_db_rows number;
341 i number;
342 ret_value number;
343 err_rcat number;
344 --
345 l_proc varchar2(72) := g_package||'chk_member_range_overlap';
346 --
347 Begin
348 --
349 hr_utility.set_location('Entering:'||l_proc, 5);
350 --
351 p_attr_tab1 := tab1;
352 p_attr_tab2 := tab2;
353 --
354 -- Select all other rules in the transaction category except the
355 -- current one.
356 --
360 p_routing_category_id => p_routing_category_id,
357 select_other_member_rules
358 (p_transaction_category_id => p_transaction_category_id,
359 p_attribute_range_id_list => p_attribute_range_id_list,
361 p_routing_type => p_routing_type,
362 p_member_id => p_member_id,
363 p_db_ranges => exist_db_ranges,
364 p_db_rows => no_db_rows);
365 --
366 -- There can be only one another rule existing . If there is another rule
367 -- existing for the same member , then check for overlap.
368 --
369 if no_db_rows > 0 then
370 --
371 --
372 ret_value := form_db_table
373 (p_form_table => p_attr_tab1,
374 p_db_table => p_attr_tab2,
375 p_db_ranges => exist_db_ranges,
376 p_no_attr => p_no_attributes,
377 p_db_rows => no_db_rows,
378 p_error_routing_cat_id => err_rcat,
379 p_error_range_name => p_error_range);
380 --
381 --
382 if ret_value = 1 then
383 RETURN 1;
384 End if;
385 --
386 End if;
387 --
388 hr_utility.set_location('Leaving:'||l_proc, 10);
389 --
390 RETURN 0;
391 --
392 End chk_member_range_overlap;
393 --
394 --
395 -- ----------------------------------------------------------------------------
396 -- | select_other_member_rules
397 -- ----------------------------------------------------------------------------
398 --
399 PROCEDURE select_other_member_rules
400 (p_transaction_category_id in number,
401 p_routing_category_id in number,
402 p_routing_type in varchar2,
403 p_member_id in number,
404 p_attribute_range_id_list in varchar2,
405 p_db_ranges out nocopy other_ranges_tab,
406 p_db_rows out nocopy number) is
407 --
408 --
409 TYPE cur_type IS REF CURSOR;
410 other_ranges_cur cur_type;
411 sql_stmt varchar2(1000);
412 l_db_ranges other_ranges_tab;
413
414 --
415 --
416 l_proc varchar2(72) := g_package||'select_other_member_rules';
417 --
418 --
419 --
420 Begin
421 --
422 hr_utility.set_location('Entering:'||l_proc, 5);
423 --
424 sql_stmt := 'Select a.routing_category_id,a.range_name,a.attribute_id,a.from_char,a.to_char,a.from_number,a.to_number,a.from_date,a.to_date '
425 ||' from pqh_attribute_ranges a,pqh_routing_categories b '
426 ||' where a.routing_category_id = :p_routing_category_id AND a.routing_category_id = b.routing_category_id AND b.transaction_category_id = :p_transaction_category_id and a.attribute_id IS NOT NULL'
427 ||' AND a.enable_flag ='
428 ||''''
429 ||'Y'
430 ||''''
431 ||' and nvl(a.delete_flag,'||''''||'N'||''''||') <> '
432 ||''''
433 ||'Y'
434 ||''''
435 ||' AND a.attribute_range_id not in ('
436 ||p_attribute_range_id_list
437 ||') AND decode(:p_routing_type,'
438 ||''''
439 ||'R'
440 ||''''
441 ||',a.routing_list_member_id,'
442 ||''''
443 ||'P'
444 ||''''
445 ||',a.position_id,a.assignment_id) = :p_member_id order by 1,2,3';
446 --
447 --
448 open other_ranges_cur for sql_stmt using p_routing_category_id,p_transaction_category_id,p_routing_type,p_member_id;
449 --
450
451 p_db_rows := 1;
452
453 loop
454
455 fetch other_ranges_cur into p_db_ranges(p_db_rows).routing_category_id,
456 p_db_ranges(p_db_rows).range_name,
457 p_db_ranges(p_db_rows).attribute_id,
458 p_db_ranges(p_db_rows).from_char,
459 p_db_ranges(p_db_rows).to_char,
460 p_db_ranges(p_db_rows).from_number,
461 p_db_ranges(p_db_rows).to_number,
462 p_db_ranges(p_db_rows).from_date,
463 p_db_ranges(p_db_rows).to_date;
464
465 Exit when other_ranges_cur%notfound;
466 --
467 p_db_rows := p_db_rows + 1;
468 --
469 End loop;
470 --
471 p_db_rows := p_db_rows - 1;
472 --
473 close other_ranges_cur;
474 --
475 hr_utility.set_location('Leaving:'||l_proc, 10);
476 --
477 exception when others then
478 p_db_ranges := l_db_ranges;
479 p_db_rows := null;
480 raise;
481 End select_other_member_rules;
482 --
483 --
484 -- ----------------------------------------------------------------------------
485 -- | select_other_routing_rules
489 (p_transaction_category_id in number,
486 -- ----------------------------------------------------------------------------
487 --
488 PROCEDURE select_other_routing_rules
490 p_attribute_range_id_list in varchar2,
491 p_routing_type in varchar2,
492 p_db_ranges out nocopy other_ranges_tab,
493 p_db_rows out nocopy number) is
494 --
495 TYPE cur_type IS REF CURSOR;
496 other_ranges_cur cur_type;
497 sql_stmt varchar2(1000);
498 l_db_ranges other_ranges_tab;
499 --
500 l_proc varchar2(72) := g_package||'select_other_routing_rules';
501 --
502 Begin
503 --
504 hr_utility.set_location('Entering:'||l_proc, 5);
505 --
506 -- The foll cursor selects all other routing rules under the transcation
507 -- category against which the current rule will be checked for overlap.
508 --
509 sql_stmt := 'Select rng.routing_category_id,rng.range_name,rng.attribute_id,rng.from_char,rng.to_char,rng.from_number,rng.to_number,rng.from_date,rng.to_date '
510 ||' from pqh_attribute_ranges rng,pqh_routing_categories rct ';
511
512 If p_routing_type = 'R' then
513 sql_stmt := sql_stmt || ' Where rct.routing_list_id is not null ';
514 Elsif p_routing_type = 'P' then
515 sql_stmt := sql_stmt || ' Where rct.position_structure_id is not null ';
516 Else
517 sql_stmt := sql_stmt || ' Where rct.routing_list_id is null and rct.position_structure_id is null ';
518 End if;
519
520 sql_stmt:= sql_stmt ||' and rct.enable_flag = :enable_flag '
521 ||' and nvl(rct.delete_flag,:null_value1) <> :delete_flag'
522 ||' and nvl(rct.default_flag,:null_value2) <> :default_flag '
523 ||' and rct.routing_category_id = rng.routing_category_id'
524 ||' AND rct.transaction_category_id = :t '
525 ||' and rng.attribute_range_id not in ('
526 ||p_attribute_range_id_list
527 ||') and rng.enable_flag = :rule_enable '
528 ||' and nvl(rng.delete_flag,:null_value2) <> :delete_rule'
529 ||' and rng.routing_list_member_id is null and rng.position_id is null and rng.assignment_id is null order by 1,2,3';
530
531 --
532 --
533 open other_ranges_cur for sql_stmt using 'Y','N','Y','N','Y',
534 p_transaction_category_id,
535 'Y','N','Y';
536 --
537 --
538 p_db_rows := 1;
539 --
540 loop
541 --
542 fetch other_ranges_cur into p_db_ranges(p_db_rows).routing_category_id,
543 p_db_ranges(p_db_rows).range_name,
544 p_db_ranges(p_db_rows).attribute_id,
545 p_db_ranges(p_db_rows).from_char,
546 p_db_ranges(p_db_rows).to_char,
547 p_db_ranges(p_db_rows).from_number,
548 p_db_ranges(p_db_rows).to_number,
549 p_db_ranges(p_db_rows).from_date,
550 p_db_ranges(p_db_rows).to_date;
551
552 Exit when other_ranges_cur%notfound;
553 --
554 hr_utility.set_location('Other rcat :'||to_char( p_db_ranges(p_db_rows).routing_category_id),9);
555 hr_utility.set_location('Other Rule :'||p_db_ranges(p_db_rows).range_name,9);
556 p_db_rows := p_db_rows + 1;
557 --
558 End loop;
559 --
560 p_db_rows := p_db_rows - 1;
561 --
562 close other_ranges_cur;
563 --
564 hr_utility.set_location('Leaving:'||l_proc, 10);
565 --
566 exception when others then
567 p_db_ranges := l_db_ranges;
568 p_db_rows := null;
569 raise;
570 End select_other_routing_rules;
571 --
572 -- ----------------------------------------------------------------------------
573 -- | form_db_table |
574 -- ----------------------------------------------------------------------------
575 --
576 FUNCTION form_db_table(p_form_table IN rule_attr_tab,
577 p_db_table IN rule_attr_tab,
578 p_db_ranges IN other_ranges_tab,
579 p_no_attr IN number,
580 p_db_rows IN number,
581 p_error_routing_cat_id OUT NOCOPY number,
582 p_error_range_name OUT NOCOPY varchar2)
583 RETURN number is
584
585 wrk_db_table rule_attr_tab;
586 prev_rcat_id number(15);
587 prev_range_name varchar2(30);
588 ret_value number;
589 i number;
590 j number;
591 --
592 l_proc varchar2(72) := g_package||'form_db_table';
593 --
594 begin
595 --
596 hr_utility.set_location('Entering:'||l_proc, 5);
597 --
598 prev_rcat_id := p_db_ranges(1).routing_category_id;
599 prev_range_name := p_db_ranges(1).range_name;
600
601 wrk_db_table := p_db_table;
602
603 For i in 1..p_db_rows loop
604 --
605 -- Check if we are in the same routing category and range name.
606 --
610 --
607 if (prev_rcat_id = p_db_ranges(i).routing_category_id AND
608 prev_range_name = p_db_ranges(i).range_name) then
609 --
611 for j in 1..p_no_attr loop
612 --
613 -- copy the existing attribute range for the right attribute
614 -- under the routing category and range name in the work table.
615 --
616 if wrk_db_table(j).attribute_id = p_db_ranges(i).attribute_id then
617 --
618 assign_right_values
619 (p_dest_from_char => wrk_db_table(j).from_char,
620 p_dest_to_char => wrk_db_table(j).to_char ,
621 p_dest_from_number => wrk_db_table(j).from_number,
622 p_dest_to_number => wrk_db_table(j).to_number ,
623 p_dest_from_date => wrk_db_table(j).from_date,
624 p_dest_to_date => wrk_db_table(j).to_date ,
625 p_src_from_char => p_db_ranges(i).from_char,
626 p_src_to_char => p_db_ranges(i).to_char,
627 p_src_from_number => p_db_ranges(i).from_number,
628 p_src_to_number => p_db_ranges(i).to_number,
629 p_src_from_date => p_db_ranges(i).from_date ,
630 p_src_to_date => p_db_ranges(i).to_date);
631 --
632 End if;
633 --
634 End loop;
635 --
636 Else
637 --
638 -- Compare a rule in the work table against the rule from the
639 -- form.
640 --
641 ret_value := check_unique_rules(p_table1 => p_form_table,
642 p_table2 => wrk_db_table,
643 p_no_attributes => p_no_attr);
644 --
645 -- If there was range overlap , Return error
646 --
647 if ret_value = 1 then
648 p_error_routing_cat_id := prev_rcat_id;
649 p_error_range_name := prev_range_name;
650 return 1;
651 end if;
652
653 --
654 -- Else process next rule
655 --
656 prev_rcat_id := p_db_ranges(i).routing_category_id;
657 prev_range_name := p_db_ranges(i).range_name;
658 wrk_db_table := p_db_table;
659 --
660 for j in 1..p_no_attr loop
661 --
662 if wrk_db_table(j).attribute_id = p_db_ranges(i).attribute_id then
663 assign_right_values
664 (p_dest_from_char => wrk_db_table(j).from_char,
665 p_dest_to_char => wrk_db_table(j).to_char ,
666 p_dest_from_number => wrk_db_table(j).from_number,
667 p_dest_to_number => wrk_db_table(j).to_number ,
668 p_dest_from_date => wrk_db_table(j).from_date,
669 p_dest_to_date => wrk_db_table(j).to_date ,
670 p_src_from_char => p_db_ranges(i).from_char,
671 p_src_to_char => p_db_ranges(i).to_char,
672 p_src_from_number => p_db_ranges(i).from_number,
673 p_src_to_number => p_db_ranges(i).to_number,
674 p_src_from_date => p_db_ranges(i).from_date ,
675 p_src_to_date => p_db_ranges(i).to_date);
676 End if;
677 --
678 End loop;
679 --
680 End if;
681 --
682 End loop;
683
684 ret_value := check_unique_rules(p_table1 => p_form_table,
685 p_table2 => wrk_db_table,
686 p_no_attributes => p_no_attr);
687 if ret_value = 1 then
688 p_error_routing_cat_id := prev_rcat_id;
689 p_error_range_name := prev_range_name;
690 return 1;
691 end if;
692
693 hr_utility.set_location('Leaving:'||l_proc,10);
694 --
695 Return 0;
696 End form_db_table;
697 --
698 -- ----------------------------------------------------------------------------
699 -- | check_unique_rules |
700 -- ----------------------------------------------------------------------------
701 --
702 FUNCTION check_unique_rules(p_table1 in rule_attr_tab,
703 p_table2 in rule_attr_tab,
704 p_no_attributes in number)
705 RETURN number is
706 unique_flag number;
707 ctr number;
708 tab1 rule_attr_tab;
709 tab2 rule_attr_tab;
710 --
711 l_proc varchar2(72) := g_package||'check_unique_rules';
712 --
713 Begin
714 --
715 hr_utility.set_location('Entering:'||l_proc, 5);
716 --
717 tab1 := p_table1;
718 tab2 := p_table2;
719
720 unique_flag := 0;
721 for ctr in 1..p_no_attributes loop
722
723 if tab1(ctr).datatype = 'N' then
724
725 if tab1(ctr).from_number = tab2(ctr).from_number AND
726 tab1(ctr).to_number = tab2(ctr).to_number then
727 unique_flag := unique_flag + 1;
728 else
732 else
729 if tab1(ctr).to_number < tab2(ctr).from_number OR
730 tab1(ctr).from_number > tab2(ctr).to_number then
731 null;
733 unique_flag := unique_flag + 1;
734 End if;
735 End if;
736 elsif tab1(ctr).datatype = 'V' then
737 if tab1(ctr).from_char = tab2(ctr).from_char AND
738 tab1(ctr).to_char = tab2(ctr).to_char then
739 unique_flag := unique_flag + 1;
740 else
741 if tab1(ctr).to_char < tab2(ctr).from_char OR
742 tab1(ctr).from_char > tab2(ctr).to_char then
743 null;
744 else
745 unique_flag := unique_flag + 1;
746 End if;
747 End if;
748 elsif tab1(ctr).datatype = 'D' then
749 if tab1(ctr).from_date = tab2(ctr).from_date AND
750 tab1(ctr).to_date = tab2(ctr).to_date then
751 unique_flag := unique_flag + 1;
752 else
753 if tab1(ctr).to_date < tab2(ctr).from_date OR
754 tab1(ctr).from_date > tab2(ctr).to_date then
755 null;
756 else
757 unique_flag := unique_flag + 1;
758 End if;
759 End if;
760 End if;
761
762 End loop;
763
764 If unique_flag = p_no_attributes then
765 return 1;
766 End if;
767 return 0;
768 hr_utility.set_location('Leaving:'||l_proc, 10);
769 --
770 End check_unique_rules;
771 --
772 -- ----------------------------------------------------------------------------
773 -- | assign_right_values |
774 -- ----------------------------------------------------------------------------
775 --
776 PROCEDURE assign_right_values(p_dest_from_char OUT NOCOPY varchar2,
777 p_dest_to_char OUT NOCOPY varchar2,
778 p_dest_from_number OUT NOCOPY number,
779 p_dest_to_number OUT NOCOPY number,
780 p_dest_from_date OUT NOCOPY date,
781 p_dest_to_date OUT NOCOPY date,
782 p_src_from_char IN varchar2,
783 p_src_to_char IN varchar2,
784 p_src_from_number IN number,
785 p_src_to_number IN number,
786 p_src_from_date IN date,
787 p_src_to_date IN date) is
788 --
789 l_proc varchar2(72) := g_package||'assign_right_values';
790 --
791 Begin
792 --
793 hr_utility.set_location('Entering:'||l_proc, 5);
794 --
795 p_dest_from_char := p_src_from_char;
796 p_dest_to_char := p_src_to_char;
797 p_dest_from_number := p_src_from_number;
798 p_dest_to_number := p_src_to_number;
799 p_dest_from_date := p_src_from_date;
800 p_dest_to_date := p_src_to_date ;
801 --
802 hr_utility.set_location('Leaving:'||l_proc, 10);
803 --
804 End assign_right_values;
805 -- ----------------------------------------------------------------------------
806 -- | Delete_attribute_ranges
807 -- ----------------------------------------------------------------------------
808 --Description : Function to Delete attribute ranges for invalid list/member
809 -- flags .
810 -- This procedure is called from row handler for pqh_attributes.
811 -- When a list or member identifier is unmarked this procedure is
812 -- called .
813 --
814 Procedure Delete_attribute_ranges(p_attribute_id IN number,
815 p_delete_attr_ranges_flag IN varchar2,
816 p_primary_flag IN varchar2) is
817 --
818 l_dummy varchar2(1);
819 l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
820 l_object_version_number pqh_attribute_ranges.object_version_number%type;
821 --
822 Cursor c1 is
823 Select attribute_range_id , object_version_number
824 from pqh_attribute_ranges
825 where attribute_id IS NOT NULL
826 AND attribute_id = p_attribute_id
827 AND routing_list_member_id is null
828 AND position_id is null
829 AND assignment_id is null;
830 --
831 Cursor c2 is
832 Select attribute_range_id , object_version_number
833 from pqh_attribute_ranges
834 where attribute_id IS NOT NULL
835 AND attribute_id = p_attribute_id
836 AND (routing_list_member_id is not null
837 OR position_id is not null
838 OR assignment_id is not null);
839 --
840 l_proc varchar2(72) := g_package||'Delete_atrribute_ranges';
841 --
842 Begin
843 --
844 hr_utility.set_location('Entering:'||l_proc, 5);
845 --
846 if p_primary_flag = 'Y' then
847 --
848 Open c1;
849 --
850 loop
851 --
852 Fetch c1 into l_attribute_range_id , l_object_version_number;
853 --
854 if c1%notfound then
855 exit;
856 Else
857 --
858 -- Delete ranges from pqh_attribute_ranges for the unchecked
859 -- list identifier
860 --
861 If p_delete_attr_ranges_flag = 'Y' then
865 ,p_attribute_range_id => l_attribute_range_id
862 --
863 pqh_ATTRIBUTE_RANGES_api.delete_ATTRIBUTE_RANGE
864 (p_validate => false
866 ,p_object_version_number => l_object_version_number
867 ,p_effective_date => sysdate);
868 --
869 Elsif p_delete_attr_ranges_flag = 'N' then
870 --
871 Close c1;
872 hr_utility.set_message(8302,'PQH_CANNOT_UNCHECK_LIST_IDENT');
873 hr_utility.raise_error;
874 --
875 Elsif p_delete_attr_ranges_flag = 'I' then
876 --
877 -- Ignore.
878 --
879 null;
880 --
881 End if;
882 --
883 End if;
884 --
885 End loop;
886 --
887 Close c1;
888 Else
889 Open c2 ;
890 --
891 loop
892 --
893 Fetch c2 into l_attribute_range_id , l_object_version_number;
894 --
895 if c2%notfound then
896 exit;
897 Else
898 --
899 -- Delete ranges from pqh_attribute_ranges for the unchecked
900 -- member identifier ,
901 --
902 If p_delete_attr_ranges_flag = 'Y' then
903 --
904 pqh_ATTRIBUTE_RANGES_api.delete_ATTRIBUTE_RANGE
905 (p_validate => false
906 ,p_attribute_range_id => l_attribute_range_id
907 ,p_object_version_number => l_object_version_number
908 ,p_effective_date => sysdate);
909 --
910 Elsif p_delete_attr_ranges_flag = 'N' then
911 --
912 Close c2;
913 hr_utility.set_message(8302,'PQH_CANNOT_UNCHECK_MEM_IDENT');
914 hr_utility.raise_error;
915 --
916 Elsif p_delete_attr_ranges_flag = 'I' then
917 --
918 -- Ignore.
919 --
920 null;
921 --
922 End if;
923 --
924 End if;
925 --
926 End loop;
927 --
928 Close c2;
929 --
930 End if;
931 --
932 --
933 hr_utility.set_location('Leaving:'||l_proc, 10);
934 --
935 End;
936 --
937 ----------Wrapper for DML's for PQH_ATTRIBUTE_RANGES--------------------------
938 --
939 -- The following procedures are coded because a Range name created in the
940 -- form has to be inserted into the database as 1 record per attribute id
941 -- The form displays ranges for all the list / member identifiers in a
942 -- range name as 1 row
943 --
944 --
945 -- ----------------------------------------------------------------------------
946 -- | on_insert_attribute_ranges |
947 -- ----------------------------------------------------------------------------
948 --
949 PROCEDURE on_insert_attribute_ranges(
950 p_routing_category_id IN number,
951 p_range_name IN varchar2,
952 p_primary_flag IN varchar2,
953 p_routing_list_member_id IN number,
954 p_position_id IN number,
955 p_assignment_id IN number,
956 p_approver_flag IN varchar2,
957 p_enable_flag IN varchar2,
958 ins_attr_ranges_table IN OUT NOCOPY att_ranges,
959 p_no_attributes IN number) is
960 ctr number;
961 attribute_range_id_list varchar2(2000);
962 l_ins_attr_ranges_table att_ranges := ins_attr_ranges_table;
963 --
964 l_proc varchar2(72) := g_package||'on_insert_atrribute_ranges';
965 --
966 Begin
967 --
968 hr_utility.set_location('Entering:'||l_proc, 5);
969 --
970 -- Concatenate all attribute_range_id 's in ins_attr_ranges_table
971 -- into attribute_range_id_list;
972 --
973 for ctr in 1..p_no_attributes loop
974 if ctr = 1 then
975 attribute_range_id_list := '(';
976 end if;
977
978 if ins_attr_ranges_table(ctr).attribute_range_id IS NOT NULL then
979 attribute_range_id_list := attribute_range_id_list ||to_char(ins_attr_ranges_table(ctr).attribute_range_id);
980 else
981 attribute_range_id_list := attribute_range_id_list ||'-1';
982 end if;
983
984 if ctr < p_no_attributes then
985 attribute_range_id_list := attribute_range_id_list ||',';
986 else
987 attribute_range_id_list := attribute_range_id_list ||')';
988 end if;
989
990 end loop;
991 --
992 -- The unique range name check is still there because creating a
993 -- unique constraint to enforce this ,is greatly affecting performance
994 --
995 chk_unique_range_name(p_routing_category_id => p_routing_category_id,
996 p_range_name => p_range_name,
1000 --
997 p_attribute_id_list => attribute_range_id_list,
998 p_primary_flag => p_primary_flag);
999
1001 for ctr in 1..p_no_attributes loop
1002 --
1003 pqh_ATTRIBUTE_RANGES_api.create_ATTRIBUTE_RANGE
1004 (p_validate => false
1005 ,p_attribute_range_id => ins_attr_ranges_table(ctr).attribute_range_id
1006 ,p_approver_flag => p_approver_flag
1007 ,p_enable_flag => p_enable_flag
1008 ,p_assignment_id => p_assignment_id
1009 ,p_attribute_id => ins_attr_ranges_table(ctr).attribute_id
1010 ,p_from_char => ins_attr_ranges_table(ctr).from_char
1011 ,p_to_char => ins_attr_ranges_table(ctr).to_char
1012 ,p_from_date => ins_attr_ranges_table(ctr).from_date
1013 ,p_to_date => ins_attr_ranges_table(ctr).to_date
1014 ,p_from_number => ins_attr_ranges_table(ctr).from_number
1015 ,p_to_number => ins_attr_ranges_table(ctr).to_number
1016 ,p_position_id => p_position_id
1017 ,p_range_name => p_range_name
1018 ,p_routing_category_id => p_routing_category_id
1019 ,p_routing_list_member_id=> p_routing_list_member_id
1020 ,p_object_version_number => ins_attr_ranges_table(ctr).ovn
1021 ,p_effective_date => sysdate
1022 );
1023 --
1024 End loop;
1025 --
1026 hr_utility.set_location('Leaving:'||l_proc, 10);
1027 --
1028 exception when others then
1029 ins_attr_ranges_table := l_ins_attr_ranges_table;
1030 raise;
1031 End on_insert_attribute_ranges;
1032 --
1033 -- ----------------------------------------------------------------------------
1034 -- | insert_update_delete_ranges |
1035 -- ----------------------------------------------------------------------------
1036 --
1037 PROCEDURE insert_update_delete_ranges (
1038 p_routing_category_id IN number,
1039 p_range_name IN varchar2,
1040 p_primary_flag IN varchar2,
1041 p_routing_list_member_id IN number,
1042 p_position_id IN number,
1043 p_assignment_id IN number,
1044 p_approver_flag IN varchar2,
1045 p_enable_flag IN varchar2,
1046 p_attr_ranges_table IN OUT NOCOPY att_ranges,
1047 p_no_attributes IN number) is
1048 ctr number;
1049 attribute_id_list varchar2(2000);
1050 l_attr_ranges_table att_ranges := p_attr_ranges_table;
1051 --
1052 l_proc varchar2(72) := g_package||'insert_update_delete_ranges';
1053 --
1054 Begin
1055 hr_utility.set_location('Entering:'||l_proc, 5);
1056
1057 for ctr in 1..p_no_attributes loop
1058 if ctr = 1 then
1059 attribute_id_list := '(';
1060 end if;
1061
1062 if p_attr_ranges_table(ctr).attribute_range_id IS NOT NULL then
1063 attribute_id_list := attribute_id_list ||to_char(p_attr_ranges_table(ctr).attribute_range_id);
1064 else
1065 attribute_id_list := attribute_id_list ||'-1';
1066 end if;
1067
1068 if ctr < p_no_attributes then
1069 attribute_id_list := attribute_id_list ||',';
1070 else
1071 attribute_id_list := attribute_id_list ||')';
1072 end if;
1073
1074 end loop;
1075 --
1076 -- The unique range name check is still there because creating a
1077 -- unique constraint to enforce this ,is greatly affecting performance
1078 --
1079 chk_unique_range_name(p_routing_category_id => p_routing_category_id,
1080 p_range_name => p_range_name,
1081 p_attribute_id_list => attribute_id_list,
1082 p_primary_flag => p_primary_flag);
1083
1084 for ctr in 1..p_no_attributes loop
1085 --
1086 if p_attr_ranges_table(ctr).attribute_range_id IS NOT NULL then
1087 --
1088 pqh_ATTRIBUTE_RANGES_api.update_ATTRIBUTE_RANGE
1089 (p_validate => false
1090 ,p_attribute_range_id => p_attr_ranges_table(ctr).attribute_range_id
1091 ,p_approver_flag => p_approver_flag
1092 ,p_enable_flag => p_enable_flag
1093 ,p_assignment_id => p_assignment_id
1094 ,p_attribute_id => p_attr_ranges_table(ctr).attribute_id
1095 ,p_from_char => p_attr_ranges_table(ctr).from_char
1096 ,p_to_char => p_attr_ranges_table(ctr).to_char
1097 ,p_from_date => p_attr_ranges_table(ctr).from_date
1098 ,p_to_date => p_attr_ranges_table(ctr).to_date
1099 ,p_from_number => p_attr_ranges_table(ctr).from_number
1100 ,p_to_number => p_attr_ranges_table(ctr).to_number
1101 ,p_position_id => p_position_id
1102 ,p_range_name => p_range_name
1103 ,p_routing_category_id => p_routing_category_id
1104 ,p_routing_list_member_id=> p_routing_list_member_id
1105 ,p_object_version_number => p_attr_ranges_table(ctr).ovn
1109 --
1106 ,p_effective_date => sysdate
1107 );
1108 --
1110 p_attr_ranges_table(ctr).operation := 'U';
1111 --
1112 else
1113 --
1114 --
1115 pqh_ATTRIBUTE_RANGES_api.create_ATTRIBUTE_RANGE
1116 (p_validate => false
1117 ,p_attribute_range_id => p_attr_ranges_table(ctr).attribute_range_id
1118 ,p_approver_flag => p_approver_flag
1119 ,p_enable_flag => p_enable_flag
1120 ,p_assignment_id => p_assignment_id
1121 ,p_attribute_id => p_attr_ranges_table(ctr).attribute_id
1122 ,p_from_char => p_attr_ranges_table(ctr).from_char
1123 ,p_to_char => p_attr_ranges_table(ctr).to_char
1124 ,p_from_date => p_attr_ranges_table(ctr).from_date
1125 ,p_to_date => p_attr_ranges_table(ctr).to_date
1126 ,p_from_number => p_attr_ranges_table(ctr).from_number
1127 ,p_to_number => p_attr_ranges_table(ctr).to_number
1128 ,p_position_id => p_position_id
1129 ,p_range_name => p_range_name
1130 ,p_routing_category_id => p_routing_category_id
1131 ,p_routing_list_member_id=> p_routing_list_member_id
1132 ,p_object_version_number => p_attr_ranges_table(ctr).ovn
1133 ,p_effective_date => sysdate
1134 );
1135 --
1136 p_attr_ranges_table(ctr).operation := 'I';
1137 --
1138 End if;
1139 --
1140 End loop;
1141 --
1142 hr_utility.set_location('Leaving:'||l_proc, 10);
1143 exception when others then
1144 p_attr_ranges_table := l_attr_ranges_table;
1145 raise;
1146 --
1147 End insert_update_delete_ranges;
1148 --
1149 -- ----------------------------------------------------------------------------
1150 -- | on_delete_attribute_ranges |
1151 -- ----------------------------------------------------------------------------
1152 --
1153 PROCEDURE on_delete_attribute_ranges (p_validate IN boolean,
1154 del_attr_ranges_table IN OUT NOCOPY att_ranges,
1155 p_no_attributes IN number) is
1156 --
1157 ctr number;
1158 l_del_attr_ranges_table att_ranges := del_attr_ranges_table;
1159 --
1160 l_proc varchar2(72) := g_package||'on_delete_attribute_ranges';
1161 --
1162 Begin
1163 hr_utility.set_location('Entering:'||l_proc, 5);
1164 --
1165 for ctr in 1..p_no_attributes loop
1166
1167 if del_attr_ranges_table(ctr).attribute_range_id IS NOT NULL then
1168 --
1169 pqh_ATTRIBUTE_RANGES_api.delete_ATTRIBUTE_RANGE
1170 (p_validate => p_validate
1171 ,p_attribute_range_id => del_attr_ranges_table(ctr).attribute_range_id
1172 ,p_object_version_number => del_attr_ranges_table(ctr).ovn
1173 ,p_effective_date => sysdate);
1174 --
1175 End if;
1176
1177 End loop;
1178 --
1179 hr_utility.set_location('Leaving:'||l_proc, 10);
1180 --
1181 exception when others then
1182 del_attr_ranges_table := l_del_attr_ranges_table;
1183 raise;
1184 End on_delete_attribute_ranges;
1185 --
1186 --
1187 -------------------------------------------------------------------------------
1188 -- | function to ensure that unique range names are entered within a |
1189 -- | routing category id |
1190 -------------------------------------------------------------------------------
1191 --
1192 -- Not making any changes to this procedure as it is yet undecided if
1193 -- this procedure is going to be replaced by an unique constraint
1194 --
1195 PROCEDURE chk_unique_range_name (p_routing_category_id in number,
1196 p_range_name in varchar2,
1197 p_attribute_id_list in varchar2,
1198 p_primary_flag in varchar2) is
1199 --
1200 l_proc varchar2(72) := g_package||'chk_unique_range_name';
1201 --
1202 type cur_type IS REF CURSOR;
1203 range_name_cur cur_type;
1204 sql_stmt varchar2(1000);
1205 exist_range_name pqh_attribute_ranges.range_name%type;
1206
1207 Begin
1208 --
1209 hr_utility.set_location('Entering:'||l_proc, 5);
1210 --
1211 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;
1212
1213 if p_primary_flag = 'Y' then
1214 sql_stmt := sql_stmt ||' AND routing_list_member_id is NULL AND position_id is NULL AND assignment_id is NULL';
1215 else
1216 sql_stmt := sql_stmt ||' AND (routing_list_member_id is NOT NULL OR position_id is NOT NULL OR assignment_id is NOT NULL)';
1217 end if;
1218
1219 open range_name_cur for sql_stmt using p_routing_category_id;
1220
1221 Loop
1222 Fetch range_name_cur into exist_range_name;
1223 exit when range_name_cur%notfound;
1224 if exist_range_name = p_range_name then
1225 hr_utility.set_message(8302, 'PQH_DUPLICATE_RANGE_NAME');
1226 hr_utility.raise_error;
1227 end if;
1228 End loop;
1229 --
1230 hr_utility.set_location('Leaving:'||l_proc, 10);
1231 --
1232 End;
1233 --
1237 -- category , its routing rules do not overlap with other routing rules in
1234 -----------------------------------------------------------------------------
1235 --
1236 -- The following procedure ensures that when enabling a disable routing
1238 -- transaction category.
1239 --
1240 FUNCTION chk_enable_routing_category( p_routing_category_id in number,
1241 p_transaction_category_id in number,
1242 p_overlap_range_name out nocopy varchar2,
1243 p_error_routing_category out nocopy varchar2,
1244 p_error_range_name out nocopy varchar2
1245 ) RETURN NUMBER is
1246 --
1247 l_prev_range_name pqh_attribute_ranges.range_name%type;
1248 cnt number(10);
1249 l_attribute_range_id_list varchar2(2000);
1250 l_no_list_identifiers number(10);
1251 l_routing_type pqh_transaction_categories.member_cd%type;
1252 --
1253 l_routing_category_id pqh_routing_categories.routing_category_id%type;
1254 l_range_name pqh_attribute_ranges.range_name%type;
1255 l_attribute_range_id pqh_attribute_ranges.attribute_range_id %type;
1256 l_attribute_id pqh_attribute_ranges.attribute_id%type;
1257 l_column_type pqh_attributes.column_type%type;
1258 l_from_char pqh_attribute_ranges.from_char%type;
1259 l_to_char pqh_attribute_ranges.to_char%type;
1260 l_from_date pqh_attribute_ranges.from_date%type;
1261 l_to_date pqh_attribute_ranges.to_date%type;
1262 l_from_number pqh_attribute_ranges.from_number%type;
1263 l_to_number pqh_attribute_ranges.to_number%type;
1264 --
1265 l_error_code number(10) := NULL;
1266 --
1267 type cur_type IS REF CURSOR;
1268 csr_enable_routing cur_type;
1269 sql_stmt varchar2(2000);
1270 --
1271 all_routing_rules rule_attr_tab;
1272 all_attributes_tab rule_attr_tab;
1273 --
1274 --
1275 Cursor csr_routing_type is
1276 Select member_Cd
1277 From pqh_transaction_categories
1278 Where transaction_category_id = p_transaction_category_id;
1279 --
1280 Cursor csr_list_ident_cnt is
1281 Select count(*)
1282 from pqh_txn_category_attributes
1283 Where transaction_category_id = p_transaction_category_id
1284 AND list_identifying_flag = 'Y';
1285 --
1286 l_proc varchar2(72) := g_package||'chk_enable_routing_category';
1287 --
1288 Begin
1289 --
1290 hr_utility.set_location('Entering:'||l_proc, 5);
1291 --
1292 Open csr_list_ident_cnt;
1293 Fetch csr_list_ident_cnt into l_no_list_identifiers;
1294 Close csr_list_ident_cnt;
1295 --
1296 -- Obtain the routing type of the transaction category
1297 --
1298 open csr_routing_type;
1299 Fetch csr_routing_type into l_routing_type;
1300 Close csr_routing_type;
1301 --
1302 sql_stmt := 'Select rct.routing_category_id, rng.range_name , 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 ';
1303 --
1304 sql_stmt := sql_stmt ||' From pqh_routing_categories rct,pqh_attribute_ranges rng,pqh_attributes att ';
1305 --
1306 sql_stmt := sql_stmt ||' Where rct.routing_category_id = :p_routing_category_id and rng.routing_category_id = rct.routing_category_id and rng.attribute_id IS NOT NULL and rng.attribute_id = att.attribute_id';
1307 --
1308 If l_routing_type = 'R' then
1309 --
1310 sql_stmt := sql_stmt || ' and rct.routing_list_id is not null';
1311 --
1312 Elsif l_routing_type = 'P' then
1313 --
1314 sql_stmt := sql_stmt || ' and rct.position_structure_id is not null';
1315 --
1316 Else
1317 --
1318 sql_stmt := sql_stmt || ' and rct.routing_list_id is null and rct.position_structure_id is null';
1319 --
1320 End if;
1321
1322 sql_stmt := sql_stmt || ' and rng.enable_flag = :enable_rule and nvl(rng.delete_flag,:null2) <> :delete_rule and rng.routing_list_member_id IS NULL and rng.position_id IS NULL and rng.assignment_id IS NULL ';
1323
1324 sql_stmt := sql_stmt || ' order by rng.range_name,rng.attribute_id';
1325 --
1326 --
1327 -- We have the sql_stmt that we can execute.
1328 --
1329 Open csr_enable_routing for sql_stmt using p_routing_category_id,'Y','N','Y';
1330 --
1331 cnt := 0;
1332 l_prev_range_name := NULL;
1333 --
1334
1335 loop
1336 --
1337 Fetch csr_enable_routing into l_routing_category_id, l_range_name,
1338 l_attribute_range_id,l_attribute_id,
1339 l_column_type,
1340 l_from_char,l_to_char,
1341 l_from_number,l_to_number,
1342 l_from_date,l_to_date;
1343 If csr_enable_routing%notfound then
1344 hr_utility.set_location('Closing cursor',100);
1345 Close csr_enable_routing;
1346 exit;
1347 End if;
1348 --
1349 --
1350 -- Check if there is a change in rule name
1351 --
1352 If nvl(l_range_name,'xXx') <> nvl(l_prev_range_name,hr_api.g_varchar2) then
1353 hr_utility.set_location('New rule:'||l_range_name ||l_proc, 6);
1354 --
1355 If cnt > 0 then
1356 hr_utility.set_location('Rules exist '||l_proc, 6);
1357 --
1358 -- call chk_routing_range_overlap procedure to check if this rule
1359 -- overlaps with any other routing rules under that
1363 chk_routing_range_overlap
1360 -- transaction category.
1361 --
1362 hr_utility.set_location('Calling chk_routing_range_overlap:'||to_char(l_no_list_identifiers), 100);
1364 (tab1 => all_routing_rules ,
1365 tab2 => all_attributes_tab,
1366 p_routing_type => l_routing_type,
1367 p_transaction_category_id => p_transaction_category_id,
1368 p_attribute_range_id_list => l_attribute_range_id_list,
1369 p_no_attributes => l_no_list_identifiers,
1370 p_error_code => l_error_code,
1371 p_error_routing_category => p_error_routing_category,
1372 p_error_range_name => p_error_range_name);
1373 --
1374 If l_error_code = 1 then
1375 --
1376 p_overlap_range_name := l_prev_range_name;
1377 RETURN 1;
1378 End if;
1379 --
1380 End if;
1381 -- Reset counters
1382 hr_utility.set_location('Reset counter'||l_proc, 6);
1383 --
1384 cnt := 1;
1385 l_prev_range_name := l_range_name;
1386 --
1387 l_error_code := NULL;
1388 p_error_routing_category := NULL;
1389 p_error_range_name := NULL;
1390 l_attribute_range_id_list := NULL;
1391 --
1392 Else
1393 hr_utility.set_location('Increment counter'||l_proc, 6);
1394 -- If we are processing same rule , increment counter
1395 cnt := cnt + 1;
1396 l_attribute_range_id_list := l_attribute_range_id_list || ',';
1397
1398 End if;
1399 --
1400 all_routing_rules(cnt).attribute_id := l_attribute_id;
1401 all_routing_rules(cnt).datatype := l_column_type;
1402 all_routing_rules(cnt).from_char := l_from_char;
1403 all_routing_rules(cnt).to_char := l_to_char;
1404 all_routing_rules(cnt).from_number := l_from_number;
1405 all_routing_rules(cnt).to_number := l_to_number;
1406 all_routing_rules(cnt).from_date := l_from_date;
1407 all_routing_rules(cnt).to_date := l_to_date;
1408 --
1409 all_attributes_tab(cnt).attribute_id := l_attribute_id;
1410 all_attributes_tab(cnt).datatype := l_column_type;
1411 --
1412 l_attribute_range_id_list := l_attribute_range_id_list || to_char(l_attribute_range_id);
1413 --
1414 End loop;
1415 --
1416 If cnt > 0 then
1417 --
1418 hr_utility.set_location('Rules exist '||l_proc, 6);
1419 --
1420 -- call chk_routing_range_overlap procedure to check if this rule
1421 -- overlaps with any other routing rules under that
1422 -- transaction category.
1423 --
1424 hr_utility.set_location('Calling chk_routing_range_overlap:'||to_char(l_no_list_identifiers), 100);
1425 --
1426 chk_routing_range_overlap
1427 (tab1 => all_routing_rules ,
1428 tab2 => all_attributes_tab,
1429 p_routing_type => l_routing_type,
1430 p_transaction_category_id => p_transaction_category_id,
1431 p_attribute_range_id_list => l_attribute_range_id_list,
1432 p_no_attributes => l_no_list_identifiers,
1433 p_error_code => l_error_code,
1434 p_error_routing_category => p_error_routing_category,
1435 p_error_range_name => p_error_range_name);
1436 --
1437 If l_error_code = 1 then
1438 --
1439 p_overlap_range_name := l_prev_range_name;
1440 RETURN 1;
1441 --
1442 End if;
1443 --
1444 End if;
1445 --
1446 hr_utility.set_location('Leaving'||l_proc, 10);
1447 --
1448 RETURN 0;
1449 --
1450 End;
1451 --
1452 ---------------------------------------------------------------------------
1453 --
1454 -- The following procedure ensures that when Freezing a transaction
1455 -- category , its routing rules do not overlap with each other
1456 --
1457 Procedure chk_rout_overlap_on_freeze(p_transaction_category_id in number
1458 ) is
1459 --
1460 l_routing_type pqh_transaction_categories.member_cd%type;
1461 --
1462 l_routing_category_id pqh_routing_categories.routing_category_id%type;
1463 --
1464 type cur_type IS REF CURSOR;
1465 csr_chk_rule_overlap cur_type;
1466 sql_stmt varchar2(2000);
1467 --
1468 l_error_code number(10);
1469 --
1470 l_overlap_routing_category varchar2(200);
1471 l_overlap_range_name pqh_attribute_ranges.range_name%type;
1472 l_error_routing_category varchar2(200);
1473 l_error_range_name pqh_attribute_ranges.range_name%type;
1474 --
1475 Cursor csr_routing_type is
1476 Select member_Cd
1477 From pqh_transaction_categories
1478 Where transaction_category_id = p_transaction_category_id;
1479 --
1480 l_proc varchar2(72) := g_package||'chk_overlap_on_freeze_cat';
1481 --
1482 Begin
1483 --
1484 hr_utility.set_location('Entering:'||l_proc, 5);
1485 --
1486 -- Obtain the routing type of the transaction category
1487 --
1488 open csr_routing_type;
1489 Fetch csr_routing_type into l_routing_type;
1490 Close csr_routing_type;
1491 --
1492 --
1496 sql_stmt := 'Select rct.routing_category_id From pqh_routing_categories rct ';
1493 -- Select all routing categories under the transaction category that
1494 -- belong to the current routing type
1495 --
1497 --
1498 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_value1) <> :default_flag and nvl(rct.delete_flag,:null_value2) <> :delete_flag';
1499 --
1500 If l_routing_type = 'R' then
1501 --
1502 sql_stmt := sql_stmt || ' and rct.routing_list_id is not null';
1503 --
1504 Elsif l_routing_type = 'P' then
1505 --
1506 sql_stmt := sql_stmt || ' and rct.position_structure_id is not null';
1507 --
1508 Else
1509 --
1510 sql_stmt := sql_stmt || ' and rct.routing_list_id is null and rct.position_structure_id is null';
1511 --
1512 End if;
1513
1514 sql_stmt := sql_stmt || ' order by rct.routing_category_id ';
1515 --
1516 --
1517 -- We have the sql_stmt that we can execute.
1518 --
1519 Open csr_chk_rule_overlap for sql_stmt using p_transaction_category_id,
1520 'Y','N','Y','N','Y';
1521 --
1522 loop
1523 --
1524 l_error_code := 0;
1525 --
1526 Fetch csr_chk_rule_overlap into l_routing_category_id ;
1527 --
1528 If csr_chk_rule_overlap%notfound then
1529 Close csr_chk_rule_overlap;
1530 exit;
1531 End if;
1532 --
1533 l_error_code := chk_enable_routing_category
1534 (p_routing_category_id => l_routing_category_id,
1535 p_transaction_category_id => p_transaction_category_id,
1536 p_overlap_range_name => l_overlap_range_name,
1537 p_error_routing_category => l_error_routing_category,
1538 p_error_range_name => l_error_range_name);
1539 --
1540 If l_error_code = 1 then
1541 --
1542 pqh_tct_bus.get_routing_category_name
1543 (p_routing_category_id => l_routing_category_id,
1544 p_routing_category_name => l_overlap_routing_category);
1545 --
1546 hr_utility.set_message(8302,'PQH_ROUT_OVERLAP_ON_CAT_FREEZE');
1547 hr_utility.set_message_token('ROUT1',l_overlap_routing_category);
1548 hr_utility.set_message_token('RULE1',l_overlap_range_name);
1549 hr_utility.set_message_token('ROUT2',l_error_routing_category);
1550 hr_utility.set_message_token('RULE2',l_error_range_name);
1551 hr_utility.raise_error;
1552 --
1553 End if;
1554 --
1555 End loop;
1556 --
1557 hr_utility.set_location('Leaving:'||l_proc, 10);
1558 --
1559 End;
1560 --
1561 ----------------------------------------------------------------------------
1562 PROCEDURE get_member_name(p_member_id in number,
1563 p_routing_type in varchar2,
1564 p_member_name out nocopy varchar2) is
1565 --
1566 type cur_type IS REF CURSOR;
1567 csr_mem_name cur_type;
1568 sql_stmt varchar2(2000);
1569 --
1570 --
1571 l_proc varchar2(72) := g_package||'get_member_name';
1572 --
1573 Begin
1574 --
1575 hr_utility.set_location('Entering:'||l_proc, 5);
1576 --
1577 If p_routing_type = 'R' then
1578 --
1579 sql_stmt := 'Select decode(RLM.user_id,NULL,RLM.role_name,RLM.user_name'
1580 ||'||'||''''||'-'||''''||'||' ||' RLM.role_name) routing_member_name from pqh_routing_list_members_v RLM where RLM.routing_list_member_id = :p_member_id';
1581 --
1582 Elsif p_routing_type = 'P' then
1583 --
1584 sql_stmt := 'Select substr(POS.name,1,240) from hr_all_positions pos where pos.position_id = :p_member_id';
1585 --
1586 End if;
1587 --
1588 --
1589 If p_routing_type = 'R' or p_routing_type = 'P' then
1590
1591 Open csr_mem_name for sql_stmt using p_member_id;
1592 --
1593 Fetch csr_mem_name into p_member_name;
1594 --
1595 Close csr_mem_name;
1596 Else
1597 --
1598 p_member_name := pqh_utility.decode_assignment_name( p_member_id);
1599 --
1600 End if;
1601 --
1602 hr_utility.set_location('Leaving:'||l_proc, 10);
1603 exception when others then
1604 p_member_name := null;
1605 raise;
1606 --
1607 End;
1608 --
1609 --
1610 --
1611 ----------------------------------------------------------------------------
1612 --
1613 -- The following procedure ensures that when Freezing a transaction
1614 -- category , its authorization rules of a member do not overlap with
1615 -- other authorization rules for the same member.
1616 --
1617 PROCEDURE chk_mem_overlap_on_freeze( p_transaction_category_id in number
1618 )is
1619 --
1620 l_overlap_range_name pqh_attribute_ranges.range_name%type;
1621 l_error_range_name pqh_attribute_ranges.range_name%type;
1622 l_error_routing_category varchar2(200);
1623 l_member_name varchar2(300);
1624 --
1625 l_prev_range_name pqh_attribute_ranges.range_name%type;
1626 l_prev_routing_category_id pqh_routing_categories.routing_category_id%type;
1627 l_prev_member_id number(30);
1628 --
1629 cnt number(10);
1630 l_attribute_range_id_list varchar2(2000);
1631 l_no_mem_identifiers number(10);
1632 l_routing_type pqh_transaction_categories.member_cd%type;
1633 --
1637 l_attribute_range_id pqh_attribute_ranges.attribute_range_id %type;
1634 l_routing_category_id pqh_routing_categories.routing_category_id%type;
1635 l_range_name pqh_attribute_ranges.range_name%type;
1636 l_member_id number(30);
1638 l_attribute_id pqh_attribute_ranges.attribute_id%type;
1639 l_column_type pqh_attributes.column_type%type;
1640 l_from_char pqh_attribute_ranges.from_char%type;
1641 l_to_char pqh_attribute_ranges.to_char%type;
1642 l_from_date pqh_attribute_ranges.from_date%type;
1643 l_to_date pqh_attribute_ranges.to_date%type;
1644 l_from_number pqh_attribute_ranges.from_number%type;
1645 l_to_number pqh_attribute_ranges.to_number%type;
1646 --
1647 l_error_code number(10) := NULL;
1648 --
1649 type cur_type IS REF CURSOR;
1650 csr_mem_overlap cur_type;
1651 sql_stmt varchar2(2000);
1652 --
1653 all_routing_rules rule_attr_tab;
1654 all_attributes_tab rule_attr_tab;
1655 --
1656 Cursor csr_routing_type is
1657 Select member_Cd
1658 From pqh_transaction_categories
1659 Where transaction_category_id = p_transaction_category_id;
1660 --
1661 Cursor csr_mem_ident_cnt is
1662 Select count(*)
1663 from pqh_txn_category_attributes
1664 Where transaction_category_id = p_transaction_category_id
1665 AND member_identifying_flag = 'Y';
1666 --
1667 l_proc varchar2(72) := g_package||'chk_mem_overlap_on_freeze';
1668 --
1669 Begin
1670 --
1671 hr_utility.set_location('Entering:'||l_proc, 5);
1672 --
1673 Open csr_mem_ident_cnt;
1674 Fetch csr_mem_ident_cnt into l_no_mem_identifiers;
1675 Close csr_mem_ident_cnt;
1676 --
1677 -- Obtain the routing type of the transaction category
1678 --
1679 open csr_routing_type;
1680 Fetch csr_routing_type into l_routing_type;
1681 Close csr_routing_type;
1682 --
1683 sql_stmt := 'Select rct.routing_category_id, rng.range_name ,';
1684 --
1685 If l_routing_type = 'R' then
1686 --
1687 sql_stmt := sql_stmt || ' rng.routing_list_member_id,';
1688 --
1689 Elsif l_routing_type = 'P' then
1690 --
1691 sql_stmt := sql_stmt || ' rng.position_id,';
1692 --
1693 Else
1694 --
1695 sql_stmt := sql_stmt || ' rng.assignment_id,';
1696 --
1697 End if;
1698 --
1699 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 ';
1700 --
1701 sql_stmt := sql_stmt ||' From pqh_routing_categories rct,pqh_attribute_ranges rng,pqh_attributes att ';
1702 --
1703 sql_stmt := sql_stmt ||' Where rct.transaction_category_id = :p_transaction_category_id and nvl(rct.default_flag,:null_value) <> :default_flag '
1704 ||' and nvl(rct.delete_flag,:null2) <> :delete_flag and rng.routing_category_id = rct.routing_category_id and rng.attribute_id = att.attribute_id';
1705 --
1706 If l_routing_type = 'R' then
1707 --
1708 sql_stmt := sql_stmt || ' and rct.routing_list_id is not null';
1709 --
1710 Elsif l_routing_type = 'P' then
1711 --
1712 sql_stmt := sql_stmt || ' and rct.position_structure_id is not null';
1713 --
1714 Else
1715 --
1716 sql_stmt := sql_stmt || ' and rct.routing_list_id is null and rct.position_structure_id is null';
1717 --
1718 End if;
1719 --
1720 sql_stmt := sql_stmt || ' and nvl(rng.delete_flag,:null3) <> :delete_rule';
1721 --
1722 If l_routing_type = 'R' then
1723 --
1724 sql_stmt := sql_stmt || ' and rng.routing_list_member_id is not null';
1725 --
1726 Elsif l_routing_type = 'P' then
1727 --
1728 sql_stmt := sql_stmt || ' and rng.position_id is not null';
1729 --
1730 Else
1731 --
1732 sql_stmt := sql_stmt || ' and rng.assignment_id is not null ';
1733 --
1734 End if;
1735 --
1736 sql_stmt := sql_stmt || ' order by rct.routing_category_id,rng.range_name,rng.attribute_id';
1737 --
1738 --
1739 -- We have the sql_stmt that we can execute.
1740 --
1741 Open csr_mem_overlap for sql_stmt using p_transaction_category_id,'N','Y','N','Y','N','Y';
1742 --
1743 cnt := 0;
1744 l_prev_range_name := NULL;
1745 l_prev_routing_category_id := NULL;
1746 l_prev_member_id := NULL;
1747 --
1748
1749 loop
1750 --
1751 Fetch csr_mem_overlap into l_routing_category_id, l_range_name,
1752 l_member_id,
1753 l_attribute_range_id,l_attribute_id,
1754 l_column_type,
1755 l_from_char,l_to_char,
1756 l_from_number,l_to_number,
1757 l_from_date,l_to_date;
1758 If csr_mem_overlap%notfound then
1759 hr_utility.set_location('Closing cursor',100);
1760 Close csr_mem_overlap;
1761 exit;
1762 End if;
1763 --
1764 --
1765 -- Check if there is a change in rule name
1766 --
1767 If l_routing_category_id <> l_prev_routing_category_id OR
1768 nvl(l_range_name,'xXx') <> nvl(l_prev_range_name,hr_api.g_varchar2) then
1769 --
1770 hr_utility.set_location('New rule:'||l_range_name ||l_proc, 6);
1771 --
1772 If cnt > 0 then
1773 hr_utility.set_location('Rules exist '||l_proc, 6);
1774 --
1778 --
1775 -- call chk_routing_range_overlap procedure to check if this rule
1776 -- overlaps with any other routing rules under that
1777 -- transaction category.
1779 hr_utility.set_location('Calling chk_member_range_overlap:'||l_proc, 6);
1780 l_error_code := chk_member_range_overlap
1781 (tab1 => all_routing_rules ,
1782 tab2 => all_attributes_tab,
1783 p_transaction_category_id => p_transaction_category_id,
1784 p_routing_category_id => l_prev_routing_category_id,
1785 p_range_name => l_prev_range_name,
1786 p_routing_type => l_routing_type,
1787 p_member_id => l_prev_member_id,
1788 p_attribute_range_id_list => l_attribute_range_id_list,
1789 p_no_attributes => l_no_mem_identifiers,
1790 p_error_range => l_error_range_name);
1791 --
1792 If l_error_code = 1 then
1793 --
1794 -- Get the name of the routing category and member for
1795 -- whom there is a overlap.
1796 --
1797 l_overlap_range_name := l_prev_range_name;
1798 --
1799 pqh_tct_bus.get_routing_category_name(
1800 p_routing_category_id => l_prev_routing_category_id,
1801 p_routing_category_name => l_error_routing_category);
1802 --
1803 get_member_name(p_member_id => l_prev_member_id,
1804 p_routing_type => l_routing_type,
1805 p_member_name => l_member_name);
1806 --
1807 hr_utility.set_message(8302,'PQH_MEM_OVERLAP_ON_CAT_FREEZE');
1808 hr_utility.set_message_token('ROUT1',l_error_routing_category);
1809 hr_utility.set_message_token('RULE1',l_overlap_range_name);
1810 hr_utility.set_message_token('RULE2',l_error_range_name);
1811 hr_utility.set_message_token('MEMBER_NAME',l_member_name);
1812 hr_utility.raise_error;
1813 --
1814 End if;
1815 --
1816 End if;
1817 -- Reset counters
1818 hr_utility.set_location('Reset counter'||l_proc, 6);
1819 --
1820 cnt := 1;
1821 l_prev_routing_category_id := l_routing_category_id;
1822 l_prev_range_name := l_range_name;
1823 l_prev_member_id := l_member_id;
1824 --
1825 l_error_code := NULL;
1826 l_error_routing_category := NULL;
1827 l_error_range_name := NULL;
1828 l_attribute_range_id_list := NULL;
1829 --
1830 Else
1831 hr_utility.set_location('Increment counter'||l_proc, 6);
1832 -- If we are processing same rule , increment counter
1833 cnt := cnt + 1;
1834 l_attribute_range_id_list := l_attribute_range_id_list || ',';
1835
1836 End if;
1837 --
1838 all_routing_rules(cnt).attribute_id := l_attribute_id;
1839 all_attributes_tab(cnt).attribute_id := l_attribute_id;
1840 all_routing_rules(cnt).datatype := l_column_type;
1841 all_attributes_tab(cnt).datatype := l_column_type;
1842 all_routing_rules(cnt).from_char := l_from_char;
1843 all_routing_rules(cnt).to_char := l_to_char;
1844 all_routing_rules(cnt).from_number := l_from_number;
1845 all_routing_rules(cnt).to_number := l_to_number;
1846 all_routing_rules(cnt).from_date := l_from_date;
1847 all_routing_rules(cnt).to_date := l_to_date;
1848 --
1849 l_attribute_range_id_list := l_attribute_range_id_list || to_char(l_attribute_range_id);
1850 --
1851 End loop;
1852 --
1853 If cnt > 0 then
1854 --
1855 hr_utility.set_location('Rules exist '||l_proc, 6);
1856 --
1857 -- call chk_routing_range_overlap procedure to check if this rule
1858 -- overlaps with any other routing rules under that
1859 -- transaction category.
1860 --
1861 hr_utility.set_location('Calling chk_routing_range_overlap:'||l_proc, 6);
1862 --
1863 l_error_code := chk_member_range_overlap
1864 (tab1 => all_routing_rules ,
1865 tab2 => all_attributes_tab,
1866 p_transaction_category_id => p_transaction_category_id,
1867 p_routing_category_id => l_prev_routing_category_id,
1868 p_range_name => l_prev_range_name,
1869 p_routing_type => l_routing_type,
1870 p_member_id => l_prev_member_id,
1871 p_attribute_range_id_list => l_attribute_range_id_list,
1872 p_no_attributes => l_no_mem_identifiers,
1873 p_error_range => l_error_range_name);
1874 --
1875 If l_error_code = 1 then
1876 --
1877 --
1878 -- Get the name of the routing category and member for
1879 -- whom there is a overlap.
1880 --
1881 l_overlap_range_name := l_prev_range_name;
1882 --
1883 pqh_tct_bus.get_routing_category_name(
1884 p_routing_category_id => l_prev_routing_category_id,
1885 p_routing_category_name => l_error_routing_category);
1886 --
1887 get_member_name(p_member_id => l_prev_member_id,
1891 --
1888 p_routing_type => l_routing_type,
1889 p_member_name => l_member_name);
1890 --
1892 hr_utility.set_message(8302,'PQH_MEM_OVERLAP_ON_CAT_FREEZE');
1893 hr_utility.set_message_token('ROUT1',l_error_routing_category);
1894 hr_utility.set_message_token('RULE1',l_overlap_range_name);
1895 hr_utility.set_message_token('RULE2',l_error_range_name);
1896 hr_utility.set_message_token('MEMBER_NAME',l_member_name);
1897 hr_utility.raise_error;
1898 --
1899 End if;
1900 --
1901 End if;
1902 --
1903 hr_utility.set_location('Leaving'||l_proc, 10);
1904 --
1905 --
1906 End;
1907 --
1908 --
1909
1910 End pqh_ATTRIBUTE_RANGES_pkg;