Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
64 jtweddle 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 
		 ELSE
15 
		 	'LIMG_REMOVED'
16 
		 END ) AS DIFF_ICON,
17 
		 pkg.PKG_ID,
18 
		 qry.node_id,
19 
		 qry.node_name,
20 
		 qry.node_icon,
21 
		 qry.os_id,
22 
		 qry.os_name,
23 
		 qry.is_obsolete
24 
  FROM (
25 
 
26 
  		/* ADDED PATCHES ONLY */
27 
		SELECT 'A' AS CHANGE_TYPE, newbom.PROD_ID, newbom.PK,
28 
		newbom.node_id, newbom.node_name, newbom.node_icon, newbom.os_id,
29 
		newbom.os_name, newbom.is_obsolete
30 
		  FROM (
31 
		  		SELECT osc.PROD_ID,
32 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
33 
					   nn.NODE_ID,
34 
					   nn.NODE_NAME,
35 
					   nt.NODE_ICON,
36 
				                 os.OS_ID,
37 
					   os.OS_NAME,
38 
					   pv.IS_OBSOLETE
39 
				  FROM OS_CONTENTS osc,
40 
				  	   OPERATING_SYSTEMS os,
41 
					   NETWORK_NODES nn,
42 
					   BOM_CONTENTS bc,
43 
					   PACKAGE_VERSIONS pv,
44 
					   NODE_TYPES nt
45 
				 WHERE osc.OS_ID = os.OS_ID
46 
				   AND os.NODE_ID = nn.NODE_ID
47 
				   AND bc.NODE_ID = nn.NODE_ID
48 
				   AND osc.PROD_ID = pv.PV_ID
49 
				   AND NOT pv.IS_PATCH IS NULL
50 
				   AND pv.IS_OBSOLETE IS NULL
51 
				   AND bc.BOM_ID = :BOM_ID
52 
				   and nt.NODE_TYPE_ID = nn.NODE_TYPE_ID
53 
		  	   ) newbom,
54 
			   (
55 
			   	SELECT osc.PROD_ID,
56 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
57 
				  FROM OS_CONTENTS osc,
58 
				  	   OPERATING_SYSTEMS os,
59 
					   NETWORK_NODES nn,
60 
					   BOM_CONTENTS bc,
61 
					   PACKAGE_VERSIONS pv,
62 
					   NODE_TYPES nt
63 
				 WHERE osc.OS_ID = os.OS_ID
64 
				   AND os.NODE_ID = nn.NODE_ID
65 
				   AND bc.NODE_ID = nn.NODE_ID
66 
				   AND osc.PROD_ID = pv.PV_ID
67 
				   AND NOT pv.IS_PATCH IS NULL
68 
				   AND bc.BOM_ID = :COMPARE_BOM_ID
69 
				   and nt.NODE_TYPE_ID = nn.NODE_TYPE_ID
70 
			   ) oldbom
71 
		 WHERE newbom.PK = oldbom.PK (+)
72 
		   AND newbom.PROD_ID = oldbom.PROD_ID (+)
73 
		   AND oldbom.PK IS NULL
74 
		   AND newbom.PROD_ID NOT IN
75 
		   (
76 
 
77 
		   SELECT pv.PV_ID AS PROD_ID
78 
           FROM bom_contents bc,
79 
                operating_systems os,
80 
				network_nodes nn,
81 
				node_types nt,
82 
                os_contents osc,
83 
                PACKAGES pkg,
84 
                package_versions pv,
85 
				product_details pd
86 
          WHERE bc.bom_id = :current_bom_id
87 
            AND os.node_id = bc.node_id
88 
            AND osc.os_id = os.os_id
89 
            AND osc.prod_id = pv.pv_id
90 
            AND pkg.pkg_id = pv.pkg_id
91 
			and pd.PROD_ID(+) = osc.PROD_ID
92 
			and nn.node_id = os.node_id
93 
			and nt.NODE_TYPE_ID = nn.NODE_TYPE_ID
94 
			and pkg.pkg_id = :PKG_ID
95 
		   )
96 
 
97 
 
98 
 
99 
       ) qry,
100 
	   PACKAGE_VERSIONS pv,
101 
	   PACKAGES pkg,
102 
	   PRODUCT_DETAILS pd
103 
 WHERE qry.PROD_ID = pv.PV_ID
104 
   AND pv.PKG_ID = pkg.PKG_ID
105 
   AND pd.PROD_ID (+) = qry.PROD_ID
106 
   AND pkg.PKG_ID = :PKG_ID
107 
 
108 
 
109 
 
110 
 
111