Skip to Content

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                                         
          }

 

 

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply