SELECT brsf_JOB_FAMILY_PATHWAY_LEVEL(app.appointment_code) AS FAM ,DECODE('2', '1', org.team_area ,NULL) AS AREA ,org.body_position AS body_pos ,org.full_name ,peo.surname || ', ' || peo.initials AS NAME ,peo.payroll_number ,DECODE(BRSF_SES_IND(peo.person_code), NULL, BRSF_SES_IND(peo.person_code,TO_DATE('01-AUG-2016', 'DD-MON-YYYY')) ,BRSF_SES_IND(peo.person_code, TO_DATE('01-AUG-2016', 'DD-MON-YYYY')), BRSF_SES_IND(peo.person_code) ,BRSF_SES_IND(peo.person_code, TO_DATE('01-AUG-2016', 'DD-MON-YYYY')) || ' at revision date, ' || BRSF_SES_IND(peo.person_code) || ' now') AS SES ,app.pos_post_number AS post_number ,app.appointment_code ,sev.start_date AS salev_date ,org.team_area ,BRSF_APPT_GRADE(app.appointment_code, sev.start_date) AS grade_name ,BRSF_APPT_HOURS(app.appointment_code, sev.start_date) AS hours_per_week ,BRSF_APPT_SALARY(app.appointment_code, sev.start_date) AS salary ,BRSF_APPT_FTE_SALARY(app.appointment_code, sev.start_date) AS fte_salary ,DECODE(BRSF_APPT_SPINAL_IND(app.appointment_code, sev.start_date) ,'N', 'NON-SPINAL' ,'Y', 'Spinal') AS spinal_ind ,gra.psp_category || gra.psp_group AS staffcat FROM hes_people peo ,hes_periods_of_service POS ,hes_appointments app ,brs_post_org_histories poh ,hes_organisation_units org ,brsv_salary_events sev ,hes_app_grade_histories agh ,hes_grades gra WHERE peo.person_code = pos.per_person_code AND pos.period_of_ser_code = app.pes_period_of_ser_code AND app.pos_post_number = poh.post_number AND app.appointment_code = agh.app_appointment_code AND sev.appointment_code = app.appointment_code AND poh.organisation_code = org.organisation_code AND poh.start_date = (SELECT MAX(poh2.start_date) FROM brs_post_org_histories poh2 WHERE poh2.post_number = poh.post_number AND poh2.start_date <= sev.start_date) AND agh.gra_grade_code = gra.grade_code AND TO_DATE('01-AUG-2016', 'DD-MON-YYYY') BETWEEN pos.start_date AND pos.end_date AND sev.start_date BETWEEN gra.start_date AND gra.end_date AND sev.start_date BETWEEN agh.effective_start_date AND agh.effective_end_date AND sev.start_date BETWEEN app.start_date AND app.end_date AND app.end_date >= TO_DATE('01-AUG-2016', 'DD-MON-YYYY') AND app.pos_post_number != 'Z1' AND ( sev.start_date >= TO_DATE('01-AUG-2016', 'DD-MON-YYYY') OR sev.start_date = (SELECT MAX(sev2.start_date) FROM brsv_salary_events sev2 WHERE sev2.appointment_code = sev.appointment_code AND sev2.start_date < TO_DATE('01-AUG-2016', 'DD-MON-YYYY')) ) AND ( ( 'Y' = 'Y' AND BRSF_APPT_SPINAL_IND(app.appointment_code, sev.start_date) = 'Y' ) OR ( 'N' = 'Y' AND BRSF_APPT_SPINAL_IND(app.appointment_code, sev.start_date) = 'N' ) ) AND ( org.team_area IN ('A') OR 'null' IN ('A') ) ORDER BY 1 ASC ,2 ASC ,3 ASC ,4 ASC ,5 ASC ,6 ASC ,7 ASC ,8 ASC ,9 ASC ,10 DESC;