[Home] [Help]
PACKAGE BODY: APPS.IEX_WEBDIR_PVT
Source
1 PACKAGE BODY iex_WebDir_Pvt AS
2 /* $Header: iexvadwb.pls 120.1 2005/07/06 19:23:34 jypark noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) :='iex_WebDir_Pvt';
4 G_FILE_NAME CONSTANT VARCHAR2(12) :='iexvadws.pls';
5
6 G_APPL_ID NUMBER;
7 G_LOGIN_ID NUMBER;
8 G_PROGRAM_ID NUMBER;
9 G_USER_ID NUMBER;
10 G_REQUEST_ID NUMBER;
11
12 PG_DEBUG NUMBER(2);
13
14 PROCEDURE Create_WebAssist(
15 p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2,
17 p_commit IN VARCHAR2,
18 p_validation_level IN NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 p_assist_rec IN assist_rec_type,
23 p_web_assist_rec IN web_assist_rec_type,
24 p_web_search_rec IN web_search_rec_type,
25 p_query_string_rec IN query_string_rec_type
26 )
27 IS
28 l_api_version CONSTANT NUMBER := 1.0;
29 l_api_name CONSTANT VARCHAR2(30) := 'Create_WebAssist';
30 l_return_status VARCHAR2(1);
31 l_msg_count NUMBER;
32 l_msg_data VARCHAR2(32767);
33
34 l_assist_rec IEX_WEBDIR_PKG.assist_rec_type;
35 l_web_assist_rec IEX_WEBDIR_PKG.web_assist_rec_type;
36 l_web_search_rec IEX_WEBDIR_PKG.web_search_rec_type;
37 l_query_string_rec IEX_WEBDIR_PKG.query_string_rec_type;
38 l_assist_id NUMBER;
39 l_web_assist_id NUMBER;
40 l_search_id NUMBER;
41 l_query_string_id NUMBER;
42 l_user_id NUMBER;
43 l_login_id NUMBER;
44
45 CURSOR c_web_assist IS
46 SELECT WEB_ASSIST_ID
47 FROM IEX_WEB_ASSISTS
48 WHERE PROXY_HOST = l_web_assist_rec.proxy_host
49 AND PROXY_PORT = l_web_assist_rec.proxy_port;
50
51 CURSOR c_web_search IS
52 SELECT SEARCH_ID
53 FROM IEX_WEB_SEARCHES
54 WHERE SEARCH_URL = l_web_search_rec.search_url
55 AND CGI_SERVER = l_web_search_rec.cgi_server;
56
57 CURSOR c_query_seq IS
58 SELECT IEX_QUERY_STRINGS_S.NEXTVAL
59 FROM SYS.DUAL;
60
61 BEGIN
62 -- Standard begin of API savepoint
63 SAVEPOINT Create_WebAssist_PVT;
64
65 -- Check p_init_msg_list
66 IF FND_API.to_Boolean( p_init_msg_list ) THEN
67 FND_MSG_PUB.initialize;
68 END IF;
69
70 -- Implementation of User Hooks
71 /* Copy all parameters to local variables to be passed to Pre, Post AND Business APIs */
72 /* l_rec - will be used as In Out NOCOPY parameter in pre/post/Business API calls */
73 /* l_return_status - will be a out NOCOPY variable to get return code FROM called APIs */
74
75 l_user_id := fnd_profile.value('USER_ID');
76 l_login_id := fnd_profile.value('LOGIN_ID');
77
78 l_assist_rec.assistance_type := 'WEB_ASSIST';
79 l_assist_rec.object_version_number := 1.0;
80 l_assist_rec.last_update_date := sysdate;
81 l_assist_rec.creation_date := sysdate;
82 l_assist_rec.created_by := l_user_id;
83 l_assist_rec.last_updated_by := l_user_id;
84 l_assist_rec.last_update_login := l_login_id;
85
86 l_web_assist_rec.object_version_number := 1.0;
87 l_web_assist_rec.proxy_host := p_web_assist_rec.proxy_host;
88 l_web_assist_rec.proxy_port := p_web_assist_rec.proxy_port;
89 l_web_assist_rec.enabled_flag := p_web_search_rec.enabled_flag;
90 l_web_assist_rec.last_update_date := sysdate;
91 l_web_assist_rec.creation_date := sysdate;
92 l_web_assist_rec.created_by := l_user_id;
93 l_web_assist_rec.last_updated_by := l_user_id;
94 l_web_assist_rec.last_update_login := l_login_id;
95
96 l_web_search_rec.object_version_number := 1.0;
97 l_web_search_rec.enabled_flag := p_web_search_rec.enabled_flag;
98 l_web_search_rec.search_url := p_web_search_rec.search_url;
99 l_web_search_rec.cgi_server := p_web_search_rec.cgi_server;
100 l_web_search_rec.next_page_ident := p_web_search_rec.next_page_ident;
101 l_web_search_rec.max_nbr_pages := p_web_search_rec.max_nbr_pages;
102 l_web_search_rec.last_update_date := sysdate;
103 l_web_search_rec.creation_date := sysdate;
104 l_web_search_rec.created_by := l_user_id;
105 l_web_search_rec.last_updated_by := l_user_id;
106 l_web_search_rec.last_update_login := l_login_id;
107 l_web_search_rec.directory_assist_flag := p_web_search_rec.directory_assist_flag;
108
109
110 l_query_string_rec.object_version_number := 1.0;
111 l_query_string_rec.switch_separator := p_query_string_rec.switch_separator;
112 l_query_string_rec.url_separator := p_query_string_rec.url_separator;
113 l_query_string_rec.header_const := p_query_string_rec.header_const;
114 l_query_string_rec.trailer_const := p_query_string_rec.trailer_const;
115 l_query_string_rec.enabled_flag := p_web_search_rec.enabled_flag;
116 l_query_string_rec.last_update_date := sysdate;
117 l_query_string_rec.creation_date := sysdate;
118 l_query_string_rec.created_by := l_user_id;
119 l_query_string_rec.last_updated_by := l_user_id;
120 l_query_string_rec.last_update_login := l_login_id;
121
122
123 -- Initialize API return status to success
124 x_return_status := FND_API.G_RET_STS_SUCCESS;
125
126
127 OPEN c_web_assist;
128 FETCH c_web_assist INTO l_web_assist_id;
129 IF c_web_assist%NOTFOUND THEN
130 DECLARE
131 CURSOR c_web_assist_seq IS
132 SELECT IEX_WEB_ASSISTS_S.NEXTVAL
133 FROM SYS.DUAL;
134 BEGIN
135 CLOSE c_web_assist;
136 OPEN c_web_assist_seq;
137 FETCH c_web_assist_seq INTO l_web_assist_id;
138 CLOSE c_web_assist_seq;
139 EXCEPTION
140 WHEN OTHERS THEN
141 null;
142 END;
143 ELSE
144 --dbms_output.put_line('OLD ID1 > ' || l_web_assist_id);
145 CLOSE c_web_assist;
146 END IF;
147
148 OPEN c_web_search;
149 FETCH c_web_search INTO l_search_id;
150 IF c_web_search%NOTFOUND THEN
151 DECLARE
152 CURSOR c_search_seq IS
153 SELECT IEX_WEB_SEARCHES_S.NEXTVAL
154 FROM SYS.DUAL;
155 BEGIN
156 CLOSE c_web_search;
157 OPEN c_search_seq;
158 FETCH c_search_seq INTO l_search_id;
159 CLOSE c_search_seq;
160 EXCEPTION
161 WHEN OTHERS THEN
162 null;
163 END;
164 ELSE
165 CLOSE c_web_search;
166 END IF;
167
168 OPEN c_query_seq;
169 FETCH c_query_seq INTO l_query_string_id;
170 CLOSE c_query_seq;
171
172 l_assist_rec.assist_id := l_assist_id;
173
174 l_web_assist_rec.web_assist_id := l_web_assist_id;
175 l_web_assist_rec.assist_id := l_assist_id;
176
177 l_web_search_rec.search_id := l_search_id;
178 l_web_search_rec.web_assist_id := l_web_assist_id;
179
180 l_query_string_rec.query_string_id := l_query_string_id;
181 l_query_string_rec.search_id := l_search_id;
182
183 IEX_WEBDIR_PKG.Create_WebAssist(
184 P_API_VERSION => l_api_version,
185 P_INIT_MSG_LIST => p_init_msg_list,
186 P_COMMIT => p_commit,
187 P_VALIDATION_LEVEL => p_validation_level,
188 X_RETURN_STATUS => x_return_status,
189 X_MSG_COUNT => x_msg_count,
190 X_MSG_DATA => x_msg_data,
191 p_assist_rec => l_assist_rec,
192 p_web_assist_rec => l_web_assist_rec,
193 p_web_search_rec => l_web_search_rec,
194 p_query_string_rec => l_query_string_rec
195 );
196
197 --
198 -- Normal API Exception handling, IF exception occurs outside of phone processing loop
199 --
200 EXCEPTION
201
202 WHEN FND_API.G_EXC_ERROR THEN
203 ROLLBACK TO Create_WebAssist_PVT;
204 x_return_status := FND_API.G_RET_STS_ERROR ;
205 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
206 p_data => x_msg_data );
207 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
208 ROLLBACK TO Create_WebAssist_PVT;
209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
210 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
211 p_data => x_msg_data );
212 WHEN OTHERS THEN
213 ROLLBACK TO Create_WebAssist_PVT;
214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
215 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
216 THEN
217 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
218 END IF;
219 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
220 p_data => x_msg_data );
221 END Create_WebAssist;
222
223 PROCEDURE Update_WebAssist(
224 p_api_version IN NUMBER,
225 p_init_msg_list IN VARCHAR2,
226 p_commit IN VARCHAR2,
227 p_validation_level IN NUMBER,
228 x_return_status OUT NOCOPY VARCHAR2,
229 x_msg_count OUT NOCOPY NUMBER,
230 x_msg_data OUT NOCOPY VARCHAR2,
231 p_assist_rec IN assist_rec_type,
232 p_web_assist_rec IN web_assist_rec_type,
233 p_web_search_rec IN web_search_rec_type,
234 p_query_string_rec IN query_string_rec_type
235 )
236 IS
237 l_api_version CONSTANT NUMBER := 1.0;
238 l_api_name CONSTANT VARCHAR2(30) := 'Create_WebAssist';
239 l_return_status VARCHAR2(1);
240 l_msg_count NUMBER;
241 l_msg_data VARCHAR2(32767);
242
243 l_assist_rec IEX_WEBDIR_PKG.assist_rec_type;
244 l_web_assist_rec IEX_WEBDIR_PKG.web_assist_rec_type;
245 l_web_search_rec IEX_WEBDIR_PKG.web_search_rec_type;
246 l_query_string_rec IEX_WEBDIR_PKG.query_string_rec_type;
247
248 l_user_id NUMBER;
249 l_login_id NUMBER;
250
251 BEGIN
252 -- Standard begin of API savepoint
253 SAVEPOINT Update_WebAssist_PVT;
254
255 -- Check p_init_msg_list
256 IF FND_API.to_Boolean( p_init_msg_list ) THEN
257 FND_MSG_PUB.initialize;
258 END IF;
259
260 -- Implementation of User Hooks
261 /* Copy all parameters to local variables to be passed to Pre, Post AND Business APIs */
262 /* l_rec - will be used as In Out NOCOPY parameter in pre/post/Business API calls */
263 /* l_return_status - will be a out NOCOPY variable to get return code FROM called APIs */
264
265 l_user_id := fnd_profile.value('USER_ID');
266 l_login_id := fnd_profile.value('LOGIN_ID');
267
268 l_assist_rec.assist_id := p_assist_rec.assist_id;
269 l_assist_rec.assistance_type := p_assist_rec.assistance_type;
270 l_assist_rec.object_version_number := p_assist_rec.object_version_number;
271 l_assist_rec.last_update_date := sysdate;
272 l_assist_rec.last_updated_by := l_user_id;
273 l_assist_rec.last_update_login := l_login_id;
274 l_assist_rec.creation_date := p_assist_rec.creation_date;
275 l_assist_rec.created_by := p_assist_rec.created_by;
276
277 l_web_assist_rec.web_assist_id := p_web_assist_rec.web_assist_id;
278 l_web_assist_rec.assist_id := p_web_assist_rec.assist_id;
279 l_web_assist_rec.object_version_number := 1.0;
280 l_web_assist_rec.proxy_host := p_web_assist_rec.proxy_host;
281 l_web_assist_rec.proxy_port := p_web_assist_rec.proxy_port;
282 l_web_assist_rec.enabled_flag := p_web_search_rec.enabled_flag;
283 l_web_assist_rec.last_update_date := sysdate;
284 l_web_assist_rec.last_updated_by := l_user_id;
285 l_web_assist_rec.last_update_login := l_login_id;
286 l_web_assist_rec.creation_date := p_web_assist_rec.creation_date;
287 l_web_assist_rec.created_by := p_web_assist_rec.created_by;
288
289 l_web_search_rec.search_id := p_web_search_rec.search_id;
290 l_web_search_rec.web_assist_id := p_web_search_rec.web_assist_id;
291 l_web_search_rec.object_version_number := 1.0;
295 l_web_search_rec.next_page_ident := p_web_search_rec.next_page_ident;
292 l_web_search_rec.enabled_flag := p_web_search_rec.enabled_flag;
293 l_web_search_rec.search_url := p_web_search_rec.search_url;
294 l_web_search_rec.cgi_server := p_web_search_rec.cgi_server;
296 l_web_search_rec.max_nbr_pages := p_web_search_rec.max_nbr_pages;
297 l_web_search_rec.last_update_date := sysdate;
298 l_web_search_rec.last_updated_by := l_user_id;
299 l_web_search_rec.last_update_login := l_login_id;
300 l_web_search_rec.directory_assist_flag := p_web_search_rec.directory_assist_flag;
301 l_web_search_rec.creation_date := p_web_search_rec.creation_date;
302 l_web_search_rec.created_by := p_web_search_rec.created_by;
303
304
305 l_query_string_rec.query_string_id := p_query_string_rec.query_string_id;
306 l_query_string_rec.search_id := p_query_string_rec.search_id;
307 l_query_string_rec.object_version_number := 1.0;
308 l_query_string_rec.switch_separator := p_query_string_rec.switch_separator;
309 l_query_string_rec.url_separator := p_query_string_rec.url_separator;
310 l_query_string_rec.header_const := p_query_string_rec.header_const;
311 l_query_string_rec.trailer_const := p_query_string_rec.trailer_const;
312 l_query_string_rec.enabled_flag := p_web_search_rec.enabled_flag;
313 l_query_string_rec.last_update_date := sysdate;
314 l_query_string_rec.last_updated_by := l_user_id;
315 l_query_string_rec.last_update_login := l_login_id;
316 l_query_string_rec.creation_date := p_query_string_rec.creation_date;
317 l_query_string_rec.created_by := p_query_string_rec.created_by;
318
319
320 -- Initialize API return status to success
321 x_return_status := FND_API.G_RET_STS_SUCCESS;
322
323
324 IEX_WEBDIR_PKG.Update_WebAssist(
325 P_API_VERSION => l_api_version,
326 P_INIT_MSG_LIST => p_init_msg_list,
327 P_COMMIT => p_commit,
328 P_VALIDATION_LEVEL => p_validation_level,
329 X_RETURN_STATUS => x_return_status,
330 X_MSG_COUNT => x_msg_count,
331 X_MSG_DATA => x_msg_data,
332 p_assist_rec => l_assist_rec,
333 p_web_assist_rec => l_web_assist_rec,
334 p_web_search_rec => l_web_search_rec,
335 p_query_string_rec => l_query_string_rec
336 );
337
338 --
339 -- Normal API Exception handling, IF exception occurs outside of phone processing loop
340 --
341 EXCEPTION
342
343 WHEN FND_API.G_EXC_ERROR THEN
344 ROLLBACK TO Update_WebAssist_PVT;
345 x_return_status := FND_API.G_RET_STS_ERROR ;
346 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
347 p_data => x_msg_data );
348 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
349 ROLLBACK TO Update_WebAssist_PVT;
350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
351 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
352 p_data => x_msg_data );
353 WHEN OTHERS THEN
354 ROLLBACK TO Update_WebAssist_PVT;
355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
356 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
357 THEN
358 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
359 END IF;
360 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
361 p_data => x_msg_data );
362 END Update_WebAssist;
363 BEGIN
364 G_APPL_ID := FND_GLOBAL.Prog_Appl_Id;
365 G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
366 G_PROGRAM_ID := FND_GLOBAL.Conc_Program_Id;
367 G_USER_ID := FND_GLOBAL.User_Id;
368 G_REQUEST_ID := FND_GLOBAL.Conc_Request_Id;
369
370 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
371
372 END iex_WebDir_Pvt;