[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_TABLE_ATTRIBUTES_PVT
Source
1 PACKAGE BODY jtf_rs_table_attributes_pvt AS
2 /* $Header: jtfrsvwb.pls 120.0 2005/05/11 08:23:21 appldev ship $ */
3
4 /*****************************************************************************************
5 Its main procedures are as following:
6 Create table attributes
7 Update table attributes
8 Delete table attributes
9 Calls to these procedures will invoke procedures from jtf_rs_table_attributes_pub
10 to do business validations and to do actual inserts, updates and deletes into tables.
11 ******************************************************************************************/
12 /* Package variables. */
13
14 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_TABLE_ATTRIBUTES_PVT';
15 G_NAME VARCHAR2(240);
16
17 /* Procedure to create table attributes
18 based on input values passed by calling routines. */
19 PROCEDURE create_table_attribute
20 (P_API_VERSION IN NUMBER,
21 P_INIT_MSG_LIST IN VARCHAR2,
22 P_COMMIT IN VARCHAR2,
23 P_ATTRIBUTE_NAME IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_NAME%TYPE,
24 P_ATTRIBUTE_ACCESS_LEVEL IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ACCESS_LEVEL%TYPE,
25 P_ATTRIBUTE1 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE1%TYPE,
26 P_ATTRIBUTE2 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE2%TYPE,
27 P_ATTRIBUTE3 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE3%TYPE,
28 P_ATTRIBUTE4 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE4%TYPE,
29 P_ATTRIBUTE5 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE5%TYPE,
30 P_ATTRIBUTE6 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE6%TYPE,
31 P_ATTRIBUTE7 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE7%TYPE,
32 P_ATTRIBUTE8 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE8%TYPE,
33 P_ATTRIBUTE9 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE9%TYPE,
34 P_ATTRIBUTE10 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE10%TYPE,
35 P_ATTRIBUTE11 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE11%TYPE,
36 P_ATTRIBUTE12 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE12%TYPE,
37 P_ATTRIBUTE13 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE13%TYPE,
38 P_ATTRIBUTE14 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE14%TYPE,
39 P_ATTRIBUTE15 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE15%TYPE,
40 P_ATTRIBUTE_CATEGORY IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_CATEGORY%TYPE,
41 P_USER_ATTRIBUTE_NAME IN JTF_RS_TABLE_ATTRIBUTES_TL.USER_ATTRIBUTE_NAME%TYPE,
42 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
43 X_MSG_COUNT OUT NOCOPY NUMBER,
44 X_MSG_DATA OUT NOCOPY VARCHAR2,
45 X_ATTRIBUTE_ID OUT NOCOPY JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ID%TYPE
46 )IS
47 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TABLE_ATTRIBUTE';
48 l_api_version CONSTANT NUMBER :=1.0;
49 l_bind_data_id number;
50
51 l_attribute_name JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_NAME%TYPE := p_attribute_name;
52 l_attribute_access_level JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ACCESS_LEVEL%TYPE := p_attribute_access_level;
53 l_user_attribute_name JTF_RS_TABLE_ATTRIBUTES_TL.USER_ATTRIBUTE_NAME%TYPE := p_user_attribute_name;
54 l_object_version_number number;
55
56 l_attribute_id JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ID%TYPE;
57
58 l_go_ahead VARCHAR2(5);
59 l_return_code VARCHAR2(100);
60 l_count NUMBER;
61 l_data VARCHAR2(200);
62
63 l_return_status VARCHAR2(200);
64 l_msg_count NUMBER;
65 l_msg_data VARCHAR2(200);
66 l_rowid VARCHAR2(200);
67
68
69 CURSOR attribute_dup_cur(ll_attribute_name JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_NAME%TYPE)
70 IS
71 SELECT attribute_access_level,
72 attribute_id
73 FROM jtf_rs_table_attributes_b
74 WHERE attribute_name = ll_attribute_name;
75
76 attribute_dup_rec attribute_dup_cur%rowtype;
77
78 l_date Date;
79 l_user_id Number;
80 l_login_id Number;
81
82 BEGIN
83 --Standard Start of API SAVEPOINT
84 SAVEPOINT TABLE_ATTRIBUTE_SP;
85
86 x_return_status := fnd_api.g_ret_sts_success;
87
88 --Standard Call to check API compatibility
89 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
90 THEN
91 RAISE FND_API.G_EXC_ERROR;
92 END IF;
93
94 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
95 IF FND_API.To_boolean(P_INIT_MSG_LIST)
96 THEN
97 FND_MSG_PUB.Initialize;
98 END IF;
99
100 --GET USER ID AND SYSDATE
101 l_date := sysdate;
102 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
103 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
104
105 -- Check that attribute access levels are valid
106 IF l_attribute_access_level IN ('U','NU','UWN','UWA') THEN
107 l_go_ahead := 'YES';
108 ELSE l_go_ahead := 'NO';
109 END IF;
110
111 IF (l_go_ahead = 'YES') THEN
112 OPEN attribute_dup_cur(l_attribute_name);
113 FETCH attribute_dup_cur into attribute_dup_rec;
114 IF (attribute_dup_cur%NOTFOUND) THEN
115
116 SELECT jtf_rs_table_attributes_s.nextval
117 INTO l_attribute_id
118 FROM dual;
119
120 JTF_RS_TABLE_ATTRIBUTES_PKG.INSERT_ROW(
121 X_ROWID => l_rowid,
122 X_ATTRIBUTE_ID => l_attribute_id,
123 X_ATTRIBUTE_NAME => l_attribute_name,
124 X_ATTRIBUTE_ACCESS_LEVEL => l_attribute_access_level,
125 X_ATTRIBUTE1 => p_attribute1,
126 X_ATTRIBUTE2 => p_attribute2,
127 X_ATTRIBUTE3 => p_attribute3,
128 X_ATTRIBUTE4 => p_attribute4,
129 X_ATTRIBUTE5 => p_attribute5,
130 X_ATTRIBUTE6 => p_attribute6,
131 X_ATTRIBUTE7 => p_attribute7,
132 X_ATTRIBUTE8 => p_attribute8,
133 X_ATTRIBUTE9 => p_attribute9,
134 X_ATTRIBUTE10 => p_attribute10,
135 X_ATTRIBUTE11 => p_attribute11,
136 X_ATTRIBUTE12 => p_attribute12,
137 X_ATTRIBUTE13 => p_attribute13,
138 X_ATTRIBUTE14 => p_attribute14,
139 X_ATTRIBUTE15 => p_attribute15,
140 X_ATTRIBUTE_CATEGORY => p_attribute_category,
141 X_OBJECT_VERSION_NUMBER => l_object_version_number,
142 X_USER_ATTRIBUTE_NAME => p_user_attribute_name,
143 X_CREATION_DATE => sysdate,
144 X_CREATED_BY => l_user_id,
145 X_LAST_UPDATE_DATE => sysdate,
146 X_LAST_UPDATED_BY => l_user_id,
147 X_LAST_UPDATE_LOGIN => 0);
148
149 -- return attribute_id
150 x_attribute_id := l_attribute_id;
151
152 ELSIF attribute_dup_cur%FOUND THEN
153 x_attribute_id := attribute_dup_rec.attribute_id;
154 fnd_message.set_name ('JTF', 'JTF_RS_ATT_DUP');
155 FND_MSG_PUB.add;
156 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
157 raise fnd_api.g_exc_error;
158 END IF;
159 CLOSE attribute_dup_cur;
160
161 ELSIF (l_go_ahead = 'NO') THEN
162 x_attribute_id := null;
163 fnd_message.set_name ('JTF', 'JTF_RS_ATT_CREATE_ERR');
164 FND_MSG_PUB.add;
165 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
166 raise fnd_api.g_exc_error;
167 END IF;
168
169
170 --standard commit
171 IF fnd_api.to_boolean (p_commit)
172 THEN
173 COMMIT WORK;
174 END IF;
175
176 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
177
178 EXCEPTION
179 WHEN fnd_api.g_exc_unexpected_error
180 THEN
181 ROLLBACK TO TABLE_ATTRIBUTE_SP;
182 x_return_status := fnd_api.g_ret_sts_unexp_error;
183 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
184 WHEN fnd_api.g_exc_error
185 THEN
186 ROLLBACK TO TABLE_ATTRIBUTE_SP;
187 x_return_status := fnd_api.g_ret_sts_error;
188 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
189 WHEN OTHERS
190 THEN
191 ROLLBACK TO ROLE_RELATE_SP;
192 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
193 fnd_message.set_token('P_SQLCODE',SQLCODE);
194 fnd_message.set_token('P_SQLERRM',SQLERRM);
195 fnd_message.set_token('P_API_NAME', l_api_name);
196 FND_MSG_PUB.add;
197 x_return_status := fnd_api.g_ret_sts_unexp_error;
198 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
199
200 END create_table_attribute;
201
202
203 /* Procedure to update the table attributes
204 based on input values passed by calling routines. */
205
206 PROCEDURE update_table_attribute
207 (P_API_VERSION IN NUMBER,
208 P_INIT_MSG_LIST IN VARCHAR2,
209 P_COMMIT IN VARCHAR2,
210 P_ATTRIBUTE_ID IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ID%TYPE,
211 P_ATTRIBUTE_NAME IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_NAME%TYPE,
212 P_ATTRIBUTE_ACCESS_LEVEL IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ACCESS_LEVEL%TYPE,
213 P_USER_ATTRIBUTE_NAME IN JTF_RS_TABLE_ATTRIBUTES_TL.USER_ATTRIBUTE_NAME%TYPE,
214 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_TABLE_ATTRIBUTES_B.OBJECT_VERSION_NUMBER%TYPE,
215 P_ATTRIBUTE1 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE1%TYPE,
216 P_ATTRIBUTE2 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE2%TYPE,
217 P_ATTRIBUTE3 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE3%TYPE,
218 P_ATTRIBUTE4 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE4%TYPE,
219 P_ATTRIBUTE5 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE5%TYPE,
220 P_ATTRIBUTE6 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE6%TYPE,
221 P_ATTRIBUTE7 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE7%TYPE,
222 P_ATTRIBUTE8 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE8%TYPE,
223 P_ATTRIBUTE9 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE9%TYPE,
224 P_ATTRIBUTE10 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE10%TYPE,
225 P_ATTRIBUTE11 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE11%TYPE,
226 P_ATTRIBUTE12 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE12%TYPE,
227 P_ATTRIBUTE13 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE13%TYPE,
228 P_ATTRIBUTE14 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE14%TYPE,
229 P_ATTRIBUTE15 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE15%TYPE,
230 P_ATTRIBUTE_CATEGORY IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_CATEGORY%TYPE,
231 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
232 X_MSG_COUNT OUT NOCOPY NUMBER,
233 X_MSG_DATA OUT NOCOPY VARCHAR2
234 )IS
235 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TABLE_ATTRIBUTE';
236 l_api_version CONSTANT NUMBER :=1.0;
237 l_bind_data_id number;
238
239 l_return_code VARCHAR2(100);
240 l_count NUMBER;
241 l_data VARCHAR2(200);
242
243
244 L_ATTRIBUTE1 JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE1%TYPE;
245 L_ATTRIBUTE2 JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE2%TYPE;
246 L_ATTRIBUTE3 JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE3%TYPE;
247 L_ATTRIBUTE4 JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE4%TYPE;
248 L_ATTRIBUTE5 JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE5%TYPE;
249 L_ATTRIBUTE6 JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE6%TYPE;
250 L_ATTRIBUTE7 JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE7%TYPE;
251 L_ATTRIBUTE8 JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE8%TYPE;
252 L_ATTRIBUTE9 JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE9%TYPE;
253 L_ATTRIBUTE10 JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE10%TYPE;
254 L_ATTRIBUTE11 JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE11%TYPE;
255 L_ATTRIBUTE12 JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE12%TYPE;
256 L_ATTRIBUTE13 JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE13%TYPE;
257 L_ATTRIBUTE14 JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE14%TYPE;
258 L_ATTRIBUTE15 JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE15%TYPE;
259 L_ATTRIBUTE_CATEGORY JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_CATEGORY%TYPE;
260
261
262 CURSOR table_attribute_cur(ll_attribute_id JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ID%TYPE)
263 IS
264 SELECT attribute_id,
265 attribute_name,
266 attribute_access_level,
267 user_attribute_name,
268 object_version_number,
269 attribute1,
270 attribute2,
271 attribute3,
272 attribute4,
273 attribute5,
274 attribute6,
275 attribute7,
276 attribute8,
277 attribute9,
278 attribute10,
279 attribute11,
280 attribute12,
281 attribute13,
282 attribute14,
283 attribute15,
284 attribute_category
285 FROM jtf_rs_table_attributes_vl
286 WHERE attribute_id = ll_attribute_id;
287
288 table_attribute_rec table_attribute_cur%rowtype;
289
290 l_attribute_name JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_NAME%TYPE := p_attribute_name;
291 l_attribute_id JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ID%TYPE := p_attribute_id;
292 l_attribute_access_level JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ACCESS_LEVEL%TYPE := p_attribute_access_level;
293 l_user_attribute_name JTF_RS_TABLE_ATTRIBUTES_TL.USER_ATTRIBUTE_NAME%TYPE := p_user_attribute_name;
294
295 l_object_version_number JTF_RS_TABLE_ATTRIBUTES_B.OBJECT_VERSION_NUMBER%TYPE := P_OBJECT_VERSION_NUM;
296
297 l_return_status VARCHAR2(200);
298 l_msg_count NUMBER;
299 l_msg_data VARCHAR2(200);
300 l_rowid VARCHAR2(200);
301
302 l_date Date;
303 l_user_id Number;
304 l_login_id Number;
305
306 BEGIN
307 --Standard Start of API SAVEPOINT
308 SAVEPOINT TABLE_ATTRIBUTE_SP;
309
310 x_return_status := fnd_api.g_ret_sts_success;
311
312 --Standard Call to check API compatibility
313 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
314 THEN
315 RAISE FND_API.G_EXC_ERROR;
316 END IF;
317
318 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
319 IF FND_API.To_boolean(P_INIT_MSG_LIST)
320 THEN
321 FND_MSG_PUB.Initialize;
322 END IF;
323
324
325 --GET USER ID AND SYSDATE
326 l_date := sysdate;
327 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
328 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
329
330 OPEN table_attribute_cur(l_attribute_id);
331 FETCH table_attribute_cur INTO table_attribute_rec;
332
333 IF (table_attribute_cur%found) THEN
334
335 IF (p_attribute_name = FND_API.G_MISS_CHAR)
336 THEN
337 l_attribute_name := table_attribute_rec.attribute_name;
338 ELSE
339 l_attribute_name := p_attribute_name;
340 END IF;
341 IF (p_attribute_access_level = FND_API.G_MISS_CHAR)
342 THEN
343 l_attribute_access_level := table_attribute_rec.attribute_access_level;
344 ELSE
345 l_attribute_access_level := p_attribute_access_level;
346 END IF;
347 IF (p_user_attribute_name = FND_API.G_MISS_CHAR)
348 THEN
349 l_user_attribute_name := table_attribute_rec.user_attribute_name;
350 ELSE
351 l_user_attribute_name := p_user_attribute_name;
352 END IF;
353 IF(p_attribute1 = FND_API.G_MISS_CHAR)
354 THEN
355 l_attribute1 := table_attribute_rec.attribute1;
356 ELSE
357 l_attribute1 := p_attribute1;
358 END IF;
359 IF(p_attribute2 = FND_API.G_MISS_CHAR)
360 THEN
361 l_attribute2 := table_attribute_rec.attribute2;
362 ELSE
363 l_attribute2 := p_attribute2;
364 END IF;
365 IF(p_attribute3 = FND_API.G_MISS_CHAR)
366 THEN
367 l_attribute3 := table_attribute_rec.attribute3;
368 ELSE
369 l_attribute3 := p_attribute3;
370 END IF;
371 IF(p_attribute4 = FND_API.G_MISS_CHAR)
372 THEN
373 l_attribute4 := table_attribute_rec.attribute4;
374 ELSE
375 l_attribute4 := p_attribute4;
376 END IF;
377 IF(p_attribute5 = FND_API.G_MISS_CHAR)
378 THEN
379 l_attribute5 := table_attribute_rec.attribute5;
380 ELSE
381 l_attribute5 := p_attribute5;
382 END IF;
383 IF(p_attribute6 = FND_API.G_MISS_CHAR)
384 THEN
385 l_attribute6 := table_attribute_rec.attribute6;
386 ELSE
387 l_attribute6 := p_attribute6;
388 END IF;
389 IF(p_attribute7 = FND_API.G_MISS_CHAR)
390 THEN
391 l_attribute7 := table_attribute_rec.attribute7;
392 ELSE
393 l_attribute7 := p_attribute7;
394 END IF;
395 IF(p_attribute8 = FND_API.G_MISS_CHAR)
396 THEN
397 l_attribute8 := table_attribute_rec.attribute8;
398 ELSE
399 l_attribute8 := p_attribute8;
400 END IF;
401 IF(p_attribute9 = FND_API.G_MISS_CHAR)
402 THEN
403 l_attribute9 := table_attribute_rec.attribute9;
404 ELSE
405 l_attribute9 := p_attribute9;
406 END IF;
407 IF(p_attribute10 = FND_API.G_MISS_CHAR)
408 THEN
409 l_attribute10 := table_attribute_rec.attribute10;
410 ELSE
411 l_attribute10 := p_attribute10;
412 END IF;
413 IF(p_attribute11 = FND_API.G_MISS_CHAR)
414 THEN
415 l_attribute11 := table_attribute_rec.attribute11;
416 ELSE
417 l_attribute11 := p_attribute11;
418 END IF;
419 IF(p_attribute12 = FND_API.G_MISS_CHAR)
420 THEN
421 l_attribute12 := table_attribute_rec.attribute12;
422 ELSE
423 l_attribute12 := p_attribute12;
424 END IF;
425 IF(p_attribute13 = FND_API.G_MISS_CHAR)
426 THEN
427 l_attribute13 := table_attribute_rec.attribute13;
428 ELSE
429 l_attribute13 := p_attribute13;
430 END IF;
431 IF(p_attribute14 = FND_API.G_MISS_CHAR)
432 THEN
433 l_attribute14 := table_attribute_rec.attribute14;
434 ELSE
435 l_attribute14 := p_attribute14;
436 END IF;
437 IF(p_attribute15 = FND_API.G_MISS_CHAR)
438 THEN
439 l_attribute15 := table_attribute_rec.attribute15;
440 ELSE
441 l_attribute15 := p_attribute15;
442 END IF;
443 IF(p_attribute_category = FND_API.G_MISS_CHAR)
444 THEN
445 l_attribute_category := table_attribute_rec.attribute_category;
446 ELSE
447 l_attribute_category := p_attribute_category;
448 END IF;
449
450 BEGIN
451
452 jtf_rs_table_attributes_pkg.lock_row(
453 x_attribute_id => l_attribute_id,
454 x_object_version_number => p_object_version_num
455 );
456
457 EXCEPTION
458
459 WHEN OTHERS THEN
460 x_return_status := fnd_api.g_ret_sts_error;
461 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
462 fnd_msg_pub.add;
463 RAISE fnd_api.g_exc_error;
464
465 END;
466
467
468 l_object_version_number := l_object_version_number +1;
469
470 jtf_rs_table_attributes_pkg.update_row(X_ATTRIBUTE_ID => l_attribute_id,
471 X_ATTRIBUTE_NAME => l_attribute_name,
472 X_ATTRIBUTE_ACCESS_LEVEL => l_attribute_access_level,
473 X_USER_ATTRIBUTE_NAME => l_user_attribute_name,
474 X_OBJECT_VERSION_NUMBER => l_object_version_number ,
475 X_ATTRIBUTE1 => l_attribute1,
476 X_ATTRIBUTE2 => l_attribute2,
477 X_ATTRIBUTE3 => l_attribute3,
478 X_ATTRIBUTE4 => l_attribute4,
479 X_ATTRIBUTE5 => l_attribute5,
480 X_ATTRIBUTE6 => l_attribute6,
481 X_ATTRIBUTE7 => l_attribute7,
482 X_ATTRIBUTE8 => l_attribute8,
483 X_ATTRIBUTE9 => l_attribute9,
484 X_ATTRIBUTE10 => l_attribute10,
485 X_ATTRIBUTE11 => l_attribute11,
486 X_ATTRIBUTE12 => l_attribute12,
487 X_ATTRIBUTE13 => l_attribute13,
488 X_ATTRIBUTE14 => l_attribute14,
489 X_ATTRIBUTE15 => l_attribute15,
490 X_ATTRIBUTE_CATEGORY => l_attribute_category,
491 X_LAST_UPDATE_DATE => l_date,
492 X_LAST_UPDATED_BY => l_user_id,
493 X_LAST_UPDATE_LOGIN => l_login_id ) ;
494
495 P_OBJECT_VERSION_NUM := l_object_version_number;
496
497 ELSIF (table_attribute_cur%notfound) THEN
498 x_return_status := fnd_api.g_ret_sts_error;
499 fnd_message.set_name ('JTF', 'JTF_RS_INVALID_ID');
500 FND_MSG_PUB.add;
501 RAISE fnd_api.g_exc_error;
502
503 END IF;
504
505 CLOSE table_attribute_cur;
506
507 IF fnd_api.to_boolean (p_commit)
508 THEN
509 COMMIT WORK;
510 END IF;
511
512 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
513
514 EXCEPTION
515 WHEN fnd_api.g_exc_error
516 THEN
517 ROLLBACK TO TABLE_ATTRIBUTE_SP;
518 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
519
520 WHEN fnd_api.g_exc_unexpected_error
521 THEN
522 ROLLBACK TO TABLE_ATTRIBUTE_SP;
523 x_return_status := fnd_api.g_ret_sts_unexp_error;
524 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
525 WHEN OTHERS
526 THEN
527 ROLLBACK TO TABLE_ATTRIBUTE_SP;
528 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
529 fnd_message.set_token('P_SQLCODE',SQLCODE);
530 fnd_message.set_token('P_SQLERRM',SQLERRM);
531 fnd_message.set_token('P_API_NAME',l_api_name);
532 FND_MSG_PUB.add;
533 x_return_status := fnd_api.g_ret_sts_unexp_error;
534 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
535 END update_table_attribute;
536
537
538 /* Procedure to delete the table attributes. */
539
540 PROCEDURE delete_table_attribute
541 (P_API_VERSION IN NUMBER,
542 P_INIT_MSG_LIST IN VARCHAR2,
543 P_COMMIT IN VARCHAR2,
544 P_ATTRIBUTE_ID IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ID%TYPE,
545 P_OBJECT_VERSION_NUM IN JTF_RS_TABLE_ATTRIBUTES_B.OBJECT_VERSION_NUMBER%TYPE,
546 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
547 X_MSG_COUNT OUT NOCOPY NUMBER,
548 X_MSG_DATA OUT NOCOPY VARCHAR2
549 )IS
550
551
552 CURSOR chk_att_exist_cur(ll_attribute_id JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ID%TYPE)
553 IS
554 SELECT attribute_name
555 FROM jtf_rs_table_attributes_b
556 WHERE attribute_id = ll_attribute_id;
557
558 chk_att_exist_rec chk_att_exist_cur%rowtype;
559
560 l_attribute_id JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ID%TYPE := p_attribute_id;
561
562 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TABLE_ATTRIBUTE';
563 l_api_version CONSTANT NUMBER :=1.0;
564 l_bind_data_id number;
565
566 l_date Date;
567 l_user_id Number;
568 l_login_id Number;
569
570
571 l_return_code VARCHAR2(100);
572 l_count NUMBER;
573 l_data VARCHAR2(200);
574
575 l_return_status VARCHAR2(200);
576 l_msg_count NUMBER;
577 l_msg_data VARCHAR2(200);
578
579 BEGIN
580 --Standard Start of API SAVEPOINT
581 SAVEPOINT TABLE_ATTRIBUTE_SP;
582
583 x_return_status := fnd_api.g_ret_sts_success;
584
585 --Standard Call to check API compatibility
586 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
587 THEN
588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
589 END IF;
590
591 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
592 IF FND_API.To_boolean(P_INIT_MSG_LIST)
593 THEN
594 FND_MSG_PUB.Initialize;
595 END IF;
596
597 OPEN chk_att_exist_cur(l_attribute_id);
598 FETCH chk_att_exist_cur INTO chk_att_exist_rec;
599 IF (chk_att_exist_cur%FOUND)
600 THEN
601
602 JTF_RS_TABLE_ATTRIBUTES_PKG.DELETE_ROW(
603 X_ATTRIBUTE_ID => l_attribute_id);
604
605 ELSIF (chk_att_exist_cur%notfound) THEN
606 x_return_status := fnd_api.g_ret_sts_error;
607 fnd_message.set_name ('JTF', 'JTF_RS_INVALID_ID');
608 FND_MSG_PUB.add;
609 RAISE fnd_api.g_exc_error;
610
611 END IF;
612
613 CLOSE chk_att_exist_cur;
614
615 IF fnd_api.to_boolean (p_commit)
616 THEN
617 COMMIT WORK;
618 END IF;
619
620 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
621
622 EXCEPTION
623 WHEN fnd_api.g_exc_unexpected_error
624 THEN
625 ROLLBACK TO TABLE_ATTRIBUTE_SP;
626 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
627 WHEN fnd_api.g_exc_error
628 THEN
629 ROLLBACK TO TABLE_ATTRIBUTE_SP;
630 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
631 WHEN OTHERS
632 THEN
633 ROLLBACK TO TABLE_ATTRIBUTE_SP;
634 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
635 fnd_message.set_token('P_SQLCODE',SQLCODE);
636 fnd_message.set_token('P_SQLERRM',SQLERRM);
637 fnd_message.set_token('P_API_NAME',l_api_name);
638 FND_MSG_PUB.add;
639 x_return_status := fnd_api.g_ret_sts_unexp_error;
640 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
641
642 END delete_table_attribute;
643
644 END jtf_rs_table_attributes_pvt;