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_AGR_TREND_PKG SQL Statements

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


select
cal_name VIEWBY,
nvl(c_new,0) ISC_MEASURE_5,
(c_new-p_new)/decode(p_new,0,null,abs(p_new))*100 ISC_MEASURE_6,
nvl(c_exp,0) ISC_MEASURE_7,
(c_exp-p_exp)/decode(p_exp,0,null,abs(p_exp))*100 ISC_MEASURE_8,
nvl(c_trm,0) ISC_MEASURE_9,
(c_trm-p_trm)/decode(p_trm,0,null,abs(p_trm))*100 ISC_MEASURE_10,
nvl(c_act,0) ISC_MEASURE_11,
(c_act-p_act)/decode(p_act,0,null,abs(p_act))*100 ISC_MEASURE_12,
nvl(c_new,0) ISC_MEASURE_13,
nvl(c_exp,0) ISC_MEASURE_14,
nvl(c_trm,0) ISC_MEASURE_15,
nvl(c_act,0) ISC_MEASURE_16
from
(select
cal_name, cal_start_date,
sum(c_new) c_new,
sum(p_new) p_new,
sum(c_exp) c_exp,
sum(p_exp) p_exp,
sum(c_trm) c_trm,
sum(p_trm) p_trm,
sum(c_act) c_act,
sum(p_act) p_act
from
(
select
cal.name cal_name,
cal.start_date cal_start_date,
c_new,
p_new,
0 c_exp,
0 p_exp,
0 c_trm,
0 p_trm,
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 new_amt else null end) c_new,
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 new_amt else null end), :ISC_LAG) over (order by n.start_date) p_new
from
(
select n.start_date,
n.report_date ,
sum(f.commit_amt_'||l_curr_suffix||') new_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, &BIS_NESTED_PATTERN) = 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_new,
0 p_new,
c_exp,
p_exp,
0 c_trm,
0 p_trm,
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 exp_amt else null end) c_exp,
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 exp_amt else null end), :ISC_LAG) over (order by n.start_date) p_exp
from
(select n.start_date,
n.report_date ,
sum(f.commit_e_t_amt_'||l_curr_suffix||') exp_amt
from isc_sam_000_mv f, -- expiration
(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, &BIS_NESTED_PATTERN) = 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_new,
0 p_new,
-c_exp,
-p_exp,
c_trm,
p_trm,
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 exp_amt else null end) c_exp,
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 exp_amt else null end), :ISC_LAG) over (order by n.start_date) p_exp,
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 trm_amt else null end) c_trm,
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 trm_amt else null end), :ISC_LAG) over (order by n.start_date) p_trm
from
(select n.start_date,
n.report_date ,
sum(f.commit_e_t_e'||l_period_str||'_amt_'||l_curr_suffix||') exp_amt,
sum(f.commit_amt_'||l_curr_suffix||') trm_amt
from isc_sam_002_mv f, -- termination
(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, &BIS_NESTED_PATTERN) = 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_new,
0 p_new,
0 c_exp,
0 p_exp,
0 c_trm,
0 p_trm,
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_new,
0 p_new,
0 c_exp,
0 p_exp,
0 c_trm,
0 p_trm,
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_new,
0 p_new,
0 c_exp,
0 p_exp,
0 c_trm,
0 p_trm,
-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(+)
)
group by cal_name, cal_start_date) uset
order by cal_start_date
';