APPS.FEM_DIM_ATTRIBUTES_UTIL_PKG SQL Statements

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

Line: 17

 |                                  inserted as the dim_id
 |   Rob Flippo  03/21/2005 Bug#4215137 Added x_user_assign_allowed_flag to the
 |                          INSERT_ROW call for FEM_DIM_ATTRIBUTES_PKG.  All
 |                          attributes created from this pkg will have 'Y'
 |                          for this flag (since only seeded attributes can be
 |                          'N')
 |
 |   Tim Moore   05/02/2005 Bug#4036498 Added following functions:
 |                          Get_Dim_Attribute_Value
 |                          Get_Dim_Attr_Value_Set
 |
 |   Tim Moore   05/24/2005 Bug#4050785 Added following procedures:
 |                          New_Dim_Attr_Version
 |                          New_Dim_Attr_Default
 |   Rob Flippo  08/16/2006 Bug#5463488 overlad get_dim_attribute_value
 |                          function so can be called via sql stmt
 *=======================================================================*/

/* ***********************
** Package constants
** ***********************/


/* ***********************
** Package variables
** ***********************/
--dbms_utility.format_call_stack                 VARCHAR2(2000);

Line: 149

         SELECT B.dimension_id
         INTO v_dimension_id
         FROM fem_dimensions_b B, fem_xdim_dimensions X
         WHERE B.dimension_varchar_label = p_dimension_varchar_label
         AND B.dimension_id = X.dimension_id
         AND X.attribute_table_name IS NOT NULL;

Line: 163

      SELECT count(*)
      INTO v_count
      FROM fem_dim_attributes_b
      WHERE attribute_varchar_label = p_attr_varchar_label
      AND dimension_id = v_dimension_id ;

Line: 175

      SELECT count(*)
      INTO v_count
      FROM fem_dim_attributes_tl
      WHERE attribute_name = p_attr_name;

Line: 185

      SELECT count(*)
      INTO v_count
      FROM fnd_lookup_values
      WHERE lookup_type = 'FEM_ATTRIBUTE_ORDER_TYPE_DSC'
      AND lookup_code = p_attr_order_type_code;

Line: 233

         SELECT dimension_id,
           DECODE(member_data_type_code,'VARCHAR',
                  'DIM_ATTRIBUTE_VARCHAR_MEMBER',
                  'NUMBER','DIM_ATTRIBUTE_NUMERIC_MEMBER',null)
         INTO v_attr_dimension_id, v_attr_value_column_name
         FROM fem_xdim_dimensions
         WHERE dimension_id IN (SELECT dimension_id FROM fem_dimensions_b
                                WHERE dimension_varchar_label = p_attr_dimension_varchar_label);

Line: 250

      SELECT fem_dim_attributes_b_s.nextval
      INTO v_attribute_id
      FROM dual;

Line: 254

      SELECT fem_dim_attr_versions_b_s.nextval
      INTO v_version_id
      FROM dual;

Line: 258

      FEM_DIM_ATTRIBUTES_PKG.INSERT_ROW(
         X_ROWID => v_rowid
        ,X_ATTRIBUTE_ID => v_attribute_id
        ,X_READ_ONLY_FLAG => 'N'
        ,X_OBJECT_VERSION_NUMBER => 1
        ,X_USER_ASSIGN_ALLOWED_FLAG => 'Y'
        ,X_ASSIGNMENT_IS_READ_ONLY_FLAG => 'N'
        ,X_PERSONAL_FLAG => v_personal_flag
        ,X_DIMENSION_ID => v_dimension_id
        ,X_ATTRIBUTE_DIMENSION_ID => v_attr_dimension_id
        ,X_ATTRIBUTE_VALUE_COLUMN_NAME => v_attr_value_column_name
        ,X_ATTRIBUTE_DATA_TYPE_CODE => p_attr_data_type_code
        ,X_ALLOW_MULTIPLE_ASSIGNMENT_FL => v_allow_mult_assign_flag
        ,X_ATTRIBUTE_ORDER_TYPE_CODE => p_attr_order_type_code
        ,X_ATTRIBUTE_REQUIRED_FLAG => v_attr_required_flag
        ,X_USE_INHERITANCE_FLAG => v_use_inheritance_flag
        ,X_QUERYABLE_FOR_REPORTING_FLAG => v_queryable_for_reporting_flag
        ,X_ALLOW_MULTIPLE_VERSIONS_FLAG => v_allow_mult_versions_flag
        ,X_ATTRIBUTE_VARCHAR_LABEL => p_attr_varchar_label
        ,X_ATTRIBUTE_NAME => p_attr_name
        ,X_DESCRIPTION  => p_attr_description
        ,X_CREATION_DATE => sysdate
        ,X_CREATED_BY => pc_user_id
        ,X_LAST_UPDATE_DATE => sysdate
        ,X_LAST_UPDATED_BY => pc_user_id
        ,X_LAST_UPDATE_LOGIN => pc_last_update_login);

Line: 285

      FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
         X_ROWID => v_rowid
        ,X_VERSION_ID => v_version_id
        ,X_ATTRIBUTE_ID => v_attribute_id
        ,X_AW_SNAPSHOT_FLAG => 'N'
        ,X_VERSION_DISPLAY_CODE => p_version_display_code
        ,X_OBJECT_VERSION_NUMBER => 1
        ,X_DEFAULT_VERSION_FLAG => 'Y'
        ,X_PERSONAL_FLAG => v_personal_flag
        ,X_VERSION_NAME => p_version_name
        ,X_DESCRIPTION => p_version_description
        ,X_CREATION_DATE => sysdate
        ,X_CREATED_BY => pc_user_id
        ,X_LAST_UPDATE_DATE => sysdate
        ,X_LAST_UPDATED_BY => pc_user_id
        ,X_LAST_UPDATE_LOGIN => pc_last_update_login);

Line: 532

   SELECT dimension_id
   INTO   v_dim_id
   FROM   fem_dimensions_b
   WHERE  dimension_varchar_label = p_dimension_varchar_label;

Line: 544

SELECT attribute_table_name,
       member_b_table_name,
       member_col,
       value_set_required_flag
INTO   v_attr_tab,
       v_mem_b_tab,
       v_mem_col,
       v_vs_req_flg
FROM   fem_xdim_dimensions
WHERE  dimension_id = v_dim_id;

Line: 567

   SELECT attribute_id,
          attribute_dimension_id,
          attribute_value_column_name
   INTO   v_attr_id,
          v_attr_dim_id,
          v_attr_val_col
   FROM   fem_dim_attributes_b
   WHERE  attribute_varchar_label = p_attribute_varchar_label
   AND    dimension_id = v_dim_id;

Line: 585

'SELECT '||v_mem_col||
' FROM '||v_mem_b_tab||
' WHERE '||v_mem_col||' = :b_member_id';

Line: 606

         SELECT value_set_id
         INTO v_vs_id
         FROM fem_value_sets_b
         WHERE value_set_id = p_value_set_id;

Line: 616

      'SELECT '||v_mem_col||
      ' FROM '||v_mem_b_tab||
      ' WHERE '||v_mem_col||' = :b_member_id'||
      ' AND value_set_id = :b_vs_id';

Line: 643

      SELECT version_id
      INTO v_attr_ver_id
      FROM fem_dim_attr_versions_b
      WHERE attribute_id =
         (SELECT attribute_id
          FROM   fem_dim_attributes_b
          WHERE  attribute_id = v_attr_id
          AND    dimension_id = v_dim_id)
      AND default_version_flag = 'Y';

Line: 663

      SELECT version_id
      INTO v_attr_ver_id
      FROM fem_dim_attr_versions_b
      WHERE attribute_id =
         (SELECT attribute_id
          FROM   fem_dim_attributes_b
          WHERE  attribute_id = v_attr_id
          AND    dimension_id = v_dim_id)
      AND version_display_code = p_attr_version_display_code;

Line: 684

   'SELECT '||v_attr_val_col||
   ' FROM '||v_attr_tab||' A,'||
   '      fem_dim_attributes_b B'||
   ' WHERE B.attribute_id = :b_attr_id'||
   ' AND A.attribute_id = B.attribute_id'||
   ' AND A.value_set_id = :b_vs_id'||
   ' AND A.version_id = :b_attr_ver_id'||
   ' AND A.'||v_mem_col||' = :b_mem_id';

Line: 706

   'SELECT '||v_attr_val_col||
   ' FROM '||v_attr_tab||' A,'||
   '      fem_dim_attributes_b B'||
   ' WHERE B.attribute_id = :b_attr_id'||
   ' AND A.attribute_id = B.attribute_id'||
   ' AND A.version_id = :b_attr_ver_id'||
   ' AND A.'||v_mem_col||' = :b_mem_id';

Line: 732

   SELECT member_b_table_name,
          member_col,
          member_display_code_col
   INTO   v_mem_b_tab,
          v_mem_col,
          v_mem_dc_col
   FROM   fem_xdim_dimensions
   WHERE  dimension_id = v_attr_dim_id;

Line: 745

   'SELECT '||v_mem_dc_col||
   ' FROM '||v_mem_b_tab||
   ' WHERE '||v_mem_col||' = :b_attr_value';

Line: 1025

   SELECT dimension_id
   INTO   v_dim_id
   FROM   fem_dimensions_b
   WHERE  dimension_varchar_label = p_dimension_varchar_label;

Line: 1037

SELECT attribute_table_name,
       member_b_table_name,
       member_col,
       value_set_required_flag
INTO   v_attr_tab,
       v_mem_b_tab,
       v_mem_col,
       v_vs_req_flg
FROM   fem_xdim_dimensions
WHERE  dimension_id = v_dim_id;

Line: 1060

   SELECT attribute_id,
          attribute_dimension_id,
          attribute_value_column_name
   INTO   v_attr_id,
          v_attr_dim_id,
          v_attr_val_col
   FROM   fem_dim_attributes_b
   WHERE  attribute_varchar_label = p_attribute_varchar_label
   AND    dimension_id = v_dim_id;

Line: 1078

'SELECT '||v_mem_col||
' FROM '||v_mem_b_tab||
' WHERE '||v_mem_col||' = :b_member_id';

Line: 1099

         SELECT value_set_id
         INTO v_vs_id
         FROM fem_value_sets_b
         WHERE value_set_id = p_value_set_id;

Line: 1109

      'SELECT '||v_mem_col||
      ' FROM '||v_mem_b_tab||
      ' WHERE '||v_mem_col||' = :b_member_id'||
      ' AND value_set_id = :b_vs_id';

Line: 1136

      SELECT version_id
      INTO v_attr_ver_id
      FROM fem_dim_attr_versions_b
      WHERE attribute_id =
         (SELECT attribute_id
          FROM   fem_dim_attributes_b
          WHERE  attribute_id = v_attr_id
          AND    dimension_id = v_dim_id)
      AND default_version_flag = 'Y';

Line: 1156

      SELECT version_id
      INTO v_attr_ver_id
      FROM fem_dim_attr_versions_b
      WHERE attribute_id =
         (SELECT attribute_id
          FROM   fem_dim_attributes_b
          WHERE  attribute_id = v_attr_id
          AND    dimension_id = v_dim_id)
      AND version_display_code = p_attr_version_display_code;

Line: 1177

   'SELECT dim_attribute_value_set_id'||
   ' FROM '||v_attr_tab||
   ' WHERE '||v_mem_col||' = :b_mem_id'||
   ' AND value_set_id = :b_vs_id'||
   ' AND attribute_id = :b_attr_id';

Line: 1195

   'SELECT dim_attribute_value_set_id'||
   ' FROM '||v_attr_tab||
   ' WHERE '||v_mem_col||' = :b_mem_id'||
   ' AND attribute_id = :b_attr_id';

Line: 1216

   SELECT value_set_display_code
   INTO v_attr_value
   FROM fem_value_sets_b
   WHERE value_set_id = v_attr_vs_id
   AND dimension_id = v_attr_dim_id;

Line: 1442

   SELECT dimension_id
   INTO   v_dim_id
   FROM   fem_dimensions_b
   WHERE  dimension_varchar_label = p_dimension_varchar_label;

Line: 1454

SELECT attribute_table_name
INTO   v_attr_tab
FROM   fem_xdim_dimensions
WHERE  dimension_id = v_dim_id;

Line: 1468

   SELECT attribute_id,
          attribute_required_flag,
          allow_multiple_versions_flag
   INTO   v_attr_id,
          v_attr_req_flg,
          v_mult_ver_flg
   FROM   fem_dim_attributes_b
   WHERE  attribute_varchar_label = p_attribute_varchar_label
   AND    dimension_id = v_dim_id;

Line: 1504

SELECT fem_dim_attr_versions_b_s.NEXTVAL
INTO v_ver_id FROM dual;

Line: 1507

FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
   x_rowid => v_row_id,
   x_version_id => v_ver_id,
   x_aw_snapshot_flag => c_aw_flg,
   x_version_display_code => p_version_display_code,
   x_object_version_number => c_obj_ver_no,
   x_default_version_flag => p_default_version_flag,
   x_personal_flag => c_pers_flg,
   x_attribute_id => v_attr_id,
   x_version_name => p_version_name,
   x_description => p_version_desc,
   x_creation_date => sysdate,
   x_created_by => pc_user_id,
   x_last_update_date => sysdate,
   x_last_updated_by => pc_user_id,
   x_last_update_login => pc_last_update_login);

Line: 1526

   UPDATE fem_dim_attr_versions_b
   SET default_version_flag='N'
   WHERE attribute_id = v_attr_id
   AND version_id <> v_ver_id;

Line: 1665

   SELECT dimension_id
   INTO   v_dim_id
   FROM   fem_dimensions_b
   WHERE  dimension_varchar_label = p_dimension_varchar_label;

Line: 1677

SELECT attribute_table_name
INTO   v_attr_tab
FROM   fem_xdim_dimensions
WHERE  dimension_id = v_dim_id;

Line: 1691

   SELECT attribute_id,
          attribute_required_flag,
          allow_multiple_versions_flag
   INTO   v_attr_id,
          v_attr_req_flg,
          v_mult_ver_flg
   FROM   fem_dim_attributes_b
   WHERE  attribute_varchar_label = p_attribute_varchar_label
   AND    dimension_id = v_dim_id;

Line: 1715

   SELECT version_id
   INTO v_ver_id
   FROM fem_dim_attr_versions_b
   WHERE attribute_id = v_attr_id
   AND   version_display_code = p_version_display_code;

Line: 1728

UPDATE fem_dim_attr_versions_b
SET default_version_flag='N'
WHERE attribute_id = v_attr_id
AND version_id <> v_ver_id;

Line: 1733

UPDATE fem_dim_attr_versions_b
SET default_version_flag='Y'
WHERE attribute_id = v_attr_id
AND version_id = v_ver_id;