Subversion Repositories DevTools

/* Formatted on 2006/09/12 14:03 (Formatter Plus v4.8.6) */
/* Bom Compare */
SELECT DISTINCT pv.pv_id AS prod_id, pkg.pkg_name, pv.pkg_version,
                pv.is_patch, pd.is_rejected, pkg.pkg_id
           FROM (
                 /* ADDED PATCHES ONLY */
                 SELECT 'A' AS change_type, newbom.prod_id, newbom.pk
                   FROM (SELECT osc.prod_id,
                                nn.node_name || '_' || os.os_name AS pk
                           FROM os_contents osc,
                                operating_systems os,
                                network_nodes nn,
                                bom_contents bc,
                                package_versions pv
                          WHERE osc.os_id = os.os_id
                            AND os.node_id = nn.node_id
                            AND bc.node_id = nn.node_id
                            AND osc.prod_id = pv.pv_id
                            AND NOT pv.is_patch IS NULL
                            AND pv.is_obsolete IS NULL
                            AND bc.bom_id = :bom_id) newbom,
                        (SELECT osc.prod_id,
                                nn.node_name || '_' || os.os_name AS pk
                           FROM os_contents osc,
                                operating_systems os,
                                network_nodes nn,
                                bom_contents bc,
                                package_versions pv
                          WHERE osc.os_id = os.os_id
                            AND os.node_id = nn.node_id
                            AND bc.node_id = nn.node_id
                            AND osc.prod_id = pv.pv_id
                            AND NOT pv.is_patch IS NULL
                            AND bc.bom_id = :compare_bom_id) oldbom
                  WHERE newbom.pk = oldbom.pk(+)
                    AND newbom.prod_id = oldbom.prod_id(+)
                    AND oldbom.pk IS NULL
                 UNION
                 /* ADDED PRODUCTS ONLY */
                 SELECT DECODE (oldbom.prod_id,
                                NULL, 'A',
                                'U'
                               ) AS change_type, newbom.prod_id, newbom.pk
                   FROM (SELECT osc.prod_id,
                                nn.node_name || '_' || os.os_name AS pk,
                                pv.pkg_id,
                                NVL (pv.v_ext, '|LINK_A_NULL|') AS v_ext
                           FROM os_contents osc,
                                operating_systems os,
                                network_nodes nn,
                                bom_contents bc,
                                package_versions pv
                          WHERE osc.os_id = os.os_id
                            AND os.node_id = nn.node_id
                            AND bc.node_id = nn.node_id
                            AND osc.prod_id = pv.pv_id
                            AND pv.is_patch IS NULL
                            AND bc.bom_id = :bom_id) newbom,
                        (SELECT osc.prod_id,
                                nn.node_name || '_' || os.os_name AS pk,
                                pv.pkg_id,
                                NVL (pv.v_ext, '|LINK_A_NULL|') AS v_ext
                           FROM os_contents osc,
                                operating_systems os,
                                network_nodes nn,
                                bom_contents bc,
                                package_versions pv
                          WHERE osc.os_id = os.os_id
                            AND os.node_id = nn.node_id
                            AND bc.node_id = nn.node_id
                            AND osc.prod_id = pv.pv_id
                            AND pv.is_patch IS NULL
                            AND bc.bom_id = :compare_bom_id) oldbom
                  WHERE newbom.pk = oldbom.pk(+)
                    --AND newbom.PROD_ID = oldbom.PROD_ID (+)
                    AND newbom.pkg_id = oldbom.pkg_id(+)
                    AND newbom.v_ext = oldbom.v_ext(+)
                    AND (   oldbom.prod_id != newbom.prod_id
                         OR oldbom.prod_id IS NULL
                        )
                    AND newbom.prod_id NOT IN (
                           SELECT osc.prod_id
                             FROM os_contents osc,
                                  operating_systems os,
                                  network_nodes nn,
                                  bom_contents bc,
                                  package_versions pv
                            WHERE osc.os_id = os.os_id
                              AND os.node_id = nn.node_id
                              AND bc.node_id = nn.node_id
                              AND osc.prod_id = pv.pv_id
                              AND pv.is_patch IS NULL
                              AND bc.bom_id = :compare_bom_id)
                 UNION
                 /* REMOVED PRODUCTS ONLY*/
                 SELECT 'R' AS change_type, oldbom.prod_id, oldbom.pk
                   FROM (SELECT osc.prod_id,
                                nn.node_name || '_' || os.os_name AS pk,
                                pv.pkg_id,
                                NVL (pv.v_ext, '|LINK_A_NULL|') AS v_ext
                           FROM os_contents osc,
                                operating_systems os,
                                network_nodes nn,
                                bom_contents bc,
                                package_versions pv
                          WHERE osc.os_id = os.os_id
                            AND os.node_id = nn.node_id
                            AND bc.node_id = nn.node_id
                            AND osc.prod_id = pv.pv_id
                            AND pv.is_patch IS NULL
                            AND bc.bom_id = :bom_id) newbom,
                        (SELECT osc.prod_id,
                                nn.node_name || '_' || os.os_name AS pk,
                                pv.pkg_id,
                                NVL (pv.v_ext, '|LINK_A_NULL|') AS v_ext
                           FROM os_contents osc,
                                operating_systems os,
                                network_nodes nn,
                                bom_contents bc,
                                package_versions pv
                          WHERE osc.os_id = os.os_id
                            AND os.node_id = nn.node_id
                            AND bc.node_id = nn.node_id
                            AND osc.prod_id = pv.pv_id
                            AND pv.is_patch IS NULL
                            AND bc.bom_id = :compare_bom_id) oldbom
                  WHERE newbom.pk(+) = oldbom.pk
                    --AND newbom.PROD_ID (+) = oldbom.PROD_ID
                    AND newbom.pkg_id(+) = oldbom.pkg_id
                    AND newbom.v_ext(+) = oldbom.v_ext
                    AND newbom.pk IS NULL
                 UNION
                 /* REMOVED PATCHES */
                 SELECT 'R' AS change_type, oldbom.prod_id, oldbom.pk
                   FROM (SELECT osc.prod_id,
                                nn.node_name || '_' || os.os_name AS pk
                           FROM os_contents osc,
                                operating_systems os,
                                network_nodes nn,
                                bom_contents bc,
                                package_versions pv
                          WHERE osc.os_id = os.os_id
                            AND os.node_id = nn.node_id
                            AND bc.node_id = nn.node_id
                            AND osc.prod_id = pv.pv_id
                            AND NOT pv.is_patch IS NULL
                            AND bc.bom_id = :bom_id) newbom,
                        (SELECT osc.prod_id,
                                nn.node_name || '_' || os.os_name AS pk
                           FROM os_contents osc,
                                operating_systems os,
                                network_nodes nn,
                                bom_contents bc,
                                package_versions pv
                          WHERE osc.os_id = os.os_id
                            AND os.node_id = nn.node_id
                            AND bc.node_id = nn.node_id
                            AND osc.prod_id = pv.pv_id
                            AND NOT pv.is_patch IS NULL
                            AND pv.is_obsolete IS NULL
                            AND bc.bom_id = :compare_bom_id) oldbom
                  WHERE newbom.pk(+) = oldbom.pk
                    AND newbom.prod_id(+) = oldbom.prod_id
                    AND newbom.pk IS NULL) qry,
                package_versions pv,
                PACKAGES pkg,
                product_details pd
          WHERE qry.prod_id = pv.pv_id
            AND pv.pkg_id = pkg.pkg_id
            AND pd.prod_id(+) = qry.prod_id
            AND (pd.is_rejected IS NULL OR pd.is_rejected = 'N')
MINUS
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
       ORDER BY pkg_name asc