Hi
We want to match the employees who has no supervisors in Oracle HRMS db and return the corresponding values for the below, currently the users whose supervisor is null isnot coming , we tried NVL in the where condition for supervisor ID but still it is not returning the values.Please let me know what could be done?
SELECT DISTINCT
a1.employee_number,
a1.full_name,
a1.person_id AS person_id,
a1.first_name,
a1.last_name,
a1.middle_names,
TO_CHAR(a1.effective_start_date, 'YYYYMMDD') AS start_date,
TO_CHAR(a1.effective_end_date, 'YYYYMMDD') AS end_date,
TO_CHAR(a2.effective_start_date, 'YYYYMMDD') AS assignment_start_date,
TO_CHAR(a2.effective_end_date, 'YYYYMMDD') AS assignment_end_date,
a1.sex AS gender,
a1.email_address AS email_address,
a1.marital_status,
TO_CHAR(a1.date_of_birth, 'YYYYMMDD') AS date_of_birth,
a2.supervisor_id,
a3.full_name AS supervisor,
a4.name AS position,
a5.name AS organization,
a6.user_person_type AS person_type,
a7.displayed_name AS business_group,
a8.name AS job,
a9.user_status AS assignment_status,
a1.created_by AS created_by
FROM
per_all_people_f a1,
per_all_assignments_f a2,
per_all_people_f a3,
hr_all_positions_f a4,
hr_all_organization_units a5,
per_person_types a6,
per_job_groups a7,
per_jobs a8,
per_assignment_status_types a9,
per_person_type_usages_f a10
WHERE
a2.assignment_number IS NOT NULL
AND a1.effective_start_date <= SYSDATE
AND a1.effective_end_date >= SYSDATE
AND a2.effective_start_date <= SYSDATE
AND a2.effective_end_date >= SYSDATE
AND a3.effective_start_date <= SYSDATE
AND a3.effective_end_date >= SYSDATE
AND a2.supervisor_id = a3.person_id
AND a1.person_id = a2.person_id
AND a1.person_id = a10.person_id
AND a6.person_type_id = a10.person_type_id
AND a6.default_flag = 'Y'
AND a10.effective_start_date <= SYSDATE
AND a10.effective_end_date >= SYSDATE
AND a2.position_id = a4.position_id
AND a2.organization_id = a5.organization_id
AND a2.business_group_id = a7.business_group_id
AND a2.job_id = a8.job_id
AND a2.assignment_status_type_id = a9.assignment_status_type_id
AND a6.system_person_type IN (
'EMP',
'CWK'
)
ORDER BY
end_date DESC,
assignment_start_date DESC,
business_group DESC