P_ProcOrdNxtOpAggr

@AbapCatalog.sqlViewName: 'PROCORDNXTOPAGGR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
//@EndUserText.label: 'Process order next operation'

@ObjectModel.usageType: {serviceQuality: #C, sizeCategory: #XL, dataClass: #TRANSACTIONAL}
@VDM.viewType:   #COMPOSITE
@VDM.private: true
@ClientHandling.algorithm: #SESSION_VARIABLE
// Calculate next operation based on operation number

define view P_ProcOrdNxtOpAggr
  as select from I_ProcOrdMgmtOpBasic as Op
  inner join P_ProcOrdAORListDistinct as _AORList on  _AORList.OrderID   = Op.ProcessOrder
  association [0..1] to P_ProcOrdLatestOpAggr as _ProcOrdLatestOp on  _ProcOrdLatestOp.MfgOrderInternalID   = Op.OrderInternalID
                                                                  and _ProcOrdLatestOp.ProcessOrder         = Op.ProcessOrder
                                                                  and _ProcOrdLatestOp.ProductionPlant      = Op.ProductionPlant
                                                                  and _ProcOrdLatestOp.ProductionSupervisor = Op.ProductionSupervisor

  //  association [0..1] to P_ProcOrdCurOpRelshpAggrgn as _ProcOrdCurOpRel on  _ProcOrdCurOpRel.MfgOrderInternalID   = Op.OrderInternalID

  //                                                                       and _ProcOrdCurOpRel.ProcessOrder         = Op.ProcessOrder

  //                                                                       and _ProcOrdCurOpRel.ProductionPlant      = Op.ProductionPlant

  //                                                                       and _ProcOrdCurOpRel.ProductionSupervisor = Op.ProductionSupervisor


  association [0..1] to P_ProcOrdFirstOpAggr  as _ProcOrdFirstOp  on  _ProcOrdFirstOp.MfgOrderInternalID   = Op.OrderInternalID
                                                                  and _ProcOrdFirstOp.ProcessOrder         = Op.ProcessOrder
                                                                  and _ProcOrdFirstOp.ProductionPlant      = Op.ProductionPlant
                                                                  and _ProcOrdFirstOp.ProductionSupervisor = Op.ProductionSupervisor
{
  key  Op.OrderInternalID as MfgOrderInternalID,
       Op.ProcessOrder,
       Op.ProductionPlant,
       Op.ProductionSupervisor,
       min(Op.Operation)  as NextOperation
}
where
  (
            Op.ManufacturingOrderCategory    = '40'
    and     Op.MfgOrderOperationIsPhase      = '' //phases are not relevant

    and     Op.Sequence                      = '000000' //only standard sequence

    and     Op.SuperiorOperationInternalID   = '00000000'
  )
  and(

            Op.OpActualExecutionStartDate    = '00000000' //orders not yet in pcnf or cnf or del status


    and(
      (
            _ProcOrdLatestOp.LatestOperation is not null
        and Op.Operation                     > _ProcOrdLatestOp.LatestOperation
      )
      //      or(

      //            _ProcOrdLatestOp.LatestOperation is null

      //        and _ProcOrdCurOpRel.CurOperation    is not null

      //        and Op.Operation                     > _ProcOrdCurOpRel.CurOperation

      //      )

      or(
            _ProcOrdLatestOp.LatestOperation is null
        //        and _ProcOrdCurOpRel.CurOperation    is null

        and _ProcOrdFirstOp.FirstOperation   is not null
        and Op.Operation                     > _ProcOrdFirstOp.FirstOperation
      )
    )
  )
group by
  Op.OrderInternalID,
  Op.ProcessOrder,
  Op.ProductionPlant,
  Op.ProductionSupervisor;
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PROCORDMGMTOPBASIC",
"P_PROCORDAORLISTDISTINCT",
"P_PROCORDFIRSTOPAGGR",
"P_PROCORDLATESTOPAGGR"
],
"ASSOCIATED":
[
"P_PROCORDFIRSTOPAGGR",
"P_PROCORDLATESTOPAGGR"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/