Subversion Repositories DevTools

Rev

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

Rev Author Line No. Line
13 rsolanki 1 
/* Bom Removed Product Location */
2 
SELECT newloc.*
3 
  FROM (
4 
 
5 
  		SELECT osc.PROD_ID,
6 
			   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
7 
			   nn.NODE_ID,
8 
			   nn.NODE_NAME,
9 
			   os.OS_ID,
10 
			   os.OS_NAME,
11 
			   pkg.PKG_NAME,
12 
			   pv.PKG_VERSION
13 
		  FROM OS_CONTENTS osc,
14 
		  	   OPERATING_SYSTEMS os,
15 
			   NETWORK_NODES nn,
16 
			   BOM_CONTENTS bc,
17 
			   PACKAGE_VERSIONS pv,
18 
			   PACKAGES pkg
19 
		 WHERE osc.OS_ID = os.OS_ID
20 
		   AND os.NODE_ID = nn.NODE_ID
21 
		   AND bc.NODE_ID = nn.NODE_ID
22 
		   AND osc.PROD_ID = pv.PV_ID
23 
		   AND pv.PKG_ID = pkg.PKG_ID
24 
		   AND bc.BOM_ID = :BOM_ID
25 
		   AND pv.PV_ID = :PROD_ID
26 
 
27 
  	   ) newbom,
28 
	   (
29 
 
30 
	   	SELECT osc.PROD_ID,
31 
			   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
32 
			   nn.NODE_ID,
33 
			   nn.NODE_NAME,
34 
			   os.OS_ID,
35 
			   os.OS_NAME,
36 
			   pkg.PKG_NAME,
37 
			   pv.PKG_VERSION
38 
		  FROM OS_CONTENTS osc,
39 
		  	   OPERATING_SYSTEMS os,
40 
			   NETWORK_NODES nn,
41 
			   BOM_CONTENTS bc,
42 
			   PACKAGE_VERSIONS pv,
43 
			   PACKAGES pkg
44 
		 WHERE osc.OS_ID = os.OS_ID
45 
		   AND os.NODE_ID = nn.NODE_ID
46 
		   AND bc.NODE_ID = nn.NODE_ID
47 
		   AND osc.PROD_ID = pv.PV_ID
48 
		   AND pv.PKG_ID = pkg.PKG_ID
49 
		   AND bc.BOM_ID = :COMPARE_BOM_ID
50 
		   AND pv.PV_ID = :PROD_ID
51 
 
52 
 
53 
	   ) oldbom,
54 
	   (
55 
 
56 
	   	SELECT nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
57 
			   nt.NODE_ICON,
58 
			   nn.NODE_ID,
59 
			   nn.NODE_NAME,
60 
			   os.OS_ID,
61 
			   os.OS_NAME
62 
		  FROM OPERATING_SYSTEMS os,
63 
			   NETWORK_NODES nn,
64 
			   NODE_TYPES nt,
65 
			   BOM_CONTENTS bc
66 
		 WHERE os.NODE_ID = nn.NODE_ID
67 
		   AND bc.NODE_ID = nn.NODE_ID
68 
		   AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
69 
		   AND bc.BOM_ID = :BOM_ID
70 
 
71 
	   ) newloc
72 
 WHERE newbom.PK (+) = oldbom.PK
73 
   AND newbom.PROD_ID (+) = oldbom.PROD_ID
74 
   AND newloc.PK = oldbom.PK
75 
   AND newbom.PROD_ID IS NULL
29 jtweddle 76 
 
77 
   AND newloc.PK NOT IN (
78 
		SELECT nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
79 
		  FROM OS_CONTENTS osc,
80 
			   OPERATING_SYSTEMS os,
81 
			   NETWORK_NODES nn,
82 
			   BOM_CONTENTS bc,
83 
			   PACKAGE_VERSIONS pv,
84 
			   PACKAGES pkg
85 
		 WHERE osc.OS_ID = os.OS_ID
86 
		   AND os.NODE_ID = nn.NODE_ID
87 
		   AND bc.NODE_ID = nn.NODE_ID
88 
		   AND osc.PROD_ID = pv.PV_ID
89 
		   AND pv.PKG_ID = pkg.PKG_ID
90 
		   AND bc.BOM_ID = :BOM_ID
91 
		   AND pkg.PKG_NAME = :PKG_NAME
92 
 
93 
			   MINUS
94 
 
95 
		SELECT nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
96 
		  FROM OS_CONTENTS osc,
97 
			   OPERATING_SYSTEMS os,
98 
			   NETWORK_NODES nn,
99 
			   BOM_CONTENTS bc,
100 
			   PACKAGE_VERSIONS pv,
101 
			   PACKAGES pkg
102 
		 WHERE osc.OS_ID = os.OS_ID
103 
		   AND os.NODE_ID = nn.NODE_ID
104 
		   AND bc.NODE_ID = nn.NODE_ID
105 
		   AND osc.PROD_ID = pv.PV_ID
106 
		   AND pv.PKG_ID = pkg.PKG_ID
107 
		   AND bc.BOM_ID = :BOM_ID
108 
		   AND pkg.PKG_NAME = :PKG_NAME
109 
		   AND pv.IS_PATCH IS NOT NULL
110 
	   )
111 
ORDER BY UPPER(oldbom.NODE_NAME), UPPER(oldbom.OS_NAME)