@Analytics.dataCategory: #FACT
@ObjectModel.usageType: { serviceQuality: #D, sizeCategory: #M, dataClass: #TRANSACTIONAL }
@VDM.viewType: #COMPOSITE
@AbapCatalog.sqlViewName: 'IINSPCHARF'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Inspection Characteristic Fact'
define view I_InspectionCharacteristicFact
as
// All characteristic results which does not have inspection samples
select from I_InspectionCharacteristic as IC
inner join I_InspectionLot on IC.InspectionLot = I_InspectionLot.InspectionLot
left outer join I_InspectionResult on IC.InspectionLot = I_InspectionResult.InspectionLot
and IC.InspPlanOperationInternalID = I_InspectionResult.InspPlanOperationInternalID
and IC.InspectionCharacteristic = I_InspectionResult.InspectionCharacteristic
{
key IC.InspectionLot,
key IC.InspPlanOperationInternalID,
key IC.InspectionCharacteristic,
key ('000000') as InspectionSubsetInternalID,
//Further attributes of characteristic
IC._InspectionOperation.InspectionOperation,
IC.InspectionSpecificationPlant,
IC.InspectionSpecification,
IC.InspectionSpecificationVersion,
I_InspectionLot.BillOfOperationsType,
I_InspectionLot.BillOfOperationsGroup,
I_InspectionLot.BillOfOperationsUsage,
I_InspectionLot.BillOfOperationsVariant,
I_InspectionLot.BillOfOperationsChangeStateID,
I_InspectionLot.Material,
I_InspectionLot.Customer,
I_InspectionLot.Supplier,
I_InspectionLot.InspectionLotType,
I_InspectionLot.Plant,
I_InspectionLot.MatlQualityAuthorizationGroup,
IC.InspectionSpecificationUnit,
//Aggregates
@DefaultAggregation: #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,
@DefaultAggregation: #SUM
case when I_InspectionResult.InspectionValuationResult = 'A' then 1
when I_InspectionResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end as InspCharcAcceptedSampleCount,
@DefaultAggregation: #SUM
case when I_InspectionResult.InspectionValuationResult = 'R' then 1
when I_InspectionResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end as InspCharRejectedSampleCount,
@DefaultAggregation: #MIN
case when I_InspectionResult.InspResultHasMinimumValue = 'X' then
I_InspectionResult.InspectionResultMinimumValue
else I_InspectionResult.InspectionResultMeanValue end as InspectionResultMinimumValue,
@DefaultAggregation: #MAX
case when I_InspectionResult.InspResultHasMaximumValue = 'X' then
I_InspectionResult.InspectionResultMaximumValue
else I_InspectionResult.InspectionResultMeanValue end as InspectionResultMaximumValue,
@DefaultAggregation: #SUM
(I_InspectionResult.InspectionResultMeanValue * cast(I_InspectionResult.InspResultValidValuesNumber as abap.fltp))
as InspectionResultMeanValueSum,
@DefaultAggregation: #SUM
I_InspectionResult.InspResultValidValuesNumber,
@DefaultAggregation: #SUM
I_InspectionResult.InspRsltNonconformingValsNmbr
//CP, CPK siehe QRKT_MEAN_VALUE_SHEWHART
}
where
I_InspectionLot.InspLotHasSubsets = ' '
and IC.HasInspCharcPrtlSmpl = ' '
union all
//All Characteristics with multiple, independent samples
select from I_InspectionCharacteristic as IC
inner join I_InspectionLot on IC.InspectionLot = I_InspectionLot.InspectionLot
left outer join I_InspSubsetResult on IC.InspectionLot = I_InspSubsetResult.InspectionLot
and IC.InspPlanOperationInternalID = I_InspSubsetResult.InspPlanOperationInternalID
and IC.InspectionCharacteristic = I_InspSubsetResult.InspectionCharacteristic
{
key IC.InspectionLot,
key IC.InspPlanOperationInternalID,
key IC.InspectionCharacteristic,
key I_InspSubsetResult.InspectionSubsetInternalID,
//Further attributes of characteristic
IC._InspectionOperation.InspectionOperation,
IC.InspectionSpecificationPlant,
IC.InspectionSpecification,
IC.InspectionSpecificationVersion,
I_InspectionLot.BillOfOperationsType,
I_InspectionLot.BillOfOperationsGroup,
I_InspectionLot.BillOfOperationsUsage,
I_InspectionLot.BillOfOperationsVariant,
I_InspectionLot.BillOfOperationsChangeStateID,
I_InspectionLot.Material,
I_InspectionLot.Customer,
I_InspectionLot.Supplier,
I_InspectionLot.InspectionLotType,
I_InspectionLot.Plant,
I_InspectionLot.MatlQualityAuthorizationGroup,
IC.InspectionSpecificationUnit,
//Aggregates
@DefaultAggregation: #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,
@DefaultAggregation: #SUM
case when I_InspSubsetResult.InspectionValuationResult = 'A' then 1
when I_InspSubsetResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end as InspCharcAcceptedSampleCount,
@DefaultAggregation: #SUM
case when I_InspSubsetResult.InspectionValuationResult = 'R' then 1
when I_InspSubsetResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end as InspCharRejectedSampleCount,
@DefaultAggregation: #MIN
case when I_InspSubsetResult.InspResultHasMinimumValue = 'X' then
I_InspSubsetResult.InspectionResultMinimumValue
else I_InspSubsetResult.InspectionResultMeanValue end as InspectionResultMinimumValue,
@DefaultAggregation: #MAX
case when I_InspSubsetResult.InspResultHasMaximumValue = 'X' then
I_InspSubsetResult.InspectionResultMaximumValue
else I_InspSubsetResult.InspectionResultMeanValue end as InspectionResultMaximumValue,
@DefaultAggregation: #SUM
(I_InspSubsetResult.InspectionResultMeanValue * cast(I_InspSubsetResult.InspResultValidValuesNumber as abap.fltp))
as InspectionResultMeanValueSum,
@DefaultAggregation: #SUM
I_InspSubsetResult.InspResultValidValuesNumber,
@DefaultAggregation: #SUM
I_InspSubsetResult.InspRsltNonconformingValsNmbr
}
where
I_InspectionLot.InspLotHasSubsets = ' '
and IC.HasInspCharcPrtlSmpl = 'X'
union all
//Results for inspection points/subset
select from I_InspectionCharacteristic as IC
inner join I_InspectionLot on IC.InspectionLot = I_InspectionLot.InspectionLot
left outer join I_InspSubsetCharacteristic on IC.InspectionLot = I_InspSubsetCharacteristic.InspectionLot
and IC.InspPlanOperationInternalID = I_InspSubsetCharacteristic.InspPlanOperationInternalID
and IC.InspectionCharacteristic = I_InspSubsetCharacteristic.InspectionCharacteristic
{
key IC.InspectionLot,
key IC.InspPlanOperationInternalID,
key IC.InspectionCharacteristic,
key I_InspSubsetCharacteristic.InspectionSubsetInternalID,
//Further attributes of characteristic
IC._InspectionOperation.InspectionOperation,
IC.InspectionSpecificationPlant,
IC.InspectionSpecification,
IC.InspectionSpecificationVersion,
I_InspectionLot.BillOfOperationsType,
I_InspectionLot.BillOfOperationsGroup,
I_InspectionLot.BillOfOperationsUsage,
I_InspectionLot.BillOfOperationsVariant,
I_InspectionLot.BillOfOperationsChangeStateID,
I_InspectionLot.Material,
I_InspectionLot.Customer,
I_InspectionLot.Supplier,
I_InspectionLot.InspectionLotType,
I_InspectionLot.Plant,
I_InspectionLot.MatlQualityAuthorizationGroup,
IC.InspectionSpecificationUnit,
//Aggregates
@DefaultAggregation: #SUM
case when I_InspSubsetCharacteristic._InspSubsetResult.InspectionValuationResult is null or
I_InspSubsetCharacteristic._InspSubsetResult.InspectionValuationResult = ' ' then 1
when IC._InspectionResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end as InspCharOpenSampleCount,
@DefaultAggregation: #SUM
case when I_InspSubsetCharacteristic._InspSubsetResult.InspectionValuationResult = 'A' then 1
when IC._InspectionResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end as InspCharcAcceptedSampleCount,
@DefaultAggregation: #SUM
case when I_InspSubsetCharacteristic._InspSubsetResult.InspectionValuationResult = 'R' then 1
when IC._InspectionResult.InspectionValuationResult = '+' then 999999 //to force int4-type
else 0 end as InspCharRejectedSampleCount,
@DefaultAggregation: #MIN
case when I_InspSubsetCharacteristic._InspSubsetResult.InspResultHasMinimumValue = 'X' then
I_InspSubsetCharacteristic._InspSubsetResult.InspectionResultMinimumValue
else I_InspSubsetCharacteristic._InspSubsetResult.InspectionResultMeanValue end as InspectionResultMinimumValue,
@DefaultAggregation: #MAX
case when I_InspSubsetCharacteristic._InspSubsetResult.InspResultHasMaximumValue = 'X' then
I_InspSubsetCharacteristic._InspSubsetResult.InspectionResultMaximumValue
else I_InspSubsetCharacteristic._InspSubsetResult.InspectionResultMeanValue end as InspectionResultMaximumValue,
@DefaultAggregation: #SUM
(I_InspSubsetCharacteristic._InspSubsetResult.InspectionResultMeanValue *
cast(I_InspSubsetCharacteristic._InspSubsetResult.InspResultValidValuesNumber as abap.fltp))
as InspectionResultMeanValueSum,
@DefaultAggregation: #SUM
I_InspSubsetCharacteristic._InspSubsetResult.InspResultValidValuesNumber,
@DefaultAggregation: #SUM
I_InspSubsetCharacteristic._InspSubsetResult.InspRsltNonconformingValsNmbr
}
where
I_InspectionLot.InspLotHasSubsets = 'X'
and I_InspSubsetCharacteristic.InspectionSubsetInternalID <> '000000'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_INSPECTIONCHARACTERISTIC",
"I_INSPECTIONLOT",
"I_INSPECTIONOPERATION",
"I_INSPECTIONRESULT",
"I_INSPSUBSETCHARACTERISTIC",
"I_INSPSUBSETRESULT"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/