P_InspSubsetCharcAgg

@AbapCatalog.sqlViewName: 'PINSSUBCHAGG'
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #COMPOSITE
@VDM.private: true

define view P_InspSubsetCharcAgg
  as select from           I_InspSubsetCharacteristic as InspSubsetCharacteristic
    left outer to one join I_InspectionSubset         as InspectionSubset on  InspSubsetCharacteristic.InspectionLot               = InspectionSubset.InspectionLot
                                                                          and InspSubsetCharacteristic.InspectionSubsetInternalID  = InspectionSubset.InspectionSubsetInternalID
                                                                          and InspSubsetCharacteristic.InspPlanOperationInternalID = InspectionSubset.InspPlanOperationInternalID
    left outer to one join I_InspSubsetResult         as InspSubsetResult on  InspSubsetCharacteristic.InspectionLot               = InspSubsetResult.InspectionLot
                                                                          and InspSubsetCharacteristic.InspPlanOperationInternalID = InspSubsetResult.InspPlanOperationInternalID
                                                                          and InspSubsetCharacteristic.InspectionCharacteristic    = InspSubsetResult.InspectionCharacteristic
                                                                          and InspSubsetCharacteristic.InspectionSubsetInternalID  = InspSubsetResult.InspectionSubsetInternalID
  //  association[0..1] to I_InspectionResultStatus as _InspectionResultStatus on $projection.inspectionresultstatus = _InspectionResultStatus.InspectionResultStatus

{
  key InspSubsetCharacteristic.InspectionLot,
  key InspSubsetCharacteristic.InspPlanOperationInternalID,
  key InspSubsetCharacteristic.InspectionCharacteristic,
      //  key InspSubsetCharacteristic.InspectionSubsetInternalID,

      sum( case
        when InspectionValuationResult = 'A' then 1
        else 0 end ) as InspCharAcceptedCount,
      sum( case
        when InspectionValuationResult = 'R' then 1
        else 0 end ) as InspCharRejectedCount,
      
      
     InspSubsetCharacteristic._InspectionLot.Plant,
     InspSubsetCharacteristic._InspectionLot.InspectionLotType,
     InspSubsetCharacteristic._InspectionLot.InspectionSubsetType,
     InspSubsetCharacteristic._InspectionLot.MatlQualityAuthorizationGroup,
     InspSubsetCharacteristic._InspectionLot.IsBusinessPurposeCompleted,
     InspSubsetCharacteristic._InspectionOperation._WorkCenter.WorkCenter     
      //      InspectionCharacteristicStatus,

      //      InspectionResultMeanValue,

      //      InspectionValuationResult,

      //      InspectionResultStatus,

      ////      _InspectionResultStatus._Text[1:Language=$session.system_language].InspectionResultStatusText,

      //      InspSubsetUsageDcsnValuation,

      //      InspectionSubsetDate,

      //      InspectionSubsetTime

}
where
     InspectionSubset.InspSubsetUsageDcsnValuation = 'A'
  or InspectionSubset.InspSubsetUsageDcsnValuation = 'R'
group by
  InspSubsetCharacteristic.InspectionLot,
  InspSubsetCharacteristic.InspPlanOperationInternalID,
  InspSubsetCharacteristic.InspectionCharacteristic,
  InspSubsetCharacteristic._InspectionLot.Plant,
  InspSubsetCharacteristic._InspectionLot.InspectionLotType,
  InspSubsetCharacteristic._InspectionLot.InspectionSubsetType,
  InspSubsetCharacteristic._InspectionLot.MatlQualityAuthorizationGroup,
  InspSubsetCharacteristic._InspectionLot.IsBusinessPurposeCompleted,
  InspSubsetCharacteristic._InspectionOperation._WorkCenter.WorkCenter