I_EHSCmplncAnlysLocTypeTaskC

@AbapCatalog.sqlViewName: 'IEHSCALTTC'
@AbapCatalog.compiler.compareFilter: true
@ClientHandling.algorithm: #SESSION_VARIABLE

@AccessControl:
{
  authorizationCheck: #MANDATORY,
  // blocking of personal data not required (user data can not be blocked and no BP info from user exposed)

  personalData.blocking: #NOT_REQUIRED
}

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

                         sizeCategory:  #XL,         // < 10.000.000

                         dataClass: #MIXED }
@EndUserText.label: 'Compl Analysis Cube of Tasks by Loc type'

@Analytics: { dataCategory:#CUBE , dataExtraction.enabled:true , internalName:#LOCAL }
@VDM.viewType: #COMPOSITE
@Metadata.allowExtensions:true

define view I_EHSCmplncAnlysLocTypeTaskC
  as select distinct from I_EHSTaskCalendar as EHSTaskCalendar
    inner join            I_EHSLocationHierarchyRevision on  I_EHSLocationHierarchyRevision.RevisionStartDate <= $session.system_date
                                                         and I_EHSLocationHierarchyRevision.RevisionEndDate   >= $session.system_date
    inner join            I_EHSLocationHierarchyNode     on  I_EHSLocationHierarchyNode.EHSLocationUUID             = EHSTaskCalendar.EHSLocationUUID
                                                         and I_EHSLocationHierarchyNode.EHSLocHierarchyRevisionUUID = I_EHSLocationHierarchyRevision.EHSLocHierarchyRevisionUUID

  association [0..*] to I_EHSLocation as _EHSLocation on  $projection.EHSLocationUUID    = _EHSLocation.EHSLocationUUID
                                                      and _EHSLocation.RevisionStartDate <= $session.system_date
                                                      and _EHSLocation.RevisionEndDate   >= $session.system_date
                                                      
  association [0..1] to  I_EHSCmplncRqmtIdHex as _CmplRqUUIDHEX on $projection.EHSTaskHostObjectInstance = _CmplRqUUIDHEX.CmplRqUUIDHex
  association [0..1] to  I_LocationDescFallbackLang  as _LocTextDesc on $projection.EHSLocationUUID = _LocTextDesc.EHSLocationUUID                                                      
{
  key        WorkflowTaskInternalID,
  key        EHSTaskDefinitionUUID,
  key        EHSTaskDueDate,
  
             EHSTaskHostObjectInstance,
             EHSTaskCalendar.EHSTaskHostObjectInstanceUUID,

             EHSTaskStatus                             as EHSTaskStatusValueForSorting,
             

             EHSTaskCalendar.EHSLocationUUID,
             bintohex(EHSTaskCalendar.EHSLocationUUID) as EHSLocationUUIDHex,
             _EHSTaskAdapterClass.EHSSemanticObject    as EHSTaskHostSemanticObject,

             @DefaultAggregation: #SUM
             cast( 1 as ehfnd_integer )                as EHSNumberOfTasks,

             // EHS Location Type

             @ObjectModel.foreignKey.association: '_EHSLocationType'
             _EHSLocation.EHSLocationType,
             _EHSLocation._EHSLocationType,

             @Semantics.text: true
             @ObjectModel.readOnly: true
             case
              when _LocTextDesc._LocationNameText.EHSLocationName is null
                then bintohex( EHSTaskCalendar.EHSLocationUUID )
              else _LocTextDesc._LocationNameText.EHSLocationName
             end                                       as EHSLocationName,
             
                          @Semantics.text: true
             @ObjectModel.readOnly: true
             case
//Shorten to 60 chars for better visibility on the chart

              when _CmplRqUUIDHEX.OutputLengthValue >= 60 then concat( substring(_CmplRqUUIDHEX.CmplRqVersName, 1, 60), '...' )
              else cast(_CmplRqUUIDHEX.CmplRqVersName as abap.char(250))
             end                                                     as CmplRqVersName,


             // Fields for authorization check

             EHSTaskCalendar.EHSTaskHostObjectAdapterClass,
             EHSTaskCalendar.EHSTaskOwnerBP,
             _CmplncRqmtAuthznFields,
             _EHSCmplncScenAuthznFields,
             _EHSCtrlImplmtnAuthznFields,
             _EHSRskAssessmentAuthznFields,
             _EHSTaskAdapterClass,
             _EHSTaskOwnerUser,
             _IncidentAuthorizationFields,
             IncidentHasAccessRestriction,
             _EHSLocation
}
where
  // cancelled tasks should be excluded for the analysis

      EHSTaskStatus                          <> '06'
  // Semantic object is needed to prevent the case of exctraction of inconsistent tasks with no semantic object

  and _EHSTaskAdapterClass.EHSSemanticObject =  'EHSComplianceRequirement'
union select distinct from I_EHSTaskCalendar
association [0..*] to I_OTRText     as _OTRText     on  _OTRText.Language = $session.system_language
association [0..*] to I_EHSLocation as _EHSLocation on  $projection.EHSLocationUUID    = _EHSLocation.EHSLocationUUID
                                                    and _EHSLocation.RevisionStartDate <= $session.system_date
                                                    and _EHSLocation.RevisionEndDate   >= $session.system_date
association [0..1] to  I_EHSCmplncRqmtIdHex as _CmplRqUUIDHEX on $projection.EHSTaskHostObjectInstance = _CmplRqUUIDHEX.CmplRqUUIDHex   
{
  key        WorkflowTaskInternalID,
  key        EHSTaskDefinitionUUID,
  key        EHSTaskDueDate,
  
             EHSTaskHostObjectInstance,
             EHSTaskHostObjectInstanceUUID,
             EHSTaskStatus                                                                                           as EHSTaskStatusValueForSorting,

             I_EHSTaskCalendar.EHSLocationUUID,
             bintohex(I_EHSTaskCalendar.EHSLocationUUID)                                                             as EHSLocationUUIDHex,

             _EHSTaskAdapterClass.EHSSemanticObject                                                                  as EHSTaskHostSemanticObject,

             @DefaultAggregation: #SUM
             cast( 1 as ehfnd_integer )                                                                              as EHSNumberOfTasks,

             // EHS Location Type

             @ObjectModel.foreignKey.association: '_EHSLocationType'
             _EHSLocation.EHSLocationType,
             _EHSLocation._EHSLocationType,

             @Semantics.text: true
             @ObjectModel.readOnly: true
             cast( _OTRText[1: OnlnTxtRpstryConceptID = '005056B252761ED3908E2F1A948BC0A9'].Text as ehfnd_loc_name ) as EHSLocationName,

                          @Semantics.text: true
             @ObjectModel.readOnly: true
             case
//Shorten to 60 chars for better visibility on the chart

              when _CmplRqUUIDHEX.OutputLengthValue >= 60 then concat( substring(_CmplRqUUIDHEX.CmplRqVersName, 1, 60), '...' )
              else cast(_CmplRqUUIDHEX.CmplRqVersName as abap.char(250))
             end                                                     as CmplRqVersName,

             // Fields for authorization check

             I_EHSTaskCalendar.EHSTaskHostObjectAdapterClass,
             I_EHSTaskCalendar.EHSTaskOwnerBP,
             _CmplncRqmtAuthznFields,
             _EHSCmplncScenAuthznFields,
             _EHSCtrlImplmtnAuthznFields,
             _EHSRskAssessmentAuthznFields,
             _EHSTaskAdapterClass,
             _EHSTaskOwnerUser,
             _IncidentAuthorizationFields,
             IncidentHasAccessRestriction,
             _EHSLocation
}
where
      EHSLocationUUID                        =  hextobin(
                                                        '00000000000000000000000000000000'
                            )
  // cancelled tasks should be excluded for the analysis

  and EHSTaskStatus                          <> '06'
  // Semantic object is needed to prevent the case of exctraction of inconsistent tasks with no semantic object

  and _EHSTaskAdapterClass.EHSSemanticObject =  'EHSComplianceRequirement'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_EHSCMPLNCRQMTIDHEX",
"I_EHSLOCATION",
"I_EHSLOCATIONHIERARCHYNODE",
"I_EHSLOCATIONHIERARCHYREVISION",
"I_EHSLOCATIONREVISIONNAMET",
"I_EHSTASKADAPTERCLASS",
"I_EHSTASKCALENDAR",
"I_LOCATIONDESCFALLBACKLANG",
"I_OTRTEXT"
],
"ASSOCIATED":
[
"I_BUSINESSUSERBASIC",
"I_CMPLNCRQMTAUTHZNFIELDS",
"I_EHSCMPLNCRQMTIDHEX",
"I_EHSCMPLNCSCENAUTHZNFIELDS",
"I_EHSCTRLIMPLMTNAUTHZNFIELDS",
"I_EHSLOCATION",
"I_EHSLOCATIONTYPE",
"I_EHSRSKASSESSMENTAUTHZNFIELDS",
"I_EHSTASKADAPTERCLASS",
"I_INCIDENTCOMBAUTHFIELDS",
"I_LOCATIONDESCFALLBACKLANG"
],
"BASE":
[
"I_EHSLOCATION",
"I_EHSTASKCALENDAR"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/