Subversion Repositories DevTools

Rev

Rev 5513 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 5513 Rev 5634
Line 1... Line 1...
1 
/* Bom Removed Product Location */
1 
/* Bom Removed Product Location */
2 
SELECT newloc.*
 2 
select * from (
3 
  FROM (
 3 
    WITH newbom AS (
4
- 
 
5 
  		SELECT osc.PROD_ID,
 4 
        SELECT osc.PROD_ID,
- 
 
 5 
               CASE WHEN pkg.PKG_ID = 11151 THEN nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || 'NOTES_' || osc.PROD_ID ELSE  nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || pv.PKG_ID ||  NVL( pv.V_EXT, '|.NULL|' ) END as PK,
6 
			   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
 6 
               nt.NODE_ICON,
7 
			   nn.NODE_ID,
 7 
               nn.NODE_ID,
8 
			   nn.NODE_NAME,
 8 
               nn.NODE_NAME,
9 
			   os.OS_ID,
 9 
               os.OS_ID,
10 
			   os.OS_NAME,
 10 
               os.OS_NAME,
- 
 
 11 
               pkg.PKG_ID,
11 
			   pkg.PKG_NAME,
 12 
               pkg.PKG_NAME,
12 
			   pv.PKG_VERSION
 13 
               pv.PKG_VERSION
13 
		  FROM OS_CONTENTS osc,
 14 
          FROM OS_CONTENTS osc,
14 
		  	   OPERATING_SYSTEMS os,
 15 
               OPERATING_SYSTEMS os,
15 
			   NETWORK_NODES nn,
 16 
               NETWORK_NODES nn,
- 
 
 17 
               NODE_TYPES nt,
16 
			   BOM_CONTENTS bc,
 18 
               BOM_CONTENTS bc,
17 
			   PACKAGE_VERSIONS pv,
 19 
               PACKAGE_VERSIONS pv,
18 
			   PACKAGES pkg
 20 
               PACKAGES pkg
19 
		 WHERE osc.OS_ID = os.OS_ID
 21 
         WHERE osc.OS_ID = os.OS_ID
20 
		   AND os.NODE_ID = nn.NODE_ID
 22 
           AND os.NODE_ID = nn.NODE_ID
21 
		   AND bc.NODE_ID = nn.NODE_ID
 23 
           AND bc.NODE_ID = nn.NODE_ID
22 
		   AND osc.PROD_ID = pv.PV_ID
 24 
           AND osc.PROD_ID = pv.PV_ID
23 
		   AND pv.PKG_ID = pkg.PKG_ID
 25 
           AND pv.PKG_ID = pkg.PKG_ID
24 
		   AND bc.BOM_ID = :BOM_ID
26 
           AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
25 
		   AND pv.PV_ID = :PROD_ID
 27 
           AND bc.BOM_ID = :BOM_ID
26
28 
    ),
27 
  	   ) newbom,
 29 
    oldbom as (
28 
	   (
 - 
 
29
- 
 
30 
	   	SELECT osc.PROD_ID,
 30 
        SELECT osc.PROD_ID,
- 
 
 31 
               CASE WHEN pkg.PKG_ID = 11151 THEN nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || 'NOTES_' || osc.PROD_ID ELSE  nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || pv.PKG_ID ||  NVL( pv.V_EXT, '|.NULL|' ) END as PK,
31 
			   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
 32 
               nt.NODE_ICON,
32 
			   nn.NODE_ID,
 33 
               nn.NODE_ID,
33 
			   nn.NODE_NAME,
 34 
               nn.NODE_NAME,
34 
			   os.OS_ID,
 35 
               os.OS_ID,
35 
			   os.OS_NAME,
 36 
               os.OS_NAME,
- 
 
 37 
               pkg.PKG_ID,
36 
			   pkg.PKG_NAME,
 38 
               pkg.PKG_NAME,
37 
			   pv.PKG_VERSION
 39 
               pv.PKG_VERSION
38 
		  FROM OS_CONTENTS osc,
 40 
          FROM OS_CONTENTS osc,
39 
		  	   OPERATING_SYSTEMS os,
 41 
               OPERATING_SYSTEMS os,
40 
			   NETWORK_NODES nn,
 42 
               NETWORK_NODES nn,
- 
 
 43 
               NODE_TYPES nt,
41 
			   BOM_CONTENTS bc,
 44 
               BOM_CONTENTS bc,
42 
			   PACKAGE_VERSIONS pv,
 45 
               PACKAGE_VERSIONS pv,
43 
			   PACKAGES pkg
 46 
               PACKAGES pkg
44 
		 WHERE osc.OS_ID = os.OS_ID
 47 
         WHERE osc.OS_ID = os.OS_ID
45 
		   AND os.NODE_ID = nn.NODE_ID
 48 
           AND os.NODE_ID = nn.NODE_ID
46 
		   AND bc.NODE_ID = nn.NODE_ID
 49 
           AND bc.NODE_ID = nn.NODE_ID
47 
		   AND osc.PROD_ID = pv.PV_ID
 50 
           AND osc.PROD_ID = pv.PV_ID
48 
		   AND pv.PKG_ID = pkg.PKG_ID
 51 
           AND pv.PKG_ID = pkg.PKG_ID
49 
		   AND bc.BOM_ID = :COMPARE_BOM_ID
 52 
           AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
50 
		   AND pv.PV_ID = :PROD_ID
 53 
           AND bc.BOM_ID = :COMPARE_BOM_ID
51
- 
 
52
54 
    ),
53 
	   ) oldbom,
 55 
    newNodes as (
54 
	   (
 - 
 
55
- 
 
56 
	   	SELECT nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
 56 
      SELECT node_id from bom_contents where bom_id = :BOM_ID
57 
			   nt.NODE_ICON,
 57 
    ),
58 
			   nn.NODE_ID,
 58 
    newOS as (
59 
			   nn.NODE_NAME,
 59 
      SELECT os_id from operating_systems os, bom_contents bc where bc.bom_id = :BOM_ID and bc.node_id = os.node_id
60 
			   os.OS_ID,
 60 
    )
61 
			   os.OS_NAME
 - 
 
62 
		  FROM OPERATING_SYSTEMS os,
 61 
    SELECT oldbom.*, newbom.PROD_ID AS NPROD_ID, oldbom.PROD_ID AS OPROD_ID , newNodes.node_id as nodeExists, newOS.os_id as osExists
63 
			   NETWORK_NODES nn,
 - 
 
64 
			   NODE_TYPES nt,
 - 
 
65 
			   BOM_CONTENTS bc
 62 
    from newbom
66 
		 WHERE os.NODE_ID = nn.NODE_ID
 63 
    full outer join oldbom on newbom.PK = oldbom.PK
67 
		   AND bc.NODE_ID = nn.NODE_ID
 64 
    left outer join newNodes on newbom.node_id = newNodes.node_id
68 
		   AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
 65 
    left outer join newOS on newbom.os_id = newOS.os_id
69 
		   AND bc.BOM_ID = :BOM_ID
- 
 
70
- 
 
71 
	   ) newloc
 66 
    ) where
72 
 WHERE newbom.PK (+) = oldbom.PK
- 
 
73 
   AND newbom.PROD_ID (+) = oldbom.PROD_ID
 67 
          OPROD_ID = :PROD_ID
74 
   AND newloc.PK = oldbom.PK
 68 
          AND NPROD_ID IS NULL
75 
   AND newbom.PROD_ID IS NULL
 69 
          ORDER BY UPPER(PK)
- 
 
 70 
 
76 
 
 71 
 
77 
   AND newloc.PK NOT IN (
 - 
 
78 
		SELECT nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
 - 
 
79 
		  FROM OS_CONTENTS osc,
 - 
 
80 
			   OPERATING_SYSTEMS os,
 - 
 
81 
			   NETWORK_NODES nn,
 - 
 
82 
			   BOM_CONTENTS bc,
 - 
 
83 
			   PACKAGE_VERSIONS pv,
 - 
 
84 
			   PACKAGES pkg
 - 
 
85 
		 WHERE osc.OS_ID = os.OS_ID
 - 
 
86 
		   AND os.NODE_ID = nn.NODE_ID
 - 
 
87 
		   AND bc.NODE_ID = nn.NODE_ID
 - 
 
88 
		   AND osc.PROD_ID = pv.PV_ID
 - 
 
89 
		   AND pv.PKG_ID = pkg.PKG_ID
 - 
 
90 
		   AND bc.BOM_ID = :BOM_ID
- 
 
91 
		   AND pkg.PKG_NAME = :PKG_NAME
 - 
 
92 
 
 72 
 
93 
			   MINUS
 - 
 
94 
 
 73 
 
95 
		SELECT nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
 - 
 
96 
		  FROM OS_CONTENTS osc,
 - 
 
97 
			   OPERATING_SYSTEMS os,
 - 
 
98 
			   NETWORK_NODES nn,
 - 
 
99 
			   BOM_CONTENTS bc,
 - 
 
100 
			   PACKAGE_VERSIONS pv,
 - 
 
101 
			   PACKAGES pkg
 - 
 
102 
		 WHERE osc.OS_ID = os.OS_ID
 - 
 
103 
		   AND os.NODE_ID = nn.NODE_ID
 - 
 
104 
		   AND bc.NODE_ID = nn.NODE_ID
 - 
 
105 
		   AND osc.PROD_ID = pv.PV_ID
 - 
 
106 
		   AND pv.PKG_ID = pkg.PKG_ID
 - 
 
107 
		   AND bc.BOM_ID = :BOM_ID
- 
 
108 
		   AND pkg.PKG_NAME = :PKG_NAME
 - 
 
109 
		   AND pv.IS_PATCH IS NOT NULL
 - 
 
110 
	   )
 - 
 
111 
ORDER BY UPPER(oldbom.NODE_NAME), UPPER(oldbom.OS_NAME)
 -