EDUCAÇÃO E TECNOLOGIA 

Excel Upload using RAP: Part -1

lwrc

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 –

  1. Creating an OData Service using SAP RAP Model.
  2. Create a Fiori Element Application for the SAP RAP Model OData Service.
  3. 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

  1. SAP BTP ABAP Environment – Release 2111 | SAP Blogs
  2. Flight Scenario RAP Service Book
  3. 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%20Diagram%20PGMS

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 the Late numbering and Validation Method

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.uy

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 !!!

In the next blog post, we will be creating a Fiori Elements App for this RAP OData Service using Business application Studio.