I_BPHierNodeProcessKPIRecType

@AbapCatalog.sqlViewName: 'IBHNPROCREKPITYP'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AccessControl.personalData.blocking: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@EndUserText.label: 'Record Type KPI of Record in MD Chg Proc'
@ObjectModel : {
  usageType : {
    dataClass: #MIXED,
    serviceQuality: #D,
    sizeCategory: #M
    }
  }
@VDM.viewType: #TRANSACTIONAL
define view I_BPHierNodeProcessKPIRecType

  // New Single Record

  as select from            I_MDChgProcessStep             as Step
    join                    I_MasterDataChangeProcess      as Process         on Step.MasterDataChangeProcess = Process.MasterDataChangeProcess
    left outer to many join I_BPHierarchyNodeProcess       as HierarchyNode   on  HierarchyNode.MasterDataChangeProcess = Step.MasterDataChangeProcess
                                                                              and HierarchyNode.MDChgProcessStep        = Step.MDChgProcessStep
    left outer to many join I_MDChgProcessMatchGroupRecord as MatchGroup      on  HierarchyNode.MasterDataChangeProcess = MatchGroup.MasterDataChangeProcess
                                                                              and HierarchyNode.MDChgProcessSrceSystem  = MatchGroup.MDChgProcessSrceSystem
                                                                              and HierarchyNode.MDChgProcessSrceObject  = MatchGroup.MDChgProcessSrceObject
    left outer to many join mdc_own_bus_sys                as _OwnBusSystem   on HierarchyNode.MDChgProcessSrceSystem = _OwnBusSystem.own_business_system
  association [0..1] to P_MDChgProcessOpenMatchGroup   as _OpenMatchGroup    on  _OpenMatchGroup.MasterDataChangeProcess  = MatchGroup.MasterDataChangeProcess
                                                                             and _OpenMatchGroup.MDChgProcessMatchGroupID = MatchGroup.MDChgProcessMatchGroupID
/*+[hideWarning] { "IDS" : [ "CARDINALITY_CHECK" ] }*/
  association [0..1] to I_MDChgProcKPIFilterRecordType as _KPIRecordTypeText on  $projection.MDChgProcKPIRecordType       = _KPIRecordTypeText.MDChangeProcKPIValueKey
/*+[hideWarning] { "IDS" : [ "CARDINALITY_CHECK" ] }*/
  association [0..1] to I_MDChgProcessStep             as _MTCStep           on  $projection.MasterDataChangeProcess       = _MTCStep.MasterDataChangeProcess
                                                                             and _MTCStep.MDChgProcessStepType             = 'MTC'
                                                                             and (
                                                                                _MTCStep.MDChgProcessSrceObjectTypeCode    = '5855'
                                                                                or _MTCStep.MDChgProcessSrceObjectTypeCode = '987'
                                                                              )
{
  key HierarchyNode.MasterDataChangeProcess,
  key HierarchyNode.MDChgProcessStep,
  key HierarchyNode.MDChgProcessSrceSystem,
  key HierarchyNode.MDChgProcessSrceObject,
      cast ('S' as mdc_kpi_filter_val ) as MDChgProcKPIRecordType,
      @ObjectModel.text.element: ['_KpiRecordTypeText.MDChangeProcKPIValueKeyDesc']
      _KPIRecordTypeText
}
where
       Process.MDChgProcessGoal                 <> 'U'
  and  Process.MDChgProcessGoal                 <> 'B'
  and  Process.MDChgProcessGoal                 <> 'R'
  and  Process.MDChgProcessGoal                 <> 'D'
  and  Process.MDChgProcessGoal                 <> 'E'
  and(
       Step.MDChgProcessStepType                =  'VAL'
    or Step.MDChgProcessStepType                =  'ACT'
    or Step.MDChgProcessStepType                =  'BRC'
  )
  and(
          MatchGroup.MDChgProcessMatchGroupID      is null
    or    MatchGroup.MDChgProcMatchGrpApprvlStatus =  'D'
    or    MatchGroup.MDChgProcMatchGrpApprvlStatus =  ''
    or    _OpenMatchGroup.MDChgProcessMatchGroupID is not null
    or    Step.MDChgProcessStep                    <  _MTCStep.MDChgProcessStep
  )
  and _OwnBusSystem.own_business_system        is null
  and HierarchyNode.MDChgProcessSourceModified <> 'D'

// Update

union select from         I_MDChgProcessStep             as Step
  join                    I_MasterDataChangeProcess      as Process         on Step.MasterDataChangeProcess = Process.MasterDataChangeProcess
  left outer to many join I_BPHierarchyNodeProcess       as HierarchyNode   on  HierarchyNode.MasterDataChangeProcess = Step.MasterDataChangeProcess
                                                                            and HierarchyNode.MDChgProcessStep        = Step.MDChgProcessStep
  join                    mdc_own_bus_sys                as OwnBusSystem    on HierarchyNode.MDChgProcessSrceSystem = OwnBusSystem.own_business_system
  join                    I_MDChgProcessMatchGroupRecord as MatchGroup      on  HierarchyNode.MasterDataChangeProcess = MatchGroup.MasterDataChangeProcess
                                                                            and HierarchyNode.MDChgProcessSrceSystem  = MatchGroup.MDChgProcessSrceSystem
                                                                            and HierarchyNode.MDChgProcessSrceObject  = MatchGroup.MDChgProcessSrceObject
/*+[hideWarning] { "IDS" : [ "CARDINALITY_CHECK" ] }*/
association [0..1] to I_MDChgProcKPIFilterRecordType as _KpiRecordTypeText on $projection.MDChgProcKPIRecordType = _KpiRecordTypeText.MDChangeProcKPIValueKey
{
  key HierarchyNode.MasterDataChangeProcess,
  key HierarchyNode.MDChgProcessStep,
  key HierarchyNode.MDChgProcessSrceSystem,
  key HierarchyNode.MDChgProcessSrceObject,
      cast ('U' as mdc_kpi_filter_val ) as MDChgProcKPIRecordType,
      @ObjectModel.text.element: ['_KPIRecordTypeText.MDChangeProcKPIValueKeyDesc']
      _KpiRecordTypeText
}
where
  (
       Step.MDChgProcessStepType                = 'VAL'
    or Step.MDChgProcessStepType                = 'ACT'
    or Step.MDChgProcessStepType                = 'BRC'
  )
  and  MatchGroup.MDChgProcMatchGrpIsBestRecord = 'X'
  and(
       MatchGroup.MDChgProcMatchGrpApprvlStatus = 'A'
    or MatchGroup.MDChgProcMatchGrpApprvlStatus = 'M'
    or MatchGroup.MDChgProcMatchGrpApprvlStatus = 'U'
  )
  and HierarchyNode.MDChgProcessSourceModified <> 'D'

// Update

union select from         I_MDChgProcessStep             as Step
  join                    I_MasterDataChangeProcess      as Process         on Step.MasterDataChangeProcess = Process.MasterDataChangeProcess
  left outer to many join I_BPHierarchyNodeProcess       as HierarchyNode   on  HierarchyNode.MasterDataChangeProcess = Step.MasterDataChangeProcess
                                                                            and HierarchyNode.MDChgProcessStep        = Step.MDChgProcessStep
  join                    mdc_own_bus_sys                as OwnBusSystem    on HierarchyNode.MDChgProcessSrceSystem = OwnBusSystem.own_business_system
  join                    I_MDChgProcessMatchGroupRecord as MatchGroup      on  HierarchyNode.MasterDataChangeProcess = MatchGroup.MasterDataChangeProcess
                                                                            and HierarchyNode.MDChgProcessSrceSystem  = MatchGroup.MDChgProcessSrceSystem
                                                                            and HierarchyNode.MDChgProcessSrceObject  = MatchGroup.MDChgProcessSrceObject
/*+[hideWarning] { "IDS" : [ "CARDINALITY_CHECK" ] }*/
association [0..1] to I_MDChgProcKPIFilterRecordType as _KPIRecordTypeText on $projection.MDChgProcKPIRecordType = _KPIRecordTypeText.MDChangeProcKPIValueKey
{
  key HierarchyNode.MasterDataChangeProcess,
  key HierarchyNode.MDChgProcessStep,
  key HierarchyNode.MDChgProcessSrceSystem,
  key HierarchyNode.MDChgProcessSrceObject,
      cast ('U' as mdc_kpi_filter_val ) as MDChgProcKPIRecordType,
      @ObjectModel.text.element: ['_KPIRecordTypeText.MDChangeProcKPIValueKeyDesc']
      _KPIRecordTypeText
}
where
  (
       Step.MDChgProcessStepType                = 'VAL'
    or Step.MDChgProcessStepType                = 'ACT'
    or Step.MDChgProcessStepType                = 'BRC'
  )
  and  MatchGroup.MDChgProcMatchGrpIsBestRecord = 'X'
  and(
       MatchGroup.MDChgProcMatchGrpApprvlStatus = 'A'
    or MatchGroup.MDChgProcMatchGrpApprvlStatus = 'M'
    or MatchGroup.MDChgProcMatchGrpApprvlStatus = 'U'
  )
  and HierarchyNode.MDChgProcessSourceModified <> 'D'

// Update without MTC

union select from         I_MDChgProcessStep        as Step
  join                    I_MasterDataChangeProcess as Process            on Step.MasterDataChangeProcess = Process.MasterDataChangeProcess
  left outer to many join I_BPHierarchyNodeProcess  as HierarchyNode      on HierarchyNode.MasterDataChangeProcess = Step.MasterDataChangeProcess
                                                                          and HierarchyNode.MDChgProcessStep        = Step.MDChgProcessStep
  join                    mdc_own_bus_sys           as OwnBusSystem       on HierarchyNode.MDChgProcessSrceSystem = OwnBusSystem.own_business_system

/*+[hideWarning] { "IDS" : [ "CARDINALITY_CHECK" ] }*/
association [0..1] to I_MDChgProcKPIFilterRecordType as _KPIRecordTypeText on $projection.MDChgProcKPIRecordType = _KPIRecordTypeText.MDChangeProcKPIValueKey
{
  key HierarchyNode.MasterDataChangeProcess,
  key HierarchyNode.MDChgProcessStep,
  key HierarchyNode.MDChgProcessSrceSystem,
  key HierarchyNode.MDChgProcessSrceObject,
      cast ('U' as mdc_kpi_filter_val ) as MDChgProcKPIRecordType,
      @ObjectModel.text.element: ['_KPIRecordTypeText.MDChangeProcKPIValueKeyDesc']
      _KPIRecordTypeText
}
where
       Process.MDChgProcessGoal  = 'U'
  or   Process.MDChgProcessGoal  = 'B'
  or   Process.MDChgProcessGoal  = 'R'
  or   Process.MDChgProcessGoal  = 'D'
  or   Process.MDChgProcessGoal  = 'E'
  or   Process.MDChgProcessGoal  = 'G'
  and(
       Step.MDChgProcessStepType = 'VAL'
    or Step.MDChgProcessStepType = 'ACT'
    or Step.MDChgProcessStepType = 'BRC'
  )
  and HierarchyNode.MDChgProcessSourceModified <> 'D'

// New Best Record

union select from         I_MDChgProcessStep             as Step
  join                    I_MasterDataChangeProcess      as Process         on Step.MasterDataChangeProcess = Process.MasterDataChangeProcess
  left outer to many join I_BPHierarchyNodeProcess       as HierarchyNode   on  HierarchyNode.MasterDataChangeProcess = Step.MasterDataChangeProcess
                                                                            and HierarchyNode.MDChgProcessStep        = Step.MDChgProcessStep
  join                    mdc_own_bus_sys                as OwnBusSystem    on HierarchyNode.MDChgProcessSrceSystem <> OwnBusSystem.own_business_system
  join                    I_MDChgProcessMatchGroupRecord as MatchGroup      on  HierarchyNode.MasterDataChangeProcess = MatchGroup.MasterDataChangeProcess
                                                                            and HierarchyNode.MDChgProcessSrceSystem  = MatchGroup.MDChgProcessSrceSystem
                                                                            and HierarchyNode.MDChgProcessSrceObject  = MatchGroup.MDChgProcessSrceObject
association [0..1] to P_MDChgProcessOpenMatchGroup   as _OpenMatchGroup    on  _OpenMatchGroup.MasterDataChangeProcess  = MatchGroup.MasterDataChangeProcess
                                                                           and _OpenMatchGroup.MDChgProcessMatchGroupID = MatchGroup.MDChgProcessMatchGroupID
/*+[hideWarning] { "IDS" : [ "CARDINALITY_CHECK" ] }*/
association [0..1] to I_MDChgProcKPIFilterRecordType as _KPIRecordTypeText on  $projection.MDChgProcKPIRecordType = _KPIRecordTypeText.MDChangeProcKPIValueKey
{
  key HierarchyNode.MasterDataChangeProcess,
  key HierarchyNode.MDChgProcessStep,
  key HierarchyNode.MDChgProcessSrceSystem,
  key HierarchyNode.MDChgProcessSrceObject,
      cast ('B' as mdc_kpi_filter_val ) as MDChgProcKPIRecordType,
      @ObjectModel.text.element: ['_KPIRecordTypeText.MDChangeProcKPIValueKeyDesc']
      _KPIRecordTypeText
}
where
  (
       Step.MDChgProcessStepType                = 'VAL'
    or Step.MDChgProcessStepType                = 'ACT'
    or Step.MDChgProcessStepType                = 'BRC'
  )
  and  MatchGroup.MDChgProcMatchGrpIsBestRecord = 'X'
  and  _OpenMatchGroup.MDChgProcessMatchGroupID is null
  and(
       MatchGroup.MDChgProcMatchGrpApprvlStatus = 'A'
    or MatchGroup.MDChgProcMatchGrpApprvlStatus = 'M'
    or MatchGroup.MDChgProcMatchGrpApprvlStatus = 'U'
  )
  and HierarchyNode.MDChgProcessSourceModified <> 'D'

union select from         I_MDChgProcessStep             as Step
  join                    I_MasterDataChangeProcess      as Process         on Step.MasterDataChangeProcess = Process.MasterDataChangeProcess
  left outer to many join I_BPHierarchyNodeProcess       as HierarchyNode   on  HierarchyNode.MasterDataChangeProcess = Step.MasterDataChangeProcess
                                                                            and HierarchyNode.MDChgProcessStep        = Step.MDChgProcessStep
/*+[hideWarning] { "IDS" : [ "CARDINALITY_CHECK" ] }*/
association [0..1] to I_MDChgProcKPIFilterRecordType as _KPIRecordTypeText on  $projection.MDChgProcKPIRecordType = _KPIRecordTypeText.MDChangeProcKPIValueKey
{
  key HierarchyNode.MasterDataChangeProcess,
  key HierarchyNode.MDChgProcessStep,
  key HierarchyNode.MDChgProcessSrceSystem,
  key HierarchyNode.MDChgProcessSrceObject,
      cast ('D' as mdc_kpi_filter_val ) as MDChgProcKPIRecordType,
      @ObjectModel.text.element: ['_KPIRecordTypeText.MDChangeProcKPIValueKeyDesc']
      _KPIRecordTypeText
}
where
  (
       Step.MDChgProcessStepType                = 'VAL'
    or Step.MDChgProcessStepType                = 'ACT'
    or Step.MDChgProcessStepType                = 'BRC'
  ) and HierarchyNode.MDChgProcessSourceModified = 'D'