Query to get Vacancy Details in Oracle Apps - iRec

Query to get Vacancy Details in Oracle iRec

SELECT name vacancy_name,
       attribute1 requestition_number,
       hr_general.decode_organization (BUSINESS_GROUP_ID) business_group,
       (SELECT name
          FROM per_all_positions
         WHERE position_id = paa.position_id AND ROWNUM = 1),
       hr_general.DECODE_JOB (job_id),
       hr_general.DECODE_GRADE (grade_id) grade,
       hr_general.decode_organization (ORGANIZATION_ID),
       hr_general.decode_location (LOCATION_ID),
       date_from,
       date_to,
       Status,
       (SELECT ppf.full_name || '(' || ppf.employee_number || ')'
          FROM per_all_people_f ppf, fnd_user fu
         WHERE     TRUNC (SYSDATE) BETWEEN effective_start_date
                                       AND effective_end_date
               AND ppf.person_id = fu.employee_id
               AND fu.user_id = paa.CREATED_BY)
          created_by,
       (SELECT ppf.full_name || '(' || ppf.employee_number || ')'
          FROM per_all_people_f ppf
         WHERE     TRUNC (SYSDATE) BETWEEN effective_start_date
                                       AND effective_end_date
               AND ppf.person_id = MANAGER_ID)
          manager,
       position_id
  FROM PER_ALL_VACANCIES paa

Stay Tuned for more updates.


"Team Work" More We Less Me.