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_GL_COST_CENTER_PKG2 dependencies on FII_GL_UTIL_PKG

Line 16: fii_gl_util_pkg.reset_globals;

12: l_url2 VARCHAR2(300);
13: l_dim_flag VARCHAR2(1);
14: BEGIN
15:
16: fii_gl_util_pkg.reset_globals;
17: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
18: fii_gl_util_pkg.get_bitmasks;
19: fii_gl_util_pkg.g_fin_type := l_fin_type;
20: fii_gl_util_pkg.get_viewby_sql;

Line 17: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);

13: l_dim_flag VARCHAR2(1);
14: BEGIN
15:
16: fii_gl_util_pkg.reset_globals;
17: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
18: fii_gl_util_pkg.get_bitmasks;
19: fii_gl_util_pkg.g_fin_type := l_fin_type;
20: fii_gl_util_pkg.get_viewby_sql;
21:

Line 18: fii_gl_util_pkg.get_bitmasks;

14: BEGIN
15:
16: fii_gl_util_pkg.reset_globals;
17: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
18: fii_gl_util_pkg.get_bitmasks;
19: fii_gl_util_pkg.g_fin_type := l_fin_type;
20: fii_gl_util_pkg.get_viewby_sql;
21:
22: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN

Line 19: fii_gl_util_pkg.g_fin_type := l_fin_type;

15:
16: fii_gl_util_pkg.reset_globals;
17: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
18: fii_gl_util_pkg.get_bitmasks;
19: fii_gl_util_pkg.g_fin_type := l_fin_type;
20: fii_gl_util_pkg.get_viewby_sql;
21:
22: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
23: l_prior_or_budget :='

Line 20: fii_gl_util_pkg.get_viewby_sql;

16: fii_gl_util_pkg.reset_globals;
17: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
18: fii_gl_util_pkg.get_bitmasks;
19: fii_gl_util_pkg.g_fin_type := l_fin_type;
20: fii_gl_util_pkg.get_viewby_sql;
21:
22: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
23: l_prior_or_budget :='
24: (SUM(SUM(CY_ACTUAL)) over() - SUM(SUM(CY_BUDGET)) over()) /

Line 22: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN

18: fii_gl_util_pkg.get_bitmasks;
19: fii_gl_util_pkg.g_fin_type := l_fin_type;
20: fii_gl_util_pkg.get_viewby_sql;
21:
22: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
23: l_prior_or_budget :='
24: (SUM(SUM(CY_ACTUAL)) over() - SUM(SUM(CY_BUDGET)) over()) /
25: ABS(NULLIF(SUM(SUM(CY_BUDGET)) over(),0)) * 100 FII_ATTRIBUTE12,
26: SUM(CY_BUDGET) FII_MEASURE3,';

Line 49: fii_gl_util_pkg.get_lob_pmv_sql;

45: -- FII_MEASURE2. Check original package.
46: -- DEBUG: Note we cannot control order by in PMV anymore for these type of reports
47: -- unless it's passed into the PLSQL table. Need to raise as concern.
48:
49: fii_gl_util_pkg.get_lob_pmv_sql;
50: fii_gl_util_pkg.get_cat_pmv_sql;
51: fii_gl_util_pkg.get_mgr_pmv_sql;
52:
53: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN

Line 50: fii_gl_util_pkg.get_cat_pmv_sql;

46: -- DEBUG: Note we cannot control order by in PMV anymore for these type of reports
47: -- unless it's passed into the PLSQL table. Need to raise as concern.
48:
49: fii_gl_util_pkg.get_lob_pmv_sql;
50: fii_gl_util_pkg.get_cat_pmv_sql;
51: fii_gl_util_pkg.get_mgr_pmv_sql;
52:
53: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
54: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;

Line 51: fii_gl_util_pkg.get_mgr_pmv_sql;

47: -- unless it's passed into the PLSQL table. Need to raise as concern.
48:
49: fii_gl_util_pkg.get_lob_pmv_sql;
50: fii_gl_util_pkg.get_cat_pmv_sql;
51: fii_gl_util_pkg.get_mgr_pmv_sql;
52:
53: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
54: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
55: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN

Line 53: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN

49: fii_gl_util_pkg.get_lob_pmv_sql;
50: fii_gl_util_pkg.get_cat_pmv_sql;
51: fii_gl_util_pkg.get_mgr_pmv_sql;
52:
53: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
54: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
55: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
56: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
57: ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN

Line 54: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;

50: fii_gl_util_pkg.get_cat_pmv_sql;
51: fii_gl_util_pkg.get_mgr_pmv_sql;
52:
53: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
54: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
55: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
56: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
57: ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
58: l_dim_flag := fii_gl_util_pkg.g_fincat_is_leaf;

Line 55: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN

51: fii_gl_util_pkg.get_mgr_pmv_sql;
52:
53: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
54: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
55: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
56: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
57: ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
58: l_dim_flag := fii_gl_util_pkg.g_fincat_is_leaf;
59: ELSE

Line 56: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;

52:
53: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
54: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
55: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
56: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
57: ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
58: l_dim_flag := fii_gl_util_pkg.g_fincat_is_leaf;
59: ELSE
60: l_dim_flag := 'Y';

Line 57: ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN

53: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
54: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
55: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
56: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
57: ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
58: l_dim_flag := fii_gl_util_pkg.g_fincat_is_leaf;
59: ELSE
60: l_dim_flag := 'Y';
61: END IF;

Line 58: l_dim_flag := fii_gl_util_pkg.g_fincat_is_leaf;

54: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
55: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
56: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
57: ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
58: l_dim_flag := fii_gl_util_pkg.g_fincat_is_leaf;
59: ELSE
60: l_dim_flag := 'Y';
61: END IF;
62:

Line 63: IF fii_gl_util_pkg.g_fin_type = 'R' THEN

59: ELSE
60: l_dim_flag := 'Y';
61: END IF;
62:
63: IF fii_gl_util_pkg.g_fin_type = 'R' THEN
64: l_url := 'pFunctionName=FII_GL_REV_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
65: l_url2 := 'pFunctionName=FII_GL_REV_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&FII_DIM5=FII_MEASURE10&pParamIds=Y';
66: ELSIF fii_gl_util_pkg.g_fin_type = 'OE' THEN
67: l_url := 'pFunctionName=FII_GL_EXP_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';

Line 66: ELSIF fii_gl_util_pkg.g_fin_type = 'OE' THEN

62:
63: IF fii_gl_util_pkg.g_fin_type = 'R' THEN
64: l_url := 'pFunctionName=FII_GL_REV_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
65: l_url2 := 'pFunctionName=FII_GL_REV_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&FII_DIM5=FII_MEASURE10&pParamIds=Y';
66: ELSIF fii_gl_util_pkg.g_fin_type = 'OE' THEN
67: l_url := 'pFunctionName=FII_GL_EXP_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
68: l_url2 := 'pFunctionName=FII_GL_EXP_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&FII_DIM5=FII_MEASURE10&pParamIds=Y';
69: ELSIF fii_gl_util_pkg.g_fin_type = 'CGS' THEN
70: l_url := 'pFunctionName=FII_GL_COR_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';

Line 69: ELSIF fii_gl_util_pkg.g_fin_type = 'CGS' THEN

65: l_url2 := 'pFunctionName=FII_GL_REV_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&FII_DIM5=FII_MEASURE10&pParamIds=Y';
66: ELSIF fii_gl_util_pkg.g_fin_type = 'OE' THEN
67: l_url := 'pFunctionName=FII_GL_EXP_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
68: l_url2 := 'pFunctionName=FII_GL_EXP_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&FII_DIM5=FII_MEASURE10&pParamIds=Y';
69: ELSIF fii_gl_util_pkg.g_fin_type = 'CGS' THEN
70: l_url := 'pFunctionName=FII_GL_COR_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
71: l_url2 := 'pFunctionName=FII_GL_COR_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&FII_DIM5=FII_MEASURE10&pParamIds=Y';
72: END IF;
73:

Line 74: IF fii_gl_util_pkg.g_mgr_id = -99999 then

70: l_url := 'pFunctionName=FII_GL_COR_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
71: l_url2 := 'pFunctionName=FII_GL_COR_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&FII_DIM5=FII_MEASURE10&pParamIds=Y';
72: END IF;
73:
74: IF fii_gl_util_pkg.g_mgr_id = -99999 then
75: l_prior_or_budget :='
76: NULL FII_ATTRIBUTE12,
77: NULL FII_MEASURE3,';
78: sqlstmt := 'select NULL VIEWBY,

Line 97: select decode(:LOB_ID, f.viewby_id,decode('''||l_dim_flag||''',''Y'','||fii_gl_util_pkg.g_viewby_value||', '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG), '||fii_gl_util_pkg.g_viewby_value||') VIEWBY,

93: FROM dual where 1= 2';
94: ELSE
95:
96: sqlstmt := '
97: select decode(:LOB_ID, f.viewby_id,decode('''||l_dim_flag||''',''Y'','||fii_gl_util_pkg.g_viewby_value||', '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG), '||fii_gl_util_pkg.g_viewby_value||') VIEWBY,
98: to_number(NULL) FII_MEASURE1,
99: f.viewby_id FII_MEASURE10,
100: f.viewby_id VIEWBYID,
101: SUM(CY_ACTUAL) FII_MEASURE2,

Line 113: FROM '||fii_gl_util_pkg.g_viewby_from_clause||',

109: ABS(NULLIF(SUM(SUM(PY_SPER_END)) over(),0)) * 100 FII_ATTRIBUTE14,'||l_prior_or_budget||'
110: SUM(to_number(NULL)) FII_MEASURE12,
111: DECODE(:LOB_ID, f.viewby_id, '''', '''||l_url||''') FII_MEASURE14,
112: DECODE(:LOB_ID, f.viewby_id, '''', '''||l_url2||''') FII_MEASURE15
113: FROM '||fii_gl_util_pkg.g_viewby_from_clause||',
114: (select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
115: SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
116: then f.actual_g
117: else to_number(NULL) end) CY_ACTUAL,

Line 114: (select /+ leading(cal) / '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,

110: SUM(to_number(NULL)) FII_MEASURE12,
111: DECODE(:LOB_ID, f.viewby_id, '''', '''||l_url||''') FII_MEASURE14,
112: DECODE(:LOB_ID, f.viewby_id, '''', '''||l_url2||''') FII_MEASURE15
113: FROM '||fii_gl_util_pkg.g_viewby_from_clause||',
114: (select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
115: SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
116: then f.actual_g
117: else to_number(NULL) end) CY_ACTUAL,
118: SUM(case when bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id

Line 127: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||'

123: else to_number(NULL) end) CY_BUDGET,
124: to_number(NULL) PY_SPER_END,
125: to_number(NULL) PY_ACTUAL
126: FROM fii_time_rpt_struct cal
127: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
128: where cal.report_date = &BIS_CURRENT_ASOF_DATE
129: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||'
130: and f.gid = 0
131: and cal.period_type_id = f.period_type_id

Line 129: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||'

125: to_number(NULL) PY_ACTUAL
126: FROM fii_time_rpt_struct cal
127: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
128: where cal.report_date = &BIS_CURRENT_ASOF_DATE
129: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||'
130: and f.gid = 0
131: and cal.period_type_id = f.period_type_id
132: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
133: group by '||fii_gl_util_pkg.g_viewby_id||'

Line 133: group by '||fii_gl_util_pkg.g_viewby_id||'

129: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||'
130: and f.gid = 0
131: and cal.period_type_id = f.period_type_id
132: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
133: group by '||fii_gl_util_pkg.g_viewby_id||'
134: union all
135: select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
136: to_number(NULL) CY_ACTUAL,
137: to_number(NULL) CY_FORECAST,

Line 135: select /+ leading(cal) / '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,

131: and cal.period_type_id = f.period_type_id
132: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
133: group by '||fii_gl_util_pkg.g_viewby_id||'
134: union all
135: select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
136: to_number(NULL) CY_ACTUAL,
137: to_number(NULL) CY_FORECAST,
138: to_number(NULL) CY_BUDGET,
139: SUM(case when bitand(cal.record_type_id, :ENT_PERIOD_TYPE) = cal.record_type_id

Line 146: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||'

142: SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
143: then f.actual_g
144: else to_number(NULL) end) PY_ACTUAL
145: FROM fii_time_rpt_struct cal
146: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
147: where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
148: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join|| '
149: and f.gid = 0
150: and cal.period_type_id = f.period_type_id

Line 148: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join|| '

144: else to_number(NULL) end) PY_ACTUAL
145: FROM fii_time_rpt_struct cal
146: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
147: where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
148: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join|| '
149: and f.gid = 0
150: and cal.period_type_id = f.period_type_id
151: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
152: group by '||fii_gl_util_pkg.g_viewby_id||') f

Line 152: group by '||fii_gl_util_pkg.g_viewby_id||') f

148: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join|| '
149: and f.gid = 0
150: and cal.period_type_id = f.period_type_id
151: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
152: group by '||fii_gl_util_pkg.g_viewby_id||') f
153: where '||fii_gl_util_pkg.g_viewby_join||'
154: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
155: order by NVL(FII_MEASURE2, -9999999999) desc';
156: END IF;

Line 153: where '||fii_gl_util_pkg.g_viewby_join||'

149: and f.gid = 0
150: and cal.period_type_id = f.period_type_id
151: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
152: group by '||fii_gl_util_pkg.g_viewby_id||') f
153: where '||fii_gl_util_pkg.g_viewby_join||'
154: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
155: order by NVL(FII_MEASURE2, -9999999999) desc';
156: END IF;
157: return sqlstmt;

Line 154: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id

150: and cal.period_type_id = f.period_type_id
151: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
152: group by '||fii_gl_util_pkg.g_viewby_id||') f
153: where '||fii_gl_util_pkg.g_viewby_join||'
154: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
155: order by NVL(FII_MEASURE2, -9999999999) desc';
156: END IF;
157: return sqlstmt;
158:

Line 174: fii_gl_util_pkg.reset_globals;

170: l_url_summary VARCHAR2(300);
171: l_url_trend VARCHAR2(300);
172:
173: BEGIN
174: fii_gl_util_pkg.reset_globals;
175: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
176: fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';
177: fii_gl_util_pkg.g_fin_type := 'OE';
178: fii_gl_util_pkg.get_bitmasks;

Line 175: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);

171: l_url_trend VARCHAR2(300);
172:
173: BEGIN
174: fii_gl_util_pkg.reset_globals;
175: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
176: fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';
177: fii_gl_util_pkg.g_fin_type := 'OE';
178: fii_gl_util_pkg.get_bitmasks;
179: fii_gl_util_pkg.get_viewby_sql;

Line 176: fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';

172:
173: BEGIN
174: fii_gl_util_pkg.reset_globals;
175: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
176: fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';
177: fii_gl_util_pkg.g_fin_type := 'OE';
178: fii_gl_util_pkg.get_bitmasks;
179: fii_gl_util_pkg.get_viewby_sql;
180: fii_gl_util_pkg.get_mgr_pmv_sql;

Line 177: fii_gl_util_pkg.g_fin_type := 'OE';

173: BEGIN
174: fii_gl_util_pkg.reset_globals;
175: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
176: fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';
177: fii_gl_util_pkg.g_fin_type := 'OE';
178: fii_gl_util_pkg.get_bitmasks;
179: fii_gl_util_pkg.get_viewby_sql;
180: fii_gl_util_pkg.get_mgr_pmv_sql;
181: fii_gl_util_pkg.get_cat_pmv_sql;

Line 178: fii_gl_util_pkg.get_bitmasks;

174: fii_gl_util_pkg.reset_globals;
175: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
176: fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';
177: fii_gl_util_pkg.g_fin_type := 'OE';
178: fii_gl_util_pkg.get_bitmasks;
179: fii_gl_util_pkg.get_viewby_sql;
180: fii_gl_util_pkg.get_mgr_pmv_sql;
181: fii_gl_util_pkg.get_cat_pmv_sql;
182:

Line 179: fii_gl_util_pkg.get_viewby_sql;

175: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
176: fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';
177: fii_gl_util_pkg.g_fin_type := 'OE';
178: fii_gl_util_pkg.get_bitmasks;
179: fii_gl_util_pkg.get_viewby_sql;
180: fii_gl_util_pkg.get_mgr_pmv_sql;
181: fii_gl_util_pkg.get_cat_pmv_sql;
182:
183:

Line 180: fii_gl_util_pkg.get_mgr_pmv_sql;

176: fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';
177: fii_gl_util_pkg.g_fin_type := 'OE';
178: fii_gl_util_pkg.get_bitmasks;
179: fii_gl_util_pkg.get_viewby_sql;
180: fii_gl_util_pkg.get_mgr_pmv_sql;
181: fii_gl_util_pkg.get_cat_pmv_sql;
182:
183:
184: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN

Line 181: fii_gl_util_pkg.get_cat_pmv_sql;

177: fii_gl_util_pkg.g_fin_type := 'OE';
178: fii_gl_util_pkg.get_bitmasks;
179: fii_gl_util_pkg.get_viewby_sql;
180: fii_gl_util_pkg.get_mgr_pmv_sql;
181: fii_gl_util_pkg.get_cat_pmv_sql;
182:
183:
184: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
185: l_prior_or_budget :='

Line 184: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN

180: fii_gl_util_pkg.get_mgr_pmv_sql;
181: fii_gl_util_pkg.get_cat_pmv_sql;
182:
183:
184: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
185: l_prior_or_budget :='
186: (SUM(SUM(CY_ACTUAL)) over() - SUM(SUM(CY_BUDGET)) over()) /
187: ABS(NULLIF(SUM(SUM(CY_BUDGET)) over(),0)) * 100 FII_ATTRIBUTE11,
188: SUM(CY_BUDGET) FII_MEASURE3,';

Line 210: IF fii_gl_util_pkg.g_mgr_id = -99999 THEN

206: * FII_MEASURE2 = Current amounts, FII_MEASURE3 = Prior amounts *
207: * FII_MEASURE5 = Forecast amounts, FII_MEASURE7 = Budget amounts *
208: ******************************************************************/
209:
210: IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
211:
212: l_prior_or_budget :='NULL FII_ATTRIBUTE11,
213: NULL FII_MEASURE3, ';
214: sqlstmt := 'select NULL VIEWBY,

Line 236: DECODE(f.viewby_id2 , f.viewby_id, '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG, '||fii_gl_util_pkg.g_viewby_value||') FII_MEASURE1,

232: ELSE
233:
234: sqlstmt := 'select
235: cat_tl2.description VIEWBY,
236: DECODE(f.viewby_id2 , f.viewby_id, '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG, '||fii_gl_util_pkg.g_viewby_value||') FII_MEASURE1,
237: f.viewby_id FII_MEASURE9,
238: f.viewby_id2 FII_MEASURE14,
239: SUM(CY_ACTUAL) FII_MEASURE2,
240: SUM(CY_FORECAST) FII_MEASURE5,

Line 253: '||fii_gl_util_pkg.g_viewby_from_clause||',

249: DECODE( f.viewby_id2 , f.viewby_id, '''' , '''||l_url_summary||''' ) FII_MEASURE13,
250: DECODE( f.viewby_id2 , f.viewby_id, '''' , '''||l_url_trend||''' ) FII_MEASURE15
251: /* Disable drills on the category and XTD column when parent category is same as child category */
252: FROM
253: '||fii_gl_util_pkg.g_viewby_from_clause||',
254: fnd_flex_values_tl cat_tl2,
255: (select
256: '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
257: cat_hier.next_level_fin_cat_id VIEWBY_ID2,

Line 256: '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,

252: FROM
253: '||fii_gl_util_pkg.g_viewby_from_clause||',
254: fnd_flex_values_tl cat_tl2,
255: (select
256: '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
257: cat_hier.next_level_fin_cat_id VIEWBY_ID2,
258: SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
259: then f.actual_g
260: else to_number(NULL) end) CY_ACTUAL,

Line 271: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'

267: to_number(NULL) PY_SPER_END,
268: to_number(NULL) PY_ACTUAL
269: FROM fii_time_rpt_struct cal,
270: fii_fin_item_hierarchies cat_hier
271: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
272: where cal.report_date = &BIS_CURRENT_ASOF_DATE
273: and f.fin_category_id = cat_hier.child_fin_cat_id
274: and ( f.parent_fin_category_id = cat_hier.NEXT_LEVEL_FIN_CAT_ID or (cat_hier.next_level_is_leaf = ''Y''))
275: and cat_hier.child_level <= 2 + cat_hier.parent_level

Line 279: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join2||'

275: and cat_hier.child_level <= 2 + cat_hier.parent_level
276: -- Modified join to fix bug 3562244. This join will let us pick up budgets and actuals that might
277: -- be loaded at summary nodes
278: and cal.time_id = f.time_id
279: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join2||'
280: and cal.period_type_id = f.period_type_id
281: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
282: group by cat_hier.next_level_fin_cat_id, '||fii_gl_util_pkg.g_viewby_id||'
283: union all

Line 282: group by cat_hier.next_level_fin_cat_id, '||fii_gl_util_pkg.g_viewby_id||'

278: and cal.time_id = f.time_id
279: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join2||'
280: and cal.period_type_id = f.period_type_id
281: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
282: group by cat_hier.next_level_fin_cat_id, '||fii_gl_util_pkg.g_viewby_id||'
283: union all
284: select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
285: cat_hier.next_level_fin_cat_id VIEWBY_ID2,
286: to_number(NULL) CY_ACTUAL,

Line 284: select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,

280: and cal.period_type_id = f.period_type_id
281: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
282: group by cat_hier.next_level_fin_cat_id, '||fii_gl_util_pkg.g_viewby_id||'
283: union all
284: select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
285: cat_hier.next_level_fin_cat_id VIEWBY_ID2,
286: to_number(NULL) CY_ACTUAL,
287: to_number(NULL) CY_FORECAST,
288: to_number(NULL) CY_BUDGET,

Line 297: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'

293: then f.actual_g
294: else to_number(NULL) end) PY_ACTUAL
295: FROM fii_time_rpt_struct cal,
296: fii_fin_item_hierarchies cat_hier
297: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
298: where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
299: and f.fin_category_id = cat_hier.child_fin_cat_id
300: and ( f.parent_fin_category_id = cat_hier.NEXT_LEVEL_FIN_CAT_ID or (cat_hier.next_level_is_leaf = ''Y''))
301: and cat_hier.child_level <= 2 + cat_hier.parent_level

Line 305: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join2|| '

301: and cat_hier.child_level <= 2 + cat_hier.parent_level
302: -- Modified join to fix bug 3562244. This join will let us pick up budgets and actuals that might
303: -- be loaded at summary nodes
304: and cal.time_id = f.time_id
305: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join2|| '
306: and cal.period_type_id = f.period_type_id
307: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
308: group by cat_hier.next_level_fin_cat_id, '||fii_gl_util_pkg.g_viewby_id||') f
309: where '||fii_gl_util_pkg.g_viewby_join||'

Line 308: group by cat_hier.next_level_fin_cat_id, '||fii_gl_util_pkg.g_viewby_id||') f

304: and cal.time_id = f.time_id
305: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join2|| '
306: and cal.period_type_id = f.period_type_id
307: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
308: group by cat_hier.next_level_fin_cat_id, '||fii_gl_util_pkg.g_viewby_id||') f
309: where '||fii_gl_util_pkg.g_viewby_join||'
310: and cat_tl2.flex_value_id = f.viewby_id2
311: and cat_tl2.language = userenv(''LANG'')
312: group by cat_tl2.description, '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id, f.viewby_id2

Line 309: where '||fii_gl_util_pkg.g_viewby_join||'

305: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join2|| '
306: and cal.period_type_id = f.period_type_id
307: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
308: group by cat_hier.next_level_fin_cat_id, '||fii_gl_util_pkg.g_viewby_id||') f
309: where '||fii_gl_util_pkg.g_viewby_join||'
310: and cat_tl2.flex_value_id = f.viewby_id2
311: and cat_tl2.language = userenv(''LANG'')
312: group by cat_tl2.description, '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id, f.viewby_id2
313: order by cat_tl2.description, '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';

Line 312: group by cat_tl2.description, '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id, f.viewby_id2

308: group by cat_hier.next_level_fin_cat_id, '||fii_gl_util_pkg.g_viewby_id||') f
309: where '||fii_gl_util_pkg.g_viewby_join||'
310: and cat_tl2.flex_value_id = f.viewby_id2
311: and cat_tl2.language = userenv(''LANG'')
312: group by cat_tl2.description, '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id, f.viewby_id2
313: order by cat_tl2.description, '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';
314:
315: END IF;
316:

Line 313: order by cat_tl2.description, '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';

309: where '||fii_gl_util_pkg.g_viewby_join||'
310: and cat_tl2.flex_value_id = f.viewby_id2
311: and cat_tl2.language = userenv(''LANG'')
312: group by cat_tl2.description, '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id, f.viewby_id2
313: order by cat_tl2.description, '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';
314:
315: END IF;
316:
317: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_by_cat_sql, exp_by_cat_output);

Line 317: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_by_cat_sql, exp_by_cat_output);

313: order by cat_tl2.description, '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';
314:
315: END IF;
316:
317: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_by_cat_sql, exp_by_cat_output);
318:
319:
320: END get_exp_by_cat;
321:

Line 345: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_cc_sql, exp_cc_output);

341: l_fin_category := 'OE';
342:
343: sqlstmt := get_revexp_cc(p_page_parameter_tbl, l_fin_category );
344:
345: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_cc_sql, exp_cc_output);
346:
347: END get_exp_cc;
348:
349: PROCEDURE get_rev_cc (

Line 360: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, rev_cc_sql, rev_cc_output);

356: l_fin_category := 'R';
357:
358: sqlstmt := get_revexp_cc(p_page_parameter_tbl, l_fin_category );
359:
360: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, rev_cc_sql, rev_cc_output);
361:
362:
363: END get_rev_cc;
364:

Line 376: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, cgs_cc_sql, cgs_cc_output);

372: l_fin_category := 'CGS';
373:
374: sqlstmt := get_revexp_cc(p_page_parameter_tbl, l_fin_category );
375:
376: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, cgs_cc_sql, cgs_cc_output);
377:
378: END get_cogs_cc;
379:
380:

Line 404: fii_gl_util_pkg.reset_globals;

400: l_url VARCHAR2(300) := NULL;
401: l_dim_flag VARCHAR2(1);
402:
403: BEGIN
404: fii_gl_util_pkg.reset_globals;
405: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
406: fii_gl_util_pkg.get_bitmasks;
407: fii_gl_util_pkg.get_viewby_sql;
408: fii_gl_util_pkg.get_lob_pmv_sql;

Line 405: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);

401: l_dim_flag VARCHAR2(1);
402:
403: BEGIN
404: fii_gl_util_pkg.reset_globals;
405: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
406: fii_gl_util_pkg.get_bitmasks;
407: fii_gl_util_pkg.get_viewby_sql;
408: fii_gl_util_pkg.get_lob_pmv_sql;
409: fii_gl_util_pkg.get_mgr_pmv_sql;

Line 406: fii_gl_util_pkg.get_bitmasks;

402:
403: BEGIN
404: fii_gl_util_pkg.reset_globals;
405: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
406: fii_gl_util_pkg.get_bitmasks;
407: fii_gl_util_pkg.get_viewby_sql;
408: fii_gl_util_pkg.get_lob_pmv_sql;
409: fii_gl_util_pkg.get_mgr_pmv_sql;
410: fii_gl_util_pkg.get_ccc_pmv_sql;

Line 407: fii_gl_util_pkg.get_viewby_sql;

403: BEGIN
404: fii_gl_util_pkg.reset_globals;
405: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
406: fii_gl_util_pkg.get_bitmasks;
407: fii_gl_util_pkg.get_viewby_sql;
408: fii_gl_util_pkg.get_lob_pmv_sql;
409: fii_gl_util_pkg.get_mgr_pmv_sql;
410: fii_gl_util_pkg.get_ccc_pmv_sql;
411:

Line 408: fii_gl_util_pkg.get_lob_pmv_sql;

404: fii_gl_util_pkg.reset_globals;
405: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
406: fii_gl_util_pkg.get_bitmasks;
407: fii_gl_util_pkg.get_viewby_sql;
408: fii_gl_util_pkg.get_lob_pmv_sql;
409: fii_gl_util_pkg.get_mgr_pmv_sql;
410: fii_gl_util_pkg.get_ccc_pmv_sql;
411:
412: IF p_opera_marg = 'Y' THEN

Line 409: fii_gl_util_pkg.get_mgr_pmv_sql;

405: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
406: fii_gl_util_pkg.get_bitmasks;
407: fii_gl_util_pkg.get_viewby_sql;
408: fii_gl_util_pkg.get_lob_pmv_sql;
409: fii_gl_util_pkg.get_mgr_pmv_sql;
410: fii_gl_util_pkg.get_ccc_pmv_sql;
411:
412: IF p_opera_marg = 'Y' THEN
413: fii_gl_util_pkg.g_fin_type := 'OM';

Line 410: fii_gl_util_pkg.get_ccc_pmv_sql;

406: fii_gl_util_pkg.get_bitmasks;
407: fii_gl_util_pkg.get_viewby_sql;
408: fii_gl_util_pkg.get_lob_pmv_sql;
409: fii_gl_util_pkg.get_mgr_pmv_sql;
410: fii_gl_util_pkg.get_ccc_pmv_sql;
411:
412: IF p_opera_marg = 'Y' THEN
413: fii_gl_util_pkg.g_fin_type := 'OM';
414: l_url := 'pFunctionName=FII_GL_OPS_LOB_MGR1&FII_DIM8=FII_MEASURE6&VIEW_BY=VIEW_BY&pParamIds=Y';

Line 413: fii_gl_util_pkg.g_fin_type := 'OM';

409: fii_gl_util_pkg.get_mgr_pmv_sql;
410: fii_gl_util_pkg.get_ccc_pmv_sql;
411:
412: IF p_opera_marg = 'Y' THEN
413: fii_gl_util_pkg.g_fin_type := 'OM';
414: l_url := 'pFunctionName=FII_GL_OPS_LOB_MGR1&FII_DIM8=FII_MEASURE6&VIEW_BY=VIEW_BY&pParamIds=Y';
415: ELSE fii_gl_util_pkg.g_fin_type := 'GM';
416: l_url := 'pFunctionName=FII_GL_MAR_LOB_MGR1&FII_DIM8=FII_MEASURE6&VIEW_BY=VIEW_BY&pParamIds=Y';
417: END IF;

Line 415: ELSE fii_gl_util_pkg.g_fin_type := 'GM';

411:
412: IF p_opera_marg = 'Y' THEN
413: fii_gl_util_pkg.g_fin_type := 'OM';
414: l_url := 'pFunctionName=FII_GL_OPS_LOB_MGR1&FII_DIM8=FII_MEASURE6&VIEW_BY=VIEW_BY&pParamIds=Y';
415: ELSE fii_gl_util_pkg.g_fin_type := 'GM';
416: l_url := 'pFunctionName=FII_GL_MAR_LOB_MGR1&FII_DIM8=FII_MEASURE6&VIEW_BY=VIEW_BY&pParamIds=Y';
417: END IF;
418:
419: fii_gl_util_pkg.get_cat_pmv_sql;

Line 419: fii_gl_util_pkg.get_cat_pmv_sql;

415: ELSE fii_gl_util_pkg.g_fin_type := 'GM';
416: l_url := 'pFunctionName=FII_GL_MAR_LOB_MGR1&FII_DIM8=FII_MEASURE6&VIEW_BY=VIEW_BY&pParamIds=Y';
417: END IF;
418:
419: fii_gl_util_pkg.get_cat_pmv_sql;
420:
421: -- since there is no weekly forecast, NULL will be returned if week period type is selected
422:
423: IF p_opera_marg = 'Y' THEN

Line 440: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN

436: NVL(SUM(SUM(CY_ACT_REV)) over(), 0) - '||l_subtractor2||' FII_ATTRIBUTE11,
437: (NVL(SUM(SUM(CY_ACT_REV)) over(), 0) - '||l_subtractor2||') /
438: ABS(NULLIF(SUM(SUM(CY_ACT_REV)) over(),0)) * 100 FII_ATTRIBUTE12,';
439:
440: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
441:
442: l_prior := 'CY_BUD';
443: l_record_type := ':BUDGET_PERIOD_TYPE';
444: l_amt := 'actual_g';

Line 472: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN

468: l_subtractor4 := ' NVL(SUM('||l_prior||'_CGS), 0) ';
469:
470: END IF;
471:
472: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
473: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
474: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
475: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
476: ELSE

Line 473: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;

469:
470: END IF;
471:
472: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
473: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
474: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
475: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
476: ELSE
477: l_dim_flag := 'Y';

Line 474: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN

470: END IF;
471:
472: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
473: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
474: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
475: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
476: ELSE
477: l_dim_flag := 'Y';
478: END IF;

Line 475: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;

471:
472: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
473: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
474: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
475: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
476: ELSE
477: l_dim_flag := 'Y';
478: END IF;
479:

Line 491: IF fii_gl_util_pkg.g_mgr_id = -99999 THEN

487: -- FII_MEASURE1 = LOB name
488: -- FII_CAL1 = Prior Total Revenue amounts
489: -- FII_CAL2 = Prior Total COR amounts
490: -- ----------------------------------------------------------------
491: IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
492:
493: cy_prior_exp_select := 'NULL FII_ATTRIBUTE4,';
494: cy_act_exp_select := ' NULL FII_ATTRIBUTE3,';
495: sqlstmt1 := ' NULL FII_MEASURE2,

Line 521: select decode(:LOB_ID, f.viewby_id,decode('''||l_dim_flag||''',''Y'','||fii_gl_util_pkg.g_viewby_value||', '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG), '||fii_gl_util_pkg.g_viewby_value||') VIEWBY,

517: WHERE 1=2 ';
518: ELSE
519:
520: sqlstmt := '
521: select decode(:LOB_ID, f.viewby_id,decode('''||l_dim_flag||''',''Y'','||fii_gl_util_pkg.g_viewby_value||', '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG), '||fii_gl_util_pkg.g_viewby_value||') VIEWBY,
522: to_number(NULL) FII_MEASURE1,
523: f.viewby_id FII_MEASURE6,
524: '||sqlstmt1||'
525: to_number(NULL) FII_CAL1,

Line 541: FROM '||fii_gl_util_pkg.g_viewby_from_clause||',

537: (case when NVL(abs((NVL(SUM(CY_ACT_REV), 0) - '||l_subtractor3||')/
538: ABS(NULLIF(SUM(CY_ACT_REV), 0)) * 100), 1000) > 999.9 THEN NULL WHEN NVL(abs((NVL(SUM('||l_prior||'_REV), 0) - '||l_subtractor4||')/
539: ABS(NULLIF(SUM('||l_prior||'_REV), 0)) * 100), 1000) > 999.9 THEN NULL ELSE 0 END) FII_MEASURE13,
540: DECODE(NVL(:LOB_ID,-9999), f.viewby_id, '''', '''||l_url||''') FII_MEASURE14
541: FROM '||fii_gl_util_pkg.g_viewby_from_clause||',
542: (select /*+ leading(cal) index(f FII_GL_MGMT_SUM_MV_N1) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
543: SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
544: and assgns.fin_cat_type_code = ''R''
545: then f.actual_g

Line 542: (select /+ leading(cal) index(f FII_GL_MGMT_SUM_MV_N1) / '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,

538: ABS(NULLIF(SUM(CY_ACT_REV), 0)) * 100), 1000) > 999.9 THEN NULL WHEN NVL(abs((NVL(SUM('||l_prior||'_REV), 0) - '||l_subtractor4||')/
539: ABS(NULLIF(SUM('||l_prior||'_REV), 0)) * 100), 1000) > 999.9 THEN NULL ELSE 0 END) FII_MEASURE13,
540: DECODE(NVL(:LOB_ID,-9999), f.viewby_id, '''', '''||l_url||''') FII_MEASURE14
541: FROM '||fii_gl_util_pkg.g_viewby_from_clause||',
542: (select /*+ leading(cal) index(f FII_GL_MGMT_SUM_MV_N1) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
543: SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
544: and assgns.fin_cat_type_code = ''R''
545: then f.actual_g
546: else to_number(NULL) end) CY_ACT_REV,

Line 575: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'

571: to_number(NULL) PYPER_ACT_CGS
572:
573: FROM fii_time_rpt_struct cal,
574: fii_fin_cat_type_assgns assgns
575: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
576: where assgns.fin_category_id = f.fin_category_id
577: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||'
578: and cal.time_id = f.time_id
579: and cal.period_type_id = f.period_type_id

Line 577: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||'

573: FROM fii_time_rpt_struct cal,
574: fii_fin_cat_type_assgns assgns
575: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
576: where assgns.fin_category_id = f.fin_category_id
577: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||'
578: and cal.time_id = f.time_id
579: and cal.period_type_id = f.period_type_id
580: and cal.report_date = &BIS_CURRENT_ASOF_DATE
581: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id

Line 582: group by '||fii_gl_util_pkg.g_viewby_id||'

578: and cal.time_id = f.time_id
579: and cal.period_type_id = f.period_type_id
580: and cal.report_date = &BIS_CURRENT_ASOF_DATE
581: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
582: group by '||fii_gl_util_pkg.g_viewby_id||'
583: union all
584: select /*+ leading(cal) index(f FII_GL_MGMT_SUM_MV_N1) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
585: to_number(NULL) CY_ACT_REV,
586: to_number(NULL) CY_ACT_EXP,

Line 584: select /+ leading(cal) index(f FII_GL_MGMT_SUM_MV_N1) / '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,

580: and cal.report_date = &BIS_CURRENT_ASOF_DATE
581: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
582: group by '||fii_gl_util_pkg.g_viewby_id||'
583: union all
584: select /*+ leading(cal) index(f FII_GL_MGMT_SUM_MV_N1) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
585: to_number(NULL) CY_ACT_REV,
586: to_number(NULL) CY_ACT_EXP,
587: to_number(NULL) CY_ACT_CGS,
588: to_number(NULL) CY_BUD_REV,

Line 608: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'

604: to_number(NULL) PYPER_ACT_CGS
605:
606: FROM fii_time_rpt_struct cal,
607: fii_fin_cat_type_assgns assgns
608: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
609: where assgns.fin_category_id = f.fin_category_id
610: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||'
611: and cal.time_id = f.time_id
612: and cal.period_type_id = f.period_type_id

Line 610: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||'

606: FROM fii_time_rpt_struct cal,
607: fii_fin_cat_type_assgns assgns
608: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
609: where assgns.fin_category_id = f.fin_category_id
610: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||'
611: and cal.time_id = f.time_id
612: and cal.period_type_id = f.period_type_id
613: and cal.report_date = &BIS_PREVIOUS_ASOF_DATE
614: and bitand(cal.record_type_id, :ACT_WHERE_PERIOD_TYPE) = cal.record_type_id

Line 615: group by '||fii_gl_util_pkg.g_viewby_id||') f

611: and cal.time_id = f.time_id
612: and cal.period_type_id = f.period_type_id
613: and cal.report_date = &BIS_PREVIOUS_ASOF_DATE
614: and bitand(cal.record_type_id, :ACT_WHERE_PERIOD_TYPE) = cal.record_type_id
615: group by '||fii_gl_util_pkg.g_viewby_id||') f
616: where '||fii_gl_util_pkg.g_viewby_join||'
617: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
618: order by NVL(FII_MEASURE11, -9999999999) desc';
619:

Line 616: where '||fii_gl_util_pkg.g_viewby_join||'

612: and cal.period_type_id = f.period_type_id
613: and cal.report_date = &BIS_PREVIOUS_ASOF_DATE
614: and bitand(cal.record_type_id, :ACT_WHERE_PERIOD_TYPE) = cal.record_type_id
615: group by '||fii_gl_util_pkg.g_viewby_id||') f
616: where '||fii_gl_util_pkg.g_viewby_join||'
617: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
618: order by NVL(FII_MEASURE11, -9999999999) desc';
619:
620: END IF;

Line 617: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id

613: and cal.report_date = &BIS_PREVIOUS_ASOF_DATE
614: and bitand(cal.record_type_id, :ACT_WHERE_PERIOD_TYPE) = cal.record_type_id
615: group by '||fii_gl_util_pkg.g_viewby_id||') f
616: where '||fii_gl_util_pkg.g_viewby_join||'
617: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
618: order by NVL(FII_MEASURE11, -9999999999) desc';
619:
620: END IF;
621:

Line 622: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, cont_marg_sql, cont_marg_output);

618: order by NVL(FII_MEASURE11, -9999999999) desc';
619:
620: END IF;
621:
622: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, cont_marg_sql, cont_marg_output);
623:
624: END get_cont_marg;
625:
626: --* Procedure added by Ilavenil.