HRMS Employee Detail Queries in oracle apps

select * from fnd_user where USER_NAME like 'IN047%'

select * from fnd_user where email_address like 'ela%'

select * from per_all_people_f

select * from PER_ADDRESSES


Active Employee in FND USER:
================================
select * from fnd_user where end_date is null;

select * from fnd_user where sysdate between start_date and nvl(end_date, sysdate)

Inactive Employee in FND USER:
=================================
select * from fnd_user where end_date is not null;

select * from fnd_user where nvl(end_date, sysdate) <= sysdate

Active Employee in HRMS per_all_people_f:
=======================================
select * from per_all_people_f where sysdate between effective_start_date and effective_end_date

Inactive Employee in HRMS per_all_people_f:
=========================================
select * from per_all_people_f where effective_end_date <= sysdate

Active Employee in FND USER and  Active Employee in HRMS PER_ALL_PEOPLE_F:
==================================================================

  SELECT   user_id,
           user_name,
           full_name,
           fu.end_date,
           effective_start_date,
           effective_end_date,
           last_name,
           full_name
    FROM   fnd_user fu, per_all_people_f papf
   WHERE   papf.person_id = fu.employee_id
           AND SYSDATE BETWEEN fu.start_date AND NVL (fu.end_date, SYSDATE)
           AND SYSDATE BETWEEN papf.effective_start_date
                           AND  NVL (papf.effective_end_date, SYSDATE)
ORDER BY   2;


Active Employee in FND USER and Inactive Employee in HRMS PER_ALL_PEOPLE_F:
==================================================================

SELECT   user_id,
         user_name,
         full_name,
         fu.start_Date,
         fu.end_date,
         papf.effective_start_date,
         papf.effective_end_date
  FROM   fnd_user fu, per_all_people_f papf
 WHERE       papf.person_id = fu.employee_id
         AND effective_end_date <= SYSDATE
    --     AND end_date >= effective_end_date
         AND SYSDATE BETWEEN fu.start_date AND NVL (fu.end_date, SYSDATE)
        


 To get Active Employees and Terminated Employees in HRMS;
=======================================================

SELECT   PAAF.ASSIGNMENT_ID,
         PAPF.PERSON_ID AS PERSON_ID2,
         PAPF.FIRST_NAME AS FIRST_NAME,
         PAPF.LAST_NAME AS LAST_NAME,
         PAPF.EMAIL_ADDRESS AS EMAIL_ADDRESS,
         TO_CHAR (PPS.ACTUAL_TERMINATION_DATE) AS ACTUAL_TERMINATION_DATE,
         TO_CHAR (PAPF.EFFECTIVE_START_DATE) AS EFFECTIVE_START_DATE,
         PAPF.EMPLOYEE_NUMBER AS EMPLOYEE_NUMBER,
         TO_CHAR (PAPF.EFFECTIVE_END_DATE) AS EFFECTIVE_END_DATE,
         PAPF.BUSINESS_GROUP_ID AS BUSINESS_GROUP_ID,
         PAAF.SUPERVISOR_ID AS SUPERVISOR_ID,
         PAPF.LAST_UPDATE_DATE papf_update_date,
         PAAF.LAST_UPDATE_DATE paaf_update_date,
         ppt.user_person_type
  FROM   PER_ALL_PEOPLE_F PAPF,
         PER_ALL_ASSIGNMENTS_F PAAF,
         PER_PERIODS_OF_SERVICE PPS,
         hr.per_person_type_usages_f pptu,
         hr.per_person_types ppt
 WHERE   PAAF.PERSON_ID = PAPF.PERSON_ID
         AND PAAF.PRIMARY_FLAG = 'Y'
         AND paaf.assignment_type != 'B'
         AND pptu.effective_start_date BETWEEN papf.effective_start_date
                                           AND  papf.effective_end_date
         AND papf.person_id = pptu.person_id
         AND papf.person_type_id = pptu.person_type_id
         AND pptu.person_type_id = ppt.person_type_id
         AND papf.person_type_id = ppt.person_type_id
         AND papf.business_group_id = ppt.business_group_id
         AND PAAF.period_of_service_id = PPS.period_of_service_id
         AND papf.person_id = pps.person_id
         AND ( (ppt.user_person_type LIKE 'Ex-employee%'
                AND PPS.ACTUAL_TERMINATION_DATE BETWEEN paaf.effective_start_date
                                                    AND  PAAF.EFFECTIVE_END_DATE)
              OR (ppt.user_person_type LIKE 'Employee%'
                  AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                          AND  PAAF.EFFECTIVE_END_DATE))
         AND ( (ppt.user_person_type LIKE 'Ex-employee%'
                AND PPS.ACTUAL_TERMINATION_DATE BETWEEN papf.effective_start_date
                                                    AND  PAPF.EFFECTIVE_END_DATE)
              OR (ppt.user_person_type LIKE 'Employee%'
                  AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                          AND  PAPF.EFFECTIVE_END_DATE))
                                       
                 
 HRMS Employee details Query:
============================
                 
  SELECT   PAPF.EMPLOYEE_NUMBER,
           PAPF.TITLE || ' ' || PAPF.FULL_NAME Emp_name,
           PAPF.SEX,
           MARITAL_STATUS,
           PPT.USER_PERSON_TYPE,
           PAPF.NATIONALITY,
           HOU.ORGANIZATION_ID ORG_ID,
           HOU.NAME ORG,
           PJT.NAME JOB,
           PGT.NAME GRADE,
           PA.ADDRESS_LINE1,
           PA.ADDRESS_LINE2,
           PA.ADDRESS_LINE3,
           PA.TOWN_OR_CITY,
           PA.POSTAL_CODE,
           PA.COUNTRY,
           PPG.GROUP_NAME,
           PPB.NAME PAY,
           PPB.PAY_BASIS
    FROM   PER_ALL_ASSIGNMENTS_F PAAF,
           HR_OPERATING_UNITS HOU,
           PER_JOBS_TL PJT,
           PER_GRADES_TL PGT,
           PER_ADDRESSES PA,
           PAY_PEOPLE_GROUPS PPG,
           PER_PAY_BASES PPB,
           PER_ALL_PEOPLE_F PAPF,
           PER_PERSON_TYPES PPT
   WHERE       PAAF.ORGANIZATION_ID = HOU.ORGANIZATION_ID
           AND PAAF.JOB_ID = PJT.JOB_ID
           AND PAAF.GRADE_ID = PGT.GRADE_ID
           AND PAAF.PEOPLE_GROUP_ID = PPG.PEOPLE_GROUP_ID
           AND PAAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
           AND PAAF.PERSON_ID = PAPF.PERSON_ID
           AND PAPF.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
           AND PA.PERSON_ID = PAPF.PERSON_ID
           AND TO_DATE (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
                                     AND  PAPF.EFFECTIVE_END_DATE
           AND TO_DATE (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE
                                     AND  PAAF.EFFECTIVE_END_DATE
           AND PPT.USER_PERSON_TYPE = 'Employee'
ORDER BY   PAPF.EMPLOYEE_NUMBER


 Get the Hierarchical Information
===============================
SELECT   a.employee_id,
         a.first_name,
         a.last_name,
         a.manager_id,
         b.first_name mgr_first_name,
         b.last_name mgr_last_name
  FROM   (    SELECT   employee_id,
                       first_name,
                       last_name,
                       manager_id
                FROM   employees
          START WITH   employee_id = 100
          CONNECT BY   PRIOR employee_id = manager_id) a, employees b
 WHERE   a.manager_id = b.employee_id(+)



      Retrieve ex-employees for last 3 months:
=======================================================

SELECT   *
  FROM   per_all_people_f papf, per_periods_of_service ppos
 WHERE   papf.person_id = ppos.person_id
         AND ppos.actual_termination_date BETWEEN ADD_MONTHS (SYSDATE, -3)
                                              AND  SYSDATE                              
         AND ppos.ACTUAL_TERMINATION_DATE BETWEEN papf.effective_start_date
                                            AND  papf.effective_end_date


         *******************            HRMS          ************************