V_COSS4W09_1_VIEW

@AbapCatalog.sqlViewName: 'V_COSS4W09_1'
@AbapCatalog.compiler.compareFilter: true
@EndUserText.label: 'COSS for WRTTP=09 - part 1'

//@ClientHandling.algorithm: #SESSION_VARIABLE

@Metadata.ignorePropagatedAnnotations: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #P

define view V_COSS4W09_1_VIEW as

  select
--           key '00'         as lednr,
           key a.kokrs      as kokrs,
           key a.objnr      as objnr,
           key a.gjahr      as gjahr,
--           key '09'         as wrttp,
           key case when a.gkont <> ''    then a.gkont  else a.racct end     as kstar,
           key a.hrkft      as hrkft,
--           key hrkft.hrkft  as uhrkft,
           key a.vrgng      as vrgng,
           key case when a.parobsrc = '1' then a.parob1 else case when a.parobsrc = '2' then a.paccasty else '' end end as parob,                      
           key a.uspob      as uspob,
           key a.co_beknz   as beknz,
           key a.rwcur      as twaer,

           key a.rldnr      as rldnr,
           key a.poper      as perio,
           
--           a.rvunit     as meinh,
           a.co_meinh   as meinh,
           a.beltp,
           a.rbukrs     as bukrs,           
           a.bstat,
           tk.xwbuk     as xwbuk,
           
           a.rfarea     as FKBER,
           a.sfarea     as PFKBER,
           a.rfund      as GEBER,
           a.sfund      as PGEBER,
           a.rgrant_nbr as GRANT_NBR,
           a.sgrant_nbr as PGRANT_NBR,
           a.segment,
           a.psegment, 
           a.rbudget_pd  as BUDGET_PD,
           a.sbudget_pd  as PBUDGET_PD,
           
           a.rco_ocur,
           a.rkcur,
           a.rocur,
--           rocur as racur,
           a.rvcur,           
           a.rbcur,           
           a.rccur,
           a.rdcur,
           a.recur,
           a.rfcur,
           a.rgcur,
           a.rhcur,

--         value fields
           sum(a.wsl)     as wsl,
           sum(a.wsl2)    as wsl2,
           sum(a.wsl3)    as wsl3,
  
           sum(a.kfsl)    as kfsl,
           sum(a.kfsl2)   as kfsl2,
           sum(a.kfsl3)   as kfsl3,

           sum(a.ksl)     as ksl,   
           sum(a.osl)     as osl,    
           sum(a.co_osl)  as co_osl,           
           sum(a.vsl)     as vsl,    
           sum(a.bsl)     as bsl,    
           sum(a.csl)     as csl,
           sum(a.dsl)     as dsl,
           sum(a.esl)     as esl,
           sum(a.fsl)     as fsl,
           sum(a.gsl)     as gsl,
           sum(a.hsl)     as hsl,
                    
           sum(a.psl)     as psl,
           sum(a.psl2)    as psl2,
           sum(a.psl3)    as psl3,
           sum(a.pfsl)    as pfsl,
           sum(a.pfsl2)   as pfsl2,
           sum(a.pfsl3)   as pfsl3,           

--           sum(a.co_megbtr)   as vmsl,
--           sum(a.co_mefbtr)   as vmfsl,
           sum(a.vmsl)    as vmsl,
           sum(a.vmfsl)   as vmfsl,           
           sum(a.msl)     as msl,
           sum(a.mfsl)    as mfsl

--           max(muvflg)  as muvflg
           
      -----------------------------------------


    from       acdoca       as a   
//    inner join acdoca       as hrkft  on hrkft.rclnt   = a.rclnt              --- original HRKFT cannot be get from ACDOCA -> it has to be added to

//                                     and hrkft.rldnr   = a.rldnr

//                                     and hrkft.rbukrs  = a.rbukrs

//                                     and hrkft.gjahr   = a.gjahr

//                                     and hrkft.belnr   = a.belnr

//                                     and hrkft.co_zlenr = a.co_zlenr

//                                     and hrkft.co_buzei <> a.co_buzei                                      


    inner join tka01        as tk on     tk.mandt   = a.rclnt                    --- check logical system for relevanz for totals    !do not delete this condition!
                                     and tk.kokrs   = a.kokrs
    inner join t000         as s  on     s.mandt    = a.rclnt
                                     and (   s.logsys   = a.logsyso
                                          or a.logsyso  = ''
                                          or (    ( a.accasty = 'KS'        or a.accasty = 'KL'  )
                                              and ( s.logsys = tk.logsystem or tk.logsystem = '' )
                                              )
                                         )
    where     ( a.vrgng   = 'KOAO'  or  a.vrgng   = 'KOAL' or a.vrgng = 'EBDP' )    
          and a.accasty <> ''                                             --- actuals only 
          and a.objnr   <> ''                                             --- OBJNR is initial in case CO has been archived!
          and ( a.uspob like 'KL%' or a.uspob like 'KS%' )
          and a.co_beknz = 'S'
          
    group by
           a.kokrs,
           a.objnr,
           a.gjahr,
           a.racct,
           a.gkont,
           a.hrkft,
--           hrkft.hrkft,
           a.vrgng,
           a.parobsrc,
           a.parob1,
           a.paccasty,
           a.uspob,
           a.co_beknz,
           a.rwcur,

           a.rldnr,
           a.poper,
           
--           a.rvunit,
           a.co_meinh,
           a.beltp,
           a.rbukrs,           
           a.bstat,
           xwbuk,
           
           a.rfarea,
           a.sfarea,
           a.rfund,
           a.sfund,
           a.rgrant_nbr,
           a.sgrant_nbr,
           a.segment,
           a.psegment, 
           a.rbudget_pd,
           a.sbudget_pd,           
           
           a.rco_ocur,
           a.rkcur,
           a.rocur,
           a.rvcur,           
           a.rbcur,           
           a.rccur,
           a.rdcur,
           a.recur,
           a.rfcur,
           a.rgcur,
           a.rhcur    
    ;