[Home] [Help]
37: l_cat_from_clause VARCHAR2(200);
38:
39: BEGIN
40:
41: fii_gl_util_pkg.g_fin_type := 'OE';
42: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
43: fii_gl_util_pkg.get_bitmasks;
44: fii_gl_util_pkg.get_mgr_pmv_sql;
45: fii_gl_util_pkg.get_cat_pmv_sql;
38:
39: BEGIN
40:
41: fii_gl_util_pkg.g_fin_type := 'OE';
42: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
43: fii_gl_util_pkg.get_bitmasks;
44: fii_gl_util_pkg.get_mgr_pmv_sql;
45: fii_gl_util_pkg.get_cat_pmv_sql;
46:
39: BEGIN
40:
41: fii_gl_util_pkg.g_fin_type := 'OE';
42: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
43: fii_gl_util_pkg.get_bitmasks;
44: fii_gl_util_pkg.get_mgr_pmv_sql;
45: fii_gl_util_pkg.get_cat_pmv_sql;
46:
47:
40:
41: fii_gl_util_pkg.g_fin_type := 'OE';
42: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
43: fii_gl_util_pkg.get_bitmasks;
44: fii_gl_util_pkg.get_mgr_pmv_sql;
45: fii_gl_util_pkg.get_cat_pmv_sql;
46:
47:
48: /******************************************************************
41: fii_gl_util_pkg.g_fin_type := 'OE';
42: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
43: fii_gl_util_pkg.get_bitmasks;
44: fii_gl_util_pkg.get_mgr_pmv_sql;
45: fii_gl_util_pkg.get_cat_pmv_sql;
46:
47:
48: /******************************************************************
49: * FII_MEASURE2 = Current amounts, FII_MEASURE3 = Prior amounts *
55: sum(PY_ACTUAL) FII_MEASURE3,
56: sum(CY_FORECAST) FII_MEASURE5,
57: sum(CY_BUDGET) FII_MEASURE7,
58: FROM
59: (select '||fii_gl_util_pkg.g_viewby_id||' ID,
60: '||fii_gl_util_pkg.g_viewby_value||' value,
61: sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id then f.actual_g else to_number(NULL) end) CY_ACTUAL,
62: sum(case when bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id then f.forecast_g else to_number(NULL) end) CY_FORECAST,
63: sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id then f.budget_g else to_number(NULL) end) CY_BUDGET,
56: sum(CY_FORECAST) FII_MEASURE5,
57: sum(CY_BUDGET) FII_MEASURE7,
58: FROM
59: (select '||fii_gl_util_pkg.g_viewby_id||' ID,
60: '||fii_gl_util_pkg.g_viewby_value||' value,
61: sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id then f.actual_g else to_number(NULL) end) CY_ACTUAL,
62: sum(case when bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id then f.forecast_g else to_number(NULL) end) CY_FORECAST,
63: sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id then f.budget_g else to_number(NULL) end) CY_BUDGET,
64: to_number(NULL) PY_ACTUAL
62: sum(case when bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id then f.forecast_g else to_number(NULL) end) CY_FORECAST,
63: sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id then f.budget_g else to_number(NULL) end) CY_BUDGET,
64: to_number(NULL) PY_ACTUAL
65: from fii_time_rpt_stuct cal
66: '||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_mgr_from_clause||'
67: where cal.report_date = &BIS_CURRENT_ASOF_DATE
68: and cal.record_type_id = bitand(cal.record_type_id, :WHERE_PERIOD_TYPE)
69: and f.time_id = cal.time_id
70: and f.period_type_id = cal.period_type_id
67: where cal.report_date = &BIS_CURRENT_ASOF_DATE
68: and cal.record_type_id = bitand(cal.record_type_id, :WHERE_PERIOD_TYPE)
69: and f.time_id = cal.time_id
70: and f.period_type_id = cal.period_type_id
71: '||fii_gl_util_pkg.g_gid||'
72: '||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_mgr_join||'
73: group by '||fii_gl_util_pkg.g_cat_viewby_id||'
74: union all
75: select '||fii_gl_util_pkg.g_cat_viewby_id||' ID,
68: and cal.record_type_id = bitand(cal.record_type_id, :WHERE_PERIOD_TYPE)
69: and f.time_id = cal.time_id
70: and f.period_type_id = cal.period_type_id
71: '||fii_gl_util_pkg.g_gid||'
72: '||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_mgr_join||'
73: group by '||fii_gl_util_pkg.g_cat_viewby_id||'
74: union all
75: select '||fii_gl_util_pkg.g_cat_viewby_id||' ID,
76: '||fii_gl_util_pkg.g_viewby_value||' VALUE,
69: and f.time_id = cal.time_id
70: and f.period_type_id = cal.period_type_id
71: '||fii_gl_util_pkg.g_gid||'
72: '||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_mgr_join||'
73: group by '||fii_gl_util_pkg.g_cat_viewby_id||'
74: union all
75: select '||fii_gl_util_pkg.g_cat_viewby_id||' ID,
76: '||fii_gl_util_pkg.g_viewby_value||' VALUE,
77: to_number(NULL) CY_ACTUAL,
71: '||fii_gl_util_pkg.g_gid||'
72: '||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_mgr_join||'
73: group by '||fii_gl_util_pkg.g_cat_viewby_id||'
74: union all
75: select '||fii_gl_util_pkg.g_cat_viewby_id||' ID,
76: '||fii_gl_util_pkg.g_viewby_value||' VALUE,
77: to_number(NULL) CY_ACTUAL,
78: to_number(NULL) CY_FORECAST,
79: to_number(NULL) CY_BUDGET,
72: '||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_mgr_join||'
73: group by '||fii_gl_util_pkg.g_cat_viewby_id||'
74: union all
75: select '||fii_gl_util_pkg.g_cat_viewby_id||' ID,
76: '||fii_gl_util_pkg.g_viewby_value||' VALUE,
77: to_number(NULL) CY_ACTUAL,
78: to_number(NULL) CY_FORECAST,
79: to_number(NULL) CY_BUDGET,
80: sum(f.actual_g) PY_ACTUAL
78: to_number(NULL) CY_FORECAST,
79: to_number(NULL) CY_BUDGET,
80: sum(f.actual_g) PY_ACTUAL
81: from fii_time_rpt_stuct cal
82: '||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_mgr_from_clause||'
83: where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
84: and cal.record_type_id = bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE)
85: and f.time_id = cal.time_id
86: and f.period_type_id = cal.period_type_id
83: where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
84: and cal.record_type_id = bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE)
85: and f.time_id = cal.time_id
86: and f.period_type_id = cal.period_type_id
87: '||fii_gl_util_pkg.g_gid||'
88: '||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_mgr_join||'
89: group by '||fii_gl_util_pkg.g_viewby_id||' )
90: group by VALUE
91: order by FII_MEASURE2 desc';
84: and cal.record_type_id = bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE)
85: and f.time_id = cal.time_id
86: and f.period_type_id = cal.period_type_id
87: '||fii_gl_util_pkg.g_gid||'
88: '||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_mgr_join||'
89: group by '||fii_gl_util_pkg.g_viewby_id||' )
90: group by VALUE
91: order by FII_MEASURE2 desc';
92:
85: and f.time_id = cal.time_id
86: and f.period_type_id = cal.period_type_id
87: '||fii_gl_util_pkg.g_gid||'
88: '||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_mgr_join||'
89: group by '||fii_gl_util_pkg.g_viewby_id||' )
90: group by VALUE
91: order by FII_MEASURE2 desc';
92:
93: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_by_cat_sql, exp_by_cat_output);
89: group by '||fii_gl_util_pkg.g_viewby_id||' )
90: group by VALUE
91: order by FII_MEASURE2 desc';
92:
93: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_by_cat_sql, exp_by_cat_output);
94:
95: END get_exp_by_cat;
96:
97: PROCEDURE get_te_cc (
99: te_cc_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
100:
101: sqlstmt VARCHAR2(32000);
102: BEGIN
103: fii_gl_util_pkg.reset_globals;
104: fii_gl_util_pkg.g_fin_type := 'TE';
105:
106: sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
107: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, te_cc_sql, te_cc_output);
100:
101: sqlstmt VARCHAR2(32000);
102: BEGIN
103: fii_gl_util_pkg.reset_globals;
104: fii_gl_util_pkg.g_fin_type := 'TE';
105:
106: sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
107: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, te_cc_sql, te_cc_output);
108: END get_te_cc;
103: fii_gl_util_pkg.reset_globals;
104: fii_gl_util_pkg.g_fin_type := 'TE';
105:
106: sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
107: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, te_cc_sql, te_cc_output);
108: END get_te_cc;
109:
110: PROCEDURE get_cogs_cc (
111: p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, cogs_cc_sql out NOCOPY VARCHAR2,
112: cogs_cc_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
113:
114: sqlstmt VARCHAR2(32000);
115: BEGIN
116: fii_gl_util_pkg.reset_globals;
117: fii_gl_util_pkg.g_fin_type := 'CGS';
118:
119: sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
120: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, cogs_cc_sql, cogs_cc_output);
113:
114: sqlstmt VARCHAR2(32000);
115: BEGIN
116: fii_gl_util_pkg.reset_globals;
117: fii_gl_util_pkg.g_fin_type := 'CGS';
118:
119: sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
120: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, cogs_cc_sql, cogs_cc_output);
121: END get_cogs_cc;
116: fii_gl_util_pkg.reset_globals;
117: fii_gl_util_pkg.g_fin_type := 'CGS';
118:
119: sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
120: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, cogs_cc_sql, cogs_cc_output);
121: END get_cogs_cc;
122:
123: PROCEDURE get_exp_cc (
124: p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, exp_cc_sql out NOCOPY VARCHAR2,
124: p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, exp_cc_sql out NOCOPY VARCHAR2,
125: exp_cc_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
126: sqlstmt VARCHAR2(32000);
127: BEGIN
128: fii_gl_util_pkg.reset_globals;
129: fii_gl_util_pkg.g_fin_type := 'OE';
130:
131: sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
132: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_cc_sql, exp_cc_output);
125: exp_cc_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
126: sqlstmt VARCHAR2(32000);
127: BEGIN
128: fii_gl_util_pkg.reset_globals;
129: fii_gl_util_pkg.g_fin_type := 'OE';
130:
131: sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
132: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_cc_sql, exp_cc_output);
133: END get_exp_cc;
128: fii_gl_util_pkg.reset_globals;
129: fii_gl_util_pkg.g_fin_type := 'OE';
130:
131: sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
132: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_cc_sql, exp_cc_output);
133: END get_exp_cc;
134:
135: PROCEDURE get_rev_cc (
136: p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, rev_cc_sql out NOCOPY VARCHAR2,
136: p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, rev_cc_sql out NOCOPY VARCHAR2,
137: rev_cc_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
138: sqlstmt VARCHAR2(32000);
139: BEGIN
140: fii_gl_util_pkg.reset_globals;
141: fii_gl_util_pkg.g_fin_type := 'R';
142: sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
143:
144: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, rev_cc_sql, rev_cc_output);
137: rev_cc_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
138: sqlstmt VARCHAR2(32000);
139: BEGIN
140: fii_gl_util_pkg.reset_globals;
141: fii_gl_util_pkg.g_fin_type := 'R';
142: sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
143:
144: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, rev_cc_sql, rev_cc_output);
145:
140: fii_gl_util_pkg.reset_globals;
141: fii_gl_util_pkg.g_fin_type := 'R';
142: sqlstmt := fii_gl_cost_center_pkg.get_revexp_cc(p_page_parameter_tbl);
143:
144: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, rev_cc_sql, rev_cc_output);
145:
146: END get_rev_cc;
147:
148: FUNCTION get_revexp_cc (
164:
165:
166: BEGIN
167:
168: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
169: fii_gl_util_pkg.get_bitmasks;
170:
171:
172: IF fii_gl_util_pkg.g_fin_type = 'R' THEN
165:
166: BEGIN
167:
168: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
169: fii_gl_util_pkg.get_bitmasks;
170:
171:
172: IF fii_gl_util_pkg.g_fin_type = 'R' THEN
173:
168: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
169: fii_gl_util_pkg.get_bitmasks;
170:
171:
172: IF fii_gl_util_pkg.g_fin_type = 'R' THEN
173:
174: gt_cy_act := 'FII_MEASURE11';
175: gt_cy_fore := 'FII_CAL1';
176: gt_fore_act := 'FII_ATTRIBUTE13';
175: gt_cy_fore := 'FII_CAL1';
176: gt_fore_act := 'FII_ATTRIBUTE13';
177: gt_fore_bud := 'FII_ATTRIBUTE14';
178: gt_fore_py := 'FII_ATTRIBUTE12';
179: IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
180: gt_py_act := 'NULL FII_MEASURE12,';
181: gt_cy_bud := 'NULL FII_CAL2,';
182: ELSE
183: gt_py_act := 'sum(sum(PY_ACTUAL)) over() FII_MEASURE12,';
185: END IF;
186:
187: l_url := 'pFunctionName=FII_GL_REV_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
188: l_url2 := 'pFunctionName=FII_GL_REV_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
189: ELSIF (fii_gl_util_pkg.g_fin_type = 'OE' OR fii_gl_util_pkg.g_fin_type = 'TE') THEN
190: gt_cy_act := 'FII_ATTRIBUTE5';
191: gt_cy_fore := 'FII_ATTRIBUTE7';
192: gt_fore_act := 'FII_ATTRIBUTE1';
193: gt_fore_bud := 'FII_ATTRIBUTE2';
191: gt_cy_fore := 'FII_ATTRIBUTE7';
192: gt_fore_act := 'FII_ATTRIBUTE1';
193: gt_fore_bud := 'FII_ATTRIBUTE2';
194: gt_fore_py := 'FII_ATTRIBUTE12';
195: IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
196: gt_py_act := 'NULL FII_ATTRIBUTE6,';
197: gt_cy_bud := 'NULL FII_ATTRIBUTE8,';
198: ELSE
199: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
195: IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
196: gt_py_act := 'NULL FII_ATTRIBUTE6,';
197: gt_cy_bud := 'NULL FII_ATTRIBUTE8,';
198: ELSE
199: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
200: gt_py_act := 'sum(sum(CY_BUDGET)) over() FII_ATTRIBUTE6,';
201: ELSE
202: gt_py_act := 'sum(sum(PY_ACTUAL)) over() FII_ATTRIBUTE6,';
203: END IF;
203: END IF;
204: gt_cy_bud := 'sum(sum(CY_BUDGET)) over() FII_ATTRIBUTE8,';
205: END IF;
206:
207: IF fii_gl_util_pkg.g_fin_type = 'OE' THEN
208: l_url := 'pFunctionName=FII_GL_EXP_LOBMGRCC1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
209: l_url2 := 'pFunctionName=FII_GL_EXP_PER_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
210: ELSE
211: l_url := 'pFunctionName=FII_GL_TE_EXP_LOB1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
210: ELSE
211: l_url := 'pFunctionName=FII_GL_TE_EXP_LOB1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
212: l_url2 := 'pFunctionName=FII_GL_TE_EXP_TREND&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
213: END IF;
214: ELSIF fii_gl_util_pkg.g_fin_type = 'CGS' THEN
215: gt_cy_act := 'FII_MEASURE11';
216: gt_cy_fore := 'FII_CAL1';
217: gt_fore_act := 'FII_ATTRIBUTE13';
218: gt_fore_bud := 'FII_ATTRIBUTE14';
216: gt_cy_fore := 'FII_CAL1';
217: gt_fore_act := 'FII_ATTRIBUTE13';
218: gt_fore_bud := 'FII_ATTRIBUTE14';
219: gt_fore_py := 'FII_ATTRIBUTE12';
220: IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
221: gt_py_act := 'NULL FII_MEASURE12,';
222: gt_cy_bud := 'NULL FII_CAL2,';
223: ELSE
224: gt_py_act := 'sum(sum(PY_ACTUAL)) over() FII_MEASURE12,';
231:
232: gt_act_py := NULL;
233:
234: --If comparison type is budget, series definition change.
235: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
236:
237: l_prior_or_budget :='(sum(sum(CY_ACTUAL)) over() - sum(sum(CY_BUDGET)) over()) /
238: ABS(NULLIF(sum(sum(CY_BUDGET)) over(),0)) * 100 FII_ATTRIBUTE11,
239: sum(CY_BUDGET) FII_MEASURE10,
247: END IF;
248:
249:
250:
251: fii_gl_util_pkg.get_viewby_sql;
252: fii_gl_util_pkg.get_mgr_pmv_sql;
253: fii_gl_util_pkg.get_lob_pmv_sql;
254: fii_gl_util_pkg.get_cat_pmv_sql;
255: fii_gl_util_pkg.get_ccc_pmv_sql;
248:
249:
250:
251: fii_gl_util_pkg.get_viewby_sql;
252: fii_gl_util_pkg.get_mgr_pmv_sql;
253: fii_gl_util_pkg.get_lob_pmv_sql;
254: fii_gl_util_pkg.get_cat_pmv_sql;
255: fii_gl_util_pkg.get_ccc_pmv_sql;
256:
249:
250:
251: fii_gl_util_pkg.get_viewby_sql;
252: fii_gl_util_pkg.get_mgr_pmv_sql;
253: fii_gl_util_pkg.get_lob_pmv_sql;
254: fii_gl_util_pkg.get_cat_pmv_sql;
255: fii_gl_util_pkg.get_ccc_pmv_sql;
256:
257: --code moved to FII_GL_UTIL_PKG
250:
251: fii_gl_util_pkg.get_viewby_sql;
252: fii_gl_util_pkg.get_mgr_pmv_sql;
253: fii_gl_util_pkg.get_lob_pmv_sql;
254: fii_gl_util_pkg.get_cat_pmv_sql;
255: fii_gl_util_pkg.get_ccc_pmv_sql;
256:
257: --code moved to FII_GL_UTIL_PKG
258: --bug 5002238..to make it as bind variable
251: fii_gl_util_pkg.get_viewby_sql;
252: fii_gl_util_pkg.get_mgr_pmv_sql;
253: fii_gl_util_pkg.get_lob_pmv_sql;
254: fii_gl_util_pkg.get_cat_pmv_sql;
255: fii_gl_util_pkg.get_ccc_pmv_sql;
256:
257: --code moved to FII_GL_UTIL_PKG
258: --bug 5002238..to make it as bind variable
259:
253: fii_gl_util_pkg.get_lob_pmv_sql;
254: fii_gl_util_pkg.get_cat_pmv_sql;
255: fii_gl_util_pkg.get_ccc_pmv_sql;
256:
257: --code moved to FII_GL_UTIL_PKG
258: --bug 5002238..to make it as bind variable
259:
260: /*IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
261: l_id := fii_gl_util_pkg.g_mgr_id;
256:
257: --code moved to FII_GL_UTIL_PKG
258: --bug 5002238..to make it as bind variable
259:
260: /*IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
261: l_id := fii_gl_util_pkg.g_mgr_id;
262: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
263: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
264: l_id := fii_gl_util_pkg.g_lob_id;
257: --code moved to FII_GL_UTIL_PKG
258: --bug 5002238..to make it as bind variable
259:
260: /*IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
261: l_id := fii_gl_util_pkg.g_mgr_id;
262: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
263: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
264: l_id := fii_gl_util_pkg.g_lob_id;
265: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
258: --bug 5002238..to make it as bind variable
259:
260: /*IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
261: l_id := fii_gl_util_pkg.g_mgr_id;
262: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
263: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
264: l_id := fii_gl_util_pkg.g_lob_id;
265: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
266: ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
259:
260: /*IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
261: l_id := fii_gl_util_pkg.g_mgr_id;
262: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
263: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
264: l_id := fii_gl_util_pkg.g_lob_id;
265: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
266: ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
267: l_id := fii_gl_util_pkg.g_fin_id;
260: /*IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
261: l_id := fii_gl_util_pkg.g_mgr_id;
262: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
263: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
264: l_id := fii_gl_util_pkg.g_lob_id;
265: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
266: ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
267: l_id := fii_gl_util_pkg.g_fin_id;
268: l_dim_flag := fii_gl_util_pkg.g_fincat_is_leaf;
261: l_id := fii_gl_util_pkg.g_mgr_id;
262: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
263: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
264: l_id := fii_gl_util_pkg.g_lob_id;
265: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
266: ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
267: l_id := fii_gl_util_pkg.g_fin_id;
268: l_dim_flag := fii_gl_util_pkg.g_fincat_is_leaf;
269: ELSE
262: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
263: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
264: l_id := fii_gl_util_pkg.g_lob_id;
265: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
266: ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
267: l_id := fii_gl_util_pkg.g_fin_id;
268: l_dim_flag := fii_gl_util_pkg.g_fincat_is_leaf;
269: ELSE
270: l_id := -9999;
263: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
264: l_id := fii_gl_util_pkg.g_lob_id;
265: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
266: ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
267: l_id := fii_gl_util_pkg.g_fin_id;
268: l_dim_flag := fii_gl_util_pkg.g_fincat_is_leaf;
269: ELSE
270: l_id := -9999;
271: l_dim_flag := 'Y';
264: l_id := fii_gl_util_pkg.g_lob_id;
265: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
266: ELSIF fii_gl_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
267: l_id := fii_gl_util_pkg.g_fin_id;
268: l_dim_flag := fii_gl_util_pkg.g_fincat_is_leaf;
269: ELSE
270: l_id := -9999;
271: l_dim_flag := 'Y';
272: END IF; */
274:
275: --Since we need to join to a different mv that is no longer
276: --aggregated along management hierarchy, we re-construct the
277: --final PMV select
278: IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
279: l_prior_or_budget :='NULL FII_ATTRIBUTE11,
280: NULL FII_MEASURE3,
281: NULL FII_MEASURE10';
282:
308:
309: ELSE
310:
311: sqlstmt := '
312: select decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','||fii_gl_util_pkg.g_viewby_value||', '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG), '||fii_gl_util_pkg.g_viewby_value||') VIEWBY,
313: f.viewby_id VIEWBYID,
314: to_number(null) FII_MEASURE13,
315: sum(CY_ACTUAL) FII_MEASURE2,
316: sum(CY_FORECAST) FII_MEASURE5,
326: (sum(sum(CY_FORECAST)) over() - sum(sum(CY_BUDGET)) over()) /
327: NULLIF(sum(sum(CY_BUDGET)) over(),0) * 100 '||GT_FORE_BUD||',
328: (sum(sum(CY_FORECAST)) over() - sum(sum(PY_SPER_END)) over()) /
329: ABS(NULLIF(sum(sum(PY_SPER_END)) over(),0)) * 100 '||GT_FORE_PY||','||l_prior_or_budget||',
330: decode('||NVL(fii_gl_util_pkg.g_mgr_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE14,
331: decode('||NVL(fii_gl_util_pkg.g_fin_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE15,
332: decode('||NVL(fii_gl_util_pkg.g_lob_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE16,
333: decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','''||l_url2||''',''''), '''||l_url2||''') FII_MEASURE17,
334: decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','''||l_url2||''',''''), '''||l_url2||''') FII_MEASURE18
327: NULLIF(sum(sum(CY_BUDGET)) over(),0) * 100 '||GT_FORE_BUD||',
328: (sum(sum(CY_FORECAST)) over() - sum(sum(PY_SPER_END)) over()) /
329: ABS(NULLIF(sum(sum(PY_SPER_END)) over(),0)) * 100 '||GT_FORE_PY||','||l_prior_or_budget||',
330: decode('||NVL(fii_gl_util_pkg.g_mgr_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE14,
331: decode('||NVL(fii_gl_util_pkg.g_fin_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE15,
332: decode('||NVL(fii_gl_util_pkg.g_lob_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE16,
333: decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','''||l_url2||''',''''), '''||l_url2||''') FII_MEASURE17,
334: decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','''||l_url2||''',''''), '''||l_url2||''') FII_MEASURE18
335: from '||fii_gl_util_pkg.g_viewby_from_clause||',
328: (sum(sum(CY_FORECAST)) over() - sum(sum(PY_SPER_END)) over()) /
329: ABS(NULLIF(sum(sum(PY_SPER_END)) over(),0)) * 100 '||GT_FORE_PY||','||l_prior_or_budget||',
330: decode('||NVL(fii_gl_util_pkg.g_mgr_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE14,
331: decode('||NVL(fii_gl_util_pkg.g_fin_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE15,
332: decode('||NVL(fii_gl_util_pkg.g_lob_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE16,
333: decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','''||l_url2||''',''''), '''||l_url2||''') FII_MEASURE17,
334: decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','''||l_url2||''',''''), '''||l_url2||''') FII_MEASURE18
335: from '||fii_gl_util_pkg.g_viewby_from_clause||',
336: (select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
331: decode('||NVL(fii_gl_util_pkg.g_fin_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE15,
332: decode('||NVL(fii_gl_util_pkg.g_lob_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE16,
333: decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','''||l_url2||''',''''), '''||l_url2||''') FII_MEASURE17,
334: decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','''||l_url2||''',''''), '''||l_url2||''') FII_MEASURE18
335: from '||fii_gl_util_pkg.g_viewby_from_clause||',
336: (select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
337: sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
338: then f.actual_g
339: else to_number(NULL) end) CY_ACTUAL,
332: decode('||NVL(fii_gl_util_pkg.g_lob_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE16,
333: decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','''||l_url2||''',''''), '''||l_url2||''') FII_MEASURE17,
334: decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','''||l_url2||''',''''), '''||l_url2||''') FII_MEASURE18
335: from '||fii_gl_util_pkg.g_viewby_from_clause||',
336: (select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
337: sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
338: then f.actual_g
339: else to_number(NULL) end) CY_ACTUAL,
340: sum(case when bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id
345: else to_number(NULL) end) CY_BUDGET,
346: to_number(NULL) PY_ACTUAL,
347: to_number(NULL) PY_SPER_END
348: from fii_time_rpt_struct cal
349: '||fii_gl_util_pkg.g_view||'
350: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
351: where cal.report_date = &BIS_CURRENT_ASOF_DATE
352: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||fii_gl_util_pkg.g_cat_join||'
353: '||fii_gl_util_pkg.g_gid||'
346: to_number(NULL) PY_ACTUAL,
347: to_number(NULL) PY_SPER_END
348: from fii_time_rpt_struct cal
349: '||fii_gl_util_pkg.g_view||'
350: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
351: where cal.report_date = &BIS_CURRENT_ASOF_DATE
352: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||fii_gl_util_pkg.g_cat_join||'
353: '||fii_gl_util_pkg.g_gid||'
354: and cal.period_type_id = f.period_type_id
348: from fii_time_rpt_struct cal
349: '||fii_gl_util_pkg.g_view||'
350: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
351: where cal.report_date = &BIS_CURRENT_ASOF_DATE
352: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||fii_gl_util_pkg.g_cat_join||'
353: '||fii_gl_util_pkg.g_gid||'
354: and cal.period_type_id = f.period_type_id
355: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
356: group by '||fii_gl_util_pkg.g_viewby_id||'
349: '||fii_gl_util_pkg.g_view||'
350: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
351: where cal.report_date = &BIS_CURRENT_ASOF_DATE
352: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||fii_gl_util_pkg.g_cat_join||'
353: '||fii_gl_util_pkg.g_gid||'
354: and cal.period_type_id = f.period_type_id
355: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
356: group by '||fii_gl_util_pkg.g_viewby_id||'
357: union all
352: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||fii_gl_util_pkg.g_cat_join||'
353: '||fii_gl_util_pkg.g_gid||'
354: and cal.period_type_id = f.period_type_id
355: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
356: group by '||fii_gl_util_pkg.g_viewby_id||'
357: union all
358: select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
359: to_number(NULL) CY_ACTUAL,
360: to_number(NULL) CY_FORECAST,
354: and cal.period_type_id = f.period_type_id
355: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
356: group by '||fii_gl_util_pkg.g_viewby_id||'
357: union all
358: select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
359: to_number(NULL) CY_ACTUAL,
360: to_number(NULL) CY_FORECAST,
361: to_number(NULL) CY_BUDGET,
362: sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
365: sum(case when bitand(cal.record_type_id, :ENT_PERIOD_TYPE) = cal.record_type_id
366: then f.actual_g
367: else to_number(NULL) end) PY_SPER_END
368: from fii_time_rpt_struct cal
369: '||fii_gl_util_pkg.g_view||'
370: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
371: where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
372: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join|| '
373: '||fii_gl_util_pkg.g_gid||'
366: then f.actual_g
367: else to_number(NULL) end) PY_SPER_END
368: from fii_time_rpt_struct cal
369: '||fii_gl_util_pkg.g_view||'
370: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
371: where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
372: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join|| '
373: '||fii_gl_util_pkg.g_gid||'
374: and cal.period_type_id = f.period_type_id
368: from fii_time_rpt_struct cal
369: '||fii_gl_util_pkg.g_view||'
370: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
371: where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
372: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join|| '
373: '||fii_gl_util_pkg.g_gid||'
374: and cal.period_type_id = f.period_type_id
375: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
376: group by '||fii_gl_util_pkg.g_viewby_id||' ) f
369: '||fii_gl_util_pkg.g_view||'
370: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
371: where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
372: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join|| '
373: '||fii_gl_util_pkg.g_gid||'
374: and cal.period_type_id = f.period_type_id
375: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
376: group by '||fii_gl_util_pkg.g_viewby_id||' ) f
377: where '||fii_gl_util_pkg.g_viewby_join||'
372: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join|| '
373: '||fii_gl_util_pkg.g_gid||'
374: and cal.period_type_id = f.period_type_id
375: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
376: group by '||fii_gl_util_pkg.g_viewby_id||' ) f
377: where '||fii_gl_util_pkg.g_viewby_join||'
378: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
379: order by NVL(FII_MEASURE2, -999999999) desc';
380:
373: '||fii_gl_util_pkg.g_gid||'
374: and cal.period_type_id = f.period_type_id
375: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
376: group by '||fii_gl_util_pkg.g_viewby_id||' ) f
377: where '||fii_gl_util_pkg.g_viewby_join||'
378: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
379: order by NVL(FII_MEASURE2, -999999999) desc';
380:
381: END IF;
374: and cal.period_type_id = f.period_type_id
375: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
376: group by '||fii_gl_util_pkg.g_viewby_id||' ) f
377: where '||fii_gl_util_pkg.g_viewby_join||'
378: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
379: order by NVL(FII_MEASURE2, -999999999) desc';
380:
381: END IF;
382:
394:
395:
396: BEGIN
397:
398: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
399: fii_gl_util_pkg.get_bitmasks;
400: fii_gl_util_pkg.g_fin_type := 'OE';
401:
402: --If comparison type is budget, series definition change.
395:
396: BEGIN
397:
398: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
399: fii_gl_util_pkg.get_bitmasks;
400: fii_gl_util_pkg.g_fin_type := 'OE';
401:
402: --If comparison type is budget, series definition change.
403: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
396: BEGIN
397:
398: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
399: fii_gl_util_pkg.get_bitmasks;
400: fii_gl_util_pkg.g_fin_type := 'OE';
401:
402: --If comparison type is budget, series definition change.
403: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
404:
399: fii_gl_util_pkg.get_bitmasks;
400: fii_gl_util_pkg.g_fin_type := 'OE';
401:
402: --If comparison type is budget, series definition change.
403: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
404:
405: l_prior_or_budget :='(sum(sum(CY_ACTUAL)) over() - sum(sum(CY_BUDGET)) over()) /
406: ABS(NULLIF(sum(sum(CY_BUDGET)) over(),0)) * 100 FII_ATTRIBUTE11,
407: sum(CY_BUDGET) FII_MEASURE3';
411: ABS(NULLIF(sum(sum(PY_ACTUAL)) over(),0)) * 100 FII_ATTRIBUTE11,
412: sum(PY_ACTUAL) FII_MEASURE3';
413: END IF;
414:
415: fii_gl_util_pkg.g_view_by := 'ORGANIZATION+HRI_CL_ORGCC';
416: fii_gl_util_pkg.get_viewby_sql;
417: fii_gl_util_pkg.get_mgr_pmv_sql;
418: fii_gl_util_pkg.get_lob_pmv_sql;
419: fii_gl_util_pkg.get_cat_pmv_sql;
412: sum(PY_ACTUAL) FII_MEASURE3';
413: END IF;
414:
415: fii_gl_util_pkg.g_view_by := 'ORGANIZATION+HRI_CL_ORGCC';
416: fii_gl_util_pkg.get_viewby_sql;
417: fii_gl_util_pkg.get_mgr_pmv_sql;
418: fii_gl_util_pkg.get_lob_pmv_sql;
419: fii_gl_util_pkg.get_cat_pmv_sql;
420: fii_gl_util_pkg.get_ccc_pmv_sql;
413: END IF;
414:
415: fii_gl_util_pkg.g_view_by := 'ORGANIZATION+HRI_CL_ORGCC';
416: fii_gl_util_pkg.get_viewby_sql;
417: fii_gl_util_pkg.get_mgr_pmv_sql;
418: fii_gl_util_pkg.get_lob_pmv_sql;
419: fii_gl_util_pkg.get_cat_pmv_sql;
420: fii_gl_util_pkg.get_ccc_pmv_sql;
421:
414:
415: fii_gl_util_pkg.g_view_by := 'ORGANIZATION+HRI_CL_ORGCC';
416: fii_gl_util_pkg.get_viewby_sql;
417: fii_gl_util_pkg.get_mgr_pmv_sql;
418: fii_gl_util_pkg.get_lob_pmv_sql;
419: fii_gl_util_pkg.get_cat_pmv_sql;
420: fii_gl_util_pkg.get_ccc_pmv_sql;
421:
422:
415: fii_gl_util_pkg.g_view_by := 'ORGANIZATION+HRI_CL_ORGCC';
416: fii_gl_util_pkg.get_viewby_sql;
417: fii_gl_util_pkg.get_mgr_pmv_sql;
418: fii_gl_util_pkg.get_lob_pmv_sql;
419: fii_gl_util_pkg.get_cat_pmv_sql;
420: fii_gl_util_pkg.get_ccc_pmv_sql;
421:
422:
423: --Since we need to join to a different mv that is no longer
416: fii_gl_util_pkg.get_viewby_sql;
417: fii_gl_util_pkg.get_mgr_pmv_sql;
418: fii_gl_util_pkg.get_lob_pmv_sql;
419: fii_gl_util_pkg.get_cat_pmv_sql;
420: fii_gl_util_pkg.get_ccc_pmv_sql;
421:
422:
423: --Since we need to join to a different mv that is no longer
424: --aggregated along management hierarchy, we re-construct the
425: --final PMV select
426:
427:
428: sqlstmt := '
429: select '||fii_gl_util_pkg.g_viewby_value||' VIEWBY,
430: f.viewby_id VIEWBYID,
431: emp.value FII_ATTRIBUTE14,
432: to_number(null) FII_MEASURE13,
433: sum(CY_ACTUAL) FII_MEASURE2,
445: NULLIF(sum(sum(CY_BUDGET)) over(),0) * 100 FII_ATTRIBUTE2,
446: (sum(sum(CY_FORECAST)) over() - sum(sum(PY_SPER_END)) over()) /
447: ABS(NULLIF(sum(sum(PY_SPER_END)) over(),0)) * 100 FII_ATTRIBUTE12,'||l_prior_or_budget||'
448:
449: from '||fii_gl_util_pkg.g_viewby_from_clause||',
450: hri_cs_per_orgcc_ct ct,
451: hri_dbi_cl_per_n_v emp,
452: (select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
453: sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
448:
449: from '||fii_gl_util_pkg.g_viewby_from_clause||',
450: hri_cs_per_orgcc_ct ct,
451: hri_dbi_cl_per_n_v emp,
452: (select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
453: sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
454: then f.actual_g
455: else to_number(NULL) end) CY_ACTUAL,
456: sum(case when bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id
461: else to_number(NULL) end) CY_BUDGET,
462: to_number(NULL) PY_ACTUAL,
463: to_number(NULL) PY_SPER_END
464: from fii_time_rpt_struct cal
465: '||fii_gl_util_pkg.g_view||'
466: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
467: where cal.report_date = &BIS_CURRENT_ASOF_DATE
468: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||fii_gl_util_pkg.g_cat_join||'
469: '||fii_gl_util_pkg.g_gid||'
462: to_number(NULL) PY_ACTUAL,
463: to_number(NULL) PY_SPER_END
464: from fii_time_rpt_struct cal
465: '||fii_gl_util_pkg.g_view||'
466: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
467: where cal.report_date = &BIS_CURRENT_ASOF_DATE
468: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||fii_gl_util_pkg.g_cat_join||'
469: '||fii_gl_util_pkg.g_gid||'
470: and cal.period_type_id = f.period_type_id
464: from fii_time_rpt_struct cal
465: '||fii_gl_util_pkg.g_view||'
466: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
467: where cal.report_date = &BIS_CURRENT_ASOF_DATE
468: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||fii_gl_util_pkg.g_cat_join||'
469: '||fii_gl_util_pkg.g_gid||'
470: and cal.period_type_id = f.period_type_id
471: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
472: group by '||fii_gl_util_pkg.g_viewby_id||'
465: '||fii_gl_util_pkg.g_view||'
466: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
467: where cal.report_date = &BIS_CURRENT_ASOF_DATE
468: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||fii_gl_util_pkg.g_cat_join||'
469: '||fii_gl_util_pkg.g_gid||'
470: and cal.period_type_id = f.period_type_id
471: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
472: group by '||fii_gl_util_pkg.g_viewby_id||'
473: union all
468: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||fii_gl_util_pkg.g_cat_join||'
469: '||fii_gl_util_pkg.g_gid||'
470: and cal.period_type_id = f.period_type_id
471: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
472: group by '||fii_gl_util_pkg.g_viewby_id||'
473: union all
474: select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
475: to_number(NULL) CY_ACTUAL,
476: to_number(NULL) CY_FORECAST,
470: and cal.period_type_id = f.period_type_id
471: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
472: group by '||fii_gl_util_pkg.g_viewby_id||'
473: union all
474: select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
475: to_number(NULL) CY_ACTUAL,
476: to_number(NULL) CY_FORECAST,
477: to_number(NULL) CY_BUDGET,
478: sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
481: sum(case when bitand(cal.record_type_id, :ENT_PERIOD_TYPE) = cal.record_type_id
482: then f.actual_g
483: else to_number(NULL) end) PY_SPER_END
484: from fii_time_rpt_struct cal
485: '||fii_gl_util_pkg.g_view||'
486: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
487: where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
488: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join|| '
489: '||fii_gl_util_pkg.g_gid||'
482: then f.actual_g
483: else to_number(NULL) end) PY_SPER_END
484: from fii_time_rpt_struct cal
485: '||fii_gl_util_pkg.g_view||'
486: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
487: where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
488: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join|| '
489: '||fii_gl_util_pkg.g_gid||'
490: and cal.period_type_id = f.period_type_id
484: from fii_time_rpt_struct cal
485: '||fii_gl_util_pkg.g_view||'
486: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
487: where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
488: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join|| '
489: '||fii_gl_util_pkg.g_gid||'
490: and cal.period_type_id = f.period_type_id
491: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
492: group by '||fii_gl_util_pkg.g_viewby_id||' ) f
485: '||fii_gl_util_pkg.g_view||'
486: '||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
487: where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
488: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join|| '
489: '||fii_gl_util_pkg.g_gid||'
490: and cal.period_type_id = f.period_type_id
491: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
492: group by '||fii_gl_util_pkg.g_viewby_id||' ) f
493: where '||fii_gl_util_pkg.g_viewby_join||'
488: and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join|| '
489: '||fii_gl_util_pkg.g_gid||'
490: and cal.period_type_id = f.period_type_id
491: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
492: group by '||fii_gl_util_pkg.g_viewby_id||' ) f
493: where '||fii_gl_util_pkg.g_viewby_join||'
494: and cc.ORGANIZATION_ID = ct.ORGANIZATION_ID
495: and sysdate between emp.effective_start_date and emp.effective_end_date
496: and emp.person_id = ct.CC_MNGR_PERSON_ID
489: '||fii_gl_util_pkg.g_gid||'
490: and cal.period_type_id = f.period_type_id
491: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
492: group by '||fii_gl_util_pkg.g_viewby_id||' ) f
493: where '||fii_gl_util_pkg.g_viewby_join||'
494: and cc.ORGANIZATION_ID = ct.ORGANIZATION_ID
495: and sysdate between emp.effective_start_date and emp.effective_end_date
496: and emp.person_id = ct.CC_MNGR_PERSON_ID
497: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id, substr(emp.first_name,1,1)' || '||''.''|| ' || 'emp.last_name
493: where '||fii_gl_util_pkg.g_viewby_join||'
494: and cc.ORGANIZATION_ID = ct.ORGANIZATION_ID
495: and sysdate between emp.effective_start_date and emp.effective_end_date
496: and emp.person_id = ct.CC_MNGR_PERSON_ID
497: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id, substr(emp.first_name,1,1)' || '||''.''|| ' || 'emp.last_name
498: &ORDER_BY_CLAUSE';
499:
500: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_ccc_mgr_sql, exp_ccc_mgr_output);
501:
496: and emp.person_id = ct.CC_MNGR_PERSON_ID
497: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id, substr(emp.first_name,1,1)' || '||''.''|| ' || 'emp.last_name
498: &ORDER_BY_CLAUSE';
499:
500: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_ccc_mgr_sql, exp_ccc_mgr_output);
501:
502:
503: END get_exp_ccc_mgr;
504:
519: l_id NUMBER;
520:
521: BEGIN
522:
523: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
524: fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';
525: fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
526: fii_gl_util_pkg.get_bitmasks;
527:
520:
521: BEGIN
522:
523: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
524: fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';
525: fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
526: fii_gl_util_pkg.get_bitmasks;
527:
528: fii_gl_util_pkg.g_fin_type := fin_type;
521: BEGIN
522:
523: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
524: fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';
525: fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
526: fii_gl_util_pkg.get_bitmasks;
527:
528: fii_gl_util_pkg.g_fin_type := fin_type;
529:
522:
523: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
524: fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';
525: fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
526: fii_gl_util_pkg.get_bitmasks;
527:
528: fii_gl_util_pkg.g_fin_type := fin_type;
529:
530: IF fii_gl_util_pkg.g_fin_type = 'R' THEN
524: fii_gl_util_pkg.g_view_by := 'FINANCIAL ITEM+GL_FII_FIN_ITEM';
525: fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
526: fii_gl_util_pkg.get_bitmasks;
527:
528: fii_gl_util_pkg.g_fin_type := fin_type;
529:
530: IF fii_gl_util_pkg.g_fin_type = 'R' THEN
531: l_cat_detail_url := 'pFunctionName=FII_GL_REV_CAT3&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
532: l_journal_src_url:= 'pFunctionName=FII_GL_INV_REV_R&FII_DIM9=FII_MEASURE7&FII_DIM2=FII_MEASURE1&FII_DIM8=FII_MEASURE10&pParamIds=Y';
526: fii_gl_util_pkg.get_bitmasks;
527:
528: fii_gl_util_pkg.g_fin_type := fin_type;
529:
530: IF fii_gl_util_pkg.g_fin_type = 'R' THEN
531: l_cat_detail_url := 'pFunctionName=FII_GL_REV_CAT3&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
532: l_journal_src_url:= 'pFunctionName=FII_GL_INV_REV_R&FII_DIM9=FII_MEASURE7&FII_DIM2=FII_MEASURE1&FII_DIM8=FII_MEASURE10&pParamIds=Y';
533: ELSIF (fii_gl_util_pkg.g_fin_type = 'OE' ) THEN
534: l_cat_detail_url := 'pFunctionName=FII_GL_EXP_CAT3&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
529:
530: IF fii_gl_util_pkg.g_fin_type = 'R' THEN
531: l_cat_detail_url := 'pFunctionName=FII_GL_REV_CAT3&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
532: l_journal_src_url:= 'pFunctionName=FII_GL_INV_REV_R&FII_DIM9=FII_MEASURE7&FII_DIM2=FII_MEASURE1&FII_DIM8=FII_MEASURE10&pParamIds=Y';
533: ELSIF (fii_gl_util_pkg.g_fin_type = 'OE' ) THEN
534: l_cat_detail_url := 'pFunctionName=FII_GL_EXP_CAT3&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
535: l_journal_src_url := 'pFunctionName=FII_GL_INV_EXP_R&FII_DIM9=FII_MEASURE7&FII_DIM2=FII_MEASURE1&FII_DIM8=FII_MEASURE10&pParamIds=Y';
536: ELSIF fii_gl_util_pkg.g_fin_type = 'CGS' THEN
537: l_cat_detail_url := 'pFunctionName=FII_GL_COR_CAT3&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
532: l_journal_src_url:= 'pFunctionName=FII_GL_INV_REV_R&FII_DIM9=FII_MEASURE7&FII_DIM2=FII_MEASURE1&FII_DIM8=FII_MEASURE10&pParamIds=Y';
533: ELSIF (fii_gl_util_pkg.g_fin_type = 'OE' ) THEN
534: l_cat_detail_url := 'pFunctionName=FII_GL_EXP_CAT3&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
535: l_journal_src_url := 'pFunctionName=FII_GL_INV_EXP_R&FII_DIM9=FII_MEASURE7&FII_DIM2=FII_MEASURE1&FII_DIM8=FII_MEASURE10&pParamIds=Y';
536: ELSIF fii_gl_util_pkg.g_fin_type = 'CGS' THEN
537: l_cat_detail_url := 'pFunctionName=FII_GL_COR_CAT3&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
538: l_journal_src_url := 'pFunctionName=FII_GL_INV_COR_R&FII_DIM9=FII_MEASURE7&FII_DIM2=FII_MEASURE1&FII_DIM8=FII_MEASURE10&pParamIds=Y';
539: END IF;
540:
540:
541:
542:
543:
544: fii_gl_util_pkg.get_viewby_sql;
545: fii_gl_util_pkg.get_mgr_pmv_sql;
546: fii_gl_util_pkg.get_lob_pmv_sql;
547: fii_gl_util_pkg.get_cat_pmv_sql;
548: fii_gl_util_pkg.get_ccc_pmv_sql;
541:
542:
543:
544: fii_gl_util_pkg.get_viewby_sql;
545: fii_gl_util_pkg.get_mgr_pmv_sql;
546: fii_gl_util_pkg.get_lob_pmv_sql;
547: fii_gl_util_pkg.get_cat_pmv_sql;
548: fii_gl_util_pkg.get_ccc_pmv_sql;
549:
542:
543:
544: fii_gl_util_pkg.get_viewby_sql;
545: fii_gl_util_pkg.get_mgr_pmv_sql;
546: fii_gl_util_pkg.get_lob_pmv_sql;
547: fii_gl_util_pkg.get_cat_pmv_sql;
548: fii_gl_util_pkg.get_ccc_pmv_sql;
549:
550: l_id := NVL(fii_gl_util_pkg.g_fin_id,-9999);
543:
544: fii_gl_util_pkg.get_viewby_sql;
545: fii_gl_util_pkg.get_mgr_pmv_sql;
546: fii_gl_util_pkg.get_lob_pmv_sql;
547: fii_gl_util_pkg.get_cat_pmv_sql;
548: fii_gl_util_pkg.get_ccc_pmv_sql;
549:
550: l_id := NVL(fii_gl_util_pkg.g_fin_id,-9999);
551:
544: fii_gl_util_pkg.get_viewby_sql;
545: fii_gl_util_pkg.get_mgr_pmv_sql;
546: fii_gl_util_pkg.get_lob_pmv_sql;
547: fii_gl_util_pkg.get_cat_pmv_sql;
548: fii_gl_util_pkg.get_ccc_pmv_sql;
549:
550: l_id := NVL(fii_gl_util_pkg.g_fin_id,-9999);
551:
552: select nvl(to_char(min(ent_period_id)),fii_gl_util_pkg.g_month_id) into l_time_parameter
546: fii_gl_util_pkg.get_lob_pmv_sql;
547: fii_gl_util_pkg.get_cat_pmv_sql;
548: fii_gl_util_pkg.get_ccc_pmv_sql;
549:
550: l_id := NVL(fii_gl_util_pkg.g_fin_id,-9999);
551:
552: select nvl(to_char(min(ent_period_id)),fii_gl_util_pkg.g_month_id) into l_time_parameter
553: from fii_time_ent_period
554: where fii_gl_util_pkg.g_as_of_date between start_date and end_date;
548: fii_gl_util_pkg.get_ccc_pmv_sql;
549:
550: l_id := NVL(fii_gl_util_pkg.g_fin_id,-9999);
551:
552: select nvl(to_char(min(ent_period_id)),fii_gl_util_pkg.g_month_id) into l_time_parameter
553: from fii_time_ent_period
554: where fii_gl_util_pkg.g_as_of_date between start_date and end_date;
555:
556: IF (fii_gl_util_pkg.g_month_id <> l_time_parameter) THEN
550: l_id := NVL(fii_gl_util_pkg.g_fin_id,-9999);
551:
552: select nvl(to_char(min(ent_period_id)),fii_gl_util_pkg.g_month_id) into l_time_parameter
553: from fii_time_ent_period
554: where fii_gl_util_pkg.g_as_of_date between start_date and end_date;
555:
556: IF (fii_gl_util_pkg.g_month_id <> l_time_parameter) THEN
557: l_bitmask := 256;
558: ELSE
552: select nvl(to_char(min(ent_period_id)),fii_gl_util_pkg.g_month_id) into l_time_parameter
553: from fii_time_ent_period
554: where fii_gl_util_pkg.g_as_of_date between start_date and end_date;
555:
556: IF (fii_gl_util_pkg.g_month_id <> l_time_parameter) THEN
557: l_bitmask := 256;
558: ELSE
559: l_bitmask := 23;
560:
575: ******************************************************************/
576: --we need to make sure that bug 2797564 is fixed
577:
578:
579: sqlstmt := ' select '||fii_gl_util_pkg.g_viewby_value||' VIEWBY,
580: f.viewby_id VIEWBYID,
581: NULL FII_MEASURE5,
582: f.viewby_id FII_MEASURE1,
583: :MGR_ID FII_MEASURE6,
592: WHERE parent_fin_cat_id = f.viewby_id
593: and child_fin_cat_id = f.viewby_id),
594: ''Y'','''||l_journal_src_url||''',
595: '''||l_cat_detail_url||''') FII_URL
596: from '||fii_gl_util_pkg.g_viewby_from_clause||',
597: (select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
598: sum(f.actual_g) CY_ACTUAL,
599: to_number(NULL) PY_ACTUAL
600: from fii_time_rpt_struct cal
593: and child_fin_cat_id = f.viewby_id),
594: ''Y'','''||l_journal_src_url||''',
595: '''||l_cat_detail_url||''') FII_URL
596: from '||fii_gl_util_pkg.g_viewby_from_clause||',
597: (select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
598: sum(f.actual_g) CY_ACTUAL,
599: to_number(NULL) PY_ACTUAL
600: from fii_time_rpt_struct cal
601: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
597: (select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
598: sum(f.actual_g) CY_ACTUAL,
599: to_number(NULL) PY_ACTUAL
600: from fii_time_rpt_struct cal
601: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
602: where cal.report_date = to_date(:P_AS_OF, ''DD-MM-YYYY'')
603: /*Fix -- should be set dynamically*/
604: and cal.record_type_id = bitand(cal.record_type_id, '||l_bitmask||')
605: and f.time_id = cal.time_id
603: /*Fix -- should be set dynamically*/
604: and cal.record_type_id = bitand(cal.record_type_id, '||l_bitmask||')
605: and f.time_id = cal.time_id
606: and f.period_type_id = cal.period_type_id
607: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
608: '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||'
609: group by '||fii_gl_util_pkg.g_viewby_id||'
610: union all
611: select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
604: and cal.record_type_id = bitand(cal.record_type_id, '||l_bitmask||')
605: and f.time_id = cal.time_id
606: and f.period_type_id = cal.period_type_id
607: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
608: '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||'
609: group by '||fii_gl_util_pkg.g_viewby_id||'
610: union all
611: select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
612: to_number(NULL) CY_ACTUAL,
605: and f.time_id = cal.time_id
606: and f.period_type_id = cal.period_type_id
607: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
608: '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||'
609: group by '||fii_gl_util_pkg.g_viewby_id||'
610: union all
611: select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
612: to_number(NULL) CY_ACTUAL,
613: sum(f.actual_g) PY_ACTUAL
607: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
608: '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||'
609: group by '||fii_gl_util_pkg.g_viewby_id||'
610: union all
611: select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
612: to_number(NULL) CY_ACTUAL,
613: sum(f.actual_g) PY_ACTUAL
614: from fii_time_rpt_struct cal
615: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
611: select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
612: to_number(NULL) CY_ACTUAL,
613: sum(f.actual_g) PY_ACTUAL
614: from fii_time_rpt_struct cal
615: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
616: where cal.report_date = to_date(:P_PREV_AS_OF, ''DD-MM-YYYY'')
617: and cal.record_type_id = bitand(cal.record_type_id, '||l_bitmask||')
618: and f.time_id = cal.time_id
619: and f.period_type_id = cal.period_type_id
616: where cal.report_date = to_date(:P_PREV_AS_OF, ''DD-MM-YYYY'')
617: and cal.record_type_id = bitand(cal.record_type_id, '||l_bitmask||')
618: and f.time_id = cal.time_id
619: and f.period_type_id = cal.period_type_id
620: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
621: '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||'
622: group by '||fii_gl_util_pkg.g_viewby_id||' ) f
623: where '||fii_gl_util_pkg.g_viewby_join||'
624: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
617: and cal.record_type_id = bitand(cal.record_type_id, '||l_bitmask||')
618: and f.time_id = cal.time_id
619: and f.period_type_id = cal.period_type_id
620: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
621: '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||'
622: group by '||fii_gl_util_pkg.g_viewby_id||' ) f
623: where '||fii_gl_util_pkg.g_viewby_join||'
624: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
625: order by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';
618: and f.time_id = cal.time_id
619: and f.period_type_id = cal.period_type_id
620: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
621: '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||'
622: group by '||fii_gl_util_pkg.g_viewby_id||' ) f
623: where '||fii_gl_util_pkg.g_viewby_join||'
624: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
625: order by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';
626:
619: and f.period_type_id = cal.period_type_id
620: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
621: '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||'
622: group by '||fii_gl_util_pkg.g_viewby_id||' ) f
623: where '||fii_gl_util_pkg.g_viewby_join||'
624: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
625: order by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';
626:
627: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_cc_by_cat1_sql, exp_cc_by_cat1_output);
620: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
621: '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||'
622: group by '||fii_gl_util_pkg.g_viewby_id||' ) f
623: where '||fii_gl_util_pkg.g_viewby_join||'
624: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
625: order by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';
626:
627: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_cc_by_cat1_sql, exp_cc_by_cat1_output);
628:
621: '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_ccc_join||'
622: group by '||fii_gl_util_pkg.g_viewby_id||' ) f
623: where '||fii_gl_util_pkg.g_viewby_join||'
624: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
625: order by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';
626:
627: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_cc_by_cat1_sql, exp_cc_by_cat1_output);
628:
629: END get_exp_cc_by_cat1;
623: where '||fii_gl_util_pkg.g_viewby_join||'
624: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
625: order by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';
626:
627: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_cc_by_cat1_sql, exp_cc_by_cat1_output);
628:
629: END get_exp_cc_by_cat1;
630: PROCEDURE get_rev_cc_by_cat (
631: p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, rev_cc_by_cat_sql out NOCOPY VARCHAR2,
631: p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, rev_cc_by_cat_sql out NOCOPY VARCHAR2,
632: rev_cc_by_cat_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
633: l_fin_type VARCHAR2(1);
634: BEGIN
635: fii_gl_util_pkg.reset_globals;
636: l_fin_type := 'R';
637: fii_gl_cost_center_pkg.get_exp_cc_by_cat1(p_page_parameter_tbl, rev_cc_by_cat_sql, rev_cc_by_cat_output, l_fin_type );
638:
639:
643: p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, exp_cc_by_cat_sql out NOCOPY VARCHAR2,
644: exp_cc_by_cat_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
645: l_fin_type VARCHAR2(2);
646: BEGIN
647: fii_gl_util_pkg.reset_globals;
648: l_fin_type := 'OE';
649: fii_gl_cost_center_pkg.get_exp_cc_by_cat1(p_page_parameter_tbl, exp_cc_by_cat_sql, exp_cc_by_cat_output, l_fin_type );
650:
651:
655: p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, cogs_cc_by_cat_sql out NOCOPY VARCHAR2,
656: cogs_cc_by_cat_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
657: l_fin_type VARCHAR2(3);
658: BEGIN
659: fii_gl_util_pkg.reset_globals;
660: l_fin_type := 'CGS';
661: fii_gl_cost_center_pkg.get_exp_cc_by_cat1(p_page_parameter_tbl, cogs_cc_by_cat_sql, cogs_cc_by_cat_output, l_fin_type );
662:
663:
678: l_lyr DATE;
679: l_llyr DATE;
680:
681: BEGIN
682: fii_gl_util_pkg.reset_globals;
683: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
684: fii_gl_util_pkg.g_fin_type := l_fin_type;
685: fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
686: fii_gl_util_pkg.get_bitmasks;
679: l_llyr DATE;
680:
681: BEGIN
682: fii_gl_util_pkg.reset_globals;
683: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
684: fii_gl_util_pkg.g_fin_type := l_fin_type;
685: fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
686: fii_gl_util_pkg.get_bitmasks;
687: fii_gl_util_pkg.get_mgr_pmv_sql;
680:
681: BEGIN
682: fii_gl_util_pkg.reset_globals;
683: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
684: fii_gl_util_pkg.g_fin_type := l_fin_type;
685: fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
686: fii_gl_util_pkg.get_bitmasks;
687: fii_gl_util_pkg.get_mgr_pmv_sql;
688: fii_gl_util_pkg.get_lob_pmv_sql;
681: BEGIN
682: fii_gl_util_pkg.reset_globals;
683: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
684: fii_gl_util_pkg.g_fin_type := l_fin_type;
685: fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
686: fii_gl_util_pkg.get_bitmasks;
687: fii_gl_util_pkg.get_mgr_pmv_sql;
688: fii_gl_util_pkg.get_lob_pmv_sql;
689: fii_gl_util_pkg.get_cat_pmv_sql;
682: fii_gl_util_pkg.reset_globals;
683: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
684: fii_gl_util_pkg.g_fin_type := l_fin_type;
685: fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
686: fii_gl_util_pkg.get_bitmasks;
687: fii_gl_util_pkg.get_mgr_pmv_sql;
688: fii_gl_util_pkg.get_lob_pmv_sql;
689: fii_gl_util_pkg.get_cat_pmv_sql;
690: fii_gl_util_pkg.get_ccc_pmv_sql;
683: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
684: fii_gl_util_pkg.g_fin_type := l_fin_type;
685: fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
686: fii_gl_util_pkg.get_bitmasks;
687: fii_gl_util_pkg.get_mgr_pmv_sql;
688: fii_gl_util_pkg.get_lob_pmv_sql;
689: fii_gl_util_pkg.get_cat_pmv_sql;
690: fii_gl_util_pkg.get_ccc_pmv_sql;
691:
684: fii_gl_util_pkg.g_fin_type := l_fin_type;
685: fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
686: fii_gl_util_pkg.get_bitmasks;
687: fii_gl_util_pkg.get_mgr_pmv_sql;
688: fii_gl_util_pkg.get_lob_pmv_sql;
689: fii_gl_util_pkg.get_cat_pmv_sql;
690: fii_gl_util_pkg.get_ccc_pmv_sql;
691:
692: IF fii_gl_util_pkg.g_lob_is_top_node <> 'Y' AND fii_gl_util_pkg.g_lob_id IS NOT NULL THEN
685: fii_gl_util_pkg.g_page_period_type := 'FII_TIME_ENT_PERIOD';
686: fii_gl_util_pkg.get_bitmasks;
687: fii_gl_util_pkg.get_mgr_pmv_sql;
688: fii_gl_util_pkg.get_lob_pmv_sql;
689: fii_gl_util_pkg.get_cat_pmv_sql;
690: fii_gl_util_pkg.get_ccc_pmv_sql;
691:
692: IF fii_gl_util_pkg.g_lob_is_top_node <> 'Y' AND fii_gl_util_pkg.g_lob_id IS NOT NULL THEN
693: l_hint1 := '/*+ leading(per) use_nl(f) */';
686: fii_gl_util_pkg.get_bitmasks;
687: fii_gl_util_pkg.get_mgr_pmv_sql;
688: fii_gl_util_pkg.get_lob_pmv_sql;
689: fii_gl_util_pkg.get_cat_pmv_sql;
690: fii_gl_util_pkg.get_ccc_pmv_sql;
691:
692: IF fii_gl_util_pkg.g_lob_is_top_node <> 'Y' AND fii_gl_util_pkg.g_lob_id IS NOT NULL THEN
693: l_hint1 := '/*+ leading(per) use_nl(f) */';
694: l_hint2 := '/*+ leading(cal) use_nl(f) */';
688: fii_gl_util_pkg.get_lob_pmv_sql;
689: fii_gl_util_pkg.get_cat_pmv_sql;
690: fii_gl_util_pkg.get_ccc_pmv_sql;
691:
692: IF fii_gl_util_pkg.g_lob_is_top_node <> 'Y' AND fii_gl_util_pkg.g_lob_id IS NOT NULL THEN
693: l_hint1 := '/*+ leading(per) use_nl(f) */';
694: l_hint2 := '/*+ leading(cal) use_nl(f) */';
695: END IF;
696:
693: l_hint1 := '/*+ leading(per) use_nl(f) */';
694: l_hint2 := '/*+ leading(cal) use_nl(f) */';
695: END IF;
696:
697: SELECT fii_time_api.ent_sd_lysper_end(fii_gl_util_pkg.g_as_of_date)
698: INTO l_lyr
699: FROM dual;
700:
701: IF l_lyr is NOT NULL THEN
715: ELSE
716: l_join2 := '>= to_date(:PPY_SAME_DAY, ''DD-MM-YYYY'')';
717: END IF;
718:
719: IF fii_gl_util_pkg.g_mgr_id = -99999 THEN /* Done for bug 3875336 */
720:
721: sqlstmt := ' SELECT NULL VIEWBY,
722: NULL FII_MEASURE1,
723: NULL FII_MEASURE2,
744: then f.actual_g else to_number(NULL) end CY_PTOT,
745: case when per.end_date < to_date(:P_SD_LYR, ''DD-MM-YYYY'')
746: then f.actual_g else to_number(NULL) end PY_PTOT
747: from fii_time_ent_period per
748: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
749: where per.ent_period_id = f.time_id
750: '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
751: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
752: and per.start_date '||l_join2||'
746: then f.actual_g else to_number(NULL) end PY_PTOT
747: from fii_time_ent_period per
748: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
749: where per.ent_period_id = f.time_id
750: '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
751: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
752: and per.start_date '||l_join2||'
753: and per.end_date < to_date(:ASOF_DATE, ''DD-MM-YYYY'')
754: and f.period_type_id = 32
747: from fii_time_ent_period per
748: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
749: where per.ent_period_id = f.time_id
750: '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
751: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
752: and per.start_date '||l_join2||'
753: and per.end_date < to_date(:ASOF_DATE, ''DD-MM-YYYY'')
754: and f.period_type_id = 32
755: union all
762: then f.actual_g
763: else to_number(NULL)
764: end PY_QTOT
765: from fii_time_rpt_struct cal
766: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
767: where cal.time_id = f.time_id
768: '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
769: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
770: and cal.period_type_id = f.period_type_id
764: end PY_QTOT
765: from fii_time_rpt_struct cal
766: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
767: where cal.time_id = f.time_id
768: '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
769: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
770: and cal.period_type_id = f.period_type_id
771: and cal.report_date in (to_date(:ASOF_DATE, ''DD-MM-YYYY'') , to_date(:P_SD_LYR, ''DD-MM-YYYY''))
772: and bitand(cal.record_type_id, 23) = cal.record_type_id) inner_inline_view
765: from fii_time_rpt_struct cal
766: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
767: where cal.time_id = f.time_id
768: '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
769: '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
770: and cal.period_type_id = f.period_type_id
771: and cal.report_date in (to_date(:ASOF_DATE, ''DD-MM-YYYY'') , to_date(:P_SD_LYR, ''DD-MM-YYYY''))
772: and bitand(cal.record_type_id, 23) = cal.record_type_id) inner_inline_view
773: group by inner_inline_view.fii_effective_num ) inline_view
777: order by cy_per.start_date';
778:
779: END IF;
780:
781: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, revexp_tr_sql, revexp_tr_output);
782:
783: END get_revexp_tr;
784:
785: PROCEDURE get_rev_tr (
846: -- l_dim_flag VARCHAR2(1);
847:
848: BEGIN
849:
850: fii_gl_util_pkg.reset_globals;
851: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
852: fii_gl_util_pkg.get_bitmasks;
853: fii_gl_util_pkg.get_viewby_sql;
854: fii_gl_util_pkg.get_mgr_pmv_sql;
847:
848: BEGIN
849:
850: fii_gl_util_pkg.reset_globals;
851: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
852: fii_gl_util_pkg.get_bitmasks;
853: fii_gl_util_pkg.get_viewby_sql;
854: fii_gl_util_pkg.get_mgr_pmv_sql;
855: fii_gl_util_pkg.get_lob_pmv_sql;
848: BEGIN
849:
850: fii_gl_util_pkg.reset_globals;
851: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
852: fii_gl_util_pkg.get_bitmasks;
853: fii_gl_util_pkg.get_viewby_sql;
854: fii_gl_util_pkg.get_mgr_pmv_sql;
855: fii_gl_util_pkg.get_lob_pmv_sql;
856: fii_gl_util_pkg.get_ccc_pmv_sql;
849:
850: fii_gl_util_pkg.reset_globals;
851: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
852: fii_gl_util_pkg.get_bitmasks;
853: fii_gl_util_pkg.get_viewby_sql;
854: fii_gl_util_pkg.get_mgr_pmv_sql;
855: fii_gl_util_pkg.get_lob_pmv_sql;
856: fii_gl_util_pkg.get_ccc_pmv_sql;
857:
850: fii_gl_util_pkg.reset_globals;
851: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
852: fii_gl_util_pkg.get_bitmasks;
853: fii_gl_util_pkg.get_viewby_sql;
854: fii_gl_util_pkg.get_mgr_pmv_sql;
855: fii_gl_util_pkg.get_lob_pmv_sql;
856: fii_gl_util_pkg.get_ccc_pmv_sql;
857:
858: IF p_opera_marg = 'Y' THEN
851: fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
852: fii_gl_util_pkg.get_bitmasks;
853: fii_gl_util_pkg.get_viewby_sql;
854: fii_gl_util_pkg.get_mgr_pmv_sql;
855: fii_gl_util_pkg.get_lob_pmv_sql;
856: fii_gl_util_pkg.get_ccc_pmv_sql;
857:
858: IF p_opera_marg = 'Y' THEN
859: fii_gl_util_pkg.g_fin_type := 'OM';
852: fii_gl_util_pkg.get_bitmasks;
853: fii_gl_util_pkg.get_viewby_sql;
854: fii_gl_util_pkg.get_mgr_pmv_sql;
855: fii_gl_util_pkg.get_lob_pmv_sql;
856: fii_gl_util_pkg.get_ccc_pmv_sql;
857:
858: IF p_opera_marg = 'Y' THEN
859: fii_gl_util_pkg.g_fin_type := 'OM';
860: l_url := 'pFunctionName=FII_GL_OPS_LOB_MGR1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
855: fii_gl_util_pkg.get_lob_pmv_sql;
856: fii_gl_util_pkg.get_ccc_pmv_sql;
857:
858: IF p_opera_marg = 'Y' THEN
859: fii_gl_util_pkg.g_fin_type := 'OM';
860: l_url := 'pFunctionName=FII_GL_OPS_LOB_MGR1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
861: ELSE fii_gl_util_pkg.g_fin_type := 'GM';
862: l_url := 'pFunctionName=FII_GL_MAR_LOB_MGR1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
863: END IF;
857:
858: IF p_opera_marg = 'Y' THEN
859: fii_gl_util_pkg.g_fin_type := 'OM';
860: l_url := 'pFunctionName=FII_GL_OPS_LOB_MGR1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
861: ELSE fii_gl_util_pkg.g_fin_type := 'GM';
862: l_url := 'pFunctionName=FII_GL_MAR_LOB_MGR1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
863: END IF;
864:
865: fii_gl_util_pkg.get_cat_pmv_sql;
861: ELSE fii_gl_util_pkg.g_fin_type := 'GM';
862: l_url := 'pFunctionName=FII_GL_MAR_LOB_MGR1&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
863: END IF;
864:
865: fii_gl_util_pkg.get_cat_pmv_sql;
866:
867:
868: IF p_opera_marg = 'Y' THEN
869: cy_act_exp_select := ' NVL(sum(NVL(CY_ACT_EXP,0)), 0) FII_ATTRIBUTE2,';
881: NVL(sum(sum(CY_ACT_REV)) over(), 0) - '||l_subtractor||' FII_ATTRIBUTE11,
882: (NVL(sum(sum(CY_ACT_REV)) over(), 0) - '||l_subtractor||') /
883: ABS(NULLIF(sum(sum(CY_ACT_REV)) over(),0)) * 100 FII_ATTRIBUTE12,';
884:
885: IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
886:
887: l_prior := 'CY_BUD';
888: l_record_type := ':BUDGET_PERIOD_TYPE';
889: l_amt := 'actual_g';
912: l_subtractor2 := 'NVL(sum(sum(NVL('||l_prior||'_CGS,0))) over(), 0)';
913: l_subtractor4 := ' NVL(sum(NVL('||l_prior||'_CGS,0)), 0) ';
914: END IF;
915:
916: --moved to fii_gl_util_pkg
917: --added for bug fix 5002238
918: --by vkazhipu
919: --changing l_id and l_dim_flag to bind variables
920: /*
917: --added for bug fix 5002238
918: --by vkazhipu
919: --changing l_id and l_dim_flag to bind variables
920: /*
921: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
922: l_id := fii_gl_util_pkg.g_mgr_id;
923: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
924: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
925: l_id := fii_gl_util_pkg.g_lob_id;
918: --by vkazhipu
919: --changing l_id and l_dim_flag to bind variables
920: /*
921: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
922: l_id := fii_gl_util_pkg.g_mgr_id;
923: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
924: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
925: l_id := fii_gl_util_pkg.g_lob_id;
926: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
919: --changing l_id and l_dim_flag to bind variables
920: /*
921: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
922: l_id := fii_gl_util_pkg.g_mgr_id;
923: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
924: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
925: l_id := fii_gl_util_pkg.g_lob_id;
926: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
927: ELSE
920: /*
921: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
922: l_id := fii_gl_util_pkg.g_mgr_id;
923: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
924: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
925: l_id := fii_gl_util_pkg.g_lob_id;
926: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
927: ELSE
928: l_id := -9999;
921: IF fii_gl_util_pkg.g_view_by = 'HRI_PERSON+HRI_PER_USRDR_H' THEN
922: l_id := fii_gl_util_pkg.g_mgr_id;
923: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
924: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
925: l_id := fii_gl_util_pkg.g_lob_id;
926: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
927: ELSE
928: l_id := -9999;
929: l_dim_flag := 'Y';
922: l_id := fii_gl_util_pkg.g_mgr_id;
923: l_dim_flag := fii_gl_util_pkg.g_mgr_is_leaf;
924: ELSIF fii_gl_util_pkg.g_view_by = 'LOB+FII_LOB' THEN
925: l_id := fii_gl_util_pkg.g_lob_id;
926: l_dim_flag := fii_gl_util_pkg.g_lob_is_leaf;
927: ELSE
928: l_id := -9999;
929: l_dim_flag := 'Y';
930: END IF;
940: -- FII_MEASURE1 = LOB name
941: -- FII_CAL1 = Prior Total Revenue amounts
942: -- FII_CAL2 = Prior Total COR amounts
943: -- ----------------------------------------------------------------
944: IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
945: cy_prior_exp_select := 'NULL FII_ATTRIBUTE4,';
946: cy_act_exp_select := ' NULL FII_ATTRIBUTE2,';
947: sqlstmt1 := ' NULL FII_MEASURE2,
948: NULL FII_MEASURE3, '||cy_act_exp_select||'
969:
970: ELSE
971:
972: sqlstmt := '
973: select decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','||fii_gl_util_pkg.g_viewby_value||', '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG), '||fii_gl_util_pkg.g_viewby_value||') VIEWBY,
974: f.viewby_id VIEWBYID,
975: '||sqlstmt1||'
976: to_number(NULL) FII_CAL1,
977: to_number(NULL) FII_CAL2,
987: ABS(NULLIF((sum(sum(NVL('||l_prior||'_REV,0))) over() - '||l_subtractor2||'),0)) * 100 FII_ATTRIBUTE14,
988: (case when NVL(abs((NVL(sum(CY_ACT_REV), 0) - '||l_subtractor3||')/
989: ABS(NULLIF(sum(CY_ACT_REV), 0)) * 100), 1000) > 999.9 THEN NULL WHEN NVL(abs((NVL(sum('||l_prior||'_REV), 0) - '||l_subtractor4||')/
990: ABS(NULLIF(sum('||l_prior||'_REV), 0)) * 100), 1000) > 999.9 THEN NULL ELSE 0 END) FII_MEASURE13,
991: decode('||NVL(fii_gl_util_pkg.g_mgr_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE14,
992: decode('||NVL(fii_gl_util_pkg.g_lob_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE15
993: from '||fii_gl_util_pkg.g_viewby_from_clause||',
994: (select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
995: sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
988: (case when NVL(abs((NVL(sum(CY_ACT_REV), 0) - '||l_subtractor3||')/
989: ABS(NULLIF(sum(CY_ACT_REV), 0)) * 100), 1000) > 999.9 THEN NULL WHEN NVL(abs((NVL(sum('||l_prior||'_REV), 0) - '||l_subtractor4||')/
990: ABS(NULLIF(sum('||l_prior||'_REV), 0)) * 100), 1000) > 999.9 THEN NULL ELSE 0 END) FII_MEASURE13,
991: decode('||NVL(fii_gl_util_pkg.g_mgr_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE14,
992: decode('||NVL(fii_gl_util_pkg.g_lob_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE15
993: from '||fii_gl_util_pkg.g_viewby_from_clause||',
994: (select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
995: sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
996: and assgns.fin_cat_type_code = ''R''
989: ABS(NULLIF(sum(CY_ACT_REV), 0)) * 100), 1000) > 999.9 THEN NULL WHEN NVL(abs((NVL(sum('||l_prior||'_REV), 0) - '||l_subtractor4||')/
990: ABS(NULLIF(sum('||l_prior||'_REV), 0)) * 100), 1000) > 999.9 THEN NULL ELSE 0 END) FII_MEASURE13,
991: decode('||NVL(fii_gl_util_pkg.g_mgr_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE14,
992: decode('||NVL(fii_gl_util_pkg.g_lob_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE15
993: from '||fii_gl_util_pkg.g_viewby_from_clause||',
994: (select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
995: sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
996: and assgns.fin_cat_type_code = ''R''
997: then f.actual_g
990: ABS(NULLIF(sum('||l_prior||'_REV), 0)) * 100), 1000) > 999.9 THEN NULL ELSE 0 END) FII_MEASURE13,
991: decode('||NVL(fii_gl_util_pkg.g_mgr_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE14,
992: decode('||NVL(fii_gl_util_pkg.g_lob_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE15
993: from '||fii_gl_util_pkg.g_viewby_from_clause||',
994: (select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
995: sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
996: and assgns.fin_cat_type_code = ''R''
997: then f.actual_g
998: else to_number(NULL) end) CY_ACT_REV,
1023: to_number(NULL) PYPER_ACT_CGS
1024:
1025: from fii_time_rpt_struct cal,
1026: fii_fin_cat_type_assgns assgns
1027: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
1028: where cal.time_id = f.time_id
1029: '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
1030: and assgns.fin_category_id= f.fin_category_id
1031: '||fii_gl_util_pkg.g_gid||'
1025: from fii_time_rpt_struct cal,
1026: fii_fin_cat_type_assgns assgns
1027: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
1028: where cal.time_id = f.time_id
1029: '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
1030: and assgns.fin_category_id= f.fin_category_id
1031: '||fii_gl_util_pkg.g_gid||'
1032: and cal.period_type_id = f.period_type_id
1033: and cal.report_date = &BIS_CURRENT_ASOF_DATE
1027: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
1028: where cal.time_id = f.time_id
1029: '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
1030: and assgns.fin_category_id= f.fin_category_id
1031: '||fii_gl_util_pkg.g_gid||'
1032: and cal.period_type_id = f.period_type_id
1033: and cal.report_date = &BIS_CURRENT_ASOF_DATE
1034: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
1035: group by '||fii_gl_util_pkg.g_viewby_id||'
1031: '||fii_gl_util_pkg.g_gid||'
1032: and cal.period_type_id = f.period_type_id
1033: and cal.report_date = &BIS_CURRENT_ASOF_DATE
1034: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
1035: group by '||fii_gl_util_pkg.g_viewby_id||'
1036: union all
1037: select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
1038: to_number(NULL) CY_ACT_REV,
1039: to_number(NULL) CY_ACT_EXP,
1033: and cal.report_date = &BIS_CURRENT_ASOF_DATE
1034: and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
1035: group by '||fii_gl_util_pkg.g_viewby_id||'
1036: union all
1037: select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
1038: to_number(NULL) CY_ACT_REV,
1039: to_number(NULL) CY_ACT_EXP,
1040: to_number(NULL) CY_ACT_CGS,
1041: to_number(NULL) CY_BUD_REV,
1057: to_number(NULL) PYPER_ACT_CGS
1058:
1059: from fii_time_rpt_struct cal,
1060: fii_fin_cat_type_assgns assgns
1061: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
1062: where cal.time_id = f.time_id
1063: '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
1064: and assgns.fin_category_id = f.fin_category_id
1065: '||fii_gl_util_pkg.g_gid||'
1059: from fii_time_rpt_struct cal,
1060: fii_fin_cat_type_assgns assgns
1061: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
1062: where cal.time_id = f.time_id
1063: '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
1064: and assgns.fin_category_id = f.fin_category_id
1065: '||fii_gl_util_pkg.g_gid||'
1066: and cal.period_type_id = f.period_type_id
1067: and cal.report_date = &BIS_PREVIOUS_ASOF_DATE
1061: '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_ccc_from_clause||'
1062: where cal.time_id = f.time_id
1063: '||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_ccc_join||'
1064: and assgns.fin_category_id = f.fin_category_id
1065: '||fii_gl_util_pkg.g_gid||'
1066: and cal.period_type_id = f.period_type_id
1067: and cal.report_date = &BIS_PREVIOUS_ASOF_DATE
1068: and bitand(cal.record_type_id, :ACT_WHERE_PERIOD_TYPE) = cal.record_type_id
1069: group by '||fii_gl_util_pkg.g_viewby_id||') f
1065: '||fii_gl_util_pkg.g_gid||'
1066: and cal.period_type_id = f.period_type_id
1067: and cal.report_date = &BIS_PREVIOUS_ASOF_DATE
1068: and bitand(cal.record_type_id, :ACT_WHERE_PERIOD_TYPE) = cal.record_type_id
1069: group by '||fii_gl_util_pkg.g_viewby_id||') f
1070: where '||fii_gl_util_pkg.g_viewby_join||'
1071: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
1072: order by NVL(FII_MEASURE11, -9999999999) desc';
1073:
1066: and cal.period_type_id = f.period_type_id
1067: and cal.report_date = &BIS_PREVIOUS_ASOF_DATE
1068: and bitand(cal.record_type_id, :ACT_WHERE_PERIOD_TYPE) = cal.record_type_id
1069: group by '||fii_gl_util_pkg.g_viewby_id||') f
1070: where '||fii_gl_util_pkg.g_viewby_join||'
1071: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
1072: order by NVL(FII_MEASURE11, -9999999999) desc';
1073:
1074: END IF;
1067: and cal.report_date = &BIS_PREVIOUS_ASOF_DATE
1068: and bitand(cal.record_type_id, :ACT_WHERE_PERIOD_TYPE) = cal.record_type_id
1069: group by '||fii_gl_util_pkg.g_viewby_id||') f
1070: where '||fii_gl_util_pkg.g_viewby_join||'
1071: group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
1072: order by NVL(FII_MEASURE11, -9999999999) desc';
1073:
1074: END IF;
1075:
1072: order by NVL(FII_MEASURE11, -9999999999) desc';
1073:
1074: END IF;
1075:
1076: fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, cont_marg_sql, cont_marg_output);
1077:
1078: END get_cont_marg;
1079:
1080: --* Procedure added by Ilavenil. This procedure is called by OPERATING MARGIN.