atov_cmn_import_missing_tr_dep

@AbapCatalog.sqlViewName: 'ATO_V_CMN_DEP_MT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Missing dependent transports'
define view atov_cmn_import_missing_tr_dep
  as select from    ATOV_COL_VER_KEY        as collection_versions
    inner join      atov_versions_to_import as import_versions       on  collection_versions.collection_id      = import_versions.collection_id
                                                                     and collection_versions.collection_version = import_versions.collection_version
    inner join      ato_changelists         as using_changelist      on  import_versions.collection_id      = using_changelist.collection_id
                                                                     and import_versions.collection_version = using_changelist.collection_version
    inner join      ato_changel_dept        as dependencies          on using_changelist.changelist_id = dependencies.changelist_id
    left outer join ato_changelists         as dependent_changelists on dependencies.request = dependent_changelists.transport
{

  collection_versions.collection_id          as collection_id,
  collection_versions.collection_version     as version,
  max( using_changelist.collection_version ) as mediating_version,
  dependencies.request                       as missing_transport
}
where
  dependent_changelists.changelist_id is null // Only select those transports that have no changelist assigned yet

group by
  collection_versions.collection_id,
  collection_versions.collection_version,
  dependencies.request
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"ATOV_COL_VER_KEY",
"ATOV_VERSIONS_TO_IMPORT",
"ATO_CHANGELISTS",
"ATO_CHANGEL_DEPT"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/