ATOV_CL_ITEM

@AbapCatalog.sqlViewName: 'ATO_V_CL_I'
@ClientHandling:{ type: #INHERITED, algorithm: #SESSION_VARIABLE }
@EndUserText.label: 'Changelist items'
define view ATOV_CL_ITEM as 

  select from ato_changel_item as Change_List_Item

  inner join ATOV_Cl as ChangeList
    on Change_List_Item.changelist_id = ChangeList.changelist_id
  
  association [0..1] to ATOV_ITEM as Existing_Item
    on Existing_Item.item_type   = Change_List_Item.item_type and
       Existing_Item.item_id     = Change_List_Item.item_id and
       Existing_Item.item_status <> 'D'
    
  association [1] to ATOV_Cl as Change_List
    on Change_List_Item.changelist_id = Change_List.changelist_id

  association [*] to ATOV_ALL_TEXT_ADAPT_TYPE as Adaptation_Type_Texts
    on Adaptation_Type_Texts.item_type = Change_List_Item.item_type

  association [*] to ATOV_TEXT_ITEM_IMPORT_STATUS as Import_Status_Texts
    on Import_Status_Texts.code = status
    
  association [*] to ATOV_TEXT_IMG_ACTIVITY as ImgActivityDescription 
    on Change_List_Item.img_activity = ImgActivityDescription.activity

  {
    key Change_List_Item.changelist_id, 
    key Change_List_Item.item_counter, 
    Change_List_Item.item_type, 
    Change_List_Item.item_id, 
    Change_List_Item.item_lifecycle_action, 
    Change_List_Item.item_description, 
    Change_List_Item.item_import_status as changelist_item_import_status,
    case COALESCE( Existing_Item.item_id,'')
      when '' then ''
      else 'X'
    end as item_exists,

    case Change_List_Item.item_import_status
      when '' 
        then //no DB status => calculate forecast

          case when Change_List_Item.item_lifecycle_action = 'D'
            then 'D'
            else case COALESCE( Existing_Item.item_id,'')
              when '' then 'N'
              else case when Change_List_Item.item_last_notification = Existing_Item.changed_at
                then 'U'
                else 'C'
                end
            end
          end
      else //return DB status

        Change_List_Item.item_import_status
    end as status,
    Change_List_Item.item_last_notification,
    Change_List_Item.item_last_notification_by,
    Change_List_Item.img_activity,
    ChangeList.collection_id,
    ChangeList.collection_version,
    /* Associations */
    Change_List,
    Adaptation_Type_Texts,
    Import_Status_Texts, 
    ImgActivityDescription
  }          
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"ATOV_CL",
"ATOV_ITEM",
"ATO_CHANGEL_ITEM"
],
"ASSOCIATED":
[
"ATOV_ALL_TEXT_ADAPT_TYPE",
"ATOV_CL",
"ATOV_ITEM",
"ATOV_TEXT_IMG_ACTIVITY",
"ATOV_TEXT_ITEM_IMPORT_STATUS"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/