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