Prompt page to enter the Manager ID to list the reports to:
Query SQL:
SELECT A.EMPLID, C.NAME_PSFORMAT, A.POSITION_NBR, A.FULL_PART_TIME, TO_CHAR(SYSDATE,'YYYY-MM-DD')
FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1, PS_PERSON_NAME C
WHERE A.EMPLID = A1.EMPLID
AND A.EMPL_RCD = A1.EMPL_RCD
AND A1.OPRID = '23420211'
AND A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD
AND A.EFFDT = A_ES.EFFDT)
AND A.HR_STATUS = 'A'
AND A.REPORTS_TO = (SELECT B.POSITION_NBR
FROM PS_JOB B, PS_EMPLMT_SRCH_QRY B1
WHERE B.EMPLID = B1.EMPLID
AND B.EMPL_RCD = B1.EMPL_RCD
AND B1.OPRID = '23420211'
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.EMPL_RCD = B_ED.EMPL_RCD
AND B_ED.EFFDT <= SYSDATE)
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND B.EMPL_RCD = B_ES.EMPL_RCD
AND B.EFFDT = B_ES.EFFDT)
AND B.HR_STATUS = 'A'
AND B.EMPLID = :1)
AND A.EMPLID = C.EMPLID
ORDER BY 1