[Home] [Help]
PACKAGE BODY: APPS.IEC_COMMON_UTIL_PVT
Source
1 PACKAGE BODY IEC_COMMON_UTIL_PVT AS
2 /* $Header: IECCMUTB.pls 120.1 2006/03/28 07:30:08 minwang noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'IEC_COMMON_UTIL_PVT';
5
6 -- Translated error message that will be raised if an exception occurs
7 -- Encoded error message will also be initialized in FND_MESSAGE
8 -- so that calling program can log the encoded message with appropriate
9 -- module name
10
11 g_error_message VARCHAR2(4000) := NULL;
12 g_encoded_message VARCHAR2(4000) := NULL;
13
14 FUNCTION Get_AppsSchemaName
15
16 RETURN VARCHAR2
17
18 IS
19
20 l_schema_name VARCHAR2(30);
21
22 BEGIN
23
24
25 SELECT ORACLE_USERNAME
26
27 INTO l_schema_name
28
29 FROM FND_ORACLE_USERID
30
31 WHERE READ_ONLY_FLAG = 'U';
32
33
34
35 RETURN l_schema_name;
36
37
38
39 EXCEPTION
40
41 WHEN OTHERS THEN
42
43 RAISE fnd_api.g_exc_unexpected_error;
44
45 END Get_AppsSchemaName;
46
47
48
49 PROCEDURE Init_SourceTypeNotSupportedMsg
50
51 ( p_source_type IN VARCHAR2)
52
53 IS
54
55 BEGIN
56
57
58
59 -- Since this is a shared utility package, we will not
60
61 -- log the message, calling program will do logging
62
63 -- so that error can be logged with calling program's module
64
65 IEC_OCS_LOG_PVT.Init_SourceTypeNotSupportedMsg
66
67 ( p_source_type
68
69 , g_error_message -- returns translated error message string
70
71 , g_encoded_message -- returns encoded error message string
72
73 );
74
75
76
77 END Init_SourceTypeNotSupportedMsg;
78
79
80
81 PROCEDURE Init_SourceTypeDoesNotExistMsg
82
83 ( p_source_type IN VARCHAR2)
84
85 IS
86
87 BEGIN
88
89
90
91 -- Since this is a shared utility package, we will not
92
93 -- log the message, calling program will do logging
94
95 -- so that error can be logged with calling program's module
96
97 IEC_OCS_LOG_PVT.Init_SourceTypeDoesNotExistMsg
98
99 ( p_source_type
100
101 , g_error_message -- returns translated error message string
102
103 , g_encoded_message -- returns encoded error message string
104
105 );
106
107
108
109
110
111 END Init_SourceTypeDoesNotExistMsg;
112
113
114
115 PROCEDURE Init_SqlErrmMsg
116
117 ( p_sql_errm IN VARCHAR2)
118
119 IS
120
121 BEGIN
122
123
124
125 -- Since this is a shared utility package, we will not
126
127 -- log the message, calling program will do logging
128
129 -- so that error can be logged with calling program's module
130
131 IEC_OCS_LOG_PVT.Init_SqlErrmMsg
132
133 ( p_sql_errm
134
135 , g_error_message -- returns translated error message string
136
137 , g_encoded_message -- returns encoded error message string
138
139 );
140
141
142
143 END Init_SqlErrmMsg;
144
145
146
147 -----------------------------++++++-------------------------------
148
149 --
150
151 -- Start of comments
152
153 --
154
155 -- API name : Get_SourceTypeView
156
157 -- Type : Private
158
159 -- Pre-reqs : None
160
161 -- Function : Return the source type view name for specified
162
163 -- target group. Raises exception if unable to
164
165 -- locate view.
166
167 --
168
169 -- Parameters : p_list_id IN NUMBER Required
170
171 -- x_source_type_view OUT VARCHAR2 Required
172
173 --
174
175 -- Version : Initial version 1.0
176
177 --
178
179 -- End of comments
180
181 --
182
183 -----------------------------++++++-------------------------------
184
185 PROCEDURE Get_SourceTypeView
186
187 ( p_list_id IN NUMBER
188
189 , x_source_type_view OUT NOCOPY VARCHAR2)
190
191 IS
192
193
194
195 l_ignore VARCHAR2(500);
196 l_source_type_code VARCHAR2(50);
197
198 l_schema VARCHAR2(30);
199
200 BEGIN
201
202
203
204 x_source_type_view := NULL;
205
206
207
208 -- Get Source Type of List
209
210 BEGIN
211
212 EXECUTE IMMEDIATE
213
214 'SELECT B.TAG, A.LIST_SOURCE_TYPE
215
216 FROM AMS_LIST_HEADERS_ALL A, IEC_LOOKUPS B
217
218 WHERE A.LIST_HEADER_ID = :list_id
219
220 AND B.LOOKUP_TYPE = ''IEC_SOURCE_VIEW_MAP''
221
222 AND A.LIST_SOURCE_TYPE = B.LOOKUP_CODE'
223
224 INTO x_source_type_view, l_source_type_code
225
226 USING p_list_id;
227
228 EXCEPTION
229
230 WHEN NO_DATA_FOUND THEN
231
232 -- Get source type of list for logging purposes
233
234 BEGIN
235
236 SELECT LIST_SOURCE_TYPE
237
238 INTO l_source_type_code
239
240 FROM AMS_LIST_HEADERS_ALL
241
242 WHERE LIST_HEADER_ID = p_list_id;
243
244 EXCEPTION
245
246 WHEN OTHERS THEN
247
248 l_source_type_code := NULL;
249
250 END;
251
252
253
254 Init_SourceTypeNotSupportedMsg(l_source_type_code);
255
256 RAISE_APPLICATION_ERROR(-20999, g_error_message);
257
258 WHEN OTHERS THEN
259
260 Init_SqlErrmMsg(SQLERRM);
261
262 RAISE_APPLICATION_ERROR(-20999, g_error_message);
263
264 END;
265
266 l_schema := Get_AppsSchemaName;
267
268 BEGIN
269
270 EXECUTE IMMEDIATE
271
272 'SELECT VIEW_NAME
273
274 FROM ALL_VIEWS
275
276 WHERE VIEW_NAME = UPPER(:source_type_view)
277
278 AND OWNER = :apps_schema'
279
280 INTO l_ignore
281
282 USING x_source_type_view, l_schema;
283
284 EXCEPTION
285
286 WHEN NO_DATA_FOUND THEN
287
288 Init_SourceTypeDoesNotExistMsg(l_source_type_code);
289 RAISE_APPLICATION_ERROR(-20999, g_error_message);
290
291 END;
292
293
294
295 END Get_SourceTypeView;
296
297
298
299 -----------------------------++++++-------------------------------
300
301 --
302
303 -- Start of comments
304
305 --
306
307 -- API name : Get_SubsetName
308
309 -- Type : Private
310
311 -- Pre-reqs : None
312
313 -- Function : Return the name for specified subset.
314
315 -- Initializes FND_MESSAGE and raises
316
317 -- exception if unable to locate name.
318
319 --
320
321 -- Parameters : p_subset_id IN NUMBER Required
322
323 -- x_subset_name OUT VARCHAR2 Required
324
325 --
326
327 -- Version : Initial version 1.0
328
329 --
330
331 -- End of comments
332
333 --
334
335 -----------------------------++++++-------------------------------
336
337 PROCEDURE Get_SubsetName
338
339 ( p_subset_id IN NUMBER
340
341 , x_subset_name OUT NOCOPY VARCHAR2)
342
343 IS
344
345 BEGIN
346
347
348
349 x_subset_name := NULL;
350
351
352
353 -- Get Subset Name
354
355 BEGIN
356
357 EXECUTE IMMEDIATE
358
359 'SELECT SUBSET_NAME
360
361 FROM IEC_G_LIST_SUBSETS
362
363 WHERE LIST_SUBSET_ID = :subset_id'
364
365 INTO x_subset_name
366
367 USING p_subset_id;
368
369 EXCEPTION
370
371 WHEN NO_DATA_FOUND THEN
372
373 x_subset_name := NULL;
374
375 WHEN OTHERS THEN
376
377 Init_SqlErrmMsg(SQLERRM);
378
379 RAISE_APPLICATION_ERROR(-20999, g_error_message);
380
381 END;
382
383
384
385 END Get_SubsetName;
386
387
388
389 -----------------------------++++++-------------------------------
390
391 --
392
393 -- Start of comments
394
395 --
396
397 -- API name : Get_ListName
398
399 -- Type : Private
400
401 -- Pre-reqs : None
402
403 -- Function : Return the name for specified list.
404
405 -- Initializes FND_MESSAGE and raises
406
407 -- exception if unable to locate name.
408
409 --
410
411 -- Parameters : p_list_id IN NUMBER Required
412
413 -- x_list_name OUT VARCHAR2 Required
414
415 --
416
417 -- Version : Initial version 1.0
418
419 --
420
421 -- End of comments
422
423 --
424
425 -----------------------------++++++-------------------------------
426
427 PROCEDURE Get_ListName
428
429 ( p_list_id IN NUMBER
430
431 , x_list_name OUT NOCOPY VARCHAR2)
432
433 IS
434
435 BEGIN
436
437 -- Get List Name
438
439 BEGIN
440
441 EXECUTE IMMEDIATE
442
443 'SELECT LIST_NAME
444
445 FROM AMS_LIST_HEADERS_VL
446
447 WHERE LIST_HEADER_ID = :list_id'
448
449 INTO x_list_name
450
451 USING p_list_id;
452
453 EXCEPTION
454
455 WHEN NO_DATA_FOUND THEN
456
457 x_list_name := NULL;
458
459 WHEN OTHERS THEN
460
461 Init_SqlErrmMsg(SQLERRM);
462
463 RAISE_APPLICATION_ERROR(-20999, g_error_message);
464
465 END;
466
467
468
469 END Get_ListName;
470
471
472
473 -----------------------------++++++-------------------------------
474
475 --
476
477 -- Start of comments
478
479 --
480
481 -- API name : Get_ScheduleName
482
483 -- Type : Private
484
485 -- Pre-reqs : None
486
487 -- Function : Return the name for specified schedule.
488
489 -- Initializes FND_MESSAGE and raises
490
491 -- exception if unable to locate name.
492
493 --
494
495 -- Parameters : p_schedule_id IN NUMBER Required
496
497 -- x_schedule_name OUT VARCHAR2 Required
498
499 --
500
501 -- Version : Initial version 1.0
502
503 --
504
505 -- End of comments
506
507 --
508
509 -----------------------------++++++-------------------------------
510
511 PROCEDURE Get_ScheduleName
512
513 ( p_schedule_id IN NUMBER
514
515 , x_schedule_name OUT NOCOPY VARCHAR2)
516
517 IS
518
519 BEGIN
520
521 -- Get Schedule Name
522
523 BEGIN
524
525 EXECUTE IMMEDIATE
526
527 'SELECT SCHEDULE_NAME
528
529 FROM AMS_CAMPAIGN_SCHEDULES_VL
530
531 WHERE SCHEDULE_ID = :schedule_id'
532
533 INTO x_schedule_name
534
535 USING p_schedule_id;
536
537 EXCEPTION
538
539 WHEN NO_DATA_FOUND THEN
540
541 x_schedule_name := NULL;
542
543 WHEN OTHERS THEN
544
545 Init_SqlErrmMsg(SQLERRM);
546
547 RAISE_APPLICATION_ERROR(-20999, g_error_message);
548
549 END;
550
551
552
553 END Get_ScheduleName;
554
555
556
557 -----------------------------++++++-------------------------------
558
559 --
560
561 -- Start of comments
562
563 --
564
565 -- API name : Get_ScheduleId
566
567 -- Type : Private
568
569 -- Pre-reqs : None
570
571 -- Function : Return the schedule id for specified list.
572
573 -- Initializes FND_MESSAGE and raises
574
575 -- exception if unable to locate name.
576
577 --
578
579 -- Parameters : p_list_id IN NUMBER Required
580
581 -- x_schedule_id OUT NUMBER Required
582
583 --
584
585 -- Version : Initial version 1.0
586
587 --
588
589 -- End of comments
590
591 --
592
593 -----------------------------++++++-------------------------------
594
595 PROCEDURE Get_ScheduleId
596
597 ( p_list_id IN NUMBER
598
599 , x_schedule_id OUT NOCOPY NUMBER)
600
601 IS
602
603 BEGIN
604
605 -- Get Campaign Schedule Id
606
607 BEGIN
608
609 EXECUTE IMMEDIATE
610
611 'SELECT LIST_USED_BY_ID
612
613 FROM AMS_ACT_LISTS
614
615 WHERE LIST_HEADER_ID = :list_id
616
617 AND LIST_USED_BY = ''CSCH''
618
619 AND LIST_ACT_TYPE = ''TARGET'''
620
621 INTO x_schedule_id
622
623 USING p_list_id;
627 WHEN OTHERS THEN
624
625 EXCEPTION
626
628
629 Init_SqlErrmMsg(SQLERRM);
630
631 RAISE_APPLICATION_ERROR(-20999, g_error_message);
632
633 END;
634
635
636
637 END Get_ScheduleId;
638
639
640
641 -----------------------------++++++-------------------------------
642
643 --
644
645 -- Start of comments
646
647 --
648
649 -- API name : Get_ListId
650
651 -- Type : Private
652
653 -- Pre-reqs : None
654
655 -- Function : Return the list header id for specified schedule.
656
657 -- Initializes FND_MESSAGE and raises
658
659 -- exception if unable to locate name.
660
661 --
662
663 -- Parameters : p_schedule_id IN NUMBER Required
664
665 -- x_list_id OUT NUMBER Required
666
667 --
668
669 -- Version : Initial version 1.0
670
671 --
672
673 -- End of comments
674
675 --
676
677 -----------------------------++++++-------------------------------
678
679 PROCEDURE Get_ListId
680
681 ( p_schedule_id IN NUMBER
682
683 , x_list_id OUT NOCOPY NUMBER)
684
685 IS
686
687 BEGIN
688
689 -- Get List Header Id
690
691 BEGIN
692
693 EXECUTE IMMEDIATE
694
695 'SELECT LIST_HEADER_ID
696
697 FROM IEC_G_AO_LISTS_V
698
699 WHERE SCHEDULE_ID = :schedule_id
700
701 AND LANGUAGE = USERENV(''LANG'')'
702
703 INTO x_list_id
704
705 USING p_schedule_id;
706
707 EXCEPTION
708
709 WHEN OTHERS THEN
710
711 Init_SqlErrmMsg(SQLERRM);
712
713 RAISE_APPLICATION_ERROR(-20999, g_error_message);
714
715 END;
716
717
718
719 END Get_ListId;
720
721
722
723 -----------------------------++++++-------------------------------
724
725 --
726
727 -- Start of comments
728
729 --
730
731 -- API name : LOCK_SCHEDULE
732
733 -- Type : Public
734
735 -- Pre-reqs : None
736
737 -- Function : Attempt to lock the schedule.
738
739 -- Parameters : P_SOURCE_ID IN NUMBER Required
740
741 -- P_SCHED_ID IN NUMBER Required
742
743 -- P_SERVER_ID IN NUMBER Required
744
745 -- P_LOCK_ATTEMPTS IN VARCHAR2 Required
746
747 -- P_ATTEMPT_INTERVAL IN VARCHAR2 Required
748
749 -- X_SUCCESS_FLAG OUT VARCHAR2 Required
750
751 -- Future : Not sure this should be an autonomous transaction. Leaving
752
753 -- for now.
754
755 --
756
757 -- Version : Initial version 1.0
758
759 --
760
761 -- End of comments
762
763 --
764
765 -----------------------------++++++-------------------------------
766
767 PROCEDURE Lock_Schedule
768
769 ( P_SOURCE_ID IN NUMBER
770
771 , P_SCHED_ID IN NUMBER
772
773 , P_SERVER_ID IN NUMBER
774
775 , P_LOCK_ATTEMPTS IN NUMBER
776
777 , P_ATTEMPT_INTERVAL IN NUMBER
778
779 , X_SUCCESS_FLAG OUT NOCOPY VARCHAR2
780
781 )
782
783 IS
784
785 PRAGMA AUTONOMOUS_TRANSACTION;
786
787 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Schedule';
788
789 l_server_id NUMBER;
790
791 l_lock_flag VARCHAR2(1);
792
793 l_refresh_rate NUMBER;
794
795 l_insert_flag VARCHAR2(1);
796
797 l_lock_attempts NUMBER;
798
799
800
801 BEGIN
802
803 l_server_id := -1;
804
805 l_lock_flag := 'N';
806
807 l_refresh_rate := -1;
808
809 l_insert_flag := 'N';
810
811 ----------------------------------------------------------------
812
813 -- initialize the local variables to indicate that we do not
814
815 -- have the lock.
816
817 ----------------------------------------------------------------
818
819 X_SUCCESS_FLAG := 'N';
820
821 l_lock_attempts := P_LOCK_ATTEMPTS;
822
823
824
825 LOOP
826
827 BEGIN
828
829
830
831 SELECT server_id
832
833 , nvl( lock_flag, 'N' )
834
835 INTO l_server_id
836
837 , l_lock_flag
838
839 FROM IEC_G_LIST_LOCK_STATES
840
841 WHERE CAMPAIGN_SCHEDULE_ID = P_SCHED_ID
842
843 FOR UPDATE;
844
845
846
847 ----------------------------------------------------------------
848
849 -- If either we were given the lock or the schedule is not locked
850
851 -- then we can proceed to locking the schedule.
852
853 ----------------------------------------------------------------
854
855 IF (l_server_id = P_SERVER_ID OR l_lock_flag = 'N')
856
857 THEN
858
859 X_SUCCESS_FLAG := 'Y';
860
861 EXIT;
862
863
864
865 ----------------------------------------------------------------
866
867 -- If another server has locked the schedule then we will wait
868
869 -- for 5 seconds and try again.
870
871 ----------------------------------------------------------------
872
873 ELSE
874
875 COMMIT;
876
877 l_lock_attempts := l_lock_attempts - 1;
878
879
880
881 ----------------------------------------------------------------
882
883 -- If already tried P_LOCK_ATTEMPTS times to gain lock
884
885 -- then exit out of loop otherwise sleep for P_ATTEMPT_INTERVAL
886
887 -- seconds and try again.
888
889 ----------------------------------------------------------------
890
891 IF (l_lock_attempts <= 0 )
892
893 THEN
894
895 EXIT;
896
897 ELSE
898
899 DBMS_LOCK.SLEEP(P_ATTEMPT_INTERVAL);
900
901 END IF;
902
903 END IF;
904
905
906
907 EXCEPTION
908
909 WHEN NO_DATA_FOUND
910
911 THEN
912
913 INSERT INTO IEC_G_LIST_LOCK_STATES
914
915 ( cpn_lock_state_id
916
917 , campaign_schedule_id
918
919 , server_id
920
921 , lock_flag
922
923 , object_version_number )
924
925 VALUES
929 , p_sched_id
926
927 ( iec_g_list_lock_states_s.nextval
928
930
931 , p_server_id
932
933 , 'Y'
934
935 , 1);
936
937 l_insert_flag := 'Y';
938
939
940
941 WHEN OTHERS
942
943 THEN
944
945 RAISE;
946
947 END;
948
949
950
951 END LOOP;
952
953
954
955 ----------------------------------------------------------------
956
957 -- If we could not get the lock try to see if the server with
958
959 -- the lock is still operating. If it isn't than grab the
960
961 -- entry and lock it.
962
963 ----------------------------------------------------------------
964
965 IF (X_SUCCESS_FLAG = 'N')
966
967 THEN
968
969
970
971 BEGIN
972
973 SELECT a.rt_refresh_rate
974
975 INTO l_refresh_rate
976
977 FROM ieo_svr_types_b a
978
979 , ieo_svr_servers b
980
981 WHERE a.type_id = b.type_id
982
983 AND b.server_id = l_server_id;
984
985 EXCEPTION
986
987 -- We need to let them know that the necessary parameter
988
989 -- does not exist.
990
991 WHEN OTHERS THEN
992
993 RAISE;
994
995 END;
996
997
998
999 l_refresh_rate := 3 * l_refresh_rate;
1000
1001
1002
1003 BEGIN
1004
1005
1006
1007 -- The server that grabbed the lock hasn't updated within
1008
1009 -- 3 X the refresh rate. We therefore allow the lock.
1010
1011 SELECT 'Y'
1012
1013 INTO X_SUCCESS_FLAG
1014
1015 FROM ieo_svr_rt_info
1016
1017 WHERE last_update_date < ( sysdate - l_refresh_rate / 1440 )
1018
1019 AND server_id = l_server_id;
1020
1021
1022
1023 EXCEPTION
1024
1025 WHEN NO_DATA_FOUND
1026
1027 THEN
1028
1029 -- The server that grabbed the lock has updated within
1030
1031 -- 3 X the refresh rate. We therefore do not allow the lock.
1032
1033 NULL;
1034
1035 WHEN OTHERS
1036
1037 THEN
1038
1039 RAISE;
1040
1041 END;
1042
1043
1044
1045
1046
1047 END IF;
1048
1049
1050
1051 IF X_SUCCESS_FLAG = 'Y' AND l_insert_flag = 'N'
1052
1053 THEN
1054
1055 BEGIN
1056
1057
1058
1059 IF l_lock_flag = 'N' THEN
1060
1061
1062
1063 ----------------------------------------------------------------
1064
1065 -- Attempt to update the lock entry if it isn't currently locked.
1066
1067 ----------------------------------------------------------------
1068
1069 UPDATE IEC_G_LIST_LOCK_STATES
1070
1071 SET LOCK_FLAG = 'Y'
1072
1073 , SERVER_ID = P_SERVER_ID
1074
1075 WHERE CAMPAIGN_SCHEDULE_ID = P_SCHED_ID
1076
1077 AND LOCK_FLAG = 'N';
1078
1079
1080
1081 ----------------------------------------------------------------
1082
1083 -- If we were unable to lock the schedule then set the success
1084
1085 -- flag to 'N'.
1086
1087 ----------------------------------------------------------------
1088
1089 IF SQL%ROWCOUNT = 0 THEN
1090
1091 X_SUCCESS_FLAG := 'N';
1092
1093 END IF;
1094
1095
1096
1097 ELSIF l_server_id <> P_SERVER_ID AND l_lock_flag = 'Y' THEN
1098
1099
1100
1101 ----------------------------------------------------------------
1102
1103 -- Attempt to update the lock entry if it isn't currently locked.
1104
1105 ----------------------------------------------------------------
1106
1107 UPDATE IEC_G_LIST_LOCK_STATES
1108
1109 SET LOCK_FLAG = 'Y'
1110
1111 , SERVER_ID = P_SERVER_ID
1112
1113 WHERE CAMPAIGN_SCHEDULE_ID = P_SCHED_ID;
1114
1115
1116
1117 ----------------------------------------------------------------
1118
1119 -- If we were unable to lock the schedule then set the success
1120
1121 -- flag to 'N'.
1122
1123 ----------------------------------------------------------------
1124
1125 IF SQL%ROWCOUNT = 0 THEN
1126
1127 X_SUCCESS_FLAG := 'N';
1128
1129 END IF;
1130
1131
1132
1133 END IF;
1134
1135
1136
1137 EXCEPTION
1138
1139 WHEN OTHERS
1140
1141 THEN
1142
1143 RAISE;
1144
1145 END;
1146
1147 END IF;
1148
1149
1150
1151 ----------------------------------------------------------------
1152
1156
1153 -- Commit all updates to the tables.
1154
1155 ----------------------------------------------------------------
1157 COMMIT;
1158
1159
1160
1161 EXCEPTION
1162
1163 WHEN OTHERS THEN
1164
1165 ROLLBACK;
1166
1167 Init_SqlErrmMsg(SQLERRM);
1168
1169 RAISE_APPLICATION_ERROR(-20999, g_error_message);
1170
1171
1172
1173 END Lock_Schedule;
1174
1175
1176
1177 -----------------------------++++++-------------------------------
1178
1179 --
1180
1181 -- Start of comments
1182
1183 --
1184
1185 -- API name : UNLOCK_SCHEDULE
1186
1187 -- Type : Public
1188
1189 -- Pre-reqs : None
1190
1191 -- Function : Attempt to unlock the schedule.
1192
1193 -- Parameters : P_SOURCE_ID IN NUMBER Required
1194
1195 -- P_SCHED_ID IN NUMBER Required
1196
1197 -- P_SERVER_ID IN NUMBER Required
1198
1199 -- X_SUCCESS_FLAG OUT VARCHAR2 Required
1200
1201 -- Future : Not sure this should be an autonomous transaction. Leaving
1202
1203 -- for now.
1204
1205 --
1206
1207 -- Version : Initial version 1.0
1208
1209 --
1210
1211 -- End of comments
1212
1213 --
1214
1215 -----------------------------++++++-------------------------------
1216
1217 PROCEDURE Unlock_Schedule
1218
1219 ( P_SOURCE_ID IN NUMBER
1220
1221 , P_SCHED_ID IN NUMBER
1222
1223 , P_SERVER_ID IN NUMBER
1224
1225 , X_SUCCESS_FLAG OUT NOCOPY VARCHAR2
1226
1227 )
1228
1229 IS
1230
1231 PRAGMA AUTONOMOUS_TRANSACTION;
1232
1233 l_api_name CONSTANT VARCHAR2(30) := 'Unlock_Schedule';
1234
1235
1236
1237 BEGIN
1238
1239
1240
1241 ----------------------------------------------------------------
1242
1243 -- Attempt to update the lock entry.
1244
1245 ----------------------------------------------------------------
1246
1247 UPDATE IEC_G_LIST_LOCK_STATES
1248
1249 SET LOCK_FLAG = 'N'
1250
1251 , SERVER_ID = P_SERVER_ID
1252
1253 WHERE CAMPAIGN_SCHEDULE_ID = P_SCHED_ID
1254
1255 AND SERVER_ID = P_SERVER_ID;
1256
1257
1258
1259 ----------------------------------------------------------------
1260
1261 -- If we weren't able to unlock the schedule b/c this server
1262
1263 -- doesn't have a lock on the schedule, then set the success
1264
1265 -- flag to 'N'.
1266
1267 ----------------------------------------------------------------
1268
1269 IF SQL%ROWCOUNT = 0 THEN
1270
1271 X_SUCCESS_FLAG := 'N';
1272
1273 ELSE
1274
1275 X_SUCCESS_FLAG := 'Y';
1276
1277 END IF;
1278
1279
1280
1281 ----------------------------------------------------------------
1282
1283 -- Commit all updates to the tables.
1284
1285 ----------------------------------------------------------------
1286
1287 COMMIT;
1288
1289
1290
1291 EXCEPTION
1292
1293 WHEN OTHERS THEN
1294
1295 ROLLBACK;
1296
1297 Init_SqlErrmMsg(SQLERRM);
1298
1299 RAISE_APPLICATION_ERROR(-20999, g_error_message);
1300
1301
1302
1303 END Unlock_Schedule;
1304
1305
1306
1307 -----------------------------++++++-------------------------------
1308
1309 --
1310
1311 -- Start of comments
1312
1313 --
1314
1315 -- API name : LOCK_SCHEDULE
1316
1317 -- Type : Public
1318
1319 -- Pre-reqs : None
1320
1321 -- Function : Either attempt to gain a lock on the schedule or unlock the schedule.
1322
1323 --
1324
1325 -- Parameters : P_SOURCE_ID IN NUMBER Required
1326
1327 -- P_SCHED_ID IN NUMBER Required
1328
1329 -- P_SERVER_ID IN NUMBER Required
1330
1331 -- P_LOCK_FLAG IN NUMBER Required
1332
1333 -- X_SUCCESS_FLAG OUT VARCHAR2 Required
1334
1335 -- Future : Not sure this should be an autonomous transaction. Leaving
1336
1337 -- for now.
1338
1339 --
1340
1341 -- Version : Initial version 1.0
1342
1343 --
1344
1345 -- End of comments
1346
1347 --
1348
1349 -----------------------------++++++-------------------------------
1350
1351 PROCEDURE Lock_Schedule
1352
1353 ( P_SOURCE_ID IN NUMBER
1354
1355 , P_SCHED_ID IN NUMBER
1356
1357 , P_SERVER_ID IN NUMBER
1358
1359 , P_LOCK_FLAG IN VARCHAR2
1360
1361 , X_SUCCESS_FLAG OUT NOCOPY VARCHAR2
1362
1363 )
1364
1365 IS
1366
1367 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Schedule';
1368
1369 l_attempt_interval NUMBER := 5;
1370
1371 l_lock_attempts NUMBER := 20;
1372
1373 l_success_flag VARCHAR2(1);
1374
1375
1376
1377 BEGIN
1378
1379
1380
1381 IF P_LOCK_FLAG = 'Y'
1382
1383 THEN
1384
1385
1386
1387 LOCK_SCHEDULE
1388
1389 ( P_SOURCE_ID => P_SOURCE_ID
1390
1391 , P_SCHED_ID => P_SCHED_ID
1392
1393 , P_SERVER_ID => P_SERVER_ID
1394
1395 , P_LOCK_ATTEMPTS => l_lock_attempts
1396
1397 , P_ATTEMPT_INTERVAL => l_attempt_interval
1398
1399 , X_SUCCESS_FLAG => l_success_flag
1400
1401 );
1402
1403 ELSE
1404
1405 UNLOCK_SCHEDULE
1406
1407 ( P_SOURCE_ID => P_SOURCE_ID
1408
1409 , P_SCHED_ID => P_SCHED_ID
1410
1411 , P_SERVER_ID => P_SERVER_ID
1412
1413 , X_SUCCESS_FLAG => l_success_flag
1414
1415 );
1416
1417 END IF;
1418
1419
1420
1421 X_SUCCESS_FLAG := l_success_flag;
1422
1423
1424
1425 EXCEPTION
1426
1427 WHEN OTHERS THEN
1428
1429 RAISE;
1430
1431 END Lock_Schedule;
1432
1433 END IEC_COMMON_UTIL_PVT;
1434