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