|
SELECT
A.EMPLID
, A.DISCIPLINE_DT
,C.DESCR
,E.ACTION_DT
,D.DESCR
FROM PS_DISCIPLIN_ACTN A,
PS_DISCIP_TYPE_TBL C,
PS_DISCIP_STEP_TBL D,
PS_DISCIPLIN_STEP E
WHERE A.EMPLID = E.EMPLID
AND A.DISCIPLINE_DT = E.DISCIPLINE_DT
AND A.DISCIPLINARY_TYPE = E.DISCIPLINARY_TYPE
AND D.DISCIPLINARY_STEP = E.DISCIPLINARY_STEP
AND D.EFFDT = (SELECT MAX(EFFDT) FROM PS_DISCIP_STEP_TBL E1
WHERE E1.DISCIPLINARY_STEP = E.DISCIPLINARY_STEP AND
E1.EFFDT <= A.DISCIPLINE_DT)
AND A.DISCIPLINARY_TYPE = C.DISCIPLINARY_TYPE
AND C.EFFDT = (SELECT MAX(EFFDT) FROM PS_DISCIP_TYPE_TBL C1
WHERE C1.DISCIPLINARY_TYPE = C.DISCIPLINARY_TYPE AND
C1.EFFDT <= A.DISCIPLINE_DT)
|
EMPLID