/* Dependencies */
SELECT lpkg.pv_id AS dpv_link, dpv.pkg_id AS dpkg_id, dpkg.pkg_name AS dpkg_name, dpv.pv_id AS orig_pv_id, dpv.pkg_version AS dpkg_version, 
       DECODE (dpv.pkg_version, lpkg.pkg_version, NULL, lpkg.pkg_version) AS latest_pkg_version,
       DECODE (lpkg.pkg_version, NULL, -1, lpkg.pkg_state) AS pkg_state,
	   dpv.dlocked,
       lpkg.dlocked AS latest_dlocked,
       DECODE (igw.dpv_id, NULL, NULL, 'checked') AS ignore_warn,
	   igw.IS_PATCH_IGNORE,
	   dep.dpv_id AS ignore_dpv, lpkg.MODIFIED_STAMP, lmod.full_name AS modifier_name, lmod.user_email AS modifier_email,
	   dpv.is_patch,
       dpv.is_obsolete,
	   lpkg.deprecated_state
  FROM PACKAGE_DEPENDENCIES dep,
       PACKAGE_VERSIONS dpv,
       PACKAGES dpkg,
       IGNORE_WARNINGS igw,
       USERS lmod,
       (
       /* Full pkg list in release contents */
       SELECT DISTINCT pv.pkg_id, NVL(pv.v_ext,'|LINK_A_NULL|') AS v_ext, 
              pv.pkg_version, pv.pv_id, rc.pkg_state, rc.deprecated_state, pv.dlocked, pv.modifier_id, pv.MODIFIED_STAMP
         FROM PACKAGE_DEPENDENCIES dep,
              PACKAGE_VERSIONS pv,
              RELEASE_CONTENT rc
        WHERE rc.rtag_id = :RTAG_ID
          AND pv.pv_id = rc.pv_id
          AND pv.pv_id = dep.pv_id(+)
       ) lpkg 
 WHERE dpv.pkg_id = dpkg.pkg_id
   AND dep.dpv_id = dpv.pv_id
   AND igw.rtag_id(+) = :RTAG_ID
   AND igw.pv_id(+) = dep.pv_id 
   AND igw.dpv_id(+) = dep.dpv_id
   AND dep.pv_id = :PV_ID
   AND lpkg.pkg_id(+) = dpv.pkg_id
   AND lpkg.v_ext(+) = NVL(dpv.v_ext,'|LINK_A_NULL|')
   AND lpkg.modifier_id = lmod.user_id(+)
ORDER BY (dep.display_order), UPPER(dpkg.PKG_NAME)
