|
SELECT DISTINCT B.OPRID ,A.EMPLID , R.ROLEUSER , B.EMAILID ,D.DEPTID , C.ROLEUSER_ALT , C.EFFDT_FROM , C.EFFDT_TO , C.ROLEUSER_SUPR FROM PSROLEUSER R , PS_PERSONAL_DATA A , PSOPRDEFN B , PSROLEXLATOPRVW C , PS_JOB D WHERE R.ROLENAME IN ( SELECT C1.ROLENAME FROM PS_PV_ROLE_ACTION C1 WHERE C1.PV_ACTION = 'SP_INVOICE_APPROVER') AND R.ROLEUSER = B.OPRID AND B.ACCTLOCK = 0 AND A.EMPLID = B.EMPLID AND B.OPRID = C.OPRID AND B.EMPLID = D.EMPLID AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_JOB D1 WHERE D1.EMPLID = D.EMPLID AND D1.EFFDT <= %CurrentDateIn) AND D.EFFSEQ = ( SELECT MAX(D2.EFFSEQ) FROM PS_JOB D2 WHERE D2.EMPLID = D.EMPLID AND D2.EFFDT = D.EFFDT) AND D.EMPL_STATUS = 'A' UNION SELECT DISTINCT B1.OPRID , ' ' , R1.ROLEUSER , B1.EMAILID , ' ' , C2.ROLEUSER_ALT , C2.EFFDT_FROM , C2.EFFDT_TO , C2.ROLEUSER_SUPR FROM PSROLEUSER R1 , PSOPRDEFN B1 , PSROLEXLATOPRVW C2 WHERE R1.ROLENAME IN ( SELECT C3.ROLENAME FROM PS_PV_ROLE_ACTION C3 WHERE C3.PV_ACTION = 'SP_SPLR_INV_APPROVER' AND C3.PV_ACTION != 'SP_INVOICE_APPROVER') AND R1.ROLEUSER = B1.OPRID AND B1.ACCTLOCK = 0 AND B1.OPRID = C2.OPRID
|