1 PACKAGE BODY PQH_PQHWSPBD_XMLP_PKG AS
2 /* $Header: PQHWSPBDB.pls 120.2 2007/12/21 19:32:17 vjaganat noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 l_dummy boolean;
5 BEGIN
6 DECLARE
7 CURSOR CSR_POSITION_NAME IS
8 SELECT
9 NAME
10 FROM
11 PER_POSITIONS
12 WHERE POSITION_ID = P_POSITION_ID;
13 CURSOR CSR_ORG_NAME IS
14 SELECT
15 NAME
16 FROM
17 HR_ALL_ORGANIZATION_UNITS_TL
18 WHERE LANGUAGE = USERENV('LANG')
19 AND ORGANIZATION_ID = P_ORGANIZATION_ID;
20 CURSOR CSR_POSN_TYPE IS
21 SELECT
22 MEANING
23 FROM
24 HR_LOOKUPS
25 WHERE LOOKUP_TYPE = 'PQH_POSITION_TYPE'
26 AND LOOKUP_CODE = P_POSITION_TYPE;
27 CURSOR CSR_CURRENCY_NAME IS
28 SELECT
29 NAME
30 FROM
31 FND_CURRENCIES_ACTIVE_V
32 WHERE CURRENCY_CODE <> 'STAT'
33 AND CURRENCY_CODE = P_CURRENCY_CODE;
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_CURRENCY_NAME;
43 FETCH CSR_CURRENCY_NAME
44 INTO
45 CP_CURRENCY;
46 CLOSE CSR_CURRENCY_NAME;
47 OPEN CSR_ORG_NAME;
48 FETCH CSR_ORG_NAME
49 INTO
50 CP_ORGANIZATION_NAME;
51 CLOSE CSR_ORG_NAME;
52 OPEN CSR_POSITION_NAME;
53 FETCH CSR_POSITION_NAME
54 INTO
55 CP_POSITION_NAME;
56 CLOSE CSR_POSITION_NAME;
57 OPEN CSR_POSN_TYPE;
58 FETCH CSR_POSN_TYPE
59 INTO
60 CP_POSITION_TYPE;
61 CLOSE CSR_POSN_TYPE;
62 P_REPORT_TITLE := HR_GENERAL.DECODE_LOOKUP('PQH_REPORT_TITLES'
63 ,'PQHWSPBD');
64 C_BUSINESS_GROUP_NAME := GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
65 OPEN CSR_SESSION_DATE;
66 FETCH CSR_SESSION_DATE
67 INTO
68 CP_SESSION_DT;
69 CLOSE CSR_SESSION_DATE;
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;
98 END CF_1FORMULA;
99
100 FUNCTION CF_DEF_EX_AMTFORMULA(BUDGETED_AMT IN NUMBER
101 ,CF_PROJECTED_EXP IN NUMBER) RETURN NUMBER IS
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_FORMAT_MASK(BUDGET_UNIT_ID IN NUMBER) RETURN CHAR IS
163 CURSOR CSR_UOM IS
164 SELECT
165 SYSTEM_TYPE_CD
166 FROM
167 PER_SHARED_TYPES
168 WHERE SHARED_TYPE_ID = BUDGET_UNIT_ID
169 AND LOOKUP_TYPE = 'BUDGET_MEASUREMENT_TYPE';
170 L_BUDGET_MEASUREMENT_TYPE PER_SHARED_TYPES.SHARED_TYPE_NAME%TYPE;
171 L_FORMAT_MASK VARCHAR2(50);
172 BEGIN
173 OPEN CSR_UOM;
174 FETCH CSR_UOM
175 INTO
176 L_BUDGET_MEASUREMENT_TYPE;
177 CLOSE CSR_UOM;
178 IF L_BUDGET_MEASUREMENT_TYPE = 'MONEY' THEN
179 L_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(P_CURRENCY_CODE
180 ,22);
181 ELSE
182 FND_CURRENCY.BUILD_FORMAT_MASK(L_FORMAT_MASK
183 ,22
184 ,2
185 ,NULL
186 ,NULL
187 ,NULL
188 ,NULL);
189 END IF;
190 RETURN L_FORMAT_MASK;
191 END CF_FORMAT_MASK;
192
193 FUNCTION AFTERREPORT RETURN BOOLEAN IS
194 BEGIN
195 RETURN (TRUE);
196 END AFTERREPORT;
197
198 FUNCTION C_REPORT_SUBTITLE_P RETURN VARCHAR2 IS
199 BEGIN
200 RETURN C_REPORT_SUBTITLE;
201 END C_REPORT_SUBTITLE_P;
202
203 FUNCTION CP_ORGANIZATION_NAME_P RETURN VARCHAR2 IS
204 BEGIN
205 RETURN CP_ORGANIZATION_NAME;
206 END CP_ORGANIZATION_NAME_P;
207
208 FUNCTION CP_POSITION_NAME_P RETURN VARCHAR2 IS
209 BEGIN
210 RETURN CP_POSITION_NAME;
211 END CP_POSITION_NAME_P;
212
213 FUNCTION CP_POSITION_TYPE_P RETURN VARCHAR2 IS
214 BEGIN
215 RETURN CP_POSITION_TYPE;
216 END CP_POSITION_TYPE_P;
217
218 FUNCTION CP_CURRENCY_P RETURN VARCHAR2 IS
219 BEGIN
220 RETURN CP_CURRENCY;
221 END CP_CURRENCY_P;
222
223 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
224 BEGIN
225 RETURN C_BUSINESS_GROUP_NAME;
226 END C_BUSINESS_GROUP_NAME_P;
227
228 FUNCTION CP_SESSION_DT_P RETURN DATE IS
229 BEGIN
230 RETURN CP_SESSION_DT;
231 END CP_SESSION_DT_P;
232
233 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
234 X0 VARCHAR2(2000);
235 BEGIN
236 X0 := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
237 RETURN X0;
238 END GET_BUSINESS_GROUP;
239
240 END PQH_PQHWSPBD_XMLP_PKG;
[Home] [Help]