1 PACKAGE BODY BSC_TIME_DIM_TEST AS
2 /* $Header: BSCTIMDB.pls 120.2.12000000.1 2007/08/09 09:54:40 appldev noship $ */
3
4 FUNCTION check_dangling_records RETURN BOOLEAN;
5 PROCEDURE correct_dangling_records;
6 ------------------------------------------------------------
7 -- procedure to initialize test datastructures
8 ------------------------------------------------------------
9 PROCEDURE init IS
10 BEGIN
11 -- test writer could insert special setup code here
12 NULL;
13 END init;
14 ------------------------------------------------------------
15 -- procedure to cleanup any test datastructures that were setup in the init
16
17 PROCEDURE cleanup IS
18 BEGIN
19 -- test writer could insert special cleanup code here
20 NULL;
21 END cleanup;
22 ------------------------------------------------------------
23 -- procedure to execute the PLSQL test
24 -- the inputs needed for the test are passed in and a report object and CLOB are
25 -- returned.
26 -- note the way that support API writes to the report CLOB.
27 ------------------------------------------------------------
28 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
29 report OUT NOCOPY JTF_DIAG_REPORT,
30 reportClob OUT NOCOPY CLOB) IS
31 reportStr LONG;
32 counter NUMBER;
33 dummy_v2t JTF_DIAGNOSTIC_COREAPI.v2t;
34 c_userid VARCHAR2(50);
35 statusStr VARCHAR2(50);
36 errStr VARCHAR2(4000);
37 fixInfo VARCHAR2(4000);
38 isFatal VARCHAR2(50);
39 dummy_num NUMBER;
40 sqltxt VARCHAR2 (2000);
41 h_count NUMBER;
42 h_module_name BSC_LOOKUPS.MEANING%TYPE;
43 h_fii_cnt NUMBER;
44 l_status VARCHAR2(1);
45 l_industry VARCHAR2(1);
46 l_oracle_schema VARCHAR2(30);
47 l_return BOOLEAN;
48
49 BEGIN
50 l_return := FND_INSTALLATION.get_app_info
51 ( application_short_name => 'BSC'
52 , status => l_status
53 , industry => l_industry
54 , oracle_schema => l_oracle_schema
55 );
56
57 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
58
59 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
60
61 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
62
63 --Check FII tables first, if there is any data
64 SELECT COUNT(1) INTO h_fii_cnt FROM FII_TIME_DAY;
65 JTF_DIAGNOSTIC_COREAPI.Line_out(BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_FII_DATA_CHK'));
66
67 IF h_fii_cnt = 0 THEN
68 JTF_DIAGNOSTIC_COREAPI.BRPrint;
69
70 errStr := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_FII_NO_DATA');
71
72 JTF_DIAGNOSTIC_COREAPI.actionerrorprint(errStr);
73
74 statusStr := 'FAILURE';
75
76 fixInfo := errStr;
77
78 isFatal := 'FALSE';
79 END IF;
80 JTF_DIAGNOSTIC_COREAPI.BRPrint;
81 JTF_DIAGNOSTIC_COREAPI.Line_out(BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_PER_DANG_CHK'));
82
83 IF check_dangling_records THEN
84 JTF_DIAGNOSTIC_COREAPI.BRPrint;
85 JTF_DIAGNOSTIC_COREAPI.Line_out(BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_PER_DANG_CORRECT'));
86 correct_dangling_records;
87 JTF_DIAGNOSTIC_COREAPI.BRPrint;
88 errStr := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_PER_DANG_FOUND');
89 statusStr := 'FAILURE';
90 fixInfo := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_PER_DANG_FIX');
91 isFatal := 'FALSE';
92 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
93 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
94 RETURN ;
95 END IF;
96 JTF_DIAGNOSTIC_COREAPI.BRPrint;
97 JTF_DIAGNOSTIC_COREAPI.Line_out(BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_TIME_DIM_INDEX'));
98
99 SELECT COUNT(1) INTO h_count FROM all_indexes
100 WHERE index_name = 'BSC_DB_CALENDAR_U1'
101 AND OWNER = l_oracle_schema;
102
103 IF h_count > 0 THEN
104 JTF_DIAGNOSTIC_COREAPI.Line_out(BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_TIMED_IND_FOUND'));
105 JTF_DIAGNOSTIC_COREAPI.BRPrint;
106 errStr := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_TIMED_IND_FOUND');
107 statusStr := 'FAILURE';
108 fixInfo := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_TIMED_IND_FIX');
109 isFatal := 'FALSE';
110 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
111 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
112 RETURN ;
113 END IF;
114
115 sqltxt := 'SELECT start_month||''/''||start_day||''/''||current_year "START_DATE",edw_calendar_id FROM'||
116 ' bsc_sys_calendars_b WHERE edw_calendar_type_id=1 AND edw_calendar_id IN (1001,1002,1003)';
117 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_DBI_CAL_METADATA'));
118
119 statusStr := 'WARNING';
120 errStr := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_NEED_INVESTIGATION');
121
122 fixInfo := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_TIMED_DATA_UPLOAD');
123 JTF_DIAGNOSTIC_COREAPI.BRPrint;
124 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(fixInfo);
125 isFatal := 'FALSE';
126 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
127 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
128 END runTest;
129
130 ------------------------------------------------------------
131 -- procedure to report name back to framework
132 ------------------------------------------------------------
133 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
134 BEGIN
135 name := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_GEN_HEALTH_CHECK');
136 END getComponentName;
137
138 ------------------------------------------------------------
139 -- procedure to report test description back to framework
140 ------------------------------------------------------------
141 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
142 BEGIN
143 descStr := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_TIME_DIM_TDESC');
144 END getTestDesc;
145
146 ------------------------------------------------------------
147 -- procedure to report test name back to framework
148 ------------------------------------------------------------
149 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
150 BEGIN
151 name := BSC_OBJECTIVE_METADATA_SETUP.get_message_name('BSC_TIME_DIM_TNAME');
152 END getTestName;
153
154
155 ------------------------------------------------------------
156 -- procedure to provide the default parameters for the test case.
157 -- please note the paramters have to be registered through the UI
158 -- before basic tests can be run.
159 --
160 ------------------------------------------------------------
161 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
162 tempInput JTF_DIAG_INPUTTBL;
163 BEGIN
164 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
165 defaultInputValues := tempInput;
166 EXCEPTION
167 WHEN OTHERS THEN
168 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
169 END getDefaultTestParams;
170
171 FUNCTION getTestMode RETURN INTEGER IS
172 BEGIN
173 RETURN JTF_DIAGNOSTIC_ADAPTUTIL.BASIC_MODE;
174 END;
175
176 PROCEDURE correct_dangling_records IS
177 BEGIN
178 DELETE bsc_sys_periods_tl WHERE periodicity_id NOT IN
179 (SELECT periodicity_id FROM bsc_sys_periodicities);
180
181 DELETE bsc_sys_periods WHERE periodicity_id NOT IN
182 (SELECT periodicity_id FROM bsc_sys_periodicities);
183
184 DELETE bsc_sys_periodicities WHERE calendar_id NOT IN
185 (SELECT calendar_id FROM bsc_sys_calendars_b);
186 END;
187
188 FUNCTION check_dangling_records RETURN BOOLEAN IS
189 h_count NUMBER;
190 h_dangling_true BOOLEAN;
191 BEGIN
192 h_dangling_true := FALSE;
193 SELECT COUNT(1) INTO h_count from bsc_sys_periods_tl WHERE periodicity_id NOT IN
194 (SELECT periodicity_id FROM bsc_sys_periodicities);
195
196 IF h_count > 0 THEN
197 h_dangling_true := TRUE;
198 END IF;
199
200 SELECT COUNT(1) INTO h_count from bsc_sys_periods WHERE periodicity_id NOT IN
201 (SELECT periodicity_id FROM bsc_sys_periodicities);
202
203 IF h_count > 0 THEN
204 h_dangling_true := TRUE;
205 END IF;
206
207 SELECT COUNT(1) INTO h_count from bsc_sys_periodicities WHERE calendar_id NOT IN
208 (SELECT calendar_id FROM bsc_sys_calendars_b);
209
210 IF h_count > 0 THEN
211 h_dangling_true := TRUE;
212 END IF;
213
214 RETURN h_dangling_true;
215 END;
216 END;
[Home] [Help]