Introduction
In the context of the Restful ABAP Programming Model, when you think about an Unmanaged Query, you're mostly thinking of a Custom Entity. In this blog post, I would like to introduce a method to implement an Unmanaged Query in combination with a Core Data Service (CDS) Root View Entity. I want to explain the advantages and disadvantages using a specific example.
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.
The search process for the signature should follow the organizational model from specific to more and more unspecific. If a signature is maintained for the logged-in user, it should be retrieved. If no signature is maintained for the user, the search should extend to the organizational and position levels. If, even with this combination, no signature is maintained, the signature from the organization level should be adopted.
Implementation
In my Example is a database table named "ZSIGNATURE" where signatures are stored based on the key fields "ORGANIZATION," "POS," and "USERNAME."
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.
For the application to read a specific signature per user, I could have worked with a table function for row retrieval and field calculation using a virtual field. To avoid wasting time with an uncertain outcome, I planned to use a custom entity. However, during setup, I noticed that the "
@ObjectModel.query.implementedBy" annotation can also be used for view entities.
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