@AbapCatalog.sqlViewName: 'FAAV_ANEA1'
@EndUserText.label: 'Intern: Verdichten auf SLALITTYPE-Ebene'
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #P
// this view is just to condense the data on slalittype level
-------------------------------------------------------------
define view Faa_Anea1
as select from Faa_Rep_Ledger as ldnr
inner join acdoca as adoc
on ldnr.bukrs = adoc.rbukrs
and ( ( ldnr.rep_ledger = adoc.rldnr
and ldnr.lead_afabe = adoc.afabe )
or ( ldnr.tech_ledger = adoc.rldnr
and ldnr.lead_afabe = adoc.afabe ) )
and ldnr.xstore = 'X'
{ rclnt as mandt,
rbukrs as bukrs,
anln1,
anln2,
ryear as gjahr,
substring( subta, 2, 5 ) as lnran,
afabe,
case when ( ( adoc.slalittype between '07040' and '07050' )
or ( adoc.slalittype = '07940' ) )
then '0002'
else '0001' end as zucod,
slalittype,
movcat,
sum ( hsl ) as hsl,
sum ( ksl ) as ksl,
sum ( osl ) as osl,
sum ( vsl ) as vsl,
sum ( bsl ) as bsl,
sum ( csl ) as csl,
sum ( dsl ) as dsl,
sum ( esl ) as esl,
sum ( fsl ) as fsl,
sum ( gsl ) as gsl }
where anln1 <> ''
and awtyp <> 'AMDP'
and bstat <> 'C' // only current year values
and ( mig_source = 'A' or mig_source = '' )
and not ( movcat = 'C7' or movcat = 'E7' ) // 2349016 w/o cum values from ldt
and ( slalittype = '07002' or slalittype = '07003' or slalittype = '07004' or slalittype = '07005' or
slalittype = '07006' or slalittype = '07007' or slalittype = '07008' or
slalittype = '07022' or slalittype = '07023' or slalittype = '07024' or slalittype = '07025' or // 2937093
slalittype = '07026' or slalittype = '07027' or slalittype = '07028' or
slalittype = '07042' or slalittype = '07043' or slalittype = '07044' or slalittype = '07045' or
slalittype = '07046' or slalittype = '07047' or slalittype = '07048' )
group by rclnt,
rbukrs,
anln1,
anln2,
ryear,
afabe,
subta,
slalittype,
movcat
union all
// the same for group assets
----------------------------
select from Faa_Rep_Ledger as ldnr
inner join acdoca as adoc
on ldnr.bukrs = adoc.rbukrs
and ( ( ldnr.rep_ledger = adoc.rldnr
and ldnr.lead_afabe = adoc.afabe )
or ( ldnr.tech_ledger = adoc.rldnr
and ldnr.lead_afabe = adoc.afabe ) )
and ldnr.xstore = 'X'
{ rclnt as mandt,
rbukrs as bukrs,
anlgr as anln1,
anlgr2 as anln2,
ryear as gjahr,
substring( subta, 2, 5 ) as lnran,
afabe,
case when ( ( adoc.slalittype between '07040' and '07050' )
or ( adoc.slalittype = '07940' ) )
then '0002'
else '0001' end as zucod,
slalittype,
movcat,
sum ( hsl ) as hsl,
sum ( ksl ) as ksl,
sum ( osl ) as osl,
sum ( vsl ) as vsl,
sum ( bsl ) as bsl,
sum ( csl ) as csl,
sum ( dsl ) as dsl,
sum ( esl ) as esl,
sum ( fsl ) as fsl,
sum ( gsl ) as gsl }
where anlgr <> ''
and ( ( adoc.anlgr <> adoc.anln1 )
or ( adoc.anlgr = adoc.anln1 and adoc.anlgr2 <> adoc.anln2 ) )
and awtyp <> 'AMDP'
and bstat <> 'C' // only current year values
and ( mig_source = 'A' or mig_source = '' )
and not ( movcat = 'C7' or movcat = 'E7' ) // 2349016 w/o cum values from ldt
and ( slalittype = '07002' or slalittype = '07003' or slalittype = '07004' or slalittype = '07005' or
slalittype = '07006' or slalittype = '07007' or slalittype = '07008' or
slalittype = '07022' or slalittype = '07023' or slalittype = '07024' or slalittype = '07025' or // 2937093
slalittype = '07026' or slalittype = '07027' or slalittype = '07028' or
slalittype = '07042' or slalittype = '07043' or slalittype = '07044' or slalittype = '07045' or
slalittype = '07046' or slalittype = '07047' or slalittype = '07048' )
group by rclnt,
rbukrs,
anlgr,
anlgr2,
ryear,
afabe,
subta,
slalittype,
movcat
union all
select from faat_doc_it
{ mandt,
bukrs,
anln1,
anln2,
gjahr,
substring( subta, 2, 5 ) as lnran,
afabe,
case when ( ( slalittype between '07040' and '07050' )
or ( slalittype = '07940' ) )
then '0002'
else '0001' end as zucod,
slalittype,
movcat,
sum ( hsl ) as hsl,
sum ( ksl ) as ksl,
sum ( osl ) as osl,
sum ( vsl ) as vsl,
sum ( bsl ) as bsl,
sum ( csl ) as csl,
sum ( dsl ) as dsl,
sum ( esl ) as esl,
sum ( fsl ) as fsl,
sum ( gsl ) as gsl }
where anln1 <> ''
and awtyp <> 'AMDP'
and bwasl <> '999' // w/o bcf
and movcat <> '00' // w/o bcf
and not ( movcat = 'C7' or movcat = 'E7' ) // 2349016 w/o cum values from ldt
and ( slalittype = '07002' or slalittype = '07003' or slalittype = '07004' or slalittype = '07005' or
slalittype = '07006' or slalittype = '07007' or slalittype = '07008' or
slalittype = '07022' or slalittype = '07023' or slalittype = '07024' or slalittype = '07025' or // 2937093
slalittype = '07026' or slalittype = '07027' or slalittype = '07028' or
slalittype = '07042' or slalittype = '07043' or slalittype = '07044' or slalittype = '07045' or
slalittype = '07046' or slalittype = '07047' or slalittype = '07048' or
slalittype = '07010' or slalittype = '07050' or slalittype = '07900' or slalittype = '07901' or
slalittype = '07940' )
and sdm_status <> '18'
and sdm_status <> '19'
and sdm_status <> '1A' // FAAT_DOC_IT entry is comletely migrated to ACDOCA
group by mandt,
bukrs,
anln1,
anln2,
gjahr,
afabe,
subta,
slalittype,
movcat
union all
// the same for group assets
----------------------------
select from faat_doc_it
{ mandt,
bukrs,
anlgr as anln1,
anlgr2 as anln2,
gjahr,
substring( subta, 2, 5 ) as lnran,
afabe,
case when ( ( slalittype between '07040' and '07050' )
or ( slalittype = '07940' ) )
then '0002'
else '0001' end as zucod,
slalittype,
movcat,
sum ( hsl ) as hsl,
sum ( ksl ) as ksl,
sum ( osl ) as osl,
sum ( vsl ) as vsl,
sum ( bsl ) as bsl,
sum ( csl ) as csl,
sum ( dsl ) as dsl,
sum ( esl ) as esl,
sum ( fsl ) as fsl,
sum ( gsl ) as gsl }
where anlgr <> ''
and ( ( anlgr <> anln1 )
or ( anlgr = anln1 and anlgr2 <> anln2 ) )
and awtyp <> 'AMDP'
and bwasl <> '999' // w/o bcf
and movcat <> '00' // w/o bcf
and not ( movcat = 'C7' or movcat = 'E7' ) // 2349016 w/o cum values from ldt
and ( slalittype = '07002' or slalittype = '07003' or slalittype = '07004' or slalittype = '07005' or
slalittype = '07006' or slalittype = '07007' or slalittype = '07008' or
slalittype = '07022' or slalittype = '07023' or slalittype = '07024' or slalittype = '07025' or // 2937093
slalittype = '07026' or slalittype = '07027' or slalittype = '07028' or
slalittype = '07042' or slalittype = '07043' or slalittype = '07044' or slalittype = '07045' or
slalittype = '07046' or slalittype = '07047' or slalittype = '07048' or
slalittype = '07010' or slalittype = '07050' or slalittype = '07900' or slalittype = '07901' or
slalittype = '07940' )
and sdm_status <> '18'
and sdm_status <> '19'
and sdm_status <> '1A' // FAAT_DOC_IT entry is comletely migrated to ACDOCA
group by mandt,
bukrs,
anlgr,
anlgr2,
gjahr,
afabe,
subta,
slalittype,
movcat
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"FAA_REP_LEDGER",
"ACDOCA",
"FAAT_DOC_IT"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/