Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
13 rsolanki 1 
 
2 
/* Bom Removed Product Location */
3 
SELECT newloc.*
4 
  FROM (
5 
 
6 
  		SELECT osc.PROD_ID,
7 
			   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
8 
			   nn.NODE_ID,
9 
			   nn.NODE_NAME,
10 
			   os.OS_ID,
11 
			   os.OS_NAME,
12 
			   pkg.PKG_NAME,
13 
			   pv.PKG_VERSION
14 
		  FROM OS_CONTENTS osc,
15 
		  	   OPERATING_SYSTEMS os,
16 
			   NETWORK_NODES nn,
17 
			   BOM_CONTENTS bc,
18 
			   PACKAGE_VERSIONS pv,
19 
			   PACKAGES pkg
20 
		 WHERE osc.OS_ID = os.OS_ID
21 
		   AND os.NODE_ID = nn.NODE_ID
22 
		   AND bc.NODE_ID = nn.NODE_ID
23 
		   AND osc.PROD_ID = pv.PV_ID
24 
		   AND pv.PKG_ID = pkg.PKG_ID
25 
		   AND bc.BOM_ID = :BOM_ID
26 
		   AND pv.PV_ID = :PROD_ID
27 
 
28 
  	   ) newbom,
29 
	   (
30 
 
31 
	   	SELECT osc.PROD_ID,
32 
			   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
33 
			   nn.NODE_ID,
34 
			   nn.NODE_NAME,
35 
			   os.OS_ID,
36 
			   os.OS_NAME,
37 
			   pkg.PKG_NAME,
38 
			   pv.PKG_VERSION
39 
		  FROM OS_CONTENTS osc,
40 
		  	   OPERATING_SYSTEMS os,
41 
			   NETWORK_NODES nn,
42 
			   BOM_CONTENTS bc,
43 
			   PACKAGE_VERSIONS pv,
44 
			   PACKAGES pkg
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 pv.PKG_ID = pkg.PKG_ID
50 
		   AND bc.BOM_ID = :COMPARE_BOM_ID
51 
		   AND pv.PV_ID = :PROD_ID
52 
 
53 
 
54 
	   ) oldbom,
55 
	   (
56 
 
57 
	   	SELECT nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
58 
			   nt.NODE_ICON,
59 
			   nn.NODE_ID,
60 
			   nn.NODE_NAME,
61 
			   os.OS_ID,
62 
			   os.OS_NAME
63 
		  FROM OPERATING_SYSTEMS os,
64 
			   NETWORK_NODES nn,
65 
			   NODE_TYPES nt,
66 
			   BOM_CONTENTS bc
67 
		 WHERE os.NODE_ID = nn.NODE_ID
68 
		   AND bc.NODE_ID = nn.NODE_ID
69 
		   AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
70 
		   AND bc.BOM_ID = :BOM_ID
71 
 
72 
	   ) newloc
73 
 WHERE newbom.PK (+) = oldbom.PK
74 
   AND newbom.PROD_ID (+) = oldbom.PROD_ID
75 
   AND newloc.PK = oldbom.PK
76 
   AND newbom.PROD_ID IS NULL
77 
ORDER BY UPPER(oldbom.NODE_NAME), UPPER(oldbom.OS_NAME)
78