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

@Consumption.dbHints: [ 'USE_HEX_PLAN' ]

define view I_EHSCmplncAnlysLocTypeTaskC
  as select from           I_EHSTaskCalendar          as EHSTaskCalendar
  
  left outer join        I_OTRText                  as _OTRText       on  _OTRText.Language               = $session.system_language
                                                                        and _OTRText.OnlnTxtRpstryConceptID = '005056B252761ED3908E2F1A948BC0A9'

  left outer join        I_LocationDescFallbackLang as _LocTextDesc   on EHSTaskCalendar.EHSLocationUUID = _LocTextDesc.EHSLocationUUID

  left outer to one join I_EHSCmplncRqmtIdHex       as _CmplRqUUIDHEX on EHSTaskCalendar.EHSTaskHostObjectInstanceUUID = _CmplRqUUIDHEX.CmplRqUUID

  association [0..*] to I_EHSLocation as _EHSLocation on  $projection.EHSLocationUUID    = _EHSLocation.EHSLocationUUID
                                                      and _EHSLocation.RevisionStartDate <= $session.system_date
                                                      and _EHSLocation.RevisionEndDate   >= $session.system_date
{
  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
             cast(
               coalesce(
                 _LocTextDesc._LocationNameText.EHSLocationName,
                 _OTRText.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

             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'