[Home] [Help]
PACKAGE BODY: APPS.IEU_SH_PVT
Source
1 PACKAGE BODY IEU_SH_PVT AS
2 /* $Header: IEUSHVB.pls 115.19 2004/07/23 10:19:31 nveerara ship $ */
3 PROCEDURE UWQ_BEGIN_SESSION
4 ( P_API_VERSION IN NUMBER,
5 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT fnd_api.g_false,
6 P_COMMIT IN VARCHAR2 DEFAULT fnd_api.g_false,
7 P_RESOURCE_ID IN NUMBER,
8 P_USER_ID IN NUMBER,
9 P_LOGIN_ID IN NUMBER DEFAULT NULL,
10 P_EXTENSION IN VARCHAR2 DEFAULT NULL,
11 P_APPLICATION_ID IN NUMBER,
12 X_SESSION_ID OUT NOCOPY NUMBER,
13 X_MSG_COUNT OUT NOCOPY NUMBER,
14 X_MSG_DATA OUT NOCOPY VARCHAR2,
15 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
16
17 PRAGMA AUTONOMOUS_TRANSACTION;
18
19 l_api_version CONSTANT NUMBER := 1.0;
20 l_api_name CONSTANT VARCHAR2(30) := 'UWQ_BEGIN_SESSION';
21
22 l_token_str VARCHAR2(4000) := '';
23
24 l_end_date_time DATE;
25
26 l_object_version_number NUMBER;
27 l_active_flag VARCHAR2(1);
28 BEGIN
29 l_object_version_number := 1;
30 l_active_flag := 'T';
31
32 x_return_status := fnd_api.g_ret_sts_success;
33
34 -- Check for API Version
35
36 IF NOT fnd_api.compatible_api_call (
37 l_api_version,
38 p_api_version,
39 l_api_name,
40 g_pkg_name
41 )
42 THEN
43 RAISE fnd_api.g_exc_unexpected_error;
44 END IF;
45
46 -- Initialize Message list
47
48 IF fnd_api.to_boolean(p_init_msg_list)
49 THEN
50 FND_MSG_PUB.INITIALIZE;
51 END IF;
52
53 INSERT INTO IEU_SH_SESSIONS (
54 SESSION_ID,
55 OBJECT_VERSION_NUMBER,
56 CREATED_BY,
57 CREATION_DATE,
58 LAST_UPDATED_BY,
59 LAST_UPDATE_DATE,
60 LAST_UPDATE_LOGIN,
61 RESOURCE_ID,
62 BEGIN_DATE_TIME,
63 END_DATE_TIME,
64 ACTIVE_FLAG,
65 EXTENSION,
66 APPLICATION_ID
67 ) values
68 (
69 IEU_SH_SESSIONS_S1.NEXTVAL,
70 l_object_version_number,
71 P_USER_ID,
72 SYSDATE,
73 P_USER_ID,
74 SYSDATE,
75 P_LOGIN_ID,
76 P_RESOURCE_ID,
77 SYSDATE,
78 NULL,
79 l_active_flag,
80 P_EXTENSION,
81 P_APPLICATION_ID)
82 RETURNING SESSION_ID INTO X_SESSION_ID;
83
84 if (sql%notfound) then
85 x_return_status := fnd_api.g_ret_sts_error;
86 end if;
87
88 IF (x_return_status <> fnd_api.g_ret_sts_success)
89 THEN
90
91 x_return_status := fnd_api.g_ret_sts_error;
92
93 l_token_str := 'USER_ID : '||p_user_id||
94 ' RESOURCE_ID : '||p_resource_id;
95
96 FND_MESSAGE.SET_NAME('IEU', 'IEU_CREATE_SESSION_FAILED');
97 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','IEU_SH_PVT.UWQ_BEGIN_SESSION');
98 FND_MESSAGE.SET_TOKEN('DETAILS', l_token_str);
99
100 fnd_msg_pub.ADD;
101 fnd_msg_pub.Count_and_Get
102 (
103 p_count => x_msg_count,
104 p_data => x_msg_data
105 );
106
107 RAISE fnd_api.g_exc_error;
108 END IF;
109
110 COMMIT;
111
112 EXCEPTION
113
114 WHEN fnd_api.g_exc_error THEN
115
116 ROLLBACK;
117 x_return_status := fnd_api.g_ret_sts_error;
118
119 fnd_msg_pub.Count_and_Get
120 (
121 p_count => x_msg_count,
122 p_data => x_msg_data
123 );
124
125 WHEN fnd_api.g_exc_unexpected_error THEN
126
127 ROLLBACK;
128 x_return_status := fnd_api.g_ret_sts_unexp_error;
129
130 fnd_msg_pub.Count_and_Get
131 (
132 p_count => x_msg_count,
133 p_data => x_msg_data
134 );
135
136
137 WHEN OTHERS THEN
138
139 ROLLBACK;
140 x_return_status := fnd_api.g_ret_sts_unexp_error;
141
142 IF FND_MSG_PUB.Check_msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
143 THEN
144
145 fnd_msg_pub.Count_and_Get
146 (
147 p_count => x_msg_count,
148 p_data => x_msg_data
149 );
150
151 END IF;
152
153 END UWQ_BEGIN_SESSION;
154
155 PROCEDURE UWQ_END_SESSION
156 ( P_API_VERSION IN NUMBER,
157 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT fnd_api.g_false,
158 P_COMMIT IN VARCHAR2 DEFAULT fnd_api.g_false,
159 P_SESSION_ID IN NUMBER,
160 P_END_REASON_CODE IN VARCHAR2 DEFAULT NULL,
161 X_MSG_COUNT OUT NOCOPY NUMBER,
162 X_MSG_DATA OUT NOCOPY VARCHAR2,
163 X_RETURN_STATUS OUT NOCOPY VARCHAR2 ) IS
164
165 PRAGMA AUTONOMOUS_TRANSACTION;
166
167 l_api_version CONSTANT NUMBER := 1.0;
168 l_api_name CONSTANT VARCHAR2(30) := 'UWQ_END_SESSION';
169
170 l_token_str VARCHAR2(4000) := '';
171
172 BEGIN
173
174 x_return_status := fnd_api.g_ret_sts_success;
175
176 -- Check for API Version
177
178 IF NOT fnd_api.compatible_api_call (
179 l_api_version,
180 p_api_version,
181 l_api_name,
182 g_pkg_name
183 )
184 THEN
185 RAISE fnd_api.g_exc_unexpected_error;
186 END IF;
187
188 -- Initialize Message list
189
190 IF fnd_api.to_boolean(p_init_msg_list)
191 THEN
192 FND_MSG_PUB.INITIALIZE;
193 END IF;
194
195 update IEU_SH_SESSIONS set
196 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
197 END_DATE_TIME = SYSDATE,
198 ACTIVE_FLAG = NULL,
199 END_REASON_CODE = P_END_REASON_CODE,
200 FORCE_CLOSED_BY_UWQ_FLAG = 'N',
201 LAST_UPDATE_DATE = SYSDATE
202 WHERE SESSION_ID = P_SESSION_ID;
203
204 if (sql%notfound) then
205 x_return_status := fnd_api.g_ret_sts_error;
206 end if;
207
208
209 IF (x_return_status <> fnd_api.g_ret_sts_success)
210 THEN
211
212 x_return_status := fnd_api.g_ret_sts_error;
213
214 l_token_str := 'SESSION_ID : '||p_session_id;
215
216 FND_MESSAGE.SET_NAME('IEU', 'IEU_END_SESSION_FAILED');
217 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','IEU_SH_PVT.UWQ_END_SESSION');
218 FND_MESSAGE.SET_TOKEN('DETAILS', l_token_str);
219
220 fnd_msg_pub.ADD;
221 fnd_msg_pub.Count_and_Get
222 (
223 p_count => x_msg_count,
224 p_data => x_msg_data
225 );
226
227 RAISE fnd_api.g_exc_error;
228 END IF;
229
230 COMMIT;
231
232 EXCEPTION
233
234 WHEN fnd_api.g_exc_error THEN
235
236 ROLLBACK;
237 x_return_status := fnd_api.g_ret_sts_error;
238
239 fnd_msg_pub.Count_and_Get
240 (
241 p_count => x_msg_count,
242 p_data => x_msg_data
243 );
244
245 WHEN fnd_api.g_exc_unexpected_error THEN
246
247 ROLLBACK;
248 x_return_status := fnd_api.g_ret_sts_unexp_error;
249
250 fnd_msg_pub.Count_and_Get
251 (
252 p_count => x_msg_count,
253 p_data => x_msg_data
254 );
255
256
257 WHEN OTHERS THEN
258
259 ROLLBACK;
260 x_return_status := fnd_api.g_ret_sts_unexp_error;
261
262 IF FND_MSG_PUB.Check_msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
263 THEN
264
265 fnd_msg_pub.Count_and_Get
266 (
267 p_count => x_msg_count,
268 p_data => x_msg_data
269 );
270
271 END IF;
272 END UWQ_END_SESSION;
273
274 PROCEDURE UWQ_BEGIN_ACTIVITY
275 ( P_API_VERSION IN NUMBER,
276 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT fnd_api.g_false,
277 P_COMMIT IN VARCHAR2 DEFAULT fnd_api.g_false,
278 P_SESSION_ID IN NUMBER,
279 -- P_ACTIVITY_TYPE_ID IN NUMBER,
280 P_ACTIVITY_TYPE_CODE IN VARCHAR2,
281 P_LAST_ACTIVITY_ID IN NUMBER DEFAULT NULL,
282 P_BEGIN_TIME_FLAG IN NUMBER DEFAULT NULL,
283 P_MEDIA_TYPE_ID IN NUMBER DEFAULT NULL,
284 P_MEDIA_ID IN NUMBER DEFAULT NULL,
285 P_USER_ID IN NUMBER,
286 P_LOGIN_ID IN NUMBER DEFAULT NULL,
287 P_REASON_CODE IN VARCHAR2 DEFAULT NULL,
288 P_REQUEST_METHOD IN VARCHAR2 DEFAULT NULL,
289 P_REQUESTED_MEDIA_TYPE_ID IN NUMBER DEFAULT NULL,
290 P_WORK_ITEM_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
291 P_WORK_ITEM_PK_ID IN NUMBER DEFAULT NULL,
292 P_END_ACTIVITY_FLAG IN VARCHAR2 DEFAULT NULL,
293 P_PARENT_CYCLE_ID IN NUMBER DEFAULT NULL,
294 P_CATEGORY_TYPE IN VARCHAR2 DEFAULT NULL,
295 P_CATEGORY_VALUE IN VARCHAR2 DEFAULT NULL,
296 X_ACTIVITY_ID OUT NOCOPY NUMBER,
297 X_MSG_COUNT OUT NOCOPY NUMBER,
298 X_MSG_DATA OUT NOCOPY VARCHAR2,
299 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
300
301 PRAGMA AUTONOMOUS_TRANSACTION;
302
303 l_api_version CONSTANT NUMBER := 1.0;
304 l_api_name CONSTANT VARCHAR2(30) := 'UWQ_BEGIN_ACTIVITY';
305
306 l_token_str VARCHAR2(4000) := '';
307
308 l_return_status varchar2(5);
309 l_msg_count number;
310 l_msg_data varchar2(1000);
311
312 l_begin_date_time DATE;
313 l_last_activity_id NUMBER;
314
315 l_end_date_time DATE;
316 l_active_flag VARCHAR2(1);
317
318 l_activity_type_id NUMBER;
319
320 BEGIN
321
322 x_return_status := fnd_api.g_ret_sts_success;
323
324 -- Check for API Version
325
326 IF NOT fnd_api.compatible_api_call (
327 l_api_version,
328 p_api_version,
329 l_api_name,
330 g_pkg_name
331 )
332 THEN
333 RAISE fnd_api.g_exc_unexpected_error;
334 END IF;
335
336 -- Initialize Message list
337
338 IF fnd_api.to_boolean(p_init_msg_list)
339 THEN
340 FND_MSG_PUB.INITIALIZE;
341 END IF;
342
343 UPDATE ieu_sh_sessions
344 SET active_flag = 'Y',
345 end_date_time = NULL
346 WHERE session_id = p_session_id
347 AND end_date_time is not null
348 AND active_flag <> 'Y';
349
350 BEGIN
351
352 SELECT activity_type_id
353 INTO l_activity_type_id
354 FROM ieu_sh_act_types_b
355 WHERE activity_type_code = p_activity_type_code;
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 NULL;
360 END;
361
362 -- changes for correcting reports..
363 -- 0 OR NULL means we have to use db native time
364 -- 1 means we have to use previous activity time
365 -- 2 means we have to use previous cycle time
366 IF ( P_BEGIN_TIME_FLAG = 1 )
367 THEN
368 l_last_activity_id := P_LAST_ACTIVITY_ID;
369 ELSIF ( P_BEGIN_TIME_FLAG = 2 )
370 THEN
371 l_last_activity_id := P_PARENT_CYCLE_ID;
372 END IF;
373
374 -- the default begin_date_time is the current systime
375 SELECT SYSDATE INTO l_begin_date_time FROM DUAL;
376
377 -- if this is the first activity in the cycle then get the begin_date_time
378 -- for the cycle
379 -- if this is an intermediate activity get the end_date_time of the
380 -- previous activity if there is an error default to the current time
381 if ( l_last_activity_id IS NOT NULL )
382 THEN
383 IF ( P_BEGIN_TIME_FLAG = 1 )
384 THEN
385 SELECT END_DATE_TIME INTO l_begin_date_time FROM IEU_SH_ACTIVITIES
386 WHERE ACTIVITY_ID = l_last_activity_id;
387 ELSIF ( P_BEGIN_TIME_FLAG = 2 )
388 THEN
389 SELECT BEGIN_DATE_TIME INTO l_begin_date_time FROM IEU_SH_ACTIVITIES
390 WHERE ACTIVITY_ID = l_last_activity_id;
391 END IF;
392 END IF;
393
394 -- end changes for correcting reports
395
396 INSERT INTO IEU_SH_ACTIVITIES (
397 ACTIVITY_ID,
398 SESSION_ID,
399 ACTIVITY_TYPE_ID,
400 ACTIVITY_TYPE_CODE,
401 MEDIA_TYPE_ID,
402 MEDIA_ID,
403 OBJECT_VERSION_NUMBER,
404 CREATED_BY,
405 CREATION_DATE,
406 LAST_UPDATED_BY,
407 LAST_UPDATE_DATE,
408 LAST_UPDATE_LOGIN,
409 BEGIN_DATE_TIME,
410 END_DATE_TIME,
411 ACTIVE_FLAG,
412 REASON_CODE,
413 REQUEST_METHOD,
414 REQUESTED_MEDIA_TYPE_ID,
415 WORK_ITEM_TYPE_CODE,
416 WORK_ITEM_PK_ID,
417 STATE_CODE,
418 PARENT_CYCLE_ID,
419 CATEGORY_TYPE,
420 CATEGORY_VALUE
421 ) values
422 (
423 IEU_SH_ACTIVITIES_S1.NEXTVAL,
424 P_SESSION_ID,
425 L_ACTIVITY_TYPE_ID,
426 P_ACTIVITY_TYPE_CODE,
427 P_MEDIA_TYPE_ID,
428 P_MEDIA_ID,
429 1,
430 P_USER_ID,
431 l_begin_date_time,
432 P_USER_ID,
433 l_begin_date_time,
434 P_LOGIN_ID,
435 l_begin_date_time,
436 NULL,
437 'T',
438 P_REASON_CODE,
439 P_REQUEST_METHOD,
440 P_REQUESTED_MEDIA_TYPE_ID,
441 P_WORK_ITEM_TYPE_CODE,
442 P_WORK_ITEM_PK_ID,
443 'BEGIN',
444 P_PARENT_CYCLE_ID,
445 P_CATEGORY_TYPE,
446 P_CATEGORY_VALUE)
447 RETURNING ACTIVITY_ID INTO X_ACTIVITY_ID;
448
449 if (sql%notfound) then
450 x_return_status := fnd_api.g_ret_sts_error;
451 l_token_str := 'USER_ID : '|| p_user_id ||
452 ' SESSION_ID : '||p_session_id;
453
454 FND_MESSAGE.SET_NAME('IEU', 'IEU_CREATE_ACTIVITY_FAILED');
455 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','IEU_SH_PVT.UWQ_BEGIN_ACTIVITY');
456 FND_MESSAGE.SET_TOKEN('DETAILS', l_token_str);
457
458 fnd_msg_pub.ADD;
459 fnd_msg_pub.Count_and_Get
460 (
461 p_count => x_msg_count,
462 p_data => x_msg_data
463 );
464
465 RAISE fnd_api.g_exc_error;
466 end if;
467
468 COMMIT;
469
470 -- A NON_MEDIA activity has only one state, so
471 -- we will just end it right away.
472
473
474 -- IF (P_ACTIVITY_TYPE_CODE = 'NON_MEDIA') THEN
475 IF (nvl(P_END_ACTIVITY_FLAG, 'N') = 'Y') THEN
476 IEU_SH_PVT.UWQ_END_ACTIVITY
480 P_ACTIVITY_ID => x_activity_id,
477 ( P_API_VERSION => l_api_version,
478 P_INIT_MSG_LIST => 'T',
479 P_COMMIT => 'T',
481 P_MEDIA_TYPE_ID => NULL,
482 P_MEDIA_ID => NULL,
483 X_MSG_COUNT => l_msg_count,
484 X_MSG_DATA => l_msg_data,
485 X_RETURN_STATUS => l_return_status);
486 END IF;
487
488
489 if (l_return_status <> 'S') then
490 x_return_status := fnd_api.g_ret_sts_error;
491 end if;
492
493 IF (x_return_status <> fnd_api.g_ret_sts_success)
494 THEN
495
496 x_return_status := fnd_api.g_ret_sts_error;
497
498 l_token_str := 'USER_ID : '|| p_user_id ||
499 ' SESSION_ID : '||p_session_id;
500
501 FND_MESSAGE.SET_NAME('IEU', 'IEU_CREATE_ACTIVITY_FAILED');
502 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','IEU_SH_PVT.UWQ_BEGIN_ACTIVITY');
503 FND_MESSAGE.SET_TOKEN('DETAILS', l_token_str);
504
505 fnd_msg_pub.ADD;
506 fnd_msg_pub.Count_and_Get
507 (
508 p_count => x_msg_count,
509 p_data => x_msg_data
510 );
511
512 RAISE fnd_api.g_exc_error;
513 END IF;
514
515 -- In any Autonomous Transaction, COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine.
516 -- when one transaction ends, the next SQL statement begins another transaction. So we have to COMMIT this transaction again.
517
518 COMMIT;
519
520 EXCEPTION
521
522 WHEN fnd_api.g_exc_error THEN
523
524 ROLLBACK;
525 x_return_status := fnd_api.g_ret_sts_error;
526
527 fnd_msg_pub.Count_and_Get
528 (
529 p_count => x_msg_count,
530 p_data => x_msg_data
531 );
532
533 WHEN fnd_api.g_exc_unexpected_error THEN
534
535 ROLLBACK;
536 x_return_status := fnd_api.g_ret_sts_unexp_error;
537
538 fnd_msg_pub.Count_and_Get
539 (
540 p_count => x_msg_count,
541 p_data => x_msg_data
542 );
543
544
545 WHEN OTHERS THEN
546
547 ROLLBACK;
548 x_return_status := fnd_api.g_ret_sts_unexp_error;
549
550 IF FND_MSG_PUB.Check_msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
551 THEN
552
553 fnd_msg_pub.Count_and_Get
554 (
555 p_count => x_msg_count,
556 p_data => x_msg_data
557 );
558
559 END IF;
560
561 END UWQ_BEGIN_ACTIVITY;
562
563
564 PROCEDURE UWQ_UPDATE_ACTIVITY
565 ( P_API_VERSION IN NUMBER,
566 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT fnd_api.g_false,
567 P_COMMIT IN VARCHAR2 DEFAULT fnd_api.g_false,
568 P_ACTIVITY_ID IN NUMBER,
569 P_INTERMEDIATE_STATE_CODE IN VARCHAR2 DEFAULT NULL,
570 P_MEDIA_TYPE_ID IN NUMBER DEFAULT NULL,
571 P_MEDIA_ID IN NUMBER DEFAULT NULL,
572 P_REASON_CODE IN VARCHAR2 DEFAULT NULL,
573 X_MSG_COUNT OUT NOCOPY NUMBER,
574 X_MSG_DATA OUT NOCOPY VARCHAR2,
575 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
576
577 PRAGMA AUTONOMOUS_TRANSACTION;
578
579 l_api_version CONSTANT NUMBER := 1.0;
580 l_api_name CONSTANT VARCHAR2(30) := 'UWQ_UPDATE_ACTIVITY';
581
582 l_token_str VARCHAR2(4000) := '';
583
584 BEGIN
585
586 x_return_status := fnd_api.g_ret_sts_success;
587
588 -- Check for API Version
589
590 IF NOT fnd_api.compatible_api_call (
591 l_api_version,
592 p_api_version,
593 l_api_name,
594 g_pkg_name
595 )
596 THEN
597 RAISE fnd_api.g_exc_unexpected_error;
598 END IF;
599
600 -- Initialize Message list
601
602 IF fnd_api.to_boolean(p_init_msg_list)
603 THEN
604 FND_MSG_PUB.INITIALIZE;
605 END IF;
606
607 IF (P_INTERMEDIATE_STATE_CODE = 'ACTIVE') THEN
608
609 update IEU_SH_ACTIVITIES set
610 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
611 DELIVER_DATE_TIME = SYSDATE,
612 STATE_CODE = P_INTERMEDIATE_STATE_CODE
613 WHERE ACTIVITY_ID = P_ACTIVITY_ID;
614
615 if (sql%notfound) then
616 x_return_status := fnd_api.g_ret_sts_error;
617 end if;
618
619 END IF;
620
621 IF (P_MEDIA_ID IS NOT NULL)
622 THEN
623
624 UPDATE IEU_SH_ACTIVITIES SET
625 MEDIA_ID = P_MEDIA_ID,
626 LAST_UPDATE_DATE = SYSDATE
627 WHERE ACTIVITY_ID = P_ACTIVITY_ID;
628
629 END IF;
630
631 IF (P_MEDIA_TYPE_ID IS NOT NULL)
632 THEN
633
634 UPDATE IEU_SH_ACTIVITIES SET
635 MEDIA_TYPE_ID = P_MEDIA_TYPE_ID,
636 LAST_UPDATE_DATE = SYSDATE
637 WHERE ACTIVITY_ID = P_ACTIVITY_ID;
638
639 END IF;
640
641 IF (P_MEDIA_TYPE_ID IS NOT NULL)
642 THEN
643
644 UPDATE IEU_SH_ACTIVITIES SET
645 REASON_CODE = P_REASON_CODE,
646 LAST_UPDATE_DATE = SYSDATE
647 WHERE ACTIVITY_ID = P_ACTIVITY_ID;
648
649 END IF;
650
651 IF (x_return_status <> fnd_api.g_ret_sts_success)
652 THEN
653
654 x_return_status := fnd_api.g_ret_sts_error;
655
659 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','IEU_SH_PVT.UWQ_UPDATE_ACTIVITY');
656 l_token_str := 'ACTIVITY_ID : '||p_activity_id;
657
658 FND_MESSAGE.SET_NAME('IEU', 'IEU_UPDATE_ACTIVITY_FAILED');
660 FND_MESSAGE.SET_TOKEN('DETAILS', l_token_str);
661
662 fnd_msg_pub.ADD;
663 fnd_msg_pub.Count_and_Get
664 (
665 p_count => x_msg_count,
666 p_data => x_msg_data
667 );
668
669 RAISE fnd_api.g_exc_error;
670 END IF;
671
672 COMMIT;
673
674 EXCEPTION
675
676 WHEN fnd_api.g_exc_error THEN
677
678 ROLLBACK;
679 x_return_status := fnd_api.g_ret_sts_error;
680
681 fnd_msg_pub.Count_and_Get
682 (
683 p_count => x_msg_count,
684 p_data => x_msg_data
685 );
686
687 WHEN fnd_api.g_exc_unexpected_error THEN
688
689 ROLLBACK;
690 x_return_status := fnd_api.g_ret_sts_unexp_error;
691
692 fnd_msg_pub.Count_and_Get
693 (
694 p_count => x_msg_count,
695 p_data => x_msg_data
696 );
697
698
699 WHEN OTHERS THEN
700
701 ROLLBACK;
702 x_return_status := fnd_api.g_ret_sts_unexp_error;
703
704 IF FND_MSG_PUB.Check_msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
705 THEN
706
707 fnd_msg_pub.Count_and_Get
708 (
709 p_count => x_msg_count,
710 p_data => x_msg_data
711 );
712
713 END IF;
714
715 END UWQ_UPDATE_ACTIVITY;
716
717
718 PROCEDURE UWQ_END_ACTIVITY
719 ( P_API_VERSION IN NUMBER,
720 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT fnd_api.g_false,
721 P_COMMIT IN VARCHAR2 DEFAULT fnd_api.g_false,
722 P_ACTIVITY_ID IN NUMBER,
723 P_LAST_ACTIVITY_ID IN NUMBER DEFAULT NULL,
724 P_MEDIA_TYPE_ID IN NUMBER DEFAULT NULL,
725 P_MEDIA_ID IN NUMBER DEFAULT NULL,
726 P_COMPLETION_CODE IN VARCHAR2 DEFAULT NULL,
727 X_MSG_COUNT OUT NOCOPY NUMBER,
728 X_MSG_DATA OUT NOCOPY VARCHAR2,
729 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
730
731 PRAGMA AUTONOMOUS_TRANSACTION;
732
733 l_api_version CONSTANT NUMBER := 1.0;
734 l_api_name CONSTANT VARCHAR2(30) := 'UWQ_END_ACTIVITY';
735
736 l_token_str VARCHAR2(4000) := '';
737 l_end_date_time DATE;
738
739 -- l_session_id NUMBER;
740
741 BEGIN
742
743 x_return_status := fnd_api.g_ret_sts_success;
744
745 -- Check for API Version
746
747 IF NOT fnd_api.compatible_api_call (
748 l_api_version,
749 p_api_version,
750 l_api_name,
751 g_pkg_name
752 )
753 THEN
754 RAISE fnd_api.g_exc_unexpected_error;
755 END IF;
756
757 -- Initialize Message list
758
759 IF fnd_api.to_boolean(p_init_msg_list)
760 THEN
761 FND_MSG_PUB.INITIALIZE;
762 END IF;
763
764 -- begin changes for correcting reports
765 -- the default end_date_time is the current time
766 -- if the last activity id is passed this is a MEDIA_CYCLE ending..
767 -- set the end_date_time as the end time of the last activity
768
769 SELECT SYSDATE INTO l_end_date_time FROM DUAL;
770
771 IF ( P_LAST_ACTIVITY_ID IS NOT NULL )
772 THEN
773 SELECT END_DATE_TIME INTO l_end_date_time FROM IEU_SH_ACTIVITIES WHERE
774 ACTIVITY_ID = P_LAST_ACTIVITY_ID;
775 END IF;
776
777 -- end changes for correcting reports
778
779 update IEU_SH_ACTIVITIES set
780 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
781 END_DATE_TIME = l_end_date_time,
782 MEDIA_ID = P_MEDIA_ID,
783 MEDIA_TYPE_ID = P_MEDIA_TYPE_ID,
784 ACTIVE_FLAG = NULL,
785 COMPLETION_CODE = P_COMPLETION_CODE,
786 STATE_CODE = 'END',
787 FORCE_CLOSED_BY_UWQ_FLAG = 'N',
788 LAST_UPDATE_DATE = l_end_date_time
789 WHERE ACTIVITY_ID = P_ACTIVITY_ID;
790
791 if (sql%notfound) then
792 x_return_status := fnd_api.g_ret_sts_error;
793 end if;
794
795 IF (x_return_status <> fnd_api.g_ret_sts_success)
796 THEN
797
798 x_return_status := fnd_api.g_ret_sts_error;
799
800 l_token_str := 'ACTIVITY_ID : '||p_activity_id;
801
802 FND_MESSAGE.SET_NAME('IEU', 'IEU_END_ACTIVITY_FAILED');
803 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','IEU_SH_PVT.UWQ_END_ACTIVITY');
804 FND_MESSAGE.SET_TOKEN('DETAILS', l_token_str);
805
806 fnd_msg_pub.ADD;
807 fnd_msg_pub.Count_and_Get
808 (
809 p_count => x_msg_count,
810 p_data => x_msg_data
811 );
812
813 RAISE fnd_api.g_exc_error;
814 END IF;
815
816 COMMIT;
817
818
819 EXCEPTION
820
821 WHEN fnd_api.g_exc_error THEN
822
823 ROLLBACK;
824 x_return_status := fnd_api.g_ret_sts_error;
825
826 fnd_msg_pub.Count_and_Get
827 (
828 p_count => x_msg_count,
829 p_data => x_msg_data
830 );
831
832 WHEN fnd_api.g_exc_unexpected_error THEN
833
834 ROLLBACK;
835 x_return_status := fnd_api.g_ret_sts_unexp_error;
836
840 p_data => x_msg_data
837 fnd_msg_pub.Count_and_Get
838 (
839 p_count => x_msg_count,
841 );
842
843
844 WHEN OTHERS THEN
845
846 ROLLBACK;
847 x_return_status := fnd_api.g_ret_sts_unexp_error;
848
849 IF FND_MSG_PUB.Check_msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
850 THEN
851
852 fnd_msg_pub.Count_and_Get
853 (
854 p_count => x_msg_count,
855 p_data => x_msg_data
856 );
857
858 END IF;
859
860 END UWQ_END_ACTIVITY;
861
862 PROCEDURE UWQ_BREAK_TRANSITION
863 ( P_API_VERSION IN NUMBER,
864 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT fnd_api.g_false,
865 P_COMMIT IN VARCHAR2 DEFAULT fnd_api.g_false,
866 P_SESSION_ID IN NUMBER,
867 P_ACTIVITY_ID IN NUMBER,
868 -- P_ACTIVITY_TYPE_ID IN NUMBER,
869 P_ACTIVITY_TYPE_CODE IN VARCHAR2,
870 P_LAST_ACTIVITY_ID IN NUMBER DEFAULT NULL,
871 P_BEGIN_TIME_FLAG IN NUMBER DEFAULT NULL,
872 P_MEDIA_TYPE_ID IN NUMBER DEFAULT NULL,
873 P_MEDIA_ID IN NUMBER DEFAULT NULL,
874 P_USER_ID IN NUMBER,
875 P_LOGIN_ID IN NUMBER DEFAULT NULL,
876 -- P_PRV_REASON_CODE IN VARCHAR2 DEFAULT NULL
877 P_COMPLETION_CODE IN VARCHAR2 DEFAULT NULL,
878 P_REASON_CODE IN VARCHAR2 DEFAULT NULL,
879 P_REQUEST_METHOD IN VARCHAR2 DEFAULT NULL,
880 P_REQUESTED_MEDIA_TYPE_ID IN NUMBER DEFAULT NULL,
881 P_WORK_ITEM_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
882 P_WORK_ITEM_PK_ID IN NUMBER DEFAULT NULL,
883 P_END_ACTIVITY_FLAG IN VARCHAR2 DEFAULT NULL,
884 P_PARENT_CYCLE_ID IN NUMBER DEFAULT NULL,
885 P_CATEGORY_TYPE IN VARCHAR2 DEFAULT NULL,
886 P_CATEGORY_VALUE IN VARCHAR2 DEFAULT NULL,
887 X_ACTIVITY_ID OUT NOCOPY NUMBER,
888 X_MSG_COUNT OUT NOCOPY NUMBER,
889 X_MSG_DATA OUT NOCOPY VARCHAR2,
890 X_RETURN_STATUS OUT NOCOPY VARCHAR2) AS
891
892 BEGIN
893 UWQ_END_ACTIVITY(
894 P_API_VERSION ,
895 P_INIT_MSG_LIST ,
896 P_COMMIT ,
897 P_ACTIVITY_ID ,
898 P_LAST_ACTIVITY_ID,
899 P_MEDIA_TYPE_ID ,
900 P_MEDIA_ID ,
901 P_COMPLETION_CODE ,
902 X_MSG_COUNT ,
903 X_MSG_DATA ,
904 X_RETURN_STATUS );
905
906 UWQ_BEGIN_ACTIVITY(
907 P_API_VERSION ,
908 P_INIT_MSG_LIST ,
909 P_COMMIT ,
910 P_SESSION_ID ,
911 -- P_ACTIVITY_TYPE_ID IN NUMBER,
912 P_ACTIVITY_TYPE_CODE,
913 P_LAST_ACTIVITY_ID,
914 P_BEGIN_TIME_FLAG ,
915 P_MEDIA_TYPE_ID ,
916 P_MEDIA_ID ,
917 P_USER_ID ,
918 P_LOGIN_ID ,
919 -- P_PRV_REASON_CODE IN VARCHAR2 DEFAULT NULL
920 P_REASON_CODE ,
921 P_REQUEST_METHOD ,
922 P_REQUESTED_MEDIA_TYPE_ID,
923 P_WORK_ITEM_TYPE_CODE,
924 P_WORK_ITEM_PK_ID ,
925 P_END_ACTIVITY_FLAG,
926 P_PARENT_CYCLE_ID ,
927 P_CATEGORY_TYPE ,
928 P_CATEGORY_VALUE ,
929 X_ACTIVITY_ID ,
930 X_MSG_COUNT ,
931 X_MSG_DATA ,
932 X_RETURN_STATUS);
933
934 END UWQ_BREAK_TRANSITION;
935
936 END IEU_SH_PVT;