APPS.PA_COMP_PROFILE_PVT SQL Statements

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

Line: 37

Procedure Update_competence_element
(p_profile_id             IN NUMBER,
p_person_id               IN NUMBER,
p_competence_id           IN NUMBER,
p_competence_element_id   IN NUMBER,
p_object_version_number   IN NUMBER,
p_rating_level_id         IN NUMBER,
p_effective_date_from     IN DATE);

Line: 68

SELECT business_group_id
FROM   per_competences
WHERE competence_id = p_competence_id;

Line: 138

     SELECT pa_competence_profiles_s.nextval
     INTO g_profile_id
     FROM dual;

Line: 172

PROCEDURE Update_competence_element
(p_person_id       IN per_competence_elements.person_id%TYPE := FND_API.G_MISS_NUM,
 p_competence_id   IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
 p_element_id      IN per_competence_elements.competence_element_id%TYPE := FND_API.G_MISS_NUM,
 p_rating_level_id IN per_competence_elements.rating_level_id%TYPE := FND_API.G_MISS_NUM,
 p_effective_date_from  IN DATE,
 p_commit      IN VARCHAR2 := FND_API.G_MISS_CHAR,
 p_validate_only   IN VARCHAR2 := FND_API.G_MISS_CHAR,
 p_init_msg_list   IN VARCHAR2 := FND_API.G_FALSE,
 p_object_version_number IN NUMBER,
 x_object_version_number OUT NOCOPY NUMBER , -- 4537865 Added the nocopy hint
 x_return_status   OUT NOCOPY VARCHAR2 ) IS -- 4537865 Added the nocopy hint

l_effective_date_from   DATE;

Line: 196

     pa_debug.set_err_stack ('PA_COMP_PROFILE_PVT.update_competence_element');

Line: 226

                        ,p_error_message_code => 'PA_PRM_CANNOT_UPDATE');

Line: 231

                        ,p_error_message_code => 'PA_PRM_CANNOT_UPDATE');

Line: 253

        SELECT pa_competence_profiles_s.nextval
        INTO g_profile_id
        FROM dual;

Line: 263

     update_competence_element
     (p_profile_id                   => g_profile_id,
      p_person_id                    => p_person_id,
      p_competence_id                => p_competence_id,
      p_competence_element_id        => p_element_id,
      p_object_version_number        => p_object_version_number,
      p_rating_level_id              => p_rating_level_id,
      p_effective_date_from          => p_effective_date_from);

Line: 286

END Update_Competence_element;

Line: 288

Procedure delete_competence_element
          ( p_person_id             IN NUMBER,
            p_competence_id         IN NUMBER,
            p_element_id            IN NUMBER,
            p_object_version_number IN NUMBER,
            p_commit                IN VARCHAR2 := FND_API.G_MISS_CHAR,
            x_return_status         OUT NOCOPY VARCHAR2)  -- 4537865 Added the nocopy hint
IS
l_competence_alias     VARCHAR2(30);

Line: 306

                        ,p_error_message_code => 'PA_PRM_CANNOT_DELETE');

Line: 311

                        ,p_error_message_code => 'PA_PRM_CANNOT_DELETE');

Line: 315

  hr_competence_element_api.delete_competence_element
         (p_competence_element_id  => p_element_id,
          p_object_version_number  => p_object_version_number);

Line: 324

    		   p_procedure_name => 'delete_competence_element');

Line: 327

END delete_competence_element;

Line: 341

SELECT object_name,
       object_id,
       competence_id,
       effective_date_from
FROM per_competence_elements
WHERE competence_element_id = p_element_id;

Line: 412

SELECT object_version_number
FROM per_competence_elements
WHERE competence_element_id = p_element_id;

Line: 462

       SELECT name,competence_alias
       INTO l_competence_name,l_competence_alias
       FROM per_competences
       WHERE competence_id = p_competence_id;

Line: 475

       SELECT step_value
       INTO l_rating_level_value
       FROM per_rating_levels
       WHERE rating_level_id = p_proficiency_level_id;

Line: 485

  INSERT INTO PA_COMPETENCE_PROFILES
  (PROFILE_ID,
   PERSON_ID,
   OBJECT_VERSION_NUMBER,
   BUSINESS_GROUP_ID,
   COMPETENCE_ID,
   COMPETENCE_ELEMENT_ID,
   EFFECTIVE_DATE_FROM,
   COMPETENCE_ALIAS,
   COMPETENCE_NAME,
   DESCRIPTION,
   RATING_LEVEL_ID,
   RATING_LEVEL_VALUE,
   LAST_UPDATED_BY,
   LAST_UPDATE_DATE,
   CREATED_BY,
   CREATION_DATE,
   OPERATION)
   VALUES
   (p_profile_id,
    p_person_id,
    p_object_version_number,
    p_business_group_id,
    p_competence_id,
    null,
    p_effective_date_from,
    l_competence_alias,
    l_competence_name,
    null,
    p_proficiency_level_id,
    l_rating_level_value,
    null,
    null,
    null,
    null,
    'INSERT');

Line: 528

Procedure Update_competence_element
(p_profile_id             IN NUMBER,
p_person_id               IN NUMBER,
p_competence_id           IN NUMBER,
p_competence_element_id   IN NUMBER,
p_object_version_number   IN NUMBER,
p_rating_level_id         IN NUMBER,
p_effective_date_from     IN DATE)
IS
l_competence_alias        VARCHAR2(30);

Line: 548

       SELECT name,competence_alias,business_group_id
       INTO l_competence_name,l_competence_alias,l_bg_id
       FROM per_competences
       WHERE competence_id = p_competence_id;

Line: 562

       SELECT proficiency_level_id,effective_date_from
       INTO l_old_rating_level_id,l_old_effective_date_from
       FROM per_competence_elements
       WHERE competence_element_id = p_competence_element_id;

Line: 575

       SELECT step_value
       INTO l_old_rating_level_value
       FROM per_rating_levels
       WHERE rating_level_id = l_old_rating_level_id;

Line: 587

       SELECT step_value
       INTO l_rating_level_value
       FROM per_rating_levels
       WHERE rating_level_id = p_rating_level_id;

Line: 597

  INSERT INTO PA_COMPETENCE_PROFILES
  (PROFILE_ID,
   PERSON_ID,
   OBJECT_VERSION_NUMBER,
   BUSINESS_GROUP_ID,
   COMPETENCE_ID,
   COMPETENCE_ELEMENT_ID,
   EFFECTIVE_DATE_FROM,
   RATING_LEVEL_ID,
   RATING_LEVEL_VALUE,
   OLD_RATING_LEVEL_ID,
   OLD_RATING_LEVEL_VALUE,
   OLD_EFFECTIVE_DATE_FROM,
   COMPETENCE_ALIAS,
   COMPETENCE_NAME,
   DESCRIPTION,
   LAST_UPDATED_BY,
   LAST_UPDATE_DATE,
   CREATED_BY,
   CREATION_DATE,
   OPERATION)
   VALUES
   (p_profile_id,
    p_person_id,
    p_object_version_number,
    l_bg_id,
    p_competence_id,
    p_competence_element_id,
    p_effective_date_from,
    p_rating_level_id,
    l_rating_level_value,
    l_old_rating_level_id,
    l_old_rating_level_value,
    l_old_effective_date_from,
    l_competence_alias,
    l_competence_name,
    null,
    null,
    null,
    null,
    null,
    'UPDATE');

Line: 668

  SELECT hremp.supervisor_id
  FROM   hr_employees_current_v hremp
  WHERE  hremp.employee_id = g_person_id;

Line: 789

            DELETE from pa_competence_profiles
            where profile_id=g_profile_id;

Line: 812

            DELETE from pa_competence_profiles
            where profile_id=g_profile_id;

Line: 831

PROCEDURE Update_HR
(p_profile_id    IN  NUMBER,
 x_return_status OUT NOCOPY VARCHAR2, -- 4537865 Added the nocopy hint
 x_msg_count     OUT NOCOPY NUMBER,  -- 4537865 Added the nocopy hint
 x_msg_data      OUT NOCOPY VARCHAR2)  -- 4537865 Added the nocopy hint
IS
l_file_val           VARCHAR2(100);

Line: 840

cursor insert_profile IS
   SELECT PERSON_ID,
          OBJECT_VERSION_NUMBER,
          BUSINESS_GROUP_ID,
          COMPETENCE_ID ,
          EFFECTIVE_DATE_FROM,
          RATING_LEVEL_ID,
          CREATED_BY,
          CREATION_DATE
   FROM PA_COMPETENCE_PROFILES
   WHERE profile_id = p_profile_id
   AND operation = 'INSERT';

Line: 853

cursor update_profile IS
   SELECT OBJECT_VERSION_NUMBER,
          COMPETENCE_ELEMENT_ID,
          EFFECTIVE_DATE_FROM,
          RATING_LEVEL_ID,
          LAST_UPDATED_BY,
          LAST_UPDATE_DATE
   FROM PA_COMPETENCE_PROFILES
   WHERE profile_id = p_profile_id
   AND operation = 'UPDATE';

Line: 864

cursor delete_profile IS
   SELECT COMPETENCE_ELEMENT_ID,
          OBJECT_VERSION_NUMBER
   FROM PA_COMPETENCE_PROFILES
   WHERE profile_id = p_profile_id
   AND operation = 'DELETE';

Line: 879

l_last_updated_by        NUMBER(15);

Line: 880

l_last_update_date       DATE;

Line: 885

   FOR c1 in insert_profile LOOP
       l_element_id := NULL;

Line: 898

            select business_group_id
            into l_bg_id
            from per_people_x
            where person_id=c1.person_id;

Line: 972

   for c2 in update_profile LOOP

--       dbms_output.put_line('In Update');

Line: 981

       hr_competence_element_api.update_competence_element
         (p_competence_element_id  => c2.competence_element_id,
          p_object_version_number  => c2.object_version_number,
          p_proficiency_level_id   => c2.rating_level_id,
          p_effective_date_from    => c2.effective_date_from,
          p_effective_date         => c2.effective_date_from);

Line: 995

   FOR c3 in delete_profile LOOP
        hr_competence_element_api.delete_competence_element
         (p_competence_element_id  => c3.competence_element_id,
          p_object_version_number  => c3.object_version_number );

Line: 1006

     DELETE from pa_competence_profiles
     where profile_id=p_profile_id;

Line: 1028

END Update_HR;

Line: 1039

    SELECT business_group_id
    INTO l_comp_bg_id
    FROM per_competences
    WHERE  competence_id = p_competence_id;

Line: 1088

 SELECT 'x' INTO l_dummy
 FROM pa_competence_profiles
 WHERE person_id = p_person_id
 AND   competence_id = p_competence_id ;

Line: 1118

 SELECT 'x' INTO l_dummy
 FROM pa_competence_profiles
 WHERE competence_element_id = p_element_id;

Line: 1142

 SELECT 'x' INTO l_dummy
 FROM per_competence_elements
 WHERE person_id = p_person_id
 AND   competence_id = p_competence_id ;

Line: 1174

cursor insert_profile
IS
SELECT PERSON_ID,
          OBJECT_VERSION_NUMBER,
          BUSINESS_GROUP_ID,
          COMPETENCE_NAME,
          EFFECTIVE_DATE_FROM,
          RATING_LEVEL_ID,
          RATING_LEVEL_VALUE
FROM PA_COMPETENCE_PROFILES
WHERE profile_id = l_profile_id
AND operation = 'INSERT';

Line: 1187

cursor update_profile
IS
SELECT PERSON_ID,
          OBJECT_VERSION_NUMBER,
          BUSINESS_GROUP_ID,
          COMPETENCE_NAME,
          EFFECTIVE_DATE_FROM,
          RATING_LEVEL_ID,
          RATING_LEVEL_VALUE
FROM PA_COMPETENCE_PROFILES
WHERE profile_id = l_profile_id
AND operation = 'UPDATE';

Line: 1200

cursor delete_profile
IS
SELECT PERSON_ID,
          OBJECT_VERSION_NUMBER,
          BUSINESS_GROUP_ID,
          COMPETENCE_NAME,
          EFFECTIVE_DATE_FROM,
          RATING_LEVEL_ID,
          RATING_LEVEL_VALUE
FROM PA_COMPETENCE_PROFILES
WHERE profile_id = l_profile_id
AND operation = 'DELETE';

Line: 1216

   SELECT item_key
   INTO l_profile_id
   FROM wf_item_activity_statuses
   where notification_id = to_number(document_id);

Line: 1238

  document := document || '
 These changes to competency profile requires your approval. Review the changes and select the action for these changes. 
';

Line: 1244

  FOR c1 in insert_profile LOOP
     l_count := l_count + 1;

Line: 1257

  FOR c2 in update_profile LOOP
     l_count := l_count + 1;

Line: 1260

         document := document ||  ' 
  Updated Competencies :  
';

Line: 1274

  FOR c3 in delete_profile LOOP
     l_count := l_count + 1;

Line: 1277

         document := document ||  ' 
  Deleted Competencies :  
';

Line: 1305

  DELETE FROM pa_competence_profiles
  WHERE profile_id=p_profile_id;