I_DefectKeyFigCube

@AbapCatalog : {
  buffering.status: #NOT_ALLOWED,
  compiler.compareFilter: true,
  sqlViewName: 'IDEFECTKEYFIG'
}
@AccessControl: {
  authorizationCheck: #CHECK,
  personalData.blocking: #REQUIRED
}
@Analytics: {
  dataCategory: #CUBE
}
@ClientHandling.algorithm: #SESSION_VARIABLE
@EndUserText.label: 'Key Figure view for Defect Analytics'
@Metadata.ignorePropagatedAnnotations: true
@Metadata.allowExtensions:true
@ObjectModel: {
    usageType: {
        dataClass: #TRANSACTIONAL,
        sizeCategory: #L,
        serviceQuality: #C },
    representativeKey: 'DefectInternalID',
    semanticKey:  [ 'Defect' ]
}

@VDM.viewType: #COMPOSITE
@VDM.lifecycle.contract.type: #SAP_INTERNAL_API
define view I_DefectKeyFigCube
  with parameters
    P_StartDate : mm_a_delivery_date,
    P_EndDate   : mm_a_delivery_date
  as select from    I_DefectUnion
  // geting supplier and customer details for non notification defects

    left outer join I_InspectionLot       on I_DefectUnion.InspectionLot = I_InspectionLot.InspectionLot

    left outer join I_InspectionOperation on  I_DefectUnion.InspectionLot               = I_InspectionOperation.InspectionLot
                                          and I_DefectUnion.InspPlanOperationInternalID = I_InspectionOperation.InspPlanOperationInternalID
  //for geting master inspection characteristics and inspection characteristics

  association [0..1] to I_InspectionCharacteristic     as _InspectionCharacteristic     on  $projection.InspectionLot               = _InspectionCharacteristic.InspectionLot
                                                                                        and $projection.InspPlanOperationInternalID = _InspectionCharacteristic.InspPlanOperationInternalID
                                                                                        and $projection.InspectionCharacteristic    = _InspectionCharacteristic.InspectionCharacteristic
  //  association [1..1] to I_InspectionOperation      as _IOperation               on  $projection.InspectionLot               = _IOperation.InspectionLot

  //                                                                                and $projection.InspPlanOperationInternalID = _IOperation.InspPlanOperationInternalID

  association [0..1] to I_WorkCenterBySemanticKey      as _WorkCenterBySemanticKey      on  $projection.MainWorkCenterInternalID = _WorkCenterBySemanticKey.WorkCenterInternalID
                                                                                        and $projection.MainWorkCenterPlant      = _WorkCenterBySemanticKey.Plant
  association [1..*] to I_InspSpecificationVersionText as _InspSpecificationVersionText on  $projection.inspectionspecificationplant   = _InspSpecificationVersionText.InspectionSpecificationPlant
                                                                                        and $projection.inspectionspecification        = _InspSpecificationVersionText.InspectionSpecification
                                                                                        and $projection.inspectionspecificationversion = _InspSpecificationVersionText.InspectionSpecificationVersion

{
  key DefectInternalID,
      I_DefectUnion.InspectionLot,
      I_DefectUnion.InspPlanOperationInternalID,
      InspectionCharacteristic,

      DefectCategory,
      CreationDate,
      CreationTime,
      DefectCode,
      DefectCodeGroup,
      WorkCenterTypeCode,
      MainWorkCenterInternalID,
      MainWorkCenterPlant,
      _InspectionCharacteristic.InspectionSpecificationText,
      _WorkCenterBySemanticKey.WorkCenter,
      _WorkCenterBySemanticKey._WorkCenterTextBySemanticKey[1:Language=$session.system_language].WorkCenterText,
      _InspectionCharacteristic.InspectionSpecificationPlant,
      _InspectionCharacteristic.InspectionSpecification,
      _InspectionCharacteristic.InspectionSpecificationVersion,
      I_InspectionLot.BillOfOperationsType,
      I_InspectionLot.BillOfOperationsGroup,
      I_InspectionOperation.BOOOperationInternalID,

      I_DefectUnion.Plant,
      I_DefectUnion._InspectionLot.Supplier,
      I_DefectUnion.Material,
      I_DefectUnion._InspectionLot.Customer,
      I_DefectUnion.DefectLifecycleStatus,
      //      case

      //          when(DefectCategory = '' or DefectCategory < '06')

      //            then

      //                   I_DefectUnion.Plant

      //          when(DefectCategory <> '' or (DefectCategory = '' and I_DefectUnion.Plant = '') )

      //              then

      //                   I_InspectionLot.Plant

      //          when(DefectCategory = '' and I_DefectUnion.Plant <> '')

      //              then

      //                   I_DefectUnion.Plant

      //          end                                                                                                                                                                                                                                                                                                                                                                                                          as               Plant,

      //

      //      case

      //          when(DefectCategory <> '' or (DefectCategory = '' and I_DefectUnion.Supplier = '') )

      //              then

      //                   I_InspectionLot.Supplier

      //          when(DefectCategory = '' and I_DefectUnion.Supplier <> '')

      //              then

      //                   I_DefectUnion.Supplier

      //          end                                                                                                                                                                                                                                                                                                                                                                                                          as               Supplier,

      //

      //      case

      //          when(DefectCategory <> '' or (DefectCategory = '' and I_DefectUnion.Material = '') )

      //              then

      //                  I_InspectionLot.Material

      //          when(DefectCategory = '' and I_DefectUnion.Material <> '')

      //              then

      //                  I_DefectUnion.Material

      //          end                                                                                                                                                                                                                                                                                                                                                                                                          as               Material,

      //

      //      case

      //          when(DefectCategory <> '' or (DefectCategory = '' and I_DefectUnion.Customer = '') )

      //              then

      //                 I_InspectionLot.Customer

      //          when(DefectCategory = '' and I_DefectUnion.Customer <> '')

      //              then

      //                 I_DefectUnion.Customer

      //          end                                                                                                                                                                                                                                                                                                                                                                                                          as               Customer,


      //Inspection Characteristics

      //Combination of Task List Type, Task List Group, Group Counter, Operation & Characteristic

      concat(concat(concat(concat(concat( concat ( concat (concat( I_InspectionOperation.BillOfOperationsType, '-'), ltrim( I_InspectionOperation.BillOfOperationsGroup , '0')  ), '-'), I_InspectionOperation.BillOfOperationsVariant), '-'), I_InspectionOperation.InspectionOperation), '-'), I_DefectUnion.InspectionCharacteristic) as InspResultHistoryCharc,

      //Master Inspection Characterisitic

      //Combination of Master Inspection Characteristic, Plant & Version

      concat( concat ( concat (concat( _InspectionCharacteristic.InspectionSpecification, '-'), _InspectionCharacteristic.InspectionSpecificationPlant), '-'), ltrim(_InspectionCharacteristic.InspectionSpecificationVersion, '0') )                                                                                                    as MasterInspCharcsKey,

      cast( 1 as vdm_numberofdefects )                                                                                                                                                                                                                                                                                                   as NumberOfDefects,

      case
          when(DefectCategory = '')
              then
                  cast( 1 as vdm_defectrecdwthnotifcnt )
          else
                  cast( 0 as vdm_defectrecdwthnotifcnt )
          end                                                                                                                                                                                                                                                                                                                            as NumberOfDefectsWithNotif,

      case
          when(DefectCategory <> '')
              then
                  cast( 1 as vdm_defectrecdwthoutnotifcnt )
          else
                  cast( 0 as vdm_defectrecdwthoutnotifcnt )
          end                                                                                                                                                                                                                                                                                                                            as NumberOfDefectsWithoutNotif,

      // fields for filter when navigating from QMIS-characteristics

      InspectionLotOrigin,
      InspectionLotType,

      //fields for DCL

      Notification,
      NotificationItem,
      MatlQualityAuthorizationGroup,
      IsBusinessPurposeCompleted,
      MainWorkCenter,

      // Associations

      _InspectionCharacteristic,
      //test

      _WorkCenterBySemanticKey,
      _DefectCode,
      _DefectCodeGroup,
      _DefectCategory,
      // _DefectCategoryText,

      //assosciation for DCL's

      _Notification,
      _InspSpecificationVersionText,
      I_DefectUnion._InspectionLot
}
where
      CreationDate >= $parameters.P_StartDate
  and CreationDate <= $parameters.P_EndDate
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_DEFECTUNION",
"I_INSPECTIONCHARACTERISTIC",
"I_INSPECTIONLOT",
"I_INSPECTIONOPERATION",
"I_WORKCENTERBYSEMANTICKEY",
"I_WORKCENTERTEXTBYSEMANTICKEY"
],
"ASSOCIATED":
[
"I_DEFECTCATEGORY",
"I_DEFECTCODE",
"I_DEFECTCODEGROUP",
"I_INSPECTIONCHARACTERISTIC",
"I_INSPECTIONLOT",
"I_INSPSPECIFICATIONVERSIONTEXT",
"I_NOTIFICATION",
"I_WORKCENTERBYSEMANTICKEY"
],
"BASE":
[
"I_DEFECTUNION"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/