This is a test message to test the length of the message box.
Login
ABAP CDS Data Model
Created by Software-Heroes

CDS - Data model

355

In this article we want to introduce you to the data model that we will be working with in the future. You should be able to use it as well.

We already used the first elements from the new data model in a previous article. In this article we want to introduce the data model, the tables and the associated interface views in more detail. At the end you should also have the opportunity to create the data model and generate test data.

 

Tables

We set up a small model with three master data tables and two transaction data tables, the focus here is on the partner with whom we do business, the material table and the invoice that is sent. The data model therefore looks as follows.

 

 

In addition, the invoice has different items and a partner can get a discount on different materials. The tables are linked via the primary and secondary keys. To do this, we now create the corresponding tables via Eclipse.

 

Partner
@EndUserText.label : 'Partner Data'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zbs_dmo_partner {
  key client       : abap.clnt not null;
  key partner      : abap.char(10) not null;
  name             : abap.char(60);
  street           : abap.char(80);
  city             : abap.char(60);
  country          : land1;
  payment_currency : abap.cuky;
}

 

Material
@EndUserText.label : 'Material Data'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zbs_dmo_material {
  key client     : abap.clnt not null;
  key material   : abap.char(5) not null;
  name           : abap.char(25);
  description    : abap.char(150);
  @Semantics.quantity.unitOfMeasure : 'zbs_dmo_material.stock_unit'
  stock          : abap.quan(10,0);
  stock_unit     : abap.unit(3);
  @Semantics.amount.currencyCode : 'zbs_dmo_material.currency'
  price_per_unit : abap.curr(15,2);
  currency       : abap.cuky;
}

 

Discount
@EndUserText.label : 'Discount Data'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zbs_dmo_discount {
  key client   : abap.clnt not null;
  key partner  : abap.char(10) not null;
  key material : abap.char(5) not null;
  discount     : abap.dec(5,2);
}

 

Invoice
@EndUserText.label : 'Invoice Data'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zbs_dmo_invoice {
  key client   : abap.clnt not null;
  key document : abap.char(8) not null;
  doc_date     : abap.dats;
  doc_time     : abap.tims;
  partner      : abap.char(10);
}

 

Invoice position
@EndUserText.label : 'Invoice Position Data'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zbs_dmo_position {
  key client     : abap.clnt not null;
  key document   : abap.char(8) not null;
  key pos_number : abap.int2 not null;
  material       : abap.char(5);
  @Semantics.quantity.unitOfMeasure : 'zbs_dmo_material.stock_unit'
  quantity       : abap.quan(10,0);
  @Semantics.amount.currencyCode : 'zbs_dmo_position.currency'
  price          : abap.curr(15,2);
  currency       : abap.cuky;
}

 

Hint: As you have probably noticed, we also use annotations when creating tables to link currencies and quantity fields.

 

Core Data Services

Every clean data model has a basis with Interface or Basic Views, on which the further layers of the model are built. We also have the option of standardizing the field names and making them unique in the entire data model. The data model looks like this:

 

The fields in the data model are now unique, making it easier to merge the data into new views. Each field name thus describes a unique task. The CDS views now look like this.

 

Partner
@AbapCatalog.sqlViewName: 'ZBSIDMOPARTNER'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Interface for Partner'
define view ZBS_I_DmoPartner
  as select from zbs_dmo_partner
{
  key partner          as PartnerNumber,
      name             as PartnerName,
      street           as Street,
      city             as City,
      country          as Country,
      payment_currency as PaymentCurrency
}

 

Material
@AbapCatalog.sqlViewName: 'ZBSIDMOMATERIAL'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Interface for Material'
define view ZBS_I_DmoMaterial
  as select from zbs_dmo_material
{
  key material       as MaterialNumber,
      name           as MaterialName,
      description    as MaterialDescription,
      stock          as Stock,
      stock_unit     as StockUnit,
      price_per_unit as PricePerUnit,
      currency       as Currency
}

 

Discount
@AbapCatalog.sqlViewName: 'ZBSIDMODISCOUNT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Interface for Discount'
define view ZBS_I_DmoDiscount
  as select from zbs_dmo_discount
{
  key partner  as PartnerNumber,
  key material as MaterialNumber,
      discount as DiscountValue
}

 

Invoice
@AbapCatalog.sqlViewName: 'ZBSIDMOINVOICE'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Interface for Invoice'
define view ZBS_I_DmoInvoice
  as select from zbs_dmo_invoice
{
  key document as DocumentNumber,
      doc_date as DocumentDate,
      doc_time as DocumentTime,
      partner  as PartnerNumber
}

 

Invoice position
@AbapCatalog.sqlViewName: 'ZBSIDMOPOSITION'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Interface for Position'
define view ZBS_I_DmoPosition
  as select from zbs_dmo_position
{
  key document   as DocumentNumber,
  key pos_number as PositionNumber,
      material   as MaterialNumber,
      quantity   as PositionQuantity,
      price      as PositionPrice,
      currency   as PositionCurrency
}

 

Test data

Nothing beats good test data, or just a certain amount of test data to work with as a developer. For this we write a small class with which the data can be easily generated. For the receipts and items, you can also configure the number and variance of the items. You can find all the sample code here:

CLASS zcl_bs_demo_dummy_data DEFINITION PUBLIC FINAL CREATE PUBLIC.
  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

    CONSTANTS:
      c_error                     TYPE zbs_dmo_position-price VALUE '37707',
      c_number_of_invoices        TYPE i VALUE 300,
      c_days_back_from_today      TYPE i VALUE 365,
      c_max_number_of_positions   TYPE i VALUE 3,
      c_max_quantity_per_position TYPE i VALUE 5.

  PROTECTED SECTION.
  PRIVATE SECTION.
    DATA:
      mt_partner         TYPE STANDARD TABLE OF zbs_dmo_partner,
      mt_material        TYPE STANDARD TABLE OF zbs_dmo_material,
      mt_discount        TYPE STANDARD TABLE OF zbs_dmo_discount,
      mt_head            TYPE STANDARD TABLE OF zbs_dmo_invoice,
      mt_position        TYPE STANDARD TABLE OF zbs_dmo_position,

      mo_random_partner  TYPE REF TO zcl_bs_demo_random,
      mo_random_date     TYPE REF TO zcl_bs_demo_random,
      mo_random_position TYPE REF TO zcl_bs_demo_random,
      mo_random_material TYPE REF TO zcl_bs_demo_random,
      mo_random_quantity TYPE REF TO zcl_bs_demo_random.

    METHODS:
      create_partner,

      create_material,

      create_discount,

      create_invoice
        IMPORTING
          id_count TYPE i,

      create_head
        RETURNING
          VALUE(rs_result) TYPE zbs_dmo_invoice,

      create_positions
        IMPORTING
          is_head TYPE zbs_dmo_invoice.
ENDCLASS.


CLASS zcl_bs_demo_dummy_data IMPLEMENTATION.
  METHOD if_oo_adt_classrun~main.
    create_partner( ).
    out->write( |Partner: { lines( mt_partner ) }| ).

    create_material( ).
    out->write( |Material: { lines( mt_material ) }| ).

    create_discount( ).
    out->write( |Discount: { lines( mt_discount ) }| ).

    create_invoice( c_number_of_invoices ).
    out->write( |Invoice: { lines( mt_head ) }| ).
    out->write( |Position: { lines( mt_position ) }| ).
  ENDMETHOD.


  METHOD create_partner.
    mt_partner = VALUE #(
      ( partner = '1000000000' name = 'SAP' street = 'Demo Street 15' city = 'Walldorf' country = 'DE' payment_currency = 'EUR' )
      ( partner = '1000000001' name = 'Microsoft' street = 'Demo Street 24' city = 'Redmond' country = 'US' payment_currency = 'USD' )
      ( partner = '1000000002' name = 'Meta' street = 'Fox Street 1' city = 'Menlo Park' country = 'US' payment_currency = 'USD' )
      ( partner = '1000000003' name = 'Alibaba' street = 'Alley 15' city = 'Hangzhou' country = 'CN' payment_currency = 'CNY' )
      ( partner = '1000000004' name = 'BMW' street = 'Main Avenue 200' city = 'Munich' country = 'DE' payment_currency = 'EUR' )
      ( partner = '1000000005' name = 'Nestle' street = 'Village Alley 14' city = 'Vevey' country = 'CH' payment_currency = 'CHF' )
      ( partner = '1000000006' name = 'Gazprom' street = 'Peace Avenue 1' city = 'Sankt Petersburg' country = 'RU' payment_currency = 'RUB' )
    ).

    DELETE FROM zbs_dmo_partner.
    INSERT zbs_dmo_partner FROM TABLE @mt_partner.
  ENDMETHOD.


  METHOD create_material.
    mt_material = VALUE #(
      ( material = 'F0001'
        name = 'Peanuts'
        description = 'Roasted Peanuts from US'
        stock = '900'
        stock_unit = 'ST'
        price_per_unit = '2.50'
        currency = 'USD' )
      ( material = 'F0002'
        name = 'Rice'
        description = 'Big bag rice from china'
        stock = '120'
        stock_unit = 'BAG'
        price_per_unit = '12.00'
        currency = 'USD' )
      ( material = 'F0003'
        name = 'Eggs'
        description = 'Eggs from happy german chickens'
        stock = '550'
        stock_unit = 'PAK'
        price_per_unit = '3.15'
        currency = 'EUR' )
      ( material = 'H0001'
        name = 'USB Stick 128 GB'
        description = 'USB Stick with security features'
        stock = '30'
        stock_unit = 'ST'
        price_per_unit = '49.99'
        currency = 'EUR' )
      ( material = 'H0002'
        name = 'OLED Display 34"'
        description = 'Big and wide display with HDMI and dsiplay port'
        stock = '18'
        stock_unit = 'ST'
        price_per_unit = '440.00'
        currency = 'USD' )
      ( material = 'R0001'
        name = 'Gas'
        description = 'Gas from sibiria'
        stock = '50000'
        stock_unit = 'MMQ'
        price_per_unit = '1560.00'
        currency = 'RUB' )
    ).

    DELETE FROM zbs_dmo_material.
    INSERT zbs_dmo_material FROM TABLE @mt_material.
  ENDMETHOD.


  METHOD create_discount.
    mt_discount = VALUE #(
      ( partner = '1000000000' material = 'F0003' discount = '10.00' )
      ( partner = '1000000001' material = 'F0001' discount = '15.00' )
      ( partner = '1000000001' material = 'H0002' discount = '3.50' )
      ( partner = '1000000006' material = 'R0001' discount = '7.50' )
    ).

    DELETE FROM zbs_dmo_discount.
    INSERT zbs_dmo_discount FROM TABLE @mt_discount.
  ENDMETHOD.


  METHOD create_invoice.
    DO id_count TIMES.
      DATA(ls_head) = create_head( ).
      create_positions( ls_head ).
    ENDDO.

    DELETE FROM zbs_dmo_invoice.
    INSERT zbs_dmo_invoice FROM TABLE @mt_head.
    DELETE FROM zbs_dmo_position.
    INSERT zbs_dmo_position FROM TABLE @mt_position.
  ENDMETHOD.


  METHOD create_head.
    DATA:
      ld_document TYPE n LENGTH 8 VALUE 30000000.

    IF mo_random_partner IS INITIAL.
      mo_random_partner = NEW #( id_min = 1 id_max = lines( mt_partner ) ).
      mo_random_date = NEW #( id_min = 1 id_max = c_days_back_from_today ).
    ENDIF.

    rs_result = VALUE #(
      document = ld_document + lines( mt_head )
      doc_date = CONV d( cl_abap_context_info=>get_system_date( ) - mo_random_date->rand( ) )
      doc_time = cl_abap_context_info=>get_system_time( )
      partner = mt_partner[ mo_random_partner->rand( ) ]-partner
    ).

    INSERT rs_result INTO TABLE mt_head.
  ENDMETHOD.


  METHOD create_positions.
    IF mo_random_position IS INITIAL.
      mo_random_position = NEW #( id_min = 1 id_max = c_max_number_of_positions ).
      mo_random_material = NEW #( id_min = 1 id_max = lines( mt_material ) ).
      mo_random_quantity = NEW #( id_min = 1 id_max = c_max_quantity_per_position ).
    ENDIF.

    DO mo_random_position->rand( ) TIMES.
      DATA(ld_index) = sy-index.
      DATA(ls_material) = mt_material[ mo_random_material->rand( ) ].
      DATA(ld_quantity) = mo_random_quantity->rand( ).

      TRY.
          DATA(ld_discount) = mt_discount[ partner = is_head-partner material = ls_material-material ]-discount.
        CATCH cx_sy_itab_line_not_found.
          ld_discount = 0.
      ENDTRY.

      DATA(ls_position) = VALUE zbs_dmo_position(
        document = is_head-document
        pos_number = ld_index
        material = ls_material-material
        quantity = ld_quantity
        price = ( ld_quantity * ls_material-price_per_unit ) * ( 1 - ld_discount / 100 )
        currency = mt_partner[ partner = is_head-partner ]-payment_currency
      ).

      TRY.
          SELECT SINGLE FROM zbs_dmo_discount
            FIELDS
              currency_conversion(
                amount = @ls_position-price,
                source_currency = @ls_material-currency,
                target_currency = @ls_position-currency,
                exchange_rate_date = @is_head-doc_date,
                round = @abap_true
              ) AS price
            INTO @ls_position-price.

        CATCH cx_sy_open_sql_db.
          ls_position-price = c_error.
      ENDTRY.

      INSERT ls_position INTO TABLE mt_position.
    ENDDO.
  ENDMETHOD.
ENDCLASS.

 

When creating it, we rely on a mixture of fixed master data and random positions, using the random number generator from another article. This randomly derives the number of items, partners, and units sold, giving us a far-reaching result set. The coding has no special features, except for one case:

TRY.
    SELECT SINGLE FROM zbs_dmo_discount
      FIELDS
        currency_conversion(
          amount = @ls_position-price,
          source_currency = @ls_material-currency,
          target_currency = @ls_position-currency,
          exchange_rate_date = @is_head-doc_date,
          round = @abap_true
        ) AS price
      INTO @ls_position-price.

  CATCH cx_sy_open_sql_db.
    ls_position-price = c_error.
ENDTRY.

 

Here we use a "dummy" select to do the currency conversion without a function module and use the standard ABAP-SQL function for it. In the event of an error that the conversion does not work, an error code is transferred to the field.

 

GitHub

All resources related to the Core Data Service series will be made available on GitHub this time for your convenience. You can use the link to find our repository and load the model into your system. The classes and the model are also Cloud Ready for use in the BTP. However, the class for the random number generator is not included in this package.

 

Conclusion

For a decent test you need a corresponding data model and also test data. In this article, we wanted to bring you closer to the model and provide you with the opportunity to use it.


Included topics:
CDSCore Data ServiceData model
Comments (0)



And further ...

Are you satisfied with the content of the article? We post new content in the ABAP area every Friday and irregularly in all other areas. Take a look at our tools and apps, we provide them free of charge.


ABAP Tools - Work with Eclipse (CDS Analysis)

Category - ABAP

In the complex world of CDS views, it is important to have the right tool at hand to ensure an analysis of the structures and data sources.

08/25/2023

CDS - View Entity

Category - ABAP

In this article you will learn more about the new Core Data Service entities, what they bring and what makes them different from the old views.

07/29/2022

CDS - Learnings

Category - ABAP

In this article we summarize the learned content and show you the way for what you will need the CDS Views in the future.

06/10/2022

CDS - Virtual fields

Category - ABAP

This article is about virtual fields in Core Data Services and how you can subsequently deliver such complex data.

06/03/2022

CDS - Authority check

Category - ABAP

How and where are the permissions for a Core Data Service delimited? Check out this article for the details.

05/20/2022