- SAP Managed Tags
What was my target?
I searched for a table-like handling of specification data regarding other areas like the MARA where data analyzing is pretty easy. For regular administrative reportings we created a few programs. For irregular analysis we had the need to use SE16N and further examination via Excel. The standard has a few tools but didn’t fulfill our requirements.
Here is the final solution displayed in SE16N:
About the columns:
- Specification ID
- RECN(ESTRH)
- ACTN (ESTRH)
- RECN(ESTVA)
- ACTN(ESTVA)
- RECNTVH
- Value Assignment Type (VAT)
- OBJEK
- Attribute Name
- Attribute Description
- [Some technical Fields from AUSP-table]
- Value
- Source Information(AUSP for AUSP table, ESTR for ESTRAUSP Table
About the solution
There are a few reasons, why we need the CDS-Views to achieve these goals:
Why do I need 7 Views for this task?
Views doesn’t allow the use of functions in the condition of a view. Because of this fact, we need to create an own view for every table with access on processed fields like AUSP-OBJEK.
Is a View over 7 Views very slow?
No, against all intuition, the database can handle this sort of task quite performant. A full load on our test system with over 800’000 valuations tooks only 36 seconds.
What is not supported, because we don't have those cases:
Participation from 3rd Party in this case is very welcome!
Code of The CDS-Views
I searched for a table-like handling of specification data regarding other areas like the MARA where data analyzing is pretty easy. For regular administrative reportings we created a few programs. For irregular analysis we had the need to use SE16N and further examination via Excel. The standard has a few tools but didn’t fulfill our requirements.
Here is the final solution displayed in SE16N:
About the columns:- Specification ID
- RECN(ESTRH)
- ACTN (ESTRH)
- RECN(ESTVA)
- ACTN(ESTVA)
- RECNTVH
- Value Assignment Type (VAT)
- OBJEK
- Attribute Name
- Attribute Description
- [Some technical Fields from AUSP-table]
- Value
- Source Information(AUSP for AUSP table, ESTR for ESTRAUSP Table
About the solution
There are a few reasons, why we need the CDS-Views to achieve these goals:
- Classic views doesn’t allow to condense multiple values into one field. This is needed to condense RECN and ACTN of table ESTVA to access table AUSP
- classic views doesn’t allow unions, which are needed to unite the data from tables AUSP and ESTRAUSP
Why do I need 7 Views for this task?
Views doesn’t allow the use of functions in the condition of a view. Because of this fact, we need to create an own view for every table with access on processed fields like AUSP-OBJEK.
Is a View over 7 Views very slow?
No, against all intuition, the database can handle this sort of task quite performant. A full load on our test system with over 800’000 valuations tooks only 36 seconds.
What is not supported, because we don't have those cases:
- Proper handling of change numbers. The given solution returns the newest change on the Specification value
- Other types than class based VATs (Value assignment types) are not supported, e.g. compositions
- Overwritten inheritances
- Attributes with multiple values
Participation from 3rd Party in this case is very welcome!
Code of The CDS-Views
@AbapCatalog.sqlViewName: 'ZPLM_SPE_ESTRHV'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'ESTRH mit höchster ACTN'
define view Zplm_Spe_Estrh_V
as select from estrh
{
mandt,
recn,
max( actn ) as actn,
subid
}
where estrh.delflg = ' '
group by mandt,
recn,
subid@AbapCatalog.sqlViewName: 'ZPLM_SPE_ESTVA_V'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Konkatinierter Schlüssel für AUSP-Zugriff'
define view Zplm_Spe_val_estva_key_v
as select from zplm_spe_estrhv as estrh
inner join estva
on estrh.recn = estva.recnroot
and estva.delflg = ' '
{
estrh.subid as estrhsubid,
estrh.recn as estrhrecn,
estrh.actn as estrhactn,
estva.recntvh as recntvh,
estva.recn as estvarecn,
max( estva.actn ) as estvaactn,
estva.recntva_orig as estvarecntva_orig
}
group by estrh.subid,
estrh.recn,
estrh.actn,
estva.recn,
estva.recntva_orig,
estva.recntvh@AbapCatalog.sqlViewName: 'ZPLM_SPE_OBJEKV'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Aufbau Objekt Feld'
define view Zplm_Objek_Key_V
as select from zplm_spe_estva_v
{
estrhsubid,
estrhrecn,
estrhactn,
estvarecn,
estvaactn,
recntvh,
concat( estvarecn, estvaactn ) as objek
}
where zplm_spe_estva_v.estvarecntva_orig = '00000000000000000000'
//Union is obligatory to show inherited valuations
union select from zplm_spe_estva_v as estva
join zplm_spe_estva_v as estva_inh on estva_inh.estvarecn = estva.estvarecntva_orig
{
estva.estrhsubid,
estva.estrhrecn,
estva.estrhactn,
//if the valuation is inherited by other SPC, then replace the key with the original source
estva.estvarecntva_orig as estvarecn,
estva.estvaactn,
estva.recntvh,
concat( estva.estvarecntva_orig, estva_inh.estvaactn ) as objek
} where estva.estvarecntva_orig <> '00000000000000000000'@AbapCatalog.sqlViewName: 'ZPLM_AUSPV'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'View über die RD-AUSP-Tabellen'
define view Zplm_Spe_val_Auspv as select from zplm_spe_objekv as estva
inner join ausp on ausp.objek = estva.objek
and ausp.klart = '100'
{
estva.estrhsubid as estrhsubid,
estva.estrhrecn as estrhrecn,
estva.estrhactn as estrhactn,
estva.estvarecn as estvarecn,
estva.estvaactn as estvaactn,
estva.recntvh,
ausp.objek,
ausp.atinn,
max(ausp.atzhl) as atzhl,
ausp.mafid,
ausp.klart,
ausp.adzhl,
ausp.atwrt,
'AUSP' as tabname
}
where ausp.atwrt <> ' '
and ausp.lkenz = ' '
group by estva.estrhsubid,
estva.estrhrecn,
estva.estrhactn,
estva.estvarecn,
estva.estvaactn,
estva.recntvh,
ausp.objek,
ausp.mafid,
ausp.klart,
ausp.adzhl,
ausp.atinn,
ausp.atwrt
@AbapCatalog.sqlViewName: 'ZPLM_SPE_EAUSP'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'View über die RD-AUSP-Tabellen'
view Zplm_Spe_val_estrausp
as select from zplm_spe_objekv as estva
inner join estausp
on estausp.objek = estva.objek
{
estva.estrhsubid as estrhsubid,
estva.estrhrecn as estrhrecn,
estva.estrhactn as estrhactn,
estva.estvarecn as estvarecn,
estva.estvaactn as estvaactn,
estva.recntvh,
estausp.objek ,
estausp.atinn,
max( estausp.atzhl ) as atzhl,
estausp.mafid,
estausp.klart,
estausp.adzhl,
estausp.est_atwrt as atwrt,
'ESTR' as tabname
}
group by estva.estrhsubid,
estva.estrhrecn,
estva.estrhactn,
estva.estrhactn,
estva.estvarecn,
estva.estvaactn,
estva.estvaactn,
estva.recntvh,
estausp.objek,
estausp.mafid,
estausp.klart,
estausp.adzhl,
estausp.atinn,
estausp.est_atwrt
@AbapCatalog.sqlViewName: 'ZPLM_SPE_VALUE_V'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Übersicht alle ESH-Bewertungen (AUSP und ESTRAUSP)'
define view Zplm_Spe_Ausp_Total
as select from Zplm_Spe_val_Auspv{ * }
union select from Zplm_Spe_val_estrausp{ * }@AbapCatalog.sqlViewName: 'ZPLM_SPE_VAL_V'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Merkmalsbewertung zu Spezifikationen inkl. Merkmalsinfos'
define view Zplm_Spe_Val_Cabn
as select from Zplm_Spe_Ausp_Total as total
inner join cabn on total.atinn = cabn.atinn
left outer join cabnt
on total.atinn = cabnt.atinn
and cabnt.spras = 'D'
inner join estvh
on estvh.recn = total.recntvh
left outer join tcg12
on tcg12.estcat = estvh.estcat
//Put here your preferred Language in
and tcg12.langu = 'D'
{
total.estrhsubid as estrhsubid,
total.estrhrecn,
total.estrhactn,
total.estvarecn,
total.estvaactn,
total.recntvh,
estvh.estcat,
tcg12.estnam,
total.objek,
cabn.atnam,
cabnt.atbez,
total.atzhl,
total.mafid,
total.klart,
total.adzhl,
total.atwrt,
total.tabname
}
Bluesky| User | Count |
|---|---|
| 8 | |
| 6 | |
| 6 | |
| 6 | |
| 5 | |
| 4 | |
| 4 | |
| 3 | |
| 3 | |
| 3 |
