P_InspOpAggr

@AbapCatalog.sqlViewName: 'PINSPOPAGGR'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #CONSUMPTION
define view P_InspOpAggr
  as select from    P_InspCharcAggr              as InspCharcAggr
    left outer join I_InspectionOperation        as InspOp                     on InspOp.InspectionLot = InspCharcAggr.InspectionLot
    left outer join I_InspOperationUsageDecision as InspOperationUsageDecision on  InspOperationUsageDecision.InspectionLot               = InspCharcAggr.InspectionLot
                                                                               and InspOperationUsageDecision.InspPlanOperationInternalID = InspOp.BOOOperationInternalID
{
  key InspCharcAggr.InspectionLot,
      InspCharcAggr.InspCharOpenCount,
      InspCharcAggr.InspCharAcceptedCount,
      InspCharcAggr.InspCharRejectedCount,
      sum( case
              when ( ( InspOperationUsageDecision.InspOpUsageDecisionValuation = 'A' or InspOperationUsageDecision.InspOpUsageDecisionValuation = 'R' ) and
                     InspCharcAggr.InspCharOpenCount > 0 and
                     InspCharcAggr.InspCharAcceptedCount = 0 and
                     InspCharcAggr.InspCharRejectedCount = 0 ) then 1
              when ( ( InspOperationUsageDecision.InspOpUsageDecisionValuation = '' or InspOperationUsageDecision.InspOpUsageDecisionValuation is null ) and
                     InspCharcAggr.InspCharRejectedCount = 0 ) then 1
              else  0
              end ) as InspOpNmbrOfOpen,
      sum( case
              when ( ( InspOperationUsageDecision.InspOpUsageDecisionValuation = 'R' or InspOperationUsageDecision.InspOpUsageDecisionValuation = 'A' or
                       InspOperationUsageDecision.InspOpUsageDecisionValuation = ''  or InspOperationUsageDecision.InspOpUsageDecisionValuation is null ) and
                     InspCharcAggr.InspCharRejectedCount > 0 ) then 1
              else  0
              end ) as InspOpNmbrOfRjctd,
      sum( case
            when ( ( InspOperationUsageDecision.InspOpUsageDecisionValuation = 'A' or InspOperationUsageDecision.InspOpUsageDecisionValuation = 'R' ) and
                   InspCharcAggr.InspCharAcceptedCount > 0 and
                   InspCharcAggr.InspCharRejectedCount = 0 ) then 1
            else 0
            end )   as InspOpNmbrOfAccepted,

      //------

      sum( case
              when ( InspOperationUsageDecision.InspOpUsageDecisionValuation = 'A' ) then 1
              else 0
              end ) as InspOpNmbrOfAcceptedCmpltd, //when completed operation is accepted

      sum( case
              when ( InspOperationUsageDecision.InspOpUsageDecisionValuation = 'R') then 1
              else 0
              end ) as InspOpNmbrOfRjctdCmpltd, //when completed operation is rejected

      sum( case
              when ( InspOperationUsageDecision.InspOpUsageDecisionValuation = '' or InspOperationUsageDecision.InspOpUsageDecisionValuation is null ) then 1
              else 0
              end ) as InspOpNmbrOfOpenCmpltd   //when valuation is blank

}
group by
  InspCharcAggr.InspectionLot,
  InspCharcAggr.InspCharOpenCount,
  InspCharcAggr.InspCharAcceptedCount,
  InspCharcAggr.InspCharRejectedCount
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_INSPECTIONOPERATION",
"I_INSPOPERATIONUSAGEDECISION",
"P_INSPCHARCAGGR"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/