@AccessControl.authorizationCheck: #MANDATORY
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.supportedCapabilities: [ #SQL_DATA_SOURCE,
#CDS_MODELING_DATA_SOURCE,
#CDS_MODELING_ASSOCIATION_TARGET
]
@ObjectModel.usageType: { serviceQuality: #D,
sizeCategory: #XL,
dataClass: #MIXED
}
@VDM.viewType: #COMPOSITE
@VDM.private:true
define view entity P_ProductChangeDocument
as select from I_ChangeDocument as ChangeDocument
left outer join I_ChangeDocumentItem as Item on ChangeDocument.ChangeDocObjectClass = Item.ChangeDocObjectClass
and ChangeDocument.ChangeDocObject = Item.ChangeDocObject
and ChangeDocument.ChangeDocument = Item.ChangeDocument
left outer join I_ProductFieldAttributes as _ProductFieldAttributes
on _ProductFieldAttributes.ProductFieldNameWithTable = cast( concat(concat( replace( Item.DatabaseTable, '_MASS', '' ),'-' ), Item.ChangeDocDatabaseTableField
) as fname_full )
association [0..1] to I_UserContactCard as _CreatedByUserContactCard on $projection.CreatedByUser = _CreatedByUserContactCard.ContactCardID
association [1..1] to I_Product as _Product on $projection.ChangeDocObject = _Product.Product
{
key _Product.Product,
key ChangeDocument.ChangeDocObjectClass,
key ChangeDocument.ChangeDocument,
key Item.DatabaseTable,
key Item.ChangeDocTableKey,
key Item.ChangeDocDatabaseTableField,
key Item.ChangeDocItemChangeType,
Item.ChangeDocObject,
ChangeDocument.CreatedByUser,
_CreatedByUserContactCard.FirstName,
_CreatedByUserContactCard.LastName,
_CreatedByUserContactCard.Department,
@Semantics.systemDate.createdAt: true
ChangeDocument.CreationDate,
@Semantics.systemTime.createdAt: true
ChangeDocument.CreationTime,
ChangeDocument.ChangeTransactionCode,
Item._TableTextDDIC.Text as DatabaseTableText,
Item._FieldTextDDIC.Text as ChangeDocFieldNameDesc,
Item.ChangeDocPreviousUnit,
Item.ChangeDocNewUnit,
Item.ChangeDocPreviousCurrency,
Item.ChangeDocNewCurrency,
Item.ChangeDocPreviousFieldValue,
Item.ChangeDocNewFieldValue,
Item._ChangeTypeText.Text as ChangeDocChangeTypeText,
Item.ChangeDocTextIsChanged,
// Plant (WERKS)
cast(
case Item.DatabaseTable
when 'DMKAL' then left(Item.ChangeDocTableKey, 4)
when 'DQMAT' then left(Item.ChangeDocTableKey, 4)
when 'MARC' then substring(Item.ChangeDocTableKey, 44, 4)
when 'MARC_MASS' then left(Item.ChangeDocTableKey, 4)
when 'MARD' then substring(Item.ChangeDocTableKey, 44, 4)
when 'MARD_MASS' then left(Item.ChangeDocTableKey, 4)
when 'MFHM' then substring(Item.ChangeDocTableKey, 44, 4)
when 'MFHM_MASS' then left(Item.ChangeDocTableKey, 4)
when 'MPGD' then substring(Item.ChangeDocTableKey, 44, 4)
when 'MPGD_MASS' then substring(Item.ChangeDocTableKey, 44, 4)
when 'MPOP' then substring(Item.ChangeDocTableKey, 44, 4)
when 'MPOP_MASS' then left(Item.ChangeDocTableKey, 4)
else ''
end
as werks_d
) as Plant,
// Sales Organization (VKORG)
cast(
case Item.DatabaseTable
when 'MVKE' then substring(Item.ChangeDocTableKey, 44, 4)
when 'MVKE_MASS' then left(Item.ChangeDocTableKey, 4)
else ''
end
as vkorg
) as ProductSalesOrg,
// Distribution Channel (VTWEG)
cast(
case Item.DatabaseTable
when 'MVKE' then substring(Item.ChangeDocTableKey, 48, 2)
when 'MVKE_MASS' then substring(Item.ChangeDocTableKey, 5, 2)
else ''
end
as vtweg
) as ProductDistributionChnl,
// Valuation Area (BWKEY)
cast(
case Item.DatabaseTable
when 'MBEW' then substring(Item.ChangeDocTableKey, 44, 4)
when 'MBEW_MASS' then left(Item.ChangeDocTableKey, 4)
else ''
end
as bwkey
) as ValuationArea,
// Warehouse Number (LGNUM)
cast(
case Item.DatabaseTable
when 'MLGN' then substring(Item.ChangeDocTableKey, 44, 3)
when 'MLGN_MASS' then left(Item.ChangeDocTableKey, 3)
when 'MLGT' then substring(Item.ChangeDocTableKey, 44, 3)
when 'MLGT_MASS' then left(Item.ChangeDocTableKey, 3)
else ''
end
as lgnum
) as WarehouseNumber,
_ProductFieldAttributes.MaintenanceStatus as MaintenanceStatus,
@UI.hidden: true
@Consumption.hidden: true
cast( substring(_ProductFieldAttributes.MaintenanceStatus, 1, 1) as statm ) as MaintenanceStatus1,
@UI.hidden: true
@Consumption.hidden: true
cast( substring(_ProductFieldAttributes.MaintenanceStatus, 2, 1) as statm ) as MaintenanceStatus2,
@UI.hidden: true
@Consumption.hidden: true
cast( substring(_ProductFieldAttributes.MaintenanceStatus, 3, 1) as statm ) as MaintenanceStatus3,
@UI.hidden: true
@Consumption.hidden: true
cast( substring(_ProductFieldAttributes.MaintenanceStatus, 4, 1) as statm ) as MaintenanceStatus4,
@UI.hidden: true
@Consumption.hidden: true
cast( substring(_ProductFieldAttributes.MaintenanceStatus, 5, 1) as statm ) as MaintenanceStatus5,
@UI.hidden: true
@Consumption.hidden: true
cast( substring(_ProductFieldAttributes.MaintenanceStatus, 6, 1) as statm ) as MaintenanceStatus6,
@UI.hidden: true
@Consumption.hidden: true
cast( substring(_ProductFieldAttributes.MaintenanceStatus, 7, 1) as statm ) as MaintenanceStatus7,
@UI.hidden: true
@Consumption.hidden: true
cast( substring(_ProductFieldAttributes.MaintenanceStatus, 8, 1) as statm ) as MaintenanceStatus8,
@UI.hidden: true
@Consumption.hidden: true
cast( substring(_ProductFieldAttributes.MaintenanceStatus, 9, 1) as statm ) as MaintenanceStatus9,
@UI.hidden: true
@Consumption.hidden: true
cast( substring(_ProductFieldAttributes.MaintenanceStatus, 10, 1) as statm ) as MaintenanceStatus10,
@UI.hidden: true
@Consumption.hidden: true
cast( substring(_ProductFieldAttributes.MaintenanceStatus, 11, 1) as statm ) as MaintenanceStatus11,
@UI.hidden: true
@Consumption.hidden: true
cast( substring(_ProductFieldAttributes.MaintenanceStatus, 12, 1) as statm ) as MaintenanceStatus12,
@UI.hidden: true
@Consumption.hidden: true
cast( substring(_ProductFieldAttributes.MaintenanceStatus, 13, 1) as statm ) as MaintenanceStatus13,
@UI.hidden: true
@Consumption.hidden: true
cast( substring(_ProductFieldAttributes.MaintenanceStatus, 14, 1) as statm ) as MaintenanceStatus14,
@UI.hidden: true
@Consumption.hidden: true
cast( substring(_ProductFieldAttributes.MaintenanceStatus, 15, 1) as statm ) as MaintenanceStatus15,
_CreatedByUserContactCard,
_Product
}
where
ChangeDocument.ChangeDocObjectClass = 'MATERIAL'
and _Product.Product is not initial