1. drop materialized view MV_WBS_SUM_HR_PER_DAY
preserve table;

2. CREATE MATERIALIZED VIEW "MV_WBS_LEVELS" BUILD IMMEDIATE REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT TRUNC(SYSDATE+1) + 21.5/24 AS
select * from v_wbs_levels;

3. CREATE MATERIALIZED VIEW "MV_WBS_SUM_HR_PER_DAY" on prebuilt table REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 5/1440 AS
SELECT
      --  ===========================================================================
      --
      --
      --  PROCEDURE : MV_WBS_SUM_HR_PER_DAY
      --
      --
      --  DESCRIPTION :
      --
      --  HISTORY:
      --    1.0   ???   ????.??.??  Creation
      --    1.1   MME   2025.11.13  Adding VO actif and limiting to Year-2
      --    1.2   DLA   2025.12.17  Using v_wbs_levels_union_mv to optimize the request
      -- ===========================================================================
      p.date_day,
      p.wbs_id,
      sum(p.trf_dhr)                      as hrd_sum ,
      w_all.lvl0_wbs_ref_number           as level0_ref_number,
      w_all.lvl1_wbs_ref_number           as level1_ref_number
      from  pd_trf_day     p,
            wbs            w,
            v_wbs_levels_union_mv w_all,
            work_code      wc,
            vehicle_owner  v
      where p.wbs_id                         = w.wbs_id
      and   w.wbs_id                         = w_all.wbs_id
      and   v.vehicle_owner_id               = wc.vehicle_owner_id
      and   w.is_project                     = 'Y'
      and   p.work_code_id                   = wc.work_code_id
      and   nvl(wc.is_wbs_activity,'N')      = 'Y'
      and   nvl(w.wbs_status_id,0)           = 0
      and   nvl(v.vehicle_owner_status_id,0) = 0
      and   v.vehicle_owner_id in (112976,101409,100737,100719,100718,100635,100631,101373)
      and   p.date_day                       >= trunc(sysdate,'YYYY') - 365
      group by p.date_day,
               p.wbs_id ,
               w_all.lvl0_wbs_ref_number,
               w_all.lvl1_wbs_ref_number;