Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
33 ghuddy 1 
/* Bom Compare */
2 
SELECT DISTINCT
3 
 
4 
       pv.PV_ID AS PROD_ID,
5 
	   pkg.PKG_NAME,
6 
	   pv.PKG_VERSION,
7 
	   qry.CHANGE_TYPE,
8 
	   pd.IS_REJECTED,
9 
	   ( CASE
10 
	     WHEN qry.CHANGE_TYPE = 'A' THEN
11 
	   		'LIMG_ADDED'
12 
		 WHEN qry.CHANGE_TYPE = 'U' THEN
13 
			'LIMG_UPDATED'
14 
		 END ) AS DIFF_ICON,
15 
		 (
16 
		  CASE
17 
		  WHEN qry.oS_NAME = 'Solaris' THEN
18 
		  	   'sparc'
19 
		  ELSE
20 
		  	  'win32'
21 
		  END ) AS OS_NAME,
22 
		  pv.IS_PATCH
23 
 
24 
  FROM (
25 
 
26 
  		/* ADDED PATCHES ONLY */
27 
		SELECT 'A' AS CHANGE_TYPE, newbom.PROD_ID, newbom.PK, newbom.OS_NAME
28 
		  FROM (
29 
		  		SELECT osc.PROD_ID,
30 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
31 
					   OS.OS_NAME
32 
				  FROM OS_CONTENTS osc,
33 
				  	   OPERATING_SYSTEMS os,
34 
					   NETWORK_NODES nn,
35 
					   BOM_CONTENTS bc,
36 
					   PACKAGE_VERSIONS pv
37 
				 WHERE osc.OS_ID = os.OS_ID
38 
				   AND os.NODE_ID = nn.NODE_ID
39 
				   AND bc.NODE_ID = nn.NODE_ID
40 
				   AND osc.PROD_ID = pv.PV_ID
41 
				   AND NOT pv.IS_PATCH IS NULL
42 
				   AND bc.BOM_ID = :BOM_ID
43 
		  	   ) newbom,
44 
			   (
45 
			   	SELECT osc.PROD_ID,
46 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
47 
				  FROM OS_CONTENTS osc,
48 
				  	   OPERATING_SYSTEMS os,
49 
					   NETWORK_NODES nn,
50 
					   BOM_CONTENTS bc,
51 
					   PACKAGE_VERSIONS pv
52 
				 WHERE osc.OS_ID = os.OS_ID
53 
				   AND os.NODE_ID = nn.NODE_ID
54 
				   AND bc.NODE_ID = nn.NODE_ID
55 
				   AND osc.PROD_ID = pv.PV_ID
56 
				   AND NOT pv.IS_PATCH IS NULL
57 
				   AND bc.BOM_ID = :COMPARE_BOM_ID
58 
			   ) oldbom
59 
		 WHERE newbom.PK = oldbom.PK (+)
60 
		   AND newbom.PROD_ID = oldbom.PROD_ID (+)
61 
		   AND oldbom.PK IS NULL
62 
 
63 
		UNION
64 
 
65 
		/* ADDED PRODUCTS ONLY */
66 
		SELECT DECODE( oldbom.PROD_ID,
67 
					   NULL, 'A', 'U' ) AS CHANGE_TYPE,
68 
			   newbom.PROD_ID, newbom.PK, newbom.OS_NAME
69 
		  FROM (
70 
		  		SELECT osc.PROD_ID,
71 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
72 
					   pv.PKG_ID,
73 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT,
74 
					   OS.OS_NAME
75 
				  FROM OS_CONTENTS osc,
76 
				  	   OPERATING_SYSTEMS os,
77 
					   NETWORK_NODES nn,
78 
					   BOM_CONTENTS bc,
79 
					   PACKAGE_VERSIONS pv
80 
				 WHERE osc.OS_ID = os.OS_ID
81 
				   AND os.NODE_ID = nn.NODE_ID
82 
				   AND bc.NODE_ID = nn.NODE_ID
83 
				   AND osc.PROD_ID = pv.PV_ID
84 
				   AND pv.IS_PATCH IS NULL
85 
				   AND bc.BOM_ID = :BOM_ID
86 
		  	   ) newbom,
87 
			   (
88 
			   	SELECT osc.PROD_ID,
89 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
90 
					   pv.PKG_ID,
91 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
92 
				  FROM OS_CONTENTS osc,
93 
				  	   OPERATING_SYSTEMS os,
94 
					   NETWORK_NODES nn,
95 
					   BOM_CONTENTS bc,
96 
					   PACKAGE_VERSIONS pv
97 
				 WHERE osc.OS_ID = os.OS_ID
98 
				   AND os.NODE_ID = nn.NODE_ID
99 
				   AND bc.NODE_ID = nn.NODE_ID
100 
				   AND osc.PROD_ID = pv.PV_ID
101 
				   AND pv.IS_PATCH IS NULL
102 
				   AND bc.BOM_ID = :COMPARE_BOM_ID
103 
			   ) oldbom
104 
		 WHERE newbom.PK = oldbom.PK (+)
105 
		   --AND newbom.PROD_ID = oldbom.PROD_ID (+)
106 
		   AND newbom.PKG_ID  = oldbom.PKG_ID (+)
107 
		   AND newbom.V_EXT  = oldbom.V_EXT (+)
108 
		   AND ( oldbom.PROD_ID != newbom.PROD_ID  OR oldbom.PROD_ID IS NULL )
109 
		   AND newbom.PROD_ID NOT IN
110 
		   (
111 
		   	   SELECT osc.PROD_ID
112 
			   FROM OS_CONTENTS osc,
113 
			  	    OPERATING_SYSTEMS os,
114 
					NETWORK_NODES nn,
115 
					BOM_CONTENTS bc,
116 
					PACKAGE_VERSIONS pv
117 
			  WHERE osc.OS_ID = os.OS_ID
118 
				AND os.NODE_ID = nn.NODE_ID
119 
				AND bc.NODE_ID = nn.NODE_ID
120 
				AND osc.PROD_ID = pv.PV_ID
121 
				AND pv.IS_PATCH IS NULL
122 
				AND bc.BOM_ID = :COMPARE_BOM_ID
123 
		  )
124 
 
125 
 
126 
 
127 
 
128 
       ) qry,
129 
	   PACKAGE_VERSIONS pv,
130 
	   PACKAGES pkg,
131 
	   PRODUCT_DETAILS pd
132 
 WHERE qry.PROD_ID = pv.PV_ID
133 
   AND pv.PKG_ID = pkg.PKG_ID
134 
   AND pd.PROD_ID (+) = qry.PROD_ID
135 
 ORDER BY UPPER(pkg.PKG_NAME)