[Home] [Help]
PACKAGE BODY: APPS.WSH_ITM_OVERRIDE
Source
1 PACKAGE BODY WSH_ITM_OVERRIDE as
2 /* $Header: WSHITOVB.pls 120.1.12010000.2 2010/02/09 18:19:42 rvarghes ship $ */
3 --
4 -- Package: WSH_ITM_OVERRIDE
5 --
6 -- Purpose: To Override the errors encountered during the Adapter Processing.
7 --
8 --
9
10 -- Procedure Name
11 -- Call_Custom_API
12 --
13 -- Purpose
14 --
15 -- This procedure accepts the request_control_id, request_set_id,
16 -- Application ID and calls the Application Specific API
17 --
18 -- Parameters
19 --
20 -- p_request_control_id Request Control ID
21 -- p_request_set_id Request Set ID
22 -- p_appl_id Application ID
23 -- x_return_status Return Status
24
25
26 PROCEDURE Call_Custom_API
27 (
28 p_request_control_id IN NUMBER ,
29 p_request_set_id IN NUMBER ,
30 p_appl_id IN NUMBER,
31 x_return_status OUT NOCOPY VARCHAR2
32 )
33 IS
34
35 l_appl_short_name VARCHAR2(50);
36 l_procedure_name VARCHAR2(150);
37 l_error_code NUMBER;
38 l_exists VARCHAR2(2);
39 l_error_text VARCHAR2(2000);
40
41 CURSOR Get_Application_Short_Name(appl_id NUMBER) IS
42 SELECT application_short_name
43 FROM fnd_application_vl
44 WHERE application_id = appl_id;
45
46 CURSOR Get_Process_Flag(req_set_id NUMBER) IS
47 SELECT 'x'
48 FROM WSH_ITM_REQUEST_CONTROL
49 WHERE request_set_id = req_set_id
50 AND process_flag not in (1,3);
51
52
53 BEGIN
54
55 WSH_UTIL_CORE.println(' Inside procedure CALL_CUSTOM_API');
56
57 x_return_status := FND_API.G_RET_STS_SUCCESS;
58
59 ----------------------------------------------------------
60 -- Select the application short name which will be later
61 -- used to call the Application Specific custom procedure.
62 ----------------------------------------------------------
63
64 OPEN Get_Application_Short_name(p_appl_id);
65 FETCH Get_Application_Short_Name INTO l_appl_short_name;
66 CLOSE Get_Application_Short_Name;
67
68 WSH_UTIL_CORE.println('Application Short Name :' || l_appl_short_name);
69 WSH_UTIL_CORE.println(' ');
70
71 -- 9172419
72 -- ONLY 2 parameters for WSH, 3 FOR ONT
73 IF l_appl_short_name = 'ONT' THEN
74 l_procedure_name := ' BEGIN ' || l_appl_short_name || '_ITM_PKG.WSH_ITM_' || l_appl_short_name ||
75 '(:p_request_control_id,:p_request_set_id,:p_status_code); END;';
76 ELSE
77 l_procedure_name := ' BEGIN ' || l_appl_short_name || '_ITM_PKG.WSH_ITM_' || l_appl_short_name ||
78 '(:p_request_control_id,:p_request_set_id); END;';
79 END IF;
80
81 IF p_request_set_id IS NULL THEN
82
83 WSH_UTIL_CORE.println('Request Set Id is Null..');
84 WSH_UTIL_CORE.println('Building the procedure name dynamically');
85
86 ------------------------------------------------------
87 -- The Procedure Name is getting built dynamiclly here.
88 -- The generic syntax is :
89 -- <appl short name>_ITM_PK.WSH_ITM_<appl short name>
90 -- It then gets executed by the EXECUTE IMMEDIATE
91 -- command.
92 ------------------------------------------------------
93 --
94 WSH_UTIL_CORE.println('Calling Application specific API');
95 WSH_UTIL_CORE.println(l_procedure_name);
96 WSH_UTIL_CORE.println(' ');
97
98 -- 9172419
99 IF l_appl_short_name = 'ONT' THEN
100 EXECUTE IMMEDIATE l_procedure_name
101 USING p_request_control_id,
102 p_request_set_id,
103 'OVERRIDE';
104 ELSE
105 EXECUTE IMMEDIATE l_procedure_name
106 USING p_request_control_id,
107 p_request_set_id;
108 END IF;
109
110 WSH_UTIL_CORE.println('Out of the Application Specific Procedure');
111
112 ELSE -- if request_set_id is not null
113
114 WSH_UTIL_CORE.println('Request Set Id is not Null..');
115 WSH_UTIL_CORE.println('Request Set Id is :' || p_request_set_id);
116
117 ----------------------------------------------------
118 -- All other requests with the same request_set_id
119 -- must have process flag as 1 or 3. Only then call
120 -- the Application Specific API.
121 ----------------------------------------------------
122
123 OPEN Get_Process_Flag(p_request_set_id);
124 FETCH Get_Process_Flag INTO l_exists;
125
126 IF Get_Process_Flag%NOTFOUND THEN
127
128 WSH_UTIL_CORE.println('Calling Application specific API');
129 WSH_UTIL_CORE.println(l_procedure_name);
130 WSH_UTIL_CORE.println(' ');
131
132 -- 9172419
133 IF l_appl_short_name = 'ONT' THEN
134 EXECUTE IMMEDIATE l_procedure_name
135 USING p_request_control_id,
136 p_request_set_id,
137 'OVERRIDE';
138 ELSE
139 EXECUTE IMMEDIATE l_procedure_name
140 USING p_request_control_id,
141 p_request_set_id;
142 END IF;
143
144 END IF;
145
146 CLOSE Get_Process_Flag;
147 END IF; -- request_set_id null
148
149
150 EXCEPTION
151
152 WHEN OTHERS THEN
153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
154 l_error_code := SQLCODE;
155 l_error_text := SQLERRM;
156
157 WSH_UTIL_CORE.PrintMsg('Failed in Procedure Call_Custom_API');
158 WSH_UTIL_CORE.PrintMsg('The unexpected error is '||l_error_code||':' || l_error_text);
159
160 END Call_Custom_API;
161
162 -- Procedure Name
163 -- Handle_Exception
164 --
165 -- Purpose
166 --
167 -- Close all the exception logged for a delivery and log another skip exception
168 --
169 -- Parameters
170 --
171 -- p_request_control_id Request Control ID
172
173
174 PROCEDURE Handle_exception (
175 p_request_control_id IN NUMBER
176 )
177 IS
178
179 l_exception_name VARCHAR2(30);
180 l_exception_id NUMBER;
181 l_status VARCHAR2(10);
182
183 l_api_version NUMBER := 1.0;
184 l_Return_status VARCHAR2(20);
185 l_msg_count NUMBER;
186 l_msg_data VARCHAR2(200);
187 x_exception_id NUMBER;
188 l_old_status VARCHAR2(30);
189 l_new_status VARCHAR2(30);
190 l_default_status VARCHAR2(100);
191 l_validation_level NUMBER default FND_API.G_VALID_LEVEL_FULL;
192 l_varchar_status_tab WSH_UTIL_CORE.Column_Tab_Type;
193 l_num_delivery_id_tab WSH_UTIL_CORE.Id_Tab_Type;
194
195 l_logged_at_location_id number ;
196 l_exception_location_id number;
197 L_EXCEPTION_MESSAGE VARCHAR2(200);
198 l_delivery_id number;
199 l_delivery_name varchar2(20);
200
201 CURSOR delivery_skip(l_request_control_id NUMBER) IS
202 SELECT
203 exception_name,
204 exception_id,status,logged_at_location_id,exception_location_id,delivery_id,delivery_name
205 FROM
206 wsh_exceptions wex,
207 wsh_itm_request_control wrc
208 WHERE
209 wrc.ORIGINAL_SYSTEM_REFERENCE=wex.delivery_id
210 AND wrc.request_control_id = l_request_control_id
211 AND (wex.exception_name LIKE 'WSH_PR%'OR wex.exception_name LIKE 'WSH_SC%' OR wex.exception_name LIKE
212 'WSH_EXPORT_COMPL_FAILED') AND WEX.STATUS <> 'CLOSED';
213
214 BEGIN
215
216 l_return_status := NULL;
217 l_msg_count :=NULL;
218 l_msg_data := NULL;
219 l_new_status := 'CLOSED';
220
221 WSH_UTIL_CORE.println('Call to shipping to log close the previous exception and log a new skip exception');
222 WSH_UTIL_CORE.println('Request Control Id is'||p_request_control_id);
223
224
225
226 OPEN DELIVERY_SKIP(p_request_control_id);
227 LOOP
228 FETCH delivery_skip INTO
229 l_exception_name,l_exception_id,l_status,l_logged_at_location_id,l_exception_location_id,l_delivery_id,l_delivery_name;
230
231 WSH_UTIL_CORE.println('The exception name is '||l_exception_name);
232 WSH_UTIL_CORE.println('The exception is is '||l_exception_id);
233
234
235 EXIT WHEN DELIVERY_SKIP%NOTFOUND;
236
237 l_return_status := NULL;
238 l_msg_count := NULL;
239 l_msg_data := NULL;
240 l_old_status := 'OPEN';
241 l_new_status := 'CLOSED';
242 l_default_status := 'F';
243
244
245 WSH_XC_UTIL.change_status ( p_api_version => l_api_version,
246 p_init_msg_list => FND_API.g_false,
247 p_commit => FND_API.g_false,
248 p_validation_level => l_validation_level,
249 x_return_status => l_return_status,
250 x_msg_count => l_msg_count,
251 x_msg_data => l_msg_data,
252 p_exception_id => l_exception_id,
253 p_old_status => l_old_status,
254 p_set_default_status => l_default_status,
255 x_new_status => l_new_status
256 );
257
258
259
260 l_return_status := NULL;
261 l_msg_count := NULL;
262 l_msg_data := NULL;
263 l_exception_id := NULL;
264 l_exception_message := 'Delivery has skipped export screening';
265 l_exception_name := 'WSH_EXPORT_COMPL_SKIP';
266
267
268
269 WSH_XC_UTIL.log_exception(
270 p_api_version => l_api_version,
271 p_init_msg_list => FND_API.g_false,
272 p_commit => FND_API.g_false,
273 p_validation_level => l_validation_level,
274 x_return_status => l_return_status,
275 x_msg_count => l_msg_count,
276 x_msg_data => l_msg_data,
277 x_exception_id => l_exception_id,
278 p_exception_location_id => l_exception_location_id,
279 p_logged_at_location_id => l_logged_at_location_id,
280 p_logging_entity => 'SHIPPER',
281 p_logging_entity_id => FND_GLOBAL.USER_ID,
282 p_exception_name => l_exception_name,
283 p_message => l_exception_message,
284 p_delivery_id => l_delivery_id,
285 p_delivery_name => l_delivery_name
286 );
287
288 WSH_UTIL_CORE.println('End of call to shipping ');
289
290
291 END LOOP;
292 CLOSE delivery_skip;
293
294 END Handle_Exception;
295
296
297
298 -- Name
299 --
300 -- ITM_Launch_Override
301 --
302 -- Purpose
303 -- This procedure selects all the eligible records from the tables
304 -- WSH_ITM_REQUEST_CONTROL, WSH_ITM_RESPONSE_HEADERS
305 -- and WSH_ITM_RESPONSE_LINES for Override.
306 --
307 -- For every record, it first updates the process_flag in the table
308 -- WSH_ITM_REQUEST_CONTROL to 3, meaning OVERRIDE and calls Application
309 -- specific custom procedure.
310 --
311 -- Arguments
312 -- ERRBUF Required by Concurrent Processing.
313 -- RETCODE Required by Concurrent Processing.
314 -- P_APPLICATION_ID Application ID
315 -- P_OVERRIDE_TYPE Denotes SYSTEM/DATA/UNPROCESSED
316 -- P_ERROR_TYPE Values for Error Type
317 -- P_ERROR_CODE Values for Error Code.
318 -- P_REFERENCE_ID Reference Number for Integrating Application
319 -- Ex: Order Number for OM.
320 -- P_REFERENCE_LINE_ID Reference Line for Integrating Application
321 -- Ex : Order Line Number for OM.
322 -- P_VENDOR_ID Value for Vendor ID.
323 -- P_PARTY_TYPE Value for Party Type
324 -- P_PARTY_Id Value for Party ID
325 --
326 -- Returns [ for functions ]
327 --
328 -- Notes
329 --
330
331 PROCEDURE ITM_Launch_Override
332 (
333 errbuf OUT NOCOPY VARCHAR2 ,
334 retcode OUT NOCOPY NUMBER ,
335 p_application_id IN NUMBER ,
336 p_override_type IN VARCHAR2 ,
337 p_reference_id IN NUMBER ,
338 p_dummy IN NUMBER DEFAULT NULL,
339 p_reference_line_id IN NUMBER ,
340 p_error_type IN VARCHAR2 ,
341 p_error_code IN VARCHAR2 ,
342 p_vendor_id IN NUMBER ,
343 p_party_type IN VARCHAR2 ,
344 p_party_id IN NUMBER
345 )
346
347 IS
348
349
350 l_response_header_id NUMBER;
351 l_exists VARCHAR2(1);
352 l_completion_status VARCHAR2(30);
353 l_temp BOOLEAN;
354 l_error_code NUMBER;
355 l_error_text VARCHAR2(2000);
356 l_log_level VARCHAR2(240);
357 l_return_status VARCHAR2(3);
358 l_SrvTab WSH_ITM_RESPONSE_PKG.SrvTabTyp;
359 l_interpreted_value VARCHAR2(30);
360 l_request_control_id NUMBER;
361 l_request_set_id NUMBER;
362 l_flag NUMBER;
363 Response_Analyser_Failed EXCEPTION;
364 Call_Custom_API_Failed EXCEPTION;
365
366 --Added variables for bug 4688380
367 l_sql_string VARCHAR2(5000);
368 l_CursorID INTEGER;
369 l_ignore INTEGER;
370 i NUMBER;
371 l_sub_str VARCHAR2(5000);
372
373 CURSOR Get_Request_Control_ship IS
374 SELECT DISTINCT
375 wrc.request_control_id,
376 wrc.request_set_id
377 FROM
378 WSH_ITM_REQUEST_CONTROL wrc,
379 WSH_ITM_RESPONSE_HEADERS wrh
380
381 WHERE
382 wrc.response_header_id = wrh.response_header_id
383 AND nvl(wrh.vendor_id,-99) = nvl(p_vendor_id, nvl(wrh.vendor_id,-99))
384 AND nvl(wrh.error_type,-99) = nvl(p_error_type, nvl(wrh.error_type,-99))
385 AND nvl(wrh.error_code,-99) = nvl(p_error_code, nvl(wrh.error_code,-99))
386 AND nvl(wrc.original_system_reference,-99) = nvl(p_reference_id,
387 nvl(wrc.original_system_reference,-99))
388 AND wrc.process_flag = 2
389 AND wrc.application_id=665;
390
391 CURSOR Get_Unprocessed_Records_1_ship IS
392 SELECT DISTINCT
393 wrc.request_control_id,
394 wrc.request_set_id
395 FROM
396 WSH_ITM_REQUEST_CONTROL wrc
397 WHERE
398 nvl(wrc.original_system_reference,-99) = nvl(p_reference_id,
399 nvl(wrc.original_system_reference,-99))
400 AND wrc.process_flag in (0,-4);
401
402
403 CURSOR Get_Unprocessed_Records_2_ship IS
404 SELECT DISTINCT
405 wrc.request_control_id,
406 wrc.request_set_id
407 FROM
408 WSH_ITM_REQUEST_CONTROL wrc,
409 WSH_ITM_SERVICE_PREFERENCES wsp,
410 WSH_ITM_VENDOR_SERVICES wvs
411 WHERE
412 nvl(wrc.original_system_reference,-99) = nvl(p_reference_id,
413 nvl(wrc.original_system_reference,-99))
414 AND wrc.application_id = wsp.application_id
415 AND wrc.master_organization_id = wsp.master_organization_id
416 AND wsp.active_flag = 'Y'
417 AND wsp.vendor_service_id = wvs.vendor_service_id
418 AND wrc.service_type_code = wvs.service_type
419 AND wvs.vendor_id = p_vendor_id
420 AND wrc.process_flag in (0,-4);
421
422 BEGIN
423
424
425 l_completion_status := 'NORMAL';
426 l_log_level := FND_PROFILE.VALUE('ONT_DEBUG_LEVEL');
427
428 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
429
430 IF l_log_level IS NOT NULL THEN
431 WSH_UTIL_CORE.Set_Log_Level(l_log_level);
432 END IF;
433
434
435 WSH_UTIL_CORE.println('*** Inside PROCEDURE ITM_Launch_Override ***');
436 WSH_UTIL_CORE.println(' ');
437
438 ------------------------------------------
439 -- Print the values of all the parameters.
440 ------------------------------------------
441
442 WSH_UTIL_CORE.println('Application Id : ' || p_application_id);
443 WSH_UTIL_CORE.println('Override Type : ' || p_override_type);
444 WSH_UTIL_CORE.println('Reference Id : ' || p_reference_id);
445 WSH_UTIL_CORE.println('Reference Line Id : ' || p_reference_line_id);
446 WSH_UTIL_CORE.println('Error Type : ' || p_error_type);
447 WSH_UTIL_CORE.println('Error Code : ' || p_error_code);
448 WSH_UTIL_CORE.println('Vendor Id : ' || p_vendor_id);
449 WSH_UTIL_CORE.println('Party Type : ' || p_party_type);
450 WSH_UTIL_CORE.println('Party ID : ' || p_party_id);
451 WSH_UTIL_CORE.println(' ');
452
453 --Issue a Savepoint
454 SAVEPOINT WSH_ITM_OVERRIDE;
458 WSH_UTIL_CORE.println('Override type is UNPROCESSED');
455 IF p_override_type = 'UNPROCESSED' OR
456 p_override_type IS NULL THEN
457
459
460 If p_vendor_id is NULL THEN
461 WSH_UTIL_CORE.println('Start the loop for Get_Unprocessed_Records_1');
462 if p_application_id=660 then
463 l_sql_string := 'SELECT DISTINCT wrc.request_control_id, wrc.request_set_id ';
464 l_sql_string := l_sql_string || 'FROM WSH_ITM_REQUEST_CONTROL wrc ';
465
466 IF ( p_party_type is NOT NULL or p_party_id is NOT NULL ) THEN
467 l_sql_string := l_sql_string || ' , WSH_ITM_PARTIES wp ';
468 END IF;
469
470 l_sql_string := l_sql_string || 'WHERE wrc.process_flag in ( 0, -4 ) ';
471
472 IF ( p_reference_id is NOT NULL ) THEN
473 l_sql_string := l_sql_string || 'AND wrc.original_system_reference = :x_reference_id ';
474 END IF;
475
476 IF ( p_reference_line_id is NOT NULL ) THEN
477 l_sql_string := l_sql_string || 'AND wrc.original_system_line_reference = :x_reference_line_id ';
478 END IF;
479
480 IF ( p_party_type is NOT NULL or p_party_id is NOT NULL ) THEN
481 --{
482 l_sql_string := l_sql_string || 'AND wrc.request_control_id = wp.request_control_id ';
483 IF ( p_party_type is NOT NULL ) THEN
484 l_sql_string := l_sql_string || 'AND wp.party_type = :x_party_type ';
485 END IF;
486
487 IF ( p_party_id is NOT NULL ) THEN
488 l_sql_string := l_sql_string || 'AND wp.source_org_id = :x_party_id';
489 END IF;
490 --}
491 END IF;
492
493 i := 1;
494 LOOP
495 IF i > length(l_sql_string) THEN
496 EXIT;
497 END IF;
498 l_sub_str := SUBSTR(l_sql_string, i , 80);
499 WSH_UTIL_CORE.println(l_sub_str);
500 i := i + 80;
501 END LOOP;
502
503 l_CursorID := DBMS_SQL.Open_Cursor;
504 DBMS_SQL.Parse(l_CursorID, l_sql_string, DBMS_SQL.v7 );
505 DBMS_SQL.Define_Column(l_CursorID, 1, l_request_control_id);
506 DBMS_SQL.Define_Column(l_CursorID, 2, l_request_set_id);
507
508 IF p_party_type IS NOT NULL THEN
509 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_party_type', p_party_type);
510 END IF;
511
512 IF p_party_id IS NOT NULL THEN
513 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_party_id', p_party_id);
514 END IF;
515
516 IF p_reference_id IS NOT NULL THEN
517 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_reference_id', p_reference_id);
518 END IF;
519
520 IF p_reference_line_id IS NOT NULL THEN
521 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_reference_line_id', p_reference_line_id);
522 END IF;
523
524 l_ignore := DBMS_SQL.Execute(l_CursorID);
525 --}
526 else
527 open Get_Unprocessed_Records_1_ship;
528 WSH_UTIL_CORE.println('Start The processing for Shipping Records :');
529 if p_override_type= null and p_reference_id is not null then
530 WSH_UTIL_CORE.println('The delivery contains errored request ' );
531 l_flag := 1;
532 WSH_UTIL_CORE.println('Start Processing for Request Control :' || l_flag);
533 end if;
534 end if;
535 ELSE
536 WSH_UTIL_CORE.println('Start the loop for Get_Unprocessed_Records_2');
537 if p_application_id=660 then
538 l_sql_string := 'SELECT DISTINCT wrc.request_control_id, wrc.request_set_id ';
539 l_sql_string := l_sql_string || 'FROM WSH_ITM_REQUEST_CONTROL wrc, ';
540 l_sql_string := l_sql_string || ' WSH_ITM_SERVICE_PREFERENCES wsp, ';
541 l_sql_string := l_sql_string || ' WSH_ITM_VENDOR_SERVICES wvs ';
542
543 IF ( p_party_type is NOT NULL or p_party_id is NOT NULL ) THEN
544 l_sql_string := l_sql_string || ' , WSH_ITM_PARTIES wp ';
545 END IF;
546
547 l_sql_string := l_sql_string || 'WHERE wrc.process_flag in ( 0, -4 ) ';
548 l_sql_string := l_sql_string || 'AND wrc.service_type_code = wvs.service_type ';
549 l_sql_string := l_sql_string || 'AND wsp.vendor_service_id = wvs.vendor_service_id ';
550 l_sql_string := l_sql_string || 'AND wsp.active_flag = ''Y'' ';
551 l_sql_string := l_sql_string || 'AND wrc.master_organization_id = wsp.master_organization_id ';
552 l_sql_string := l_sql_string || 'AND wrc.application_id = wsp.application_id ';
553 l_sql_string := l_sql_string || 'AND wvs.vendor_id = :x_vendor_id ';
554
555 IF ( p_reference_id is NOT NULL ) THEN
559 IF ( p_reference_line_id is NOT NULL ) THEN
556 l_sql_string := l_sql_string || 'AND wrc.original_system_reference = :x_reference_id ';
557 END IF;
558
560 l_sql_string := l_sql_string || 'AND wrc.original_system_line_reference = :x_reference_line_id ';
561 END IF;
562
563 IF ( p_party_type is NOT NULL or p_party_id is NOT NULL ) THEN
564 --{
565 l_sql_string := l_sql_string || 'AND wrc.request_control_id = wp.request_control_id ';
566 IF ( p_party_type is NOT NULL ) THEN
567 l_sql_string := l_sql_string || 'AND wp.party_type = :x_party_type ';
568 END IF;
569
570 IF ( p_party_id is NOT NULL ) THEN
571 l_sql_string := l_sql_string || 'AND wp.source_org_id = :x_party_id';
572 END IF;
573 --}
574 END IF;
575
576 i := 1;
577 LOOP
578 IF i > length(l_sql_string) THEN
579 EXIT;
580 END IF;
581 l_sub_str := SUBSTR(l_sql_string, i , 80);
582 WSH_UTIL_CORE.println(l_sub_str);
583 i := i + 80;
584 END LOOP;
585
586 l_CursorID := DBMS_SQL.Open_Cursor;
587 DBMS_SQL.Parse(l_CursorID, l_sql_string, DBMS_SQL.v7 );
588 DBMS_SQL.Define_Column(l_CursorID, 1, l_request_control_id);
589 DBMS_SQL.Define_Column(l_CursorID, 2, l_request_set_id);
590
591 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_vendor_id', p_vendor_id);
592 IF p_party_type IS NOT NULL THEN
593 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_party_type', p_party_type);
594 END IF;
595
596 IF p_party_id IS NOT NULL THEN
597 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_party_id', p_party_id);
598 END IF;
599
600 IF p_reference_id IS NOT NULL THEN
601 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_reference_id', p_reference_id);
602 END IF;
603
604 IF p_reference_line_id IS NOT NULL THEN
605 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_reference_line_id', p_reference_line_id);
606 END IF;
607
608 l_ignore := DBMS_SQL.Execute(l_CursorID);
609 else
610 open Get_UNprocessed_Records_2_ship;
611 if p_override_type= null and p_reference_id is not null then
612 l_flag := 1;
613 end if;
614 end if;
615
616 END IF;
617
618 LOOP
619 If p_vendor_id is NULL THEN
620 if p_application_id=660 then
621 IF DBMS_SQL.Fetch_Rows(l_cursorID) = 0 THEN
622 DBMS_SQL.Close_Cursor(l_cursorID);
623 EXIT;
624 ELSE
625 DBMS_SQL.Column_Value(l_CursorID, 1, l_request_control_id);
626 DBMS_SQL.Column_Value(l_CursorID, 2, l_request_set_id);
627 END IF;
628 else
629
630 EXIT WHEN l_flag=1;
631 WSH_UTIL_CORE.println('Start Processing for Request Control :' || l_flag);
632
633 Fetch Get_Unprocessed_Records_1_ship into
634 l_request_control_id,
635 l_request_set_id;
636 WSH_UTIL_CORE.println('Start Processing for Request Control :' || l_request_control_id);
637 EXIT WHEN Get_Unprocessed_Records_1_ship%NOTFOUND;
638
639 end if;
640 Else
641 if p_application_id=660 then
642 IF DBMS_SQL.Fetch_Rows(l_cursorID) = 0 THEN
643 DBMS_SQL.Close_Cursor(l_cursorID);
644 EXIT;
645 ELSE
646 DBMS_SQL.Column_Value(l_CursorID, 1, l_request_control_id);
647 DBMS_SQL.Column_Value(l_CursorID, 2, l_request_set_id);
648 END IF;
649 else
650 EXIT WHEN l_flag=1;
651 Fetch Get_Unprocessed_Records_2_ship into
652 l_request_control_id,
653 l_request_set_id;
654 EXIT WHEN Get_Unprocessed_Records_2_ship%NOTFOUND;
655 end if;
656
657 End If;
658 WSH_UTIL_CORE.println('Start Processing for Request Control :' || l_request_control_id);
659
660
661 WSH_UTIL_CORE.println('Updating Process Flag');
662
663 update wsh_itm_request_control
664 SET process_flag = 3
665 WHERE request_control_id = l_request_control_id;
666
667 if p_application_id=665 then
668 Handle_Exception(l_request_control_id);
669 end if;
670 BEGIN
674 p_request_control_id => l_request_control_id,
671 WSH_UTIL_CORE.println('Calling Procedure Call_Custom_API');
672 Call_Custom_API
673 (
675 p_request_set_id => l_request_set_id,
676 p_appl_id => p_application_id,
677 x_return_status => l_return_status
678 );
679
680 WSH_UTIL_CORE.println('After Call to Call_Custom_API');
681 WSH_UTIL_CORE.println('Return Status from Call_Custom_API:' || l_return_status);
682
683 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
684 RAISE Call_Custom_API_Failed;
685 END IF;
686 commit;
687
688 EXCEPTION
689 WHEN Call_Custom_API_Failed THEN
690 ROLLBACK TO WSH_ITM_OVERRIDE;
691 WSH_UTIL_CORE.println('Failed in Call_Custom_API for request control:' || l_request_control_id);
692
693 END;
694
695 WSH_UTIL_CORE.println('Finished processing for Request Control :' || l_request_control_id);
696
697 END LOOP;
698
699 If p_vendor_id is NULL THEN
700 if p_application_id=660 then
701 IF DBMS_SQL.IS_Open(l_cursorID) THEN
702 DBMS_SQL.Close_Cursor(l_cursorID);
703 END IF;
704 else
705 close Get_Unprocessed_Records_1_ship;
706 end if;
707 ELSE
708 if p_application_id=660 then
709 IF DBMS_SQL.IS_Open(l_cursorID) THEN
710 DBMS_SQL.Close_Cursor(l_cursorID);
711 END IF;
712 else
713 close Get_Unprocessed_Records_2_ship;
714 end if;
715 END IF;
716 END IF; -- p_override_type is UNPROCESSED
717
718 IF p_override_type = 'SYSTEM' OR
719 p_override_type = 'DATA' OR
720 p_override_type IS NULL THEN
721
722
723 WSH_UTIL_CORE.println('p_override_type is not unprocessed');
724 if p_application_id =660 THEN
725 -- {
726 -- Using Dynamic Cursor instead of static cursor for performance.
727 l_sql_string := 'SELECT DISTINCT wrc.request_control_id, wrc.request_set_id ';
728 l_sql_string := l_sql_string || 'FROM WSH_ITM_REQUEST_CONTROL wrc, ';
729 l_sql_string := l_sql_string || ' WSH_ITM_RESPONSE_HEADERS wrh ';
730
731 IF ( p_party_type is NOT NULL or p_party_id is NOT NULL ) THEN
732 l_sql_string := l_sql_string || ' , WSH_ITM_PARTIES wp ';
733 END IF;
734
735 l_sql_string := l_sql_string || 'WHERE wrc.response_header_id = wrh.response_header_id ';
736 l_sql_string := l_sql_string || 'AND wrc.process_flag = 2 ';
737 l_sql_string := l_sql_string || 'AND wrc.application_id = 660 ';
738
739 IF ( p_vendor_id is NOT NULL ) THEN
740 l_sql_string := l_sql_string || 'AND wrh.vendor_id = :x_vendor_id ';
741 END IF;
742
743 IF ( p_error_type is NOT NULL ) THEN
744 l_sql_string := l_sql_string || 'AND wrh.error_type = :x_error_type ';
745 END IF;
746
747 IF ( p_error_code is NOT NULL ) THEN
748 l_sql_string := l_sql_string || 'AND wrh.error_code = :x_error_code ';
749 END IF;
750
751 IF ( p_reference_id is NOT NULL ) THEN
752 l_sql_string := l_sql_string || 'AND wrc.original_system_reference = :x_reference_id ';
753 END IF;
754
755 IF ( p_reference_line_id is NOT NULL ) THEN
756 l_sql_string := l_sql_string || 'AND wrc.original_system_line_reference = :x_reference_line_id ';
757 END IF;
758
759 IF ( p_party_type is NOT NULL or p_party_id is NOT NULL ) THEN
760 --{
761 l_sql_string := l_sql_string || 'AND wrc.request_control_id = wp.request_control_id ';
762
763 IF ( p_party_type is NOT NULL ) THEN
764 l_sql_string := l_sql_string || 'AND wp.party_type = :x_party_type ';
765 END IF;
766
767 IF ( p_party_id is NOT NULL ) THEN
768 l_sql_string := l_sql_string || 'AND wp.source_org_id = :x_party_id ';
769 END IF;
770 --}
771 END IF;
772
773 l_sql_string := l_sql_string || 'UNION ';
774 l_sql_string := l_sql_string || 'SELECT DISTINCT wrc.request_control_id, wrc.request_set_id ';
775 l_sql_string := l_sql_string || 'FROM WSH_ITM_REQUEST_CONTROL wrc, ';
776 l_sql_string := l_sql_string || ' WSH_ITM_RESPONSE_HEADERS wrh, ';
777 l_sql_string := l_sql_string || ' WSH_ITM_RESPONSE_LINES wrl ';
778
779 IF ( p_party_type is NOT NULL or p_party_id is NOT NULL ) THEN
780 l_sql_string := l_sql_string || ' , WSH_ITM_PARTIES wp ';
781 END IF;
782
783 l_sql_string := l_sql_string || 'WHERE wrc.response_header_id = wrh.response_header_id ';
784 l_sql_string := l_sql_string || 'AND wrh.response_header_id = wrl.response_header_id ';
785 l_sql_string := l_sql_string || 'AND wrc.process_flag = 2 ';
786 l_sql_string := l_sql_string || 'AND wrc.application_id = 660 ';
787
788 IF ( p_vendor_id is NOT NULL ) THEN
792 IF ( p_error_type is NOT NULL ) THEN
789 l_sql_string := l_sql_string || 'AND wrh.vendor_id = :x_vendor_id ';
790 END IF;
791
793 l_sql_string := l_sql_string || 'AND wrl.error_type = :x_error_type ';
794 END IF;
795
796 IF ( p_error_code is NOT NULL ) THEN
797 l_sql_string := l_sql_string || 'AND wrl.error_code = :x_error_code ';
798 END IF;
799
800 IF ( p_reference_id is NOT NULL ) THEN
801 l_sql_string := l_sql_string || 'AND wrc.original_system_reference = :x_reference_id ';
802 END IF;
803
804 IF ( p_reference_line_id is NOT NULL ) THEN
805 l_sql_string := l_sql_string || 'AND wrc.original_system_line_reference = :x_reference_line_id ';
806 END IF;
807
808 IF ( p_party_type is NOT NULL or p_party_id is NOT NULL ) THEN
809 --{
810 l_sql_string := l_sql_string || 'AND wrc.request_control_id = wp.request_control_id ';
811
812 IF ( p_party_type is NOT NULL ) THEN
813 l_sql_string := l_sql_string || 'AND wp.party_type = :x_party_type ';
814 END IF;
815
816 IF ( p_party_id is NOT NULL ) THEN
817 l_sql_string := l_sql_string || 'AND wp.source_org_id = :x_party_id ';
818 END IF;
819 --}
820 END IF;
821
822 i := 1;
823 LOOP
824 IF i > length(l_sql_string) THEN
825 EXIT;
826 END IF;
827 l_sub_str := SUBSTR(l_sql_string, i , 80);
828 WSH_UTIL_CORE.println(l_sub_str);
829 i := i + 80;
830 END LOOP;
831
832 l_CursorID := DBMS_SQL.Open_Cursor;
833 DBMS_SQL.Parse(l_CursorID, l_sql_string, DBMS_SQL.v7 );
834 DBMS_SQL.Define_Column(l_CursorID, 1, l_request_control_id);
835 DBMS_SQL.Define_Column(l_CursorID, 2, l_request_set_id);
836
837 IF p_party_type IS NOT NULL THEN
838 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_party_type', p_party_type);
839 END IF;
840
841 IF p_party_id IS NOT NULL THEN
842 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_party_id', p_party_id);
843 END IF;
844
845 IF p_vendor_id IS NOT NULL THEN
846 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_vendor_id', p_vendor_id);
847 END IF;
848
849 IF p_error_type IS NOT NULL THEN
850 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_error_type', p_error_type);
851 END IF;
852
853 IF p_error_code IS NOT NULL THEN
854 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_error_code', p_error_code);
855 END IF;
856
857 IF p_reference_id IS NOT NULL THEN
858 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_reference_id', p_reference_id);
859 END IF;
860
861 IF p_reference_line_id IS NOT NULL THEN
862 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':x_reference_line_id', p_reference_line_id);
863 END IF;
864
865 l_ignore := DBMS_SQL.Execute(l_CursorID);
866 -- }
867 ELSE
868 open Get_Request_Control_ship;
869 END IF;
870
871 LOOP
872 IF p_application_id = 660 THEN
873 --{
874 IF DBMS_SQL.Fetch_Rows(l_cursorID) = 0 THEN
875 DBMS_SQL.Close_Cursor(l_cursorID);
876 EXIT;
877 ELSE
878 DBMS_SQL.Column_Value(l_CursorID, 1, l_request_control_id);
879 DBMS_SQL.Column_Value(l_CursorID, 2, l_request_set_id);
880 END IF;
881 -- }
882 ELSE
883 fetch Get_Request_Control_ship into l_request_control_id,l_request_set_id;
884 EXIT WHEN Get_Request_Control_ship%NOTFOUND;
885 END IF;
886
887 WSH_UTIL_CORE.println('Start of Processing for Request Control : ' || l_request_control_id);
888
889 ----------------------------------------------------------
890 -- Call the Response Analyser API
891 ----------------------------------------------------------
892
893 WSH_UTIL_CORE.println(' Calling the Response Analyser');
894
895 WSH_ITM_RESPONSE_PKG.ONT_RESPONSE_ANALYSER
896 (
897 p_request_control_id => l_request_control_id,
898 x_interpreted_value => l_interpreted_value,
899 x_SrvTab => l_SrvTab,
900 x_return_status => l_return_status
901 );
902 IF l_interpreted_value = 'DATA' AND p_application_id =660 THEN
903 IF p_override_type ='SYSTEM' THEN
904 WSH_UTIL_CORE.println('Response from Response analyser is different from the override parameter entered by user.. ignoring this and processing next record');
905 ELSE
906 WSH_UTIL_CORE.println('Cannot process data errors');
907 END IF;
908 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
909 RAISE Response_Analyser_Failed;
910 END IF;
911
912 ELSE
913
914 WSH_UTIL_CORE.println('After Calling the Response Analyser');
915 WSH_UTIL_CORE.println(' ');
916
917
918 WSH_UTIL_CORE.println('Response from Response analyser: ' || l_interpreted_value);
919 WSH_UTIL_CORE.println('Return Status from Response analyser :' || l_return_status);
920 WSH_UTIL_CORE.println(' ');
921
922 BEGIN
923
924 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
925 RAISE Response_Analyser_Failed;
926 END IF;
927
928 IF l_interpreted_value = p_override_type OR
929 p_override_type IS NULL THEN
930
931
932 WSH_UTIL_CORE.println('Updating process_flag to 3');
933
934 UPDATE wsh_itm_request_control
935 SET process_flag = 3
936 WHERE request_control_id = l_request_control_id;
937
938 IF p_application_id=665 THEN
939 Handle_Exception(l_request_control_id);
940 ELSE
941 Call_Custom_API
942 (
943 p_request_control_id => l_request_control_id,
944 p_request_set_id => l_request_set_id,
945 p_appl_id => p_application_id,
946 x_return_status => l_return_status
947 );
948
949 WSH_UTIL_CORE.println('AFter Call_Custom_API');
950
951 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
952 RAISE Call_Custom_API_Failed;
953 END IF;
954
955 END IF;
956
957 WSH_UTIL_CORE.println('Finished Processing for Request Control :' || l_request_control_id);
958 commit;
959 ELSE
960 WSH_UTIL_CORE.println('Response from Response analyser is different from the override parameter entered by user.. ignoring this and processing next record');
961 END IF;
962
963 EXCEPTION
964 WHEN Response_Analyser_Failed THEN
965 ROLLBACK TO WSH_ITM_OVERRIDE;
966 WSH_UTIL_CORE.println('Processing failed in Response Analyser');
967
968 WHEN Call_Custom_API_Failed THEN
969 ROLLBACK TO WSH_ITM_OVERRIDE;
970 WSH_UTIL_CORE.println('Failed in Call_Custom_API for request control:' || l_request_control_id);
971
972 END;
973 END IF ;
974 END LOOP;
975 IF p_application_id=660 THEN
976 IF DBMS_SQL.IS_Open(l_cursorID) THEN
977 DBMS_SQL.Close_Cursor(l_cursorID);
978 END IF;
979 ELSE
980 close Get_Request_Control_ship;
981 END IF;
982 END IF;
983
984 WSH_UTIL_CORE.println('The Processing - Completed..');
985 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
986
987
988 EXCEPTION
989
990
991 WHEN OTHERS THEN
992
993 ROLLBACK TO WSH_ITM_OVERRIDE;
994 l_completion_status := 'ERROR';
995 l_error_code := SQLCODE;
996 l_error_text := SQLERRM;
997 WSH_UTIL_CORE.PrintMsg('Failed in Procedure ITM_Launch_Override');
998 WSH_UTIL_CORE.PrintMsg('The unexpected error is '||l_error_code||':' || l_error_text);
999 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
1000
1001
1002 END ITM_Launch_Override;
1003
1004 END WSH_ITM_OVERRIDE;