1 PACKAGE BODY FND_OAM_DSCRAM_UTILS_PKG as
2 /* $Header: AFOAMDSUTILB.pls 120.8 2006/06/07 17:46:54 ilawler noship $ */
3
4 -- Private Body Constants
5 PKG_NAME CONSTANT VARCHAR2(20) := 'DSCRAM_UTILS_PKG.';
6 B_ARG_ROWID_CLAUSE CONSTANT VARCHAR2(60) := 'ROWID BETWEEN :'||G_ARG_ROWID_LBOUND_NAME||' AND :'||G_ARG_ROWID_UBOUND_NAME;
7
8 -- number of seconds to wait for a unit to finish. This is kept low so in case we register to
9 -- wait for a progress update and we miss the alert we'll wake up and check for ourselves
10 B_PROGRESS_ALERT_MAX_WAIT CONSTANT NUMBER := 60;
11 B_PROGRESS_ALERT_DIAG_WAIT CONSTANT NUMBER := 1;
12
13 -- Public
14 FUNCTION CONV_VALIDATE_START_STS_TO_RET(p_status IN VARCHAR2)
15 RETURN VARCHAR2
16 IS
17 l_ret_sts VARCHAR2(6) := G_RET_STS_ERROR_UNKNOWN;
18 BEGIN
19 IF p_status = G_STATUS_PROCESSED THEN
20 l_ret_sts := G_RET_STS_PROCESSED;
21 ELSIF p_status = G_STATUS_FINISHING THEN
22 l_ret_sts := G_RET_STS_PROCESSED;
23 ELSIF p_status = G_STATUS_STOPPED THEN
24 l_ret_sts := G_RET_STS_STOPPED;
25 ELSIF p_status = G_STATUS_STOPPING THEN
26 l_ret_sts := G_RET_STS_STOPPED;
27 ELSIF p_status = G_STATUS_SKIPPED THEN
28 l_ret_sts := G_RET_STS_SKIPPED;
29 ELSIF p_status = G_STATUS_ERROR_FATAL THEN
30 l_ret_sts := G_RET_STS_ERROR_FATAL;
31 END IF;
32 RETURN l_ret_sts;
33 END;
34
35 -- Public
36 FUNCTION CONV_VALIDATE_CONT_STS_TO_RET(p_status IN VARCHAR2)
37 RETURN VARCHAR2
38 IS
39 l_ret_sts VARCHAR2(6) := G_RET_STS_ERROR_UNKNOWN;
40 BEGIN
41 IF p_status = G_STATUS_PROCESSED THEN
42 l_ret_sts := G_RET_STS_PROCESSED;
43 ELSIF p_status = G_STATUS_FINISHING THEN
44 l_ret_sts := G_RET_STS_PROCESSED;
45 ELSIF p_status = G_STATUS_STOPPED THEN
46 l_ret_sts := G_RET_STS_STOPPED;
47 ELSIF p_status = G_STATUS_STOPPING THEN
48 l_ret_sts := G_RET_STS_STOPPED;
49 ELSIF p_status = G_STATUS_SKIPPED THEN
50 l_ret_sts := G_RET_STS_SKIPPED;
51 ELSIF p_status = G_STATUS_ERROR_FATAL THEN
52 l_ret_sts := G_RET_STS_ERROR_FATAL;
53 END IF;
54 RETURN l_ret_sts;
55 END;
56
57 -- Public
58 FUNCTION CONV_RET_STS_TO_COMPL_STATUS(p_ret_sts IN VARCHAR2)
59 RETURN VARCHAR2
60 IS
61 l_status VARCHAR2(30) := G_STATUS_ERROR_UNKNOWN;
62 BEGIN
63 IF p_ret_sts = FND_API.G_RET_STS_SUCCESS OR p_ret_sts = G_RET_STS_PROCESSED THEN
64 l_status := G_STATUS_PROCESSED;
65 ELSIF p_ret_sts = G_RET_STS_STOPPED THEN
66 l_status := G_STATUS_STOPPED;
67 ELSIF p_ret_sts = G_RET_STS_ERROR_FATAL THEN
68 l_status := G_STATUS_ERROR_FATAL;
69 END IF;
70 RETURN l_status;
71 END;
72
73 -- Public
74 FUNCTION STATUS_IS_EXECUTABLE(p_status IN VARCHAR2)
75 RETURN BOOLEAN
76 IS
77 BEGIN
78 IF p_status IS NULL THEN
79 RETURN FALSE;
80 END IF;
81 --FINISHING not included so we can't start something that's finishing, breaks other
82 --assumptions and allows for querying on what should be a done deal
83 RETURN (p_status IN (G_STATUS_UNPROCESSED,
84 G_STATUS_PROCESSING,
85 G_STATUS_RESTARTABLE));
86 END;
87
88 -- Public
89 FUNCTION STATUS_IS_PROCESSING(p_status IN VARCHAR2)
90 RETURN BOOLEAN
91 IS
92 BEGIN
93 IF p_status IS NULL THEN
94 RETURN FALSE;
95 END IF;
96 --include finishing as an ok processing state for a parent
97 RETURN (p_status IN (G_STATUS_PROCESSING,
98 G_STATUS_FINISHING));
99 END;
100
101 FUNCTION STATUS_IS_FINAL(p_status IN VARCHAR2)
102 RETURN BOOLEAN
103 IS
104 BEGIN
105 IF p_status IS NULL THEN
106 RETURN FALSE;
107 END IF;
108 RETURN (p_status IN (G_STATUS_ERROR_FATAL,
109 G_STATUS_ERROR_UNKNOWN,
110 G_STATUS_PROCESSED,
111 G_STATUS_STOPPED,
112 G_STATUS_SKIPPED));
113 END;
114
115 -- Public
116 FUNCTION STATUS_IS_ERROR(p_status IN VARCHAR2)
117 RETURN BOOLEAN
118 IS
119 BEGIN
120 IF p_status IS NULL THEN
121 RETURN FALSE;
122 END IF;
123 RETURN (p_status IN (G_STATUS_ERROR_FATAL,
124 G_STATUS_ERROR_UNKNOWN));
125 END;
126
127 -- Public
128 FUNCTION RET_STS_IS_ERROR(p_ret_sts IN VARCHAR2)
129 RETURN BOOLEAN
130 IS
131 BEGIN
132 IF p_ret_sts IS NULL THEN
133 RETURN FALSE;
134 END IF;
135 RETURN (p_ret_sts IN (FND_API.G_RET_STS_ERROR,
136 FND_API.G_RET_STS_UNEXP_ERROR,
137 G_RET_STS_ERROR_FATAL,
138 G_RET_STS_ERROR_UNKNOWN));
139 END;
140
141 -- Public
142 PROCEDURE TRANSLATE_COMPLETED_STATUS(p_current_status IN VARCHAR2,
143 p_workers_assigned IN NUMBER,
144 p_proposed_status IN VARCHAR2,
145 p_proposed_ret_sts IN VARCHAR2,
146 x_final_status OUT NOCOPY VARCHAR2,
147 x_final_ret_sts OUT NOCOPY VARCHAR2)
148 IS
149 BEGIN
150 x_final_status := p_proposed_status;
151 x_final_ret_sts := p_proposed_ret_sts;
152
153 --can't lose an unknown error, not even for other errors
154 IF p_current_status = G_STATUS_ERROR_UNKNOWN THEN
155 x_final_status := G_STATUS_ERROR_UNKNOWN;
156 x_final_ret_sts := G_RET_STS_ERROR_UNKNOWN;
157 RETURN;
158 END IF;
159
160 --if any other form of error then let it override
161 IF STATUS_IS_ERROR(p_proposed_status) THEN
162 RETURN;
163 END IF;
164
165 --otherwise follow some rules to keep the status transitions valid
166 IF p_current_status IN (G_STATUS_PROCESSING, G_STATUS_FINISHING) THEN
167 --if we're in a processing-like state, each worker returns processed when
168 --it can't find any more work, but the first N-1 workers really just set it
169 --to finishing to keep more workers out, only the last one sets it processed.
170 IF p_proposed_status = G_STATUS_PROCESSED THEN
171 IF p_workers_assigned > 1 THEN
172 --don't let it stay in processing, allows race where worker re-enters before
173 --others finish
174 x_final_status := G_STATUS_FINISHING;
175 END IF;
176 --unless our proposed return status is full, make sure the final return status is success
177 IF p_proposed_ret_sts <> G_RET_STS_FULL THEN
178 x_final_ret_sts := FND_API.G_RET_STS_SUCCESS;
179 END IF;
180 ELSIF p_proposed_status = G_STATUS_STOPPED THEN
181 x_final_ret_sts := G_RET_STS_STOPPED;
182 IF p_workers_assigned > 1 THEN
183 x_final_status := G_STATUS_STOPPING;
184 ELSE
185 x_final_status := G_STATUS_STOPPED;
186 END IF;
187 END IF;
188 ELSIF p_current_status = G_STATUS_PROCESSED THEN
189 --if we're processed and moving to processed, that's a success
190 IF p_proposed_status = G_STATUS_PROCESSED THEN
191 --unless our proposed return status is full, make sure the final return status is success
192 IF p_proposed_ret_sts <> G_RET_STS_FULL THEN
193 x_final_ret_sts := FND_API.G_RET_STS_SUCCESS;
194 END IF;
195 END IF;
196 ELSIF p_current_status = G_STATUS_STOPPING THEN
197 --only update it to stopped if we're the last worker completing.
198 x_final_ret_sts := G_RET_STS_STOPPED;
199 IF p_workers_assigned > 1 THEN
200 x_final_status := G_STATUS_STOPPING;
201 ELSE
202 x_final_status := G_STATUS_STOPPED;
203 END IF;
204 ELSIF p_current_status = G_STATUS_STOPPED THEN
205 --if we're stopped already, don't let somebody say we're processed
206 --shouldn't happen
207 x_final_ret_sts := G_RET_STS_STOPPED;
208 IF p_proposed_status = G_STATUS_PROCESSED THEN
209 x_final_status := G_STATUS_STOPPED;
210 END IF;
211 END IF;
212
213 END;
214
215 -- Public
216 FUNCTION VALIDATION_DUE(p_last_validated IN DATE)
217 RETURN BOOLEAN
218 IS
219 l_interval NUMBER;
220 BEGIN
221 IF p_last_validated IS NULL THEN
222 RETURN TRUE;
223 END IF;
224
225 l_interval := FND_OAM_DSCRAM_RUNS_PKG.GET_VALID_CHECK_INTERVAL;
226
227 --allows the user to turn off status checking
228 IF l_interval IS NULL OR l_interval <= 0 THEN
229 RETURN FALSE;
230 END IF;
231
232 RETURN (trunc((SYSDATE - p_last_validated)*86400) >= l_interval);
233 EXCEPTION
234 WHEN OTHERS THEN
235 RETURN FALSE;
236 END;
237
238 -- Public
239 FUNCTION FLAG_TO_BOOLEAN(p_flag IN VARCHAR2)
240 RETURN BOOLEAN
241 IS
242 BEGIN
243 IF p_flag IS NULL THEN
244 RETURN FALSE;
245 END IF;
246 RETURN (p_flag = FND_API.G_TRUE);
247 END;
248
249 -- Public
250 FUNCTION BOOLEAN_TO_FLAG(p_bool IN BOOLEAN)
251 RETURN VARCHAR2
252 IS
253 BEGIN
254 IF p_bool THEN
255 RETURN FND_API.G_TRUE;
256 END IF;
257 RETURN FND_API.G_FALSE;
258 END;
259
260 --Public
261 PROCEDURE PROPOGATE_FATALITY_LEVEL(p_fatality_level IN VARCHAR2)
262 IS
263 PRAGMA AUTONOMOUS_TRANSACTION;
264
265 l_ctxt VARCHAR2(60) := PKG_NAME||'PROPOGATE_FATALITY_LEVEL';
266
267 l_id NUMBER;
268 l_current_status VARCHAR2(30);
269 BEGIN
270 IF p_fatality_level = G_TYPE_TASK THEN
271 l_id := FND_OAM_DSCRAM_TASKS_PKG.GET_TASK_ID;
272 fnd_oam_debug.log(1, l_ctxt, 'Attempting to stop Task ID: '||l_id);
273 SELECT task_status
274 INTO l_current_status
275 FROM fnd_oam_dscram_tasks
276 WHERE task_id = l_id
277 FOR UPDATE;
278
279 IF STATUS_IS_PROCESSING(l_current_status) THEN
280 UPDATE fnd_oam_dscram_tasks
281 SET task_status = G_STATUS_STOPPING,
282 last_updated_by = fnd_global.user_id,
283 last_update_login = fnd_global.user_id,
284 last_update_date = SYSDATE
285 WHERE task_id = l_id;
286 COMMIT;
287 ELSE
288 fnd_oam_debug.log(1, l_ctxt, 'Skipping stopping task due to current status: '||l_current_status);
289 END IF;
290 ELSIF p_fatality_level = G_TYPE_BUNDLE THEN
291 l_id := FND_OAM_DSCRAM_BUNDLES_PKG.GET_BUNDLE_ID;
292 fnd_oam_debug.log(1, l_ctxt, 'Attempting to stop Bundle ID: '||l_id);
293 SELECT bundle_status
294 INTO l_current_status
295 FROM fnd_oam_dscram_bundles
296 WHERE bundle_id = l_id
297 FOR UPDATE;
298
299 IF STATUS_IS_PROCESSING(l_current_status) THEN
300 UPDATE fnd_oam_dscram_bundles
301 SET bundle_status = G_STATUS_STOPPING,
302 last_updated_by = fnd_global.user_id,
303 last_update_login = fnd_global.user_id,
304 last_update_date = SYSDATE
305 WHERE bundle_id = l_id;
306 COMMIT;
307 ELSE
308 fnd_oam_debug.log(1, l_ctxt, 'Skipping stopping bundle due to current status: '||l_current_status);
309 END IF;
310 ELSIF p_fatality_level = G_TYPE_RUN THEN
311 l_id := FND_OAM_DSCRAM_RUNS_PKG.GET_RUN_ID;
312 fnd_oam_debug.log(1, l_ctxt, 'Attempting to stop Run ID: '||l_id);
313 SELECT run_status
314 INTO l_current_status
315 FROM fnd_oam_dscram_runs_b
316 WHERE run_id = l_id
317 FOR UPDATE;
318
319 IF STATUS_IS_PROCESSING(l_current_status) THEN
320 UPDATE fnd_oam_dscram_runs_b
321 SET run_status = G_STATUS_STOPPING,
322 last_updated_by = fnd_global.user_id,
323 last_update_login = fnd_global.user_id,
324 last_update_date = SYSDATE
325 WHERE run_id = l_id;
326 COMMIT;
327 ELSE
328 fnd_oam_debug.log(1, l_ctxt, 'Skipping stopping run due to current status: '||l_current_status);
329 END IF;
330 ELSE
331 fnd_oam_debug.log(6, l_ctxt, 'Unknown fatality level: '|| p_fatality_level);
332 ROLLBACK;
333 END IF;
334 EXCEPTION
335 WHEN OTHERS THEN
336 ROLLBACK;
337 -- what do you do when fatally erroring a parent fails?
338 RETURN;
339 END;
340
341 -- Public
342 FUNCTION MAKE_AD_SCRIPT_KEY(p_run_id IN NUMBER,
343 p_unit_id IN NUMBER)
344 RETURN VARCHAR2
345 IS
346 BEGIN
347 RETURN G_DSCRAM_GLOBAL_PREFIX||to_char(p_run_id)||'_'||to_char(p_unit_id);
348 END;
349
350 -- Public
351 FUNCTION MAKE_AD_SCRIPT_KEY(p_unit_id IN NUMBER)
352 RETURN VARCHAR2
353 IS
354 BEGIN
355 RETURN G_DSCRAM_GLOBAL_PREFIX||to_char(FND_OAM_DSCRAM_RUNS_PKG.GET_RUN_ID)||'_'||to_char(p_unit_id);
356 END;
357
358 -- Helper to lock_run to provide the named lock's name
359 FUNCTION MAKE_RUN_LOCK_NAME(p_run_id IN NUMBER)
360 RETURN VARCHAR2
361 IS
362 BEGIN
363 RETURN 'DSCRAM.RUN.'||p_run_id;
364 END;
365
366 --wrapped dbms_lock procedure in this procedure to use an autonomous txn to keep it from committing the parent txn.
367 PROCEDURE ALLOCATE_LOCK(p_lock_name IN VARCHAR2,
368 x_lock_handle OUT NOCOPY VARCHAR2)
369 IS
370 PRAGMA AUTONOMOUS_TRANSACTION;
371 BEGIN
372 DBMS_LOCK.ALLOCATE_UNIQUE(p_lock_name,
373 x_lock_handle);
374 COMMIT;
375 END;
376
377 -- Private helper to grant exclusive access to a possibly non-existent run
378 FUNCTION LOCK_RUN(p_run_id IN NUMBER,
379 x_lock_handle OUT NOCOPY VARCHAR2)
380 RETURN BOOLEAN
381 IS
382 l_ctxt VARCHAR2(60) := PKG_NAME||'LOCK_RUN';
383
384 l_retval NUMBER;
385 l_lock_name VARCHAR2(30);
386 l_lock_handle VARCHAR2(128);
387 BEGIN
388 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
389 l_lock_name := MAKE_RUN_LOCK_NAME(p_run_id);
390
391 --fnd_oam_debug.log(1, l_ctxt, 'Allocated lock handle: '||l_lock_handle);
392 ALLOCATE_LOCK(l_lock_name,
393 l_lock_handle);
394 l_retval := DBMS_LOCK.REQUEST(l_lock_handle,
395 dbms_lock.x_mode,
396 dbms_lock.maxwait,
397 TRUE);
398 --fnd_oam_debug.log(1, l_ctxt, 'Retval: '||l_retval);
399 IF (l_retval <> 0) THEN
400 fnd_oam_debug.log(6, l_ctxt, 'Run ID ('||p_run_id||'), lock request failed: '||l_retval);
401 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
402 RETURN FALSE;
403 END IF;
404
405 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
406 x_lock_handle := l_lock_handle;
407 RETURN TRUE;
408 EXCEPTION
409 WHEN OTHERS THEN
410 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
411 IF l_lock_handle IS NOT NULL THEN
412 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
413 IF l_retval <> 0 THEN
414 fnd_oam_debug.log(6, l_ctxt, 'Failed to release run lock: '||l_retval);
415 END IF;
416 END IF;
417 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
418 RETURN FALSE;
419 END;
420
421 -- helper to lock arg
422 FUNCTION MAKE_ARG_LOCK_NAME(p_arg_id IN NUMBER)
423 RETURN VARCHAR2
424 IS
425 BEGIN
426 RETURN 'DSCRAM.ARG.'||p_arg_id;
427 END;
428
429 --Public
430 FUNCTION LOCK_ARG(p_arg_id IN NUMBER,
431 x_lock_handle OUT NOCOPY VARCHAR2)
432 RETURN BOOLEAN
433 IS
434 l_ctxt VARCHAR2(60) := PKG_NAME||'LOCK_ARG';
435
436 l_retval NUMBER;
437 l_lock_name VARCHAR2(30);
438 l_lock_handle VARCHAR2(128) := NULL;
439 BEGIN
440 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
441 l_lock_name := MAKE_ARG_LOCK_NAME(p_arg_id);
442
443 ALLOCATE_LOCK(l_lock_name,
444 l_lock_handle);
445 --fnd_oam_debug.log(1, l_ctxt, 'Allocated lock handle: '||l_lock_handle);
446
447 --Allow lock releases on commit/rollback so we can keep write_once args locked when first set until the
448 --batch is comitted or rolled back.
449 l_retval := DBMS_LOCK.REQUEST(l_lock_handle,
450 dbms_lock.x_mode,
451 dbms_lock.maxwait,
452 TRUE);
453 --fnd_oam_debug.log(1, l_ctxt, 'Retval: '||l_retval);
454 IF (l_retval <> 0) THEN
455 fnd_oam_debug.log(6, l_ctxt, 'Arg ID ('||p_arg_id||'), lock request failed: '||l_retval);
456 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
457 RETURN FALSE;
458 END IF;
459
460 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
461 x_lock_handle := l_lock_handle;
462 RETURN TRUE;
463 EXCEPTION
464 WHEN OTHERS THEN
465 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
466 IF l_lock_handle IS NOT NULL THEN
467 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
468 IF l_retval <> 0 THEN
469 fnd_oam_debug.log(6, l_ctxt, 'Failed to release arg lock: '||l_retval);
470 END IF;
471 END IF;
472 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
473 RETURN FALSE;
474 END;
475
476 -- helper to DELETE_<entity> functions
477 PROCEDURE DELETE_STATS(p_object_type IN VARCHAR2,
478 p_object_id IN VARCHAR2)
479 IS
480 l_ctxt VARCHAR2(60) := PKG_NAME||'DELETE_STATS';
481 BEGIN
482 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
483
484 --fnd_oam_debug.log(1, l_ctxt, 'Deleting stats...');
485 DELETE FROM fnd_oam_dscram_stats
486 WHERE source_object_type = p_object_type
487 AND source_object_id = p_object_id;
488
489 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
490 EXCEPTION
491 WHEN OTHERS THEN
492 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
493 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
494 END;
495
496 --helper to DELETE_<entity> functions
497 PROCEDURE DELETE_ARGS(p_object_type IN VARCHAR2,
498 p_object_id IN VARCHAR2)
499 IS
500 l_ctxt VARCHAR2(60) := PKG_NAME||'DELETE_ARGS';
501 l_ids DBMS_SQL.NUMBER_TABLE;
502 k NUMBER;
503 l_id NUMBER;
504 BEGIN
505 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
506
507 --fnd_oam_debug.log(1, l_ctxt, 'Deleting args...');
508 SELECT arg_id
509 BULK COLLECT INTO l_ids
510 FROM fnd_oam_dscram_args_b
511 WHERE parent_type = p_object_type
512 AND parent_id = p_object_id;
513
514 k := l_ids.FIRST;
515 WHILE k IS NOT NULL LOOP
516 l_id := l_ids(k);
517 DELETE FROM fnd_oam_dscram_args_b
518 WHERE arg_id = l_id;
519 DELETE FROM fnd_oam_dscram_args_tl
520 WHERE arg_id = l_id;
521 DELETE FROM fnd_oam_dscram_arg_values
522 WHERE arg_id = l_id;
523 k := l_ids.NEXT(k);
524 END LOOP;
525
526 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
527 EXCEPTION
528 WHEN OTHERS THEN
529 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
530 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
531 END;
532
533 --helper to DELETE_UNIT to delete AD parallel updates info when splitting was involved.
534 FUNCTION DELETE_AD_DATA(p_run_id IN NUMBER,
535 p_unit_id IN NUMBER,
536 p_owner IN VARCHAR2,
537 p_table_name IN VARCHAR2)
538 RETURN BOOLEAN
539 IS
540 l_ctxt VARCHAR2(60) := PKG_NAME||'DELETE_AD_DATA';
541
542 l_script_key VARCHAR2(30) := MAKE_AD_SCRIPT_KEY(p_run_id, p_unit_id);
543 l_ids DBMS_SQL.NUMBER_TABLE;
544 l_id NUMBER;
545
546 k NUMBER;
547 BEGIN
548 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
549 --fnd_oam_debug.log(1, l_ctxt, 'Deleting args...');
550 SELECT update_id
551 BULK COLLECT INTO l_ids
552 FROM ad_parallel_updates
553 WHERE owner = p_owner
554 AND table_name = p_table_name
555 AND script_name = l_script_key;
556
557 k := l_ids.FIRST;
558 WHILE k IS NOT NULL LOOP
559 l_id := l_ids(k);
560 fnd_oam_debug.log(1, l_ctxt, 'AD Update ID: '||l_id);
561 DELETE FROM ad_parallel_workers
562 WHERE update_id = l_id;
563 DELETE FROM ad_parallel_update_units
564 WHERE update_id = l_id;
565 DELETE FROM ad_parallel_updates
566 WHERE update_id = l_id;
567 k := l_ids.NEXT(k);
568 END LOOP;
569
570 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
571 RETURN TRUE;
572 EXCEPTION
573 WHEN OTHERS THEN
574 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
575 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
576 RETURN FALSE;
577 END;
578
579 --helper to DELETE_UNIT to delete plsqls
580 FUNCTION DELETE_PLSQL(p_plsql_id IN NUMBER)
581 RETURN BOOLEAN
582 IS
583 l_ctxt VARCHAR2(60) := PKG_NAME||'DELETE_PLSQL';
584
585 l_ids DBMS_SQL.NUMBER_TABLE;
586 k NUMBER;
587
588 BEGIN
589 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
590
591 fnd_oam_debug.log(1, l_ctxt, 'PLSQL ID: '||p_plsql_id);
592
593 DELETE_STATS(FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
594 p_plsql_id);
595
596 DELETE_ARGS(FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
597 p_plsql_id);
598
599 --delete the actual entity
600 fnd_oam_debug.log(1, l_ctxt, 'Deleting entity...');
601 DELETE FROM fnd_oam_dscram_plsqls
602 WHERE plsql_id = p_plsql_id;
603
604 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
605 RETURN TRUE;
606 EXCEPTION
607 WHEN OTHERS THEN
608 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
609 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
610 RETURN FALSE;
611 END;
612
613 --helper to DELETE_UNIT
614 FUNCTION DELETE_DML(p_dml_id IN NUMBER)
615 RETURN BOOLEAN
616 IS
617 l_ctxt VARCHAR2(60) := PKG_NAME||'DELETE_DML';
618
619 l_ids DBMS_SQL.NUMBER_TABLE;
620 k NUMBER;
621
622 BEGIN
623 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
624
625 fnd_oam_debug.log(1, l_ctxt, 'DML ID: '||p_dml_id);
626
627 DELETE_STATS(FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
628 p_dml_id);
629
630 DELETE_ARGS(FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
631 p_dml_id);
632
633 --delete the actual entity
634 fnd_oam_debug.log(1, l_ctxt, 'Deleting entity...');
635 DELETE FROM fnd_oam_dscram_dmls
636 WHERE dml_id = p_dml_id;
637
638 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
639 RETURN TRUE;
640 EXCEPTION
641 WHEN OTHERS THEN
642 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
643 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
644 RETURN FALSE;
645 END;
646
647 --helper to DELETE_TASK
648 FUNCTION DELETE_UNIT(p_run_id IN NUMBER,
649 p_unit_id IN NUMBER)
650 RETURN BOOLEAN
651 IS
652 l_ctxt VARCHAR2(60) := PKG_NAME||'DELETE_UNIT';
653
654 l_ids DBMS_SQL.NUMBER_TABLE;
655 l_unit_type VARCHAR2(30);
656 l_object_owner VARCHAR2(30);
657 l_object_name VARCHAR2(30);
658
659 k NUMBER;
660
661 BEGIN
662 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
663
664 fnd_oam_debug.log(1, l_ctxt, 'Unit ID: '||p_unit_id);
665
666 --get the unit type
667 SELECT unit_type, unit_object_owner, unit_object_name
668 INTO l_unit_type, l_object_owner, l_object_name
669 FROM fnd_oam_dscram_units
670 WHERE unit_id = p_unit_id;
671
672 --first get the list of child units
673 SELECT unit_id
674 BULK COLLECT INTO l_ids
675 FROM fnd_oam_dscram_units
676 WHERE concurrent_group_unit_id = p_unit_id;
677
678 --nuke the children
679 k := l_ids.FIRST;
680 WHILE k IS NOT NULL LOOP
681 IF NOT DELETE_UNIT(p_run_id,
682 l_ids(k)) THEN
683 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
684 RETURN FALSE;
685 END IF;
686
687 k := l_ids.NEXT(k);
688 END LOOP;
689
690 --now grab the list of dmls
691 SELECT dml_id
692 BULK COLLECT INTO l_ids
693 FROM fnd_oam_dscram_dmls
694 WHERE unit_id = p_unit_id;
695
696 --nuke the dmls
697 k := l_ids.FIRST;
698 WHILE k IS NOT NULL LOOP
699 IF NOT DELETE_DML(l_ids(k)) THEN
700 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
701 RETURN FALSE;
702 END IF;
703
704 k := l_ids.NEXT(k);
705 END LOOP;
706
707 --grab the list of plsqls
708 SELECT plsql_id
709 BULK COLLECT INTO l_ids
710 FROM fnd_oam_dscram_plsqls
711 WHERE unit_id = p_unit_id;
712
713 --nuke them
714 k := l_ids.FIRST;
715 WHILE k IS NOT NULL LOOP
716 IF NOT DELETE_PLSQL(l_ids(k)) THEN
717 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
718 RETURN FALSE;
719 END IF;
720
721 k := l_ids.NEXT(k);
722 END LOOP;
723
724 --nuke any ad data
725 IF NOT DELETE_AD_DATA(p_run_id,
726 p_unit_id,
727 l_object_owner,
728 l_object_name) THEN
729 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
730 RETURN FALSE;
731 END IF;
732
733 DELETE_STATS(FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_UNIT,
734 p_unit_id);
735
736 --delete the actual entity
737 fnd_oam_debug.log(1, l_ctxt, 'Deleting entity...');
738 DELETE FROM fnd_oam_dscram_units
739 WHERE unit_id = p_unit_id;
740
741 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
742 RETURN TRUE;
743 EXCEPTION
744 WHEN OTHERS THEN
745 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
746 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
747 RETURN FALSE;
748 END;
749
750 --helper to DELETE_BUNDLE
751 FUNCTION DELETE_TASK(p_run_id IN NUMBER,
752 p_task_id IN NUMBER)
753 RETURN BOOLEAN
754 IS
755 l_ctxt VARCHAR2(60) := PKG_NAME||'DELETE_TASK';
756
757 l_ids DBMS_SQL.NUMBER_TABLE;
758 k NUMBER;
759
760 BEGIN
761 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
762
763 fnd_oam_debug.log(1, l_ctxt, 'Task ID: '||p_task_id);
764
765 --grab the list of top-level units
766 SELECT unit_id
767 BULK COLLECT INTO l_ids
768 FROM fnd_oam_dscram_units
769 WHERE task_id = p_task_id
770 AND concurrent_group_unit_id IS NULL;
771
772 k := l_ids.FIRST;
773 WHILE k IS NOT NULL LOOP
774 IF NOT DELETE_UNIT(p_run_id,
775 l_ids(k)) THEN
776 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
777 RETURN FALSE;
778 END IF;
779
780 k := l_ids.NEXT(k);
781 END LOOP;
782
783 DELETE_STATS(FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_TASK,
784 p_task_id);
785
786 --delete the actual entity
787 fnd_oam_debug.log(1, l_ctxt, 'Deleting entity...');
788 DELETE FROM fnd_oam_dscram_tasks
789 WHERE task_id = p_task_id;
790
791 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
792 RETURN TRUE;
793 EXCEPTION
794 WHEN OTHERS THEN
795 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
796 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
797 RETURN FALSE;
798 END;
799
800 --helper to DELETE_RUN
801 FUNCTION DELETE_BUNDLE(p_run_id IN NUMBER,
802 p_bundle_id IN NUMBER)
803 RETURN BOOLEAN
804 IS
805 l_ctxt VARCHAR2(60) := PKG_NAME||'DELETE_BUNDLE';
806
807 l_ids DBMS_SQL.NUMBER_TABLE;
808 k NUMBER;
809
810 BEGIN
811 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
812
813 fnd_oam_debug.log(1, l_ctxt, 'Bundle ID: '||p_bundle_id);
814
815 --grab the list of tasks
816 SELECT task_id
817 BULK COLLECT INTO l_ids
818 FROM fnd_oam_dscram_tasks
819 WHERE bundle_id = p_bundle_id;
820
821 k := l_ids.FIRST;
822 WHILE k IS NOT NULL LOOP
823 IF NOT DELETE_TASK(p_run_id,
824 l_ids(k)) THEN
825 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
826 RETURN FALSE;
827 END IF;
828
829 k := l_ids.NEXT(k);
830 END LOOP;
831
832 DELETE_STATS(FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_BUNDLE,
833 p_bundle_id);
834
835 --delete the actual entity
836 fnd_oam_debug.log(1, l_ctxt, 'Deleting entity...');
837 DELETE FROM fnd_oam_dscram_bundles
838 WHERE bundle_id = p_bundle_id;
839
840 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
841 RETURN TRUE;
842 EXCEPTION
843 WHEN OTHERS THEN
844 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
845 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
846 RETURN FALSE;
847 END;
848
849 -- Public: Called before a test invocation to clear space for a test run.
850 FUNCTION DELETE_RUN(p_run_id IN NUMBER)
851 RETURN BOOLEAN
852 IS
853 l_ctxt VARCHAR2(60) := PKG_NAME||'DELETE_RUN';
854
855 l_lock_handle VARCHAR2(128);
856 l_retval NUMBER;
857 l_ids DBMS_SQL.NUMBER_TABLE;
858 k NUMBER;
859
860 l_retbool BOOLEAN;
861 BEGIN
862 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
863
864 --get a lock on the run
865 IF NOT LOCK_RUN(p_run_id,
866 l_lock_handle) THEN
867 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
868 RETURN FALSE;
869 END IF;
870
871 fnd_oam_debug.log(1, l_ctxt, 'Run ID: '||p_run_id);
872
873 --grab the list of bundles
874 SELECT bundle_id
875 BULK COLLECT INTO l_ids
876 FROM fnd_oam_dscram_bundles
877 WHERE RUN_ID = p_run_id;
878
879 --delete the child bundles
880 l_retbool := TRUE;
881 k := l_ids.FIRST;
882 WHILE k IS NOT NULL LOOP
883 IF NOT DELETE_BUNDLE(p_run_id,
884 l_ids(k)) THEN
885 l_retbool := FALSE;
886 EXIT;
887 END IF;
888
889 k := l_ids.NEXT(k);
890 END LOOP;
891
892 --delete the run's stats
893 DELETE_STATS(FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_RUN,
894 p_run_id);
895
896 DELETE_ARGS(FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_RUN,
897 p_run_id);
898
899 --delete the actual run
900 fnd_oam_debug.log(1, l_ctxt, 'Deleting entity...');
901 DELETE FROM fnd_oam_dscram_runs_b
902 WHERE run_id = p_run_id;
903 DELETE FROM fnd_oam_dscram_runs_tl
904 WHERE run_id = p_run_id;
905
906 --release the lock on the run
907 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
908 IF l_retval <> 0 THEN
909 fnd_oam_debug.log(6, l_ctxt, 'Failed to release run lock: '||l_retval);
910 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
911 RETURN FALSE;
912 END IF;
913
914 SELECT count(*)
915 INTO k
916 FROM fnd_oam_dscram_runs_b;
917 fnd_oam_debug.log(1, l_ctxt, 'Found '||k||' remaining base run rows.');
918 SELECT count(*)
919 INTO k
920 FROM fnd_oam_dscram_runs_tl;
921 fnd_oam_debug.log(1, l_ctxt, 'Found '||k||' remaining trans run rows.');
922
923 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
924 RETURN l_retbool;
925 EXCEPTION
926 WHEN OTHERS THEN
927 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
928 IF l_lock_handle IS NOT NULL THEN
929 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
930 END IF;
931 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
932 RETURN FALSE;
933 END;
934
935 -- Public
936 FUNCTION RUN_IS_NORMAL
937 RETURN BOOLEAN
938 IS
939 l_mode VARCHAR2(30);
940 BEGIN
941 l_mode := FND_OAM_DSCRAM_RUNS_PKG.GET_RUN_MODE;
942 IF l_mode IS NULL THEN
943 RETURN FALSE;
944 END IF;
945 RETURN l_mode = G_MODE_NORMAL;
946 EXCEPTION
947 WHEN OTHERS THEN
948 RETURN FALSE;
949 END;
950
951 -- Public
952 FUNCTION RUN_IS_DIAGNOSTIC
953 RETURN BOOLEAN
954 IS
955 l_mode VARCHAR2(30);
956 BEGIN
957 l_mode := FND_OAM_DSCRAM_RUNS_PKG.GET_RUN_MODE;
958 IF l_mode IS NULL THEN
959 RETURN FALSE;
960 END IF;
961 RETURN l_mode = G_MODE_DIAGNOSTIC;
962 EXCEPTION
963 WHEN OTHERS THEN
964 RETURN FALSE;
965 END;
966
967 -- Public
968 FUNCTION SOURCE_TYPE_USES_SQL(p_source_type IN VARCHAR2)
969 RETURN BOOLEAN
970 IS
971 BEGIN
972 RETURN p_source_type IN (G_SOURCE_SQL,
973 G_SOURCE_SQL_RESTRICTABLE);
974 END;
975
976 -- Public
977 PROCEDURE MAKE_FINAL_SQL_STMT(px_arg_context IN FND_OAM_DSCRAM_ARGS_PKG.arg_context,
978 p_stmt IN VARCHAR2,
979 p_where_clause IN VARCHAR2,
980 p_use_splitting IN BOOLEAN,
981 x_final_stmt OUT NOCOPY VARCHAR2,
982 x_return_status OUT NOCOPY VARCHAR2,
983 x_return_msg OUT NOCOPY VARCHAR2)
984 IS
985 l_ctxt VARCHAR2(60) := PKG_NAME||'MAKE_FINAL_SQL_STMT';
986 l_final_stmt VARCHAR2(4000);
987 l_stmt_length NUMBER;
988 l_stmt_maxlen NUMBER := 4000;
989 BEGIN
990 x_return_status := FND_API.G_RET_STS_ERROR;
991 x_return_msg := '';
992
993 --for now, all bind variables are bound in the cursor and not replaced in the DML statement. If in
994 --the future we tag some bind variables as being "bound" at design time and not run time, we'll do
995 --the string replacement here. Also means px_arg_context is unused here.
996 IF p_use_splitting THEN
997 --make sure we have room
998 IF p_where_clause IS NULL THEN
999 l_stmt_length := length(p_stmt) + length(B_ARG_ROWID_CLAUSE) + 7;
1000 ELSE
1001 l_stmt_length := length(p_stmt) + length(p_where_clause) + length(B_ARG_ROWID_CLAUSE) + 12;
1002 END IF;
1003 IF l_stmt_length > l_stmt_maxlen THEN
1004 x_return_msg := 'Total length of statement would be '||l_stmt_length||', greater than max ('||l_stmt_maxlen||').';
1005 x_final_stmt := NULL;
1006 RETURN;
1007 END IF;
1008 fnd_oam_debug.log(1, l_ctxt, 'Determined statement length: '||l_stmt_length);
1009
1010 --form the statement
1011 IF p_where_clause IS NULL THEN
1012 l_final_stmt := p_stmt||' WHERE '||B_ARG_ROWID_CLAUSE;
1013 ELSE
1014 l_final_stmt := p_stmt||' WHERE '||p_where_clause||' AND '||B_ARG_ROWID_CLAUSE;
1015 END IF;
1016 ELSE
1017 --make sure we have room
1018 IF p_where_clause IS NOT NULL THEN
1019 l_stmt_length := length(p_stmt) + length(p_where_clause) + 7;
1020
1021 IF l_stmt_length > l_stmt_maxlen THEN
1022 x_return_msg := 'Total length of statement would be '||l_stmt_length||', greater than max '||l_stmt_maxlen||'.';
1023 x_final_stmt := NULL;
1024 RETURN;
1025 END IF;
1026 fnd_oam_debug.log(1, l_ctxt, 'Determined statement length: '||l_stmt_length);
1027
1028 --no added clauses so just append parts
1029 l_final_stmt := p_stmt||' WHERE '||p_where_clause;
1030 ELSE
1031 l_final_stmt := p_stmt;
1032 END IF;
1033 END IF;
1034
1035 --return success
1036 x_return_status := FND_API.G_RET_STS_SUCCESS;
1037 x_final_stmt := l_final_stmt;
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1041 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
1042 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
1043 x_final_stmt := NULL;
1044 END;
1045
1046 -- Public
1047 FUNCTION MAKE_PROGRESS_ALERT_NAME(p_run_id IN NUMBER)
1048 RETURN VARCHAR2
1049 IS
1050 BEGIN
1051 RETURN G_DSCRAM_GLOBAL_PREFIX||to_char(p_run_id);
1052 END;
1053
1054 -- Public
1055 -- Autonomous because WAITONE does an implicit commit
1056 PROCEDURE WAIT_FOR_PROGRESS_ALERT
1057 IS
1058 PRAGMA AUTONOMOUS_TRANSACTION;
1059
1060 l_ctxt VARCHAR2(60) := PKG_NAME||'WAIT_FOR_PROGRESS_ALERT';
1061
1062 l_progress_alert_name VARCHAR2(30);
1063 l_msg VARCHAR2(3);
1064 l_status INTEGER;
1065 l_wait NUMBER := B_PROGRESS_ALERT_MAX_WAIT;
1066 BEGIN
1067 l_progress_alert_name := MAKE_PROGRESS_ALERT_NAME(FND_OAM_DSCRAM_RUNS_PKG.GET_RUN_ID);
1068 --register first
1069 DBMS_ALERT.REGISTER(l_progress_alert_name);
1070
1071 --if we're in a mode where the work is very quuick, cut down the max end of run wait
1072 IF FND_OAM_DSCRAM_RUNS_PKG.GET_RUN_MODE IN (G_MODE_DIAGNOSTIC,
1073 G_MODE_TEST_NO_EXEC) THEN
1074 l_wait := B_PROGRESS_ALERT_DIAG_WAIT;
1075 END IF;
1076 DBMS_ALERT.WAITONE(l_progress_alert_name,
1077 l_msg,
1078 l_status,
1079 l_wait);
1080 --ignore the message and status, we just want the notification
1081 ROLLBACK;
1082 EXCEPTION
1083 WHEN OTHERS THEN
1084 fnd_oam_debug.log(1, l_ctxt, 'Failed to wait for progress alert: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1085 ROLLBACK;
1086
1087 END;
1088
1089 -- Public
1090 -- Not autonomous because the alert is sent with the parent transaction's commit
1091 PROCEDURE SIGNAL_PROGRESS_ALERT
1092 IS
1093 l_ctxt VARCHAR2(60) := PKG_NAME||'SIGNAL_PROGRESS_ALERT';
1094
1095 l_progress_alert_name VARCHAR2(30);
1096 BEGIN
1097 l_progress_alert_name := MAKE_PROGRESS_ALERT_NAME(FND_OAM_DSCRAM_RUNS_PKG.GET_RUN_ID);
1098 DBMS_ALERT.SIGNAL(l_progress_alert_name,
1099 NULL);
1100 EXCEPTION
1101 WHEN OTHERS THEN
1102 --log it but don't throw it since this is only to help minimize delay
1103 fnd_oam_debug.log(1, l_ctxt, 'Failed to signal progress alert: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1104 END;
1105
1106 -- Public
1107 PROCEDURE SIGNAL_AUT_PROGRESS_ALERT
1108 IS
1109 PRAGMA AUTONOMOUS_TRANSACTION;
1110 BEGIN
1111 SIGNAL_PROGRESS_ALERT;
1112 COMMIT;
1113 EXCEPTION
1114 WHEN OTHERS THEN
1115 ROLLBACK;
1116 END;
1117
1118 -- Private helper function to take the keys of a number_table and return them as an in order list. Useful
1119 -- for cases where we need a list that's 1->N for FORALL bulk binding. Keys are assumed to be numeric.
1120 FUNCTION GET_MAP_KEYS(p_map IN DBMS_SQL.NUMBER_TABLE)
1121 RETURN DBMS_SQL.NUMBER_TABLE
1122 IS
1123 l_list DBMS_SQL.NUMBER_TABLE;
1124 k NUMBER;
1125 j NUMBER := 1;
1126 BEGIN
1127 k := p_map.FIRST;
1128 WHILE k IS NOT NULL LOOP
1129 l_list(j) := k;
1130 j := j + 1;
1131 k := p_map.NEXT(k);
1132 END LOOP;
1133
1134 RETURN l_list;
1135 END;
1136
1137 -- Forward Declarations
1138 PROCEDURE PREPARE_RUNS_FOR_RETRY(p_run_ids IN DBMS_SQL.NUMBER_TABLE,
1139 p_recurse_children IN VARCHAR2);
1140 PROCEDURE PREPARE_BUNDLES_FOR_RETRY(p_bundle_ids IN DBMS_SQL.NUMBER_TABLE,
1141 p_recurse_parents IN VARCHAR2,
1142 p_recurse_children IN VARCHAR2);
1143 PROCEDURE PREPARE_TASKS_FOR_RETRY(p_task_ids IN DBMS_SQL.NUMBER_TABLE,
1144 p_recurse_parents IN VARCHAR2,
1145 p_recurse_children IN VARCHAR2);
1146 PROCEDURE PREPARE_UNITS_FOR_RETRY(p_unit_ids IN DBMS_SQL.NUMBER_TABLE,
1147 p_recurse_parents IN VARCHAR2,
1148 p_recurse_children IN VARCHAR2);
1149 PROCEDURE PREPARE_DMLS_FOR_RETRY(p_dml_ids IN DBMS_SQL.NUMBER_TABLE,
1150 p_recurse_parents IN VARCHAR2);
1151 PROCEDURE PREPARE_PLSQLS_FOR_RETRY(p_plsql_ids IN DBMS_SQL.NUMBER_TABLE,
1152 p_recurse_parents IN VARCHAR2);
1153
1154 -- Private, prepares multiple plsqls for retry at once.
1155 PROCEDURE PREPARE_PLSQLS_FOR_RETRY(p_plsql_ids IN DBMS_SQL.NUMBER_TABLE,
1156 p_recurse_parents IN VARCHAR2)
1157 IS
1158 l_ctxt VARCHAR2(60) := PKG_NAME||'PREPARE_PLSQLS_FOR_RETRY';
1159
1160 l_parent_id NUMBER;
1161 l_parents_map DBMS_SQL.NUMBER_TABLE;
1162 j NUMBER;
1163 k NUMBER;
1164 l_msg VARCHAR2(4000);
1165 BEGIN
1166 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1167
1168 --sanity check
1169 IF (p_plsql_ids.COUNT < 1) THEN
1170 fnd_oam_debug.log(1, l_ctxt, 'No IDs to process.');
1171 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1172 RETURN;
1173 END IF;
1174
1175 --handle the parents
1176 IF p_recurse_parents IS NOT NULL AND p_recurse_parents = FND_API.G_TRUE THEN
1177 FOR k in p_plsql_ids.FIRST..p_plsql_ids.LAST LOOP
1178 SELECT unit_id
1179 INTO l_parent_id
1180 FROM fnd_oam_dscram_plsqls
1181 WHERE plsql_id = p_plsql_ids(k);
1182
1183 --add it to the parents_map if not present
1184 IF NOT l_parents_map.EXISTS(l_parent_id) THEN
1185 l_parents_map(l_parent_id) := 1;
1186 fnd_oam_debug.log(1, l_ctxt, 'Adding parent unit_id: '||l_parent_id);
1187 END IF;
1188 END LOOP;
1189
1190 --delegate the list to the parent prepare_for_retry procedure
1191 IF l_parents_map.COUNT > 0 THEN
1192 PREPARE_UNITS_FOR_RETRY(GET_MAP_KEYS(l_parents_map),
1193 FND_API.G_TRUE,
1194 FND_API.G_FALSE);
1195 ELSE
1196 fnd_oam_debug.log(1, l_ctxt, 'No parent units found.');
1197 END IF;
1198 END IF;
1199
1200 --and finally update the local entities
1201 fnd_oam_debug.log(1, l_ctxt, 'Processing '||p_plsql_ids.COUNT||' plsqls...');
1202 FORALL k in p_plsql_ids.FIRST..p_plsql_ids.LAST
1203 UPDATE fnd_oam_dscram_plsqls
1204 SET finished_ret_sts = NULL
1205 WHERE plsql_id = p_plsql_ids(k)
1206 AND finished_ret_sts <> FND_API.G_RET_STS_SUCCESS;
1207 fnd_oam_debug.log(1, l_ctxt, 'Done.');
1208
1209 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1210 EXCEPTION
1211 WHEN OTHERS THEN
1212 l_msg := 'Unexpected Error preparing plsqls for retry';
1213 fnd_oam_debug.log(6, l_ctxt, l_msg);
1214 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1215 RAISE_APPLICATION_ERROR(-20000, l_msg, TRUE);
1216 END;
1217
1218 -- Private, prepares multiple dmls for retry at once.
1219 PROCEDURE PREPARE_DMLS_FOR_RETRY(p_dml_ids IN DBMS_SQL.NUMBER_TABLE,
1220 p_recurse_parents IN VARCHAR2)
1221 IS
1222 l_ctxt VARCHAR2(60) := PKG_NAME||'PREPARE_DMLS_FOR_RETRY';
1223
1224 l_parent_id NUMBER;
1225 l_parents_map DBMS_SQL.NUMBER_TABLE;
1226 j NUMBER;
1227 k NUMBER;
1228 l_msg VARCHAR2(4000);
1229 BEGIN
1230 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1231
1232 --sanity check
1233 IF (p_dml_ids.COUNT < 1) THEN
1234 fnd_oam_debug.log(1, l_ctxt, 'No IDs to process.');
1235 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1236 RETURN;
1237 END IF;
1238
1239 --handle the parents
1240 IF p_recurse_parents IS NOT NULL AND p_recurse_parents = FND_API.G_TRUE THEN
1241 FOR k in p_dml_ids.FIRST..p_dml_ids.LAST LOOP
1242 SELECT unit_id
1243 INTO l_parent_id
1244 FROM fnd_oam_dscram_dmls
1245 WHERE dml_id = p_dml_ids(k);
1246
1247 --add it to the parents_map if not present
1248 IF NOT l_parents_map.EXISTS(l_parent_id) THEN
1249 l_parents_map(l_parent_id) := 1;
1250 fnd_oam_debug.log(1, l_ctxt, 'Adding parent unit_id: '||l_parent_id);
1251 END IF;
1252 END LOOP;
1253
1254 --delegate the list to the parent prepare_for_retry procedure
1255 IF l_parents_map.COUNT > 0 THEN
1256 PREPARE_UNITS_FOR_RETRY(GET_MAP_KEYS(l_parents_map),
1257 FND_API.G_TRUE,
1258 FND_API.G_FALSE);
1259 ELSE
1260 fnd_oam_debug.log(1, l_ctxt, 'No parent units found.');
1261 END IF;
1262 END IF;
1263
1264 --and finally update the local entities
1265 fnd_oam_debug.log(1, l_ctxt, 'Processing '||p_dml_ids.COUNT||' dmls...');
1266 FORALL k in p_dml_ids.FIRST..p_dml_ids.LAST
1267 UPDATE fnd_oam_dscram_dmls
1268 SET finished_ret_sts = NULL
1269 WHERE dml_id = p_dml_ids(k)
1270 AND finished_ret_sts <> FND_API.G_RET_STS_SUCCESS;
1271 fnd_oam_debug.log(1, l_ctxt, 'Done.');
1272
1273 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1274 EXCEPTION
1275 WHEN OTHERS THEN
1276 l_msg := 'Unexpected Error preparing dmls for retry.';
1277 fnd_oam_debug.log(6, l_ctxt, l_msg);
1278 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1279 RAISE_APPLICATION_ERROR(-20000, l_msg, TRUE);
1280 END;
1281
1282 -- Private, prepares multiple units for retry at once.
1283 PROCEDURE PREPARE_UNITS_FOR_RETRY(p_unit_ids IN DBMS_SQL.NUMBER_TABLE,
1284 p_recurse_parents IN VARCHAR2,
1285 p_recurse_children IN VARCHAR2)
1286 IS
1287 l_ctxt VARCHAR2(60) := PKG_NAME||'PREPARE_UNITS_FOR_RETRY';
1288
1289 l_ids DBMS_SQL.NUMBER_TABLE;
1290 l_parent_id NUMBER;
1291 l_parents_map DBMS_SQL.NUMBER_TABLE;
1292 j NUMBER;
1293 k NUMBER;
1294 l_msg VARCHAR2(4000);
1295 BEGIN
1296 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1297
1298 --sanity check
1299 IF (p_unit_ids.COUNT < 1) THEN
1300 fnd_oam_debug.log(1, l_ctxt, 'No IDs to process.');
1301 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1302 RETURN;
1303 END IF;
1304
1305 --handle the children
1306 IF p_recurse_children IS NOT NULL AND p_recurse_children = FND_API.G_TRUE THEN
1307 FOR k in p_unit_ids.FIRST..p_unit_ids.LAST LOOP
1308 --query out any child units
1309 fnd_oam_debug.log(1, l_ctxt, 'Processing child units of unit_id: '||p_unit_ids(k));
1310 SELECT unit_id
1311 BULK COLLECT INTO l_ids
1312 FROM fnd_oam_dscram_units
1313 WHERE concurrent_group_unit_id = p_unit_ids(k);
1314
1315 PREPARE_UNITS_FOR_RETRY(l_ids,
1316 FND_API.G_FALSE,
1317 FND_API.G_TRUE);
1318
1319 fnd_oam_debug.log(1, l_ctxt, 'Processing dmls of unit_id: '||p_unit_ids(k));
1320
1321 --get the dmls for the k'th unit
1322 l_ids.DELETE;
1323 SELECT dml_id
1324 BULK COLLECT INTO l_ids
1325 FROM fnd_oam_dscram_dmls
1326 WHERE unit_id = p_unit_ids(k)
1327 AND finished_ret_sts <> FND_API.G_RET_STS_SUCCESS;
1328
1329 --delegate the list to the child prepare_for_retry procedure
1330 PREPARE_DMLS_FOR_RETRY(l_ids,
1331 FND_API.G_FALSE);
1332
1333 fnd_oam_debug.log(1, l_ctxt, 'Processing plsqls of unit_id: '||p_unit_ids(k));
1334
1335 --get the dmls for the k'th unit
1336 l_ids.DELETE;
1337 SELECT plsql_id
1338 BULK COLLECT INTO l_ids
1339 FROM fnd_oam_dscram_plsqls
1340 WHERE unit_id = p_unit_ids(k)
1341 AND finished_ret_sts <> FND_API.G_RET_STS_SUCCESS;
1342
1343 --delegate the list to the child prepare_for_retry procedure
1344 PREPARE_PLSQLS_FOR_RETRY(l_ids,
1345 FND_API.G_FALSE);
1346
1347 END LOOP;
1348 END IF;
1349
1350 --handle the parents
1351 IF p_recurse_parents IS NOT NULL AND p_recurse_parents = FND_API.G_TRUE THEN
1352 FOR k in p_unit_ids.FIRST..p_unit_ids.LAST LOOP
1353 SELECT task_id
1354 INTO l_parent_id
1355 FROM fnd_oam_dscram_units
1356 WHERE unit_id = p_unit_ids(k);
1357
1358 --add it to the parents_map if not present
1359 IF NOT l_parents_map.EXISTS(l_parent_id) THEN
1360 l_parents_map(l_parent_id) := 1;
1361 fnd_oam_debug.log(1, l_ctxt, 'Adding parent task_id: '||l_parent_id);
1362 END IF;
1363 END LOOP;
1364
1365 --delegate the list to the parent prepare_for_retry procedure
1366 IF l_parents_map.COUNT > 0 THEN
1367 PREPARE_TASKS_FOR_RETRY(GET_MAP_KEYS(l_parents_map),
1368 FND_API.G_TRUE,
1369 FND_API.G_FALSE);
1370 ELSE
1371 --this happens for units when they're child units
1372 fnd_oam_debug.log(1, l_ctxt, 'No parent tasks found.');
1373 END IF;
1374 END IF;
1375
1376 --and finally update the local entities
1377 fnd_oam_debug.log(1, l_ctxt, 'Processing '||p_unit_ids.COUNT||' units...');
1378 FORALL k in p_unit_ids.FIRST..p_unit_ids.LAST
1379 UPDATE fnd_oam_dscram_units
1380 SET unit_status = G_STATUS_RESTARTABLE,
1381 workers_assigned = 0
1382 WHERE unit_id = p_unit_ids(k)
1383 AND unit_status NOT IN (G_STATUS_PROCESSED, G_STATUS_SKIPPED, G_STATUS_NO_STATUS, G_STATUS_UNPROCESSED);
1384 fnd_oam_debug.log(1, l_ctxt, 'Done.');
1385
1386 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1387 EXCEPTION
1388 WHEN OTHERS THEN
1389 l_msg := 'Unexpected Error preparing units for retry';
1390 fnd_oam_debug.log(6, l_ctxt, l_msg);
1391 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1392 RAISE_APPLICATION_ERROR(-20000, l_msg, TRUE);
1393 END;
1394
1395 -- Private, prepares multiple tasks for retry at once.
1396 PROCEDURE PREPARE_TASKS_FOR_RETRY(p_task_ids IN DBMS_SQL.NUMBER_TABLE,
1397 p_recurse_parents IN VARCHAR2,
1398 p_recurse_children IN VARCHAR2)
1399 IS
1400 l_ctxt VARCHAR2(60) := PKG_NAME||'PREPARE_TASKS_FOR_RETRY';
1401
1402 l_ids DBMS_SQL.NUMBER_TABLE;
1403 l_parent_id NUMBER;
1404 l_parents_map DBMS_SQL.NUMBER_TABLE;
1405 j NUMBER;
1406 k NUMBER;
1407 l_msg VARCHAR2(4000);
1408 BEGIN
1409 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1410
1411 --sanity check
1412 IF (p_task_ids.COUNT < 1) THEN
1413 fnd_oam_debug.log(1, l_ctxt, 'No IDs to process.');
1414 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1415 RETURN;
1416 END IF;
1417
1418 --handle the children
1419 IF p_recurse_children IS NOT NULL AND p_recurse_children = FND_API.G_TRUE THEN
1420 FOR k in p_task_ids.FIRST..p_task_ids.LAST LOOP
1421 fnd_oam_debug.log(1, l_ctxt, 'Processing children of task_id: '||p_task_ids(k));
1422
1423 --get the units for the k'th task
1424 SELECT unit_id
1425 BULK COLLECT INTO l_ids
1426 FROM fnd_oam_dscram_units
1427 WHERE task_id = p_task_ids(k)
1428 AND unit_status NOT IN (G_STATUS_PROCESSED, G_STATUS_SKIPPED, G_STATUS_NO_STATUS, G_STATUS_UNPROCESSED);
1429
1430 --delegate the list to the child prepare_for_retry procedure
1431 PREPARE_UNITS_FOR_RETRY(l_ids,
1432 FND_API.G_FALSE,
1433 FND_API.G_TRUE);
1434 END LOOP;
1435 END IF;
1436
1437 --handle the parents
1438 IF p_recurse_parents IS NOT NULL AND p_recurse_parents = FND_API.G_TRUE THEN
1439 FOR k in p_task_ids.FIRST..p_task_ids.LAST LOOP
1440 SELECT bundle_id
1441 INTO l_parent_id
1442 FROM fnd_oam_dscram_tasks
1443 WHERE task_id = p_task_ids(k);
1444
1445 --add it to the parents_map if not present
1446 IF NOT l_parents_map.EXISTS(l_parent_id) THEN
1447 l_parents_map(l_parent_id) := 1;
1448 fnd_oam_debug.log(1, l_ctxt, 'Adding parent bundle_id: '||l_parent_id);
1449 END IF;
1450 END LOOP;
1451
1452 --delegate the list to the parent prepare_for_retry procedure
1453 IF l_parents_map.COUNT > 0 THEN
1454 PREPARE_BUNDLES_FOR_RETRY(GET_MAP_KEYS(l_parents_map),
1455 FND_API.G_TRUE,
1456 FND_API.G_FALSE);
1457 ELSE
1458 fnd_oam_debug.log(1, l_ctxt, 'No parent bundles found.');
1459 END IF;
1460 END IF;
1461
1462 --and finally update the local entities
1463 fnd_oam_debug.log(1, l_ctxt, 'Processing '||p_task_ids.COUNT||' tasks...');
1464 FORALL k in p_task_ids.FIRST..p_task_ids.LAST
1465 UPDATE fnd_oam_dscram_tasks
1466 SET task_status = G_STATUS_RESTARTABLE,
1467 workers_assigned = 0
1468 WHERE task_id = p_task_ids(k)
1469 AND task_status NOT IN (G_STATUS_PROCESSED, G_STATUS_SKIPPED, G_STATUS_UNPROCESSED);
1470 fnd_oam_debug.log(1, l_ctxt, 'Done.');
1471
1472 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1473 EXCEPTION
1474 WHEN OTHERS THEN
1475 l_msg := 'Unexpected Error preparing tasks for retry';
1476 fnd_oam_debug.log(6, l_ctxt, l_msg);
1477 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1478 RAISE_APPLICATION_ERROR(-20000, l_msg, TRUE);
1479 END;
1480
1481 -- Private, prepares multiple bundles for retry at once.
1482 PROCEDURE PREPARE_BUNDLES_FOR_RETRY(p_bundle_ids IN DBMS_SQL.NUMBER_TABLE,
1483 p_recurse_parents IN VARCHAR2,
1484 p_recurse_children IN VARCHAR2)
1485 IS
1486 l_ctxt VARCHAR2(60) := PKG_NAME||'PREPARE_BUNDLES_FOR_RETRY';
1487
1488 l_ids DBMS_SQL.NUMBER_TABLE;
1489 l_parent_id NUMBER;
1490 l_parents_map DBMS_SQL.NUMBER_TABLE;
1491 j NUMBER;
1492 k NUMBER;
1493 l_msg VARCHAR2(4000);
1494 BEGIN
1495 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1496
1497 --sanity check
1498 IF (p_bundle_ids.COUNT < 1) THEN
1499 fnd_oam_debug.log(1, l_ctxt, 'No IDs to process.');
1500 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1501 RETURN;
1502 END IF;
1503
1504 --handle the children
1505 IF p_recurse_children IS NOT NULL AND p_recurse_children = FND_API.G_TRUE THEN
1506 FOR k in p_bundle_ids.FIRST..p_bundle_ids.LAST LOOP
1507 fnd_oam_debug.log(1, l_ctxt, 'Processing children of bundle_id: '||p_bundle_ids(k));
1508
1509 --get the tasks for the k'th bundle
1510 SELECT task_id
1511 BULK COLLECT INTO l_ids
1512 FROM fnd_oam_dscram_tasks
1513 WHERE bundle_id = p_bundle_ids(k)
1514 AND task_status NOT IN (G_STATUS_PROCESSED, G_STATUS_SKIPPED, G_STATUS_UNPROCESSED);
1515
1516 --delegate the list to the child prepare_for_retry procedure
1517 PREPARE_TASKS_FOR_RETRY(l_ids,
1518 FND_API.G_FALSE,
1519 FND_API.G_TRUE);
1520 END LOOP;
1521 END IF;
1522
1523 --handle the parents
1524 IF p_recurse_parents IS NOT NULL AND p_recurse_parents = FND_API.G_TRUE THEN
1525 FOR k in p_bundle_ids.FIRST..p_bundle_ids.LAST LOOP
1526 SELECT run_id
1527 INTO l_parent_id
1528 FROM fnd_oam_dscram_bundles
1529 WHERE bundle_id = p_bundle_ids(k);
1530
1531 --add it to the parents_map if not present
1532 IF NOT l_parents_map.EXISTS(l_parent_id) THEN
1533 l_parents_map(l_parent_id) := 1;
1534 fnd_oam_debug.log(1, l_ctxt, 'Adding parent run_id: '||l_parent_id);
1535 END IF;
1536 END LOOP;
1537
1538 --delegate the list to the parent prepare_for_retry procedure
1539 IF l_parents_map.COUNT > 0 THEN
1540 PREPARE_RUNS_FOR_RETRY(GET_MAP_KEYS(l_parents_map),
1541 FND_API.G_FALSE);
1542 ELSE
1543 fnd_oam_debug.log(1, l_ctxt, 'No parent runs found.');
1544 END IF;
1545 END IF;
1546
1547 --and finally update the local entities
1548 fnd_oam_debug.log(1, l_ctxt, 'Processing '||p_bundle_ids.COUNT||' bundles...');
1549 FORALL k in p_bundle_ids.FIRST..p_bundle_ids.LAST
1550 UPDATE fnd_oam_dscram_bundles
1551 SET bundle_status = G_STATUS_RESTARTABLE,
1552 workers_assigned = 0
1553 WHERE bundle_id = p_bundle_ids(k)
1554 AND bundle_status NOT IN (G_STATUS_PROCESSED, G_STATUS_SKIPPED, G_STATUS_UNPROCESSED);
1555 fnd_oam_debug.log(1, l_ctxt, 'Done.');
1556
1557 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1558 EXCEPTION
1559 WHEN OTHERS THEN
1560 l_msg := 'Unexpected Error preparing bundles for retry';
1561 fnd_oam_debug.log(6, l_ctxt, l_msg);
1562 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1563 RAISE_APPLICATION_ERROR(-20000, l_msg, TRUE);
1564 END;
1565
1566 -- Private, prepares multiple runs at once.
1567 PROCEDURE PREPARE_RUNS_FOR_RETRY(p_run_ids IN DBMS_SQL.NUMBER_TABLE,
1568 p_recurse_children IN VARCHAR2)
1569 IS
1570 l_ctxt VARCHAR2(60) := PKG_NAME||'PREPARE_RUNS_FOR_RETRY';
1571
1572 l_ids DBMS_SQL.NUMBER_TABLE;
1573 k NUMBER;
1574 l_msg VARCHAR2(4000);
1575 BEGIN
1576 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1577
1578 --sanity check
1579 IF (p_run_ids.COUNT < 1) THEN
1580 fnd_oam_debug.log(1, l_ctxt, 'No IDs to process.');
1581 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1582 RETURN;
1583 END IF;
1584
1585 --handle the children
1586 IF p_recurse_children IS NOT NULL AND p_recurse_children = FND_API.G_TRUE THEN
1587 FOR k in p_run_ids.FIRST..p_run_ids.LAST LOOP
1588 --get the bundles for the k'th task
1589 SELECT bundle_id
1590 BULK COLLECT INTO l_ids
1591 FROM fnd_oam_dscram_bundles
1592 WHERE run_id = p_run_ids(k)
1593 AND bundle_status NOT IN (G_STATUS_PROCESSED, G_STATUS_SKIPPED, G_STATUS_UNPROCESSED);
1594
1595 --delegate the list to the child prepare_for_retry procedure
1596 PREPARE_BUNDLES_FOR_RETRY(l_ids,
1597 FND_API.G_FALSE,
1598 FND_API.G_TRUE);
1599 END LOOP;
1600 END IF;
1601
1602 --and finally update the local entities
1603 FORALL k in p_run_ids.FIRST..p_run_ids.LAST
1604 UPDATE fnd_oam_dscram_runs_b
1605 SET run_status = G_STATUS_RESTARTABLE
1606 WHERE run_id = p_run_ids(k)
1607 AND run_status NOT IN (G_STATUS_PROCESSED, G_STATUS_SKIPPED, G_STATUS_UNPROCESSED);
1608
1609 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1610 EXCEPTION
1611 WHEN OTHERS THEN
1612 l_msg := 'Unexpected Error preparing runs for retry.';
1613 fnd_oam_debug.log(6, l_ctxt, l_msg);
1614 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1615 RAISE_APPLICATION_ERROR(-20000, l_msg, TRUE);
1616 END;
1617
1618 -- Public
1619 PROCEDURE PREPARE_RUN_FOR_RETRY(p_run_id IN NUMBER,
1620 p_recurse_children IN VARCHAR2)
1621 IS
1622 l_ctxt VARCHAR2(60) := PKG_NAME||'PREPARE_RUN_FOR_RETRY';
1623
1624 l_lock_handle VARCHAR2(128);
1625 l_retval NUMBER;
1626 l_status VARCHAR2(30);
1627 l_ids DBMS_SQL.NUMBER_TABLE;
1628
1629 l_msg VARCHAR2(4000);
1630 BEGIN
1631 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1632
1633 --get a lock on the run
1634 IF NOT LOCK_RUN(p_run_id,
1635 l_lock_handle) THEN
1636 RAISE_APPLICATION_ERROR(-20000, 'Failed to get a lock on run_id: '||p_run_id);
1637 END IF;
1638
1639 fnd_oam_debug.log(1, l_ctxt, 'Run ID: '||p_run_id);
1640
1641 --make sure the run's in a state where we can retry it
1642 SELECT run_status
1643 INTO l_status
1644 FROM fnd_oam_dscram_runs_b
1645 WHERE run_id = p_run_id;
1646
1647 IF l_status NOT IN (G_STATUS_STOPPED, G_STATUS_ERROR_UNKNOWN, G_STATUS_ERROR_FATAL) THEN
1648 RAISE_APPLICATION_ERROR(-20000, 'Run is in an invalid status('||l_status||') and cannot be retried.');
1649 END IF;
1650
1651 IF p_recurse_children IS NOT NULL AND p_recurse_children = FND_API.G_TRUE THEN
1652 --grab the list of bundles
1653 SELECT bundle_id
1654 BULK COLLECT INTO l_ids
1655 FROM fnd_oam_dscram_bundles
1656 WHERE RUN_ID = p_run_id;
1657
1658 --delegate to the bundles_retry
1659 PREPARE_BUNDLES_FOR_RETRY(l_ids,
1660 FND_API.G_FALSE,
1661 FND_API.G_TRUE);
1662 END IF;
1663
1664 --update the run
1665 UPDATE fnd_oam_dscram_runs_b
1666 SET run_status = G_STATUS_RESTARTABLE
1667 WHERE run_id = p_run_id;
1668
1669 --release the lock on the run
1670 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
1671 IF l_retval <> 0 THEN
1672 RAISE_APPLICATION_ERROR(-20000, 'Failed to release run lock: '||l_retval);
1673 END IF;
1674
1675 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1676 EXCEPTION
1677 WHEN OTHERS THEN
1678 l_msg := 'Unexpected Error preparing run_id ('||p_run_id||') for retry.';
1679 fnd_oam_debug.log(6, l_ctxt, l_msg);
1680 IF l_lock_handle IS NOT NULL THEN
1681 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
1682 END IF;
1683 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1684 RAISE_APPLICATION_ERROR(-20000, l_msg, TRUE);
1685 END;
1686
1687 END FND_OAM_DSCRAM_UTILS_PKG;
[Home] [Help]