Query to fetched Published Schedule of Employee

Query to fetched Published Schedule of Employee

select 

scheduled_ref_date,papf.person_id,

ds.scheduled_shift_code,

ds.scheduled_shift_name

from   

per_all_people_f papf,

per_all_assignments_m paam,

table(hts_ff_util.hts_get_scheduled_shifts(p_start_time => :p_start_date ,p_stop_time =>to_date(to_char(:p_end_date,'dd-mm-yyyy'),'dd-mm-yyyy') + 1 ,p_assignment_id =>paam.assignment_id)) ds

where  

1 = 1

and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

and papf.person_id = paam.person_id

and trunc(sysdate) between paam.effective_start_date and  paam.effective_end_date

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' )