[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_cond := '(SELECT start_date FROM fii_time_ent_period WHERE ent_period_id BETWEEN :CURR_START_PERIOD_ID AND :CURR_END_PERIOD_ID)';
(SELECT SUM(DECODE(f.forecast_g,0,NULL,f.forecast_g))
FROM '||l_table_name||' g
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
WHERE f.time_id = '||l_time_id||'
'||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||'
and to_date(:ASOF_DATE,''DD-MM-YYYY'') between g.start_date and g.end_date
and f.period_type_id = '||l_period_id||'
)';
(SELECT SUM(DECODE(f.budget_g,0,to_number(NULL),f.budget_g))
FROM '||l_budget_table_name||' g
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
WHERE f.time_id = '||l_budget_time_id||'
'||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
to_date(:ASOF_DATE,''DD-MM-YYYY'') between g.start_date and g.end_date and
f.period_type_id = '||l_budget_period_id||'
)';
SELECT month_name VIEWBY,
SUM(FII_CURRENT_TD) FII_CURRENT_TD,
SUM(SUM(DECODE(FII_PRIOR_TD,-99999,NULL,FII_PRIOR_TD))) OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING) FII_PRIOR_TD,
SUM(SUM(DECODE(FII_FORECAST,-99999,NULL,FII_FORECAST))) OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING) FII_FORECAST
FROM (
SELECT FII_EFFECTIVE_NUM,
MAX(month_name) month_name ,
SUM(CURR) FII_CURRENT_TD,
DECODE(PREVIOUS,-99999,NULL,PREVIOUS ) FII_PRIOR_TD,
DECODE(CY_FOR,-99999,NULL,CY_FOR ) FII_FORECAST
FROM (
SELECT per.sequence FII_EFFECTIVE_NUM,
per.name month_name,
per.ent_period_id id,
NULL CURR,
(CASE WHEN per.end_date <= to_date(:P_PRIOR_END,''DD-MM-YYYY'') THEN
CASE f.budget_g
WHEN 0 THEN -99999 /* -99999 used to show NULL when budget
value in the DB is equal to 0 */
ELSE f.budget_g
END
ELSE NULL
END) PREVIOUS,
NULL CY_FOR
FROM fii_time_ent_period per
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
WHERE per.ent_period_id = f.time_id
'||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
per.start_date >= to_date(:P_PRIOR_START,''DD-MM-YYYY'') and
per.end_date <= to_date(:P_PRIOR_END,''DD-MM-YYYY'') and
f.period_type_id = 32
UNION ALL
(
SELECT per.sequence FII_EFFECTIVE_NUM,
per.name month_name,
per.ent_period_id id,
CASE WHEN per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
per.end_date <= to_date(:ASOF_DATE,''DD-MM-YYYY'')
THEN SUM(f.actual_g) OVER (ORDER BY per.ent_period_id ROWS UNBOUNDED PRECEDING)
ELSE to_number(NULL)
END CURR,
NULL PREVIOUS,
NULL CY_FOR
FROM fii_time_ent_period per
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
WHERE per.ent_period_id = f.time_id
'||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
per.end_date <= to_date(:ASOF_DATE,''DD-MM-YYYY'') and
f.period_type_id = 32
UNION ALL
/* the SELECT statement below makes sure that we always return revenue for all 12 months
regardless of whether all the months have data or not */
SELECT per.sequence FII_EFFECTIVE_NUM,
per.name month_name,
per.ent_period_id id,
CASE WHEN per.start_date >= to_date(:P_TEMP,''DD-MM-YYYY'') THEN to_number(NULL)
ELSE 0
END CURR,
NULL PREVIOUS,
NULL CY_FOR
FROM fii_time_ent_period per
WHERE per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
per.end_date <= to_date(:P_CURR_END,''DD-MM-YYYY'')
UNION ALL
SELECT per.sequence FII_EFFECTIVE_NUM,
per.name month_name,
per.ent_period_id id,
NULL CURR,
NULL PREVIOUS,
(CASE WHEN per.end_date <= to_date(:P_CURR_END,''DD-MM-YYYY'') THEN
CASE f.forecast_g
WHEN 0 THEN -99999 /* -99999 used to show NULL when forecast
value in the DB is equal to 0 */
ELSE f.forecast_g
END
ELSE NULL
END) CY_FOR
FROM fii_time_ent_period per
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
WHERE per.ent_period_id = f.time_id
'||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
per.end_date <= to_date(:P_CURR_END,''DD-MM-YYYY'') and
f.period_type_id = 32
)
)
GROUP BY FII_EFFECTIVE_NUM, PREVIOUS, CY_FOR
)
GROUP BY FII_EFFECTIVE_NUM, month_name
ORDER BY FII_EFFECTIVE_NUM';
SELECT VIEWBY,
SUM(FII_CURRENT_TD) FII_CURRENT_TD,
SUM(FII_PRIOR_TD) FII_PRIOR_TD,
SUM(FII_FORECAST) FII_FORECAST
FROM (
SELECT days VIEWBY,
SUM(DECODE(SIGN(report_date - to_date(:ASOF_DATE,''DD-MM-YYYY'')),1,NULL,CY_REV)) FII_CURRENT_TD ,
NULL FII_PRIOR_TD,
NULL CY_BUD,
NULL CY_FOR,
NULL FII_FORECAST
FROM(
SELECT g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||') days,
report_date,
NVL(SUM(SUM(f.actual_g)) OVER (ORDER BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+ to_number('||l_adjust1||') ROWS UNBOUNDED PRECEDING),0) CY_REV,
0 PY_REV
FROM fii_time_day g,
( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
f.manager_id(+) = :MGR_MGR_ID and
f.gid (+) = 4 and
f.period_type_id (+) = 1
'||fii_gl_util_pkg.g_cat_join||')
) f
WHERE g.report_date_julian = f.time_id (+) and
g.report_date_julian between :CURR_START_DAY_ID and :CURR_END_DAY_ID
GROUP BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||'),report_date
)
GROUP BY days
UNION ALL
SELECT days VIEWBY,
NULL FII_CURRENT_TD,
SUM(SUM(DECODE(inline_view.CY_BUD,-99999,NULL,inline_view.CY_BUD))) OVER (ORDER BY days
rows unbounded preceding) FII_PRIOR_TD,
CY_BUD,
NULL CY_FOR,
NULL FII_FORECAST
FROM
( SELECT g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||') days,
report_date test,
CASE WHEN (g.report_date IN '||l_cond||' ) THEN
CASE SUM(f.budget_g)
WHEN 0 THEN -99999
ELSE SUM(f.budget_g)
END
ELSE NULL
END CY_BUD
FROM fii_time_day g,
( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
f.manager_id(+) =:MGR_MGR_ID and
f.gid (+) = 4 and
f.period_type_id (+) = '||l_period_id2||'
'||fii_gl_util_pkg.g_cat_join||')
) f
WHERE '||l_time_id2||' = f.time_id (+) and
g.report_date_julian between :CURR_START_DAY_ID and :CURR_END_DAY_ID
GROUP BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||'), report_date
ORDER BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||')
) inline_view
GROUP BY days,CY_BUD
UNION ALL
SELECT days VIEWBY,
NULL FII_CURRENT_TD,
NULL FII_PRIOR_TD,
NULL CY_BUD,
CY_FOR,
SUM(SUM(DECODE(inline_view.CY_FOR,-99999,NULL,inline_view.CY_FOR))) OVER (ORDER BY days
ROWS UNBOUNDED PRECEDING) FII_FORECAST
FROM
( SELECT g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||') days,
report_date test,
CASE WHEN (g.report_date IN '||l_cond||' ) THEN
CASE SUM(f.forecast_g)
WHEN 0 THEN -99999
ELSE SUM(f.forecast_g)
END
ELSE NULL
END CY_FOR
FROM fii_time_day g,
( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
f.manager_id(+) =:MGR_MGR_ID and
f.gid (+) = 4 and
f.period_type_id (+) = '||l_period_id2||'
'||fii_gl_util_pkg.g_cat_join||')
) f
WHERE '||l_time_id2||' = f.time_id (+) and
g.report_date_julian between :CURR_START_DAY_ID and :CURR_END_DAY_ID
GROUP BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||'), report_date
ORDER BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||')
) inline_view
GROUP BY days, CY_FOR
)
GROUP BY VIEWBY
ORDER BY VIEWBY';
SELECT VIEWBY,
FII_CURRENT_TD,
SUM(FII_PRIOR_TD) OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING) FII_PRIOR_TD,
FII_FORECAST
FROM (
SELECT FII_EFFECTIVE_NUM,
month_name VIEWBY,
SUM(FII_CURRENT_TD) FII_CURRENT_TD,
SUM(FII_PRIOR_TD) FII_PRIOR_TD,
SUM(SUM(DECODE(FII_FORECAST,-99999,NULL,FII_FORECAST))) OVER (ORDER BY FII_EFFECTIVE_NUM
ROWS UNBOUNDED PRECEDING) FII_FORECAST
FROM (
SELECT FII_EFFECTIVE_NUM,
MAX(month_name) month_name ,
SUM(CURR) FII_CURRENT_TD,
SUM(PREVIOUS) FII_PRIOR_TD,
DECODE(CY_FOR,-99999,NULL,CY_FOR ) FII_FORECAST
FROM (
SELECT per.sequence FII_EFFECTIVE_NUM,
per.name month_name,
per.ent_period_id id,
NULL CURR,
(CASE WHEN per.end_date <= to_date(:P_PRIOR_END,''DD-MM-YYYY'') THEN f.actual_g
ELSE to_number(NULL)
END) PREVIOUS,
NULL CY_FOR
FROM fii_time_ent_period per
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
WHERE per.ent_period_id = f.time_id
'||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
per.start_date >= to_date(:P_PRIOR_START,''DD-MM-YYYY'') and
per.end_date <= to_date(:P_PRIOR_END,''DD-MM-YYYY'') and
f.period_type_id = 32
UNION ALL
(
SELECT per.sequence FII_EFFECTIVE_NUM,
per.name month_name,
per.ent_period_id id,
CASE WHEN per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
per.end_date <= to_date(:ASOF_DATE,''DD-MM-YYYY'')
THEN SUM(f.actual_g) OVER (ORDER BY per.ent_period_id ROWS UNBOUNDED PRECEDING)
ELSE to_number(NULL)
END CURR,
NULL PREVIOUS,
NULL CY_FOR
FROM fii_time_ent_period per
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
WHERE per.ent_period_id = f.time_id
'||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
per.end_date <= to_date(:ASOF_DATE,''DD-MM-YYYY'') and
f.period_type_id = 32
UNION ALL
SELECT per.sequence FII_EFFECTIVE_NUM,
per.name month_name,
per.ent_period_id id,
CASE WHEN per.start_date >= to_date(:P_TEMP,''DD-MM-YYYY'') THEN to_number(NULL)
ELSE 0
END CURR,
NULL PREVIOUS,
NULL CY_FOR
FROM fii_time_ent_period per
WHERE per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
per.end_date <= to_date(:P_CURR_END,''DD-MM-YYYY'')
UNION ALL
SELECT per.sequence FII_EFFECTIVE_NUM,
per.name month_name,
per.ent_period_id id,
NULL CURR,
NULL PREVIOUS,
(CASE WHEN per.end_date <= to_date(:P_CURR_END,''DD-MM-YYYY'') THEN
CASE f.forecast_g
WHEN 0 THEN -99999
ELSE f.forecast_g
END
ELSE NULL
END) CY_FOR
FROM fii_time_ent_period per
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
WHERE per.ent_period_id = f.time_id
'||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
per.end_date <= to_date(:P_CURR_END,''DD-MM-YYYY'') and
f.period_type_id = 32
)
)
GROUP BY FII_EFFECTIVE_NUM, CY_FOR
)
GROUP BY FII_EFFECTIVE_NUM, month_name
ORDER BY FII_EFFECTIVE_NUM)';
SELECT VIEWBY,
SUM(FII_CURRENT_TD) FII_CURRENT_TD,
SUM(FII_PRIOR_TD) FII_PRIOR_TD,
SUM(FII_FORECAST) FII_FORECAST
FROM (
SELECT VIEWBY,
SUM(FII_CURRENT_TD) FII_CURRENT_TD,
SUM(FII_PRIOR_TD) FII_PRIOR_TD,
( CASE WHEN (CY_FOR = -99999 or CY_FOR = NULL) THEN NULL
ELSE SUM(FII_FORECAST)
END
) FII_FORECAST
FROM (
SELECT days VIEWBY,
SUM(DECODE(SIGN(report_date - to_date(:ASOF_DATE,''DD-MM-YYYY'')),1,NULL,CY_REV)) FII_CURRENT_TD ,
SUM(DECODE(SIGN(report_date - to_date(:P_PRIOR_END,''DD-MM-YYYY'')),1,NULL,PY_REV)) FII_PRIOR_TD,
NULL CY_FOR,
NULL FII_FORECAST
FROM (
SELECT g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||') days,
report_date,
NVL(SUM(SUM(f.actual_g)) OVER (ORDER BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+
to_number('||l_adjust1||') ROWS UNBOUNDED PRECEDING),0) CY_REV,
0 PY_REV
FROM fii_time_day g,
( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
f.manager_id(+) = :MGR_MGR_ID and
f.gid (+) = 4 and
f.period_type_id (+) = 1
'||fii_gl_util_pkg.g_cat_join||')
) f
WHERE g.report_date_julian = f.time_id (+) and
g.report_date_julian between :CURR_START_DAY_ID and :CURR_END_DAY_ID
GROUP BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||'),report_date
UNION ALL
SELECT g.report_date-to_date(:P_PRIOR_START,''DD-MM-YYYY'')+to_number('||l_adjust2||') days,
report_date,
to_number(NULL) CY_REV,
NVL(SUM(SUM(f.actual_g)) OVER (ORDER BY g.report_date-to_date(:P_PRIOR_START,''DD-MM-YYYY'')+
to_number('||l_adjust2||') ROWS UNBOUNDED PRECEDING),0) PY_REV
FROM fii_time_day g,
( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
f.manager_id(+) = :MGR_MGR_ID and
f.gid (+) = 4 and
f.period_type_id (+) = 1
'||fii_gl_util_pkg.g_cat_join||')
) f
WHERE f.time_id (+) = g.report_date_julian and
g.report_date_julian between :PRIOR_START_DAY_ID and :PRIOR_END_DAY_ID
GROUP BY g.report_date-to_date(:P_PRIOR_START,''DD-MM-YYYY'')+to_number('||l_adjust2||'),report_date
)
GROUP BY days
UNION ALL
SELECT days VIEWBY,
NULL FII_CURRENT_TD,
NULL FII_PRIOR_TD,
CY_FOR,
SUM(SUM(DECODE(inline_view.CY_FOR,-99999,NULL,inline_view.CY_FOR))) OVER (ORDER BY days
ROWS UNBOUNDED PRECEDING) FII_FORECAST
FROM
(SELECT g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||') days,
report_date test,
CASE WHEN (g.report_date IN '||l_cond||' ) THEN
CASE SUM(f.forecast_g)
WHEN 0 THEN -99999
ELSE SUM(f.forecast_g)
END
ELSE NULL
END CY_FOR
FROM fii_time_day g,
( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
f.manager_id(+) =:MGR_MGR_ID and
f.gid (+) = 4 and
f.period_type_id (+) = '||l_period_id2||'
'||fii_gl_util_pkg.g_cat_join||')
) f
WHERE
'||l_time_id2||' = f.time_id (+) and
g.report_date_julian between :CURR_START_DAY_ID and :CURR_END_DAY_ID
GROUP BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||'), report_date
ORDER BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||')
) inline_view
GROUP BY days, CY_FOR
)
GROUP BY VIEWBY, CY_FOR
)
GROUP BY VIEWBY
ORDER BY VIEWBY';
SELECT MONTH_NAME VIEWBY ,
FII_CURRENT_TD,
CASE WHEN '''||l_if_budget||''' = ''Y'' THEN '||l_budget||'
ELSE SUM(FII_PRIOR_TD) OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING)
END FII_PRIOR_TD,
FII_FORECAST
FROM
(SELECT MAX(month_name) month_name,
FII_EFFECTIVE_NUM FII_EFFECTIVE_NUM,
SUM(CURR) FII_CURRENT_TD,
SUM(PREVIOUS) FII_PRIOR_TD,
'||l_forecast||' FII_FORECAST
FROM (
SELECT per.sequence FII_EFFECTIVE_NUM,
per.name month_name,
per.ent_period_id id,
NULL CURR,
CASE WHEN '''||fii_gl_util_pkg.g_time_comp||'''=''BUDGET'' THEN '||l_budget||'
ELSE
(CASE WHEN per.end_date <= to_date(:P_PRIOR_END,''DD-MM-YYYY'') THEN f.actual_g
ELSE to_number(NULL)
END)
END PREVIOUS
FROM fii_time_ent_period per
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
WHERE per.ent_period_id = f.time_id
'||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
per.start_date >= to_date(:P_PRIOR_START,''DD-MM-YYYY'') and
per.end_date <= to_date(:P_PRIOR_END,''DD-MM-YYYY'') and
f.period_type_id = 32
UNION ALL
(
SELECT per.sequence FII_EFFECTIVE_NUM,
per.name month_name,
per.ent_period_id id,
CASE WHEN per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and per.end_date <= to_date(:ASOF_DATE,''DD-MM-YYYY'')
THEN SUM(f.actual_g) OVER (ORDER BY per.ent_period_id ROWS UNBOUNDED PRECEDING)
ELSE to_number(NULL)
END CURR,
0 PREVIOUS
FROM fii_time_ent_period per
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
WHERE per.ent_period_id = f.time_id
'||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
per.end_date <= to_date(:ASOF_DATE,''DD-MM-YYYY'') and
f.period_type_id = 32
UNION ALL
SELECT per.sequence FII_EFFECTIVE_NUM,
per.name month_name,
per.ent_period_id id,
CASE WHEN per.start_date >= to_date(:P_TEMP,''DD-MM-YYYY'') THEN to_number(NULL)
ELSE 0
END CURR,
0 PREVIOUS
FROM fii_time_ent_period per
WHERE per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
per.end_date <= to_date(:P_CURR_END,''DD-MM-YYYY'')
)
)
GROUP BY FII_EFFECTIVE_NUM
ORDER BY FII_EFFECTIVE_NUM
)';
SELECT days VIEWBY,
SUM(DECODE(SIGN(report_date - to_date(:ASOF_DATE,''DD-MM-YYYY'')),1,NULL,CY_REV)) FII_CURRENT_TD ,
CASE WHEN '''||fii_gl_util_pkg.g_time_comp||'''=''BUDGET'' THEN '||l_budget||'
ELSE SUM(DECODE(SIGN(report_date - to_date(:P_PRIOR_END,''DD-MM-YYYY'')),1,NULL,PY_REV))
END FII_PRIOR_TD,
'||l_forecast||' FII_FORECAST
FROM(
SELECT g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||') days,
report_date,
NVL(SUM(SUM(f.actual_g)) OVER (ORDER BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||') ROWS UNBOUNDED PRECEDING),0) CY_REV,
0 PY_REV
FROM fii_time_day g,
( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
f.manager_id(+) = :MGR_MGR_ID and
f.gid (+) = 4 and
f.period_type_id (+) = 1
'||fii_gl_util_pkg.g_cat_join||')
) f
WHERE g.report_date_julian = f.time_id (+) and
g.report_date_julian between :CURR_START_DAY_ID and :CURR_END_DAY_ID
GROUP BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||'),report_date
UNION ALL
SELECT g.report_date-to_date(:P_PRIOR_START,''DD-MM-YYYY'')+to_number('||l_adjust2||') days,
report_date,
to_number(NULL) CY_REV,
NVL(SUM(SUM(f.actual_g)) OVER (ORDER BY g.report_date-to_date(:P_PRIOR_START,''DD-MM-YYYY'')+to_number('||l_adjust2||') ROWS UNBOUNDED PRECEDING),0) PY_REV
FROM fii_time_day g,
( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
f.manager_id(+) = :MGR_MGR_ID and
f.gid (+) = 4 and
f.period_type_id (+) = 1
'||fii_gl_util_pkg.g_cat_join||')
) f
WHERE g.report_date_julian = f.time_id (+) and
g.report_date_julian between :PRIOR_START_DAY_ID and :PRIOR_END_DAY_ID
GROUP BY g.report_date-to_date(:P_PRIOR_START,''DD-MM-YYYY'')+to_number('||l_adjust2||'),report_date
)
GROUP BY days
ORDER BY days';