P_ProductionHoldEnhcd

@AbapCatalog.sqlViewName: 'PMPEPRODHOLDENH'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType: {serviceQuality: #B, sizeCategory: #L, dataClass: #TRANSACTIONAL}
@VDM.viewType: #COMPOSITE
@VDM.private: true
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey:true
define view P_ProductionHoldEnhcd
  -- Union of different ProductionHoldObjectType from performance perspective is better then doing CAST-statements
  as select from I_ProductionHoldBasic as ProductionHold
{
  key ProductionHold.ProductionHold,
      ProductionHold.ProductionHoldObjectType,
      ProductionHold.ProductionPlant,
      ProductionHold.Material,
      ProductionHold.WorkCenterInternalID,
      ProductionHold.WorkCenterTypeCode,
      ProductionHold.ManufacturingOrder,
      ProductionHold.OrderInternalID,
      ProductionHold.OrderOperationInternalID,
      cast('' as manufacturingordersequence )  as ManufacturingOrderSequence,
      cast('' as manufacturingorderoperation ) as ManufacturingOrderOperation,
      cast('' as mfgorderoperationtext )       as OperationText,
      ProductionHold.ShopFloorItem,
      ProductionHold.OpActyNtwkInstance,
      ProductionHold.OpActyNtwkElement,
      ProductionHold.OperationActivitySFIGroup,
      cast('' as mpe_oa_description)           as OperationActivityName,
      cast('' as mpe_oa_number_ext)            as OpActyNtwkElementExternalID,
      ProductionHold.ProductionHoldStartDateTime,
      ProductionHold.ProductionHoldEndDateTime,
      ProductionHold.HoldDurationInSeconds,
      ProductionHold.CalendarBasedNetDurnInSeconds,
      ProductionHold.WorkCenterBsdNetDurnInSeconds,
      ProductionHold.DefectInternalID,
      ProductionHold.Notification,
      ProductionHold.NotificationItem,
      ProductionHold.ProdnHoldIsAppldForOrderChg,
      ProductionHold.ProductionHoldStatus,
      ProductionHold._ProductionHoldObjectType,
      ProductionHold._ProductionPlant,
      ProductionHold._Material,
      ProductionHold._WorkCenter,
      ProductionHold._ShopFloorItem,
      ProductionHold._OpActyNtwkInstance,
      ProductionHold._OperationActivityInstance,
      ProductionHold._OperationActivitySFIGroup,
      ProductionHold._Defect,
      ProductionHold._Notification,
      ProductionHold._NotificationItem
}
where
      ProductionHold.ProductionHoldObjectType <> '4'
  and ProductionHold.ProductionHoldObjectType <> '6'

union all
// Join all Operation and OA Holds and enhance by operation data

select from  P_ProductionHoldOpAndOpActy as ProductionHold
  inner join I_OrderOperationBasic       as OrderOperationBasic on  OrderOperationBasic.OrderOperationInternalID = ProductionHold.OrderOperationInternalID
                                                                and OrderOperationBasic.OrderInternalID          = ProductionHold.OrderInternalID
association [0..1] to I_WorkCenter as _WorkCenter on  $projection.WorkCenterTypeCode   = _WorkCenter.WorkCenterTypeCode
                                                  and $projection.WorkCenterInternalID = _WorkCenter.WorkCenterInternalID
{
  key ProductionHold.ProductionHold,
      ProductionHold.ProductionHoldObjectType,
      ProductionHold.ProductionPlant,
      ProductionHold.Material,
      OrderOperationBasic.WorkCenterInternalID,
      OrderOperationBasic.WorkCenterTypeCode,
      ProductionHold.ManufacturingOrder,
      ProductionHold.OrderInternalID,
      ProductionHold.OrderOperationInternalID,
      cast(OrderOperationBasic.Sequence as manufacturingordersequence preserving type )   as ManufacturingOrderSequence,
      cast(OrderOperationBasic.Operation as manufacturingorderoperation preserving type ) as ManufacturingOrderOperation,
      cast(OrderOperationBasic.OperationText as mfgorderoperationtext preserving type)    as OperationText,
      ProductionHold.ShopFloorItem,
      ProductionHold.OpActyNtwkInstance,
      ProductionHold.OpActyNtwkElement,
      ProductionHold.OperationActivitySFIGroup,
      ProductionHold.OperationActivityName,
      ProductionHold.OpActyNtwkElementExternalID,
      ProductionHold.ProductionHoldStartDateTime,
      ProductionHold.ProductionHoldEndDateTime,
      ProductionHold.HoldDurationInSeconds,
      ProductionHold.CalendarBasedNetDurnInSeconds,
      ProductionHold.WorkCenterBsdNetDurnInSeconds,
      ProductionHold.DefectInternalID,
      ProductionHold.Notification,
      ProductionHold.NotificationItem,
      ProductionHold.ProdnHoldIsAppldForOrderChg,
      ProductionHold.ProductionHoldStatus,
      ProductionHold._ProductionHoldObjectType,
      ProductionHold._ProductionPlant,
      ProductionHold._Material,
      _WorkCenter,
      ProductionHold._ShopFloorItem,
      ProductionHold._OpActyNtwkInstance,
      ProductionHold._OperationActivityInstance,
      ProductionHold._OperationActivitySFIGroup,
      ProductionHold._Defect,
      ProductionHold._Notification,
      ProductionHold._NotificationItem
}