ATOV_COL_EXP

@AbapCatalog.sqlViewName: 'ATO_V_C_EXP'
@ClientHandling:{ type: #INHERITED, algorithm: #SESSION_VARIABLE }
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Collections'
define view ATOV_COL_EXP
  as

  select from ato_cols                              as Collection
  // latest version with latest Status

  inner join  ATOV_COL_VER_EXP_KEY                  as Collection_Version        on  Collection_Version.collection_id     = Collection.collection_id

  association [1]    to ATOV_COL_COUNT_VERSION      as Version_Counter           on  Version_Counter.collection_id = Collection.collection_id

  association [*]    to ATOV_COL_VER_EXP            as Collection_Versions       on  Collection_Versions.collection_id = Collection.collection_id

  association [1]    to ATOV_COL_VER_EXP            as Latest_Collection_Version on  Latest_Collection_Version.collection_id     = Collection.collection_id
                                                                                 and Latest_Collection_Version.is_latest_version = 'X'

  association [1]    to ATOV_COL_STATUS_DESCRIPTION as Collection_Status         on  Collection_Status.code = Collection.status

  association [0..1] to ATOV_COL_MERGE              as Collection_Merge          on  Collection_Merge.MergedCollectionId = Collection.collection_id
  
  association [1] to ATOV_USER as Changed_By_Description on Changed_By_Description.name = last_changed_by 

  association [1] to ATOV_USER as Created_By_Description on Created_By_Description.name = created_by
{
  key Collection.collection_id,
      Collection.original_system_type,
      Collection.last_changed_by         as changed_by,
      Collection.last_changed_at         as changed_at,
      Collection.created_by              as created_by,
      Collection.created_at              as created_at,
      Collection.original_systname       as original_systname,
      Collection.status                  as status,
      Collection_Status.description      as status_description,
      Changed_By_Description.description as changed_by_description,
      Created_By_Description.description as created_by_description,         
      
      case COALESCE(Collection.description_text,'')
        when '' then Collection.collection_id
        else Collection.description_text
      end                                as description,

      Collection.category,
      /* Associations */
      Version_Counter,
      Collection_Versions,
      Latest_Collection_Version
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"ATOV_COL_STATUS_DESCRIPTION",
"ATOV_COL_VER_EXP_KEY",
"ATOV_USER",
"ATO_COLS"
],
"ASSOCIATED":
[
"ATOV_COL_COUNT_VERSION",
"ATOV_COL_MERGE",
"ATOV_COL_STATUS_DESCRIPTION",
"ATOV_COL_VER_EXP",
"ATOV_USER"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/