Subversion Repositories DevTools

/* Crumbs */
SELECT qry.*
  FROM (   
  
                /* LEVEL 1 */
                SELECT 1 AS CRUMB_LEVEL,
                           'Default.asp?' || 'proj_id=' || :PROJ_ID AS CRUMB_LINK,
                           proj.proj_name AS CRUMB_NAME
                  FROM DM_PROJECTS proj
                 WHERE proj.proj_id = :PROJ_ID    
                 
                 
                UNION
                
                /* LEVEL 2 */
                SELECT 2 AS CRUMB_LEVEL,
                           'BomStates.asp?' || 'proj_id=' || :PROJ_ID || '&branch_id=' || :BRANCH_ID AS CRUMB_LINK,
                           br.branch_name AS CRUMB_NAME
                  FROM BRANCHES br
                 WHERE br.branch_id = :BRANCH_ID
                 
                 
                UNION
                
                /* LEVEL 3 */
                SELECT 3 AS CRUMB_LEVEL,
                           'BomDefault.asp?' || 'bom_id=' || :BOM_ID AS CRUMB_LINK,
                           bn.BOM_NAME ||' '|| bo.BOM_VERSION ||'.'|| bo.BOM_LIFECYCLE AS CRUMB_NAME
                  FROM BOMS bo,
                           BOM_NAMES bn
                 WHERE bo.BOM_NAME_ID = bn.bom_name_id 
                   AND bo.bom_id = :BOM_ID  
           
           
        UNION
                
                /* LEVEL 4 */
                SELECT 4 AS CRUMB_LEVEL,
                           'NodeDefault.asp?' || 'bom_id=' || :BOM_ID || '&node_id=' || :NODE_ID AS CRUMB_LINK,
                           nn.NODE_NAME AS CRUMB_NAME
                  FROM NETWORK_NODES nn
                 WHERE nn.NODE_ID = :NODE_ID 
           
        
        UNION
                
                /* LEVEL 5 */
                SELECT 5 AS CRUMB_LEVEL,
                           'OsDefault.asp?' || 'bom_id=' || :BOM_ID || '&os_id=' || :OS_ID AS CRUMB_LINK,
                           os.OS_NAME AS CRUMB_NAME
                  FROM OPERATING_SYSTEMS os
                 WHERE os.OS_ID = :OS_ID  
         
       
        UNION
                
                /* LEVEL 6 */
                SELECT 6 AS CRUMB_LEVEL,
                           'ProdDefault.asp?' || 'bom_id=' || :BOM_ID || '&os_id=' || :OS_ID || '&prod_id=' || :PROD_ID AS CRUMB_LINK,
                           pkg.PKG_NAME ||' '|| pv.PKG_VERSION AS CRUMB_NAME
                  FROM PACKAGES pkg,
                   PACKAGE_VERSIONS pv
                 WHERE pv.PKG_ID = pkg.PKG_ID
           AND pv.PV_ID = :PROD_ID 
         
       ) qry  
 ORDER BY qry.CRUMB_LEVEL