I_EHSRiskOverviewC

@EndUserText.label: 'Risk Overview'
@Analytics: { dataCategory:#CUBE , dataExtraction.enabled:true }
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #CHECK

@AbapCatalog.sqlViewName: 'IEHSRSKOVERVIEWC'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey:true
// Client handling by session

@ClientHandling.algorithm: #SESSION_VARIABLE

// used for BOBF Master Data Object

@ObjectModel.usageType:{ serviceQuality: #D,        // < 15 msec

                         sizeCategory:  #L,         // < 10.000.000

                         dataClass: #MIXED }
@Metadata.allowExtensions:true 
                         
// Cube for Risk Overview

define view I_EHSRiskOverviewC
  with parameters
    @Consumption.hidden: true
    @Environment.systemField: #SYSTEM_LANGUAGE
    P_Language : sylangu,
    @Consumption.hidden: true
    @Environment.systemField: #SYSTEM_DATE
    P_KeyDate  : sydatum
  as select from I_EHSRisk 


  // Filter risks by "My Location" and its sublocations

    inner join I_MyEHSLocationExpanded on I_EHSRisk.EHSLocationUUID = I_MyEHSLocationExpanded.EHSLocationUUID
  association [0..*] to I_EHSAgent as _Agent on $projection.EHSAgentUUID = _Agent.EHSAgentUUID 
  association [1..1] to  I_RatedEHSRiskLevel as _RatedEHSRiskLevel on _RatedEHSRiskLevel.LinkFlagIsOne = 1
{

  key I_EHSRisk.EHSRiskUUID,

      @ObjectModel.foreignKey.association: '_Hazard'
      _EHSRiskRoot.Hazard,
      _EHSRiskRoot._Hazard,
      
      @ObjectModel.foreignKey.association: '_HazardCategory'
      _EHSRiskRoot.HazardCategory,
      _EHSRiskRoot._HazardCategory,
      
      // Merge the "Agent" fields (Physical, Chemical and Airborne) into one single field

      @ObjectModel.foreignKey.association: '_Agent'
      cast ( case
        when I_EHSRisk.ChemicalUUID != hextobin( '00000000000000000000000000000000' )
        then I_EHSRisk.ChemicalUUID
        when I_EHSRisk.AirborneAgentUUID != hextobin( '00000000000000000000000000000000' )
        then I_EHSRisk.AirborneAgentUUID
        when I_EHSRisk._EHSRiskRoot.PhysicalAgentUUID != hextobin( '00000000000000000000000000000000' )
        then I_EHSRisk._EHSRiskRoot.PhysicalAgentUUID
        else hextobin( '00000000000000000000000000000000' )
      end as ehhss_agent_combined_uuid_ref preserving type ) as EHSAgentUUID,
      _Agent,


      @ObjectModel.foreignKey.association: '_EHSRiskLevel'
      _EHSRiskRoot.EHSRiskLevel,
      _EHSRiskRoot._EHSRiskLevel,

      @ObjectModel.foreignKey.association: '_EHSJob'
      _EHSRiskRoot.EHSJobUUID,
      _EHSRiskRoot._EHSJob,

      @ObjectModel.foreignKey.association: '_EHSRiskAssessment'
      EHSRiskAssessmentUUID,
      _EHSRiskAssessment,

      @ObjectModel.foreignKey.association: '_EHSRiskStatus'
      I_EHSRisk.EHSRiskStatus,
      @ObjectModel.association.boundFields.dataMatchesSourceAndTargetTypes:true
      _EHSRiskStatus,

      @ObjectModel.foreignKey.association: '_EHSLocation'
      I_EHSRisk.EHSLocationUUID,
      _EHSLocation,

      // Count risks

      @DefaultAggregation: #SUM
      cast (1 as ehhss_number_of_risks)     as NumberOfEHSRisks,

      I_EHSRisk._EHSRiskRoot                    as _EHSRiskRoot,

      @DefaultAggregation: #SUM
      cast ( case
        when _EHSRiskRoot.EHSRiskLevel = _RatedEHSRiskLevel.EHSRiskLevelPrio3 // Low

        then 1
        else 0
      end as ehhss_risk_level_prio_3 )      as NumberOfEHSRisksPrio3,

      @DefaultAggregation: #SUM
      cast ( case
        when _EHSRiskRoot.EHSRiskLevel = _RatedEHSRiskLevel.EHSRiskLevelPrio2 // Medium

        then 1
        else 0
      end as ehhss_risk_level_prio_2 )      as NumberOfEHSRisksPrio2,

      @DefaultAggregation: #SUM
      cast ( case
        when _EHSRiskRoot.EHSRiskLevel = _RatedEHSRiskLevel.EHSRiskLevelPrio1 // High

        then 1
        else 0
      end as ehhss_risk_level_prio_1 ) as NumberOfEHSRisksPrio1,
      
      @DefaultAggregation: #SUM
      cast ( case
        when _EHSRiskRoot.EHSRiskLevel = '' // High

        then 1
        else 0
      end as ehhss_risk_level_unassigned ) as NumberOfUnassignedEHSRisks      
      
}
// Filter by current risk revision

where RevisionStartDate <= $parameters.P_KeyDate
  and RevisionEndDate   >= $parameters.P_KeyDate
  and ( EHSRiskStatus = '02' or EHSRiskStatus = '03' )
  // Only consider active or inactive locations (to be precise: the current location revision revision must be either inactive or active)

  and ( I_EHSRisk._EHSLocation[1: RevisionStartDate <= $parameters.P_KeyDate  and RevisionEndDate >= $parameters.P_KeyDate].EHSLocationStatus = '02'
    or I_EHSRisk._EHSLocation[1: RevisionStartDate <= $parameters.P_KeyDate and RevisionEndDate >= $parameters.P_KeyDate].EHSLocationStatus = '03' )