|
SELECT DISTINCT
E.SETID
,A.DEPTID
,B.SETID
FROM PS_POSITION_DATA A
,PS_JOBCODE_TBL B
,PS_DEPT_TBL E
WHERE E.SETID=(SELECT Z.SETID FROM PS_SET_CNTRL_REC Z
WHERE Z.SETCNTRLVALUE=A.BUSINESS_UNIT AND Z.RECNAME='DEPT_TBL')
AND E.DEPTID=A.DEPTID
AND B.SETID=(SELECT Z.SETID FROM PS_SET_CNTRL_REC Z
WHERE Z.SETCNTRLVALUE=A.BUSINESS_UNIT AND Z.RECNAME='JOBCODE_TBL')
AND A.JOBCODE = B.JOBCODE
AND B.EFFDT = (SELECT MAX(D.EFFDT)
FROM PS_JOBCODE_TBL D
WHERE D.JOBCODE = B.JOBCODE
AND D.SETID = B.SETID
AND D.EFFDT<=%CURRENTDATEIN)
AND E.EFFDT=(SELECT MAX(F.EFFDT)
FROM PS_DEPT_TBL F
WHERE F.DEPTID = E.DEPTID
AND F.SETID=E.SETID
AND
F.EFFDT<=%CURRENTDATEIN)
GROUP BY E.SETID,A.DEPTID,B.SETID
|
SETID