P_RSHMaintSchedOperations

@AbapCatalog.sqlViewName: 'PRSHMNTSCHDOP'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #CONSUMPTION
@EndUserText.label: 'Operations from frozen schedule'

/*
CONTRACT*************************************************************************************************************************
Name:                       Maintenance Scheduling Frozen Operations
Specification:              This view will return the operations that are part of the frozen schedule and operations that are confirmed
                            in the schedule period
Requires:                   NA
Ensures:                    Operations of Maintenance Scheduling Frozen Schedule are returned
Owners:                     AN
Contributors:               PSP
Unit Test required Y/N:     N
Additional comments         None

END OF CONTRACT******************************************************************************************************************
*/


define view P_RSHMaintSchedOperations
  as select from    P_RSHMaintSchedFrozenOp   as _FrozenOperations

    inner join      I_OrderOperationBasic     as _Operation        on  _FrozenOperations.MaintOrderRoutingNumber    = _Operation.OrderInternalID
                                                                   and _FrozenOperations.MaintOrderOperationCounter = _Operation.OrderOperationInternalID
                                                                   and _FrozenOperations.MaintOrderConfirmation     = _Operation.OperationConfirmation

  //to filter out PP-Orders etc.

    inner join      I_RSHOrder                as _MaintenanceOrder on _Operation.OrderInternalID = _MaintenanceOrder.MaintOrderRoutingNumber

  //status filter -> see where condition at the bottom

    left outer join I_StatusObjectStatusBasic as _ExclStatusObj    on  _Operation.ObjectInternalID     = _ExclStatusObj.StatusObject
                                                                   and _ExclStatusObj.StatusIsInactive = ''
                                                                   and (
                                                                      _ExclStatusObj.StatusCode        = 'I0013' //DELETED

                                                                      or _ExclStatusObj.StatusCode     = 'I0043' //LOCKED    (LKD)

                                                                    )

  association [1..1] to I_RSHOperationProcessingStatus as _ProcStatus                 on  _Operation.ObjectInternalID = _ProcStatus.StatusObject

  association [1..1] to I_RSHOrderMaterialStatus       as _MaterialStatus             on  _MaintenanceOrder.MaintenanceOrderInternalID = _MaterialStatus.StatusObject

  association [0..1] to I_RSHOperationHasCrossOrderRel as _OperationHasCrossOrderRel  on  $projection.MaintOrderRoutingNumber    = _OperationHasCrossOrderRel.MaintOrderRoutingNumber
                                                                                      and $projection.MaintOrderOperationCounter = _OperationHasCrossOrderRel.MaintOrderOperationCounter

  association [0..1] to I_OrderOperationBasic          as _SuperiorOperation          on  $projection.MaintOrderRoutingNumber     = _SuperiorOperation.OrderInternalID
                                                                                      and $projection.SuperiorOperationInternalID = _SuperiorOperation.OrderOperationInternalID


  association [0..1] to I_WorkCenter                   as _WorkCenter                 on  _WorkCenter.WorkCenterInternalID = _FrozenOperations.WorkCenterInternalID
                                                                                      and _WorkCenter.WorkCenterTypeCode   = _FrozenOperations.WorkCenterTypeCode
  association [1..1] to I_MaintOrderOperPlanningValues as _OperationPlanningValues    on  $projection.MaintOrderRoutingNumber    = _OperationPlanningValues.MaintOrderRoutingNumber
                                                                                      and $projection.MaintOrderOperationCounter = _OperationPlanningValues.MaintOrderOperationCounter

  association [0..1] to I_WorkAssignment               as _PersonResponsible          on  $projection.OperationPersonResponsible = _PersonResponsible.WorkAssignment

  association [0..1] to I_UnitOfMeasure                as UnitOfMeasure               on  UnitOfMeasure.UnitOfMeasure = 'H'

  association [0..1] to P_RSHMaintSchedUtilBaseKPI     as _SchUtilKPI                 on  $projection.MaintSchedSimlnUUID        = _SchUtilKPI.MaintSchedSimlnUUID
                                                                                      and $projection.MaintOrderRoutingNumber    = _SchUtilKPI.MaintOrderRoutingNumber
                                                                                      and $projection.MaintOrderOperationCounter = _SchUtilKPI.MaintOrderOperationCounter
                                                                                      and $projection.MaintOrderConfirmation     = _SchUtilKPI.MaintOrderConf
  association [0..1] to I_MaintOrderOperObjectListLink as _MaintOrderOpObjectListLink on  $projection.MaintOrderOperationCounter = _MaintOrderOpObjectListLink.MaintOrderOperationCounter
                                                                                      and $projection.MaintOrderRoutingNumber    = _MaintOrderOpObjectListLink.MaintOrderRoutingNumber

{

  key  _FrozenOperations.MaintOrderRoutingNumber,
  key  _FrozenOperations.MaintOrderOperationCounter,
  key  _FrozenOperations.MaintSchedSimlnUUID,
       _Operation.SuperiorOperationInternalID                                                                                                                                                                                       as SuperiorOperationInternalID,
       _Operation.ObjectInternalID                                                                                                                                                                                                  as MaintOrderOperationInternalID,
       coalesce( _SuperiorOperation.Operation, _Operation.Operation )                                                                                                                                                               as MaintenanceOrderOperation,
       case when _Operation.SuperiorOperationInternalID = '00000000' then '' else _Operation.Operation_2 end                                                                                                                        as MaintenanceOrderSubOperation,
       _Operation.OperationControlProfile                                                                                                                                                                                           as OperationControlKey,
       _Operation.OperationText                                                                                                                                                                                                     as OperationDescription,

       _Operation.Plant,

       _FrozenOperations.WorkCenterInternalID,
       _FrozenOperations.WorkCenterTypeCode,
       _Operation.NumberOfCapacities,

       _Operation.OperationConfirmation                                                                                                                                                                                             as MaintOrderConfirmation,
       _Operation.OperationPersonResponsible,

       _Operation.Language,
       _Operation.MaintOperationExecStageCode,
       _Operation.Equipment                                                                                                                                                                                                         as OperationEquipment,
       _Operation.FunctionalLocation                                                                                                                                                                                                as OperationFunctionalLocation,
       _Operation.OperationSystemCondition,
       cast( case when _MaintOrderOpObjectListLink.MaintenanceItemObject is not null then
        case when _MaintOrderOpObjectListLink._MaintenanceItemObject.Equipment <> '' then _MaintOrderOpObjectListLink._MaintenanceItemObject.Equipment
        else _MaintOrderOpObjectListLink._MaintenanceItemObject.FunctionalLocation end
        else case when _Operation.Equipment != '' then
                    _Operation.Equipment
               when _Operation.Equipment = '' and
                    _Operation.FunctionalLocation != '' then
                    _Operation.FunctionalLocation
               else '' end
        end as eams_tec_obj )                                                                                                                                                                                                       as OperationTechnicalObject,
       cast( case when _MaintOrderOpObjectListLink.MaintenanceItemObject is not null then
       case when _MaintOrderOpObjectListLink._MaintenanceItemObject.Equipment <> '' then 'EAMS_EQUI'
       when _MaintOrderOpObjectListLink._MaintenanceItemObject.FunctionalLocation <> '' then 'EAMS_FL'
       else '' end
       else case when  _Operation.Equipment != '' then
                    'EAMS_EQUI'
       when _Operation.Equipment = '' and
                   _Operation.FunctionalLocation != '' then
                    'EAMS_FL'
       else '' end
       end as rsh_eams_op_tec_obj_type_value )                                                                                                                                                                                      as OpTechObjEquipOrFuncnlLoc,

       _MaintenanceOrder.MaintenanceOrder,
       _MaintenanceOrder.BasicSchedulingType,
       _MaintenanceOrder.MaintenanceOrderInternalID,
       _MaintenanceOrder.MaintPriority,
       _MaintenanceOrder.MaintPriorityType,
       _MaintenanceOrder.MaintenanceOrderType,
       _MaintenanceOrder.MaintenanceActivityType,
       _MaintenanceOrder.MaintenancePlanningPlant,
       _MaintenanceOrder.MaintenanceOrderDesc,
       _MaintenanceOrder.OrderHasLongText,
       _MaintenanceOrder.MaintOrdBasicStartDate,
       _MaintenanceOrder.MaintOrdBasicStartTime,
       _MaintenanceOrder.MaintOrdBasicEndDate,
       _MaintenanceOrder.MaintOrdBasicEndTime,
       _MaintenanceOrder.MaintenancePlannerGroup,
       _MaintenanceOrder.MaintenanceRevision,
       _MaintenanceOrder.OperationSystemCondition                                                                                                                                                                                   as OrderSystemCondition,
       _MaintenanceOrder.MaintenanceNotification,
       _MaintenanceOrder.Equipment,
       _MaintenanceOrder._LocationAccountAssignment.FunctionalLocation                                                                                                                                                              as OrderFunctionalLocation,
       coalesce(cast( _MaintenanceOrder._LocationAccountAssignment._FunctionalLocation._FunctionalLocationText[ 1: Language = $session.system_language ].FunctionalLocationName as rsh_eam_func_location_name preserving type ),'') as FunctionalLocationName,
       cast( case when _MaintenanceOrder.Equipment = '' and _MaintenanceOrder._LocationAccountAssignment.FunctionalLocation = '' then ''
          else case when _MaintenanceOrder.Equipment = '' then 'EAMS_FL'
          else 'EAMS_EQUI' end end as rsh_eams_tec_obj_type_value )                                                                                                                                                                 as TechObjIsEquipOrFuncnlLoc,
       _MaintenanceOrder.MaintObjectLocAcctAssgmtNmbr,
       _MaintenanceOrder.MaintenancePlan,
       _MaintenanceOrder.MaintenancePlant,
       _MaintenanceOrder.SchedulingIsPerformedBackward,
       _MaintenanceOrder.SchedulingType,
       _MaintenanceOrder.LatestAcceptableCompletionDate,

       _MaintenanceOrder.SuperiorProjectNetwork,
       _MaintenanceOrder.ControllingArea,
       _MaintenanceOrder.ResponsibleCostCenter,
       _MaintenanceOrder.MainWorkCenter,
       _MaintenanceOrder.MainWorkCenterPlant,
       cast(_MaintenanceOrder.MaintOrdProcessPhaseCode as eam_maint_ord_proc_phase_code preserving type)                                                                                                                            as MaintOrdProcessPhaseCode,
       cast(_MaintenanceOrder._EAMProcessPhase._EAMProcessPhaseText[1:Language = $session.system_language].EAMProcessPhaseCodeDesc as eam_maint_ord_proc_phase )                                                                    as MaintOrdProcessPhaseDesc,
       cast(_MaintenanceOrder.MaintOrdProcessSubPhaseCode as eam_maint_ord_proc_sph_code preserving type)                                                                                                                           as MaintOrdProcessSubPhaseCode,
       cast(_MaintenanceOrder._EAMProcessSubPhase._EAMProcessSubPhaseText[1:Language = $session.system_language].EAMProcessSubPhaseCodeDesc as eam_maint_ord_proc_subphase )                                                        as MaintOrdProcessSubPhaseDesc,

       cast(_MaintenanceOrder._Project.Project as rsh_eam_project preserving type )                                                                                                                                                 as Project,
       _MaintenanceOrder._Project.ProjectDescription,
       cast(_MaintenanceOrder._WBSElement.WBSElement as rsh_eam_wbs_element preserving type )                                                                                                                                       as WBSElement,
       cast(_MaintenanceOrder._WBSElement.WBSDescription as rsh_eam_wbs_element_desc preserving type )                                                                                                                              as WBSDescription,

       _OperationPlanningValues.OpErlstSchedldExecStrtDte,
       _OperationPlanningValues.OpErlstSchedldExecStrtTme,
       _OperationPlanningValues.OpErlstSchedldExecEndDte,
       _OperationPlanningValues.OpErlstSchedldExecEndTme,
       _OperationPlanningValues.OpLtstSchedldExecStrtDte,
       _OperationPlanningValues.OpLtstSchedldExecStrtTme,
       _OperationPlanningValues.OpLtstSchedldExecEndDte,
       _OperationPlanningValues.OpLtstSchedldExecEndTme,
       _OperationPlanningValues.OpActualExecutionStartDate,
       _OperationPlanningValues.OpActualExecutionStartTime,
       _OperationPlanningValues.OpActualExecutionEndDate,
       _OperationPlanningValues.OpActualExecutionEndTime,
       _OperationPlanningValues.ForecastedWorkQty,
       _OperationPlanningValues.ConfirmationTotalQuantity,

       _OperationPlanningValues.OperationPlannedWork,
       _OperationPlanningValues.OperationPlannedWorkUnit,
       _OperationPlanningValues.OperationDuration,
       _OperationPlanningValues.OperationDurationUnit,
       _ProcStatus.ProcessingStatus,
       cast( coalesce( _MaterialStatus.MaterialStatus , 30 ) as rsh_eam_material_status_int preserving type )                                                                                                                       as MaterialStatus,
       cast(case when Language = '' then '' else 'X' end as rsh_eam_long_text_available preserving type)                                                                                                                            as OperationHasLongText,

       //    Consider the dates of operations at the time freeze

       case when _FrozenOperations.MaintSchedCompletionStatus = 30 then
              case when SchedulingIsPerformedBackward = ''
              then _OperationPlanningValues.OpErlstSchedldExecStrtDte
              else _OperationPlanningValues.OpLtstSchedldExecStrtDte end
            else
              _FrozenOperations.PlannedStartDate end                                                                                                                                                                                as PlannedStartDate,

       case when _FrozenOperations.MaintSchedCompletionStatus = 30 then
              case when SchedulingIsPerformedBackward = ''
              then _OperationPlanningValues.OpErlstSchedldExecStrtTme
              else _OperationPlanningValues.OpLtstSchedldExecStrtTme end
            else
              _FrozenOperations.PlannedStartTime end                                                                                                                                                                                as PlannedStartTime,

       case when _FrozenOperations.MaintSchedCompletionStatus = 30 then
              case when SchedulingIsPerformedBackward = ''
              then _OperationPlanningValues.OpErlstSchedldExecEndDte
              else _OperationPlanningValues.OpLtstSchedldExecEndDte end
            else
              _FrozenOperations.PlannedEndDate end                                                                                                                                                                                  as PlannedEndDate,

       case when _FrozenOperations.MaintSchedCompletionStatus = 30 then
              case when SchedulingIsPerformedBackward = ''
              then _OperationPlanningValues.OpErlstSchedldExecEndTme
              else _OperationPlanningValues.OpLtstSchedldExecEndTme end
            else
              _FrozenOperations.PlannedEndTime end                                                                                                                                                                                  as PlannedEndTime,

       case when  _OperationHasCrossOrderRel.HasCrossOrderRelationship = 'X'
                then cast('X' as rsh_eam_hascrossorderrelation preserving type)
                else cast('' as rsh_eam_hascrossorderrelation preserving type)
       end                                                                                                                                                                                                                          as HasCrossOrderRelationship,

       _FrozenOperations.MaintSchedCompletionStatus,

       UnitOfMeasure._Text[ 1: Language = $session.system_language ].UnitOfMeasure_E                                                                                                                                                as TotalActualWorkUnit,

       cast( unit_conversion(
       quantity =>  _SchUtilKPI.TotalConfirmedWork,
       source_unit => cast( 'S' as abap.unit(3) ) ,
       target_unit => _OperationPlanningValues.OperationPlannedWorkUnit ,
       error_handling => 'SET_TO_NULL' ) as abap.dec(24,1) )                                                                                                                                                                        as ActualWorkQuantity,

       cast( unit_conversion(
        quantity =>  _FrozenOperations.MaintOrdOpSchedWork,
       source_unit => cast( 'H' as abap.unit(3) ) ,
       target_unit => _OperationPlanningValues.OperationPlannedWorkUnit ,
       error_handling => 'SET_TO_NULL'
       )  as abap.dec(24, 1))                                                                                                                                                                                                       as MaintOrdOpSchedWork,

       /* Associations */
       _WorkCenter,
       _SuperiorOperation,
       _ProcStatus,
       _MaterialStatus,
       _OperationHasCrossOrderRel,
       _PersonResponsible,

       _MaintenanceOrder._BasicSchedulingType,
       _MaintenanceOrder._Equipment,
       _MaintenanceOrder._LocationAccountAssignment,
       _MaintenanceOrder._MaintenanceActivityType,
       _MaintenanceOrder._MaintenanceNotification,
       _MaintenanceOrder._MaintenancePriority

}

where
  _ExclStatusObj.StatusObject is null