@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@EndUserText.label: 'Delivery Document Item Analytics'
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck:#CHECK
@AccessControl.privilegedAssociations: [ '_CreatedByUser', '_Partner' ]
@AccessControl.personalData.blocking: #BLOCKED_DATA_EXCLUDED
@AbapCatalog.sqlViewName: 'ISDDELDOCITMANA'
define view I_DeliveryDocItemAnalytics
as select from I_DeliveryDocumentItem as DDI
inner join I_SalesDocumentItem as SDI on DDI.ReferenceSDDocument = SDI.SalesDocument
and DDI.ReferenceSDDocumentItem = SDI.SalesDocumentItem
--AT 21.09.2018 Incident 484039 Batch Handling:
-- by this l.o. join to a given lips a potential SUPERordenated lips is joined. Later in KPI calculation:
-- info of this SUBordenated lips is only considered, if the
-- SUBordenated is billing relevant,but not the SUPERordenated !
-- (--> DDI_SUPER.DeliveryRelatedBillingStatus = A,B)
left outer to one join I_DeliveryDocumentItem as DDI_SUPER on DDI.HigherLvlItmOfBatSpltItm is not initial
and DDI_SUPER.DeliveryDocument = DDI.DeliveryDocument
and DDI.HigherLvlItmOfBatSpltItm = DDI_SUPER.DeliveryDocumentItem
association [0..1] to I_Currency as _StatisticsCurrency on $projection.StatisticsCurrency = _StatisticsCurrency.Currency
{
// Key
@ObjectModel.foreignKey.association: '_DeliveryDocument'
key DDI.DeliveryDocument,
key DDI.DeliveryDocumentItem,
//Dimensions
//category
@ObjectModel.foreignKey.association: '_SDDocumentCategory'
DDI.SDDocumentCategory,
@ObjectModel.foreignKey.association: '_ItemCategory'
DDI.DeliveryDocumentItemCategory,
@ObjectModel.foreignKey.association: '_SalesDocumentItemType'
DDI.SalesDocumentItemType,
//Admin
DDI.CreatedByUser,
@Semantics.systemDate.createdAt: true
DDI.CreationDate,
DDI.CreationTime,
@Semantics.systemDate.lastChangedAt: true
DDI.LastChangeDate,
//Organization (Team View,..)
@ObjectModel.foreignKey.association: '_SalesOrganization'
DDI._DeliveryDocument.SalesOrganization,
@ObjectModel.foreignKey.association: '_DistributionChannel'
DDI.DistributionChannel,
@ObjectModel.foreignKey.association: '_Division'
DDI.Division,
@ObjectModel.foreignKey.association: '_SalesGroup'
DDI.SalesGroup,
@ObjectModel.foreignKey.association: '_SalesOffice'
DDI.SalesOffice,
DDI.DepartmentClassificationByCust,
//Product --AT*2 17.2.2020 add Product
@ObjectModel.foreignKey.association: '_Material'
@VDM.lifecycle.status: #DEPRECATED
@VDM.lifecycle.successor: 'Product'
DDI.Material,
@Analytics.internalName: #LOCAL
@ObjectModel.foreignKey.association: '_Product'
@VDM.lifecycle: { status: #DEPRECATED, successor: '_Product' }
DDI.Product,
DDI.MaterialByCustomer,
@ObjectModel.foreignKey.association: '_OriginallyRequestedMaterial'
DDI.OriginallyRequestedMaterial,
DDI.InternationalArticleNumber,
DDI.Batch,
DDI.BatchClassification,
DDI.BatchBySupplier,
DDI.MaterialIsIntBatchManaged,
DDI.MaterialIsBatchManaged,
@ObjectModel.foreignKey.association: '_MaterialGroup'
DDI.MaterialGroup,
@Analytics.internalName: #LOCAL
@ObjectModel.foreignKey.association: '_ProductGroup'
DDI.ProductGroup,
@ObjectModel.foreignKey.association: '_MaterialFreightGroup'
DDI.MaterialFreightGroup,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup1'
DDI.AdditionalMaterialGroup1,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup2'
DDI.AdditionalMaterialGroup2,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup3'
DDI.AdditionalMaterialGroup3,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup4'
DDI.AdditionalMaterialGroup4,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup5'
DDI.AdditionalMaterialGroup5,
@ObjectModel.foreignKey.association: '_Plant'
DDI.Plant,
DDI._Plant.PlantName,
@ObjectModel.foreignKey.association: '_StorageLocation'
DDI.StorageLocation,
DDI._StorageLocation.StorageLocationName,
@ObjectModel.foreignKey.association: '_Warehouse'
DDI.Warehouse,
DDI.StorageBin,
DDI.StorageType,
@ObjectModel.foreignKey.association: '_InventorySpecialStockType'
DDI.InventorySpecialStockType,
DDI.ShelfLifeExpirationDate,
DDI.NumberOfSerialNumbers,
DDI.ProductConfiguration,
DDI.ProductHierarchyNode,
DDI.ManufactureDate,
//delivery
DDI.DeliveryDocumentItemText,
DDI.HigherLevelItem,
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'DeliveryQuantityUnit'
DDI.ActualDeliveryQuantity,
DDI.QuantityIsFixed,
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'DeliveryQuantityUnit'
DDI.OriginalDeliveryQuantity,
@Semantics.unitOfMeasure: true
@ObjectModel.foreignKey.association: '_DeliveryQuantityUnit'
DDI.DeliveryQuantityUnit,
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'BaseUnit'
DDI.ActualDeliveredQtyInBaseUnit,
// @Semantics.unitOfMeasure: true //See Below in //Measures
// @ObjectModel.foreignKey.association: '_BaseUnit'
// DDI.BaseUnit,
DDI.DeliveryToBaseQuantityDnmntr,
DDI.DeliveryToBaseQuantityNmrtr,
DDI.ProductAvailabilityDate,
DDI.ProductAvailabilityTime,
DDI.DeliveryGroup,
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'ItemWeightUnit'
DDI.ItemGrossWeight,
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'ItemWeightUnit'
DDI.ItemNetWeight,
@Semantics.unitOfMeasure: true
@ObjectModel.foreignKey.association: '_ItemWeightUnit'
DDI.ItemWeightUnit,
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'ItemVolumeUnit'
DDI.ItemVolume,
@Semantics.unitOfMeasure: true
@ObjectModel.foreignKey.association: '_ItemVolumeUnit'
DDI.ItemVolumeUnit,
DDI.InspectionLot,
DDI.InspectionPartialLot,
DDI.PartialDeliveryIsAllowed,
DDI.UnlimitedOverdeliveryIsAllowed,
DDI.OverdelivTolrtdLmtRatioInPct,
DDI.UnderdelivTolrtdLmtRatioInPct,
DDI.WarehouseStorageBin,
@ObjectModel.foreignKey.association: '_BOMExplosion'
DDI.BOMExplosion,
DDI.CustEngineeringChgStatus,
@ObjectModel.foreignKey.association: '_WarehouseStagingArea'
DDI.WarehouseStagingArea,
DDI.WarehouseStockCategory,
DDI.IssgOrRcvgStockCategory,
DDI.IssgOrRcvgMaterial,
DDI.IssuingOrReceivingPlant,
DDI.IssuingOrReceivingStorageLoc,
DDI.IssgOrRcvgBatch,
DDI.IssgOrRcvgValuationType,
DDI.IssgOrRcvgSpclStockInd,
DDI.PrimaryPostingSwitch,
DDI.StockType,
DDI.IsNotGoodsMovementsRelevant,
DDI.GLAccount,
DDI.GoodsMovementReasonCode,
DDI.SubsequentMovementType,
DDI.IsCompletelyDelivered,
DDI.AlternateProductNumber,
//pick pack load
@ObjectModel.foreignKey.association: '_PickingControl'
DDI.PickingControl,
@ObjectModel.foreignKey.association: '_LoadingGroup'
DDI.LoadingGroup,
@ObjectModel.foreignKey.association: '_GoodsMovementType'
DDI.GoodsMovementType,
//shipping
@ObjectModel.foreignKey.association: '_TransportationGroup'
DDI.TransportationGroup,
DDI.ReceivingPoint,
DDI.FixedShipgProcgDurationInDays,
DDI.VarblShipgProcgDurationInDays,
DDI.ProofOfDeliveryRelevanceCode,
//billing
@ObjectModel.foreignKey.association: '_ItemIsBillingRelevant'
DDI.ItemIsBillingRelevant,
@ObjectModel.foreignKey.association: '_ItemBillingBlockReason'
DDI.ItemBillingBlockReason,
@ObjectModel.foreignKey.association: '_PaymentGuaranteeForm'
DDI.PaymentGuaranteeForm,
DDI.IntercompanyBillingStatus,
//accounting
@ObjectModel.foreignKey.association: '_BusinessArea'
DDI.BusinessArea,
@ObjectModel.foreignKey.association: '_ControllingArea'
DDI.ControllingArea,
DDI.ProfitabilitySegment_2 as ProfitabilitySegment,
DDI.ProfitCenter,
--@ObjectModel.foreignKey.association: '_InventoryValuationType' -- AT 24.8.16 commented due to false positive ATC check POC_ANNOTA, OMFK
DDI.InventoryValuationType,
DDI.IsSeparateValuation,
DDI.ConsumptionPosting,
@ObjectModel.foreignKey.association: '_OrderID'
DDI.OrderID,
DDI.OrderItem,
DDI.CostCenter,
//reference
@ObjectModel.foreignKey.association: '_ReferenceSDDocument'
DDI.ReferenceSDDocument,
@ObjectModel.foreignKey.association: '_ReferenceSalesDocumentItem'
DDI.ReferenceSDDocumentItem,
@ObjectModel.foreignKey.association: '_ReferenceSDDocumentCategory'
DDI.ReferenceSDDocumentCategory,
@ObjectModel.foreignKey.association: '_LogicalSystem'
DDI.ReferenceDocumentLogicalSystem,
//sales
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup1'
DDI.AdditionalCustomerGroup1,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup2'
DDI.AdditionalCustomerGroup2,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup3'
DDI.AdditionalCustomerGroup3,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup4'
DDI.AdditionalCustomerGroup4,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup5'
DDI.AdditionalCustomerGroup5,
DDI.RetailPromotion,
DDI.StatisticsDate,
//status
@ObjectModel.foreignKey.association: '_SDProcessStatus'
DDI.SDProcessStatus,
@ObjectModel.foreignKey.association: '_PickingConfirmationStatus'
DDI.PickingConfirmationStatus,
@ObjectModel.foreignKey.association: '_PickingStatus'
DDI.PickingStatus,
@ObjectModel.foreignKey.association: '_WarehouseActivityStatus'
DDI.WarehouseActivityStatus,
@ObjectModel.foreignKey.association: '_PackingStatus'
DDI.PackingStatus,
@ObjectModel.foreignKey.association: '_GoodsMovementStatus'
DDI.GoodsMovementStatus,
@ObjectModel.foreignKey.association: '_DeliveryRelatedBillingStatus'
DDI.DeliveryRelatedBillingStatus,
@ObjectModel.foreignKey.association: '_ProofOfDeliveryStatus'
DDI.ProofOfDeliveryStatus,
@ObjectModel.foreignKey.association: '_ItemGeneralIncompletionStatus'
DDI.ItemGeneralIncompletionStatus,
@ObjectModel.foreignKey.association: '_ItemDelivIncompletionSts'
DDI.ItemDeliveryIncompletionStatus,
@ObjectModel.foreignKey.association: '_ItemPickingIncompletionStatus'
DDI.ItemPickingIncompletionStatus,
@ObjectModel.foreignKey.association: '_ItemGdsMvtIncompletionSts'
DDI.ItemGdsMvtIncompletionSts,
@ObjectModel.foreignKey.association: '_ItemPackingIncompletionStatus'
DDI.ItemPackingIncompletionStatus,
@ObjectModel.foreignKey.association: '_ItemBillingIncompletionStatus'
DDI.ItemBillingIncompletionStatus,
//Customer
@ObjectModel.foreignKey.association: '_SoldToParty'
DDI._DeliveryDocument.SoldToParty,
cast(DDI._DeliveryDocument._SoldToParty.CustomerName as vdm_sold_to_name) as SoldToPartyName,
@ObjectModel.foreignKey.association: '_CustomerGroup'
DDI._DeliveryDocument.CustomerGroup,
DDI._DeliveryDocument._SoldToParty.CustomerClassification as SoldToPartyClassification,
//Geographics
@ObjectModel.foreignKey.association: '_SalesDistrict'
DDI._DeliveryDocument.SalesDistrict,
// Misc Attributes (for DCL, I_CalenderDate-join)
@ObjectModel.foreignKey.association: '_ShippingPoint'
DDI._DeliveryDocument.ShippingPoint, -- DCL Check for DeliveryDocument
DDI._DeliveryDocument.BillingDocumentDate, -- For Join with I_CalendarDate and CurrencyConversion
//Measures
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'TransactionCurrency'
case when SDI.IsReturnsItem = '' and
( DDI.DeliveryRelatedBillingStatus = 'A' or
DDI.DeliveryRelatedBillingStatus = 'B'
) and
( DDI.HigherLvlItmOfBatSpltItm is initial or --only NORMAL entries --AT 21.09.2018 Incident 484039 Batch Handling
DDI_SUPER.DeliveryRelatedBillingStatus is initial --only BATCH (SUBordonated) entries with a SUPER entry that is not billing relevant
)
then
case when SDI.RequestedQuantityInBaseUnit > 0
then
--AT 21.09.2018 Incident 484039 Batch Handling
--cast(division(abs(SDI.NetAmount) * DDI.ActualDeliveredQtyInBaseUnit, SDI.RequestedQuantityInBaseUnit,3 ) as opn_dlv_for_inv_net_amt)
cast(division(abs(SDI.NetAmount) * cast( ( DDI.ActualDeliveredQtyInBaseUnit + DDI.CumulativeBatchQtyInBaseUnit ) as opn_shppd_dlv_for_inv_qty), SDI.RequestedQuantityInBaseUnit,3 ) as opn_dlv_for_inv_net_amt)
else cast(0 as opn_dlv_for_inv_net_amt )
end
end
as OpnOutbDelivsForInvcNetAmt,
--AT8.2.22: intro of enlarged successors (->wertv10)
@VDM.lifecycle: { status: #DEPRECATED, successor: 'ShpdNotInvcdDelivsNetAmount_2' }
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'TransactionCurrency'
case when SDI.IsReturnsItem = '' and
( DDI.DeliveryRelatedBillingStatus = 'A' or
DDI.DeliveryRelatedBillingStatus = 'B'
) and
DDI.GoodsMovementStatus = 'C' and
( DDI.HigherLvlItmOfBatSpltItm is initial or --only NORMAL entries --AT 21.09.2018 Incident 484039 Batch Handling
DDI_SUPER.DeliveryRelatedBillingStatus is initial --only BATCH (SSUBordonated) entries with a SUPER entry that is not billing relevant
)
then
case when SDI.RequestedQuantityInBaseUnit > 0
then
--AT 21.09.2018 Incident 484039 Batch Handling
--cast(division(abs(SDI.NetAmount) * DDI.ActualDeliveredQtyInBaseUnit , SDI.RequestedQuantityInBaseUnit,3 ) as opn_dlv_for_inv_net_amt)
cast(division(abs(SDI.NetAmount) * cast( ( DDI.ActualDeliveredQtyInBaseUnit + DDI.CumulativeBatchQtyInBaseUnit ) as opn_shppd_dlv_for_inv_qty) , SDI.RequestedQuantityInBaseUnit,3 ) as opn_shppd_dlv_for_inv_net_shor) -- middle cast prevents type conflict
else cast(0 as opn_shppd_dlv_for_inv_net_shor )
end
end --Wertv8
as ShpdNotInvcdDelivsNetAmount, --AT 25.4.2018: new for CE1808
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'TransactionCurrency'
@Analytics.internalName: #LOCAL
case when SDI.IsReturnsItem = '' and
( DDI.DeliveryRelatedBillingStatus = 'A' or
DDI.DeliveryRelatedBillingStatus = 'B'
) and
DDI.GoodsMovementStatus = 'C' and
( DDI.HigherLvlItmOfBatSpltItm is initial or --only NORMAL entries --AT 21.09.2018 Incident 484039 Batch Handling
DDI_SUPER.DeliveryRelatedBillingStatus is initial --only BATCH (SSUBordonated) entries with a SUPER entry that is not billing relevant
)
then
case when SDI.RequestedQuantityInBaseUnit > 0
then
--AT 21.09.2018 Incident 484039 Batch Handling
--cast(division(abs(SDI.NetAmount) * DDI.ActualDeliveredQtyInBaseUnit , SDI.RequestedQuantityInBaseUnit,3 ) as opn_dlv_for_inv_net_amt)
cast(division(abs(SDI.NetAmount) * cast( ( DDI.ActualDeliveredQtyInBaseUnit + DDI.CumulativeBatchQtyInBaseUnit ) as opn_shppd_dlv_for_inv_qty) , SDI.RequestedQuantityInBaseUnit,3 ) as opn_shppd_dlv_for_inv_net_amt) -- middle cast prevents type conflict
else cast(0 as opn_shppd_dlv_for_inv_net_amt )
end
end --Wertv10
as ShpdNotInvcdDelivsNetAmount_2,
// ----------------------------END--------------------
@Semantics.currencyCode: true
@ObjectModel.foreignKey.association: '_TransactionCurrency'
SDI.TransactionCurrency,
@Semantics.currencyCode: true
@ObjectModel.foreignKey.association: '_StatisticsCurrency'
_DeliveryDocument._SalesOrganization.SalesOrganizationCurrency as StatisticsCurrency, --Note: take statistical currency from central tvko (versus document)
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'BaseUnit'
case when SDI.IsReturnsItem = '' and
( DDI.DeliveryRelatedBillingStatus = 'A' or
DDI.DeliveryRelatedBillingStatus = 'B'
) and
( DDI.HigherLvlItmOfBatSpltItm is initial or --only NORMAL entries --AT 21.09.2018 Incident 484039 Batch Handling
DDI_SUPER.DeliveryRelatedBillingStatus is initial --only BATCH (SUBordonated) entries with a SUPER entry that is not billing relevant
)
then
cast( ( DDI.ActualDeliveredQtyInBaseUnit + DDI.CumulativeBatchQtyInBaseUnit ) as opn_shppd_dlv_for_inv_qty) --AT 21.09.2018 Incident 484039 Batch Handling
end
as OpnOutbDelivsForInvcQty,
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'BaseUnit'
case when SDI.IsReturnsItem = '' and
( DDI.DeliveryRelatedBillingStatus = 'A' or
DDI.DeliveryRelatedBillingStatus = 'B'
) and
DDI.GoodsMovementStatus = 'C' and
( DDI.HigherLvlItmOfBatSpltItm is initial or --only NORMAL entries --AT 21.09.2018 Incident 484039 Batch Handling
DDI_SUPER.DeliveryRelatedBillingStatus is initial --only BATCH (SUBordonated) entries with a SUPER entry that is not billing relevant
)
then
cast( ( DDI.ActualDeliveredQtyInBaseUnit + DDI.CumulativeBatchQtyInBaseUnit ) as opn_shppd_dlv_for_inv_qty) --AT 21.09.2018 Incident 484039 Batch Handling
end
as ShpdNotInvcdDelivsQuantity, --AT 25.4.2018: new for CE1808
@Semantics.unitOfMeasure: true
@ObjectModel.foreignKey.association: '_BaseUnit'
DDI.BaseUnit,
//Associations
@ObjectModel.association.type: [#TO_COMPOSITION_PARENT, #TO_COMPOSITION_ROOT]
DDI._DeliveryDocument,
DDI._Partner,
DDI._SDDocumentCategory,
DDI._ItemCategory,
DDI._SalesDocumentItemType,
DDI._CreatedByUser,
DDI._DistributionChannel,
DDI._Division,
DDI._SalesGroup,
DDI._SalesOffice,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_Product' }
DDI._Material,
DDI._Product,
DDI._OriginallyRequestedMaterial,
DDI._ProductHierarchyNode,
@VDM.lifecycle: { status: #DEPRECATED, successor: '_ProductGroup' }
DDI._MaterialGroup,
DDI._ProductGroup,
DDI._MaterialFreightGroup,
DDI._AdditionalMaterialGroup1,
DDI._AdditionalMaterialGroup2,
DDI._AdditionalMaterialGroup3,
DDI._AdditionalMaterialGroup4,
DDI._AdditionalMaterialGroup5,
DDI._Plant,
DDI._Warehouse,
DDI._StorageLocation,
DDI._InventorySpecialStockType,
DDI._DeliveryQuantityUnit,
DDI._BaseUnit,
DDI._ItemWeightUnit,
DDI._ItemVolumeUnit,
DDI._BOMExplosion,
DDI._WarehouseStagingArea,
DDI._PickingControl,
DDI._LoadingGroup,
DDI._GoodsMovementType,
DDI._TransportationGroup,
DDI._ItemIsBillingRelevant,
DDI._ItemBillingBlockReason,
DDI._PaymentGuaranteeForm,
DDI._BusinessArea,
DDI._ControllingArea,
DDI._ProfitCenter,
DDI._InventoryValuationType,
DDI._OrderID,
DDI._CostCenter,
DDI._ReferenceSDDocument,
DDI._ReferenceSalesDocumentItem,
DDI._ReferenceSDDocumentCategory,
DDI._LogicalSystem,
DDI._AdditionalCustomerGroup1,
DDI._AdditionalCustomerGroup2,
DDI._AdditionalCustomerGroup3,
DDI._AdditionalCustomerGroup4,
DDI._AdditionalCustomerGroup5,
DDI._SDProcessStatus,
DDI._PickingConfirmationStatus,
DDI._PickingStatus,
DDI._WarehouseActivityStatus,
DDI._PackingStatus,
DDI._GoodsMovementStatus,
DDI._DeliveryRelatedBillingStatus,
DDI._ProofOfDeliveryStatus,
DDI._ItemGeneralIncompletionStatus,
DDI._ItemDelivIncompletionSts,
DDI._ItemPickingIncompletionStatus,
DDI._ItemGdsMvtIncompletionSts,
DDI._ItemPackingIncompletionStatus,
DDI._ItemBillingIncompletionStatus,
// some further associations (historical reasons, don't use them !)
DDI._DeliveryDocument._SalesOrganization,
DDI._DeliveryDocument._CustomerGroup,
DDI._DeliveryDocument._SalesDistrict,
DDI._DeliveryDocument._ShippingPoint,
DDI._DeliveryDocument._SoldToParty,
DDI._DeliveryDocument._SoldToParty._CustomerClassification,
_StatisticsCurrency,
SDI._TransactionCurrency
}
where SDI.StatisticalValueControl = ''