[Home] [Help]
PACKAGE BODY: APPS.XDP_PROC_CTL
Source
1 PACKAGE BODY XDP_PROC_CTL AS
2 /* $Header: XDPPCTLB.pls 120.1 2005/06/16 02:20:17 appldev $ */
3
4
5
6 g_new_line CONSTANT VARCHAR2(10) := convert(FND_GLOBAL.LOCAL_CHR(10),
7 substr(userenv('LANGUAGE'), instr(userenv('LANGUAGE'),'.') +1),
8 'WE8ISO8859P1') ;
9
10 Procedure AppendConnectCommands(Command in varchar2,
11 Response in varchar2);
12
13 Function HandlePipeErrors (ErrorCode in number, PipeOperation in varchar2,
14 Module in varchar2, HandShake varchar2,
15 Command varchar2 , InternalError in varchar2) return varchar2;
16
17 Function HandleMessageErrors (ErrorCode in number, InstanceID in number,Module in varchar2, HandShake varchar2,
18 PipeName in varchar2, Timeout in number, LogFlag in number,
19 Command varchar2 , LogCommand in varchar2, Response in varchar2,
20 LogResponse in varchar2, InternalError in varchar2) return varchar2;
21
22 Procedure LogAuditTrail(LogFlag in number, FAInstanceID in number, FEName in varchar2,
23 FeType in varchar2, SWGeneric in varchar2, LogCmd in varchar2,
24 ActualStr in varchar2, Response in varchar2, ResponseLong CLOB,
25 LogResp in varchar2, ProcName in varchar2);
26 /*
27 Procedure LogAuditTrail(LogFlag in number, FAInstanceID in number, FEName in varchar2,
28 FeType in varchar2, SWGeneric in varchar2, LogCmd in varchar2,
29 ActualStr in varchar2, Response in varchar2, LogResp in varchar2,
30 ProcName in varchar2);
31
32 Procedure LogAuditTrail(LogFlag in number, FAInstanceID in number, FEName in varchar2,
33 FeType in varchar2, SWGeneric in varchar2, LogCmd in varchar2,
34 ActualStr in varchar2, Response in varchar2, LogResp in varchar2,
35 ProcName in varchar2, ErrCode OUT NOCOPY number, ErrStr OUT NOCOPY varchar2);
36 */
37
38 Function HandlePipeErrors (ErrorCode in number, PipeOperation in varchar2,
39 Module in varchar2, HandShake varchar2,
40 Command varchar2, InternalError in varchar2) return varchar2 is
41
42 ErrStr varchar2(2000);
43 begin
44
45 if ErrorCode = -6558 then
46 ErrStr := 'Error in ' || Module || ' Message Exceeded Pipe Limit (4096 bytes). Command being Sent: ' ||
47 substr(Command,1,1500);
48 elsif ErrorCode = -6556 then
49 ErrStr := 'Error in ' || Module || ' No Item to UNPACK for Response: ' || HandShake || ' Last Command Sent: '
50 || substr(Command, 1, 1500);
51 elsif ErrorCode = -6559 then
52 ErrStr := 'Error in ' || Module || ' Wrong Data Type to UNPACK for Response: ' || HandShake ||
53 ' Last Command Sent: ' || substr(Command, 1, 1500);
54 else
55 if HandShake is null then
56 ErrStr := 'Unhandled Exception in ' || Module || ' Error: ' || substr(InternalError, 1, 700) ||
57 ' Pipe Operation: ' || PipeOperation || ' Command being Sent: ' || substr(Command, 1, 700);
58 else
59 ErrStr := 'Unhandled Exception in ' || Module || ' Error: ' || substr(InternalError, 1, 700) ||
60 ' Pipe Operation: ' || PipeOperation || ' Response: ' || HandShake || ' Command being Sent: '
61 || substr(Command, 1, 600);
62
63 end if;
64
65 end if;
66
67 return ErrStr;
68
69 end HandlePipeErrors;
70
71 Function HandleMessageErrors (ErrorCode in number, InstanceID in number,Module in varchar2, HandShake varchar2,
72 PipeName in varchar2, Timeout in number, LogFlag in number,
73 Command varchar2 , LogCommand in varchar2, Response in varchar2,
74 LogResponse in varchar2, InternalError in varchar2) return varchar2
75 is
76 ErrStr varchar2(2000);
77 l_LogResp varchar2(300) := 'CANNOT Log NE Responses due to security reasons. Please use the Command Sent as the cross reference';
78 l_LogCommand varchar2(32767);
79 l_Response varchar2(32767);
80 begin
81
82 if LogFlag > 0 then
83 l_LogCommand := LogCommand;
84 else
85 l_LogCommand := Command;
86 end if;
87
88 if LogResponse = 'Y' then
89 l_Response := l_LogResp;
90 else
91 l_Response := Response;
92 end if;
93
94 if ErrorCode = -2001 then
95 -- ParamValueException;
96 null;
97
98 elsif ErrorCode = -20021 then
99 ErrStr := 'Error in ' || Module || ' Got exception when logging into the command audit trail for ' ||
100 ' Worklist id: ' || InstanceID || ' Message Received: ' || Handshake || ' Command Sent: ' ||
101 substr(l_LogCommand,1, 400) || ' Response: ' || substr(l_Response, 1, 400) || ' Error: ' ||
102 substr(InternalError, 1, 400);
103
104 elsif ErrorCode = -20101 then
105 ErrStr := 'Error in ' || Module || ' Error when sending ACK for ' || HandShake ||
106 ' Error: ' || substr(ErrStr, 1, 600) || ' Last Command sent: ' || substr(l_LogCommand, 1, 400) ||
107 ' Response: ' || substr(l_Response,1,400);
108
109 elsif ErrorCode = -20102 then
110 ErrStr := 'Error in ' || Module || ' Message Timedout when waiting for an ACK for ' || HandShake ||
111 ' Timeout period: ' || to_char(Timeout) || ' Last Command sent: ' || substr(l_LogCommand, 1, 600) ||
112 ' Error: ' || substr(InternalError, 1, 600);
113
114 elsif ErrorCode = -20103 then
115 ErrStr := 'Error in ' || Module || ' Wait for Message Timedout. Last Message: ' || HandShake ||
116 ' Timeout period: ' || to_char(Timeout) || ' Last Command sent: ' || substr(l_LogCommand, 1, 600) ||
117 ' Error: ' || substr(InternalError, 1, 600);
118
119 elsif ErrorCode = -20104 then
120 -- e_PipeSendMesgException
121 null;
122
123 elsif ErrorCode = -20105 then
124 -- e_PipePackMesgException
125 null;
126
127 elsif ErrorCode = -20106 then
128 --e_PipeUnpackMesgException
129 null;
130
131 elsif ErrorCode = -20107 then
132 ErrStr := 'Error in ' || Module || ' Pipes found in an inconsistent state. Got unexpected ' || HandShake ||
133 ' SYNC message sent ';
134
135 elsif ErrorCode = -20300 then
136 ErrStr:= 'Error in ' || Module || ' Got NE_FAILURE Message. Last Command Sent: ' ||
137 substr(l_LogCommand, 1, 600) || ' Failure Message: ' || substr(InternalError, 1, 600);
138
139 elsif ErrorCode = -20500 then
140 ErrStr:= 'Warning in ' || Module || ' Got NE_WARNING Message. Last Command Sent: ' ||
141 substr(l_LogCommand, 1, 600) || ' Warning Message: ' || substr(InternalError, 1, 600);
142
143 elsif ErrorCode = -20610 then
144 ErrStr:= 'Error in ' || Module || ' Got SESSION_LOST Message. Last Command Sent: ' ||
145 substr(l_LogCommand, 1, 600) || ' Error Message: ' || substr(InternalError, 1, 600);
146
147 elsif ErrorCode = -20620 then
148 ErrStr:= 'Warning in ' || Module || ' Got NE_TIMEDOUT Message. Last Command Sent: ' ||
149 substr(l_LogCommand, 1, 600) || ' Warning Message: ' || substr(InternalError, 1, 600);
150 else
151 ErrStr := 'Unhandled Exception in ' || Module || 'Pipe: ' || PipeName || ' SQLCODE: ' ||
152 ErrorCode || ' Error: ' || substr(InternalError,1,1500);
153 end if;
154
155
156 return ErrStr;
157
158 end HandleMessageErrors;
159
160 /*
161 Procedure LogAuditTrail(LogFlag in number, FAInstanceID in number, FEName in varchar2,
162 FeType in varchar2, SWGeneric in varchar2, LogCmd in varchar2,
163 ActualStr in varchar2, Response in varchar2, LogResp in varchar2,
164 ProcName in varchar2, ErrCode OUT NOCOPY number, ErrStr OUT NOCOPY varchar2)
165 is
166 begin
167
168 LogAuditTrail(LogFlag, FAInstanceID, FEName, FeType, SWGeneric, LogCmd,
169 ActualStr, Response, LogResp, ProcName);
170
171 exception
172 when others then
173 ErrCode := SQLCODE;
174 ErrStr := SQLERRM;
175 end LogAuditTrail;
176 */
177
178 Procedure LogAuditTrail(LogFlag in number, FAInstanceID in number, FEName in varchar2,
179 FeType in varchar2, SWGeneric in varchar2, LogCmd in varchar2,
180 ActualStr in varchar2, Response in varchar2, ResponseLong in CLOB,
181 LogResp in varchar2, ProcName in varchar2)
182 is
183 l_LogResp varchar2(300) := 'CANNOT Log FE Responses due to security reasons. Please use the Command Sent as the cross reference';
184 l_LogCommand varchar2(32767);
185 l_Response varchar2(32767);
186 begin
187
188 if LogFlag > 0 then
189 l_LogCommand := substr(LogCmd,1,3999);
190 else
191 l_LogCommand := ActualStr;
192 end if;
193
194 if LogResp = 'Y' then
195 l_Response := l_LogResp;
196 else
197 l_Response := substr(Response,1,32766);
198 end if;
199
200 XDP_AQ_UTILITIES.LogCommandAuditTrail( FAInstanceID, FEName, FeType, SWGeneric, l_LogCommand, sysdate, l_Response, ResponseLong, sysdate, ProcName);
201
202 end LogAuditTrail;
203
204
205 Procedure AppendConnectCommands(Command in varchar2,
206 Response in varchar2)
207 is
208 l_size number;
209 begin
210 l_size := pv_ConnectCommands.count;
211
212 pv_ConnectCommands(l_size + 1).command := Command;
213 pv_ConnectCommands(l_size + 1).response := Response;
214
215 end AppendConnectCommands;
216
217
218 /***************************************************
219 ** These Set of Procedures/Functions are for PROVIIONING PROCEDURE
220 ** GENERATION, EXECUTION etc
221 ***************************************************/
222
223
224
225 /******** Procedure CHECK_FOR_OLD_PARAM ***********/
226 /*
227 * Author: V.Rajaram
228 * Date Created: April 10 1999
229 *
230 * INPUT: parameter name
231 * OUTPUT: parameter name with ".old" stripped off (if any),
232 * : flag indicating if the input parameter name had a ".old"
233 *
234 * This procedure checks if the input parameter has ".old" in it.
235 * If so it strips it out and sets the old_flag to 1 and returns
236 *
237 * Usage: In sdp_find_parameters, sdp_find_replace_procedures procedure.
238 */
239
240 Procedure CHECK_FOR_OLD_PARAM (Param in varchar2,
241 ParamMinusOld OUT NOCOPY varchar2,
242 OldFlag OUT NOCOPY number)
243 is
244
245 l_dummy number;
246 begin
247 l_dummy := INSTR(UPPER(Param),'.OLD',1,1);
248
249 IF l_dummy = 0 then
250 ParamMinusOld := Param;
251 OldFlag := 0;
252 ELSE
253 ParamMinusOld := SUBSTR(Param,1,l_dummy-1);
254 OldFlag := 1;
255 end IF;
256 end CHECK_FOR_OLD_PARAM;
257
258
259
260
261
262
263 /******** Procedure CHECK_PARAM_NAME ***********/
264 /*
265 * Author: V.Rajaram
266 * Date Created: June 12 1998
267 *
268 * INPUT: Service Name, Version, Param Name
269 * OUTPUT: Error Code, Error String
270 *
271 * This procedure takes an SFM Parameter name as input and checks if they have been
272 * defined for the service name, verision
273 *
274 * Usage: In the sdp_find_parameters procedure
275 */
276
277 Procedure CHECK_PARAM_NAME (WorkitemID in number,
278 FAId in number,
279 ParamType in varchar2,
280 Param in varchar2,
281 ErrCode OUT NOCOPY number,
282 ErrStr OUT NOCOPY varchar2)
283 is
284 l_ParamCount number := 0;
285
286 begin
287 ErrCode := 0;
288 ErrStr := null;
289
290 if ParamType = 'WORKITEM' then
291 begin
292 select 1 into l_ParamCount from dual
293 where exists ( select LOOKUP_CODE
294 from CSI_LOOKUPS
295 where LOOKUP_CODE = Param
296 and lookup_type = 'CSI_EXTEND_ATTRIB_POOL');
297
298
299 exception
300 when no_data_found then
301 l_ParamCount := 0;
302 when others then
303 RAISE;
304 end;
305
306 elsif ParamType = 'FA' then
307 begin
308 select 1 into l_ParamCount from dual
309 where exists (
310 select XFA.PARAMETER_NAME
311 from XDP_FA_PARAMETERS XFA
312 where FULFILLMENT_ACTION_ID = FAID
313 and XFA.PARAMETER_NAME = Param );
314
315 exception
316 when no_data_found then
317 l_ParamCount := 0;
318 when others then
319 RAISE;
320 end;
321 elsif ParamType = 'ORDER' then
322 l_ParamCount := 1;
323 elsif ParamType = 'LINE' then
324 l_ParamCount := 1;
325 end if;
326
327
328 if l_ParamCount = 0 then
329 ErrCode := -1;
330 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_PARAMETER');
331 FND_MESSAGE.SET_TOKEN('PARAMETER_NAME', Param);
332 FND_MESSAGE.SET_TOKEN('PARAMETER_TYPE', ParamType);
333 ErrStr := FND_MESSAGE.GET;
334 end if;
335
336 exception
337 when others then
338 ErrCode := SQLCODE;
339 ErrStr := 'Unhandeled Exception in XDP_PROC_CTL.CHECK_PARAM_NAME. Error: ' || SUBSTR(SQLERRM,1,200);
340 end CHECK_PARAM_NAME;
341
342
343
344
345 Procedure GET_PARAM_TYPE (Param in varchar2,
346 ParamType OUT NOCOPY varchar2,
347 ActualParam OUT NOCOPY varchar2,
348 ErrCode OUT NOCOPY number,
349 ErrStr OUT NOCOPY varchar2)
350 is
351 l_Dummy number;
352 l_ParamType varchar2(10);
353
354 begin
355 ErrCode := 0;
356 ErrStr := null;
357
358 l_Dummy := INSTR(Param,'.');
359 if l_Dummy > 0 then
360
361 l_ParamType := SUBSTR(Param, 1, l_Dummy-1);
362 if l_ParamType = 'WI' then
363 ParamType := 'WORKITEM';
364 ActualParam := SUBSTR(Param, (l_Dummy + 1), LENGTH(Param));
365 return;
366 end if;
367
368 if l_ParamType = 'FA' then
369 ParamType := 'FA';
370 ActualParam := SUBSTR(Param, (l_Dummy + 1), LENGTH(Param));
371 return;
372 end if;
373
374 if l_ParamType = 'ORDER' then
375 ParamType := 'ORDER';
376 ActualParam := SUBSTR(Param, (l_Dummy + 1), LENGTH(Param));
377 return;
378 end if;
379
380 if l_ParamType = 'LINE' then
381 ParamType := 'LINE';
382 ActualParam := SUBSTR(Param, (l_Dummy + 1), LENGTH(Param));
383 return;
384 end if;
385
386 end if;
387
388 ErrCode := -1;
389 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_PARAMETER_TYPE');
390 FND_MESSAGE.SET_TOKEN('PARAMETER_NAME', l_ParamType);
391 ErrStr := FND_MESSAGE.GET;
392
393 exception
394 when others then
395 ErrCode := SQLCODE;
396 ErrStr := SQLERRM;
397 end GET_PARAM_TYPE;
398
399
400
401
402 /******** Procedure GET_PARAMETER_VALUE ***********/
403 /*
404 * Author: V.Rajaram
405 * Date Created: June 15 1998
406 *
407 * INPUT: workitem_instance_id, parameter name, old parameter flag, service,
408 * version, action code
409 * OUTPUT: parameter value, Err_code, Error String
410 *
411 * This procedure gets the value of the input parameter. If the old parameter flag is
412 * true then the old value of the parameter is returned.
413 * Also if the value is to be determined by executing an external procedure, this
414 * procedure identifies the procedure and executes the procedure dynamically.
415 *
416 * Usage: At Runtime in the sdp_find_replace_param procedure
417 */
418
419 Procedure GET_PARAMETER_VALUE ( OrderID in number,
420 LineItemID in number,
421 WIInstanceID in number,
422 FAInstanceID in number,
423 ParamName in varchar2,
424 ParamType in varchar2,
425 ParamOldFlag in number,
426 ParamValue OUT NOCOPY varchar2,
427 LogFlag OUT NOCOPY boolean,
428 ParamLogValue OUT NOCOPY varchar2,
429 ErrCode OUT NOCOPY number,
430 ErrStr OUT NOCOPY varchar2)
431 IS
432 e_InvalidParamTypeException exception;
433 l_ParamRefValue varchar2(4000);
434 x_progress varchar2(2000);
435 begin
436
437
438 if ParamType = 'WORKITEM' then
439 XDP_ENGINE.GET_WORKITEM_PARAM_VALUE(WIInstanceID,
440 ParamName,
441 ParamValue,
442 l_ParamRefValue,
443 LogFlag,
444 ErrCode,
445 ErrStr);
446
447 if ParamOldFlag = 1 then
448 ParamValue := l_ParamRefValue;
449 end if;
450
451 if LogFlag = FALSE then
452 ParamLogValue := ParamName;
453 else
454 ParamLogValue := ParamValue;
455 end if;
456
457 elsif ParamType = 'FA' then
458
459 XDP_ENGINE.GET_FA_PARAM(FAInstanceID,
460 ParamName,
461 ParamValue,
462 l_ParamRefValue,
463 LogFlag,
464 ErrCode,
465 ErrStr);
466
467 if ErrCode <> 0 then
468 RAISE e_ParamValueException;
469 end if;
470
471 if ParamOldFlag = 1 then
472 ParamValue := l_ParamRefValue;
473 end if;
474
475 if LogFlag = FALSE then
476 ParamLogValue := ParamName;
477 else
478 ParamLogValue := ParamValue;
479 end if;
480
481 elsif ParamType = 'ORDER' then
482 ParamValue := XDP_ENGINE.GET_ORDER_PARAM_VALUE (OrderID,
483 ParamName);
484 elsif ParamType = 'LINE' then
485 ParamValue := XDP_ENGINE.GET_LINE_PARAM_VALUE(LineItemID,
486 ParamName);
487 else
488 RAISE e_InvalidParamTypeException;
489 end if;
490
491 ErrCode := 0;
492 ErrStr := NULL;
493
494 exception
495 when e_InvalidParamTypeException then
496 ErrCode := -1;
497 ErrStr := x_progress;
498 when e_ParamValueException then
499 ErrCode := -20001;
500 ErrStr := x_progress;
501 when others then
502 ErrCode := SQLCODE;
503 ErrStr := 'Unhandeled Exception in XDP_PROC_CTL.GET_PARAMETER_VALUE. Error: ' || SUBSTR(SQLERRM,1,200);
504 end GET_PARAMETER_VALUE;
505
506
507
508
509
510
511 /******** Procedure FIND_PARAMETERS ***********/
512 /*
513 * Author: V.Rajaram
514 * Date Created: June 12 1998
515 *
516 * INPUT: Service Name, Version, Action Code, Input String
517 * OUTPUT: Err_code, Error String
518 *
519 * This procedure takes a long string as input and checks for any SFM parameters.
520 * The parameters are identified by $... The delimiters are " ", ":", ";", ",", "'"
521 * When found it checks if such a parameter has been defined for the service, version,
522 * action. This is done by Sdp_Check_Param_Name procedure.
523 *
524 * Usage: In forms. When the user tries to saves his provisioning program.
525 */
526
527 Procedure FIND_PARAMETERS (FAID in number,
528 WorkitemID number,
529 Str in varchar2,
530 ErrCode OUT NOCOPY varchar2,
531 ErrStr OUT NOCOPY varchar2)
532 is
533 TYPE DELIM_POS IS TABLE OF number INDEX BY BINARY_INTEGER;
534
535 e_CheckParamException exception;
536 e_InvalidParamTypeException exception;
537
538 l_param_w_dollar varchar2(50);
539 l_param_wo_dollar varchar2(50);
540 l_param_wo_old varchar2(50);
541 l_old_flag number;
542 l_save_str varchar2(32767);
543 i number;
544 j number;
545 l_str_len number;
546 l_dollar_found number;
547 l_syntax_error number;
548 l_done number;
549 l_delim DELIM_POS;
550 l_mark number;
551 l_counter number;
552
553 l_ParamType varchar2(50);
554 l_ActualParam varchar2(50);
555 l_dummy number;
556 x_progress varchar2(2000);
557 begin
558
559 ErrCode := 0;
560 ErrStr := null;
561
562 l_save_str := Str;
563 l_done := 0;
564
565 WHILE l_done <> 1 LOOP
566 l_str_len := LENGTH(l_save_str);
567
568 /* Check for "$" character. Indicates sn SFM variable */
569 i := INSTR(l_save_str,'$',1,1);
570
571 IF i = 0 then
572 /* No Variable found return the same string */
573 ErrCode := 0;
574 ErrStr := NULL;
575 l_done := 1;
576 RETURN;
577 ELSE
578 /* A variable found */
579 l_dollar_found := 1;
580 end IF;
581
582 /*
583 * If the $ is the last letter then there is a syntax error
584 */
585 IF i = l_str_len then
586 l_syntax_error := 1;
587 ErrCode := -1;
588 ErrStr := 'Syntax Error in ' || Str || ' No variable found';
589 RETURN;
590 end IF;
591
592 l_save_str := SUBSTR(l_save_str,i,l_str_len);
593
594 /*
595 * Find all the possible delimiters to get the variable name
596 */
597 l_delim(1) := INSTR(l_save_str,',',1,1);
598 l_delim(2) := INSTR(l_save_str,':',1,1);
599 l_delim(3) := INSTR(l_save_str,';',1,1);
600 l_delim(4) := INSTR(l_save_str,' ',1,1);
601 l_delim(5) := INSTR(l_save_str,'''',1,1);
602 l_delim(6) := INSTR(l_save_str,'"',1,1);
603
604 l_mark := -1;
605 j := -1;
606
607 /*
608 * Find the closest delimter from the $ sign
609 */
610 FOR l_counter in 1..6 LOOP
611 IF l_delim(l_counter) > 0 AND j < 0 then
612 j := l_delim(l_counter);
613 l_mark := l_counter;
614 ELSIF l_delim(l_counter) > 0 AND l_delim(l_counter) < j then
615 j := l_delim(l_counter);
616 l_mark := l_counter;
617 end IF;
618 end LOOP;
619
620 /*
621 * Get the parameter
622 */
623 IF j > 0 then
624 l_param_w_dollar := SUBSTR(l_save_str,1,j-1);
625 l_save_str := SUBSTR(l_save_str,j,l_str_len);
626 ELSIF j = -1 then
627 l_param_w_dollar := l_save_str;
628 l_save_str := ' ';
629 end IF;
630
631 l_param_wo_dollar := SUBSTR(l_param_w_dollar,2,LENGTH(l_param_w_dollar));
632 l_old_flag := 0;
633
634 /*
635 * Check if the parameter has a ".old" in it. If so the procedure strips it off
636 */
637 CHECK_FOR_OLD_PARAM(l_param_wo_dollar,l_param_wo_old,l_old_flag);
638
639 GET_PARAM_TYPE(l_param_wo_old, l_ParamType, l_ActualParam, ErrCode, ErrStr);
640
641 if ErrCode <> 0 then
642 ErrStr := ErrStr || ' in ' || l_param_wo_old;
643 Raise e_InvalidParamTypeException;
644 end if;
645
646 /*
647 * Check if the parameter got is a valid parameter for the
648 * service, version, action. If not found error out
649 */
650 CHECK_PARAM_NAME(WorkitemID, FAID, l_ParamType, l_ActualParam , ErrCode, ErrStr);
651 IF ErrCode <> 0 then
652 RAISE e_CheckParamException;
653 end IF;
654 end LOOP;
655 exception
656 when e_CheckParamException then
657 null;
658 when e_InvalidParamTypeException then
659 ErrCode := -1;
660 -- ErrStr := 'Invalid Parameter Type';
661 when e_ProcExecException then
662 ErrCode := -20021;
663 ErrStr := x_progress;
664 when others then
665 ErrCode := SQLCODE;
666 ErrStr := 'Unhandeled Exception in XDP_PROC_CTL.FIND_PARAMETERS. Error: ' || SUBSTR(SQLERRM,1,200);
667 end FIND_PARAMETERS;
668
669
670
671
672
673
674 /******** Procedure FIND_REPLACE_PARAMS ***********/
675 /*
676 * Author: V.Rajaram
677 * Date Created: June 12 1998
678 *
679 * INPUT: WIInstanceID, Input String (command), service, version, action code
680 * OUTPUT: Command str (with params replaced with values) Err_code, Error String
681 *
682 * This procedure is executed at run time by the Send function to get the values of
683 * of the parameters. It takes a long string as input and checks for any SFM parameters.
684 * The parameters are identified by $... The delimiters are " ", ":", ";", ",", "'"
685 * When found it gets the value using Get_Param_Value procedure.
686 *
687 * Usage: At runtime in the Send procedure
688 */
689
690 Procedure FIND_REPLACE_PARAMS ( OrderID in number,
691 LineItemID in number,
692 WorkiteminstanceID in number,
693 FAinstanceID in number,
694 Str in varchar2,
695 CmdStr OUT NOCOPY varchar2,
696 LogFlag OUT NOCOPY number,
697 LogStr OUT NOCOPY varchar2,
698 ErrCode OUT NOCOPY varchar2,
699 ErrStr OUT NOCOPY varchar2)
700
701 IS
702
703 TYPE DELIM_POS IS TABLE OF number INDEX BY BINARY_INTEGER;
704
705 e_ParamValueException exception;
706 e_InvalidParamTypeException exception;
707
708 l_param_w_dollar varchar2(50);
709 l_ActualParam varchar2(50);
710 l_ParamType varchar2(50);
711 l_param_wo_dollar varchar2(50);
712 l_param_wo_old varchar2(50);
713 l_old_flag number;
714 l_save_str varchar2(32767);
715 i number;
716 j number;
717 l_str_len number;
718 l_dollar_found number;
719 l_syntax_error number;
720 l_done number;
721 l_delim DELIM_POS;
722 l_mark number;
723 l_counter number;
724 l_param_value varchar2(4000);
725 l_param_log_value varchar2(4000);
726 l_out_log_flag BOOLEAN;
727
728 x_progress varchar2(2000);
729 begin
730
731 ErrCode := 0;
732 ErrStr := null;
733
734 l_save_str := Str;
735 l_done := 0;
736
737 l_out_log_flag := TRUE;
738 LogFlag := 0;
739
740 WHILE l_done <> 1 LOOP
741 l_str_len := LENGTH(l_save_str);
742 /*
743 * Check for the character "$". It identifies an SFM parameter
744 */
745
746 i := INSTR(l_save_str,'$',1,1);
747 IF i = 0 then
748 /* No Variable found return the same string */
749 ErrCode := 0;
750 ErrStr := NULL;
751 l_done := 1;
752 if l_save_str = ' ' then
753 exit;
754 else
755 CmdStr := CmdStr || l_save_str;
756 LogStr := LogStr || l_save_str;
757 exit;
758 end if;
759 ELSE
760 /* A variable found */
761 l_dollar_found := 1;
762 /* Save the string to the left of the "$" sign in CmdStr */
763 CmdStr := CmdStr || SUBSTR(l_save_str,1,i-1);
764 LogStr := LogStr || SUBSTR(l_save_str,1,i-1);
765 end IF;
766
767 /*
768 * If the $ is the last letter syntax error
769 */
770 IF i = l_str_len then
771 l_syntax_error := 1;
772 ErrCode := -1;
773 ErrStr := 'Syntax Error in ' || Str || ' No variable found';
774 RETURN;
775 end IF;
776
777 l_save_str := SUBSTR(l_save_str,i,l_str_len);
778 /*
779 * Find all the possible delimiters to get the variable name
780 */
781 l_delim(1) := INSTR(l_save_str,',',1,1);
782 l_delim(2) := INSTR(l_save_str,':',1,1);
783 l_delim(3) := INSTR(l_save_str,';',1,1);
784 l_delim(4) := INSTR(l_save_str,' ',1,1);
785 l_delim(5) := INSTR(l_save_str,'''',1,1);
786 l_delim(6) := INSTR(l_save_str,'"',1,1);
787
788 l_mark := -1;
789 j := -1;
790
791 /*
792 * Find the closest delimter from the $ sign
793 */
794 FOR l_counter in 1..6 LOOP
795 IF l_delim(l_counter) > 0 AND j < 0 then
796 j := l_delim(l_counter);
797 l_mark := l_counter;
798 ELSIF l_delim(l_counter) > 0 AND l_delim(l_counter) < j then
799 j := l_delim(l_counter);
800 l_mark := l_counter;
801 end IF;
802 end LOOP;
803
804 /*
805 * Get the parameter
806 */
807 IF j > 0 then
808 l_param_w_dollar := SUBSTR(l_save_str,1,j-1);
809 l_save_str := SUBSTR(l_save_str,j,l_str_len);
810 ELSIF j = -1 then
811 l_param_w_dollar := l_save_str;
812 l_save_str := ' ';
813 end IF;
814
815 l_param_wo_dollar := SUBSTR(l_param_w_dollar,2,LENGTH(l_param_w_dollar));
816
817 l_old_flag := 0;
818
819 /*
820 * Check if the parameter had a ".old" in it.
821 */
822 CHECK_FOR_OLD_PARAM(l_param_wo_dollar,l_param_wo_old,l_old_flag);
823
824 GET_PARAM_TYPE(l_param_wo_old, l_ParamType, l_ActualParam, ErrCode, ErrStr);
825 if ErrCode <> 0 then
826 raise e_InvalidParamTypeException;
827 end if;
828
829 /*
830 * Get the parameter value at run time
831 */
832 GET_PARAMETER_VALUE(OrderID, LineItemID, WorkitemInstanceID, FAInstanceID, l_ActualParam, l_ParamType, l_old_flag, l_param_value, l_out_log_flag, l_param_log_value, ErrCode, ErrStr);
833 IF ErrCode <> 0 then
834 x_progress := 'In XDP_PROC_CTL.FIND_REPLACE_PARAMS Got parameter value exception when evaluating the value of: ' || l_param_wo_dollar || ' Error: ' || SUBSTR(ErrStr, 1, 1500);
835 CmdStr := Str;
836 LogStr := Str;
837 RAISE e_ParamValueException;
838 end IF;
839
840 /*
841 * Replace parameter with its value
842 */
843 CmdStr := CmdStr || l_param_value;
844 IF l_out_log_flag = FALSE then
845 LogStr := LogStr || l_param_log_value;
846 LogFlag := LogFlag + 1;
847 ELSE
848 LogStr := LogStr || l_param_value;
849 end IF;
850
851 end LOOP;
852
853
854
855 ErrCode := 0;
856 ErrStr := null;
857
858 exception
859 when e_ParamValueException then
860 ErrCode := -20001;
861 ErrStr := x_progress;
862 when e_ProcExecException then
863 ErrCode := -20021;
864 ErrStr := x_progress;
865 when others then
866 ErrCode := SQLCODE;
867 ErrStr := 'Unhandeled Exception in XDP_PROC_CTL.FIND_REPLACE_PARAMS. Error: ' || SUBSTR(SQLERRM,1,200);
868 end FIND_REPLACE_PARAMS;
869
870
871 Procedure GENERATE_PROC (ProcName in varchar2,
872 ProcStr in varchar2,
873 CompiledProc OUT NOCOPY varchar2,
874 ErrCode OUT NOCOPY number,
875 ErrStr OUT NOCOPY varchar2)
876
877 IS
878 l_dummy number;
879
880 l_out_str varchar2(32767);
881 l_temp_str varchar2(32767);
882
883 l_count number;
884 l_cnt number;
885 l_done number;
886 l_start number;
887
888 l_final_str varchar2(32767);
889 l_replace_send_str varchar2(500);
890 l_replace_send_http_str varchar2(500);
891 l_replace_resp_str varchar2(500);
892 l_replace_notify_str varchar2(500);
893 l_replace_get_response_str varchar2(500);
894 l_sync_str varchar2(500);
895 l_replace_get_param_str varchar2(500);
896
897 l_str_before_send varchar2(32767);
898 l_str_after_send varchar2(32767);
899 l_send_loc number;
900
901 l_str_before_declare varchar2(32767);
902 l_str_after_declare varchar2(32767);
903 l_declare_loc number;
904
905 begin
906 ErrCode := 0;
907 ErrStr := null;
908
909 l_temp_str := ProcStr;
910
911 /*
912 * Replace user written SEND, RESPONSE_CONTAINS etc... to comply with the actual spec of those
913 * procedures
914 * Remove the user written DECLARE as we will be generating the procedure on the fly
915 *
916 * Form the strings here...
917 */
918
919 l_replace_send_str := 'XDP_PROC_CTL.SEND(order_id, ' || g_new_line ||
920 ' line_item_id, ' || g_new_line ||
921 ' workitem_instance_id, ' || g_new_line ||
922 ' fa_instance_id, ' || g_new_line ||
923 ' db_channel_name, ' || g_new_line ||
924 ' FE_name, ''' ||
925 ProcName ||''', ' || g_new_line ||
926 ' sdp_internal_response, ' || g_new_line ||
927 ' sdp_internal_err_code, ' || g_new_line ||
928 ' sdp_internal_err_str, ';
929
930 l_replace_send_http_str := 'XDP_PROC_CTL.SEND_HTTP(order_id, ' || g_new_line ||
931 ' line_item_id, ' || g_new_line ||
932 ' workitem_instance_id, ' ||g_new_line ||
933 ' fa_instance_id, ' || g_new_line ||
934 ' db_channel_name, ' || g_new_line ||
935 ' FE_name, ''' ||
936 ProcName ||''', ' || g_new_line ||
937 ' sdp_internal_response, '||g_new_line ||
938 ' sdp_internal_err_code, '||g_new_line ||
939 ' sdp_internal_err_str, ';
940
941 l_replace_resp_str := 'XDP_PROC_CTL.RESPONSE_CONTAINS(sdp_internal_response, '||g_new_line;
942
943 l_replace_notify_str := 'XDP_PROC_CTL.NOTIFY_ERROR(sdp_internal_response, '||g_new_line||
944 ' sdp_internal_err_code, ' ||g_new_line||
945 ' sdp_internal_err_str, ';
946
947 l_replace_get_response_str := 'XDP_PROC_CTL.GET_RESPONSE(sdp_internal_response)';
948
949 l_sync_str := 'XDP_PROC_CTL.SEND_SYNC(db_channel_name, ' || g_new_line ||
950 ' fe_name, ' || g_new_line ||
951 ' sdp_internal_err_code, ' || g_new_line ||
952 ' sdp_internal_err_str);';
953
954 l_replace_get_param_str := 'XDP_PROC_CTL.GET_PARAM_VALUE(
955 order_id, ' || g_new_line ||
956 ' line_item_id, ' || g_new_line ||
957 ' workitem_instance_id, '|| g_new_line ||
958 ' fa_instance_id, ';
959
960
961 /*
962 * Replace the strings here...
963 */
964 l_temp_str := REPLACE(l_temp_str, 'SEND(', l_replace_send_str);
965 l_temp_str := REPLACE(l_temp_str, 'SEND_HTTP(', l_replace_send_http_str);
966 l_temp_str := REPLACE(l_temp_str, 'RESPONSE_CONTAINS(', l_replace_resp_str);
967 l_temp_str := REPLACE(l_temp_str, 'NOTIFY_ERROR(', l_replace_notify_str);
968 l_temp_str := REPLACE(l_temp_str, 'GET_RESPONSE', l_replace_get_response_str);
969 l_temp_str := REPLACE(l_temp_str, 'GET_PARAM_VALUE(', l_replace_get_param_str);
970
971 /*
972 * Need to find the DECLARE word location and delete the users declare string (can be case sensitive)
973 */
974
975 l_declare_loc := INSTR(UPPER(l_temp_str), 'DECLARE', 1, 1);
976 IF l_declare_loc <> 0 then
977 l_str_before_declare := SUBSTR(l_temp_str, 1, l_declare_loc - 1);
978 l_str_after_declare := SUBSTR(l_temp_str, l_declare_loc + 7, LENGTH(l_temp_str));
979
980 l_temp_str := l_str_before_declare || ' ' || l_str_after_declare;
981 end IF;
982
983 /*
984 * Construct the procedure
985 */
986 if fnd_profile.defined('XDP_FP_SPEC') then
987 fnd_profile.get('XDP_FP_SPEC', l_final_str);
988 l_final_str := ' Procedure ' || ProcName || ' ( '|| g_new_line || l_final_str || ' ) ' || g_new_line ||
989 'IS ' || g_new_line ||
990 ' sdp_internal_response varchar2(32767); ' || g_new_line || ' ';
991 else
992
993 l_final_str := ' Procedure ' || ProcName || ' ( ' || g_new_line ||
994 ' order_id in number, ' || g_new_line ||
995 ' line_item_id in number, ' || g_new_line ||
996 ' workitem_instance_id in number, ' || g_new_line ||
997 ' fa_instance_id in number, ' || g_new_line ||
998 ' db_channel_name in varchar2, ' || g_new_line ||
999 ' FE_name in varchar2, ' || g_new_line ||
1000 ' FA_item_type in varchar2, ' || g_new_line ||
1001 ' FA_item_key in varchar2, ' || g_new_line ||
1002 ' sdp_internal_err_code out number, ' || g_new_line ||
1003 ' sdp_internal_err_str out varchar2 ' || ' ) ' || g_new_line ||
1004 'IS ' || g_new_line ||
1005 ' sdp_internal_response varchar2(32767); ' || g_new_line || ' ';
1006
1007 end if;
1008
1009 /*
1010 l_final_str := ' Procedure ' || ProcName || ' ( order_id in number, ' || g_new_line ||
1011 ' line_item_id in number, ' || g_new_line ||
1012 ' workitem_instance_id in number, ' || g_new_line ||
1013 ' fa_instance_id in number, ' || g_new_line ||
1014 ' db_channel_name in varchar2, ' || g_new_line ||
1015 ' FE_name in varchar2, ' || g_new_line ||
1016 ' FA_item_type in varchar2, ' || g_new_line ||
1017 ' FA_item_key in varchar2, ' || g_new_line ||
1018 ' sdp_internal_err_code out number, ' || g_new_line ||
1019 ' sdp_internal_err_str out varchar2 ) ' || g_new_line ||
1020 'IS ' || g_new_line ||
1021 ' sdp_internal_response varchar2 (32767); ' || g_new_line ||
1022 ' ';
1023 */
1024
1025 l_final_str := l_final_str || l_temp_str;
1026
1027 /*
1028 * Find the first users's Send string and insert the SEND_SYNC procedure call before the
1029 * first call to send.
1030 */
1031 l_send_loc := INSTR(UPPER(l_final_str), 'XDP_PROC_CTL.SEND(', 1, 1);
1032
1033 IF l_send_loc <> 0 then
1034 l_str_before_send := SUBSTR(l_final_str, 1, l_send_loc -1);
1035 l_str_after_send := SUBSTR(l_final_str, l_send_loc, LENGTH(l_final_str));
1036
1037 l_final_str := l_str_before_send || l_sync_str || l_str_after_send;
1038 end IF;
1039
1040 /*
1041 * Parse the string for any errors. This is the dynamic generation of the procedure
1042 */
1043
1044 CompiledProc := l_final_str;
1045 ErrCode := 0;
1046 ErrStr := 'Successfully generated the stored procedure ' || ProcName;
1047
1048 exception
1049 when others then
1050 ErrCode := SQLCODE;
1051 ErrStr := 'Unhandeled Exception in XDP_PROC_CTL.GENERATE_PROC. Procedure to be generated: ' || ProcName || ' Error: ' || SUBSTR(SQLERRM,1,200);
1052 end GENERATE_PROC;
1053
1054
1055
1056
1057 /******** Procedure GENERATE_PROC ***********/
1058 /*
1059 * Author: V.Rajaram
1060 * Date Created: June 12 1998
1061 *
1062 * INPUT: Procedure Name, Input String (user written procedure)
1063 * OUTPUT: Err_code, Error String
1064 *
1065 * This procedure takes the user written procedure string and the procedure name
1066 * as input and generates the actual procedure dynamically to be executed by Workflow.
1067 * It also generates any syntax errors in the actual PL/SQL block written by the user.
1068 *
1069 * Usage: In forms. After the sdp_find_parameters procedure went successfully.
1070 */
1071
1072 Procedure GENERATE_PROC (ProcName in varchar2,
1073 ProcStr in varchar2,
1074 ErrCode OUT NOCOPY number,
1075 ErrStr OUT NOCOPY varchar2)
1076 IS
1077 -- PL/SQL Block
1078 l_cursorID number;
1079 l_dummy number;
1080
1081 l_out_str varchar2(32767);
1082 l_temp_str varchar2(32767);
1083
1084 l_count number;
1085 l_cnt number;
1086 l_done number;
1087 l_start number;
1088
1089 l_final_str varchar2(32767);
1090 l_replace_send_str varchar2(500);
1091 l_replace_send_http_str varchar2(500);
1092 l_replace_resp_str varchar2(500);
1093 l_replace_notify_str varchar2(500);
1094 l_replace_get_response_str varchar2(500);
1095 l_sync_str varchar2(500);
1096 l_replace_get_param_str varchar2(500);
1097 --l_replace_connect_str varchar2(500);
1098 --l_replace_disconnect_str varchar2(500);
1099 --l_replace_tempconn_str varchar2(500);
1100
1101 l_str_before_send varchar2(32767);
1102 l_str_after_send varchar2(32767);
1103 l_send_loc number;
1104
1105 l_str_before_declare varchar2(32767);
1106 l_str_after_declare varchar2(32767);
1107 l_declare_loc number;
1108
1109 begin
1110 ErrCode := 0;
1111 ErrStr := null;
1112
1113 l_cursorID := DBMS_SQL.OPEN_CURSOR;
1114
1115
1116 l_temp_str := ProcStr;
1117
1118 /*
1119 * Replace user written SEND, RESPONSE_CONTAINS etc... to comply with the actual spec of those
1120 * procedures
1121 * Remove the user written DECLARE as we will be generating the procedure on the fly
1122 *
1123 * Form the strings here...
1124 */
1125 l_replace_send_str := 'XDP_PROC_CTL.SEND(order_id,
1126 line_item_id,
1127 workitem_instance_id,
1128 fa_instance_id,
1129 db_channel_name,
1130 FE_name, ''' ||
1131 ProcName ||''',
1132 sdp_internal_response,
1133 sdp_internal_err_code, sdp_internal_err_str, ';
1134
1135 l_replace_send_http_str := 'XDP_PROC_CTL.SEND_HTTP(
1136 order_id,
1137 line_item_id,
1138 workitem_instance_id,
1139 fa_instance_id,
1140 db_channel_name,
1141 FE_name, ''' || ProcName ||''',
1142 sdp_internal_response,
1143 sdp_internal_err_code,
1144 sdp_internal_err_str, ';
1145
1146 l_replace_resp_str := 'XDP_PROC_CTL.RESPONSE_CONTAINS(sdp_internal_response, ';
1147
1148 l_replace_notify_str := 'XDP_PROC_CTL.NOTIFY_ERROR(sdp_internal_response,
1149 sdp_internal_err_code,
1150 sdp_internal_err_str, ';
1151
1152 l_replace_get_response_str := 'XDP_PROC_CTL.GET_RESPONSE(sdp_internal_response)';
1153
1154 l_sync_str := 'XDP_PROC_CTL.SEND_SYNC(db_channel_name,
1155 fe_name,
1156 sdp_internal_err_code,
1157 sdp_internal_err_str);';
1158
1159 l_replace_get_param_str := 'XDP_PROC_CTL.GET_PARAM_VALUE(order_id,
1160 line_item_id,
1161 workitem_instance_id,
1162 fa_instance_id, ';
1163
1164
1165 /*
1166 * Replace the strings here...
1167 */
1168 l_temp_str := REPLACE(l_temp_str, 'SEND(', l_replace_send_str);
1169 l_temp_str := REPLACE(l_temp_str, 'SEND_HTTP(', l_replace_send_http_str);
1170 l_temp_str := REPLACE(l_temp_str, 'RESPONSE_CONTAINS(', l_replace_resp_str);
1171 l_temp_str := REPLACE(l_temp_str, 'NOTIFY_ERROR(', l_replace_notify_str);
1172 l_temp_str := REPLACE(l_temp_str, 'GET_RESPONSE', l_replace_get_response_str);
1173 l_temp_str := REPLACE(l_temp_str, 'GET_PARAM_VALUE(', l_replace_get_param_str);
1174
1175 /*
1176 * Need to find the DECLARE word location and delete the users declare string (can be case sensitive)
1177 */
1178
1179 l_declare_loc := INSTR(UPPER(l_temp_str), 'DECLARE', 1, 1);
1180 IF l_declare_loc <> 0 then
1181 l_str_before_declare := SUBSTR(l_temp_str, 1, l_declare_loc - 1);
1182 l_str_after_declare := SUBSTR(l_temp_str, l_declare_loc + 7, LENGTH(l_temp_str));
1183
1184 l_temp_str := l_str_before_declare || ' ' || l_str_after_declare;
1185 end IF;
1186
1187
1188 /*
1189 * Construct the procedure
1190 */
1191 l_final_str := 'CREATE OR REPLACE Procedure ' || ProcName || ' (
1192 order_id in number,
1193 line_item_id in number,
1194 workitem_instance_id in number,
1195 fa_instance_id in number,
1196 db_channel_name in varchar2,
1197 FE_name in varchar2,
1198 FA_item_type in varchar2,
1199 FA_item_key in varchar2,
1200 sdp_internal_err_code out number,
1201 sdp_internal_err_str out varchar2)
1202 IS
1203 sdp_internal_response varchar2 (32767);
1204 ';
1205
1206 l_final_str := l_final_str || l_temp_str;
1207
1208 /*
1209 * Find the first users's Send string and insert the SEND_SYNC procedure call before the
1210 * first call to send.
1211 */
1212 l_send_loc := INSTR(UPPER(l_final_str), 'XDP_PROC_CTL.SEND(', 1, 1);
1213
1214 IF l_send_loc <> 0 then
1215 l_str_before_send := SUBSTR(l_final_str, 1, l_send_loc -1);
1216 l_str_after_send := SUBSTR(l_final_str, l_send_loc, LENGTH(l_final_str));
1217
1218 l_final_str := l_str_before_send || l_sync_str || l_str_after_send;
1219 end IF;
1220
1221
1222 /*
1223 * Parse the string for any errors. This is the dynamic generation of the procedure
1224 */
1225 DBMS_SQL.PARSE(l_cursorID, l_final_str, DBMS_SQL.V7);
1226
1227 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
1228 COMMIT;
1229 ErrCode := 0;
1230 ErrStr := 'Successfully generated the stored procedure ' || ProcName;
1231
1232 exception
1233 when others then
1234 ErrCode := SQLCODE;
1235 ErrStr := 'Unhandeled Exception in XDP_PROC_CTL.GENERATE_PROC. Procedure to be generated: ' || ProcName || ' Error: ' || SUBSTR(SQLERRM,1,200);
1236 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
1237 end GENERATE_PROC;
1238
1239
1240
1241
1242
1243 /******** Procedure SHOW_PROC_ERRORS ***********/
1244 /*
1245 * Author: V.Rajaram
1246 * Date Created: Sept 4 1998
1247 *
1248 * INPUT: Procedure Name (Users procedure)
1249 * OUTPUT: Error text, Err_code, Error String
1250 *
1251 * This procedure takes the user written procedure name
1252 * as input and lists all the errors when generating the procedure
1253 *
1254 * Usage: In forms. When trying to generate the provisioning procedure
1255 */
1256
1257 Procedure SHOW_PROC_ERRORS (ProcName in varchar2,
1258 ErrCode OUT NOCOPY number,
1259 Errors OUT NOCOPY varchar2)
1260 IS
1261
1262 CURSOR c_GetAllErrors(p_ProcName varchar2) IS
1263 SELECT SUBSTR(text,1,80)
1264 FROM user_errors
1265 WHERE UPPER(name) = UPPER(p_ProcName)
1266 and sequence < 3
1267 order by line;
1268
1269 temp_err varchar2(100);
1270
1271 begin
1272
1273 ErrCode := 0;
1274 ERRORS := null;
1275
1276 OPEN c_GetAllErrors(ProcName);
1277 LOOP
1278 FETCH c_GetAllErrors into temp_err;
1279 EXIT when c_GetAllErrors%NOTFOUND;
1280
1281 ERRORS := ERRORS || '
1282 ' || temp_err;
1283 end LOOP;
1284
1285 IF ERRORS IS NULL then
1286 ERRORS := 'No Errors';
1287 end IF;
1288
1289 IF c_GetAllErrors%ISOPEN then
1290 CLOSE c_GetAllErrors;
1291 end IF;
1292
1293
1294 exception
1295 when others then
1296 IF c_GetAllErrors%ISOPEN then
1297 CLOSE c_GetAllErrors;
1298 end IF;
1299
1300 ErrCode := SQLCODE;
1301 ERRORS := 'Unhandeled Exception in XDP_PROC_CTL.SHOW_PROC_ERRORS. for Procedure ' || ProcName || ' Error: ' || SUBSTR(SQLERRM,1,200);
1302 end SHOW_PROC_ERRORS;
1303
1304
1305
1306 /******** Procedure GET_ADAPTER_TOTAL_TIMEOUT ***********/
1307 /*
1308 * Author: V.Rajaram
1309 * Date Created: August 11 1998
1310 *
1311 * INPUT: NE Name, NE Type, SW Generic
1312 * OUTPUT: Total Timeout , Err_code, Error String
1313 *
1314 * This is the procedure is used to find out the total timeout to which the Provisining
1315 * procedure waits for the message from the nem
1316 *
1317 * Usage: In Send function for to assign the WAIT Message timeout.
1318 */
1319
1320 Procedure GET_ADAPTER_TOTAL_TIMEOUT (NeID in number,
1321 Timeout OUT NOCOPY number,
1322 ErrCode OUT NOCOPY number,
1323 ErrStr OUT NOCOPY varchar2)
1324 is
1325 l_FeValue varchar2(20);
1326 l_Retries number := 0;
1327 l_CmdTimeout number := 120;
1328 l_RetryWait number := 0;
1329
1330
1331 begin
1332 ErrCode := 0;
1333 ErrStr := null;
1334
1335 Timeout := 0;
1336
1337 begin
1338 l_FeValue := XDP_ENGINE.Get_FE_AttributeVal(NeID, pv_attrFeCmdTimeout);
1339 if l_FeValue is null then
1340 l_CmdTimeout := 120;
1341 else
1342 l_CmdTimeout := to_number(l_FeValue);
1343 end if;
1344 exception
1345 when no_data_found then
1346 l_CmdTimeout := 120;
1347 when others then
1348 RAISE e_ParamValueException;
1349 end;
1350
1351 begin
1352 l_FeValue := XDP_ENGINE.Get_FE_AttributeVal(NeID, pv_attrFeRetryCount);
1353 if l_FeValue is null then
1354 l_Retries := 0;
1355 else
1356 l_Retries := to_number(l_FeValue);
1357 end if;
1358 exception
1359 when no_data_found then
1360 l_Retries := 0;
1361 when others then
1362 RAISE e_ParamValueException;
1363 end;
1364
1365 begin
1366 l_FeValue := XDP_ENGINE.Get_FE_AttributeVal(NeID, pv_attrFeCmdRetryWait);
1367 if l_FeValue is null then
1368 l_RetryWait := 0;
1369 else
1370 l_RetryWait := to_number(l_FeValue);
1371 end if;
1372 exception
1373 when no_data_found then
1374 l_RetryWait := 0;
1375 when others then
1376 RAISE e_ParamValueException;
1377 end;
1378
1379 IF to_number(l_Retries) <> 0 then
1380 Timeout := to_number(l_CmdTimeout) + ( to_number(l_CmdTimeout) + to_number(l_RetryWait) ) * to_number(l_Retries);
1381 ELSE
1382 Timeout := to_number(l_CmdTimeout);
1383 end IF;
1384
1385 IF Timeout = 0 then
1386 Timeout := 120;
1387 end IF;
1388
1389 Timeout := Timeout + 5;
1390
1391 exception
1392 when others then
1393 ErrCode := SQLCODE;
1394 ErrStr := 'Unhandeled Exception in XDP_PROC_CTL.GET_NEM_TOTAL_TIMEOUT. Error: ' || SUBSTR(SQLERRM,1,200);
1395 end GET_ADAPTER_TOTAL_TIMEOUT;
1396
1397
1398
1399
1400 /******** Procedure GET_UNIQUE_CHANNEL_NAME ***********/
1401 /*
1402 * Author: V.Rajaram
1403 * Date Created: June 17 1998
1404 *
1405 * INPUT: ne_name
1406 * OUTPUT: Unique Pipe Name
1407 *
1408 * For each type of NEM this returns a unique pipe name for the communication
1409 *
1410 * Usage: General Utility
1411 */
1412
1413 FUNCTION GET_UNIQUE_CHANNEL_NAME (Name in varchar2)
1414
1415 RETURN varchar2
1416 IS
1417 l_PipeName varchar2(50);
1418 l_Temp varchar2(80);
1419 l_Seq number;
1420 l_SeqLen number;
1421 l_TruncLen number;
1422 begin
1423 l_temp := RTRIM(Name,' ');
1424 l_temp := LTRIM(l_temp);
1425
1426
1427 l_temp := REPLACE(l_Temp, ' ', '_');
1428
1429 SELECT XDP_CHANNEL_S.NEXTVAL into l_Seq
1430 FROM dual;
1431
1432 l_PipeName := to_char(l_Seq);
1433
1434 l_SeqLen := LENGTH(l_PipeName);
1435
1436 l_TruncLen := 30 - (l_SeqLen + 9);
1437
1438 l_PipeName := SUBSTR(l_Temp, 1, l_TruncLen) || '_' || l_PipeName;
1439
1440
1441
1442 RETURN l_PipeName;
1443
1444 end GET_UNIQUE_CHANNEL_NAME;
1445
1446
1447
1448 /******** Procedure LOG_COMMAND_AUDIT_TRAIL ***********/
1449 /*
1450 * Author: V.Rajaram
1451 * Date Created: June 17 1998
1452 *
1453 * INPUT: WIInstanceID, NE name, NE type, SW Generic, command sent,
1454 * date sent, response, response date, procedure name
1455 * OUTPUT: error_code, error string
1456 *
1457 * This procedure logs the sudit trail for each command sent to the NE
1458 *
1459 * Usage: In Send procedure at run time.
1460 */
1461
1462 Procedure LOG_COMMAND_AUDIT_TRAIL (FAInstanceID in number,
1463 FeName in varchar2,
1464 FeType in varchar2,
1465 SW_Generic in varchar2,
1466 CommandSent in varchar2,
1467 SentDate in DATE,
1468 Response in varchar2,
1469 RespDate in DATE,
1470 ProcName in varchar2,
1471 ErrCode OUT NOCOPY number,
1472 ErrStr OUT NOCOPY varchar2)
1473 IS
1474 l_response_id number;
1475 l_FeName varchar2(80);
1476 l_FeType varchar2(80);
1477 l_SW_Generic varchar2(80);
1478 l_CommandSent varchar2(32767);
1479 l_Response varchar2(32767);
1480 begin
1481 ErrCode := 0;
1482 ErrStr := null;
1483
1484
1485 IF FeName IS NULL then
1486 l_FeName := 'GOT NULL FE NAME';
1487 ELSE
1488 l_FeName := FeName;
1489 end IF;
1490
1491 IF FeType IS NULL then
1492 l_FeType := 'GOT NULL FE TYPE';
1493 ELSE
1494 l_FeType := FeType;
1495 end IF;
1496
1497 IF SW_Generic IS NULL then
1498 l_SW_Generic := 'GOT NULL SW_GENERIC';
1499 ELSE
1500 l_SW_Generic := SW_Generic;
1501 end IF;
1502
1503 IF CommandSent IS NULL then
1504 l_CommandSent := 'GOT NULL Command to be sent';
1505 ELSE
1506 l_CommandSent := CommandSent;
1507 end IF;
1508
1509 IF Response IS NULL then
1510 l_Response := 'GOT NULL Response from the NE';
1511 ELSE
1512 l_Response := Response;
1513 end IF;
1514
1515 /*
1516 * Insert into the audit trail table.
1517 */
1518
1519 INSERT INTO XDP_FE_CMD_AUD_TRAILS (
1520 fa_instance_id,
1521 fe_command_seq,
1522 fulfillment_element_name,
1523 fulfillment_element_type,
1524 sw_generic,
1525 command_sent,
1526 command_sent_date,
1527 response,
1528 response_date,
1529 provisioning_procedure)
1530 VALUES (
1531 FAInstanceID,
1532 XDP_FE_CMD_AUD_TRAILS_S.NEXTVAL,
1533 l_FeName,
1534 l_FeType,
1535 l_Sw_Generic,
1536 l_CommandSent,
1537 SentDate,
1538 SUBSTR(l_Response,1,3999),
1539 RespDate,
1540 ProcName);
1541
1542
1543 exception
1544 when others then
1545 ErrCode := SQLCODE;
1546 ErrStr := 'Unhandeled Exception in XDP_PROC_CTL.LOG_COMMAND_AUDIT_TRAIL. Error: ' || SUBSTR(SQLERRM,1,200);
1547 end LOG_COMMAND_AUDIT_TRAIL;
1548
1549
1550
1551 /******** Procedure SEND_ACK ***********/
1552 /*
1553 * Author: V.Rajaram
1554 * Date Created: June 19 1998
1555 *
1556 * INPUT: pipe_name, timeout
1557 * OUTPUT: Err_code, Error String
1558 *
1559 * This procedure sends 'ACK_NEM" message over the input pipe to the NEM
1560 *
1561 * Usage: Gereral Utility
1562 */
1563
1564 Procedure SEND_ACK (ChannelName in varchar2,
1565 Timeout in number,
1566 ErrCode OUT NOCOPY number,
1567 ErrStr OUT NOCOPY varchar2)
1568 IS
1569 e_PipeException exception;
1570 l_ReturnCode number;
1571 begin
1572 ErrCode := 0;
1573 ErrStr := null;
1574
1575 begin
1576 DBMS_PIPE.PACK_MESSAGE('ACK_SDP');
1577 l_ReturnCode := DBMS_PIPE.SEND_MESSAGE(ChannelName,Timeout);
1578 END;
1579
1580 IF l_ReturnCode <> 0 then
1581 RAISE e_PipeException;
1582 end IF;
1583
1584 exception
1585 when e_PipeException then
1586 ErrCode := -20101;
1587 ErrStr := 'SFM -20101 Could NOT send ACK message to the NEM';
1588 when others then
1589 ErrCode := SQLCODE;
1590 ErrStr := 'Unhandeled Exception in XDP_PROC_CTL.SEND_ACK. Error: ' || SUBSTR(SQLERRM,1,200);
1591 end SEND_ACK;
1592
1593
1594
1595
1596
1597 /******** Procedure WAIT_FOR_MESSAGE ***********/
1598 /*
1599 * Author: V.Rajaram
1600 * Date Created: June 19 1998
1601 *
1602 * INPUT: pipe_name, timeout
1603 * OUTPUT: message, Err_code, Error String
1604 *
1605 * This procedure waits for a message over the pipe and returns the message
1606 *
1607 * Usage: Gereral Utility
1608 */
1609
1610 Procedure WAIT_FOR_MESSAGE (ChannelName in varchar2,
1611 Timeout in number,
1612 Message OUT NOCOPY varchar2,
1613 ErrCode OUT NOCOPY number,
1614 ErrStr OUT NOCOPY varchar2)
1615 IS
1616 e_PipeException exception;
1617 e_InternalPipeException exception;
1618 l_ReturnCode number;
1619 begin
1620 ErrCode := 0;
1621 ErrStr := null;
1622
1623 /*
1624 * Wait till you receive a message
1625 */
1626 l_ReturnCode := DBMS_PIPE.RECEIVE_MESSAGE(ChannelName,Timeout);
1627
1628 /*
1629 * Got timeout on RECEIVE_MESSAGE
1630 */
1631
1632 IF l_ReturnCode = 1 then
1633 RAISE e_PipeException;
1634 ELSIF l_ReturnCode <> 0 then
1635 /* Internal Error */
1636 RAISE e_InternalPipeException;
1637 ELSE
1638 DBMS_PIPE.UNPACK_MESSAGE(Message);
1639 end IF;
1640
1641
1642 exception
1643 when e_PipeException then
1644 ErrCode := -20103;
1645 ErrStr := 'SFM-20103 Could not get Response from NEM on Pipe: ' || ChannelName || ' Timeout: ' || TIMEOUT;
1646 when e_InternalPipeException then
1647 ErrCode := -20103;
1648 ErrStr := 'SFM-20103 Unknown error when waiting for response from NEM on Pipe: ' || ChannelName || ' Timeout: ' || TIMEOUT;
1649 when others then
1650 ErrCode := SQLCODE;
1651 ErrStr := 'Unhandeled Exception in XDP_PROC_CTL.WAIT_FOR_MESSAGE. Error: ' || SUBSTR(SQLERRM,1,200);
1652 end WAIT_FOR_MESSAGE;
1653
1654
1655
1656 /******** Procedure Send ***********/
1657 /*
1658 * Author: V.Rajaram
1659 * Date Created: June 12 1998
1660 *
1661 * INPUT: workitem_instance_id, pipe name, procedure name, command string
1662 * OUTPUT: response, Err_code, Error String
1663 *
1664 * This is the procedure to which the user written SEND's are mapped to.
1665 * The procedure sends the users commands to the NEM through the input pipe name.
1666 * Before doing this it calls the find_replace_procedure procedure to get the
1667 * parameter value at runtime. hence needs the workitem_instance_id. It manages
1668 * the connection to the NEM
1669 *
1670 * Usage: In forms. This is the procedure the suers SEND's are mapped to.
1671 */
1672
1673 Procedure SEND (OrderID in number,
1674 LineItemID in number,
1675 WIInstanceID in number,
1676 FAInstanceID in number,
1677 ChannelName in varchar2,
1678 FEName in varchar2,
1679 ProcName in varchar2,
1680 Response OUT NOCOPY varchar2,
1681 sdp_internal_err_code OUT NOCOPY number,
1682 sdp_internal_err_str OUT NOCOPY varchar2,
1683 CmdStr in varchar2,
1684 EncryptFlag in varchar2,
1685 Prompt in varchar2,
1686 ErrCode OUT NOCOPY number,
1687 ErrStr OUT NOCOPY varchar2)
1688
1689 IS
1690
1691 -- PL/SQL Block
1692
1693 l_ReturnCode number;
1694 l_ReturnChannelName varchar2(50);
1695 l_ApplChannelName varchar2(50);
1696 l_Handshake varchar2(20);
1697 l_ActualStr varchar2(32767);
1698
1699 l_TempResponse varchar2(32767);
1700 l_RespCount number;
1701 l_Counter number;
1702
1703 /*
1704 * Dec 15 1999
1705 * The Prompt value was 50 now making it 4000
1706 * Should we make it 32767?
1707 */
1708
1709 l_PromptValue varchar2(4000);
1710
1711
1712 l_LogFlag number;
1713 l_LogCmd varchar2(32767);
1714 l_LogResp varchar2(32767);
1715
1716 l_FeWarning varchar2(32767);
1717 l_NeSessionLost varchar2(32767);
1718 l_NeTimedOut varchar2(32767);
1719 l_AdapterType varchar2(80);
1720
1721 l_RespXML varchar2(32767);
1722 l_MoreFlag varchar2(10);
1723 l_Status varchar2(40);
1724
1725 l_Templob CLOB;
1726 x_progress varchar2(2000);
1727 begin
1728
1729 sdp_internal_err_code := 0;
1730 sdp_internal_err_str := null;
1731
1732 ErrCode := 0;
1733 ErrStr := null;
1734
1735 RESPONSE := ' ';
1736
1737 IF EncryptFlag = 'Y' then
1738 l_LogResp := 'CANNOT Log NE Responses due to security reasons. Please use the Command Sent as the cross reference';
1739 ELSE
1740 null;
1741 end IF;
1742
1743
1744 /*
1745 * find and replace any parameters with their value
1746 */
1747 FIND_REPLACE_PARAMS(OrderID, LineItemID, WIInstanceID, FAInstanceID, CmdStr, l_ActualStr, l_LogFlag, l_LogCmd, ErrCode, ErrStr);
1748 IF ErrCode <> 0 then
1749 x_progress := 'In XDP_PROC_CTL.SEND.FIND_REPLACE_PARAMS: Error when trying to get the value of parameters in: ' || CmdStr || ' Error: ' || SUBSTR(ErrStr, 1, 1500);
1750 RAISE e_ParamValueException;
1751 end IF;
1752
1753 l_PromptValue := PROMPT;
1754 IF Prompt IS NULL then
1755 l_PromptValue := 'IGNORE';
1756 end IF;
1757
1758
1759 l_ApplChannelName := XDP_ADAPTER_CORE_PIPE.ConstructChannelName
1760 (p_ChannelType => 'APPL',
1761 p_ChannelName => ChannelName);
1762
1763 l_ReturnChannelName := XDP_ADAPTER_CORE_PIPE.GetReturnChannelName
1764 (p_ChannelName => l_ApplChannelName);
1765
1766 begin
1767 DBMS_LOB.createtemporary(l_TempLob, TRUE);
1768 xdp_adapter.pv_AdapterExitCode := null;
1769
1770 -- dbms_output.put_line('Sending: ' || substr(l_ActualStr, 1, 200));
1771 -- dbms_output.put_line('Prompt: ' || substr(l_PromptValue, 1, 200));
1772
1773 XDP_ADAPTER_CORE.SendApplicationMessage(p_ChannelName => l_ApplChannelName,
1774 p_Command => l_ActualStr,
1775 p_Response => l_PromptValue);
1776
1777 -- dbms_output.put_line('Waiting for response...');
1778 -- dbms_output.put_line('Timeout: ' || to_char(pv_MesgTimeout));
1779 XDP_ADAPTER_CORE.WaitForMessage(p_ChannelName => l_ReturnChannelName,
1780 p_Timeout => pv_MesgTimeout + 5,
1781 p_ResponseMessage => l_RespXML);
1782
1783 l_Status := XDP_ADAPTER_CORE_XML.DecodeMessage(p_WhattoDecode => 'STATUS',
1784 p_XMLMessage => l_RespXML);
1785
1786 -- dbms_output.put_line('Status: ' || l_Status);
1787
1788 -- dbms_output.put_line('Sending ACK');
1789 XDP_ADAPTER_CORE.SendAck(p_ChannelName => l_ApplChannelName);
1790
1791 -- dbms_output.put_line('After Sending ACK');
1792 xdp_adapter.pv_AdapterExitCode := XDP_ADAPTER_CORE_XML.DecodeMessage
1793 (p_WhattoDecode => 'EXIT_CODE',
1794 p_XMLMessage => l_RespXML);
1795
1796 -- dbms_output.put_line('Exit Code: ' || xdp_adapter.pv_AdapterExitCode);
1797 if (l_Status = 'SUCCESS') then
1798
1799 l_MoreFlag := XDP_ADAPTER_CORE_XML.DecodeMessage(
1800 p_WhattoDecode => 'MORE_FLAG',
1801 p_XMLMessage => l_RespXML);
1802
1803 -- dbms_output.put_line('More Flag : ' || l_MoreFlag);
1804 Response := XDP_ADAPTER_CORE_XML.DecodeMessage(p_WhattoDecode => 'DATA',
1805 p_XMLMessage => l_RespXML);
1806
1807 if Response is not null then
1808 -- dbms_output.put_line('After Reponse' || length(Response));
1809 dbms_lob.writeappend(l_TempLob, length(Response), Response);
1810 end if;
1811 -- dbms_output.put_line('After Logging..');
1812
1813 while (l_MoreFlag is not null and l_MoreFlag = 'Y' ) loop
1814
1815 -- dbms_output.put_line('Waiting for message(LOOP)');
1816 -- dbms_output.put_line('Timeout: ' || to_char(pv_MesgTimeout));
1817 XDP_ADAPTER_CORE.WaitForMessage(
1818 p_ChannelName => l_ReturnChannelName,
1819 p_Timeout => pv_MesgTimeout + 5,
1820 p_ResponseMessage => l_RespXML);
1821
1822 l_Status := XDP_ADAPTER_CORE_XML.DecodeMessage(
1823 p_WhattoDecode => 'STATUS',
1824 p_XMLMessage => l_RespXML);
1825 -- dbms_output.put_line('Status: (LOOP) ' || l_Status);
1826
1827 -- dbms_output.put_line('Sending ACK (LOOP)');
1828 XDP_ADAPTER_CORE.SendAck(p_ChannelName => l_ApplChannelName);
1829 -- dbms_output.put_line('After Sending ACK (LOOP)');
1830
1831 xdp_adapter.pv_AdapterExitCode := XDP_ADAPTER_CORE_XML.DecodeMessage
1832 (p_WhattoDecode => 'EXIT_CODE',
1833 p_XMLMessage => l_RespXML);
1834
1835 -- dbms_output.put_line('Exit Code:(LOOP) ' || xdp_adapter.pv_AdapterExitCode);
1836 if (l_Status = 'SUCCESS' ) then
1837
1838 l_MoreFlag := XDP_ADAPTER_CORE_XML.DecodeMessage(
1839 p_WhattoDecode => 'MORE_FLAG',
1840 p_XMLMessage => l_RespXML);
1841
1842 -- dbms_output.put_line('More Flag: (LOOP) ' || l_MoreFlag);
1843 l_TempResponse := XDP_ADAPTER_CORE_XML.DecodeMessage(
1844 p_WhattoDecode => 'DATA',
1845 p_XMLMessage => l_RespXML);
1846
1847 if l_TempResponse is not null then
1848 -- dbms_output.put_line('After Reponse (LOOP)' || length(l_TempResponse));
1849 dbms_lob.writeappend(
1850 l_TempLob, length(l_TempResponse), l_TempResponse);
1851 end if;
1852 -- dbms_output.put_line('After Logging(LOOP)..');
1853
1854 if length(Response) + length(l_TempResponse) <= 32767 then
1855 Response := Response || l_TempResponse;
1856 elsif length(Response) < 32766 then
1857 Response := Response || substr(
1858 l_TempResponse, 1, 32766 - length(Response) );
1859 end if;
1860
1861 else
1862 ErrStr := XDP_ADAPTER_CORE_XML.DecodeMessage(
1863 p_WhattoDecode => 'DATA',
1864 p_XMLMessage => l_RespXML);
1865 exit;
1866 end if;
1867 end loop;
1868
1869 else
1870 ErrStr := XDP_ADAPTER_CORE_XML.DecodeMessage(
1871 p_WhattoDecode => 'DATA',
1872 p_XMLMessage => l_RespXML);
1873 end if;
1874 exception
1875 when others then
1876 -- dbms_output.put_line('EXCEPTION: ' || SQLCODE);
1877 -- Log into the Audit trail the command the response
1878 -- Log the command send irrespective of any errors.
1879 LogAuditTrail(LogFlag => l_LogFlag,
1880 FAInstanceID => FAInstanceID,
1881 FEName => FEName,
1882 FeType => pv_FeType,
1883 SWGeneric => pv_SWGeneric,
1884 LogCmd => l_LogCmd,
1885 ActualStr => l_ActualStr,
1886 Response => Response,
1887 ResponseLong => l_TempLob,
1888 LogResp => l_LogResp,
1889 ProcName => ProcName);
1890
1891 raise;
1892
1893 end;
1894
1895 if (l_Status <> 'SESSION_LOST') then
1896
1897 -- dbms_output.put_line('Sending Sync..');
1898 XDP_ADAPTER_CORE.SendSync(p_ChannelName => l_ApplChannelName,
1899 p_CleanupPipe => 'N');
1900 -- dbms_output.put_line('After Sending Sync..');
1901
1902 END IF;
1903
1904 -- Log the Response
1905 LogAuditTrail(LogFlag => l_LogFlag,
1906 FAInstanceID => FAInstanceID,
1907 FEName => FEName,
1908 FeType => pv_FeType,
1909 SWGeneric => pv_SWGeneric,
1910 LogCmd => l_LogCmd,
1911 ActualStr => l_ActualStr,
1912 Response => Response,
1913 ResponseLong => l_TempLob,
1914 LogResp => l_LogResp,
1915 ProcName => ProcName);
1916
1917 if l_Status = 'SUCCESS' then
1918 null;
1919 elsif (l_Status = 'FAILURE') then
1920 x_Progress := HandleMessageErrors
1921 (xdp_adapter.pv_AdapterFailure,NULL, 'XDP_PROC_CTL.SEND', 'RESP',
1922 l_ReturnChannelName, pv_MesgTimeout, l_LogFlag,
1923 l_ActualStr, l_LogCmd, Response, EncryptFlag, ErrStr);
1924 RAISE e_NeFailureException;
1925
1926 elsif (l_Status = 'TIMEOUT') then
1927 x_Progress := HandleMessageErrors
1928 (xdp_adapter.pv_AdapterTimeout,NULL, 'XDP_PROC_CTL.SEND', 'RESP',
1929 l_ReturnChannelName, pv_MesgTimeout, l_LogFlag,
1930 l_ActualStr, l_LogCmd, Response, EncryptFlag, ErrStr);
1931 RAISE e_NeTimedOutException;
1932
1933 elsif (l_Status = 'WARNING') then
1934 x_Progress := HandleMessageErrors(
1935 xdp_adapter.pv_AdapterWarning, NULL, 'XDP_PROC_CTL.SEND', 'RESP',
1936 l_ReturnChannelName, pv_MesgTimeout, l_LogFlag,
1937 l_ActualStr, l_LogCmd, Response, EncryptFlag, ErrStr);
1938 RAISE e_NeWarningException;
1939
1940 elsif (l_Status = 'SESSION_LOST') then
1941 x_Progress := HandleMessageErrors(
1942 xdp_adapter.pv_AdapterSessionLost,NULL,'XDP_PROC_CTL.SEND','RESP',
1943 l_ReturnChannelName, pv_AckTimeout, l_LogFlag,
1944 l_ActualStr, l_LogCmd, Response, EncryptFlag, ErrStr);
1945 RAISE e_NeSessionLostException;
1946
1947 else
1948 RAISE e_UnhandledException;
1949 end if;
1950
1951 DBMS_LOB.freetemporary(l_TempLob);
1952
1953 exception
1954 when e_ParamValueException then
1955 ErrCode := -20001;
1956 ErrStr := x_progress;
1957 sdp_internal_err_code := ErrCode;
1958 sdp_internal_err_str := ErrStr;
1959 DBMS_LOB.freetemporary(l_TempLob);
1960
1961 RAISE_APPLICATION_ERROR(-20001, x_progress);
1962
1963 when e_ProcExecException then
1964 ErrCode := -20021;
1965 ErrStr := x_progress;
1966 sdp_internal_err_code := ErrCode;
1967 sdp_internal_err_str := ErrStr;
1968 DBMS_LOB.freetemporary(l_TempLob);
1969
1970 RAISE_APPLICATION_ERROR(-20021, x_progress);
1971
1972 when e_PipeSendAckException then
1973 ErrCode := -20101;
1974 ErrStr := x_progress;
1975 sdp_internal_err_code := ErrCode;
1976 sdp_internal_err_str := ErrStr;
1977 DBMS_LOB.freetemporary(l_TempLob);
1978
1979 RAISE_APPLICATION_ERROR(-20101, x_progress);
1980
1981 when e_PipeWaitForAckException then
1982 ErrCode := -20102;
1983 ErrStr := x_progress;
1984 sdp_internal_err_code := ErrCode;
1985 sdp_internal_err_str := ErrStr;
1986 DBMS_LOB.freetemporary(l_TempLob);
1987 RAISE_APPLICATION_ERROR(-20102, x_progress);
1988
1989 when e_PipeWaitForMesgException then
1990 ErrCode := -20103;
1991 ErrStr := x_progress;
1992 sdp_internal_err_code := ErrCode;
1993 sdp_internal_err_str := ErrStr;
1994 DBMS_LOB.freetemporary(l_TempLob);
1995
1996 RAISE_APPLICATION_ERROR(-20103, x_progress);
1997
1998 when e_PipeSendMesgException then
1999 ErrCode := -20104;
2000 ErrStr := x_progress;
2001 sdp_internal_err_code := ErrCode;
2002 sdp_internal_err_str := ErrStr;
2003 DBMS_LOB.freetemporary(l_TempLob);
2004
2005 RAISE_APPLICATION_ERROR(-20104, x_progress);
2006
2007 when e_PipePackMesgException then
2008 ErrCode := -20105;
2009 ErrStr := x_progress;
2010 sdp_internal_err_code := ErrCode;
2011 sdp_internal_err_str := ErrStr;
2012 DBMS_LOB.freetemporary(l_TempLob);
2013
2014 RAISE_APPLICATION_ERROR(-20105, x_progress);
2015
2016 when e_PipeUnpackMesgException then
2017 ErrCode := -20106;
2018 ErrStr := x_progress;
2019 sdp_internal_err_code := ErrCode;
2020 sdp_internal_err_str := ErrStr;
2021 DBMS_LOB.freetemporary(l_TempLob);
2022
2023 RAISE_APPLICATION_ERROR(-20106, x_progress);
2024
2025 when e_PipeOutOfSyncException then
2026 ErrCode := -20107;
2027 ErrStr := x_progress;
2028 sdp_internal_err_code := ErrCode;
2029 sdp_internal_err_str := ErrStr;
2030 DBMS_LOB.freetemporary(l_TempLob);
2031
2032 RAISE_APPLICATION_ERROR(-20107, x_progress);
2033
2034 when e_NeFailureException then
2035 ErrCode := xdp_adapter.pv_AdapterFailure;
2036 ErrStr := x_progress;
2037 sdp_internal_err_code := ErrCode;
2038 sdp_internal_err_str := ErrStr;
2039 DBMS_LOB.freetemporary(l_TempLob);
2040
2041 RAISE_APPLICATION_ERROR(-20300, x_progress);
2042
2043 when e_NeWarningException then
2044 ErrCode := xdp_adapter.pv_AdapterWarning;
2045 ErrStr := x_progress;
2046 sdp_internal_err_code := ErrCode;
2047 sdp_internal_err_str := ErrStr;
2048 DBMS_LOB.freetemporary(l_TempLob);
2049
2050 -- RAISE_APPLICATION_ERROR(-20500, x_progress);
2051
2052 when e_NeSessionLostException then
2053 ErrCode := xdp_adapter.pv_AdapterSessionLost;
2054 ErrStr := x_progress;
2055 sdp_internal_err_code := ErrCode;
2056 sdp_internal_err_str := ErrStr;
2057 DBMS_LOB.freetemporary(l_TempLob);
2058
2059 --Changed sacsharm 2/19/02 Use update API that is autonomous
2060 --update XDP_ADAPTER_REG
2061 --set adapter_status = 'SESSION_LOST',
2062 -- LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2063 -- LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
2064 --where channel_name = ChannelName;
2065 XDP_ADAPTER_CORE_DB.Update_Adapter_Status (
2066 p_ChannelName => ChannelName,
2067 p_Status => XDP_ADAPTER.pv_statusSessionLost);
2068
2069
2070 when e_NeTimedOutException then
2071 ErrCode := xdp_adapter.pv_AdapterTimeout;
2072 ErrStr := x_progress;
2073 sdp_internal_err_code := ErrCode;
2074 sdp_internal_err_str := ErrStr;
2075 DBMS_LOB.freetemporary(l_TempLob);
2076
2077 when others then
2078 x_progress := 'In XDP_PROC_CTL.Send Got Unhandeled Exception while sending ' || l_ActualStr || 'Error:' || SUBSTR(SQLERRM,1,400);
2079 ErrCode := -20400;
2080 ErrStr := 'Unhandeled Exception in XDP_PROC_CTL.SEND. Error: ' || SUBSTR(x_progress,1,200);
2081 sdp_internal_err_code := ErrCode;
2082 sdp_internal_err_str := ErrStr;
2083 DBMS_LOB.freetemporary(l_TempLob);
2084
2085 RAISE_APPLICATION_ERROR(-20400,x_progress);
2086 end SEND;
2087
2088
2089
2090
2091
2092
2093 /******** Procedure Send ***********/
2094 /*
2095 * Author: V.Rajaram
2096 * Date Created: June 12 1998
2097 *
2098 * INPUT: workitem_instance_id, pipe name, procedure name, command string
2099 * OUTPUT: response, Err_code, Error String
2100 *
2101 * This procedure is an overloaded send without the prompt
2102 *
2103 * Usage: In forms. This is the procedure the suers SEND's are mapped to.
2104 */
2105 Procedure SEND (OrderID in number,
2106 LineItemID in number,
2107 WIInstanceID in number,
2108 FAInstanceID in number,
2109 ChannelName in varchar2,
2110 FEName in varchar2,
2111 ProcName in varchar2,
2112 Response OUT NOCOPY varchar2,
2113 sdp_internal_err_code OUT NOCOPY number,
2114 sdp_internal_err_str OUT NOCOPY varchar2,
2115 CmdStr in varchar2,
2116 EncryptFlag in varchar2,
2117 ErrCode OUT NOCOPY number,
2118 ErrStr OUT NOCOPY varchar2)
2119
2120 IS
2121 begin
2122 ErrCode := 0;
2123 ErrStr := null;
2124
2125 SEND(OrderID, LineItemID, WIInstanceID, FAInstanceID, ChannelName, FEName, ProcName, Response, sdp_internal_err_code, sdp_internal_err_str, CmdStr, EncryptFlag, 'IGNORE', ErrCode, ErrStr);
2126
2127 exception
2128 when others then
2129 RAISE;
2130 end SEND;
2131
2132
2133
2134 Procedure SEND_HTTP (OrderID in number,
2135 LineItemID in number,
2136 WIInstanceID in number,
2137 FAInstanceID in number,
2138 ChannelName in varchar2,
2139 FEName in varchar2,
2140 ProcName in varchar2,
2141 Response OUT NOCOPY varchar2,
2142 sdp_internal_err_code OUT NOCOPY number,
2143 sdp_internal_err_str OUT NOCOPY varchar2,
2144 CmdStr in varchar2,
2145 EncryptFlag in varchar2,
2146 ErrCode OUT NOCOPY number,
2147 ErrStr OUT NOCOPY varchar2)
2148 is
2149
2150 l_LogFlag number;
2151 l_ReminderLen number;
2152
2153 l_ActualStr varchar2(32767);
2154 l_LogCmd varchar2(32767);
2155
2156
2157 l_Resp UTL_HTTP.HTML_PIECES;
2158
2159 x_progress varchar2(2000);
2160 begin
2161
2162 Response := null;
2163
2164 /*
2165 * find and replace any parameters with their value
2166 */
2167 FIND_REPLACE_PARAMS(OrderID, LineItemID, WIInstanceID, FAInstanceID, CmdStr, l_ActualStr, l_LogFlag, l_LogCmd, ErrCode, ErrStr);
2168 IF ErrCode <> 0 then
2169 x_progress := 'In XDP_PROC_CTL.SEND_HTTP.FIND_REPLACE_PARAMS: Error when trying to get the value of parameters in: ' || SUBSTR(CmdStr, 1, 600) || ' Error: ' || SUBSTR(ErrStr, 1, 600);
2170 RAISE e_ParamValueException;
2171 end IF;
2172
2173 l_Resp := UTL_HTTP.Request_pieces(l_ActualStr);
2174
2175 Response := l_Resp(1);
2176
2177 for i in 2..l_Resp.count LOOP
2178 if LENGTH(Response) < 32767 then
2179 if (LENGTH(Response) + LENGTH(l_Resp(i))) < 32767 then
2180 Response := Response || l_Resp(i);
2181 else
2182 l_ReminderLen := 32767 - LENGTH(l_Resp(i));
2183 Response := Response || SUBSTR(l_Resp(i), 1, l_ReminderLen);
2184 end if;
2185 end if;
2186 END LOOP;
2187
2188 exception
2189 when UTL_HTTP.request_failed then
2190 ErrCode := -20050;
2191 ErrStr := 'Request Failed';
2192 sdp_internal_err_code := ErrCode;
2193 sdp_internal_err_str := ErrStr;
2194
2195 when UTL_HTTP.init_failed then
2196 ErrCode := -20051;
2197 ErrStr := 'Initialization Failed';
2198 sdp_internal_err_code := ErrCode;
2199 sdp_internal_err_str := ErrStr;
2200
2201 when others then
2202 ErrCode := SQLCODE;
2203 ErrStr := SQLERRM;
2204 sdp_internal_err_code := ErrCode;
2205 sdp_internal_err_str := ErrStr;
2206 RAISE;
2207 end SEND_HTTP;
2208
2209
2210
2211
2212
2213 /******** Procedure NOTIFY_ERROR ***********/
2214 /*
2215 * Author: V.Rajaram
2216 * Date Created: June 17 1998
2217 *
2218 * INPUT: 2 strings
2219 * OUTPUT: err_string
2220 *
2221 * This procedure takes 2 strings and checks if one is contained in the other
2222 *
2223 * Usage: In forms. The users NOTIFY_ERROR is modified to match this procedure's
2224 * specs.
2225 */
2226
2227 Procedure NOTIFY_ERROR (ResponseStr in varchar2,
2228 ErrCode OUT NOCOPY number,
2229 ErrStr OUT NOCOPY varchar2,
2230 UserStr in varchar2,
2231 LogFlag in varchar2)
2232 IS
2233 begin
2234 ErrCode := '-666';
2235 IF LogFlag = 'R' then
2236 ErrStr := 'User Defined error. NE Response will not be logged because of security reasons (as per user' || '''s request). ' || UserStr;
2237 ELSE
2238 ErrStr := 'User Defined error: ' || UserStr || '
2239 ' || ResponseStr;
2240 end IF;
2241 end NOTIFY_ERROR;
2242
2243
2244
2245
2246 /******** FUNCTION RESPONSE_CONTAINS ***********/
2247 /*
2248 * Author: V.Rajaram
2249 * Date Created: June 12 1998
2250 *
2251 * INPUT: 2 strings
2252 * OUTPUT: Boolean
2253 *
2254 * This procedure takes 2 strings and checks if one is contained in the other
2255 *
2256 * Usage: In forms. The users RESPONSE_CONTAINS is mmodified to match this procedures
2257 * specs.
2258 */
2259
2260 FUNCTION RESPONSE_CONTAINS (String1 in varchar2,
2261 String2 in varchar2
2262 )
2263 RETURN BOOLEAN
2264 IS
2265 l_dummy number;
2266
2267 begin
2268
2269 IF String1 = NULL OR String2 = NULL then
2270 RETURN TRUE;
2271 end IF;
2272 l_dummy := INSTR(String1, String2, 1, 1);
2273
2274 IF l_dummy = 0 then
2275 RETURN FALSE;
2276 ELSE
2277 RETURN TRUE;
2278 end IF;
2279 end RESPONSE_CONTAINS;
2280
2281
2282
2283 /******** FUNCTION GET_RESPONSE ***********/
2284 /*
2285 * Author: V.Rajaram
2286 * Date Created: June 19 1998
2287 *
2288 * INPUT: inp Str
2289 * OUTPUT: same_str;
2290 *
2291 * This function returns in the input str!!
2292 *
2293 * Usage: In forms. The users Get_Response string is modified to match this function's
2294 * specs.
2295 */
2296
2297 FUNCTION GET_RESPONSE ( ResponseStr in varchar2) RETURN varchar2
2298 IS
2299
2300 begin
2301 RETURN ResponseStr;
2302 end GET_RESPONSE;
2303
2304
2305
2306
2307 /******** Procedure GET_PARAM_VALUE ***********/
2308 /*
2309 * Author: V.Rajaram
2310 * Date Created: Sept 4 1998
2311 *
2312 * INPUT: Fulfillment worklist id, pipe name , param string
2313 * OUTPUT: parameter value
2314 *
2315 * This is another constructs provided to the user to get the value of the parameter
2316 *
2317 * Usage: In forms. The users GET_PARAM_VALUE is modified to match this procedure's
2318 * specs.
2319 */
2320
2321
2322 FUNCTION GET_PARAM_VALUE (OrderID in number,
2323 LineItemID in number,
2324 WIInstanceID in number,
2325 FAInstanceID in number,
2326 ParamName in varchar2
2327 ) RETURN varchar2
2328 IS
2329 l_LogStr varchar2(32767);
2330 l_LogFlag number;
2331 l_ErrCode number;
2332 l_ErrStr varchar2(4000);
2333
2334 l_ParamValue varchar2(600);
2335
2336 x_progress varchar2(2000);
2337 begin
2338 FIND_REPLACE_PARAMS (OrderID,
2339 LineItemID,
2340 WIInstanceID,
2341 FAinstanceID,
2342 ParamName,
2343 l_ParamValue,
2344 l_LogFlag,
2345 l_LogStr,
2346 l_ErrCode,
2347 l_ErrStr);
2348
2349 if l_ErrCode <> 0 then
2350 RAISE e_ParamValueException;
2351 end if;
2352
2353 return l_ParamValue;
2354
2355 exception
2356 when e_ProcExecException then
2357 RAISE_APPLICATION_ERROR(-20021, x_progress);
2358
2359 when others then
2360 x_progress := 'In XDP_PROC_CTL.GET_PARAM_VALUE Got Unhandeled Exception while Trying to get value of parameter: ' || ParamName || 'Error:' || SUBSTR(SQLERRM,1,1000);
2361 RAISE_APPLICATION_ERROR(-20400,x_progress);
2362 end GET_PARAM_VALUE;
2363
2364
2365
2366
2367
2368
2369 -- OLD ONE WILL BE DEPRECATED SOON...
2370 Procedure SEND_SYNC ( ChannelName in varchar2,
2371 ErrCode OUT NOCOPY number,
2372 ErrStr OUT NOCOPY varchar2)
2373 IS
2374 l_Handshake varchar2(80);
2375 l_Counter number;
2376 l_ReturnCode number;
2377 l_ReturnChannelName varchar2(80);
2378
2379
2380 x_progress varchar2(2000);
2381 begin
2382 ErrCode := 0;
2383 ErrStr := null;
2384
2385 l_counter := 1;
2386
2387 l_ReturnChannelName := ChannelName || '_R_PIPE';
2388
2389
2390 /* Clean up the pipes */
2391 begin
2392 DBMS_PIPE.PURGE(ChannelName);
2393 exception
2394 when others then
2395 RAISE e_PipeOutOfSyncException;
2396 END;
2397
2398
2399 begin
2400 DBMS_PIPE.PURGE(l_ReturnChannelName);
2401 exception
2402 when others then
2403 RAISE e_PipeOutOfSyncException;
2404 END;
2405
2406
2407 begin
2408 DBMS_PIPE.PACK_MESSAGE('SYNC');
2409 DBMS_PIPE.PACK_MESSAGE(l_ReturnChannelName);
2410 l_ReturnCode := DBMS_PIPE.SEND_MESSAGE(ChannelName);
2411 exception
2412 when others then
2413 x_Progress := HandlePipeErrors(SQLCODE, 'PACK', 'XDP_PROC_CTL.SEND_SYNC', NULL, 'SYNC', SQLERRM);
2414 RAISE e_PipePackMesgException;
2415 END;
2416
2417 IF l_ReturnCode <> 0 then
2418 x_progress := 'Could not send message to pipe. Command to be sent: SYNC';
2419 RAISE e_PipeSendMesgException;
2420 end IF;
2421
2422 l_Handshake := 'JUNK';
2423
2424 WHILE (l_handshake <> 'ACK_SYNC') LOOP
2425 WAIT_FOR_MESSAGE(l_ReturnChannelName, pv_MesgTimeout, l_Handshake, ErrCode, ErrStr);
2426 IF ErrCode <> 0 then
2427 x_Progress := HandleMessageErrors(-20103, NULL, 'XDP_PROC_CTL.SEND_SYNC', 'SYNC',
2428 l_ReturnChannelName, pv_MesgTimeout, 0,
2429 'SYNC', 'SYNC', NULL, 'N', ErrStr);
2430 RAISE e_PipeWaitForMesgException;
2431 end IF;
2432 end LOOP;
2433
2434
2435 /* Raja: 09/01/1999
2436 Added to reset the Dirty Bit to be FALSE so that each new SEND will get the latest NE Command Timeout
2437 */
2438 pv_DirtyBit := FALSE;
2439
2440
2441 exception
2442 when e_ProcExecException then
2443 ErrCode := -20021;
2444 ErrStr := x_progress;
2445 RAISE_APPLICATION_ERROR(-20021, x_progress);
2446 when e_PipeSendAckException then
2447 ErrCode := -20101;
2448 ErrStr := x_progress;
2449 RAISE_APPLICATION_ERROR(-20101, x_progress);
2450 when e_PipeWaitForMesgException then
2451 ErrCode := -20103;
2452 ErrStr := x_progress;
2453 RAISE_APPLICATION_ERROR(-20103, x_progress);
2454 when e_PipeSendMesgException then
2455 ErrCode := -20104;
2456 ErrStr := x_progress;
2457 RAISE_APPLICATION_ERROR(-20104, x_progress);
2458 when e_PipePackMesgException then
2459 ErrCode := -20105;
2460 ErrStr := x_progress;
2461 RAISE_APPLICATION_ERROR(-20105, x_progress);
2462 when e_PipeUnpackMesgException then
2463 ErrCode := -20106;
2464 ErrStr := x_progress;
2465 RAISE_APPLICATION_ERROR(-20106, x_progress);
2466 when e_PipeOutOfSyncException then
2467 ErrCode := -20107;
2468 ErrStr := x_progress;
2469 RAISE_APPLICATION_ERROR(-20107, x_progress);
2470 when others then
2471 x_Progress := HandleMessageErrors(SQLCODE, NULL, 'XDP_PROC_CTL.SEND_SYNC', 'SYNC',
2472 ChannelName, pv_MesgTimeout, 0,
2473 'SYNC', 'SYNC', NULL, 'N', SQLERRM);
2474 ErrCode := SQLCODE;
2475 RAISE_APPLICATION_ERROR(-20021, x_progress);
2476 end SEND_SYNC;
2477
2478
2479 /******** Procedure SEND_SYNC ***********/
2480 /*
2481 * Author: V.Rajaram
2482 * Date Created: August 11 1998
2483 *
2484 * INPUT: workitem_instance_id, Pipename (send), pipename (return)
2485 * OUTPUT: Err_code, Error String
2486 *
2487 * This is the procedure is executed if every time before the provisionnog procedure
2488 * is executed.
2489 *
2490 * Usage: Before the users writtes send function.
2491 */
2492
2493 Procedure SEND_SYNC ( ChannelName in varchar2,
2494 FeName in varchar2,
2495 ErrCode OUT NOCOPY number,
2496 ErrStr OUT NOCOPY varchar2)
2497 IS
2498 l_Handshake varchar2(80);
2499 l_Counter number;
2500 l_ReturnCode number;
2501 l_ReturnChannelName varchar2(80);
2502 l_ApplChannelName varchar2(80);
2503
2504
2505 x_progress varchar2(2000);
2506 begin
2507 ErrCode := 0;
2508 ErrStr := null;
2509
2510 xdp_adapter.pv_AdapterExitCode := null;
2511
2512 l_ApplChannelName := XDP_ADAPTER_CORE_PIPE.ConstructChannelName
2513 (p_ChannelType => 'APPL',
2514 p_ChannelName => ChannelName);
2515
2516 l_ReturnChannelName := XDP_ADAPTER_CORE_PIPE.GetReturnChannelName
2517 (p_ChannelName => l_ApplChannelName);
2518
2519 XDP_ADAPTER_CORE_PIPE.CleanupPipe(p_ChannelName => l_ApplChannelName,
2520 p_CleanReturn => 'Y');
2521
2522 XDP_ADAPTER_CORE.SendSync(p_ChannelName => l_ApplChannelName);
2523
2524 XDP_ENGINE.GET_FE_CONFIGINFO ( FEName,
2525 pv_FeID,
2526 pv_FeTypeID,
2527 pv_FeType,
2528 pv_SwGeneric,
2529 pv_AdapterType);
2530
2531 GET_ADAPTER_TOTAL_TIMEOUT (pv_FeID, pv_MesgTimeout, ErrCode, ErrStr);
2532 if ErrCode <> 0 then
2533 RAISE e_ParamValueException;
2534 end if;
2535
2536 /************************ OLD CODE - COMMENTEDOUT TILL THE END *********************/
2537 /*
2538 l_counter := 1;
2539
2540 l_ReturnChannelName := ChannelName || '_R_PIPE';
2541
2542
2543 -- Clean up the pipes
2544 begin
2545 DBMS_PIPE.PURGE(ChannelName);
2546 exception
2547 when others then
2548 RAISE e_PipeOutOfSyncException;
2549 END;
2550
2551
2552 begin
2553 DBMS_PIPE.PURGE(l_ReturnChannelName);
2554 exception
2555 when others then
2556 RAISE e_PipeOutOfSyncException;
2557 END;
2558
2559
2560 begin
2561 DBMS_PIPE.PACK_MESSAGE('SYNC');
2562 DBMS_PIPE.PACK_MESSAGE(l_ReturnChannelName);
2563 l_ReturnCode := DBMS_PIPE.SEND_MESSAGE(ChannelName);
2564 exception
2565 when others then
2566 x_Progress := HandlePipeErrors(SQLCODE, 'PACK', 'XDP_PROC_CTL.SEND_SYNC', NULL, 'SYNC', SQLERRM);
2567 RAISE e_PipePackMesgException;
2568 END;
2569
2570 IF l_ReturnCode <> 0 then
2571 x_progress := 'Could not send message to pipe. Command to be sent: SYNC';
2572 RAISE e_PipeSendMesgException;
2573 end IF;
2574
2575 l_Handshake := 'JUNK';
2576
2577 WHILE (l_handshake <> 'ACK_SYNC') LOOP
2578 WAIT_FOR_MESSAGE(l_ReturnChannelName, pv_MesgTimeout, l_Handshake, ErrCode, ErrStr);
2579 IF ErrCode <> 0 then
2580 x_Progress := HandleMessageErrors(-20103, NULL, 'XDP_PROC_CTL.SEND_SYNC', 'SYNC',
2581 l_ReturnChannelName, pv_MesgTimeout, 0,
2582 'SYNC', 'SYNC', NULL, 'N', ErrStr);
2583 RAISE e_PipeWaitForMesgException;
2584 end IF;
2585 end LOOP;
2586
2587
2588 -- Raja: 09/01/1999
2589 -- Added to reset the Dirty Bit to be FALSE so that each new SEND will get the latest NE Command Timeout
2590 pv_DirtyBit := FALSE;
2591 */
2592
2593 /************************ END OF OLD CODE - COMMENTEDOUT *********************/
2594 exception
2595 when e_ProcExecException then
2596 ErrCode := -20021;
2597 ErrStr := x_progress;
2598 RAISE_APPLICATION_ERROR(-20021, x_progress);
2599 when e_PipeSendAckException then
2600 ErrCode := -20101;
2601 ErrStr := x_progress;
2602 RAISE_APPLICATION_ERROR(-20101, x_progress);
2603 when e_PipeWaitForMesgException then
2604 ErrCode := -20103;
2605 ErrStr := x_progress;
2606 RAISE_APPLICATION_ERROR(-20103, x_progress);
2607 when e_PipeSendMesgException then
2608 ErrCode := -20104;
2609 ErrStr := x_progress;
2610 RAISE_APPLICATION_ERROR(-20104, x_progress);
2611 when e_PipePackMesgException then
2612 ErrCode := -20105;
2613 ErrStr := x_progress;
2614 RAISE_APPLICATION_ERROR(-20105, x_progress);
2615 when e_PipeUnpackMesgException then
2616 ErrCode := -20106;
2617 ErrStr := x_progress;
2618 RAISE_APPLICATION_ERROR(-20106, x_progress);
2619 when e_PipeOutOfSyncException then
2620 ErrCode := -20107;
2621 ErrStr := x_progress;
2622 RAISE_APPLICATION_ERROR(-20107, x_progress);
2623 when others then
2624 x_Progress := HandleMessageErrors(SQLCODE, NULL, 'XDP_PROC_CTL.SEND_SYNC', 'SYNC',
2625 ChannelName, pv_MesgTimeout, 0,
2626 'SYNC', 'SYNC', NULL, 'N', SQLERRM);
2627 ErrCode := SQLCODE;
2628 RAISE_APPLICATION_ERROR(-20021, x_progress);
2629 end SEND_SYNC;
2630
2631
2632
2633
2634
2635
2636 /***************************************************
2637 ** These Set of Procedures/Functions are for CONNECT/DISCONNECT PROCEDURE
2638 ** GENERATION, EXECUTION etc
2639 ***************************************************/
2640
2641
2642 /******** PROCEDURE CHECK_CONNECT_PARAM_NAME ***********/
2643 /*
2644 * Author: V.Rajaram
2645 * Date Created: July 27 1998
2646 *
2647 * INPUT: NE type ID, Parameter Name
2648 * OUTPUT: Error Code, Error String
2649 *
2650 * This procedure takes an SFM CONNECT Parameter name as input and checks if they have been
2651 * defined for that NE Type ID
2652 *
2653 * Usage: In the FIND_REPLACE_CONNECT_PARAMS procedure
2654 */
2655
2656 PROCEDURE CHECK_CONNECT_PARAM_NAME (FeTypeID in number,
2657 Param in varchar2,
2658 ErrCode OUT NOCOPY number,
2659 ErrStr OUT NOCOPY varchar2)
2660 IS
2661 l_ParamCount number;
2662
2663 begin
2664
2665 ErrCode := 0;
2666 ErrStr := NULL;
2667
2668 l_ParamCount := 0;
2669
2670 /*
2671 * Check if the parameter is configured for the specified NE type ID in the
2672 * FE_ATTRIBUTE_DEF table
2673 * If not there return an error message
2674 */
2675
2676 begin
2677 select 1 into l_ParamCount from dual
2678 where exists (
2679 select FE_ATTRIBUTE_ID
2680 from XDP_FE_ATTRIBUTE_DEF xad, XDP_FE_SW_GEN_LOOKUP xfl
2681 where xad.FE_ATTRIBUTE_NAME = Param
2682 and xad.FE_SW_GEN_LOOKUP_ID = xfl.FE_SW_GEN_LOOKUP_ID
2683 and xfl.FETYPE_ID = FeTypeID);
2684
2685 l_ParamCount := 1;
2686 exception
2687 when no_data_found then
2688 l_ParamCount := 0;
2689 when others then
2690 RAISE;
2691 end;
2692
2693 IF l_ParamCount = 0 THEN
2694 ErrCode := -1;
2695 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_ATTRIBUTE');
2696 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_NAME', Param);
2697 ErrStr := FND_MESSAGE.GET;
2698 RETURN;
2699 end IF;
2700
2701 exception
2702 when others then
2703 ErrCode := SQLCODE;
2704 ErrStr := 'Unhandled Exception in XDP_PROC_CTL.CHECK_CONNECT_PARAM_NAME. Error: ' || SUBSTR(SQLERRM,1,200);
2705 end CHECK_CONNECT_PARAM_NAME;
2706
2707
2708
2709
2710
2711
2712
2713
2714 /******** PROCEDURE FIND_CONNECT_PARAMS ***********/
2715 /*
2716 * Author: V.Rajaram
2717 * Date Created: July 27 1998
2718 *
2719 * INPUT: NE Type, Input String
2720 * OUTPUT: Err_code, Error String
2721 *
2722 * This procedure takes a long string as input and checks for any SFM parameters.
2723 * The parameters are identified by $... The delimiters are " ", ":", ";", ",", "'"
2724 * When found it checks if such a parameter has been defined for the service, version,
2725 * action. This is done by Sdp_Check_Connect_Param_Name procedure.
2726 *
2727 * Usage: In forms. When the user tries to saves his connection procedure.
2728 */
2729
2730 PROCEDURE FIND_CONNECT_PARAMETERS (FeTypeID in number,
2731 ConnectStr in varchar2,
2732 ErrCode OUT NOCOPY varchar2,
2733 ErrStr OUT NOCOPY varchar2)
2734
2735 IS
2736 TYPE DELIM_POS IS TABLE OF number INDEX BY BINARY_INTEGER;
2737
2738 e_check_param_exception exception;
2739 l_param_w_dollar varchar2(50);
2740 l_param_wo_dollar varchar2(50);
2741 l_param_wo_old varchar2(50);
2742 l_save_str varchar2(32700);
2743 i number;
2744 j number;
2745 l_str_len number;
2746 l_dollar_found number;
2747 l_syntax_error number;
2748 l_done number;
2749 l_delim DELIM_POS;
2750 l_mark number;
2751 l_counter number;
2752
2753 begin
2754 ErrCode := 0;
2755 ErrStr := NULL;
2756
2757 l_save_str := ConnectStr;
2758 l_done := 0;
2759
2760 WHILE l_done <> 1 LOOP
2761 l_str_len := LENGTH(l_save_str);
2762 /* Check for "$" character. Indicates an SFM variable */
2763 i := INSTR(l_save_str,'$',1,1);
2764
2765 IF i = 0 THEN
2766 /* No Variable found return the same string */
2767 ErrCode := 0;
2768 ErrStr := NULL;
2769 l_done := 1;
2770 RETURN;
2771 ELSE
2772 /* A variable found */
2773 l_dollar_found := 1;
2774 end IF;
2775 /*
2776 * If the $ is the last letter then there is a syntax error
2777 */
2778 IF i = l_str_len THEN
2779 l_syntax_error := 1;
2780 ErrCode := -1;
2781 ErrStr := 'Syntax Error in ' || ConnectStr || '$ found but no variable specified';
2782 RETURN;
2783 end IF;
2784 l_save_str := SUBSTR(l_save_str,i,l_str_len);
2785 /*
2786 * Find all the possible delimiters to get the variable name
2787 */
2788 l_delim(1) := INSTR(l_save_str,',',1,1);
2789 l_delim(2) := INSTR(l_save_str,':',1,1);
2790 l_delim(3) := INSTR(l_save_str,';',1,1);
2791 l_delim(4) := INSTR(l_save_str,' ',1,1);
2792 l_delim(5) := INSTR(l_save_str,'''',1,1);
2793 l_delim(6) := INSTR(l_save_str,'"',1,1);
2794
2795 l_mark := -1;
2796 j := -1;
2797 /*
2798 * Find the closest de-limiter from the $ sign
2799 */
2800 FOR l_counter in 1..6 LOOP
2801 IF l_delim(l_counter) > 0 and j < 0 THEN
2802 j := l_delim(l_counter);
2803 l_mark := l_counter;
2804 ELSIF l_delim(l_counter) > 0 and l_delim(l_counter) < j THEN
2805 j := l_delim(l_counter);
2806 l_mark := l_counter;
2807 end IF;
2808 end LOOP;
2809 /*
2810 * Get the parameter
2811 */
2812 IF j > 0 THEN
2813 l_param_w_dollar := SUBSTR(l_save_str,1,j-1);
2814 l_save_str := SUBSTR(l_save_str,j,l_str_len);
2815 ELSIF j = -1 THEN
2816 l_param_w_dollar := l_save_str;
2817 l_save_str := ' ';
2818 end IF;
2819 l_param_wo_dollar := SUBSTR(l_param_w_dollar,2,LENGTH(l_param_w_dollar));
2820
2821 /*
2822 * Check if the parameter got is a valid parameter for the
2823 * NE Type
2824 */
2825 CHECK_CONNECT_PARAM_NAME(FeTypeID, l_param_wo_dollar, ErrCode, ErrStr);
2826 IF ErrCode < 0 OR ErrCode = 100 THEN
2827 RAISE e_check_param_exception;
2828 end IF;
2829 end LOOP;
2830 exception
2831 WHEN e_check_param_exception THEN
2832 null;
2833 when others then
2834 ErrCode := SQLCODE;
2835 ErrStr := 'Unhandled Exception in XDP_PROC_CTL.FIND_CONNECT_PARAMETERS. Error: ' || SUBSTR(SQLERRM,1,200);
2836 end FIND_CONNECT_PARAMETERS;
2837
2838
2839 /******** PROCEDURE FIND_REPLACE_CONNECT_PARAMS ***********/
2840 /*
2841 * Author: V.Rajaram
2842 * Date Created: July 27 1998
2843 *
2844 * INPUT: NE Type ID, NE ID, Input String (command), S/W Generic
2845 * OUTPUT: Command str (with parameters replaced with values) Err_code, Error String
2846 *
2847 * This procedure is executed at run time by the Send function to get the values of
2848 * of the parameters. It takes a long string as input and checks for any SFM parameters.
2849 * The parameters are identified by $... The delimiters are " ", ":", ";", ",", "'"
2850 * When found it gets the value using Get_Connect_Param_Value procedure.
2851 *
2852 * Usage: At runtime in the Send procedure for NE connection
2853 */
2854
2855 PROCEDURE FIND_REPLACE_CONNECT_PARAMS (FeName in varchar2,
2856 ConnectStr in varchar2,
2857 ActualStr OUT NOCOPY varchar2,
2858 ErrCode OUT NOCOPY varchar2,
2859 ErrStr OUT NOCOPY varchar2)
2860
2861 IS
2862 -- Datastructure Definitions
2863
2864 TYPE DELIM_POS IS TABLE OF number INDEX BY BINARY_INTEGER;
2865
2866 -- PL/SQL Block
2867 e_ParamValueException exception;
2868 l_param_w_dollar varchar2(50);
2869 l_param_wo_dollar varchar2(50);
2870 l_param_wo_old varchar2(50);
2871 l_save_str varchar2(900);
2872 i number;
2873 j number;
2874 l_str_len number;
2875 l_dollar_found number;
2876 l_syntax_error number;
2877 l_done number;
2878 l_delim DELIM_POS;
2879 l_mark number;
2880 l_counter number;
2881 l_param_value varchar2(4000);
2882
2883 begin
2884 ErrCode := 0;
2885 ErrStr := NULL;
2886
2887 l_save_str := ConnectStr;
2888 l_done := 0;
2889 WHILE l_done <> 1 LOOP
2890 l_str_len := LENGTH(l_save_str);
2891 /*
2892 * Check for the character "$". It identifies an SFM parameter
2893 */
2894 i := INSTR(l_save_str,'$',1,1);
2895 IF i = 0 THEN
2896 /* No Variable found return the same string */
2897 ErrCode := 0;
2898 ErrStr := NULL;
2899 l_done := 1;
2900 if l_save_str = ' ' then
2901 exit;
2902 else
2903 ActualStr := ActualStr || l_save_str;
2904 exit;
2905 end if;
2906 ELSE
2907 /* A variable found */
2908 l_dollar_found := 1;
2909 /* Save the string to the left of the "$" sign in ActualStr */
2910 ActualStr := ActualStr || SUBSTR(l_save_str,1,i-1);
2911 end IF;
2912 /*
2913 * If the $ is the last letter syntax error
2914 */
2915 IF i = l_str_len THEN
2916 l_syntax_error := 1;
2917 ErrCode := -1;
2918 ErrStr := 'Syntax Error in ' || ConnectStr || ' No variable found';
2919 RETURN;
2920 end IF;
2921 l_save_str := SUBSTR(l_save_str,i,l_str_len);
2922 /*
2923 * Find all the possible delimiters to get the variable name
2924 */
2925 l_delim(1) := INSTR(l_save_str,',',1,1);
2926 l_delim(2) := INSTR(l_save_str,':',1,1);
2927 l_delim(3) := INSTR(l_save_str,';',1,1);
2928 l_delim(4) := INSTR(l_save_str,' ',1,1);
2929 l_delim(5) := INSTR(l_save_str,'''',1,1);
2930 l_mark := -1;
2931 j := -1;
2932 /*
2933 * Find the closest de-limiter from the $ sign
2934 */
2935 FOR l_counter in 1..5 LOOP
2936 IF l_delim(l_counter) > 0 and j < 0 THEN
2937 j := l_delim(l_counter);
2938 l_mark := l_counter;
2939 ELSIF l_delim(l_counter) > 0 and l_delim(l_counter) < j THEN
2940 j := l_delim(l_counter);
2941 l_mark := l_counter;
2942 end IF;
2943 end LOOP;
2944 /*
2945 * Get the parameter
2946 */
2947 IF j > 0 THEN
2948 l_param_w_dollar := SUBSTR(l_save_str,1,j-1);
2949 l_save_str := SUBSTR(l_save_str,j,l_str_len);
2950 ELSIF j = -1 THEN
2951 l_param_w_dollar := l_save_str;
2952 l_save_str := ' ';
2953 end IF;
2954
2955 l_param_wo_dollar := SUBSTR(l_param_w_dollar,2,LENGTH(l_param_w_dollar));
2956
2957 /*
2958 * Get the Connect parameter value at run time
2959 */
2960 l_param_value := XDP_ENGINE.Get_FE_ATTRIBUTEVAL(FeName, l_param_wo_dollar);
2961 IF ErrCode < 0 OR ErrCode = 100 THEN
2962 RAISE e_ParamValueException;
2963 ActualStr := ConnectStr;
2964 end IF;
2965
2966 /*
2967 * Replace parameter with its value
2968 */
2969 ActualStr := ActualStr || l_param_value;
2970 end LOOP;
2971
2972 ErrCode := 0;
2973 ErrStr := NULL;
2974
2975 exception
2976 WHEN e_ParamValueException THEN
2977 null;
2978 when others then
2979 ErrCode := SQLCODE;
2980 ErrStr := 'Unhandled Exception in XDP_PROC_CTL.FIND_REPLACE_CONNECT_PARAMS. Error: ' || SUBSTR(SQLERRM,1,200);
2981 end FIND_REPLACE_CONNECT_PARAMS;
2982
2983
2984 PROCEDURE GET_FE_PREFERENCES (FeName in varchar2,
2985 CmdTimeout OUT NOCOPY number,
2986 CmdRetryCount OUT NOCOPY number,
2987 CmdWait OUT NOCOPY number,
2988 NoActTimeout OUT NOCOPY number,
2989 DummyCmd OUT NOCOPY varchar2,
2990 ConnectRetryCount OUT NOCOPY number,
2991 ConnectRetryWait OUT NOCOPY number,
2992 ErrCode OUT NOCOPY number,
2993 ErrStr OUT NOCOPY varchar2)
2994 IS
2995 l_FeValue varchar2(40);
2996 begin
2997 ErrCode := 0;
2998 ErrStr := NULL;
2999
3000 begin
3001 l_FeValue := XDP_ENGINE.Get_FE_AttributeVal(FeName, pv_attrFeCmdTimeout);
3002 if l_FeValue is null then
3003 CmdTimeout := 120;
3004 else
3005 CmdTimeout := to_number(l_FeValue);
3006 end if;
3007 exception
3008 when no_data_found then
3009 CmdTimeout := 120;
3010 when others then
3011 RAISE e_ParamValueException;
3012 end;
3013
3014 begin
3015 l_FeValue := XDP_ENGINE.Get_FE_AttributeVal(FeName, pv_attrFeRetryCount);
3016 if l_FeValue is null then
3017 CmdRetryCount := 0;
3018 else
3019 CmdRetryCount := to_number(l_FeValue);
3020 end if;
3021 exception
3022 when no_data_found then
3023 CmdRetryCount := 0;
3024 when others then
3025 RAISE e_ParamValueException;
3026 end;
3027
3028 begin
3029 l_FeValue := XDP_ENGINE.Get_FE_AttributeVal(FeName, pv_attrFeCmdRetryWait);
3030 if l_FeValue is null then
3031 CmdWait := 0;
3032 else
3033 CmdWait := to_number(l_FeValue);
3034 end if;
3035 exception
3036 when no_data_found then
3037 CmdWait := 0;
3038 when others then
3039 RAISE e_ParamValueException;
3040 end;
3041
3042 begin
3043 l_FeValue := XDP_ENGINE.Get_FE_AttributeVal(FeName, pv_attrFeNoActTimeout);
3044 if l_FeValue is null then
3045 NoActTimeout := 0;
3046 else
3047 NoActTimeout := to_number(l_FeValue);
3048 end if;
3049 exception
3050 when no_data_found then
3051 NoActTimeout := 0;
3052 when others then
3053 RAISE e_ParamValueException;
3054 end;
3055
3056
3057 begin
3058 DummyCmd := XDP_ENGINE.Get_FE_AttributeVal(FeName, pv_attrFeCmdKeepAlive);
3059 if DummyCmd is null then
3060 DummyCmd := ' ';
3061 end if;
3062 exception
3063 when no_data_found then
3064 DummyCmd := ' ';
3065 when others then
3066 RAISE e_ParamValueException;
3067 end;
3068
3069 begin
3070 l_FeValue := XDP_ENGINE.Get_FE_AttributeVal(FeName, pv_attrFeConnRetryCount);
3071 if l_FeValue is null then
3072 ConnectRetryCount := 0;
3073 else
3074 ConnectRetryCount := to_number(l_FeValue);
3075 end if;
3076 exception
3077 when no_data_found then
3078 ConnectRetryCount := 0;
3079 when others then
3080 RAISE e_ParamValueException;
3081 end;
3082
3083
3084 begin
3085 l_FeValue := XDP_ENGINE.Get_FE_AttributeVal(FeName, pv_attrFeConnRetryWait);
3086 if l_FeValue is null then
3087 ConnectRetryWait := 0;
3088 else
3089 ConnectRetryWait := to_number(l_FeValue);
3090 end if;
3091 exception
3092 when no_data_found then
3093 ConnectRetryWait := 0;
3094 when others then
3095 RAISE e_ParamValueException;
3096 end;
3097 exception
3098 WHEN e_ParamValueException THEN
3099 ErrCode := SQLCODE;
3100 ErrStr := 'Error when getting NE preferences. Error: ' || SUBSTR(SQLERRM,1,500);
3101 when others then
3102 ErrCode := SQLCODE;
3103 ErrStr := 'Unhandled Exception in XDP_PROC_CTL.GET_NE_PREFERENCES. Error: ' || SUBSTR(SQLERRM,1,200);
3104 end GET_FE_PREFERENCES;
3105
3106
3107
3108 PROCEDURE SEND_CONNECT (FeName in varchar2,
3109 ChannelName in varchar2,
3110 ProcName in varchar2,
3111 Response OUT NOCOPY varchar2,
3112 sdp_internal_err_code OUT NOCOPY number,
3113 sdp_internal_err_str OUT NOCOPY varchar2,
3114 CmdStr in varchar2,
3115 Prompt in varchar2 ,
3116 ErrCode OUT NOCOPY number,
3117 ErrStr OUT NOCOPY varchar2)
3118
3119 IS
3120
3121 -- PL/SQL Block
3122
3123 l_actual_str varchar2(32767);
3124
3125 l_prompt varchar2(32767);
3126 l_prompt_value varchar2(32767);
3127
3128 x_progress varchar2(2000);
3129 begin
3130 ErrCode := 0;
3131 ErrStr := NULL;
3132
3133 Response := ' ';
3134
3135 /*
3136 * find and replace any connect parameters with their value
3137 */
3138 FIND_REPLACE_CONNECT_PARAMS(FeName, CmdStr, l_actual_str, ErrCode, ErrStr);
3139 IF ErrCode < 0 OR ErrCode = 100 THEN
3140 x_progress := 'In XDP_PROC_CTL.SEND.FIND_REPLACE_CONNECT_PARAMS: Error when trying to get the value of parameters in: ' || SUBSTR(CmdStr, 1, 600) || ' Error: ' || SUBSTR(ErrStr,1,600);
3141 RAISE e_ParamValueException;
3142 end IF;
3143
3144 /*
3145 * Same deal with the prompt
3146 */
3147
3148 l_prompt := PROMPT;
3149 l_prompt_value := l_prompt;
3150
3151 /*
3152 * The Prompt itself can be a parameter. Check for the value of the prompt
3153 */
3154
3155 if l_prompt <> 'IGNORE' THEN
3156 FIND_REPLACE_CONNECT_PARAMS(FeName, l_prompt, l_prompt_value, ErrCode, ErrStr);
3157 IF ErrCode < 0 OR ErrCode = 100 THEN
3158 x_progress := 'In XDP_PROC_CTL.SEND.FIND_REPLACE_CONNECT_PARAMS: Error when trying to get the value of prompt: ' || SUBSTR(l_prompt, 1, 600) || ' Error: ' || SUBSTR(ErrStr,1,600);
3159 RAISE e_ParamValueException;
3160 end IF;
3161 end IF;
3162
3163
3164 /*
3165 * Trim the trailing spaces in the commands and the prompt to be sent
3166 */
3167 IF LENGTH(RTRIM(l_actual_str, ' ')) > 0 THEN
3168 l_actual_str := RTRIM(l_actual_str, ' ');
3169 end IF;
3170
3171 IF LENGTH(RTRIM(l_prompt_value, ' ')) > 0 THEN
3172 l_prompt_value := RTRIM(l_prompt_value, ' ');
3173 end IF;
3174
3175
3176 AppendConnectCommands(l_actual_str, l_prompt_value);
3177
3178 exception
3179 when others then
3180 x_progress := 'Unhandeled Exception in XDP_PROC_CTL.Send while sending ' || l_actual_str || 'Error:' || SUBSTR(SQLERRM,1,400);
3181 ErrCode := -20400;
3182 ErrStr := x_progress;
3183 sdp_internal_err_code := ErrCode;
3184 sdp_internal_err_str := ErrStr;
3185 RAISE_APPLICATION_ERROR(-20400,x_progress);
3186 end SEND_CONNECT;
3187
3188
3189
3190 PROCEDURE SEND_CONNECT (FeName in varchar2,
3191 ChannelName in varchar2,
3192 ProcName in varchar2,
3193 Response OUT NOCOPY varchar2,
3194 sdp_internal_err_code OUT NOCOPY number,
3195 sdp_internal_err_str OUT NOCOPY varchar2,
3196 CmdStr in varchar2,
3197 ErrCode OUT NOCOPY number,
3198 ErrStr OUT NOCOPY varchar2)
3199
3200 IS
3201 x_progress varchar2(2000);
3202 begin
3203 SEND_CONNECT(FeName, ChannelName, ProcName, Response, sdp_internal_err_code, sdp_internal_err_str, CmdStr, 'IGNORE', ErrCode, ErrStr);
3204
3205 exception
3206 when others then
3207 x_progress := 'Unhandeled Exception in XDP_PROC_CTL.Send while sending ' || CmdStr || 'Error:' || SUBSTR(SQLERRM,1,400);
3208 ErrCode := -20400;
3209 ErrStr := x_progress;
3210 sdp_internal_err_code := ErrCode;
3211 sdp_internal_err_str := ErrStr;
3212 RAISE_APPLICATION_ERROR(-20400,x_progress);
3213 end SEND_CONNECT;
3214
3215
3216
3217
3218 /******** PROCEDURE GENERATE_CONNECT_PROC ***********/
3219 /*
3220 * Author: V.Rajaram
3221 * Date Created: July 27 1998
3222 *
3223 * INPUT: Procedure Name, Input String (user written procedure)
3224 * OUTPUT: Err_code, Error String
3225 *
3226 * This procedure takes the user written connect procedure string and the procedure name
3227 * as input and generates the connect actual procedure.
3228 * It also generates any syntax errors in the actual PL/SQL block written by the user.
3229 *
3230 * Usage: In forms. After the sdp_find_connect_parameters procedure went successfully.
3231 */
3232
3233 PROCEDURE GENERATE_CONNECT_PROC (ProcName in varchar2,
3234 ProcBody in varchar2,
3235 CompiledProc OUT NOCOPY varchar2,
3236 ErrCode OUT NOCOPY number,
3237 ErrStr OUT NOCOPY varchar2)
3238 IS
3239 -- PL/SQL Block
3240 l_dummy number;
3241
3242 l_temp_str varchar2(32767);
3243
3244 l_count number;
3245 l_cnt number;
3246 l_done number;
3247 l_start number;
3248
3249 l_final_str varchar2(32767);
3250 l_replace_send_str varchar2(500);
3251 l_replace_connect_str varchar2(500);
3252 l_end_str varchar2(500);
3253 l_sync_str varchar2(500);
3254
3255 l_str_before_end varchar2(32767);
3256 l_str_after_end varchar2(4000);
3257 l_end_loc number;
3258
3259 l_str_before_send varchar2(32767);
3260 l_str_after_send varchar2(32767);
3261 l_send_loc number;
3262
3263 l_str_before_lastsend varchar2(32767);
3264 l_str_after_lastsend varchar2(32767);
3265 l_lastsend_loc number;
3266
3267 l_str_before_declare varchar2(32767);
3268 l_str_after_declare varchar2(32767);
3269 l_declare_loc number;
3270
3271 begin
3272 ErrCode := 0;
3273 ErrStr := NULL;
3274
3275 l_temp_str := ProcBody;
3276
3277 /*
3278 * Replace user written SEND, LOGIN to comply with the actual spec of those procedures
3279 * Remove the user written DECLARE as we will be generating the procedure on the fly
3280 */
3281 l_replace_connect_str := 'XDP_PROC_CTL.SEND_CONNECT(Fe_Name, ' || g_new_line ||
3282 ' Channel_Name, ' || g_new_line ||
3283 '''' || ProcName ||''', '|| g_new_line ||
3284 ' sdp_internal_response, '||g_new_line ||
3285 ' sdp_internal_err_code, '||g_new_line||
3286 ' sdp_internal_err_str, ';
3287
3288 l_replace_send_str := 'XDP_PROC_CTL.SEND_CONNECT(Fe_Name, ' || g_new_line ||
3289 ' Channel_Name, ' || g_new_line ||
3290 '''' || ProcName ||''', ' ||g_new_line ||
3291 ' sdp_internal_response, ' || g_new_line ||
3292 ' sdp_internal_err_code, ' || g_new_line ||
3293 ' sdp_internal_err_str, ';
3294
3295 l_sync_str := 'XDP_PROC_CTL.RESET_BUFFER; ' || g_new_line;
3296
3297
3298 /*
3299 * Replace the strings here...
3300 */
3301
3302 l_temp_str := REPLACE(l_temp_str, 'SEND(', l_replace_send_str);
3303 l_temp_str := REPLACE(l_temp_str, 'LOGIN(', l_replace_connect_str);
3304
3305 /*
3306 * Need to find the DECLARE word location and delete the users declare string (can be case sensitive)
3307 */
3308
3309 l_declare_loc := INSTR(UPPER(l_temp_str), 'DECLARE', 1, 1);
3310 IF l_declare_loc <> 0 THEN
3311 l_str_before_declare := SUBSTR(l_temp_str, 1, l_declare_loc - 1);
3312 l_str_after_declare := SUBSTR(l_temp_str, l_declare_loc + 7, LENGTH(l_temp_str));
3313
3314 l_temp_str := l_str_before_declare || ' ' || l_str_after_declare;
3315 end IF;
3316
3317
3318 /*
3319 * Construct the procedure
3320 */
3321 if fnd_profile.defined('XDP_FP_SPEC') then
3322 fnd_profile.get('XDP_FP_SPEC', l_final_str);
3323 l_final_str := ' Procedure ' || ProcName || ' ( '|| g_new_line || l_final_str || ' ) ' || g_new_line ||
3324 'IS ' || g_new_line ||
3325 ' sdp_internal_response varchar2(32767); ' || g_new_line || ' ';
3326 else
3327
3328 l_final_str := ' Procedure ' || ProcName || ' ( ' || g_new_line ||
3329 ' Fe_Name in varchar2, ' || g_new_line ||
3330 ' Channel_Name in varchar2, ' || g_new_line ||
3331 ' sdp_internal_err_code out number, ' || g_new_line ||
3332 ' sdp_internal_err_str out varchar2 ' || ' ) ' || g_new_line ||
3333 'IS ' || g_new_line ||
3334 ' sdp_internal_response varchar2(32767); ' || g_new_line || ' ';
3335
3336 end if;
3337
3338 l_final_str := l_final_str || l_temp_str;
3339
3340 /*
3341 * Find the first users's Send string and insert the RESET_BUFFER procedure call before the
3342 * first call to send.
3343 */
3344 l_send_loc := INSTR(UPPER(l_final_str), 'XDP_PROC_CTL.SEND_CONNECT(', 1, 1);
3345
3346 IF l_send_loc <> 0 THEN
3347 l_str_before_send := SUBSTR(l_final_str, 1, l_send_loc -1);
3348 l_str_after_send := SUBSTR(l_final_str, l_send_loc, LENGTH(l_final_str));
3349
3350 l_final_str := l_str_before_send || l_sync_str || l_str_after_send;
3351 end IF;
3352
3353
3354 CompiledProc := l_final_str;
3355
3356 ErrCode := 0;
3357 ErrStr := 'Successfully generated the stored procedure ' || ProcName;
3358 exception
3359 when others then
3360 ErrCode := SQLCODE;
3361 ErrStr := 'Unhandled Exception in XDP_PROC_CTL.GENERATE_CONNECT_PROC. Error: ' || SUBSTR(SQLERRM,1,200);
3362 end GENERATE_CONNECT_PROC;
3363
3364
3365
3366
3367 /******** PROCEDURE GENERATE_DISCONNECT_PROC ***********/
3368 /*
3369 * Author: V.Rajaram
3370 * Date Created: July 27 1998
3371 *
3372 * INPUT: Procedure Name, Input String (user written procedure)
3373 * OUTPUT: Err_code, Error String
3374 *
3375 * This procedure takes the user written disconnect procedure string and the procedure name
3376 * as input and generates the disconnect actual procedure.
3377 * It also generates any syntax errors in the actual PL/SQL block written by the user.
3378 *
3379 * Usage: In forms. After the sdp_find_connect_parameters procedure went successfully.
3380 */
3381
3382 PROCEDURE GENERATE_DISCONNECT_PROC (ProcName in varchar2,
3383 ProcBody in varchar2,
3384 CompiledProc OUT NOCOPY varchar2,
3385 ErrCode OUT NOCOPY number,
3386 ErrStr OUT NOCOPY varchar2)
3387
3388 IS
3389 -- PL/SQL Block
3390 l_dummy number;
3391
3392 l_temp_str varchar2(32767);
3393
3394 l_count number;
3395 l_cnt number;
3396 l_done number;
3397 l_start number;
3398
3399 l_final_str varchar2(32767);
3400 l_replace_send_str varchar2(500);
3401 l_disconnect_str varchar2(500);
3402 l_end_str varchar2(500);
3403
3404 l_sync_str varchar2(500);
3405
3406 l_str_before_end varchar2(32767);
3407 l_str_after_end varchar2(4000);
3408 l_end_loc number;
3409
3410 l_str_before_send varchar2(32767);
3411 l_str_after_send varchar2(4000);
3412 l_send_loc number;
3413 l_sync_loc number;
3414
3415 l_str_before_lastsend varchar2(32767);
3416 l_str_after_lastsend varchar2(32767);
3417 l_lastsend_loc number;
3418
3419 l_str_before_declare varchar2(32767);
3420 l_str_after_declare varchar2(32767);
3421 l_declare_loc number;
3422
3423 begin
3424 ErrCode := 0;
3425 ErrStr := NULL;
3426
3427 l_temp_str := ProcBody;
3428
3429 /*
3430 * Replace user written Send to comply with the actual spec of those procedures
3431 * Remove the user written DECLARE as we will be generating the procedure on the fly
3432 */
3433
3434 l_replace_send_str := 'XDP_PROC_CTL.SEND_CONNECT(Fe_Name, ' || g_new_line ||
3435 ' Channel_Name, ' || g_new_line ||
3436 '''' || ProcName ||''', ' || g_new_line ||
3437 ' sdp_internal_response, ' || g_new_line ||
3438 ' sdp_internal_err_code, ' || g_new_line ||
3439 ' sdp_internal_err_str, ';
3440
3441 l_sync_str := 'XDP_PROC_CTL.RESET_BUFFER; ' || g_new_line;
3442
3443
3444 /*
3445 * Replace the strings here...
3446 */
3447 l_temp_str := REPLACE(l_temp_str, 'SEND(', l_replace_send_str);
3448
3449 /*
3450 * Need to find the DECLARE word location and delete the users declare string (can be case sensitive)
3451 */
3452
3453 l_declare_loc := INSTR(UPPER(l_temp_str), 'DECLARE', 1, 1);
3454 IF l_declare_loc <> 0 THEN
3455 l_str_before_declare := SUBSTR(l_temp_str, 1, l_declare_loc - 1);
3456 l_str_after_declare := SUBSTR(l_temp_str, l_declare_loc + 7, LENGTH(l_temp_str));
3457
3458 l_temp_str := l_str_before_declare || ' ' || l_str_after_declare;
3459 end IF;
3460
3461
3462 /*
3463 * Construct the procedure
3464 */
3465 if fnd_profile.defined('XDP_FP_SPEC') then
3466 fnd_profile.get('XDP_FP_SPEC', l_final_str);
3467 l_final_str := ' Procedure ' || ProcName || ' ( '|| g_new_line || l_final_str || ' ) ' || g_new_line ||
3468 'IS ' || g_new_line ||
3469 ' sdp_internal_response varchar2(32767); ' || g_new_line || ' ';
3470 else
3471
3472 l_final_str := ' Procedure ' || ProcName || ' ( ' || g_new_line ||
3473 ' Fe_Name in varchar2, ' || g_new_line ||
3474 ' Channel_Name in varchar2, ' || g_new_line ||
3475 ' sdp_internal_err_code out number, ' || g_new_line ||
3476 ' sdp_internal_err_str out varchar2 ' || ' ) ' || g_new_line ||
3477 'IS ' || g_new_line ||
3478 ' sdp_internal_response varchar2(32767); ' || g_new_line || ' ';
3479
3480 end if;
3481
3482 l_final_str := l_final_str || l_temp_str;
3483
3484
3485 /*
3486 * Find the first users's DISCONNECT string and insert the RESET_BUFFER procedure call
3487 * before the first call to send.
3488 */
3489 l_sync_loc := INSTR(UPPER(l_final_str), 'XDP_PROC_CTL.SEND_CONNECT', 1, 1);
3490
3491 IF l_sync_loc <> 0 THEN
3492 l_str_before_send := SUBSTR(l_final_str, 1, l_sync_loc -1);
3493 l_str_after_send := SUBSTR(l_final_str, l_sync_loc, LENGTH(l_final_str));
3494
3495 l_final_str := l_str_before_send || l_sync_str || l_str_after_send;
3496 end IF;
3497
3498 CompiledProc := l_final_str;
3499
3500 ErrCode := 0;
3501 ErrStr := 'Successfully generated the stored procedure ' || ProcName;
3502 exception
3503 when others then
3504 ErrCode := SQLCODE;
3505 ErrStr := 'Unhandled Exception in XDP_PROC_CTL.GENERATE_DISCONNECT_PROC. Error: ' || SUBSTR(SQLERRM,1,200);
3506 end GENERATE_DISCONNECT_PROC;
3507
3508
3509 Procedure RESET_BUFFER
3510 is
3511
3512 begin
3513
3514 pv_ConnectCommands.delete;
3515
3516 end RESET_BUFFER;
3517
3518 PROCEDURE FETCH_CONNECT_COMMANDS(CurrIndex in number,
3519 TotalCount OUT NOCOPY number,
3520 Command OUT NOCOPY varchar2,
3521 Response OUT NOCOPY varchar2)
3522 is
3523
3524 begin
3525 xdp_macros.FETCH_CONNECT_COMMANDS(CurrIndex,
3526 TotalCount,
3527 Command,
3528 Response);
3529 -- This is the new Procedure Builder Implementation
3530 -- Comment out the old code.
3531 /*
3532 TotalCount := pv_ConnectCommands.count;
3533
3534 if TotalCount > 0 then
3535 Command := pv_ConnectCommands(CurrIndex).Command;
3536 Response := pv_ConnectCommands(CurrIndex).Response;
3537 else
3538 Command := null;
3539 Response := null;
3540 end if;
3541 */
3542 end FETCH_CONNECT_COMMANDS;
3543
3544
3545 begin
3546
3547 pv_AckTimeout := 60;
3548 pv_MesgTimeout := 90;
3549 pv_ack_conn_timeout := 30;
3550 pv_cmd_conn_timeout := 45;
3551 pv_debug_mode := 'Y';
3552 pv_DirtyBit := FALSE;
3553
3554 reset_buffer;
3555
3556 end XDP_PROC_CTL;