5.F4 help and Select option in CDS view.

F4 help and Select option in CDS view 

F4 Help implementation

1. Using Association
2. Using Basic and Consumption view

Code:

Basic view:

1. @AbapCatalog.sqlViewName: 'ZSQL_SO_BASEV'
2. @AbapCatalog.compiler.compareFilter: true
3. @AbapCatalog.preserveKey: true
4. @AccessControl.authorizationCheck: #CHECK
5. @EndUserText.label: 'salesorder base view'
6. @VDM.viewType: #BASIC
7. define view zsalesorder_base_test
8.   with parameters
9.     targer_currecny : abap.cuky,
10.     exchange_data   : abap.dats
11.   as select from    vbak as SalesHeader
12.     left outer join vbap as SalesItem on SalesHeader.vbeln = SalesItem.vbeln
13.   //Association with std cds view(I_material) for F4 help
14.   association [1..*] to I_Material as _material on matnr = SalesItem.matnr
15. {
16.   //Annotation for select option
17.   @Consumption.filter:{selectionType: #SINGLE, multipleSelections: true, mandatory: false}
18.   SalesItem.vbeln                 as SalesOrder,
19.   SalesItem.posnr                 as Item,
20.   SalesItem.erdat                 as OrderDate,
21.   SalesItem.matnr                 as Material,
22.   @DefaultAggregation: #SUM
23.   @Semantics.amount.currencyCode: 'Currency'
24.   SalesItem.netwr                 as ActualPrice,
25.   @Semantics.amount.currencyCode:true
26.   SalesItem.waerk                 as Currency,
27.   concat(cast(SalesItem.netwr as abap.char( 20 )), SalesItem.waerk) as PriceCurrency,
28.   currency_conversion( amount => ActualPrice,
29.                      source_currency => Currency,
30.                      target_currency => $parameters.target_currency,
31.                      exchange_rate_date => $parameters.exchange_date,
32.                      exchange_rate_type => 'M' ) as ConvertedCurrency
33.   //Exposing this cds view
34.   _material
35. }

Consumption view:

1. @AbapCatalog.sqlViewName: 'ZSQL_SO_CONSUMV'
2. @AbapCatalog.compiler.compareFilter: true
3. @AbapCatalog.preserveKey: true
4. @AccessControl.authorizationCheck: #CHECK
5. @EndUserText.label: 'Consum view_association_F4 help_rsrt annotation'
6. @VDM.viewType: #CONSUMPTION
7. define view zsalesorder_consum_test
8.   with parameters
9.     target_currecny : abap.cuky,
10.     exchange_date   : abap.dats
11.   as select from zsalesorder_base_test (targer_currecny: $parameters.target_currecny, exchange_data: $parameters.exchange_date)
12. {
13.   SalesOrder,
14.   Item,
15.   OrderDate,
16.   //Annotation for select option
17.   @Consumption.filter:{selectionType: #SINGLE, multipleSelections: true, mandatory: false}
18.   //Estbl FK rel with std cds view to achieve F4 help
19.   @ObjectModel.foreignKey.association: '_material'
20.   Material,
21.   ActualPrice,
22.   Currency,
23.   PriceCurrency,
24.   ConvertedCurrency,
25.   /* Associations */
26.   _material
27. }

4.Types of CDS view

 Types of CDS view

Types of views

There are three types of views

1. Basic view - used to write basic operation like select operation. 
2. Composite view - used to write all the business logics
3. Consumption view - used to expose the view to outside environment 

Note:

1. @VDM.viewType: #BASIC
2. @VDM.viewType: #COMPOSITE
3. @VDM.viewType: #CONSUMPTION

code:

    Basic view:

1. @AbapCatalog.sqlViewName: 'ZSALESORDERV'
2. @AbapCatalog.compiler.compareFilter: true
3. @AbapCatalog.preserveKey: true
4. @AccessControl.authorizationCheck: #CHECK
5. @EndUserText.label: 'base view for sales order'
6. @VDM.viewType: #BASIC
7. //Join operaration
8. define view zsalesorder_base
9.   as select from    vbak as SalesHeader
10.     left outer join vbap as SalesItem on SalesHeader.vbeln = SalesItem.vbeln
11. {
12.   //Fields to be displayed in OP
13.   SalesItem.vbeln                         as SalesOrder,
14.   SalesItem.posnr                         as Item,
15.   SalesItem.erdat                         as OrderDate,
16.   SalesItem.matnr                         as Material,
17.   //Sum operation using annotation
18.   @DefaultAggregation: #SUM
19.   //creating FK rel btw actualprice and currency field
20.   @Semantics.amount.currencyCode: 'Currency'
21.   SalesItem.netwr                         as ActualPrice,
22.   //currency field is key field for actual price
23.   @Semantics.amount.currencyCode:true
24.   SalesItem.waerk                         as Currency,
25.   //concatenate price and currecny unit field
26.   concat(cast(SalesItem.netwr as abap.char( 20 )), SalesItem.waerk ) as Price
27.   //Exposing this cds view
28. }

    Consumption view:

1. @AbapCatalog.sqlViewName: 'ZSALORDER_CONSV'
2. @AbapCatalog.compiler.compareFilter: true
3. @AbapCatalog.preserveKey: true
4. @AccessControl.authorizationCheck: #CHECK
5. @EndUserText.label: 'Consumption view for sales order'
6. @VDM.viewType: #CONSUMPTION
7. @Analytics.query: true
8. define view zsalesorder_consum
9.   with parameters
10.     //Annotation to default the value for parameter
11.     @Consumption.defaultValue: 'USD'
12.     target_currency : abap.cuky,
13.     //Annotation to default system date for parameter implementing consumption view
14.     @Environment.systemField: #SYSTEM_DATE
15.     exchange_date   : abap.dats
16.   //implementing consumption view
17.   as select from zsalesorder_base
18. {
19.   //Annotation for select option
20.   @Consumption.filter:{selectionType: #SINGLE, multipleSelections: true, mandatory: false}
21.   //Annotation to filter the fields on rsrt display
22.   @AnalyticsDetails.query.axis: #ROWS
23.   //Creating key field in consumption view using keyword 'key'
24.   key SalesOrder,
25.   @AnalyticsDetails.query.axis: #ROWS
26.   key Item,
27.       @AnalyticsDetails.query.axis: #ROWS
28.       OrderDate,
29.       Material,
30.       ActualPrice,
31.       Currency,
32.       Price,
33.       //Inbuild currency conversion func
34.       currency_conversion( amount => ActualPrice,
35.                            source_currency => Currency,
36.                            target_currency => $parameters.target_currency,
37.                            exchange_rate_date => $parameters.exchange_date,
38.                            exchange_rate_type => 'M' ) as ConvertedCurrency
39. }

3.CDS view with association

 CDS view with association

2.Association in CDS view

1. Associations is nothing but joins, but these are on demand select. ie.. if we establish association between vbak and vbap, associations will be triggers only if we use any of the fields of vbap in our CDS view or else associations will not be triggered.
2. We can define any number of association in our CDS view, but it will be triggered only if we used the fields in our CDS view. 
3. When ever we consume standard CDS view in our program we should go for association. Ex: to achieve F4 help(list of possible values).
4. Standard CDS views starts with 'I_'
5. All the association name should starts with '_'
6. With the help of association we can expose one view with other view.

Code:

1. @AbapCatalog.sqlViewName: 'ZSALORDER_ASSOV'
2. @AbapCatalog.compiler.compareFilter: true
3. @AbapCatalog.preserveKey: true
4. @AccessControl.authorizationCheck: #CHECK
5. @EndUserText.label: 'F4 help using std cds view'
6. define view zsalesorder_association
7.   with parameters
8.     targer_currecny : abap.cuky,
9.     exchange_data   : abap.dats
10.   as select from    vbak as SalesHeader
11.     left outer join vbap as SalesItem on SalesHeader.vbeln = SalesItem.vbeln
12.   //Association with std cds view(I_material)
13.   association [1..*] to I_Material as _material on matnr = SalesItem.matnr
14. {
15.   //Fields to be displayed in OP
16.   SalesItem.vbeln                         as SalesOrder,
17.   SalesItem.posnr                         as Item,
18.   SalesItem.erdat                         as OrderDate,
19.   SalesItem.matnr                         as Material,
20.  
21.   //Establishing FK
22.   @DefaultAggregation: #SUM
23.   @Semantics.amount.currencyCode: 'Currency'
24.   SalesItem.netwr                         as ActualPrice,
25.   @Semantics.amount.currencyCode:true
26.   SalesItem.waerk                         as Currency,
27.   concat(cast(SalesItem.netwr as abap.char( 20 )), SalesItem.waerk) as PriceCurrency,
28.  
29.   //Inbuild func for currency conversion
30.   currency_conversion( amount => SalesItem.netwr, source_currency => SalesItem.waerk, target_currency => $parameters.targer_currecny, exchange_rate_date => $parameters.exchange_data, exchange_rate_type => 'M' )             as ConvertedCurrency,
31.   //Exposing this cds view
32.   _material
33. }

2.CDS view with parameter and build in functions

CDS view with parameter

Sample CDS view with parameter for sales order.

• Declaring parameter using with parameter keyword
• There are various inbuild functions supported  by cds views, here we use currency_conversion inbuild function to achieve currency conversion.

Code:

1. @AbapCatalog.sqlViewName: 'ZSALORDER_PARAMV'
2. @AbapCatalog.compiler.compareFilter: true
3. @AbapCatalog.preserveKey: true
4. @AccessControl.authorizationCheck: #CHECK
5. @EndUserText.label: 'Currency conversion using parameter'
6. 
   
7. define view zsalesorder_parameter
8. //declaring parameter
9. with parameters
10.     target_currency : abap.cuky,
11.     exchange_data   : abap.dats
12. as select from vbak as SalesHeader    left outer join vbap as SalesItem on SalesHeader.vbeln = SalesItem.vbeln
13. {
14. SalesItem.vbeln                       as SalesOrder,
15. SalesItem.posnr                       as Item,
16. SalesItem.erdat                        as OrderDate,
17. SalesItem.matnr                       as Material,
18. //Establishing FK relationship
19. @DefaultAggregation: #SUM
20. @Semantics.amount.currencyCode: 'Currency'
21. SalesItem.netwr                       as ActualPrice,
22. @Semantics.amount.currencyCode:true
23. SalesItem.waerk                      as Currency,
24. //Concatenate actual price and currency
25. concat(cast(SalesItem.netwr as abap.char( 20 )), SalesItem.waerk)   as PriceCurrency,
26. //Logic for currency conversion
27. currency_conversion( amount => SalesItem.netwr, source_currency => SalesItem.waerk, target_currency => $parameters.target_currency, exchange_rate_date => $parameters.exchange_data )                                                  as ConvertedValue,
28. $parameters.target_currency    as ConvertedCurrency
29. }

Note:

1. Similarly there are various inbuild functions available like
• //inbluid func to calculate days between two date
• dats_days_between(SalesItem.erdat, $parameters.exchange_data)      as DaysBetween.
2. There is no message classes in CDS view. we cannot display any message using CDS views 

Conversion Functions

The following table shows the conversion functions supported by ABAP CDS and Open SQL. The last two columns indicate where a function can be used.

SQL Function	Result	ABAP CDS	Open SQL
FLTP_TO_DEC( arg AS dtype )	Conversion of an argument of type FLTP to a packed number.	x	-
UNIT_CONVERSION( p1 => a1, p2 => a2, ... )	Conversion of units.	x	-
CURRENCY_CONVERSION( p1 => a1, p2 => a2, ... )	Conversion of currencies.	x	-
DECIMAL_SHIFT( p1 => a1, p2 => a2, ... )	Setting the decimal separator.	x	-

SQL Functions for Byte Strings

The following table shows the SQL functions for byte strings supported by ABAP CDS and Open SQL. The last two columns indicate where a function can be used.

SQL Function	Result	ABAP CDS	Open SQL
BINTOHEX(arg)	Character string containing the half bytes arg converted to the hexadecimal characters "0" to "9" and "A" to "F" (left-justified).	x	-
HEXTOBIN(arg)	Byte string whose half bytes are determined from the hexadecimal characters in arg. Any leading blanks are removed before the conversion from arg and all trailing blanks are then replaced by "0".	x	-

Date Functions and Time Functions

The following table shows the date and time functions supported by ABAP CDS and Open SQL. The last two columns indicate where a function can be used.

SQL Function	Result	ABAP CDS	Open SQL
DATS_IS_VALID( date )	Shows whether the argument is a valid date.	x	x
DATS_DAYS_BETWEEN( date1, date2 )	Difference between two dates.	x	x
DATS_ADD_DAYS( date, days , on_error )	Total of days and a date.	x	x
DATS_ADD_MONTHS ( date, months, on_error )	Total of months and a date.	x	x
TIMS_IS_VALID( time )	Shows whether the argument is a valid time.	x	-
TSTMP_IS_VALID( tstmp )	Shows whether the argument is a valid time stamp.	x	-
TSTMP_CURRENT_UTCTIMESTAMP( )	Current UTC time stamp.	x	-
TSTMP_SECONDS_BETWEEN ( tstmp1, tstmp2, on_error )	Difference between two time stamps in seconds.	x	-
TSTMP_ADD_SECONDS( tstmp, seconds, on_error )	Total of seconds and a time stamp.	x	-
TSTMP_TO_DATS( tstmp, tzone, clnt, on_error )	Local date of a time stamp.	x	-
TSTMP_TO_TIMS( tstmp, tzone, clnt, on_error )	Local time of a time stamp.	x	-
TSTMP_TO_DST( tstmp, tzone, clnt, on_error )	Local summer time marker of a time stamp.	x	-
DATS_TIMS_TO_TSTMP( date, time, tzone, clnt, on_error )	Time stamp for a local date and a local time.	x	-
ABAP_SYSTEM_TIMEZONE( clnt, on_error )	System time zone of AS ABAP.	x	-
ABAP_USER_TIMEZONE( user, clnt, on_error )	User time zone of AS ABAP.	x	-

SQL Functions for Numeric Values

The following table shows the numeric SQL functions supported by ABAP CDS and Open SQL. The last two columns indicate where a function can be used.

SQL Function	Result	ABAP CDS	Open SQL
ABS(arg)	Absolute amount of arg.	x	x
CEIL(arg)	Smallest integer number not less than the value of arg.	x	x
DIV(arg1, arg2)	Integer part of the division of arg1 by arg2 The sign is assigned after the amounts are divided; positive if the arguments have the same sign, and negative if the arguments have different signs. Exception: arg2 has the value 0.	x	x
DIVISION(arg1, arg2, dec)	Division of arg1 by arg2 The result is rounded to dec decimal places.	x	x
FLOOR(arg)	Largest integer number not greater than the value of arg.	x	x
MOD(arg1, arg2)	Positive or negative integer remainder of the division of arg1 by arg2.	x	x
ROUND(arg, pos)	Rounded value of arg. If pos is greater than 0, the value is rounded to the position pos on the right of the decimal separator. If this is not the case, position abs(pos)+1 to the left of the decimal separator is rounded. This results in a 0 if the number of places is not sufficient.	x	x

SQL Functions for Strings

The following table shows the SQL functions for strings supported by ABAP CDS and Open SQL. The last two columns indicate where a function can be used.

SQL Function	Result	ABAP CDS	Open SQL
CONCAT( arg1, arg2 )	Chaining of character strings in arg1 and arg2. Trailing blanks in arg1, arg2, and in the result are ignored. The maximum length of the result is 1333.	x	x
CONCAT_WITH_SPACE(  arg1, arg2, spaces )	Concatenation of strings in arg1 and arg2 as with CONCAT. The number of blanks specified in spaces is inserted between arg1 and arg2. The maximum length of the result is 1333.	x	x
INSTR( arg, sub )	Position of the first occurrence of the string from sub in arg (case-sensitive). arg respects leading blanks and ignores trailing blanks. sub respects all blanks. sub must contain at least one character. If no occurrences are found, the result is 0.	x	x
LEFT( arg, len )	String of the length len with the len left characters of arg (ignoring the trailing blanks). The value of len cannot be greater than the length of arg.	x	x
LENGTH( arg )	Number of characters in arg ignoring trailing blanks.	x	x
LOWER( arg )	String with a length of arg, in which all upper and lowercase characters are have been converted.	x	x
LPAD( arg, len, src )	String of the length len with the right-justified content of arg without trailing blanks and in which leading blanks produced by the expanded string are replaced by the characters from the argument src (respecting all blanks). Trailing blanks from arg are preserved. If more characters are required than exist in src, the content of src is used repeatedly. If len is less than the length of arg, it is truncated on the right. If src is empty and len is greater than the length of arg, arg remains unchanged.	x	x
LTRIM( arg, char )	String with the content of arg in which all trailing blanks and leading characters are removed that match the character in char. A blank in char is significant.	x	x
REPLACE( arg1, arg2, arg3 )	Character string arg1, in which all instances of arg2 are replaced by the content from arg3. The replacement of letters is case-sensitive. Trailing blanks are ignored in all arguments. The maximum length of the result is 1333.	x	x
RIGHT( arg, len )	String of the length len with the len right characters of arg (ignoring the trailing blanks). The value of len cannot be greater than the length of arg.	x	x
RPAD( arg, len, src )	String of the length len with the left-justified content of arg without trailing blanks and in which trailing blanks produced by the expanded string are replaced by the characters from the argument src (respecting all blanks). Trailing blanks from arg are preserved. If more characters are required than exist in src, the content of src is used repeatedly. If len is less than the length of arg, it is truncated on the right. If src is empty and len is greater than the length of arg, arg remains unchanged.	x	x
RTRIM( arg, char )	String with the content of arg in which all trailing blanks are removed and all trailing characters that match the character in char. A blank in char is significant.	x	x
SUBSTRING( arg, pos, len )	Substring of arg from the position pos in the length len. pos and len must be specified so that the substring is within arg.	x	x
UPPER( arg )	String with a length of arg, in which all lower and uppercase characters are have been converted.	x	x

SQL Functions for Null Values

The following table shows the SQL functions for null values that are supported by ABAP CDS and Open SQL. The last two columns indicate where a function can be used.

SQL Function	Result	ABAP CDS	Open SQL
COALESCE( arg1, arg2, ... )	Value of the first argument that does not have a null value.	x	x