@EndUserText.label: 'Goods Movement data extractor'
@AccessControl: {
authorizationCheck: #PRIVILEGED_ONLY,
personalData.blocking: #NOT_REQUIRED
}
@Metadata: {
ignorePropagatedAnnotations: true
}
@ObjectModel: {
usageType: {
sizeCategory: #XXL,
serviceQuality: #D,
dataClass: #MIXED
},
sapObjectNodeType.name: 'MaterialDocument',
supportedCapabilities: [#EXTRACTION_DATA_SOURCE],
modelingPattern: #NONE
}
@VDM: {
viewType: #CONSUMPTION,
lifecycle.contract.type: #PUBLIC_LOCAL_API
}
@Analytics: {
dataCategory: #FACT,
internalName: #LOCAL,
technicalName: 'CSUSTGDSMVTDOCDEX',
dataExtraction: {
enabled: true,
delta.changeDataCapture:
{ mapping:
[
{ role: #MAIN, table: 'MATDOC', tableElement: ['key1', 'key2', 'key3', 'key4', 'key5', 'key6'], viewElement: ['MaterialDocumentKey1', 'MaterialDocumentKey2', 'MaterialDocumentKey3', 'MaterialDocumentKey4', 'MaterialDocumentKey5', 'MaterialDocumentKey6'] },
{ role: #LEFT_OUTER_TO_ONE_JOIN, table: 'lfa1', tableElement: ['lifnr'], viewElement: ['Supplier'] },
{ role: #LEFT_OUTER_TO_ONE_JOIN, table: 'kna1', tableElement: ['kunnr'], viewElement: ['Customer'] },
{ role: #LEFT_OUTER_TO_ONE_JOIN, table: 'mara', tableElement: ['matnr'], viewElement: ['Material'] },
{ role: #LEFT_OUTER_TO_ONE_JOIN, table: 'adrc', tableElement: ['addrnumber'], viewElement: ['PlantAddressID'] },
{ role: #LEFT_OUTER_TO_ONE_JOIN, table: 'adrc', tableElement: ['addrnumber'], viewElement: ['IssgOrRcvgPlantAddressID'] },
{ role: #LEFT_OUTER_TO_ONE_JOIN, table: 'kna1', tableElement: ['kunnr'], viewElement: ['SoldToParty'] },
{ role: #LEFT_OUTER_TO_ONE_JOIN, table: 'adrc', tableElement: ['addrnumber'], viewElement: ['SoldToPartyAddressID'] },
{ role: #LEFT_OUTER_TO_ONE_JOIN, table: 'kna1', tableElement: ['kunnr'], viewElement: ['ShipToParty'] },
{ role: #LEFT_OUTER_TO_ONE_JOIN, table: 'adrc', tableElement: ['addrnumber'], viewElement: ['ShipToPartyAddressID'] },
{ role: #LEFT_OUTER_TO_ONE_JOIN, table: 'lfa1', tableElement: ['lifnr'], viewElement: ['SupplyingSupplier'] },
{ role: #LEFT_OUTER_TO_ONE_JOIN, table: 'adrc', tableElement: ['addrnumber'], viewElement: ['StorageLocationAddressID'] },
{ role: #LEFT_OUTER_TO_ONE_JOIN, table: 'adrc', tableElement: ['addrnumber'], viewElement: ['ManualDeliveryAddressID'] },
{ role: #LEFT_OUTER_TO_ONE_JOIN, table: 'adrc', tableElement: ['addrnumber'], viewElement: ['IssgOrRcvgStorLocAddressID'] }
]
}
}
}
define view entity C_SustGoodsMovementDocumentDEX
as select from I_MaterialDocumentRecord as MaterialDocumentRecord
left outer to one join P_SustPurchaseOrderItemStorLoc as _PurchOrdIssuingStorLoc on MaterialDocumentRecord.PurchaseOrderItem = _PurchOrdIssuingStorLoc.PurchaseOrderItem
and MaterialDocumentRecord.PurchaseOrder = _PurchOrdIssuingStorLoc.PurchaseOrder
left outer to one join P_SustStorLocAddrMinSqnc as _StorageLocationAddress on MaterialDocumentRecord.StorageLocation = _StorageLocationAddress.StorageLocation
and MaterialDocumentRecord.Plant = _StorageLocationAddress.Plant
left outer to one join P_SustStorLocAddrMinSqnc as _IssuingOrReceivingStorLocAddr on MaterialDocumentRecord.IssuingOrReceivingStorageLoc = _IssuingOrReceivingStorLocAddr.StorageLocation
and MaterialDocumentRecord.IssuingOrReceivingPlant = _IssuingOrReceivingStorLocAddr.Plant
association [0..1] to I_Order as _Order on MaterialDocumentRecord.OrderID = _Order.OrderID
{
key MaterialDocumentRecord.MaterialDocumentKey1,
key MaterialDocumentRecord.MaterialDocumentKey2,
key MaterialDocumentRecord.MaterialDocumentKey3,
key MaterialDocumentRecord.MaterialDocumentKey4,
key MaterialDocumentRecord.MaterialDocumentKey5,
key MaterialDocumentRecord.MaterialDocumentKey6,
@Semantics.calendar.year: true
MaterialDocumentRecord.MaterialDocumentYear,
MaterialDocumentRecord.MaterialDocument,
MaterialDocumentRecord.MaterialDocumentItem,
MaterialDocumentRecord.MaterialDocumentLine,
MaterialDocumentRecord.GoodsMovementType,
MaterialDocumentRecord.GoodsMovementRefDocType,
MaterialDocumentRecord.GoodsMovementIsCancelled,
MaterialDocumentRecord.IsReversalMovementType,
MaterialDocumentRecord.StockIdentifyingMaterial,
MaterialDocumentRecord.Plant,
MaterialDocumentRecord.StorageLocation,
_StorageLocationAddress.Plant as StorageLocationPlant,
_StorageLocationAddress.AddressID as StorageLocationAddressID,
_StorageLocationAddress.Country as StorageLocationCountry,
_StorageLocationAddress.Region as StorageLocationRegion,
MaterialDocumentRecord.InventorySpecialStockType,
MaterialDocumentRecord.InventoryStockType,
MaterialDocumentRecord.CompanyCode,
MaterialDocumentRecord.CompanyCodeCurrency,
MaterialDocumentRecord.ControllingArea,
MaterialDocumentRecord.CostCenter,
@Semantics.businessDate.at: true
MaterialDocumentRecord.PostingDate,
MaterialDocumentRecord.DocumentDate,
MaterialDocumentRecord.ManufactureDate,
MaterialDocumentRecord.CreationDate,
MaterialDocumentRecord.CreationTime,
@Semantics.calendar.dayOfYear: true
MaterialDocumentRecord.CalendarDay as CalendarDayOfYear, // The GFN used by the underlying basic view is wrong, and this would raise Prio 2 ATC issues on consumption-level due to a type-mismatch to the default data element
@Semantics.calendar.quarter: true
MaterialDocumentRecord.CalendarQuarter,
@Semantics.calendar.month: true
MaterialDocumentRecord.CalendarMonth,
@Semantics.calendar.week: true
MaterialDocumentRecord.CalendarWeek,
//References
MaterialDocumentRecord.SalesOrder,
MaterialDocumentRecord.SalesOrderItem,
MaterialDocumentRecord.SalesOrderScheduleLine,
MaterialDocumentRecord.PurchaseOrder,
MaterialDocumentRecord.PurchaseOrderItem,
MaterialDocumentRecord.InvtryMgmtReferenceDocument,
MaterialDocumentRecord.ManufacturingOrder,
MaterialDocumentRecord.ReferenceDocument as AccountingDocExternalReference, // This is a FIN-specific field, and the GFN used by the underlying basic view does not reflect that. Hence changing the GFN to comply to the VDM, and avoid Prio 2 ATC issues.
MaterialDocumentRecord.DeliveryDocument,
MaterialDocumentRecord.DeliveryDocumentItem,
MaterialDocumentRecord.Material,
MaterialDocumentRecord.Batch,
MaterialDocumentRecord.Supplier,
MaterialDocumentRecord.Customer,
MaterialDocumentRecord.GoodsRecipientName,
MaterialDocumentRecord.IssgOrRcvgMaterial,
MaterialDocumentRecord.IssgOrRcvgBatch,
MaterialDocumentRecord.IssuingOrReceivingPlant,
MaterialDocumentRecord.InventoryTransactionType,
MaterialDocumentRecord.StockOwner,
MaterialDocumentRecord.IssuingOrReceivingStorageLoc,
_IssuingOrReceivingStorLocAddr.AddressID as IssgOrRcvgStorLocAddressID,
_IssuingOrReceivingStorLocAddr.Country as IssgOrRcvgStorLocCountry,
_IssuingOrReceivingStorLocAddr.Region as IssgOrRcvgStorLocRegion,
cast(MaterialDocumentRecord._ManufacturingOrder.Material as pph_matko preserving type) as MainProduct,
MaterialDocumentRecord._PurchaseOrder.PurchasingGroup,
MaterialDocumentRecord._PurchaseOrder.PurchasingOrganization,
MaterialDocumentRecord.ProjectNetwork,
cast(MaterialDocumentRecord.WBSElementInternalID as ps_s4_pspnr preserving type) as WBSElementInternalID,
MaterialDocumentRecord._WBSElementBasicData.WBSElementExternalID as WBSElementExternalID,
MaterialDocumentRecord._WBSElementBasicData._Project.ProjectExternalID as ProjectExternalID,
cast(MaterialDocumentRecord._WBSElementBasicData.ProjectInternalID as ps_pspnr preserving type) as ProjectInternalID,
//Reference types
MaterialDocumentRecord._Material.MaterialType,
MaterialDocumentRecord._Material.ProductCategory as MaterialCategory,
MaterialDocumentRecord._Material.MaterialGroup,
_Order.OrderID,
_Order.OrderCategory,
MaterialDocumentRecord.AccountingDocumentType,
MaterialDocumentRecord._ManufacturingOrder.ManufacturingOrderCategory,
//Address and Countries
// Note: To be consistent with the extractors C_SustPlantAddressDEX, I_Supplier and I_Customer, the "Country" information are either retrieved via the
// associated Address (for Plant), or the direct field at the entity (for Supplier and Customer), even though both would be available for each entity.
MaterialDocumentRecord._Plant.AddressID as PlantAddressID,
@Analytics.hidden: true
MaterialDocumentRecord._IssuingOrReceivingPlant.AddressID as IssgOrRcvgPlantAddressID, // needed for CDC-based delta
MaterialDocumentRecord._Plant._StandardOrganizationAddress.Country as PlantCountry,
MaterialDocumentRecord._Plant._StandardOrganizationAddress.Region as PlantRegion,
MaterialDocumentRecord._IssuingOrReceivingPlant._StandardOrganizationAddress.Country as IssuingOrReceivingPlantCountry,
MaterialDocumentRecord._IssuingOrReceivingPlant._StandardOrganizationAddress.Region as IssuingOrReceivingPlantRegion,
MaterialDocumentRecord._Supplier.Country as SupplierCountry,
MaterialDocumentRecord._Supplier.Region as SupplierRegion,
MaterialDocumentRecord._Customer.Country as CustomerCountry,
MaterialDocumentRecord._Customer.Region as CustomerRegion,
MaterialDocumentRecord._DeliveryDocument._SoldToParty.Customer as SoldToParty,
MaterialDocumentRecord._DeliveryDocument._SoldToParty.AddressID as SoldToPartyAddressID,
MaterialDocumentRecord._DeliveryDocument._SoldToParty._AddressDefaultRepresentation.Country as SoldToPartyCountry,
MaterialDocumentRecord._DeliveryDocument._SoldToParty._AddressDefaultRepresentation.Region as SoldToPartyRegion,
MaterialDocumentRecord._DeliveryDocument._ShipToParty.Customer as ShipToParty,
MaterialDocumentRecord._DeliveryDocument._ShipToParty.AddressID as ShipToPartyAddressID,
MaterialDocumentRecord._DeliveryDocument._ShipToParty._AddressDefaultRepresentation.Country as ShipToPartyCountry,
MaterialDocumentRecord._DeliveryDocument._ShipToParty._AddressDefaultRepresentation.Region as ShipToPartyRegion,
MaterialDocumentRecord._PurchaseOrder.SupplyingPlant,
MaterialDocumentRecord._PurchaseOrder._SupplyingPlant._StandardOrganizationAddress.Country as SupplyingPlantCountry,
MaterialDocumentRecord._PurchaseOrder._SupplyingPlant._StandardOrganizationAddress.Region as SupplyingPlantRegion,
MaterialDocumentRecord._PurchaseOrder.SupplyingSupplier,
MaterialDocumentRecord._PurchaseOrder._SupplyingSupplier.Country as SupplyingSupplierCountry,
MaterialDocumentRecord._PurchaseOrder._SupplyingSupplier.Region as SupplyingSupplierRegion,
MaterialDocumentRecord._PurchaseOrderItem.IssuingStorageLocation,
_PurchOrdIssuingStorLoc.Country as IssuingStorageLocationCountry,
_PurchOrdIssuingStorLoc.Region as IssuingStorageLocationRegion,
MaterialDocumentRecord._PurchaseOrderItem.ManualDeliveryAddressID,
MaterialDocumentRecord._PurchaseOrderItem._ManualDeliveryAddress_2.Country as ManualDeliveryAddressCountry,
MaterialDocumentRecord._PurchaseOrderItem._ManualDeliveryAddress_2.Region as ManualDeliveryAddressRegion,
MaterialDocumentRecord._PurchaseOrderItem.Subcontractor,
MaterialDocumentRecord._PurchaseOrderItem._Subcontractor._AddressDefaultRepresentation.Country as SubcontractorCountry,
MaterialDocumentRecord._PurchaseOrderItem._Subcontractor._AddressDefaultRepresentation.Region as SubcontractorRegion,
MaterialDocumentRecord._PurchaseOrderItem.Customer as PurchaseOrderReceivingCustomer,
MaterialDocumentRecord._PurchaseOrderItem._Customer._AddressDefaultRepresentation.Country as PurOrdReceivingCustomerCountry,
MaterialDocumentRecord._PurchaseOrderItem._Customer._AddressDefaultRepresentation.Region as PurOrdReceivingCustomerRegion,
// Calculated Fields
cast(case when MaterialDocumentRecord.MatlStkChangeQtyInBaseUnit > 0 and MaterialDocumentRecord.IsReversalMovementType = '' or MaterialDocumentRecord.MatlStkChangeQtyInBaseUnit < 0 and MaterialDocumentRecord.IsReversalMovementType = 'X'
then 'GR'
else case
when MaterialDocumentRecord.MatlStkChangeQtyInBaseUnit < 0 and MaterialDocumentRecord.IsReversalMovementType = '' or MaterialDocumentRecord.MatlStkChangeQtyInBaseUnit > 0 and MaterialDocumentRecord.IsReversalMovementType = 'X'
then 'GI'
else '' end
end as nsdm_stock_change_category preserving type) as StockChangeCategory,
cast(case when MaterialDocumentRecord.IssuingOrReceivingPlant <> '' and MaterialDocumentRecord.IssuingOrReceivingPlant <> MaterialDocumentRecord.Plant
then 'X'
else ''
end as nsdm_cross_plant_transfer preserving type) as IsCrossPlantTransfer,
case when MaterialDocumentRecord._PurchaseOrderItem.IncotermsClassification <> '' then MaterialDocumentRecord._PurchaseOrderItem.IncotermsClassification
else MaterialDocumentRecord._PurchaseOrder.IncotermsClassification end as IncotermsClassification,
// Units
MaterialDocumentRecord.MaterialBaseUnit,
MaterialDocumentRecord.EntryUnit,
// Amounts
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
@Aggregation.default: #SUM
MaterialDocumentRecord.TotalGoodsMvtAmtInCCCrcy,
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
@Aggregation.default: #SUM
MaterialDocumentRecord.GoodsMovementStkAmtInCCCrcy,
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
@Aggregation.default: #SUM
MaterialDocumentRecord.GoodsMvtCnsmpnAmtInCCCrcy,
// Quantities
@Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
@Aggregation.default: #SUM
MaterialDocumentRecord.MatlStkChangeQtyInBaseUnit,
@Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
@Aggregation.default: #SUM
MaterialDocumentRecord.MatlCnsmpnQtyInMatlBaseUnit,
@Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
@Aggregation.default: #SUM
MaterialDocumentRecord.QuantityInBaseUnit,
@Semantics.quantity.unitOfMeasure: 'EntryUnit'
MaterialDocumentRecord.QuantityInEntryUnit
}
// the following where-condition is a copy of I_GoodsMovementDocumentDEX (PO decision)
where
MaterialDocumentRecord.MaterialDocumentRecordType = 'MDOC'
or MaterialDocumentRecord.MaterialDocumentRecordType = 'MDOC_CP'
or MaterialDocumentRecord.MaterialDocumentRecordType = 'MDOC340'
or MaterialDocumentRecord.MaterialDocumentRecordType = 'CORR'