FGL_BCF_MIG

@AbapCatalog.sqlViewName: 'FGLV_BCF_MIG'
@EndUserText.label: 'Calculate Balances from open items'

@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.sizeCategory: #XXL 
@ObjectModel.usageType.dataClass: #MIXED

define view FGL_BCF_MIG as
-- select correct values from open items
  select rclnt, f.ryear as ryear, drcrk, '000' as poper,
  
  s.mitkz,
  
  case s.xsalh when 'X' then hsl else tsl end as tsl, 
  hsl, ksl, osl, msl, hsl as wsl, co_osl, pfsl, psl, vmsl, vsl, bsl, csl, dsl, esl, fsl, gsl, fcsl, hvkwrt,
  coalesce( c.ttcfo, y.rmvct ) as rmvct,

  afabe, anbwa, anlgr, anlgr2, anln1, anln2, awsys, awtyp, blart, bttype, bwkey, bwtar, eprctr,
  glaccount_type, kalnr, koart, kokrs, y.ktop2, y.ktopl, ktosl, kunnr, kzbws, lifnr, lokkt, matnr,
  mat_kdauf, mat_kdpos, mat_lifnr, mat_pspnr, movcat, y.periv, pprctr, prctr, psegment, 
  quant1, quant2, quant3, qunit1, qunit2, qunit3, racct, rassc, f.rbukrs, rbusa, rcntr, rco_ocur,
  rfarea, rbcur, rccur, rdcur, recur, rfcur, rgcur, rfccur, rhcur, rhoart, rkcur, f.rldnr, rocur, rrcty, rtcur, runit, rvunit, rvcur, rhcur as rwcur,
  sbusa, scntr, segment, sfarea, slalittype, sobkz, vorgn, vrgng, xobew, y.hbkid, y.hktid, y.anlkl, y.mwskz,
  -- PA Customer Fields
  brsch, bzirk, kdgrp, konzs, kunre, kunwe, y.land1, matkl, matnr_copa, spart, vkorg, vtweg,    
  -- Public sector 
  re_bukrs, y.re_account, y.fikrs, fistl, measure, rfund, rgrant_nbr, rbudget_pd, sfund, sgrant_nbr, sbudget_pd, 
  case when t001.fikrs <> '' then coalesce( ps.bdgt_acct_cf_to, y.bdgt_account ) else y.bdgt_account end as bdgt_account, bdgt_account_cocode,
  -- JVA
  vname, egrup, y.recid, btype, prodper, cbrunid, jvactivity, pvname, pegrup, y.etype,
  --CO
  aufnr, ps_posid, ps_psp_pnr, pps_posid, ps_pspid, ps_prj_pnr, kdauf, kdpos, pkdauf, pkdpos, 
  werks, bukrs_sender, racct_sender, pps_prj_pnr, pps_psp_pnr, 
  vptnr, swenr, sgenr, sgrnr,
  -- Accrual
  acrlogsys, acritmtype, acrobjtype, acrobj_id, acrsobj_id, 
  -- Valuation
  valobjtype, valobj_id, valsobj_id, risk_class,
  -- Group Reporting
  y.rbunit,
  y.rbuptr,
  y.rcomp,
  y.ritclg,
  y.ritem,
  y.sityp,
  y.subit,
  -- Banking
  y.fs_product_group,
    
-- placeholder for fields of extension include of acdoca
-- placeholder for fields of extension include of acdoca
-- placeholder for fields of extension include of acdoca
//<$VF>

//<$FIELDS>

//<$VF>

-- placeholder for fields of extension include of acdoca
-- placeholder for fields of extension include of acdoca
-- placeholder for fields of extension include of acdoca
  'F' as mig_source -- to distinguish from normal balance carry forward
  from acdoca as y
  join fgls_bcf_mig_fy as f on y.rclnt = f.mandt and y.rldnr = f.rldnr and y.rbukrs = f.rbukrs 
  join skb1 as s on y.rclnt = s.mandt and s.bukrs = y.rbukrs and s.saknr = y.racct 
           and ( ( ( s.xopvw = 'X' or s.xlgclr = 'X' ) and s.mitkz = ' ' ) or  s.mitkz = 'D' or s.mitkz = 'K' )
  inner join t001                as t001 on y.rbukrs = t001.bukrs
  left outer join t856 as c    on c.trtyp = y.rmvct and c.ttcfo <> '   '
  left outer join psm_d_glacctx  as ps   on ps.chrt_accts = y.ktopl and ps.gl_account = y.bdgt_account
  
  where y.budat <= f.cfday 
    and (    y.augdt > f.cfday        -- cleared after last day of previous fiscal year     
          or y.augdt    = '00000000'  -- still open
          or y.augbl = 'ALE-extern'   -- pseudo clearing info 'ALE-extern' (processed in another system) and will always remain open
        )
    and poper <> '000'  
    and bstat <> 'C'
    and ( koart = 'K' or koart = 'D' or y.xopvw = 'X' ) --> all open item managed accounts  

union all 


-- undo all items of period 0 (for the same accounts)
  select y.rclnt, y.ryear, drcrk, poper,
  
  s.mitkz,
  
  -tsl as tsl, -hsl as hsl, -ksl as ksl, -osl as osl, -msl as msl, -hsl as wsl, -co_osl as co_osl, -pfsl as pfsl, -psl as psl, -vmsl as vmsl, -vsl as vsl, 
  -bsl as bsl, -csl as csl, -dsl as dsl, -esl as esl, -fsl as fsl, -gsl as gsl, -fcsl as fcsl, -hvkwrt as hvkwrt,
  rmvct,
    
  afabe, anbwa, anlgr, anlgr2, anln1, anln2, awsys, awtyp, blart, bttype, bwkey, bwtar, eprctr,
  glaccount_type, kalnr, koart, kokrs, ktop2, y.ktopl, ktosl, kunnr, kzbws, lifnr, lokkt, matnr,
  mat_kdauf, mat_kdpos, mat_lifnr, mat_pspnr, movcat, y.periv, pprctr, prctr, psegment, 
  quant1, quant2, quant3, qunit1, qunit2, qunit3, racct, rassc, f.rbukrs, rbusa, rcntr, rco_ocur,
  rfarea, rbcur, rccur, rdcur, recur, rfcur, rgcur, rfccur, rhcur, rhoart, rkcur, f.rldnr, rocur, rrcty, rtcur, runit, rvunit, rvcur, rhcur as rwcur,
  sbusa, scntr, segment, sfarea, slalittype, sobkz, vorgn, vrgng, xobew, y.hbkid, y.hktid, y.anlkl, y.mwskz,
  -- PA Customer Fields
  brsch, bzirk, kdgrp, konzs, kunre, kunwe, y.land1, matkl, matnr_copa, spart, vkorg, vtweg,    
  -- Public sector 
  re_bukrs, re_account, fikrs, fistl, measure, rfund, rgrant_nbr, rbudget_pd, sfund, sgrant_nbr, sbudget_pd, bdgt_account, bdgt_account_cocode,
  -- JVA
  vname, egrup, y.recid, btype, prodper, cbrunid, jvactivity, pvname, pegrup, y.etype,
  --CO
  aufnr, ps_posid, ps_psp_pnr, pps_posid, ps_pspid, ps_prj_pnr, kdauf, kdpos, pkdauf, pkdpos, 
  werks, bukrs_sender, racct_sender, pps_prj_pnr, pps_psp_pnr, 
  vptnr, swenr, sgenr, sgrnr,
  -- Accrual
  acrlogsys, acritmtype, acrobjtype, acrobj_id, acrsobj_id, 
  -- Valuation
  valobjtype, valobj_id, valsobj_id, risk_class, 
  -- Group Reporting
  y.rbunit,
  y.rbuptr,
  y.rcomp,
  y.ritclg,
  y.ritem,
  y.sityp,
  y.subit,
  -- Banking
  y.fs_product_group,
    
-- placeholder for fields of extension include of acdoca
-- placeholder for fields of extension include of acdoca
-- placeholder for fields of extension include of acdoca
//<$VF>

//<$FIELDS>

//<$VF>

-- placeholder for fields of extension include of acdoca
-- placeholder for fields of extension include of acdoca
-- placeholder for fields of extension include of acdoca
  'F' as mig_source -- to distinguish from normal balance carry forward
  from acdoca as y
  join fgls_bcf_mig_fy as f on y.rclnt = f.mandt and y.rldnr = f.rldnr and y.rbukrs = f.rbukrs and y.ryear = f.ryear  
  join skb1 as s on y.rclnt = s.mandt and s.bukrs = y.rbukrs and s.saknr = y.racct 
           and ( ( ( s.xopvw = 'X' or s.xlgclr = 'X' ) and s.mitkz = ' ' ) or  s.mitkz = 'D' or s.mitkz = 'K' ) 
  
  where y.poper = '000'