Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
13 rsolanki 1 
/* Bom Added Product Location */
2 
SELECT newbom.*
3 
  FROM (
4 
 
5 
  		SELECT osc.PROD_ID,
6 
			   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
7 
			   nt.NODE_ICON,
8 
			   nn.NODE_ID,
9 
			   nn.NODE_NAME,
10 
			   os.OS_ID,
11 
			   os.OS_NAME,
15 rsolanki 12 
			   pkg.PKG_ID,
13 rsolanki 13 
			   pkg.PKG_NAME,
14 
			   pv.PKG_VERSION
15 
		  FROM OS_CONTENTS osc,
16 
		  	   OPERATING_SYSTEMS os,
17 
			   NETWORK_NODES nn,
18 
			   NODE_TYPES nt,
19 
			   BOM_CONTENTS bc,
20 
			   PACKAGE_VERSIONS pv,
21 
			   PACKAGES pkg
22 
		 WHERE osc.OS_ID = os.OS_ID
23 
		   AND os.NODE_ID = nn.NODE_ID
24 
		   AND bc.NODE_ID = nn.NODE_ID
25 
		   AND osc.PROD_ID = pv.PV_ID
26 
		   AND pv.PKG_ID = pkg.PKG_ID
27 
		   AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
28 
		   AND bc.BOM_ID = :BOM_ID
29 
		   AND pv.PV_ID = :PROD_ID
30 
 
31 
  	   ) newbom,
32 
	   (
33 
 
34 
	   	SELECT osc.PROD_ID,
35 
			   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
36 
			   nn.NODE_ID,
37 
			   nn.NODE_NAME,
38 
			   os.OS_ID,
39 
			   os.OS_NAME,
15 rsolanki 40 
			   pkg.PKG_ID,
13 rsolanki 41 
			   pkg.PKG_NAME,
42 
			   pv.PKG_VERSION
43 
		  FROM OS_CONTENTS osc,
44 
		  	   OPERATING_SYSTEMS os,
45 
			   NETWORK_NODES nn,
46 
			   BOM_CONTENTS bc,
47 
			   PACKAGE_VERSIONS pv,
48 
			   PACKAGES pkg
49 
		 WHERE osc.OS_ID = os.OS_ID
50 
		   AND os.NODE_ID = nn.NODE_ID
51 
		   AND bc.NODE_ID = nn.NODE_ID
52 
		   AND osc.PROD_ID = pv.PV_ID
53 
		   AND pv.PKG_ID = pkg.PKG_ID
54 
		   AND bc.BOM_ID = :COMPARE_BOM_ID
25 rsolanki 55 
		   AND pv.PV_ID = :PROD_ID
15 rsolanki 56 
 
13 rsolanki 57 
	   ) oldbom
15 rsolanki 58 
		 WHERE newbom.PK = oldbom.PK (+)
59 
		   --AND newbom.PROD_ID = oldbom.PROD_ID (+)
60 
		   AND newbom.PKG_ID  = oldbom.PKG_ID (+)
61 
 
62 
		   AND ( oldbom.PROD_ID IS NULL )
63 
		   AND newbom.PROD_ID NOT IN
64 
		   (
65 
		   	   SELECT osc.PROD_ID
66 
			   FROM OS_CONTENTS osc,
67 
			  	    OPERATING_SYSTEMS os,
68 
					NETWORK_NODES nn,
69 
					BOM_CONTENTS bc,
70 
					PACKAGE_VERSIONS pv
71 
			  WHERE osc.OS_ID = os.OS_ID
72 
				AND os.NODE_ID = nn.NODE_ID
73 
				AND bc.NODE_ID = nn.NODE_ID
74 
				AND osc.PROD_ID = pv.PV_ID
75 
				AND pv.IS_PATCH IS NULL
76 
				AND bc.BOM_ID = :COMPARE_BOM_ID
77 
		  )
13 rsolanki 78 
 ORDER BY UPPER(newbom.NODE_NAME), UPPER(newbom.OS_NAME)
79 
 
80