CREATE OR REPLACE FORCE VIEW VIEW_REPORTEVENTS 
  AS
  SELECT W.WorkPolygonName,
          div.DivisionName ,
          D.DistrictName ,
          cp.CompanyName ,
          e.EventKey ,
          (CASE WHEN e.IsCompleted = 'True' THEN 1 ELSE 0 END) Completed,
          (CASE WHEN e.IsFieldException = 'True' THEN 1 ELSE 0 END) Exception_,
          (CASE WHEN e.DueDate <= SYSDATE THEN 1 ELSE 0 END) Due,
          e.DueDate ,
          e.ComplianceDate ,
          e.CompletedDate ,
          (CASE WHEN e.ComplianceDate <= SYSDATE THEN 1 ELSE 0 END) Compliance,
          ac.ActivityName ,
          ProgramType.ProgramTypeName ,
          W.WorkPolygonKey ,
          cp.CompanyKey ,
          div.DivisionKey ,
          D.DistrictKey ,
          e.TargetType ,
          ac.WorkType ,
          p.CycleStartDate ,
          p.CycleEndDate ,
          p.ProgramName ,
          p.ProgramKey ,
          W.GISFeatureName ,
          (CASE WHEN W.GISLayerId IS NULL THEN CAST(W.WorkPolygonKey AS VARCHAR2(100)) ELSE CONCAT(CONCAT(W.GISLayerId, '-'), W.GISUniqueId) END) GISFeatureKey  ,
          ac.AverageNumberPeople * (EXTRACT(minute from ac.AverageActivityTime) / 60 + EXTRACT(hour from ac.AverageActivityTime))  Manhour  
     FROM Company cp 
             INNER JOIN Division div ON cp.CompanyKey = div.CompanyKey 
             INNER JOIN District D  ON div.DivisionKey = D.DivisionKey  
             INNER JOIN view_ReportPrograms p ON D.DistrictKey = p.DistrictKey
            INNER JOIN WorkPolygon W ON p.ProgramKey = W.ProgramKey
            INNER JOIN Event e ON W.WorkPolygonKey = e.WorkPolygonKey AND e.ParentEventKey IS NULL
            INNER JOIN ProgramActivity  ON p.ProgramKey = ProgramActivity.ProgramKey  AND e.ProgramActivityKey = ProgramActivity.ProgramActivityKey 
            INNER JOIN Activity ac  ON ProgramActivity.ActivityKey = ac.ActivityKey
            INNER JOIN ProgramType  ON p.ProgramTypeKey = ProgramType.ProgramTypeKey
     GROUP BY W.WorkPolygonName,p.ProgramName,div.DivisionName,D.DistrictName,cp.CompanyName,e.EventKey,e.DueDate,
     ac.ActivityName,ProgramType.ProgramTypeName,W.WorkPolygonKey,cp.CompanyKey,div.DivisionKey,D.DistrictKey,e.ComplianceDate,
     e.IsCompleted,e.IsFieldException,e.CompletedDate,e.TargetType,ac.WorkType,p.CycleStartDate,p.CycleEndDate,p.ProgramKey,W.GISFeatureName,
     W.GISUniqueId,W.GISLayerId,ac.AverageActivityTime,ac.AverageNumberPeople;