@AbapCatalog.sqlViewName: 'FAAV_REC_ANLC'
@EndUserText.label: 'Reconciliation ANLC (Base View)'
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #P
define view FAA_REC_ANLC_BASE
as select from Faa_1st_Open_Fy as open
// select only close fy and the first open fy
---------------------------------------------
inner join anlc as anlc
on anlc.bukrs = open.bukrs
and anlc.afabe = open.afabe
and anlc.gjahr <= open.fyear
inner join t093c as t093c
on t093c.bukrs = anlc.bukrs
inner join t093 as t093
on t093.afapl = t093c.afapl
and t093.afaber = anlc.afabe
inner join anlb as anlb -- note 2226771
on anlb.bukrs = anlc.bukrs
and anlb.anln1 = anlc.anln1
and anlb.anln2 = anlc.anln2
and anlb.afabe = anlc.afabe
{ anlc.mandt as mandt,
anlc.bukrs as bukrs,
anlc.anln1 as anln1,
anlc.anln2 as anln2,
anlc.gjahr as gjahr,
anlc.afabe as afabe,
--CUM Values
kansw as kansw,
kaufw as KAUFW,
kinvz as KINVZ,
knafa as KNAFA,
ksafa as KSAFA,
kaafa as KAAFA,
kmafa as KMAFA,
kzinw as KZINW,
kaufn as KAUFN,
kanza as KANZA,
ksans as KSANS,
// --PLAN Values
// aufwp as AUFWP, --note 2131822
// nafap as NAFAP, --note 2131822
// safap as SAFAP, --note 2131822
// aafap as AAFAP, --note 2131822
// mafap as MAFAP, --note 2131822
// zinsp as ZINSP, --note 2131822
// aufnp as AUFNP, --note 2131822
--POSTED Values --note 2179452
case when t093.xstore = 'X'
and t093.buhbkt <> '0'
then aufwb
else 0 end as aufwb,
case when t093.xstore = 'X'
and t093.buhbkt <> '0'
then nafag
else 0 end as nafag,
case when t093.xstore = 'X'
and t093.buhbkt <> '0'
then safag
else 0 end as safag,
case when t093.xstore = 'X'
and t093.buhbkt <> '0'
then aafag
else 0 end as aafag,
case when t093.xstore = 'X'
and t093.buhbkt <> '0'
then mafag
else 0 end as mafag,
case when t093.xstore = 'X'
and t093.buhbkt <> '0'
then zinsg
else 0 end as zinsg,
case when t093.xstore = 'X'
and t093.buhbkt <> '0'
then aufng
else 0 end as aufng,
--Current Year Values
answl as ANSWL,
ansaz as ANSAZ,
aufwm as AUFWM,
invzm as INVZM,
nafam as NAFAM,
safam as SAFAM,
aafam as AAFAM,
mafam as MAFAM,
zinsm as ZINSM,
aufnm as AUFNM,
sansl as SANSL,
--Writeup
zusna as ZUSNA,
zussa as ZUSSA,
zusaa as ZUSAA,
zusma as ZUSMA,
--prop.vals - PY
case when anlb.anlgr <> '' -- note 2226771
then 0
else aufwv end as aufwv,
case when anlb.anlgr <> '' -- note 2226771
then 0
else invzv end as invzv,
case when anlb.anlgr <> '' -- note 2226771
then 0
else nafav end as nafav,
case when anlb.anlgr <> '' -- note 2226771
then 0
else safav end as safav,
case when anlb.anlgr <> '' -- note 2226771
then 0
else aafav end as aafav,
case when anlb.anlgr <> '' -- note 2226771
then 0
else mafav end as mafav,
case when anlb.anlgr <> '' -- note 2226771
then 0
else aufnv end as aufnv,
--prop.vals - PY
case when anlb.anlgr <> '' -- note 2226771
then 0
else aufwl end as aufwl,
case when anlb.anlgr <> '' -- note 2226771
then 0
else invzl end as invzl,
case when anlb.anlgr <> '' -- note 2226771
then 0
else nafal end as nafal,
case when anlb.anlgr <> '' -- note 2226771
then 0
else safal end as safal,
case when anlb.anlgr <> '' -- note 2226771
then 0
else aafal end as aafal,
case when anlb.anlgr <> '' -- note 2226771
then 0
else mafal end as mafal,
case when anlb.anlgr <> '' -- note 2226771
then 0
else aufnl end as aufnl }
union all
select from Faa_1st_Open_Fy as open
// select only close fy and the first open fy
---------------------------------------------
inner join faav_anlc as anlc
on anlc.bukrs = open.bukrs
and anlc.afabe = open.afabe
and anlc.gjahr <= open.fyear
inner join t093c as t093c
on t093c.bukrs = anlc.bukrs
inner join t093 as t093
on t093.afapl = t093c.afapl
and t093.afaber = anlc.afabe
inner join anlb as anlb -- note 2226771
on anlb.bukrs = anlc.bukrs
and anlb.anln1 = anlc.anln1
and anlb.anln2 = anlc.anln2
and anlb.afabe = anlc.afabe
{ anlc.mandt as mandt,
anlc.bukrs as bukrs,
anlc.anln1 as anln1,
anlc.anln2 as anln2,
anlc.gjahr as gjahr,
anlc.afabe as afabe,
--CUM Values
- kansw as kansw,
- kaufw as KAUFW,
- kinvz as KINVZ,
- knafa as KNAFA,
- ksafa as KSAFA,
- kaafa as KAAFA,
- kmafa as KMAFA,
- kzinw as KZINW,
- kaufn as KAUFN,
- kanza as KANZA,
- ksans as KSANS,
// --PLAN Values
// - aufwp as AUFWP, --note 2131822
// - nafap as NAFAP, --note 2131822
// - safap as SAFAP, --note 2131822
// - aafap as AAFAP, --note 2131822
// - mafap as MAFAP, --note 2131822
// - zinsp as ZINSP, --note 2131822
// - aufnp as AUFNP, --note 2131822
--POSTED Values --note 2179452
case when t093.xstore = 'X'
and t093.buhbkt <> '0'
then - aufwb
else 0 end as aufwb,
case when t093.xstore = 'X'
and t093.buhbkt <> '0'
then - nafag
else 0 end as nafag,
case when t093.xstore = 'X'
and t093.buhbkt <> '0'
then - safag
else 0 end as safag,
case when t093.xstore = 'X'
and t093.buhbkt <> '0'
then - aafag
else 0 end as aafag,
case when t093.xstore = 'X'
and t093.buhbkt <> '0'
then - mafag
else 0 end as mafag,
case when t093.xstore = 'X'
and t093.buhbkt <> '0'
then - zinsg
else 0 end as zinsg,
case when t093.xstore = 'X'
and t093.buhbkt <> '0'
then - aufng
else 0 end as aufng,
--Current Year Values
- answl as ANSWL,
- ansaz as ANSAZ,
- aufwm as AUFWM,
- invzm as INVZM,
- nafam as NAFAM,
- safam as SAFAM,
- aafam as AAFAM,
- mafam as MAFAM,
- zinsm as ZINSM,
- aufnm as AUFNM,
- sansl as SANSL,
--Writeup
- zusna as ZUSNA,
- zussa as ZUSSA,
- zusaa as ZUSAA,
- zusma as ZUSMA,
--prop.vals - PY
case when anlb.anlgr <> '' -- note 2226771
then 0
else - aufwv end as aufwv,
case when anlb.anlgr <> '' -- note 2226771
then 0
else - invzv end as invzv,
case when anlb.anlgr <> '' -- note 2226771
then 0
else - nafav end as nafav,
case when anlb.anlgr <> '' -- note 2226771
then 0
else - safav end as safav,
case when anlb.anlgr <> '' -- note 2226771
then 0
else - aafav end as aafav,
case when anlb.anlgr <> '' -- note 2226771
then 0
else - mafav end as mafav,
case when anlb.anlgr <> '' -- note 2226771
then 0
else - aufnv end as aufnv,
--prop.vals - PY
case when anlb.anlgr <> '' -- note 2226771
then 0
else - aufwl end as aufwl,
case when anlb.anlgr <> '' -- note 2226771
then 0
else - invzl end as invzl,
case when anlb.anlgr <> '' -- note 2226771
then 0
else - nafal end as nafal,
case when anlb.anlgr <> '' -- note 2226771
then 0
else - safal end as safal,
case when anlb.anlgr <> '' -- note 2226771
then 0
else - aafal end as aafal,
case when anlb.anlgr <> '' -- note 2226771
then 0
else - mafal end as mafal,
case when anlb.anlgr <> '' -- note 2226771
then 0
else - aufnl end as aufnl }
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"FAA_1ST_OPEN_FY",
"ANLB",
"ANLC",
"T093",
"T093C",
"FAAV_ANLC"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/