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
Parameters are Campus and Year. Report should have the following fields.
| Application ID | Applicant Name | Applicant's UH Username | Year | Campus | Office | Current Classification and Rank | Excluded Faculty's Name | Excluded Faculty's UH Username |
Questions/Observations
What fields are needed in this report? See above
Manoa, West Oahu, and Hilo shouldn’t have any results for 2022 and prior because they’ve never used this feature, but results are showing up for those campuses. I believe that’s because when those campuses came on board, the Exclusions tab was available to the applicant but unbeknownst to them, who they excluded on the tab wasn't relevant.
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;