@AbapCatalog.sqlViewName: 'FAAV_ANEP1'
@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_Anep1
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'
{ key rclnt as mandt,
key rbukrs as bukrs,
key anln1,
key anln2,
// key gjahr,
key ryear as gjahr,
key substring( subta, 2,5 ) as lnran,
key afabe,
case when ( ( adoc.slalittype between '07040' and '07050' )
or ( adoc.slalittype = '07940' ) )
then '0002'
else '0001' end as zucod,
awref as belnr,
case when adoc.mig_source = 'A'
then substring(adoc.awitgrp,4,3)
else substring(adoc.awitem,4,3) end as buzei,
bzdat,
anbwa as bwasl,
slalittype,
movcat,
case when adoc.mig_source = 'A' then substring( subta_rev, 2,5 )
when adoc.vorgn = 'OCAB' or adoc.vorgn = 'OCZU' then '99999' // 2346736
else substring( subta_rev, 2,5 ) end as lnsan,
case when subta_rev > '000000' then substring( subta_rev, 2,5 ) // 2475656
when (( xsettled = 'X' or xsettling = 'X') and prec_awref = '' )
and subta_rev = '000000' then '99999'
when (( xsettled = '' and xsettling = '' )
and ( xreversed = '' and xreversing = '' )
or ( xreversing = 'X' and subta_rev = '000000' ) -- Note 2549604
or ( xreversed = 'X' and subta_rev = '000000' )) then '00000'
else '99999' end as augln, // note 2257760 - 2497351
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 ( ( slalittype = '07000' or slalittype = '07020' or slalittype = '07040' or
slalittype = '07001' or slalittype = '07021' or slalittype = '07041' or
slalittype = '07002' or slalittype = '07022' or slalittype = '07042' )
or ( ( slalittype = '07003' or slalittype = '07023' or slalittype = '07043' or
slalittype = '07004' or slalittype = '07024' or slalittype = '07044' ) // note 2459507 - 2497351
and ( movcat = '11' or movcat = '12' or movcat = '13' or
movcat = '14' or movcat = '15' or movcat = '61' or
movcat = '65' ) )
or ( ( slalittype = '07025' or slalittype = '07045' or slalittype = '07026' or
slalittype = '07046' or slalittype = '07027' or slalittype = '07047' )
and ( movcat = '46' or movcat = '11' or movcat = '12' or
movcat = '13' or movcat = '14' or // note 2420878
movcat = '56' or movcat = '59' ) ) // note 2446873
or ( ( slalittype = '07028' or slalittype = '07048' )
and ( movcat = '11' or movcat = '12' or movcat = '13' or
movcat = '14' or movcat = '15' or movcat = '46' or
movcat = '56' or movcat = '59' ) ) // note 2446873
-- or ( ( slalittype = '07006' or slalittype = '07007' )
-- and ( movcat = '46' or movcat = '56' or movcat = '59' ) )
or ( ( slalittype = '07005' or slalittype = '07006' or slalittype = '07007' or
slalittype = '07008' )
and ( movcat = '11' or movcat = '12' or movcat = '13' or
movcat = '14' or movcat = '15' or movcat = '46' or
movcat = '56' or movcat = '59' ) ) ) // 2497351
and not ( movcat = 'C7' or movcat = 'E7') // ( keine unterjährige Altdatenübernahme ) note 2212511 // 2349016
group by rclnt,
rbukrs,
anln1,
anln2,
// gjahr,
ryear,
afabe,
subta,
awref,
awitgrp,
awitem,
mig_source,
bzdat,
anbwa,
slalittype,
movcat,
vorgn,
subta_rev,
prec_awref,
xsettled,
xsettling,
xreversed,
xreversing
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'
{ key rclnt as mandt,
key rbukrs as bukrs,
key anlgr as anln1,
key anlgr2 as anln2,
// key gjahr,
key ryear as gjahr,
key substring( subta, 2,5 ) as lnran,
key afabe,
case when ( ( adoc.slalittype between '07040' and '07050' )
or ( adoc.slalittype = '07940' ) )
then '0002'
else '0001' end as zucod,
awref as belnr,
case when adoc.mig_source = 'A'
then substring(adoc.awitgrp,4,3)
else substring(adoc.awitem,4,3) end as buzei,
bzdat,
anbwa as bwasl,
slalittype,
movcat,
case when adoc.mig_source = 'A' then substring( subta_rev, 2,5 )
when adoc.vorgn = 'OCAB' or adoc.vorgn = 'OCZU' then '99999' // 2346736
else substring( subta_rev, 2,5 ) end as lnsan,
case when subta_rev > '000000' then substring( subta_rev, 2,5 ) // 2475656
when (( xsettled = 'X' or xsettling = 'X') and prec_awref = '' )
and subta_rev = '000000' then '99999'
when (( xsettled = '' and xsettling = '' )
and ( xreversed = '' and xreversing = '' )
or ( xreversing = 'X' and subta_rev = '000000' ) // Note 2549604
or ( xreversed = 'X' and subta_rev = '000000' )) then '00000'
else '99999' end as augln, // note 2257760 - 2497351
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 ( ( slalittype = '07000' or slalittype = '07020' or slalittype = '07040' or
slalittype = '07001' or slalittype = '07021' or slalittype = '07041' or
slalittype = '07002' or slalittype = '07022' or slalittype = '07042' )
or ( ( slalittype = '07003' or slalittype = '07023' or slalittype = '07043' or
slalittype = '07004' or slalittype = '07024' or slalittype = '07044' ) // note 2459507 - 2497351
and ( movcat = '11' or movcat = '12' or movcat = '13' or
movcat = '14' or movcat = '15' or movcat = '61' or
movcat = '65' ) )
or ( ( slalittype = '07025' or slalittype = '07045' or slalittype = '07026' or
slalittype = '07046' or slalittype = '07027' or slalittype = '07047' )
and ( movcat = '46' or movcat = '11' or movcat = '12' or
movcat = '13' or movcat = '14' or // note 2420878
movcat = '56' or movcat = '59' ) ) // note 2446873
or ( ( slalittype = '07028' or slalittype = '07048' )
and ( movcat = '11' or movcat = '12' or movcat = '13' or
movcat = '14' or movcat = '15' or movcat = '46' or
movcat = '56' or movcat = '59' ) ) // note 2446873
-- or ( ( slalittype = '07006' or slalittype = '07007' )
-- and ( movcat = '46' or movcat = '56' or movcat = '59' ) )
or ( ( slalittype = '07005' or slalittype = '07006' or slalittype = '07007' or
slalittype = '07008' )
and ( movcat = '11' or movcat = '12' or movcat = '13' or
movcat = '14' or movcat = '15' or movcat = '46' or
movcat = '56' or movcat = '59' ) ) ) // 2497351
and not ( movcat = 'C7' or movcat = 'E7') // ( keine unterjährige Altdatenübernahme ) note 2212511 // 2349016
group by rclnt,
rbukrs,
anlgr,
anlgr2,
// gjahr,
ryear,
afabe,
subta,
awref,
awitgrp,
awitem,
mig_source,
bzdat,
anbwa,
slalittype,
movcat,
vorgn,
subta_rev,
prec_awref,
xsettled,
xsettling,
xreversed,
xreversing
union all
select from faat_doc_it
{ key mandt,
key bukrs,
key anln1,
key anln2,
key gjahr,
key substring( subta, 2,5 ) as lnran,
key afabe,
case when ( ( slalittype between '07040' and '07050' )
or ( slalittype = '07940' ) )
then '0002'
else '0001' end as zucod,
awref as belnr,
substring(awitem,4,3) as buzei,
bzdat,
bwasl,
slalittype,
movcat,
case when mig_source = 'A' then substring( subta_rev, 2,5 )
when vorgn = 'OCAB' or vorgn = 'OCZU' then '99999' // 2346736
else substring( subta_rev, 2,5 ) end as lnsan,
case when subta_rev > '000000' then substring( subta_rev, 2,5 ) // 2475656
when (( xsettled = 'X' or xsettling = 'X') and prec_awref = '' )
and subta_rev = '000000' then '99999'
when (( xsettled = '' and xsettling = '' )
and ( xreversed = '' and xreversing = '' )
or ( xreversing = 'X' and subta_rev = '000000' ) // Note 2549604
or ( xreversed = 'X' and subta_rev = '000000' )) then '00000'
else '99999' end as augln, // note 2257760 - 2497351
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 ( ( slalittype = '07000' or slalittype = '07020' or slalittype = '07040' or
slalittype = '07001' or slalittype = '07021' or slalittype = '07041' or
slalittype = '07002' or slalittype = '07022' or slalittype = '07042' )
or ( ( slalittype = '07003' or slalittype = '07023' or slalittype = '07043' or
slalittype = '07004' or slalittype = '07024' or slalittype = '07044' ) // note 2459507 - 2497351
and ( movcat = '11' or movcat = '12' or movcat = '13' or
movcat = '14' or movcat = '15' or movcat = '61' or
movcat = '65' ) )
or ( ( slalittype = '07025' or slalittype = '07045' or slalittype = '07026' or
slalittype = '07046' or slalittype = '07027' or slalittype = '07047' )
and ( movcat = '46' or movcat = '11' or movcat = '12' or
movcat = '13' or movcat = '14' or // note 2420878
movcat = '56' or movcat = '59' ) ) // note 2446873
or ( ( slalittype = '07028' or slalittype = '07048' )
and ( movcat = '11' or movcat = '12' or movcat = '13' or
movcat = '14' or movcat = '15' or movcat = '46' or
movcat = '56' or movcat = '59' ) ) // note 2446873
-- or ( ( slalittype = '07006' or slalittype = '07007' )
-- and ( movcat = '46' or movcat = '56' or movcat = '59' ) )
or ( ( slalittype = '07005' or slalittype = '07006' or slalittype = '07007' or
slalittype = '07008' )
and ( movcat = '11' or movcat = '12' or movcat = '13' or
movcat = '14' or movcat = '15' or movcat = '46' or
movcat = '56' or movcat = '59' ) ) // 2497351
or ( ( slalittype = '07900' or slalittype = '07940' )
and ( movcat = 'B8' or movcat = '12' ) )
or ( ( slalittype = '07901' )
and ( movcat = 'A8' or movcat = '12' ) ) )
and not ( movcat = 'C7' or movcat = 'E7') // ( keine unterjährige Altdatenübernahme ) note 2212511 // 2349016
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,
awref,
awitem,
mig_source,
bzdat,
bwasl,
slalittype,
movcat,
vorgn,
subta_rev,
prec_awref,
xsettled,
xsettling,
xreversed,
xreversing
union all
// the same für group assets
----------------------------
select from faat_doc_it
{ key mandt,
key bukrs,
key anlgr as anln1,
key anlgr2 as anln2,
key gjahr,
key substring( subta, 2,5 ) as lnran,
key afabe,
case when ( ( slalittype between '07040' and '07050' )
or ( slalittype = '07940' ) )
then '0002'
else '0001' end as zucod,
awref as belnr,
substring(awitem,4,3) as buzei,
bzdat,
bwasl,
slalittype,
movcat,
case when mig_source = 'A' then substring( subta_rev, 2,5 )
when vorgn = 'OCAB' or vorgn = 'OCZU' then '99999' // 2346736
else substring( subta_rev, 2,5 ) end as lnsan,
case when subta_rev > '000000' then substring( subta_rev, 2,5 ) // 2475656
when (( xsettled = 'X' or xsettling = 'X') and prec_awref = '' )
and subta_rev = '000000' then '99999'
when (( xsettled = '' and xsettling = '' )
and ( xreversed = '' and xreversing = '' )
or ( xreversing = 'X' and subta_rev = '000000' ) // Note 2549604
or ( xreversed = 'X' and subta_rev = '000000' )) then '00000'
else '99999' end as augln, // note 2257760 - 2497351
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 ( ( slalittype = '07000' or slalittype = '07020' or slalittype = '07040' or
slalittype = '07001' or slalittype = '07021' or slalittype = '07041' or
slalittype = '07002' or slalittype = '07022' or slalittype = '07042' )
or ( ( slalittype = '07003' or slalittype = '07023' or slalittype = '07043' or
slalittype = '07004' or slalittype = '07024' or slalittype = '07044' ) // note 2459507 - 2497351
and ( movcat = '11' or movcat = '12' or movcat = '13' or
movcat = '14' or movcat = '15' or movcat = '61' or
movcat = '65' ) )
or ( ( slalittype = '07025' or slalittype = '07045' or slalittype = '07026' or
slalittype = '07046' or slalittype = '07027' or slalittype = '07047' )
and ( movcat = '46' or movcat = '11' or movcat = '12' or
movcat = '13' or movcat = '14' or // note 2420878
movcat = '56' or movcat = '59' ) ) // note 2446873
or ( ( slalittype = '07028' or slalittype = '07048' )
and ( movcat = '11' or movcat = '12' or movcat = '13' or
movcat = '14' or movcat = '15' or movcat = '46' or
movcat = '56' or movcat = '59' ) ) // note 2446873
-- or ( ( slalittype = '07006' or slalittype = '07007' )
-- and ( movcat = '46' or movcat = '56' or movcat = '59' ) )
or ( ( slalittype = '07005' or slalittype = '07006' or slalittype = '07007' or
slalittype = '07008' )
and ( movcat = '11' or movcat = '12' or movcat = '13' or
movcat = '14' or movcat = '15' or movcat = '46' or
movcat = '56' or movcat = '59' ) ) // 2497351
or ( ( slalittype = '07900' or slalittype = '07940' )
and ( movcat = 'B8' or movcat = '12' ) )
or ( ( slalittype = '07901' )
and ( movcat = 'A8' or movcat = '12' ) ) )
and not ( movcat = 'C7' or movcat = 'E7') // ( keine unterjährige Altdatenübernahme ) note 2212511 // 2349016
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,
awref,
awitem,
mig_source,
bzdat,
bwasl,
slalittype,
movcat,
vorgn,
subta_rev,
prec_awref,
xsettled,
xsettling,
xreversed,
xreversing
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"FAA_REP_LEDGER",
"ACDOCA",
"FAAT_DOC_IT"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/