Query to fetch assignment details - 1

select papf.person_number,
       papf.person_id,
       papf.start_date,
       ppnf.full_name employee_full_name,
       paam.assignment_number,
       paam.assignment_name,
       paam.assignment_sequence,
       paam.effective_start_date,
       paam.effective_end_date,
       paam.normal_hours,
       paam.frequency,
       paam.work_at_home,
       paam.date_probation_end,
       paam.probation_period,
       paam.probation_unit,
       paam.notice_period,
       paam.notice_period_uom,
       paam.employee_category,
       paam.employment_category,
       paam.action_code,
       paam.primary_assignment_flag,
       paam.system_person_type,
       paam.retirement_date,
       paam.adjusted_fte
from   per_all_people_f papf,
       per_all_assignments_m paam,
       per_person_names_f ppnf
where  1 = 1
       and trunc(sysdate) between papf.effective_start_date and
                                  papf.effective_end_date
       and papf.person_id = ppnf.person_id
       and trunc(sysdate) between ppnf.effective_start_date and
                                  ppnf.effective_end_date
       and ppnf.name_type = 'GLOBAL'
       and papf.person_id = paam.person_id
       and trunc(sysdate) between paam.effective_start_date and
                                  paam.effective_end_date
       and paam.assignment_status_type in ( 'ACTIVE' ) -- Active Assignment
       and paam.assignment_type in ( 'E' ) -- Type Employee
       and paam.primary_flag = 'Y' -- primary assignment

--and papf.person_number = :p_person_number -- parameter