1 PACKAGE BODY PQH_PQHWSPCH_XMLP_PKG AS
2 /* $Header: PQHWSPCHB.pls 120.2 2007/12/21 19:32:33 vjaganat noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 l_dummy boolean;
5 BEGIN
6 DECLARE
7 CURSOR CSR_HIERARCHY IS
8 SELECT
9 NAME
10 FROM
11 PER_ORGANIZATION_STRUCTURES
12 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
13 AND NVL(POSITION_CONTROL_STRUCTURE_FLG
14 ,'N') = 'Y';
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_HIERARCHY;
43 FETCH CSR_HIERARCHY
44 INTO
45 CP_HIERARCHY_NAME;
46 CLOSE CSR_HIERARCHY;
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 ,'PQHWSPCH');
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_ID1 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_ID1;
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_ORG_PROJECTED_EXPFORMULA(BUDGET_UNIT_ID IN NUMBER
153 ,CF_ORG_ACTUAL_AMT IN NUMBER
154 ,CF_ORG_COMMITTED_AMT IN NUMBER) RETURN NUMBER IS
155 CURSOR SHARED_TYPES IS
156 SELECT
157 SYSTEM_TYPE_CD
158 FROM
159 PER_SHARED_TYPES
160 WHERE SHARED_TYPE_ID = BUDGET_UNIT_ID;
161 L_SHARED_TYPE_CD PER_SHARED_TYPES.SYSTEM_TYPE_CD%TYPE;
162 BEGIN
163 OPEN SHARED_TYPES;
164 FETCH SHARED_TYPES
165 INTO
166 L_SHARED_TYPE_CD;
167 CLOSE SHARED_TYPES;
168 IF L_SHARED_TYPE_CD = 'MONEY' THEN
169 RETURN (NVL(CF_ORG_ACTUAL_AMT
170 ,0) + NVL(CF_ORG_COMMITTED_AMT
171 ,0));
172 ELSE
173 RETURN (NVL(CF_ORG_ACTUAL_AMT
174 ,0));
175 END IF;
176 END CF_ORG_PROJECTED_EXPFORMULA;
177
178 FUNCTION CF_ORG_DEF_EX_AMTFORMULA(CF_ORG_BUDGETED_AMT IN NUMBER
179 ,CF_ORG_PROJECTED_EXP IN NUMBER) RETURN NUMBER IS
180 BEGIN
181 RETURN (NVL(CF_ORG_BUDGETED_AMT
182 ,0) - NVL(CF_ORG_PROJECTED_EXP
183 ,0));
184 END CF_ORG_DEF_EX_AMTFORMULA;
185
186 FUNCTION CF_ORG_ACT_PERFORMULA(CF_ORG_BUDGETED_AMT IN NUMBER
187 ,CF_ORG_ACTUAL_AMT IN NUMBER) RETURN NUMBER IS
188 BEGIN
189 IF CF_ORG_BUDGETED_AMT = 0 THEN
190 RETURN 0;
191 ELSE
192 RETURN (NVL(CF_ORG_ACTUAL_AMT
193 ,0) / CF_ORG_BUDGETED_AMT) * 100;
194 END IF;
195 END CF_ORG_ACT_PERFORMULA;
196
197 FUNCTION CF_ORG_COM_PERFORMULA(CF_ORG_BUDGETED_AMT IN NUMBER
198 ,CF_ORG_COMMITTED_AMT IN NUMBER) RETURN NUMBER IS
199 BEGIN
200 IF CF_ORG_BUDGETED_AMT = 0 THEN
201 RETURN 0;
202 ELSE
203 RETURN (NVL(CF_ORG_COMMITTED_AMT
204 ,0) / CF_ORG_BUDGETED_AMT) * 100;
205 END IF;
206 END CF_ORG_COM_PERFORMULA;
207
208 FUNCTION CF_ORG_PROJ_PERFORMULA(CF_ORG_BUDGETED_AMT IN NUMBER
209 ,CF_ORG_PROJECTED_EXP IN NUMBER) RETURN NUMBER IS
210 BEGIN
211 IF CF_ORG_BUDGETED_AMT = 0 THEN
212 RETURN 0;
213 ELSE
214 RETURN (NVL(CF_ORG_PROJECTED_EXP
215 ,0) / CF_ORG_BUDGETED_AMT) * 100;
216 END IF;
217 END CF_ORG_PROJ_PERFORMULA;
218
219 FUNCTION CF_ORG_DEF_EX_PERFORMULA(CF_ORG_BUDGETED_AMT IN NUMBER
220 ,CF_ORG_DEF_EX_AMT IN NUMBER) RETURN NUMBER IS
221 BEGIN
222 IF CF_ORG_BUDGETED_AMT = 0 THEN
223 RETURN 0;
224 ELSE
225 RETURN (NVL(CF_ORG_DEF_EX_AMT
226 ,0) / CF_ORG_BUDGETED_AMT) * 100;
227 END IF;
228 END CF_ORG_DEF_EX_PERFORMULA;
229
230 FUNCTION CF_ORG_BUDGETED_AMTFORMULA(ORGANIZATION_ID1 IN NUMBER
231 ,BUDGET_UNIT_ID IN NUMBER) RETURN NUMBER IS
232 L_AMT NUMBER(15,2);
233 BEGIN
234 L_AMT := PQH_MGMT_RPT_PKG.GET_ORG_POSN_BUDGET_AMT(ORGANIZATION_ID1
235 ,P_START_DATE
236 ,P_END_DATE
237 ,BUDGET_UNIT_ID
238 ,P_CURRENCY_CODE);
239 RETURN NVL(L_AMT
240 ,0);
241 END CF_ORG_BUDGETED_AMTFORMULA;
242
243 FUNCTION CF_ORG_ACTUAL_AMTFORMULA(ORGANIZATION_ID1 IN NUMBER
244 ,BUDGET_UNIT_ID IN NUMBER) RETURN NUMBER IS
245 L_AMT NUMBER(15,2);
246 BEGIN
247 L_AMT := PQH_MGMT_RPT_PKG.GET_ORG_POSN_ACTUAL_CMMTMNTS(ORGANIZATION_ID1
248 ,P_START_DATE
249 ,P_END_DATE
250 ,BUDGET_UNIT_ID
251 ,'A'
252 ,P_CURRENCY_CODE);
253 RETURN NVL(L_AMT
254 ,0);
255 END CF_ORG_ACTUAL_AMTFORMULA;
256
257 FUNCTION CF_ORG_COMMITTED_AMTFORMULA(ORGANIZATION_ID1 IN NUMBER
258 ,BUDGET_UNIT_ID IN NUMBER) RETURN NUMBER IS
259 L_AMT NUMBER(15,2);
260 BEGIN
261 L_AMT := PQH_MGMT_RPT_PKG.GET_ORG_POSN_ACTUAL_CMMTMNTS(ORGANIZATION_ID1
262 ,P_START_DATE
263 ,P_END_DATE
264 ,BUDGET_UNIT_ID
265 ,'C'
266 ,P_CURRENCY_CODE);
267 RETURN NVL(L_AMT
268 ,0);
269 END CF_ORG_COMMITTED_AMTFORMULA;
270
271 FUNCTION CF_BGRP_BUDGETED_AMTFORMULA(BGRP_BUDGET_UNIT_ID IN NUMBER
272 ,BGRP_ACTUAL_AMT IN NUMBER
273 ,BGRP_COMMITTED_AMT IN NUMBER) RETURN NUMBER IS
274 CURSOR SHARED_TYPES IS
275 SELECT
276 SYSTEM_TYPE_CD
277 FROM
278 PER_SHARED_TYPES
279 WHERE SHARED_TYPE_ID = BGRP_BUDGET_UNIT_ID;
280 L_SHARED_TYPE_CD PER_SHARED_TYPES.SYSTEM_TYPE_CD%TYPE;
281 BEGIN
282 OPEN SHARED_TYPES;
283 FETCH SHARED_TYPES
284 INTO
285 L_SHARED_TYPE_CD;
286 CLOSE SHARED_TYPES;
287 IF L_SHARED_TYPE_CD = 'MONEY' THEN
288 RETURN (NVL(BGRP_ACTUAL_AMT
289 ,0) + NVL(BGRP_COMMITTED_AMT
290 ,0));
291 ELSE
292 RETURN (NVL(BGRP_ACTUAL_AMT
293 ,0));
294 END IF;
295 END CF_BGRP_BUDGETED_AMTFORMULA;
296
297 FUNCTION CF_BGRP_DEF_EX_AMTFORMULA(BGRP_BUDGETED_AMT IN NUMBER
298 ,CF_BGRP_PROJECTED_AMT IN NUMBER) RETURN NUMBER IS
299 BEGIN
300 RETURN (NVL(BGRP_BUDGETED_AMT
301 ,0) - NVL(CF_BGRP_PROJECTED_AMT
302 ,0));
303 END CF_BGRP_DEF_EX_AMTFORMULA;
304
305 FUNCTION CF_BGRP_COM_PERFORMULA(BGRP_BUDGETED_AMT IN NUMBER
306 ,BGRP_COMMITTED_AMT IN NUMBER) RETURN NUMBER IS
307 BEGIN
308 IF BGRP_BUDGETED_AMT = 0 THEN
309 RETURN 0;
310 ELSE
311 RETURN ((NVL(BGRP_COMMITTED_AMT
312 ,0) / BGRP_BUDGETED_AMT) * 100);
313 END IF;
314 END CF_BGRP_COM_PERFORMULA;
315
316 FUNCTION CF_BGRP_PROJ_PERFORMULA(BGRP_BUDGETED_AMT IN NUMBER
317 ,CF_BGRP_PROJECTED_AMT IN NUMBER) RETURN NUMBER IS
318 BEGIN
319 IF BGRP_BUDGETED_AMT = 0 THEN
320 RETURN 0;
321 ELSE
322 RETURN ((NVL(CF_BGRP_PROJECTED_AMT
323 ,0) / BGRP_BUDGETED_AMT) * 100);
324 END IF;
325 END CF_BGRP_PROJ_PERFORMULA;
326
327 FUNCTION CF_BGRP_DEF_EX_PERFORMULA(BGRP_BUDGETED_AMT IN NUMBER
328 ,CF_BGRP_DEF_EX_AMT IN NUMBER) RETURN NUMBER IS
329 BEGIN
330 IF BGRP_BUDGETED_AMT = 0 THEN
331 RETURN 0;
332 ELSE
333 RETURN ((NVL(CF_BGRP_DEF_EX_AMT
334 ,0) / BGRP_BUDGETED_AMT) * 100);
335 END IF;
336 END CF_BGRP_DEF_EX_PERFORMULA;
337
338 FUNCTION CF_BGRP_ACTUAL_PERFORMULA(BGRP_BUDGETED_AMT IN NUMBER
339 ,BGRP_ACTUAL_AMT IN NUMBER) RETURN NUMBER IS
340 BEGIN
341 IF BGRP_BUDGETED_AMT = 0 THEN
342 RETURN 0;
343 ELSE
344 RETURN ((NVL(BGRP_ACTUAL_AMT
345 ,0) / BGRP_BUDGETED_AMT) * 100);
346 END IF;
347 END CF_BGRP_ACTUAL_PERFORMULA;
348
349 FUNCTION CF_FORMAT_MASK2(BUDGET_UNIT_ID IN NUMBER) RETURN CHAR IS
350 CURSOR CSR_UOM IS
351 SELECT
352 SYSTEM_TYPE_CD
353 FROM
354 PER_SHARED_TYPES
355 WHERE SHARED_TYPE_ID = BUDGET_UNIT_ID
356 AND LOOKUP_TYPE = 'BUDGET_MEASUREMENT_TYPE';
357 L_BUDGET_MEASUREMENT_TYPE PER_SHARED_TYPES.SHARED_TYPE_NAME%TYPE;
358 L_FORMAT_MASK VARCHAR2(50);
359 BEGIN
360 OPEN CSR_UOM;
361 FETCH CSR_UOM
362 INTO
363 L_BUDGET_MEASUREMENT_TYPE;
364 CLOSE CSR_UOM;
365 IF L_BUDGET_MEASUREMENT_TYPE = 'MONEY' THEN
366 L_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(P_CURRENCY_CODE
367 ,22);
368 ELSE
369 FND_CURRENCY.BUILD_FORMAT_MASK(L_FORMAT_MASK
370 ,22
371 ,2
372 ,NULL
373 ,NULL
374 ,NULL
375 ,NULL);
376 END IF;
377 RETURN L_FORMAT_MASK;
378 END CF_FORMAT_MASK2;
379
380 FUNCTION CF_FORMAT_MASK1(BUDGET_UNIT_ID1 IN NUMBER) RETURN CHAR IS
381 CURSOR CSR_UOM IS
382 SELECT
383 SYSTEM_TYPE_CD
384 FROM
385 PER_SHARED_TYPES
386 WHERE SHARED_TYPE_ID = BUDGET_UNIT_ID1
387 AND LOOKUP_TYPE = 'BUDGET_MEASUREMENT_TYPE';
388 L_BUDGET_MEASUREMENT_TYPE PER_SHARED_TYPES.SHARED_TYPE_NAME%TYPE;
389 L_FORMAT_MASK VARCHAR2(50);
390 BEGIN
391 OPEN CSR_UOM;
392 FETCH CSR_UOM
393 INTO
394 L_BUDGET_MEASUREMENT_TYPE;
395 CLOSE CSR_UOM;
396 IF L_BUDGET_MEASUREMENT_TYPE = 'MONEY' THEN
397 L_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(P_CURRENCY_CODE
398 ,22);
399 ELSE
400 FND_CURRENCY.BUILD_FORMAT_MASK(L_FORMAT_MASK
401 ,22
402 ,2
403 ,NULL
404 ,NULL
405 ,NULL
406 ,NULL);
407 END IF;
408 RETURN L_FORMAT_MASK;
409 END CF_FORMAT_MASK1;
410
411 FUNCTION CF_FORMAT_MASK3(BGRP_BUDGET_UNIT_ID IN NUMBER) RETURN CHAR IS
412 CURSOR CSR_UOM IS
413 SELECT
414 SYSTEM_TYPE_CD
415 FROM
416 PER_SHARED_TYPES
417 WHERE SHARED_TYPE_ID = BGRP_BUDGET_UNIT_ID
418 AND LOOKUP_TYPE = 'BUDGET_MEASUREMENT_TYPE';
419 L_BUDGET_MEASUREMENT_TYPE PER_SHARED_TYPES.SHARED_TYPE_NAME%TYPE;
420 L_FORMAT_MASK VARCHAR2(50);
421 BEGIN
422 OPEN CSR_UOM;
423 FETCH CSR_UOM
424 INTO
425 L_BUDGET_MEASUREMENT_TYPE;
426 CLOSE CSR_UOM;
427 IF L_BUDGET_MEASUREMENT_TYPE = 'MONEY' THEN
428 L_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(P_CURRENCY_CODE
429 ,22);
430 ELSE
431 FND_CURRENCY.BUILD_FORMAT_MASK(L_FORMAT_MASK
432 ,22
433 ,2
434 ,NULL
435 ,NULL
436 ,NULL
437 ,NULL);
438 END IF;
439 RETURN L_FORMAT_MASK;
440 END CF_FORMAT_MASK3;
441
442 FUNCTION AFTERREPORT RETURN BOOLEAN IS
443 BEGIN
444 RETURN (TRUE);
445 END AFTERREPORT;
446
447 FUNCTION C_REPORT_SUBTITLE_P RETURN VARCHAR2 IS
448 BEGIN
449 RETURN C_REPORT_SUBTITLE;
450 END C_REPORT_SUBTITLE_P;
451
452 FUNCTION CP_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
453 BEGIN
454 RETURN CP_BUSINESS_GROUP_NAME;
455 END CP_BUSINESS_GROUP_NAME_P;
456
457 FUNCTION CP_HIERARCHY_NAME_P RETURN VARCHAR2 IS
458 BEGIN
459 RETURN CP_HIERARCHY_NAME;
460 END CP_HIERARCHY_NAME_P;
461
462 FUNCTION CP_POSITION_TYPE_P RETURN VARCHAR2 IS
463 BEGIN
464 RETURN CP_POSITION_TYPE;
465 END CP_POSITION_TYPE_P;
466
467 FUNCTION CP_CURRENCY_P RETURN VARCHAR2 IS
468 BEGIN
469 RETURN CP_CURRENCY;
470 END CP_CURRENCY_P;
471
472 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
473 BEGIN
474 RETURN C_BUSINESS_GROUP_NAME;
475 END C_BUSINESS_GROUP_NAME_P;
476
477 FUNCTION CP_SESSION_DT_P RETURN DATE IS
478 BEGIN
479 RETURN CP_SESSION_DT;
480 END CP_SESSION_DT_P;
481
482 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
483 X0 VARCHAR2(2000);
484 BEGIN
485 X0 := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
486 RETURN X0;
487 END GET_BUSINESS_GROUP;
488
489 END PQH_PQHWSPCH_XMLP_PKG;
[Home] [Help]