|
SELECT A.EMPLID , A.SRVC_IND_DTTM , S.LANGUAGE_CD , I.DESCR , S.DESCR , D.DESCR , R.DESCR , X.DESCRLONG FROM PS_SRVC_IN_RSN_LNG X , PS_INSTITUTION_LNG I , PS_INSTITUTION_TBL B , PS_SRVC_IND_CD_LNG S , PS_SRVC_IND_CD_TBL C , PS_DEPT_TBL E , PS_DEPT_TBL_LANG R , PS_SRVC_IND_DATA A LEFT OUTER JOIN PS_EMAIL_ADDRESSES F ON A.CONTACT_ID = F.EMPLID AND F.PREF_EMAIL_FLAG = 'Y' LEFT OUTER JOIN PS_TERM_VAL_LANG D ON A.SRVC_IND_ACT_TERM = D.STRM WHERE A.INSTITUTION = B.INSTITUTION AND B.EFF_STATUS = 'A' AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_INSTITUTION_TBL B1 WHERE B1.INSTITUTION = A.INSTITUTION AND B1.EFFDT <= %CurrentDateIn) AND A.SRVC_IND_CD = C.SRVC_IND_CD AND A.INSTITUTION = C.INSTITUTION AND C.EFF_STATUS = 'A' AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_SRVC_IND_CD_TBL C1 WHERE C1.INSTITUTION = A.INSTITUTION AND C1.SRVC_IND_CD = A.SRVC_IND_CD AND C1.EFFDT <= %CurrentDateIn) AND A.DEPTID = E.DEPTID AND E.SETID = ( SELECT E2.SETID FROM PS_SET_CNTRL_GROUP E2 WHERE E2.SETCNTRLVALUE = A.INSTITUTION AND E2.REC_GROUP_ID = ( SELECT E3.REC_GROUP_ID FROM PS_REC_GROUP_REC E3 WHERE E3.RECNAME = 'DEPT_TBL')) AND E.EFF_STATUS = 'A' AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_DEPT_TBL_VW E1 WHERE E1.DEPTID = A.DEPTID AND E1.SETID = E.SETID AND E1.EFFDT <= %CurrentDateIn) AND X.INSTITUTION = C.INSTITUTION AND X.SRVC_IND_CD = C.SRVC_IND_CD AND X.EFFDT = C.EFFDT AND X.SRVC_IND_REASON = A.SRVC_IND_REASON AND I.INSTITUTION = B.INSTITUTION AND I.EFFDT = B.EFFDT AND C.INSTITUTION = S.INSTITUTION AND C.SRVC_IND_CD = S.SRVC_IND_CD AND C.EFFDT = S.EFFDT AND E.SETID = R.SETID AND E.DEPTID = R.DEPTID AND E.EFFDT = R.EFFDT
|
EMPLID