Combine Select-option, CDS Table function and Call AMDP methods in Program

[chau1995]

When we use openSQL, filtering data from parameters and select-option is too easy. With AMDP or CDS, we can only filter data with parameters. Some case, we want to filter both
select-option and parameters, So how can do it?
Additionally, AMDP is only a method, so if we have more AMDP methods, how can we call another AMDP inside the AMDP method?
Stay tuned!!! We will learn it in this article.

Prerequisites:

• Eclipse ADT Bundle or HANA Studio


• SAP HANA DB 1.0 or higher


• Basic knowledge with AMDP. If you don't have it, don't worry, please refer to this site: https://blogs.sap.com/2020/08/25/first-program-with-amdp-method/

1. CDS Table Function

Firstly, we can create a CDS table function to view data when handle logic from AMDP procedure

@EndUserText.label: 'ZCDS_TABLE_FUNC'

@ClientHandling.type: #CLIENT_DEPENDENT 

define table function ZCDS_TABLE_FUNC

with parameters IV_STRING : abap.char( 255 )

returns {

  CLIENT      : abap.clnt;

  RBUKRS      : abap.char( 4 );

  BELNR       : belnr_d;

  GJAHR       : gjahr;

  BUZEI       : buzei;

  BUDAT       : budat;

  BLART       : blart_d;

  RACCT       : racct;

  RACCT_TEXT  : abap.char( 50 );

  HSL         : wertv12;

  OSL         : wertv12;

  LIFNR       : lifnr;

  KUNNR       : kunnr;

  VENDOR_TEXT : abap.char( 50 );

  CUST_TEXT   : abap.char( 50 );

}

implemented by method ZCL_AMDP_TUTORIAL_02=>GET_DATA;

With statement

implemented by method ZCL_AMDP_TUTORIAL_02=>GET_DATA;

CDS will delegate all logic inside GET_DATA method. That mean's when GET_DATA was run, it would return a dataset, and this CDS table function will store temperately it to show in main program

Please note that the number of the field when you return in CDS must be matched with AMDP procedure

2. Class Definition

CLASS ZCL_AMDP_TUTORIAL_02 DEFINITION

  PUBLIC

  FINAL

  CREATE PUBLIC .



  PUBLIC SECTION.

    interfaces IF_AMDP_MARKER_HDB .



    TYPES:

      BEGIN OF TY_ACDOCA,

        RBUKRS TYPE ACDOCA-RBUKRS,

        BELNR  TYPE ACDOCA-BELNR,

        GJAHR  TYPE ACDOCA-GJAHR,

        BUZEI  TYPE ACDOCA-BUZEI,

        BUDAT  TYPE ACDOCA-BUDAT,

        BLART  TYPE ACDOCA-BLART,

        RACCT  TYPE ACDOCA-RACCT,

        RACCT_TEXT TYPE SKAT-TXT50,

        HSL    TYPE ACDOCA-HSL,

        OSL    TYPE ACDOCA-OSL,

        LIFNR  TYPE ACDOCA-LIFNR,

        KUNNR  TYPE ACDOCA-KUNNR,

        VENDOR_TEXT TYPE TXT50,

        CUST_TEXT   TYPE TXT50,

      END   OF TY_ACDOCA,

      TT_ACDOCA TYPE STANDARD TABLE OF TY_ACDOCA,



      BEGIN OF TY_VENDOR,

        LIFNR TYPE BUT000-PARTNER,

        TEXT  TYPE TXT50,

      END   OF TY_VENDOR,

      TT_VENDOR TYPE STANDARD TABLE OF TY_VENDOR,



      BEGIN OF TY_CUST,

        KUNNR TYPE BUT000-PARTNER,

        TEXT  TYPE TXT50,

      END   OF TY_CUST,

      TT_CUST TYPE STANDARD TABLE OF TY_CUST.

    CLASS-METHODS:

      GET_DATA  FOR TABLE FUNCTION ZCDS_TABLE_FUNC.

  PROTECTED SECTION.

  PRIVATE SECTION.

    CLASS-METHODS:

      GET_DATA_FROM_VENDOR EXPORTING VALUE(EV_VENDOR) TYPE TT_VENDOR,

      GET_DATA_FROM_CUST   EXPORTING VALUE(EV_CUST)   TYPE TT_CUST,

      GET_DATA_FROM_ACDOCA IMPORTING VALUE(IV_STRING) TYPE STRING

                           EXPORTING VALUE(EV_ACDOCA) TYPE TT_ACDOCA.

ENDCLASS.

 

3.Class Implementation

CLASS ZCL_AMDP_TUTORIAL_02 IMPLEMENTATION.

""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

"

""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

  METHOD GET_DATA_FROM_ACDOCA BY DATABASE PROCEDURE FOR HDB

                              LANGUAGE SQLSCRIPT

                              OPTIONS READ-ONLY

                              USING ACDOCA SKAT.

    --init MANDT variable

    DECLARE lv_CLNT "$ABAP.type( MANDT )" := session_context('CLIENT');

    --init SPRAS variable

    DECLARE lv_langu "$ABAP.type( SPRAS )" := 'E';



    --GET DATA FROM SOURCE

   EV_ACDOCA =

      SELECT T1.RBUKRS,

             T1.BELNR,

             T1.GJAHR,

             T1.BUZEI,

             T1.BUDAT,

             T1.blart,

             LTRIM( T1.RACCT,'' ) AS RACCT,

             T2.TXT50 AS RACCT_TEXT,

             T1.HSL,

             T1.OSL,

             T1.LIFNR,

             T1.KUNNR,

             '' AS VENDOR_TEXT,

             '' AS CUST_TEXT

        FROM ACDOCA     AS T1

       INNER JOIN SKAT  AS T2

          ON T1.RCLNT = T2.MANDT

         AND T1.rclnt = :lv_clnt

         AND T2.MANDT = :LV_CLNT

         AND T1.RACCT = T2.saknr

         AND T2.SPRAS = :LV_LANGU

         AND T1.KTOPL = T2.KTOPL;



   --USING APPLY FILTER TO TABLE

   EV_ACDOCA = APPLY_FILTER( :EV_ACDOCA, :IV_STRING );

  ENDMETHOD.

""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

"

""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

  METHOD GET_DATA_FROM_CUST BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT

                            OPTIONS READ-ONLY

                            USING KNA1.

    --init MANDT variable

    DECLARE lv_CLNT "$ABAP.type( MANDT )" := session_context('CLIENT');



    EV_CUST = SELECT T1.KUNNR,

                     T1.NAME1 AS TEXT

                FROM KNA1 AS T1

               WHERE T1.MANDT = :lv_CLNT;

  ENDMETHOD.



""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

"

""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

  METHOD GET_DATA_FROM_VENDOR BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT

                              OPTIONS READ-ONLY

                              USING LFA1.

    --init MANDT variable

    DECLARE lv_CLNT "$ABAP.type( MANDT )" := session_context('CLIENT');



    EV_VENDOR = SELECT T1.LIFNR,

                       T1.NAME1 AS TEXT

                  FROM LFA1 AS T1

                 WHERE T1.MANDT = :lv_CLNT;

  ENDMETHOD.



""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

"

""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

  METHOD GET_DATA BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT

                  OPTIONS READ-ONLY

                  USING ZCL_AMDP_TUTORIAL_02=>GET_DATA_FROM_VENDOR

                        ZCL_AMDP_TUTORIAL_02=>GET_DATA_FROM_CUST

                        ZCL_AMDP_TUTORIAL_02=>GET_DATA_FROM_ACDOCA.

    --init MANDT variable

    DECLARE lv_CLNT "$ABAP.type( MANDT )" := session_context('CLIENT');



    --GET DATA FROM VENDOR MASTER DATA

    CALL "ZCL_AMDP_TUTORIAL_02=>GET_DATA_FROM_VENDOR"( EV_VENDOR => :EV_VENDOR );

    --GET DATA FROM CUSTOMER MASTER DATA

    CALL "ZCL_AMDP_TUTORIAL_02=>GET_DATA_FROM_CUST"( EV_CUST => :EV_CUST );

    --GET DATA FROM FI TRANSACTION DATA

    CALL "ZCL_AMDP_TUTORIAL_02=>GET_DATA_FROM_ACDOCA"(

                                                        IV_STRING => :IV_STRING,

                                                        EV_ACDOCA => :EV_ACDOCA

                                                     );



    --RETURN VALUE

    RETURN      SELECT :lv_CLNT AS CLIENT,

                       T1.RBUKRS,

                       T1.BELNR,

                       T1.GJAHR,

                       T1.BUZEI,

                       T1.BUDAT,

                       T1.BLART,

                       T1.RACCT,

                       T1.RACCT_TEXT,

                       T1.HSL,

                       T1.OSL,

                       T1.LIFNR,

                       T1.KUNNR,

                       T3.TEXT              AS VENDOR_TEXT,

                       T2.TEXT              AS CUST_TEXT

                  FROM      :EV_ACDOCA      AS T1

                  LEFT JOIN :EV_CUST        AS T2

                    ON T2.KUNNR = T1.KUNNR

                  LEFT JOIN :EV_VENDOR      AS T3

                    ON T3.LIFNR = T1.LIFNR

                 ORDER BY T1.RBUKRS,

                          T1.BELNR,

                          T1.GJAHR;





  ENDMETHOD.

ENDCLASS.

By using

DECLARE lv_CLNT "$ABAP.type( MANDT )" := session_context('CLIENT');

We want to get exactly CLIENT in the system and filter it in the database.

Look at GET_DATA method, We will easily call another AMDP by using statement CALL

    --GET DATA FROM VENDOR MASTER DATA

    CALL "ZCL_AMDP_TUTORIAL_02=>GET_DATA_FROM_VENDOR"( EV_VENDOR => :EV_VENDOR );

    --GET DATA FROM CUSTOMER MASTER DATA

    CALL "ZCL_AMDP_TUTORIAL_02=>GET_DATA_FROM_CUST"( EV_CUST => :EV_CUST );

    --GET DATA FROM FI TRANSACTION DATA

    CALL "ZCL_AMDP_TUTORIAL_02=>GET_DATA_FROM_ACDOCA"(

                                                        IV_STRING => :IV_STRING,

                                                        EV_ACDOCA => :EV_ACDOCA

                                                     );

 

4. Main Program

We need to create main report to execute this CDS table function

DATA: GV_BELNR TYPE BELNR_D,

      GV_WHERE TYPE STRING.



DATA: OUT TYPE REF TO IF_DEMO_OUTPUT.



OUT = CL_DEMO_OUTPUT=>NEW( ).

SELECTION-SCREEN: BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

  PARAMETERS:

    P_RBUKRS TYPE BUKRS,

    P_GJAHR  TYPE GJAHR.

  SELECT-OPTIONS:

    S_BELNR  FOR  GV_BELNR.

SELECTION-SCREEN: END OF BLOCK B1.





START-OF-SELECTION.

  PERFORM TRANSFER_PARAMS_TO_STRING.



  SELECT *

    FROM ZCDS_TABLE_FUNC( IV_STRING = @GV_WHERE )

    INTO TABLE @DATA(GT_OUTPUT).

  OUT->DISPLAY( GT_OUTPUT ).



*&---------------------------------------------------------------------*

*&  TRANSFER PARAMETER AND SELECT-OPTION TO STRING VALUE

*&---------------------------------------------------------------------*

FORM TRANSFER_PARAMS_TO_STRING.

  DATA(LV_CONDITION) = CL_SHDB_SELTAB=>COMBINE_SELTABS(

                                          IT_NAMED_SELTABS = VALUE #(

                                                                      ( NAME = 'BELNR'  DREF = REF #( S_BELNR[]  ) )

                                                                    )

                                                      ).

  IF P_RBUKRS IS NOT INITIAL.

    GV_WHERE = |RBUKRS = '{ P_RBUKRS }'|.

  ENDIF.



  IF P_GJAHR IS NOT INITIAL.

    GV_WHERE = | AND { GV_WHERE } AND GJAHR = '{ P_GJAHR }'|.

  ENDIF.



  IF LV_CONDITION IS NOT INITIAL.

    GV_WHERE = |{ LV_CONDITION } { GV_WHERE } |.

  ELSE.

    GV_WHERE = |{ GV_WHERE }|.

  ENDIF.

ENDFORM.

Because we have both Parameter and Select-option in the screen, so if we want to filter in AMDP, we can transfer it to string by using COMBINE_SELTABS,  then pass it in CDS table function parameter.

  DATA(LV_CONDITION) = CL_SHDB_SELTAB=>COMBINE_SELTABS(

                                          IT_NAMED_SELTABS = VALUE #(

                                                                      ( NAME = 'BELNR'  DREF = REF #( S_BELNR[]  ) )

                                                                    )

                                                      ).

 

Run this program, input your parameter and you will see data show on the screen.

Hope this helps!!!