eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
APPS.ISC_DBI_SAM_OUT_TREND_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':
Line: 151


select
cal_name VIEWBY,
nvl(c_out,0) ISC_MEASURE_3,
(c_out-p_out)/decode(p_out,0,null,abs(p_out))*100 ISC_MEASURE_4,
nvl(c_act,0) ISC_MEASURE_5,
(c_act-p_act)/decode(p_act,0,null,abs(p_act))*100 ISC_MEASURE_6,
nvl(c_out,0) ISC_MEASURE_7,
nvl(c_act,0) ISC_MEASURE_8
from
(select
cal_name, cal_start_date,
sum(c_act)+sum(c_out) c_out,
sum(p_act)+sum(p_out) p_out,
sum(c_act) c_act,
sum(p_act) p_act
from
(
select
cal.name cal_name,
cal.start_date cal_start_date,
0 c_out,
0 p_out,
c_act,
p_act
from
(select
n.start_date,
sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
then act_amt else null end) c_act,
lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
then act_amt else null end), :ISC_LAG) over (order by n.start_date) p_act
from
(
select n.start_date,
n.report_date ,
sum(f.commit_amt_'||l_curr_suffix||') act_amt
from isc_sam_003_mv f, -- active balance
(select /*+ NO_MERGE */ n.ent_year_id,
n.report_date,
cal.start_date,
cal.end_date
from '||l_period_type||' cal,
fii_time_day n
where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
and n.report_date between cal.start_date and cal.end_date) n
where f.ent_year_id = n.ent_year_id
and f.agg_level = :ISC_AGG_LEVEL
'||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
group by n.start_date, n.report_date) i, '||l_period_type||' n
where i.start_date (+) = n.start_date
and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
group by n.start_date) iset, '||l_period_type||' cal
where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and cal.start_date = iset.start_date(+)
union all
select
cal.name cal_name,
cal.start_date cal_start_date,
0 c_out,
0 p_out,
c_act,
p_act
from
(select
n.start_date,
sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
then act_amt else null end) c_act,
lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
then act_amt else null end), :ISC_LAG) over (order by n.start_date) p_act
from
(
select n.start_date,
n.report_date ,
sum(f.commit_amt_'||l_curr_suffix||') act_amt
from isc_sam_001_mv f, -- activation
(select /*+ NO_MERGE */ n.time_id,
n.record_type_id,
n.period_type_id,
n.report_date,
cal.start_date,
cal.end_date
from '||l_period_type||' cal,
fii_time_rpt_struct_v n
where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
and n.report_date between cal.start_date and cal.end_date
and bitand(n.record_type_id, 119) = n.record_type_id) n
where f.time_id = n.time_id
and f.agg_level = :ISC_AGG_LEVEL
'||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
group by n.start_date, n.report_date) i, '||l_period_type||' n
where i.start_date (+) = n.start_date
and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
group by n.start_date) iset, '||l_period_type||' cal
where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and cal.start_date = iset.start_date(+)
union all
select
cal.name cal_name,
cal.start_date cal_start_date,
0 c_out,
0 p_out,
-c_act,
-p_act
from
(select
n.start_date,
sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
then act_amt else null end) c_act,
lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
then act_amt else null end), :ISC_LAG) over (order by n.start_date) p_act
from
(
select n.start_date,
n.report_date ,
sum(f.commit_amt_'||l_curr_suffix||') act_amt
from isc_sam_004_mv f, -- effective end
(select /*+ NO_MERGE */ n.time_id,
n.record_type_id,
n.period_type_id,
n.report_date,
cal.start_date,
cal.end_date
from '||l_period_type||' cal,
fii_time_rpt_struct_v n
where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
and n.report_date between cal.start_date and cal.end_date
and bitand(n.record_type_id, 119) = n.record_type_id) n
where f.time_id = n.time_id
and f.agg_level = :ISC_AGG_LEVEL
'||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
group by n.start_date, n.report_date) i, '||l_period_type||' n
where i.start_date (+) = n.start_date
and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
group by n.start_date) iset, '||l_period_type||' cal
where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and cal.start_date = iset.start_date(+)
union all
select
cal.name cal_name,
cal.start_date cal_start_date,
-c_out,
-p_out,
0 c_act,
0 p_act
from
(select
n.start_date,
sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
then out_amt else null end) c_out,
lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
then out_amt else null end), :ISC_LAG) over (order by n.start_date) p_out
from
(
select n.start_date,
n.report_date ,
sum(f.fulfill_out_f_ee_amt_'||l_curr_suffix||') out_amt
from isc_sam_005_mv f, -- fulfilled
(select /*+ NO_MERGE */ n.time_id,
n.record_type_id,
n.period_type_id,
n.report_date,
cal.start_date,
cal.end_date
from '||l_period_type||' cal,
fii_time_rpt_struct_v n
where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
and n.report_date between cal.start_date and cal.end_date
and bitand(n.record_type_id, 1143) = n.record_type_id) n
where f.time_id = n.time_id
and f.agg_level = :ISC_AGG_LEVEL
'||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
group by n.start_date, n.report_date) i, '||l_period_type||' n
where i.start_date (+) = n.start_date
and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
group by n.start_date) iset, '||l_period_type||' cal
where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and cal.start_date = iset.start_date(+)
union all
select
cal.name cal_name,
cal.start_date cal_start_date,
c_out,
p_out,
0 c_act,
0 p_act
from
(select
n.start_date,
sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
then out_amt else null end) c_out,
lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
then out_amt else null end), :ISC_LAG) over (order by n.start_date) p_out
from
(
select n.start_date,
n.report_date ,
sum(f.fulfill_out_f_ee_amt_'||l_curr_suffix||') out_amt
from isc_sam_004_mv f, -- effective end
(select /*+ NO_MERGE */ n.time_id,
n.record_type_id,
n.period_type_id,
n.report_date,
cal.start_date,
cal.end_date
from '||l_period_type||' cal,
fii_time_rpt_struct_v n
where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
and n.report_date between cal.start_date and cal.end_date
and bitand(n.record_type_id, 1143) = n.record_type_id) n
where f.time_id = n.time_id
and f.agg_level = :ISC_AGG_LEVEL
'||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
group by n.start_date, n.report_date) i, '||l_period_type||' n
where i.start_date (+) = n.start_date
and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
group by n.start_date) iset, '||l_period_type||' cal
where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and cal.start_date = iset.start_date(+)
)
group by cal_name, cal_start_date) uset
order by cal_start_date
';