/* Patches */ 
SELECT qry.*
  FROM (   
  
		 SELECT pp.PATCH_ID AS PPATCH_ID,
		 		0 AS PATCH_ID,
		        pp.INSTALL_ORDER,
                pkg.PKG_NAME,
                pv.PKG_VERSION,
                pv.COMMENTS,
                pv.OBSOLETE_COMMENTS,
                pv.DLOCKED,
                DECODE( pv.dlocked, 'Y', pv.modified_stamp, NULL ) AS release_stamp, 
                pv.IS_PATCH,
                pv.is_obsolete,
                usr.FULL_NAME,
                usr.USER_EMAIL
		   FROM PACKAGE_PATCHES pp,
           	    PACKAGES pkg,
                PACKAGE_VERSIONS pv,
                USERS usr
		  WHERE pp.PV_ID = :PV_ID
            AND pv.PKG_ID = pkg.PKG_ID
            AND pp.PATCH_ID = pv.PV_ID
            AND pv.MODIFIER_ID = usr.USER_ID
			AND ( ( NVL( pv.IS_OBSOLETE, 'N' ) = 'N' AND :HIDE_OBSOLETE = 'Y' ) OR
				  ( NVL( pv.IS_OBSOLETE, 'N' ) = NVL( pv.IS_OBSOLETE, 'N' ) AND :HIDE_OBSOLETE = 'N' ) 
				) 
			
			
          
		UNION  
                   
        
		SELECT pdep.PV_ID AS PPATCH_ID,
			   pdep.DPV_ID AS PATCH_ID,
		       pp.INSTALL_ORDER AS INSTALL_ORDER,
               pkg.PKG_NAME,
               pv.PKG_VERSION,
               pv.COMMENTS,
               pv.OBSOLETE_COMMENTS,
               pv.DLOCKED,
               DECODE( pv.dlocked, 'Y', pv.modified_stamp, NULL ) AS release_stamp, 
               pv.IS_PATCH,
               pv.is_obsolete,
               usr.FULL_NAME,
               usr.USER_EMAIL
		  FROM PACKAGE_DEPENDENCIES pdep,
		  	   (
		         SELECT pp.PV_ID, pp.PATCH_ID, pp.INSTALL_ORDER
				   FROM PACKAGE_PATCHES pp
				  WHERE pp.PV_ID = :PV_ID 
		       ) pp,
               PACKAGES pkg,
               PACKAGE_VERSIONS pv,
               USERS usr
		 WHERE pdep.PV_ID = pp.PATCH_ID     
           AND pv.PKG_ID = pkg.PKG_ID
           AND pdep.DPV_ID = pv.PV_ID
           AND pv.MODIFIER_ID = usr.USER_ID
		   AND ( ( NVL( pv.IS_OBSOLETE, 'N' ) = 'N' AND :HIDE_OBSOLETE = 'Y' ) OR
				  ( NVL( pv.IS_OBSOLETE, 'N' ) = NVL( pv.IS_OBSOLETE, 'N' ) AND :HIDE_OBSOLETE = 'N' ) 
				) 
           
  		) qry  
 ORDER BY qry.INSTALL_ORDER, qry.PATCH_ID         
