Query to Extract Employee Qualifications in Oracle HRMS

Query:

SELECT 

        PAPF.EMPLOYEE_NUMBER 

       ,PAPF.FULL_NAME

       ,PQV.NAME QUALIFICATION_TYPE

       ,PQV.TITLE

       ,PQV.STATUS_MEANING

       ,PQV.GRADE_ATTAINED

       ,PQV.ESTABLISHMENT

       ,PQV.START_DATE

       ,PQV.END_DATE

       ,PQV.AWARDED_DATE

       ,PQV.PROJECTED_COMPLETION_DATE

       ,PQV.GROUP_RANKING

       ,PQV.COMMENTS

       ,PQV.AWARDING_BODY

       ,PQV.QUA_INFORMATION_CATEGORY||'.'||PQV.QUA_INFORMATION2||'.'||PQV.QUA_INFORMATION3 FURTHER_INFORMATION

       ,PQV.LICENSE_NUMBER

       ,PQV.LICENSE_RESTRICTIONS

       ,PQV.EXPIRY_DATE

       ,PQV.FEE

       ,PQV.FEE_CURRENCY

       ,PQV.TUITION_METHOD_MEANING

       ,PQV.REIMBURSEMENT_ARRANGEMENTS

       ,PQV.TRAINING_COMPLETED_AMOUNT

       ,PQV.TOTAL_TRAINING_AMOUNT

       ,PQV.TRAINING_COMPLETED_UNITS

       ,PQV.PROFESSIONAL_BODY_NAME

       ,PQV.MEMBERSHIP_NUMBER

       ,PQV.MEMBERSHIP_CATEGORY

       ,PQV.SUBSCRIPTION_PAYMENT_METHOD

       ,PST.SUBJECT

       ,HR_GENERAL.DECODE_LOOKUP ('PER_SUBJECT_STATUSES', PST.SUBJECT_STATUS) STATUS

       ,PST.START_DATE SUBJECT_START_DATE

       ,PST.END_DATE    SUBJECT_END_DATE

       ,PST.MAJOR       

       ,PST.GRADE_ATTAINED GRADE

FROM    PER_ALL_PEOPLE_F            PAPF

       ,PER_ALL_ASSIGNMENTS_F       PAAF

       ,PER_QUALIFICATIONS_V        PQV

       ,PER_SUBJECTS_TAKEN          PST

WHERE   1=1

AND     PAPF.PERSON_ID          =   PAAF.PERSON_ID

AND     PAAF.PERSON_ID          =   PQV.PERSON_ID

AND     PQV.QUALIFICATION_ID    =   PST.QUALIFICATION_ID(+)

--AND     PAPF.EMPLOYEE_NUMBER    =   40

AND     SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

AND     SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE

Navigation: