Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member323955
Discoverer
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:

  • 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
}

 

 
Labels in this area