1 PACKAGE BODY IEM_SEARCHMESSAGE_PVT as
2 /* $Header: iemvmshb.pls 120.3.12020000.2 2012/07/13 19:32:08 siahmed ship $*/
3 G_PKG_NAME CONSTANT varchar2(30) :='IEM_SEARCHMESSAGE_PVT ';
4 PROCEDURE searchmessages (p_api_version_number IN NUMBER,
5 p_init_msg_list IN VARCHAR2 ,
6 p_commit IN VARCHAR2 ,
7 p_email_account_id IN NUMBER,
8 p_resource_id IN NUMBER,
9 p_email_queue IN varchar2,
10 p_sent_date_from IN varchar2,
11 p_sent_date_to IN varchar2,
12 p_received_date_from in date,
13 p_received_date_to in date,
14 p_from_str in varchar2,
15 p_recepients in varchar2,
16 p_cc_flag in varchar2,
17 p_subject in varchar2,
18 p_message_body in varchar2,
19 p_customer_id in number,
20 p_classification in varchar2,
21 p_resolved_agent in varchar2,
22 p_resolved_group in varchar2,
23 x_message_tbl out nocopy message_rec_tbl,
24 x_return_status OUT NOCOPY VARCHAR2,
25 x_msg_count OUT NOCOPY NUMBER,
26 x_msg_data OUT NOCOPY VARCHAR2
27 ) IS
28 l_cursorid INTEGER;
29 l_dummy INTEGER;
30 l_api_version_number number:=1.0;
31 l_api_name varchar2(30):='searchmessage';
32 l_str varchar2(1000);
33 l_exe_Str varchar2(2000);
34 Type get_data is REF CURSOR;-- RETURN acq_email_info_tbl;
35 email_cur get_data;
36 l_temp_tbl message_rec_tbl;
37 l_counter number:=1;
38 l_message_id iem_arch_msgdtls.message_id%type;
39 l_ih_media_item_id iem_arch_msgdtls.ih_media_item_id%type;
40 l_from_str iem_arch_msgdtls.from_str%type;
41 l_from_str1 iem_arch_msgdtls.from_str%type;
42 l_to_str iem_arch_msgdtls.to_str%type;
43 l_to_str1 iem_arch_msgdtls.to_str%type;
44 l_subject iem_arch_msgdtls.subject%type;
45 l_subject1 iem_arch_msgdtls.subject%type;
46 l_sent_date iem_arch_msgdtls.sent_date%type;
47 l_Str1 varchar2(500);
48 BEGIN
49 -- Standard call to check for call compatibility.
50 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
51 p_api_version_number,
52 l_api_name,
53 G_PKG_NAME)
54 THEN
55 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
56 END IF;
57 SAVEPOINT select_mail_count_pvt;
58 IF p_email_queue <> 'I' THEN -- Search non draft message
59
60 l_str:='select message_id,ih_media_item_id,from_str,to_str,subject, to_char(to_date(substr(sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH''),''MM/DD/RRRR HH24:MI:SS'')';
61 l_str1:=' from iem_arch_msgdtls where email_account_id=:id and mailproc_status=:q_status';
62 l_str:=l_str||l_str1;
63
64 If p_sent_date_from is not null then
65 l_str:=l_str||' AND to_date(substr(sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') >= to_date(:f_dt,''mm-dd-rrrr hh24:mi:ss'',''NLS_DATE_LANGUAGE=ENGLISH'')';
66 end if;
67 if p_sent_date_to is not null then
68 l_str:=l_str||' AND to_date(substr(sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') <= to_date(:t_dt,''mm-dd-rrrr hh24:mi:ss'',''NLS_DATE_LANGUAGE=ENGLISH'')';
69 end if;
70
71 IF p_subject is not null then
72 l_str:=l_str||' AND upper(subject) like :subject';
73 END IF;
74 IF p_from_str is not null then
75 l_str:=l_str||' AND upper(from_str) like :from1';
76 END IF;
77 IF p_recepients is not null and nvl(p_cc_flag,' ')<>'Y' then
78 l_str:=l_str||' AND upper(to_str) like :tostr';
79 END IF;
80 IF p_recepients is not null and nvl(p_cc_flag,' ')='Y' then
81 l_str:=l_str||' AND (upper(to_str) like :tostr OR upper(cc_str) like :tostr)';
82 END IF;
83
84 ELSE
85
86 l_str:='select a.message_id,b.ih_media_item_id,a.from_str,a.to_str,a.subject,to_char(to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH''),''MM/DD/RRRR HH24:MI:SS'')';
87 l_str1:=' from iem_ms_base_headers a,iem_rt_proc_emails b,iem_rt_media_items c where a.message_id=b.message_id and b.resource_id=:resource_id and b.email_account_id=:id and b.message_id=c.message_id and c.expire=''N''';
88 l_str:=l_str||l_str1;
89 If p_sent_date_from is not null then
90 l_str:=l_str||' AND to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') >= to_date(:f_dt,''mm-dd-rrrr hh24:mi:ss'',''NLS_DATE_LANGUAGE=ENGLISH'')';
91 end if;
92
93 if p_sent_date_to is not null then
94 l_str:=l_str||' AND to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') <= to_date(:t_dt,''mm-dd-rrrr hh24:mi:ss'',''NLS_DATE_LANGUAGE=ENGLISH'')';
95 end if;
96
97 IF p_subject is not null then
98 l_str:=l_str||' AND upper(a.subject) like :subject';
99 END IF;
100 IF p_from_str is not null then
101 l_str:=l_str||' AND upper(a.from_str) like :from1';
102 END IF;
103 IF p_recepients is not null and nvl(p_cc_flag,' ')<>'Y' then
104 l_str:=l_str||' AND upper(a.to_str) like :tostr';
105 END IF;
106 IF p_recepients is not null and nvl(p_cc_flag,' ')='Y' then
107 l_str:=l_str||' AND (upper(a.to_str) like :tostr OR upper(a.cc_str) like :tostr)';
108 END IF;
109 END IF; -- End if for p_email_queue<>'I'
110 l_cursorID := DBMS_SQL.OPEN_CURSOR;
111 DBMS_SQL.PARSE(l_cursorID, l_str, DBMS_SQL.native);
112 DBMS_SQL.BIND_VARIABLE(l_cursorid, ':id', p_email_account_id);
113 IF p_email_queue <>'I' THEN
114 DBMS_SQL.BIND_VARIABLE(l_cursorid, ':q_status', p_email_queue);
115 END IF;
116 IF p_resource_id is not null then
117 DBMS_SQL.BIND_VARIABLE(l_cursorid, ':resource_id', p_resource_id);
118 END IF;
119 If p_sent_date_from is not null then
120 DBMS_SQL.BIND_VARIABLE(l_cursorid, ':f_dt', p_sent_date_from);
121 end if;
122
123 if p_sent_date_to is not null then
124 DBMS_SQL.BIND_VARIABLE(l_cursorid, ':t_dt', p_sent_date_to);
125 end if;
126 IF p_subject is not null then
127 l_subject1:=upper(p_subject);
128 DBMS_SQL.BIND_VARIABLE(l_cursorid, ':subject', l_subject1);
129 END IF;
130 IF p_from_str is not null then
131 l_from_str1:='%'||upper(p_from_str)||'%';
132 DBMS_SQL.BIND_VARIABLE(l_cursorid, ':from1', l_from_str1);
133 END IF;
134 IF p_recepients is not null then
135 l_to_str1:='%'||upper(p_recepients)||'%';
136 DBMS_SQL.BIND_VARIABLE(l_cursorid,':tostr',l_to_str1);
137 END IF;
138 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 1, l_message_id);
139 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 2, l_ih_media_item_id);
140 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 3, l_from_str,2000);
141 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 4, l_to_str,2000);
142 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 5, l_subject,2000);
143 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 6, l_sent_date,60);
144
145 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
146 LOOP
147 IF (DBMS_SQL.FETCH_ROWS(l_cursorid) = 0) THEN
148 EXIT;
149 END IF;
150 DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_message_id);
151 DBMS_SQL.COLUMN_VALUE(l_cursorID, 2, l_ih_media_item_id);
152 DBMS_SQL.COLUMN_VALUE(l_cursorID, 3, l_from_str);
153 DBMS_SQL.COLUMN_VALUE(l_cursorID, 4, l_to_str);
154 DBMS_SQL.COLUMN_VALUE(l_cursorID, 5, l_subject);
155 DBMS_SQL.COLUMN_VALUE(l_cursorID, 6, l_sent_date);
156
157 x_message_tbl(l_counter).message_id:=l_message_id;
158 x_message_tbl(l_counter).ih_media_item_id:=l_ih_media_item_id;
159 x_message_tbl(l_counter).from_str:=l_from_str;
160 x_message_tbl(l_counter).to_str:=l_to_str;
161 x_message_tbl(l_counter).subject:=l_subject;
162 x_message_tbl(l_counter).sent_date:=l_sent_date;
163 l_counter:=l_counter+1;
164 END LOOP;
165
166 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
167
168 IF p_commit='T' THEN
169 COMMIT WORK;
170 END IF;
171 x_return_Status:='S';
172 -- Standard callto get message count and if count is 1, get message info.
173 FND_MSG_PUB.Count_And_Get
174 ( p_count => x_msg_count,
175 p_data => x_msg_data
176 );
177 EXCEPTION
178 WHEN FND_API.G_EXC_ERROR THEN
179 ROLLBACK TO select_mail_count_pvt;
180 x_return_status := FND_API.G_RET_STS_ERROR ;
181 FND_MSG_PUB.Count_And_Get
182 ( p_count => x_msg_count,
183 p_data => x_msg_data
184 );
185 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
186 ROLLBACK TO select_mail_count_pvt;
187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
188 FND_MSG_PUB.Count_And_Get
189 ( p_count => x_msg_count,
190 p_data => x_msg_data
191 );
192 WHEN OTHERS THEN
193 ROLLBACK TO select_mail_count_pvt;
194 x_return_status := FND_API.G_RET_STS_ERROR;
195 IF FND_MSG_PUB.Check_Msg_Level
196 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
197 THEN
198 FND_MSG_PUB.Add_Exc_Msg
199 ( G_PKG_NAME ,
200 l_api_name
201 );
202 END IF;
203 FND_MSG_PUB.Count_And_Get
204 ( p_count => x_msg_count ,
205 p_data => x_msg_data
206 );
207 END searchmessages;
208 END IEM_SEARCHMESSAGE_PVT ;
[Home] [Help]