select * from (

-- Select items from the PLANND table
-- These will already be marked fro merging, possibly from a previous screen refresh
--
select rc.pv_id, v.view_name, pv.pkg_id, p.pkg_name, pv.pkg_version, pv.v_ext,
    TO_CHAR( pv.created_stamp,'DD-Mon-YYYY HH24:MI:SS' ) AS created_stamp, 
    pvc.pv_id as new_pv_id, 
    pvc.pkg_version as new_pkg_version, 
    TO_CHAR( pvc.created_stamp,'DD-Mon-YYYY HH24:MI:SS' ) AS new_created_stamp,
    TRUNC (sysdate - pvc.created_stamp) as NEW_AGE,
   'A' as eTYPE,
   pvc.created_stamp AS sortDate
    
from release_content rc, package_versions pv , package_versions pvc, packages p, views v, planned p
where rc.rtag_id = :RTAG_ID and rc.sdktag_id is null and pv.pv_id = rc.pv_id
and p.pkg_id = pv.pkg_id
and NVL(pvc.v_ext, 'isNULL') = NVL(pv.v_ext, 'isNULL')
and pvc.pkg_id = pv.pkg_id
--and pvc.created_stamp > pv.created_stamp
--and pvc.build_type != 'Y'
--and pvc.dlocked = 'Y'
and rc.base_view_id = v.view_id
and p.rtag_id = :RTAG_ID
and p.pv_id = pvc.pv_id
and p.operation ='A'

union

--  Select packages that have a more recently ceated packages
--
select rc.pv_id, v.view_name, pv.pkg_id, p.pkg_name, pv.pkg_version, pv.v_ext,
    TO_CHAR( pv.created_stamp,'DD-Mon-YYYY HH24:MI:SS' ) AS created_stamp, 
    pvc.pv_id as new_pv_id, 
    pvc.pkg_version as new_pkg_version, 
    TO_CHAR( pvc.created_stamp,'DD-Mon-YYYY HH24:MI:SS' ) AS new_created_stamp,
    TRUNC (sysdate - pvc.created_stamp) as NEW_AGE,
    '-' as eTYPE,
    pvc.created_stamp AS sortDate

from release_content rc, package_versions pv , package_versions pvc, packages p, views v
where rtag_id = :RTAG_ID and rc.sdktag_id is null and pv.pv_id = rc.pv_id
and p.pkg_id = pv.pkg_id
and NVL(pvc.v_ext, 'isNULL') = NVL(pv.v_ext, 'isNULL')
and pvc.pkg_id = pv.pkg_id
and pvc.created_stamp > pv.created_stamp
and pvc.build_type != 'Y'
and pvc.dlocked = 'Y'
and rc.base_view_id = v.view_id
)
order by UPPER(pkg_name), sortDate desc, ETYPE DESC


