/* Product Versions List */
SELECT distinct qry.*, boms.bom_id
  FROM (
   
		/* Return Last 10 versions */ 
		SELECT last.*
		  FROM (
				SELECT pv.pv_id,
					   pv.pkg_version
				  FROM PACKAGE_VERSIONS pv
				 WHERE pv.pkg_id = :PKG_ID
				   AND (
				   		/* Filter other extentions */
				   		( NVL( pv.V_EXT, 'LINK_NULL' ) NOT IN ( 
																SELECT pe.EXT_NAME
																  FROM PROJECT_EXTENTIONS pe,
																  	   PROJECTS proj 
																 WHERE proj.PROJ_ID != pe.PROJ_ID
																   AND proj.PROJ_ID = :PROJ_ID
															   )   AND  :FILTER_ON = 1 )  OR
						( NVL( pv.V_EXT, 'LINK_NULL' ) = NVL( pv.V_EXT, 'LINK_NULL' )   AND  :FILTER_ON = 0 ) 
				   	    )
				 ORDER BY pv.PV_ID DESC
				) last
		 WHERE ROWNUM <= 5				
		
		UNION
		
		/* Return Date and Extention filter */		 
		SELECT pv.pv_id,
			   pv.pkg_version
		  FROM PACKAGE_VERSIONS pv
		 WHERE pv.pkg_id = :PKG_ID
		   AND (
		   		/* Filter other extentions */
		   		( NVL( pv.V_EXT, 'LINK_NULL' ) NOT IN ( 
														SELECT pe.EXT_NAME
														  FROM PROJECT_EXTENTIONS pe,
														  	   PROJECTS proj 
														 WHERE proj.PROJ_ID != pe.PROJ_ID
														   AND proj.PROJ_ID = :PROJ_ID
													   )   AND  :FILTER_ON = 1 )  OR
				( NVL( pv.V_EXT, 'LINK_NULL' ) = NVL( pv.V_EXT, 'LINK_NULL' )   AND  :FILTER_ON = 0 ) 
		   	    )
			AND (
				 /* Filter on Date Modified */
				 ( TO_DATE( TO_CHAR( pv.MODIFIED_STAMP,'DD-MON-YYYY' ),'DD-MON-YYYY' )  >=  TO_DATE( TO_CHAR( SYSDATE - 365/2,'DD-MON-YYYY' ),'DD-MON-YYYY' )  AND :FILTER_ON = 1 ) OR
				 ( TO_DATE( TO_CHAR( pv.MODIFIED_STAMP,'DD-MON-YYYY' ),'DD-MON-YYYY' )  =   TO_DATE( TO_CHAR( pv.MODIFIED_STAMP,'DD-MON-YYYY' ),'DD-MON-YYYY' )  AND :FILTER_ON = 0 )
				)	
		
		) qry,
		
		BOM_STATE bms,
		branches br,
		boms,
		os_contents osc,
		bom_contents bc,
		operating_systems os,
	              production_bom pm 
		
		
		WHERE bms.state_id = pm.state_id
		AND br.PROJ_ID = :PROJ_ID 
		AND boms.BRANCH_ID = br.BRANCH_ID
		and bms.bom_id = boms.BOM_ID
		and osc.prod_id = qry.pv_id
		and bc.BOM_ID = boms.BOM_ID
		and os.NODE_ID = bc.NODE_ID
		and os.os_id = osc.os_id
		and br.BRANCH_ID = :BRANCH_ID
		
		
		
ORDER BY boms.bom_id desc 