Table Function Implementation in SAP RAP (ABAP RESTful Application Programming)

[Aman07]

Introduction-

Table Function - In SAP RAP (ABAP RESTful Application Programming), a table function is a special kind of function in CDS (Core Data Services) that allows you to return a structured set of data, like a database table. And it is used for Complex Logic, Performance Optimization, Flexible Data Retrieval.

Implementation-

Sample Requirement – We have a header database table and an item database table with several fields. From the item table, we need to retrieve the values of the Amount and Tax Amount fields. Using these values, we will calculate the Total Amount as the sum of Amount and Tax Amount. After that, we will apply a 5% discount on the Total Amount and compute the Discounted Amount accordingly for the header COMPANY_CODE  = ‘4601’ and PURCH_ORG = ‘P001’.

Solution-

Header table

@EndUserText.label : 'header table'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table ztm_head_11559 {

  key client          : abap.clnt not null;
  key document_number : sysuuid_x16 not null;
  status              : ztstatus_code not null;
  company_code        : ztcompny_cd;
  purchase_org        : ztpurc_org;
  currency            : ztcurr;
  supplier_number     : ztsupp_no;
  changed_at          : ztchange_at;
  created_at          : ztcreatedat_dt;
  created_by          : ztcreatby_nm;
  last_localchange    : timestampl;

}

 Item Table.

@EndUserText.label : 'Item DB'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table ztm_itm_11559 {

  key client          : abap.clnt not null;
  key document_number : sysuuid_x16 not null;
  key item_number     : sysuuid_x16 not null;
  status              : ztdoc_st;
  quantity            : ztitm_qty;
  currency            : ztcurr;
  @Semantics.amount.currencyCode : 'ztm_head_11559.currency'
  amount              : ztitm_amt;
  @Semantics.amount.currencyCode : 'ztm_head_11559.currency'
  tax_amount          : zttax_amt;
  changed_at          : ztchange_at;
  created_at          : ztcreatedat_dt;
  created_by          : abp_creation_user;
  localchangeat       : timestampl;

}

Creating the table function.

Right Click on the data definition a chose the option New data definition --> give the table function name and description.

Now click on Next --> next --> choose the option (Table Function) --> Finish.

Created Table Function and added the required fields. here I have taken document_number, company_code, Purchage_org from Header Table and amount and tax_amount from item table. and  created two new fields total_amount and discounted_amount to take the Implemented values.

and Give the CLASS name (zcl_tf_info_11559) and METHOD (get_info) you can give your own name as per your Preference.

Refer the given code :-

@EndUserText.label: 'table funtion'
@ClientHandling.type: #CLIENT_DEPENDENT
define table function ztf_info_11559
returns
{
  client            : abap.clnt;
  document_number   : sysuuid_x16;
  company_code      : ztcompny_cd;
  currency          : ztcurr;
  purchase_org      : ztpurc_org;
  amount            : ztitm_amt;
  tax_amount        : zttax_amt;
  total_amount      : ztitm_amt;
  discounted_amount : ztitm_amt; //taking 5% discount

}

implemented by method
  zcl_tf_info_11559=>get_data;

Here I have to give the annotation @ClientHandling.type: #CLIENT_DEPENDENT
you can choose client as per your requirement. 

Now here we have to add two new fields total_amount and discounted_amount and giving your class name and method name (zcl_tf_info_11559  => get_data) get_details method name.

Now create the Interface View (CDS) for the table function ( ZTF_INFO_11559  ).

CDS-> Add your reference fields accordingly like currency semantics.

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'info cds'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
}
define view entity zcds_info_cds_11559
  as select from ztf_info_11559
{
  document_number,
  company_code,
  currency,
  purchase_org,
  @Semantics.amount.currencyCode : 'currency'
  amount,
  @Semantics.amount.currencyCode : 'currency'
  tax_amount,
  @Semantics.amount.currencyCode : 'currency'
  total_amount,
  @Semantics.amount.currencyCode : 'currency'
  discounted_amount
  
    
}

CLASS Implementation Code.

CLASS zcl_tf_info_11559 DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.
    CLASS-METHODS get_data
        FOR TABLE FUNCTION ztf_info_11559.
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.



CLASS zcl_tf_info_11559 IMPLEMENTATION.




  METHOD get_data BY DATABASE FUNCTION
                  FOR HDB LANGUAGE SQLSCRIPT
                  OPTIONS READ-ONLY
                  USING ztm_head_11559 ztm_itm_11559.



    RETURN

    select a.client as client,
    a.document_number as document_number,
    a.company_code as company_code,
    a.currency as currency ,
    a.purchase_org as purchase_org ,
    sum( b.amount ) as  amount ,
    sum(b.tax_amount) as tax_amount,
    ( sum(b.amount) +  sum(b.tax_amount) ) as total_amount,
    ( sum(amount) + sum(tax_amount)) * 0.95 as discounted_amount  -- 5% discount applied
    FROM ztm_head_11559 AS a
    inner JOIN ztm_itm_11559 AS b
    ON a.client = b.client
    and a.document_number = b.document_number
    where a.client = session_context('CLIENT') and
    a.purchase_org = 'P001' and
    a.company_code = '4601'
    GROUP BY a.document_number , a.client, a.company_code , a.currency , a.purchase_org;
  endmethod.

ENDCLASS.

After that we must associate the table function CDS with the header CDS (parent Interface).

reference given below :-

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'cds header'
//@Metadata.ignorePropagatedAnnotations: true

@Metadata.allowExtensions: true

@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
}
define root view entity zi_head_11559 as select from ztm_head_11559
composition [ 1..* ] of zi_itm_11559 as _itm 
association [ 0..1 ] to zcds_info_cds_11559 as _tblfn
    on $projection.DocumentNumber = _tblfn.document_number

{
    key document_number as DocumentNumber,
    status as Status,
    company_code as CompanyCode,
    purchase_org as PurchaseOrg,
    currency as Currency,
    supplier_number as SupplierNumber,
    changed_at as ChangedAt,
    created_at as CreatedAt,
    created_by as CreatedBy,
    last_localchange as LastLocalchange ,
    
    //composition
    
    _itm,
    _tblfn
}

Now add the fields (total_amount and discounted_amount ) in the projection header or header consumption view. References are  given below:

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Projection Header'
//@Metadata.ignorePropagatedAnnotations: true
@Metadata.allowExtensions: true
define root view entity zprj_head_11559 provider contract transactional_query as projection on zi_head_11559
{
    key DocumentNumber,
    Status,
    CompanyCode,
    PurchaseOrg,
    Currency,
   @ObjectModel.virtualElementCalculatedBy : 'ABAP:ZCL_CONCATENATE'
   @EndUserText.label: 'Purchase Order Key'
   virtual purchkey : abap.char( 20 ),
    SupplierNumber,
    ChangedAt,
    CreatedAt,
    CreatedBy,
    LastLocalchange,
    
    @EndUserText.label: 'Total Amount'
    _tblfn.total_amount as Total_Amt,
    
    @EndUserText.label: 'Discount Amount'
    _tblfn.discounted_amount as Dicount_Amount,
    
    _itm : redirected to composition child zprj_itm_11559
}

Then Add the new fields (total_amount and discounted_amount )  in the Header metadata.

@ui: {
  identification: [{ position: 21, importance: #HIGH }],
  lineItem: [{ position: 21, importance: #HIGH, label: 'Total Amount' }],
  selectionField: [{ position: 21 }]
  }
  Total_Amt;

  @ui: {
   identification: [{ position: 22, importance: #HIGH }],
   lineItem: [{ position: 22, importance: #HIGH, label: 'Discount Amt:' }],
   selectionField: [{ position: 22 }]
  }
  Dicount_Amount;

Refresh your service binding and preview your projection.

Here you can see the total amount of the all the line items and discounted amount.

OUTPUT :-

Recommended articles for your Reference:

https://community.sap.com/t5/technology-blogs-by-members/abap-managed-database-procedure/ba-p/135117...

help.sap.com/doc/saphelp_nw75/7.5.5/en-us/e5/529f75afbc43e7803b30346a56f963/frameset.html