[Home] [Help]
PACKAGE BODY: APPS.CS_KB_REPOSITORIES_PKG
Source
1 package body CS_KB_REPOSITORIES_PKG as
2 /* $Header: cskbrepb.pls 120.1 2005/08/09 16:20:19 mkettle 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_REPOSITORIES_PKG
10 |
11 | History:
12 | 04 Apr 05 Matt Kettle Created
13 | 05 Aug 05 Matt Kettle Added Load_Seed_Row
14 *=======================================================================*/
15
16 procedure INSERT_ROW (
17 X_ROWID in out nocopy VARCHAR2,
18 X_REPOSITORY_ID in NUMBER,
19 X_OBJECT_VERSION_NUMBER in NUMBER,
20 X_ATTRIBUTE_CATEGORY in VARCHAR2,
21 X_ATTRIBUTE1 in VARCHAR2,
22 X_ATTRIBUTE2 in VARCHAR2,
23 X_ATTRIBUTE3 in VARCHAR2,
24 X_ATTRIBUTE4 in VARCHAR2,
25 X_ATTRIBUTE5 in VARCHAR2,
26 X_ATTRIBUTE6 in VARCHAR2,
27 X_ATTRIBUTE7 in VARCHAR2,
28 X_ATTRIBUTE8 in VARCHAR2,
29 X_ATTRIBUTE9 in VARCHAR2,
30 X_ATTRIBUTE10 in VARCHAR2,
31 X_ATTRIBUTE11 in VARCHAR2,
32 X_ATTRIBUTE12 in VARCHAR2,
33 X_ATTRIBUTE13 in VARCHAR2,
34 X_ATTRIBUTE14 in VARCHAR2,
35 X_ATTRIBUTE15 in VARCHAR2,
36 X_REPOSITORY_NAME in VARCHAR2,
37 X_REPOSITORY_TYPE in VARCHAR2,
38 X_RESULT_REGION in VARCHAR2,
39 X_SEARCH_RESULT_VO in VARCHAR2,
40 X_SEARCH_RESULT_COUNT_VO in VARCHAR2,
41 X_UPDATABLE in VARCHAR2,
42 X_CREATION_DATE in DATE,
43 X_CREATED_BY in NUMBER,
44 X_LAST_UPDATE_DATE in DATE,
45 X_LAST_UPDATED_BY in NUMBER,
46 X_LAST_UPDATE_LOGIN in NUMBER
47 ) is
48 cursor C is select ROWID from CS_KB_REPOSITORIES
49 where REPOSITORY_ID = X_REPOSITORY_ID;
50 begin
51 insert into CS_KB_REPOSITORIES (
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 REPOSITORY_ID,
70 REPOSITORY_NAME,
71 REPOSITORY_TYPE,
72 RESULT_REGION,
73 SEARCH_RESULT_VO,
74 SEARCH_RESULT_COUNT_VO,
75 UPDATABLE,
76 CREATION_DATE,
77 CREATED_BY,
78 LAST_UPDATE_DATE,
79 LAST_UPDATED_BY,
80 LAST_UPDATE_LOGIN
81 ) values (
82 X_OBJECT_VERSION_NUMBER,
83 X_ATTRIBUTE_CATEGORY,
84 X_ATTRIBUTE1,
85 X_ATTRIBUTE2,
86 X_ATTRIBUTE3,
87 X_ATTRIBUTE4,
88 X_ATTRIBUTE5,
89 X_ATTRIBUTE6,
90 X_ATTRIBUTE7,
91 X_ATTRIBUTE8,
92 X_ATTRIBUTE9,
93 X_ATTRIBUTE10,
94 X_ATTRIBUTE11,
95 X_ATTRIBUTE12,
96 X_ATTRIBUTE13,
97 X_ATTRIBUTE14,
98 X_ATTRIBUTE15,
99 X_REPOSITORY_ID,
100 X_REPOSITORY_NAME,
101 X_REPOSITORY_TYPE,
102 X_RESULT_REGION,
103 X_SEARCH_RESULT_VO,
104 X_SEARCH_RESULT_COUNT_VO,
105 X_UPDATABLE,
106 X_CREATION_DATE,
107 X_CREATED_BY,
108 X_LAST_UPDATE_DATE,
109 X_LAST_UPDATED_BY,
110 X_LAST_UPDATE_LOGIN
111 );
112
113 open c;
114 fetch c into X_ROWID;
115 if (c%notfound) then
116 close c;
117 raise no_data_found;
118 end if;
119 close c;
120
121 end INSERT_ROW;
122
123 procedure LOCK_ROW (
124 X_REPOSITORY_ID in NUMBER,
125 X_OBJECT_VERSION_NUMBER in NUMBER,
126 X_ATTRIBUTE_CATEGORY in VARCHAR2,
127 X_ATTRIBUTE1 in VARCHAR2,
128 X_ATTRIBUTE2 in VARCHAR2,
129 X_ATTRIBUTE3 in VARCHAR2,
130 X_ATTRIBUTE4 in VARCHAR2,
131 X_ATTRIBUTE5 in VARCHAR2,
132 X_ATTRIBUTE6 in VARCHAR2,
133 X_ATTRIBUTE7 in VARCHAR2,
134 X_ATTRIBUTE8 in VARCHAR2,
135 X_ATTRIBUTE9 in VARCHAR2,
136 X_ATTRIBUTE10 in VARCHAR2,
137 X_ATTRIBUTE11 in VARCHAR2,
138 X_ATTRIBUTE12 in VARCHAR2,
139 X_ATTRIBUTE13 in VARCHAR2,
140 X_ATTRIBUTE14 in VARCHAR2,
141 X_ATTRIBUTE15 in VARCHAR2,
142 X_REPOSITORY_NAME in VARCHAR2,
143 X_REPOSITORY_TYPE in VARCHAR2,
144 X_RESULT_REGION in VARCHAR2,
145 X_SEARCH_RESULT_VO in VARCHAR2,
146 X_SEARCH_RESULT_COUNT_VO in VARCHAR2,
147 X_UPDATABLE in VARCHAR2
148 ) is
149 cursor c is select
150 OBJECT_VERSION_NUMBER,
151 ATTRIBUTE_CATEGORY,
152 ATTRIBUTE1,
153 ATTRIBUTE2,
154 ATTRIBUTE3,
155 ATTRIBUTE4,
156 ATTRIBUTE5,
157 ATTRIBUTE6,
158 ATTRIBUTE7,
159 ATTRIBUTE8,
160 ATTRIBUTE9,
161 ATTRIBUTE10,
162 ATTRIBUTE11,
163 ATTRIBUTE12,
164 ATTRIBUTE13,
165 ATTRIBUTE14,
166 ATTRIBUTE15,
167 REPOSITORY_NAME,
168 REPOSITORY_TYPE,
169 RESULT_REGION,
170 SEARCH_RESULT_VO,
171 SEARCH_RESULT_COUNT_VO,
172 UPDATABLE
173 from CS_KB_REPOSITORIES
174 where REPOSITORY_ID = X_REPOSITORY_ID
175 for update of REPOSITORY_ID nowait;
176
177 recinfo c%rowtype;
178
179 begin
180 open c;
181 fetch c into recinfo;
182 if (c%notfound) then
183 close c;
184 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
185 app_exception.raise_exception;
186 end if;
187 close c;
188 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
189 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
190 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
191 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
192 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
193 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
194 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
195 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
196 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
197 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
198 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
199 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
200 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
201 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
202 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
203 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
204 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
205 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
206 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
207 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
208 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
209 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
210 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
211 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
212 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
213 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
214 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
215 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
216 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
217 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
218 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
219 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
220 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
221 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
222 AND (recinfo.REPOSITORY_NAME = X_REPOSITORY_NAME)
223 AND (recinfo.REPOSITORY_TYPE = X_REPOSITORY_TYPE)
224 AND (recinfo.RESULT_REGION = X_RESULT_REGION)
225 AND (recinfo.SEARCH_RESULT_VO = X_SEARCH_RESULT_VO)
226 AND (recinfo.SEARCH_RESULT_COUNT_VO = X_SEARCH_RESULT_COUNT_VO)
227 AND ((recinfo.UPDATABLE = X_UPDATABLE)
228 OR ((recinfo.UPDATABLE is null) AND (X_UPDATABLE is null)))
229 ) then
230 null;
231 else
232 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
233 app_exception.raise_exception;
234 end if;
235
236 return;
237 end LOCK_ROW;
238
239 procedure UPDATE_ROW (
240 X_REPOSITORY_ID in NUMBER,
241 X_OBJECT_VERSION_NUMBER in NUMBER,
242 X_ATTRIBUTE_CATEGORY in VARCHAR2,
243 X_ATTRIBUTE1 in VARCHAR2,
244 X_ATTRIBUTE2 in VARCHAR2,
245 X_ATTRIBUTE3 in VARCHAR2,
246 X_ATTRIBUTE4 in VARCHAR2,
247 X_ATTRIBUTE5 in VARCHAR2,
248 X_ATTRIBUTE6 in VARCHAR2,
249 X_ATTRIBUTE7 in VARCHAR2,
250 X_ATTRIBUTE8 in VARCHAR2,
251 X_ATTRIBUTE9 in VARCHAR2,
252 X_ATTRIBUTE10 in VARCHAR2,
253 X_ATTRIBUTE11 in VARCHAR2,
254 X_ATTRIBUTE12 in VARCHAR2,
255 X_ATTRIBUTE13 in VARCHAR2,
256 X_ATTRIBUTE14 in VARCHAR2,
257 X_ATTRIBUTE15 in VARCHAR2,
258 X_REPOSITORY_NAME in VARCHAR2,
259 X_REPOSITORY_TYPE in VARCHAR2,
260 X_RESULT_REGION in VARCHAR2,
261 X_SEARCH_RESULT_VO in VARCHAR2,
262 X_SEARCH_RESULT_COUNT_VO in VARCHAR2,
263 X_UPDATABLE in VARCHAR2,
264 X_LAST_UPDATE_DATE in DATE,
265 X_LAST_UPDATED_BY in NUMBER,
266 X_LAST_UPDATE_LOGIN in NUMBER
267 ) is
268 begin
269 update CS_KB_REPOSITORIES set
270 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
271 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
272 ATTRIBUTE1 = X_ATTRIBUTE1,
273 ATTRIBUTE2 = X_ATTRIBUTE2,
274 ATTRIBUTE3 = X_ATTRIBUTE3,
275 ATTRIBUTE4 = X_ATTRIBUTE4,
276 ATTRIBUTE5 = X_ATTRIBUTE5,
277 ATTRIBUTE6 = X_ATTRIBUTE6,
278 ATTRIBUTE7 = X_ATTRIBUTE7,
279 ATTRIBUTE8 = X_ATTRIBUTE8,
280 ATTRIBUTE9 = X_ATTRIBUTE9,
281 ATTRIBUTE10 = X_ATTRIBUTE10,
282 ATTRIBUTE11 = X_ATTRIBUTE11,
283 ATTRIBUTE12 = X_ATTRIBUTE12,
284 ATTRIBUTE13 = X_ATTRIBUTE13,
285 ATTRIBUTE14 = X_ATTRIBUTE14,
286 ATTRIBUTE15 = X_ATTRIBUTE15,
287 REPOSITORY_NAME = X_REPOSITORY_NAME,
288 REPOSITORY_TYPE = X_REPOSITORY_TYPE,
289 RESULT_REGION = X_RESULT_REGION,
290 SEARCH_RESULT_VO = X_SEARCH_RESULT_VO,
291 SEARCH_RESULT_COUNT_VO = X_SEARCH_RESULT_COUNT_VO,
292 UPDATABLE = X_UPDATABLE,
293 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
294 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
295 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
296 where REPOSITORY_ID = X_REPOSITORY_ID;
297
298 if (sql%notfound) then
299 raise no_data_found;
300 end if;
301
302 end UPDATE_ROW;
303
304 procedure DELETE_ROW (
305 X_REPOSITORY_ID in NUMBER
306 ) is
307 begin
308
309 delete from CS_KB_REPOSITORIES
310 where REPOSITORY_ID = X_REPOSITORY_ID;
311
312 if (sql%notfound) then
313 raise no_data_found;
314 end if;
315
316 end DELETE_ROW;
317
318 PROCEDURE LOAD_ROW(
319 X_REPOSITORY_ID in NUMBER,
320 X_REPOSITORY_NAME in VARCHAR2,
321 X_REPOSITORY_TYPE in VARCHAR2,
322 X_RESULT_REGION in VARCHAR2,
323 X_SEARCH_RESULT_VO in VARCHAR2,
324 X_SEARCH_RESULT_COUNT_VO in VARCHAR2,
325 X_UPDATABLE in VARCHAR2,
326 X_OWNER in VARCHAR2,
327 X_LAST_UPDATE_DATE in VARCHAR2,
328 X_CUSTOM_MODE in VARCHAR2
329 ) IS
330 f_luby number; -- entity owner in file
331 f_ludate date; -- entity update date in file
332 db_luby number; -- entity owner in db
333 db_ludate date; -- entity update date in db
334 db_ovn NUMBER;
335 l_rowid rowid;
339 -- Note table handler apis should be coded to treat
336 BEGIN
337
338 -- Translate a true null value to fnd_api.g_miss_char
340 -- fnd_api.g_miss_* as true nulls, and not as no-change.
341 -- if (x_meaning = fnd_load_util.null_value) then
342 -- l_meaning := fnd_api.g_miss_char;
343 -- else
344 -- l_meaning := x_meaning;
345 -- end if;
346
347 -- Translate owner to file_last_updated_by
348 f_luby := fnd_load_util.owner_id(X_OWNER);
349
350 -- Translate char last_update_date to date
351 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
352 BEGIN
353 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE, OBJECT_VERSION_NUMBER
354 INTO db_luby, db_ludate, db_ovn
355 FROM CS_KB_REPOSITORIES
356 WHERE REPOSITORY_ID = X_REPOSITORY_ID;
357
358 -- Test for customization and version
359 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
360 db_ludate, X_CUSTOM_MODE)) THEN
361 -- Update existing row
362 UPDATE_ROW (
363 X_REPOSITORY_ID => X_REPOSITORY_ID,
364 X_OBJECT_VERSION_NUMBER => db_ovn+1,
365 X_ATTRIBUTE_CATEGORY => null,
366 X_ATTRIBUTE1 => null,
367 X_ATTRIBUTE2 => null,
368 X_ATTRIBUTE3 => null,
369 X_ATTRIBUTE4 => null,
370 X_ATTRIBUTE5 => null,
371 X_ATTRIBUTE6 => null,
372 X_ATTRIBUTE7 => null,
373 X_ATTRIBUTE8 => null,
374 X_ATTRIBUTE9 => null,
375 X_ATTRIBUTE10 => null,
376 X_ATTRIBUTE11 => null,
377 X_ATTRIBUTE12 => null,
378 X_ATTRIBUTE13 => null,
379 X_ATTRIBUTE14 => null,
380 X_ATTRIBUTE15 => null,
381 X_REPOSITORY_NAME => X_REPOSITORY_NAME,
382 X_REPOSITORY_TYPE => X_REPOSITORY_TYPE,
383 X_RESULT_REGION => X_RESULT_REGION,
384 X_SEARCH_RESULT_VO => X_SEARCH_RESULT_VO,
385 X_SEARCH_RESULT_COUNT_VO => X_SEARCH_RESULT_COUNT_VO,
386 X_UPDATABLE => X_UPDATABLE,
387 X_LAST_UPDATE_DATE => f_ludate,
388 X_LAST_UPDATED_BY => f_luby,
389 X_LAST_UPDATE_LOGIN => 0);
390
391
392 END IF;
393 EXCEPTION
394 WHEN no_data_found THEN
395 -- Record doesn't exist - insert in all cases
396 INSERT_ROW (
397 X_ROWID => l_rowid,
398 X_REPOSITORY_ID => X_REPOSITORY_ID,
399 X_OBJECT_VERSION_NUMBER => 1,
400 X_ATTRIBUTE_CATEGORY => null,
401 X_ATTRIBUTE1 => null,
402 X_ATTRIBUTE2 => null,
403 X_ATTRIBUTE3 => null,
404 X_ATTRIBUTE4 => null,
405 X_ATTRIBUTE5 => null,
406 X_ATTRIBUTE6 => null,
407 X_ATTRIBUTE7 => null,
408 X_ATTRIBUTE8 => null,
409 X_ATTRIBUTE9 => null,
410 X_ATTRIBUTE10 => null,
411 X_ATTRIBUTE11 => null,
412 X_ATTRIBUTE12 => null,
413 X_ATTRIBUTE13 => null,
414 X_ATTRIBUTE14 => null,
415 X_ATTRIBUTE15 => null,
416 X_REPOSITORY_NAME => X_REPOSITORY_NAME,
417 X_REPOSITORY_TYPE => X_REPOSITORY_TYPE,
418 X_RESULT_REGION => X_RESULT_REGION,
419 X_SEARCH_RESULT_VO => X_SEARCH_RESULT_VO,
420 X_SEARCH_RESULT_COUNT_VO => X_SEARCH_RESULT_COUNT_VO,
421 X_UPDATABLE => X_UPDATABLE,
422 X_CREATION_DATE => f_ludate,
423 X_CREATED_BY => f_luby,
424 X_LAST_UPDATE_DATE => f_ludate,
425 X_LAST_UPDATED_BY => f_luby,
426 X_LAST_UPDATE_LOGIN => 0);
427
428 END;
429
430 END LOAD_ROW;
431
432 PROCEDURE LOAD_SEED_ROW(
433 X_UPLOAD_MODE in VARCHAR2,
434 X_REPOSITORY_ID in NUMBER,
435 X_REPOSITORY_NAME in VARCHAR2,
436 X_REPOSITORY_TYPE in VARCHAR2,
437 X_RESULT_REGION in VARCHAR2,
438 X_SEARCH_RESULT_VO in VARCHAR2,
439 X_SEARCH_RESULT_COUNT_VO in VARCHAR2,
440 X_UPDATABLE in VARCHAR2,
441 X_OWNER in VARCHAR2,
442 X_LAST_UPDATE_DATE in VARCHAR2,
443 X_CUSTOM_MODE in VARCHAR2) IS
444 BEGIN
445
446 if (X_UPLOAD_MODE = 'NLS') then
447 null; -- Entity is not translatable
448 else
449 LOAD_ROW(
450 X_REPOSITORY_ID,
451 X_REPOSITORY_NAME,
452 X_REPOSITORY_TYPE,
453 X_RESULT_REGION,
454 X_SEARCH_RESULT_VO,
455 X_SEARCH_RESULT_COUNT_VO,
456 X_UPDATABLE,
457 X_OWNER,
458 X_LAST_UPDATE_DATE,
459 X_CUSTOM_MODE );
460 end if;
461
462 END LOAD_SEED_ROW;
463
464 end CS_KB_REPOSITORIES_PKG;