I_MDGovChgReqMappgTopWrkflw

@AbapCatalog.sqlViewName: 'IMDGCRWFMAPPG'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType: { sizeCategory: #XL, dataClass: #TRANSACTIONAL, serviceQuality: #C }
@VDM.viewType: #BASIC
@EndUserText.label: 'Mapping From Top Workitem to CR'
define view I_MDGovChgReqMappgTopWrkflw
  as select from usmd2400       as _cr_top_wi
    inner join   I_WorkflowTask as wi on  _cr_top_wi.top_wi_id = TopLevelWorkflowTask
                                      and wi.WorkflowTaskType  = 'W' //dialog task

  association [0..1] to I_CalendarDate as _ChgReqCreationDate   on $projection.MDGovChgReqWrkflwTskCrtnDte = _ChgReqCreationDate.CalendarDate
  association [0..1] to I_CalendarDate as _ChgReqReleaseDate    on $projection.MDGovChgReqWrkflwTskCompltnDte = _ChgReqReleaseDate.CalendarDate
{
  key wi.WorkflowTaskInternalID,
      wi.WorkflowTaskDefinition,
      wi.TopLevelWorkflowTask,
      wi.ParentWorkflowTask,
      wi.TopLevelWorkflowTaskDefinition,
      wi.WorkflowTaskType,
      cast (wi.WorkflowTaskStatus as mdg_ana_wrkflw_status ) as WorkflowTaskStatus,
      @Semantics.systemDate.createdAt: true
      @EndUserText: { label: 'Work Item Created On', quickInfo: 'Work Item Created On' }
      tstmp_to_dats( WrkflwTskCreationUTCDateTime,
                     abap_system_timezone( $session.client, 'NULL' ),
                     $session.client,
                     'NULL' )                                as MDGovChgReqWrkflwTskCrtnDte,

      @EndUserText: { label: 'Work Item Completed On', quickInfo: 'Work Item Completed On' }
      tstmp_to_dats( WrkflwTskCompletionUTCDateTime,
                     abap_system_timezone( $session.client, 'NULL' ),
                     $session.client,
                     'NULL' )                                as MDGovChgReqWrkflwTskCompltnDte,
      @EndUserText.label: 'Work Item Created Year'
      cast(substring( tstmp_to_dats( WrkflwTskCreationUTCDateTime,
                      abap_system_timezone( $session.client, 'NULL' ),
                      $session.client,
                      'NULL' ), 1, 4)  as abap.numc( 4 ) )   as MDGovChgReqWrkflwTskCrtnYear,
      @EndUserText.label: 'Work Item Completed Year'
      cast(substring( tstmp_to_dats( WrkflwTskCompletionUTCDateTime,
                      abap_system_timezone( $session.client, 'NULL' ),
                      $session.client,
                      'NULL' ), 1, 4) as abap.numc( 4 ) )    as MDGovWrkflwTskCompltnYear,
      @EndUserText.label: 'Work Item Completed Month'
      cast(substring( tstmp_to_dats( WrkflwTskCompletionUTCDateTime,
                abap_system_timezone( $session.client, 'NULL' ),
                $session.client,
      /*.Format of MDGovWrkflwTskCompltnMonth is YYYYMM.*/
            'NULL' ), 1, 6)  as vdm_yearmonth)               as MDGovWrkflwTskCompltnMonth,

      /*.Format of MDGovWrkflwTskCrtnMonth will be MM. As this is used already in OP 2020 app in this format*/
      @EndUserText.label: 'Work Item Created Month'
      cast( substring( tstmp_to_dats( WrkflwTskCreationUTCDateTime,
                abap_system_timezone( $session.client, 'NULL' ),
                $session.client,
            'NULL' ), 5, 2)  as abap.numc( 2 ) )             as MDGovChgReqWrkflwTskCrtnMonth,


      @EndUserText.label: 'Work Item Creation Time'
      tstmp_to_tims( WrkflwTskCreationUTCDateTime,
                abap_system_timezone( $session.client, 'NULL' ),
                $session.client,
            'NULL' )                                         as MDGovChgReqWrkflwTskCrtnTme,

      @EndUserText.label: 'Work Item Completion Time'
      tstmp_to_tims( WrkflwTskCompletionUTCDateTime,
                abap_system_timezone( $session.client, 'NULL' ),
                $session.client,
            'NULL' )                                         as MDGovChgReqWrkflwTskCompltnTme,

      wi.WrkflwTskCreationUTCDateTime,
      wi.WrkflwTskCompletionUTCDateTime,
      wi.WorkflowTaskCurrentUser,
      wi.WorkflowTaskCreatedByUser,
      _cr_top_wi.usmd_crequest                               as MDGovChangeRequest,

      cast (1 as abap.int4 )                                 as MDGovChgReqNmbrOfWrkflwTsks,
      case //:P_MDGovChgReqWrkflwScen when 'C'  then

        when  WrkflwTskCompletionUTCDateTime is not initial then
            division(tstmp_seconds_between(WrkflwTskCreationUTCDateTime, WrkflwTskCompletionUTCDateTime, 'INITIAL'), 3600 * 24, 1)
      else
          division(tstmp_seconds_between(WrkflwTskCreationUTCDateTime,tstmp_current_utctimestamp(), 'INITIAL'), 3600 * 24, 1)
      end                                                    as MDGovWrkflwTskAvgProcgDurn,

      case //:P_MDGovChgReqWrkflwScen when 'C'  then

      when  WrkflwTskCompletionUTCDateTime is not initial then
            division(tstmp_seconds_between(WrkflwTskCreationUTCDateTime, WrkflwTskCompletionUTCDateTime, 'INITIAL'), 3600, 1)
      else
          division(tstmp_seconds_between(WrkflwTskCreationUTCDateTime,tstmp_current_utctimestamp(), 'INITIAL'), 3600, 1)
      end                                                    as MDGovWrkflwTskAvgProcgHrs,

      case //:P_MDGovChgReqWrkflwScen when 'C'  then

      when  WrkflwTskCompletionUTCDateTime is not initial then
            division(tstmp_seconds_between(WrkflwTskCreationUTCDateTime, WrkflwTskCompletionUTCDateTime, 'INITIAL'), 60, 1)
      else
          division(tstmp_seconds_between(WrkflwTskCreationUTCDateTime,tstmp_current_utctimestamp(), 'INITIAL'), 60, 1)
      end                                                    as MDGovWrkflwTskAvgProcgMins,
      //Associations

      _ChgReqCreationDate,
      _ChgReqReleaseDate
}