1 PACKAGE BODY WSH_ITM_ASYN_MSGMAP_ASSISTANT AS
2 /* $Header: WSHITMAB.pls 120.2.12010000.4 2010/04/08 13:09:15 skanduku ship $ */
3 --
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_ITM_ASYN_MSGMAP_ASSISTANT';
5 G_ITM_LOG_LEVEL WSH_ITM_PARAMETER_SETUPS_B.VALUE%TYPE;
6 G_LOG_FILENAME VARCHAR2(100);
7 G_FILE_PTR UTL_FILE.File_Type;
8 G_REQ_CONTROL_ID VARCHAR(50); --Bug 9226895 changed from number
9 G_PRV_REQ_CONTROL_ID VARCHAR(50):= '-1';--Bug 9226895
10 G_DEBUG_PROCESS BOOLEAN;
11 --
12 PROCEDURE Close_Debug;
13 --
14 /*
15 ** This procedure is used have a post process Procedure call
16 ** that updates the request control table with appropriate
17 ** response header and process flag values
18 **
19 ** p_request_control_id - REQUEST CONTROL ID
20 ** p_response_header_id - RESPONSE HEADER ID
21 ** p_process_flag - PROCESS FLAG
22 **
23 */
24 PROCEDURE UPDATE_REQCTRL_REC( p_request_control_id IN NUMBER,
25 p_response_header_id IN NUMBER,
26 p_process_flag IN NUMBER) IS
27 --
28 l_debug_on BOOLEAN;
29 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_REQCTRL_REC';
30 --
31 BEGIN
32 --
33
34 --
35 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
36 --
37 IF ( l_debug_on IS NULL )
38 THEN
39 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
40 END IF;
41 --
42 IF l_debug_on THEN
43 WSH_DEBUG_SV.Push(l_module_name);
44 WSH_DEBUG_SV.Log(l_module_name, 'p_request_control_id', p_request_control_id );
45 WSH_DEBUG_SV.Log(l_module_name, 'p_response_header_id', p_response_header_id );
46 WSH_DEBUG_SV.Log(l_module_name, 'p_process_flag', p_process_flag );
47 END IF;
48
49 --UPDATING TABLE WSH_ITM_REQUEST_CONTROL WITH RESPECTIVE
50 -- RESPONSE_HEADER_ID and PROCESS_FLAG
51
52 UPDATE WSH_ITM_REQUEST_CONTROL SET
53 RESPONSE_HEADER_ID = p_response_header_id,
54 PROCESS_FLAG = p_process_flag
55 WHERE REQUEST_CONTROL_ID = p_request_control_id;
56 --
57 IF l_debug_on THEN
58 WSH_DEBUG_SV.Pop(l_module_name);
59 END IF;
60 --
61
62 END UPDATE_REQCTRL_REC;
63
64 /*
65 ** This procedure is used when there is an error tag in the header
66 ** level, i.e.. under response tag and it for multiple requests clubbed
67 ** in a single XML Request. In this case, multiple response headers
68 ** should be created for the respective request records and their request
69 ** records updated with the appropriate values.
70 **
71 ** p_message_id - MESSAGE ID (part of incoming response message)
72 ** p_error_code - ERROR CODE
73 ** p_error_type - ERROR TYPE
74 ** p_error_text - ERROR MESSAGE
75 **
76 */
77 PROCEDURE INSERT_ERROR( p_message_id IN VARCHAR2,
78 p_error_code IN VARCHAR2,
79 p_error_type IN VARCHAR2,
80 p_error_text IN VARCHAR2) IS
81 l_reqclrt_ids VARCHAR2(2000); --Bug 9226895
82 l_service_type_code VARCHAR2(10);
83 l_vendor_id VARCHAR2(10);
84 l_reqclrt_id VARCHAR2(50);
85 l_response_header_id NUMBER DEFAULT -99;
86 --
87 l_debug_on BOOLEAN;
88 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ERROR';
89 --
90 BEGIN
91 --
92 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
93 --
94 IF ( l_debug_on IS NULL )
95 THEN
96 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
97 END IF;
98 --
99 IF l_debug_on THEN
100 WSH_DEBUG_SV.Push(l_module_name);
101 WSH_DEBUG_SV.Log(l_module_name, 'p_message_id', p_message_id );
102 WSH_DEBUG_SV.Log(l_module_name, 'p_error_code', p_error_code );
103 WSH_DEBUG_SV.Log(l_module_name, 'p_error_type', p_error_type );
104 WSH_DEBUG_SV.Log(l_module_name, 'p_error_text', p_error_text );
105 END IF;
106 --
107
108 TOKENIZE_MESSAGEID(p_message_id, l_reqclrt_ids, l_service_type_code, l_vendor_id);
109
110 LOOP
111 l_reqclrt_id := SUBSTR(l_reqclrt_ids, 0, (INSTR(l_reqclrt_ids, '-')-1));
112 IF (l_reqclrt_id IS NULL) THEN
113
114
115 BEGIN
116 SELECT WSH_ITM_RESPONSE_HEADERS_S.NEXTVAL INTO l_response_header_id FROM DUAL;
117 INSERT INTO WSH_ITM_RESPONSE_HEADERS
118 (
119 RESPONSE_HEADER_ID,
120 REQUEST_CONTROL_ID,
121 VENDOR_ID,
122 SERVICE_TYPE_CODE,
123 MESSAGE_ID,
124 RESPONSE_DATE,
125 ERROR_TYPE,
126 ERROR_CODE,
127 ERROR_TEXT,
128 LAST_UPDATE_DATE,
129 LAST_UPDATED_BY,
130 CREATION_DATE,
131 CREATED_BY,
132 LAST_UPDATE_LOGIN
133 )
134 VALUES
135 (
136 l_response_header_id,
137 l_reqclrt_ids,
138 l_vendor_id,
139 l_service_type_code,
140 p_message_id,
141 SYSDATE,
142 p_error_type,
143 p_error_code,
144 p_error_text,
145 SYSDATE,
146 1,
147 SYSDATE,
148 1,
149 1
150 );
151 EXCEPTION
152 WHEN OTHERS THEN
153 NULL;
154 END;
155
156 UPDATE WSH_ITM_REQUEST_CONTROL SET
157 PROCESS_FLAG = 2,
158 RESPONSE_HEADER_ID = l_response_header_id
159 WHERE REQUEST_CONTROL_ID=l_reqclrt_ids;
160
161 EXIT;
162 ELSE
163
164 BEGIN
165
166 SELECT WSH_ITM_RESPONSE_HEADERS_S.NEXTVAL INTO l_response_header_id FROM DUAL;
167 INSERT INTO WSH_ITM_RESPONSE_HEADERS
168 (
169 RESPONSE_HEADER_ID,
170 REQUEST_CONTROL_ID,
171 VENDOR_ID,
172 SERVICE_TYPE_CODE,
173 MESSAGE_ID,
174 RESPONSE_DATE,
175 ERROR_TYPE,
176 ERROR_CODE,
177 ERROR_TEXT,
178 LAST_UPDATE_DATE,
179 LAST_UPDATED_BY,
180 CREATION_DATE,
181 CREATED_BY,
182 LAST_UPDATE_LOGIN
183 )
184 VALUES
185 (
186 l_response_header_id,
187 l_reqclrt_id,
188 l_vendor_id,
189 l_service_type_code,
190 p_message_id,
191 SYSDATE,
192 p_error_type,
193 p_error_code,
194 p_error_text,
195 SYSDATE,
196 1,
197 SYSDATE,
198 1,
199 1
200 );
201 EXCEPTION
202 WHEN OTHERS THEN
203 NULL;
204 END;
205
206 UPDATE WSH_ITM_REQUEST_CONTROL SET
207 PROCESS_FLAG = 2,
208 RESPONSE_HEADER_ID = l_response_header_id
209 WHERE REQUEST_CONTROL_ID = l_reqclrt_id;
210
211 END IF;
212 l_reqclrt_ids := SUBSTR(l_reqclrt_ids, (INSTR(l_reqclrt_ids, '-')+1));
213 END LOOP;
214 --
215 IF l_debug_on THEN
216 WSH_DEBUG_SV.Pop(l_module_name);
217 END IF;
218 --
219
220 END INSERT_ERROR;
221
222 /*
223 ** This procedure is used especially for parsing the message_id
224 ** value and returns req_ctrl_ids, service_code for the request, vendor_id.
225
226 **
227 ** p_message_id - MESSAGE ID
228 ** x_reqclrt_ids - REQUEST CONTROL IDs separated by '-'
229 ** x_service_type_code - SERVICE CODES for current trasaction
230 ** x_vendor_id - VENDOR Id of vendor used for the
231 ** transaction
232 **
233 */
234 PROCEDURE TOKENIZE_MESSAGEID( p_message_id IN VARCHAR2,
235 x_reqclrt_ids OUT NOCOPY VARCHAR2,
236 x_service_type_code OUT NOCOPY VARCHAR2,
237 x_vendor_id OUT NOCOPY VARCHAR2) IS
238 l_temp_id VARCHAR2(500);
239 --
240 l_debug_on BOOLEAN;
241 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'TOKENIZE_MESSAGEID';
242 --
243
244 BEGIN
245 --
246 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
247 --
248 IF ( l_debug_on IS NULL )
249 THEN
250 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
251 END IF;
252 --
253 IF l_debug_on THEN
254 WSH_DEBUG_SV.Push(l_module_name);
255 WSH_DEBUG_SV.Log(l_module_name, 'p_message_id', p_message_id );
256 END IF;
257 --
258
259 x_reqclrt_ids := substr(p_message_id, 0, (instr(p_message_id, '~')-1));
260 l_temp_id := substr(p_message_id, (instr(p_message_id, '~')+1));
261 x_service_type_code := substr(l_temp_id, 0, (instr(l_temp_id, '~')-1));
262 x_vendor_id := substr(l_temp_id, (instr(l_temp_id, '~')+1));
263 --
264 IF l_debug_on THEN
265 WSH_DEBUG_SV.Pop(l_module_name);
266 END IF;
267 --
268 END TOKENIZE_MESSAGEID;
269
270 /*
271 ** This procedure is used especially for parsing the party ID
272 ** which comes as part of the incoming XML response and
273 ** retuns the party_id that maps to WSH_ITM_PARTIES Table as out variables.
274 **
275 ** p_party_id - PARTY ID AS PER THE RESPONSE XML MESSAGE
276 ** x_actual_party_id - Party ID as per in WSH_ITM_PARTIES Table
277 **
278 */
279 PROCEDURE TOKENIZE_PARTYID( p_party_id IN VARCHAR2,
280 x_actual_party_id OUT NOCOPY VARCHAR2) IS
281 --
282 l_debug_on BOOLEAN;
283 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'TOKENIZE_PARTYID';
284 --
285 BEGIN
286 --
287 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
288 --
289 IF ( l_debug_on IS NULL )
290 THEN
291 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
292 END IF;
293 --
294 IF l_debug_on THEN
295 WSH_DEBUG_SV.Push(l_module_name);
296 WSH_DEBUG_SV.Log(l_module_name, 'p_party_id', p_party_id );
297 END IF;
298 --
299
300 x_actual_party_id := substr(p_party_id, (instr(p_party_id, '-')+1));
301 --
302 IF l_debug_on THEN
303 WSH_DEBUG_SV.Pop(l_module_name);
304 END IF;
305 --
306
307 END TOKENIZE_PARTYID;
308
309 /*
310 ** For processing response Acknowledgements, in case the request is sent
311 ** Asynchronously and the acknowledgment is also received Asynchronously,
312 ** the Message Map has calls this procedure to update the process flag to
313 ** '-4'(Marking the request record as ACKNOLEDGMENT received.
314 **
315 ** p_message_id - MESSAGE ID for which acknowledgement is received.
316 **
317 */
318 PROCEDURE PROCESS_ACK_RECEIPT(p_message_id IN VARCHAR2) IS
319 l_reqclrt_ids VARCHAR2(2000); --Bug 9226895
320 l_reqclrt_id varchar2(50);
321 l_service_type_code VARCHAR2(250);
322 l_vendor_id VARCHAR2(100);
323 --
324 l_debug_on BOOLEAN;
325 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_ACK_RECEIPT';
326 --
327 BEGIN
328 --
329 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
330 --
331 IF ( l_debug_on IS NULL )
332 THEN
333 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
334 END IF;
335 --
336 IF l_debug_on THEN
337 WSH_DEBUG_SV.Push(l_module_name);
338 WSH_DEBUG_SV.Log(l_module_name, 'p_message_id', p_message_id );
339 END IF;
340 --
341
342 --GET THE ERQUEST CONTROL IDS
343 TOKENIZE_MESSAGEID(p_message_id, l_reqclrt_ids, l_service_type_code, l_vendor_id);
344 --CURRENTLY WE UPDATE THE WSH_REQUEST_CONTROL TABLE
345 -- PROCESS FLAG TO -4 FOR ALL REQUEST_CONTROL_IDS
346 -- WHO HAVE NOT RECEIVED ANY ACTUAL RESPONSES.
347 loop
348 l_reqclrt_id := substr(l_reqclrt_ids, 0, (instr(l_reqclrt_ids, '-')-1));
349 IF (l_reqclrt_id IS NULL) THEN
350
351 UPDATE WSH_ITM_REQUEST_CONTROL
352 SET PROCESS_FLAG = -4
353 WHERE REQUEST_CONTROL_ID = l_reqclrt_ids
354 AND PROCESS_FLAG IN (-2, -3);
355 EXIT;
356 ELSE
357 UPDATE WSH_ITM_REQUEST_CONTROL
358 SET PROCESS_FLAG = -4
359 WHERE REQUEST_CONTROL_ID = l_reqclrt_id
360 AND PROCESS_FLAG IN (-2, -3);
361 END IF;
362 l_reqclrt_ids := substr(l_reqclrt_ids, (instr(l_reqclrt_ids, '-')+1));
363 end loop;
364 --
365 IF l_debug_on THEN
366 WSH_DEBUG_SV.Pop(l_module_name);
367 END IF;
368 --
369
370 END PROCESS_ACK_RECEIPT;
371
372 /*
373 ** This procedure is used to update DENIED_PARTY_MATCH flag in WSH_ITM_RESPONSE_LINES
374 ** Table if a DENIED_PARTY_MATCH tag is found in the response.
375 **
376 ** p_response_line_id - RESPONSE LINE ID FOR WHICH DENIED PARTY HAS BEEN
377 ** FOUND IN THE RECEIVED RESPONSE.
378 **
379 */
380 PROCEDURE UPDATE_DENIED_PARTY_MATCH(p_response_line_id IN NUMBER) IS
381 --
382 l_debug_on BOOLEAN;
383 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DENIED_PARTY_MATCH';
384 --
385 BEGIN
386 --
387 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
388 --
389 IF ( l_debug_on IS NULL )
390 THEN
391 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
392 END IF;
393 --
394 IF l_debug_on THEN
395 WSH_DEBUG_SV.Push(l_module_name);
396 WSH_DEBUG_SV.Log(l_module_name, 'p_response_line_id', p_response_line_id );
397 END IF;
398 --
399
400 --UPDATE WSH_ITM_RESPONSE_LINES SET DENIED_PARTY_MATCH = 'y'
401 UPDATE WSH_ITM_RESPONSE_LINES
402 SET DENIED_PARTY_FLAG = 'Y'
403 WHERE RESPONSE_LINE_ID = p_response_line_id;
404 --
405 IF l_debug_on THEN
406 WSH_DEBUG_SV.Pop(l_module_name);
407 END IF;
408 --
409 END UPDATE_DENIED_PARTY_MATCH;
410
411 /*
412 ** Create a new response header record in case x_create_resp_hdr = 1
413 ** else, uses the response header ID returned to create the lines
414 ** record for the header.
415 **
416 ** p_request_control_id - REQUEST CONTROL ID
417 ** x_response_header_id - RESPONSE HEADER ID
418 ** x_create_resp_hdr 1 - HEADER EXISTING (NO NEED TO CREATE)
419 ** NULL - NEW HEADER (INSERT DATA TO HDR TABLE)
420 **
421 */
422 PROCEDURE GET_RESPONSE_HDR(p_request_control_id IN NUMBER,
423 x_response_header_id OUT NOCOPY NUMBER,
424 x_create_resp_hdr OUT NOCOPY NUMBER) IS
425 --
426 l_debug_on BOOLEAN;
427 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_RESPONSE_HDR';
428 --
429 BEGIN
430 --
431 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
432 --
433 IF ( l_debug_on IS NULL )
434 THEN
435 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
436 END IF;
437 --
438 IF l_debug_on THEN
439 WSH_DEBUG_SV.Push(l_module_name);
440 WSH_DEBUG_SV.Log(l_module_name, 'p_request_control_id', p_request_control_id );
441 END IF;
442 --
443 BEGIN
444
445 SELECT RESPONSE_HEADER_ID INTO x_response_header_id
446 FROM WSH_ITM_REQUEST_CONTROL
447 WHERE REQUEST_CONTROL_ID = p_request_control_id
448 AND ROWNUM < 2;
449 EXCEPTION
450 WHEN NO_DATA_FOUND THEN
451 x_response_header_id := NULL;
452 END;
453
454 IF x_response_header_id IS NULL THEN
455 SELECT WSH_ITM_RESPONSE_HEADERS_S.NEXTVAL INTO
456 x_response_header_id FROM DUAL;
457 x_create_resp_hdr := 1;
458 ELSE
459 x_create_resp_hdr := NULL;
460 END IF;
461 --
462 IF l_debug_on THEN
463 WSH_DEBUG_SV.Pop(l_module_name);
464 END IF;
465 --
466 END GET_RESPONSE_HDR;
467
468 /*
469 ** As message Map calls with message ID a new procedure which first
470 ** parses the message id and then calls WSH_ITM_POST_PROCESS_HANDLER.CHECK_PENDING_CALL_API
471 ** for further processing.
472 **
473 ** p_message_ID - MESSAGE ID received.
474 **
475 */
476 PROCEDURE MESSAGE_POSTPROCESS(p_message_ID IN VARCHAR2) IS
477
478 l_reqclrt_ids VARCHAR2(2000); --Bug 9226895
479 l_service_type_code VARCHAR2(100);
480 l_vendor_id VARCHAR2(5);
481 l_reqclrt_id NUMBER;
482 l_request_set_id NUMBER;
483 l_application_id NUMBER;
484 --
485 l_debug_on BOOLEAN;
486 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MESSAGE_POSTPROCESS1';
487 --
488 BEGIN
489 --
490 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
491 --
492 IF ( l_debug_on IS NULL )
493 THEN
494 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
495 END IF;
496 --
497 IF l_debug_on THEN
498 WSH_DEBUG_SV.Push(l_module_name);
499 WSH_DEBUG_SV.Log(l_module_name, 'p_message_ID', p_message_ID );
500 END IF;
501 --
502
503
504 -- get request control IDs
505 TOKENIZE_MESSAGEID(p_message_ID,
506 l_reqclrt_ids,
507 l_service_type_code,
508 l_vendor_id);
509 LOOP
510 l_reqclrt_id := substr(l_reqclrt_ids, 0, (instr(l_reqclrt_ids, '-')-1));
511 IF (l_reqclrt_id IS NULL) THEN
512 l_reqclrt_id := l_reqclrt_ids;
513 SELECT REQUEST_SET_ID,
514 APPLICATION_ID
515 INTO l_request_set_id,
516 l_application_id
517 FROM WSH_ITM_REQUEST_CONTROL
518 WHERE REQUEST_CONTROL_ID = l_reqclrt_id;
519
520 WSH_ITM_POST_PROCESS_HANDLER.CHECK_PENDING_CALL_API(l_reqclrt_id,
521 l_request_set_id,
522 l_application_id,
523 'ITM'); --Bug 9226895 replaced ECX with ITM
524 EXIT;
525 ELSE
526 SELECT REQUEST_SET_ID,
527 APPLICATION_ID
528 INTO l_request_set_id,
529 l_application_id
530 FROM WSH_ITM_REQUEST_CONTROL
531 WHERE REQUEST_CONTROL_ID = l_reqclrt_id;
532
533 WSH_ITM_POST_PROCESS_HANDLER.CHECK_PENDING_CALL_API(l_reqclrt_id,
534 l_request_set_id,
535 l_application_id,
536 'ITM'); --Bug 9226895 replaced ECX with ITM
537 END IF;
538 l_reqclrt_ids := substr(l_reqclrt_ids, (instr(l_reqclrt_ids, '-')+1));
539
540 END LOOP;
541 --
542 IF l_debug_on THEN
543 WSH_DEBUG_SV.Pop(l_module_name);
544 END IF;
545 --
546
547 Close_Debug;
548
549 END MESSAGE_POSTPROCESS;
550
551 /*
552 ** As message Map calls with requestCtrl ID a new procedure which first
553 ** gets the requestSetID and application id and then calls
554 ** WSH_ITM_POST_PROCESS_HANDLER.CHECK_PENDING_CALL_API
555 ** for further processing.
556 **
557 ** p_reqCtrlID - REQUEST CONTROL ID received.
558 **
559 */
560 PROCEDURE MESSAGE_POSTPROCESS(p_reqCtrlID NUMBER) IS
561 l_request_set_id NUMBER;
562 l_application_id NUMBER;
563 --
564 l_debug_on BOOLEAN;
565 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MESSAGE_POSTPROCESS';
566 --
567 BEGIN
568 --
569 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
570 --
571 IF ( l_debug_on IS NULL )
572 THEN
573 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
574 END IF;
575 --
576 IF l_debug_on THEN
577 WSH_DEBUG_SV.Push(l_module_name);
578 WSH_DEBUG_SV.Log(l_module_name, 'p_request_control_id', p_reqCtrlID );
579 END IF;
580 --
581
582 SELECT NVL(REQUEST_SET_ID, 0),
583 APPLICATION_ID
584 INTO l_request_set_id,
585 l_application_id
586 FROM WSH_ITM_REQUEST_CONTROL
587 WHERE REQUEST_CONTROL_ID = p_reqCtrlID;
588
589 WSH_ITM_POST_PROCESS_HANDLER.CHECK_PENDING_CALL_API(p_reqCtrlID,
590 l_request_set_id,
591 l_application_id,
592 'ITM');--Bug 9226895 replaced ECX with ITM
593
594 --
595 IF l_debug_on THEN
596 WSH_DEBUG_SV.Pop(l_module_name);
597 END IF;
598 --
599 END MESSAGE_POSTPROCESS;
600 PROCEDURE Initialize_Debug(p_reference_id VARCHAR2) IS
601 l_file_ptr UTL_FILE.File_Type;
602 l_log_directory VARCHAR2(4000);
603 l_log_filename VARCHAR2(100);
604 l_dbg_level VARCHAR2(100);
605 l_dbg_module VARCHAR2(100);
606 l_req_control_ids VARCHAR2(2000); --Bug 9226895
607 --
608 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Initialize_Debug';
609 --
610 BEGIN
611
612 l_req_control_ids := substr(p_reference_id, 0, (instr(p_reference_id, '~')-1)); --Bug 9226895 seperate out Request control ids
613
614 g_req_control_id := substr(l_req_control_ids, 0, (instr(l_req_control_ids, '-')-1)); --Bug 9226895 take out the first Request control id
615
616 IF ( g_req_control_id IS NULL )--Bug 9226895 if only one Request control id is present.
617 THEN
618 --{
619 g_req_control_id := l_req_control_ids;
620 --}
621 END IF;
622
623 IF G_ITM_LOG_LEVEL IS NULL THEN
624 BEGIN
625 select value
626 into G_ITM_LOG_LEVEL
627 from WSH_ITM_PARAMETER_SETUPS_B
628 where parameter_name = 'WSH_ITM_LOG_SEVERITY';
629
630 EXCEPTION
631 when no_data_found then
632 G_ITM_LOG_LEVEL := '0';
633 END;
634 END IF;
635
636 IF G_ITM_LOG_LEVEL = '1' THEN
637 BEGIN
638 IF G_LOG_FILENAME IS NULL OR
639 G_PRV_REQ_CONTROL_ID <> G_REQ_CONTROL_ID
640 THEN
641 --Generating log file for each Request Control even if it processed
642 --within one concurrent process
643 --Close the file if its already open
644 IF G_PRV_REQ_CONTROL_ID <> G_REQ_CONTROL_ID THEN
645 IF utl_file.is_open(g_file_ptr) THEN
646 utl_file.fclose(g_file_ptr);
647 END IF;
648 END IF;
649
650 G_PRV_REQ_CONTROL_ID := G_REQ_CONTROL_ID;
651
652 --Get profile option value from SITE level for debug log directory
653 l_log_directory := FND_PROFILE.VALUE_SPECIFIC( 'WSH_DEBUG_LOG_DIRECTORY', -1, -1, -1, -1, -1 );
654 l_dbg_level := FND_PROFILE.VALUE_SPECIFIC( 'WSH_DEBUG_LEVEL', -1, -1, -1, -1, -1 );
655 l_dbg_module := FND_PROFILE.VALUE_SPECIFIC( 'WSH_DEBUG_MODULE', -1, -1, -1, -1, -1 );
656
657 FND_PROFILE.PUT('WSH_DEBUG_LEVEL', l_dbg_level);
658 FND_PROFILE.PUT('WSH_DEBUG_MODULE',l_dbg_module);
659
660 --G_LOG_FILENAME := 'wshitm_async_' || g_req_control_id || '.dbg';
661
662 G_LOG_FILENAME := 'wshitm_' || g_req_control_id || '_' || to_char(sysdate,'MMYYHH24MISS') ||'.dbg';
663
664 l_file_ptr := UTL_FILE.Fopen(l_log_directory, G_LOG_FILENAME, 'a');
665
666 --Debug messages will be generated in a log file instead of
667 --being printed in concurrent request log file
668 WSH_DEBUG_SV.G_ITM_ASYN_PROC := TRUE;
669
670 WSH_DEBUG_INTERFACE.Start_Debugger(
671 p_dir_name => l_log_directory,
672 p_file_name => G_LOG_FILENAME,
673 p_file_handle => l_file_ptr );
674 OE_DEBUG_PUB.Start_ONT_Debugger(
675 p_directory => l_log_directory,
676 p_filename => G_LOG_FILENAME,
677 p_file_handle => l_file_ptr );
678
679 G_FILE_PTR := l_file_ptr;
680 G_DEBUG_PROCESS := TRUE;
681
682 --Deleting pl/sql table g_CallStack so that debug messages
683 --will be indented properly if more than one ITM request
684 --controls are processed by same concurrent process.
685 WSH_DEBUG_SV.g_CallStack.DELETE;
686 --Calling Set_Debug_Count to reinitialize debug global
687 --variables in debug package WSH_DEBUG_SV
688 WSH_DEBUG_SV.Set_Debug_Count;
689
690 WSH_DEBUG_SV.Push(l_module_name);
691 WSH_DEBUG_SV.Log(l_module_name, 'Concurrent Request Id', fnd_global.conc_request_id );
692 WSH_DEBUG_SV.Log(l_module_name, 'User Name', fnd_global.user_name );
693 WSH_DEBUG_SV.Log(l_module_name, 'Responsibility Id', fnd_global.resp_id );
694 WSH_DEBUG_SV.Log(l_module_name, 'Responsibility Application Id', fnd_global.resp_appl_id );
695 WSH_DEBUG_SV.Pop(l_module_name);
696 ELSE
697 G_DEBUG_PROCESS := FALSE;
698 END IF;
699
700 -- Added Exception handler so that we will proceed further
701 -- even if file handling raises any exception.
702 EXCEPTION
703 WHEN OTHERS THEN
704 G_DEBUG_PROCESS := FALSE;
705 END;
706 END IF;
707 END Initialize_Debug;
708
709 PROCEDURE Close_Debug IS
710 --
711 l_debug_on BOOLEAN;
712 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Close_Debug';
713 --
714 BEGIN
715 --
716 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
717 --
718 IF ( l_debug_on IS NULL )
719 THEN
720 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
721 END IF;
722 --
723 IF l_debug_on THEN
724 WSH_DEBUG_SV.Push(l_module_name);
725 WSH_DEBUG_SV.Logmsg(l_module_name, 'Closing debug log file' );
726 WSH_DEBUG_SV.Pop(l_module_name);
727
728 --If ITM Log Severity is set to DEBUG then disable Shipping and OM Debugging
729 WSH_DEBUG_INTERFACE.stop_debugger;
730 OE_DEBUG_PUB.STOP_ONT_DEBUGGER;
731 --
732 END IF;
733 --
734
735 IF ( G_DEBUG_PROCESS ) THEN
736 IF utl_file.is_open(g_file_ptr) THEN
737 utl_file.fclose(g_file_ptr);
738 END IF;
739 END IF;
740
741 -- Added Exception handler so that we will proceed further
742 -- even if file handling raises any exception.
743 EXCEPTION
744 WHEN OTHERS THEN
745 null;
746 END Close_Debug;
747 --Added the procedure Get_Valid_Reference_Id for the bug 9544400
748 -- Inputs: reference_id VARCHAR2
749 -- request_control_id NUMBER
750 -- The API removes the given request_control_id from the string 'reference_id'.
751 PROCEDURE Get_Valid_Reference_Id
752 ( p_reference_id IN OUT NOCOPY VARCHAR2 ,
753 p_request_ctrl_id IN NUMBER)IS
754
755 l_reference_id VARCHAR2(2000);
756 --
757 l_debug_on BOOLEAN;
758 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Valid_Reference_Id';
759 --
760 BEGIN
761 --
762 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
763 --
764 IF ( l_debug_on IS NULL ) THEN
765 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
766 END IF;
767 --
768 IF l_debug_on THEN
769 WSH_DEBUG_SV.push(l_module_name);
770 WSH_DEBUG_SV.Log(l_module_name, 'p_reference_id',p_reference_id);
771 WSH_DEBUG_SV.Log(l_module_name, 'Request_control_id that will be removed from p_reference_id',p_request_ctrl_id);
772 END IF;
773
774 --
775 SELECT Decode(InStr(p_reference_id, '-' ||p_request_ctrl_id),0, REPLACE(p_reference_id, p_request_ctrl_id||'-', ''),REPLACE(p_reference_id, '-'||p_request_ctrl_id, '') )
776 INTO
777 l_reference_id
778 FROM dual;
779
780 IF l_debug_on THEN
781 WSH_DEBUG_SV.Log(l_module_name, 'New p_reference_id',l_reference_id);
782 WSH_DEBUG_SV.pop(l_module_name);
783 END IF;
784
785 p_reference_id := l_reference_id;
786 /*No exception block is being added, since The caller(ECX)
787 would take care and stop the process if any exception is raised here */
788 END Get_Valid_Reference_Id;
789
790
791 END WSH_ITM_ASYN_MSGMAP_ASSISTANT;