Selection Options on Associated CDS Views (Exists Operator)

You want to link a CDS view in your output, using factory method CREATE_FOR_CDS( ) (see also Support of Core Data Services (CDS)). This CDS view contains header data and has associations with CDS views that contain further details (for example, items, addresses). When associated CDS views are used, selection conditions in their fields can be created without these fields having to be part of the CDS view with the header data. This means that even with 1:n relationships between entities you can display one line of data for each header if no fields of the associated CDS view are included in the CDS view with the header data.

The example below illustrates this concept.

Using ALV for IDA, sales orders are to be displayed and the following selection conditions made available to the user:

Conditions from the sales order header
Sales orders that contain items of a specific type (for example, selected products only)
Sales orders whose customers are located in selected countries
The graphic below shows a small entity model and its dependencies: Sales Order represents the CDS view with the header data Customer, Address, and Items, represents the associated CDS views.

A sales order should appear once only in the list.

Example Table Sales Order with 1:n cardinality to table Items:

Table 1: **Sales Order**
SO1	23.12.2014	SalesItem1	Customer1
SO2	04.09.2014	SalesItem2	Customer1
SO3	01.04.2014	SalesItem3	Customer7

Table 2: **Items**
SO1	SalesItem1	Item1	AV100
SO1	SalesItem2	Item2	AV300
SO1	SalesItem3	Item3	AV100
SO2	SalesItem4	Item1	AV300
SO3	SalesItem5	Item1	AV100
SO3	SalesItem6	Item2	AV400

The result with a standard Select statement clause ... WHERE sales order type ='AV100' has the following format:

SO1	AV100
SO1	AV100
SO3	AV100

If a standard WHERE condition is used, the fields must be defined as output in the sales order header, the cartesian product is output by a 1:n cardinality, and the Sales Orders are displayed in addition to the number of Items.

If the exists operator is used internally, the result is displayed as requested - each request appears only once in the list:

SO1	Customer1
SO3	Customer3

To achieve this, the Items fields must not be listed in the CDS view with the header data, the WHERE condition must be specified as the SELECT OPTION, the same as the association path.

So the developer defines a CDS view that contains the fields of the sales order header and the necessary associations.

The system contains CDS view SEPM_CDS_SALES_ORDER. To transfer fields from associated CDS views (for example, SEPM_CDS_SALES_ORDER_ITEM ) into the selection conditions, and to use them as exists operators, dot notations can be passed to parameter IV_NAME of method ADD_RANGES_FOR_NAME of class cl_salv_range_tab_collector. If no dot notation is used, only fields from the linked CDS view can be addressed. For example, if values for the COUNTRY field from an entity model are provided as the selection condition, the association path in parameter IV_NAME is specified as follows: Customer.Address.Country.

Below is an example of source code to transfer selection conditions in fields from the linked CDS view and its associated CDS views:

data(lo_collector) = NEW cl_salv_range_tab_collector( ).
lo_collector-> add_ranges_for_name(    
  iv_name = 'SALES_ORDER_ID'  it_ranges =  salesorderid[] ). 
lo_collector-> add_ranges_for_name(    
  iv_name = 'CUSTOMER.ADDRESS.COUNTRY'  it_ranges =  bupa_address_cntr[] ). 
lo_collector-> add_ranges_for_name(    
  iv_name = 'ITEMS.PRODUCT.ID'    it_ranges =  items_product_id[] ). 
lo_collector-> get_collected_ranges(   
  IMPORTING  et_named_ranges = data( lt_name_range_pairs) ). 
lo_alv_display-> set_select_options(  it_ranges =  lt_name_range_pairs ).

Conditions in different fields are linked with the logical AND; multiple values and ranges are linked with the logical OR.

Example This way requests can be formulated in the following format:

Displays all sales orders from the number range 200*, whose customers have a branch office in Germany or England, and which contain a PC or monitor.

The following request types cannot be covered:

Displays all sales orders from the number range 200*, whose customers have a branch office in Germany or England, and which contain a PC and a monitor.

This behavior follows the standard SELECT OPTIONS links. The AND operator between different values in a field or the OR operator between different fields is not supported.

Example

You can find an example in the system in report sadl_alv_ida_test_sales_exist. The purpose of this report is to display only those sales order headers for which items were invoiced in a specific currency.

If the selection Display Sales Order Item is activated, all items are displayed in CDS view SEPM_CDS_SALES_ORDER_ITEM. Then an existing currency from the items, for instance USD and option Display Sales Orders can be selected (the standard CDS view SEPM_CDS_SALES_ORDER is used for this) to display a list of all sales order headers. The user can now set a currency from the Sales Order Items, whose value is passed and evaluated as the SELECT option with the association path.

The items.CURRENCY_CODE addressing is shown in the CDS views (the entities shown in bold in the coding example below) as follows:

SEPM_CDS_SALES_ORDER(1)	SEPM_CDS_SALES_ORDER_ITEM(n)
... define view sepm_cds_sales_order as select from snwd_so association [0..*] to sepm_cds_sales_order_item as items on node_key = items.sales_order_key association to sepm_cds_business_partner as customer on buyer_guid = customer.business_partner_key { ...	@AbapCatalog.sqlViewName: 'SEPMV_SO_I' define view sepm_cds_sales_order_item as select from snwd_so_i association to sepm_cds_sales_order as sales_order on parent_key = sales_order.sales_order_key association to sepm_cds_product as product on product_guid = product.product_key { key node_key as sales_order_item_key, parent_key as sales_order_key, so_item_pos, @Semantics.currencyCode currency_code, @Semantics.amount.currencyCode: 'CURRENCY_CODE' gross_amount, @Semantics.amount.currencyCode: 'CURRENCY_CODE' net_amount, @Semantics.amount.currencyCode: 'CURRENCY_CODE' tax_amount, item_atp_status, sales_order, product_guid, product }

SEPM_CDS_SALES_ORDER(1)

SEPM_CDS_SALES_ORDER_ITEM(n)

...
define view sepm_cds_sales_order
  as select from snwd_so 
  association  [0..*]
    to sepm_cds_sales_order_item as items
    on node_key = 
items.sales_order_key
  association 
    to sepm_cds_business_partner as 
customer
    on buyer_guid = 
customer.business_partner_key
{
...

@AbapCatalog.sqlViewName: 'SEPMV_SO_I'
define view sepm_cds_sales_order_item
  as select from snwd_so_i 
  association 
    to sepm_cds_sales_order as sales_order
    on parent_key = sales_order.sales_order_key
  association
    to sepm_cds_product as product
    on product_guid = product.product_key
{
    key node_key as sales_order_item_key,
    parent_key   as sales_order_key,
    so_item_pos,
    @Semantics.currencyCode currency_code,
    @Semantics.amount.currencyCode: 'CURRENCY_CODE' gross_amount,
    @Semantics.amount.currencyCode: 'CURRENCY_CODE' net_amount,
    @Semantics.amount.currencyCode: 'CURRENCY_CODE' tax_amount,
    item_atp_status,
    sales_order,    
    product_guid,   
    product          
}