[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_TEAM_MEMBERS_PVT
Source
1 PACKAGE BODY jtf_rs_team_members_pvt AS
2 /* $Header: jtfrsveb.pls 120.0 2005/05/11 08:22:56 appldev ship $ */
3
4 /*****************************************************************************************
5 This private package body defines the procedures for managing resource team members,
6 like create and delete resource team members.
7 Its main procedures are as following:
8 Create Resource Team Members
9 Delete Resource Team Members
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 /* Package variables. */
15
16 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_TEAM_MEMBERS_PVT';
17
18
19 /* Procedure to create the resource team members
20 based on input values passed by calling routines. */
21
22 PROCEDURE create_resource_team_members
23 (P_API_VERSION IN NUMBER,
24 P_INIT_MSG_LIST IN VARCHAR2,
25 P_COMMIT IN VARCHAR2,
26 P_TEAM_ID IN JTF_RS_TEAM_MEMBERS.TEAM_ID%TYPE,
27 P_TEAM_RESOURCE_ID IN JTF_RS_TEAM_MEMBERS.TEAM_RESOURCE_ID%TYPE,
28 P_RESOURCE_TYPE IN JTF_RS_TEAM_MEMBERS.RESOURCE_TYPE%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_MEMBER_ID OUT NOCOPY JTF_RS_TEAM_MEMBERS.TEAM_MEMBER_ID%TYPE
49 ) IS
50
51 l_api_version CONSTANT NUMBER := 1.0;
52 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_TEAM_MEMBERS';
53 l_rowid ROWID;
54 l_team_id jtf_rs_team_members.team_id%TYPE;
55 l_team_resource_id jtf_rs_team_members.team_resource_id%TYPE;
56 l_resource_type jtf_rs_team_members.resource_type%TYPE;
57 l_team_member_id jtf_rs_team_members.team_member_id%TYPE;
58 l_person_id jtf_rs_team_members.person_id%TYPE;
59
60 l_check_char VARCHAR2(1);
61 l_check_count NUMBER;
62 l_bind_data_id NUMBER;
63 l_resource_id NUMBER;
64 l_group_id NUMBER;
65 l_return_status VARCHAR2(1);
66 l_msg_data VARCHAR2(2000);
67 l_msg_count NUMBER;
68
69
70 CURSOR c_jtf_rs_team_members( l_rowid IN ROWID ) IS
71 SELECT 'Y'
72 FROM jtf_rs_team_members
73 WHERE ROWID = l_rowid;
74
75
76 CURSOR c_employee_person_id( l_team_resource_id IN NUMBER ) IS
77 SELECT source_id
78 FROM jtf_rs_resource_extns
79 WHERE resource_id = l_team_resource_id
80 AND category = 'EMPLOYEE';
81
82
83 BEGIN
84
85 l_team_id := p_team_id;
86 l_team_resource_id := p_team_resource_id;
87 l_resource_type := upper(p_resource_type);
88
89 SAVEPOINT create_resource_member_pvt;
90
91 x_return_status := fnd_api.g_ret_sts_success;
92
93 -- DBMS_OUTPUT.put_line(' Started Create Resource Member Pvt ');
94
95
96 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
97
98 RAISE fnd_api.g_exc_unexpected_error;
99
100 END IF;
101
102
103 IF fnd_api.to_boolean(p_init_msg_list) THEN
104
105 fnd_msg_pub.initialize;
106
107 END IF;
108
109
110
111 /* Make the pre processing call to the user hooks */
112
113 /* Pre Call to the Customer Type User Hook */
114
115 IF jtf_resource_utl.ok_to_execute(
116 'JTF_RS_TEAM_MEMBERS_PVT',
117 'CREATE_RESOURCE_TEAM_MEMBERS',
118 'B',
119 'C')
120 THEN
121 IF jtf_usr_hks.ok_to_execute(
122 'JTF_RS_TEAM_MEMBERS_PVT',
123 'CREATE_RESOURCE_TEAM_MEMBERS',
124 'B',
125 'C')
126 THEN
127
128 jtf_rs_team_member_cuhk.create_team_members_pre(
129 p_team_id => l_team_id,
130 p_team_resource_id => l_team_resource_id,
131 p_resource_type => l_resource_type,
132 x_return_status => x_return_status);
133
134 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
135
136 -- dbms_output.put_line('Returned Error status from the Pre Customer User Hook');
137
138 x_return_status := fnd_api.g_ret_sts_unexp_error;
139
140 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
141 fnd_msg_pub.add;
142
143 RAISE fnd_api.g_exc_unexpected_error;
144
145 END IF;
146
147 END IF;
148 END IF;
149
150
151 /* Pre Call to the Vertical Type User Hook */
152
153 IF jtf_resource_utl.ok_to_execute(
154 'JTF_RS_TEAM_MEMBERS_PVT',
155 'CREATE_RESOURCE_TEAM_MEMBERS',
156 'B',
157 'V')
158 THEN
159 IF jtf_usr_hks.ok_to_execute(
160 'JTF_RS_TEAM_MEMBERS_PVT',
161 'CREATE_RESOURCE_TEAM_MEMBERS',
162 'B',
163 'V')
164 THEN
165
166 jtf_rs_team_member_vuhk.create_team_members_pre(
167 p_team_id => l_team_id,
168 p_team_resource_id => l_team_resource_id,
169 p_resource_type => l_resource_type,
170 x_return_status => x_return_status);
171
172 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
173
174 -- dbms_output.put_line('Returned Error status from the Pre Vertical User Hook');
175
176 x_return_status := fnd_api.g_ret_sts_unexp_error;
177
178 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
179 fnd_msg_pub.add;
180
181 RAISE fnd_api.g_exc_unexpected_error;
182
183 END IF;
184
185 END IF;
186 END IF;
187
188
189 /* Pre Call to the Internal Type User Hook */
190
191 IF jtf_resource_utl.ok_to_execute(
192 'JTF_RS_TEAM_MEMBERS_PVT',
193 'CREATE_RESOURCE_TEAM_MEMBERS',
194 'B',
195 'I')
196 THEN
197 IF jtf_usr_hks.ok_to_execute(
198 'JTF_RS_TEAM_MEMBERS_PVT',
199 'CREATE_RESOURCE_TEAM_MEMBERS',
200 'B',
201 'I')
202 THEN
203
204 jtf_rs_team_member_iuhk.create_team_members_pre(
205 p_team_id => l_team_id,
206 p_team_resource_id => l_team_resource_id,
207 p_resource_type => l_resource_type,
208 x_return_status => x_return_status);
209
210 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
211
212 x_return_status := fnd_api.g_ret_sts_unexp_error;
213
214 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
215 fnd_msg_pub.add;
216
217 RAISE fnd_api.g_exc_unexpected_error;
218
219 END IF;
220
221 END IF;
222 END IF;
223
224
225 /* Validate that the team resource is not an existing member of the team. */
226
227 l_check_count := 0;
228
229 SELECT count(*)
230 INTO l_check_count
231 FROM jtf_rs_team_members
232 WHERE team_id = l_team_id
233 AND team_resource_id = l_team_resource_id
234 AND resource_type = l_resource_type
235 AND nvl(delete_flag,'N') <> 'Y';
236
237 IF l_check_count > 0 THEN
238
239 -- dbms_output.put_line('Resource already exists in the team');
240
241 x_return_status := fnd_api.g_ret_sts_error;
242
243 fnd_message.set_name('JTF', 'JTF_RS_RESOURCE_EXISTS_TEAM');
244 fnd_msg_pub.add;
245
246 RAISE fnd_api.g_exc_unexpected_error;
247
248 END IF;
249
250
251
252 /* If the resource type is INDIVIDUAL' then get the Employee Person Id
253 from the Resource Extension table. */
254
255 IF l_resource_type = 'INDIVIDUAL' THEN
256
257 OPEN c_employee_person_id(l_team_resource_id);
258
259 FETCH c_employee_person_id INTO l_person_id;
260
261
262 IF c_employee_person_id%NOTFOUND THEN
263
264 l_person_id := NULL;
265
266 END IF;
267
268 END IF;
269
270
271 /* Get the next value of the Team_member_id from the sequence. */
272
273 SELECT jtf_rs_team_members_s.nextval
274 INTO l_team_member_id
275 FROM dual;
276
277
278 /* Insert the row into the table by calling the table handler. */
279
280 jtf_rs_team_members_pkg.insert_row(
281 x_rowid => l_rowid,
282 x_team_member_id => l_team_member_id,
283 x_team_id => l_team_id,
284 x_team_resource_id => l_team_resource_id,
285 x_resource_type => l_resource_type,
286 x_person_id => l_person_id,
287 x_delete_flag => 'N',
288 x_attribute1 => p_attribute1,
289 x_attribute2 => p_attribute2,
290 x_attribute3 => p_attribute3,
291 x_attribute4 => p_attribute4,
292 x_attribute5 => p_attribute5,
293 x_attribute6 => p_attribute6,
294 x_attribute7 => p_attribute7,
295 x_attribute8 => p_attribute8,
296 x_attribute9 => p_attribute9,
297 x_attribute10 => p_attribute10,
298 x_attribute11 => p_attribute11,
299 x_attribute12 => p_attribute12,
300 x_attribute13 => p_attribute13,
301 x_attribute14 => p_attribute14,
302 x_attribute15 => p_attribute15,
303 x_attribute_category => p_attribute_category,
304 x_creation_date => SYSDATE,
305 x_created_by => jtf_resource_utl.created_by,
306 x_last_update_date => SYSDATE,
307 x_last_updated_by => jtf_resource_utl.updated_by,
308 x_last_update_login => jtf_resource_utl.login_id
309 );
310
311
312 -- dbms_output.put_line('Inserted Row');
313
314 OPEN c_jtf_rs_team_members(l_rowid);
315
316 FETCH c_jtf_rs_team_members INTO l_check_char;
317
318
319 IF c_jtf_rs_team_members%NOTFOUND THEN
320
321 -- dbms_output.put_line('Error in Table Handler');
322
323 x_return_status := fnd_api.g_ret_sts_unexp_error;
324
325 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
326 fnd_msg_pub.add;
327
328 IF c_jtf_rs_team_members%ISOPEN THEN
329
330 CLOSE c_jtf_rs_team_members;
331
332 END IF;
333
334 RAISE fnd_api.g_exc_unexpected_error;
335
336 ELSE
337
338 -- dbms_output.put_line('Team Member Successfully Created');
339
340 x_team_member_id := l_team_member_id;
341
342 END IF;
343
344
345 /* Close the cursors */
346
347 IF c_employee_person_id%ISOPEN THEN
348
349 CLOSE c_employee_person_id;
350
351 END IF;
352
353 IF c_jtf_rs_team_members%ISOPEN THEN
354
355 CLOSE c_jtf_rs_team_members;
356
357 END IF;
358
359
360 /* Make the post processing call to the user hooks */
361
362 /* Post Call to the Customer Type User Hook */
363
364 IF jtf_resource_utl.ok_to_execute(
365 'JTF_RS_TEAM_MEMBERS_PVT',
366 'CREATE_RESOURCE_TEAM_MEMBERS',
367 'A',
368 'C')
369 THEN
370 IF jtf_usr_hks.ok_to_execute(
371 'JTF_RS_TEAM_MEMBERS_PVT',
372 'CREATE_RESOURCE_TEAM_MEMBERS',
373 'A',
374 'C')
375 THEN
376
377 jtf_rs_team_member_cuhk.create_team_members_post(
378 p_team_member_id => l_team_member_id,
379 p_team_id => l_team_id,
380 p_team_resource_id => l_team_resource_id,
381 p_resource_type => l_resource_type,
382 x_return_status => x_return_status);
383
384 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
385
386 -- dbms_output.put_line('Returned Error status from the Post Customer User Hook');
387
388 x_return_status := fnd_api.g_ret_sts_unexp_error;
389
390 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
391 fnd_msg_pub.add;
392
393 RAISE fnd_api.g_exc_unexpected_error;
394
395 END IF;
396
397 END IF;
398 END IF;
399
400
401 /* Post Call to the Vertical Type User Hook */
402
403 IF jtf_resource_utl.ok_to_execute(
404 'JTF_RS_TEAM_MEMBERS_PVT',
405 'CREATE_RESOURCE_TEAM_MEMBERS',
406 'A',
407 'V')
408 THEN
409 IF jtf_usr_hks.ok_to_execute(
410 'JTF_RS_TEAM_MEMBERS_PVT',
411 'CREATE_RESOURCE_TEAM_MEMBERS',
412 'A',
413 'V')
414 THEN
415
416 jtf_rs_team_member_vuhk.create_team_members_post(
417 p_team_member_id => l_team_member_id,
418 p_team_id => l_team_id,
419 p_team_resource_id => l_team_resource_id,
420 p_resource_type => l_resource_type,
421 x_return_status => x_return_status);
422
423 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
424
425 -- dbms_output.put_line('Returned Error status from the Post Vertical User Hook');
426
427 x_return_status := fnd_api.g_ret_sts_unexp_error;
428
429 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
430 fnd_msg_pub.add;
431
432 RAISE fnd_api.g_exc_unexpected_error;
433
434 END IF;
435
436 END IF;
437 END IF;
438
439
440 /* Post Call to the Internal Type User Hook */
441
442 IF jtf_resource_utl.ok_to_execute(
443 'JTF_RS_TEAM_MEMBERS_PVT',
444 'CREATE_RESOURCE_TEAM_MEMBERS',
445 'A',
446 'I')
447 THEN
448 IF jtf_usr_hks.ok_to_execute(
449 'JTF_RS_TEAM_MEMBERS_PVT',
450 'CREATE_RESOURCE_TEAM_MEMBERS',
451 'A',
452 'I')
453 THEN
454
455 jtf_rs_team_member_iuhk.create_team_members_post(
456 p_team_member_id => l_team_member_id,
457 p_team_id => l_team_id,
458 p_team_resource_id => l_team_resource_id,
459 p_resource_type => l_resource_type,
460 x_return_status => x_return_status);
461
462 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
463
464 x_return_status := fnd_api.g_ret_sts_unexp_error;
465
466 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
467 fnd_msg_pub.add;
468
469 RAISE fnd_api.g_exc_unexpected_error;
470
471 END IF;
472
473 END IF;
474 END IF;
475
476
477 /* Standard call for Message Generation */
478
479 IF jtf_resource_utl.ok_to_execute(
480 'JTF_RS_TEAM_MEMBERS_PVT',
481 'CREATE_RESOURCE_TEAM_MEMBERS',
482 'M',
483 'M')
484 THEN
485 IF jtf_usr_hks.ok_to_execute(
486 'JTF_RS_TEAM_MEMBERS_PVT',
487 'CREATE_RESOURCE_TEAM_MEMBERS',
488 'M',
489 'M')
490 THEN
491
492 IF (jtf_rs_team_member_cuhk.ok_to_generate_msg(
493 p_team_member_id => l_team_member_id,
494 x_return_status => x_return_status) )
495 THEN
496
497 /* Get the bind data id for the Business Object Instance */
498
499 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
500
501
502 /* Set bind values for the bind variables in the Business Object SQL */
503
504 jtf_usr_hks.load_bind_data(l_bind_data_id, 'team_member_id', l_team_member_id, 'S', 'N');
505
506
507 /* Call the message generation API */
508
509 jtf_usr_hks.generate_message(
510 p_prod_code => 'JTF',
511 p_bus_obj_code => 'RS_TMBR',
512 p_action_code => 'I',
513 p_bind_data_id => l_bind_data_id,
514 x_return_code => x_return_status);
515
516
517 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
518
519 -- dbms_output.put_line('Returned Error status from the Message Generation API');
520
521 x_return_status := fnd_api.g_ret_sts_unexp_error;
522
523 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
524 fnd_msg_pub.add;
525
526 RAISE fnd_api.g_exc_unexpected_error;
527
528 END IF;
529
530 END IF;
531
532 END IF;
533 END IF;
534
535 -- create wf_user_role record for the new team member
536 -- Don't care for its success status
537 BEGIN
538 IF l_resource_type = 'INDIVIDUAL' THEN
539 l_resource_id := l_team_resource_id;
540 l_group_id := NULL;
541 ELSE
542 l_resource_id := NULL;
543 l_group_id := l_team_resource_id;
544 END IF;
545
546 jtf_rs_wf_integration_pub.create_resource_team_members
547 (P_API_VERSION => 1.0,
548 P_RESOURCE_ID => l_resource_id,
549 P_GROUP_ID => l_group_id,
550 P_TEAM_ID => l_team_id,
551 X_RETURN_STATUS => l_return_status,
552 X_MSG_COUNT => l_msg_count,
553 X_MSG_DATA => l_msg_data);
554 EXCEPTION
555 WHEN OTHERS THEN
556 NULL;
557 END;
558
559
560 IF fnd_api.to_boolean(p_commit) THEN
561
562 COMMIT WORK;
563
564 END IF;
565
566 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
567
568
569 EXCEPTION
570
571
572 WHEN fnd_api.g_exc_unexpected_error THEN
573
574 -- DBMS_OUTPUT.put_line (' ========================================== ');
575
576 -- DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error ======= ======== ');
577
578 ROLLBACK TO create_resource_member_pvt;
579
580 x_return_status := fnd_api.g_ret_sts_unexp_error;
581
582 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
583
584
585 WHEN OTHERS THEN
586
587 -- DBMS_OUTPUT.put_line (' ========================================== ');
588
589 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Create Team_member Pvt ============= ');
590
591 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
592
593 ROLLBACK TO create_resource_member_pvt;
594
595 x_return_status := fnd_api.g_ret_sts_unexp_error;
596
597 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
598
599
600 END create_resource_team_members;
601
602
603 /* Procedure to update the resource team members
604 based on input values passed by calling routines. */
605
606 PROCEDURE update_resource_team_members
607 (P_API_VERSION IN NUMBER,
608 P_INIT_MSG_LIST IN VARCHAR2,
609 P_COMMIT IN VARCHAR2,
610 P_TEAM_MEMBER_ID IN JTF_RS_TEAM_MEMBERS.TEAM_MEMBER_ID%TYPE,
611 P_TEAM_ID IN JTF_RS_TEAM_MEMBERS.TEAM_ID%TYPE,
612 P_TEAM_RESOURCE_ID IN JTF_RS_TEAM_MEMBERS.TEAM_RESOURCE_ID%TYPE,
613 P_PERSON_ID IN JTF_RS_TEAM_MEMBERS.PERSON_ID%TYPE,
614 P_RESOURCE_TYPE IN JTF_RS_TEAM_MEMBERS.RESOURCE_TYPE%TYPE,
615 P_DELETE_FLAG IN JTF_RS_TEAM_MEMBERS.DELETE_FLAG%TYPE,
616 P_ATTRIBUTE1 IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE1%TYPE,
617 P_ATTRIBUTE2 IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE2%TYPE,
618 P_ATTRIBUTE3 IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE3%TYPE,
619 P_ATTRIBUTE4 IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE4%TYPE,
620 P_ATTRIBUTE5 IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE5%TYPE,
621 P_ATTRIBUTE6 IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE6%TYPE,
622 P_ATTRIBUTE7 IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE7%TYPE,
623 P_ATTRIBUTE8 IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE8%TYPE,
624 P_ATTRIBUTE9 IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE9%TYPE,
625 P_ATTRIBUTE10 IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE10%TYPE,
626 P_ATTRIBUTE11 IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE11%TYPE,
627 P_ATTRIBUTE12 IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE12%TYPE,
628 P_ATTRIBUTE13 IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE13%TYPE,
629 P_ATTRIBUTE14 IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE14%TYPE,
630 P_ATTRIBUTE15 IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE15%TYPE,
631 P_ATTRIBUTE_CATEGORY IN JTF_RS_TEAM_MEMBERS.ATTRIBUTE_CATEGORY%TYPE,
632 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY JTF_RS_TEAM_MEMBERS.OBJECT_VERSION_NUMBER%TYPE,
633 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
634 X_MSG_COUNT OUT NOCOPY NUMBER,
635 X_MSG_DATA OUT NOCOPY VARCHAR2
636 ) IS
637
638 l_api_version CONSTANT NUMBER := 1.0;
639 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_TEAM_MEMBERS';
640 l_team_member_id jtf_rs_team_members.team_member_id%TYPE;
641 l_team_id jtf_rs_team_members.team_id%TYPE;
642 l_team_resource_id jtf_rs_team_members.team_resource_id%TYPE;
643 l_resource_type jtf_rs_team_members.resource_type%TYPE;
644 l_person_id jtf_rs_team_members.person_id%TYPE;
645 l_delete_flag jtf_rs_team_members.delete_flag%TYPE;
646 l_object_version_number jtf_rs_team_members.object_version_number%TYPE;
647 l_attribute1 jtf_rs_team_members.attribute1%TYPE;
648 l_attribute2 jtf_rs_team_members.attribute2%TYPE;
649 l_attribute3 jtf_rs_team_members.attribute3%TYPE;
650 l_attribute4 jtf_rs_team_members.attribute4%TYPE;
651 l_attribute5 jtf_rs_team_members.attribute5%TYPE;
652 l_attribute6 jtf_rs_team_members.attribute6%TYPE;
653 l_attribute7 jtf_rs_team_members.attribute7%TYPE;
654 l_attribute8 jtf_rs_team_members.attribute8%TYPE;
655 l_attribute9 jtf_rs_team_members.attribute9%TYPE;
656 l_attribute10 jtf_rs_team_members.attribute10%TYPE;
657 l_attribute11 jtf_rs_team_members.attribute11%TYPE;
658 l_attribute12 jtf_rs_team_members.attribute12%TYPE;
659 l_attribute13 jtf_rs_team_members.attribute13%TYPE;
660 l_attribute14 jtf_rs_team_members.attribute14%TYPE;
661 l_attribute15 jtf_rs_team_members.attribute15%TYPE;
662 l_attribute_catgory jtf_rs_team_members.attribute_category%TYPE;
663
664 l_check_char VARCHAR2(1);
665 l_check_count NUMBER;
666 l_bind_data_id NUMBER;
667
668 CURSOR c_rs_team_members_update(l_team_member_id IN NUMBER) IS
669 SELECT DECODE(p_team_id, fnd_api.g_miss_num, team_id, p_team_id) l_team_id,
670 DECODE(p_team_resource_id, fnd_api.g_miss_num, team_resource_id, p_team_resource_id) l_team_resource_id,
671 DECODE(p_person_id, fnd_api.g_miss_num, person_id, p_person_id) l_person_id,
672 DECODE(p_resource_type, fnd_api.g_miss_char, resource_type, p_resource_type) l_resource_type,
673 DECODE(p_delete_flag, fnd_api.g_miss_char, delete_flag, p_delete_flag) l_delete_flag,
674 DECODE(p_attribute1, fnd_api.g_miss_char, attribute1, p_attribute1) l_attribute1,
675 DECODE(p_attribute2, fnd_api.g_miss_char, attribute2, p_attribute2) l_attribute2,
676 DECODE(p_attribute3, fnd_api.g_miss_char, attribute3, p_attribute3) l_attribute3,
677 DECODE(p_attribute4, fnd_api.g_miss_char, attribute4, p_attribute4) l_attribute4,
678 DECODE(p_attribute5, fnd_api.g_miss_char, attribute5, p_attribute5) l_attribute5,
679 DECODE(p_attribute6, fnd_api.g_miss_char, attribute6, p_attribute6) l_attribute6,
680 DECODE(p_attribute7, fnd_api.g_miss_char, attribute7, p_attribute7) l_attribute7,
681 DECODE(p_attribute8, fnd_api.g_miss_char, attribute8, p_attribute8) l_attribute8,
682 DECODE(p_attribute9, fnd_api.g_miss_char, attribute9, p_attribute9) l_attribute9,
683 DECODE(p_attribute10, fnd_api.g_miss_char, attribute10, p_attribute10) l_attribute10,
684 DECODE(p_attribute11, fnd_api.g_miss_char, attribute11, p_attribute11) l_attribute11,
685 DECODE(p_attribute12, fnd_api.g_miss_char, attribute12, p_attribute12) l_attribute12,
686 DECODE(p_attribute13, fnd_api.g_miss_char, attribute13, p_attribute13) l_attribute13,
687 DECODE(p_attribute14, fnd_api.g_miss_char, attribute14, p_attribute14) l_attribute14,
688 DECODE(p_attribute15, fnd_api.g_miss_char, attribute15, p_attribute15) l_attribute15,
689 DECODE(p_attribute_category, fnd_api.g_miss_char, attribute_category, p_attribute_category) l_attribute_category
690 FROM jtf_rs_team_members
691 WHERE team_member_id = l_team_member_id ;
692
693 rs_team_member_rec c_rs_team_members_update%ROWTYPE ;
694
695 BEGIN
696
697 l_team_member_id := p_team_member_id;
698 l_team_id := p_team_id;
699 l_team_resource_id := p_team_resource_id;
700 l_resource_type := upper(p_resource_type);
701 l_person_id := p_person_id;
702 l_delete_flag := p_delete_flag ;
703 l_object_version_number := p_object_version_number ;
704 l_attribute1 := p_attribute1 ;
705 l_attribute2 := p_attribute2 ;
706 l_attribute3 := p_attribute3 ;
707 l_attribute4 := p_attribute4 ;
708 l_attribute5 := p_attribute5 ;
709 l_attribute6 := p_attribute6 ;
710 l_attribute7 := p_attribute7 ;
711 l_attribute8 := p_attribute8 ;
712 l_attribute9 := p_attribute9 ;
713 l_attribute10 := p_attribute10 ;
714 l_attribute11 := p_attribute11 ;
715 l_attribute12 := p_attribute12 ;
716 l_attribute13 := p_attribute13 ;
717 l_attribute14 := p_attribute14 ;
718 l_attribute15 := p_attribute15 ;
719 l_attribute_catgory := p_attribute_category ;
720
721 SAVEPOINT update_resource_member_pvt;
722 x_return_status := fnd_api.g_ret_sts_success;
723
724 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
725 RAISE fnd_api.g_exc_unexpected_error;
726 END IF;
727
728 IF fnd_api.to_boolean(p_init_msg_list) THEN
729 fnd_msg_pub.initialize;
730 END IF;
731
732 /* Make the pre processing call to the user hooks */
733
734 /* Pre Call to the Customer Type User Hook */
735
736 IF jtf_resource_utl.ok_to_execute(
737 'JTF_RS_TEAM_MEMBERS_PVT',
738 'UPDATE_RESOURCE_TEAM_MEMBERS',
739 'B',
740 'C')
741 THEN
742 IF jtf_usr_hks.ok_to_execute(
743 'JTF_RS_TEAM_MEMBERS_PVT',
744 'UPDATE_RESOURCE_TEAM_MEMBERS',
745 'B',
746 'C')
747 THEN
748 jtf_rs_team_member_cuhk.update_team_members_pre(
749 p_team_member_id => l_team_member_id,
750 x_return_status => x_return_status);
751
752 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
753 -- dbms_output.put_line('Returned Error status from the Pre Customer User Hook');
754 x_return_status := fnd_api.g_ret_sts_unexp_error;
755 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
756 fnd_msg_pub.add;
757 RAISE fnd_api.g_exc_unexpected_error;
758 END IF;
759 END IF;
760 END IF;
761
762 /* Pre Call to the Vertical Type User Hook */
763
764 IF jtf_resource_utl.ok_to_execute(
765 'JTF_RS_TEAM_MEMBERS_PVT',
766 'UPDATE_RESOURCE_TEAM_MEMBERS',
767 'B',
768 'V')
769 THEN
770 IF jtf_usr_hks.ok_to_execute(
771 'JTF_RS_TEAM_MEMBERS_PVT',
772 'UPDATE_RESOURCE_TEAM_MEMBERS',
773 'B',
774 'V')
775 THEN
776 jtf_rs_team_member_vuhk.update_team_members_pre(
777 p_team_member_id => l_team_member_id,
778 x_return_status => x_return_status);
779
780 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
781 -- dbms_output.put_line('Returned Error status from the Pre Vertical User Hook');
782 x_return_status := fnd_api.g_ret_sts_unexp_error;
783 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
784 fnd_msg_pub.add;
785 RAISE fnd_api.g_exc_unexpected_error;
786 END IF;
787 END IF;
788 END IF;
789
790 /* Pre Call to the Internal Type User Hook */
791
792 IF jtf_resource_utl.ok_to_execute(
793 'JTF_RS_TEAM_MEMBERS_PVT',
794 'UPDATE_RESOURCE_TEAM_MEMBERS',
795 'B',
796 'I')
797 THEN
798 IF jtf_usr_hks.ok_to_execute(
799 'JTF_RS_TEAM_MEMBERS_PVT',
800 'UPDATE_RESOURCE_TEAM_MEMBERS',
801 'B',
802 'I')
803 THEN
804 jtf_rs_team_member_iuhk.update_team_members_pre(
805 p_team_member_id => l_team_member_id,
806 x_return_status => x_return_status);
807 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
808 x_return_status := fnd_api.g_ret_sts_unexp_error;
809 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
810 fnd_msg_pub.add;
811 RAISE fnd_api.g_exc_unexpected_error;
812 END IF;
813 END IF;
814 END IF;
815
816 /* Validate the team member for update. */
817
818 OPEN c_rs_team_members_update(l_team_member_id) ;
819 FETCH c_rs_team_members_update
820 INTO rs_team_member_rec ;
821 IF c_rs_team_members_update%NOTFOUND THEN
822 CLOSE c_rs_team_members_update ;
823 fnd_message.set_name('JTF', 'JTF_RS_INVALID_TEAM_MBR_ID');
824 fnd_message.set_token('P_TEAM_MBR_ID', p_team_member_id);
825 fnd_msg_pub.add;
826 x_return_status := fnd_api.g_ret_sts_unexp_error;
827 RAISE fnd_api.g_exc_unexpected_error;
828 END IF ;
829
830 /* Call the Lock Row Table Handler before updating the record */
831 BEGIN
832 jtf_rs_team_members_pkg.lock_row(
833 x_team_member_id => l_team_member_id,
834 x_object_version_number => l_object_version_number) ;
835 EXCEPTION
836 WHEN OTHERS THEN
837 x_return_status := fnd_api.g_ret_sts_unexp_error;
838
839 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
840 fnd_msg_pub.add;
841
842 RAISE fnd_api.g_exc_unexpected_error;
843 END;
844
845 /* Update the Object Version Number by Incrementing It */
846 l_object_version_number := l_object_version_number + 1 ;
847
848 /* Update the row into the table by calling the table handler. */
849 BEGIN
850 jtf_rs_team_members_pkg.update_row(
851 x_team_member_id => l_team_member_id,
852 x_team_id => rs_team_member_rec.l_team_id,
853 x_team_resource_id => rs_team_member_rec.l_team_resource_id,
854 x_resource_type => rs_team_member_rec.l_resource_type,
855 x_person_id => rs_team_member_rec.l_person_id,
856 x_delete_flag => rs_team_member_rec.l_delete_flag,
857 x_object_version_number =>l_object_version_number,
858 x_attribute1 => rs_team_member_rec.l_attribute1,
859 x_attribute2 => rs_team_member_rec.l_attribute2,
860 x_attribute3 => rs_team_member_rec.l_attribute3,
861 x_attribute4 => rs_team_member_rec.l_attribute4,
862 x_attribute5 => rs_team_member_rec.l_attribute5,
863 x_attribute6 => rs_team_member_rec.l_attribute6,
864 x_attribute7 => rs_team_member_rec.l_attribute7,
865 x_attribute8 => rs_team_member_rec.l_attribute8,
866 x_attribute9 => rs_team_member_rec.l_attribute9,
867 x_attribute10 => rs_team_member_rec.l_attribute10,
868 x_attribute11 => rs_team_member_rec.l_attribute11,
869 x_attribute12 => rs_team_member_rec.l_attribute12,
870 x_attribute13 => rs_team_member_rec.l_attribute13,
871 x_attribute14 => rs_team_member_rec.l_attribute14,
872 x_attribute15 => rs_team_member_rec.l_attribute15,
873 x_attribute_category => rs_team_member_rec.l_attribute_category,
874 x_last_update_date => SYSDATE,
875 x_last_updated_by => jtf_resource_utl.updated_by,
876 x_last_update_login => jtf_resource_utl.login_id
877 );
878
879 p_object_version_number := l_object_version_number ;
880 -- dbms_output.put_line('Updated Row');
881
882 EXCEPTION
883 WHEN NO_DATA_FOUND THEN
884 CLOSE c_rs_team_members_update ;
885 x_return_status := fnd_api.g_ret_sts_unexp_error ;
886 fnd_message.set_name('JTF','JTF_RS_TABLE_HANDLER_ERROR') ;
887 fnd_msg_pub.add ;
888 RAISE fnd_api.g_exc_unexpected_error ;
889 END ;
890
891 IF c_rs_team_members_update%ISOPEN THEN
892 CLOSE c_rs_team_members_update;
893 END IF;
894
895 /* Make the post processing call to the user hooks */
896
897 /* Post Call to the Customer Type User Hook */
898
899 IF jtf_resource_utl.ok_to_execute(
900 'JTF_RS_TEAM_MEMBERS_PVT',
901 'UPDATE_RESOURCE_TEAM_MEMBERS',
902 'A',
903 'C')
904 THEN
905 IF jtf_usr_hks.ok_to_execute(
906 'JTF_RS_TEAM_MEMBERS_PVT',
907 'UPDATE_RESOURCE_TEAM_MEMBERS',
908 'A',
909 'C')
910 THEN
911 jtf_rs_team_member_cuhk.update_team_members_post(
912 p_team_member_id => l_team_member_id,
913 x_return_status => x_return_status);
914
915 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
916 x_return_status := fnd_api.g_ret_sts_unexp_error;
917 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
918 fnd_msg_pub.add;
919 RAISE fnd_api.g_exc_unexpected_error;
920 END IF;
921 END IF;
922 END IF;
923
924 /* Post Call to the Vertical Type User Hook */
925
926 IF jtf_resource_utl.ok_to_execute(
927 'JTF_RS_TEAM_MEMBERS_PVT',
928 'UPDATE_RESOURCE_TEAM_MEMBERS',
929 'A',
930 'V')
931 THEN
932 IF jtf_usr_hks.ok_to_execute(
933 'JTF_RS_TEAM_MEMBERS_PVT',
934 'UPDATE_RESOURCE_TEAM_MEMBERS',
935 'A',
936 'V')
937 THEN
938 jtf_rs_team_member_vuhk.update_team_members_post(
939 p_team_member_id => l_team_member_id,
940 x_return_status => x_return_status);
941
942 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
943 -- dbms_output.put_line('Returned Error status from the Post Vertical User Hook');
944 x_return_status := fnd_api.g_ret_sts_unexp_error;
945 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
946 fnd_msg_pub.add;
947 RAISE fnd_api.g_exc_unexpected_error;
948 END IF;
949 END IF;
950 END IF;
951
952 /* Post Call to the Internal Type User Hook */
953
954 IF jtf_resource_utl.ok_to_execute(
955 'JTF_RS_TEAM_MEMBERS_PVT',
956 'UPDATE_RESOURCE_TEAM_MEMBERS',
957 'A',
958 'I')
959 THEN
960 IF jtf_usr_hks.ok_to_execute(
961 'JTF_RS_TEAM_MEMBERS_PVT',
962 'UPDATE_RESOURCE_TEAM_MEMBERS',
963 'A',
964 'I')
965 THEN
966 jtf_rs_team_member_iuhk.update_team_members_post(
967 p_team_member_id => l_team_member_id,
968 x_return_status => x_return_status);
969
970 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
971 x_return_status := fnd_api.g_ret_sts_unexp_error;
972 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
973 fnd_msg_pub.add;
974 RAISE fnd_api.g_exc_unexpected_error;
975 END IF;
976 END IF;
977 END IF;
978
979 /* Standard call for Message Generation */
980
981 IF jtf_resource_utl.ok_to_execute(
982 'JTF_RS_TEAM_MEMBERS_PVT',
983 'UPDATE_RESOURCE_TEAM_MEMBERS',
984 'M',
985 'M')
986 THEN
987 IF jtf_usr_hks.ok_to_execute(
988 'JTF_RS_TEAM_MEMBERS_PVT',
989 'UPDATE_RESOURCE_TEAM_MEMBERS',
990 'M',
991 'M')
992 THEN
993 IF (jtf_rs_team_member_cuhk.ok_to_generate_msg(
994 p_team_member_id => l_team_member_id,
995 x_return_status => x_return_status) )
996 THEN
997
998 /* Get the bind data id for the Business Object Instance */
999
1000 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1001
1002
1003 /* Set bind values for the bind variables in the Business Object SQL */
1004
1005 jtf_usr_hks.load_bind_data(l_bind_data_id, 'team_member_id', l_team_member_id, 'S', 'N');
1006
1007 /* Call the message generation API */
1008
1009 jtf_usr_hks.generate_message(
1010 p_prod_code => 'JTF',
1011 p_bus_obj_code => 'RS_TMBR',
1012 p_action_code => 'U',
1013 p_bind_data_id => l_bind_data_id,
1014 x_return_code => x_return_status);
1015
1016
1017 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1018 -- dbms_output.put_line('Returned Error status from the Message Generation API');
1019 x_return_status := fnd_api.g_ret_sts_unexp_error;
1020 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1021 fnd_msg_pub.add;
1022 RAISE fnd_api.g_exc_unexpected_error;
1023 END IF;
1024 END IF;
1025 END IF;
1026 END IF;
1027
1028 IF fnd_api.to_boolean(p_commit) THEN
1029 COMMIT WORK;
1030 END IF;
1031
1032 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1033
1034 EXCEPTION
1035 WHEN fnd_api.g_exc_unexpected_error THEN
1036 ROLLBACK TO update_resource_member_pvt;
1037 x_return_status := fnd_api.g_ret_sts_unexp_error;
1038 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1039 WHEN OTHERS THEN
1040 ROLLBACK TO update_resource_member_pvt;
1041 fnd_message.set_token('P_SQLCODE',SQLCODE) ;
1042 fnd_message.set_token('P_SQLERRM',SQLERRM) ;
1043 fnd_message.set_token('P_API_NAME',l_api_name) ;
1044 fnd_msg_pub.add ;
1045 x_return_status := fnd_api.g_ret_sts_unexp_error;
1046 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1047 END update_resource_team_members;
1048
1049 /* Procedure to delete the resource team members. */
1050
1051 PROCEDURE delete_resource_team_members
1052 (P_API_VERSION IN NUMBER,
1053 P_INIT_MSG_LIST IN VARCHAR2,
1054 P_COMMIT IN VARCHAR2,
1055 P_TEAM_ID IN JTF_RS_TEAM_MEMBERS.TEAM_ID%TYPE,
1056 P_TEAM_RESOURCE_ID IN JTF_RS_TEAM_MEMBERS.TEAM_RESOURCE_ID%TYPE,
1057 P_RESOURCE_TYPE IN JTF_RS_TEAM_MEMBERS.RESOURCE_TYPE%TYPE,
1058 P_OBJECT_VERSION_NUM IN JTF_RS_TEAM_MEMBERS.OBJECT_VERSION_NUMBER%TYPE,
1059 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1060 X_MSG_COUNT OUT NOCOPY NUMBER,
1061 X_MSG_DATA OUT NOCOPY VARCHAR2
1062 ) IS
1063
1064 l_api_version CONSTANT NUMBER := 1.0;
1065 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_TEAM_MEMBERS';
1066 l_team_id jtf_rs_team_members.team_id%TYPE;
1067 l_team_resource_id jtf_rs_team_members.team_resource_id%TYPE;
1068 l_resource_type jtf_rs_team_members.resource_type%TYPE;
1069 l_team_member_id jtf_rs_team_members.team_member_id%TYPE;
1070 l_role_relate_count NUMBER;
1071 l_bind_data_id NUMBER;
1072 l_resource_id NUMBER;
1073 l_group_id NUMBER;
1074 l_return_status VARCHAR2(1);
1075 l_msg_data VARCHAR2(2000);
1076 l_msg_count NUMBER;
1077
1078 CURSOR c_team_member_id(
1079 l_team_id IN NUMBER,
1080 l_team_resource_id IN NUMBER,
1081 l_resource_type IN VARCHAR2)
1082 IS
1083 SELECT team_member_id
1084 FROM jtf_rs_team_members
1085 WHERE team_id = l_team_id
1086 AND team_resource_id = l_team_resource_id
1087 AND resource_type = l_resource_type
1088 AND nvl(delete_flag,'N') <> 'Y';
1089
1090
1091 CURSOR c_related_role_count(
1092 l_team_member_id IN NUMBER)
1093 IS
1094 SELECT count(*)
1095 FROM jtf_rs_role_relations
1096 WHERE role_resource_type = 'RS_TEAM_MEMBER'
1097 AND role_resource_id = l_team_member_id
1098 AND ( end_date_active is null OR end_date_active >= sysdate )
1099 AND nvl(delete_flag,'N') <> 'Y';
1100
1101
1102
1103 BEGIN
1104
1105 l_team_id := p_team_id;
1106 l_team_resource_id := p_team_resource_id;
1107 l_resource_type := upper(p_resource_type);
1108
1109 SAVEPOINT delete_resource_member_pvt;
1110
1111 x_return_status := fnd_api.g_ret_sts_success;
1112
1113 -- DBMS_OUTPUT.put_line(' Started Delete Resource Member Pvt ');
1114
1115
1116 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1117
1118 RAISE fnd_api.g_exc_unexpected_error;
1119
1120 END IF;
1121
1122
1123 IF fnd_api.to_boolean(p_init_msg_list) THEN
1124
1125 fnd_msg_pub.initialize;
1126
1127 END IF;
1128
1129
1130 /* Make the pre processing call to the user hooks */
1131
1132 /* Pre Call to the Customer Type User Hook */
1133
1134 IF jtf_resource_utl.ok_to_execute(
1135 'JTF_RS_TEAM_MEMBERS_PVT',
1136 'DELETE_RESOURCE_TEAM_MEMBERS',
1137 'B',
1138 'C')
1139 THEN
1140 IF jtf_usr_hks.ok_to_execute(
1141 'JTF_RS_TEAM_MEMBERS_PVT',
1142 'DELETE_RESOURCE_TEAM_MEMBERS',
1143 'B',
1144 'C')
1145 THEN
1146
1147 jtf_rs_team_member_cuhk.delete_team_members_pre(
1148 p_team_id => l_team_id,
1149 p_team_resource_id => l_team_resource_id,
1150 p_resource_type => l_resource_type,
1151 x_return_status => x_return_status);
1152
1153 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1154
1155 -- dbms_output.put_line('Returned Error status from the Pre Customer User Hook');
1156
1157 x_return_status := fnd_api.g_ret_sts_unexp_error;
1158
1159 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
1160 fnd_msg_pub.add;
1161
1162 RAISE fnd_api.g_exc_unexpected_error;
1163
1164 END IF;
1165
1166 END IF;
1167 END IF;
1168
1169
1170 /* Pre Call to the Vertical Type User Hook */
1171
1172 IF jtf_resource_utl.ok_to_execute(
1173 'JTF_RS_TEAM_MEMBERS_PVT',
1174 'DELETE_RESOURCE_TEAM_MEMBERS',
1175 'B',
1176 'V')
1177 THEN
1178 IF jtf_usr_hks.ok_to_execute(
1179 'JTF_RS_TEAM_MEMBERS_PVT',
1180 'DELETE_RESOURCE_TEAM_MEMBERS',
1181 'B',
1182 'V')
1183 THEN
1184
1185 jtf_rs_team_member_vuhk.delete_team_members_pre(
1186 p_team_id => l_team_id,
1187 p_team_resource_id => l_team_resource_id,
1188 p_resource_type => l_resource_type,
1189 x_return_status => x_return_status);
1190
1191 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1192
1193 -- dbms_output.put_line('Returned Error status from the Pre Vertical User Hook');
1194
1195 x_return_status := fnd_api.g_ret_sts_unexp_error;
1196
1197 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
1198 fnd_msg_pub.add;
1199
1200 RAISE fnd_api.g_exc_unexpected_error;
1201
1202 END IF;
1203
1204 END IF;
1205 END IF;
1206
1207
1208 /* Pre Call to the Internal Type User Hook */
1209
1210 IF jtf_resource_utl.ok_to_execute(
1211 'JTF_RS_TEAM_MEMBERS_PVT',
1212 'DELETE_RESOURCE_TEAM_MEMBERS',
1213 'B',
1214 'I')
1215 THEN
1216 IF jtf_usr_hks.ok_to_execute(
1217 'JTF_RS_TEAM_MEMBERS_PVT',
1218 'DELETE_RESOURCE_TEAM_MEMBERS',
1219 'B',
1220 'I')
1221 THEN
1222
1223 jtf_rs_team_member_iuhk.delete_team_members_pre(
1224 p_team_id => l_team_id,
1225 p_team_resource_id => l_team_resource_id,
1226 p_resource_type => l_resource_type,
1227 x_return_status => x_return_status);
1228
1229 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1230
1231 x_return_status := fnd_api.g_ret_sts_unexp_error;
1232
1233 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
1234 fnd_msg_pub.add;
1235
1236 RAISE fnd_api.g_exc_unexpected_error;
1237
1238 END IF;
1239
1240 END IF;
1241 END IF;
1242
1243
1244 /* Validate that the specified resource exists as a member of the
1245 specified team */
1246
1247 OPEN c_team_member_id(l_team_id, l_team_resource_id, l_resource_type);
1248
1249 FETCH c_team_member_id INTO l_team_member_id;
1250
1251
1252 IF c_team_member_id%NOTFOUND THEN
1253
1254 -- dbms_output.put_line('Resource is not setup as a member of the Team');
1255
1256 IF c_team_member_id%ISOPEN THEN
1257
1258 CLOSE c_team_member_id;
1259
1260 END IF;
1261
1262 fnd_message.set_name('JTF', 'JTF_RS_INVALID_TEAM_MEMBER');
1263 fnd_message.set_token('P_TEAM_RESOURCE_ID', l_team_resource_id);
1264 fnd_msg_pub.add;
1265
1266 x_return_status := fnd_api.g_ret_sts_unexp_error;
1267
1268 RAISE fnd_api.g_exc_unexpected_error;
1269
1270 END IF;
1271
1272
1273 /* Close the cursor */
1274
1275 IF c_team_member_id%ISOPEN THEN
1276
1277 CLOSE c_team_member_id;
1278
1279 END IF;
1280
1281
1282 /* Validate that there are no associated roles with the team member
1283 which are presently active or will be active in future */
1284
1285 OPEN c_related_role_count(l_team_member_id);
1286
1287 FETCH c_related_role_count INTO l_role_relate_count;
1288
1289
1290 IF c_related_role_count%NOTFOUND THEN
1291
1292 -- dbms_output.put_line('Error in getting the count of the related role records');
1293
1294 IF c_related_role_count%ISOPEN THEN
1295
1296 CLOSE c_related_role_count;
1297
1298 END IF;
1299
1300 fnd_message.set_name('JTF', 'JTF_RS_ERROR_ROLE_COUNT');
1301 fnd_msg_pub.add;
1302
1303 x_return_status := fnd_api.g_ret_sts_unexp_error;
1304
1305 RAISE fnd_api.g_exc_unexpected_error;
1306
1307 ELSE
1308
1309 IF l_role_relate_count > 0 THEN
1310
1311 -- dbms_output.put_line('Active Role Related Records found');
1312
1313 IF c_related_role_count%ISOPEN THEN
1314
1315 CLOSE c_related_role_count;
1316
1317 END IF;
1318
1319 fnd_message.set_name('JTF', 'JTF_RS_ACTIVE_TEAM_ROLE_EXIST');
1320 fnd_msg_pub.add;
1321
1322 x_return_status := fnd_api.g_ret_sts_unexp_error;
1323
1324 RAISE fnd_api.g_exc_unexpected_error;
1325
1326 END IF;
1327
1328 END IF;
1329
1330
1331
1332 /* Call the lock row procedure to ensure that the object version number
1333 is still valid. */
1334
1335 BEGIN
1336
1337 jtf_rs_team_members_pkg.lock_row(
1338 x_team_member_id => l_team_member_id,
1339 x_object_version_number => p_object_version_num
1340 );
1341
1342 EXCEPTION
1343
1344 WHEN OTHERS THEN
1345
1346 -- dbms_output.put_line('Error in Locking the Row');
1347
1348 x_return_status := fnd_api.g_ret_sts_unexp_error;
1349
1350 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
1351 fnd_msg_pub.add;
1352
1353 RAISE fnd_api.g_exc_unexpected_error;
1354
1355 END;
1356
1357
1358
1359 /* Call the private procedure for logical delete */
1360
1361 BEGIN
1362
1363 -- delete wf_user_role record for the new team member
1364 -- Don't care for its success status
1365 BEGIN
1366 IF l_resource_type = 'INDIVIDUAL' THEN
1367 l_resource_id := l_team_resource_id;
1368 l_group_id := NULL;
1369 ELSE
1370 l_resource_id := NULL;
1371 l_group_id := l_team_resource_id;
1372 END IF;
1373
1374 jtf_rs_wf_integration_pub.delete_resource_team_members
1375 (P_API_VERSION => 1.0,
1376 P_RESOURCE_ID => l_resource_id,
1377 P_GROUP_ID => l_group_id,
1378 P_TEAM_ID => l_team_id,
1379 X_RETURN_STATUS => l_return_status,
1380 X_MSG_COUNT => l_msg_count,
1381 X_MSG_DATA => l_msg_data);
1382 EXCEPTION
1383 WHEN OTHERS THEN
1384 NULL;
1385 END;
1386
1387 /* Delete the row into the table by calling the table handler. */
1388
1389 jtf_rs_team_members_pkg.logical_delete_row(
1390 x_team_member_id => l_team_member_id
1391 );
1392
1393 EXCEPTION
1394
1395 WHEN NO_DATA_FOUND THEN
1396
1397 -- dbms_output.put_line('Error in Table Handler');
1398
1399 x_return_status := fnd_api.g_ret_sts_unexp_error;
1400
1401 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
1402 fnd_msg_pub.add;
1403
1404 RAISE fnd_api.g_exc_unexpected_error;
1405
1406 END;
1407
1408
1409 /* Make the post processing call to the user hooks */
1410
1411 /* Post Call to the Customer Type User Hook */
1412
1413 IF jtf_resource_utl.ok_to_execute(
1414 'JTF_RS_TEAM_MEMBERS_PVT',
1415 'DELETE_RESOURCE_TEAM_MEMBERS',
1416 'A',
1417 'C')
1418 THEN
1419 IF jtf_usr_hks.ok_to_execute(
1420 'JTF_RS_TEAM_MEMBERS_PVT',
1421 'DELETE_RESOURCE_TEAM_MEMBERS',
1422 'A',
1423 'C')
1424 THEN
1425
1426 jtf_rs_team_member_cuhk.delete_team_members_post(
1427 p_team_id => l_team_id,
1428 p_team_resource_id => l_team_resource_id,
1429 p_resource_type => l_resource_type,
1430 x_return_status => x_return_status);
1431
1432 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1433
1434 -- dbms_output.put_line('Returned Error status from the Post Customer User Hook');
1435
1436 x_return_status := fnd_api.g_ret_sts_unexp_error;
1437
1438 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
1439 fnd_msg_pub.add;
1440
1441 RAISE fnd_api.g_exc_unexpected_error;
1442
1443 END IF;
1444
1445 END IF;
1446 END IF;
1447
1448
1449 /* Post Call to the Vertical Type User Hook */
1450
1451 IF jtf_resource_utl.ok_to_execute(
1452 'JTF_RS_TEAM_MEMBERS_PVT',
1453 'DELETE_RESOURCE_TEAM_MEMBERS',
1454 'A',
1455 'V')
1456 THEN
1457 IF jtf_usr_hks.ok_to_execute(
1458 'JTF_RS_TEAM_MEMBERS_PVT',
1459 'DELETE_RESOURCE_TEAM_MEMBERS',
1460 'A',
1461 'V')
1462 THEN
1463
1464 jtf_rs_team_member_vuhk.delete_team_members_post(
1465 p_team_id => l_team_id,
1466 p_team_resource_id => l_team_resource_id,
1467 p_resource_type => l_resource_type,
1468 x_return_status => x_return_status);
1469
1470 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1471
1472 -- dbms_output.put_line('Returned Error status from the Post Vertical User Hook');
1473
1474 x_return_status := fnd_api.g_ret_sts_unexp_error;
1475
1476 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
1477 fnd_msg_pub.add;
1478
1479 RAISE fnd_api.g_exc_unexpected_error;
1480
1481 END IF;
1482
1483 END IF;
1484 END IF;
1485
1486
1487 /* Post Call to the Internal Type User Hook */
1488
1489 IF jtf_resource_utl.ok_to_execute(
1490 'JTF_RS_TEAM_MEMBERS_PVT',
1491 'DELETE_RESOURCE_TEAM_MEMBERS',
1492 'A',
1493 'I')
1494 THEN
1495 IF jtf_usr_hks.ok_to_execute(
1496 'JTF_RS_TEAM_MEMBERS_PVT',
1497 'DELETE_RESOURCE_TEAM_MEMBERS',
1498 'A',
1499 'I')
1500 THEN
1501
1502 jtf_rs_team_member_iuhk.delete_team_members_post(
1503 p_team_id => l_team_id,
1504 p_team_resource_id => l_team_resource_id,
1505 p_resource_type => l_resource_type,
1506 x_return_status => x_return_status);
1507
1508 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1509
1510 -- dbms_output.put_line('Returned Error status from the Post Customer User Hook');
1511
1512 x_return_status := fnd_api.g_ret_sts_unexp_error;
1513
1514 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1515 fnd_msg_pub.add;
1516
1517 RAISE fnd_api.g_exc_unexpected_error;
1518
1519 END IF;
1520
1521 END IF;
1522 END IF;
1523
1524
1525 /* Standard call for Message Generation */
1526
1527 IF jtf_resource_utl.ok_to_execute(
1528 'JTF_RS_TEAM_MEMBERS_PVT',
1529 'DELETE_RESOURCE_TEAM_MEMBERS',
1530 'M',
1531 'M')
1532 THEN
1533 IF jtf_usr_hks.ok_to_execute(
1534 'JTF_RS_TEAM_MEMBERS_PVT',
1535 'DELETE_RESOURCE_TEAM_MEMBERS',
1536 'M',
1537 'M')
1538 THEN
1539
1540 IF (jtf_rs_team_member_cuhk.ok_to_generate_msg(
1541 p_team_member_id => l_team_member_id,
1542 x_return_status => x_return_status) )
1543 THEN
1544
1545 /* Get the bind data id for the Business Object Instance */
1546
1547 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1548
1549
1550 /* Set bind values for the bind variables in the Business Object SQL */
1551
1552 jtf_usr_hks.load_bind_data(l_bind_data_id, 'team_member_id', l_team_member_id, 'S', 'N');
1553
1554
1555 /* Call the message generation API */
1556
1557 jtf_usr_hks.generate_message(
1558 p_prod_code => 'RS',
1559 p_bus_obj_code => 'TMBR',
1560 p_action_code => 'D',
1561 p_bind_data_id => l_bind_data_id,
1562 x_return_code => x_return_status);
1563
1564
1565 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1566
1567 -- dbms_output.put_line('Returned Error status from the Message Generation API');
1568
1569 x_return_status := fnd_api.g_ret_sts_unexp_error;
1570
1571 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1572 fnd_msg_pub.add;
1573
1574 RAISE fnd_api.g_exc_unexpected_error;
1575
1576 END IF;
1577
1578 END IF;
1579
1580 END IF;
1581 END IF;
1582
1583
1584 IF fnd_api.to_boolean(p_commit) THEN
1585
1586 COMMIT WORK;
1587
1588 END IF;
1589
1590 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1591
1592
1593 EXCEPTION
1594
1595
1596 WHEN fnd_api.g_exc_unexpected_error THEN
1597
1598 -- DBMS_OUTPUT.put_line (' ========================================== ');
1599
1600 -- DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error ======= ======== ');
1601
1602 ROLLBACK TO delete_resource_member_pvt;
1603
1604 x_return_status := fnd_api.g_ret_sts_unexp_error;
1605
1606 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1607
1608
1609 WHEN OTHERS THEN
1610
1611 -- DBMS_OUTPUT.put_line (' ========================================== ');
1612
1613 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Delete Team Member Pvt ============= ');
1614
1615 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
1616
1617 ROLLBACK TO delete_resource_member_pvt;
1618
1619 x_return_status := fnd_api.g_ret_sts_unexp_error;
1620
1621 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1622
1623
1624 END delete_resource_team_members;
1625
1626
1627
1628 END jtf_rs_team_members_pvt;