@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 = ' '
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CHEMICALREVISION",
"I_CHEMICALROOT",
"I_EHSLOCATION",
"I_EHSRISK",
"I_EHSRISKANALYSIS",
"I_EHSRISKANALYSISSTANDARD",
"I_EHSRISKASSESSMENT",
"I_EHSRISKROOT"
],
"ASSOCIATED":
[
"I_BUSINESSAREA",
"I_CHEMICAL",
"I_CHEMICALTYPE",
"I_COMPANYCODE",
"I_CONTROLLINGAREA",
"I_COUNTRY",
"I_EHSJOB",
"I_EHSLOCATION",
"I_EHSLOCATIONAUTHZNGROUP",
"I_EHSLOCATIONTYPE",
"I_EHSMETHODOFUSE",
"I_EHSOPERATIONALSTATUS",
"I_EHSRISK",
"I_EHSRISKASSESSMENTTYPE",
"I_EHSRISKLEVEL",
"I_EHSRISKTYPE",
"I_HAZARD",
"I_HAZARDCATEGORY",
"I_ORGUNIT",
"I_PLANT",
"I_STORAGELOCATION"
],
"BASE":
[
"I_CHEMICALROOT",
"I_EHSLOCATION",
"I_EHSRISKANALYSIS",
"I_EHSRISKASSESSMENT",
"I_EHSRISKROOT"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/