SELECT rep.*
FROM (

		/* Parents UNIT TESTS */
		SELECT orc.BASE_VIEW_ID,
			   vi.view_name,
			   pv.DLOCKED,
			   pv.pv_id,
			   pkg.pkg_name, 
			   pv.pkg_version,
			   NULL AS ddlocked,
			   TO_NUMBER(NULL) AS dpv_id, '------' AS dpkg_name, NULL AS dpkg_version, 
			   ut.test_id, 
			   tt.TEST_TYPE_NAME AS TEST_NAME,
			   --ut.test_name, 
			   ut.TEST_SUMMARY, 
			   ut.COMPLETION_DATE, 
			   usr.FULL_NAME AS completed_by, 
			   usr.user_email,
			   --ut.AUTO_GEN,
			   ut.RESULTS_URL,
			   ut.RESULTS_ATTACHMENT_NAME,
			   DECODE ( ut.TEST_TYPES_FK,
			   		  	1, 'Y',
						DECODE ( ut.TEST_TYPES_FK, NULL, 'Y', NULL )
					  ) AS not_done
		  FROM unit_tests ut,
		  	   test_types tt,
			   package_versions pv,
			   packages pkg, 
			   users usr,
			   views vi,
			   (
				/* Packages official and unofficial without previous version */ 
				SELECT rc.pv_id, rc.BASE_VIEW_ID
				  FROM release_content rc,
					   package_versions pv
				 WHERE rc.rtag_id = :RTAG_ID
				   AND rc.pv_id = pv.pv_id
				   AND ( ( pv.LAST_PV_ID IS NULL AND pv.DLOCKED = 'N' ) OR pv.dlocked = 'Y' )
				 UNION
				/* Packages unofficial with previous version */ 
				SELECT pv.LAST_PV_ID AS PV_ID, rc.BASE_VIEW_ID
				  FROM package_versions pv,
				  	   release_content rc
				 WHERE rc.rtag_id = :RTAG_ID
				   AND rc.pv_id = pv.pv_id
				   AND ( NOT pv.LAST_PV_ID IS NULL AND pv.DLOCKED = 'N'  )  
		       ) orc   /* Create last valid release */
		 WHERE orc.pv_id = pv.pv_id
		   AND pv.pkg_id = pkg.pkg_id
		   AND ut.TEST_TYPES_FK = tt.TEST_TYPE_ID (+)
		   AND ut.pv_id (+) = pv.pv_id
		   AND ut.COMPLETED_BY = usr.user_id (+)
		   AND orc.base_view_id = vi.view_id
		   
		UNION ALL
		
		/* Dependencies UNIT TESTS */
		SELECT orc.BASE_VIEW_ID, vi.view_name,
			   NULL AS dlocked,
			   dep.pv_id, pkg.pkg_name, NULL AS pkg_version,
			   dpv.DLOCKED AS ddlocked,
			   dep.dpv_id, dpkg.pkg_name AS dpkg_name, dpv.pkg_version AS dpkg_version, 
			   ut.test_id, 
			   tt.TEST_TYPE_NAME AS TEST_NAME,
			   --ut.test_name, 
			   ut.TEST_SUMMARY, 
			   ut.COMPLETION_DATE, 
			   usr.FULL_NAME AS completed_by, 
			   usr.user_email,
			   --ut.AUTO_GEN,
			   ut.RESULTS_URL,
			   ut.RESULTS_ATTACHMENT_NAME,
			   DECODE ( ut.TEST_TYPES_FK,
			   		  	1, 'Y',
						DECODE ( ut.TEST_TYPES_FK, NULL, 'Y', NULL )
					  ) AS not_done
		  FROM unit_tests ut,
		  	   test_types tt,
			   package_versions dpv,
			   packages dpkg, 
			   packages pkg,
			   package_dependencies dep,
			   users usr,
			   views vi,
			   (
				/* Packages official and unofficial without previous version */ 
				SELECT rc.pv_id, rc.BASE_VIEW_ID
				  FROM release_content rc,
					   package_versions pv
				 WHERE rc.rtag_id = :RTAG_ID
				   AND rc.pv_id = pv.pv_id
				   AND ( ( pv.LAST_PV_ID IS NULL AND pv.DLOCKED = 'N' ) OR pv.dlocked = 'Y' )
				 UNION
				/* Packages unofficial with previous version */ 
				SELECT pv.LAST_PV_ID AS PV_ID, rc.BASE_VIEW_ID
				  FROM package_versions pv,
				  	   release_content rc
				 WHERE rc.rtag_id = :RTAG_ID
				   AND rc.pv_id = pv.pv_id
				   AND ( NOT pv.LAST_PV_ID IS NULL AND pv.DLOCKED = 'N'  )  
		       ) orc   /* Create last valid release */
		 WHERE orc.pv_id = dep.pv_id
		   AND dep.dpv_id = dpv.pv_id
		   AND dep.PKG_ID = pkg.pkg_id
		   AND dpv.pkg_id = dpkg.pkg_id
		   AND ut.TEST_TYPES_FK = tt.TEST_TYPE_ID (+)
		   AND ut.pv_id (+) = dpv.pv_id
		   AND ut.COMPLETED_BY = usr.user_id (+)
		   AND orc.base_view_id = vi.view_id
		   AND ( /* Show Dependnecies */
		   	     ( dpv.pv_id = dpv.pv_id AND :SHOW_DEPS = 1 ) OR
				 ( dpv.pv_id = -1 	     AND :SHOW_DEPS = 0 ) 
		   	   )
			   
 	   ) rep   
 WHERE
       ( /* Base View SELECTOR */
	   	 ( rep.BASE_VIEW_ID = rep.BASE_VIEW_ID AND :BASE_VIEW =  -1 ) OR
		 ( rep.BASE_VIEW_ID = :BASE_VIEW 	   AND :BASE_VIEW <> -1 ) 
       )   
   AND ( /* Completeness SELECTOR */
   	   	 ( NVL( rep.not_done, 'LINK_A_NULL') = NVL( rep.not_done, 'LINK_A_NULL') AND :COMPLETENESS =  0 ) OR
   	   	 ( rep.not_done = 'Y'   		 AND :COMPLETENESS = -1 ) OR
		 ( rep.not_done IS NULL 		 AND :COMPLETENESS = 1 ) 
	   )
   AND ( /* Single Package DRILL DOWN */
   		 ( rep.pv_id = rep.pv_id AND :PV_ID = -1 ) OR
		 ( rep.pv_id = :PV_ID 	 AND :PV_ID <> -1 ) 
       )
 ORDER BY UPPER( rep.view_name ), UPPER( pkg_name ), UPPER( dpkg_name )   