Subversion Repositories DevTools

/* Release Diff */
SELECT qry.*
  FROM (
       
       /* Removed Packages */
       SELECT 'R' AS CHANGE_TYPE,
              v.VIEW_ID,
              v.VIEW_NAME,
              pv.PKG_ID,
              pv.PV_ID AS PV_ID_A,
              pv.COMMENTS AS COMMENTS_A,
              pkg.PKG_NAME AS PKG_NAME_A,
              pv.PKG_VERSION AS PKG_VERSION_A,
              pv.V_NMM AS MAJOR_MINOR_A,
              get_patch_version(pv.V_MM) AS PATCH_NUMBER_A,
              get_build_number(pv.V_MM) AS BUILD_NUMBER_A,
              rc.deprecated_state AS PKG_STATE_A,
              NULL AS PV_ID_B,
              NULL AS COMMENTS_B,
              NULL AS PKG_NAME_B,
              NULL AS PKG_VERSION_B,
              NULL AS MAJOR_MINOR_B,
              NULL AS PATCH_NUMBER_B,
              NULL AS BUILD_NUMBER_B,
              NULL AS PKG_STATE_B
         FROM (
              SELECT pv.PKG_ID, pv.V_EXT
                FROM RELEASE_CONTENT rc,
                     PACKAGE_VERSIONS pv
               WHERE rc.PV_ID = pv.PV_ID
                 AND rc.RTAG_ID = :RTAG_A  
              MINUS   
              SELECT pv.PKG_ID, pv.V_EXT
                FROM RELEASE_CONTENT rc,
                     PACKAGE_VERSIONS pv
               WHERE rc.PV_ID = pv.PV_ID
                 AND rc.RTAG_ID = :RTAG_B 
              ) ad,
              RELEASE_CONTENT rc,
              PACKAGE_VERSIONS pv,
              PACKAGES pkg,
              VIEWS v
        WHERE rc.RTAG_ID = :RTAG_A
          AND rc.BASE_VIEW_ID = v.VIEW_ID
          AND rc.PV_ID = pv.PV_ID
          AND pv.PKG_ID = pkg.PKG_ID
          AND pv.PKG_ID = ad.PKG_ID
          AND NVL( pv.V_EXT, '|LINK_A_NULL|') = NVL( ad.V_EXT, '|LINK_A_NULL|') 
          AND :REMOVED IS NULL
        
       UNION
        
       /* Added Packages */   
       SELECT 'A' AS CHANGE_TYPE,
              v.VIEW_ID,
              v.VIEW_NAME,
              pv.PKG_ID,
              NULL AS PV_ID_A,
              NULL AS COMMENTS_A,
              NULL AS PKG_NAME_A,
              NULL AS PKG_VERSION_A,
              NULL AS MAJOR_MINOR_A,
              NULL AS PATCH_NUMBER_A,
              NULL AS BUILD_NUMBER_A,
              NULL AS PKG_STATE_A,
              pv.PV_ID AS PV_ID_B,
              pv.COMMENTS AS COMMENTS_B,
              pkg.PKG_NAME AS PKG_NAME_B,
              pv.PKG_VERSION AS PKG_VERSION_B,
              pv.V_NMM AS MAJOR_MINOR_B,
              get_patch_version(pv.V_MM) AS PATCH_NUMBER_B,
              get_build_number(pv.V_MM) AS BUILD_NUMBER_B,
              rc.deprecated_state as PKG_STATE_B 
         FROM (
              SELECT pv.PKG_ID, pv.V_EXT
                FROM RELEASE_CONTENT rc,
                     PACKAGE_VERSIONS pv
               WHERE rc.PV_ID = pv.PV_ID
                 AND rc.RTAG_ID = :RTAG_B  
              MINUS   
              SELECT pv.PKG_ID, pv.V_EXT
                FROM RELEASE_CONTENT rc,
                     PACKAGE_VERSIONS pv
               WHERE rc.PV_ID = pv.PV_ID
                 AND rc.RTAG_ID = :RTAG_A 
              ) ad,
              RELEASE_CONTENT rc,
              PACKAGE_VERSIONS pv,
              PACKAGES pkg,
              VIEWS v
        WHERE rc.RTAG_ID = :RTAG_B
          AND v.VIEW_ID = rc.BASE_VIEW_ID       
          AND rc.PV_ID = pv.PV_ID
          AND pv.PKG_ID = pkg.PKG_ID
          AND pv.PKG_ID = ad.PKG_ID
          AND NVL( pv.V_EXT, '|LINK_A_NULL|') = NVL( ad.V_EXT, '|LINK_A_NULL|') 
          AND :ADDED IS NULL
        
       UNION
        
       /* UPDATED Packages */
       SELECT ( CASE
                  WHEN ra.V_NMM = rb.V_NMM AND ra.patch_number = rb.patch_number AND ra.build_number != rb.build_number THEN
                    'UR'
                  WHEN ra.PV_ID < rb.PV_ID OR (ra.V_NMM = rb.V_NMM AND ra.patch_number < rb.patch_number ) THEN
                    'UW'
                  ELSE
                    'U'
                END
              ) AS CHANGE_TYPE,
              ra.VIEW_ID,
              ra.VIEW_NAME,
              ra.PKG_ID,
              ra.PV_ID AS PV_ID_A,
              ra.COMMENTS AS COMMENTS_A,
              ra.PKG_NAME AS PKG_NAME_A,
              ra.PKG_VERSION AS PKG_VERSION_A,
              ra.V_NMM AS MAJOR_MINOR_A,
              ra.patch_number AS PATCH_NUMBER_A,
              ra.build_number AS BUILD_NUMBER_A,
              ra.DEPRECATED_STATE AS PKG_STATE_A,
              rb.PV_ID AS PV_ID_B,
              rb.COMMENTS AS COMMENTS_B,
              rb.PKG_NAME AS PKG_NAME_B,
              rb.PKG_VERSION AS PKG_VERSION_B,
              rb.V_NMM AS MAJOR_MINOR_B,
              rb.patch_number AS PATCH_NUMBER_B,
              rb.build_number AS BUILD_NUMBER_B,
              rb.DEPRECATED_STATE AS PKG_STATE_B
         FROM (
              SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, v.VIEW_NAME, v.VIEW_ID, rc.DEPRECATED_STATE,
                     get_patch_version(pv.V_MM) AS patch_number,
                     get_build_number(pv.V_MM) AS build_number
                FROM RELEASE_CONTENT rc,
                     PACKAGE_VERSIONS pv,
                     PACKAGES pkg,
                     VIEWS v
               WHERE rc.PV_ID = pv.PV_ID
                 AND rc.RTAG_ID = :RTAG_A
                 AND pv.PKG_ID = pkg.PKG_ID
                 AND v.VIEW_ID  = rc.BASE_VIEW_ID
              ) ra,
              (
              SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, rc.DEPRECATED_STATE,
                     get_patch_version(pv.V_MM) AS patch_number,
                     get_build_number(pv.V_MM) AS build_number
                FROM RELEASE_CONTENT rc,
                     PACKAGE_VERSIONS pv,
                     PACKAGES pkg
               WHERE rc.PV_ID = pv.PV_ID
                 AND rc.RTAG_ID = :RTAG_B
                 AND pv.PKG_ID = pkg.PKG_ID
              ) rb
        WHERE ra.PKG_ID = rb.PKG_ID
          AND NVL( ra.V_EXT, '|LINK_A_NULL|') = NVL( rb.V_EXT, '|LINK_A_NULL|')
          AND ra.PV_ID != rb.PV_ID
          AND :UPDATED IS NULL
        
       UNION
        
       /* NOT CHANGED Packages */
       SELECT NULL AS CHANGE_TYPE,
              ra.VIEW_ID,
              ra.VIEW_NAME,
              ra.PKG_ID,
              ra.PV_ID AS PV_ID_A,
              ra.COMMENTS AS COMMENTS_A,
              ra.PKG_NAME AS PKG_NAME_A,
              ra.PKG_VERSION AS PKG_VERSION_A,
              ra.V_NMM AS MAJOR_MINOR_A,
              get_patch_version(ra.V_MM) AS PATCH_NUMBER_A,
              get_build_number(ra.V_MM) AS BUILD_NUMBER_A,
              ra.DEPRECATED_STATE as PKG_STATE_A,
              rb.PV_ID AS PV_ID_B,
              rb.COMMENTS AS COMMENTS_B,
              rb.PKG_NAME AS PKG_NAME_B,
              rb.PKG_VERSION AS PKG_VERSION_B,
              rb.V_NMM AS MAJOR_MINOR_B,
              get_patch_version(rb.V_MM) AS PATCH_NUMBER_B,
              get_build_number(rb.V_MM) AS BUILD_NUMBER_B,
              rb.DEPRECATED_STATE AS PKG_STATE_B
         FROM (
              SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, v.VIEW_NAME, v.VIEW_ID, rc.DEPRECATED_STATE
                FROM RELEASE_CONTENT rc,
                     PACKAGE_VERSIONS pv,
                     PACKAGES pkg,
                     VIEWS v
               WHERE rc.PV_ID = pv.PV_ID
                 AND rc.RTAG_ID = :RTAG_A
                 AND pv.PKG_ID = pkg.PKG_ID
                 AND v.VIEW_ID = rc.BASE_VIEW_ID
              ) ra,
              (
              SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, rc.DEPRECATED_STATE
                FROM RELEASE_CONTENT rc,
                     PACKAGE_VERSIONS pv,
                     PACKAGES pkg
               WHERE rc.PV_ID = pv.PV_ID
                 AND rc.RTAG_ID = :RTAG_B
                 AND pv.PKG_ID = pkg.PKG_ID
              ) rb
        WHERE ra.PKG_ID = rb.PKG_ID
          AND NVL( ra.V_EXT, '|LINK_A_NULL|') = NVL( rb.V_EXT, '|LINK_A_NULL|')
          AND ra.PV_ID = rb.PV_ID
          AND :NO_CHANGE IS NULL
       
    ) qry
    
ORDER BY UPPER(qry.VIEW_NAME),UPPER(qry.PKG_NAME_A)