I_SESWorkflowStatus

@AbapCatalog.sqlViewName: 'ISESWFSTATUS'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@EndUserText.label: 'Approval Status of Service Entry Sheet'
@VDM.viewType: #COMPOSITE

@ObjectModel: {
   representativeKey: 'ServiceEntrySheet',
   usageType.serviceQuality: #D,
   usageType.sizeCategory: #L,
   usageType.dataClass: #TRANSACTIONAL
}

define view I_SESWorkflowStatus
  //Determination of all SES with a Workitem

  as select distinct from  I_ServiceEntrySheetWorkItem as Document
    left outer to one join I_SESWorkItemBasic          as Basic on  Document.ServiceEntrySheet           = Basic.ServiceEntrySheet
                                                                and Document.WrkflwTskChangedUTCDateTime = Basic.WrkflwTskChangedUTCDateTime
{
  key Document.ServiceEntrySheet,
      case
      //SES not yet sent for approval or just sent, but Workflow not yet started

          when
          Basic.WorkflowTaskInternalID is null and
          ( Basic.ApprovalStatus = '10' or Basic.ApprovalStatus = '20' )
            then cast( '10' as mmpur_ses_workflow_status)
      //SES just sent for approval and workflow just started (workitem exists but not in inbox yet)

                when Basic.WorkflowTaskStatus = 'STARTED' or Basic.WorkflowTaskStatus = 'WAITING'
                  then cast( '10' as mmpur_ses_workflow_status)
      //SES was revoked and the last Workitem is completed through the approval

      //or SES was canceled and the Woritem was not updated yet (One of several approver has already approved the SES)

                when Basic.WorkflowTaskInternalID is not null and Basic.ApprovalStatus = '10'
                and ( Basic.WorkflowTaskStatus = 'COMPLETED' or Basic.WorkflowTaskStatus = 'COMMITTED' )
                  then cast( '10' as mmpur_ses_workflow_status)
      //Waiting for approval decision in my inbox

                when Basic.WorkflowTaskStatus = 'READY' or Basic.WorkflowTaskStatus = 'SELECTED'
                  and Basic.ApprovalStatus = '20'
                  then cast( '20' as mmpur_ses_workflow_status)
      //Multi-Approver: Maybe most up to date workitem is completed, but not all relevant workitems are completed yet

                when ( Basic.WorkflowTaskStatus = 'COMPLETED' or Basic.WorkflowTaskStatus = 'COMMITTED' )
                  and Basic.ApprovalStatus = '20'
                  then cast( '20' as mmpur_ses_workflow_status)
      //SES was canceled and send again for approval, but Workitem was not updated yet

                when Basic.WorkflowTaskInternalID is not null and Basic.ApprovalStatus = '20'
                  and Basic.WorkflowTaskStatus = 'CANCELLED'
                  then cast( '20' as mmpur_ses_workflow_status)
      //SES was created externally and is waiting for (external) approval decision

                when Basic.WorkflowTaskInternalID is null and Basic.ApprovalStatus = '21'
                  then cast( '21' as mmpur_ses_workflow_status)
      //Approval decision done, SES approved (according to task: manually)

                when ( Basic.WorkflowTaskStatus = 'COMPLETED' or Basic.WorkflowTaskStatus = 'COMMITTED' )
                      and Basic.ApprovalStatus = '30' and Basic.WorkflowTaskDefinition = 'TS00800593'
                  then cast( '30' as mmpur_ses_workflow_status)
      //SES was directly created in status approved (no workitem and therefore no approval decision was necessary)

                when Basic.WorkflowTaskInternalID is null and Basic.ApprovalStatus = '30'
                  then cast( '30' as mmpur_ses_workflow_status)
      //Approval decision done, SES approved (according to task: automatically)

                when ( Basic.WorkflowTaskStatus = 'COMPLETED' or Basic.WorkflowTaskStatus = 'COMMITTED' )
                      and Basic.ApprovalStatus = '30' and Basic.WorkflowTaskDefinition = 'TS00800596'
                  then cast( '31' as mmpur_ses_workflow_status)
      //Approval decision done, SES rejected

                when ( Basic.WorkflowTaskStatus = 'COMPLETED' or Basic.WorkflowTaskStatus = 'COMMITTED' ) and Basic.ApprovalStatus = '40'
                  then cast( '40' as mmpur_ses_workflow_status)
      //SES was created externally and rejected via (external) decision

                when Basic.WorkflowTaskInternalID is null and Basic.ApprovalStatus = '40'
                  then cast( '40' as mmpur_ses_workflow_status)
      //SES was withdrawn from approval

                when Basic.WorkflowTaskStatus = 'CANCELLED'
                  then cast( '50' as mmpur_ses_workflow_status)
      //SES was canceled and the Workitem has the not yet updated the status

                when Basic.WorkflowTaskInternalID is not null and Basic.ApprovalStatus = '10'
                and ( Basic.WorkflowTaskStatus = 'READY'
                  or Basic.WorkflowTaskStatus = 'ERROR' or Basic.WorkflowTaskStatus = 'EXCPCAUGHT' or Basic.WorkflowTaskStatus = 'EXCPHANDLR' )
                  then cast( '50' as mmpur_ses_workflow_status)
      //Error in workflow occurred (probably during creation of goods receipt)

                when Basic.WorkflowTaskStatus = 'ERROR' or Basic.WorkflowTaskStatus = 'EXCPCAUGHT' or Basic.WorkflowTaskStatus = 'EXCPHANDLR'
                  then cast( '80' as mmpur_ses_workflow_status)
          end as SESWorkflowStatus
}

union all

//Determination of all SES created via API in status 'In Process (10)' or was deleted via API

select from I_ServiceEntrySheetBasic as Document
{
  key Document.ServiceEntrySheet,
      cast( '10' as mmpur_ses_workflow_status) as SESWorkflowStatus
}
where
       Document.OriginObjectType = 'EX'
  and(
       Document.ApprovalStatus   = '10'
    or Document.ApprovalStatus   = '60'
  )

union all

//Determination of all SES created via API in status 'In external Approval (21)'

select from I_ServiceEntrySheetBasic as Document
{
  key Document.ServiceEntrySheet,
      cast( '21' as mmpur_ses_workflow_status) as SESWorkflowStatus
}
where
      Document.ApprovalStatus   = '21'
  and Document.OriginObjectType = 'EX'

union all

//Determination of all SES created via Transportation Management

select from I_ServiceEntrySheetBasic as Document
{
  key Document.ServiceEntrySheet,
      case
      //Transportation Management: Creation or Deletion

      when Document.ApprovalStatus = '10' or Document.ApprovalStatus = '60'
      then cast( '10' as mmpur_ses_workflow_status)
      //Transportation Management: Creation of Goods Receipt was successfull

      when Document.ApprovalStatus = '30'
      then cast( '30' as mmpur_ses_workflow_status)
      end as SESWorkflowStatus
}
where
  Document.OriginObjectType = 'TM'

union all

//Determination of all SES created via Timesheet

select distinct from I_SESTimeSheetLogBasic as Document
{
  key Document.ServiceEntrySheet,
      case
      //Timesheet: Deletion

      when Document.ApprovalStatus = '10'
      then cast( '10' as mmpur_ses_workflow_status)
      //Timesheet: Creation of Goods Receipt (successfully) ongoing

        when Document.ApprovalStatus = '25'
        and Document.NumberOfErrorLogMessages is null
          then cast( '20' as mmpur_ses_workflow_status)
      //Timesheet: Creation of Goods Receipt was successfull

        when Document.ApprovalStatus = '30'
          then cast( '30' as mmpur_ses_workflow_status)
      //Timesheet: According to application log, error during goods receipt creation

        when ( Document.ApprovalStatus = '25' or Document.ApprovalStatus = '30' )
        and Document.NumberOfErrorLogMessages > '000000'
          then cast( '70' as mmpur_ses_workflow_status)
       end as SESWorkflowStatus
}