[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select mgr_level into mgr_level from fii_cc_mgr_hierarchies where EMP_ID = l_mgr_id and DIRECT_ID = l_mgr_id and MGR_ID = l_mgr_id;
sqlstmt := ' select value VIEWBY,
sum(CY_ACTUAL) FII_MEASURE2,
sum(PY_ACTUAL) FII_MEASURE3,
sum(CY_FORECAST) FII_MEASURE5,
sum(CY_BUDGET) FII_MEASURE7,
FROM
(select '||fii_gl_util_pkg.g_viewby_id||' ID,
'||fii_gl_util_pkg.g_viewby_value||' value,
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,
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,
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,
to_number(NULL) PY_ACTUAL
from fii_time_rpt_stuct cal
'||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_mgr_from_clause||'
where cal.report_date = &BIS_CURRENT_ASOF_DATE
and cal.record_type_id = bitand(cal.record_type_id, :WHERE_PERIOD_TYPE)
and f.time_id = cal.time_id
and f.period_type_id = cal.period_type_id
'||fii_gl_util_pkg.g_gid||'
'||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_mgr_join||'
group by '||fii_gl_util_pkg.g_cat_viewby_id||'
union all
select '||fii_gl_util_pkg.g_cat_viewby_id||' ID,
'||fii_gl_util_pkg.g_viewby_value||' VALUE,
to_number(NULL) CY_ACTUAL,
to_number(NULL) CY_FORECAST,
to_number(NULL) CY_BUDGET,
sum(f.actual_g) PY_ACTUAL
from fii_time_rpt_stuct cal
'||fii_gl_util_pkg.g_cat_from_clause||fii_gl_util_pkg.g_mgr_from_clause||'
where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
and cal.record_type_id = bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE)
and f.time_id = cal.time_id
and f.period_type_id = cal.period_type_id
'||fii_gl_util_pkg.g_gid||'
'||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_mgr_join||'
group by '||fii_gl_util_pkg.g_viewby_id||' )
group by VALUE
order by FII_MEASURE2 desc';
select NULL VIEWBY,
NULL VIEWBYID,
NULL FII_MEASURE13,
NULL FII_MEASURE2,
NULL FII_MEASURE5,
NULL FII_MEASURE7,
NULL FII_MEASURE9,
NULL FII_ATTRIBUTE4,
NULL '||GT_CY_ACT||',
'||GT_PY_ACT||'
NULL '||GT_CY_FORE||',
'||GT_CY_BUD||'
NULL '||GT_FORE_ACT||',
NULL '||GT_FORE_BUD||',
NULL '||GT_FORE_PY||',
'||l_prior_or_budget||',
NULL FII_MEASURE14,
NULL FII_MEASURE15,
NULL FII_MEASURE16,
NULL FII_MEASURE17,
NULL FII_MEASURE18
FROM DUAL
WHERE 1=2 ';
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,
f.viewby_id VIEWBYID,
to_number(null) FII_MEASURE13,
sum(CY_ACTUAL) FII_MEASURE2,
sum(CY_FORECAST) FII_MEASURE5,
sum(CY_BUDGET) FII_MEASURE7,
sum(CY_ACTUAL) FII_MEASURE9,
sum(PY_SPER_END) FII_ATTRIBUTE4,
sum(sum(CY_ACTUAL)) over() '||GT_CY_ACT||',
'||GT_PY_ACT||'
sum(sum(CY_FORECAST)) over() '||GT_CY_FORE||',
'||GT_CY_BUD||'
sum(sum(CY_ACTUAL)) over() /
NULLIF(sum(sum(CY_FORECAST)) over(),0) * 100 '||GT_FORE_ACT||',
(sum(sum(CY_FORECAST)) over() - sum(sum(CY_BUDGET)) over()) /
NULLIF(sum(sum(CY_BUDGET)) over(),0) * 100 '||GT_FORE_BUD||',
(sum(sum(CY_FORECAST)) over() - sum(sum(PY_SPER_END)) over()) /
ABS(NULLIF(sum(sum(PY_SPER_END)) over(),0)) * 100 '||GT_FORE_PY||','||l_prior_or_budget||',
decode('||NVL(fii_gl_util_pkg.g_mgr_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE14,
decode('||NVL(fii_gl_util_pkg.g_fin_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE15,
decode('||NVL(fii_gl_util_pkg.g_lob_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE16,
decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','''||l_url2||''',''''), '''||l_url2||''') FII_MEASURE17,
decode(:L_ID, f.viewby_id,decode(:DIM_FLAG,''Y'','''||l_url2||''',''''), '''||l_url2||''') FII_MEASURE18
from '||fii_gl_util_pkg.g_viewby_from_clause||',
(select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
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,
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,
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,
to_number(NULL) PY_ACTUAL,
to_number(NULL) PY_SPER_END
from fii_time_rpt_struct cal
'||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||fii_gl_util_pkg.g_ccc_from_clause||'
where cal.report_date = &BIS_CURRENT_ASOF_DATE
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||'
'||fii_gl_util_pkg.g_gid||'
and cal.period_type_id = f.period_type_id
and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
group by '||fii_gl_util_pkg.g_viewby_id||'
union all
select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
to_number(NULL) CY_ACTUAL,
to_number(NULL) CY_FORECAST,
to_number(NULL) CY_BUDGET,
sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
then f.actual_g
else to_number(NULL) end) PY_ACTUAL,
sum(case when bitand(cal.record_type_id, :ENT_PERIOD_TYPE) = cal.record_type_id
then f.actual_g
else to_number(NULL) end) PY_SPER_END
from fii_time_rpt_struct cal
'||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||fii_gl_util_pkg.g_ccc_from_clause||'
where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
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|| '
'||fii_gl_util_pkg.g_gid||'
and cal.period_type_id = f.period_type_id
and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
group by '||fii_gl_util_pkg.g_viewby_id||' ) f
where '||fii_gl_util_pkg.g_viewby_join||'
group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
order by NVL(FII_MEASURE2, -999999999) desc';
select '||fii_gl_util_pkg.g_viewby_value||' VIEWBY,
f.viewby_id VIEWBYID,
emp.value FII_ATTRIBUTE14,
to_number(null) FII_MEASURE13,
sum(CY_ACTUAL) FII_MEASURE2,
sum(CY_FORECAST) FII_MEASURE5,
sum(CY_BUDGET) FII_MEASURE7,
sum(CY_ACTUAL) FII_MEASURE9,
sum(PY_SPER_END) FII_ATTRIBUTE4,
sum(sum(CY_ACTUAL)) over() FII_ATTRIBUTE5,
sum(sum(PY_ACTUAL)) over() FII_ATTRIBUTE6,
sum(sum(CY_FORECAST)) over() FII_ATTRIBUTE7,
sum(sum(CY_BUDGET)) over() FII_ATTRIBUTE8,
sum(sum(CY_ACTUAL)) over() /
NULLIF(sum(sum(CY_FORECAST)) over(),0) * 100 FII_ATTRIBUTE1,
(sum(sum(CY_FORECAST)) over() - sum(sum(CY_BUDGET)) over()) /
NULLIF(sum(sum(CY_BUDGET)) over(),0) * 100 FII_ATTRIBUTE2,
(sum(sum(CY_FORECAST)) over() - sum(sum(PY_SPER_END)) over()) /
ABS(NULLIF(sum(sum(PY_SPER_END)) over(),0)) * 100 FII_ATTRIBUTE12,'||l_prior_or_budget||'
from '||fii_gl_util_pkg.g_viewby_from_clause||',
hri_cs_per_orgcc_ct ct,
hri_dbi_cl_per_n_v emp,
(select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
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,
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,
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,
to_number(NULL) PY_ACTUAL,
to_number(NULL) PY_SPER_END
from fii_time_rpt_struct cal
'||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||fii_gl_util_pkg.g_ccc_from_clause||'
where cal.report_date = &BIS_CURRENT_ASOF_DATE
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||'
'||fii_gl_util_pkg.g_gid||'
and cal.period_type_id = f.period_type_id
and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
group by '||fii_gl_util_pkg.g_viewby_id||'
union all
select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
to_number(NULL) CY_ACTUAL,
to_number(NULL) CY_FORECAST,
to_number(NULL) CY_BUDGET,
sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
then f.actual_g
else to_number(NULL) end) PY_ACTUAL,
sum(case when bitand(cal.record_type_id, :ENT_PERIOD_TYPE) = cal.record_type_id
then f.actual_g
else to_number(NULL) end) PY_SPER_END
from fii_time_rpt_struct cal
'||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||fii_gl_util_pkg.g_ccc_from_clause||'
where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
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|| '
'||fii_gl_util_pkg.g_gid||'
and cal.period_type_id = f.period_type_id
and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
group by '||fii_gl_util_pkg.g_viewby_id||' ) f
where '||fii_gl_util_pkg.g_viewby_join||'
and cc.ORGANIZATION_ID = ct.ORGANIZATION_ID
and sysdate between emp.effective_start_date and emp.effective_end_date
and emp.person_id = ct.CC_MNGR_PERSON_ID
group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id, substr(emp.first_name,1,1)' || '||''.''|| ' || 'emp.last_name
&ORDER_BY_CLAUSE';
select nvl(to_char(min(ent_period_id)),fii_gl_util_pkg.g_month_id) into l_time_parameter
from fii_time_ent_period
where fii_gl_util_pkg.g_as_of_date between start_date and end_date;
sqlstmt := ' select '||fii_gl_util_pkg.g_viewby_value||' VIEWBY,
f.viewby_id VIEWBYID,
NULL FII_MEASURE5,
f.viewby_id FII_MEASURE1,
:MGR_ID FII_MEASURE6,
:MONTH_ID FII_MEASURE7,
:LOB_ID FII_MEASURE8,
:CURRENCY FII_MEASURE9,
:CCC_ID FII_MEASURE10,
sum(CY_ACTUAL) FII_MEASURE2,
sum(PY_ACTUAL) FII_MEASURE3,
decode((SELECT is_leaf_flag
FROM fii_fin_item_hierarchies
WHERE parent_fin_cat_id = f.viewby_id
and child_fin_cat_id = f.viewby_id),
''Y'','''||l_journal_src_url||''',
'''||l_cat_detail_url||''') FII_URL
from '||fii_gl_util_pkg.g_viewby_from_clause||',
(select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
sum(f.actual_g) CY_ACTUAL,
to_number(NULL) PY_ACTUAL
from fii_time_rpt_struct cal
'||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||'
where cal.report_date = to_date(:P_AS_OF, ''DD-MM-YYYY'')
/*Fix -- should be set dynamically*/
and cal.record_type_id = bitand(cal.record_type_id, '||l_bitmask||')
and f.time_id = cal.time_id
and f.period_type_id = cal.period_type_id
'||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
'||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||'
group by '||fii_gl_util_pkg.g_viewby_id||'
union all
select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
to_number(NULL) CY_ACTUAL,
sum(f.actual_g) PY_ACTUAL
from fii_time_rpt_struct cal
'||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||'
where cal.report_date = to_date(:P_PREV_AS_OF, ''DD-MM-YYYY'')
and cal.record_type_id = bitand(cal.record_type_id, '||l_bitmask||')
and f.time_id = cal.time_id
and f.period_type_id = cal.period_type_id
'||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
'||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||'
group by '||fii_gl_util_pkg.g_viewby_id||' ) f
where '||fii_gl_util_pkg.g_viewby_join||'
group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
order by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';
SELECT fii_time_api.ent_sd_lysper_end(fii_gl_util_pkg.g_as_of_date)
INTO l_lyr
FROM dual;
SELECT fii_time_api.ent_sd_lysper_end(l_lyr)
INTO l_llyr
FROM dual;
sqlstmt := ' SELECT NULL VIEWBY,
NULL FII_MEASURE1,
NULL FII_MEASURE2,
NULL FII_MEASURE3
FROM DUAL
WHERE 1=2 ';
select cy_per.name VIEWBY,
cy_per.ent_period_id FII_MEASURE1,
inline_view.cy_ptot FII_MEASURE2,
inline_view.py_ptot FII_MEASURE3
from
fii_time_ent_period cy_per,
(select inner_inline_view.fii_effective_num FII_EFFECTIVE_NUM,
sum(CY_PTOT) CY_PTOT,
sum(PY_PTOT) PY_PTOT
from
(select '||l_hint1||' per.sequence FII_EFFECTIVE_NUM,
case when per.start_date '||l_join1||' and
per.end_date < to_date(:ASOF_DATE, ''DD-MM-YYYY'')
then f.actual_g else to_number(NULL) end CY_PTOT,
case when per.end_date < to_date(:P_SD_LYR, ''DD-MM-YYYY'')
then f.actual_g else to_number(NULL) end PY_PTOT
from fii_time_ent_period per
'||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||'
where per.ent_period_id = f.time_id
'||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||'
'||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
and per.start_date '||l_join2||'
and per.end_date < to_date(:ASOF_DATE, ''DD-MM-YYYY'')
and f.period_type_id = 32
union all
select '||l_hint2||' :CURR_EFFECTIVE_SEQ FII_EFFECTIVE_NUM,
case when cal.REPORT_DATE = to_date(:ASOF_DATE, ''DD-MM-YYYY'')
then f.actual_g
else to_number(NULL)
end CY_QTOT,
case when cal.REPORT_DATE '||l_join3||'
then f.actual_g
else to_number(NULL)
end PY_QTOT
from fii_time_rpt_struct cal
'||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||'
where cal.time_id = f.time_id
'||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||'
'||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_ccc_mgr_join||'
and cal.period_type_id = f.period_type_id
and cal.report_date in (to_date(:ASOF_DATE, ''DD-MM-YYYY'') , to_date(:P_SD_LYR, ''DD-MM-YYYY''))
and bitand(cal.record_type_id, 23) = cal.record_type_id) inner_inline_view
group by inner_inline_view.fii_effective_num ) inline_view
where cy_per.start_date <= to_date(:ASOF_DATE, ''DD-MM-YYYY'')
and cy_per.start_date >= to_date(:P_SD_LYR, ''DD-MM-YYYY'')
and cy_per.sequence = inline_view.fii_effective_num (+)
order by cy_per.start_date';
cy_act_exp_select VARCHAR2(100) := NULL;
cy_prior_exp_select VARCHAR2(100) := NULL;
cy_act_exp_select := ' NVL(sum(NVL(CY_ACT_EXP,0)), 0) FII_ATTRIBUTE2,';
NVL(sum(CY_ACT_CGS), 0) FII_MEASURE3, '||cy_act_exp_select||'
(NVL(sum(CY_ACT_REV), 0) - '||l_subtractor3||')/
ABS(NULLIF(sum(CY_ACT_REV), 0)) * 100 FII_MEASURE11,
NVL(sum(sum(CY_ACT_REV)) over(), 0) - '||l_subtractor||' FII_ATTRIBUTE11,
(NVL(sum(sum(CY_ACT_REV)) over(), 0) - '||l_subtractor||') /
ABS(NULLIF(sum(sum(CY_ACT_REV)) over(),0)) * 100 FII_ATTRIBUTE12,';
cy_prior_exp_select := 'NVL(sum('||l_label||'), 0) FII_ATTRIBUTE4,';
cy_prior_exp_select := 'NULL FII_ATTRIBUTE4,';
cy_act_exp_select := ' NULL FII_ATTRIBUTE2,';
NULL FII_MEASURE3, '||cy_act_exp_select||'
NULL FII_MEASURE11,
NULL FII_ATTRIBUTE11,
NULL FII_ATTRIBUTE12,';
SELECT NULL VIEWBY,
NULL VIEWBYID,
'||sqlstmt1||'
NULL FII_CAL1,
NULL FII_CAL2,
'||cy_prior_exp_select||'
NULL FII_MEASURE4,
NULL FII_MEASURE5,
NULL FII_ATTRIBUTE13,
NULL FII_ATTRIBUTE14,
NULL FII_MEASURE13,
NULL FII_MEASURE14,
NULL FII_MEASURE15
FROM DUAL
WHERE 1=2 ';
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,
f.viewby_id VIEWBYID,
'||sqlstmt1||'
to_number(NULL) FII_CAL1,
to_number(NULL) FII_CAL2,
'||cy_prior_exp_select||'
NVL(sum('||l_prior||'_REV), 0) FII_MEASURE4,
NVL(sum('||l_prior||'_CGS), 0) FII_MEASURE5,
( (NVL(sum(sum(NVL(CY_ACT_REV,0))) over(), 0) - '||l_subtractor||') /
ABS(NULLIF(sum(sum(NVL(CY_ACT_REV,0))) over(),0)) -
(NVL(sum(sum(NVL('||l_prior||'_REV,0))) over(), 0) - '||l_subtractor2||') /
ABS(NULLIF(sum(sum(NVL('||l_prior||'_REV,0))) over(),0))) * 100 FII_ATTRIBUTE13,
((NVL(sum(sum(NVL(CY_ACT_REV,0))) over(), 0) - '||l_subtractor||') -
(NVL(sum(sum(NVL('||l_prior||'_REV,0))) over(), 0) - '||l_subtractor2||')) /
ABS(NULLIF((sum(sum(NVL('||l_prior||'_REV,0))) over() - '||l_subtractor2||'),0)) * 100 FII_ATTRIBUTE14,
(case when NVL(abs((NVL(sum(CY_ACT_REV), 0) - '||l_subtractor3||')/
ABS(NULLIF(sum(CY_ACT_REV), 0)) * 100), 1000) > 999.9 THEN NULL WHEN NVL(abs((NVL(sum('||l_prior||'_REV), 0) - '||l_subtractor4||')/
ABS(NULLIF(sum('||l_prior||'_REV), 0)) * 100), 1000) > 999.9 THEN NULL ELSE 0 END) FII_MEASURE13,
decode('||NVL(fii_gl_util_pkg.g_mgr_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE14,
decode('||NVL(fii_gl_util_pkg.g_lob_id, -9999)||', f.viewby_id, '''', '''||l_url||''') FII_MEASURE15
from '||fii_gl_util_pkg.g_viewby_from_clause||',
(select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''R''
then f.actual_g
else to_number(NULL) end) CY_ACT_REV,
sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''OE''
then f.actual_g
else to_number(NULL) end) CY_ACT_EXP,
sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''CGS''
then f.actual_g
else to_number(NULL) end) CY_ACT_CGS,
sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''R''
then f.budget_g
else to_number(NULL) end) CY_BUD_REV,
sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''CGS''
then f.budget_g
else to_number(NULL) end) CY_BUD_CGS,
sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''OE''
then f.budget_g
else to_number(NULL) end) CY_BUD_EXP,
to_number(NULL) PY_ACT_REV,
to_number(NULL) PY_ACT_EXP,
to_number(NULL) PY_ACT_CGS,
to_number(NULL) PYPER_ACT_REV,
to_number(NULL) PYPER_ACT_CGS
from fii_time_rpt_struct cal,
fii_fin_cat_type_assgns assgns
'||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||'
where cal.time_id = f.time_id
'||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||'
and assgns.fin_category_id= f.fin_category_id
'||fii_gl_util_pkg.g_gid||'
and cal.period_type_id = f.period_type_id
and cal.report_date = &BIS_CURRENT_ASOF_DATE
and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
group by '||fii_gl_util_pkg.g_viewby_id||'
union all
select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
to_number(NULL) CY_ACT_REV,
to_number(NULL) CY_ACT_EXP,
to_number(NULL) CY_ACT_CGS,
to_number(NULL) CY_BUD_REV,
to_number(NULL) CY_BUD_CGS,
to_number(NULL) CY_BUD_EXP,
sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''R''
then f.actual_g
else to_number(NULL) end) PY_ACT_REV,
sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''OE''
then f.actual_g
else to_number(NULL) end) PY_ACT_EXP,
sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''CGS''
then f.actual_g
else to_number(NULL) end) PY_ACT_CGS,
to_number(NULL) PYPER_ACT_REV,
to_number(NULL) PYPER_ACT_CGS
from fii_time_rpt_struct cal,
fii_fin_cat_type_assgns assgns
'||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||'
where cal.time_id = f.time_id
'||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||'
and assgns.fin_category_id = f.fin_category_id
'||fii_gl_util_pkg.g_gid||'
and cal.period_type_id = f.period_type_id
and cal.report_date = &BIS_PREVIOUS_ASOF_DATE
and bitand(cal.record_type_id, :ACT_WHERE_PERIOD_TYPE) = cal.record_type_id
group by '||fii_gl_util_pkg.g_viewby_id||') f
where '||fii_gl_util_pkg.g_viewby_join||'
group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
order by NVL(FII_MEASURE11, -9999999999) desc';