P_MfgOrderDefectRecord

@AbapCatalog.sqlViewName: 'PMFGORDDEFREC'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #COMPOSITE
@VDM.private: true
@ObjectModel: {
    usageType: {
        dataClass: #MIXED,
        sizeCategory: #XL,
        serviceQuality: #C }
}
define view P_MfgOrderDefectRecord
  as select from           I_NotificationItem as NotificationItem
    left outer to one join I_ShopFloorItem    as ShopFloorItem on  ShopFloorItem.SerialNumber       = NotificationItem.ProducedSerialNumber
                                                               and ShopFloorItem.Material           = NotificationItem.ProducedMaterial
                                                               and ShopFloorItem.ManufacturingOrder = NotificationItem.ProductionOrder
  association [0..1] to I_DefectStatus         as _DefectStatus         on $projection.DefectLifecycleStatus = _DefectStatus.DefectLifecycleStatus
  association [0..1] to I_NotifProcessingPhase as _NotifProcessingPhase on $projection.NotifProcessingPhase = _NotifProcessingPhase.NotifProcessingPhase
{
  key       NotificationItem.Notification                                                      as DefectInternalID,
  key       NotificationItem.NotificationItem,
            cast('' as qmnum)                                                                  as Notification,
            cast( substring(NotificationItem.Notification,2,11) as qdefectid preserving type ) as Defect,
            NotificationItem.DefectCategory,
            cast('' as qmart)                                                                  as NotificationType,
            @ObjectModel: { foreignKey.association: '_CreatedByUserContactCard' }
            NotificationItem.CreatedByUser,
            NotificationItem.CreationDate,
            @ObjectModel: { foreignKey.association: '_LastChangedByUserContactCard' }
            NotificationItem.LastChangedByUser,
            NotificationItem.LastChangeDate,
            @Semantics.text: true
            cast(NotificationItem.NotificationItemText as vdm_qfetxt preserving type )         as DefectText,
            @Semantics.text: true
            cast('' as qmtxt)                                                                  as NotificationText,
            @ObjectModel: { foreignKey.association: '_DefectCodeCatalog' }
            NotificationItem.DefectCodeCatalog,
            @ObjectModel: { foreignKey.association: '_DefectCodeGroup' }
            cast( NotificationItem.DefectCodeGroup as vdm_qfegrp preserving type )             as DefectCodeGroup,
            @ObjectModel: { foreignKey.association: '_DefectCode' }
            cast( NotificationItem.DefectCode      as vdm_qfecod preserving type )             as DefectCode,
            @Semantics.quantity.unitOfMeasure: 'DefectiveQuantityUnit'
            NotificationItem.DefectiveQuantity,
            @Semantics.unitOfMeasure: true
            NotificationItem.DefectiveQuantityUnit,
            @Semantics.booleanIndicator: true
            NotificationItem.DefectIsCreatedAutomatically,
            NotificationItem.ProductionOrderOperationActy,
            NotificationItem.ProductionOrderOpActyVers,
            cast( NotificationItem.ProductionOrder     as vdm_qm_fertaufnr preserving type )   as ProductionOrder,
            NotificationItem.OrderInternalID,
            @Semantics.time: true
            cast( NotificationItem.CreationTime        as vdm_qerstezeit preserving type )     as CreationTime,
            @Semantics.time: true
            cast( NotificationItem.LastChangeTime      as vdm_qaendezeit preserving type )     as LastChangeTime,
            NotificationItem.InspPlanOperationInternalID,
            NotificationItem.InspectionCharacteristic,
            NotificationItem.InspectionSubsetInternalID,
            NotificationItem.DefectiveSingleUnit,
            NotificationItem.ProducedMaterial,
            NotificationItem.ProducedSerialNumber,
            NotificationItem.Batch,
            ShopFloorItem.ShopFloorItem,
            NotificationItem.Equipment,
            @Semantics.booleanIndicator: true
            cast( NotificationItem.IsDeleted     as vdm_qdefisdeleted preserving type )        as IsDeleted,
            cast( NotificationItem.Material as vdm_defective_material preserving type )        as Material,
            NotificationItem.Plant,
            NotificationItem.InspectionLot,
            NotificationItem.ChangedDateTime,
            cast( '' as qm_phase preserving type )                                             as NotifProcessingPhase,
            NotificationItem.DefectLifecycleStatus,

            NotificationItem.BOMItemInstanceUUID,
            NotificationItem.WorkCenterTypeCode,
            NotificationItem.MainWorkCenterInternalID                                          as WorkCenterInternalID,
            NotificationItem.MainWorkCenter,
            NotificationItem.MainWorkCenterPlant,


            /* Associations */
            NotificationItem._DefectCode,
            NotificationItem._DefectCodeCatalog,
            NotificationItem._DefectCodeGroup,
            NotificationItem._ProducedMaterial,
            NotificationItem._CreatedByUserContactCard,
            NotificationItem._LastChangedByUserContactCard,
            NotificationItem._DefectCategory,
            _DefectStatus,
            _NotifProcessingPhase,
            NotificationItem._Notification
}
where
      NotificationItem.ProductionOrderOperationActy is not initial
  and NotificationItem.ProductionOrderOpActyVers    is not initial
  and NotificationItem.IsDeleted                    is initial
  and NotificationItem.DefectCategory               is not initial

union all select from    I_NotificationItem as NotificationItem
  inner join             I_NotificationItem as NotificationItemRef on  NotificationItemRef.ReferencedDefect =  NotificationItem.Notification
                                                                   and NotificationItemRef.IsDeleted        <> 'X'
  inner join             I_Notification     as Notification        on Notification.Notification = NotificationItemRef.Notification
  left outer to one join I_ShopFloorItem    as ShopFloorItem       on  ShopFloorItem.SerialNumber       = NotificationItem.ProducedSerialNumber
                                                                   and ShopFloorItem.Material           = NotificationItem.ProducedMaterial
                                                                   and ShopFloorItem.ManufacturingOrder = NotificationItem.ProductionOrder
association [0..1] to I_DefectStatus         as _DefectStatus         on $projection.DefectLifecycleStatus = _DefectStatus.DefectLifecycleStatus
association [0..1] to I_NotifProcessingPhase as _NotifProcessingPhase on $projection.NotifProcessingPhase = _NotifProcessingPhase.NotifProcessingPhase
{
  key       NotificationItem.Notification                                                      as DefectInternalID,
  key       NotificationItemRef.NotificationItem,
            NotificationItemRef.Notification,
            cast( substring(NotificationItem.Notification,2,11) as qdefectid preserving type ) as Defect,
            NotificationItem.DefectCategory,
            Notification.NotificationType,
            @ObjectModel: { foreignKey.association: '_CreatedByUserContactCard' }
            NotificationItem.CreatedByUser,
            NotificationItem.CreationDate,
            @ObjectModel: { foreignKey.association: '_LastChangedByUserContactCard' }
            NotificationItem.LastChangedByUser,
            NotificationItem.LastChangeDate,
            @Semantics.text: true
            cast(NotificationItem.NotificationItemText as vdm_qfetxt preserving type )         as DefectText,
            @Semantics.text: true
            Notification.NotificationText,
            @ObjectModel: { foreignKey.association: '_DefectCodeCatalog' }
            NotificationItem.DefectCodeCatalog,
            @ObjectModel: { foreignKey.association: '_DefectCodeGroup' }
            cast( NotificationItem.DefectCodeGroup as vdm_qfegrp preserving type )             as DefectCodeGroup,
            @ObjectModel: { foreignKey.association: '_DefectCode' }
            cast( NotificationItem.DefectCode      as vdm_qfecod preserving type )             as DefectCode,
            @Semantics.quantity.unitOfMeasure: 'DefectiveQuantityUnit'
            NotificationItem.DefectiveQuantity,
            @Semantics.unitOfMeasure: true
            NotificationItem.DefectiveQuantityUnit,
            @Semantics.booleanIndicator: true
            NotificationItem.DefectIsCreatedAutomatically,
            NotificationItem.ProductionOrderOperationActy,
            NotificationItem.ProductionOrderOpActyVers,
            cast( NotificationItem.ProductionOrder     as vdm_qm_fertaufnr preserving type )   as ProductionOrder,
            NotificationItem.OrderInternalID,
            @Semantics.time: true
            cast( NotificationItem.CreationTime        as vdm_qerstezeit preserving type )     as CreationTime,
            @Semantics.time: true
            cast( NotificationItem.LastChangeTime      as vdm_qaendezeit preserving type )     as LastChangeTime,
            NotificationItem.InspPlanOperationInternalID,
            NotificationItem.InspectionCharacteristic,
            NotificationItem.InspectionSubsetInternalID,
            NotificationItem.DefectiveSingleUnit,
            NotificationItem.ProducedMaterial,
            NotificationItem.ProducedSerialNumber,
            NotificationItem.Batch,
            ShopFloorItem.ShopFloorItem,
            NotificationItem.Equipment,
            @Semantics.booleanIndicator: true
            cast( NotificationItem.IsDeleted     as vdm_qdefisdeleted preserving type )        as IsDeleted,
            cast( NotificationItem.Material as vdm_defective_material preserving type )        as Material,
            NotificationItem.Plant,
            NotificationItem.InspectionLot,
            NotificationItem.ChangedDateTime,
            Notification.NotifProcessingPhase,
            NotificationItemRef.DefectLifecycleStatus,

            NotificationItem.BOMItemInstanceUUID,
            NotificationItem.WorkCenterTypeCode,
            NotificationItem.MainWorkCenterInternalID                                          as WorkCenterInternalID,
            NotificationItem.MainWorkCenter,
            NotificationItem.MainWorkCenterPlant,


            /* Associations */
            NotificationItem._DefectCode,
            NotificationItem._DefectCodeCatalog,
            NotificationItem._DefectCodeGroup,
            NotificationItem._ProducedMaterial,
            NotificationItem._CreatedByUserContactCard,
            NotificationItem._LastChangedByUserContactCard,
            NotificationItem._DefectCategory,
            _DefectStatus,
            _NotifProcessingPhase,
            NotificationItem._Notification
}
where
      NotificationItem.ProductionOrderOperationActy is not initial
  and NotificationItem.ProductionOrderOpActyVers    is not initial
  and NotificationItem.IsDeleted                    = 'X'
  and NotificationItem.DefectCategory               is not initial

union all select from    I_NotificationItem as NotificationItem
  inner join             I_Notification     as Notification     on Notification.Notification = NotificationItem.Notification
  left outer to one join I_InspectionSubset as InspectionSubset on  InspectionSubset.InspectionLot               = Notification.InspectionLot
                                                                and InspectionSubset.InspPlanOperationInternalID = NotificationItem.InspPlanOperationInternalID
                                                                and InspectionSubset.InspectionSubsetInternalID  = NotificationItem.InspectionSubsetInternalID
  left outer to one join I_ShopFloorItem    as ShopFloorItem    on ShopFloorItem.ShopFloorItem = InspectionSubset.ShopFloorItem

association [0..1] to I_DefectStatus         as _DefectStatus         on $projection.DefectLifecycleStatus = _DefectStatus.DefectLifecycleStatus
association [0..1] to I_NotifProcessingPhase as _NotifProcessingPhase on $projection.NotifProcessingPhase = _NotifProcessingPhase.NotifProcessingPhase
{
  key       NotificationItem.Notification                                                  as DefectInternalID,
  key       NotificationItem.NotificationItem,
            NotificationItem.Notification,
            ''                                                                             as Defect,
            NotificationItem.DefectCategory,
            Notification.NotificationType,
            @ObjectModel: { foreignKey.association: '_CreatedByUserContactCard' }
            NotificationItem.CreatedByUser,
            NotificationItem.CreationDate,
            @ObjectModel: { foreignKey.association: '_LastChangedByUserContactCard' }
            NotificationItem.LastChangedByUser,
            NotificationItem.LastChangeDate,
            @Semantics.text: true
            cast(NotificationItem.NotificationItemText as vdm_qfetxt preserving type )     as DefectText,
            @Semantics.text: true
            Notification.NotificationText,
            @ObjectModel: { foreignKey.association: '_DefectCodeCatalog' }
            NotificationItem.DefectCodeCatalog,
            @ObjectModel: { foreignKey.association: '_DefectCodeGroup' }
            cast( NotificationItem.DefectCodeGroup as vdm_qfegrp preserving type )         as DefectCodeGroup,
            @ObjectModel: { foreignKey.association: '_DefectCode' }
            cast( NotificationItem.DefectCode      as vdm_qfecod preserving type )         as DefectCode,
            @Semantics.quantity.unitOfMeasure: 'DefectiveQuantityUnit'
            Notification.NotificationReferenceQuantity                                     as DefectiveQuantity,
            @Semantics.unitOfMeasure: true
            Notification.NotificationQuantityUnit                                          as DefectiveQuantityUnit,
            @Semantics.booleanIndicator: true
            NotificationItem.DefectIsCreatedAutomatically,
            NotificationItem.ProductionOrderOperationActy,
            NotificationItem.ProductionOrderOpActyVers,
            cast( Notification.ProductionOrder     as vdm_qm_fertaufnr preserving type )   as ProductionOrder,
            Notification.MfgOrderInternalID                                                as OrderInternalID,
            @Semantics.time: true
            cast( NotificationItem.CreationTime        as vdm_qerstezeit preserving type ) as CreationTime,
            @Semantics.time: true
            cast( NotificationItem.LastChangeTime      as vdm_qaendezeit preserving type ) as LastChangeTime,
            NotificationItem.InspPlanOperationInternalID,
            NotificationItem.InspectionCharacteristic,
            NotificationItem.InspectionSubsetInternalID,
            ShopFloorItem.SerialNumber                                                     as DefectiveSingleUnit,
            NotificationItem.ProducedMaterial,
            NotificationItem.ProducedSerialNumber,
            NotificationItem.Batch,
            InspectionSubset.ShopFloorItem,
            NotificationItem.Equipment,
            @Semantics.booleanIndicator: true
            cast( NotificationItem.IsDeleted     as vdm_qdefisdeleted preserving type )    as IsDeleted,
            cast( Notification.Material as vdm_defective_material preserving type )        as Material,
            Notification.Plant,
            Notification.InspectionLot,
            NotificationItem.ChangedDateTime,
            Notification.NotifProcessingPhase,
            NotificationItem.DefectLifecycleStatus,

            NotificationItem.BOMItemInstanceUUID,
            NotificationItem.WorkCenterTypeCode,
            NotificationItem.MainWorkCenterInternalID                                      as WorkCenterInternalID,
            NotificationItem.MainWorkCenter,
            NotificationItem.MainWorkCenterPlant,


            /* Associations */
            NotificationItem._DefectCode,
            NotificationItem._DefectCodeCatalog,
            NotificationItem._DefectCodeGroup,
            NotificationItem._ProducedMaterial,
            NotificationItem._CreatedByUserContactCard,
            NotificationItem._LastChangedByUserContactCard,
            NotificationItem._DefectCategory,
            _DefectStatus,
            _NotifProcessingPhase,
            NotificationItem._Notification
}
where
      NotificationItem.ProductionOrderOperationActy is not initial
  and NotificationItem.ProductionOrderOpActyVers    is not initial
  and NotificationItem.DefectCategory               is initial
  and NotificationItem.IsDeleted                    is initial
  and NotificationItem.InspectionCharacteristic     is not initial