1 PACKAGE BODY IEC_RECOVER_PVT AS
2 /* $Header: IECOCRCB.pls 115.30 2004/05/19 17:14:04 minwang ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'IEC_RECOVER_PVT';
5 g_error_msg VARCHAR2(2048);
6
7 G_RECOVER_ACTION_TYPE CONSTANT NUMBER := 2;
8 G_NUM_MINUTES_IN_DAY CONSTANT NUMBER := 1440;
9 G_SYSTEM_OUTCOME_CODE CONSTANT NUMBER := 37;
10 G_LOST_RESULT_CODE CONSTANT NUMBER := 11;
11 G_FUNCTIONAL CONSTANT NUMBER := 2;
12 G_PERFORMANCE CONSTANT NUMBER := 1;
13 G_SOURCE_ID NUMBER;
14
15
16 PROCEDURE Log ( p_activity_desc IN VARCHAR2
17 , p_method_name IN VARCHAR2
18 , p_sub_method IN VARCHAR2
19 , p_sql_code IN NUMBER
20 , p_sql_errm IN VARCHAR2)
21 IS
22 l_error_msg VARCHAR2(2048);
23 BEGIN
24
25 IEC_OCS_LOG_PVT.LOG_INTERNAL_PLSQL_ERROR
26 ( 'IEC_RECOVER_PVT'
27 , p_method_name
28 , p_sub_method
29 , p_activity_desc
30 , p_sql_code
31 , p_sql_errm
32 , l_error_msg
33 );
34
35 END Log;
36
37
38 -----------------------------++++++-------------------------------
39 --
40 -- Start of comments
41 --
42 -- API name : RECOVER_LIST_ENTRIES
43 -- Type : Public
44 -- Pre-reqs : None
45 -- Function : Recover entries that have been checked out of AMS_LIST_ENTRIES for longer
46 -- than the time sent in as P_LOST_INTERVAL.
47 -- Parameters : P_LOST_INTERVAL IN NUMBER Required
48 -- X_RETURN_CODE OUT VARCHAR2 Required
49 --
50 -- Version : Initial version 1.0
51 --
52 -- End of comments
53 --
54 -----------------------------++++++-------------------------------
55 PROCEDURE RECOVER_LIST_ENTRIES
56 ( P_SOURCE_ID IN NUMBER
57 , P_LIST_ID IN NUMBER
58 , P_LOST_INTERVAL IN NUMBER
59 , X_ACTION_ID IN OUT NOCOPY NUMBER
60 )
61 IS
62
63 l_api_name CONSTANT VARCHAR2(30) := 'RECOVER_LIST_ENTRIES';
64 TYPE EntryCollection IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
65 TYPE FlagCollection IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
66 l_update_count NUMBER := 0;
67 l_update_list EntryCollection;
68 l_entry_list EntryCollection;
69 l_cache_list EntryCollection;
70 l_subset_collection EntryCollection;
71 l_update_subset_collection EntryCollection;
72 l_callback_collection FlagCollection;
73 l_recycle_list NUMBER := 0;
74 l_ok_list NUMBER := 0;
75 l_lost_interval NUMBER;
76 l_list_entry_found NUMBER;
77 l_returns_id NUMBER;
78 l_user_id NUMBER;
79 l_err_msg VARCHAR2(100);
80 l_update_date DATE;
81 l_check_date DATE;
82 l_recycle_flag VARCHAR2(10);
83 l_batch_entries CONSTANT NUMBER := 10000;
84 l_entry_count NUMBER;
85 l_watermark NUMBER := 0;
86 l_outer_itertion NUMBER := 0;
87 l_inner_itertion NUMBER := 0;
88 l_start_time NUMBER ;
89 l_end_time NUMBER ;
90 l_log_status VARCHAR2(1);
91 l_total_count NUMBER;
92 l_sequence NUMBER;
93 l_subset_id NUMBER;
94 l_cache_id NUMBER;
95 l_callback_flag VARCHAR2(1);
96 l_no_sequence_except EXCEPTION;
97 l_status_code VARCHAR2(1);
98 l_action_id NUMBER;
99 BEGIN
100
101 l_user_id := NVL(FND_GLOBAL.USER_ID, -1);
102 l_entry_count := l_batch_entries;
103 l_subset_id := -1;
104 l_cache_id := -1;
105 SAVEPOINT RECOVER_SUBSET_START;
106
107 l_action_id := x_action_id;
108
109 ----------------------------------------------------------------
110 -- The percentage of the day that the entry can reside in
111 -- the cache prior to being thought of as LOST.
112 ----------------------------------------------------------------
113 l_lost_interval := P_LOST_INTERVAL / G_NUM_MINUTES_IN_DAY;
114
115 ----------------------------------------------------------------
116 -- Retrieve the sysdate once to use in the update returns table
117 -- buld insert.
118 ----------------------------------------------------------------
119 SELECT SYSDATE
120 INTO l_update_date
121 FROM DUAL;
122
123 ----------------------------------------------------------------
124 -- this loop returns all of the entries that have been checked
125 -- out of the list for too long.
126 ----------------------------------------------------------------
127 FOR entry_rec IN (SELECT LIST_ENTRY_ID
128 , RETURNS_ID
129 FROM IEC_G_RETURN_ENTRIES
130 WHERE LIST_HEADER_ID = P_LIST_ID
131 AND RECORD_OUT_FLAG = 'Y'
132 AND DO_NOT_USE_FLAG = 'N'
133 AND NVL(RECYCLE_FLAG, 'N') = 'N'
134 AND l_update_date > RECORD_RELEASE_TIME + l_lost_interval
135 ORDER BY LIST_ENTRY_ID)
136 LOOP
137
138 l_update_count := l_update_list.COUNT + 1;
139 l_update_list(l_update_count) := entry_rec.RETURNS_ID;
140
141 IF l_update_list.COUNT = 500
142 THEN
143
144 IF (l_action_id = -1)
145 THEN
146
147 SELECT IEC_G_RETURN_ENTRY_ACTION_S.NEXTVAL
148 INTO l_action_id
149 FROM DUAL;
150
151 X_ACTION_ID := l_action_id;
152
153 END IF;
154
155 -- might want to get sysdate once and use.
156 FORALL j IN 1..L_UPDATE_LIST.COUNT
157 UPDATE IEC_G_RETURN_ENTRIES
158 SET RECORD_OUT_FLAG = 'N'
159 , LAST_UPDATE_DATE = SYSDATE
160 , LAST_UPDATED_BY = l_user_id
161 , CHECKIN_ACTION_TYPE = G_RECOVER_ACTION_TYPE
162 , CHECKIN_ACTION_TIME = SYSDATE
163 , CHECKIN_ACTION_ID = l_action_id
164 WHERE RETURNS_ID = l_update_list(j);
165
166 l_update_list.DELETE;
167 COMMIT;
168 END IF;
169
170 END LOOP; -- end entry loop
171
172 IF l_update_list.COUNT > 0
173 THEN
174
175 IF (l_action_id = -1)
176 THEN
177 SELECT IEC_G_RETURN_ENTRY_ACTION_S.NEXTVAL
178 INTO l_action_id
179 FROM DUAL;
180
181 X_ACTION_ID := l_action_id;
182
183 END IF;
184
185 -- might want to get sysdate once and use.
186 FORALL j IN 1..L_UPDATE_LIST.COUNT
187 UPDATE IEC_G_RETURN_ENTRIES
188 SET RECORD_OUT_FLAG = 'N'
189 , CHECKIN_ACTION_TYPE = G_RECOVER_ACTION_TYPE
190 , CHECKIN_ACTION_TIME = SYSDATE
191 , CHECKIN_ACTION_ID = l_action_id
192 , LAST_UPDATE_DATE = SYSDATE
193 , LAST_UPDATED_BY = l_user_id
194 WHERE RETURNS_ID = l_update_list(j);
195
196 l_update_list.DELETE;
197 COMMIT;
198 END IF;
199
200
201
202 EXCEPTION
203 WHEN FND_API.G_EXC_ERROR THEN
204 ROLLBACK;
205 RAISE;
206 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
207 ROLLBACK;
208 RAISE;
209
210 ----------------------------------------------------------------
211 -- If an anonymous exception has been thrown then
212 -- the we must log an internal PLSQL error and
213 -- set the return status flag and return to the calling
214 -- procedure.
215 ----------------------------------------------------------------
216 WHEN OTHERS THEN
217 Log( 'Recovering List Entries on list ' || p_list_id
218 , l_api_name
219 , 'MAIN'
220 , SQLCODE
221 , SQLERRM
222 );
223 ROLLBACK;
224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
225
226 END RECOVER_LIST_ENTRIES;
227
228 -----------------------------++++++-------------------------------
229 --
230 -- Start of comments
231 --
232 -- API name : RECOVER_ENTRIES
233 -- Type : Public
234 -- Pre-reqs : None
235 -- Function : Recover entries that have been checked out of AMS_LIST_ENTRIES for longer
236 -- than the time sent in as P_LOST_INTERVAL.
237 -- Parameters : P_LOST_INTERVAL IN NUMBER Required
238 -- X_RETURN_CODE OUT VARCHAR2 Required
239 --
240 -- Version : Initial version 1.0
241 --
242 -- End of comments
243 --
244 -----------------------------++++++-------------------------------
245 /* Called by the Recover Plugin. */
246 PROCEDURE RECOVER_SCHED_ENTRIES
247 ( P_SOURCE_ID IN NUMBER
248 , P_SCHED_ID IN NUMBER
249 , P_LOST_INTERVAL IN NUMBER
250 , X_ACTION_ID OUT NOCOPY NUMBER
251 )
252 IS
253 l_status_code VARCHAR2(1);
254 l_log_status VARCHAR2(1);
255 l_api_name CONSTANT VARCHAR2(30) := 'RECOVER_SCHED_ENTRIES';
256 l_action_id NUMBER;
257
258 BEGIN
259 l_status_code := FND_API.G_RET_STS_SUCCESS;
260 x_action_id := NULL;
261
262 ----------------------------------------------------------------
263 -- Set the source id to use for logging in
264 -- the rest of the package.
265 ----------------------------------------------------------------
266 G_SOURCE_ID := P_SOURCE_ID;
267
268 ----------------------------------------------------------------
269 -- Initialize the return code to 'S'
270 ----------------------------------------------------------------
271 x_action_id := -1;
272 l_action_id := -1;
273
274 ----------------------------------------------------------------
275 -- Loop thru the set of executing Lists. An executing
276 -- List is one that has been assigned the AO activity, has
277 -- a status of 'ACTIVE' and has the target group associated
278 -- with it that has a status of 'EXECUTING'.
279 ----------------------------------------------------------------
280 FOR schedule_rec IN (SELECT LIST_HEADER_ID
281 FROM IEC_G_EXECUTING_LISTS_V
282 WHERE SCHEDULE_ID = P_SCHED_ID)
283 LOOP
284
285 RECOVER_LIST_ENTRIES( P_SOURCE_ID
286 , schedule_rec.LIST_HEADER_ID
287 , P_LOST_INTERVAL
288 , l_action_id);
289 END LOOP; -- end schedule loop
290
291
292 COMMIT;
293
294 X_ACTION_ID := l_action_id;
295
296 EXCEPTION
297 ----------------------------------------------------------------
298 -- If either of the two FND_API exceptions have been thrown then
299 -- the procedure has already logged the error and we now just
300 -- set the return status flag and return to the calling
301 -- procedure.
302 ----------------------------------------------------------------
303 WHEN FND_API.G_EXC_ERROR THEN
304 ROLLBACK;
305 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
306 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
307 ROLLBACK;
308 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
309
310 ----------------------------------------------------------------
311 -- If an anonymous exception has been thrown then
312 -- the we must log an internal PLSQL error and
313 -- set the return status flag and return to the calling
314 -- procedure.
315 ----------------------------------------------------------------
316 WHEN OTHERS THEN
317 Log( 'Recovering Sched Entries'
318 , l_api_name
319 , 'MAIN'
320 , SQLCODE
321 , SQLERRM
322 );
323 ROLLBACK;
324 RAISE_APPLICATION_ERROR(-20999, g_error_msg);
325
326 END RECOVER_SCHED_ENTRIES;
327
328 END IEC_RECOVER_PVT;
[Home] [Help]