V_Coss_D_as_Coep_Age_View

@AbapCatalog.sqlViewName: 'V_COSS_D_AGE'
@EndUserText.label: 'Cold Data: Totals - Migrated Deltas (secondary)'

@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #P

define view V_Coss_D_as_Coep_Age_View as 
select           
           key a.rclnt      as mandt,
           key '00'         as lednr, 
           key a.objnr      as objnr,     
           key a.ryear      as gjahr, -- a.gjahr as gjahr
           key '04'         as wrttp,           
           key v.versn      as versn,
           key a.co_belnr   as belnr,    
           key a.co_buzei   as buzei, 
           key a.racct      as kstar,
           key a.hrkft      as hrkft,
           key a.vrgng      as vrgng,
           key 
              case 
                when a.parobsrc = '1'  then a.parob1 
                when a.parobsrc = '2'  then a.paccasty 
                else                        '' 
               end          as parob,      
           key uspob        as uspob, 
           key a.co_beknz   as beknz,     
           key a.rwcur      as twaer,   
           key '016'        as perbl,

           key a.poper      as perio,
             
           a.kokrs          as kokrs,           
           a.co_meinh       as meinh,  
           case when ( a.runit is null or a.runit = '' ) then '' else a.runit end as meinb,  
           
           case 
--             when rco_ocur <> '' and tk.xwbuk = ''  then co_osl       
             when wogbtr   = 'CO_OSL'  then co_osl
             when wogbtr   = 'KSL'  then ksl
             when wogbtr   = 'HSL'  then hsl   
             when wogbtr   = 'OSL'  then osl            
             when wogbtr   = 'VSL'  then vsl
             when wogbtr   = 'BSL'  then bsl
             when wogbtr   = 'CSL'  then csl
             when wogbtr   = 'DSL'  then dsl
             when wogbtr   = 'ESL'  then esl
             when wogbtr   = 'FSL'  then fsl
             when wogbtr   = 'GSL'  then gsl
             else                        0  
           end as wogbtr,  

           case wtgbtr
             when 'WSL'  then wsl        
             when 'WSL2' then wsl2
             when 'WSL3' then wsl3            
             else             0  
           end as wtgbtr,           

           case wkgbtr          
             when 'KSL'  then ksl
             when 'HSL'  then hsl             
             when 'OSL'  then osl            
             when 'VSL'  then vsl
             when 'BSL'  then bsl
             when 'CSL'  then csl
             when 'DSL'  then dsl
             when 'ESL'  then esl
             when 'FSL'  then fsl
             when 'GSL'  then gsl
             else             0  
           end as wkgbtr,  
                               
           case  wkfbtr
             when 'KFSL'   then kfsl
             when 'KFSL2'  then kfsl2
             when 'KFSL3'  then kfsl3  
             else               0        
           end as wkfbtr,
                                    
           case  pagbtr
             when 'PSL'    then psl
             when 'PSL2'   then psl2
             when 'PSL3'   then psl3  
             else               0        
           end as pagbtr,  
           case  pafbtr
             when 'PFSL'   then pfsl
             when 'PFSL2'  then pfsl2
             when 'PFSL3'  then pfsl3  
             else               0        
           end as pafbtr, 
           
           a.co_megbtr      as megbtr,
           a.co_mefbtr      as mefbtr,
--           a.msl            as mbgbtr,
--           a.mfsl           as mbfbtr,  
           a.muvflg         as muvflg,
           a.beltp          as beltp,
           0                as timestmp,             
           a.rbukrs         as bukrs, 
           a.rfarea         as fkber,  
           a.segment        as segment,   
           a.rfund          as geber,
           a.rgrant_nbr     as grant_nbr,
           rbudget_pd       as budget_pd             
             
        from acdoca         as a
  inner join t000           as s  on     s.mandt    = a.rclnt
                                 and (   s.logsys   = a.logsyso 
                                      or a.logsyso  = '' )        
  inner join finsc_ledger   as l  on  l.rldnr    = a.rldnr
                                 and  l.appl     = 'FI'
                                 and  l.subappl  = 'GLF'  
                                 and  l.xleading <> ''
  inner join finsc_cmp_versnc as v  on v.bukrs = a.rbukrs        --- select only the ledger which is assigned to version 000;
                                   and v.rldnr = a.rldnr         --- Ledger space is obsolete     or a.rldnr = '' )
                                   and v.versn = '000'                                
  inner join tj01      as t  on t.vrgng = a.vrgng  
                            and t.wtkat = 'A'
                            and t.xcosp = '' 
                            and t.xcoss = 'X' 
  inner join tka01     as tk on  tk.mandt  = a.rclnt  
                            and  tk.kokrs  = a.kokrs                            
  where a.mig_source = 'C' and a.bstat = 'C' 
                           and a.accasty <> '' --only actuals; former value type 04
                           and not ( _dataaging is null or _dataaging = '00000000' ) 
  ;