[Home] [Help]
PACKAGE BODY: APPS.IEM_DIAG_OES_RULE_PVT
Source
1 PACKAGE BODY IEM_DIAG_OES_RULE_PVT AS
2 /* $Header: iemdorub.pls 115.2 2003/01/20 22:41:03 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 fixInfo VARCHAR2(4000);
25 isFatal VARCHAR2(50);
26 l_norule_accounts varchar2(32767);
27 l_mulrule_accounts varchar2(32767);
28 l_invalidrule_accounts varchar2(32767);
29 l_notauthen_accounts varchar2(32767);
30 dummy_num NUMBER;
31 l_db_link iem_db_connections.db_link%type;
32 l_apps_link iem_db_connections.db_link%type;
33 l_db_name iem_db_servers.db_name%type;
34 l_db_server_tbl jtf_varchar2_Table_100:=jtf_varchar2_Table_100();
35 l_count number;
36 l_link_count number;
37
38 l_account_rec iem_diag_oes_rule_pvt.account_type;
39 Type get_account_rec is REF CURSOR ;
40 email_account_cur get_account_rec;
41 l_string varchar2(2000);
42 l_str VARCHAR2(2000);
43 l_str1 VARCHAR2(2000);
44 l_ret NUMBER;
45 l_stat varchar2(10);
46 l_data varchar2(255);
47 l_im_link varchar2(200);
48 l_im_link1 varchar2(200);
49 l_dummy INTEGER;
50 l_rule_table VARCHAR(100);
51 l_account_table VARCHAR(100);
52 l_domain_table VARCHAR(100);
53 l_rule_count NUMBER := 0;
54 l_rule_name VARCHAR2(100);
55 l_rule_apps_name VARCHAR2(100);
56 l_cursorID INTEGER;
57 l_cursorID1 INTEGER;
58 l_length NUMBER;
59 BEGIN
60 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
61 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
62 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
63
64 l_string := 'select email_user, domain, db_server_id, email_password, account_flag from iem_email_accounts';
65
66 select db_link into l_apps_link from iem_db_connections where is_admin='O';
67
68 -- Start looping through all email accounts defined in SSA
69 OPEN email_account_cur FOR l_string;
70 LOOP
71 FETCH email_account_cur into l_account_rec;
72 EXIT WHEN email_account_cur%NOTFOUND;
73
74 IEM_DB_CONNECTIONS_PVT.select_item(
75 p_api_version_number =>1.0,
76 p_db_server_id =>l_account_rec.db_server_id,
77 p_is_admin =>'A',
78 x_db_link=>l_im_link1,
79 x_return_status =>l_stat,
80 x_msg_count => l_count,
81 x_msg_data => l_data);
82
83
84 If l_im_link1 is null then
85 l_im_link:=null;
86 else
87 l_im_link:='@'||l_im_link1;
88 end if;
89
90 l_str:='begin :l_ret:=im_api.authenticate'||l_im_link||'(:a_user,:a_domain,:a_password);end; ';
91 EXECUTE IMMEDIATE l_str using OUT l_ret,l_account_rec.email_user,l_account_rec.domain,l_account_rec.email_password;
92 IF l_ret<>0 THEN
93 statusStr := 'FAILURE';
94 l_notauthen_accounts := l_notauthen_accounts || l_account_rec.email_user || '@' || l_account_rec.domain || ', ';
95 fixInfo := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_HELP');
96 isFatal := 'FALSE';
97 END IF;
98
99 l_cursorID := DBMS_SQL.OPEN_CURSOR;
100
101 l_rule_table := 'om_server_rules' || l_im_link;
102 l_account_table := 'ds_account' || l_im_link;
103 l_domain_table := 'ds_domain' || l_im_link;
104
105 l_str := 'select count(*) from ' || l_rule_table || ' a, ' || l_account_table || ' b, ' || l_domain_table || ' c where b.objectid = a.account_id and b.domainid = c.objectid and c.qualifiedname=UPPER(:domain) and b.name = UPPER(:username)';
106
107 l_str1 := 'select info1 from ' || l_rule_table || ' a, ' || l_account_table || ' b, ' || l_domain_table || ' c where b.objectid = a.account_id and b.domainid = c.objectid and c.qualifiedname=UPPER(:domain) and b.name = UPPER(:username)';
108
109 DBMS_SQL.PARSE(l_cursorID, l_str, DBMS_SQL.V7);
110
111 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':domain', l_account_rec.domain);
112 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':username', l_account_rec.email_user);
113
114 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 1, l_rule_count);
115
116 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
117
118 IF DBMS_SQL.FETCH_ROWS(l_cursorID) = 0 THEN
119 reportStr := 'no rows selected';
120 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
121 EXIT;
122 END IF;
123
124 DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_rule_count);
125
126 if (l_account_rec.account_flag = 'A') then
127 if (l_rule_count <> 0) then
128 statusStr := 'FAILURE';
129 l_mulrule_accounts := l_mulrule_accounts || l_account_rec.email_user || ', ';
130 fixInfo := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_HELP');
131 isFatal := 'FALSE';
132 end if;
133
134 else
135 if (l_rule_count = 0) then
136 statusStr := 'FAILURE';
137 l_norule_accounts := l_norule_accounts || l_account_rec.email_user || '@' || l_account_rec.domain || ', ';
138 fixInfo := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_HELP');
139 isFatal := 'FALSE';
140 elsif (l_rule_count > 1) then
141 statusStr := 'FAILURE';
142 l_mulrule_accounts := l_mulrule_accounts || l_account_rec.email_user || '@' || l_account_rec.domain || ', ';
143 fixInfo := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_HELP');
144 isFatal := 'FALSE';
145 else
146
147 -- Check the rule has valid apps link name
148 l_cursorID1 := DBMS_SQL.OPEN_CURSOR;
149
150 DBMS_SQL.PARSE(l_cursorID1, l_str1, DBMS_SQL.V7);
151 DBMS_SQL.BIND_VARIABLE(l_cursorID1, ':domain', l_account_rec.domain);
152 DBMS_SQL.BIND_VARIABLE(l_cursorID1, ':username', l_account_rec.email_user);
153
154 DBMS_SQL.DEFINE_COLUMN(l_cursorID1, 1, l_rule_name, 500);
155
156 l_dummy := DBMS_SQL.EXECUTE(l_cursorID1);
157
158 IF DBMS_SQL.FETCH_ROWS(l_cursorID1) = 0 THEN
159 reportStr := 'no rows selected';
160 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
161 EXIT;
162 END IF;
163
164 DBMS_SQL.COLUMN_VALUE(l_cursorID1, 1, l_rule_name);
165
166 select length(l_rule_name) into l_length from dual;
167
168 if (l_length < 35) then
169 statusStr := 'FAILURE';
170 l_invalidrule_accounts := l_invalidrule_accounts || l_account_rec.email_user || '@' || l_account_rec.domain || ', ';
171 fixInfo := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_HELP');
172 isFatal := 'FALSE';
173 else
174 select substr(l_rule_name, 35) into l_rule_apps_name from dual;
175
176 if (l_rule_apps_name <> l_apps_link) then
177 statusStr := 'FAILURE';
178 l_invalidrule_accounts := l_invalidrule_accounts || l_account_rec.email_user || '@' || l_account_rec.domain || ', ';
179 fixInfo := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_HELP');
180 isFatal := 'FALSE';
181 end if;
182 end if;
183
184 DBMS_SQL.CLOSE_CURSOR(l_cursorID1);
185 end if;
186 end if;
187
188 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
189
190
191 END LOOP; -- for email account
192
193 close email_account_cur;
194
195 if (statusStr = 'SUCCESS') then
196 reportStr := '<font color=blue> ' || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC3_SUCCESS') || ' </font><p>';
197 else
198 if (l_notauthen_accounts <> FND_API.G_MISS_CHAR) then
199 l_notauthen_accounts := RTRIM(l_notauthen_accounts, ', ');
200 errStr := errStr || l_notauthen_accounts || ' ' || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC2_ERROR1') || ' ';
201 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC2_SUM1'));
202 end if;
203 if (l_norule_accounts <> FND_API.G_MISS_CHAR) then
204 l_norule_accounts := RTRIM(l_norule_accounts, ', ');
205 errStr := errStr || l_norule_accounts || ' ' || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC3_ERROR2') || ' ';
206 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC3_SUM1'));
207 end if;
208 if (l_mulrule_accounts <> FND_API.G_MISS_CHAR) then
209 l_mulrule_accounts := RTRIM(l_mulrule_accounts, ', ');
210 errStr := errStr || l_mulrule_accounts || ' ' || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC3_ERROR3') || ' ';
211 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC3_SUM2'));
212 end if;
213 if (l_invalidrule_accounts <> FND_API.G_MISS_CHAR) then
214 l_invalidrule_accounts := RTRIM(l_invalidrule_accounts, ', ');
215 errStr := errStr || l_invalidrule_accounts || ' ' || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC3_ERROR4') || ' ';
216 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC3_SUM2'));
217 end if;
218 end if;
219
220 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
221
222 reports := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
223 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
224 END runTest;
225
226
227 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
228 BEGIN
229 name := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_COMPONENT1');
230 END getComponentName;
231
232 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
233 BEGIN
234 descStr := '<ul><li> ' || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC3_DESC') || ' <li> ' || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC3_DESC1') || ' </ul>';
235 END getTestDesc;
236
237 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
238 BEGIN
239 name := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TESTCASE_NAME3');
240 END getTestName;
241
242 FUNCTION getTestMode RETURN INTEGER IS
243 BEGIN
244 return JTF_DIAGNOSTIC_ADAPTUTIL.BOTH_MODE;
245 END getTestMode;
246
247 PROCEDURE getDefaultTestParams(defaultInputvalues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
248 tempInput JTF_DIAG_INPUTTBL;
249 BEGIN
250 null;
251 END getDefaultTestParams;
252
253 END;