I_EHSCOMBINEDTASKDEFANDINSTCE

@AbapCatalog.sqlViewName: 'IEHSTASKDEFINSTN'
@VDM.viewType: #COMPOSITE
@AbapCatalog.compiler.compareFilter: true

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

                         sizeCategory:  #XL,         // < 10.000.000

                         dataClass: #MIXED }

@AccessControl.authorizationCheck: #MANDATORY
// #NOT_REQUIRED as a start

@AccessControl.personalData.blocking: #NOT_REQUIRED
@EndUserText.label: 'Task Instances and Future Definitions'

define view I_EHSCombinedTaskDefAndInstce
  as select from I_EHSTaskPlannedInstance           as  PlannedInstance
    left outer to one join P_EHSWorkflowHeader      as  WorkflowTask
                                                    on  PlannedInstance.WorkflowTaskInternalID = WorkflowTask.WorkflowTaskInternalID

  association [0..1] to I_BusinessUser              as  _EHSTaskAssignedUser
                                                    on  PlannedInstance.EHSTaskAssignedBP = _EHSTaskAssignedUser.BusinessPartner
  association [0..1] to I_EHSTaskPriority           as  _EHSTaskPriority 
                                                    on  $projection.EHSTaskPriority = _EHSTaskPriority.EHSTaskPriority
  association [0..*] to I_OTRText as _OTRText on _OTRText.Language = $session.system_language
  
{
  key PlannedInstance.EHSPlannedTaskUUID,
      PlannedInstance.EHSTaskDefinitionUUID,
      PlannedInstance.WorkflowTaskInternalID,
      
//    WORKFLOW INFORMATION

      WorkflowTask.WorkflowTaskStatus,
      case
        when WorkflowTask.WorkflowTaskInternalID is null then cast ( '' as ehfnd_task_has_workflow preserving type )
        else cast ( 'X' as ehfnd_task_has_workflow preserving type )
      end                                                                                         as EHSTaskHasWorkflow,
      WorkflowTask.WorkflowTaskDefinition,

//    HOST OBJECT

      PlannedInstance._EHSTaskDefinition._EHSTaskAdapterClass.EHSSemanticObject                   as EHSTaskHostSemanticObject,
      PlannedInstance._EHSTaskDefinition.EHSTaskHostObjectAdapterClass,
      PlannedInstance._EHSTaskDefinition.EHSTaskHostObjectInstance,
      PlannedInstance._EHSTaskDefinition.EHSTaskHostObjectInstanceUUID,

//    TASK DEFINITION

      PlannedInstance._EHSTaskDefinition.EHSTaskWorkflowEvent                                     as EHSTaskDefinitionType,      
      PlannedInstance._EHSTaskDefinition.EHSTaskDefinitionStatus,
      PlannedInstance._EHSTaskDefinition.EHSTaskDefinitionIsPaused,

      coalesce( cast(WorkflowTask.WorkflowTaskPriority as ehfnd_task_def_priority_code preserving type ),
                PlannedInstance._EHSTaskDefinition.EHSTaskPriority )                              as EHSTaskPriority,
      PlannedInstance._EHSTaskDefinition.EHSTaskRecurrenceType,

//    DATE & TIME

      PlannedInstance.EHSTaskStartDate,
      PlannedInstance.EHSTaskDueDate,
      WorkflowTask.WrkflwTskCompletionUTCDateTime,
      PlannedInstance.EHSTaskInstceCompletionDate,

//    USERS

      cast ( '' as bu_partner )                                                                   as EHSTaskUser,
      PlannedInstance._EHSTaskOwnerUser.UserID                                                    as EHSTaskOwnerUser,
      PlannedInstance.EHSTaskInstanceProcessorBP,
      PlannedInstance.EHSTaskAssignedBP,
      PlannedInstance._EHSTaskAssigneeUser.PersonFullName                                         as EHSTaskAssignedUserName,
      PlannedInstance.EHSTaskApprovingBP,
      PlannedInstance.EHSTaskOwnerBP,

//    Roles      

      PlannedInstance.EHSTaskAssignedLocRole,
      PlannedInstance.EHSTaskApproverLocRole,

//    DATA

                                         
            

      cast( coalesce( replace(WorkflowTask.WorkflowTaskName, concat_with_space( 
                                                                         concat(
                                                                                 concat_with_space( ' /', 
                                                                                                    _OTRText[1: OnlnTxtRpstryConceptID = '80215A5C32181DEF8E9BBBFAF5718652'].Text, 1
                                                                                                  ),
                                                                                 ': '
                                                                                ),
                                                                         PlannedInstance.EHSPlannedTaskID, 1
                                                                       ), ''), PlannedInstance._EHSTaskDefinition.EHSTaskTitle  ) as ehfnd_task_definition_title    )  as EHSTaskTitle,
      
      

//    SORTING

      case
        when PlannedInstance.WorkflowTaskInternalID is not initial then
          case WorkflowTask.WorkflowTaskStatus
            when 'ERROR' then cast( '01' as ehfnd_task_sorting_status_nc)
            when 'COMPLETED' then cast( '05' as ehfnd_task_sorting_status_nc)
            when 'CANCELLED' then cast( '06' as ehfnd_task_sorting_status_nc)
            when 'STARTED' then
              case
                when PlannedInstance.EHSTaskDueDate < tstmp_to_dats(tstmp_current_utctimestamp(), PlannedInstance.EHSTaskTimeZone, $session.client, 'INITIAL') then cast( '02' as ehfnd_task_sorting_status_nc)
                when PlannedInstance.EHSTaskDueDate >= tstmp_to_dats(tstmp_current_utctimestamp(), PlannedInstance.EHSTaskTimeZone, $session.client, 'INITIAL') then cast( '03' as ehfnd_task_sorting_status_nc)
                else cast( '00' as ehfnd_task_sorting_status_nc)
              end
            else case
              when WorkflowTask.WorkflowTaskInternalID is null then
                case
                  when PlannedInstance.EHSTaskInstceCompletionDate is initial then cast( '06' as ehfnd_task_sorting_status_nc ) //Archived Incomplete -> status Cancelled

                  else cast( '05' as ehfnd_task_sorting_status_nc ) //Archived Completed -> status Completed

                end
              else cast( '00' as ehfnd_task_sorting_status_nc)
            end
          end
        else case PlannedInstance._EHSTaskDefinition.EHSTaskDefinitionIsPaused
          when 'X' then cast( '01' as ehfnd_task_sorting_status_nc)
          else cast( '04' as ehfnd_task_sorting_status_nc)
        end
      end                                                                                         as EHSTaskStatusValueForSorting,
      case
        when PlannedInstance.WorkflowTaskInternalID is not initial then
          case
            when WorkflowTask.WorkflowTaskStatus = 'COMPLETED' then '99991231'
            when WorkflowTask.WorkflowTaskStatus = 'CANCELLED' then '99991231'
            else PlannedInstance.EHSTaskDueDate
          end
        else PlannedInstance.EHSTaskStartDate
      end                                                                                         as EHSTaskDateForSorting,

      PlannedInstance._EHSTaskDefinition.EHSLocationUUID,
      PlannedInstance.EHSTaskTimeZone,
      
      PlannedInstance._EHSTaskDefinition._EHSTaskAdapterClass,
      PlannedInstance._EHSTaskOwnerUser,
      _EHSTaskAssignedUser,
      PlannedInstance._EHSTaskDefinition,
      
      PlannedInstance._EHSTaskDefinition._EHSTaskLearning.EHSLearningUUID as EHSLearningUUID,
      
//    Associations for Authorization Check of Host BO

      PlannedInstance._EHSTaskDefinition._IncidentAuthorizationFields,
      PlannedInstance._EHSTaskDefinition._EHSCtrlImplmtnAuthznFields,
      PlannedInstance._EHSTaskDefinition._EHSRskAssessmentAuthznFields,
      PlannedInstance._EHSTaskDefinition._EHSRskAssmt2RskCtrlAuthznFld,
      PlannedInstance._EHSTaskDefinition._EHSCmplncScenAuthznFields,
      PlannedInstance._EHSTaskDefinition._CmplncRqmtAuthznFields,
      _EHSTaskPriority,
      PlannedInstance._EHSTaskDefinition._IncidentTaskRefAccRestricted.IncidentHasAccessRestriction,
      PlannedInstance.EHSPlannedTaskID
}