[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select name VIEWBY,
ROUND((nvl(sum(CURR_ERT),0)/DECODE(SUM(CURR_REPLD),0,NULL,SUM(CURR_REPLD)))/3600,1) BIX_EMC_CRTIME,
ROUND((nvl(sum(PREV_ERT),0)/DECODE(SUM(PREV_REPLD),0,NULL,SUM(PREV_REPLD)))/3600,1) BIX_EMC_PRCRTIME,
' || 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
EMAIL_RESP_TIME_IN_PERIOD
else
null
end
) CURR_ERT
,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
EMAIL_RESP_TIME_IN_PERIOD
else
null
end
)
) over (order by cal.start_date) PREV_ERT
,
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
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,
EMAIL_RESP_TIME_IN_PERIOD,
EMAILS_REPLIED_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 fii2.name VIEWBY,
ROUND((nvl(sum(CURR_ERT),0)/DECODE(SUM(CURR_REPLD),0,NULL,SUM(CURR_REPLD)))/3600,1) BIX_EMC_CRTIME,
ROUND((nvl(sum(PREV_ERT),0)/DECODE(SUM(PREV_REPLD),0,NULL,SUM(PREV_REPLD)))/3600,1) BIX_EMC_PRCRTIME,
' || l_goal ||' BIX_EMC_GOAL
FROM
( SELECT fiitb.sequence SEQUENCE,
SUM( CASE when
(
fiitb.start_date between &BIS_CURRENT_REPORT_START_DATE
and &BIS_CURRENT_ASOF_DATE
and cal.report_date = least(fiitb.end_date,&BIS_CURRENT_ASOF_DATE)
)
then
EMAIL_RESP_TIME_IN_PERIOD
else
NULL
end
) CURR_ERT,
SUM( CASE when
(
fiitb.start_date between &BIS_CURRENT_REPORT_START_DATE
and &BIS_CURRENT_ASOF_DATE
and cal.report_date = least(fiitb.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
(
fiitb.start_date between &BIS_PREVIOUS_REPORT_START_DATE
and &BIS_PREVIOUS_ASOF_DATE
and cal.report_date = least(fiitb.end_date,&BIS_PREVIOUS_ASOF_DATE)
)
then
EMAIL_RESP_TIME_IN_PERIOD
else
NULL
end
) PREV_ERT,
SUM( CASE when
(
fiitb.start_date between &BIS_PREVIOUS_REPORT_START_DATE
and &BIS_PREVIOUS_ASOF_DATE
and cal.report_date = least(fiitb.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||' fiitb,
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 fiitb.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND
&BIS_CURRENT_ASOF_DATE
AND cal.report_date = (CASE WHEN(
fiitb.start_date between
&BIS_PREVIOUS_REPORT_START_DATE and
&BIS_PREVIOUS_ASOF_DATE
)
THEN
least(fiitb.end_date, &BIS_PREVIOUS_ASOF_DATE)
ELSE
least(fiitb.end_date, &BIS_CURRENT_ASOF_DATE)
END
)
AND cal.period_type_id = eml.period_type_id ';
SELECT fii2.name VIEWBY,
ROUND((nvl(sum(CURR_ERT),0)/DECODE(SUM(CURR_REPLD),0,NULL,SUM(CURR_REPLD)))/3600,1) BIX_EMC_CRTIME,
NULL BIX_EMC_PRCRTIME,
' || l_goal ||' BIX_EMC_GOAL
FROM
( SELECT fiitb.name NAME,
sum(EMAIL_RESP_TIME_IN_PERIOD) CURR_ERT,
NVL(sum(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_REPLIED_IN_PERIOD),0) CURR_REPLD
FROM '||l_period_type||' fiitb,
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 fiitb.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND
&BIS_CURRENT_ASOF_DATE
AND cal.report_date = least(&BIS_CURRENT_ASOF_DATE,fiitb.end_date)
AND cal.period_type_id = eml.period_type_id ';