[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_GROUP_MEMBERS_PUB
Source
1 PACKAGE BODY jtf_rs_group_members_pub AS
2 /* $Header: jtfrspmb.pls 120.0 2005/05/11 08:21:16 appldev ship $ */
3
4 /*****************************************************************************************
5 This package body defines the procedures for managing resource group members, like
6 create, update and delete resource group members.
7 Its main procedures are as following:
8 Create Resource Group Members
9 Delete Resource Group Members
10 This package validates the input parameters to these procedures and then
11 Calls corresponding procedures from jtf_rs_group_members_pvt
12 to do business validations and to do actual inserts and deletes into tables.
13 ******************************************************************************************/
14
15 /* Package variables. */
16
17 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_GROUP_MEMBERS_PUB';
18
19
20 /* Procedure to create the resource group members
21 based on input values passed by calling routines. */
22
23 PROCEDURE create_resource_group_members
24 (P_API_VERSION IN NUMBER,
25 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
26 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
27 P_GROUP_ID IN JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE,
28 P_GROUP_NUMBER IN JTF_RS_GROUPS_VL.GROUP_NUMBER%TYPE,
29 P_RESOURCE_ID IN JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE,
30 P_RESOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
31 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
32 X_MSG_COUNT OUT NOCOPY NUMBER,
33 X_MSG_DATA OUT NOCOPY VARCHAR2,
34 X_GROUP_MEMBER_ID OUT NOCOPY JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE
35 ) IS
36
37
38 l_api_version CONSTANT NUMBER := 1.0;
39 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_GROUP_MEMBERS';
40 l_group_id jtf_rs_group_members.group_id%TYPE := p_group_id;
41 l_group_number jtf_rs_groups_vl.group_number%TYPE := p_group_number;
42 l_resource_id jtf_rs_group_members.resource_id%type := p_resource_id;
43 l_resource_number jtf_rs_resource_extns.resource_number%type := p_resource_number;
44 l_group_member_id jtf_rs_group_members.group_member_id%TYPE;
45
46 l_group_id_out jtf_rs_group_members.group_id%TYPE;
47 l_resource_id_out jtf_rs_group_members.resource_id%type;
48
49 BEGIN
50
51
52 SAVEPOINT create_resource_member_pub;
53
54 x_return_status := fnd_api.g_ret_sts_success;
55
56 -- DBMS_OUTPUT.put_line(' Started Create Resource Member Pub ');
57
58
59 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
60
61 RAISE fnd_api.g_exc_unexpected_error;
62
63 END IF;
64
65
66 IF fnd_api.to_boolean(p_init_msg_list) THEN
67
68 fnd_msg_pub.initialize;
69
70 END IF;
71
72
73 /* Validate the Resource Group. */
74
75 BEGIN
76
77 jtf_resource_utl.validate_resource_group(
78 p_group_id => l_group_id,
79 p_group_number => l_group_number,
80 x_return_status => x_return_status,
81 x_group_id => l_group_id_out
82 );
83 -- added for NOCOPY
84 l_group_id := l_group_id_out;
85
86 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
87
88 x_return_status := fnd_api.g_ret_sts_unexp_error;
89
90 RAISE fnd_api.g_exc_unexpected_error;
91
92 END IF;
93
94 END; /* End of Resource Group Validation */
95
96
97
98 /* Validate the Resource */
99
100 BEGIN
101
102 jtf_resource_utl.validate_resource_number(
103 p_resource_id => l_resource_id,
104 p_resource_number => l_resource_number,
105 x_return_status => x_return_status,
106 x_resource_id => l_resource_id_out
107 );
108
109 -- added for NOCOPY
110 l_resource_id := l_resource_id_out;
111
112 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
113
114 x_return_status := fnd_api.g_ret_sts_unexp_error;
115
116 RAISE fnd_api.g_exc_unexpected_error;
117
118 END IF;
119
120 END; /* End of Resource Validation */
121
122
123
124 jtf_rs_group_members_pvt.create_resource_group_members
125 (P_API_VERSION => 1,
126 P_INIT_MSG_LIST => fnd_api.g_false,
127 P_COMMIT => fnd_api.g_false,
128 P_GROUP_ID => l_group_id,
129 P_RESOURCE_ID => l_resource_id,
130 X_RETURN_STATUS => x_return_status,
131 X_MSG_COUNT => x_msg_count,
132 X_MSG_DATA => x_msg_data,
133 X_GROUP_MEMBER_ID => x_group_member_id
134 );
135
136
137 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
138
139 -- dbms_output.put_line('Failed status from call to private procedure');
140
141 RAISE fnd_api.g_exc_unexpected_error;
142
143 END IF;
144
145
146 IF fnd_api.to_boolean(p_commit) THEN
147
148 COMMIT WORK;
149
150 END IF;
151
152 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
153
154
155 EXCEPTION
156
157
158 WHEN fnd_api.g_exc_unexpected_error THEN
159
160 -- DBMS_OUTPUT.put_line (' ========================================== ');
161
162 -- DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error ======= ======== ');
163
164 ROLLBACK TO create_resource_member_pub;
165
166 x_return_status := fnd_api.g_ret_sts_unexp_error;
167
168 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
169
170
171 WHEN OTHERS THEN
172
173 -- DBMS_OUTPUT.put_line (' ========================================== ');
174
175 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Create Group Member Pub ============= ');
176
177 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
178
179 ROLLBACK TO create_resource_member_pub;
180 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
181 fnd_message.set_token('P_SQLCODE',SQLCODE);
182 fnd_message.set_token('P_SQLERRM',SQLERRM);
183 fnd_message.set_token('P_API_NAME',l_api_name);
184 FND_MSG_PUB.add;
185
186 x_return_status := fnd_api.g_ret_sts_unexp_error;
187
188 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
189
190
191
192 END create_resource_group_members;
193
194
195 /* Procedure to update the resource group members
196 based on input values passed by calling routines. */
197
198 PROCEDURE update_resource_group_members
199 (P_API_VERSION IN NUMBER,
200 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
201 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
202 P_GROUP_MEMBER_ID IN JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE,
203 P_GROUP_ID IN JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE,
204 P_GROUP_NUMBER IN JTF_RS_GROUPS_VL.GROUP_NUMBER%TYPE,
205 P_RESOURCE_ID IN JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE,
206 P_RESOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
207 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY JTF_RS_GROUP_MEMBERS.OBJECT_VERSION_NUMBER%TYPE,
208 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
209 X_MSG_COUNT OUT NOCOPY NUMBER,
210 X_MSG_DATA OUT NOCOPY VARCHAR2
211 ) IS
212
213
214 l_api_version CONSTANT NUMBER := 1.0;
215 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_GROUP_MEMBERS';
216 l_group_id jtf_rs_group_members.group_id%TYPE := p_group_id;
217 l_group_number jtf_rs_groups_vl.group_number%TYPE := p_group_number;
218 l_resource_id jtf_rs_group_members.resource_id%type := p_resource_id;
219 l_resource_number jtf_rs_resource_extns.resource_number%type := p_resource_number;
220 l_group_member_id jtf_rs_group_members.group_member_id%TYPE := p_group_member_id;
221
222 l_group_id_out jtf_rs_group_members.group_id%TYPE;
223 l_resource_id_out jtf_rs_group_members.resource_id%type;
224
225 BEGIN
226
227
228 SAVEPOINT update_resource_member_pub;
229
230 x_return_status := fnd_api.g_ret_sts_success;
231
232 -- DBMS_OUTPUT.put_line(' Started Update Resource Member Pub ');
233
234
235 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
236
237 RAISE fnd_api.g_exc_unexpected_error;
238
239 END IF;
240
241
242 IF fnd_api.to_boolean(p_init_msg_list) THEN
243
244 fnd_msg_pub.initialize;
245
246 END IF;
247
248
249 /* Validate the Resource Group. */
250
251 BEGIN
252
253 jtf_resource_utl.validate_resource_group(
254 p_group_id => l_group_id,
255 p_group_number => l_group_number,
256 x_return_status => x_return_status,
257 x_group_id => l_group_id_out
258 );
259 -- added for NOCOPY
260 l_group_id := l_group_id_out;
261
262 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
263
264 x_return_status := fnd_api.g_ret_sts_unexp_error;
265
266 RAISE fnd_api.g_exc_unexpected_error;
267
268 END IF;
269
270 END; /* End of Resource Group Validation */
271
272
273
274 /* Validate the Resource */
275
276 BEGIN
277
278 jtf_resource_utl.validate_resource_number(
279 p_resource_id => l_resource_id,
280 p_resource_number => l_resource_number,
281 x_return_status => x_return_status,
282 x_resource_id => l_resource_id_out
283 );
284 -- added for NOCOPY
285 l_resource_id := l_resource_id_out;
286
287 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
288
289 x_return_status := fnd_api.g_ret_sts_unexp_error;
290
291 RAISE fnd_api.g_exc_unexpected_error;
292
293 END IF;
294
295 END; /* End of Resource Validation */
296
297
298
299 jtf_rs_group_members_pvt.update_resource_group_members
300 (P_API_VERSION => 1,
301 P_INIT_MSG_LIST => fnd_api.g_false,
302 P_COMMIT => fnd_api.g_false,
303 P_GROUP_MEMBER_ID => l_group_member_id,
304 P_GROUP_ID => l_group_id,
305 P_RESOURCE_ID => l_resource_id,
306 P_OBJECT_VERSION_NUMBER => p_object_version_number,
307 X_RETURN_STATUS => x_return_status,
308 X_MSG_COUNT => x_msg_count,
309 X_MSG_DATA => x_msg_data
310 );
311
312
313 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
314
315 -- dbms_output.put_line('Failed status from call to private procedure');
316
317 RAISE fnd_api.g_exc_unexpected_error;
318
319 END IF;
320
321
322 IF fnd_api.to_boolean(p_commit) THEN
323
324 COMMIT WORK;
325
326 END IF;
327
328 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
329
330
331 EXCEPTION
332
333
334 WHEN fnd_api.g_exc_unexpected_error THEN
335
336 -- DBMS_OUTPUT.put_line (' ========================================== ');
337
338 -- DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error ======= ======== ');
339
340 ROLLBACK TO update_resource_member_pub;
341
342 x_return_status := fnd_api.g_ret_sts_unexp_error;
343
344 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
345
346
347 WHEN OTHERS THEN
348
349 -- DBMS_OUTPUT.put_line (' ========================================== ');
350
351 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Update Group Member Pub ============= ');
352
353 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
354
355 ROLLBACK TO update_resource_member_pub;
356 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
357 fnd_message.set_token('P_SQLCODE',SQLCODE);
358 fnd_message.set_token('P_SQLERRM',SQLERRM);
359 fnd_message.set_token('P_API_NAME',l_api_name);
360 FND_MSG_PUB.add;
361
362 x_return_status := fnd_api.g_ret_sts_unexp_error;
363
364 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
365
366
367
368 END update_resource_group_members;
369
370
371
372 /* Procedure to delete the resource group members. */
373
374 PROCEDURE delete_resource_group_members
375 (P_API_VERSION IN NUMBER,
376 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
377 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
378 P_GROUP_ID IN JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE,
379 P_GROUP_NUMBER IN JTF_RS_GROUPS_VL.GROUP_NUMBER%TYPE,
380 P_RESOURCE_ID IN JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE,
381 P_RESOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
382 P_OBJECT_VERSION_NUM IN JTF_RS_GROUP_MEMBERS.OBJECT_VERSION_NUMBER%TYPE,
383 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
384 X_MSG_COUNT OUT NOCOPY NUMBER,
385 X_MSG_DATA OUT NOCOPY VARCHAR2
386 ) IS
387
388 l_api_version CONSTANT NUMBER := 1.0;
389 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_GROUP_MEMBERS';
390 l_group_id jtf_rs_group_members.group_id%TYPE := p_group_id;
391 l_group_number jtf_rs_groups_vl.group_number%TYPE := p_group_number;
392 l_resource_id jtf_rs_group_members.resource_id%type := p_resource_id;
393 l_resource_number jtf_rs_resource_extns.resource_number%type := p_resource_number;
394 l_group_member_id jtf_rs_group_members.group_member_id%TYPE;
395 l_group_id_out jtf_rs_group_members.group_id%TYPE;
396 l_resource_id_out jtf_rs_group_members.resource_id%type;
397
398
399 CURSOR c_resource_id IS
400 SELECT resource_id
401 FROM jtf_rs_resource_extns
402 WHERE resource_id = p_resource_id;
403
404 CURSOR c_resource_number IS
405 SELECT resource_id
406 FROM jtf_rs_resource_extns
407 WHERE resource_number = p_resource_number;
408
409 BEGIN
410
411
412 SAVEPOINT delete_resource_member_pub;
413
414 x_return_status := fnd_api.g_ret_sts_success;
415
416 -- DBMS_OUTPUT.put_line(' Started Delete Resource Member Pub ');
417
418
419 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
420
421 RAISE fnd_api.g_exc_unexpected_error;
422
423 END IF;
424
425
426 IF fnd_api.to_boolean(p_init_msg_list) THEN
427
428 fnd_msg_pub.initialize;
429
430 END IF;
431
432
433 /* Validate the Resource Group. */
434
435 BEGIN
436
437 jtf_resource_utl.validate_resource_group(
438 p_group_id => l_group_id,
439 p_group_number => l_group_number,
440 x_return_status => x_return_status,
441 x_group_id => l_group_id_out
442 );
443 -- added for NOCOPY
444 l_group_id := l_group_id_out;
445
446 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
447
448 x_return_status := fnd_api.g_ret_sts_unexp_error;
449
450 RAISE fnd_api.g_exc_unexpected_error;
451
452 END IF;
453
454 END; /* End of Resource Group Validation */
455
456
457
458 /* Validate the Resource Number. */
459
460 BEGIN
461
462 IF p_resource_id IS NULL AND p_resource_number is NULL THEN
463
464
465 fnd_message.set_name('JTF', 'JTF_RS_RESOURCE_NULL');
466 fnd_msg_pub.add;
467
468 x_return_status := fnd_api.g_ret_sts_unexp_error;
469 RAISE fnd_api.g_exc_unexpected_error;
470
471 END IF;
472
473 IF p_resource_id IS NOT NULL THEN
474 OPEN c_resource_id;
475
476 FETCH c_resource_id INTO l_resource_id;
477
478 IF c_resource_id%NOTFOUND THEN
479
480 -- dbms_output.put_line('Invalid or Inactive Resource');
481
482 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE');
483 fnd_message.set_token('P_RESOURCE_ID', p_resource_id);
484 fnd_msg_pub.add;
485
486 x_return_status := fnd_api.g_ret_sts_unexp_error;
487 RAISE fnd_api.g_exc_unexpected_error;
488
489 END IF;
490
491 CLOSE c_resource_id;
492
493 ELSIF p_resource_number IS NOT NULL THEN
494
495 OPEN c_resource_number;
496
497 FETCH c_resource_number INTO l_resource_id;
498
499 IF c_resource_number%NOTFOUND THEN
500
501
502 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE_NUMBER');
503 fnd_message.set_token('P_RESOURCE_NUMBER', p_resource_number);
504 fnd_msg_pub.add;
505
506 x_return_status := fnd_api.g_ret_sts_unexp_error;
507 RAISE fnd_api.g_exc_unexpected_error;
508
509 END IF;
510
511 CLOSE c_resource_number;
512
513 END IF; /* End of Resource Number Validation */
514
515 END;
516
517 /* Call the private procedure for delete */
518
519 jtf_rs_group_members_pvt.delete_resource_group_members
520 (P_API_VERSION => 1,
521 P_INIT_MSG_LIST => fnd_api.g_false,
522 P_COMMIT => fnd_api.g_false,
523 P_GROUP_ID => l_group_id,
524 P_RESOURCE_ID => l_resource_id,
525 P_OBJECT_VERSION_NUM => p_object_version_num,
526 X_RETURN_STATUS => x_return_status,
527 X_MSG_COUNT => x_msg_count,
528 X_MSG_DATA => x_msg_data
529 );
530
531
532 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
533
534 -- dbms_output.put_line('Failed status from call to private procedure');
535
536 RAISE fnd_api.g_exc_unexpected_error;
537
538 END IF;
539
540
541 IF fnd_api.to_boolean(p_commit) THEN
542
543 COMMIT WORK;
544
545 END IF;
546
547 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
548
549
550 EXCEPTION
551
552
553 WHEN fnd_api.g_exc_unexpected_error THEN
554
555 -- DBMS_OUTPUT.put_line (' ========================================== ');
556
557 -- DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error ======= ======== ');
558
559 ROLLBACK TO delete_resource_member_pub;
560
561 x_return_status := fnd_api.g_ret_sts_unexp_error;
562
563 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
564
565
566 WHEN OTHERS THEN
567
568 -- DBMS_OUTPUT.put_line (' ========================================== ');
569
570 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Delete Group Member Pub ============= ');
571
572 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
573
574 ROLLBACK TO delete_resource_member_pub;
575 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
576 fnd_message.set_token('P_SQLCODE',SQLCODE);
577 fnd_message.set_token('P_SQLERRM',SQLERRM);
578 fnd_message.set_token('P_API_NAME',l_api_name);
579 FND_MSG_PUB.add;
580
581 x_return_status := fnd_api.g_ret_sts_unexp_error;
582
583 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
584
585
586 END delete_resource_group_members;
587
588
589
590 END jtf_rs_group_members_pub;