In this article, we will create a CDS table function and see how can we implement it with an example.
So, let’s start:
1. Pre-requisites
If you are not aware about CDS Table Function check this article first: CDS TABLE Functions
We need to create 4 tables: Business partner data, Sales Order Header Data, Sales Item data and product/material data.
- Create Business partner table
@EndUserText.label : 'Business partners1'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zha_sa_bpa1 {
key client : abap.clnt not null;
key bp_id : zha_sa_dte_id not null;
bp_role : zha_sa_dte_bprole;
@EndUserText.label : 'Company Name'
company_name : abap.char(24);
@EndUserText.label : 'Street'
street : abap.string(256);
@EndUserText.label : 'Country'
country : abap.char(3);
@EndUserText.label : 'Region'
region : abap.char(5);
@EndUserText.label : 'City'
city : abap.string(256);
}Save and Activate the Business partner table
- Create Sales order header table
@EndUserText.label : 'Sales Order Header'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zha_sa_so_hdr {
key client : abap.clnt not null;
key order_id : zha_sa_dte_id not null;
order_no : int4;
@AbapCatalog.foreignKey.screenCheck : false
buyer : zha_sa_dte_id not null
with foreign key [0..*,1] zha_sa_bpa1
where bp_id = zha_sa_so_hdr.buyer;
@Semantics.amount.currencyCode : 'zha_sa_so_hdr.currency_code'
gross_amount : abap.curr(10,2);
currency_code : abap.cuky;
include zha_sa_admin_data;
}- Create Sales order item table
@EndUserText.label : 'Sales items data'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zha_sa_so_item {
key client : abap.clnt not null;
key item_id : zha_sa_dte_id not null;
order_id : zha_sa_dte_id not null;
@AbapCatalog.foreignKey.screenCheck : false
product : zha_sa_dte_id not null
with foreign key [0..*,1] zha_sa_product
where product_id = zha_sa_so_item.product;
@Semantics.quantity.unitOfMeasure : 'zha_sa_so_item.uom'
qty : abap.quan(5,0);
uom : abap.unit(3);
@Semantics.amount.currencyCode : 'zha_sa_so_item.currency'
amount : abap.curr(15,2);
currency : abap.cuky;
}- Create product table
@EndUserText.label : 'Product master table'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zha_sa_product {
key client : abap.clnt not null;
key product_id : zha_sa_dte_id not null;
name : abap.string(256);
category : abap.char(40);
@Semantics.amount.currencyCode : 'zha_sa_product.currency'
price : abap.curr(10,2);
currency : abap.cuky;
discount : abap.int4;
}We need to create a class to load data in these tables.
- Create a ABAP Class to load data in the created tables.
CLASS zcl_load_data3 DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_oo_adt_classrun.
PROTECTED SECTION.
METHODS: insert_data,
remove_data.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_load_data3 IMPLEMENTATION.
METHOD if_oo_adt_classrun~main.
remove_data( ).
insert_data( ).
out->write(
EXPORTING
data = 'processing is completed successfully!'
* name =
* RECEIVING
* output =
).
ENDMETHOD.
METHOD remove_data.
DELETE FROM : zha_sa_bpa1.
DELETE FROM : zha_sa_product.
DELETE FROM : zha_sa_so_hdr.
DELETE FROM : zha_sa_so_item.
ENDMETHOD.
METHOD insert_data.
DATA: lt_bpa TYPE TABLE OF zha_sa_bpa1,
lt_prod TYPE TABLE OF zha_sa_product,
lt_so TYPE TABLE OF zha_sa_so_hdr,
lt_so_i TYPE TABLE OF zha_sa_so_item,
o_rand TYPE REF TO cl_abap_random_int,
n TYPE i,
seed TYPE i,
lv_date TYPE timestamp,
lv_ord_id TYPE zha_sa_dte_id.
"Insert BP header data
APPEND VALUE #(
bp_id = '1000001'
bp_role = 'CA'
company_name = 'ABC'
street = 'IT'
country = 'Ind'
region = '100'
city = 'Bengaluru'
)
TO lt_bpa.
APPEND VALUE #(
bp_id = '1000002'
bp_role = 'CA'
company_name = 'ABD'
street = 'IT'
country = 'Ind'
region = '101'
city = 'Bengaluru'
)
TO lt_bpa.
APPEND VALUE #(
bp_id = '1000003'
bp_role = 'CA'
company_name = 'ABD'
street = 'IT'
country = 'Ind'
region = '102'
city = 'Bengaluru'
)
TO lt_bpa.
APPEND VALUE #(
bp_id = '1000004'
bp_role = 'CA'
company_name = 'ABD'
street = 'IT'
country = 'Ind'
region = '103'
city = 'Bengaluru'
)
TO lt_bpa.
APPEND VALUE #(
bp_id = '1000005'
bp_role = 'CA'
company_name = 'ABD'
street = 'IT'
country = 'Ind'
region = '104'
city = 'Bengaluru'
)
TO lt_bpa.
"Fill product data
APPEND VALUE #(
product_id = cl_uuid_factory=>create_system_uuid( )->create_uuid_c32( )
name = 'Blaster Extreme'
category = 'Speakers'
price = 1500
currency = 'INR'
discount = 3
)
TO lt_prod.
APPEND VALUE #(
product_id = cl_uuid_factory=>create_system_uuid( )->create_uuid_c32( )
name = 'Sound Booster'
category = 'Speakers'
price = 2500
currency = 'INR'
discount = 2
)
TO lt_prod.
APPEND VALUE #(
product_id = cl_uuid_factory=>create_system_uuid( )->create_uuid_c32( )
name = 'Smart Office'
category = 'Software'
price = 1540
currency = 'INR'
discount = 32
)
TO lt_prod.
APPEND VALUE #(
product_id = cl_uuid_factory=>create_system_uuid( )->create_uuid_c32( )
name = 'Smart Design'
category = 'Software'
price = 2400
currency = 'INR'
discount = 12
)
TO lt_prod.
APPEND VALUE #(
product_id = cl_uuid_factory=>create_system_uuid( )->create_uuid_c32( )
name = 'Transcend Carry pocket'
category = 'PCs'
price = 14000
currency = 'INR'
discount = 7
)
TO lt_prod.
APPEND VALUE #(
product_id = cl_uuid_factory=>create_system_uuid( )->create_uuid_c32( )
name = 'Gaming Monster Pro'
category = 'PCs'
price = 15500
currency = 'INR'
discount = 8
)
TO lt_prod.
"Fill SO header data
seed = cl_abap_random=>seed( ).
cl_abap_random_int=>create(
EXPORTING
seed = seed
min = 1
max = 7
RECEIVING
prng = o_rand
).
GET TIME STAMP FIELD lv_date.
DO 50 TIMES.
lv_ord_id = cl_uuid_factory=>create_system_uuid( )->create_uuid_c32( ).
n = o_rand->get_next( ).
READ TABLE lt_bpa INTO DATA(ls_bp) INDEX n.
APPEND VALUE #(
order_id = lv_ord_id
order_no = sy-index
buyer = ls_bp-bp_id
gross_amount = 10 * n
currency_code = 'EUR'
created_by = sy-uname
created_on = lv_date
changed_by = sy-uname
changed_on = lv_date
) TO lt_so.
"Fill item data
DO 2 TIMES.
READ TABLE lt_prod INTO DATA(ls_prod) INDEX n.
APPEND VALUE #(
item_id = cl_uuid_factory=>create_system_uuid( )->create_uuid_c32( )
order_id = lv_ord_id
product = ls_prod-product_id
qty = n
uom = 'EA'
amount = n * ls_prod-price
currency = ls_prod-currency
) TO lt_so_i.
ENDDO.
ENDDO.
INSERT zha_sa_bpa1 FROM TABLE @lt_bpa.
INSERT zha_sa_so_hdr FROM TABLE @lt_so.
INSERT zha_sa_product FROM TABLE @lt_prod.
INSERT zha_sa_so_item FROM TABLE @lt_so_i.
ENDMETHOD.
ENDCLASS.Save and Activate.
Now, execute load data class to fill Business Partner, Sales Header, Sales Item table and Product table.
2. Create CDS Table Function
@EndUserText.label: 'Table function for customer sales'
define table function zha_sa_table_func1
with parameters
@Environment.systemField: #CLIENT
p_clnt : abap.clnt
returns {
client : abap.clnt;
company_name : abap.char(24 );
total_sales : abap.curr(15,2);
currency_code : abap.cuky(5);
customer_rank : abap.int4;
}
implemented by method zcl_ha_sa_tablf=>get_total_sales;3. Implement CDS Table Function
Create class that we mentioned in “implemented by method” statement above.
Here we are implementing CDS Table function “zha_sa_table_func1″ by static method ” get_total_sales” of class zcl_ha_sa_tablf .
In method get_total_sales, we are calculating total sales based on business partner is and saler order id.
Here, we also used HANA function RANK( ) to get rank of the companies based on the total sales.
For example, if company “A” has maximum sales, then it’s Rank will be “1”.
CLASS zcl_ha_sa_tablf DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb. "To let system know that it is a amdp class
"Declare static method for table function
CLASS-METHODS get_total_sales FOR TABLE FUNCTION zha_sa_table_func1.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_ha_sa_tablf IMPLEMENTATION.
METHOD get_total_sales BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING zha_sa_bpa1 zha_sa_so_hdr zha_sa_so_item.
RETURN select
bpa.client,
bpa.company_name as company_name,
sum( itm.amount ) as total_sales,
itm.currency as currency_code,
rank ( ) over ( order by sum( itm.amount ) desc ) as customer_rank
from zha_sa_bpa1 as bpa
inner join zha_sa_so_hdr as hdr
on bpa.bp_id = hdr.buyer
inner join zha_sa_so_item as itm
on hdr.order_id = itm.order_id
group by bpa.client,
company_name,
itm.currency ;
endmethod.
ENDCLASS.4. Output
Now, execute the CDS Table Function to see the output.
Thanks for Visiting!
Sangeeta Singh
Leave a Reply