I_FrtOrdWithDsputForDshbrd

@EndUserText.label: 'Frt Ord with Dsput Anlyts f SAC Dashbrd'

@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}


define view entity I_FrtOrdWithDsputForDshbrd
  as select from I_TransportationOrderAnalytics as _TransportationOrderAnalytics
    inner join   I_TranspChargeObject           as _TranspChargeObject on  _TransportationOrderAnalytics.TransportationOrderUUID =  _TranspChargeObject.TranspChargeHostDocumentUUID
                                                                       and (
                                                                          _TranspChargeObject.InvoicingStatus                    =  '01' // Not Invoiced (FO does not contain Charges, only FSD)

                                                                          or _TranspChargeObject.InvoicingStatus                 =  '03' // Partially Invoiced

                                                                          or _TranspChargeObject.InvoicingStatus                 =  '04' // Completely Invoiced

                                                                          or _TranspChargeObject.InvoicingStatus                 =  '05' // Over-Invoiced

                                                                          or _TranspChargeObject.InvoicingStatus                 =  '06' // Not Up-to-Date

                                                                          or _TranspChargeObject.InvoicingStatus                 =  '07' // Cannot Invoice Automatically

                                                                        )
                                                                       and _TranspChargeObject.TranspChargeUsage                 <> '4' // Internal DO Charges will be ignored; only active external DO


  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 [0..1] to I_LocationAnalytics           as _SourceLocation              on  $projection.TranspOrdSrceLocation = _SourceLocation.Location
  association [0..1] to I_LocationAnalytics           as _DestinationLocation         on  $projection.TranspOrdDestLocation = _DestinationLocation.Location
  association [1..1] to P_FrtDsputCaseNegttnCyclesAvg as _FrtDsputCaseNegttnCyclesAvg on  _FrtDsputCaseNegttnCyclesAvg.FrtDsputCaseItemSourceUUID = $projection.TransportationOrderUUID

{
      /* keys and ID's */
      @Semantics.uuid: true
  key _TransportationOrderAnalytics.TransportationOrderUUID                                                                    as TransportationOrderUUID,
      cast (_TransportationOrderAnalytics.TransportationOrder as /scmtms/vdm_freight_order_id preserving type)                 as FreightOrder,

      /* TOR information */
      @ObjectModel.foreignKey.association: '_TransportationOrderCategory'
      _TransportationOrderAnalytics.TransportationOrderCategory                                                                as TransportationOrderCategory,
      @ObjectModel.foreignKey.association: '_TransportationOrderType'
      _TransportationOrderAnalytics.TransportationOrderType                                                                    as TransportationOrderType,

      /* Dispute information */
      @ObjectModel.foreignKey.association: '_FrtDsputCaseStatus'
      _TransportationOrderAnalytics.FrtDsputCaseStatus                                                                         as FrtDsputCaseStatus,
      @ObjectModel.foreignKey.association: '_InvoicingStatus'
      cast(_TranspChargeObject.InvoicingStatus as /scmtms/vdm_chrgob_inv_st_cod preserving type)                               as TranspChargeObjInvoicingStatus,

      /* Admin */
      @Semantics.user.createdBy: true
      _TransportationOrderAnalytics.CreatedByUser                                                                              as CreatedByUser,
      _TransportationOrderAnalytics.TranspOrdCreationDateTime                                                                  as TranspOrdCreationDateTime,
      @Semantics.businessDate.at: true
      _TransportationOrderAnalytics.TranspOrdCreationDate                                                                      as 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'
      _TransportationOrderAnalytics.Carrier                                                                                    as Carrier,
      @ObjectModel.foreignKey.association: '_Shipper'
      _TransportationOrderAnalytics.Shipper                                                                                    as Shipper,
      @ObjectModel.foreignKey.association: '_Consignee'
      _TransportationOrderAnalytics.Consignee                                                                                  as Consignee,
      @Semantics.booleanIndicator:true
      cast(_TransportationOrderAnalytics._Carrier.IsBusinessPurposeCompleted as /scmtms/vdm_carrier_xpcpt preserving type)     as CarrierBusPurposeIsCompleted,
      @Semantics.booleanIndicator:true
      cast(_TransportationOrderAnalytics._Shipper.IsBusinessPurposeCompleted as /scmtms/vdm_shipper_xpcpt preserving type)     as ShipperBusPurposeIsCompleted,
      @Semantics.booleanIndicator:true
      cast(_TransportationOrderAnalytics._Consignee.IsBusinessPurposeCompleted as /scmtms/vdm_consignee_xpcpt preserving type) as ConsigneeBusPurposeIsCompleted,

      /* OrgUnits for DCL, without foreign key association */
      _TransportationOrderAnalytics.PlngAndExecOrgExternalID                                                                   as PlngAndExecOrgExternalID,
      _TransportationOrderAnalytics.PlngAndExecGroupExternalID                                                                 as PlngAndExecGroupExternalID,
      _TransportationOrderAnalytics.PurchasingOrgExternalID                                                                    as PurchasingOrgExternalID,
      _TransportationOrderAnalytics.PurchasingGroupExternalID                                                                  as PurchasingGroupExternalID,

      /* Transportation Information */
      @ObjectModel.foreignKey.association: '_TransportationMode'
      _TransportationOrderAnalytics.TransportationMode                                                                         as TransportationMode,
      @ObjectModel.foreignKey.association: '_TranspOrdShippingType'
      cast(_TransportationOrderAnalytics.TranspOrdShippingType as /scmtms/shipping_type preserving type)                       as TranspOrdShippingType,
      @ObjectModel.foreignKey.association: '_TranspOrdTrafficDirection'
      _TransportationOrderAnalytics.TranspOrdTrafficDirection                                                                  as TranspOrdTrafficDirection,
      @ObjectModel.foreignKey.association: '_MovementType'
      cast(_TransportationOrderAnalytics.MovementType as /scmtms/movement_type preserving type)                                as MovementType,
      _TransportationOrderAnalytics.TranspOrdResponsiblePerson                                                                 as TranspOrdResponsiblePerson,

      /* Locations */
      @ObjectModel.foreignKey.association: '_SourceLocation'
      cast(_TranspOrdSourceStop.TranspOrdStopLocation as /scmtms/vdm_src_location preserving type)                             as TranspOrdSrceLocation,
      @ObjectModel.foreignKey.association: '_DestinationLocation'
      cast(_TranspOrdDestinationStop.TranspOrdStopLocation as /scmtms/vdm_dest_location preserving type)                       as TranspOrdDestLocation,

      /* Dangerous Goods */
      _TransportationOrderAnalytics.DangerousGoodsAreContained                                                                 as DangerousGoodsAreContained,
      @ObjectModel.foreignKey.association: '_TranspOrdDngrsGdsStatus'
      _TransportationOrderAnalytics.TranspOrderDngrsGdsSts                                                                     as TranspOrderDngrsGdsSts,

      /* Negotiation Cycles */
      _FrtDsputCaseNegttnCyclesAvg.AvgNrOfFrtDsputCaseNegttnCyc                                                                as AvgNrOfFrtDsputCaseNegttnCyc,

      /* Measures and Units */
      @Aggregation.default: #SUM
      @Semantics.quantity.unitOfMeasure: 'TranspOrdGrossWeightUnit'
      _TransportationOrderAnalytics.TranspOrdGrossWeight                                                                       as TranspOrdGrossWeight,
      @ObjectModel.foreignKey.association: '_TranspOrdGrossWeightUnit'
      _TransportationOrderAnalytics.TranspOrdGrossWeightUnit,

      @Aggregation.default: #SUM
      @Semantics.quantity.unitOfMeasure: 'TranspOrdGrossVolumeUnit'
      _TransportationOrderAnalytics.TranspOrdGrossVolume                                                                       as TranspOrdGrossVolume,
      @ObjectModel.foreignKey.association: '_TranspOrdGrossVolumeUnit'
      _TransportationOrderAnalytics.TranspOrdGrossVolumeUnit,

      @Aggregation.default: #SUM
      @Semantics.quantity.unitOfMeasure: 'TranspOrdDistanceUnit'
      _TransportationOrderAnalytics.TranspOrdDistance                                                                          as TranspOrdDistance,
      _TransportationOrderAnalytics.TranspOrdDistanceUnit                                                                      as TranspOrdDistanceUnit,

      _TransportationOrderAnalytics.TranspOrdNetDuration                                                                       as TranspOrdNetDuration,
      
      //TranspOrdNetDuration in Hours

      @Aggregation.default: #SUM
      @Semantics.quantity.unitOfMeasure: 'TranspOrdNetDurationUnit'
      cast( ( div( _TransportationOrderAnalytics.TranspOrdNetDuration, 10000) )
      +     ( division( mod( div( cast(_TransportationOrderAnalytics.TranspOrdNetDuration as abap.int8), 100), 100 ), 60,5) )
      +     ( division( mod( cast(_TransportationOrderAnalytics.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,

      //Associations

      _TransportationOrderAnalytics._TransportationOrderCategory                                                               as _TransportationOrderCategory,
      _TransportationOrderAnalytics._TransportationOrderType                                                                   as _TransportationOrderType,
      _TransportationOrderAnalytics._FrtDsputCaseStatus                                                                        as _FrtDsputCaseStatus,
      _TransportationOrderAnalytics._Carrier                                                                                   as _Carrier,
      _TransportationOrderAnalytics._Shipper                                                                                   as _Shipper,
      _TransportationOrderAnalytics._Consignee                                                                                 as _Consignee,
      _TransportationOrderAnalytics._TransportationMode                                                                        as _TransportationMode,
      _TransportationOrderAnalytics._TranspOrdShippingType                                                                     as _TranspOrdShippingType,
      _TransportationOrderAnalytics._TranspOrdTrafficDirection                                                                 as _TranspOrdTrafficDirection,
      _TransportationOrderAnalytics._MovementType                                                                              as _MovementType,
      _TransportationOrderAnalytics._TranspOrdDngrsGdsStatus                                                                   as _TranspOrdDngrsGdsStatus,
      _TransportationOrderAnalytics._TranspOrdGrossWeightUnit                                                                  as _TranspOrdGrossWeightUnit,
      _TransportationOrderAnalytics._TranspOrdGrossVolumeUnit                                                                  as _TranspOrdGrossVolumeUnit,
      _TranspChargeObject._InvoicingStatus                                                                                     as _InvoicingStatus,
      _SourceLocation,
      _DestinationLocation
}
where
  _TransportationOrderAnalytics.TransportationOrderCategory = 'TO'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_BUSINESSPARTNER",
"I_CALENDARDATE",
"I_TRANSPCHARGEOBJECT",
"I_TRANSPORDSTOPANALYTICS",
"I_TRANSPORTATIONORDERANALYTICS",
"P_FRTDSPUTCASENEGTTNCYCLESAVG"
],
"ASSOCIATED":
[
"I_BUSINESSPARTNER",
"I_FRTDSPUTCASESTATUS",
"I_INVOICINGSTATUS",
"I_LOCATIONANALYTICS",
"I_TRANSPMOVEMENTTYPE",
"I_TRANSPORDDNGRSGDSSTATUS",
"I_TRANSPORDSHIPPINGTYPE",
"I_TRANSPORDTRAFFICDIRECTION",
"I_TRANSPORTATIONMODE",
"I_TRANSPORTATIONORDERCATEGORY",
"I_TRANSPORTATIONORDERTYPE",
"I_UNITOFMEASURE"
],
"BASE":
[
"I_TRANSPCHARGEOBJECT",
"I_TRANSPORTATIONORDERANALYTICS"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/