Subversion Repositories DevTools

SELECT qry.*
  FROM (
  
                /* All states for this project */
                SELECT st.state_id,
                           st.state_name,
                           st.STATE_SEQ,
                           st.STATE_TYPE_ENUM,
                           TO_NUMBER(NULL) AS bom_id,
                           NULL AS BOM_NAME,
                           NULL AS BOM_VERSION,
                           NULL AS BOM_LIFECYCLE,
                           TO_DATE(NULL) AS DATE_STAMP,
                           NULL AS IS_READONLY,
                           NULL AS IS_REJECTED
                  FROM STATES st
                 WHERE st.PROJ_ID = :PROJ_ID  
                 
                UNION 
                 
                /* All boms for this branch */ 
                SELECT st.state_id,
                           st.state_name,
                           st.STATE_SEQ,
                           st.STATE_TYPE_ENUM,
                           bm.bom_id,
                           bn.BOM_NAME,
                           bm.BOM_VERSION,
                           bm.BOM_LIFECYCLE,
                           bs.DATE_STAMP,
                           bm.IS_READONLY,
                           bm.IS_REJECTED
                  FROM BOM_STATE bs,
                           BOM_NAMES bn,
                           BOMS bm,
                           STATES st
                 WHERE bs.BOM_ID = bm.bom_id
                   AND bs.STATE_ID = st.state_id
                   AND bm.bom_name_id = bn.bom_name_id
                   AND bm.branch_id IN (
                                                                  /* Ensure branch is from this project */
                                                                  SELECT br.branch_id 
                                                                    FROM DM_PROJECTS proj,
                                                                                 BRANCHES br
                                                                   WHERE br.PROJ_ID = proj.proj_id
                                                                     AND br.branch_id = :BRANCH_ID
                                                                         AND proj.proj_id = :PROJ_ID
                                                                 ) 
                   AND bs.IS_DISPLAYED = 'Y'
                   
                  ) qry
 ORDER BY       qry.STATE_SEQ,
                UPPER(qry.BOM_NAME) NULLS FIRST,
                qry.DATE_STAMP DESC,
                (qry.BOM_VERSION) DESC,
                 TO_NUMBER(qry.BOM_LIFECYCLE) DESC