1 Package Body PQH_DE_GRADE_COMPUTE As
2 /* $Header: pqhgrdco.pkb 115.10 2002/12/10 06:10:00 vevenkat noship $ */
3
4 Procedure PQH_DE_WC_COMPUTE
5 (p_Wrkplc_Vldtn_Ver_Id In Number,
6 P_Business_Group_Id In Number,
7 P_WC_Grade Out NOCOPY Varchar2,
8 P_Wc_Case_group Out NOCOPY Varchar2) is
9
10 Cursor Jobftr_Unitperc is
11 Select Sum(Nvl(Unit_Percentage,0)) Untperc , Job_Feature_Code
12 from PQH_DE_WRKPLC_VLDTN_OPS a, PQH_DE_WRKPLC_VLDTN_Jobftrs b Where
13 WRKPLC_VLDTN_VER_ID = p_Wrkplc_Vldtn_Ver_Id and
14 a.Business_group_Id = p_Business_Group_Id and
15 a.WRKPLC_VLDTN_OP_ID = WRKPLC_VLDTN_OPR_JOB_ID and
16 WRKPLC_VLDTN_OPR_JOB_TYPE = 'O' and
17 a.Business_group_Id = b.Business_Group_Id
18 Group By Job_Feature_Code
19 Order By To_Number(Job_Feature_Code) Desc;
20
21 Cursor Case_group_Algor(p_Entity_Id In Varchar2) is
22 Select Entity_Id, PARENT_HIERARCHY_NODE_ID, Nvl(Information2,0) Perc, Hierarchy_Version_Id
23 from Per_gen_Hierarchy_Nodes
24 Where Node_type = 'JOB_FTR' and
25 Entity_Id = p_Entity_Id and
26 Hierarchy_Version_Id in
27 (Select HIERARCHY_VERSION_ID from
28 Per_gen_Hierarchy_Versions
29 where Trunc(Sysdate) between Date_From and Nvl(date_To,Sysdate)
30 and Hierarchy_Id in
31 (Select Hierarchy_Id from
32 Per_Gen_Hierarchy
33 Where type = 'REMUNERATION_REGULATION' and Business_Group_id=p_Business_Group_id))
34 Order By Perc Desc;
35
36 Cursor Grade(p_Hierarchy_Node_id In Varchar2, p_Hierarchy_Version_Id In Number) is
37 Select a.Entity_Id, b.Entity_Id from
38 Per_gen_Hierarchy_Nodes b, Per_gen_Hierarchy_Nodes a
39 Where a.Node_type = 'CASE_GROUP'
40 and a.Hierarchy_Node_id = p_Hierarchy_Node_id
41 and a.Hierarchy_Version_Id = p_Hierarchy_Version_Id
42 and b.HIERARCHY_NODE_ID = a.PARENT_HIERARCHY_NODE_ID
43 and b.Hierarchy_Version_Id = a.Hierarchy_Version_Id;
44
45 l_Grade_Status Varchar2(1) := 'N';
46 l_UntPerct Pqh_de_wrkplc_Vldtn_Ops.Unit_Percentage%TYPE;
47 Begin
48 P_WC_Grade := -4;
49 Select Nvl(Sum(Unit_Percentage),0) into l_UntPerct
50 From Pqh_de_wrkplc_Vldtn_Ops
51 Where Wrkplc_Vldtn_Ver_Id = p_Wrkplc_Vldtn_Ver_Id;
52 If l_UntPerct <> 100 Then
53 P_WC_Grade := -1;
54 Return;
55 End If;
56 For JobFtrrec in Jobftr_Unitperc
57 Loop
58 For Percrec in Case_group_Algor(JobFtrrec.Job_Feature_Code)
59 Loop
60 If Percrec.Perc <= JobFtrrec.Untperc Then
61 Open Grade(Percrec.PARENT_HIERARCHY_NODE_ID, Percrec.Hierarchy_Version_Id);
62 Fetch Grade into P_Wc_Case_group, P_WC_Grade;
63 If Grade%ROWCOUNT > 1 then
64 Close Grade;
65 P_WC_Grade := -3;
66 Return;
67 End If;
68 If Grade%FOUND Then
69 Close Grade;
70 Return;
71 Else
72 Close Grade;
73 P_WC_Grade := -2;
74 Return;
75 End If;
76 Return;
77 End If;
78 End Loop;
79 End Loop;
80 Exception
81 When Others Then
82 Hr_utility.Set_message(8302,sqlerrm);
83 Hr_utility.raise_Error;
84 End;
85
86 Procedure PQH_DE_CS_COMPUTE
87 (P_GVNumber In Number,
88 P_CS_Grade Out NOCOPY Number ,
89 p_cs_Grdnam Out NOCOPY Varchar2) is
90
91 Cursor Derive_Grad Is
92 Select GRADE_ID
93 From PQH_DE_RESULT_SETS
94 Where P_GVNumber
95 Between GRADUAL_VALUE_NUMBER_FROM
96 and GRADUAL_VALUE_NUMBER_To;
97 Begin
98
99 hr_multi_message.enable_message_list;
100
101 Open Derive_Grad;
102 Fetch Derive_Grad into P_CS_Grade;
103 If Derive_Grad%NOTFOUND Then
104 Close Derive_Grad;
105 Hr_utility.Set_message(8302,'PQH_DE_GRADE');
106 Hr_utility.raise_Error;
107 End If;
108 If Derive_Grad%ROWCOUNT > 1 then
109 Close Derive_Grad;
110 Hr_utility.Set_message(8302,'PQH_DE_GRADE');
111 Hr_utility.raise_Error;
112 End If;
113 p_cs_Grdnam := hr_general.Decode_grade(P_CS_Grade);
114 Close Derive_Grad;
115 Exception
116 when app_exception.application_exception then
117 if hr_multi_message.exception_add
118 (p_associated_column1 => 'PQH_DE_WRKPLC_VLDTN_VERS.GRADE'
119 ) then
120 -- raise;
121 NULL;
122 end if;
123 End;
124
125 Function Lookup_Meaning
126 (P_Lookup_Type In Varchar2,
127 P_Lookup_Code In Varchar2)
128 Return Varchar2 Is
129 Cursor C1 is
130 Select Meaning
131 from Hr_lookups
132 where Lookup_Type = p_lookup_Type and
133 Lookup_Code = P_Lookup_Code and
134 Enabled_Flag = 'Y';
135 l_Meaning Varchar2(80);
136 Begin
137 Open C1;
138 Fetch C1 into l_Meaning;
139 Close C1;
140 Return L_Meaning;
141 End;
142
143 Function UNFreeze(P_Wrkplc_Vldtn_vern_Id In Number)
144 Return Varchar2 is
145 c_Frz varchar2(1);
146 c_Frz_Enab_Dis number:=0;
147 cursor c1(Vald_id number) is
148 select count(*) from
149 Hr_all_Positions_F
150 where INFORMATION_CATEGORY = 'DE_PQH_WP'
151 and Nvl(Information5,Information9) = To_Char(Vald_id);
152 cursor c2(Vald_id number) is
153 select FREEZE
154 from pqh_de_wrkplc_vldtn_vers
155 where WRKPLC_VLDTN_VER_ID = Vald_id;
156 Begin
157 OPEN c2(P_Wrkplc_Vldtn_vern_Id);
158 fetch c2 into c_Frz;
159 CLOSE c2;
160 if c_Frz = 'U' then
161 return 'U';
162 elsif c_Frz = 'F'
163 then
164 OPEN c1(P_Wrkplc_Vldtn_vern_Id);
165 fetch c1 into c_Frz_Enab_Dis;
166 CLOSE c1;
167 if c_Frz_Enab_Dis > 0
168 then
169 Return 'D';
170 else
171 Return 'F';
172 end if;
173 end if;
174 END;
175 End;
[Home] [Help]