Atov_Col_Item_Conti_Join

@AbapCatalog.sqlViewName: 'ATO_V_COL_CONTI'
@ClientHandling:{ type: #INHERITED, algorithm: #SESSION_VARIABLE }
@EndUserText.label: 'Join Continous BOM and Collection Items'
define view Atov_Col_Item_Conti_Join  as

  select from ato_items as Item 
   left outer join Atov_Conti_Item_Key as Conti on
   Item.item_type = Conti.item_type and
   Item.item_id = Conti.item_id
   left outer join ato_conti_exclud as Exclude on
   Item.item_type = Exclude.item_type and
   Item.item_id = Exclude.item_id

  {
    key Item.item_type,
    key Item.item_id,
    Item.item_status,
    Item.last_notification as changed_at, // this is the last Notification Timestamp

    Conti.last_updated_at as BOM_updated_at,
    case COALESCE(Conti.last_updated_at, 0) 
      when 0 then ''
      else 'X'
    end as BOM_exists,
    case when Item.last_notification > Conti.last_updated_at    then 'X'
      else ''
    end as BOM_outdated,
    case when Item.last_notification> Exclude.excluded_at    then ''
      else case COALESCE(Exclude.excluded_at, 0)
         when 0 then ''
         else 'X'
      end   
    end as Excluded    

  }
  where Item.timestamp = 0;