1 PACKAGE BODY PQH_PQHWSEPS_XMLP_PKG AS
2 /* $Header: PQHWSEPSB.pls 120.3 2007/12/21 17:29:42 vjaganat noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 l_dummy boolean;
5 BEGIN
6 LP_START_DATE := to_char(P_START_DATE,'DD-MON-YYYY');
7 LP_END_DATE := to_char(P_END_DATE,'DD-MON-YYYY');
8 DECLARE
9 CURSOR CSR_ELEMENT_NAME IS
10 SELECT
11 ELEMENT_NAME
12 FROM
13 PAY_ELEMENT_TYPES
14 WHERE ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID;
15 CURSOR CSR_POSN_TYPE IS
16 SELECT
17 MEANING
18 FROM
19 HR_LOOKUPS
20 WHERE LOOKUP_TYPE = 'PQH_POSITION_TYPE'
21 AND LOOKUP_CODE = P_POSITION_TYPE;
22 CURSOR CSR_CURRENCY_NAME IS
23 SELECT
24 NAME
25 FROM
26 FND_CURRENCIES_ACTIVE_V
27 WHERE CURRENCY_CODE <> 'STAT'
28 AND CURRENCY_CODE = P_CURRENCY_CODE;
29 CURSOR CSR_SESSION_DATE IS
30 SELECT
31 sysdate
32 FROM
33 DUAL;
34 BEGIN
35 l_dummy:=BEFOREPFORM();
36 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
37 OPEN CSR_CURRENCY_NAME;
38 FETCH CSR_CURRENCY_NAME
39 INTO
40 CP_CURRENCY;
41 CLOSE CSR_CURRENCY_NAME;
42 OPEN CSR_ELEMENT_NAME;
43 FETCH CSR_ELEMENT_NAME
44 INTO
45 CP_ELEMENT_NAME;
46 CLOSE CSR_ELEMENT_NAME;
47 OPEN CSR_POSN_TYPE;
48 FETCH CSR_POSN_TYPE
49 INTO
50 CP_POSITION_TYPE;
51 CLOSE CSR_POSN_TYPE;
52 P_REPORT_TITLE := HR_GENERAL.DECODE_LOOKUP('PQH_REPORT_TITLES'
53 ,'PQHWSEPS');
54 C_BUSINESS_GROUP_NAME := GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
55 OPEN CSR_SESSION_DATE;
56 FETCH CSR_SESSION_DATE
57 INTO
58 CP_SESSION_DT;
59 CLOSE CSR_SESSION_DATE;
60 END;
61 RETURN (TRUE);
62 END BEFOREREPORT;
63
64 FUNCTION CF_1FORMULA(BUDGET_UNIT_ID IN NUMBER
65 ,ACTUAL_AMT IN NUMBER
66 ,COMMITTED_AMT IN NUMBER) RETURN NUMBER IS
67 CURSOR SHARED_TYPES IS
68 SELECT
69 SYSTEM_TYPE_CD
70 FROM
71 PER_SHARED_TYPES
72 WHERE SHARED_TYPE_ID = BUDGET_UNIT_ID;
73 L_SHARED_TYPE_CD PER_SHARED_TYPES.SYSTEM_TYPE_CD%TYPE;
74 BEGIN
75 OPEN SHARED_TYPES;
76 FETCH SHARED_TYPES
77 INTO
78 L_SHARED_TYPE_CD;
79 CLOSE SHARED_TYPES;
80 IF L_SHARED_TYPE_CD = 'MONEY' THEN
81 RETURN (NVL(ACTUAL_AMT
82 ,0) + NVL(COMMITTED_AMT
83 ,0));
84 ELSE
85 RETURN (NVL(ACTUAL_AMT
86 ,0));
87 END IF;
88 END CF_1FORMULA;
89
90 FUNCTION CF_DEF_EX_AMTFORMULA(BUDGETED_AMT IN NUMBER
91 ,CF_PROJECTED_EXP IN NUMBER) RETURN NUMBER IS
92 BEGIN
93 RETURN (NVL(BUDGETED_AMT
94 ,0) - NVL(CF_PROJECTED_EXP
95 ,0));
96 END CF_DEF_EX_AMTFORMULA;
97
98 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
99 BEGIN
100 INSERT INTO FND_SESSIONS
101 (SESSION_ID
102 ,EFFECTIVE_DATE)
103 VALUES (USERENV('sessionid')
104 ,P_EFFECTIVE_DATE);
105 RETURN (TRUE);
106 END BEFOREPFORM;
107
108 FUNCTION CF_ACT_PERFORMULA(BUDGETED_AMT IN NUMBER
109 ,ACTUAL_AMT IN NUMBER) RETURN NUMBER IS
110 BEGIN
111 IF BUDGETED_AMT = 0 THEN
112 RETURN 0;
113 ELSE
114 RETURN (NVL(ACTUAL_AMT
115 ,0) / BUDGETED_AMT) * 100;
116 END IF;
117 END CF_ACT_PERFORMULA;
118
119 FUNCTION CF_COM_PERFORMULA(BUDGETED_AMT IN NUMBER
120 ,COMMITTED_AMT IN NUMBER) RETURN NUMBER IS
121 BEGIN
122 IF BUDGETED_AMT = 0 THEN
123 RETURN 0;
124 ELSE
125 RETURN (NVL(COMMITTED_AMT
126 ,0) / BUDGETED_AMT) * 100;
127 END IF;
128 END CF_COM_PERFORMULA;
129
130 FUNCTION CF_PROJ_PERFORMULA(BUDGETED_AMT IN NUMBER
131 ,CF_PROJECTED_EXP IN NUMBER) RETURN NUMBER IS
132 BEGIN
133 IF BUDGETED_AMT = 0 THEN
134 RETURN 0;
135 ELSE
136 RETURN (NVL(CF_PROJECTED_EXP
137 ,0) / BUDGETED_AMT) * 100;
138 END IF;
139 END CF_PROJ_PERFORMULA;
140
141 FUNCTION CF_DEF_EX_PERFORMULA(BUDGETED_AMT IN NUMBER
142 ,CF_DEF_EX_AMT IN NUMBER) RETURN NUMBER IS
143 BEGIN
144 IF BUDGETED_AMT = 0 THEN
145 RETURN 0;
146 ELSE
147 RETURN (NVL(CF_DEF_EX_AMT
148 ,0) / BUDGETED_AMT) * 100;
149 END IF;
150 END CF_DEF_EX_PERFORMULA;
151
152 FUNCTION CF_ELM_ACTUAL_PERFORMULA(SUM_BUDGETED_AMT IN NUMBER
153 ,SUM_ACTUAL_AMT IN NUMBER) RETURN NUMBER IS
154 BEGIN
155 IF SUM_BUDGETED_AMT = 0 THEN
156 RETURN 0;
157 ELSE
158 RETURN ((NVL(SUM_ACTUAL_AMT
159 ,0) / SUM_BUDGETED_AMT) * 100);
160 END IF;
161 END CF_ELM_ACTUAL_PERFORMULA;
162
163 FUNCTION CF_ELM_COMMIT_PERFORMULA(SUM_BUDGETED_AMT IN NUMBER
164 ,SUM_COMMITTED_AMT IN NUMBER) RETURN NUMBER IS
165 BEGIN
166 IF SUM_BUDGETED_AMT = 0 THEN
167 RETURN 0;
168 ELSE
169 RETURN ((NVL(SUM_COMMITTED_AMT
170 ,0) / SUM_BUDGETED_AMT) * 100);
171 END IF;
172 END CF_ELM_COMMIT_PERFORMULA;
173
174 FUNCTION CF_ELM_PROJECTED_EXPFORMULA(ELM_BUDGET_UNIT_ID IN NUMBER
175 ,SUM_ACTUAL_AMT IN NUMBER
176 ,SUM_COMMITTED_AMT IN NUMBER) RETURN NUMBER IS
177 CURSOR SHARED_TYPES IS
178 SELECT
179 SYSTEM_TYPE_CD
180 FROM
181 PER_SHARED_TYPES
182 WHERE SHARED_TYPE_ID = ELM_BUDGET_UNIT_ID;
183 L_SHARED_TYPE_CD PER_SHARED_TYPES.SYSTEM_TYPE_CD%TYPE;
184 BEGIN
185 OPEN SHARED_TYPES;
186 FETCH SHARED_TYPES
187 INTO
188 L_SHARED_TYPE_CD;
189 CLOSE SHARED_TYPES;
190 IF L_SHARED_TYPE_CD = 'MONEY' THEN
191 RETURN (NVL(SUM_ACTUAL_AMT
192 ,0) + NVL(SUM_COMMITTED_AMT
193 ,0));
194 ELSE
195 RETURN (NVL(SUM_ACTUAL_AMT
196 ,0));
197 END IF;
198 END CF_ELM_PROJECTED_EXPFORMULA;
199
200 FUNCTION CF_ELM_PROJ_PERFORMULA(SUM_BUDGETED_AMT IN NUMBER
201 ,CF_ELM_PROJECTED_EXP IN NUMBER) RETURN NUMBER IS
202 BEGIN
203 IF SUM_BUDGETED_AMT = 0 THEN
204 RETURN 0;
205 ELSE
206 RETURN ((NVL(CF_ELM_PROJECTED_EXP
207 ,0) / SUM_BUDGETED_AMT) * 100);
208 END IF;
209 END CF_ELM_PROJ_PERFORMULA;
210
211 FUNCTION CF_ELM_DEF_EX_AMTFORMULA(SUM_BUDGETED_AMT IN NUMBER
212 ,CF_ELM_PROJECTED_EXP IN NUMBER) RETURN NUMBER IS
213 BEGIN
214 RETURN (NVL(SUM_BUDGETED_AMT
215 ,0) - NVL(CF_ELM_PROJECTED_EXP
216 ,0));
217 END CF_ELM_DEF_EX_AMTFORMULA;
218
219 FUNCTION CF_ELM_DEF_EX_PERFORMULA(SUM_BUDGETED_AMT IN NUMBER
220 ,CF_ELM_DEF_EX_AMT IN NUMBER) RETURN NUMBER IS
221 BEGIN
222 IF SUM_BUDGETED_AMT = 0 THEN
223 RETURN 0;
224 ELSE
225 RETURN ((NVL(CF_ELM_DEF_EX_AMT
226 ,0) / SUM_BUDGETED_AMT) * 100);
227 END IF;
228 END CF_ELM_DEF_EX_PERFORMULA;
229
230 FUNCTION CF_FORMAT_MASK2(ELM_BUDGET_UNIT_ID IN NUMBER) RETURN CHAR IS
231 CURSOR CSR_UOM IS
232 SELECT
233 SYSTEM_TYPE_CD
234 FROM
235 PER_SHARED_TYPES
236 WHERE SHARED_TYPE_ID = ELM_BUDGET_UNIT_ID
237 AND LOOKUP_TYPE = 'BUDGET_MEASUREMENT_TYPE';
238 L_BUDGET_MEASUREMENT_TYPE PER_SHARED_TYPES.SHARED_TYPE_NAME%TYPE;
239 L_FORMAT_MASK VARCHAR2(50);
240 BEGIN
241 OPEN CSR_UOM;
242 FETCH CSR_UOM
243 INTO
244 L_BUDGET_MEASUREMENT_TYPE;
245 CLOSE CSR_UOM;
246 IF L_BUDGET_MEASUREMENT_TYPE = 'MONEY' THEN
247 L_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(P_CURRENCY_CODE
248 ,22);
249 ELSE
250 FND_CURRENCY.BUILD_FORMAT_MASK(L_FORMAT_MASK
251 ,22
252 ,2
253 ,NULL
254 ,NULL
255 ,NULL
256 ,NULL);
257 END IF;
258 RETURN L_FORMAT_MASK;
259 END CF_FORMAT_MASK2;
260
261 FUNCTION CF_FORMAT_MASK1(BUDGET_UNIT_ID IN NUMBER) RETURN CHAR IS
262 CURSOR CSR_UOM IS
263 SELECT
264 SYSTEM_TYPE_CD
265 FROM
266 PER_SHARED_TYPES
267 WHERE SHARED_TYPE_ID = BUDGET_UNIT_ID
268 AND LOOKUP_TYPE = 'BUDGET_MEASUREMENT_TYPE';
269 L_BUDGET_MEASUREMENT_TYPE PER_SHARED_TYPES.SHARED_TYPE_NAME%TYPE;
270 L_FORMAT_MASK VARCHAR2(50);
271 BEGIN
272 OPEN CSR_UOM;
273 FETCH CSR_UOM
274 INTO
275 L_BUDGET_MEASUREMENT_TYPE;
276 CLOSE CSR_UOM;
277 IF L_BUDGET_MEASUREMENT_TYPE = 'MONEY' THEN
278 L_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(P_CURRENCY_CODE
279 ,22);
280 ELSE
281 FND_CURRENCY.BUILD_FORMAT_MASK(L_FORMAT_MASK
282 ,22
283 ,2
284 ,NULL
285 ,NULL
286 ,NULL
287 ,NULL);
288 END IF;
289 RETURN L_FORMAT_MASK;
290 END CF_FORMAT_MASK1;
291
292 FUNCTION AFTERREPORT RETURN BOOLEAN IS
293 BEGIN
294 RETURN (TRUE);
295 END AFTERREPORT;
296
297 FUNCTION C_REPORT_SUBTITLE_P RETURN VARCHAR2 IS
298 BEGIN
299 RETURN C_REPORT_SUBTITLE;
300 END C_REPORT_SUBTITLE_P;
301
302 FUNCTION CP_ELEMENT_NAME_P RETURN VARCHAR2 IS
303 BEGIN
304 RETURN CP_ELEMENT_NAME;
305 END CP_ELEMENT_NAME_P;
306
307 FUNCTION CP_POSITION_TYPE_P RETURN VARCHAR2 IS
308 BEGIN
309 RETURN CP_POSITION_TYPE;
310 END CP_POSITION_TYPE_P;
311
312 FUNCTION CP_CURRENCY_P RETURN VARCHAR2 IS
313 BEGIN
314 RETURN CP_CURRENCY;
315 END CP_CURRENCY_P;
316
317 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
318 BEGIN
319 RETURN C_BUSINESS_GROUP_NAME;
320 END C_BUSINESS_GROUP_NAME_P;
321
322 FUNCTION CP_SESSION_DT_P RETURN DATE IS
323 BEGIN
324 RETURN CP_SESSION_DT;
325 END CP_SESSION_DT_P;
326
327 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
328 X0 VARCHAR2(2000);
329 BEGIN
330 X0 := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
331 RETURN X0;
332 END GET_BUSINESS_GROUP;
333
334 END PQH_PQHWSEPS_XMLP_PKG;
[Home] [Help]