APPS.PA_CANDIDATE_UTILS SQL Statements

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

Line: 13

  SELECT no_of_active_candidates
  INTO l_no_of_active_candidates
  FROM pa_project_assignments
  WHERE assignment_id = p_assignment_id;

Line: 39

  SELECT
  assignment_id
  BULK COLLECT INTO l_candidate_req_table
  FROM pa_candidates
  WHERE resource_id = p_resource_id;

Line: 61

  SELECT resource_id
  INTO l_resource_id
  FROM pa_resources_denorm
  WHERE person_id = p_person_id and rownum=1;

Line: 85

  SELECT 'Y'
  into l_exists
  from pa_candidates
  where assignment_id = p_assignment_id
  and resource_id = p_resource_id;

Line: 131

 SELECT candidate_id,
        candidate_ranking,
        record_version_number
 INTO   l_candidate_id,
        l_candidate_ranking,
        l_record_version_number
 FROM   pa_candidates
 WHERE  assignment_id = p_assignment_id
 AND    resource_id = p_resource_id;

Line: 146

 SELECT project_status_code
 INTO   l_project_status_code
 FROM   PA_PROJECT_STATUSES
 WHERE  project_system_status_code = 'CANDIDATE_PENDING_REVIEW'
 AND  PREDEFINED_FLAG = 'Y'  -- Added for bug 5222893
 AND status_type = 'CANDIDATE';     -- Bug 4773033 CANDIDATE ROUTINES JOIN FOR PROJECT STATUSES

Line: 153

 PA_CANDIDATE_PUB.Update_Candidate
   (p_candidate_id               => l_candidate_id,
    p_status_code                => l_project_status_code,
    p_ranking                    => l_candidate_ranking,
    p_change_reason_code         => null,
    p_record_version_number      => l_record_version_number,
    x_record_version_number      => l_new_record_version_number, -- 4537865 : Changed from l_record_version_number to new variable
    x_msg_count                  => l_msg_count,
    x_msg_data                   => l_msg_data,
    x_return_status              => l_return_status);

Line: 228

SELECT min_resource_job_level, max_resource_job_level, competence_match_weighting, availability_match_weighting, job_level_match_weighting
FROM   pa_project_assignments
WHERE  assignment_id = p_assignment_id;

Line: 368

     SELECT message_text
     INTO   l_nominator_name
     FROM   fnd_new_messages
     WHERE  message_name = 'PA_AUTOMATED_SEARCH_PROCESS';

Line: 374

     SELECT full_name
     INTO   l_nominator_name
     FROM   per_people_x
     WHERE  person_id = p_nominated_by_person_id;

Line: 397

  SELECT count(*)
  INTO   l_num_nomination
  FROM   pa_candidates can,
         pa_project_assignments asmt,
         pa_project_statuses ps
  WHERE  can.assignment_id = asmt.assignment_id
  AND    can.status_code = ps.project_status_code
  AND    can.resource_id = p_resource_id
  AND    asmt.assignment_type = 'OPEN_ASSIGNMENT'
  AND    ps.status_type = 'CANDIDATE'
  AND    ps.project_system_status_code IN ('CANDIDATE_PENDING_REVIEW',
                                           'CANDIDATE_UNDER_REVIEW',
                                           'CANDIDATE_SUITABLE',
                                           'CANDIDATE_SYSTEM_NOMINATED');

Line: 429

  SELECT count(*)
  INTO   l_num_qualifieds
  FROM   pa_candidates can,
         pa_project_assignments asmt,
         pa_project_statuses ps
  WHERE  can.assignment_id = asmt.assignment_id
  AND    can.status_code = ps.project_status_code
  AND    asmt.assignment_type = 'OPEN_ASSIGNMENT'
  AND    can.resource_id = p_resource_id
  AND    ps.status_type = 'CANDIDATE'
  AND    ps.project_system_status_code = 'CANDIDATE_SYSTEM_QUALIFIED';

Line: 448

FUNCTION: Update_No_Of_Active_Candidates
PURPOSE:  This API updates the no_of_active_candidates column in the
          pa_project_assignments table. It will be called when the user
          changes the duration for the requirements.
-------------------------------------------------------------------- */
PROCEDURE Update_No_Of_Active_Candidates (p_assignment_id IN NUMBER,
                                          x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_no_of_active_candidates    NUMBER := 0;

Line: 461

  PA_DEBUG.init_err_stack('PA_CANDIDATE_UTIL.Update_No_Of_Active_Candidates');

Line: 465

  SELECT count(*)
  INTO   l_no_of_active_candidates
  FROM   pa_candidates cand,
         pa_project_assignments asmt,
         pa_resources_denorm res,
         pa_project_statuses ps
  WHERE  cand.assignment_id = p_assignment_id
  AND    cand.assignment_id = asmt.assignment_id
  AND    cand.resource_id = res.resource_id
  AND    asmt.start_date BETWEEN res.resource_effective_start_date AND
                         NVL(res.resource_effective_end_date, asmt.start_date+1)
  AND    cand.status_code = ps.project_status_code
  AND    ps.status_type = 'CANDIDATE'
  AND    ps.project_system_status_code in ('CANDIDATE_PENDING_REVIEW',
                                           'CANDIDATE_UNDER_REVIEW',
                                           'CANDIDATE_SYSTEM_NOMINATED',
                                           'CANDIDATE_SUITABLE')
  AND    res.schedulable_flag = 'Y';

Line: 484

  SELECT record_version_number
  INTO   l_record_version_number
  FROM   pa_project_assignments
  WHERE  assignment_id = p_assignment_id;

Line: 489

  pa_project_assignments_pkg.Update_row(
                             p_assignment_id           => p_assignment_id,
                             p_no_of_active_candidates => l_no_of_active_candidates,
                             p_record_version_number   => l_record_version_number,
                             x_return_status           => l_return_status );

Line: 505

                 p_procedure_name => 'Update_No_Of_Active_Candidates' );

Line: 508

END Update_No_Of_Active_Candidates;