-- Outer select is used to order the results
select * from (
	(
	  -- Select package version and project details for packages that are in a project
	  SELECT pkg.pkg_name as pkg_name, pv.pkg_version, proj.proj_name, 'xxx' as vtree_name, rt.rtag_name, rt.rtag_id, pv.pv_id, pv.v_ext as v_ext
	  FROM package_versions pv,
		   packages pkg,
		   projects proj,
		   release_tags rt,
		   release_content rc
	 WHERE pv.pkg_id = pkg.pkg_id
	   AND rc.rtag_id = rt.rtag_id
	   AND rc.pv_id = pv.pv_id
	   and proj.PROJ_ID = rt.PROJ_ID
	   AND LOWER(pkg.pkg_name) LIKE LOWER('/*PKG_NAME*/')
	   AND NVL(LOWER(pv.v_ext),'LINK_A_NULL') LIKE NVL(LOWER('/*V_EXT*/'),'LINK_A_NULL')
	)
UNION
	(
	  -- Select package version and dummy data for all packages
	  -- Create a very low pv_id such that these appear before versions that are in a package
	  SELECT unique pkg.pkg_name as pkg_name, pv.v_ext, null as proj_name, null as vtree_name, null as rtag_name, null as rtag_id, -1 as pv_id , pv.v_ext as v_ext
	  FROM package_versions pv,
		   packages pkg
	 WHERE pv.pkg_id = pkg.pkg_id
	   AND LOWER(pkg.pkg_name) LIKE LOWER('/*PKG_NAME*/')
	   AND NVL(LOWER(pv.v_ext),'LINK_A_NULL') LIKE NVL(LOWER('/*V_EXT*/'),'LINK_A_NULL')
	)
)
ORDER BY UPPER(pkg_name), UPPER(v_ext), pv_id, UPPER(proj_name), rtag_id

