Technical Articles
Excel Upload using RAP: Part -1
Links to other blogs post in this series –
Introduction
One of the most common scenario in the SAP On Premise System is to develop a program in ABAP for uploading data to a custom database table using a Excel or CSV file. I was wondering how to achieve the same in SAP BTP ABAP Environment.
This will be a series of 3 blog post to develop a solution on uploading data to custom database table using SAP RAP Model using the Fiori Interface.
I will be following the below approach –
- Creating an OData Service using SAP RAP Model.
- Create a Fiori Element Application for the SAP RAP Model OData Service. ( Excel Upload using RAP: Part -2 | SAP Blogs )
- Extending the created Fiori Application with excel upload functionality.
Prerequisites
- Basic Concept on how to generate an OData Service using RAP
- For using Late Numbering in Managed RAP BO, SAP BTP ABAP Environment 2111 is required.
References
- SAP BTP ABAP Environment – Release 2111 | SAP Blogs
- Flight Scenario RAP Service Book
- ABAP Platform in SAP S/4HANA 2021 – Overview and Product Highlights
Business Scenario
The requirement to develop an Fiori application for a company whose primary business is to provide accommodation to a person (Paying Guest) in monthly Basis.
This application must have the following features –
- Generating the Building ID using Number Range using Late Numbering RAP Managed BO.
- Validation on the filed No of Rooms(n_rooms) of Building Entity – the value of it must lie between 1 and 10.
Basic ER Diagram for this Application is show below, which has three entities Building, Rooms and Beds –
ER Diagram
However in this blog post we will consider only Building Entity and will create an OData Service using Managed RAP BO Runtime
Lets start building the application
Step -1: Creating Database table – ZMN_BUILDINGS
@EndUserText.label : 'PG Building'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zmn_buildings {
key client : abap.clnt not null;
key building_id : abap.char(20) not null;
building_name : abap.char(50);
n_rooms : abap.int1;
address_line : abap.char(100);
city : abap.string(0);
state : abap.string(0);
country : abap.string(0);
created_by : syuname;
created_at : timestampl;
last_changed_by : syuname;
last_changed_at : timestampl;
}
Step – 2: Creating an Interface CDS View
The annotation @EndUserText.label is used for setting the field label in Fiori Application.
I will explain the use of field – ExcelRowNumber, in the upcoming blog post
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Buildings Interface View'
define root view entity zmn_i_buildings
as select from zmn_buildings
{
@EndUserText.label: 'Building ID'
key building_id as BuildingId,
@EndUserText.label: 'Building Name'
building_name as BuildingName,
@EndUserText.label: 'No of Rooms'
n_rooms as NRooms,
@EndUserText.label: 'Address Line'
address_line as AddressLine,
@EndUserText.label: 'City'
city as City,
@EndUserText.label: 'State'
state as State,
@EndUserText.label: 'Country'
country as Country,
@Semantics.user.createdBy: true
@EndUserText.label: 'Created By'
created_by as CreatedBy,
@Semantics.systemDateTime.createdAt: true
@EndUserText.label: 'Created At'
created_at as CreatedAt,
@Semantics.user.lastChangedBy: true
@EndUserText.label: 'Last Changed By'
last_changed_by as LastChangedBy,
@Semantics.systemDateTime.lastChangedAt: true
@EndUserText.label: 'Last Changed At'
last_changed_at as LastChangedAt,
0 as ExcelRowNumber
}
Step – 3: Creating a Consumption View
In this step, using the CDS annotation creating the basic list view report and an object page for the building entity
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Buildings Consumption View'
@UI.headerInfo:{
typeName: 'Building',
typeNamePlural: 'Buildings',
typeImageUrl: 'sap-icon://building',
title: {
type: #STANDARD,
value: 'BuildingName'
},
description.value: 'BuildingId'
}
define root view entity zmn_c_buildings
provider contract transactional_query
as projection on zmn_i_buildings
{
@UI.facet: [{ id: 'Building',
purpose: #STANDARD,
type: #IDENTIFICATION_REFERENCE,
label: 'Building',
position: 10
}, {
id: 'addr',position:20,
type: #FIELDGROUP_REFERENCE,
targetQualifier: 'Address',
label:'Address'
}, {
id:'log',position:30,
type: #FIELDGROUP_REFERENCE,
targetQualifier: 'ChangeLog',
label:'Change Log'
}]
@UI: { lineItem: [{ position: 10 }],
identification: [{ position: 10 }],
selectionField: [{ position: 10 }] }
key BuildingId,
@UI: { lineItem: [{ position: 20 }],
identification: [{ position: 20 }],
selectionField: [{ position: 20 }] }
BuildingName,
@UI: { lineItem: [{ position: 30 }],
identification: [{ position: 30 }] }
NRooms,
@UI.fieldGroup: [{ qualifier: 'Address', position: 10 }]
AddressLine,
@UI.fieldGroup: [{ qualifier: 'Address', position: 20 }]
City,
@UI.fieldGroup: [{ qualifier: 'Address', position: 30 }]
State,
@UI.fieldGroup: [{ qualifier: 'Address', position: 40 }]
Country,
@UI.fieldGroup: [{ qualifier: 'ChangeLog', position: 10 }]
CreatedBy,
@UI.fieldGroup: [{ qualifier: 'ChangeLog', position: 20 }]
CreatedAt,
@UI.fieldGroup: [{ qualifier: 'ChangeLog', position: 30 }]
LastChangedBy,
@UI.fieldGroup: [{ qualifier: 'ChangeLog', position: 40 }]
LastChangedAt,
@UI.hidden: true
ExcelRowNumber
}
Step – 4: Creating the Behavior Definition for the interface View zmn_i_buildings
This consists of
- Implementing the Late Number Concept for generating the Building ID, and
- The validation for the field n_rooms which was mentioned earlier.
managed implementation in class zbp_mn_i_buildings unique;
strict;
define behavior for zmn_i_buildings alias Building
persistent table zmn_buildings
lock master
authorization master ( instance )
//etag master <field_name>
{
create;
update;
delete;
field ( readonly ) BuildingId, CreatedBy, CreatedAt, LastChangedBy, LastChangedAt;
validation validateNRooms on save { create; field NRooms; }
mapping for zmn_buildings
{
BuildingId = building_id;
BuildingName = building_name;
NRooms = n_rooms;
AddressLine = address_line;
City = city;
State = state;
Country = country;
CreatedBy = created_by;
CreatedAt = created_at;
LastChangedBy = last_changed_by;
LastChangedAt = last_changed_at;
}
}
Step – 5: Creating the Behavior Definition projection on the Consumption View
projection;
strict;
define behavior for zmn_c_buildings alias Building
{
use create;
use update;
use delete;
}
Step – 6: Creating the Behavior Implementation for Late Numbering and Validation Method –
Global Class to implement the behavior specified in the behavior definition
CLASS zbp_mn_i_buildings DEFINITION PUBLIC ABSTRACT FINAL
FOR BEHAVIOR OF zmn_i_buildings.
ENDCLASS.
CLASS zbp_mn_i_buildings IMPLEMENTATION.
ENDCLASS.
Local handler classes are created in Local Types section of Global Class to implement the business logics.
CLASS lsc_zmn_i_buildings DEFINITION INHERITING FROM cl_abap_behavior_saver.
PROTECTED SECTION.
METHODS adjust_numbers REDEFINITION.
ENDCLASS.
CLASS lsc_zmn_i_buildings IMPLEMENTATION.
* this method using the late numbering concept to assign the building id for the entity
* using number range object
METHOD adjust_numbers.
DATA lv_bldg_num TYPE n LENGTH 5.
LOOP AT mapped-building ASSIGNING FIELD-SYMBOL(<map_building>) WHERE %key-BuildingId IS INITIAL .
TRY.
* using number range to generate the building id
cl_numberrange_runtime=>number_get(
EXPORTING
nr_range_nr = 'N1'
object = 'ZNR_BLD_NO'
quantity = 1
IMPORTING
number = DATA(number)
returncode = DATA(ret_code)
returned_quantity = DATA(ret_qty)
).
lv_bldg_num = number.
<map_building>-%key-BuildingId = |B{ lv_bldg_num }|.
CATCH cx_nr_object_not_found cx_number_ranges INTO DATA(lox_exp).
APPEND VALUE #(
%key = <map_building>-%key
%msg = lox_exp
) TO reported-building.
ENDTRY.
ENDLOOP.
ENDMETHOD.
ENDCLASS.
CLASS lhc_Building DEFINITION INHERITING FROM cl_abap_behavior_handler.
PRIVATE SECTION.
METHODS get_instance_authorizations FOR INSTANCE AUTHORIZATION
IMPORTING keys REQUEST requested_authorizations FOR Building RESULT result.
METHODS validateNRooms FOR VALIDATE ON SAVE
IMPORTING keys FOR Building~validateNRooms.
ENDCLASS.
CLASS lhc_Building IMPLEMENTATION.
METHOD get_instance_authorizations.
ENDMETHOD.
* method to validate the no of rooms field
METHOD validateNRooms.
* reading the building entites
READ ENTITIES OF zmn_i_buildings IN LOCAL MODE
ENTITY Building
ALL FIELDS
WITH CORRESPONDING #( keys )
RESULT DATA(buildings)
FAILED DATA(building_failed).
IF building_failed IS NOT INITIAL.
* if the above read fails then return the error message
failed = CORRESPONDING #( DEEP building_failed ).
RETURN.
ENDIF.
LOOP AT buildings ASSIGNING FIELD-SYMBOL(<building>).
IF NOT <building>-NRooms BETWEEN 1 AND 10.
* if bulk upload, then the excel row no field will not be initial,
* creating a message prefix for the output message
DATA(lv_msg) = |No of Rooms must be in Range 1 to 10|.
lv_msg = COND #( WHEN <building>-ExcelRowNumber IS INITIAL
THEN lv_msg
ELSE |Row { <building>-ExcelRowNumber }: { lv_msg }|
).
APPEND VALUE #(
%tky = <building>-%tky
) TO failed-building.
APPEND VALUE #(
%tky = <building>-%tky
%state_area = 'Validate_Rooms'
%msg = new_message_with_text(
severity = if_abap_behv_message=>severity-error
text = lv_msg
)
%element-NRooms = if_abap_behv=>mk-on
) TO reported-building.
ENDIF.
CLEAR lv_msg.
ENDLOOP.
ENDMETHOD.
ENDCLASS.
Step – 7: Creating Service Definition
@EndUserText.label: 'Building Service'
define service Zmn_building {
expose zmn_c_buildings as Buildings;
}
Step – 8: Creating Service Bindings. In this scenario we will be create a OData V2 Service. I’m skipping this step as I am assuming you know how to do that.
Application Preview
- List Report Page
- Building Object Page
- Validation
The below screen shows the testing for the validation message.
Conclusion
And there you have learned how to create a basic RAP OData service with Late Numbering & Validations Concepts. Along with this, you also had a chance to know about some of the CDS Annotation to design & build the Fiori Element Application (List Report & Object Page).
Thanks for reading this post, I would like to read your thoughts in the comments !!!
Hi Mani,
Thanks for the wonderful blog. I have few questions:
Awaiting for your next blog 🙂
Hi Venkatesh,
Thanks for your comment. 🙂
For the 1st question, the class zbp_mn_i_buildings is a special global class for implementing the behavior definition. The classes which is in step 6 are called as local handler class, are declared and implemented in the local types of the global class section. In RAP, the local handler classes are used to implement custom business logic. and here the custom logic is the entity validation.
Reference: Handler Classes - SAP Help Portal
For the 2nd question, the method adjust_numbers gets automatically called by the RAP framework after the succesful validation check and before the final save call of the object to generate the primary key for that entity.
Reference: Save Sequence Runtime - SAP Help Portal
For more clarity, I have also updated the step 6.
Hi @Namasivayam Mani
Could you pls post the Excel upload blog ASAP?
I have a requirement for that in my project and have been searching for such an example. Good that you have initiated. could you pls post the other parts ASAP?
You can use ABAP2XLSX for all the Excel needs in ABAP or use one of what must be hundreds of existing blog posts on this in ABAP.
The data uploaded from Excel would need to go somewhere, obviously. If it just goes into a Z table, then I guess you could use RAP managed scenario (there is a massive RAP document here). Otherwise unmanaged scenario with a BAPI or something would be the way to go.
All the separate parts of the process already have information and tools available. It's very nice of the author to describe an end-to-end scenario but I doubt there should be a work stoppage situation because next part has not been published yet. (It is published now though.)
Hello Mani,
I have below, a couple of requirements, can you please provide some insights into this if possible?
HI Namasivayam ,
Thank you so much for the blog . I have an observation that inside the consumption view you are using a lot of properties can't we segregate the meta data extension so that our code doesn't look clumsy. I mean we can define view as 'Meta Data Extension '
ex:- these are in next step after the consumption view .
@UI.facet: [{ id: 'Building',
purpose: #STANDARD,
type: #IDENTIFICATION_REFERENCE,
label: 'Building',
position: 10
}, {
id: 'addr',position:20,
type: #FIELDGROUP_REFERENCE,
targetQualifier: 'Address',
label:'Address'
}, {
id:'log',position:30,
type: #FIELDGROUP_REFERENCE,
targetQualifier: 'ChangeLog',
label:'Change Log'
}]
@UI: { lineItem: [{ position: 10 }],
identification: [{ position: 10 }],
selectionField: [{ position: 10 }] }
I have in legacy system a text file ( CSV) how do we download here using RAP ? Is it possible ujsing the managed approach .It's a green field implementation .
BR,
Somnath.
Sorry I am taking the blog seriously and we are supposed to when we are being provided to deliver .
Step-4 don't we require to use the key word late numbering in order to redefine the Adjust Numbers method .
BR,
Somnath.