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

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

Line: 113


 * Procedure   :  CAL_SELECTIVITY
 * Type        :  Private
 * Pre_reqs    :
 * Parameters  :
 * input
 *        p_table_name
 *        v_colname   : array of column name
 *        numcol      : number of name
 * output
 *        v_colname   : array of sorted column name by selectivity
 *        o_sel       : array of odinal selectivity
 *        std_dev     : array of standard deviation
 */
PROCEDURE CAL_SELECTIVITY( p_table_name    IN            varchar2,
                           v_colname       IN OUT NOCOPY name_varray,
                           o_sel           IN OUT NOCOPY value_varray,
                           std_dev         IN OUT NOCOPY value_varray,
                           flag            IN OUT NOCOPY value_varray,
                           numcol          IN            integer,
                           x_return_status OUT NOCOPY    varchar2)
IS

  l_status         VARCHAR2(30);

Line: 147


                   'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.start',
                   'Start of the procedure jty_tae_index_creation_pvt.cal_selectivity ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));

Line: 161


  SELECT NVL(dt.NUM_ROWS,1)
  INTO   v_cardinality
  FROM   dba_tables dt
  WHERE  dt.owner = l_jtf_schema
  AND    dt.table_name = p_table_name;

Line: 169


                   'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.cardinality',
                   'Number of rows for table ' || p_table_name || ' : ' || v_cardinality);

Line: 175


                     'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.cardinality',
                     'API jty_tae_index_creation_pvt.cal_selectivity has failed as the number of rows in the table ' ||
                         p_table_name || ' is 0');

Line: 186


        SELECT 100 - (NVL(dtc.num_distinct,1)*100/v_cardinality)
        INTO   o_sel(i)
        FROM   dba_tab_columns dtc, user_synonyms syn
        WHERE  syn.synonym_name = UPPER(p_table_name)
		AND    dtc.owner = syn.table_owner
        AND    dtc.table_name = syn.table_name
        AND    dtc.column_name = UPPER(v_colname(i));

Line: 207


                     'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.bubble_sort',
                     'API jty_tae_index_creation_pvt.bubble_sort has failed');

Line: 215


                   'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.end',
                   'End of the procedure jty_tae_index_creation_pvt.cal_selectivity ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));

Line: 222


                     'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.l_schema_notfound',
                     'Schema name corresponding to JTF application not found');

Line: 228


                     'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.no_data_found',
                     substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));

Line: 234


                     'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.g_exc_error',
                     'jty_tae_index_creation_pvt.cal_selectivity has failed with G_EXC_ERROR exception');

Line: 240


                     'jtf.plsql.jty_tae_index_creation_pvt.cal_selectivity.others',
                     substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));

Line: 243


END CAL_SELECTIVITY;

Line: 246


 * Procedure   :  DEA_SELECTIVITY
 * Type        :  Private
 * Pre_reqs    :
 * Description :
 * Parameters  :
 * input  JTY_DEA_ATTR_FACTORS.SQUAL_ALIAS
 * outout JTY_DEA_ATTR_FACTORS.INPUT_SELECTIVITY         is populated with selectivity order
          JTY_DEA_ATTR_FACTORS.INPUT_ORDINAL_SELECTIVITY is populated with ordinal_selectivity
          JTY_DEA_ATTR_FACTORS.INPUT_DEVIATION           is populated with standard deviation
 */

PROCEDURE DEA_SELECTIVITY(p_TABLE_NAME    IN         VARCHAR2,
                          x_return_status OUT NOCOPY VARCHAR2)
IS

  -- SOLIN, Bug 5893926
  -- extend to 300 elements
  col_name name_varray :=   name_varray(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);

Line: 307


  SELECT DISTINCT
    A.TAE_COL_MAP                 sqname,
    A.INPUT_DEVIATION             dev,
    A.INPUT_ORDINAL_SELECTIVITY   ord_sele,
    A.INPUT_SELECTIVITY           sele,
    decode(A.UPDATE_SELECTIVITY_FLAG,'Y',1,0) flag
  FROM JTY_DEA_ATTR_FACTORS A
  WHERE A.USE_TAE_COL_IN_INDEX_FLAG = 'Y'
  AND A.TAE_COL_MAP is not null ;

Line: 322


                   'jtf.plsql.jty_tae_index_creation_pvt.dea_selectivity.start',
                   'Start of the procedure jty_tae_index_creation_pvt.dea_selectivity ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));

Line: 343


                     'jtf.plsql.jty_tae_index_creation_pvt.dea_selectivity.col_name',
                     'API jty_tae_index_creation_pvt.dea_selectivity has failed as there is no valid column name, or all flag = No');

Line: 349


  CAL_SELECTIVITY(
    p_table_name    => p_table_name,
    v_colname       => col_name,
    o_sel           => o_sel,
    std_dev         => std_dev,
    flag            => flag,
    numcol          => i-1,
    x_return_status => x_return_status);

Line: 361


                     'jtf.plsql.jty_tae_index_creation_pvt.dea_selectivity.cal_selectivity',
                     'API jty_tae_index_creation_pvt.cal_selectivity has failed');

Line: 369


    UPDATE JTY_DEA_ATTR_FACTORS
    SET INPUT_SELECTIVITY           = i,
        INPUT_ORDINAL_SELECTIVITY   = o_sel(i),
        INPUT_DEVIATION             = std_dev(i)
    WHERE  TAE_COL_MAP = col_name(i);

Line: 380


                   'jtf.plsql.jty_tae_index_creation_pvt.dea_selectivity.end',
                   'End of the procedure jty_tae_index_creation_pvt.dea_selectivity ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));

Line: 387


                     'jtf.plsql.jty_tae_index_creation_pvt.dea_selectivity.g_exc_error',
                     'jty_tae_index_creation_pvt.dea_selectivity has failed with G_EXC_ERROR exception');

Line: 393


                     'jtf.plsql.jty_tae_index_creation_pvt.dea_selectivity.others',
                     substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));

Line: 396


END DEA_SELECTIVITY;

Line: 400


 * Procedure   :  SELECTIVITY
 * Type        :  Private
 * Pre_reqs    :
 * Description :
 * Parameters  :
 * input  JTF_TAE_QUAL_FACTORS.SQUAL_ALIAS
 * outout JTF_TAE_QUAL_FACTORS.INPUT_SELECTIVITY         is populated with selectivity order
          JTF_TAE_QUAL_FACTORS.INPUT_ORDINAL_SELECTIVITY is populated with ordinal_selectivity
          JTF_TAE_QUAL_FACTORS.INPUT_DEVIATION           is populated with standard deviation
 */

PROCEDURE SELECTIVITY(p_TABLE_NAME    IN         VARCHAR2,
                      p_mode          IN         VARCHAR2,
                      p_source_id     IN         NUMBER,
                      x_return_status OUT NOCOPY VARCHAR2)
IS

  col_name name_varray :=  name_varray(
     0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
     0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
     0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
     0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
     0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
     0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
     0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);

Line: 462


  SELECT DISTINCT
    A.TAE_COL_MAP                 sqname,
    A.INPUT_DEVIATION             dev,
    A.INPUT_ORDINAL_SELECTIVITY   ord_sele,
    A.INPUT_SELECTIVITY           sele,
    decode(A.UPDATE_SELECTIVITY_FLAG,'Y',1,0) flag
  FROM JTF_TAE_QUAL_FACTORS A
  WHERE A.USE_TAE_COL_IN_INDEX_FLAG = 'Y'
  AND A.TAE_COL_MAP is not null ;

Line: 473


  SELECT DISTINCT
    A.VALUES_COL_MAP              sqname,
    NULL                          dev,
    A.INPUT_ORDINAL_SELECTIVITY   ord_sele,
    A.INPUT_SELECTIVITY           sele,
    1                             flag
  FROM jty_terr_values_idx_details A,
       jty_terr_values_idx_header  B
  WHERE A.VALUES_COL_MAP is not null
  AND   B.delete_flag = 'N'
  AND   A.terr_values_idx_header_id = B.terr_values_idx_header_id
  AND   B.source_id = cl_source_id;

Line: 487


  SELECT DISTINCT
    A.VALUES_COL_MAP              sqname,
    NULL                          dev,
    A.INPUT_ORDINAL_SELECTIVITY   ord_sele,
    A.INPUT_SELECTIVITY           sele,
    1                             flag
  FROM jty_dea_values_idx_details A,
       jty_dea_values_idx_header  B
  WHERE A.VALUES_COL_MAP is not null
  AND   A.dea_values_idx_header_id = B.dea_values_idx_header_id
  AND   B.source_id = cl_source_id;

Line: 502


                   'jtf.plsql.jty_tae_index_creation_pvt.selectivity.start',
                   'Start of the procedure jty_tae_index_creation_pvt.selectivity ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));

Line: 545


                     'jtf.plsql.jty_tae_index_creation_pvt.selectivity.col_name',
                     'API jty_tae_index_creation_pvt.selectivity has failed as there is no valid column name, or all flag = No');

Line: 551


  CAL_SELECTIVITY(
    p_table_name    => p_table_name,
    v_colname       => col_name,
    o_sel           => o_sel,
    std_dev         => std_dev,
    flag            => flag,
    numcol          => i-1,
    x_return_status => x_return_status);

Line: 563


                     'jtf.plsql.jty_tae_index_creation_pvt.selectivity.cal_selectivity',
                     'API jty_tae_index_creation_pvt.selectivity has failed');

Line: 572


      UPDATE JTF_TAE_QUAL_FACTORS
      SET INPUT_SELECTIVITY           = i,
          INPUT_ORDINAL_SELECTIVITY   = o_sel(i),
          INPUT_DEVIATION             = std_dev(i)
      WHERE  TAE_COL_MAP = col_name(i);

Line: 581


        UPDATE jty_dea_values_idx_details
        SET INPUT_SELECTIVITY           = i,
            INPUT_ORDINAL_SELECTIVITY   = o_sel(i)
        WHERE  VALUES_COL_MAP = col_name(i);

Line: 588


        UPDATE jty_terr_values_idx_details
        SET INPUT_SELECTIVITY           = i,
            INPUT_ORDINAL_SELECTIVITY   = o_sel(i)
        WHERE  VALUES_COL_MAP = col_name(i);

Line: 600


                   'jtf.plsql.jty_tae_index_creation_pvt.selectivity.end',
                   'End of the procedure jty_tae_index_creation_pvt.selectivity ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));

Line: 607


                     'jtf.plsql.jty_tae_index_creation_pvt.selectivity.g_exc_error',
                     'jty_tae_index_creation_pvt.selectivity has failed with G_EXC_ERROR exception');

Line: 613


                     'jtf.plsql.jty_tae_index_creation_pvt.selectivity.others',
                     substr(SQLCODE || ' : ' || SQLERRM, 1, 4000));

Line: 616


END SELECTIVITY;

Line: 783


  SELECT  P.qual_product_id         qual_product_id,
          p.RELATION_PRODUCT        RELATION_PRODUCT,
          MAX(p.index_name)         index_name,
          MAX(p.first_char_flag)    first_char_flag,
          COUNT(r.qual_factor_id)   cartesian_terr_X_factors
  FROM  jtf_terr_denorm_rules_all d
       ,jtf_terr_qtype_usgs_all jtqu
	   ,jtf_qual_type_usgs_all jqtu
       ,JTF_TAE_QUAL_PRODUCTS P
       , JTF_TAE_QUAL_PROD_FACTORS R
  WHERE jtqu.qual_relation_product = p.relation_product
  AND   jqtu.source_id = d.source_id
  AND   jqtu.qual_type_id = p.trans_object_type_id
  AND   d.terr_id = jtqu.terr_id
  AND   jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
  AND   d.source_id = p.source_id
  AND   P.qual_product_id = R.qual_product_id
  AND   P.BUILD_INDEX_FLAG = 'Y'
  AND   p.TRANS_OBJECT_TYPE_ID = cl_trans_id
  AND   P.SOURCE_ID = cl_source_id
  GROUP BY P.qual_product_id, p.RELATION_PRODUCT
  ORDER BY cartesian_terr_X_factors DESC, first_char_flag;

Line: 807


  SELECT  DISTINCT TAE_COL_MAP, J2.INPUT_SELECTIVITY
  FROM    JTF_TAE_QUAL_PRODUCTS J3,
          JTF_TAE_QUAL_FACTORS J2,
          JTF_TAE_QUAL_PROD_FACTORS J1
  WHERE J1.qual_product_id = c_pid
  AND   J1.qual_product_id = J3.qual_product_id
  AND   J1.qual_factor_id = J2.qual_factor_id
  AND   J2.USE_TAE_COL_IN_INDEX_FLAG = 'Y'
  AND   J2.TAE_COL_MAP is NOT NULL
  ORDER BY J2.INPUT_SELECTIVITY;

Line: 819


  SELECT  P.qual_product_id         qual_product_id,
          p.RELATION_PRODUCT        RELATION_PRODUCT,
          p.index_name         index_name,
          p.first_char_flag    first_char_flag,
          rownum               index_counter
  FROM    JTF_TAE_QUAL_PRODUCTS P
  WHERE   P.BUILD_INDEX_FLAG = 'Y'
  AND     P.FIRST_CHAR_FLAG = 'Y'
  AND     p.TRANS_OBJECT_TYPE_ID = cl_trans_id
  AND     P.SOURCE_ID = cl_source_id;

Line: 831


  select  f.tae_col_map, qual_usg_id, input_selectivity
  from    jtf_tae_qual_prod_factors pf,
          jtf_tae_qual_factors f
  where pf.qual_product_id = cl_pid
  and   f.qual_factor_id = pf.qual_factor_id
  and   f.tae_col_map is not null
  order by input_selectivity desc;

Line: 840


  select NON_NULL_TAE_COL_MAPS, RELATION_PRODUCT
  from (
         select  count(*) NON_NULL_TAE_COL_MAPS,
                 p.qual_product_id QUAL_PRODUCT_ID,
                 p.relation_product RELATION_PRODUCT
         from JTF_TAE_QUAL_products p,
              JTF_TAE_QUAL_prod_factors pf,
              JTF_TAE_QUAL_FACTORS f
         where p.qual_product_id = pf.qual_product_id
         and   pf.qual_factor_id = f.qual_factor_id
         and   p.source_id = cl_source_id
         and   p.trans_object_type_id = cl_trans_id
         and   p.relation_product > 0
         and   tae_col_map is not null
         and   p.relation_product = cp_product
         group by p.qual_product_id, p.relation_product
       )
  where NON_NULL_TAE_COL_MAPS = cp_non_null_tae_col_maps;

Line: 860


  SELECT  P.dea_attr_products_id         dea_attr_products_id,
          p.attr_relation_product        attr_relation_product,
          MAX(p.index_name)              index_name,
          MAX(p.first_char_flag)         first_char_flag,
          COUNT(r.dea_attr_factors_id)   cartesian_terr_X_factors
  FROM  jty_denorm_dea_rules_all d
       ,jtf_terr_qtype_usgs_all jtqu
	   ,jtf_qual_type_usgs_all jqtu
       ,jty_dea_attr_products P
       ,jty_dea_attr_prod_factors R
  WHERE jtqu.qual_relation_product = p.attr_relation_product
  AND   jqtu.source_id = d.source_id
  AND   jqtu.qual_type_id = p.trans_type_id
  AND   d.terr_id = jtqu.terr_id
  AND   jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
  AND   d.source_id = p.source_id
  AND   P.dea_attr_products_id = R.dea_attr_products_id
  AND   P.build_index_flag = 'Y'
  AND   p.trans_type_id = cl_trans_id
  AND   P.source_id = cl_source_id
  GROUP BY P.dea_attr_products_id, p.attr_relation_product
  ORDER BY cartesian_terr_X_factors DESC, first_char_flag;

Line: 884


  SELECT  DISTINCT TAE_COL_MAP, J2.INPUT_SELECTIVITY
  FROM    jty_dea_attr_products J3,
          jty_dea_attr_factors J2,
          jty_dea_attr_prod_factors J1
  WHERE J1.dea_attr_products_id = c_pid
  AND   J1.dea_attr_products_id = J3.dea_attr_products_id
  AND   J1.dea_attr_factors_id = J2.dea_attr_factors_id
  AND   J2.USE_TAE_COL_IN_INDEX_FLAG = 'Y'
  AND   J2.TAE_COL_MAP is NOT NULL
  ORDER BY J2.INPUT_SELECTIVITY;

Line: 896


  SELECT  P.dea_attr_products_id    dea_attr_products_id,
          p.attr_relation_product   attr_relation_product,
          p.index_name              index_name,
          p.first_char_flag         first_char_flag,
          rownum                    index_counter
  FROM    jty_dea_attr_products P
  WHERE   P.BUILD_INDEX_FLAG = 'Y'
  AND     P.FIRST_CHAR_FLAG = 'Y'
  AND     p.trans_type_id = cl_trans_id
  AND     P.source_id = cl_source_id;

Line: 908


  select  f.tae_col_map, qual_usg_id, input_selectivity
  from    jty_dea_attr_prod_factors pf,
          jty_dea_attr_factors f
  where pf.dea_attr_products_id = cl_pid
  and   f.dea_attr_factors_id = pf.dea_attr_factors_id
  and   f.tae_col_map is not null
  order by input_selectivity desc;

Line: 917


  select NON_NULL_TAE_COL_MAPS, attr_relation_product
  from (
         select  count(*) NON_NULL_TAE_COL_MAPS,
                 p.dea_attr_products_id dea_attr_products_id,
                 p.attr_relation_product attr_relation_product
         from jty_dea_attr_products p,
              jty_dea_attr_prod_factors pf,
              jty_dea_attr_factors f
         where p.dea_attr_products_id = pf.dea_attr_products_id
         and   pf.dea_attr_factors_id = f.dea_attr_factors_id
         and   p.source_id = cl_source_id
         and   p.trans_type_id = cl_trans_id
         and   p.attr_relation_product > 0
         and   tae_col_map is not null
         and   p.attr_relation_product = cp_product
         group by p.dea_attr_products_id, p.attr_relation_product
       )
  where NON_NULL_TAE_COL_MAPS = cp_non_null_tae_col_maps;

Line: 937


  select index_name
  from all_indexes
  where table_name = cl_table_name
  and table_owner = cl_owner;

Line: 963


  SELECT MIN(TO_NUMBER(v.value))
  INTO   l_dop
  FROM   v$parameter v
  WHERE v.name = 'parallel_max_servers'
  OR    v.name = 'cpu_count';

Line: 975


  SELECT i.tablespace, i.index_tablespace, u.oracle_username
  INTO l_table_tablespace, l_idx_tablespace, l_ora_username
  FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
  WHERE a.application_short_name = 'JTF'
  AND a.application_id = i.application_id
  AND u.oracle_id = i.oracle_id;

Line: 997


    SELECT index_extn
    INTO   l_index_extn
    FROM   jty_trans_usg_pgm_details
    WHERE  source_id     = p_source_id
    AND    trans_type_id = p_trans_id
    AND    program_name  = p_program_name;

Line: 1013


    SELECT qual_type_usg_id
    INTO   l_qual_type_usg_id
    FROM   jtf_qual_type_usgs_all
    WHERE  source_id = p_source_id
    AND    qual_type_id = p_trans_id;

Line: 1628


  SELECT aidx.owner, aidx.INDEX_NAME
  FROM   DBA_INDEXES aidx
  WHERE  aidx.table_name = cl_table_name
  AND    aidx.table_owner = cl_jtf_schema
  AND aidx.index_name not in ('JTF_TAE_TN1002_CASE_N1W', 'JTF_TAE_TN1003_CASE_N1W', 'JTF_TAE_TN1004_CASE_N1W', 'JTF_TAE_TN1105_CASE_N1W', 'JTF_TAE_TN1106_CASE_N1W');