SELECT pkg.pkg_id, pv.pv_id, dpv.pv_id AS dpv_id, 
      DECODE(fullrel.v_nmm, 
               NULL, DECODE(dpv.pv_id, 0, 'DEPENDENCIES NOT DEFINED','NOT FOUND'), 
               dpv.v_nmm, DECODE(fullrel.v_mm, dpv.v_mm, NULL, 'MINOR MINOR'), 
               'MAJOR') AS message, 
      DECODE(fullrel.v_nmm, 
               NULL, NULL, 
               dpv.v_nmm, DECODE(fullrel.v_mm, dpv.v_mm, NULL, fullrel.pkg_version), 
               fullrel.pkg_version) AS latest 
 FROM packages pkg,
      packages dpkg,
      package_dependencies dep,
      package_versions pv,
      release_content rel,
      package_versions dpv,
      (    
      SELECT rel.pv_id, pkg.pkg_name, pv.pkg_version, pv.v_mm, pv.v_nmm
        FROM release_content rel, 
             package_versions pv, 
             packages pkg 
       WHERE rel.pv_id = pv.pv_id 
              AND pv.pkg_id = pkg.pkg_id 
              AND rel.rtag_id = $RTAG_ID$ 
      ) fullrel
WHERE pkg.pkg_id = pv.pkg_id 
  AND dpkg.pkg_id = dpv.pkg_id 
  AND pv.pv_id = dep.pv_id 
  AND pv.pv_id = rel.pv_id 
  AND dpv.pv_id = dep.dpv_id 
  AND dpkg.pkg_name = fullrel.pkg_name(+)
  /*AND dpv.pv_id NOT IN ( SELECT iw.iw_id FROM ignore_warnings iw WHERE iw.rtag_id = $RTAG_ID$ )*/
  AND DECODE(fullrel.v_nmm, 
             NULL, DECODE(dpv.pv_id, 0, 'DEPENDENCIES NOT DEFINED','NOT FOUND'), 
             dpv.v_nmm, DECODE(fullrel.v_mm, dpv.v_mm, NULL, 'MINOR MINOR'), 
             'MAJOR') IS NOT NULL 
  AND rel.rtag_id = $RTAG_ID$