P_WorkRequestOrderUnion

@VDM.private: true
@VDM.viewType:#COMPOSITE
@AbapCatalog.sqlViewName: 'PREQORDUNION'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@ClientHandling.algorithm: #SESSION_VARIABLE

define view P_WorkRequestOrderUnion
  as select from I_MaintOrderTechObj

  association [0..1] to I_User                       as _CreatedByUser                    on  _CreatedByUser.UserID = $projection.CreatedByUser

  association [0..*] to I_SchedulingTypeText         as _SchedulingTypeName               on  _SchedulingTypeName.SchedulingType = $projection.BasicSchedulingType

  association [0..1] to I_BusinessAreaText           as _BusinessAreaText                 on  _BusinessAreaText.BusinessArea = $projection.businessarea
                                                                                          and _BusinessAreaText.Language     = $session.system_language

  association [0..1] to I_CompanyCodeStdVH           as _CompanyCodeStdVH                 on  _CompanyCodeStdVH.CompanyCode = $projection.companycode

  association [0..1] to I_EquipmentText              as _EquipmentText                    on  _EquipmentText.Equipment = $projection.Equipment
                                                                                          and _EquipmentText.Language  = $session.system_language

  association [0..1] to I_FunctionalLocationText     as _FunctionalLocationText           on  _FunctionalLocationText.FunctionalLocation = $projection.FunctionalLocation
                                                                                          and _FunctionalLocationText.Language           = $session.system_language

  association [0..1] to I_FunctionalAreaText         as _FunctionalAreaText               on  _FunctionalAreaText.FunctionalArea = $projection.FunctionalArea
                                                                                          and _FunctionalAreaText.Language       = $session.system_language

  association [0..1] to I_MaintenanceItemBasic       as _MaintenanceItem                  on  $projection.MaintenanceItem = _MaintenanceItem.MaintenanceItem

  association [0..1] to I_PMNotificationPriorityType as _MaintenancePriorityType          on  $projection.MaintPriorityType = _MaintenancePriorityType.MaintPriorityType

  association [0..1] to I_PMNotificationPriority     as _MaintenancePriority              on  $projection.MaintPriority     = _MaintenancePriority.MaintPriority
                                                                                          and $projection.MaintPriorityType = _MaintenancePriority.MaintPriorityType
  association [0..1] to I_MaintPlanningBucketTP      as _MaintenancePlanningBucket        on  _MaintenancePlanningBucket.MaintenancePlanningPlant = $projection.MaintenancePlanningPlant
  association [0..1] to I_LocationAccountAssignment  as _LocationAccountAssignment        on  _LocationAccountAssignment.MaintObjectLocAcctAssgmtNmbr = $projection.MaintObjectLocAcctAssgmtNmbr
  association [0..1] to I_FunctionalLocationLabel    as _FunctionalLocationLabel          on  _FunctionalLocationLabel.FunctionalLocation = $projection.FunctionalLocation
  association [0..1] to I_CostCenterText             as _CostCenterText                   on  _CostCenterText.CostCenter      = $projection.ResponsibleCostCenter
                                                                                          and _CostCenterText.ControllingArea = $projection.ControllingArea
                                                                                          and _CostCenterText.ValidityEndDate = '12319999'
                                                                                          and _CostCenterText.Language        = $session.system_language
  association [0..1] to E_PMNotifMaintenanceData     as _MaintenanceNotificationExtension on  $projection.MaintenanceNotification = _MaintenanceNotificationExtension.MaintenanceNotification
  association [0..1] to E_MaintenanceOrder           as _MaintOrderExtension              on  $projection.MaintenanceOrder = _MaintOrderExtension.MaintenanceOrder
  association [0..1] to I_MaintPlanningBucketVH      as _MaintPlanningBucketVH            on  _MaintPlanningBucketVH.MaintPlanningBucketUUID = $projection.MaintPlanningBucketUUID
  association [0..1] to I_MaintNotificationTechObj   as _MaintNotificationTechObj         on  $projection.MaintenanceNotification = _MaintNotificationTechObj.MaintenanceNotification
  

{
  key cast ( MaintenanceOrder as eam_maint_exec_obj )                                                                                             as MaintExecutionObject,
      @Environment.sql.passValue: true
  key cast ( 'WO' as eam_maint_exec_obj_type)                                                                                                     as MaintExecutionObjectType,
      MaintPriorityType,
      MaintPriority,
      TechnicalObject,
      TechObjIsEquipOrFuncnlLoc,
      Equipment,
      FunctionalLocation, 
      @ObjectModel.filter.transformedBy: 'CL_EAM_TECHOBJ_FILTER_EXIT'
      case when Equipment = '' then
           cast( _FunctionalLocationLabel.FunctionalLocationLabelName as eams_tech_obj_alpha_conv )
      else cast( Equipment as eams_tech_obj_alpha_conv ) end                                                                                      as TechnicalObjectLabel,

      case when Equipment = '' then
            cast( _FunctionalLocationText.FunctionalLocationName as eams_tec_obj_des )
      else  cast( _EquipmentText.EquipmentName as eams_tec_obj_des ) end                                                                          as TechnicalObjectDescription,

      cast(_FunctionalLocation._SuperiorFunctionalLocation._FunctionalLocationLabel.FunctionalLocationLabelName as eams_bcv_tplnr_no_conversion ) as SuperiorFunctionalLocation,

      _FunctionalLocation._SuperiorFunctionalLocation._FunctionalLocationText[ 1:Language = $session.system_language ].FunctionalLocationName     as SuperiorTechnicalObjectName,
      _FunctionalLocation.FunctionalLocationCategory,
      @ObjectModel.foreignKey.association: '_MaintenanceItem'
      MaintenanceItem,
      MaintOrdMainWorkCenter                                                                                                                      as MainWorkCenter,
      WorkCenterTypeCode,
      MaintOrdMainWorkCenterPlant                                                                                                                 as MaintenanceWorkCenterPlant,
      WorkCenterInternalID,
      @ObjectModel.foreignKey.association: '_MaintenancePlanningPlant'
      MaintenancePlanningPlant,

      @ObjectModel.foreignKey.association: '_MaintenancePlannerGroup'
      MaintenancePlannerGroup,

      MaintObjectLocAcctAssgmtNmbr,

      cast( '' as auswk )                                                                                                                         as MalfunctionEffect,

      /* *********Date Fields*************** */
      I_MaintOrderTechObj.CreationDate,
      I_MaintOrderTechObj.CreationTime,
      cast(dats_tims_to_tstmp( I_MaintOrderTechObj.CreationDate, I_MaintOrderTechObj.CreationTime,
          abap_system_timezone($session.client,'NULL'), $session.client,'NULL') as timestamp)                                                     as CreationDateTime,        
      CreatedByUser,
      MaintOrdBasicStartDate                                                                                                                      as MaintExecObjRequiredStartDate,
      MaintOrdBasicEndDate                                                                                                                        as MaintExecObjectRequiredEndDate,
      MaintOrdBasicStartTime                                                                                                                      as MaintExecObjRequiredStartTime,
      MaintOrdBasicEndTime                                                                                                                        as MaintExecObjectRequiredEndTime,
      cast(dats_tims_to_tstmp(MaintOrdBasicStartDate, MaintOrdBasicStartTime,
      abap_system_timezone($session.client,'NULL'), $session.client,'NULL') as timestamp)                                                         as MaintExecObjRqdStartDateTime,                                                                                    
      cast(dats_tims_to_tstmp(MaintOrdBasicEndDate, MaintOrdBasicEndTime,
      abap_system_timezone($session.client,'NULL'), $session.client,'NULL') as timestamp)                                                         as MaintExecObjRqdEndDateTime,     
      LatestAcceptableCompletionDate,

      MaintOrdPersonResponsible                                                                                                                   as PersonResponsible,
      cast ('' as qmnam)                                                                                                                          as ReportedByUser,
      cast ('' as ad_namtext)                                                                                                                     as ReporterFullName,
      MaintenanceProcessingPhase,
      MaintenanceNotification,
      MaintenanceOrder,
      MaintenanceActivityType,
      Assembly,
      SerialNumber,
      cast( '' as qmart)                                                                                                                          as NotificationType,
      //      _MaintenanceNotificationExtension.MaintenanceNotification as NotificationType,

      ResponsibleCostCenter,
      OperationSystemCondition,

      //     ScheduledBasicStartDate,

      //      @Semantics.time: true

      //      ScheduledBasicStartTime,


      MaintenanceOrderType                                                                                                                        as OrderType,
      OrderHasLongText                                                                                                                            as NotificationHasLongText, // HasLongText

      Currency,
      Material,
      MaintenanceOrderDesc                                                                                                                        as DocumentText, // OrderStatusDescription,

      MaintenanceOrderInternalID, // used for status profile


      /* *********Failure mode *************** */
      cast ('' as qmcod)                                                                                                                          as MaintNotificationCode,
      cast ('' as qmgrp)                                                                                                                          as MaintNotificationCodeGroup,
      cast ('' as qmkat)                                                                                                                          as MaintNotificationCatalog,
      cast ('' as msaus)                                                                                                                          as MaintenanceObjectIsDown,
      
      cast('' as ausvn )                                                                                                                          as MalfunctionStartDate,
      cast('' as auztv )                                                                                                                          as MalfunctionStartTime,
      cast('' as ausvn )                                                                                                                          as MalfunctionEndDate,
      cast('' as auztv )                                                                                                                          as MalfunctionEndTime,   
      _MaintNotificationTechObj.NotificationTimeZone, 
         
      
      MaintenancePlant,

      _LocationAccountAssignment.AssetLocation,
      _LocationAccountAssignment._Location.LocationName,
      _LocationAccountAssignment.PlantSection,
      _LocationAccountAssignment.MaintObjectFreeDefinedAttrib, //sort field

      _LocationAccountAssignment._ABCIndicator.ABCIndicator,
      _LocationAccountAssignment.BusinessArea,
      _LocationAccountAssignment.CompanyCode,
      _LocationAccountAssignment.AssetRoom,
      _LocationAccountAssignment._WBSElementBasicData.WBSElementExternalID                                                                        as LocAcctAssgmtWBSElementExtID,
      _LocationAccountAssignment.CostCenter                                                                                                       as LocAcctAssgmtCostCenter,
      _LocationAccountAssignment.WBSElementInternalID,
      ControllingArea,
      cast ('' as kostl)                                                                                                                          as CostCenter,
      cast ('' as anln1)                                                                                                                          as MasterFixedAsset,
      cast ('' as ilom_ordst)                                                                                                                     as SettlementOrder,
      cast ('' as ps_posid_edit)                                                                                                                  as OrdSettlmtWBSElementExternalID,
      cast ('' as maintordhassettlementrules)                                                                                                     as MaintOrdHasSettlementRules,
      ProfitCenter,
      cast ('' as qmobjnr)                                                                                                                        as MaintNotifInternalID,
      MaintenanceOrderObjectList,
      MaintOrdProcessPhaseCode,
      MaintOrdProcessSubPhaseCode,
      MaintOrderProcessingContext,
      cast ('' as  maintnotifprocessingcontext )                                                                                                  as MaintNotifProcessingContext,

      @ObjectModel.text.element: ['FunctionalLocationLabelName']
      _FunctionalLocationText[1:Language = $session.system_language].FunctionalLocationName                                                       as FunctionalLocationName,
      @Semantics.text: true
      _FunctionalLocationLabel.FunctionalLocationLabelName                                                                                        as FunctionalLocationLabelName,
      FunctionalArea,
      OrderProcessingGroup,
      BasicSchedulingType,
      ReferenceElement,
      @ObjectModel.text.element: ['MaintenanceRevisionDesc']
      MaintenanceRevision,
      @Semantics.text: true
      _MaintenanceRevision.MaintenanceRevisionDesc,
      AdditionalDeviceData,
      LastChangeDateTime,
      _LocationAccountAssignment._MaintenancePlant.PlantName,
      cast( hextobin('00000000000000000000000000000000') as maintplanningbucketuuid )                                                             as MaintPlanningBucketUUID,
      cast( '' as eam_plngbktlabel )                                                                                                              as MaintPlngBucketLabel,
      MaintOrderRoutingNumber,
      MaintOrdBasicStartDate,
      MaintOrdBasicStartTime,
      MaintOrdBasicEndDate,
      MaintOrdBasicEndTime,
      ScheduledBasicStartDate,
      ScheduledBasicStartTime,
      ScheduledBasicEndDate,
      ScheduledBasicEndTime,
      cast( case when ScheduledBasicStartTime = '240000' and ScheduledBasicStartDate <> '00000000'
                  then dats_tims_to_tstmp(dats_add_days( ScheduledBasicStartDate, 1, 'NULL'), cast( '000000' as abap.tims ),
                    abap_system_timezone($session.client,'NULL'), $session.client, 'NULL' )
                 else dats_tims_to_tstmp(ScheduledBasicStartDate, ScheduledBasicStartTime,
                        abap_system_timezone($session.client,'NULL'), $session.client, 'NULL' )
                        end as timestamp preserving type )                                                                                        as MaintOrdSchedBscStartDateTime,
      cast( case when ScheduledBasicEndTime = '240000' and ScheduledBasicEndDate <> '00000000'
                  then dats_tims_to_tstmp(dats_add_days( ScheduledBasicEndDate, 1, 'NULL'), cast( '000000' as abap.tims ),
                    abap_system_timezone($session.client,'NULL'), $session.client, 'NULL' )
                 else dats_tims_to_tstmp(ScheduledBasicEndDate, ScheduledBasicEndTime,
                        abap_system_timezone($session.client,'NULL'), $session.client, 'NULL' )
                        end as timestamp preserving type )                                                                                        as MaintOrdSchedldBscEndDateTime,
      MaintOrderReferenceDate                                                                                                                     as MaintExecObjectReferenceDate, 
      MaintOrderReferenceTime                                                                                                                     as MaintExecObjectReferenceTime,                

      /* *********Associations*************** */
      _MaintenancePlanningBucket,
      _CreatedByUser,
      _SchedulingTypeName,
      _BusinessAreaText,
      _EquipmentText,
      _FunctionalLocationText,
      _FunctionalAreaText,
      _CompanyCodeStdVH,
      _CostCenterText,
      _MaintenancePriorityType,
      _MaintenancePriority,
      _MaintenanceItem,
      _MaintenancePlanningPlant,
      _MaintenanceActivityType,
      _MaintenancePlannerGroup,
      _Assembly,
      _LocationAccountAssignment,
      _MaintNotificationTechObj


}
where MaintOrdProcessPhaseCode <> ''
and   MaintOrderProcessingContext <> 'B'

union all select from I_PMNotifMaintenanceData

association [0..1] to I_User                       as _CreatedByUser                    on  _CreatedByUser.UserID = $projection.createdbyuser

association [0..*] to I_SchedulingTypeText         as _SchedulingTypeName               on  _SchedulingTypeName.SchedulingType = $projection.basicschedulingtype

association [0..1] to I_BusinessAreaText           as _BusinessAreaText                 on  _BusinessAreaText.BusinessArea = $projection.businessarea
                                                                                        and _BusinessAreaText.Language     = $session.system_language

association [0..1] to I_CompanyCodeStdVH           as _CompanyCodeStdVH                 on  _CompanyCodeStdVH.CompanyCode = $projection.companycode

association [0..1] to I_EquipmentText              as _EquipmentText                    on  _EquipmentText.Equipment = $projection.Equipment
                                                                                        and _EquipmentText.Language  = $session.system_language

association [0..1] to I_FunctionalLocationText     as _FunctionalLocationText           on  _FunctionalLocationText.FunctionalLocation = $projection.functionallocation
                                                                                        and _FunctionalLocationText.Language           = $session.system_language

association [0..1] to I_FunctionalAreaText         as _FunctionalAreaText               on  _FunctionalAreaText.FunctionalArea = $projection.functionalarea
                                                                                        and _FunctionalAreaText.Language       = $session.system_language

association [0..1] to I_MaintenanceItemBasic       as _MaintenanceItem                  on  $projection.MaintenanceItem = _MaintenanceItem.MaintenanceItem

association [0..1] to I_PMNotificationPriorityType as _MaintenancePriorityType          on  $projection.maintprioritytype = _MaintenancePriorityType.MaintPriorityType

association [0..1] to I_PMNotificationPriority     as _MaintenancePriority              on  $projection.maintpriority     = _MaintenancePriority.MaintPriority
                                                                                        and $projection.maintprioritytype = _MaintenancePriority.MaintPriorityType

association [0..1] to I_MaintPlanningBucketTP      as _MaintenancePlanningBucket        on  _MaintenancePlanningBucket.MaintenancePlanningPlant = $projection.MaintenancePlanningPlant
association [0..1] to I_MaintenanceActivityType    as _MaintenanceActivityType          on  $projection.MaintenanceActivityType = _MaintenanceActivityType.MaintenanceActivityType
association [0..1] to I_LocationAccountAssignment  as _LocationAccountAssignment        on  _LocationAccountAssignment.MaintObjectLocAcctAssgmtNmbr = $projection.MaintObjectLocAcctAssgmtNmbr
association [0..1] to I_FunctionalLocationLabel    as _FunctionalLocationLabel          on  _FunctionalLocationLabel.FunctionalLocation = $projection.functionallocation
association [0..1] to I_CostCenterText             as _CostCenterText                   on  _CostCenterText.CostCenter      = $projection.ResponsibleCostCenter
                                                                                        and _CostCenterText.ControllingArea = $projection.controllingarea
                                                                                        and _CostCenterText.ValidityEndDate = '12319999'
                                                                                        and _CostCenterText.Language        = $session.system_language
association [0..1] to E_PMNotifMaintenanceData     as _MaintenanceNotificationExtension on  I_PMNotifMaintenanceData.MaintenanceNotification = _MaintenanceNotificationExtension.MaintenanceNotification
association [0..1] to E_MaintenanceOrder           as _MaintOrderExtension              on  $projection.maintenanceorder = _MaintOrderExtension.MaintenanceOrder
association [0..1] to I_MaintPlanningBucketVH      as _MaintPlanningBucketVH            on  _MaintPlanningBucketVH.MaintPlanningBucketUUID = $projection.MaintPlanningBucketUUID
association [0..1] to I_MaintNotificationTechObj   as _MaintNotificationTechObj         on  $projection.MaintenanceNotification = _MaintNotificationTechObj.MaintenanceNotification

{

  key cast ( MaintenanceNotification as eam_maint_exec_obj )                                                                                              as MaintExecutionObject,
  key cast ( 'WR' as eam_maint_exec_obj_type)                                                                                                             as MaintExecutionObjectType,
      _MaintenanceNotification.MaintPriorityType,
      _MaintenanceNotification.MaintPriority,
      case when Equipment != '' then
            cast( Equipment as eams_tec_obj )
      when Equipment = '' and
          _LocationAccountAssignment.FunctionalLocation != '' then
            cast(  _LocationAccountAssignment.FunctionalLocation as eams_tec_obj)
      end                                                                                                                                                 as TechnicalObject,
      case when Equipment != '' then
          cast ( 'EAMS_EQUI' as eams_tec_obj_type_value)
      when Equipment = '' and
        _LocationAccountAssignment.FunctionalLocation != '' then
          cast ( 'EAMS_FL' as eams_tec_obj_type_value)
      end                                                                                                                                                 as TechObjIsEquipOrFuncnlLoc,
      Equipment,
      _LocationAccountAssignment.FunctionalLocation,
      case when Equipment = '' then
           cast( _LocationAccountAssignment._FunctionalLocation._FunctionalLocationLabel.FunctionalLocationLabelName as eams_tech_obj_alpha_conv )
      else cast( Equipment as eams_tech_obj_alpha_conv ) end                                                                                              as TechnicalObjectLabel,

      case when Equipment = '' then
           cast( _LocationAccountAssignment._FunctionalLocation._FunctionalLocationText[1:Language = $session.system_language].FunctionalLocationName as eams_tec_obj_des )
      else  cast( _EquipmentText.EquipmentName as eams_tec_obj_des ) end                                                                                  as TechnicalObjectDescription,

      cast(_RelevantFunctionalLocation._SuperiorFunctionalLocation._FunctionalLocationLabel.FunctionalLocationLabelName as eams_bcv_tplnr_no_conversion ) as SuperiorFunctionalLocation,

      _RelevantFunctionalLocation._SuperiorFunctionalLocation._FunctionalLocationText[ 1:Language = $session.system_language ].FunctionalLocationName     as SuperiorTechnicalObjectName,
      _LocationAccountAssignment._FunctionalLocation.FunctionalLocationCategory,
      @ObjectModel.foreignKey.association: '_MaintenanceItem'
      MaintenanceItem,
      _MaintenanceNotification._MainWorkCenter.WorkCenter                                                                                                 as MainWorkCenter,
      _MaintenanceNotification.WorkCenterTypeCode,
      _MaintenanceNotification.MaintenanceWorkCenterPlant,
      _MaintenanceNotification.WorkCenterInternalID,

      @ObjectModel.foreignKey.association: '_MaintenancePlanningPlant'
      MaintenancePlanningPlant,

      @ObjectModel.foreignKey.association: '_MaintenancePlannerGroup'
      MaintenancePlannerGroup,

      MaintObjectLocAcctAssgmtNmbr,

      @ObjectModel.foreignKey.association: '_MalfunctionEffect'
      MalfunctionEffect,

      //Date fields

      _MaintenanceNotification.CreationDate,
      _MaintenanceNotification.CreationTime,
      cast(dats_tims_to_tstmp( _MaintenanceNotification.CreationDate, _MaintenanceNotification.CreationTime,
          abap_system_timezone($session.client,'NULL'), $session.client,'NULL') as timestamp)                                                             as CreationDateTime,  
      _MaintenanceNotification.CreatedByUser,
      
      _MaintNotificationTechObj.RequiredStartDate                                                                                                         as MaintExecObjRequiredStartDate, 
      _MaintNotificationTechObj.RequiredEndDate                                                                                                           as MaintExecObjectRequiredEndDate,
      _MaintNotificationTechObj.RequiredStartTime                                                                                                         as MaintExecObjRequiredStartTime,                                                                                                        
      _MaintNotificationTechObj.RequiredEndTime                                                                                                           as MaintExecObjectRequiredEndTime,                                                                                                          
      cast (dats_tims_to_tstmp( _MaintNotificationTechObj.RequiredStartDate, _MaintNotificationTechObj.RequiredStartTime , 
      _MaintNotificationTechObj.NotificationTimeZone , $session.client, 'NULL') as  tzntstmps preserving type )                                           as MaintExecObjRqdStartDateTime,
      cast (dats_tims_to_tstmp( _MaintNotificationTechObj.RequiredEndDate, _MaintNotificationTechObj.RequiredEndTime,
      _MaintNotificationTechObj.NotificationTimeZone, $session.client, 'NULL') as  tzntstmps preserving type )                                            as MaintExecObjRqdEndDateTime,      
        
      LatestAcceptableCompletionDate,

      NotificationPersonResponsible                                                                                                                       as PersonResponsible,
      _MaintenanceNotification.ReportedByUser,
      //      Replace below wrong feild : use I_USER

      //      coalesce( _CreatedByUser.UserID, _MaintenanceNotification.ReportedByUser ) as ReporterFullName,

      _MaintenanceNotification._ReportedByUser.UserDescription                                                                                            as ReporterFullName,
      //      _MaintenanceNotification.ReportedByUser                                                                                                             as ReporterFullName,

      _MaintenanceNotification.NotifProcessingPhase                                                                                                       as MaintenanceProcessingPhase,

      MaintenanceNotification,
      _MaintenanceNotification.MaintenanceOrder,

      @ObjectModel.foreignKey.association: '_MaintenanceActivityType'
      MaintenanceActivityType,

      Assembly,
      cast ( '' as sernr)                                                                                                                                 as SerialNumber,
      _MaintenanceNotification.NotificationType,
      cast ('' as pph_kostl )                                                                                                                             as ResponsibleCostCenter,
      cast ('' as anlzu )