Subversion Repositories DevTools

/* Issue List */

SELECT cq.iss_id,
           cq.iss_db,
           cq.pv_id,
           pkg.pkg_name,
           pv.pkg_version,
           DECODE( cq.iss_state,
                           1, DECODE ( isspv.status,
                                                   1, 'LIMG_FIXED',
                                                   2, 'LIMG_OLD_FIXED' ),
                           '' ) AS ICON_FIXED
  FROM 
           (
                   (
                    /* Modules updated + added in this new Product */
                        
                        /* Original Product */
                        SELECT 1 AS STATUS, dep.dpv_id
                          FROM PACKAGE_DEPENDENCIES dep
                         WHERE dep.PV_ID = :PROD_ID
                        MINUS
                        /* Previous Version */ 
                        SELECT 1 AS STATUS, dep.dpv_id
                          FROM PACKAGE_DEPENDENCIES dep
                         WHERE dep.PV_ID = :OLD_PROD_ID  
               AND :PROD_ID != :OLD_PROD_ID 
                        UNION
                        /* Product itself */
                        SELECT 1 AS STATUS, TO_NUMBER( :PROD_ID ) FROM DUAL
                        
                   )    
                   
                   UNION
                   
                   (
                        /* Modules + Products itself of other versions */
                        
                    SELECT 2 AS STATUS, dep.DPV_ID
                          FROM PACKAGE_DEPENDENCIES dep
                         WHERE dep.pv_id IN ( :OTHER_PROD_ID )
                         UNION
                        SELECT 2 AS STATUS, TO_NUMBER( :OTHER_PROD_ID ) FROM DUAL
                        
                        MINUS
                        
                        (
                    /* Modules updated + added in this new Product */
                        
                        /* Original Product */
                        SELECT 2 AS STATUS, dep.dpv_id
                          FROM PACKAGE_DEPENDENCIES dep
                         WHERE dep.PV_ID = :PROD_ID
                        MINUS
                        /* Previous Version */ 
                        SELECT 2 AS STATUS, dep.dpv_id
                          FROM PACKAGE_DEPENDENCIES dep
                         WHERE dep.PV_ID = :OLD_PROD_ID   
               AND :PROD_ID != :OLD_PROD_ID   
                        UNION
                        /* Product itself */
                        SELECT 2 AS STATUS, TO_NUMBER( :PROD_ID ) FROM DUAL
                        
                   )    
                         
                   ) 
           ) isspv,
           
           CQ_ISSUES cq,
           PACKAGE_VERSIONS pv,
           PACKAGES pkg
           
           
 WHERE isspv.dpv_id = cq.pv_id
   AND pv.pkg_id = pkg.pkg_id
   AND pv.pv_id = isspv.dpv_id