[Home] [Help]
PACKAGE BODY: APPS.MSC_E1APS_UTIL
Source
1 PACKAGE BODY MSC_E1APS_UTIL AS -- body
2 --# $Header: MSCE1ULB.pls 120.12.12020000.4 2013/03/21 07:20:59 sravinoo ship $
3 -- Global variables
4 ws_prf VARCHAR2(1000);
5 sql_cnd VARCHAR2(2000);
6 srch_str VARCHAR2(10) := 'SoapHttp';
7 chk_cnd INTEGER;
8
9 FUNCTION MSC_E1APS_ODIScenarioExecute ( ScenarioName IN VARCHAR2,
10 ScenarioVersion IN VARCHAR2,
11 ScenarioParam IN VARCHAR2,
12 WsUrl IN VARCHAR2 )
13 RETURN VARCHAR2 AS soap_request VARCHAR2(30000);
14 soap_respond VARCHAR2(30000);
15 http_req utl_http.req;
16 http_resp utl_http.resp;
17 StartIndex INTEGER;
18 EndIndex INTEGER;
19 returnStr VARCHAR2(2000);
20 Time_Out INTEGER;
21 BEGIN
22
23 -- For bug # 11713909
24 -- Check to launch 10g or 11g soap request based on MSC_E1APS_ODIURL profile value
25 -- Check for 'SoapHttp' string in the profile value
26 -- if found launch 10g soap request function MSC_E1APS_ODIScenExecute10g
27 -- else launch 11g soap request
28
29 ws_prf := fnd_profile.value('MSC_E1APS_ODIURL');
30 sql_cnd := 'select INSTR('''||ws_prf||''','''||srch_str||''') from dual';
31 EXECUTE immediate sql_cnd into chk_cnd;
32
33 IF (chk_cnd > 0) THEN
34 -- Launch Soap request for 10G -- call function MSC_E1APS_ODIScenExecute10g
35 returnStr := MSC_E1APS_ODIScenExecute10g(ScenarioName,ScenarioVersion,ScenarioParam,WsUrl);
36 RETURN returnStr;
37 ELSE
38 -- 11g Web Logic SOAP Request
39 soap_request:= '<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://odiws.msc.com/"><env:Header/><env:Body><ns1:ExecuteScenario><arg0>'
40 || ScenarioName || '</arg0><arg1>'
41 || ScenarioVersion || '</arg1><arg2>'
42 || ScenarioParam || '</arg2></ns1:ExecuteScenario>/env:Body></env:Envelope>';
43
44 Time_Out := fnd_profile.value('MSC_E1APS_WS_TIME_OUT');
45 UTL_HTTP.set_transfer_timeout(Time_Out);
46 -- UTL_HTTP.set_transfer_timeout(36000);
47
48 http_req:= utl_http.begin_request ( WsUrl, 'POST', 'HTTP/1.1' );
49 utl_http.set_header(http_req, 'Content-Type', 'text/xml');
50 utl_http.set_header(http_req, 'Content-Length', LENGTH(soap_request));
51 utl_http.set_header(http_req, 'SOAPAction', '');
52 utl_http.write_text(http_req, soap_request);
53 http_resp:= utl_http.get_response(http_req);
54 utl_http.read_text(http_resp, soap_respond);
55 utl_http.end_response(http_resp);
56 SELECT instr(soap_respond,'<return>')
57 INTO StartIndex
58 FROM dual;
59
60 SELECT instr(soap_respond,'</return>')
61 INTO EndIndex
62 FROM dual;
63
64 SELECT SUBSTR(soap_respond,StartIndex+8,EndIndex-(StartIndex+8))
65 INTO returnStr
66 FROM dual;
67
68 RETURN returnStr;
69 END IF;
70 EXCEPTION
71 WHEN OTHERS THEN
72 returnStr:= '-1#Error in execution of ODI Scenario:'
73 ||ScenarioName
74 ||'. SOAP Please check Application Server Log.';
75 RETURN returnStr;
76 END MSC_E1APS_ODIScenarioExecute;
77
78 FUNCTION MSC_E1APS_ODIInitialize ( WsUrl IN VARCHAR2,
79 BaseDate INTEGER )
80 RETURN VARCHAR2 AS soap_request VARCHAR2(30000);
81 soap_respond VARCHAR2(30000);
82 http_req utl_http.req;
83 http_resp utl_http.resp;
84 StartIndex INTEGER;
85 EndIndex INTEGER;
86 SessionNum VARCHAR2(10);
87 ErrMessage VARCHAR2(1900);
88 ErrLength INTEGER;
89 returnStr VARCHAR2(2000);
90 finalreturnStr VARCHAR2(5000);
91 Time_Out INTEGER;
92 err_msg1 varchar2(4000);
93 BEGIN
94 finalreturnStr:=NULL;
95 -- For bug # 11713909
96 -- Check to launch 10g or 11g soap request based on MSC_E1APS_ODIURL profile value
97 -- Check for 'SoapHttp' string in the profile value
98 -- if found launch 10g soap request function MSC_E1APS_ODIInitialize10g
99 -- else launch 11g soap request
100
101 ws_prf := fnd_profile.value('MSC_E1APS_ODIURL');
102 sql_cnd := 'select INSTR('''||ws_prf||''','''||srch_str||''') from dual';
103 EXECUTE immediate sql_cnd into chk_cnd;
104
105 IF (chk_cnd > 0) THEN
106 returnStr := MSC_E1APS_ODIInitialize10g(WsUrl,BaseDate);
107 RETURN returnStr;
108 ELSE
109 BEGIN
110 --****LOADPARAMETERSDATATOWORKREPPKG****
111 BEGIN
112 --11g Web Logic SOAP Request
113 soap_request:= '<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://odiws.msc.com/"><env:Header/><env:Body><ns1:ExecuteScenario><arg0>'
114 || 'LOADPARAMETERSDATATOWORKREPPKG</arg0><arg1>001</arg1>'
115 || '<arg2></arg2></ns1:ExecuteScenario>/env:Body></env:Envelope>';
116
117 Time_Out := fnd_profile.value('MSC_E1APS_WS_TIME_OUT');
118 UTL_HTTP.set_transfer_timeout(Time_Out);
119 -- UTL_HTTP.set_transfer_timeout(36000);
120 http_req:= utl_http.begin_request ( WsUrl, 'POST', 'HTTP/1.1' );
121 utl_http.set_header(http_req, 'Content-Type', 'text/xml');
122 utl_http.set_header(http_req, 'Content-Length', LENGTH(soap_request));
123 utl_http.set_header(http_req, 'SOAPAction', '');
124 utl_http.write_text(http_req, soap_request);
125 http_resp:= utl_http.get_response(http_req);
126 utl_http.read_text(http_resp, soap_respond);
127 utl_http.end_response(http_resp);
128 SELECT instr(soap_respond,'<return>')
129 INTO StartIndex
130 FROM dual;
131
132 SELECT instr(soap_respond,'</return>')
133 INTO EndIndex
134 FROM dual;
135
136 SELECT SUBSTR(soap_respond,StartIndex+8,EndIndex-(StartIndex+8))
137 INTO returnStr
138 FROM dual;
139
140 SELECT instr(returnStr,'#')
141 INTO StartIndex
142 FROM dual;
143
144 SELECT SUBSTR(returnStr,0,StartIndex-1)
145 INTO SessionNum
146 FROM dual;
147
148 SELECT SUBSTR(returnStr,StartIndex+1,1800)
149 INTO ErrMessage
150 FROM dual;
151 IF SessionNum = '-1' THEN
152 finalreturnStr:= 'ODI Scenario:'
153 ||'LOADPARAMETERSDATATOWORKREPPKG'
154 ||'#ODI Session No.#Error Message='
155 ||returnStr;
156 RETURN finalreturnStr;
157 END IF;
158
159 IF SessionNum <> '-1' AND LENGTH(ErrMessage) > 0 THEN
160 finalreturnStr:= 'ODI Scenario:'
161 ||'LOADPARAMETERSDATATOWORKREPPKG'
162 ||'#ODI Session No.#Error Message='
163 ||returnStr;
164 RETURN finalreturnStr;
165 END IF;
166 -- Changes for 11G
167 IF SessionNum <> '-1' AND (LENGTH(ErrMessage) IS NULL) THEN
168 err_msg1 := GetODIErrorMessage(SessionNum);
169 IF (length(err_msg1) IS NOT NULL) THEN
170 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'LOAD PARAMETER FAILED. Session # ' || SessionNum);
171 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error : ' || err_msg1);
172 finalreturnStr := err_msg1;
173 RETURN finalreturnStr;
174 END IF;
175 END IF;
176 EXCEPTION
177 WHEN OTHERS THEN
178 finalreturnStr:= 'IN OTHERS. ODI Scenario: LOADPARAMETERSDATATOWORKREPPKG ODI Session No. ' || SessionNum || 'Error Message =-1 Error in execution of ODI scenario. Please check Application Server Log.';
179 RETURN finalreturnStr;
180 END;
181
182 --****UPDATEVARIABLE****
183 BEGIN
184 --11g Web Logic SOAP Request
185 soap_request:= '<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://odiws.msc.com/"><env:Header/><env:Body><ns1:ExecuteScenario><arg0>'
186 ||'UPDATEVARIABLE</arg0><arg1>001</arg1><arg2>'
187 ||'E1TOAPSPROJECT.PVD_BASE_DATE='||BaseDate ||'</arg2></ns1:ExecuteScenario>/env:Body></env:Envelope>';
188
189 Time_Out := fnd_profile.value('MSC_E1APS_WS_TIME_OUT');
190 UTL_HTTP.set_transfer_timeout(Time_Out);
191 --UTL_HTTP.set_transfer_timeout(36000);
192 http_req:= utl_http.begin_request ( WsUrl, 'POST', 'HTTP/1.1' );
193 utl_http.set_header(http_req, 'Content-Type', 'text/xml');
194 utl_http.set_header(http_req, 'Content-Length', LENGTH(soap_request));
195 utl_http.set_header(http_req, 'SOAPAction', '');
196 utl_http.write_text(http_req, soap_request);
197 http_resp:= utl_http.get_response(http_req);
198 utl_http.read_text(http_resp, soap_respond);
199 utl_http.end_response(http_resp);
200 SELECT instr(soap_respond,'<return>')
201 INTO StartIndex
202 FROM dual;
203
204 SELECT instr(soap_respond,'</return>')
205 INTO EndIndex
206 FROM dual;
207
208 SELECT SUBSTR(soap_respond,StartIndex+8,EndIndex-(StartIndex+8))
209 INTO returnStr
210 FROM dual;
211
212 SELECT instr(returnStr,'#')
213 INTO StartIndex
214 FROM dual;
215
216 SELECT SUBSTR(returnStr,0,StartIndex-1)
217 INTO SessionNum
218 FROM dual;
219
220 SELECT SUBSTR(returnStr,StartIndex+1,1800)
221 INTO ErrMessage
222 FROM dual;
223
224 IF SessionNum = '-1' THEN
225 finalreturnStr:= 'ODI Scenario:'
226 ||'UPDATEVARIABLE'
227 ||'#ODI Session No.#Error Message='
228 ||returnStr;
229 RETURN finalreturnStr;
230 END IF;
231
232 IF SessionNum <> '-1' AND LENGTH(ErrMessage) > 0 THEN
233 finalreturnStr:= 'ODI Scenario:'
234 ||'UPDATEVARIABLE'
235 ||'#ODI Session No.#Error Message='
236 ||returnStr;
237 RETURN finalreturnStr;
238 END IF;
239
240 -- Changes for 11G
241 IF SessionNum <> '-1' AND (LENGTH(ErrMessage) IS NULL) THEN
242 err_msg1 := GetODIErrorMessage(SessionNum);
243 IF (length(err_msg1) > 0) THEN
244 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'UPDATE VARIABLE FAILED. Session # ' || SessionNum);
245 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error : ' || err_msg1);
246 finalreturnStr := err_msg1;
247 RETURN finalreturnStr;
248 END IF;
249 END IF;
250 EXCEPTION
251 WHEN OTHERS THEN
252 finalreturnStr:= 'ODI Scenario:'
253 ||'UPDATEVARIABLE'
254 ||'#ODI Session No.#Error Message=-1#Error in execution of ODI Scenario. Please check Application Server Log.';
255 RETURN finalreturnStr;
256 END;
257
258 --***SYNCHRONIZE_XML****
259 BEGIN
260 --11g Web Logic SOAP Request
261 soap_request:= '<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://odiws.msc.com/"><env:Header/><env:Body><ns1:ExecuteScenario><arg0>'
262 || 'SYNCHRONIZE_XML</arg0><arg1>001</arg1>'
263 || '<arg2></arg2></ns1:ExecuteScenario>/env:Body></env:Envelope>';
264
265 Time_Out := fnd_profile.value('MSC_E1APS_WS_TIME_OUT');
266 UTL_HTTP.set_transfer_timeout(Time_Out);
267 -- UTL_HTTP.set_transfer_timeout(36000);
268 http_req:= utl_http.begin_request ( WsUrl, 'POST', 'HTTP/1.1' );
269 utl_http.set_header(http_req, 'Content-Type', 'text/xml');
270 utl_http.set_header(http_req, 'Content-Length', LENGTH(soap_request));
271 utl_http.set_header(http_req, 'SOAPAction', '');
272 utl_http.write_text(http_req, soap_request);
273 http_resp:= utl_http.get_response(http_req);
274 utl_http.read_text(http_resp, soap_respond);
275 utl_http.end_response(http_resp);
276 SELECT instr(soap_respond,'<return>')
277 INTO StartIndex
278 FROM dual;
279
280 SELECT instr(soap_respond,'</return>')
281 INTO EndIndex
282 FROM dual;
283
284 SELECT SUBSTR(soap_respond,StartIndex+8,EndIndex-(StartIndex+8))
285 INTO returnStr
286 FROM dual;
287
288 SELECT instr(returnStr,'#')
289 INTO StartIndex
290 FROM dual;
291
292 SELECT SUBSTR(returnStr,0,StartIndex-1)
293 INTO SessionNum
294 FROM dual;
295
296 SELECT SUBSTR(returnStr,StartIndex+1,1800)
297 INTO ErrMessage
298 FROM dual;
299
300 IF SessionNum = '-1' THEN
301 finalreturnStr:= 'ODI Scenario:'
302 ||'SYNCHRONIZE_XML'
303 ||'#ODI Session No.#Error Message='
304 ||returnStr;
305 RETURN finalreturnStr;
306 END IF;
307
308 IF SessionNum <> '-1' AND LENGTH(ErrMessage) > 0 THEN
309 finalreturnStr:= 'ODI Scenario:'
310 ||'SYNCHRONIZE_XML'
311 ||'#ODI Session No.#Error Message='
312 ||returnStr;
313 RETURN finalreturnStr;
314 END IF;
315
316 -- Changes for 11G
317 IF SessionNum <> '-1' AND (LENGTH(ErrMessage) IS NULL) THEN
318 err_msg1 := GetODIErrorMessage(SessionNum);
319 IF (length(err_msg1) > 0) THEN
320 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'SYNCHRONIZE XML FAILED. Session # ' || SessionNum);
321 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error : ' || err_msg1);
322 finalreturnStr := err_msg1;
323 RETURN finalreturnStr;
324 END IF;
325 END IF;
326 EXCEPTION
327 WHEN OTHERS THEN
328 finalreturnStr:= 'ODI Scenario:'
329 ||'SYNCHRONIZE_XML'
330 ||'#ODI Session No.#Error Message=-1#Error in invoking Web Service. Please check Application Server Log.';
331 RETURN finalreturnStr;
332 END;
333 RETURN finalreturnStr;
334 END; -- End for ELSE
335 END IF;
336 END MSC_E1APS_ODIInitialize ;
337
338 --Procedure to call Demantra WorkFlow
339 PROCEDURE DEM_WORKFLOW(errbuf OUT NOCOPY VARCHAR2,
340 retcode OUT NOCOPY VARCHAR2,
341 l_wf_lookup_code IN VARCHAR2,
342 process_id OUT NOCOPY VARCHAR2,
343 p_user_id IN NUMBER )
344 IS
345 /* Variables to Launch Dem WorkFlow */
346 l_sql VARCHAR2(1000);
347 DEM_SCHEMA VARCHAR2(100);
348 l_url VARCHAR2(1000);
349 l_dummy VARCHAR2(100);
350 l_user_name VARCHAR2(30);
351 l_password VARCHAR2(100);
352 l_user_id NUMBER;
353 -- Bug#7199587 syenamar
354 l_schema_name VARCHAR2(255);
355 l_schema_id NUMBER;
356 BEGIN
357 msd_dem_common_utilities.log_debug ('Entering: MSC_E1APS_UTIL.DEM_WORKFLOW - '
358 || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
359 DEM_SCHEMA := fnd_profile.value('MSD_DEM_SCHEMA');
360 IF fnd_profile.value('MSD_DEM_SCHEMA') IS NOT NULL THEN
361 l_user_id := p_user_id;
362 IF l_user_id IS NOT NULL THEN
363 l_sql := 'select user_name, password from '
364 ||dem_schema
365 ||'.user_id where user_id = '
366 ||l_user_id;
367 EXECUTE immediate l_sql INTO l_user_name, l_password;
368 ELSE
369 /* Bug#8224935 - APP ID */
370 l_user_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID', 'COMP_SOP', 1, 'user_id'));
371 IF l_user_id IS NOT NULL THEN
372 l_sql := 'select user_name, password from '
373 ||dem_schema
374 ||'.user_id where user_id = '
375 ||l_user_id;
376 EXECUTE immediate l_sql INTO l_user_name, l_password;
377 ELSE
378 msd_dem_common_utilities.log_message('Component is not found.');
379 END IF;
380 END IF;
381 IF l_user_name IS NOT NULL THEN
382 l_url := fnd_profile.value('MSD_DEM_HOST_URL');
383 -- Bug#7199587 syenamar
384 -- Do not hard-code 'EBS Full Download' workflow name here. Get its ID from lookup, get its name from demantra schema using the ID.
385 /* Bug#8224935 - APP ID */
386 -- l_wf_lookup_code is a schema_id in the database and meaning column in common lookup
387 l_schema_name := msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID', l_wf_lookup_code, 1, 'schema_name');
388 l_schema_name := trim(l_schema_name);
389 l_sql := NULL;
390 l_sql := 'SELECT
391 utl_http.request('''
392 ||l_url
393 ||'/WorkflowServer?action=run_proc&user='
394 ||l_user_name
395 ||'&password='
396 ||l_password
397 ||'&schema='
398 || REPLACE(l_schema_name, ' ', '%20')
399 ||'&sync=no'') FROM dual';
400 msd_dem_common_utilities.log_debug (l_sql);
401 EXECUTE immediate l_sql INTO process_id;
402 -- msd_dem_common_utilities.log_message('Process Id:'||l_dummy);
403 -- syenamar
404 ELSE
405 msd_dem_common_utilities.log_message('Error in launching the download workflow.');
406 retcode := -1;
407 RETURN;
408 END IF;
409 ELSE
410 msd_dem_common_utilities.log_message('Demantra Schema not set');
411 END IF;
412 /* ELSE
413 msd_dem_common_utilities.log_message ('Auto Run Download - No ');
414 msd_dem_common_utilities.log_message ('Exiting without launching the download workflow.');
415 END IF; */
416 msd_dem_common_utilities.log_debug ('Exiting: MSC_E1APS_UTIL.DEM_WORKFLOW - '
417 || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
418 EXCEPTION
419 WHEN OTHERS THEN
420 errbuf := SUBSTR(SQLERRM,1,150);
421 retcode := -1 ;
422 -- l_stmt := 'begin ' || DEM_SCHEMA || '.DATA_LOAD.LOG_EP_LOAD_FAILURE; end;';
423 -- execute immediate l_stmt;
424 -- l_stmt := 'alter session set current_schema=APPS';
425 -- execute immediate l_stmt;
426 msd_dem_common_utilities.log_message ('Exception: MSC_E1APS_UTIL.DEM_WORKFLOW - '
427 || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
428 msd_dem_common_utilities.log_message (errbuf);
429 RETURN;
430 END DEM_WORKFLOW;
431 /*Function To Launch Demantra Workflow and Executes ODI*/
432 FUNCTION PUBLISH_DEM_WORKFLOW(ERRBUF OUT NOCOPY VARCHAR2 ,
433 RETCODE OUT NOCOPY VARCHAR2 ,
434 p_instance_id IN NUMBER ,
435 l_wf_lookup_code IN VARCHAR2 ,
436 scenario_name IN VARCHAR2 ,
437 p_user_id IN NUMBER)
438 RETURN Number AS
439 /* Variables to Check Demantra WorkFlow is Completed */
440 dem_cnt NUMBER(2):=0;
441 dem_status NUMBER(2);
442 dem_flag NUMBER(2):= DEM_SUCCESS;
443 ret_process_id VARCHAR2(10);
444 v_sql VARCHAR2(200);
445 g_demantra_schema VARCHAR2(30);
446 /* Variables to Initialize and Execute ODI */
447 scenario_version VARCHAR2(100):= '001';
448 odi_url VARCHAR2(300);
449 ReturnStr VARCHAR2(2000);
450 -- ret_value boolean;
451 /* Variables to Execute FileCopy */
452 fc_url VARCHAR2(1000);
453 fc_ret_value BOOLEAN DEFAULT TRUE;
454 ret_value BOOLEAN DEFAULT TRUE;
455 ret_value1 BOOLEAN DEFAULT TRUE;
456 ret_value2 BOOLEAN DEFAULT TRUE;
457 ERRMESSAGE VARCHAR2(1900);
458 StartIndex INTEGER;
459 sleepTime NUMBER(2) :=60;
460 demTimeOut NUMBER;
461
462 BEGIN
463 /* Calling DEM WorkFlow */
464 DEM_WORKFLOW(errbuf, retcode, l_wf_lookup_code, ret_process_id, p_user_id);
465
466 /* Profile for Demantra WorkFlow TimeOut */
467 demTimeOut := fnd_profile.value('MSC_E1APS_DEM_WF_TIME_OUT');
468
469 IF retcode= -1 OR ret_process_id = -1 THEN
470 msd_dem_common_utilities.log_message('DEM WORKFLOW NOT LAUNCHED');
471 RETURN 2;
472 ELSE
473 msd_dem_common_utilities.log_message('DEM WORKFLOW LAUNCHED.Process Id:'
474 ||ret_process_id );
475 g_demantra_schema := msd_dem_demantra_utilities.get_demantra_schema;
476 LOOP
477 dem_cnt:=dem_cnt+ 1; -- +1 indicates 60 seconds
478 v_sql := 'select status from '
479 || g_demantra_schema
480 || '.wf_process_log'
481 || ' where '
482 || ' process_id '
483 || '='
484 || ret_process_id;
485 EXECUTE immediate v_sql INTO dem_status;
486 DBMS_LOCK.sleep(sleepTime);
487 IF dem_status = 0 THEN
488 msd_dem_common_utilities.log_message('DEMANTRA WORKFLOW COMPLETED SUCCESSFULLY');
489 EXIT;
490 elsif dem_cnt = demTimeOut THEN
491 msd_dem_common_utilities.log_message('DEMANTRA WORKFLOW TIMEOUT');
492 msd_dem_common_utilities.log_message('PLEASE CHECK THE DEMANTRA WORKFLOW STATUS.');
493 dem_flag:= DEM_WARNING;
494 EXIT;
495 elsif dem_status = -1 THEN
496 msd_dem_common_utilities.log_message('DEMANTRA WORKFLOW FAILED.');
497 dem_flag:= DEM_FAILURE;
498 EXIT;
499 END IF;
500 END LOOP;
501 IF dem_flag = DEM_WARNING THEN
502 RETURN DEM_WARNING;
503 END IF;
504
505 IF dem_flag = DEM_FAILURE THEN
506 RETURN DEM_FAILURE;
507 END IF;
508
509 IF dem_flag = DEM_SUCCESS THEN
510 /*Copies the file from Demantra Server To E1_file_DS */
511 fc_url := fnd_profile.value('MSC_E1APS_FCURL');
512 IF fc_url IS NOT NULL THEN
513 fc_ret_value :=MSC_E1APS_DEMCL.CALL_ODIEXE('EXPORTFILESFROMDEMANTRASERVER' ,scenario_version ,'' ,fc_url);
514 IF fc_ret_value = FALSE THEN
515 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'File copy failed.' );
516 RETURN DEM_FAILURE;
517 END IF;
518 ELSE
519 RETURN DEM_SUCCESS;
520 END IF;
521
522 IF fc_ret_value THEN
523 ret_value1:=MSC_E1APS_DEMCL.CALL_ODIEXE(scenario_name ,scenario_version ,'' ,fc_url);
524 END IF;
525
526 IF ret_value1 THEN
527 RETURN DEM_SUCCESS;
528 ELSE
529 RETURN DEM_FAILURE;
530 END IF;
531 END IF;
532 END IF;
533 END PUBLISH_DEM_WORKFLOW;
534
535 /* Function to Check the ODI Scenario Status using Database Link*/
536 FUNCTION OdiSessionStatus( scenario_name IN VARCHAR2 )
537 RETURN Boolean
538 IS
539 x_sql varchar2(500) :=null;
540 DbLink varchar2(100);
541 SessionStatus VARCHAR2(2);
542 ResultFlag Number;
543 TIME_OUT INTEGER;
544 v_Sleep INTEGER :=0;
545 Session_number Number;
546
547
548 Begin
549 DbLink := fnd_profile.value('MSC_E1_DBLINK');
550 Time_Out := fnd_profile.value('MSC_E1APS_WS_TIME_OUT');
551
552 IF (Time_Out is NULL)Then
553 Time_Out:=36000;
554 END IF;
555 IF (DbLink is Not NULL) Then
556 x_sql := 'select sess_no,sess_status from snp_session@'|| DbLink||
557 ' where scen_name= '''||scenario_name||''' and rownum=1 order by sess_no desc ';
558
559 Else
560 x_sql := 'select sess_no,sess_status from snp_session
561 where scen_name= '''||scenario_name||'''
562 and rownum=1 order by sess_no desc ';
563 END IF;
564
565 LOOP
566 EXECUTE immediate x_sql into Session_number,SessionStatus;
567
568 IF (SESSIONSTATUS = 'D' or SESSIONSTATUS ='M') then /* Checking for Success or Warning*/
569 ResultFlag := 1;
570 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,' ' || scenario_name ||' SUCCESS');
571 EXIT;
572 ELSIF (SESSIONSTATUS = 'R') then /*Checking for Scenario still Running*/
573 ResultFlag := 2;
574 ELSIF (SESSIONSTATUS = 'E') then /*Checking for Scenario failure*/
575 ResultFlag := 0;
576 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Scenario ' || scenario_name|| ' executed with errors. Session # ' || Session_number );
577 EXIT;
578 END IF;
579
580 DBMS_LOCK.sleep(60);/*Sleeps the execution for 60 seconds*/
581 v_Sleep:= v_Sleep+60; /*Counter value for Sleep */
582
583 IF (v_Sleep = Time_Out) Then
584 ResultFlag := 0;
585 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Session Time out after' || Time_Out ||'Seconds Session No#'|| Session_number|| ' Please set maximum time to profile MSC_E1APS_WS_TIME_OUT');
586 EXIT;
587 END IF;
588 END LOOP;
589
590 IF ResultFlag=1 THEN
591 return TRUE;
592 Elsif ResultFlag=0 THEN
593 return FALSE;
594 END IF;
595 --return ResultFlag;
596 END ODISESSIONSTATUS;
597
598 /* Procedure to Update Trading Partner Site Code bug#9757888*/
599 PROCEDURE UPDATE_TP_SITE_CODE(errbuf OUT NOCOPY VARCHAR2,
600 retcode OUT NOCOPY VARCHAR2,
601 parInstanceID IN VARCHAR2,
602 updateFlag IN NUMBER )
603 IS
604
605 lv_sql_stmt VARCHAR2(2000);
606 e1_instance_name VARCHAR2(15) DEFAULT NULL;
607 x_sql VARCHAR2(500) := NULL;
608 x_dem_schema VARCHAR2(100) := NULL;
609 x_table_presence NUMBER(1) := 0 ;
610
611 BEGIN
612
613 -- Bug#14010362 nallkuma
614 SELECT NVL(count(1),0) into x_table_presence
615 FROM dba_tables
616 WHERE owner = owner AND table_name = 'MDP_MATRIX';
617
618 IF (updateFlag = MSC_UTIL.SYS_NO) THEN
619 IF (fnd_profile.value('MSD_DEM_SCHEMA') IS NOT NULL AND x_table_presence > 0) THEN
620 x_dem_schema := fnd_profile.value('MSD_DEM_SCHEMA');
621 IF (fnd_profile.value('MSD_DEM_TRUNCATE_STG_TABLE') = 'Y' ) THEN
622 x_sql := 'truncate table ' || x_dem_schema || '.t_src_sales_tmpl';
623 execute immediate x_sql;
624 x_sql := 'truncate table ' || x_dem_schema || '.t_src_item_tmpl';
625 execute immediate x_sql;
626 x_sql := 'truncate table ' || x_dem_schema || '.t_src_loc_tmpl';
627 execute immediate x_sql;
628 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Truncated tables t_src_sales_tmpl/item/loc.');
629 END IF;
630 END IF;
631 END IF;
632
633
634 -- Fix For 9757888 (Only for E1 VCP Integration)
635 -- Updating TP_SITE_CODE with BILL_TO and SHIP_TO .
636 -- If Partner Address like 'E1_BILL_TO+9718+9718+Oilers Bikes Inc.'
637 -- Then the site is BILL_TO else catogorizes as SHIP_TO SITE.
638
639 IF fnd_profile.value('MSC_E1APS_ODIURL') IS NOT NULL THEN
640 select attribute15 into e1_instance_name from msc_apps_instances
641 where instance_id = parInstanceID;
642 --check If instance using both Legacy and E1 Intergration
643 --bug#10021262
644 IF e1_instance_name is not null THEN
645
646 -- Profile Check for E1VCP Integration
647
648 IF updateFlag = MSC_UTIL.SYS_YES THEN
649 -- TO Update TP_SITE_CODE to BILL_TO
650 lv_sql_stmt :=
651 'UPDATE msc_trading_partner_sites '
652 ||' SET TP_SITE_CODE = ''BILL_TO'' '
653 ||' WHERE partner_type = ' || MSC_CL_PRE_PROCESS.G_CUSTOMER
654 ||' AND sr_instance_id = ' || parInstanceID
655 ||' AND partner_address like ''E1_BILL_TO%''';
656
657 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
658 EXECUTE IMMEDIATE lv_sql_stmt;
659 COMMIT;
660
661 --TO Update TP_SITE_CODE to SHIP_TO
662 lv_sql_stmt :=
663 'UPDATE msc_trading_partner_sites '
664 ||' SET TP_SITE_CODE = ''SHIP_TO'' '
665 ||' WHERE partner_type = ' || MSC_CL_PRE_PROCESS.G_CUSTOMER
666 ||' AND sr_instance_id = ' || parInstanceID
667 ||' AND partner_address not like ''E1_BILL_TO%'''; -- Removed comment for bug# 12558193
668 -- OR partner_address is null )';
669
670 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
671
672 EXECUTE IMMEDIATE lv_sql_stmt;
673 COMMIT;
674
675 ELSIF updateFlag = MSC_UTIL.SYS_NO THEN
676 -- To Update TP_SITE_CODE with Location
677
678 lv_sql_stmt :=
679 'UPDATE msc_trading_partner_sites '
680 ||' SET TP_SITE_CODE = LOCATION '
681 ||' WHERE partner_type = ' || MSC_CL_PRE_PROCESS.G_CUSTOMER
682 ||' AND sr_instance_id = ' || parInstanceID ;
683
684 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
685 EXECUTE IMMEDIATE lv_sql_stmt;
686 COMMIT;
687 END IF ;
688 END IF;
689 END IF;
690
691 exception
692 when others then
693 errbuf := ' TP_SITE_CODE Update failed ';
694 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,substr(SQLERRM,1,150));
695 retcode := MSC_UTIL.G_ERROR;
696
697 END UPDATE_TP_SITE_CODE;
698
699
700 -- Function to check and return Error messages for 11G changes
701 FUNCTION GetODIErrorMessage (session_no IN VARCHAR2)
702 RETURN VARCHAR2
703 IS
704 chk_sql varchar2(1900);
705 DbLink varchar2(100);
706 err_msg1 varchar2(4000);
707 BEGIN
708 -- Get profile value and check for DB errors
709 DbLink := fnd_profile.value('MSC_E1_DBLINK');
710
711 if (DbLink is NOT NULL) then
712 chk_sql := 'select err_msg from wr_execution_status@' ||DbLink|| ' where session_id = '''|| session_no || '''';
713 else
714 chk_sql := 'select err_msg from wr_execution_status where session_id = ''' || session_no || '''';
715 end if;
716
717 -- Get the actual error message
718 EXECUTE immediate chk_sql into err_msg1;
719 Return err_msg1;
720 EXCEPTION
721 WHEN NO_DATA_FOUND THEN
722 err_msg1 := NULL;
723 RETURN err_msg1;
724 WHEN OTHERS THEN
725 err_msg1 := SQLERRM;
726 RETURN err_msg1;
727 END; -- END OF Function GetODIErrorMessage
728
729 -- Following 2 functions added for bug # 11713909
730 FUNCTION MSC_E1APS_ODIScenExecute10g ( ScenarioName IN VARCHAR2,
731 ScenarioVersion IN VARCHAR2,
732 ScenarioParam IN VARCHAR2,
733 WsUrl IN VARCHAR2 )
734 RETURN VARCHAR2 AS soap_request VARCHAR2(30000);
735 soap_respond VARCHAR2(30000);
736 http_req utl_http.req;
737 http_resp utl_http.resp;
738 StartIndex INTEGER;
739 EndIndex INTEGER;
740 returnStr VARCHAR2(2000);
741 Time_Out INTEGER;
742 BEGIN
743 soap_request:= '<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns0="http://odiws.msc.com/">'
744 || '<env:Body>'
745 || '<ns0:ExecuteScenario>'
746 || '<ns0:ScenarioName>'
747 ||ScenarioName
748 ||'</ns0:ScenarioName>'
749 || '<ns0:ScenarioVersion>'
750 ||ScenarioVersion
751 ||'</ns0:ScenarioVersion>'
752 || '<ns0:ODIParameter>'
753 ||ScenarioParam
754 ||'</ns0:ODIParameter>'
755 || '</ns0:ExecuteScenario>'
756 || '</env:Body>'
757 ||'</env:Envelope>';
758
759 Time_Out := fnd_profile.value('MSC_E1APS_WS_TIME_OUT');
760 UTL_HTTP.set_transfer_timeout(Time_Out);
761 -- UTL_HTTP.set_transfer_timeout(36000);
762
763 http_req:= utl_http.begin_request ( WsUrl, 'POST', 'HTTP/1.1' );
764 utl_http.set_header(http_req, 'Content-Type', 'text/xml');
765 utl_http.set_header(http_req, 'Content-Length', LENGTH(soap_request));
766 utl_http.set_header(http_req, 'SOAPAction', '');
767 utl_http.write_text(http_req, soap_request);
768 http_resp:= utl_http.get_response(http_req);
769 utl_http.read_text(http_resp, soap_respond);
770 utl_http.end_response(http_resp);
771 SELECT instr(soap_respond,'<ns0:return>')
772 INTO StartIndex
773 FROM dual;
774
775 SELECT instr(soap_respond,'</ns0:return>')
776 INTO EndIndex
777 FROM dual;
778
779 SELECT SUBSTR(soap_respond,StartIndex+12,EndIndex-(StartIndex+12))
780 INTO returnStr
781 FROM dual;
782
783 RETURN returnStr;
784 EXCEPTION
785 WHEN OTHERS THEN
786 returnStr:= '-1#Error in execution of ODI Scenario:'
787 ||ScenarioName
788 ||'. Please check Application Server Log.';
789 RETURN returnStr;
790 END MSC_E1APS_ODIScenExecute10g;
791
792 FUNCTION MSC_E1APS_ODIInitialize10g ( WsUrl IN VARCHAR2,
793 BaseDate INTEGER )
794 RETURN VARCHAR2 AS soap_request VARCHAR2(30000);
795 soap_respond VARCHAR2(30000);
796 http_req utl_http.req;
797 http_resp utl_http.resp;
798 StartIndex INTEGER;
799 EndIndex INTEGER;
800 SessionNum VARCHAR2(10);
801 ErrMessage VARCHAR2(1900);
802 ErrLength INTEGER;
803 returnStr VARCHAR2(2000);
804 finalreturnStr VARCHAR2(5000);
805 Time_Out INTEGER;
806 BEGIN
807 finalreturnStr:=NULL;
808
809 /*LOADPARAMETERSDATATOWORKREPPKG*/
810 BEGIN
811 soap_request:= '<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns0="http://odiws.msc.com/">'
812 || '<env:Body>'
813 || '<ns0:ExecuteScenario>'
814 || '<ns0:ScenarioName>LOADPARAMETERSDATATOWORKREPPKG</ns0:ScenarioName>'
815 || '<ns0:ScenarioVersion>001</ns0:ScenarioVersion>'
816 || '<ns0:ODIParameter></ns0:ODIParameter>'
817 || '</ns0:ExecuteScenario>'
818 || '</env:Body>'
819 ||'</env:Envelope>';
820
821 Time_Out := fnd_profile.value('MSC_E1APS_WS_TIME_OUT');
822 UTL_HTTP.set_transfer_timeout(Time_Out);
823 -- UTL_HTTP.set_transfer_timeout(36000);
824 http_req:= utl_http.begin_request ( WsUrl, 'POST', 'HTTP/1.1' );
825 utl_http.set_header(http_req, 'Content-Type', 'text/xml');
826 utl_http.set_header(http_req, 'Content-Length', LENGTH(soap_request));
827 utl_http.set_header(http_req, 'SOAPAction', '');
828 utl_http.write_text(http_req, soap_request);
829 http_resp:= utl_http.get_response(http_req);
830 utl_http.read_text(http_resp, soap_respond);
831 utl_http.end_response(http_resp);
832 SELECT instr(soap_respond,'<ns0:return>')
833 INTO StartIndex
834 FROM dual;
835
836 SELECT instr(soap_respond,'</ns0:return>')
837 INTO EndIndex
838 FROM dual;
839
840 SELECT SUBSTR(soap_respond,StartIndex+12,EndIndex-(StartIndex+12))
841 INTO returnStr
842 FROM dual;
843
844 SELECT instr(returnStr,'#')
845 INTO StartIndex
846 FROM dual;
847
848 SELECT SUBSTR(returnStr,0,StartIndex-1)
849 INTO SessionNum
850 FROM dual;
851
852 SELECT SUBSTR(returnStr,StartIndex+1,1800)
853 INTO ErrMessage
854 FROM dual;
855
856 IF SessionNum = '-1' THEN
857 finalreturnStr:= 'ODI Scenario:'
858 ||'LOADPARAMETERSDATATOWORKREPPKG'
859 ||'#ODI Session No.#Error Message='
860 ||returnStr;
861 RETURN finalreturnStr;
862 END IF;
863 IF SessionNum <> '-1' AND LENGTH(ErrMessage) > 0 THEN
864 finalreturnStr:= 'ODI Scenario:'
865 ||'LOADPARAMETERSDATATOWORKREPPKG'
866 ||'#ODI Session No.#Error Message='
867 ||returnStr;
868 RETURN finalreturnStr;
869 END IF;
870 EXCEPTION
871 WHEN OTHERS THEN
872 finalreturnStr:= 'ODI Scenario:'
873 ||'LOADPARAMETERSDATATOWORKREPPKG'
874 ||'#ODI Session No.#Error Message=-1#Error in execution of ODI scenario. Please check Application Server Log.';
875 RETURN finalreturnStr;
876 END;
877
878 /*UPDATEVARIABLE*/
879 BEGIN
880 soap_request:= '<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns0="http://odiws.msc.com/">'
881 || '<env:Body>'
882 || '<ns0:ExecuteScenario>'
883 || '<ns0:ScenarioName>UPDATEVARIABLE</ns0:ScenarioName>'
884 || '<ns0:ScenarioVersion>001</ns0:ScenarioVersion>'
885 || '<ns0:ODIParameter>E1TOAPSPROJECT.PVD_BASE_DATE='||BaseDate ||'</ns0:ODIParameter>'
886 || '</ns0:ExecuteScenario>'
887 || '</env:Body>'
888 ||'</env:Envelope>';
889 UTL_HTTP.set_transfer_timeout(Time_Out);
890 --UTL_HTTP.set_transfer_timeout(36000);
891 http_req:= utl_http.begin_request ( WsUrl, 'POST', 'HTTP/1.1' );
892 utl_http.set_header(http_req, 'Content-Type', 'text/xml');
893 utl_http.set_header(http_req, 'Content-Length', LENGTH(soap_request));
894 utl_http.set_header(http_req, 'SOAPAction', '');
895 utl_http.write_text(http_req, soap_request);
896 http_resp:= utl_http.get_response(http_req);
897 utl_http.read_text(http_resp, soap_respond);
898 utl_http.end_response(http_resp);
899 SELECT instr(soap_respond,'<ns0:return>')
900 INTO StartIndex
901 FROM dual;
902
903 SELECT instr(soap_respond,'</ns0:return>')
904 INTO EndIndex
905 FROM dual;
906
907 SELECT SUBSTR(soap_respond,StartIndex+12,EndIndex-(StartIndex+12))
908 INTO returnStr
909 FROM dual;
910
911 SELECT instr(returnStr,'#')
912 INTO StartIndex
913 FROM dual;
914
915 SELECT SUBSTR(returnStr,0,StartIndex-1)
916 INTO SessionNum
917 FROM dual;
918
919 SELECT SUBSTR(returnStr,StartIndex+1,1800)
920 INTO ErrMessage
921 FROM dual;
922
923 IF SessionNum = '-1' THEN
924 finalreturnStr:= 'ODI Scenario:'
925 ||'UPDATEVARIABLE'
926 ||'#ODI Session No.#Error Message='
927 ||returnStr;
928 RETURN finalreturnStr;
929 END IF;
930 IF SessionNum <> '-1' AND LENGTH(ErrMessage) > 0 THEN
931 finalreturnStr:= 'ODI Scenario:'
932 ||'UPDATEVARIABLE'
933 ||'#ODI Session No.#Error Message='
934 ||returnStr;
935 RETURN finalreturnStr;
936 END IF;
937 EXCEPTION
938 WHEN OTHERS THEN
939 finalreturnStr:= 'ODI Scenario:'
940 ||'UPDATEVARIABLE'
941 ||'#ODI Session No.#Error Message=-1#Error in execution of ODI Scenario. Please check Application Server Log.';
942 RETURN finalreturnStr;
943 END;
944
945 /*SYNCHRONIZE_XML*/
946 BEGIN
947 soap_request:= '<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns0="http://odiws.msc.com/">'
948 || '<env:Body>'
949 || '<ns0:ExecuteScenario>'
950 || '<ns0:ScenarioName>SYNCHRONIZE_XML</ns0:ScenarioName>'
951 || '<ns0:ScenarioVersion>001</ns0:ScenarioVersion>'
952 || '<ns0:ODIParameter></ns0:ODIParameter>'
953 || '</ns0:ExecuteScenario>'
954 || '</env:Body>'
955 ||'</env:Envelope>';
956
957 Time_Out := fnd_profile.value('MSC_E1APS_WS_TIME_OUT');
958 UTL_HTTP.set_transfer_timeout(Time_Out);
959 -- UTL_HTTP.set_transfer_timeout(36000);
960 http_req:= utl_http.begin_request ( WsUrl, 'POST', 'HTTP/1.1' );
961 utl_http.set_header(http_req, 'Content-Type', 'text/xml');
962 utl_http.set_header(http_req, 'Content-Length', LENGTH(soap_request));
963 utl_http.set_header(http_req, 'SOAPAction', '');
964 utl_http.write_text(http_req, soap_request);
965 http_resp:= utl_http.get_response(http_req);
966 utl_http.read_text(http_resp, soap_respond);
967 utl_http.end_response(http_resp);
968 SELECT instr(soap_respond,'<ns0:return>')
969 INTO StartIndex
970 FROM dual;
971
972 SELECT instr(soap_respond,'</ns0:return>')
973 INTO EndIndex
974 FROM dual;
975
976 SELECT SUBSTR(soap_respond,StartIndex+12,EndIndex-(StartIndex+12))
977 INTO returnStr
978 FROM dual;
979
980 SELECT instr(returnStr,'#')
981 INTO StartIndex
982 FROM dual;
983
984 SELECT SUBSTR(returnStr,0,StartIndex-1)
985 INTO SessionNum
986 FROM dual;
987
988 SELECT SUBSTR(returnStr,StartIndex+1,1800)
989 INTO ErrMessage
990 FROM dual;
991
992 IF SessionNum = '-1' THEN
993 finalreturnStr:= 'ODI Scenario:'
994 ||'SYNCHRONIZE_XML'
995 ||'#ODI Session No.#Error Message='
996 ||returnStr;
997 RETURN finalreturnStr;
998 END IF;
999 IF SessionNum <> '-1' AND LENGTH(ErrMessage) > 0 THEN
1000 finalreturnStr:= 'ODI Scenario:'
1001 ||'SYNCHRONIZE_XML'
1002 ||'#ODI Session No.#Error Message='
1003 ||returnStr;
1004 RETURN finalreturnStr;
1005 END IF;
1006 EXCEPTION
1007 WHEN OTHERS THEN
1008 finalreturnStr:= 'ODI Scenario:'
1009 ||'SYNCHRONIZE_XML'
1010 ||'#ODI Session No.#Error Message=-1#Error in invoking Web Service. Please check Application Server Log.';
1011 RETURN finalreturnStr;
1012 END;
1013
1014 RETURN finalreturnStr;
1015 END MSC_E1APS_ODIInitialize10g;
1016 END MSC_E1APS_UTIL;