@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 ) as xopvw,
cast( ' ' as mitkz ) 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,
f.periv,
-- 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,
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>
//<$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 = ''
inner join FGL_BCF_TECH_LEDGER as l on l.rldnr = y.rldnr
or l.tech_ledger = y.rldnr
inner join finsc_ld_cmp as f on f.rldnr = l.rldnr
and f.bukrs = y.rbukrs
where ( y.rrcty = '0' or y.rrcty = '5' ) and 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 ) as xopvw,
cast( ' ' as mitkz ) as mitkz,
-- Calculated Values
'S' as koart,
a.vtkon as racct, y.ktopl,
case when reacc.vtkon = a.vtkon then y.tsl else y.hsl end as tsl,
case when reacc.vtkon = a.vtkon then y.rtcur else y.rhcur 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 t001.fikrs <> '' then coalesce( ps.bdgt_acct_cf_to, y.bdgt_account ) else y.bdgt_account end as bdgt_account,
y.bdgt_account_cocode,
f.periv,
-- 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,
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>
//<$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 = ''
inner join t001 as t001 on y.rbukrs = t001.bukrs
inner join FGL_BCF_TECH_LEDGER as l on l.rldnr = y.rldnr
or l.tech_ledger = y.rldnr
inner join finsc_ld_cmp as f on f.rldnr = l.rldnr
and f.bukrs = y.rbukrs
left outer join t856 as c on c.trtyp = y.rmvct and c.ttcfo <> ' '
left outer join FGL_BCF_REACC10 as reacc on reacc.bukrs = y.rbukrs and reacc.vtkon = a.vtkon and reacc.xsalh = ''
left outer join psm_d_glacctx as ps on ps.chrt_accts = y.ktopl and ps.gl_account = y.bdgt_account
where ( y.rrcty = '0' or y.rrcty = '5' )
and 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 ) as xopvw,
cast( ' ' as mitkz ) 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,
f.periv,
-- 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,
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>
//<$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 = ''
inner join FGL_BCF_TECH_LEDGER as l on l.rldnr = y.rldnr
or l.tech_ledger = y.rldnr
inner join finsc_ld_cmp as f on f.rldnr = l.rldnr
and f.bukrs = y.rbukrs
where ( y.rrcty = '0' or y.rrcty = '5' ) and 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'