1 PACKAGE BODY jtf_rs_groups_pvt AS
2 /* $Header: jtfrsvgb.pls 120.0 2005/05/11 08:23:00 appldev ship $ */
3
4 /*****************************************************************************************
5 This package body defines the procedures for managing resource groups.
6 Its main procedures are as following:
7 Create Resource Group Members
8 Update Resource Group 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_GROUPS_PVT';
16
17
18 /* Procedure to create the resource group and the members
19 based on input values passed by calling routines. */
20
21 PROCEDURE create_resource_group
22 (P_API_VERSION IN NUMBER,
23 P_INIT_MSG_LIST IN VARCHAR2,
24 P_COMMIT IN VARCHAR2,
25 P_GROUP_NAME IN JTF_RS_GROUPS_VL.GROUP_NAME%TYPE,
26 P_GROUP_DESC IN JTF_RS_GROUPS_VL.GROUP_DESC%TYPE,
27 P_EXCLUSIVE_FLAG IN JTF_RS_GROUPS_VL.EXCLUSIVE_FLAG%TYPE,
28 P_EMAIL_ADDRESS IN JTF_RS_GROUPS_VL.EMAIL_ADDRESS%TYPE,
29 P_START_DATE_ACTIVE IN JTF_RS_GROUPS_VL.START_DATE_ACTIVE%TYPE,
30 P_END_DATE_ACTIVE IN JTF_RS_GROUPS_VL.END_DATE_ACTIVE%TYPE,
31 P_ACCOUNTING_CODE IN JTF_RS_GROUPS_VL.ACCOUNTING_CODE%TYPE,
32 P_ATTRIBUTE1 IN JTF_RS_GROUPS_VL.ATTRIBUTE1%TYPE,
33 P_ATTRIBUTE2 IN JTF_RS_GROUPS_VL.ATTRIBUTE2%TYPE,
34 P_ATTRIBUTE3 IN JTF_RS_GROUPS_VL.ATTRIBUTE3%TYPE,
35 P_ATTRIBUTE4 IN JTF_RS_GROUPS_VL.ATTRIBUTE4%TYPE,
36 P_ATTRIBUTE5 IN JTF_RS_GROUPS_VL.ATTRIBUTE5%TYPE,
37 P_ATTRIBUTE6 IN JTF_RS_GROUPS_VL.ATTRIBUTE6%TYPE,
38 P_ATTRIBUTE7 IN JTF_RS_GROUPS_VL.ATTRIBUTE7%TYPE,
39 P_ATTRIBUTE8 IN JTF_RS_GROUPS_VL.ATTRIBUTE8%TYPE,
40 P_ATTRIBUTE9 IN JTF_RS_GROUPS_VL.ATTRIBUTE9%TYPE,
41 P_ATTRIBUTE10 IN JTF_RS_GROUPS_VL.ATTRIBUTE10%TYPE,
42 P_ATTRIBUTE11 IN JTF_RS_GROUPS_VL.ATTRIBUTE11%TYPE,
43 P_ATTRIBUTE12 IN JTF_RS_GROUPS_VL.ATTRIBUTE12%TYPE,
44 P_ATTRIBUTE13 IN JTF_RS_GROUPS_VL.ATTRIBUTE13%TYPE,
45 P_ATTRIBUTE14 IN JTF_RS_GROUPS_VL.ATTRIBUTE14%TYPE,
46 P_ATTRIBUTE15 IN JTF_RS_GROUPS_VL.ATTRIBUTE15%TYPE,
47 P_ATTRIBUTE_CATEGORY IN JTF_RS_GROUPS_VL.ATTRIBUTE_CATEGORY%TYPE,
48 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
49 X_MSG_COUNT OUT NOCOPY NUMBER,
50 X_MSG_DATA OUT NOCOPY VARCHAR2,
51 X_GROUP_ID OUT NOCOPY JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
52 X_GROUP_NUMBER OUT NOCOPY JTF_RS_GROUPS_VL.GROUP_NUMBER%TYPE
53 ) IS
54
55 l_api_version CONSTANT NUMBER := 1.0;
56 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_GROUP';
57 l_rowid ROWID;
58 l_group_name jtf_rs_groups_vl.group_name%TYPE := p_group_name;
59 l_group_desc jtf_rs_groups_vl.group_desc%TYPE := p_group_desc;
60 l_exclusive_flag jtf_rs_groups_vl.exclusive_flag%TYPE := p_exclusive_flag;
61 l_email_address jtf_rs_groups_vl.email_address%TYPE := p_email_address;
62 l_start_date_active jtf_rs_groups_vl.start_date_active%TYPE := trunc(p_start_date_active);
63 l_end_date_active jtf_rs_groups_vl.end_date_active%TYPE := trunc(p_end_date_active);
64 l_accounting_code jtf_rs_groups_vl.accounting_code%TYPE := p_accounting_code;
65 l_group_id jtf_rs_groups_vl.group_id%TYPE;
66 l_group_number jtf_rs_groups_vl.group_number%TYPE;
67 l_check_char VARCHAR2(1);
68 l_check_dup_id VARCHAR2(1);
69 l_bind_data_id NUMBER;
70 l_return_status VARCHAR2(1);
71 l_msg_data VARCHAR2(2000);
72 l_msg_count NUMBER;
73
74 CURSOR c_jtf_rs_groups( l_rowid IN ROWID ) IS
75 SELECT 'Y'
76 FROM jtf_rs_groups_b
77 WHERE ROWID = l_rowid;
78
79 CURSOR c_dup_group_id (l_group_id IN jtf_rs_groups_vl.group_id%type) IS
80 SELECT 'X'
81 FROM jtf_rs_groups_vl
82 WHERE group_id = l_group_id;
83
84 BEGIN
85
86 SAVEPOINT create_resource_group_pvt;
87
88 x_return_status := fnd_api.g_ret_sts_success;
89
90 -- DBMS_OUTPUT.put_line(' Started Create Resource Group Pvt ');
91
92
93 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
94
95 RAISE fnd_api.g_exc_unexpected_error;
96
97 END IF;
98
99
100 IF fnd_api.to_boolean(p_init_msg_list) THEN
101
102 fnd_msg_pub.initialize;
103
104 END IF;
105
106
107 /* Make the pre processing call to the user hooks */
108
109 /* Pre Call to the Customer Type User Hook */
110
111 IF jtf_resource_utl.ok_to_execute(
112 'JTF_RS_GROUPS_PVT',
113 'CREATE_RESOURCE_GROUP',
114 'B',
115 'C')
116 THEN
117 IF jtf_usr_hks.ok_to_execute(
118 'JTF_RS_GROUPS_PVT',
119 'CREATE_RESOURCE_GROUP',
120 'B',
121 'C')
122 THEN
123
124 jtf_rs_resource_group_cuhk.create_resource_group_pre(
125 p_group_name => l_group_name,
126 p_group_desc => l_group_desc,
127 p_exclusive_flag => l_exclusive_flag,
128 p_email_address => l_email_address,
129 p_start_date_active => l_start_date_active,
130 p_end_date_active => l_end_date_active,
131 p_accounting_code => l_accounting_code,
132 x_return_status => x_return_status);
133
134 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
135
136 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
137 fnd_msg_pub.add;
138 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
139 RAISE FND_API.G_EXC_ERROR;
140 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
141 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
142 END IF;
143
144 END IF;
145
146 END IF;
147 END IF;
148
149
150 /* Pre Call to the Vertical Type User Hook */
151
152 IF jtf_resource_utl.ok_to_execute(
153 'JTF_RS_GROUPS_PVT',
154 'CREATE_RESOURCE_GROUP',
155 'B',
156 'V')
157 THEN
158 IF jtf_usr_hks.ok_to_execute(
159 'JTF_RS_GROUPS_PVT',
160 'CREATE_RESOURCE_GROUP',
161 'B',
162 'V')
163 THEN
164
165 jtf_rs_resource_group_vuhk.create_resource_group_pre(
166 p_group_name => l_group_name,
167 p_group_desc => l_group_desc,
168 p_exclusive_flag => l_exclusive_flag,
169 p_email_address => l_email_address,
170 p_start_date_active => l_start_date_active,
171 p_end_date_active => l_end_date_active,
172 p_accounting_code => l_accounting_code,
173 x_return_status => x_return_status);
174
175 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
176
177 -- dbms_output.put_line('Returned Error status from the Pre Vertical User Hook');
178
179 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
180 fnd_msg_pub.add;
181
182 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
183 RAISE FND_API.G_EXC_ERROR;
184 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
186 END IF;
187
188 END IF;
189
190 END IF;
191 END IF;
192
193
194 /* Pre Call to the Internal Type User Hook */
195
196 IF jtf_resource_utl.ok_to_execute(
197 'JTF_RS_GROUPS_PVT',
198 'CREATE_RESOURCE_GROUP',
199 'B',
200 'I')
201 THEN
202 IF jtf_usr_hks.ok_to_execute(
203 'JTF_RS_GROUPS_PVT',
204 'CREATE_RESOURCE_GROUP',
205 'B',
206 'I')
207 THEN
208
209 jtf_rs_resource_group_iuhk.create_resource_group_pre(
210 p_group_name => l_group_name,
211 p_group_desc => l_group_desc,
212 p_exclusive_flag => l_exclusive_flag,
213 p_email_address => l_email_address,
214 p_start_date_active => l_start_date_active,
215 p_end_date_active => l_end_date_active,
216 p_accounting_code => l_accounting_code,
217 x_return_status => x_return_status);
218
219 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
220
221 -- dbms_output.put_line('Returned Error status from the Pre Customer User Hook');
222
223
224 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
225 fnd_msg_pub.add;
226
227 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
228 RAISE FND_API.G_EXC_ERROR;
229 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
230 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
231 END IF;
232
233 END IF;
234
235 END IF;
236 END IF;
237
238
239 /* Validate the Input Dates */
240
241 jtf_resource_utl.validate_input_dates(
242 p_start_date_active => l_start_date_active,
243 p_end_date_active => l_end_date_active,
244 x_return_status => x_return_status
245 );
246
247 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
248
249 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
250 RAISE FND_API.G_EXC_ERROR;
251 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253 END IF;
254
255 END IF;
256
257 /* This portion of the code was modified to accomodate the calls to Migration API */
258 /* Check if the Global Variable Flag for Group ID is Y or N */
259
260 -- dbms_output.put_line ('Before checkin the Global flag in PVT API');
261
262 IF G_RS_GRP_ID_PVT_FLAG = 'Y' THEN
263
264 /* Get the next value of the Group ID from the sequence. */
265
266 LOOP
267 SELECT jtf_rs_groups_s.nextval
268 INTO l_group_id
269 FROM dual;
270 --dbms_output.put_line ('After Select - Group ID ' || l_group_id);
271
272 OPEN c_dup_group_id (l_group_id);
273 FETCH c_dup_group_id INTO l_check_dup_id;
274 EXIT WHEN c_dup_group_id%NOTFOUND;
275 CLOSE c_dup_group_id;
276 END LOOP;
277 CLOSE c_dup_group_id;
278
279 ELSE
280 l_group_id := JTF_RS_GROUPS_PUB.G_GROUP_ID;
281
282 END IF;
283
284 /* Get the next value of the Group number from the sequence. */
285
286 SELECT jtf_rs_group_number_s.nextval
287 INTO l_group_number
288 FROM dual;
289
290 /* Make a call to the group Audit API */
291
292 jtf_rs_groups_aud_pvt.insert_group
293 (P_API_VERSION => 1,
294 P_INIT_MSG_LIST => fnd_api.g_false,
295 P_COMMIT => fnd_api.g_false,
296 P_GROUP_ID => l_group_id,
297 P_GROUP_NUMBER => l_group_number,
298 P_GROUP_NAME => l_group_name,
299 P_GROUP_DESC => l_group_desc,
300 P_EXCLUSIVE_FLAG => l_exclusive_flag,
301 P_EMAIL_ADDRESS => l_email_address,
302 P_START_DATE_ACTIVE => l_start_date_active,
303 P_END_DATE_ACTIVE => l_end_date_active,
304 P_ACCOUNTING_CODE => l_accounting_code,
305 P_OBJECT_VERSION_NUMBER => 1,
306 X_RETURN_STATUS => x_return_status,
307 X_MSG_COUNT => x_msg_count,
308 X_MSG_DATA => x_msg_data
309 );
310
311 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
312
313 -- dbms_output.put_line('Failed status from call to audit procedure');
314
315 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
316 RAISE FND_API.G_EXC_ERROR;
317 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
319 END IF;
320
321
322 END IF;
323
324
325 /* Insert the row into the table by calling the table handler. */
326
327 jtf_rs_groups_pkg.insert_row(
328 x_rowid => l_rowid,
329 x_group_id => l_group_id,
330 x_group_number => l_group_number,
331 x_exclusive_flag => l_exclusive_flag,
332 x_email_address => l_email_address,
333 x_start_date_active => l_start_date_active,
334 x_end_date_active => l_end_date_active,
335 x_group_name => l_group_name,
336 x_group_desc => l_group_desc,
337 x_accounting_code => l_accounting_code,
338 x_attribute1 => p_attribute1,
339 x_attribute2 => p_attribute2,
340 x_attribute3 => p_attribute3,
341 x_attribute4 => p_attribute4,
342 x_attribute5 => p_attribute5,
343 x_attribute6 => p_attribute6,
344 x_attribute7 => p_attribute7,
345 x_attribute8 => p_attribute8,
346 x_attribute9 => p_attribute9,
347 x_attribute10 => p_attribute10,
348 x_attribute11 => p_attribute11,
349 x_attribute12 => p_attribute12,
350 x_attribute13 => p_attribute13,
351 x_attribute14 => p_attribute14,
352 x_attribute15 => p_attribute15,
353 x_attribute_category => p_attribute_category,
354 x_creation_date => SYSDATE,
355 x_created_by => jtf_resource_utl.created_by,
356 x_last_update_date => SYSDATE,
357 x_last_updated_by => jtf_resource_utl.updated_by,
358 x_last_update_login => jtf_resource_utl.login_id
359 );
360
361
362 -- dbms_output.put_line('Inserted Row');
363
364 OPEN c_jtf_rs_groups(l_rowid);
365
366 FETCH c_jtf_rs_groups INTO l_check_char;
367
368
369 IF c_jtf_rs_groups%NOTFOUND THEN
370
371 -- dbms_output.put_line('Error in Table Handler');
372
373 IF c_jtf_rs_groups%ISOPEN THEN
374
375 CLOSE c_jtf_rs_groups;
376
377 END IF;
378
379
380 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
381 fnd_msg_pub.add;
382
383 RAISE fnd_api.g_exc_error;
384
385
386 ELSE
387
388 -- dbms_output.put_line('Group Successfully Created');
389
390 x_group_id := l_group_id;
391
392 x_group_number := l_group_number;
393
394 END IF;
395
396
397 /* Close the cursors */
398
399 IF c_jtf_rs_groups%ISOPEN THEN
400
401 CLOSE c_jtf_rs_groups;
402
403 END IF;
404
405
406 /* Make a call to the Group Denorm API */
407
408 jtf_rs_group_denorm_pvt.create_res_groups
409 (P_API_VERSION => 1,
410 P_INIT_MSG_LIST => fnd_api.g_false,
411 P_COMMIT => fnd_api.g_false,
412 P_GROUP_ID => l_group_id,
413 X_RETURN_STATUS => x_return_status,
414 X_MSG_COUNT => x_msg_count,
415 X_MSG_DATA => x_msg_data
416 );
417
418 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
419
420 -- dbms_output.put_line('Failed status from call to Group Denorm procedure');
421
422 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
423 RAISE FND_API.G_EXC_ERROR;
424 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
426 END IF;
427
428 END IF;
429
430
431
432 /* Make the post processing call to the user hooks */
433
434 /* Post Call to the Customer Type User Hook */
435
436 IF jtf_usr_hks.ok_to_execute(
437 'JTF_RS_GROUPS_PVT',
438 'CREATE_RESOURCE_GROUP',
439 'A',
440 'C')
441 THEN
442
443 jtf_rs_resource_group_cuhk.create_resource_group_post(
444 p_group_id => l_group_id,
445 p_group_name => l_group_name,
446 p_group_desc => l_group_desc,
447 p_exclusive_flag => l_exclusive_flag,
448 p_email_address => l_email_address,
449 p_start_date_active => l_start_date_active,
450 p_end_date_active => l_end_date_active,
451 p_accounting_code => l_accounting_code,
452 x_return_status => x_return_status);
453
454 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
455
456 -- dbms_output.put_line('Returned Error status from the Post Customer User Hook');
457
458
459 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
460 fnd_msg_pub.add;
461
462 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
463 RAISE FND_API.G_EXC_ERROR;
464 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
465 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
466 END IF;
467
468 END IF;
469
470 END IF;
471
472
473 /* Post Call to the Vertical Type User Hook */
474
475 IF jtf_usr_hks.ok_to_execute(
476 'JTF_RS_GROUPS_PVT',
477 'CREATE_RESOURCE_GROUP',
478 'A',
479 'V')
480 THEN
481
482 jtf_rs_resource_group_vuhk.create_resource_group_post(
483 p_group_id => l_group_id,
484 p_group_name => l_group_name,
485 p_group_desc => l_group_desc,
486 p_exclusive_flag => l_exclusive_flag,
487 p_email_address => l_email_address,
488 p_start_date_active => l_start_date_active,
489 p_end_date_active => l_end_date_active,
490 p_accounting_code => l_accounting_code,
491 x_return_status => x_return_status);
492
493 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
494
495 -- dbms_output.put_line('Returned Error status from the Post Vertical User Hook');
496
497
498 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
499 fnd_msg_pub.add;
500
501 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
502 RAISE FND_API.G_EXC_ERROR;
503 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
505 END IF;
506
507
508 END IF;
509
510 END IF;
511
512
513 /* Post Call to the Internal Type User Hook */
514
515 IF jtf_usr_hks.ok_to_execute(
516 'JTF_RS_GROUPS_PVT',
517 'CREATE_RESOURCE_GROUP',
518 'A',
519 'I')
520 THEN
521
522 jtf_rs_resource_group_iuhk.create_resource_group_post(
523 p_group_id => l_group_id,
524 p_group_name => l_group_name,
525 p_group_desc => l_group_desc,
526 p_exclusive_flag => l_exclusive_flag,
527 p_email_address => l_email_address,
528 p_start_date_active => l_start_date_active,
529 p_end_date_active => l_end_date_active,
530 p_accounting_code => l_accounting_code,
531 x_return_status => x_return_status);
532
533 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
534
535 -- dbms_output.put_line('Returned Error status from the Post Customer User Hook');
536
537
538 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
539 fnd_msg_pub.add;
540
541 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
542 RAISE FND_API.G_EXC_ERROR;
543 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
545 END IF;
546
547 END IF;
548
549 END IF;
550
551
552 /* Standard call for Message Generation */
553
554 IF jtf_usr_hks.ok_to_execute(
555 'JTF_RS_GROUPS_PVT',
556 'CREATE_RESOURCE_GROUP',
557 'M',
558 'M')
559 THEN
560
561 IF (jtf_rs_resource_group_cuhk.ok_to_generate_msg(
562 p_group_id => l_group_id,
563 x_return_status => x_return_status) )
564 THEN
565
566 /* Get the bind data id for the Business Object Instance */
567
568 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
569
570
571 /* Set bind values for the bind variables in the Business Object SQL */
572
573 jtf_usr_hks.load_bind_data(l_bind_data_id, 'group_id', l_group_id, 'S', 'N');
574
575
576 /* Call the message generation API */
577
578 jtf_usr_hks.generate_message(
579 p_prod_code => 'JTF',
580 p_bus_obj_code => 'JTF_GRP',
581 p_action_code => 'I',
582 p_bind_data_id => l_bind_data_id,
583 x_return_code => x_return_status);
584
585
586 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
587
588 -- dbms_output.put_line('Returned Error status from the Message Generation API');
589
590
591 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
592 fnd_msg_pub.add;
593
594 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
595 RAISE FND_API.G_EXC_ERROR;
596 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
598 END IF;
599
600 END IF;
601
602 END IF;
603
604 END IF;
605
606 -- create the wf roles with new resource group
607 -- Don't care for its success status
608 BEGIN
609 jtf_rs_wf_integration_pub.create_resource_group
610 (P_API_VERSION => 1.0,
611 P_GROUP_ID => l_group_id,
612 P_GROUP_NAME => l_group_name,
613 P_EMAIL_ADDRESS => l_email_address,
614 P_START_DATE_ACTIVE => l_start_date_active,
615 P_END_DATE_ACTIVE => l_end_date_active,
616 X_RETURN_STATUS => l_return_status,
617 X_MSG_COUNT => l_msg_count,
618 X_MSG_DATA => l_msg_data);
619 EXCEPTION
620 WHEN OTHERS THEN
621 NULL;
622 END;
623
624
625 IF fnd_api.to_boolean(p_commit) THEN
626
627 COMMIT WORK;
628
629 END IF;
630
631 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
632
633 EXCEPTION
634
635
636 WHEN fnd_api.g_exc_error THEN
637 ROLLBACK TO create_resource_group_pvt;
638 x_return_status := fnd_api.g_ret_sts_error;
639 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
640 p_data => x_msg_data);
641 WHEN fnd_api.g_exc_unexpected_error THEN
642 ROLLBACK TO create_resource_group_pvt;
643 x_return_status := fnd_api.g_ret_sts_unexp_error;
644 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
645 p_data => x_msg_data);
646 WHEN OTHERS THEN
647 ROLLBACK TO create_resource_group_pvt;
648 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
649 fnd_message.set_token('P_SQLCODE',SQLCODE);
650 fnd_message.set_token('P_SQLERRM',SQLERRM);
651 fnd_message.set_token('P_API_NAME', l_api_name);
652 FND_MSG_PUB.add;
653 x_return_status := fnd_api.g_ret_sts_unexp_error;
654 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
655 p_data => x_msg_data);
656
657 END create_resource_group;
658
659 PROCEDURE create_resource_group_migrate
660 (P_API_VERSION IN NUMBER,
661 P_INIT_MSG_LIST IN VARCHAR2,
662 P_COMMIT IN VARCHAR2,
663 P_GROUP_NAME IN JTF_RS_GROUPS_VL.GROUP_NAME%TYPE,
664 P_GROUP_DESC IN JTF_RS_GROUPS_VL.GROUP_DESC%TYPE,
665 P_EXCLUSIVE_FLAG IN JTF_RS_GROUPS_VL.EXCLUSIVE_FLAG%TYPE,
666 P_EMAIL_ADDRESS IN JTF_RS_GROUPS_VL.EMAIL_ADDRESS%TYPE,
667 P_START_DATE_ACTIVE IN JTF_RS_GROUPS_VL.START_DATE_ACTIVE%TYPE,
668 P_END_DATE_ACTIVE IN JTF_RS_GROUPS_VL.END_DATE_ACTIVE%TYPE,
669 P_ACCOUNTING_CODE IN JTF_RS_GROUPS_VL.ACCOUNTING_CODE%TYPE,
670 P_ATTRIBUTE1 IN JTF_RS_GROUPS_VL.ATTRIBUTE1%TYPE,
671 P_ATTRIBUTE2 IN JTF_RS_GROUPS_VL.ATTRIBUTE2%TYPE,
672 P_ATTRIBUTE3 IN JTF_RS_GROUPS_VL.ATTRIBUTE3%TYPE,
673 P_ATTRIBUTE4 IN JTF_RS_GROUPS_VL.ATTRIBUTE4%TYPE,
674 P_ATTRIBUTE5 IN JTF_RS_GROUPS_VL.ATTRIBUTE5%TYPE,
675 P_ATTRIBUTE6 IN JTF_RS_GROUPS_VL.ATTRIBUTE6%TYPE,
676 P_ATTRIBUTE7 IN JTF_RS_GROUPS_VL.ATTRIBUTE7%TYPE,
677 P_ATTRIBUTE8 IN JTF_RS_GROUPS_VL.ATTRIBUTE8%TYPE,
678 P_ATTRIBUTE9 IN JTF_RS_GROUPS_VL.ATTRIBUTE9%TYPE,
679 P_ATTRIBUTE10 IN JTF_RS_GROUPS_VL.ATTRIBUTE10%TYPE,
680 P_ATTRIBUTE11 IN JTF_RS_GROUPS_VL.ATTRIBUTE11%TYPE,
681 P_ATTRIBUTE12 IN JTF_RS_GROUPS_VL.ATTRIBUTE12%TYPE,
682 P_ATTRIBUTE13 IN JTF_RS_GROUPS_VL.ATTRIBUTE13%TYPE,
683 P_ATTRIBUTE14 IN JTF_RS_GROUPS_VL.ATTRIBUTE14%TYPE,
684 P_ATTRIBUTE15 IN JTF_RS_GROUPS_VL.ATTRIBUTE15%TYPE,
685 P_ATTRIBUTE_CATEGORY IN JTF_RS_GROUPS_VL.ATTRIBUTE_CATEGORY%TYPE,
686 P_GROUP_ID IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
687 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
688 X_MSG_COUNT OUT NOCOPY NUMBER,
689 X_MSG_DATA OUT NOCOPY VARCHAR2,
690 X_GROUP_ID OUT NOCOPY JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
691 X_GROUP_NUMBER OUT NOCOPY JTF_RS_GROUPS_VL.GROUP_NUMBER%TYPE
692 ) IS
693
694 BEGIN
695
696 G_RS_GRP_ID_PVT_FLAG := 'N';
697
698 jtf_rs_groups_pvt.create_resource_group (
699 P_API_VERSION => P_API_VERSION,
700 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
701 P_COMMIT => P_COMMIT,
702 P_GROUP_NAME => P_GROUP_NAME,
703 P_GROUP_DESC => P_GROUP_DESC,
704 P_EXCLUSIVE_FLAG => P_EXCLUSIVE_FLAG,
705 P_EMAIL_ADDRESS => P_EMAIL_ADDRESS,
706 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
707 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
708 P_ACCOUNTING_CODE => P_ACCOUNTING_CODE,
709 P_ATTRIBUTE1 => P_ATTRIBUTE1,
710 P_ATTRIBUTE2 => P_ATTRIBUTE2,
711 P_ATTRIBUTE3 => P_ATTRIBUTE3,
712 P_ATTRIBUTE4 => P_ATTRIBUTE4,
713 P_ATTRIBUTE5 => P_ATTRIBUTE5,
714 P_ATTRIBUTE6 => P_ATTRIBUTE6,
715 P_ATTRIBUTE7 => P_ATTRIBUTE7,
716 P_ATTRIBUTE8 => P_ATTRIBUTE8,
717 P_ATTRIBUTE9 => P_ATTRIBUTE9,
718 P_ATTRIBUTE10 => P_ATTRIBUTE10,
719 P_ATTRIBUTE11 => P_ATTRIBUTE11,
720 P_ATTRIBUTE12 => P_ATTRIBUTE12,
721 P_ATTRIBUTE13 => P_ATTRIBUTE13,
722 P_ATTRIBUTE14 => P_ATTRIBUTE14,
723 P_ATTRIBUTE15 => P_ATTRIBUTE15,
724 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
725 X_RETURN_STATUS => X_RETURN_STATUS,
726 X_MSG_COUNT => X_MSG_COUNT,
727 X_MSG_DATA => X_MSG_DATA,
728 X_GROUP_ID => X_GROUP_ID,
729 X_GROUP_NUMBER => X_GROUP_NUMBER
730 );
731
732 END create_resource_group_migrate;
733
734 /* Procedure to update the resource group based on input values
735 passed by calling routines. */
736
737 PROCEDURE update_resource_group
738 (P_API_VERSION IN NUMBER,
739 P_INIT_MSG_LIST IN VARCHAR2,
740 P_COMMIT IN VARCHAR2,
741 P_GROUP_ID IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
742 P_GROUP_NAME IN JTF_RS_GROUPS_VL.GROUP_NAME%TYPE,
743 P_GROUP_DESC IN JTF_RS_GROUPS_VL.GROUP_DESC%TYPE,
744 P_EXCLUSIVE_FLAG IN JTF_RS_GROUPS_VL.EXCLUSIVE_FLAG%TYPE,
745 P_EMAIL_ADDRESS IN JTF_RS_GROUPS_VL.EMAIL_ADDRESS%TYPE,
746 P_START_DATE_ACTIVE IN JTF_RS_GROUPS_VL.START_DATE_ACTIVE%TYPE,
747 P_END_DATE_ACTIVE IN JTF_RS_GROUPS_VL.END_DATE_ACTIVE%TYPE,
748 P_ACCOUNTING_CODE IN JTF_RS_GROUPS_VL.ACCOUNTING_CODE%TYPE,
749 P_ATTRIBUTE1 IN JTF_RS_GROUPS_VL.ATTRIBUTE1%TYPE,
750 P_ATTRIBUTE2 IN JTF_RS_GROUPS_VL.ATTRIBUTE2%TYPE,
751 P_ATTRIBUTE3 IN JTF_RS_GROUPS_VL.ATTRIBUTE3%TYPE,
752 P_ATTRIBUTE4 IN JTF_RS_GROUPS_VL.ATTRIBUTE4%TYPE,
753 P_ATTRIBUTE5 IN JTF_RS_GROUPS_VL.ATTRIBUTE5%TYPE,
754 P_ATTRIBUTE6 IN JTF_RS_GROUPS_VL.ATTRIBUTE6%TYPE,
755 P_ATTRIBUTE7 IN JTF_RS_GROUPS_VL.ATTRIBUTE7%TYPE,
756 P_ATTRIBUTE8 IN JTF_RS_GROUPS_VL.ATTRIBUTE8%TYPE,
757 P_ATTRIBUTE9 IN JTF_RS_GROUPS_VL.ATTRIBUTE9%TYPE,
758 P_ATTRIBUTE10 IN JTF_RS_GROUPS_VL.ATTRIBUTE10%TYPE,
759 P_ATTRIBUTE11 IN JTF_RS_GROUPS_VL.ATTRIBUTE11%TYPE,
760 P_ATTRIBUTE12 IN JTF_RS_GROUPS_VL.ATTRIBUTE12%TYPE,
761 P_ATTRIBUTE13 IN JTF_RS_GROUPS_VL.ATTRIBUTE13%TYPE,
762 P_ATTRIBUTE14 IN JTF_RS_GROUPS_VL.ATTRIBUTE14%TYPE,
763 P_ATTRIBUTE15 IN JTF_RS_GROUPS_VL.ATTRIBUTE15%TYPE,
764 P_ATTRIBUTE_CATEGORY IN JTF_RS_GROUPS_VL.ATTRIBUTE_CATEGORY%TYPE,
765 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_GROUPS_VL.OBJECT_VERSION_NUMBER%TYPE,
766 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
767 X_MSG_COUNT OUT NOCOPY NUMBER,
768 X_MSG_DATA OUT NOCOPY VARCHAR2
769 ) IS
770
771 l_api_version CONSTANT NUMBER := 1.0;
772 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_GROUP';
773 l_group_id jtf_rs_groups_vl.group_id%TYPE := p_group_id;
774 l_group_name jtf_rs_groups_vl.group_name%TYPE := p_group_name;
775 l_group_desc jtf_rs_groups_vl.group_desc%TYPE := p_group_desc;
776 l_exclusive_flag jtf_rs_groups_vl.exclusive_flag%TYPE := p_exclusive_flag;
777 l_email_address jtf_rs_groups_vl.email_address%TYPE := p_email_address;
778 l_start_date_active jtf_rs_groups_vl.start_date_active%TYPE := trunc(p_start_date_active);
779 l_end_date_active jtf_rs_groups_vl.end_date_active%TYPE := trunc(p_end_date_active);
780 l_accounting_code jtf_rs_groups_vl.accounting_code%TYPE := p_accounting_code;
781 l_object_version_num jtf_rs_groups_vl.object_version_number%type := p_object_version_num;
782
783 l_max_end_date DATE;
784 l_min_start_date DATE;
785 l_check_char VARCHAR2(1);
786 l_bind_data_id NUMBER;
787
788
789 CURSOR c_group_update(
790 l_group_id IN NUMBER )
791 IS
792 SELECT
793 group_number,
794 DECODE(p_group_name, fnd_api.g_miss_char, group_name, p_group_name) group_name,
795 DECODE(p_group_desc, fnd_api.g_miss_char, group_desc, p_group_desc) group_desc,
796 DECODE(p_exclusive_flag, fnd_api.g_miss_char, exclusive_flag, NULL, 'N', p_exclusive_flag) exclusive_flag,
797 DECODE(p_email_address, fnd_api.g_miss_char, email_address, p_email_address) email_address,
798 DECODE(p_start_date_active, fnd_api.g_miss_date, start_date_active, trunc(p_start_date_active)) start_date_active,
799 DECODE(p_end_date_active, fnd_api.g_miss_date, end_date_active, trunc(p_end_date_active)) end_date_active,
800 DECODE(p_accounting_code, fnd_api.g_miss_char, accounting_code, p_accounting_code) accounting_code,
801 DECODE(p_attribute1, fnd_api.g_miss_char, attribute1, p_attribute1) attribute1,
802 DECODE(p_attribute2, fnd_api.g_miss_char, attribute2, p_attribute2) attribute2,
803 DECODE(p_attribute3, fnd_api.g_miss_char, attribute3, p_attribute3) attribute3,
804 DECODE(p_attribute4, fnd_api.g_miss_char, attribute4, p_attribute4) attribute4,
805 DECODE(p_attribute5, fnd_api.g_miss_char, attribute5, p_attribute5) attribute5,
806 DECODE(p_attribute6, fnd_api.g_miss_char, attribute6, p_attribute6) attribute6,
807 DECODE(p_attribute7, fnd_api.g_miss_char, attribute7, p_attribute7) attribute7,
808 DECODE(p_attribute8, fnd_api.g_miss_char, attribute8, p_attribute8) attribute8,
809 DECODE(p_attribute9, fnd_api.g_miss_char, attribute9, p_attribute9) attribute9,
810 DECODE(p_attribute10, fnd_api.g_miss_char, attribute10, p_attribute10) attribute10,
811 DECODE(p_attribute11, fnd_api.g_miss_char, attribute11, p_attribute11) attribute11,
812 DECODE(p_attribute12, fnd_api.g_miss_char, attribute12, p_attribute12) attribute12,
813 DECODE(p_attribute13, fnd_api.g_miss_char, attribute13, p_attribute13) attribute13,
814 DECODE(p_attribute14, fnd_api.g_miss_char, attribute14, p_attribute14) attribute14,
815 DECODE(p_attribute15, fnd_api.g_miss_char, attribute15, p_attribute15) attribute15,
816 DECODE(p_attribute_category, fnd_api.g_miss_char, attribute_category, p_attribute_category) attribute_category
817 FROM jtf_rs_groups_vl
818 WHERE group_id = l_group_id;
819
820 group_rec c_group_update%ROWTYPE;
821
822
823 CURSOR c_related_role_dates_first(
824 l_group_id IN NUMBER )
825 IS
826 SELECT min(start_date_active),
827 max(end_date_active)
828 FROM jtf_rs_role_relations
829 WHERE role_resource_type = 'RS_GROUP'
830 AND role_resource_id = l_group_id
831 AND nvl(delete_flag, 'N') <> 'Y'
832 AND end_date_active is not null;
833
834
835 CURSOR c_related_role_dates_sec(
836 l_group_id IN NUMBER )
837 IS
838 SELECT min(start_date_active)
839 FROM jtf_rs_role_relations
840 WHERE role_resource_type = 'RS_GROUP'
841 AND role_resource_id = l_group_id
842 AND nvl(delete_flag, 'N') <> 'Y'
843 AND end_date_active is null;
844
845
846 CURSOR c_grp_mbr_role_dates_first(
847 l_group_id IN NUMBER )
848 IS
849 SELECT min(jrrr.start_date_active),
850 max(jrrr.end_date_active)
851 FROM jtf_rs_group_members jrgm,
852 jtf_rs_role_relations jrrr
853 WHERE jrgm.group_member_id = jrrr.role_resource_id
854 AND jrrr.role_resource_type = 'RS_GROUP_MEMBER'
855 AND nvl(jrrr.delete_flag, 'N') <> 'Y'
856 AND nvl(jrgm.delete_flag, 'N') <> 'Y'
857 AND jrgm.group_id = l_group_id
858 AND jrrr.end_date_active is not null;
859
860
861 CURSOR c_grp_mbr_role_dates_sec(
862 l_group_id IN NUMBER )
863 IS
864 SELECT min(jrrr.start_date_active)
865 FROM jtf_rs_group_members jrgm,
866 jtf_rs_role_relations jrrr
867 WHERE jrgm.group_member_id = jrrr.role_resource_id
868 AND jrrr.role_resource_type = 'RS_GROUP_MEMBER'
869 AND nvl(jrrr.delete_flag, 'N') <> 'Y'
870 AND nvl(jrgm.delete_flag, 'N') <> 'Y'
871 AND jrgm.group_id = l_group_id
872 AND jrrr.end_date_active is null;
873
874
875 CURSOR c_related_group_dates_first(
876 l_group_id IN NUMBER )
877 IS
878 SELECT min(start_date_active),
879 max(end_date_active)
880 FROM jtf_rs_grp_relations
881 WHERE ( group_id = l_group_id
882 OR related_group_id = l_group_id )
883 AND nvl(delete_flag, 'N') <> 'Y'
884 AND end_date_active is not null;
885
886
887 CURSOR c_related_group_dates_sec(
888 l_group_id IN NUMBER )
889 IS
890 SELECT min(start_date_active)
891 FROM jtf_rs_grp_relations
892 WHERE ( group_id = l_group_id
893 OR related_group_id = l_group_id )
894 AND nvl(delete_flag, 'N') <> 'Y'
895 AND end_date_active is null;
896
897
898 CURSOR c_team_mbr_role_dates_first(
899 l_group_id IN NUMBER )
900 IS
901 SELECT min(jrrr.start_date_active),
902 max(jrrr.end_date_active)
903 FROM jtf_rs_team_members jrtm,
904 jtf_rs_role_relations jrrr
905 WHERE jrtm.team_member_id = jrrr.role_resource_id
906 AND jrrr.role_resource_type = 'RS_TEAM_MEMBER'
907 AND nvl(jrrr.delete_flag, 'N') <> 'Y'
908 AND nvl(jrtm.delete_flag, 'N') <> 'Y'
909 AND jrtm.team_resource_id = l_group_id
910 AND jrtm.resource_type = 'RS_GROUP'
911 AND jrrr.end_date_active is not null;
912
913
914 CURSOR c_team_mbr_role_dates_sec(
915 l_group_id IN NUMBER )
916 IS
917 SELECT min(jrrr.start_date_active)
918 FROM jtf_rs_team_members jrtm,
919 jtf_rs_role_relations jrrr
920 WHERE jrtm.team_member_id = jrrr.role_resource_id
921 AND jrrr.role_resource_type = 'RS_TEAM_MEMBER'
922 AND nvl(jrrr.delete_flag, 'N') <> 'Y'
923 AND nvl(jrtm.delete_flag, 'N') <> 'Y'
924 AND jrtm.team_resource_id = l_group_id
925 AND jrtm.resource_type = 'RS_GROUP'
926 AND jrrr.end_date_active is null;
927
928
929 CURSOR c_exclusive_group_check(
930 l_group_id IN NUMBER )
931 IS
932 SELECT 'Y'
933 FROM jtf_rs_groups_vl G1,
934 jtf_rs_groups_vl G2,
935 jtf_rs_group_members GM1,
936 jtf_rs_group_members GM2,
937 jtf_rs_group_usages GU1,
938 jtf_rs_group_usages GU2,
939 jtf_rs_role_relations RR1,
940 jtf_rs_role_relations RR2
941 WHERE G1.group_id = GM1.group_id
942 AND G2.group_id = GM2.group_id
943 AND nvl(GM1.delete_flag, 'N') <> 'Y'
944 AND nvl(GM2.delete_flag, 'N') <> 'Y'
945 AND GM1.resource_id = GM2.resource_id
946 AND GM1.group_member_id = RR1.role_resource_id
947 AND GM2.group_member_id = RR2.role_resource_id
948 AND RR1.role_resource_type = 'RS_GROUP_MEMBER'
949 AND RR2.role_resource_type = 'RS_GROUP_MEMBER'
950 AND nvl(RR1.delete_flag, 'N') <> 'Y'
951 AND nvl(RR2.delete_flag, 'N') <> 'Y'
952 /*AND NOT (((RR2.end_date_active < RR1.start_date_active OR
953 RR2.start_date_active > RR1.end_date_active) AND
954 RR1.end_date_active IS NOT NULL)
955 OR (RR2.end_date_active < RR1.start_date_active AND
956 RR1.end_date_active IS NULL)) */
957 AND not (((nvl(RR1.end_date_active,RR2.start_date_active + 1) < RR2.start_date_active OR
958 RR1.start_date_active > RR2.end_date_active) AND
959 RR2.end_date_active IS NOT NULL)
960 OR ( nvl(RR1.end_date_active,RR2.start_date_active + 1) < RR2.start_date_active AND
961 RR2.end_date_active IS NULL ))
962 AND G2.exclusive_flag = 'Y'
963 AND GU1.group_id = G1.group_id
964 AND GU2.group_id = G2.group_id
965 AND GU1.usage = GU2.usage
966 AND G1.group_id <> G2.group_id
967 AND G1.group_id = l_group_id;
968
969
970 l_return_status VARCHAR2(1);
971 l_msg_data VARCHAR2(2000);
972 l_msg_count NUMBER;
973
974 BEGIN
975
976
977 SAVEPOINT update_resource_group_pvt;
978
979 x_return_status := fnd_api.g_ret_sts_success;
980
981 -- DBMS_OUTPUT.put_line(' Started Update Resource Group Pvt ');
982
983
984 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
985
986 RAISE fnd_api.g_exc_unexpected_error;
987
988 END IF;
989
990
991 IF fnd_api.to_boolean(p_init_msg_list) THEN
992
993 fnd_msg_pub.initialize;
994
995 END IF;
996
997 /* Make the pre processing call to the user hooks */
998
999 /* Pre Call to the Customer Type User Hook */
1000
1001 IF jtf_resource_utl.ok_to_execute(
1002 'JTF_RS_GROUPS_PVT',
1003 'UPDATE_RESOURCE_GROUP',
1004 'B',
1005 'C')
1006 THEN
1007 IF jtf_usr_hks.ok_to_execute(
1008 'JTF_RS_GROUPS_PVT',
1009 'UPDATE_RESOURCE_GROUP',
1010 'B',
1011 'C')
1012 THEN
1013
1014 jtf_rs_resource_group_cuhk.update_resource_group_pre(
1015 p_group_id => l_group_id,
1016 p_group_name => l_group_name,
1017 p_group_desc => l_group_desc,
1018 p_exclusive_flag => l_exclusive_flag,
1019 p_email_address => l_email_address,
1020 p_start_date_active => l_start_date_active,
1021 p_end_date_active => l_end_date_active,
1022 p_accounting_code => l_accounting_code,
1023 x_return_status => x_return_status);
1024
1025 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1026
1027 -- dbms_output.put_line('Returned Error status from the Pre Customer User Hook');
1028
1029 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
1030 fnd_msg_pub.add;
1031
1032 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1033 RAISE FND_API.G_EXC_ERROR;
1034 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1035 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1036 END IF;
1037
1038
1039 END IF;
1040
1041 END IF;
1042 END IF;
1043
1044
1045 /* Pre Call to the Vertical Type User Hook */
1046
1047 IF jtf_resource_utl.ok_to_execute(
1048 'JTF_RS_GROUPS_PVT',
1049 'UPDATE_RESOURCE_GROUP',
1050 'B',
1051 'V')
1052 THEN
1053 IF jtf_usr_hks.ok_to_execute(
1054 'JTF_RS_GROUPS_PVT',
1055 'UPDATE_RESOURCE_GROUP',
1056 'B',
1057 'V')
1058 THEN
1059
1060 jtf_rs_resource_group_vuhk.update_resource_group_pre(
1061 p_group_id => l_group_id,
1062 p_group_name => l_group_name,
1063 p_group_desc => l_group_desc,
1064 p_exclusive_flag => l_exclusive_flag,
1065 p_email_address => l_email_address,
1066 p_start_date_active => l_start_date_active,
1067 p_end_date_active => l_end_date_active,
1068 p_accounting_code => l_accounting_code,
1069 x_return_status => x_return_status);
1070
1071 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1072
1073 -- dbms_output.put_line('Returned Error status from the Pre Vertical User Hook');
1074
1075
1076 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
1077 fnd_msg_pub.add;
1078
1079 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1080 RAISE FND_API.G_EXC_ERROR;
1081 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1082 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1083 END IF;
1084
1085
1086 END IF;
1087
1088 END IF;
1089 END IF;
1090
1091
1092 /* Pre Call to the Internal Type User Hook */
1093
1094 IF jtf_resource_utl.ok_to_execute(
1095 'JTF_RS_GROUPS_PVT',
1096 'UPDATE_RESOURCE_GROUP',
1097 'B',
1098 'I')
1099 THEN
1100 IF jtf_usr_hks.ok_to_execute(
1101 'JTF_RS_GROUPS_PVT',
1102 'UPDATE_RESOURCE_GROUP',
1103 'B',
1104 'I')
1105 THEN
1106
1107 jtf_rs_resource_group_iuhk.update_resource_group_pre(
1108 p_group_id => l_group_id,
1109 p_group_name => l_group_name,
1110 p_group_desc => l_group_desc,
1111 p_exclusive_flag => l_exclusive_flag,
1112 p_email_address => l_email_address,
1113 p_start_date_active => l_start_date_active,
1114 p_end_date_active => l_end_date_active,
1115 p_accounting_code => l_accounting_code,
1116 x_return_status => x_return_status);
1117
1118 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1119
1120
1121 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
1122 fnd_msg_pub.add;
1123
1124 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1125 RAISE FND_API.G_EXC_ERROR;
1126 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1128 END IF;
1129
1130 END IF;
1131
1132 END IF;
1133 END IF;
1134
1135
1136
1137 OPEN c_group_update(l_group_id);
1138
1139 FETCH c_group_update INTO group_rec;
1140
1141
1142 IF c_group_update%NOTFOUND THEN
1143
1144 IF c_group_update%ISOPEN THEN
1145
1146 CLOSE c_group_update;
1147
1148 END IF;
1149
1150 fnd_message.set_name('JTF', 'JTF_RS_INVALID_GROUP');
1151 fnd_message.set_token('P_GROUP_ID', l_group_id);
1152 fnd_msg_pub.add;
1153
1154 RAISE fnd_api.g_exc_error;
1155
1156 END IF;
1157
1158 /* Validate that the Group Name is specified */
1159
1160 IF group_rec.group_name IS NULL THEN
1161
1162 -- dbms_output.put_line('Group Name cannot be null');
1163
1164 fnd_message.set_name('JTF', 'JTF_RS_GROUP_NAME_NULL');
1165 fnd_msg_pub.add;
1166
1167 RAISE fnd_api.g_exc_error;
1168
1169 END IF;
1170
1171
1172 l_start_date_active := group_rec.start_date_active;
1173 l_end_date_active := group_rec.end_date_active;
1174
1175
1176 /* Validate the Input Dates */
1177
1178 jtf_resource_utl.validate_input_dates(
1179 p_start_date_active => l_start_date_active,
1180 p_end_date_active => l_end_date_active,
1181 x_return_status => x_return_status
1182 );
1183
1184 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1185
1186 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1187 RAISE FND_API.G_EXC_ERROR;
1188 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1190 END IF;
1191
1192
1193 END IF;
1194
1195
1196
1197 /* Validate that the group dates cover the role related dates for the
1198 group */
1199
1200 /* First part of the validation where the role relate end date active
1201 is not null */
1202
1203 OPEN c_related_role_dates_first(l_group_id);
1204
1205 FETCH c_related_role_dates_first INTO l_min_start_date, l_max_end_date;
1206
1207
1208 IF l_min_start_date IS NOT NULL THEN
1209
1210 IF l_min_start_date < l_start_date_active THEN
1211
1212 fnd_message.set_name('JTF', 'JTF_RS_ERR_ROLE_START_DATE');
1213 fnd_msg_pub.add;
1214
1215 RAISE fnd_api.g_exc_error;
1216
1217
1218 END IF;
1219
1220 IF ( l_max_end_date > l_end_date_active AND l_end_date_active IS NOT NULL ) THEN
1221
1222 fnd_message.set_name('JTF', 'JTF_RS_ERR_ROLE_END_DATE');
1223 fnd_msg_pub.add;
1224
1225 RAISE fnd_api.g_exc_error;
1226
1227 END IF;
1228
1229 END IF;
1230
1231
1232 /* Close the cursor */
1233
1234 IF c_related_role_dates_first%ISOPEN THEN
1235
1236 CLOSE c_related_role_dates_first;
1237
1238 END IF;
1239
1240
1241
1242 /* Second part of the validation where the role relate end date active
1243 is null */
1244
1245 OPEN c_related_role_dates_sec(l_group_id);
1246
1247 FETCH c_related_role_dates_sec INTO l_min_start_date;
1248
1249
1250 IF l_min_start_date IS NOT NULL THEN
1251
1252 IF l_min_start_date < l_start_date_active THEN
1253
1254 fnd_message.set_name('JTF', 'JTF_RS_ERR_ROLE_START_DATE');
1255 fnd_msg_pub.add;
1256
1257 RAISE fnd_api.g_exc_error;
1258
1259
1260 END IF;
1261
1262 IF l_end_date_active IS NOT NULL THEN
1263
1264 fnd_message.set_name('JTF', 'JTF_RS_ERR_ROLE_END_DATE');
1265 fnd_msg_pub.add;
1266
1267 RAISE fnd_api.g_exc_error;
1268
1269
1270 END IF;
1271
1272 END IF;
1273
1274
1275 /* Close the cursor */
1276
1277 IF c_related_role_dates_sec%ISOPEN THEN
1278
1279 CLOSE c_related_role_dates_sec;
1280
1281 END IF;
1282
1283
1284
1285 /* Validate that the group dates cover the group member role related dates for the
1286 group */
1287
1288 /* First part of the validation where the group member role relate end date active
1289 is not null */
1290
1291 OPEN c_grp_mbr_role_dates_first(l_group_id);
1292
1293 FETCH c_grp_mbr_role_dates_first INTO l_min_start_date, l_max_end_date;
1294
1295
1296 IF l_min_start_date IS NOT NULL THEN
1297
1298 IF l_min_start_date < l_start_date_active THEN
1299
1300 fnd_message.set_name('JTF', 'JTF_RS_ERR_GRP_MBR_START_DATE');
1301 fnd_msg_pub.add;
1302
1303 RAISE fnd_api.g_exc_error;
1304
1305 END IF;
1306
1307 IF ( l_max_end_date > l_end_date_active AND l_end_date_active IS NOT NULL ) THEN
1308
1309 fnd_message.set_name('JTF', 'JTF_RS_ERR_GRP_MBR_END_DATE');
1310 fnd_msg_pub.add;
1311
1312 RAISE fnd_api.g_exc_error;
1313
1314
1315 END IF;
1316
1317 END IF;
1318
1319
1320 /* Close the cursor */
1321
1322 IF c_grp_mbr_role_dates_first%ISOPEN THEN
1323
1324 CLOSE c_grp_mbr_role_dates_first;
1325
1326 END IF;
1327
1328
1329
1330 /* Second part of the validation where the member role relate end date active
1331 is null */
1332
1333 OPEN c_grp_mbr_role_dates_sec(l_group_id);
1334
1335 FETCH c_grp_mbr_role_dates_sec INTO l_min_start_date;
1336
1337
1338 IF l_min_start_date IS NOT NULL THEN
1339
1340 IF l_min_start_date < l_start_date_active THEN
1341
1342 fnd_message.set_name('JTF', 'JTF_RS_ERR_GRP_MBR_START_DATE');
1343 fnd_msg_pub.add;
1344
1345 RAISE fnd_api.g_exc_error;
1346
1347
1348 END IF;
1349
1350 IF l_end_date_active IS NOT NULL THEN
1351
1352 fnd_message.set_name('JTF', 'JTF_RS_ERR_GRP_MBR_END_DATE');
1353 fnd_msg_pub.add;
1354
1355 RAISE fnd_api.g_exc_error;
1356
1357
1358 END IF;
1359
1360 END IF;
1361
1362
1363 /* Close the cursor */
1364
1365 IF c_grp_mbr_role_dates_sec%ISOPEN THEN
1366
1367 CLOSE c_grp_mbr_role_dates_sec;
1368
1369 END IF;
1370
1371
1372
1373 /* Validate that the group dates cover the group relation dates for the
1374 group */
1375
1376 /* First part of the validation where the group relate end date active
1377 is not null */
1378
1379 OPEN c_related_group_dates_first(l_group_id);
1380
1381 FETCH c_related_group_dates_first INTO l_min_start_date, l_max_end_date;
1382
1383
1384 IF l_min_start_date IS NOT NULL THEN
1385
1386 IF l_min_start_date < l_start_date_active THEN
1387
1388 fnd_message.set_name('JTF', 'JTF_RS_ERR_GRP_REL_START_DATE');
1389 fnd_msg_pub.add;
1390
1391 RAISE fnd_api.g_exc_error;
1392
1393
1394 END IF;
1395
1396 IF ( l_max_end_date > l_end_date_active AND l_end_date_active IS NOT NULL ) THEN
1397
1398 fnd_message.set_name('JTF', 'JTF_RS_ERR_GRP_REL_END_DATE');
1399 fnd_msg_pub.add;
1400
1401 RAISE fnd_api.g_exc_error;
1402
1403
1404 END IF;
1405
1406 END IF;
1407
1408
1409 /* Close the cursor */
1410
1411 IF c_related_group_dates_first%ISOPEN THEN
1412
1413 CLOSE c_related_group_dates_first;
1414
1415 END IF;
1416
1417
1418
1419 /* Second part of the validation where the group relate end date active
1420 is null */
1421
1422 OPEN c_related_group_dates_sec(l_group_id);
1423
1424 FETCH c_related_group_dates_sec INTO l_min_start_date;
1425
1426
1427 IF l_min_start_date IS NOT NULL THEN
1428
1429 IF l_min_start_date < l_start_date_active THEN
1430
1431 fnd_message.set_name('JTF', 'JTF_RS_ERR_GRP_REL_START_DATE');
1432 fnd_msg_pub.add;
1433
1434 RAISE fnd_api.g_exc_error;
1435
1436 END IF;
1437
1438 IF l_end_date_active IS NOT NULL THEN
1439
1440 fnd_message.set_name('JTF', 'JTF_RS_ERR_GRP_REL_END_DATE');
1441 fnd_msg_pub.add;
1442
1443 RAISE fnd_api.g_exc_error;
1444
1445 END IF;
1446
1447 END IF;
1448
1449
1450 /* Close the cursor */
1451
1452 IF c_related_group_dates_sec%ISOPEN THEN
1453
1454 CLOSE c_related_group_dates_sec;
1455
1456 END IF;
1457
1458
1459
1460 /* Validate that the group dates cover the team member role related dates for the
1461 group, where the team member is a group */
1462
1463 /* First part of the validation where the team member role relate end date active
1464 is not null */
1465
1466 OPEN c_team_mbr_role_dates_first(l_group_id);
1467
1468 FETCH c_team_mbr_role_dates_first INTO l_min_start_date, l_max_end_date;
1469
1470
1471 IF l_min_start_date IS NOT NULL THEN
1472
1473 IF l_min_start_date < l_start_date_active THEN
1474
1475 fnd_message.set_name('JTF', 'JTF_RS_ERR_TEAM_MBR_START_DATE');
1476 fnd_msg_pub.add;
1477
1478 RAISE fnd_api.g_exc_error;
1479
1480 END IF;
1481
1482 IF ( l_max_end_date > l_end_date_active AND l_end_date_active IS NOT NULL ) THEN
1483
1484 fnd_message.set_name('JTF', 'JTF_RS_ERR_TEAM_MBR_END_DATE');
1485 fnd_msg_pub.add;
1486
1487 RAISE fnd_api.g_exc_error;
1488
1489 END IF;
1490
1491 END IF;
1492
1493
1494 /* Close the cursor */
1495
1496 IF c_team_mbr_role_dates_first%ISOPEN THEN
1497
1498 CLOSE c_team_mbr_role_dates_first;
1499
1500 END IF;
1501
1502
1503
1504 /* Second part of the validation where the member role relate end date active
1505 is null */
1506
1507 OPEN c_team_mbr_role_dates_sec(l_group_id);
1508
1509 FETCH c_team_mbr_role_dates_sec INTO l_min_start_date;
1510
1511
1512 IF l_min_start_date IS NOT NULL THEN
1513
1514 IF l_min_start_date < l_start_date_active THEN
1515
1516 fnd_message.set_name('JTF', 'JTF_RS_ERR_TEAM_MBR_START_DATE');
1517 fnd_msg_pub.add;
1518
1519 RAISE fnd_api.g_exc_error;
1520
1521 END IF;
1522
1523 IF l_end_date_active IS NOT NULL THEN
1524
1525 fnd_message.set_name('JTF', 'JTF_RS_ERR_TEAM_MBR_END_DATE');
1526 fnd_msg_pub.add;
1527
1528 RAISE fnd_api.g_exc_error;
1529
1530 END IF;
1531
1532 END IF;
1533
1534
1535 /* Close the cursor */
1536
1537 IF c_team_mbr_role_dates_sec%ISOPEN THEN
1538
1539 CLOSE c_team_mbr_role_dates_sec;
1540
1541 END IF;
1542
1543
1544
1545 /* If Group Exclusive Flag is checked then only those resources can be
1546 assigned to the group, who are not assigned to any other Exclusive group
1547 having the same USAGE value in that same time period. Validate that the
1548 passed values support the above condition for all the group members. */
1549
1550 IF (group_rec.exclusive_flag = 'Y')
1551 THEN
1552 OPEN c_exclusive_group_check(l_group_id);
1553
1554 FETCH c_exclusive_group_check INTO l_check_char;
1555
1556
1557 IF c_exclusive_group_check%FOUND THEN
1558
1559 -- dbms_output.put_line('Group record cannot be updated as one of the member
1560 -- dates overlap with another record for the same resource assigned to
1561 -- another exclusive group with the same usage in the same time period');
1562
1563 IF c_exclusive_group_check%ISOPEN THEN
1564
1565 CLOSE c_exclusive_group_check;
1566
1567 END IF;
1568
1569 fnd_message.set_name('JTF', 'JTF_RS_EXCLUSIVE_GROUP');
1570 fnd_msg_pub.add;
1571
1572 RAISE fnd_api.g_exc_error;
1573
1574 END IF;
1575 END IF;
1576
1577 /* Close the cursors */
1578
1579 IF c_exclusive_group_check%ISOPEN THEN
1580
1581 CLOSE c_exclusive_group_check;
1582
1583 END IF;
1584
1585
1586
1587 /* Call the lock row procedure to ensure that the object version number
1588 is still valid. */
1589
1590 BEGIN
1591
1592 jtf_rs_groups_pkg.lock_row(
1593 x_group_id => l_group_id,
1594 x_object_version_number => p_object_version_num
1595 );
1596
1597 EXCEPTION
1598
1599 WHEN OTHERS THEN
1600
1601 -- dbms_output.put_line('Error in Locking the Row');
1602
1603
1604 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
1605 fnd_msg_pub.add;
1606
1607 RAISE fnd_api.g_exc_error;
1608
1609
1610 END;
1611
1612
1613 /* Make a call to the group Audit API */
1614
1615 jtf_rs_groups_aud_pvt.update_group
1616 (P_API_VERSION => 1,
1617 P_INIT_MSG_LIST => fnd_api.g_false,
1618 P_COMMIT => fnd_api.g_false,
1619 P_GROUP_ID => l_group_id,
1620 P_GROUP_NUMBER => group_rec.group_number,
1621 P_GROUP_NAME => group_rec.group_name,
1622 P_GROUP_DESC => group_rec.group_desc,
1623 P_EXCLUSIVE_FLAG => group_rec.exclusive_flag,
1624 P_EMAIL_ADDRESS => group_rec.email_address,
1625 P_START_DATE_ACTIVE => l_start_date_active,
1626 P_END_DATE_ACTIVE => l_end_date_active,
1627 P_ACCOUNTING_CODE => group_rec.accounting_code,
1628 P_OBJECT_VERSION_NUMBER => p_object_version_num + 1,
1629 X_RETURN_STATUS => x_return_status,
1630 X_MSG_COUNT => x_msg_count,
1631 X_MSG_DATA => x_msg_data
1632 );
1633
1634 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1635
1636 -- dbms_output.put_line('Failed status from call to audit procedure');
1637
1638 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1639 RAISE FND_API.G_EXC_ERROR;
1640 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1642 END IF;
1643
1644 END IF;
1645
1646 /* update the wf roles with changes resource group
1647 this should be done before the chnages happens to
1648 the database since we need the old values */
1649 -- Don't care for its success status
1650 BEGIN
1651 jtf_rs_wf_integration_pub.update_resource_group
1652 (P_API_VERSION => 1.0,
1653 P_GROUP_ID => l_group_id,
1654 P_GROUP_NAME => l_group_name,
1655 P_EMAIL_ADDRESS => l_email_address,
1656 P_START_DATE_ACTIVE => l_start_date_active,
1657 P_END_DATE_ACTIVE => l_end_date_active,
1658 X_RETURN_STATUS => l_return_status,
1659 X_MSG_COUNT => l_msg_count,
1660 X_MSG_DATA => l_msg_data);
1661 EXCEPTION
1662 WHEN OTHERS THEN
1663 NULL;
1664 END;
1665
1666
1667 BEGIN
1668
1669
1670 /* Increment the object version number */
1671
1672 l_object_version_num := p_object_version_num + 1;
1673
1674
1675 /* Update the row into the table by calling the table handler. */
1676
1677 jtf_rs_groups_pkg.update_row(
1678 x_group_id => l_group_id,
1679 x_group_number => group_rec.group_number,
1680 x_exclusive_flag => group_rec.exclusive_flag,
1681 x_email_address => group_rec.email_address,
1682 x_start_date_active => l_start_date_active,
1683 x_end_date_active => l_end_date_active,
1684 x_group_name => group_rec.group_name,
1685 x_group_desc => group_rec.group_desc,
1686 x_accounting_code => group_rec.accounting_code,
1687 x_object_version_number => l_object_version_num,
1688 x_attribute1 => group_rec.attribute1,
1689 x_attribute2 => group_rec.attribute2,
1690 x_attribute3 => group_rec.attribute3,
1691 x_attribute4 => group_rec.attribute4,
1692 x_attribute5 => group_rec.attribute5,
1693 x_attribute6 => group_rec.attribute6,
1694 x_attribute7 => group_rec.attribute7,
1695 x_attribute8 => group_rec.attribute8,
1696 x_attribute9 => group_rec.attribute9,
1697 x_attribute10 => group_rec.attribute10,
1698 x_attribute11 => group_rec.attribute11,
1699 x_attribute12 => group_rec.attribute12,
1700 x_attribute13 => group_rec.attribute13,
1701 x_attribute14 => group_rec.attribute14,
1702 x_attribute15 => group_rec.attribute15,
1703 x_attribute_category => group_rec.attribute_category,
1704 x_last_update_date => SYSDATE,
1705 x_last_updated_by => jtf_resource_utl.updated_by,
1706 x_last_update_login => jtf_resource_utl.login_id
1707 );
1708
1709
1710 /* Return the new value of the object version number */
1711
1712 p_object_version_num := l_object_version_num;
1713
1714
1715 EXCEPTION
1716
1717 WHEN NO_DATA_FOUND THEN
1718
1719 -- dbms_output.put_line('Error in Table Handler');
1720
1721 IF c_group_update%ISOPEN THEN
1722
1723 CLOSE c_group_update;
1724
1725 END IF;
1726
1727
1728 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
1729 fnd_msg_pub.add;
1730
1731 RAISE fnd_api.g_exc_error;
1732
1733 END;
1734
1735 -- dbms_output.put_line('Group Successfully Updated');
1736
1737
1738 /* Close the cursors */
1739
1740 IF c_group_update%ISOPEN THEN
1741
1742 CLOSE c_group_update;
1743
1744 END IF;
1745
1746
1747 /* Make a call to the Group Denorm API */
1748
1749 jtf_rs_group_denorm_pvt.update_res_groups
1750 (P_API_VERSION => 1,
1751 P_INIT_MSG_LIST => fnd_api.g_false,
1752 P_COMMIT => fnd_api.g_false,
1753 P_GROUP_ID => l_group_id,
1754 X_RETURN_STATUS => x_return_status,
1755 X_MSG_COUNT => x_msg_count,
1756 X_MSG_DATA => x_msg_data
1757 );
1758
1759 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1760
1761 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1762 RAISE FND_API.G_EXC_ERROR;
1763 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1765 END IF;
1766
1767
1768 END IF;
1769
1770
1771 /* Make the post processing call to the user hooks */
1772
1773 /* Post Call to the Customer Type User Hook */
1774
1775 IF jtf_resource_utl.ok_to_execute(
1776 'JTF_RS_GROUPS_PVT',
1777 'UPDATE_RESOURCE_GROUP',
1778 'A',
1779 'C')
1780 THEN
1781 IF jtf_usr_hks.ok_to_execute(
1782 'JTF_RS_GROUPS_PVT',
1783 'UPDATE_RESOURCE_GROUP',
1784 'A',
1785 'C')
1786 THEN
1787
1788 jtf_rs_resource_group_cuhk.update_resource_group_post(
1789 p_group_id => l_group_id,
1790 p_group_name => l_group_name,
1791 p_group_desc => l_group_desc,
1792 p_exclusive_flag => l_exclusive_flag,
1793 p_email_address => l_email_address,
1794 p_start_date_active => l_start_date_active,
1795 p_end_date_active => l_end_date_active,
1796 p_accounting_code => l_accounting_code,
1797 x_return_status => x_return_status);
1798
1799 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1800
1801 -- dbms_output.put_line('Returned Error status from the Post Customer User Hook');
1802
1803
1804 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
1805 fnd_msg_pub.add;
1806 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1807 RAISE FND_API.G_EXC_ERROR;
1808 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1809 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1810 END IF;
1811
1812 END IF;
1813
1814 END IF;
1815 END IF;
1816
1817
1818 /* Post Call to the Vertical Type User Hook */
1819
1820 IF jtf_resource_utl.ok_to_execute(
1821 'JTF_RS_GROUPS_PVT',
1822 'UPDATE_RESOURCE_GROUP',
1823 'A',
1824 'V')
1825 THEN
1826 IF jtf_usr_hks.ok_to_execute(
1827 'JTF_RS_GROUPS_PVT',
1828 'UPDATE_RESOURCE_GROUP',
1829 'A',
1830 'V')
1831 THEN
1832
1833 jtf_rs_resource_group_vuhk.update_resource_group_post(
1834 p_group_id => l_group_id,
1835 p_group_name => l_group_name,
1836 p_group_desc => l_group_desc,
1837 p_exclusive_flag => l_exclusive_flag,
1838 p_email_address => l_email_address,
1839 p_start_date_active => l_start_date_active,
1840 p_end_date_active => l_end_date_active,
1841 p_accounting_code => l_accounting_code,
1842 x_return_status => x_return_status);
1843
1844 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1845
1846 -- dbms_output.put_line('Returned Error status from the Post Vertical User Hook');
1847
1848
1849 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
1850 fnd_msg_pub.add;
1851
1852 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1853 RAISE FND_API.G_EXC_ERROR;
1854 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1855 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1856 END IF;
1857
1858
1859 END IF;
1860
1861 END IF;
1862 END IF;
1863
1864
1865 /* Post Call to the Internal Type User Hook */
1866
1867 IF jtf_resource_utl.ok_to_execute(
1868 'JTF_RS_GROUPS_PVT',
1869 'UPDATE_RESOURCE_GROUP',
1870 'A',
1871 'I')
1872 THEN
1873 IF jtf_usr_hks.ok_to_execute(
1874 'JTF_RS_GROUPS_PVT',
1875 'UPDATE_RESOURCE_GROUP',
1876 'A',
1877 'I')
1878 THEN
1879
1880 jtf_rs_resource_group_iuhk.update_resource_group_post(
1881 p_group_id => l_group_id,
1882 p_group_name => l_group_name,
1883 p_group_desc => l_group_desc,
1884 p_exclusive_flag => l_exclusive_flag,
1885 p_email_address => l_email_address,
1886 p_start_date_active => l_start_date_active,
1887 p_end_date_active => l_end_date_active,
1888 p_accounting_code => l_accounting_code,
1889 x_return_status => x_return_status);
1890
1891 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1892
1893
1894 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1895 fnd_msg_pub.add;
1896
1897 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1898 RAISE FND_API.G_EXC_ERROR;
1899 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1901 END IF;
1902
1903 END IF;
1904
1905 END IF;
1906 END IF;
1907
1908
1909 /* Standard call for Message Generation */
1910
1911 IF jtf_resource_utl.ok_to_execute(
1912 'JTF_RS_GROUPS_PVT',
1913 'UPDATE_RESOURCE_GROUP',
1914 'M',
1915 'M')
1916 THEN
1917 IF jtf_usr_hks.ok_to_execute(
1918 'JTF_RS_GROUPS_PVT',
1919 'UPDATE_RESOURCE_GROUP',
1920 'M',
1921 'M')
1922 THEN
1923
1924 IF (jtf_rs_resource_group_cuhk.ok_to_generate_msg(
1925 p_group_id => l_group_id,
1926 x_return_status => x_return_status) )
1927 THEN
1928
1929 /* Get the bind data id for the Business Object Instance */
1930
1931 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1932
1933
1934 /* Set bind values for the bind variables in the Business Object SQL */
1935
1936 jtf_usr_hks.load_bind_data(l_bind_data_id, 'group_id', l_group_id, 'S', 'N');
1937
1938
1939 /* Call the message generation API */
1940
1941 jtf_usr_hks.generate_message(
1942 p_prod_code => 'RS',
1943 p_bus_obj_code => 'GRP',
1944 p_action_code => 'U',
1945 p_bind_data_id => l_bind_data_id,
1946 x_return_code => x_return_status);
1947
1948
1949 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1950
1951 -- dbms_output.put_line('Returned Error status from the Message Generation API');
1952
1953
1954 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1955 fnd_msg_pub.add;
1956
1957 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1958 RAISE FND_API.G_EXC_ERROR;
1959 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1961 END IF;
1962
1963 END IF;
1964
1965 END IF;
1966
1967 END IF;
1968 END IF;
1969
1970
1971 IF fnd_api.to_boolean(p_commit) THEN
1972
1973 COMMIT WORK;
1974
1975 END IF;
1976
1977 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1978
1979
1980 EXCEPTION
1981
1982 WHEN fnd_api.g_exc_error THEN
1983 ROLLBACK TO update_resource_group_pvt;
1984 x_return_status := fnd_api.g_ret_sts_error;
1985 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1986 p_data => x_msg_data);
1987 WHEN fnd_api.g_exc_unexpected_error THEN
1988 ROLLBACK TO update_resource_group_pvt;
1989 x_return_status := fnd_api.g_ret_sts_unexp_error;
1990 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1991 p_data => x_msg_data);
1992 WHEN OTHERS THEN
1993 ROLLBACK TO update_resource_group_pvt;
1994 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1995 fnd_message.set_token('P_SQLCODE',SQLCODE);
1996 fnd_message.set_token('P_SQLERRM',SQLERRM);
1997 fnd_message.set_token('P_API_NAME', l_api_name);
1998 FND_MSG_PUB.add;
1999 x_return_status := fnd_api.g_ret_sts_unexp_error;
2000 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2001 p_data => x_msg_data);
2002
2003 END update_resource_group;
2004
2005
2006 END jtf_rs_groups_pvt;
[Home] [Help]