ERPstuff - Oracle EBS Community

hello,

Employee information report was requested like:
ENO,Grade,Job,salary,etc

I have managed to select most of the required data using the following query: (it's for one employee and gives me 2 records because of the change in salary)

select *
from per_all_people_f e
,per_all_assignments_f a
,HR_ALL_ORGANIZATION_UNITS o
,PAY_COST_ALLOCATION_KEYFLEX k
,per_jobs j
,PER_ALL_POSITIONS s
,HR_LOCATIONS_ALL_TL l
,per_grades g
,per_grade_definitions d
,PER_pay_proposals y
,pay_people_groups u
where
a.PERSON_ID(+)=e.PERSON_ID
and e.attribute30 = 5291
and a.ORGANIZATION_ID=o.ORGANIZATION_ID
and o.COST_ALLOCATION_KEYFLEX_ID=k.COST_ALLOCATION_KEYFLEX_ID(+)
and e.person_id in ('322','1018') -- 3 records
and e.BUSINESS_GROUP_ID = 229
and a.JOB_ID=j.JOB_ID(+)
and a.POSITION_ID=s.POSITION_ID(+)
and a.LOCATION_ID=l.LOCATION_ID
and a.GRADE_ID=g.GRADE_ID
and g.GRADE_DEFINITION_ID=d.GRADE_DEFINITION_ID
and a.ASSIGNMENT_ID=y.ASSIGNMENT_ID
and a.PAYROLL_ID=u.PEOPLE_GROUP_ID

My first question is:For any employee how can i choose the record with the most recent change in salary?

Another issue how can i join the element tables to the query above? I need to add 2 columns of elements such as car hire and children with the values assigned to employees within the employee record. How can I do that?

Help please...

Another question please:

I need to add the cost center column to each employee and as far as i know i can get the cost center from the following
query:

select
k.COST_ALLOCATION_KEYFLEX_ID,k.SEGMENT3
from HR_ALL_ORGANIZATION_UNITS o
,PAY_COST_ALLOCATION_KEYFLEX k
where
k.COST_ALLOCATION_KEYFLEX_ID=o.COST_ALLOCATION_KEYFLEX_ID

I need to know how can i join the above query to:

per_all_people_f or per_all_assignments_f tables???

select F.EMPLOYEE_NUMBER"FILE NUMBER",initcap(F.FULL_NAME)"NAME",TO_CHAR (F.ORIGINAL_DATE_OF_HIRE,'DD/MM/YYYY')"Hire Date",
t.name"DEPARTMENT",l.MEANING"Nationality",
p.segment1||p.segment2"COST CENTER",S.NAME"POSITION",ASSIGNMENT_STATUS_TYPE_ID"STATUS",
F.DATE_OF_BIRTH "BIRTH DATE"---,E.PEI_INFORMATION1"NUMBER"
,PRO.PROPOSED_SALARY_N "BASIC_SALARY",hr_person_type_usage_info.get_user_person_type(sysdate,F.person_id) person_type,G.NAME"N GRAD"
from hr_all_organization_units t,pay_cost_allocation_keyflex p ,
PER_ALL_ASSIGNMENTS_F A,HR_ALL_POSITIONS_F S
,PER_GRADES G,PER_ALL_PEOPLE_f F,FND_LOOKUP_VALUES_VL L,PER_PAY_PROPOSALS PRO---,PER_PEOPLE_EXTRA_INFO E
where (t.cost_allocation_keyflex_id=p.cost_allocation_keyflex_id)
and A.business_group_id = '229'AND T.BUSINESS_GROUP_ID='229'and F.business_group_id = '229'
AND A.GRADE_ID=G.GRADE_ID
AND F.PERSON_ID=A.PERSON_ID
AND sysdate BETWEEN A.EFFECTIVE_START_DATE and A.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN F.EFFECTIVE_START_DATE and F.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN S.EFFECTIVE_START_DATE and S.EFFECTIVE_END_DATE
AND A.ORGANIZATION_ID=T.ORGANIZATION_ID
AND A.POSITION_ID = S.POSITION_ID
AND A.ASSIGNMENT_STATUS_TYPE_ID NOT IN('3','2')
AND F.NATIONALITY=L.LOOKUP_CODE
AND A.ASSIGNMENT_ID= PRO.ASSIGNMENT_ID
--and TO_CHAR (F.ORIGINAL_DATE_OF_HIRE,'DD/MM/YYYY')>'31/12/2007'
--AND SUBSTR (lpad(G.NAME, 5, 0),0,2) <'09'
--AND S.NAME<>'Trainee- PF'
--AND ASSIGNMENT_STATUS_TYPE_ID='1'
--AND E.PERSON_ID(+)=F.PERSON_ID
--AND E.INFORMATION_TYPE='SA_IQAMA'
AND PRO.CHANGE_DATE =
(SELECT MAX(X.CHANGE_DATE)
FROM PER_PAY_PROPOSALS X
WHERE X.ASSIGNMENT_ID = A.ASSIGNMENT_ID)
---AND hr_person_type_usage_info.get_user_person_type(sysdate,F.person_id) LIKE'T%'
--AND p.segment1=
--AND p.segment2 =