APPS.FEM_DIM_SNAPSHOT_ENG_PKG SQL Statements

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

Line: 120

 |     2. Deletes all previously captured version TL information for the
 |        same created_by_object_id
 |     3. Inserts the version TL information into the
 |        FEM_DSNP_DIM_ATTR_VRS_TL table for all shared versions in the system.
 |        As of FEM.D, there is no "personal version" concept (i.e,. all versions
 |        are shared), however the dynamic SQL filters on personal_Flag = 'N'
 |        just in case for future implementation of such a feature.
 |     4. Deletes all previously captured attribute assignments from the
 |        target DNSP ATTR table for the same created_by_object_id
 |     5. Fetches the list of all attributed dimensions (with target snapshot
 |        tables into a cursor
 |     6. For each fetched dimension it inserts all attribute assignment rows
 |        for shared dimension members into the target DNSP ATTR table.  It
 |        commits after each dimension.
 |
 | HISTORY
 |
 |    29-JUN-05  Created
 |
 |
 +=========================================================================*/

PROCEDURE Main (
   x_return_status                OUT NOCOPY VARCHAR2,
   x_msg_count                    OUT NOCOPY NUMBER,
   x_msg_data                     OUT NOCOPY VARCHAR2,
   p_api_version                   IN NUMBER     DEFAULT c_api_version,
   p_init_msg_list                 IN VARCHAR2   DEFAULT c_false,
   p_commit                        IN VARCHAR2   DEFAULT c_true,
   p_encoded                       IN VARCHAR2   DEFAULT c_true,
   p_dim_snapshot_obj_def_id       IN NUMBER
)

IS
   c_api_name  CONSTANT VARCHAR2(30) := 'main';

Line: 162

   v_sql_delete_stmt      VARCHAR2(4000);

Line: 163

   v_sql_insert_stmt      VARCHAR2(4000);

Line: 164

   v_sql_vers_delete_stmt VARCHAR2(4000);

Line: 165

   v_sql_vers_insert_stmt VARCHAR2(4000);

Line: 171

   SELECT X.dimension_id
      , X.dimension_varchar_label
      , X.value_set_required_flag
      , X.member_col
      , X.attribute_table_name
      , X.dsnp_attribute_table_name
      , X.member_b_table_name
   FROM fem_xdim_dimensions_vl X, fem_dsnp_rule_dims R
   WHERE X.attribute_table_name is not null
   AND X.dsnp_attribute_table_name is not null
   AND X.dimension_id = R.dimension_id
   AND R.dim_snapshot_obj_def_id = p_dim_snapshot_obj_def_id
   ORDER BY dimension_id;

Line: 218

SELECT O.object_id
INTO v_object_id
FROM fem_object_definition_b D, fem_object_catalog_b O
WHERE D.object_definition_id = p_dim_snapshot_obj_def_id
AND D.object_id = O.object_id
AND O.object_type_code = 'DIMENSION_SNAPSHOT';

Line: 231

SELECT sysdate
INTO v_sysdate
FROM dual;

Line: 236

v_sql_vers_delete_stmt :=
   'DELETE FROM fem_dsnp_dim_attr_vrs_tl '||
   ' WHERE created_by_object_id = '||v_object_id;

Line: 242

   p_module   => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name||'.v_sql_vers_delete_stmt',
   p_msg_text => v_sql_vers_delete_stmt);

Line: 247

	v_sql_vers_insert_stmt :=
       'INSERT INTO fem_dsnp_dim_attr_vrs_tl '||
       '(created_by_object_id'||
       ', version_id'||
       ', language'||
       ', source_lang'||
       ', version_name'||
       ', description'||
       ', created_by'||
       ', creation_date'||
       ', last_updated_by'||
       ', last_update_date'||
       ', last_update_login)'||
       ' SELECT '||v_object_id||
       ', V.version_id'||
       ', V.language'||
       ', V.source_lang'||
       ', V.version_name'||
       ', V.description'||
       ' ,'||c_user_id||
       ' ,:b_v_sysdate'||
       ' ,'||c_user_id||
       ' ,:b_v_sysdate'||
       ' ,'||c_login_id||
       ' FROM fem_dim_attr_Versions_tl V, fem_dsnp_rule_dims R'||
       ', fem_dim_attributes_b A ,fem_dim_attr_versions_b VB'||
       ' WHERE VB.default_version_flag = ''Y'''||
       ' AND VB.personal_flag = ''N'''||
       ' AND V.version_id = VB.version_id'||
       ' AND VB.attribute_id = A.attribute_id'||
       ' AND A.dimension_id = R.dimension_id'||
       ' AND R.dim_snapshot_obj_def_id = '||p_dim_snapshot_obj_def_id;

Line: 282

   p_module   => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name||'.v_sql_vers_insert_stmt',
   p_msg_text => v_sql_vers_insert_stmt);

Line: 286

   EXECUTE IMMEDIATE v_sql_vers_delete_stmt;

Line: 292

   p_msg_text => 'after version delete');

Line: 294

   EXECUTE IMMEDIATE v_sql_vers_insert_stmt
     USING v_sysdate
          ,v_sysdate;

Line: 302

   p_msg_text => 'after version insert');

Line: 308

   v_sql_delete_stmt :=
      'DELETE FROM '||dim.dsnp_attribute_table_name||
      ' WHERE created_by_object_id = '||v_object_id;

Line: 315

      v_sql_insert_stmt :=
         'INSERT INTO '||dim.dsnp_attribute_table_name||
         ' (created_by_object_id'||
         ' ,attribute_id'||
         ' ,version_id'||
         ' ,'||dim.member_col||
         ' ,value_set_id'||
         ' ,dim_attribute_numeric_member'||
         ' ,dim_attribute_value_set_id'||
         ' ,dim_attribute_varchar_member'||
         ' ,number_assign_value'||
         ' ,varchar_assign_value'||
         ' ,date_assign_value'||
         ' ,created_by'||
         ' ,creation_date'||
         ' ,last_updated_by'||
         ' ,last_update_date'||
         ' ,last_update_login)'||
         ' SELECT '||v_object_id||
         ' ,A.attribute_id'||
         ' ,A.version_id'||
         ' ,A.'||dim.member_col||
         ' ,A.value_set_id'||
         ' ,A.dim_attribute_numeric_member'||
         ' ,A.dim_attribute_value_set_id'||
         ' ,A.dim_attribute_varchar_member'||
         ' ,A.number_assign_value'||
         ' ,A.varchar_assign_value'||
         ' ,A.date_assign_value'||
         ' ,'||c_user_id||
         ' ,:b_v_sysdate'||
         ' ,'||c_user_id||
         ' ,:b_v_sysdate'||
         ' ,'||c_login_id||
         ' FROM '||dim.attribute_table_name||' A'||
         ', fem_dim_attr_versions_b V'||
         ','||dim.member_b_table_name||' B'||
         ' WHERE V.version_id = A.version_id'||
         ' AND V.default_version_flag = ''Y'''||
         ' AND A.'||dim.member_col||' = B.'||dim.member_col||
         ' AND A.value_set_id = B.value_set_id'||
         ' AND B.personal_flag = ''N''';

Line: 358

      v_sql_insert_stmt :=
         'INSERT INTO '||dim.dsnp_attribute_table_name||
         '(created_by_object_id'||
         ' ,attribute_id'||
         ' ,version_id'||
         ' ,'||dim.member_col||
         ' ,dim_attribute_numeric_member'||
         ' ,dim_attribute_value_set_id'||
         ' ,dim_attribute_varchar_member'||
         ' ,number_assign_value'||
         ' ,varchar_assign_value'||
         ' ,date_assign_value'||
         ' ,created_by'||
         ' ,creation_date'||
         ' ,last_updated_by'||
         ' ,last_update_date'||
         ' ,last_update_login)'||
         ' SELECT '||v_object_id||
         ' ,A.attribute_id'||
         ' ,A.version_id'||
         ' ,A.'||dim.member_col||
         ' ,A.dim_attribute_numeric_member'||
         ' ,A.dim_attribute_value_set_id'||
         ' ,A.dim_attribute_varchar_member'||
         ' ,A.number_assign_value'||
         ' ,A.varchar_assign_value'||
         ' ,A.date_assign_value'||
         ' ,'||c_user_id||
         ' ,:b_v_sysdate'||
         ' ,'||c_user_id||
         ' ,:b_v_sysdate'||
         ' ,'||c_login_id||
         ' FROM '||dim.attribute_table_name||' A'||
         ', fem_dim_attr_versions_b V'||
         ','||dim.member_b_table_name||' B'||
         ' WHERE V.version_id = A.version_id'||
         ' AND V.default_version_flag = ''Y'''||
         ' AND A.'||dim.member_col||' = B.'||dim.member_col||
         ' AND B.personal_flag = ''N''';

Line: 401

   p_module   => 'fem.plsql.'||pc_pkg_name||'.'||c_api_name||'.v_sql_insert_stmt',
   p_msg_text => v_sql_insert_stmt);

Line: 406

   EXECUTE IMMEDIATE v_sql_delete_stmt;

Line: 412

   p_msg_text => 'after attribute delete');

Line: 416

   EXECUTE IMMEDIATE v_sql_insert_stmt
      USING v_sysdate
        ,v_sysdate;