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

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

Line: 26


  l_viewby_select_str    varchar2(32000);

Line: 27


  l_viewbyid_select_str  varchar2(32000);

Line: 121


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

Line: 122


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

Line: 131


    l_viewby_select_str := 'v.value';

Line: 132


    l_viewbyid_select_str := 'v.id';

Line: 140


    l_viewby_select_str := 'v.value';

Line: 141


    l_viewbyid_select_str := 'v.id';

Line: 147


 select '||l_viewby_select_str||'  VIEWBY,
'||l_viewbyid_select_str||'  VIEWBYID,
'||l_sg_drill_str||'    ISC_ATTRIBUTE_3,
'||l_col_drill_str||'
'||l_class_drill_str||'     ISC_ATTRIBUTE_8,
ISC_MEASURE_5,ISC_MEASURE_6,ISC_MEASURE_8,ISC_MEASURE_9,
ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_15,
ISC_MEASURE_16,ISC_MEASURE_18,ISC_MEASURE_19,
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_30,
ISC_MEASURE_31,ISC_MEASURE_32,ISC_MEASURE_33,ISC_MEASURE_35,
ISC_MEASURE_36,ISC_MEASURE_37,ISC_MEASURE_38,ISC_MEASURE_40,
ISC_MEASURE_41
   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_8,ISC_MEASURE_9,
ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_15,
ISC_MEASURE_16,ISC_MEASURE_18,ISC_MEASURE_19,
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_30,
ISC_MEASURE_31,ISC_MEASURE_32,ISC_MEASURE_33,ISC_MEASURE_35,
ISC_MEASURE_36,ISC_MEASURE_37,ISC_MEASURE_38,ISC_MEASURE_40,
ISC_MEASURE_41
  from (select '||l_viewby_col_str||',
c_agr ISC_MEASURE_5,
(c_agr - p_agr) / decode(p_agr,0,null,abs(p_agr)) * 100 ISC_MEASURE_6,
c_nagr ISC_MEASURE_8,
(c_nagr - p_nagr) / decode(p_nagr,0,null,abs(p_nagr)) * 100 ISC_MEASURE_9,
c_agr+c_nagr ISC_MEASURE_11,
(c_agr+c_nagr-p_agr-p_nagr) / decode(p_agr+p_nagr,0,null,abs(p_agr+p_nagr)) * 100 ISC_MEASURE_12,
c_nagr / decode(c_agr+c_nagr,0,null,abs(c_agr+c_nagr)) * 100 ISC_MEASURE_13,
ct_agr ISC_MEASURE_15,
(ct_agr - pt_agr) / decode(pt_agr,0,null,abs(pt_agr)) * 100 ISC_MEASURE_16,
ct_nagr ISC_MEASURE_18,
(ct_nagr - pt_nagr) / decode(pt_nagr,0,null,abs(pt_nagr)) * 100 ISC_MEASURE_19,
ct_agr+ct_nagr ISC_MEASURE_21,
(ct_agr+ct_nagr-pt_agr-pt_nagr) / decode(pt_agr+pt_nagr,0,null,abs(pt_agr+pt_nagr)) * 100 ISC_MEASURE_22,
ct_nagr / decode(ct_agr+ct_nagr,0,null,abs(ct_agr+ct_nagr)) * 100 ISC_MEASURE_23,
c_agr ISC_MEASURE_24,
c_agr ISC_MEASURE_25,
c_nagr ISC_MEASURE_26,
c_agr ISC_MEASURE_27,
p_agr ISC_MEASURE_28,
ct_agr ISC_MEASURE_30,
pt_agr ISC_MEASURE_31,
c_nagr ISC_MEASURE_32,
p_nagr ISC_MEASURE_33,
ct_nagr ISC_MEASURE_35,
pt_nagr ISC_MEASURE_36,
c_agr / decode(c_agr+c_nagr,0,null,abs(c_agr+c_nagr)) * 100 ISC_MEASURE_37,
p_agr / decode(p_agr+p_nagr,0,null,abs(p_agr+p_nagr)) * 100 ISC_MEASURE_38,
ct_nagr / decode(ct_agr+ct_nagr,0,null,abs(ct_agr+ct_nagr)) * 100 ISC_MEASURE_40,
pt_nagr / decode(pt_agr+pt_nagr,0,null,abs(pt_agr+pt_nagr)) * 100 ISC_MEASURE_41
  from (
select '||l_viewby_col_str||',
sum(c_agr) c_agr,
sum(p_agr) p_agr,
sum(c_nagr) c_nagr,
sum(p_nagr) p_nagr,
sum(sum(c_agr))over() ct_agr,
sum(sum(p_agr))over() pt_agr,
sum(sum(c_nagr))over() ct_nagr,
sum(sum(p_nagr))over() pt_nagr
  from (
select '||l_viewby_col_str||',
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_amt_'||l_curr_suffix||',0), 0)  c_agr,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.fulfill_amt_'||l_curr_suffix||',0), 0)  p_agr,
0 c_nagr,
0 p_nagr
  from isc_sam_007_mv f,  -- agreement 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, &BIS_NESTED_PATTERN) = t.record_type_id
   and f.agg_level = :ISC_AGG_LEVEL
'||l_sg_where||l_class_where||l_cust_where||'
union all
select '||l_viewby_col_str||',
0 c_agr,
0 p_agr,
decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_amt_'||l_curr_suffix||',0), 0)  c_nagr,
decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.fulfill_amt_'||l_curr_suffix||',0), 0)  p_nagr
  from isc_sam_006_mv f,  -- non-agreement 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, &BIS_NESTED_PATTERN) = t.record_type_id
   and f.agg_level = :ISC_AGG_LEVEL
'||l_sg_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_8 <> 0
or isc_measure_9 <> 0
)  oset,
'||l_dim_join_str||'
    and ((oset.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
order by oset.rnk
';