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

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

Line: 21


    select
      language_code
    from icx_sessions
    where session_id = l_session_id;

Line: 49


    select attribute_code
    , replace(attribute_label_long,'''',''''||'''') attribute_label_long
    from
      ak_region_items_tl
    where region_code = 'ISC_FS_TASK_ACTIVITY_TBL'
    and region_application_id = 454
    and attribute_code in ( 'ISC_MEASURE_1', 'ISC_MEASURE_2', 'ISC_MEASURE_7' )
    and attribute_application_id = 454
    and language = g_language;

Line: 159


select
  v.top_node_flag vbh_top_node_flag
, v.parent_id vbh_parent_category_id
, v.imm_child_id vbh_child_category_id
, f.time_id
, f.period_type_id ' || case
                          when l_district_leaf_node = 'N' then '
, den.parent_prg_id parent_district_id
, decode( den.record_type, ''GROUP'', den.record_type, f.record_type ) record_type
, decode( den.record_type, ''GROUP'', den.prg_id , f.district_id ) district_id
, decode( den.record_type, ''GROUP'', to_char(den.prg_id), f.district_id_c )  district_id_c'
                          else '
, f.parent_district_id
, f.record_type
, f.district_id
, f.district_id_c'
                        end || '
, f.task_type_id
, f.customer_id
, f.product_id
, f.first_opened
, f.reopened
, nvl(f.first_opened,0)+nvl(f.reopened,0) opened
, f.closed
from
  isc_fs_003_mv f' || case
                         when l_district_leaf_node = 'N' then '
, isc_fs_002_mv den'
                       end || '
, eni_denorm_hierarchies v
, mtl_default_category_sets m
where
    m.functional_area_id = 11
and v.object_id = m.category_set_id
and v.dbi_flag = ''Y''
and v.object_type = ''CATEGORY_SET''
and f.vbh_category_id = v.child_id' || case
                                         when l_district_leaf_node = 'N' then '
and f.parent_district_id = den.rg_id'
                                       end || '
and f.grp_id = &ISC_GRP_ID' || case
                                 when l_top_node = 'Y' then '
and v.top_node_flag = ''Y'''   end || '
)';

Line: 209


select
  f.time_id
, f.period_type_id ' || case
                          when l_district_leaf_node = 'N' then '
, den.parent_prg_id parent_district_id
, decode( den.record_type, ''GROUP'', den.record_type, f.record_type ) record_type
, decode( den.record_type, ''GROUP'', den.prg_id , f.district_id ) district_id
, decode( den.record_type, ''GROUP'', to_char(den.prg_id), f.district_id_c )  district_id_c'
                          else '
, f.parent_district_id
, f.record_type
, f.district_id
, f.district_id_c'
                        end || '
, f.task_type_id
, f.customer_id
, f.product_id
, f.first_opened
, f.reopened
, nvl(f.first_opened,0)+nvl(f.reopened,0) opened
, f.closed
from
  isc_fs_003_mv f' || case
                        when l_district_leaf_node = 'N' then '
, isc_fs_002_mv den'
                      end || '
where
    f.grp_id = &ISC_GRP_ID ' || case
                                  when l_district_leaf_node = 'N' then '
and f.parent_district_id = den.rg_id'
                                end || '
)';

Line: 245


select
  f.time_id
, f.period_type_id
, f.parent_district_id
, f.record_type
, f.district_id
, f.district_id_c
, f.first_opened
, f.reopened
, nvl(f.first_opened,0)+nvl(f.reopened,0) opened
, f.closed
from isc_fs_004_mv f
)';

Line: 273


    select
      t.task_id
    , t.task_number
    , a.activity_date report_date
    , a.first_opened
    , a.reopened
    , a.closed
    , t.task_type_id
    , t.owner_id
    -- R12 resource type
    , t.owner_type
    , decode(t.first_asgn_creation_date,null,to_number(null),t.act_bac_assignee_id) assignee_id
    , decode(t.first_asgn_creation_date,null,null,t.act_bac_assignee_type) assignee_type' ||
      case
        when l_resource = 'N' then '
    , d.parent_prg_id parent_district_id '
        else '
    , t.act_bac_assignee_id || ''.'' || t.act_bac_district_id district_id_c '
      end  || '
    , t.actual_start_date
    , t.actual_end_date
    , t.actual_effort_hrs
    , t.source_object_name
    , t.source_object_id
    , t.customer_id
    , nvl(s.master_id,s.id) product_id ' ||
      case
        when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
          '
    , v.top_node_flag vbh_top_node_flag
    , v.parent_id vbh_parent_category_id
    , v.imm_child_id vbh_child_category_id'
      end || '
    from
      isc_fs_tasks_f t
    , isc_fs_task_activity_f a
    , eni_oltp_item_star s' ||
      case
        when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
          '
    , eni_denorm_hierarchies v
    , mtl_default_category_sets m'
      end ||
      case
        when l_resource = 'N' then '
    , isc_fs_002_mv d'
      end || '
    where
        t.task_id = a.task_id
    and t.task_type_rule = ''DISPATCH''
    and t.source_object_type_code = ''SR''
    and t.deleted_flag = ''N''
    and t.inventory_item_id = s.inventory_item_id
    and t.inv_organization_id = s.organization_id' ||
      case
        when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
          '
    and m.functional_area_id = 11
    and v.object_id = m.category_set_id
    and v.dbi_flag = ''Y''
    and v.object_type = ''CATEGORY_SET''
    and s.vbh_category_id = v.child_id' ||
        case l_top_node when 'Y' then ' and v.top_node_flag = ''Y''' end
      end ||
      case
        when l_resource = 'N' then '
    and d.rg_id = t.act_bac_district_id'
      end || '
    )';

Line: 362


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

Line: 394


  , 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: 457


  l_stmt := 'select
  ' || l_viewby_select || '
, ISC_MEASURE_1
, ISC_MEASURE_2
, ISC_MEASURE_3
, ISC_MEASURE_4
, ISC_MEASURE_5
, ISC_MEASURE_6
, ISC_MEASURE_8
, ISC_MEASURE_7
, ISC_MEASURE_9
, ISC_MEASURE_10
, ISC_MEASURE_11
, 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
, ' || l_product || '
, ' || isc_fs_rpt_util_pkg.get_district_drill_down
       ( l_view_by
       , g_task_rep_func
       , 'ISC_ATTRIBUTE_2' ) || '
, ' || isc_fs_rpt_util_pkg.get_category_drill_down
       ( l_view_by
       , g_task_rep_func
       , 'ISC_ATTRIBUTE_3' ) || '
, ' || get_detail_drill( l_view_by, 1, 'ISC_MEASURE_1', 'ISC_ATTRIBUTE_4' ) || '
, ' || get_detail_drill( l_view_by, 2, 'ISC_MEASURE_2', 'ISC_ATTRIBUTE_5' ) || '
, ' || get_detail_drill( l_view_by, 3, 'ISC_MEASURE_7', 'ISC_ATTRIBUTE_6' ) || '
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(c_first_opened,0) ISC_MEASURE_1
, nvl(c_reopened,0) ISC_MEASURE_2
, nvl(p_opened,0) ISC_MEASURE_3
, nvl(c_opened,0) ISC_MEASURE_4' || '
, ' || isc_fs_rpt_util_pkg.change_column
       ( 'c_opened'
       , 'p_opened'
       , 'ISC_MEASURE_5' ) || '
, nvl(p_closed,0) ISC_MEASURE_6
, nvl(c_closed,0) ISC_MEASURE_7' || '
, ' || isc_fs_rpt_util_pkg.change_column
       ( 'c_closed'
       , 'p_closed'
       , 'ISC_MEASURE_8' ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'p_opened'
       , 'p_closed'
       , 'ISC_MEASURE_9'
       , 'N' ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_opened'
       , 'c_closed'
       , 'ISC_MEASURE_10'
       , 'N' ) || '
, ' || isc_fs_rpt_util_pkg.change_column
       ( isc_fs_rpt_util_pkg.rate_column( 'c_opened'
                                        , 'c_closed'
                                        , null
                                        , 'N' )
       , isc_fs_rpt_util_pkg.rate_column( 'p_opened'
                                        , 'p_closed'
                                        , null
                                        , 'N' )
       , 'ISC_MEASURE_11'
       , 'N') || '
, nvl(c_first_opened_total,0) ISC_MEASURE_21
, nvl(c_reopened_total,0) ISC_MEASURE_22
, nvl(p_opened_total,0) ISC_MEASURE_23
, nvl(c_opened_total,0) ISC_MEASURE_24' || '
, ' || isc_fs_rpt_util_pkg.change_column
       ( 'c_opened_total'
       , 'p_opened_total'
       , 'ISC_MEASURE_25' ) || '
, nvl(p_closed_total,0) ISC_MEASURE_26
, nvl(c_closed_total,0) ISC_MEASURE_27' || '
, ' || isc_fs_rpt_util_pkg.change_column
       ( 'c_closed_total'
       , 'p_closed_total'
       , 'ISC_MEASURE_28' ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'p_opened_total'
       , 'p_closed_total'
       , 'ISC_MEASURE_29'
       , 'N') || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_opened_total'
       , 'c_closed_total'
       , 'ISC_MEASURE_30'
       , 'N') || '
, ' || isc_fs_rpt_util_pkg.change_column
       ( isc_fs_rpt_util_pkg.rate_column( 'c_opened_total'
                                        , 'c_closed_total'
                                        , null
                                        , 'N' )
       , isc_fs_rpt_util_pkg.rate_column( 'p_opened_total'
                                        , 'p_closed_total'
                                        , null
                                        , 'N' )
       , 'ISC_MEASURE_31'
       , 'N' ) || '
from ' || poa_dbi_template_pkg.status_sql
        ( p_fact_name            => l_mv
        , p_where_clause         => l_where_clause
        , p_join_tables          => l_join_tbl
        , p_use_windowing        => 'Y' --'N'
        , p_col_name             => l_col_tbl
        , p_use_grpid            => 'N'
        , p_paren_count          => 3
        , p_filter_where         => '1=1) iset ' --null
        , p_generate_viewby      => 'Y'
        );

Line: 614


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

Line: 644


  , 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: 675


  l_stmt := 'select
  cal.name VIEWBY
, nvl(iset.p_closed,0) ISC_MEASURE_1
, nvl(iset.c_closed,0) ISC_MEASURE_2' || '
, ' ||
  isc_fs_rpt_util_pkg.change_column
    ( 'iset.c_closed'
    , 'iset.p_closed'
    , 'ISC_MEASURE_3' ) ||
  isc_fs_rpt_util_pkg.get_trend_drill
  ( l_xtd
  , g_trd_rep_func
  , 'ISC_ATTRIBUTE_1'
  , 'ISC_ATTRIBUTE_2'
  ) || '
from
  ' || poa_dbi_template_pkg.trend_sql
        ( p_xtd                  => l_xtd
        , p_comparison_type      => l_comparison_type
        , p_fact_name            => l_mv
        , p_where_clause         => l_where_clause
        , p_col_name             => l_col_tbl
        , p_use_grpid            => 'N'
        );

Line: 746


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

Line: 782


  , 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: 924


  l_stmt := 'select
  oset.task_number ISC_ATTRIBUTE_1
, oset.activity_date ISC_MEASURE_1
, ' || isc_fs_rpt_util_pkg.get_detail_column
       (l_detail_col_tbl,'task_type','ISC_ATTRIBUTE_2') || '
, ' || isc_fs_rpt_util_pkg.get_detail_column
       (l_detail_col_tbl,'task_owner','ISC_ATTRIBUTE_3') || '
, ' || isc_fs_rpt_util_pkg.get_detail_column
       (l_detail_col_tbl,'task_assignee','ISC_ATTRIBUTE_4') || '
, oset.actual_start_date ISC_MEASURE_2
, oset.actual_end_date ISC_MEASURE_3
, oset.actual_effort_hrs ISC_MEASURE_4
, oset.source_object_name ISC_ATTRIBUTE_5
, ' || isc_fs_rpt_util_pkg.get_detail_column
       (l_detail_col_tbl,'customer','ISC_ATTRIBUTE_6') || '
, ' || isc_fs_rpt_util_pkg.get_detail_column
       (l_detail_col_tbl,'product','ISC_ATTRIBUTE_7') || '
, ' || isc_fs_rpt_util_pkg.get_sr_detail_page_function('oset.source_object_id') || '  ISC_ATTRIBUTE_8
, ' || isc_fs_rpt_util_pkg.get_task_detail_page_function('oset.task_id') || '  ISC_ATTRIBUTE_10
from
' || isc_fs_rpt_util_pkg.detail_sql
     ( p_detail_col_tbl => l_detail_col_tbl
     , p_dimension_tbl  => l_dimension_tbl
     , p_mv_name        => l_mv
     , p_where_clause   => l_where_clause
     , p_rank_order     => l_rank_order
     );