eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.FII_PL_PAGE_PKG dependencies on FII_EA_UTIL_PKG

Line 12: fii_ea_util_pkg.reset_globals;

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:

Line 13: fii_ea_util_pkg.g_fin_cat_type :=NULL;

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;

Line 15: fii_ea_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, pl_graph_sql, pl_graph_output);

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:

Line 39: fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);

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')));

Line 40: fii_ea_util_pkg.g_view_by := 'FII_COMPANIES+FII_COMPANIES';

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')));

Line 41: fii_ea_util_pkg.populate_security_gt_tables(p_aggrt_gt_is_empty, p_non_aggrt_gt_is_empty);

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')));

Line 62: FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,

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) =

Line 81: FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,

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) =

Line 99: FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,

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 )

Line 127: fii_ea_util_pkg.reset_globals;

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,

Line 128: fii_ea_util_pkg.g_fin_type := 'R';

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);

Line 141: fii_ea_util_pkg.reset_globals;

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,

Line 142: fii_ea_util_pkg.g_fin_type := 'OE';

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);

Line 155: fii_ea_util_pkg.reset_globals;

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,

Line 156: fii_ea_util_pkg.g_fin_type := 'CGS';

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);

Line 169: fii_ea_util_pkg.reset_globals;

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);

Line 192: fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);

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

Line 193: fii_ea_util_pkg.populate_security_gt_tables(

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

Line 196: CASE fii_ea_util_pkg.g_page_period_type

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:

Line 214: fii_ea_util_pkg.g_as_of_date)))),l_min_start_date)

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(

Line 215: INTO fii_ea_util_pkg.g_py_sday

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(

Line 221: fii_ea_util_pkg.g_previous_asof_date)))),l_min_start_date)

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;

Line 222: INTO fii_ea_util_pkg.g_five_yr_back

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:

Line 228: IF (fii_ea_util_pkg.g_time_comp = 'SEQUENTIAL') OR

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 ';

Line 229: (fii_ea_util_pkg.g_time_comp = 'FORECAST') THEN

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

Line 233: ELSIF (fii_ea_util_pkg.g_time_comp = 'YEARLY') THEN

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;

Line 235: ELSIF (fii_ea_util_pkg.g_time_comp = 'BUDGET') THEN

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: /* ----------------------------------

Line 247: IF fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN

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,

Line 267: from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,

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

Line 269: '||fii_ea_util_pkg.g_page_period_type||' t

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

Line 290: from fii_gl_trend_sum_mv'|| fii_ea_util_pkg.g_curr_view ||' f,

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

Line 291: '||fii_ea_util_pkg.g_page_period_type||' t,

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

Line 310: ) g1, '||fii_ea_util_pkg.g_page_period_type||' t

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||'

Line 317: ELSIF (fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') and

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,

Line 318: (fii_ea_util_pkg.g_time_comp = 'SEQUENTIAL') THEN

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,

Line 351: from fii_gl_trend_sum_mv'|| fii_ea_util_pkg.g_curr_view ||' f,

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

Line 352: '||fii_ea_util_pkg.g_page_period_type||' t,

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

Line 378: from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,

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,

Line 396: ) g1, '||fii_ea_util_pkg.g_page_period_type||' t

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';

Line 437: from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,

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

Line 438: '||fii_ea_util_pkg.g_page_period_type||' t,

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

Line 465: from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,

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,

Line 484: ) g1, '||fii_ea_util_pkg.g_page_period_type||' t

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';

Line 491: fii_ea_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl,

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:

Line 510: fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);

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

Line 511: fii_ea_util_pkg.populate_security_gt_tables(

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

Line 514: CASE fii_ea_util_pkg.g_page_period_type

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:

Line 532: fii_ea_util_pkg.g_as_of_date)))),l_min_start_date)

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(

Line 533: INTO fii_ea_util_pkg.g_py_sday

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(

Line 539: fii_ea_util_pkg.g_previous_asof_date)))),l_min_start_date)

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;

Line 540: INTO fii_ea_util_pkg.g_five_yr_back

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:

Line 554: IF fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN

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)) /

Line 583: from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,

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

Line 585: '||fii_ea_util_pkg.g_page_period_type||' t

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

Line 603: from fii_gl_trend_sum_mv'|| fii_ea_util_pkg.g_curr_view ||' f,

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

Line 604: '||fii_ea_util_pkg.g_page_period_type||' t,

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

Line 621: ) g1, '||fii_ea_util_pkg.g_page_period_type||' t

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||'

Line 628: ELSIF (fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') and

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,

Line 629: (fii_ea_util_pkg.g_time_comp = 'SEQUENTIAL') THEN

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,

Line 669: from fii_gl_trend_sum_mv'|| fii_ea_util_pkg.g_curr_view ||' f,

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

Line 670: '||fii_ea_util_pkg.g_page_period_type||' t,

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

Line 696: from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,

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,

Line 713: ) g1, '||fii_ea_util_pkg.g_page_period_type||' t

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';

Line 789: from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,

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

Line 790: '||fii_ea_util_pkg.g_page_period_type||' t,

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

Line 827: from fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view ||' f,

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,

Line 844: ) g1, '||fii_ea_util_pkg.g_page_period_type||' t

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';

Line 852: fii_ea_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl,

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: ---------------------------------------------------------------------------------

Line 903: -- Initialization. Calling fii_ea_util_pkg APIs necessary for constructing

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:

Line 906: fii_ea_util_pkg.reset_globals;

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;

Line 910: fii_ea_util_pkg.g_fin_cat_type := NULL;

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:

Line 913: fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);

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:

Line 928: fii_ea_util_pkg.get_viewby_id(p_aggrt_viewby_id, p_snap_aggrt_viewby_id, p_nonaggrt_viewby_id);

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:

Line 929: fii_ea_util_pkg.populate_security_gt_tables(p_aggrt_gt_is_empty, p_non_aggrt_gt_is_empty);

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

Line 931: CASE fii_ea_util_pkg.g_page_period_type -- we set different 'period type' dependent variables in this CASE structure

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' ;

Line 955: IF (fii_ea_util_pkg.g_time_comp = 'BUDGET') THEN

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

Line 966: ELSIF fii_ea_util_pkg.g_time_comp = 'SEQUENTIAL' THEN

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

Line 1013: ELSIF fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN

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

Line 1103: IF fii_ea_util_pkg.g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN

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:

Line 1105: fii_ea_util_pkg.check_if_leaf(fii_ea_util_pkg.g_udd1_id);

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.

Line 1106: l_if_leaf_flag := fii_ea_util_pkg.g_ud1_is_leaf;

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:

Line 1133: IF fii_ea_util_pkg.g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_2' THEN

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

Line 1143: ELSIF fii_ea_util_pkg.g_fud2_id <> 'All' THEN

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

Line 1211: FROM fii_gl_agrt_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,

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,

Line 1295: FROM fii_gl_base_map_mv'||fii_ea_util_pkg.g_curr_view||' f,

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,

Line 1381: FROM fii_gl_snap_sum_f'||fii_ea_util_pkg.g_curr_view||' f,

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

Line 1421: FROM fii_gl_agrt_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,

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,

Line 1495: FROM fii_gl_base_map_mv'||fii_ea_util_pkg.g_curr_view||' f,

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,

Line 1577: FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,

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

Line 1644: FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,

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

Line 1676: IF fii_ea_util_pkg.g_if_trend_sum_mv = 'Y' THEN

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

Line 1680: ELSIF fii_ea_util_pkg.g_snapshot = 'Y' THEN

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;

Line 1861: fii_ea_util_pkg.bind_variable(p_sqlstmt => l_sqlstmt,

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:

Line 1915: -- Initialization. Calling fii_ea_util_pkg APIs necessary for constructing

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:

Line 1918: fii_ea_util_pkg.reset_globals;

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;

Line 1922: fii_ea_util_pkg.g_fin_cat_type := NULL;

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:

Line 1925: fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);

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:

Line 1940: fii_ea_util_pkg.get_viewby_id(p_aggrt_viewby_id, p_snap_aggrt_viewby_id, p_nonaggrt_viewby_id);

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:

Line 1941: fii_ea_util_pkg.populate_security_gt_tables(p_aggrt_gt_is_empty, p_non_aggrt_gt_is_empty);

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

Line 1943: CASE fii_ea_util_pkg.g_page_period_type -- we set different 'period type' dependent variables in this CASE structure

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' ;

Line 1967: IF (fii_ea_util_pkg.g_time_comp = 'BUDGET') THEN

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

Line 1981: ELSIF fii_ea_util_pkg.g_time_comp = 'SEQUENTIAL' THEN

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||'

Line 2028: ELSIF fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN

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||'

Line 2121: IF fii_ea_util_pkg.g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN

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:

Line 2123: fii_ea_util_pkg.check_if_leaf(fii_ea_util_pkg.g_udd1_id);

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.

Line 2124: l_if_leaf_flag := fii_ea_util_pkg.g_ud1_is_leaf;

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:

Line 2151: IF fii_ea_util_pkg.g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_2' THEN

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 ';

Line 2159: ELSIF fii_ea_util_pkg.g_fud2_id <> 'All' THEN

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 ';

Line 2201: FROM fii_gl_agrt_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,

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,

Line 2261: FROM fii_gl_base_map_mv'||fii_ea_util_pkg.g_curr_view||' f,

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,

Line 2340: FROM fii_gl_snap_sum_f'||fii_ea_util_pkg.g_curr_view||' f,

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

Line 2378: FROM fii_gl_agrt_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,

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,

Line 2433: FROM fii_gl_base_map_mv'||fii_ea_util_pkg.g_curr_view||' f,

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,

Line 2529: FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,

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

Line 2610: FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,

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

Line 2639: IF fii_ea_util_pkg.g_if_trend_sum_mv = 'Y' THEN

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

Line 2643: ELSIF fii_ea_util_pkg.g_snapshot = 'Y' THEN

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;

Line 2756: fii_ea_util_pkg.bind_variable(p_sqlstmt => l_sqlstmt,

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: