Subversion Repositories DevTools

Rev

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

Rev Author Line No. Line
13 rsolanki 1 
/* New Patches */
2 
SELECT pkg.pkg_id,
3 
	   pv.PV_ID AS PROD_ID,
4 
	   os.os_id,
5 
	   pkg.pkg_name AS PROD_NAME,
6 
	   nt.NODE_ICON,
7 
	   nn.NODE_NAME ||' / '|| os.OS_NAME ||' / ' AS PROD_LOCATION,
8 
	   pv.pkg_version AS PROD_VERSION,
9 
	   pv.dlocked,
10 
	   pv.comments
11 
  FROM
12 
	   (
13 
 
14 
		/* All patches from RM for all Products in BOM */
15 
	    SELECT os.OS_ID, pp.PV_ID, pp.PATCH_ID
16 
		  FROM OS_CONTENTS osc,
17 
		  	   PACKAGE_PATCHES pp,
18 
               OPERATING_SYSTEMS os,
19 
               BOM_CONTENTS bc,
20 
               PACKAGE_VERSIONS pv,
21 
               PACKAGE_VERSIONS pppv
22 
		 WHERE osc.PROD_ID = pp.PV_ID
23 
		   AND osc.OS_ID = os.OS_ID
24 
           AND os.NODE_ID = bc.NODE_ID
25 
           AND bc.BOM_ID = :BOM_ID
26 
           AND osc.PROD_ID = pv.PV_ID
27 
           AND pv.IS_PATCH IS NULL
28 
           AND pp.PATCH_ID = pppv.PV_ID
29 
           AND pppv.IS_OBSOLETE IS NULL
30 
 
31 
 
32 
		MINUS
33 
 
34 
        /* Bom Patches */
35 
        SELECT os.OS_ID, pp.PV_ID, pp.PATCH_ID
36 
		  FROM OS_CONTENTS osc,
37 
		  	   PACKAGE_PATCHES pp,
38 
               OPERATING_SYSTEMS os,
39 
               BOM_CONTENTS bc,
40 
               PACKAGE_VERSIONS pv
41 
		 WHERE osc.PROD_ID = pp.PATCH_ID
42 
		   AND osc.OS_ID = os.OS_ID
43 
           AND os.NODE_ID = bc.NODE_ID
44 
           AND bc.BOM_ID = :BOM_ID
45 
           AND osc.PROD_ID = pv.PV_ID
46 
           AND pv.IS_PATCH IS NOT NULL
47 
 
48 
 
49 
	   ) pp,
50 
       PACKAGES pkg,
51 
       PACKAGE_VERSIONS pv,
52 
  	   OPERATING_SYSTEMS os,
53 
	   NETWORK_NODES nn,
54 
	   NODE_TYPES nt
55 
 WHERE pp.PATCH_ID = pv.PV_ID
56 
   AND pv.pkg_id = pkg.pkg_id
57 
   AND pp.OS_ID = os.OS_ID
58 
   AND os.NODE_ID = nn.NODE_ID
59 
   AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
60 
   AND pv.DLOCKED = 'Y'
61 
ORDER BY UPPER(pkg.pkg_name), UPPER(PROD_LOCATION), UPPER(pv.pkg_version)