[Home] [Help]
PACKAGE BODY: APPS.FII_PL_PAGE_PKG
Source
1 PACKAGE BODY fii_pl_page_pkg AS
2 /* $Header: FIIPLPGB.pls 120.5.12000000.2 2007/04/16 06:42:17 dhmehra ship $ */
3
4 PROCEDURE get_pl_graph (
5 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, pl_graph_sql out NOCOPY VARCHAR2,
6 pl_graph_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
7
8 sqlstmt VARCHAR2(30000);
9
10
11 BEGIN
12 fii_ea_util_pkg.reset_globals;
13 fii_ea_util_pkg.g_fin_cat_type :=NULL;
14 sqlstmt := fii_pl_page_pkg.get_pl_graph_val(p_page_parameter_tbl);
15 fii_ea_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, pl_graph_sql, pl_graph_output);
16
17 END get_pl_graph;
18
19
20 FUNCTION get_pl_graph_val (
21 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL) return VARCHAR2 IS
22
23 sqlstmt VARCHAR2(15000);
24
25 p_aggrt_viewby_id VARCHAR2(30);
26 p_snap_aggrt_viewby_id VARCHAR2(30);
27 p_nonaggrt_viewby_id VARCHAR2(50);
28
29 p_aggrt_gt_is_empty VARCHAR2(1);
30 p_non_aggrt_gt_is_empty VARCHAR2(1);
31 --l_roll_column VARCHAR2(10);
32 l_xtd_column VARCHAR2(10);
33 l_source_cogs VARCHAR2(100);
34 l_source_exp VARCHAR2(100);
35 l_source_inc VARCHAR2(100);
36
37
38 BEGIN
39 fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);
40 fii_ea_util_pkg.g_view_by := 'FII_COMPANIES+FII_COMPANIES';
41 fii_ea_util_pkg.populate_security_gt_tables(p_aggrt_gt_is_empty, p_non_aggrt_gt_is_empty);
42
43 l_source_cogs := ltrim(rtrim(fnd_message.get_string('FII','FII_PL_SOURCE_COGS')));
44 l_source_exp := ltrim(rtrim(fnd_message.get_string('FII','FII_PL_SOURCE_EXP')));
45 l_source_inc := ltrim(rtrim(fnd_message.get_string('FII','FII_PL_SOURCE_INC')));
46
47 sqlstmt :='
48 SELECT
49 FII_PL_SOURCE,
50 FII_PL_XTD_AMT,
51 (NVL(FII_PL_XTD_AMT,0)/NULLIF(ABS(NVL((SUM(FII_PL_XTD_AMT) over()),0)),0))*100 FII_PL_XTD_AMT_R
52 FROM
53 (SELECT
54 FII_PL_SOURCE,
55 CASE WHEN FII_PL_XTD_AMT < 0 THEN 0 ELSE FII_PL_XTD_AMT END FII_PL_XTD_AMT,
56 FII_ORDER_BY
57 FROM
58 (
59 SELECT '||''''||l_source_exp||''''||' FII_PL_SOURCE,
60 SUM(f.actual_g) FII_PL_XTD_AMT,
61 2 FII_ORDER_BY
62 FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
63 ( SELECT /*+ NO_MERGE cardinality(gt 1) */ *
64 FROM fii_time_structures cal, fii_pmv_aggrt_gt gt
65 where report_date in (:ASOF_DATE)
66 and ( bitand(cal.record_type_id, :ACTUAL_BITAND) =
67 :ACTUAL_BITAND)
68 ) inner_inline_view
69 WHERE f.time_id = inner_inline_view.time_id
70 AND f.period_type_id = inner_inline_view.period_type_id
71 AND f.parent_company_id = inner_inline_view.parent_company_id
72 AND f.company_id = inner_inline_view.company_id
73 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
74 AND f.cost_center_id = inner_inline_view.cc_id
75 AND f.top_node_fin_cat_type=''OE''
76 UNION ALL
77 SELECT '||''''||l_source_inc||''''||' FII_EA_SOURCE,
78 SUM(DECODE(f.top_node_fin_cat_type, ''R'',f.actual_g,f.actual_g*-1))
79 FII_PL_XTD_AMT,
80 1 FII_ORDER_BY
81 FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
82 ( SELECT /*+ NO_MERGE cardinality(gt 1) */ *
83 FROM fii_time_structures cal, fii_pmv_aggrt_gt gt
84 where report_date in (:ASOF_DATE)
85 and ( bitand(cal.record_type_id, :ACTUAL_BITAND) =
86 :ACTUAL_BITAND)
87 ) inner_inline_view
88 WHERE f.time_id = inner_inline_view.time_id
89 AND f.period_type_id = inner_inline_view.period_type_id
90 AND f.parent_company_id = inner_inline_view.parent_company_id
91 AND f.company_id = inner_inline_view.company_id
92 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
93 AND f.cost_center_id = inner_inline_view.cc_id
94 AND f.top_node_fin_cat_type IN ('||'''R'''||','||'''OE'''||','||'''CGS'''||')
95 UNION ALL
96 SELECT '||''''||l_source_cogs||''''||' FII_PL_SOURCE,
97 SUM(f.actual_g) FII_PL_XTD_AMT,
98 3 FII_ORDER_BY
99 FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
100 ( SELECT /*+ NO_MERGE cardinality(gt 1) */ *
101 FROM fii_time_structures cal, fii_pmv_aggrt_gt gt
102 where report_date in (:ASOF_DATE)
103 and ( bitand(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND )
104 ) inner_inline_view
105 WHERE f.time_id = inner_inline_view.time_id
106 AND f.period_type_id = inner_inline_view.period_type_id
107 AND f.parent_company_id = inner_inline_view.parent_company_id
108 AND f.company_id = inner_inline_view.company_id
109 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
110 AND f.cost_center_id = inner_inline_view.cc_id
111 AND f.top_node_fin_cat_type=''CGS''
112 ) ORDER BY FII_ORDER_BY)
113 ';
114
115
116 return sqlstmt;
117
118 END get_pl_graph_val;
119
120
121 PROCEDURE get_rev_trend (
122 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
123 expense_sum_sql out NOCOPY VARCHAR2,
124 expense_sum_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
125
126 BEGIN
127 fii_ea_util_pkg.reset_globals;
128 fii_ea_util_pkg.g_fin_type := 'R';
129
130 fii_pl_page_pkg.get_expense_sum(p_page_parameter_tbl,
131 expense_sum_sql,
132 expense_sum_output);
133 END get_rev_trend;
134
135 PROCEDURE get_exp_trend (
136 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
137 expense_sum_sql out NOCOPY VARCHAR2,
138 expense_sum_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
139
140 BEGIN
141 fii_ea_util_pkg.reset_globals;
142 fii_ea_util_pkg.g_fin_type := 'OE';
143
144 fii_pl_page_pkg.get_expense_sum(p_page_parameter_tbl,
145 expense_sum_sql,
146 expense_sum_output);
147 END get_exp_trend;
148
149 PROCEDURE get_cogs_trend (
150 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
151 expense_sum_sql out NOCOPY VARCHAR2,
152 expense_sum_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
153
154 BEGIN
155 fii_ea_util_pkg.reset_globals;
156 fii_ea_util_pkg.g_fin_type := 'CGS';
157
158 fii_pl_page_pkg.get_expense_sum(p_page_parameter_tbl,
159 expense_sum_sql,
160 expense_sum_output);
161 END get_cogs_trend;
162
163 PROCEDURE get_mar_trend (
164 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
165 expense_sum_sql out NOCOPY VARCHAR2,
166 expense_sum_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
167
168 BEGIN
169 fii_ea_util_pkg.reset_globals;
170
171 fii_pl_page_pkg.get_margin_sum(p_page_parameter_tbl,
172 expense_sum_sql,
173 expense_sum_output);
174 END get_mar_trend;
175
176 PROCEDURE get_expense_sum (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
177 expense_sum_sql out NOCOPY VARCHAR2, expense_sum_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
178 IS
179 sqlstmt VARCHAR2(32000);
180 l_pk VARCHAR2(30);
181 l_name VARCHAR2(100);
182 l_time_comp VARCHAR2(20);
183 l_prior_or_budget VARCHAR2(1000);
184 l_prior_or_budget1 VARCHAR2(1000);
185 l_curr_effective_num NUMBER;
186 l_min_start_date DATE;
187 p_aggrt_gt_is_empty VARCHAR2(1);
188 p_non_aggrt_gt_is_empty VARCHAR2(1);
189
190 BEGIN
191
192 fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);
193 fii_ea_util_pkg.populate_security_gt_tables(
194 p_aggrt_gt_is_empty, p_non_aggrt_gt_is_empty);
195
196 CASE fii_ea_util_pkg.g_page_period_type
197 WHEN 'FII_TIME_ENT_PERIOD' THEN
198 l_pk := 'ent_period_id';
199 l_name := 'to_char(t.start_date,''Mon'')';
200
201 WHEN 'FII_TIME_ENT_QTR' THEN
202 l_pk := 'ent_qtr_id';
203 l_name := 'replace(fnd_message.get_string(''FII'',''FII_QUARTER_LABEL''),''&QUARTER_NUMBER'',t.sequence)';
204
205 WHEN 'FII_TIME_ENT_YEAR' THEN
206 l_pk := 'ent_year_id';
207
208 SELECT MIN(start_date) into l_min_start_date
209 FROM fii_time_ent_period;
210
211 SELECT NVL(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(
212 fii_time_api.ent_pyr_start(
213 fii_time_api.ent_pyr_start(
214 fii_ea_util_pkg.g_as_of_date)))),l_min_start_date)
215 INTO fii_ea_util_pkg.g_py_sday
216 FROM dual;
217
218 SELECT NVL(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(
219 fii_time_api.ent_pyr_start(
220 fii_time_api.ent_pyr_start(
221 fii_ea_util_pkg.g_previous_asof_date)))),l_min_start_date)
222 INTO fii_ea_util_pkg.g_five_yr_back
223 FROM dual;
224
225 END CASE;
226
227 /* if budget is selected, the prior amount column will return 0 */
228 IF (fii_ea_util_pkg.g_time_comp = 'SEQUENTIAL') OR
229 (fii_ea_util_pkg.g_time_comp = 'FORECAST') THEN
230 l_prior_or_budget :='case when t.start_date between :P_EXP_ASOF
231 and :CY_PERIOD_END
232 then f.forecast_g else TO_NUMBER(NULL) end FORECAST ';
233 ELSIF (fii_ea_util_pkg.g_time_comp = 'YEARLY') THEN
234 l_prior_or_budget := 'to_number(NULL) FORECAST ';
235 ELSIF (fii_ea_util_pkg.g_time_comp = 'BUDGET') THEN
236 l_prior_or_budget := ' to_number(NULL) FORECAST ';
237 END IF;
238
239 /* ----------------------------------
240 FII_MEASURE1 = Time Level Name
241 FII_MEASURE2 = Current Year XTotal
242 FII_MEASURE3 = Prior Year XTotal
243 FII_MEASURE4 = Current Year XTD
244 FII_MEASURE5 = Prior Year XTD
245 * ----------------------------------*/
246
247 IF fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN
248 sqlstmt := '
249 select t.name VIEWBY,
250 t.'||l_pk||' VIEWBYID,
251 sum(CY_QTOT) FII_MEASURE2,
252 sum(PY_QTOT) FII_MEASURE3,
253 sum(CY_QTD) FII_MEASURE4,
254 sum(PY_QTD) FII_MEASURE5,
255 sum(BUDGET) FII_MEASURE7,
256 sum(FORECAST) FII_MEASURE8,
257 sum(FORECAST) FII_MEASURE9,
258 NVL(sum(CY_QTOT), 0) + NVL(sum(CY_QTD), 0) FII_CAL1
259 from (
260 select t.sequence FII_SEQUENCE,
261 f.actual_g CY_QTOT,
262 TO_NUMBER(NULL) PY_QTOT,
263 TO_NUMBER(NULL) CY_QTD,
264 TO_NUMBER(NULL) PY_QTD,
265 f.budget_g BUDGET,
266 TO_NUMBER(NULL) FORECAST
267 from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,
268 fii_pmv_aggrt_gt gt,
269 '||fii_ea_util_pkg.g_page_period_type||' t
270 where f.parent_company_id = gt.parent_company_id
271 and f.company_id = gt.company_id
272 and f.parent_cost_center_id = gt.parent_cc_id
273 and f.cost_center_id = gt.cc_id
274 and f.top_node_fin_cat_type = :FIN_TYPE
275 and f.time_id = t.'||l_pk||'
276 and f.period_type_id = :PERIOD_TYPE
277 and t.start_date between :FIVE_YR_BACK
278 and :ENT_PYR_END
279 union all
280 select t.sequence FII_SEQUENCE,
281 TO_NUMBER(NULL) CY_QTOT,
282 TO_NUMBER(NULL) PY_QTOT,
283 case when bitand(inner_inline_view.record_type_id, :ACTUAL_BITAND)=:ACTUAL_BITAND
284 then f.actual_g else null end CY_QTD,
285 TO_NUMBER(NULL) PY_QTD,
286 case when bitand(inner_inline_view.record_type_id, :BUDGET_BITAND)=:BUDGET_BITAND
287 then f.budget_g else null end BUDGET,
288 case when bitand(inner_inline_view.record_type_id, :FORECAST_BITAND)=:FORECAST_BITAND
289 then f.forecast_g else null end FORECAST
290 from fii_gl_trend_sum_mv'|| fii_ea_util_pkg.g_curr_view ||' f,
291 '||fii_ea_util_pkg.g_page_period_type||' t,
292 (SELECT /*+ NO_MERGE cardinality(gt 1) */ *
293 FROM fii_time_structures cal,
294 fii_pmv_aggrt_gt gt
295 WHERE cal.report_date = &BIS_CURRENT_ASOF_DATE
296 AND (bitand(cal.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND OR
297 bitand(cal.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND OR
298 bitand(cal.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND)
299 ) inner_inline_view,
300 fii_time_day day
301 where f.time_id = inner_inline_view.time_id
302 and f.period_type_id = inner_inline_view.period_type_id
303 and f.parent_company_id = inner_inline_view.parent_company_id
304 and f.company_id = inner_inline_view.company_id
305 and f.parent_cost_center_id = inner_inline_view.parent_cc_id
306 and f.cost_center_id = inner_inline_view.cc_id
307 and f.top_node_fin_cat_type = :FIN_TYPE
308 and inner_inline_view.report_date = day.report_date
309 and day.'||l_pk||' = t.'||l_pk||'
310 ) g1, '||fii_ea_util_pkg.g_page_period_type||' t
311 where FII_SEQUENCE (+)= t.sequence
312 and t.start_date >= :PY_SAME_DAY
313 and t.end_date <= :ENT_CYR_END
314 group by t.sequence, t.name, t.'||l_pk||'
315 order by t.sequence';
316
317 ELSIF (fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') and
318 (fii_ea_util_pkg.g_time_comp = 'SEQUENTIAL') THEN
319 sqlstmt := '
320 select t.name VIEWBY,
321 t.'||l_pk||' VIEWBYID,
322 CY_QTOT FII_MEASURE2,
323 PY_QTOT FII_MEASURE3,
324 CY_QTD FII_MEASURE4,
325 PY_QTD FII_MEASURE5,
326 BUDGET FII_MEASURE7,
327 FORECAST FII_MEASURE8,
328 FORECAST FII_MEASURE9,
329 NVL(CY_QTOT, 0) + NVL(CY_QTD, 0) FII_CAL1
330 from
331 (select inner_inline_view2.FII_SEQUENCE FII_EFFECTIVE_NUM,
332 sum(CY_QTOT) CY_QTOT,
333 sum(PY_QTOT) PY_QTOT,
334 sum(CY_QTD) CY_QTD,
335 sum(PY_QTD) PY_QTD,
336 sum(BUDGET) BUDGET,
337 sum(FORECAST) FORECAST
338 from
339 (select t.'||l_pk||' FII_SEQUENCE,
340 sum(case when t.'||l_pk||' <> :CURR_EFFECTIVE_SEQ
341 then f.actual_g else TO_NUMBER(NULL) end) CY_QTOT,
342 TO_NUMBER(NULL) PY_QTOT,
343 TO_NUMBER(NULL) CY_QTD,
344 TO_NUMBER(NULL) PY_QTD,
345 sum(case when t.start_date between :P_EXP_ASOF
346 and :CY_PERIOD_END
347 then f.budget_g else TO_NUMBER(NULL) end) BUDGET,
348 sum(case when t.start_date between :P_EXP_ASOF
349 and :CY_PERIOD_END
350 then f.forecast_g else TO_NUMBER(NULL) end) FORECAST
351 from fii_gl_trend_sum_mv'|| fii_ea_util_pkg.g_curr_view ||' f,
352 '||fii_ea_util_pkg.g_page_period_type||' t,
353 fii_pmv_aggrt_gt gt
354 where f.parent_company_id = gt.parent_company_id
355 and f.company_id = gt.company_id
356 and f.parent_cost_center_id = gt.parent_cc_id
357 and f.cost_center_id = gt.cc_id
358 and f.top_node_fin_cat_type = :FIN_TYPE
359 and f.time_id = t.'||l_pk||'
360 and f.period_type_id = :PERIOD_TYPE
361 and t.start_date between :P_EXP_START
362 and &BIS_CURRENT_ASOF_DATE
363 group by t.'||l_pk||'
364 union all
365 select :CURR_EFFECTIVE_SEQ FII_SEQUENCE,
366 TO_NUMBER(NULL) CY_QTOT,
367 TO_NUMBER(NULL) PY_QTOT,
368 case when inner_inline_view.report_date = &BIS_CURRENT_ASOF_DATE AND
369 bitand(inner_inline_view.record_type_id, :ACTUAL_BITAND)=:ACTUAL_BITAND
370 then f.actual_g else TO_NUMBER(NULL) end CY_QTD,
371 TO_NUMBER(NULL) PY_QTD,
372 case when inner_inline_view.report_date = &BIS_CURRENT_ASOF_DATE AND
373 bitand(inner_inline_view.record_type_id, :BUDGET_BITAND)=:BUDGET_BITAND
374 then f.budget_g else to_number(null) end BUDGET,
375 case when inner_inline_view.report_date = &BIS_CURRENT_ASOF_DATE AND
376 bitand(inner_inline_view.record_type_id, :FORECAST_BITAND)=:FORECAST_BITAND
377 then f.forecast_g else to_number(null) end FORECAST
378 from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,
379 (SELECT /*+ NO_MERGE cardinality(gt 1) */ *
380 FROM fii_time_structures cal,
381 fii_pmv_aggrt_gt gt
382 WHERE cal.report_date in (&BIS_CURRENT_ASOF_DATE,
383 :P_EXP_ASOF)
384 AND (bitand(cal.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND OR
385 bitand(cal.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND OR
386 bitand(cal.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND)) inner_inline_view
387 where f.time_id = inner_inline_view.time_id
388 and f.period_type_id = inner_inline_view.period_type_id
389 and f.parent_company_id = inner_inline_view.parent_company_id
390 and f.company_id = inner_inline_view.company_id
391 and f.parent_cost_center_id = inner_inline_view.parent_cc_id
392 and f.cost_center_id = inner_inline_view.cc_id
393 and f.top_node_fin_cat_type = :FIN_TYPE
394 ) inner_inline_view2
395 group by inner_inline_view2.FII_SEQUENCE
396 ) g1, '||fii_ea_util_pkg.g_page_period_type||' t
397 where g1.fii_effective_num (+)= t.'||l_pk||'
398 and t.start_date <= &BIS_CURRENT_ASOF_DATE
399 and t.start_date > :P_EXP_START
400 order by t.start_date';
401 ELSE
402 sqlstmt := '
403 select t.name VIEWBY,
404 t.'||l_pk||' VIEWBYID,
405 CY_QTOT FII_MEASURE2,
406 PY_QTOT FII_MEASURE3,
407 CY_QTD FII_MEASURE4,
408 PY_QTD FII_MEASURE5,
409 BUDGET FII_MEASURE7,
410 FORECAST FII_MEASURE8,
411 FORECAST FII_MEASURE9,
412 NVL(CY_QTOT, 0) + NVL(CY_QTD, 0) FII_CAL1
413 from
414 (select inner_inline_view2.FII_SEQUENCE FII_EFFECTIVE_NUM,
415 sum(CY_QTOT) CY_QTOT,
416 sum(PY_QTOT) PY_QTOT,
417 sum(CY_QTD) CY_QTD,
418 sum(PY_QTD) PY_QTD,
419 sum(BUDGET) BUDGET,
420 sum(FORECAST) FORECAST
421 from
422 (select t.sequence FII_SEQUENCE,
423 case when t.sequence <> :CURR_EFFECTIVE_SEQ
424 then (case when t.start_date between :P_EXP_ASOF
425 and :CY_PERIOD_END
426 then f.actual_g else TO_NUMBER(NULL)end)
427 else TO_NUMBER(NULL) end CY_QTOT,
428 case when t.start_date between :P_EXP_START
429 and :P_EXP_ASOF
430 then f.actual_g else TO_NUMBER(NULL) end PY_QTOT,
431 TO_NUMBER(NULL) CY_QTD,
432 TO_NUMBER(NULL) PY_QTD,
433 case when t.start_date between :P_EXP_ASOF
434 and :CY_PERIOD_END
435 then f.budget_g else TO_NUMBER(NULL) end BUDGET,
436 '||l_prior_or_budget||'
437 from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,
438 '||fii_ea_util_pkg.g_page_period_type||' t,
439 fii_pmv_aggrt_gt gt
440 where f.time_id = t.'||l_pk||'
441 and f.period_type_id = :PERIOD_TYPE
442 and f.parent_company_id = gt.parent_company_id
443 and f.company_id = gt.company_id
444 and f.parent_cost_center_id = gt.parent_cc_id
445 and f.cost_center_id = gt.cc_id
446 and f.top_node_fin_cat_type = :FIN_TYPE
447 and t.start_date between :P_EXP_START
448 and &BIS_CURRENT_ASOF_DATE
449 union all
450 select :CURR_EFFECTIVE_SEQ FII_SEQUENCE,
451 TO_NUMBER(NULL) CY_QTOT,
452 TO_NUMBER(NULL) PY_QTOT,
453 case when inner_inline_view.report_date = &BIS_CURRENT_ASOF_DATE and
454 bitand(inner_inline_view.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND
455 then f.actual_g else TO_NUMBER(NULL) end CY_QTD,
456 case when inner_inline_view.report_date = :P_EXP_ASOF and
457 bitand(inner_inline_view.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND
458 then f.actual_g else TO_NUMBER(NULL) end PY_QTD,
459 case when inner_inline_view.report_date = &BIS_CURRENT_ASOF_DATE and
460 bitand(inner_inline_view.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND
461 then f.budget_g else to_number(null) end BUDGET,
462 case when inner_inline_view.report_date = &BIS_CURRENT_ASOF_DATE and
463 bitand(inner_inline_view.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND
464 then f.forecast_g else to_number(null) end FORECAST
465 from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,
466 (SELECT /*+ NO_MERGE cardinality(gt 1) */ *
467 FROM fii_time_structures cal,
468 fii_pmv_aggrt_gt gt
469 WHERE cal.report_date in (&BIS_CURRENT_ASOF_DATE,
470 :P_EXP_ASOF)
471 AND (bitand(cal.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND OR
472 bitand(cal.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND OR
473 bitand(cal.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND)
474 ) inner_inline_view
475 where f.time_id = inner_inline_view.time_id
476 and f.period_type_id = inner_inline_view.period_type_id
477 and f.parent_company_id = inner_inline_view.parent_company_id
478 and f.company_id = inner_inline_view.company_id
479 and f.parent_cost_center_id = inner_inline_view.parent_cc_id
480 and f.cost_center_id = inner_inline_view.cc_id
481 and f.top_node_fin_cat_type = :FIN_TYPE
482 ) inner_inline_view2
483 group by inner_inline_view2.FII_SEQUENCE
484 ) g1, '||fii_ea_util_pkg.g_page_period_type||' t
485 where g1.fii_effective_num (+)= t.sequence
486 and t.start_date <= &BIS_CURRENT_ASOF_DATE
487 and t.start_date > :P_EXP_BEGIN
488 order by t.start_date';
489 END IF;
490
491 fii_ea_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl,
492 expense_sum_sql, expense_sum_output);
493 END get_expense_sum;
494
495
496 PROCEDURE get_margin_sum (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
497 expense_sum_sql out NOCOPY VARCHAR2, expense_sum_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
498 IS
499 sqlstmt VARCHAR2(32000);
500 l_pk VARCHAR2(30);
501 l_name VARCHAR2(100);
502 l_time_comp VARCHAR2(20);
503 l_curr_effective_num NUMBER;
504 l_min_start_date DATE;
505 p_aggrt_gt_is_empty VARCHAR2(1);
506 p_non_aggrt_gt_is_empty VARCHAR2(1);
507
508 BEGIN
509
510 fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);
511 fii_ea_util_pkg.populate_security_gt_tables(
512 p_aggrt_gt_is_empty, p_non_aggrt_gt_is_empty);
513
514 CASE fii_ea_util_pkg.g_page_period_type
515 WHEN 'FII_TIME_ENT_PERIOD' THEN
516 l_pk := 'ent_period_id';
517 l_name := 'to_char(t.start_date,''Mon'')';
518
519 WHEN 'FII_TIME_ENT_QTR' THEN
520 l_pk := 'ent_qtr_id';
521 l_name := 'replace(fnd_message.get_string(''FII'',''FII_QUARTER_LABEL''),''&QUARTER_NUMBER'',t.sequence)';
522
523 WHEN 'FII_TIME_ENT_YEAR' THEN
524 l_pk := 'ent_year_id';
525
526 SELECT MIN(start_date) into l_min_start_date
527 FROM fii_time_ent_period;
528
529 SELECT NVL(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(
530 fii_time_api.ent_pyr_start(
531 fii_time_api.ent_pyr_start(
532 fii_ea_util_pkg.g_as_of_date)))),l_min_start_date)
533 INTO fii_ea_util_pkg.g_py_sday
534 FROM dual;
535
536 SELECT NVL(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(
537 fii_time_api.ent_pyr_start(
538 fii_time_api.ent_pyr_start(
539 fii_ea_util_pkg.g_previous_asof_date)))),l_min_start_date)
540 INTO fii_ea_util_pkg.g_five_yr_back
541 FROM dual;
542
543 END CASE;
544
545
546 /* ----------------------------------
547 FII_MEASURE1 = Time Level Name
548 FII_MEASURE2 = Current Year XTotal
549 FII_MEASURE3 = Prior Year XTotal
550 FII_MEASURE4 = Current Year XTD
551 FII_MEASURE5 = Prior Year XTD
552 * ----------------------------------*/
553
554 IF fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN
555 sqlstmt := '
556 select t.name VIEWBY,
557 t.'||l_pk||' VIEWBYID,
558 (nvl(sum(CY_QTOT_REV), 0) - nvl(sum(CY_QTOT_EXP), 0) - nvl(sum(CY_QTOT_CGS), 0)) /
559 nullif(abs(nvl(sum(CY_QTOT_REV), 0)), 0) * 100 FII_MEASURE2,
560 to_number(NULL) FII_MEASURE3,
561 (nvl(sum(CY_QTD_REV), 0) - nvl(sum(CY_QTD_EXP), 0) - nvl(sum(CY_QTD_CGS), 0)) /
562 nullif(abs(nvl(sum(CY_QTD_REV), 0)), 0) * 100 FII_MEASURE4,
563 to_number(NULL) FII_MEASURE5,
564 ((nvl(sum(CY_QTOT_REV), 0) + nvl(sum(CY_QTD_REV), 0)) -
565 (nvl(sum(CY_QTOT_EXP), 0) + nvl(sum(CY_QTD_EXP), 0)) -
566 (nvl(sum(CY_QTOT_CGS), 0) + nvl(sum(CY_QTD_CGS), 0))) /
567 nullif(abs(nvl(sum(CY_QTOT_REV), 0) + nvl(sum(CY_QTD_REV),0)), 0) * 100 FII_CY_XTD,
568 to_number(NULL) FII_PY_XTD,
569 ((nvl(sum(sum(CY_QTOT_REV)) over(), 0) + nvl(sum(sum(CY_QTD_REV)) over(), 0)) -
570 (nvl(sum(sum(CY_QTOT_EXP)) over(), 0) + nvl(sum(sum(CY_QTD_EXP)) over(), 0)) -
571 (nvl(sum(sum(CY_QTOT_CGS)) over(), 0) + nvl(sum(sum(CY_QTD_CGS)) over(), 0)))
572 / nullif(abs(nvl(sum(sum(CY_QTOT_REV)) over(), 0) + nvl(sum(sum(CY_QTD_REV)) over(), 0)), 0)
573 * 100 FII_CY_XTD_GT,
574 to_number(NULL) FII_PY_XTD_GT
575 from (
576 select t.sequence FII_SEQUENCE,
577 decode(f.top_node_fin_cat_type, ''R'', f.actual_g, to_number(null)) CY_QTOT_REV,
578 decode(f.top_node_fin_cat_type, ''OE'', f.actual_g, to_number(null)) CY_QTOT_EXP,
579 decode(f.top_node_fin_cat_type, ''CGS'', f.actual_g, to_number(null)) CY_QTOT_CGS,
580 TO_NUMBER(NULL) CY_QTD_REV,
581 TO_NUMBER(NULL) CY_QTD_EXP,
582 TO_NUMBER(NULL) CY_QTD_CGS
583 from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,
584 fii_pmv_aggrt_gt gt,
585 '||fii_ea_util_pkg.g_page_period_type||' t
586 where f.parent_company_id = gt.parent_company_id
587 and f.company_id = gt.company_id
588 and f.parent_cost_center_id = gt.parent_cc_id
589 and f.cost_center_id = gt.cc_id
590 and f.top_node_fin_cat_type IN (''R'', ''OE'', ''CGS'')
591 and f.time_id = t.'||l_pk||'
592 and f.period_type_id = :PERIOD_TYPE
593 and t.start_date between :FIVE_YR_BACK
594 and :ENT_PYR_END
595 union all
596 select t.sequence FII_SEQUENCE,
597 TO_NUMBER(NULL) CY_QTOT_REV,
598 TO_NUMBER(NULL) CY_QTOT_EXP,
599 TO_NUMBER(NULL) CY_QTOT_CGS,
600 decode(f.top_node_fin_cat_type, ''R'', f.actual_g, to_number(null)) CY_QTD_REV,
601 decode(f.top_node_fin_cat_type, ''OE'', f.actual_g, to_number(null)) CY_QTD_EXP,
602 decode(f.top_node_fin_cat_type, ''CGS'', f.actual_g, to_number(null)) CY_QTD_CGS
603 from fii_gl_trend_sum_mv'|| fii_ea_util_pkg.g_curr_view ||' f,
604 '||fii_ea_util_pkg.g_page_period_type||' t,
605 (SELECT /*+ NO_MERGE cardinality(gt 1) */ *
606 FROM fii_time_structures cal,
607 fii_pmv_aggrt_gt gt
608 WHERE cal.report_date = &BIS_CURRENT_ASOF_DATE
609 AND bitand(cal.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
610 ) inner_inline_view,
611 fii_time_day day
612 where f.time_id = inner_inline_view.time_id
613 and f.period_type_id = inner_inline_view.period_type_id
614 and f.parent_company_id = inner_inline_view.parent_company_id
615 and f.company_id = inner_inline_view.company_id
616 and f.parent_cost_center_id = inner_inline_view.parent_cc_id
617 and f.cost_center_id = inner_inline_view.cc_id
618 and f.top_node_fin_cat_type IN (''R'', ''OE'', ''CGS'')
619 and inner_inline_view.report_date = day.report_date
620 and day.'||l_pk||' = t.'||l_pk||'
621 ) g1, '||fii_ea_util_pkg.g_page_period_type||' t
622 where FII_SEQUENCE (+)= t.sequence
623 and t.start_date >= :PY_SAME_DAY
624 and t.end_date <= :ENT_CYR_END
625 group by t.sequence, t.name, t.'||l_pk||'
626 order by t.sequence';
627
628 ELSIF (fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') and
629 (fii_ea_util_pkg.g_time_comp = 'SEQUENTIAL') THEN
630 sqlstmt := '
631 select t.name VIEWBY,
632 t.'||l_pk||' VIEWBYID,
633 CY_QTOT FII_MEASURE2,
634 to_number(NULL) FII_MEASURE3,
635 CY_QTD FII_MEASURE4,
636 to_number(NULL) FII_MEASURE5,
637 FII_CY_XTD FII_CY_XTD,
638 to_number(NULL) FII_PY_XTD,
639 FII_CY_XTD_GT FII_CY_XTD_GT,
640 to_number(NULL) FII_PY_XTD_GT
641 from
642 (select inner_inline_view2.FII_SEQUENCE FII_EFFECTIVE_NUM,
643 (nvl(sum(CY_QTOT_REV), 0) - nvl(sum(CY_QTOT_EXP), 0) - nvl(sum(CY_QTOT_CGS), 0)) /
644 nullif(abs(nvl(sum(CY_QTOT_REV), 0)), 0) * 100 CY_QTOT,
645 (nvl(sum(CY_QTD_REV), 0) - nvl(sum(CY_QTD_EXP), 0) - nvl(sum(CY_QTD_CGS), 0)) /
646 nullif(abs(nvl(sum(CY_QTD_REV), 0)), 0) * 100 CY_QTD,
647 ((nvl(sum(CY_QTOT_REV), 0) + nvl(sum(CY_QTD_REV), 0)) -
648 (nvl(sum(CY_QTOT_EXP), 0) + nvl(sum(CY_QTD_EXP), 0)) -
649 (nvl(sum(CY_QTOT_CGS), 0) + nvl(sum(CY_QTD_CGS), 0))) /
650 nullif(abs(nvl(sum(CY_QTOT_REV), 0) + nvl(sum(CY_QTD_REV), 0)), 0) * 100 FII_CY_XTD,
651 ((nvl(sum(sum(CY_QTOT_REV)) over(), 0) + nvl(sum(sum(CY_QTD_REV)) over(), 0)) -
652 (nvl(sum(sum(CY_QTOT_EXP)) over(), 0) + nvl(sum(sum(CY_QTD_EXP)) over(), 0)) -
653 (nvl(sum(sum(CY_QTOT_CGS)) over(), 0) + nvl(sum(sum(CY_QTD_CGS)) over(), 0))) /
654 nullif(abs(nvl(sum(sum(CY_QTOT_REV)) over(), 0) + nvl(sum(sum(CY_QTD_REV)) over(), 0)), 0) * 100 FII_CY_XTD_GT
655 from
656 (select t.'||l_pk||' FII_SEQUENCE,
657 sum(case when t.'||l_pk||' <> :CURR_EFFECTIVE_SEQ
658 then decode(f.top_node_fin_cat_type, ''R'', f.actual_g, to_number(null))
659 else TO_NUMBER(NULL) end) CY_QTOT_REV,
660 sum(case when t.'||l_pk||' <> :CURR_EFFECTIVE_SEQ
661 then decode(f.top_node_fin_cat_type, ''OE'', f.actual_g, to_number(null))
662 else TO_NUMBER(NULL) end) CY_QTOT_EXP,
663 sum(case when t.'||l_pk||' <> :CURR_EFFECTIVE_SEQ
664 then decode(f.top_node_fin_cat_type, ''CGS'', f.actual_g, to_number(null))
665 else TO_NUMBER(NULL) end) CY_QTOT_CGS,
666 TO_NUMBER(NULL) CY_QTD_REV,
667 TO_NUMBER(NULL) CY_QTD_EXP,
668 TO_NUMBER(NULL) CY_QTD_CGS
669 from fii_gl_trend_sum_mv'|| fii_ea_util_pkg.g_curr_view ||' f,
670 '||fii_ea_util_pkg.g_page_period_type||' t,
671 fii_pmv_aggrt_gt gt
672 where f.parent_company_id = gt.parent_company_id
673 and f.company_id = gt.company_id
674 and f.parent_cost_center_id = gt.parent_cc_id
675 and f.cost_center_id = gt.cc_id
676 and f.top_node_fin_cat_type IN (''R'', ''OE'', ''CGS'')
677 and f.time_id = t.'||l_pk||'
678 and f.period_type_id = :PERIOD_TYPE
679 and t.start_date between :P_EXP_START
680 and &BIS_CURRENT_ASOF_DATE
681 group by t.'||l_pk||'
682 union all
683 select :CURR_EFFECTIVE_SEQ FII_SEQUENCE,
684 TO_NUMBER(NULL) CY_QTOT_REV,
685 TO_NUMBER(NULL) CY_QTOT_EXP,
686 TO_NUMBER(NULL) CY_QTOT_CGS,
687 case when inner_inline_view.report_date = &BIS_CURRENT_ASOF_DATE
688 then decode(f.top_node_fin_cat_type, ''R'', f.actual_g, to_number(null))
689 else TO_NUMBER(NULL) end CY_QTD_REV,
690 case when inner_inline_view.report_date = &BIS_CURRENT_ASOF_DATE
691 then decode(f.top_node_fin_cat_type, ''OE'', f.actual_g, to_number(null))
692 else TO_NUMBER(NULL) end CY_QTD_EXP,
693 case when inner_inline_view.report_date = &BIS_CURRENT_ASOF_DATE
694 then decode(f.top_node_fin_cat_type, ''CGS'', f.actual_g, to_number(null))
695 else TO_NUMBER(NULL) end CY_QTD_CGS
696 from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,
697 (SELECT /*+ NO_MERGE cardinality(gt 1) */ *
698 FROM fii_time_structures cal,
699 fii_pmv_aggrt_gt gt
700 WHERE cal.report_date in (&BIS_CURRENT_ASOF_DATE,
701 :P_EXP_ASOF)
702 AND bitand(cal.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
703 ) inner_inline_view
704 where f.time_id = inner_inline_view.time_id
705 and f.period_type_id = inner_inline_view.period_type_id
706 and f.parent_company_id = inner_inline_view.parent_company_id
707 and f.company_id = inner_inline_view.company_id
708 and f.parent_cost_center_id = inner_inline_view.parent_cc_id
709 and f.cost_center_id = inner_inline_view.cc_id
710 and f.top_node_fin_cat_type IN (''R'', ''OE'', ''CGS'')
711 ) inner_inline_view2
712 group by inner_inline_view2.FII_SEQUENCE
713 ) g1, '||fii_ea_util_pkg.g_page_period_type||' t
714 where g1.fii_effective_num (+)= t.'||l_pk||'
715 and t.start_date <= &BIS_CURRENT_ASOF_DATE
716 and t.start_date > :P_EXP_START
717 order by t.start_date';
718
719 ELSE
720 sqlstmt := '
721 select t.name VIEWBY,
722 t.'||l_pk||' VIEWBYID,
723 CY_QTOT FII_MEASURE2,
724 PY_QTOT FII_MEASURE3,
725 PY_QTD FII_MEASURE5,
726 PY_QTOT FII_PY_XTD,
727 PY_XTD_GT FII_PY_XTD_GT,
728 CY_QTD FII_MEASURE4,
729 CY_XTD FII_CY_XTD ,
730 CY_XTD_GT FII_CY_XTD_GT
731 from
732 (select inner_inline_view2.FII_SEQUENCE FII_EFFECTIVE_NUM,
733 (nvl(sum(CY_QTOT_REV), 0) - nvl(sum(CY_QTOT_EXP), 0) - nvl(sum(CY_QTOT_CGS), 0)) /
734 nullif(abs(nvl(sum(CY_QTOT_REV), 0)), 0) * 100 CY_QTOT,
735 (nvl(sum(PY_QTOT_REV), 0) - nvl(sum(PY_QTOT_EXP), 0) - nvl(sum(PY_QTOT_CGS), 0)) /
736 nullif(abs(nvl(sum(PY_QTOT_REV), 0)), 0) * 100 PY_QTOT,
737 (sum(CY_QTD_REV) - sum(CY_QTD_EXP) - sum(CY_QTD_CGS)) /
738 nullif(abs(nvl(sum(CY_QTD_REV), 0)), 0) * 100 CY_QTD,
739 (sum(PY_QTD_REV) - sum(PY_QTD_EXP) - sum(PY_QTD_CGS)) /
740 nullif(abs(nvl(sum(PY_QTD_REV), 0)), 0) * 100 PY_QTD,
741 ((nvl(sum(CY_QTOT_REV), 0) + nvl(sum(CY_QTD_REV), 0)) -
742 (nvl(sum(CY_QTOT_EXP), 0) + nvl(sum(CY_QTD_EXP), 0)) -
743 (nvl(sum(CY_QTOT_CGS), 0) + nvl(sum(CY_QTD_CGS), 0))) /
744 nullif(abs(nvl(sum(CY_QTOT_REV), 0) + nvl(sum(CY_QTD_REV), 0)), 0) * 100 CY_XTD,
745 ((nvl(sum(sum(CY_QTOT_REV)) over(), 0) + nvl(sum(sum(CY_QTD_REV)) over(), 0)) -
746 (nvl(sum(sum(CY_QTOT_EXP)) over(), 0) + nvl(sum(sum(CY_QTD_EXP)) over(), 0)) -
747 (nvl(sum(sum(CY_QTOT_CGS)) over(), 0) + nvl(sum(sum(CY_QTD_CGS)) over(), 0))) /
748 nullif(abs(nvl(sum(sum(CY_QTOT_REV)) over(), 0) + nvl(sum(sum(CY_QTD_REV)) over(), 0)), 0) * 100 CY_XTD_GT,
749 (nvl(sum(sum(PY_QTOT_REV)) over(), 0) - nvl(sum(sum(PY_QTOT_EXP)) over(), 0) - nvl(sum(sum(PY_QTOT_CGS)) over(), 0)) /
750 nullif(abs(nvl(sum(sum(PY_QTOT_REV)) over(), 0)), 0) * 100 PY_XTD_GT
751 from
752 (select t.sequence FII_SEQUENCE,
753 case when t.sequence <> :CURR_EFFECTIVE_SEQ
754 then (case when t.start_date between :P_EXP_ASOF
755 and :CY_PERIOD_END
756 then decode(f.top_node_fin_cat_type, ''R'', f.actual_g, to_number(null))
757 else TO_NUMBER(NULL)end)
758 else TO_NUMBER(NULL) end CY_QTOT_REV,
759 case when t.sequence <> :CURR_EFFECTIVE_SEQ
760 then (case when t.start_date between :P_EXP_ASOF
761 and :CY_PERIOD_END
762 then decode(f.top_node_fin_cat_type, ''OE'', f.actual_g, to_number(null))
763 else TO_NUMBER(NULL)end)
764 else TO_NUMBER(NULL) end CY_QTOT_EXP,
765 case when t.sequence <> :CURR_EFFECTIVE_SEQ
766 then (case when t.start_date between :P_EXP_ASOF
767 and :CY_PERIOD_END
768 then decode(f.top_node_fin_cat_type, ''CGS'', f.actual_g, to_number(null))
769 else TO_NUMBER(NULL)end)
770 else TO_NUMBER(NULL) end CY_QTOT_CGS,
771 case when t.start_date between :P_EXP_START
772 and :P_EXP_ASOF
773 then decode(f.top_node_fin_cat_type, ''R'', f.actual_g, to_number(null))
774 else TO_NUMBER(NULL) end PY_QTOT_REV,
775 case when t.start_date between :P_EXP_START
776 and :P_EXP_ASOF
777 then decode(f.top_node_fin_cat_type, ''OE'', f.actual_g, to_number(null))
778 else TO_NUMBER(NULL) end PY_QTOT_EXP,
779 case when t.start_date between :P_EXP_START
780 and :P_EXP_ASOF
781 then decode(f.top_node_fin_cat_type, ''CGS'', f.actual_g, to_number(null))
782 else TO_NUMBER(NULL) end PY_QTOT_CGS,
783 TO_NUMBER(NULL) CY_QTD_REV,
784 TO_NUMBER(NULL) CY_QTD_EXP,
785 TO_NUMBER(NULL) CY_QTD_CGS,
786 TO_NUMBER(NULL) PY_QTD_REV,
787 TO_NUMBER(NULL) PY_QTD_EXP,
788 TO_NUMBER(NULL) PY_QTD_CGS
789 from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,
790 '||fii_ea_util_pkg.g_page_period_type||' t,
791 fii_pmv_aggrt_gt gt
792 where f.time_id = t.'||l_pk||'
793 and f.period_type_id = :PERIOD_TYPE
794 and f.parent_company_id = gt.parent_company_id
795 and f.company_id = gt.company_id
796 and f.parent_cost_center_id = gt.parent_cc_id
797 and f.cost_center_id = gt.cc_id
798 and f.top_node_fin_cat_type IN (''R'', ''OE'', ''CGS'')
799 and t.start_date between :P_EXP_START
800 and &BIS_CURRENT_ASOF_DATE
801 union all
802 select :CURR_EFFECTIVE_SEQ FII_SEQUENCE,
803 TO_NUMBER(NULL) CY_QTOT_REV,
804 TO_NUMBER(NULL) CY_QTOT_EXP,
805 TO_NUMBER(NULL) CY_QTOT_CGS,
806 TO_NUMBER(NULL) PY_QTOT_REV,
807 TO_NUMBER(NULL) PY_QTOT_EXP,
808 TO_NUMBER(NULL) PY_QTOT_CGS,
809 case when inner_inline_view.report_date = &BIS_CURRENT_ASOF_DATE
810 then decode(f.top_node_fin_cat_type, ''R'', f.actual_g, to_number(null))
811 else TO_NUMBER(NULL) end CY_QTD_REV,
812 case when inner_inline_view.report_date = &BIS_CURRENT_ASOF_DATE
813 then decode(f.top_node_fin_cat_type, ''OE'', f.actual_g, to_number(null))
814 else TO_NUMBER(NULL) end CY_QTD_EXP,
815 case when inner_inline_view.report_date = &BIS_CURRENT_ASOF_DATE
816 then decode(f.top_node_fin_cat_type, ''CGS'', f.actual_g, to_number(null))
817 else TO_NUMBER(NULL) end CY_QTD_CGS,
818 case when inner_inline_view.report_date = :P_EXP_ASOF
819 then decode(f.top_node_fin_cat_type, ''R'', f.actual_g, to_number(null))
820 else TO_NUMBER(NULL) end PY_QTD_REV,
821 case when inner_inline_view.report_date = :P_EXP_ASOF
822 then decode(f.top_node_fin_cat_type, ''OE'', f.actual_g, to_number(null))
823 else TO_NUMBER(NULL) end PY_QTD_EXP,
824 case when inner_inline_view.report_date = :P_EXP_ASOF
825 then decode(f.top_node_fin_cat_type, ''CGS'', f.actual_g, to_number(null))
826 else TO_NUMBER(NULL) end PY_QTD_CGS
827 from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,
828 (SELECT /*+ NO_MERGE cardinality(gt 1) */ *
829 FROM fii_time_structures cal,
830 fii_pmv_aggrt_gt gt
831 WHERE cal.report_date in (&BIS_CURRENT_ASOF_DATE,
832 :P_EXP_ASOF)
833 AND bitand(cal.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
834 ) inner_inline_view
835 where f.time_id = inner_inline_view.time_id
836 and f.period_type_id = inner_inline_view.period_type_id
837 and f.parent_company_id = inner_inline_view.parent_company_id
838 and f.company_id = inner_inline_view.company_id
839 and f.parent_cost_center_id = inner_inline_view.parent_cc_id
840 and f.cost_center_id = inner_inline_view.cc_id
841 and f.top_node_fin_cat_type IN (''R'', ''OE'', ''CGS'')
842 ) inner_inline_view2
843 group by inner_inline_view2.FII_SEQUENCE
844 ) g1, '||fii_ea_util_pkg.g_page_period_type||' t
845 where g1.fii_effective_num (+)= t.sequence
846 and t.start_date <= &BIS_CURRENT_ASOF_DATE
847 and t.start_date > :P_EXP_BEGIN
848 order by t.start_date';
849
850 END IF;
851
852 fii_ea_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl,
853 expense_sum_sql, expense_sum_output);
854 END get_margin_sum;
855
856 ---------------------------------------------------------------------------------
857 -- Following procedure is used to form PMV SQL, which is used to retrieve data
858 -- for Gross Margin Table portlet and Gross Margin Summary report
859
860 PROCEDURE get_gross_margin( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
861 ,p_gross_margin_sql OUT NOCOPY VARCHAR2
862 ,p_gross_margin_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
863 )
864
865 IS
866 l_sqlstmt VARCHAR2(32767);
867 p_aggrt_viewby_id VARCHAR2(30);
868 p_snap_aggrt_viewby_id VARCHAR2(30);
869 p_nonaggrt_viewby_id VARCHAR2(50);
870 p_aggrt_gt_is_empty VARCHAR2(1);
871 p_non_aggrt_gt_is_empty VARCHAR2(1);
872 l_union_all VARCHAR2(15);
873 l_xtd_column VARCHAR2(10); -- At the time of hitting snapshot tables, l_xtd_xolumn is used
874 -- based on period type chosen i.e if column used to display xtd data
875 -- should be actual_curr_mtd/qtd/ytd
876 l_roll_column VARCHAR2(10);
877 l_aggrt_sql VARCHAR2(15000) := NULL;
878 l_sqlstmt1 VARCHAR2(15000) := NULL;
879 l_snap_sqlstmt1 VARCHAR2(15000) := NULL;
880 l_non_aggrt_sql VARCHAR2(15000) := NULL;
881 l_sqlstmt2 VARCHAR2(15000) := NULL;
882 l_snap_sqlstmt2 VARCHAR2(15000) := NULL;
883 l_trend_sum_mv_sql VARCHAR2(15000) := NULL;
884 l_trend_sum_mv_sql_port VARCHAR2(15000) := NULL;
885 l_viewby_drill_url VARCHAR2(300);
886 l_snap_prior VARCHAR2(10000);
887 l_trend_mv_prior VARCHAR2(10000);
888 l_agrt_base_prior VARCHAR2(10000);
889 l_if_leaf_flag VARCHAR2(1); -- local var to denote, if category or fud1 param chosen to run the report is a leaf or not..
890 l_fud2_enabled_flag VARCHAR2(1);
891 l_fud2_where VARCHAR2(300);
892 l_fud2_snap_where VARCHAR2(300);
893 l_fud2_from VARCHAR2(100);
894 l_fud1_decode VARCHAR2(300); -- local variable to append decode check for fud1, when viewby chosen is FUD1
895 l_budget_decode VARCHAR2(300); -- Since we can load budget only against category and fud1 summary nodes,
896 -- this local variable appends a check to agrt MV and base map MV queries, so that budget is checked only for xTD period.
897 -- Budget loaded for prior xTD should not result in any unwanted record, having 0/NA in all columns..
898 l_budget_snap_decode VARCHAR2(300); -- local variable analogous to l_budget_decode..it appends a similar check to snapshot query
899 l_function_name VARCHAR2(100);
900
901 BEGIN
902
903 -- Initialization. Calling fii_ea_util_pkg APIs necessary for constructing
904 -- the PMV sql
905
906 fii_ea_util_pkg.reset_globals;
907
908 -- Reassigning following variable to NULL, since it is assigned to OE in reset_globals procedure
909
910 fii_ea_util_pkg.g_fin_cat_type := NULL;
911
912 -- Call to get_parameters procedure
913 fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);
914
915 -- Following variable would store the FormFunction name.
916 -- Based on this, PMV SQL would be constructed for Gross Margin table portlet OR Gross Margin Summary report
917
918 IF (p_page_parameter_tbl.count > 0) THEN
919 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
920
921 IF (p_page_parameter_tbl(i).parameter_name = 'BIS_FXN_NAME') THEN
922 l_function_name := p_page_parameter_tbl(i).parameter_value;
923 END IF;
924
925 END LOOP;
926 END IF;
927
928 fii_ea_util_pkg.get_viewby_id(p_aggrt_viewby_id, p_snap_aggrt_viewby_id, p_nonaggrt_viewby_id);
929 fii_ea_util_pkg.populate_security_gt_tables(p_aggrt_gt_is_empty, p_non_aggrt_gt_is_empty);
930
931 CASE fii_ea_util_pkg.g_page_period_type -- we set different 'period type' dependent variables in this CASE structure
932
933 WHEN 'FII_TIME_ENT_YEAR' THEN
934 l_roll_column := 'qtd';
935 l_xtd_column := 'ytd' ;
936
937 WHEN 'FII_TIME_ENT_QTR' THEN
938 l_roll_column := 'mtd';
939 l_xtd_column := 'qtd' ;
940
941 WHEN 'FII_TIME_ENT_PERIOD' THEN
942 l_roll_column := 'mtd';
943 l_xtd_column := 'mtd' ;
944
945 ELSE
946 NULL;
947
948 END CASE;
949
950 -- When Compare To is Budget, we display Budget instead of Prior Income
951 -- l_snap_prior is used when hitting fii_gl_snap_sum_f
952 -- l_agrt_base_prior is used when hitting fii_gl_agrt_sum_mv OR fii_gl_base_map_mv
953 -- l_trend_mv_prior is used when hitting fii_gl_trend_sum_mv
954
955 IF (fii_ea_util_pkg.g_time_comp = 'BUDGET') THEN
956 l_snap_prior := ',NULL FII_PL_PRIOR_REVENUE
957 ,NULL FII_PL_PRIOR_COGS
958 ,NULL FII_PL_PRIOR_COGS_TOTAL_G
959 ,NULL FII_PL_PRIOR_REVENUE_TOTAL_G
960 ';
961 l_agrt_base_prior := l_snap_prior;
962 l_trend_mv_prior := REPLACE(l_agrt_base_prior,'fin_hier','f');
963
964 -- When Compare To is Prior Period
965
966 ELSIF fii_ea_util_pkg.g_time_comp = 'SEQUENTIAL' THEN
967 l_snap_prior := ',SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''R''
968 THEN f.actual_prior_'||l_xtd_column||'
969 ELSE NULL
970 END
971 ) FII_PL_PRIOR_REVENUE
972 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''CGS''
973 THEN f.actual_prior_'||l_xtd_column||'
974 ELSE NULL
975 END
976 ) FII_PL_PRIOR_COGS
977 ,NULL FII_PL_PRIOR_COGS_TOTAL_G
978 ,NULL FII_PL_PRIOR_REVENUE_TOTAL_G
979 ';
980 l_agrt_base_prior := ',SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE
981 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
982 AND fin_hier.top_node_fin_cat_type = ''R''
983 THEN f.actual_g
984 ELSE NULL
985 END
986 ) FII_PL_PRIOR_REVENUE
987 ,SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE
988 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
989 AND fin_hier.top_node_fin_cat_type = ''CGS''
990 THEN f.actual_g
991 ELSE NULL
992 END
993 ) FII_PL_PRIOR_COGS
994 ,SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_PERIOD_END
995 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
996 AND fin_hier.top_node_fin_cat_type = ''R''
997 THEN f.actual_g
998 ELSE NULL
999 END
1000 ) FII_PL_PRIOR_REVENUE_TOTAL_G
1001 ,SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_PERIOD_END
1002 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1003 AND fin_hier.top_node_fin_cat_type = ''CGS''
1004 THEN f.actual_g
1005 ELSE NULL
1006 END
1007 ) FII_PL_PRIOR_COGS_TOTAL_G
1008 ';
1009 l_trend_mv_prior := REPLACE(l_agrt_base_prior,'fin_hier','f');
1010
1011 -- When Period Type chosen is Year
1012
1013 ELSIF fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN
1014 l_snap_prior := ',SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''R''
1015 THEN f.actual_prior_'||l_xtd_column||'
1016 ELSE NULL
1017 END) FII_PL_PRIOR_REVENUE
1018 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''CGS''
1019 THEN f.actual_prior_'||l_xtd_column||'
1020 ELSE NULL
1021 END) FII_PL_PRIOR_COGS
1022 ,NULL FII_PL_PRIOR_COGS_TOTAL_G
1023 ,NULL FII_PL_PRIOR_REVENUE_TOTAL_G
1024 ';
1025 l_agrt_base_prior := ',SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE
1026 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1027 AND fin_hier.top_node_fin_cat_type = ''R''
1028 THEN f.actual_g
1029 ELSE NULL
1030 END
1031 ) FII_PL_PRIOR_REVENUE
1032 ,SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE
1033 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1034 AND fin_hier.top_node_fin_cat_type = ''CGS''
1035 THEN f.actual_g
1036 ELSE NULL
1037 END
1038 ) FII_PL_PRIOR_COGS
1039 ,SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_PERIOD_END
1040 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1041 AND fin_hier.top_node_fin_cat_type = ''R''
1042 THEN f.actual_g
1043 ELSE NULL
1044 END
1045 ) FII_PL_PRIOR_REVENUE_TOTAL_G
1046 ,SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_PERIOD_END
1047 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1048 AND fin_hier.top_node_fin_cat_type = ''CGS''
1049 THEN f.actual_g
1050 ELSE NULL
1051 END
1052 ) FII_PL_PRIOR_COGS_TOTAL_G
1053 ';
1054 l_trend_mv_prior := REPLACE(l_agrt_base_prior,'fin_hier','f');
1055
1056 ELSE
1057 l_snap_prior := ',SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''R''
1058 THEN f.actual_last_year_'||l_xtd_column||'
1059 ELSE NULL
1060 END
1061 ) FII_PL_PRIOR_REVENUE
1062 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''CGS''
1063 THEN f.actual_last_year_'||l_xtd_column||'
1064 ELSE NULL
1065 END
1066 ) FII_PL_PRIOR_COGS
1067 ,NULL FII_PL_PRIOR_COGS_TOTAL_G
1068 ,NULL FII_PL_PRIOR_REVENUE_TOTAL_G
1069 ';
1070 l_agrt_base_prior := ',SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE
1071 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1072 AND fin_hier.top_node_fin_cat_type = ''R''
1073 THEN f.actual_g
1074 ELSE NULL
1075 END
1076 ) FII_PL_PRIOR_REVENUE
1077 ,SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE
1078 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1079 AND fin_hier.top_node_fin_cat_type = ''CGS''
1080 THEN f.actual_g
1081 ELSE NULL
1082 END
1083 ) FII_PL_PRIOR_COGS
1084 ,SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_PERIOD_END
1085 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1086 AND fin_hier.top_node_fin_cat_type = ''R''
1087 THEN f.actual_g
1088 ELSE NULL
1089 END
1090 ) FII_PL_PRIOR_REVENUE_TOTAL_G
1091 ,SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_PERIOD_END
1092 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1093 AND fin_hier.top_node_fin_cat_type = ''CGS''
1094 THEN f.actual_g
1095 ELSE NULL
1096 END
1097 ) FII_PL_PRIOR_COGS_TOTAL_G
1098 ';
1099 l_trend_mv_prior := REPLACE(l_agrt_base_prior,'fin_hier','f');
1100
1101 END IF;
1102
1103 IF fii_ea_util_pkg.g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN
1104
1105 fii_ea_util_pkg.check_if_leaf(fii_ea_util_pkg.g_udd1_id);
1106 l_if_leaf_flag := fii_ea_util_pkg.g_ud1_is_leaf;
1107
1108 -- Following variables are used to check for loading of budgets against summary nodes,
1109 -- we don't need to append l_budget_snap_decode and l_budget_decode to the main sql, when we choose a leaf fud1 node.
1110
1111 IF l_if_leaf_flag = 'N' THEN
1112 l_fud1_decode := 'and fud1_hier.parent_value_id = DECODE(fud1_hier.parent_value_id, :UDD1_ID,
1113 fud1_hier.child_value_id, fud1_hier.parent_value_id)';
1114 l_budget_snap_decode := 'and f.fud1_id = DECODE( :G_ID, f.fud1_id,
1115 DECODE(budget_cur_'||l_roll_column||',0, -99999, f.fud1_id),f.fud1_id)';
1116 l_budget_decode := 'and f.fud1_id = DECODE(:G_ID, f.fud1_id,
1117 DECODE(f.time_id,:TIME_ID, f.fud1_id,-99999),f.fud1_id)';
1118 END IF;
1119 ELSE
1120 l_if_leaf_flag := 'Y';
1121
1122 END IF;
1123
1124 -- Checking if User Defined Dimension2 is enabled and forming FROM/WHERE clauses
1125
1126 SELECT dbi_enabled_flag
1127 INTO l_fud2_enabled_flag
1128 FROM fii_financial_dimensions
1129 WHERE dimension_short_name = 'FII_USER_DEFINED_2';
1130
1131 IF l_fud2_enabled_flag = 'Y' THEN
1132
1133 IF fii_ea_util_pkg.g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_2' THEN
1134
1135 l_fud2_from := ' fii_udd2_hierarchies fud2_hier, ';
1136
1137 l_fud2_snap_where := ' and fud2_hier.parent_value_id = gt.fud2_id
1138 and fud2_hier.child_value_id = f.fud2_id ';
1139
1140 l_fud2_where := ' and fud2_hier.parent_value_id = inner_inline_view.fud2_id
1141 and fud2_hier.child_value_id = f.fud2_id ';
1142
1143 ELSIF fii_ea_util_pkg.g_fud2_id <> 'All' THEN
1144
1145 l_fud2_from := ' fii_udd2_hierarchies fud2_hier, ';
1146
1147 l_fud2_snap_where := ' and fud2_hier.parent_value_id = gt.fud2_id
1148 and fud2_hier.child_value_id = f.fud2_id ';
1149
1150 l_fud2_where := ' and fud2_hier.parent_value_id = inner_inline_view.fud2_id
1151 and fud2_hier.child_value_id = f.fud2_id ';
1152 END IF;
1153
1154 END IF;
1155
1156 -- Drill on ViewBy Column
1157 l_viewby_drill_url := 'pFunctionName=FII_PL_GROSS_MARGIN_SUMM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
1158
1159 -- l_sqlstmt1 is the sql to be used, when report_date <> sysdate and fii_pmv_aggrt_gt has been populated
1160
1161 l_sqlstmt1 :=
1162
1163 ' /* this query returns data for aggregated nodes */
1164 SELECT /*+ index(f fii_gl_agrt_sum_mv_n1) */
1165 '||p_aggrt_viewby_id||' viewby_id
1166 ,inner_inline_view.viewby viewby
1167 ,inner_inline_view.sort_order sort_order
1168 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1169 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1170 AND fin_hier.top_node_fin_cat_type = ''R''
1171 THEN f.actual_g
1172 ELSE NULL
1173 END
1174 ) FII_PL_CURR_REVENUE
1175 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1176 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1177 AND fin_hier.top_node_fin_cat_type = ''CGS''
1178 THEN f.actual_g
1179 ELSE NULL
1180 END
1181 ) FII_PL_CURR_COGS
1182 '||l_agrt_base_prior||'
1183 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1184 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
1185 AND fin_hier.top_node_fin_cat_type = ''R''
1186 THEN f.budget_g
1187 ELSE NULL
1188 END
1189 ) FII_PL_REV_BUDGET
1190 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1191 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
1192 AND fin_hier.top_node_fin_cat_type = ''CGS''
1193 THEN f.budget_g
1194 ELSE NULL
1195 END
1196 ) FII_PL_COGS_BUDGET
1197 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1198 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
1199 AND fin_hier.top_node_fin_cat_type = ''R''
1200 THEN f.forecast_g
1201 ELSE NULL
1202 END
1203 ) FII_PL_REV_FORECAST
1204 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1205 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
1206 AND fin_hier.top_node_fin_cat_type = ''CGS''
1207 THEN f.forecast_g
1208 ELSE NULL
1209 END
1210 ) FII_PL_COGS_FORECAST
1211 FROM fii_gl_agrt_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
1212 fii_fin_item_leaf_hiers fin_hier,
1213 '||l_fud2_from||'
1214 (SELECT /*+ NO_MERGE cardinality(gt 1) */ *
1215 FROM fii_time_structures cal,
1216 fii_pmv_aggrt_gt gt
1217 WHERE report_date IN ( :ASOF_DATE
1218 ,:PREVIOUS_ASOF_DATE
1219 ,:PRIOR_PERIOD_END
1220 )
1221 AND ( BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND OR
1222 BITAND(cal.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND OR
1223 BITAND(cal.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND
1224 )
1225 ) inner_inline_view
1226 WHERE f.time_id = inner_inline_view.time_id
1227 AND f.period_type_id = inner_inline_view.period_type_id
1228 AND f.parent_company_id = inner_inline_view.parent_company_id
1229 AND f.company_id = inner_inline_view.company_id
1230 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
1231 AND f.cost_center_id = inner_inline_view.cc_id
1232 '||l_budget_decode||'
1233 AND f.parent_fud1_id = inner_inline_view.parent_fud1_id
1234 AND f.fud1_id = inner_inline_view.fud1_id
1235 '||l_fud2_where||'
1236 AND fin_hier.top_node_fin_cat_type IN (''R'', ''CGS'')
1237 AND fin_hier.next_level_fin_cat_id = f.fin_category_id
1238 AND fin_hier.next_level_fin_cat_id = fin_hier.child_fin_cat_id
1239 GROUP BY '||p_aggrt_viewby_id||',
1240 inner_inline_view.viewby,
1241 inner_inline_view.sort_order';
1242
1243 -- l_sqlstmt2 is the sql to be used, when report_date <> sysdate and fii_pmv_non_aggrt_gt has been populated
1244
1245 l_sqlstmt2 :=
1246
1247 ' /* this query returns data for non_aggregated nodes */
1248 SELECT /*+ index(f fii_gl_base_map_mv_n1) */
1249 '||p_nonaggrt_viewby_id||' viewby_id
1250 ,inner_inline_view.viewby viewby
1251 ,inner_inline_view.sort_order sort_order
1252 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1253 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1254 AND fin_hier.top_node_fin_cat_type = ''R''
1255 THEN f.actual_g
1256 ELSE NULL
1257 END
1258 ) FII_PL_CURR_REVENUE
1259 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1260 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1261 AND fin_hier.top_node_fin_cat_type = ''CGS''
1262 THEN f.actual_g
1263 ELSE NULL
1264 END
1265 ) FII_PL_CURR_COGS
1266 '||l_agrt_base_prior||'
1267 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1268 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
1269 AND fin_hier.top_node_fin_cat_type = ''R''
1270 THEN f.budget_g
1271 ELSE NULL
1272 END
1273 ) FII_PL_REV_BUDGET
1274 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1275 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
1276 AND fin_hier.top_node_fin_cat_type = ''CGS''
1277 THEN f.budget_g
1278 ELSE NULL
1279 END
1280 ) FII_PL_COGS_BUDGET
1281 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1282 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
1283 AND fin_hier.top_node_fin_cat_type = ''R''
1284 THEN f.forecast_g
1285 ELSE NULL
1286 END
1287 ) FII_PL_REV_FORECAST
1288 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1289 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
1290 AND fin_hier.top_node_fin_cat_type = ''CGS''
1291 THEN f.forecast_g
1292 ELSE NULL
1293 END
1294 ) FII_PL_COGS_FORECAST
1295 FROM fii_gl_base_map_mv'||fii_ea_util_pkg.g_curr_view||' f,
1296 fii_company_hierarchies co_hier,
1297 fii_cost_ctr_hierarchies cc_hier,
1298 fii_fin_item_leaf_hiers fin_hier,
1299 fii_udd1_hierarchies fud1_hier,
1300 '||l_fud2_from||'
1301 ( SELECT /*+ NO_MERGE cardinality(gt 1) */ *
1302 FROM fii_time_structures cal,
1303 fii_pmv_non_aggrt_gt gt
1304 WHERE report_date IN ( :ASOF_DATE
1305 ,:PREVIOUS_ASOF_DATE
1306 ,:PRIOR_PERIOD_END
1307 )
1308 AND ( BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND OR
1309 BITAND(cal.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND OR
1310 BITAND(cal.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND
1311 )
1312 ) inner_inline_view
1313 WHERE f.period_type_id = inner_inline_view.period_type_id
1314 AND f.time_id = inner_inline_view.time_id
1315 AND co_hier.parent_company_id = inner_inline_view.company_id
1316 AND co_hier.child_company_id = f.company_id
1317 AND cc_hier.parent_cc_id = inner_inline_view.cost_center_id
1318 AND cc_hier.child_cc_id = f.cost_center_id
1319 '||l_budget_decode||'
1320 AND fin_hier.child_fin_cat_id = f.fin_category_id
1321 AND fin_hier.top_node_fin_cat_type IN (''R'', ''CGS'')
1322 AND fud1_hier.parent_value_id = inner_inline_view.fud1_id
1323 '||l_fud1_decode||'
1324 AND fud1_hier.child_value_id = f.fud1_id
1325 '||l_fud2_where||'
1326 GROUP BY '||p_nonaggrt_viewby_id||',
1327 inner_inline_view.viewby,
1328 inner_inline_view.sort_order';
1329
1330 l_snap_sqlstmt1 := ' -- Hitting fii_gl_snap_sum_f
1331
1332 SELECT viewby_id
1333 ,viewby
1334 ,sort_order
1335 ,SUM(FII_PL_CURR_REVENUE) FII_PL_CURR_REVENUE
1336 ,SUM(FII_PL_CURR_COGS) FII_PL_CURR_COGS
1337 ,SUM(FII_PL_PRIOR_REVENUE) FII_PL_PRIOR_REVENUE
1338 ,SUM(FII_PL_PRIOR_COGS) FII_PL_PRIOR_COGS
1339 ,SUM(FII_PL_REV_BUDGET) FII_PL_REV_BUDGET
1340 ,SUM(FII_PL_COGS_BUDGET) FII_PL_COGS_BUDGET
1341 ,SUM(FII_PL_REV_FORECAST) FII_PL_REV_FORECAST
1342 ,SUM(FII_PL_COGS_FORECAST) FII_PL_COGS_FORECAST
1343 ,SUM(FII_PL_PRIOR_COGS_TOTAL_G) FII_PL_PRIOR_COGS_TOTAL_G
1344 ,SUM(FII_PL_PRIOR_REVENUE_TOTAL_G) FII_PL_PRIOR_REVENUE_TOTAL_G
1345 FROM
1346 (SELECT /*+ index(f fii_gl_snap_sum_f_n1) */
1347 '||p_snap_aggrt_viewby_id||' viewby_id
1348 ,gt.viewby viewby
1349 ,gt.sort_order sort_order
1350 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''R''
1351 THEN f.actual_cur_'||l_xtd_column||'
1352 ELSE NULL
1353 END
1354 ) FII_PL_CURR_REVENUE
1355 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''CGS''
1356 THEN f.actual_cur_'||l_xtd_column||'
1357 ELSE NULL
1358 END
1359 ) FII_PL_CURR_COGS
1360 '||l_snap_prior||'
1361 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''R''
1362 THEN f.budget_cur_'||l_xtd_column||'
1363 ELSE NULL
1364 END
1365 ) FII_PL_REV_BUDGET
1366 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''CGS''
1367 THEN f.budget_cur_'||l_xtd_column||'
1368 ELSE NULL
1369 END
1370 ) FII_PL_COGS_BUDGET
1371 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''R''
1372 THEN f.forecast_cur_'||l_xtd_column||'
1373 ELSE NULL
1374 END
1375 ) FII_PL_REV_FORECAST
1376 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''CGS''
1377 THEN f.forecast_cur_'||l_xtd_column||'
1378 ELSE NULL
1379 END
1380 ) FII_PL_COGS_FORECAST
1381 FROM fii_gl_snap_sum_f'||fii_ea_util_pkg.g_curr_view||' f,
1382 fii_fin_item_leaf_hiers fin_hier,
1383 '||l_fud2_from||'
1384 fii_pmv_aggrt_gt gt
1385 WHERE f.parent_company_id = gt.parent_company_id
1386 and f.fin_category_id = fin_hier.child_fin_cat_id
1387 and fin_hier.top_node_fin_cat_type IN (''R'', ''CGS'')
1388 and f.company_id = gt.company_id
1389 and f.parent_cost_center_id = gt.parent_cc_id
1390 and f.cost_center_id =gt.cc_id
1391 '||l_budget_snap_decode||'
1392 and f.parent_fud1_id = gt.parent_fud1_id
1393 and f.fud1_id =gt.fud1_id
1394 '||l_fud2_snap_where||'
1395 GROUP BY '||p_snap_aggrt_viewby_id||', gt.viewby, gt.sort_order
1396
1397 UNION ALL
1398 /* Following Query calculates PRIOR TOTAL INCOME */
1399 SELECT /*+ index(f fii_gl_agrt_sum_mv_n1) */
1400 '||p_aggrt_viewby_id||' viewby_id
1401 ,inner_inline_view.viewby viewby
1402 ,inner_inline_view.sort_order sort_order
1403 ,NULL FII_PL_CURR_REVENUE
1404 ,NULL FII_PL_CURR_COGS
1405 ,NULL FII_PL_PRIOR_REVENUE
1406 ,NULL FII_PL_PRIOR_COGS
1407 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''CGS''
1408 THEN f.actual_g
1409 ELSE NULL
1410 END
1411 ) FII_PL_PRIOR_COGS_TOTAL_G
1412 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''R''
1413 THEN f.actual_g
1414 ELSE NULL
1415 END
1416 ) FII_PL_PRIOR_REVENUE_TOTAL_G
1417 ,NULL FII_PL_REV_BUDGET
1418 ,NULL FII_PL_COGS_BUDGET
1419 ,NULL FII_PL_REV_FORECAST
1420 ,NULL FII_PL_COGS_FORECAST
1421 FROM fii_gl_agrt_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
1422 fii_fin_item_leaf_hiers fin_hier,
1423 '||l_fud2_from||'
1424 ( SELECT /*+ NO_MERGE cardinality(gt 1) */ *
1425 FROM fii_time_structures cal,
1426 fii_pmv_aggrt_gt gt
1427 WHERE report_date = :PRIOR_PERIOD_END
1428 AND BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND
1429 ) inner_inline_view
1430 WHERE f.time_id = inner_inline_view.time_id
1431 AND f.period_type_id = inner_inline_view.period_type_id
1432 AND f.parent_company_id = inner_inline_view.parent_company_id
1433 AND f.company_id = inner_inline_view.company_id
1434 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
1435 AND f.cost_center_id = inner_inline_view.cc_id
1436 '||l_budget_decode||'
1437 AND f.parent_fud1_id = inner_inline_view.parent_fud1_id
1438 AND f.fud1_id = inner_inline_view.fud1_id
1439 '||l_fud2_where||'
1440 AND fin_hier.top_node_fin_cat_type IN (''R'', ''CGS'')
1441 AND fin_hier.next_level_fin_cat_id = f.fin_category_id
1442 AND fin_hier.next_level_fin_cat_id = fin_hier.child_fin_cat_id
1443 GROUP BY '||p_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order
1444 ) GROUP BY viewby_id, viewby, sort_order';
1445
1446 l_snap_sqlstmt2 :=
1447
1448 ' SELECT /*+ index(f fii_gl_base_map_mv_n1) */
1449 '||p_nonaggrt_viewby_id||' viewby_id
1450 ,inner_inline_view.viewby viewby
1451 ,inner_inline_view.sort_order sort_order
1452 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1453 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1454 AND fin_hier.top_node_fin_cat_type = ''R''
1455 THEN f.actual_g
1456 ELSE NULL
1457 END
1458 ) FII_PL_CURR_REVENUE
1459 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1460 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1461 AND fin_hier.top_node_fin_cat_type = ''CGS''
1462 THEN f.actual_g
1463 ELSE NULL
1464 END
1465 ) FII_PL_CURR_COGS
1466 '||l_agrt_base_prior||'
1467 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1468 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
1469 AND fin_hier.top_node_fin_cat_type = ''R''
1470 THEN f.budget_g
1471 ELSE NULL
1472 END
1473 ) FII_PL_REV_BUDGET
1474 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1475 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
1476 AND fin_hier.top_node_fin_cat_type = ''CGS''
1477 THEN f.budget_g
1478 ELSE NULL
1479 END
1480 ) FII_PL_COGS_BUDGET
1481 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1482 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
1483 AND fin_hier.top_node_fin_cat_type = ''R''
1484 THEN f.forecast_g
1485 ELSE NULL
1486 END
1487 ) FII_PL_REV_FORECAST
1488 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1489 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
1490 AND fin_hier.top_node_fin_cat_type = ''CGS''
1491 THEN f.forecast_g
1492 ELSE NULL
1493 END
1494 ) FII_PL_COGS_FORECAST
1495 FROM fii_gl_base_map_mv'||fii_ea_util_pkg.g_curr_view||' f,
1496 fii_company_hierarchies co_hier,
1497 fii_cost_ctr_hierarchies cc_hier,
1498 fii_fin_item_leaf_hiers fin_hier,
1499 fii_udd1_hierarchies fud1_hier,
1500 '||l_fud2_from||'
1501 ( SELECT /*+ NO_MERGE cardinality(gt 1) */ *
1502 FROM fii_time_structures cal,
1503 fii_pmv_non_aggrt_gt gt
1504 WHERE report_date IN ( :ASOF_DATE
1505 ,:PREVIOUS_ASOF_DATE
1506 ,:PRIOR_PERIOD_END
1507 )
1508 AND ( BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND OR
1509 BITAND(cal.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND OR
1510 BITAND(cal.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND
1511 )
1512 ) inner_inline_view
1513 WHERE f.period_type_id = inner_inline_view.period_type_id
1514 and f.time_id = inner_inline_view.time_id
1515 and co_hier.parent_company_id = inner_inline_view.company_id
1516 and co_hier.child_company_id = f.company_id
1517 and cc_hier.parent_cc_id = inner_inline_view.cost_center_id
1518 and cc_hier.child_cc_id = f.cost_center_id
1519 and fin_hier.child_fin_cat_id = f.fin_category_id
1520 and fin_hier.top_node_fin_cat_type IN (''R'', ''CGS'')
1521 and fud1_hier.parent_value_id = inner_inline_view.fud1_id
1522 '||l_fud1_decode||'
1523 '||l_budget_decode||'
1524 and fud1_hier.child_value_id = f.fud1_id
1525 '||l_fud2_where||'
1526 GROUP BY '||p_nonaggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order';
1527
1528 -- When fii_gl_trend_sum_mv is hit
1529
1530 l_trend_sum_mv_sql :='
1531 SELECT '||p_aggrt_viewby_id||' viewby_id
1532 ,inner_inline_view.viewby viewby
1533 ,inner_inline_view.sort_order sort_order
1534 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1535 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1536 AND f.top_node_fin_cat_type = ''R''
1537 THEN f.actual_g
1538 ELSE NULL
1539 END
1540 ) FII_PL_CURR_REVENUE
1541 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1542 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1543 AND f.top_node_fin_cat_type = ''CGS''
1544 THEN f.actual_g
1545 ELSE NULL
1546 END
1547 ) FII_PL_CURR_COGS
1548 '||l_trend_mv_prior||'
1549 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1550 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
1551 AND f.top_node_fin_cat_type = ''R''
1552 THEN f.budget_g
1553 ELSE NULL
1554 END
1555 ) FII_PL_REV_BUDGET
1556 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1557 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
1558 AND f.top_node_fin_cat_type = ''CGS''
1559 THEN f.budget_g
1560 ELSE NULL
1561 END
1562 ) FII_PL_COGS_BUDGET
1563 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1564 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
1565 AND f.top_node_fin_cat_type = ''R''
1566 THEN f.forecast_g
1567 ELSE NULL
1568 END
1569 ) FII_PL_REV_FORECAST
1570 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1571 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
1572 AND f.top_node_fin_cat_type = ''CGS''
1573 THEN f.forecast_g
1574 ELSE NULL
1575 END
1576 ) FII_PL_COGS_FORECAST
1577 FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
1578 ( SELECT /*+ NO_MERGE cardinality(gt 1) */ *
1579 FROM fii_time_structures cal,
1580 fii_pmv_aggrt_gt gt
1581 WHERE report_date IN ( :ASOF_DATE
1582 ,:PREVIOUS_ASOF_DATE
1583 ,:PRIOR_PERIOD_END
1584 )
1585 AND ( BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND OR
1586 BITAND(cal.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND OR
1587 BITAND(cal.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND
1588 )
1589 ) inner_inline_view
1590
1591 WHERE f.time_id = inner_inline_view.time_id
1592 AND f.period_type_id = inner_inline_view.period_type_id
1593 AND f.parent_company_id = inner_inline_view.parent_company_id
1594 AND f.company_id = inner_inline_view.company_id
1595 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
1596 AND f.cost_center_id = inner_inline_view.cc_id
1597 AND f.top_node_fin_cat_type IN (''R'',''CGS'')
1598 '||l_budget_decode||'
1599
1600 GROUP BY '||p_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order';
1601
1602 -- SQL for Gross Margin table portlet
1603 -- Here, we don't calculate any PRIOR columns
1604 -- Even, Budget is not calculated
1605
1606 l_trend_sum_mv_sql_port :='
1607 SELECT '||p_aggrt_viewby_id||' viewby_id
1608 ,inner_inline_view.viewby viewby
1609 ,inner_inline_view.sort_order sort_order
1610 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1611 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1612 AND f.top_node_fin_cat_type = ''R''
1613 THEN f.actual_g
1614 ELSE NULL
1615 END
1616 ) FII_PL_CURR_REVENUE
1617 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1618 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
1619 AND f.top_node_fin_cat_type = ''CGS''
1620 THEN f.actual_g
1621 ELSE NULL
1622 END
1623 ) FII_PL_CURR_COGS
1624 ,NULL FII_PL_PRIOR_REVENUE
1625 ,NULL FII_PL_PRIOR_COGS
1626 ,NULL FII_PL_REV_BUDGET
1627 ,NULL FII_PL_COGS_BUDGET
1628 ,NULL FII_PL_PRIOR_REVENUE_TOTAL_G
1629 ,NULL FII_PL_PRIOR_COGS_TOTAL_G
1630 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1631 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
1632 AND f.top_node_fin_cat_type = ''R''
1633 THEN f.forecast_g
1634 ELSE NULL
1635 END
1636 ) FII_PL_REV_FORECAST
1637 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
1638 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
1639 AND f.top_node_fin_cat_type = ''CGS''
1640 THEN f.forecast_g
1641 ELSE NULL
1642 END
1643 ) FII_PL_COGS_FORECAST
1644 FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
1645 ( SELECT /*+ NO_MERGE cardinality(gt 1) */ *
1646 FROM fii_time_structures cal,
1647 fii_pmv_aggrt_gt gt
1648 WHERE report_date IN ( :ASOF_DATE
1649 ,:PREVIOUS_ASOF_DATE
1650 ,:PRIOR_PERIOD_END
1651 )
1652 AND ( BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND OR
1653 BITAND(cal.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND OR
1654 BITAND(cal.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND
1655 )
1656 ) inner_inline_view
1657
1658 WHERE f.time_id = inner_inline_view.time_id
1659 AND f.period_type_id = inner_inline_view.period_type_id
1660 AND f.parent_company_id = inner_inline_view.parent_company_id
1661 AND f.company_id = inner_inline_view.company_id
1662 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
1663 AND f.cost_center_id = inner_inline_view.cc_id
1664 AND f.top_node_fin_cat_type IN (''R'',''CGS'')
1665 '||l_budget_decode||'
1666
1667 GROUP BY '||p_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order';
1668
1669 -- Deciding upon SQL, based on FF name
1670 IF l_function_name = 'FII_PL_GROSS_MARGIN_TABLE' THEN
1671 l_trend_sum_mv_sql := l_trend_sum_mv_sql_port;
1672 END IF;
1673
1674 -- Checking conditions to decide upon the SQL variable
1675
1676 IF fii_ea_util_pkg.g_if_trend_sum_mv = 'Y' THEN
1677
1678 l_aggrt_sql := l_trend_sum_mv_sql;
1679
1680 ELSIF fii_ea_util_pkg.g_snapshot = 'Y' THEN
1681
1682 IF p_aggrt_gt_is_empty = 'N' then -- aggrt GT table is populated
1683
1684 l_aggrt_sql := l_snap_sqlstmt1;
1685
1686 IF p_non_aggrt_gt_is_empty = 'N' then -- both GT tables are populated
1687 l_non_aggrt_sql := l_snap_sqlstmt2;
1688 l_union_all := 'UNION ALL';
1689 END IF;
1690
1691 ELSIF p_non_aggrt_gt_is_empty = 'N' then -- only non aggrt GT table is populated
1692
1693 l_non_aggrt_sql := l_snap_sqlstmt2;
1694
1695 ELSE -- neither of the GT tables are populated...
1696
1697 l_aggrt_sql := l_snap_sqlstmt1;
1698
1699 END IF;
1700 ELSE
1701 IF p_aggrt_gt_is_empty = 'N' then -- aggrt GT table is populated
1702
1703 l_aggrt_sql := l_sqlstmt1;
1704
1705 IF p_non_aggrt_gt_is_empty = 'N' then -- both GT tables are populated
1706 l_non_aggrt_sql := l_sqlstmt2;
1707 l_union_all := 'UNION ALL';
1708 END IF;
1709
1710 ELSIF p_non_aggrt_gt_is_empty = 'N' then -- only non aggrt GT table is populated
1711
1712 l_non_aggrt_sql := l_sqlstmt2;
1713
1714 ELSE -- neither of the GT tables are populated...
1715
1716 l_aggrt_sql := l_sqlstmt1;
1717
1718 END IF;
1719
1720 END IF;
1721
1722 l_sqlstmt :=
1723
1724 'SELECT
1725 DECODE(:G_ID, inline_view.viewby_id,DECODE('''||l_if_leaf_flag||''',''Y'',inline_view.viewby
1726 ,inline_view.viewby||'' ''||:DIR_MSG), inline_view.viewby)
1727 VIEWBY
1728 ,inline_view.viewby_id VIEWBYID
1729 ,CASE WHEN FII_PL_CURR_REVENUE IS NULL AND FII_PL_CURR_COGS IS NULL
1730 THEN NULL
1731 ELSE
1732 (NVL(FII_PL_CURR_REVENUE,0) - NVL(FII_PL_CURR_COGS,0))*100
1733 /NULLIF(ABS(FII_PL_CURR_REVENUE),0)
1734 END FII_PL_GROSS_MARGIN_PERCENT
1735 ,CASE WHEN FII_PL_PRIOR_REVENUE IS NULL AND FII_PL_PRIOR_COGS IS NULL
1736 THEN NULL
1737 ELSE
1738 (NVL(FII_PL_PRIOR_REVENUE,0) - NVL(FII_PL_PRIOR_COGS,0))*100
1739 /NULLIF(ABS(FII_PL_PRIOR_REVENUE),0)
1740 END FII_PL_PRIOR_GROSS_MGN_PCNT
1741 ,CASE WHEN FII_PL_CURR_REVENUE IS NULL AND FII_PL_CURR_COGS IS NULL
1742 AND FII_PL_PRIOR_REVENUE IS NULL AND FII_PL_PRIOR_COGS IS NULL
1743 THEN NULL
1744 ELSE
1745 (NVL(FII_PL_CURR_REVENUE,0) - NVL(FII_PL_CURR_COGS,0))*100
1746 /NULLIF(ABS(FII_PL_CURR_REVENUE),0) -
1747 (NVL(FII_PL_PRIOR_REVENUE,0) - NVL(FII_PL_PRIOR_COGS,0))*100
1748 /NULLIF(ABS(FII_PL_PRIOR_REVENUE),0)
1749 END FII_PL_GROSS_MGN_CHANGE
1750 ,CASE WHEN FII_PL_PRIOR_REVENUE_TOTAL_G IS NULL AND FII_PL_PRIOR_COGS_TOTAL_G IS NULL
1751 THEN NULL
1752 ELSE
1753 NVL(FII_PL_PRIOR_REVENUE_TOTAL_G,0) - NVL(FII_PL_PRIOR_COGS_TOTAL_G,0)
1754 END FII_PL_PRIOR_GROSS_INC_TOTAL
1755 ,CASE WHEN FII_PL_CURR_REVENUE IS NULL AND FII_PL_CURR_COGS IS NULL
1756 THEN NULL
1757 ELSE NVL(FII_PL_CURR_REVENUE,0) - NVL(FII_PL_CURR_COGS,0)
1758 END FII_PL_GROSS_INCOME_XTD
1759 ,NULL FII_PL_GROSS_INCOME_TOTAL
1760 ,CASE WHEN FII_PL_PRIOR_REVENUE IS NULL AND FII_PL_PRIOR_COGS IS NULL
1761 THEN NULL
1762 ELSE NVL(FII_PL_PRIOR_REVENUE,0) - NVL(FII_PL_PRIOR_COGS,0)
1763 END FII_PL_PRIOR_GROSS_INCOME_XTD
1764 ,CASE WHEN FII_PL_CURR_REVENUE IS NULL AND FII_PL_CURR_COGS IS NULL
1765 AND FII_PL_PRIOR_REVENUE IS NULL AND FII_PL_PRIOR_COGS IS NULL
1766 THEN NULL
1767 ELSE
1768 ((NVL(FII_PL_CURR_REVENUE,0) - NVL(FII_PL_CURR_COGS,0)) -
1769 (NVL(FII_PL_PRIOR_REVENUE,0) - NVL(FII_PL_PRIOR_COGS,0))) *100
1770 /NULLIF(ABS((NVL(FII_PL_PRIOR_REVENUE,0) - NVL(FII_PL_PRIOR_COGS,0))),0)
1771 END FII_PL_GROSS_INCOME_CHANGE
1772 ,CASE WHEN FII_PL_REV_BUDGET IS NULL AND FII_PL_COGS_BUDGET IS NULL
1773 THEN NULL
1774 ELSE
1775 NVL(FII_PL_REV_BUDGET,0) - NVL(FII_PL_COGS_BUDGET,0)
1776 END FII_PL_BUDGET
1777 ,CASE WHEN FII_PL_CURR_REVENUE IS NULL AND FII_PL_CURR_COGS IS NULL
1778 THEN NULL
1779 ELSE (NVL(FII_PL_CURR_REVENUE,0) - NVL(FII_PL_CURR_COGS,0))*100
1780 /NULLIF(ABS(NVL(FII_PL_REV_BUDGET,0) - NVL(FII_PL_COGS_BUDGET,0)),0)
1781 END FII_PL_PCNT_BUDGET
1782 ,CASE WHEN FII_PL_REV_FORECAST IS NULL AND FII_PL_COGS_FORECAST IS NULL
1783 THEN NULL
1784 ELSE NVL(FII_PL_REV_FORECAST,0) - NVL(FII_PL_COGS_FORECAST,0)
1785 END FII_PL_FORECAST
1786 ,CASE WHEN FII_PL_CURR_REVENUE IS NULL AND FII_PL_CURR_COGS IS NULL
1787 THEN NULL
1788 ELSE (NVL(FII_PL_CURR_REVENUE,0) - NVL(FII_PL_CURR_COGS,0))*100
1789 /NULLIF(ABS(NVL(FII_PL_REV_FORECAST,0) - NVL(FII_PL_COGS_FORECAST,0)),0)
1790 END FII_PL_PCNT_FORECAST
1791 ,CASE WHEN FII_PL_CURR_REVENUE IS NULL AND FII_PL_CURR_COGS IS NULL
1792 THEN NULL
1793 ELSE (SUM(NVL(FII_PL_CURR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_CURR_COGS,0)) OVER())*100
1794 /NULLIF(ABS(SUM(FII_PL_CURR_REVENUE) OVER ()),0)
1795 END FII_PL_GT_GROSS_MARGIN_PERCENT
1796 ,CASE WHEN FII_PL_PRIOR_REVENUE IS NULL AND FII_PL_PRIOR_COGS IS NULL
1797 THEN NULL
1798 ELSE (SUM(NVL(FII_PL_PRIOR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_PRIOR_COGS,0)) OVER())*100
1799 /NULLIF(ABS(SUM(FII_PL_PRIOR_REVENUE) OVER ()),0)
1800 END FII_PL_GT_PRIOR_GROSS_MGN_PCNT
1801 ,CASE WHEN FII_PL_CURR_REVENUE IS NULL AND FII_PL_CURR_COGS IS NULL
1802 AND FII_PL_PRIOR_REVENUE IS NULL AND FII_PL_PRIOR_COGS IS NULL
1803 THEN NULL
1804 ELSE (SUM(NVL(FII_PL_CURR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_CURR_COGS,0)) OVER())*100
1805 /NULLIF(ABS(SUM(FII_PL_CURR_REVENUE) OVER ()),0) -
1806 (SUM(NVL(FII_PL_PRIOR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_PRIOR_COGS,0)) OVER())*100
1807 /NULLIF(ABS(SUM(FII_PL_PRIOR_REVENUE) OVER ()),0)
1808 END FII_PL_GT_GROSS_MGN_CHANGE
1809 ,CASE WHEN FII_PL_CURR_REVENUE IS NULL AND FII_PL_CURR_COGS IS NULL
1810 AND FII_PL_PRIOR_REVENUE IS NULL AND FII_PL_PRIOR_COGS IS NULL
1811 THEN NULL
1812 ELSE ((SUM(NVL(FII_PL_CURR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_CURR_COGS,0)) OVER ()) -
1813 (SUM(NVL(FII_PL_PRIOR_REVENUE,0)) OVER ()- SUM(NVL(FII_PL_PRIOR_COGS,0)) OVER ()))*100
1814 /NULLIF(ABS((SUM(NVL(FII_PL_PRIOR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_PRIOR_COGS,0)) OVER () )),0)
1815 END FII_PL_GT_GROSS_INCOME_CHANGE
1816 ,CASE WHEN FII_PL_CURR_REVENUE IS NULL AND FII_PL_CURR_COGS IS NULL
1817 THEN NULL
1818 ELSE (SUM(NVL(FII_PL_CURR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_CURR_COGS,0)) OVER ())*100
1819 /NULLIF(ABS(SUM(NVL(FII_PL_REV_BUDGET,0)) OVER () - SUM(NVL(FII_PL_COGS_BUDGET,0)) OVER ()),0)
1820 END FII_PL_GT_PCNT_BUDGET
1821 ,CASE WHEN FII_PL_CURR_REVENUE IS NULL AND FII_PL_CURR_COGS IS NULL
1822 THEN NULL
1823 ELSE (SUM(NVL(FII_PL_CURR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_CURR_COGS,0)) OVER ())*100
1824 /NULLIF(ABS(SUM(NVL(FII_PL_REV_FORECAST,0)) OVER () - SUM(NVL(FII_PL_COGS_FORECAST,0)) OVER ()),0)
1825 END FII_PL_GT_PCNT_FORECAST
1826 ,DECODE((SELECT is_leaf_flag
1827 FROM fii_company_hierarchies
1828 WHERE parent_company_id = inline_view.viewby_id
1829 AND child_company_id = inline_view.viewby_id),
1830 ''Y'',
1831 '''',
1832 '''||l_viewby_drill_url||''') FII_PL_COMP_DRILL
1833 ,DECODE((SELECT is_leaf_flag
1834 FROM fii_cost_ctr_hierarchies
1835 WHERE parent_cc_id = inline_view.viewby_id
1836 AND child_cc_id = inline_view.viewby_id),
1837 ''Y'',
1838 '''',
1839 '''||l_viewby_drill_url||''') FII_PL_CC_DRILL
1840 ,DECODE((SELECT is_leaf_flag
1841 FROM fii_udd1_hierarchies
1842 WHERE parent_value_id = inline_view.viewby_id
1843 AND child_value_id = inline_view.viewby_id),
1844 ''Y'',
1845 '''',
1846 DECODE(:G_ID, inline_view.viewby_id,'''',
1847 '''||l_viewby_drill_url||''')) FII_PL_UDD1_DRILL
1848 ,DECODE((SELECT is_leaf_flag
1849 FROM fii_udd2_hierarchies
1850 WHERE parent_value_id = inline_view.viewby_id
1851 AND child_value_id = inline_view.viewby_id),
1852 ''Y'',
1853 '''',
1854 '''||l_viewby_drill_url||''') FII_PL_UDD2_DRILL
1855 FROM ( '||l_aggrt_sql||'
1856 '||l_union_all||'
1857 '||l_non_aggrt_sql||'
1858 ) inline_view
1859 ORDER BY NVL(inline_view.sort_order,999999) ASC, NVL(FII_PL_GROSS_MARGIN_PERCENT,-999999999) DESC';
1860
1861 fii_ea_util_pkg.bind_variable(p_sqlstmt => l_sqlstmt,
1862 p_page_parameter_tbl => p_page_parameter_tbl,
1863 p_sql_output => p_gross_margin_sql,
1864 p_bind_output_table => p_gross_margin_output);
1865
1866 END get_gross_margin;
1867
1868 ---------------------------------------------------------------------------------
1869 -- Following procedure is used to form PMV SQL, which is used to retrieve data
1870 -- for Operating Margin Table portlet and Operating Margin Summary report
1871
1872 PROCEDURE get_oper_margin( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1873 ,p_oper_margin_sql OUT NOCOPY VARCHAR2
1874 ,p_oper_margin_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
1875 )
1876
1877 IS
1878 l_sqlstmt VARCHAR2(30000);
1879 p_aggrt_viewby_id VARCHAR2(30);
1880 p_snap_aggrt_viewby_id VARCHAR2(30);
1881 p_nonaggrt_viewby_id VARCHAR2(50);
1882 p_aggrt_gt_is_empty VARCHAR2(1);
1883 p_non_aggrt_gt_is_empty VARCHAR2(1);
1884 l_union_all VARCHAR2(10);
1885 l_xtd_column VARCHAR2(10); -- At the time of hitting snapshot tables, l_xtd_xolumn is used
1886 -- based on period type chosen i.e if column used to display xtd data
1887 -- should be actual_curr_mtd/qtd/ytd
1888 l_roll_column VARCHAR2(10);
1889 l_aggrt_sql VARCHAR2(20000) := NULL;
1890 l_sqlstmt1 VARCHAR2(20000) := NULL;
1891 l_snap_sqlstmt1 VARCHAR2(20000) := NULL;
1892 l_non_aggrt_sql VARCHAR2(20000) := NULL;
1893 l_sqlstmt2 VARCHAR2(20000) := NULL;
1894 l_snap_sqlstmt2 VARCHAR2(20000) := NULL;
1895 l_trend_sum_mv_sql VARCHAR2(15000) := NULL;
1896 l_trend_sum_mv_sql_port VARCHAR2(15000) := NULL;
1897 l_viewby_drill_url VARCHAR2(300);
1898 l_snap_prior VARCHAR2(10000);
1899 l_trend_mv_prior VARCHAR2(10000);
1900 l_agrt_base_prior VARCHAR2(10000);
1901 l_if_leaf_flag VARCHAR2(1); -- local var to denote, if category or fud1 param chosen to run the report is a leaf or not..
1902 l_fud2_enabled_flag VARCHAR2(1);
1903 l_fud2_where VARCHAR2(300);
1904 l_fud2_snap_where VARCHAR2(300);
1905 l_fud2_from VARCHAR2(100);
1906 l_fud1_decode VARCHAR2(300); -- local variable to append decode check for fud1, when viewby chosen is FUD1
1907 l_budget_decode VARCHAR2(300); -- Since we can load budget only against category and fud1 summary nodes,
1908 -- this local variable appends a check to agrt MV and base map MV queries, so that budget is checked only for xTD period.
1909 -- Budget loaded for prior xTD should not result in any unwanted record, having 0/NA in all columns..
1910 l_budget_snap_decode VARCHAR2(300); -- local variable analogous to l_budget_decode..it appends a similar check to snapshot query
1911 l_function_name VARCHAR2(100);
1912
1913 BEGIN
1914
1915 -- Initialization. Calling fii_ea_util_pkg APIs necessary for constructing
1916 -- the PMV sql
1917
1918 fii_ea_util_pkg.reset_globals;
1919
1920 -- Reassigning following variable to NULL, since it is assigned to OE in reset_globals procedure
1921
1922 fii_ea_util_pkg.g_fin_cat_type := NULL;
1923
1924 -- Call to get_parameters procedure
1925 fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);
1926
1927 -- Following variable would store the FormFunction name.
1928 -- Based on this, PMV SQL would be constructed for Operating Margin table portlet OR Operating Margin Summary report
1929
1930 IF (p_page_parameter_tbl.count > 0) THEN
1931 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
1932
1933 IF (p_page_parameter_tbl(i).parameter_name = 'BIS_FXN_NAME') THEN
1934 l_function_name := p_page_parameter_tbl(i).parameter_value;
1935 END IF;
1936
1937 END LOOP;
1938 END IF;
1939
1940 fii_ea_util_pkg.get_viewby_id(p_aggrt_viewby_id, p_snap_aggrt_viewby_id, p_nonaggrt_viewby_id);
1941 fii_ea_util_pkg.populate_security_gt_tables(p_aggrt_gt_is_empty, p_non_aggrt_gt_is_empty);
1942
1943 CASE fii_ea_util_pkg.g_page_period_type -- we set different 'period type' dependent variables in this CASE structure
1944
1945 WHEN 'FII_TIME_ENT_YEAR' THEN
1946 l_roll_column := 'qtd';
1947 l_xtd_column := 'ytd' ;
1948
1949 WHEN 'FII_TIME_ENT_QTR' THEN
1950 l_roll_column := 'mtd';
1951 l_xtd_column := 'qtd' ;
1952
1953 WHEN 'FII_TIME_ENT_PERIOD' THEN
1954 l_roll_column := 'mtd';
1955 l_xtd_column := 'mtd' ;
1956
1957 ELSE
1958 NULL;
1959
1960 END CASE;
1961
1962 -- When Compare To is Budget, we display Budget instead of Prior Income
1963 -- l_snap_prior is used when hitting fii_gl_snap_sum_f
1964 -- l_agrt_base_prior is used when hitting fii_gl_agrt_sum_mv & fii_gl_base_map_mv
1965 -- l_trend_mv_prior is used when hitting fii_gl_trend_sum_mv
1966
1967 IF (fii_ea_util_pkg.g_time_comp = 'BUDGET') THEN
1968 l_snap_prior :=
1969 ',NULL FII_PL_PRIOR_REVENUE
1970 ,NULL FII_PL_PRIOR_COGS
1971 ,NULL FII_PL_PRIOR_EXP
1972 ,NULL FII_PL_PRIOR_COGS_TOTAL_G
1973 ,NULL FII_PL_PRIOR_REVENUE_TOTAL_G
1974 ,NULL FII_PL_PRIOR_EXP_TOTAL_G
1975 ';
1976 l_agrt_base_prior := l_snap_prior;
1977 l_trend_mv_prior := REPLACE(l_agrt_base_prior,'fin_hier','f');
1978
1979 -- When Compare To is Prior Period
1980
1981 ELSIF fii_ea_util_pkg.g_time_comp = 'SEQUENTIAL' THEN
1982 l_snap_prior :=
1983
1984 ',SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''R''
1985 THEN f.actual_prior_'||l_xtd_column||'
1986 ELSE NULL
1987 END
1988 ) FII_PL_PRIOR_REVENUE
1989 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''CGS''
1990 THEN f.actual_prior_'||l_xtd_column||'
1991 ELSE NULL
1992 END
1993 ) FII_PL_PRIOR_COGS
1994 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''OE''
1995 THEN f.actual_prior_'||l_xtd_column||'
1996 ELSE NULL
1997 END
1998 ) FII_PL_PRIOR_EXP
1999 ,NULL FII_PL_PRIOR_COGS_TOTAL_G
2000 ,NULL FII_PL_PRIOR_REVENUE_TOTAL_G
2001 ,NULL FII_PL_PRIOR_EXP_TOTAL_G
2002 ';
2003
2004 l_agrt_base_prior :=
2005
2006 ',SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
2007 AND fin_hier.top_node_fin_cat_type = ''R'' THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_REVENUE
2008 ,SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE
2009 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''CGS''
2010 THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_COGS
2011 ,SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE
2012 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''OE''
2013 THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_EXP
2014 ,SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_PERIOD_END AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
2015 AND fin_hier.top_node_fin_cat_type = ''R'' THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_REVENUE_TOTAL_G
2016 ,SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_PERIOD_END
2017 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''CGS''
2018 THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_COGS_TOTAL_G
2019 ,SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_PERIOD_END
2020 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''OE''
2021 THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_EXP_TOTAL_G
2022 ';
2023
2024 l_trend_mv_prior := REPLACE(l_agrt_base_prior,'fin_hier','f');
2025
2026 -- When Period Type chosen is Year
2027
2028 ELSIF fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN
2029 l_snap_prior :=
2030
2031 ',SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''R''
2032 THEN f.actual_prior_'||l_xtd_column||'
2033 ELSE NULL
2034 END
2035 ) FII_PL_PRIOR_REVENUE
2036 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''CGS''
2037 THEN f.actual_prior_'||l_xtd_column||'
2038 ELSE NULL
2039 END
2040 ) FII_PL_PRIOR_COGS
2041 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''OE''
2042 THEN f.actual_prior_'||l_xtd_column||'
2043 ELSE NULL
2044 END
2045 ) FII_PL_PRIOR_EXP
2046 ,NULL FII_PL_PRIOR_COGS_TOTAL_G
2047 ,NULL FII_PL_PRIOR_REVENUE_TOTAL_G
2048 ,NULL FII_PL_PRIOR_EXP_TOTAL_G
2049 ';
2050
2051 l_agrt_base_prior :=
2052
2053 ',SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
2054 AND fin_hier.top_node_fin_cat_type = ''R'' THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_REVENUE
2055 ,SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE
2056 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''CGS''
2057 THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_COGS
2058 ,SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE
2059 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''OE''
2060 THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_EXP
2061 ,SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_PERIOD_END
2062 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''R''
2063 THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_REVENUE_TOTAL_G
2064 ,SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_PERIOD_END AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
2065 AND fin_hier.top_node_fin_cat_type = ''CGS'' THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_COGS_TOTAL_G
2066 ,SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_PERIOD_END
2067 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''OE''
2068 THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_EXP_TOTAL_G
2069 ';
2070 l_trend_mv_prior := REPLACE(l_agrt_base_prior,'fin_hier','f');
2071
2072 ELSE
2073
2074 l_snap_prior :=
2075
2076 ',SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''R''
2077 THEN f.actual_last_year_'||l_xtd_column||'
2078 ELSE NULL
2079 END
2080 ) FII_PL_PRIOR_REVENUE
2081 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''CGS''
2082 THEN f.actual_last_year_'||l_xtd_column||'
2083 ELSE NULL
2084 END
2085 ) FII_PL_PRIOR_COGS
2086 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''OE''
2087 THEN f.actual_last_year_'||l_xtd_column||'
2088 ELSE NULL
2089 END
2090 ) FII_PL_PRIOR_EXP
2091 ,NULL FII_PL_PRIOR_COGS_TOTAL_G
2092 ,NULL FII_PL_PRIOR_REVENUE_TOTAL_G
2093 ,NULL FII_PL_PRIOR_EXP_TOTAL_G
2094 ';
2095 l_agrt_base_prior :=
2096
2097 ',SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE
2098 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''R''
2099 THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_REVENUE
2100 ,SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE
2101 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''CGS''
2102 THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_COGS
2103 ,SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE
2104 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''OE''
2105 THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_EXP
2106 ,SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_PERIOD_END
2107 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''R''
2108 THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_REVENUE_TOTAL_G
2109 ,SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_PERIOD_END
2110 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''CGS''
2111 THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_COGS_TOTAL_G
2112 ,SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_PERIOD_END
2113 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''OE''
2114 THEN f.actual_g ELSE NULL END) FII_PL_PRIOR_EXP_TOTAL_G
2115 ';
2116
2117 l_trend_mv_prior := REPLACE(l_agrt_base_prior,'fin_hier','f');
2118
2119 END IF;
2120
2121 IF fii_ea_util_pkg.g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN
2122
2123 fii_ea_util_pkg.check_if_leaf(fii_ea_util_pkg.g_udd1_id);
2124 l_if_leaf_flag := fii_ea_util_pkg.g_ud1_is_leaf;
2125
2126 -- Following variables are used to check for loading of budgets against summary nodes,
2127 -- we don't need to append l_budget_snap_decode and l_budget_decode to the main sql, when we choose a leaf fud1 node.
2128
2129 IF l_if_leaf_flag = 'N' THEN
2130 l_fud1_decode := 'and fud1_hier.parent_value_id = DECODE(fud1_hier.parent_value_id, :UDD1_ID,
2131 fud1_hier.child_value_id, fud1_hier.parent_value_id)';
2132 l_budget_snap_decode := 'and f.fud1_id = DECODE( :G_ID, f.fud1_id,
2133 DECODE(budget_cur_'||l_roll_column||',0, -99999, f.fud1_id),f.fud1_id)';
2134 l_budget_decode := 'and f.fud1_id = DECODE(:G_ID, f.fud1_id,
2135 DECODE(f.time_id,:TIME_ID, f.fud1_id,-99999),f.fud1_id)';
2136 END IF;
2137 ELSE
2138 l_if_leaf_flag := 'Y';
2139
2140 END IF;
2141
2142 -- Checking if User Defined Dimension2 is enabled and forming FROM/WHERE clauses
2143
2144 SELECT dbi_enabled_flag
2145 INTO l_fud2_enabled_flag
2146 FROM fii_financial_dimensions
2147 WHERE dimension_short_name = 'FII_USER_DEFINED_2';
2148
2149 IF l_fud2_enabled_flag = 'Y' THEN
2150
2151 IF fii_ea_util_pkg.g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_2' THEN
2152
2153 l_fud2_from := ' fii_udd2_hierarchies fud2_hier, ';
2154
2155 l_fud2_snap_where := ' AND fud2_hier.parent_value_id = gt.fud2_id AND fud2_hier.child_value_id = f.fud2_id ';
2156
2157 l_fud2_where := ' AND fud2_hier.parent_value_id = inner_inline_view.fud2_id AND fud2_hier.child_value_id = f.fud2_id ';
2158
2159 ELSIF fii_ea_util_pkg.g_fud2_id <> 'All' THEN
2160
2161 l_fud2_from := ' fii_udd2_hierarchies fud2_hier, ';
2162
2163 l_fud2_snap_where := ' AND fud2_hier.parent_value_id = gt.fud2_id AND fud2_hier.child_value_id = f.fud2_id ';
2164
2165 l_fud2_where := ' AND fud2_hier.parent_value_id = inner_inline_view.fud2_id AND fud2_hier.child_value_id = f.fud2_id ';
2166 END IF;
2167
2168 END IF;
2169
2170 -- Drill on ViewBy Column
2171 l_viewby_drill_url := 'pFunctionName=FII_PL_OPER_MARGIN_SUMM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
2172
2173 -- l_sqlstmt1 is the sql to be used, when report_date <> sysdate and fii_pmv_aggrt_gt has been populated
2174
2175 l_sqlstmt1 :=
2176
2177 ' -- Aggrt nodes
2178 SELECT /*+ index(f fii_gl_agrt_sum_mv_n1) */
2179 '||p_aggrt_viewby_id||' viewby_id
2180 ,inner_inline_view.viewby viewby
2181 ,inner_inline_view.sort_order sort_order
2182 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
2183 AND fin_hier.top_node_fin_cat_type = ''R'' THEN f.actual_g ELSE NULL END) FII_PL_CURR_REVENUE
2184 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
2185 AND fin_hier.top_node_fin_cat_type = ''CGS'' THEN f.actual_g ELSE NULL END) FII_PL_CURR_COGS
2186 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
2187 AND fin_hier.top_node_fin_cat_type = ''OE'' THEN f.actual_g ELSE NULL END) FII_PL_CURR_EXP
2188 '||l_agrt_base_prior||'
2189 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
2190 AND fin_hier.top_node_fin_cat_type = ''R'' THEN f.budget_g ELSE NULL END) FII_PL_REV_BUDGET
2191 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
2192 AND fin_hier.top_node_fin_cat_type = ''CGS'' THEN f.budget_g ELSE NULL END) FII_PL_COGS_BUDGET
2193 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
2194 AND fin_hier.top_node_fin_cat_type = ''OE'' THEN f.budget_g ELSE NULL END) FII_PL_EXP_BUDGET
2195 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
2196 AND fin_hier.top_node_fin_cat_type = ''R'' THEN f.forecast_g ELSE NULL END) FII_PL_REV_FORECAST
2197 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
2198 AND fin_hier.top_node_fin_cat_type = ''CGS'' THEN f.forecast_g ELSE NULL END) FII_PL_COGS_FORECAST
2199 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
2200 AND fin_hier.top_node_fin_cat_type = ''OE'' THEN f.forecast_g ELSE NULL END) FII_PL_EXP_FORECAST
2201 FROM fii_gl_agrt_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
2202 fii_fin_item_leaf_hiers fin_hier,
2203 '||l_fud2_from||'
2204 (SELECT /*+ NO_MERGE cardinality(gt 1) */ *
2205 FROM fii_time_structures cal,
2206 fii_pmv_aggrt_gt gt
2207 WHERE report_date IN ( :ASOF_DATE,:PREVIOUS_ASOF_DATE,:PRIOR_PERIOD_END)
2208 AND (BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND OR BITAND(cal.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND OR
2209 BITAND(cal.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND)) inner_inline_view
2210 WHERE f.time_id = inner_inline_view.time_id
2211 AND f.period_type_id = inner_inline_view.period_type_id
2212 AND f.parent_company_id = inner_inline_view.parent_company_id
2213 AND f.company_id = inner_inline_view.company_id
2214 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
2215 AND f.cost_center_id = inner_inline_view.cc_id
2216 '||l_budget_decode||'
2217 AND f.parent_fud1_id = inner_inline_view.parent_fud1_id
2218 AND f.fud1_id = inner_inline_view.fud1_id
2219 '||l_fud2_where||'
2220 AND fin_hier.next_level_fin_cat_id = f.fin_category_id
2221 AND fin_hier.next_level_fin_cat_id = fin_hier.child_fin_cat_id
2222 GROUP BY '||p_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order';
2223
2224 -- l_sqlstmt2 is the sql to be used, when report_date <> sysdate and fii_pmv_non_aggrt_gt has been populated
2225
2226 l_sqlstmt2 :=
2227
2228 ' -- NonAggrt nodes
2229 SELECT /*+ index(f fii_gl_base_map_mv_n1) */
2230 '||p_nonaggrt_viewby_id||' viewby_id
2231 ,inner_inline_view.viewby viewby
2232 ,inner_inline_view.sort_order sort_order
2233 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2234 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''R''
2235 THEN f.actual_g ELSE NULL END) FII_PL_CURR_REVENUE
2236 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2237 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''CGS''
2238 THEN f.actual_g ELSE NULL END) FII_PL_CURR_COGS
2239 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2240 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''OE''
2241 THEN f.actual_g ELSE NULL END) FII_PL_CURR_EXP
2242 '||l_agrt_base_prior||'
2243 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2244 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND AND fin_hier.top_node_fin_cat_type = ''R''
2245 THEN f.budget_g ELSE NULL END) FII_PL_REV_BUDGET
2246 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2247 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND AND fin_hier.top_node_fin_cat_type = ''CGS''
2248 THEN f.budget_g ELSE NULL END) FII_PL_COGS_BUDGET
2249 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2250 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND AND fin_hier.top_node_fin_cat_type = ''OE''
2251 THEN f.budget_g ELSE NULL END) FII_PL_EXP_BUDGET
2252 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2253 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND AND fin_hier.top_node_fin_cat_type = ''R''
2254 THEN f.forecast_g ELSE NULL END) FII_PL_REV_FORECAST
2255 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2256 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND AND fin_hier.top_node_fin_cat_type = ''CGS''
2257 THEN f.forecast_g ELSE NULL END) FII_PL_COGS_FORECAST
2258 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2259 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND AND fin_hier.top_node_fin_cat_type = ''OE''
2260 THEN f.forecast_g ELSE NULL END) FII_PL_EXP_FORECAST
2261 FROM fii_gl_base_map_mv'||fii_ea_util_pkg.g_curr_view||' f,
2262 fii_company_hierarchies co_hier,
2263 fii_cost_ctr_hierarchies cc_hier,
2264 fii_fin_item_leaf_hiers fin_hier,
2265 fii_udd1_hierarchies fud1_hier,
2266 '||l_fud2_from||'
2267 (SELECT /*+ NO_MERGE cardinality(gt 1) */ *
2268 FROM fii_time_structures cal,
2269 fii_pmv_non_aggrt_gt gt
2270 WHERE report_date IN ( :ASOF_DATE,:PREVIOUS_ASOF_DATE,:PRIOR_PERIOD_END)
2271 AND (BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND OR BITAND(cal.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND OR
2272 BITAND(cal.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND)) inner_inline_view
2273 WHERE f.period_type_id = inner_inline_view.period_type_id
2274 AND f.time_id = inner_inline_view.time_id
2275 AND co_hier.parent_company_id = inner_inline_view.company_id
2276 AND co_hier.child_company_id = f.company_id
2277 AND cc_hier.parent_cc_id = inner_inline_view.cost_center_id
2278 AND cc_hier.child_cc_id = f.cost_center_id
2279 '||l_budget_decode||'
2280 AND fin_hier.child_fin_cat_id = f.fin_category_id
2281 AND fud1_hier.parent_value_id = inner_inline_view.fud1_id
2282 '||l_fud1_decode||'
2283 AND fud1_hier.child_value_id = f.fud1_id
2284 '||l_fud2_where||'
2285 GROUP BY '||p_nonaggrt_viewby_id||',inner_inline_view.viewby,inner_inline_view.sort_order';
2286
2287 l_snap_sqlstmt1 :=
2288
2289 'SELECT viewby_id
2290 ,viewby
2291 ,sort_order
2292 ,SUM(FII_PL_CURR_REVENUE) FII_PL_CURR_REVENUE
2293 ,SUM(FII_PL_CURR_COGS) FII_PL_CURR_COGS
2294 ,SUM(FII_PL_CURR_EXP) FII_PL_CURR_EXP
2295 ,SUM(FII_PL_PRIOR_REVENUE) FII_PL_PRIOR_REVENUE
2296 ,SUM(FII_PL_PRIOR_COGS) FII_PL_PRIOR_COGS
2297 ,SUM(FII_PL_PRIOR_EXP) FII_PL_PRIOR_EXP
2298 ,SUM(FII_PL_REV_BUDGET) FII_PL_REV_BUDGET
2299 ,SUM(FII_PL_COGS_BUDGET) FII_PL_COGS_BUDGET
2300 ,SUM(FII_PL_EXP_BUDGET) FII_PL_EXP_BUDGET
2301 ,SUM(FII_PL_REV_FORECAST) FII_PL_REV_FORECAST
2302 ,SUM(FII_PL_COGS_FORECAST) FII_PL_COGS_FORECAST
2303 ,SUM(FII_PL_EXP_FORECAST) FII_PL_EXP_FORECAST
2304 ,SUM(FII_PL_PRIOR_COGS_TOTAL_G) FII_PL_PRIOR_COGS_TOTAL_G
2305 ,SUM(FII_PL_PRIOR_REVENUE_TOTAL_G) FII_PL_PRIOR_REVENUE_TOTAL_G
2306 ,SUM(FII_PL_PRIOR_EXP_TOTAL_G) FII_PL_PRIOR_EXP_TOTAL_G
2307 FROM
2308 (SELECT /*+ index(f fii_gl_snap_sum_f_n1) */
2309 '||p_snap_aggrt_viewby_id||' viewby_id
2310 ,gt.viewby viewby
2311 ,gt.sort_order sort_order
2312 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''R''
2313 THEN f.actual_cur_'||l_xtd_column||'
2314 ELSE NULL END) FII_PL_CURR_REVENUE
2315 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''CGS''
2316 THEN f.actual_cur_'||l_xtd_column||'
2317 ELSE NULL END) FII_PL_CURR_COGS
2318 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''OE''
2319 THEN f.actual_cur_'||l_xtd_column||'
2320 ELSE NULL END) FII_PL_CURR_EXP
2321 '||l_snap_prior||'
2322 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''R''
2323 THEN f.budget_cur_'||l_xtd_column||'
2324 ELSE NULL END) FII_PL_REV_BUDGET
2325 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''CGS''
2326 THEN f.budget_cur_'||l_xtd_column||'
2327 ELSE NULL END) FII_PL_COGS_BUDGET
2328 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''OE''
2329 THEN f.budget_cur_'||l_xtd_column||'
2330 ELSE NULL END) FII_PL_EXP_BUDGET
2331 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''R''
2332 THEN f.forecast_cur_'||l_xtd_column||'
2333 ELSE NULL END) FII_PL_REV_FORECAST
2334 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''CGS''
2335 THEN f.forecast_cur_'||l_xtd_column||'
2336 ELSE NULL END) FII_PL_COGS_FORECAST
2337 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''OE''
2338 THEN f.forecast_cur_'||l_xtd_column||'
2339 ELSE NULL END) FII_PL_EXP_FORECAST
2340 FROM fii_gl_snap_sum_f'||fii_ea_util_pkg.g_curr_view||' f,
2341 fii_fin_item_leaf_hiers fin_hier,
2342 '||l_fud2_from||'
2343 fii_pmv_aggrt_gt gt
2344 WHERE f.parent_company_id = gt.parent_company_id
2345 and f.fin_category_id = fin_hier.child_fin_cat_id
2346 and f.company_id = gt.company_id
2347 and f.parent_cost_center_id = gt.parent_cc_id
2348 and f.cost_center_id =gt.cc_id
2349 '||l_budget_snap_decode||'
2350 and f.parent_fud1_id = gt.parent_fud1_id
2351 and f.fud1_id =gt.fud1_id
2352 '||l_fud2_snap_where||'
2353 GROUP BY '||p_snap_aggrt_viewby_id||', gt.viewby, gt.sort_order
2354 UNION ALL
2355 /* QUERY -- PRIOR TOTAL INCOME */
2356 SELECT /*+ index(f fii_gl_agrt_sum_mv_n1) */
2357 '||p_aggrt_viewby_id||' viewby_id
2358 ,inner_inline_view.viewby viewby
2359 ,inner_inline_view.sort_order sort_order
2360 ,NULL FII_PL_CURR_REVENUE
2361 ,NULL FII_PL_CURR_COGS
2362 ,NULL FII_PL_CURR_EXP
2363 ,NULL FII_PL_PRIOR_REVENUE
2364 ,NULL FII_PL_PRIOR_COGS
2365 ,NULL FII_PL_PRIOR_EXP
2366 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''CGS'' THEN f.actual_g
2367 ELSE NULL END) FII_PL_PRIOR_COGS_TOTAL_G
2368 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''R'' THEN f.actual_g
2369 ELSE NULL END) FII_PL_PRIOR_REVENUE_TOTAL_G
2370 ,SUM(CASE WHEN fin_hier.top_node_fin_cat_type = ''OE'' THEN f.actual_g
2371 ELSE NULL END) FII_PL_PRIOR_EXP_TOTAL_G
2372 ,NULL FII_PL_REV_BUDGET
2373 ,NULL FII_PL_COGS_BUDGET
2374 ,NULL FII_PL_EXP_BUDGET
2375 ,NULL FII_PL_REV_FORECAST
2376 ,NULL FII_PL_COGS_FORECAST
2377 ,NULL FII_PL_EXP_FORECAST
2378 FROM fii_gl_agrt_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
2379 fii_fin_item_leaf_hiers fin_hier,
2380 '||l_fud2_from||'
2381 (SELECT /*+ NO_MERGE cardinality(gt 1) */ *
2382 FROM fii_time_structures cal,
2383 fii_pmv_aggrt_gt gt
2384 WHERE report_date = :PRIOR_PERIOD_END
2385 AND BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND) inner_inline_view
2386 WHERE f.time_id = inner_inline_view.time_id
2387 AND f.period_type_id = inner_inline_view.period_type_id
2388 AND f.parent_company_id = inner_inline_view.parent_company_id
2389 AND f.company_id = inner_inline_view.company_id
2390 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
2391 AND f.cost_center_id = inner_inline_view.cc_id
2392 '||l_budget_decode||'
2393 AND f.parent_fud1_id = inner_inline_view.parent_fud1_id
2394 AND f.fud1_id = inner_inline_view.fud1_id
2395 '||l_fud2_where||'
2396 AND fin_hier.top_node_fin_cat_type IN (''R'', ''CGS'', ''OE'')
2397 AND fin_hier.next_level_fin_cat_id = f.fin_category_id
2398 AND fin_hier.next_level_fin_cat_id = fin_hier.child_fin_cat_id
2399 GROUP BY '||p_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order
2400 ) GROUP BY viewby_id, viewby, sort_order';
2401
2402 l_snap_sqlstmt2 :=
2403
2404 ' SELECT /*+ index(f fii_gl_base_map_mv_n1) */
2405 '||p_nonaggrt_viewby_id||' viewby_id
2406 ,inner_inline_view.viewby viewby
2407 ,inner_inline_view.sort_order sort_order
2408 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
2409 AND fin_hier.top_node_fin_cat_type = ''R'' THEN f.actual_g ELSE NULL END) FII_PL_CURR_REVENUE
2410 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2411 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND AND fin_hier.top_node_fin_cat_type = ''CGS''
2412 THEN f.actual_g ELSE NULL END) FII_PL_CURR_COGS
2413 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
2414 AND fin_hier.top_node_fin_cat_type = ''OE'' THEN f.actual_g ELSE NULL END) FII_PL_CURR_EXP
2415 '||l_agrt_base_prior||'
2416 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2417 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
2418 AND fin_hier.top_node_fin_cat_type = ''R'' THEN f.budget_g ELSE NULL END) FII_PL_REV_BUDGET
2419 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2420 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
2421 AND fin_hier.top_node_fin_cat_type = ''CGS'' THEN f.budget_g ELSE NULL END) FII_PL_COGS_BUDGET
2422 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2423 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
2424 AND fin_hier.top_node_fin_cat_type = ''OE'' THEN f.budget_g ELSE NULL END) FII_PL_EXP_BUDGET
2425 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2426 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
2427 AND fin_hier.top_node_fin_cat_type = ''R'' THEN f.forecast_g ELSE NULL END) FII_PL_REV_FORECAST
2428 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2429 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
2430 AND fin_hier.top_node_fin_cat_type = ''CGS'' THEN f.forecast_g ELSE NULL END) FII_PL_COGS_FORECAST
2431 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
2432 AND fin_hier.top_node_fin_cat_type = ''OE'' THEN f.forecast_g ELSE NULL END) FII_PL_EXP_FORECAST
2433 FROM fii_gl_base_map_mv'||fii_ea_util_pkg.g_curr_view||' f,
2434 fii_company_hierarchies co_hier,
2435 fii_cost_ctr_hierarchies cc_hier,
2436 fii_fin_item_leaf_hiers fin_hier,
2437 fii_udd1_hierarchies fud1_hier,
2438 '||l_fud2_from||'
2439 (SELECT /*+ NO_MERGE cardinality(gt 1) */ *
2440 FROM fii_time_structures cal,
2441 fii_pmv_non_aggrt_gt gt
2442 WHERE report_date IN ( :ASOF_DATE,:PREVIOUS_ASOF_DATE,:PRIOR_PERIOD_END)
2443 AND (BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND OR BITAND(cal.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND OR
2444 BITAND(cal.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND)) inner_inline_view
2445 WHERE f.period_type_id = inner_inline_view.period_type_id
2446 and f.time_id = inner_inline_view.time_id
2447 and co_hier.parent_company_id = inner_inline_view.company_id
2448 and co_hier.child_company_id = f.company_id
2449 and cc_hier.parent_cc_id = inner_inline_view.cost_center_id
2450 and cc_hier.child_cc_id = f.cost_center_id
2451 and fin_hier.child_fin_cat_id = f.fin_category_id
2452 and fud1_hier.parent_value_id = inner_inline_view.fud1_id
2453 '||l_fud1_decode||'
2454 '||l_budget_decode||'
2455 and fud1_hier.child_value_id = f.fud1_id
2456 '||l_fud2_where||'
2457 GROUP BY '||p_nonaggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order';
2458
2459 -- When fii_gl_trend_sum_mv is hit
2460
2461 l_trend_sum_mv_sql :='
2462 SELECT '||p_aggrt_viewby_id||' viewby_id
2463 ,inner_inline_view.viewby viewby
2464 ,inner_inline_view.sort_order sort_order
2465 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2466 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
2467 AND f.top_node_fin_cat_type = ''R''
2468 THEN f.actual_g
2469 ELSE NULL
2470 END
2471 ) FII_PL_CURR_REVENUE
2472 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2473 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
2474 AND f.top_node_fin_cat_type = ''CGS''
2475 THEN f.actual_g
2476 ELSE NULL
2477 END
2478 ) FII_PL_CURR_COGS
2479 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2480 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
2481 AND f.top_node_fin_cat_type = ''OE''
2482 THEN f.actual_g
2483 ELSE NULL
2484 END
2485 ) FII_PL_CURR_EXP
2486 '||l_trend_mv_prior||'
2487 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2488 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
2489 AND f.top_node_fin_cat_type = ''R''
2490 THEN f.budget_g
2491 ELSE NULL
2492 END
2493 ) FII_PL_REV_BUDGET
2494 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2495 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
2496 AND f.top_node_fin_cat_type = ''CGS''
2497 THEN f.budget_g
2498 ELSE NULL
2499 END
2500 ) FII_PL_COGS_BUDGET
2501 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2502 AND BITAND(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
2503 AND f.top_node_fin_cat_type = ''OE''
2504 THEN f.budget_g
2505 ELSE NULL
2506 END
2507 ) FII_PL_EXP_BUDGET
2508 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2509 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
2510 AND f.top_node_fin_cat_type = ''R''
2511 THEN f.forecast_g
2512 ELSE NULL
2513 END
2514 ) FII_PL_REV_FORECAST
2515 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2516 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
2517 AND f.top_node_fin_cat_type = ''CGS''
2518 THEN f.forecast_g
2519 ELSE NULL
2520 END
2521 ) FII_PL_COGS_FORECAST
2522 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2523 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
2524 AND f.top_node_fin_cat_type = ''OE''
2525 THEN f.forecast_g
2526 ELSE NULL
2527 END
2528 ) FII_PL_EXP_FORECAST
2529 FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
2530 ( SELECT /*+ NO_MERGE cardinality(gt 1) */ *
2531 FROM fii_time_structures cal,
2532 fii_pmv_aggrt_gt gt
2533 WHERE report_date IN ( :ASOF_DATE
2534 ,:PREVIOUS_ASOF_DATE
2535 ,:PRIOR_PERIOD_END
2536 )
2537 AND (BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND OR
2538 BITAND(cal.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND OR
2539 BITAND(cal.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND
2540 )
2541 ) inner_inline_view
2542 WHERE f.time_id = inner_inline_view.time_id
2543 AND f.period_type_id = inner_inline_view.period_type_id
2544 AND f.parent_company_id = inner_inline_view.parent_company_id
2545 AND f.company_id = inner_inline_view.company_id
2546 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
2547 AND f.cost_center_id = inner_inline_view.cc_id
2548 '||l_budget_decode||'
2549
2550 GROUP BY '||p_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order';
2551
2552 -- SQL for Operating Margin table portlet
2553 -- Here, we don't calculate any prior and budget columns
2554
2555 l_trend_sum_mv_sql_port :='
2556 SELECT '||p_aggrt_viewby_id||' viewby_id
2557 ,inner_inline_view.viewby viewby
2558 ,inner_inline_view.sort_order sort_order
2559 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2560 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
2561 AND f.top_node_fin_cat_type = ''R''
2562 THEN f.actual_g
2563 ELSE NULL
2564 END
2565 ) FII_PL_CURR_REVENUE
2566 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2567 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
2568 AND f.top_node_fin_cat_type = ''CGS''
2569 THEN f.actual_g
2570 ELSE NULL
2571 END
2572 ) FII_PL_CURR_COGS
2573 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2574 AND BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
2575 AND f.top_node_fin_cat_type = ''OE''
2576 THEN f.actual_g
2577 ELSE NULL
2578 END
2579 ) FII_PL_CURR_EXP
2580 ,NULL FII_PL_PRIOR_REVENUE
2581 ,NULL FII_PL_PRIOR_COGS
2582 ,NULL FII_PL_PRIOR_EXP
2583 ,NULL FII_PL_REV_BUDGET
2584 ,NULL FII_PL_COGS_BUDGET
2585 ,NULL FII_PL_EXP_BUDGET
2586 ,NULL FII_PL_PRIOR_REVENUE_TOTAL_G
2587 ,NULL FII_PL_PRIOR_COGS_TOTAL_G
2588 ,NULL FII_PL_PRIOR_EXP_TOTAL_G
2589 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2590 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
2591 AND f.top_node_fin_cat_type = ''R''
2592 THEN f.forecast_g
2593 ELSE NULL
2594 END
2595 ) FII_PL_REV_FORECAST
2596 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2597 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
2598 AND f.top_node_fin_cat_type = ''CGS''
2599 THEN f.forecast_g
2600 ELSE NULL
2601 END
2602 ) FII_PL_COGS_FORECAST
2603 ,SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
2604 AND BITAND(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
2605 AND f.top_node_fin_cat_type = ''OE''
2606 THEN f.forecast_g
2607 ELSE NULL
2608 END
2609 ) FII_PL_EXP_FORECAST
2610 FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
2611 ( SELECT /*+ NO_MERGE cardinality(gt 1) */ *
2612 FROM fii_time_structures cal,
2613 fii_pmv_aggrt_gt gt
2614 WHERE report_date IN ( :ASOF_DATE
2615 ,:PREVIOUS_ASOF_DATE
2616 ,:PRIOR_PERIOD_END
2617 )
2618 AND (BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND OR
2619 BITAND(cal.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND OR
2620 BITAND(cal.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND
2621 )
2622 ) inner_inline_view
2623 WHERE f.time_id = inner_inline_view.time_id
2624 AND f.period_type_id = inner_inline_view.period_type_id
2625 AND f.parent_company_id = inner_inline_view.parent_company_id
2626 AND f.company_id = inner_inline_view.company_id
2627 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
2628 AND f.cost_center_id = inner_inline_view.cc_id
2629 '||l_budget_decode||'
2630 GROUP BY '||p_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order';
2631
2632 -- Deciding upon SQL, based on FF name
2633 IF l_function_name = 'FII_PL_OPER_MARGIN_TABLE' THEN
2634 l_trend_sum_mv_sql := l_trend_sum_mv_sql_port;
2635 END IF;
2636
2637 -- Checking conditions to decide upon the SQL variable
2638
2639 IF fii_ea_util_pkg.g_if_trend_sum_mv = 'Y' THEN
2640
2641 l_aggrt_sql := l_trend_sum_mv_sql;
2642
2643 ELSIF fii_ea_util_pkg.g_snapshot = 'Y' THEN
2644
2645 IF p_aggrt_gt_is_empty = 'N' then -- aggrt GT table is populated
2646
2647 l_aggrt_sql := l_snap_sqlstmt1;
2648
2649 IF p_non_aggrt_gt_is_empty = 'N' then -- both GT tables are populated
2650 l_non_aggrt_sql := l_snap_sqlstmt2;
2651 l_union_all := 'UNION ALL';
2652 END IF;
2653
2654 ELSIF p_non_aggrt_gt_is_empty = 'N' then -- only non aggrt GT table is populated
2655
2656 l_non_aggrt_sql := l_snap_sqlstmt2;
2657
2658 ELSE -- neither of the GT tables are populated...
2659
2660 l_aggrt_sql := l_snap_sqlstmt1;
2661
2662 END IF;
2663 ELSE
2664 IF p_aggrt_gt_is_empty = 'N' then -- aggrt GT table is populated
2665
2666 l_aggrt_sql := l_sqlstmt1;
2667
2668 IF p_non_aggrt_gt_is_empty = 'N' then -- both GT tables are populated
2669 l_non_aggrt_sql := l_sqlstmt2;
2670 l_union_all := 'UNION ALL';
2671 END IF;
2672
2673 ELSIF p_non_aggrt_gt_is_empty = 'N' then -- only non aggrt GT table is populated
2674
2675 l_non_aggrt_sql := l_sqlstmt2;
2676
2677 ELSE -- neither of the GT tables are populated...
2678
2679 l_aggrt_sql := l_sqlstmt1;
2680
2681 END IF;
2682
2683 END IF;
2684
2685 l_sqlstmt :=
2686 'SELECT DECODE(:G_ID, inline_view.viewby_id,DECODE('''||l_if_leaf_flag||''',''Y'',
2687 inline_view.viewby, inline_view.viewby||'' ''||:DIR_MSG),
2688 inline_view.viewby) VIEWBY
2689 ,inline_view.viewby_id VIEWBYID
2690 ,(NVL(FII_PL_CURR_REVENUE,0) - NVL(FII_PL_CURR_COGS,0) - NVL(FII_PL_CURR_EXP,0))*100
2691 /NULLIF(ABS(FII_PL_CURR_REVENUE),0) FII_PL_OPER_MARGIN_PCNT
2692 ,(NVL(FII_PL_PRIOR_REVENUE,0) - NVL(FII_PL_PRIOR_COGS,0) - NVL(FII_PL_PRIOR_EXP,0))*100
2693 /NULLIF(ABS(FII_PL_PRIOR_REVENUE),0) FII_PL_PRIOR_OPER_MARGIN_PCNT
2694 ,NVL(FII_PL_PRIOR_REVENUE_TOTAL_G,0) - NVL(FII_PL_PRIOR_COGS_TOTAL_G,0) - NVL(FII_PL_PRIOR_EXP_TOTAL_G,0)
2695 FII_PL_PRIOR_OPER_INC_TOTAL
2696 ,NVL(FII_PL_CURR_REVENUE,0) - NVL(FII_PL_CURR_COGS,0) - NVL(FII_PL_CURR_EXP,0) FII_PL_OPER_INCOME_XTD
2697 ,NULL FII_PL_OPER_INCOME_TOTAL
2698 ,NVL(FII_PL_PRIOR_REVENUE,0) - NVL(FII_PL_PRIOR_COGS,0) - NVL(FII_PL_PRIOR_EXP,0) FII_PL_PRIOR_OPER_INCOME_XTD
2699 ,((NVL(FII_PL_CURR_REVENUE,0) - NVL(FII_PL_CURR_COGS,0) - NVL(FII_PL_CURR_EXP,0)) -
2700 (NVL(FII_PL_PRIOR_REVENUE,0) - NVL(FII_PL_PRIOR_COGS,0) - NVL(FII_PL_PRIOR_EXP,0))) *100
2701 /NULLIF(ABS((NVL(FII_PL_PRIOR_REVENUE,0) - NVL(FII_PL_PRIOR_COGS,0) - NVL(FII_PL_PRIOR_EXP,0))),0) FII_PL_OPER_INCOME_CHANGE
2702 ,NVL(FII_PL_REV_BUDGET,0) - NVL(FII_PL_COGS_BUDGET,0) - NVL(FII_PL_EXP_BUDGET,0) FII_PL_BUDGET
2703 ,(NVL(FII_PL_CURR_REVENUE,0) - NVL(FII_PL_CURR_COGS,0) - NVL(FII_PL_CURR_EXP,0))*100
2704 /NULLIF(ABS(NVL(FII_PL_REV_BUDGET,0) - NVL(FII_PL_COGS_BUDGET,0) - NVL(FII_PL_EXP_BUDGET,0)),0) FII_PL_PCNT_BUDGET
2705 ,NVL(FII_PL_REV_FORECAST,0) - NVL(FII_PL_COGS_FORECAST,0) - NVL(FII_PL_EXP_FORECAST,0) FII_PL_FORECAST
2706 ,(NVL(FII_PL_CURR_REVENUE,0) - NVL(FII_PL_CURR_COGS,0) - NVL(FII_PL_CURR_EXP,0))*100
2707 /NULLIF(ABS(NVL(FII_PL_REV_FORECAST,0) - NVL(FII_PL_COGS_FORECAST,0) - NVL(FII_PL_EXP_FORECAST,0)),0) FII_PL_PCNT_FORECAST
2708 ,(SUM(NVL(FII_PL_CURR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_CURR_COGS,0)) OVER() - SUM(NVL(FII_PL_CURR_EXP,0)) OVER())*100
2709 /NULLIF(ABS(SUM(FII_PL_CURR_REVENUE) OVER ()),0) FII_PL_GT_OPER_MARGIN_PCNT
2710 ,(SUM(NVL(FII_PL_PRIOR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_PRIOR_COGS,0)) OVER() - SUM(NVL(FII_PL_PRIOR_EXP,0)) OVER())*100
2711 /NULLIF(ABS(SUM(FII_PL_PRIOR_REVENUE) OVER ()),0) FII_PL_GT_PRIOR_OPER_MGN_PCNT
2712 ,(SUM(NVL(FII_PL_CURR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_CURR_COGS,0)) OVER() - SUM(NVL(FII_PL_CURR_EXP,0)) OVER())*100
2713 /NULLIF(ABS(SUM(FII_PL_CURR_REVENUE) OVER ()),0) -
2714 (SUM(NVL(FII_PL_PRIOR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_PRIOR_COGS,0)) OVER() - SUM(NVL(FII_PL_PRIOR_EXP,0)) OVER())*100
2715 /NULLIF(ABS(SUM(FII_PL_PRIOR_REVENUE) OVER ()),0) FII_PL_GT_OPER_MARGIN_CHANGE
2716 ,((SUM(NVL(FII_PL_CURR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_CURR_COGS,0)) OVER () - SUM(NVL(FII_PL_CURR_EXP,0)) OVER()) -
2717 (SUM(NVL(FII_PL_PRIOR_REVENUE,0)) OVER ()- SUM(NVL(FII_PL_PRIOR_COGS,0)) OVER () - SUM(NVL(FII_PL_PRIOR_EXP,0)) OVER()))*100
2718 /NULLIF(ABS((SUM(NVL(FII_PL_PRIOR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_PRIOR_COGS,0)) OVER () - SUM(NVL(FII_PL_PRIOR_EXP,0)) OVER() )),0)
2719 FII_PL_GT_OPER_INCOME_CHANGE
2720 ,(SUM(NVL(FII_PL_CURR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_CURR_COGS,0)) OVER () - SUM(NVL(FII_PL_CURR_EXP,0)) OVER())*100
2721 /NULLIF(ABS(SUM(NVL(FII_PL_REV_BUDGET,0)) OVER () - SUM(NVL(FII_PL_COGS_BUDGET,0)) OVER () - SUM(NVL(FII_PL_EXP_BUDGET,0)) OVER()),0)
2722 FII_PL_GT_PCNT_BUDGET
2723 ,(SUM(NVL(FII_PL_CURR_REVENUE,0)) OVER () - SUM(NVL(FII_PL_CURR_COGS,0)) OVER () - SUM(NVL(FII_PL_CURR_EXP,0)) OVER())*100
2724 /NULLIF(ABS(SUM(NVL(FII_PL_REV_FORECAST,0)) OVER () - SUM(NVL(FII_PL_COGS_FORECAST,0)) OVER () - SUM(NVL(FII_PL_EXP_FORECAST,0)) OVER()),0)
2725 FII_PL_GT_PCNT_FORECAST
2726 ,DECODE
2727 ((SELECT is_leaf_flag
2728 FROM fii_company_hierarchies
2729 WHERE parent_company_id = inline_view.viewby_id
2730 AND child_company_id = inline_view.viewby_id),
2731 ''Y'',
2732 '''',
2733 '''||l_viewby_drill_url||''') FII_PL_COMP_DRILL
2734 ,DECODE
2735 ((SELECT is_leaf_flag
2736 FROM fii_cost_ctr_hierarchies
2737 WHERE parent_cc_id = inline_view.viewby_id
2738 AND child_cc_id = inline_view.viewby_id),''Y'','''','''||l_viewby_drill_url||''') FII_PL_CC_DRILL
2739 ,DECODE
2740 ((SELECT is_leaf_flag
2741 FROM fii_udd1_hierarchies
2742 WHERE parent_value_id = inline_view.viewby_id
2743 AND child_value_id = inline_view.viewby_id),''Y'','''',
2744 DECODE(:G_ID, inline_view.viewby_id,'''','''||l_viewby_drill_url||''')) FII_PL_UDD1_DRILL
2745 ,DECODE
2746 ((SELECT is_leaf_flag
2747 FROM fii_udd2_hierarchies
2748 WHERE parent_value_id = inline_view.viewby_id
2749 AND child_value_id = inline_view.viewby_id),''Y'','''','''||l_viewby_drill_url||''') FII_PL_UDD2_DRILL
2750 FROM ('||l_aggrt_sql||'
2751 '||l_union_all||'
2752 '||l_non_aggrt_sql||'
2753 ) inline_view
2754 ORDER BY NVL(inline_view.sort_order,999999) ASC, NVL(FII_PL_OPER_MARGIN_PCNT,-999999999) DESC';
2755
2756 fii_ea_util_pkg.bind_variable(p_sqlstmt => l_sqlstmt,
2757 p_page_parameter_tbl => p_page_parameter_tbl,
2758 p_sql_output => p_oper_margin_sql,
2759 p_bind_output_table => p_oper_margin_output);
2760
2761 END get_oper_margin;
2762
2763
2764
2765
2766 END fii_pl_page_pkg;
2767