/* Formatted on 2006/09/01 10:11 (Formatter Plus v4.8.6) */
SELECT DISTINCT pv.pv_id AS prod_id, pkg.pkg_name, pv.pkg_version,
                pv.is_patch, pd.is_rejected, pkg.pkg_id
           FROM bom_contents bc,
                operating_systems os,
                os_contents osc,
                PACKAGES pkg,
                package_versions pv,
                product_details pd
          WHERE bc.bom_id = :current_bom
            AND os.node_id = bc.node_id
            AND osc.os_id = os.os_id
            AND osc.prod_id = pv.pv_id
            AND pkg.pkg_id = pv.pkg_id
            AND pd.prod_id(+) = osc.prod_id
            AND (pd.is_rejected IS NULL OR pd.is_rejected <> 'Y') 
            AND osc.prod_id NOT IN (
                   SELECT DISTINCT osc.prod_id
                              FROM bom_contents bc,
                                   operating_systems os,
                                   os_contents osc,
                                   PACKAGES pkg,
                                   package_versions pv,
                                   product_details pd
                             WHERE bc.bom_id = :production_bom
                               AND os.node_id = bc.node_id
                               AND osc.os_id = os.os_id
                               AND osc.prod_id = pv.pv_id
                               AND pkg.pkg_id = pv.pkg_id
                               AND pd.prod_id(+) = osc.prod_id)