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,

  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'


/*+[internal] {
"BASEINFO":
{
"FROM":
[
"ACDOCA",
"FGLS_BCF_MIG_FY",
"PSM_D_GLACCTX",
"SKB1",
"T001",
"T856"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/