SELECT Emp.EMP_ID,Emp.EMP_DOB,Emp.EMP_NICEXPIRYDATE,EMP_MAILINGADDRESS,Emp.EMP_CODE,Emp.EMP_FIRSTNAME,Emp.EMP_LASTNAME,
                    (SELECT ISNULL(AdminReport.EMP_FIRSTNAME, '')+'  '+ ISNULL(AdminReport.EMP_LASTNAME, '') FROM SETUP_EMPLOYEE AdminReport WHERE AdminReport.EMP_ID=PLAC.JBT_ID_ADMIN )AS ADMINREPNAME,
                    (SELECT ISNULL(FUNCNAME.EMP_FIRSTNAME, '')+'  '+ ISNULL(FUNCNAME.EMP_LASTNAME, '') FROM SETUP_EMPLOYEE FUNCNAME WHERE FUNCNAME.EMP_ID=PLAC.JBT_ID_FUNC )AS FUNCREPNAME, 
                    (
                              SELECT DSG_TITLE FROM SETUP_DESIGNATION DSG3, SETUP_EMPLOYEEPLACEMENT PLAC3, SETUP_EMPLOYEE EMP3
                              WHERE 
                                           (
                                                  DSG3.DSG_ID = PLAC3.PL_DESIGNATION AND
                                                  EMP3.EMP_ID = PLAC3.EMP_ID AND
                                                  PLAC.JBT_ID_ADMIN = EMP3.EMP_ID AND
                                                  PLAC3.PL_PERIOD =
                                                                                     (
                                                                                           SELECT MAX(PL_PERIOD) 
                                                                                           FROM SETUP_EMPLOYEEPLACEMENT empPLAC3 
                                                                                           WHERE empPLAC3.EMP_ID=PLAC3.EMP_ID
                                                                                     )
                                           )
                       )AdminRepDesig,  --Taimoor 03.06.2015
                       (
                              SELECT DSG_TITLE FROM SETUP_DESIGNATION DSG3, SETUP_EMPLOYEEPLACEMENT PLAC3, SETUP_EMPLOYEE EMP3
                              WHERE 
                                           (
                                                  DSG3.DSG_ID = PLAC3.PL_DESIGNATION AND
                                                  EMP3.EMP_ID = PLAC3.EMP_ID AND
                                                  PLAC.JBT_ID_FUNC = EMP3.EMP_ID AND
                                                  PLAC3.PL_PERIOD =
                                                                                     (
                                                                                           SELECT MAX(PL_PERIOD) 
                                                                                           FROM SETUP_EMPLOYEEPLACEMENT empPLAC3 
                                                                                           WHERE empPLAC3.EMP_ID=PLAC3.EMP_ID
                                                                                     )
                                           )
                       )FuncRepDesig,  --Taimoor 03.06.2015
                    Emp.GEN_ID, Emp.MAT_ID,Emp.EMP_DATELEAVE, 
                    Emp.REL_ID, Emp.CTY_ID, Emp.CON_ID,Emp.ACG_ID,Emp.LVG_ID, Cty.CTY_ID, Cty.CTY_TITLE, 
                    Con.CON_ID,Con.CON_TITLE, Lvg.LVG_ID,Lvg.LVG_TITLE,Acg.ACG_ID, Acg.ACG_TITLE, 
                    Gen.GEN_ID,Gen.GEN_TITLE, Mat.MAT_ID,Mat.MAT_TITLE,Emp.EMP_PEMADDRESS,Emp.EMP_PRTADDRESS,
                    LOC.LOC_TITLE,DPT.DPT_TITLE,DSG.DSG_TITLE,SEC.SEC_TITLE,SHT.SHT_TITLE,EMPG.EMPG_TITLE,EMT.EMT_TITLE,Emp.EMP_NIC,Emp.EMP_JOINDATE,Emp.EMP_DATELEAVE,Emp.EMP_EMAIL,DSG.dSG_title 
                    FROM SETUP_EMPLOYEE Emp 
                    LEFT OUTER JOIN SYS_SETUP_GENDER Gen ON Emp.GEN_ID = Gen.GEN_ID 
                    LEFT OUTER JOIN SYS_SETUP_MARITALSTATUS Mat ON Emp.MAT_ID = Mat.MAT_ID 
                    LEFT OUTER JOIN SETUP_CITY Cty ON Emp.CTY_ID = Cty.CTY_ID 
                    LEFT OUTER JOIN SETUP_COUNTRY Con ON Emp.CON_ID = Con.CON_ID 
                    LEFT OUTER JOIN SETUP_LEAVEGROUP Lvg ON Emp.LVG_ID = Lvg.LVG_ID 
                    LEFT OUTER JOIN SETUP_ACCESSGROUP Acg ON Emp.ACG_ID = Acg.ACG_ID 
                    INNER JOIN SETUP_EMPLOYEEPLACEMENT PLAC ON EMP.EMP_ID=PLAC.EMP_ID
                    LEFT OUTER JOIN SETUP_LOCATION LOC ON PLAC.PL_LOCATION = LOC.LOC_ID 
                    LEFT OUTER JOIN SETUP_DEPARTMENT DPT ON PLAC.PL_DEPARTMENT = DPT.DPT_ID
                    LEFT OUTER JOIN SETUP_DESIGNATION DSG ON PLAC.PL_DESIGNATION = DSG.DSG_ID
                    LEFT OUTER JOIN SETUP_SECTION SEC ON PLAC.PL_DIVISION = SEC.SEC_ID
                    LEFT OUTER JOIN SETUP_EMPLOYEEGRADE EMPG ON PLAC.PL_GRADE = EMPG.EMPG_ID
                    LEFT OUTER JOIN SETUP_EMPLOYEETYPE EMT ON PLAC.PL_EMPLOYEE_TYPE = EMT.EMT_ID
                    LEFT OUTER JOIN SETUP_SHIFT SHT ON PLAC.PL_SHIFT = SHT.SHT_ID
                    WHERE PLAC.PL_PERIOD=(SELECT MAX(PL_PERIOD) FROM SETUP_EMPLOYEEPLACEMENT empPLAC WHERE empPLAC.EMP_ID=plac.EMP_ID) AND
                                (Emp.EMP_DATELEAVE IS NULL OR Emp.EMP_DATELEAVE='' OR Emp.EMP_DATELEAVE = '01/01/1900' )  AND Emp.ACG_ID IN('0c96b775-8c22-4b0a-9abe-2478d8a32067','ef0ee9de-c215-45e7-a1c9-65c09cd3fb6b','2c2e5157-27ce-4357-bdb0-f3562a38d23f')