nsdm_e_msteh

@AbapCatalog.preserveKey: true
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.sqlViewName:      'NSDM_V_MSTEH'
@EndUserText.label:            'CDS View for MSTEH on NSDM_T_MTDCSA'
@DataAging.noAgingRestriction: 'false'

@ObjectModel.usageType.sizeCategory: 'L'
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ClientHandling.algorithm: #AUTOMATED
@AccessControl.authorizationCheck: #NOT_ALLOWED
@AbapCatalog.viewEnhancementCategory: #PROJECTION_LIST
define view nsdm_e_msteh  as
select from nsdm_e_msteh_per as p left outer join nsdm_e_msteh_agg as a 
         on  p.mandt   = a.mandt
         and p.matnr   = a.matnr
         and p.werks   = a.werks
         and p.charg   = a.charg
         and p.sobkz   = a.sobkz
         and p.vbeln   = a.vbeln
         and p.posnr   = a.posnr
         and p.gjper  >= a.gjper  
 inner join mcha as m
      on p.mandt = m.mandt and
         p.matnr = m.matnr and
         p.werks = m.werks and
         p.charg = m.charg                        
{
key p.mandt,
key p.matnr, 
key p.werks,
key p.charg,
key p.sobkz,
key p.vbeln,
key p.posnr,
key m.bwtar,

key cast(substring(p.gjper, 1, 4) as abap.numc(4)) as lfgja,
key cast(substring(p.gjper, 6, 2) as abap.numc(2)) as lfmon,

sum(a.ewesb) as ewesb,
sum(a./cwm/ewesb) as /cwm/ewesb

}
group by p.mandt,
         p.matnr,
         p.werks,
         p.charg,
         p.sobkz,
         m.bwtar,
         p.vbeln,
         p.posnr,
         p.gjper