Subversion Repositories DevTools

SELECT qry.*
  FROM (
  
                /* NETWORK NODE LEVEL */
                SELECT DECODE ( COUNT( os.os_id ),
                                                0, NULL,
                                                NULL, NULL,
                                                DECODE ( parent.node_id,
                                                             :node_id, '-',
                                                                 '+' 
                                                                )
                                          ) AS expand,
                           parent.NODE_SEQ_NUM,
                           parent.node_id,
                           parent.NODE_NAME,
                           parent.NODE_ICON,
                           parent.os_id,
                           parent.OS_NAME,
                           parent.BOS_ICON,
                           parent.prod_id,
                           parent.prod_name,
                           parent.SEQ_NUM,
                           parent.CHANGE_LOG_FLAG,
                           NULL AS IS_REJECTED,
                           parent.NODE_VERSION,
                           parent.NODE_LIFE_CYCLE
                  FROM OPERATING_SYSTEMS os,
                           (    
                            SELECT nn.node_id AS node_id,
                                           nn.NODE_NAME,
                                           nt.NODE_ICON,
                                           0 AS os_id,
                                           '-' AS OS_NAME,
                                           NULL AS BOS_ICON,
                                           0 AS prod_id,
                                           '-' AS prod_name,
                                           0 AS SEQ_NUM,
                                           NULL AS CHANGE_LOG_FLAG,
                                           nn.NODE_VERSION,
                                           nn.NODE_LIFE_CYCLE,
                                           bc.SEQ_NUM AS NODE_SEQ_NUM
                                  FROM NETWORK_NODES nn,
                                           BOM_CONTENTS bc,
                                           NODE_TYPES nt                                           
                                 WHERE bc.node_id = nn.node_id
                                   AND nn.node_type_id = nt.node_type_id
                                   AND bc.bom_id IN (
                                                                          /* Ensures that node_id IS IN bom_id */
                                                                          SELECT DISTINCT bc.bom_id
                                                                            FROM BOM_CONTENTS bc
                                                                           WHERE bc.bom_id = :bom_id
                                                                             AND ( 
                                                                                            ( bc.node_id = bc.node_id AND :node_id = -1 ) OR
                                                                                                ( bc.node_id = :node_id               AND :node_id != -1 ) 
                                                                                         )  
                                                                         )
                           ) parent                                
                 WHERE os.node_id (+) = parent.node_id
                 GROUP BY  parent.NODE_SEQ_NUM,
                                   parent.node_id,
                                   parent.NODE_NAME,
                                   parent.NODE_ICON,
                                   parent.os_id,
                                   parent.OS_NAME,
                                   parent.BOS_ICON,
                                   parent.prod_id,
                                   parent.prod_name,
                                   parent.SEQ_NUM,
                                   parent.CHANGE_LOG_FLAG,
                                   parent.NODE_VERSION,
                                   parent.NODE_LIFE_CYCLE
                                   
                   
                   
                UNION
                
                
                /* OS LEVEL */
                SELECT DECODE ( COUNT( * ),
                                                0, NULL,
                                                NULL, NULL,
                                                DECODE ( parent.node_id, 
                                                                 :node_id, DECODE ( parent.os_id, 
                                                                                                    :os_id, '-',
                                                                                                        DECODE ( parent.node_id,
                                                                                                                         :node_id, DECODE ( :os_id, -1, '-', '+')
                                                                                                                   )
                                                                                                  )
                                                                )
                                          ) AS expand,
                           parent.NODE_SEQ_NUM,
                           parent.node_id,
                           parent.NODE_NAME,
                           parent.NODE_ICON,
                           parent.os_id,
                           parent.OS_NAME,
                           parent.BOS_ICON,
                           parent.prod_id,
                           parent.prod_name,
                           parent.SEQ_NUM,
                           parent.CHANGE_LOG_FLAG,
                           NULL AS IS_REJECTED,
                           parent.NODE_VERSION,
                           parent.NODE_LIFE_CYCLE
                  FROM OS_CONTENTS osc,
                           (    
                                SELECT nn.node_id AS node_id,
                                           nn.NODE_NAME,
                                           NULL AS NODE_ICON,
                                           os.os_id AS os_id,
                                           os.OS_NAME,
                                           DECODE ( bt.BOS_ICON,
                                                                NULL, 'i_generic_os.gif',
                                                                bt.BOS_ICON
                                                          ) BOS_ICON,
                                           0 AS prod_id,
                                           '-' AS prod_name,
                                           0 AS SEQ_NUM,
                                           NULL AS CHANGE_LOG_FLAG,
                                           NULL AS NODE_VERSION,
                                           0 AS NODE_LIFE_CYCLE,
                                           bc.SEQ_NUM AS NODE_SEQ_NUM
                                  FROM NETWORK_NODES nn,
                                           BOM_CONTENTS bc,
                                           OPERATING_SYSTEMS os,
                                           BASE_ENV be,
                                           OS_BASE_ENV osbe,
                                           BOS_TYPES bt
                                 WHERE bc.node_id = nn.node_id
                                   AND os.node_id = nn.node_id
                                   AND osbe.OS_ID (+) = os.OS_ID
                                   AND osbe.BASE_ENV_ID = be.base_env_id (+)
                                   AND be.BOS_ID = bt.bos_id (+)
                                   AND bc.bom_id = :bom_id  
                                   AND nn.node_id = :node_id
                           ) parent                                
                 WHERE osc.os_id (+) = parent.os_id
                 GROUP BY  parent.NODE_SEQ_NUM,
                                   parent.node_id,
                                   parent.NODE_NAME,
                                   parent.NODE_ICON,
                                   parent.os_id,
                                   parent.OS_NAME,
                                   parent.BOS_ICON,
                                   parent.prod_id,
                                   parent.prod_name,
                                   parent.SEQ_NUM,
                                   parent.CHANGE_LOG_FLAG,
                                   parent.NODE_VERSION,
                                   parent.NODE_LIFE_CYCLE 

                  
                UNION   
                 
                 
                /* PRODUCTS LEVEL */   
                SELECT NULL AS expand,
                           bc.SEQ_NUM AS NODE_SEQ_NUM,
                           nn.node_id AS node_id,
                           nn.NODE_NAME,
                           NULL AS NODE_ICON,
                           os.os_id AS os_id,
                           os.OS_NAME,
                           NULL AS BOS_ICON,
                           pv.pv_id AS prod_id,
                           pkg.pkg_name || ' ' || pv.pkg_version AS prod_name,
                           osc.SEQ_NUM,
                           osc.CHANGE_LOG_FLAG,
                           pd.IS_REJECTED,
                           NULL AS NODE_VERSION,
                           0 AS NODE_LIFE_CYCLE
                  FROM NETWORK_NODES nn,
                           BOM_CONTENTS bc,
                           OPERATING_SYSTEMS os,
                           OS_CONTENTS osc,
                           PACKAGES pkg,
                           PACKAGE_VERSIONS pv,
                           PRODUCT_DETAILS pd
                 WHERE pd.PROD_ID (+) = pv.PV_ID
                   AND bc.node_id = nn.node_id
                   AND os.node_id = nn.node_id
                   AND pv.pkg_id = pkg.pkg_id
                   AND osc.PROD_ID = pv.pv_id
                   AND osc.os_id = os.os_id
                   AND bc.bom_id = :bom_id  
                   AND nn.node_id = :node_id                       
                   AND ( 
                                   ( os.os_id = os.os_id        AND :os_id = -1 ) OR
                                   ( os.os_id = :os_id  AND :os_id != -1 ) 
                           )
                                   
                  ) qry
                    
 ORDER BY qry.NODE_SEQ_NUM, UPPER(qry.node_name), UPPER(qry.os_name), qry.seq_num