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

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

Line: 64


		 p_operation        => 'INSERT',
		 x_return_status    => l_return_status,
		 x_competence_id    => l_competence_id,
		 x_rating_level_id  => l_rating_level_id );

Line: 182


PROCEDURE Update_competence_element
(p_object_name     IN per_competence_elements.object_name%TYPE := FND_API.G_MISS_CHAR,
 p_object_id	   IN per_competence_elements.object_id%TYPE := FND_API.G_MISS_NUM,
 p_competence_id   IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
p_competence_alias IN per_competences.competence_alias%TYPE  := FND_API.G_MISS_CHAR,
p_competence_name  IN per_competences.name%TYPE  := FND_API.G_MISS_CHAR,
 p_element_rowid   IN ROWID := FND_API.G_MISS_CHAR,
 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_rating_level_value IN per_rating_levels.step_value%TYPE := FND_API.G_MISS_NUM,
 p_mandatory_flag  IN per_competence_elements.mandatory%TYPE := FND_API.G_MISS_CHAR,
 p_init_msg_list   IN VARCHAR2 := FND_API.G_MISS_CHAR,
 p_commit	   IN VARCHAR2 := FND_API.G_MISS_CHAR,
 p_validate_only   IN VARCHAR2 := FND_API.G_MISS_CHAR,
 p_object_version_number IN NUMBER,
 x_object_version_number OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
 x_return_status   OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
 x_msg_count	   OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
 x_msg_data	   OUT NOCOPY VARCHAR2)  IS --File.Sql.39 bug 4440895

l_data	       VARCHAR2(500);

Line: 213


     pa_debug.init_err_stack ('pa_competence_pub.update_competence_element');

Line: 238


            p_operation        => 'UPDATE',
            x_return_status    => l_return_status,
            x_competence_id    => l_competence_id,
            x_rating_level_id  => l_rating_level_id );

Line: 262


      pa_competence_pvt.update_competence_element
          ( p_object_name     => p_object_name
           ,p_object_id	      => p_object_id
           ,p_competence_id   => l_competence_id
           ,p_element_rowid   => p_element_rowid
           ,p_element_id      => p_element_id
           ,p_rating_level_id => l_rating_level_id
           ,p_mandatory_flag  => p_mandatory_flag
           ,p_commit	      => p_commit
           ,p_validate_only   => p_validate_only
           ,p_object_version_number => p_object_version_number
           ,x_object_version_number => x_object_version_number
           ,x_return_status    => l_return_status );

Line: 337


END update_competence_element ;

Line: 339


PROCEDURE delete_competence_element
(p_object_name     IN per_competence_elements.object_name%TYPE := FND_API.G_MISS_CHAR,
 p_object_id	   IN per_competence_elements.object_id%TYPE := FND_API.G_MISS_NUM,
 p_competence_id   IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
 p_competence_alias IN per_competences.competence_alias%TYPE := FND_API.G_MISS_CHAR,
 p_competence_name IN per_competences.name%TYPE := chr(0),
 p_element_rowid   IN ROWID := FND_API.G_MISS_CHAR,
 p_element_id	   IN per_competence_elements.competence_element_id%TYPE := FND_API.G_MISS_NUM,
 p_init_msg_list   IN VARCHAR2 := FND_API.G_MISS_CHAR,
 p_commit	   IN VARCHAR2 := FND_API.G_MISS_CHAR,
 p_validate_only   IN VARCHAR2 := FND_API.G_MISS_CHAR,
 p_object_version_number IN NUMBER,
 x_return_status  OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
 x_msg_count	  OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
 x_msg_data	  OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895

l_data	       VARCHAR2(500);

Line: 363


     pa_debug.init_err_stack ('pa_competence_pub.delete_competence_element');

Line: 379


            p_operation        => 'DELETE',
            x_return_status    => l_return_status,
            x_competence_id    => l_competence_id,
            x_rating_level_id  => l_rating_level_id );

Line: 403


          pa_competence_pvt.delete_competence_element
            (p_object_name     => p_object_name,
             p_object_id       => p_object_id,
             p_competence_id   => l_competence_id,
             p_element_rowid   => p_element_rowid,
             p_element_id      => p_element_id,
             p_commit	       => p_commit,
             p_validate_only   => p_validate_only,
             p_object_version_number => p_object_version_number,
             x_return_status   => l_return_status );

Line: 468


END delete_competence_element;

Line: 538


  IF p_operation = 'INSERT' THEN
     IF l_in_competence_name is not null AND
        l_in_competence_alias is not null THEN
        BEGIN
          SELECT 'Y'
          INTO l_exists
          FROM per_competences
          WHERE name = l_in_competence_name
          AND competence_alias = l_in_competence_alias;

Line: 558


  IF p_operation = 'UPDATE' THEN
      IF p_element_id is not null THEN
         SELECT comp_ele.competence_id,
                comp.name,
                comp.competence_alias
         INTO l_old_competence_id,
              l_old_comp_name,
              l_old_comp_alias
         FROM per_competence_elements comp_ele,
              per_competences comp
         WHERE COMPETENCE_ELEMENT_ID = p_element_id
         AND comp.competence_id = comp_ele.competence_id;

Line: 665


( p_asgn_update_mode            IN  VARCHAR2 := FND_API.G_MISS_CHAR
 ,p_project_id                  IN  pa_project_assignments.project_id%TYPE
 ,p_assignment_id_tbl           IN  SYSTEM.pa_num_tbl_type
 ,p_competence_id_tbl           IN  SYSTEM.pa_num_tbl_type
 ,p_competence_name_tbl         IN  SYSTEM.pa_varchar2_240_tbl_type
 ,p_competence_alias_tbl        IN  SYSTEM.pa_varchar2_30_tbl_type
 ,p_rating_level_id_tbl         IN  SYSTEM.pa_num_tbl_type
 ,p_rating_level_value_tbl      IN  SYSTEM.pa_num_tbl_type
 ,p_mandatory_flag_tbl          IN  SYSTEM.pa_varchar2_1_tbl_type
 ,p_init_msg_list               IN  VARCHAR2  := FND_API.G_FALSE
 ,p_commit                      IN  VARCHAR2  := FND_API.G_FALSE
 ,p_validate_only               IN  VARCHAR2  := FND_API.G_TRUE
 ,p_max_msg_count               IN  NUMBER    := FND_API.G_MISS_NUM
 ,x_return_status               OUT   NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
 ,x_msg_count                   OUT   NOCOPY NUMBER --File.Sql.39 bug 4440895
 ,x_msg_data                    OUT   NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)

IS

l_wf_mode              VARCHAR2(200);

Line: 735


                 p_operation           => 'UPDATE',
                 x_return_status       => l_return_status,
                 x_competence_id       => l_competence_id,
                 x_rating_level_id     => l_rating_level_id_tbl(I));

Line: 754


    l_wf_mode := PA_MASS_ASGMT_TRX.G_MASS_UPDATE_COMPETENCIES;

Line: 805


           then the Update API is called to update the HR Competence
           Element record. If the competence does not exist, the
           INSERT API is called to create the new competence element.
           If even one competence processing errors out for a given
           assignment, then all the competence changes are rolled back
           for that assignment.
 -------------------------------------------------------------------- */

PROCEDURE Mass_Process_Competences
  ( p_project_id                 IN  pa_project_assignments.project_id%TYPE,
  p_assignment_tbl               IN  SYSTEM.pa_num_tbl_type,
  p_competence_id_tbl            IN  SYSTEM.pa_num_tbl_type,
  p_competence_name_tbl          IN  SYSTEM.pa_varchar2_240_tbl_type,
  p_competence_alias_tbl         IN  SYSTEM.pa_varchar2_30_tbl_type,
  p_rating_level_id_tbl          IN  SYSTEM.pa_num_tbl_type,
  p_mandatory_flag_tbl           IN  SYSTEM.pa_varchar2_1_tbl_type,
  p_init_msg_list                IN  VARCHAR2 := FND_API.G_TRUE,
  p_validate_only                IN  VARCHAR2 := FND_API.G_TRUE,
  p_commit                       IN  VARCHAR2 := FND_API.G_FALSE,
  x_success_assignment_id_tbl    OUT NOCOPY SYSTEM.pa_num_tbl_type,  /* Added NOCOPY for bug#2674619 */
  x_return_status                OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
  x_msg_count                    OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
  x_msg_data             OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_element_rowid            ROWID;

Line: 860


                    p_source_type2       =>  PA_MASS_ASGMT_TRX.G_MASS_UPDATE_COMPETENCIES,
                    p_source_identifier1 =>  PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_TYPE,
                    p_source_identifier2 =>  PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_KEY,
                    p_context1           =>  p_project_id,
                    p_context2           =>  p_assignment_tbl(I-1),
                    p_context3           =>  NULL,
                    p_commit             =>  FND_API.G_TRUE,
                    x_return_status      =>  l_return_status);

Line: 888


                    SELECT object_version_number,
                           competence_element_id
                    INTO l_object_version_number,
                         l_competence_element_id
                    FROM per_competence_elements
                    WHERE object_id     = p_assignment_tbl(I)
                    AND   object_name   = 'OPEN_ASSIGNMENT'
                    AND   competence_id = p_competence_id_tbl(J);

Line: 896


                    l_mode := 'UPDATE';

Line: 899


                         l_mode := 'INSERT';

Line: 901


             IF (l_mode = 'UPDATE')
             THEN

                  Update_competence_element
                     (p_element_id            => l_competence_element_id,
                      p_object_name           => 'OPEN_ASSIGNMENT',
                      p_object_id             => p_assignment_tbl(I),
                      p_competence_id         => p_competence_id_tbl(J),
                      p_competence_alias      => p_competence_alias_tbl(J),
                      p_rating_level_id       => p_rating_level_id_tbl(J),
                      p_mandatory_flag        => p_mandatory_flag_tbl(J),
                      p_object_version_number => l_object_version_number,
           --         x_object_version_number => l_object_version_number,       * commented for bug: 4537865
		      x_object_version_number => l_new_object_version_number,   -- added for bug: 4537865
                      x_return_status         => l_return_status,
                      x_msg_count             => l_msg_count,
                      x_msg_data              => l_msg_data);

Line: 958


                p_source_type2       =>  PA_MASS_ASGMT_TRX.G_MASS_UPDATE_COMPETENCIES,
                p_source_identifier1 =>  PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_TYPE,
                p_source_identifier2 =>  PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_KEY,
                p_context1           =>  p_project_id,
                p_context2           =>  p_assignment_tbl(p_assignment_tbl.count),
                p_context3           =>  NULL,
                p_commit             =>  FND_API.G_TRUE,
                x_return_status      =>  l_return_status);