| 221 |
vnguyen |
1 |
CREATE PROCEDURE "RELEASE_MANAGER"."UNDEPRECATE_PACKAGE" (nPvId IN PACKAGE_VERSIONS.PV_ID%TYPE,
|
|
|
2 |
nRtagId IN DEPRECATED_PACKAGES.RTAG_ID%TYPE,
|
|
|
3 |
nPkgId IN DEPRECATED_PACKAGES.PKG_ID%TYPE,
|
|
|
4 |
nUserId IN NUMBER) IS
|
|
|
5 |
|
|
|
6 |
ext VARCHAR2(50);
|
|
|
7 |
PvIdList VARCHAR2(32767);
|
|
|
8 |
nIdCollector RELMGR_NUMBER_TAB_t := RELMGR_NUMBER_TAB_t();
|
|
|
9 |
PvId NUMBER;
|
|
|
10 |
sComments VARCHAR2(32767);
|
|
|
11 |
|
|
|
12 |
BEGIN
|
|
|
13 |
|
|
|
14 |
|
|
|
15 |
|
|
|
16 |
--Extract the package extension
|
|
|
17 |
SELECT V_EXT into ext
|
|
|
18 |
FROM PACKAGE_VERSIONS
|
|
|
19 |
WHERE PV_ID = nPvId;
|
|
|
20 |
|
|
|
21 |
--SELECT COMMENTS into sComments
|
|
|
22 |
--FROM DEPRECATED_PACKAGES
|
|
|
23 |
--WHERE RTAG_ID = nRtagId
|
|
|
24 |
--AND V_EXT = ext
|
|
|
25 |
--AND PKG_ID = nPkgId;
|
|
|
26 |
|
|
|
27 |
--SELECT PV_ID into PvIdList FROM
|
|
|
28 |
--RELEASE_CONTENT WHERE RTAG_ID = nRtagId
|
|
|
29 |
--AND PKG_STATE = 6
|
|
|
30 |
--AND PV_ID NOT IN nPvId;
|
|
|
31 |
|
|
|
32 |
|
|
|
33 |
IF ext IS NOT NULL THEN
|
|
|
34 |
-- Undeprecate Package
|
|
|
35 |
DELETE FROM DEPRECATED_PACKAGES
|
|
|
36 |
WHERE RTAG_ID = nRtagId
|
|
|
37 |
AND PKG_ID = nPkgId
|
|
|
38 |
AND V_EXT = ext;
|
|
|
39 |
|
|
|
40 |
UPDATE RELEASE_CONTENT
|
|
|
41 |
SET PKG_ID = NULL, DEPRECATED_STATE = NULL
|
|
|
42 |
WHERE RTAG_ID = nRtagId
|
|
|
43 |
AND PV_ID IN (SELECT PV.PV_ID
|
|
|
44 |
FROM PACKAGE_VERSIONS PV, PACKAGES PKG
|
|
|
45 |
WHERE PKG.PKG_ID = PV.PKG_ID
|
|
|
46 |
AND PKG.PKG_ID = nPkgId
|
|
|
47 |
AND PV.V_EXT = ext
|
|
|
48 |
UNION
|
|
|
49 |
SELECT DISTINCT
|
|
|
50 |
qry.PV_ID
|
|
|
51 |
FROM (
|
|
|
52 |
SELECT dep.*,
|
|
|
53 |
LEVEL AS LEVEL_NUM
|
|
|
54 |
FROM PACKAGE_DEPENDENCIES dep
|
|
|
55 |
START WITH dep.DPV_ID IN ( nPvId )
|
|
|
56 |
--AND dep.DPV_ID NOT IN ( SELECT PV_ID FROM RELEASE_CONTENT WHERE RTAG_ID = nRtagId AND PKG_STATE = 6 )
|
|
|
57 |
CONNECT BY PRIOR dep.PV_ID = dep.DPV_ID
|
|
|
58 |
) qry,
|
|
|
59 |
PACKAGES pkg,
|
|
|
60 |
PACKAGE_VERSIONS pv,
|
|
|
61 |
RELEASE_CONTENT rc
|
|
|
62 |
WHERE qry.PV_ID = pv.PV_ID AND pv.PKG_ID = pkg.PKG_ID
|
|
|
63 |
AND rc.RTAG_ID = nRtagId AND rc.PV_ID = qry.PV_ID
|
|
|
64 |
|
|
|
65 |
);
|
|
|
66 |
|
|
|
67 |
ELSE
|
|
|
68 |
-- Undeprecate Package
|
|
|
69 |
DELETE FROM DEPRECATED_PACKAGES
|
|
|
70 |
WHERE RTAG_ID = nRtagId
|
|
|
71 |
AND PKG_ID = nPkgId
|
|
|
72 |
AND V_EXT IS NULL;
|
|
|
73 |
|
|
|
74 |
UPDATE RELEASE_CONTENT
|
|
|
75 |
SET PKG_ID = NULL, DEPRECATED_STATE = NULL
|
|
|
76 |
WHERE RTAG_ID = nRtagId
|
|
|
77 |
AND PV_ID IN (SELECT PV.PV_ID
|
|
|
78 |
FROM PACKAGE_VERSIONS PV, PACKAGES PKG
|
|
|
79 |
WHERE PKG.PKG_ID = PV.PKG_ID
|
|
|
80 |
AND PKG.PKG_ID = nPkgId
|
|
|
81 |
AND PV.V_EXT IS NULL
|
|
|
82 |
UNION
|
|
|
83 |
SELECT DISTINCT
|
|
|
84 |
qry.PV_ID
|
|
|
85 |
FROM (
|
|
|
86 |
SELECT dep.*,
|
|
|
87 |
LEVEL AS LEVEL_NUM
|
|
|
88 |
FROM PACKAGE_DEPENDENCIES dep
|
|
|
89 |
START WITH dep.DPV_ID IN ( nPvId )
|
|
|
90 |
--AND dep.DPV_ID NOT IN ( SELECT PV_ID FROM RELEASE_CONTENT WHERE RTAG_ID = nRtagId AND PKG_STATE = 6 )
|
|
|
91 |
CONNECT BY PRIOR dep.PV_ID = dep.DPV_ID
|
|
|
92 |
) qry,
|
|
|
93 |
PACKAGES pkg,
|
|
|
94 |
PACKAGE_VERSIONS pv,
|
|
|
95 |
RELEASE_CONTENT rc
|
|
|
96 |
WHERE qry.PV_ID = pv.PV_ID AND pv.PKG_ID = pkg.PKG_ID
|
|
|
97 |
AND rc.RTAG_ID = nRtagId AND rc.PV_ID = qry.PV_ID
|
|
|
98 |
|
|
|
99 |
);
|
|
|
100 |
|
|
|
101 |
END IF;
|
|
|
102 |
|
|
|
103 |
--IF PvIdList IS NOT NULL THEN
|
|
|
104 |
|
|
|
105 |
-- nIdCollector := IN_LIST_NUMBER ( PvIdList );
|
|
|
106 |
|
|
|
107 |
-- FOR i IN 1..nIdCollector.COUNT
|
|
|
108 |
-- LOOP
|
|
|
109 |
-- PvId := nIdCollector(i);
|
|
|
110 |
|
|
|
111 |
|
|
|
112 |
|
|
|
113 |
-- Deprecate_Package (nPvId, nRtagId, nPkgId, sComments, nUserId );
|
|
|
114 |
-- END LOOP;
|
|
|
115 |
--END IF;
|
|
|
116 |
|
|
|
117 |
Rebuild_environment(nRtagId);
|
|
|
118 |
|
|
|
119 |
|
|
|
120 |
|
|
|
121 |
END Undeprecate_Package;
|
|
|
122 |
/
|