[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fii2.name VIEWBY,
nvl(sum(CURR_RCVD),0) BIX_EMC_RCVD,
sum(CURR_REPLD) BIX_EMC_REPLD
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_OFFERED_IN_PERIOD
else
NULL
end
) CURR_RCVD,
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
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(fii604.end_date, &BIS_CURRENT_ASOF_DATE)
AND cal.period_type_id = eml.period_type_id ';
SELECT fii604.name VIEWBY,
nvl(sum(CURR_RCVD),0) BIX_EMC_RCVD,
sum(CURR_REPLD) BIX_EMC_REPLD
FROM
( SELECT fii1.name NAME,
sum(EMAILS_OFFERED_IN_PERIOD) CURR_RCVD,
NVL(sum(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_REPLIED_IN_PERIOD),0) CURR_REPLD
FROM '||l_period_type||' fii1,
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 fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND
&BIS_CURRENT_ASOF_DATE
AND cal.report_date = least(&BIS_CURRENT_ASOF_DATE,fii1.end_date)
AND cal.period_type_id = eml.period_type_id ';