P_ChmlCmpstnStatusCriticality

@AbapCatalog.sqlViewName: 'PCCSTSCRITLTY'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED

@ClientHandling.algorithm: #SESSION_VARIABLE

@VDM.viewType: #COMPOSITE
@VDM.private: true

@ObjectModel:
{
  resultSet.sizeCategory: #XS,

  --Performance Annotations
  usageType:
  {
    dataClass: #META,
    sizeCategory: #M,
    serviceQuality: #B
  }
}

define view P_ChmlCmpstnStatusCriticality
  as

  --Missing: At least one composition in progress, but no released one for that composition type
  select from               I_ChmlCmplncInfo  as ChmlCmplncInfo
    inner join              I_ChmlComposition as CmpsInProgress on  CmpsInProgress.ChmlCmplncInfoUUID    = ChmlCmplncInfo.ChmlCmplncInfoUUID
                                                                and CmpsInProgress.ChmlCompositionStatus = 'IP'
    left outer to many join I_ChmlComposition as CmpsReleased   on  CmpsReleased.ChmlCmplncInfoUUID    = CmpsInProgress.ChmlCmplncInfoUUID
                                                                and CmpsReleased.ChmlCompositionType   = CmpsInProgress.ChmlCompositionType
                                                                and CmpsReleased.ChmlCompositionStatus = 'RE'

{
  key ChmlCmplncInfo.ChmlCmplncInfoUUID as ChmlCmplncInfoUUID,

      CmpsInProgress.ChmlCompositionType,

      1                                 as ChmlCompositionStsCriticality

}
where
  CmpsReleased.ChmlCmplncInfoUUID is null

union all

--Missing: No composition at all
select from               I_ChmlCmplncInfo  as ChmlCmplncInfo
  left outer to many join I_ChmlComposition as Cmps on ChmlCmplncInfo.ChmlCmplncInfoUUID = Cmps.ChmlCmplncInfoUUID
                                                    and(
                                                      Cmps.ChmlCompositionStatus         = 'IP'
                                                      or Cmps.ChmlCompositionStatus      = 'RE'
                                                    )
{
  key ChmlCmplncInfo.ChmlCmplncInfoUUID as ChmlCmplncInfoUUID,

      max( Cmps.ChmlCompositionType )   as ChmlCompositionType,

      1                                 as ChmlCompositionStsCriticality

}
where
  Cmps.ChmlCmplncInfoUUID is null
group by
  ChmlCmplncInfo.ChmlCmplncInfoUUID


union all

--In Progress: At least one in progress
select from I_ChmlCmplncInfo  as ChmlCmplncInfo
  join      I_ChmlComposition as CmpsInProgress on  ChmlCmplncInfo.ChmlCmplncInfoUUID    = CmpsInProgress.ChmlCmplncInfoUUID
                                                and CmpsInProgress.ChmlCompositionStatus = 'IP'

{
  key ChmlCmplncInfo.ChmlCmplncInfoUUID as ChmlCmplncInfoUUID,

      CmpsInProgress.ChmlCompositionType,

      2                                 as ChmlCompositionStsCriticality

}

union all

-- Released: Released versions for which no in progress version exists
select from               I_ChmlCmplncInfo  as ChmlCmplncInfo
  join                    I_ChmlComposition as CmpsReleased   on  ChmlCmplncInfo.ChmlCmplncInfoUUID  = CmpsReleased.ChmlCmplncInfoUUID
                                                              and CmpsReleased.ChmlCompositionStatus = 'RE'
  left outer to many join I_ChmlComposition as CmpsInProgress on  CmpsReleased.ChmlCmplncInfoUUID      = CmpsInProgress.ChmlCmplncInfoUUID
                                                              and CmpsReleased.ChmlCompositionType     = CmpsInProgress.ChmlCompositionType
                                                              and CmpsInProgress.ChmlCompositionStatus = 'IP'

{
  key ChmlCmplncInfo.ChmlCmplncInfoUUID as ChmlCmplncInfoUUID,

      CmpsReleased.ChmlCompositionType,

      3                                 as ChmlCompositionStsCriticality

}
where
  CmpsInProgress.ChmlCmplncInfoUUID is null