[Home] [Help]
PACKAGE BODY: APPS.XDP_MACROS
Source
1 PACKAGE BODY XDP_MACROS AS
2 /* $Header: XDPMACRB.pls 120.1 2005/06/09 00:07:05 appldev $ */
3
4
5 g_new_line CONSTANT VARCHAR2(10) := convert(FND_GLOBAL.LOCAL_CHR(10),
6 substr(userenv('LANGUAGE'), instr(userenv('LANGUAGE'),'.') +1),
7 'WE8ISO8859P1') ;
8
9 g_Unhandled number := -20001;
10 g_MsgNoLogString varchar2(2000);
11 g_MsgNullResponse varchar2(2000);
12 g_MsgAuditStr varchar2(2000);
13
14 g_StatusSuccess varchar2(30) := 'SUCCESS';
15 g_StatusTimeout varchar2(30) := 'TIMEOUT';
16 g_StatusFailure varchar2(30) := 'FAILURE';
17 g_StatusWarning varchar2(30) := 'WARNING';
18 g_StatusSessionLost varchar2(30) := 'SESSION_LOST';
19
20 -- Private Routines
21 Procedure SetFeCmdTimeout;
22
23 Procedure SetupUserMessages;
24 Function GetNoLogString return varchar2;
25 Function getNullResp return varchar2;
26
27 Procedure ResetResponseBuffer;
28 Procedure StripOffendingChars;
29
30 -- do an HTTP Get
31 Procedure doGet(p_Url in varchar2,
32 p_LogCommand in varchar2,
33 p_EncryptFlag in varchar2);
34
35 -- Procedure AppendConnectCommands(p_Command in varchar2,
36 -- p_Response in varchar2);
37
38 Procedure LoadAuditTrail(CommandSent in varchar2,
39 Response in varchar2,
40 NoLob in boolean default false);
41
42 Procedure LogAuditTrail(LogCmd in varchar2,
43 EncryptFlag in varchar2,
44 ErrorMessage in varchar2 default null);
45
46 Procedure LogAuditTrail(AuditCmd in varchar2);
47
48
49 -- Public Routines...
50 -- Sends a SYNC message which in turn also cleans up the TO and RETURN Channels
51 Procedure SEND_SYNC
52 is
53 begin
54
55 XDP_ADAPTER_CORE.SendSync(p_ChannelName => pv_ApplChannelName);
56
57 end SEND_SYNC;
58
59 --
60 -- This is the internal translation of the users SEND macro.
61 Procedure SEND(p_Command in varchar2,
62 p_EncryptFlag in varchar2 default 'N',
63 p_Prompt in varchar2 default 'IGNORE')
64 is
65
66 -- PL/SQL Block
67 l_ActualStr varchar2(32767);
68
69 l_TempResponse varchar2(32767);
70
71 l_LogCmd varchar2(32767);
72 l_Dummy varchar2(32767);
73
74 l_PromptValue varchar2(4000);
75
76 l_RespXML varchar2(32767);
77 l_MoreFlag varchar2(10);
78 l_Status varchar2(40);
79
80 begin
81
82 SFM_SQLCODE := 0;
83 SFM_SQLERRM := null;
84
85 -- Cleanup buffers b4 every send
86 ResetResponseBuffer;
87
88 -- dbms_output.put_line('SEND: Cmd: ' || substr(p_Command,1,200));
89 -- dbms_output.put_line(pv_OrderID || ':' ||
90 -- pv_LineItemID || ':' ||
91 -- pv_WorkItemInstanceID || ':' ||
92 -- pv_FAInstanceID);
93 -- Get the Command String to be sent out
94 xdp_procedure_builder_util.ReplaceOrderParameters(
95 p_OrderID => pv_OrderID,
96 p_LineItemID => pv_LineItemID,
97 p_WIInstanceID => pv_WorkItemInstanceID,
98 p_FAInstanceID => pv_FaInstanceId,
99 p_CmdString => p_Command,
100 x_CmdStringReplaced => l_ActualStr,
101 x_CmdStringLog => l_LogCmd,
102 x_ErrorCode => SFM_SQLCODE,
103 x_ErrorString => SFM_SQLERRM);
104
105 if SFM_SQLCODE <> 0 then
106 xdpcore.context('XDP_MACROS',
107 'SEND.ReplaceOrderParameters',
108 p_Command, SFM_SQLCODE, SFM_SQLERRM);
109 xdpcore.raise(SFM_SQLCODE, SFM_SQLERRM);
110 end if;
111
112 -- Get the Prompt String to be sent out...
113 if p_Prompt is not null and p_Prompt <> 'IGNORE' then
114 xdp_procedure_builder_util.ReplaceOrderParameters(
115 p_OrderID => pv_OrderID,
116 p_LineItemID => pv_LineItemID,
117 p_WIInstanceID => pv_WorkItemInstanceID,
118 p_FAInstanceID => pv_FaInstanceId,
119 p_CmdString => p_Prompt,
120 x_CmdStringReplaced => l_PromptValue,
121 x_CmdStringLog => l_Dummy,
122 x_ErrorCode => SFM_SQLCODE,
123 x_ErrorString => SFM_SQLERRM);
124
125 if SFM_SQLCODE <> 0 then
126 xdpcore.context('XDP_MACROS',
127 'SEND.ReplaceOrderParameters',
128 p_Prompt, SFM_SQLCODE, SFM_SQLERRM);
129 xdpcore.raise(SFM_SQLCODE, SFM_SQLERRM);
130 end if;
131 else
132 l_PromptValue := p_Prompt;
133 end if;
134
135 begin
136 -- reset the Log Locator for every SEND
137 pv_ResponseLob := null;
138 DBMS_LOB.createtemporary(pv_ResponseLob, TRUE);
139
140 xdp_adapter.pv_AdapterExitCode := null;
141
142 -- dbms_output.put_line('Sending: ' || substr(l_ActualStr, 1, 200));
143 -- dbms_output.put_line('Prompt: ' || substr(l_PromptValue, 1, 200));
144
145 XDP_ADAPTER_CORE.SendApplicationMessage(p_ChannelName => pv_ApplChannelName,
146 p_Command => l_ActualStr,
147 p_Response => l_PromptValue);
148
149 -- dbms_output.put_line('Waiting for response...');
150 -- dbms_output.put_line('Timeout: ' || to_char(pv_MesgTimeout));
151 XDP_ADAPTER_CORE.WaitForMessage(p_ChannelName => pv_ReturnChannelName,
152 p_Timeout => pv_MesgTimeout + 5,
153 p_ResponseMessage => l_RespXML);
154
155 l_Status := XDP_ADAPTER_CORE_XML.DecodeMessage(p_WhattoDecode => 'STATUS',
156 p_XMLMessage => l_RespXML);
157
158 -- dbms_output.put_line('Status: ' || l_Status);
159
160 -- dbms_output.put_line('Sending ACK');
161 XDP_ADAPTER_CORE.SendAck(p_ChannelName => pv_ApplChannelName);
162
163 -- dbms_output.put_line('After Sending ACK');
164
165 xdp_adapter.pv_AdapterExitCode := XDP_ADAPTER_CORE_XML.DecodeMessage
166 (p_WhattoDecode => 'EXIT_CODE',
167 p_XMLMessage => l_RespXML);
168
169 -- dbms_output.put_line('Exit Code: ' || xdp_adapter.pv_AdapterExitCode);
170 if (l_Status = g_StatusSuccess) then
171
172 l_MoreFlag := XDP_ADAPTER_CORE_XML.DecodeMessage(
173 p_WhattoDecode => 'MORE_FLAG',
174 p_XMLMessage => l_RespXML);
175
176 -- dbms_output.put_line('More Flag : ' || l_MoreFlag);
177 SFM_int_response_buffer :=
178 XDP_ADAPTER_CORE_XML.DecodeMessage(p_WhattoDecode => 'DATA',
179 p_XMLMessage => l_RespXML);
180
181 if SFM_int_response_buffer is not null then
182 -- dbms_output.put_line('After Reponse' || SFM_int_response_buffer);
183 dbms_lob.writeappend(pv_ResponseLob, length(SFM_int_response_buffer), SFM_int_response_buffer);
184 end if;
185 -- dbms_output.put_line('After Logging..');
186 -- dbms_output.put_line('After Reponse' || SFM_int_response_buffer);
187
188 while (l_MoreFlag is not null and l_MoreFlag = 'Y' ) loop
189
190 -- dbms_output.put_line('Waiting for message(LOOP)');
191 -- dbms_output.put_line('Timeout: ' || to_char(pv_MesgTimeout));
192 XDP_ADAPTER_CORE.WaitForMessage(
193 p_ChannelName => pv_ReturnChannelName,
194 p_Timeout => pv_MesgTimeout + 5,
195 p_ResponseMessage => l_RespXML);
196
197 l_Status := XDP_ADAPTER_CORE_XML.DecodeMessage(
198 p_WhattoDecode => 'STATUS',
199 p_XMLMessage => l_RespXML);
200 -- dbms_output.put_line('Status: (LOOP) ' || l_Status);
201
202 -- dbms_output.put_line('Sending ACK (LOOP)');
203 XDP_ADAPTER_CORE.SendAck(p_ChannelName => pv_ApplChannelName);
204 -- dbms_output.put_line('After Sending ACK (LOOP)');
205
206 xdp_adapter.pv_AdapterExitCode := XDP_ADAPTER_CORE_XML.DecodeMessage
207 (p_WhattoDecode => 'EXIT_CODE',
208 p_XMLMessage => l_RespXML);
209
210 -- dbms_output.put_line('Exit Code:(LOOP) ' || xdp_adapter.pv_AdapterExitCode);
211 if (l_Status = g_StatusSuccess ) then
212
213 l_MoreFlag := XDP_ADAPTER_CORE_XML.DecodeMessage(
214 p_WhattoDecode => 'MORE_FLAG',
215 p_XMLMessage => l_RespXML);
216
217 -- dbms_output.put_line('More Flag: (LOOP) ' || l_MoreFlag);
218 l_TempResponse := XDP_ADAPTER_CORE_XML.DecodeMessage(
219 p_WhattoDecode => 'DATA',
220 p_XMLMessage => l_RespXML);
221
222 if l_TempResponse is not null then
223 -- dbms_output.put_line('After Reponse (LOOP)' || length(l_TempResponse));
224 dbms_lob.writeappend(
225 pv_ResponseLob, length(l_TempResponse), l_TempResponse);
226 end if;
227 -- dbms_output.put_line('After Logging(LOOP)..');
228
229 if length(SFM_int_response_buffer) + length(l_TempResponse) <= 32767 then
230 SFM_int_response_buffer := SFM_int_response_buffer || l_TempResponse;
231 elsif length(SFM_int_response_buffer) < 32766 then
232 SFM_int_response_buffer :=
233 SFM_int_response_buffer ||
234 substr(l_TempResponse, 1,
235 32766 - length(SFM_int_response_buffer) );
236 end if;
237
238 else
239 SFM_SQLERRM := XDP_ADAPTER_CORE_XML.DecodeMessage(
240 p_WhattoDecode => 'DATA',
241 p_XMLMessage => l_RespXML);
242 exit;
243 end if;
244 end loop;
245
246 else
247 SFM_SQLERRM := XDP_ADAPTER_CORE_XML.DecodeMessage(
248 p_WhattoDecode => 'DATA',
249 p_XMLMessage => l_RespXML);
250 end if;
251 exception
252 when others then
253 -- dbms_output.put_line('EXCEPTION: ' || SQLCODE);
254 -- Log into the Audit trail the command the response
255 -- Log the command send irrespective of any errors.
256
257 -- Strip any PL/SQL offending characters
258 -- Mainly chr(0)'s
259 StripOffendingChars;
260
261 LogAuditTrail( LogCmd => l_LogCmd,
262 EncryptFlag => p_EncryptFlag,
263 ErrorMessage => sqlerrm);
264
265 xdpcore.context('XDP_MACROS',
266 'SEND',
267 l_ActualStr, SQLCODE, SQLERRM);
268 xdpcore.raise(g_Unhandled);
269
270 end;
271
272 -- Strip any PL/SQL offending characters
273 -- Mainly chr(0)'s
274 StripOffendingChars;
275
276 -- dbms_output.put_line('Sending Sync..');
277 -- XDP_ADAPTER_CORE.SendSync(p_ChannelName => pv_ApplChannelName,
278 -- p_CleanupPipe => 'N');
279 -- dbms_output.put_line('After Sending Sync..');
280
281 -- Log the Response
282 LogAuditTrail( LogCmd => l_LogCmd,
283 EncryptFlag => p_EncryptFlag,
284 ErrorMessage => SFM_SQLERRM);
285
286 if l_Status = g_StatusSuccess then
287 null;
288 else
289 -- Set the Context
290 xdpcore.context('XDP_MACROS',
291 'Send',
292 l_Status,
293 pv_ReturnChannelName,
294 l_ActualStr,
295 l_PromptValue,
296 pv_MesgTimeout,
297 SFM_int_response_buffer);
298
299 if (l_Status = g_StatusFailure) then
300 SFM_SQLCODE := xdp_adapter.pv_AdapterFailure;
301 xdpcore.raise(xdp_adapter.pv_AdapterFailure, SFM_SQLERRM);
302
303 elsif (l_Status = g_StatusTimeout) then
304 SFM_SQLCODE := xdp_adapter.pv_AdapterTimeOut;
305 xdpcore.raise(xdp_adapter.pv_AdapterTimeOut, SFM_SQLERRM);
306
307 elsif (l_Status = g_StatusWarning) then
308 SFM_SQLCODE := xdp_adapter.pv_AdapterWarning;
309 xdpcore.raise(xdp_adapter.pv_AdapterWarning, SFM_SQLERRM);
310
311 elsif (l_Status = g_StatusSessionLost) then
312 SFM_SQLCODE := xdp_adapter.pv_AdapterSessionLost;
313 xdpcore.raise(xdp_adapter.pv_AdapterSessionLost, SFM_SQLERRM);
314 else
315 xdpcore.raise(g_Unhandled, SFM_SQLERRM);
316 end if;
317 end if;
318
319 -- DBMS_LOB.freetemporary(pv_ResponseLob);
320
321 end SEND;
322
323 --
324 -- For Backward Compatibility...
325 Procedure SEND(p_Command in varchar2,
326 p_EncryptFlag in varchar2 default 'N',
327 p_Prompt in varchar2 default 'IGNORE',
328 x_ErrorCode OUT NOCOPY number,
329 x_ErrorString OUT NOCOPY varchar2)
330 is
331
332 begin
333 Send(p_Command => p_Command,
334 p_EncryptFlag => p_EncryptFlag,
335 p_Prompt => p_Prompt);
336
337 x_ErrorCode := SFM_SQLCODE;
338 x_ErrorString := SFM_SQLERRM;
339
340 exception
341 when others then
342 if SFM_SQLCODE <> 0 then
343 x_ErrorCode := SFM_SQLCODE;
344 x_ErrorString := SFM_SQLERRM;
345 -- For Backward Compatibility. Do not raise exception
346 -- in cases of timeout Only. SessionLost, Failure etc will still
347 -- Raise the exception
348 if SFM_SQLCODE = xdp_adapter.pv_AdapterTimeOut then
349 -- NO OP
350 null;
351 else
352 raise;
353 end if;
354 else
355 -- Unhandled Exception. Raise it
356 x_ErrorCode := SQLCODE;
357 x_ErrorString := SQLERRM;
358 raise;
359 end if;
360 end SEND;
361
362 --
363 -- For Backward Compatibility...
364 Procedure SEND(p_Command in varchar2,
365 p_EncryptFlag in varchar2 default 'N',
366 x_ErrorCode OUT NOCOPY number,
367 x_ErrorString OUT NOCOPY varchar2)
368 is
369
370 begin
371 Send(p_Command => p_Command,
372 p_EncryptFlag => p_EncryptFlag);
373
374 x_ErrorCode := SFM_SQLCODE;
375 x_ErrorString := SFM_SQLERRM;
376
377 exception
378 when others then
379 if SFM_SQLCODE <> 0 then
380 x_ErrorCode := SFM_SQLCODE;
381 x_ErrorString := SFM_SQLERRM;
382 -- For Backward Compatibility. Do not raise exception
383 -- in cases of timeout Only. SessionLost, Failure etc will still
384 -- Raise the exception
385 if SFM_SQLCODE = xdp_adapter.pv_AdapterTimeOut then
386 -- NO OP
387 null;
388 else
389 raise;
390 end if;
391 else
392 -- Unhandled Exception. Raise it
393 x_ErrorCode := SQLCODE;
394 x_ErrorString := SQLERRM;
395 raise;
396 end if;
397 end SEND;
398
399
400 --
401 -- This is the internal translation of the users SEND_HTTP macro.
402 Procedure SEND_HTTP(p_Url in varchar2,
403 p_EncryptFlag in varchar2 default 'N',
404 p_Proxy in varchar2 default null)
405 is
406 l_ActualStr varchar2(32767);
407 l_LogCmd varchar2(32767);
408
409
410 begin
411
412 -- cleanup buffers b4 each SEND_HTTP
413 ResetResponseBuffer;
414
415 -- Get the Command String to be sent out
416 xdp_procedure_builder_util.ReplaceOrderParameters(
417 p_OrderID => pv_OrderID,
418 p_LineItemID => pv_LineItemID,
419 p_WIInstanceID => pv_WorkItemInstanceID,
420 p_FAInstanceID => pv_FaInstanceId,
421 p_CmdString => p_Url,
422 x_CmdStringReplaced => l_ActualStr,
423 x_CmdStringLog => l_LogCmd,
424 x_ErrorCode => SFM_SQLCODE,
425 x_ErrorString => SFM_SQLERRM);
426
427 if SFM_SQLCODE <> 0 then
428 xdpcore.context('XDP_MACROS',
429 'SEND_HTTP.ReplaceOrderParameters',
430 p_Url, SFM_SQLCODE, SFM_SQLERRM);
431 xdpcore.raise;
432 end if;
433
434 -- Do a Get Depending on the input
435 -- GET is the ONLY option supported as of now
436 doGet( p_Url => l_ActualStr,
437 p_LogCommand => l_LogCmd,
438 p_EncryptFlag => p_EncryptFlag);
439
440 end SEND_HTTP;
441
442
443 --
444 -- For Backward Compatibility...
445 Procedure SEND_HTTP(p_Url in varchar2,
446 p_EncryptFlag in varchar2 default 'N',
447 x_ErrorCode OUT NOCOPY number,
448 x_ErrorString OUT NOCOPY varchar2)
449 is
450
451 begin
452 SEND_HTTP(p_Url => p_Url,
453 p_EncryptFlag => p_EncryptFlag);
454
455 x_ErrorCode := SFM_SQLCODE;
456 x_ErrorString := SFM_SQLERRM;
457
458 exception
459 when others then
460 -- Unhandled Exception. Raise it
461 -- SEND_HTTP does not support any user specific interactions with errors etc
462 -- for e.g Command Timeout etc are not supported in SEND_HTTP and user cannot
463 -- trap this error code and code accordingly
464 x_ErrorCode := SQLCODE;
465 x_ErrorString := SQLERRM;
466 raise;
467 end SEND_HTTP;
468
469 --
470 -- This method is done for an "HTTP GET Request"
471 Procedure doGet(p_Url in varchar2,
472 p_LogCommand in varchar2,
473 p_EncryptFlag in varchar2)
474 is
475 l_ReminderLen number;
476
477 l_Resp UTL_HTTP.HTML_PIECES;
478
479 begin
480
481 -- Use the UTL_HTTP package to do the GET request
482 begin
483 l_Resp := UTL_HTTP.Request_pieces(p_Url);
484
485 SFM_int_response_buffer := l_Resp(1);
486
487 for i in 2..l_Resp.count loop
488 if LENGTH(SFM_int_response_buffer) < 32767 then
489 if (LENGTH(SFM_int_response_buffer) +
490 LENGTH(l_Resp(i))) < 32767 then
491 SFM_int_response_buffer := SFM_int_response_buffer ||
492 l_Resp(i);
493 else
494 l_ReminderLen := 32767 - LENGTH(l_Resp(i));
495 SFM_int_response_buffer := SFM_int_response_buffer
496 || SUBSTR(l_Resp(i), 1, l_ReminderLen);
497 end if;
498 end if;
499 end loop;
500 exception
501 when others then
502 -- Log in the audit trail anyway
503 LogAuditTrail( LogCmd => p_LogCommand,
504 EncryptFlag => p_EncryptFlag,
505 ErrorMessage => sqlerrm);
506
507 SFM_SQLCODE := sqlcode;
508 SFM_SQLCODE := sqlerrm;
509
510 xdpcore.context('XDP_MACROS',
511 'SEND_HTTP.doGet.UTL_HTTP.Request_pieces',
512 p_Url, SQLCODE, SQLERRM);
513 xdpcore.raise(g_Unhandled);
514 end;
515
516 -- Log the response
517 LogAuditTrail( LogCmd => p_LogCommand,
518 EncryptFlag => p_EncryptFlag);
519 end doGet;
520
521 --
522 -- This is the internal translation of the users GET_RESPONSE macro.
523 Function GET_RESPONSE return varchar2
524 is
525 begin
526 return (SFM_int_response_buffer);
527
528 end GET_RESPONSE;
529
530 --
531 -- This is the internal translation of the users GET_PARAM_VALUE macro.
532 Function GET_PARAM_VALUE(p_ParamName in varchar2) return varchar2
533 is
534 l_ActualStr varchar2(4000);
535 l_dummy varchar2(4000);
536 begin
537
538 -- Get the Command String
539 xdp_procedure_builder_util.ReplaceOrderParameters(
540 p_OrderID => pv_OrderID,
541 p_LineItemID => pv_LineItemID,
542 p_WIInstanceID => pv_WorkItemInstanceID,
543 p_FAInstanceID => pv_FaInstanceId,
544 p_CmdString => p_ParamName,
545 x_CmdStringReplaced => l_ActualStr,
546 x_CmdStringLog => l_dummy,
547 x_ErrorCode => SFM_SQLCODE,
548 x_ErrorString => SFM_SQLERRM);
549
550 if SFM_SQLCODE <> 0 then
551 xdpcore.context('XDP_MACROS',
552 'GET_PARAM_VALUE.ReplaceOrderParameters',
553 p_ParamName, SFM_SQLCODE, SFM_SQLERRM);
554 xdpcore.raise;
555 end if;
556
557 -- dbms_output.put_line('SFMCODE: ' || SFM_SQLCODE);
558 return (l_ActualStr);
559
560 end GET_PARAM_VALUE;
561
562 --
563 -- For Future use only!!
564 Function GET_ATTR_VALUE(p_AttrName in varchar2) return varchar2
565 is
566 l_ActualCmd varchar2(2000);
567 begin
568 xdp_procedure_builder_util.ReplaceFEAttributes(
569 p_FeName => pv_FeName,
570 p_CmdString => p_AttrName,
571 x_CmdStringReplaced => l_ActualCmd,
572 x_ErrorCode => SFM_SQLCODE,
573 x_ErrorString => SFM_SQLERRM);
574
575 if SFM_SQLCODE <> 0 then
576 -- dbms_output.put_line('Error: ' || SFM_SQLERRM);
577 xdpcore.context('XDP_MACROS',
578 'GET_ATTR_VALUE.RepalceFEAttributes',
579 p_AttrName, SFM_SQLCODE, SFM_SQLERRM);
580 xdpcore.raise;
581 end if;
582
583 return (l_ActualCmd);
584
585 end GET_ATTR_VALUE;
586
587 --
588 -- This is the internal translation of the users RESPONSE_CONTAINS macro.
589 Function RESPONSE_CONTAINS(p_UserString in varchar2) return boolean
590 is
591
592 begin
593
594 if p_UserString is NULL or SFM_int_response_buffer is NULL then
595 return true;
596 end if;
597
598 if instrb(SFM_int_response_buffer, p_UserString, 1, 1) > 0 then
599 return true;
600 else
601 return false;
602 end if;
603
604 end RESPONSE_CONTAINS;
605
606 --
607 -- This is the internal translation of the users NOTIFY_ERROR macro.
608 Procedure NOTIFY_ERROR(p_UserMessage in varchar2,
609 p_AutomaticRetry in number default 0)
610 is
611 begin
612 SFM_SQLCODE := g_NotifyError;
613 SFM_SQLERRM := p_UserMessage;
614 pv_AutoRetry := p_AutomaticRetry;
615 raise e_NotifyError;
616
617 end NOTIFY_ERROR;
618
619 --
620 -- For Backward compatibility
621 Procedure NOTIFY_ERROR(p_UserMessage in varchar2,
622 p_Overwrite in varchar2)
623 is
624 begin
625 SFM_SQLCODE := g_NotifyError;
626 SFM_SQLERRM := p_UserMessage;
627
628 if p_Overwrite = 'A' then
629 SFM_SQLERRM := substr(p_UserMessage ||
630 ' ' ||
631 substr(SFM_int_response_buffer,1,1500), 1, 2000);
632 end if;
633 raise e_NotifyError;
634
635 end NOTIFY_ERROR;
636
637 -- New for 11.5.6++
638 --
639 -- This is the internal translation of the users GET_LONG_RESPONSE macro.
640 Function GET_LONG_RESPONSE return CLOB
641 is
642 begin
643 return (pv_ResponseLob);
644
645 end GET_LONG_RESPONSE;
646
647 --
648 -- This is the internal translation of the users AUDIT macro.
649 Procedure AUDIT(p_AuditString in varchar2)
650 is
651 begin
652 LogAuditTrail(AuditCmd => p_AuditString);
653 end AUDIT;
654
655 --
656 -- This function returns if the FP error needs to be automatically
657 -- retried
658 Function AUTO_RETRY_ENABLED return varchar2
659 is
660 begin
661 if pv_AutoRetry = 0 then
662 return 'N';
663 else
664 return 'Y';
665 end if;
666
667 end AUTO_RETRY_ENABLED;
668
669 --
670 -- This is the internal translation of the Users SEND and LOGIN macros
671 -- in the Connect/Disconnect procedures
672 Procedure SEND_CONNECT( p_Command in varchar2,
673 p_Prompt in varchar2 default 'IGNORE')
674 is
675 l_ActualCmd varchar2(4000);
676 l_ActualPrompt varchar2(4000);
677 begin
678
679 -- Replace the FE Attributes in the command to be sent by their values
680 xdp_procedure_builder_util.ReplaceFEAttributes(
681 p_FeName => pv_FeName,
682 p_CmdString => p_Command,
683 x_CmdStringReplaced => l_ActualCmd,
684 x_ErrorCode => SFM_SQLCODE,
685 x_ErrorString => SFM_SQLERRM);
686
687 if SFM_SQLCODE <> 0 then
688 -- dbms_output.put_line('Error: ' || SFM_SQLERRM);
689 xdpcore.context('XDP_MACROS',
690 'SEND_CONNET.RepalceFEAttributes',
691 p_Command, SFM_SQLCODE, SFM_SQLERRM);
692 xdpcore.raise;
693 end if;
694
695 -- Replace the FE Attributes in the Prompt by their values
696 xdp_procedure_builder_util.ReplaceFEAttributes(
697 p_FeName => pv_FeName,
698 p_CmdString => p_Prompt,
699 x_CmdStringReplaced => l_ActualPrompt,
700 x_ErrorCode => SFM_SQLCODE,
701 x_ErrorString => SFM_SQLERRM);
702
703 if SFM_SQLCODE <> 0 then
704 -- dbms_output.put_line('Error: ' || SFM_SQLERRM);
705 xdpcore.context('XDP_MACROS',
706 'SEND_CONNET.RepalceFEAttributes',
707 p_Command, SFM_SQLCODE, SFM_SQLERRM);
708 xdpcore.raise;
709 end if;
710
711 -- dbms_output.put_line('Command: ' || l_ActualCmd);
712 -- dbms_output.put_line('Response: ' || l_ActualPrompt);
713
714 -- Append the commands to the internal buffer
715 -- The commands are not actually "Sent" to the adapter
716 -- The adapter executes the Connect Procedure which in turn populated
717 -- the buffer. Once this is done the adapter executes the
718 -- "FetchConnectCommands reoutine to get the commands
719 AppendConnectCommands(p_Command => l_ActualCmd,
720 p_Response => l_ActualPrompt);
721
722 end SEND_CONNECT;
723
724 --
725 -- For backward compatibility
726 Procedure SEND_CONNECT( p_Command in varchar2,
727 p_Prompt in varchar2 default 'IGNORE',
728 x_ErrorCode OUT NOCOPY number,
729 x_ErrorString OUT NOCOPY varchar2)
730 is
731
732 begin
733
734 Send_Connect(p_Command => p_Command,
735 p_Prompt => p_Prompt);
736
737 x_ErrorCode := SFM_SQLCODE;
738 x_ErrorString := SFM_SQLERRM;
739
740 exception
741 when others then
742 x_ErrorCode := SQLCODE;
743 x_ErrorString := SFM_SQLERRM;
744 end SEND_CONNECT;
745
746
747 --
748 -- This routine fetches the commands and their respective responses from
749 -- the buffer. The buffer is populated by each SEND macro
750 PROCEDURE FETCH_CONNECT_COMMANDS(p_CurrIndex in number,
751 x_TotalCount OUT NOCOPY number,
752 x_Command OUT NOCOPY varchar2,
753 x_Response OUT NOCOPY varchar2)
754 is
755
756 begin
757 x_TotalCount := xdp_macros.g_ConnectCommands.count;
758
759 if x_TotalCount > 0 then
760 x_Command := xdp_macros.g_ConnectCommands(p_CurrIndex).Command;
761 x_Response := xdp_macros.g_ConnectCommands(p_CurrIndex).Response;
762 else
763 x_Command := null;
764 x_Response := null;
765 end if;
766
767 end FETCH_CONNECT_COMMANDS;
768
769
770 -- The commands are not actually "Sent" to the adapter
771 -- The adapter executes the Connect Procedure which in turn populated
772 -- the buffer. Once this is done the adapter executes the
773 -- "FetchConnectCommands reoutine to get the commands
774 Procedure AppendConnectCommands(p_Command in varchar2,
775 p_Response in varchar2)
776 is
777 l_size number;
778 begin
779 l_size := xdp_macros.g_ConnectCommands.count;
780
781 xdp_macros.g_ConnectCommands(l_size + 1).command := p_Command;
782 xdp_macros.g_ConnectCommands(l_size + 1).response := p_Response;
783
784 end AppendConnectCommands;
785
786
787 --
788 -- Log the Command sent to the audit trail
789 -- The internal response buffer is also logged
790 Procedure LogAuditTrail(LogCmd in varchar2,
791 EncryptFlag in varchar2,
792 ErrorMessage in varchar2 default null)
793 is
794 l_Response varchar2(32767) := '' ;
795 begin
796
797 if ErrorMessage is not null then
798 l_Response := ErrorMessage || g_new_line;
799 end if;
800
801 if EncryptFlag = 'Y' then
802 l_Response := l_Response || GetNoLogString;
803 else
804 l_Response := l_Response || substr(SFM_int_response_buffer,1,32766);
805 end if;
806
807 -- dbms_output.put_line('LOG: Cmd: ' || substr(LogCmd,1,200));
808 -- dbms_output.put_line(pv_OrderID || ':' ||
809 -- pv_LineItemID || ':' ||
810 -- pv_WorkItemInstanceID || ':' ||
811 -- pv_FAInstanceID);
812
813 LoadAuditTrail(CommandSent => LogCmd,
814 Response => NVL(l_Response, getNullResp));
815
816 end LogAuditTrail;
817
818 --
819 -- Log the AUDIT string to the audit trail
820 -- This is used when the user requests a specific audit request
821 -- with the AUDIT macro
822 Procedure LogAuditTrail(AuditCmd in varchar2)
823 is
824 begin
825
826 LoadAuditTrail( CommandSent => substr(AuditCmd, 1, 1996),
827 Response => g_MsgAuditStr,
828 NoLob => true);
829
830 end LogAuditTrail;
831
832
833 --
834 -- Get the translated String for the user to let him know that he
835 -- has asked NOT to log the responses from the FE
836 Function GetNoLogString return varchar2 is
837 begin
838
839 return (g_MsgNoLogString);
840
841 end GetNoLogString;
842
843 --
844 -- Get the translated String for the user to indicate a NULL response from the FE
845 Function getNullResp return varchar2 is
846 begin
847
848 return (g_MsgNullResponse);
849
850 end getNullResp;
851
852 --
853 -- Load the Audit Trail Table
854 Procedure LoadAuditTrail(CommandSent in varchar2,
855 Response in varchar2,
856 NoLob boolean default false)
857 is
858 PRAGMA AUTONOMOUS_TRANSACTION;
859
860 l_Response varchar2(4000);
861 begin
862
863 -- Bug 3064571
864 -- Max Size for RESPONSE Column is 4000
865 -- Rest of the Response can be obtained from the RESPONSE_LONG CLOB column
866
867 l_Response := substr(Response, 1, 3999);
868
869 if NoLob then
870 INSERT INTO XDP_FE_CMD_AUD_TRAILS (
871 created_by,
872 creation_date,
873 last_updated_by,
874 last_update_date,
875 last_update_login,
876 fa_instance_id,
877 fe_command_seq,
878 fulfillment_element_name,
879 fulfillment_element_type,
880 sw_generic,
881 command_sent,
882 command_sent_date,
883 response,
884 response_long,
885 response_date,
886 provisioning_procedure)
887 VALUES (
888 FND_GLOBAL.USER_ID,
889 sysdate,
890 FND_GLOBAL.USER_ID,
891 sysdate,
892 FND_GLOBAL.LOGIN_ID,
893 pv_FAInstanceID,
894 XDP_FE_CMD_AUD_TRAILS_S.NEXTVAL,
895 pv_FEName,
896 pv_FeType,
897 pv_SWGeneric,
898 CommandSent,
899 sysdate,
900 l_Response,
901 Response,
902 sysdate,
903 pv_ProcName);
904 else
905 INSERT INTO XDP_FE_CMD_AUD_TRAILS (
906 created_by,
907 creation_date,
908 last_updated_by,
909 last_update_date,
910 last_update_login,
911 fa_instance_id,
912 fe_command_seq,
913 fulfillment_element_name,
914 fulfillment_element_type,
915 sw_generic,
916 command_sent,
917 command_sent_date,
918 response,
919 response_long,
920 response_date,
921 provisioning_procedure)
922 VALUES (
923 FND_GLOBAL.USER_ID,
924 sysdate,
925 FND_GLOBAL.USER_ID,
926 sysdate,
927 FND_GLOBAL.LOGIN_ID,
928 pv_FAInstanceID,
929 XDP_FE_CMD_AUD_TRAILS_S.NEXTVAL,
930 pv_FEName,
931 pv_FeType,
932 pv_SWGeneric,
933 CommandSent,
934 sysdate,
935 l_Response,
936 pv_ResponseLob,
937 sysdate,
938 pv_ProcName);
939 end if;
940
941 commit;
942
943 end LoadAuditTrail;
944
945
946 --
947 -- Get the total timout for the command execution
948 Procedure SetFeCmdTimeout
949 is
950 l_FeAttrVal varchar2(4000);
951 l_Retries number := 0;
952 l_CmdTimeout number := 120;
953 l_RetryWait number := 0;
954 begin
955
956 pv_MesgTimeout := 0;
957
958 begin
959 l_FeAttrVal := xdp_engine.get_fe_attributeval(
960 pv_FeID, pv_attrFeCmdTimeout);
961 if l_FeAttrVal is null then
962 l_CmdTimeout := pv_defFeCmdTimeout;
963 else
964 l_CmdTimeout := to_number(l_FeAttrVal);
965 end if;
966 exception
967 when no_data_found then
968 l_CmdTimeout := pv_defFeCmdTimeout;
969 when others then
970 raise;
971 end;
972
973 begin
974 l_FeAttrVal := xdp_engine.get_fe_attributeval(
975 pv_FeID, pv_attrFeRetryCount);
976 if l_FeAttrVal is null then
977 l_Retries := pv_defFeRetryCount;
978 else
979 l_Retries := to_number(l_FeAttrVal);
980 end if;
981 exception
982 when no_data_found then
983 l_Retries := pv_defFeRetryCount;
984 when others then
985 raise;
986 end;
987
988 begin
989 l_FeAttrVal := xdp_engine.get_fe_attributeval(
990 pv_FeID, pv_attrFeCmdRetryWait);
991 if l_FeAttrVal is null then
992 l_RetryWait := pv_defFeCmdRetryWait;
993 else
994 l_RetryWait := to_number(l_FeAttrVal);
995 end if;
996 exception
997 when no_data_found then
998 l_RetryWait := pv_defFeCmdRetryWait;
999 when others then
1000 raise;
1001 end;
1002
1003 if to_number(l_Retries) <> 0 then
1004 pv_MesgTimeout := to_number(l_CmdTimeout) +
1005 ( to_number(l_CmdTimeout) + to_number(l_RetryWait) ) *
1006 to_number(l_Retries);
1007 else
1008 pv_MesgTimeout := to_number(l_CmdTimeout);
1009 end if;
1010
1011 if pv_MesgTimeout = 0 then
1012 pv_MesgTimeout := pv_defMesgTimeout;
1013 end if;
1014
1015
1016 end SetFeCmdTimeout;
1017
1018 Procedure ResetResponseBuffer
1019 is
1020
1021 begin
1022 SFM_int_response_buffer := '';
1023
1024 end ResetResponseBuffer;
1025
1026 --
1027 -- PL/SQL does not like chr(0)!!
1028 Procedure StripOffendingChars
1029 is
1030
1031 begin
1032 SFM_int_response_buffer := replace(SFM_int_response_buffer, chr(0), '');
1033
1034 end StripOffendingChars;
1035
1036 --
1037 -- Clean up the buffers
1038 Procedure ResetCommandBuffer
1039 is
1040 begin
1041
1042 xdp_macros.g_ConnectCommands.delete;
1043
1044 end ResetCommandBuffer;
1045
1046 --
1047 -- Default Initialization for all Procedure Types which DO NOT
1048 -- require any any special treatment. The FP, Connect and Disconnect
1049 -- Procedure types have special initialization routines
1050 Procedure Initdefault( OrderID in number,
1051 LineItemID in number,
1052 WIInstanceID in number,
1053 FAInstanceID in number)
1054 is
1055
1056 begin
1057
1058 SFM_SQLCODE := 0;
1059 SFM_SQLERRM := null;
1060
1061 --
1062 -- In case the Procedure being executed is invoked from an FP
1063 -- for example GET_PARAM_VALUE of a Work Item which has an evaluation
1064 -- Procedure associated with it, the global variables to be initialized are
1065 -- NOT to be reset with nulls. If the Globals are already Initialized
1066 -- then leave them as is
1067 pv_OrderID := nvl(OrderID, pv_OrderID);
1068 pv_LineItemID := nvl(LineItemID, pv_LineItemID);
1069 pv_WorkItemInstanceID := nvl(WIInstanceID, pv_WorkItemInstanceID);
1070 pv_FAInstanceID := nvl(FAInstanceID, pv_FAInstanceID);
1071
1072 if pv_ParamCacheReqd = 'Y' then
1073 -- Cache is already set
1074 -- By FP
1075 null;
1076 else
1077 pv_ParamCacheReqd := 'N';
1078 end if;
1079
1080 end Initdefault;
1081
1082
1083 --
1084 -- Initialization specific for Fulfillment Procdure
1085 -- 1. The Parameter Config Cache needs to be initialized
1086 -- 2. The Communication channels(pipes) needs to be set
1087 -- 3. SYNC message needs to be sent
1088 -- 4. The Command time out value needs to be set
1089 Procedure InitFP(OrderID in number,
1090 LineItemID in number,
1091 WIInstanceID in number,
1092 FAInstanceID in number,
1093 ChannelName in varchar2,
1094 FEName in varchar2,
1095 ProcName in varchar2)
1096 is
1097
1098 begin
1099
1100 -- Initialize Order Related Package Variables
1101
1102 pv_OrderID := OrderID;
1103 pv_LineItemID := LineItemID;
1104 pv_WorkItemInstanceID := WIInstanceID;
1105 pv_FAInstanceID := FAInstanceID;
1106 pv_ChannelName := ChannelName;
1107 pv_FeName := FEName;
1108 pv_ProcName := ProcName;
1109 SFM_SQLCODE := 0;
1110 SFM_SQLERRM := null;
1111
1112 pv_ResponseLob := null;
1113
1114 pv_AutoRetry := 0;
1115
1116 pv_AdapterImplemented := false;
1117
1118 -- Buffers
1119 ResetResponseBuffer;
1120
1121 -- The Parameter Config Cache is required for an FP
1122 -- Intialize the Cache
1123 pv_ParamCacheReqd := 'Y';
1124 xdp_param_cache.clear_cache;
1125 xdp_param_cache.init_cache(p_wi_instance_id => WIInstanceID,
1126 p_fa_instance_id => FAInstanceID);
1127
1128 -- Setup the Channel Names for Communications and Send a SYNC message
1129 -- The SEND requires 2 channels: 1 Channel for sending commands
1130 -- and another channel for the responses from the adapter
1131
1132 -- Bug 2486815
1133 -- Send Sync for only Implemented Adapters
1134 if (XDP_ADAPTER_CORE_DB.Is_Adapter_Implemented (pv_ChannelName)) then
1135 pv_ApplChannelName := XDP_ADAPTER_CORE_PIPE.ConstructChannelName
1136 (p_ChannelType => 'APPL',
1137 p_ChannelName => pv_ChannelName);
1138
1139 pv_ReturnChannelName := XDP_ADAPTER_CORE_PIPE.GetReturnChannelName
1140 (p_ChannelName => pv_ApplChannelName);
1141
1142 Send_Sync;
1143 end if;
1144
1145 -- Setup the timeout for response from the adapter for each
1146 -- command
1147
1148 XDP_ENGINE.GET_FE_CONFIGINFO ( pv_FeName,
1149 pv_FeID,
1150 pv_FeTypeID,
1151 pv_FeType,
1152 pv_SwGeneric,
1153 pv_AdapterType);
1154
1155 SetFeCmdTimeout;
1156
1157 -- Finally set up from User Messages
1158 SetupUserMessages;
1159 end InitFP;
1160
1161
1162 --
1163 -- Procedure Finilization routine to be for all procedures.
1164 Procedure EndProc(p_return_code in OUT NOCOPY number,
1165 p_error_description in OUT NOCOPY varchar2)
1166 is
1167
1168 begin
1169 if SFM_SQLCODE <> 0 then
1170 -- Internal Error had occured
1171 -- Return with an error
1172 p_return_code := SFM_SQLCODE;
1173 p_error_description := SFM_SQLERRM;
1174 else
1175 -- User could set the return core
1176 -- Let it be as is
1177 null;
1178 end if;
1179
1180 -- dbms_output.put_line('SFMCODE: ' || SFM_SQLCODE);
1181 -- dbms_output.put_line('retcode: ' || p_return_code);
1182 end EndProc;
1183
1184 --
1185 -- Initialization routine specific to the Connetion procedure
1186 Procedure InitConnection(ChannelName in varchar2,
1187 FEName in varchar2)
1188 is
1189
1190 begin
1191 pv_FeName := FEName;
1192 pv_ChannelName := ChannelName;
1193 SFM_SQLCODE := 0;
1194 SFM_SQLERRM := null;
1195
1196 ResetResponseBuffer;
1197
1198 ResetCommandBuffer;
1199
1200 end InitConnection;
1201
1202
1203 --
1204 -- Initialization routine specific to the Connetion procedure
1205 Procedure InitDisConnection(ChannelName in varchar2,
1206 FEName in varchar2)
1207 is
1208
1209 begin
1210 null;
1211
1212 end InitDisconnection;
1213
1214
1215 --
1216 -- This routine traps all the errors in any type of procedure and returns
1217 -- appropriate error codes
1218 Procedure HandleProcErrors(p_return_code OUT NOCOPY number,
1219 p_error_description OUT NOCOPY varchar2)
1220 is
1221 begin
1222 -- Cleanup the Cache..
1223 xdp_param_cache.clear_cache;
1224
1225 if SFM_SQLCODE <> 0 then
1226 p_return_code := SFM_SQLCODE;
1227 p_error_description := SFM_SQLERRM;
1228 return;
1229 end if;
1230 p_return_code := sqlcode;
1231 p_error_description := sqlerrm;
1232
1233 -- For future Use if you want to do anything special for each error
1234
1235 if sqlcode = g_CommandTimeout then
1236 -- handle Command Timeout
1237 null;
1238 elsif sqlcode = g_SessionLost then
1239 -- Handle Session Lost
1240 null;
1241 elsif sqlcode = g_FeFailure then
1242 -- Handle FE Failure
1243 null;
1244 else
1245 -- Unhandled Exception
1246 null;
1247 end if;
1248
1249 p_return_code := sqlcode;
1250 p_error_description := substr(sqlerrm,1,1996);
1251
1252 end HandleProcErrors;
1253
1254 --
1255 -- Setup the Translated User Messages ONCE
1256 Procedure SetupUserMessages
1257 is
1258 begin
1259
1260 FND_MESSAGE.SET_NAME('XDP','XDP_FP_NO_LOG_STR');
1261 g_MsgNoLogString := FND_MESSAGE.GET;
1262
1263 FND_MESSAGE.SET_NAME('XDP','XDP_FP_NULL_RESPONSE');
1264 g_MsgNullResponse := FND_MESSAGE.GET;
1265
1266 FND_MESSAGE.SET_NAME('XDP','XDP_FP_AUDIT_STR');
1267 g_MsgAuditStr := FND_MESSAGE.GET;
1268
1269 end SetupUserMessages;
1270
1271 begin
1272 ResetCommandBuffer;
1273 pv_AckTimeout := 60;
1274 pv_MesgTimeout := 90;
1275
1276 end XDP_MACROS;