P_OperationActivityWorklist

@AbapCatalog.sqlViewName: 'PMPEOAIWRKLST'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey:true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType: {
  serviceQuality: #D,
  sizeCategory:   #L,
  dataClass:      #MIXED
}
@VDM: {
  private: true,
  viewType: #COMPOSITE
}
define view P_OperationActivityWorklist
  as select from           I_OpActyInstceOpActyNtwkElmnt as OpActyInstceOpActyNtwkElmnt
    inner join             I_OpActyInstanceStatusBasic   as OpActyInstanceStatusBasic   on  OpActyInstanceStatusBasic.OpActyNtwkInstance = OpActyInstceOpActyNtwkElmnt.OpActyNtwkInstance
                                                                                        and OpActyInstanceStatusBasic.OpActyNtwkElement  = OpActyInstceOpActyNtwkElmnt.OpActyNtwkElement
    inner join             I_OrderOperationDateTime      as OrderOperationDateTime      on  OrderOperationDateTime.OrderInternalID          = OpActyInstceOpActyNtwkElmnt.OrderInternalID
                                                                                        and OrderOperationDateTime.OrderOperationInternalID = OpActyInstceOpActyNtwkElmnt.OrderOperationInternalID
    left outer to one join I_WorkCenter                  as WorkCenter                  on  WorkCenter.WorkCenterTypeCode   = OrderOperationDateTime.WorkCenterTypeCode
                                                                                        and WorkCenter.WorkCenterInternalID = OrderOperationDateTime.WorkCenterInternalID
  // Every OA just has to be shown once in the Worklist. Thus,

    left outer to one join P_NrOfOpActyUserAssgmnts      as NrOfOpActyUserAssgmnts      on  NrOfOpActyUserAssgmnts.OpActyNtwkInstance = OpActyInstceOpActyNtwkElmnt.OpActyNtwkInstance
                                                                                        and NrOfOpActyUserAssgmnts.OpActyNtwkElement  = OpActyInstceOpActyNtwkElmnt.OpActyNtwkElement
    left outer to one join P_NrOfOpActyTeamAssgmnts      as NrOfOpActyTeamAssgmnts      on  NrOfOpActyTeamAssgmnts.OpActyNtwkInstance = OpActyInstceOpActyNtwkElmnt.OpActyNtwkInstance
                                                                                        and NrOfOpActyTeamAssgmnts.OpActyNtwkElement  = OpActyInstceOpActyNtwkElmnt.OpActyNtwkElement
  // Needed to identify whether the session user is assigned to the Operation Activity

    left outer to one join I_OpActyUserAssgmtBasic       as OpActySessionUserAssgmt     on  OpActySessionUserAssgmt.OpActyNtwkInstance = OpActyInstceOpActyNtwkElmnt.OpActyNtwkInstance
                                                                                        and OpActySessionUserAssgmt.OpActyNtwkElement  = OpActyInstceOpActyNtwkElmnt.OpActyNtwkElement
                                                                                        and OpActySessionUserAssgmt.UserID             = $session.user

  association [0..*] to I_OpActyUserAssgmt        as _OpActyUserAssgmt        on  $projection.OpActyNtwkInstance = _OpActyUserAssgmt.OpActyNtwkInstance
                                                                              and $projection.OpActyNtwkElement  = _OpActyUserAssgmt.OpActyNtwkElement
  association [1..1] to I_User                    as _User                    on  $projection.UserID = _User.UserID
  association [1..1] to I_Plant                   as _ProductionPlant         on  $projection.ProductionPlant = _ProductionPlant.Plant
  association [0..1] to I_WorkCenterBySemanticKey as _WorkCenterBySemanticKey on  $projection.WorkCenter      = _WorkCenterBySemanticKey.WorkCenter
                                                                              and $projection.ProductionPlant = _WorkCenterBySemanticKey.Plant
  association [0..1] to I_WorkCenterType          as _WorkCenterType          on  $projection.WorkCenterTypeCode = _WorkCenterType.WorkCenterTypeCode
{
  key OpActyInstceOpActyNtwkElmnt.OpActyNtwkInstance,
  key OpActyInstceOpActyNtwkElmnt.OpActyNtwkElement,
      cast(case
        when OpActySessionUserAssgmt.UserID is not null then OpActySessionUserAssgmt.UserID
        else NrOfOpActyUserAssgmnts.UserID
      end as mpe_user preserving type)                             as UserID,
      cast(case
        when OpActySessionUserAssgmt.MfgOpActyExecutionPriority is not null then OpActySessionUserAssgmt.MfgOpActyExecutionPriority
        else NrOfOpActyUserAssgmnts.MfgOpActyExecutionPriority
      end as mpe_oa_exec_prio preserving type)                     as MfgOpActyExecutionPriority,
      OpActyInstceOpActyNtwkElmnt.OpActyNtwkElementElement,
      OpActyInstceOpActyNtwkElmnt.OpActyNtwkElementElmntVersCntr,
      OpActyInstceOpActyNtwkElmnt.ManufacturingOrder,
      OrderOperationDateTime.Operation                             as ManufacturingOrderOperation,
      OrderOperationDateTime.OperationText                         as MfgOrderOperationText,
      WorkCenter.WorkCenter,
      WorkCenter.WorkCenterInternalID,
      WorkCenter.WorkCenterTypeCode,
      OrderOperationDateTime.Plant                                 as ProductionPlant,
      StatusAndActionSchema,
      StatusAndActionSchemaStatus,
      OpActyInstanceStatusBasic._SASStatus.SASStatusCategory,
      OpActyInstceOpActyNtwkElmnt.ProductionUnit,
      OpActyInstceOpActyNtwkElmnt.OpPlannedYieldQuantity,
      OpActyInstceOpActyNtwkElmnt.OpPlannedScrapQuantity,
      OpActyInstceOpActyNtwkElmnt.OpActyExpdExecDurnInSeconds,
      OpActyInstceOpActyNtwkElmnt.OpActyExpdExecLaborDurnInSecs,
      OpActyInstceOpActyNtwkElmnt.OpActyNtwkElementExternalID,
      OpActyInstceOpActyNtwkElmnt.OperationActivityNetwork,
      OpActyInstceOpActyNtwkElmnt.OpActyNtwkVersionCounter,
      OpActyInstceOpActyNtwkElmnt.OpActyNtwkSegmentType,
      OpActyInstceOpActyNtwkElmnt.OpActyConfIsSFIBased,
      OpActyInstceOpActyNtwkElmnt.OperationActivity,
      OpActyInstceOpActyNtwkElmnt.OperationActyVersionCounter,
      OpActyInstanceStatusBasic.OpActyIsSeldForRtactvPostg,
      //      CreatedByUser,

      //      CreationDateTime,

      cast(
        coalesce(NrOfOpActyUserAssgmnts.NrOfOpActyUserAssignments, 0)
        as mpe_exec_usr_assgnmts preserving type )                 as NrOfOpActyUserAssignments,
      cast(
        coalesce(NrOfOpActyTeamAssgmnts.NrOfOpActyTeamAssignments, 0)
        as mpe_exec_team_assgnmts preserving type )                as NrOfOpActyTeamAssignments,
      cast(
        coalesce(NrOfOpActyTeamAssgmnts.RespyMgmtTeamID, '')
        as mpe_team_id )                                           as RespyMgmtTeamID,
      OpActyInstceOpActyNtwkElmnt.OrderInternalID,
      OpActyInstceOpActyNtwkElmnt.OrderOperationInternalID,

      //_MfgOrderOpAdaptedDates

      //Scheduled Timestamps

      cast( case OpActyNtwkSegmentType
        when '1' then
          OrderOperationDateTime.OpLtstSchedldExecStrtDteTme
        when '2' then
          OrderOperationDateTime.OpLtstSchedldProcgStrtDteTme
        else
          OrderOperationDateTime.OpLtstSchedldTrdwnStrtDteTme
      end as mpe_start_time preserving type )                      as OpLtstSchedldExecStrtDteTme,

      cast( case OpActyNtwkSegmentType
        when '1' then
          OrderOperationDateTime.OpLtstSchedldProcgStrtDteTme
        when '2' then
          OrderOperationDateTime.OpLtstSchedldTrdwnStrtDteTme
        else
          OrderOperationDateTime.OpLtstSchedldExecEndDteTme
      end as mpe_start_time preserving type )                      as OpLtstSchedldExecEndDteTme,

      //Actual Timestamps

      cast( case OpActyNtwkSegmentType
        when '1' then
          OrderOperationDateTime.OpActualExecutionStartDateTime
        when '2' then
          OrderOperationDateTime.OpActualProcessingStartDteTme
        else
          OrderOperationDateTime.OpActualTeardownStartDateTime
      end as mpe_start_time preserving type )                      as OpActualExecutionStartDateTime,

      cast( case OpActyNtwkSegmentType
        when '1' then
          OrderOperationDateTime.OpActualSetupEndDateTime
        when '2' then
          OrderOperationDateTime.OpActualProcessingEndDateTime
        else
          OrderOperationDateTime.OpActualExecutionEndDateTime
      end as mpe_start_time preserving type )                      as OpActualExecutionEndDateTime,

      /* Associations */
      OpActyInstceOpActyNtwkElmnt._OpActyNtwkInstance,
      OpActyInstceOpActyNtwkElmnt._OperationActivityText,
      _User,
      OpActyInstceOpActyNtwkElmnt._MfgOrder,
      _OpActyNtwkInternalId,
      _OperationActivityNetwork,
      _SASSchema,
      _UnitOfMeasure,
      _SASStatus,
      _ProductionPlant,
      _WorkCenterBySemanticKey,
      _WorkCenterType,
      _OpActyUserAssgmt
}
where
  OpActyInstceOpActyNtwkElmnt.OpActyConfirmationIsAllowed = 'X'