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

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

Line: 31


  l_viewby_select_str    varchar2(32000);

Line: 32


  l_viewbyid_select_str  varchar2(32000);

Line: 171


    l_viewby_select_str := 'decode(oset.resource_id, null, g.group_name, r.resource_name)';

Line: 172


    l_viewbyid_select_str := 'decode(oset.resource_id, null, to_char(oset.sales_grp_id), oset.resource_id||''.''||oset.sales_grp_id)';

Line: 181


    l_viewby_select_str := 'v.value';

Line: 182


    l_viewbyid_select_str := 'v.id';

Line: 190


    l_viewby_select_str := 'v.value';

Line: 191


    l_viewbyid_select_str := 'v.id';

Line: 199


    l_viewby_select_str := 'v.value';

Line: 200


    l_viewbyid_select_str := 'v.id';

Line: 206


 select '||l_viewby_select_str||'  VIEWBY,
'||l_viewbyid_select_str||'  VIEWBYID,
'||l_sg_drill_str||'    ISC_ATTRIBUTE_6,
'||l_col_drill_str||'
'||l_class_drill_str||'     ISC_ATTRIBUTE_13,
ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,
ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_18,ISC_MEASURE_19,ISC_MEASURE_20,ISC_MEASURE_21,ISC_MEASURE_22,ISC_MEASURE_23,ISC_MEASURE_24,ISC_MEASURE_25,
ISC_MEASURE_26,ISC_MEASURE_27,ISC_MEASURE_28,ISC_MEASURE_29,ISC_MEASURE_30,ISC_MEASURE_31,ISC_MEASURE_32,ISC_MEASURE_33,ISC_MEASURE_34,ISC_MEASURE_35,
ISC_MEASURE_36,ISC_MEASURE_38,ISC_MEASURE_39,ISC_MEASURE_40,ISC_MEASURE_41,ISC_MEASURE_43,ISC_MEASURE_44,ISC_MEASURE_45,
ISC_MEASURE_46,ISC_MEASURE_48,ISC_MEASURE_49,ISC_MEASURE_50,ISC_MEASURE_51,ISC_MEASURE_53,ISC_MEASURE_54,ISC_MEASURE_55,
ISC_MEASURE_56,ISC_MEASURE_58,ISC_MEASURE_59,ISC_MEASURE_60,ISC_MEASURE_61,ISC_MEASURE_63,ISC_MEASURE_64
   from (select '||l_viewby_col_str||',
(rank() over (&ORDER_BY_CLAUSE nulls last, '||l_viewby_col_str||'))-1  rnk,
ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,
ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_18,ISC_MEASURE_19,ISC_MEASURE_20,
ISC_MEASURE_21,ISC_MEASURE_22,ISC_MEASURE_23,ISC_MEASURE_24,ISC_MEASURE_25,
ISC_MEASURE_26,ISC_MEASURE_27,ISC_MEASURE_28,ISC_MEASURE_29,ISC_MEASURE_30,
ISC_MEASURE_31,ISC_MEASURE_32,ISC_MEASURE_33,ISC_MEASURE_34,ISC_MEASURE_35,
ISC_MEASURE_36,ISC_MEASURE_38,ISC_MEASURE_39,ISC_MEASURE_40,
ISC_MEASURE_41,ISC_MEASURE_43,ISC_MEASURE_44,ISC_MEASURE_45,
ISC_MEASURE_46,ISC_MEASURE_48,ISC_MEASURE_49,ISC_MEASURE_50,
ISC_MEASURE_51,ISC_MEASURE_53,ISC_MEASURE_54,ISC_MEASURE_55,
ISC_MEASURE_56,ISC_MEASURE_58,ISC_MEASURE_59,ISC_MEASURE_60,
ISC_MEASURE_61,ISC_MEASURE_63,ISC_MEASURE_64
  from (select '||l_viewby_col_str||',
c_ba	ISC_MEASURE_7,
(c_ba-p_ba)/decode(p_ba,0,null,abs(p_ba)) * 100	ISC_MEASURE_8,
c_nc	ISC_MEASURE_9,
c_new	ISC_MEASURE_10,
(c_new-p_new)/decode(p_new,0,null,abs(p_new)) * 100	ISC_MEASURE_11,
c_exp	ISC_MEASURE_12,
(c_exp-p_exp)/decode(p_exp,0,null,abs(p_exp)) * 100	ISC_MEASURE_13,
c_trm	ISC_MEASURE_14,
(c_trm-p_trm)/decode(p_trm,0,null,abs(p_trm)) * 100	ISC_MEASURE_15,
p_ta	ISC_MEASURE_16,
c_ta	ISC_MEASURE_17,
c_ta	ISC_MEASURE_18,
(c_ta-p_ta)/decode(p_ta,0,null,abs(p_ta)) * 100	ISC_MEASURE_19,
c_exg	ISC_MEASURE_20,
(c_exg-p_exg)/decode(p_exg,0,null,abs(p_exg)) * 100	ISC_MEASURE_21,
ct_ba	ISC_MEASURE_22,
(ct_ba-pt_ba)/decode(pt_ba,0,null,abs(pt_ba)) * 100	ISC_MEASURE_23,
ct_nc	ISC_MEASURE_24,
ct_new	ISC_MEASURE_25,
(ct_new-pt_new)/decode(pt_new,0,null,abs(pt_new)) * 100	ISC_MEASURE_26,
ct_exp	ISC_MEASURE_27,
(ct_exp-pt_exp)/decode(pt_exp,0,null,abs(pt_exp)) * 100	ISC_MEASURE_28,
ct_trm	ISC_MEASURE_29,
(ct_trm-pt_trm)/decode(pt_trm,0,null,abs(pt_trm)) * 100	ISC_MEASURE_30,
ct_ta	ISC_MEASURE_31,
(ct_ta-pt_ta)/decode(pt_ta,0,null,abs(pt_ta)) * 100	ISC_MEASURE_32,
ct_exg	ISC_MEASURE_33,
(ct_exg-pt_exg)/decode(pt_exg,0,null,abs(pt_exg)) * 100	ISC_MEASURE_34,
c_ba	ISC_MEASURE_35,
p_ba	ISC_MEASURE_36,
ct_ba	ISC_MEASURE_38,
pt_ba	ISC_MEASURE_39,
c_new	ISC_MEASURE_40,
p_new	ISC_MEASURE_41,
ct_new	ISC_MEASURE_43,
pt_new	ISC_MEASURE_44,
c_exp	ISC_MEASURE_45,
p_exp	ISC_MEASURE_46,
ct_exp	ISC_MEASURE_48,
pt_exp	ISC_MEASURE_49,
c_trm	ISC_MEASURE_50,
p_trm	ISC_MEASURE_51,
ct_trm	ISC_MEASURE_53,
pt_trm	ISC_MEASURE_54,
c_ta	ISC_MEASURE_55,
p_ta	ISC_MEASURE_56,
ct_ta	ISC_MEASURE_58,
pt_ta	ISC_MEASURE_59,
c_exg	ISC_MEASURE_60,
p_exg	ISC_MEASURE_61,
ct_exg	ISC_MEASURE_63,
pt_exg	ISC_MEASURE_64
  from (
select '||l_viewby_col_str||',
sum(c_ba1)+sum(c_ba2)-sum(c_ba3)  c_ba,
sum(p_ba1)+sum(p_ba2)-sum(p_ba3)  p_ba,
sum(c_ta1)+sum(c_ta2)-sum(c_ta3)  c_ta,
sum(p_ta1)+sum(p_ta2)-sum(p_ta3)  p_ta,
sum(c_nc) c_nc,
sum(c_new1)  c_new,
sum(p_new1)  p_new,
sum(c_exp1)-sum(c_exp2)  c_exp,
sum(p_exp1)-sum(p_exp2)  p_exp,
sum(c_trm1)  c_trm,
sum(p_trm1)  p_trm,
sum(c_exg1)-sum(c_exg2)-sum(c_exg3)+sum(c_exg4)-sum(c_exg5)+sum(c_exg6)  c_exg,
sum(p_exg1)-sum(p_exg2)-sum(p_exg3)+sum(p_exg4)-sum(p_exg5)+sum(p_exg6)  p_exg,
sum(sum(c_ba1))over()+sum(sum(c_ba2))over()-sum(sum(c_ba3))over()  ct_ba,
sum(sum(p_ba1))over()+sum(sum(p_ba2))over()-sum(sum(p_ba3))over()  pt_ba,
sum(sum(c_ta1))over()+sum(sum(c_ta2))over()-sum(sum(c_ta3))over()  ct_ta,
sum(sum(p_ta1))over()+sum(sum(p_ta2))over()-sum(sum(p_ta3))over()  pt_ta,
sum(sum(c_nc)) over() ct_nc,
sum(sum(c_new1))over()  ct_new,
sum(sum(p_new1))over()  pt_new,
sum(sum(c_exp1))over()-sum(sum(c_exp2))over()  ct_exp,
sum(sum(p_exp1))over()-sum(sum(p_exp2))over()  pt_exp,
sum(sum(c_trm1))over()  ct_trm,
sum(sum(p_trm1))over()  pt_trm,
sum(sum(c_exg1))over()-sum(sum(c_exg2))over()-sum(sum(c_exg3))over()+sum(sum(c_exg4))over()
- sum(sum(c_exg5))over()+sum(sum(c_exg6))over()  ct_exg,
sum(sum(p_exg1))over()-sum(sum(p_exg2))over()-sum(sum(p_exg3))over()+sum(sum(p_exg4))over()
- sum(sum(p_exg5))over()+sum(sum(p_exg6))over()  pt_exg
  from (
select '||l_viewby_col_str||',
decode(t.report_date, &BIS_CURRENT_EFFECTIVE_START_DATE-1, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  c_ba1,
decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_START_DATE-1, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  p_ba1,
0 c_ba2, 0 p_ba2, 0 c_ba3, 0 p_ba3,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  c_ta1,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  p_ta1,
0 c_ta2, 0 p_ta2, 0 c_ta3, 0 p_ta3, 0 c_nc, 0 c_new1, 0 p_new1, 0 c_exp1, 0 p_exp1, 0 c_exp2,
0 p_exp2, 0 c_trm1, 0 p_trm1, 0 c_exg1, 0 p_exg1, 0 c_exg2, 0 p_exg2, 0 c_exg3, 0 p_exg3, 0 c_exg4,
0 p_exg4, 0 c_exg5, 0 p_exg5, 0 c_exg6, 0 p_exg6
  from isc_sam_003_mv f, -- active balance
fii_time_day t
 where t.report_date in (&BIS_CURRENT_EFFECTIVE_START_DATE-1,&BIS_PREVIOUS_EFFECTIVE_START_DATE-1,
 &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
   and t.ent_year_id = f.ent_year_id
   and f.agg_level = :ISC_AGG_LEVEL
'||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
union all
select '||l_viewby_col_str||',
0 c_ba1, 0 p_ba1, 0 c_ba2, 0 p_ba2, 0 c_ba3, 0 p_ba3, 0 c_ta1, 0 p_ta1,
0 c_ta2, 0 p_ta2, 0 c_ta3, 0 p_ta3, 0 c_nc, 0 c_new1, 0 p_new1,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_e_t_amt_'||l_curr_suffix||',0), 0)  c_exp1,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_e_t_amt_'||l_curr_suffix||',0), 0)  p_exp1,
0 c_exp2, 0 p_exp2, 0 c_trm1, 0 p_trm1,
decode(t.report_date, &BIS_CURRENT_EFFECTIVE_END_DATE,
nvl(f.commit_calc1_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0)  c_exg1,
decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_END_DATE,
nvl(f.commit_calc1_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0)  p_exg1,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE,
nvl(f.commit_calc1_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0)  c_exg2,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE,
nvl(f.commit_calc1_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0)  p_exg2,
0 c_exg3, 0 p_exg3, 0 c_exg4, 0 p_exg4, 0 c_exg5, 0 p_exg5,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_t_e_t'||l_period_str||'_amt_'||l_curr_suffix||',0),0)  c_exp6,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_t_e_t'||l_period_str||'_amt_'||l_curr_suffix||',0),0)  p_exp6
  from isc_sam_000_mv f,  -- expiration
fii_time_rpt_struct_v t
 where f.time_id = t.time_id
   and t.report_date in (&BIS_CURRENT_EFFECTIVE_END_DATE,&BIS_PREVIOUS_EFFECTIVE_END_DATE,
      &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
   and bitand(t.record_type_id, &BIS_NESTED_PATTERN) = t.record_type_id
   and f.agg_level = :ISC_AGG_LEVEL
'||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
union all
select '||l_viewby_col_str||',
0 c_ba1, 0 p_ba1, 0 c_ba2, 0 p_ba2, 0 c_ba3, 0 p_ba3, 0 c_ta1, 0 p_ta1,
0 c_ta2, 0 p_ta2, 0 c_ta3, 0 p_ta3,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.new_agr_cnt,0), 0)  c_nc,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  c_new1,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  p_new1,
0 c_exp1, 0 p_exp1, 0 c_exp2, 0 p_exp2, 0 c_trm1, 0 p_trm1, 0 c_exg1, 0 p_exg1, 0 c_exg2, 0 p_exg2,
decode(t.report_date, &BIS_CURRENT_EFFECTIVE_END_DATE,
nvl(f.commit_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0)  c_exg3,
decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_END_DATE,
nvl(f.commit_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0)  p_exg3,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE,
nvl(f.commit_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0)  c_exg4,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE,
nvl(f.commit_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0)  p_exg4,
0 c_exg5, 0 p_exg5, 0 c_exg6, 0 p_exg6
  from isc_sam_001_mv f,  -- activation
fii_time_rpt_struct_v t
 where f.time_id = t.time_id
   and t.report_date in (&BIS_CURRENT_EFFECTIVE_END_DATE,&BIS_PREVIOUS_EFFECTIVE_END_DATE,
      &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
   and bitand(t.record_type_id, &BIS_NESTED_PATTERN) = t.record_type_id
   and f.agg_level = :ISC_AGG_LEVEL
'||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
union all
select '||l_viewby_col_str||',
0 c_ba1, 0 p_ba1,
decode(t.report_date, &BIS_CURRENT_EFFECTIVE_START_DATE-1, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  c_ba2,
decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_START_DATE-1, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  p_ba2,
0 c_ba3, 0 p_ba3, 0 c_ta1, 0 p_ta1,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  c_ta2,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  p_ta2,
0 c_ta3, 0 p_ta3, 0 c_nc, 0 c_new1, 0 p_new1, 0 c_exp1, 0 p_exp1, 0 c_exp2, 0 p_exp2, 0 c_trm1, 0 p_trm1,
0 c_exg1, 0 p_exg1, 0 c_exg2, 0 p_exg2, 0 c_exg3, 0 p_exg3, 0 c_exg4, 0 p_exg4, 0 c_exg5, 0 p_exg5,
0 c_exg6, 0 p_exg6
  from isc_sam_001_mv f,  -- activation
fii_time_rpt_struct_v t
 where f.time_id = t.time_id
   and t.report_date in (&BIS_CURRENT_EFFECTIVE_START_DATE-1,&BIS_PREVIOUS_EFFECTIVE_START_DATE-1,
 &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
   and bitand(t.record_type_id, 119) = t.record_type_id
   and f.agg_level = :ISC_AGG_LEVEL
'||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
union all
select '||l_viewby_col_str||',
0 c_ba1, 0 p_ba1, 0 c_ba2, 0 p_ba2, 0 c_ba3, 0 p_ba3, 0 c_ta1, 0 p_ta1,
0 c_ta2, 0 p_ta2, 0 c_ta3, 0 p_ta3, 0 c_nc, 0 c_new1, 0 p_new1, 0 c_exp1, 0 p_exp1,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_e_t_e'||l_period_str||'_amt_'||l_curr_suffix||',0),0)  c_exp2,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_e_t_e'||l_period_str||'_amt_'||l_curr_suffix||',0),0) p_exp2,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  c_trm1,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  p_trm1,
0 c_exg1, 0 p_exg1, 0 c_exg2, 0 p_exg2, 0 c_exg3, 0 p_exg3, 0 c_exg4, 0 p_exg4,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_t_e_e'||l_period_str||'_amt_'||l_curr_suffix||',0),0)  c_exg5,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_t_e_e'||l_period_str||'_amt_'||l_curr_suffix||',0),0) p_exg5,
0 c_exg6, 0 p_exg6
  from isc_sam_002_mv f,  -- termination
fii_time_rpt_struct_v t
 where f.time_id = t.time_id
   and t.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
   and bitand(t.record_type_id, &BIS_NESTED_PATTERN) = t.record_type_id
   and f.agg_level = :ISC_AGG_LEVEL
'||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
union all
select '||l_viewby_col_str||',
0 c_ba1, 0 p_ba1, 0 c_ba2, 0 p_ba2,
decode(t.report_date, &BIS_CURRENT_EFFECTIVE_START_DATE-1, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  c_ba3,
decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_START_DATE-1, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  p_ba3,
0 c_ta1, 0 p_ta1, 0 c_ta2, 0 p_ta2,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  c_ta3,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0)  p_ta3,
0 c_nc, 0 c_new1, 0 p_new1, 0 c_exp1, 0 p_exp1, 0 c_exp2, 0 p_exp2, 0 c_trm1, 0 p_trm1, 0 c_exg1, 0 p_exg1,
0 c_exg2, 0 p_exg2, 0 c_exg3, 0 p_exg3, 0 c_exg4, 0 p_exg4, 0 c_exg5, 0 p_exg5, 0 c_exg6, 0 p_exg6
  from isc_sam_004_mv f,  -- effective_end
fii_time_rpt_struct_v t
 where f.time_id = t.time_id
   and t.report_date in (&BIS_CURRENT_EFFECTIVE_START_DATE-1,&BIS_PREVIOUS_EFFECTIVE_START_DATE-1,
 &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
   and bitand(t.record_type_id, 119) = t.record_type_id
   and f.agg_level = :ISC_AGG_LEVEL
'||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
) oset
 group by '||l_viewby_col_str||'
) ) where isc_measure_7 <> 0
or isc_measure_8 <> 0
or isc_measure_9 <> 0
or isc_measure_10 <> 0
or isc_measure_11 <> 0
or isc_measure_12 <> 0
or isc_measure_13 <> 0
or isc_measure_14 <> 0
or isc_measure_15 <> 0
or isc_measure_18 <> 0
or isc_measure_19 <> 0
or isc_measure_20 <> 0
or isc_measure_21 <> 0
)  oset,
'||l_dim_join_str||'
    and ((oset.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
order by oset.rnk
';