DBA Data[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;