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