Excel Upload using RAP: Part -1

Links to other blogs post in this series –

1. Excel Upload using RAP: Part – 1
2. Excel Upload using RAP: Part – 2
3. Excel Upload using RAP: Part – 3

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. ( Excel Upload using RAP: Part -2 | SAP Blogs )
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 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 !!!

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