|
SELECT DEP.SETID , DEP.DEPTID , DEP.EFFDT , EDR.INEFFDT , DEP.EFF_STATUS , DEP.DESCR , DEP.DESCRSHORT , DEP.COMPANY , CO.DESCR CMPY_DESCR , CO.DESCRSHORT CMPY_DESCRSHORT , DEP.SETID_LOCATION , DEP.LOCATION , DEP.TAX_LOCATION_CD , DEP.MANAGER_ID , PD.NAME , PD.NAME_TITLE , PD.FIRST_NAME , PD.MIDDLE_NAME , PD.LAST_NAME , DEP.MANAGER_POSN , DEP.BUDGET_YR_END_DT , DEP.BUDGET_LVL , DEP.GL_EXPENSE , DEP.FTE_EDIT_INDC , DEP.DEPT_TENURE_FLG , DEP.TL_DISTRIB_INFO , DEP.USE_BUDGETS , DEP.USE_ENCUMBRANCES , DEP.USE_DISTRIBUTION , DEP.BUDGET_DEPTID , DEP.DIST_PRORATE_OPTN , DEP.HP_STATS_DEPT_CD , DEP.HP_STATS_FACULTY , DEP.BUDGETARY_ONLY FROM PS_DEPT_TBL DEP INNER JOIN PS_DEPT_EDR EDR ON EDR.SETID =DEP.SETID AND EDR.DEPTID =DEP.DEPTID AND EDR.EFFDT=DEP.EFFDT LEFT OUTER JOIN PS_COMPANY_TBL CO ON CO.COMPANY=DEP.COMPANY LEFT OUTER JOIN PS_PERSONAL_DATA PD ON PD.EMPLID=DEP.MANAGER_ID WHERE (CO.EFFDT = ( SELECT MAX(CO_ED.EFFDT) FROM PS_COMPANY_TBL CO_ED WHERE CO_ED.COMPANY=CO.COMPANY AND CO_ED.EFFDT <= DEP.EFFDT ) OR CO.COMPANY IS NULL) UNION SELECT DISTINCT SETID , ' ' , %DateIn('1900-01-01') , %DateIn('9999-12-31') , 'A' , 'Not Available' , 'NA' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , 0 , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' FROM PS_SETID_TBL UNION SELECT ' ' , ' ' , %DateIn('1900-01-01') , %DateIn('9999-12-31') , 'A' , 'Not Available' , 'NA' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , 0 , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' FROM PS_INSTALLATION
|
SETID