[Home] [Help]
PACKAGE BODY: APPS.IEM_DIAG_DATABASE_LINK_PVT
Source
1 PACKAGE BODY IEM_DIAG_DATABASE_LINK_PVT AS
2 /* $Header: iemddblb.pls 115.6 2004/04/01 00:59:51 chtang noship $ */
3
4 PROCEDURE init IS
5 BEGIN
6 null;
7 END init;
8
9
10 PROCEDURE cleanup IS
11 BEGIN
12 null;
13 END cleanup;
14
15 PROCEDURE runTest(inputs IN JTF_DIAG_INPUTTBL,
16 reports OUT NOCOPY JTF_DIAG_REPORT,
17 reportClob OUT NOCOPY CLOB) IS
18 reportStr LONG;
19 counter NUMBER;
20 dummy_v2t JTF_DIAGNOSTIC_COREAPI.v2t;
21 c_userid VARCHAR2(50);
22 statusStr VARCHAR2(50) := 'SUCCESS';
23 errStr VARCHAR2(4000);
24 errOO1 VARCHAR2(4000);
25 errOO2 VARCHAR2(4000);
26 errOO3 VARCHAR2(4000);
27 errOra1 VARCHAR2(4000);
28 errOra2 VARCHAR2(4000);
29 errOra3 VARCHAR2(4000);
30 fixInfo VARCHAR2(4000);
31 isFatal VARCHAR2(50);
32 dummy_num NUMBER;
33 l_db_link iem_db_connections.db_link%type;
34 l_db_name iem_db_servers.db_name%type;
35 l_db_server_tbl jtf_varchar2_Table_100:=jtf_varchar2_Table_100();
36 l_count number;
37 l_link_count number;
38 l_search_dblink VARCHAR2(129);
39 l_global_name VARCHAR2(240);
40 l_schema_owner VARCHAR2(30);
41
42 link_not_correct EXCEPTION;
43
44 TYPE LinkCur Is REF CURSOR;
45 l_link_cur LinkCur;
46 l_statement VARCHAR2(2000);
47
48 BEGIN
49 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
50 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
51 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
52
53 select oracle_username into l_schema_owner from fnd_oracle_userid where read_only_flag = 'U';
54
55 select db_server_id bulk collect into l_db_server_tbl from iem_db_servers;
56
57 errStr := '';
58 fixInfo := '';
59
60 FOR i IN l_db_server_tbl.FIRST..l_db_server_tbl.LAST LOOP
61 select count(*) into l_count from iem_db_connections where db_server_id=l_db_server_tbl(i) and is_admin='A';
62 select db_name into l_db_name from iem_db_servers where db_server_id=l_db_server_tbl(i);
63
64 if (l_count = 0) then
65 statusStr := 'FAILURE';
66 errOO1 := errOO1 || l_db_name || ', ';
67 fixInfo := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_HELP');
68 isFatal := 'TRUE';
69 else
70 select a.db_link into l_db_link from iem_db_connections a, iem_db_servers b where a.db_server_id=b.db_server_id and a.is_admin='A' and a.db_server_id=l_db_server_tbl(i);
71
72 l_search_dblink := l_db_link || '%';
73
74 select count(*)into l_link_count from all_db_links where upper(owner)=upper(l_schema_owner) and db_link like UPPER(l_search_dblink);
75
76 if (l_link_count = 0) then
77 statusStr := 'FAILURE';
78 errOO2 := errOO2 || l_db_link || ', ';
79 fixInfo := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_HELP');
80 isFatal := 'TRUE';
81 else
82 -- check if oo link is valid
83 BEGIN
84 l_statement := 'SELECT global_name FROM global_name@'||l_db_link;
85 OPEN l_link_cur for l_statement;
86 LOOP
87 FETCH l_link_cur INTO l_global_name;
88 EXIT WHEN l_link_cur%notfound;
89 END LOOP;
90 close l_link_cur;
91
92 EXCEPTION
93
94 WHEN OTHERS THEN
95 statusStr := 'FAILURE';
96 errOO3 := errOO3 || l_db_link || ', ';
97 fixInfo := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_HELP');
98 isFatal := 'TRUE';
99 END;
100 end if; -- (l_link_count = 0)
101 end if; -- (l_count = 0)
102
103 select count(*) into l_count from iem_db_connections where db_server_id=l_db_server_tbl(i) and is_admin='P';
104
105 if (l_count = 0) then
106 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
107 statusStr := 'FAILURE';
108 errOra1 := errOra1 || l_db_name || ', ';
109 fixInfo := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_HELP');
110 isFatal := 'TRUE';
111 else
112 select a.db_link into l_db_link from iem_db_connections a, iem_db_servers b where a.db_server_id=b.db_server_id and a.is_admin='P' and a.db_server_id=l_db_server_tbl(i);
113
114 l_search_dblink := l_db_link || '%';
115
116 select count(*)into l_link_count from all_db_links where upper(owner)=upper(l_schema_owner) and db_link like UPPER(l_search_dblink);
117
118 if (l_link_count = 0) then
119 statusStr := 'FAILURE';
120 errOra2 := errOra2 || l_db_link || ', ';
121 fixInfo := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_HELP');
122 isFatal := 'TRUE';
123 else
124 -- check if ora link is valid
125 BEGIN
126 l_statement := 'SELECT global_name FROM global_name@'||l_db_link;
127 OPEN l_link_cur for l_statement;
128 LOOP
129 FETCH l_link_cur INTO l_global_name;
130 EXIT WHEN l_link_cur%notfound;
131 END LOOP;
132 close l_link_cur;
133
134 EXCEPTION
135 WHEN OTHERS THEN
136 statusStr := 'FAILURE';
137 errOra3 := errOra3 || l_db_link || ', ';
138 fixInfo := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_HELP');
139 isFatal := 'TRUE';
140 END;
141
142 end if; -- (l_link_count = 0)
143 end if; -- (l_count = 0)
144
145 END LOOP;
146
147 if (statusStr = 'SUCCESS') then
148 reportStr := '<font color=blue> ' || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC7_SUCCESS') || ' </font><p>';
149 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
150 else
151 reportStr := '<hr>';
152 if (errOO1 <> FND_API.G_MISS_CHAR) then
153 errOO1 := RTRIM(errOO1, ', ');
154 errStr := errStr || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC7_ERROR1') || ' ';
155 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint( FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC7_SUM1') );
156 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
157 end if;
158
159 if (errOO2 <> FND_API.G_MISS_CHAR) then
160 errOO2 := RTRIM(errOO2, ', ');
161 errStr := errStr || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC7_ERROR2') || ' (' || errOO2 || ' )' || ' ';
162 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC7_SUM2'));
163 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
164 end if;
165 if (errOO3 <> FND_API.G_MISS_CHAR) then
166 errOO3 := RTRIM(errOO3, ', ');
167 errStr := errStr || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC7_ERROR5') || ' (' || errOO3 || ' )' || ' ';
168 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC7_SUM5'));
169 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
170 end if;
171
172 if (errOra1 <> FND_API.G_MISS_CHAR) then
173 errOra1 := RTRIM(errOra1, ', ');
174 errStr := errStr || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC7_ERROR3') || ' ';
175 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC7_SUM3'));
176 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
177 end if;
178 if (errOra2 <> FND_API.G_MISS_CHAR) then
179 errOra2 := RTRIM(errOra2, ', ');
180 errStr := errStr || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC7_ERROR4') || ' (' || errOra2|| ' )' || ' ';
181 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC7_SUM4'));
182 end if;
183 if (errOra3 <> FND_API.G_MISS_CHAR) then
184 errOra3 := RTRIM(errOra3, ', ');
185 errStr := errStr || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC7_ERROR6') || ' (' || errOra3|| ' )' || ' ';
186 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC7_SUM6'));
187 end if;
188 end if;
189
190 reports := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
191 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
192 END runTest;
193
194 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
195 BEGIN
196 name := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_COMPONENT3');
197 END getComponentName;
198
199 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
200 BEGIN
201 descStr := '<ul><li> ' || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC7_DESC') || ' <li> ' || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC7_DESC1') || '</ul>';
202 END getTestDesc;
203
204 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
205 BEGIN
206 name := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TESTCASE_NAME7');
207 END getTestName;
208
209 FUNCTION getTestMode RETURN INTEGER IS
210 BEGIN
211 return JTF_DIAGNOSTIC_ADAPTUTIL.BOTH_MODE;
212 END getTestMode;
213
214 PROCEDURE getDefaultTestParams(defaultInputvalues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
215 tempInput JTF_DIAG_INPUTTBL;
216 BEGIN
217 null;
218 END getDefaultTestParams;
219
220 END;