1 package body CS_KB_REPO_PROPERTIES_PKG as
2 /* $Header: cskbrprb.pls 120.0 2005/06/01 12:47:52 appldev noship $ */
3 /*=======================================================================+
4 | Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME
8 | DESCRIPTION
9 | PL/SQL body for package: CS_KB_REPO_PROPERTIES_PKG
10 |
11 | History:
12 | 04 Apr 05 Matt Kettle Created
13 *=======================================================================*/
14 procedure INSERT_ROW (
15 X_ROWID in out nocopy VARCHAR2,
16 X_PROPERTY_ID in NUMBER,
17 X_REPOSITORY_ID in NUMBER,
18 X_PROPERTY_NAME in VARCHAR2,
19 X_PROPERTY_VALUE in VARCHAR2,
20 X_OBJECT_VERSION_NUMBER in NUMBER,
21 X_ATTRIBUTE_CATEGORY in VARCHAR2,
22 X_ATTRIBUTE1 in VARCHAR2,
23 X_ATTRIBUTE2 in VARCHAR2,
24 X_ATTRIBUTE3 in VARCHAR2,
25 X_ATTRIBUTE4 in VARCHAR2,
26 X_ATTRIBUTE5 in VARCHAR2,
27 X_ATTRIBUTE6 in VARCHAR2,
28 X_ATTRIBUTE7 in VARCHAR2,
29 X_ATTRIBUTE8 in VARCHAR2,
30 X_ATTRIBUTE9 in VARCHAR2,
31 X_ATTRIBUTE10 in VARCHAR2,
32 X_ATTRIBUTE11 in VARCHAR2,
33 X_ATTRIBUTE12 in VARCHAR2,
34 X_ATTRIBUTE13 in VARCHAR2,
35 X_ATTRIBUTE14 in VARCHAR2,
36 X_ATTRIBUTE15 in VARCHAR2,
37 X_CREATION_DATE in DATE,
38 X_CREATED_BY in NUMBER,
39 X_LAST_UPDATE_DATE in DATE,
40 X_LAST_UPDATED_BY in NUMBER,
41 X_LAST_UPDATE_LOGIN in NUMBER
42 ) is
43 cursor C is select ROWID from CS_KB_REPO_PROPERTIES
44 where PROPERTY_ID = X_PROPERTY_ID
45 ;
46 begin
47 insert into CS_KB_REPO_PROPERTIES (
48 PROPERTY_ID,
49 REPOSITORY_ID,
50 PROPERTY_NAME,
51 PROPERTY_VALUE,
52 OBJECT_VERSION_NUMBER,
53 ATTRIBUTE_CATEGORY,
54 ATTRIBUTE1,
55 ATTRIBUTE2,
56 ATTRIBUTE3,
57 ATTRIBUTE4,
58 ATTRIBUTE5,
59 ATTRIBUTE6,
60 ATTRIBUTE7,
61 ATTRIBUTE8,
62 ATTRIBUTE9,
63 ATTRIBUTE10,
64 ATTRIBUTE11,
65 ATTRIBUTE12,
66 ATTRIBUTE13,
67 ATTRIBUTE14,
68 ATTRIBUTE15,
69 CREATION_DATE,
70 CREATED_BY,
71 LAST_UPDATE_DATE,
72 LAST_UPDATED_BY,
73 LAST_UPDATE_LOGIN
74 ) values (
75 X_PROPERTY_ID,
76 X_REPOSITORY_ID,
77 X_PROPERTY_NAME,
78 X_PROPERTY_VALUE,
79 X_OBJECT_VERSION_NUMBER,
80 X_ATTRIBUTE_CATEGORY,
81 X_ATTRIBUTE1,
82 X_ATTRIBUTE2,
83 X_ATTRIBUTE3,
84 X_ATTRIBUTE4,
85 X_ATTRIBUTE5,
86 X_ATTRIBUTE6,
87 X_ATTRIBUTE7,
88 X_ATTRIBUTE8,
89 X_ATTRIBUTE9,
90 X_ATTRIBUTE10,
91 X_ATTRIBUTE11,
92 X_ATTRIBUTE12,
93 X_ATTRIBUTE13,
94 X_ATTRIBUTE14,
95 X_ATTRIBUTE15,
96 X_CREATION_DATE,
97 X_CREATED_BY,
98 X_LAST_UPDATE_DATE,
99 X_LAST_UPDATED_BY,
100 X_LAST_UPDATE_LOGIN
101 );
102
103
104 open c;
105 fetch c into X_ROWID;
106 if (c%notfound) then
107 close c;
108 raise no_data_found;
109 end if;
110 close c;
111
112 end INSERT_ROW;
113
114 procedure LOCK_ROW (
115 X_PROPERTY_ID in NUMBER,
116 X_REPOSITORY_ID in NUMBER,
117 X_PROPERTY_NAME in VARCHAR2,
118 X_PROPERTY_VALUE in VARCHAR2,
119 X_OBJECT_VERSION_NUMBER in NUMBER,
120 X_ATTRIBUTE_CATEGORY in VARCHAR2,
121 X_ATTRIBUTE1 in VARCHAR2,
122 X_ATTRIBUTE2 in VARCHAR2,
123 X_ATTRIBUTE3 in VARCHAR2,
124 X_ATTRIBUTE4 in VARCHAR2,
125 X_ATTRIBUTE5 in VARCHAR2,
126 X_ATTRIBUTE6 in VARCHAR2,
127 X_ATTRIBUTE7 in VARCHAR2,
128 X_ATTRIBUTE8 in VARCHAR2,
129 X_ATTRIBUTE9 in VARCHAR2,
130 X_ATTRIBUTE10 in VARCHAR2,
131 X_ATTRIBUTE11 in VARCHAR2,
132 X_ATTRIBUTE12 in VARCHAR2,
133 X_ATTRIBUTE13 in VARCHAR2,
134 X_ATTRIBUTE14 in VARCHAR2,
135 X_ATTRIBUTE15 in VARCHAR2
136 ) is
137 cursor c is select
138 REPOSITORY_ID,
139 PROPERTY_NAME,
140 PROPERTY_VALUE,
141 OBJECT_VERSION_NUMBER,
142 ATTRIBUTE_CATEGORY,
143 ATTRIBUTE1,
144 ATTRIBUTE2,
145 ATTRIBUTE3,
146 ATTRIBUTE4,
147 ATTRIBUTE5,
148 ATTRIBUTE6,
149 ATTRIBUTE7,
150 ATTRIBUTE8,
151 ATTRIBUTE9,
152 ATTRIBUTE10,
153 ATTRIBUTE11,
154 ATTRIBUTE12,
155 ATTRIBUTE13,
156 ATTRIBUTE14,
157 ATTRIBUTE15
158 from CS_KB_REPO_PROPERTIES
159 where PROPERTY_ID = X_PROPERTY_ID
160 for update of PROPERTY_ID nowait;
161 recinfo c%rowtype;
162
163 begin
164 open c;
165 fetch c into recinfo;
166 if (c%notfound) then
167 close c;
168 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
169 app_exception.raise_exception;
170 end if;
171 close c;
172 if ( (recinfo.REPOSITORY_ID = X_REPOSITORY_ID)
173 AND (recinfo.PROPERTY_NAME = X_PROPERTY_NAME)
174 AND ((recinfo.PROPERTY_VALUE = X_PROPERTY_VALUE)
175 OR ((recinfo.PROPERTY_VALUE is null) AND (X_PROPERTY_VALUE is null)))
176 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
177 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
178 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
179 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
180 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
181 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
182 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
183 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
184 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
185 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
186 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
187 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
188 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
189 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
190 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
191 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
192 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
193 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
194 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
195 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
196 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
197 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
198 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
199 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
200 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
201 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
202 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
203 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
204 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
205 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
206 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
207 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
208 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
209 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
210 ) then
211 null;
212 else
213 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
214 app_exception.raise_exception;
215 end if;
216
217 return;
218
219 end LOCK_ROW;
220
221 procedure UPDATE_ROW (
222 X_PROPERTY_ID in NUMBER,
223 X_REPOSITORY_ID in NUMBER,
224 X_PROPERTY_NAME in VARCHAR2,
225 X_PROPERTY_VALUE in VARCHAR2,
226 X_OBJECT_VERSION_NUMBER in NUMBER,
227 X_ATTRIBUTE_CATEGORY in VARCHAR2,
228 X_ATTRIBUTE1 in VARCHAR2,
229 X_ATTRIBUTE2 in VARCHAR2,
230 X_ATTRIBUTE3 in VARCHAR2,
231 X_ATTRIBUTE4 in VARCHAR2,
232 X_ATTRIBUTE5 in VARCHAR2,
233 X_ATTRIBUTE6 in VARCHAR2,
234 X_ATTRIBUTE7 in VARCHAR2,
235 X_ATTRIBUTE8 in VARCHAR2,
236 X_ATTRIBUTE9 in VARCHAR2,
237 X_ATTRIBUTE10 in VARCHAR2,
238 X_ATTRIBUTE11 in VARCHAR2,
239 X_ATTRIBUTE12 in VARCHAR2,
240 X_ATTRIBUTE13 in VARCHAR2,
241 X_ATTRIBUTE14 in VARCHAR2,
242 X_ATTRIBUTE15 in VARCHAR2,
243 X_LAST_UPDATE_DATE in DATE,
244 X_LAST_UPDATED_BY in NUMBER,
245 X_LAST_UPDATE_LOGIN in NUMBER
246 ) is
247 begin
248 update CS_KB_REPO_PROPERTIES set
249 REPOSITORY_ID = X_REPOSITORY_ID,
250 PROPERTY_NAME = X_PROPERTY_NAME,
251 PROPERTY_VALUE = X_PROPERTY_VALUE,
252 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
253 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
254 ATTRIBUTE1 = X_ATTRIBUTE1,
255 ATTRIBUTE2 = X_ATTRIBUTE2,
256 ATTRIBUTE3 = X_ATTRIBUTE3,
257 ATTRIBUTE4 = X_ATTRIBUTE4,
258 ATTRIBUTE5 = X_ATTRIBUTE5,
259 ATTRIBUTE6 = X_ATTRIBUTE6,
260 ATTRIBUTE7 = X_ATTRIBUTE7,
261 ATTRIBUTE8 = X_ATTRIBUTE8,
262 ATTRIBUTE9 = X_ATTRIBUTE9,
263 ATTRIBUTE10 = X_ATTRIBUTE10,
264 ATTRIBUTE11 = X_ATTRIBUTE11,
265 ATTRIBUTE12 = X_ATTRIBUTE12,
266 ATTRIBUTE13 = X_ATTRIBUTE13,
267 ATTRIBUTE14 = X_ATTRIBUTE14,
268 ATTRIBUTE15 = X_ATTRIBUTE15,
269 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
270 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
271 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
272 where PROPERTY_ID = X_PROPERTY_ID;
273
274 if (sql%notfound) then
275 raise no_data_found;
276 end if;
277
278 end UPDATE_ROW;
279
280 procedure DELETE_ROW (
281 X_PROPERTY_ID in NUMBER
282 ) is
283 begin
284
285 delete from CS_KB_REPO_PROPERTIES
286 where PROPERTY_ID = X_PROPERTY_ID;
287
288 if (sql%notfound) then
289 raise no_data_found;
290 end if;
291
292 end DELETE_ROW;
293
294
295 end CS_KB_REPO_PROPERTIES_PKG;