[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_DSCRAM_DIAG_PKG
Source
1 PACKAGE BODY FND_OAM_DSCRAM_DIAG_PKG as
2 /* $Header: AFOAMDSDIAGB.pls 120.10 2006/01/17 13:57 ilawler noship $ */
3
4 ----------------------------------------
5 -- Private Body Constants
6 ----------------------------------------
7 PKG_NAME CONSTANT VARCHAR2(20) := 'DSCRAM_DIAG_PKG.';
8 INIT_FAILED EXCEPTION;
9 SYNC_FAILED EXCEPTION;
10 VALIDATE_FAILED EXCEPTION;
11
12 -- # of seconds to wait when trying to sync the workers of a run on completion.
13 B_MAX_WAIT CONSTANT NUMBER := 600;
14
15 --run name prefix
16 B_DIAG_RUN_NAME_PREFIX CONSTANT VARCHAR2(20) := 'Diagnostic Test: ';
17
18 --test table default params
19 B_TEST_TABLE_OWNER CONSTANT VARCHAR2(30) := 'APPS';
20 B_TEST_TABLE_TABLESPACE CONSTANT VARCHAR2(30) := 'APPS_TS_TX_DATA';
21 B_TEST_TABLE_NAME_PREFIX CONSTANT VARCHAR2(20) := 'FND_OAM_DSCRAM_TT_';
22 --table serving as the master for test data so we don't re-create it each time
23 B_TEST_TABLE_MASTER_NAME CONSTANT VARCHAR2(30) := B_TEST_TABLE_NAME_PREFIX||'MASTER';
24
25 ----------------------------------------
26 -- Public Functions/Procedures
27 ----------------------------------------
28
29 --helper to drop table
30 FUNCTION DROP_TEST_TABLE_INDICIES(p_table_name IN VARCHAR2,
31 p_owner IN VARCHAR2)
32 RETURN BOOLEAN
33 IS
34 l_ctxt VARCHAR2(60) := PKG_NAME||'DROP_TEST_TABLE_INDICIES';
35
36 l_stmt VARCHAR2(2000);
37 index_missing EXCEPTION;
38 PRAGMA EXCEPTION_INIT(index_missing, -1418);
39 BEGIN
40 fnd_oam_debug.log(1, l_ctxt, 'Dropping test table indicies');
41 l_stmt := 'DROP INDEX '||p_table_name||'_N1';
42 EXECUTE IMMEDIATE l_stmt;
43 l_stmt := 'DROP INDEX '||p_table_name||'_N2';
44 EXECUTE IMMEDIATE l_stmt;
45 RETURN TRUE;
46 EXCEPTION
47 WHEN index_missing THEN
48 RETURN TRUE;
49 WHEN OTHERS THEN
50 fnd_oam_debug.log(6, l_ctxt, 'Unhandled exception: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
51 RETURN FALSE;
52 END;
53
54 --Private, helper to MAKE_TEST_TABLE to drop it if it already exists
55 FUNCTION DROP_TEST_TABLE(p_table_name IN VARCHAR2,
56 p_owner IN VARCHAR2)
57 RETURN BOOLEAN
58 IS
59 l_ctxt VARCHAR2(60) := PKG_NAME||'DROP_TEST_TABLE';
60
61 l_stmt VARCHAR2(2000);
62 BEGIN
63 IF NOT DROP_TEST_TABLE_INDICIES(p_table_name,
64 p_owner) THEN
65 RETURN FALSE;
66 END IF;
67
68 fnd_oam_debug.log(1, l_ctxt, 'Dropping table');
69 l_stmt := 'DROP TABLE '||p_owner||'.'||p_table_name;
70 EXECUTE IMMEDIATE l_stmt;
71 RETURN TRUE;
72 EXCEPTION
73 WHEN OTHERS THEN
74 fnd_oam_debug.log(6, l_ctxt, 'Unhandled exception: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
75 RETURN FALSE;
76 END;
77
78 --helper to make_test_table
79 FUNCTION MAKE_TEST_TABLE_INDICIES(p_table_name IN VARCHAR2,
80 p_owner IN VARCHAR2)
81 RETURN BOOLEAN
82 IS
83 l_ctxt VARCHAR2(60) := PKG_NAME||'MAKE_TEST_TABLE_INDICIES';
84
85 l_stmt VARCHAR2(2000);
86 BEGIN
87 fnd_oam_debug.log(1, l_ctxt, 'Creating Test Table Indicies');
88 l_stmt := 'CREATE UNIQUE INDEX '||p_table_name||'_U1 ON '||p_table_name||' (C1)';
89 EXECUTE IMMEDIATE l_stmt;
90 l_stmt := 'CREATE INDEX '||p_table_name||'_N1 ON '||p_table_name||' (C3)';
91 EXECUTE IMMEDIATE l_stmt;
92 RETURN TRUE;
93 EXCEPTION
94 WHEN OTHERS THEN
95 fnd_oam_debug.log(6, l_ctxt, 'Unhandled exception: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
96 RETURN FALSE;
97 END;
98
99 --Private helper to make_test_table to create the data
100 FUNCTION MAKE_TEST_TABLE_DATA(p_table_name IN VARCHAR2,
101 p_num_rows IN NUMBER,
102 p_owner IN VARCHAR2)
103 RETURN BOOLEAN
104 IS
105 l_ctxt VARCHAR2(60) := PKG_NAME||'MAKE_TEST_TABLE_DATA';
106
107 l_stmt VARCHAR2(2000);
108 k NUMBER;
109 v2 NUMBER := 1;
110 v3 VARCHAR2(30) := 'ROWNAME';
111 v4 DATE := FND_DATE.CANONICAL_TO_DATE('2005/08/30 11:22:33');
112 v5 VARCHAR2(2000) := 'ROWDESC';
113 v5_len NUMBER := 993;
114 v5_tmp VARCHAR2(2000);
115
116 v1_vals DBMS_SQL.NUMBER_TABLE;
117 v2_vals DBMS_SQL.NUMBER_TABLE;
118 v3_vals DBMS_SQL.VARCHAR2_TABLE;
119 v4_vals DBMS_SQL.DATE_TABLE;
120 v5_vals DBMS_SQL.VARCHAR2_TABLE;
121 BEGIN
122 fnd_oam_debug.log(1, l_ctxt, 'Creating Test Table Data');
123 l_stmt := 'INSERT INTO '||p_owner||'.'||p_table_name||' (C1, C2, C3, C4, C5) VALUES (:1, :2, :3, :4, :5)';
124
125 --if below some threshold, prepare in memory and insert
126 --DBMS_RANDOM.initialize(100);
127 IF p_num_rows <= 1000000 THEN
128 fnd_oam_debug.log(1, l_ctxt, 'Using Buffer and Bulk Bind method.');
129 k := 1;
130 WHILE k <= p_num_rows LOOP
131 v1_vals(k) := k;
132 v2_vals(k) := v2;
133 v3_vals(k) := v3||to_char(round((k/20),0));
134 --v4_vals(k) := SYSDATE;
135 v4_vals(k) := v4;
136 --v5_vals(k) := v5||DBMS_RANDOM.STRING('U', v5_len);
137 v5_vals(k) := v5||k;
138 k := k + 1;
139 END LOOP;
140 FORALL k IN 1..p_num_rows
141 EXECUTE IMMEDIATE l_stmt USING v1_vals(k), v2_vals(k), v3_vals(k), v4_vals(k), v5_vals(k);
142 ELSE
143 fnd_oam_debug.log(1, l_ctxt, 'Using Serial Insert method...');
144 --populate the table
145 k := 0;
146 WHILE k < p_num_rows LOOP
147 --v4 := SYSDATE;
148 --v5_tmp := v5||DBMS_RANDOM.STRING('U', v5_len);
149 v5_tmp := v5||k;
150 EXECUTE IMMEDIATE l_stmt USING k, v2, v3||round(to_char(k/20),0), v4, v5_tmp;
151 k := k + 1;
152 IF MOD(k, 1000) = 0 THEN
153 COMMIT;
154 END IF;
155 END LOOP;
156 END IF;
157 --DBMS_RANDOM.terminate;
158 RETURN TRUE;
159 EXCEPTION
160 WHEN OTHERS THEN
161 fnd_oam_debug.log(6, l_ctxt, 'Unhandled exception: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
162 RETURN FALSE;
163 END;
164
165 -- Private, helper to the EXECUTE_* procedures to create a test table for ensuring diagnostic-style
166 -- tests don't manipulate data before executing diagnostic tests on real tables.
167 FUNCTION MAKE_TEST_TABLE(p_table_name IN VARCHAR2 DEFAULT B_TEST_TABLE_MASTER_NAME,
168 p_num_rows IN NUMBER DEFAULT 1000,
169 p_owner IN VARCHAR2 DEFAULT B_TEST_TABLE_OWNER,
170 p_tablespace IN VARCHAR2 DEFAULT B_TEST_TABLE_TABLESPACE)
171 RETURN BOOLEAN
172 IS
173 PRAGMA AUTONOMOUS_TRANSACTION;
174
175 l_ctxt VARCHAR2(60) := PKG_NAME||'MAKE_TEST_TABLE';
176
177 table_exists EXCEPTION;
178 tablespace_missing EXCEPTION;
179 PRAGMA EXCEPTION_INIT(table_exists, -955);
180 PRAGMA EXCEPTION_INIT(tablespace_missing, -959);
181
182 l_table_def VARCHAR2(2000);
183 l_tablespace_def VARCHAR2(1000);
184 l_nologging VARCHAR2(100) := 'NOLOGGING';
185 BEGIN
186 fnd_oam_debug.log(1, l_ctxt, 'Creating table: '||p_owner||'.'||p_table_name||'('||p_tablespace||')');
187 l_table_def := 'CREATE TABLE '||p_table_name||' (C1 NUMBER NOT NULL,
188 C2 NUMBER,
189 C3 VARCHAR2(30),
190 C4 DATE,
191 C5 VARCHAR2(2000))';
192 l_tablespace_def := 'TABLESPACE '||p_tablespace;
193
194 BEGIN
195 EXECUTE IMMEDIATE l_table_def||' '||l_tablespace_def||' '||l_nologging;
196 EXCEPTION
197 WHEN table_exists THEN
198 fnd_oam_debug.log(1, l_ctxt, 'Table already exists - dropping first.');
199 IF NOT DROP_TEST_TABLE(p_table_name,
200 p_owner) THEN
201 RETURN FALSE;
202 END IF;
203 --otherwise, try to create the table again
204 EXECUTE IMMEDIATE l_table_def||' '||l_tablespace_def||' '||l_nologging;
205 WHEN tablespace_missing THEN
206 fnd_oam_debug.log(1, l_ctxt, 'Tablespace missing, using default tablespace.');
207 BEGIN
208 EXECUTE IMMEDIATE l_table_def||' '||l_nologging;
209 EXCEPTION
210 WHEN table_exists THEN
211 fnd_oam_debug.log(1, l_ctxt, 'Table already exists - dropping first.');
212 IF NOT DROP_TEST_TABLE(p_table_name,
213 p_owner) THEN
214 RETURN FALSE;
215 END IF;
216 --otherwise, try to create the table again
217 EXECUTE IMMEDIATE l_table_def||' '||l_nologging;
218 WHEN OTHERS THEN
219 fnd_oam_debug.log(6, l_ctxt, 'Unhandled exception: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
220 RETURN FALSE;
221 END;
222 WHEN OTHERS THEN
223 fnd_oam_debug.log(6, l_ctxt, 'Unhandled exception: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
224 RETURN FALSE;
225 END;
226 COMMIT;
227
228 -- make the table's data
229 IF MAKE_TEST_TABLE_DATA(p_table_name,
230 p_num_rows,
231 p_owner) THEN
232 COMMIT;
233 ELSE
234 ROLLBACK;
235 RETURN FALSE;
236 END IF;
237
238 -- make the table's data
239 IF MAKE_TEST_TABLE_INDICIES(p_table_name,
240 p_owner) THEN
241 COMMIT;
242 ELSE
243 ROLLBACK;
244 RETURN FALSE;
245 END IF;
246
247 COMMIT;
248 RETURN TRUE;
249 EXCEPTION
250 WHEN OTHERS THEN
251 fnd_oam_debug.log(6, l_ctxt, 'Unhandled exception: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
252 ROLLBACK;
253 RETURN FALSE;
254 END;
255
256 -- Private, helper to the EXECUTE_* procedures to duplicate a test table. Faster than create.
257 FUNCTION DUPLICATE_TEST_TABLE(p_dest_table_name IN VARCHAR2,
258 p_src_table_name IN VARCHAR2 DEFAULT B_TEST_TABLE_MASTER_NAME,
259 p_dest_owner IN VARCHAR2 DEFAULT B_TEST_TABLE_OWNER,
260 p_src_owner IN VARCHAR2 DEFAULT B_TEST_TABLE_OWNER,
261 p_dest_tablespace IN VARCHAR2 DEFAULT B_TEST_TABLE_TABLESPACE)
262 RETURN BOOLEAN
263 IS
264 PRAGMA AUTONOMOUS_TRANSACTION;
265
266 l_ctxt VARCHAR2(60) := PKG_NAME||'DUPLICATE_TEST_TABLE';
267
268 table_exists EXCEPTION;
269 PRAGMA EXCEPTION_INIT(table_exists, -955);
270
271 l_stmt VARCHAR2(2000);
272 BEGIN
273 fnd_oam_debug.log(1, l_ctxt, 'Creating table: '||p_dest_owner||'.'||p_dest_table_name||' from table '||p_src_owner||'.'||p_src_table_name);
274 l_stmt := 'CREATE TABLE '||p_dest_table_name||' (C1 NOT NULL,
275 C2,
276 C3,
277 C4,
278 C5) TABLESPACE '||p_dest_tablespace|| 'NOLOGGING
279 AS SELECT * FROM '||p_src_owner||'.'||p_src_table_name;
280 BEGIN
281 EXECUTE IMMEDIATE l_stmt;
282 EXCEPTION
283 WHEN table_exists THEN
284 fnd_oam_debug.log(1, l_ctxt, 'Table already exists - dropping first.');
285 IF NOT DROP_TEST_TABLE(p_dest_table_name,
286 p_dest_owner) THEN
287 RETURN FALSE;
288 END IF;
289 --otherwise, try to create the table again
290 EXECUTE IMMEDIATE l_stmt;
291 WHEN OTHERS THEN
292 fnd_oam_debug.log(6, l_ctxt, 'Unhandled exception: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
293 RETURN FALSE;
294 END;
295
296 COMMIT;
297 RETURN TRUE;
298 EXCEPTION
299 WHEN OTHERS THEN
300 fnd_oam_debug.log(6, l_ctxt, 'Unhandled exception: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
301 ROLLBACK;
302 RETURN FALSE;
303 END;
304
305 -- Private, helper to the VALIDATE_* procedures to make sure diagnostic tests didn't modify the contents of
306 -- the test table.
307 FUNCTION VALIDATE_TEST_TABLE_UNCHANGED(p_table_name IN VARCHAR2 DEFAULT B_TEST_TABLE_MASTER_NAME,
308 p_num_rows IN NUMBER DEFAULT 1000,
309 p_owner IN VARCHAR2 DEFAULT B_TEST_TABLE_OWNER,
310 p_tablespace IN VARCHAR2 DEFAULT B_TEST_TABLE_TABLESPACE)
311 RETURN BOOLEAN
312 IS
313 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_TEST_TABLE_UNCHANGED';
314
315 l_stmt VARCHAR2(2000);
316 l_row_count NUMBER;
317 l_c2_sum NUMBER;
318 l_c3_bad_count NUMBER;
319 l_c4 DATE := FND_DATE.CANONICAL_TO_DATE('2005/08/30 11:22:33');
320 l_c4_bad_count NUMBER;
321 BEGIN
322 fnd_oam_debug.log(1, l_ctxt, 'Validating table: '||p_owner||'.'||p_table_name||'('||p_tablespace||')');
323
324 --check the row count
325 l_stmt := 'SELECT COUNT(ROWID) FROM '||p_owner||'.'||p_table_name;
326 BEGIN
327 EXECUTE IMMEDIATE l_stmt INTO l_row_count;
328 EXCEPTION
329 WHEN OTHERS THEN
330 fnd_oam_debug.log(1, l_ctxt, 'Error selecting row count: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
331 RETURN FALSE;
332 END;
333 IF l_row_count IS NULL OR l_row_count <> p_num_rows THEN
334 fnd_oam_debug.log(6, l_ctxt, 'Queried row count('||l_row_count||') unequal to expected row count('||p_num_rows||')');
335 RETURN FALSE;
336 END IF;
337
338 --check that the sum of C2 is the row count
339 l_stmt := 'SELECT SUM(C2) FROM '||p_owner||'.'||p_table_name;
340 BEGIN
341 EXECUTE IMMEDIATE l_stmt INTO l_c2_sum;
342 EXCEPTION
343 WHEN OTHERS THEN
344 fnd_oam_debug.log(1, l_ctxt, 'Error selecting C2 sum: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
345 RETURN FALSE;
346 END;
347 IF l_c2_sum IS NULL OR l_c2_sum <> p_num_rows THEN
348 fnd_oam_debug.log(6, l_ctxt, 'Column C2 has sum('||l_c2_sum||'), should be equal to the row count('||p_num_rows||')');
349 RETURN FALSE;
350 END IF;
351
352 --check that C3 has the proper setup
353 l_stmt := 'SELECT COUNT(ROWID) FROM '||p_owner||'.'||p_table_name||' WHERE C3 <> CONCAT(''ROWNAME'',to_char(round((C1/20),0)))';
354 BEGIN
355 EXECUTE IMMEDIATE l_stmt INTO l_c3_bad_count;
356 EXCEPTION
357 WHEN OTHERS THEN
358 fnd_oam_debug.log(1, l_ctxt, 'Error selecting C3 bad count: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
359 RETURN FALSE;
360 END;
361 IF l_c3_bad_count IS NULL OR l_c3_bad_count <> 0 THEN
362 fnd_oam_debug.log(6, l_ctxt, 'Found ('||l_c3_bad_count||') rows with invalid C3 values.');
363 RETURN FALSE;
364 END IF;
365
366 --check that C4 has the proper setup
367 l_stmt := 'SELECT COUNT(ROWID) FROM '||p_owner||'.'||p_table_name||' WHERE C4 <> :1';
368 BEGIN
369 EXECUTE IMMEDIATE l_stmt INTO l_c4_bad_count USING l_c4;
370 EXCEPTION
371 WHEN OTHERS THEN
372 fnd_oam_debug.log(1, l_ctxt, 'Error selecting C4 bad count: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
373 RETURN FALSE;
374 END;
375 IF l_c4_bad_count IS NULL OR l_c4_bad_count <> 0 THEN
376 fnd_oam_debug.log(6, l_ctxt, 'Found ('||l_c3_bad_count||') rows with invalid C4 values.');
377 RETURN FALSE;
378 END IF;
379
380 RETURN TRUE;
381 EXCEPTION
382 WHEN OTHERS THEN
383 fnd_oam_debug.log(6, l_ctxt, 'Unhandled exception: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
384 RETURN FALSE;
385 END;
386
387 -- Private, helper to the VALIDATE_* procedures to make sure diagnostic tests didn't modify the contents of
388 -- the test table.
389 FUNCTION VALIDATE_TEST_TABLE_ARG_VALUES(p_using_splitting IN BOOLEAN,
390 p_c2_arg_id IN NUMBER,
391 p_c2_target_sum IN NUMBER,
392 p_table_name IN VARCHAR2 DEFAULT B_TEST_TABLE_MASTER_NAME,
393 p_num_rows IN NUMBER DEFAULT 1000,
394 p_c3_arg_id IN NUMBER DEFAULT NULL,
395 p_c4_arg_id IN NUMBER DEFAULT NULL,
396 p_owner IN VARCHAR2 DEFAULT B_TEST_TABLE_OWNER,
397 p_tablespace IN VARCHAR2 DEFAULT B_TEST_TABLE_TABLESPACE)
398 RETURN BOOLEAN
399 IS
400 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_TEST_TABLE_ARG_VALUES';
401
402 l_valid_value_flag VARCHAR2(3);
403 l_canonical_value VARCHAR2(4000);
404 l_stmt VARCHAR2(2000);
405 l_c2_sum NUMBER;
406 l_c3_bad_count NUMBER;
407 l_c4 DATE := FND_DATE.CANONICAL_TO_DATE('2005/08/30 11:22:33');
408 l_c4_bad_count NUMBER;
409 BEGIN
410 fnd_oam_debug.log(1, l_ctxt, 'Validating Arg Values for table: '||p_owner||'.'||p_table_name||'('||p_tablespace||')');
411
412 --check the c2 value
413 BEGIN
414 IF p_using_splitting THEN
415 SELECT SUM(to_number(canonical_value))
416 INTO l_c2_sum
417 FROM fnd_oam_dscram_arg_values
418 WHERE arg_id = p_c2_arg_id
419 AND valid_value_flag = FND_API.G_TRUE;
420 ELSE
421 SELECT valid_value_flag, canonical_value
422 INTO l_valid_value_flag, l_canonical_value
423 FROM fnd_oam_dscram_args_b
424 WHERE arg_id = p_c2_arg_id;
425 IF l_valid_value_flag IS NULL OR l_valid_value_flag <> FND_API.G_TRUE THEN
426 fnd_oam_debug.log(6, l_ctxt, 'Valid value flag of C2 Sum is incorrect: '||l_valid_value_flag);
427 RETURN FALSE;
428 END IF;
429 l_c2_sum := FND_NUMBER.CANONICAL_TO_NUMBER(l_canonical_value);
430 END IF;
431 EXCEPTION
432 WHEN OTHERS THEN
433 fnd_oam_debug.log(6, l_ctxt, 'Failed to query arg value for C2 sum: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
434 RETURN FALSE;
435 END;
436 IF l_c2_sum IS NULL OR l_c2_sum <> p_c2_target_sum THEN
437 fnd_oam_debug.log(6, l_ctxt, 'C2 Sum('||l_c2_sum||') not equal to target value('||p_c2_target_sum||')');
438 RETURN FALSE;
439 END IF;
440
441 --check the c3 val
442 IF p_c3_arg_id IS NOT NULL THEN
443 BEGIN
444 IF p_using_splitting THEN
445 SELECT SUM(to_number(canonical_value))
446 INTO l_c3_bad_count
447 FROM fnd_oam_dscram_arg_values
448 WHERE arg_id = p_c3_arg_id
449 AND valid_value_flag = FND_API.G_TRUE;
450 ELSE
451 SELECT valid_value_flag, canonical_value
452 INTO l_valid_value_flag, l_canonical_value
453 FROM fnd_oam_dscram_args_b
454 WHERE arg_id = p_c3_arg_id;
455 IF l_valid_value_flag IS NULL OR l_valid_value_flag <> FND_API.G_TRUE THEN
456 fnd_oam_debug.log(6, l_ctxt, 'Valid value flag of C3 Bad Count is incorrect: '||l_valid_value_flag);
457 RETURN FALSE;
458 END IF;
459 l_c2_sum := FND_NUMBER.CANONICAL_TO_NUMBER(l_canonical_value);
460 END IF;
461 EXCEPTION
462 WHEN OTHERS THEN
463 fnd_oam_debug.log(6, l_ctxt, 'Failed to query arg value for C3 bad count: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
464 RETURN FALSE;
465 END;
466 IF l_c3_bad_count IS NULL OR l_c3_bad_count <> 0 THEN
467 fnd_oam_debug.log(6, l_ctxt, 'C3 Bad Count('||l_c3_bad_count||') not equal to zero.');
468 RETURN FALSE;
469 END IF;
470 END IF;
471
472 --check the c4 val
473 IF p_c4_arg_id IS NOT NULL THEN
474 BEGIN
475 IF p_using_splitting THEN
476 SELECT SUM(to_number(canonical_value))
477 INTO l_c4_bad_count
478 FROM fnd_oam_dscram_arg_values
479 WHERE arg_id = p_c4_arg_id
480 AND valid_value_flag = FND_API.G_TRUE;
481 ELSE
482 SELECT valid_value_flag, canonical_value
483 INTO l_valid_value_flag, l_canonical_value
484 FROM fnd_oam_dscram_args_b
485 WHERE arg_id = p_c4_arg_id;
486 IF l_valid_value_flag IS NULL OR l_valid_value_flag <> FND_API.G_TRUE THEN
487 fnd_oam_debug.log(6, l_ctxt, 'Valid value flag of C4 Bad Count is incorrect: '||l_valid_value_flag);
488 RETURN FALSE;
489 END IF;
490 l_c2_sum := FND_NUMBER.CANONICAL_TO_NUMBER(l_canonical_value);
491 END IF;
492 EXCEPTION
493 WHEN OTHERS THEN
494 fnd_oam_debug.log(6, l_ctxt, 'Failed to query arg value for C4 bad count: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
495 RETURN FALSE;
496 END;
497 IF l_c4_bad_count IS NULL OR l_c4_bad_count <> 0 THEN
498 fnd_oam_debug.log(6, l_ctxt, 'C4 Bad Count('||l_c4_bad_count||') not equal to zero.');
499 RETURN FALSE;
500 END IF;
501 END IF;
502
503 RETURN TRUE;
504 EXCEPTION
505 WHEN OTHERS THEN
506 fnd_oam_debug.log(6, l_ctxt, 'Unhandled exception: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
507 RETURN FALSE;
508 END;
509
510 --used by the SYNC functions to coordinate on a name for the finish message pipe
511 FUNCTION MAKE_RUN_PIPE_NAME(p_run_id IN NUMBER)
512 RETURN VARCHAR2
513 IS
514 BEGIN
515 RETURN FND_OAM_DSCRAM_UTILS_PKG.G_DSCRAM_GLOBAL_PREFIX||'PIPE_'||to_char(p_run_id);
516 END;
517
518 --used by the SYNC functions to coordinate on a name for the ack pipe used by the
519 --initializing worker to tell other workers when they can proceed and what the final
520 --status was for the previous test.
521 FUNCTION MAKE_RUN_ACK_PIPE_NAME(p_run_id IN NUMBER)
522 RETURN VARCHAR2
523 IS
524 BEGIN
525 RETURN FND_OAM_DSCRAM_UTILS_PKG.G_DSCRAM_GLOBAL_PREFIX||'ACKPIPE_'||to_char(p_run_id);
526 END;
527
528 --used in delete_all_diagnostic runs to clear out the pipes we use for sychronizing runs
529 FUNCTION CLEAR_PIPES(p_run_id IN NUMBER)
530 RETURN BOOLEAN
531 IS
532 l_ctxt VARCHAR2(60) := PKG_NAME||'CLEAR_PIPES';
533
534 l_run_pipe VARCHAR2(60) := MAKE_RUN_PIPE_NAME(p_run_id);
535 l_run_ack_pipe VARCHAR2(60) := MAKE_RUN_ACK_PIPE_NAME(p_run_id);
536 l_retval INTEGER;
537 BEGIN
538 --could also use DBMS_PIPE.PURGE(<pipe_name>) if we didn't want to know how many stale messages there were
539 WHILE TRUE LOOP
540 l_retval := DBMS_PIPE.RECEIVE_MESSAGE(l_run_pipe,
541 0);
542 IF l_retval = 0 THEN
543 fnd_oam_debug.log(1, l_ctxt, 'Removed message from run pipe.');
544 ELSE
545 EXIT;
546 END IF;
547 END LOOP;
548 WHILE TRUE LOOP
549 l_retval := DBMS_PIPE.RECEIVE_MESSAGE(l_run_ack_pipe,
550 0);
551 IF l_retval = 0 THEN
552 fnd_oam_debug.log(1, l_ctxt, 'Removed message from run ack pipe.');
553 ELSE
554 EXIT;
555 END IF;
556 END LOOP;
557
558 RETURN TRUE;
559 EXCEPTION
560 WHEN OTHERS THEN
561 fnd_oam_debug.log(6, l_ctxt, 'Error while cleaning pipes: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
562 RETURN FALSE;
563 END;
564
565 --Public
566 PROCEDURE DELETE_ALL_DIAGNOSTIC_RUNS(x_verdict OUT NOCOPY VARCHAR2)
567 IS
568 l_ctxt VARCHAR2(60) := PKG_NAME||'DELETE_ALL_DIAGNOSTIC_RUNS';
569
570 l_ids DBMS_SQL.NUMBER_TABLE;
571 k NUMBER;
572 l_retbool BOOLEAN;
573 BEGIN
574 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
575
576 --query out all runs in the diagnostic space
577 SELECT run_id
578 BULK COLLECT INTO l_ids
579 FROM fnd_oam_dscram_runs_b
580 WHERE run_id between 0 and 999;
581
582 IF SQL%NOTFOUND THEN
583 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
584 x_verdict := FND_API.G_TRUE;
585 RETURN;
586 END IF;
587
588 --loop over ids found, nuking
589 k := l_ids.FIRST;
590 l_retbool := TRUE;
591 WHILE k IS NOT NULL LOOP
592 IF NOT FND_OAM_DSCRAM_UTILS_PKG.DELETE_RUN(l_ids(k)) THEN
593 l_retbool := FALSE;
594 END IF;
595
596 IF NOT CLEAR_PIPES(l_ids(k)) THEN
597 l_retbool := FALSE;
598 END IF;
599 k := l_ids.NEXT(k);
600 END LOOP;
601
602 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
603 IF l_retbool THEN
604 x_verdict := FND_API.G_TRUE;
605 ELSE
606 x_verdict := FND_API.G_FALSE;
607 END IF;
608 EXCEPTION
609 WHEN OTHERS THEN
610 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
611 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
612 x_verdict := FND_API.G_FALSE;
613 RETURN;
614 END;
615
616 -- helper to remove a list of global arguments since these would affect later tests
617 FUNCTION DELETE_GLOBAL_ARGS(p_global_arg_names IN DBMS_SQL.VARCHAR2_TABLE)
618 RETURN BOOLEAN
619 IS
620 l_ctxt VARCHAR2(60) := PKG_NAME||'DELETE_GLOBAL_ARGS';
621 k NUMBER;
622 l_arg_id NUMBER;
623 l_arg_name VARCHAR2(60);
624 BEGIN
625 k := p_global_arg_names.FIRST;
626 WHILE k IS NOT NULL LOOP
627 l_arg_name := p_global_arg_names(k);
628 fnd_oam_debug.log(1, l_ctxt, 'Deleting Global Arg: '||l_arg_name);
629 DELETE FROM fnd_oam_dscram_args_b
630 WHERE parent_type = FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_GLOBAL
631 AND arg_name = l_arg_name
632 RETURNING arg_id INTO l_arg_id;
633 DELETE FROM fnd_oam_dscram_args_tl
634 WHERE arg_id = l_arg_id;
635 DELETE FROM fnd_oam_dscram_arg_values
636 WHERE arg_id = l_arg_id;
637 k := p_global_arg_names.NEXT(k);
638 END LOOP;
639 RETURN TRUE;
640 EXCEPTION
641 WHEN OTHERS THEN
642 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
643 RETURN FALSE;
644 END;
645
646 --after a run is finished, validate that the dml entity completed as expected.
647 FUNCTION VALIDATE_DML_SUCCESS(p_run_id IN NUMBER,
648 p_dml_id IN NUMBER,
649 p_target_status IN VARCHAR2 DEFAULT FND_API.G_RET_STS_SUCCESS,
650 p_target_rows IN NUMBER DEFAULT -1)
651 RETURN BOOLEAN
652 IS
653 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_DML_SUCCESS';
654
655 l_status VARCHAR2(30);
656 l_rows_processed NUMBER;
657
658 k NUMBER;
659 l_retbool BOOLEAN;
660 BEGIN
661 --fnd_oam_debug.log(2, l_ctxt, 'ENTER');
662
663 --query out the run's final status
664 SELECT finished_ret_sts, rows_processed
665 INTO l_status, l_rows_processed
666 FROM fnd_oam_dscram_dmls
667 WHERE dml_id = p_dml_id;
668
669 --status check
670 IF l_status IS NULL OR l_status <> p_target_status THEN
671 fnd_oam_debug.log(6, l_ctxt, 'Status incorrect: Current('||l_status||'), Target('||p_target_status||')');
672 RETURN FALSE;
673 END IF;
674
675 --check that we processed all the rows
676 IF p_target_rows >= 0 AND (l_rows_processed IS NULL OR l_rows_processed <> p_target_rows) THEN
677 fnd_oam_debug.log(6, l_ctxt, 'DML ID('||p_dml_id||'), processed '||l_rows_processed||' rows, should have been '||p_target_rows);
678 RETURN FALSE;
679 END IF;
680
681 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
682 RETURN TRUE;
683 EXCEPTION
684 WHEN NO_DATA_FOUND THEN
685 fnd_oam_debug.log(6, l_ctxt, 'DML with DML ID ('||p_dml_id||') not found.');
686 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
687 RETURN FALSE;
688 WHEN OTHERS THEN
689 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
690 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
691 RETURN FALSE;
692 END;
693
694 --after a run is finished, validate that the dml entity completed as expected.
695 FUNCTION VALIDATE_PLSQL_SUCCESS(p_run_id IN NUMBER,
696 p_plsql_id IN NUMBER,
697 p_target_status IN VARCHAR2 DEFAULT FND_API.G_RET_STS_SUCCESS)
698 RETURN BOOLEAN
699 IS
700 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_PLSQL_SUCCESS';
701
702 l_status VARCHAR2(30);
703
704 k NUMBER;
705 l_retbool BOOLEAN;
706 BEGIN
707 --fnd_oam_debug.log(2, l_ctxt, 'ENTER');
708
709 --query out the run's final status
710 SELECT finished_ret_sts
711 INTO l_status
712 FROM fnd_oam_dscram_plsqls
713 WHERE plsql_id = p_plsql_id;
714
715 --status check
716 IF l_status IS NULL OR l_status <> p_target_status THEN
717 fnd_oam_debug.log(6, l_ctxt, 'Status incorrect: Current('||l_status||'), Target('||p_target_status||')');
718 RETURN FALSE;
719 END IF;
720
721 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
722 RETURN TRUE;
723 EXCEPTION
724 WHEN NO_DATA_FOUND THEN
725 fnd_oam_debug.log(6, l_ctxt, 'PLSQL with PLSQL ID ('||p_plsql_id||') not found.');
726 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
727 RETURN FALSE;
728 WHEN OTHERS THEN
729 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
730 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
731 RETURN FALSE;
732 END;
733
734 --after a run is finished, validate that the unit entity completed as expected.
735 FUNCTION VALIDATE_UNIT_SUCCESS(p_run_id IN NUMBER,
736 p_unit_id IN NUMBER,
737 p_target_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSED)
738 RETURN BOOLEAN
739 IS
740 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_UNIT_SUCCESS';
741
742 l_status VARCHAR2(30);
743 l_workers_assigned NUMBER;
744 k NUMBER;
745 l_retbool BOOLEAN;
746 BEGIN
747 --fnd_oam_debug.log(2, l_ctxt, 'ENTER');
748
749 --query out the run's final status
750 SELECT unit_status, workers_assigned
751 INTO l_status, l_workers_assigned
752 FROM fnd_oam_dscram_units
753 WHERE unit_id = p_unit_id;
754
755 --status check
756 IF (l_status IS NULL AND p_target_status IS NOT NULL) OR
757 (l_status IS NOT NULL AND p_target_status IS NULL) OR
758 l_status <> p_target_status THEN
759
760 fnd_oam_debug.log(6, l_ctxt, 'Status incorrect: Current('||l_status||'), Target('||p_target_status||')');
761 RETURN FALSE;
762 END IF;
763
764 --worker check
765 IF l_workers_assigned IS NULL OR l_workers_assigned > 0 THEN
766 fnd_oam_debug.log(6, l_ctxt, 'Still has workers: '||l_workers_assigned);
767 RETURN FALSE;
768 END IF;
769
770 --check the status row
771 --skip for now
772
773 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
774 RETURN TRUE;
775 EXCEPTION
776 WHEN NO_DATA_FOUND THEN
777 fnd_oam_debug.log(6, l_ctxt, 'Unit with Unit ID ('||p_unit_id||') not found.');
778 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
779 RETURN FALSE;
780 WHEN OTHERS THEN
781 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
782 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
783 RETURN FALSE;
784 END;
785
786 --after a run is finished, validate that the task entity completed as expected.
787 FUNCTION VALIDATE_TASK_SUCCESS(p_run_id IN NUMBER,
788 p_task_id IN NUMBER,
789 p_target_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSED)
790 RETURN BOOLEAN
791 IS
792 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_TASK_SUCCESS';
793
794 l_status VARCHAR2(30);
795 l_workers_assigned NUMBER;
796 k NUMBER;
797 l_retbool BOOLEAN;
798 BEGIN
799 --fnd_oam_debug.log(2, l_ctxt, 'ENTER');
800
801 --query out the run's final status
802 SELECT task_status, workers_assigned
803 INTO l_status, l_workers_assigned
804 FROM fnd_oam_dscram_tasks
805 WHERE task_id = p_task_id;
806
807 --status check
808 IF l_status IS NULL OR l_status <> p_target_status THEN
809 fnd_oam_debug.log(6, l_ctxt, 'Status incorrect: Current('||l_status||'), Target('||p_target_status||')');
810 RETURN FALSE;
811 END IF;
812
813 --worker check
814 IF l_workers_assigned IS NULL OR l_workers_assigned > 0 THEN
815 fnd_oam_debug.log(6, l_ctxt, 'Still has workers: '||l_workers_assigned);
816 RETURN FALSE;
817 END IF;
818
819 --check the status row
820 --skip for now
821
822 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
823 RETURN TRUE;
824 EXCEPTION
825 WHEN NO_DATA_FOUND THEN
826 fnd_oam_debug.log(6, l_ctxt, 'Task with Task ID ('||p_task_id||') not found.');
827 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
828 RETURN FALSE;
829 WHEN OTHERS THEN
830 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
831 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
832 RETURN FALSE;
833 END;
834
835 --after a run is finished, validate that the bundle entity completed as expected.
836 FUNCTION VALIDATE_BUNDLE_SUCCESS(p_run_id IN NUMBER,
837 p_bundle_id IN NUMBER,
838 p_target_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSED)
839 RETURN BOOLEAN
840 IS
841 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_BUNDLE_SUCCESS';
842
843 l_status VARCHAR2(30);
844 l_workers_assigned NUMBER;
845 k NUMBER;
846 l_retbool BOOLEAN;
847 BEGIN
848 --fnd_oam_debug.log(2, l_ctxt, 'ENTER');
849
850 --query out the run's final status
851 SELECT bundle_status, workers_assigned
852 INTO l_status, l_workers_assigned
853 FROM fnd_oam_dscram_bundles
854 WHERE run_id = p_run_id
855 AND bundle_id = p_bundle_id;
856
857 --status check
858 IF l_status IS NULL OR l_status <> p_target_status THEN
859 fnd_oam_debug.log(6, l_ctxt, 'Status incorrect: Current('||l_status||'), Target('||p_target_status||')');
860 RETURN FALSE;
861 END IF;
862
863 --worker check
864 IF l_workers_assigned IS NULL OR l_workers_assigned > 0 THEN
865 fnd_oam_debug.log(6, l_ctxt, 'Still has workers: '||l_workers_assigned);
866 RETURN FALSE;
867 END IF;
868
869 --check the status row
870 --skip for now
871
872 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
873 RETURN TRUE;
874 EXCEPTION
875 WHEN NO_DATA_FOUND THEN
876 fnd_oam_debug.log(6, l_ctxt, 'Bundle with Run ID ('||p_run_id||') and Bundle ID ('||p_bundle_id||') not found.');
877 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
878 RETURN FALSE;
879 WHEN OTHERS THEN
880 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
881 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
882 RETURN FALSE;
883 END;
884
885 --after a run is finished, validate that the run entity completed as expected. In this case
886 --we check for processing instead of processed since processed would have been set by the Java controller
887 FUNCTION VALIDATE_RUN_SUCCESS(p_run_id IN NUMBER,
888 p_target_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSING,
889 x_run_stat_id OUT NOCOPY NUMBER)
890 RETURN BOOLEAN
891 IS
892 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_RUN_SUCCESS';
893
894 l_status VARCHAR2(30);
895 l_run_stat_id NUMBER;
896 k NUMBER;
897 l_retbool BOOLEAN;
898 BEGIN
899 --fnd_oam_debug.log(2, l_ctxt, 'ENTER');
900
901 --query out the run's final status
902 SELECT run_status, last_run_stat_id
903 INTO l_status, l_run_stat_id
904 FROM fnd_oam_dscram_runs_vl
905 WHERE run_id = p_run_id;
906
907 --status check
908 IF l_status IS NULL OR l_status <> p_target_status THEN
909 fnd_oam_debug.log(6, l_ctxt, 'Status incorrect: Current('||l_status||'), Target('||p_target_status||')');
910 RETURN FALSE;
911 END IF;
912
913 --check the status row
914 --skip for now
915
916 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
917 x_run_stat_id := l_run_stat_id;
918 RETURN TRUE;
919 EXCEPTION
920 WHEN NO_DATA_FOUND THEN
921 fnd_oam_debug.log(6, l_ctxt, 'Run ID ('||p_run_id||') not found.');
922 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
923 RETURN FALSE;
924 WHEN OTHERS THEN
925 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
926 --fnd_oam_debug.log(2, l_ctxt, 'EXIT');
927 RETURN FALSE;
928 END;
929
930 -- Helper function to validate a single run, bundle, task.
931 -- Covers the simple test cases.
932 FUNCTION VALIDATE_UNIT_RECURSIVE(p_run_id IN NUMBER,
933 p_run_stat_id IN NUMBER DEFAULT NULL,
934 p_unit_id IN NUMBER,
935 p_num_workers IN NUMBER DEFAULT 1,
936 p_num_dmls IN NUMBER DEFAULT -1,
937 p_num_dml_rows IN NUMBER DEFAULT -1,
938 p_num_plsqls IN NUMBER DEFAULT -1,
939 p_unit_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSED,
940 p_dml_status IN VARCHAR2 DEFAULT FND_API.G_RET_STS_SUCCESS,
941 p_plsql_status IN VARCHAR2 DEFAULT FND_API.G_RET_STS_SUCCESS)
942 RETURN BOOLEAN
943 IS
944 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_UNIT_RECURSIVE';
945 l_run_stat_id NUMBER;
946 l_ids DBMS_SQL.NUMBER_TABLE;
947 l_id NUMBER;
948 k NUMBER;
949 l_count NUMBER;
950 BEGIN
951 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
952 fnd_oam_debug.log(1, l_ctxt, 'Unit ID: '||p_unit_id);
953
954 --first validate the bundle
955 IF NOT VALIDATE_UNIT_SUCCESS(p_run_id,
956 p_unit_id,
957 p_unit_status) THEN
958 RAISE VALIDATE_FAILED;
959 END IF;
960
961 --doesn't handle the case where this unit is a concurrent group unit
962
963 IF p_num_dmls >= 0 THEN
964 --now query child dmls
965 SELECT dml_id
966 BULK COLLECT INTO l_ids
967 FROM fnd_oam_dscram_dmls
968 WHERE unit_id = p_unit_id;
969
970 l_count := 0;
971 k := l_ids.FIRST;
972 WHILE k IS NOT NULL LOOP
973 fnd_oam_debug.log(1, l_ctxt, 'Dml ID: '||l_ids(k));
974 IF NOT VALIDATE_DML_SUCCESS(p_run_id,
975 l_ids(k),
976 p_dml_status,
977 p_num_dml_rows) THEN
978 RAISE VALIDATE_FAILED;
979 END IF;
980
981 k := l_ids.NEXT(k);
982 l_count := l_count + 1;
983 END LOOP;
984 IF (l_count IS NULL OR l_count <> p_num_dmls) THEN
985 fnd_oam_debug.log(6, l_ctxt, 'Number of dmls found ('||l_count||') did not match the number expected ('||p_num_dmls||')');
986 RAISE VALIDATE_FAILED;
987 END IF;
988 END IF;
989
990 IF p_num_plsqls >= 0 THEN
991 --now query child dmls
992 SELECT plsql_id
993 BULK COLLECT INTO l_ids
994 FROM fnd_oam_dscram_plsqls
995 WHERE unit_id = p_unit_id;
996
997 l_count := 0;
998 k := l_ids.FIRST;
999 WHILE k IS NOT NULL LOOP
1000 fnd_oam_debug.log(1, l_ctxt, 'Plsql ID: '||l_ids(k));
1001 IF NOT VALIDATE_PLSQL_SUCCESS(p_run_id,
1002 l_ids(k),
1003 p_plsql_status) THEN
1004 RAISE VALIDATE_FAILED;
1005 END IF;
1006
1007 k := l_ids.NEXT(k);
1008 l_count := l_count + 1;
1009 END LOOP;
1010 IF (l_count IS NULL OR l_count <> p_num_plsqls) THEN
1011 fnd_oam_debug.log(6, l_ctxt, 'Number of dmls found ('||l_count||') did not match the number expected ('||p_num_plsqls||')');
1012 RAISE VALIDATE_FAILED;
1013 END IF;
1014 END IF;
1015
1016 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1017 RETURN TRUE;
1018 EXCEPTION
1019 WHEN VALIDATE_FAILED THEN
1020 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1021 RETURN FALSE;
1022 WHEN OTHERS THEN
1023 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1024 RETURN FALSE;
1025 END;
1026
1027 -- Helper function to validate a single run, bundle, task.
1028 -- Covers the simple test cases.
1029 FUNCTION VALIDATE_TASK_RECURSIVE(p_run_id IN NUMBER,
1030 p_run_stat_id IN NUMBER DEFAULT NULL,
1031 p_task_id IN NUMBER,
1032 p_num_workers IN NUMBER DEFAULT 1,
1033 p_num_units IN NUMBER DEFAULT -1,
1034 p_num_dmls IN NUMBER DEFAULT -1,
1035 p_num_dml_rows IN NUMBER DEFAULT -1,
1036 p_num_plsqls IN NUMBER DEFAULT -1,
1037 p_task_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSED,
1038 p_unit_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSED,
1039 p_dml_status IN VARCHAR2 DEFAULT FND_API.G_RET_STS_SUCCESS,
1040 p_plsql_status IN VARCHAR2 DEFAULT FND_API.G_RET_STS_SUCCESS)
1041
1042 RETURN BOOLEAN
1043 IS
1044 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_TASK_RECURSIVE';
1045 l_run_stat_id NUMBER;
1046 l_ids DBMS_SQL.NUMBER_TABLE;
1047 l_id NUMBER;
1048 k NUMBER;
1049 l_count_units NUMBER;
1050 BEGIN
1051 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1052 fnd_oam_debug.log(1, l_ctxt, 'Task ID: '||p_task_id);
1053
1054 --first validate the bundle
1055 IF NOT VALIDATE_TASK_SUCCESS(p_run_id,
1056 p_task_id,
1057 p_task_status) THEN
1058 RAISE VALIDATE_FAILED;
1059 END IF;
1060
1061 IF p_num_units >= 0 THEN
1062 --now query the units and validate those
1063 SELECT unit_id
1064 BULK COLLECT INTO l_ids
1065 FROM fnd_oam_dscram_units
1066 WHERE task_id = p_task_id
1067 AND concurrent_group_unit_id IS NULL;
1068
1069 l_count_units := 0;
1070 k := l_ids.FIRST;
1071 WHILE k IS NOT NULL LOOP
1072 IF NOT VALIDATE_UNIT_RECURSIVE(p_run_id,
1073 l_run_stat_id,
1074 l_ids(k),
1075 p_num_workers,
1076 p_num_dmls,
1077 p_num_dml_rows,
1078 p_num_plsqls,
1079 p_unit_status,
1080 p_dml_status,
1081 p_plsql_status) THEN
1082 RAISE VALIDATE_FAILED;
1083 END IF;
1084
1085 k := l_ids.NEXT(k);
1086 l_count_units := l_count_units + 1;
1087 END LOOP;
1088 IF p_num_units >= 0 AND (l_count_units IS NULL OR l_count_units <> p_num_units) THEN
1089 fnd_oam_debug.log(6, l_ctxt, 'Task ID('||p_task_id||'), Number of units found ('||l_count_units||') did not match the number expected ('||p_num_units||')');
1090 RAISE VALIDATE_FAILED;
1091 END IF;
1092 END IF;
1093
1094 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1095 RETURN TRUE;
1096 EXCEPTION
1097 WHEN VALIDATE_FAILED THEN
1098 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1099 RETURN FALSE;
1100 WHEN OTHERS THEN
1101 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1102 RETURN FALSE;
1103 END;
1104
1105 -- Helper function to validate a single run, bundle, task.
1106 -- Covers the simple test cases.
1107 FUNCTION VALIDATE_BUNDLE_RECURSIVE(p_run_id IN NUMBER,
1108 p_run_stat_id IN NUMBER DEFAULT NULL,
1109 p_bundle_id IN NUMBER,
1110 p_num_workers IN NUMBER DEFAULT 1,
1111 p_num_tasks IN NUMBER DEFAULT -1,
1112 p_num_units IN NUMBER DEFAULT -1,
1113 p_num_dmls IN NUMBER DEFAULT -1,
1114 p_num_dml_rows IN NUMBER DEFAULT -1,
1115 p_num_plsqls IN NUMBER DEFAULT -1,
1116 p_bundle_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSED,
1117 p_task_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSED,
1118 p_unit_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSED,
1119 p_dml_status IN VARCHAR2 DEFAULT FND_API.G_RET_STS_SUCCESS,
1120 p_plsql_status IN VARCHAR2 DEFAULT FND_API.G_RET_STS_SUCCESS)
1121
1122 RETURN BOOLEAN
1123 IS
1124 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_BUNDLE_RECURSIVE';
1125 l_run_stat_id NUMBER;
1126 l_ids DBMS_SQL.NUMBER_TABLE;
1127 l_id NUMBER;
1128 k NUMBER;
1129 l_count_tasks NUMBER;
1130 BEGIN
1131 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1132 fnd_oam_debug.log(1, l_ctxt, 'Bundle ID: '||p_bundle_id);
1133
1134 --first validate the bundle
1135 IF NOT VALIDATE_BUNDLE_SUCCESS(p_run_id,
1136 p_bundle_id,
1137 p_bundle_status) THEN
1138 RAISE VALIDATE_FAILED;
1139 END IF;
1140
1141 IF p_num_tasks >= 0 THEN
1142 --now query the tasks and validate those
1143 SELECT task_id
1144 BULK COLLECT INTO l_ids
1145 FROM fnd_oam_dscram_tasks
1146 WHERE bundle_id = p_bundle_id;
1147
1148 l_count_tasks := 0;
1149 k := l_ids.FIRST;
1150 WHILE k IS NOT NULL LOOP
1151 IF NOT VALIDATE_TASK_RECURSIVE(p_run_id,
1152 l_run_stat_id,
1153 l_ids(k),
1154 p_num_workers,
1155 p_num_units,
1156 p_num_dmls,
1157 p_num_dml_rows,
1158 p_num_plsqls,
1159 p_task_status,
1160 p_unit_status,
1161 p_dml_status,
1162 p_plsql_status) THEN
1163 RAISE VALIDATE_FAILED;
1164 END IF;
1165
1166 k := l_ids.NEXT(k);
1167 l_count_tasks := l_count_tasks + 1;
1168 END LOOP;
1169 IF p_num_tasks >= 0 AND (l_count_tasks IS NULL OR l_count_tasks <> p_num_tasks) THEN
1170 fnd_oam_debug.log(6, l_ctxt, 'Bundle ID('||p_bundle_id||'), Number of tasks found ('||l_count_tasks||') did not match the number expected ('||p_num_tasks||')');
1171 RAISE VALIDATE_FAILED;
1172 END IF;
1173 END IF;
1174
1175 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1176 RETURN TRUE;
1177 EXCEPTION
1178 WHEN VALIDATE_FAILED THEN
1179 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1180 RETURN FALSE;
1181 WHEN OTHERS THEN
1182 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1183 RETURN FALSE;
1184 END;
1185
1186 -- Helper function to validate a single run, bundle, task.
1187 -- Covers the simple test cases.
1188 FUNCTION VALIDATE_RUN_RECURSIVE(p_run_id IN NUMBER,
1189 p_num_workers IN NUMBER DEFAULT 1,
1190 p_num_bundles IN NUMBER DEFAULT -1,
1191 p_num_tasks IN NUMBER DEFAULT -1,
1192 p_num_units IN NUMBER DEFAULT -1,
1193 p_num_dmls IN NUMBER DEFAULT -1,
1194 p_num_dml_rows IN NUMBER DEFAULT -1,
1195 p_num_plsqls IN NUMBER DEFAULT -1,
1196 p_run_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSING,
1197 p_bundle_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSED,
1198 p_task_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSED,
1199 p_unit_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSED,
1200 p_dml_status IN VARCHAR2 DEFAULT FND_API.G_RET_STS_SUCCESS,
1201 p_plsql_status IN VARCHAR2 DEFAULT FND_API.G_RET_STS_SUCCESS)
1202 RETURN BOOLEAN
1203 IS
1204 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_RUN_RECURSIVE';
1205 l_run_stat_id NUMBER;
1206 l_ids DBMS_SQL.NUMBER_TABLE;
1207 l_id NUMBER;
1208 k NUMBER;
1209 l_count_bundles NUMBER;
1210 BEGIN
1211 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1212 fnd_oam_debug.log(1, l_ctxt, 'Run ID: '||p_run_id);
1213
1214 --before doing any work, rollback the current transaction in case some state didn't get comitted.
1215 ROLLBACK;
1216
1217 --first validate the run
1218 IF NOT VALIDATE_RUN_SUCCESS(p_run_id,
1219 p_run_status,
1220 x_run_stat_id => l_run_stat_id) THEN
1221 RAISE VALIDATE_FAILED;
1222 END IF;
1223
1224 IF p_num_bundles >= 0 THEN
1225 --now query the bundles and validate those
1226 SELECT bundle_id
1227 BULK COLLECT INTO l_ids
1228 FROM fnd_oam_dscram_bundles
1229 WHERE run_id = p_run_id;
1230
1231 l_count_bundles := 0;
1232 k := l_ids.FIRST;
1233 WHILE k IS NOT NULL LOOP
1234 IF NOT VALIDATE_BUNDLE_RECURSIVE(p_run_id,
1235 l_run_stat_id,
1236 l_ids(k),
1237 p_num_workers,
1238 p_num_tasks,
1239 p_num_units,
1240 p_num_dmls,
1241 p_num_dml_rows,
1242 p_num_plsqls,
1243 p_bundle_status,
1244 p_task_status,
1245 p_unit_status,
1246 p_dml_status,
1247 p_plsql_status) THEN
1248 RAISE VALIDATE_FAILED;
1249 END IF;
1250
1251 k := l_ids.NEXT(k);
1252 l_count_bundles := l_count_bundles + 1;
1253 END LOOP;
1254 IF p_num_bundles >= 0 AND (l_count_bundles IS NULL OR l_count_bundles <> p_num_bundles) THEN
1255 fnd_oam_debug.log(6, l_ctxt, 'Run ID('||p_run_id||'), Number of bundles found ('||l_count_bundles||') did not match the number expected ('||p_num_bundles||')');
1256 RAISE VALIDATE_FAILED;
1257 END IF;
1258 END IF;
1259
1260 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1261 RETURN TRUE;
1262 EXCEPTION
1263 WHEN VALIDATE_FAILED THEN
1264 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1265 RETURN FALSE;
1266 WHEN OTHERS THEN
1267 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1268 RETURN FALSE;
1269 END;
1270
1271 -- create an argument entry, typically attached to a run or dml
1272 FUNCTION MAKE_ARG(p_arg_name IN VARCHAR2,
1273 p_parent_type IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_RUN,
1274 p_parent_id IN NUMBER DEFAULT NULL,
1275 p_init_success_flag IN VARCHAR2 DEFAULT NULL,
1276 p_allow_override_source IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1277 p_binding_enabled_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1278 p_permissions IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ,
1279 p_write_policy IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
1280 p_datatype IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
1281 p_valid_value_flag IN VARCHAR2 DEFAULT NULL,
1282 p_canon_value IN VARCHAR2 DEFAULT NULL,
1283 p_src_type IN VARCHAR2 DEFAULT NULL,
1284 p_src_text IN VARCHAR2 DEFAULT NULL,
1285 p_src_where_clause IN VARCHAR2 DEFAULT NULL,
1286 x_arg_id OUT NOCOPY NUMBER)
1287 RETURN BOOLEAN
1288 IS
1289 l_ctxt VARCHAR2(60) := PKG_NAME||'MAKE_ARG';
1290 l_retval NUMBER;
1291 BEGIN
1292 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1293
1294 INSERT INTO FND_OAM_DSCRAM_ARGS_B (ARG_ID,
1295 ARG_NAME,
1296 PARENT_TYPE,
1297 PARENT_ID,
1298 ENABLED_FLAG,
1299 INITIALIZED_SUCCESS_FLAG,
1300 ALLOW_OVERRIDE_SOURCE_FLAG,
1301 BINDING_ENABLED_FLAG,
1302 PERMISSIONS,
1303 WRITE_POLICY,
1304 DATATYPE,
1305 VALID_VALUE_FLAG,
1306 CANONICAL_VALUE,
1307 SOURCE_TYPE,
1308 SOURCE_TEXT,
1309 SOURCE_WHERE_CLAUSE,
1310 CREATED_BY,
1311 CREATION_DATE,
1312 LAST_UPDATED_BY,
1313 LAST_UPDATE_DATE,
1314 LAST_UPDATE_LOGIN)
1315 VALUES
1316 (fnd_oam_dscram_args_s.nextval,
1317 p_arg_name,
1318 p_parent_type,
1319 p_parent_id,
1320 FND_API.G_TRUE,
1321 p_init_success_flag,
1322 p_allow_override_source,
1323 p_binding_enabled_flag,
1324 p_permissions,
1325 p_write_policy,
1326 p_datatype,
1327 p_valid_value_flag,
1328 p_canon_value,
1329 p_src_type,
1330 p_src_text,
1331 p_src_where_clause,
1332 FND_GLOBAL.USER_ID,
1333 SYSDATE,
1334 FND_GLOBAL.USER_ID,
1335 SYSDATE,
1336 FND_GLOBAL.USER_ID)
1337 RETURNING ARG_ID INTO l_retval;
1338
1339 INSERT INTO FND_OAM_DSCRAM_ARGS_TL (ARG_ID,
1340 DISPLAY_NAME,
1341 DESCRIPTION,
1342 LANGUAGE,
1343 SOURCE_LANG,
1344 CREATED_BY,
1345 CREATION_DATE,
1346 LAST_UPDATED_BY,
1347 LAST_UPDATE_DATE,
1348 LAST_UPDATE_LOGIN
1349 )
1350 VALUES
1351 (l_retval,
1352 'ARG'||l_retval,
1353 'DESC'||l_retval,
1354 FND_GLOBAL.CURRENT_LANGUAGE,
1355 FND_GLOBAL.CURRENT_LANGUAGE,
1356 FND_GLOBAL.USER_ID,
1357 SYSDATE,
1358 FND_GLOBAL.USER_ID,
1359 SYSDATE,
1360 FND_GLOBAL.USER_ID);
1361
1362 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1363 x_arg_id := l_retval;
1364 RETURN TRUE;
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1368 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1369 x_arg_id := NULL;
1370 RETURN FALSE;
1371 END;
1372
1373 -- create a dml entry for a given unit
1374 FUNCTION MAKE_DML(p_unit_id IN NUMBER,
1375 p_priority IN NUMBER DEFAULT NULL,
1376 p_weight IN NUMBER DEFAULT NULL,
1377 p_dml_stmt IN VARCHAR2,
1378 p_where_clause IN VARCHAR2 DEFAULT NULL,
1379 x_dml_id OUT NOCOPY NUMBER)
1380 RETURN BOOLEAN
1381 IS
1382 l_ctxt VARCHAR2(60) := PKG_NAME||'MAKE_DML';
1383 l_retval NUMBER;
1384 BEGIN
1385 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1386
1387 INSERT INTO FND_OAM_DSCRAM_DMLS (DML_ID,
1388 UNIT_ID,
1389 PRIORITY,
1390 WEIGHT,
1391 DML_STMT,
1392 DML_WHERE_CLAUSE,
1393 CREATED_BY,
1394 CREATION_DATE,
1395 LAST_UPDATED_BY,
1396 LAST_UPDATE_DATE,
1397 LAST_UPDATE_LOGIN
1398 )
1399 VALUES
1400 (fnd_oam_dscram_dmls_s.nextval,
1401 p_unit_id,
1402 p_priority,
1403 p_weight,
1404 p_dml_stmt,
1405 p_where_clause,
1406 FND_GLOBAL.USER_ID,
1407 SYSDATE,
1408 FND_GLOBAL.USER_ID,
1409 SYSDATE,
1410 FND_GLOBAL.USER_ID)
1411 RETURNING DML_ID INTO l_retval;
1412
1413 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1414 x_dml_id := l_retval;
1415 RETURN TRUE;
1416 EXCEPTION
1417 WHEN OTHERS THEN
1418 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1419 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1420 x_dml_id := NULL;
1421 RETURN FALSE;
1422 END;
1423
1424 -- create a plsql entry for a given unit
1425 FUNCTION MAKE_PLSQL(p_unit_id IN NUMBER,
1426 p_priority IN NUMBER DEFAULT NULL,
1427 p_weight IN NUMBER DEFAULT NULL,
1428 p_plsql_text IN VARCHAR2,
1429 x_plsql_id OUT NOCOPY NUMBER)
1430 RETURN BOOLEAN
1431 IS
1432 l_ctxt VARCHAR2(60) := PKG_NAME||'MAKE_PLSQL';
1433 l_retval NUMBER;
1434 BEGIN
1435 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1436
1437 INSERT INTO FND_OAM_DSCRAM_PLSQLS (PLSQL_ID,
1438 UNIT_ID,
1439 PRIORITY,
1440 WEIGHT,
1441 PLSQL_TEXT,
1442 CREATED_BY,
1443 CREATION_DATE,
1444 LAST_UPDATED_BY,
1445 LAST_UPDATE_DATE,
1446 LAST_UPDATE_LOGIN
1447 )
1448 VALUES
1449 (fnd_oam_dscram_plsqls_s.nextval,
1450 p_unit_id,
1451 p_priority,
1452 p_weight,
1453 p_plsql_text,
1454 FND_GLOBAL.USER_ID,
1455 SYSDATE,
1456 FND_GLOBAL.USER_ID,
1457 SYSDATE,
1458 FND_GLOBAL.USER_ID)
1459 RETURNING PLSQL_ID INTO l_retval;
1460
1461 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1462 x_plsql_id := l_retval;
1463 RETURN TRUE;
1464 EXCEPTION
1465 WHEN OTHERS THEN
1466 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1467 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1468 x_plsql_id := NULL;
1469 RETURN FALSE;
1470 END;
1471
1472 -- create a task entry for a given bundle
1473 FUNCTION MAKE_UNIT(p_task_id IN NUMBER,
1474 p_conc_unit_id IN NUMBER DEFAULT NULL,
1475 p_unit_type IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_UNIT_TYPE_DML_SET,
1476 p_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_UNPROCESSED,
1477 p_phase IN NUMBER DEFAULT NULL,
1478 p_priority IN NUMBER DEFAULT NULL,
1479 p_weight IN NUMBER DEFAULT NULL,
1480 p_sug_workers_allowed IN NUMBER DEFAULT NULL,
1481 p_act_workers_allowed IN NUMBER DEFAULT NULL,
1482 p_unit_obj_owner IN VARCHAR2 DEFAULT NULL,
1483 p_unit_obj_name IN VARCHAR2 DEFAULT NULL,
1484 p_batch_size IN NUMBER DEFAULT NULL,
1485 p_fatality_level IN VARCHAR2 DEFAULT NULL,
1486 p_sug_disable_splitting IN VARCHAR2 DEFAULT NULL,
1487 p_act_disable_splitting IN VARCHAR2 DEFAULT NULL,
1488 x_unit_id OUT NOCOPY NUMBER)
1489 RETURN BOOLEAN
1490 IS
1491 l_ctxt VARCHAR2(60) := PKG_NAME||'MAKE_UNIT';
1492 l_retval NUMBER;
1493 BEGIN
1494 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1495
1496 INSERT INTO FND_OAM_DSCRAM_UNITS (UNIT_ID,
1497 TASK_ID,
1498 CONCURRENT_GROUP_UNIT_ID,
1499 UNIT_TYPE,
1500 UNIT_STATUS,
1501 PHASE,
1502 PRIORITY,
1503 WEIGHT,
1504 SUGGEST_WORKERS_ALLOWED,
1505 ACTUAL_WORKERS_ALLOWED,
1506 WORKERS_ASSIGNED,
1507 UNIT_OBJECT_OWNER,
1508 UNIT_OBJECT_NAME,
1509 BATCH_SIZE,
1510 ERROR_FATALITY_LEVEL,
1511 SUGGEST_DISABLE_SPLITTING,
1512 ACTUAL_DISABLE_SPLITTING,
1513 CREATED_BY,
1514 CREATION_DATE,
1515 LAST_UPDATED_BY,
1516 LAST_UPDATE_DATE,
1517 LAST_UPDATE_LOGIN
1518 )
1519 VALUES
1520 (fnd_oam_dscram_units_s.nextval,
1521 p_task_id,
1522 p_conc_unit_id,
1523 p_unit_type,
1524 p_status,
1525 p_phase,
1526 p_priority,
1527 p_weight,
1528 p_sug_workers_allowed,
1529 p_act_workers_allowed,
1530 0,
1531 p_unit_obj_owner,
1532 p_unit_obj_name,
1533 p_batch_size,
1534 p_fatality_level,
1535 p_sug_disable_splitting,
1536 p_act_disable_splitting,
1537 FND_GLOBAL.USER_ID,
1538 SYSDATE,
1539 FND_GLOBAL.USER_ID,
1540 SYSDATE,
1541 FND_GLOBAL.USER_ID)
1542 RETURNING UNIT_ID INTO l_retval;
1543
1544 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1545 x_unit_id := l_retval;
1546 RETURN TRUE;
1547 EXCEPTION
1548 WHEN OTHERS THEN
1549 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1550 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1551 x_unit_id := NULL;
1552 RETURN FALSE;
1553 END;
1554
1555 -- create a task entry for a given bundle
1556 FUNCTION MAKE_TASK(p_bundle_id IN NUMBER,
1557 p_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_UNPROCESSED,
1558 p_priority IN NUMBER DEFAULT NULL,
1559 p_weight IN NUMBER DEFAULT NULL,
1560 p_dom_owner IN VARCHAR2 DEFAULT NULL,
1561 p_dom_name IN VARCHAR2 DEFAULT NULL,
1562 x_task_id OUT NOCOPY NUMBER)
1563 RETURN BOOLEAN
1564 IS
1565 l_ctxt VARCHAR2(60) := PKG_NAME||'MAKE_TASK';
1566 l_retval NUMBER;
1567 BEGIN
1568 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1569
1570 INSERT INTO FND_OAM_DSCRAM_TASKS (TASK_ID,
1571 BUNDLE_ID,
1572 TASK_STATUS,
1573 PRIORITY,
1574 WEIGHT,
1575 WORKERS_ASSIGNED,
1576 CREATED_BY,
1577 CREATION_DATE,
1578 LAST_UPDATED_BY,
1579 LAST_UPDATE_DATE,
1580 LAST_UPDATE_LOGIN
1581 )
1582 VALUES
1583 (fnd_oam_dscram_tasks_s.nextval,
1584 p_bundle_id,
1585 p_status,
1586 p_priority,
1587 p_weight,
1588 0,
1589 FND_GLOBAL.USER_ID,
1590 SYSDATE,
1591 FND_GLOBAL.USER_ID,
1592 SYSDATE,
1593 FND_GLOBAL.USER_ID)
1594 RETURNING TASK_ID INTO l_retval;
1595
1596 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1597 x_task_id := l_retval;
1598 RETURN TRUE;
1599 EXCEPTION
1600 WHEN OTHERS THEN
1601 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1602 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1603 x_task_id := NULL;
1604 RETURN FALSE;
1605 END;
1606
1607 -- create a bundle entry for a given run
1608 FUNCTION MAKE_BUNDLE(p_run_id IN NUMBER,
1609 p_bundle_id IN NUMBER,
1610 p_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_UNPROCESSED,
1611 p_weight IN NUMBER DEFAULT NULL,
1612 p_workers_allowed IN NUMBER DEFAULT 1,
1613 p_batch_size IN NUMBER DEFAULT 1000,
1614 p_min_par_weight IN NUMBER DEFAULT NULL)
1615 RETURN BOOLEAN
1616 IS
1617 l_ctxt VARCHAR2(60) := PKG_NAME||'MAKE_BUNDLE';
1618 l_batch_size NUMBER := p_batch_size;
1619 BEGIN
1620 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1621
1622 --if batch size was given as null, default it
1623 IF p_batch_size IS NULL THEN
1624 l_batch_size := 1000;
1625 END IF;
1626
1627 INSERT INTO FND_OAM_DSCRAM_BUNDLES (BUNDLE_ID,
1628 RUN_ID,
1629 BUNDLE_STATUS,
1630 WEIGHT,
1631 WORKERS_ALLOWED,
1632 WORKERS_ASSIGNED,
1633 BATCH_SIZE,
1634 MIN_PARALLEL_UNIT_WEIGHT,
1635 CREATED_BY,
1636 CREATION_DATE,
1637 LAST_UPDATED_BY,
1638 LAST_UPDATE_DATE,
1639 LAST_UPDATE_LOGIN
1640 )
1641 VALUES
1642 (p_bundle_id,
1643 p_run_id,
1644 p_status,
1645 p_weight,
1646 p_workers_allowed,
1647 0,
1648 l_batch_size,
1649 p_min_par_weight,
1650 FND_GLOBAL.USER_ID,
1651 SYSDATE,
1652 FND_GLOBAL.USER_ID,
1653 SYSDATE,
1654 FND_GLOBAL.USER_ID);
1655
1656 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1657 RETURN TRUE;
1658 EXCEPTION
1659 WHEN OTHERS THEN
1660 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1661 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1662 RETURN FALSE;
1663 END;
1664
1665 --creates a RUN
1666 FUNCTION MAKE_RUN(p_run_id IN NUMBER,
1667 p_mode IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_MODE_DIAGNOSTIC,
1668 p_status IN VARCHAR2 DEFAULT FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSING,
1669 p_weight IN NUMBER DEFAULT NULL,
1670 p_check_interval IN NUMBER DEFAULT 300,
1671 p_name IN VARCHAR2 DEFAULT NULL,
1672 p_desc IN VARCHAR2 DEFAULT NULL)
1673 RETURN BOOLEAN
1674 IS
1675 l_ctxt VARCHAR2(60) := PKG_NAME||'MAKE_RUN';
1676 l_dbname VARCHAR2(30);
1677 l_run_stat_id NUMBER;
1678 l_run_name VARCHAR2(30) := p_name;
1679 l_return_status VARCHAR2(6);
1680 l_return_msg VARCHAR2(2048);
1681 BEGIN
1682 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1683
1684 --default the run name if not provided
1685 IF p_name IS NULL THEN
1686 l_run_name := 'Diagnostic Test '||to_char(SYSDATE, 'MI_SS');
1687 END IF;
1688
1689 --query the current db
1690 SELECT name
1691 INTO l_dbname
1692 FROM v$database
1693 WHERE rownum < 2;
1694
1695 INSERT INTO FND_OAM_DSCRAM_RUNS_B (RUN_ID,
1696 RUN_STATUS,
1697 RUN_MODE,
1698 TARGET_DBNAME,
1699 WEIGHT,
1700 VALID_CHECK_INTERVAL,
1701 CREATED_BY,
1702 CREATION_DATE,
1703 LAST_UPDATED_BY,
1704 LAST_UPDATE_DATE,
1705 LAST_UPDATE_LOGIN
1706 )
1707 VALUES
1708 (p_run_id,
1709 p_status,
1710 p_mode,
1711 l_dbname,
1712 p_weight,
1713 p_check_interval,
1714 FND_GLOBAL.USER_ID,
1715 SYSDATE,
1716 FND_GLOBAL.USER_ID,
1717 SYSDATE,
1718 FND_GLOBAL.USER_ID);
1719
1720 INSERT INTO FND_OAM_DSCRAM_RUNS_TL (RUN_ID,
1721 DISPLAY_NAME,
1722 DESCRIPTION,
1723 LANGUAGE,
1724 SOURCE_LANG,
1725 CREATED_BY,
1726 CREATION_DATE,
1727 LAST_UPDATED_BY,
1728 LAST_UPDATE_DATE,
1729 LAST_UPDATE_LOGIN
1730 )
1731 VALUES
1732 (p_run_id,
1733 l_run_name,
1734 p_desc,
1735 FND_GLOBAL.CURRENT_LANGUAGE,
1736 FND_GLOBAL.CURRENT_LANGUAGE,
1737 FND_GLOBAL.USER_ID,
1738 SYSDATE,
1739 FND_GLOBAL.USER_ID,
1740 SYSDATE,
1741 FND_GLOBAL.USER_ID);
1742
1743 --also create the run stats entry
1744 FND_OAM_DSCRAM_STATS_PKG.CREATE_ENTRY_FOR_RUN(p_run_id => p_run_id,
1745 p_start_time => SYSDATE,
1746 p_prestart_status => 'UNPROCESSED',
1747 x_run_stat_id => l_run_stat_id,
1748 x_return_status => l_return_status,
1749 x_return_msg => l_return_msg);
1750 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1751 fnd_oam_debug.log(6, l_ctxt, 'Failed to create run stat entry: '||l_return_msg);
1752 RETURN FALSE;
1753 END IF;
1754
1755 --update the run with this run stat id
1756 UPDATE fnd_oam_dscram_runs_b
1757 SET last_run_stat_id = l_run_stat_id
1758 WHERE run_id = p_run_id;
1759
1760 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1761 RETURN TRUE;
1762 EXCEPTION
1763 WHEN OTHERS THEN
1764 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1765 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1766 RETURN FALSE;
1767 END;
1768
1769 -- Helper init function to make a single run, bundle, task.
1770 -- Covers the simple test cases.
1771 FUNCTION INIT_SINGLE_TASK(p_run_id IN NUMBER,
1772 p_bundle_id IN NUMBER,
1773 p_testnum IN VARCHAR2,
1774 p_num_workers IN NUMBER DEFAULT 1,
1775 p_batch_size IN NUMBER DEFAULT NULL,
1776 x_task_id OUT NOCOPY NUMBER)
1777 RETURN BOOLEAN
1778 IS
1779 l_ctxt VARCHAR2(60) := PKG_NAME||'INIT_SINGLE_TASK';
1780 l_task_id NUMBER;
1781 BEGIN
1782 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1783
1784 --do the meat of creating a run, bundle, and task
1785 IF NOT MAKE_RUN(p_run_id,
1786 p_name => B_DIAG_RUN_NAME_PREFIX||p_testnum) THEN
1787 RAISE INIT_FAILED;
1788 END IF;
1789
1790 IF NOT MAKE_BUNDLE(p_run_id,
1791 p_bundle_id,
1792 p_workers_allowed => p_num_workers,
1793 p_batch_size => p_batch_size) THEN
1794 RAISE INIT_FAILED;
1795 END IF;
1796
1797 IF NOT MAKE_TASK(p_bundle_id,
1798 x_task_id => l_task_id) THEN
1799 RAISE INIT_FAILED;
1800 END IF;
1801
1802 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1803 x_task_id := l_task_id;
1804 RETURN TRUE;
1805 EXCEPTION
1806 WHEN INIT_FAILED THEN
1807 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1808 x_task_id := NULL;
1809 RETURN TRUE;
1810 WHEN OTHERS THEN
1811 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1812 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1813 x_task_id := NULL;
1814 RETURN FALSE;
1815 END;
1816
1817 -- Helper init function to make a single run, bundle, task.
1818 -- Covers the simple test cases.
1819 FUNCTION INIT_SINGLE_DML_UNIT(p_run_id IN NUMBER,
1820 p_bundle_id IN NUMBER,
1821 p_testnum IN VARCHAR2,
1822 p_num_workers IN NUMBER DEFAULT 1,
1823 p_batch_size IN NUMBER DEFAULT 1000,
1824 x_task_id OUT NOCOPY NUMBER,
1825 x_unit_id OUT NOCOPY NUMBER)
1826 RETURN BOOLEAN
1827 IS
1828 l_ctxt VARCHAR2(60) := PKG_NAME||'INIT_SINGLE_DML_UNIT';
1829 l_task_id NUMBER;
1830 l_unit_id NUMBER;
1831 BEGIN
1832 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1833
1834 --make the run,bundle,task
1835 IF NOT INIT_SINGLE_TASK(p_run_id,
1836 p_bundle_id,
1837 p_testnum,
1838 p_num_workers,
1839 p_batch_size,
1840 l_task_id) THEN
1841 RAISE INIT_FAILED;
1842 END IF;
1843
1844 --make the unit
1845 IF NOT MAKE_UNIT(l_task_id,
1846 x_unit_id => l_unit_id) THEN
1847 RAISE INIT_FAILED;
1848 END IF;
1849
1850 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1851 x_task_id := l_task_id;
1852 x_unit_id := l_unit_id;
1853 RETURN TRUE;
1854 EXCEPTION
1855 WHEN INIT_FAILED THEN
1856 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1857 x_task_id := NULL;
1858 x_unit_id := NULL;
1859 RETURN TRUE;
1860 WHEN OTHERS THEN
1861 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1862 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1863 x_task_id := NULL;
1864 x_unit_id := NULL;
1865 RETURN FALSE;
1866 END;
1867
1868 --helper to the EXECUTE_TEST# functions to detect when a run is finished, replaced by the SYNC_ON_FINISH
1869 --which uses DBMS_PIPE based IPC to detect when the run is finished. This function is kept in case
1870 --we need a more manual check.
1871 FUNCTION RUN_IS_FINISHED(p_run_id IN NUMBER)
1872 RETURN BOOLEAN
1873 IS
1874 l_ctxt VARCHAR2(60) := PKG_NAME||'RUN_IS_FINISHED';
1875 l_temp NUMBER;
1876 BEGIN
1877 --Can't use the status of the run so see if any bundles exist in a non-finished status or have
1878 --workers
1879 SELECT 1
1880 INTO l_temp
1881 FROM DUAL
1882 WHERE EXISTS (SELECT 1
1883 FROM fnd_oam_dscram_bundles
1884 WHERE run_id = p_run_id
1885 AND (workers_assigned > 0 OR bundle_status NOT IN (FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSED,
1886 FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_STOPPED,
1887 FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_SKIPPED,
1888 FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_ERROR_FATAL,
1889 FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_ERROR_UNKNOWN)));
1890 RETURN FALSE;
1891 EXCEPTION
1892 WHEN NO_DATA_FOUND THEN
1893 RETURN TRUE;
1894 WHEN OTHERS THEN
1895 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1896 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1897 RETURN FALSE;
1898 END;
1899
1900 --called by EXECUTE_TEST to sync the workers at the end of each run so that
1901 --things like cleanup can be called.
1902 --throws SYNC_FAILED if the timeout is hit
1903 FUNCTION SYNC_ON_FINISH(p_run_id IN NUMBER,
1904 p_test_success IN BOOLEAN DEFAULT FALSE,
1905 p_num_workers IN NUMBER DEFAULT 1,
1906 p_did_init IN BOOLEAN DEFAULT FALSE)
1907 RETURN BOOLEAN
1908 IS
1909 l_ctxt VARCHAR2(60) := PKG_NAME||'SYNC_ON_FINISH';
1910 l_retval NUMBER;
1911 l_msg VARCHAR2(30);
1912 l_pipename VARCHAR2(30) := MAKE_RUN_PIPE_NAME(p_run_id);
1913 l_ack_pipename VARCHAR2(30);
1914 l_msg_count NUMBER;
1915 l_target_msg_count NUMBER;
1916 l_retbool BOOLEAN;
1917 BEGIN
1918 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1919
1920 l_retbool := TRUE;
1921 --different behavior depending on whether we're the initializing worker or not
1922 IF p_did_init THEN
1923 --we need to recieve done messages from the other n-1 messages
1924 l_msg_count := 0;
1925 l_target_msg_count := p_num_workers - 1;
1926 WHILE l_msg_count < l_target_msg_count LOOP
1927 l_retval := DBMS_PIPE.RECEIVE_MESSAGE(l_pipename,
1928 B_MAX_WAIT);
1929 IF l_retval <> 0 THEN
1930 fnd_oam_debug.log(6, l_ctxt, 'Failed to receive message: '||l_retval);
1931 RAISE SYNC_FAILED;
1932 END IF;
1933
1934 --get the message, update the overall status if a worker failed
1935 DBMS_PIPE.UNPACK_MESSAGE(l_msg);
1936 IF l_msg <> FND_API.G_TRUE THEN
1937 l_retbool := FALSE;
1938 END IF;
1939
1940 --increment the message received count
1941 l_msg_count := l_msg_count + 1;
1942 END LOOP;
1943
1944 --all other workers have finished, return our status
1945 l_retbool := l_retbool AND p_test_success;
1946 ELSE
1947 --if we didn't do the init, send our status on the pipe
1948 IF p_test_success THEN
1949 DBMS_PIPE.PACK_MESSAGE(FND_API.G_TRUE);
1950 ELSE
1951 DBMS_PIPE.PACK_MESSAGE(FND_API.G_FALSE);
1952 END IF;
1953 l_retval := DBMS_PIPE.SEND_MESSAGE(l_pipename,
1954 B_MAX_WAIT);
1955 IF l_retval <> 0 THEN
1956 fnd_oam_debug.log(6, l_ctxt, 'Failed to send message: '||l_retval);
1957 RAISE SYNC_FAILED;
1958 END IF;
1959
1960 --now block waiting for an ack with the final status of the test
1961 l_ack_pipename := MAKE_RUN_ACK_PIPE_NAME(p_run_id);
1962 l_retval := DBMS_PIPE.RECEIVE_MESSAGE(l_ack_pipename,
1963 B_MAX_WAIT);
1964 IF l_retval <> 0 THEN
1965 fnd_oam_debug.log(6, l_ctxt, 'Failed to receive ACK: '||l_retval);
1966 RAISE SYNC_FAILED;
1967 END IF;
1968 DBMS_PIPE.UNPACK_MESSAGE(l_msg);
1969 l_retbool := (l_msg = FND_API.G_TRUE);
1970 END IF;
1971
1972 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1973 RETURN l_retbool;
1974 EXCEPTION
1975 WHEN SYNC_FAILED THEN
1976 RAISE SYNC_FAILED;
1977 WHEN OTHERS THEN
1978 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1979 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1980 RETURN FALSE;
1981 END;
1982
1983 --used by the initializing worker to send acks to the other workers with the final status
1984 PROCEDURE SEND_ACKS(p_run_id IN NUMBER,
1985 p_final_test_success IN BOOLEAN,
1986 p_num_workers IN NUMBER)
1987 IS
1988 l_ctxt VARCHAR2(60) := PKG_NAME||'SEND_ACKS';
1989 l_retval NUMBER;
1990 l_msg VARCHAR2(30);
1991 l_ack_pipename VARCHAR2(30) := MAKE_RUN_ACK_PIPE_NAME(p_run_id);
1992 k NUMBER := 0;
1993 l_num_msgs NUMBER := p_num_workers - 1;
1994 BEGIN
1995 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1996
1997 --prep the ack message
1998 IF p_final_test_success THEN
1999 l_msg := FND_API.G_TRUE;
2000 ELSE
2001 l_msg := FND_API.G_FALSE;
2002 END IF;
2003
2004 --send the messages
2005 WHILE k < l_num_msgs LOOP
2006 DBMS_PIPE.PACK_MESSAGE(l_msg);
2007 l_retval := DBMS_PIPE.SEND_MESSAGE(l_ack_pipename,
2008 B_MAX_WAIT);
2009 IF l_retval <> 0 THEN
2010 fnd_oam_debug.log(6, l_ctxt, 'Failed to send ack message: '||l_retval);
2011 RAISE SYNC_FAILED;
2012 END IF;
2013 k := k + 1;
2014 END LOOP;
2015
2016 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2017 EXCEPTION
2018 WHEN SYNC_FAILED THEN
2019 RAISE SYNC_FAILED;
2020 WHEN OTHERS THEN
2021 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
2022 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2023 END;
2024
2025 --Helper to EXECUTE_TEST# functions to see if the run's been initialized yet, if it hasn't then it
2026 --keeps the lock and returns to EXECUTE_ to do the init and release the lock.
2027 FUNCTION RUN_NEEDS_INIT(p_run_id IN NUMBER,
2028 x_lock_handle OUT NOCOPY VARCHAR2)
2029 RETURN BOOLEAN
2030 IS
2031 l_ctxt VARCHAR2(60) := PKG_NAME||'RUN_NEEDS_INIT';
2032 l_lock_handle VARCHAR2(128);
2033 l_retval NUMBER;
2034 BEGIN
2035 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
2036 x_lock_handle := NULL;
2037
2038 --lock the run
2039 IF NOT FND_OAM_DSCRAM_UTILS_PKG.LOCK_RUN(p_run_id,
2040 l_lock_handle) THEN
2041 fnd_oam_debug.log(1, l_ctxt, 'Failed to lock run.');
2042 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2043 RETURN FALSE;
2044 END IF;
2045
2046 --after getting the lock, see if the run's there
2047 BEGIN
2048 SELECT 1
2049 INTO l_retval
2050 FROM fnd_oam_dscram_runs_b
2051 WHERE run_id = p_run_id;
2052
2053 --select suceeded, release and return
2054 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
2055 IF l_retval <> 0 THEN
2056 fnd_oam_debug.log(6, l_ctxt, 'Failed to release run lock: '||l_retval);
2057 END IF;
2058 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2059 RETURN FALSE;
2060 EXCEPTION
2061 WHEN NO_DATA_FOUND THEN
2062 x_lock_handle := l_lock_handle;
2063 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2064 RETURN TRUE;
2065 END;
2066
2067 --shouldn't get here
2068 fnd_oam_debug.log(1, l_ctxt, 'Bad spot?');
2069 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
2070 IF l_retval <> 0 THEN
2071 fnd_oam_debug.log(6, l_ctxt, 'Failed to release run lock: '||l_retval);
2072 END IF;
2073 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2074 RETURN FALSE;
2075 EXCEPTION
2076 WHEN OTHERS THEN
2077 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
2078 --try to release the lock
2079 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
2080 IF l_retval <> 0 THEN
2081 fnd_oam_debug.log(6, l_ctxt, 'Failed to release run lock: '||l_retval);
2082 END IF;
2083 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2084 RETURN FALSE;
2085 END;
2086
2087 --helper to the EXECUTE_TEST# functions
2088 FUNCTION EXECUTE_BUNDLE_WRAPPER(p_ctxt IN VARCHAR2,
2089 p_run_id IN NUMBER,
2090 p_bundle_id IN NUMBER,
2091 px_worker_id IN OUT NOCOPY NUMBER,
2092 x_return_status OUT NOCOPY VARCHAR2,
2093 x_return_msg OUT NOCOPY VARCHAR2)
2094 RETURN BOOLEAN
2095 IS
2096 l_start NUMBER;
2097 l_end NUMBER;
2098 BEGIN
2099 l_start := DBMS_UTILITY.GET_TIME;
2100 FND_OAM_DSCRAM_BUNDLES_PKG.EXECUTE_BUNDLE(p_run_id => p_run_id,
2101 p_bundle_id => p_bundle_id,
2102 px_worker_id => px_worker_id,
2103 x_return_status => x_return_status,
2104 x_return_msg => x_return_msg);
2105 l_end := DBMS_UTILITY.GET_TIME;
2106 fnd_oam_debug.log(1, p_ctxt, 'Execute Done - Duration: '||(l_end - l_start)/100|| ' seconds.');
2107 fnd_oam_debug.log(1, p_ctxt, 'Return Status: '||x_return_status);
2108 fnd_oam_debug.log(1, p_ctxt, 'Return Msg: "'||x_return_msg||'"');
2109
2110 --figure out if we should return a success or failure
2111 --allowable outputs, SUCCESS/PROCESSED
2112 RETURN x_return_status IN (FND_API.G_RET_STS_SUCCESS,
2113 FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_PROCESSED);
2114 END;
2115
2116 --helper to EXECUTE_TEST* to print some state on entry
2117 PROCEDURE PRINT_TEST_ENTRY_STATE(p_ctxt IN VARCHAR2,
2118 p_run_id IN NUMBER,
2119 p_bundle_id IN NUMBER,
2120 p_num_bundles IN NUMBER,
2121 p_num_workers IN NUMBER)
2122 IS
2123 BEGIN
2124 fnd_oam_debug.log(1, p_ctxt, 'Run ID: '||p_run_id);
2125 fnd_oam_debug.log(1, p_ctxt, 'Bundle ID: '||p_bundle_id);
2126 fnd_oam_debug.log(1, p_ctxt, 'Bundles: '||p_num_bundles);
2127 fnd_oam_debug.log(1, p_ctxt, 'Workers: '||p_num_workers);
2128 END;
2129
2130 --Public
2131 PROCEDURE EXECUTE_TEST1(p_run_id IN NUMBER DEFAULT 1,
2132 p_bundle_id IN NUMBER DEFAULT 1,
2133 p_num_bundles IN NUMBER DEFAULT 1,
2134 p_num_workers IN NUMBER DEFAULT 1,
2135 x_verdict OUT NOCOPY VARCHAR2)
2136 IS
2137 l_testnum VARCHAR2(20) := 'TEST1';
2138 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_'||l_testnum;
2139
2140 l_did_init BOOLEAN := FALSE;
2141 l_lock_handle VARCHAR2(128);
2142 l_retval NUMBER;
2143 l_retbool BOOLEAN;
2144 l_retbool_final BOOLEAN;
2145
2146 l_worker_id NUMBER;
2147 l_task_id NUMBER;
2148 l_return_status VARCHAR2(6);
2149 l_return_msg VARCHAR2(2048);
2150 l_start NUMBER;
2151 l_end NUMBER;
2152 BEGIN
2153 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
2154 PRINT_TEST_ENTRY_STATE(l_ctxt, p_run_id, p_bundle_id, p_num_bundles, p_num_workers);
2155
2156 ROLLBACK;
2157 -- make sure the run is initialized
2158 IF RUN_NEEDS_INIT(p_run_id,
2159 l_lock_handle) THEN
2160
2161 fnd_oam_debug.log(1, l_ctxt, 'Initializing Test...');
2162 l_start := DBMS_UTILITY.GET_TIME;
2163
2164 IF NOT INIT_SINGLE_TASK(p_run_id,
2165 p_bundle_id,
2166 l_testnum,
2167 p_num_workers,
2168 x_task_id => l_task_id) THEN
2169 RAISE INIT_FAILED;
2170 END IF;
2171
2172 COMMIT;
2173 l_end := DBMS_UTILITY.GET_TIME;
2174 fnd_oam_debug.log(1, l_ctxt, 'Init Done - Duration: '||(l_end - l_start)/100|| ' seconds.');
2175 l_did_init := TRUE;
2176 END IF;
2177
2178 --do work
2179 l_retbool := EXECUTE_BUNDLE_WRAPPER(l_ctxt,
2180 p_run_id,
2181 p_bundle_id,
2182 l_worker_id,
2183 l_return_status,
2184 l_return_msg);
2185
2186 --sync the finish
2187 IF l_did_init THEN
2188 l_retbool_final := SYNC_ON_FINISH(p_run_id,
2189 l_retbool,
2190 p_num_workers,
2191 l_did_init);
2192 --validate the results
2193 l_retbool_final := l_retbool_final AND
2194 VALIDATE_RUN_RECURSIVE(p_run_id,
2195 p_num_workers,
2196 p_num_bundles => 1,
2197 p_num_tasks => 1,
2198 p_num_units => 0);
2199 --send acks to the other workers with the final status
2200 SEND_ACKS(p_run_id,
2201 l_retbool_final,
2202 p_num_workers);
2203 ELSE
2204 --send a msg with our status and wait for a msg with the final status
2205 l_retbool_final := SYNC_ON_FINISH(p_run_id,
2206 l_retbool);
2207 END IF;
2208
2209 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2210 IF l_retbool_final THEN
2211 x_verdict := FND_API.G_TRUE;
2212 ELSE
2213 x_verdict := FND_API.G_FALSE;
2214 END IF;
2215 EXCEPTION
2216 WHEN INIT_FAILED THEN
2217 --release the run lock on failure just in case
2218 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
2219 IF l_retval <> 0 THEN
2220 fnd_oam_debug.log(6, l_ctxt, 'Failed to release run lock: '||l_retval);
2221 END IF;
2222 ROLLBACK;
2223 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2224 x_verdict := FND_API.G_FALSE;
2225 WHEN SYNC_FAILED THEN
2226 fnd_oam_debug.log(6, l_ctxt, 'Sync Failed');
2227 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2228 x_verdict := FND_API.G_FALSE;
2229 WHEN OTHERS THEN
2230 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
2231 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2232 x_verdict := FND_API.G_FALSE;
2233 END;
2234
2235 --Public
2236 PROCEDURE EXECUTE_TEST2(p_run_id IN NUMBER DEFAULT 1,
2237 p_bundle_id IN NUMBER DEFAULT 1,
2238 p_num_bundles IN NUMBER DEFAULT 1,
2239 p_num_workers IN NUMBER DEFAULT 1,
2240 x_verdict OUT NOCOPY VARCHAR2)
2241 IS
2242 l_testnum VARCHAR2(20) := 'TEST2';
2243 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_'||l_testnum;
2244
2245 l_worker_id NUMBER;
2246 l_task_id NUMBER;
2247 l_unit_id NUMBER;
2248
2249 l_did_init BOOLEAN := FALSE;
2250 l_lock_handle VARCHAR2(128);
2251 l_retval NUMBER;
2252 l_retbool BOOLEAN;
2253 l_retbool_final BOOLEAN;
2254 l_return_status VARCHAR2(6);
2255 l_return_msg VARCHAR2(2048);
2256 l_start NUMBER;
2257 l_end NUMBER;
2258 BEGIN
2259 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
2260 PRINT_TEST_ENTRY_STATE(l_ctxt, p_run_id, p_bundle_id, p_num_bundles, p_num_workers);
2261
2262 ROLLBACK;
2263 IF RUN_NEEDS_INIT(p_run_id,
2264 l_lock_handle) THEN
2265
2266 fnd_oam_debug.log(1, l_ctxt, 'Initializing Test...');
2267 l_start := DBMS_UTILITY.GET_TIME;
2268
2269 --create a single run/bundle/task
2270 IF NOT INIT_SINGLE_DML_UNIT(p_run_id,
2271 p_bundle_id,
2272 l_testnum,
2273 p_num_workers,
2274 x_task_id => l_task_id,
2275 x_unit_id => l_unit_id) THEN
2276 RAISE INIT_FAILED;
2277 END IF;
2278
2279 COMMIT;
2280 l_end := DBMS_UTILITY.GET_TIME;
2281 fnd_oam_debug.log(1, l_ctxt, 'Init Done - Duration: '||(l_end - l_start)/100|| ' seconds.');
2282 l_did_init := TRUE;
2283 END IF;
2284
2285 --do work
2286 l_retbool := EXECUTE_BUNDLE_WRAPPER(l_ctxt,
2287 p_run_id,
2288 p_bundle_id,
2289 l_worker_id,
2290 l_return_status,
2291 l_return_msg);
2292
2293 --sync the finish
2294 IF l_did_init THEN
2295 l_retbool_final := SYNC_ON_FINISH(p_run_id,
2296 l_retbool,
2297 p_num_workers,
2298 l_did_init);
2299 --validate the results
2300 l_retbool_final := l_retbool_final AND
2301 VALIDATE_RUN_RECURSIVE(p_run_id,
2302 p_num_workers,
2303 p_num_bundles => 1,
2304 p_num_tasks => 1,
2305 p_num_units => 1);
2306 --send acks to the other workers with the final status
2307 SEND_ACKS(p_run_id,
2308 l_retbool_final,
2309 p_num_workers);
2310 ELSE
2311 --send a msg with our status and wait for a msg with the final status
2312 l_retbool_final := SYNC_ON_FINISH(p_run_id,
2313 l_retbool);
2314 END IF;
2315
2316 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2317 IF l_retbool_final THEN
2318 x_verdict := FND_API.G_TRUE;
2319 ELSE
2320 x_verdict := FND_API.G_FALSE;
2321 END IF;
2322 EXCEPTION
2323 WHEN INIT_FAILED THEN
2324 --release the run lock on failure just in case
2325 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
2326 IF l_retval <> 0 THEN
2327 fnd_oam_debug.log(6, l_ctxt, 'Failed to release run lock: '||l_retval);
2328 END IF;
2329 ROLLBACK;
2330 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2331 x_verdict := FND_API.G_FALSE;
2332 WHEN SYNC_FAILED THEN
2333 fnd_oam_debug.log(6, l_ctxt, 'Sync Failed');
2334 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2335 x_verdict := FND_API.G_FALSE;
2336 WHEN OTHERS THEN
2337 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
2338 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2339 x_verdict := FND_API.G_FALSE;
2340 END;
2341
2342 --Helper to execute_test3 to remove any state that will negatively impact future tests
2343 FUNCTION CLEANUP_TEST3(p_run_id IN NUMBER,
2344 p_bundle_id IN NUMBER,
2345 p_global_arg_names IN DBMS_SQL.VARCHAR2_TABLE)
2346 RETURN BOOLEAN
2347 IS
2348 l_ctxt VARCHAR2(60) := PKG_NAME||'CLEANUP_TEST3';
2349 l_retbool BOOLEAN;
2350 BEGIN
2351 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
2352 fnd_oam_debug.log(1, l_ctxt, 'Run ID: '||p_run_id);
2353 fnd_oam_debug.log(1, l_ctxt, 'Bundle ID: '||p_bundle_id);
2354
2355 l_retbool := DELETE_GLOBAL_ARGS(p_global_arg_names);
2356
2357 COMMIT;
2358
2359 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2360 RETURN l_retbool;
2361 EXCEPTION
2362 WHEN OTHERS THEN
2363 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
2364 RETURN FALSE;
2365 END;
2366
2367 --Public
2368 PROCEDURE EXECUTE_TEST3(p_run_id IN NUMBER DEFAULT 1,
2369 p_bundle_id IN NUMBER DEFAULT 1,
2370 p_num_bundles IN NUMBER DEFAULT 1,
2371 p_num_workers IN NUMBER DEFAULT 1,
2372 x_verdict OUT NOCOPY VARCHAR2)
2373 IS
2374 l_testnum VARCHAR2(20) := 'TEST3';
2375 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_'||l_testnum;
2376
2377 l_worker_id NUMBER;
2378 l_task_id NUMBER;
2379 l_unit_id NUMBER;
2380 l_arg_name VARCHAR2(60);
2381 l_global_arg_names DBMS_SQL.VARCHAR2_TABLE;
2382
2383 l_did_init BOOLEAN := FALSE;
2384 l_lock_handle VARCHAR2(128);
2385 l_retval NUMBER;
2386 l_retbool BOOLEAN;
2387 l_retbool_final BOOLEAN;
2388 l_return_status VARCHAR2(6);
2389 l_return_msg VARCHAR2(2048);
2390 l_start NUMBER;
2391 l_end NUMBER;
2392 BEGIN
2393 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
2394 PRINT_TEST_ENTRY_STATE(l_ctxt, p_run_id, p_bundle_id, p_num_bundles, p_num_workers);
2395
2396 ROLLBACK;
2397 IF RUN_NEEDS_INIT(p_run_id,
2398 l_lock_handle) THEN
2399
2400 fnd_oam_debug.log(1, l_ctxt, 'Initializing Test...');
2401 l_start := DBMS_UTILITY.GET_TIME;
2402
2403 --create a single run/bundle/task
2404 IF NOT INIT_SINGLE_TASK(p_run_id,
2405 p_bundle_id,
2406 l_testnum,
2407 p_num_workers,
2408 x_task_id => l_task_id) THEN
2409 RAISE INIT_FAILED;
2410 END IF;
2411
2412 --create a global arg
2413 l_arg_name := FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_GLOBAL_ARG1';
2414 IF NOT MAKE_ARG(p_arg_name => l_arg_name,
2415 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_GLOBAL,
2416 p_init_success_flag => FND_API.G_TRUE,
2417 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
2418 p_valid_value_flag => FND_API.G_TRUE,
2419 p_canon_value => 'Value1',
2420 x_arg_id => l_retval) THEN
2421 RAISE INIT_FAILED;
2422 END IF;
2423 l_global_arg_names(1) := l_arg_name;
2424
2425 --create a run arg
2426 IF NOT MAKE_ARG(p_arg_name => FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_RUN_ARG1',
2427 p_parent_id => p_run_id,
2428 p_init_success_flag => FND_API.G_TRUE,
2429 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
2430 p_valid_value_flag => FND_API.G_TRUE,
2431 p_canon_value => '123.45',
2432 x_arg_id => l_retval) THEN
2433 RAISE INIT_FAILED;
2434 END IF;
2435
2436 --create a dynamic run arg, sourcing a constant
2437 IF NOT MAKE_ARG(p_arg_name => FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_RUN_DYNARG1',
2438 p_parent_id => p_run_id,
2439 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
2440 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_CONSTANT,
2441 p_src_text => '3.141592653',
2442 x_arg_id => l_retval) THEN
2443 RAISE INIT_FAILED;
2444 END IF;
2445
2446 --create a dynamic run arg, sourcing a constant
2447 IF NOT MAKE_ARG(p_arg_name => FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_RUN_DYNARG1.5',
2448 p_parent_id => p_run_id,
2449 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
2450 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_DATE,
2451 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_CONSTANT,
2452 p_src_text => '2005/08/30 11:26:45',
2453 x_arg_id => l_retval) THEN
2454 RAISE INIT_FAILED;
2455 END IF;
2456
2457 --create a dynamic run arg, sourcing the date as a date
2458 IF NOT MAKE_ARG(p_arg_name => FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_RUN_DYNARG2',
2459 p_parent_id => p_run_id,
2460 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_DATE,
2461 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL,
2462 p_src_text => 'SELECT SYSDATE FROM DUAL',
2463 x_arg_id => l_retval) THEN
2464 RAISE INIT_FAILED;
2465 END IF;
2466
2467 --create a dynamic run arg, sourcing the timestamp as a string, no write so each call gets a different value
2468 IF NOT MAKE_ARG(p_arg_name => FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_RUN_DYNARG3',
2469 p_parent_id => p_run_id,
2470 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
2471 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL,
2472 p_src_text => 'SELECT to_char(SYSTIMESTAMP, ''HH24:MI:SS.FF'') FROM DUAL',
2473 x_arg_id => l_retval) THEN
2474 RAISE INIT_FAILED;
2475 END IF;
2476
2477 --create a dynamic run arg, sourcing the timestamp as a string, writeable so each worker should get the same value
2478 IF NOT MAKE_ARG(p_arg_name => FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_RUN_DYNARG4',
2479 p_parent_id => p_run_id,
2480 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
2481 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
2482 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
2483 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL,
2484 p_src_text => 'SELECT to_char(SYSTIMESTAMP, ''HH24:MI:SS.FF'') FROM DUAL',
2485 x_arg_id => l_retval) THEN
2486 RAISE INIT_FAILED;
2487 END IF;
2488
2489 --create a dynamic run arg, sourcing the timestamp but writing one per worker
2490 IF NOT MAKE_ARG(p_arg_name => FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_RUN_DYNARG5',
2491 p_parent_id => p_run_id,
2492 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
2493 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
2494 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_WORKER,
2495 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL,
2496 p_src_text => 'SELECT to_char(SYSTIMESTAMP, ''HH24:MI:SS.FF'') FROM DUAL',
2497 x_arg_id => l_retval) THEN
2498 RAISE INIT_FAILED;
2499 END IF;
2500
2501 --create a dynamic run arg, sourcing the timestamp and writing always
2502 IF NOT MAKE_ARG(p_arg_name => FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_RUN_DYNARG6',
2503 p_parent_id => p_run_id,
2504 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
2505 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
2506 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ALWAYS,
2507 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL,
2508 p_src_text => 'SELECT to_char(SYSTIMESTAMP, ''HH24:MI:SS.FF'') FROM DUAL',
2509 x_arg_id => l_retval) THEN
2510 RAISE INIT_FAILED;
2511 END IF;
2512
2513 --create a dynamic run arg, sourcing the timestamp and writing per range
2514 IF NOT MAKE_ARG(p_arg_name => FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_RUN_DYNARG7',
2515 p_parent_id => p_run_id,
2516 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
2517 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
2518 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
2519 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL_RESTRICTABLE,
2520 p_src_text => 'SELECT to_char(SYSTIMESTAMP, ''HH24:MI:SS.FF'') FROM DUAL',
2521 x_arg_id => l_retval) THEN
2522 RAISE INIT_FAILED;
2523 END IF;
2524
2525 --create a dynamic run arg, sourcing the rowid from dual, no write
2526 IF NOT MAKE_ARG(p_arg_name => FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_RUN_DYNARG8',
2527 p_parent_id => p_run_id,
2528 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID,
2529 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ,
2530 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL_RESTRICTABLE,
2531 p_src_text => 'SELECT ROWID FROM DUAL',
2532 x_arg_id => l_retval) THEN
2533 RAISE INIT_FAILED;
2534 END IF;
2535
2536 --create a dynamic run arg, sourcing the runid from state
2537 IF NOT MAKE_ARG(p_arg_name => FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_RUN_DYNARG9',
2538 p_parent_id => p_run_id,
2539 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
2540 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ,
2541 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
2542 p_src_text => 'RUN_ID',
2543 x_arg_id => l_retval) THEN
2544 RAISE INIT_FAILED;
2545 END IF;
2546
2547 --create a dynamic run arg, sourcing the runid from state
2548 IF NOT MAKE_ARG(p_arg_name => FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_RUN_DYNARG10',
2549 p_parent_id => p_run_id,
2550 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
2551 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
2552 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_WORKER,
2553 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
2554 p_src_text => 'WORKER_ID',
2555 x_arg_id => l_retval) THEN
2556 RAISE INIT_FAILED;
2557 END IF;
2558
2559 COMMIT;
2560 l_end := DBMS_UTILITY.GET_TIME;
2561 fnd_oam_debug.log(1, l_ctxt, 'Init Done - Duration: '||(l_end - l_start)/100|| ' seconds.');
2562 l_did_init := TRUE;
2563 END IF;
2564
2565 --do work
2566 l_retbool := EXECUTE_BUNDLE_WRAPPER(l_ctxt,
2567 p_run_id,
2568 p_bundle_id,
2569 l_worker_id,
2570 l_return_status,
2571 l_return_msg);
2572
2573 --make the guy who did init manage the sync, do cleanup and verify
2574 IF l_did_init THEN
2575 l_retbool_final := SYNC_ON_FINISH(p_run_id,
2576 l_retbool,
2577 p_num_workers,
2578 l_did_init);
2579 --validate the results
2580 l_retbool_final := l_retbool_final AND
2581 VALIDATE_RUN_RECURSIVE(p_run_id,
2582 p_num_workers,
2583 p_num_bundles => 1,
2584 p_num_tasks => 1,
2585 p_num_units => 0);
2586
2587 --cleanup the test's side effects
2588 l_retbool_final := CLEANUP_TEST3(p_run_id,
2589 p_bundle_id,
2590 l_global_arg_names) AND l_retbool_final;
2591
2592 --send acks to the other workers with the final status
2593 SEND_ACKS(p_run_id,
2594 l_retbool_final,
2595 p_num_workers);
2596 ELSE
2597 l_retbool_final := SYNC_ON_FINISH(p_run_id,
2598 l_retbool);
2599 END IF;
2600
2601 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2602 IF l_retbool_final THEN
2603 x_verdict := FND_API.G_TRUE;
2604 ELSE
2605 x_verdict := FND_API.G_FALSE;
2606 END IF;
2607 EXCEPTION
2608 WHEN INIT_FAILED THEN
2609 --release the run lock on failure just in case
2610 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
2611 IF l_retval <> 0 THEN
2612 fnd_oam_debug.log(6, l_ctxt, 'Failed to release run lock: '||l_retval);
2613 END IF;
2614 ROLLBACK;
2615 l_retbool := CLEANUP_TEST3(p_run_id,
2616 p_bundle_id,
2617 l_global_arg_names);
2618 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2619 x_verdict := FND_API.G_FALSE;
2620 WHEN SYNC_FAILED THEN
2621 l_retbool := CLEANUP_TEST3(p_run_id,
2622 p_bundle_id,
2623 l_global_arg_names);
2624 fnd_oam_debug.log(6, l_ctxt, 'Sync Failed');
2625 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2626 x_verdict := FND_API.G_FALSE;
2627 WHEN OTHERS THEN
2628 l_retbool := CLEANUP_TEST3(p_run_id,
2629 p_bundle_id,
2630 l_global_arg_names);
2631 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
2632 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2633 x_verdict := FND_API.G_FALSE;
2634 END;
2635
2636 --helper to EXECUTE_TEST4 to validate the test succeeded
2637 FUNCTION VALIDATE_TEST4(p_run_id IN NUMBER,
2638 p_bundle_id IN NUMBER,
2639 p_num_workers IN NUMBER)
2640 RETURN BOOLEAN
2641 IS
2642 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_TEST4';
2643
2644 l_run_stat_id NUMBER;
2645 l_task_id NUMBER;
2646 l_unit_id NUMBER;
2647
2648 l_retbool BOOLEAN;
2649 l_return_status VARCHAR2(6);
2650 l_return_msg VARCHAR2(2048);
2651 k NUMBER;
2652 j NUMBER;
2653 l_count NUMBER;
2654 l_ids DBMS_SQL.NUMBER_TABLE;
2655 BEGIN
2656 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
2657
2658 --first validate the run
2659 IF NOT VALIDATE_RUN_SUCCESS(p_run_id,
2660 x_run_stat_id => l_run_stat_id) THEN
2661 RAISE VALIDATE_FAILED;
2662 END IF;
2663
2664 --now the bundle
2665 IF NOT VALIDATE_BUNDLE_SUCCESS(p_run_id,
2666 p_bundle_id) THEN
2667 RAISE VALIDATE_FAILED;
2668 END IF;
2669
2670 --now query out the task ids to validate each
2671 SELECT task_id
2672 BULK COLLECT INTO l_ids
2673 FROM fnd_oam_dscram_tasks
2674 WHERE bundle_id = p_bundle_id
2675 ORDER BY task_id ASC;
2676
2677 l_count := 0;
2678 k := l_ids.FIRST;
2679 WHILE k IS NOT NULL LOOP
2680 IF NOT VALIDATE_TASK_RECURSIVE(p_run_id,
2681 p_task_id => l_ids(k),
2682 p_num_workers => p_num_workers,
2683 p_num_units => l_count) THEN
2684 RAISE VALIDATE_FAILED;
2685 END IF;
2686
2687 k := l_ids.NEXT(k);
2688 l_count := l_count + 1;
2689 END LOOP;
2690 IF l_count IS NULL OR l_count <> 5 THEN
2691 fnd_oam_debug.log(6, l_ctxt, 'Number of tasks found ('||l_count||') did not match the number expected (5)');
2692 RAISE VALIDATE_FAILED;
2693 END IF;
2694
2695 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2696 RETURN TRUE;
2697 EXCEPTION
2698 WHEN VALIDATE_FAILED THEN
2699 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2700 RETURN FALSE;
2701 WHEN OTHERS THEN
2702 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
2703 RETURN FALSE;
2704 END;
2705
2706 --Public
2707 PROCEDURE EXECUTE_TEST4(p_run_id IN NUMBER DEFAULT 1,
2708 p_bundle_id IN NUMBER DEFAULT 1,
2709 p_num_bundles IN NUMBER DEFAULT 1,
2710 p_num_workers IN NUMBER DEFAULT 1,
2711 x_verdict OUT NOCOPY VARCHAR2)
2712 IS
2713 l_testnum VARCHAR2(20) := 'TEST4';
2714 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_'||l_testnum;
2715
2716 l_worker_id NUMBER;
2717 l_task_id NUMBER;
2718 l_unit_id NUMBER;
2719
2720 l_did_init BOOLEAN := FALSE;
2721 l_lock_handle VARCHAR2(128);
2722 l_retval NUMBER;
2723 l_retbool BOOLEAN;
2724 l_retbool_final BOOLEAN;
2725 l_return_status VARCHAR2(6);
2726 l_return_msg VARCHAR2(2048);
2727 l_start NUMBER;
2728 l_end NUMBER;
2729 k NUMBER;
2730 j NUMBER;
2731 BEGIN
2732 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
2733 PRINT_TEST_ENTRY_STATE(l_ctxt, p_run_id, p_bundle_id, p_num_bundles, p_num_workers);
2734
2735 ROLLBACK;
2736 IF RUN_NEEDS_INIT(p_run_id,
2737 l_lock_handle) THEN
2738
2739 fnd_oam_debug.log(1, l_ctxt, 'Initializing Test...');
2740 l_start := DBMS_UTILITY.GET_TIME;
2741
2742 --create a single run/bundle
2743 IF NOT MAKE_RUN(p_run_id,
2744 p_name => B_DIAG_RUN_NAME_PREFIX||l_testnum) THEN
2745 RAISE INIT_FAILED;
2746 END IF;
2747 IF NOT MAKE_BUNDLE(p_run_id,
2748 p_bundle_id,
2749 p_workers_allowed => p_num_workers) THEN
2750 RAISE INIT_FAILED;
2751 END IF;
2752
2753 --create 5 tasks, each with a number of tasks equivalent to the value of the iterator
2754 k := 0;
2755 WHILE k < 5 LOOP
2756 --create task
2757 IF NOT MAKE_TASK(p_bundle_id,
2758 x_task_id => l_task_id) THEN
2759 RAISE INIT_FAILED;
2760 END IF;
2761
2762 --create k units
2763 j := 0;
2764 WHILE j < k LOOP
2765 --make unit
2766 IF NOT MAKE_UNIT(l_task_id,
2767 p_sug_workers_allowed => (MOD(j, 2) + 1),
2768 x_unit_id => l_unit_id) THEN
2769 RAISE INIT_FAILED;
2770 END IF;
2771 j := j + 1;
2772 END LOOP;
2773
2774 k := k + 1;
2775 END LOOP;
2776
2777 COMMIT;
2778 l_end := DBMS_UTILITY.GET_TIME;
2779 fnd_oam_debug.log(1, l_ctxt, 'Init Done - Duration: '||(l_end - l_start)/100|| ' seconds.');
2780 l_did_init := TRUE;
2781 END IF;
2782
2783 --do work
2784 l_retbool := EXECUTE_BUNDLE_WRAPPER(l_ctxt,
2785 p_run_id,
2786 p_bundle_id,
2787 l_worker_id,
2788 l_return_status,
2789 l_return_msg);
2790
2791 --make the guy who did init manage the sync, do cleanup and verify
2792 IF l_did_init THEN
2793 l_retbool_final := SYNC_ON_FINISH(p_run_id,
2794 l_retbool,
2795 p_num_workers,
2796 l_did_init);
2797 --validate the results
2798 l_retbool_final := l_retbool_final AND
2799 VALIDATE_TEST4(p_run_id,
2800 p_bundle_id,
2801 p_num_workers);
2802
2803 --send acks to the other workers with the final status
2804 SEND_ACKS(p_run_id,
2805 l_retbool_final,
2806 p_num_workers);
2807 ELSE
2808 l_retbool_final := SYNC_ON_FINISH(p_run_id,
2809 l_retbool);
2810 END IF;
2811
2812 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2813 IF l_retbool_final THEN
2814 x_verdict := FND_API.G_TRUE;
2815 ELSE
2816 x_verdict := FND_API.G_FALSE;
2817 END IF;
2818 EXCEPTION
2819 WHEN INIT_FAILED THEN
2820 --release the run lock on failure just in case
2821 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
2822 IF l_retval <> 0 THEN
2823 fnd_oam_debug.log(6, l_ctxt, 'Failed to release run lock: '||l_retval);
2824 END IF;
2825 ROLLBACK;
2826 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2827 x_verdict := FND_API.G_FALSE;
2828 WHEN SYNC_FAILED THEN
2829 fnd_oam_debug.log(6, l_ctxt, 'Sync Failed');
2830 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2831 x_verdict := FND_API.G_FALSE;
2832 WHEN OTHERS THEN
2833 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
2834 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2835 x_verdict := FND_API.G_FALSE;
2836 END;
2837
2838 --helper to EXECUTE_TEST5 to validate the test succeeded
2839 FUNCTION VALIDATE_TEST5(p_run_id IN NUMBER,
2840 p_bundle_id IN NUMBER,
2841 p_num_workers IN NUMBER,
2842 p_dml_id IN NUMBER,
2843 p_test_tab_name IN VARCHAR2,
2844 p_test_tab_num_rows IN NUMBER,
2845 p_c2_arg_id IN NUMBER)
2846 RETURN BOOLEAN
2847 IS
2848 l_testnum VARCHAR2(20) := 'TEST5';
2849 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_'||l_testnum;
2850
2851 l_c2_target_sum NUMBER := p_test_tab_num_rows*2;
2852 BEGIN
2853 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
2854
2855 --first validate the run/bundle/task/unit
2856 IF NOT VALIDATE_RUN_RECURSIVE(p_run_id,
2857 p_num_workers,
2858 p_num_bundles => 1,
2859 p_num_tasks => 1,
2860 p_num_units => 1,
2861 p_num_dmls => 1,
2862 p_num_dml_rows => p_test_tab_num_rows) THEN
2863 RAISE VALIDATE_FAILED;
2864 END IF;
2865
2866 --check that the test table has the correct number of rows
2867 IF NOT VALIDATE_TEST_TABLE_UNCHANGED(p_test_tab_name,
2868 p_test_tab_num_rows) THEN
2869 RAISE VALIDATE_FAILED;
2870 END IF;
2871
2872 --check that the c2 sum stored in the arg is correct
2873 IF NOT VALIDATE_TEST_TABLE_ARG_VALUES(FALSE,
2874 p_c2_arg_id,
2875 l_c2_target_sum,
2876 p_test_tab_name,
2877 p_test_tab_num_rows) THEN
2878 RAISE VALIDATE_FAILED;
2879 END IF;
2880
2881 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2882 RETURN TRUE;
2883 EXCEPTION
2884 WHEN VALIDATE_FAILED THEN
2885 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2886 RETURN FALSE;
2887 WHEN OTHERS THEN
2888 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
2889 RETURN FALSE;
2890 END;
2891
2892 --Public
2893 PROCEDURE EXECUTE_TEST5(p_run_id IN NUMBER DEFAULT 1,
2894 p_bundle_id IN NUMBER DEFAULT 1,
2895 p_num_bundles IN NUMBER DEFAULT 1,
2896 p_num_workers IN NUMBER DEFAULT 1,
2897 x_verdict OUT NOCOPY VARCHAR2)
2898 IS
2899 l_testnum VARCHAR2(20) := 'TEST5';
2900 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_'||l_testnum;
2901
2902 l_test_table_name VARCHAR2(30) := B_TEST_TABLE_NAME_PREFIX||'5_TAB1';
2903 l_test_table_num_rows NUMBER := 500;
2904 l_worker_id NUMBER;
2905 l_task_id NUMBER;
2906 l_unit_id NUMBER;
2907 l_dml_id NUMBER;
2908 l_arg_name VARCHAR2(60) := FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_C2_SUM';
2909 l_arg_id NUMBER;
2910
2911 l_did_init BOOLEAN := FALSE;
2912 l_lock_handle VARCHAR2(128);
2913 l_retval NUMBER;
2914 l_retbool BOOLEAN;
2915 l_retbool_final BOOLEAN;
2916 l_return_status VARCHAR2(6);
2917 l_return_msg VARCHAR2(2048);
2918 l_start NUMBER;
2919 l_end NUMBER;
2920 k NUMBER;
2921 j NUMBER;
2922 BEGIN
2923 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
2924 PRINT_TEST_ENTRY_STATE(l_ctxt, p_run_id, p_bundle_id, p_num_bundles, p_num_workers);
2925
2926 ROLLBACK;
2927 IF RUN_NEEDS_INIT(p_run_id,
2928 l_lock_handle) THEN
2929
2930 fnd_oam_debug.log(1, l_ctxt, 'Initializing Test...');
2931 l_start := DBMS_UTILITY.GET_TIME;
2932
2933 --create a test table to work on
2934 IF NOT MAKE_TEST_TABLE(l_test_table_name,
2935 l_test_table_num_rows) THEN
2936 RAISE INIT_FAILED;
2937 END IF;
2938
2939 --create a single run/bundle
2940 IF NOT MAKE_RUN(p_run_id,
2941 p_weight => 100,
2942 p_name => B_DIAG_RUN_NAME_PREFIX||l_testnum) THEN
2943 RAISE INIT_FAILED;
2944 END IF;
2945 --make the task execute serially by one worker
2946 IF NOT MAKE_BUNDLE(p_run_id,
2947 p_bundle_id,
2948 p_weight => 100,
2949 p_min_par_weight => 5000,
2950 p_workers_allowed => p_num_workers) THEN
2951 RAISE INIT_FAILED;
2952 END IF;
2953 --create task
2954 IF NOT MAKE_TASK(p_bundle_id,
2955 p_weight => 100,
2956 x_task_id => l_task_id) THEN
2957 RAISE INIT_FAILED;
2958 END IF;
2959 --make unit
2960 IF NOT MAKE_UNIT(l_task_id,
2961 p_weight => 100,
2962 x_unit_id => l_unit_id) THEN
2963 RAISE INIT_FAILED;
2964 END IF;
2965 --make a simple dml
2966 IF NOT MAKE_DML(l_unit_id,
2967 p_weight => 100,
2968 p_dml_stmt => 'UPDATE '||l_test_table_name||' SET C2 = C2 + 1',
2969 p_where_clause => NULL,
2970 x_dml_id => l_dml_id) THEN
2971 RAISE INIT_FAILED;
2972 END IF;
2973 --make an output arg for the dml to fetch C2's new sum
2974 IF NOT MAKE_ARG(p_arg_name => l_arg_name,
2975 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
2976 p_parent_id => l_dml_id,
2977 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
2978 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_WRITE,
2979 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
2980 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL,
2981 p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
2982 x_arg_id => l_arg_id) THEN
2983 RAISE INIT_FAILED;
2984 END IF;
2985
2986 COMMIT;
2987 l_end := DBMS_UTILITY.GET_TIME;
2988 fnd_oam_debug.log(1, l_ctxt, 'Init Done - Duration: '||(l_end - l_start)/100|| ' seconds.');
2989 l_did_init := TRUE;
2990 END IF;
2991
2992 --do work
2993 l_retbool := EXECUTE_BUNDLE_WRAPPER(l_ctxt,
2994 p_run_id,
2995 p_bundle_id,
2996 l_worker_id,
2997 l_return_status,
2998 l_return_msg);
2999
3000 --make the guy who did init manage the sync, do cleanup and verify
3001 IF l_did_init THEN
3002 l_retbool_final := SYNC_ON_FINISH(p_run_id,
3003 l_retbool,
3004 p_num_workers,
3005 l_did_init);
3006 --validate the results
3007 l_retbool_final := l_retbool_final AND
3008 VALIDATE_TEST5(p_run_id,
3009 p_bundle_id,
3010 p_num_workers,
3011 l_dml_id,
3012 l_test_table_name,
3013 l_test_table_num_rows,
3014 l_arg_id);
3015
3016 --send acks to the other workers with the final status
3017 SEND_ACKS(p_run_id,
3018 l_retbool_final,
3019 p_num_workers);
3020 ELSE
3021 l_retbool_final := SYNC_ON_FINISH(p_run_id,
3022 l_retbool);
3023 END IF;
3024
3025 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3026 IF l_retbool_final THEN
3027 x_verdict := FND_API.G_TRUE;
3028 ELSE
3029 x_verdict := FND_API.G_FALSE;
3030 END IF;
3031 EXCEPTION
3032 WHEN INIT_FAILED THEN
3033 --release the run lock on failure just in case
3034 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
3035 IF l_retval <> 0 THEN
3036 fnd_oam_debug.log(6, l_ctxt, 'Failed to release run lock: '||l_retval);
3037 END IF;
3038 ROLLBACK;
3039 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3040 x_verdict := FND_API.G_FALSE;
3041 WHEN SYNC_FAILED THEN
3042 fnd_oam_debug.log(6, l_ctxt, 'Sync Failed');
3043 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3044 x_verdict := FND_API.G_FALSE;
3045 WHEN OTHERS THEN
3046 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
3047 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3048 x_verdict := FND_API.G_FALSE;
3049 END;
3050
3051 -- Public
3052 FUNCTION VALIDATE_TEST6(p_run_id IN NUMBER,
3053 p_bundle_id IN NUMBER,
3054 p_num_workers IN NUMBER,
3055 p_test_tab_name IN VARCHAR2,
3056 p_test_tab_num_rows IN NUMBER,
3057 p_c2_arg_id IN NUMBER,
3058 p_c2_final_arg_id IN NUMBER,
3059 p_c2_run_arg_id IN NUMBER)
3060 RETURN BOOLEAN
3061 IS
3062 l_testnum VARCHAR2(20) := 'TEST6';
3063 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_'||l_testnum;
3064
3065 l_c2_target_sum NUMBER := p_test_tab_num_rows*2;
3066 l_c2_final_sum NUMBER;
3067 l_c2_run_sum NUMBER;
3068 l_valid_value_flag VARCHAR2(3);
3069 l_canonical_value VARCHAR2(4000);
3070 BEGIN
3071 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
3072
3073 --first validate the run/bundle/task/unit
3074 IF NOT VALIDATE_RUN_RECURSIVE(p_run_id,
3075 p_num_workers,
3076 p_num_bundles => 1,
3077 p_num_tasks => 1,
3078 p_num_units => 1,
3079 p_num_dmls => 1,
3080 p_num_dml_rows => p_test_tab_num_rows) THEN
3081 RAISE VALIDATE_FAILED;
3082 END IF;
3083
3084 --check that the test table has the correct number of rows
3085 IF NOT VALIDATE_TEST_TABLE_UNCHANGED(p_test_tab_name,
3086 p_test_tab_num_rows) THEN
3087 RAISE VALIDATE_FAILED;
3088 END IF;
3089
3090 --check that the c2 sum stored in the arg is correct
3091 IF NOT VALIDATE_TEST_TABLE_ARG_VALUES(TRUE,
3092 p_c2_arg_id,
3093 l_c2_target_sum,
3094 p_test_tab_name,
3095 p_test_tab_num_rows) THEN
3096 RAISE VALIDATE_FAILED;
3097 END IF;
3098
3099 --make sure that the final c2 sum was run on the unchanged test table only and sums to the # of rows
3100 --check the row count
3101 BEGIN
3102 SELECT valid_value_flag, canonical_value
3103 INTO l_valid_value_flag, l_canonical_value
3104 FROM fnd_oam_dscram_args_b
3105 WHERE arg_id = p_c2_final_arg_id;
3106 EXCEPTION
3107 WHEN OTHERS THEN
3108 fnd_oam_debug.log(6, l_ctxt, 'Failed to query arg value for final C2 sum: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
3109 RAISE VALIDATE_FAILED;
3110 END;
3111 IF l_valid_value_flag IS NULL OR l_valid_value_flag <> FND_API.G_TRUE THEN
3112 fnd_oam_debug.log(6, l_ctxt, 'Valid value flag of final C2 Sum for dml is incorrect: '||l_valid_value_flag);
3113 RAISE VALIDATE_FAILED;
3114 END IF;
3115 l_c2_final_sum := FND_NUMBER.CANONICAL_TO_NUMBER(l_canonical_value);
3116 IF l_c2_final_sum IS NULL OR l_c2_final_sum <> p_test_tab_num_rows THEN
3117 fnd_oam_debug.log(6, l_ctxt, 'Final C2 Sum('||l_c2_final_sum||') not equal to target value('||p_test_tab_num_rows||')');
3118 RAISE VALIDATE_FAILED;
3119 END IF;
3120
3121 --make sure that the run c2 sum also equals the # of rows.
3122 BEGIN
3123 SELECT SUM(to_number(canonical_value))
3124 INTO l_c2_run_sum
3125 FROM fnd_oam_dscram_arg_values
3126 WHERE arg_id = p_c2_run_arg_id
3127 AND valid_value_flag = FND_API.G_TRUE
3128 AND rownum < 2;
3129 EXCEPTION
3130 WHEN OTHERS THEN
3131 fnd_oam_debug.log(6, l_ctxt, 'Failed to query arg value for run C2 sum: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
3132 RAISE VALIDATE_FAILED;
3133 END;
3134 IF l_c2_run_sum IS NULL OR l_c2_run_sum <> p_test_tab_num_rows THEN
3135 fnd_oam_debug.log(6, l_ctxt, 'Run C2 Sum('||l_c2_run_sum||') not equal to target value('||p_test_tab_num_rows||')');
3136 RAISE VALIDATE_FAILED;
3137 END IF;
3138
3139 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3140 RETURN TRUE;
3141 EXCEPTION
3142 WHEN VALIDATE_FAILED THEN
3143 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3144 RETURN FALSE;
3145 WHEN OTHERS THEN
3146 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
3147 RETURN FALSE;
3148 END;
3149
3150 --Public
3151 PROCEDURE EXECUTE_TEST6(p_run_id IN NUMBER DEFAULT 1,
3152 p_bundle_id IN NUMBER DEFAULT 1,
3153 p_num_bundles IN NUMBER DEFAULT 1,
3154 p_num_workers IN NUMBER DEFAULT 1,
3155 x_verdict OUT NOCOPY VARCHAR2)
3156 IS
3157 l_testnum VARCHAR2(20) := 'TEST6';
3158 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_'||l_testnum;
3159
3160 l_test_table_name VARCHAR2(30) := B_TEST_TABLE_NAME_PREFIX||'6_TAB1';
3161 l_test_table_num_rows NUMBER := 5000;
3162 l_worker_id NUMBER;
3163 l_task_id NUMBER;
3164 l_unit_id NUMBER;
3165 l_dml_id NUMBER;
3166 l_c2_arg_name VARCHAR2(60) := FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_C2_SUM';
3167 l_c2_final_arg_name VARCHAR2(60) := FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_C2_FINAL_SUM';
3168 l_c2_arg_id NUMBER;
3169 l_c2_final_arg_id NUMBER;
3170 l_c2_run_arg_id NUMBER;
3171
3172 l_did_init BOOLEAN := FALSE;
3173 l_lock_handle VARCHAR2(128);
3174 l_retval NUMBER;
3175 l_retbool BOOLEAN;
3176 l_retbool_final BOOLEAN;
3177 l_return_status VARCHAR2(6);
3178 l_return_msg VARCHAR2(2048);
3179 l_start NUMBER;
3180 l_end NUMBER;
3181 k NUMBER;
3182 j NUMBER;
3183 BEGIN
3184 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
3185 PRINT_TEST_ENTRY_STATE(l_ctxt, p_run_id, p_bundle_id, p_num_bundles, p_num_workers);
3186
3187 ROLLBACK;
3188 IF RUN_NEEDS_INIT(p_run_id,
3189 l_lock_handle) THEN
3190
3191 fnd_oam_debug.log(1, l_ctxt, 'Initializing Test...');
3192 l_start := DBMS_UTILITY.GET_TIME;
3193
3194 --create a test table to work on
3195 IF NOT MAKE_TEST_TABLE(l_test_table_name,
3196 l_test_table_num_rows) THEN
3197 RAISE INIT_FAILED;
3198 END IF;
3199
3200 --create a single run
3201 IF NOT MAKE_RUN(p_run_id,
3202 p_weight => 200,
3203 p_name => B_DIAG_RUN_NAME_PREFIX||l_testnum) THEN
3204 RAISE INIT_FAILED;
3205 END IF;
3206
3207 --create a context arg to receive the final c2 sum
3208 IF NOT MAKE_ARG(p_arg_name => l_c2_final_arg_name,
3209 p_parent_id => p_run_id,
3210 p_allow_override_source => FND_API.G_TRUE,
3211 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
3212 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
3213 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_WORKER,
3214 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_CONSTANT,
3215 p_src_text => '99',
3216 x_arg_id => l_c2_run_arg_id) THEN
3217 RAISE INIT_FAILED;
3218 END IF;
3219
3220 --solo bundle
3221 IF NOT MAKE_BUNDLE(p_run_id,
3222 p_bundle_id,
3223 p_weight => 200,
3224 p_min_par_weight => 5,
3225 p_workers_allowed => p_num_workers) THEN
3226 RAISE INIT_FAILED;
3227 END IF;
3228 --create task
3229 IF NOT MAKE_TASK(p_bundle_id,
3230 p_weight => 200,
3231 x_task_id => l_task_id) THEN
3232 RAISE INIT_FAILED;
3233 END IF;
3234 --make unit
3235 IF NOT MAKE_UNIT(l_task_id,
3236 p_weight => 200,
3237 p_unit_obj_owner => B_TEST_TABLE_OWNER,
3238 p_unit_obj_name => l_test_table_name,
3239 p_batch_size => 100,
3240 p_sug_disable_splitting => FND_API.G_FALSE,
3241 x_unit_id => l_unit_id) THEN
3242 RAISE INIT_FAILED;
3243 END IF;
3244 --make a simple dml
3245 IF NOT MAKE_DML(l_unit_id,
3246 p_weight => 200,
3247 p_dml_stmt => 'UPDATE '||l_test_table_name||' SET C2 = C2 + 1',
3248 p_where_clause => NULL,
3249 x_dml_id => l_dml_id) THEN
3250 RAISE INIT_FAILED;
3251 END IF;
3252 --make an output arg for the dml to fetch C2's new sum
3253 IF NOT MAKE_ARG(p_arg_name => l_c2_arg_name,
3254 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
3255 p_parent_id => l_dml_id,
3256 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
3257 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_WRITE,
3258 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
3259 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL_RESTRICTABLE,
3260 p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
3261 x_arg_id => l_c2_arg_id) THEN
3262 RAISE INIT_FAILED;
3263 END IF;
3264 --make an output arg to run at the end of the splitting to get the final c2 sum on all rows
3265 IF NOT MAKE_ARG(p_arg_name => l_c2_final_arg_name,
3266 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
3267 p_parent_id => l_dml_id,
3268 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
3269 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
3270 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
3271 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL,
3272 p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
3273 x_arg_id => l_c2_final_arg_id) THEN
3274 RAISE INIT_FAILED;
3275 END IF;
3276
3277 COMMIT;
3278 l_end := DBMS_UTILITY.GET_TIME;
3279 fnd_oam_debug.log(1, l_ctxt, 'Init Done - Duration: '||(l_end - l_start)/100|| ' seconds.');
3280 l_did_init := TRUE;
3281 END IF;
3282
3283 --do work
3284 l_retbool := EXECUTE_BUNDLE_WRAPPER(l_ctxt,
3285 p_run_id,
3286 p_bundle_id,
3287 l_worker_id,
3288 l_return_status,
3289 l_return_msg);
3290
3291 --make the guy who did init manage the sync, do cleanup and verify
3292 IF l_did_init THEN
3293 l_retbool_final := SYNC_ON_FINISH(p_run_id,
3294 l_retbool,
3295 p_num_workers,
3296 l_did_init);
3297 --validate the results
3298 l_retbool_final := l_retbool_final AND
3299 VALIDATE_TEST6(p_run_id,
3300 p_bundle_id,
3301 p_num_workers,
3302 l_test_table_name,
3303 l_test_table_num_rows,
3304 l_c2_arg_id,
3305 l_c2_final_arg_id,
3306 l_c2_run_arg_id);
3307
3308 --send acks to the other workers with the final status
3309 SEND_ACKS(p_run_id,
3310 l_retbool_final,
3311 p_num_workers);
3312 ELSE
3313 l_retbool_final := SYNC_ON_FINISH(p_run_id,
3314 l_retbool);
3315 END IF;
3316
3317 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3318 IF l_retbool_final THEN
3319 x_verdict := FND_API.G_TRUE;
3320 ELSE
3321 x_verdict := FND_API.G_FALSE;
3322 END IF;
3323 EXCEPTION
3324 WHEN INIT_FAILED THEN
3325 --release the run lock on failure just in case
3326 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
3327 IF l_retval <> 0 THEN
3328 fnd_oam_debug.log(6, l_ctxt, 'Failed to release run lock: '||l_retval);
3329 END IF;
3330 ROLLBACK;
3331 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3332 x_verdict := FND_API.G_FALSE;
3333 WHEN SYNC_FAILED THEN
3334 fnd_oam_debug.log(6, l_ctxt, 'Sync Failed');
3335 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3336 x_verdict := FND_API.G_FALSE;
3337 WHEN OTHERS THEN
3338 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
3339 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3340 x_verdict := FND_API.G_FALSE;
3341 END;
3342
3343 -- Public
3344 FUNCTION VALIDATE_TEST7(p_run_id IN NUMBER,
3345 p_bundle_id IN NUMBER,
3346 p_num_workers IN NUMBER,
3347 p_test_tab_name IN VARCHAR2,
3348 p_test_tab_num_rows IN NUMBER,
3349 p_del_dml_id IN NUMBER,
3350 p_upd_dml_id IN NUMBER,
3351 p_c2_arg_id IN NUMBER,
3352 p_c3_arg_id IN NUMBER,
3353 p_c4_arg_id IN NUMBER)
3354 RETURN BOOLEAN
3355 IS
3356 l_testnum VARCHAR2(20) := 'TEST7';
3357 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_'||l_testnum;
3358
3359 l_c2_target_sum NUMBER := ((p_test_tab_num_rows/2)*((p_test_tab_num_rows/2)+2))/4 + (2*(p_test_tab_num_rows/2));
3360
3361 l_valid_value_flag VARCHAR2(3);
3362 l_canonical_value VARCHAR2(4000);
3363 BEGIN
3364 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
3365
3366 --first validate the run/bundle/task/unit
3367 IF NOT VALIDATE_RUN_RECURSIVE(p_run_id,
3368 p_num_workers,
3369 p_num_bundles => 1,
3370 p_num_tasks => 1,
3371 p_num_units => 1) THEN
3372 RAISE VALIDATE_FAILED;
3373 END IF;
3374
3375 --validate each of the DMLs
3376 IF NOT VALIDATE_DML_SUCCESS(p_run_id,
3377 p_del_dml_id,
3378 p_target_rows => p_test_tab_num_rows*3/4) THEN
3379 RAISE VALIDATE_FAILED;
3380 END IF;
3381 --update should have only been run on half the rows
3382 IF NOT VALIDATE_DML_SUCCESS(p_run_id,
3383 p_upd_dml_id,
3384 p_target_rows => p_test_tab_num_rows*1/4) THEN
3385 RAISE VALIDATE_FAILED;
3386 END IF;
3387
3388 --check that the test table has the correct number of rows
3389 IF NOT VALIDATE_TEST_TABLE_UNCHANGED(p_test_tab_name,
3390 p_test_tab_num_rows) THEN
3391 RAISE VALIDATE_FAILED;
3392 END IF;
3393
3394 --check that the c2 sum stored in the arg is correct
3395 IF NOT VALIDATE_TEST_TABLE_ARG_VALUES(TRUE,
3396 p_c2_arg_id,
3397 l_c2_target_sum,
3398 p_test_tab_name,
3399 p_test_tab_num_rows,
3400 p_c3_arg_id,
3401 p_c4_arg_id) THEN
3402 RAISE VALIDATE_FAILED;
3403 END IF;
3404
3405 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3406 RETURN TRUE;
3407 EXCEPTION
3408 WHEN VALIDATE_FAILED THEN
3409 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3410 RETURN FALSE;
3411 WHEN OTHERS THEN
3412 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
3413 RETURN FALSE;
3414 END;
3415
3416 --Public
3417 PROCEDURE EXECUTE_TEST7(p_run_id IN NUMBER DEFAULT 1,
3418 p_bundle_id IN NUMBER DEFAULT 1,
3419 p_num_bundles IN NUMBER DEFAULT 1,
3420 p_num_workers IN NUMBER DEFAULT 1,
3421 x_verdict OUT NOCOPY VARCHAR2)
3422 IS
3423 l_testnum VARCHAR2(20) := 'TEST7';
3424 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_'||l_testnum;
3425
3426 l_test_table_name VARCHAR2(30) := B_TEST_TABLE_NAME_PREFIX||'7_TAB1';
3427 l_test_table_num_rows NUMBER := 10000;
3428 l_worker_id NUMBER;
3429 l_task_id NUMBER;
3430 l_unit_id NUMBER;
3431 l_del_dml_id NUMBER;
3432 l_upd_dml_id NUMBER;
3433 l_c2_arg_in_name VARCHAR2(60) := FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_C2_IN';
3434 l_c3_arg_in_name VARCHAR2(60) := FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_C3_IN';
3435 l_c4_arg_in_name VARCHAR2(60) := FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_C4_IN';
3436 l_c2_arg_out_name VARCHAR2(60) := FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_C2_OUT';
3437 l_c3_arg_out_name VARCHAR2(60) := FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_C3_OUT';
3438 l_c4_arg_out_name VARCHAR2(60) := FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_C4_OUT';
3439 l_c2_arg_in_id NUMBER;
3440 l_c3_arg_in_id NUMBER;
3441 l_c4_run_in_id NUMBER;
3442 l_c4_arg_in_id NUMBER;
3443 l_c2_arg_out_id NUMBER;
3444 l_c3_arg_out_id NUMBER;
3445 l_c4_arg_out_id NUMBER;
3446 l_c2_val VARCHAR2(30) := '3';
3447 l_c3_val VARCHAR2(30) := l_testnum||'_TESTVAL';
3448 l_c4_val VARCHAR2(30) := '2005/08/31 11:22:33';
3449
3450 l_did_init BOOLEAN := FALSE;
3451 l_lock_handle VARCHAR2(128);
3452 l_retval NUMBER;
3453 l_retbool BOOLEAN;
3454 l_retbool_final BOOLEAN;
3455 l_return_status VARCHAR2(6);
3456 l_return_msg VARCHAR2(2048);
3457 l_start NUMBER;
3458 l_end NUMBER;
3459 k NUMBER;
3460 j NUMBER;
3461 BEGIN
3462 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
3463 PRINT_TEST_ENTRY_STATE(l_ctxt, p_run_id, p_bundle_id, p_num_bundles, p_num_workers);
3464
3465 ROLLBACK;
3466 IF RUN_NEEDS_INIT(p_run_id,
3467 l_lock_handle) THEN
3468
3469 fnd_oam_debug.log(1, l_ctxt, 'Initializing Test...');
3470 l_start := DBMS_UTILITY.GET_TIME;
3471
3472 --create a test table to work on
3473 IF NOT MAKE_TEST_TABLE(l_test_table_name,
3474 l_test_table_num_rows) THEN
3475 RAISE INIT_FAILED;
3476 END IF;
3477
3478 --create a single run
3479 IF NOT MAKE_RUN(p_run_id,
3480 p_weight => 400,
3481 p_name => B_DIAG_RUN_NAME_PREFIX||l_testnum) THEN
3482 RAISE INIT_FAILED;
3483 END IF;
3484 --create a context arg to do the actually sourcing of the v4 value
3485 IF NOT MAKE_ARG(p_arg_name => l_c4_arg_in_name,
3486 p_parent_id => p_run_id,
3487 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_DATE,
3488 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
3489 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
3490 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL,
3491 p_src_text => 'SELECT FND_DATE.CANONICAL_TO_DATE('''||l_c4_val||''') FROM dual',
3492 p_src_where_clause => NULL,
3493 x_arg_id => l_c4_run_in_id) THEN
3494 RAISE INIT_FAILED;
3495 END IF;
3496
3497 --solo bundle
3498 IF NOT MAKE_BUNDLE(p_run_id,
3499 p_bundle_id,
3500 p_weight => 400,
3501 p_min_par_weight => 50,
3502 p_workers_allowed => p_num_workers) THEN
3503 RAISE INIT_FAILED;
3504 END IF;
3505 --create task
3506 IF NOT MAKE_TASK(p_bundle_id,
3507 p_weight => 400,
3508 x_task_id => l_task_id) THEN
3509 RAISE INIT_FAILED;
3510 END IF;
3511 --make unit
3512 IF NOT MAKE_UNIT(l_task_id,
3513 p_weight => 400,
3514 p_unit_obj_owner => B_TEST_TABLE_OWNER,
3515 p_unit_obj_name => l_test_table_name,
3516 p_batch_size => 100,
3517 p_sug_disable_splitting => FND_API.G_FALSE,
3518 x_unit_id => l_unit_id) THEN
3519 RAISE INIT_FAILED;
3520 END IF;
3521 --make the update dml first, but put priority such that it executes second
3522 IF NOT MAKE_DML(l_unit_id,
3523 p_priority => 2,
3524 p_weight => 133,
3525 p_dml_stmt => 'UPDATE '||l_test_table_name||' SET C2 = C2 + C1 + :DS__TEST7_C2_IN, C3 = :DS__TEST7_C3_IN, C4 = :DS__TEST7_C4_IN',
3526 p_where_clause => NULL,
3527 x_dml_id => l_upd_dml_id) THEN
3528 RAISE INIT_FAILED;
3529 END IF;
3530 --make an input arg for the C2 arg
3531 IF NOT MAKE_ARG(p_arg_name => l_c2_arg_in_name,
3532 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
3533 p_parent_id => l_upd_dml_id,
3534 p_binding_enabled_flag => FND_API.G_TRUE,
3535 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
3536 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ,
3537 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
3538 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_CONSTANT,
3539 p_src_text => l_c2_val,
3540 x_arg_id => l_c2_arg_in_id) THEN
3541 RAISE INIT_FAILED;
3542 END IF;
3543 --make an output arg for the C2 arg
3544 IF NOT MAKE_ARG(p_arg_name => l_c2_arg_out_name,
3545 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
3546 p_parent_id => l_upd_dml_id,
3547 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
3548 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_WRITE,
3549 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
3550 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL_RESTRICTABLE,
3551 p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
3552 x_arg_id => l_c2_arg_out_id) THEN
3553 RAISE INIT_FAILED;
3554 END IF;
3555 --make an input arg for C3
3556 IF NOT MAKE_ARG(p_arg_name => l_c3_arg_in_name,
3557 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
3558 p_parent_id => l_upd_dml_id,
3559 p_binding_enabled_flag => FND_API.G_TRUE,
3560 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
3561 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
3562 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_WORKER,
3563 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL,
3564 p_src_text => 'SELECT '''||l_c3_val||''' FROM DUAL',
3565 x_arg_id => l_c3_arg_in_id) THEN
3566 RAISE INIT_FAILED;
3567 END IF;
3568 --make an output arg for the C3 arg
3569 IF NOT MAKE_ARG(p_arg_name => l_c3_arg_out_name,
3570 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
3571 p_parent_id => l_upd_dml_id,
3572 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
3573 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_WRITE,
3574 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
3575 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL_RESTRICTABLE,
3576 p_src_text => 'SELECT COUNT(ROWID) FROM '||l_test_table_name,
3577 p_src_where_clause => 'C3 IS NOT NULL AND C3 <> '''||l_c3_val||'''',
3578 x_arg_id => l_c3_arg_out_id) THEN
3579 RAISE INIT_FAILED;
3580 END IF;
3581 --make an input arg for C4
3582 IF NOT MAKE_ARG(p_arg_name => l_c4_arg_in_name,
3583 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
3584 p_parent_id => l_upd_dml_id,
3585 p_binding_enabled_flag => FND_API.G_TRUE,
3586 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_DATE,
3587 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
3588 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ALWAYS,
3589 p_src_type => NULL,
3590 x_arg_id => l_c4_arg_in_id) THEN
3591 RAISE INIT_FAILED;
3592 END IF;
3593 --make an output arg for C4
3594 IF NOT MAKE_ARG(p_arg_name => l_c4_arg_out_name,
3595 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
3596 p_parent_id => l_upd_dml_id,
3597 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
3598 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_WRITE,
3599 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
3600 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL_RESTRICTABLE,
3601 p_src_text => 'SELECT COUNT(ROWID) FROM '||l_test_table_name,
3602 p_src_where_clause => 'C4 IS NOT NULL AND C4 <> FND_DATE.CANONICAL_TO_DATE('''||l_c4_val||''')',
3603 x_arg_id => l_c4_arg_out_id) THEN
3604 RAISE INIT_FAILED;
3605 END IF;
3606
3607 --finally, make another dml for the delete that preceeds the update and removes all odd rows
3608 IF NOT MAKE_DML(l_unit_id,
3609 p_priority => 1,
3610 p_weight => 266,
3611 p_dml_stmt => 'DELETE FROM '||l_test_table_name,
3612 p_where_clause => '(MOD(C1, 2) = 1 OR C1 > '||l_test_table_num_rows/2||')',
3613 x_dml_id => l_del_dml_id) THEN
3614 RAISE INIT_FAILED;
3615 END IF;
3616
3617
3618 COMMIT;
3619 l_end := DBMS_UTILITY.GET_TIME;
3620 fnd_oam_debug.log(1, l_ctxt, 'Init Done - Duration: '||(l_end - l_start)/100|| ' seconds.');
3621 l_did_init := TRUE;
3622 END IF;
3623
3624 --do work
3625 l_retbool := EXECUTE_BUNDLE_WRAPPER(l_ctxt,
3626 p_run_id,
3627 p_bundle_id,
3628 l_worker_id,
3629 l_return_status,
3630 l_return_msg);
3631
3632 --make the guy who did init manage the sync, do cleanup and verify
3633 IF l_did_init THEN
3634 l_retbool_final := SYNC_ON_FINISH(p_run_id,
3635 l_retbool,
3636 p_num_workers,
3637 l_did_init);
3638 --validate the results
3639 l_retbool_final := l_retbool_final AND
3640 VALIDATE_TEST7(p_run_id,
3641 p_bundle_id,
3642 p_num_workers,
3643 l_test_table_name,
3644 l_test_table_num_rows,
3645 l_del_dml_id,
3646 l_upd_dml_id,
3647 l_c2_arg_out_id,
3648 l_c3_arg_out_id,
3649 l_c4_arg_out_id);
3650
3651 --send acks to the other workers with the final status
3652 SEND_ACKS(p_run_id,
3653 l_retbool_final,
3654 p_num_workers);
3655 ELSE
3656 l_retbool_final := SYNC_ON_FINISH(p_run_id,
3657 l_retbool);
3658 END IF;
3659
3660 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3661 IF l_retbool_final THEN
3662 x_verdict := FND_API.G_TRUE;
3663 ELSE
3664 x_verdict := FND_API.G_FALSE;
3665 END IF;
3666 EXCEPTION
3667 WHEN INIT_FAILED THEN
3668 --release the run lock on failure just in case
3669 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
3670 IF l_retval <> 0 THEN
3671 fnd_oam_debug.log(6, l_ctxt, 'Failed to release run lock: '||l_retval);
3672 END IF;
3673 ROLLBACK;
3674 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3675 x_verdict := FND_API.G_FALSE;
3676 WHEN SYNC_FAILED THEN
3677 fnd_oam_debug.log(6, l_ctxt, 'Sync Failed');
3678 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3679 x_verdict := FND_API.G_FALSE;
3680 WHEN OTHERS THEN
3681 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
3682 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3683 x_verdict := FND_API.G_FALSE;
3684 END;
3685
3686 -- Public
3687 FUNCTION VALIDATE_TEST8(p_run_id IN NUMBER,
3688 p_bundle_id IN NUMBER,
3689 p_num_workers IN NUMBER,
3690 p_verdict_arg_id IN NUMBER)
3691 RETURN BOOLEAN
3692 IS
3693 l_testnum VARCHAR2(20) := 'TEST8';
3694 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_'||l_testnum;
3695
3696 l_valid_value_flag VARCHAR2(3);
3697 l_canonical_value VARCHAR2(4000);
3698 BEGIN
3699 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
3700
3701 --first validate the run/bundle/task/unit/plsql
3702 IF NOT VALIDATE_RUN_RECURSIVE(p_run_id,
3703 p_num_workers,
3704 p_num_bundles => 1,
3705 p_num_tasks => 1,
3706 p_num_units => 1,
3707 p_num_plsqls => 1) THEN
3708 RAISE VALIDATE_FAILED;
3709 END IF;
3710
3711 --get the value of the verdict output arg
3712 BEGIN
3713 SELECT valid_value_flag, canonical_value
3714 INTO l_valid_value_flag, l_canonical_value
3715 FROM fnd_oam_dscram_arg_values
3716 WHERE arg_id = p_verdict_arg_id;
3717 EXCEPTION
3718 WHEN OTHERS THEN
3719 fnd_oam_debug.log(6, l_ctxt, 'Failed to query arg value for verdict: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
3720 RAISE VALIDATE_FAILED;
3721 END;
3722 IF l_valid_value_flag IS NULL OR l_valid_value_flag <> FND_API.G_TRUE THEN
3723 fnd_oam_debug.log(6, l_ctxt, 'Valid value flag of verdict arg is incorrect: '||l_valid_value_flag);
3724 RAISE VALIDATE_FAILED;
3725 END IF;
3726 IF l_canonical_value IS NULL OR l_canonical_value <> FND_API.G_TRUE THEN
3727 fnd_oam_debug.log(6, l_ctxt, 'Verdict('||l_canonical_value||') not equal to target value('||FND_API.G_TRUE||')');
3728 RAISE VALIDATE_FAILED;
3729 END IF;
3730
3731 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3732 RETURN TRUE;
3733 EXCEPTION
3734 WHEN VALIDATE_FAILED THEN
3735 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
3736 RETURN FALSE;
3737 WHEN OTHERS THEN
3738 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
3739 RETURN FALSE;
3740 END;
3741
3742 -- Public
3743 PROCEDURE TEST8_PROC1(p_run_id IN NUMBER,
3744 p_run_mode IN VARCHAR2,
3745 p_bundle_id IN NUMBER,
3746 p_bundle_workers_allowed IN NUMBER,
3747 p_bundle_batch_size IN NUMBER,
3748 p_worker_id IN NUMBER,
3749 p_task_id IN NUMBER,
3750 p_unit_id IN NUMBER,
3751 p_using_splitting IN VARCHAR2,
3752 p_rowid_lbound IN ROWID,
3753 p_rowid_ubound IN ROWID,
3754 p_unit_object_owner IN VARCHAR2,
3755 p_unit_object_name IN VARCHAR2,
3756 p_unit_workers_allowed IN NUMBER,
3757 p_unit_batch_size IN NUMBER,
3758 p_plsql_id IN NUMBER,
3759 p_arg_id IN NUMBER,
3760 p_workers_allowed IN NUMBER,
3761 p_batch_size IN NUMBER,
3762 x_verdict OUT NOCOPY VARCHAR2,
3763 x_return_msg OUT NOCOPY VARCHAR2)
3764 IS
3765
3766 l_id NUMBER;
3767 l_run_mode VARCHAR2(30);
3768 l_batch_size NUMBER;
3769 l_workers_allowed NUMBER;
3770 l_worker_id NUMBER;
3771 l_disable_splitting VARCHAR2(3);
3772 l_unit_object_owner VARCHAR2(30);
3773 l_unit_object_name VARCHAR2(30);
3774 BEGIN
3775 --default to the failed status
3776 x_verdict := FND_API.G_FALSE;
3777 x_return_msg := '';
3778
3779 --query out the run with this run_id, also validate run_mode
3780 BEGIN
3781 SELECT run_mode
3782 INTO l_run_mode
3783 FROM fnd_oam_dscram_runs_b
3784 WHERE run_id = p_run_id;
3785 EXCEPTION
3786 WHEN no_data_found THEN
3787 x_return_msg := 'Failed to query run for run_id: '||p_run_id;
3788 RETURN;
3789 END;
3790
3791 IF l_run_mode IS NULL OR p_run_mode IS NULL OR l_run_mode <> p_run_mode THEN
3792 x_return_msg := 'Expected run_mode('||p_run_mode||'), found run_mode('||l_run_mode||')';
3793 RETURN;
3794 END IF;
3795
3796 --check bundle_id/batch size
3797 BEGIN
3798 SELECT batch_size, workers_allowed
3799 INTO l_batch_size, l_workers_allowed
3800 FROM fnd_oam_dscram_bundles
3801 WHERE run_id = p_run_id
3802 AND bundle_id = p_bundle_id;
3803 EXCEPTION
3804 WHEN no_data_found THEN
3805 x_return_msg := 'Failed to query bundle for run_id: '||p_run_id||', bundle_id: '||p_bundle_id;
3806 RETURN;
3807 END;
3808
3809 IF l_batch_size IS NULL OR p_bundle_batch_size IS NULL OR l_batch_size <> p_bundle_batch_size THEN
3810 x_return_msg := 'Expected bundle batch_size('||p_bundle_batch_size||'), found batch_size('||l_batch_size||')';
3811 RETURN;
3812 END IF;
3813 IF l_batch_size IS NULL OR p_batch_size IS NULL OR l_batch_size <> p_batch_size THEN
3814 x_return_msg := 'Expected general batch_size('||p_batch_size||'), found batch_size('||l_batch_size||')';
3815 RETURN;
3816 END IF;
3817 IF l_workers_allowed IS NULL OR p_bundle_workers_allowed IS NULL OR l_workers_allowed <> p_bundle_workers_allowed THEN
3818 x_return_msg := 'Expected bundle workers_allowed('||p_bundle_workers_allowed||'), found workers_allowed('||l_workers_allowed||')';
3819 RETURN;
3820 END IF;
3821 --check the worker_id matches the state
3822 l_worker_id := FND_OAM_DSCRAM_BUNDLES_PKG.GET_WORKER_ID;
3823 IF p_worker_id IS NULL OR p_worker_id <> l_worker_id THEN
3824 x_return_msg := 'Worker ID('||p_worker_id||') not the same as the one provided by dscram_bundles('||l_worker_id||')';
3825 RETURN;
3826 END IF;
3827
3828 --check task_id
3829 BEGIN
3830 SELECT task_id
3831 INTO l_id
3832 FROM fnd_oam_dscram_tasks
3833 WHERE bundle_id = p_bundle_id
3834 AND task_id = p_task_id;
3835 EXCEPTION
3836 WHEN no_data_found THEN
3837 x_return_msg := 'Failed to query task for bundle_id: '||p_bundle_id||', task_id: '||p_task_id;
3838 RETURN;
3839 END;
3840
3841 --check the unit
3842 BEGIN
3843 SELECT actual_disable_splitting, actual_workers_allowed, unit_object_owner, unit_object_name, batch_size
3844 INTO l_disable_splitting, l_workers_allowed, l_unit_object_owner, l_unit_object_name, l_batch_size
3845 FROM fnd_oam_dscram_units
3846 WHERE task_id = p_task_id
3847 AND unit_id = p_unit_id;
3848 EXCEPTION
3849 WHEN no_data_found THEN
3850 x_return_msg := 'Failed to query unit for task_id: '||p_task_id||', unit_id: '||p_unit_id;
3851 RETURN;
3852 END;
3853
3854 IF l_disable_splitting IS NULL OR l_disable_splitting <> FND_API.G_TRUE THEN
3855 x_return_msg := 'Expected disable_splitting('||FND_API.G_TRUE||'), found disable_splitting('||l_disable_splitting||')';
3856 RETURN;
3857 END IF;
3858 IF p_using_splitting IS NULL OR p_using_splitting <> FND_API.G_FALSE THEN
3859 x_return_msg := 'Expected using_splitting('||FND_API.G_TRUE||'), found using_splitting('||p_using_splitting||')';
3860 RETURN;
3861 END IF;
3862 IF l_workers_allowed IS NULL OR l_workers_allowed <> 1 THEN
3863 x_return_msg := 'Expected database unit workers_allowed(1), found unit workers_allowed('||l_workers_allowed||')';
3864 RETURN;
3865 END IF;
3866 IF p_unit_workers_allowed IS NULL OR p_unit_workers_allowed <> 1 THEN
3867 x_return_msg := 'Expected unit workers_allowed(1), found unit workers_allowed('||p_unit_workers_allowed||')';
3868 RETURN;
3869 END IF;
3870 IF p_workers_allowed IS NULL OR p_workers_allowed <> 1 THEN
3871 x_return_msg := 'Expected general workers_allowed(1), found unit workers_allowed('||p_workers_allowed||')';
3872 RETURN;
3873 END IF;
3874 IF p_unit_batch_size IS NOT NULL THEN
3875 x_return_msg := 'Expected unit batch_size(NULL), found unit batch_size('||p_unit_batch_size||')';
3876 RETURN;
3877 END IF;
3878 IF l_unit_object_owner IS NULL OR p_unit_object_owner IS NULL OR l_unit_object_owner <> p_unit_object_owner THEN
3879 x_return_msg := 'Expected unit_object_owner('||p_unit_object_owner||'), found unit_object_owner('||l_unit_object_owner||')';
3880 RETURN;
3881 END IF;
3882 IF l_unit_object_name IS NULL OR p_unit_object_name IS NULL OR l_unit_object_name <> p_unit_object_name THEN
3883 x_return_msg := 'Expected unit_object_name('||p_unit_object_name||'), found unit_object_name('||l_unit_object_name||')';
3884 RETURN;
3885 END IF;
3886
3887 --make sure the rowids are null
3888 IF p_rowid_lbound IS NOT NULL THEN
3889 x_return_msg := 'Expected rowid_lbound to be NULL, found: '||p_rowid_lbound;
3890 RETURN;
3891 END IF;
3892 IF p_rowid_ubound IS NOT NULL THEN
3893 x_return_msg := 'Expected rowid_ubound to be NULL, found: '||p_rowid_ubound;
3894 RETURN;
3895 END IF;
3896
3897 --check the plsql id
3898 BEGIN
3899 SELECT plsql_id
3900 INTO l_id
3901 FROM fnd_oam_dscram_plsqls
3902 WHERE unit_id = p_unit_id
3903 AND plsql_id = p_plsql_id;
3904 EXCEPTION
3905 WHEN no_data_found THEN
3906 x_return_msg := 'Failed to query plsql for unit_id: '||p_unit_id||', plsql_id: '||p_plsql_id;
3907 RETURN;
3908 END;
3909
3910 --check the arg id
3911 BEGIN
3912 SELECT arg_id
3913 INTO l_id
3914 FROM fnd_oam_dscram_args_b
3915 WHERE arg_id = p_arg_id
3916 AND parent_type = FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL
3917 AND parent_id = p_plsql_id;
3918 EXCEPTION
3919 WHEN no_data_found THEN
3920 x_return_msg := 'Failed to query arg for arg_id: '||p_arg_id||', plsql_id: '||p_plsql_id;
3921 RETURN;
3922 END;
3923
3924 --success
3925 x_verdict := FND_API.G_TRUE;
3926 EXCEPTION
3927 WHEN OTHERS THEN
3928 x_verdict := FND_API.G_FALSE;
3929 x_return_msg := SUBSTR('Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))', 1, 4000);
3930 END;
3931
3932 --Public
3933 PROCEDURE EXECUTE_TEST8(p_run_id IN NUMBER DEFAULT 1,
3934 p_bundle_id IN NUMBER DEFAULT 1,
3935 p_num_bundles IN NUMBER DEFAULT 1,
3936 p_num_workers IN NUMBER DEFAULT 1,
3937 x_verdict OUT NOCOPY VARCHAR2)
3938 IS
3939 l_testnum VARCHAR2(20) := 'TEST8';
3940 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_'||l_testnum;
3941
3942 l_worker_id NUMBER;
3943 l_task_id NUMBER;
3944 l_unit_id NUMBER;
3945 l_plsql_id NUMBER;
3946 l_arg_id NUMBER;
3947 l_verdict_arg_id NUMBER;
3948
3949 l_did_init BOOLEAN := FALSE;
3950 l_lock_handle VARCHAR2(128);
3951 l_retval NUMBER;
3952 l_retbool BOOLEAN;
3953 l_retbool_final BOOLEAN;
3954 l_return_status VARCHAR2(6);
3955 l_return_msg VARCHAR2(2048);
3956 l_start NUMBER;
3957 l_end NUMBER;
3958 k NUMBER;
3959 j NUMBER;
3960 BEGIN
3961 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
3962 PRINT_TEST_ENTRY_STATE(l_ctxt, p_run_id, p_bundle_id, p_num_bundles, p_num_workers);
3963
3964 ROLLBACK;
3965 IF RUN_NEEDS_INIT(p_run_id,
3966 l_lock_handle) THEN
3967
3968 fnd_oam_debug.log(1, l_ctxt, 'Initializing Test...');
3969 l_start := DBMS_UTILITY.GET_TIME;
3970
3971 --create a single run
3972 IF NOT MAKE_RUN(p_run_id,
3973 p_weight => 25,
3974 p_name => B_DIAG_RUN_NAME_PREFIX||l_testnum) THEN
3975 RAISE INIT_FAILED;
3976 END IF;
3977 --solo bundle
3978 IF NOT MAKE_BUNDLE(p_run_id,
3979 p_bundle_id,
3980 p_weight => 25,
3981 p_min_par_weight => 26,
3982 p_batch_size => 123,
3983 p_workers_allowed => p_num_workers) THEN
3984 RAISE INIT_FAILED;
3985 END IF;
3986 --solo task
3987 IF NOT MAKE_TASK(p_bundle_id,
3988 p_weight => 25,
3989 x_task_id => l_task_id) THEN
3990 RAISE INIT_FAILED;
3991 END IF;
3992 --make unit
3993 IF NOT MAKE_UNIT(l_task_id,
3994 p_unit_type => FND_OAM_DSCRAM_UTILS_PKG.G_UNIT_TYPE_PLSQL_SET,
3995 p_weight => 25,
3996 p_unit_obj_owner => 'DUMMY_OBJ_OWNER',
3997 p_unit_obj_name => 'DUMMY_OBJ_NAME',
3998 x_unit_id => l_unit_id) THEN
3999 RAISE INIT_FAILED;
4000 END IF;
4001 --make the update dml first, but put priority such that it executes second
4002 IF NOT MAKE_PLSQL(l_unit_id,
4003 p_priority => 1,
4004 p_weight => 25,
4005 p_plsql_text => 'FND_OAM_DSCRAM_DIAG_PKG.TEST8_PROC1(:p_run_id,
4006 :p_run_mode,
4007 :p_bundle_id,
4008 :p_bundle_workers_allowed,
4009 :p_bundle_batch_size,
4010 :p_worker_id,
4011 :p_task_id,
4012 :p_unit_id,
4013 :p_using_splitting,
4014 :p_rowid_lbound,
4015 :p_rowid_ubound,
4016 :p_unit_object_owner,
4017 :p_unit_object_name,
4018 :p_unit_workers_allowed,
4019 :p_unit_batch_size,
4020 :p_plsql_id,
4021 :p_arg_id,
4022 :p_workers_allowed,
4023 :p_batch_size,
4024 :x_verdict,
4025 :x_return_msg)',
4026 x_plsql_id => l_plsql_id) THEN
4027 RAISE INIT_FAILED;
4028 END IF;
4029 --make args for all the state variables
4030 IF NOT MAKE_ARG(p_arg_name => 'p_run_id',
4031 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4032 p_parent_id => l_plsql_id,
4033 p_binding_enabled_flag => FND_API.G_TRUE,
4034 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4035 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4036 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4037 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4038 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_RUN_ID,
4039 x_arg_id => l_arg_id) THEN
4040 RAISE INIT_FAILED;
4041 END IF;
4042 IF NOT MAKE_ARG(p_arg_name => 'p_run_mode',
4043 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4044 p_parent_id => l_plsql_id,
4045 p_binding_enabled_flag => FND_API.G_TRUE,
4046 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
4047 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4048 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4049 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4050 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_RUN_MODE,
4051 x_arg_id => l_arg_id) THEN
4052 RAISE INIT_FAILED;
4053 END IF;
4054 IF NOT MAKE_ARG(p_arg_name => 'p_bundle_id',
4055 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4056 p_parent_id => l_plsql_id,
4057 p_binding_enabled_flag => FND_API.G_TRUE,
4058 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4059 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4060 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4061 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4062 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_BUNDLE_ID,
4063 x_arg_id => l_arg_id) THEN
4064 RAISE INIT_FAILED;
4065 END IF;
4066 IF NOT MAKE_ARG(p_arg_name => 'p_bundle_workers_allowed',
4067 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4068 p_parent_id => l_plsql_id,
4069 p_binding_enabled_flag => FND_API.G_TRUE,
4070 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4071 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4072 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4073 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4074 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_BUNDLE_WORKERS_ALLOWED,
4075 x_arg_id => l_arg_id) THEN
4076 RAISE INIT_FAILED;
4077 END IF;
4078 IF NOT MAKE_ARG(p_arg_name => 'p_bundle_batch_size',
4079 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4080 p_parent_id => l_plsql_id,
4081 p_binding_enabled_flag => FND_API.G_TRUE,
4082 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4083 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4084 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4085 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4086 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_BUNDLE_BATCH_SIZE,
4087 x_arg_id => l_arg_id) THEN
4088 RAISE INIT_FAILED;
4089 END IF;
4090 IF NOT MAKE_ARG(p_arg_name => 'p_worker_id',
4091 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4092 p_parent_id => l_plsql_id,
4093 p_binding_enabled_flag => FND_API.G_TRUE,
4094 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4095 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4096 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_WORKER,
4097 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4098 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_WORKER_ID,
4099 x_arg_id => l_arg_id) THEN
4100 RAISE INIT_FAILED;
4101 END IF;
4102 IF NOT MAKE_ARG(p_arg_name => 'p_task_id',
4103 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4104 p_parent_id => l_plsql_id,
4105 p_binding_enabled_flag => FND_API.G_TRUE,
4106 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4107 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4108 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4109 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4110 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_TASK_ID,
4111 x_arg_id => l_arg_id) THEN
4112 RAISE INIT_FAILED;
4113 END IF;
4114 IF NOT MAKE_ARG(p_arg_name => 'p_unit_id',
4115 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4116 p_parent_id => l_plsql_id,
4117 p_binding_enabled_flag => FND_API.G_TRUE,
4118 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4119 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4120 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4121 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4122 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_ID,
4123 x_arg_id => l_arg_id) THEN
4124 RAISE INIT_FAILED;
4125 END IF;
4126 IF NOT MAKE_ARG(p_arg_name => 'p_using_splitting',
4127 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4128 p_parent_id => l_plsql_id,
4129 p_binding_enabled_flag => FND_API.G_TRUE,
4130 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
4131 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4132 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4133 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4134 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_USING_SPLITTING,
4135 x_arg_id => l_arg_id) THEN
4136 RAISE INIT_FAILED;
4137 END IF;
4138 IF NOT MAKE_ARG(p_arg_name => 'p_rowid_lbound',
4139 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4140 p_parent_id => l_plsql_id,
4141 p_binding_enabled_flag => FND_API.G_TRUE,
4142 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID,
4143 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4144 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
4145 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4146 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_LBOUND,
4147 x_arg_id => l_arg_id) THEN
4148 RAISE INIT_FAILED;
4149 END IF;
4150 IF NOT MAKE_ARG(p_arg_name => 'p_rowid_ubound',
4151 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4152 p_parent_id => l_plsql_id,
4153 p_binding_enabled_flag => FND_API.G_TRUE,
4154 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID,
4155 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4156 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
4157 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4158 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_UBOUND,
4159 x_arg_id => l_arg_id) THEN
4160 RAISE INIT_FAILED;
4161 END IF;
4162 IF NOT MAKE_ARG(p_arg_name => 'p_unit_object_owner',
4163 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4164 p_parent_id => l_plsql_id,
4165 p_binding_enabled_flag => FND_API.G_TRUE,
4166 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
4167 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4168 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4169 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4170 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_OBJECT_OWNER,
4171 x_arg_id => l_arg_id) THEN
4172 RAISE INIT_FAILED;
4173 END IF;
4174 IF NOT MAKE_ARG(p_arg_name => 'p_unit_object_name',
4175 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4176 p_parent_id => l_plsql_id,
4177 p_binding_enabled_flag => FND_API.G_TRUE,
4178 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
4179 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4180 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4181 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4182 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_OBJECT_NAME,
4183 x_arg_id => l_arg_id) THEN
4184 RAISE INIT_FAILED;
4185 END IF;
4186 IF NOT MAKE_ARG(p_arg_name => 'p_unit_workers_allowed',
4187 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4188 p_parent_id => l_plsql_id,
4189 p_binding_enabled_flag => FND_API.G_TRUE,
4190 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4191 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4192 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4193 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4194 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_WORKERS_ALLOWED,
4195 x_arg_id => l_arg_id) THEN
4196 RAISE INIT_FAILED;
4197 END IF;
4198 IF NOT MAKE_ARG(p_arg_name => 'p_unit_batch_size',
4199 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4200 p_parent_id => l_plsql_id,
4201 p_binding_enabled_flag => FND_API.G_TRUE,
4202 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4203 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4204 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4205 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4206 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_BATCH_SIZE,
4207 x_arg_id => l_arg_id) THEN
4208 RAISE INIT_FAILED;
4209 END IF;
4210 IF NOT MAKE_ARG(p_arg_name => 'p_plsql_id',
4211 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4212 p_parent_id => l_plsql_id,
4213 p_binding_enabled_flag => FND_API.G_TRUE,
4214 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4215 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4216 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4217 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4218 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_PLSQL_ID,
4219 x_arg_id => l_arg_id) THEN
4220 RAISE INIT_FAILED;
4221 END IF;
4222 IF NOT MAKE_ARG(p_arg_name => 'p_arg_id',
4223 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4224 p_parent_id => l_plsql_id,
4225 p_binding_enabled_flag => FND_API.G_TRUE,
4226 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4227 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4228 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4229 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4230 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ARGUMENT_ID,
4231 x_arg_id => l_arg_id) THEN
4232 RAISE INIT_FAILED;
4233 END IF;
4234 IF NOT MAKE_ARG(p_arg_name => 'p_workers_allowed',
4235 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4236 p_parent_id => l_plsql_id,
4237 p_binding_enabled_flag => FND_API.G_TRUE,
4238 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4239 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4240 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4241 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4242 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_WORKERS_ALLOWED,
4243 x_arg_id => l_arg_id) THEN
4244 RAISE INIT_FAILED;
4245 END IF;
4246 IF NOT MAKE_ARG(p_arg_name => 'p_batch_size',
4247 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4248 p_parent_id => l_plsql_id,
4249 p_binding_enabled_flag => FND_API.G_TRUE,
4250 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4251 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4252 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4253 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4254 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_BATCH_SIZE,
4255 x_arg_id => l_arg_id) THEN
4256 RAISE INIT_FAILED;
4257 END IF;
4258
4259 --make the return message var
4260 IF NOT MAKE_ARG(p_arg_name => 'x_return_msg',
4261 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4262 p_parent_id => l_plsql_id,
4263 p_binding_enabled_flag => FND_API.G_TRUE, --outputs must be bound also
4264 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
4265 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_WRITE,
4266 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4267 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_EXECUTION_CURSOR,
4268 x_arg_id => l_arg_id) THEN
4269 RAISE INIT_FAILED;
4270 END IF;
4271 --make the verdict output arg
4272 IF NOT MAKE_ARG(p_arg_name => 'x_verdict',
4273 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4274 p_parent_id => l_plsql_id,
4275 p_binding_enabled_flag => FND_API.G_TRUE, --outputs must be bound also
4276 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
4277 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_WRITE,
4278 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
4279 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_EXECUTION_CURSOR,
4280 x_arg_id => l_verdict_arg_id) THEN
4281 RAISE INIT_FAILED;
4282 END IF;
4283
4284 COMMIT;
4285 l_end := DBMS_UTILITY.GET_TIME;
4286 fnd_oam_debug.log(1, l_ctxt, 'Init Done - Duration: '||(l_end - l_start)/100|| ' seconds.');
4287 l_did_init := TRUE;
4288 END IF;
4289
4290 --do work
4291 l_retbool := EXECUTE_BUNDLE_WRAPPER(l_ctxt,
4292 p_run_id,
4293 p_bundle_id,
4294 l_worker_id,
4295 l_return_status,
4296 l_return_msg);
4297
4298 --make the guy who did init manage the sync, do cleanup and verify
4299 IF l_did_init THEN
4300 l_retbool_final := SYNC_ON_FINISH(p_run_id,
4301 l_retbool,
4302 p_num_workers,
4303 l_did_init);
4304 --validate the results
4305 l_retbool_final := l_retbool_final AND
4306 VALIDATE_TEST8(p_run_id,
4307 p_bundle_id,
4308 p_num_workers,
4309 l_verdict_arg_id);
4310
4311 --send acks to the other workers with the final status
4312 SEND_ACKS(p_run_id,
4313 l_retbool_final,
4314 p_num_workers);
4315 ELSE
4316 l_retbool_final := SYNC_ON_FINISH(p_run_id,
4317 l_retbool);
4318 END IF;
4319
4320 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
4321 IF l_retbool_final THEN
4322 x_verdict := FND_API.G_TRUE;
4323 ELSE
4324 x_verdict := FND_API.G_FALSE;
4325 END IF;
4326 EXCEPTION
4327 WHEN INIT_FAILED THEN
4328 --release the run lock on failure just in case
4329 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
4330 IF l_retval <> 0 THEN
4331 fnd_oam_debug.log(6, l_ctxt, 'Failed to release run lock: '||l_retval);
4332 END IF;
4333 ROLLBACK;
4334 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
4335 x_verdict := FND_API.G_FALSE;
4336 WHEN SYNC_FAILED THEN
4337 fnd_oam_debug.log(6, l_ctxt, 'Sync Failed');
4338 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
4339 x_verdict := FND_API.G_FALSE;
4340 WHEN OTHERS THEN
4341 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
4342 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
4343 x_verdict := FND_API.G_FALSE;
4344 END;
4345
4346 -- Public
4347 FUNCTION VALIDATE_TEST9(p_run_id IN NUMBER,
4348 p_bundle_id IN NUMBER,
4349 p_num_workers IN NUMBER,
4350 p_plsql_unit_id IN NUMBER,
4351 p_dml_unit_id IN NUMBER,
4352 p_test_tab_name IN VARCHAR2,
4353 p_test_tab_num_rows IN NUMBER,
4354 p_c2_run_arg_id IN NUMBER,
4355 p_c2_plsql_range_arg_id IN NUMBER,
4356 p_c2_dml_range_arg_id IN NUMBER,
4357 p_verdict_arg_id IN NUMBER)
4358 RETURN BOOLEAN
4359 IS
4360 l_testnum VARCHAR2(20) := 'TEST9';
4361 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_'||l_testnum;
4362
4363 l_count NUMBER;
4364 l_valid_value_flag VARCHAR2(3);
4365 l_canonical_value VARCHAR2(4000);
4366 l_c2_sum NUMBER;
4367 l_c2_plsql_target_sum NUMBER := 2*p_test_tab_num_rows;
4368 l_c2_dml_target_sum NUMBER := p_test_tab_num_rows*p_test_tab_num_rows + p_test_tab_num_rows;
4369 BEGIN
4370 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
4371
4372 --first validate the run/bundle/task/unit/plsql
4373 IF NOT VALIDATE_RUN_RECURSIVE(p_run_id,
4374 p_num_workers,
4375 p_num_bundles => 1,
4376 p_num_tasks => 1,
4377 p_num_units => 2) THEN
4378 RAISE VALIDATE_FAILED;
4379 END IF;
4380
4381 --validate the plsql unit
4382 IF NOT VALIDATE_UNIT_RECURSIVE(p_run_id,
4383 NULL,
4384 p_plsql_unit_id,
4385 p_num_workers,
4386 p_num_dmls => 0,
4387 p_num_dml_rows => 0,
4388 p_num_plsqls => 1) THEN
4389 RAISE VALIDATE_FAILED;
4390 END IF;
4391
4392 --validate the dml unit
4393 IF NOT VALIDATE_UNIT_RECURSIVE(p_run_id,
4394 NULL,
4395 p_dml_unit_id,
4396 p_num_workers,
4397 p_num_dmls => 1,
4398 p_num_dml_rows => p_test_tab_num_rows,
4399 p_num_plsqls => 0) THEN
4400 RAISE VALIDATE_FAILED;
4401 END IF;
4402
4403 --make sure the plsql's verdict came out ok
4404 BEGIN
4405 SELECT COUNT(ROWID)
4406 INTO l_count
4407 FROM fnd_oam_dscram_arg_values
4408 WHERE arg_id = p_verdict_arg_id
4409 AND (valid_value_flag IS NULL OR valid_value_flag <> FND_API.G_TRUE)
4410 AND (canonical_value IS NULL OR canonical_value <> FND_API.G_TRUE);
4411 EXCEPTION
4412 WHEN OTHERS THEN
4413 fnd_oam_debug.log(6, l_ctxt, 'Failed to query count of invalid verdicts: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
4414 RAISE VALIDATE_FAILED;
4415 END;
4416 IF l_count IS NULL OR l_count <> 0 THEN
4417 fnd_oam_debug.log(6, l_ctxt, 'Arg ID('||p_verdict_arg_id||'), found ('||l_count||') invalid verdicts');
4418 RAISE VALIDATE_FAILED;
4419 END IF;
4420
4421 --check the run arg's value
4422 fnd_oam_debug.log(1, l_ctxt, 'Checking intermediate run C2 arg value...');
4423 BEGIN
4424 SELECT valid_value_flag, canonical_value
4425 INTO l_valid_value_flag, l_canonical_value
4426 FROM fnd_oam_dscram_args_b
4427 WHERE arg_id = p_c2_run_arg_id;
4428 IF l_valid_value_flag IS NULL OR l_valid_value_flag <> FND_API.G_TRUE THEN
4429 fnd_oam_debug.log(6, l_ctxt, 'Valid value flag of run C2 intermediate sum is incorrect: '||l_valid_value_flag);
4430 RAISE VALIDATE_FAILED;
4431 END IF;
4432 l_c2_sum := FND_NUMBER.CANONICAL_TO_NUMBER(l_canonical_value);
4433 EXCEPTION
4434 WHEN VALIDATE_FAILED THEN
4435 RAISE;
4436 WHEN OTHERS THEN
4437 fnd_oam_debug.log(6, l_ctxt, 'Failed to query arg value for run C2 intermediate sum: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
4438 RAISE VALIDATE_FAILED;
4439 END;
4440 IF l_c2_sum IS NULL OR l_c2_sum <> p_test_tab_num_rows THEN
4441 fnd_oam_debug.log(6, l_ctxt, 'Run intermediate C2 Sum('||l_c2_sum||') not equal to target value('||p_test_tab_num_rows||')');
4442 RAISE VALIDATE_FAILED;
4443 END IF;
4444
4445 --check the plsql ranged c2 sum
4446 fnd_oam_debug.log(1, l_ctxt, 'Checking plsql ranged c2 sum...');
4447 IF NOT VALIDATE_TEST_TABLE_ARG_VALUES(TRUE,
4448 p_c2_plsql_range_arg_id,
4449 l_c2_plsql_target_sum,
4450 p_test_tab_name,
4451 p_test_tab_num_rows) THEN
4452 RAISE VALIDATE_FAILED;
4453 END IF;
4454
4455 --check the dml ranged c2 sum
4456 fnd_oam_debug.log(1, l_ctxt, 'Checking dml ranged c2 sum...');
4457 IF NOT VALIDATE_TEST_TABLE_ARG_VALUES(TRUE,
4458 p_c2_dml_range_arg_id,
4459 l_c2_dml_target_sum,
4460 p_test_tab_name,
4461 p_test_tab_num_rows) THEN
4462 RAISE VALIDATE_FAILED;
4463 END IF;
4464
4465 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
4466 RETURN TRUE;
4467 EXCEPTION
4468 WHEN VALIDATE_FAILED THEN
4469 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
4470 RETURN FALSE;
4471 WHEN OTHERS THEN
4472 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
4473 RETURN FALSE;
4474 END;
4475
4476 -- Public
4477 PROCEDURE TEST9_PROC1(p_using_splitting IN VARCHAR2,
4478 p_rowid_lbound IN ROWID,
4479 p_rowid_ubound IN ROWID,
4480 p_unit_object_owner IN VARCHAR2,
4481 p_unit_object_name IN VARCHAR2,
4482 x_verdict OUT NOCOPY VARCHAR2,
4483 x_return_msg OUT NOCOPY VARCHAR2)
4484 IS
4485 l_stmt VARCHAR2(4000);
4486 BEGIN
4487 --default to the failed status
4488 x_verdict := FND_API.G_FALSE;
4489 x_return_msg := '';
4490
4491 --make sure we're splitting
4492 IF p_using_splitting IS NULL or p_using_splitting <> FND_API.G_TRUE THEN
4493 x_return_msg := 'Procedure expects splitting to be enabled, has value: '||p_using_splitting;
4494 RETURN;
4495 END IF;
4496
4497 -- set up the simple update statement
4498 l_stmt := 'UPDATE '||p_unit_object_owner||'.'||p_unit_object_name||' SET C2 = C2 + 1 WHERE ROWID BETWEEN :1 AND :2';
4499 BEGIN
4500 EXECUTE IMMEDIATE l_stmt USING p_rowid_lbound, p_rowid_ubound;
4501 EXCEPTION
4502 WHEN OTHERS THEN
4503 x_return_msg := 'Exception during execute immediate: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
4504 RETURN;
4505 END;
4506
4507 --success
4508 x_verdict := FND_API.G_TRUE;
4509 EXCEPTION
4510 WHEN OTHERS THEN
4511 x_verdict := FND_API.G_FALSE;
4512 x_return_msg := SUBSTR('Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))',1,4000);
4513 END;
4514
4515 --Public
4516 PROCEDURE EXECUTE_TEST9(p_run_id IN NUMBER DEFAULT 1,
4517 p_bundle_id IN NUMBER DEFAULT 1,
4518 p_num_bundles IN NUMBER DEFAULT 1,
4519 p_num_workers IN NUMBER DEFAULT 1,
4520 x_verdict OUT NOCOPY VARCHAR2)
4521 IS
4522 l_testnum VARCHAR2(20) := 'TEST9';
4523 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_'||l_testnum;
4524
4525 l_test_table_name VARCHAR2(30) := B_TEST_TABLE_NAME_PREFIX||'9_TAB1';
4526 l_test_table_num_rows NUMBER := 8000; --make this small so we force the dependency issue for larger # of workers
4527 l_c2_range_arg_name VARCHAR2(60) := FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_C2_RANGE_SUM';
4528 l_c2_inter_arg_name VARCHAR2(60) := FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_C2_INTER_SUM';
4529 l_c2_run_arg_id NUMBER;
4530 l_c2_plsql_range_arg_id NUMBER;
4531 l_c2_plsql_inter_arg_id NUMBER;
4532 l_c2_dml_range_arg_id NUMBER;
4533 l_c2_dml_inter_arg_id NUMBER;
4534 l_worker_id NUMBER;
4535 l_task_id NUMBER;
4536 l_plsql_unit_id NUMBER;
4537 l_plsql_id NUMBER;
4538 l_dml_unit_id NUMBER;
4539 l_dml_id NUMBER;
4540 l_arg_id NUMBER;
4541 l_verdict_arg_id NUMBER;
4542
4543 l_did_init BOOLEAN := FALSE;
4544 l_lock_handle VARCHAR2(128);
4545 l_retval NUMBER;
4546 l_retbool BOOLEAN;
4547 l_retbool_final BOOLEAN;
4548 l_return_status VARCHAR2(6);
4549 l_return_msg VARCHAR2(2048);
4550 l_start NUMBER;
4551 l_end NUMBER;
4552 k NUMBER;
4553 j NUMBER;
4554 BEGIN
4555 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
4556 PRINT_TEST_ENTRY_STATE(l_ctxt, p_run_id, p_bundle_id, p_num_bundles, p_num_workers);
4557
4558 ROLLBACK;
4559 IF RUN_NEEDS_INIT(p_run_id,
4560 l_lock_handle) THEN
4561
4562 fnd_oam_debug.log(1, l_ctxt, 'Initializing Test...');
4563 l_start := DBMS_UTILITY.GET_TIME;
4564
4565 --create a test table to work on
4566 IF NOT MAKE_TEST_TABLE(l_test_table_name,
4567 l_test_table_num_rows) THEN
4568 RAISE INIT_FAILED;
4569 END IF;
4570
4571 --create a single run
4572 IF NOT MAKE_RUN(p_run_id,
4573 p_weight => 30,
4574 p_name => B_DIAG_RUN_NAME_PREFIX||l_testnum) THEN
4575 RAISE INIT_FAILED;
4576 END IF;
4577
4578 --create a context arg to receive the final c2 sum
4579 IF NOT MAKE_ARG(p_arg_name => l_c2_inter_arg_name,
4580 p_parent_id => p_run_id,
4581 p_allow_override_source => FND_API.G_TRUE,
4582 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4583 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4584 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE, --can't be always, otherwise it won't use unit1's cached val
4585 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE, --shouldn't be used beyond print_context
4586 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_RUN_ID,
4587 x_arg_id => l_c2_run_arg_id) THEN
4588 RAISE INIT_FAILED;
4589 END IF;
4590
4591 --solo bundle
4592 IF NOT MAKE_BUNDLE(p_run_id,
4593 p_bundle_id,
4594 p_weight => 30,
4595 p_min_par_weight => 15,
4596 p_batch_size => 1,
4597 p_workers_allowed => p_num_workers) THEN
4598 RAISE INIT_FAILED;
4599 END IF;
4600 --solo task
4601 IF NOT MAKE_TASK(p_bundle_id,
4602 p_weight => 30,
4603 x_task_id => l_task_id) THEN
4604 RAISE INIT_FAILED;
4605 END IF;
4606 --make a plsql set unit
4607 IF NOT MAKE_UNIT(l_task_id,
4608 p_unit_type => FND_OAM_DSCRAM_UTILS_PKG.G_UNIT_TYPE_PLSQL_SET,
4609 p_phase => 1,
4610 p_weight => 15,
4611 p_unit_obj_owner => B_TEST_TABLE_OWNER,
4612 p_unit_obj_name => l_test_table_name,
4613 x_unit_id => l_plsql_unit_id) THEN
4614 RAISE INIT_FAILED;
4615 END IF;
4616 --make the plsql
4617 IF NOT MAKE_PLSQL(l_plsql_unit_id,
4618 p_priority => 1,
4619 p_weight => 25,
4620 p_plsql_text => 'FND_OAM_DSCRAM_DIAG_PKG.TEST9_PROC1(:p_using_splitting,
4621 :p_rowid_lbound,
4622 :p_rowid_ubound,
4623 :p_unit_object_owner,
4624 :p_unit_object_name,
4625 :x_verdict,
4626 :x_return_msg)',
4627 x_plsql_id => l_plsql_id) THEN
4628 RAISE INIT_FAILED;
4629 END IF;
4630 IF NOT MAKE_ARG(p_arg_name => 'p_using_splitting',
4631 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4632 p_parent_id => l_plsql_id,
4633 p_binding_enabled_flag => FND_API.G_TRUE,
4634 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
4635 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4636 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4637 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4638 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_USING_SPLITTING,
4639 x_arg_id => l_arg_id) THEN
4640 RAISE INIT_FAILED;
4641 END IF;
4642 IF NOT MAKE_ARG(p_arg_name => 'p_rowid_lbound',
4643 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4644 p_parent_id => l_plsql_id,
4645 p_binding_enabled_flag => FND_API.G_TRUE,
4646 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID,
4647 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4648 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
4649 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4650 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_LBOUND,
4651 x_arg_id => l_arg_id) THEN
4652 RAISE INIT_FAILED;
4653 END IF;
4654 IF NOT MAKE_ARG(p_arg_name => 'p_rowid_ubound',
4655 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4656 p_parent_id => l_plsql_id,
4657 p_binding_enabled_flag => FND_API.G_TRUE,
4658 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID,
4659 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4660 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
4661 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4662 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_UBOUND,
4663 x_arg_id => l_arg_id) THEN
4664 RAISE INIT_FAILED;
4665 END IF;
4666 IF NOT MAKE_ARG(p_arg_name => 'p_unit_object_owner',
4667 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4668 p_parent_id => l_plsql_id,
4669 p_binding_enabled_flag => FND_API.G_TRUE,
4670 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
4671 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4672 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4673 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4674 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_OBJECT_OWNER,
4675 x_arg_id => l_arg_id) THEN
4676 RAISE INIT_FAILED;
4677 END IF;
4678 IF NOT MAKE_ARG(p_arg_name => 'p_unit_object_name',
4679 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4680 p_parent_id => l_plsql_id,
4681 p_binding_enabled_flag => FND_API.G_TRUE,
4682 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
4683 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4684 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4685 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
4686 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_OBJECT_NAME,
4687 x_arg_id => l_arg_id) THEN
4688 RAISE INIT_FAILED;
4689 END IF;
4690
4691 --make the return message var
4692 IF NOT MAKE_ARG(p_arg_name => 'x_return_msg',
4693 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4694 p_parent_id => l_plsql_id,
4695 p_binding_enabled_flag => FND_API.G_TRUE, --outputs must be bound also
4696 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
4697 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_WRITE,
4698 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
4699 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_EXECUTION_CURSOR,
4700 x_arg_id => l_arg_id) THEN
4701 RAISE INIT_FAILED;
4702 END IF;
4703 --make the verdict output arg
4704 IF NOT MAKE_ARG(p_arg_name => 'x_verdict',
4705 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4706 p_parent_id => l_plsql_id,
4707 p_binding_enabled_flag => FND_API.G_TRUE, --outputs must be bound also
4708 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
4709 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_WRITE,
4710 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
4711 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_EXECUTION_CURSOR,
4712 x_arg_id => l_verdict_arg_id) THEN
4713 RAISE INIT_FAILED;
4714 END IF;
4715
4716 --make an output arg for the plsql to run at the end of each range to get the c2 sum
4717 IF NOT MAKE_ARG(p_arg_name => l_c2_range_arg_name,
4718 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4719 p_parent_id => l_plsql_id,
4720 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4721 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_WRITE,
4722 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
4723 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL_RESTRICTABLE,
4724 p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
4725 x_arg_id => l_c2_plsql_range_arg_id) THEN
4726 RAISE INIT_FAILED;
4727 END IF;
4728
4729 --make an output arg for the plsql to run at the end of the splitting to get the final c2 sum on all rows
4730 IF NOT MAKE_ARG(p_arg_name => l_c2_inter_arg_name,
4731 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
4732 p_parent_id => l_plsql_id,
4733 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4734 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4735 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4736 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL,
4737 p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
4738 x_arg_id => l_c2_plsql_inter_arg_id) THEN
4739 RAISE INIT_FAILED;
4740 END IF;
4741
4742 --make the dml unit
4743 IF NOT MAKE_UNIT(l_task_id,
4744 p_unit_type => FND_OAM_DSCRAM_UTILS_PKG.G_UNIT_TYPE_DML_SET,
4745 p_phase => NULL,
4746 p_weight => 15,
4747 p_unit_obj_owner => B_TEST_TABLE_OWNER,
4748 p_unit_obj_name => l_test_table_name,
4749 x_unit_id => l_dml_unit_id) THEN
4750 RAISE INIT_FAILED;
4751 END IF;
4752 --make a simple dml
4753 IF NOT MAKE_DML(l_dml_unit_id,
4754 p_weight => 15,
4755 p_dml_stmt => 'UPDATE '||l_test_table_name||' SET C2 = C2 + :'|| l_c2_inter_arg_name,
4756 p_where_clause => NULL,
4757 x_dml_id => l_dml_id) THEN
4758 RAISE INIT_FAILED;
4759 END IF;
4760 --make an input arg for the dml to fetch the sum from the first unit
4761 IF NOT MAKE_ARG(p_arg_name => l_c2_inter_arg_name,
4762 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
4763 p_parent_id => l_dml_id,
4764 p_binding_enabled_flag => FND_API.G_TRUE,
4765 p_allow_override_source => FND_API.G_TRUE,
4766 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4767 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
4768 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
4769 p_src_type => NULL,
4770 x_arg_id => l_c2_dml_inter_arg_id) THEN
4771 RAISE INIT_FAILED;
4772 END IF;
4773 --make a ranged final arg_id
4774 IF NOT MAKE_ARG(p_arg_name => l_c2_range_arg_name,
4775 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
4776 p_parent_id => l_dml_id,
4777 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
4778 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_WRITE,
4779 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
4780 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL_RESTRICTABLE,
4781 p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
4782 x_arg_id => l_c2_dml_range_arg_id) THEN
4783 RAISE INIT_FAILED;
4784 END IF;
4785
4786 COMMIT;
4787 l_end := DBMS_UTILITY.GET_TIME;
4788 fnd_oam_debug.log(1, l_ctxt, 'Init Done - Duration: '||(l_end - l_start)/100|| ' seconds.');
4789 l_did_init := TRUE;
4790 END IF;
4791
4792 --do work
4793 l_retbool := EXECUTE_BUNDLE_WRAPPER(l_ctxt,
4794 p_run_id,
4795 p_bundle_id,
4796 l_worker_id,
4797 l_return_status,
4798 l_return_msg);
4799
4800 --make the guy who did init manage the sync, do cleanup and verify
4801 IF l_did_init THEN
4802 l_retbool_final := SYNC_ON_FINISH(p_run_id,
4803 l_retbool,
4804 p_num_workers,
4805 l_did_init);
4806 --validate the results
4807 l_retbool_final := l_retbool_final AND
4808 VALIDATE_TEST9(p_run_id,
4809 p_bundle_id,
4810 p_num_workers,
4811 l_plsql_unit_id,
4812 l_dml_unit_id,
4813 l_test_table_name,
4814 l_test_table_num_rows,
4815 l_c2_run_arg_id,
4816 l_c2_plsql_range_arg_id,
4817 l_c2_dml_range_arg_id,
4818 l_verdict_arg_id);
4819
4820 --send acks to the other workers with the final status
4821 SEND_ACKS(p_run_id,
4822 l_retbool_final,
4823 p_num_workers);
4824 ELSE
4825 l_retbool_final := SYNC_ON_FINISH(p_run_id,
4826 l_retbool);
4827 END IF;
4828
4829 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
4830 IF l_retbool_final THEN
4831 x_verdict := FND_API.G_TRUE;
4832 ELSE
4833 x_verdict := FND_API.G_FALSE;
4834 END IF;
4835 EXCEPTION
4836 WHEN INIT_FAILED THEN
4837 --release the run lock on failure just in case
4838 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
4839 IF l_retval <> 0 THEN
4840 fnd_oam_debug.log(6, l_ctxt, 'Failed to release run lock: '||l_retval);
4841 END IF;
4842 ROLLBACK;
4843 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
4844 x_verdict := FND_API.G_FALSE;
4845 WHEN SYNC_FAILED THEN
4846 fnd_oam_debug.log(6, l_ctxt, 'Sync Failed');
4847 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
4848 x_verdict := FND_API.G_FALSE;
4849 WHEN OTHERS THEN
4850 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
4851 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
4852 x_verdict := FND_API.G_FALSE;
4853 END;
4854
4855 -- Public
4856 FUNCTION VALIDATE_TEST10(p_run_id IN NUMBER,
4857 p_bundle_id IN NUMBER,
4858 p_num_workers IN NUMBER,
4859 p_child1_unit_id IN NUMBER,
4860 p_child2_unit_id IN NUMBER,
4861 p_child3_unit_id IN NUMBER,
4862 p_test_tab_name IN VARCHAR2,
4863 p_test_tab_num_rows IN NUMBER,
4864 p_c2_final_arg_id IN NUMBER)
4865 RETURN BOOLEAN
4866 IS
4867 l_testnum VARCHAR2(20) := 'TEST10';
4868 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_'||l_testnum;
4869
4870 l_count NUMBER;
4871 l_valid_value_flag VARCHAR2(3);
4872 l_canonical_value VARCHAR2(4000);
4873 l_c2_sum NUMBER;
4874 l_c2_target_sum NUMBER := 51208*p_test_tab_num_rows;
4875 BEGIN
4876 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
4877
4878 --first validate the run/bundle/task/unit/plsql
4879 IF NOT VALIDATE_RUN_RECURSIVE(p_run_id,
4880 p_num_workers,
4881 p_num_bundles => 1,
4882 p_num_tasks => 1,
4883 p_num_units => 1) THEN
4884 RAISE VALIDATE_FAILED;
4885 END IF;
4886
4887 --validate the child1 unit
4888 IF NOT VALIDATE_UNIT_RECURSIVE(p_run_id,
4889 NULL,
4890 p_child1_unit_id,
4891 p_num_workers,
4892 p_num_dmls => 0,
4893 p_num_dml_rows => 0,
4894 p_num_plsqls => 2,
4895 p_unit_status => FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_NO_STATUS) THEN
4896 RAISE VALIDATE_FAILED;
4897 END IF;
4898
4899 --validate the child2 unit
4900 IF NOT VALIDATE_UNIT_RECURSIVE(p_run_id,
4901 NULL,
4902 p_child2_unit_id,
4903 p_num_workers,
4904 p_num_dmls => 2,
4905 p_num_dml_rows => p_test_tab_num_rows,
4906 p_num_plsqls => 0,
4907 p_unit_status => FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_NO_STATUS) THEN
4908 RAISE VALIDATE_FAILED;
4909 END IF;
4910
4911 --validate the child3 unit
4912 IF NOT VALIDATE_UNIT_RECURSIVE(p_run_id,
4913 NULL,
4914 p_child3_unit_id,
4915 p_num_workers,
4916 p_num_dmls => 0,
4917 p_num_dml_rows => 0,
4918 p_num_plsqls => 2,
4919 p_unit_status => FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_NO_STATUS) THEN
4920 RAISE VALIDATE_FAILED;
4921 END IF;
4922
4923 --check the plsql ranged c2 sum
4924 fnd_oam_debug.log(1, l_ctxt, 'Checking final ranged c2 sum...');
4925 IF NOT VALIDATE_TEST_TABLE_ARG_VALUES(TRUE,
4926 p_c2_final_arg_id,
4927 l_c2_target_sum,
4928 p_test_tab_name,
4929 p_test_tab_num_rows) THEN
4930 RAISE VALIDATE_FAILED;
4931 END IF;
4932
4933 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
4934 RETURN TRUE;
4935 EXCEPTION
4936 WHEN VALIDATE_FAILED THEN
4937 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
4938 RETURN FALSE;
4939 WHEN OTHERS THEN
4940 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
4941 RETURN FALSE;
4942 END;
4943
4944 -- Public
4945 PROCEDURE TEST10_PROC1_1(p_using_splitting IN VARCHAR2,
4946 p_rowid_lbound IN ROWID,
4947 p_rowid_ubound IN ROWID,
4948 p_unit_object_owner IN VARCHAR2,
4949 p_unit_object_name IN VARCHAR2)
4950 IS
4951 l_stmt VARCHAR2(4000);
4952 BEGIN
4953 --make sure we're splitting
4954 IF p_using_splitting IS NULL or p_using_splitting <> FND_API.G_TRUE THEN
4955 RAISE VALUE_ERROR;
4956 END IF;
4957
4958 -- set up the simple update statement
4959 l_stmt := 'UPDATE '||p_unit_object_owner||'.'||p_unit_object_name||' SET C2 = 7*C2 + 1 WHERE ROWID BETWEEN :1 AND :2';
4960 EXECUTE IMMEDIATE l_stmt USING p_rowid_lbound, p_rowid_ubound;
4961 END;
4962
4963 -- Public
4964 PROCEDURE TEST10_PROC1_2(p_using_splitting IN VARCHAR2,
4965 p_rowid_lbound IN ROWID,
4966 p_rowid_ubound IN ROWID,
4967 p_unit_object_owner IN VARCHAR2,
4968 p_unit_object_name IN VARCHAR2)
4969 IS
4970 l_stmt VARCHAR2(4000);
4971 BEGIN
4972 --make sure we're splitting
4973 IF p_using_splitting IS NULL or p_using_splitting <> FND_API.G_TRUE THEN
4974 RAISE VALUE_ERROR;
4975 END IF;
4976
4977 -- set up the simple update statement
4978 l_stmt := 'UPDATE '||p_unit_object_owner||'.'||p_unit_object_name||' SET C2 = 11*C2 + 1 WHERE ROWID BETWEEN :1 AND :2';
4979 EXECUTE IMMEDIATE l_stmt USING p_rowid_lbound, p_rowid_ubound;
4980 END;
4981
4982 -- Public
4983 FUNCTION TEST10_FUNC2_2(p_using_splitting IN VARCHAR2,
4984 p_rowid_lbound IN ROWID,
4985 p_rowid_ubound IN ROWID,
4986 p_unit_object_owner IN VARCHAR2,
4987 p_unit_object_name IN VARCHAR2)
4988 RETURN NUMBER
4989 IS
4990 l_stmt VARCHAR2(4000);
4991 l_count NUMBER;
4992 BEGIN
4993 --make sure we're splitting
4994 IF p_using_splitting IS NULL or p_using_splitting <> FND_API.G_TRUE THEN
4995 RAISE VALUE_ERROR;
4996 END IF;
4997
4998 -- set up the simple update statement
4999 l_stmt := 'SELECT COUNT(ROWID) FROM '||p_unit_object_owner||'.'||p_unit_object_name||' WHERE ROWID BETWEEN :1 AND :2';
5000 EXECUTE IMMEDIATE l_stmt INTO l_count USING p_rowid_lbound, p_rowid_ubound;
5001 RETURN l_count;
5002 END;
5003
5004 -- Public
5005 PROCEDURE TEST10_PROC3_1(p_using_splitting IN VARCHAR2,
5006 p_rowid_lbound IN ROWID,
5007 p_rowid_ubound IN ROWID,
5008 p_unit_object_owner IN VARCHAR2,
5009 p_unit_object_name IN VARCHAR2)
5010 IS
5011 l_stmt VARCHAR2(4000);
5012 BEGIN
5013 --make sure we're splitting
5014 IF p_using_splitting IS NULL or p_using_splitting <> FND_API.G_TRUE THEN
5015 RAISE VALUE_ERROR;
5016 END IF;
5017
5018 -- set up the simple update statement
5019 l_stmt := 'UPDATE '||p_unit_object_owner||'.'||p_unit_object_name||' SET C2 = 3*C2 + 1 + :1 WHERE ROWID BETWEEN :2 AND :3';
5020 EXECUTE IMMEDIATE l_stmt USING TEST10_FUNC2_2(p_using_splitting, p_rowid_lbound, p_rowid_ubound, p_unit_object_owner, p_unit_object_name), p_rowid_lbound, p_rowid_ubound;
5021 END;
5022
5023 -- Public
5024 PROCEDURE TEST10_PROC3_2(p_using_splitting IN VARCHAR2,
5025 p_rowid_lbound IN ROWID,
5026 p_rowid_ubound IN ROWID,
5027 p_unit_object_owner IN VARCHAR2,
5028 p_unit_object_name IN VARCHAR2)
5029 IS
5030 l_stmt VARCHAR2(4000);
5031 BEGIN
5032 --make sure we're splitting
5033 IF p_using_splitting IS NULL or p_using_splitting <> FND_API.G_TRUE THEN
5034 RAISE VALUE_ERROR;
5035 END IF;
5036
5037 -- set up the simple update statement
5038 l_stmt := 'UPDATE '||p_unit_object_owner||'.'||p_unit_object_name||' SET C2 = 2*C2 + 1 WHERE ROWID BETWEEN :1 AND :2';
5039 EXECUTE IMMEDIATE l_stmt USING p_rowid_lbound, p_rowid_ubound;
5040 END;
5041
5042 --Public
5043 PROCEDURE EXECUTE_TEST10(p_run_id IN NUMBER DEFAULT 1,
5044 p_bundle_id IN NUMBER DEFAULT 1,
5045 p_num_bundles IN NUMBER DEFAULT 1,
5046 p_num_workers IN NUMBER DEFAULT 1,
5047 x_verdict OUT NOCOPY VARCHAR2)
5048 IS
5049 l_testnum VARCHAR2(20) := 'TEST10';
5050 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_'||l_testnum;
5051
5052 l_test_table_name VARCHAR2(30) := B_TEST_TABLE_NAME_PREFIX||'10_TAB1';
5053 l_test_table_num_rows NUMBER := 4800; --make this small since we have many operations
5054 l_c2_final_arg_name VARCHAR2(60) := FND_OAM_DSCRAM_UTILS_PKG.G_ARG_INTERNAL_PREFIX||l_testnum||'_C2_FINAL_SUM';
5055 l_worker_id NUMBER;
5056 l_task_id NUMBER;
5057 l_parent_unit_id NUMBER;
5058 l_child1_unit_id NUMBER;
5059 l_child2_unit_id NUMBER;
5060 l_child3_unit_id NUMBER;
5061 l_plsql_id NUMBER;
5062 l_dml_id NUMBER;
5063 l_c2_final_arg_id NUMBER;
5064 l_arg_id NUMBER;
5065
5066 l_did_init BOOLEAN := FALSE;
5067 l_lock_handle VARCHAR2(128);
5068 l_retval NUMBER;
5069 l_retbool BOOLEAN;
5070 l_retbool_final BOOLEAN;
5071 l_return_status VARCHAR2(6);
5072 l_return_msg VARCHAR2(2048);
5073 l_start NUMBER;
5074 l_end NUMBER;
5075 k NUMBER;
5076 j NUMBER;
5077 BEGIN
5078 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
5079 PRINT_TEST_ENTRY_STATE(l_ctxt, p_run_id, p_bundle_id, p_num_bundles, p_num_workers);
5080
5081 ROLLBACK;
5082 IF RUN_NEEDS_INIT(p_run_id,
5083 l_lock_handle) THEN
5084
5085 fnd_oam_debug.log(1, l_ctxt, 'Initializing Test...');
5086 l_start := DBMS_UTILITY.GET_TIME;
5087
5088 --create a test table to work on
5089 IF NOT MAKE_TEST_TABLE(l_test_table_name,
5090 l_test_table_num_rows) THEN
5091 RAISE INIT_FAILED;
5092 END IF;
5093
5094 --create a single run
5095 IF NOT MAKE_RUN(p_run_id,
5096 p_weight => NULL,
5097 p_name => B_DIAG_RUN_NAME_PREFIX||l_testnum) THEN
5098 RAISE INIT_FAILED;
5099 END IF;
5100 --solo bundle
5101 IF NOT MAKE_BUNDLE(p_run_id,
5102 p_bundle_id,
5103 p_weight => NULL,
5104 p_min_par_weight => 456,
5105 p_batch_size => 1,
5106 p_workers_allowed => p_num_workers) THEN
5107 RAISE INIT_FAILED;
5108 END IF;
5109 --solo task
5110 IF NOT MAKE_TASK(p_bundle_id,
5111 p_weight => NULL,
5112 x_task_id => l_task_id) THEN
5113 RAISE INIT_FAILED;
5114 END IF;
5115 --make a concurrent group meta-unit
5116 IF NOT MAKE_UNIT(l_task_id,
5117 p_unit_type => FND_OAM_DSCRAM_UTILS_PKG.G_UNIT_TYPE_CONC_GROUP,
5118 p_unit_obj_owner => B_TEST_TABLE_OWNER,
5119 p_unit_obj_name => l_test_table_name,
5120 x_unit_id => l_parent_unit_id) THEN
5121 RAISE INIT_FAILED;
5122 END IF;
5123
5124 --make child unit 1
5125 IF NOT MAKE_UNIT(l_task_id,
5126 p_unit_type => FND_OAM_DSCRAM_UTILS_PKG.G_UNIT_TYPE_PLSQL_SET,
5127 p_conc_unit_id => l_parent_unit_id,
5128 p_status => FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_NO_STATUS,
5129 p_priority => NULL,
5130 p_weight => 1000,
5131 p_unit_obj_owner => B_TEST_TABLE_OWNER,
5132 p_unit_obj_name => l_test_table_name,
5133 x_unit_id => l_child1_unit_id) THEN
5134 RAISE INIT_FAILED;
5135 END IF;
5136 --make the 1.1 plsql
5137 IF NOT MAKE_PLSQL(l_child1_unit_id,
5138 p_priority => 2,
5139 p_weight => 11,
5140 p_plsql_text => 'FND_OAM_DSCRAM_DIAG_PKG.TEST10_PROC1_1(:p_using_splitting,
5141 :p_rowid_lbound,
5142 :p_rowid_ubound,
5143 :p_unit_object_owner,
5144 :p_unit_object_name)',
5145 x_plsql_id => l_plsql_id) THEN
5146 RAISE INIT_FAILED;
5147 END IF;
5148 IF NOT MAKE_ARG(p_arg_name => 'p_using_splitting',
5149 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5150 p_parent_id => l_plsql_id,
5151 p_binding_enabled_flag => FND_API.G_TRUE,
5152 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
5153 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5154 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
5155 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5156 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_USING_SPLITTING,
5157 x_arg_id => l_arg_id) THEN
5158 RAISE INIT_FAILED;
5159 END IF;
5160 IF NOT MAKE_ARG(p_arg_name => 'p_rowid_lbound',
5161 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5162 p_parent_id => l_plsql_id,
5163 p_binding_enabled_flag => FND_API.G_TRUE,
5164 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID,
5165 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5166 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
5167 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5168 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_LBOUND,
5169 x_arg_id => l_arg_id) THEN
5170 RAISE INIT_FAILED;
5171 END IF;
5172 IF NOT MAKE_ARG(p_arg_name => 'p_rowid_ubound',
5173 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5174 p_parent_id => l_plsql_id,
5175 p_binding_enabled_flag => FND_API.G_TRUE,
5176 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID,
5177 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5178 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
5179 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5180 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_UBOUND,
5181 x_arg_id => l_arg_id) THEN
5182 RAISE INIT_FAILED;
5183 END IF;
5184 IF NOT MAKE_ARG(p_arg_name => 'p_unit_object_owner',
5185 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5186 p_parent_id => l_plsql_id,
5187 p_binding_enabled_flag => FND_API.G_TRUE,
5188 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
5189 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5190 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
5191 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5192 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_OBJECT_OWNER,
5193 x_arg_id => l_arg_id) THEN
5194 RAISE INIT_FAILED;
5195 END IF;
5196 IF NOT MAKE_ARG(p_arg_name => 'p_unit_object_name',
5197 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5198 p_parent_id => l_plsql_id,
5199 p_binding_enabled_flag => FND_API.G_TRUE,
5200 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
5201 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5202 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
5203 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5204 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_OBJECT_NAME,
5205 x_arg_id => l_arg_id) THEN
5206 RAISE INIT_FAILED;
5207 END IF;
5208 --make the 1.2 plsql
5209 IF NOT MAKE_PLSQL(l_child1_unit_id,
5210 p_priority => NULL,
5211 p_weight => 112,
5212 p_plsql_text => 'FND_OAM_DSCRAM_DIAG_PKG.TEST10_PROC1_2(:p_using_splitting,
5213 :p_rowid_lbound,
5214 :p_rowid_ubound,
5215 :p_unit_object_owner,
5216 :p_unit_object_name)',
5217 x_plsql_id => l_plsql_id) THEN
5218 RAISE INIT_FAILED;
5219 END IF;
5220 IF NOT MAKE_ARG(p_arg_name => 'p_using_splitting',
5221 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5222 p_parent_id => l_plsql_id,
5223 p_binding_enabled_flag => FND_API.G_TRUE,
5224 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
5225 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5226 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
5227 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5228 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_USING_SPLITTING,
5229 x_arg_id => l_arg_id) THEN
5230 RAISE INIT_FAILED;
5231 END IF;
5232 IF NOT MAKE_ARG(p_arg_name => 'p_rowid_lbound',
5233 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5234 p_parent_id => l_plsql_id,
5235 p_binding_enabled_flag => FND_API.G_TRUE,
5236 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID,
5237 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5238 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
5239 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5240 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_LBOUND,
5241 x_arg_id => l_arg_id) THEN
5242 RAISE INIT_FAILED;
5243 END IF;
5244 IF NOT MAKE_ARG(p_arg_name => 'p_rowid_ubound',
5245 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5246 p_parent_id => l_plsql_id,
5247 p_binding_enabled_flag => FND_API.G_TRUE,
5248 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID,
5249 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5250 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
5251 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5252 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_UBOUND,
5253 x_arg_id => l_arg_id) THEN
5254 RAISE INIT_FAILED;
5255 END IF;
5256 IF NOT MAKE_ARG(p_arg_name => 'p_unit_object_owner',
5257 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5258 p_parent_id => l_plsql_id,
5259 p_binding_enabled_flag => FND_API.G_TRUE,
5260 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
5261 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5262 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
5263 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5264 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_OBJECT_OWNER,
5265 x_arg_id => l_arg_id) THEN
5266 RAISE INIT_FAILED;
5267 END IF;
5268 IF NOT MAKE_ARG(p_arg_name => 'p_unit_object_name',
5269 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5270 p_parent_id => l_plsql_id,
5271 p_binding_enabled_flag => FND_API.G_TRUE,
5272 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
5273 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5274 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
5275 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5276 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_OBJECT_NAME,
5277 x_arg_id => l_arg_id) THEN
5278 RAISE INIT_FAILED;
5279 END IF;
5280
5281 --make child unit 2
5282 IF NOT MAKE_UNIT(l_task_id,
5283 p_unit_type => FND_OAM_DSCRAM_UTILS_PKG.G_UNIT_TYPE_DML_SET,
5284 p_conc_unit_id => l_parent_unit_id,
5285 p_status => FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_NO_STATUS,
5286 p_priority => NULL,
5287 p_weight => 200,
5288 p_unit_obj_owner => B_TEST_TABLE_OWNER,
5289 p_unit_obj_name => l_test_table_name,
5290 x_unit_id => l_child2_unit_id) THEN
5291 RAISE INIT_FAILED;
5292 END IF;
5293
5294 --make dml 2.1
5295 IF NOT MAKE_DML(l_child2_unit_id,
5296 p_weight => 21,
5297 p_dml_stmt => 'UPDATE '||l_test_table_name||' SET C2 = 13*C2 + 1',
5298 p_where_clause => NULL,
5299 x_dml_id => l_dml_id) THEN
5300 RAISE INIT_FAILED;
5301 END IF;
5302 --dml 2.1 will be scheduled last, affix a range sum to check our sequence of updates
5303 IF NOT MAKE_ARG(p_arg_name => l_c2_final_arg_name,
5304 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
5305 p_parent_id => l_dml_id,
5306 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER,
5307 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_WRITE,
5308 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
5309 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL_RESTRICTABLE,
5310 p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
5311 x_arg_id => l_c2_final_arg_id) THEN
5312 RAISE INIT_FAILED;
5313 END IF;
5314
5315 --make dml 2.2
5316 --can't use function TEST10_FUNC2_2 in the math because it throws the error: Code(-4091),
5317 --Message("ORA-04091: table APPS.FND_OAM_DSCRAM_TT_10_TAB1 is mutating, trigger/function may not see it"))"
5318 IF NOT MAKE_DML(l_child2_unit_id,
5319 p_priority => 1,
5320 p_weight => 22,
5321 p_dml_stmt => 'UPDATE '||l_test_table_name||' SET C2 = 5*(C2 - (SELECT COUNT(ROWID) FROM '||l_test_table_name||' WHERE ROWID BETWEEN :p_rowid_lbound AND :p_rowid_ubound)) + 1',
5322 p_where_clause => NULL,
5323 x_dml_id => l_dml_id) THEN
5324 RAISE INIT_FAILED;
5325 END IF;
5326 IF NOT MAKE_ARG(p_arg_name => 'p_rowid_lbound',
5327 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
5328 p_parent_id => l_dml_id,
5329 p_binding_enabled_flag => FND_API.G_TRUE,
5330 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID,
5331 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5332 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
5333 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5334 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_LBOUND,
5335 x_arg_id => l_arg_id) THEN
5336 RAISE INIT_FAILED;
5337 END IF;
5338 IF NOT MAKE_ARG(p_arg_name => 'p_rowid_ubound',
5339 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_DML,
5340 p_parent_id => l_dml_id,
5341 p_binding_enabled_flag => FND_API.G_TRUE,
5342 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID,
5343 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5344 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
5345 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5346 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_UBOUND,
5347 x_arg_id => l_arg_id) THEN
5348 RAISE INIT_FAILED;
5349 END IF;
5350
5351 --make child unit 3
5352 IF NOT MAKE_UNIT(l_task_id,
5353 p_unit_type => FND_OAM_DSCRAM_UTILS_PKG.G_UNIT_TYPE_PLSQL_SET,
5354 p_conc_unit_id => l_parent_unit_id,
5355 p_status => FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_NO_STATUS,
5356 p_priority => 1,
5357 p_weight => 300,
5358 p_unit_obj_owner => B_TEST_TABLE_OWNER,
5359 p_unit_obj_name => l_test_table_name,
5360 x_unit_id => l_child3_unit_id) THEN
5361 RAISE INIT_FAILED;
5362 END IF;
5363 --make the 3.1 plsql
5364 IF NOT MAKE_PLSQL(l_child3_unit_id,
5365 p_priority => NULL,
5366 p_weight => 31,
5367 p_plsql_text => 'FND_OAM_DSCRAM_DIAG_PKG.TEST10_PROC3_1(:p_using_splitting,
5368 :p_rowid_lbound,
5369 :p_rowid_ubound,
5370 :p_unit_object_owner,
5371 :p_unit_object_name)',
5372 x_plsql_id => l_plsql_id) THEN
5373 RAISE INIT_FAILED;
5374 END IF;
5375 IF NOT MAKE_ARG(p_arg_name => 'p_using_splitting',
5376 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5377 p_parent_id => l_plsql_id,
5378 p_binding_enabled_flag => FND_API.G_TRUE,
5379 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
5380 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5381 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
5382 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5383 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_USING_SPLITTING,
5384 x_arg_id => l_arg_id) THEN
5385 RAISE INIT_FAILED;
5386 END IF;
5387 IF NOT MAKE_ARG(p_arg_name => 'p_rowid_lbound',
5388 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5389 p_parent_id => l_plsql_id,
5390 p_binding_enabled_flag => FND_API.G_TRUE,
5391 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID,
5392 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5393 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
5394 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5395 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_LBOUND,
5396 x_arg_id => l_arg_id) THEN
5397 RAISE INIT_FAILED;
5398 END IF;
5399 IF NOT MAKE_ARG(p_arg_name => 'p_rowid_ubound',
5400 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5401 p_parent_id => l_plsql_id,
5402 p_binding_enabled_flag => FND_API.G_TRUE,
5403 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID,
5404 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5405 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
5406 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5407 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_UBOUND,
5408 x_arg_id => l_arg_id) THEN
5409 RAISE INIT_FAILED;
5410 END IF;
5411 IF NOT MAKE_ARG(p_arg_name => 'p_unit_object_owner',
5412 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5413 p_parent_id => l_plsql_id,
5414 p_binding_enabled_flag => FND_API.G_TRUE,
5415 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
5416 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5417 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
5418 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5419 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_OBJECT_OWNER,
5420 x_arg_id => l_arg_id) THEN
5421 RAISE INIT_FAILED;
5422 END IF;
5423 IF NOT MAKE_ARG(p_arg_name => 'p_unit_object_name',
5424 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5425 p_parent_id => l_plsql_id,
5426 p_binding_enabled_flag => FND_API.G_TRUE,
5427 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
5428 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5429 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
5430 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5431 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_OBJECT_NAME,
5432 x_arg_id => l_arg_id) THEN
5433 RAISE INIT_FAILED;
5434 END IF;
5435 --make the 3.2 plsql
5436 IF NOT MAKE_PLSQL(l_child3_unit_id,
5437 p_priority => 1,
5438 p_weight => 32,
5439 p_plsql_text => 'FND_OAM_DSCRAM_DIAG_PKG.TEST10_PROC3_2(:p_using_splitting,
5440 :p_rowid_lbound,
5441 :p_rowid_ubound,
5442 :p_unit_object_owner,
5443 :p_unit_object_name)',
5444 x_plsql_id => l_plsql_id) THEN
5445 RAISE INIT_FAILED;
5446 END IF;
5447 IF NOT MAKE_ARG(p_arg_name => 'p_using_splitting',
5448 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5449 p_parent_id => l_plsql_id,
5450 p_binding_enabled_flag => FND_API.G_TRUE,
5451 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
5452 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5453 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
5454 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5455 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_USING_SPLITTING,
5456 x_arg_id => l_arg_id) THEN
5457 RAISE INIT_FAILED;
5458 END IF;
5459 IF NOT MAKE_ARG(p_arg_name => 'p_rowid_lbound',
5460 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5461 p_parent_id => l_plsql_id,
5462 p_binding_enabled_flag => FND_API.G_TRUE,
5463 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID,
5464 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5465 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
5466 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5467 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_LBOUND,
5468 x_arg_id => l_arg_id) THEN
5469 RAISE INIT_FAILED;
5470 END IF;
5471 IF NOT MAKE_ARG(p_arg_name => 'p_rowid_ubound',
5472 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5473 p_parent_id => l_plsql_id,
5474 p_binding_enabled_flag => FND_API.G_TRUE,
5475 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID,
5476 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5477 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
5478 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5479 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_UBOUND,
5480 x_arg_id => l_arg_id) THEN
5481 RAISE INIT_FAILED;
5482 END IF;
5483 IF NOT MAKE_ARG(p_arg_name => 'p_unit_object_owner',
5484 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5485 p_parent_id => l_plsql_id,
5486 p_binding_enabled_flag => FND_API.G_TRUE,
5487 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
5488 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5489 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
5490 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5491 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_OBJECT_OWNER,
5492 x_arg_id => l_arg_id) THEN
5493 RAISE INIT_FAILED;
5494 END IF;
5495 IF NOT MAKE_ARG(p_arg_name => 'p_unit_object_name',
5496 p_parent_type => FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL,
5497 p_parent_id => l_plsql_id,
5498 p_binding_enabled_flag => FND_API.G_TRUE,
5499 p_datatype => FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2,
5500 p_permissions => FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE,
5501 p_write_policy => FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
5502 p_src_type => FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE,
5503 p_src_text => FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_OBJECT_NAME,
5504 x_arg_id => l_arg_id) THEN
5505 RAISE INIT_FAILED;
5506 END IF;
5507
5508 COMMIT;
5509 l_end := DBMS_UTILITY.GET_TIME;
5510 fnd_oam_debug.log(1, l_ctxt, 'Init Done - Duration: '||(l_end - l_start)/100|| ' seconds.');
5511 l_did_init := TRUE;
5512 END IF;
5513
5514 --do work
5515 l_retbool := EXECUTE_BUNDLE_WRAPPER(l_ctxt,
5516 p_run_id,
5517 p_bundle_id,
5518 l_worker_id,
5519 l_return_status,
5520 l_return_msg);
5521
5522 --make the guy who did init manage the sync, do cleanup and verify
5523 IF l_did_init THEN
5524 l_retbool_final := SYNC_ON_FINISH(p_run_id,
5525 l_retbool,
5526 p_num_workers,
5527 l_did_init);
5528 --validate the results
5529 l_retbool_final := l_retbool_final AND
5530 VALIDATE_TEST10(p_run_id,
5531 p_bundle_id,
5532 p_num_workers,
5533 l_child1_unit_id,
5534 l_child2_unit_id,
5535 l_child3_unit_id,
5536 l_test_table_name,
5537 l_test_table_num_rows,
5538 l_c2_final_arg_id);
5539
5540 --send acks to the other workers with the final status
5541 SEND_ACKS(p_run_id,
5542 l_retbool_final,
5543 p_num_workers);
5544 ELSE
5545 l_retbool_final := SYNC_ON_FINISH(p_run_id,
5546 l_retbool);
5547 END IF;
5548
5549 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5550 IF l_retbool_final THEN
5551 x_verdict := FND_API.G_TRUE;
5552 ELSE
5553 x_verdict := FND_API.G_FALSE;
5554 END IF;
5555 EXCEPTION
5556 WHEN INIT_FAILED THEN
5557 --release the run lock on failure just in case
5558 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
5559 IF l_retval <> 0 THEN
5560 fnd_oam_debug.log(6, l_ctxt, 'Failed to release run lock: '||l_retval);
5561 END IF;
5562 ROLLBACK;
5563 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5564 x_verdict := FND_API.G_FALSE;
5565 WHEN SYNC_FAILED THEN
5566 fnd_oam_debug.log(6, l_ctxt, 'Sync Failed');
5567 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5568 x_verdict := FND_API.G_FALSE;
5569 WHEN OTHERS THEN
5570 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
5571 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5572 x_verdict := FND_API.G_FALSE;
5573 END;
5574
5575 --Public
5576 PROCEDURE EXECUTE_ALL_TESTS(p_run_id IN NUMBER,
5577 p_bundle_id IN NUMBER,
5578 p_num_bundles IN NUMBER,
5579 p_num_workers IN NUMBER,
5580 p_fail_fast IN VARCHAR2,
5581 p_execute_real_table_tests IN VARCHAR2,
5582 x_verdict OUT NOCOPY VARCHAR2)
5583 IS
5584 l_ctxt VARCHAR2(60) := PKG_NAME||'EXECUTE_ALL_TESTS';
5585 l_fail_fast BOOLEAN := FND_OAM_DSCRAM_UTILS_PKG.FLAG_TO_BOOLEAN(p_fail_fast);
5586 l_execute_real_table_tests BOOLEAN := FND_OAM_DSCRAM_UTILS_PKG.FLAG_TO_BOOLEAN(p_execute_real_table_tests);
5587
5588 l_run_id NUMBER := p_run_id;
5589 l_bundle_id NUMBER := p_bundle_id;
5590
5591 l_retval BOOLEAN := TRUE;
5592 l_start NUMBER;
5593 l_end NUMBER;
5594 l_verdict VARCHAR2(6);
5595 BEGIN
5596 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
5597 l_start := DBMS_UTILITY.GET_TIME;
5598
5599 EXECUTE_TEST1(l_run_id,
5600 l_bundle_id,
5601 p_num_bundles,
5602 p_num_workers,
5603 l_verdict);
5604 IF l_verdict <> FND_API.G_TRUE THEN
5605 IF l_fail_fast THEN
5606 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5607 x_verdict := l_verdict;
5608 RETURN;
5609 END IF;
5610 END IF;
5611
5612 l_run_id := l_run_id + 1;
5613 l_bundle_id := l_bundle_id + p_num_bundles;
5614
5615 EXECUTE_TEST2(l_run_id,
5616 l_bundle_id,
5617 p_num_bundles,
5618 p_num_workers,
5619 l_verdict);
5620 IF l_verdict <> FND_API.G_TRUE THEN
5621 IF l_fail_fast THEN
5622 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5623 x_verdict := l_verdict;
5624 RETURN;
5625 END IF;
5626 END IF;
5627
5628 l_run_id := l_run_id + 1;
5629 l_bundle_id := l_bundle_id + p_num_bundles;
5630
5631 EXECUTE_TEST3(l_run_id,
5632 l_bundle_id,
5633 p_num_bundles,
5634 p_num_workers,
5635 l_verdict);
5636 IF l_verdict <> FND_API.G_TRUE THEN
5637 IF l_fail_fast THEN
5638 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5639 x_verdict := l_verdict;
5640 RETURN;
5641 END IF;
5642 END IF;
5643
5644 l_run_id := l_run_id + 1;
5645 l_bundle_id := l_bundle_id + p_num_bundles;
5646
5647 EXECUTE_TEST4(l_run_id,
5648 l_bundle_id,
5649 p_num_bundles,
5650 p_num_workers,
5651 l_verdict);
5652 IF l_verdict <> FND_API.G_TRUE THEN
5653 IF l_fail_fast THEN
5654 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5655 x_verdict := l_verdict;
5656 RETURN;
5657 END IF;
5658 END IF;
5659
5660 l_run_id := l_run_id + 1;
5661 l_bundle_id := l_bundle_id + p_num_bundles;
5662
5663 EXECUTE_TEST5(l_run_id,
5664 l_bundle_id,
5665 p_num_bundles,
5666 p_num_workers,
5667 l_verdict);
5668 IF l_verdict <> FND_API.G_TRUE THEN
5669 IF l_fail_fast THEN
5670 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5671 x_verdict := l_verdict;
5672 RETURN;
5673 END IF;
5674 END IF;
5675
5676 l_run_id := l_run_id + 1;
5677 l_bundle_id := l_bundle_id + p_num_bundles;
5678
5679 EXECUTE_TEST6(l_run_id,
5680 l_bundle_id,
5681 p_num_bundles,
5682 p_num_workers,
5683 l_verdict);
5684 IF l_verdict <> FND_API.G_TRUE THEN
5685 IF l_fail_fast THEN
5686 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5687 x_verdict := l_verdict;
5688 RETURN;
5689 END IF;
5690 END IF;
5691
5692 l_run_id := l_run_id + 1;
5693 l_bundle_id := l_bundle_id + p_num_bundles;
5694
5695 EXECUTE_TEST7(l_run_id,
5696 l_bundle_id,
5697 p_num_bundles,
5698 p_num_workers,
5699 l_verdict);
5700 IF l_verdict <> FND_API.G_TRUE THEN
5701 IF l_fail_fast THEN
5702 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5703 x_verdict := l_verdict;
5704 RETURN;
5705 END IF;
5706 END IF;
5707
5708 l_run_id := l_run_id + 1;
5709 l_bundle_id := l_bundle_id + p_num_bundles;
5710
5711 EXECUTE_TEST8(l_run_id,
5712 l_bundle_id,
5713 p_num_bundles,
5714 p_num_workers,
5715 l_verdict);
5716 IF l_verdict <> FND_API.G_TRUE THEN
5717 IF l_fail_fast THEN
5718 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5719 x_verdict := l_verdict;
5720 RETURN;
5721 END IF;
5722 END IF;
5723
5724 l_run_id := l_run_id + 1;
5725 l_bundle_id := l_bundle_id + p_num_bundles;
5726
5727 EXECUTE_TEST9(l_run_id,
5728 l_bundle_id,
5729 p_num_bundles,
5730 p_num_workers,
5731 l_verdict);
5732 IF l_verdict <> FND_API.G_TRUE THEN
5733 IF l_fail_fast THEN
5734 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5735 x_verdict := l_verdict;
5736 RETURN;
5737 END IF;
5738 END IF;
5739
5740 l_run_id := l_run_id + 1;
5741 l_bundle_id := l_bundle_id + p_num_bundles;
5742
5743 EXECUTE_TEST10(l_run_id,
5744 l_bundle_id,
5745 p_num_bundles,
5746 p_num_workers,
5747 l_verdict);
5748 IF l_verdict <> FND_API.G_TRUE THEN
5749 IF l_fail_fast THEN
5750 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5751 x_verdict := l_verdict;
5752 RETURN;
5753 END IF;
5754 END IF;
5755
5756 l_end := DBMS_UTILITY.GET_TIME;
5757 fnd_oam_debug.log(1, l_ctxt, 'Done - Duration: '||(l_end - l_start)/100|| ' seconds.');
5758
5759 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5760 x_verdict := l_verdict;
5761 EXCEPTION
5762 WHEN OTHERS THEN
5763 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
5764 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
5765 x_verdict := FND_API.G_FALSE;
5766 END;
5767
5768 END FND_OAM_DSCRAM_DIAG_PKG;