Implementing AMDP procedure in the BW customer exit variable

Introduction

Implementing AMDP procedure for the custom logic used in the BW customer exit variables.

Applies to

BW/4HANA, BW Powered by HANA.

Summary

What you learn from this blog is to push the custom logics based on table lookups into the HANA via AMDP method to Database instead of relying on the application server.

Many a times in our customer exit variables we might have to read from the cube, ADSO tables, info object tables, HANA Tables, SAP Standard, Custom tables etc.

if we are in a bw4hana then we can use the AMDP procedure to do the “select operations” from the tables. The reason is simple we are trying to bring the database operation to the database itself.(Code to Database)

Author          : Lakshminarasimhan Narasimhamurthy

Created on   : 13/JUN/2021

Body

Requirement

I have a customer exit variable and the logic is that it must include only the cost centers that are present in the employee master data InfoObject and also include costcenters present for the user in the Z info object(Custom info object).

we can implement the badi RSROA_VARIABLES_EXIT and Now in the custom logic part I’m going to make use of AMDP procedures to read the costcenters.

AMDP stands for abap managed database procedures, basically we’re going to make use of native SQL statements within the method.

The interface IF_AMDP_MARKER_HDB must be added to the public section of the class and then add the method to the class with importing and exporting parameters.

The AMDP methods must not be blank and must have SQL statements only.

In the AMDP method implementation we must add the following,

Method <method name>

By database procedure

For HDB

Language sql-script

Options Read-only

Using <table name/view names>

I have created the method “get_cost_centers” inside the class ycl_amdp_var_exit_1(Screen shot 1), the method takes the input of the username and outputs the relevant costcenters from the masterdata.

(Note : The master data infoobjects/ADSO’s must be exposed to hana in prior via the check box “External SAP HANA view for master data” if at all you need to read from the generated view(s) in HANA for the BW objects)

The table type YCOST_CENTERS has been created with the line type as cost center(Screen shots 2 and 3)

Screen shot 1 –

Code snippet

class ycl_amdp_var_exit_1 definition
public
final
create public.
public section.
interfaces IF_AMDP_MARKER_HDB.

METHODS:
get_cost_centers
importing
value(lp_un) type XUBNAME
exporting
value(result) type YCOST_CENTERS raising cx_amdp_error.

protected section.
private section.
endclass.
class ycl_amdp_var_exit_1 implementation.
method get_cost_centers
by database procedure for hdb language sqlscript options read-only
using /BIC/Q<table name> /bic/p<table name>.

result = select MAST_CCTR
from
"/BIC/Q<table name>"
where
dateto = '99991231' and
"/BIC/<field name>" = :lp_un
union
select "/BIC/<field name>" AS MAST_CCTR
from
"/BIC/P<table name>"
where
"/BIC/<field name>" = :lp_un;

endmethod.

Screen shot 2 –

Screen shot 3 –

Now create a query (Screen shot 5)and customer exit variable for cost center(Screen shot 4). The processing type is customer exit and it is a mandatory variable.

Screen shot 4 –

Screen shot 5-

I have implemented the badi RSROA_VARIABLES_EXIT and the debugger comes to the variable Y_COSTCENTERS in i_step 2. (Even in i_step 1 same logic can be applied).

The code is given below in Screen shot 6.

Screen shot 6 –

    data : lv_username         type                   xubname,
           ls_range            type                   rrrangesid,
           yob_amdp_var_exit_1 type ref to            ycl_amdp_var_exit_1,
           yt_cost_centers     type                   ycost_centers,
           ya_cost_centers     type line of           ycost_centers.

    case i_vnam.

      when 'Y_COSTCENTERS_01'. " Customer exit variable

To get the username of the user executing the report(SYST-UNAME can also be used instead of the below FM).

          clear lv_username.

          call function 'RSEC_GET_USERNAME'
           importing
             e_username       = lv_username.

Call the AMDP method

             clear la_pernr.

             create object yob_amdp_var_exit_1.

*             try.
             call method yob_amdp_var_exit_1->get_cost_centers
               exporting
                 lp_un  = lv_username
               importing
                 result = yt_cost_centers
                 .
*              catch cx_amdp_error .
*             endtry.

In debug mode

After F6 the data is populated in the yt_cost_centers

As expected 5 cost centers were retrieved from master data.

The report now yields the output

The user(1736) has the following cost centers in Z* infoobject and master cost center in 0employee master data and all of them are visible in the report.

Note – We can debug the AMDP procedure step by step using “Debugger perspective” in SAP HANA STUDIO. Step by step debug of AMDP is possible using F5, F6 etc similar to ABAP Debugger. This will give a greater clarity on the execution of SQL Scripts.

Welcoming the readers to provide feedback and engage with me in the comment section.

References 

Introduction to ABAP Managed Database Procedure (AMDP) (saplearners.com)