Atov_Col_Item_ContiDep_Join

@AbapCatalog.sqlViewName: 'ATO_V_COL_CONTID'
@ClientHandling:{ type: #INHERITED, algorithm: #SESSION_VARIABLE }
@EndUserText.label: 'Join Continous Dep and Collection Items'
define view Atov_Col_Item_ContiDep_Join  as 
   select from ato_items as Item
    
   left outer join Atov_Contidep_Item_Key as Conti on
     Item.item_type = Conti.item_type and
     Item.item_id = Conti.item_id
        
    left outer join ATOV_CONTI_DEP_SAP_KEY as Released_References on
      Item.item_type = Released_References.item_type and   
      Item.item_id   = Released_References.item_id
    
    inner join ato_adapt_types as Adapt_Type on
       Item.item_type = Adapt_Type.adaptation_type 
      
  {
    key Item.item_type,
    key Item.item_id,
    Item.item_status,
    Item.last_notification as changed_at, // this is the last Notification Timestamp

    COALESCE(Conti.last_updated_at, 0) as Dep_updated_at,
    COALESCE(Released_References.updated_at , 0)  as SAP_Ref_updated_at,
    case 
      when Item.last_notification < COALESCE(Conti.last_updated_at, 0) then 'X'
      else case
        when Item.last_notification < COALESCE(Released_References.updated_at , 0) then 'X'
        else ''
      end    
     end as Dep_up_to_date,
    case COALESCE(Conti.last_updated_at, 0)
      when 0 then 
        case when Adapt_Type.col_empty_bom_allowed = 'X' then 'X'
        else ''
      end
      else ''
    end as empty_bom_allowed     
  }
  where  Item.timestamp = 0;     
  
  
 /*+[internal] {
"BASEINFO":
{
"FROM":
[
"ATOV_CONTIDEP_ITEM_KEY",
"ATOV_CONTI_DEP_SAP_KEY",
"ATO_ADAPT_TYPES",
"ATO_ITEMS"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/