1 PACKAGE BODY CZ_DIAGNOSTICS_PVT AS
2 /* $Header: czdiagb.pls 120.3 2007/11/26 08:20:51 kdande ship $ */
3 ---------------------------------------------------------------------------------------
4 --This procedure traverses product structure and explosion in parallel, and reports the
5 --first encountered problem. It does not continue after the first problems because most
6 --errors in explosion will induce other errors, which would go away after the first one
7 --is corrected.
8 --
9 --p_debug_flag if 1, writes the detailed message log to cz_db_logs, otherwise just
10 -- returns one error message in the output parameter x_msg_data.
11 --
12 --p_fix_extra_flag in case when extra records are found the procedure can be requested
13 -- to automatically delete them with this parameter set to 1.
14 --
15 --p_mark_fixed_char if deleting explosions, deleted_flag will be set to this character.
16 --
17 --x_return_status FND_API.G_RET_STS_ERROR / FND_API.G_RET_STS_SUCCESS
18
19 PROCEDURE verify_structure(p_api_version IN NUMBER,
20 p_devl_project_id IN NUMBER,
21 p_debug_flag IN PLS_INTEGER,
22 p_fix_extra_flag IN PLS_INTEGER,
23 p_mark_fixed_char IN VARCHAR2,
24 x_run_id IN OUT NOCOPY NUMBER,
25 x_return_status IN OUT NOCOPY VARCHAR2,
26 x_msg_count IN OUT NOCOPY NUMBER,
27 x_msg_data IN OUT NOCOPY VARCHAR2)
28 IS
29
30 TYPE tIntegerArray IS TABLE OF NUMBER INDEX BY VARCHAR2(15);
31 TYPE tStringArray IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
32 TYPE tNumberArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
33 TYPE tDateArray IS TABLE OF DATE INDEX BY BINARY_INTEGER;
34
35 TYPE typePsNodeId IS TABLE OF cz_ps_nodes.ps_node_id%TYPE INDEX BY BINARY_INTEGER;
36 TYPE typePsNodeType IS TABLE OF cz_ps_nodes.ps_node_type%TYPE INDEX BY BINARY_INTEGER;
37 TYPE typeParentId IS TABLE OF cz_ps_nodes.parent_id%TYPE INDEX BY BINARY_INTEGER;
38 TYPE typeVirtualFlag IS TABLE OF cz_ps_nodes.virtual_flag%TYPE INDEX BY BINARY_INTEGER;
39 TYPE typeName IS TABLE OF cz_ps_nodes.name%TYPE INDEX BY BINARY_INTEGER;
40 TYPE typeReferenceId IS TABLE OF cz_ps_nodes.reference_id%TYPE INDEX BY VARCHAR2(15);
41
42 TYPE tExplNodeId IS TABLE OF cz_model_ref_expls.model_ref_expl_id%TYPE INDEX BY BINARY_INTEGER;
43
44 rootProjectName cz_devl_projects.name%TYPE;
45 rootProjectType cz_devl_projects.model_type%TYPE;
46 rootExplId cz_model_ref_expls.model_ref_expl_id%TYPE;
47
48 IsLogicGenerated tIntegerArray;
49
50 glPsNodeId typePsNodeId;
51 glReferenceId typeReferenceId;
52 glPsNodeType typePsNodeType;
53 glIndexByPsNodeId tIntegerArray;
54 glParentId typeParentId;
55 glName typeName;
56 glVirtualFlag typeVirtualFlag;
57
58 v_NodeIdByComponent tExplNodeId;
59
60 globalCount PLS_INTEGER := 1;
61 --Just to support debugging
62 nDebug PLS_INTEGER := 7777777;
63 --Auxiliery parameters for reporting
64 errorMessage VARCHAR2(4000);
65 g_message_id PLS_INTEGER := 1;
66
67 --Referencing level indicator and model stack
68 globalLevel INTEGER := 0;
69 globalStack tIntegerArray;
70
71 PS_NODE_TYPE_PRODUCT CONSTANT PLS_INTEGER := 258;
72 PS_NODE_TYPE_COMPONENT CONSTANT PLS_INTEGER := 259;
73 PS_NODE_TYPE_REFERENCE CONSTANT PLS_INTEGER := 263;
74 PS_NODE_TYPE_CONNECTOR CONSTANT PLS_INTEGER := 264;
75
76 CZ_CHK_UNKNOWN_NODE_TYPE EXCEPTION;
77 CZ_CHK_EMPTY_PROJECT EXCEPTION;
78 CZ_CHK_MISSING_ROOT_RECORD EXCEPTION;
79 CZ_CHK_DOUBLE_ROOT_RECORD EXCEPTION;
80 CZ_CHK_MISSING_EXPL_RECORD EXCEPTION;
81 CZ_CHK_EXTRA_EXPL_RECORD EXCEPTION;
82 CZ_CHK_INCORRECT_PARAM EXCEPTION;
83 ---------------------------------------------------------------------------------------
84 --Reporting procedure
85
86 PROCEDURE REPORT(inMessage IN VARCHAR2) IS
87 BEGIN
88
89 IF(p_debug_flag = 1)THEN
90
91 INSERT INTO cz_db_logs (run_id, logtime, message, caller, message_id)
92 VALUES (x_run_id, SYSDATE, inMessage, 'CZ_DIAGNOSTICS_PVT.VERIFY_STRUCTURE', g_message_id);
93
94 g_message_id := g_message_id + 1;
95
96 COMMIT;
97 END IF;
98 END;
99 ---------------------------------------------------------------------------------------
100 PROCEDURE RETURN_ERROR(inMessage IN VARCHAR2) IS
101 BEGIN
102 x_msg_data := inMessage;
103 x_msg_count := 1;
104 x_return_status := FND_API.G_RET_STS_ERROR;
105 REPORT(inMessage);
106 END;
107 ---------------------------------------------------------------------------------------
108 PROCEDURE verify_substructure(inComponentId IN NUMBER,
109 inProjectId IN NUMBER,
110 inExplId IN NUMBER)
111 IS
112
113 TYPE tNodeDepth IS TABLE OF cz_model_ref_expls.node_depth%TYPE INDEX BY BINARY_INTEGER;
114 TYPE tNodeType IS TABLE OF cz_model_ref_expls.ps_node_type%TYPE INDEX BY BINARY_INTEGER;
115 TYPE tVirtualFlag IS TABLE OF cz_model_ref_expls.virtual_flag%TYPE INDEX BY BINARY_INTEGER;
116 TYPE tParentId IS TABLE OF cz_model_ref_expls.parent_expl_node_id%TYPE INDEX BY BINARY_INTEGER;
117 TYPE tPsNodeId IS TABLE OF cz_model_ref_expls.component_id%TYPE INDEX BY BINARY_INTEGER;
118 TYPE tChildModelExpl IS TABLE OF cz_model_ref_expls.child_model_expl_id%TYPE INDEX BY BINARY_INTEGER;
119 TYPE tExplNodeType IS TABLE OF cz_model_ref_expls.expl_node_type%TYPE INDEX BY BINARY_INTEGER;
120 TYPE typeReferenceId IS TABLE OF cz_ps_nodes.reference_id%TYPE INDEX BY BINARY_INTEGER;
121 ntPsNodeId typePsNodeId;
122 ntPsNodeType typePsNodeType;
123 ntParentId typeParentId;
124 ntVirtualFlag typeVirtualFlag;
125 ntName typeName;
126 ntReferenceId typeReferenceId;
127
128 v_tNodeDepth tNodeDepth;
129 v_tNodeType tNodeType;
130 v_tVirtualFlag tVirtualFlag;
131 v_tParentId tParentId;
132 v_tComponentId tPsNodeId;
133 v_tReferringId tPsNodeId;
134 v_tChildModelExpl tChildModelExpl;
135 v_tExplNodeType tExplNodeType;
136 v_tExplNodeId tExplNodeId;
137
138 h_tNodeDepth tNodeDepth;
139 h_tNodeType tNodeType;
140 h_tVirtualFlag tVirtualFlag;
141 h_tParentId tParentId;
142 h_tComponentId tPsNodeId;
143 h_tReferringId tPsNodeId;
144 h_tChildModelExpl tChildModelExpl;
145 h_tExplNodeType tExplNodeType;
146
147 v_IndexByNodeId tIntegerArray;
148 v_TypeByExplId tExplNodeType;
149
150 thisComponentExplId cz_model_ref_expls.model_ref_expl_id%TYPE;
151 thisProjectId cz_devl_projects.devl_project_id%TYPE;
152 thisProjectName cz_devl_projects.name%TYPE;
153 thisProjectType cz_devl_projects.model_type%TYPE;
154 thisRootExplIndex PLS_INTEGER;
155
156 i PLS_INTEGER;
157 j PLS_INTEGER;
158 localCount PLS_INTEGER;
159 ---------------------------------------------------------------------------------------
160 BEGIN --verify_substructure
161
162 REPORT('Entering component_id = ' || inComponentId || ', devl_project_id = ' || inProjectId || ', expl_id = ' || inExplId);
163
164 --IF(inComponentId = inProjectId)THEN
165
166 REPORT('Reading explosions...');
167
168 --If this is a new model, read its explosion table.
169
170 SELECT model_ref_expl_id, component_id, ps_node_type, virtual_flag, referring_node_id,
171 child_model_expl_id, expl_node_type, node_depth
172 BULK COLLECT INTO v_tExplNodeId, v_tComponentId, v_tNodeType, v_tVirtualFlag, v_tReferringId,
173 v_tChildModelExpl, v_tExplNodeType, v_tNodeDepth
174 FROM cz_model_ref_expls
175 WHERE deleted_flag = '0'
176 AND model_id = inProjectId
177 AND parent_expl_node_id = inExplId;
178
179 FOR i IN 1..v_tExplNodeId.COUNT LOOP
180
181 REPORT('-> model_ref_expl_id = ' || v_tExplNodeId(i));
182
183 h_tNodeDepth(v_tExplNodeId(i)) := v_tNodeDepth(i);
184 h_tNodeType(v_tExplNodeId(i)) := v_tNodeType(i);
185 h_tVirtualFlag(v_tExplNodeId(i)) := v_tVirtualFlag(i);
186 h_tComponentId(v_tExplNodeId(i)) := v_tComponentId(i);
187 h_tReferringId(v_tExplNodeId(i)) := v_tReferringId(i);
188 h_tChildModelExpl(v_tExplNodeId(i)) := v_tChildModelExpl(i);
189 h_tExplNodeType(v_tExplNodeId(i)) := v_tExplNodeType(i);
190 END LOOP;
191 --END IF;
192
193 nDebug := 1110005;
194
195 --This SELECT statement reads the whole 'virtual' tree under a non-virtual component which also
196 --includes the non-virtual component itself. Non-virtual components underneath are included in
197 --order to recurse, and this function will be called for every non-virtual component underneath.
198
199 ntPsNodeId.DELETE;
200 ntParentId.DELETE;
201 ntName.DELETE;
202 ntPsNodeType.DELETE;
203 ntVirtualFlag.DELETE;
204 ntReferenceId.DELETE;
205
206 SELECT ps_node_id, parent_id, name, ps_node_type, virtual_flag, reference_id
207 BULK COLLECT INTO ntPsNodeId, ntParentId, ntName, ntPsNodeType, ntVirtualFlag, ntReferenceId
208 FROM cz_ps_nodes
209 WHERE deleted_flag = '0'
210 START WITH ps_node_id = inComponentId
211 CONNECT BY
212 (PRIOR virtual_flag IS NULL OR PRIOR virtual_flag = '1' OR
213 PRIOR ps_node_id = inComponentId)
214 AND PRIOR ps_node_id = parent_id;
215
216 nDebug := 1110006;
217
218 --Make sure there is some data returned
219
220 IF(ntPsNodeId.LAST IS NOT NULL)THEN
221
222 nDebug := 1110007;
223
224 --Having this dummy boundary node eliminates the necessity of potentially time
225 --consuming boundary checks.
226
227 ntParentId(ntPsNodeId.LAST + 1) := -99999;
228
229 --Prepare to start the main cycle
230
231 i := ntPsNodeId.FIRST;
232
233 WHILE(i <= ntPsNodeId.LAST) LOOP --Start the main structure verification cycle
234
235 BEGIN
236
237 --Populate the 'global' arrays, not used in this version.
238
239 nDebug := 1110010;
240
241 IF(NOT glIndexByPsNodeId.EXISTS(ntPsNodeId(i)))THEN
242
243 glPsNodeId(globalCount) := ntPsNodeId(i);
244 glPsNodeType(globalCount) := ntPsNodeType(i);
245 glParentId(globalCount) := ntParentId(i);
246 glName(globalCount) := ntName(i);
247 glVirtualFlag(globalCount) := ntVirtualFlag(i);
248
249 --Add an indexing option.
250
251 glIndexByPsNodeId(ntPsNodeId(i)) := globalCount;
252
253 --These global arrays will be indexed differently because we only need to get
254 --persistent_node_id or reference_id by ps_node_id. Probably, good indexing
255 --option for some of the other global arrays, too.
256
257 glReferenceId(ntPsNodeId(i)) := ntReferenceId(i);
258
259 --Children of any node start right after the node. But then, the children list may
260 --not be dense, because children may have their own children. So in order to find
261 --all the children of a node we need to search the whole structure after the node.
262 --Here we store the last child's index so that we need to search not the whole
263 --structure up to the end but up to this last child's index.
264
265 nDebug := 1110011;
266
267 globalCount := globalCount + 1;
268 END IF;
269
270 nDebug := 1110012;
271
272 --We need to call the procedure for any non-virtual component (bug #2065239) and for any
273 --component and reference.
274
275 IF(ntPsNodeType(i) IN (PS_NODE_TYPE_REFERENCE, PS_NODE_TYPE_CONNECTOR))THEN
276
277 REPORT('Found reference/connector, ps_node_id = ' || ntPsNodeId(i));
278
279 --Check for circularity.
280
281 localCount := 0;
282
283 FOR n IN 1..globalLevel LOOP
284 IF(globalStack(n) = ntReferenceId(i))THEN
285
286 --Circularity detected.
287
288 localCount := 1;
289 EXIT;
290 END IF;
291 END LOOP;
292
293 --Find the corresponding explosion record v_tExplNodeId(j). Use FIRST and NEXT, as the table
294 --may become sparse.
295
296 REPORT('Looking for explosion...');
297
298 j := v_tExplNodeId.FIRST;
299
300 WHILE(j IS NOT NULL)LOOP
301
302 REPORT('-> checking expl_id = ' || v_tExplNodeId(j));
303
304 IF(v_tComponentId(j) = ntReferenceId(i) AND
305 v_tReferringId(j) = ntPsNodeId(i) AND
306 v_tNodeType(j) = ntPsNodeType(i) AND
307 v_tVirtualflag(j) = ntVirtualFlag(i))THEN
308
309 REPORT('Match found!');
310 EXIT;
311 END IF;
312
313 j := v_tExplNodeId.NEXT(j);
314 END LOOP;
315
316 IF(localCount = 0)THEN
317 IF(j IS NULL)THEN
318
319 REPORT('Global stack:');
320 FOR n IN 1..globalLevel LOOP
321 REPORT('model_id = ' || globalStack(n));
322 END LOOP;
323 REPORT('ps_node_id = ' || ntPsNodeId(i) || ', component_id = ' || inComponentId || ', devl_project_id = ' || inProjectId);
324
325 errorMessage := 'model_id = ' || inProjectId || ': missing explosion record for ps_node_id = ' || ntPsNodeId(i) ||
326 ', component_id = ' || inComponentId;
327 RAISE CZ_CHK_MISSING_EXPL_RECORD;
328 END IF;
329
330 globalLevel := globalLevel + 1;
331 globalStack(globalLevel) := ntReferenceId(i);
332
333 verify_substructure(ntReferenceId(i), inProjectId, v_tExplNodeId(j));
334
335 --Now follow the reference again but this time switching context to the child model.
336
337 IF(v_tNodeDepth(j) = 1 AND (NOT isLogicGenerated.EXISTS(ntReferenceId(i))))THEN
338
339 verify_substructure(ntReferenceId(i), ntReferenceId(i), v_tChildModelExpl(j));
340 END IF;
341
342 globalLevel := globalLevel - 1;
343 END IF;
344
345 --Delete the explosion record from the memory table.
346
347 IF(j IS NOT NULL)THEN v_tExplNodeId.DELETE(j); END IF;
348
349 ELSIF(ntVirtualFlag(i) = '0' AND ntPsNodeType(i) IN (PS_NODE_TYPE_COMPONENT, PS_NODE_TYPE_PRODUCT) AND
350 ntPsNodeId(i) <> inComponentId)THEN
351
352 REPORT('Found non-virtual component, ps_node_id = ' || ntPsNodeId(i));
353
354 --Find the corresponding explosion record v_tExplNodeId(j). Use FIRST and NEXT, as the table
355 --may become sparse.
356
357 REPORT('Looking for explosion...');
358
359 j := v_tExplNodeId.FIRST;
360
361 WHILE(j IS NOT NULL)LOOP
362
363 REPORT('-> checking expl_id = ' || v_tExplNodeId(j));
364
365 IF(v_tComponentId(j) = ntPsNodeId(i) AND
366 v_tNodeType(j) = ntPsNodeType(i) AND
367 v_tVirtualflag(j) = ntVirtualFlag(i))THEN
368
369 REPORT('Match found!');
370 EXIT;
371 END IF;
372
373 j := v_tExplNodeId.NEXT(j);
374 END LOOP;
375
376 IF(j IS NULL)THEN
377
378 REPORT('Global stack:');
379 FOR n IN 1..globalLevel LOOP
380 REPORT('model_id = ' || globalStack(n));
381 END LOOP;
382 REPORT('ps_node_id = ' || ntPsNodeId(i) || ', component_id = ' || inComponentId || ', devl_project_id = ' || inProjectId);
383
384 errorMessage := 'model_id = ' || inProjectId || ': missing explosion record for ps_node_id = ' || ntPsNodeId(i) ||
385 ', component_id = ' || inComponentId;
386 RAISE CZ_CHK_MISSING_EXPL_RECORD;
387 END IF;
388
389 verify_substructure(ntPsNodeId(i), inProjectId, v_tExplNodeId(j));
390
394 END IF;
391 --Delete the explosion record from the memory table.
392
393 v_tExplNodeId.DELETE(j);
395 END;
396
397 --Increase the main cycle counter
398
399 i := i + 1;
400
401 END LOOP; --end of the main structure verification cycle
402
403 IF(v_tExplNodeId.COUNT > 0)THEN
404
405 --There are still explosion records that haven't been matched with any of the
406 --structure records.
407
408 REPORT('Global stack:');
409 FOR n IN 1..globalLevel LOOP
410 REPORT('model_id = ' || globalStack(n));
411 END LOOP;
412
413 j := v_tExplNodeId.FIRST;
414
415 WHILE(j IS NOT NULL)LOOP
416
417 REPORT('extra explosion: ' || v_tExplNodeId(j));
418
419 IF(p_fix_extra_flag = 1)THEN
420
421 UPDATE cz_model_ref_expls SET deleted_flag = p_mark_fixed_char WHERE model_ref_expl_id = v_tExplNodeId(j);
422 END IF;
423
424 j := v_tExplNodeId.NEXT(j);
425 END LOOP;
426
427 errorMessage := 'model_id = ' || inProjectId || ', component_id = ' || inComponentId || ': extra explosion records';
428 RAISE CZ_CHK_EXTRA_EXPL_RECORD;
429 END IF;
430
431 nDebug := 1110038;
432
433 IF(inComponentId = inProjectId)THEN IsLogicGenerated(inComponentId) := 1; END IF;
434
435 ELSIF(inComponentId = inProjectId)THEN --IF 'there is some data returned'
436
437 --The project is empty, stop here.
438
439 errorMessage := 'model_id = ' || inProjectId || ' contains no data.';
440 RAISE CZ_CHK_EMPTY_PROJECT;
441
442 END IF; --end of the ELSE block of IF 'there is some data returned'.
443
444 REPORT('Exiting component_id = ' || inComponentId || ', devl_project_id = ' || inProjectId || ', expl_id = ' || inExplId);
445
446 END; --verify_substructure
447 ---------------------------------------------------------------------------------------
448 BEGIN --verify_structure
449
450 IF(p_fix_extra_flag = 1 AND p_mark_fixed_char IS NULL)THEN
451
452 errorMessage := 'p_mark_fixed_flag parameter must be specified for p_fix_extra_flag = 1';
453 RAISE CZ_CHK_INCORRECT_PARAM;
454 END IF;
455
456 IF(p_fix_extra_flag = 1 AND LENGTH(p_mark_fixed_char) > 1)THEN
457
458 errorMessage := 'p_mark_fixed_flag parameter must be one character.';
459 RAISE CZ_CHK_INCORRECT_PARAM;
460 END IF;
461
462 --Get the run id. If a valid value has been passed as a parameter, use it,
463 --else generate a new value.
464
465 IF(x_run_id IS NULL OR x_run_id = 0)THEN
466 SELECT cz_xfr_run_infos_s.NEXTVAL INTO x_run_id FROM DUAL;
467 END IF;
468
469 globalLevel := globalLevel + 1;
470 globalStack(globalLevel) := p_devl_project_id;
471
472 --Start off the recursion
473
474 BEGIN
475
476 SELECT model_ref_expl_id INTO rootExplId
477 FROM cz_model_ref_expls
478 WHERE deleted_flag = '0'
479 AND model_id = p_devl_project_id
480 AND parent_expl_node_id IS NULL;
481
482 EXCEPTION
483 WHEN NO_DATA_FOUND THEN
484 errorMessage := 'Missing root explosion record for model_id = ' || p_devl_project_id;
485 RAISE CZ_CHK_MISSING_ROOT_RECORD;
486 WHEN TOO_MANY_ROWS THEN
487 errorMessage := 'Multiple root explosion records for model_id = ' || p_devl_project_id;
488 RAISE CZ_CHK_DOUBLE_ROOT_RECORD;
489 END;
490
491 verify_substructure(p_devl_project_id, p_devl_project_id, rootExplId);
492 x_return_status := FND_API.G_RET_STS_SUCCESS;
493 x_msg_count := 0;
494 x_msg_data := NULL;
495
496 EXCEPTION
497 WHEN CZ_CHK_MISSING_ROOT_RECORD THEN
498 RETURN_ERROR(errorMessage);
499 WHEN CZ_CHK_DOUBLE_ROOT_RECORD THEN
500 RETURN_ERROR(errorMessage);
501 WHEN CZ_CHK_MISSING_EXPL_RECORD THEN
502 RETURN_ERROR(errorMessage);
503 WHEN CZ_CHK_EXTRA_EXPL_RECORD THEN
504 RETURN_ERROR(errorMessage);
505 WHEN CZ_CHK_EMPTY_PROJECT THEN
506 RETURN_ERROR(errorMessage);
507 WHEN CZ_CHK_INCORRECT_PARAM THEN
508 RETURN_ERROR(errorMessage);
509 WHEN OTHERS THEN
510 RETURN_ERROR('(' || nDebug || '): Root model_id = ' || p_devl_project_id || ', unexpected error occurred:' || SQLERRM);
511 END verify_structure;
512 ---------------------------------------------------------------------------------------
513 --The default API - can provide simple or detailed output, does not fix anything.
514
515 PROCEDURE verify_structure(p_api_version IN NUMBER,
516 p_devl_project_id IN NUMBER,
517 p_debug_flag IN PLS_INTEGER,
518 x_run_id IN OUT NOCOPY NUMBER,
519 x_return_status IN OUT NOCOPY VARCHAR2,
520 x_msg_count IN OUT NOCOPY NUMBER,
521 x_msg_data IN OUT NOCOPY VARCHAR2)
522 IS
523 BEGIN
524 verify_structure(p_api_version, p_devl_project_id, p_debug_flag, 0, NULL,
525 x_run_id, x_return_status, x_msg_count, x_msg_data);
526 END verify_structure; --(standard API)
527 ---------------------------------------------------------------------------------------
528 --Example if use:
529 -- SELECT cz_diagnostics_pvt.fast_verify(623160) FROM DUAL;
533
530
531 FUNCTION fast_verify(p_devl_project_id IN NUMBER) RETURN VARCHAR2
532 IS
534 l_run_id NUMBER;
535 l_return_status VARCHAR2(3);
536 l_msg_count NUMBER;
537 l_msg_data VARCHAR2(2000);
538 BEGIN
539 verify_structure(1.0, p_devl_project_id, 0, 0, NULL,
540 l_run_id, l_return_status, l_msg_count, l_msg_data);
541 RETURN l_msg_data;
542 END fast_verify;
543 ---------------------------------------------------------------------------------------
544 --Example if use:
545 -- SET SERVEROUTPUT ON
546 -- BEGIN DBMS_OUTPUT.PUT_LINE(cz_diagnostics_pvt.fast_debug(623160)); END;
547 -- /
548 -- SELECT message FROM cz_db_logs WHERE run_id = <value> ORDER BY message_id;
549
550 FUNCTION fast_debug(p_devl_project_id IN NUMBER) RETURN NUMBER
551 IS
552
553 l_run_id NUMBER;
554 l_return_status VARCHAR2(3);
555 l_msg_count NUMBER;
556 l_msg_data VARCHAR2(2000);
557 BEGIN
558 verify_structure(1.0, p_devl_project_id, 1, 0, NULL,
559 l_run_id, l_return_status, l_msg_count, l_msg_data);
560 RETURN l_run_id;
561 END fast_debug;
562 ---------------------------------------------------------------------------------------
563 --This procedure can be used to automatically fix the explosions - it runs until the
564 --return status is successful. However, currently it can only delete extra explosion
565 --records, and has not been thoroughly tested. It is relatively safe, when the extra
566 --records is the only type of problem.
567 --
568 --It will mark the records it deletes with the character in p_mark_fixed_char, which
569 --must be specified. It is best if this character is not present in the deleted_flag
570 --before the run - this makes it easy to rollback the changes.
571 --
572 --Because the procedure may run many times, the debug output is disabled.
573 --
574 --Example of use: EXECUTE cz_diagnostics_pvt.fast_fix_extra(623160, '7');
575
576 PROCEDURE fast_fix_extra(p_devl_project_id IN NUMBER, p_mark_fixed_char IN VARCHAR2)
577 IS
578
579 l_run_id NUMBER;
580 l_return_status VARCHAR2(3) := FND_API.G_RET_STS_ERROR;
581 l_msg_count NUMBER;
582 l_msg_data VARCHAR2(2000);
583 BEGIN
584
585 WHILE(l_return_status = FND_API.G_RET_STS_ERROR)LOOP
586
587 l_return_status := NULL;
588 verify_structure(1.0, p_devl_project_id, 0, 1, p_mark_fixed_char,
589 l_run_id, l_return_status, l_msg_count, l_msg_data);
590 END LOOP;
591 END fast_fix_extra;
592 ---------------------------------------------------------------------------------------
593 END CZ_DIAGNOSTICS_PVT;
[Home] [Help]