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