[Home] [Help]
PACKAGE BODY: APPS.WSH_OTM_HTTP_UTL
Source
1 PACKAGE BODY WSH_OTM_HTTP_UTL AS
2 /* $Header: WSHGLHUB.pls 120.2.12010000.2 2008/12/10 10:28:03 anvarshn ship $ */
3
4
5
6 -- GLOBAL VARIABLES/CONSTANTS
7 -- --------------------------
8 G_WSH_OTM_SERVLET_URI VARCHAR2(4000) := NULL;
9 G_WSH_OTM_WS_ENDPOINT VARCHAR2(4000) := NULL;
10 G_WSH_OTM_PROXY_SERVER VARCHAR2(1000) := NULL;
11 G_WSH_OTM_PROXY_PORT NUMBER;
12 G_WSH_TKT_OP_CODE VARCHAR2(255) := 'WshRateOTM';
13 G_WSH_TKT_ARGUMENT_VALUE VARCHAR2(4000) := 'WshRate';
14 G_WSH_TKT_LIFESPAN NUMBER := 36000; --10 hrs.
15 G_ENC_STYLE VARCHAR2(1000) := NULL;
16 G_WALLET_PATH VARCHAR2(32767) := NULL;
17 G_WALLET_PASSWORD VARCHAR2(1000) := NULL;
18 G_OTM_UNAME VARCHAR2(2000) := NULL;
19 G_OTM_PSWD VARCHAR2(2000) := NULL;
20 G_NEWLINE_CHARACTER CHAR(1) := FND_GLOBAL.Newline;
21 G_HARD_CHAR VARCHAR2(1) := '&';
22 G_PARAM_NAME_DBC VARCHAR2(3) := 'dbc';
23 G_PARAM_NAME_USERID VARCHAR2(6) := 'userId';
24 G_PARAM_NAME_RESPID VARCHAR2(6) := 'respId';
25 G_PARAM_NAME_TICKET VARCHAR2(1) := 't';
26 G_PARAM_NAME_RIQ_INPUT VARCHAR2(3) := 'riq';
27 G_PARAM_NAME_ENC_STYLE VARCHAR2(3) := 'enc';
28 G_PARAM_NAME_OTM_UNAME VARCHAR2(10) := 'oun';
29 G_PARAM_NAME_OTM_PSSWD VARCHAR2(10) := 'oup';
30 G_PARAM_NAME_RESP_APPL_ID VARCHAR2(10) := 'respApplId';
31 G_PARAM_NAME_TKT_EXP_DATE VARCHAR2(3) := 'ted';
32 G_PARAM_NAME_WS_END_POINT VARCHAR2(3) := 'wep';
33 G_TKT_ERROR EXCEPTION;
34 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_OTM_HTTP_UTL';
35 -- -----------------------------------------------------------------------------------
36
37
38 -- Load profile values in global variables
39 -- -----------------------------------------------------------------------------------
40 FUNCTION load_profiles RETURN VARCHAR2
41 IS
42 l_debug_on BOOLEAN;
43 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'load_profiles';
44 j NUMBER;
45 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
46 wsh_otm_endpoint_null EXCEPTION;
47 wsh_otm_uname_null EXCEPTION;
48 wsh_otm_pswd_null EXCEPTION;
49 BEGIN
50
51 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
52 IF l_debug_on IS NULL
53 THEN
54 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
55 END IF;
56
57 IF l_debug_on THEN
58 WSH_DEBUG_SV.push(l_module_name);
59 END IF;
60
61 G_ENC_STYLE := FND_PROFILE.VALUE('ICX_CLIENT_IANA_ENCODING');
62 G_WSH_OTM_SERVLET_URI := FND_PROFILE.value('APPS_FRAMEWORK_AGENT')||'/OA_HTML/wshRequestRates';
63 G_WSH_OTM_WS_ENDPOINT := FND_PROFILE.VALUE('WSH_OTM_SERVLET_URI');
64 G_WSH_OTM_PROXY_SERVER := FND_PROFILE.VALUE('WSH_OTM_PROXY_SERVER');
65 G_WSH_OTM_PROXY_PORT := FND_PROFILE.VALUE('WSH_OTM_PROXY_PORT');
66 G_OTM_UNAME := FND_PROFILE.VALUE('WSH_OTM_USER_ID');
67 G_OTM_PSWD := FND_PROFILE.VALUE('WSH_OTM_PASSWORD');
68 G_WALLET_PATH := nvl(G_WALLET_PATH,'file:/'||FND_PROFILE.VALUE('FND_DB_WALLET_DIR'));
69 G_WALLET_PASSWORD := nvl(G_WALLET_PASSWORD,FND_PROFILE.VALUE('WSH_OTM_WALLET_PASSWORD'));
70
71 --TODO: Look into encrypt/decrypt the uname / pswd.
72
73 --preprocess the URL strings to make sure they are correct without newline character
74 IF ((G_WSH_OTM_WS_ENDPOINT IS NULL ) OR (length(G_WSH_OTM_WS_ENDPOINT) = 0 ) )
75 THEN
76 FND_MESSAGE.SET_NAME('WSH','WSH_PROFILE_NOT_SET_ERR');
77 FND_MESSAGE.SET_TOKEN('PRF_NAME',fnd_message.get_string('WSH','WSH_OTM_SERVLET_URI'));
78 FND_MSG_PUB.ADD;
79 IF l_debug_on THEN
80 WSH_DEBUG_SV.logmsg(l_module_name,'OTM Servlet URI profile can not be null ',WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
81 END IF;
82 END IF;
83
84 IF ( (G_OTM_UNAME IS NULL ) OR ( length(G_OTM_UNAME) = 0)) THEN
85 FND_MESSAGE.SET_NAME('WSH','WSH_PROFILE_NOT_SET_ERR');
86 FND_MESSAGE.SET_TOKEN('PRF_NAME',fnd_message.get_string('WSH','WSH_OTM_USER_ID'));
87 FND_MSG_PUB.ADD;
88 IF l_debug_on THEN
89 WSH_DEBUG_SV.logmsg(l_module_name,'OTM user name profile can not be null ',WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
90 END IF;
91 END IF;
92
93 IF ( (G_OTM_PSWD IS NULL) OR (length(G_OTM_PSWD) = 0)) THEN
94 FND_MESSAGE.SET_NAME('WSH','WSH_PROFILE_NOT_SET_ERR');
95 FND_MESSAGE.SET_TOKEN('PRF_NAME',fnd_message.get_string('WSH','WSH_OTM_PASSWORD'));
96 FND_MSG_PUB.ADD;
97 IF l_debug_on THEN
98 WSH_DEBUG_SV.logmsg(l_module_name,'OTM user password profile can not be null',WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
99 END IF;
100 END IF;
101
102 IF ((G_WSH_OTM_WS_ENDPOINT IS NULL ) OR (length(G_WSH_OTM_WS_ENDPOINT) = 0) OR
103 (G_OTM_UNAME IS NULL ) OR ( length(G_OTM_UNAME) = 0) OR
104 (G_OTM_PSWD IS NULL) OR (length(G_OTM_PSWD) = 0))
105 THEN
106 RAISE wsh_otm_endpoint_null;
107 END IF;
108
109 IF(G_WSH_OTM_SERVLET_URI is not null) THEN
110 j := instr(G_WSH_OTM_SERVLET_URI, G_NEWLINE_CHARACTER);
111 IF j <> 0 THEN
112 G_WSH_OTM_SERVLET_URI := substr(G_WSH_OTM_SERVLET_URI, 1, j-1);
113 END IF;
114 END IF;
115 IF(G_WSH_OTM_WS_ENDPOINT is not null) THEN
116 j := instr(G_WSH_OTM_WS_ENDPOINT, G_NEWLINE_CHARACTER);
117 IF j <> 0 THEN
118 G_WSH_OTM_WS_ENDPOINT := substr(G_WSH_OTM_WS_ENDPOINT, 1, j-1);
119 END IF;
120 END IF;
121
122 IF l_debug_on THEN
123 WSH_DEBUG_SV.log(l_module_name,'g_wsh_otm_servlet_uri='||G_WSH_OTM_SERVLET_URI);
124 WSH_DEBUG_SV.log(l_module_name,'g_wsh_otm_ws_endpoint='||G_WSH_OTM_WS_ENDPOINT);
125 WSH_DEBUG_SV.log(l_module_name,'g_wsh_otm_proxy_server='||G_WSH_OTM_PROXY_SERVER);
126 WSH_DEBUG_SV.log(l_module_name,'g_wsh_otm_proxy_port='||G_WSH_OTM_PROXY_PORT);
127 WSH_DEBUG_SV.log(l_module_name,'G_OTM_UNAME='||G_OTM_UNAME);
128 WSH_DEBUG_SV.log(l_module_name,'G_OTM_PSWD'||G_OTM_PSWD);
129 WSH_DEBUG_SV.pop(l_module_name);
130 END IF;
131
132 RETURN FND_API.G_RET_STS_SUCCESS;
133
134 EXCEPTION
135 WHEN wsh_otm_endpoint_null THEN
136 IF l_debug_on THEN
137 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_otm_endpoint_null');
138 END IF;
139 RETURN FND_API.G_RET_STS_ERROR;
140 WHEN others THEN
141 IF l_debug_on THEN
142 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
143 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
144 END IF;
145 RETURN FND_API.G_RET_STS_ERROR;
146 END load_profiles;
147
148 -- Check if URL is an HTTPS URL
149 -- Return Y or N
150 FUNCTION is_SSL_Enabled(p_url_string IN VARCHAR2)
151 RETURN VARCHAR2
152 IS
153 BEGIN
154 IF(instr(upper(p_url_string), 'HTTPS') <> 0) THEN
155 RETURN 'Y';
156 ELSE
157 RETURN 'N';
158 END IF;
159 END is_SSL_Enabled;
160
161
162 -- Returns a String with all the parameters appended to it.
163 -- This string will be passed to WSH rating servlet.
164 FUNCTION Get_Context_Params (p_fnd_ticket IN VARCHAR2
165 )
166 RETURN CLOB IS
167 p_ctxt_str CLOB;
168 BEGIN
169 p_ctxt_str := G_PARAM_NAME_DBC||'='||fnd_web_config.database_id;
170 p_ctxt_str := p_ctxt_str||G_HARD_CHAR;
171 p_ctxt_str := p_ctxt_str||G_PARAM_NAME_USERID||'='||fnd_global.user_id;
172 p_ctxt_str := p_ctxt_str||G_HARD_CHAR;
173 p_ctxt_str := p_ctxt_str||G_PARAM_NAME_RESPID||'='||fnd_global.resp_id;
174 p_ctxt_str := p_ctxt_str||G_HARD_CHAR;
175 p_ctxt_str := p_ctxt_str||G_PARAM_NAME_RESP_APPL_ID||'='||fnd_global.resp_appl_id;
176 p_ctxt_str := p_ctxt_str||G_HARD_CHAR;
177 p_ctxt_str := p_ctxt_str||G_PARAM_NAME_TICKET||'='||p_fnd_ticket;
178 p_ctxt_str := p_ctxt_str||G_HARD_CHAR;
179 p_ctxt_str := p_ctxt_str||G_PARAM_NAME_WS_END_POINT||'='||G_WSH_OTM_WS_ENDPOINT;
180 p_ctxt_str := p_ctxt_str||G_HARD_CHAR;
181 p_ctxt_str := p_ctxt_str||G_PARAM_NAME_ENC_STYLE||'='||G_ENC_STYLE;
182 p_ctxt_str := p_ctxt_str||G_HARD_CHAR;
183 p_ctxt_str := p_ctxt_str||G_PARAM_NAME_OTM_UNAME||'='||G_OTM_UNAME;
184 p_ctxt_str := p_ctxt_str||G_HARD_CHAR;
185 p_ctxt_str := p_ctxt_str||G_PARAM_NAME_OTM_PSSWD||'='||G_OTM_PSWD;
186
187 RETURN p_ctxt_str;
188 END Get_Context_Params;
189
190
191 --Posts the request to OTM
192 --Takes in the input and returns the CLOB data
193 PROCEDURE post_request_to_otm( p_request IN XMLType,
194 x_response OUT NOCOPY CLOB,
195 x_return_status OUT NOCOPY VARCHAR2
196 )
197 IS
198 i NUMBER;
199 l_servlet_uri CLOB;
200 l_fnd_ticket RAW(1000);
201 l_tkt_end_date VARCHAR2(500);
202 l_end_point VARCHAR2(4000);
203 l_return_status VARCHAR2(1);
204 l_response UTL_HTTP.Resp;
205 l_request UTL_HTTP.Req;
206 l_clob_response CLOB;
207 l_response_data VARCHAR2(2000);
208 l_resp_pieces UTL_HTTP.html_pieces;
209 l_profiles VARCHAR2(1);
210 l_context_params CLOB;--VARCHAR2(32676);
211 l_server_tz VARCHAR2(100);
212 l_debug_on BOOLEAN;
213 l_return_status_text VARCHAR2(7000);
214
215 -- Bug 5625714
216 l_amt NUMBER;
217 l_pos NUMBER;
218 l_length NUMBER;
219 l_buffer VARCHAR2(32767);
220 -- End of Bug 5625714
221 --Bug 7519244
222 l_context_params_tmp CLOB;
223 --Bug 7519244
224
225 wsh_otm_load_profile_failed EXCEPTION;
226 wsh_otm_unavailable_exception EXCEPTION;
227 wsh_null_otm_response EXCEPTION;
228 l_module_name CONSTANT VARCHAR2(2000) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'post_request_to_otm';
229
230 BEGIN
231
232 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
233 IF l_debug_on IS NULL
234 THEN
235 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
236 END IF;
237
238 IF l_debug_on THEN
239 WSH_DEBUG_SV.push(l_module_name);
240 END IF;
241
242
243 x_return_status := FND_API.G_RET_STS_SUCCESS;
244
245 IF (l_debug_on) THEN
246 WSH_DEBUG_SV.log(l_module_name,'before load profiles');
247 END IF;
248
249 l_profiles := load_profiles();
250
251
252 IF (l_debug_on) THEN
253 WSH_DEBUG_SV.log(l_module_name,'l_profiles='||l_profiles);
254 END IF;
255
256 IF l_profiles = FND_API.G_RET_STS_ERROR THEN
257 RAISE wsh_otm_load_profile_failed;
258 END IF;
259
260 IF (l_debug_on) THEN
261 WSH_DEBUG_SV.log(l_module_name,'After load profiles');
262 END IF;
263
264 get_secure_ticket_details ( p_op_code => G_WSH_TKT_OP_CODE,
265 p_argument => G_WSH_TKT_ARGUMENT_VALUE,
266 x_ticket => l_fnd_ticket,
267 x_server_time_zone => l_server_tz,
268 x_return_status => l_return_status);
269 IF (l_debug_on) THEN
270 WSH_DEBUG_SV.log(l_module_name,'After get secure tkt');
271 END IF;
272 IF (l_debug_on) THEN
273 WSH_DEBUG_SV.log(l_module_name,'l_return_status='||l_return_status);
274 END IF;
275
276 --5226917
277 --IF (l_debug_on) THEN
278 --WSH_DEBUG_SV.log(l_module_name,'l_fnd_ticket='||l_fnd_ticket);
279 --WSH_DEBUG_SV.log(l_module_name,'l_tkt_end_date='||l_tkt_end_date);
280 --END IF;
281
282 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
283 raise g_tkt_error;
284 END IF;
285 --Bug 7519244
286 l_context_params_tmp := p_request.getClobVal();
287 l_context_params_tmp := REPLACE(l_context_params_tmp,'&','%26');
288 --Bug 7519244
289 l_context_params := Get_Context_Params(l_fnd_ticket);
290 l_context_params := l_context_params||G_HARD_CHAR;
291 l_context_params := l_context_params||G_PARAM_NAME_RIQ_INPUT||'='||l_context_params_tmp;--Bug 7519244
292
293 l_servlet_uri := G_WSH_OTM_SERVLET_URI; ---||'?'||l_context_params;
294
295 IF (l_debug_on) THEN
296 --5226917
297 --WSH_DEBUG_SV.log(l_module_name,'l_context_params='||l_context_params);
298 WSH_DEBUG_SV.log(l_module_name,'l_servlet_uri='||l_servlet_uri);
299 WSH_DEBUG_SV.log(l_module_name,'to_char(length(l_context_params))='||to_char(length(l_context_params)));
300
301 END IF;
302
303
304 IF(is_SSL_Enabled(G_WSH_OTM_SERVLET_URI) = 'Y') THEN
305 IF (l_debug_on) THEN
306 WSH_DEBUG_SV.log(l_module_name,'SSL is enabled');
307 WSH_DEBUG_SV.log(l_module_name,'g_wallet_path='||G_WALLET_PATH);
308 WSH_DEBUG_SV.log(l_module_name,'g_wallet_password='||G_WALLET_PASSWORD);
309 END IF;
310
311 UTL_HTTP.SET_WALLET(G_WALLET_PATH,G_WALLET_PASSWORD);
312
313 --l_resp_pieces := UTL_HTTP.Request_Pieces(url => l_servlet_uri,
314 -- proxy => G_WSH_OTM_PROXY_SERVER,
315 -- wallet_path => G_WALLET_PATH,
316 -- wallet_password => G_WALLET_PASSWORD);
317 END IF;
318 --l_resp_pieces := UTL_HTTP.Request_Pieces(url => l_servlet_uri,
319 -- proxy => G_WSH_OTM_PROXY_SERVER);
320 Utl_Http.Set_Proxy ( proxy => G_WSH_OTM_PROXY_SERVER );
321
322 UTL_HTTP.set_response_error_check ( enable => false);
323 --Utl_Http.Set_Detailed_Excp_Support ( enable => true );
324
325 l_request := UTL_HTTP.begin_request(l_servlet_uri, 'POST' );--,'HTTP/1.0');--UTL_HTTP.HTTP_VERSION_1_0);
326 UTL_HTTP.SET_HEADER(l_request,'Content-Type','application/x-www-form-urlencoded');
327 UTL_HTTP.SET_HEADER(l_request,'Content-length',to_char(length(l_context_params)));
328
329 -- Bug 5625714
330 -- need to repeat Utl_Http.Write_Text as it can handle
331 -- only up to 32767 characters
332 -- Utl_Http.Write_Text(l_request,l_context_params);
333 l_amt:= 32000;
334 l_pos:= 1;
335 l_length:=DBMS_LOB.GETLENGTH(l_context_params);
336 IF (l_debug_on) THEN
337 WSH_DEBUG_SV.log(l_module_name,'Length',l_length);
338 END IF;
339 WHILE(l_length > 0)
340 LOOP
341 --{
342 IF (l_length < l_amt)
343 THEN
344 l_amt:=l_length;
345 END IF;
346 IF (l_debug_on) THEN
347 WSH_DEBUG_SV.log(l_module_name,'l_amt', l_amt);
348 END IF;
349 dbms_lob.read(l_context_params, l_amt, l_pos, l_buffer);
350 Utl_Http.Write_Text(l_request,l_buffer);
351 l_length:=l_length-l_amt;
352 l_pos := l_pos + l_amt;
353 --}
354 END LOOP;
355 -- End of Bug 5625714
356 l_response := utl_http.get_response(l_request);
357
358 --END IF;
359
360 IF (l_debug_on) THEN
361 WSH_DEBUG_SV.log(l_module_name,'HTTP response status code:='||l_response.status_code);
362 WSH_DEBUG_SV.log(l_module_name,'HTTP response reason phras:='||l_response.reason_phrase);
363 --WSH_DEBUG_SV.log(l_module_name,'l_resp_pieces.COUNT='||l_resp_pieces.COUNT);
364 END IF;
365
366 IF l_response.status_code = 500 THEN
367 RAISE wsh_otm_unavailable_exception;
368 END IF;
369
370 BEGIN
371 LOOP
372 utl_http.read_text(l_response, l_response_data);
373 IF (l_debug_on) THEN
374 WSH_DEBUG_SV.log(l_module_name,'Line ='||l_response_data);
375 END IF;
376 l_clob_response := l_clob_response||l_response_data;
377 END LOOP;
378 EXCEPTION
379 WHEN UTL_HTTP.end_of_body THEN
380 UTL_HTTP.end_response(l_response);
381 END;
382
383 IF l_clob_response IS NULL THEN
384 RAISE wsh_null_otm_response;
385 END IF;
386
387 x_response := l_clob_response;--l_response;
388
389 IF l_debug_on THEN
390 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
391 WSH_DEBUG_SV.pop(l_module_name);
392 END IF;
393
394 EXCEPTION
395 WHEN wsh_null_otm_response THEN
396 x_return_status := FND_API.G_RET_STS_ERROR;
397 IF l_debug_on THEN
398 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status='||x_return_status);
399 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_null_otm_response: Null response received.');
400 END IF;
401 WHEN wsh_otm_unavailable_exception THEN
402 x_return_status := FND_API.G_RET_STS_ERROR;
403 IF l_debug_on THEN
404 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status='||x_return_status);
405 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_otm_unavailable_exception: OTM Server is down. Contact the System Administrator');
406 END IF;
407 WHEN wsh_otm_load_profile_failed THEN
408 x_return_status := FND_API.G_RET_STS_ERROR;
409 IF l_debug_on THEN
410 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status='||x_return_status);
411 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_otm_load_profile_failed');
412 END IF;
413 WHEN UTL_HTTP.INIT_FAILED THEN
414 BEGIN
415 EXECUTE IMMEDIATE 'SELECT UTL_HTTP.get_detailed_sqlcode from dual' INTO x_return_status;
416 x_return_status := FND_API.G_RET_STS_ERROR;
417 EXECUTE IMMEDIATE 'SELECT UTL_HTTP.get_detailed_sqlerrm from dual' INTO l_return_status_text;
418 IF l_debug_on THEN
419 WSH_DEBUG_SV.logmsg(l_module_name,'HTTP request failed because of UTL_HTTP_INIT_FAILED');
420 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status='||x_return_status||' l_return_status_text='||l_return_status_text);
421 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.UTL_HTTP.INIT_FAILED');
422 END IF;
423 END;
424 WHEN UTL_HTTP.REQUEST_FAILED THEN
425 x_return_status := FND_API.G_RET_STS_ERROR;
426 l_return_status_text := 'UTL_HTTP.REQUEST_FAILED';
427 IF l_debug_on THEN
428 WSH_DEBUG_SV.logmsg(l_module_name,'HTTP request failed because of UTL HTTP REQUEST_FAILED'||l_return_status_text);
429 END IF;
430 BEGIN
431 EXECUTE IMMEDIATE 'SELECT UTL_HTTP.get_detailed_sqlcode from dual' INTO x_return_status;
432 x_return_status := FND_API.G_RET_STS_ERROR;
433 EXECUTE IMMEDIATE 'SELECT UTL_HTTP.get_detailed_sqlerrm from dual' INTO l_return_status_text;
434 IF l_debug_on THEN
435 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status='||x_return_status||' l_return_status_text='||l_return_status_text);
436 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UTL_HTTP.REQUEST_FAILED');
437 END IF;
438 END;
439 WHEN UTL_TCP.END_OF_INPUT THEN
440 x_return_status := FND_API.G_RET_STS_ERROR;
441 l_return_status_text := 'UTL_TCP.END_OF_INPUT';
442 IF l_debug_on THEN
443 WSH_DEBUG_SV.logmsg(l_module_name,'HTTP request failed because of UTL_TCP.END_OF_INPUT');
444 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status='||x_return_status||' l_return_status_text='||l_return_status_text);
445 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UTL_TCP.END_OF_INPUT');
446 END IF;
447 WHEN FND_API.G_EXC_ERROR THEN
448 x_return_status := FND_API.G_RET_STS_ERROR;
449 IF l_debug_on THEN
450 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
451 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
452 END IF;
453 WHEN g_tkt_error THEN
454 x_return_status := FND_API.G_RET_STS_ERROR;
455 IF l_debug_on THEN
456 WSH_DEBUG_SV.logmsg(l_module_name,'g_tkt_error has occured.',WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
457 WSH_DEBUG_SV.pop(l_module_name,'FND_API.G_EXC_ERROR');
458 END IF;
459 WHEN others THEN
460 x_return_status := FND_API.G_RET_STS_ERROR;
461 IF l_debug_on THEN
462 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
463 BEGIN
464 EXECUTE IMMEDIATE 'SELECT UTL_HTTP.get_detailed_sqlcode from dual' INTO x_return_status;
465 EXECUTE IMMEDIATE 'SELECT UTL_HTTP.get_detailed_sqlerrm from dual' INTO l_return_status_text;
466 IF l_debug_on THEN
467 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status='||x_return_status||' l_return_status_text='||l_return_status_text);
468 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
469 END IF;
470 EXCEPTION
471 WHEN OTHERS THEN
472 x_return_status := FND_API.G_RET_STS_ERROR;
473 FND_MESSAGE.SET_NAME('WSH','WSH_UTL_POST_UNEXPECTED_ERROR');
474 l_return_status_text := FND_MESSAGE.GET;
475 END;
476 END IF;
477 END post_request_to_otm;
478
479 -- Get FND Security details.
480 -- Create New if doesn't exist
481 -- Return existing if valid.
482 -- If expired, delete existing and create new
483 PROCEDURE get_secure_ticket_details( p_op_code IN VARCHAR2,
484 p_argument IN VARCHAR2,
485 x_ticket OUT NOCOPY RAW,
486 x_server_time_zone OUT NOCOPY VARCHAR2,
487 x_return_status OUT NOCOPY VARCHAR2
488 )
489 IS
490 l_ticket RAW(16);
491 l_ticket_string VARCHAR2(1000);
492 l_operation VARCHAR2(255);
493 l_argument VARCHAR2(4000);
494 l_end_date VARCHAR2(100);
495 l_edate TimeStamp;
496 l_sysdate TimeStamp;
497 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_secure_ticket_details';
498 l_debug_on BOOLEAN;
499
500 CURSOR c_get_ticket_details (c_operation VARCHAR2, c_argument VARCHAR2) IS
501 SELECT ticket, operation, argument, end_date
502 FROM FND_HTTP_TICKETS
503 WHERE operation = c_operation
504 AND argument = c_argument;
505
506 --CURSOR c_get_ticket_end_date (c_ticket RAW) IS
507 --SELECT to_char(end_date,'yyyy/mm/dd hh:mi:ss')
508 --FROM FND_HTTP_TICKETS
509 --WHERE ticket = c_ticket;
510
511 CURSOR c_get_sysdate IS
512 SELECT SYSDATE FROM DUAL;
513
514
515 BEGIN
516
517 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
518 IF l_debug_on IS NULL THEN
519 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
520 END IF;
521
522 IF l_debug_on THEN
523 WSH_DEBUG_SV.push(l_module_name);
524 END IF;
525
526 x_return_status := FND_API.G_RET_STS_SUCCESS;
527
528 OPEN c_get_ticket_details (p_op_code,p_argument);
529 FETCH c_get_ticket_details INTO l_ticket,l_operation, l_argument, l_edate;
530 CLOSE c_get_ticket_details;
531
532 IF l_debug_on THEN
533 --5226917
534 --WSH_DEBUG_SV.log(l_module_name,'l_ticket='||l_ticket);
535 WSH_DEBUG_SV.log(l_module_name,'l_operation='||l_operation);
536 WSH_DEBUG_SV.log(l_module_name,'l_argument='||l_argument);
537 WSH_DEBUG_SV.log(l_module_name,'l_edate='||l_edate);
538 END IF;
539 --FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'sysdate='||to_date(to_char(sysdate,'yyyy/mm/dd hh:mi:ss'),'yyyy/mm/dd hh:mi:ss'));
540
541 -- Ticket Exists. Valid and not expired
542 -- return the existing ticket
543 OPEN c_get_sysdate;
544 FETCH c_get_sysdate INTO l_sysdate;
545 CLOSE c_get_sysdate;
546
547 IF ( l_edate IS NOT NULL) AND ( l_edate > SYSDATE) THEN
548 IF l_debug_on THEN
549 WSH_DEBUG_SV.log(l_module_name,'Ticket Exists. Valid and not expired');
550 END IF;
551 -- l_ticket is actual ticket. Do Nothing.
552 -- Ticket Exists but expired.Delete existing
553 ELSIF ( l_edate IS NOT NULL) AND ( l_edate < SYSDATE) THEN
554 IF l_debug_on THEN
555 WSH_DEBUG_SV.log(l_module_name,'Ticket Exists but expired.Delete existing');
556 WSH_DEBUG_SV.log(l_module_name,'Deleting...');
557 END IF;
558 FND_HTTP_TICKET.DESTROY_TICKET(l_ticket);
559 IF l_debug_on THEN
560 WSH_DEBUG_SV.log(l_module_name,'Creating new ticket...');
561 END IF;
562 l_ticket := FND_HTTP_TICKET.CREATE_TICKET(p_op_code
563 ,p_argument
564 ,G_WSH_TKT_LIFESPAN
565 );
566 --5226917
567 --IF l_debug_on THEN
568 -- WSH_DEBUG_SV.log(l_module_name,'new l_ticket='||l_ticket);
569 --END IF;
570 -- ticket doesn't exist . Get a new ticket.
571 --FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'x_end_date='||x_end_date);
572 ELSE
573 IF l_debug_on THEN
574 WSH_DEBUG_SV.log(l_module_name,'ticket doesnt exist . Create a new ticket');
575 END IF;
576 l_ticket := FND_HTTP_TICKET.CREATE_TICKET(p_op_code
577 ,p_argument
578 ,G_WSH_TKT_LIFESPAN
579 );
580 END IF;
581
582 x_ticket := l_ticket;
583
584 --5226917
585 --IF l_debug_on THEN
586 -- WSH_DEBUG_SV.log(l_module_name,'l_ticket='||x_ticket);
587 --END IF;
588
589 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
590
591 IF l_debug_on THEN
592 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
593 WSH_DEBUG_SV.pop(l_module_name);
594 END IF;
595
596 EXCEPTION
597 WHEN others THEN
598 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
599 IF l_debug_on THEN
600 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
601 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
602 END IF;
603
604 END get_secure_ticket_details;
605
606
607 END WSH_OTM_HTTP_UTL;
608