I_DEFECTUNION
Defects and Quality Notification Items
I_DEFECTUNION is a CDS View in S/4HANA. Defects and Quality Notification Items. It contains 43 fields. 11 CDS views read from this table.
CDS Views using this table (11)
| View | Type | Join | VDM | Description |
|---|---|---|---|---|
| C_Defect | view | from | CONSUMPTION | Defect Consumption View |
| C_DefectCalc | view | from | CONSUMPTION | Manage Defects Calculation Fields |
| C_DefectCalcFld | view | from | CONSUMPTION | Defects Calculation Fields View |
| C_DefectDetailsRsltRecg | view_entity | from | CONSUMPTION | Defect Details for Inspection Lot |
| C_DefectMng | view | from | CONSUMPTION | Manage Defects |
| C_DefectsByCode | view | from | CONSUMPTION | Defects by Defect Code |
| C_InspCharcDefectCntRsltRecg | view_entity | from | CONSUMPTION | Inspection Characteristic Defect Count |
| C_InspOriginDefects | view | from | CONSUMPTION | Defects by Insp Origin |
| C_MatlDefect | view | from | CONSUMPTION | Defects by Material |
| I_DefectCube | view | from | COMPOSITE | Defect Analytics Cube View |
| I_DefectKeyFigCube | view | from | COMPOSITE | Key Figure view for Defect Analytics |
Fields (43)
| Key | Field | CDS Fields | Used in Views |
|---|---|---|---|
| KEY | DefectInternalID | DefectInternalID | 5 |
| KEY | NotificationItem | NotificationItem | 5 |
| _InspectionLot | _InspectionLot | 2 | |
| Batch | Batch | 1 | |
| CreatedByUser | CreatedByUser | 1 | |
| CreationDate | CreationDate | 4 | |
| CreationTime | CreationTime | 1 | |
| Defect | Defect | 5 | |
| DefectCategory | DefectCategory | 4 | |
| DefectCausedBy | DefectCausedBy | 1 | |
| DefectClass | DefectClass | 4 | |
| DefectCode | DefectCode | 5 | |
| DefectCodeCatalog | DefectCodeCatalog | 1 | |
| DefectCodeGroup | DefectCodeGroup | 5 | |
| DefectLifecycleStatus | DefectLifecycleStatus | 3 | |
| DefectLocationCatalog | DefectLocationCatalog | 1 | |
| DefectLocationCode | DefectLocationCode | 1 | |
| DefectLocationCodeGroup | DefectLocationCodeGroup | 1 | |
| DefectText | DefectText | 1 | |
| InspectionCharacteristic | InspectionCharacteristic | 1 | |
| InspectionLot | InspectionLot | 3 | |
| InspectionSubsetInternalID | InspectionSubsetInternalID | 1 | |
| InspPlanOperationInternalID | InspPlanOperationInternalID | 3 | |
| IsDeleted | IsDeleted | 1 | |
| LastChangeDate | LastChangeDate | 1 | |
| LastChangedByUser | LastChangedByUser | 1 | |
| LastChangeTime | LastChangeTime | 1 | |
| MainWorkCenter | MainWorkCenter | 4 | |
| MainWorkCenterInternalID | MainWorkCenterInternalID | 4 | |
| MainWorkCenterPlant | MainWorkCenterPlant | 4 | |
| Material | Material | 6 | |
| MaterialSample | MaterialSample | 1 | |
| Notification | Notification | 4 | |
| NotificationItemExternalID | NotificationItemExternalID | 1 | |
| NotificationType | NotificationType | 1 | |
| NumberOfDefects | NumberOfDefects | 5 | |
| Plant | Plant | 7 | |
| ProductionSupplyArea | ProductionSupplyArea | 1 | |
| SourceStorageBin | SourceStorageBin | 1 | |
| StorageLocation | StorageLocation | 1 | |
| StorageType | StorageType | 1 | |
| Warehouse | Warehouse | 1 | |
| WorkCenterTypeCode | WorkCenterTypeCode | 4 |
@AbapCatalog.sqlViewName: 'IDEFECTUNI'
@AbapCatalog.compiler.compareFilter:true
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM: {
viewType: #COMPOSITE,
lifecycle.contract.type: #SAP_INTERNAL_API
}
@AccessControl.authorizationCheck: #CHECK
@ObjectModel: {
usageType: {
dataClass: #TRANSACTIONAL,
sizeCategory: #S,
serviceQuality: #C },
representativeKey: 'DefectInternalID',
semanticKey: [ 'Defect' ]
}
@Consumption.dbHints: [ 'USE_HEX_PLAN' ]
@EndUserText.label: 'Defects and Quality Notification Items'
/* 1a. independent defects - except categories 01 to 05 */
define view I_DefectUnion as select from I_Defect_TP
//this associatons are just defined so that the same association for notification items can be (1) exposed and (2) be used in the DCL to inherit the condition!
association [0..1] to I_Notification as _Notification on $projection.Notification = _Notification.Notification
association [0..1] to I_NotificationType as _NotificationType on $projection.NotificationType = _NotificationType.NotificationType
association [0..1] to I_DefectLocationCatalog as _DefectLocationCatalog on $projection.DefectLocationCatalog = _DefectLocationCatalog.DefectLocationCatalog
association [0..1] to I_DefectLocationCodeGroup as _DefectLocationCodeGroup on $projection.DefectLocationCatalog = _DefectLocationCodeGroup.DefectLocationCatalog
and $projection.DefectLocationCodeGroup = _DefectLocationCodeGroup.DefectLocationCodeGroup
association [0..1] to I_DefectLocationCode as _DefectLocationCode on $projection.DefectLocationCatalog = _DefectLocationCode.DefectLocationCatalog
and $projection.DefectLocationCodeGroup = _DefectLocationCode.DefectLocationCodeGroup
and $projection.DefectLocationCode = _DefectLocationCode.DefectLocationCode
{
key DefectInternalID, //contains value from QMFE.QMNUM
cast( ' ' as qmnum preserving type ) as Notification,
cast( '0000' as felfd preserving type ) as NotificationItem,
cast( '0000' as qnotifitemexternal preserving type ) as NotificationItemExternalID,
Defect,
DefectCategory,
CreatedByUser,
CreationDate,
CreationTime,
LastChangedByUser,
LastChangeDate,
LastChangeTime,
DefectText,
DefectCodeCatalog,
DefectCodeGroup,
DefectCode,
DefectCodeVersion,
@ObjectModel.foreignKey.association: '_DefectLocationCatalog'
DefectObjectCodeCatalog as DefectLocationCatalog,
@ObjectModel.foreignKey.association: '_DefectLocationCodeGroup'
DefectObjectCodeGroup as DefectLocationCodeGroup,
@ObjectModel.foreignKey.association: '_DefectLocationCode'
DefectObjectCode as DefectLocationCode,
// DefectObjectCodeVersion as DefectLocationCodeVersion,
DefectClass,
NumberOfDefects,
DefectLifecycleStatus,
cast( ' ' as qdefrefnum preserving type ) as ReferencedDefect,
Plant,
Material,
Batch,
InspectionLot,
InspPlanOperationInternalID,
InspectionCharacteristic,
InspectionSubsetInternalID,
MaterialSample,
cast( ' ' as elifn preserving type ) as Supplier,
cast( ' ' as qkunwe preserving type ) as Customer,
@ObjectModel.foreignKey.association: '_NotificationType'
cast( ' ' as qmart preserving type ) as NotificationType,
// For defects with production order and operation
ProductionOrder,
// ProductionOrder as ManufacturingOrder,
ProductionOrderOperation,
ProductionOrderSequence,
// Work center fields
WorkCenterTypeCode,
MainWorkCenterInternalID,
MainWorkCenterPlant,
MainWorkCenter,
// EWM related fields
StorageLocation,
ProductionSupplyArea,
Warehouse,
StorageType,
SourceStorageBin,
WarehouseLogicalSystem,
IsDeleted,
DefectOrigin,
DefectCausedBy,
DefectUUID,
_Notification,
_NotificationType,
_DefectCategory,
_DefectCodeGroup,
_DefectCode,
_DefectLocationCatalog,
_DefectLocationCodeGroup,
_DefectLocationCode,
_DefectClass,
_DefectStatus,
_DefectCausedBy,
_Plant,
_Product,
_InspectionLot,
_EWM_WarehouseNumber_2,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_EWM_WarehouseNumber_2' }
_EWM_WarehouseNumber,
_CreatedByUserContactCard,
_LastChangedByUserContactCard
} where
// Independent defects, except the one related to inspection lot and inspection results
DefectCategory <> ''
and DefectCategory <> '01' and DefectCategory <> '02' and DefectCategory <> '03' and DefectCategory <> '04' and DefectCategory <> '05'
and IsDeleted <> 'X'
/* 1b. independent defects - only categories 01 to 05 */
union select from I_Defect_TP
//this associatons are just defined so that the same association for notification items can be (1) exposed and (2) be used in the DCL to inherit the condition!
association [0..1] to I_Notification as _Notification on $projection.Notification = _Notification.Notification
association [0..1] to I_NotificationType as _NotificationType on $projection.NotificationType = _NotificationType.NotificationType
association [0..1] to I_DefectLocationCatalog as _DefectLocationCatalog on $projection.DefectLocationCatalog = _DefectLocationCatalog.DefectLocationCatalog
association [0..1] to I_DefectLocationCodeGroup as _DefectLocationCodeGroup on $projection.DefectLocationCatalog = _DefectLocationCodeGroup.DefectLocationCatalog
and $projection.DefectLocationCodeGroup = _DefectLocationCodeGroup.DefectLocationCodeGroup
association [0..1] to I_DefectLocationCode as _DefectLocationCode on $projection.DefectLocationCatalog = _DefectLocationCode.DefectLocationCatalog
and $projection.DefectLocationCodeGroup = _DefectLocationCode.DefectLocationCodeGroup
and $projection.DefectLocationCode = _DefectLocationCode.DefectLocationCode
{
key DefectInternalID, //contains value from QMFE.QMNUM
cast( ' ' as qmnum preserving type ) as Notification,
cast( '0000' as felfd preserving type ) as NotificationItem,
cast( '0000' as qnotifitemexternal preserving type ) as NotificationItemExternalID,
Defect,
DefectCategory,
CreatedByUser,
CreationDate,
CreationTime,
LastChangedByUser,
LastChangeDate,
LastChangeTime,
DefectText,
DefectCodeCatalog,
DefectCodeGroup,
DefectCode,
DefectCodeVersion,
@ObjectModel.foreignKey.association: '_DefectLocationCatalog'
DefectObjectCodeCatalog as DefectLocationCatalog,
@ObjectModel.foreignKey.association: '_DefectLocationCodeGroup'
DefectObjectCodeGroup as DefectLocationCodeGroup,
@ObjectModel.foreignKey.association: '_DefectLocationCode'
DefectObjectCode as DefectLocationCode,
// DefectObjectCodeVersion as DefectLocationCodeVersion,
DefectClass,
NumberOfDefects,
DefectLifecycleStatus,
cast( ' ' as qdefrefnum preserving type ) as ReferencedDefect,
Plant,
Material,
_InspectionLot.Batch,
InspectionLot,
InspPlanOperationInternalID,
InspectionCharacteristic,
InspectionSubsetInternalID,
MaterialSample,
cast( ' ' as elifn preserving type ) as Supplier,
cast( ' ' as qkunwe preserving type ) as Customer,
@ObjectModel.foreignKey.association: '_NotificationType'
cast( ' ' as qmart preserving type ) as NotificationType,
// For defects with reference to an inspection lot, the order data are not stored in the defect
_InspectionLot.ManufacturingOrder as ProductionOrder,
ProductionOrderOperation,
ProductionOrderSequence,
// Work center fields
WorkCenterTypeCode,
MainWorkCenterInternalID,
MainWorkCenterPlant,
MainWorkCenter,
// EWM related fields
StorageLocation,
ProductionSupplyArea,
Warehouse,
StorageType,
SourceStorageBin,
WarehouseLogicalSystem,
IsDeleted,
DefectOrigin,
DefectCausedBy,
DefectUUID,
_Notification,
_NotificationType,
_DefectCategory,
_DefectCodeGroup,
_DefectCode,
_DefectLocationCatalog,
_DefectLocationCodeGroup,
_DefectLocationCode,
_DefectClass,
_DefectStatus,
_DefectCausedBy,
_Plant,
_Product,
_InspectionLot,
_EWM_WarehouseNumber_2,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_EWM_WarehouseNumber_2' }
_EWM_WarehouseNumber,
_CreatedByUserContactCard,
_LastChangedByUserContactCard
} where
// Categories related to inspection lot and inspection results
( DefectCategory = '01' or DefectCategory = '02' or DefectCategory = '03' or DefectCategory = '04' or DefectCategory = '05' )
and
IsDeleted <> 'X'
/* 2a. notification items - with referenced independent defect - except (referenced) categories 01 to 05 */
union select from I_NotificationItem
//inner join to I_Notification via association _Notification is intended
association [0..1] to I_NotificationType as _NotificationType on $projection.notificationtype = _NotificationType.NotificationType
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
association [0..1] to I_InspectionLot as _InspectionLot on $projection.inspectionlot = _InspectionLot.InspectionLot
association [0..1] to I_DefectStatus as _DefectStatus on $projection.DefectLifecycleStatus = _DefectStatus.DefectLifecycleStatus
association [0..1] to I_DefectCausedBy as _DefectCausedBy on $projection.DefectCausedBy = _DefectCausedBy.DefectCausedBy
association [0..1] to I_EWM_WarehouseNumber_2 as _EWM_WarehouseNumber_2 on $projection.Warehouse = _EWM_WarehouseNumber_2.EWMWarehouse
association [0..1] to I_EWM_WarehouseNumber as _EWM_WarehouseNumber on $projection.Warehouse = _EWM_WarehouseNumber.Warehouse
association [0..1] to I_UserContactCard as _CreatedByUserContactCard on $projection.CreatedByUser = _CreatedByUserContactCard.ContactCardID
association [0..1] to I_DefectLocationCatalog as _DefectLocationCatalog on $projection.DefectLocationCatalog = _DefectLocationCatalog.DefectLocationCatalog
association [0..1] to I_DefectLocationCodeGroup as _DefectLocationCodeGroup on $projection.DefectLocationCatalog = _DefectLocationCodeGroup.DefectLocationCatalog
and $projection.DefectLocationCodeGroup = _DefectLocationCodeGroup.DefectLocationCodeGroup
association [0..1] to I_DefectLocationCode as _DefectLocationCode on $projection.DefectLocationCatalog = _DefectLocationCode.DefectLocationCatalog
and $projection.DefectLocationCodeGroup = _DefectLocationCode.DefectLocationCodeGroup
and $projection.DefectLocationCode = _DefectLocationCode.DefectLocationCode
association [0..1] to I_Defect_TP as _ReferencedDefect on $projection.ReferencedDefect = _ReferencedDefect.DefectInternalID
{
key DefectInternalID, //contains value from QMFE.INTERNALID
I_NotificationItem.Notification,
I_NotificationItem.NotificationItem,
cast( NotificationItemExternalID as qnotifitemexternal preserving type ) as NotificationItemExternalID,
cast( substring(ReferencedDefect,2,11) as qdefectid preserving type ) as Defect,
DefectCategory,
//for notification items that reference a defect the 'original' creation data is taken from ReportingDate/Time/User (cast to dataelements fitting to the new field name)
cast( I_NotificationItem.NotifItmReportedByUser as ernam preserving type ) as CreatedByUser,
cast( I_NotificationItem.NotifItmReportingDate as erdat preserving type ) as CreationDate,
cast( I_NotificationItem.NotifItmReportingTime as vdm_qerstezeit preserving type ) as CreationTime,
I_NotificationItem.LastChangedByUser,
I_NotificationItem.LastChangeDate,
cast( I_NotificationItem.LastChangeTime as vdm_qaendezeit preserving type ) as LastChangeTime,
NotificationItemText as DefectText,
DefectCodeCatalog,
DefectCodeGroup,
DefectCode,
DefectCodeVersion,
@ObjectModel.foreignKey.association: '_DefectLocationCatalog'
cast( NotifItmObjectPartCodeCtlg as vdm_qfeocat preserving type ) as DefectLocationCatalog,
@ObjectModel.foreignKey.association: '_DefectLocationCodeGroup'
cast( NotifItmObjectPartCodeGroup as vdm_qfeogrp preserving type ) as DefectLocationCodeGroup,
@ObjectModel.foreignKey.association: '_DefectLocationCode'
cast( NotifItmObjectPartCode as vdm_qfeocod preserving type ) as DefectLocationCode,
// NotifItmObjectPartVersion as DefectLocationCodeVersion,
DefectClass,
NumberOfDefects,
cast( ' ' as qdeflcyclestat preserving type ) as DefectLifecycleStatus,
ReferencedDefect,
Plant,
// Transfer from a Defect: Defect: MATNR -> NotificationItem: BAUTL
Assembly as Material,
Batch,
// For item with referenced defect, fetch inspection data from this reference
_ReferencedDefect.InspectionLot,
_ReferencedDefect.InspPlanOperationInternalID,
_ReferencedDefect.InspectionCharacteristic,
_ReferencedDefect.InspectionSubsetInternalID,
_ReferencedDefect.MaterialSample,
_Notification.Supplier,
_Notification.Customer,
@ObjectModel.foreignKey.association: '_NotificationType'
_Notification.NotificationType,
// For item with referenced defect, fetch manufacturing/ production data from this reference
_ReferencedDefect.ProductionOrder,
_ReferencedDefect.ProductionOrderOperation,
_ReferencedDefect.ProductionOrderSequence,
_Notification.WorkCenterTypeCode,
_Notification.MainWorkCenterInternalID,
_Notification.MainWorkCenterPlant,
MainWorkCenter,
// EWM related fields
I_NotificationItem.StorageLocation,
I_NotificationItem.ProductionSupplyArea,
I_NotificationItem.EWMWarehouse as Warehouse,
I_NotificationItem.SourceStorageType as StorageType,
I_NotificationItem.SourceStorageBin,
I_NotificationItem.WarehouseLogicalSystem,
I_NotificationItem.IsDeleted,
I_NotificationItem.DefectOrigin,
DefectCausedBy,
DefectUUID,
_Notification,
_NotificationType,
_DefectCategory,
_DefectCodeGroup,
_DefectCode,
_DefectLocationCatalog,
_DefectLocationCodeGroup,
_DefectLocationCode,
_DefectClass,
_DefectStatus,
_DefectCausedBy,
_Plant,
_Assembly as _Product,
_InspectionLot,
_EWM_WarehouseNumber_2,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_EWM_WarehouseNumber_2' }
_EWM_WarehouseNumber,
_CreatedByUserContactCard,
_LastChangedByUserContactCard
} where //select only notification items
DefectCategory = ''
//and only from quality notifications
and ( _Notification.NotificationOrigin = 'Q1' or _Notification.NotificationOrigin = 'Q2' or _Notification.NotificationOrigin = 'Q3' )
//and only those that have the new ID assigned
and DefectInternalID <> ''
//and with referenced defect
and ReferencedDefect <> ''
// Independent defects, except the one related to inspection lot and inspection results
and _ReferencedDefect.DefectCategory <> ''
and _ReferencedDefect.DefectCategory <> '01' and _ReferencedDefect.DefectCategory <> '02' and _ReferencedDefect.DefectCategory <> '03' and _ReferencedDefect.DefectCategory <> '04' and _ReferencedDefect.DefectCategory <> '05'
and IsDeleted <> 'X'
and _Notification.NotifProcessingPhase <> '5'
/* 2b. notification items - with referenced independent defect - only (referenced) categories 01 to 05 */
union select from I_NotificationItem
//inner join to I_Notification via association _Notification is intended
association [0..1] to I_NotificationType as _NotificationType on $projection.notificationtype = _NotificationType.NotificationType
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
association [0..1] to I_InspectionLot as _InspectionLot on $projection.inspectionlot = _InspectionLot.InspectionLot
association [0..1] to I_DefectStatus as _DefectStatus on $projection.DefectLifecycleStatus = _DefectStatus.DefectLifecycleStatus
association [0..1] to I_DefectCausedBy as _DefectCausedBy on $projection.DefectCausedBy = _DefectCausedBy.DefectCausedBy
association [0..1] to I_EWM_WarehouseNumber_2 as _EWM_WarehouseNumber_2 on $projection.Warehouse = _EWM_WarehouseNumber_2.EWMWarehouse
association [0..1] to I_EWM_WarehouseNumber as _EWM_WarehouseNumber on $projection.Warehouse = _EWM_WarehouseNumber.Warehouse
association [0..1] to I_UserContactCard as _CreatedByUserContactCard on $projection.CreatedByUser = _CreatedByUserContactCard.ContactCardID
association [0..1] to I_DefectLocationCatalog as _DefectLocationCatalog on $projection.DefectLocationCatalog = _DefectLocationCatalog.DefectLocationCatalog
association [0..1] to I_DefectLocationCodeGroup as _DefectLocationCodeGroup on $projection.DefectLocationCatalog = _DefectLocationCodeGroup.DefectLocationCatalog
and $projection.DefectLocationCodeGroup = _DefectLocationCodeGroup.DefectLocationCodeGroup
association [0..1] to I_DefectLocationCode as _DefectLocationCode on $projection.DefectLocationCatalog = _DefectLocationCode.DefectLocationCatalog
and $projection.DefectLocationCodeGroup = _DefectLocationCode.DefectLocationCodeGroup
and $projection.DefectLocationCode = _DefectLocationCode.DefectLocationCode
association [0..1] to I_Defect_TP as _ReferencedDefect on $projection.ReferencedDefect = _ReferencedDefect.DefectInternalID
{
key DefectInternalID, //contains value from QMFE.INTERNALID
I_NotificationItem.Notification,
I_NotificationItem.NotificationItem,
cast( NotificationItemExternalID as qnotifitemexternal preserving type ) as NotificationItemExternalID,
cast( substring(ReferencedDefect,2,11) as qdefectid preserving type ) as Defect,
DefectCategory,
//for notification items that reference a defect the 'original' creation data is taken from ReportingDate/Time/User (cast to dataelements fitting to the new field name)
cast( I_NotificationItem.NotifItmReportedByUser as ernam preserving type ) as CreatedByUser,
cast( I_NotificationItem.NotifItmReportingDate as erdat preserving type ) as CreationDate,
cast( I_NotificationItem.NotifItmReportingTime as vdm_qerstezeit preserving type ) as CreationTime,
I_NotificationItem.LastChangedByUser,
I_NotificationItem.LastChangeDate,
cast( I_NotificationItem.LastChangeTime as vdm_qaendezeit preserving type ) as LastChangeTime,
NotificationItemText as DefectText,
DefectCodeCatalog,
DefectCodeGroup,
DefectCode,
DefectCodeVersion,
@ObjectModel.foreignKey.association: '_DefectLocationCatalog'
cast( NotifItmObjectPartCodeCtlg as vdm_qfeocat preserving type ) as DefectLocationCatalog,
@ObjectModel.foreignKey.association: '_DefectLocationCodeGroup'
cast( NotifItmObjectPartCodeGroup as vdm_qfeogrp preserving type ) as DefectLocationCodeGroup,
@ObjectModel.foreignKey.association: '_DefectLocationCode'
cast( NotifItmObjectPartCode as vdm_qfeocod preserving type ) as DefectLocationCode,
// NotifItmObjectPartVersion as DefectLocationCodeVersion,
DefectClass,
NumberOfDefects,
cast( ' ' as qdeflcyclestat preserving type ) as DefectLifecycleStatus,
ReferencedDefect,
Plant,
// Transfer from a Defect: Defect: MATNR -> NotificationItem: BAUTL
Assembly as Material,
Batch,
// For item with referenced defect, fetch inspection data from this reference
_ReferencedDefect.InspectionLot,
_ReferencedDefect.InspPlanOperationInternalID,
_ReferencedDefect.InspectionCharacteristic,
_ReferencedDefect.InspectionSubsetInternalID,
_ReferencedDefect.MaterialSample,
_Notification.Supplier,
_Notification.Customer,
@ObjectModel.foreignKey.association: '_NotificationType'
_Notification.NotificationType,
// For item with referenced defect, fetch manufacturing/ production data from this reference
// For defects with reference to an inspection lot, the order data are not stored in the defect
_ReferencedDefect._InspectionLot.ManufacturingOrder as ProductionOrder,
_ReferencedDefect.ProductionOrderOperation,
_ReferencedDefect.ProductionOrderSequence,
_Notification.WorkCenterTypeCode,
_Notification.MainWorkCenterInternalID,
_Notification.MainWorkCenterPlant,
MainWorkCenter,
// EWM related fields
I_NotificationItem.StorageLocation,
I_NotificationItem.ProductionSupplyArea,
I_NotificationItem.EWMWarehouse as Warehouse,
I_NotificationItem.SourceStorageType as StorageType,
I_NotificationItem.SourceStorageBin,
I_NotificationItem.WarehouseLogicalSystem,
I_NotificationItem.IsDeleted,
I_NotificationItem.DefectOrigin,
DefectCausedBy,
DefectUUID,
_Notification,
_NotificationType,
_DefectCategory,
_DefectCodeGroup,
_DefectCode,
_DefectLocationCatalog,
_DefectLocationCodeGroup,
_DefectLocationCode,
_DefectClass,
_DefectStatus,
_DefectCausedBy,
_Plant,
_Assembly as _Product,
_InspectionLot,
_EWM_WarehouseNumber_2,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_EWM_WarehouseNumber_2' }
_EWM_WarehouseNumber,
_CreatedByUserContactCard,
_LastChangedByUserContactCard
} where //select only notification items
DefectCategory = ''
//and only from quality notifications
and ( _Notification.NotificationOrigin = 'Q1' or _Notification.NotificationOrigin = 'Q2' or _Notification.NotificationOrigin = 'Q3' )
//and only those that have the new ID assigned
and DefectInternalID <> ''
//and with referenced defect
and ReferencedDefect <> ''
// Categories related to inspection lot and inspection results
and ( _ReferencedDefect.DefectCategory = '01' or _ReferencedDefect.DefectCategory = '02' or _ReferencedDefect.DefectCategory = '03' or _ReferencedDefect.DefectCategory = '04' or _ReferencedDefect.DefectCategory = '05' )
and IsDeleted <> 'X'
and _Notification.NotifProcessingPhase <> '5'
/* 3. notification items - w/o referenced independent defect */
union select from I_NotificationItem
//inner join to I_Notification via association _Notification is intended
association [0..1] to I_NotificationType as _NotificationType on $projection.notificationtype = _NotificationType.NotificationType
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
association [0..1] to I_InspectionLot as _InspectionLot on $projection.inspectionlot = _InspectionLot.InspectionLot
association [0..1] to I_DefectStatus as _DefectStatus on $projection.DefectLifecycleStatus = _DefectStatus.DefectLifecycleStatus
association [0..1] to I_DefectCausedBy as _DefectCausedBy on $projection.DefectCausedBy = _DefectCausedBy.DefectCausedBy
association [0..1] to I_EWM_WarehouseNumber_2 as _EWM_WarehouseNumber_2 on $projection.Warehouse = _EWM_WarehouseNumber_2.EWMWarehouse
association [0..1] to I_EWM_WarehouseNumber as _EWM_WarehouseNumber on $projection.Warehouse = _EWM_WarehouseNumber.Warehouse
association [0..1] to I_UserContactCard as _CreatedByUserContactCard on $projection.CreatedByUser = _CreatedByUserContactCard.ContactCardID
association [0..1] to I_DefectLocationCatalog as _DefectLocationCatalog on $projection.DefectLocationCatalog = _DefectLocationCatalog.DefectLocationCatalog
association [0..1] to I_DefectLocationCodeGroup as _DefectLocationCodeGroup on $projection.DefectLocationCatalog = _DefectLocationCodeGroup.DefectLocationCatalog
and $projection.DefectLocationCodeGroup = _DefectLocationCodeGroup.DefectLocationCodeGroup
association [0..1] to I_DefectLocationCode as _DefectLocationCode on $projection.DefectLocationCatalog = _DefectLocationCode.DefectLocationCatalog
and $projection.DefectLocationCodeGroup = _DefectLocationCode.DefectLocationCodeGroup
and $projection.DefectLocationCode = _DefectLocationCode.DefectLocationCode
{
key DefectInternalID, //contains value from QMFE.INTERNALID
I_NotificationItem.Notification,
NotificationItem,
cast( NotificationItemExternalID as qnotifitemexternal preserving type ) as NotificationItemExternalID,
cast( substring(DefectInternalID,2,11) as qdefectid preserving type ) as Defect,
DefectCategory,
//for notification items that not reference a defect the creation data are taken from the original fields
I_NotificationItem.CreatedByUser,
I_NotificationItem.CreationDate,
cast( I_NotificationItem.CreationTime as vdm_qerstezeit preserving type ) as CreationTime,
I_NotificationItem.LastChangedByUser,
I_NotificationItem.LastChangeDate,
cast( I_NotificationItem.LastChangeTime as vdm_qaendezeit preserving type ) as LastChangeTime,
NotificationItemText as DefectText,
DefectCodeCatalog,
DefectCodeGroup,
DefectCode,
DefectCodeVersion,
@ObjectModel.foreignKey.association: '_DefectLocationCatalog'
cast( NotifItmObjectPartCodeCtlg as vdm_qfeocat preserving type ) as DefectLocationCatalog,
@ObjectModel.foreignKey.association: '_DefectLocationCodeGroup'
cast( NotifItmObjectPartCodeGroup as vdm_qfeogrp preserving type ) as DefectLocationCodeGroup,
@ObjectModel.foreignKey.association: '_DefectLocationCode'
cast( NotifItmObjectPartCode as vdm_qfeocod preserving type ) as DefectLocationCode,
// NotifItmObjectPartVersion as DefectLocationCodeVersion,
DefectClass,
NumberOfDefects,
cast( ' ' as qdeflcyclestat preserving type ) as DefectLifecycleStatus,
ReferencedDefect,
_Notification.Plant,
_Notification.Material,
_Notification.Batch,
_Notification.InspectionLot,
InspPlanOperationInternalID,
InspectionCharacteristic,
InspectionSubsetInternalID,
MaterialSample,
_Notification.Supplier,
_Notification.Customer,
@ObjectModel.foreignKey.association: '_NotificationType'
_Notification.NotificationType,
// No order data in notification item so far
cast( ' ' as vdm_qm_fertaufnr preserving type ) as ProductionOrder,
// cast( ' ' as vdm_qm_fertaufnr preserving type ) as ManufacturingOrder,
cast( ' ' as vornr preserving type ) as ProductionOrderOperation, // External operation number
cast( ' ' as plnfolge preserving type ) as ProductionOrderSequence, // Sequence of operation
_Notification.WorkCenterTypeCode,
_Notification.MainWorkCenterInternalID,
_Notification.MainWorkCenterPlant,
MainWorkCenter,
// No EWM related fields for items without referenced defect
cast( ' ' as lgort_d preserving type ) as StorageLocation,
cast( ' ' as vdm_prvbe preserving type) as ProductionSupplyArea,
cast( ' ' as /scwm/lgnum preserving type ) as Warehouse,
cast( ' ' as /scwm/ltap_vltyp preserving type ) as StorageType,
cast( ' ' as /scwm/ltap_vlpla preserving type ) as SourceStorageBin,
cast( ' ' as qfewm_wrhslgclsyst preserving type ) as WarehouseLogicalSystem,
I_NotificationItem.IsDeleted,
I_NotificationItem.DefectOrigin,
cast( ' ' as qdefectcausedby preserving type ) as DefectCausedBy,
DefectUUID,
_Notification,
_NotificationType,
_DefectCategory,
_DefectCodeGroup,
_DefectCode,
_DefectLocationCatalog,
_DefectLocationCodeGroup,
_DefectLocationCode,
_DefectClass,
_DefectStatus,
_DefectCausedBy,
_Notification._Plant,
_Notification._Product,
_InspectionLot,
_EWM_WarehouseNumber_2,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_EWM_WarehouseNumber_2' }
_EWM_WarehouseNumber,
_CreatedByUserContactCard,
_LastChangedByUserContactCard
} where //select only notification items
DefectCategory = ''
//and only from quality notifications
and ( _Notification.NotificationOrigin = 'Q1' or _Notification.NotificationOrigin = 'Q2' or _Notification.NotificationOrigin = 'Q3' )
//and only those that have the new ID assigned
and DefectInternalID <> ''
//and without referenced defect
and ReferencedDefect = ''
and IsDeleted <> 'X'
and _Notification.NotifProcessingPhase <> '5'