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: