@EndUserText.label: 'Transp Order Analytics Preparation'
@Metadata: {allowExtensions: true,
ignorePropagatedAnnotations: true}
@AccessControl: {authorizationCheck: #MANDATORY,
personalData.blocking: #('TRANSACTIONAL_DATA')}
@ObjectModel: {representativeKey: 'TransportationOrderUUID',
usageType: {serviceQuality: #D,
sizeCategory: #XXL,
dataClass: #MIXED}}
@VDM: {viewType: #COMPOSITE}
@Analytics: {dataCategory: #DIMENSION,
internalName: #LOCAL}
define view entity I_TranspOrdCubePrep
as select from I_TransportationOrderAnalytics
association [0..1] to I_TranspOrdStopAnalytics as _TranspOrdSourceStop on $projection.TransportationOrderUUID = _TranspOrdSourceStop.TransportationOrderUUID
and _TranspOrdSourceStop.TranspOrdStopSequencePosition = 'F'
association [0..1] to I_TranspOrdStopAnalytics as _TranspOrdDestinationStop on $projection.TransportationOrderUUID = _TranspOrdDestinationStop.TransportationOrderUUID
and _TranspOrdDestinationStop.TranspOrdStopSequencePosition = 'L'
association [0..1] to I_TranspOrdStopAnalytics as _TranspOrdMainLegStartStop on $projection.TransportationOrderUUID = _TranspOrdMainLegStartStop.TransportationOrderUUID
and _TranspOrdMainLegStartStop.TranspOrdStopCategory = 'O'
and _TranspOrdMainLegStartStop.TranspOrdStopRole = 'MS'
association [0..1] to I_TranspOrdStopAnalytics as _TranspOrdMainLegEndStop on $projection.TransportationOrderUUID = _TranspOrdMainLegEndStop.TransportationOrderUUID
and _TranspOrdMainLegEndStop.TranspOrdStopCategory = 'I'
and _TranspOrdMainLegEndStop.TranspOrdStopRole = 'ME'
// association [1..1] to P_TranspOrdArrvlEvtSumDelay as _TranspOrdSumArrivalDelay on $projection.TransportationOrderUUID = _TranspOrdSumArrivalDelay.TransportationOrderUUID
{
key TransportationOrderUUID,
TransportationOrder,
@ObjectModel.foreignKey.association: '_TransportationOrderCategory'
TransportationOrderCategory,
@ObjectModel.foreignKey.association: '_TransportationOrderType'
TransportationOrderType,
@ObjectModel.foreignKey.association: '_TranspOrdShippingType'
TranspOrdShippingType,
@ObjectModel.foreignKey.association: '_MovementType'
MovementType,
@ObjectModel.foreignKey.association: '_TransportationMode'
TransportationMode,
@ObjectModel.foreignKey.association: '_TransportationModeCategory'
TransportationModeCategory,
/* Admin */
@Semantics.user.createdBy: true
@ObjectModel.foreignKey.association: '_CreatedBy'
CreatedByUser,
TranspOrdCreationDateTime,
TranspOrdOrderDateTime,
/* Business Partner */
@ObjectModel.foreignKey.association: '_Carrier'
Carrier,
@Semantics.uuid: true
CarrierUUID,
@ObjectModel.foreignKey.association: '_TranspSCACCode'
cast(StandardCarrierAlphaCode as /scmtms/scacd preserving type) as TranspSCACCode,
@ObjectModel.foreignKey.association: '_Shipper'
Shipper,
@Semantics.uuid: true
ShipperUUID,
@ObjectModel.foreignKey.association: '_Consignee'
Consignee,
@Semantics.uuid: true
ConsigneeUUID,
MasterBillOfLading,
/* Locations */
cast(_TranspOrdSourceStop.TranspOrdStopLocation as /scmtms/vdm_src_location preserving type) as TranspOrdSrceLocation,
cast(_TranspOrdDestinationStop.TranspOrdStopLocation as /scmtms/vdm_dest_location preserving type) as TranspOrdDestLocation,
/* Date/Times for Locations */
cast(_TranspOrdSourceStop.TranspOrdStopDteTme as /scmtms/vdm_departure_datetime preserving type) as TranspOrdDepartureDateTime,
cast(_TranspOrdDestinationStop.TranspOrdStopDteTme as /scmtms/vdm_arrival_datetime preserving type) as TranspOrdArrivalDateTime,
/* OrgUnits for DCL, without foreign key association */
PlngAndExecOrgExternalID,
PlngAndExecGroupExternalID,
PurchasingOrgExternalID,
PurchasingGroupExternalID,
TranspOrdResponsiblePerson,
/* Status Information */
@ObjectModel.foreignKey.association: '_TranspOrdLifeCycleStatus'
TranspOrdLifeCycleStatus,
@ObjectModel.foreignKey.association: '_TransportationOrderExecSts'
TransportationOrderExecSts,
@ObjectModel.foreignKey.association: '_TranspOrdDngrsGdsStatus'
TranspOrderDngrsGdsSts,
// @ObjectModel.foreignKey.association: '_TranspOrdSubcontractingSts' --> not yet C1 released
TranspOrderSubcontrgSts,
// @ObjectModel.foreignKey.association: '_TranspOrdConfirmationStatus' --> not yet C1 released
TransportationOrderConfSts,
// @ObjectModel.foreignKey.association: '_TranspOrdGoodsMvtStatus' --> not yet C1 released
TranspOrdGoodsMovementStatus,
// @ObjectModel.foreignKey.association: '_TranspOrdWhseProcgStatus' --> not yet C1 released
TranspOrdWhseProcessingStatus,
/* Block Information */
@Semantics.booleanIndicator:true
@ObjectModel.foreignKey.association: '_TranspOrdExecutionIsBlocked'
TranspOrdExecutionIsBlocked,
@Semantics.booleanIndicator:true
@ObjectModel.foreignKey.association: '_InvoicingIsBlocked'
InvoicingIsBlocked,
/* KPIs */
NumberOfTransportationOrders,
/* Ratio in Percent */
@Aggregation.default: #MAX
@Semantics.quantity.unitOfMeasure: 'TranspOrdUtilznUnit'
TranspOrdMaxUtilznRatio, // contains Max value of Mass and Volume and Length Ratio
cast(cast('%' as abap.unit(3)) as /scmtms/vdm_util_percent_unit preserving type) as TranspOrdUtilznUnit,
/* Measures and Units */
@Aggregation.default: #SUM
@Semantics.quantity.unitOfMeasure: 'TranspOrdGrossWeightUnit'
TranspOrdGrossWeight,
@ObjectModel.foreignKey.association: '_TranspOrdGrossWeightUnit'
TranspOrdGrossWeightUnit,
@Aggregation.default: #SUM
@Semantics.quantity.unitOfMeasure: 'TranspOrdGrossVolumeUnit'
TranspOrdGrossVolume,
@ObjectModel.foreignKey.association: '_TranspOrdGrossVolumeUnit'
TranspOrdGrossVolumeUnit,
@Aggregation.default: #SUM
@Semantics.quantity.unitOfMeasure: 'TranspOrdQuantityUnit'
TranspOrdQuantity,
@ObjectModel.foreignKey.association: '_TranspOrdQuantityUnit'
TranspOrdQuantityUnit,
@Aggregation.default: #SUM
@Semantics.quantity.unitOfMeasure: 'TranspOrdTEUQuantityUnit'
cast(TranspOrdNumberOfCtns as /scmtms/vdm_tor_cont_count_val preserving type) as TranspOrdTEUQuantity,
@ObjectModel.foreignKey.association: '_TranspOrdTEUQuantityUnit'
cast(TranspOrdNumberOfCtnsUnit as /scmtms/cont_count_uni preserving type) as TranspOrdTEUQuantityUnit,
// @Aggregation.default: #SUM
// hhhhhhh:mm:ss --> Summierung nicht so möglich, erst umrechnen
// DB: TSTRDURAN (mit conversion exit!)
// GFN: /scmtms/vdm_tor_net_duration (TSTRDURAH ohne conversion exit)
TranspOrdNetDuration,
@Aggregation.default: #SUM
@Semantics.quantity.unitOfMeasure: 'TranspOrdNetDurationUnit'
cast( ( div( TranspOrdNetDuration, 10000) )
+ ( division( mod( div( cast(TranspOrdNetDuration as abap.int8), 100), 100 ), 60,5) )
+ ( division( mod( cast(TranspOrdNetDuration as abap.int8),100), 3600, 5) )
as /scmtms/vdm_tor_net_dur_in_hrs ) as TranspOrdNetDurnInHours,
cast(cast('H' as abap.unit(3)) as /scmtms/vdm_net_duration_unit preserving type) as TranspOrdNetDurationUnit,
// /* TOR is delayed if at least 1 Arrival with delay (expd. - actual) */
// @Semantics.booleanIndicator: true
// cast(case when (_TranspOrdSumArrivalDelay.NumberOfTranspOrdEvents > 0 and
// _TranspOrdSumArrivalDelay.TranspOrdEventDelayInHours > 0)
// then 'X'
// else ''
// end as /scmtms/vdm_tor_is_delayed preserving type) as TranspOrdIsDelayed,
/* Associations */
_TranspOrdItemAnalytics,
_TranspOrdStopAnalytics,
_TranspOrdStageAnalytics,
_TranspOrdExecutionAnalytics,
_TranspOrdSourceStop,
_TranspOrdDestinationStop,
_TranspOrdMainLegStartStop,
_TranspOrdMainLegEndStop,
_TransportationOrderCategory,
_TransportationOrderType,
_TranspOrdShippingType,
_MovementType,
_Carrier,
_TranspSCACCode,
_Shipper,
_Consignee,
_TranspOrdLifeCycleStatus,
_TransportationOrderExecSts,
_TranspOrdDngrsGdsStatus,
// _TranspOrdSubcontractingSts,
// _TranspOrdConfirmationStatus,
// _TranspOrdGoodsMvtStatus,
// _TranspOrdWhseProcgStatus,
_TranspOrdExecutionIsBlocked,
_InvoicingIsBlocked,
_TransportationMode,
_TransportationModeCategory,
_CreatedBy,
_TranspOrdGrossWeightUnit,
_TranspOrdGrossVolumeUnit,
_TranspOrdQuantityUnit,
_TranspOrdNumberOfCtnsUnit as _TranspOrdTEUQuantityUnit
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_TRANSPORDSTOPANALYTICS",
"I_TRANSPORTATIONORDERANALYTICS"
],
"ASSOCIATED":
[
"I_BUSINESSPARTNER",
"I_INVOICINGISBLOCKED",
"I_TRANSPMOVEMENTTYPE",
"I_TRANSPORDDNGRSGDSSTATUS",
"I_TRANSPORDEXECUTIONANALYTICS",
"I_TRANSPORDEXECUTIONISBLOCKED",
"I_TRANSPORDITEMANALYTICS",
"I_TRANSPORDLIFECYCLESTATUS",
"I_TRANSPORDSHIPPINGTYPE",
"I_TRANSPORDSTAGEANALYTICS",
"I_TRANSPORDSTOPANALYTICS",
"I_TRANSPORTATIONMODE",
"I_TRANSPORTATIONMODECATEGORY",
"I_TRANSPORTATIONORDERCATEGORY",
"I_TRANSPORTATIONORDEREXECSTS",
"I_TRANSPORTATIONORDERTYPE",
"I_TRANSPSCACCODE",
"I_UNITOFMEASURE",
"I_USER"
],
"BASE":
[
"I_TRANSPORTATIONORDERANALYTICS"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/