[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_TABLE_ATTRIBUTES_PUB
Source
1 PACKAGE BODY jtf_rs_table_attributes_pub AS
2 /* $Header: jtfrspwb.pls 120.0 2005/05/11 08:21:27 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 of jtf_rs_table_attributes_pvt
10 to do inserts, updates and deletes into tables.
11 ******************************************************************************************/
12 /* Package variables. */
13
14 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_TABLE_ATTRIBUTES_PUB';
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 DEFAULT FND_API.G_FALSE,
22 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
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 DEFAULT NULL,
26 P_ATTRIBUTE2 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE2%TYPE DEFAULT NULL,
27 P_ATTRIBUTE3 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE3%TYPE DEFAULT NULL,
28 P_ATTRIBUTE4 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE4%TYPE DEFAULT NULL,
29 P_ATTRIBUTE5 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE5%TYPE DEFAULT NULL,
30 P_ATTRIBUTE6 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE6%TYPE DEFAULT NULL,
31 P_ATTRIBUTE7 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE7%TYPE DEFAULT NULL,
32 P_ATTRIBUTE8 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE8%TYPE DEFAULT NULL,
33 P_ATTRIBUTE9 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE9%TYPE DEFAULT NULL,
34 P_ATTRIBUTE10 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE10%TYPE DEFAULT NULL,
35 P_ATTRIBUTE11 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE11%TYPE DEFAULT NULL,
36 P_ATTRIBUTE12 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE12%TYPE DEFAULT NULL,
37 P_ATTRIBUTE13 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE13%TYPE DEFAULT NULL,
38 P_ATTRIBUTE14 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE14%TYPE DEFAULT NULL,
39 P_ATTRIBUTE15 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE15%TYPE DEFAULT NULL,
40 P_ATTRIBUTE_CATEGORY IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_CATEGORY%TYPE DEFAULT NULL,
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_return_code VARCHAR2(100);
59 l_count NUMBER;
60 l_data VARCHAR2(200);
61
62 l_return_status VARCHAR2(200);
63 l_msg_count NUMBER;
64 l_msg_data VARCHAR2(200);
65
66 BEGIN
67 --Standard Start of API SAVEPOINT
68 SAVEPOINT TABLE_ATTRIBUTE_SP;
69
70 x_return_status := fnd_api.g_ret_sts_success;
71
72 --Standard Call to check API compatibility
73 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
74 THEN
75 RAISE FND_API.G_EXC_ERROR;
76 END IF;
77
78 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
79 IF FND_API.To_boolean(P_INIT_MSG_LIST)
80 THEN
81 FND_MSG_PUB.Initialize;
82 END IF;
83
84 JTF_RS_TABLE_ATTRIBUTES_PVT.CREATE_TABLE_ATTRIBUTE(
85 P_API_VERSION => l_api_version,
86 P_INIT_MSG_LIST => p_init_msg_list,
87 P_COMMIT => p_commit,
88 P_ATTRIBUTE_NAME => l_attribute_name,
89 P_ATTRIBUTE_ACCESS_LEVEL => l_attribute_access_level,
90 P_ATTRIBUTE1 => p_attribute1,
91 P_ATTRIBUTE2 => p_attribute2,
92 P_ATTRIBUTE3 => p_attribute3,
93 P_ATTRIBUTE4 => p_attribute4,
94 P_ATTRIBUTE5 => p_attribute5,
95 P_ATTRIBUTE6 => p_attribute6,
96 P_ATTRIBUTE7 => p_attribute7,
97 P_ATTRIBUTE8 => p_attribute8,
98 P_ATTRIBUTE9 => p_attribute9,
99 P_ATTRIBUTE10 => p_attribute10,
100 P_ATTRIBUTE11 => p_attribute11,
101 P_ATTRIBUTE12 => p_attribute12,
102 P_ATTRIBUTE13 => p_attribute13,
103 P_ATTRIBUTE14 => p_attribute14,
104 P_ATTRIBUTE15 => p_attribute15,
105 P_ATTRIBUTE_CATEGORY => p_attribute_category,
106 P_USER_ATTRIBUTE_NAME => l_user_attribute_name,
107 X_RETURN_STATUS => l_return_status,
108 X_MSG_COUNT => l_msg_count ,
109 X_MSG_DATA => l_msg_data,
110 X_ATTRIBUTE_ID => l_attribute_id
111 );
112
113 X_ATTRIBUTE_ID := l_attribute_id;
114 X_RETURN_STATUS := l_return_status;
115 X_MSG_COUNT := l_msg_count;
116 X_MSG_DATA := l_msg_data;
117
118
119 --standard commit
120 IF fnd_api.to_boolean (p_commit)
121 THEN
122 COMMIT WORK;
123 END IF;
124
125 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
126
127 EXCEPTION
128 WHEN fnd_api.g_exc_unexpected_error
129 THEN
130
131 ROLLBACK TO TABLE_ATTRIBUTE_SP;
132 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
133 WHEN fnd_api.g_exc_error
134 THEN
135 ROLLBACK TO TABLE_ATTRIBUTE_SP;
136 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
137 WHEN OTHERS
138 THEN
139 ROLLBACK TO ROLE_RELATE_SP;
140 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
141 fnd_message.set_token('P_SQLCODE',SQLCODE);
142 fnd_message.set_token('P_SQLERRM',SQLERRM);
143 fnd_message.set_token('P_API_NAME', l_api_name);
144 FND_MSG_PUB.add;
145 x_return_status := fnd_api.g_ret_sts_unexp_error;
146 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
147
148 END create_table_attribute;
149
150
151 /* Procedure to update the table attributes
152 based on input values passed by calling routines. */
153
154 PROCEDURE update_table_attribute
155 (P_API_VERSION IN NUMBER,
156 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
157 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
158 P_ATTRIBUTE_ID IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ID%TYPE,
159 P_ATTRIBUTE_NAME IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_NAME%TYPE DEFAULT FND_API.G_MISS_CHAR,
160 P_ATTRIBUTE_ACCESS_LEVEL IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ACCESS_LEVEL%TYPE DEFAULT FND_API.G_MISS_CHAR,
161 P_USER_ATTRIBUTE_NAME IN JTF_RS_TABLE_ATTRIBUTES_TL.USER_ATTRIBUTE_NAME%TYPE DEFAULT FND_API.G_MISS_CHAR,
162 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_TABLE_ATTRIBUTES_B.OBJECT_VERSION_NUMBER%TYPE,
163 P_ATTRIBUTE1 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE1%TYPE DEFAULT FND_API.G_MISS_CHAR,
164 P_ATTRIBUTE2 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE2%TYPE DEFAULT FND_API.G_MISS_CHAR,
165 P_ATTRIBUTE3 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE3%TYPE DEFAULT FND_API.G_MISS_CHAR,
166 P_ATTRIBUTE4 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE4%TYPE DEFAULT FND_API.G_MISS_CHAR,
167 P_ATTRIBUTE5 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE5%TYPE DEFAULT FND_API.G_MISS_CHAR,
168 P_ATTRIBUTE6 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE6%TYPE DEFAULT FND_API.G_MISS_CHAR,
169 P_ATTRIBUTE7 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE7%TYPE DEFAULT FND_API.G_MISS_CHAR,
170 P_ATTRIBUTE8 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE8%TYPE DEFAULT FND_API.G_MISS_CHAR,
171 P_ATTRIBUTE9 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE9%TYPE DEFAULT FND_API.G_MISS_CHAR,
172 P_ATTRIBUTE10 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE10%TYPE DEFAULT FND_API.G_MISS_CHAR,
173 P_ATTRIBUTE11 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE11%TYPE DEFAULT FND_API.G_MISS_CHAR,
174 P_ATTRIBUTE12 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE12%TYPE DEFAULT FND_API.G_MISS_CHAR,
175 P_ATTRIBUTE13 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE13%TYPE DEFAULT FND_API.G_MISS_CHAR,
176 P_ATTRIBUTE14 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE14%TYPE DEFAULT FND_API.G_MISS_CHAR,
177 P_ATTRIBUTE15 IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE15%TYPE DEFAULT FND_API.G_MISS_CHAR,
178 P_ATTRIBUTE_CATEGORY IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_CATEGORY%TYPE DEFAULT FND_API.G_MISS_CHAR,
179 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
180 X_MSG_COUNT OUT NOCOPY NUMBER,
181 X_MSG_DATA OUT NOCOPY VARCHAR2
182 )IS
183 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TABLE_ATTRIBUTE';
184 l_api_version CONSTANT NUMBER := 1.0;
185 l_bind_data_id number;
186
187 l_return_code VARCHAR2(100);
188 l_count NUMBER;
189 l_data VARCHAR2(200);
190
191 l_attribute_name JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_NAME%TYPE := p_attribute_name;
192 l_attribute_id JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ID%TYPE := p_attribute_id;
193 l_attribute_access_level JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ACCESS_LEVEL%TYPE := p_attribute_access_level;
194 l_user_attribute_name JTF_RS_TABLE_ATTRIBUTES_TL.USER_ATTRIBUTE_NAME%TYPE := p_user_attribute_name;
195
196 l_object_version_number JTF_RS_TABLE_ATTRIBUTES_B.OBJECT_VERSION_NUMBER%TYPE := P_OBJECT_VERSION_NUM;
197
198 l_return_status VARCHAR2(200);
199 l_msg_count NUMBER;
200 l_msg_data VARCHAR2(200);
201
202 BEGIN
203 --Standard Start of API SAVEPOINT
204 SAVEPOINT TABLE_ATTRIBUTE_SP;
205
206 x_return_status := fnd_api.g_ret_sts_success;
207
208 --Standard Call to check API compatibility
209 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
210 THEN
211 RAISE FND_API.G_EXC_ERROR;
212 END IF;
213
214 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
215 IF FND_API.To_boolean(P_INIT_MSG_LIST)
216 THEN
217 FND_MSG_PUB.Initialize;
218 END IF;
219
220 JTF_RS_TABLE_ATTRIBUTES_PVT.UPDATE_TABLE_ATTRIBUTE(
221 P_API_VERSION => l_api_version,
222 P_INIT_MSG_LIST => p_init_msg_list,
223 P_COMMIT => p_commit,
224 P_ATTRIBUTE_ID => p_attribute_id,
225 P_ATTRIBUTE_NAME => p_attribute_name,
226 P_ATTRIBUTE_ACCESS_LEVEL => p_attribute_access_level,
227 P_USER_ATTRIBUTE_NAME => p_user_attribute_name,
228 P_OBJECT_VERSION_NUM => l_object_version_number,
229 P_ATTRIBUTE1 => p_attribute1,
230 P_ATTRIBUTE2 => P_attribute2,
231 P_ATTRIBUTE3 => p_attribute3,
232 P_ATTRIBUTE4 => p_attribute4,
233 P_ATTRIBUTE5 => p_attribute5,
234 P_ATTRIBUTE6 => p_attribute6,
235 P_ATTRIBUTE7 => p_attribute7,
236 P_ATTRIBUTE8 => p_attribute8,
237 P_ATTRIBUTE9 => p_attribute9,
238 P_ATTRIBUTE10 => p_attribute10,
239 P_ATTRIBUTE11 => p_attribute11,
240 P_ATTRIBUTE12 => p_attribute12,
241 P_ATTRIBUTE13 => p_attribute13,
242 P_ATTRIBUTE14 => p_attribute14,
243 P_ATTRIBUTE15 => p_attribute15,
244 P_ATTRIBUTE_CATEGORY => p_attribute_category,
245 X_RETURN_STATUS => l_return_status,
246 X_MSG_COUNT => l_msg_count,
247 X_MSG_DATA => l_msg_data
248 );
249
250 X_RETURN_STATUS := l_return_status;
251 X_MSG_COUNT := l_msg_count;
252 X_MSG_DATA := l_msg_data;
253 P_OBJECT_VERSION_NUM := l_object_version_number;
254
255
256 IF fnd_api.to_boolean (p_commit)
257 THEN
258 COMMIT WORK;
259 END IF;
260
261 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
262
263 EXCEPTION
264 WHEN fnd_api.g_exc_error
265 THEN
266 ROLLBACK TO TABLE_ATTRIBUTE_SP;
267 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
268
269 WHEN fnd_api.g_exc_unexpected_error
270 THEN
271 ROLLBACK TO TABLE_ATTRIBUTE_SP;
272 x_return_status := fnd_api.g_ret_sts_unexp_error;
273 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
274 WHEN OTHERS
275 THEN
276 ROLLBACK TO TABLE_ATTRIBUTE_SP;
277 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
278 fnd_message.set_token('P_SQLCODE',SQLCODE);
279 fnd_message.set_token('P_SQLERRM',SQLERRM);
280 fnd_message.set_token('P_API_NAME',l_api_name);
281 FND_MSG_PUB.add;
282 x_return_status := fnd_api.g_ret_sts_unexp_error;
283 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
284 END update_table_attribute;
285
286
287 /* Procedure to delete the table attributes. */
288
289 PROCEDURE delete_table_attribute
290 (P_API_VERSION IN NUMBER,
291 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
292 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
293 P_ATTRIBUTE_ID IN JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ID%TYPE,
294 P_OBJECT_VERSION_NUM IN JTF_RS_TABLE_ATTRIBUTES_B.OBJECT_VERSION_NUMBER%TYPE,
295 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
296 X_MSG_COUNT OUT NOCOPY NUMBER,
297 X_MSG_DATA OUT NOCOPY VARCHAR2
298 )IS
299
300
301 l_attribute_id JTF_RS_TABLE_ATTRIBUTES_B.ATTRIBUTE_ID%TYPE := p_attribute_id;
302
303 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TABLE_ATTRIBUTE';
304 l_api_version CONSTANT NUMBER :=1.0;
305 l_bind_data_id number;
306
307 l_return_code VARCHAR2(100);
308 l_count NUMBER;
309 l_data VARCHAR2(200);
310
311 l_return_status VARCHAR2(200);
312 l_msg_count NUMBER;
313 l_msg_data VARCHAR2(200);
314
315 BEGIN
316 --Standard Start of API SAVEPOINT
317 SAVEPOINT TABLE_ATTRIBUTE_SP;
318
319 x_return_status := fnd_api.g_ret_sts_success;
320
321 --Standard Call to check API compatibility
322 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
323 THEN
324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
325 END IF;
326
327 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
328 IF FND_API.To_boolean(P_INIT_MSG_LIST)
329 THEN
330 FND_MSG_PUB.Initialize;
331 END IF;
332
333
334 JTF_RS_TABLE_ATTRIBUTES_PVT.DELETE_TABLE_ATTRIBUTE
335 (P_API_VERSION => l_api_version,
336 P_INIT_MSG_LIST => p_init_msg_list,
337 P_COMMIT => p_commit,
338 P_ATTRIBUTE_ID => p_attribute_id,
339 P_OBJECT_VERSION_NUM => p_object_version_num,
340 X_RETURN_STATUS => l_return_status,
341 X_MSG_COUNT => l_msg_count,
342 X_MSG_DATA => l_msg_data
343 );
344
345 X_RETURN_STATUS := l_return_status;
346 X_MSG_COUNT := l_msg_count;
347 X_MSG_DATA := l_msg_data;
348
349 IF fnd_api.to_boolean (p_commit)
350 THEN
351 COMMIT WORK;
352 END IF;
353
354 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
355
356 EXCEPTION
357 WHEN fnd_api.g_exc_unexpected_error
358 THEN
359 ROLLBACK TO TABLE_ATTRIBUTE_SP;
360 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
361 WHEN fnd_api.g_exc_error
362 THEN
363 ROLLBACK TO TABLE_ATTRIBUTE_SP;
364 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
365 WHEN OTHERS
366 THEN
367 ROLLBACK TO TABLE_ATTRIBUTE_SP;
368 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
369 fnd_message.set_token('P_SQLCODE',SQLCODE);
370 fnd_message.set_token('P_SQLERRM',SQLERRM);
371 fnd_message.set_token('P_API_NAME',l_api_name);
372 FND_MSG_PUB.add;
373 x_return_status := fnd_api.g_ret_sts_unexp_error;
374 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
375
376 END delete_table_attribute;
377
378 END jtf_rs_table_attributes_pub;