P_ChmlCmpstnStatusCriticality

DDL: P_CHMLCMPSTNSTATUSCRITICALITY SQL: PCCSTSCRITLTY Type: view COMPOSITE Package: EHFND_CNS_CCI

Criticality of Chemical Composition

P_ChmlCmpstnStatusCriticality is a Composite CDS View that provides data about "Criticality of Chemical Composition" in SAP S/4HANA. It reads from 2 data sources (I_ChmlCmplncInfo, I_ChmlComposition) and exposes 9 fields with key field ChmlCmplncInfoUUID. Part of development package EHFND_CNS_CCI.

2 data source(s) 9 fields

Data Sources (2)

SourceAliasJoin Type
I_ChmlCmplncInfo ChmlCmplncInfo from
I_ChmlComposition CmpsInProgress inner

Annotations (11)

NameValueLevelField
AbapCatalog.sqlViewName PCCSTSCRITLTY view
AbapCatalog.compiler.compareFilter true view
AbapCatalog.preserveKey true view
AccessControl.authorizationCheck #NOT_REQUIRED view
ClientHandling.algorithm #SESSION_VARIABLE view
VDM.viewType #COMPOSITE view
VDM.private true view
ObjectModel.resultSet.sizeCategory #XS view
ObjectModel.usageType.dataClass #META view
ObjectModel.usageType.sizeCategory #M view
ObjectModel.usageType.serviceQuality #B view

Fields (9)

KeyFieldSource TableSource FieldDescription
KEY ChmlCmplncInfoUUID I_ChmlCmplncInfo ChmlCmplncInfoUUID
ChmlCompositionType I_ChmlComposition ChmlCompositionType
ChmlCmplncInfoUUIDasChmlCmplncInfoUUID
ChmlCompositionType
ChmlCmplncInfoUUIDasChmlCmplncInfoUUID
ChmlCompositionType I_ChmlComposition ChmlCompositionType
ChmlCmplncInfoUUIDasChmlCmplncInfoUUID
ChmlCompositionType CmpsReleased ChmlCompositionType
ChmlCompositionStsCriticality 3 
@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

Learn More