Subversion Repositories DevTools

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
13 rsolanki 1 
SELECT qry.*
2 
  FROM (
3 
 
4 
		/* All states for this project */
5 
		SELECT st.state_id,
6 
			   st.state_name,
7 
			   st.STATE_SEQ,
8 
			   st.STATE_TYPE_ENUM,
9 
			   TO_NUMBER(NULL) AS bom_id,
10 
			   NULL AS BOM_NAME,
11 
			   NULL AS BOM_VERSION,
12 
			   NULL AS BOM_LIFECYCLE,
13 
			   TO_DATE(NULL) AS DATE_STAMP,
14 
			   NULL AS IS_READONLY,
15 
			   NULL AS IS_REJECTED
16 
		  FROM STATES st
17 
		 WHERE st.PROJ_ID = :PROJ_ID
18 
 
19 
		UNION
20 
 
21 
		/* All boms for this branch */
22 
		SELECT st.state_id,
23 
			   st.state_name,
24 
			   st.STATE_SEQ,
25 
			   st.STATE_TYPE_ENUM,
26 
			   bm.bom_id,
27 
			   bn.BOM_NAME,
28 
			   bm.BOM_VERSION,
29 
			   bm.BOM_LIFECYCLE,
30 
			   bs.DATE_STAMP,
31 
			   bm.IS_READONLY,
32 
			   bm.IS_REJECTED
33 
		  FROM BOM_STATE bs,
34 
		  	   BOM_NAMES bn,
35 
		  	   BOMS bm,
36 
			   STATES st
37 
		 WHERE bs.BOM_ID = bm.bom_id
38 
		   AND bs.STATE_ID = st.state_id
39 
		   AND bm.bom_name_id = bn.bom_name_id
40 
		   AND bm.branch_id IN (
41 
		   	   				  	  /* Ensure branch is from this project */
42 
		   	   				  	  SELECT br.branch_id
43 
								    FROM DM_PROJECTS proj,
44 
										 BRANCHES br
45 
								   WHERE br.PROJ_ID = proj.proj_id
46 
								     AND br.branch_id = :BRANCH_ID
47 
									 AND proj.proj_id = :PROJ_ID
48 
		   	   				  	 )
49 
		   AND bs.IS_DISPLAYED = 'Y'
50 
 
51 
		  ) qry
52 
 ORDER BY   	qry.STATE_SEQ,
53 
		UPPER(qry.BOM_NAME) NULLS FIRST,
19 rsolanki 54 
		qry.DATE_STAMP DESC,
55 
		(qry.BOM_VERSION) DESC,
56 
		 TO_NUMBER(qry.BOM_LIFECYCLE) DESC
57 
 
13 rsolanki 58