P_MaintOrderForComplPreAggr

//@EndUserText.label: 'Number of Open Purchase Order Items'


@AbapCatalog: {
  sqlViewName: 'PMAINTORDCOMPL',
  compiler.compareFilter: true,
  preserveKey: true
}
@AccessControl.authorizationCheck: #NOT_REQUIRED

@VDM: {
    viewType: #CONSUMPTION,
    private: true
}

@ClientHandling: {
  type:      #CLIENT_DEPENDENT,
  algorithm: #SESSION_VARIABLE
}

define view P_MaintOrderForComplPreAggr
  as select from            I_MaintenanceOrder
    left outer to one join  I_StatusObjectStatus as _StatusObjectStatus on  _StatusObjectStatus.StatusObject = I_MaintenanceOrder.MaintenanceOrderInternalID
                                                                        and _StatusObjectStatus.StatusCode   = 'I0009'

    left outer to many join I_ReservationItem                           on I_ReservationItem.ReferenceOrder = I_MaintenanceOrder.MaintenanceOrder
    left outer to many join I_PurgDocAccountAssignment                  on I_PurgDocAccountAssignment.OrderID = I_MaintenanceOrder.MaintenanceOrder
    left outer to one join  I_PurchasingDocumentItem                    on  I_PurchasingDocumentItem.PurchasingDocumentItem     = I_PurgDocAccountAssignment.PurchasingDocumentItem
                                                                        and I_PurchasingDocumentItem.PurchasingDocument         = I_PurgDocAccountAssignment.PurchasingDocument
                                                                        and I_PurchasingDocumentItem.PurchasingDocumentCategory = 'F'
                                                                        and (
                                                                           I_PurchasingDocumentItem.ProductType                 = '1'
                                                                           or I_PurchasingDocumentItem.ProductType              = ''
                                                                         )
                                                                        and I_PurchasingDocumentItem.IsCompletelyDelivered      = ''
{

  key I_MaintenanceOrder.MaintenanceOrder,

      MaintenanceOrderDesc,

      MaintOrdPersonResponsible,

      // PSTYP = 9 --> Service, Table T163Y, Note 441371

      // ProductType = 2 --> Lean Service

      cast( max(  case when PurchasingDocumentItemCategory <> '9' then 'X' end ) as xfeld preserving type )                                       as MaintOrdHasOpenPurchaseOrders,

      cast( max(  case when PurchasingDocumentItemCategory = '9' then 'X' end ) as xfeld preserving type )                                        as MaintOrdHasOpenServices,

      cast( max(  case when ReservationItemIsFinallyIssued = '' and MaterialCompIsProcuredDirectly = '' then 'X' end ) as xfeld preserving type ) as MaintOrderHasOpenReservations,

      MaintOrdBasicEndDate                                                                                                                        as ReferenceDate,

      MaintenanceRevision,

      I_MaintenanceOrder.ControllingArea,

      MaintenanceOrderInternalID,

      Currency,

      MainWorkCenter,

      MainWorkCenterPlant,

      ResponsibleCostCenter,

      MaintenancePlanningPlant,

      MaintenanceOrderType,

      MaintenancePlannerGroup,

      MaintenancePlant,

      MaintObjectLocAcctAssgmtNmbr,

      Equipment,

      _Equipment,

      _LocationAccountAssignment
}
where
      I_MaintenanceOrder.MaintenanceProcessingPhase = '2'
  and _StatusObjectStatus.StatusIsInactive          = ''
group by
  I_MaintenanceOrder.MaintenanceOrder,
  MaintOrdBasicEndDate,
  MaintenanceOrderDesc,
  MaintOrdPersonResponsible,
  MaintenanceRevision,
  I_MaintenanceOrder.ControllingArea,
  MaintenanceOrderInternalID,
  Currency,
  MainWorkCenter,
  MainWorkCenterPlant,
  ResponsibleCostCenter,
  MaintenancePlanningPlant,
  MaintenanceOrderType,
  MaintenancePlannerGroup,
  MaintenancePlant,
  MaintObjectLocAcctAssgmtNmbr,
  Equipment
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MAINTENANCEORDER",
"I_PURCHASINGDOCUMENTITEM",
"I_PURGDOCACCOUNTASSIGNMENT",
"I_RESERVATIONITEM",
"I_STATUSOBJECTSTATUS"
],
"ASSOCIATED":
[
"I_EQUIPMENT",
"I_LOCATIONACCOUNTASSIGNMENT"
],
"BASE":
[
"I_MAINTENANCEORDER"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/