@AbapCatalog.sqlViewName: 'FGLV_BCF_PL'
@EndUserText.label: 'Balance Carry Forward: Profit and Loss'
@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_PL
with parameters
p_rldnr : fagl_rldnr,
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,
@Semantics.amount.currencyCode: 'RHCUR'
-y.hsl as hsl,
@Semantics.amount.currencyCode: 'RKCUR'
-y.ksl as ksl,
y.rhcur, y.rkcur,
y.ryear as org_ryear,
y.gjahr as original_gjahr,
y.gkont as gkont, -- Original Values
'PL_TO' as bcf_type, -- Type Indicator
cast( ' ' as xopvw preserving type ) as xopvw,
cast( ' ' as mitkz preserving type ) as mitkz,
-- Calculated Values
'S' as koart,
y.racct,
y.ktopl,
@Semantics.amount.currencyCode: 'RTCUR'
-y.tsl as tsl,
y.rtcur,
@Semantics.amount.currencyCode: 'RWCUR'
-y.hsl as wsl,
y.rhcur as rwcur,
y.lokkt,
y.ktop2,
y.rmvct,
y.cbrunid,
y.bdgt_account,
y.bdgt_account_cocode,
-- Additonal Fields
@Semantics.amount.currencyCode: 'ROCUR'
-y.osl as osl,
@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.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: 'RFCCUR'
-y.fcsl as fcsl,
@Semantics.amount.currencyCode: 'RHCUR'
-y.hvkwrt as hvkwrt,
y.awsys,
y.awtyp,
y.blart,
y.eprctr,
y.kokrs,
y.periv,
y.pprctr,
y.prctr,
y.psegment,
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.rvcur,
y.sbusa,
y.scntr,
y.segment,
y.sfarea,
y.hbkid,
y.hktid,
y.anlkl,
y.mwskz,
y.matnr,
-- 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.werks,
y.bukrs_sender,
y.racct_sender,
y.vptnr,
--RE
y.smenr,
y.recnnr,
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,
-- Group Reporting
y.rbunit,
y.rbuptr,
y.rcomp,
y.ritclg,
y.ritem,
y.sityp,
y.subit,
-- Banking
y.fs_product_group,
y.kdgrp,
y.land1,
-- 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>
y.gebie_pa, y.kmhi01_pa, y.kmhi02_pa, y.kmhi03_pa, y.kmkdgr_pa,
y.kmland_pa, y.kmvkbu_pa, y.kmvkgr_pa, y.kmvtnr_pa, y.pstlz_pa,
y.wwort_pa, y.wwstr_pa, y.klabc_pa, y.ww1ei_pa, y.ww1zh_pa, y.ww1zi_pa,
y.ww1zm_pa, y.ww1zo_pa, y.ww1zp_pa, y.ww1zs_pa, y.ww1zv_pa,
//<$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 = ''
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.hsl, y.ksl,
y.rhcur, y.rkcur,
y.ryear as org_ryear,
y.gjahr as original_gjahr,
y.racct as gkont, -- Original Values
'PL_FR' as bcf_type, -- Type Indicator
cast( ' ' as xopvw preserving type ) as xopvw,
cast( ' ' as mitkz preserving type ) as mitkz,
-- Calculated Values
'S' as koart,
a.vtkon as racct, y.ktopl,
case when a.xsalh = 'X' then y.hsl else y.tsl end as tsl,
case when a.xsalh = 'X' then y.rhcur else y.rtcur end as rtcur,
y.hsl as wsl, y.rhcur as rwcur,
y.lokkt, --always derived in ABAP
y.ktop2,
coalesce( c.ttcfo, y.rmvct ) as rmvct,
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,
-- Additonal Fields
y.osl as osl,
y.co_osl as co_osl,
y.pfsl as pfsl,
y.psl as psl,
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.fcsl as fcsl,
y.hvkwrt as hvkwrt,
y.awsys,
y.awtyp,
y.blart,
y.eprctr,
y.kokrs,
y.periv,
y.pprctr,
y.prctr,
y.psegment,
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.rvcur,
y.sbusa,
y.scntr,
y.segment,
y.sfarea,
y.hbkid,
y.hktid,
y.anlkl,
y.mwskz,
y.matnr,
-- 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.werks,
y.bukrs_sender,
y.racct_sender,
y.vptnr,
--RE
y.smenr,
y.recnnr,
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,
-- Group Reporting
y.rbunit,
y.rbuptr,
y.rcomp,
y.ritclg,
y.ritem,
y.sityp,
y.subit,
-- Banking
y.fs_product_group,
y.kdgrp,
y.land1,
-- 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>
y.gebie_pa, y.kmhi01_pa, y.kmhi02_pa, y.kmhi03_pa, y.kmkdgr_pa,
y.kmland_pa, y.kmvkbu_pa, y.kmvkgr_pa, y.kmvtnr_pa, y.pstlz_pa,
y.wwort_pa, y.wwstr_pa, y.klabc_pa, y.ww1ei_pa, y.ww1zh_pa, y.ww1zi_pa,
y.ww1zm_pa, y.ww1zo_pa, y.ww1zp_pa, y.ww1zs_pa, y.ww1zv_pa,
//<$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 = ''
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.rldnr = $parameters.p_rldnr -- for fromYear only productive data is considered
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
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.hsl as hsl,
-y.ksl as ksl,
y.rhcur,
y.rkcur,
y.ryear as org_ryear,
y.gjahr as original_gjahr,
y.gkont as gkont, -- Original Values
'PL_TO' as bcf_type, -- Type Indicator
cast( ' ' as xopvw preserving type ) as xopvw,
cast( ' ' as mitkz preserving type ) as mitkz,
-- Calculated Values
'S' as koart,
y.racct,
y.ktopl,
-y.tsl as tsl,
y.rtcur,
-y.hsl as wsl,
y.rhcur as rwcur,
y.lokkt,
y.ktop2,
y.rmvct,
y.cbrunid,
y.bdgt_account,
y.bdgt_account_cocode,
-- Additonal Fields
-y.osl as osl,
-y.co_osl as co_osl,
-y.pfsl as pfsl,
-y.psl as psl,
-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.fcsl as fcsl,
-y.hvkwrt as hvkwrt,
y.awsys,
y.awtyp,
y.blart,
y.eprctr,
y.kokrs,
y.periv,
y.pprctr,
y.prctr,
y.psegment,
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.rvcur,
y.sbusa,
y.scntr,
y.segment,
y.sfarea,
y.hbkid,
y.hktid,
y.anlkl,
y.mwskz,
y.matnr,
-- 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.werks,
y.bukrs_sender,
y.racct_sender,
y.vptnr,
--RE
y.smenr,
y.recnnr,
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,
-- Group Reporting
y.rbunit,
y.rbuptr,
y.rcomp,
y.ritclg,
y.ritem,
y.sityp,
y.subit,
-- Banking
y.fs_product_group,
y.kdgrp,
y.land1,
-- 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>
y.gebie_pa, y.kmhi01_pa, y.kmhi02_pa, y.kmhi03_pa, y.kmkdgr_pa,
y.kmland_pa, y.kmvkbu_pa, y.kmvkgr_pa, y.kmvtnr_pa, y.pstlz_pa,
y.wwort_pa, y.wwstr_pa, y.klabc_pa, y.ww1ei_pa, y.ww1zh_pa, y.ww1zi_pa,
y.ww1zm_pa, y.ww1zo_pa, y.ww1zp_pa, y.ww1zs_pa, y.ww1zv_pa,
//<$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 = ''
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'