CDS Views: Performant Status Query using the JEST Table

 The application and the system status in SAP play a very big role in many reports, search helps and evaluations. 

The conventional approach to retrieve the JEST status is to use the dedicated function module STATUS_READ or STATUS_READ_MULTI from the function group BSVA.

If you need the application or the user status just in order to exclude certain objects from a result list or a search help, it is more comfortable to use dictionary objects, such as CDS Views.

In this post I am going to demonstrate how to classify QM notifications into a group of valid and invalid. The invalid notifications include the status values 'completed' and 'cancelled' and have to be filtered out. All the other notifications are valid and have to be shown.

This can be achieved with the help of two CDS Views. 

The main CDS View contains the join with the JEST Table and casts priorities to the status values in the where clause. It is split into two, because we want to have a 'valid' or 'invalid' for every notification.

Here goes the code for the first CDS View:

@AbapCatalog.sqlViewName: 'zcds_notif_main'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Main View JEST, QMEL'
define view Z_CDS_NOTIF_MAIN as select distinct from qmel as notif
association [1] to jest as _jest on notif.objnr = _jest.objnr{
    _jest.objnr,
    _jest.stat,
    notif.qmnum,
    cast('invalid' as abap.char( 10 )) as status,
    cast('5' as abap.int1) as prio
} where _jest.stat = 'E0016' and  _jest.stat like 'E%' and _jest.inact = ''
// or _jest.stat = 'E0012' //completed or cancelled, maybe join with table tj30, this is just a demo!
union select distinct from qmel as notif
association [1] to jest as _jest on notif.objnr = _jest.objnr{
    _jest.objnr,
    _jest.stat,
    notif.qmnum,
    cast(' valid' as abap.char( 10 )) as status,
    cast('4' as abap.int1) as prio
} where _jest.stat != 'E0016' and  _jest.stat like 'E%' and _jest.inact = ''
//and _jest.stat != 'E0012'

The second CDS View accumulates the values of the first view, here we have one entry per QM notification, according to priority.

Here goes the code for the second CDS View:

@AbapCatalog.sqlViewName: 'ZDS_NOTIF_STATUS'

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'QMEL mit Status'

define view Z_CDS_NOTIF_STATUS as select from qmel  as NOTIF inner join zcds_notif_main as MAIN

on NOTIF.qmnum = MAIN.qmnum {

 key   NOTIF.qmnum,

 max(MAIN.status) as status,

 max(prio) as stat

} group by NOTIF.qmnum

You could create an auxiliary view on the JEST Table, using only the relevant status schema. Another improvement would be to push the 'valid / invalid' logics to a customizng table.

Have fun while trying, adjustung and improving the approach!