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