[Home] [Help]
PACKAGE BODY: APPS.FII_GL_COST_CENTER_PKG2
Source
1 PACKAGE BODY fii_gl_cost_center_pkg2 AS
2 /* $Header: FIIGLC2B.pls 120.66 2006/04/22 00:23:57 mmanasse noship $ */
3
4 FUNCTION get_revexp_cc (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, l_fin_type IN VARCHAR2) return VARCHAR2
5 IS
6
7 revexp_cc_rec BIS_QUERY_ATTRIBUTES;
8 sqlstmt VARCHAR2(32000);
9 l_prior_or_budget VARCHAR2(5000);
10 l_hint VARCHAR2(300);
11 l_url VARCHAR2(300);
12 l_url2 VARCHAR2(300);
13 l_dim_flag VARCHAR2(1);
14 BEGIN
15
16 fii_gl_util_pkg.reset_globals;
17 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
18 fii_gl_util_pkg.get_bitmasks;
19 fii_gl_util_pkg.g_fin_type := l_fin_type;
20 fii_gl_util_pkg.get_viewby_sql;
21
22 IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
23 l_prior_or_budget :='
24 (SUM(SUM(CY_ACTUAL)) over() - SUM(SUM(CY_BUDGET)) over()) /
25 ABS(NULLIF(SUM(SUM(CY_BUDGET)) over(),0)) * 100 FII_ATTRIBUTE12,
26 SUM(CY_BUDGET) FII_MEASURE3,';
27 ELSE
28 l_prior_or_budget :='
29 (SUM(SUM(CY_ACTUAL)) over() - SUM(SUM(PY_ACTUAL)) over()) /
30 ABS(NULLIF(SUM(SUM(PY_ACTUAL)) over(),0)) * 100 FII_ATTRIBUTE12,
31 SUM(PY_ACTUAL) FII_MEASURE3, ';
32 END IF;
33
34 l_hint := '/*+ use_nl(f cat cal mgr per lob) ordered */';
35 -- ----------------------------------------------------------------
36 -- FII_MEASURE1 = Line of Business
37 -- FII_MEASURE10 = Line of Business id (this is added for pass by id uptake)
38 -- FII_MEASURE2,9 = Current amounts
39 -- FII_MEASURE3 = Prior amounts
40 -- FII_MEASURE5 = Forecast amounts
41 -- FII_MEASURE7 = Budget amounts
42 -- ----------------------------------------------------------------
43 -- DEBUG: Why do we select same thing into FII_MEASURE2 and
44 -- FII_MEASURE9? Both attribute codes should map to alias of
45 -- FII_MEASURE2. Check original package.
46 -- DEBUG: Note we cannot control order by in PMV anymore for these type of reports
47 -- unless it's passed into the PLSQL table. Need to raise as concern.
48
49 fii_gl_util_pkg.get_lob_pmv_sql;
50 fii_gl_util_pkg.get_cat_pmv_sql;
51 fii_gl_util_pkg.get_mgr_pmv_sql;
52
53 IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
54 l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
55 ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
56 l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
57 ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
58 l_dim_flag := fii_gl_util_pkg.g_fincat_is_leaf;
59 ELSE
60 l_dim_flag := 'Y';
61 END IF;
62
63 IF fii_gl_util_pkg.g_fin_type = 'R' THEN
64 l_url := 'pFunctionName=FII_GL_REV_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
65 l_url2 := 'pFunctionName=FII_GL_REV_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&FII_DIM5=FII_MEASURE10&pParamIds=Y';
66 ELSIF fii_gl_util_pkg.g_fin_type = 'OE' THEN
67 l_url := 'pFunctionName=FII_GL_EXP_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
68 l_url2 := 'pFunctionName=FII_GL_EXP_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&FII_DIM5=FII_MEASURE10&pParamIds=Y';
69 ELSIF fii_gl_util_pkg.g_fin_type = 'CGS' THEN
70 l_url := 'pFunctionName=FII_GL_COR_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
71 l_url2 := 'pFunctionName=FII_GL_COR_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&FII_DIM5=FII_MEASURE10&pParamIds=Y';
72 END IF;
73
74 IF fii_gl_util_pkg.g_mgr_id = -99999 then
75 l_prior_or_budget :='
76 NULL FII_ATTRIBUTE12,
77 NULL FII_MEASURE3,';
78 sqlstmt := 'select NULL VIEWBY,
79 NULL FII_MEASURE1,
80 NULL FII_MEASURE10,
81 NULL VIEWBYID,
82 NULL FII_MEASURE2,
83 NULL FII_MEASURE9,
84 NULL FII_MEASURE5,
85 NULL FII_MEASURE7,
86 NULL FII_MEASURE11,
87 NULL FII_ATTRIBUTE11,
88 NULL FII_ATTRIBUTE13,
89 NULL FII_ATTRIBUTE14,'||l_prior_or_budget||'
90 NULL FII_MEASURE12,
91 NULL FII_MEASURE14,
92 NULL FII_MEASURE15
93 FROM dual where 1= 2';
94 ELSE
95
96 sqlstmt := '
97 select decode(:LOB_ID, f.viewby_id,decode('''||l_dim_flag||''',''Y'','||fii_gl_util_pkg.g_viewby_value||', '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG), '||fii_gl_util_pkg.g_viewby_value||') VIEWBY,
98 to_number(NULL) FII_MEASURE1,
99 f.viewby_id FII_MEASURE10,
100 f.viewby_id VIEWBYID,
101 SUM(CY_ACTUAL) FII_MEASURE2,
102 SUM(CY_ACTUAL) FII_MEASURE9,
103 SUM(CY_FORECAST) FII_MEASURE5,
104 SUM(CY_BUDGET) FII_MEASURE7,
105 SUM(PY_SPER_END) FII_MEASURE11,
106 SUM(SUM(CY_ACTUAL)) over() FII_ATTRIBUTE11,
107 SUM(SUM(CY_FORECAST)) over() FII_ATTRIBUTE13,
108 (SUM(SUM(CY_FORECAST)) over() - SUM(SUM(PY_SPER_END)) over()) /
109 ABS(NULLIF(SUM(SUM(PY_SPER_END)) over(),0)) * 100 FII_ATTRIBUTE14,'||l_prior_or_budget||'
110 SUM(to_number(NULL)) FII_MEASURE12,
111 DECODE(:LOB_ID, f.viewby_id, '''', '''||l_url||''') FII_MEASURE14,
112 DECODE(:LOB_ID, f.viewby_id, '''', '''||l_url2||''') FII_MEASURE15
113 FROM '||fii_gl_util_pkg.g_viewby_from_clause||',
114 (select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
115 SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
116 then f.actual_g
117 else to_number(NULL) end) CY_ACTUAL,
118 SUM(case when bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id
119 then f.forecast_g
120 else to_number(NULL) end) CY_FORECAST,
121 SUM(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
122 then f.budget_g
123 else to_number(NULL) end) CY_BUDGET,
124 to_number(NULL) PY_SPER_END,
125 to_number(NULL) PY_ACTUAL
126 FROM fii_time_rpt_struct cal
127 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
128 where cal.report_date = &BIS_CURRENT_ASOF_DATE
129 and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||'
130 and f.gid = 0
131 and cal.period_type_id = f.period_type_id
132 and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
133 group by '||fii_gl_util_pkg.g_viewby_id||'
134 union all
135 select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
136 to_number(NULL) CY_ACTUAL,
137 to_number(NULL) CY_FORECAST,
138 to_number(NULL) CY_BUDGET,
139 SUM(case when bitand(cal.record_type_id, :ENT_PERIOD_TYPE) = cal.record_type_id
140 then f.actual_g
141 else to_number(NULL) end) PY_SPER_END,
142 SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
143 then f.actual_g
144 else to_number(NULL) end) PY_ACTUAL
145 FROM fii_time_rpt_struct cal
146 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
147 where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
148 and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join|| '
149 and f.gid = 0
150 and cal.period_type_id = f.period_type_id
151 and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
152 group by '||fii_gl_util_pkg.g_viewby_id||') f
153 where '||fii_gl_util_pkg.g_viewby_join||'
154 group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
155 order by NVL(FII_MEASURE2, -9999999999) desc';
156 END IF;
157 return sqlstmt;
158
159 END get_revexp_cc;
160
161 PROCEDURE get_exp_by_cat (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
162 exp_by_cat_sql out NOCOPY VARCHAR2,
163 exp_by_cat_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
164 IS
165
166 sqlstmt VARCHAR2(32000);
167 l_time_comp VARCHAR2(20);
168 l_prior_or_budget VARCHAR2(3000);
169 l_hint VARCHAR2(300);
170 l_url_summary VARCHAR2(300);
171 l_url_trend VARCHAR2(300);
172
173 BEGIN
174 fii_gl_util_pkg.reset_globals;
175 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
176 fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';
177 fii_gl_util_pkg.g_fin_type := 'OE';
178 fii_gl_util_pkg.get_bitmasks;
179 fii_gl_util_pkg.get_viewby_sql;
180 fii_gl_util_pkg.get_mgr_pmv_sql;
181 fii_gl_util_pkg.get_cat_pmv_sql;
182
183
184 IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
185 l_prior_or_budget :='
186 (SUM(SUM(CY_ACTUAL)) over() - SUM(SUM(CY_BUDGET)) over()) /
187 ABS(NULLIF(SUM(SUM(CY_BUDGET)) over(),0)) * 100 FII_ATTRIBUTE11,
188 SUM(CY_BUDGET) FII_MEASURE3,';
189
190
191 ELSE
192 l_prior_or_budget :='
193 (SUM(SUM(CY_ACTUAL)) over() - SUM(SUM(PY_ACTUAL)) over()) /
194 ABS(NULLIF(SUM(SUM(PY_ACTUAL)) over(),0)) * 100 FII_ATTRIBUTE11,
195 SUM(PY_ACTUAL) FII_MEASURE3, ';
196 END IF;
197
198 l_url_summary := 'pFunctionName=FII_GL_EXP_LOBMGRCC1&FII_DIM7=FII_MEASURE9&VIEW_BY=VIEW_BY&pParamIds=Y';
199 --drill across url on category column
200 l_url_trend := 'pFunctionName=FII_GL_EXP_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&FII_DIM3=FII_MEASURE9&pParamIds=Y';
201 --drill across url on XTD column
202
203 l_hint := '/*+ ordered use_nl(cal) */';
204
205 /******************************************************************
206 * FII_MEASURE2 = Current amounts, FII_MEASURE3 = Prior amounts *
207 * FII_MEASURE5 = Forecast amounts, FII_MEASURE7 = Budget amounts *
208 ******************************************************************/
209
210 IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
211
212 l_prior_or_budget :='NULL FII_ATTRIBUTE11,
213 NULL FII_MEASURE3, ';
214 sqlstmt := 'select NULL VIEWBY,
215 NULL FII_MEASURE1,
216 NULL FII_MEASURE9,
217 NULL FII_MEASURE14,
218 NULL FII_MEASURE2,
219 NULL FII_MEASURE5,
220 NULL FII_MEASURE7,
221 NULL FII_MEASURE11,
222 NULL FII_ATTRIBUTE14,
223 NULL FII_ATTRIBUTE13,
224 NULL FII_ATTRIBUTE12,
225 '||l_prior_or_budget||'
226 NULL FII_MEASURE12,
227 NULL FII_MEASURE13,
228 NULL FII_MEASURE15
229 FROM DUAL
230 WHERE 1=2';
231
232 ELSE
233
234 sqlstmt := 'select
235 cat_tl2.description VIEWBY,
236 DECODE(f.viewby_id2 , f.viewby_id, '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG, '||fii_gl_util_pkg.g_viewby_value||') FII_MEASURE1,
237 f.viewby_id FII_MEASURE9,
238 f.viewby_id2 FII_MEASURE14,
239 SUM(CY_ACTUAL) FII_MEASURE2,
240 SUM(CY_FORECAST) FII_MEASURE5,
241 SUM(CY_BUDGET) FII_MEASURE7,
242 SUM(PY_SPER_END) FII_MEASURE11,
243 SUM(SUM(CY_ACTUAL)) over() FII_ATTRIBUTE14,
244 (SUM(SUM(CY_FORECAST)) over() - SUM(SUM(CY_BUDGET)) over()) /
245 NULLIF(SUM(SUM(CY_BUDGET)) over(),0) * 100 FII_ATTRIBUTE13,
246 SUM(SUM(CY_ACTUAL)) over() /
247 NULLIF(SUM(SUM(CY_FORECAST)) over(),0) * 100 FII_ATTRIBUTE12,
248 '||l_prior_or_budget||'SUM(to_number(NULL)) FII_MEASURE12,
249 DECODE( f.viewby_id2 , f.viewby_id, '''' , '''||l_url_summary||''' ) FII_MEASURE13,
250 DECODE( f.viewby_id2 , f.viewby_id, '''' , '''||l_url_trend||''' ) FII_MEASURE15
251 /* Disable drills on the category and XTD column when parent category is same as child category */
252 FROM
253 '||fii_gl_util_pkg.g_viewby_from_clause||',
254 fnd_flex_values_tl cat_tl2,
255 (select
256 '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
257 cat_hier.next_level_fin_cat_id VIEWBY_ID2,
258 SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
259 then f.actual_g
260 else to_number(NULL) end) CY_ACTUAL,
261 SUM(case when bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id
262 then f.forecast_g
263 else to_number(NULL) end) CY_FORECAST,
264 SUM(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
265 then f.budget_g
266 else to_number(NULL) end) CY_BUDGET,
267 to_number(NULL) PY_SPER_END,
268 to_number(NULL) PY_ACTUAL
269 FROM fii_time_rpt_struct cal,
270 fii_fin_item_hierarchies cat_hier
271 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
272 where cal.report_date = &BIS_CURRENT_ASOF_DATE
273 and f.fin_category_id = cat_hier.child_fin_cat_id
274 and ( f.parent_fin_category_id = cat_hier.NEXT_LEVEL_FIN_CAT_ID or (cat_hier.next_level_is_leaf = ''Y''))
275 and cat_hier.child_level <= 2 + cat_hier.parent_level
276 -- Modified join to fix bug 3562244. This join will let us pick up budgets and actuals that might
277 -- be loaded at summary nodes
278 and cal.time_id = f.time_id
279 '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join2||'
280 and cal.period_type_id = f.period_type_id
281 and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
282 group by cat_hier.next_level_fin_cat_id, '||fii_gl_util_pkg.g_viewby_id||'
283 union all
284 select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
285 cat_hier.next_level_fin_cat_id VIEWBY_ID2,
286 to_number(NULL) CY_ACTUAL,
287 to_number(NULL) CY_FORECAST,
288 to_number(NULL) CY_BUDGET,
289 SUM(case when bitand(cal.record_type_id, :ENT_PERIOD_TYPE) = cal.record_type_id
290 then f.actual_g
291 else to_number(NULL) end) PY_SPER_END,
292 SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
293 then f.actual_g
294 else to_number(NULL) end) PY_ACTUAL
295 FROM fii_time_rpt_struct cal,
296 fii_fin_item_hierarchies cat_hier
297 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
298 where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
299 and f.fin_category_id = cat_hier.child_fin_cat_id
300 and ( f.parent_fin_category_id = cat_hier.NEXT_LEVEL_FIN_CAT_ID or (cat_hier.next_level_is_leaf = ''Y''))
301 and cat_hier.child_level <= 2 + cat_hier.parent_level
302 -- Modified join to fix bug 3562244. This join will let us pick up budgets and actuals that might
303 -- be loaded at summary nodes
304 and cal.time_id = f.time_id
305 '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join2|| '
306 and cal.period_type_id = f.period_type_id
307 and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
308 group by cat_hier.next_level_fin_cat_id, '||fii_gl_util_pkg.g_viewby_id||') f
309 where '||fii_gl_util_pkg.g_viewby_join||'
310 and cat_tl2.flex_value_id = f.viewby_id2
311 and cat_tl2.language = userenv(''LANG'')
312 group by cat_tl2.description, '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id, f.viewby_id2
313 order by cat_tl2.description, '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';
314
315 END IF;
316
317 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_by_cat_sql, exp_by_cat_output);
318
319
320 END get_exp_by_cat;
321
322 -- Function
323 -- get_exp_by_cat
324 --
325 -- Purpose
326 -- Returns data for the Expense Summary by Category report.
327 --
328 -- History
329 -- 10-MAY-02 M Bedekar Created
330 --
331 --
332
333
334 PROCEDURE get_exp_cc (
335 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, exp_cc_sql out NOCOPY VARCHAR2,
336 exp_cc_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
337 l_fin_category VARCHAR2(2);
338 sqlstmt VARCHAR2(32000);
339 BEGIN
340
341 l_fin_category := 'OE';
342
343 sqlstmt := get_revexp_cc(p_page_parameter_tbl, l_fin_category );
344
345 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_cc_sql, exp_cc_output);
346
347 END get_exp_cc;
348
349 PROCEDURE get_rev_cc (
350 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, rev_cc_sql out NOCOPY VARCHAR2,
351 rev_cc_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
352 l_fin_category VARCHAR2(1);
353 sqlstmt VARCHAR2(32000);
354 BEGIN
355
356 l_fin_category := 'R';
357
358 sqlstmt := get_revexp_cc(p_page_parameter_tbl, l_fin_category );
359
360 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, rev_cc_sql, rev_cc_output);
361
362
363 END get_rev_cc;
364
365 PROCEDURE get_cogs_cc (
366 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, cgs_cc_sql out NOCOPY VARCHAR2,
367 cgs_cc_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
368 l_fin_category VARCHAR2(3);
369 sqlstmt VARCHAR2(32000);
370 BEGIN
371
372 l_fin_category := 'CGS';
373
374 sqlstmt := get_revexp_cc(p_page_parameter_tbl, l_fin_category );
375
376 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, cgs_cc_sql, cgs_cc_output);
377
378 END get_cogs_cc;
379
380
381 PROCEDURE get_cont_marg(
382 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
383 cont_marg_sql out NOCOPY VARCHAR2,
384 cont_marg_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL, p_opera_marg IN Char DEFAULT 'N') IS
385
386 sqlstmt VARCHAR2(32000);
387 l_prior_or_budget VARCHAR2(10000);
388 l_hint VARCHAR2(300);
389 sqlstmt1 VARCHAR2(5000);
390 cy_act_exp_select VARCHAR2(100) := NULL;
391 l_prior VARCHAR2(20) := NULL;
392 l_record_type VARCHAR2(20) := NULL;
393 l_amt VARCHAR2(20) := NULL;
394 l_label VARCHAR2(20) := NULL;
395 cy_prior_exp_select VARCHAR2(100) := NULL;
396 l_subtractor VARCHAR2(100) := NULL;
397 l_subtractor2 VARCHAR2(100) := NULL;
398 l_subtractor3 VARCHAR2(100) := NULL;
399 l_subtractor4 VARCHAR2(100) := NULL;
400 l_url VARCHAR2(300) := NULL;
401 l_dim_flag VARCHAR2(1);
402
403 BEGIN
404 fii_gl_util_pkg.reset_globals;
405 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
406 fii_gl_util_pkg.get_bitmasks;
407 fii_gl_util_pkg.get_viewby_sql;
408 fii_gl_util_pkg.get_lob_pmv_sql;
409 fii_gl_util_pkg.get_mgr_pmv_sql;
410 fii_gl_util_pkg.get_ccc_pmv_sql;
411
412 IF p_opera_marg = 'Y' THEN
413 fii_gl_util_pkg.g_fin_type := 'OM';
414 l_url := 'pFunctionName=FII_GL_OPS_LOB_MGR1&FII_DIM8=FII_MEASURE6&VIEW_BY=VIEW_BY&pParamIds=Y';
415 ELSE fii_gl_util_pkg.g_fin_type := 'GM';
416 l_url := 'pFunctionName=FII_GL_MAR_LOB_MGR1&FII_DIM8=FII_MEASURE6&VIEW_BY=VIEW_BY&pParamIds=Y';
417 END IF;
418
419 fii_gl_util_pkg.get_cat_pmv_sql;
420
421 -- since there is no weekly forecast, NULL will be returned if week period type is selected
422
423 IF p_opera_marg = 'Y' THEN
424 cy_act_exp_select := ' NVL(SUM(CY_ACT_EXP), 0) FII_ATTRIBUTE3,';
425 l_subtractor2 := '(NVL(SUM(SUM(CY_ACT_CGS)) over(), 0) + NVL(SUM(SUM(CY_ACT_EXP)) over(), 0))';
426 l_subtractor3 := '(NVL(SUM(CY_ACT_CGS), 0) + NVL(SUM(CY_ACT_EXP), 0))';
427 ELSE
428 l_subtractor2 := ' NVL(SUM(SUM(CY_ACT_CGS)) over(), 0) ';
429 l_subtractor3 := ' NVL(SUM(CY_ACT_CGS), 0) ';
430 END IF;
431
432 sqlstmt1 := ' NVL(SUM(CY_ACT_REV), 0) FII_MEASURE2,
433 NVL(SUM(CY_ACT_CGS), 0) FII_MEASURE3, '||cy_act_exp_select||'
434 (NVL(SUM(CY_ACT_REV), 0) - '||l_subtractor3||')/
435 ABS(NULLIF(SUM(CY_ACT_REV), 0)) * 100 FII_MEASURE11,
436 NVL(SUM(SUM(CY_ACT_REV)) over(), 0) - '||l_subtractor2||' FII_ATTRIBUTE11,
437 (NVL(SUM(SUM(CY_ACT_REV)) over(), 0) - '||l_subtractor2||') /
438 ABS(NULLIF(SUM(SUM(CY_ACT_REV)) over(),0)) * 100 FII_ATTRIBUTE12,';
439
440 IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
441
442 l_prior := 'CY_BUD';
443 l_record_type := ':BUDGET_PERIOD_TYPE';
444 l_amt := 'actual_g';
445 IF p_opera_marg = 'Y' THEN
446 l_label := 'CY_BUD_EXP';
447 END IF;
448
449 ELSE
450
451 l_prior := 'PY_ACT';
452 l_record_type := ':ACTUAL_PERIOD_TYPE';
453 l_amt := 'actual_g';
454 IF p_opera_marg = 'Y' THEN
455 l_label := 'PY_ACT_EXP';
456 END IF;
457
458 END IF;
459
460
461 IF p_opera_marg = 'Y' THEN
462
463 cy_prior_exp_select := 'NVL(SUM('||l_label||'), 0) FII_ATTRIBUTE4,';
464 l_subtractor := '(NVL(SUM(SUM('||l_prior||'_EXP)) over(), 0) + NVL(SUM(SUM('||l_prior||'_CGS)) over(), 0) )';
465 l_subtractor4 := '(NVL(SUM('||l_prior||'_CGS), 0) + NVL(SUM('||l_prior||'_EXP), 0))';
466 ELSE
467 l_subtractor := 'NVL(SUM(SUM('||l_prior||'_CGS)) over(), 0)';
468 l_subtractor4 := ' NVL(SUM('||l_prior||'_CGS), 0) ';
469
470 END IF;
471
472 IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
473 l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
474 ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
475 l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
476 ELSE
477 l_dim_flag := 'Y';
478 END IF;
479
480 l_hint := '/*+ index(mgr.HRI_CS_SUPH, HRI_CS_SUPH_N5) use_nl(f cat cal mgr per lob lob2) ordered */';
481
482 -- ----------------------------------------------------------------
483 -- FII_MEASURE2 = Forecasted Rev amounts
484 -- FII_MEASURE3 = Forecasted COR amounts
485 -- FII_MEASURE4 = Prior Revenue amounts
486 -- FII_MEASURE5 = Prior COR amounts
487 -- FII_MEASURE1 = LOB name
488 -- FII_CAL1 = Prior Total Revenue amounts
489 -- FII_CAL2 = Prior Total COR amounts
490 -- ----------------------------------------------------------------
491 IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
492
493 cy_prior_exp_select := 'NULL FII_ATTRIBUTE4,';
494 cy_act_exp_select := ' NULL FII_ATTRIBUTE3,';
495 sqlstmt1 := ' NULL FII_MEASURE2,
496 NULL FII_MEASURE3, '||cy_act_exp_select||'
497 NULL FII_MEASURE11,
498 NULL FII_ATTRIBUTE11,
499 NULL FII_ATTRIBUTE12,';
500 sqlstmt := '
501 select NULL VIEWBY,
502 NULL FII_MEASURE1,
503 NULL FII_MEASURE6,
504 '||sqlstmt1||'
505 NULL FII_CAL1,
506 NULL FII_CAL2,
507 '||cy_prior_exp_select||'
508 NULL FII_MEASURE4,
509 NULL FII_MEASURE5,
510 NULL ATTRIBUTE13,
511 NULL FII_ATTRIBUTE14,
512 NULL FII_ATTRIBUTE2,
513 NULL FII_MEASURE13,
514 NULL FII_MEASURE14
515
516 FROM DUAL
517 WHERE 1=2 ';
518 ELSE
519
520 sqlstmt := '
521 select decode(:LOB_ID, f.viewby_id,decode('''||l_dim_flag||''',''Y'','||fii_gl_util_pkg.g_viewby_value||', '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG), '||fii_gl_util_pkg.g_viewby_value||') VIEWBY,
522 to_number(NULL) FII_MEASURE1,
523 f.viewby_id FII_MEASURE6,
524 '||sqlstmt1||'
525 to_number(NULL) FII_CAL1,
526 to_number(NULL) FII_CAL2,
527 '||cy_prior_exp_select||'
528 NVL(SUM('||l_prior||'_REV), 0) FII_MEASURE4,
529 NVL(SUM('||l_prior||'_CGS), 0) FII_MEASURE5,
530 ((NVL(SUM(SUM(CY_ACT_REV)) over(), 0) - '||l_subtractor2||') /
531 ABS(NULLIF(SUM(SUM(CY_ACT_REV)) over(),0)) -
532 (NVL(SUM(SUM('||l_prior||'_REV)) over(), 0) - '||l_subtractor||') /
533 ABS(NULLIF(SUM(SUM('||l_prior||'_REV)) over(),0))) * 100 FII_ATTRIBUTE13,
534 ((NVL(SUM(SUM(CY_ACT_REV)) over(), 0) - '||l_subtractor2||') - (NVL(SUM(SUM('||l_prior||'_REV)) over(), 0) - '||l_subtractor||')) /
535 ABS(NULLIF((NVL(SUM(SUM('||l_prior||'_REV)) over(), 0) - '||l_subtractor||'),0)) * 100 FII_ATTRIBUTE14,
536 SUM(to_number(NULL)) FII_ATTRIBUTE2,
537 (case when NVL(abs((NVL(SUM(CY_ACT_REV), 0) - '||l_subtractor3||')/
538 ABS(NULLIF(SUM(CY_ACT_REV), 0)) * 100), 1000) > 999.9 THEN NULL WHEN NVL(abs((NVL(SUM('||l_prior||'_REV), 0) - '||l_subtractor4||')/
539 ABS(NULLIF(SUM('||l_prior||'_REV), 0)) * 100), 1000) > 999.9 THEN NULL ELSE 0 END) FII_MEASURE13,
540 DECODE(NVL(:LOB_ID,-9999), f.viewby_id, '''', '''||l_url||''') FII_MEASURE14
541 FROM '||fii_gl_util_pkg.g_viewby_from_clause||',
542 (select /*+ leading(cal) index(f FII_GL_MGMT_SUM_MV_N1) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
543 SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
544 and assgns.fin_cat_type_code = ''R''
545 then f.actual_g
546 else to_number(NULL) end) CY_ACT_REV,
547 SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
548 and assgns.fin_cat_type_code = ''OE''
549 then f.actual_g
550 else to_number(NULL) end) CY_ACT_EXP,
551 SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
552 and assgns.fin_cat_type_code = ''CGS''
553 then f.actual_g
554 else to_number(NULL) end) CY_ACT_CGS,
555 SUM(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
556 and assgns.fin_cat_type_code = ''R''
557 then f.budget_g
558 else to_number(NULL) end) CY_BUD_REV,
559 SUM(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
560 and assgns.fin_cat_type_code = ''CGS''
561 then f.budget_g
562 else to_number(NULL) end) CY_BUD_CGS,
563 SUM(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
564 and assgns.fin_cat_type_code = ''OE''
565 then f.budget_g
566 else to_number(NULL) end) CY_BUD_EXP,
567 to_number(NULL) PY_ACT_REV,
568 to_number(NULL) PY_ACT_EXP,
569 to_number(NULL) PY_ACT_CGS,
570 to_number(NULL) PYPER_ACT_REV,
571 to_number(NULL) PYPER_ACT_CGS
572
573 FROM fii_time_rpt_struct cal,
574 fii_fin_cat_type_assgns assgns
575 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
576 where assgns.fin_category_id = f.fin_category_id
577 '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||'
578 and cal.time_id = f.time_id
579 and cal.period_type_id = f.period_type_id
580 and cal.report_date = &BIS_CURRENT_ASOF_DATE
581 and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
582 group by '||fii_gl_util_pkg.g_viewby_id||'
583 union all
584 select /*+ leading(cal) index(f FII_GL_MGMT_SUM_MV_N1) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
585 to_number(NULL) CY_ACT_REV,
586 to_number(NULL) CY_ACT_EXP,
587 to_number(NULL) CY_ACT_CGS,
588 to_number(NULL) CY_BUD_REV,
589 to_number(NULL) CY_BUD_CGS,
590 to_number(NULL) CY_BUD_EXP,
591 SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
592 and assgns.fin_cat_type_code = ''R''
593 then f.actual_g
594 else to_number(NULL) end) PY_ACT_REV,
595 SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
596 and assgns.fin_cat_type_code = ''OE''
597 then f.actual_g
598 else to_number(NULL) end) PY_ACT_EXP,
599 SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
600 and assgns.fin_cat_type_code = ''CGS''
601 then f.actual_g
602 else to_number(NULL) end) PY_ACT_CGS,
603 to_number(NULL) PYPER_ACT_REV,
604 to_number(NULL) PYPER_ACT_CGS
605
606 FROM fii_time_rpt_struct cal,
607 fii_fin_cat_type_assgns assgns
608 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
609 where assgns.fin_category_id = f.fin_category_id
610 '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||'
611 and cal.time_id = f.time_id
612 and cal.period_type_id = f.period_type_id
613 and cal.report_date = &BIS_PREVIOUS_ASOF_DATE
614 and bitand(cal.record_type_id, :ACT_WHERE_PERIOD_TYPE) = cal.record_type_id
615 group by '||fii_gl_util_pkg.g_viewby_id||') f
616 where '||fii_gl_util_pkg.g_viewby_join||'
617 group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
618 order by NVL(FII_MEASURE11, -9999999999) desc';
619
620 END IF;
621
622 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, cont_marg_sql, cont_marg_output);
623
624 END get_cont_marg;
625
626 --* Procedure added by Ilavenil.
627 --* Procedure is called by OPERATING MARGIN
628
629 PROCEDURE get_opera_marg(
630 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
631 cont_marg_sql out NOCOPY VARCHAR2,
632 cont_marg_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
633 Begin
634 get_cont_marg(p_page_parameter_tbl, cont_marg_sql, cont_marg_output, 'Y');
635 End get_opera_marg;
636
637 END fii_gl_cost_center_pkg2;
638