Exclusion Report

Jira Reference: FT-689

Background

This report lives in ReportServer and separate reports per campus. Will be used by the Provost/Chancellor offices to determine TPRC committees.

Requirements

Report should have the following fields

Applicant Name

Applicant's UH Username

Campus

Office

Current Classification and Rank

Excluded Faculty's Name

Excluded Faculty's UH Username

Questions

What fields are needed in this report?

Current Query 6/16/2023

SELECT DISTINCT applicant.name as 'Applicant Name', 
applicant.username as 'Applicant''s UH Username', 
office.description as 'Office',
campus.description as 'Campus',
hrdw_employee.current_classification as 'Current Classification', 
hrdw_employee.current_rank as 'Current Rank', 
excluded.name as 'Excluded Faculty''s Name',
excluded.username as 'Excluded Faculty''s UH Username'
FROM application, office, campus, hrdw_employee,
(SELECT application_role.application_id, person.uhuuid, person.name, person.username FROM person INNER JOIN application_role ON person.id=application_role.person_id WHERE application_role.role_id=1) applicant,
(SELECT application_role.application_id, person.name, person.username FROM person INNER JOIN application_role ON person.id=application_role.person_id WHERE application_role.role_id=12) excluded
WHERE
applicant.application_id=excluded.application_id AND
application.id=applicant.application_id AND
application.office_id=office.id AND
campus.id=office.campus_id AND
applicant.uhuuid=hrdw_employee.uhuuid AND
campus.code='MA'
ORDER BY applicant.application_id;