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

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

Line: 28


      select f.record_type,f.parent_district_id
       ,decode(f.record_type, ''GROUP'', f.district_id, f.resource_id ) district_id
       ,district_id_c,f.inv_category_id,f.item_org_id,f.time_id,f.period_type_id,f.onhand_value_g  Inv_usg_g,f.onhand_value_sg Inv_usg_sg
      from isc_fs_015_mv f where f.grp_id = &ISC_GRP_ID)';

Line: 44


      select
        f.record_type,f.parent_district_id
       ,decode(f.record_type, ''GROUP'', f.district_id, f.resource_id ) district_id
       ,district_id_c
       ,f.inv_category_id,f.item_org_id,f.time_id,f.period_type_id,f.uonhand_value_g
       ,f.uonhand_value_sg,f.donhand_value_g,f.donhand_value_sg,nvl(f.uonhand_value_g,0) + nvl(f.donhand_value_g,0) totalonhand_value_g
       ,nvl(f.uonhand_value_sg,0) + nvl(f.donhand_value_sg,0) totalonhand_value_sg
      from isc_fs_016_mv f where f.grp_id = &ISC_GRP_ID)';

Line: 58


      return '(select decode(grouping_id(c.ent_year_id,c.ent_qtr_id,c.ent_period_id,c.week_id,c.report_date_julian),0,c.report_date_julian,1,c.week_id,3,c.ent_period_id,7,c.ent_qtr_id,15,c.ent_year_id) time_id
      ,decode(grouping_id(c.ent_year_id,c.ent_qtr_id,c.ent_period_id,c.week_id,c.report_date_julian),0,1,1,16,3,32,7,64,15,128) period_type_id
      ,sum(CASE WHEN (t.start_date <= &ISC_TO_DATE and t.end_date >= &ISC_TO_DATE) and t.flag = ''C'' THEN &ISC_TO_DATE - t.start_date + 1
             WHEN (t.start_date <= &BIS_PREVIOUS_ASOF_DATE and t.end_date >= &BIS_PREVIOUS_ASOF_DATE) and t.flag=''C'' THEN &BIS_PREVIOUS_ASOF_DATE-t.start_date+1
            WHEN (t.flag=''P'') THEN t.end_date-&BIS_PREVIOUS_ASOF_DATE ' || /* Do not need +1 because start date row "C" already has it */
             'ELSE t.end_date-t.start_date+1 END) period_days' ||
      ' from fii_time_day c
      ,(select ' || /* need this row to complete current number of days of the period before current period in the trend */
       'tt.end_date start_date, tt.end_date end_date, ''P'' flag
       from PERIOD_TABLE tt
       where tt.start_date <= &BIS_PREVIOUS_ASOF_DATE and tt.end_date >= &BIS_PREVIOUS_ASOF_DATE
       union all
       select start_date,end_date,''C'' flag from PERIOD_TABLE ttt) t
      where  c.report_date = t.start_date and t.start_date <= &ISC_TO_DATE group by t.start_date,c.ent_year_id,rollup(c.ent_qtr_id,c.ent_period_id,c.week_id,c.report_date_julian))';

Line: 79


      select
       decode(grouping_id(c.ent_year_id, c.ent_qtr_id, c.ent_period_id, c.week_id, c.report_date_julian), 0, c.report_date_julian, 1, c.week_id, 3, c.ent_period_id, 7, c.ent_qtr_id, 15, c.ent_year_id) time_id
      ,decode(grouping_id(c.ent_year_id, c.ent_qtr_id , c.ent_period_id, c.week_id, c.report_date_julian), 0, 1, 1, 16, 3, 32, 7, 64, 15, 128) period_type_id
      ,sum(CASE
             WHEN (t.start_date <= &ISC_TO_DATE and t.end_date >= &ISC_TO_DATE) THEN
               &ISC_TO_DATE - t.start_date + 1
             ELSE t.end_date - t.start_date + 1
           END) period_days
      from
        fii_time_day c
        ,PERIOD_TABLE t
      where
        c.report_date = t.start_date and t.start_date <= &ISC_TO_DATE
      group by
       t.start_date
      , c.ent_year_id
      ,rollup(c.ent_qtr_id, c.ent_period_id, c.week_id, c.report_date_julian))';

Line: 117


  l_viewby_select    varchar2(400); -- needed to be increased from 200

Line: 160


  , x_viewby_select    => l_viewby_select
  , x_join_tbl         => l_join_tbl
  , x_dim_bmap         => l_dim_bmap
  , x_comparison_type  => l_comparison_type
  , x_xtd              => l_xtd
  );

Line: 275


  l_stmt := 'select
  ' || l_viewby_select || '
, ' ||
   case l_view_by
      when isc_fs_rpt_util_pkg.G_ITEM_ORG
      then 'v12.description'
      else 'null'
   end ||'
 ISC_ATTRIBUTE_5
, ISC_MEASURE_1
, ISC_MEASURE_26 ISC_MEASURE_2
, ISC_MEASURE_7
, ISC_MEASURE_27 ISC_MEASURE_8
, ISC_MEASURE_11
, ISC_MEASURE_30
, ISC_MEASURE_31
, ISC_MEASURE_26
, ISC_MEASURE_3
, ISC_MEASURE_5
, ISC_MEASURE_6
, ISC_MEASURE_27
, ISC_MEASURE_9
, ISC_MEASURE_12
, ISC_MEASURE_13
, 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_16 ISC_MEASURE_39
, ISC_MEASURE_21 ISC_MEASURE_40
, ' || isc_fs_rpt_util_pkg.get_district_drill_down
       ( l_view_by
       , 'ISC_FS_INV_DOH_TBL_REP'
       , 'ISC_ATTRIBUTE_4' ) || '
from (
select
  row_number() over(&ORDER_BY_CLAUSE nulls last, '|| isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
, iset.*
from ( select * from (
select ' || isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || '
, nvl(p_Inv_usg,0)  ISC_MEASURE_1
, nvl(c_Inv_usg,0) ISC_MEASURE_26
, ' || isc_fs_rpt_util_pkg.change_column
       ( 'c_Inv_usg'
       , 'p_Inv_usg'
       , 'ISC_MEASURE_3' ) || '
, nvl(c_uonhand_value,0)  ISC_MEASURE_5
, nvl(c_donhand_value,0) ISC_MEASURE_6
, nvl(p_totalonhand_value,0) ISC_MEASURE_7
, nvl(c_totalonhand_value,0) ISC_MEASURE_27
, ' || isc_fs_rpt_util_pkg.change_column
       ( 'c_totalonhand_value'
       , 'p_totalonhand_value'
       , 'ISC_MEASURE_9' ) || '
, p_uonhand_value/(decode(p_Inv_usg, 0, null, decode(&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1,0,null,p_Inv_usg/(&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1)))) ISC_MEASURE_11
, case when &BIS_PREVIOUS_ASOF_DATE <= &ISC_TO_DATE then (case when (&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) end)
else (case when (&ISC_TO_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&ISC_TO_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) end) end ISC_MEASURE_30
, case when (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) < 0 then 0
  else &ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1 end ISC_MEASURE_31
, c_uonhand_value*(case when (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) end)/(decode(c_Inv_usg, 0, null, c_Inv_usg)) ISC_MEASURE_12
, c_uonhand_value*(case when (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) end)/(decode(c_Inv_usg, 0, null, c_Inv_usg)) -
p_uonhand_value*(&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1)/(decode(p_Inv_usg, 0, null, p_Inv_usg)) ISC_MEASURE_13
, nvl(p_Inv_usg_total,0) ISC_MEASURE_15
, nvl(c_Inv_usg_total,0) ISC_MEASURE_16
, ' || isc_fs_rpt_util_pkg.change_column
       ( 'c_Inv_usg_total'
       , 'p_Inv_usg_total'
       , 'ISC_MEASURE_17' ) || '
, nvl(c_uonhand_value_total, 0) ISC_MEASURE_18
, nvl(c_donhand_value_total, 0) ISC_MEASURE_19
, nvl(p_totalonhand_value_total, 0) ISC_MEASURE_20
, nvl(c_totalonhand_value_total, 0) ISC_MEASURE_21
, ' || isc_fs_rpt_util_pkg.change_column
       ( 'c_totalonhand_value_total'
       , 'p_totalonhand_value_total'
       , 'ISC_MEASURE_22' ) || '
, p_uonhand_value_total*(case when &BIS_PREVIOUS_ASOF_DATE <= &ISC_TO_DATE then (case when (&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&BIS_PREVIOUS_ASOF_DATE -
&BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) end) else (case when (&ISC_TO_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&ISC_TO_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) end)
end)/(decode(p_Inv_usg_total, 0, null, p_Inv_usg_total)) ISC_MEASURE_23
, c_uonhand_value_total*(case when (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) end)/(decode(c_Inv_usg_total, 0, null,c_Inv_usg_total)) ISC_MEASURE_24
, c_uonhand_value_total*(case when (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) end)/(decode(c_Inv_usg_total,
0, null,c_Inv_usg_total)) - p_uonhand_value_total*(case when &BIS_PREVIOUS_ASOF_DATE <= &ISC_TO_DATE then (case when (&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) < 0 then 0
else (&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) end)
else (case when (&ISC_TO_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&ISC_TO_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) end) end)/(decode(p_Inv_usg_total, 0, null, p_Inv_usg_total)) ISC_MEASURE_25
from (' || l_stmt;

Line: 481


  l_viewby_select    varchar2(400); -- needed to be increased from 200

Line: 525


  , x_viewby_select    => l_viewby_select
  , x_join_tbl         => l_join_tbl
  , x_dim_bmap         => l_dim_bmap
  , x_comparison_type  => l_comparison_type
  , x_xtd              => l_xtd
  );

Line: 596


                             , p_to_date_type => l_to_date_type /* This affects only the select list of columns structure for getting current and prior values */
                             );

Line: 604


                             , p_to_date_type => l_to_date_type /* This affects only the select list of columns structure for getting current and prior values */
                             );

Line: 674


  l_stmt := 'select
  cal_name VIEWBY
, nvl(p_totalonhand_value,0) ISC_MEASURE_1
, nvl(p_Inv_usg,0)  ISC_MEASURE_7
, (p_uonhand_value*' || get_period_days(l_xtd ,'P') ||')/decode(p_Inv_usg,0,null,p_Inv_usg) ISC_MEASURE_11
, ' || get_period_days(l_xtd ,'P')  || ' ISC_MEASURE_30
, ' || get_period_days(l_xtd ,'C')  || ' ISC_MEASURE_31
, nvl(c_totalonhand_value,0) ISC_MEASURE_2
, ' || isc_fs_rpt_util_pkg.change_column
       ( 'c_totalonhand_value'
       , 'p_totalonhand_value'
       , 'ISC_MEASURE_3' ) || '
, nvl(c_Inv_usg,0) ISC_MEASURE_8
, ' || isc_fs_rpt_util_pkg.change_column
       ( 'c_Inv_usg'
       , 'p_Inv_usg'
       , 'ISC_MEASURE_9' ) || '
, (c_uonhand_value*' || get_period_days(l_xtd ,'C') ||')/decode(c_Inv_usg,0,null,c_Inv_usg) ISC_MEASURE_12
, (c_uonhand_value*' || get_period_days(l_xtd ,'C') ||')/decode(c_Inv_usg,0,null,c_Inv_usg) - (p_uonhand_value*' || get_period_days(l_xtd ,'P') ||')/decode(p_Inv_usg,0,null,p_Inv_usg) ISC_MEASURE_13
' ||
  isc_fs_rpt_util_pkg.get_trend_drill
  ( l_xtd
  , 'ISC_FS_INV_TRD_REP'
  , 'ISC_ATTRIBUTE_4'
  , 'ISC_ATTRIBUTE_5'
  , p_override_end_date =>  'cal_end_date'
  ) || '
from
  ' || l_stmt;