ABAP CDS Views: Understanding and Using Core Data Services

@AbapCatalog.sqlViewName: 'ZSQL_VIEW_NAME'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'View Description'

define view Z_CDS_VIEW_NAME
  as select from <data_source>
  {
    <field_list>
  }

@AbapCatalog.sqlViewName: 'ZSQLCUSTOMERS'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Customer Master Data'

define view Z_I_Customers
  as select from kna1
{
  key kunnr as CustomerId,
      name1 as CustomerName,
      ort01 as City,
      land1 as Country,
      erdat as CreatedDate
}

" Use CDS View like a table
SELECT * FROM z_i_customers
  INTO TABLE @DATA(lt_customers)
  WHERE country = 'DE'.

LOOP AT lt_customers INTO DATA(ls_customer).
  WRITE: / ls_customer-customerid, ls_customer-customername.
ENDLOOP.

" With inline declaration
SELECT customerid, customername, city
  FROM z_i_customers
  WHERE country = 'DE'
  INTO TABLE @DATA(lt_german_customers).

@AbapCatalog.sqlViewName: 'ZSQLORDERCUST'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Orders with Customer Data'

define view Z_I_OrdersWithCustomer
  as select from vbak as order
    inner join kna1 as customer
      on order.kunnr = customer.kunnr
{
  key order.vbeln   as OrderNumber,
      order.erdat   as OrderDate,
      order.netwr   as NetValue,
      order.waerk   as Currency,
      customer.kunnr as CustomerId,
      customer.name1 as CustomerName,
      customer.ort01 as CustomerCity
}

define view Z_I_CustomersWithOrders
  as select from kna1 as customer
    left outer join vbak as order
      on customer.kunnr = order.kunnr
{
  key customer.kunnr as CustomerId,
      customer.name1 as CustomerName,
      order.vbeln    as OrderNumber,
      order.netwr    as OrderValue
}

@AbapCatalog.sqlViewName: 'ZSQLORDERCALC'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Orders with Calculations'

define view Z_I_OrderCalculations
  as select from vbak
{
  key vbeln as OrderNumber,
      netwr as NetValue,
      waerk as Currency,

      // Calculations
      netwr * 1.19 as GrossValue,

      // Conditions with CASE
      case
        when netwr >= 10000 then 'HIGH'
        when netwr >= 1000  then 'MEDIUM'
        else 'LOW'
      end as ValueCategory,

      // Date functions
      dats_days_between( erdat, $session.system_date ) as DaysSinceOrder
}

@AbapCatalog.sqlViewName: 'ZSQLCUSTTOTAL'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Customer Revenue Aggregated'

define view Z_I_CustomerTotals
  as select from vbak
{
  key kunnr as CustomerId,

      // Aggregate functions
      sum( netwr )   as TotalOrderValue,
      count(*)       as OrderCount,
      avg( netwr )   as AverageOrderValue,
      min( erdat )   as FirstOrderDate,
      max( erdat )   as LastOrderDate
}
group by kunnr

@AbapCatalog.sqlViewName: 'ZSQLORDERASSOC'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Orders with Associations'

define view Z_I_Orders
  as select from vbak as Order
  association [1..1] to kna1 as _Customer
    on $projection.CustomerId = _Customer.kunnr
  association [0..*] to vbap as _Items
    on $projection.OrderNumber = _Items.vbeln
{
  key vbeln as OrderNumber,
      kunnr as CustomerId,
      erdat as OrderDate,
      netwr as NetValue,

      // Expose associations
      _Customer,
      _Items
}

" Use path expressions
SELECT OrderNumber,
       OrderDate,
       NetValue,
       \_Customer-name1 as CustomerName,
       \_Customer-ort01 as CustomerCity
  FROM z_i_orders
  INTO TABLE @DATA(lt_orders_with_customer).

" With WHERE on association
SELECT *
  FROM z_i_orders
  WHERE \_Customer-land1 = 'DE'
  INTO TABLE @DATA(lt_german_orders).

@AbapCatalog.sqlViewName: 'ZSQLORDERPARAM'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Orders with Parameter'

define view Z_I_OrdersByDate
  with parameters
    p_from_date : abap.dats,
    p_to_date   : abap.dats
  as select from vbak
{
  key vbeln as OrderNumber,
      erdat as OrderDate,
      netwr as NetValue,
      kunnr as CustomerId
}
where erdat >= $parameters.p_from_date
  and erdat <= $parameters.p_to_date

" Pass parameters
SELECT *
  FROM z_i_ordersbydate( p_from_date = '20240101',
                          p_to_date   = '20241231' )
  INTO TABLE @DATA(lt_orders_2024).

" With variables
DATA: lv_from TYPE d VALUE '20240101',
      lv_to   TYPE d VALUE '20241231'.

SELECT *
  FROM z_i_ordersbydate( p_from_date = @lv_from,
                          p_to_date   = @lv_to )
  INTO TABLE @DATA(lt_orders).

@AbapCatalog.sqlViewName: 'ZSQLCUSTUI'
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Customers for UI'

@UI.headerInfo: {
  typeName: 'Customer',
  typeNamePlural: 'Customers',
  title: { value: 'CustomerName' }
}

define view Z_C_Customers
  as select from z_i_customers
{
      @UI.facet: [{ position: 10, type: #IDENTIFICATION_REFERENCE }]

  key @UI.lineItem: [{ position: 10 }]
      @UI.identification: [{ position: 10 }]
      CustomerId,

      @UI.lineItem: [{ position: 20 }]
      @UI.identification: [{ position: 20 }]
      @UI.selectionField: [{ position: 10 }]
      CustomerName,

      @UI.lineItem: [{ position: 30 }]
      @UI.selectionField: [{ position: 20 }]
      City,

      @UI.lineItem: [{ position: 40 }]
      Country
}

@EndUserText.label: 'Access Control for Customers'
@MappingRole: true

define role Z_I_Customers_Role {
  grant select on Z_I_Customers
    where Country = aspect pfcg_auth( ZAUTH_OBJ, ZLAND, ACTVT = '03' );
}

define view Z_I_AllPartners
  as select from kna1
{
  key kunnr as PartnerId,
      name1 as PartnerName,
      'CUSTOMER' as PartnerType
}
union all
  select from lfa1
{
  key lifnr as PartnerId,
      name1 as PartnerName,
      'SUPPLIER' as PartnerType
}

@EndUserText.label: 'CDS Table Function'
define table function Z_TF_ComplexLogic
  with parameters
    p_date : abap.dats
  returns {
    key OrderId : abap.char(10);
        Amount  : abap.curr(15,2);
        Status  : abap.char(1);
  }
  implemented by method
    zcl_complex_logic=>get_orders;

CLASS zcl_complex_logic DEFINITION PUBLIC.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.

    CLASS-METHODS get_orders
      FOR TABLE FUNCTION z_tf_complexlogic.
ENDCLASS.

CLASS zcl_complex_logic IMPLEMENTATION.
  METHOD get_orders BY DATABASE FUNCTION FOR HDB
    LANGUAGE SQLSCRIPT OPTIONS READ-ONLY
    USING vbak.

    RETURN SELECT vbeln as orderid,
                  netwr as amount,
                  'A' as status
           FROM vbak
           WHERE erdat = :p_date;
  ENDMETHOD.
ENDCLASS.

@AbapCatalog.sqlViewAppendName: 'ZSQLCUSTEXT'
@EndUserText.label: 'Customer Extension'

extend view Z_I_Customers with Z_E_Customers_Extension
{
  // Additional fields from other tables
  kna1.brsch as Industry,
  kna1.kukla as CustomerClass
}

// Catalog annotations
@AbapCatalog.sqlViewName: 'SQL_NAME'           // SQL View Name (max 16 characters)
@AbapCatalog.compiler.compareFilter: true      // Filter optimization
@AbapCatalog.preserveKey: true                 // Preserve key
@AbapCatalog.buffering.status: #ACTIVE         // Enable buffering

// Access control
@AccessControl.authorizationCheck: #CHECK      // Authorization check
@AccessControl.authorizationCheck: #NOT_REQUIRED

// Metadata
@EndUserText.label: 'Description'
@ObjectModel.representativeKey: 'FieldName'
@ObjectModel.semanticKey: ['Field1', 'Field2']

// Analytics
@Analytics.dataCategory: #CUBE
@Analytics.dataCategory: #DIMENSION

// OData/Fiori
@OData.publish: true
@UI.headerInfo.typeName: 'Entity'

// String functions
concat( field1, field2 )
substring( field, position, length )
length( field )
upper( field )
lower( field )
ltrim( field, char )
rtrim( field, char )

// Numeric functions
abs( field )
ceil( field )
floor( field )
round( field, decimals )
div( field1, field2 )
mod( field1, field2 )

// Date/time functions
dats_days_between( date1, date2 )
dats_add_days( date, days )
dats_add_months( date, months )
$session.system_date
$session.user
$session.client

// Conversions
cast( field as type )
currency_conversion( ... )
unit_conversion( ... )

// Null handling
coalesce( field, default )

// 1. Meaningful field names (CamelCase)
@AbapCatalog.sqlViewName: 'ZSQLSALES'
define view Z_I_SalesOrders
  as select from vbak
{
  key vbeln as SalesOrderId,      // Not: VBELN
      kunnr as CustomerId,         // Not: KUNNR
      erdat as CreationDate        // Not: ERDAT
}

// 2. Annotations for semantics
{
  @Semantics.amount.currencyCode: 'Currency'
  netwr as NetAmount,

  @Semantics.currencyCode: true
  waerk as Currency,

  @Semantics.quantity.unitOfMeasure: 'Unit'
  kwmeng as Quantity,

  @Semantics.unitOfMeasure: true
  vrkme as Unit
}

// 3. Cardinalities for associations
association [1..1] to ...    // Exactly one
association [0..1] to ...    // Zero or one
association [0..*] to ...    // Zero to many
association [1..*] to ...    // One to many