CDS_SUI_TM_CATALOG

@AbapCatalog.sqlViewName: 'SUI_TM_APP_CAT_V'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Catalogs derived from SUI_TM_MM_APP'
define view CDS_SUI_TM_CATALOG 
( id, type, langu, title, changed_at, changed_by,
  author, created_on, devclass, is_adapted )
as select distinct from sui_tm_mm_cat 
          left outer join sui_tm_mm_catt on sui_tm_mm_cat.id = sui_tm_mm_catt.id   
                                         and  sui_tm_mm_catt.langu = $session.system_language  //'E'  

          left outer join tadir on  tadir.pgmid  = 'R3TR'      
                                and tadir.object = 'UIAC'      
                                and tadir.obj_name = sui_tm_mm_cat.id 
                                
          left outer join ( sui_tm_mm_app inner join sui_uiaa on sui_tm_mm_app.app_id = sui_uiaa.app_id )        
          on sui_tm_mm_app.cat_id = sui_tm_mm_cat.id                                                 
{ key sui_tm_mm_cat.id,
  sui_tm_mm_cat.type,
  sui_tm_mm_catt.langu,
  sui_tm_mm_catt.title,
  tstmp_to_dats( sui_tm_mm_cat.changed_at, abap_system_timezone( $session.client,'NULL' ), $session.client,'NULL' ) as changed_at,
//  cast( sui_tm_mm_cat.changed_at as timestamp ) as changed_at,

  cast( sui_tm_mm_cat.changed_by as responsibl) as changed_by, 
  tadir.author,
  tadir.created_on,
  tadir.devclass,
  cast( case when sui_uiaa.app_id is not null then 'X' else ' ' end as sui_uiad_is_adapted ) as is_adapted
}
 
 union select distinct from sui_tm_mm_app 

   left outer join sui_tm_mm_cat on sui_tm_mm_app.cat_id = sui_tm_mm_cat.id       
                                          
   left outer join sui_tm_mm_catt on sui_tm_mm_cat.id     = sui_tm_mm_catt.id   
                                  and sui_tm_mm_catt.langu = $session.system_language  //'E' 

   
   left outer join sui_tm_mm_app as app on
        sui_tm_mm_app.cat_id = app.cat_id and
        sui_tm_mm_app.changed_at > app.changed_at
                               
   left outer join tadir on tadir.pgmid    = 'R3TR'      
                         and tadir.object   = 'UIAC'      
                         and tadir.obj_name = sui_tm_mm_app.cat_id   
                         
   left outer join ( sui_tm_mm_app as app2 inner join sui_uiaa on app2.app_id = sui_uiaa.app_id )        
          on app2.cat_id = sui_tm_mm_app.cat_id                       
                                
{ key sui_tm_mm_app.cat_id,
//  case when sui_tm_mm_cat.type is not null then sui_tm_mm_cat.type else '<>' end,

  sui_tm_mm_cat.type,
  sui_tm_mm_catt.langu,
  sui_tm_mm_catt.title,
//  sui_tm_mm_app.cat_descr,

  tstmp_to_dats( sui_tm_mm_cat.changed_at, abap_system_timezone( $session.client,'NULL' ), $session.client,'NULL' ) as changed_at,
//  sui_tm_mm_app.changed_at,

  sui_tm_mm_app.changed_by,
  tadir.author,
  tadir.created_on, 
  tadir.devclass,  
  cast( case when sui_uiaa.app_id is not null then 'X' else ' ' end as sui_uiad_is_adapted ) as is_adapted
}
where 
type is null and 
app.changed_at is null