User-defined database function and CDS view

ABAP Database view and CDS view :

1. ABAP Database View:

    - An ABAP database view is a simple view on one or more database tables defined in the ABAP dictionary (SE11).

    - These views are processed on the application server of the ABAP stack.

    - They allow basic selection and projection over tables and other database views.

    - Joins are possible but with limitations (inner join only).

    - They don't allow complex calculations or expressions.

    - The processing and consumption of database views are usually done using Open SQL.

    - Limited to use within the SAP system.

2. CDS View (Core Data Services):

    - CDS views are part of SAP's new programming model.

    - They are defined on the HANA database layer and are processed in the database itself.

    - They allow complex calculations, expressions, and functions.

    - Support different types of joins, not just inner join.

    - They can expose associations between different entities (tables/views), allowing for more complex and efficient querying.

    - They can be consumed in ABAP programs using Open SQL, but also can be used in other SAP technologies like Fiori, OData, etc.

    - CDS views also offer additional capabilities like defining access controls (Authorizations) at the view level.

    - CDS views can also include features such as input parameters, allowing for greater flexibility.

    - Can be consumed outside the SAP system.

In conclusion, while ABAP database views and CDS views both serve the purpose of creating reusable views on top of database tables, CDS views offer more advanced features and capabilities, especially for SAP HANA environments and the new SAP programming model.

Consider a scenario where we need to calculate the total revenue generated by a company based on sales orders. We have a CDS view called "SalesOrderDetails" (as defined in the previous example) that provides information about sales orders, including the order amount. To calculate the total revenue, we can create a user-defined database function called "CalculateTotalRevenue" that sums up the order amounts from the "SalesOrderDetails" view.

Here's an example of how the user-defined database function might be defined:

FUNCTION CalculateTotalRevenue RETURNING VALUE(rv_total_revenue) TYPE p DECIMALS 2.
  DATA(l_total_revenue) = 0.
  SELECT SUM(OrderAmount)
    FROM SalesOrderDetails
    INTO l_total_revenue.
  rv_total_revenue = l_total_revenue.
ENDFUNCTION.

In the above example:

• The function "CalculateTotalRevenue" is defined with a return parameter rv_total_revenue of type p (decimal number) with two decimal places.
• Within the function, a local variable l_total_revenue is initialized to zero.
• The SELECT statement queries the "SalesOrderDetails" CDS view and calculates the sum of the "OrderAmount" column.
• The result is stored in the local variable l_total_revenue.
• Finally, the function assigns the value of l_total_revenue to the return parameter rv_total_revenue.

Now, we can use the user-defined database function "CalculateTotalRevenue" to obtain the total revenue generated by the company. For example, in an ABAP program or SQL statement, we can call the function and retrieve the result:

DATA(l_total_revenue) = CalculateTotalRevenue( ).
WRITE: 'Total Revenue:', l_total_revenue.

The CalculateTotalRevenue function executes the SQL query defined within it, retrieves the total revenue from the "SalesOrderDetails" view, and returns the result. The total revenue value is then displayed on the screen or can be used for further processing.

By combining the CDS view "SalesOrderDetails" and the user-defined database function "CalculateTotalRevenue," we can efficiently calculate and retrieve the total revenue based on sales orders. This example showcases the integration of CDS views and user-defined database functions to achieve more complex data calculations and transformations.