v_cosp_epa_l1_view

@AbapCatalog.sqlViewName: 'V_COSP_EPA_L1'
@EndUserText.label: 'Rebuild COSP for archived COEP from ACDOCA - part 1'

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

define view v_cosp_epa_l1_view as
   select    
          a.rclnt as mandt,
          key kokrs,         
          key co_belnr as belnr,    
          key co_buzei as buzei,    
          poper as PERIO,
          
          case 
//            when co_osl   <> 0     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,  

          rwcur as TWAER,  
          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,
           
--          vmsl as MEGBTR,
--          vmfsl as MEFBTR,     
          co_megbtr as megbtr,
          co_mefbtr as mefbtr,           
          '00' as LEDNR,   --- 1 : 1 only 0L?
          objnr,
          ryear as GJAHR, --- gjahr as GJAHR
          '04' as WRTTP,      
          v.versn as VERSN,     --- default: '000'; delta versions?
          racct as KSTAR, --- always filled?
          hrkft,
          a.vrgng,          
          rassc as VBUND,         --- VBUND = RASSC?
          sbusa as PARGB,         --- check???
          case when t.vrgng <> ''
                    or  
                                    -- a.vrgng = 'COIN' or a.vrgng = 'RKU1' or a.vrgng = 'COIE'
                                    -- or a.vrgng = 'RKU2' or a.vrgng = 'RKU3'
                                    -- or a.vrgng = 'KZRI' or 
                                     a.vrgng  = 'KSI1' or a.vrgng = 'KSP1'
                                    -- or a.vrgng = 'RKIB' or a.vrgng = 'RKPB' or a.vrgng = 'RRIB'
                                    -- or a.vrgng = 'KZRP' or a.vrgng = 'KSP1'                          
                and ( co_belkz <> 'L' and co_belkz <> 'A' ) 
                then drcrk else co_belkz end as BEKNZ,      --- other VRGNG's ?-> see FUNCTION romu_om_fill_belkz.
                              
--          rvunit as MEINH,
          co_meinh as meinh,
          case when runit is null then '' else runit end as MEINB,       
          cast(muvflg as abap.int1) as incomp,    
          beltp,
          rbukrs as BUKRS, --ok
          rbusa as GSBER,   ---??? check!
          rfarea as FKBER,   --- fits to FKBER_LONG??
          bwstrat,
          objnr_hk, 
          timestamp as TIMESTMP, -- acdoca-timestamp:  20.141.030.145.000 COEP: KOKRS: C001, BELNR= 5000005226; timestmp: 7.835.286.530.000
         
         --seconds_between( to_timestamp( '1990-01-01 00:00:00' ), cast(timestamp as abap.timestamp) ) * 10000 as timestmp,
         --  case when timestamp > 0 then second( add_seconds( to_timestamp('1990-01-01 00:00:00'), ( to_int( timestamp / 10000 ) ) ) ) + 100 * minute( add_seconds( to_timestamp('1990-01-01 00:00:00'), ( to_int( timestamp / 10000 ) ) ) ) + 10000 * hour( add_seconds( to_timestamp('1990-01-01 00:00:00'), ( to_int( timestamp / 10000 ) ) ) ) + 1000000 * dayofmonth( add_seconds( to_timestamp('1990-01-01 00:00:00'), ( to_int( timestamp / 10000 ) ) ) ) +         100000000 * month( add_seconds( to_timestamp('1990-01-01 00:00:00'), ( to_int( timestamp / 10000 ) ) ) ) + 10000000000 * year( add_seconds( to_timestamp('1990-01-01 00:00:00'), ( to_int( timestamp / 10000 ) ) ) ) else 0 end as timestmp,
          
          segment,
          '' as GEBER,          -- to do: soon available in ACDOCA
          '' as GRANT_NBR,      -- to do: soon available in ACDOCA
          '' as BUDGET_PD       -- to do: soon available in ACDOCA
    from acdoca  as a 
    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        
                                     and v.versn = '000'
    left outer join tj01 as t on t.vrgng = a.vrgng
                             and t.wtkat = 'A' 
                             and t.xcosp = 'X'                                 
    where a.co_belnr <> '' 
      and a.mig_source = 'C'
      and a.bstat      = 'C'      
      ;