Subversion Repositories DevTools

Rev

Rev 13 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
13 rsolanki 1 
/* Compare BOM With Latest From Release Manager */
2 
SELECT dmbom.pkg_id,
3 
	   dmbom.PROD_ID,
4 
	   dmbom.os_id,
5 
	   pkg.pkg_name AS PROD_NAME,
6 
	   nt.NODE_ICON,
7 
	   nn.NODE_NAME ||' / '|| os.OS_NAME ||' / ' AS PROD_LOCATION,
8 
	   dmbom.pkg_version AS PROD_VERSION,
9 
	   DECODE( dmbom.pkg_version,
10 
	   		   rml.pkg_version, NULL,
11 
			   CASE WHEN rml.pv_id < dmbom.prod_id THEN
12 
			   		'Older ('|| rml.pkg_version ||')'
13 
					ELSE
14 
					rml.pkg_version
15 
					END
16 
			 ) AS RM_LATEST_VERSION,
17 
	   rml.pv_id AS LATEST_PROD_ID,
18 
	   ( CASE WHEN rml.pv_id < dmbom.prod_id THEN
19 
	   		NULL
20 
		 ELSE
21 
			'checked'
22 
		 END ) AS CHECKED
23 
  FROM PACKAGES pkg,
24 
  	   OPERATING_SYSTEMS os,
25 
	   NETWORK_NODES nn,
26 
	   NODE_TYPES nt,
27 
  	   (
28 
		/* Latest packages in Release Manager release */
29 
		SELECT pv.pv_id, pv.pkg_id, pv.v_ext,
30 
			   pv.pkg_version
31 
		  FROM RELEASE_CONTENT rc,
32 
		  	   PACKAGE_VERSIONS pv
33 
		 WHERE rc.PV_ID = pv.PV_ID
34 
		   AND rc.RTAG_ID = :RTAG_ID
35 
  	   ) rml,
36 
	   (
37 
		/* BOM Products */
38 
		SELECT osc.PROD_ID, pv.pkg_id, pv.V_EXT, osc.OS_ID,
39 
			   pv.pkg_version
40 
		  FROM OS_CONTENTS osc,
41 
		  	   OPERATING_SYSTEMS os,
42 
			   BOM_CONTENTS bc,
43 
			   PACKAGE_VERSIONS pv
44 
		 WHERE osc.OS_ID = os.OS_ID
45 
		   AND bc.NODE_ID = os.NODE_ID
46 
		   AND osc.PROD_ID = pv.PV_ID
47 
		   AND bc.BOM_ID = :BOM_ID
48 
           AND pv.IS_PATCH IS NULL
49 
	   ) dmbom
50 
 WHERE dmbom.pkg_id = pkg.pkg_id
51 
   AND dmbom.OS_ID = os.OS_ID
52 
   AND os.NODE_ID = nn.NODE_ID
53 
   AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
54 
   AND rml.pkg_version IS NOT NULL
55 
   AND rml.pv_id != dmbom.prod_id
56 
   AND rml.pkg_id (+) = dmbom.pkg_id
57 
   /* AND NVL( rml.v_ext (+) , 'LINK_NULL_VALUE' ) = NVL( dmbom.v_ext, 'LINK_NULL_VALUE' )   */
58 
ORDER BY UPPER(pkg.pkg_name), UPPER(PROD_LOCATION)