[Home] [Help]
PACKAGE BODY: APPS.FII_GL_COST_CENTER_PKG
Source
1 PACKAGE BODY fii_gl_cost_center_pkg AS
2 /* $Header: FIIGLC1B.pls 120.108 2006/02/21 12:24:13 hpoddar noship $ */
3
4
5 PROCEDURE is_mgr_topnode ( l_mgr_id IN NUMBER,
6 mgr_is_topnode OUT NOCOPY VARCHAR2) IS
7
8
9 mgr_level NUMBER;
10
11
12 BEGIN
13 select mgr_level into mgr_level from fii_cc_mgr_hierarchies where EMP_ID = l_mgr_id and DIRECT_ID = l_mgr_id and MGR_ID = l_mgr_id;
14 if (mgr_level = 1) THEN
15 mgr_is_topnode := 'Y';
16 ELSE
17 mgr_is_topnode := 'N';
18 END IF;
19
20 END is_mgr_topnode;
21
22 PROCEDURE get_exp_by_cat (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
23 l_fin_category IN VARCHAR2,
24 exp_by_cat_sql out NOCOPY VARCHAR2,
25 exp_by_cat_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
26 IS
27
28 exp_by_cat_rec BIS_QUERY_ATTRIBUTES;
29 sqlstmt VARCHAR2(32000);
30 l_mgr_viewby_value VARCHAR2(30);
31 l_cat_viewby_value VARCHAR2(30);
32 l_mgr_viewby_id VARCHAR2(30);
33 l_cat_viewby_id VARCHAR2(30);
34 l_mgr_join VARCHAR2(100);
35 l_cat_join VARCHAR2(100);
36 l_mgr_from_clause VARCHAR2(200);
37 l_cat_from_clause VARCHAR2(200);
38
39 BEGIN
40
41 fii_gl_util_pkg.g_fin_type := 'OE';
42 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
43 fii_gl_util_pkg.get_bitmasks;
44 fii_gl_util_pkg.get_mgr_pmv_sql;
45 fii_gl_util_pkg.get_cat_pmv_sql;
46
47
48 /******************************************************************
49 * FII_MEASURE2 = Current amounts, FII_MEASURE3 = Prior amounts *
50 * FII_MEASURE5 = Forecast amounts, FII_MEASURE7 = Budget amounts *
51 ******************************************************************/
52
53 sqlstmt := ' select value VIEWBY,
54 sum(CY_ACTUAL) FII_MEASURE2,
55 sum(PY_ACTUAL) FII_MEASURE3,
56 sum(CY_FORECAST) FII_MEASURE5,
57 sum(CY_BUDGET) FII_MEASURE7,
58 FROM
59 (select '||fii_gl_util_pkg.g_viewby_id||' ID,
60 '||fii_gl_util_pkg.g_viewby_value||' value,
61 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id then f.actual_g else to_number(NULL) end) CY_ACTUAL,
62 sum(case when bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id then f.forecast_g else to_number(NULL) end) CY_FORECAST,
63 sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id then f.budget_g else to_number(NULL) end) CY_BUDGET,
64 to_number(NULL) PY_ACTUAL
65 from fii_time_rpt_stuct cal
66 '||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_mgr_from_clause||'
67 where cal.report_date = &BIS_CURRENT_ASOF_DATE
68 and cal.record_type_id = bitand(cal.record_type_id, :WHERE_PERIOD_TYPE)
69 and f.time_id = cal.time_id
70 and f.period_type_id = cal.period_type_id
71 '||fii_gl_util_pkg.g_gid||'
72 '||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_mgr_join||'
73 group by '||fii_gl_util_pkg.g_cat_viewby_id||'
74 union all
75 select '||fii_gl_util_pkg.g_cat_viewby_id||' ID,
76 '||fii_gl_util_pkg.g_viewby_value||' VALUE,
77 to_number(NULL) CY_ACTUAL,
78 to_number(NULL) CY_FORECAST,
79 to_number(NULL) CY_BUDGET,
80 sum(f.actual_g) PY_ACTUAL
81 from fii_time_rpt_stuct cal
82 '||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_mgr_from_clause||'
83 where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
84 and cal.record_type_id = bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE)
85 and f.time_id = cal.time_id
86 and f.period_type_id = cal.period_type_id
87 '||fii_gl_util_pkg.g_gid||'
88 '||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_mgr_join||'
89 group by '||fii_gl_util_pkg.g_viewby_id||' )
90 group by VALUE
91 order by FII_MEASURE2 desc';
92
93 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_by_cat_sql, exp_by_cat_output);
94
95 END get_exp_by_cat;
96
97 PROCEDURE get_te_cc (
98 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, te_cc_sql out NOCOPY VARCHAR2,
99 te_cc_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
100
101 sqlstmt VARCHAR2(32000);
102 BEGIN
103 fii_gl_util_pkg.reset_globals;
104 fii_gl_util_pkg.g_fin_type := 'TE';
105
106 sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
107 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, te_cc_sql, te_cc_output);
108 END get_te_cc;
109
110 PROCEDURE get_cogs_cc (
111 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, cogs_cc_sql out NOCOPY VARCHAR2,
112 cogs_cc_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
113
114 sqlstmt VARCHAR2(32000);
115 BEGIN
116 fii_gl_util_pkg.reset_globals;
117 fii_gl_util_pkg.g_fin_type := 'CGS';
118
119 sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
120 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, cogs_cc_sql, cogs_cc_output);
121 END get_cogs_cc;
122
123 PROCEDURE get_exp_cc (
124 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, exp_cc_sql out NOCOPY VARCHAR2,
125 exp_cc_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
126 sqlstmt VARCHAR2(32000);
127 BEGIN
128 fii_gl_util_pkg.reset_globals;
129 fii_gl_util_pkg.g_fin_type := 'OE';
130
131 sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
132 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_cc_sql, exp_cc_output);
133 END get_exp_cc;
134
135 PROCEDURE get_rev_cc (
136 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, rev_cc_sql out NOCOPY VARCHAR2,
137 rev_cc_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
138 sqlstmt VARCHAR2(32000);
139 BEGIN
140 fii_gl_util_pkg.reset_globals;
141 fii_gl_util_pkg.g_fin_type := 'R';
142 sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
143
144 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, rev_cc_sql, rev_cc_output);
145
146 END get_rev_cc;
147
148 FUNCTION get_revexp_cc (
149 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL) return VARCHAR2 IS
150
151 sqlstmt VARCHAR2(32000);
152 l_prior_or_budget VARCHAR2(3000) := NULL;
153 gt_cy_act VARCHAR2(20);
154 gt_py_act VARCHAR2(200);
155 gt_cy_fore VARCHAR2(20);
156 gt_cy_bud VARCHAR2(200);
157 gt_fore_act VARCHAR2(20);
158 gt_fore_bud VARCHAR2(20);
159 gt_fore_py VARCHAR2(20);
160 gt_act_py VARCHAR2(20);
161 l_url VARCHAR2(300);
162 l_url2 VARCHAR2(300);
163
164
165
166 BEGIN
167
168 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
169 fii_gl_util_pkg.get_bitmasks;
170
171
172 IF fii_gl_util_pkg.g_fin_type = 'R' THEN
173
174 gt_cy_act := 'FII_MEASURE11';
175 gt_cy_fore := 'FII_CAL1';
176 gt_fore_act := 'FII_ATTRIBUTE13';
177 gt_fore_bud := 'FII_ATTRIBUTE14';
178 gt_fore_py := 'FII_ATTRIBUTE12';
179 IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
180 gt_py_act := 'NULL FII_MEASURE12,';
181 gt_cy_bud := 'NULL FII_CAL2,';
182 ELSE
183 gt_py_act := 'sum(sum(PY_ACTUAL)) over() FII_MEASURE12,';
184 gt_cy_bud := 'sum(sum(CY_BUDGET)) over() FII_CAL2,';
185 END IF;
186
187 l_url := 'pFunctionName=FII_GL_REV_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
188 l_url2 := 'pFunctionName=FII_GL_REV_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
189 ELSIF (fii_gl_util_pkg.g_fin_type = 'OE' OR fii_gl_util_pkg.g_fin_type = 'TE') THEN
190 gt_cy_act := 'FII_ATTRIBUTE5';
191 gt_cy_fore := 'FII_ATTRIBUTE7';
192 gt_fore_act := 'FII_ATTRIBUTE1';
193 gt_fore_bud := 'FII_ATTRIBUTE2';
194 gt_fore_py := 'FII_ATTRIBUTE12';
195 IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
196 gt_py_act := 'NULL FII_ATTRIBUTE6,';
197 gt_cy_bud := 'NULL FII_ATTRIBUTE8,';
198 ELSE
199 IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
200 gt_py_act := 'sum(sum(CY_BUDGET)) over() FII_ATTRIBUTE6,';
201 ELSE
202 gt_py_act := 'sum(sum(PY_ACTUAL)) over() FII_ATTRIBUTE6,';
203 END IF;
204 gt_cy_bud := 'sum(sum(CY_BUDGET)) over() FII_ATTRIBUTE8,';
205 END IF;
206
207 IF fii_gl_util_pkg.g_fin_type = 'OE' THEN
208 l_url := 'pFunctionName=FII_GL_EXP_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
209 l_url2 := 'pFunctionName=FII_GL_EXP_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
210 ELSE
211 l_url := 'pFunctionName=FII_GL_TE_EXP_LOB1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
212 l_url2 := 'pFunctionName=FII_GL_TE_EXP_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
213 END IF;
214 ELSIF fii_gl_util_pkg.g_fin_type = 'CGS' THEN
215 gt_cy_act := 'FII_MEASURE11';
216 gt_cy_fore := 'FII_CAL1';
217 gt_fore_act := 'FII_ATTRIBUTE13';
218 gt_fore_bud := 'FII_ATTRIBUTE14';
219 gt_fore_py := 'FII_ATTRIBUTE12';
220 IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
221 gt_py_act := 'NULL FII_MEASURE12,';
222 gt_cy_bud := 'NULL FII_CAL2,';
223 ELSE
224 gt_py_act := 'sum(sum(PY_ACTUAL)) over() FII_MEASURE12,';
225 gt_cy_bud := 'sum(sum(CY_BUDGET)) over() FII_CAL2,';
226 END IF;
227
228 l_url := 'pFunctionName=FII_GL_COR_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
229 l_url2 := 'pFunctionName=FII_GL_COR_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
230 END IF;
231
232 gt_act_py := NULL;
233
234 --If comparison type is budget, series definition change.
235 IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
236
237 l_prior_or_budget :='(sum(sum(CY_ACTUAL)) over() - sum(sum(CY_BUDGET)) over()) /
238 ABS(NULLIF(sum(sum(CY_BUDGET)) over(),0)) * 100 FII_ATTRIBUTE11,
239 sum(CY_BUDGET) FII_MEASURE10,
240 sum(CY_BUDGET) FII_MEASURE3';
241
242 ELSE
243 l_prior_or_budget := '(sum(sum(CY_ACTUAL)) over() - sum(sum(PY_ACTUAL)) over()) /
244 ABS(NULLIF(sum(sum(PY_ACTUAL)) over(),0)) * 100 FII_ATTRIBUTE11,
245 sum(PY_ACTUAL) FII_MEASURE10,
246 sum(PY_ACTUAL) FII_MEASURE3';
247 END IF;
248
249
250
251 fii_gl_util_pkg.get_viewby_sql;
252 fii_gl_util_pkg.get_mgr_pmv_sql;
253 fii_gl_util_pkg.get_lob_pmv_sql;
254 fii_gl_util_pkg.get_cat_pmv_sql;
255 fii_gl_util_pkg.get_ccc_pmv_sql;
256
257 --code moved to FII_GL_UTIL_PKG
258 --bug 5002238..to make it as bind variable
259
260 /*IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
261 l_id := fii_gl_util_pkg.g_mgr_id;
262 l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
263 ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
264 l_id := fii_gl_util_pkg.g_lob_id;
265 l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
266 ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
267 l_id := fii_gl_util_pkg.g_fin_id;
268 l_dim_flag := fii_gl_util_pkg.g_fincat_is_leaf;
269 ELSE
270 l_id := -9999;
271 l_dim_flag := 'Y';
272 END IF; */
273
274
275 --Since we need to join to a different mv that is no longer
276 --aggregated along management hierarchy, we re-construct the
277 --final PMV select
278 IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
279 l_prior_or_budget :='NULL FII_ATTRIBUTE11,
280 NULL FII_MEASURE3,
281 NULL FII_MEASURE10';
282
283 sqlstmt := '
284 select NULL VIEWBY,
285 NULL VIEWBYID,
286 NULL FII_MEASURE13,
287 NULL FII_MEASURE2,
288 NULL FII_MEASURE5,
289 NULL FII_MEASURE7,
290 NULL FII_MEASURE9,
291 NULL FII_ATTRIBUTE4,
292 NULL '||GT_CY_ACT||',
293 '||GT_PY_ACT||'
294 NULL '||GT_CY_FORE||',
295 '||GT_CY_BUD||'
296 NULL '||GT_FORE_ACT||',
297 NULL '||GT_FORE_BUD||',
298 NULL '||GT_FORE_PY||',
299 '||l_prior_or_budget||',
300 NULL FII_MEASURE14,
301 NULL FII_MEASURE15,
302 NULL FII_MEASURE16,
303 NULL FII_MEASURE17,
304 NULL FII_MEASURE18
305
306 FROM DUAL
307 WHERE 1=2 ';
308
309 ELSE
310
311 sqlstmt := '
312 select decode(:L_ID, f.viewby_id,decode(: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,
313 f.viewby_id VIEWBYID,
314 to_number(null) FII_MEASURE13,
315 sum(CY_ACTUAL) FII_MEASURE2,
316 sum(CY_FORECAST) FII_MEASURE5,
317 sum(CY_BUDGET) FII_MEASURE7,
318 sum(CY_ACTUAL) FII_MEASURE9,
319 sum(PY_SPER_END) FII_ATTRIBUTE4,
320 sum(sum(CY_ACTUAL)) over() '||GT_CY_ACT||',
321 '||GT_PY_ACT||'
322 sum(sum(CY_FORECAST)) over() '||GT_CY_FORE||',
323 '||GT_CY_BUD||'
324 sum(sum(CY_ACTUAL)) over() /
325 NULLIF(sum(sum(CY_FORECAST)) over(),0) * 100 '||GT_FORE_ACT||',
326 (sum(sum(CY_FORECAST)) over() - sum(sum(CY_BUDGET)) over()) /
327 NULLIF(sum(sum(CY_BUDGET)) over(),0) * 100 '||GT_FORE_BUD||',
328 (sum(sum(CY_FORECAST)) over() - sum(sum(PY_SPER_END)) over()) /
329 ABS(NULLIF(sum(sum(PY_SPER_END)) over(),0)) * 100 '||GT_FORE_PY||','||l_prior_or_budget||',
330 decode('||NVL(fii_gl_util_pkg.g_mgr_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE14,
331 decode('||NVL(fii_gl_util_pkg.g_fin_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE15,
332 decode('||NVL(fii_gl_util_pkg.g_lob_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE16,
333 decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','''||l_url2||''',''''), '''||l_url2||''') FII_MEASURE17,
334 decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','''||l_url2||''',''''), '''||l_url2||''') FII_MEASURE18
335 from '||fii_gl_util_pkg.g_viewby_from_clause||',
336 (select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
337 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
338 then f.actual_g
339 else to_number(NULL) end) CY_ACTUAL,
340 sum(case when bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id
341 then f.forecast_g
342 else to_number(NULL) end) CY_FORECAST,
343 sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
344 then f.budget_g
345 else to_number(NULL) end) CY_BUDGET,
346 to_number(NULL) PY_ACTUAL,
347 to_number(NULL) PY_SPER_END
348 from fii_time_rpt_struct cal
349 '||fii_gl_util_pkg.g_view||'
350 '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
351 where cal.report_date = &BIS_CURRENT_ASOF_DATE
352 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_ccc_join||fii_gl_util_pkg.g_cat_join||'
353 '||fii_gl_util_pkg.g_gid||'
354 and cal.period_type_id = f.period_type_id
355 and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
356 group by '||fii_gl_util_pkg.g_viewby_id||'
357 union all
358 select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
359 to_number(NULL) CY_ACTUAL,
360 to_number(NULL) CY_FORECAST,
361 to_number(NULL) CY_BUDGET,
362 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
363 then f.actual_g
364 else to_number(NULL) end) PY_ACTUAL,
365 sum(case when bitand(cal.record_type_id, :ENT_PERIOD_TYPE) = cal.record_type_id
366 then f.actual_g
367 else to_number(NULL) end) PY_SPER_END
368 from fii_time_rpt_struct cal
369 '||fii_gl_util_pkg.g_view||'
370 '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
371 where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
372 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||fii_gl_util_pkg.g_ccc_join|| '
373 '||fii_gl_util_pkg.g_gid||'
374 and cal.period_type_id = f.period_type_id
375 and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
376 group by '||fii_gl_util_pkg.g_viewby_id||' ) f
377 where '||fii_gl_util_pkg.g_viewby_join||'
378 group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
379 order by NVL(FII_MEASURE2, -999999999) desc';
380
381 END IF;
382
383 return sqlstmt;
384
385 END get_revexp_cc;
386
387
388 PROCEDURE get_exp_ccc_mgr (
389 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, exp_ccc_mgr_sql out NOCOPY VARCHAR2,
390 exp_ccc_mgr_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
391
392 sqlstmt VARCHAR2(32000);
393 l_prior_or_budget VARCHAR2(3000) := NULL;
394
395
396 BEGIN
397
398 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
399 fii_gl_util_pkg.get_bitmasks;
400 fii_gl_util_pkg.g_fin_type := 'OE';
401
402 --If comparison type is budget, series definition change.
403 IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
404
405 l_prior_or_budget :='(sum(sum(CY_ACTUAL)) over() - sum(sum(CY_BUDGET)) over()) /
406 ABS(NULLIF(sum(sum(CY_BUDGET)) over(),0)) * 100 FII_ATTRIBUTE11,
407 sum(CY_BUDGET) FII_MEASURE3';
408
409 ELSE
410 l_prior_or_budget := '(sum(sum(CY_ACTUAL)) over() - sum(sum(PY_ACTUAL)) over()) /
411 ABS(NULLIF(sum(sum(PY_ACTUAL)) over(),0)) * 100 FII_ATTRIBUTE11,
412 sum(PY_ACTUAL) FII_MEASURE3';
413 END IF;
414
415 fii_gl_util_pkg.g_view_by := 'ORGANIZATION+HRI_CL_ORGCC';
416 fii_gl_util_pkg.get_viewby_sql;
417 fii_gl_util_pkg.get_mgr_pmv_sql;
418 fii_gl_util_pkg.get_lob_pmv_sql;
419 fii_gl_util_pkg.get_cat_pmv_sql;
420 fii_gl_util_pkg.get_ccc_pmv_sql;
421
422
423 --Since we need to join to a different mv that is no longer
424 --aggregated along management hierarchy, we re-construct the
425 --final PMV select
426
427
428 sqlstmt := '
429 select '||fii_gl_util_pkg.g_viewby_value||' VIEWBY,
430 f.viewby_id VIEWBYID,
431 emp.value FII_ATTRIBUTE14,
432 to_number(null) FII_MEASURE13,
433 sum(CY_ACTUAL) FII_MEASURE2,
434 sum(CY_FORECAST) FII_MEASURE5,
435 sum(CY_BUDGET) FII_MEASURE7,
436 sum(CY_ACTUAL) FII_MEASURE9,
437 sum(PY_SPER_END) FII_ATTRIBUTE4,
438 sum(sum(CY_ACTUAL)) over() FII_ATTRIBUTE5,
439 sum(sum(PY_ACTUAL)) over() FII_ATTRIBUTE6,
440 sum(sum(CY_FORECAST)) over() FII_ATTRIBUTE7,
441 sum(sum(CY_BUDGET)) over() FII_ATTRIBUTE8,
442 sum(sum(CY_ACTUAL)) over() /
443 NULLIF(sum(sum(CY_FORECAST)) over(),0) * 100 FII_ATTRIBUTE1,
444 (sum(sum(CY_FORECAST)) over() - sum(sum(CY_BUDGET)) over()) /
445 NULLIF(sum(sum(CY_BUDGET)) over(),0) * 100 FII_ATTRIBUTE2,
446 (sum(sum(CY_FORECAST)) over() - sum(sum(PY_SPER_END)) over()) /
447 ABS(NULLIF(sum(sum(PY_SPER_END)) over(),0)) * 100 FII_ATTRIBUTE12,'||l_prior_or_budget||'
448
449 from '||fii_gl_util_pkg.g_viewby_from_clause||',
450 hri_cs_per_orgcc_ct ct,
451 hri_dbi_cl_per_n_v emp,
452 (select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
453 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
454 then f.actual_g
455 else to_number(NULL) end) CY_ACTUAL,
456 sum(case when bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id
457 then f.forecast_g
458 else to_number(NULL) end) CY_FORECAST,
459 sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
460 then f.budget_g
461 else to_number(NULL) end) CY_BUDGET,
462 to_number(NULL) PY_ACTUAL,
463 to_number(NULL) PY_SPER_END
464 from fii_time_rpt_struct cal
465 '||fii_gl_util_pkg.g_view||'
466 '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
467 where cal.report_date = &BIS_CURRENT_ASOF_DATE
468 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_ccc_join||fii_gl_util_pkg.g_cat_join||'
469 '||fii_gl_util_pkg.g_gid||'
470 and cal.period_type_id = f.period_type_id
471 and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
472 group by '||fii_gl_util_pkg.g_viewby_id||'
473 union all
474 select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
475 to_number(NULL) CY_ACTUAL,
476 to_number(NULL) CY_FORECAST,
477 to_number(NULL) CY_BUDGET,
478 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
479 then f.actual_g
480 else to_number(NULL) end) PY_ACTUAL,
481 sum(case when bitand(cal.record_type_id, :ENT_PERIOD_TYPE) = cal.record_type_id
482 then f.actual_g
483 else to_number(NULL) end) PY_SPER_END
484 from fii_time_rpt_struct cal
485 '||fii_gl_util_pkg.g_view||'
486 '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
487 where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
488 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||fii_gl_util_pkg.g_ccc_join|| '
489 '||fii_gl_util_pkg.g_gid||'
490 and cal.period_type_id = f.period_type_id
491 and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
492 group by '||fii_gl_util_pkg.g_viewby_id||' ) f
493 where '||fii_gl_util_pkg.g_viewby_join||'
494 and cc.ORGANIZATION_ID = ct.ORGANIZATION_ID
495 and sysdate between emp.effective_start_date and emp.effective_end_date
496 and emp.person_id = ct.CC_MNGR_PERSON_ID
497 group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id, substr(emp.first_name,1,1)' || '||''.''|| ' || 'emp.last_name
498 &ORDER_BY_CLAUSE';
499
500 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_ccc_mgr_sql, exp_ccc_mgr_output);
501
502
503 END get_exp_ccc_mgr;
504
505
506
507
508
509 PROCEDURE get_exp_cc_by_cat1 (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
510 exp_cc_by_cat1_sql out NOCOPY VARCHAR2, exp_cc_by_cat1_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL, fin_type VARCHAR2)
511 IS
512
513 exp_cc_by_cat1_rec BIS_QUERY_ATTRIBUTES;
514 sqlstmt VARCHAR2(32000);
515 l_time_parameter VARCHAR2(100);
516 l_bitmask NUMBER;
517 l_cat_detail_url VARCHAR2(300);
518 l_journal_src_url VARCHAR2(300);
519 l_id NUMBER;
520
521 BEGIN
522
523 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
524 fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';
525 fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
526 fii_gl_util_pkg.get_bitmasks;
527
528 fii_gl_util_pkg.g_fin_type := fin_type;
529
530 IF fii_gl_util_pkg.g_fin_type = 'R' THEN
531 l_cat_detail_url := 'pFunctionName=FII_GL_REV_CAT3&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
532 l_journal_src_url:= 'pFunctionName=FII_GL_INV_REV_R&FII_DIM9=FII_MEASURE7&FII_DIM2=FII_MEASURE1&FII_DIM8=FII_MEASURE10&pParamIds=Y';
533 ELSIF (fii_gl_util_pkg.g_fin_type = 'OE' ) THEN
534 l_cat_detail_url := 'pFunctionName=FII_GL_EXP_CAT3&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
535 l_journal_src_url := 'pFunctionName=FII_GL_INV_EXP_R&FII_DIM9=FII_MEASURE7&FII_DIM2=FII_MEASURE1&FII_DIM8=FII_MEASURE10&pParamIds=Y';
536 ELSIF fii_gl_util_pkg.g_fin_type = 'CGS' THEN
537 l_cat_detail_url := 'pFunctionName=FII_GL_COR_CAT3&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
538 l_journal_src_url := 'pFunctionName=FII_GL_INV_COR_R&FII_DIM9=FII_MEASURE7&FII_DIM2=FII_MEASURE1&FII_DIM8=FII_MEASURE10&pParamIds=Y';
539 END IF;
540
541
542
543
544 fii_gl_util_pkg.get_viewby_sql;
545 fii_gl_util_pkg.get_mgr_pmv_sql;
546 fii_gl_util_pkg.get_lob_pmv_sql;
547 fii_gl_util_pkg.get_cat_pmv_sql;
548 fii_gl_util_pkg.get_ccc_pmv_sql;
549
550 l_id := NVL(fii_gl_util_pkg.g_fin_id,-9999);
551
552 select nvl(to_char(min(ent_period_id)),fii_gl_util_pkg.g_month_id) into l_time_parameter
553 from fii_time_ent_period
554 where fii_gl_util_pkg.g_as_of_date between start_date and end_date;
555
556 IF (fii_gl_util_pkg.g_month_id <> l_time_parameter) THEN
557 l_bitmask := 256;
558 ELSE
559 l_bitmask := 23;
560
561 END IF;
562
563
564
565 /******************************************************************
566 * FII_MEASURE5 = Parent category -> NULL aftr bug 2797564 *
567 * FII_MEASURE1 = Category id *
568 * FII_MEASURE6 = Manager id *
569 * FII_MEASURE7 = Month id *
570 * FII_MEASURE8 = Line of Business id *
571 * FII_MEASURE9 = Currency id *
572 * FII_MEASURE10 = Cost Center id *
573 * FII_MEASURE2 = Current amounts *
574 * FII_MEASURE3 = Prior amounts *
575 ******************************************************************/
576 --we need to make sure that bug 2797564 is fixed
577
578
579 sqlstmt := ' select '||fii_gl_util_pkg.g_viewby_value||' VIEWBY,
580 f.viewby_id VIEWBYID,
581 NULL FII_MEASURE5,
582 f.viewby_id FII_MEASURE1,
583 :MGR_ID FII_MEASURE6,
584 :MONTH_ID FII_MEASURE7,
585 :LOB_ID FII_MEASURE8,
586 :CURRENCY FII_MEASURE9,
587 :CCC_ID FII_MEASURE10,
588 sum(CY_ACTUAL) FII_MEASURE2,
589 sum(PY_ACTUAL) FII_MEASURE3,
590 decode((SELECT is_leaf_flag
591 FROM fii_fin_item_hierarchies
592 WHERE parent_fin_cat_id = f.viewby_id
593 and child_fin_cat_id = f.viewby_id),
594 ''Y'','''||l_journal_src_url||''',
595 '''||l_cat_detail_url||''') FII_URL
596 from '||fii_gl_util_pkg.g_viewby_from_clause||',
597 (select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
598 sum(f.actual_g) CY_ACTUAL,
599 to_number(NULL) PY_ACTUAL
600 from fii_time_rpt_struct cal
601 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
602 where cal.report_date = to_date(:P_AS_OF, ''DD-MM-YYYY'')
603 /*Fix -- should be set dynamically*/
604 and cal.record_type_id = bitand(cal.record_type_id, '||l_bitmask||')
605 and f.time_id = cal.time_id
606 and f.period_type_id = cal.period_type_id
607 '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
608 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||'
609 group by '||fii_gl_util_pkg.g_viewby_id||'
610 union all
611 select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
612 to_number(NULL) CY_ACTUAL,
613 sum(f.actual_g) PY_ACTUAL
614 from fii_time_rpt_struct cal
615 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
616 where cal.report_date = to_date(:P_PREV_AS_OF, ''DD-MM-YYYY'')
617 and cal.record_type_id = bitand(cal.record_type_id, '||l_bitmask||')
618 and f.time_id = cal.time_id
619 and f.period_type_id = cal.period_type_id
620 '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
621 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||'
622 group by '||fii_gl_util_pkg.g_viewby_id||' ) f
623 where '||fii_gl_util_pkg.g_viewby_join||'
624 group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
625 order by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';
626
627 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_cc_by_cat1_sql, exp_cc_by_cat1_output);
628
629 END get_exp_cc_by_cat1;
630 PROCEDURE get_rev_cc_by_cat (
631 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, rev_cc_by_cat_sql out NOCOPY VARCHAR2,
632 rev_cc_by_cat_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
633 l_fin_type VARCHAR2(1);
634 BEGIN
635 fii_gl_util_pkg.reset_globals;
636 l_fin_type := 'R';
637 fii_gl_cost_center_pkg.get_exp_cc_by_cat1(p_page_parameter_tbl, rev_cc_by_cat_sql, rev_cc_by_cat_output, l_fin_type );
638
639
640 END get_rev_cc_by_cat;
641
642 PROCEDURE get_exp_cc_by_cat (
643 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, exp_cc_by_cat_sql out NOCOPY VARCHAR2,
644 exp_cc_by_cat_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
645 l_fin_type VARCHAR2(2);
646 BEGIN
647 fii_gl_util_pkg.reset_globals;
648 l_fin_type := 'OE';
649 fii_gl_cost_center_pkg.get_exp_cc_by_cat1(p_page_parameter_tbl, exp_cc_by_cat_sql, exp_cc_by_cat_output, l_fin_type );
650
651
652 END get_exp_cc_by_cat;
653
654 PROCEDURE get_cogs_cc_by_cat (
655 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, cogs_cc_by_cat_sql out NOCOPY VARCHAR2,
656 cogs_cc_by_cat_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
657 l_fin_type VARCHAR2(3);
658 BEGIN
659 fii_gl_util_pkg.reset_globals;
660 l_fin_type := 'CGS';
661 fii_gl_cost_center_pkg.get_exp_cc_by_cat1(p_page_parameter_tbl, cogs_cc_by_cat_sql, cogs_cc_by_cat_output, l_fin_type );
662
663
664 END get_cogs_cc_by_cat;
665
666
667 PROCEDURE get_revexp_tr(
668 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
669 revexp_tr_sql out NOCOPY VARCHAR2, revexp_tr_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL, l_fin_type in VARCHAR2) IS
670
671 revexp_tr_rec BIS_QUERY_ATTRIBUTES;
672 sqlstmt VARCHAR2(32000);
673 l_hint1 VARCHAR2(200) := NULL;
674 l_hint2 VARCHAR2(200) := NULL;
675 l_join1 VARCHAR2(100):= NULL;
676 l_join2 VARCHAR2(100):= NULL;
677 l_join3 VARCHAR2(100):= NULL;
678 l_lyr DATE;
679 l_llyr DATE;
680
681 BEGIN
682 fii_gl_util_pkg.reset_globals;
683 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
684 fii_gl_util_pkg.g_fin_type := l_fin_type;
685 fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
686 fii_gl_util_pkg.get_bitmasks;
687 fii_gl_util_pkg.get_mgr_pmv_sql;
688 fii_gl_util_pkg.get_lob_pmv_sql;
689 fii_gl_util_pkg.get_cat_pmv_sql;
690 fii_gl_util_pkg.get_ccc_pmv_sql;
691
692 IF fii_gl_util_pkg.g_lob_is_top_node <> 'Y' AND fii_gl_util_pkg.g_lob_id IS NOT NULL THEN
693 l_hint1 := '/*+ leading(per) use_nl(f) */';
694 l_hint2 := '/*+ leading(cal) use_nl(f) */';
695 END IF;
696
697 SELECT fii_time_api.ent_sd_lysper_end(fii_gl_util_pkg.g_as_of_date)
698 INTO l_lyr
699 FROM dual;
700
701 IF l_lyr is NOT NULL THEN
702 l_join1 := ' > to_date(:P_SD_LYR, ''DD-MM-YYYY'')';
703 l_join3 := '= to_date(:P_SD_LYR, ''DD-MM-YYYY'')';
704 ELSE
705 l_join1 := ' >= to_date(:P_SD_LYR, ''DD-MM-YYYY'')';
706 l_join3 := '< to_date(:P_SD_LYR, ''DD-MM-YYYY'')';
707 END IF;
708
709 SELECT fii_time_api.ent_sd_lysper_end(l_lyr)
710 INTO l_llyr
711 FROM dual;
712
713 IF l_llyr is NOT NULL THEN
714 l_join2 := '> to_date(:PPY_SAME_DAY, ''DD-MM-YYYY'')';
715 ELSE
716 l_join2 := '>= to_date(:PPY_SAME_DAY, ''DD-MM-YYYY'')';
717 END IF;
718
719 IF fii_gl_util_pkg.g_mgr_id = -99999 THEN /* Done for bug 3875336 */
720
721 sqlstmt := ' SELECT NULL VIEWBY,
722 NULL FII_MEASURE1,
723 NULL FII_MEASURE2,
724 NULL FII_MEASURE3
725 FROM DUAL
726 WHERE 1=2 ';
727
728 ELSE
729
730 sqlstmt := '
731 select cy_per.name VIEWBY,
732 cy_per.ent_period_id FII_MEASURE1,
733 inline_view.cy_ptot FII_MEASURE2,
734 inline_view.py_ptot FII_MEASURE3
735 from
736 fii_time_ent_period cy_per,
737 (select inner_inline_view.fii_effective_num FII_EFFECTIVE_NUM,
738 sum(CY_PTOT) CY_PTOT,
739 sum(PY_PTOT) PY_PTOT
740 from
741 (select '||l_hint1||' per.sequence FII_EFFECTIVE_NUM,
742 case when per.start_date '||l_join1||' and
743 per.end_date < to_date(:ASOF_DATE, ''DD-MM-YYYY'')
744 then f.actual_g else to_number(NULL) end CY_PTOT,
745 case when per.end_date < to_date(:P_SD_LYR, ''DD-MM-YYYY'')
746 then f.actual_g else to_number(NULL) end PY_PTOT
747 from fii_time_ent_period per
748 '||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||fii_gl_util_pkg.g_ccc_from_clause||'
749 where per.ent_period_id = f.time_id
750 '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
751 '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
752 and per.start_date '||l_join2||'
753 and per.end_date < to_date(:ASOF_DATE, ''DD-MM-YYYY'')
754 and f.period_type_id = 32
755 union all
756 select '||l_hint2||' :CURR_EFFECTIVE_SEQ FII_EFFECTIVE_NUM,
757 case when cal.REPORT_DATE = to_date(:ASOF_DATE, ''DD-MM-YYYY'')
758 then f.actual_g
759 else to_number(NULL)
760 end CY_QTOT,
761 case when cal.REPORT_DATE '||l_join3||'
762 then f.actual_g
763 else to_number(NULL)
764 end PY_QTOT
765 from fii_time_rpt_struct cal
766 '||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||fii_gl_util_pkg.g_ccc_from_clause||'
767 where cal.time_id = f.time_id
768 '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
769 '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
770 and cal.period_type_id = f.period_type_id
771 and cal.report_date in (to_date(:ASOF_DATE, ''DD-MM-YYYY'') , to_date(:P_SD_LYR, ''DD-MM-YYYY''))
772 and bitand(cal.record_type_id, 23) = cal.record_type_id) inner_inline_view
773 group by inner_inline_view.fii_effective_num ) inline_view
774 where cy_per.start_date <= to_date(:ASOF_DATE, ''DD-MM-YYYY'')
775 and cy_per.start_date >= to_date(:P_SD_LYR, ''DD-MM-YYYY'')
776 and cy_per.sequence = inline_view.fii_effective_num (+)
777 order by cy_per.start_date';
778
779 END IF;
780
781 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, revexp_tr_sql, revexp_tr_output);
782
783 END get_revexp_tr;
784
785 PROCEDURE get_rev_tr (
786 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, rev_tr_sql out NOCOPY VARCHAR2,
787 rev_tr_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
788 l_fin_type VARCHAR2(1);
789 BEGIN
790 l_fin_type := 'R';
791 fii_gl_cost_center_pkg.get_revexp_tr(p_page_parameter_tbl, rev_tr_sql, rev_tr_output, l_fin_type );
792
793
794 END get_rev_tr;
795
796 PROCEDURE get_cogs_tr (
797 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, cogs_tr_sql out NOCOPY VARCHAR2,
798 cogs_tr_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
799 l_fin_type VARCHAR2(3);
800 BEGIN
801 l_fin_type := 'CGS';
802 fii_gl_cost_center_pkg.get_revexp_tr(p_page_parameter_tbl, cogs_tr_sql, cogs_tr_output, l_fin_type );
803
804
805 END get_cogs_tr;
806
807 PROCEDURE get_exp_tr (
808 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, exp_tr_sql out NOCOPY VARCHAR2,
809 exp_tr_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
810 l_fin_type VARCHAR2(2);
811 BEGIN
812 l_fin_type := 'OE';
813 fii_gl_cost_center_pkg.get_revexp_tr(p_page_parameter_tbl, exp_tr_sql, exp_tr_output, l_fin_type );
814 END get_exp_tr;
815
816 PROCEDURE get_te_tr (
817 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, te_tr_sql out NOCOPY VARCHAR2,
818 te_tr_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
819 l_fin_type VARCHAR2(2);
820 BEGIN
821 l_fin_type := 'TE';
822 fii_gl_cost_center_pkg.get_revexp_tr(p_page_parameter_tbl, te_tr_sql, te_tr_output, l_fin_type );
823
824 END get_te_tr;
825
826 PROCEDURE get_cont_marg(
827 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
828 cont_marg_sql out NOCOPY VARCHAR2, cont_marg_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL, p_opera_marg IN Char DEFAULT 'N') IS
829
830 cont_marg_rec BIS_QUERY_ATTRIBUTES;
831 sqlstmt VARCHAR2(32000);
832 sqlstmt1 VARCHAR2(5000);
833 cy_act_exp_select VARCHAR2(100) := NULL;
834 l_prior VARCHAR2(20) := NULL;
835 l_record_type VARCHAR2(20) := NULL;
836 l_amt VARCHAR2(20) := NULL;
837 l_label VARCHAR2(20) := NULL;
838 cy_prior_exp_select VARCHAR2(100) := NULL;
839 l_subtractor2 VARCHAR2(100) := NULL;
840 l_subtractor VARCHAR2(100) := NULL;
841 l_subtractor3 VARCHAR2(100) := NULL;
842 l_subtractor4 VARCHAR2(100) := NULL;
843 l_hint VARCHAR2(100) := NULL;
844 l_url VARCHAR2(300) := NULL;
845 -- l_id NUMBER;
846 -- l_dim_flag VARCHAR2(1);
847
848 BEGIN
849
850 fii_gl_util_pkg.reset_globals;
851 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
852 fii_gl_util_pkg.get_bitmasks;
853 fii_gl_util_pkg.get_viewby_sql;
854 fii_gl_util_pkg.get_mgr_pmv_sql;
855 fii_gl_util_pkg.get_lob_pmv_sql;
856 fii_gl_util_pkg.get_ccc_pmv_sql;
857
858 IF p_opera_marg = 'Y' THEN
859 fii_gl_util_pkg.g_fin_type := 'OM';
860 l_url := 'pFunctionName=FII_GL_OPS_LOB_MGR1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
861 ELSE fii_gl_util_pkg.g_fin_type := 'GM';
862 l_url := 'pFunctionName=FII_GL_MAR_LOB_MGR1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
863 END IF;
864
865 fii_gl_util_pkg.get_cat_pmv_sql;
866
867
868 IF p_opera_marg = 'Y' THEN
869 cy_act_exp_select := ' NVL(sum(NVL(CY_ACT_EXP,0)), 0) FII_ATTRIBUTE2,';
870 l_subtractor := '(NVL(sum(sum(NVL(CY_ACT_CGS,0))) over(), 0) + NVL(sum(sum(NVL(CY_ACT_EXP,0))) over(), 0))';
871 l_subtractor3 := '(NVL(sum(NVL(CY_ACT_CGS,0)), 0) + NVL(sum(NVL(CY_ACT_EXP,0)), 0))';
872 ELSE
873 l_subtractor := ' NVL(sum(sum(NVL(CY_ACT_CGS,0))) over(), 0) ';
874 l_subtractor3 := ' NVL(sum(NVL(CY_ACT_CGS,0)), 0) ';
875 END IF;
876
877 sqlstmt1 := ' NVL(sum(CY_ACT_REV), 0) FII_MEASURE2,
878 NVL(sum(CY_ACT_CGS), 0) FII_MEASURE3, '||cy_act_exp_select||'
879 (NVL(sum(CY_ACT_REV), 0) - '||l_subtractor3||')/
880 ABS(NULLIF(sum(CY_ACT_REV), 0)) * 100 FII_MEASURE11,
881 NVL(sum(sum(CY_ACT_REV)) over(), 0) - '||l_subtractor||' FII_ATTRIBUTE11,
882 (NVL(sum(sum(CY_ACT_REV)) over(), 0) - '||l_subtractor||') /
883 ABS(NULLIF(sum(sum(CY_ACT_REV)) over(),0)) * 100 FII_ATTRIBUTE12,';
884
885 IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
886
887 l_prior := 'CY_BUD';
888 l_record_type := ':BUDGET_PERIOD_TYPE';
889 l_amt := 'actual_g';
890 IF p_opera_marg = 'Y' THEN
891 l_label := 'CY_BUD_EXP';
892 END IF;
893
894 ELSE
895
896 l_prior := 'PY_ACT';
897 l_record_type := ':ACTUAL_PERIOD_TYPE';
898 l_amt := 'actual_g';
899 IF p_opera_marg = 'Y' THEN
900 l_label := 'PY_ACT_EXP';
901 END IF;
902
903 END IF;
904
905
906 IF p_opera_marg = 'Y' THEN
907
908 cy_prior_exp_select := 'NVL(sum('||l_label||'), 0) FII_ATTRIBUTE4,';
909 l_subtractor2 := '(NVL(sum(sum(NVL('||l_prior||'_EXP,0))) over(), 0) + NVL(sum(sum(NVL('||l_prior||'_CGS,0))) over(), 0) )';
910 l_subtractor4 := '(NVL(sum(NVL('||l_prior||'_CGS,0)), 0) + NVL(sum(NVL('||l_prior||'_EXP,0)), 0))';
911 ELSE
912 l_subtractor2 := 'NVL(sum(sum(NVL('||l_prior||'_CGS,0))) over(), 0)';
913 l_subtractor4 := ' NVL(sum(NVL('||l_prior||'_CGS,0)), 0) ';
914 END IF;
915
916 --moved to fii_gl_util_pkg
917 --added for bug fix 5002238
918 --by vkazhipu
919 --changing l_id and l_dim_flag to bind variables
920 /*
921 IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
922 l_id := fii_gl_util_pkg.g_mgr_id;
923 l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
924 ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
925 l_id := fii_gl_util_pkg.g_lob_id;
926 l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
927 ELSE
928 l_id := -9999;
929 l_dim_flag := 'Y';
930 END IF;
931 */
932
933 l_hint := '/*+ index(mgr.HRI_CS_SUPH, HRI_CS_SUPH_N5) use_nl(f cat cal mgr per lob lob2) ordered */';
934
935 -- ----------------------------------------------------------------
936 -- FII_MEASURE2 = Forecasted Rev amounts
937 -- FII_MEASURE3 = Forecasted COR amounts
938 -- FII_MEASURE4 = Prior Revenue amounts
939 -- FII_MEASURE5 = Prior COR amounts
940 -- FII_MEASURE1 = LOB name
941 -- FII_CAL1 = Prior Total Revenue amounts
942 -- FII_CAL2 = Prior Total COR amounts
943 -- ----------------------------------------------------------------
944 IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
945 cy_prior_exp_select := 'NULL FII_ATTRIBUTE4,';
946 cy_act_exp_select := ' NULL FII_ATTRIBUTE2,';
947 sqlstmt1 := ' NULL FII_MEASURE2,
948 NULL FII_MEASURE3, '||cy_act_exp_select||'
949 NULL FII_MEASURE11,
950 NULL FII_ATTRIBUTE11,
951 NULL FII_ATTRIBUTE12,';
952 sqlstmt := '
953 SELECT NULL VIEWBY,
954 NULL VIEWBYID,
955 '||sqlstmt1||'
956 NULL FII_CAL1,
957 NULL FII_CAL2,
958 '||cy_prior_exp_select||'
959 NULL FII_MEASURE4,
960 NULL FII_MEASURE5,
961 NULL FII_ATTRIBUTE13,
962 NULL FII_ATTRIBUTE14,
963 NULL FII_MEASURE13,
964 NULL FII_MEASURE14,
965 NULL FII_MEASURE15
966
967 FROM DUAL
968 WHERE 1=2 ';
969
970 ELSE
971
972 sqlstmt := '
973 select decode(:L_ID, f.viewby_id,decode(: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,
974 f.viewby_id VIEWBYID,
975 '||sqlstmt1||'
976 to_number(NULL) FII_CAL1,
977 to_number(NULL) FII_CAL2,
978 '||cy_prior_exp_select||'
979 NVL(sum('||l_prior||'_REV), 0) FII_MEASURE4,
980 NVL(sum('||l_prior||'_CGS), 0) FII_MEASURE5,
981 ( (NVL(sum(sum(NVL(CY_ACT_REV,0))) over(), 0) - '||l_subtractor||') /
982 ABS(NULLIF(sum(sum(NVL(CY_ACT_REV,0))) over(),0)) -
983 (NVL(sum(sum(NVL('||l_prior||'_REV,0))) over(), 0) - '||l_subtractor2||') /
984 ABS(NULLIF(sum(sum(NVL('||l_prior||'_REV,0))) over(),0))) * 100 FII_ATTRIBUTE13,
985 ((NVL(sum(sum(NVL(CY_ACT_REV,0))) over(), 0) - '||l_subtractor||') -
986 (NVL(sum(sum(NVL('||l_prior||'_REV,0))) over(), 0) - '||l_subtractor2||')) /
987 ABS(NULLIF((sum(sum(NVL('||l_prior||'_REV,0))) over() - '||l_subtractor2||'),0)) * 100 FII_ATTRIBUTE14,
988 (case when NVL(abs((NVL(sum(CY_ACT_REV), 0) - '||l_subtractor3||')/
989 ABS(NULLIF(sum(CY_ACT_REV), 0)) * 100), 1000) > 999.9 THEN NULL WHEN NVL(abs((NVL(sum('||l_prior||'_REV), 0) - '||l_subtractor4||')/
990 ABS(NULLIF(sum('||l_prior||'_REV), 0)) * 100), 1000) > 999.9 THEN NULL ELSE 0 END) FII_MEASURE13,
991 decode('||NVL(fii_gl_util_pkg.g_mgr_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE14,
992 decode('||NVL(fii_gl_util_pkg.g_lob_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE15
993 from '||fii_gl_util_pkg.g_viewby_from_clause||',
994 (select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
995 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
996 and assgns.fin_cat_type_code = ''R''
997 then f.actual_g
998 else to_number(NULL) end) CY_ACT_REV,
999 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
1000 and assgns.fin_cat_type_code = ''OE''
1001 then f.actual_g
1002 else to_number(NULL) end) CY_ACT_EXP,
1003 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
1004 and assgns.fin_cat_type_code = ''CGS''
1005 then f.actual_g
1006 else to_number(NULL) end) CY_ACT_CGS,
1007 sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
1008 and assgns.fin_cat_type_code = ''R''
1009 then f.budget_g
1010 else to_number(NULL) end) CY_BUD_REV,
1011 sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
1012 and assgns.fin_cat_type_code = ''CGS''
1013 then f.budget_g
1014 else to_number(NULL) end) CY_BUD_CGS,
1015 sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
1016 and assgns.fin_cat_type_code = ''OE''
1017 then f.budget_g
1018 else to_number(NULL) end) CY_BUD_EXP,
1019 to_number(NULL) PY_ACT_REV,
1020 to_number(NULL) PY_ACT_EXP,
1021 to_number(NULL) PY_ACT_CGS,
1022 to_number(NULL) PYPER_ACT_REV,
1023 to_number(NULL) PYPER_ACT_CGS
1024
1025 from fii_time_rpt_struct cal,
1026 fii_fin_cat_type_assgns assgns
1027 '||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||fii_gl_util_pkg.g_ccc_from_clause||'
1028 where cal.time_id = f.time_id
1029 '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
1030 and assgns.fin_category_id= f.fin_category_id
1031 '||fii_gl_util_pkg.g_gid||'
1032 and cal.period_type_id = f.period_type_id
1033 and cal.report_date = &BIS_CURRENT_ASOF_DATE
1034 and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
1035 group by '||fii_gl_util_pkg.g_viewby_id||'
1036 union all
1037 select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
1038 to_number(NULL) CY_ACT_REV,
1039 to_number(NULL) CY_ACT_EXP,
1040 to_number(NULL) CY_ACT_CGS,
1041 to_number(NULL) CY_BUD_REV,
1042 to_number(NULL) CY_BUD_CGS,
1043 to_number(NULL) CY_BUD_EXP,
1044 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
1045 and assgns.fin_cat_type_code = ''R''
1046 then f.actual_g
1047 else to_number(NULL) end) PY_ACT_REV,
1048 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
1049 and assgns.fin_cat_type_code = ''OE''
1050 then f.actual_g
1051 else to_number(NULL) end) PY_ACT_EXP,
1052 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
1053 and assgns.fin_cat_type_code = ''CGS''
1054 then f.actual_g
1055 else to_number(NULL) end) PY_ACT_CGS,
1056 to_number(NULL) PYPER_ACT_REV,
1057 to_number(NULL) PYPER_ACT_CGS
1058
1059 from fii_time_rpt_struct cal,
1060 fii_fin_cat_type_assgns assgns
1061 '||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||fii_gl_util_pkg.g_ccc_from_clause||'
1062 where cal.time_id = f.time_id
1063 '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
1064 and assgns.fin_category_id = f.fin_category_id
1065 '||fii_gl_util_pkg.g_gid||'
1066 and cal.period_type_id = f.period_type_id
1067 and cal.report_date = &BIS_PREVIOUS_ASOF_DATE
1068 and bitand(cal.record_type_id, :ACT_WHERE_PERIOD_TYPE) = cal.record_type_id
1069 group by '||fii_gl_util_pkg.g_viewby_id||') f
1070 where '||fii_gl_util_pkg.g_viewby_join||'
1071 group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
1072 order by NVL(FII_MEASURE11, -9999999999) desc';
1073
1074 END IF;
1075
1076 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, cont_marg_sql, cont_marg_output);
1077
1078 END get_cont_marg;
1079
1080 --* Procedure added by Ilavenil. This procedure is called by OPERATING MARGIN.
1081 PROCEDURE get_opera_marg(
1082 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
1083 cont_marg_sql out NOCOPY VARCHAR2, cont_marg_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
1084 Begin
1085 get_cont_marg(p_page_parameter_tbl, cont_marg_sql, cont_marg_output, 'Y');
1086 End;
1087
1088 END fii_gl_cost_center_pkg;
1089