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:
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;
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
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:
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;
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
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:
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
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);
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;
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:
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
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
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:
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;
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
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:
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;
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
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:
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
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);
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;
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:
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
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
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:
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;
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
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:
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;
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
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:
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
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);
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;
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:
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
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
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:
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
1336: return(FALSE);
1337: end if;
1338: END chk_criteria_exists;
1339:
1340: END hxc_resource_rules_utils;