CREATE MATERIALIZED VIEW "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" ("DATE_DAY", "WBS_ID", "HRD_SUM", "LEVEL0_REF_NUMBER", "LEVEL1_REF_NUMBER")
  SEGMENT CREATION IMMEDIATE
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FLEETDATA"   NO INMEMORY
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 5/1440
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE
  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;

  CREATE INDEX "OF_OWNER"."I_MV_WBS_SUM_HR_PER_DAY_WBS_DATE" ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" ("WBS_ID", "DATE_DAY")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FLEETDATA" ;
  CREATE INDEX "OF_OWNER"."I_MV_WBS_SUM_HR_PER_DAY_WBS_ID" ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" ("WBS_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FLEETDATA" ;

   COMMENT ON MATERIALIZED VIEW "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY"  IS 'snapshot table for snapshot OF_OWNER.MV_WBS_SUM_HR_PER_DAY';


  GRANT SELECT ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_TABLES_READER_ROLE";
  GRANT INSERT ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_TABLES_WRITER_ROLE";
  GRANT UPDATE ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_TABLES_WRITER_ROLE";
  GRANT DELETE ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_TABLES_ERASER_ROLE";
  GRANT ALTER ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_TABLES_ADMIN_ROLE";
  GRANT DELETE ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_TABLES_ADMIN_ROLE";
  GRANT INSERT ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_TABLES_ADMIN_ROLE";
  GRANT SELECT ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_TABLES_ADMIN_ROLE";
  GRANT UPDATE ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_TABLES_ADMIN_ROLE";
  GRANT READ ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_TABLES_ADMIN_ROLE";
  GRANT ON COMMIT REFRESH ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_TABLES_ADMIN_ROLE";
  GRANT QUERY REWRITE ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_TABLES_ADMIN_ROLE";
  GRANT DEBUG ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_TABLES_ADMIN_ROLE";
  GRANT FLASHBACK ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_TABLES_ADMIN_ROLE";
  GRANT SELECT ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_MATERIALIZED_VIEWS_READER_ROLE";
  GRANT INSERT ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_MATERIALIZED_VIEWS_WRITER_ROLE";
  GRANT UPDATE ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_MATERIALIZED_VIEWS_WRITER_ROLE";
  GRANT ALTER ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_MATERIALIZED_VIEWS_ADMIN_ROLE";
  GRANT DELETE ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_MATERIALIZED_VIEWS_ADMIN_ROLE";
  GRANT INSERT ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_MATERIALIZED_VIEWS_ADMIN_ROLE";
  GRANT SELECT ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_MATERIALIZED_VIEWS_ADMIN_ROLE";
  GRANT UPDATE ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_MATERIALIZED_VIEWS_ADMIN_ROLE";
  GRANT READ ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_MATERIALIZED_VIEWS_ADMIN_ROLE";
  GRANT ON COMMIT REFRESH ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_MATERIALIZED_VIEWS_ADMIN_ROLE";
  GRANT QUERY REWRITE ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_MATERIALIZED_VIEWS_ADMIN_ROLE";
  GRANT DEBUG ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_MATERIALIZED_VIEWS_ADMIN_ROLE";
  GRANT FLASHBACK ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_MATERIALIZED_VIEWS_ADMIN_ROLE";
  GRANT DEBUG ON "OF_OWNER"."MV_WBS_SUM_HR_PER_DAY" TO "TRX_OF_OWNER_MATERIALIZED_VIEWS_REVIEWER_ROLE";