Tuesday 29 April 2014

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          ************************

3 comments:

  1. Interesting post! Thanks for writing it. What's wrong with this kind of post exactly? It follows your previous guideline for post length as well as clarity..
    HRMS Software in Dubai
    HRMS Software Dubai
    HRMS Software in UAE
    HRMS Software
    HR Management Software
    Payroll Software

    ReplyDelete
  2. They're produced by the very best degree developers who will be distinguished for your polo dress creating. You'll find polo Ron Lauren inside exclusive array which include particular classes for men, women. Employee engagement in Dubai

    ReplyDelete
  3. Are you looking for SEO? Hire SEO staff for small business, start-ups, and big companies too. You will get affordable packages with top 10 results in Google.

    ReplyDelete