[Home] [Help]
PACKAGE BODY: APPS.WSH_OTM_HTTP_UTL
Source
1 PACKAGE BODY WSH_OTM_HTTP_UTL AS
2 /* $Header: WSHGLHUB.pls 120.2.12000000.1 2007/01/25 16:15:04 amohamme noship $ */
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
222 wsh_otm_load_profile_failed EXCEPTION;
223 wsh_otm_unavailable_exception EXCEPTION;
224 wsh_null_otm_response EXCEPTION;
225 l_module_name CONSTANT VARCHAR2(2000) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'post_request_to_otm';
226
227 BEGIN
228
229 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
230 IF l_debug_on IS NULL
231 THEN
232 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
233 END IF;
234
235 IF l_debug_on THEN
236 WSH_DEBUG_SV.push(l_module_name);
237 END IF;
238
239
240 x_return_status := FND_API.G_RET_STS_SUCCESS;
241
242 IF (l_debug_on) THEN
243 WSH_DEBUG_SV.log(l_module_name,'before load profiles');
244 END IF;
245
246 l_profiles := load_profiles();
247
248
249 IF (l_debug_on) THEN
250 WSH_DEBUG_SV.log(l_module_name,'l_profiles='||l_profiles);
251 END IF;
252
253 IF l_profiles = FND_API.G_RET_STS_ERROR THEN
254 RAISE wsh_otm_load_profile_failed;
255 END IF;
256
257 IF (l_debug_on) THEN
258 WSH_DEBUG_SV.log(l_module_name,'After load profiles');
259 END IF;
260
261 get_secure_ticket_details ( p_op_code => G_WSH_TKT_OP_CODE,
262 p_argument => G_WSH_TKT_ARGUMENT_VALUE,
263 x_ticket => l_fnd_ticket,
264 x_server_time_zone => l_server_tz,
265 x_return_status => l_return_status);
266 IF (l_debug_on) THEN
267 WSH_DEBUG_SV.log(l_module_name,'After get secure tkt');
268 END IF;
269 IF (l_debug_on) THEN
270 WSH_DEBUG_SV.log(l_module_name,'l_return_status='||l_return_status);
271 END IF;
272
273 --5226917
274 --IF (l_debug_on) THEN
275 --WSH_DEBUG_SV.log(l_module_name,'l_fnd_ticket='||l_fnd_ticket);
276 --WSH_DEBUG_SV.log(l_module_name,'l_tkt_end_date='||l_tkt_end_date);
277 --END IF;
278
279 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
280 raise g_tkt_error;
281 END IF;
282
283 l_context_params := Get_Context_Params(l_fnd_ticket);
284 l_context_params := l_context_params||G_HARD_CHAR;
285 l_context_params := l_context_params||G_PARAM_NAME_RIQ_INPUT||'='||p_request.getClobVal();
286
287 l_servlet_uri := G_WSH_OTM_SERVLET_URI; ---||'?'||l_context_params;
288
289 IF (l_debug_on) THEN
290 --5226917
291 --WSH_DEBUG_SV.log(l_module_name,'l_context_params='||l_context_params);
292 WSH_DEBUG_SV.log(l_module_name,'l_servlet_uri='||l_servlet_uri);
293 WSH_DEBUG_SV.log(l_module_name,'to_char(length(l_context_params))='||to_char(length(l_context_params)));
294
295 END IF;
296
297
298 IF(is_SSL_Enabled(G_WSH_OTM_SERVLET_URI) = 'Y') THEN
299 IF (l_debug_on) THEN
300 WSH_DEBUG_SV.log(l_module_name,'SSL is enabled');
301 WSH_DEBUG_SV.log(l_module_name,'g_wallet_path='||G_WALLET_PATH);
302 WSH_DEBUG_SV.log(l_module_name,'g_wallet_password='||G_WALLET_PASSWORD);
303 END IF;
304
305 UTL_HTTP.SET_WALLET(G_WALLET_PATH,G_WALLET_PASSWORD);
306
307 --l_resp_pieces := UTL_HTTP.Request_Pieces(url => l_servlet_uri,
308 -- proxy => G_WSH_OTM_PROXY_SERVER,
309 -- wallet_path => G_WALLET_PATH,
310 -- wallet_password => G_WALLET_PASSWORD);
311 END IF;
312 --l_resp_pieces := UTL_HTTP.Request_Pieces(url => l_servlet_uri,
313 -- proxy => G_WSH_OTM_PROXY_SERVER);
314 Utl_Http.Set_Proxy ( proxy => G_WSH_OTM_PROXY_SERVER );
315
316 UTL_HTTP.set_response_error_check ( enable => false);
317 --Utl_Http.Set_Detailed_Excp_Support ( enable => true );
318
319 l_request := UTL_HTTP.begin_request(l_servlet_uri, 'POST' );--,'HTTP/1.0');--UTL_HTTP.HTTP_VERSION_1_0);
320 UTL_HTTP.SET_HEADER(l_request,'Content-Type','application/x-www-form-urlencoded');
321 UTL_HTTP.SET_HEADER(l_request,'Content-length',to_char(length(l_context_params)));
322
323 -- Bug 5625714
324 -- need to repeat Utl_Http.Write_Text as it can handle
325 -- only up to 32767 characters
326 -- Utl_Http.Write_Text(l_request,l_context_params);
327 l_amt:= 32000;
328 l_pos:= 1;
329 l_length:=DBMS_LOB.GETLENGTH(l_context_params);
330 IF (l_debug_on) THEN
331 WSH_DEBUG_SV.log(l_module_name,'Length',l_length);
332 END IF;
333 WHILE(l_length > 0)
334 LOOP
335 --{
336 IF (l_length < l_amt)
337 THEN
338 l_amt:=l_length;
339 END IF;
340 IF (l_debug_on) THEN
341 WSH_DEBUG_SV.log(l_module_name,'l_amt', l_amt);
342 END IF;
343 dbms_lob.read(l_context_params, l_amt, l_pos, l_buffer);
344 Utl_Http.Write_Text(l_request,l_buffer);
345 l_length:=l_length-l_amt;
346 l_pos := l_pos + l_amt;
347 --}
348 END LOOP;
349 -- End of Bug 5625714
350 l_response := utl_http.get_response(l_request);
351
352 --END IF;
353
354 IF (l_debug_on) THEN
355 WSH_DEBUG_SV.log(l_module_name,'HTTP response status code:='||l_response.status_code);
356 WSH_DEBUG_SV.log(l_module_name,'HTTP response reason phras:='||l_response.reason_phrase);
357 --WSH_DEBUG_SV.log(l_module_name,'l_resp_pieces.COUNT='||l_resp_pieces.COUNT);
358 END IF;
359
360 IF l_response.status_code = 500 THEN
361 RAISE wsh_otm_unavailable_exception;
362 END IF;
363
364 BEGIN
365 LOOP
366 utl_http.read_text(l_response, l_response_data);
367 IF (l_debug_on) THEN
368 WSH_DEBUG_SV.log(l_module_name,'Line ='||l_response_data);
369 END IF;
370 l_clob_response := l_clob_response||l_response_data;
371 END LOOP;
372 EXCEPTION
373 WHEN UTL_HTTP.end_of_body THEN
374 UTL_HTTP.end_response(l_response);
375 END;
376
377 IF l_clob_response IS NULL THEN
378 RAISE wsh_null_otm_response;
379 END IF;
380
381 x_response := l_clob_response;--l_response;
382
383 IF l_debug_on THEN
384 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
385 WSH_DEBUG_SV.pop(l_module_name);
386 END IF;
387
388 EXCEPTION
389 WHEN wsh_null_otm_response THEN
390 x_return_status := FND_API.G_RET_STS_ERROR;
391 IF l_debug_on THEN
392 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status='||x_return_status);
393 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_null_otm_response: Null response received.');
394 END IF;
395 WHEN wsh_otm_unavailable_exception 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_otm_unavailable_exception: OTM Server is down. Contact the System Administrator');
400 END IF;
401 WHEN wsh_otm_load_profile_failed 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_load_profile_failed');
406 END IF;
407 WHEN UTL_HTTP.INIT_FAILED THEN
408 BEGIN
409 EXECUTE IMMEDIATE 'SELECT UTL_HTTP.get_detailed_sqlcode from dual' INTO x_return_status;
410 x_return_status := FND_API.G_RET_STS_ERROR;
411 EXECUTE IMMEDIATE 'SELECT UTL_HTTP.get_detailed_sqlerrm from dual' INTO l_return_status_text;
412 IF l_debug_on THEN
413 WSH_DEBUG_SV.logmsg(l_module_name,'HTTP request failed because of UTL_HTTP_INIT_FAILED');
414 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status='||x_return_status||' l_return_status_text='||l_return_status_text);
415 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.UTL_HTTP.INIT_FAILED');
416 END IF;
417 END;
418 WHEN UTL_HTTP.REQUEST_FAILED THEN
419 x_return_status := FND_API.G_RET_STS_ERROR;
420 l_return_status_text := 'UTL_HTTP.REQUEST_FAILED';
421 IF l_debug_on THEN
422 WSH_DEBUG_SV.logmsg(l_module_name,'HTTP request failed because of UTL HTTP REQUEST_FAILED'||l_return_status_text);
423 END IF;
424 BEGIN
425 EXECUTE IMMEDIATE 'SELECT UTL_HTTP.get_detailed_sqlcode from dual' INTO x_return_status;
426 x_return_status := FND_API.G_RET_STS_ERROR;
427 EXECUTE IMMEDIATE 'SELECT UTL_HTTP.get_detailed_sqlerrm from dual' INTO l_return_status_text;
428 IF l_debug_on THEN
429 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status='||x_return_status||' l_return_status_text='||l_return_status_text);
430 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UTL_HTTP.REQUEST_FAILED');
431 END IF;
432 END;
433 WHEN UTL_TCP.END_OF_INPUT THEN
434 x_return_status := FND_API.G_RET_STS_ERROR;
435 l_return_status_text := 'UTL_TCP.END_OF_INPUT';
436 IF l_debug_on THEN
437 WSH_DEBUG_SV.logmsg(l_module_name,'HTTP request failed because of UTL_TCP.END_OF_INPUT');
438 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status='||x_return_status||' l_return_status_text='||l_return_status_text);
439 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UTL_TCP.END_OF_INPUT');
440 END IF;
441 WHEN FND_API.G_EXC_ERROR THEN
442 x_return_status := FND_API.G_RET_STS_ERROR;
443 IF l_debug_on THEN
444 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
445 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
446 END IF;
447 WHEN g_tkt_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,'g_tkt_error has occured.',WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
451 WSH_DEBUG_SV.pop(l_module_name,'FND_API.G_EXC_ERROR');
452 END IF;
453 WHEN others 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,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
457 BEGIN
458 EXECUTE IMMEDIATE 'SELECT UTL_HTTP.get_detailed_sqlcode from dual' INTO x_return_status;
459 EXECUTE IMMEDIATE 'SELECT UTL_HTTP.get_detailed_sqlerrm from dual' INTO l_return_status_text;
460 IF l_debug_on THEN
461 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status='||x_return_status||' l_return_status_text='||l_return_status_text);
462 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
463 END IF;
464 EXCEPTION
465 WHEN OTHERS THEN
466 x_return_status := FND_API.G_RET_STS_ERROR;
467 FND_MESSAGE.SET_NAME('WSH','WSH_UTL_POST_UNEXPECTED_ERROR');
468 l_return_status_text := FND_MESSAGE.GET;
469 END;
470 END IF;
471 END post_request_to_otm;
472
473 -- Get FND Security details.
474 -- Create New if doesn't exist
475 -- Return existing if valid.
476 -- If expired, delete existing and create new
477 PROCEDURE get_secure_ticket_details( p_op_code IN VARCHAR2,
478 p_argument IN VARCHAR2,
479 x_ticket OUT NOCOPY RAW,
480 x_server_time_zone OUT NOCOPY VARCHAR2,
481 x_return_status OUT NOCOPY VARCHAR2
482 )
483 IS
484 l_ticket RAW(16);
485 l_ticket_string VARCHAR2(1000);
486 l_operation VARCHAR2(255);
487 l_argument VARCHAR2(4000);
488 l_end_date VARCHAR2(100);
489 l_edate TimeStamp;
490 l_sysdate TimeStamp;
491 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_secure_ticket_details';
492 l_debug_on BOOLEAN;
493
494 CURSOR c_get_ticket_details (c_operation VARCHAR2, c_argument VARCHAR2) IS
495 SELECT ticket, operation, argument, end_date
496 FROM FND_HTTP_TICKETS
497 WHERE operation = c_operation
498 AND argument = c_argument;
499
500 --CURSOR c_get_ticket_end_date (c_ticket RAW) IS
501 --SELECT to_char(end_date,'yyyy/mm/dd hh:mi:ss')
502 --FROM FND_HTTP_TICKETS
503 --WHERE ticket = c_ticket;
504
505 CURSOR c_get_sysdate IS
506 SELECT SYSDATE FROM DUAL;
507
508
509 BEGIN
510
511 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
512 IF l_debug_on IS NULL THEN
513 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
514 END IF;
515
516 IF l_debug_on THEN
517 WSH_DEBUG_SV.push(l_module_name);
518 END IF;
519
520 x_return_status := FND_API.G_RET_STS_SUCCESS;
521
522 OPEN c_get_ticket_details (p_op_code,p_argument);
523 FETCH c_get_ticket_details INTO l_ticket,l_operation, l_argument, l_edate;
524 CLOSE c_get_ticket_details;
525
526 IF l_debug_on THEN
527 --5226917
528 --WSH_DEBUG_SV.log(l_module_name,'l_ticket='||l_ticket);
529 WSH_DEBUG_SV.log(l_module_name,'l_operation='||l_operation);
530 WSH_DEBUG_SV.log(l_module_name,'l_argument='||l_argument);
531 WSH_DEBUG_SV.log(l_module_name,'l_edate='||l_edate);
532 END IF;
533 --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'));
534
535 -- Ticket Exists. Valid and not expired
536 -- return the existing ticket
537 OPEN c_get_sysdate;
538 FETCH c_get_sysdate INTO l_sysdate;
539 CLOSE c_get_sysdate;
540
541 IF ( l_edate IS NOT NULL) AND ( l_edate > SYSDATE) THEN
542 IF l_debug_on THEN
543 WSH_DEBUG_SV.log(l_module_name,'Ticket Exists. Valid and not expired');
544 END IF;
545 -- l_ticket is actual ticket. Do Nothing.
546 -- Ticket Exists but expired.Delete existing
547 ELSIF ( 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 but expired.Delete existing');
550 WSH_DEBUG_SV.log(l_module_name,'Deleting...');
551 END IF;
552 FND_HTTP_TICKET.DESTROY_TICKET(l_ticket);
553 IF l_debug_on THEN
554 WSH_DEBUG_SV.log(l_module_name,'Creating new ticket...');
555 END IF;
556 l_ticket := FND_HTTP_TICKET.CREATE_TICKET(p_op_code
557 ,p_argument
558 ,G_WSH_TKT_LIFESPAN
559 );
560 --5226917
561 --IF l_debug_on THEN
562 -- WSH_DEBUG_SV.log(l_module_name,'new l_ticket='||l_ticket);
563 --END IF;
564 -- ticket doesn't exist . Get a new ticket.
565 --FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'x_end_date='||x_end_date);
566 ELSE
567 IF l_debug_on THEN
568 WSH_DEBUG_SV.log(l_module_name,'ticket doesnt exist . Create a new ticket');
569 END IF;
570 l_ticket := FND_HTTP_TICKET.CREATE_TICKET(p_op_code
571 ,p_argument
572 ,G_WSH_TKT_LIFESPAN
573 );
574 END IF;
575
576 x_ticket := l_ticket;
577
578 --5226917
579 --IF l_debug_on THEN
580 -- WSH_DEBUG_SV.log(l_module_name,'l_ticket='||x_ticket);
581 --END IF;
582
583 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
584
585 IF l_debug_on THEN
586 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
587 WSH_DEBUG_SV.pop(l_module_name);
588 END IF;
589
590 EXCEPTION
591 WHEN others THEN
592 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
593 IF l_debug_on THEN
594 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
595 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
596 END IF;
597
598 END get_secure_ticket_details;
599
600
601 END WSH_OTM_HTTP_UTL;
602