APPS.PA_USER_ATTR_PVT SQL Statements

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

Line: 9

PROCEDURE DELETE_USER_ATTRS_DATA
( p_commit                        IN VARCHAR2 DEFAULT FND_API.G_FALSE
 ,p_validate_only                 IN VARCHAR2 := FND_API.G_TRUE
 ,p_validation_level              IN NUMBER   := FND_API.G_VALID_LEVEL_FULL
 ,p_calling_module                IN VARCHAR2 := 'SELF_SERVICE'
 ,p_debug_mode                    IN VARCHAR2 := 'N'
 ,p_project_id                    IN NUMBER
 ,p_proj_element_id               IN NUMBER DEFAULT NULL
 ,p_old_classification_id         IN NUMBER
 ,p_new_classification_id         IN NUMBER DEFAULT NULL
 ,p_classification_type           IN VARCHAR2
 ,x_return_status                 OUT NOCOPY VARCHAR2
 ,x_msg_count                     OUT NOCOPY NUMBER
 ,x_msg_data                      OUT NOCOPY VARCHAR2
)
IS
  l_return_status                 VARCHAR2(1);

Line: 38

  CURSOR get_deleted_attr_grps1
  IS
  SELECT assocs.attr_group_id, assocs.data_level
  FROM EGO_OBJ_AG_ASSOCS_B assocs,
       FND_OBJECTS obj
  WHERE assocs.classification_code = p_classification_type||':'||to_char(p_old_classification_id)
  AND assocs.object_id = obj.object_id
  AND obj.obj_name = 'PA_PROJECTS'
  MINUS
  SELECT assocs.attr_group_id, assocs.data_level
  FROM EGO_OBJ_AG_ASSOCS_B assocs,
       FND_OBJECTS obj
  WHERE assocs.classification_code = p_classification_type||':'||to_char(p_new_classification_id)
  AND assocs.object_id = obj.object_id
  AND obj.obj_name = 'PA_PROJECTS';

Line: 54

  CURSOR get_deleted_attr_grps2
  IS
  SELECT assocs.attr_group_id, assocs.data_level
  FROM EGO_OBJ_AG_ASSOCS_B assocs,
       FND_OBJECTS obj
  WHERE assocs.classification_code = p_classification_type||':'||to_char(p_old_classification_id)
  AND assocs.object_id = obj.object_id
  AND obj.obj_name = 'PA_PROJECTS';

Line: 65

  SELECT 'Y'
  FROM DUAL
  WHERE c_attr_group_id IN
  (SELECT assocs.attr_group_id
   FROM EGO_OBJ_AG_ASSOCS_B assocs,
        PA_PROJECTS_ALL ppa,
        PA_PROJECT_TYPES ppt,
        FND_OBJECTS obj
   WHERE ppa.project_id = p_project_id
   AND ppa.project_type = ppt.project_type
   AND assocs.classification_code = 'PROJECT_TYPE:'||to_char(ppt.project_type_id)
   AND assocs.data_level = 'PROJECT_LEVEL'
   AND assocs.object_id = obj.object_id
   AND obj.obj_name = 'PA_PROJECTS'
   UNION
   SELECT DISTINCT assocs.attr_group_id
   FROM EGO_OBJ_AG_ASSOCS_B assocs,
        PA_PROJECT_CLASSES ppc,
        PA_CLASS_CATEGORIES pcc,
        FND_OBJECTS obj
   WHERE ppc.project_id = p_project_id
   AND ppc.class_category = pcc.class_category
   AND assocs.classification_code = 'CLASS_CATEGORY:'||to_char(pcc.class_category_id)
   AND assocs.data_level = 'PROJECT_LEVEL'
   AND assocs.object_id = obj.object_id
   AND obj.obj_name = 'PA_PROJECTS'
   UNION
   SELECT assocs.attr_group_id
   FROM EGO_OBJ_AG_ASSOCS_B assocs,
        PA_PROJECT_CLASSES ppc,
        PA_CLASS_CODES pcc,
        FND_OBJECTS obj
   WHERE ppc.project_id = p_project_id
   AND ppc.class_category = pcc.class_category
   AND ppc.class_code = pcc.class_code
   AND assocs.classification_code = 'CLASS_CODE:'||to_char(pcc.class_code_id)
   AND assocs.data_level = 'PROJECT_LEVEL'
   AND assocs.object_id = obj.object_id
   AND obj.obj_name = 'PA_PROJECTS');

Line: 107

  SELECT 'Y'
  FROM DUAL
  WHERE c_attr_group_id IN
  (SELECT assocs.attr_group_id
   FROM EGO_OBJ_AG_ASSOCS_B assocs,
        PA_PROJECTS_ALL ppa,
        PA_PROJECT_TYPES ppt,
        FND_OBJECTS obj
   WHERE ppa.project_id = p_project_id
   AND ppa.project_type = ppt.project_type
   AND assocs.classification_code = 'PROJECT_TYPE:'||to_char(ppt.project_type_id)
   AND assocs.data_level = 'TASK_LEVEL'
   AND assocs.object_id = obj.object_id
   AND obj.obj_name = 'PA_PROJECTS'
   UNION
   SELECT DISTINCT assocs.attr_group_id
   FROM EGO_OBJ_AG_ASSOCS_B assocs,
        PA_PROJECT_CLASSES ppc,
        PA_CLASS_CATEGORIES pcc,
        FND_OBJECTS obj
   WHERE ppc.project_id = p_project_id
   AND ppc.class_category = pcc.class_category
   AND assocs.classification_code = 'CLASS_CATEGORY:'||to_char(pcc.class_category_id)
   AND assocs.data_level = 'TASK_LEVEL'
   AND assocs.object_id = obj.object_id
   AND obj.obj_name = 'PA_PROJECTS'
   UNION
   SELECT assocs.attr_group_id
   FROM EGO_OBJ_AG_ASSOCS_B assocs,
        PA_PROJECT_CLASSES ppc,
        PA_CLASS_CODES pcc,
        FND_OBJECTS obj
   WHERE ppc.project_id = p_project_id
   AND ppc.class_category = pcc.class_category
   AND ppc.class_code = pcc.class_code
   AND assocs.classification_code = 'CLASS_CODE:'||to_char(pcc.class_code_id)
   AND assocs.data_level = 'TASK_LEVEL'
   AND assocs.object_id = obj.object_id
   AND obj.obj_name = 'PA_PROJECTS'
   UNION
   SELECT assocs.attr_group_id
   FROM EGO_OBJ_AG_ASSOCS_B assocs,
        PA_PROJ_ELEMENTS ppe,
        FND_OBJECTS obj
   WHERE ppe.project_id = p_project_id
   AND ppe.proj_element_id = c_proj_element_id
   AND assocs.classification_code = 'TASK_TYPE:'||to_char(ppe.type_id)
   AND assocs.object_id = obj.object_id
   AND obj.obj_name = 'PA_PROJECTS');

Line: 159

  SELECT proj_element_id
  FROM PA_PROJ_ELEMENTS
  WHERE project_id = p_project_id;

Line: 165

    pa_debug.debug('PA_USER_ATTR_PVT.Delete_User_Attrs_Data BEGIN');

Line: 169

    savepoint delete_user_attrs_data_pvt;

Line: 174

      OPEN get_deleted_attr_grps1;

Line: 176

        FETCH get_deleted_attr_grps1 INTO l_attr_group_id, l_data_level;

Line: 177

        EXIT WHEN get_deleted_attr_grps1%NOTFOUND;

Line: 187

              DELETE FROM PA_PROJECTS_ERP_EXT_B
              WHERE PROJECT_ID = p_project_id
              AND PROJ_ELEMENT_ID is NULL
              AND ATTR_GROUP_ID = l_attr_group_id;

Line: 192

              DELETE FROM PA_PROJECTS_ERP_EXT_TL
              WHERE PROJECT_ID = p_project_id
              AND PROJ_ELEMENT_ID is NULL
              AND ATTR_GROUP_ID = l_attr_group_id;

Line: 213

                DELETE FROM PA_PROJECTS_ERP_EXT_B
                WHERE PROJECT_ID = p_project_id
                AND PROJ_ELEMENT_ID = l_proj_element_id
                AND ATTR_GROUP_ID = l_attr_group_id;

Line: 218

                DELETE FROM PA_PROJECTS_ERP_EXT_TL
                WHERE PROJECT_ID = p_project_id
                AND PROJ_ELEMENT_ID = l_proj_element_id
                AND ATTR_GROUP_ID = l_attr_group_id;

Line: 231

      CLOSE get_deleted_attr_grps1;

Line: 233

      OPEN get_deleted_attr_grps2;

Line: 235

        FETCH get_deleted_attr_grps2 INTO l_attr_group_id, l_data_level;

Line: 236

        EXIT WHEN get_deleted_attr_grps2%NOTFOUND;

Line: 245

              DELETE FROM PA_PROJECTS_ERP_EXT_B
              WHERE PROJECT_ID = p_project_id
              AND PROJ_ELEMENT_ID is NULL
              AND ATTR_GROUP_ID = l_attr_group_id;

Line: 250

              DELETE FROM PA_PROJECTS_ERP_EXT_TL
              WHERE PROJECT_ID = p_project_id
              AND PROJ_ELEMENT_ID is NULL
              AND ATTR_GROUP_ID = l_attr_group_id;

Line: 271

                DELETE FROM PA_PROJECTS_ERP_EXT_B
                WHERE PROJECT_ID = p_project_id
                AND PROJ_ELEMENT_ID = l_proj_element_id
                AND ATTR_GROUP_ID = l_attr_group_id;

Line: 276

                DELETE FROM PA_PROJECTS_ERP_EXT_TL
                WHERE PROJECT_ID = p_project_id
                AND PROJ_ELEMENT_ID = l_proj_element_id
                AND ATTR_GROUP_ID = l_attr_group_id;

Line: 289

      CLOSE get_deleted_attr_grps2;

Line: 293

    OPEN get_deleted_attr_grps1;

Line: 295

      FETCH get_deleted_attr_grps1 INTO l_attr_group_id, l_data_level;

Line: 296

      EXIT WHEN get_deleted_attr_grps1%NOTFOUND;

Line: 303

          DELETE FROM PA_PROJECTS_ERP_EXT_B
          WHERE PROJECT_ID = p_project_id
          AND PROJ_ELEMENT_ID = p_proj_element_id
          AND ATTR_GROUP_ID = l_attr_group_id;

Line: 308

          DELETE FROM PA_PROJECTS_ERP_EXT_TL
          WHERE PROJECT_ID = p_project_id
          AND PROJ_ELEMENT_ID = p_proj_element_id
          AND ATTR_GROUP_ID = l_attr_group_id;

Line: 318

    CLOSE get_deleted_attr_grps1;

Line: 328

    pa_debug.debug('PA_USER_ATTR_PVT.Delete_User_Attrs_Data END');

Line: 334

         rollback to delete_user_attrs_data_pvt;

Line: 339

         rollback to delete_user_attrs_data_pvt;

Line: 343

                              p_procedure_name => 'Delete_User_Attrs_Data',
                              p_error_text     => SUBSTRB(SQLERRM,1,240));

Line: 347

         rollback to delete_user_attrs_data_pvt;

Line: 351

                              p_procedure_name => 'Delete_User_Attrs_Data',
                              p_error_text     => SUBSTRB(SQLERRM,1,240));

Line: 354

END DELETE_USER_ATTRS_DATA;

Line: 357

PROCEDURE DELETE_ALL_USER_ATTRS_DATA
( p_api_version                   IN NUMBER   := 1.0
 ,p_init_msg_list                 IN VARCHAR2 := FND_API.G_TRUE
 ,p_commit                        IN VARCHAR2 DEFAULT FND_API.G_FALSE
 ,p_validate_only                 IN VARCHAR2 := FND_API.G_TRUE
 ,p_validation_level              IN NUMBER   := FND_API.G_VALID_LEVEL_FULL
 ,p_calling_module                IN VARCHAR2 := 'SELF_SERVICE'
 ,p_debug_mode                    IN VARCHAR2 := 'N'
 ,p_project_id                    IN NUMBER
 ,p_proj_element_id               IN NUMBER DEFAULT NULL
 ,x_return_status                 OUT NOCOPY VARCHAR2
 ,x_msg_count                     OUT NOCOPY NUMBER
 ,x_msg_data                      OUT NOCOPY VARCHAR2
)
IS
  l_return_status                 VARCHAR2(1);

Line: 381

    pa_debug.debug('PA_USER_ATTR_PVT.Delete_All_User_Attrs_Data BEGIN');

Line: 385

    savepoint delete_all_user_attrs_data_pvt;

Line: 390

      DELETE FROM PA_PROJECTS_ERP_EXT_B
      WHERE PROJECT_ID = p_project_id;

Line: 393

      DELETE FROM PA_PROJECTS_ERP_EXT_TL
      WHERE PROJECT_ID = p_project_id;

Line: 396

      DELETE FROM PA_PROJECTS_ERP_EXT_B
      WHERE PROJECT_ID = p_project_id
      AND PROJ_ELEMENT_ID = p_proj_element_id;

Line: 400

      DELETE FROM PA_PROJECTS_ERP_EXT_TL
      WHERE PROJECT_ID = p_project_id
      AND PROJ_ELEMENT_ID = p_proj_element_id;

Line: 413

    pa_debug.debug('PA_USER_ATTR_PVT.Delete_All_User_Attrs_Data END');

Line: 419

         rollback to delete_all_user_attrs_data_pvt;

Line: 424

         rollback to delete_all_user_attrs_data_pvt;

Line: 428

                              p_procedure_name => 'Delete_All_User_Attrs_Data',
                              p_error_text     => SUBSTRB(SQLERRM,1,240));

Line: 432

         rollback to delete_all_user_attrs_data_pvt;

Line: 436

                              p_procedure_name => 'Delete_All_User_Attrs_Data',
                              p_error_text     => SUBSTRB(SQLERRM,1,240));

Line: 439

END DELETE_ALL_USER_ATTRS_DATA;