1 PACKAGE BODY FND_OAM_DSCRAM_DMLS_PKG as
2 /* $Header: AFOAMDSDMLB.pls 120.6 2006/06/07 18:02:20 ilawler noship $ */
3
4 ----------------------------------------
5 -- Private Body Constants
6 ----------------------------------------
7 PKG_NAME CONSTANT VARCHAR2(20) := 'DSCRAM_DMLS_PKG.';
8
9 --exception used by INTERNAL_EXECUTE to do common cleanup code when failing an execute
10 EXECUTE_FAILED EXCEPTION;
11
12 ----------------------------------------
13 -- Private Body Types/Variables
14 ----------------------------------------
15 TYPE b_dml_cache_entry IS RECORD
16 (
17 dml_id NUMBER(15) := NULL,
18 cursor_id INTEGER := NULL,
19 arg_list FND_OAM_DSCRAM_ARGS_PKG.arg_list,
20 use_splitting BOOLEAN := FALSE,
21 has_writable_args BOOLEAN := FALSE,
22 rows_processed NUMBER := NULL,
23 last_execute_ret_sts VARCHAR2(3) := NULL,
24 last_execute_ret_msg VARCHAR2(2048) := NULL
25 );
26 TYPE b_dml_cache_type IS TABLE OF b_dml_cache_entry INDEX BY BINARY_INTEGER;
27
28 -- Package cache of DMLs that have been prepared and parsed
29 -- into a dbms_sql cursor
30 b_dml_cache b_dml_cache_type;
31
32 -- DML ID of the currently executing DML
33 b_current_dml_id NUMBER := NULL;
34
35 ----------------------------------------
36 -- Public/Private Procedures/Functions
37 ----------------------------------------
38
39 -- Public
40 FUNCTION GET_CURRENT_DML_ID
41 RETURN NUMBER
42 IS
43 BEGIN
44 IF b_current_dml_id IS NULL THEN
45 RAISE NO_DATA_FOUND;
46 END IF;
47
48 RETURN b_current_dml_id;
49 END;
50
51 -- Public
52 PROCEDURE FETCH_DML_IDS(p_unit_id IN NUMBER,
53 x_work_queue OUT NOCOPY FND_OAM_DSCRAM_UNITS_PKG.ordered_work_queue_type,
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_return_msg OUT NOCOPY VARCHAR2)
56 IS
57 l_ctxt VARCHAR2(60) := PKG_NAME||'FETCH_DML_IDS';
58
59 l_ids DBMS_SQL.NUMBER_TABLE;
60 l_priorities DBMS_SQL.NUMBER_TABLE;
61 l_weights DBMS_SQL.NUMBER_TABLE;
62
63 l_work_queue FND_OAM_DSCRAM_UNITS_PKG.ordered_work_queue_type := FND_OAM_DSCRAM_UNITS_PKG.ordered_work_queue_type();
64 k NUMBER;
65 BEGIN
66 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
67 x_return_status := FND_API.G_RET_STS_ERROR;
68 x_return_msg := '';
69
70 -- bulk select all valid dmls with work to do
71 SELECT dml_id, priority, weight
72 BULK COLLECT INTO l_ids, l_priorities, l_weights
73 FROM fnd_oam_dscram_dmls
74 WHERE unit_id = p_unit_id
75 AND finished_ret_sts IS NULL
76 ORDER BY priority ASC, weight DESC;
77
78 --allocate the work queue
79 l_work_queue.EXTEND(l_ids.COUNT);
80
81 --since we select them in the proper order, construct the work queue by doing a single pass of the array
82 k := l_ids.FIRST;
83 WHILE k IS NOT NULL LOOP
84 l_work_queue(k) := FND_OAM_DSCRAM_UNITS_PKG.CREATE_WORK_ITEM(l_priorities(k),
85 l_weights(k),
86 FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
87 l_ids(k));
88 k := l_ids.NEXT(k);
89 END LOOP;
90
91 --success
92 x_work_queue := l_work_queue;
93 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
94 x_return_status := FND_API.G_RET_STS_SUCCESS;
95 EXCEPTION
96 WHEN OTHERS THEN
97 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
98 x_return_msg := 'Unit ID ('||p_unit_id||') failed to fetch dml_ids: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
99 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
100 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
101 END;
102
103 -- Public
104 PROCEDURE UPDATE_COMP_DML_WRITABLE_ARGS(p_dml_id IN NUMBER,
105 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
106 p_using_splitting IN BOOLEAN,
107 x_return_status OUT NOCOPY VARCHAR2,
108 x_return_msg OUT NOCOPY VARCHAR2)
109 IS
110 l_ctxt VARCHAR2(60) := PKG_NAME||'UPDATE_COMP_DML_WRITABLE_ARGS';
111
112 l_arg_list FND_OAM_DSCRAM_ARGS_PKG.arg_list;
113 l_has_writable_args BOOLEAN;
114 l_dml_cache_entry b_dml_cache_entry;
115
116 l_return_status VARCHAR2(6);
117 l_return_msg VARCHAR2(2048);
118 BEGIN
119 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
120 x_return_status := FND_API.G_RET_STS_ERROR;
121 x_return_msg := '';
122
123 --make sure the dml's in the cache, if not we need to fetch its arg list now to update the writable args
124 --and place it in the cache if its possible that we will update an arg here.
125 IF NOT b_dml_cache.EXISTS(p_dml_id) THEN
126 --fetch the arg list first
127 FND_OAM_DSCRAM_ARGS_PKG.FETCH_ARG_LIST(FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
128 p_dml_id,
129 l_arg_list,
130 l_has_writable_args,
131 l_return_status,
132 l_return_msg);
133 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
134 x_return_status := l_return_status;
135 x_return_msg := l_return_msg;
136 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
137 RETURN;
138 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS AND l_has_writable_args THEN
139 --to allow these args to be pushed into the context, we also need to make a dml cache entry so that
140 --destroy_dml_cache_entry can get the computed value
141 l_dml_cache_entry.dml_id := p_dml_id;
142 l_dml_cache_entry.arg_list := l_arg_list;
143 l_dml_cache_entry.use_splitting := p_using_splitting;
144 l_dml_cache_entry.has_writable_args := l_has_writable_args;
145 b_dml_cache(p_dml_id) := l_dml_cache_entry;
146 END IF;
147 END IF;
148
149 --if the dml made it into the cache and has writable args, update them
150 IF b_dml_cache.EXISTS(p_dml_id) AND b_dml_cache(p_dml_id).has_writable_args THEN
151 FND_OAM_DSCRAM_ARGS_PKG.UPDATE_WRITABLE_ARG_VALUES(b_dml_cache(p_dml_id).arg_list,
152 px_arg_context,
153 TRUE,
154 b_dml_cache(p_dml_id).use_splitting,
155 NULL,
156 NULL,
157 NULL,
158 l_return_status,
159 l_return_msg);
160 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
161 x_return_status := l_return_status;
162 x_return_msg := l_return_msg;
163 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
164 RETURN;
165 END IF;
166 END IF;
167
168 --success
169 x_return_status := FND_API.G_RET_STS_SUCCESS;
170 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
171 EXCEPTION
172 WHEN OTHERS THEN
173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
174 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
175 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
176 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
177 END;
178
179 -- Private helper to the destroy procedures to do the actual work of destroying a
180 -- cache entry, assumes p_dml_id is in the cache.
181 PROCEDURE INTERNAL_DESTROY_CACHE_ENTRY(p_dml_id IN NUMBER,
182 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
183 p_update_context IN BOOLEAN,
184 x_return_status OUT NOCOPY VARCHAR2,
185 x_return_msg OUT NOCOPY VARCHAR2)
186 IS
187 l_ctxt VARCHAR2(60) := PKG_NAME||'INTERNAL_DESTROY_CACHE_ENTRY';
188
189 l_return_status VARCHAR2(6);
190 l_return_msg VARCHAR2(2048);
191 BEGIN
192 x_return_status := FND_API.G_RET_STS_ERROR;
193 x_return_msg := '';
194
195 -- if requested, roll the arg list into the context
196 IF p_update_context THEN
197 FND_OAM_DSCRAM_ARGS_PKG.UPDATE_CONTEXT_USING_ARG_LIST(px_arg_context,
198 b_dml_cache(p_dml_id).arg_list,
199 b_dml_cache(p_dml_id).use_splitting);
200 END IF;
201
202 --first destroy the argument list
203 FND_OAM_DSCRAM_ARGS_PKG.DESTROY_ARG_LIST(b_dml_cache(p_dml_id).arg_list,
204 l_return_status,
205 l_return_msg);
206 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
207 x_return_status := l_return_status;
208 x_return_msg := l_return_msg;
209 RETURN;
210 END IF;
211
212 --now de-allocate the local cursor
213 IF b_dml_cache(p_dml_id).cursor_id IS NOT NULL AND DBMS_SQL.IS_OPEN(b_dml_cache(p_dml_id).cursor_id) THEN
214 DBMS_SQL.CLOSE_CURSOR(b_dml_cache(p_dml_id).cursor_id);
215 END IF;
216
217 --remove the dml from the cache
218 b_dml_cache.DELETE(p_dml_id);
219
220 x_return_status := FND_API.G_RET_STS_SUCCESS;
221 EXCEPTION
222 WHEN OTHERS THEN
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
224 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
225 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
226 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
227 END;
228
229 -- Public
230 PROCEDURE DESTROY_DML_CACHE_ENTRY(p_dml_id IN NUMBER,
231 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
232 p_update_context IN BOOLEAN,
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_return_msg OUT NOCOPY VARCHAR2)
235 IS
236 l_ctxt VARCHAR2(60) := PKG_NAME||'DESTROY_DML_CACHE_ENTRY';
237
238 l_return_status VARCHAR2(6);
239 l_return_msg VARCHAR2(2048);
240 BEGIN
241 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
242 x_return_status := FND_API.G_RET_STS_ERROR;
243 x_return_msg := '';
244
245 --make sure the dml's in the cache and get it's entry
246 IF NOT b_dml_cache.EXISTS(p_dml_id) THEN
247 --not existing is fine here
248 x_return_status := FND_API.G_RET_STS_SUCCESS;
249 fnd_oam_debug.log(1, l_ctxt, 'DML ID ('||p_dml_id||') not found in cache.');
250 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
251 RETURN;
252 END IF;
253
254 --call the internal procedure to do the work and return the status of the operation
255 INTERNAL_DESTROY_CACHE_ENTRY(p_dml_id,
256 px_arg_context,
257 p_update_context,
258 x_return_status,
259 x_return_msg);
260
261 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
262 EXCEPTION
263 WHEN OTHERS THEN
264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
265 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
266 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
267 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
268 END;
269
270 -- Public
271 PROCEDURE DESTROY_DML_CACHE(px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
272 x_return_status OUT NOCOPY VARCHAR2,
273 x_return_msg OUT NOCOPY VARCHAR2)
274 IS
275 l_ctxt VARCHAR2(60) := PKG_NAME||'DESTROY_DML_CACHE';
276
277 k NUMBER;
278 l_found_failure BOOLEAN := FALSE;
279
280 l_return_status VARCHAR2(6);
281 l_return_msg VARCHAR2(2048);
282 BEGIN
283 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
284 x_return_status := FND_API.G_RET_STS_ERROR;
285 x_return_msg := '';
286
287 --loop through what's left in the cache, destroying each, ignore rolling the args into the context
288 k := b_dml_cache.FIRST;
289 WHILE k IS NOT NULL LOOP
290 fnd_oam_debug.log(1, l_ctxt, 'DML ID: '||k);
291 INTERNAL_DESTROY_CACHE_ENTRY(k,
292 px_arg_context,
293 FALSE,
294 l_return_status,
295 l_return_msg);
296 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
297 l_found_failure := TRUE;
298 --don't return, let it try to destroy the others first
299 x_return_status := l_return_status;
300 x_return_msg := l_return_msg;
301 END IF;
302
303 k := b_dml_cache.NEXT(k);
304 END LOOP;
305
306 --delete all members of the cache even if some failed
307 b_dml_cache.DELETE;
308
309 IF NOT l_found_failure THEN
310 x_return_status := FND_API.G_RET_STS_SUCCESS;
311 END IF;
312 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
313 EXCEPTION
314 WHEN OTHERS THEN
315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
316 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
317 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
318 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
319 END;
320
321 -- Public
322 PROCEDURE COMPLETE_DML(p_dml_id IN NUMBER,
323 p_finished_ret_sts IN VARCHAR2,
324 p_message IN VARCHAR2,
325 p_workers_assigned IN NUMBER,
326 x_return_status OUT NOCOPY VARCHAR2,
327 x_return_msg OUT NOCOPY VARCHAR2)
328 IS
329 l_ctxt VARCHAR2(60) := PKG_NAME||'COMPLETE_DML';
330
331 l_message VARCHAR2(2048);
332 l_finished_ret_sts VARCHAR2(3);
333 l_finished_status VARCHAR2(30);
334 l_rows_processed NUMBER := 0;
335
336 l_dml_in_cache BOOLEAN;
337
338 l_return_status VARCHAR2(6);
339 l_return_msg VARCHAR2(2048);
340 BEGIN
341 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
342 x_return_status := FND_API.G_RET_STS_ERROR;
343 x_return_msg := '';
344
345 --make sure the dml's in the cache
346 IF b_dml_cache.EXISTS(p_dml_id) THEN
347 l_dml_in_cache := TRUE;
348 ELSE
349 -- its possible for a split dml to execute but not have any ad units. In this case,
350 -- the dml will not make it into the cache since there's no point in parsing it. Instead
351 -- we just need to complete the record if workers_assigned = 1.
352 l_dml_in_cache := FALSE;
353 fnd_oam_debug.log(1, l_ctxt, 'DML id ('||p_dml_id||') not in dml cache. This happens.');
354 END IF;
355
356 --perform a blocking select for update on the DML, allows sequencing of rows_processed updates
357 SELECT finished_ret_sts
358 INTO l_finished_ret_sts
359 FROM fnd_oam_dscram_dmls
360 WHERE dml_id = p_dml_id
361 FOR UPDATE;
362
363 --make sure it's not already finished, only the last complete_dml should do this
364 IF l_finished_ret_sts IS NOT NULL THEN
365 x_return_msg := 'DML id ('||p_dml_id||') has finished already set. This should not happen.';
366 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
367 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
368 --don't rollback, parent can do it
369 RETURN;
370 END IF;
371
372 --only deal with the finished state if we're the last worker
373 IF p_workers_assigned IS NOT NULL AND p_workers_assigned = 1 THEN
374 --default the status, message to those of the last execute if not passed in explicitly
375 IF l_dml_in_cache THEN
376 l_finished_ret_sts := NVL(p_finished_ret_sts, b_dml_cache(p_dml_id).last_execute_ret_sts);
377 l_message := NVL(p_message, b_dml_cache(p_dml_id).last_execute_ret_msg);
378 l_rows_processed := NVL(b_dml_cache(p_dml_id).rows_processed, 0);
379 ELSE
380 l_finished_ret_sts := p_finished_ret_sts;
381 l_message := p_message;
382 END IF;
383 l_finished_status := FND_OAM_DSCRAM_UTILS_PKG.CONV_RET_STS_TO_COMPL_STATUS(l_finished_ret_sts);
384
385 fnd_oam_debug.log(1, l_ctxt, 'Finished DML with Status: '||l_finished_status||'('||l_finished_ret_sts||')');
386
387 --update the dml
388 UPDATE fnd_oam_dscram_dmls
389 SET finished_ret_sts = l_finished_ret_sts
390 WHERE dml_id = p_dml_id;
391
392 --dump a stats row as well
393 FND_OAM_DSCRAM_STATS_PKG.COMPLETE_ENTRY(p_source_object_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
394 p_source_object_id => p_dml_id,
395 p_end_time => SYSDATE,
396 p_end_message => l_message,
397 p_postend_status => l_finished_status);
398 ELSIF l_dml_in_cache THEN
399 l_rows_processed := NVL(b_dml_cache(p_dml_id).rows_processed, 0);
400 END IF;
401
402 --always update the number of rows processed
403 UPDATE fnd_oam_dscram_dmls
404 SET rows_processed = NVL(rows_processed, 0) + l_rows_processed, --needed since its different for each dml and not stored in AD
405 last_updated_by = fnd_global.user_id,
406 last_update_login = fnd_global.user_id,
407 last_update_date = SYSDATE
408 WHERE dml_id = p_dml_id;
409
410 --exit without committing, leave that to the parent
411
412 --success
413 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
414 x_return_status := FND_API.G_RET_STS_SUCCESS;
415 EXCEPTION
416 WHEN OTHERS THEN
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
418 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
419 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
420 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
421 END;
422
423 -- Private wrapper on COMPLETE_DML to do it in an autonomous transaction. This is often used when
424 -- a piece of work has failed and we want to update the metadata even though the data will be
425 -- rolled back.
426 PROCEDURE COMPLETE_DML_AUTONOMOUSLY(p_dml_id IN NUMBER,
427 p_finished_ret_sts IN VARCHAR2,
428 p_message IN VARCHAR2,
429 p_workers_assigned IN NUMBER,
430 x_return_status OUT NOCOPY VARCHAR2,
431 x_return_msg OUT NOCOPY VARCHAR2)
432 IS
433 PRAGMA AUTONOMOUS_TRANSACTION;
434
435 l_ctxt VARCHAR2(60) := PKG_NAME||'COMPLETE_DML_AUTONOMOUSLY';
436 BEGIN
437 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
438 COMPLETE_DML(p_dml_id,
439 p_finished_ret_sts,
440 p_message,
441 p_workers_assigned,
442 x_return_status,
443 x_return_msg);
444 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
445 COMMIT;
446 fnd_oam_debug.log(1, l_ctxt, 'Committed completion of DML ('||p_dml_id||')');
447 ELSE
448 fnd_oam_debug.log(3, l_ctxt, 'Failed to complete DML ('||p_dml_id||') autonomously: status('||x_return_status||'), message('||x_return_msg||')');
449 ROLLBACK;
450 END IF;
451 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
452 END;
453
454 -- Private: helper to ADD_DML_TO_CACHE to prepare the final statement for a DML. This is
455 -- autonomous to keep from commiting data on the transaction.
456 PROCEDURE GENERATE_FINAL_DML_STMT(p_dml_id IN NUMBER,
457 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
458 p_use_splitting IN BOOLEAN,
459 x_final_dml_stmt OUT NOCOPY VARCHAR2,
460 x_return_status OUT NOCOPY VARCHAR2,
461 x_return_msg OUT NOCOPY VARCHAR2)
462 IS
463 PRAGMA AUTONOMOUS_TRANSACTION;
464
465 l_ctxt VARCHAR2(60) := PKG_NAME||'GENERATE_FINAL_DML_STMT';
466
467 l_dml_stmt VARCHAR2(4000);
468 l_where_clause VARCHAR2(4000);
469 l_final_dml_stmt VARCHAR2(4000);
470
471 l_stmt_length NUMBER;
472 l_stmt_maxlen NUMBER := 4000;
473
474 l_return_status VARCHAR2(6);
475 l_return_msg VARCHAR2(2048);
476 BEGIN
477 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
478 x_return_status := FND_API.G_RET_STS_ERROR;
479 x_return_msg := '';
480
481 --assume that the check already occured in add_dml_to_cache so proceed right to the lock
482 SELECT dml_stmt, dml_where_clause, dml_final_stmt
483 INTO l_dml_stmt, l_where_clause, l_final_dml_stmt
484 FROM fnd_oam_dscram_dmls
485 WHERE dml_id = p_dml_id
486 FOR UPDATE;
487
488 --make sure the final stmt's still null
489 IF l_final_dml_stmt IS NOT NULL THEN
490 x_final_dml_stmt := l_final_dml_stmt;
491 ROLLBACK;
492 x_return_status := FND_API.G_RET_STS_SUCCESS;
493 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
494 RETURN;
495 END IF;
496
497 --prepare the final stmt from the stmt, where clause and possible the rowid restrict where clause
498 FND_OAM_DSCRAM_UTILS_PKG.MAKE_FINAL_SQL_STMT(px_arg_context,
499 l_dml_stmt,
500 l_where_clause,
501 p_use_splitting,
502 l_final_dml_stmt,
503 l_return_status,
504 l_return_msg);
505 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
506 x_return_status := l_return_status;
507 x_return_msg := l_return_msg;
508 --commit the stats entry, make_final just does string manipulation
509 COMMIT;
510 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
511 RETURN;
512 END IF;
513
514 --store this string as the final stmt
515 UPDATE fnd_oam_dscram_dmls
516 SET dml_final_stmt = l_final_dml_stmt,
517 stats_finished = FND_API.G_FALSE,
518 last_updated_by = fnd_global.user_id,
519 last_update_login = fnd_global.user_id,
520 last_update_date = SYSDATE
521 WHERE dml_id = p_dml_id;
522
523 --commit the autonomous txn to release the lock
524 COMMIT;
525
526 --success
527 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
528 x_return_status := FND_API.G_RET_STS_SUCCESS;
529 x_final_dml_stmt := l_final_dml_stmt;
530 EXCEPTION
531 WHEN OTHERS THEN
532 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
533 x_return_msg := 'DML ID ('||p_dml_id||'), failed to generate final dml stmt: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
534 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
535 ROLLBACK;
536 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
537 END;
538
539 -- Private helper to EXECUTE_DML* to take a foreign dml_id and prepare it by generating the
540 -- final dml statement if necessary, parsing it into a cursor and fetching the arg list
541 PROCEDURE ADD_DML_TO_CACHE(p_dml_id IN NUMBER,
542 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
543 p_use_splitting IN BOOLEAN,
544 x_return_status OUT NOCOPY VARCHAR2,
545 x_return_msg OUT NOCOPY VARCHAR2)
546 IS
547 l_ctxt VARCHAR2(60) := PKG_NAME||'ADD_DML_TO_CACHE';
548
549 l_stat_id NUMBER;
550 l_cache_entry b_dml_cache_entry;
551 l_final_dml_stmt VARCHAR2(4000);
552 l_cursor_id INTEGER;
553 l_arg_list FND_OAM_DSCRAM_ARGS_PKG.arg_list;
554 l_has_writable_args BOOLEAN;
555
556 l_return_status VARCHAR2(6);
557 l_return_msg VARCHAR2(2048);
558 l_temp VARCHAR2(30);
559 BEGIN
560 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
561 x_return_status := FND_API.G_RET_STS_ERROR;
562 x_return_msg := '';
563
564 --if the dml isn't cached, there's a chance that its stats row hasn't been created yet, query and
565 --create if necessary. This is not mt-safe but extra calls to create_entry will be discarded due to
566 --the unique index on fnd_oam_dscram_stats.
567 IF NOT FND_OAM_DSCRAM_STATS_PKG.HAS_ENTRY(FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
568 p_dml_id) THEN
569 --autonomously create the stats entry
570 FND_OAM_DSCRAM_STATS_PKG.CREATE_ENTRY_AUTONOMOUSLY(p_source_object_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
571 p_source_object_id => p_dml_id,
572 p_start_time => SYSDATE,
573 p_prestart_status => NULL,
574 p_dismiss_failure => FND_API.G_TRUE,
575 x_stat_id => l_stat_id);
576 END IF;
577
578 --query out the prepared DML statement
579 SELECT dml_final_stmt
580 INTO l_final_dml_stmt
581 FROM fnd_oam_dscram_dmls
582 WHERE dml_id = p_dml_id;
583
584 --if it's not present, generate it
585 IF l_final_dml_stmt IS NULL THEN
586 GENERATE_FINAL_DML_STMT(p_dml_id,
587 px_arg_context,
588 p_use_splitting,
589 l_final_dml_stmt,
590 l_return_status,
591 l_return_msg);
592 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
593 x_return_status := l_return_status;
594 x_return_msg := l_return_msg;
595 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
596 RETURN;
597 END IF;
598 END IF;
599
600 --got a final stmt, put it into a cursor
601 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
602 BEGIN
603 DBMS_SQL.PARSE(l_cursor_id,
604 l_final_dml_stmt,
605 DBMS_SQL.NATIVE);
606 EXCEPTION
607 WHEN OTHERS THEN
608 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
609 x_return_msg := 'DML_ID ('||p_dml_id||'), failed to parse final stmt: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
610 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
611 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
612 RETURN;
613 END;
614
615 --now lets get the arg list for the dml
616 FND_OAM_DSCRAM_ARGS_PKG.FETCH_ARG_LIST(FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
617 p_dml_id,
618 l_arg_list,
619 l_has_writable_args,
620 l_return_status,
621 l_return_msg);
622 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
623 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
624 x_return_status := l_return_status;
625 x_return_msg := l_return_msg;
626 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
627 RETURN;
628 END IF;
629
630 --SUCCESS, update internal structures
631
632 --initialize the entry
633 l_cache_entry.dml_id := p_dml_id;
634 l_cache_entry.cursor_id := l_cursor_id;
635 l_cache_entry.arg_list := l_arg_list;
636 l_cache_entry.use_splitting := p_use_splitting;
637 l_cache_entry.has_writable_args := l_has_writable_args;
638 l_cache_entry.rows_processed := 0;
639 l_cache_entry.last_execute_ret_sts := NULL;
640 l_cache_entry.last_execute_ret_msg := NULL;
641
642 --store the entry in the cache
643 b_dml_cache(p_dml_id) := l_cache_entry;
644
645 --success
646 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
647 x_return_status := FND_API.G_RET_STS_SUCCESS;
648 EXCEPTION
649 WHEN OTHERS THEN
650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
651 x_return_msg := 'DML ID ('||p_dml_id||'), failed to add to cache: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
652 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
653 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
654 END;
655
656 -- Private, helper to EXECUTE_DML* procedures to do the grunt work. the call to complete the DML
657 -- will be done by the caller. No commits or rollbacks are done here.
658 PROCEDURE INTERNAL_EXECUTE_DML(p_dml_id IN NUMBER,
659 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
660 p_use_splitting IN BOOLEAN,
661 p_rowid_lbound IN ROWID,
662 p_rowid_ubound IN ROWID,
663 x_rows_processed OUT NOCOPY NUMBER,
664 x_return_status OUT NOCOPY VARCHAR2,
665 x_return_msg OUT NOCOPY VARCHAR2)
666 IS
667 l_ctxt VARCHAR2(60) := PKG_NAME||'INTERNAL_EXECUTE_DML';
668
669 l_rows_processed NUMBER;
670
671 l_return_status VARCHAR2(6);
672 l_return_msg VARCHAR2(2048);
673 BEGIN
674 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
675 x_return_status := FND_API.G_RET_STS_ERROR;
676 x_return_msg := '';
677
678 fnd_oam_debug.log(1, l_ctxt, 'DML ID: '||p_dml_id);
679
680 --set the current dml id before binding args to allow for access of the dml_id from a state argument
681 b_current_dml_id := p_dml_id;
682
683 --first see if the dml's in the cache
684 IF b_dml_cache.EXISTS(p_dml_id) THEN
685 --make sure the cursor isn't configured for splitting
686 IF b_dml_cache(p_dml_id).use_splitting <> p_use_splitting THEN
687 x_return_msg := 'DML ID ('||p_dml_id||'), cached splitting enabled not equal to provided splitting enabled. This should not happen.';
688 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
689 RAISE EXECUTE_FAILED;
690 END IF;
691 ELSE
692 --no cache entry, create one
693 ADD_DML_TO_CACHE(p_dml_id,
694 px_arg_context,
695 p_use_splitting,
696 l_return_status,
697 l_return_msg);
698 --react to the return status to skip execution if we failed
699 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
700 x_return_status := l_return_status;
701 x_return_msg := l_return_msg;
702 RAISE EXECUTE_FAILED;
703 END IF;
704 END IF;
705
706 --before every execution, re-bind the readable argument values. This may or may not change the
707 --value of the binding depending on the arg's write policy.
708 FND_OAM_DSCRAM_ARGS_PKG.BIND_ARG_LIST_TO_CURSOR(b_dml_cache(p_dml_id).arg_list,
709 px_arg_context,
710 b_dml_cache(p_dml_id).cursor_id,
711 p_use_splitting,
712 p_rowid_lbound,
713 p_rowid_ubound,
714 l_return_status,
715 l_return_msg);
716 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
717 x_return_status := l_return_status;
718 x_return_msg := l_return_msg;
719 RAISE EXECUTE_FAILED;
720 END IF;
721
722 --cache entry should be valid now, do additional bindings if needed
723 IF p_use_splitting THEN
724 fnd_oam_debug.log(1, l_ctxt, 'Binding lower('||p_rowid_lbound||') and upper('||p_rowid_ubound||') rowids');
725 DBMS_SQL.BIND_VARIABLE(b_dml_cache(p_dml_id).cursor_id,
726 FND_OAM_DSCRAM_UTILS_PKG.G_ARG_ROWID_LBOUND_NAME,
727 p_rowid_lbound);
728 DBMS_SQL.BIND_VARIABLE(b_dml_cache(p_dml_id).cursor_id,
729 FND_OAM_DSCRAM_UTILS_PKG.G_ARG_ROWID_UBOUND_NAME,
730 p_rowid_ubound);
731 END IF;
732
733 --skip the execute if we're in test-no-exec mode
734 IF FND_OAM_DSCRAM_RUNS_PKG.GET_RUN_MODE = FND_OAM_DSCRAM_UTILS_PKG.G_MODE_TEST_NO_EXEC THEN
735 fnd_oam_debug.log(1, l_ctxt, 'Skipping Executiong due to run mode.');
736 l_rows_processed := 0;
737 ELSE
738 --do the execute
739 fnd_oam_debug.log(1, l_ctxt, 'Executing cursor...');
740 BEGIN
741 l_rows_processed := DBMS_SQL.EXECUTE(b_dml_cache(p_dml_id).cursor_id);
742 EXCEPTION
743 WHEN OTHERS THEN
744 x_return_msg := 'SQL execute error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
745 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
746 RAISE EXECUTE_FAILED;
747 END;
748 fnd_oam_debug.log(1, l_ctxt, '...Done.('||l_rows_processed||' rows)');
749 END IF;
750
751 -- If the dml has any output variables, we should get values for them now if we can
752 IF b_dml_cache(p_dml_id).has_writable_args THEN
753 FND_OAM_DSCRAM_ARGS_PKG.UPDATE_WRITABLE_ARG_VALUES(b_dml_cache(p_dml_id).arg_list,
754 px_arg_context,
755 NOT p_use_splitting, --we're finished if we're not splitting
756 p_use_splitting,
757 p_rowid_lbound,
758 p_rowid_ubound,
759 b_dml_cache(p_dml_id).cursor_id,
760 l_return_status,
761 l_return_msg);
762 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
763 x_return_status := l_return_status;
764 x_return_msg := l_return_msg;
765 RAISE EXECUTE_FAILED;
766 END IF;
767 END IF;
768
769 -- Success
770 b_current_dml_id := NULL;
771 x_return_status := FND_API.G_RET_STS_SUCCESS;
772 x_rows_processed := l_rows_processed;
773 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
774 EXCEPTION
775 WHEN EXECUTE_FAILED THEN
776 b_current_dml_id := NULL;
777 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
778 RETURN;
779 WHEN OTHERS THEN
780 b_current_dml_id := NULL;
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
782 x_return_msg := 'Unhandled Exception: [Code('||SQLCODE||'), Message("'||SQLERRM||'")]';
783 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
784 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
785 RETURN;
786 END;
787
788 -- Public
789 PROCEDURE EXECUTE_DML(p_dml_id IN NUMBER,
790 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
791 x_return_status OUT NOCOPY VARCHAR2,
792 x_return_msg OUT NOCOPY VARCHAR2)
793 IS
794 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_DML';
795 l_rows_processed NUMBER;
796
797 l_return_status VARCHAR2(6);
798 l_return_msg VARCHAR2(2048);
799 l_return_status2 VARCHAR2(6);
800 l_return_msg2 VARCHAR2(2048);
801 BEGIN
802 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
803
804 --do the single-threaded execute
805 INTERNAL_EXECUTE_DML(p_dml_id,
806 px_arg_context,
807 FALSE,
808 NULL,
809 NULL,
810 l_rows_processed,
811 l_return_status,
812 l_return_msg);
813
814 -- if successful, complete the dml
815 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
816 --update the rows dml cache's derived attributes
817 b_dml_cache(p_dml_id).rows_processed := b_dml_cache(p_dml_id).rows_processed + l_rows_processed;
818 b_dml_cache(p_dml_id).last_execute_ret_sts := l_return_status;
819 b_dml_cache(p_dml_id).last_execute_ret_msg := l_return_msg;
820
821 --complete differently based on the run mode
822 IF FND_OAM_DSCRAM_UTILS_PKG.RUN_IS_NORMAL THEN
823 --complete the dml as part of the transaction
824 COMPLETE_DML(p_dml_id,
825 l_return_status,
826 l_return_msg,
827 1,
828 x_return_status,
829 x_return_msg);
830 ELSE
831 --if not in normal mode, or our writable args action failed, we need to complete autonomously
832 COMPLETE_DML_AUTONOMOUSLY(p_dml_id,
833 l_return_status,
834 l_return_msg,
835 1,
836 x_return_status,
837 x_return_msg);
838 END IF;
839 ELSE
840 --update the rows dml cache's derived attributes
841 IF b_dml_cache.EXISTS(p_dml_id) THEN
842 b_dml_cache(p_dml_id).last_execute_ret_sts := l_return_status;
843 b_dml_cache(p_dml_id).last_execute_ret_msg := l_return_msg;
844 END IF;
845
846 --if failed, complete it autonomously since the data will be rolled back and errors override
847 --this does not need to be atomic with the unit update since individually executed DMLs are
848 --not queried concurrently and passing the data back to the unit to commit is extra unnecessary work.
849 COMPLETE_DML_AUTONOMOUSLY(p_dml_id,
850 l_return_status,
851 l_return_msg,
852 1,
853 l_return_status2,
854 l_return_msg2);
855
856 --return status/msg of execute failure, not possible complete failure
857 x_return_status := l_return_status;
858 x_return_msg := l_return_msg;
859 END IF;
860
861 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
862 EXCEPTION
863 WHEN OTHERS THEN
864 x_return_msg := 'Unhandled Exception: [Code('||SQLCODE||'), Message("'||SQLERRM||'")]';
865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
866 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
867 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
868 END;
869
870 -- Public
871 PROCEDURE EXECUTE_DML_ON_RANGE(p_dml_id IN NUMBER,
872 px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context,
873 p_rowid_lbound IN ROWID,
874 p_rowid_ubound IN ROWID,
875 x_rows_processed OUT NOCOPY NUMBER,
876 x_return_status OUT NOCOPY VARCHAR2,
877 x_return_msg OUT NOCOPY VARCHAR2)
878 IS
879 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_DML_ON_RANGE';
880
881 l_rows_processed NUMBER;
882
883 l_return_status VARCHAR2(6);
884 l_return_msg VARCHAR2(2048);
885 BEGIN
886 x_return_status := FND_API.G_RET_STS_ERROR;
887 x_return_msg := '';
888
889 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
890 INTERNAL_EXECUTE_DML(p_dml_id,
891 px_arg_context,
892 TRUE,
893 p_rowid_lbound,
894 p_rowid_ubound,
895 l_rows_processed,
896 l_return_status,
897 l_return_msg);
898 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
899 --update the rows dml cache's derived attributes
900 b_dml_cache(p_dml_id).rows_processed := b_dml_cache(p_dml_id).rows_processed + l_rows_processed;
901 b_dml_cache(p_dml_id).last_execute_ret_sts := l_return_status;
902 b_dml_cache(p_dml_id).last_execute_ret_msg := l_return_msg;
903 ELSE
904 --update the rows dml cache's derived attributes
905 IF b_dml_cache.EXISTS(p_dml_id) THEN
906 b_dml_cache(p_dml_id).last_execute_ret_sts := l_return_status;
907 b_dml_cache(p_dml_id).last_execute_ret_msg := l_return_msg;
908 END IF;
909
910 --return status/msg of execute failure
911 x_rows_processed := 0;
912 x_return_status := l_return_status;
913 x_return_msg := l_return_msg;
914 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
915 RETURN;
916 END IF;
917
918 --success
919 x_rows_processed := l_rows_processed;
920 x_return_status := FND_API.G_RET_STS_SUCCESS;
921 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
922 EXCEPTION
923 WHEN OTHERS THEN
924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
925 x_return_msg := 'Unhandled Exception: [Code('||SQLCODE||'), Message("'||SQLERRM||'")]';
926 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
927 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
928 END;
929
930 END FND_OAM_DSCRAM_DMLS_PKG;
[Home] [Help]