I_QualityDefectUnion

@AbapCatalog.sqlViewName: 'IQLTYDFCTUNION'
@ClientHandling.algorithm: #SESSION_VARIABLE
@EndUserText.label: 'Quality Defect with Independent Defect'
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #CHECK
@ObjectModel: {
    usageType: {
        dataClass: #TRANSACTIONAL,
        sizeCategory: #S, // Relevant is I_NotificationItem, not I_Notification

        serviceQuality: #B },
    representativeKey: 'NotificationItem'
}

define view I_QualityDefectUnion as select from I_NotificationItem
   left outer join I_Notification // left outer join + where condition ensures that only items of quality notifications are selected

      on  I_Notification.Notification = I_NotificationItem.Notification
   association [0..1] to I_Defect              as _ReferencedDefect            on  $projection.ReferencedDefect = _ReferencedDefect.DefectInternalID
   association [0..1] to I_InspectionLot       as _InspectionLot               on  $projection.InspectionLot = _InspectionLot.InspectionLot
   association [0..1] to I_DefectCodeGroup     as _DefectCodeGroup             on  $projection.DefectCodeGroup   = _DefectCodeGroup.DefectCodeGroup
   association [0..1] to I_DefectCode          as _DefectCode                  on  $projection.DefectCodeGroup   = _DefectCode.DefectCodeGroup
                                                                               and $projection.DefectCode        = _DefectCode.DefectCode  
{
   
   @ObjectModel: { foreignKey.association: '_Notification' , readOnly: 'EXTERNAL_CALCULATION' }
   key I_NotificationItem.Notification, 
   @ObjectModel.readOnly: 'EXTERNAL_CALCULATION'
   key I_NotificationItem.NotificationItem, 
   
       @ObjectModel.readOnly: true
       I_NotificationItem.CreatedByUser,
       @ObjectModel.readOnly: true 
       I_NotificationItem.CreationDate, 
       @ObjectModel.readOnly: true
       I_NotificationItem.LastChangedByUser, 
       @ObjectModel.readOnly: true
       I_NotificationItem.LastChangeDate, 
       @ObjectModel.readOnly: 'EXTERNAL_CALCULATION'
       I_NotificationItem.NotificationItemText, 
       @ObjectModel.readOnly: 'EXTERNAL_CALCULATION'
       I_NotificationItem.DefectCodeCatalog, 
       @ObjectModel: { readOnly: 'EXTERNAL_CALCULATION' , foreignKey.association: '_DefectCodeGroup' }
       cast( I_NotificationItem.DefectCodeGroup as vdm_qfegrp preserving type )  as DefectCodeGroup, 
       @ObjectModel: { readOnly: 'EXTERNAL_CALCULATION' , foreignKey.association: '_DefectCode' }
       cast( I_NotificationItem.DefectCode      as vdm_qfecod preserving type )  as DefectCode, 
       @ObjectModel.readOnly: 'EXTERNAL_CALCULATION'
       I_NotificationItem.NotifItmObjectPartCodeCtlg, 
       @ObjectModel.readOnly: 'EXTERNAL_CALCULATION'
       I_NotificationItem.NotifItmObjectPartCodeGroup, 
       @ObjectModel.readOnly: 'EXTERNAL_CALCULATION'
       I_NotificationItem.NotifItmObjectPartCode, 
       @ObjectModel.readOnly: true
       I_NotificationItem.Plant,
       @ObjectModel.readOnly: true
       I_NotificationItem.DefectCategory,
       @ObjectModel.readOnly: true
       cast( I_NotificationItem.CreationTime        as vdm_qerstezeit preserving type )    as CreationTime, 
       @ObjectModel.readOnly: true
       cast( I_NotificationItem.LastChangeTime      as vdm_qaendezeit preserving type )    as LastChangeTime, 
       @ObjectModel.readOnly: true
       I_NotificationItem.IsDeleted, 
       @ObjectModel: { foreignKey.association: '_ReferencedDefect' , readOnly: true }
       I_NotificationItem.ReferencedDefect,
       @ObjectModel.readOnly: true
       I_NotificationItem.ChangedDateTime,
       @ObjectModel.readOnly: true
       I_NotificationItem.InspPlanOperationInternalID,  
       @ObjectModel.readOnly: true
       I_NotificationItem.InspectionCharacteristic, 
       @ObjectModel.readOnly: true

       case I_NotificationItem.DefectCategory
          when ' ' then I_NotificationItem._Notification.InspectionLot
          else I_NotificationItem.InspectionLot
       end as InspectionLot,
       
       @ObjectModel.readOnly: true
       I_NotificationItem.NumberOfDefects,

//     Work center fields (required for authorization checks)       

       @ObjectModel.readOnly: true
       I_NotificationItem.WorkCenterTypeCode,
       @ObjectModel.readOnly: true
       I_NotificationItem.MainWorkCenterInternalID,
       @ObjectModel.readOnly: true
       I_NotificationItem.MainWorkCenter,
       @ObjectModel.readOnly: true
       I_NotificationItem.MainWorkCenterPlant,


       /* Associations */
       I_NotificationItem._Notification, 
       _DefectCode, 
       I_NotificationItem._DefectCodeCatalog, 
       _DefectCodeGroup, 
       I_NotificationItem._NotifItmObjectPartCodeCtlg,
       I_NotificationItem._NotifItmObjectPartCodeGroup,
       I_NotificationItem._NotifItmObjectPartCode,
       _InspectionLot,
       _ReferencedDefect
}
where ( I_Notification.NotificationOrigin = 'Q1' or 
        I_Notification.NotificationOrigin = 'Q2' or 
        I_Notification.NotificationOrigin = 'Q3' or
        I_Notification.NotificationOrigin is null )