[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.4 2009/11/11 06:32:10 amakrish 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 -- Modified DECODE statement for bug 9104542
555 CURSOR csr_get_scl_meaning(p_id_flex_num NUMBER
556 , p_sequence NUMBER
557 , p_max_sequence NUMBER
558 , p_delimiter VARCHAR2
559 , p_type VARCHAR2
560 , p_value VARCHAR2 ) IS
561 SELECT
562 DECODE ( p_sequence,
563 1,
564 REPLACE( SUBSTR( REPLACE(concatenated_segments, '\'||p_delimiter, '\ ' ), 0,DECODE(INSTR ( REPLACE(concatenated_segments, '\'||p_delimiter, '\ ' ),p_delimiter,1,1),0,LENGTH(concatenated_segments),
565 INSTR ( REPLACE(concatenated_segments, '\'||p_delimiter, '\ ' ), p_delimiter, 1,1)-1)), '\ ', p_delimiter),
566 p_max_sequence,
567 REPLACE( SUBSTR( REPLACE(concatenated_segments, '\'||p_delimiter, '\ ' ), INSTR( REPLACE(concatenated_segments, '\'||p_delimiter, '\ ' ), p_delimiter, 1, (p_sequence-1))+1), '\ ', p_delimiter ),
568 REPLACE( SUBSTR( REPLACE(concatenated_segments, '\'||p_delimiter, '\ ' ), (INSTR( REPLACE(concatenated_segments, '\'||p_delimiter, '\ ' ), p_delimiter, 1, (p_sequence-1))+1),
569 ( INSTR( REPLACE(concatenated_segments, '\'||p_delimiter, '\ ' ), p_delimiter, 1, p_sequence)
570 -(INSTR( REPLACE(concatenated_segments, '\'||p_delimiter, '\ ' ), p_delimiter, 1, (p_sequence-1))+1))), '\ ', p_delimiter )
571 ) meaning
572 FROM hr_soft_coding_keyflex
573 WHERE id_flex_num = p_id_flex_num
574 AND DECODE ( SUBSTR( p_type, 5 ),
575 'SEGMENT1', segment1,
576 'SEGMENT2', segment2,
577 'SEGMENT3', segment3,
578 'SEGMENT4', segment4,
579 'SEGMENT5', segment5,
580 'SEGMENT6', segment6,
581 'SEGMENT7', segment7,
582 'SEGMENT8', segment8,
583 'SEGMENT9', segment9,
584 'SEGMENT10', segment10,
585 'SEGMENT11', segment11,
586 'SEGMENT12', segment12,
587 'SEGMENT13', segment13,
588 'SEGMENT14', segment14,
589 'SEGMENT15', segment15,
590 'SEGMENT16', segment16,
591 'SEGMENT17', segment17,
592 'SEGMENT18', segment18,
593 'SEGMENT19', segment19,
594 'SEGMENT20', segment20,
595 'SEGMENT21', segment21,
596 'SEGMENT22', segment22,
597 'SEGMENT23', segment23,
598 'SEGMENT24', segment24,
599 'SEGMENT25', segment25,
600 'SEGMENT26', segment26,
601 'SEGMENT27', segment27,
602 'SEGMENT28', segment28,
603 'SEGMENT29', segment29,
604 'SEGMENT30', segment30, -1 ) = p_value;
605
606
607 -- Modified DECODE statement for bug 9104542
608 CURSOR csr_get_people_meaning(
609 p_id_flex_num NUMBER
610 , p_sequence NUMBER
611 , p_max_sequence NUMBER
612 , p_delimiter VARCHAR2
613 , p_type VARCHAR2
614 , p_value VARCHAR2 ) IS
615 SELECT
616 DECODE ( p_sequence,
617 1,
618 REPLACE( SUBSTR( REPLACE(group_name, '\'||p_delimiter, '\ ' ), 0,DECODE(INSTR ( REPLACE(group_name, '\'||p_delimiter, '\ ' ),p_delimiter,1,1),0,LENGTH(group_name),
619 INSTR ( REPLACE(group_name, '\'||p_delimiter, '\ ' ), p_delimiter, 1,1)-1)), '\ ', p_delimiter),
620 p_max_sequence,
621 REPLACE( SUBSTR( REPLACE(group_name, '\'||p_delimiter, '\ ' ), INSTR( REPLACE(group_name, '\'||p_delimiter, '\ ' ), p_delimiter, 1, (p_sequence-1))+1), '\ ', p_delimiter ),
622 REPLACE( SUBSTR( REPLACE(group_name, '\'||p_delimiter, '\ ' ), (INSTR( REPLACE(group_name, '\'||p_delimiter, '\ ' ), p_delimiter, 1, (p_sequence-1))+1),
623 ( INSTR( REPLACE(group_name, '\'||p_delimiter, '\ ' ), p_delimiter, 1, p_sequence)
624 -(INSTR( REPLACE(group_name, '\'||p_delimiter, '\ ' ), p_delimiter, 1, (p_sequence-1))+1))), '\ ', p_delimiter )
625 ) meaning
626 FROM pay_people_groups
627 WHERE id_flex_num = p_id_flex_num
628 AND DECODE ( SUBSTR( p_type, 8 ),
629 'SEGMENT1', segment1,
630 'SEGMENT2', segment2,
631 'SEGMENT3', segment3,
632 'SEGMENT4', segment4,
633 'SEGMENT5', segment5,
634 'SEGMENT6', segment6,
635 'SEGMENT7', segment7,
636 'SEGMENT8', segment8,
637 'SEGMENT9', segment9,
638 'SEGMENT10', segment10,
639 'SEGMENT11', segment11,
640 'SEGMENT12', segment12,
641 'SEGMENT13', segment13,
642 'SEGMENT14', segment14,
643 'SEGMENT15', segment15,
644 'SEGMENT16', segment16,
645 'SEGMENT17', segment17,
646 'SEGMENT18', segment18,
647 'SEGMENT19', segment19,
648 'SEGMENT20', segment20,
649 'SEGMENT21', segment21,
650 'SEGMENT22', segment22,
651 'SEGMENT23', segment23,
652 'SEGMENT24', segment24,
653 'SEGMENT25', segment25,
654 'SEGMENT26', segment26,
655 'SEGMENT27', segment27,
656 'SEGMENT28', segment28,
657 'SEGMENT29', segment29,
658 'SEGMENT30', segment30, -1 ) = p_value;
659
660
661 -- Modified DECODE statement for bug 9104542
662 CURSOR csr_get_grade_meaning(
663 p_id_flex_num NUMBER
664 , p_sequence NUMBER
665 , p_max_sequence NUMBER
666 , p_delimiter VARCHAR2
667 , p_type VARCHAR2
668 , p_value VARCHAR2 ) IS
669 SELECT
670 DECODE ( p_sequence,
671 1,
672 REPLACE( SUBSTR( REPLACE(g.name, '\'||p_delimiter, '\ '), 0,DECODE(INSTR ( REPLACE(g.name, '\'||p_delimiter, '\ '),p_delimiter,1,1),0,LENGTH(g.name), INSTR ( REPLACE(g.name, '\'||p_delimiter, '\ '), p_delimiter, 1,1)-1)), '\ ', p_delimiter),
673 p_max_sequence,
674 REPLACE( SUBSTR( REPLACE(g.name, '\'||p_delimiter, '\ '), INSTR( REPLACE(g.name, '\'||p_delimiter, '\ '), p_delimiter, 1, (p_sequence-1))+1), '\ ', p_delimiter),
675 REPLACE( SUBSTR( REPLACE(g.name, '\'||p_delimiter, '\ '), (INSTR( REPLACE(g.name, '\'||p_delimiter, '\ '), p_delimiter, 1, (p_sequence-1))+1),
676 ( INSTR( REPLACE(g.name, '\'||p_delimiter, '\ '), p_delimiter, 1, p_sequence)
677 -(INSTR( REPLACE(g.name, '\'||p_delimiter, '\ '), p_delimiter, 1, (p_sequence-1))+1))), '\ ', p_delimiter)
678 ) meaning
679 FROM per_grades_vl g
680 , per_grade_definitions gd
681 WHERE gd.id_flex_num = p_id_flex_num
682 AND gd.grade_definition_id = g.grade_definition_id
683 AND DECODE ( SUBSTR( p_type, 7 ),
684 'SEGMENT1', gd.segment1,
685 'SEGMENT2', gd.segment2,
686 'SEGMENT3', gd.segment3,
687 'SEGMENT4', gd.segment4,
688 'SEGMENT5', gd.segment5,
689 'SEGMENT6', gd.segment6,
690 'SEGMENT7', gd.segment7,
691 'SEGMENT8', gd.segment8,
692 'SEGMENT9', gd.segment9,
693 'SEGMENT10', gd.segment10,
694 'SEGMENT11', gd.segment11,
695 'SEGMENT12', gd.segment12,
696 'SEGMENT13', gd.segment13,
697 'SEGMENT14', gd.segment14,
698 'SEGMENT15', gd.segment15,
699 'SEGMENT16', gd.segment16,
700 'SEGMENT17', gd.segment17,
701 'SEGMENT18', gd.segment18,
702 'SEGMENT19', gd.segment19,
703 'SEGMENT20', gd.segment20,
704 'SEGMENT21', gd.segment21,
705 'SEGMENT22', gd.segment22,
706 'SEGMENT23', gd.segment23,
707 'SEGMENT24', gd.segment24,
708 'SEGMENT25', gd.segment25,
709 'SEGMENT26', gd.segment26,
710 'SEGMENT27', gd.segment27,
711 'SEGMENT28', gd.segment28,
712 'SEGMENT29', gd.segment29,
713 'SEGMENT30', gd.segment30, -1 ) = p_value;
714
715 BEGIN
716
717
718
719 IF ( SUBSTR( p_type,1,3) = 'SCL' )
720 THEN
721 if g_debug then
722 hr_utility.set_location('gaz - num of segs is '||to_Char(hxc_resource_rules_utils.g_scl_num_of_segs), 999);
723 end if;
724 IF ( hxc_resource_rules_utils.g_scl_num_of_segs IS NULL )
725 THEN
726 OPEN csr_get_scl_segs;
727 FETCH csr_get_scl_segs
728 INTO hxc_resource_rules_utils.g_scl_num_of_segs
729 , hxc_resource_rules_utils.g_scl_delimiter
730 , hxc_resource_rules_utils.g_scl_id_flex_num;
731 CLOSE csr_get_scl_segs;
732
733 END IF;
734 if g_debug then
735 hr_utility.set_location('gaz - num of segs is '||to_Char(hxc_resource_rules_utils.g_scl_num_of_segs), 999);
736 end if;
737
738 -- get sequence
739
740 l_sequence := hxc_resource_rules_utils.get_sequence ( p_type );
741
742 -- get value
743
744 if g_debug then
745 hr_utility.set_location('gaz - params are ', 999);
746 hr_utility.set_location('gaz - p id_flex_num '||to_char(hxc_resource_rules_utils.g_scl_id_flex_num) , 999);
747 hr_utility.set_location('gaz - p sequence '||to_char(l_Sequence) , 999);
748 hr_utility.set_location('gaz - p max sequence '||to_char(hxc_resource_rules_utils.g_scl_num_of_segs) , 999);
749 hr_utility.set_location('gaz - p delim '||hxc_resource_rules_utils.g_scl_delimiter , 999);
750 hr_utility.set_location('gaz - p type '||p_type , 999);
751 hr_utility.set_location('gaz - p value '||p_value , 999);
752 end if;
753
754 FOR scl IN csr_get_scl_meaning (
755 p_id_flex_num => hxc_resource_rules_utils.g_scl_id_flex_num
756 , p_sequence => l_sequence
757 , p_max_sequence=> hxc_resource_rules_utils.g_scl_num_of_segs
758 , p_delimiter => hxc_resource_rules_utils.g_scl_delimiter
759 , p_type => p_type
760 , p_value => p_value )
761 LOOP
762
763 IF scl.meaning IS NOT NULL
764 THEN
765 l_meaning := scl.meaning;
766 EXIT;
767 END IF;
768
769 END LOOP;
770
771 ELSIF ( SUBSTR( p_type,1,6) = 'PEOPLE' )
772 THEN
773 if g_debug then
774 hr_utility.set_location('gaz - num of segs is '||to_Char(hxc_resource_rules_utils.g_people_num_of_segs), 999);
775 end if;
776 IF ( hxc_resource_rules_utils.g_people_num_of_segs IS NULL )
777 THEN
778 OPEN csr_get_people_segs;
779 FETCH csr_get_people_segs
780 INTO hxc_resource_rules_utils.g_people_num_of_segs
781 , hxc_resource_rules_utils.g_people_delimiter
782 , hxc_resource_rules_utils.g_people_id_flex_num;
783 CLOSE csr_get_people_segs;
784
785 END IF;
786 if g_debug then
787 hr_utility.set_location('gaz - num of segs is '||to_Char(hxc_resource_rules_utils.g_people_num_of_segs), 999);
788 end if;
789
790 -- get sequence
791
792 l_sequence := hxc_resource_rules_utils.get_sequence ( p_type, hxc_resource_rules_utils.g_people_id_flex_num );
793
794 -- get value
795
796 if g_debug then
797 hr_utility.set_location('gaz - params are ', 999);
798 hr_utility.set_location('gaz - p id_flex_num '||to_char(hxc_resource_rules_utils.g_people_id_flex_num) , 999);
799 hr_utility.set_location('gaz - l sequence '||to_char(l_Sequence) , 999);
800 hr_utility.set_location('gaz - p max sequence '||to_char(hxc_resource_rules_utils.g_people_num_of_segs) , 999);
801 hr_utility.set_location('gaz - p delim '||hxc_resource_rules_utils.g_people_delimiter , 999);
802 hr_utility.set_location('gaz - p type '||p_type , 999);
803 hr_utility.set_location('gaz - p value '||p_value , 999);
804 end if;
805
806 FOR people IN csr_get_people_meaning (
807 p_id_flex_num => hxc_resource_rules_utils.g_people_id_flex_num
808 , p_sequence => l_sequence
809 , p_max_sequence=> hxc_resource_rules_utils.g_people_num_of_segs
810 , p_delimiter => hxc_resource_rules_utils.g_people_delimiter
811 , p_type => p_type
812 , p_value => p_value )
813 LOOP
814
815 IF people.meaning IS NOT NULL
816 THEN
817 l_meaning := people.meaning;
818 EXIT;
819 END IF;
820
821 END LOOP;
822
823 if g_debug then
824 hr_utility.set_location('gaz - meaning is '||l_meaning, 999);
825 end if;
826
827 ELSIF ( SUBSTR( p_type,1,5) = 'GRADE' )
828 THEN
829 if g_debug then
830 hr_utility.set_location('gaz - num of segs is '||to_Char(hxc_resource_rules_utils.g_grade_num_of_segs), 999);
831 end if;
832 IF ( hxc_resource_rules_utils.g_grade_num_of_segs IS NULL )
833 THEN
834 OPEN csr_get_grade_segs;
835 FETCH csr_get_grade_segs
836 INTO hxc_resource_rules_utils.g_grade_num_of_segs
837 , hxc_resource_rules_utils.g_grade_delimiter
838 , hxc_resource_rules_utils.g_grade_id_flex_num;
839 CLOSE csr_get_grade_segs;
840
841 END IF;
842 if g_debug then
843 hr_utility.set_location('gaz - num of segs is '||to_Char(hxc_resource_rules_utils.g_grade_num_of_segs), 999);
844 end if;
845
846 -- get sequence
847
848 l_sequence := hxc_resource_rules_utils.get_sequence ( p_type, hxc_resource_rules_utils.g_grade_id_flex_num );
849
850 -- get value
851
852 if g_debug then
853 hr_utility.set_location('gaz - params are ', 999);
854 hr_utility.set_location('gaz - p id_flex_num '||to_char(hxc_resource_rules_utils.g_grade_id_flex_num) , 999);
855 hr_utility.set_location('gaz - l sequence '||to_char(l_Sequence) , 999);
856 hr_utility.set_location('gaz - p max sequence '||to_char(hxc_resource_rules_utils.g_grade_num_of_segs) , 999);
857 hr_utility.set_location('gaz - p delim '||hxc_resource_rules_utils.g_grade_delimiter , 999);
858 hr_utility.set_location('gaz - p type '||p_type , 999);
859 hr_utility.set_location('gaz - p value '||p_value , 999);
860 end if;
861
862 FOR grade IN csr_get_grade_meaning (
863 p_id_flex_num => hxc_resource_rules_utils.g_grade_id_flex_num
864 , p_sequence => l_sequence
865 , p_max_sequence=> hxc_resource_rules_utils.g_grade_num_of_segs
866 , p_delimiter => hxc_resource_rules_utils.g_grade_delimiter
867 , p_type => p_type
868 , p_value => p_value )
869 LOOP
870
871 IF grade.meaning is not null
872 THEN
873 l_meaning := grade.meaning;
874 if g_debug then
875 hr_utility.set_location('gaz - meaning is '||l_meaning, 999);
876 end if;
877 exit;
878 END IF;
879
880 END LOOP;
881
882 END IF;
883
884 RETURN l_meaning;
885
886 END get_meaning;
887
888 FUNCTION get_criteria_meaning ( p_type varchar2
889 , p_business_group_id number ) RETURN VARCHAR2 IS
890
891 l_meaning varchar2(240);
892
893 -- ***** Start commented code for bug 2669059 **************
894
895 -- CURSOR csr_get_people_tl IS
896 -- SELECT
897 -- tl.form_left_prompt meaning
898 -- FROM
899 -- fnd_id_flex_segments_tl tl
900 -- , fnd_id_flex_segments seg
901 -- , fnd_id_flex_structures s
902 -- , fnd_id_Flexs fl
903 -- WHERE
904 -- fl.id_flex_name = 'People Group Flexfield'
905 -- AND
906 -- s.id_flex_code = fl.id_flex_code AND
907 -- s.id_flex_num = seg.id_flex_num
908 -- AND
909 -- seg.id_flex_code= fl.id_flex_code AND
910 -- seg.id_flex_num = (
911 -- SELECT DISTINCT z.id_flex_num
912 -- FROM per_all_assignments_f asg
913 -- , pay_people_groups z
914 -- WHERE asg.people_group_id = z.people_group_id
915 -- AND asg.business_group_id = p_business_group_id ) AND
916 -- seg.display_flag = 'Y'
917 -- AND
918 -- seg.application_column_name = SUBSTR(p_type, 8)
919 -- AND
920 -- tl.application_id = seg.application_id AND
921 -- tl.id_flex_num = seg.id_flex_num AND
922 -- tl.id_flex_code = seg.id_flex_code AND
923 -- tl.application_column_name = seg.application_column_name;
924 -- ***** End commented code for bug 2669059 **************
925
926 -- ***** Start new code for bug 2669059 **************
927
928
929 CURSOR csr_get_people_tl IS
930 SELECT
931 tl.form_left_prompt meaning
932 FROM
933 fnd_id_flex_segments_tl tl
934 , fnd_id_flex_segments seg
935 , fnd_id_flex_structures s
936 , fnd_id_Flexs fl
937 WHERE
938 -- ***** Start commented code for bug 2678547 **************
939 -- fl.id_flex_name = 'People Group Flexfield' and
940 -- ***** End commented code for bug 2678547 **************
941
942 -- ***** Start new code for bug 2678547 **************
943 fl.id_flex_code = 'GRP' and
944 -- ***** End new code for bug 2678547 **************
945
946 fl.application_id = 801 AND
947 s.id_flex_code = fl.id_flex_code AND
948 s.application_id = fl.application_id and
949 s.id_flex_num = seg.id_flex_num AND
950 seg.id_flex_code = fl.id_flex_code AND
951 seg.application_id = fl.application_id AND
952 seg.id_flex_num =
953 (
954 --***** start new code for Bug 5089488**************
955 select hoi.ORG_INFORMATION5
956 from hr_organization_units hou,
957 hr_organization_information hoi,
958 hr_organization_information hoi2
959 where hou.business_group_id = p_business_group_id
960 and hou.organization_id = hoi.organization_id
961 and hoi.organization_id = hoi2.organization_id
962 and hoi.org_information_context = 'Business Group Information'
963 and sysdate between hou.Date_from and nvl(hou.date_to,sysdate)
964 and hoi2.org_information1='HR_BG' and hoi2.org_information2='Y'
965 --***** end new code for Bug 5089488**************
966 )
967 and
968 seg.display_flag = 'Y' and
969 seg.application_column_name = SUBSTR(p_type, 8) and
970 tl.application_id = seg.application_id and
971 tl.id_flex_num = seg.id_flex_num and
972 tl.id_flex_code = seg.id_flex_code and
973 tl.language = USERENV('LANG') and
974 tl.application_column_name = seg.application_column_name;
975
976 -- ***** End new code for bug 2669059 **************
977
978
979 -- ***** Start commented code for bug 2669059 **************
980
981 -- CURSOR csr_get_grade_tl IS
982 -- SELECT
983 -- tl.form_left_prompt meaning
984 -- FROM
985 -- fnd_id_flex_segments_tl tl
986 -- , fnd_id_flex_segments seg
987 -- , fnd_id_flex_structures s
988 -- , fnd_id_Flexs fl
989 -- WHERE
990 -- fl.id_flex_name = 'Grade Flexfield'
991 -- AND
992 -- s.id_flex_code = fl.id_flex_code AND
993 -- s.id_flex_num = seg.id_flex_num
994 -- AND
995 -- seg.id_flex_code= fl.id_flex_code AND
996 -- seg.id_flex_num = (
997 -- SELECT DISTINCT z.id_flex_num
998 -- FROM per_grade_definitions z
999 -- , per_grades y
1000 -- WHERE y.business_group_id = p_business_group_id
1001 -- AND y.grade_definition_id = z.grade_definition_id ) AND
1002 -- seg.display_flag = 'Y'
1003 -- AND
1004 -- seg.application_column_name = SUBSTR(p_type, 7)
1005 -- AND
1006 -- tl.application_id = seg.application_id AND
1007 -- tl.id_flex_num = seg.id_flex_num AND
1008 -- tl.id_flex_code = seg.id_flex_code AND
1009 -- tl.application_column_name = seg.application_column_name;
1010
1011 -- ***** End commented code for bug 2669059 **************
1012
1013
1014 -- ***** Start new code for bug 2669059 **************
1015
1016
1017 CURSOR csr_get_grade_tl IS
1018 select
1019 tl.form_left_prompt meaning
1020 from
1021 fnd_id_flex_segments_tl tl
1022 , fnd_id_flex_segments seg
1023 , fnd_id_flex_structures s
1024 , fnd_id_Flexs fl
1025 where
1026 -- ***** Start commented code for bug 2678547 **************
1027 -- fl.id_flex_name = 'Grade Flexfield' and
1028 -- ***** End commented code for bug 2678547 **************
1029
1030 -- ***** Start new code for bug 2678547 **************
1031 fl.id_flex_code = 'GRD' and
1032 -- ***** End new code for bug 2678547 **************
1033
1034 fl.application_id = 800 and
1035 s.id_flex_code = fl.id_flex_code and
1036 s.application_id = fl.application_id and
1037 s.id_flex_num = seg.id_flex_num and
1038 seg.id_flex_code = fl.id_flex_code and
1039 seg.application_id = fl.application_id and
1040 seg.id_flex_num =
1041 (
1042 --***** start new code for Bug 5089488**************
1043 select hoi.ORG_INFORMATION4
1044 from hr_organization_units hou,
1045 hr_organization_information hoi,
1046 hr_organization_information hoi2
1047 where hou.business_group_id = p_business_group_id
1048 and hou.organization_id = hoi.organization_id
1049 and hoi.organization_id = hoi2.organization_id
1050 and hoi.org_information_context = 'Business Group Information'
1051 and sysdate between hou.Date_from and nvl(hou.date_to,sysdate)
1052 and hoi2.org_information1='HR_BG' and hoi2.org_information2='Y'
1053 --***** end new code for Bug 5089488**************
1054 ) and
1055 seg.display_flag = 'Y' and
1056 seg.application_column_name = SUBSTR(p_type, 7) and
1057 tl.application_id = seg.application_id and
1058 tl.id_flex_num = seg.id_flex_num and
1059 tl.id_flex_code = seg.id_flex_code and
1060 tl.language = USERENV('LANG') and
1061 tl.application_column_name = seg.application_column_name;
1062
1063 -- ***** End new code for bug 2669059 **************
1064
1065 -- ***** Start commented code for bug 2669059 **************
1066
1067
1068 -- CURSOR csr_get_scl_tl IS
1069 -- SELECT tl.form_left_prompt meaning
1070 -- FROM
1071 -- fnd_id_flex_segments_tl tl
1072 -- , fnd_id_flex_segments seg
1073 -- , fnd_id_flex_structures s
1074 -- , fnd_id_Flexs fl
1075 -- , pay_legislation_rules lr
1076 -- WHERE
1077 -- lr.legislation_code = 'US' AND
1078 -- lr.rule_type = 'S'
1079 -- AND
1080 -- fl.id_flex_name = 'Soft Coded KeyFlexfield'
1081 -- AND
1082 -- s.id_flex_code = fl.id_flex_code AND
1083 -- s.id_flex_num = seg.id_flex_num
1084 -- AND
1085 -- seg.id_flex_code= fl.id_flex_code AND
1086 -- seg.id_flex_num = lr.rule_mode AND
1087 -- seg.display_flag = 'Y'
1088 -- AND
1089 -- seg.application_column_name = SUBSTR(p_type,5)
1090 -- AND
1091 -- tl.application_id = seg.application_id AND
1092 -- tl.id_flex_num = seg.id_flex_num AND
1093 -- tl.id_flex_code = seg.id_flex_code AND
1094 -- tl.application_column_name = seg.application_column_name;
1095 -- ***** End commented code for bug 2669059 **************
1096
1097 -- ***** Start new code for bug 2669059 **************
1098
1099 CURSOR csr_get_scl_tl IS
1100 select tl.form_left_prompt meaning
1101 from
1102 fnd_id_flex_segments_tl tl
1103 , fnd_id_flex_segments seg
1104 , fnd_id_flex_structures s
1105 , fnd_id_Flexs fl
1106 , pay_legislation_rules lr
1107 where
1108 lr.legislation_code = 'US' and
1109 lr.rule_type = 'S' and
1110
1111 -- ***** Start commented code for bug 2678547 **************
1112 -- fl.id_flex_name = 'Soft Coded KeyFlexfield' and
1113 -- ***** End commented code for bug 2678547 **************
1114
1115 -- ***** Start new code for bug 2678547 **************
1116 fl.id_flex_code = 'SCL' and
1117 -- ***** End new code for bug 2678547 **************
1118
1119 fl.application_id = 800 and
1120 s.id_flex_code = fl.id_flex_code and
1121 s.application_id = fl.application_id and
1122 s.id_flex_num = seg.id_flex_num and
1123 seg.id_flex_code = fl.id_flex_code and
1124 seg.application_id = fl.application_id and
1125 seg.id_flex_num = lr.rule_mode and
1126 seg.display_flag = 'Y' and
1127 seg.application_column_name = SUBSTR(p_type,5) and
1128 tl.application_id = seg.application_id and
1129 tl.id_flex_num = seg.id_flex_num and
1130 tl.id_flex_code = seg.id_flex_code and
1131 tl.language = USERENV('LANG') and
1132 tl.application_column_name = seg.application_column_name;
1133
1134 -- ***** End new code for bug 2669059 **************
1135
1136
1137 BEGIN
1138
1139 IF ( SUBSTR( p_type,1,6 ) = 'PEOPLE' )
1140 THEN
1141
1142 OPEN csr_get_people_tl;
1143 FETCH csr_get_people_tl INTO l_meaning;
1144 CLOSE csr_get_people_tl;
1145
1146 ELSIF ( SUBSTR( p_type,1,3 ) = 'SCL' )
1147 THEN
1148
1149 OPEN csr_get_scl_tl;
1150 FETCH csr_get_scl_tl INTO l_meaning;
1151 CLOSE csr_get_scl_tl;
1152
1153 ELSIF ( SUBSTR( p_type,1,5 ) = 'GRADE' )
1154 THEN
1155
1156 OPEN csr_get_grade_tl;
1157 FETCH csr_get_grade_tl INTO l_meaning;
1158 CLOSE csr_get_grade_tl;
1159
1160 END IF;
1161
1162 RETURN l_meaning;
1163
1164
1165 END get_criteria_meaning;
1166
1167 FUNCTION check_flex( p_flex_id in number
1168 , p_segment in VARCHAR2
1169 , p_value in VARCHAR2
1170 , p_type in varchar2
1171 , p_flex_tab IN OUT NOCOPY t_flex_valid) RETURN NUMBER
1172 IS
1173 cursor get_scl_segment_value IS
1174 select SEGMENT1,SEGMENT2,SEGMENT3,SEGMENT4,SEGMENT5,SEGMENT6,SEGMENT7,SEGMENT8,SEGMENT9,SEGMENT10,
1175 SEGMENT11,SEGMENT12,SEGMENT13,SEGMENT14,SEGMENT15,SEGMENT16,SEGMENT17,SEGMENT18,SEGMENT19,SEGMENT20,
1176 SEGMENT21,SEGMENT22,SEGMENT23,SEGMENT24,SEGMENT25,SEGMENT26,SEGMENT27,SEGMENT28,SEGMENT29,SEGMENT30
1177 FROM hr_soft_coding_keyflex scl
1178 WHERE scl.soft_coding_keyflex_id = p_flex_id;
1179
1180
1181 cursor get_people_segment_value IS
1182 select SEGMENT1,SEGMENT2,SEGMENT3,SEGMENT4,SEGMENT5,SEGMENT6,SEGMENT7,SEGMENT8,SEGMENT9,SEGMENT10,
1183 SEGMENT11,SEGMENT12,SEGMENT13,SEGMENT14,SEGMENT15,SEGMENT16,SEGMENT17,SEGMENT18,SEGMENT19,SEGMENT20,
1184 SEGMENT21,SEGMENT22,SEGMENT23,SEGMENT24,SEGMENT25,SEGMENT26,SEGMENT27,SEGMENT28,SEGMENT29,SEGMENT30
1185 FROM pay_people_groups grp
1186 WHERE grp.people_group_id = p_flex_id;
1187
1188 cursor get_grade_segment_value IS
1189 select gd.SEGMENT1,gd.SEGMENT2,gd.SEGMENT3,gd.SEGMENT4,gd.SEGMENT5,gd.SEGMENT6,gd.SEGMENT7,gd.SEGMENT8,gd.SEGMENT9,gd.SEGMENT10,
1190 gd.SEGMENT11,gd.SEGMENT12,gd.SEGMENT13,gd.SEGMENT14,gd.SEGMENT15,gd.SEGMENT16,gd.SEGMENT17,gd.SEGMENT18,gd.SEGMENT19,gd.SEGMENT20,
1191 gd.SEGMENT21,gd.SEGMENT22,gd.SEGMENT23,gd.SEGMENT24,gd.SEGMENT25,gd.SEGMENT26,gd.SEGMENT27,gd.SEGMENT28,gd.SEGMENT29,gd.SEGMENT30
1192 FROM per_grades g,per_grade_definitions gd
1193 WHERE g.grade_id = p_flex_id
1194 AND gd.grade_definition_id = g.grade_definition_id;
1195
1196
1197 BEGIN
1198
1199
1200 if (not p_flex_tab.exists(p_flex_id))
1201 then
1202 --segment does not exist in the cache. let us get it
1203
1204 if p_type = 'SCL' then
1205
1206 OPEN get_scl_segment_value;
1207 FETCH get_scl_segment_value into p_flex_tab(p_flex_id);
1208 CLOSE get_scl_segment_value;
1209
1210 elsif p_type = 'PEOPLE' then
1211
1212 OPEN get_people_segment_value;
1213 FETCH get_people_segment_value into p_flex_tab(p_flex_id);
1214 CLOSE get_people_segment_value;
1215
1216 elsif p_type = 'GRADE' then
1217
1218 OPEN get_grade_segment_value;
1219 FETCH get_grade_segment_value into p_flex_tab(p_flex_id);
1220 CLOSE get_grade_segment_value;
1221
1222 end if;
1223
1224 end if;
1225
1226
1227 if (p_flex_tab.exists(p_flex_id))
1228 then
1229
1230 if (p_segment = 'SEGMENT1' and p_value = p_flex_tab(p_flex_id).segment1) then
1231 return 1;
1232 elsif (p_segment = 'SEGMENT2' and p_value = p_flex_tab(p_flex_id).segment2) then
1233 return 1;
1234 elsif (p_segment = 'SEGMENT3' and p_value = p_flex_tab(p_flex_id).segment3) then
1235 return 1;
1236 elsif (p_segment = 'SEGMENT4' and p_value = p_flex_tab(p_flex_id).segment4) then
1237 return 1;
1238 elsif (p_segment = 'SEGMENT5' and p_value = p_flex_tab(p_flex_id).segment5) then
1239 return 1;
1240 elsif (p_segment = 'SEGMENT6' and p_value = p_flex_tab(p_flex_id).segment6) then
1241 return 1;
1242 elsif (p_segment = 'SEGMENT7' and p_value = p_flex_tab(p_flex_id).segment7) then
1243 return 1;
1244 elsif (p_segment = 'SEGMENT8' and p_value = p_flex_tab(p_flex_id).segment8) then
1245 return 1;
1246 elsif (p_segment = 'SEGMENT9' and p_value = p_flex_tab(p_flex_id).segment9) then
1247 return 1;
1248 elsif (p_segment = 'SEGMENT10' and p_value = p_flex_tab(p_flex_id).segment10) then
1249 return 1;
1250 elsif (p_segment = 'SEGMENT11' and p_value = p_flex_tab(p_flex_id).segment11) then
1251 return 1;
1252 elsif (p_segment = 'SEGMENT12' and p_value = p_flex_tab(p_flex_id).segment12) then
1253 return 1;
1254 elsif (p_segment = 'SEGMENT13' and p_value = p_flex_tab(p_flex_id).segment13) then
1255 return 1;
1256 elsif (p_segment = 'SEGMENT14' and p_value = p_flex_tab(p_flex_id).segment14) then
1257 return 1;
1258 elsif (p_segment = 'SEGMENT15' and p_value = p_flex_tab(p_flex_id).segment15) then
1259 return 1;
1260 elsif (p_segment = 'SEGMENT16' and p_value = p_flex_tab(p_flex_id).segment16) then
1261 return 1;
1262 elsif (p_segment = 'SEGMENT17' and p_value = p_flex_tab(p_flex_id).segment17) then
1263 return 1;
1264 elsif (p_segment = 'SEGMENT18' and p_value = p_flex_tab(p_flex_id).segment18) then
1265 return 1;
1266 elsif (p_segment = 'SEGMENT19' and p_value = p_flex_tab(p_flex_id).segment19) then
1267 return 1;
1268 elsif (p_segment = 'SEGMENT20' and p_value = p_flex_tab(p_flex_id).segment20) then
1269 return 1;
1270 elsif (p_segment = 'SEGMENT21' and p_value = p_flex_tab(p_flex_id).segment21) then
1271 return 1;
1272 elsif (p_segment = 'SEGMENT22' and p_value = p_flex_tab(p_flex_id).segment22) then
1273 return 1;
1274 elsif (p_segment = 'SEGMENT23' and p_value = p_flex_tab(p_flex_id).segment23) then
1275 return 1;
1276 elsif (p_segment = 'SEGMENT24' and p_value = p_flex_tab(p_flex_id).segment24) then
1277 return 1;
1278 elsif (p_segment = 'SEGMENT25' and p_value = p_flex_tab(p_flex_id).segment25) then
1279 return 1;
1280 elsif (p_segment = 'SEGMENT26' and p_value = p_flex_tab(p_flex_id).segment26) then
1281 return 1;
1282 elsif (p_segment = 'SEGMENT27' and p_value = p_flex_tab(p_flex_id).segment27) then
1283 return 1;
1284 elsif (p_segment = 'SEGMENT28' and p_value = p_flex_tab(p_flex_id).segment28) then
1285 return 1;
1286 elsif (p_segment = 'SEGMENT29' and p_value = p_flex_tab(p_flex_id).segment29) then
1287 return 1;
1288 elsif (p_segment = 'SEGMENT30' and p_value = p_flex_tab(p_flex_id).segment30) then
1289 return 1;
1290 else
1291 return 0;
1292 end if;
1293 end if;
1294 return 0;
1295
1296 END check_flex;
1297
1298
1299
1300 FUNCTION chk_flex_valid ( p_type VARCHAR2
1301 , p_flex_id NUMBER
1302 , p_segment VARCHAR2
1303 , p_value VARCHAR2 ) RETURN NUMBER IS
1304
1305 BEGIN
1306
1307 if (p_type = 'SCL') then
1308 return check_flex(p_flex_id,p_segment,p_value,p_type,g_flex_valid_scl_ct);
1309 elsif (p_type = 'PEOPLE') then
1310 return check_flex(p_flex_id,p_segment,p_value,p_type,g_flex_valid_people_ct);
1311 elsif (p_type = 'GRADE') then
1312 return check_flex(p_flex_id,p_segment,p_value,p_type,g_flex_valid_grade_ct);
1313 end if;
1314 END chk_flex_valid;
1315
1316 -- Bug 3322725
1317 FUNCTION chk_criteria_exists ( p_eligibility_criteria_type VARCHAR2,
1318 p_eligibility_criteria_id VARCHAR2) RETURN BOOLEAN IS
1319
1320 l_criteria_exists number;
1321
1322 CURSOR c_chk_resource_rules( p_eligibility_criteria_type varchar,
1323 p_eligibility_criteria_id varchar) IS
1324 SELECT '1' FROM hxc_resource_rules
1325 WHERE eligibility_criteria_type = p_eligibility_criteria_type
1326 and eligibility_criteria_id = p_eligibility_criteria_id;
1327
1328 BEGIN
1329 Open c_chk_resource_rules(p_eligibility_criteria_type,p_eligibility_criteria_id);
1330 Fetch c_chk_resource_rules into l_criteria_exists;
1331 If c_chk_resource_rules%FOUND then
1332 Close c_chk_resource_rules;
1333 return(TRUE);
1334 else
1335 Close c_chk_resource_rules;
1336 return(FALSE);
1337 end if;
1338 END chk_criteria_exists;
1339
1340 END hxc_resource_rules_utils;