Example

Two Fiori applications are required: One app is needed for maintaining an HTML email signature in a Database Table with placeholder for Name & Position, and the other app is intended to locate a specific signature for the logged-in user and replacing these placeholder.

define table zsignature {
  key client       : abap.clnt not null;
  key organisation : abap.char(8) not null;
  key pos          : abap.char(8) not null;
  key username     : xubname not null;
  signature        : abap.string(0);

}

A CDS Root View “Z_I_SIGNATURE” has been created for the database table, with a managed behavior implementation. No specific Code has been added to the managed Implementation Class.

In the next step, a Projection View named “Z_C_SIGNATURE_MAINTAIN” was created for the Interface View, which is used for maintain signatures. Additionally, a Metadata Extension with annotations for the Fiori application was added to this Projection View.

@EndUserText.label: 'Signature Maintain View'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@Metadata.allowExtensions: true
define root view entity Z_C_SIGNATURE_MAINTAIN 
 provider contract transactional_query
 as projection on Z_I_SIGNATURE
{
    key Organisation,
    key Pos,
    key Username,
    Signature
}

Afterwards, a service definition was created, along with an ODATA V2 service binding, from which an application was generated using the “List Report Page” template in the Business Application Studio. As there is no built-in editor for formatting HTML code in Fiori Elements standard, I replaced the “Signature” section with a custom XML fragment in the Business Application Studio. I achieved this by utilizing the Fiori Page Map.

In the XML fragment, I inserted a rich-text editor for editing the signature. This allows signatures to be formatted and saved in HTML format. With that, the initial application for creating and editing signatures was completed.

I thought to myself: Nice! I already have the Interface View, and if it’s possible to create a Projection View, I can directly adopt the fields. So I created a new Projection View called “Z_C_SIGNATURE_READ” and tried to use the annotation. Work’s like a charm! 

@EndUserText.label: 'Signature Read View'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.query.implementedBy: 'ABAP:ZCL_SIGNATURE_READ'
@Metadata.allowExtensions: true
define root view entity Z_C_SIGNATURE_READ
 provider contract transactional_query
 as projection on Z_I_SIGNATURE
{
    key Organisation,
    key Pos,
    key Username,
    Signature
}

Afterwards, I implemented the already familiar interface “IF_RAP_QUERY_PROVIDER” in the class “ZCL_SIGNATURE_READ,” which is known from the custom entity. There, I determined the data from the organizational model for the logged-in user through an evaluation path and replaced the placeholders.

METHOD if_rap_query_provider~select.
    DATA: lt_objects   TYPE objec_t,
          lt_signature TYPE TABLE OF z_c_signature_read.


    DATA(lo_paging) = io_request->get_paging( ).
    TRY.
        "Filter not needed, because we all calculate fields in Backend
        DATA(lo_filter) = io_request->get_filter( )->get_as_ranges( ).
      CATCH cx_rap_query_filter_no_range.
    ENDTRY.

    CALL FUNCTION 'HR_STRUCTURE_GET'
      EXPORTING
        root_plvar      = '01'
        root_otype      = 'US'
        root_objid      = sy-uname
        pathid          = 'US-BP-OO'
      IMPORTING
        result_objects  = lt_objects
      EXCEPTIONS
        plvar_not_found = 1
        root_not_found  = 2
        path_not_found  = 3
        internal_error  = 4
        OTHERS          = 5.

    DATA(ls_o) = VALUE #( lt_objects[ otype = 'O' ] OPTIONAL ).
    DATA(ls_s) = VALUE #( lt_objects[ otype = 'S' ] OPTIONAL ).
    DATA(ls_us) = VALUE #( lt_objects[ otype = 'US' ] OPTIONAL ).

    "First check if there is a Signature for specific User
    IF ls_us IS NOT INITIAL.
      SELECT SINGLE * FROM z_i_signature WHERE username = @ls_us-realo INTO @DATA(ls_signature).
    ENDIF.

    "Second check combination position & organisation
    IF ls_signature IS INITIAL AND ls_s IS NOT INITIAL AND ls_o IS NOT INITIAL.
      SELECT SINGLE * FROM z_i_signature WHERE pos = @ls_s-realo AND organisation = @ls_o-realo INTO @ls_signature.
    ENDIF.

    "Third one check for organisation
    IF ls_signature IS INITIAL AND ls_o IS NOT INITIAL.
      SELECT SINGLE * FROM z_i_signature WHERE organisation = @ls_o-realo INTO @ls_signature.
    ENDIF.

    ls_signature-Signature = replace( val = ls_signature-Signature sub = '${User}' with = ls_us-stext ).
    ls_signature-Signature = replace( val = ls_signature-Signature sub = '${Position}' with = ls_s-stext ).

    APPEND CORRESPONDING #( ls_signature ) TO lt_signature.

    io_response->set_total_number_of_records( 1 ).
    io_response->set_data( lt_signature ).
  ENDMETHOD.

At the newest S/4 HANA On Premise release level (2022), it is not possible yet to externalize annotations into a metadata extension for a custom entity. Since we are dealing with a CDS Root View Entity, annotations can be easily outsourced here as well.

@Metadata.layer: #CUSTOMER
annotate view Z_C_SIGNATURE_READ
    with 
{
    @UI.facet: [
           {
               label: 'Signature',
               id : 'Signature',
               purpose: #STANDARD,
               type : #IDENTIFICATION_REFERENCE,
               position: 10,
               targetQualifier: 'Signature'
           }]
    @EndUserText.label: 'Organisation (O)'
    @UI.selectionField: [{ position: 10 }]
    @UI.lineItem: [{ position: 10 }]
    Organisation;
    @EndUserText.label: 'Position (S)'
    @UI.selectionField: [{ position: 20 }]
    @UI.lineItem: [{ position: 20 }]
    Pos;
    @EndUserText.label: 'User (US)'
    @UI.selectionField: [{ position: 30 }]
    @UI.lineItem: [{ position: 30 }]
    Username;    
    @EndUserText.label: 'Signature'
    @UI.identification: [{ position: 10, qualifier: 'Signature' }]
    Signature;
    
}

Now, in a second application, I can display the formatted signature for the logged-in user.

Summary (TLDR)

I wasn’t aware previously that I could use an Unmanaged Query in a CDS Root View Entity, allowing me to override the GET method for web protocols. Almost all examples I came across regarding Unmanaged Queries were based on Custom Entities. This approach offers several advantages, but it always requires a database table as the foundation.

Here are the advantages that come to mind:

• For complex SQL queries, the result can be cumulated at the row level, and CRUD operations (if key values are not altered) can be implemented in a managed scenario.
• An interface/projection view construct is possible.
• A separate Unmanaged Query implementation can be created for each projection view or interface view.
• Metadata extensions can be created (which is currently not possible for a custom entity).
• You can use it for BAdI Definitions, so the customer can implement custom field calulations