Consume table function in CDS view Part- 6

Let’s try to resolve one real issue now. What we want to achieve is: in CRM we need a CDS view which returns the service order guid together with its Sold-to Party information, “Title” ( Mr. ) and “Name” ( blGMOUTH ).

The title and Name information are stored on table BUT000, while Service order transactional information is maintained in table CRMD_PARTNER, which has a field PARTNER_NO ( CHAR32 ) linking to table BUT000’s PARTNER_GUID ( RAW16 ).

It is not allowed to do join on these two fields since their data type are not equal. 

As suggested in the Correction Answer, this issue could be resolved by using CDS Table Function. Here below are the detail steps.

1. Create a new table function

@ClientDependent: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

define table function ztf_BP_DETAIL

  with parameters @Environment.systemField: #CLIENT

                  clnt:abap.clnt

  returns { client:s_mandt;

            partner_guid:BU_PARTNER_GUID;

            partset_guid:CRMT_OBJECT_GUID;

            partner_no: CRMT_PARTNER_NO;

            bp_guid: BU_PARTNER_GUID;

            title:AD_TITLE;

            name: BU_NAME1TX;

          }

  implemented by method

    zcl_amdp_bp_detail=>crmd_partner_but000;

With keyword “with parameters”, the client parameters is defined which works as the importing parameters for the ABAP class method zcl_amdp_bp_detail=>crmd_partner_but000. The keywords “returns” defines available fields which could be consumed by other CDS entities.

2. Create a new AMDP implementation

Create a new ABAP class zcl_amdp_bp_detail by copying the following source code:

CLASS zcl_amdp_bp_detail DEFINITION

  PUBLIC

  FINAL

  CREATE PUBLIC .

  PUBLIC SECTION.

  INTERFACES if_amdp_marker_hdb.

  CLASS-METHODS crmd_partner_but000 FOR TABLE FUNCTION ztf_bp_Detail.

  PROTECTED SECTION.

  PRIVATE SECTION.

ENDCLASS.

CLASS zcl_amdp_bp_detail IMPLEMENTATION.

METHOD crmd_partner_but000

        BY DATABASE FUNCTION FOR HDB

        LANGUAGE SQLSCRIPT

        OPTIONS READ-ONLY

        USING crmd_partner but000.

    RETURN SELECT sc.client as client,

                  sc.partner_guid as partner_guid,

                  sc.guid as partset_guid,

                  sc.partner_no as partner_no,

                  sp.partner_guid as bp_guid,

                  sp.title as title,

                  sp.name1_text as name

                  FROM crmd_partner AS sc

                    INNER JOIN but000 AS sp ON sc.client = sp.client AND

                                              sc.partner_no = sp.partner_guid

                    WHERE sc.client = :clnt AND

                          sc.partner_fct = '00000001'

                    ORDER BY sc.client;

  ENDMETHOD.

ENDCLASS.

Here in line 30 the two columns of CRMD_PARTNER and BUT000 are joined. The importing parameter is used in SQLScript source code by adding a “:” before the variable name. The hard code “00000001” means the constant value for partner function “Sold-to Party”.

3. Consume the created table function in CDS view

@AbapCatalog.sqlViewName: 'zcpartner'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'partner detail'

define view Z_c_partner as select from crmd_orderadm_h

inner join crmd_link as _link on crmd_orderadm_h.guid = _link.guid_hi and _link.objtype_hi = '05'

  and _link.objtype_set = '07'

inner join ztf_bp_detail( clnt: '001') as _bp on _link.guid_set = _bp.partset_guid

{

  key crmd_orderadm_h.guid,

  --_link.objtype_hi as header_type,

  --_link.objtype_set as item_type,

  _bp.bp_guid,

  _bp.partner_no,

  _bp.name,

  case _bp.title

    when '0001' then 'Ms.'

    when '0002' then 'Mr.'

    when '0003' then 'Company'

    when '0004' then 'Mr and Mrs'

    else 'Unknown'

  end as title

}

Please note that the created table function in step1 could be directly consumed just as a normal CDS view, see example in line 8.

Since the table function declares client as parameter, so when consumed, I put the current client id 001 into it. The fields defined as the returning parameters in table function could be used in consuming view.

4. Test the whole solution

Click F8 on the view z_c_partner, check whether data previews as expected.

or you can also check against single data record, by clicking “SQL Console”, maintain the SQL and click Run:

The same test could also easily be done on ABAP side: