Forum Stats

  • 3,837,390 Users
  • 2,262,253 Discussions
  • 7,900,269 Comments

Discussions

Details all employees that have changed their job title/role and/or have left the company within the

1046792
1046792 Member Posts: 18
edited Jan 14, 2016 7:09AM in SQLとPL/SQL

Team,

Can anyone help me to get the sql query to get the below output:

Employee_name- Employee_number -Old_org_name-New_org_name - Old_Job_Title,-New_Job_Title

ABC - 101-Company1-Company2-Engineer- Sr.Engineer

This is apart of Oracle HRMS module where we are using common tables like per_all_people_f, hr_organisations_all.. etc.

Do let me know if required more details

Thanks

Answers

  • 1046792
    1046792 Member Posts: 18
    edited Jan 14, 2016 7:09AM

    Below is the query where you can get the table details:

    select

    1. papf.employee_number,
    2. papf.full_name,
    3. PAAF.effective_start_Date asgn_start_date,
    4. PAAF.effective_end_Date asgn_end_date,

    --PAPF.EFFECTIVE_START_DATE person_start_date,

    --PAPF.EFFECTIVE_END_DATE person_end_date,

    1. HAOU.name org_name,

    NVL(PJ.NAME,'Not Defined') JOB_TITLE,

    1. LOC.LOCATION_CODE

    from PER_ALL_ASSIGNMENTS_F PAAF,

    PER_ALL_PEOPLE_F PAPF,

    HR_ALL_ORGANIZATION_UNITS HAOU,

    HR_LOCATIONS_ALL LOC,

    PER_PERSON_TYPES PPT,

    PER_PERSON_TYPE_USAGES_F PPTUF,

    PER_JOBS PJ

    where 1=1

    AND PJ.JOB_ID(+)=PAAF.JOB_ID

    AND PAAF.PERSON_ID          = PAPF.PERSON_ID

    AND PAAF.ORGANIZATION_ID          = HAOU.ORGANIZATION_ID

    AND PAAF.LOCATION_ID        = LOC.LOCATION_ID

    AND PPT.PERSON_TYPE_ID    = PPTUF.PERSON_TYPE_ID

    AND PPTUF.PERSON_ID         = PAPF.PERSON_ID

    AND PPT.SYSTEM_PERSON_TYPE IN('EMP', 'CWK', 'APL', 'OTHER')

    and to_char(PAAF.effective_end_date,'dd-mon-yyyy') <to_char(sysdate,'dd-mon-yyyy')

    and papf.employee_number=2003713

    union all

    select

    1. papf.employee_number,
    2. papf.full_name,
    3. PAAF.effective_start_Date,
    4. PAAF.effective_end_Date,
    5. HAOU.name,

    NVL(PJ.NAME,'Not Defined') JOB_TITLE,

    1. LOC.LOCATION_CODE

    from PER_ALL_ASSIGNMENTS_F PAAF,

    PER_ALL_PEOPLE_F PAPF,

    HR_ALL_ORGANIZATION_UNITS HAOU,

    HR_LOCATIONS_ALL LOC,

    PER_PERSON_TYPES PPT,

    PER_PERSON_TYPE_USAGES_F PPTUF,

    PER_JOBS PJ

    where 1=1

    and PAAF.person_id=papf.person_id

    AND PJ.JOB_ID(+)=PAAF.JOB_ID

    AND PAAF.PERSON_ID          = PAPF.PERSON_ID

    AND PAAF.ORGANIZATION_ID          = HAOU.ORGANIZATION_ID

    AND TRUNC(SYSDATE) BETWEEN TRUNC(PAAF.EFFECTIVE_START_DATE) AND TRUNC(PAAF.EFFECTIVE_END_DATE)

    AND TRUNC(SYSDATE) BETWEEN TRUNC(PPTUF.EFFECTIVE_START_DATE) AND TRUNC(PPTUF.EFFECTIVE_END_DATE)

    AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE)

    AND PAAF.LOCATION_ID        = LOC.LOCATION_ID

    AND PPT.PERSON_TYPE_ID    = PPTUF.PERSON_TYPE_ID

    AND PPTUF.PERSON_ID         = PAPF.PERSON_ID

    AND PPT.SYSTEM_PERSON_TYPE IN('EMP', 'CWK', 'APL', 'OTHER')

    AND PAPF.EMPLOYEE_NUMBER=2003713;

This discussion has been closed.