Search This Blog

Friday, April 6, 2012

HRMS- SQL Query for Extracting Employee Information


select
papf.Last_Name, papf.First_Name, papf.Title, hl_sex.meaning ,pptt.user_person_type ,papf.national_identifier SSN,
papf.date_of_birth, hl_nat.meaning Nationality,
TRUNC (MONTHS_BETWEEN (SYSDATE, papf.date_of_birth) / 12) Age,
hl_mar.meaning Marital_Status, papf.Effective_Start_Date,
papf.Effective_End_Date, hl_eth.meaning Ethnic_Origin,
papf.Email_address,
papf.original_date_of_hire Date_First_Hired
FROM
per_all_people_f papf,
hr_lookups hl_sex,
per_person_types_tl pptt,
hr_lookups hl_nat,
hr_lookups hl_mar,
hr_lookups hl_eth
WHERE 1=1
AND hl_sex.lookup_code(+) = papf.sex
AND hl_sex.lookup_type(+) = 'SEX'
AND hl_nat.lookup_code(+) = papf.nationality
AND hl_nat.lookup_type(+) = 'NATIONALITY'
AND hl_mar.lookup_code(+) = papf.marital_status
AND hl_mar.lookup_type(+) = 'MAR_STATUS'
AND hl_eth.lookup_code(+) = papf.per_information1
AND hl_eth.lookup_type(+) = 'US_ETHNIC_GROUP'
and papf.person_type_id = pptt.person_type_id
AND papf.effective_end_date > SYSDATE
AND papf.person_id =&Person_ID;

5 comments:

  1. Hi Vamsi,

    can you do me a favor ?
    I am able to find supervisor name on HRMS using below query

    SELECT papf1.full_name supervisor_name From apps.per_all_people_f papf,apps.per_all_assignments_f paaf,apps.per_all_people_f papf1 WHERE papf.person_id = paaf.person_id AND paaf.primary_flag = 'Y' AND paaf.assignment_type = 'E' AND paaf.supervisor_id = papf1.person_id AND papf1.current_employee_flag = 'Y'AND papf.business_group_id = paaf.business_group_id AND SYSDATE BETWEEN papf.effective_start_date and papf.effective_end_date AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND SYSDATE BETWEEN papf1.effective_start_date AND papf1.effective_end_date AND papf.employee_number='23436';


    and now i am trying to find employee salary (gross) on HRMS using below query...for some reason the query is not working for me can you help ?


    SELECT ppp.proposed_salary_n salary From per_pay_proposals ppp,apps.per_all_people_f papf,apps.per_all_assignments_f paaf,apps.per_all_people_f papf1 WHERE papf.person_id = paaf.person_id AND paaf.primary_flag = 'Y' AND paaf.assignment_type = 'E' AND paaf.supervisor_id = papf1.person_id AND papf1.current_employee_flag = 'Y'AND papf.business_group_id = paaf.business_group_id AND SYSDATE BETWEEN papf.effective_start_date and papf.effective_end_date AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND SYSDATE BETWEEN papf1.effective_start_date AND papf1.effective_end_date AND papf.employee_number='23436';


    Thanks
    Raj

    ReplyDelete
  2. Hi Raj,

    Please use below query..

    SELECT
    p.person_id
    ,p.full_name
    ,p.employee_number
    ,p.business_group_id
    , (pp.proposed_salary_n * b.pay_annualization_factor) salary
    ,a.assignment_id
    FROM per_all_people_f p
    ,per_all_assignments_f a
    ,per_pay_proposals pp
    ,Per_Pay_Bases B
    WHERE p.person_id = 293884
    And P.Person_Id = A.Person_Id
    And Sysdate Between P.Effective_Start_Date And P.Effective_End_Date
    and sysdate between a.effective_start_date and a.effective_end_date
    And Pp.Assignment_Id = A.Assignment_Id
    And A.Assignment_Id = 281336
    And B.Pay_Basis_Id = A.Pay_Basis_Id;

    Regards,
    Vamsi

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Hi Vamsi,

      thanks for your reply can you help me to fix the query. I try to match with your syntax and i am still missing some thing.

      SELECT ppp.proposed_salary_n salary From apps.per_pay_proposals ppp,apps.per_all_people_f papf,apps.per_all_assignments_f paaf,apps.per_all_people_f papf1 WHERE papf.person_id = paaf.person_id AND SYSDATE BETWEEN papf.effective_start_date And papf.effective_end_date AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND papf.employee_number='31284';

      Delete
  4. Can i have query department wise employees in HRMS

    ReplyDelete