1 PACKAGE BODY PQH_PQHWSOPS_XMLP_PKG AS
2 /* $Header: PQHWSOPSB.pls 120.3 2008/01/11 08:31:45 srikrish noship $ */
3
4 function BeforeReport return boolean is
5
6 val boolean;
7 begin
8 P_START_DATE_1 := NVL(P_START_DATE,TO_DATE('01-01-1990','DD-MM-YYYY'));
9 P_END_DATE_1 := NVL(P_END_DATE,TO_DATE('01-01-2020','DD-MM-YYYY'));
10 p_organization_id_1 := NVL(p_organization_id,202);
11 p_business_group_id_1 := NVL(p_business_group_id,202);
12 p_session_date_1 := NVL(p_session_date,TO_DATE('26-10-2000','DD-MM-YYYY'));
13 p_effective_date_1 := p_effective_date;
14
15 declare
16
17
18 CURSOR csr_org_name IS
19 SELECT name
20 FROM hr_all_organization_units_tl
21 WHERE language = userenv('LANG')
22 AND organization_id = p_organization_id;
23
24 CURSOR csr_posn_type IS
25 SELECT meaning
26 FROM hr_lookups
27 WHERE lookup_type = 'PQH_POSITION_TYPE'
28 AND lookup_code = p_position_type;
29
30 Cursor csr_currency_name IS
31 SELECT name
32 FROM FND_CURRENCIES_ACTIVE_V
33 WHERE currency_code <>'STAT'
34 AND currency_code = p_currency_code;
35
36 CURSOR csr_session_date IS
37 SELECT sysdate
38 FROM dual;
39
40 begin
41
42 val:= BeforePForm;
43 null;
44 /*srw.user_exit('FND SRWINIT');*/null;
45
46
47 OPEN csr_currency_name;
48 FETCH csr_currency_name into cp_currency;
49 CLOSE csr_currency_name;
50
51 OPEN csr_org_name;
52 FETCH csr_org_name INTO cp_organization_name;
53 CLOSE csr_org_name;
54
55
56 OPEN csr_posn_type;
57 FETCH csr_posn_type INTO cp_position_type;
58 CLOSE csr_posn_type;
59
60
61 P_REPORT_TITLE := hr_general.decode_lookup('PQH_REPORT_TITLES','PQHWSOPS');
62
63 c_business_group_name :=
64 hr_reports.get_business_group(p_business_group_id);
65
66 OPEN csr_session_date;
67 FETCH csr_session_date INTO cp_session_dt;
68 CLOSE csr_session_date;
69
70 end;
71
72 return (TRUE);
73 end;
74
75 function cf_1formula(Budget_Unit_id1 in number, actual_amt in number, committed_amt in number) return number is
76 Cursor Shared_types is
77 Select System_Type_cd
78 from Per_Shared_types
79 Where Shared_type_id = Budget_Unit_id1;
80
81 L_Shared_type_Cd Per_Shared_Types.System_Type_Cd%TYPE;
82 begin
83 Open Shared_types;
84 Fetch Shared_types into l_Shared_Type_Cd;
85 Close ShaRED_TYPES;
86 IF L_SHARED_TYPE_CD = 'MONEY' THEN
87 return (NVL(actual_amt,0) + NVL(committed_amt,0) ) ;
88 Else
89 return (NVL(actual_amt,0)) ;
90 End If;
91 end;
92
93 function cf_def_ex_amtformula(budgeted_amt in number, cf_projected_exp in number) return number is
94 begin
95 return (NVL(budgeted_amt,0) - NVL(cf_projected_exp,0));
96 end;
97
98 function BeforePForm return boolean is
99 begin
100 insert into fnd_sessions(session_id, effective_date)
101 values(userenv('sessionid'), p_effective_date);
102
103 return (TRUE);
104 end;
105
106 function cf_act_performula(budgeted_amt in number, actual_amt in number) return number is
107 begin
108 if budgeted_amt = 0 then
109 return 0;
110 else
111 return (NVL(actual_amt,0) / budgeted_amt ) * 100 ;
112 end if;
113 end;
114
115 function cf_com_performula(budgeted_amt in number, committed_amt in number) return number is
116 begin
117 if budgeted_amt = 0 then
118 return 0;
119 else
120 return (NVL(committed_amt,0) / budgeted_amt ) * 100 ;
121 end if;
122 end;
123
124 function cf_proj_performula(budgeted_amt in number, cf_projected_exp in number) return number is
125 begin
126 if budgeted_amt = 0 then
127 return 0;
128 else
129 return (NVL(cf_projected_exp,0) / budgeted_amt ) * 100 ;
130 end if;
131 end;
132
133 function cf_def_ex_performula(budgeted_amt in number, cf_def_ex_amt in number) return number is
134 begin
135 if budgeted_amt = 0 then
136 return 0;
137 else
138 return (NVL(cf_def_ex_amt,0) / budgeted_amt ) * 100 ;
139 end if;
140 end;
141
142 function cf_org_budgeted_amtformula(organization_id1 in number, budget_unit_id in number) return number is
143
144 l_amt number(15,2);
145
146 begin
147
148 l_amt := pqh_mgmt_rpt_pkg.get_org_posn_budget_amt
149 (
150 organization_id1,
151 p_start_date,
152 p_end_date,
153 budget_unit_id,
154 p_currency_code
155 ) ;
156
157 return NVL(l_amt,0);
158 end;
159
160 function cf_org_actual_amtformula(organization_id1 in number, budget_unit_id in number) return number is
161
162 l_amt number(15,2);
163
164 begin
165
166 l_amt := pqh_mgmt_rpt_pkg.get_org_posn_actual_cmmtmnts
167 (
168 organization_id1,
169 p_start_date,
170 p_end_date,
171 budget_unit_id,
172 'A',
173 p_currency_code
174 ) ;
175
176 return NVL(l_amt,0);
177
178 end;
179
180 function cf_org_act_performula(cf_org_budgeted_amt in number, cf_org_actual_amt in number) return number is
181 begin
182 if cf_org_budgeted_amt = 0 then
183 return 0;
184 else
185 return (NVL(cf_org_actual_amt,0) / cf_org_budgeted_amt ) * 100 ;
186 end if;
187 end;
188
189 function cf_org_committed_amtformula(organization_id1 in number, budget_unit_id in number) return number is
190 l_amt number(15,2);
191
192 begin
193
194 l_amt := pqh_mgmt_rpt_pkg.get_org_posn_actual_cmmtmnts
195 (
196 organization_id1,
197 p_start_date,
198 p_end_date,
199 budget_unit_id,
200 'C',
201 p_currency_code
202 ) ;
203
204 return NVL(l_amt,0);
205
206 end;
207
208 function cf_org_com_performula(cf_org_budgeted_amt in number, cf_org_committed_amt in number) return number is
209 begin
210 if cf_org_budgeted_amt = 0 then
211 return 0;
212 else
213 return (NVL(cf_org_committed_amt,0) / cf_org_budgeted_amt ) * 100 ;
214 end if;
215 end;
216
217 function cf_org_projected_expformula(Budget_Unit_id in number, cf_org_actual_amt in number, cf_org_committed_amt in number) return number is
218 Cursor Shared_types is
219 Select System_Type_cd
220 from Per_Shared_types
221 Where Shared_type_id = Budget_Unit_id;
222
223 L_Shared_type_Cd Per_Shared_Types.System_Type_Cd%TYPE;
224 begin
225 Open Shared_types;
226 Fetch Shared_types into l_Shared_Type_Cd;
227 Close ShaRED_TYPES;
228 IF L_SHARED_TYPE_CD = 'MONEY' THEN
229 return (NVL(cf_org_actual_amt,0) + NVL(cf_org_committed_amt,0) ) ;
230 Else
231 return (NVL(cf_org_actual_amt,0) ) ;
232 End If;
233 end;
234
235 function cf_org_proj_performula(cf_org_budgeted_amt in number, cf_org_projected_exp in number) return number is
236 begin
237 if cf_org_budgeted_amt = 0 then
238 return 0;
239 else
240 return (NVL(cf_org_projected_exp,0) / cf_org_budgeted_amt ) * 100 ;
241 end if;
242 end;
243
244 function cf_org_def_ex_amtformula(cf_org_budgeted_amt in number, cf_org_projected_exp in number) return number is
245 begin
246 return (NVL(cf_org_budgeted_amt,0) - NVL(cf_org_projected_exp,0));
247 end;
248
249 function cf_org_def_ex_performula(cf_org_budgeted_amt in number, cf_org_def_ex_amt in number) return number is
250 begin
251 if cf_org_budgeted_amt = 0 then
252 return 0;
253 else
254 return (NVL(cf_org_def_ex_amt,0) / cf_org_budgeted_amt ) * 100 ;
255 end if;
256 end;
257
258 function cf_format_mask1(budget_unit_id in number) return char is
259 cursor csr_uom is
260 select system_type_cd
261 from per_shared_types
262 where shared_type_id = budget_unit_id and
263 lookup_type = 'BUDGET_MEASUREMENT_TYPE';
264
265 l_budget_measurement_type per_shared_types.shared_type_name%TYPE;
266 l_format_mask varchar2(50);
267 BEGIN
268 open csr_uom;
269 fetch csr_uom into l_budget_measurement_type;
270 close csr_uom;
271
272 if l_budget_measurement_type = 'MONEY' then
273 l_format_mask := fnd_currency.get_format_mask(p_currency_code,22);
274 else
275 fnd_currency.build_format_mask(l_format_mask,22,2,null,null,null,null);
276 end if;
277 return l_format_mask;
278 end;
279
280 function cf_format_mask2(budget_unit_id1 in number) return char is
281 cursor csr_uom is
282 select system_type_cd
283 from per_shared_types
284 where shared_type_id = budget_unit_id1 and
285 lookup_type = 'BUDGET_MEASUREMENT_TYPE';
286
287 l_budget_measurement_type per_shared_types.shared_type_name%TYPE;
288 l_format_mask varchar2(50);
289 BEGIN
290 open csr_uom;
291 fetch csr_uom into l_budget_measurement_type;
292 close csr_uom;
293
294 if l_budget_measurement_type = 'MONEY' then
295 l_format_mask := fnd_currency.get_format_mask(p_currency_code,22);
296 else
297 fnd_currency.build_format_mask(l_format_mask,22,2,null,null,null,null);
298 end if;
299 return l_format_mask;
300 end;
301
302 function AfterReport return boolean is
303 begin
304 /*srw.user_exit('FND SRWEXIT');*/null;
305
306 return (TRUE);
307 end;
308
309 --Functions to refer Oracle report placeholders--
310
311 Function C_REPORT_SUBTITLE_p return varchar2 is
312 Begin
313 return C_REPORT_SUBTITLE;
314 END;
315 Function CP_organization_name_p return varchar2 is
316 Begin
317 return CP_organization_name;
318 END;
319 Function CP_position_type_p return varchar2 is
320 Begin
321 return CP_position_type;
322 END;
323 Function CP_currency_p return varchar2 is
324 Begin
325 return CP_currency;
326 END;
327 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
328 Begin
329 return C_BUSINESS_GROUP_NAME;
330 END;
331 Function cp_session_dt_p return date is
332 Begin
333 return cp_session_dt;
334 END;
335 END PQH_PQHWSOPS_XMLP_PKG ;
[Home] [Help]