Personal Insights
Combine Select-option, CDS Table function and Call AMDP methods in Program
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!!!
What's the use of CDS view with parameters? Is it only for single value? Can we pass multiple values to it? Should we use the table function approach when we need to use select-options?
You cannot use select options in CDS View directly. If you want use it, i think you have some options:
1. Select from cdsview and filter it in ABAP program
2. Use cds table function instead
I just have do it
Good one Chau. Very informative. Keep the great work.
Hi Chau,
Nice blog and great work...!
Hi!
is it only possible to select all records before filtering?
or
is it possible to apply filtering at the first select statement that joins with others by using the iv_string parameter that has join alias.(column), like a.carrid = 'AA' and b.cityfrom = 'NEW YORK'?
Yes, you can select all records before filtering
And you can also select with where condition (only for parameter)
If you filter is select-option, you must use apply_filter function