[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_GROUP_USAGES_PVT
Source
1 PACKAGE BODY jtf_rs_group_usages_pvt AS
2 /* $Header: jtfrsvab.pls 120.0 2005/05/11 08:22:52 appldev ship $ */
3
4 /*****************************************************************************************
5 This private package body defines the procedures for managing resource group usages,
6 like create and delete resource group usages.
7 Its main procedures are as following:
8 Create Resource Group Usage
9 Delete Resource Group Usage
10 These procedures does the business validations and then Calls the corresponding
11 table handlers to do actual inserts and deletes into tables.
12 ******************************************************************************************/
13
14
15 /* Package variables. */
16
17 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_GROUP_USAGES_PVT';
18
19
20 /* Procedure to create the resource group usage
21 based on input values passed by calling routines. */
22
23 PROCEDURE create_group_usage
24 (P_API_VERSION IN NUMBER,
25 P_INIT_MSG_LIST IN VARCHAR2,
26 P_COMMIT IN VARCHAR2,
27 P_GROUP_ID IN JTF_RS_GROUP_USAGES.GROUP_ID%TYPE,
28 P_USAGE IN JTF_RS_GROUP_USAGES.USAGE%TYPE,
29 P_ATTRIBUTE1 IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE1%TYPE,
30 P_ATTRIBUTE2 IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE2%TYPE,
31 P_ATTRIBUTE3 IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE3%TYPE,
32 P_ATTRIBUTE4 IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE4%TYPE,
33 P_ATTRIBUTE5 IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE5%TYPE,
34 P_ATTRIBUTE6 IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE6%TYPE,
35 P_ATTRIBUTE7 IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE7%TYPE,
36 P_ATTRIBUTE8 IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE8%TYPE,
37 P_ATTRIBUTE9 IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE9%TYPE,
38 P_ATTRIBUTE10 IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE10%TYPE,
39 P_ATTRIBUTE11 IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE11%TYPE,
40 P_ATTRIBUTE12 IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE12%TYPE,
41 P_ATTRIBUTE13 IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE13%TYPE,
42 P_ATTRIBUTE14 IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE14%TYPE,
43 P_ATTRIBUTE15 IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE15%TYPE,
44 P_ATTRIBUTE_CATEGORY IN JTF_RS_GROUP_MEMBERS.ATTRIBUTE_CATEGORY%TYPE,
45 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
46 X_MSG_COUNT OUT NOCOPY NUMBER,
47 X_MSG_DATA OUT NOCOPY VARCHAR2,
48 X_GROUP_USAGE_ID OUT NOCOPY JTF_RS_GROUP_USAGES.GROUP_USAGE_ID%TYPE
49 ) IS
50
51 l_api_version CONSTANT NUMBER := 1.0;
52 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_GROUP_USAGE';
53 l_group_id jtf_rs_group_usages.group_id%TYPE := p_group_id;
54 l_usage jtf_rs_group_usages.usage%TYPE := upper(p_usage);
55
56 l_rowid ROWID;
57 l_group_usage_id jtf_rs_group_usages.group_usage_id%TYPE;
58 l_check_char VARCHAR2(1);
59 l_check_count NUMBER;
60 l_bind_data_id NUMBER;
61
62
63 CURSOR c_exclusive_group_check(
64 l_group_id IN NUMBER,
65 l_usage IN VARCHAR)
66 IS
67 SELECT 'Y'
68 FROM jtf_rs_groups_vl G1,
69 jtf_rs_groups_vl G2,
70 jtf_rs_group_members GM1,
71 jtf_rs_group_members GM2,
72 jtf_rs_group_usages GU2,
73 jtf_rs_role_relations RR1,
74 jtf_rs_role_relations RR2
75 WHERE G1.group_id = GM1.group_id
76 AND G2.group_id = GM2.group_id
77 AND nvl(GM1.delete_flag, 'N') <> 'Y'
78 AND nvl(GM2.delete_flag, 'N') <> 'Y'
79 AND GM1.resource_id = GM2.resource_id
80 AND GM1.group_member_id = RR1.role_resource_id
81 AND GM2.group_member_id = RR2.role_resource_id
82 AND RR1.role_resource_type = 'RS_GROUP_MEMBER'
83 AND RR2.role_resource_type = 'RS_GROUP_MEMBER'
84 AND nvl(RR1.delete_flag, 'N') <> 'Y'
85 AND nvl(RR2.delete_flag, 'N') <> 'Y'
86 /*AND NOT (((RR2.end_date_active < RR1.start_date_active OR
87 RR2.start_date_active > RR1.end_date_active) AND
88 RR1.end_date_active IS NOT NULL)
89 OR (RR2.end_date_active < RR1.start_date_active AND
90 RR1.end_date_active IS NULL)) */
91 AND not (((nvl(RR2.end_date_active,RR1.start_date_active + 1) < RR1.start_date_active OR
92 RR2.start_date_active > RR1.end_date_active) AND
93 RR1.end_date_active IS NOT NULL)
94 OR ( nvl(RR2.end_date_active,RR1.start_date_active + 1) < RR1.start_date_active AND
95 RR1.end_date_active IS NULL ))
96 AND G2.exclusive_flag = 'Y'
97 AND GU2.group_id = G2.group_id
98 AND GU2.usage = l_usage
99 AND G1.group_id <> G2.group_id
100 AND G1.group_id = l_group_id
101 AND nvl(G1.exclusive_flag,'N') = 'Y';
102
103
104 CURSOR c_jtf_rs_group_usages( l_rowid IN ROWID ) IS
105 SELECT 'Y'
106 FROM jtf_rs_group_usages
107 WHERE ROWID = l_rowid;
108
109
110 BEGIN
111
112
113 SAVEPOINT create_rs_group_usage_pvt;
114
115 x_return_status := fnd_api.g_ret_sts_success;
116
117 -- DBMS_OUTPUT.put_line('Started Create Resource Group Usage Pvt ');
118
119
120 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
121
122 RAISE fnd_api.g_exc_unexpected_error;
123
124 END IF;
125
126
127 IF fnd_api.to_boolean(p_init_msg_list) THEN
128
129 fnd_msg_pub.initialize;
130
131 END IF;
132
133
134 /* Make the pre processing call to the user hooks */
135
136 /* Pre Call to the Customer Type User Hook */
137
138 IF jtf_resource_utl.ok_to_execute(
139 'JTF_RS_GROUP_USAGES_PVT',
140 'CREATE_GROUP_USAGE',
141 'B',
142 'C')
143 THEN
144 IF jtf_usr_hks.ok_to_execute(
145 'JTF_RS_GROUP_USAGES_PVT',
146 'CREATE_GROUP_USAGE',
147 'B',
148 'C')
149 THEN
150
151 jtf_rs_group_usage_cuhk.create_group_usage_pre(
152 p_group_id => l_group_id,
153 p_usage => l_usage,
154 x_return_status => x_return_status);
155
156 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
157
158 -- dbms_output.put_line('Returned Error status from the Pre Customer User Hook');
159
160 x_return_status := fnd_api.g_ret_sts_unexp_error;
161
162 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
163 fnd_msg_pub.add;
164
165 RAISE fnd_api.g_exc_unexpected_error;
166
167 END IF;
168
169 END IF;
170 END IF;
171
172
173 /* Pre Call to the Vertical Type User Hook */
174
175 IF jtf_resource_utl.ok_to_execute(
176 'JTF_RS_GROUP_USAGES_PVT',
177 'CREATE_GROUP_USAGE',
178 'B',
179 'V')
180 THEN
181 IF jtf_usr_hks.ok_to_execute(
182 'JTF_RS_GROUP_USAGES_PVT',
183 'CREATE_GROUP_USAGE',
184 'B',
185 'V')
186 THEN
187
188 jtf_rs_group_usage_vuhk.create_group_usage_pre(
189 p_group_id => l_group_id,
190 p_usage => l_usage,
191 x_return_status => x_return_status);
192
193 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
194
195 -- dbms_output.put_line('Returned Error status from the Pre Vertical User Hook');
196
197 x_return_status := fnd_api.g_ret_sts_unexp_error;
198
199 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
200 fnd_msg_pub.add;
201
202 RAISE fnd_api.g_exc_unexpected_error;
203
204 END IF;
205
206 END IF;
207 END IF;
208
209
210 /* Pre Call to the Internal Type User Hook */
211
212 IF jtf_resource_utl.ok_to_execute(
213 'JTF_RS_GROUP_USAGES_PVT',
214 'CREATE_GROUP_USAGE',
215 'B',
216 'I')
217 THEN
218 IF jtf_usr_hks.ok_to_execute(
219 'JTF_RS_GROUP_USAGES_PVT',
220 'CREATE_GROUP_USAGE',
221 'B',
222 'I')
223 THEN
224
225 jtf_rs_group_usage_iuhk.create_group_usage_pre(
226 p_group_id => l_group_id,
227 p_usage => l_usage,
228 x_return_status => x_return_status);
229
230 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
231
232 x_return_status := fnd_api.g_ret_sts_unexp_error;
233
234 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
235 fnd_msg_pub.add;
236
237 RAISE fnd_api.g_exc_unexpected_error;
238
239 END IF;
240
241 END IF;
242 END IF;
243
244
245 /* Validate that the usage is not already assigned */
246
247 l_check_count := 0;
248
249 SELECT count(*)
250 INTO l_check_count
251 FROM jtf_rs_group_usages
252 WHERE group_id = l_group_id
253 AND usage = l_usage;
254
255 IF l_check_count > 0 THEN
256
257 -- dbms_output.put_line('Usage already assigned to the Group');
258
259 x_return_status := fnd_api.g_ret_sts_error;
260
261 fnd_message.set_name('JTF', 'JTF_RS_USAGE_EXISTS');
262 fnd_msg_pub.add;
263
264 RAISE fnd_api.g_exc_unexpected_error;
265
266 END IF;
267
268
269 /* If Group Exclusive Flag is checked then only those resources can be
270 assigned to the group, who are not assigned to any other Exclusive group
271 having the same USAGE value in that same time period. Validate that the
272 new group usage support the above condition for all the group members. */
273
274 OPEN c_exclusive_group_check(l_group_id, l_usage);
275
276 FETCH c_exclusive_group_check INTO l_check_char;
277
278
279 IF c_exclusive_group_check%FOUND THEN
280
281 -- dbms_output.put_line('Group usage cannot be created as one of the member
282 -- dates overlap with another record for the same resource assigned to
283 -- another exclusive group with the same usage in the same time period');
284
285 IF c_exclusive_group_check%ISOPEN THEN
286
287 CLOSE c_exclusive_group_check;
288
289 END IF;
290
291 x_return_status := fnd_api.g_ret_sts_unexp_error;
292
293 fnd_message.set_name('JTF', 'JTF_RS_EXCLUSIVE_GROUP_ERR');
294 fnd_msg_pub.add;
295
296 RAISE fnd_api.g_exc_unexpected_error;
297
298 END IF;
299
300
301 /* Close the cursors */
302
303 IF c_exclusive_group_check%ISOPEN THEN
304
305 CLOSE c_exclusive_group_check;
306
307 END IF;
308
309
310
311 /* Get the next value of the Group_usage_id from the sequence. */
312
313 SELECT jtf_rs_group_usages_s.nextval
314 INTO l_group_usage_id
315 FROM dual;
316
317
318 /* Insert the row into the table by calling the table handler. */
319
320 jtf_rs_group_usages_pkg.insert_row(
321 x_rowid => l_rowid,
322 x_group_usage_id => l_group_usage_id,
323 x_group_id => l_group_id,
324 x_usage => l_usage,
325 x_attribute1 => p_attribute1,
326 x_attribute2 => p_attribute2,
327 x_attribute3 => p_attribute3,
328 x_attribute4 => p_attribute4,
329 x_attribute5 => p_attribute5,
330 x_attribute6 => p_attribute6,
331 x_attribute7 => p_attribute7,
332 x_attribute8 => p_attribute8,
333 x_attribute9 => p_attribute9,
334 x_attribute10 => p_attribute10,
335 x_attribute11 => p_attribute11,
336 x_attribute12 => p_attribute12,
337 x_attribute13 => p_attribute13,
338 x_attribute14 => p_attribute14,
339 x_attribute15 => p_attribute15,
340 x_attribute_category => p_attribute_category,
341 x_creation_date => SYSDATE,
342 x_created_by => jtf_resource_utl.created_by,
343 x_last_update_date => SYSDATE,
344 x_last_updated_by => jtf_resource_utl.updated_by,
345 x_last_update_login => jtf_resource_utl.login_id
346 );
347
348
349 -- dbms_output.put_line('Inserted Row');
350
351 OPEN c_jtf_rs_group_usages(l_rowid);
352
353 FETCH c_jtf_rs_group_usages INTO l_check_char;
354
355
356 IF c_jtf_rs_group_usages%NOTFOUND THEN
357
358 -- dbms_output.put_line('Error in Table Handler');
359
360 x_return_status := fnd_api.g_ret_sts_unexp_error;
361
362 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
363 fnd_msg_pub.add;
364
365 IF c_jtf_rs_group_usages%ISOPEN THEN
366
367 CLOSE c_jtf_rs_group_usages;
368
369 END IF;
370
371 RAISE fnd_api.g_exc_unexpected_error;
372
373 ELSE
374
375 -- dbms_output.put_line('Group Usage Successfully Created');
376
377 x_group_usage_id := l_group_usage_id;
378
379 END IF;
380
381
382 /* Close the cursors */
383
384 IF c_jtf_rs_group_usages%ISOPEN THEN
385
386 CLOSE c_jtf_rs_group_usages;
387
388 END IF;
389
390
391
392 /* Make the post processing call to the user hooks */
393
394 /* Post Call to the Customer Type User Hook */
395
396 IF jtf_resource_utl.ok_to_execute(
397 'JTF_RS_GROUP_USAGES_PVT',
398 'CREATE_GROUP_USAGE',
399 'A',
400 'C')
401 THEN
402 IF jtf_usr_hks.ok_to_execute(
403 'JTF_RS_GROUP_USAGES_PVT',
404 'CREATE_GROUP_USAGE',
405 'A',
406 'C')
407 THEN
408
409 jtf_rs_group_usage_cuhk.create_group_usage_post(
410 p_group_usage_id => l_group_usage_id,
411 p_group_id => l_group_id,
412 p_usage => l_usage,
413 x_return_status => x_return_status);
414
415 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
416
417 -- dbms_output.put_line('Returned Error status from the Post Customer User Hook');
418
419 x_return_status := fnd_api.g_ret_sts_unexp_error;
420
421 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
422 fnd_msg_pub.add;
423
424 RAISE fnd_api.g_exc_unexpected_error;
425
426 END IF;
427
428 END IF;
429 END IF;
430
431
432 /* Post Call to the Vertical Type User Hook */
433
434 IF jtf_resource_utl.ok_to_execute(
435 'JTF_RS_GROUP_USAGES_PVT',
436 'CREATE_GROUP_USAGE',
437 'A',
438 'V')
439 THEN
440 IF jtf_usr_hks.ok_to_execute(
441 'JTF_RS_GROUP_USAGES_PVT',
442 'CREATE_GROUP_USAGE',
443 'A',
444 'V')
445 THEN
446
447 jtf_rs_group_usage_vuhk.create_group_usage_post(
448 p_group_usage_id => l_group_usage_id,
449 p_group_id => l_group_id,
450 p_usage => l_usage,
451 x_return_status => x_return_status);
452
453 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
454
455 -- dbms_output.put_line('Returned Error status from the Post Vertical User Hook');
456
457 x_return_status := fnd_api.g_ret_sts_unexp_error;
458
459 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
460 fnd_msg_pub.add;
461
465
462 RAISE fnd_api.g_exc_unexpected_error;
463
464 END IF;
466 END IF;
467 END IF;
468
469
470 /* Post Call to the Internal Type User Hook */
471
472 IF jtf_resource_utl.ok_to_execute(
473 'JTF_RS_GROUP_USAGES_PVT',
474 'CREATE_GROUP_USAGE',
475 'A',
476 'I')
477 THEN
478 IF jtf_usr_hks.ok_to_execute(
479 'JTF_RS_GROUP_USAGES_PVT',
480 'CREATE_GROUP_USAGE',
481 'A',
482 'I')
483 THEN
484
485 jtf_rs_group_usage_iuhk.create_group_usage_post(
486 p_group_usage_id => l_group_usage_id,
487 p_group_id => l_group_id,
488 p_usage => l_usage,
489 x_return_status => x_return_status);
490
491 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
492
493 x_return_status := fnd_api.g_ret_sts_unexp_error;
494
495 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
496 fnd_msg_pub.add;
497
498 RAISE fnd_api.g_exc_unexpected_error;
499
500 END IF;
501
502 END IF;
503 END IF;
504
505
506 /* Standard call for Message Generation */
507
508 IF jtf_resource_utl.ok_to_execute(
509 'JTF_RS_GROUP_USAGES_PVT',
510 'CREATE_GROUP_USAGE',
511 'M',
512 'M')
513 THEN
514 IF jtf_usr_hks.ok_to_execute(
515 'JTF_RS_GROUP_USAGES_PVT',
516 'CREATE_GROUP_USAGE',
517 'M',
518 'M')
519 THEN
520
521 IF (jtf_rs_group_usage_cuhk.ok_to_generate_msg(
522 p_group_usage_id => l_group_usage_id,
523 x_return_status => x_return_status) )
524 THEN
525
526 /* Get the bind data id for the Business Object Instance */
527
528 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
529
530
531 /* Set bind values for the bind variables in the Business Object SQL */
532
533 jtf_usr_hks.load_bind_data(l_bind_data_id, 'group_usage_id', l_group_usage_id, 'S', 'N');
534
535
536 /* Call the message generation API */
537
538 jtf_usr_hks.generate_message(
539 p_prod_code => 'JTF',
540 p_bus_obj_code => 'RS_GUSG',
541 p_action_code => 'I',
542 p_bind_data_id => l_bind_data_id,
543 x_return_code => x_return_status);
544
545
546 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
547
548 -- dbms_output.put_line('Returned Error status from the Message Generation API');
549
550 x_return_status := fnd_api.g_ret_sts_unexp_error;
551
552 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
553 fnd_msg_pub.add;
554
555 RAISE fnd_api.g_exc_unexpected_error;
556
557 END IF;
558
559 END IF;
560
561 END IF;
562 END IF;
563
564
565 IF fnd_api.to_boolean(p_commit) THEN
566
567 COMMIT WORK;
568
569 END IF;
570
571 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
572
573
574 EXCEPTION
575
576
577 WHEN fnd_api.g_exc_unexpected_error THEN
578
579 -- DBMS_OUTPUT.put_line (' ========================================== ');
580
581 -- DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error ======= ======== ');
582
583 ROLLBACK TO create_rs_group_usage_pvt;
584
585 x_return_status := fnd_api.g_ret_sts_unexp_error;
586
587 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
588
589
590 WHEN OTHERS THEN
591
592 -- DBMS_OUTPUT.put_line (' ========================================== ');
593
594 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Create Group Usage Pvt ========= ');
595
596 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
597
598 ROLLBACK TO create_rs_group_usage_pvt;
599
600 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
601 fnd_message.set_token('P_SQLCODE',SQLCODE);
602 fnd_message.set_token('P_SQLERRM',SQLERRM);
603 fnd_message.set_token('P_API_NAME',l_api_name);
604 FND_MSG_PUB.add;
605
606 x_return_status := fnd_api.g_ret_sts_unexp_error;
607
608 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
609
610
611 END create_group_usage;
612
613
614
615 /* Procedure to delete the resource group usage
616 based on input values passed by calling routines. */
617
618 PROCEDURE delete_group_usage
619 (P_API_VERSION IN NUMBER,
620 P_INIT_MSG_LIST IN VARCHAR2,
621 P_COMMIT IN VARCHAR2,
622 P_GROUP_ID IN JTF_RS_GROUP_USAGES.GROUP_ID%TYPE,
623 P_USAGE IN JTF_RS_GROUP_USAGES.USAGE%TYPE,
624 P_OBJECT_VERSION_NUM IN JTF_RS_GROUP_USAGES.OBJECT_VERSION_NUMBER%TYPE,
625 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
626 X_MSG_COUNT OUT NOCOPY NUMBER,
630 l_api_version CONSTANT NUMBER := 1.0;
627 X_MSG_DATA OUT NOCOPY VARCHAR2
628 ) IS
629
631 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GROUP_USAGE';
632 l_group_id jtf_rs_group_usages.group_id%TYPE := p_group_id;
633 l_usage jtf_rs_group_usages.usage%TYPE := upper(p_usage);
634
635 l_check_char VARCHAR2(1);
636 l_group_usage_id jtf_rs_group_usages.group_usage_id%TYPE;
637 l_bind_data_id NUMBER;
638
639
640 CURSOR c_group_usage_id(
641 l_group_id IN NUMBER,
642 l_usage IN VARCHAR2 )
643 IS
644 SELECT group_usage_id
645 FROM jtf_rs_group_usages
646 WHERE group_id = l_group_id
647 AND usage = l_usage;
648
649 -- Added the below cursor to get the meaning of the usage code passed.
650
651 CURSOR c_usage_name(
652 c_usage_code IN VARCHAR2 )
653 IS
654 select meaning
655 from fnd_lookups
656 where lookup_type = 'JTF_RS_USAGE'
657 and lookup_code = c_usage_code;
658
659 l_usage_name fnd_lookups.meaning%TYPE;
660
661 BEGIN
662
663
664 SAVEPOINT delete_group_usage_pvt;
665
666 x_return_status := fnd_api.g_ret_sts_success;
667
668 -- DBMS_OUTPUT.put_line(' Started Delete Group Usage Pvt ');
669
670
671 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
672
673 RAISE fnd_api.g_exc_unexpected_error;
674
675 END IF;
676
677
678 IF fnd_api.to_boolean(p_init_msg_list) THEN
679
680 fnd_msg_pub.initialize;
681
682 END IF;
683
684
685 /* Make the pre processing call to the user hooks */
686
687 /* Pre Call to the Customer Type User Hook */
688
689 IF jtf_resource_utl.ok_to_execute(
690 'JTF_RS_GROUP_USAGES_PVT',
691 'DELETE_GROUP_USAGE',
692 'B',
693 'C')
694 THEN
695 IF jtf_usr_hks.ok_to_execute(
696 'JTF_RS_GROUP_USAGES_PVT',
697 'DELETE_GROUP_USAGE',
698 'B',
699 'C')
700 THEN
701
702 jtf_rs_group_usage_cuhk.delete_group_usage_pre(
703 p_group_id => l_group_id,
704 p_usage => l_usage,
705 x_return_status => x_return_status);
706
707 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
708
709 -- dbms_output.put_line('Returned Error status from the Pre Customer User Hook');
710
711 x_return_status := fnd_api.g_ret_sts_unexp_error;
712
713 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
714 fnd_msg_pub.add;
715
716 RAISE fnd_api.g_exc_unexpected_error;
717
718 END IF;
719
720 END IF;
721 END IF;
722
723
724 /* Pre Call to the Vertical Type User Hook */
725
726 IF jtf_resource_utl.ok_to_execute(
727 'JTF_RS_GROUP_USAGES_PVT',
728 'DELETE_GROUP_USAGE',
729 'B',
730 'V')
731 THEN
732 IF jtf_usr_hks.ok_to_execute(
733 'JTF_RS_GROUP_USAGES_PVT',
734 'DELETE_GROUP_USAGE',
735 'B',
736 'V')
737 THEN
738
739 jtf_rs_group_usage_vuhk.delete_group_usage_pre(
740 p_group_id => l_group_id,
741 p_usage => l_usage,
742 x_return_status => x_return_status);
743
744 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
745
746 -- dbms_output.put_line('Returned Error status from the Pre Vertical User Hook');
747
748 x_return_status := fnd_api.g_ret_sts_unexp_error;
749
750 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
751 fnd_msg_pub.add;
752
753 RAISE fnd_api.g_exc_unexpected_error;
754
755 END IF;
756
757 END IF;
758 END IF;
759
760
761 /* Pre Call to the Internal Type User Hook */
762
763 IF jtf_resource_utl.ok_to_execute(
764 'JTF_RS_GROUP_USAGES_PVT',
765 'DELETE_GROUP_USAGE',
766 'B',
767 'I')
768 THEN
769 IF jtf_usr_hks.ok_to_execute(
770 'JTF_RS_GROUP_USAGES_PVT',
771 'DELETE_GROUP_USAGE',
772 'B',
773 'I')
774 THEN
775
776 jtf_rs_group_usage_iuhk.delete_group_usage_pre(
777 p_group_id => l_group_id,
778 p_usage => l_usage,
779 x_return_status => x_return_status);
780
781 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
782
783 x_return_status := fnd_api.g_ret_sts_unexp_error;
784
785 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
786 fnd_msg_pub.add;
787
788 RAISE fnd_api.g_exc_unexpected_error;
789
790 END IF;
791
792 END IF;
793 END IF;
794
795
796 /* Validate that the specified usage exists for the specified group */
797
801
798 OPEN c_group_usage_id(l_group_id, l_usage);
799
800 FETCH c_group_usage_id INTO l_group_usage_id;
802
803 IF c_group_usage_id%NOTFOUND THEN
804
805 -- dbms_output.put_line('Usage is not setup for the Group');
806
807 IF c_group_usage_id%ISOPEN THEN
808
809 CLOSE c_group_usage_id;
810
811 END IF;
812
813 fnd_message.set_name('JTF', 'JTF_RS_INVALID_GROUP_USAGE');
814 fnd_message.set_token('P_USAGE', l_usage);
815 fnd_message.set_token('P_GROUP_ID', l_group_id);
816 fnd_msg_pub.add;
817
818 x_return_status := fnd_api.g_ret_sts_unexp_error;
819
820 RAISE fnd_api.g_exc_unexpected_error;
821
822 END IF;
823
824
825 /* Close the cursor */
826
827 IF c_group_usage_id%ISOPEN THEN
828
829 CLOSE c_group_usage_id;
830
831 END IF;
832
833
834 -- Added the below if condition to validate the group usage is a seeded usage or not.
835 -- If the group usage is seeded, the user will not be able to delete that usage.
836 -- seeded group usages will have a group_usage_id < 0.
837 -- Fix for Enhancement: 4070272
838
839 IF (l_group_usage_id < 0) THEN
840
841 OPEN c_usage_name(l_usage);
842 FETCH c_usage_name INTO l_usage_name;
843 CLOSE c_usage_name;
844
845 fnd_message.set_name('JTF', 'JTF_RS_GRP_USAGE_DEL_ERROR');
846 fnd_message.set_token('P_USAGE', l_usage_name);
847 fnd_msg_pub.add;
848
849 x_return_status := fnd_api.g_ret_sts_error;
850 RAISE fnd_api.g_exc_error;
851
852 END IF;
853
854 /* Call the lock row procedure to ensure that the object version number
855 is still valid. */
856
857 BEGIN
858
859 jtf_rs_group_usages_pkg.lock_row(
860 x_group_usage_id => l_group_usage_id,
861 x_object_version_number => p_object_version_num
862 );
863
864 EXCEPTION
865
866 WHEN OTHERS THEN
867
868 -- dbms_output.put_line('Error in Locking the Row');
869
870 x_return_status := fnd_api.g_ret_sts_unexp_error;
871
872 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
873 fnd_msg_pub.add;
874
875 RAISE fnd_api.g_exc_unexpected_error;
876
877 END;
878
879
880
881 /* Call the private procedure for physical delete */
882
883 BEGIN
884
885 /* Delete the row into the table by calling the table handler. */
886
887 jtf_rs_group_usages_pkg.delete_row(
888 x_group_usage_id => l_group_usage_id
889 );
890
891 EXCEPTION
892
893 WHEN NO_DATA_FOUND THEN
894
895 -- dbms_output.put_line('Error in Table Handler');
896
897 x_return_status := fnd_api.g_ret_sts_unexp_error;
898
899 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
900 fnd_msg_pub.add;
901
902 RAISE fnd_api.g_exc_unexpected_error;
903
904 END;
905
906
907
908 /* Make the post processing call to the user hooks */
909
910 /* Post Call to the Customer Type User Hook */
911
912 IF jtf_resource_utl.ok_to_execute(
913 'JTF_RS_GROUP_USAGES_PVT',
914 'DELETE_GROUP_USAGE',
915 'A',
916 'C')
917 THEN
918 IF jtf_usr_hks.ok_to_execute(
919 'JTF_RS_GROUP_USAGES_PVT',
920 'DELETE_GROUP_USAGE',
921 'A',
922 'C')
923 THEN
924
925 jtf_rs_group_usage_cuhk.delete_group_usage_post(
926 p_group_id => l_group_id,
927 p_usage => l_usage,
928 x_return_status => x_return_status);
929
930 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
931
932 -- dbms_output.put_line('Returned Error status from the Post Customer User Hook');
933
934 x_return_status := fnd_api.g_ret_sts_unexp_error;
935
936 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
937 fnd_msg_pub.add;
938
939 RAISE fnd_api.g_exc_unexpected_error;
940
941 END IF;
942
943 END IF;
944 END IF;
945
946
947 /* Post Call to the Vertical Type User Hook */
948
949 IF jtf_resource_utl.ok_to_execute(
950 'JTF_RS_GROUP_USAGES_PVT',
951 'DELETE_GROUP_USAGE',
952 'A',
953 'V')
954 THEN
955 IF jtf_usr_hks.ok_to_execute(
956 'JTF_RS_GROUP_USAGES_PVT',
957 'DELETE_GROUP_USAGE',
958 'A',
959 'V')
960 THEN
961
962 jtf_rs_group_usage_vuhk.delete_group_usage_post(
963 p_group_id => l_group_id,
964 p_usage => l_usage,
965 x_return_status => x_return_status);
966
967 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
968
969 -- dbms_output.put_line('Returned Error status from the Post Vertical User Hook');
970
971 x_return_status := fnd_api.g_ret_sts_unexp_error;
972
973 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
974 fnd_msg_pub.add;
975
976 RAISE fnd_api.g_exc_unexpected_error;
977
978 END IF;
979
980 END IF;
981 END IF;
982
983
984 /* Post Call to the Internal Type User Hook */
985
986 IF jtf_resource_utl.ok_to_execute(
987 'JTF_RS_GROUP_USAGES_PVT',
988 'DELETE_GROUP_USAGE',
989 'A',
990 'I')
991 THEN
992 IF jtf_usr_hks.ok_to_execute(
993 'JTF_RS_GROUP_USAGES_PVT',
994 'DELETE_GROUP_USAGE',
995 'A',
996 'I')
997 THEN
998
999 jtf_rs_group_usage_iuhk.delete_group_usage_post(
1000 p_group_id => l_group_id,
1001 p_usage => l_usage,
1002 x_return_status => x_return_status);
1003
1004 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1005
1006 x_return_status := fnd_api.g_ret_sts_unexp_error;
1007
1008 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1009 fnd_msg_pub.add;
1010
1011 RAISE fnd_api.g_exc_unexpected_error;
1012
1013 END IF;
1014
1015 END IF;
1016 END IF;
1017
1018
1019 /* Standard call for Message Generation */
1020
1021 IF jtf_resource_utl.ok_to_execute(
1022 'JTF_RS_GROUP_USAGES_PVT',
1023 'DELETE_GROUP_USAGE',
1024 'M',
1025 'M')
1026 THEN
1027 IF jtf_usr_hks.ok_to_execute(
1028 'JTF_RS_GROUP_USAGES_PVT',
1029 'DELETE_GROUP_USAGE',
1030 'M',
1031 'M')
1032 THEN
1033
1034 IF (jtf_rs_group_usage_cuhk.ok_to_generate_msg(
1035 p_group_usage_id => l_group_usage_id,
1036 x_return_status => x_return_status) )
1037 THEN
1038
1039 /* Get the bind data id for the Business Object Instance */
1040
1041 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1042
1043
1044 /* Set bind values for the bind variables in the Business Object SQL */
1045
1046 jtf_usr_hks.load_bind_data(l_bind_data_id, 'group_usage_id', l_group_usage_id, 'S', 'N');
1047
1048
1049 /* Call the message generation API */
1050
1051 jtf_usr_hks.generate_message(
1052 p_prod_code => 'RS',
1053 p_bus_obj_code => 'GUSG',
1054 p_action_code => 'D',
1055 p_bind_data_id => l_bind_data_id,
1056 x_return_code => x_return_status);
1057
1058
1059 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1060
1061 -- dbms_output.put_line('Returned Error status from the Message Generation API');
1062
1063 x_return_status := fnd_api.g_ret_sts_unexp_error;
1064
1065 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1066 fnd_msg_pub.add;
1067
1068 RAISE fnd_api.g_exc_unexpected_error;
1069
1070 END IF;
1071
1072 END IF;
1073
1074 END IF;
1075 END IF;
1076
1077
1078 IF fnd_api.to_boolean(p_commit) THEN
1079
1080 COMMIT WORK;
1081
1082 END IF;
1083
1084 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1085
1086
1087 EXCEPTION
1088
1089
1090 WHEN fnd_api.g_exc_error THEN
1091
1092 ROLLBACK TO delete_group_usage_pvt;
1093 x_return_status := fnd_api.g_ret_sts_error;
1094 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1095
1096 WHEN fnd_api.g_exc_unexpected_error THEN
1097
1098 -- DBMS_OUTPUT.put_line (' ========================================== ');
1099
1100 -- DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error ======= ======== ');
1101
1102 ROLLBACK TO delete_group_usage_pvt;
1103
1104 x_return_status := fnd_api.g_ret_sts_unexp_error;
1105
1106 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1107
1108
1109 WHEN OTHERS THEN
1110
1111 -- DBMS_OUTPUT.put_line (' ========================================== ');
1112
1113 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Delete Group Usage Pvt ============= ');
1114
1115 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
1116
1117 ROLLBACK TO delete_group_usage_pvt;
1118
1119 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1120 fnd_message.set_token('P_SQLCODE',SQLCODE);
1121 fnd_message.set_token('P_SQLERRM',SQLERRM);
1122 fnd_message.set_token('P_API_NAME',l_api_name);
1123 FND_MSG_PUB.add;
1124
1125 x_return_status := fnd_api.g_ret_sts_unexp_error;
1126
1127 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1128
1129
1130 END delete_group_usage;
1131
1132
1133 END jtf_rs_group_usages_pvt;