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_PRF_COM_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: 165


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

Line: 166


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

Line: 174


    l_viewby_select_str := 'v.value';

Line: 175


    l_viewbyid_select_str := 'v.id';

Line: 182


    l_viewby_select_str := 'v.value';

Line: 183


    l_viewbyid_select_str := 'v.id';

Line: 190


    l_viewby_select_str := 'v.value';

Line: 191


    l_viewbyid_select_str := 'v.id';

Line: 197


 select '||l_viewby_select_str||'  VIEWBY,
'||l_viewbyid_select_str||'  VIEWBYID,
'||l_sg_drill_str||'	ISC_ATTRIBUTE_1,
'||l_class_drill_str||'     ISC_ATTRIBUTE_6,
'||l_col_drill_str||'
ISC_MEASURE_5,ISC_MEASURE_6,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_32,ISC_MEASURE_33
   from (select '||l_viewby_col_str||',
(rank() over (&ORDER_BY_CLAUSE nulls last, '||l_viewby_col_str||'))-1  rnk,
ISC_MEASURE_5,ISC_MEASURE_6,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_32,ISC_MEASURE_33
  from (select '||l_viewby_col_str||',
c_ta ISC_MEASURE_5,
c_to ISC_MEASURE_6,
c_exg ISC_MEASURE_7,
c_ego ISC_MEASURE_8,
c_trm ISC_MEASURE_9,
c_tmo ISC_MEASURE_10,
c_exp ISC_MEASURE_11,
c_epo ISC_MEASURE_12,
sum(c_ta)over() ISC_MEASURE_13,
sum(c_to)over() ISC_MEASURE_14,
sum(c_exg)over() ISC_MEASURE_15,
sum(c_ego)over() ISC_MEASURE_16,
sum(c_trm)over() ISC_MEASURE_17,
sum(c_tmo)over() ISC_MEASURE_18,
sum(c_exp)over() ISC_MEASURE_19,
sum(c_epo)over() ISC_MEASURE_20,
c_to ISC_MEASURE_21,
c_ta-c_to ISC_MEASURE_22,
c_ego ISC_MEASURE_23,
c_exg-c_ego ISC_MEASURE_24,
c_tmo ISC_MEASURE_25,
c_trm-c_tmo ISC_MEASURE_26,
c_epo ISC_MEASURE_27,
c_exp-c_epo ISC_MEASURE_28,
c_ego ISC_MEASURE_29,
p_ego ISC_MEASURE_30,
sum(c_ego)over() ISC_MEASURE_32,
sum(p_ego)over() ISC_MEASURE_33
  from (
select '||l_viewby_col_str||',
sum(c_ta1)+sum(c_ta2)-sum(c_ta3) c_ta,
sum(c_ta1)+sum(c_ta2)-sum(c_ta3)-sum(c_to1)+sum(c_to2) c_to,
sum(p_ta1)+sum(p_ta2)-sum(p_ta3)-sum(p_to1)+sum(p_to2) p_to,
sum(c_exg1)-sum(c_exg2)-sum(c_exg3)+sum(c_exg4)-sum(c_exg5) c_exg,
sum(c_exg1)-sum(c_exg2)-sum(c_exg3)+sum(c_exg4)-sum(c_exg5)-sum(c_ego1)+sum(c_ego2)+sum(c_ego3)-sum(c_ego4)+sum(c_ego5) c_ego,
sum(p_exg1)-sum(p_exg2)-sum(p_exg3)+sum(p_exg4)-sum(p_exg5)-sum(p_ego1)+sum(p_ego2)+sum(p_ego3)-sum(p_ego4)+sum(p_ego5) p_ego,
sum(c_trm1) c_trm,
sum(c_trm1)-sum(c_tmo1)-sum(c_tmo2) c_tmo,
sum(c_exp1)-sum(c_exp2) c_exp,
sum(c_exp1)-sum(c_exp2)-sum(c_epo1)+sum(c_epo2)-sum(c_epo3) c_epo
  from (
select '||l_viewby_col_str||',
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_ta1,
0 c_ta2, 0 c_ta3,
0 c_to1, 0 c_to2,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) p_ta1,
0 p_ta2, 0 p_ta3, 0 p_to1, 0 p_to2,
0 c_exg1, 0 c_exg2, 0 c_exg3, 0 c_exg4, 0 c_exg5,
0 c_ego1, 0 c_ego2, 0 c_ego3, 0 c_ego4, 0 c_ego5,
0 p_exg1, 0 p_exg2, 0 p_exg3, 0 p_exg4, 0 p_exg5,
0 p_ego1, 0 p_ego2, 0 p_ego3, 0 p_ego4, 0 p_ego5,
0 c_trm1,
0 c_tmo1, 0 c_tmo2,
0 c_exp1, 0 c_exp2,
0 c_epo1, 0 c_epo2, 0 c_epo3
  from isc_sam_003_mv f, -- active balance
fii_time_day t
 where t.report_date in (&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_ta1,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_ta2,
0 c_ta3,
0 c_to1, 0 c_to2,
0 p_ta1,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) p_ta2,
0 p_ta3, 0 p_to1, 0 p_to2,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0 c_trm1,
0 c_tmo1, 0 c_tmo2,
0 c_exp1, 0 c_exp2,
0 c_epo1, 0 c_epo2, 0 c_epo3
  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_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_ta1, 0 c_ta2,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_ta3,
0 c_to1, 0 c_to2,
0 p_ta1, 0 p_ta2,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) p_ta3,
0 p_to1, 0 p_to2,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0 c_trm1,
0 c_tmo1, 0 c_tmo2,
0 c_exp1, 0 c_exp2,
0 c_epo1, 0 c_epo2, 0 c_epo3
  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_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_ta1, 0 c_ta2, 0 c_ta3,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_f_ee_amt_'||l_curr_suffix||',0), 0) c_to1,
0 c_to2,
0 p_ta1, 0 p_ta2, 0 p_ta3,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.fulfill_out_f_ee_amt_'||l_curr_suffix||',0), 0) p_to1,
0 p_to2,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0 c_trm1,
0 c_tmo1, 0 c_tmo2,
0 c_exp1, 0 c_exp2,
0 c_epo1, 0 c_epo2, 0 c_epo3
  from isc_sam_005_mv f, -- fulfilled
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, 1143) = 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_ta1, 0 c_ta2, 0 c_ta3,
0 c_to1,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_f_ee_amt_'||l_curr_suffix||',0), 0) c_to2,
0 p_ta1, 0 p_ta2, 0 p_ta3, 0 p_to1,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.fulfill_out_f_ee_amt_'||l_curr_suffix||',0), 0) p_to2,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0 c_trm1,
0 c_tmo1, 0 c_tmo2,
0 c_exp1, 0 c_exp2,
0 c_epo1, 0 c_epo2, 0 c_epo3
  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_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
   and bitand(t.record_type_id, 1143) = 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_ta1, 0 c_ta2, 0 c_ta3,
0 c_to1, 0 c_to2,
0 p_ta1, 0 p_ta2, 0 p_ta3, 0 p_to1, 0 p_to2,
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_CURRENT_ASOF_DATE,
nvl(f.commit_calc1_t'||l_period_str||'_amt_'||l_curr_suffix||',0)-nvl(f.commit_t_e_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_exg2,
0 c_exg3, 0 c_exg4, 0 c_exg5,
decode(t.report_date, &BIS_CURRENT_EFFECTIVE_END_DATE, nvl(f.fulfill_out_calc2_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_ego1,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE,
nvl(f.fulfill_out_calc2_t'||l_period_str||'_amt_'||l_curr_suffix||',0)-nvl(f.fulfill_out_f_t_e_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_ego2,
0 c_ego3, 0 c_ego4, 0 c_ego5,
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_PREVIOUS_ASOF_DATE,
nvl(f.commit_calc1_t'||l_period_str||'_amt_'||l_curr_suffix||',0)-nvl(f.commit_t_e_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_exg2,
0 p_exg3, 0 p_exg4, 0 p_exg5,
decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_END_DATE, nvl(f.fulfill_out_calc2_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_ego1,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE,
nvl(f.fulfill_out_calc2_t'||l_period_str||'_amt_'||l_curr_suffix||',0)-nvl(f.fulfill_out_f_t_e_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_ego2,
0 p_ego3, 0 p_ego4, 0 p_ego5,
0 c_trm1,
0 c_tmo1, 0 c_tmo2,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_e_t_amt_'||l_curr_suffix||',0), 0) c_exp1,
0 c_exp2,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_f_e_t_amt_'||l_curr_suffix||',0), 0) c_epo1,
0 c_epo2, 0 c_epo3
  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_CURRENT_ASOF_DATE,&BIS_PREVIOUS_EFFECTIVE_END_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_ta1, 0 c_ta2, 0 c_ta3,
0 c_to1, 0 c_to2,
0 p_ta1, 0 p_ta2, 0 p_ta3, 0 p_to1, 0 p_to2,
0 c_exg1, 0 c_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_CURRENT_ASOF_DATE, nvl(f.commit_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_exg4,
0 c_exg5,
0 c_ego1, 0 c_ego2, 0 c_ego3, 0 c_ego4, 0 c_ego5,
0 p_exg1, 0 p_exg2,
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_PREVIOUS_ASOF_DATE, nvl(f.commit_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_exg4,
0 p_exg5,
0 p_ego1, 0 p_ego2, 0 p_ego3, 0 p_ego4, 0 p_ego5,
0 c_trm1,
0 c_tmo1, 0 c_tmo2,
0 c_exp1, 0 c_exp2,
0 c_epo1, 0 c_epo2, 0 c_epo3
  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_CURRENT_ASOF_DATE,&BIS_PREVIOUS_EFFECTIVE_END_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_ta1, 0 c_ta2, 0 c_ta3,
0 c_to1, 0 c_to2,
0 p_ta1, 0 p_ta2, 0 p_ta3, 0 p_to1, 0 p_to2,
0 c_exg1, 0 c_exg2, 0 c_exg3, 0 c_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,
0 c_ego1, 0 c_ego2, 0 c_ego3, 0 c_ego4,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_f_t_e_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_ego5,
0 p_exg1, 0 p_exg2, 0 p_exg3, 0 p_exg4,
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 p_ego1, 0 p_ego2, 0 p_ego3, 0 p_ego4,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.fulfill_out_f_t_e_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_ego5,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_trm1,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_f_t_amt_'||l_curr_suffix||',0), 0) c_tmo1,
0 c_tmo2,
0 c_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,
0 c_epo1,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_ef_t_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_epo2,
0 c_epo3
  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_ta1, 0 c_ta2, 0 c_ta3,
0 c_to1, 0 c_to2,
0 p_ta1, 0 p_ta2, 0 p_ta3, 0 p_to1, 0 p_to2,
0 c_exg1, 0 c_exg2, 0 c_exg3, 0 c_exg4, 0 c_exg5,
0 c_ego1, 0 c_ego2,
decode(t.report_date, &BIS_CURRENT_EFFECTIVE_END_DATE, nvl(f.fulfill_out_f_et_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_ego3,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_f_et_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_ego4,
0 c_ego5,
0 p_exg1, 0 p_exg2, 0 p_exg3, 0 p_exg4, 0 p_exg5,
0 p_ego1, 0 p_ego2,
decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_END_DATE, nvl(f.fulfill_out_f_et_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_ego3,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.fulfill_out_f_et_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_ego4,
0 p_ego5,
0 c_trm1,
0 c_tmo1,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_t_f_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_tmo2,
0 c_exp1, 0 c_exp2,
0 c_epo1, 0 c_epo2,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_e_ft_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_epo3
  from isc_sam_005_mv f,  -- fulfilled
fii_time_rpt_struct_v t
 where f.time_id = t.time_id
   and t.report_date in (&BIS_CURRENT_EFFECTIVE_END_DATE,&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_EFFECTIVE_END_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||'
) oset
 group by '||l_viewby_col_str||'
) ) where isc_measure_5 <> 0
or isc_measure_6 <> 0
or 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
)  oset,
'||l_dim_join_str||'
    and ((oset.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
order by oset.rnk
';