I_EHSTaskPlannedInstancesAll

@AbapCatalog.sqlViewName: 'IEHSTASKPLANAL'
@AbapCatalog.preserveKey:true
@AbapCatalog.compiler.compareFilter:true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #('TRANSACTIONAL_DATA')
@ClientHandling.algorithm: #SESSION_VARIABLE
@EndUserText.label: 'EHS Planned and Executed Task Instances'

@ObjectModel.usageType:{ serviceQuality: #C,
                         sizeCategory:  #XL,
                         dataClass: #MIXED }
@VDM.viewType: #BASIC
define view I_EHSTaskPlannedInstancesAll
  as select from           I_EHSTaskPlannedInstance as EHSTaskPlannedInstance
    inner join             I_EHSTaskDefinition      as EHSTaskDefinition    on EHSTaskPlannedInstance.EHSTaskDefinitionUUID = EHSTaskDefinition.EHSTaskDefinitionUUID
    left outer to one join P_EHSWorkflowTask        as WorkflowTask         on  WorkflowTask.WorkflowTaskInternalID = EHSTaskPlannedInstance.WorkflowTaskInternalID
                                                                            and WorkflowTask.WorkflowTaskType       = 'F' //only WorkflowTasker items

  association [1..1] to I_BusinessUser              as _EHSTaskOwnerUser    on  $projection.EHSTaskOwnerBP = _EHSTaskOwnerUser.BusinessPartner
  association [0..1] to I_EHSTaskPriority           as _EHSTaskPriority     on  $projection.EHSTaskPriority = _EHSTaskPriority.EHSTaskPriority

  association [1..1] to I_EHSTaskAdapterClass       as _EHSTaskAdapterClass on  $projection.EHSTaskHostObjectAdapterClass = _EHSTaskAdapterClass.EHSTaskAdapterClass
{
  key EHSTaskPlannedInstance.EHSPlannedTaskUUID,
      EHSTaskPlannedInstance.EHSTaskDefinitionUUID             as EHSTaskDefinitionUUID,

      EHSTaskPlannedInstance.WorkflowTaskInternalID,

      EHSTaskPlannedInstance.EHSTaskStartDate,
      EHSTaskPlannedInstance.EHSTaskDueDate,
      EHSTaskPlannedInstance.EHSTaskInstceCompletionDate,
      EHSTaskPlannedInstance.EHSTaskTimeZone,

      // USERS

      EHSTaskPlannedInstance.EHSTaskOwnerBP,
      EHSTaskPlannedInstance.EHSTaskAssignedBP,
      EHSTaskPlannedInstance.EHSTaskApprovingBP,

      // TASK DEFINITION

      EHSTaskDefinition.EHSTaskTitle,
      EHSTaskDefinition.EHSTaskWorkflowEvent                   as EHSTaskDefinitionType,

      EHSTaskDefinition.EHSTaskPriority,
      EHSTaskDefinition.EHSTaskDefinitionStatus,
      EHSTaskDefinition.EHSTaskDefinitionIsPaused,
      EHSTaskDefinition.EHSTaskRecurrenceType,
      _EHSTaskDefinitionExecution.EHSTaskNextExecUTCDateTime,
      EHSTaskDefinition.EHSLocationUUID,

      // HOST OBJECT

      EHSTaskDefinition._EHSTaskAdapterClass.EHSSemanticObject as EHSTaskHostSemanticObject,
      EHSTaskDefinition.EHSTaskHostObjectInstanceUUID,
      EHSTaskDefinition.EHSTaskHostObjectInstance,
      EHSTaskDefinition.EHSTaskHostObjectAdapterClass,


      //Workflow

      // STATUS CODE

      cast( case WorkflowTask.WorkflowTaskStatus
       when 'ERROR' then cast( '01' as ehfnd_task_calendar_status)
       when 'COMPLETED' then cast( '05' as ehfnd_task_calendar_status)
       when 'CANCELLED' then cast( '06' as ehfnd_task_calendar_status)
       when 'STARTED' then
         case
          when EHSTaskPlannedInstance.EHSTaskDueDate < tstmp_to_dats(tstmp_current_utctimestamp(), EHSTaskTimeZone, $session.client, 'INITIAL') then cast( '02' as ehfnd_task_calendar_status)
          when EHSTaskPlannedInstance.EHSTaskDueDate >= tstmp_to_dats(tstmp_current_utctimestamp(), EHSTaskTimeZone, $session.client, 'INITIAL') then cast( '03' as ehfnd_task_calendar_status)
          else '00'
         end
       else case
         when EHSTaskPlannedInstance.WorkflowTaskInternalID is initial then '00'
         else case
           when EHSTaskPlannedInstance.EHSTaskInstceCompletionDate is initial then '06'
           else '05'
         end
       end
      end     as ehfnd_task_calendar_status)                   as WorkflowTaskStatus,
      WorkflowTask.WorkflowTaskStatus as EHSTaskStatus,


      //association exposure:

      _EHSTaskOwnerUser,
      _EHSTaskPriority,
      EHSTaskDefinition.EHSTaskAssignedUser,
      EHSTaskDefinition._EHSTaskAssignedUser,

      _EHSTaskAdapterClass

}