P_ServiceOrderDataCube

//@EndUserText.label: 'Service Order Data Cube'

@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog: {
  sqlViewName: 'PSERVORDDCUBE',
  compiler.compareFilter: true,
  preserveKey: true
}
@VDM: {
  viewType: #COMPOSITE,
  private:true
}
@AccessControl: {
  personalData.blocking: #('TRANSACTIONAL_DATA')
}

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

define view P_ServiceOrderDataCube
  as select from I_ServiceDocumentEnhcd
  
  association [0..1] to I_CalendarDate             as _Period              on  $projection.ServiceDocumentCreationDate = _Period.CalendarDate
  association [0..1] to P_SrvcOrdDistinctConfExist as _SrvcOrdItmConfExist on  $projection.ServiceObjectType = _SrvcOrdItmConfExist.ServiceObjectType
                                                                           and $projection.ServiceOrder      = _SrvcOrdItmConfExist.ServiceOrder

  association [1..1] to I_SrvcOrdConfStatus        as _SrvcOrdConfStatus   on  $projection.SrvcOrdHasConfdItem = _SrvcOrdConfStatus.SrvcOrdHasConfdItem

{
      @ObjectModel.foreignKey.association: '_ServiceObjType'
  key ServiceObjectType,
  key ServiceDocument               as ServiceOrder,
      ServiceDocumentDescription    as ServiceOrderDescription,
      @ObjectModel.foreignKey.association: '_SrvcDocLifecycleStatus'
      ServiceDocumentStatus         as ServiceOrderStatus,
      @ObjectModel.foreignKey.association: '_ServiceDocumentType'
      ServiceDocumentType,
      RefBusinessSolutionOrder,
     
      RespyMgmtServiceTeam,
      
       _ServiceTeamHeader._TeamDescription.TeamDescription as RespyMgmtServiceTeamDesc,
       
       _ServiceTeamHeader.TeamName as TeamName,

      ServiceDocumentTemplateType,

      @ObjectModel.foreignKey.association: '_SoldToParty'
      SoldToParty,
      @ObjectModel.foreignKey.association: '_RespEmployee'
      ResponsibleEmployee,
      @ObjectModel.foreignKey.association: '_ServiceEmployee'
      ServiceEmployee,
      @ObjectModel.foreignKey.association: '_ContactPerson'
      ContactPerson,
      @ObjectModel.foreignKey.association: '_ServiceTeam'
      ServiceTeam,
      
      RequestedServiceEndDate,
      ServiceDocumentCreationDate,

      @ObjectModel.foreignKey.association: '_ServiceDocumentPriority'
      ServiceDocumentPriority,

      @ObjectModel.foreignKey.association: '_ServiceDocHasError'
      ServiceDocumentHasError,

      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      ServiceDocNetAmount,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      TransactionCurrency,

      PurchaseOrderByCustomer,

//      @ObjectModel.foreignKey.association: '_SalesOrganizationOrgUnit'

      @ObjectModel.foreignKey.association: '_SalesOrganizationOrgUnit_2'
      SalesOrganizationOrgUnitID,

//      @ObjectModel.foreignKey.association: '_SalesOfficeOrgUnit'

      @ObjectModel.foreignKey.association: '_SalesOfficeOrgUnit_2'
      SalesOfficeOrgUnitID,

//      @ObjectModel.foreignKey.association: '_SalesGroupOrgUnit'

      @ObjectModel.foreignKey.association: '_SalesGroupOrgUnit_2'
      SalesGroupOrgUnitID,

      ServiceOrganization,

      @ObjectModel.foreignKey.association: '_SalesOrganization'
      SalesOrganization,

      @ObjectModel.foreignKey.association: '_DistributionChannel'
      DistributionChannel,

      @ObjectModel.foreignKey.association: '_Division'
      Division,

      @ObjectModel.foreignKey.association: '_SalesOffice'
      SalesOffice,

      @ObjectModel.foreignKey.association: '_SalesGroup'
      SalesGroup,

      @Semantics.calendar.quarter
      _Period.CalendarQuarter       as SrvcOrdCreationQuarter,
      @Semantics.calendar.month
      _Period.CalendarMonth         as SrvcOrdCreationMonth,
      @Semantics.calendar.year
      _Period.CalendarYear          as SrvcOrdCreationYear,

      @DefaultAggregation: #SUM
      cast( case when ServiceDocumentIsOpen = 'X' then cast (1 as abap.int4)
                 end as abap.int4 ) as NmbrOfIncompleteServiceOrders,

      @DefaultAggregation: #SUM
      cast(1 as abap.int4)          as NumberOfIncomingServiceOrders,

      @DefaultAggregation: #SUM
      cast( case when ServiceDocumentIsOpen = 'X'
              then 1
              else 0
            end as abap.int4 )      as NumberOfOpenServiceOrders,

      @DefaultAggregation: #SUM
      case when RequestedServiceEndDate < $session.system_date
                    and    ServiceDocumentIsOpen = 'X'
            then cast (1 as abap.int4)
            end                     as NumberOfOverdueServiceOrders,

      // Confirmation exists or not

      case
           when _SrvcOrdItmConfExist.ServiceConfirmation is not null and
                    ServiceDocumentIsOpen = 'X'
           then 'X'
           else ' '
        end                         as SrvcOrdHasConfdItem,

      @ObjectModel.foreignKey.association: '_ServiceDocumentIsOpen'
      ServiceDocumentIsOpen,

      _ServiceDocRefObj.ServiceReferenceObjectType,
      _ServiceDocRefObj.SrvcRefObjIsMainObject,
      _ServiceDocRefObj.ProductID,
      _ServiceDocRefObj.SerialNumber,
      _ServiceDocRefObj.Equipment,
      _ServiceDocRefObj.FunctionalLocation,


      _ServiceObjType,
      _ServiceDocumentIsOpen,
      @API.element.releaseState: #DEPRECATED
      @API.element.successor: '_SrvcDocLifecycleStatus'
      _ServiceDocumentStatus,
      _SrvcDocLifecycleStatus,
      _ServiceDocumentType,
      _ServiceDocumentPriority,
      _SoldToParty,
      _RespEmployee,
      _ServiceTeam,
      _ServiceEmployee,
      _ContactPerson,
      _ServiceDocHasError,
      _SrvcOrdConfStatus,
      _SalesOrganizationOrgUnit,
      _SalesOfficeOrgUnit,
      _SalesGroupOrgUnit,
      _SalesOrganization,
      _SalesOffice,
      _SalesGroup,
      _DistributionChannel,
      _Division,
      _SalesOrganizationOrgUnit_2,
      _SalesOfficeOrgUnit_2,
      _SalesGroupOrgUnit_2,
      _TransactionCurrency,
      _Period,
      _ServiceDocRefObj,
      _ServiceTeamHeader

}
where
      ServiceObjectType           = 'BUS2000116'
  and ServiceDocumentIsQuotation != 'X'
  and SrvcDocIsMaintServiceOrder != 'X'
  and ServiceDocumentTemplateType is initial
  and RefInHouseRepairIsExisting  is initial