Get the list of equipment with it’s parent equipment and top level equipment using CDS views

Introduction

This blog post discusses the usage of the CDS views, CDS Table Functions and AMDP methods with code push down approach to do calculations and write business logic at database level instead of application server level.

Here I have shared one scenario where the business requirement is to find the list of equipment with it’s parent equipment and the lop level equipment.

As we know that parent equipment can be easily found in EQUZ-HEQUI but for getting its top level equipment it requires little calculation and logic.

Now for doing these kinds of calculations and logic at database level we have AMDP methods where we can write using SQL code and achieve our desired output.

You can refer below link for basic understanding of CDS Table Function

CDS Development User Guid

Requirement

Business requirement is to get the list of equipment with its parent equipment and top level equipment using CDS views

Now as we know the parent equipment can be easily found in table EQUZ field HEQUI, but the top level equipment is nowhere present in any database table

So if we want to achieve this functionality using ABAP then it will be a piece of cake for any ABAPer using LOOP and SELECT statements.

But to achieve this using CDS views we need to explore AMDP feature and write calculation and logic part for top level equipment.

Given equipment hierarchy structure is somewhat like

Equipment Hierarchy

Required final output table should be as below

Approach

So Let’s begin.

Step 1 – Create CDS Table Function

Create CDS Table Function name zi_equi_hier_1

Input will be mandt and output will be mandt, equipment, parent equipment and top level equipment.

implemented by method zcl_get_hier=>get_top_hier;

@EndUserText.label: 'Equipment Hierarchy'
define table function zi_equi_hier_1
with parameters 
 @Environment.systemField: #CLIENT
mandt:abap.clnt
returns {
mandt : mandt;
equnr : equnr;  // Equipment
pequi : equnr;  // Parent Equipment
hequi : equnr;  // Top Level Equipment
}
implemented by method zcl_get_hier=>get_top_hier;

Step 2 – Implement the Table Function method AMDP

Define class ZCL_GET_HIER

INTERFACES IF_AMDP_MARKER_HDB is must to use when defining as an AMDP procedure

Create class-method GET_TOP_HIER for table function zi_equi_hier_1.

Also create two private class methods

get_all_equnr – For getting the list of equipment having parent

get_top – For getting the list of equipment and its top equipment

class ZCL_GET_HIER definition
  public
  final
  create public .

public section.
TYPES: BEGIN OF ty_equi,
       equnr TYPE equnr,
       hequi TYPE equnr,
       END OF ty_equi,

       BEGIN OF ty_equnr,
       equnr TYPE equnr,
       END OF ty_equnr,

       tty_equi TYPE STANDARD TABLE OF ty_equi,
       tty_equnr TYPE STANDARD TABLE OF ty_equnr.

data: lv_data type char1,
      lv_equnr type equnr,
      lv_hequi type equnr,
      lwa_equi TYPE ty_equi,
      lt_equi TYPE STANDARD TABLE OF ty_equi.

INTERFACES IF_AMDP_MARKER_HDB.
class-methods: GET_TOP_HIER  FOR TABLE FUNCTION zi_equi_hier_1.

protected section.
private section.
class-methods: get_top EXPORTING VALUE(et_equi) TYPE tty_equi,
               get_all_equnr EXPORTING VALUE(et_equnr) TYPE tty_equnr.
ENDCLASS.

Now implement method get_all_equnr having parent, this method is being called from get_top method.

METHOD get_all_equnr BY DATABASE PROCEDURE
                     FOR HDB LANGUAGE SQLSCRIPT
                     OPTIONS READ-ONLY USING equz.
declare lv_mandt "$ABAP.type( MANDT )" := session_context('CLIENT');
et_equnr = select equnr
           from equz
           where mandt = :lv_mandt
            and  hequi <> '';
endmethod.

Now implement method get_top to get the list of equipment and its top equipment

Call get_all_equnr method to get the list of equipment having parent

Start first loop for each of the equipment got from above method.

Now for each equipment start second loop to gets its top most equipment

Start Second Loop until hequi is empty in EQUZ table

If hequi is not empty then replace its value with equnr to be used in next iteration

Else fill exporting table value and end second loop.

Check if we reached to the last equipment

If yes then end first loop

Else goto next equipment in first loop

METHOD get_top BY DATABASE PROCEDURE
               FOR HDB LANGUAGE SQLSCRIPT
               OPTIONS READ-ONLY USING equz ZCL_GET_HIER=>GET_ALL_EQUNR.
declare lv_mandt "$ABAP.type( MANDT )" := session_context('CLIENT');
DECLARE lv_dat int;
DECLARE lv_data int;
DECLARE lv_ind int;
DECLARE lv_equi char( 18 );
DECLARE lv_equnr char( 18 );
DECLARE lv_hequi char( 18 );
CALL "ZCL_GET_HIER=>GET_ALL_EQUNR"( et_equnr => lt_equi );

lv_ind = 1;
lv_data = 1;
while:lv_data = 1 DO

lv_dat = 1;
while:lv_dat = 1 DO

if :lv_equi = ''
then
lv_equi = :lt_equi.equnr[lv_ind];
lv_equnr = :lt_equi.equnr[lv_ind];
ELSEIF :lv_equi IS NULL
then
lv_equi = :lt_equi.equnr[lv_ind];
lv_equnr = :lt_equi.equnr[lv_ind];
END IF;

begin

 lt_data = select hequi as hequi
 from equz
 where mandt = :lv_mandt and
       equnr = :lv_equi;

lv_hequi = :lt_data.hequi[1];

if :lv_hequi = ''
then
et_equi.equnr[lv_ind] = :lv_equnr;
et_equi.hequi[lv_ind] = :lv_equi;
lv_dat = 0;
ELSE
lv_equi = lv_hequi;
END IF;
end;
end WHILE ;

lv_ind = lv_ind + 1;

lv_equi = :lt_equi.equnr[lv_ind];

if :lv_equi IS NULL
then
lv_data = 0;
else
lv_equi = '';
lv_equnr = '';
end if;

end WHILE ;

ENDMETHOD.

Now implement method GET_TOP_HIER this will return data to the table function

First call GET_TOP method to get the list of equipment and top level equipment

Then join the result table with the EQUZ to get the final output

  method GET_TOP_HIER BY DATABASE FUNCTION
                      FOR HDB LANGUAGE SQLSCRIPT
                      OPTIONS READ-ONLY USING equz ZCL_GET_HIER=>GET_TOP.

CALL "ZCL_GET_HIER=>GET_TOP"(et_equi => lt_equi);

  RETURN
  select eq.mandt as mandt,
         eq.equnr as equnr,
         eq.hequi as pequi,
         teq.hequi as hequi
         from equz as eq
         LEFT OUTER JOIN :lt_equi as teq
         ON teq.equnr = eq.equnr
         where eq.mandt = :mandt;

  endmethod.

Step 3 – Use CDS Table Function in a new CDS View

Create new CDS ZEQUI_HIER_2 and use above created table function

@AbapCatalog.sqlViewName: 'ZEQUI_HIER2'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'ZI_EQUI_HIER_1'
define view ZEQUI_HIER_2 
as select from zi_equi_hier_1( mandt : $session.client ) as zeq
{
 key zeq.equnr as Equipment,
     zeq.pequi as Parent_Equipment,
     zeq.hequi as Top_Level_Equipment       
}

Step 4 – Execute New CDS view and get the desires out

Execute new CDS ZEQUI_HIER_2 and here is the result !!

Conclusion

Hence we can see that by utilizing the features of AMDP class/methods we can write calculations/business logic in the database level making it more towards code-push-down.

As an ABAPer writing code in SQLScript can be bit difficult at first, but with practice it will become as simple as ABAP.

Refer below link for more basic understanding on SQLScript
SAP HANA SQLScript Reference

I hope you find this blog post useful and benefit from it. Feel free to ask any question or provide your feedback in the comment section below.

Never stop learning, because life never stops teaching !!