Wednesday 17 April 2024

Upload Excel using SAP RAP Only

Develop an SAP RAP based App to upload excel (CSV) file just like you used to develop SE38 classical report.

We often get this requirement of developing a utility to create/update mass data for a BO such as Purchase Orders, Materials, Sales Orders etc. In S/4 HANA, the customer prefers to use SAP Fiori applications, rather than using the old classical reports. But can we develop a Fiori application to upload a file without involving using SAP UI5? Yes, we can.

Using the following annotations, we can attach any file to our BO in SAP RAP –

@Semantics.largeObject

The only constraint is that we would need a table holding the uploaded attachment large object. Let’s dive into the steps –

First, we will create database tables. We need to create two DB tables. The first table i.e. parent table will store the file attachment, and the second table i.e. child table will store the data from the uploaded file. In our scenario, we will be uploading and processing CSV file.

File table - ZSES_FILE_TABLE

@EndUserText.label : 'User File Table'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zses_file_table {
  key client            : abap.clnt not null;
  key end_user          : uname not null;
      status            : abap.char(1);
      attachment        : xstringval;
      mimetype          : abap.char(128);
      filename          : abap.char(128);
      local_created_by      : abp_creation_user;
      local_created_at      : abp_creation_tstmpl;
      local_last_changed_by : abp_locinst_lastchange_user;
      local_last_changed_at : abp_locinst_lastchange_tstmpl;
      last_changed_at       : abp_lastchange_tstmpl
}
 
Excel data table - ZSES_DB

@EndUserText.label : 'Excel Data'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #ALLOWED
define table zses_db {
  key mandt      : mandt not null;
  key end_user   : uname not null;
  key entrysheet : lblni not null;
  key ebeln      : ebeln not null;
  key ebelp      : ebelp not null;
  ext_number     : lblne1;
  begdate        : lzvon;
  enddate        : lzbis;
  @Semantics.quantity.unitOfMeasure : 'zses_db.base_uom'
  quantity       : mengev;
  base_uom       : meins;
  fin_entry      : final;
  error          : boolean;
  error_message  : char100;
}
 
Now, let's create Interface views on top of the tables we just created.

For parent interface view, we create ZI_SES_PARENT.

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Excel File Table'
define root view entity zi_ses_parent
  as select from    usr02           as _user
    left outer join zses_file_table as _ses_file on _user.bname = _ses_file.end_user
  composition [0..*] of zi_ses_excel_data as _ses_excel
{
  key _user.bname                                                                                              as end_user,
      _ses_file.status                                                                                         as status,

      cast( case when _ses_file.filename is initial and _ses_file.status is      initial then 'File Not Uploaded'
                 when _ses_file.filename is not initial and  _ses_file.status is initial  then 'File Uploaded'
                 when _ses_file.filename is initial then 'File Not Uploaded'
                 when  _ses_file.status is not initial then 'File Processed' else ' ' end as abap.char( 20 ) ) as FileStatus,
      cast( case when _ses_file.filename is initial and _ses_file.status is initial then '1'
                 when _ses_file.filename is not initial and  _ses_file.status is initial  then '2'
                 when _ses_file.filename is initial then '1'
                 when  _ses_file.status is not initial then '3' else ' ' end as abap.char( 1 ) )               as CriticalityStatus,
      cast( case when _ses_file.filename is not initial then ' ' else 'X' end as boolean preserving type  )    as HideExcel,
      @Semantics.largeObject:
      { mimeType: 'MimeType',
      fileName: 'Filename',
      acceptableMimeTypes: [ 'text/csv' ],
      contentDispositionPreference: #INLINE }  // This will store the File into our table 
      _ses_file.attachment                                                                                     as Attachment,
      @Semantics.mimeType: true
      _ses_file.mimetype                                                                                       as MimeType,
      _ses_file.filename                                                                                       as Filename,
      @Semantics.user.createdBy: true
      _ses_file.local_created_by                                                                               as Local_Created_By,
      @Semantics.systemDateTime.createdAt: true
      _ses_file.local_created_at                                                                               as Local_Created_At,
      @Semantics.user.lastChangedBy: true
      _ses_file.local_last_changed_by                                                                          as Local_Last_Changed_By,
      //local ETag field --> OData ETag
      @Semantics.systemDateTime.localInstanceLastChangedAt: true
      _ses_file.local_last_changed_at                                                                          as Local_Last_Changed_At,
      //total ETag field
      @Semantics.systemDateTime.lastChangedAt: true
      _ses_file.last_changed_at                                                                                as Last_Changed_At,

      _ses_excel
}
where
  _user.bname = $session.user

Looks complicated? Let me explain -

1. We select primary data from USR02 table and left outer join it with our parent table ZSES_FILE_TABLE, so that every user can use the app without worrying about locking. As this application serves as a utility for excel file upload, multiple users can use it simultaneously.

2. FileStatus field is introduced to decorate the application and show state to the user whether the file is uploaded, processed or not.

Then we create interface view of child entity as ZI_SES_EXCEL_DATA.

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'SES_excel_data'
@Metadata.allowExtensions: true 
define view entity zi_ses_excel_data
  as select from zses_db
  association to parent zi_ses_parent as _ses_file on $projection.end_user = _ses_file.end_user
{
  key end_user              as end_user,
  key zses_db.entrysheet   as Entrysheet,
  key zses_db.ebeln         as Ebeln,
  key zses_db.ebelp         as Ebelp,
      zses_db.ext_number    as Ext_Number,
      zses_db.begdate       as Begdate,
      zses_db.enddate       as Enddate,
      zses_db.quantity      as Quantity,
      zses_db.base_uom      as Base_Uom,
      zses_db.fin_entry     as Fin_Entry,
      zses_db.error         as Error,
      zses_db.error_message as Error_Message,

      _ses_file
}
 
Now, let's go for consumption views. These are pretty straightforward.

For parent, we create ZC_SES_PARENT.

@EndUserText.label: 'Consumption View for File'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@Metadata.allowExtensions: true
define root view entity zc_ses_parent
  provider contract transactional_query
  as projection on zi_ses_parent
{
  key end_user,
      @EndUserText.label: 'Processing Status'
      FileStatus as status,
      Attachment,
      MimeType,
      Filename,
      Local_Created_By,
      Local_Created_At,
      Local_Last_Changed_By,
      @EndUserText.label: 'Last Action On'
      Local_Last_Changed_At,
      Last_Changed_At,
      CriticalityStatus,
      HideExcel,
      
      /* Associations */
      _ses_excel : redirected to composition child zc_ses_excel
}
 
For child, we create ZC_SES_EXCEL.

@EndUserText.label: 'Consumption View for Ses Excel Data'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@Metadata.allowExtensions: true 
define view entity zc_ses_excel
  as projection on zi_ses_excel_data
{
  key end_user,
  key Entrysheet,
  key Ebeln,
  key Ebelp,
      Ext_Number,
      Begdate,
      Enddate,
      Quantity,
      Base_Uom,
      Fin_Entry,
      Error,
      Error_Message,
      /* Associations */
      _ses_file : redirected to parent zc_ses_parent
}
 
We then create Metadata Extensions for both of our consumption/projection views -

@Metadata.layer: #CORE
annotate entity zc_ses_parent with
{
  @UI.facet: [
      /* Header Fecets and Datapoints */
      { purpose: #HEADER,             id:'HDR_USER',        type: #DATAPOINT_REFERENCE,      position: 10, targetQualifier: 'end_user'                                             },
      { purpose: #HEADER,             id:'HDR_FILE',        type: #DATAPOINT_REFERENCE,      position: 20, targetQualifier: 'Local_Last_Changed_At'                                },
      { purpose: #HEADER,             id:'HDR_STATUS',      type: #DATAPOINT_REFERENCE,      position: 30, targetQualifier: 'status'                                               },
  //**----  Body facets
      { label: 'File Information',    id: 'Attachment',     type: #COLLECTION,               position: 10                                                                        },
      { label: 'Invoice Details',     id: 'Invoicedet',     type: #IDENTIFICATION_REFERENCE, position: 10,                             parentId: 'File',        purpose: #STANDARD },
      {                               id: 'Upload',         type: #FIELDGROUP_REFERENCE,     position: 20  ,targetQualifier: 'Upload', parentId: 'Attachment',  purpose: #STANDARD },

  //** --- Excel data Facet **
      { label: 'Excel Data',          id: 'Data',           type: #LINEITEM_REFERENCE,       position: 30,  targetElement: '_ses_excel', parentId: 'Attachment', purpose: #STANDARD } ]// , hidden: #(HideExcel) } ]


  @UI: { lineItem:       [ { position: 10, importance: #HIGH , label: 'Person Responsible'}  ] ,
         identification: [ { position: 10 , label: 'Person Responsible' } ],
                     //      { type: #FOR_ACTION, dataAction: 'uploadExcelData', label: 'Validate and Show' } ] ,
         dataPoint:        { title: 'Responsible Person', targetValueElement: 'end_user' } }
  end_user;
  @UI: { lineItem:       [ { position: 20, importance: #HIGH , label: 'Processing Status'} ] ,
         identification: [ { position: 20 , label: 'Processing Status' } ] ,
         dataPoint:        { title: 'Processing Status', targetValueElement: 'status' ,criticality: 'CriticalityStatus' ,criticalityRepresentation: #WITHOUT_ICON} }
  status;
  @UI: { fieldGroup:     [ { position: 50, qualifier: 'Upload' , label: 'Attachment'} ]}
  @UI: { identification: [ { position: 30 , label: 'File' } ] }
  Attachment;

  @UI.hidden: true
  MimeType;

  @UI.hidden: true
  Filename;
  @UI: { dataPoint:{ title: 'Last Action On', targetValueElement: 'Local_Last_Changed_At' } }
  Local_Last_Changed_At;

}

@Metadata.layer: #CORE
annotate entity zc_ses_excel with
{

  @UI.facet: [{
      id: 'SES',
      type: #FIELDGROUP_REFERENCE,
      purpose: #STANDARD,
      label: 'SES',
      targetQualifier: 'SESDetails'
   }]
  @UI.lineItem: [{ position: 1 }]
  end_user;
  @UI.lineItem: [{ position: 10 , label: 'SES Number', invocationGrouping: #ISOLATED },
  { type: #FOR_ACTION, dataAction: 'createSES', label: 'Process SES' }]
  @UI.fieldGroup: [{ position: 10 , label: 'SES Number', qualifier: 'SESDetails' }]
  Entrysheet;
  @UI.lineItem: [{ position: 20 , label: 'PO' }]
  @UI.fieldGroup: [{ position: 20 , label: 'PO', qualifier: 'SESDetails' }]
  Ebeln;
  @UI.lineItem: [{ position: 30 , label: 'PO Item' }]
  @UI.fieldGroup: [{ position: 30 , label: 'PO Item', qualifier: 'SESDetails' }]
  Ebelp;
  @UI.lineItem: [{ position: 40 , label: 'SES Name' }]
  @UI.fieldGroup: [{ position: 40 , label: 'SES Name', qualifier: 'SESDetails' }]
  Ext_Number;
  @UI.lineItem: [{ position: 50 , label: 'Valid From' }]
  @UI.fieldGroup: [{ position: 50 , label: 'Valid From', qualifier: 'SESDetails' }]
  Begdate;
  @UI.lineItem: [{ position: 60 , label: 'Valid To' }]
  @UI.fieldGroup: [{ position: 60 , label: 'Valid To', qualifier: 'SESDetails' }]
  Enddate;
  @UI.lineItem: [{ position: 70 , label: 'Quantity' }]
  @UI.fieldGroup: [{ position: 70 , label: 'Quantity', qualifier: 'SESDetails' }]
  Quantity;
  @UI.lineItem: [{ position: 80 , label: 'UOM' }]
  @UI.fieldGroup: [{ position: 80 , label: 'UOM', qualifier: 'SESDetails' }]
  Base_Uom;
  @UI.lineItem: [{ position: 90 , label: 'Final Entry' }]
  @UI.fieldGroup: [{ position: 90 , label: 'Final Entry', qualifier: 'SESDetails' }]
  Fin_Entry;
  @UI.lineItem: [{ position: 100 , label: 'Error' , hidden: true}]
  @UI.fieldGroup: [{ position: 100 , label: 'Error' , hidden: true}]
  Error;
  @UI.lineItem: [{ position: 110 , label: 'Error Message' }]
  @UI.fieldGroup: [{ position: 110 , label: 'Error Message', qualifier: 'SESDetails' }]
  Error_Message;
}

Now, we create behavior definition ZI_SES_PARENT -

managed implementation in class zbp_i_ses_parent unique;
strict ( 2 );
with draft;

define behavior for zi_ses_parent alias File
persistent table zses_file_table
lock master
total etag end_user
draft table zses_file_tabled
authorization master ( instance )
etag master end_user
{
  create;
  update;
  delete;
  // Logic to convert uploaded excel into internal table and save to the child entity is written here
  action ( features : instance ) uploadExcelData  result [1] $self;

  association _ses_excel { create; with draft; }
  // Logic to trigger action uploadExcelData
  determination fields on modify { field Filename ; }
  draft action Edit ;
  draft action Activate;
  draft action Discard;
  draft action Resume;
  draft determine action Prepare ;
}

define behavior for zi_ses_excel_data alias ExcelData
persistent table ZSES_DB
lock dependent by _ses_file
draft table zses_dbd
authorization dependent by _ses_file
etag master Begdate
{
  update;
  delete;
  field ( readonly ) end_user;
  association _ses_file { with draft; }
  // Logic to process the uploaded data from excel
  action createSES result [1] $self;
}

And the behavior projection ZC_SES_PARENT - 

projection;
strict ( 2 );
use draft;

define behavior for zc_ses_parent //alias <alias_name>
{
  use update;
  use delete;

  field ( readonly ) status , Local_Last_Changed_At ;
  use action Edit;
  use action Activate;
  use action Discard;
  use action Resume;
  use action Prepare;
  use association _ses_excel { create; with draft; }
}

define behavior for zc_ses_excel //alias <alias_name>
{
  use update;
  use delete;
  use association _ses_file { with draft; }
  use action createSES;
}
 
Now, in the behavior implementation class - ZBP_I_SES_PARENT, we have two important methods -

Determination method - 'Fields' - 

METHOD fields.

    SELECT @abap_true INTO @DATA(lv_valid) FROM zses_file_table UP TO 1 ROWS WHERE end_user = @SY-uname.
    ENDSELECT.

    IF lv_valid <> abap_true.
      INSERT zses_file_table FROM @( VALUE #( end_user = sy-uname ) ).
    ENDIF.

    MODIFY ENTITIES OF zi_ses_parent IN LOCAL MODE
    ENTITY file
    UPDATE FROM VALUE #( FOR key IN keys ( end_user        = key-end_user
                                           status          = ' ' " Accepted
                                           %control-status = if_abap_behv=>mk-on ) ).

    IF keys[ 1 ]-%is_draft = '01'.

      MODIFY ENTITIES OF zi_ses_parent IN LOCAL MODE
      ENTITY file
      EXECUTE uploadexceldata
      FROM CORRESPONDING #( keys ).
    ENDIF.
  ENDMETHOD.

Explanation:

1. We save an entry with our username to the parent table, so that the once an instance is created and it goes inside the draft, it finds an instance to edit.

2. We call the action uploadexceldata such that whenever a new file is uploaded, it converts the data and store it into the child table.

Action method 'uploadExcelData' -

    READ ENTITIES OF zi_ses_parent IN LOCAL MODE
      ENTITY file
      ALL FIELDS WITH
      CORRESPONDING #( keys )
      RESULT DATA(lt_inv).

    DATA(lv_attachment) = lt_inv[ 1 ]-attachment.

    DATA: rows          TYPE STANDARD TABLE OF string,
          content       TYPE string,
          conv          TYPE REF TO cl_abap_conv_in_ce,
          ls_excel_data TYPE zses_db,
          lt_excel_data TYPE STANDARD TABLE OF zses_db,
          lv_quantity   TYPE char10,
          lv_entrysheet TYPE ebeln.

    conv = cl_abap_conv_in_ce=>create( input = lv_attachment ).
    conv->read( IMPORTING data = content ).

    SPLIT content AT cl_abap_char_utilities=>cr_lf INTO TABLE rows.

    LOOP AT rows INTO DATA(ls_row).
      SPLIT ls_row AT ',' INTO ls_excel_data-entrysheet
                               ls_excel_data-ebeln
                               ls_excel_data-ebelp
                               ls_excel_data-ext_number
                               ls_excel_data-begdate
                               ls_excel_data-enddate
                               lv_quantity
                               "ls_attdata-BASE_UOM
                               ls_excel_data-fin_entry.

      ls_excel_data-entrysheet = lv_entrysheet = |{ ls_excel_data-entrysheet ALPHA = IN }|.
      ls_excel_data-ebeln      = |{ ls_excel_data-ebeln ALPHA = IN }|.
      ls_excel_data-ebelp      = |{ ls_excel_data-ebelp ALPHA = IN }|.
      ls_excel_data-quantity = CONV #( lv_quantity ).
      
      APPEND ls_excel_data TO lt_excel_data.
      CLEAR: ls_row, ls_excel_data.
ENDLOOP.

Explanation -

1. Read the excel file (CSV in our case) from the entity and append it to internal table LT_EXCEL_DATA.

2. Use EML to insert the data from LT_EXCEL_DATA to the child table. 

Please refer below code for the implementation class -

CLASS lhc_exceldata DEFINITION INHERITING FROM cl_abap_behavior_handler.

  PRIVATE SECTION.
    METHODS get_instance_authorizations FOR INSTANCE AUTHORIZATION
      IMPORTING keys REQUEST requested_authorizations FOR exceldata RESULT result.

    METHODS createses FOR MODIFY
      IMPORTING keys FOR ACTION exceldata~createses RESULT result.

ENDCLASS.

CLASS lhc_exceldata IMPLEMENTATION.

  METHOD get_instance_authorizations.
  ENDMETHOD.

  METHOD createses.

    READ ENTITIES OF zi_ses_parent IN LOCAL MODE
        ENTITY exceldata
        ALL FIELDS WITH
        CORRESPONDING #( keys )
        RESULT DATA(lt_data).

    DATA:
      ls_header_data TYPE i_serviceentrysheettp_2,
      ls_item_data   TYPE i_serviceentrysheetitemtp_2,

      lt_header_crt  TYPE TABLE FOR CREATE i_serviceentrysheettp_2\\serviceentrysheet,
      lt_item_cba    TYPE TABLE FOR CREATE i_serviceentrysheettp_2\\serviceentrysheet\_serviceentrysheetitem.

* Create SES with reference to a Service Purchase Order Item

*--- Prepare Header Data
    ls_header_data-serviceentrysheetname  = 'Demo SES'.
    ls_header_data-purchaseorder          = `4500000286`.
    ls_header_data-sesoriginobjecttype    = `EX`.

*--- Prepare Item Data
    ls_item_data-purchaseorderitem              = '00010'.
    ls_item_data-confirmedquantity              = '1'.
    ls_item_data-accountassignmentcategory      = 'K'.
    ls_item_data-serviceperformancedate         = '20230207'.
    ls_item_data-multipleacctassgmtdistribution = '0'.


*--- Prepare Payload
    APPEND INITIAL LINE TO lt_header_crt ASSIGNING FIELD-SYMBOL(<ls_hdr_crt>).
    <ls_hdr_crt> = CORRESPONDING #( ls_header_data CHANGING CONTROL ).
    <ls_hdr_crt>-%cid     = `HEADER_1` .

    APPEND INITIAL LINE TO lt_item_cba ASSIGNING FIELD-SYMBOL(<ls_itm_cba>).
    <ls_itm_cba>-%cid_ref = 'HEADER_1'.

    APPEND INITIAL LINE TO <ls_itm_cba>-%target ASSIGNING FIELD-SYMBOL(<item_data>).
    <item_data>       = CORRESPONDING #( ls_item_data CHANGING CONTROL ).
    <item_data>-%cid  = 'Item_1'.


    MODIFY ENTITIES OF i_serviceentrysheettp_2
      ENTITY serviceentrysheet
        CREATE
          FROM lt_header_crt
         CREATE BY \_serviceentrysheetitem
          FROM  lt_item_cba
      FAILED DATA(ls_failed_crt)
      REPORTED DATA(ls_reported_crt)
      MAPPED DATA(ls_mapped_crt).

  ENDMETHOD.

ENDCLASS.

CLASS lhc_file DEFINITION INHERITING FROM cl_abap_behavior_handler.
  PRIVATE SECTION.

    METHODS get_instance_authorizations FOR INSTANCE AUTHORIZATION
      IMPORTING keys REQUEST requested_authorizations FOR file RESULT result.

    METHODS uploadexceldata FOR MODIFY
      IMPORTING keys FOR ACTION file~uploadexceldata RESULT result.
    METHODS fields FOR DETERMINE ON MODIFY
      IMPORTING keys FOR file~fields.
    METHODS get_instance_features FOR INSTANCE FEATURES
      IMPORTING keys REQUEST requested_features FOR file RESULT result.

ENDCLASS.

CLASS lhc_file IMPLEMENTATION.

  METHOD get_instance_authorizations.
  ENDMETHOD.

  METHOD uploadexceldata.

** Check if there exist an entry with current logged in username in parent table
    SELECT @abap_true INTO @DATA(lv_valid) FROM zses_file_table UP TO 1 ROWS WHERE end_user = @SY-uname.
    ENDSELECT.
** Create one entry, if it does not exist
    IF lv_valid <> abap_true.
      INSERT zses_file_table FROM @( VALUE #( end_user = sy-uname ) ).
    ENDIF.
** Read the parent instance
    READ ENTITIES OF zi_ses_parent IN LOCAL MODE
      ENTITY file
      ALL FIELDS WITH
      CORRESPONDING #( keys )
      RESULT DATA(lt_inv).

** Get attachment value from the instance
    DATA(lv_attachment) = lt_inv[ 1 ]-attachment.

** Data declarations
    DATA: rows          TYPE STANDARD TABLE OF string,
          content       TYPE string,
          conv          TYPE REF TO cl_abap_conv_in_ce,
          ls_excel_data TYPE zses_db,
          lt_excel_data TYPE STANDARD TABLE OF zses_db,
          lv_quantity   TYPE char10,
          lv_entrysheet TYPE ebeln.

** Convert excel file with CSV format into internal table of type string 
    conv = cl_abap_conv_in_ce=>create( input = lv_attachment ).
    conv->read( IMPORTING data = content ).

** Split the string table to rows
    SPLIT content AT cl_abap_char_utilities=>cr_lf INTO TABLE rows.

** Process the rows and append to the internal table
    LOOP AT rows INTO DATA(ls_row).
      SPLIT ls_row AT ',' INTO ls_excel_data-entrysheet
                               ls_excel_data-ebeln
                               ls_excel_data-ebelp
                               ls_excel_data-ext_number
                               ls_excel_data-begdate
                               ls_excel_data-enddate
                               lv_quantity
                               "ls_attdata-BASE_UOM
                               ls_excel_data-fin_entry.

      ls_excel_data-entrysheet = lv_entrysheet = |{ ls_excel_data-entrysheet ALPHA = IN }|.
      ls_excel_data-ebeln      = |{ ls_excel_data-ebeln ALPHA = IN }|.
      ls_excel_data-ebelp      = |{ ls_excel_data-ebelp ALPHA = IN }|.
      ls_excel_data-quantity = CONV #( lv_quantity ).

      APPEND ls_excel_data TO lt_excel_data.

      CLEAR: ls_row, ls_excel_data.
    ENDLOOP.

** Delete duplicate records
    DELETE ADJACENT DUPLICATES FROM lt_excel_data.
    DELETE lt_excel_data WHERE ebeln IS INITIAL.

** Prepare the datatypes to store the data from internal table lt_excel_data to child entity through EML
    DATA lt_att_create TYPE TABLE FOR CREATE zi_ses_parent\_ses_excel.

    lt_att_create = VALUE #( (  %cid_ref  = keys[ 1 ]-%cid_ref
                                %is_draft = keys[ 1 ]-%is_draft
                                end_user  = keys[ 1 ]-end_user
                                %target   = VALUE #( FOR ls_data IN lt_excel_data ( %cid       = |{ ls_data-ebeln }{ ls_data-ebelp }|
                                                                                   %is_draft   = keys[ 1 ]-%is_draft
                                                                                   end_user    = sy-uname
                                                                                   entrysheet  = ls_data-entrysheet
                                                                                   ebeln       = ls_data-ebeln
                                                                                   ebelp       = ls_data-ebelp
                                                                                   ext_number  = ls_data-ext_number
                                                                                   begdate     = ls_data-begdate
                                                                                   enddate     = ls_data-enddate
                                                                                   quantity    = ls_data-quantity
                                                                                  " BASE_UOM    = ls_data-
                                                                                   fin_entry   = ls_data-fin_entry
                                                                                  %control = VALUE #( end_user    = if_abap_behv=>mk-on
                                                                                                      entrysheet  = if_abap_behv=>mk-on
                                                                                                      ebeln       = if_abap_behv=>mk-on
                                                                                                      ebelp       = if_abap_behv=>mk-on
                                                                                                      ext_number  = if_abap_behv=>mk-on
                                                                                                      begdate     = if_abap_behv=>mk-on
                                                                                                      enddate     = if_abap_behv=>mk-on
                                                                                                      quantity    = if_abap_behv=>mk-on
                                                                                                     " BASE_UOM    = ls_data-
                                                                                                      fin_entry   = if_abap_behv=>mk-on  ) ) ) ) ).
    READ ENTITIES OF zi_ses_parent IN LOCAL MODE
    ENTITY file
    BY \_ses_excel
    ALL FIELDS WITH
    CORRESPONDING #( keys )
    RESULT DATA(lt_excel).

** Delete already existing entries from child entity
    MODIFY ENTITIES OF zi_ses_parent IN LOCAL MODE
    ENTITY exceldata
    DELETE FROM VALUE #( FOR ls_excel IN lt_excel (  %is_draft = ls_excel-%is_draft
                                                     %key      = ls_excel-%key ) )
    MAPPED DATA(lt_mapped_delete)
    REPORTED DATA(lt_reported_delete)
    FAILED DATA(lt_failed_delete).

** Create the records from the new attached CSV file
    MODIFY ENTITIES OF zi_ses_parent IN LOCAL MODE
    ENTITY file
    CREATE BY \_ses_excel
    AUTO FILL CID
    WITH lt_att_create.


    APPEND VALUE #( %tky = lt_inv[ 1 ]-%tky ) TO mapped-file.
    APPEND VALUE #( %tky = lt_inv[ 1 ]-%tky
                    %msg = new_message_with_text( severity = if_abap_behv_message=>severity-success
                                                  text = 'Excel Data Uploaded' )
                   ) TO reported-file.

    MODIFY ENTITIES OF zi_ses_parent IN LOCAL MODE
    ENTITY file
    UPDATE FROM VALUE #( ( %is_draft = keys[ 1 ]-%is_draft
                           end_user  = sy-uname
                           status     =  'P'
                          " %data     = VALUE #( status = 'P' )
                           %control  = VALUE #( status = if_abap_behv=>mk-on ) ) )
    MAPPED DATA(lt_mapped_update)
    REPORTED DATA(lt_reported_update)
    FAILED DATA(lt_failed_update).

    READ ENTITIES OF zi_ses_parent IN LOCAL MODE
    ENTITY file
    ALL FIELDS WITH CORRESPONDING #( keys )
    RESULT DATA(lt_file_status).

    MODIFY ENTITIES OF zi_ses_parent IN LOCAL MODE
    ENTITY file
    UPDATE FROM VALUE #( FOR ls_file_status IN lt_file_status ( %is_draft = ls_file_status-%is_draft
                                                                %tky      = ls_file_status-%tky
                                                                %data     = VALUE #( status = 'C'  )
                                                                %control  = VALUE #( status = if_abap_behv=>mk-on )
                                                     ) ).

    READ ENTITIES OF zi_ses_parent IN LOCAL MODE
       ENTITY file
       ALL FIELDS WITH
       CORRESPONDING #( keys )
       RESULT DATA(lt_file).



    result = VALUE #( FOR ls_file IN lt_file ( %tky   = ls_file-%tky

                                               %param = ls_file ) ).

  ENDMETHOD.

  METHOD fields.

    SELECT @abap_true INTO @DATA(lv_valid) FROM zses_file_table UP TO 1 ROWS WHERE end_user = @SY-uname.
    ENDSELECT.

    IF lv_valid <> abap_true.
      INSERT zses_file_table FROM @( VALUE #( end_user = sy-uname ) ).
    ENDIF.

    MODIFY ENTITIES OF zi_ses_parent IN LOCAL MODE
    ENTITY file
    UPDATE FROM VALUE #( FOR key IN keys ( end_user        = key-end_user
                                           status          = ' ' " Accepted
                                           %control-status = if_abap_behv=>mk-on ) ).

    IF keys[ 1 ]-%is_draft = '01'.

      MODIFY ENTITIES OF zi_ses_parent IN LOCAL MODE
      ENTITY file
      EXECUTE uploadexceldata
      FROM CORRESPONDING #( keys ).
    ENDIF.
  ENDMETHOD.


  METHOD get_instance_features.

    READ ENTITIES OF zi_ses_parent IN LOCAL MODE
           ENTITY file
             FIELDS ( end_user )
                WITH CORRESPONDING #( keys )
              RESULT DATA(lt_file).


    result = VALUE #( FOR ls_file IN lt_file ( %key = ls_file-%key
                                               %is_draft = ls_file-%is_draft
                                               %features-%action-uploadexceldata = COND #( WHEN ls_file-%is_draft = '00'
                                                                                           THEN if_abap_behv=>fc-f-read_only
                                                                                           ELSE if_abap_behv=>fc-f-unrestricted ) ) ).

  ENDMETHOD.

ENDCLASS.

After this, create service definition, expose both the parent and child consumption views, create a service binding and see the wonders.

The list report page would look like - 

Upload Excel using SAP RAP Only

Since, we used where clause to filter the parent records based on the current logged-in user in the interface view ZI_SES_PARENT, we should see only 1 record here. You can also skip the list report page in SAP BAS and deploy the app which directly opens the object page. 

When navigate to the object page and click on edit.

Upload Excel using SAP RAP Only

Once saved, we should see the records -

Upload Excel using SAP RAP Only

You can write the logic to process these records in an instance action for child entity, just like we have an action defined as 'Process SES'.

With this process, you can upload CSV file and process the records. You can try changing the logic to process XSLX file as well.

No comments:

Post a Comment