I_ChmRskAtEHSLocC

@EndUserText.label: 'Chemical Risk at EHS Location Cube'
@Analytics: { dataCategory:#CUBE , dataExtraction.enabled:true }
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #CHECK
@AccessControl.privilegedAssociations: ['_EHSRiskAnalysis', '_EHSRiskAnalysisStandard']
@Metadata.allowExtensions:true 

@AbapCatalog.sqlViewName: 'ICHMRISKATLOCC'
@AbapCatalog.compiler.compareFilter: true

// Client handling by session 

@ClientHandling.algorithm: #SESSION_VARIABLE

// used for BOBF Master Data Object

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

                         sizeCategory:  #L,         // < 10.000.000

                         dataClass: #MIXED } 

// Cube for Chemical Risk Report

define view I_ChmRskAtEHSLocC
as select from I_ChemicalRoot

  inner join I_EHSRiskRoot on I_ChemicalRoot.ChemicalUUID = I_EHSRiskRoot.ChemicalUUID

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

//  inner join I_MyEHSLocationExpanded on I_EHSRiskRoot.EHSLocationUUID = I_MyEHSLocationExpanded.EHSLocationUUID


  association [0..*] to I_EHSRisk           as _EHSRisk           on $projection.EHSRiskUUID = _EHSRisk.EHSRiskUUID

  association [0..*] to I_Chemical          as _Chemical          on $projection.ChemicalUUID = _Chemical.ChemicalUUID

  association [0..*] to I_EHSLocation       as _EHSLocation       on $projection.EHSLocationUUID = _EHSLocation.EHSLocationUUID
{
  @ObjectModel.foreignKey.association: '_Chemical'
  key I_ChemicalRoot.ChemicalUUID,
  _Chemical,

  // EHS Location

  @ObjectModel.foreignKey.association: '_EHSLocation'
  key I_EHSRiskRoot.EHSLocationUUID,
  _EHSLocation,

  // Hazard

  @ObjectModel.foreignKey.association: '_Hazard'
  key I_EHSRiskRoot.Hazard,
  I_EHSRiskRoot._Hazard,
  
  // EHS Location Type

  @ObjectModel.foreignKey.association: '_EHSLocationType'
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].EHSLocationType, 
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._EHSLocationType,

  // EHS Location Plant ID 

  @ObjectModel.foreignKey.association: '_Plant'
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].Plant,
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._Plant,
  
  // EHS Location Company Code 

  @ObjectModel.foreignKey.association: '_CompanyCode'
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].CompanyCode,
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._CompanyCode,
  
  // EHS Location Business Area

  @ObjectModel.foreignKey.association: '_BusinessArea'
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].BusinessArea,
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._BusinessArea,
  
  // EHS Location Cost Center

  @ObjectModel.foreignKey.association: null
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].CostCenter,
//  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._CostCenter,


  // EHS Location Country

  @ObjectModel.foreignKey.association: '_Country'
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].Country,
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._Country,

  // EHS Location Countrling Area

  @ObjectModel.foreignKey.association: '_ControllingArea'
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].ControllingArea,
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._ControllingArea,  

  // EHS Location Org Unit

  @ObjectModel.foreignKey.association: '_OrganizationalUnit'
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].OrganizationalUnit,
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._OrganizationalUnit,
  
  // EHS Location Storage Location

  @ObjectModel.foreignKey.association: '_StorageLocation'
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].StorageLocation,
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._StorageLocation,

  // EHS Location Authorization Group

  @ObjectModel.foreignKey.association: '_EHSLocationAuthGroup'
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].EHSLocationAuthorizationGroup,
  _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._EHSLocationAuthGroup,

  // Chemical: Type

  @ObjectModel.foreignKey.association: '_ChemicalType'
  I_ChemicalRoot.ChemicalType,
  I_ChemicalRoot._ChemicalType,

  
  // Risk

  @ObjectModel.foreignKey.association: '_EHSRisk'
  EHSRiskUUID,
  _EHSRisk,
  
  // Risk Type 

  @ObjectModel.foreignKey.association: '_EHSRiskType'
  _EHSRisk[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._EHSRiskAnalysisStandard._EHSRiskAnalysis.EHSRiskType,
  _EHSRisk[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._EHSRiskAnalysisStandard._EHSRiskAnalysis._EHSRiskType,

  @ObjectModel.foreignKey.association: '_EHSRiskLevel'
  _EHSRisk[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._EHSRiskAnalysisStandard._EHSRiskAnalysis.EHSRiskLevel, 
  _EHSRisk[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._EHSRiskAnalysisStandard._EHSRiskAnalysis._EHSRiskLevel,

  // Risk Value 

  cast (
    case when
      _EHSRisk[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._EHSRiskAnalysisStandard._EHSRiskAnalysis.EHSRiskValue is null
    then   
      0 // "Not Assessed"

    else
      _EHSRisk[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._EHSRiskAnalysisStandard._EHSRiskAnalysis.EHSRiskValue  
    end
  as ehhss_risk_value preserving type ) as EHSRiskValue,

  // Risk: Method of Use

  @ObjectModel.foreignKey.association: '_EHSMethodOfUse'
  I_EHSRiskRoot.MethodOfUse,
  I_EHSRiskRoot._EHSMethodOfUse,

  // Operational Status

  @ObjectModel.foreignKey.association: '_EHSOperationalStatus'
  I_EHSRiskRoot.OperationalStatus,
  I_EHSRiskRoot._EHSOperationalStatus,

  // Hazard Category

  @ObjectModel.foreignKey.association: '_HazardCategory'
  I_EHSRiskRoot.HazardCategory,
  I_EHSRiskRoot._HazardCategory,
  
  // Job

  @ObjectModel.foreignKey.association: '_EHSJob'
  I_EHSRiskRoot.EHSJobUUID,
  I_EHSRiskRoot._EHSJob,
  
  // Risk Assessment Type

  _EHSRisk[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._EHSRiskAssessment.EHSRiskAssessmentType as EHSRiskAssessmentType,
  _EHSRisk[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date]._EHSRiskAssessment._EHSRiskAssessmentType as _EHSRiskAssessmentType
  
}
// consider chemical in process or released chemicals

where ( I_ChemicalRoot._ChemicalRevision[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].ChemicalStatus = '02'  
     or I_ChemicalRoot._ChemicalRevision[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].ChemicalStatus = '03' ) 
// Consider only active or inactive locations (to be precise: the current location revision revision must be either inactive or active)

and ( _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].EHSLocationStatus = '02'
   or _EHSLocation[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].EHSLocationStatus = '03' )   
// Consider only in process or released risks

and ( _EHSRisk[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].EHSRiskStatus  = '02' 
   or _EHSRisk[1: RevisionStartDate <= $session.system_date and RevisionEndDate >= $session.system_date].EHSRiskStatus  = '03' )
// ignore simulated risks   

and I_EHSRiskRoot.IsSimulatedEHSRisk = ' '