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;
Hi Vamsi,
ReplyDeletecan 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
Hi Raj,
ReplyDeletePlease 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
This comment has been removed by the author.
ReplyDeleteHi Vamsi,
Deletethanks 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';
Can i have query department wise employees in HRMS
ReplyDelete