[Home] [Help]
PACKAGE BODY: APPS.IEM_DIAG_KB_DOC_PVT
Source
1 PACKAGE BODY IEM_DIAG_KB_DOC_PVT AS
2 /* $Header: iemddocb.pls 115.4 2004/02/06 19:39:09 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 dummy_v2t JTF_DIAGNOSTIC_COREAPI.v2t;
20 statusStr VARCHAR2(50) := 'SUCCESS';
21 errStr VARCHAR2(4000);
22 fixInfo VARCHAR2(4000);
23 isFatal VARCHAR2(50);
24 l_count number := 0;
25 l_document_rec iem_diag_kb_doc_pvt.document_type;
26 Type document_rec is REF CURSOR ;
27 document_cur document_rec;
28 l_classification_id NUMBER;
29 l_imt_string varchar2(32000);
30 l_imt_string1 varchar2(5000);
31 l_imt_string2 varchar2(5000);
32 l_result_string varchar2(32000);
33 c_account iem_email_accounts.email_user%type;
34 c_domain iem_email_accounts.domain%type;
35 c_intent iem_classifications.classification%type;
36 l_theme iem_themes.theme%type;
37 l_classification_count number;
38 l_theme_count number;
39 l_account_count number;
40 l_kb_result_count number := 0;
41
42 CURSOR theme_csr IS
43 SELECT theme,score*10 score
44 FROM IEM_THEMES
45 WHERE CLASSIFICATION_ID=l_classification_id
46 AND QUERY_RESPONSE='R'
47 and score>0
48 order by score desc;
49 BEGIN
50 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
51 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
52 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
53
54 c_account := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Account', inputs);
55 c_domain := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Domain', inputs);
56 c_intent := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Intent', inputs);
57
58 select count(*) into l_account_count from iem_email_accounts
59 where UPPER(email_user)=UPPER(c_account) and UPPER(domain)=UPPER(c_domain);
60
61 if (l_account_count = 0) then
62 statusStr := 'FAILURE';
63 errStr := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC9_ERROR1');
64 reportStr := reportStr || '<font color=red> ' || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC9_ERROR1') || '</font><br>';
65 fixInfo := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_HELP');
66 isFatal := 'FALSE';
67 else
68
69
70 select count(*) into l_classification_count from iem_email_accounts a, iem_classifications b
71 where a.email_account_id=b.email_account_id and upper(a.email_user)=upper(c_account)
72 and UPPER(a.domain)=UPPER(c_domain)
73 and UPPER(b.classification)=UPPER(c_intent);
74
75 if (l_classification_count = 0) then
76 statusStr := 'FAILURE';
77 errStr := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC9_ERROR2');
78 reportStr := reportStr || '<font color=red> ' || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC9_ERROR2') || '</font><br>';
79 fixInfo := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_HELP');
80 isFatal := 'FALSE';
81 else
82
83 select b.classification_id into l_classification_id from iem_email_accounts a, iem_classifications b
84 where a.email_account_id=b.email_account_id and upper(a.email_user)=upper(c_account)
85 and UPPER(b.classification)=UPPER(c_intent);
86
87 l_imt_string1 := 'select b.item_id,fl.file_name , cim.channel_category_id , score(1) score from jtf_amv_items_vl b, jtf_amv_attachments a, fnd_lobs fl, amv_c_chl_item_match cim where contains(file_data,''';
88
89 l_imt_string2 := ''', 1)>0 and b.application_id = 520 and nvl(b.effective_start_date, sysdate) <= sysdate+1 and nvl(b.expiration_date, sysdate) >= sysdate
90 and b.item_id = a.attachment_used_by_id and a.file_id = fl.file_id and b.item_id = cim.item_id and cim.channel_id is null
91 and cim.approval_status_type = ''APPROVED'' AND cim.table_name_code = ''ITEM'' and cim.available_for_channel_date <= sysdate order by score desc';
92
93 SELECT count(*) into l_theme_count FROM IEM_THEMES
94 WHERE CLASSIFICATION_ID=l_classification_id AND QUERY_RESPONSE='R' and score>0;
95
96 if (l_theme_count = 0) then
97 statusStr := 'FAILURE';
98 errStr := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC9_ERROR3');
99 reportStr := reportStr || '<font color=red> ' || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC9_ERROR3') || '</font><br>';
100 fixInfo := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_HELP');
101 isFatal := 'FALSE';
102 else
103
104 FOR v1 in theme_csr LOOP
105
106 if v1.score<.1 then
107 v1.score:=.1;
108 end if;
109 if v1.score>10 then
110 v1.score:=10;
111 end if;
112
113 select replace(v1.theme, '''', '') into l_theme from dual;
114
115 l_imt_string:=l_imt_string||'about ('||l_theme||')*'||v1.score||',';
116 l_count:=l_count+1;
117 EXIT when l_count=20; -- Top 20 Response theme from classification
118 END LOOP;
119
120 l_imt_string:=substr(l_imt_string,1,length(l_imt_string)-1);
121
122 l_imt_string := l_imt_string1 || l_imt_string || l_imt_string2;
123
124 -- Start looping through all email accounts defined in SSA
125 OPEN document_cur FOR l_imt_string;
126 LOOP
127 FETCH document_cur into l_document_rec;
128 EXIT WHEN document_cur%NOTFOUND;
129
130 l_kb_result_count := l_kb_result_count + 1;
131 l_result_string := l_result_string || '<li>' || l_document_rec.file_name || ' (' || l_document_rec.score || '%)' || '</li>';
132 END LOOP;
133
134 if (l_kb_result_count = 0) then
135 l_result_string := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC9_SUM1');
136 end if;
137
138 reportStr := '<font color=blue> <ul>' || l_result_string || '</ul> </font><p>';
139
140 end if; -- if (l_account_count = 0) then
141 end if; -- if (l_theme_count = 0) then
142 end if; -- if (l_classification_count = 0) then
143 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
144
145 reports := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
146 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
147 END runTest;
148
149
150 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
151 BEGIN
152 name := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_COMPONENT4');
153 END getComponentName;
154
155 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
156 BEGIN
157 descStr := '<ul><li> ' || FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TC9_DESC1') || ' </ul>';
158 END getTestDesc;
159
160 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
161 BEGIN
162 name := FND_MESSAGE.GET_STRING('IEM', 'IEM_DIAG_TESTCASE_NAME9');
163 END getTestName;
164
165 FUNCTION getTestMode RETURN INTEGER IS
166 BEGIN
167 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
168 END getTestMode;
169
170 PROCEDURE getDefaultTestParams(defaultInputvalues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
171 tempInput JTF_DIAG_INPUTTBL;
172 BEGIN
173 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
174 tempInput :=
175 JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput, 'Account','');
176 tempInput :=
177 JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput, 'Domain','');
178 tempInput :=
179 JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput, 'Intent','');
180 defaultInputValues := tempInput;
181 EXCEPTION
182 WHEN OTHERS THEN
183 defaultInputvalues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
184 END getDefaultTestParams;
185
186 END;