The following lines contain the word 'select', 'insert', 'update' or 'delete':
select name VIEWBY,
ROUND((nvl(sum(CURR_ODONE),0)/SUM(CURR_THREADS))*100,1) BIX_EMC_ONE_DONE,
ROUND((nvl(sum(PREV_ODONE),0)/SUM(PREV_THREADS))*100,1) BIX_EMC_PRONE_DONE
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
ONE_RSLN_IN_PERIOD
else
null
end
) CURR_ODONE
,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
ONE_RSLN_IN_PERIOD
else
null
end
)
) over (order by cal.start_date) PREV_ODONE
,
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
INTERACTION_THREADS_IN_PERIOD
else
null
end
) CURR_THREADS
,
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
INTERACTION_THREADS_IN_PERIOD
else
null
end
)
) over (order by cal.start_date) PREV_THREADS
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,
ONE_RSLN_IN_PERIOD,
INTERACTION_THREADS_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_ODONE),0)/SUM(CURR_THREADS))*100,1) BIX_EMC_ONE_DONE,
ROUND((nvl(sum(PREV_ODONE),0)/SUM(PREV_THREADS))*100,1) BIX_EMC_PRONE_DONE
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
ONE_RSLN_IN_PERIOD
else
NULL
end
) CURR_ODONE,
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
INTERACTION_THREADS_IN_PERIOD
else
NULL
end
) CURR_THREADS,
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
ONE_RSLN_IN_PERIOD
else
NULL
end
) PREV_ODONE,
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
INTERACTION_THREADS_IN_PERIOD
else
NULL
end
) PREV_THREADS
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_ODONE),0)/SUM(CURR_THREADS))*100,1) BIX_EMC_ONE_DONE,
NULL BIX_EMC_PRONE_DONE
FROM
( SELECT fii604.name NAME,
sum(ONE_RSLN_IN_PERIOD) CURR_ODONE,
sum(INTERACTION_THREADS_IN_PERIOD) CURR_THREADS
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 ';