+- Oracle Forum - The Knowledge Center for Oracle Professionals - Looking Beyond the Possibilities (http://www.oraerp.com)
+-- Forum: Oracle Applications (http://www.oraerp.com/forum-39.html)
+--- Forum: Oracle E-Business Suite (EBS) (http://www.oraerp.com/forum-1.html)
+---- Forum: Human Resources (http://www.oraerp.com/forum-4.html)
+---- Thread: Employee Contact Detail (/thread-71192.html)
Employee Contact Detail - shirfanarshad - 10-24-2014
select
papf.EMPLOYEE_NUMBER, papf.FULL_NAME Employee_name, PAPF.ORIGINAL_DATE_OF_HIRE
, pg.NAME Grade, pp.NAME Position, ppf.PAYROLL_NAME
,decode (pcr.CONTACT_TYPE, 'S','Spouse','JP_MT','Mother','JP_FT','Father', pcr.CONTACT_TYPE) CONTACT_TYPE
, papr.FULL_NAME
, papr.DATE_OF_BIRTH
, pcr.DEPENDENT_FLAG, pcr.PERSONAL_FLAG Persoal_Relationship
, pcr.DATE_START, pcr.DATE_END
, pcr.CONT_ATTRIBUTE12 , CONT_ATTRIBUTE1 , CONT_ATTRIBUTE2 , CONT_ATTRIBUTE3 , CONT_ATTRIBUTE4 ,CONT_ATTRIBUTE5 , CONT_ATTRIBUTE6 , CONT_ATTRIBUTE7 , CONT_ATTRIBUTE8 , CONT_ATTRIBUTE9 ,
CONT_ATTRIBUTE10 , CONT_ATTRIBUTE11 , CONT_ATTRIBUTE13
--, papr.FULL_NAME
from per_all_people_f papf, PER_CONTACT_RELATIONSHIPS pcr, per_all_people_f papr
, per_all_assignments_f paaf, per_grades pg, per_positions pp, pay_payrolls_f ppf
where
papf.PERSON_ID = pcr.PERSON_ID
and pcr.CONTACT_PERSON_ID = papr.PERSON_ID
and papf.PERSON_ID = paaf.PERSON_ID
and paaf.GRADE_ID = pg.GRADE_ID
and paaf.POSITION_ID = pp.POSITION_ID
and paaf.PAYROLL_ID = ppf.PAYROLL_ID
and sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE
and sysdate between papr.EFFECTIVE_START_DATE and papr.EFFECTIVE_END_DATE
and sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE
and ppf.PAYROLL_NAME = 'Payroll-UAE'
--and papf.EMPLOYEE_NUMBER = :Employee_number