ABAP CDS Select from dynamic data source

[AbdelrahmanZaki]

Hello friends,

I faced a little problem while reporting in Fund Management (FM) module as the tables are generated automatically while creating derivation rules with dynamic names and differs by system /client.

The rule entries are stored in the generated table "FMFMOAXXXYYYZZZZ", where XYZ symbols means as follows:

• XXX is the system specification (e.g., DE1 or QE1)
• YYY is the client number (e.g., 120 or 200)

ZZZZ is the number of the derivation rule (which you should have as per your table 31, 51, 64, or 61)

To be able to move my report / solution I had to selecting from dynamic source which is not supported in CDS!

After investing some time I came to try it using AMDP with table function

Now let's explain it as simple as possible. 

*Step 1 create a table function  *

@EndUserText.label: 'demo table function' 
define table function zcds_amdp_demo_tf with parameters @Environment.systemField:#CLIENT clnt5 : abap.clnt, derivation : char4 
returns { 
MANDT : mandt ; 
SOUR1_FROM : ps_posid ;
SOUR1_TO : ps_posid ;
VALID_FROM : abaintab ;
TARGET1 : fistl;
DELETE_FLG : abadrdelflag;
ADDED_BY : abadrerfasser;
ADDED_ON : erdat; 

} 
implemented by method zcl_amdp_demo=>get_data; ​

*Step 2 Create the class zcl_amdp_demo*

CLASS zcl_amdp_demo DEFINITION PUBLIC FINAL CREATE PUBLIC.
PUBLIC SECTION.
INTERFACES: if_amdp_marker_hdb, if_oo_adt_classrun.

CLASS-METHODS get_data FOR TABLE FUNCTION zcds_amdp_demo_tf.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.

CLASS zcl_amdp_demo IMPLEMENTATION. 
  METHOD get_data BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING tadir.
    declare lv_fm_tab string; 
    declare tablename string; 
    declare clientnumber string; 
    declare systemid string; 
    declare deriv string;
*must be the same data types as consuming cds also in same order 
declare it_fm_tab table ( 
  MANDT nvarchar(3), 
  SOUR1_FROM nvarchar(24), 
  SOUR1_TO nvarchar(24), 
  VALID_FROM nvarchar(10), 
  TARGET1 nvarchar(16), 
  DELETE_FLG nvarchar(1), 
  ADDED_BY nvarchar(12),
  added_on nvarchar(10) ); 
  
* fmfmoaxxxyyyzzzz 
*fields from parameters 
clientnumber = clnt5; 
select srcsystem into systemid from tadir where pgmid = 'HEAD' and object = 'SYST';

*'COST' => Cost Element = Commitment Item 
*'WBSE' => wbs Element = Funds Center

  if derivation = 'COST' then
    if systemid = 'DS1' then deriv = '0051'; end if; 
    if systemid = 'QS1' then deriv = '0062'; end if; 
    if systemid = 'PS1' then deriv = '0051'; end if; 
  else
    if systemid = 'DS1' then deriv = '0052'; end if;
    if systemid = 'QS1' then deriv = '0064'; end if;
    if systemid = 'PS1' then deriv = '0041'; end if; 
  end if; 
  
  tablename = 'FMFMOA' || systemid || clientnumber || deriv; 
  
  EXECUTE immediate 'select MANDT, SOUR1_FROM, SOUR1_TO, VALID_FROM, TARGET1, DELETE_FLG, ADDED_BY, ADDED_ON from ' || tablename into it_fm_tab ;
  
  RETURN SELECT * FROM :it_fm_tab; ENDMETHOD.
ENDCLASS.
​

*Step 3 and final Create a sonsumable CDS view*

@AbapCatalog.sqlViewName: 'ZCDS_AMDP_DEMO_V' 
@AbapCatalog.compiler.compareFilter: true 
@AbapCatalog.preserveKey: true 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
@EndUserText.label: 'CDS that consuming the TF' 
define view zcds_amdp_demo as select from zcds_amdp_demo_tf (clnt5 : $session.client, derivation : 'COST' ) 
//COST => Cost Element = Commitment Item 
//WBSE =>WBS Element = Funds Center 
{ 
* 
}​

Output:

A Closer look: if you change the parameter from 'COST' to 'WBSE' the return changes which makes it dynamic selection now 🙂 .. congratulations!

Please feel free to comment any observations, enhancements or recommendations.

Regards,
Abdelrahman Zaki

[ravishgarg]

Hi Abdelrahman 

Could this also not be achieved by using a Custom Entity if your use case is to consume it via Fiori?

Regards

RG

[AbdelrahmanZaki]

Hi Ravi,

Yes, it can be achieved using a Custom Entity with the annotation:

@ObjectModel.query.implementedBy: 'ABAP:<ZCL_CLASS_NAME>'

if you are publishing a Fiori app (which means application layer). However, our blog is focused on preparing a data source for consumption in the database layer.

Regards,
Abdelrahman Zaki

[AbdelrahmanZaki]

Dears,

I recently made some progress and wanted to share it with you. The get_data method update makes the select statement more dynamic and complex, as shown below:

There's also an interesting point in AMDP: if you want to use single quotes inside a string, you need to use double quotes, similar to ABAP syntax.

  METHOD get_data BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY
  USING
  tadir
  .
    declare lv_fm_tab string;
    declare tablename string;
    declare clientnumber string;
    declare systemid string;
    declare deriv string;
    declare select_state string;
*    must be the same data types as consuming cds also in same order
    declare it_fm_tab table (
      mandt nvarchar(3),
      sour1_from nvarchar(24),
      sour1_to nvarchar(24),
      valid_from nvarchar(10),
      target1 nvarchar(16),
      delete_flg nvarchar(1),
      added_by nvarchar(12),
      added_on nvarchar(10),
      sour2_from nvarchar(12),
      sour2_to   nvarchar(12)
      );

*    fields from parameters
    clientnumber = clnt5;
    SELECT srcsystem INTO systemid FROM tadir WHERE pgmid = 'HEAD' AND object = 'SYST';


      IF derivation = 'COST' then
        IF systemid = 'DS1' then deriv = '0051'; END if;
        IF systemid = 'QS1' then deriv = '0062'; END if;
        IF systemid = 'PS1' then deriv = '0051'; END if;

      select_state = 'select MANDT, SOUR1_FROM, SOUR1_TO, VALID_FROM, TARGET1, DELETE_FLG, ADDED_BY, ADDED_ON, '' '' as SOUR2_FROM, '' '' as SOUR2_TO from ';

      END if;
      IF derivation = 'NETW' then
        IF systemid = 'DS1' then deriv = '0022'; END if;
        IF systemid = 'QS1' then deriv = '0063'; END if;
        IF systemid = 'PS1' then deriv = '0051'; END if;

        select_state = 'select MANDT, SOUR1_FROM, SOUR1_TO, VALID_FROM, TARGET1, DELETE_FLG, ADDED_BY, ADDED_ON, SOUR2_FROM, SOUR2_TO from ';
        
      END if;

    tablename = 'FMFMOA' || systemid || clientnumber || deriv;
    EXECUTE immediate select_state || tablename into it_fm_tab ;
    RETURN SELECT * FROM :it_fm_tab;

  ENDMETHOD.

Regards,

Abdelrahman Zaki