APPS.ENI_DBI_IVA_PKG SQL Statements

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

Line: 45

    l_lookup_select    VARCHAR2(100);

Line: 59

    l_lookup_inner_select VARCHAR2(100);

Line: 109

      select top_node_flag,leaf_node_flag
        into top_flag,leaf_flag
        from eni_denorm_hierarchies edh
       where edh.parent_id = l_category1
         and edh.child_id = edh.parent_id;

Line: 138

      l_lookup_select := 'lookupv.value VIEWBY, lookupv.id VIEWBYID';

Line: 142

      l_lookup_inner_select := ' edismv.item_org_id ';

Line: 180

      l_lookup_select := 'lookupv.VALUE VIEWBY, lookupv.id VIEWBYID';

Line: 184

      l_lookup_inner_select := ' edismv.product_category_id ';

Line: 221

        l_lookup_inner_select := ' edh.parent_id product_category_id ';

Line: 231

        l_lookup_inner_select := ' edh.imm_child_id product_category_id ';

Line: 245

      l_lookup_select := 'lookupv.organization_name VIEWBY, lookupv.organization_id VIEWBYID ';

Line: 249

      l_lookup_inner_select := ' odipmv.organization_id ';

Line: 292

      'SELECT /*+ LEADING(ftrs) */
          ' || l_lookup_select || ',
   ENI_MEASURE1,
   ENI_MEASURE2,
   ENI_MEASURE4,
   ENI_MEASURE5,
   ENI_MEASURE7,
   ENI_MEASURE8,
   ENI_MEASURE10,
   ENI_MEASURE11,
   SUM(ENI_MEASURE1) OVER() ENI_MEASURE14,
   SUM(ENI_MEASURE2) OVER() ENI_MEASURE15,
   SUM(ENI_MEASURE4) OVER() ENI_MEASURE17,
   SUM(ENI_MEASURE5) OVER() ENI_MEASURE18,
   SUM(ENI_MEASURE7) OVER() ENI_MEASURE20,
   SUM(ENI_MEASURE8) OVER() ENI_MEASURE21,
   SUM(ENI_MEASURE10) OVER() ENI_MEASURE23,
   SUM(ENI_MEASURE11) OVER() ENI_MEASURE24,
   (RATIO_TO_REPORT(ENI_MEASURE1) OVER())*100 AS ENI_MEASURE26,
   100 AS ENI_MEASURE27,
   ' || l_drill_to_cat_url || ' AS ENI_MEASURE28
  FROM
  (
   SELECT a.*,
   rank() over ( &'||'ORDER_BY_CLAUSE'||' nulls last,' || l_rank_measure ||')-1 as rank_num
   FROM
   (
   SELECT
    ' || l_lookup_inner_select || ',
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
     THEN
      '|| l_summary || '.inv_total_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE1,
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
     THEN
      '|| l_summary || '.inv_total_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE2,
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
     THEN
      '|| l_summary || '.onhand_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE4,
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
     THEN
      '|| l_summary || '.onhand_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE5,
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
     THEN
      '|| l_summary || '.intransit_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE7,
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
     THEN
      '|| l_summary || '.intransit_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE8,
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
     THEN
      '|| l_summary || '.wip_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE10,
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
     THEN
      '|| l_summary || '.wip_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE11
   FROM
    ' || l_from_clause || ', fii_time_rpt_struct ftrs
   WHERE
    ftrs.time_id = '|| l_summary || '.time_id
    AND
    (
     ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
     OR ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
    )
    AND BITAND(ftrs.record_type_id,1143) = ftrs.record_type_id
    ' || l_where_clause || '
   GROUP BY
    ' || l_group_by_clause || '
   ) a
  )b ' || l_lookup || '
  where ((b.rank_num between &'||'START_INDEX and &'||'END_INDEX) OR (&'||'END_INDEX = -1)) '||
  l_lookup_where || '
  order by rank_num';

Line: 408

      'SELECT
          ' || l_lookup_select || ',
   ENI_MEASURE1,
   ENI_MEASURE2,
   ENI_MEASURE4,
   ENI_MEASURE5,
   ENI_MEASURE7,
   ENI_MEASURE8,
   ENI_MEASURE10,
   ENI_MEASURE11,
   ENI_MEASURE14,
   ENI_MEASURE15,
   ENI_MEASURE17,
   ENI_MEASURE18,
   ENI_MEASURE20,
   ENI_MEASURE21,
   ENI_MEASURE23,
   ENI_MEASURE24,
   ENI_MEASURE26,
   100 AS ENI_MEASURE27,
   ' || l_drill_to_cat_url || ' AS ENI_MEASURE28
  FROM
  (
   SELECT a.*,
   (RATIO_TO_REPORT(ENI_MEASURE1) OVER())*100 AS ENI_MEASURE26,
   rank() over ( &'||'ORDER_BY_CLAUSE'||' nulls last,' || l_rank_measure ||')-1 as rank_num
   FROM
   (
   SELECT
    ' || l_lookup_inner_select || ',
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
     THEN
      '|| l_summary || '.inv_total_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE1,
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
     THEN
      '|| l_summary || '.inv_total_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE2,
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
     THEN
      '|| l_summary || '.onhand_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE4,
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
     THEN
      '|| l_summary || '.onhand_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE5,
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
     THEN
      '|| l_summary || '.intransit_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE7,
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
     THEN
      '|| l_summary || '.intransit_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE8,
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
     THEN
      '|| l_summary || '.wip_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE10,
    SUM
    (
     case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
     THEN
      '|| l_summary || '.wip_value_'||l_curr_suffix||'
     END
    ) AS ENI_MEASURE11,
    SUM
    (
     SUM
     (
      case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
      THEN
       '|| l_summary || '.inv_total_value_'||l_curr_suffix||'
      END
     )
    ) OVER() AS ENI_MEASURE14,
    SUM
    (
     SUM
     (
      case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
      THEN
       '|| l_summary || '.inv_total_value_'||l_curr_suffix||'
      END
     )
    ) OVER() AS ENI_MEASURE15,
    SUM
    (
     SUM
     (
      case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
      THEN
       '|| l_summary || '.onhand_value_'||l_curr_suffix||'
      END
     )
    ) OVER() AS ENI_MEASURE17,
    SUM
    (
     SUM
     (
      case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
      THEN
       '|| l_summary || '.onhand_value_'||l_curr_suffix||'
      END
     )
    ) OVER() AS ENI_MEASURE18,
    SUM
    (
     SUM
     (
      case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
      THEN
       '|| l_summary || '.intransit_value_'||l_curr_suffix||'
      END
     )
    ) OVER() AS ENI_MEASURE20,
    SUM
    (
     SUM
     (
      case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
      THEN
       '|| l_summary || '.intransit_value_'||l_curr_suffix||'
      END
     )
    ) OVER() AS ENI_MEASURE21,
    SUM
    (
     SUM
     (
      case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
      THEN
       '|| l_summary || '.wip_value_'||l_curr_suffix||'
      END
     )
    ) OVER() AS ENI_MEASURE23,
    SUM
    (
     SUM
     (
      case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
      THEN
       '|| l_summary || '.wip_value_'||l_curr_suffix||'
      END
     )
    ) OVER() AS ENI_MEASURE24
   FROM
    ' || l_from_clause || ', fii_time_rpt_struct ftrs
   WHERE
    ftrs.time_id = '|| l_summary || '.time_id
    AND
    (
     ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
     OR ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
    )
    AND BITAND(ftrs.record_type_id,1143) = ftrs.record_type_id
    ' || l_where_clause || '
   GROUP BY
    ' || l_group_by_clause || '
   ) a
  )b ' || l_lookup || '
  where ((b.rank_num between &'||'START_INDEX and &'||'END_INDEX) OR (&'||'END_INDEX = -1)) '||
  l_lookup_where || '
  order by rank_num';