A4H Use Case – Creating CDS view to get System status and User Status Texts for bulk of Objects

Introduction:

In this blog post you will learn a use-case on concatenation of column values from multiple records and bring the data into single record in ABAP CDS views.

Overview

At some point in time we would have used the Function Module STATUS_TEXT_EDIT to fetch System Statuses and User status for any OBJNR.

Consider the below output from FM STATUS_TEXT_EDIT.

Lets say for reporting purposes we need to get system status and user status for many objects: Service Orders, Equipments, Notifications etc.

Let us consider building the logic for the below table-

Tables involved:
JEST – Individual Object Status
JSTO – Status object information
TJ30T- Texts for User Status
TJ02T – System status texts

Pseudo Code:

1. Pass OBJNR to JEST to get STAT where INACT <> ‘X’
2. Pass OBJNR to JSTO to get STSMA
3. Pass JEST-STAT and JSTO-STSMA to TJ30T and SPRAS = english to get User status TXT04
4. Pass JEST-STAT and SPRAS = english to TJ02T to get System status TXT04
5. Concatenate all the system status and user status to get one result per Object

Steps Involved:

1. Create a CDS table function.

@ClientDependent: false
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Static'
define table function ZG_CDS_TF_TEXT 
returns {
    objnr :J_OBJNR;
    status_profile :j_stsma;
    system_status :string40;
    user_status :string40;
}
implemented by method zg_cl_st_text=>get_status_text;

2. Create AMDP method for the above CDS table function

CLASS zg_cl_st_text DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES: if_amdp_marker_hdb.
    TYPES:
      BEGIN OF ty_status,
        objnr         TYPE j_objnr,
        status_profile TYPE j_stsma,
        system_status TYPE char40,
        user_status   TYPE char40,
      END OF ty_status.
    TYPES:
     tt_status TYPE STANDARD TABLE OF ty_status.
    DATA : gt_status TYPE STANDARD TABLE OF ty_status.
    CLASS-METHODS get_status_text FOR TABLE FUNCTION ZG_CDS_TF_TEXT.
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.

CLASS zg_cl_st_text IMPLEMENTATION.
  METHOD get_status_text BY DATABASE FUNCTION
                             FOR HDB
                             LANGUAGE SQLSCRIPT
                             OPTIONS READ-ONLY
                             USING jest jsto tj30t tj02t.
    gt_status = SELECT DISTINCT a.objnr,
                      b.stsma as status_profile,
                      d.txt04 as system_status,
                      c.txt04 as user_status
                      from JEST as a
                      inner join jsto as b on b.objnr = a.objnr
                      left outer join tj30t as c on c.estat = a.stat and
                                                    c.stsma = b.stsma and
                                                    c.spras = 'E'
                      left outer join tj02t as d on d.istat = a.stat and
                                                    d.spras = 'E'
                       where  a.inact <> 'X';

    RETURN SELECT objnr,
                  status_profile,
                  STRING_AGG( system_status, ',' ORDER BY system_status) as system_status,
                  STRING_AGG( user_status, ',' ORDER BY user_status) as user_status
               FROM :gt_status
               GROUP BY objnr, status_profile;
  ENDMETHOD.
ENDCLASS.

3. Finally creating CDS view for reporting purposes.

@AbapCatalog.sqlViewName: 'YGF_SQL_STAT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'CDS View for Status Text'
@ClientDependent: true
define view YGF_DDL_STAT as select from ygf_ddl_stat_tf as a {
    key a.objnr,
        a.status_profile,
        a.system_status,
        a.user_status
}

Result:

Conclusion:

Utilizing built-in functions, we are able to collate system statuses and user statuses for any Object.

Next, I’ll share the solution for searching Object records having a particular system status.

References:

CDS Table functions