| 221 |
vnguyen |
1 |
CREATE PROCEDURE "RELEASE_MANAGER"."UPDATE_MISC_VIEW" IS
|
|
|
2 |
/******************************************************************************
|
|
|
3 |
NAME: UPDATE_MISC_VIEW
|
|
|
4 |
PURPOSE:
|
|
|
5 |
|
|
|
6 |
REVISIONS:
|
|
|
7 |
Ver Date Author Description
|
|
|
8 |
--------- ---------- --------------- ------------------------------------
|
|
|
9 |
1.0 16/03/2007 1. Created this procedure.
|
|
|
10 |
|
|
|
11 |
NOTES:
|
|
|
12 |
|
|
|
13 |
Automatically available Auto Replace Keywords:
|
|
|
14 |
Object Name: UPDATE_MISC_VIEW
|
|
|
15 |
Sysdate: 16/03/2007
|
|
|
16 |
Date and Time: 16/03/2007, 8:56:29 AM, and 16/03/2007 8:56:29 AM
|
|
|
17 |
Username: (set in TOAD Options, Procedure Editor)
|
|
|
18 |
Table Name: (set in the "New PL/SQL Object" dialog)
|
|
|
19 |
|
|
|
20 |
******************************************************************************/
|
|
|
21 |
CURSOR view_cur /*Create a record set to store the ripple data*/
|
|
|
22 |
IS
|
|
|
23 |
SELECT pkg.pkg_id, rc.base_view_id
|
|
|
24 |
FROM package_versions pv, packages pkg, release_content rc
|
|
|
25 |
where rc.rtag_id = 2362
|
|
|
26 |
and rc.pv_id = pv.pv_id
|
|
|
27 |
and pv.pkg_id = pkg.pkg_id;
|
|
|
28 |
view_rec view_cur%ROWTYPE;
|
|
|
29 |
|
|
|
30 |
|
|
|
31 |
|
|
|
32 |
BEGIN
|
|
|
33 |
OPEN view_cur;
|
|
|
34 |
|
|
|
35 |
FETCH view_cur
|
|
|
36 |
INTO view_rec;
|
|
|
37 |
|
|
|
38 |
WHILE view_cur%FOUND
|
|
|
39 |
LOOP
|
|
|
40 |
|
|
|
41 |
|
|
|
42 |
update release_content
|
|
|
43 |
set base_view_id = view_rec.base_view_id
|
|
|
44 |
where rtag_id = 8027
|
|
|
45 |
and pv_id IN
|
|
|
46 |
(
|
|
|
47 |
select pv.pv_id from release_content rc, package_versions pv
|
|
|
48 |
where rc.rtag_id = 8027
|
|
|
49 |
and pv.pv_id = rc.pv_id
|
|
|
50 |
and pv.pkg_id = view_rec.pkg_id
|
|
|
51 |
|
|
|
52 |
);
|
|
|
53 |
|
|
|
54 |
|
|
|
55 |
|
|
|
56 |
|
|
|
57 |
|
|
|
58 |
|
|
|
59 |
|
|
|
60 |
|
|
|
61 |
|
|
|
62 |
FETCH view_cur
|
|
|
63 |
INTO view_rec;
|
|
|
64 |
END LOOP;
|
|
|
65 |
|
|
|
66 |
|
|
|
67 |
|
|
|
68 |
|
|
|
69 |
|
|
|
70 |
END UPDATE_MISC_VIEW; /
|
|
|
71 |
|
|
|
72 |
CREATE PROCEDURE "RELEASE_MANAGER"."UPDATE_PACKAGE_DEPENDENCY" ( NNpv_id IN NUMBER,
|
|
|
73 |
SSdep_name IN PACKAGES.pkg_name%TYPE,
|
|
|
74 |
SSdep_version IN PACKAGE_VERSIONS.pkg_version%TYPE,
|
|
|
75 |
CCbuild_type IN PACKAGE_DEPENDENCIES.build_type%TYPE,
|
|
|
76 |
NNuser_id IN NUMBER,
|
|
|
77 |
NNdelete_old_dependency IN NUMBER
|
|
|
78 |
) IS
|
|
|
79 |
/* ---------------------------------------------------------------------------
|
|
|
80 |
Version: 3.3
|
|
|
81 |
--------------------------------------------------------------------------- */
|
|
|
82 |
|
|
|
83 |
retPV_ID NUMBER;
|
|
|
84 |
SSV_EXT PACKAGE_VERSIONS.v_ext%TYPE;
|
|
|
85 |
DepIsPatch PACKAGE_VERSIONS.is_patch%TYPE;
|
|
|
86 |
PvIsPatch PACKAGE_VERSIONS.is_patch%TYPE;
|
|
|
87 |
NNpkg_id NUMBER;
|
|
|
88 |
NNdpkg_id NUMBER;
|
|
|
89 |
|
|
|
90 |
BEGIN
|
|
|
91 |
--- Seed database with package_name and version if required ---
|
|
|
92 |
Seed_Package_Names_Versions ( SSdep_name, SSdep_version, NNuser_id, retPV_ID );
|
|
|
93 |
|
|
|
94 |
-- get v_ext,pkg_id of current dependency
|
|
|
95 |
SELECT v_ext, pkg_id, is_patch INTO SSV_EXT, NNdpkg_id, DepIsPatch
|
|
|
96 |
FROM PACKAGE_VERSIONS
|
|
|
97 |
WHERE pv_id = retPV_ID;
|
|
|
98 |
|
|
|
99 |
-- get pkg_id of parent package
|
|
|
100 |
SELECT pkg_id, is_patch INTO NNpkg_id, PvIsPatch
|
|
|
101 |
FROM PACKAGE_VERSIONS
|
|
|
102 |
WHERE pv_id = NNpv_id;
|
|
|
103 |
|
|
|
104 |
|
|
|
105 |
IF ( (PvIsPatch IS NULL) AND (DepIsPatch IS NULL) ) OR
|
|
|
106 |
( (PvIsPatch IS NOT NULL) )THEN
|
|
|
107 |
|
|
|
108 |
IF NNdelete_old_dependency = 1 THEN
|
|
|
109 |
/* Used for CUSTOM dependency add/edit */
|
|
|
110 |
|
|
|
111 |
--- Remove old dependency ---
|
|
|
112 |
IF (PvIsPatch IS NULL) THEN
|
|
|
113 |
-- Do it for Packages
|
|
|
114 |
DELETE FROM PACKAGE_DEPENDENCIES
|
|
|
115 |
WHERE ( pv_id, dpv_id, pkg_id, dpkg_id, build_type, display_order ) IN
|
|
|
116 |
(
|
|
|
117 |
SELECT dep.*
|
|
|
118 |
FROM PACKAGE_DEPENDENCIES dep,
|
|
|
119 |
PACKAGE_VERSIONS dpv,
|
|
|
120 |
PACKAGE_VERSIONS pv
|
|
|
121 |
WHERE dep.dpv_id = dpv.pv_id
|
|
|
122 |
AND dep.pv_id = NNpv_id
|
|
|
123 |
AND pv.pv_id = retPV_ID
|
|
|
124 |
AND dpv.pkg_id = pv.pkg_id
|
|
|
125 |
AND NVL(dpv.v_ext, '|LINK_A_NULL|') = NVL(SSV_EXT, '|LINK_A_NULL|')
|
|
|
126 |
);
|
|
|
127 |
ELSE
|
|
|
128 |
-- Do it for Patches
|
|
|
129 |
DELETE FROM PACKAGE_DEPENDENCIES
|
|
|
130 |
WHERE ( pv_id, dpv_id, pkg_id, dpkg_id, build_type, display_order ) IN
|
|
|
131 |
(
|
|
|
132 |
SELECT dep.*
|
|
|
133 |
FROM PACKAGE_DEPENDENCIES dep
|
|
|
134 |
WHERE dep.dpv_id = retPV_ID
|
|
|
135 |
AND dep.pv_id = NNpv_id
|
|
|
136 |
);
|
|
|
137 |
END IF;
|
|
|
138 |
|
|
|
139 |
END IF;
|
|
|
140 |
|
|
|
141 |
/* NOTE: for JATS, ANTS dependency update, it is expected that all dependencies are removed. */
|
|
|
142 |
|
|
|
143 |
--- Add new dependency ---
|
|
|
144 |
INSERT INTO PACKAGE_DEPENDENCIES ( pv_id, dpv_id, pkg_id, dpkg_id, build_type )
|
|
|
145 |
VALUES ( NNpv_id, retPV_ID, NNpkg_id, NNdpkg_id, CCbuild_type );
|
|
|
146 |
|
|
|
147 |
END IF;
|
|
|
148 |
|
|
|
149 |
END Update_Package_Dependency;
|
|
|
150 |
/
|