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

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

Line: 248


    l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0');

Line: 302


    (select (rank() over ( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;

Line: 322


     (select ' || p_view_by_col || ',
             ' || p_view_by_col || ' VIEWBY,';

Line: 588


    l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0');

Line: 633


      (select (rank() over ( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;

Line: 654


     (select ' || p_view_by_col || ',
             ' || p_view_by_col || ' VIEWBY,';

Line: 886


  l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0');

Line: 929


    (select (rank() over
        ( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;

Line: 951


     (select ' || p_view_by_col || ',
             ' || p_view_by_col || ' VIEWBY,';

Line: 1202


    l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0');

Line: 1253


      (select (rank() over ( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;

Line: 1275


     (select ' || p_view_by_col || ',
             ' || p_view_by_col || ' VIEWBY,';

Line: 1469


      l_sel_clause := 'select cal.name VIEWBY,'||fnd_global.newline;

Line: 1471


      l_sel_clause := 'select cal_name VIEWBY,'||fnd_global.newline;

Line: 1654


    l_sel_clause := 'select cal.name VIEWBY,';

Line: 1656


    l_sel_clause := 'select cal_name VIEWBY,';

Line: 1732


      l_query :=  poa_dbi_sutil_pkg.get_viewby_select_clause(l_view_by, 'PO','6.0');

Line: 1748


     (select * from (select ' || l_view_by_col || ',
      ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
      ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
      ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
      ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
      ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
      ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
      ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
      ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
      ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
      ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
      ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
      ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
      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  => 'N',
             p_col_name       => l_col_tbl,
             p_use_grpid      => 'N',
             p_in_join_tables => l_in_join_tbl);

Line: 1847


        l_query :=  poa_dbi_sutil_pkg.get_viewby_select_clause(l_view_by, 'PO','6.0');

Line: 1863


       (select * from (select company_id,
        ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
        ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
        ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
        ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
        ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
        ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
        ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
        ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
        ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
        ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
        ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
        ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
        from (
        ' || poa_dbi_template_pkg.union_all_status_sql(
                    p_mv              => l_mv_tbl,
                    p_join_tables     => l_join_tbl,
                    p_use_windowing   => 'N',
                    p_paren_count     => 3,
                    p_generate_viewby => 'Y',
                    p_diff_measures   => 'N');

Line: 1885


        l_query :=  poa_dbi_sutil_pkg.get_viewby_select_clause(l_view_by, 'PO','6.0');

Line: 1901


       (select * from (select company_id,
        ' || poa_dbi_util_pkg.rate_clause('p_contract_amt','p_purchase_amt') || ' POA_MEASURE1,
        ' || poa_dbi_util_pkg.rate_clause('c_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
        ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_MEASURE2,
        ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
        ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_MEASURE3,
        ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
        ' || poa_dbi_util_pkg.rate_clause('p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4,
        ' || poa_dbi_util_pkg.rate_clause('c_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE5,
        ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
        ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7,
        ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE8,
        ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE9
        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  => 'N',
               p_col_name       => l_col_tbl,
               p_use_grpid      => 'N',
               p_in_join_tables => l_in_join_tbl);

Line: 1992


      l_query := 'select description VIEWBY,
      nvl(c_con_type_amt_total,0) POA_MEASURE1,
      c_con_type_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total)*100 POA_PERCENT1,
      (c_con_type_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total) -
      p_con_type_amt_total/decode(p_purchase_amt_total, 0, null, p_purchase_amt_total))*100 POA_MEASURE2,
      nvl(c_purchase_amt_total,0) POA_MEASURE3,
      c_purchase_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total)*100 POA_MEASURE4
      from
      ( select lookup_code, description, p_purchase_amt_total, c_purchase_amt_total,
        decode(lookup_code, ''1'', c_contract_amt_total, c_n_contract_amt_total) c_con_type_amt_total,
        decode(lookup_code, ''1'', p_contract_amt_total, p_n_contract_amt_total) p_con_type_amt_total
        from
        ( select fl.lookup_code,
          fl.meaning description,
          c_n_contract_amt_total,
          p_n_contract_amt_total,
          c_contract_amt_total,
          p_contract_amt_total,
          c_purchase_amt_total,
          p_purchase_amt_total
          from
          ( select
            sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () c_n_contract_amt_total,
            sum(sum(decode(cal.report_date,  &BIS_PREVIOUS_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () p_n_contract_amt_total,
            sum(sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () c_contract_amt_total,
            sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () p_contract_amt_total,
            sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () c_purchase_amt_total,
            sum(sum(decode(cal.report_date,  &BIS_PREVIOUS_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () p_purchase_amt_total
            from ' || l_mv ||' fact,
            fii_time_rpt_struct_v cal
            ' || l_in_join_tables || '
            where
            fact.time_id = cal.time_id '
            || l_where_clause ||
           'and cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE )
            and bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
          ) oset,
          fnd_lookups fl
          where fl.lookup_type=''POA_CONTRACT_UTILIZATION_TYPES''
          and fl.enabled_flag = ''Y''
        )
      )';

Line: 2102


        l_query := 'select description VIEWBY,
        nvl(c_con_type_amt_total,0) POA_MEASURE1,
        c_con_type_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total)*100 POA_PERCENT1,
        (c_con_type_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total) -
        p_con_type_amt_total/decode(p_purchase_amt_total, 0, null, p_purchase_amt_total))*100 POA_MEASURE2,
        nvl(c_purchase_amt_total,0) POA_MEASURE3,
        c_purchase_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total)*100 POA_MEASURE4
        from
        ( select lookup_code, description, p_purchase_amt_total, c_purchase_amt_total,
          decode(lookup_code, ''1'', c_contract_amt_total, ''2'', c_n_contract_amt_total, c_p_contract_amt_total) c_con_type_amt_total,
          decode(lookup_code, ''1'', p_contract_amt_total,''2'', p_n_contract_amt_total, p_p_contract_amt_total) p_con_type_amt_total
          from
          ( select fl.lookup_code,
            fl.meaning description,
            sum(c_n_contract_amt_total) c_n_contract_amt_total,
            sum(p_n_contract_amt_total) p_n_contract_amt_total,
            sum(c_contract_amt_total) c_contract_amt_total,
            sum(p_contract_amt_total) p_contract_amt_total,
            sum(c_p_contract_amt_total) c_p_contract_amt_total,
            sum(p_p_contract_amt_total) p_p_contract_amt_total,
            sum(c_purchase_amt_total) c_purchase_amt_total,
            sum(p_purchase_amt_total) p_purchase_amt_total
            from
            (
              ( select
                sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () c_n_contract_amt_total,
                sum(sum(decode(cal.report_date,  &BIS_PREVIOUS_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () p_n_contract_amt_total,
                sum(sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () c_contract_amt_total,
                sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () p_contract_amt_total,
                sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE , p_contract_amt_' || l_cur_suffix || ', null))) over () c_p_contract_amt_total,
                sum(sum(decode(cal.report_date,  &BIS_PREVIOUS_ASOF_DATE, p_contract_amt_' || l_cur_suffix || ', null))) over () p_p_contract_amt_total,
                sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () c_purchase_amt_total,
                sum(sum(decode(cal.report_date,  &BIS_PREVIOUS_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () p_purchase_amt_total
                from ' || l_mv ||' fact,
                fii_time_rpt_struct_v cal
                ' || l_in_join_tables || '
                where
                fact.time_id = cal.time_id '
                || l_where_clause ||
               'and cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE )
                and bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
              )
              union all
              ( select
                sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () c_n_contract_amt_total,
                sum(sum(decode(cal.report_date,  &BIS_PREVIOUS_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () p_n_contract_amt_total,
                sum(sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () c_contract_amt_total,
                sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () p_contract_amt_total,
                sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE , p_contract_amt_' || l_cur_suffix || ', null))) over () c_p_contract_amt_total,
                sum(sum(decode(cal.report_date,  &BIS_PREVIOUS_ASOF_DATE, p_contract_amt_' || l_cur_suffix || ', null))) over () p_p_contract_amt_total,
                sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () c_purchase_amt_total,
                sum(sum(decode(cal.report_date,  &BIS_PREVIOUS_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () p_purchase_amt_total
                from ' || l_mv2 ||' fact,
                fii_time_rpt_struct_v cal
                ' || l_in_join_tables2 || '
                where
                fact.time_id = cal.time_id '
                || l_where_clause2 ||
               'and cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE )
                and bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
              )
            ) oset,
            fnd_lookups fl
            where fl.lookup_type=''POA_CONTRACT_UTILIZATION_TYPES''
            and fl.enabled_flag = ''Y''
            group by fl.lookup_code, fl.meaning
          )
        )';

Line: 2171


        l_query := 'select description VIEWBY,
        nvl(c_con_type_amt_total,0) POA_MEASURE1,
        c_con_type_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total)*100 POA_PERCENT1,
        (c_con_type_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total) -
        p_con_type_amt_total/decode(p_purchase_amt_total, 0, null, p_purchase_amt_total))*100 POA_MEASURE2,
        nvl(c_purchase_amt_total,0) POA_MEASURE3,
        c_purchase_amt_total/decode(c_purchase_amt_total, 0, null, c_purchase_amt_total)*100 POA_MEASURE4
        from
        ( select lookup_code, description, p_purchase_amt_total, c_purchase_amt_total,
          decode(lookup_code, ''1'', c_contract_amt_total, ''2'', c_n_contract_amt_total, c_p_contract_amt_total) c_con_type_amt_total,
          decode(lookup_code, ''1'', p_contract_amt_total,''2'', p_n_contract_amt_total, p_p_contract_amt_total) p_con_type_amt_total
          from
          ( select fl.lookup_code,
            fl.meaning description,
            c_n_contract_amt_total,
            p_n_contract_amt_total,
            c_contract_amt_total,
            p_contract_amt_total,
            c_p_contract_amt_total,
            p_p_contract_amt_total,
            c_purchase_amt_total,
            p_purchase_amt_total
            from
            ( select
              sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () c_n_contract_amt_total,
              sum(sum(decode(cal.report_date,  &BIS_PREVIOUS_ASOF_DATE, n_contract_amt_' || l_cur_suffix || ', null))) over () p_n_contract_amt_total,
              sum(sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () c_contract_amt_total,
              sum(sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE , contract_amt_' || l_cur_suffix || ', null))) over () p_contract_amt_total,
              sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE , p_contract_amt_' || l_cur_suffix || ', null))) over () c_p_contract_amt_total,
              sum(sum(decode(cal.report_date,  &BIS_PREVIOUS_ASOF_DATE, p_contract_amt_' || l_cur_suffix || ', null))) over () p_p_contract_amt_total,
              sum(sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () c_purchase_amt_total,
              sum(sum(decode(cal.report_date,  &BIS_PREVIOUS_ASOF_DATE, purchase_amt_' || l_cur_suffix || ', null))) over () p_purchase_amt_total
              from ' || l_mv ||' fact,
              fii_time_rpt_struct_v cal
              ' || l_in_join_tables || '
              where
              fact.time_id = cal.time_id '
              || l_where_clause ||
             'and cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE )
              and bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
            ) oset,
            fnd_lookups fl
            where fl.lookup_type=''POA_CONTRACT_UTILIZATION_TYPES''
            and fl.enabled_flag = ''Y''
          )
        )';

Line: 2411


  'select v.value VIEWBY, v.id VIEWBYID,
    oset.POA_MEASURE1 POA_MEASURE1,
    oset.POA_MEASURE1 POA_MEASURE3,
    oset.POA_PERCENT1 POA_PERCENT1,
    oset.POA_PERCENT2 POA_PERCENT2,
    oset.POA_MEASURE2 POA_MEASURE2,
    oset.POA_PERCENT3 POA_PERCENT3,
           ''' || 'pFunctionName=POA_DBI_CUD_CON_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y' || ''' POA_ATTRIBUTE1
     from
     (select ' || 'contract_type' || ',
             ' || 'contract_type' || ' VIEWBY,
      c_purchase_amt POA_MEASURE1, '
      || poa_dbi_util_pkg.rate_clause( 'c_purchase_amt', 'c_purchase_amt_total' ) || ' POA_PERCENT1,
      '
      || poa_dbi_util_pkg.change_clause (
           poa_dbi_util_pkg.rate_clause( 'c_purchase_amt', 'c_purchase_amt_total' ) ,
           poa_dbi_util_pkg.rate_clause( 'p_purchase_amt', 'p_purchase_amt_total' ) ,
           'P') || ' POA_PERCENT2,
      c_purchase_amt_total POA_MEASURE2,
      decode(c_purchase_amt_total, null, null, 100) POA_PERCENT3';