APPS.BIV_DBI_TMPL_UTIL SQL Statements

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

Line: 53

            when g_REQUEST_TYPE then '(select incident_type_id id, name value from cs_incident_types_tl where userenv(''LANG'') = language)'
            when g_CATEGORY then 'eni_item_vbh_nodes_v'
            when g_PRODUCT then 'eni_item_v'
            when g_SEVERITY then 'biv_severities_v'
            when g_STATUS then 'biv_statuses_v'
            when g_CHANNEL then 'biv_channels_v'
            when g_RESOLUTION then 'biv_resolutions_v'
            when g_CUSTOMER then 'aso_bi_prospect_v'
            when g_ASSIGNMENT then '(select group_id id, group_name value from jtf_rs_groups_tl where userenv(''LANG'') = language)'
            when g_AGING then '(select id, value from biv_bucket_aging_v where short_name=''BIV_DBI_BACKLOG_AGING'')'
            when g_RES_STATUS then 'biv_dbi_res_status_v'
            else ''
          end);

Line: 139

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, f.incident_type_id, f.product_id, f.incident_severity_id
, f.customer_id, f.owner_group_id, f.sr_creation_channel
, f.first_opened_count
, f.reopened_count
, f.closed_count
from
  biv_act_sum_mv f
, 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
and f.grp_id = ' || get_grp_id(p_bmap) || '
)';

Line: 159

        return '(select * from biv_act_sum_mv where grp_id = ' ||
                    get_grp_id(p_bmap) || ')';

Line: 174

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, f.incident_type_id, f.product_id, f.incident_severity_id
, f.customer_id, f.owner_group_id, f.sr_creation_channel, f.resolution_code
, f.closed_count
, f.total_time_to_close
, f.time_to_close_b1
, f.time_to_close_b2
, f.time_to_close_b3
, f.time_to_close_b4
, f.time_to_close_b5
, f.time_to_close_b6
, f.time_to_close_b7
, f.time_to_close_b8
, f.time_to_close_b9
, f.time_to_close_b10
from
  biv_clo_sum_mv f
, 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
and f.grp_id = ' || get_grp_id(p_bmap) || '
)';

Line: 203

        return '(select * from biv_clo_sum_mv where grp_id = ' ||
                    get_grp_id(p_bmap) || ')';

Line: 218

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, f.incident_type_id, f.product_id, f.incident_severity_id
, f.customer_id, f.owner_group_id, f.sr_creation_channel, f.resolution_code
, f.resolution_count
, f.total_time_to_resolution
, f.time_to_resolution_b1
, f.time_to_resolution_b2
, f.time_to_resolution_b3
, f.time_to_resolution_b4
, f.time_to_resolution_b5
, f.time_to_resolution_b6
, f.time_to_resolution_b7
, f.time_to_resolution_b8
, f.time_to_resolution_b9
, f.time_to_resolution_b10
from
  biv_res_sum_mv f
, 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
and f.grp_id = ' || get_grp_id(p_bmap) || '
)';

Line: 247

        return '(select * from biv_res_sum_mv where grp_id = ' ||
                    get_grp_id(p_bmap) || ')';

Line: 263

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.*
from
  biv_bac_sum_mv f
, 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
and f.grp_id = ' || get_grp_id( p_bmap ) || '
)';

Line: 278

        return '( select * from biv_bac_sum_mv f where grp_id = ' || get_grp_id( p_bmap ) || ')';

Line: 291

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.incident_type_id, f.product_id, f.incident_severity_id
, f.customer_id, f.owner_group_id, f.incident_status_id, f.resolved_flag
, f.backlog_count
, f.escalated_count
, f.unowned_count
, c.report_date
from
  biv_bac_sum_mv f
, 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.grp_id = ' || get_grp_id( p_bmap ) || '
and f.time_id = c.time_id
and f.period_type_id = c.period_type_id
and bitand(c.record_type_id,512) = 512
)';

Line: 317

select
  f.incident_type_id, f.product_id, f.incident_severity_id
, f.customer_id, f.owner_group_id, f.incident_status_id
, f.resolved_flag
, f.backlog_count
, f.escalated_count
, f.unowned_count
, c.report_date
from
  biv_bac_sum_mv f
, fii_time_structures c
where grp_id = ' || get_grp_id( p_bmap ) || '
and f.time_id = c.time_id
and f.period_type_id = c.period_type_id
and bitand(c.record_type_id,512) = 512
)';

Line: 336

select
  f.vbh_top_node_flag, f.vbh_parent_category_id, f.vbh_child_category_id
, f.incident_type_id, f.incident_severity_id
, f.resolved_flag
, f.backlog_count
, f.escalated_count
, f.unowned_count
, c.report_date
from
  biv_bac_h_sum_mv f
, fii_time_structures c
where
    f.time_id = c.time_id
and f.period_type_id = c.period_type_id
and bitand(c.record_type_id,512) = 512
)';

Line: 362

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.report_date, f.incident_type_id, f.product_id, f.incident_severity_id
, f.customer_id, f.owner_group_id, f.incident_status_id, f.resolved_flag
, f.backlog_count
, f.total_backlog_age
, f.backlog_age_b1
, f.backlog_age_b2
, f.backlog_age_b3
, f.backlog_age_b4
, f.backlog_age_b5
, f.backlog_age_b6
, f.backlog_age_b7
, f.backlog_age_b8
, f.backlog_age_b9
, f.backlog_age_b10
, f.escalated_count
, f.total_escalated_age
, f.escalated_age_b1
, f.escalated_age_b2
, f.escalated_age_b3
, f.escalated_age_b4
, f.escalated_age_b5
, f.escalated_age_b6
, f.escalated_age_b7
, f.escalated_age_b8
, f.escalated_age_b9
, f.escalated_age_b10
, f.unowned_count
, f.total_unowned_age
, f.unowned_age_b1
, f.unowned_age_b2
, f.unowned_age_b3
, f.unowned_age_b4
, f.unowned_age_b5
, f.unowned_age_b6
, f.unowned_age_b7
, f.unowned_age_b8
, f.unowned_age_b9
, f.unowned_age_b10
from
  biv_bac_age_sum_f f
, 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
and f.grp_id = ' || get_grp_id( p_bmap ) || '
)';

Line: 415

        return '(select * from biv_bac_age_sum_f where grp_id = ' ||
                    get_grp_id( p_bmap ) || ')';

Line: 423

select
  f.backlog_date_from
, f.backlog_date_to
, f.incident_id
, f.incident_type_id
, nvl(s.master_id,s.id) product_id
, f.incident_severity_id
, f.customer_id
, f.owner_group_id
, f.incident_status_id
, f.incident_date
, f.resolved_flag
, f.escalated_date
, f.unowned_date
, (&AGE_CURRENT_ASOF_DATE + &AGE_CURRENT_ASOF_DATE_TIME) - f.incident_date age' ||
  case when bitand(p_bmap,g_CATEGORY_BMAP) = 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'
       else null
  end || '
from
  biv_dbi_backlog_sum_f f
, eni_oltp_item_star s' ||
  case when bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
         '
, eni_denorm_hierarchies v
, mtl_default_category_sets m'
       else null
  end || '
where
    f.inventory_item_id = s.inventory_item_id
and f.inv_organization_id = s.organization_id' ||
  case when bitand(p_bmap,g_CATEGORY_BMAP) = 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'
       else null
  end || '
and (&AGE_CURRENT_ASOF_DATE + &AGE_CURRENT_ASOF_DATE_TIME) - f.incident_date >= 0
)';

Line: 471

select
  f.report_date closed_date
, f.incident_id
, f.incident_type_id
, nvl(s.master_id,s.id) product_id
, f.incident_severity_id
, f.customer_id
, f.owner_group_id
, f.sr_creation_channel
, f.resolution_code
, f.time_to_close age' ||
  case when bitand(p_bmap,g_CATEGORY_BMAP) = 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'
       else null
  end || '
from
  biv_dbi_closed_sum_f f
, eni_oltp_item_star s' ||
  case when bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
         '
, eni_denorm_hierarchies v
, mtl_default_category_sets m'
       else null
  end || '
where
    f.inventory_item_id = s.inventory_item_id
and f.inv_organization_id = s.organization_id' ||
  case when bitand(p_bmap,g_CATEGORY_BMAP) = 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'
       else null
  end || '
and f.reopened_date is null
and f.time_to_close >= 0
)';

Line: 516

select
  f.report_date resolved_date
, f.incident_id
, f.incident_type_id
, nvl(s.master_id,s.id) product_id
, f.incident_severity_id
, f.customer_id
, f.owner_group_id
, f.sr_creation_channel
, f.resolution_code
, f.time_to_resolution age' ||
  case when bitand(p_bmap,g_CATEGORY_BMAP) = 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'
       else null
  end || '
from
  biv_dbi_resolution_sum_f f
, eni_oltp_item_star s' ||
  case when bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
         '
, eni_denorm_hierarchies v
, mtl_default_category_sets m'
       else null
  end || '
where
    f.inventory_item_id = s.inventory_item_id
and f.inv_organization_id = s.organization_id' ||
  case when bitand(p_bmap,g_CATEGORY_BMAP) = 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'
       else null
  end || '
and f.time_to_resolution >= 0
)';

Line: 1320

  select
    '/* ' ||
    lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc'
    || ' */'
  into l_trace_file_name
  from
    ( select
        p.spid
      from
        sys.v_$mystat m,
        sys.v_$session s,
        sys.v_$process p
      where
        m.statistic# = 1 and
        s.sid = m.sid and
        p.addr = s.paddr
    ) p,
    ( select
        t.instance
      from
        sys.v_$thread  t,
        sys.v_$parameter  v
      where
        v.name = 'thread' and
        (
          v.value = 0 or
          t.thread# = to_number(v.value)
        )
  ) i;

Line: 1460

    select
      max(decode(lookup_code,'Y',meaning,null))
    , max(decode(lookup_code,'N',meaning,null))
    from fnd_lookup_values
    where lookup_type = 'YES_NO'
    and view_application_id = 0
    and language = userenv('LANG');

Line: 1681

  select max(report_date)
  into l_current_date
  from biv_dbi_backlog_age_dates;