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_COMP_PROFILE_PUB SQL Statements

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

Line: 23


PURPOSE   This procedure inserts a competence element for a person
 -------------------------------------------------------------------- */

PROCEDURE Add_competence_element
(
p_person_id	    IN per_competence_elements.person_id%TYPE,
p_competence_id	    IN per_competences.competence_id%TYPE,
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_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_effective_date_from IN DATE := FND_API.G_MISS_DATE,
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,
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: 86


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

Line: 193


PROCEDURE Update_competence_element
PURPOSE   This procedure updates the competence elements for a person
 -------------------------------------------------------------------- */

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_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_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_effective_date_from IN DATE := FND_API.G_MISS_DATE,
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: 235


  pa_debug.init_err_stack ('PA_COMP_PROFILE_PUB.update_competence_element');

Line: 262


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

Line: 293


  PA_COMP_PROFILE_PVT.update_competence_element
          ( p_person_id	      => p_person_id
           ,p_competence_id   => l_competence_id
           ,p_element_id      => p_element_id
           ,p_rating_level_id => l_rating_level_id
           ,p_effective_date_from => l_effective_date_from
           ,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: 368


END update_competence_element ;

Line: 370


PROCEDURE delete_competence_element
(p_person_id      IN per_competence_elements.person_id%TYPE  := FND_API.G_MISS_NUM,
 p_competence_id  IN per_competence_elements.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_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) --File.Sql.39 bug 4440895
IS
l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;

Line: 390


  PA_COMP_PROFILE_PVT.delete_competence_element
          ( p_person_id             => p_person_id,
            p_competence_id         => p_competence_id,
            p_element_id            => p_element_id,
            p_object_version_number => p_object_version_number,
            p_commit	            => p_commit,
            x_return_status         => l_return_status );

Line: 405


       p_procedure_name => 'delete_competence_element');

Line: 434


            If the operation is update, you are not allowed to
            update the competence_id on the record.

            Effective Date From: If returns back the sysdate is the date
            is null or missing. Validates that the effective date is
            in the within limits of the dates the competence itself is
            valid
 -------------------------------------------------------------------- */
PROCEDURE validate_attributes
(
p_element_id       IN NUMBER := null,
p_person_id        IN per_competences.competence_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_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_effective_date_from  IN DATE := FND_API.G_MISS_DATE,
p_operation            IN VARCHAR2,
x_return_status	      OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_competence_id       OUT NOCOPY per_competences.competence_id%TYPE, --File.Sql.39 bug 4440895
x_rating_level_id     OUT NOCOPY per_competence_elements.rating_level_id%TYPE, --File.Sql.39 bug 4440895
x_effective_date_from OUT NOCOPY DATE) --File.Sql.39 bug 4440895
IS
l_return_status         VARCHAR2(30);

Line: 517


   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: 537


   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: 601


   IF p_operation = 'UPDATE' THEN
      IF p_element_id is not null THEN
         BEGIN
            SELECT competence_id
            INTO l_old_competence_id
            FROM per_competence_elements
            WHERE competence_element_id = p_element_id;

Line: 636


     SELECT 'Y'
     INTO l_valid
     FROM per_competences
     WHERE x_effective_date_from BETWEEN date_from AND NVL(date_to,x_effective_date_from)
     AND competence_id=x_competence_id;

Line: 726


PROCEDURE Update_HR(
    itemtype  in varchar2,
    itemkey   in varchar2,
    actid     in number,
    funcmode  in varchar2,
    resultout in out NOCOPY varchar2) --File.Sql.39 bug 4440895
IS
l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;

Line: 744


    PA_COMP_PROFILE_PVT.Update_HR
       (p_profile_id    => to_number(itemkey),
        x_return_status => l_return_status,
        x_msg_count     => l_msg_count,
        x_msg_data      => l_msg_data);

Line: 764


    wf_core.context('PA_COMP_PROFILE_PUB', 'Update_HR',
		    itemtype, itemkey, to_char(actid), funcmode);

Line: 767


END Update_HR;

Line: 808


    wf_core.context('PA_COMP_PROFILE_PUB', 'Update_HR',
		    itemtype, itemkey, to_char(actid), funcmode);

Line: 842


FUNCTION  Get_Select_Flag
PURPOSE   This function is called from the view PA_ALL_COMPETENCES_LOV_V.
          It returns 'Y' for the global person (g_person_id) or
          assignment (g_assignment_id) if a competence
          exists for it. Returns 'N' if it does not.
 -------------------------------------------------------------------- */

Function Get_Select_Flag(p_competence_id IN NUMBER)
RETURN VARCHAR2
IS
l_exists  VARCHAR2(1) := 'N';

Line: 861


    SELECT 'Y'
    INTO l_exists
    FROM per_competence_elements
    WHERE person_id = g_person_id
    AND competence_id = p_competence_id;

Line: 869


    SELECT 'Y'
    INTO l_exists
    FROM per_competence_elements
    WHERE object_id = g_assignment_id
    AND competence_id = p_competence_id
    and OBJECT_NAME = 'OPEN_ASSIGNMENT' ; -- Included for 4765876

Line: 898


	 SELECT employee_id
		 INTO l_employee_id
		 from fnd_user
		 where user_id=to_number(p_user_id);

Line: 905


   	   SELECT resource_id
		 INTO l_resource_id
		 FROM pa_resource_txn_attributes
		 WHERE person_id=l_employee_id;

Line: 915


	 SELECT full_name
		 INTO l_resource_name
		 FROM per_all_people_f
		 WHERE person_id = l_employee_id
                 and  trunc(sysdate) between trunc(effective_start_date)
                                     and trunc(effective_end_date);

Line: 941


	 SELECT employee_id
		 INTO l_employee_id
		 from fnd_user
		 where user_id=to_number(p_user_id);

Line: 947


	 SELECT resource_id
		 INTO l_resource_id
		 FROM pa_resource_txn_attributes
		 WHERE person_id=l_employee_id;

Line: 970


  SELECT BUSINESS_GROUP_ID
  into l_bg_id
  FROM PER_PEOPLE_X
  WHERE person_id=P_Person_id;