| 119 |
ghuddy |
1 |
SELECT rep.*
|
|
|
2 |
FROM (
|
|
|
3 |
|
|
|
4 |
/* Parents UNIT TESTS */
|
|
|
5 |
SELECT orc.BASE_VIEW_ID,
|
|
|
6 |
vi.view_name,
|
|
|
7 |
pv.DLOCKED,
|
|
|
8 |
pv.pv_id,
|
|
|
9 |
pkg.pkg_name,
|
|
|
10 |
pv.pkg_version,
|
|
|
11 |
NULL AS ddlocked,
|
|
|
12 |
TO_NUMBER(NULL) AS dpv_id, '------' AS dpkg_name, NULL AS dpkg_version,
|
|
|
13 |
ut.test_id,
|
|
|
14 |
tt.TEST_TYPE_NAME AS TEST_NAME,
|
|
|
15 |
--ut.test_name,
|
|
|
16 |
ut.TEST_SUMMARY,
|
|
|
17 |
ut.COMPLETION_DATE,
|
|
|
18 |
usr.FULL_NAME AS completed_by,
|
|
|
19 |
usr.user_email,
|
|
|
20 |
--ut.AUTO_GEN,
|
|
|
21 |
ut.RESULTS_URL,
|
|
|
22 |
ut.RESULTS_ATTACHMENT_NAME,
|
|
|
23 |
DECODE ( ut.TEST_TYPES_FK,
|
|
|
24 |
1, 'Y',
|
|
|
25 |
DECODE ( ut.TEST_TYPES_FK, NULL, 'Y', NULL )
|
|
|
26 |
) AS not_done
|
|
|
27 |
FROM unit_tests ut,
|
|
|
28 |
test_types tt,
|
|
|
29 |
package_versions pv,
|
|
|
30 |
packages pkg,
|
|
|
31 |
users usr,
|
|
|
32 |
views vi,
|
|
|
33 |
(
|
|
|
34 |
/* Packages official and unofficial without previous version */
|
|
|
35 |
SELECT rc.pv_id, rc.BASE_VIEW_ID
|
|
|
36 |
FROM release_content rc,
|
|
|
37 |
package_versions pv
|
|
|
38 |
WHERE rc.rtag_id = :RTAG_ID
|
|
|
39 |
AND rc.pv_id = pv.pv_id
|
|
|
40 |
AND ( ( pv.LAST_PV_ID IS NULL AND pv.DLOCKED = 'N' ) OR pv.dlocked = 'Y' )
|
|
|
41 |
UNION
|
|
|
42 |
/* Packages unofficial with previous version */
|
|
|
43 |
SELECT pv.LAST_PV_ID AS PV_ID, rc.BASE_VIEW_ID
|
|
|
44 |
FROM package_versions pv,
|
|
|
45 |
release_content rc
|
|
|
46 |
WHERE rc.rtag_id = :RTAG_ID
|
|
|
47 |
AND rc.pv_id = pv.pv_id
|
|
|
48 |
AND ( NOT pv.LAST_PV_ID IS NULL AND pv.DLOCKED = 'N' )
|
|
|
49 |
) orc /* Create last valid release */
|
|
|
50 |
WHERE orc.pv_id = pv.pv_id
|
|
|
51 |
AND pv.pkg_id = pkg.pkg_id
|
|
|
52 |
AND ut.TEST_TYPES_FK = tt.TEST_TYPE_ID (+)
|
|
|
53 |
AND ut.pv_id (+) = pv.pv_id
|
|
|
54 |
AND ut.COMPLETED_BY = usr.user_id (+)
|
|
|
55 |
AND orc.base_view_id = vi.view_id
|
|
|
56 |
|
|
|
57 |
UNION ALL
|
|
|
58 |
|
|
|
59 |
/* Dependencies UNIT TESTS */
|
|
|
60 |
SELECT orc.BASE_VIEW_ID, vi.view_name,
|
|
|
61 |
NULL AS dlocked,
|
|
|
62 |
dep.pv_id, pkg.pkg_name, NULL AS pkg_version,
|
|
|
63 |
dpv.DLOCKED AS ddlocked,
|
|
|
64 |
dep.dpv_id, dpkg.pkg_name AS dpkg_name, dpv.pkg_version AS dpkg_version,
|
|
|
65 |
ut.test_id,
|
|
|
66 |
tt.TEST_TYPE_NAME AS TEST_NAME,
|
|
|
67 |
--ut.test_name,
|
|
|
68 |
ut.TEST_SUMMARY,
|
|
|
69 |
ut.COMPLETION_DATE,
|
|
|
70 |
usr.FULL_NAME AS completed_by,
|
|
|
71 |
usr.user_email,
|
|
|
72 |
--ut.AUTO_GEN,
|
|
|
73 |
ut.RESULTS_URL,
|
|
|
74 |
ut.RESULTS_ATTACHMENT_NAME,
|
|
|
75 |
DECODE ( ut.TEST_TYPES_FK,
|
|
|
76 |
1, 'Y',
|
|
|
77 |
DECODE ( ut.TEST_TYPES_FK, NULL, 'Y', NULL )
|
|
|
78 |
) AS not_done
|
|
|
79 |
FROM unit_tests ut,
|
|
|
80 |
test_types tt,
|
|
|
81 |
package_versions dpv,
|
|
|
82 |
packages dpkg,
|
|
|
83 |
packages pkg,
|
|
|
84 |
package_dependencies dep,
|
|
|
85 |
users usr,
|
|
|
86 |
views vi,
|
|
|
87 |
(
|
|
|
88 |
/* Packages official and unofficial without previous version */
|
|
|
89 |
SELECT rc.pv_id, rc.BASE_VIEW_ID
|
|
|
90 |
FROM release_content rc,
|
|
|
91 |
package_versions pv
|
|
|
92 |
WHERE rc.rtag_id = :RTAG_ID
|
|
|
93 |
AND rc.pv_id = pv.pv_id
|
|
|
94 |
AND ( ( pv.LAST_PV_ID IS NULL AND pv.DLOCKED = 'N' ) OR pv.dlocked = 'Y' )
|
|
|
95 |
UNION
|
|
|
96 |
/* Packages unofficial with previous version */
|
|
|
97 |
SELECT pv.LAST_PV_ID AS PV_ID, rc.BASE_VIEW_ID
|
|
|
98 |
FROM package_versions pv,
|
|
|
99 |
release_content rc
|
|
|
100 |
WHERE rc.rtag_id = :RTAG_ID
|
|
|
101 |
AND rc.pv_id = pv.pv_id
|
|
|
102 |
AND ( NOT pv.LAST_PV_ID IS NULL AND pv.DLOCKED = 'N' )
|
|
|
103 |
) orc /* Create last valid release */
|
|
|
104 |
WHERE orc.pv_id = dep.pv_id
|
|
|
105 |
AND dep.dpv_id = dpv.pv_id
|
|
|
106 |
AND dep.PKG_ID = pkg.pkg_id
|
|
|
107 |
AND dpv.pkg_id = dpkg.pkg_id
|
|
|
108 |
AND ut.TEST_TYPES_FK = tt.TEST_TYPE_ID (+)
|
|
|
109 |
AND ut.pv_id (+) = dpv.pv_id
|
|
|
110 |
AND ut.COMPLETED_BY = usr.user_id (+)
|
|
|
111 |
AND orc.base_view_id = vi.view_id
|
|
|
112 |
AND ( /* Show Dependnecies */
|
|
|
113 |
( dpv.pv_id = dpv.pv_id AND :SHOW_DEPS = 1 ) OR
|
|
|
114 |
( dpv.pv_id = -1 AND :SHOW_DEPS = 0 )
|
|
|
115 |
)
|
|
|
116 |
|
|
|
117 |
) rep
|
|
|
118 |
WHERE
|
|
|
119 |
( /* Base View SELECTOR */
|
|
|
120 |
( rep.BASE_VIEW_ID = rep.BASE_VIEW_ID AND :BASE_VIEW = -1 ) OR
|
|
|
121 |
( rep.BASE_VIEW_ID = :BASE_VIEW AND :BASE_VIEW <> -1 )
|
|
|
122 |
)
|
|
|
123 |
AND ( /* Completeness SELECTOR */
|
|
|
124 |
( NVL( rep.not_done, 'LINK_A_NULL') = NVL( rep.not_done, 'LINK_A_NULL') AND :COMPLETENESS = 0 ) OR
|
|
|
125 |
( rep.not_done = 'Y' AND :COMPLETENESS = -1 ) OR
|
|
|
126 |
( rep.not_done IS NULL AND :COMPLETENESS = 1 )
|
|
|
127 |
)
|
|
|
128 |
AND ( /* Single Package DRILL DOWN */
|
|
|
129 |
( rep.pv_id = rep.pv_id AND :PV_ID = -1 ) OR
|
|
|
130 |
( rep.pv_id = :PV_ID AND :PV_ID <> -1 )
|
|
|
131 |
)
|
|
|
132 |
ORDER BY UPPER( rep.view_name ), UPPER( pkg_name ), UPPER( dpkg_name )
|