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