@AbapCatalog.sqlViewName: 'IINSPCHARCAGGR'
@AbapCatalog.preserveKey: true
@ObjectModel.usageType: { serviceQuality: #D, sizeCategory: #M, dataClass: #TRANSACTIONAL }
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Aggregation on inspection characteristic level'
@VDM.viewType: #COMPOSITE
define view I_InspCharacteristicAggregate
as
// All characteristic results which does not have inspection samples
select from I_InspectionCharacteristic
inner join I_InspectionLot on I_InspectionCharacteristic.InspectionLot = I_InspectionLot.InspectionLot
inner join I_InspectionOperation on I_InspectionCharacteristic.InspectionLot = I_InspectionOperation.InspectionLot
and I_InspectionCharacteristic.InspPlanOperationInternalID = I_InspectionOperation.InspPlanOperationInternalID
left outer join I_InspectionResult on I_InspectionCharacteristic.InspectionLot = I_InspectionResult.InspectionLot
and I_InspectionCharacteristic.InspPlanOperationInternalID = I_InspectionResult.InspPlanOperationInternalID
and I_InspectionCharacteristic.InspectionCharacteristic = I_InspectionResult.InspectionCharacteristic
{
key I_InspectionCharacteristic.InspectionLot,
key I_InspectionCharacteristic.InspPlanOperationInternalID,
key I_InspectionCharacteristic.InspectionCharacteristic,
//Further attributes of characteristic
I_InspectionOperation.InspectionOperation,
//Aggregates
sum(case when I_InspectionResult.InspectionValuationResult is null or
I_InspectionResult.InspectionValuationResult = ' ' or
I_InspectionResult.InspectionValuationResult = 'F' then 1
when I_InspectionResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end) as InspCharOpenSampleCount,
sum(case when I_InspectionResult.InspectionValuationResult = 'A' then 1
when I_InspectionResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end) as InspCharcAcceptedSampleCount,
sum(case when I_InspectionResult.InspectionValuationResult = 'R' then 1
when I_InspectionResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end) as InspCharRejectedSampleCount
}
where
I_InspectionLot.InspLotHasSubsets = ' '
and I_InspectionCharacteristic.HasInspCharcPrtlSmpl = ' '
group by
I_InspectionCharacteristic.InspectionLot,
I_InspectionCharacteristic.InspPlanOperationInternalID,
I_InspectionOperation.InspectionOperation,
I_InspectionCharacteristic.InspectionCharacteristic
union all
//All Characteristics with multiple, independent samples
select from I_InspectionCharacteristic
inner join I_InspectionLot on I_InspectionCharacteristic.InspectionLot = I_InspectionLot.InspectionLot
inner join I_InspectionOperation on I_InspectionCharacteristic.InspectionLot = I_InspectionOperation.InspectionLot
and I_InspectionCharacteristic.InspPlanOperationInternalID = I_InspectionOperation.InspPlanOperationInternalID
left outer join I_InspSubsetResult on I_InspectionCharacteristic.InspectionLot = I_InspSubsetResult.InspectionLot
and I_InspectionCharacteristic.InspPlanOperationInternalID = I_InspSubsetResult.InspPlanOperationInternalID
and I_InspectionCharacteristic.InspectionCharacteristic = I_InspSubsetResult.InspectionCharacteristic
{
key I_InspectionCharacteristic.InspectionLot,
key I_InspectionCharacteristic.InspPlanOperationInternalID,
key I_InspectionCharacteristic.InspectionCharacteristic,
//Further attributes of characteristic
I_InspectionOperation.InspectionOperation,
//Aggregates
sum(case when I_InspSubsetResult.InspectionValuationResult is null or
I_InspSubsetResult.InspectionValuationResult = ' ' or
I_InspSubsetResult.InspectionValuationResult = 'F' then 1
when I_InspSubsetResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end) as InspCharOpenSampleCount,
sum(case when I_InspSubsetResult.InspectionValuationResult = 'A' then 1
when I_InspSubsetResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end) as InspCharcAcceptedSampleCount,
sum(case when I_InspSubsetResult.InspectionValuationResult = 'R' then 1
when I_InspSubsetResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end) as InspCharRejectedSampleCount
}
where
I_InspectionLot.InspLotHasSubsets = ' '
and I_InspectionCharacteristic.HasInspCharcPrtlSmpl = 'X'
group by
I_InspectionCharacteristic.InspectionLot,
I_InspectionCharacteristic.InspPlanOperationInternalID,
I_InspectionOperation.InspectionOperation,
I_InspectionCharacteristic.InspectionCharacteristic
union all
//Results for inspection points/subset
select from I_InspectionCharacteristic
inner join I_InspectionLot on I_InspectionCharacteristic.InspectionLot = I_InspectionLot.InspectionLot
inner join I_InspectionOperation on I_InspectionCharacteristic.InspectionLot = I_InspectionOperation.InspectionLot
and I_InspectionCharacteristic.InspPlanOperationInternalID = I_InspectionOperation.InspPlanOperationInternalID
left outer join I_InspSubsetCharacteristic on I_InspectionCharacteristic.InspectionLot = I_InspSubsetCharacteristic.InspectionLot
and I_InspectionCharacteristic.InspPlanOperationInternalID = I_InspSubsetCharacteristic.InspPlanOperationInternalID
and I_InspectionCharacteristic.InspectionCharacteristic = I_InspSubsetCharacteristic.InspectionCharacteristic
{
key I_InspectionCharacteristic.InspectionLot,
key I_InspectionCharacteristic.InspPlanOperationInternalID,
key I_InspectionCharacteristic.InspectionCharacteristic,
I_InspectionOperation.InspectionOperation,
//Aggregates
sum(case when I_InspSubsetCharacteristic._InspSubsetResult.InspectionValuationResult is null or
I_InspSubsetCharacteristic._InspSubsetResult.InspectionValuationResult = ' ' then 1
when I_InspectionCharacteristic._InspectionResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end) as InspCharOpenSampleCount,
sum(case when I_InspSubsetCharacteristic._InspSubsetResult.InspectionValuationResult = 'A' then 1
when I_InspectionCharacteristic._InspectionResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end) as InspCharcAcceptedSampleCount,
sum(case when I_InspSubsetCharacteristic._InspSubsetResult.InspectionValuationResult = 'R' then 1
when I_InspectionCharacteristic._InspectionResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end) as InspCharRejectedSampleCount
}
where
I_InspectionLot.InspLotHasSubsets = 'X'
and I_InspSubsetCharacteristic.InspectionSubsetInternalID <> '000000'
group by
I_InspectionCharacteristic.InspectionLot,
I_InspectionCharacteristic.InspPlanOperationInternalID,
I_InspectionOperation.InspectionOperation,
I_InspectionCharacteristic.InspectionCharacteristic