[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select name VIEWBY,
ROUND(nvl(sum(CURR_REPLDBYGOAL),0)*100/DECODE(sum(CURR_REPLD),0,NULL,sum(CURR_REPLD)),1) BIX_EMC_MSGSGOAL,
ROUND(nvl(sum(PREV_REPLDBYGOAL),0)*100/DECODE(sum(PREV_REPLD),0,NULL,sum(PREV_REPLD)),1) BIX_EMC_PREVMSGSGOAL,
' || l_goal ||' BIX_EMC_GOAL
from
(
/* Outer most iview .Uses lag to select prior values for the corresponding year*/
select cal.name,cal.start_date,
SUM(
CASE when (cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and cal.report_date = least(cal.end_date,&BIS_CURRENT_ASOF_DATE))
then
NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0)
else
null
end
) CURR_REPLD
,lag(
SUM(
CASE WHEN (cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
and cal.report_date = least(cal.end_date,&BIS_PREVIOUS_ASOF_DATE ))
then
NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0)
else
null
end
)
) over (order by cal.start_date) PREV_REPLD
,
SUM(
CASE when (cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and cal.report_date = least(cal.end_date,&BIS_CURRENT_ASOF_DATE))
then
EMAILS_RPLD_BY_GOAL_IN_PERIOD
else
null
end
) CURR_REPLDBYGOAL
,
lag(
SUM (
CASE WHEN (cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
and cal.report_date = least(cal.end_date,&BIS_PREVIOUS_ASOF_DATE ))
then
EMAILS_RPLD_BY_GOAL_IN_PERIOD
else
null
end
)
) over (order by cal.start_date) PREV_REPLDBYGOAL
from
(
/* Selects measures for all years in time range from previous report start date to current as of date*/
select fii604.name,fii604.start_date,fii604.end_date,cal.report_Date,cal.period_type_id,cal.time_id
from fii_time_ent_year fii604, fii_time_rpt_struct cal
where
fii604.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
and cal.report_date between fii604.start_date and fii604.end_date
and cal.report_date in (least(fii604.end_date, &BIS_CURRENT_ASOF_DATE) , &BIS_PREVIOUS_ASOF_DATE)
AND bitAND(cal.record_type_id,&BIS_NESTED_PATTERN ) = cal.record_type_id
order by fii604.sequence
)cal,(
select period_type_id,time_id,emails_replied_in_period,emails_rpld_by_goal_in_period,emails_auto_replied_in_period from bix_Email_Details_mv
where row_type=:l_row_type '|| l_where_clause || '
)mv
where mv.period_type_id(+)=cal.period_type_id
and mv.time_id(+)=cal.time_id
group by cal.name,cal.start_date
) recset /*End of outermost view */
WHERE recset.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
group by name';
SELECT fii604.name VIEWBY,
ROUND(nvl(sum(CURR_REPLDBYGOAL),0)*100/DECODE(sum(CURR_REPLD),0,NULL,sum(CURR_REPLD)),1) BIX_EMC_MSGSGOAL,
ROUND(nvl(sum(PREV_REPLDBYGOAL),0)*100/DECODE(sum(PREV_REPLD),0,NULL,sum(PREV_REPLD)),1) BIX_EMC_PREVMSGSGOAL,
' || l_goal ||' BIX_EMC_GOAL
FROM
( SELECT fii604.sequence SEQUENCE,
SUM( CASE when
(
fii604.start_date between &BIS_CURRENT_REPORT_START_DATE
and &BIS_CURRENT_ASOF_DATE
and cal.report_date = least(fii604.end_date,&BIS_CURRENT_ASOF_DATE)
)
then
EMAILS_RPLD_BY_GOAL_IN_PERIOD
else
NULL
end
) CURR_REPLDBYGOAL,
SUM( CASE when
(
fii604.start_date between &BIS_CURRENT_REPORT_START_DATE
and &BIS_CURRENT_ASOF_DATE
and cal.report_date = least(fii604.end_date,&BIS_CURRENT_ASOF_DATE)
)
then
NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0)
else
NULL
end
) CURR_REPLD,
SUM( CASE when
(
fii604.start_date between &BIS_PREVIOUS_REPORT_START_DATE
and &BIS_PREVIOUS_ASOF_DATE
and cal.report_date = least(fii604.end_date,&BIS_PREVIOUS_ASOF_DATE)
)
then
EMAILS_RPLD_BY_GOAL_IN_PERIOD
else
NULL
end
) PREV_REPLDBYGOAL,
SUM( CASE when
(
fii604.start_date between &BIS_PREVIOUS_REPORT_START_DATE
and &BIS_PREVIOUS_ASOF_DATE
and cal.report_date = least(fii604.end_date,&BIS_PREVIOUS_ASOF_DATE)
)
then
NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0)
else
NULL
end
) PREV_REPLD
FROM '||l_period_type||' fii604,
bix_email_details_mv eml,
fii_time_rpt_struct cal
WHERE eml.time_id = cal.time_id
AND eml.row_type = :l_row_type
AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
AND fii604.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND
&BIS_CURRENT_ASOF_DATE
AND cal.report_date = (CASE WHEN(
fii604.start_date between
&BIS_PREVIOUS_REPORT_START_DATE and
&BIS_PREVIOUS_ASOF_DATE
)
THEN
least(fii604.end_date, &BIS_PREVIOUS_ASOF_DATE)
ELSE
least(fii604.end_date, &BIS_CURRENT_ASOF_DATE)
END
)
AND cal.period_type_id = eml.period_type_id';
SELECT fii604.name VIEWBY,
ROUND(nvl(sum(CURR_REPLDBYGOAL),0)*100/DECODE(sum(CURR_REPLD),0,NULL,sum(CURR_REPLD)),1) BIX_EMC_MSGSGOAL,
NULL BIX_EMC_PREVMSGSGOAL,
' || l_goal ||' BIX_EMC_GOAL
FROM
( SELECT fii604.name NAME,
sum(EMAILS_RPLD_BY_GOAL_IN_PERIOD) CURR_REPLDBYGOAL,
NVL(sum(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_REPLIED_IN_PERIOD),0) CURR_REPLD
FROM '||l_period_type||' fii604,
bix_email_details_mv eml,
fii_time_rpt_struct cal
WHERE eml.time_id = cal.time_id
AND eml.row_type = :l_row_type
AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
AND fii604.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND
&BIS_CURRENT_ASOF_DATE
AND cal.report_date = least(&BIS_CURRENT_ASOF_DATE,fii604.end_date)
AND cal.period_type_id = eml.period_type_id ';