1 PACKAGE BODY BSC_INVALID_DIM_LVLS_RPT AS
2 /* $Header: BSCIVLDB.pls 120.1.12000000.2 2007/08/10 06:08:15 amitgupt noship $ */
3
4 ------------------------------------------------------------
5 -- procedure to initialize test datastructures
6 ------------------------------------------------------------
7 PROCEDURE init IS
8 BEGIN
9 -- test writer could insert special setup code here
10 NULL;
11 END init;
12 ------------------------------------------------------------
13 -- procedure to cleanup any test datastructures that were setup in the init
14
15 PROCEDURE cleanup IS
16 BEGIN
17 -- test writer could insert special cleanup code here
18 NULL;
19 END cleanup;
20 ------------------------------------------------------------
21 -- procedure to execute the PLSQL test
22 -- the inputs needed for the test are passed in and a report object and CLOB are
23 -- returned.
24 -- note the way that support API writes to the report CLOB.
25 ------------------------------------------------------------
26 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
27 report OUT NOCOPY JTF_DIAG_REPORT,
28 reportClob OUT NOCOPY CLOB) IS
29 reportStr LONG;
30 counter NUMBER;
31 dummy_v2t JTF_DIAGNOSTIC_COREAPI.v2t;
32 c_userid VARCHAR2(50);
33 statusStr VARCHAR2(50);
34 errStr VARCHAR2(4000);
35 fixInfo VARCHAR2(4000);
36 isFatal VARCHAR2(50);
37 dummy_num NUMBER;
38 sqltxt VARCHAR2 (2000);
39 h_headers JTF_DIAGNOSTIC_COREAPI.headers;
40 h_lengths JTF_DIAGNOSTIC_COREAPI.lengths;
41 h_count NUMBER;
42 h_source bsc_sys_dim_levels_b.source%TYPE;
43 h_pmf_count NUMBER;
44 h_bsc_count NUMBER;
45 CURSOR cv IS
46 SELECT source,COUNT(short_name) FROM bsc_sys_dim_levels_vl
47 WHERE table_type = 1 AND EXISTS (SELECT 1 FROM user_objects WHERE status='INVALID' AND
48 object_name = level_table_name)
49 GROUP BY source;
50 BEGIN
51 h_bsc_count := 0;
52 h_pmf_count := 0;
53 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
54
55 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
56
57 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
58
59 IF cv%ISOPEN THEN
60 CLOSE cv;
61 END IF;
62
63 --CHECK IF THERE ARE INVALID DIMENSION OBJECTS IN BSC
64
65 OPEN cv;
66 LOOP
67 FETCH cv INTO h_source,h_count;
68 EXIT WHEN cv%NOTFOUND;
69 IF h_source = 'PMF' THEN
70 h_pmf_count := h_count;
71 ELSIF h_source = 'BSC' THEN
72 h_bsc_count := h_count;
73 END IF;
74 END LOOP;
75 CLOSE cv;
76
77
78 h_count := h_pmf_count + h_bsc_count;
79 IF h_count >0 THEN
80 JTF_DIAGNOSTIC_COREAPI.BRPrint;
81 sqltxt := 'SELECT Name "Dimension Object Name", short_name, SOURCE, LEVEL_TABLE_NAME, '||
82 ' LEVEL_VIEW_NAME FROM bsc_sys_dim_levels_vl '||
83 ' WHERE TABLE_TYPE = 1 AND EXISTS (SELECT 1 FROM user_objects '||
84 ' WHERE status=''INVALID'' AND object_name = LEVEL_VIEW_NAME) ';
85
86 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_INVALID_DIM_LVLS'));
87 JTF_DIAGNOSTIC_COREAPI.BRPrint;
88 statusStr := 'FAILURE';
89 errStr := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_INVALID_DIM_LVLS_F');
90 IF h_pmf_count >0 THEN
91 fixInfo := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_PMF_DIM_LVLS_FIX');
92 END IF;
93 fixInfo := fixInfo || BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_BSC_DIM_LVLS_FIX');
94
95 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(fixInfo);
96 isFatal := 'FALSE';
97 ELSE
98 statusStr := 'SUCCESS';
99 reportStr := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_NO_INVLD_VIEWS');
100 JTF_DIAGNOSTIC_COREAPI.Line_out(reportStr);
101 JTF_DIAGNOSTIC_COREAPI.BRPrint;
102 END IF;
103 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
104 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
105 END runTest;
106
107 ------------------------------------------------------------
108 -- procedure to report name back to framework
109 ------------------------------------------------------------
110 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
111 BEGIN
112 name := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_GEN_HEALTH_CHECK');
113 END getComponentName;
114
115 ------------------------------------------------------------
116 -- procedure to report test description back to framework
117 ------------------------------------------------------------
118 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
119 BEGIN
120 descStr := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_DIM_VIEW_TDESC');
121 END getTestDesc;
122
123 ------------------------------------------------------------
124 -- procedure to report test name back to framework
125 ------------------------------------------------------------
126 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
127 BEGIN
128 name := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_DIM_VIEW_TNAME');
129 END getTestName;
130
131 ------------------------------------------------------------
132 -- procedure to provide the default parameters for the test case.
133 -- please note the paramters have to be registered through the UI
134 -- before basic tests can be run.
135 --
136 ------------------------------------------------------------
137 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
138 tempInput JTF_DIAG_INPUTTBL;
139 BEGIN
140 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
141 defaultInputValues := tempInput;
142 EXCEPTION
143 WHEN OTHERS THEN
144 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
145 END getDefaultTestParams;
146
147 FUNCTION getTestMode RETURN INTEGER IS
148 BEGIN
149 RETURN JTF_DIAGNOSTIC_ADAPTUTIL.BASIC_MODE;
150 END;
151 END;
[Home] [Help]