1 PACKAGE BODY PQH_PQHWSBPR_XMLP_PKG AS
2 /* $Header: PQHWSBPRB.pls 120.2 2007/12/21 19:31:13 vjaganat noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 l_dummy boolean;
5 BEGIN
6 DECLARE
7 CURSOR CSR_BDGT_NO IS
8 SELECT
9 BUDGET_NAME,
10 VERSION_NUMBER
11 FROM
12 PQH_BUDGETS BGT,
13 PQH_BUDGET_VERSIONS BVR
14 WHERE BGT.BUDGET_ID = BVR.BUDGET_ID
15 AND BGT.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
16 AND BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
17 CURSOR CSR_CURR_CODE IS
18 SELECT
19 BG.CURRENCY_CODE
20 FROM
21 PER_BUSINESS_GROUPS BG,
22 PQH_BUDGETS BGT,
23 PQH_BUDGET_VERSIONS BVR
24 WHERE BGT.BUDGET_ID = BVR.BUDGET_ID
25 AND BVR.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
26 AND BGT.BUSINESS_GROUP_ID = BG.BUSINESS_GROUP_ID;
27 CURSOR CSR_BGT_CURR IS
28 SELECT
29 BGT.CURRENCY_CODE
30 FROM
31 PQH_BUDGETS BGT,
32 PQH_BUDGET_VERSIONS BVR
33 WHERE BGT.BUDGET_ID = BVR.BUDGET_ID
34 AND BVR.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
35 CURSOR CSR_SESSION_DATE IS
36 SELECT
37 sysdate
38 FROM
39 DUAL;
40 CURSOR CSR_RECORD_VALUE IS
41 SELECT
42 MEANING
43 FROM
44 HR_LOOKUPS
45 WHERE LOOKUP_TYPE = 'PQH_BUDGET_DETAIL_RECORD_TYPE'
46 AND LOOKUP_CODE = P_RECORD_TYPE;
47 L_BG_CURR_CODE VARCHAR2(150) := '';
48 L_BUDGET_CURR VARCHAR2(150) := '';
49 BEGIN
50 l_dummy := BEFOREPFORM;
51 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
52 OPEN CSR_BDGT_NO;
53 FETCH CSR_BDGT_NO
54 INTO
55 CP_BUDGET_NAME
56 ,CP_BUDGET_VERSION;
57 CLOSE CSR_BDGT_NO;
58 OPEN CSR_RECORD_VALUE;
59 FETCH CSR_RECORD_VALUE
60 INTO
61 CP_RECORD_VALUE;
62 CLOSE CSR_RECORD_VALUE;
63 P_REPORT_TITLE := HR_GENERAL.DECODE_LOOKUP('PQH_REPORT_TITLES'
64 ,'PQHWSBPR');
65 C_BUSINESS_GROUP_NAME := GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
66 OPEN CSR_BGT_CURR;
67 FETCH CSR_BGT_CURR
68 INTO
69 L_BUDGET_CURR;
70 CLOSE CSR_BGT_CURR;
71 IF L_BUDGET_CURR IS NOT NULL THEN
72 CP_CURRENCY_CODE := L_BUDGET_CURR;
73 ELSE
74 OPEN CSR_CURR_CODE;
75 FETCH CSR_CURR_CODE
76 INTO
77 L_BG_CURR_CODE;
78 CLOSE CSR_CURR_CODE;
79 CP_CURRENCY_CODE := L_BG_CURR_CODE;
80 END IF;
81 OPEN CSR_SESSION_DATE;
82 FETCH CSR_SESSION_DATE
83 INTO
84 CP_SESSION_DT;
85 CLOSE CSR_SESSION_DATE;
86 END;
87 RETURN (TRUE);
88 END BEFOREREPORT;
89
90 FUNCTION CF_1FORMULA(BUDGET_UNIT_ID IN NUMBER
91 ,BUDGET_PERIOD_ACTUAL_VALUE IN NUMBER
92 ,BUDGET_PERIOD_CMMTMNT_VALUE IN NUMBER) RETURN NUMBER IS
93 CURSOR SHARED_TYPES IS
94 SELECT
95 SYSTEM_TYPE_CD
96 FROM
97 PER_SHARED_TYPES
98 WHERE SHARED_TYPE_ID = BUDGET_UNIT_ID;
99 L_SHARED_TYPE_CD PER_SHARED_TYPES.SYSTEM_TYPE_CD%TYPE;
100 BEGIN
101 OPEN SHARED_TYPES;
102 FETCH SHARED_TYPES
103 INTO
104 L_SHARED_TYPE_CD;
105 CLOSE SHARED_TYPES;
106 IF NVL(L_SHARED_TYPE_CD
107 ,'XX') = 'MONEY' THEN
108 RETURN (NVL(BUDGET_PERIOD_ACTUAL_VALUE
109 ,0) + NVL(BUDGET_PERIOD_CMMTMNT_VALUE
110 ,0));
111 ELSE
112 RETURN (NVL(BUDGET_PERIOD_ACTUAL_VALUE
113 ,0));
114 END IF;
115 END CF_1FORMULA;
116
117 FUNCTION CF_DEF_EX_AMTFORMULA(BUDGET_PERIOD_BUDGETED_VALUE IN NUMBER
118 ,CF_PROJECTED_EXP IN NUMBER) RETURN NUMBER IS
119 BEGIN
120 RETURN (NVL(BUDGET_PERIOD_BUDGETED_VALUE
121 ,0) - NVL(CF_PROJECTED_EXP
122 ,0));
123 END CF_DEF_EX_AMTFORMULA;
124
125 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
126 BEGIN
127 INSERT INTO FND_SESSIONS
128 (SESSION_ID
129 ,EFFECTIVE_DATE)
130 VALUES (USERENV('sessionid')
131 ,P_EFFECTIVE_DATE);
132 RETURN (TRUE);
133 END BEFOREPFORM;
134
135 FUNCTION CF_ACT_PERFORMULA(BUDGET_PERIOD_BUDGETED_VALUE IN NUMBER
136 ,BUDGET_PERIOD_ACTUAL_VALUE IN NUMBER) RETURN NUMBER IS
137 BEGIN
138 IF BUDGET_PERIOD_BUDGETED_VALUE = 0 THEN
139 RETURN 0;
140 ELSE
141 RETURN (NVL(BUDGET_PERIOD_ACTUAL_VALUE
142 ,0) / BUDGET_PERIOD_BUDGETED_VALUE) * 100;
143 END IF;
144 END CF_ACT_PERFORMULA;
145
146 FUNCTION CF_COM_PERFORMULA(BUDGET_PERIOD_BUDGETED_VALUE IN NUMBER
147 ,BUDGET_PERIOD_CMMTMNT_VALUE IN NUMBER) RETURN NUMBER IS
148 BEGIN
149 IF BUDGET_PERIOD_BUDGETED_VALUE = 0 THEN
150 RETURN 0;
151 ELSE
152 RETURN (NVL(BUDGET_PERIOD_CMMTMNT_VALUE
153 ,0) / BUDGET_PERIOD_BUDGETED_VALUE) * 100;
154 END IF;
155 END CF_COM_PERFORMULA;
156
157 FUNCTION CF_PROJ_PERFORMULA(BUDGET_PERIOD_BUDGETED_VALUE IN NUMBER
158 ,CF_PROJECTED_EXP IN NUMBER) RETURN NUMBER IS
159 BEGIN
160 IF BUDGET_PERIOD_BUDGETED_VALUE = 0 THEN
161 RETURN 0;
162 ELSE
163 RETURN (NVL(CF_PROJECTED_EXP
164 ,0) / BUDGET_PERIOD_BUDGETED_VALUE) * 100;
165 END IF;
166 END CF_PROJ_PERFORMULA;
167
168 FUNCTION CF_DEF_EX_PERFORMULA(BUDGET_PERIOD_BUDGETED_VALUE IN NUMBER
169 ,CF_DEF_EX_AMT IN NUMBER) RETURN NUMBER IS
170 BEGIN
171 IF BUDGET_PERIOD_BUDGETED_VALUE = 0 THEN
172 RETURN 0;
173 ELSE
174 RETURN (NVL(CF_DEF_EX_AMT
175 ,0) / BUDGET_PERIOD_BUDGETED_VALUE) * 100;
176 END IF;
177 END CF_DEF_EX_PERFORMULA;
178
179 FUNCTION CF_FORMAT_MASKFORMULA(BUDGET_UNIT_ID IN NUMBER) RETURN CHAR IS
180 BUDGET_UNIT_CD VARCHAR2(30);
181 L_FORMAT_MASK VARCHAR2(40);
182 BEGIN
183 SELECT
184 SYSTEM_TYPE_CD
185 INTO
186 BUDGET_UNIT_CD
187 FROM
188 PER_SHARED_TYPES
189 WHERE SHARED_TYPE_ID = BUDGET_UNIT_ID;
190 IF BUDGET_UNIT_CD = 'MONEY' THEN
191 L_FORMAT_MASK := FND_CURRENCY.GET_FORMAT_MASK(CP_CURRENCY_CODE
192 ,22);
193 ELSE
194 FND_CURRENCY.BUILD_FORMAT_MASK(L_FORMAT_MASK
195 ,22
196 ,2
197 ,NULL
198 ,NULL
199 ,NULL
200 ,NULL);
201 END IF;
202 RETURN L_FORMAT_MASK;
203 END CF_FORMAT_MASKFORMULA;
204
205 FUNCTION AFTERREPORT RETURN BOOLEAN IS
206 BEGIN
207 RETURN (TRUE);
208 END AFTERREPORT;
209
210 FUNCTION C_REPORT_SUBTITLE_P RETURN VARCHAR2 IS
211 BEGIN
212 RETURN C_REPORT_SUBTITLE;
213 END C_REPORT_SUBTITLE_P;
214
215 FUNCTION CP_BUDGET_NAME_P RETURN VARCHAR2 IS
216 BEGIN
217 RETURN CP_BUDGET_NAME;
218 END CP_BUDGET_NAME_P;
219
220 FUNCTION CP_BUDGET_VERSION_P RETURN NUMBER IS
221 BEGIN
222 RETURN CP_BUDGET_VERSION;
223 END CP_BUDGET_VERSION_P;
224
225 FUNCTION CP_CURRENCY_CODE_P RETURN VARCHAR2 IS
226 BEGIN
227 RETURN CP_CURRENCY_CODE;
228 END CP_CURRENCY_CODE_P;
229
230 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
231 BEGIN
232 RETURN C_BUSINESS_GROUP_NAME;
233 END C_BUSINESS_GROUP_NAME_P;
234
235 FUNCTION CP_SESSION_DT_P RETURN DATE IS
236 BEGIN
237 RETURN CP_SESSION_DT;
238 END CP_SESSION_DT_P;
239
240 FUNCTION CP_RECORD_VALUE_P RETURN VARCHAR2 IS
241 BEGIN
242 RETURN CP_RECORD_VALUE;
243 END CP_RECORD_VALUE_P;
244
245 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
246 X0 VARCHAR2(2000);
247 BEGIN
248 X0 := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
249 RETURN X0;
250 END GET_BUSINESS_GROUP;
251
252 END PQH_PQHWSBPR_XMLP_PKG;
[Home] [Help]