[Home] [Help]
PACKAGE BODY: APPS.HXC_RESOURCE_RULES_UTILS
Source
1 Package Body hxc_resource_rules_utils as
2 /* $Header: hxchrrutl.pkb 120.3.12010000.2 2008/08/05 12:03:04 ubhat ship $ */
3 --
4 g_debug boolean :=hr_utility.debug_enabled;
5
6 PROCEDURE get_value_set_sql ( p_flex_Field_name varchar2
7 , p_legislation_code varchar2 ) IS
8
9 l_id_flex_code fnd_id_flexs.id_flex_code%TYPE;
10 l_id_flex_num fnd_id_flex_segments.id_flex_num%TYPE;
11 l_flex_seg varchar2(9);
12 l_where_clause fnd_flex_validation_tables.additional_where_clause%TYPE;
13
14 r_valueset fnd_vset.valueset_r;
15 l_valueset_dr fnd_vset.valueset_dr;
16
17 CURSOR csr_get_scl_vset_id ( p_flex_code varchar2
18 , p_id_flex_num number ) IS
19 SELECT seg.flex_value_Set_id
20 FROM
21 fnd_id_flex_segments seg
22 , fnd_id_Flexs fl
23 WHERE
24 fl.id_flex_code = p_flex_code
25 AND
26 seg.id_flex_code= fl.id_flex_code AND
27 -- ***** Start new code for bug 2669059 **************
28 seg.application_id = fl.application_id AND
29 -- ***** End new code for bug 2669059 **************
30 seg.id_flex_num = p_id_flex_num;
31
32
33 FUNCTION get_scl_flex_num RETURN NUMBER IS
34
35 CURSOR csr_get_id_flex_num IS
36 SELECT rule_mode
37 FROM pay_legislation_rules
38 WHERE legislation_code = p_legislation_code
39 AND rule_type = 'S';
40
41 l_id_flex_num fnd_id_flex_segments.id_flex_num%TYPE;
42
43 BEGIN
44
45 OPEN csr_get_id_flex_num;
46 FETCH csr_get_id_flex_num INTO l_id_flex_num;
47 CLOSE csr_get_id_flex_num;
48
49 RETURN l_id_flex_num;
50
51 END get_scl_flex_num;
52
53
54 BEGIN
55
56 l_id_flex_code := SUBSTR( p_flex_field_name, 1, (INSTR(p_flex_field_name, '-',1,1)-1) );
57 l_flex_seg := SUBSTR( p_flex_field_name, (INSTR(p_flex_field_name, '-',1,1)+1) );
58
59 IF ( l_id_flex_code = 'SCL' )
60 THEN
61
62 l_id_flex_num := get_scl_flex_num;
63
64 -- get the value set id associated with each segment
65
66 -- gaz - need to add param to the cursor for segment
67
68
69 FOR vset_rec IN csr_get_scl_vset_id ( p_flex_code => l_id_flex_code
70 , p_id_flex_num => l_id_flex_num )
71 LOOP
72
73 IF ( vset_rec.flex_value_set_id IS NOT NULL )
74 THEN
75
76 fnd_vset.get_valueset(
77 valueset_id => vset_rec.flex_value_set_id
78 , valueset => r_valueset
79 , format => l_valueset_dr);
80
81 /*
82 insert into gaz_value_set ( vset_id, name, where_clause )
83 values ( vset_rec.flex_value_set_id, r_valueset.name, r_valueset.table_info.where_clause );
84 */
85
86 END IF;
87
88 END LOOP;
89
90 END IF;
91
92 END get_value_set_sql;
93
94 FUNCTION get_sequence ( p_type varchar2
95 , p_id_flex_num number default null ) RETURN NUMBER IS
96
97 -- ***** Start commented code for bug 2669059 **************
98
99
100 -- CURSOR csr_scl_get_seq IS
101 -- SELECT seg.application_column_name
102 -- FROM
103 -- fnd_id_flex_segments seg
104 -- , fnd_id_flex_structures s
105 -- , fnd_id_Flexs fl
106 -- , pay_legislation_rules lr
107 -- WHERE
108 -- fl.id_flex_name = 'Soft Coded KeyFlexfield'
109 -- AND
110 -- s.id_flex_code = fl.id_flex_code AND
111 -- s.id_flex_num = seg.id_flex_num AND
112 -- s.id_flex_structure_code = 'GRES_AND_OTHER_DATA'
113 -- AND
114 -- lr.legislation_code = 'US' AND
115 -- lr.rule_type = 'S'
116 -- AND
117 -- seg.id_flex_num = lr.rule_mode AND
118 -- seg.id_flex_code= fl.id_flex_code AND
119 -- seg.display_flag = 'Y'
120 -- order by seg.segment_num;
121
122 -- ***** End commented code for bug 2669059 **************
123
124 -- ***** Start new code for bug 2669059 **************
125
126 CURSOR csr_scl_get_seq IS
127 SELECT seg.application_column_name
128 FROM
129 fnd_id_flex_segments seg
130 , fnd_id_flex_structures s
131 , fnd_id_Flexs fl
132 , pay_legislation_rules lr
133 WHERE
134 -- ***** Start commented code for bug 2678547 **************
135 -- fl.id_flex_name = 'Soft Coded KeyFlexfield' AND
136 -- ***** End commented code for bug 2678547 **************
137
138 -- ***** Start new code for bug 2678547 **************
139 fl.id_flex_code = 'SCL' AND
140 -- ***** End new code for bug 2678547 **************
141
142 fl.application_id = 800 AND
143 s.id_flex_code = fl.id_flex_code AND
144 s.application_id = fl.application_id AND
145 s.id_flex_num = seg.id_flex_num AND
146 s.id_flex_structure_code = 'GRES_AND_OTHER_DATA' AND
147 lr.legislation_code = 'US' AND
148 lr.rule_type = 'S' AND
149 seg.id_flex_num = lr.rule_mode AND
150 seg.application_id = s.application_id AND
151 seg.id_flex_code = fl.id_flex_code AND
152 seg.display_flag = 'Y'
153
154 ORDER BY seg.segment_num;
155
156 -- ***** End new code for bug 2669059 **************
157
158
159 -- ***** Start commented code for bug 2669059 **************
160
161
162 -- CURSOR csr_people_get_seq IS
163 -- SELECT seg.application_column_name
164 -- FROM
165 -- fnd_id_flex_segments seg
166 -- , fnd_id_flex_structures s
167 -- , fnd_id_Flexs fl
168 -- WHERE
169 -- fl.id_flex_name = 'People Group Flexfield'
170 -- AND
171 -- s.id_flex_code = fl.id_flex_code AND
172 -- s.id_flex_num = seg.id_flex_num
173 -- AND
174 -- seg.id_flex_num = p_id_flex_num AND
175 -- seg.id_flex_code= fl.id_flex_code AND
176 -- seg.display_flag = 'Y'
177 -- order by seg.segment_num;
178 -- ***** End commented code for bug 2669059 **************
179
180 -- ***** Start new code for bug 2669059 **************
181
182 CURSOR csr_people_get_seq IS
183 select seg.application_column_name
184 from
185 fnd_id_flex_segments seg,
186 fnd_id_flex_structures s,
187 fnd_id_Flexs fl
188 where
189 -- ***** Start commented code for bug 2678547 **************
190 -- fl.id_flex_name = 'People Group Flexfield' and
191 -- ***** End commented code for bug 2678547 **************
192
193 -- ***** Start new code for bug 2678547 **************
194 fl.id_flex_code = 'GRP' and
195 -- ***** End new code for bug 2678547 **************
196
197 fl.application_id = 801 and
198 s.id_flex_code = fl.id_flex_code and
199 s.application_id = fl.application_id and
200 s.id_flex_num = seg.id_flex_num and
201 seg.id_flex_num = p_id_flex_num and
202 seg.application_id = s.application_id and
203 seg.id_flex_code = fl.id_flex_code and
204 seg.display_flag = 'Y'
205 order by seg.segment_num;
206
207 -- ***** End new code for bug 2669059 **************
208
209 -- ***** Start commented code for bug 2669059 **************
210
211
212 -- CURSOR csr_grade_get_seq IS
213 -- SELECT seg.application_column_name
214 -- FROM
215 -- fnd_id_flex_segments seg
216 -- , fnd_id_flex_structures s
217 -- , fnd_id_Flexs fl
218 -- WHERE
219 -- fl.id_flex_name = 'Grade Flexfield'
220 -- AND
221 -- s.id_flex_code = fl.id_flex_code AND
222 -- s.id_flex_num = seg.id_flex_num
223 -- AND
224 -- seg.id_flex_num = p_id_flex_num AND
225 -- seg.id_flex_code= fl.id_flex_code AND
226 -- seg.display_flag = 'Y'
227 -- order by seg.segment_num;
228
229 -- ***** End commented code for bug 2669059 **************
230
231 -- ***** Start new code for bug 2669059 **************
232
233 CURSOR csr_grade_get_seq IS
234 select seg.application_column_name
235 from
236 fnd_id_flex_segments seg
237 , fnd_id_flex_structures s
238 , fnd_id_Flexs fl
239 where
240 -- ***** Start commented code for bug 2678547 **************
241 -- fl.id_flex_name = 'Grade Flexfield' and
242 -- ***** End commented code for bug 2678547 **************
243
244 -- ***** Start new code for bug 2678547 **************
245 fl.id_flex_code = 'GRD' and
246 -- ***** End new code for bug 2678547 **************
247
248 fl.application_id = 800 and
249 s.id_flex_code = fl.id_flex_code and
250 s.application_id = fl.application_id and
251 s.id_flex_num = seg.id_flex_num and
252 seg.id_flex_num = p_id_flex_num and
253 seg.application_id = s.application_id and
254 seg.id_flex_code = fl.id_flex_code and
255 seg.display_flag = 'Y'
256 order by seg.segment_num;
257
258 -- ***** End new code for bug 2669059 **************
259
260
261 l_seq NUMBER(2) := 1;
262
263
264 BEGIN
265 g_debug:=hr_utility.debug_enabled;
266 if g_debug then
267 hr_utility.set_location('gaz - in get sequence',10);
268 hr_utility.set_location('gaz - l seq is '||to_char(l_seq),10);
269 end if;
270
271 IF (SUBSTR(p_type, 1, 3) = 'SCL')
272 THEN
273 if g_debug then
274 hr_utility.set_location('gaz - in get sequence',20);
275 end if;
276
277 -- loop to find a value - sometimes null when keyflex
278 -- has had different number of segments and concatenated segment value
279 -- has different number of values.
280
281 FOR scl IN csr_scl_get_seq
282 LOOP
283 if g_debug then
284 hr_utility.set_location('gaz - in get sequence',30);
285 end if;
286
287 IF ( scl.application_column_name = SUBSTR( p_type, 5 ) )
288 THEN
289 EXIT;
290 END IF;
291
292 l_seq := l_seq + 1;
293
294 END LOOP;
295
296 ELSIF ( SUBSTR( p_type, 1, 6) = 'PEOPLE' )
297 THEN
298 if g_debug then
299 hr_utility.set_location('gaz - in get sequence',40);
300 hr_utility.set_location('gaz - l seq is '||to_char(l_seq),10);
301 end if;
302
303 FOR grp IN csr_people_get_seq
304 LOOP
305 if g_debug then
306 hr_utility.set_location('gaz - in get sequence',50);
307 hr_utility.set_location('gaz - l seq is '||to_char(l_seq),10);
308 end if;
309
310 IF ( grp.application_column_name = SUBSTR( p_type, 8 ) )
311 THEN
312 EXIT;
313 END IF;
314
315 l_seq := l_seq + 1;
316
317 END LOOP;
318 if g_debug then
319 hr_utility.set_location('gaz - in get sequence',60);
320 hr_utility.set_location('gaz - l seq is '||to_char(l_seq),10);
321 end if;
322
323 ELSIF ( SUBSTR( p_type, 1, 5) = 'GRADE' )
324 THEN
325 if g_debug then
326 hr_utility.set_location('gaz - in get sequence',70);
327 hr_utility.set_location('gaz - l seq is '||to_char(l_seq),10);
328 end if;
329
330 FOR grd IN csr_grade_get_seq
331 LOOP
332 if g_debug then
333 hr_utility.set_location('gaz - in get sequence',80);
334 hr_utility.set_location('gaz - l seq is '||to_char(l_seq),10);
335 end if;
336
337 IF ( grd.application_column_name = SUBSTR( p_type, 7 ) )
338 THEN
339 EXIT;
340 END IF;
341
342 l_seq := l_seq + 1;
343
344 END LOOP;
345 if g_debug then
346 hr_utility.set_location('gaz - in get sequence',90);
347 hr_utility.set_location('gaz - l seq is '||to_char(l_seq),10);
348 end if;
349
350 END IF;
351
352 RETURN l_seq;
353
354 END get_sequence;
355
356 FUNCTION get_meaning ( p_type VARCHAR2
357 , p_value VARCHAR2
358 , p_business_group_id NUMBER default null
359 , p_legislation_code VARCHAR2 ) RETURN VARCHAR2 IS
360
361 l_sequence NUMBER(2);
362 l_meaning VARCHAR2(240);
363
364
365 -- Perf Rep.Fix - SQL ID:3174489
366 CURSOR csr_get_scl_segs IS
367 SELECT COUNT( seg.display_flag)
368 , ifs.concatenated_segment_delimiter
369 , lr.rule_mode
370 FROM
371 fnd_id_flex_segments seg
372 , fnd_id_flex_structures ifs
373 , pay_legislation_rules lr
374 WHERE
375 lr.legislation_code = p_legislation_code AND
376 lr.rule_type = 'S' AND
377 ifs.id_flex_num = lr.rule_mode AND
378
379 -- ***** Start new code for bug 2669059 **************
380 ifs.application_id = 800 AND
381 -- ***** End new code for bug 2669059 **************
382
383 seg.id_flex_num = ifs.id_flex_num AND
384
385 -- ***** Start new code for bug 2669059 **************
386 seg.application_id = ifs.application_id AND
387 -- ***** End new code for bug 2669059 **************
388
389 seg.display_flag = 'Y'
390 GROUP BY
391 ifs.concatenated_segment_delimiter
392 , lr.rule_mode;
393
394
395 -- ***** Start commented code for bug 2669059 **************
396
397 -- CURSOR csr_get_people_segs IS
398 -- SELECT
399 -- COUNT(seg.display_flag)
400 -- , ifs.concatenated_segment_delimiter
401 -- , ifs.id_flex_num
402 -- FROM
403 -- fnd_id_flex_segments seg
404 -- , fnd_id_flex_structures ifs
405 -- , fnd_id_Flexs fl
406 -- WHERE
407 -- fl.id_flex_name = 'People Group Flexfield'
408 -- AND
409 -- ifs.id_flex_code = fl.id_Flex_code AND
410 -- ifs.id_flex_num = (
411 -- SELECT DISTINCT z.id_flex_num
412 -- FROM per_all_assignments_f asg
413 -- , pay_people_groups z
414 -- WHERE asg.people_group_id = z.people_group_id
415 -- AND asg.business_group_id = p_business_group_id )
416 -- AND
417 -- seg.id_flex_num = ifs.id_flex_num AND
418 -- seg.display_flag = 'Y'
419 -- GROUP BY
420 -- ifs.concatenated_segment_delimiter
421 -- , ifs.id_flex_num;
422 -- ***** End commented code for bug 2669059 **************
423
424
425 -- ***** Start new code for bug 2669059 **************
426
427 CURSOR csr_get_people_segs IS
428 select
429 COUNT(seg.display_flag)
430 , ifs.concatenated_segment_delimiter
431 , ifs.id_flex_num
432 from
433 fnd_id_flex_segments seg
434 , fnd_id_flex_structures ifs
435 , fnd_id_Flexs fl
436 where
437 -- ***** Start commented code for bug 2678547 **************
438 -- fl.id_flex_name = 'People Group Flexfield' AND
439 -- ***** End commented code for bug 2678547 **************
440
441 -- ***** Start new code for bug 2678547 **************
442 fl.id_flex_code = 'GRP' AND
443 -- ***** End new code for bug 2678547 **************
444
445 fl.application_id = 801 AND
446 ifs.id_flex_code = fl.id_Flex_code AND
447 ifs.application_id = fl.application_id AND
448 ifs.id_flex_num =
449 (
450 --***** Start new code for Bug 5089488**************
451 select hoi.ORG_INFORMATION5
452 from hr_organization_units hou,
453 hr_organization_information hoi,
454 hr_organization_information hoi2
455 where hou.business_group_id = p_business_group_id
456 and hou.organization_id = hoi.organization_id
457 and hoi.organization_id = hoi2.organization_id
458 and hoi.org_information_context = 'Business Group Information'
459 and sysdate between hou.Date_from and nvl(hou.date_to,sysdate)
460 and hoi2.org_information1='HR_BG' and hoi2.org_information2='Y'
461 --***** end new code for Bug 5089488**************
462 ) AND
463 seg.id_flex_num = ifs.id_flex_num AND
464 seg.application_id = ifs.application_id AND
465 -- Bug 2926733
466 seg.id_flex_code = ifs.id_flex_code AND
467 seg.display_flag = 'Y'
468 group by
469 ifs.concatenated_segment_delimiter
470 , ifs.id_flex_num;
471
472 -- ***** End new code for bug 2669059 **************
473
474
475 -- ***** Start commented code for bug 2669059 **************
476
477 -- CURSOR csr_get_grade_segs IS
478 -- SELECT
479 -- COUNT(seg.display_flag)
480 -- , ifs.concatenated_segment_delimiter
481 -- , ifs.id_flex_num
482 -- FROM
483 -- fnd_id_flex_segments seg
484 -- , fnd_id_flex_structures ifs
485 -- , fnd_id_Flexs fl
486 -- WHERE
487 -- fl.id_flex_name = 'Grade Flexfield'
488 -- AND
489 -- ifs.id_flex_code = fl.id_Flex_code AND
490 -- ifs.id_flex_num = (
491 -- SELECT DISTINCT gd.id_flex_num
492 -- FROM per_grades g
493 -- , per_grade_Definitions gd
494 -- WHERE gd.grade_definition_id = g.grade_definition_id
495 -- AND g.business_group_id = p_business_group_id )
496 -- AND
497 -- seg.id_flex_num = ifs.id_flex_num AND
498 -- seg.display_flag = 'Y'
499 -- GROUP BY
500 -- ifs.concatenated_segment_delimiter
501 -- , ifs.id_flex_num;
502 -- ***** End commented code for bug 2669059 **************
503
504
505 -- ***** Start new code for bug 2669059 **************
506
507 CURSOR csr_get_grade_segs IS
508 select
509 COUNT(seg.display_flag),
510 ifs.concatenated_segment_delimiter,
511 ifs.id_flex_num
512 from
513 fnd_id_flex_segments seg,
514 fnd_id_flex_structures ifs,
515 fnd_id_Flexs fl
516 where
517 -- ***** Start commented code for bug 2678547 **************
518 -- fl.id_flex_name = 'Grade Flexfield' and
519 -- ***** End commented code for bug 2678547 **************
520
521 -- ***** Start new code for bug 2678547 **************
522 fl.id_flex_code = 'GRD' and
523 -- ***** End new code for bug 2678547 **************
524
525 fl.application_id = 800 and
526 ifs.id_flex_code = fl.id_Flex_code and
527 ifs.application_id = fl.application_id and
528 ifs.id_flex_num =
529 (
530 --***** start new code for Bug 5089488**************
531 select hoi.ORG_INFORMATION4
532 from hr_organization_units hou,
533 hr_organization_information hoi,
534 hr_organization_information hoi2
535 where hou.business_group_id = p_business_group_id
536 and hou.organization_id = hoi.organization_id
537 and hoi.organization_id = hoi2.organization_id
538 and hoi.org_information_context = 'Business Group Information'
539 and sysdate between hou.Date_from and nvl(hou.date_to,sysdate)
540 and hoi2.org_information1='HR_BG' and hoi2.org_information2='Y'
541 --***** end new code for Bug 5089488**************
542 ) and
543 seg.id_flex_num = ifs.id_flex_num and
544 seg.application_id = ifs.application_id and
545 -- Bug 2926733
546 seg.id_flex_code = ifs.id_flex_code AND
547 seg.display_flag = 'Y'
548 group by
549 ifs.concatenated_segment_delimiter,
550 ifs.id_flex_num;
551 -- ***** End new code for bug 2669059 **************
552
553
554 CURSOR csr_get_scl_meaning(p_id_flex_num NUMBER
555 , p_sequence NUMBER
556 , p_max_sequence NUMBER
557 , p_delimiter VARCHAR2
558 , p_type VARCHAR2
559 , p_value VARCHAR2 ) IS
560 SELECT
561 DECODE ( p_sequence,
562 -- Bug 2926733
563 --1, SUBSTR( concatenated_segments, 0, INSTR ( concatenated_segments, p_delimiter, 1, 1 )-1),
564 1, SUBSTR( concatenated_segments, 0,DECODE(INSTR ( concatenated_segments,p_delimiter,1,1),0,LENGTH(concatenated_segments),
565 INSTR ( concatenated_segments, p_delimiter, 1,1)-1)),
566 p_max_sequence, SUBSTR( concatenated_segments, INSTR( concatenated_segments, p_delimiter, 1, (p_sequence-1))+1),
567 SUBSTR( concatenated_segments, (INSTR( concatenated_segments, p_delimiter, 1, (p_sequence-1))+1),
568 ( INSTR( concatenated_segments, p_delimiter, 1, p_sequence)
569 -(INSTR( concatenated_segments, p_delimiter, 1, (p_sequence-1))+1)))) meaning
570 FROM hr_soft_coding_keyflex
571 WHERE id_flex_num = p_id_flex_num
572 AND DECODE ( SUBSTR( p_type, 5 ),
573 'SEGMENT1', segment1,
574 'SEGMENT2', segment2,
575 'SEGMENT3', segment3,
576 'SEGMENT4', segment4,
577 'SEGMENT5', segment5,
578 'SEGMENT6', segment6,
579 'SEGMENT7', segment7,
580 'SEGMENT8', segment8,
581 'SEGMENT9', segment9,
582 'SEGMENT10', segment10,
583 'SEGMENT11', segment11,
584 'SEGMENT12', segment12,
585 'SEGMENT13', segment13,
586 'SEGMENT14', segment14,
587 'SEGMENT15', segment15,
588 'SEGMENT16', segment16,
589 'SEGMENT17', segment17,
590 'SEGMENT18', segment18,
591 'SEGMENT19', segment19,
592 'SEGMENT20', segment20,
593 'SEGMENT21', segment21,
594 'SEGMENT22', segment22,
595 'SEGMENT23', segment23,
596 'SEGMENT24', segment24,
597 'SEGMENT25', segment25,
598 'SEGMENT26', segment26,
599 'SEGMENT27', segment27,
600 'SEGMENT28', segment28,
601 'SEGMENT29', segment29,
602 'SEGMENT30', segment30, -1 ) = p_value;
603
604 CURSOR csr_get_people_meaning(
605 p_id_flex_num NUMBER
606 , p_sequence NUMBER
607 , p_max_sequence NUMBER
608 , p_delimiter VARCHAR2
609 , p_type VARCHAR2
610 , p_value VARCHAR2 ) IS
611 SELECT
612 DECODE ( p_sequence,
613 -- Bug 2926733
614 -- 1, SUBSTR( group_name, 0, INSTR ( group_name, p_delimiter, 1, 1 )-1),
615 1, SUBSTR( group_name, 0,DECODE(INSTR ( group_name,p_delimiter,1,1),0,LENGTH(group_name),
616 INSTR ( group_name, p_delimiter, 1,1)-1)),
617 p_max_sequence, SUBSTR( group_name, INSTR( group_name, p_delimiter, 1, (p_sequence-1))+1),
618 SUBSTR( group_name, (INSTR( group_name, p_delimiter, 1, (p_sequence-1))+1),
619 ( INSTR( group_name, p_delimiter, 1, p_sequence)
620 -(INSTR( group_name, p_delimiter, 1, (p_sequence-1))+1)))) meaning
621 FROM pay_people_groups
622 WHERE id_flex_num = p_id_flex_num
623 AND DECODE ( SUBSTR( p_type, 8 ),
624 'SEGMENT1', segment1,
625 'SEGMENT2', segment2,
626 'SEGMENT3', segment3,
627 'SEGMENT4', segment4,
628 'SEGMENT5', segment5,
629 'SEGMENT6', segment6,
630 'SEGMENT7', segment7,
631 'SEGMENT8', segment8,
632 'SEGMENT9', segment9,
633 'SEGMENT10', segment10,
634 'SEGMENT11', segment11,
635 'SEGMENT12', segment12,
636 'SEGMENT13', segment13,
637 'SEGMENT14', segment14,
638 'SEGMENT15', segment15,
639 'SEGMENT16', segment16,
640 'SEGMENT17', segment17,
641 'SEGMENT18', segment18,
642 'SEGMENT19', segment19,
643 'SEGMENT20', segment20,
644 'SEGMENT21', segment21,
645 'SEGMENT22', segment22,
646 'SEGMENT23', segment23,
647 'SEGMENT24', segment24,
648 'SEGMENT25', segment25,
649 'SEGMENT26', segment26,
650 'SEGMENT27', segment27,
651 'SEGMENT28', segment28,
652 'SEGMENT29', segment29,
653 'SEGMENT30', segment30, -1 ) = p_value;
654
655 CURSOR csr_get_grade_meaning(
656 p_id_flex_num NUMBER
657 , p_sequence NUMBER
658 , p_max_sequence NUMBER
659 , p_delimiter VARCHAR2
660 , p_type VARCHAR2
661 , p_value VARCHAR2 ) IS
662 SELECT
663 DECODE ( p_sequence,
664 -- Bug 2926733
665 --1, SUBSTR( g.name, 0, INSTR ( g.name, p_delimiter, 1, 1 )-1),
666 1, SUBSTR( g.name, 0,DECODE(INSTR ( g.name,p_delimiter,1,1),0,LENGTH(g.name),
667 INSTR ( g.name, p_delimiter, 1,1)-1)),
668 p_max_sequence, SUBSTR( g.name, INSTR( g.name, p_delimiter, 1, (p_sequence-1))+1),
669 SUBSTR( g.name, (INSTR( g.name, p_delimiter, 1, (p_sequence-1))+1),
670 ( INSTR( g.name, p_delimiter, 1, p_sequence)
671 -(INSTR( g.name, p_delimiter, 1, (p_sequence-1))+1)))) meaning
672 FROM per_grades_vl g
673 , per_grade_definitions gd
674 WHERE gd.id_flex_num = p_id_flex_num
675 AND gd.grade_definition_id = g.grade_definition_id
676 AND DECODE ( SUBSTR( p_type, 7 ),
677 'SEGMENT1', gd.segment1,
678 'SEGMENT2', gd.segment2,
679 'SEGMENT3', gd.segment3,
680 'SEGMENT4', gd.segment4,
681 'SEGMENT5', gd.segment5,
682 'SEGMENT6', gd.segment6,
683 'SEGMENT7', gd.segment7,
684 'SEGMENT8', gd.segment8,
685 'SEGMENT9', gd.segment9,
686 'SEGMENT10', gd.segment10,
687 'SEGMENT11', gd.segment11,
688 'SEGMENT12', gd.segment12,
689 'SEGMENT13', gd.segment13,
690 'SEGMENT14', gd.segment14,
691 'SEGMENT15', gd.segment15,
692 'SEGMENT16', gd.segment16,
693 'SEGMENT17', gd.segment17,
694 'SEGMENT18', gd.segment18,
695 'SEGMENT19', gd.segment19,
696 'SEGMENT20', gd.segment20,
697 'SEGMENT21', gd.segment21,
698 'SEGMENT22', gd.segment22,
699 'SEGMENT23', gd.segment23,
700 'SEGMENT24', gd.segment24,
701 'SEGMENT25', gd.segment25,
702 'SEGMENT26', gd.segment26,
703 'SEGMENT27', gd.segment27,
704 'SEGMENT28', gd.segment28,
705 'SEGMENT29', gd.segment29,
706 'SEGMENT30', gd.segment30, -1 ) = p_value;
707
708 BEGIN
709
710
711
712 IF ( SUBSTR( p_type,1,3) = 'SCL' )
713 THEN
714 if g_debug then
715 hr_utility.set_location('gaz - num of segs is '||to_Char(hxc_resource_rules_utils.g_scl_num_of_segs), 999);
716 end if;
717 IF ( hxc_resource_rules_utils.g_scl_num_of_segs IS NULL )
718 THEN
719 OPEN csr_get_scl_segs;
720 FETCH csr_get_scl_segs
721 INTO hxc_resource_rules_utils.g_scl_num_of_segs
722 , hxc_resource_rules_utils.g_scl_delimiter
723 , hxc_resource_rules_utils.g_scl_id_flex_num;
724 CLOSE csr_get_scl_segs;
725
726 END IF;
727 if g_debug then
728 hr_utility.set_location('gaz - num of segs is '||to_Char(hxc_resource_rules_utils.g_scl_num_of_segs), 999);
729 end if;
730
731 -- get sequence
732
733 l_sequence := hxc_resource_rules_utils.get_sequence ( p_type );
734
735 -- get value
736
737 if g_debug then
738 hr_utility.set_location('gaz - params are ', 999);
739 hr_utility.set_location('gaz - p id_flex_num '||to_char(hxc_resource_rules_utils.g_scl_id_flex_num) , 999);
740 hr_utility.set_location('gaz - p sequence '||to_char(l_Sequence) , 999);
741 hr_utility.set_location('gaz - p max sequence '||to_char(hxc_resource_rules_utils.g_scl_num_of_segs) , 999);
742 hr_utility.set_location('gaz - p delim '||hxc_resource_rules_utils.g_scl_delimiter , 999);
743 hr_utility.set_location('gaz - p type '||p_type , 999);
744 hr_utility.set_location('gaz - p value '||p_value , 999);
745 end if;
746
747 FOR scl IN csr_get_scl_meaning (
748 p_id_flex_num => hxc_resource_rules_utils.g_scl_id_flex_num
749 , p_sequence => l_sequence
750 , p_max_sequence=> hxc_resource_rules_utils.g_scl_num_of_segs
751 , p_delimiter => hxc_resource_rules_utils.g_scl_delimiter
752 , p_type => p_type
753 , p_value => p_value )
754 LOOP
755
756 IF scl.meaning IS NOT NULL
757 THEN
758 l_meaning := scl.meaning;
759 EXIT;
760 END IF;
761
762 END LOOP;
763
764 ELSIF ( SUBSTR( p_type,1,6) = 'PEOPLE' )
765 THEN
766 if g_debug then
767 hr_utility.set_location('gaz - num of segs is '||to_Char(hxc_resource_rules_utils.g_people_num_of_segs), 999);
768 end if;
769 IF ( hxc_resource_rules_utils.g_people_num_of_segs IS NULL )
770 THEN
771 OPEN csr_get_people_segs;
772 FETCH csr_get_people_segs
773 INTO hxc_resource_rules_utils.g_people_num_of_segs
774 , hxc_resource_rules_utils.g_people_delimiter
775 , hxc_resource_rules_utils.g_people_id_flex_num;
776 CLOSE csr_get_people_segs;
777
778 END IF;
779 if g_debug then
780 hr_utility.set_location('gaz - num of segs is '||to_Char(hxc_resource_rules_utils.g_people_num_of_segs), 999);
781 end if;
782
783 -- get sequence
784
785 l_sequence := hxc_resource_rules_utils.get_sequence ( p_type, hxc_resource_rules_utils.g_people_id_flex_num );
786
787 -- get value
788
789 if g_debug then
790 hr_utility.set_location('gaz - params are ', 999);
791 hr_utility.set_location('gaz - p id_flex_num '||to_char(hxc_resource_rules_utils.g_people_id_flex_num) , 999);
792 hr_utility.set_location('gaz - l sequence '||to_char(l_Sequence) , 999);
793 hr_utility.set_location('gaz - p max sequence '||to_char(hxc_resource_rules_utils.g_people_num_of_segs) , 999);
794 hr_utility.set_location('gaz - p delim '||hxc_resource_rules_utils.g_people_delimiter , 999);
795 hr_utility.set_location('gaz - p type '||p_type , 999);
796 hr_utility.set_location('gaz - p value '||p_value , 999);
797 end if;
798
799 FOR people IN csr_get_people_meaning (
800 p_id_flex_num => hxc_resource_rules_utils.g_people_id_flex_num
801 , p_sequence => l_sequence
802 , p_max_sequence=> hxc_resource_rules_utils.g_people_num_of_segs
803 , p_delimiter => hxc_resource_rules_utils.g_people_delimiter
804 , p_type => p_type
805 , p_value => p_value )
806 LOOP
807
808 IF people.meaning IS NOT NULL
809 THEN
810 l_meaning := people.meaning;
811 EXIT;
812 END IF;
813
814 END LOOP;
815
816 if g_debug then
817 hr_utility.set_location('gaz - meaning is '||l_meaning, 999);
818 end if;
819
820 ELSIF ( SUBSTR( p_type,1,5) = 'GRADE' )
821 THEN
822 if g_debug then
823 hr_utility.set_location('gaz - num of segs is '||to_Char(hxc_resource_rules_utils.g_grade_num_of_segs), 999);
824 end if;
825 IF ( hxc_resource_rules_utils.g_grade_num_of_segs IS NULL )
826 THEN
827 OPEN csr_get_grade_segs;
828 FETCH csr_get_grade_segs
829 INTO hxc_resource_rules_utils.g_grade_num_of_segs
830 , hxc_resource_rules_utils.g_grade_delimiter
831 , hxc_resource_rules_utils.g_grade_id_flex_num;
832 CLOSE csr_get_grade_segs;
833
834 END IF;
835 if g_debug then
836 hr_utility.set_location('gaz - num of segs is '||to_Char(hxc_resource_rules_utils.g_grade_num_of_segs), 999);
837 end if;
838
839 -- get sequence
840
841 l_sequence := hxc_resource_rules_utils.get_sequence ( p_type, hxc_resource_rules_utils.g_grade_id_flex_num );
842
843 -- get value
844
845 if g_debug then
846 hr_utility.set_location('gaz - params are ', 999);
847 hr_utility.set_location('gaz - p id_flex_num '||to_char(hxc_resource_rules_utils.g_grade_id_flex_num) , 999);
848 hr_utility.set_location('gaz - l sequence '||to_char(l_Sequence) , 999);
849 hr_utility.set_location('gaz - p max sequence '||to_char(hxc_resource_rules_utils.g_grade_num_of_segs) , 999);
850 hr_utility.set_location('gaz - p delim '||hxc_resource_rules_utils.g_grade_delimiter , 999);
851 hr_utility.set_location('gaz - p type '||p_type , 999);
852 hr_utility.set_location('gaz - p value '||p_value , 999);
853 end if;
854
855 FOR grade IN csr_get_grade_meaning (
856 p_id_flex_num => hxc_resource_rules_utils.g_grade_id_flex_num
857 , p_sequence => l_sequence
858 , p_max_sequence=> hxc_resource_rules_utils.g_grade_num_of_segs
859 , p_delimiter => hxc_resource_rules_utils.g_grade_delimiter
860 , p_type => p_type
861 , p_value => p_value )
862 LOOP
863
864 IF grade.meaning is not null
865 THEN
866 l_meaning := grade.meaning;
867 if g_debug then
868 hr_utility.set_location('gaz - meaning is '||l_meaning, 999);
869 end if;
870 exit;
871 END IF;
872
873 END LOOP;
874
875 END IF;
876
877 RETURN l_meaning;
878
879 END get_meaning;
880
881 FUNCTION get_criteria_meaning ( p_type varchar2
882 , p_business_group_id number ) RETURN VARCHAR2 IS
883
884 l_meaning varchar2(240);
885
886 -- ***** Start commented code for bug 2669059 **************
887
888 -- CURSOR csr_get_people_tl IS
889 -- SELECT
890 -- tl.form_left_prompt meaning
891 -- FROM
892 -- fnd_id_flex_segments_tl tl
893 -- , fnd_id_flex_segments seg
894 -- , fnd_id_flex_structures s
895 -- , fnd_id_Flexs fl
896 -- WHERE
897 -- fl.id_flex_name = 'People Group Flexfield'
898 -- AND
899 -- s.id_flex_code = fl.id_flex_code AND
900 -- s.id_flex_num = seg.id_flex_num
901 -- AND
902 -- seg.id_flex_code= fl.id_flex_code AND
903 -- seg.id_flex_num = (
904 -- SELECT DISTINCT z.id_flex_num
905 -- FROM per_all_assignments_f asg
906 -- , pay_people_groups z
907 -- WHERE asg.people_group_id = z.people_group_id
908 -- AND asg.business_group_id = p_business_group_id ) AND
909 -- seg.display_flag = 'Y'
910 -- AND
911 -- seg.application_column_name = SUBSTR(p_type, 8)
912 -- AND
913 -- tl.application_id = seg.application_id AND
914 -- tl.id_flex_num = seg.id_flex_num AND
915 -- tl.id_flex_code = seg.id_flex_code AND
916 -- tl.application_column_name = seg.application_column_name;
917 -- ***** End commented code for bug 2669059 **************
918
919 -- ***** Start new code for bug 2669059 **************
920
921
922 CURSOR csr_get_people_tl IS
923 SELECT
924 tl.form_left_prompt meaning
925 FROM
926 fnd_id_flex_segments_tl tl
927 , fnd_id_flex_segments seg
928 , fnd_id_flex_structures s
929 , fnd_id_Flexs fl
930 WHERE
931 -- ***** Start commented code for bug 2678547 **************
932 -- fl.id_flex_name = 'People Group Flexfield' and
933 -- ***** End commented code for bug 2678547 **************
934
935 -- ***** Start new code for bug 2678547 **************
936 fl.id_flex_code = 'GRP' and
937 -- ***** End new code for bug 2678547 **************
938
939 fl.application_id = 801 AND
940 s.id_flex_code = fl.id_flex_code AND
941 s.application_id = fl.application_id and
942 s.id_flex_num = seg.id_flex_num AND
943 seg.id_flex_code = fl.id_flex_code AND
944 seg.application_id = fl.application_id AND
945 seg.id_flex_num =
946 (
947 --***** start new code for Bug 5089488**************
948 select hoi.ORG_INFORMATION5
949 from hr_organization_units hou,
950 hr_organization_information hoi,
951 hr_organization_information hoi2
952 where hou.business_group_id = p_business_group_id
953 and hou.organization_id = hoi.organization_id
954 and hoi.organization_id = hoi2.organization_id
955 and hoi.org_information_context = 'Business Group Information'
956 and sysdate between hou.Date_from and nvl(hou.date_to,sysdate)
957 and hoi2.org_information1='HR_BG' and hoi2.org_information2='Y'
958 --***** end new code for Bug 5089488**************
959 )
960 and
961 seg.display_flag = 'Y' and
962 seg.application_column_name = SUBSTR(p_type, 8) and
963 tl.application_id = seg.application_id and
964 tl.id_flex_num = seg.id_flex_num and
965 tl.id_flex_code = seg.id_flex_code and
966 tl.language = USERENV('LANG') and
967 tl.application_column_name = seg.application_column_name;
968
969 -- ***** End new code for bug 2669059 **************
970
971
972 -- ***** Start commented code for bug 2669059 **************
973
974 -- CURSOR csr_get_grade_tl IS
975 -- SELECT
976 -- tl.form_left_prompt meaning
977 -- FROM
978 -- fnd_id_flex_segments_tl tl
979 -- , fnd_id_flex_segments seg
980 -- , fnd_id_flex_structures s
981 -- , fnd_id_Flexs fl
982 -- WHERE
983 -- fl.id_flex_name = 'Grade Flexfield'
984 -- AND
985 -- s.id_flex_code = fl.id_flex_code AND
986 -- s.id_flex_num = seg.id_flex_num
987 -- AND
988 -- seg.id_flex_code= fl.id_flex_code AND
989 -- seg.id_flex_num = (
990 -- SELECT DISTINCT z.id_flex_num
991 -- FROM per_grade_definitions z
992 -- , per_grades y
993 -- WHERE y.business_group_id = p_business_group_id
994 -- AND y.grade_definition_id = z.grade_definition_id ) AND
995 -- seg.display_flag = 'Y'
996 -- AND
997 -- seg.application_column_name = SUBSTR(p_type, 7)
998 -- AND
999 -- tl.application_id = seg.application_id AND
1000 -- tl.id_flex_num = seg.id_flex_num AND
1001 -- tl.id_flex_code = seg.id_flex_code AND
1002 -- tl.application_column_name = seg.application_column_name;
1003
1004 -- ***** End commented code for bug 2669059 **************
1005
1006
1007 -- ***** Start new code for bug 2669059 **************
1008
1009
1010 CURSOR csr_get_grade_tl IS
1011 select
1012 tl.form_left_prompt meaning
1013 from
1014 fnd_id_flex_segments_tl tl
1015 , fnd_id_flex_segments seg
1016 , fnd_id_flex_structures s
1017 , fnd_id_Flexs fl
1018 where
1019 -- ***** Start commented code for bug 2678547 **************
1020 -- fl.id_flex_name = 'Grade Flexfield' and
1021 -- ***** End commented code for bug 2678547 **************
1022
1023 -- ***** Start new code for bug 2678547 **************
1024 fl.id_flex_code = 'GRD' and
1025 -- ***** End new code for bug 2678547 **************
1026
1027 fl.application_id = 800 and
1028 s.id_flex_code = fl.id_flex_code and
1029 s.application_id = fl.application_id and
1030 s.id_flex_num = seg.id_flex_num and
1031 seg.id_flex_code = fl.id_flex_code and
1032 seg.application_id = fl.application_id and
1033 seg.id_flex_num =
1034 (
1035 --***** start new code for Bug 5089488**************
1036 select hoi.ORG_INFORMATION4
1037 from hr_organization_units hou,
1038 hr_organization_information hoi,
1039 hr_organization_information hoi2
1040 where hou.business_group_id = p_business_group_id
1041 and hou.organization_id = hoi.organization_id
1042 and hoi.organization_id = hoi2.organization_id
1043 and hoi.org_information_context = 'Business Group Information'
1044 and sysdate between hou.Date_from and nvl(hou.date_to,sysdate)
1045 and hoi2.org_information1='HR_BG' and hoi2.org_information2='Y'
1046 --***** end new code for Bug 5089488**************
1047 ) and
1048 seg.display_flag = 'Y' and
1049 seg.application_column_name = SUBSTR(p_type, 7) and
1050 tl.application_id = seg.application_id and
1051 tl.id_flex_num = seg.id_flex_num and
1052 tl.id_flex_code = seg.id_flex_code and
1053 tl.language = USERENV('LANG') and
1054 tl.application_column_name = seg.application_column_name;
1055
1056 -- ***** End new code for bug 2669059 **************
1057
1058 -- ***** Start commented code for bug 2669059 **************
1059
1060
1061 -- CURSOR csr_get_scl_tl IS
1062 -- SELECT tl.form_left_prompt meaning
1063 -- FROM
1064 -- fnd_id_flex_segments_tl tl
1065 -- , fnd_id_flex_segments seg
1066 -- , fnd_id_flex_structures s
1067 -- , fnd_id_Flexs fl
1068 -- , pay_legislation_rules lr
1069 -- WHERE
1070 -- lr.legislation_code = 'US' AND
1071 -- lr.rule_type = 'S'
1072 -- AND
1073 -- fl.id_flex_name = 'Soft Coded KeyFlexfield'
1074 -- AND
1075 -- s.id_flex_code = fl.id_flex_code AND
1076 -- s.id_flex_num = seg.id_flex_num
1077 -- AND
1078 -- seg.id_flex_code= fl.id_flex_code AND
1079 -- seg.id_flex_num = lr.rule_mode AND
1080 -- seg.display_flag = 'Y'
1081 -- AND
1082 -- seg.application_column_name = SUBSTR(p_type,5)
1083 -- AND
1084 -- tl.application_id = seg.application_id AND
1085 -- tl.id_flex_num = seg.id_flex_num AND
1086 -- tl.id_flex_code = seg.id_flex_code AND
1087 -- tl.application_column_name = seg.application_column_name;
1088 -- ***** End commented code for bug 2669059 **************
1089
1090 -- ***** Start new code for bug 2669059 **************
1091
1092 CURSOR csr_get_scl_tl IS
1093 select tl.form_left_prompt meaning
1094 from
1095 fnd_id_flex_segments_tl tl
1096 , fnd_id_flex_segments seg
1097 , fnd_id_flex_structures s
1098 , fnd_id_Flexs fl
1099 , pay_legislation_rules lr
1100 where
1101 lr.legislation_code = 'US' and
1102 lr.rule_type = 'S' and
1103
1104 -- ***** Start commented code for bug 2678547 **************
1105 -- fl.id_flex_name = 'Soft Coded KeyFlexfield' and
1106 -- ***** End commented code for bug 2678547 **************
1107
1108 -- ***** Start new code for bug 2678547 **************
1109 fl.id_flex_code = 'SCL' and
1110 -- ***** End new code for bug 2678547 **************
1111
1112 fl.application_id = 800 and
1113 s.id_flex_code = fl.id_flex_code and
1114 s.application_id = fl.application_id and
1115 s.id_flex_num = seg.id_flex_num and
1116 seg.id_flex_code = fl.id_flex_code and
1117 seg.application_id = fl.application_id and
1118 seg.id_flex_num = lr.rule_mode and
1119 seg.display_flag = 'Y' and
1120 seg.application_column_name = SUBSTR(p_type,5) and
1121 tl.application_id = seg.application_id and
1122 tl.id_flex_num = seg.id_flex_num and
1123 tl.id_flex_code = seg.id_flex_code and
1124 tl.language = USERENV('LANG') and
1125 tl.application_column_name = seg.application_column_name;
1126
1127 -- ***** End new code for bug 2669059 **************
1128
1129
1130 BEGIN
1131
1132 IF ( SUBSTR( p_type,1,6 ) = 'PEOPLE' )
1133 THEN
1134
1135 OPEN csr_get_people_tl;
1136 FETCH csr_get_people_tl INTO l_meaning;
1137 CLOSE csr_get_people_tl;
1138
1139 ELSIF ( SUBSTR( p_type,1,3 ) = 'SCL' )
1140 THEN
1141
1142 OPEN csr_get_scl_tl;
1143 FETCH csr_get_scl_tl INTO l_meaning;
1144 CLOSE csr_get_scl_tl;
1145
1146 ELSIF ( SUBSTR( p_type,1,5 ) = 'GRADE' )
1147 THEN
1148
1149 OPEN csr_get_grade_tl;
1150 FETCH csr_get_grade_tl INTO l_meaning;
1151 CLOSE csr_get_grade_tl;
1152
1153 END IF;
1154
1155 RETURN l_meaning;
1156
1157
1158 END get_criteria_meaning;
1159
1160 FUNCTION check_flex( p_flex_id in number
1161 , p_segment in VARCHAR2
1162 , p_value in VARCHAR2
1163 , p_type in varchar2
1164 , p_flex_tab IN OUT NOCOPY t_flex_valid) RETURN NUMBER
1165 IS
1166 cursor get_scl_segment_value IS
1167 select SEGMENT1,SEGMENT2,SEGMENT3,SEGMENT4,SEGMENT5,SEGMENT6,SEGMENT7,SEGMENT8,SEGMENT9,SEGMENT10,
1168 SEGMENT11,SEGMENT12,SEGMENT13,SEGMENT14,SEGMENT15,SEGMENT16,SEGMENT17,SEGMENT18,SEGMENT19,SEGMENT20,
1169 SEGMENT21,SEGMENT22,SEGMENT23,SEGMENT24,SEGMENT25,SEGMENT26,SEGMENT27,SEGMENT28,SEGMENT29,SEGMENT30
1170 FROM hr_soft_coding_keyflex scl
1171 WHERE scl.soft_coding_keyflex_id = p_flex_id;
1172
1173
1174 cursor get_people_segment_value IS
1175 select SEGMENT1,SEGMENT2,SEGMENT3,SEGMENT4,SEGMENT5,SEGMENT6,SEGMENT7,SEGMENT8,SEGMENT9,SEGMENT10,
1176 SEGMENT11,SEGMENT12,SEGMENT13,SEGMENT14,SEGMENT15,SEGMENT16,SEGMENT17,SEGMENT18,SEGMENT19,SEGMENT20,
1177 SEGMENT21,SEGMENT22,SEGMENT23,SEGMENT24,SEGMENT25,SEGMENT26,SEGMENT27,SEGMENT28,SEGMENT29,SEGMENT30
1178 FROM pay_people_groups grp
1179 WHERE grp.people_group_id = p_flex_id;
1180
1181 cursor get_grade_segment_value IS
1182 select gd.SEGMENT1,gd.SEGMENT2,gd.SEGMENT3,gd.SEGMENT4,gd.SEGMENT5,gd.SEGMENT6,gd.SEGMENT7,gd.SEGMENT8,gd.SEGMENT9,gd.SEGMENT10,
1183 gd.SEGMENT11,gd.SEGMENT12,gd.SEGMENT13,gd.SEGMENT14,gd.SEGMENT15,gd.SEGMENT16,gd.SEGMENT17,gd.SEGMENT18,gd.SEGMENT19,gd.SEGMENT20,
1184 gd.SEGMENT21,gd.SEGMENT22,gd.SEGMENT23,gd.SEGMENT24,gd.SEGMENT25,gd.SEGMENT26,gd.SEGMENT27,gd.SEGMENT28,gd.SEGMENT29,gd.SEGMENT30
1185 FROM per_grades g,per_grade_definitions gd
1186 WHERE g.grade_id = p_flex_id
1187 AND gd.grade_definition_id = g.grade_definition_id;
1188
1189
1190 BEGIN
1191
1192
1193 if (not p_flex_tab.exists(p_flex_id))
1194 then
1195 --segment does not exist in the cache. let us get it
1196
1197 if p_type = 'SCL' then
1198
1199 OPEN get_scl_segment_value;
1200 FETCH get_scl_segment_value into p_flex_tab(p_flex_id);
1201 CLOSE get_scl_segment_value;
1202
1203 elsif p_type = 'PEOPLE' then
1204
1205 OPEN get_people_segment_value;
1206 FETCH get_people_segment_value into p_flex_tab(p_flex_id);
1207 CLOSE get_people_segment_value;
1208
1209 elsif p_type = 'GRADE' then
1210
1211 OPEN get_grade_segment_value;
1212 FETCH get_grade_segment_value into p_flex_tab(p_flex_id);
1213 CLOSE get_grade_segment_value;
1214
1215 end if;
1216
1217 end if;
1218
1219
1220 if (p_flex_tab.exists(p_flex_id))
1221 then
1222
1223 if (p_segment = 'SEGMENT1' and p_value = p_flex_tab(p_flex_id).segment1) then
1224 return 1;
1225 elsif (p_segment = 'SEGMENT2' and p_value = p_flex_tab(p_flex_id).segment2) then
1226 return 1;
1227 elsif (p_segment = 'SEGMENT3' and p_value = p_flex_tab(p_flex_id).segment3) then
1228 return 1;
1229 elsif (p_segment = 'SEGMENT4' and p_value = p_flex_tab(p_flex_id).segment4) then
1230 return 1;
1231 elsif (p_segment = 'SEGMENT5' and p_value = p_flex_tab(p_flex_id).segment5) then
1232 return 1;
1233 elsif (p_segment = 'SEGMENT6' and p_value = p_flex_tab(p_flex_id).segment6) then
1234 return 1;
1235 elsif (p_segment = 'SEGMENT7' and p_value = p_flex_tab(p_flex_id).segment7) then
1236 return 1;
1237 elsif (p_segment = 'SEGMENT8' and p_value = p_flex_tab(p_flex_id).segment8) then
1238 return 1;
1239 elsif (p_segment = 'SEGMENT9' and p_value = p_flex_tab(p_flex_id).segment9) then
1240 return 1;
1241 elsif (p_segment = 'SEGMENT10' and p_value = p_flex_tab(p_flex_id).segment10) then
1242 return 1;
1243 elsif (p_segment = 'SEGMENT11' and p_value = p_flex_tab(p_flex_id).segment11) then
1244 return 1;
1245 elsif (p_segment = 'SEGMENT12' and p_value = p_flex_tab(p_flex_id).segment12) then
1246 return 1;
1247 elsif (p_segment = 'SEGMENT13' and p_value = p_flex_tab(p_flex_id).segment13) then
1248 return 1;
1249 elsif (p_segment = 'SEGMENT14' and p_value = p_flex_tab(p_flex_id).segment14) then
1250 return 1;
1251 elsif (p_segment = 'SEGMENT15' and p_value = p_flex_tab(p_flex_id).segment15) then
1252 return 1;
1253 elsif (p_segment = 'SEGMENT16' and p_value = p_flex_tab(p_flex_id).segment16) then
1254 return 1;
1255 elsif (p_segment = 'SEGMENT17' and p_value = p_flex_tab(p_flex_id).segment17) then
1256 return 1;
1257 elsif (p_segment = 'SEGMENT18' and p_value = p_flex_tab(p_flex_id).segment18) then
1258 return 1;
1259 elsif (p_segment = 'SEGMENT19' and p_value = p_flex_tab(p_flex_id).segment19) then
1260 return 1;
1261 elsif (p_segment = 'SEGMENT20' and p_value = p_flex_tab(p_flex_id).segment20) then
1262 return 1;
1263 elsif (p_segment = 'SEGMENT21' and p_value = p_flex_tab(p_flex_id).segment21) then
1264 return 1;
1265 elsif (p_segment = 'SEGMENT22' and p_value = p_flex_tab(p_flex_id).segment22) then
1266 return 1;
1267 elsif (p_segment = 'SEGMENT23' and p_value = p_flex_tab(p_flex_id).segment23) then
1268 return 1;
1269 elsif (p_segment = 'SEGMENT24' and p_value = p_flex_tab(p_flex_id).segment24) then
1270 return 1;
1271 elsif (p_segment = 'SEGMENT25' and p_value = p_flex_tab(p_flex_id).segment25) then
1272 return 1;
1273 elsif (p_segment = 'SEGMENT26' and p_value = p_flex_tab(p_flex_id).segment26) then
1274 return 1;
1275 elsif (p_segment = 'SEGMENT27' and p_value = p_flex_tab(p_flex_id).segment27) then
1276 return 1;
1277 elsif (p_segment = 'SEGMENT28' and p_value = p_flex_tab(p_flex_id).segment28) then
1278 return 1;
1279 elsif (p_segment = 'SEGMENT29' and p_value = p_flex_tab(p_flex_id).segment29) then
1280 return 1;
1281 elsif (p_segment = 'SEGMENT30' and p_value = p_flex_tab(p_flex_id).segment30) then
1282 return 1;
1283 else
1284 return 0;
1285 end if;
1286 end if;
1287 return 0;
1288
1289 END check_flex;
1290
1291
1292
1293 FUNCTION chk_flex_valid ( p_type VARCHAR2
1294 , p_flex_id NUMBER
1295 , p_segment VARCHAR2
1296 , p_value VARCHAR2 ) RETURN NUMBER IS
1297
1298 BEGIN
1299
1300 if (p_type = 'SCL') then
1301 return check_flex(p_flex_id,p_segment,p_value,p_type,g_flex_valid_scl_ct);
1302 elsif (p_type = 'PEOPLE') then
1303 return check_flex(p_flex_id,p_segment,p_value,p_type,g_flex_valid_people_ct);
1304 elsif (p_type = 'GRADE') then
1305 return check_flex(p_flex_id,p_segment,p_value,p_type,g_flex_valid_grade_ct);
1306 end if;
1307 END chk_flex_valid;
1308
1309 -- Bug 3322725
1310 FUNCTION chk_criteria_exists ( p_eligibility_criteria_type VARCHAR2,
1311 p_eligibility_criteria_id VARCHAR2) RETURN BOOLEAN IS
1312
1313 l_criteria_exists number;
1314
1315 CURSOR c_chk_resource_rules( p_eligibility_criteria_type varchar,
1316 p_eligibility_criteria_id varchar) IS
1317 SELECT '1' FROM hxc_resource_rules
1318 WHERE eligibility_criteria_type = p_eligibility_criteria_type
1319 and eligibility_criteria_id = p_eligibility_criteria_id;
1320
1321 BEGIN
1322 Open c_chk_resource_rules(p_eligibility_criteria_type,p_eligibility_criteria_id);
1323 Fetch c_chk_resource_rules into l_criteria_exists;
1324 If c_chk_resource_rules%FOUND then
1325 Close c_chk_resource_rules;
1326 return(TRUE);
1327 else
1328 Close c_chk_resource_rules;
1329 return(FALSE);
1330 end if;
1331 END chk_criteria_exists;
1332
1333 END hxc_resource_rules_utils;