select pj.job_id,papf.employee_number,papf.attribute9 global_emp_number,pj.NAME job_name,paaf.CHANGE_REASON,paaf.EFFECTIVE_START_DATE from hr.per_all_people_f papf,hr.per_all_assignments_f paaf, per_jobs pj where papf.PERSON_ID = paaf.PERSON_ID and paaf.JOB_ID = pj.JOB_ID and paaf.PRIMARY_FLAG='Y' and papf.CURRENT_EMPLOYEE_FLAG='Y' and employee_number='4050'-----give your desired employee id and trunc(sysdate) between papf.effective_start_date and papf.EFFECTIVE_END_DATE order by paaf.EFFECTIVE_START_DATE