[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_SKILL_LEVELS_PUB
Source
1 PACKAGE BODY jtf_rs_skill_levels_pub AS
2 /* $Header: jtfrsusb.pls 120.0 2005/05/11 08:22:50 appldev ship $ */
3
4 /*****************************************************************************************
5 This is a public API that user API will invoke.
6 It provides procedures for managing seed data of jtf_rs_skill_levels_vl tables
7 create, update and delete rows
8 Its main procedures are as following:
9 Create skills
10 Update skills
11 Delete skills
12 Calls to these procedures will call procedures of jtf_rs_skill_levels_pvt
13 to do inserts, updates and deletes into tables.
14 ******************************************************************************************/
15
16 /* Package variables. */
17
18 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_SKILL_LEVELS_PUB';
19 G_NAME VARCHAR2(240);
20
21 /*****************************************************************************************
22 This is a public API that user API will invoke.
23 It provides procedures for managing seed data of jtf_rs_skill_levels_vl view
24 create, update and delete rows
25 Its main procedures are as following:
26 Create skills
27 Update skills
28 Delete skills
29 Calls to these procedures will call procedures of jtf_rs_skill_levels_pvt
30 to do inserts, updates and deletes into tables.
31 ******************************************************************************************/
32
33
34 /* Procedure to create the skill levels
35 based on input values passed by calling routines. */
36
37 PROCEDURE create_skills
38 (P_API_VERSION IN NUMBER,
39 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
40 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
41 P_SKILL_LEVEL IN JTF_RS_SKILL_LEVELS_B.SKILL_LEVEL%TYPE,
42 P_LEVEL_NAME IN JTF_RS_SKILL_LEVELS_TL.LEVEL_NAME%TYPE,
43 P_LEVEL_DESC IN JTF_RS_SKILL_LEVELS_TL.LEVEL_DESC%TYPE DEFAULT NULL,
44 P_ATTRIBUTE1 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE1%TYPE DEFAULT NULL,
45 P_ATTRIBUTE2 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE2%TYPE DEFAULT NULL,
46 P_ATTRIBUTE3 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE3%TYPE DEFAULT NULL,
47 P_ATTRIBUTE4 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE4%TYPE DEFAULT NULL,
48 P_ATTRIBUTE5 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE5%TYPE DEFAULT NULL,
49 P_ATTRIBUTE6 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE6%TYPE DEFAULT NULL,
50 P_ATTRIBUTE7 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE7%TYPE DEFAULT NULL,
51 P_ATTRIBUTE8 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE8%TYPE DEFAULT NULL,
52 P_ATTRIBUTE9 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE9%TYPE DEFAULT NULL,
53 P_ATTRIBUTE10 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE10%TYPE DEFAULT NULL,
54 P_ATTRIBUTE11 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE11%TYPE DEFAULT NULL,
55 P_ATTRIBUTE12 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE12%TYPE DEFAULT NULL,
56 P_ATTRIBUTE13 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE13%TYPE DEFAULT NULL,
57 P_ATTRIBUTE14 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE14%TYPE DEFAULT NULL,
58 P_ATTRIBUTE15 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE15%TYPE DEFAULT NULL,
59 P_ATTRIBUTE_CATEGORY IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE_CATEGORY%TYPE DEFAULT NULL,
60 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
61 X_MSG_COUNT OUT NOCOPY NUMBER,
62 X_MSG_DATA OUT NOCOPY VARCHAR2,
63 X_SKILL_LEVEL_ID OUT NOCOPY JTF_RS_SKILL_LEVELS_B.SKILL_LEVEL_ID%TYPE
64 )IS
65
66 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SKILLS';
67 l_api_version CONSTANT NUMBER := 1.0;
68 l_bind_data_id number;
69
70 l_object_version_number number;
71
72 l_skill_level_id JTF_RS_SKILL_LEVELS_B.SKILL_LEVEL_ID%TYPE;
73
74 l_return_code VARCHAR2(100);
75 l_count NUMBER;
76 l_data VARCHAR2(200);
77
78 l_return_status VARCHAR2(200);
79 l_msg_count NUMBER;
80 l_msg_data VARCHAR2(200);
81
82 BEGIN
83 --Standard Start of API SAVEPOINT
84 SAVEPOINT CREATE_SKILL_LEVELS_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 JTF_RS_SKILL_LEVELS_PVT.CREATE_SKILLS(
101 P_API_VERSION => l_api_version,
102 P_INIT_MSG_LIST => p_init_msg_list,
103 P_COMMIT => p_commit,
104 P_SKILL_LEVEL => p_skill_level,
105 P_LEVEL_NAME => p_level_name,
106 P_LEVEL_DESC => p_level_desc,
107 P_ATTRIBUTE1 => p_attribute1,
108 P_ATTRIBUTE2 => p_attribute2,
109 P_ATTRIBUTE3 => p_attribute3,
110 P_ATTRIBUTE4 => p_attribute4,
111 P_ATTRIBUTE5 => p_attribute5,
112 P_ATTRIBUTE6 => p_attribute6,
113 P_ATTRIBUTE7 => p_attribute7,
114 P_ATTRIBUTE8 => p_attribute8,
115 P_ATTRIBUTE9 => p_attribute9,
116 P_ATTRIBUTE10 => p_attribute10,
117 P_ATTRIBUTE11 => p_attribute11,
118 P_ATTRIBUTE12 => p_attribute12,
119 P_ATTRIBUTE13 => p_attribute13,
120 P_ATTRIBUTE14 => p_attribute14,
121 P_ATTRIBUTE15 => p_attribute15,
122 P_ATTRIBUTE_CATEGORY => p_attribute_category,
123 X_RETURN_STATUS => l_return_status,
124 X_MSG_COUNT => l_msg_count ,
125 X_MSG_DATA => l_msg_data,
126 X_SKILL_LEVEL_ID => l_skill_level_id
127 );
128
129 X_SKILL_LEVEL_ID := l_skill_level_id;
130 X_RETURN_STATUS := l_return_status;
131 X_MSG_COUNT := l_msg_count;
132 X_MSG_DATA := l_msg_data;
133
134
135 --standard commit
136 IF fnd_api.to_boolean (p_commit)
137 THEN
138 COMMIT WORK;
139 END IF;
140
141 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
142
143 EXCEPTION
144 WHEN fnd_api.g_exc_unexpected_error
145 THEN
146
147 ROLLBACK TO CREATE_SKILL_LEVELS_SP;
148 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
149 WHEN fnd_api.g_exc_error
150 THEN
151 ROLLBACK TO CREATE_SKILL_LEVELS_SP;
152 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
153 WHEN OTHERS
154 THEN
155 ROLLBACK TO CREATE_SKILL_LEVELS_SP;
156 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
157 fnd_message.set_token('P_SQLCODE',SQLCODE);
158 fnd_message.set_token('P_SQLERRM',SQLERRM);
159 fnd_message.set_token('P_API_NAME', l_api_name);
160 FND_MSG_PUB.add;
161 x_return_status := fnd_api.g_ret_sts_unexp_error;
162 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
163
164 END CREATE_SKILLS;
165
166
167 /* Procedure to update skill levels
168 based on input values passed by calling routines. */
169
170 PROCEDURE update_skills
171 (P_API_VERSION IN NUMBER,
172 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
173 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
174 P_SKILL_LEVEL_ID IN JTF_RS_SKILL_LEVELS_B.SKILL_LEVEL_ID%TYPE,
175 P_SKILL_LEVEL IN JTF_RS_SKILL_LEVELS_B.SKILL_LEVEL%TYPE DEFAULT FND_API.G_MISS_NUM,
176 P_LEVEL_NAME IN JTF_RS_SKILL_LEVELS_TL.LEVEL_NAME%TYPE DEFAULT FND_API.G_MISS_CHAR,
177 P_LEVEL_DESC IN JTF_RS_SKILL_LEVELS_TL.LEVEL_DESC%TYPE DEFAULT FND_API.G_MISS_CHAR,
178 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_SKILL_LEVELS_B.OBJECT_VERSION_NUMBER%TYPE,
179 P_ATTRIBUTE1 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE1%TYPE DEFAULT FND_API.G_MISS_CHAR,
180 P_ATTRIBUTE2 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE2%TYPE DEFAULT FND_API.G_MISS_CHAR,
181 P_ATTRIBUTE3 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE3%TYPE DEFAULT FND_API.G_MISS_CHAR,
182 P_ATTRIBUTE4 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE4%TYPE DEFAULT FND_API.G_MISS_CHAR,
183 P_ATTRIBUTE5 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE5%TYPE DEFAULT FND_API.G_MISS_CHAR,
184 P_ATTRIBUTE6 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE6%TYPE DEFAULT FND_API.G_MISS_CHAR,
185 P_ATTRIBUTE7 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE7%TYPE DEFAULT FND_API.G_MISS_CHAR,
186 P_ATTRIBUTE8 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE8%TYPE DEFAULT FND_API.G_MISS_CHAR,
187 P_ATTRIBUTE9 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE9%TYPE DEFAULT FND_API.G_MISS_CHAR,
188 P_ATTRIBUTE10 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE10%TYPE DEFAULT FND_API.G_MISS_CHAR,
189 P_ATTRIBUTE11 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE11%TYPE DEFAULT FND_API.G_MISS_CHAR,
190 P_ATTRIBUTE12 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE12%TYPE DEFAULT FND_API.G_MISS_CHAR,
191 P_ATTRIBUTE13 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE13%TYPE DEFAULT FND_API.G_MISS_CHAR,
192 P_ATTRIBUTE14 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE14%TYPE DEFAULT FND_API.G_MISS_CHAR,
193 P_ATTRIBUTE15 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE15%TYPE DEFAULT FND_API.G_MISS_CHAR,
194 P_ATTRIBUTE_CATEGORY IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE_CATEGORY%TYPE DEFAULT FND_API.G_MISS_CHAR,
195 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
196 X_MSG_COUNT OUT NOCOPY NUMBER,
197 X_MSG_DATA OUT NOCOPY VARCHAR2
198 ) IS
199 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SKILLS';
200 l_api_version CONSTANT NUMBER := 1.0;
201 l_bind_data_id number;
202
203 l_return_code VARCHAR2(100);
204 l_count NUMBER;
205 l_data VARCHAR2(200);
206
207 l_object_version_number JTF_RS_SKILL_LEVELS_B.OBJECT_VERSION_NUMBER%TYPE := P_OBJECT_VERSION_NUM;
208
209 l_return_status VARCHAR2(200);
210 l_msg_count NUMBER;
211 l_msg_data VARCHAR2(200);
212
213 BEGIN
214 --Standard Start of API SAVEPOINT
215 SAVEPOINT UPDATE_SKILL_LEVELS_SP;
216
217 x_return_status := fnd_api.g_ret_sts_success;
218
219 --Standard Call to check API compatibility
220 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
221 THEN
222 RAISE FND_API.G_EXC_ERROR;
223 END IF;
224
225 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
226 IF FND_API.To_boolean(P_INIT_MSG_LIST)
227 THEN
228 FND_MSG_PUB.Initialize;
229 END IF;
230
231 JTF_RS_SKILL_LEVELS_PVT.UPDATE_SKILLS(
232 P_API_VERSION => l_api_version,
233 P_INIT_MSG_LIST => p_init_msg_list,
234 P_COMMIT => p_commit,
235 P_SKILL_LEVEL_ID => p_skill_level_id,
236 P_SKILL_LEVEL => p_skill_level,
237 P_LEVEL_NAME => p_level_name,
238 P_LEVEL_DESC => p_level_desc,
239 P_OBJECT_VERSION_NUM => l_object_version_number,
240 P_ATTRIBUTE1 => p_attribute1,
241 P_ATTRIBUTE2 => P_attribute2,
242 P_ATTRIBUTE3 => p_attribute3,
243 P_ATTRIBUTE4 => p_attribute4,
244 P_ATTRIBUTE5 => p_attribute5,
245 P_ATTRIBUTE6 => p_attribute6,
246 P_ATTRIBUTE7 => p_attribute7,
247 P_ATTRIBUTE8 => p_attribute8,
248 P_ATTRIBUTE9 => p_attribute9,
249 P_ATTRIBUTE10 => p_attribute10,
250 P_ATTRIBUTE11 => p_attribute11,
251 P_ATTRIBUTE12 => p_attribute12,
252 P_ATTRIBUTE13 => p_attribute13,
253 P_ATTRIBUTE14 => p_attribute14,
254 P_ATTRIBUTE15 => p_attribute15,
255 P_ATTRIBUTE_CATEGORY => p_attribute_category,
256 X_RETURN_STATUS => l_return_status,
257 X_MSG_COUNT => l_msg_count,
258 X_MSG_DATA => l_msg_data
259 );
260
261 X_RETURN_STATUS := l_return_status;
262 X_MSG_COUNT := l_msg_count;
263 X_MSG_DATA := l_msg_data;
264 P_OBJECT_VERSION_NUM := l_object_version_number;
265
266
267 IF fnd_api.to_boolean (p_commit)
268 THEN
269 COMMIT WORK;
270 END IF;
271
272 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
273
274 EXCEPTION
275 WHEN fnd_api.g_exc_error
276 THEN
277 ROLLBACK TO UPDATE_SKILL_LEVELS_SP;
278 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
279
280 WHEN fnd_api.g_exc_unexpected_error
281 THEN
282 ROLLBACK TO UPDATE_SKILL_LEVELS_SP;
283 x_return_status := fnd_api.g_ret_sts_unexp_error;
284 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
285 WHEN OTHERS
286 THEN
287 ROLLBACK TO UPDATE_SKILL_LEVELS_SP;
288 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
289 fnd_message.set_token('P_SQLCODE',SQLCODE);
290 fnd_message.set_token('P_SQLERRM',SQLERRM);
291 fnd_message.set_token('P_API_NAME',l_api_name);
292 FND_MSG_PUB.add;
293 x_return_status := fnd_api.g_ret_sts_unexp_error;
294 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
295 END update_skills;
296
297
298 PROCEDURE delete_skills
299 (P_API_VERSION IN NUMBER,
300 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
301 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
302 P_SKILL_LEVEL_ID IN JTF_RS_SKILL_LEVELS_B.skill_level_id%TYPE,
303 P_OBJECT_VERSION_NUM IN JTF_RS_SKILL_LEVELS_B.OBJECT_VERSION_NUMBER%TYPE,
304 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
305 X_MSG_COUNT OUT NOCOPY NUMBER,
306 X_MSG_DATA OUT NOCOPY VARCHAR2
307 )IS
308
309
310 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SKILLS';
311 l_api_version CONSTANT NUMBER :=1.0;
312 l_bind_data_id number;
313
314 l_return_code VARCHAR2(100);
315 l_count NUMBER;
316 l_data VARCHAR2(200);
317
318 l_return_status VARCHAR2(200);
319 l_msg_count NUMBER;
320 l_msg_data VARCHAR2(200);
321
322 BEGIN
323 --Standard Start of API SAVEPOINT
324 SAVEPOINT DELETE_SKILL_LEVELS_SP;
325
326 x_return_status := fnd_api.g_ret_sts_success;
327
328 --Standard Call to check API compatibility
329 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
330 THEN
331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
332 END IF;
333
334 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
335 IF FND_API.To_boolean(P_INIT_MSG_LIST)
336 THEN
337 FND_MSG_PUB.Initialize;
338 END IF;
339
340
341 JTF_RS_SKILL_LEVELS_PVT.DELETE_SKILLS
342 (P_API_VERSION => l_api_version,
343 P_INIT_MSG_LIST => p_init_msg_list,
344 P_COMMIT => p_commit,
345 P_SKILL_LEVEL_ID => p_skill_level_id,
346 P_OBJECT_VERSION_NUM => p_object_version_num,
347 X_RETURN_STATUS => l_return_status,
348 X_MSG_COUNT => l_msg_count,
349 X_MSG_DATA => l_msg_data
350 );
351
352 X_RETURN_STATUS := l_return_status;
353 X_MSG_COUNT := l_msg_count;
354 X_MSG_DATA := l_msg_data;
355
356 IF fnd_api.to_boolean (p_commit)
357 THEN
358 COMMIT WORK;
359 END IF;
360
361 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
362
363 EXCEPTION
364 WHEN fnd_api.g_exc_unexpected_error
365 THEN
366 ROLLBACK TO DELETE_SKILL_LEVELS_SP;
367 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
368 WHEN fnd_api.g_exc_error
369 THEN
370 ROLLBACK TO DELETE_SKILL_LEVELS_SP;
371 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
372 WHEN OTHERS
373 THEN
374 ROLLBACK TO DELETE_SKILL_LEVELS_SP;
375 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
376 fnd_message.set_token('P_SQLCODE',SQLCODE);
377 fnd_message.set_token('P_SQLERRM',SQLERRM);
378 fnd_message.set_token('P_API_NAME',l_api_name);
379 FND_MSG_PUB.add;
380 x_return_status := fnd_api.g_ret_sts_unexp_error;
381 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
382
383 END delete_skills;
384
385 END jtf_rs_skill_levels_pub;