Subversion Repositories DevTools

SELECT rep.*
FROM (

                /* Parents UNIT TESTS */
                SELECT orc.BASE_VIEW_ID,
                           vi.view_name,
                           pv.DLOCKED,
                           pv.pv_id,
                           pkg.pkg_name, 
                           pv.pkg_version,
                           NULL AS ddlocked,
                           TO_NUMBER(NULL) AS dpv_id, '------' AS dpkg_name, NULL AS dpkg_version, 
                           ut.test_id, 
                           tt.TEST_TYPE_NAME AS TEST_NAME,
                           --ut.test_name, 
                           ut.TEST_SUMMARY, 
                           ut.COMPLETION_DATE, 
                           usr.FULL_NAME AS completed_by, 
                           usr.user_email,
                           --ut.AUTO_GEN,
                           ut.RESULTS_URL,
                           ut.RESULTS_ATTACHMENT_NAME,
                           DECODE ( ut.TEST_TYPES_FK,
                                                1, 'Y',
                                                DECODE ( ut.TEST_TYPES_FK, NULL, 'Y', NULL )
                                          ) AS not_done
                  FROM unit_tests ut,
                           test_types tt,
                           package_versions pv,
                           packages pkg, 
                           users usr,
                           views vi,
                           (
                                /* Packages official and unofficial without previous version */ 
                                SELECT rc.pv_id, rc.BASE_VIEW_ID
                                  FROM release_content rc,
                                           package_versions pv
                                 WHERE rc.rtag_id = :RTAG_ID
                                   AND rc.pv_id = pv.pv_id
                                   AND ( ( pv.LAST_PV_ID IS NULL AND pv.DLOCKED = 'N' ) OR pv.dlocked = 'Y' )
                                 UNION
                                /* Packages unofficial with previous version */ 
                                SELECT pv.LAST_PV_ID AS PV_ID, rc.BASE_VIEW_ID
                                  FROM package_versions pv,
                                           release_content rc
                                 WHERE rc.rtag_id = :RTAG_ID
                                   AND rc.pv_id = pv.pv_id
                                   AND ( NOT pv.LAST_PV_ID IS NULL AND pv.DLOCKED = 'N'  )  
                       ) orc   /* Create last valid release */
                 WHERE orc.pv_id = pv.pv_id
                   AND pv.pkg_id = pkg.pkg_id
                   AND ut.TEST_TYPES_FK = tt.TEST_TYPE_ID (+)
                   AND ut.pv_id (+) = pv.pv_id
                   AND ut.COMPLETED_BY = usr.user_id (+)
                   AND orc.base_view_id = vi.view_id
                   
                UNION ALL
                
                /* Dependencies UNIT TESTS */
                SELECT orc.BASE_VIEW_ID, vi.view_name,
                           NULL AS dlocked,
                           dep.pv_id, pkg.pkg_name, NULL AS pkg_version,
                           dpv.DLOCKED AS ddlocked,
                           dep.dpv_id, dpkg.pkg_name AS dpkg_name, dpv.pkg_version AS dpkg_version, 
                           ut.test_id, 
                           tt.TEST_TYPE_NAME AS TEST_NAME,
                           --ut.test_name, 
                           ut.TEST_SUMMARY, 
                           ut.COMPLETION_DATE, 
                           usr.FULL_NAME AS completed_by, 
                           usr.user_email,
                           --ut.AUTO_GEN,
                           ut.RESULTS_URL,
                           ut.RESULTS_ATTACHMENT_NAME,
                           DECODE ( ut.TEST_TYPES_FK,
                                                1, 'Y',
                                                DECODE ( ut.TEST_TYPES_FK, NULL, 'Y', NULL )
                                          ) AS not_done
                  FROM unit_tests ut,
                           test_types tt,
                           package_versions dpv,
                           packages dpkg, 
                           packages pkg,
                           package_dependencies dep,
                           users usr,
                           views vi,
                           (
                                /* Packages official and unofficial without previous version */ 
                                SELECT rc.pv_id, rc.BASE_VIEW_ID
                                  FROM release_content rc,
                                           package_versions pv
                                 WHERE rc.rtag_id = :RTAG_ID
                                   AND rc.pv_id = pv.pv_id
                                   AND ( ( pv.LAST_PV_ID IS NULL AND pv.DLOCKED = 'N' ) OR pv.dlocked = 'Y' )
                                 UNION
                                /* Packages unofficial with previous version */ 
                                SELECT pv.LAST_PV_ID AS PV_ID, rc.BASE_VIEW_ID
                                  FROM package_versions pv,
                                           release_content rc
                                 WHERE rc.rtag_id = :RTAG_ID
                                   AND rc.pv_id = pv.pv_id
                                   AND ( NOT pv.LAST_PV_ID IS NULL AND pv.DLOCKED = 'N'  )  
                       ) orc   /* Create last valid release */
                 WHERE orc.pv_id = dep.pv_id
                   AND dep.dpv_id = dpv.pv_id
                   AND dep.PKG_ID = pkg.pkg_id
                   AND dpv.pkg_id = dpkg.pkg_id
                   AND ut.TEST_TYPES_FK = tt.TEST_TYPE_ID (+)
                   AND ut.pv_id (+) = dpv.pv_id
                   AND ut.COMPLETED_BY = usr.user_id (+)
                   AND orc.base_view_id = vi.view_id
                   AND ( /* Show Dependnecies */
                             ( dpv.pv_id = dpv.pv_id AND :SHOW_DEPS = 1 ) OR
                                 ( dpv.pv_id = -1            AND :SHOW_DEPS = 0 ) 
                           )
                           
           ) rep   
 WHERE
       ( /* Base View SELECTOR */
                 ( rep.BASE_VIEW_ID = rep.BASE_VIEW_ID AND :BASE_VIEW =  -1 ) OR
                 ( rep.BASE_VIEW_ID = :BASE_VIEW           AND :BASE_VIEW <> -1 ) 
       )   
   AND ( /* Completeness SELECTOR */
                 ( NVL( rep.not_done, 'LINK_A_NULL') = NVL( rep.not_done, 'LINK_A_NULL') AND :COMPLETENESS =  0 ) OR
                 ( rep.not_done = 'Y'                    AND :COMPLETENESS = -1 ) OR
                 ( rep.not_done IS NULL                  AND :COMPLETENESS = 1 ) 
           )
   AND ( /* Single Package DRILL DOWN */
                 ( rep.pv_id = rep.pv_id AND :PV_ID = -1 ) OR
                 ( rep.pv_id = :PV_ID    AND :PV_ID <> -1 ) 
       )
 ORDER BY UPPER( rep.view_name ), UPPER( pkg_name ), UPPER( dpkg_name )