[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_DYNAMIC_GROUPS_PUB
Source
1 PACKAGE BODY jtf_rs_dynamic_groups_pub AS
2 /* $Header: jtfrspyb.pls 120.0 2005/05/11 08:21:30 appldev ship $ */
3
4 /*****************************************************************************************
5 This package body defines the procedures for managing dynamic groups , like
6 create, update and delete Dynamic Groups.
7 Its main procedures are as following:
8 Create Dynamic Groups
9 Update Dynamic Groups
10 Delete Dynamic Groups
11 This package valoidates the input parameters to these procedures and then
12 to do business validations and to do actual inserts, updates and deletes into tables.
13 ******************************************************************************************/
14
15 /* Package variables. */
16
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_DYNAMIC_GROUPS_PUB';
18
19
20 /* Procedure to create the Dynamic Groups
21 based on input values passed by calling routines. */
22
23 PROCEDURE create_dynamic_groups
24 (P_API_VERSION IN NUMBER,
25 P_INIT_MSG_LIST IN VARCHAR2,
26 P_COMMIT IN VARCHAR2,
27 P_GROUP_NAME IN JTF_RS_DYNAMIC_GROUPS_TL.GROUP_NAME%TYPE,
28 P_GROUP_DESC IN JTF_RS_DYNAMIC_GROUPS_TL.GROUP_DESC%TYPE,
29 P_USAGE IN JTF_RS_DYNAMIC_GROUPS_B.USAGE%TYPE,
30 P_START_DATE_ACTIVE IN JTF_RS_DYNAMIC_GROUPS_B.START_DATE_ACTIVE%TYPE,
31 P_END_DATE_ACTIVE IN JTF_RS_DYNAMIC_GROUPS_B.END_DATE_ACTIVE%TYPE,
32 P_SQL_TEXT IN JTF_RS_DYNAMIC_GROUPS_B.SQL_TEXT%TYPE,
33 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
34 X_MSG_COUNT OUT NOCOPY NUMBER,
35 X_MSG_DATA OUT NOCOPY VARCHAR2,
36 X_GROUP_ID OUT NOCOPY JTF_RS_DYNAMIC_GROUPS_B.GROUP_ID%TYPE,
37 X_GROUP_NUMBER OUT NOCOPY JTF_RS_DYNAMIC_GROUPS_B.GROUP_NUMBER%TYPE
38 ) IS
39 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_DYNAMIC_GROUPS';
40 l_api_version CONSTANT NUMBER :=1.0;
41
42 l_return_status VARCHAR2(200);
43 l_msg_count NUMBER;
44 l_msg_data VARCHAR2(200);
45 l_rowid VARCHAR2(200);
46
47 l_return_code VARCHAR2(100);
48 l_count NUMBER;
49 l_data VARCHAR2(200);
50
51 l_date Date;
52 l_user_id Number;
53 l_login_id Number;
54
55
56 l_group_name jtf_rs_dynamic_groups_tl.group_name%type;
57 l_group_desc jtf_rs_dynamic_groups_tl.group_desc%type;
58 l_usage jtf_rs_dynamic_groups_b.usage%type;
59 l_start_date_active jtf_rs_dynamic_groups_b.start_date_active%type;
60 l_end_date_active jtf_rs_dynamic_groups_b.end_date_active%type;
61 l_sql_text jtf_rs_dynamic_groups_b.sql_text%type;
62
63
64 l_group_id jtf_rs_dynamic_groups_b.group_id%type;
65 l_group_number jtf_rs_dynamic_groups_b.group_number%type;
66
67 BEGIN
68
69 l_group_name := P_GROUP_NAME;
70 l_group_desc := P_GROUP_DESC;
71 l_usage := P_USAGE;
72 l_start_date_active := P_START_DATE_ACTIVE;
73 l_end_date_active := P_END_DATE_ACTIVE;
74 l_sql_text := P_SQL_TEXT;
75
76 --Standard Start of API SAVEPOINT
77 SAVEPOINT GROUP_DYNAMIC_SP;
78
79 x_return_status := fnd_api.g_ret_sts_success;
80
81 --Standard Call to check API compatibility
82 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
83 THEN
84 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
85 END IF;
86
87 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
88 IF FND_API.To_boolean(P_INIT_MSG_LIST)
89 THEN
90 FND_MSG_PUB.Initialize;
91 END IF;
92
93 --GET USER ID AND SYSDATE
94 l_date := sysdate;
95 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
96 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
97
98 -- Commented the below code to validate usage since it is calling from pvt API
99 --call usage validation
100
101 -- JTF_RESOURCE_UTL.VALIDATE_USAGE(l_usage,
102 -- l_return_status);
103 --
104 -- IF(l_return_status <> fnd_api.g_ret_sts_success)
105 -- THEN
106 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
107 -- fnd_message.set_name ('JTF', 'JTF_RS_USAGE_ERR');
108 -- FND_MSG_PUB.add;
109 -- RAISE fnd_api.g_exc_unexpected_error;
110 -- END IF;
111
112
113
114 --call private api for insert
115 jtf_rs_dynamic_groups_pvt.create_dynamic_groups(
116 P_API_VERSION => 1.0,
117 P_INIT_MSG_LIST => p_init_msg_list,
118 P_COMMIT => null,
119 P_GROUP_NAME => l_group_name,
120 P_GROUP_DESC => l_group_desc,
121 P_USAGE => l_usage,
122 P_START_DATE_ACTIVE => l_start_date_active,
123 P_END_DATE_ACTIVE => l_end_date_active,
124 P_SQL_TEXT => l_sql_text,
125 X_RETURN_STATUS => l_return_status,
126 X_MSG_COUNT => l_msg_count,
127 X_MSG_DATA => l_msg_data,
128 X_GROUP_ID => l_group_id,
129 X_GROUP_NUMBER => l_group_number);
130
131 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
132 IF l_return_status = fnd_api.g_ret_sts_error THEN
133 RAISE fnd_api.g_exc_error;
134 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
135 RAISE fnd_api.g_exc_unexpected_error;
136 END IF;
137 END IF;
138 /*
139 IF(l_return_status <> fnd_api.g_ret_sts_success)
140 THEN
141 x_return_status := fnd_api.g_ret_sts_unexp_error;
142 fnd_message.set_name ('JTF', 'JTF_RS_DYN_GRP_INS_ERR');
143 FND_MSG_PUB.add;
144 RAISE fnd_api.g_exc_unexpected_error;
145 END IF;
146 */
147 x_group_id := l_group_id;
148 x_group_number := l_group_number;
149
150
151
152 EXCEPTION
153
154 WHEN fnd_api.g_exc_error THEN
155 ROLLBACK TO group_dynamic_sp;
156 x_return_status := fnd_api.g_ret_sts_error;
157 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
158 p_data => x_msg_data);
159 WHEN fnd_api.g_exc_unexpected_error THEN
160 ROLLBACK TO group_dynamic_sp;
161 x_return_status := fnd_api.g_ret_sts_unexp_error;
162 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
163 p_data => x_msg_data);
164 WHEN OTHERS THEN
165 ROLLBACK TO group_dynamic_sp;
166 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
167 fnd_message.set_token('P_SQLCODE',SQLCODE);
168 fnd_message.set_token('P_SQLERRM',SQLERRM);
169 fnd_message.set_token('P_API_NAME', l_api_name);
170 FND_MSG_PUB.add;
171 x_return_status := fnd_api.g_ret_sts_unexp_error;
172 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
173 p_data => x_msg_data);
174 /*
175 WHEN fnd_api.g_exc_unexpected_error
176 THEN
177 ROLLBACK TO GROUP_DYNAMIC_SP;
178 fnd_message.set_name ('JTF', 'JTF_RS_DYN_GRP_PUB_ERR');
179 FND_MSG_PUB.add;
180 x_return_status := fnd_api.g_ret_sts_unexp_error;
181 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
182 WHEN OTHERS
183 THEN
184 ROLLBACK TO GROUP_DYNAMIC_SP;
185 fnd_message.set_name ('JTF', 'JTF_RS_DYN_GRP_PUB_ERR');
186 FND_MSG_PUB.add;
187 x_return_status := fnd_api.g_ret_sts_unexp_error;
188 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
189 */
190 END create_dynamic_groups;
191
192
193
194 /* Procedure to update the Dynamic Groups
195 based on input values passed by calling routines. */
196
197 PROCEDURE update_dynamic_groups
198 (P_API_VERSION IN NUMBER,
199 P_INIT_MSG_LIST IN VARCHAR2,
200 P_COMMIT IN VARCHAR2,
201 P_GROUP_ID IN JTF_RS_DYNAMIC_GROUPS_B.GROUP_ID%TYPE,
202 P_GROUP_NUMBER IN JTF_RS_DYNAMIC_GROUPS_B.GROUP_NUMBER%TYPE,
203 P_GROUP_NAME IN JTF_RS_DYNAMIC_GROUPS_TL.GROUP_NAME%TYPE,
204 P_GROUP_DESC IN JTF_RS_DYNAMIC_GROUPS_TL.GROUP_DESC%TYPE,
205 P_USAGE IN JTF_RS_DYNAMIC_GROUPS_B.USAGE%TYPE,
206 P_START_DATE_ACTIVE IN JTF_RS_DYNAMIC_GROUPS_B.START_DATE_ACTIVE%TYPE,
207 P_END_DATE_ACTIVE IN JTF_RS_DYNAMIC_GROUPS_B.END_DATE_ACTIVE%TYPE,
208 P_SQL_TEXT IN JTF_RS_DYNAMIC_GROUPS_B.SQL_TEXT%TYPE,
209 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY JTF_RS_DYNAMIC_GROUPS_B.OBJECT_VERSION_NUMBER%TYPE,
210 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
211 X_MSG_COUNT OUT NOCOPY NUMBER,
212 X_MSG_DATA OUT NOCOPY VARCHAR2
213 ) IS
214
215 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_DYNAMIC_GROUPS';
216 l_api_version CONSTANT NUMBER :=1.0;
217
218 l_return_status VARCHAR2(200);
219 l_msg_count NUMBER;
220 l_msg_data VARCHAR2(200);
221 l_rowid VARCHAR2(200);
222
223 l_return_code VARCHAR2(100);
224 l_count NUMBER;
225 l_data VARCHAR2(200);
226
227 l_date Date;
228 l_user_id Number;
229 l_login_id Number;
230
231
232 l_group_name jtf_rs_dynamic_groups_tl.group_name%type;
233 l_group_desc jtf_rs_dynamic_groups_tl.group_desc%type;
234 l_usage jtf_rs_dynamic_groups_b.usage%type;
235 l_start_date_active jtf_rs_dynamic_groups_b.start_date_active%type;
236 l_end_date_active jtf_rs_dynamic_groups_b.end_date_active%type;
237 l_sql_text jtf_rs_dynamic_groups_b.sql_text%type;
238 l_object_version_number jtf_rs_dynamic_groups_b.object_version_number%type;
239
240 l_group_id jtf_rs_dynamic_groups_b.group_id%type;
241 l_group_number jtf_rs_dynamic_groups_b.group_number%type;
242
243 BEGIN
244
245 l_group_name := P_GROUP_NAME;
246 l_group_desc := P_GROUP_DESC;
247 l_usage := P_USAGE;
248 l_start_date_active := P_START_DATE_ACTIVE;
249 l_end_date_active := P_END_DATE_ACTIVE;
250 l_sql_text := P_SQL_TEXT;
251 l_object_version_number := P_OBJECT_VERSION_NUMBER;
252
253 l_group_id := P_GROUP_ID;
254 l_group_number := P_GROUP_NUMBER;
255
256 --Standard Start of API SAVEPOINT
257 SAVEPOINT GROUP_DYNAMIC_SP;
258
259 x_return_status := fnd_api.g_ret_sts_success;
260
261 --Standard Call to check API compatibility
262 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
263 THEN
264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
265 END IF;
266
267 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
268 IF FND_API.To_boolean(P_INIT_MSG_LIST)
269 THEN
270 FND_MSG_PUB.Initialize;
271 END IF;
272
273 --GET USER ID AND SYSDATE
274 l_date := sysdate;
275 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
276 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
277
278
279 --call private api for insert
280 jtf_rs_dynamic_groups_pvt.update_dynamic_groups(
281 P_API_VERSION => 1.0,
282 P_INIT_MSG_LIST => p_init_msg_list,
283 P_COMMIT => null,
284 P_GROUP_ID => l_group_id,
285 P_GROUP_NUMBER => l_group_number,
286 P_GROUP_NAME => l_group_name,
287 P_GROUP_DESC => l_group_desc,
288 P_USAGE => l_usage,
289 P_START_DATE_ACTIVE => l_start_date_active,
290 P_END_DATE_ACTIVE => l_end_date_active,
291 P_SQL_TEXT => l_sql_text,
292 P_OBJECT_VERSION_NUMBER => p_object_version_number,
293 X_RETURN_STATUS => l_return_status,
294 X_MSG_COUNT => l_msg_count,
295 X_MSG_DATA => l_msg_data
296 );
297
298 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
299 IF l_return_status = fnd_api.g_ret_sts_error THEN
300 RAISE fnd_api.g_exc_error;
301 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
302 RAISE fnd_api.g_exc_unexpected_error;
303 END IF;
304 END IF;
305 /*
306 IF(l_return_status <> fnd_api.g_ret_sts_success)
307 THEN
308 x_return_status := fnd_api.g_ret_sts_unexp_error;
309 fnd_message.set_name ('JTF', 'JTF_RS_DYN_GRP_UPDATE_ERR');
310 FND_MSG_PUB.add;
311 RAISE fnd_api.g_exc_unexpected_error;
312 END IF;
313 */
314 EXCEPTION
315
316 WHEN fnd_api.g_exc_error THEN
317 ROLLBACK TO group_dynamic_sp;
318 x_return_status := fnd_api.g_ret_sts_error;
319 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
320 p_data => x_msg_data);
321 WHEN fnd_api.g_exc_unexpected_error THEN
322 ROLLBACK TO group_dynamic_sp;
323 x_return_status := fnd_api.g_ret_sts_unexp_error;
324 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
325 p_data => x_msg_data);
326 WHEN OTHERS THEN
327 ROLLBACK TO group_dynamic_sp;
328 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
329 fnd_message.set_token('P_SQLCODE',SQLCODE);
330 fnd_message.set_token('P_SQLERRM',SQLERRM);
331 fnd_message.set_token('P_API_NAME', l_api_name);
332 FND_MSG_PUB.add;
333 x_return_status := fnd_api.g_ret_sts_unexp_error;
334 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
335 p_data => x_msg_data);
336 /*
337 WHEN fnd_api.g_exc_unexpected_error
338 THEN
339 ROLLBACK TO GROUP_DYNAMIC_SP;
340 fnd_message.set_name ('JTF', 'JTF_RS_DYN_GRP_PUB_ERR');
341 FND_MSG_PUB.add;
342 x_return_status := fnd_api.g_ret_sts_unexp_error;
343 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
344 WHEN OTHERS
345 THEN
346 ROLLBACK TO GROUP_DYNAMIC_SP;
347 fnd_message.set_name ('JTF', 'JTF_RS_DYN_GRP_PUB_ERR');
348 FND_MSG_PUB.add;
349 x_return_status := fnd_api.g_ret_sts_unexp_error;
350 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
351 */
352 END update_dynamic_groups;
353
354
355 /* Procedure to delete the Dynamic Groups. */
356
357 PROCEDURE delete_dynamic_groups
358 (P_API_VERSION IN NUMBER,
359 P_INIT_MSG_LIST IN VARCHAR2,
360 P_COMMIT IN VARCHAR2,
361 P_GROUP_ID IN JTF_RS_DYNAMIC_GROUPS_B.GROUP_ID%TYPE,
362 P_OBJECT_VERSION_NUMBER IN JTF_RS_DYNAMIC_GROUPS_B.OBJECT_VERSION_NUMBER%TYPE,
363 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
364 X_MSG_COUNT OUT NOCOPY NUMBER,
365 X_MSG_DATA OUT NOCOPY VARCHAR2
366 ) IS
367 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_DYNAMIC_GROUPS';
368 l_api_version CONSTANT NUMBER :=1.0;
369
370 l_return_status VARCHAR2(200);
371 l_msg_count NUMBER;
372 l_msg_data VARCHAR2(200);
373 l_rowid VARCHAR2(200);
374
375 l_return_code VARCHAR2(100);
376 l_count NUMBER;
377 l_data VARCHAR2(200);
378
379 l_date Date;
380 l_user_id Number;
381 l_login_id Number;
382
383 l_group_id jtf_rs_dynamic_groups_b.group_id%type;
384
385
386 BEGIN
387
388 l_group_id := P_GROUP_ID;
389
390 --Standard Start of API SAVEPOINT
391 SAVEPOINT GROUP_DYNAMIC_SP;
392
393 x_return_status := fnd_api.g_ret_sts_success;
394
395 --Standard Call to check API compatibility
396 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
397 THEN
398 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
399 END IF;
400
401 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
402 IF FND_API.To_boolean(P_INIT_MSG_LIST)
403 THEN
404 FND_MSG_PUB.Initialize;
405 END IF;
406
407 --GET USER ID AND SYSDATE
408 l_date := sysdate;
409 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
410 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
411
412
413 --call private api for delete
414 jtf_rs_dynamic_groups_pvt.delete_dynamic_groups(
415 P_API_VERSION => 1.0,
416 P_INIT_MSG_LIST => p_init_msg_list,
417 P_COMMIT => null,
418 P_GROUP_ID => l_group_id,
419 P_OBJECT_VERSION_NUMBER => p_object_version_number,
420 X_RETURN_STATUS => l_return_status,
421 X_MSG_COUNT => l_msg_count,
422 X_MSG_DATA => l_msg_data
423 );
424
425 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
426 IF l_return_status = fnd_api.g_ret_sts_error THEN
427 RAISE fnd_api.g_exc_error;
428 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
429 RAISE fnd_api.g_exc_unexpected_error;
430 END IF;
431 END IF;
432 /*
433 IF(l_return_status <> fnd_api.g_ret_sts_success)
434 THEN
435 x_return_status := fnd_api.g_ret_sts_unexp_error;
436 fnd_message.set_name ('JTF', 'JTF_RS_DYN_GRP_DELETE_ERR');
437 FND_MSG_PUB.add;
438 RAISE fnd_api.g_exc_unexpected_error;
439 END IF;
440 */
441 EXCEPTION
442
443 WHEN fnd_api.g_exc_error THEN
444 ROLLBACK TO group_dynamic_sp;
445 x_return_status := fnd_api.g_ret_sts_error;
446 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
447 p_data => x_msg_data);
448 WHEN fnd_api.g_exc_unexpected_error THEN
449 ROLLBACK TO group_dynamic_sp;
450 x_return_status := fnd_api.g_ret_sts_unexp_error;
451 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
452 p_data => x_msg_data);
453 WHEN OTHERS THEN
454 ROLLBACK TO group_dynamic_sp;
455 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
456 fnd_message.set_token('P_SQLCODE',SQLCODE);
457 fnd_message.set_token('P_SQLERRM',SQLERRM);
458 fnd_message.set_token('P_API_NAME', l_api_name);
459 FND_MSG_PUB.add;
460 x_return_status := fnd_api.g_ret_sts_unexp_error;
461 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
462 p_data => x_msg_data);
463 /*
464 WHEN fnd_api.g_exc_unexpected_error
465 THEN
466 ROLLBACK TO GROUP_DYNAMIC_SP;
467 fnd_message.set_name ('JTF', 'JTF_RS_DYN_GRP_PUB_ERR');
468 FND_MSG_PUB.add;
469 x_return_status := fnd_api.g_ret_sts_unexp_error;
470 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
471 WHEN OTHERS
472 THEN
473 ROLLBACK TO GROUP_DYNAMIC_SP;
474 fnd_message.set_name ('JTF', 'JTF_RS_DYN_GRP_PUB_ERR');
475 FND_MSG_PUB.add;
476 x_return_status := fnd_api.g_ret_sts_unexp_error;
477 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
478 */
479 END delete_dynamic_groups;
480
481
482 END jtf_rs_dynamic_groups_pub;