V_COSS_X_PER_1_VIEW

@AbapCatalog.sqlViewName: 'V_COSS_X_PER1'
@AbapCatalog.compiler.compareFilter: true
@EndUserText.label: 'COSS totals in all currencies/valuations - 1'

//@ClientHandling.algorithm: #SESSION_VARIABLE

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

define view V_COSS_X_PER_1_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.racct      as kstar,
           key a.hrkft      as hrkft,
           key a.vrgng      as vrgng,
           key case when parobsrc = '1' then parob1 else case when parobsrc = '2' then paccasty else '' end end as parob,
           key a.uspob      as uspob,
           key a.co_beknz   as beknz,           
           key a.rwcur      as twaer,

/*
           case when b.buzei = 'CO_BUZEI'  then a.co_buzei
                when b.buzei = 'CO_BUZEI1' then a.co_buzei1
                when b.buzei = 'CO_BUZEI2' then a.co_buzei2
                when b.buzei = 'CO_BUZEI5' then a.co_buzei5
                when b.buzei = 'CO_BUZEI6' then a.co_buzei6
                when b.buzei = 'CO_BUZEI7' then a.co_buzei7
                else                            a.co_buzei
           end              as buzei,
*/

           a.rldnr          as rldnr,

           a.poper          as perio,
--           a.rvunit         as meinh,
           a.co_meinh       as meinh,

--         value fields
           case
             when m.curtph <> c.curtype then 0
             when b.wtgbtr = 'WSL'  then wsl
             when b.wtgbtr = 'WSL2' then wsl2
             when b.wtgbtr = 'WSL3' then wsl3
             else             0
           end as wtgbtr,           

           case
             when m.curtph <> c.curtype then 0           
//             when ( tk.xwbuk = ''  )  and  (   ( bstat =  'C' and co_osl <> 0 )

//                                            or ( bstat <> 'C' and rco_ocur <> '' ) )

//                                                 then co_osl

             when b.wogbtr = 'CO_OSL'  then co_osl
             when b.wogbtr = 'HSL'  then hsl
             when b.wogbtr = 'OSL'  then osl
             when b.wogbtr = 'VSL'  then vsl
             when b.wogbtr = 'BSL'  then bsl
             when b.wogbtr = 'CSL'  then csl
             when b.wogbtr = 'DSL'  then dsl
             when b.wogbtr = 'ESL'  then esl
             when b.wogbtr = 'FSL'  then fsl
             when b.wogbtr = 'GSL'  then gsl
             when b.wogbtr = 'KSL'  then ksl
             else                        0
           end  as wogbtr,    --- 1. CO_OSL; 2. HSL
           case
//             when ( tk.xwbuk = ''  )  and  (   ( bstat =  'C' and co_osl <> 0 )

//                                            or ( bstat <> 'C' and rco_ocur <> '' ) )

//                                                 then rco_ocur

             when b.wogbtr = 'CO_OSL'  then rco_ocur
             when b.wogbtr = 'HSL'  then rhcur
             when b.wogbtr = 'OSL'  then rocur
             when b.wogbtr = 'VSL'  then rvcur
             when b.wogbtr = 'BSL'  then rbcur
             when b.wogbtr = 'CSL'  then rccur
             when b.wogbtr = 'DSL'  then rdcur
             when b.wogbtr = 'ESL'  then recur
             when b.wogbtr = 'FSL'  then rfcur
             when b.wogbtr = 'GSL'  then rgcur
             when b.wogbtr = 'KSL'  then rkcur
             else                        ''
           end  as rocur,    
           

           case  
             when m.curtpk <> c.curtype then 0           
             when b.wkgbtr = 'KSL'  then ksl
             when b.wkgbtr = 'OSL'  then osl
             when b.wkgbtr = 'VSL'  then vsl
             when b.wkgbtr = 'BSL'  then bsl
             when b.wkgbtr = 'CSL'  then csl
             when b.wkgbtr = 'DSL'  then dsl
             when b.wkgbtr = 'ESL'  then esl
             when b.wkgbtr = 'FSL'  then fsl
             when b.wkgbtr = 'GSL'  then gsl
             when b.wkgbtr = 'HSL'  then hsl
             else             0
           end as wkgbtr,
           case  
             when m.curtpk <> c.curtype then 0    
             when b.wkfbtr = 'KFSL'   then kfsl
             when b.wkfbtr = 'KFSL2'  then kfsl2
             when b.wkfbtr = 'KFSL3'  then kfsl3
            else               0
           end as wkfbtr,           
           case  b.wkgbtr
             when 'KSL'  then rkcur
             when 'OSL'  then rocur
             when 'VSL'  then rvcur
             when 'BSL'  then rbcur
             when 'CSL'  then rccur
             when 'DSL'  then rdcur
             when 'ESL'  then recur
             when 'FSL'  then rfcur
             when 'GSL'  then rgcur
             when 'HSL'  then rhcur
             else             ''
           end as rkcur,

           rocur as racur,
           rvcur,           
           rbcur,           
           rccur,
           rdcur,
           recur,
           rfcur,
           rgcur,
           rhcur,
           
           case                                         --- Amount in Freely Defined Currency 1
             when m.curtpo = c.curtype then osl
             else                           0
           end as wagbtr,
           case                                         --- Amount in Freely Defined Currency 2
             when m.curtpv = c.curtype then vsl
             else                           0
           end as wvgbtr,
           case                                         --- Amount in Freely Defined Currency 3
             when m.curtpb = c.curtype then bsl
             else                           0
           end as wbgbtr,
           case                                         --- Amount in Freely Defined Currency 4
             when m.curtpc = c.curtype then csl
             else                           0
           end as wcgbtr,
           case                                         --- Amount in Freely Defined Currency 5
             when m.curtpd = c.curtype then dsl
             else                           0
           end as wdgbtr,
           case                                         --- Amount in Freely Defined Currency 6
             when m.curtpe = c.curtype then esl
             else                           0
           end as wegbtr,    
           case                                         --- Amount in Freely Defined Currency 7
             when m.curtpf = c.curtype then fsl
             else                           0
           end as wfgbtr,   
           case                                         --- Amount in Freely Defined Currency 8
             when m.curtpg = c.curtype then gsl
             else                           0
           end as wggbtr,                  
           case                                         --- Amount in Company Currency          
             when m.curtph = c.curtype then hsl
             else                           0
           end as whgbtr, 
                                 
           
           case  
             when m.curtpk <> c.curtype then 0           
             when b.pagbtr = 'PSL'  then psl
             when b.pagbtr = 'PSL2' then psl2
             when b.pagbtr = 'PSL3' then psl3
             else             0
           end as pagbtr,
           case  
             when m.curtpk <> c.curtype then 0               
             when b.pafbtr = 'PFSL'  then pfsl
             when b.pafbtr = 'PFSL2' then pfsl2
             when b.pafbtr = 'PFSL3' then pfsl3
             else             0
           end as pafbtr,           


           case  
            when m.curtpk = c.curtype then co_megbtr  --  vmsl
            else                           0  
           end as megbtr,
           case  
            when m.curtpk = c.curtype then co_mefbtr  -- vmfsl
            else                           0  
           end as mefbtr,
           
           case  
            when m.curtpk = c.curtype then msl
            else                           0  
           end as mbgbtr,
           case  
            when m.curtpk = c.curtype then mfsl
            else                           0  
           end as mbfbtr, 


           cast(a.muvflg as abap.int1) as muvflg,

           a.beltp          as beltp,
           a.rbukrs         as bukrs,
           a.rfarea         as fkber,
           a.segment        as segment,
           a.rfund          as geber,
           a.rgrant_nbr     as grant_nbr,
           a.rbudget_pd     as budget_pd,
           
           a.paobjnr        as paobjnr,

           a.mig_source     as mig_source,
           a.bstat          as bstat
-----------------------------------------


    from         acdoca            as a                                         
    inner join   tka09             as v   on  v.mandt   = a.rclnt               --- this JOIN an the next 3 ones should be replaced by a new version of FINSC_CMP_VERSNC 
                                         and  v.kokrs   = a.kokrs               --- not very good right now because to many records are temporarily calculated being aggregated in V_COSP_PER
    inner join   finsc_cmp_versnc  as b   on  b.mandt   = a.rclnt        
                                         and  b.bukrs   = a.rbukrs
                                         and  b.versn   = v.versn      
                                         and  b.rldnr   = a.rldnr                                          
    inner join   finsc_curtype     as c   on  c.mandt   = a.rclnt
                                         and  c.valutyp = v.valutyp
    inner join   finsc_ld_cmp      as m   on  m.mandt   = a.rclnt       
                                         and  m.rldnr   = a.rldnr  
                                         and  m.bukrs   = a.rbukrs     
                                         and (    c.curtype = m.curtph 
                                               or c.curtype = m.curtpk
                                               or c.curtype = m.curtpo
                                               or c.curtype = m.curtpv
                                               or c.curtype = m.curtpb
                                               or c.curtype = m.curtpc
                                               or c.curtype = m.curtpd
                                               or c.curtype = m.curtpe
                                               or c.curtype = m.curtpf
                                               or c.curtype = m.curtpg
                                              )


    inner join tj01         as t  on     t.vrgng = a.vrgng                       --- only actuals relevant for primary postings
                                     and t.xcosp = ''
                                     and t.xcoss = 'X'
                                     --- redundant check: and t.wtkat = 'A'
    inner join tka01        as tk on     tk.mandt   = a.rclnt                    --- check logical system for relevanz for totals
                                     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.co_buzei <> '000' and not a.accasty = '' ) or     --- actuals with CO document numbers ; ... where a.co_belnr <> ''  is redundant
            ( a.mig_source = 'C'  and a.bstat = 'C'      )        --- actuals + statisticals from CO balances migration
          ) and
          a.objnr <> ''                                           --- in case CO has been archive OBJNR is initial!
      ;

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"ACDOCA",
"FINSC_CMP_VERSNC",
"FINSC_CURTYPE",
"FINSC_LD_CMP",
"T000",
"TJ01",
"TKA01",
"TKA09"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/