I_TranspOrdForBusDshbrd

@EndUserText.label:          'TOR Root Analytics for SAC Dashboard'
@ClientHandling.algorithm:   #SESSION_VARIABLE

@Metadata:          {allowExtensions:             true,
                     ignorePropagatedAnnotations: true}

@AbapCatalog:       {sqlViewName:                 'ITORROOTSACANA',
                     compiler.compareFilter:      true,
                     buffering:                   {status: #NOT_ALLOWED},
                     preserveKey:                 true}

@AccessControl:     {authorizationCheck:          #CHECK,
                     personalData.blocking:       #('TRANSACTIONAL_DATA')
                    }

@ObjectModel:       {representativeKey:           'TransportationOrderUUID',
                     usageType:                   {serviceQuality: #D,
                                                   sizeCategory:   #XXL,
                                                   dataClass:      #MIXED}}

@VDM:               {viewType:                    #COMPOSITE}

@Analytics:         {dataCategory:                #DIMENSION,
                     internalName:                #LOCAL}

@VDM.lifecycle.status:#DEPRECATED


define view I_TranspOrdForBusDshbrd
  as select from I_TransportationOrderAnalytics
  association [0..1] to I_CalendarDate             as _TranspOrdCreationDate    on  $projection.TranspOrdCreationDate = _TranspOrdCreationDate.CalendarDate
  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 [1..1] to P_TranspOrdSumArrivalDelay as _TranspOrdSumArrivalDelay on  $projection.TransportationOrderUUID = _TranspOrdSumArrivalDelay.TransportationOrderUUID
{
  key TransportationOrderUUID,

      TransportationOrder,
      @ObjectModel.foreignKey.association: '_TransportationOrderCategory'
      TransportationOrderCategory,
      @ObjectModel.foreignKey.association: '_TransportationOrderType'
      TransportationOrderType,
      NumberOfTransportationOrders,

      /* Admin */
      @Semantics.user.createdBy: true
      CreatedByUser,
      TranspOrdCreationDateTime,
      @Semantics.businessDate.at: true
      TranspOrdCreationDate,

      /* Get Time Periods */
      @Semantics.calendar.yearWeek:    true
      cast(_TranspOrdCreationDate.YearWeek      as /scmtms/vdm_creation_week preserving type)            as TranspOrdCreationYearWeek,
      @Semantics.calendar.yearMonth:   true
      cast(_TranspOrdCreationDate.YearMonth     as /scmtms/vdm_creation_month preserving type)           as TranspOrdCreationYearMonth,
      @Semantics.calendar.yearQuarter: true
      cast(_TranspOrdCreationDate.YearQuarter   as /scmtms/vdm_creation_quarter preserving type)         as TranspOrdCreationYearQuarter,
      @Semantics.calendar.year:        true
      cast(_TranspOrdCreationDate.CalendarYear  as /scmtms/vdm_creation_year preserving type)            as TranspOrdCreationYear,

      /* Business Partner */
      @ObjectModel.foreignKey.association: '_Carrier'
      Carrier,
      CarrierUUID,
      TranspOrdResponsiblePerson,
      /* OrgUnits for DCL, without foreign key association */
      PlngAndExecOrgExternalID,
      PlngAndExecGroupExternalID,
      PurchasingOrgExternalID,
      PurchasingGroupExternalID,

      /* Status Information */
      @ObjectModel.foreignKey.association: '_TranspOrdLifeCycleStatus'
      TranspOrdLifeCycleStatus,
      @ObjectModel.foreignKey.association: '_TranspOrdPlanningStatus'
      TranspOrdPlanningStatus,
      @ObjectModel.foreignKey.association: '_TransportationOrderExecSts'
      TransportationOrderExecSts,

      /* Transportation Information */
      @ObjectModel.foreignKey.association: '_TransportationMode'
      TransportationMode,
      @ObjectModel.foreignKey.association: '_TransportationModeCategory'
      TransportationModeCategory,

      /* Locatons */
      cast(_TranspOrdSourceStop.TranspOrdStopLocation as /scmtms/vdm_src_location preserving type)       as TranspOrdSrceLocation,
      cast(_TranspOrdDestinationStop.TranspOrdStopLocation as /scmtms/vdm_dest_location preserving type) as TranspOrdDestLocation,

      /*  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,


      /* Ratio in Percent */
      @DefaultAggregation: #MAX
      @Semantics.quantity.unitOfMeasure: 'TranspOrdUtilznUnit'
      TranspOrdMaxUtilznMassRatio,

      @DefaultAggregation: #MAX
      @Semantics.quantity.unitOfMeasure: 'TranspOrdUtilznUnit'
      TranspOrdMaxUtilznVolumeRatio,

      @DefaultAggregation: #MAX
      @Semantics.quantity.unitOfMeasure: 'TranspOrdUtilznUnit'
      TranspOrdMaxUtilznLengthRatio,

      @Semantics.unitOfMeasure: true
      cast(cast('%' as abap.unit(3)) as /scmtms/vdm_util_percent_unit preserving type)                   as TranspOrdUtilznUnit,

      /* Measures and Units */
      @DefaultAggregation: #SUM
      @Semantics.quantity.unitOfMeasure: 'TranspOrdGrossWeightUnit'
      TranspOrdGrossWeight,
      @ObjectModel.foreignKey.association: '_TranspOrdGrossWeightUnit'
      @Semantics.unitOfMeasure: true
      TranspOrdGrossWeightUnit,

      @DefaultAggregation: #SUM
      @Semantics.quantity.unitOfMeasure: 'TranspOrdGrossVolumeUnit'
      TranspOrdGrossVolume,
      @ObjectModel.foreignKey.association: '_TranspOrdGrossVolumeUnit'
      @Semantics.unitOfMeasure: true
      TranspOrdGrossVolumeUnit,

      @DefaultAggregation: #SUM
      @Semantics.quantity.unitOfMeasure: 'TranspOrdQuantityUnit'
      TranspOrdQuantity,
      @ObjectModel.foreignKey.association: '_TranspOrdQuantityUnit'
      @Semantics.unitOfMeasure: true
      TranspOrdQuantityUnit,

      @DefaultAggregation: #SUM
      @Semantics.quantity.unitOfMeasure: 'TranspOrdNumberOfCtnsUnit'
      TranspOrdNumberOfCtns,
      @ObjectModel.foreignKey.association: '_TranspOrdNumberOfCtnsUnit'
      @Semantics.unitOfMeasure: true
      TranspOrdNumberOfCtnsUnit,

      @DefaultAggregation: #SUM
      @Semantics.quantity.unitOfMeasure: 'TranspOrdDistanceUnit'
      TranspOrdDistance,
      @Semantics.unitOfMeasure: true
      TranspOrdDistanceUnit,

      @DefaultAggregation: #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,

      @Semantics.unitOfMeasure: true
      cast(cast('H' as abap.unit(3)) as /scmtms/vdm_net_duration_unit preserving type)                   as TranspOrdNetDurationUnit,


      /* Associations */
      _TranspOrdItemAnalytics,
      _TranspOrdStopAnalytics,
      _TranspOrdStageAnalytics,
      _TranspOrdExecutionAnalytics,
      _Carrier,

      _TransportationOrderCategory,
      _TransportationOrderType,

      _TranspOrdLifeCycleStatus,
      _TranspOrdPlanningStatus,
      _TransportationOrderExecSts,

      _TransportationMode,
      _TransportationModeCategory,

      _TranspOrdGrossWeightUnit,
      _TranspOrdGrossVolumeUnit,
      _TranspOrdQuantityUnit,
      _TranspOrdNumberOfCtnsUnit,

      _TranspOrdSourceStop,
      _TranspOrdDestinationStop
}