[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
sqlstmt := 'SELECT NULL FII_MEASURE1,
NULL FII_CAL2,
NULL FII_MEASURE2,
NULL FII_MEASURE3,
NULL FII_MEASURE5,
NULL FII_MEASURE6,
NULL FII_MEASURE12,
NULL FII_ATTRIBUTE2,
NULL FII_MEASURE9,
NULL FII_MEASURE10,
NULL FII_MEASURE11
FROM DUAL
WHERE 1=2';
select
f.value FII_MEASURE1,
f.id FII_CAL2,
sum(CY_ACTUAL) FII_MEASURE2,
sum(PY_ACTUAL) FII_MEASURE3,
sum(CY_FORECAST) FII_MEASURE5,
sum(CY_ACTUAL) FII_MEASURE6,
sum(CY_BUDGET) FII_MEASURE12,
sum(PY_SPER_END) FII_ATTRIBUTE2,
sum(sum(CY_ACTUAL)) over() FII_MEASURE9,
sum(sum(PY_ACTUAL)) over() FII_MEASURE10,
sum(sum(CY_FORECAST)) over() FII_MEASURE11
from (select prod.value VALUE,
prod.id 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,
eni_item_vbh_nodes_v prod,
fii_gl_prd_v'||fii_gl_util_pkg.g_global_curr_view||' f,
fii_fin_item_hierarchies cat,
fii_cc_mgr_hierarchies h
where h.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
and h.emp_id = f.manager_id
and f.time_id = cal.time_id
and cat.parent_fin_cat_id in (select fin_category_id from fii_fin_cat_type_assgns where FIN_CAT_TYPE_CODE = ''R'' and TOP_NODE_FLAG = ''Y'')
and f.period_type_id = cal.period_type_id
and cat.child_fin_cat_id = f.fin_category_id
and f.product_category_id = prod.child_id'||l_prod_join||'
and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
and cal.report_date = &BIS_CURRENT_ASOF_DATE
group by prod.value, prod.id
union all
select prod.value VALUE,
prod.id 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,
eni_item_vbh_nodes_v prod,
fii_gl_prd_v'||fii_gl_util_pkg.g_global_curr_view||' f,
fii_fin_item_hierarchies cat,
fii_cc_mgr_hierarchies h
where h.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
and h.emp_id = f.manager_id
and f.time_id = cal.time_id
and f.period_type_id = cal.period_type_id
and cat.parent_fin_cat_id in (select fin_category_id from fii_fin_cat_type_assgns where FIN_CAT_TYPE_CODE = ''R'' and TOP_NODE_FLAG = ''Y'')
and cat.child_fin_cat_id = f.fin_category_id
and f.product_category_id = prod.child_id'||l_prod_join||'
and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
and cal.report_date = &BIS_PREVIOUS_ASOF_DATE
group by prod.value, prod.id) f
group by f.value, f.id
';