with

ws_interval as
---------
(
select ws.id_paragraph
       , min(ws.d_start) as d_start
       , max(ws.d_end) as d_end
from working_hours_sheet ws
where ws.id_paragraph = 9824473
group by ws.id_paragraph
)
---------

, whs_whis_edition as
---------
(
-- график и редакции графика
-- график
select ws.id_working_hours_sheet
from working_hours_sheet ws
where ws.id_paragraph = 9824473
union all
-- редакции графика
select ws.id_working_hours_sheet
from working_hours_sheet ws
where ws.id_whs_changed in
(
select ws.id_working_hours_sheet
from working_hours_sheet ws
where ws.id_paragraph = 9824473
)
)
---------

, workers_this_whs as
---------
(
-- работники заданного табеля
select ew.id_employees_in_whs
       , s.id_worker
       , ws.id_d
from working_hours_sheet ws
     , employees_in_whs ew
     , staff s
where ws.id_paragraph = 9824473
      and ew.id_working_hours_sheet = ws.id_working_hours_sheet
      and s.id_staff = ew.id_staff
)
---------

, workers_ex as
-- Работники заданного табеля и актуальных редакций предыдущего табеля для учёта переходящих смен (должно ограничиваться датами)
---------
(
-- работники заданного табеля
select ew.id_employees_in_whs
from workers_this_whs ew
union all

-- работники других табелей (с учётом редакций) из состава заданного табеля (для учёта мигрирующих смен)
select ew.id_employees_in_whs
from
(
select ew.id_employees_in_whs
       , s.id_worker
       , ws.id_d
       , row_number() over(partition by nvl(ws.id_whs_changed, ws.id_working_hours_sheet), s.id_worker, ws.id_d order by nvl(ps.hisdate, ps.d_modification) desc) rn
from working_hours_sheet ws
     , paragraphs_status ps
     , employees_in_whs ew
     , staff s
     , ws_interval wsi
where
      -- исключаем заданный табель и его редакции
      ws.id_working_hours_sheet not in
---
(
select wh2.id_working_hours_sheet
from whs_whis_edition wh2
)
---
      -- берём предыдущий интервал ()
      and ws.d_end = wsi.d_start - 1
      and ew.id_working_hours_sheet = ws.id_working_hours_sheet
      and s.id_staff = ew.id_staff
      and ps.id_paragraph = ws.id_paragraph
--      and ps.status_order = 1
      and
-- работники рассматриваемого табеля
--(s.id_worker, ws.id_d) in
-- не соединяем по id_worker, так как мог уволиться и в текущем табеле отсутствует
(ws.id_d) in
(
select
       --w2.id_worker
       --,
       w2.id_d
from workers_this_whs w2
)
) ew
where
      -- актуальная редакция
      ew.rn = 1
)
---------













select ws.id_employees_in_whs
       , ws.id_e
       , ws.surname
       , ws.id_paragraph
       , ws.id_work_system_interval
       , ws.d_work
       , ws.id_post_location
       , ws.work_shift_number
       , ws.hours
       , ws.nigth_hours
       ,
---
case
when exists
(
select 1
from holidays_ h
--     , (select to_date('01.01.2019', 'dd.mm.yyyy') as d_work from dual) ws
where ws.d_work between trunc(h.y_start, 'yyyy') and ( add_months(trunc(nvl(h.y_end, ws.d_work), 'yyyy'), 12) - 1 )
      and
(
ws.d_work between
to_date(to_char(h.d_start, 'dd.mm') || '.' || to_char(ws.d_work, 'yyyy'), 'dd.mm.yyyy')
and to_date(to_char(h.d_end, 'dd.mm') || '.' || to_char(ws.d_work, 'yyyy'), 'dd.mm.yyyy')
)
)
then 1
else 0
end as is_holiday
---

from
(
select sh.id_employees_in_whs
       , sh.id_work_system_interval
       , sh.d_work + wsi.ld_num - 1 as d_work
       , sh.id_post_location
       , wsi.work_shift_number
       , wsi.hours
       , wsi.nigth_hours
       , ws.d_start
       , ws.d_end
       , ws.id_paragraph
       , e.id_e
       , e.surname
from worked_shifts sh
     , table(cast(pilot.p_hr_worktime.get_work_system_interval() as pilot.pt_work_system_interval_t)) wsi
     , employees_in_whs ew
     , working_hours_sheet ws
     , staff s
     , employees e
where sh.id_work_system_interval = wsi.id_work_system_interval
      and sh.id_employees_in_whs in
---
-- По сменному графику работы при восстановлении табеля включаются записи непосредственно табеля,
-- а также записи других табелей по данному человеку с учётом редакций, попадающие в интервал дат рассматриваемого табеля.
(
select ew2.id_employees_in_whs
from workers_ex ew2
)
---
      and sh.id_employees_in_whs = ew.id_employees_in_whs
      and ws.id_working_hours_sheet = ew.id_working_hours_sheet
      and s.id_staff = ew.id_staff
      and e.id_e = s.id_e
) ws
     , ws_interval wsi
where
      ws.d_work between wsi.d_start and wsi.d_end
order by
ws.id_e
, ws.d_work