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

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

Line: 71


select
  v.top_node_flag vbh_top_node_flag
, v.parent_id vbh_parent_category_id
, v.imm_child_id vbh_child_category_id
, c.report_date ' || 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.backlog_count
, f.in_planning_count
, f.assigned_count
, f.working_count
, f.completed_count
, f.other_count
from
  isc_fs_005_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
, fii_time_structures c
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
and f.time_id = c.time_id
and f.period_type_id = c.period_type_id
and f.time_id = c.time_id
and bitand(c.record_type_id,512) = 512
and f.grp_id = &ISC_GRP_ID' || case
                                 when l_district_leaf_node = 'N' then '
and f.parent_district_id = den.rg_id'
                               end ||
                               case
                                 when l_top_node = 'Y' then '
and v.top_node_flag = ''Y'''   end || '
)';

Line: 128


select
  c.report_date ' || 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.backlog_count
, f.in_planning_count
, f.assigned_count
, f.working_count
, f.completed_count
, f.other_count
from
  isc_fs_005_mv f' || case
                        when l_district_leaf_node = 'N' then '
, isc_fs_002_mv den'
                      end || '
, fii_time_structures c
where
    f.period_type_id = c.period_type_id
and f.time_id = c.time_id
and bitand(c.record_type_id,512) = 512
and f.grp_id = &ISC_GRP_ID' || case
                                 when l_district_leaf_node = 'N' then '
and f.parent_district_id = den.rg_id'
                               end || '
)';

Line: 169


select
  c.report_date
, f.parent_district_id
, f.record_type
, f.district_id
, f.district_id_c
, f.backlog_count
, f.in_planning_count
, f.assigned_count
, f.working_count
, f.completed_count
, f.other_count
from
  isc_fs_006_mv f
, fii_time_structures c
where
    f.period_type_id = c.period_type_id
and f.time_id = c.time_id
and bitand(c.record_type_id,512) = 512
)';

Line: 204


    select
      t.task_id
    , t.task_number
    , b.backlog_date_from
    , b.backlog_date_to
    , b.backlog_status_code
    , 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.planned_start_date
    , t.source_object_name
    , t.source_object_id
    , t.incident_date
    , 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_backlog_f b
    , 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 = b.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: 293


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

Line: 325


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


  l_stmt := 'select
  ' || l_viewby_select || '
, ISC_MEASURE_1
, ISC_MEASURE_2
, ISC_MEASURE_3
, ISC_MEASURE_4_B1
, ISC_MEASURE_4_B2
, ISC_MEASURE_4_B3
, ISC_MEASURE_4_B4
, ISC_MEASURE_4_B5
, ISC_MEASURE_21
, ISC_MEASURE_22
, ISC_MEASURE_23
, ISC_MEASURE_24_B1
, ISC_MEASURE_24_B2
, ISC_MEASURE_24_B3
, ISC_MEASURE_24_B4
, ISC_MEASURE_24_B5
, ' || 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_ATTRIBUTE_4' ) || '
, ' || get_detail_drill( l_view_by, '2', 'ISC_ATTRIBUTE_4' ) || '
, ' || get_detail_drill( l_view_by, '3', 'ISC_ATTRIBUTE_4' ) || '
, ' || get_detail_drill( l_view_by, '4', 'ISC_ATTRIBUTE_4' ) || '
, ' || get_detail_drill( l_view_by, '5', '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_backlog,0) ISC_MEASURE_1
, nvl(c_backlog,0) ISC_MEASURE_2
, ' || isc_fs_rpt_util_pkg.change_column
       ( 'c_backlog'
       , 'p_backlog'
       , 'ISC_MEASURE_3' ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_in_planning'
       , 'c_backlog'
       , 'ISC_MEASURE_4_B1'
       ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_assigned'
       , 'c_backlog'
       , 'ISC_MEASURE_4_B2'
       ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_working'
       , 'c_backlog'
       , 'ISC_MEASURE_4_B3'
       ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_completed'
       , 'c_backlog'
       , 'ISC_MEASURE_4_B4'
       ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_other'
       , 'c_backlog'
       , 'ISC_MEASURE_4_B5'
       ) || '
, nvl(p_backlog_total,0) ISC_MEASURE_21
, nvl(c_backlog_total,0) ISC_MEASURE_22
, ' || isc_fs_rpt_util_pkg.change_column
       ( 'c_backlog_total'
       , 'p_backlog_total'
       , 'ISC_MEASURE_23' ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_in_planning_total'
       , 'c_backlog_total'
       , 'ISC_MEASURE_24_B1'
       ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_assigned_total'
       , 'c_backlog_total'
       , 'ISC_MEASURE_24_B2'
       ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_working_total'
       , 'c_backlog_total'
       , 'ISC_MEASURE_24_B3'
       ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_completed_total'
       , 'c_backlog_total'
       , 'ISC_MEASURE_24_B4'
       ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_other_total'
       , 'c_backlog_total'
       , 'ISC_MEASURE_24_B5'
       ) || '
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         => '(isc_measure_1 >0 or isc_measure_2>0)) iset ' --null
        , p_generate_viewby      => 'Y'
        );

Line: 543


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

Line: 580


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


  l_stmt := 'select
  oset.task_number ISC_ATTRIBUTE_1
, ' || isc_fs_rpt_util_pkg.get_detail_column
       (l_detail_col_tbl,'task_status','ISC_ATTRIBUTE_2') || '
, ' || isc_fs_rpt_util_pkg.get_detail_column
       (l_detail_col_tbl,'task_type','ISC_ATTRIBUTE_3') || '
, ' || isc_fs_rpt_util_pkg.get_detail_column
       (l_detail_col_tbl,'task_owner','ISC_ATTRIBUTE_4') || '
, ' || isc_fs_rpt_util_pkg.get_detail_column
       (l_detail_col_tbl,'task_assignee','ISC_ATTRIBUTE_5') || '
, oset.actual_start_date ISC_MEASURE_1
, oset.actual_end_date ISC_MEASURE_2
, oset.planned_start_date ISC_MEASURE_3
, oset.source_object_name ISC_ATTRIBUTE_6
, oset.incident_date ISC_MEASURE_4
, ' || isc_fs_rpt_util_pkg.get_detail_column
       (l_detail_col_tbl,'customer','ISC_ATTRIBUTE_7') || '
, ' || isc_fs_rpt_util_pkg.get_detail_column
       (l_detail_col_tbl,'product','ISC_ATTRIBUTE_8') || '
, ' || isc_fs_rpt_util_pkg.get_sr_detail_page_function('oset.source_object_id') || ' ISC_ATTRIBUTE_9
, null ISC_ATTRIBUTE_10'
-- above is needed to associate bucket set with report
    || '
, ' || isc_fs_rpt_util_pkg.get_task_detail_page_function('oset.task_id') || ' ISC_ATTRIBUTE_11
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
     , p_override_date_clause => 'least(&LAST_COLLECTION,&BIS_CURRENT_ASOF_DATE) between fact.backlog_date_from and fact.backlog_date_to'
     );