Query to find Employee details,location and user

              Query to find Employee details,location and user

Sharing query to find employee details based on its location.You can get user name,employee id etc.

SELECT DISTINCT PER.FULL_NAME,

                PER.EMAIL_ADDRESS,

                FU.USER_NAME,

                PER.NATIONAL_IDENTIFIER,

                HL.DESCRIPTION,

                PT.SYSTEM_NAME,

                HO.NAME BU_NAME,

                PS.ORGANIZATION_ID OU

  FROM APPS.FND_USER                FU,

       APPS.PER_ALL_PEOPLE_F        PER,

       APPS.HR_LOCATIONS_ALL        HL,

       APPS.PER_PERSON_TYPES_V      PT,

       APPS.PER_ALL_ASSIGNMENTS_F   PS,

       HR.HR_ALL_ORGANIZATION_UNITS HO

 WHERE PS.LOCATION_ID = HL.LOCATION_ID

 AND PER.PERSON_TYPE_ID = PT.PERSON_TYPE_ID

 AND FU.EMPLOYEE_ID = PER.PERSON_ID

 AND PS.PERSON_ID = PER.PERSON_ID

 AND PS.ORGANIZATION_ID = HO.ORGANIZATION_ID

 AND TRUNC(SYSDATE) BETWEEN TRUNC(PER.EFFECTIVE_START_DATE) AND

 (PER.EFFECTIVE_END_DATE)

 AND TRUNC(SYSDATE) BETWEEN TRUNC(PS.EFFECTIVE_START_DATE) AND

 (PS.EFFECTIVE_END_DATE)

 AND (FU.END_DATE >= SYSDATE OR FU.END_DATE IS NULL)

 AND HL.location_id = P_location_id

 ORDER BY PER.FULL_NAME;