1 PACKAGE BODY PQH_PQHUSRCP_XMLP_PKG AS
2 /* $Header: PQHUSRCPB.pls 120.2 2007/12/07 06:58:13 vjaganat noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 --HR_STANDARD.EVENT('BEFORE REPORT');
6 CP_BUSINESS_GROUP_NAME := GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
7 RETURN (TRUE);
8 END BEFOREREPORT;
9
10 FUNCTION CF_GETTENURECOUNTFORMULA RETURN NUMBER IS
11 L_START_OF_TIME DATE := HR_GENERAL.START_OF_TIME;
12 BEGIN
13 CP_TENURED_CNT := PQH_TENURE_DETAIL.GETPERIODSTATUSCOUNT(P_SUPERVISOR_ID
14 ,'01'
15 ,L_START_OF_TIME
16 ,P_END_DATE);
17 CP_TENELIGIBLE_CNT := PQH_TENURE_DETAIL.GETPERIODSTATUSCOUNT(P_SUPERVISOR_ID
18 ,'04'
19 ,L_START_OF_TIME
20 ,P_END_DATE);
21 CP_TENDENIED_CNT := PQH_TENURE_DETAIL.GETPERIODSTATUSCOUNT(P_SUPERVISOR_ID
22 ,'05'
23 ,L_START_OF_TIME
24 ,P_END_DATE);
25 CP_TENUREDPERIOD_CNT := PQH_TENURE_DETAIL.GETPERIODSTATUSCOUNT(P_SUPERVISOR_ID
26 ,'01'
27 ,P_START_DATE
28 ,P_END_DATE);
29 CP_TENELIGIBLEPERIOD_CNT := PQH_TENURE_DETAIL.GETPERIODSTATUSCOUNT(P_SUPERVISOR_ID
30 ,'04'
31 ,P_START_DATE
32 ,P_END_DATE);
33 CP_TENDENIEDPERIOD_CNT := PQH_TENURE_DETAIL.GETPERIODSTATUSCOUNT(P_SUPERVISOR_ID
34 ,'05'
35 ,P_START_DATE
36 ,P_END_DATE);
37 RETURN 0;
38 END CF_GETTENURECOUNTFORMULA;
39
40 FUNCTION AFTERPFORM RETURN BOOLEAN IS
41 BEGIN
42 IF P_DIRECT_OR_ALL = 'A' THEN
43 P_AND_CLAUSE := NULL;
44 END IF;
45 RETURN (TRUE);
46 END AFTERPFORM;
47
48 FUNCTION CF_JOB_NAMEFORMULA(JOB_ID IN NUMBER) RETURN CHAR IS
49 CURSOR JOB_NM_CUR IS
50 SELECT
51 NAME
52 FROM
53 PER_JOBS_VL
54 WHERE JOB_ID = JOB_ID
55 AND BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
56 AND sysdate between NVL(DATE_FROM
57 ,SYSDATE)
58 AND NVL(DATE_TO
59 ,SYSDATE);
60 L_JOB_NAME VARCHAR2(240);
61 BEGIN
62 IF JOB_ID IS NOT NULL THEN
63 OPEN JOB_NM_CUR;
64 FETCH JOB_NM_CUR
65 INTO
66 L_JOB_NAME;
67 CLOSE JOB_NM_CUR;
68 END IF;
69 RETURN (L_JOB_NAME);
70 END CF_JOB_NAMEFORMULA;
71
72 FUNCTION CF_ACADEMIC_RANKFORMULA(RANK_ID IN VARCHAR2) RETURN CHAR IS
73 CURSOR ACADEMIC_RANK_CUR IS
74 SELECT
75 MEANING
76 FROM
77 HR_LOOKUPS
78 WHERE LOOKUP_TYPE = 'PQH_ACADEMIC_RANK'
79 AND LOOKUP_CODE = RANK_ID;
80 L_ACADEMIC_RANK VARCHAR2(40);
81 BEGIN
82 IF RANK_ID IS NOT NULL THEN
83 OPEN ACADEMIC_RANK_CUR;
84 FETCH ACADEMIC_RANK_CUR
85 INTO
86 L_ACADEMIC_RANK;
87 CLOSE ACADEMIC_RANK_CUR;
88 END IF;
89 RETURN (L_ACADEMIC_RANK);
90 END CF_ACADEMIC_RANKFORMULA;
91
92 FUNCTION CF_TENURE_STATUSFORMULA(TENURE_STATUS_CD IN VARCHAR2
93 ,RANK_ID IN VARCHAR2) RETURN CHAR IS
94 CURSOR TENURE_STATUS_CUR IS
95 SELECT
96 MEANING
97 FROM
98 HR_LOOKUPS
99 WHERE LOOKUP_TYPE = 'PQH_TENURE_STATUS'
100 AND LOOKUP_CODE = TENURE_STATUS_CD;
101 L_TENURE_STATUS VARCHAR2(40);
102 BEGIN
103 IF RANK_ID IS NOT NULL THEN
104 OPEN TENURE_STATUS_CUR;
105 FETCH TENURE_STATUS_CUR
106 INTO
107 L_TENURE_STATUS;
108 CLOSE TENURE_STATUS_CUR;
109 END IF;
110 RETURN (L_TENURE_STATUS);
111 END CF_TENURE_STATUSFORMULA;
112
113 FUNCTION CF_MANAGER_NAMEFORMULA(MANAGER_ID IN NUMBER) RETURN VARCHAR2 IS
114 BEGIN
115 RETURN (GETMANAGERNAME(MANAGER_ID));
116 END CF_MANAGER_NAMEFORMULA;
117
118 FUNCTION CF_DIRECT_OR_HIERARCHIALFORMUL RETURN CHAR IS
119 BEGIN
120 IF P_DIRECT_OR_ALL = 'D' THEN
121 RETURN ('Direct Report');
122 ELSE
123 RETURN ('Hierarchical Report');
124 END IF;
125 END CF_DIRECT_OR_HIERARCHIALFORMUL;
126
127 FUNCTION GETMANAGERNAME(P_MANAGER_ID IN NUMBER) RETURN VARCHAR2 IS
128 CURSOR MGR_NM_CUR IS
129 SELECT
130 LAST_NAME || ', ' || FIRST_NAME
131 FROM
132 PER_ALL_PEOPLE_F
133 WHERE PERSON_ID = P_MANAGER_ID
134 AND sysdate between EFFECTIVE_START_DATE
135 AND EFFECTIVE_END_DATE;
136 L_MANAGER_NM VARCHAR2(240);
137 BEGIN
138 OPEN MGR_NM_CUR;
139 FETCH MGR_NM_CUR
140 INTO
141 L_MANAGER_NM;
142 CLOSE MGR_NM_CUR;
143 RETURN (L_MANAGER_NM);
144 END GETMANAGERNAME;
145
146 FUNCTION CF_MGR_NAMEFORMULA RETURN CHAR IS
147 BEGIN
148 RETURN (GETMANAGERNAME(P_SUPERVISOR_ID));
149 END CF_MGR_NAMEFORMULA;
150
151 FUNCTION CF_SET_TOTAL_FOR_PERIODFORMULA(TENURE_STATUS_CD IN VARCHAR2
152 ,CS_STATUS_HEADCOUNT IN NUMBER) RETURN NUMBER IS
153 BEGIN
154 IF TENURE_STATUS_CD = '01' THEN
155 CP_TENURED_FOR_PERIOD := NVL(CP_TENURED_FOR_PERIOD
156 ,0) + CS_STATUS_HEADCOUNT;
157 ELSIF TENURE_STATUS_CD = '04' THEN
158 CP_TENURE_ELIGIBLE_FOR_PERIOD := NVL(CP_TENURE_ELIGIBLE_FOR_PERIOD
159 ,0) + CS_STATUS_HEADCOUNT;
160 ELSIF TENURE_STATUS_CD = '05' THEN
161 CP_TENURE_DENIED_FOR_PERIOD := NVL(CP_TENURE_DENIED_FOR_PERIOD
162 ,0) + CS_STATUS_HEADCOUNT;
163 END IF;
164 RETURN 0;
165 END CF_SET_TOTAL_FOR_PERIODFORMULA;
166
167 FUNCTION CF_GETTENURECNTFORMULA(MANAGER_ID IN NUMBER) RETURN NUMBER IS
168 BEGIN
169 CP_TENUREDMGR_CNT := PQH_TENURE_DETAIL.GETSUPSTATUSCOUNT(MANAGER_ID
170 ,'01'
171 ,P_END_DATE);
172 CP_TENELIGIBLEMGR_CNT := PQH_TENURE_DETAIL.GETSUPSTATUSCOUNT(MANAGER_ID
173 ,'04'
174 ,P_END_DATE);
175 CP_TENUREDENIED_CNT := PQH_TENURE_DETAIL.GETSUPSTATUSCOUNT(MANAGER_ID
176 ,'05'
177 ,P_END_DATE);
178 RETURN 0;
179 END CF_GETTENURECNTFORMULA;
180
181 FUNCTION AFTERREPORT RETURN BOOLEAN IS
182 BEGIN
183 --HR_STANDARD.EVENT('AFTER REPORT');
184 RETURN (TRUE);
185 END AFTERREPORT;
186
187 FUNCTION CP_TENUREDMGR_CNT_P RETURN NUMBER IS
188 BEGIN
189 RETURN CP_TENUREDMGR_CNT;
190 END CP_TENUREDMGR_CNT_P;
191
192 FUNCTION CP_TENELIGIBLEMGR_CNT_P RETURN NUMBER IS
193 BEGIN
194 RETURN CP_TENELIGIBLEMGR_CNT;
195 END CP_TENELIGIBLEMGR_CNT_P;
196
197 FUNCTION CP_TENUREDENIED_CNT_P RETURN NUMBER IS
198 BEGIN
199 RETURN CP_TENUREDENIED_CNT;
200 END CP_TENUREDENIED_CNT_P;
201
202 FUNCTION CP_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
203 BEGIN
204 RETURN CP_BUSINESS_GROUP_NAME;
205 END CP_BUSINESS_GROUP_NAME_P;
206
207 FUNCTION CP_TENURED_CNT_P RETURN NUMBER IS
208 BEGIN
209 RETURN CP_TENURED_CNT;
210 END CP_TENURED_CNT_P;
211
212 FUNCTION CP_TENELIGIBLE_CNT_P RETURN NUMBER IS
213 BEGIN
214 RETURN CP_TENELIGIBLE_CNT;
215 END CP_TENELIGIBLE_CNT_P;
216
217 FUNCTION CP_TENDENIED_CNT_P RETURN NUMBER IS
218 BEGIN
219 RETURN CP_TENDENIED_CNT;
220 END CP_TENDENIED_CNT_P;
221
222 FUNCTION CP_TENURED_FOR_PERIOD_P RETURN NUMBER IS
223 BEGIN
224 RETURN CP_TENURED_FOR_PERIOD;
225 END CP_TENURED_FOR_PERIOD_P;
226
227 FUNCTION CP_TENURE_DENIED_FOR_PERIOD_P RETURN NUMBER IS
228 BEGIN
229 RETURN CP_TENURE_DENIED_FOR_PERIOD;
230 END CP_TENURE_DENIED_FOR_PERIOD_P;
231
232 FUNCTION CP_TENUREDPERIOD_CNT_P RETURN NUMBER IS
233 BEGIN
234 RETURN CP_TENUREDPERIOD_CNT;
235 END CP_TENUREDPERIOD_CNT_P;
236
237 FUNCTION CP_TENELIGIBLEPERIOD_CNT_P RETURN NUMBER IS
238 BEGIN
239 RETURN CP_TENELIGIBLEPERIOD_CNT;
240 END CP_TENELIGIBLEPERIOD_CNT_P;
241
242 FUNCTION CP_TENDENIEDPERIOD_CNT_P RETURN NUMBER IS
243 BEGIN
244 RETURN CP_TENDENIEDPERIOD_CNT;
245 END CP_TENDENIEDPERIOD_CNT_P;
246
247 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
248 X0 VARCHAR2(2000);
249 BEGIN
250 X0 := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
251 RETURN X0;
252 END GET_BUSINESS_GROUP;
253
254 END PQH_PQHUSRCP_XMLP_PKG;
[Home] [Help]