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.OPI_DBI_WMS_RPT_PKG SQL Statements

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

Line: 160


    l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
                                            (p_join_tbl);

Line: 171


        'SELECT
            ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
            || fnd_global.newline ||'       ' || l_description ||
            ' OPI_ATTRIBUTE10 ';

Line: 204


    l_sel_clause := l_sel_clause || 'FROM ( SELECT
     rank() over (&ORDER_BY_CLAUSE nulls last '||', '||l_view_by_fact_col||') - 1 rnk
       ,'||l_view_by_fact_col;

Line: 237


    'FROM ( SELECT  '  || fnd_global.newline ||'
          ' || l_view_by_fact_col || ',
          ' || opi_dbi_rpt_util_pkg.nvl_str (
                       p_str         => 'p_picks',
                       p_default_val => 0) || ' OPI_MEASURE11,
          '  || opi_dbi_rpt_util_pkg.nvl_str (
                       p_str         => 'c_picks',
                       p_default_val => 0) || ' OPI_MEASURE1,
          ' || opi_dbi_rpt_util_pkg.change_str (
                       p_new_numerator   => 'c_picks',
                       p_old_numerator   => 'p_picks',
                       p_denominator     => 'p_picks',
                       p_measure_name    => 'OPI_MEASURE2') || ',
          ' || opi_dbi_rpt_util_pkg.percent_str(
                       p_numerator      => 'c_picks',
                       p_denominator    => 'c_picks_total',
                       p_measure_name   => 'OPI_MEASURE3') || ',
          ' || opi_dbi_rpt_util_pkg.nvl_str (
                       p_str         => 'p_picks_with_exceptions',
                       p_default_val => 0) || ' OPI_MEASURE12,
          ' || opi_dbi_rpt_util_pkg.nvl_str (
                       p_str         => 'c_picks_with_exceptions',
                       p_default_val => 0) || ' OPI_MEASURE4,
          ' || opi_dbi_rpt_util_pkg.change_str (
                       p_new_numerator   => 'c_picks_with_exceptions',
                       p_old_numerator   => 'p_picks_with_exceptions',
                       p_denominator     => 'p_picks_with_exceptions',
                       p_measure_name    => 'OPI_MEASURE5') || ',
          ' || opi_dbi_rpt_util_pkg.percent_str(
                       p_numerator      => 'p_picks_with_exceptions',
                       p_denominator    => 'p_picks',
                       p_measure_name   => 'OPI_MEASURE13') || ',
          ' || opi_dbi_rpt_util_pkg.percent_str(
                       p_numerator      => 'c_picks_with_exceptions',
                       p_denominator    => 'c_picks',
                       p_measure_name   => 'OPI_MEASURE6') || ',
          ' || opi_dbi_rpt_util_pkg.change_pct_str (
                       p_new_numerator   => 'c_picks_with_exceptions',
                       p_new_denominator => 'c_picks',
                       p_old_numerator   => 'p_picks_with_exceptions',
                       p_old_denominator => 'p_picks',
                       p_measure_name    => 'OPI_MEASURE7') || ',
          ' || opi_dbi_rpt_util_pkg.nvl_str (
                       p_str         => 'p_pick_exceptions',
                       p_default_val => 0) || ' OPI_MEASURE14,
          ' || opi_dbi_rpt_util_pkg.nvl_str (
                       p_str         => 'c_pick_exceptions',
                       p_default_val => 0) || ' OPI_MEASURE8,
          ' || opi_dbi_rpt_util_pkg.change_str (
                       p_new_numerator   => 'c_pick_exceptions',
                       p_old_numerator   => 'p_pick_exceptions',
                       p_denominator     => 'p_pick_exceptions',
                       p_measure_name    => 'OPI_MEASURE9') || ',
          ' || opi_dbi_rpt_util_pkg.nvl_str (
                       p_str         => 'c_picks_total',
                       p_default_val => 0) || ' OPI_MEASURE21,
          ' || opi_dbi_rpt_util_pkg.change_str (
                       p_new_numerator   => 'c_picks_total',
                       p_old_numerator   => 'p_picks_total',
                       p_denominator     => 'p_picks_total',
                       p_measure_name    => 'OPI_MEASURE22') || ',
          ' || opi_dbi_rpt_util_pkg.percent_str(
                       p_numerator      => 'c_picks_total',
                       p_denominator    => 'c_picks_total',
                       p_measure_name   => 'OPI_MEASURE23')  || ',
          ' || opi_dbi_rpt_util_pkg.nvl_str (
                       p_str         => 'c_picks_with_exceptions_total',
                       p_default_val => 0) || ' OPI_MEASURE24,
          ' || opi_dbi_rpt_util_pkg.change_str (
                       p_new_numerator   => 'c_picks_with_exceptions_total',
                       p_old_numerator   => 'p_picks_with_exceptions_total',
                       p_denominator     => 'p_picks_with_exceptions_total',
                       p_measure_name    => 'OPI_MEASURE25') || ',
          ' || opi_dbi_rpt_util_pkg.percent_str(
                       p_numerator      => 'c_picks_with_exceptions_total',
                       p_denominator    => 'c_picks_total',
                       p_measure_name   => 'OPI_MEASURE26')  || ',
          ' || opi_dbi_rpt_util_pkg.change_pct_str (
                       p_new_numerator   => 'c_picks_with_exceptions_total',
                       p_new_denominator => 'c_picks_total',
                       p_old_numerator   => 'p_picks_with_exceptions_total',
                       p_old_denominator => 'p_picks_total',
                       p_measure_name    => 'OPI_MEASURE27') || ',
          ' || opi_dbi_rpt_util_pkg.nvl_str (
                       p_str         => 'c_pick_exceptions_total',
                       p_default_val => 0) || ' OPI_MEASURE28,
          ' || opi_dbi_rpt_util_pkg.change_str (
                       p_new_numerator   => 'c_pick_exceptions_total',
                       p_old_numerator   => 'p_pick_exceptions_total',
                       p_denominator     => 'p_pick_exceptions_total',
                       p_measure_name    => 'OPI_MEASURE29') || ',
          ' || opi_dbi_rpt_util_pkg.percent_str(
                       p_numerator      => 'c_picks_with_exceptions',
                       p_denominator    => 'c_picks',
                       p_measure_name   => 'OPI_MEASURE30') || ',
          ' || opi_dbi_rpt_util_pkg.percent_str(
                       p_numerator      => 'p_picks_with_exceptions',
                       p_denominator    => 'p_picks',
                       p_measure_name   => 'OPI_MEASURE31') || ',
          ' || opi_dbi_rpt_util_pkg.percent_str(
                       p_numerator      => 'c_picks_with_exceptions_total',
                       p_denominator    => 'c_picks_total',
                       p_measure_name   => 'OPI_MEASURE32')  || ',
          ' || opi_dbi_rpt_util_pkg.percent_str(
                       p_numerator      => 'p_picks_with_exceptions_total',
                       p_denominator    => 'p_picks_total',
                       p_measure_name   => 'OPI_MEASURE33');

Line: 460


    l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
                                                (p_join_tbl);

Line: 465


        'SELECT
           ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
          || fnd_global.newline;

Line: 477


            (SELECT (rank () over
               (&ORDER_BY_CLAUSE nulls last,
               ' || l_view_by_fact_col || ')) - 1 rnk,
               ' || l_view_by_fact_col || ',
              OPI_MEASURE1,
              OPI_MEASURE2,
              OPI_MEASURE3,
              OPI_MEASURE4,
              OPI_MEASURE5,
              OPI_MEASURE6'|| fnd_global.newline;

Line: 489


            'FROM ( SELECT  '  || fnd_global.newline ||'
            ' ||  l_view_by_fact_col || ',
            ' ||  opi_dbi_rpt_util_pkg.nvl_str (
                           p_str         => 'c_exceptions',
                           p_default_val => 0) || ' OPI_MEASURE1,
            ' ||  opi_dbi_rpt_util_pkg.change_str (
                           p_new_numerator   => 'c_exceptions',
                           p_old_numerator   => 'p_exceptions',
                           p_denominator     => 'p_exceptions',
                           p_measure_name    => 'OPI_MEASURE2') || ',
           ' ||  opi_dbi_rpt_util_pkg.percent_str(
                           p_numerator      => 'c_exceptions',
                           p_denominator    => 'c_exceptions_total',
                           p_measure_name   => 'OPI_MEASURE3') || ',
            ' ||  opi_dbi_rpt_util_pkg.nvl_str (
                           p_str         => 'c_exceptions_total',
                           p_default_val => 0) || ' OPI_MEASURE4,
            ' ||  opi_dbi_rpt_util_pkg.change_str (
                           p_new_numerator   => 'c_exceptions_total',
                           p_old_numerator   => 'p_exceptions_total',
                           p_denominator     => 'p_exceptions_total',
                           p_measure_name    => 'OPI_MEASURE5') || ',
            ' || opi_dbi_rpt_util_pkg.percent_str(
                           p_numerator      => 'c_exceptions_total',
                           p_denominator    => 'c_exceptions_total',
                           p_measure_name   => 'OPI_MEASURE6');

Line: 640


    l_sel_clause := 'SELECT
      ' || ' cal.name VIEWBY,
      ' || opi_dbi_rpt_util_pkg.nvl_str (
                    p_str           => 'p_picks',
                    p_default_val   => 0) || ' OPI_MEASURE11,
      ' || opi_dbi_rpt_util_pkg.nvl_str (
                    p_str           => 'c_picks',
                    p_default_val   => 0) || ' OPI_MEASURE1,
      ' || opi_dbi_rpt_util_pkg.change_str (
                    p_new_numerator     => 'c_picks',
                    p_old_numerator   => 'p_picks',
                    p_denominator     => 'p_picks',
                    p_measure_name      => 'OPI_MEASURE2') || ',
      ' || opi_dbi_rpt_util_pkg.nvl_str (
                    p_str           => 'p_picks_with_exceptions',
                    p_default_val   => 0) || ' OPI_MEASURE12,
      ' || opi_dbi_rpt_util_pkg.nvl_str (
                    p_str           => 'c_picks_with_exceptions',
                    p_default_val   => 0) || ' OPI_MEASURE3,
      ' || opi_dbi_rpt_util_pkg.change_str (
                    p_new_numerator  => 'c_picks_with_exceptions',
                    p_old_numerator  => 'p_picks_with_exceptions',
                    p_denominator    => 'p_picks_with_exceptions',
                    p_measure_name   => 'OPI_MEASURE4') || ',
      ' || opi_dbi_rpt_util_pkg.percent_str(
                   p_numerator       => 'p_picks_with_exceptions',
                   p_denominator    => 'p_picks',
                   p_measure_name   => 'OPI_MEASURE13') || ',
      ' || opi_dbi_rpt_util_pkg.percent_str(
                   p_numerator      => 'c_picks_with_exceptions',
                   p_denominator    => 'c_picks',
                   p_measure_name   => 'OPI_MEASURE5') || ',
      ' || opi_dbi_rpt_util_pkg.change_pct_str (
                   p_new_numerator   => 'c_picks_with_exceptions',
                   p_new_denominator => 'c_picks',
                   p_old_numerator   => 'p_picks_with_exceptions',
                   p_old_denominator => 'p_picks',
                   p_measure_name    => 'OPI_MEASURE6') || ',
      ' || opi_dbi_rpt_util_pkg.nvl_str (
                   p_str         => 'p_pick_exceptions',
                   p_default_val => 0) || ' OPI_MEASURE14,
      ' || opi_dbi_rpt_util_pkg.nvl_str (
                   p_str         => 'c_pick_exceptions',
                   p_default_val => 0) || ' OPI_MEASURE7,
      ' || opi_dbi_rpt_util_pkg.change_str (
                   p_new_numerator     => 'c_pick_exceptions',
                   p_old_numerator   => 'p_pick_exceptions',
                   p_denominator     => 'p_pick_exceptions',
                   p_measure_name      => 'OPI_MEASURE8');

Line: 831


    l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
                                                (p_join_tbl);

Line: 836


        'SELECT
        ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline;

Line: 861


            'FROM ( SELECT
             rank() over (&ORDER_BY_CLAUSE nulls last '||', '||l_view_by_fact_col||') - 1 rnk
               ,'||l_view_by_fact_col;

Line: 887


        'FROM ( SELECT  '  || fnd_global.newline ||
                         l_view_by_fact_col || fnd_global.newline ||
               ',' || opi_dbi_rpt_util_pkg.rate_str (
                           p_numerator       => 'p_elapsed_time',
                           p_denominator     => 'p_executions',
                           p_rate_type       => 'NP') || 'OPI_MEASURE13,
              ' || opi_dbi_rpt_util_pkg.rate_str (
                           p_numerator       => 'c_elapsed_time',
                           p_denominator     => 'c_executions',
                           p_rate_type       => 'NP') || 'OPI_MEASURE2,
              ' || opi_dbi_rpt_util_pkg.change_pct_str_basic(
                               p_new_numerator     => 'c_elapsed_time',
                               p_new_denominator   => 'c_executions',
                               p_old_numerator     => 'p_elapsed_time',
                               p_old_denominator   => 'c_executions',
                               p_measure_name      => 'OPI_MEASURE3') || ',
              ' || opi_dbi_rpt_util_pkg.nvl_str (
                           p_str         => 'c_executions',
                           p_default_val => 0) || ' OPI_MEASURE4,
              '  || opi_dbi_rpt_util_pkg.nvl_str (
                           p_str         => 'c_exec_with_exceptions',
                           p_default_val => 0) || ' OPI_MEASURE5,
              ' || opi_dbi_rpt_util_pkg.rate_str (
                           p_numerator       => 'p_exec_with_exceptions',
                           p_denominator     => 'p_executions',
                           p_rate_type       => 'P') || 'OPI_MEASURE15,
              ' || opi_dbi_rpt_util_pkg.rate_str (
                           p_numerator       => 'c_exec_with_exceptions',
                           p_denominator     => 'c_executions',
                           p_rate_type       => 'P') || 'OPI_MEASURE6,
              ' || opi_dbi_rpt_util_pkg.change_pct_str (
                           p_new_numerator   => 'c_exec_with_exceptions',
                           p_new_denominator => 'c_executions',
                           p_old_numerator   => 'p_exec_with_exceptions',
                           p_old_denominator => 'p_executions',
                           p_measure_name    => 'OPI_MEASURE7') || ',
              ' || opi_dbi_rpt_util_pkg.nvl_str (
                           p_str         => 'p_exceptions',
                           p_default_val => 0) || ' OPI_MEASURE16,
              ' || opi_dbi_rpt_util_pkg.nvl_str (
                           p_str         => 'c_exceptions',
                           p_default_val => 0) || ' OPI_MEASURE8,
              ' || opi_dbi_rpt_util_pkg.change_str (
                           p_new_numerator   => 'c_exceptions',
                           p_old_numerator   => 'p_exceptions',
                           p_denominator     => 'p_exceptions',
                           p_measure_name    => 'OPI_MEASURE9') || ',
              ' || opi_dbi_rpt_util_pkg.rate_str (
                           p_numerator       => 'c_elapsed_time_total',
                           p_denominator     => 'c_executions_total',
                           p_rate_type       => 'NP') || 'OPI_MEASURE21,
              ' || opi_dbi_rpt_util_pkg.change_pct_str_basic(
                               p_new_numerator     => 'c_elapsed_time_total',
                               p_new_denominator   => 'c_executions_total',
                               p_old_numerator     => 'p_elapsed_time_total',
                               p_old_denominator   => 'c_executions_total',
                               p_measure_name      => 'OPI_MEASURE22') || ',
              ' || opi_dbi_rpt_util_pkg.nvl_str (
                           p_str         => 'c_executions_total',
                           p_default_val => 0) || ' OPI_MEASURE23,
              ' || opi_dbi_rpt_util_pkg.nvl_str (
                           p_str         => 'c_exec_with_exceptions_total',
                           p_default_val => 0) || ' OPI_MEASURE24,
              ' || opi_dbi_rpt_util_pkg.rate_str (
                           p_numerator       => 'c_exec_with_exceptions_total',
                           p_denominator     => 'c_executions_total',
                           p_rate_type       => 'P') || 'OPI_MEASURE25,
              ' || opi_dbi_rpt_util_pkg.change_pct_str (
                           p_new_numerator   => 'c_exec_with_exceptions_total',
                           p_new_denominator => 'c_executions_total',
                           p_old_numerator   => 'p_exec_with_exceptions_total',
                           p_old_denominator => 'p_executions_total',
                           p_measure_name    => 'OPI_MEASURE26') || ',
              ' || opi_dbi_rpt_util_pkg.nvl_str (
                           p_str         => 'c_exceptions_total',
                           p_default_val => 0) || ' OPI_MEASURE27,
              ' || opi_dbi_rpt_util_pkg.change_str (
                           p_new_numerator   => 'c_exceptions_total',
                           p_old_numerator   => 'p_exceptions_total',
                           p_denominator     => 'p_exceptions_total',
                           p_measure_name    => 'OPI_MEASURE28');

Line: 1084


    l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
                                                (p_join_tbl);

Line: 1089


        'SELECT
            ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
              || fnd_global.newline;

Line: 1101


            (SELECT (rank () over
                   (&ORDER_BY_CLAUSE nulls last,
                   ' || l_view_by_fact_col || ')) - 1 rnk,
                   ' || l_view_by_fact_col || ',
              OPI_MEASURE1,
              OPI_MEASURE2,
              OPI_MEASURE3,
              OPI_MEASURE4,
              OPI_MEASURE5,
              OPI_MEASURE6'|| fnd_global.newline;

Line: 1113


        'FROM ( SELECT  '  || fnd_global.newline ||
                         l_view_by_fact_col || fnd_global.newline ||
         ',' ||  opi_dbi_rpt_util_pkg.nvl_str (
                           p_str         => 'c_exceptions',
                           p_default_val => 0) || ' OPI_MEASURE1,
           ' ||  opi_dbi_rpt_util_pkg.change_str (
                           p_new_numerator   => 'c_exceptions',
                           p_old_numerator   => 'p_exceptions',
                           p_denominator     => 'p_exceptions',
                           p_measure_name    => 'OPI_MEASURE2') || ',
           ' ||  opi_dbi_rpt_util_pkg.percent_str(
                           p_numerator      => 'c_exceptions',
                           p_denominator    => 'c_exceptions_total',
                           p_measure_name   => 'OPI_MEASURE3') || ',
            ' ||  opi_dbi_rpt_util_pkg.nvl_str (
                           p_str         => 'c_exceptions_total',
                           p_default_val => 0) || ' OPI_MEASURE4,
            ' ||  opi_dbi_rpt_util_pkg.change_str (
                           p_new_numerator   => 'c_exceptions_total',
                           p_old_numerator   => 'p_exceptions_total',
                           p_denominator     => 'p_exceptions_total',
                           p_measure_name    => 'OPI_MEASURE5') || ',
            ' || opi_dbi_rpt_util_pkg.percent_str(
                           p_numerator      => 'c_exceptions_total',
                           p_denominator    => 'c_exceptions_total',
                           p_measure_name   => 'OPI_MEASURE6');