I_MDChgProcessMatchGroupRecord

@AbapCatalog.sqlViewName: 'IMTCGRPREC'
@AbapCatalog.compiler.compareFilter: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'MD Consolidation Match Group Record'
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #M
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@VDM.viewType: #COMPOSITE
define view I_MDChgProcessMatchGroupRecord
  as select from           I_MDChgProcessMatchGroup      as MatchGroup
    inner join             P_MDChgProcLastCmpltdMtchgStp as LastMTCStep on MatchGroup.MasterDataChangeProcess = LastMTCStep.MasterDataChangeProcess
    inner join             I_MDChgProcessStep            as Step        on  LastMTCStep.MasterDataChangeProcess = Step.MasterDataChangeProcess
                                                                        and LastMTCStep.MDChgProcessStep        = Step.MDChgProcessStep
    inner join             I_MDChgProcessMatchGroup      as BestRecord  on  MatchGroup.MasterDataChangeProcess       = BestRecord.MasterDataChangeProcess
                                                                        and MatchGroup.MDChgProcessMatchGroupID      = BestRecord.MDChgProcessMatchGroupID
                                                                        and BestRecord.MDChgProcMatchGrpIsBestRecord = 'X'
    left outer to one join I_MDChgProcOwnBusinessSystem  as OwnSystem   on MatchGroup.MDChgProcessSrceSystem = OwnSystem.MDChgProcessSrceSystem

{
  key MatchGroup.MasterDataChangeProcess,
  key MatchGroup.MDChgProcessMatchGroupID,
  key MatchGroup.MDChgProcessSrceSystem,
  key MatchGroup.MDChgProcessSrceObject,
  key Step.MDChgProcessSrceObjectTypeCode,
      MatchGroup.MDChgProcMatchGrpIsBestRecord,
      MatchGroup.MDChgProcMatchGrpApprvlStatus,
      MatchGroup.MDChgProcMatchGroupApprover,
      MatchGroup.MDChgProcMatchGrpApprvlDteTime,
      MatchGroup.MDChgProcMatchGroupOriginalID,
      cast( case when OwnSystem.MDChgProcessSrceSystem is not null then 'X' else ' ' end as boole_d ) as MDChangeProcessIsActiveRecord
}

union select from I_MDChgProcIntmdMatchGrp      as MatchGroup
  inner join      I_MasterDataChangeProcess     as Process      on MatchGroup.MasterDataChangeProcess = Process.MasterDataChangeProcess
  inner join      P_MDChgProcLastCmpltdMtchgStp as LastMTCStep  on MatchGroup.MasterDataChangeProcess = LastMTCStep.MasterDataChangeProcess
  inner join      I_MDChgProcessStep            as MatchingStep on  MatchGroup.MasterDataChangeProcess  =  MatchingStep.MasterDataChangeProcess
                                                                and MatchingStep.MDChgProcessStepType   =  'MTC'
                                                                and MatchingStep.MDChgProcessStepStatus =  '4'
                                                                and LastMTCStep.MDChgProcessStep        <> MatchingStep.MDChgProcessStep
  inner join      I_MDChgProcIntmdMatchGrp      as BestRecord   on  MatchGroup.MasterDataChangeProcess       = BestRecord.MasterDataChangeProcess
                                                                and MatchGroup.MDChgProcessMatchGroupID      = BestRecord.MDChgProcessMatchGroupID
                                                                and BestRecord.MDChgProcMatchGrpIsBestRecord = 'X'
  left outer to one join I_MDChgProcOwnBusinessSystem  as OwnSystem    on MatchGroup.MDChgProcessSrceSystem = OwnSystem.MDChgProcessSrceSystem
{
  key MatchGroup.MasterDataChangeProcess,
  key MatchGroup.MDChgProcessMatchGroupID,
  key MatchGroup.MDChgProcessSrceSystem,
  key MatchGroup.MDChgProcessSrceObject,
  key MatchGroup.MDChgProcessSrceObjectTypeCode,
      MatchGroup.MDChgProcMatchGrpIsBestRecord,
      MatchGroup.MDChgProcMatchGrpApprvlStatus,
      MatchGroup.MDChgProcMatchGroupApprover,
      MatchGroup.MDChgProcMatchGrpApprvlDteTime,
      MatchGroup.MDChgProcMatchGroupOriginalID,
      cast( case when OwnSystem.MDChgProcessSrceSystem is not null then 'X' else ' ' end as boole_d ) as MDChangeProcessIsActiveRecord
}
where
      Process.MDChgProcessStatus   <> 'F'
  and Process.MDChgProcessIsErased <> 'X'