Monday, August 24, 2009

Oracle APPS HRMS Query for Education Background

Given query will extract data for employee's education background.

SELECT PAPF.PERSON_ID,PAPF.FIRST_NAME ||' ' || PAPF.LAST_NAME AS NAME,PJ.NAME AS JOBS ,PG.NAME AS GRADE ,PAP.NAME AS POSITION,
HAOU.NAME AS ORAGNAIZATION,PPT.SYSTEM_PERSON_TYPE,PQT.NAME AS DEGR,PQ.TITLE,PQ.GRADE_ATTAINED,PQ.AWARDED_DATE,PQ.START_DATE,PQ.END_DATE
FROM PER_ALL_PEOPLE_F PAPF,HR_ALL_ORGANIZATION_UNITS HAOU,PER_ALL_ASSIGNMENTS_F PASF,PER_JOBS PJ,PER_GRADES PG,
PER_ALL_POSITIONS PAP,PER_PERSON_TYPES PPT,PER_QUALIFICATIONS PQ,PER_QUALIFICATION_TYPES PQT
WHERE PASF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND PASF.LOCATION_ID = HAOU.LOCATION_ID
AND PASF.PERSON_ID = PAPF.PERSON_ID
AND PAPF.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE IN ('EMP','APP')
AND PJ.JOB_ID = PASF.JOB_ID
AND PG.GRADE_ID = PASF.GRADE_ID
AND PAP.POSITION_ID = PASF.POSITION_ID
AND PAPF.EFFECTIVE_END_DATE > SYSDATE
AND PASF.EFFECTIVE_END_DATE > sysdate
AND PQ.QUALIFICATION_TYPE_ID = PQT.QUALIFICATION_TYPE_ID
AND PQ.PERSON_ID = PAPF.PERSON_ID
ORDER BY 1,4

1 comment:

  1. Excellent post ! Thanks for posting Hrms Software Features Softwares help to make the Payroll work easy. They are one of the payroll developing companies. They provide the best payroll outsourcing and HR Software.
    Hrms Software Features
    Cloud Based Hrms

    ReplyDelete