Subversion Repositories DevTools

Rev

Rev 5513 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 5513 Rev 5634
Line 1... Line 1...
1 
/* Bom Added Product Location */
1 
/* Bom Added Product Location */
2 
SELECT newbom.*
 2 
select * from (
3 
  FROM (
 3 
    WITH newbom as
4
4 
    (
5 
  		SELECT osc.PROD_ID,
 5 
        SELECT osc.PROD_ID,
6 
			   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
 6 
               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,
7 
			   nt.NODE_ICON,
 7 
               nt.NODE_ICON,
8 
			   nn.NODE_ID,
 8 
               nn.NODE_ID,
9 
			   nn.NODE_NAME,
 9 
               nn.NODE_NAME,
10 
			   os.OS_ID,
 10 
               os.OS_ID,
11 
			   os.OS_NAME,
 11 
               os.OS_NAME,
12 
			   pkg.PKG_ID,
 12 
               pkg.PKG_ID,
13 
			   pkg.PKG_NAME,
 13 
               pkg.PKG_NAME,
14 
			   pv.PKG_VERSION
 14 
               pv.PKG_VERSION
15 
		  FROM OS_CONTENTS osc,
 15 
          FROM OS_CONTENTS osc,
16 
		  	   OPERATING_SYSTEMS os,
 16 
               OPERATING_SYSTEMS os,
17 
			   NETWORK_NODES nn,
 17 
               NETWORK_NODES nn,
18 
			   NODE_TYPES nt,
 18 
               NODE_TYPES nt,
19 
			   BOM_CONTENTS bc,
 19 
               BOM_CONTENTS bc,
20 
			   PACKAGE_VERSIONS pv,
 20 
               PACKAGE_VERSIONS pv,
21 
			   PACKAGES pkg
 21 
               PACKAGES pkg
22 
		 WHERE osc.OS_ID = os.OS_ID
 22 
         WHERE osc.OS_ID = os.OS_ID
23 
		   AND os.NODE_ID = nn.NODE_ID
 23 
           AND os.NODE_ID = nn.NODE_ID
24 
		   AND bc.NODE_ID = nn.NODE_ID
 24 
           AND bc.NODE_ID = nn.NODE_ID
25 
		   AND osc.PROD_ID = pv.PV_ID
 25 
           AND osc.PROD_ID = pv.PV_ID
26 
		   AND pv.PKG_ID = pkg.PKG_ID
 26 
           AND pv.PKG_ID = pkg.PKG_ID
27 
		   AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
 27 
           AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
28 
		   AND bc.BOM_ID = :BOM_ID
28 
           AND bc.BOM_ID = :BOM_ID
29 
		   AND pv.PV_ID = :PROD_ID
 - 
 
30
29 
    ),
31 
  	   ) newbom,
 30 
    oldbom as  (
32 
	   (
 - 
 
33
- 
 
34 
	   	SELECT osc.PROD_ID,
 31 
        SELECT osc.PROD_ID,
35 
			   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
 32 
               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,
36 
			   nn.NODE_ID,
 33 
               nn.NODE_ID,
37 
			   nn.NODE_NAME,
 34 
               nn.NODE_NAME,
38 
			   os.OS_ID,
 35 
               os.OS_ID,
39 
			   os.OS_NAME,
 36 
               os.OS_NAME,
40 
			   pkg.PKG_ID,
 37 
               pkg.PKG_ID,
41 
			   pkg.PKG_NAME,
 38 
               pkg.PKG_NAME,
42 
			   pv.PKG_VERSION
 39 
               pv.PKG_VERSION
43 
		  FROM OS_CONTENTS osc,
 40 
          FROM OS_CONTENTS osc,
44 
		  	   OPERATING_SYSTEMS os,
 41 
               OPERATING_SYSTEMS os,
45 
			   NETWORK_NODES nn,
 42 
               NETWORK_NODES nn,
46 
			   BOM_CONTENTS bc,
 43 
               BOM_CONTENTS bc,
47 
			   PACKAGE_VERSIONS pv,
 44 
               PACKAGE_VERSIONS pv,
48 
			   PACKAGES pkg
 45 
               PACKAGES pkg
49 
		 WHERE osc.OS_ID = os.OS_ID
 46 
         WHERE osc.OS_ID = os.OS_ID
50 
		   AND os.NODE_ID = nn.NODE_ID
 47 
           AND os.NODE_ID = nn.NODE_ID
51 
		   AND bc.NODE_ID = nn.NODE_ID
 48 
           AND bc.NODE_ID = nn.NODE_ID
52 
		   AND osc.PROD_ID = pv.PV_ID
 49 
           AND osc.PROD_ID = pv.PV_ID
53 
		   AND pv.PKG_ID = pkg.PKG_ID
 50 
           AND pv.PKG_ID = pkg.PKG_ID
54 
		   AND bc.BOM_ID = :COMPARE_BOM_ID
 51 
           AND bc.BOM_ID = :COMPARE_BOM_ID
55 
		   AND pv.PV_ID = :PROD_ID
 52 
               ),
- 
 
 53 
    newNodes as (
- 
 
 54 
      SELECT node_id from bom_contents where bom_id = :BOM_ID
56
55 
    ),
- 
 
 56 
    newOS as (
- 
 
 57 
      SELECT os_id from operating_systems os, bom_contents bc where bc.bom_id = :BOM_ID and bc.node_id = os.node_id
- 
 
 58 
    )
- 
 
 59 
    SELECT newbom.*, oldbom.PROD_ID AS OPROD_ID, newNodes.node_id as nodeExists, newOS.os_id as osExists
57 
	   ) oldbom
 60 
        from newbom
58 
		 WHERE newbom.PK = oldbom.PK (+)
61 
        full outer join oldbom on newbom.PK = oldbom.PK
- 
 
 62 
        left outer join newNodes on newbom.node_id = newNodes.node_id
- 
 
 63 
        left outer join newOS on newbom.os_id = newOS.os_id
- 
 
 64 
    ) where
59 
		   --AND newbom.PROD_ID = oldbom.PROD_ID (+)
 65 
      PROD_ID = :PROD_ID
60 
		   AND newbom.PKG_ID  = oldbom.PKG_ID (+)
 66 
      AND OPROD_ID IS NULL
- 
 
 67 
      ORDER BY UPPER(PK)
- 
 
 68 
 
- 
 
 69 
 
61 
 
 70 
 
62 
		   AND ( oldbom.PROD_ID IS NULL )
 - 
 
63 
		   AND newbom.PROD_ID NOT IN
- 
 
64 
		   (
 - 
 
65 
		   	   SELECT osc.PROD_ID
 - 
 
66 
			   FROM OS_CONTENTS osc,
 - 
 
67 
			  	    OPERATING_SYSTEMS os,
 - 
 
68 
					NETWORK_NODES nn,
 - 
 
69 
					BOM_CONTENTS bc,
 - 
 
70 
					PACKAGE_VERSIONS pv
 - 
 
71 
			  WHERE osc.OS_ID = os.OS_ID
 - 
 
72 
				AND os.NODE_ID = nn.NODE_ID
 - 
 
73 
				AND bc.NODE_ID = nn.NODE_ID
 - 
 
74 
				AND osc.PROD_ID = pv.PV_ID
 - 
 
75 
				AND pv.IS_PATCH IS NULL
 - 
 
76 
				AND bc.BOM_ID = :COMPARE_BOM_ID
- 
 
77 
		  )
- 
 
78 
 ORDER BY UPPER(newbom.NODE_NAME), UPPER(newbom.OS_NAME)
- 
 
79
- 
 
80
- 
 
81 

            71