@AbapCatalog.sqlViewName: 'FGLV_BCF_BS'
@EndUserText.label: 'Balance Carry Forward: Balance Sheet'
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AbapCatalog.viewEnhancementCategory: [#PROJECTION_LIST, #UNION]
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass: #MIXED
define view FGL_BCF_BS
with parameters
p_rldnr : fagl_rldnr, -- test ledger in case of bcf in test mode
p_bukrs : bukrs,
p_fromYear : gjahr,
p_toYear : gjahr
as select -- Period 000 of To-Year
y.belnr,
y.docln,
'1' as unionCode,
'BCF ' as temp_appl,
' ' as temp_id,
' ' as bcf_test_data,
-- Base Fields
$parameters.p_rldnr as rldnr,
y.rbukrs,
$parameters.p_toYear as ryear,
'000' as poper,
y.racct,
y.ktopl,
@Semantics.amount.currencyCode: 'RTCUR'
-y.tsl as tsl,
@Semantics.amount.currencyCode: 'RHCUR'
-y.hsl as hsl,
@Semantics.amount.currencyCode: 'RKCUR'
-y.ksl as ksl,
y.rtcur,
y.rhcur,
y.rkcur,
y.ryear as ryear_orig,
y.gjahr as original_gjahr,
y.gkont as gkont, -- Original Values
'BS_TO' as bcf_type, -- Type Indicator
a.xopvw,
a.mitkz,
-- Calculated Values
@Semantics.amount.currencyCode: 'RWCUR'
-y.hsl as wsl,
y.rhcur as rwcur,
y.lokkt,
y.ktop2,
y.rmvct,
y.movcat,
y.anbwa,
y.slalittype,
y.afabe,
y.anlgr,
y.anlgr2,
y.anln1,
y.anln2,
y.cbrunid,
y.bdgt_account,
y.bdgt_account_cocode,
case when y.rrcty = '5' or y.awtyp = 'GLYEC' then y.koart
when a.mitkz = 'A' or a.mitkz = 'D' or a.mitkz = 'K' then a.mitkz
when a.mitkz = '' and y.ktosl = 'BSX' then 'M'
else 'S' end as koart,
-- Additonal Fields
@Semantics.amount.currencyCode: 'ROCUR'
-y.osl as osl,
@Semantics.quantity.unitOfMeasure: 'RUNIT'
-y.msl as msl,
@Semantics.amount.currencyCode: 'RCO_OCUR'
-y.co_osl as co_osl,
@Semantics.amount.currencyCode: 'RKCUR'
-y.pfsl as pfsl,
@Semantics.amount.currencyCode: 'RKCUR'
-y.psl as psl,
@Semantics.quantity.unitOfMeasure: 'RVUNIT'
-y.vmsl as vmsl,
@Semantics.amount.currencyCode: 'RVCUR'
-y.vsl as vsl,
@Semantics.amount.currencyCode: 'RBCUR'
-y.bsl as bsl,
@Semantics.amount.currencyCode: 'RCCUR'
-y.csl as csl,
@Semantics.amount.currencyCode: 'RDCUR'
-y.dsl as dsl,
@Semantics.amount.currencyCode: 'RECUR'
-y.esl as esl,
@Semantics.amount.currencyCode: 'RFCUR'
-y.fsl as fsl,
@Semantics.amount.currencyCode: 'RGCUR'
-y.gsl as gsl,
@Semantics.amount.currencyCode: 'RHCUR'
-y.hvkwrt as hvkwrt,
@Semantics.amount.currencyCode: 'RFCCUR'
-y.fcsl as fcsl,
y.awsys,
y.awtyp,
y.blart,
y.bttype,
y.bwkey,
y.bwtar,
y.eprctr,
y.glaccount_type,
y.kalnr,
--koart,
y.kokrs,
y.ktosl,
y.kunnr,
y.kzbws,
y.lifnr,
y.matnr,
y.mat_kdauf,
y.mat_kdpos,
y.mat_lifnr,
y.mat_pspnr,
y.periv,
y.pprctr,
y.prctr,
y.psegment,
y.quant1,
y.quant2,
y.quant3,
y.qunit1,
y.qunit2,
y.qunit3,
y.rassc,
y.rbusa,
y.rcntr,
y.rco_ocur,
y.rfarea,
y.rbcur,
y.rccur,
y.rdcur,
y.recur,
y.rfcur,
y.rgcur,
y.rfccur,
y.rhoart,
y.rocur,
y.rrcty,
y.runit,
y.rvunit,
y.rvcur,
y.sbusa,
y.scntr,
y.segment,
y.sfarea,
y.sobkz,
y.vorgn,
y.vrgng,
y.xobew,
y.hbkid,
y.hktid,
y.anlkl,
y.mwskz,
-- PA Customer Fields
y.brsch,
y.bzirk,
y.kdgrp,
y.konzs,
y.kunre,
y.kunwe,
y.land1,
y.matkl,
y.matnr_copa,
y.spart,
y.vkorg,
y.vtweg,
-- Public sector
y.re_account,
y.rbudget_pd,
y.sbudget_pd,
y.re_bukrs,
y.fikrs,
y.fistl,
y.measure,
y.rfund,
y.rgrant_nbr,
y.sfund,
y.sgrant_nbr,
-- JVA
y.vname,
y.egrup,
y.recid,
y.btype,
y.prodper,
y.jvactivity,
y.pvname,
y.pegrup,
y.etype,
--CO
y.aufnr,
y.ps_posid,
y.ps_psp_pnr,
y.pps_posid,
y.ps_pspid,
y.ps_prj_pnr,
y.kdauf,
y.kdpos,
y.pkdauf,
y.pkdpos,
y.werks,
y.bukrs_sender,
y.racct_sender,
y.pps_prj_pnr,
y.pps_psp_pnr,
y.pps_pspid,
y.vptnr,
y.swenr,
y.sgenr,
y.sgrnr,
-- Accrual
y.acrlogsys,
y.acritmtype,
y.acrobjtype,
y.acrobj_id,
y.acrsobj_id,
-- Valuation
y.valobjtype,
y.valobj_id,
y.valsobj_id,
y.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
' ' as mig_source
from acdoca as y
inner join fgl_bcf_acc as a on a.bukrs = y.rbukrs
and a.racct = y.racct
and a.ktopl = y.ktopl
and a.xbilk_internal = 'X'
where y.poper = '000'
and y.bstat = 'C'
and y.rldnr = $parameters.p_rldnr
and y.rbukrs = $parameters.p_bukrs
and y.ryear = $parameters.p_toYear
and y.fiscyearper = concat($parameters.p_toYear, '000' ) -- for FISCYEARPER partition pruning
union all
select -- All Periods of From-Year
y.belnr,
y.docln,
'1' as unionCode,
'BCF' as temp_appl,
' ' as temp_id,
' ' as bcf_test_data,
-- Base Fields
$parameters.p_rldnr as rldnr,
y.rbukrs,
$parameters.p_toYear as ryear,
'000' as poper,
y.racct,
y.ktopl,
y.tsl,
y.hsl,
y.ksl,
y.rtcur,
y.rhcur,
y.rkcur,
y.ryear as ryear_orig,
y.gjahr as original_gjahr,
y.racct as gkont, -- Original Values
'BS_FR' as bcf_type, -- Type Indicator
a.xopvw,
a.mitkz,
-- Calculated Values
y.hsl as wsl,
y.rhcur as rwcur,
y.lokkt, --always derived in ABAP
y.ktop2,
case a.xopvw
when 'X' -- includes mitkz DK account
then coalesce( c.ttcfo, '' )
else coalesce( c.ttcfo, y.rmvct )
end as rmvct,
case
when y.koart = 'A' or a.mitkz = 'A' or y.slalittype between '07000' and '07209' -- asset line item BCF movecat = 00 -- e.g. koart S and 7004 for derived area
then '00'
else ''
end as movcat,
case
when y.koart = 'A' or a.mitkz = 'A' or y.slalittype between '07000' and '07209' -- asset line item BCF anbwa = 999
then '999'
else ''
end as anbwa,
case
when d.slalittype_trgt is null
then y.slalittype
when y.koart = 'A' or a.mitkz = 'A' or y.slalittype between '07000' and '07209' -- asset line item BCF slalitype based on finsts_faa_bcf
then d.slalittype_trgt
else y.slalittype
end as slalittype,
case
when y.koart = 'A' or a.mitkz = 'A' or y.slalittype between '07000' and '07209'
then y.afabe
else '00'
end as afabe,
case
when y.koart = 'A' or a.mitkz = 'A' or y.slalittype between '07000' and '07209'
then y.anlgr
else ''
end as anlgr,
case
when y.koart = 'A' or a.mitkz = 'A' or y.slalittype between '07000' and '07209'
then y.anlgr2
else ''
end as anlgr2,
case
when y.koart = 'A' or a.mitkz = 'A' or y.slalittype between '07000' and '07209'
then y.anln1
else ''
end as anln1,
case
when y.koart = 'A' or a.mitkz = 'A' or y.slalittype between '07000' and '07209'
then y.anln2
else ''
end as anln2,
case
when y.cbrunid <> 0 then 999
else 0
end as cbrunid,
case when a.fikrs <> '' then coalesce( ps.bdgt_acct_cf_to, y.bdgt_account ) else y.bdgt_account end as bdgt_account,
y.bdgt_account_cocode,
case when y.rrcty = '5' or y.awtyp = 'GLYEC' then y.koart
when a.mitkz = 'A' or a.mitkz = 'D' or a.mitkz = 'K' then a.mitkz
when a.mitkz = '' and y.ktosl = 'BSX' then 'M'
else 'S' end as koart,
-- Additonal Fields
y.osl,
y.msl,
y.co_osl,
y.pfsl,
y.psl,
y.vmsl,
y.vsl,
y.bsl,
y.csl,
y.dsl,
y.esl,
y.fsl,
y.gsl,
y.hvkwrt,
y.fcsl,
y.awsys,
y.awtyp,
y.blart,
y.bttype,
y.bwkey,
y.bwtar,
y.eprctr,
y.glaccount_type,
y.kalnr,
--koart,
y.kokrs,
y.ktosl,
y.kunnr,
y.kzbws,
y.lifnr,
y.matnr,
y.mat_kdauf,
y.mat_kdpos,
y.mat_lifnr,
y.mat_pspnr,
y.periv,
y.pprctr,
y.prctr,
y.psegment,
y.quant1,
y.quant2,
y.quant3,
y.qunit1,
y.qunit2,
y.qunit3,
y.rassc,
y.rbusa,
y.rcntr,
y.rco_ocur,
y.rfarea,
y.rbcur,
y.rccur,
y.rdcur,
y.recur,
y.rfcur,
y.rgcur,
y.rfccur,
y.rhoart,
y.rocur,
y.rrcty,
y.runit,
y.rvunit,
y.rvcur,
y.sbusa,
y.scntr,
y.segment,
y.sfarea,
y.sobkz,
y.vorgn,
y.vrgng,
y.xobew,
y.hbkid,
y.hktid,
y.anlkl,
y.mwskz,
-- PA Customer Fields
y.brsch,
y.bzirk,
y.kdgrp,
y.konzs,
y.kunre,
y.kunwe,
y.land1,
y.matkl,
y.matnr_copa,
y.spart,
y.vkorg,
y.vtweg,
-- Public sector
y.re_account,
y.rbudget_pd,
y.sbudget_pd,
y.re_bukrs,
y.fikrs,
y.fistl,
y.measure,
y.rfund,
y.rgrant_nbr,
y.sfund,
y.sgrant_nbr, --bdgt_account,
-- JVA
y.vname,
y.egrup,
y.recid,
y.btype,
y.prodper,
y.jvactivity,
y.pvname,
y.pegrup,
y.etype,
--CO
y.aufnr,
y.ps_posid,
y.ps_psp_pnr,
y.pps_posid,
y.ps_pspid,
y.ps_prj_pnr,
y.kdauf,
y.kdpos,
y.pkdauf,
y.pkdpos,
y.werks,
y.bukrs_sender,
y.racct_sender,
y.pps_prj_pnr,
y.pps_psp_pnr,
y.pps_pspid,
y.vptnr,
y.swenr,
y.sgenr,
y.sgrnr,
-- Accrual
y.acrlogsys,
y.acritmtype,
y.acrobjtype,
y.acrobj_id,
y.acrsobj_id,
-- Valuation
y.valobjtype,
y.valobj_id,
y.valsobj_id,
y.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
' ' as mig_source
from acdoca as y
inner join fgl_bcf_acc as a on a.bukrs = y.rbukrs
and a.racct = y.racct
and a.ktopl = y.ktopl
and a.xbilk_internal = 'X'
left outer join t856 as c on c.trtyp = y.rmvct
and c.ttcfo <> ' ' -- ' ' to not use initial values
left outer join finsts_faa_bcf as d on d.slalittype_srce = y.slalittype
and d.slalittype_trgt <> '00000' --originally that was space !!!!!!!!!
left outer join psm_d_glacctx as ps on ps.chrt_accts = y.ktopl
and ps.gl_account = y.bdgt_account
where y.rldnr = $parameters.p_rldnr
and y.rbukrs = $parameters.p_bukrs
and y.ryear = $parameters.p_fromYear
and y.fiscyearper >= concat($parameters.p_fromYear, '000' ) -- for FISCYEARPER partition pruning
and y.fiscyearper <= concat($parameters.p_fromYear, '999' ) -- for FISCYEARPER partition pruning
union all
select -- Period 000 of To-Year (of TEST Data)
y.belnr,
y.docln,
'2' as unionCode,
y.temp_appl as temp_appl,
y.temp_id as temp_id,
'X' as bcf_test_data,
-- Base Fields
$parameters.p_rldnr as rldnr,
y.rbukrs,
$parameters.p_toYear as ryear,
'000' as poper,
y.racct,
y.ktopl,
-y.tsl as tsl,
-y.hsl as hsl,
-y.ksl as ksl,
y.rtcur,
y.rhcur,
y.rkcur,
y.ryear as ryear_orig,
y.gjahr as original_gjahr,
y.gkont as gkont, -- Original Values
'BS_TO' as bcf_type, -- Type Indicator
a.xopvw,
a.mitkz,
-- Calculated Values
-y.hsl as wsl,
y.rhcur as rwcur,
y.lokkt,
y.ktop2,
y.rmvct,
y.movcat,
y.anbwa,
y.slalittype,
y.afabe,
y.anlgr,
y.anlgr2,
y.anln1,
y.anln2,
y.cbrunid,
y.bdgt_account,
y.bdgt_account_cocode,
case when a.mitkz = 'A' or a.mitkz = 'D' or a.mitkz = 'K' then a.mitkz
when a.mitkz = '' and y.ktosl = 'BSX' then 'M'
else 'S' end as koart,
-- Additonal Fields
-y.osl as osl,
-y.msl as msl,
-y.co_osl as co_osl,
-y.pfsl as pfsl,
-y.psl as psl,
-y.vmsl as vmsl,
-y.vsl as vsl,
-y.bsl as bsl,
-y.csl as csl,
-y.dsl as dsl,
-y.esl as esl,
-y.fsl as fsl,
-y.gsl as gsl,
-y.hvkwrt as hvkwrt,
-y.fcsl as fcsl,
y.awsys,
y.awtyp,
y.blart,
y.bttype,
y.bwkey,
y.bwtar,
y.eprctr,
y.glaccount_type,
y.kalnr,
--koart,
y.kokrs,
y.ktosl,
y.kunnr,
y.kzbws,
y.lifnr,
y.matnr,
y.mat_kdauf,
y.mat_kdpos,
y.mat_lifnr,
y.mat_pspnr,
y.periv,
y.pprctr,
y.prctr,
y.psegment,
y.quant1,
y.quant2,
y.quant3,
y.qunit1,
y.qunit2,
y.qunit3,
y.rassc,
y.rbusa,
y.rcntr,
y.rco_ocur,
y.rfarea,
y.rbcur,
y.rccur,
y.rdcur,
y.recur,
y.rfcur,
y.rgcur,
y.rfccur,
y.rhoart,
y.rocur,
y.rrcty,
y.runit,
y.rvunit,
y.rvcur,
y.sbusa,
y.scntr,
y.segment,
y.sfarea,
y.sobkz,
y.vorgn,
y.vrgng,
y.xobew,
y.hbkid,
y.hktid,
y.anlkl,
y.mwskz,
-- PA Customer Fields
y.brsch,
y.bzirk,
y.kdgrp,
y.konzs,
y.kunre,
y.kunwe,
y.land1,
y.matkl,
y.matnr_copa,
y.spart,
y.vkorg,
y.vtweg,
-- Public sector
y.re_account,
y.rbudget_pd,
y.sbudget_pd,
y.re_bukrs,
y.fikrs,
y.fistl,
y.measure,
y.rfund,
y.rgrant_nbr,
y.sfund,
y.sgrant_nbr,
-- JVA
y.vname,
y.egrup,
y.recid,
y.btype,
y.prodper,
y.jvactivity,
y.pvname,
y.pegrup,
y.etype,
--CO
y.aufnr,
y.ps_posid,
y.ps_psp_pnr,
y.pps_posid,
y.ps_pspid,
y.ps_prj_pnr,
y.kdauf,
y.kdpos,
y.pkdauf,
y.pkdpos,
y.werks,
y.bukrs_sender,
y.racct_sender,
y.pps_prj_pnr,
y.pps_psp_pnr,
y.pps_pspid,
y.vptnr,
y.swenr,
y.sgenr,
y.sgrnr,
-- Accrual
y.acrlogsys,
y.acritmtype,
y.acrobjtype,
y.acrobj_id,
y.acrsobj_id,
-- Valuation
y.valobjtype,
y.valobj_id,
y.valsobj_id,
y.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
' ' as mig_source
from acdoctemp as y
inner join fgl_bcf_acc as a on a.bukrs = y.rbukrs
and a.racct = y.racct
and a.ktopl = y.ktopl
and a.xbilk_internal = 'X'
where y.poper = '000'
and y.bstat = 'C'
and y.rldnr = $parameters.p_rldnr
and y.rbukrs = $parameters.p_bukrs
and y.ryear = $parameters.p_toYear
and y.fiscyearper = concat($parameters.p_toYear, '000' ) -- for FISCYEARPER partition pruning
and y.temp_appl = 'BCF'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"FGL_BCF_ACC",
"ACDOCA",
"ACDOCTEMP",
"FINSTS_FAA_BCF",
"PSM_D_GLACCTX",
"T856"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/