1 PACKAGE BODY WSH_ITM_RESUBMIT as
2 /* $Header: WSHITRSB.pls 120.1 2005/10/23 23:51:30 bradha noship $ */
3
4 --
5 -- Package: WSH_ITM_RESUBMIT
6 --
7 -- Purpose: To Resubmit requests for Adapter Processing.
8 --
9 --
10 -- PRIVATE FUNCTIONS
11 --
12
13 --
14 -- Name
15 -- Handle exception
16 -- Purpose
17 -- Closes all the exception logged for that delivery
18 -- and log another exception skip of low severity to inform the shipper
19 -- that delivery has skipped screening
20 -- Arguments
21 -- P_REQUEST_CONTROL_ID Value for Request control ID
22 --
23 -- Returns [ for functions ]
24 --
25 -- Notes
26 --
27 PROCEDURE Handle_Exception
28 (
29 p_request_control_id IN NUMBER
30 )
31 IS
32
33 l_exception_name VARCHAR2(30);
34 l_exception_id NUMBER;
35 l_status VARCHAR2(10);
36 l_api_version NUMBER := 1.0;
37 l_Return_status VARCHAR2(20);
38 l_msg_count NUMBER;
39 l_msg_data VARCHAR2(200);
40 x_exception_id NUMBER;
41 l_old_status VARCHAR2(30);
42 l_new_status VARCHAR2(30);
43 l_default_status VARCHAR2(100);
44 l_validation_level NUMBER default FND_API.G_VALID_LEVEL_FULL;
45 l_varchar_status_tab WSH_UTIL_CORE.Column_Tab_Type;
46 l_num_delivery_id_tab WSH_UTIL_CORE.Id_Tab_Type;
47 l_logged_at_location_id number ;
48 l_exception_location_id number;
49 L_EXCEPTION_MESSAGE VARCHAR2(200);
50 l_delivery_id number;
51 l_delivery_name varchar2(20);
52 l_triggering_point varchar2(20);
53 l_request_control_id number;
54 l_flag boolean;
55
56 cursor triggering_point(c_request_control_id number) is
57 SELECT DISTINCT
58 TRIGGERING_POINT
59 FROM WSH_ITM_REQUEST_CONTROL
60 WHERE request_control_id= c_request_control_id;
61
62 cursor delivery_resubmit(c_request_control_id number) is
63 SELECT
64 exception_id,status,logged_at_location_id,exception_location_id,DELIVERY_ID,delivery_name
65 FROM
66 wsh_exceptions wex,
67 wsh_itm_request_control wrc
68 WHERE
69 wrc.original_system_reference =wex.delivery_id
70 AND wex.exception_name LIKE 'WSH_EXPORT_COMPL_FAILED' AND WEX.STATUS='OPEN'
71 AND WRC.REQUEST_CONTROL_ID = C_REQUEST_CONTROL_ID
72 ORDER BY wex.creation_date;
73
74 BEGIN
75
76 WSH_UTIL_CORE.println('Start of Handle exception for the request control id'||p_request_control_id);
77 WSH_UTIL_CORE.println('Closes all failed exception for that request control id and logs another submit exception for it');
78
79 l_flag :=FALSE;
80
81 OPEN DELIVERY_RESUBMIT(p_request_control_id);
82 LOOP
83 FETCH delivery_RESUBMIT INTO
84 l_exception_id,l_status,l_logged_at_location_id,l_exception_locatiON_ID,L_DELIVERY_ID,L_DELIVERY_NAME;
85
86
87 l_return_status := NULL;
88 l_msg_count := NULL;
89 l_msg_data := NULL;
90 l_new_status := 'CLOSED';
91
92 EXIT WHEN DELIVERY_RESUBMIT%NOTFOUND;
93
94
95 WSH_UTIL_CORE.println('Exception id :'||l_exception_id);
96 WSH_UTIL_CORE.println('The status :'||l_status);
97 WSH_UTIL_CORE.println('Location id :'||l_logged_at_location_id);
98 WSH_UTIL_CORE.println('Exception location id :'||l_exception_location_id);
99 WSH_UTIL_CORE.println('Delivery id :'||l_delivery_id);
100 WSH_UTIL_CORE.println('Delivery name :'||l_delivery_name);
101
102 l_msg_data := NULL;
103 l_old_status := 'OPEN';
104 l_new_status := 'CLOSED';
105 l_default_status := 'F';
106 WSH_XC_UTIL.change_status ( p_api_version => l_api_version,
107 p_init_msg_list => FND_API.g_false,
108 p_commit => FND_API.g_false,
109 p_validation_level => l_validation_level,
110 x_return_status => l_return_status,
111 x_msg_count => l_msg_count,
112 x_msg_data => l_msg_data,
113 p_exception_id => l_exception_id,
114 p_old_status => l_old_status,
115 p_set_default_status => l_default_status,
116 x_new_status => l_new_status
117 );
118
119 END LOOP;
120 CLOSE delivery_RESUBMIT;
121
122
123 OPEN triggering_point(p_request_control_id);
124
125 FETCH triggering_point into l_triggering_point;
126
127 CLOSE triggering_point;
128
129 WSH_UTIL_CORE.println('Triggering point is'|| l_triggering_point);
130 IF l_triggering_point= 'PICK_RELEASE' THEN
131 L_EXCEPTION_NAME := 'WSH_PR_SUB_EXPORT_COMPL';
132 ELSE
133 L_EXCEPTION_NAME := 'WSH_SC_SUB_EXPORT_COMPL';
134 END IF;
135
136
137 l_return_status := NULL;
138 l_msg_count := NULL;
139 l_msg_data := NULL;
140 l_exception_id := NULL;
141
142 l_exception_message := 'Delivery has been submitted for export screening';
143 WSH_XC_UTIL.log_exception(
144 p_api_version => l_api_version,
145 p_init_msg_list => FND_API.g_false,
146 p_commit => FND_API.g_false,
147 p_validation_level => l_validation_level,
148 x_return_status => l_return_status,
149 x_msg_count => l_msg_count,
150 x_msg_data => l_msg_data,
151 x_exception_id => l_exception_id,
152 p_exception_location_id => l_exception_location_id,
153 p_logged_at_location_id => l_logged_at_location_id,
154 p_logging_entity => 'SHIPPER',
155 p_logging_entity_id => FND_GLOBAL.USER_ID,
156 p_exception_name => l_exception_name,
157 p_message => l_exception_message,
158 p_delivery_id => l_delivery_id,
159 p_delivery_name => l_delivery_name
160 );
161
162 WSH_UTIL_CORE.println('End of Call Shipping');
163
164 /* UPDATE wsh_itm_request_control
165 SET process_flag = 0,
166 response_header_id = NULL
167 WHERE request_control_id = p_request_control_id;
168 */
169 COMMIT;
170
171 EXCEPTION
172
173 WHEN OTHERS THEN
174 ROLLBACK TO WSH_ITM_RESUBMIT;
175 WSH_UTIL_CORE.println(' Error while hadling the exceptions');
176
177 END Handle_Exception;
178
179
180 --
181 -- Package: WSH_ITM_RESUBMIT
182 --
183 -- Purpose: To Resubmit requests for Adapter Processing.
184 --
185 --
186 -- PRIVATE FUNCTIONS
187 --
188
189 --
190 -- Name
191 -- Resubmit_Requests
192 -- Purpose
193 -- This procedure selects all the eligible records from the tables
194 -- WSH_ITM_REQUEST_CONTROL, WSH_ITM_RESPONSE_HEADERS
195 -- and WSH_ITM_RESPONSE_LINES for Resubmit.
196 -- For every record, it first updates the process_flag in the table
197 -- WSH_ITM_REQUEST_CONTROL to 0, meaning RESUBMIT
198 --
199 -- Arguments
200 -- ERRBUF Required by Concurrent Processing.
201 -- RETCODE Required by Concurrent Processing.
202 -- P_APPLICATION_ID Application ID
203 -- P_RESUBMIT_TYPE Denotes SYSTEM/DATA
204 -- P_ERROR_TYPE Values for Error Type
205 -- P_ERROR_CODE Values for Error Code.
206 -- P_PREFERENCE_ID Reference Number for Integrating Application
207 -- Ex: Order Number for OM.
208 -- P_REFERENCE_LINE_ID Reference Line for Integrating Application
209 -- Ex : Order Line Number for OM.
210 -- P_VENDOR_ID Value for Vendor ID.
211 -- P_PARTY_TYPE Value for Party Type
212 -- P_PARTY_ID Value for Party ID
213 --
214 -- Returns [ for functions ]
215 --
216 -- Notes
217 --
218
219
220
221
222 PROCEDURE ITM_Resubmit_Requests
223 (
224 errbuf OUT NOCOPY VARCHAR2,
225 retcode OUT NOCOPY NUMBER,
226 p_application_id IN NUMBER,
227 p_resubmit_type IN VARCHAR2 ,
228 p_dummy IN NUMBER default NULL,
229 p_reference_id IN NUMBER ,
230 p_error_type IN VARCHAR2 ,
231 p_error_code IN VARCHAR2 ,
232 p_vendor_id IN NUMBER ,
233 p_reference_line_id IN NUMBER ,
234 p_party_type IN VARCHAR2 ,
235 p_party_id IN NUMBER
236 )
237
238 IS
239
240 l_process_flag NUMBER;
241 l_error_code NUMBER;
242 l_temp BOOLEAN;
243 l_error_text VARCHAR2(2000);
244 l_log_level NUMBER;
245 l_completion_status VARCHAR2(30);
246 l_SrvTab WSH_ITM_RESPONSE_PKG.SrvTabTyp;
247 l_return_status VARCHAR2(1);
248 l_interpreted_value VARCHAR2(30);
249 l_request_control_id NUMBER;
250 Response_Analyser_Failed EXCEPTION;
251
252 l_sql_string VARCHAR2(4000);
253 l_CursorID INTEGER;
254 l_ignore INTEGER;
255 i NUMBER;
256 l_sub_str VARCHAR2(4000);
257
258 CURSOR Get_Request_Control_ship IS
259 SELECT DISTINCT
260 wrc.request_control_id
261 FROM
262 WSH_ITM_REQUEST_CONTROL wrc,
263 WSH_ITM_RESPONSE_HEADERS wrh
264 WHERE
265 wrc.response_header_id = wrh.response_header_id
266 AND nvl(wrh.vendor_id,-99) = nvl(p_vendor_id, nvl(wrh.vendor_id,-99))
267 AND nvl(wrh.error_type,-99) = nvl(p_error_type, nvl(wrh.error_type,-99))
268 AND nvl(wrh.error_code,-99) = nvl(p_error_code, nvl(wrh.error_code,-99))
269 AND nvl( wrc.original_system_reference,-99) = nvl(p_reference_id,
270 nvl(wrc.original_system_reference,-99))
271 AND wrc.process_flag = 2
272 AND wrc.application_id =665;
273
274
275 BEGIN
276
277 l_completion_status := 'NORMAL';
278 l_log_level := FND_PROFILE.VALUE('ONT_DEBUG_LEVEL');
279
280 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
281
282 IF l_log_level IS NOT NULL THEN
283 WSH_UTIL_CORE.Set_Log_Level(l_log_level);
284 END IF;
285
286 ------------------------------------------
287 -- Print the values of all the parameters.
288 ------------------------------------------
289
290 WSH_UTIL_CORE.println('Application Id : ' || p_application_id);
291 WSH_UTIL_CORE.println('Resubmit Type : ' || p_resubmit_type);
292 WSH_UTIL_CORE.println('Reference No. : ' || p_reference_id);
293 WSH_UTIL_CORE.println('Reference Line No. : ' || p_reference_line_id);
294 WSH_UTIL_CORE.println('Error Type : ' || p_error_type);
295 WSH_UTIL_CORE.println('Error Code : ' || p_error_code);
296 WSH_UTIL_CORE.println('Vendor Id : ' || p_vendor_id);
297 WSH_UTIL_CORE.println('Party Type : ' || p_party_type);
298 WSH_UTIL_CORE.println('Party id : ' || p_party_id);
299
300 WSH_UTIL_CORE.println('*** Inside PROCEDURE ITM_Resubmit_Requests ***');
301
302 IF p_application_id = 660 THEN --{
303
304 l_sql_string := 'SELECT DISTINCT wrc.request_control_id '||
305 'FROM WSH_ITM_REQUEST_CONTROL wrc '||
306 ', WSH_ITM_RESPONSE_HEADERS wrh '||
307 ', OE_ORDER_HEADERS_ALL oh ';
308
309 IF (p_party_type IS NOT NULL) OR (p_party_id IS NOT NULL) THEN
310 l_sql_string := l_sql_string || ', WSH_ITM_PARTIES wp ';
311 END IF;
312 l_sql_string := l_sql_string || ' WHERE wrc.response_header_id = wrh.response_header_id '||
313 ' AND wrc.original_system_reference = oh.header_id '||
314 ' AND oh.flow_status_code <> ''CLOSED'' '||
315 ' AND wrc.process_flag = 2 '||
316 ' AND wrc.application_id = 660 ';
317 IF p_vendor_id IS NOT NULL THEN
318 l_sql_string := l_sql_string || ' AND wrh.vendor_id = :x_vendor_id ';
319 END IF;
320 IF p_error_type IS NOT NULL THEN
321 l_sql_string := l_sql_string || ' AND wrh.error_type = :x_error_type ';
322 END IF;
323 IF p_error_code IS NOT NULL THEN
324 l_sql_string := l_sql_string || ' AND wrh.error_code = :x_error_code ';
325 END IF;
326 IF p_reference_id IS NOT NULL THEN
327 l_sql_string := l_sql_string || ' AND wrc.original_system_reference = :x_reference_id ';
328 END IF;
329 IF p_reference_line_id IS NOT NULL THEN
330 l_sql_string := l_sql_string || ' AND wrc.original_system_line_reference = :x_reference_line_id ';
331 END IF;
332 IF (p_party_type IS NOT NULL) OR (p_party_id IS NOT NULL) THEN
333 l_sql_string := l_sql_string || ' AND wrc.request_control_id = wp.request_control_id ';
334 IF p_party_type IS NOT NULL THEN
335 l_sql_string := l_sql_string || ' AND wp.party_type = :x_party_type ';
336 END IF;
337 IF p_party_id IS NOT NULL THEN
338 l_sql_string := l_sql_string || ' AND wp.source_org_id = :x_party_id ';
339 END IF;
340 END IF;
341 l_sql_string := l_sql_string || ' UNION '||
342 ' SELECT DISTINCT wrc.request_control_id '||
343 ' FROM WSH_ITM_REQUEST_CONTROL wrc '||
344 ' , WSH_ITM_RESPONSE_HEADERS wrh '||
345 ' , WSH_ITM_RESPONSE_LINES wrl '||
346 ' , OE_ORDER_LINES_ALL ol ';
347 IF (p_party_type IS NOT NULL) OR (p_party_id IS NOT NULL) THEN
348 l_sql_string := l_sql_string || ', WSH_ITM_PARTIES wp ';
349 END IF;
350 l_sql_string := l_sql_string || ' WHERE wrc.response_header_id = wrh.response_header_id '||
351 ' AND wrh.response_header_id = wrl.response_header_id '||
352 ' AND wrc.original_system_line_reference = ol.line_id '||
353 ' AND ol.flow_status_code <> ''CLOSED'' '||
354 ' AND wrc.process_flag = 2 '||
355 ' AND wrc.application_id = 660 ';
356 IF p_vendor_id IS NOT NULL THEN
357 l_sql_string := l_sql_string || ' AND wrh.vendor_id = :x_vendor_id ';
358 END IF;
359 IF p_error_type IS NOT NULL THEN
360 l_sql_string := l_sql_string || ' AND wrl.error_type = :x_error_type ';
361 END IF;
362 IF p_error_code IS NOT NULL THEN
363 l_sql_string := l_sql_string || ' AND wrl.error_code = :x_error_code ';
364 END IF;
365 IF p_reference_id IS NOT NULL THEN
366 l_sql_string := l_sql_string || ' AND wrc.original_system_reference = :x_reference_id ';
367 END IF;
368 IF p_reference_line_id IS NOT NULL THEN
369 l_sql_string := l_sql_string || ' AND wrc.original_system_line_reference = :x_reference_line_id ';
370 END IF;
371 IF (p_party_type IS NOT NULL) OR (p_party_id IS NOT NULL) THEN
372 l_sql_string := l_sql_string || ' AND wrc.request_control_id = wp.request_control_id ';
373 IF p_party_type IS NOT NULL THEN
374 l_sql_string := l_sql_string || ' AND wp.party_type = :x_party_type ';
375 END IF;
376 IF p_party_id IS NOT NULL THEN
377 l_sql_string := l_sql_string || ' AND wp.source_org_id = :x_party_id ';
378 END IF;
379 END IF;
380
381 i := 1;
382 LOOP
383 IF i > length(l_sql_string) THEN
384 EXIT;
385 END IF;
386 l_sub_str := SUBSTR(l_sql_string, i , 80);
387 WSH_UTIL_CORE.println(l_sub_str);
388 i := i + 80;
389 END LOOP;
390
391 l_CursorID := DBMS_SQL.Open_Cursor;
392
393 DBMS_SQL.Parse(l_CursorID, l_sql_string, DBMS_SQL.v7 );
394
395 DBMS_SQL.Define_Column(l_CursorID, 1, l_request_control_id);
396
397 IF p_party_type IS NOT NULL THEN
398 DBMS_SQL.BIND_VARIABLE(l_CursorID,':x_party_type', p_party_type);
399 END IF;
400 IF p_party_id IS NOT NULL THEN
401 DBMS_SQL.BIND_VARIABLE(l_CursorID,':x_party_id', p_party_id);
402 END IF;
403 IF p_vendor_id IS NOT NULL THEN
404 DBMS_SQL.BIND_VARIABLE(l_CursorID,':x_vendor_id', p_vendor_id);
405 END IF;
406 IF p_error_type IS NOT NULL THEN
407 DBMS_SQL.BIND_VARIABLE(l_CursorID,':x_error_type', p_error_type);
408 END IF;
409 IF p_error_code IS NOT NULL THEN
410 DBMS_SQL.BIND_VARIABLE(l_CursorID,':x_error_code', p_error_code);
411 END IF;
412 IF p_reference_id IS NOT NULL THEN
413 DBMS_SQL.BIND_VARIABLE(l_CursorID,':x_reference_id', p_reference_id);
414 END IF;
415 IF p_reference_line_id IS NOT NULL THEN
416 DBMS_SQL.BIND_VARIABLE(l_CursorID,':x_reference_line_id', p_reference_line_id);
417 END IF;
418
419 l_ignore := DBMS_SQL.Execute(l_CursorID);
420 --}
421 ELSE
422 OPEN Get_Request_Control_ship;
423 END IF;
424
425 LOOP --{
426
427 --Issue a Savepoint
428 SAVEPOINT WSH_ITM_RESUBMIT;
429
430 IF p_application_id = 660 THEN
431 IF DBMS_SQL.Fetch_Rows(l_cursorID) = 0 THEN
432 DBMS_SQL.Close_Cursor(l_cursorID);
433 EXIT;
434 ELSE
435 DBMS_SQL.Column_Value(l_CursorID, 1, l_request_control_id);
436 WSH_UTIL_CORE.println('Request Control Id is' || l_request_control_id);
437 END IF;
438 ELSE
439 fetch Get_Request_Control_ship into l_request_control_id;
440
441 WSH_UTIL_CORE.println('Request Control Id for application shipping is' || l_request_control_id);
442 exit when Get_request_control_ship%notfound;
443 WSH_UTIL_CORE.println('Before the handle exception '||l_request_control_id);
444 Handle_Exception(l_request_control_id);
445 WSH_UTIL_CORE.println('After the handle exception'||l_request_control_id);
446 WSH_UTIL_CORE.println('Request Control Id after handling the exception for shipping is' || l_request_control_id);
447 END IF;
448
449 WSH_UTIL_CORE.println('Request Control Id is' || l_request_control_id);
450
451 WSH_UTIL_CORE.println(' ');
452 WSH_UTIL_CORE.println('Start Processing for Request control : ' || l_request_control_id);
453
454
455 WSH_UTIL_CORE.println('Calling the Response Analyser');
456 WSH_UTIL_CORE.println('Request Control Id :' || l_request_control_id);
457
458 -----------------------------------------------------------
459 -- Calling the Response Analyser to Get the Interpretation
460 -----------------------------------------------------------
461
462
463 WSH_ITM_RESPONSE_PKG.ONT_RESPONSE_ANALYSER
464 (
465 p_request_control_id => l_request_control_id,
466 x_interpreted_value => l_interpreted_value,
467 x_return_status => l_return_status,
468 x_SrvTab => l_SrvTab
469 );
470
471 WSH_UTIL_CORE.println(' ');
472 WSH_UTIL_CORE.println('After Call to Response Analyser');
473 WSH_UTIL_CORE.println('Response from Response Analyser :' || l_interpreted_value);
474
475 IF (p_application_id =660 AND l_interpreted_value ='DATA') then
476 WSH_UTIL_CORE.println('Data errors will not be processes for Order Management');
477 ELSE
478
479 BEGIN
480 IF l_return_Status <> FND_API.G_RET_STS_SUCCESS THEN
481 RAISE Response_Analyser_Failed;
482 END IF;
483
484
485 IF (l_interpreted_value = p_resubmit_type OR p_resubmit_type IS NULL) THEN
486
487 WSH_UTIL_CORE.println('Updating process flag to 0');
488
489 --------------------------------
490 -- Update the Process Flag to 0
491 --------------------------------
492
493 UPDATE wsh_itm_request_control
494 SET process_flag = 0,
495 response_header_id = NULL
496 WHERE request_control_id = l_request_control_id;
497
498
499 WSH_UTIL_CORE.println('Commiting the records..');
500 commit;
501 WSH_UTIL_CORE.println('Finished processing for Request Control :' || l_request_control_id);
502 WSH_UTIL_CORE.println(' ');
503
504 ELSE
505
506 WSH_UTIL_CORE.println('Response from Response Analyser is different from parameter p_resubmit_type');
507 WSH_UTIL_CORE.println('So will not process this record');
508
509 END IF;
510
511 EXCEPTION
512 WHEN Response_Analyser_Failed THEN
513 ROLLBACK TO WSH_ITM_RESUBMIT;
514 WSH_UTIL_CORE.println('Processing Failed in Response Analyser');
515 END;
516 END IF;
517
518 END LOOP; --}
519
520 IF p_application_id=660 THEN
521 IF DBMS_SQL.IS_Open(l_cursorID) THEN
522 DBMS_SQL.Close_Cursor(l_cursorID);
523 END IF;
524 ELSE
525 CLOSE Get_Request_Control_ship;
526 END IF;
527
528
529 WSH_UTIL_CORE.println('End of the Loop for the Cursor ');
530
531 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
532
533
534 EXCEPTION
535
536 WHEN OTHERS THEN
537
538 ROLLBACK TO WSH_ITM_RESUBMIT;
539 IF DBMS_SQL.IS_Open(l_cursorID) THEN
540 DBMS_SQL.Close_Cursor(l_cursorID);
541 END IF;
542 IF Get_Request_Control_ship%ISOPEN THEN
543 CLOSE Get_Request_Control_ship;
544 END IF;
545 l_completion_status := 'ERROR';
546 l_error_code := SQLCODE;
547 l_error_text := SQLERRM;
548 WSH_UTIL_CORE.PrintMsg('In the exception Block');
549 WSH_UTIL_CORE.PrintMsg('Processing failed with an error');
550 WSH_UTIL_CORE.PrintMsg('The unexpected error is '||l_error_code||':' || l_error_text);
551 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
552
553 END ITM_Resubmit_Requests;
554
555 END WSH_ITM_RESUBMIT;
[Home] [Help]