1 PACKAGE BODY FND_OAM_DSCRAM_BUNDLES_PKG as
2 /* $Header: AFOAMDSBDLB.pls 120.8 2006/05/16 20:47:20 ilawler noship $ */
3
4 ----------------------------------------
5 -- Private Body Constants
6 ----------------------------------------
7 PKG_NAME CONSTANT VARCHAR2(20) := 'DSCRAM_BUNDLES_PKG.';
8
9 ----------------------------------------
10 -- Private Body Variables
11 ----------------------------------------
12 TYPE b_bundle_cache_type IS RECORD
13 (
14 initialized BOOLEAN := FALSE,
15 bundle_id NUMBER := NULL,
16 worker_id NUMBER := NULL,
17 workers_allowed NUMBER := NULL,
18 batch_size NUMBER := NULL,
19 min_parallel_unit_weight NUMBER := NULL,
20 last_validated DATE := NULL,
21 last_validation_ret_sts VARCHAR2(6) := NULL
22 );
23 b_bundle_info b_bundle_cache_type;
24
25 ----------------------------------------
26 -- Public/Private Procedures/Functions
27 ----------------------------------------
28
29 -- Public
30 FUNCTION GET_BUNDLE_ID
31 RETURN NUMBER
32 IS
33 BEGIN
34 IF NOT b_bundle_info.initialized THEN
35 RAISE NO_DATA_FOUND;
36 END IF;
37
38 RETURN b_bundle_info.bundle_id;
39 END;
40
41 -- Public
42 FUNCTION GET_WORKER_ID
43 RETURN NUMBER
44 IS
45 BEGIN
46 IF NOT b_bundle_info.initialized THEN
47 RAISE NO_DATA_FOUND;
48 END IF;
49
50 RETURN b_bundle_info.worker_id;
51 END;
52
53 -- Public
54 FUNCTION GET_WORKERS_ALLOWED
55 RETURN NUMBER
56 IS
57 BEGIN
58 IF NOT b_bundle_info.initialized THEN
59 RAISE NO_DATA_FOUND;
60 END IF;
61
62 RETURN b_bundle_info.workers_allowed;
63 END;
64
65 -- Public
66 FUNCTION GET_BATCH_SIZE
67 RETURN NUMBER
68 IS
69 BEGIN
70 IF NOT b_bundle_info.initialized THEN
71 RAISE NO_DATA_FOUND;
72 END IF;
73
74 RETURN b_bundle_info.batch_size;
75 END;
76
77 -- Public
78 FUNCTION GET_MIN_PARALLEL_UNIT_WEIGHT
79 RETURN NUMBER
80 IS
81 BEGIN
82 IF NOT b_bundle_info.initialized THEN
83 RAISE NO_DATA_FOUND;
84 END IF;
85
86 RETURN b_bundle_info.min_parallel_unit_weight;
87 END;
88
89 -- Private
90 -- Called by execute_bundle before assigning the worker to sanity check
91 -- the input args and make sure the bundle is prepared for another worker.
92 -- Return Statuses:
93 -- SUCCESS, ERROR, ERROR_UNEXP, SKIPPED, FULL
94 -- -Converted- : PROCESSED, STOPPED, ERROR_FATAL, ERROR_UNKNOWN
95 FUNCTION VALIDATE_START_EXECUTION(p_run_id IN NUMBER,
96 p_bundle_id IN NUMBER,
97 x_return_status OUT NOCOPY VARCHAR2,
98 x_return_msg OUT NOCOPY VARCHAR2)
99 RETURN BOOLEAN
100 IS
101 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_START_EXECUTION';
102
103 l_status VARCHAR2(30);
104 l_workers_allowed NUMBER(15);
105 l_workers_assigned NUMBER(15);
106
107 l_return_status VARCHAR2(6);
108 l_return_msg VARCHAR2(4000);
109 CURSOR C1
110 IS
111 SELECT bundle_status, workers_allowed, workers_assigned
112 FROM fnd_oam_dscram_bundles
113 WHERE run_id = p_run_id
114 AND bundle_id = p_bundle_id;
115 BEGIN
116 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
117 x_return_status := FND_API.G_RET_STS_ERROR;
118 x_return_msg := '';
119
120 -- first validate the run
121 IF NOT FND_OAM_DSCRAM_RUNS_PKG.VALIDATE_START_EXECUTION(p_run_id,
122 l_return_status,
123 l_return_msg) THEN
124 -- the run has an invalid status, tell the execute to stop the bundle and return.
125 x_return_status := FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_STOPPED;
126 x_return_msg := '[Run Validation Failed]:(Status('||l_return_status||'), Msg('||l_return_msg||'))';
127 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
128 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
129 RETURN FALSE;
130 END IF;
131
132 --fetch necessary bundle attributes
133 OPEN C1;
134 FETCH C1 INTO l_status, l_workers_allowed, l_workers_assigned;
135 IF C1%NOTFOUND THEN
136 x_return_msg := 'Invalid run_id: ('||p_run_id||') and/or bundle_id('||p_bundle_id||')';
137 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
138 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
139 RETURN FALSE;
140 END IF;
141 CLOSE C1;
142
143 --make sure the bundle is in a startable state
144 IF NOT FND_OAM_DSCRAM_UTILS_PKG.STATUS_IS_EXECUTABLE(l_status) THEN
145 -- report the true status of the bundle to execute to pass on to execute's caller
146 x_return_status := FND_OAM_DSCRAM_UTILS_PKG.CONV_VALIDATE_START_STS_TO_RET(l_status);
147 IF FND_OAM_DSCRAM_UTILS_PKG.RET_STS_IS_ERROR(x_return_status) THEN
148 x_return_msg := 'Invalid bundle status('||l_status||')';
149 fnd_oam_debug.log(1, l_ctxt, x_return_msg);
150 END IF;
151 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
152 RETURN FALSE;
153 END IF;
154
155 --make sure there's a worker spot, assign will allocate but this check helps short
156 --circuit the lock.
157 IF l_workers_assigned >= l_workers_allowed THEN
158 x_return_status := FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_FULL;
159 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
160 RETURN FALSE;
161 END IF;
162
163 --success
164 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
165 x_return_status := FND_API.G_RET_STS_SUCCESS;
166 RETURN TRUE;
167 EXCEPTION
168 WHEN OTHERS THEN
169 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
170 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
172 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
173 RETURN FALSE;
174 END;
175
176 -- Public
177 -- Return Statuses:
178 -- SUCCESS, ERROR, ERROR_UNEXP, STOPPED
179 -- -Converted- : PROCESSED, STOPPED, ERROR_FATAL, ERROR_UNKNOWN
180 FUNCTION VALIDATE_CONTINUED_EXECUTION(p_force_query IN BOOLEAN,
181 p_recurse IN BOOLEAN,
182 x_return_status OUT NOCOPY VARCHAR2,
183 x_return_msg OUT NOCOPY VARCHAR2)
184 RETURN BOOLEAN
185 IS
186 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_CONTINUED_EXECUTION';
187
188 l_status VARCHAR2(30) := NULL;
189 l_return_status VARCHAR2(6);
190 l_return_msg VARCHAR2(4000);
191
192 CURSOR C1
193 IS
194 SELECT bundle_status
195 FROM fnd_oam_dscram_bundles
196 WHERE bundle_id = b_bundle_info.bundle_id;
197 BEGIN
198 x_return_status := FND_API.G_RET_STS_ERROR;
199 x_return_msg := '';
200
201 -- make sure the state's initialized
202 IF NOT b_bundle_info.initialized THEN
203 RAISE NO_DATA_FOUND;
204 END IF;
205
206 -- check if we should do work or if we can presume the cached status
207 IF (p_force_query OR
208 FND_OAM_DSCRAM_UTILS_PKG.VALIDATION_DUE(b_bundle_info.last_validated)) THEN
209
210 fnd_oam_debug.log(1, l_ctxt, '>RE-QUERYING<');
211
212 -- re-init the cached fields to allow easy exit
213 b_bundle_info.last_validation_ret_sts := x_return_status;
214 b_bundle_info.last_validated := SYSDATE;
215
216 --otherwise, fetch necessary run attributes and evaluate
217 OPEN C1;
218 FETCH C1 INTO l_status;
219 IF C1%NOTFOUND THEN
220 --shouldn't happen since we're using the cache
221 x_return_msg := 'Invalid cached bundle_id: '||b_bundle_info.bundle_id;
222 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
223 RETURN FALSE;
224 END IF;
225 CLOSE C1;
226
227 --make sure the bundle has been marked as some valid processing state
228 IF NOT FND_OAM_DSCRAM_UTILS_PKG.STATUS_IS_PROCESSING(l_status) THEN
229 x_return_status := FND_OAM_DSCRAM_UTILS_PKG.CONV_VALIDATE_CONT_STS_TO_RET(l_status);
230 b_bundle_info.last_validation_ret_sts := x_return_status;
231 IF x_return_status <> FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_PROCESSED THEN
232 x_return_msg := 'Invalid bundle status('||l_status||')';
233 fnd_oam_debug.log(1, l_ctxt, x_return_msg);
234 END IF;
235 RETURN FALSE;
236 END IF;
237 x_return_status := FND_API.G_RET_STS_SUCCESS;
238 ELSE
239 x_return_status := b_bundle_info.last_validation_ret_sts;
240 END IF;
241
242 -- make a recursive call to the run if required
243 IF p_recurse THEN
244 IF NOT FND_OAM_DSCRAM_RUNS_PKG.VALIDATE_CONTINUED_EXECUTION(p_force_query,
245 TRUE,
246 l_return_status,
247 l_return_msg) THEN
248 -- the run has an invalid status, tell the execute to stop the bundle
249 x_return_status := FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_STOPPED;
250 x_return_msg := '[Continued Run Validation Failed]:(Status('||l_return_status||'), Msg('||l_return_msg||'))';
251 fnd_oam_debug.log(1, l_ctxt, x_return_msg);
252 RETURN FALSE;
253 END IF;
254 END IF;
255
256 --success
257 b_bundle_info.last_validation_ret_sts := x_return_status;
258 b_bundle_info.last_validated := SYSDATE;
259 RETURN (x_return_status = FND_API.G_RET_STS_SUCCESS);
260 EXCEPTION
261 WHEN OTHERS THEN
262 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
263 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
265 b_bundle_info.last_validation_ret_sts := x_return_status;
266 b_bundle_info.last_validated := SYSDATE;
267 RETURN FALSE;
268 END;
269
270 -- Private: Autonomous Txn
271 -- Before a call to execute_bundle can start doing work, it needs to call this procedure
272 -- to allocate a space in the bundle for this worker. Since this updates the bundle, this
273 -- is done in an autonomous transaction that locks the bundle temporarily.
274 -- Invariant: VALIDATE_FOR_EXECUTION should have preceeded this to make sure the status was
275 -- startable.
276 -- Return Statuses:
277 -- SUCCESS, ERROR, ERROR_UNEXP, FULL
278 PROCEDURE ASSIGN_WORKER_TO_BUNDLE(p_run_id IN NUMBER,
279 p_bundle_id IN NUMBER,
280 px_worker_id IN OUT NOCOPY NUMBER,
281 x_return_status OUT NOCOPY VARCHAR2,
282 x_return_msg OUT NOCOPY VARCHAR2)
283 IS
284 PRAGMA AUTONOMOUS_TRANSACTION;
285
286 l_ctxt VARCHAR2(60) := PKG_NAME||'ASSIGN_WORKER_TO_BUNDLE';
287
288 l_workers_allowed NUMBER;
289 l_workers_assigned NUMBER;
290 l_batch_size NUMBER;
291 l_min_parallel_unit_weight NUMBER;
292 l_status VARCHAR2(30);
293 l_stat_id NUMBER;
294 l_new_worker_id NUMBER := NULL;
295
296 l_return_status VARCHAR2(6);
297 l_return_msg VARCHAR2(4000);
298 BEGIN
299 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
300 x_return_status := FND_API.G_RET_STS_ERROR;
301 x_return_msg := '';
302
303 -- assign the worker to the run first
304 -- first validate the run
305 FND_OAM_DSCRAM_RUNS_PKG.ASSIGN_WORKER_TO_RUN(p_run_id,
306 l_return_status,
307 l_return_msg);
308 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
309 x_return_status := l_return_status;
310 x_return_msg := '[Assigning Worker to Run Failed]:('||l_return_msg||')';
311 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
312 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
313 RETURN;
314 END IF;
315
316 -- Do a locking select without a pre-select since we always have to update the bundle
317 -- row to add to the # of workers assigned. Also updates the status to started if it
318 -- hasn't been set yet.
319 SELECT bundle_status, workers_allowed, workers_assigned, batch_size, min_parallel_unit_weight
320 INTO l_status, l_workers_allowed, l_workers_assigned, l_batch_size, l_min_parallel_unit_weight
321 FROM fnd_oam_dscram_bundles
322 WHERE bundle_id = p_bundle_id
323 FOR UPDATE;
324
325 fnd_oam_debug.log(1, l_ctxt, 'Bundle Status: '||l_status);
326 fnd_oam_debug.log(1, l_ctxt, 'Workers Allowed: '||l_workers_allowed);
327 fnd_oam_debug.log(1, l_ctxt, 'Workers Assigned: '||l_workers_assigned);
328 fnd_oam_debug.log(1, l_ctxt, 'Argument Worker ID: '||to_char(px_worker_id));
329
330 -- make sure the status is still good
331 IF NOT FND_OAM_DSCRAM_UTILS_PKG.STATUS_IS_EXECUTABLE(l_status) THEN
332 x_return_status := FND_OAM_DSCRAM_UTILS_PKG.CONV_VALIDATE_START_STS_TO_RET(l_status);
333 IF FND_OAM_DSCRAM_UTILS_PKG.RET_STS_IS_ERROR(x_return_status) THEN
334 x_return_msg := 'Invalid status for assign: ('||l_status||')';
335 fnd_oam_debug.log(1, l_ctxt, x_return_msg);
336 END IF;
337 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
338 ROLLBACK;
339 RETURN;
340 END IF;
341
342 -- make sure there's room for this worker
343 IF l_workers_assigned >= l_workers_allowed THEN
344 x_return_status := FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_FULL;
345 x_return_msg := 'Workers Assigned('||l_workers_assigned||') >= workers allowed('||l_workers_allowed||')';
346 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
347 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
348 ROLLBACK;
349 RETURN;
350 END IF;
351
352 fnd_oam_debug.log(1, l_ctxt, 'Creating a New Worker...');
353 IF l_status <> FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSING THEN
354 IF l_workers_assigned = 0 THEN
355 --create a stats entry
356 FND_OAM_DSCRAM_STATS_PKG.CREATE_ENTRY(p_source_object_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_BUNDLE,
357 p_source_object_id => p_bundle_id,
358 p_start_time => SYSDATE,
359 p_prestart_status => l_status,
360 x_stat_id => l_stat_id);
361 --update the status
362 UPDATE fnd_oam_dscram_bundles
363 SET bundle_status = FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSING,
364 STATS_FINISHED = FND_API.G_FALSE
365 WHERE bundle_id = p_bundle_id;
366 ELSE
367 --the bundle isn't processing but somebody's set it to processing, this shouldn't happen
368 x_return_msg := 'Bundle Status ('||l_status||') is not in-progress but the number of workers assigned('||l_workers_assigned||') is nonzero.';
369 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
370 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
371 ROLLBACK;
372 RETURN;
373 END IF;
374 END IF;
375
376 -- sanity check the worker id
377 IF px_worker_id IS NOT NULL AND (px_worker_id <= 0 OR px_worker_id > l_workers_allowed) THEN
378 fnd_oam_debug.log(1, l_ctxt, 'Invalid argument worker_id, resetting to NULL.');
379 px_worker_id := NULL;
380 END IF;
381 -- default the worker_id if one was not provided
382 IF px_worker_id IS NULL THEN
383 px_worker_id := l_workers_assigned + 1;
384 END IF;
385
386 UPDATE fnd_oam_dscram_bundles
387 SET workers_assigned = l_workers_assigned + 1,
388 last_updated_by = fnd_global.user_id,
389 last_update_login = fnd_global.user_id,
390 last_update_date = SYSDATE
391 WHERE bundle_id = p_bundle_id;
392 COMMIT;
393
394 --now populate the bundle info state
395 b_bundle_info.bundle_id := p_bundle_id;
396 b_bundle_info.worker_id := px_worker_id;
397 b_bundle_info.workers_allowed := l_workers_allowed;
398 b_bundle_info.batch_size := l_batch_size;
399 b_bundle_info.min_parallel_unit_weight := NVL(l_min_parallel_unit_weight, 0);
400 b_bundle_info.last_validated := NULL;
401 b_bundle_info.initialized := TRUE;
402
403 --now call run API for initializing arg context, needs to occur after the bundle is
404 --initialized to allow per-worker args to print correctly here.
405 FND_OAM_DSCRAM_RUNS_PKG.INITIALIZE_RUN_ARG_CONTEXT(l_return_status,
406 l_return_msg);
407 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
408 b_bundle_info.initialized := FALSE;
409 x_return_status := l_return_status;
410 x_return_msg := l_return_msg;
411 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
412 RETURN;
413 END IF;
414
415 fnd_oam_debug.log(1, l_ctxt, 'Worker Assigned.');
416 fnd_oam_debug.log(1, l_ctxt, 'Worker ID: '||px_worker_id);
417 fnd_oam_debug.log(1, l_ctxt, 'Default Batch Size: '||l_batch_size);
418
419 x_return_status := FND_API.G_RET_STS_SUCCESS;
420 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
421 EXCEPTION
422 WHEN OTHERS THEN
423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
424 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
425 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
426 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
427 ROLLBACK;
428 END;
429
430 -- Private: Autonomous Txn
431 -- Called when a bundle is completed in some way, usually when there are no more
432 -- tasks to fetch. Duties include updating the bundle's status, workers_assigned and completing
433 -- the stats record. If the final_status is the same as the current_status, assume
434 -- that some other worker already completed it.
435 -- Invariant: since this is private, assume the bundle state is valid.
436 -- Returns the ret_sts that execute should return
437 PROCEDURE COMPLETE_BUNDLE(p_proposed_status IN VARCHAR2,
438 p_proposed_ret_sts IN VARCHAR2,
439 x_final_status OUT NOCOPY VARCHAR2,
440 x_final_ret_sts OUT NOCOPY VARCHAR2)
441 IS
442 PRAGMA AUTONOMOUS_TRANSACTION;
443
444 l_ctxt VARCHAR2(60) := PKG_NAME||'COMPLETE_BUNDLE';
445
446 l_final_status VARCHAR2(30);
447 l_final_ret_sts VARCHAR2(6);
448
449 l_status VARCHAR2(30);
450 l_workers_assigned NUMBER;
451 BEGIN
452 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
453
454 -- always lock the bundle since we have to decrement the worker count
455 SELECT bundle_status, workers_assigned
456 INTO l_status, l_workers_assigned
457 FROM fnd_oam_dscram_bundles
458 WHERE bundle_id = b_bundle_info.bundle_id
459 FOR UPDATE;
460
461 -- translate the new_status into a valid final status
462 FND_OAM_DSCRAM_UTILS_PKG.TRANSLATE_COMPLETED_STATUS(l_status,
463 l_workers_assigned,
464 p_proposed_status,
465 p_proposed_ret_sts,
466 l_final_status,
467 l_final_ret_sts);
468 fnd_oam_debug.log(1, l_ctxt, 'Translated status "'||p_proposed_status||'" into "'||l_final_status||'"');
469 fnd_oam_debug.log(1, l_ctxt, 'Translated Execute Ret Sts "'||p_proposed_ret_sts||'" into "'||l_final_ret_sts||'"');
470
471 --update the status, workers_assigned
472 UPDATE fnd_oam_dscram_bundles
473 SET bundle_status = l_final_status,
474 workers_assigned = workers_assigned - 1,
475 last_updated_by = fnd_global.user_id,
476 last_update_login = fnd_global.user_id,
477 last_update_date = SYSDATE
478 WHERE bundle_id = b_bundle_info.bundle_id;
479
480 --only complete stats if we changed state
481 IF l_final_status <> l_status AND
482 FND_OAM_DSCRAM_UTILS_PKG.STATUS_IS_FINAL(l_final_status) THEN
483
484 FND_OAM_DSCRAM_STATS_PKG.COMPLETE_ENTRY(p_source_object_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_BUNDLE,
485 p_source_object_id => b_bundle_info.bundle_id,
486 p_end_time => SYSDATE,
487 p_postend_status => l_final_status);
488 END IF;
489
490 --signal a progress alert to wake up any waiting workers for the run.
491 --this is done here as well as the unit to increase the chance that a waiting
492 --worker will see a progress alert
493 FND_OAM_DSCRAM_UTILS_PKG.SIGNAL_PROGRESS_ALERT;
494
495 -- push the changes and release the lock
496 COMMIT;
497
498 x_final_status := l_final_status;
499 x_final_ret_sts := l_final_ret_sts;
500
501 --after completing the bundle, kill the state
502 b_bundle_info.initialized := FALSE;
503
504 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
505 EXCEPTION
506 WHEN OTHERS THEN
507 x_final_status := FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_ERROR_UNKNOWN;
508 x_final_ret_sts := FND_API.G_RET_STS_UNEXP_ERROR;
509 fnd_oam_debug.log(6, l_ctxt, 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
510 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
511 ROLLBACK;
512 END;
513
514 -- Private
515 -- When a task fails, we don't want the bundle to exit immediately because there's other, independent
516 -- tasks which can still be executed. Instead, we accumulate the messages into the return_msg and set the
517 -- final return status to stopped.
518 PROCEDURE HANDLE_FAILED_TASK(p_task_id IN NUMBER,
519 p_task_return_status IN VARCHAR2,
520 px_final_status IN OUT NOCOPY VARCHAR2,
521 px_return_msg IN OUT NOCOPY VARCHAR2)
522 IS
523 l_ctxt VARCHAR2(60) := PKG_NAME||'HANDLE_FAILED_TASK';
524
525 l_msg VARCHAR2(200);
526 l_msg_maxlen NUMBER := 4000;
527 BEGIN
528 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
529
530 fnd_oam_debug.log(1, l_ctxt, 'Task ID('||p_task_id||') seen as failed - current bundle final status('||px_final_status||')');
531
532 --set the final status to stopped if it's not set to error
533 IF NOT FND_OAM_DSCRAM_UTILS_PKG.STATUS_IS_ERROR(px_final_status) THEN
534 px_final_status := FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_STOPPED;
535 END IF;
536 l_msg := '(Task with ID('||p_task_id||') failed with Status('||p_task_return_status||'))';
537 IF ((px_return_msg IS NULL) OR
538 ((length(px_return_msg) + length(l_msg)) < l_msg_maxlen)) THEN
539 px_return_msg := px_return_msg||l_msg;
540 END IF;
541
542 fnd_oam_debug.log(1, l_ctxt, 'New bundle final status: '||px_final_status);
543 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
544 END;
545
546 -- Public
547 -- Return Statuses:
548 -- SUCCESS, ERROR, ERROR_UNEXP
549 -- Pass Through: SKIPPED, FULL, PROCESSED, STOPPED, ERROR_FATAL, ERROR_UNKNOWN
550 PROCEDURE EXECUTE_BUNDLE(p_run_id IN NUMBER,
551 p_bundle_id IN NUMBER,
552 px_worker_id IN OUT NOCOPY NUMBER,
553 x_return_status OUT NOCOPY VARCHAR2,
554 x_return_msg OUT NOCOPY VARCHAR2)
555 IS
556 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_BUNDLE';
557
558 l_task_id NUMBER;
559 l_completed_status VARCHAR2(30);
560
561 l_return_status VARCHAR2(6);
562 l_return_msg VARCHAR2(4000);
563 l_temp VARCHAR2(30);
564 BEGIN
565 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
566 x_return_status := FND_API.G_RET_STS_ERROR;
567 x_return_msg := '';
568
569 -- do an initial validation of the bundle
570 IF NOT VALIDATE_START_EXECUTION(p_run_id,
571 p_bundle_id,
572 l_return_status,
573 l_return_msg) THEN
574 x_return_status := l_return_status;
575 IF l_return_status NOT IN (FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_PROCESSED,
576 FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_SKIPPED) THEN
577 x_return_msg := '[Bundle Start Validation Failed]:('||l_return_msg||')';
578 END IF;
579 fnd_oam_debug.log(1, l_ctxt, x_return_msg);
580 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
581 RETURN;
582 END IF;
583
584 -- attempt to assign this invocation as a worker for the bundle
585 ASSIGN_WORKER_TO_BUNDLE(p_run_id,
586 p_bundle_id,
587 px_worker_id,
588 l_return_status,
589 l_return_msg);
590 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
591 x_return_status := l_return_status;
592 x_return_msg := '[Bundle Worker Assignment Failed]:('||l_return_msg||')';
593 fnd_oam_debug.log(1, l_ctxt, x_return_msg);
594 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
595 RETURN;
596 END IF;
597
598 --before proceeding after the assign, check our parent objects to make sure
599 --their state suggests we should continue
600 IF NOT FND_OAM_DSCRAM_RUNS_PKG.VALIDATE_CONTINUED_EXECUTION(FALSE,
601 TRUE,
602 l_return_status,
603 l_return_msg) THEN
604 --we don't care why a parent is invalid, just knowing so forces us to
605 --stop our work
606 COMPLETE_BUNDLE(FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_STOPPED,
607 FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_STOPPED,
608 l_completed_status,
609 x_return_status);
610 x_return_msg := '[Post-Assignment Parent Validation Failed]:('||l_return_msg||')';
611 fnd_oam_debug.log(1, l_ctxt, x_return_msg);
612 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
613 RETURN;
614 END IF;
615
616 --at this point, we're assigned so we need to issue a complete before returning.
617 --this means no quick returns, so we hit the complete after the loop.
618 l_return_status := FND_API.G_RET_STS_SUCCESS;
619 l_completed_status := FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSED;
620
621 -- we're in, start pulling tasks and executing them.
622 <<outer>>
623 LOOP
624 --get the next available task
625 FND_OAM_DSCRAM_TASKS_PKG.FETCH_NEXT_TASK(TRUE,
626 l_task_id,
627 l_return_status,
628 l_return_msg);
629 IF l_return_status = FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_EMPTY THEN
630 --the bundle is processed when a requery of task queue is empty
631 l_return_status := FND_API.G_RET_STS_SUCCESS;
632 EXIT outer;
633 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
634 l_completed_status := FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_ERROR_UNKNOWN;
635 x_return_msg := '[Fetch Next Task Failed]:('||l_return_msg||')';
636 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
637 EXIT outer;
638 END IF;
639
640 --execute the task
641 FND_OAM_DSCRAM_TASKS_PKG.EXECUTE_TASK(l_task_id,
642 l_return_status,
643 l_return_msg);
644 IF l_return_status = FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_FULL THEN
645 -- the task is full when all available(in-phase) child units have the max
646 -- # of workers assigned.
647 -- to handle this, loop to fetch additional tasks and try to execute those
648 fnd_oam_debug.log(1, l_ctxt, 'Exec task found the task was full, loop fetch for more tasks');
649 <<inner>>
650 LOOP
651 FND_OAM_DSCRAM_TASKS_PKG.FETCH_NEXT_TASK(FALSE,
652 l_task_id,
653 l_return_status,
654 l_return_msg);
655 IF l_return_status = FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_EMPTY THEN
656 -- seeing an empty here doesn't mean the bundle's finished, just that
657 --the bundle is busy and we should pause before restarting the outer loop.
658 fnd_oam_debug.log(1, l_ctxt, '[Inner Fetch Empty, Waiting for a progress alert]');
659 FND_OAM_DSCRAM_UTILS_PKG.WAIT_FOR_PROGRESS_ALERT;
660 EXIT inner;
661 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
662 l_completed_status := FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_ERROR_UNKNOWN;
663 x_return_msg := '[Inner Fetch Next Task Failed]:('||l_return_msg||')';
664 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
665 EXIT outer;
666 END IF;
667
668 -- try to execute this task
669 FND_OAM_DSCRAM_TASKS_PKG.EXECUTE_TASK(l_task_id,
670 l_return_status,
671 l_return_msg);
672 IF l_return_status = FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_FULL THEN
673 --continue to the next inner loop iteration for the next fetch
674 null;
675 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS AND
676 l_return_status <> FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_PROCESSED AND
677 l_return_status <> FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_SKIPPED THEN
678 HANDLE_FAILED_TASK(l_task_id,
679 l_return_status,
680 l_completed_status,
681 x_return_msg);
682 --besides knowing that the inner task was full and we should keep pulling from
683 --the initialized task queue, how the task completed - even with errors - has no
684 --bearing on the bundle. If the task wants the bundle to stop, it'll update the
685 --bundle's status which is checked at the end of the outer loop. Instead, we just
686 --assume the task is done and proceed with the bundle from the top.
687 EXIT inner;
688 ELSE
689 --success means jump to the outer loop
690 EXIT inner;
691 END IF;
692 END LOOP inner;
693 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS AND
694 l_return_status <> FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_PROCESSED AND
695 l_return_status <> FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_SKIPPED THEN
696 HANDLE_FAILED_TASK(l_task_id,
697 l_return_status,
698 l_completed_status,
699 x_return_msg);
700 -- no ELSE here because a success should just continue on.
701 END IF;
702
703 --after every task evaluation, check on the bundle and its parent run.
704 --tell it to requery if the child's execution came back non-successful since this should
705 --be pretty uncommon and will help detect when a child has seen a stop in a parent such as
706 --this bundle.
707 IF NOT VALIDATE_CONTINUED_EXECUTION((l_return_status <> FND_API.G_RET_STS_SUCCESS),
708 TRUE,
709 l_return_status,
710 l_return_msg) THEN
711 --complete the bundle based on what validate found
712 l_completed_status := FND_OAM_DSCRAM_UTILS_PKG.CONV_RET_STS_TO_COMPL_STATUS(l_return_status);
713 --change stop to stopped
714 IF FND_OAM_DSCRAM_UTILS_PKG.STATUS_IS_ERROR(l_return_status) THEN
715 x_return_msg := '[End-of-Loop Validate Failed]:('||l_return_msg||')';
716 fnd_oam_debug.log(1, l_ctxt, x_return_msg);
717 END IF;
718 EXIT outer;
719 END IF;
720
721 END LOOP outer;
722
723 --finished processing the bundle
724 fnd_oam_debug.log(1, l_ctxt, 'Finished Bundle with status: '||l_completed_status||'('||l_return_status||')');
725 COMPLETE_BUNDLE(l_completed_status,
726 l_return_status,
727 l_temp,
728 x_return_status);
729
730 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
731 EXCEPTION
732 WHEN OTHERS THEN
733 COMPLETE_BUNDLE(FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_ERROR_UNKNOWN,
734 FND_API.G_RET_STS_UNEXP_ERROR,
735 l_completed_status,
736 x_return_status);
737 x_return_msg := 'Unhandled Exception: [Code('||SQLCODE||'), Message("'||SQLERRM||'")]';
738 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
739 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
740 END;
741
742 -- Public
743 -- Return Statuses:
744 -- Whatever are listed for EXECUTE_BUNDLE
745 -- Pass Through: SKIPPED, FULL, PROCESSED, STOPPED, ERROR_FATAL, ERROR_UNKNOWN
746 PROCEDURE EXECUTE_HOST_BUNDLES(p_run_id IN NUMBER,
747 px_worker_id IN OUT NOCOPY NUMBER,
748 x_return_status OUT NOCOPY VARCHAR2,
749 x_return_msg OUT NOCOPY VARCHAR2)
750 IS
751 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_HOST_BUNDLES';
752
753 l_host_name VARCHAR2(256);
754 l_bundle_ids DBMS_SQL.NUMBER_TABLE;
755 l_bundle_id NUMBER;
756
757 k NUMBER;
758 l_return_status VARCHAR2(6);
759 l_return_msg VARCHAR2(4000);
760 BEGIN
761 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
762
763 --default to success if there's no bundles for this host
764 x_return_status := FND_API.G_RET_STS_SUCCESS;
765 x_return_msg := '';
766
767 --query the host name
768 SELECT UPPER(host_name)
769 INTO l_host_name
770 FROM v$instance;
771
772 --query the bundles for the host as well as any bundles for all hosts denoted by a null hostname
773 SELECT bundle_id
774 BULK COLLECT INTO l_bundle_ids
775 FROM fnd_oam_dscram_bundles
776 WHERE run_id = p_run_id
777 AND (target_hostname = l_host_name OR target_hostname IS NULL)
778 ORDER BY target_hostname DESC, weight DESC;
779
780 --iterate and call
781 k := l_bundle_ids.FIRST;
782 WHILE k IS NOT NULL LOOP
783 fnd_oam_debug.log(2, l_ctxt, 'Executing bundle_id: '||l_bundle_ids(k));
784 EXECUTE_BUNDLE(p_run_id,
785 l_bundle_ids(k),
786 px_worker_id,
787 x_return_status,
788 x_return_msg);
789 IF x_return_status IN (FND_API.G_RET_STS_SUCCESS,
790 FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_PROCESSED,
791 FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_FULL,
792 FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_SKIPPED) THEN
793 --override the output status to success if it's a success-like status
794 x_return_status := FND_API.G_RET_STS_SUCCESS;
795 ELSE
796 --if we didn't succeed, exit early
797 EXIT;
798 END IF;
799
800 k := l_bundle_ids.NEXT(k);
801 END LOOP;
802
803 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
804 EXCEPTION
805 WHEN OTHERS THEN
806 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
807 x_return_msg := 'Unhandled Exception: [Code('||SQLCODE||'), Message("'||SQLERRM||'")]';
808 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
809 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
810 END;
811
812 END FND_OAM_DSCRAM_BUNDLES_PKG;