1 PACKAGE BODY XDP_PROCEDURE_BUILDER_UTIL AS
2 /* $Header: XDPPRBUB.pls 120.1 2005/06/24 17:29:13 appldev ship $ */
3
4 g_new_line CONSTANT VARCHAR2(10) := convert(FND_GLOBAL.LOCAL_CHR(10),
5 substr(userenv('LANGUAGE'), instr(userenv('LANGUAGE'),'.') +1),
6 'WE8ISO8859P1') ;
7
8 type ParamLocation IS RECORD
9 ( Location number,
10 ParamName varchar2(80),
11 Verified varchar2(1) );
12
13 type ParamLocations is table of ParamLocation
14 INDEX BY BINARY_INTEGER;
15
16 g_ParamLocations ParamLocations;
17
18 g_FaParams varchar2(10) := '$FA.';
19 g_FaParam varchar2(10) := 'FA';
20 g_WIParams varchar2(10) := '$WI.';
21 g_WIParam varchar2(10) := 'WI';
22 g_OrderParams varchar2(10) := '$ORDER.';
23 g_OrderParam varchar2(10) := 'ORDER';
24 g_LineParams varchar2(10) := '$LINE.';
25 g_LineParam varchar2(10) := 'LINE';
26
27 g_FEAttr varchar2(10) := '$';
28
29 -- These are the translations for macros
30 g_replace_get_param_str varchar2(200) := 'XDP_MACROS.GET_PARAM_VALUE';
31 g_replace_send_str varchar2(200) := 'XDP_MACROS.SEND';
32 g_replace_send_http_str varchar2(200) := 'XDP_MACROS.SEND_HTTP';
33 g_replace_resp_str varchar2(200) := 'XDP_MACROS.RESPONSE_CONTAINS';
34 g_replace_notify_str varchar2(200) := 'XDP_MACROS.NOTIFY_ERROR';
35 g_replace_get_response_str varchar2(200) := 'XDP_MACROS.GET_RESPONSE';
36
37 g_replace_connect_str varchar2(200) := 'XDP_MACROS.SEND_CONNECT';
38 g_replace_get_attr_str varchar2(200) := 'XDP_MACROS.GET_ATTR_VALUE';
39
40 -- New for 11.5.6+
41 g_replace_get_longresp_str varchar2(200) := 'XDP_MACROS.GET_LONG_RESPONSE';
42 g_replace_audit_str varchar2(200) := 'XDP_MACROS.AUDIT';
43
44 cursor gc_getFAParams(FAID number, Param varchar2) is
45 select 'Y' "FOUND"
46 from XDP_FA_PARAMETERS XFA
47 where FULFILLMENT_ACTION_ID = FAID
48 and upper(XFA.PARAMETER_NAME) = upper(Param) ;
49
50 cursor gc_getParamPool(Param varchar2) is
51 select 'Y' "FOUND"
52 from CSI_LOOKUPS
53 where lookup_type = 'CSI_EXTEND_ATTRIB_POOL'
54 and upper(LOOKUP_CODE) = upper(Param);
55
56 cursor gc_getFeAttr(FeTypeID number, FEAttr varchar2) is
57 select 'Y' "FOUND"
58 from XDP_FE_ATTRIBUTE_DEF xad, XDP_FE_SW_GEN_LOOKUP xfl
59 where xad.FE_SW_GEN_LOOKUP_ID = xfl.FE_SW_GEN_LOOKUP_ID
60 and xfl.FETYPE_ID = FeTypeID
61 and upper(xad.FE_ATTRIBUTE_NAME) = upper(FEAttr);
62
63 Procedure CleanupParameterLocations;
64
65 -- Private Routines for Validation of Parameters
66
67 Procedure GetParametersLocations( p_Parameter in varchar2,
68 p_ProcText in varchar2);
69
70 Procedure ValidateFAParameters( p_FAID in number,
71 p_ProcText in varchar2,
72 x_ErrorCode OUT NOCOPY number,
73 x_ErrorString OUT NOCOPY varchar2);
74
75 Procedure ValidateWIParameters( p_ProcText in varchar2,
76 x_ErrorCode OUT NOCOPY number,
77 x_ErrorString OUT NOCOPY varchar2);
78
79 Procedure ValidateFEAttr( p_ID in number,
80 p_ProcText in varchar2,
81 x_ErrorCode OUT NOCOPY number,
82 x_ErrorString OUT NOCOPY varchar2);
83
84 Procedure FetchParameters(p_ProcText in varchar2,
85 p_ParamType in varchar2);
86
87 Procedure VerifyFAParams(p_FAID in number,
88 x_ErrorCode OUT NOCOPY number,
89 x_ErrorString OUT NOCOPY varchar2);
90
91 Procedure VerifyPoolParams(x_ErrorCode OUT NOCOPY number,
92 x_ErrorString OUT NOCOPY varchar2);
93
94 Procedure VerifyFEAttr( p_FeTypeID in number,
95 x_ErrorCode OUT NOCOPY number,
96 x_ErrorString OUT NOCOPY varchar2);
97
98 Procedure GetValidParamLocation(p_CmdString in varchar2,
99 x_ParamLoc OUT NOCOPY number,
100 x_ParamType OUT NOCOPY varchar2);
101
102 Procedure GetParamValue(p_ID in number,
103 p_ParamType in varchar2,
104 p_ParamName in varchar2,
105 x_ParamValue OUT NOCOPY varchar2,
106 x_ParamLogFlag OUT NOCOPY varchar2);
107
108 Function GetParamString(p_ProcText in varchar2,
109 p_Location in number) return varchar2;
110
111 Function IsValidParamChar(p_Char in varchar2) return boolean;
112
113 Procedure OKtoLog(p_ParamType in varchar2,
114 p_ID in number,
115 p_ParamName in varchar2,
116 p_LogFlag OUT NOCOPY varchar2);
117
118 Procedure ReplaceFEAttr(p_FeName in varchar2,
119 p_CmdString in varchar2,
120 x_CmdStringReplaced OUT NOCOPY varchar2,
121 x_ErrorCode OUT NOCOPY number,
122 x_ErrorString OUT NOCOPY varchar2);
123
124 -- Public routines
125
126 -- This routine is used to check the validity of paramters used in
127 -- a Fulfillment Procedure
128 -- The validations to be done are only for WI and FA
129 -- The ID being passed is the FA ID
130 -- The WI Paramter validation is done from the pool
131 -- LINE and ORDER parameter validtion are NOT done!
132 Procedure ValidateFPParameters( p_ID in number,
133 p_ProcBody in varchar2,
134 x_ErrorCode OUT NOCOPY number,
135 x_ErrorString OUT NOCOPY varchar2)
136 is
137
138 l_WiParamFound number := 0;
139 l_FaParamFound number := 0;
140 begin
141 x_ErrorCode := 0;
142 x_ErrorString := null;
143
144 l_WiParamFound := INSTR(p_ProcBody, g_WIParams,1,1);
145 l_FaParamFound := INSTR(p_ProcBody,g_FAParams,1,1);
146
147 if l_FaParamFound > 0 then
148 -- FA Parameter Found....
149 -- Get the locations of the FA Paramaters
150 GetParametersLocations( p_Parameter => g_FaParams,
151 p_ProcText => p_ProcBody);
152
153 -- Validate the FA Paramters found
154 -- dbms_output.put_line('Validating FA Parameters..');
155 ValidateFAParameters(p_FAID => p_ID,
156 p_ProcText => p_ProcBody,
157 x_ErrorCode => x_ErrorCode,
158 x_ErrorString => x_ErrorString);
159
160 if x_ErrorCode <> 0 then
161 return;
162 end if;
163
164 end if;
165
166 if l_WiParamFound > 0 then
167 -- Work Item Parameter Found
168 -- Get the locations of the WI Paramaters
169 GetParametersLocations( p_Parameter => g_WIParams,
170 p_ProcText => p_ProcBody);
171
172 -- Validate the WI Paramters found
173 -- dbms_output.put_line('Validating WI Parameters..');
174 ValidateWIParameters(p_ProcText => p_ProcBody,
175 x_ErrorCode => x_ErrorCode,
176
177 x_ErrorString => x_ErrorString);
178
179 if x_ErrorCode <> 0 then
180 return;
181 end if;
182 end if;
183
184 end ValidateFPParameters;
185
186
187
188 -- This is used at run-time to get the paramter values. The macros
189 -- SEND and GET_PARAM_VALUE use this routine to get the command string
190 -- The strings used for logging are also setup.
191 Procedure ReplaceOrderParameters(p_OrderID in number,
192 p_LineItemID in number,
193 p_WIInstanceID in number,
194 p_FAInstanceID in number,
195 p_CmdString in varchar2,
196 x_CmdStringReplaced OUT NOCOPY varchar2,
197 x_CmdStringLog OUT NOCOPY varchar2,
198 x_ErrorCode OUT NOCOPY number,
199 x_ErrorString OUT NOCOPY varchar2)
200 is
201
202 l_ActualParam varchar2(50);
203 l_ParamType varchar2(50);
204 l_save_str varchar2(32767);
205 l_ParamLoc number;
206 l_ParamEndLoc number;
207 l_param_value varchar2(4000);
208 l_param_log_value varchar2(4000);
209 l_param_log_flag varchar2(20);
210
211 l_CmdB4Param varchar2(4000);
212 l_CmdAfterParam varchar2(4000);
213
214 l_ID number;
215
216 begin
217
218 -- dbms_output.put_line('REPLACE: Cmd: ' || substr(p_CmdString,1,200));
219 -- dbms_output.put_line(xdp_macros.pv_OrderID || ':' ||
220 -- xdp_macros.pv_LineItemID || ':' ||
221 -- xdp_macros.pv_WorkItemInstanceID || ':' ||
222 -- xdp_macros.pv_FAInstanceID);
223 x_ErrorCode := 0;
224 x_ErrorString := null;
225
226 if p_CmdString is null then
227 return;
228 end if;
229
230 l_save_str := p_CmdString;
231 while (true) loop
232 -- Get a Valid parameter location
233 GetValidParamLocation(l_save_str, l_ParamLoc, l_ParamType);
234 if l_ParamLoc = 0 then
235 -- Done!!
236 if l_save_str = ' ' then
237 exit;
238 else
239 x_CmdStringReplaced := x_CmdStringReplaced || l_save_str;
240 x_CmdStringLog := x_CmdStringLog || l_save_str;
241 exit;
242 end if;
243 end if;
244
245 -- Found a Valid Parameter here..
246
247 -- Construct Command String
248 l_CmdB4Param := substr(l_save_str, 1, l_ParamLoc-1);
249 l_CmdAfterParam := substr(l_save_str, l_ParamLoc+1 , length(l_save_str));
250
251 x_CmdStringReplaced := x_CmdStringReplaced || l_CmdB4Param;
252 x_CmdStringLog := x_CmdStringLog || l_CmdB4Param;
253
254 -- Get the Actual parameter
255 -- dbms_output.put_line('Param type:' || l_ParamType || ':');
256 l_ActualParam := GetParamString(l_CmdAfterParam, length(l_ParamType) + 2 );
257
258 -- dbms_output.put_line('Param:' || l_ActualParam || ':');
259 l_ParamEndLoc := l_ParamLoc + length(l_ActualParam) + length(l_ParamType) + 2;
260
261 -- Prepare the rest of strings
262 l_CmdAfterParam := substr(l_save_str, l_ParamEndLoc , length(l_save_str));
263 l_save_str := l_CmdAfterParam;
264
265 -- Get the parameter values
266 if l_ParamType = g_FAParam then
267 l_ID := p_FAInstanceID;
268 elsif l_ParamType = g_WIParam then
269 l_ID := p_WIInstanceID;
270 elsif l_ParamType = g_LineParam then
271 l_ID := p_LineItemID;
272 elsif l_ParamType = g_OrderParam then
273 l_ID := p_OrderID;
274 end if;
275
276 -- Get the value of the paramter found
277 GetParamValue(
278 p_ID => l_ID,
279 p_ParamType => l_ParamType,
280 p_ParamName => l_ActualParam,
281 x_ParamValue => l_Param_Value,
282 x_ParamLogFlag => l_Param_Log_Flag);
283
284 --dbms_output.put_line('AFTER Get Value');
285 --dbms_output.put_line(xdp_macros.pv_OrderID || ':' ||
286 -- xdp_macros.pv_LineItemID || ':' ||
287 -- xdp_macros.pv_WorkItemInstanceID || ':' ||
288 -- xdp_macros.pv_FAInstanceID);
289
290 x_CmdStringReplaced := x_CmdStringReplaced || l_Param_Value;
291 if l_Param_Log_Flag = 'Y' then
292 x_CmdStringLog := x_CmdStringLog || l_Param_Value;
293 else
294 x_CmdStringLog := x_CmdStringLog||'$'||l_paramType||'.'||l_ActualParam;
295 end if;
296
297
298 -- dbms_output.put_line('Replaced String:' || x_CmdStringReplaced||':');
299 -- dbms_output.put_line('Log String: ' || x_CmdStringLog);
300 -- dbms_output.put_line('Saved String: ' || l_save_str || 'Len' || length(l_save_str));
301
302 -- Check if the String Search is done...
303 if l_save_str is null then
304 exit;
305 elsif length(l_save_str) = 0 then
306 exit;
307 end if;
308 end loop;
309
310 x_ErrorCode := 0;
311 x_ErrorString := null;
312
313 -- dbms_output.put_line('Cmd:' || x_CmdStringReplaced||':');
314 -- dbms_output.put_line('Log:' || x_CmdStringLog||':');
315
316 end ReplaceOrderParameters;
317
318
319
320 -- Get the next valid paramter location. The $variables are scanned
321 -- and the first valid parameter location is returned. The parameter
322 -- type is also returned.
323 -- Valid parameters are $WI $FA $ORDER and $LINE
324 -- for e.g 'This is a test for $XDP.test and $WI.SP_NAME'
325 -- ^
326 -- the location of $WI is returned. $XDP is ignored
327 Procedure GetValidParamLocation(p_CmdString in varchar2,
328 x_ParamLoc OUT NOCOPY number,
329 x_ParamType OUT NOCOPY varchar2)
330 is
331
332 l_StartLoc number;
333 begin
334 l_StartLoc := 1;
335
336 while (true) loop
337 -- Check for the first $ location
338 x_ParamLoc := INSTR(p_CmdString, '$', l_StartLoc);
339 if x_ParamLoc = 0 then
340 -- No Params Found
341 exit;
342 end if;
343 -- Check if the $ reference is for WI or FA
344 -- If so Exit
345 x_ParamType := SUBSTR(p_CmdString, x_ParamLoc + 1, 2);
346 if x_ParamType in('FA', 'WI') then
347 -- Valid params
348 exit;
349 end if;
350
351 -- Check if the $ reference is for LINE
352 -- If so Exit
353 x_ParamType := SUBSTR(p_CmdString, x_ParamLoc + 1, 4);
354 if x_ParamType in ('LINE') then
355 -- Valid params
356 exit;
357 end if;
358
359 -- Check if the $ reference is for ORDER
360 -- If so Exit
361 x_ParamType := SUBSTR(p_CmdString, x_ParamLoc + 1, 5);
362 if x_ParamType in ('ORDER') then
363 -- Valid params
364 exit;
365 end if;
366
367 -- The $ is not a valid parameter reference
368 -- Continue to find the next $ occurence
369 l_StartLoc := x_ParamLoc + 1;
370
371 end loop;
372
373 end GetValidParamLocation;
374
375
376 -- Validate the FE Attributes
377 -- This is used when the connect and disconnect procedures are being
378 -- generated
379 Procedure ValidateFEAttributes( p_FeTypeID in number,
380 p_ProcBody in varchar2,
381 x_ErrorCode OUT NOCOPY number,
382 x_ErrorString OUT NOCOPY varchar2)
383 is
384
385 l_FeAttrFound number := 0;
386 begin
387
388 x_ErrorCode := 0;
389 x_ErrorString := null;
390
391 l_FeAttrFound := INSTR(p_ProcBody, g_FEAttr,1,1);
392
393 if l_FeAttrFound > 0 then
394 -- FE Attributes Found
395 -- Get the locations of the FE Attributes
396 GetParametersLocations( p_Parameter => g_FEAttr,
397 p_ProcText => p_ProcBody);
398
399 -- Validate the FE Attributes with the FE Type
400 -- dbms_output.put_line('Validating FE Attributes..');
401 ValidateFEAttr(p_ID => p_FeTypeID,
402 p_ProcText => p_ProcBody,
403 x_ErrorCode => x_ErrorCode,
404 x_ErrorString => x_ErrorString);
405
406 if x_ErrorCode <> 0 then
407 return;
408 end if;
409
410 end if;
411
412 end ValidateFEAttributes;
413
414
415 -- This is used at run-time to get the paramter values. The macros
416 -- SEND and GET_PARAM_VALUE ONLY in Connection and Disconnection routines
417 -- ------------------------------------
418 -- use this routine to get the command string
419 Procedure ReplaceFEAttributes( p_FeName in varchar2,
420 p_CmdString in varchar2,
421 x_CmdStringReplaced OUT NOCOPY varchar2,
422 x_ErrorCode OUT NOCOPY number,
423 x_ErrorString OUT NOCOPY varchar2)
424 is
425 l_FeAttrFound number := 0;
426 begin
427
428 x_ErrorCode := 0;
429 x_ErrorString := null;
430
431 l_FeAttrFound := INSTR(p_CmdString, g_FEAttr,1,1);
432
433 if l_FeAttrFound > 0 then
434 -- FE Attributes Found
435 -- Get the locations of the FE Attributes
436 GetParametersLocations( p_Parameter => g_FEAttr,
437 p_ProcText => p_CmdString);
438
439 -- dbms_output.put_line('Validating FE Attributes..');
440 -- Replace the FE Attributes with their values
441 ReplaceFEAttr(p_FeName => p_FeName,
442 p_CmdString => p_CmdString,
443 x_CmdStringReplaced => x_CmdStringReplaced,
444 x_ErrorCode => x_ErrorCode,
445 x_ErrorString => x_ErrorString);
446
447 if x_ErrorCode <> 0 then
448 return;
449 end if;
450
451 else
452 x_CmdStringReplaced := p_CmdString;
453
454 end if;
455
456 end ReplaceFEAttributes;
457
458
459 Procedure ReplaceFEAttr(p_FeName in varchar2,
460 p_CmdString in varchar2,
461 x_CmdStringReplaced OUT NOCOPY varchar2,
462 x_ErrorCode OUT NOCOPY number,
463 x_ErrorString OUT NOCOPY varchar2)
464 is
465 l_FeAttrValue varchar2(4000);
466 l_TempCmdString varchar2(4000);
467 begin
468
469 x_ErrorCode := 0;
470 x_ErrorString := null;
471
472 if g_ParamLocations.count > 0 then
473 FetchParameters(p_ProcText => p_CmdString,
474 p_ParamType => g_FEAttr);
475 else
476 x_CmdStringReplaced := p_CmdString;
477 return;
478 end if;
479
480 l_TempCmdString := p_CmdString;
481 x_CmdStringReplaced := p_CmdString;
482
483 for i in 1..g_ParamLocations.count loop
484 l_FeAttrValue := xdp_engine.get_fe_attributeval(
485 p_FeName, upper(g_ParamLocations(i).ParamName));
486
487 x_CmdStringReplaced :=
488 replace(l_TempCmdString,
489 g_FEAttr || g_ParamLocations(i).ParamName,
490 l_FeAttrValue);
491
492 l_TempCmdString := x_CmdStringReplaced;
493
494 end loop;
495
496 exception
497 when others then
498 x_ErrorCode := sqlcode;
499 x_ErrorString := substr(sqlerrm,1,2000);
500 end ReplaceFEAttr;
501
502 -- Cleanup
503 Procedure CleanupParameterLocations
504 is
505
506 begin
507 g_ParamLocations.delete;
508
509 end CleanupParameterLocations;
510
511
512 -- Get all the locations of the parameter types in a string
513 -- Valid Parameter types are $WI. $FA. $ORDER. $LINE.
514 -- This will be used for checking the validy of the paramters
515
516 Procedure GetParametersLocations( p_Parameter in varchar2,
517 p_ProcText in varchar2)
518 is
519 l_CurrLocation number := 0;
520 l_NextLocation number := 0;
521 l_ParamCount number := 0;
522
523 l_Paramoffset number := length(p_Parameter) + 1;
524
525 NoMoreOccurences boolean := true;
526 begin
527
528 g_ParamLocations.delete;
529
530 l_CurrLocation := INSTR(p_ProcText, p_Parameter, 1, 1);
531 if l_CurrLocation = 0 then
532 return;
533 end if;
534
535 -- We have found occurance of the parameter type
536 l_CurrLocation := 1;
537
538 while (NoMoreOccurences) loop
539
540 -- Get the Next occurence of the parameter
541
542 l_NextLocation := INSTR(p_ProcText, p_Parameter, l_CurrLocation, 1);
543
544 if l_NextLocation = 0 then
545 NoMoreOccurences := false;
546 else
547 l_ParamCount := l_ParamCount + 1;
548 g_ParamLocations(l_ParamCount).location := l_NextLocation;
549 g_ParamLocations(l_ParamCount).Verified := 'N';
550 l_CurrLocation := l_NextLocation + l_Paramoffset;
551 end if;
552
553 end loop;
554
555 end GetParametersLocations;
556
557 -- This routine checks the Validity of the FA Parameters in a
558 -- Procedure.
559 -- If there are any FA parameters these are checked against the
560 -- configuration for that FA
561 -- The FA ID needs to be passed
562 Procedure ValidateFAParameters( p_FAID in number,
563 p_ProcText in varchar2,
564 x_ErrorCode OUT NOCOPY number,
565 x_ErrorString OUT NOCOPY varchar2)
566 is
567
568 begin
569 x_ErrorCode := 0;
570 x_ErrorString := null;
571
572 -- Check the validity only if there are any FA parameters
573 -- The g_ParamLocations is populated by GetParametersLocations routine
574 if g_ParamLocations.count > 0 then
575 -- Get all the FA Parameters based on their location
576 FetchParameters(p_ProcText => p_ProcText,
577 p_ParamType => g_FAParams);
578
579 -- Check against the Configuration
580 VerifyFAParams( p_FAID => p_FAID,
581 x_ErrorCode => x_ErrorCode,
582 x_ErrorString => x_ErrorString);
583 else
584 return;
585 end if;
586
587 end ValidateFAParameters;
588
589
590 -- This routine checks the Validity of the WI Parameters in a
591 -- Procedure.
592 -- If there are any WI parameters these are checked against the
593 -- configuration for that WI.
594 -- The WI Parameters are checked against the Parameter Pool
595 -- Hence no Workitem ID needs to be passed
596 Procedure ValidateWIParameters( p_ProcText in varchar2,
597 x_ErrorCode OUT NOCOPY number,
598 x_ErrorString OUT NOCOPY varchar2)
599 is
600
601 begin
602 x_ErrorCode := 0;
603 x_ErrorString := null;
604
605 -- Check the validity only if there are any WI parameters
606 -- The g_ParamLocations is populated by GetParametersLocations routine
607 if g_ParamLocations.count > 0 then
608 -- Get all the WI Parameters based on their location
609 FetchParameters(p_ProcText => p_ProcText,
610 p_ParamType => g_WIParams);
611
612 -- Check against the Configuration
613 VerifyPoolParams(x_ErrorCode => x_ErrorCode,
614 x_ErrorString => x_ErrorString);
615 else
616 -- No Parameters found.
617
618 return;
619 end if;
620
621 end ValidateWIParameters;
622
623
624 -- This routine checks the Validity of the FE Attributes in a
625 -- Procedure.
626 -- If there are any FE Attributes, these are checked against the
627 -- configuration for that FE Type.
628 -- The FE Type ID needs to be passed
629 Procedure ValidateFEAttr( p_ID in number,
630 p_ProcText in varchar2,
631 x_ErrorCode OUT NOCOPY number,
632 x_ErrorString OUT NOCOPY varchar2)
633 is
634
635 begin
636 x_ErrorCode := 0;
637 x_ErrorString := null;
638
639 -- Check the validity only if there are any FE attriutes
640 -- The g_ParamLocations is populated by GetParametersLocations routine
641
642 if g_ParamLocations.count > 0 then
643 -- Get all the FE Attributes based on their location
644 FetchParameters(p_ProcText => p_ProcText,
645 p_ParamType => g_FEAttr);
646
647 -- Check against the configuration
648 VerifyFEAttr(p_FeTypeID => p_ID,
649 x_ErrorCode => x_ErrorCode,
650 x_ErrorString => x_ErrorString);
651 else
652 return;
653 end if;
654
655 end ValidateFEAttr;
656
657 -- This routine is used to identify all the Potential
658 -- Parameters/attributes based on the locations in the procedure
659 -- for e.g. if the procedure has 'This is a test for $WI.PARAM and $FA.FA_PARAM'
660 -- This routine will identify PARAM as a WorkItem parameter and
661 -- FA_PARAM as an FA parameter
662 -- The parameter starting locations are populated by the
663 -- GetParametersLocation routine
664 Procedure FetchParameters(p_ProcText in varchar2,
665 p_ParamType in varchar2)
666 is
667 l_Param varchar2(80);
668 l_ParamCount number := 1;
669 begin
670 for i in 1..g_ParamLocations.count loop
671 -- Get the parameter string starting from the location
672 l_Param := GetParamString(p_ProcText => p_ProcText,
673 p_Location => g_ParamLocations(i).location
674 + length(p_ParamType) );
675
676 g_ParamLocations(i).ParamName := l_Param;
677 end loop;
678 end FetchParameters;
679
680 -- This routine checks the validity of ALL the FA parameters
681 -- identified against the FA Configuration
682 -- The FetchParameters routine identifies all the FA Parameters
683 Procedure VerifyFAParams(p_FAID in number,
684 x_ErrorCode OUT NOCOPY number,
685 x_ErrorString OUT NOCOPY varchar2)
686 is
687 l_FoundFlag varchar(1) := 'N';
688 begin
689 x_ErrorCode := 0;
690 x_ErrorString := null;
691
692 for i in 1..g_ParamLocations.count loop
693 for v_getFAParams in gc_getFAParams(
694 p_FAID, g_ParamLocations(i).ParamName) loop
695 l_FoundFlag := v_getFAParams.FOUND;
696 end loop;
697
698 if l_FoundFlag = 'Y' then
699 -- Param is valid OK...
700 l_FoundFlag := 'N';
701 else
702 -- Params in not valid
703 x_ErrorCode := -1;
704 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_PARAMETER');
705 FND_MESSAGE.SET_TOKEN('PARAMETER_NAME', g_ParamLocations(i).ParamName);
706 FND_MESSAGE.SET_TOKEN('PARAMETER_TYPE',
707 xdp_procedure_builder.pv_ParamFADisp);
708 x_ErrorString := FND_MESSAGE.GET;
709 exit;
710 end if;
711
712 end loop;
713
714 end VerifyFAParams;
715
716
717 -- This routine checks the validity of ALL the WI parameters
718 -- identified against the Parameter Pool
719 -- The FetchParameters routine identifies all the WI Parameters
720 Procedure VerifyPoolParams( x_ErrorCode OUT NOCOPY number,
721 x_ErrorString OUT NOCOPY varchar2)
722 is
723 l_FoundFlag varchar(1) := 'N';
724 begin
725 x_ErrorCode := 0;
726 x_ErrorString := null;
727
728 for i in 1..g_ParamLocations.count loop
729
730 for v_getParamPool in gc_getParamPool(
731 g_ParamLocations(i).ParamName) loop
732 l_FoundFlag := v_getParamPool.FOUND;
733 end loop;
734
735 if l_FoundFlag = 'Y' then
736 -- Param is valid OK...
737 l_FoundFlag := 'N';
738 else
739 -- Params in not valid
740 x_ErrorCode := -1;
741 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_PARAMETER');
742 FND_MESSAGE.SET_TOKEN('PARAMETER_NAME', g_ParamLocations(i).ParamName);
743 FND_MESSAGE.SET_TOKEN('PARAMETER_TYPE',
744 xdp_procedure_builder.pv_ParamWIDisp);
745 x_ErrorString := FND_MESSAGE.GET;
746 exit;
747 end if;
748 end loop;
749
750 end VerifyPoolParams;
751
752
753 -- This routine checks the validity of ALL the FE attributes
754 -- identified against the FE Configuration
755 -- The FetchParameters routine identifies all the FE Attributes
756 Procedure VerifyFEAttr( p_FeTypeID in number,
757 x_ErrorCode OUT NOCOPY number,
758 x_ErrorString OUT NOCOPY varchar2)
759 is
760 l_FoundFlag varchar(1) := 'N';
761 begin
762 x_ErrorCode := 0;
763 x_ErrorString := null;
764
765 for i in 1..g_ParamLocations.count loop
766 for v_getFeAttr in gc_getFeAttr(
767 p_FeTypeID, g_ParamLocations(i).ParamName) loop
768 l_FoundFlag := v_getFeAttr.FOUND;
769 end loop;
770
771 if l_FoundFlag = 'Y' then
772 -- Param is valid OK...
773 l_FoundFlag := 'N';
774 else
775 -- Params in not valid
776 x_ErrorCode := -1;
777 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_PARAMETER');
778 FND_MESSAGE.SET_TOKEN('PARAMETER_NAME', g_ParamLocations(i).ParamName);
779 FND_MESSAGE.SET_TOKEN('PARAMETER_TYPE',
780 xdp_procedure_builder.pv_ParamFEDisp);
781 x_ErrorString := FND_MESSAGE.GET;
782 exit;
783 end if;
784
785 end loop;
786
787 end VerifyFEAttr;
788
789
790 -- Based on the ID and type of the paramter. Obtain the paramter values
791 -- The log/nolog configuration for the paramter is also obtained.
792 Procedure GetParamValue(p_ID in number,
793 p_ParamType in varchar2,
794 p_ParamName in varchar2,
795 x_ParamValue OUT NOCOPY varchar2,
796 x_ParamLogFlag OUT NOCOPY varchar2)
797 is
798 begin
799
800 -- dbms_output.put_line('Getting the param value of:' || p_ParamType
801 -- || ':' || p_ID || ':' || p_ParamName);
802
803 x_ParamLogFlag := 'Y';
804 if p_ParamType= g_OrderParam then
805 x_ParamValue := xdp_engine.get_order_param_value(
806 p_ID,
807 upper(p_ParamName));
808 elsif p_ParamType = g_LineParam then
809 x_ParamValue := xdp_engine.get_line_param_value(
810 p_ID,
811 upper(p_ParamName));
812 elsif p_ParamType = g_FAParam then
813 x_ParamValue := xdp_engine.get_fa_param_value(
814 p_ID,
815 upper(p_ParamName));
816 -- dbms_output.put_line('Checking to Log FA..');
817 OKtoLog(p_ParamType => g_FAParam,
818 p_ID => p_ID,
819 p_ParamName => p_ParamName,
820 p_LogFlag => x_ParamLogFlag);
821
822 elsif p_ParamType = g_WIParam then
823 x_ParamValue := xdp_engine.get_workitem_param_value(
824 p_ID,
825 upper(p_ParamName));
826
827 -- dbms_output.put_line('Checking to Log WI..');
828 OKtoLog(p_ParamType => g_WIParam,
829 p_ID => p_ID,
830 p_ParamName => upper(p_ParamName),
831 p_LogFlag => x_ParamLogFlag);
832 -- dbms_output.put_line('AFTer Checking to Log WI..');
833 end if;
834
835 -- dbms_output.put_line('After Getting the param value of:' || p_ParamType
836 -- || ':' || p_ID || ':' || p_ParamName);
837 -- dbms_output.put_line('Value: ' || x_ParamValue || ':log: ' || x_ParamLogFlag);
838
839 end GetParamValue;
840
841
842 -- This routine is used to check if the Paramter configuration
843 -- for audit trail logging.
844 -- This is mainly used in the SEND macro where the logging is performed.
845 -- The Configuration information is cached and the routines fetches the
846 -- value from the param cache
847 Procedure OKtoLog(p_ParamType in varchar2,
848 p_ID in number,
849 p_ParamName in varchar2,
850 p_LogFlag OUT NOCOPY varchar2)
851 is
852 l_dummyChar varchar2(2000);
853
854 begin
855 -- The Logging of the parameter is done in the FP
856 -- Check if the Param Cache reqd flag is set.
857 -- The v_ParamCacheReqd flag is set by in the InitFP routing within the FP
858
859 -- dbms_output.put_line('Check Cache for: ' || p_ParamType || ' ' || p_ParamName);
860 if p_ParamType = g_WIParam then
861 if xdp_macros.pv_ParamCacheReqd = 'Y' then
862 -- dbms_output.put_line('Getting WI from Cache..');
863 xdp_param_cache.Get_wi_param_from_cache(
864 p_param_name => p_ParamName,
865 p_exists_in_cache => l_dummyChar,
866 p_param_value => l_dummyChar,
867 p_param_ref_value => l_dummyChar,
868 p_log_flag => p_LogFlag,
869 p_evaluation_mode => l_dummyChar,
870 p_evaluation_proc => l_dummyChar,
871 p_default_value => l_dummyChar);
872 else
873 -- dbms_output.put_line('Not Getting from Cache..');
874 p_LogFlag := 'Y';
875 end if;
876 -- dbms_output.put_line('Param: ' || p_ParamName || ' flag: ' || p_LogFlag);
877 elsif p_ParamType = g_FAParam then
878 if xdp_macros.pv_ParamCacheReqd = 'Y' then
879 -- dbms_output.put_line('Getting FA from Cache..');
880 xdp_param_cache.Get_fa_param_from_cache(
881 p_param_name => p_ParamName,
882 p_exists_in_cache => l_dummyChar,
883 p_param_value => l_dummyChar,
884 p_log_flag => p_LogFlag,
885 p_evaluation_proc => l_dummyChar,
886 p_default_value => l_dummyChar);
887 else
888 p_LogFlag := 'Y';
889 end if;
890 else
891 -- Return Default Flag to be 'Y'
892 p_LogFlag := 'Y';
893 end if;
894
895 end OKtoLog;
896
897
898 -- This routines identifies the parameter name starting from a location
899 -- within a string.
900 -- e.g. 'This is a test for $WI.PARAM and $FA.FA_PARAM'
901 -- ^(20) ^(34)
902 -- The routine identifies PARAM as a WI Parameter from the 20th location
903 -- and FA_PARAM as another parameter from 34th location
904 Function GetParamString(p_ProcText in varchar2,
905 p_Location in number) return varchar2
906 is
907 l_ProcSubString varchar2(80);
908 l_NextChar varchar2(1);
909 NotDone boolean := true;
910 l_CurLoc number := p_Location;
911 l_CurLength number := 0;
912 begin
913 while (NotDone) loop
914 -- Get the Next character
915 l_NextChar := substr(p_ProcText, l_CurLoc, 1);
916
917 -- Check if the character is a valid character from the
918 -- ASCII set
919 if IsValidParamChar(p_Char => l_NextChar) then
920 -- Valid Char. So continue..
921 l_CurLength := l_CurLength + 1;
922 else
923 l_ProcSubString :=
924 substr(p_ProcText, p_Location, l_CurLength);
925 NotDone := false;
926 end if;
927 l_CurLoc := l_CurLoc + 1;
928 end loop;
929
930 -- dbms_output.put_line('Found Param: ' || l_ProcSubString);
931 return (l_ProcSubString);
932
933 end GetParamString;
934
935 -- This routine is used when triyng to find a potential parameter
936 -- Only certain characters from the ASCII table are valid characters
937 Function IsValidParamChar(p_Char in varchar2) return boolean
938 is
939 l_Valid boolean := false;
940 l_AsciiVal number := ascii(p_Char);
941 begin
942 if l_AsciiVal in (95, 45, 46) then
943 -- Value is "_" and "-" and "."
944 l_Valid := true;
945 elsif l_AsciiVal >= 65 and l_AsciiVal <= 90 then
946 -- Value is between "A" and "Z"
947 l_Valid := true;
948 elsif l_AsciiVal >= 97 and l_AsciiVal <= 122 then
949 -- Value is between "a" and "z"
950 l_Valid := true;
951 elsif l_AsciiVal >= 48 and l_AsciiVal <= 57 then
952 -- Value is between "0" and "9"
953 l_Valid := true;
954 else
955 l_Valid := false;
956 end if;
957
958 return (l_Valid);
959
960 end IsValidParamChar;
961
962 --
963 --
964 -- This routine is used for transtation of default macros
965 -- These are:
966 -- 1. GET_PARAM_VALUE
967 Procedure TranslateDefMacros (ProcName in varchar2,
968 ProcStr in varchar2,
969 CompiledProc OUT NOCOPY varchar2)
970
971 IS
972 l_temp_str varchar2(32767);
973
974 l_str_before_declare varchar2(32767);
975 l_str_after_declare varchar2(32767);
976 l_declare_loc number;
977
978 begin
979 l_temp_str := ProcStr;
980
981 -- Find the DECLARE word location and delete the users declare string
982 -- (can be case sensitive)
983
984 l_declare_loc := INSTR(UPPER(l_temp_str), 'DECLARE', 1, 1);
985 IF l_declare_loc <> 0 then
986 l_str_before_declare := SUBSTR(l_temp_str, 1, l_declare_loc - 1);
987 l_str_after_declare := SUBSTR(l_temp_str, l_declare_loc + 7, LENGTH(l_temp_str));
988
989 l_temp_str := l_str_before_declare || ' ' || l_str_after_declare;
990 end IF;
991
992 -- Replace the Users GET_PARAM_VALUE references with the actual routine
993 l_temp_str := REPLACE(l_temp_str, 'GET_PARAM_VALUE', g_replace_get_param_str);
994
995 -- Construct the procedure
996 CompiledProc := CompiledProc || l_temp_str;
997
998 end TranslateDefMacros;
999
1000
1001 --
1002 -- This routine translates all the macros which can be used within
1003 -- a Fulfillment Procedure
1004 -- These are:
1005 -- 1. GET_PARAM_VALUE
1006 -- 2. SEND
1007 -- 3. SEND_HTTP
1008 -- 4. GET_RESPONSE
1009 -- 5. GET_LONG_RESPONSE
1010 -- 6. NOTIFY_ERROR
1011 -- 7. RESPONSE_CONTAINS
1012 -- 8. AUDIT
1013
1014 Procedure TranslateFPMacros (ProcName in varchar2,
1015 ProcStr in varchar2,
1016 CompiledProc OUT NOCOPY varchar2)
1017
1018 IS
1019 l_dummy number;
1020
1021 l_temp_str varchar2(32767);
1022
1023 l_final_str varchar2(32767);
1024 l_clash_str1 varchar2(500);
1025 l_replace_clash_str1 varchar2(500);
1026
1027 l_send_loc number;
1028
1029 l_str_before_declare varchar2(32767);
1030 l_str_after_declare varchar2(32767);
1031 l_declare_loc number;
1032
1033 begin
1034 l_temp_str := ProcStr;
1035
1036 -- These are chashing strings which needs to be replaced fisrt
1037 -- before the actual macro transtlation
1038 l_clash_str1 := '.SEND';
1039 l_replace_clash_str1 := '##XEND##';
1040
1041
1042 -- These are to Replacing the Clashing strings with the macros...
1043 l_temp_str := REPLACE(l_temp_str, xdp_procedure_builder.g_MacroSendHttp,'SFM_HTTP');
1044 l_temp_str := REPLACE(l_temp_str, l_clash_str1, l_replace_clash_str1);
1045
1046 -- Translate the Users macro calls into the actual calls
1047 l_temp_str := REPLACE(l_temp_str, xdp_procedure_builder.g_MacroSend,
1048 g_replace_send_str);
1049 l_temp_str := REPLACE(l_temp_str, 'SFM_HTTP', g_replace_send_http_str);
1050 l_temp_str := REPLACE(l_temp_str, xdp_procedure_builder.g_MacroResponseContains,
1051 g_replace_resp_str);
1052 l_temp_str := REPLACE(l_temp_str, xdp_procedure_builder.g_MacroNotifError,
1053 g_replace_notify_str);
1054 l_temp_str := REPLACE(l_temp_str, xdp_procedure_builder.g_MacroGetResp,
1055 g_replace_get_response_str);
1056 l_temp_str := REPLACE(l_temp_str, xdp_procedure_builder.g_MacroGetParam,
1057 g_replace_get_param_str);
1058 -- New for 11.5.6++
1059 l_temp_str := REPLACE(l_temp_str, xdp_procedure_builder.g_MacroGetLongResp,
1060 g_replace_get_longresp_str);
1061 l_temp_str := REPLACE(l_temp_str, xdp_procedure_builder.g_MacroAudit,
1062 g_replace_audit_str);
1063
1064 l_temp_str := REPLACE(l_temp_str, l_replace_clash_str1, l_clash_str1);
1065
1066 -- Need to find the DECLARE word location and delete the users
1067 -- declare string (can be case sensitive)
1068
1069 l_declare_loc := INSTR(UPPER(l_temp_str), 'DECLARE', 1, 1);
1070 IF l_declare_loc <> 0 then
1071 l_str_before_declare := SUBSTR(l_temp_str, 1, l_declare_loc - 1);
1072 l_str_after_declare := SUBSTR(l_temp_str, l_declare_loc + 7, LENGTH(l_temp_str));
1073
1074 l_temp_str := l_str_before_declare || ' ' || l_str_after_declare;
1075 end IF;
1076
1077 l_final_str := l_final_str || l_temp_str;
1078
1079 CompiledProc := l_final_str;
1080
1081 end TranslateFPMacros;
1082
1083
1084 --
1085 -- This routine translates all the macros which can be used within
1086 -- a Connection Procedure
1087 -- These are:
1088 -- 1. GET_PARAM_VALUE
1089 -- 2. SEND
1090 -- 3. LOGIN
1091 Procedure TranslateConnMacros (ProcName in varchar2,
1092 ProcBody in varchar2,
1093 CompiledProc OUT NOCOPY varchar2)
1094 IS
1095 -- PL/SQL Block
1096 l_dummy number;
1097
1098 l_temp_str varchar2(32767);
1099 l_temp_login_str varchar2(32767);
1100
1101 l_final_str varchar2(32767);
1102 l_replace_login_str varchar2(500);
1103 l_clash_str1 varchar2(500);
1104 l_clash_str2 varchar2(500);
1105 l_clash_str3 varchar2(500);
1106 l_replace_clash_str1 varchar2(500);
1107 l_replace_clash_str2 varchar2(500);
1108 l_replace_clash_str3 varchar2(500);
1109
1110 l_str_before_declare varchar2(32767);
1111 l_str_after_declare varchar2(32767);
1112 l_declare_loc number;
1113
1114 begin
1115
1116 l_temp_str := ProcBody;
1117
1118 -- These are chashing strings which needs to be replaced fisrt
1119 -- before the actual macro transtlation
1120 l_clash_str1 := '$LOGIN';
1121 l_replace_clash_str1 := '##XOGIN##';
1122 l_clash_str2 := '_LOGIN';
1123 l_replace_clash_str2 := '##_XOGIN##';
1124 l_clash_str3 := 'LOGIN_';
1125 l_replace_clash_str3 := '##XOGIN_##';
1126
1127 l_temp_str := REPLACE(l_temp_str, l_clash_str1, l_replace_clash_str1);
1128 l_temp_str := REPLACE(l_temp_str, l_clash_str2, l_replace_clash_str2);
1129 l_temp_str := REPLACE(l_temp_str, l_clash_str3, l_replace_clash_str3);
1130
1131 -- Translate the Users macro calls into the actual calls
1132 l_temp_str := REPLACE(l_temp_str, 'SEND', g_replace_connect_str);
1133 l_temp_str := REPLACE(l_temp_str, 'LOGIN', g_replace_connect_str);
1134 l_temp_str := REPLACE(l_temp_str, 'GET_PARAM_VALUE', g_replace_get_attr_str);
1135
1136 l_temp_str := REPLACE(l_temp_str, l_replace_clash_str3, l_clash_str3);
1137 l_temp_str := REPLACE(l_temp_str, l_replace_clash_str2, l_clash_str2);
1138 l_temp_str := REPLACE(l_temp_str, l_replace_clash_str1, l_clash_str1);
1139
1140 -- Need to find the DECLARE word location and delete the users
1141 -- declare string (can be case sensitive)
1142
1143 l_declare_loc := INSTR(UPPER(l_temp_str), 'DECLARE', 1, 1);
1144 IF l_declare_loc <> 0 THEN
1145 l_str_before_declare := SUBSTR(l_temp_str, 1, l_declare_loc - 1);
1146 l_str_after_declare := SUBSTR(l_temp_str, l_declare_loc + 7, LENGTH(l_temp_str));
1147
1148 l_temp_str := l_str_before_declare || ' ' || l_str_after_declare;
1149 end IF;
1150
1151
1152 l_final_str := l_final_str || l_temp_str;
1153
1154 CompiledProc := l_final_str;
1155
1156 end TranslateConnMacros;
1157
1158
1159
1160 --
1161 -- This routine translates all the macros which can be used within
1162 -- a Disconnection Procedure
1163 -- These are:
1164 -- 1. GET_PARAM_VALUE
1165 -- 2. SEND
1166 Procedure TranslateDisconnMacros (ProcName in varchar2,
1167 ProcBody in varchar2,
1168 CompiledProc OUT NOCOPY varchar2)
1169
1170 IS
1171 -- PL/SQL Block
1172 l_dummy number;
1173
1174 l_temp_str varchar2(32767);
1175
1176 l_final_str varchar2(32767);
1177 l_disconnect_str varchar2(500);
1178 l_end_str varchar2(500);
1179
1180 l_str_before_declare varchar2(32767);
1181 l_str_after_declare varchar2(32767);
1182 l_declare_loc number;
1183
1184 begin
1185
1186 l_temp_str := ProcBody;
1187
1188 -- Translate the Users macro calls into the actual calls
1189 l_temp_str := REPLACE(l_temp_str, 'SEND', g_replace_connect_str);
1190 l_temp_str := REPLACE(l_temp_str, 'GET_PARAM_VALUE', g_replace_get_attr_str);
1191
1192 -- Need to find the DECLARE word location and delete the users
1193 -- declare string (can be case sensitive)
1194
1195 l_declare_loc := INSTR(UPPER(l_temp_str), 'DECLARE', 1, 1);
1196 IF l_declare_loc <> 0 THEN
1197 l_str_before_declare := SUBSTR(l_temp_str, 1, l_declare_loc - 1);
1198 l_str_after_declare := SUBSTR(l_temp_str, l_declare_loc + 7, LENGTH(l_temp_str));
1199
1200 l_temp_str := l_str_before_declare || ' ' || l_str_after_declare;
1201 end IF;
1202
1203
1204 l_final_str := l_final_str || l_temp_str;
1205
1206 CompiledProc := l_final_str;
1207
1208 end TranslateDisconnMacros;
1209
1210
1211 begin
1212 g_ParamLocations.delete;
1213
1214 end XDP_PROCEDURE_BUILDER_UTIL;
[Home] [Help]