[Home] [Help]
PACKAGE BODY: APPS.XDP_PROCEDURE_BUILDER
Source
1 PACKAGE BODY XDP_PROCEDURE_BUILDER AS
2 /* $Header: XDPPRBDB.pls 120.1 2005/06/24 17:29:52 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 g_StartComment varchar2(200) := '/*****************************************************************************';
9 g_EndComment varchar2(200) := '*****************************************************************************/';
10
11
12 g_ProcStart varchar2(20) := 'Procedure ';
13 g_ProcEnd varchar2(20) := '; ';
14 g_ProcBlockBegin varchar2(20) := 'begin' || g_new_line ;
15 g_ProcBlockDeclare varchar2(20) := 'declare ' || g_new_line;
16 g_ProcBlockEnd varchar2(500) :=
17 g_new_line || g_new_line || 'exception ' || g_new_line ||
18 'when others then ' || g_new_line ||
19 ' xdp_macros.HandleProcErrors(p_return_code,p_error_description); ' ||
20 g_new_line || 'end ';
21
22 g_ProcSpec varchar2(2000);
23 g_ProcHeader varchar2(2000);
24 g_ProcFooter varchar2(2000);
25
26 g_ProcBody varchar2(32767);
27
28 g_PkgCreateStmtStartSpec varchar2(80) := 'CREATE OR REPLACE PACKAGE ';
29 g_PkgCreateStmtEndSpec varchar2(80) := ' AUTHID CURRENT_USER AS';
30 g_PkgCreateStmtStartBody varchar2(80) := 'CREATE OR REPLACE PACKAGE BODY ';
31 g_PkgCreateStmtEndBody varchar2(80) := ' AS';
32
33 g_PackageSpec varchar2(32767);
34 g_PackageHeader varchar2(2000);
35 g_PackageFooter varchar2(2000) := 'END ';
36
37 g_PackageBody varchar2(32767);
38
39 g_SvcWIMapTypeDisp varchar2(80) := 'DYNAMIC_WI_MAPPING';
40 g_WIFAMapTypeDisp varchar2(80) := 'DYNAMIC_FA_MAPPING';
41 g_WIWFStartTypeDisp varchar2(80) := 'EXEC_WI_WORKFLOW';
42
43 g_WIParamEvalTypeDisp varchar2(80) := 'WI_PARAM_EVAL_PROC';
44 g_FAParamEvalTypeDisp varchar2(80) := 'FA_PARAM_EVAL_PROC';
45 g_EvalAllFAParamTypeDisp varchar2(80) := 'FA_PARAM_EVAL_ALL_PROC';
46
47 g_LocateFETypeDisp varchar2(80) := 'LOCATE_FE';
48 g_FPTypeDisp varchar2(80) := 'PROVISIONING';
49
50 g_ConnectTypeDisp varchar2(80) := 'CONNECT';
51 g_DisconnectTypeDisp varchar2(80) := 'DISCONNECT';
52
53 g_ProcType varchar2(80) := 'XDP_PROCEDURE_TYPE';
54 g_ParamType varchar2(80) := 'XDP_OBJECTS';
55
56 g_Sync varchar2(30) := 'PIPE';
57 g_ASync varchar2(30) := 'QUEUE';
58 g_None varchar2(30) := 'NONE';
59
60 g_SelectStmt varchar2(20) := 'SELECT';
61 g_InsertStmt varchar2(20) := 'INSERT';
62 g_DeleteStmt varchar2(20) := 'DELETE';
63 g_UpdateStmt varchar2(20) := 'UPDATE';
64
65 g_ApplMode varchar2(40) := ' ';
66 g_AdapterType varchar2(40);
67 g_AdapterTypeDisp varchar2(240);
68
69 cursor c_getProcLookupMeaning(code varchar2) is
70 select meaning
71 from fnd_lookups
72 where lookup_type = 'XDP_PROCEDURE_TYPE'
73 and lookup_code = code;
74
75 cursor c_getParamLookupMeaning(code varchar2) is
76 select meaning
77 from fnd_lookups
78 where lookup_type = 'XDP_OBJECTS'
79 and lookup_code = code;
80
81 cursor c_getAdapterApplMode is
82 select application_mode, display_name
83 from xdp_adapter_types_vl
84 where adapter_type = g_AdapterType;
85
86 Procedure InitGlobals;
87 Procedure InitDisplayNames;
88 Function FetchLookupMeaning(p_LookupType in varchar2,
89 p_LookupCode in varchar2) return varchar2;
90 Function GetProcDispName(p_ProcType in varchar2) return varchar2;
91 Function GetParamDispName(p_OrderParam in boolean default false,
92 p_LineParam in boolean default false,
93 p_WIParam in boolean default false,
94 p_FAParam in boolean default false) return varchar2;
95 Procedure FetchAdapterApplMode( p_AdapterType in varchar2,
96 x_ErrorCode OUT NOCOPY number,
97 x_ErrorString OUT NOCOPY varchar2);
98
99 Procedure Init;
100 Function getProcSpec(p_ProcType in varchar2) return varchar2;
101 Function getComments(p_ProcType in varchar2) return varchar2;
102 Function getAdditionalStuff(p_ProcType in varchar2) return varchar2;
103 Function getInitialization(p_ProcType in varchar2,
104 p_ProcName in varchar2) return varchar2;
105 Function getFinilization(p_ProcType in varchar2,
106 p_ProcName in varchar2) return varchar2;
107
108 Procedure ValidateParameters(p_ProcType in varchar2,
109 p_ProcBody in varchar2,
110 p_ID in number,
111 x_ErrorCode OUT NOCOPY number,
112 x_ErrorString OUT NOCOPY varchar2);
113
114 Procedure CheckForSQL( p_ProcBody in varchar2,
115 x_ErrorString OUT NOCOPY varchar2);
116
117 Procedure ValidateMacros(p_ProcType in varchar2,
118 p_ProcBody in varchar2,
119 x_ErrorCode OUT NOCOPY number,
120 x_ErrorString OUT NOCOPY varchar2);
121
122 Function CheckForMacros(p_ProcBody in varchar2,
123 p_MacroSend in boolean default false,
124 p_MacroSendHttp in boolean default false,
125 p_MacroLogin in boolean default false,
126 p_MacroGetResp in boolean default false,
127 p_MacroGetParam in boolean default false,
128 p_MacroNotifError in boolean default false,
129 p_MacroGetLongResp in boolean default false,
130 p_MacroRespContains in boolean default false,
131 p_MacroAudit in boolean default false) return boolean;
132
133 Procedure ProcessMacros(p_ProcType in varchar2,
134 p_ProcName in varchar2,
135 p_ProcBody in varchar2);
136
137 Function IsMacroUsed(p_Macro in varchar2,
138 p_ProcBody in varchar2) return boolean;
139
140
141 Procedure FindParamUsage(ProcBody in varchar2);
142
143 Procedure CheckIfDuplicate(p_ProcName IN VARCHAR2,
144 p_ProcType IN VARCHAR2,
145 p_Duplicate OUT NOCOPY BOOLEAN,
146 p_DuplicateType OUT NOCOPY VARCHAR2);
147
148 Function GetPackageName (p_ProcName in varchar2) return varchar2;
149
150 ------
151
152 -- Given the Package.Procedure Name get the Procedure name
153 Function DecodeProcName (p_PackageName in varchar2) return varchar2
154 is
155
156 begin
157
158 return (substr(p_PackageName,
159 (instr(p_PackageName,'.',1) + 1), length(p_PackageName)));
160
161 end DecodeProcName;
162
163 --
164 -- Generate the Package Name from the Procedure Name which the user
165 -- has given. Optionally you can check the validity of the Procedure name
166 -- specified. Validity include: Length, Duplicate
167 Procedure GeneratePackageName ( p_ProcType in varchar2,
168 p_ProcName in varchar2,
169 p_Validate in boolean default false,
170 x_PackageName OUT NOCOPY varchar2,
171 x_ErrorCode OUT NOCOPY number,
172 x_ErrorString OUT NOCOPY varchar2)
173 is
174 l_IsDup boolean := false;
175 l_DupType varchar2(80);
176 begin
177 x_ErrorCode := 0;
178
179 x_PackageName := GetPackageName(p_ProcName);
180
181 if not p_Validate then
182 return;
183 end if;
184
185 if length(p_ProcName) > g_MaxProcLength then
186 x_ErrorCode := -191332;
187 FND_MESSAGE.SET_NAME('XDP', 'XDP_PROC_NAME_TOO_LONG');
188 x_ErrorString := FND_MESSAGE.GET;
189 return;
190 end if;
191
192 CheckIfDuplicate(p_ProcName => x_PackageName || '.' || p_ProcName,
193 p_ProcType => p_ProcType,
194 p_Duplicate => l_IsDup,
195 p_DuplicateType => l_DupType);
196
197 if l_IsDup then
198 x_ErrorCode := -191226;
199 FND_MESSAGE.SET_NAME('XDP', 'XDP_PROC_NAME_EXISTS');
200 x_ErrorString := FND_MESSAGE.GET;
201 return;
202 end if;
203
204 end GeneratePackageName;
205
206 --
207 -- Get the Package Name
208 Function GetPackageName (p_ProcName in varchar2) return varchar2
209 is
210
211 begin
212
213 return (g_PackagePrefix || p_ProcName || g_PackageSuffix);
214
215 end GetPackageName;
216
217 --
218 -- Check if there are any other procedures of a different type with
219 -- the same name
220 Procedure CheckIfDuplicate(p_ProcName IN VARCHAR2,
221 p_ProcType IN VARCHAR2,
222 p_Duplicate OUT NOCOPY BOOLEAN,
223 p_DuplicateType OUT NOCOPY VARCHAR2)
224 is
225
226 CURSOR c_CheckDup is
227 select proc_type
228 from xdp_proc_body
229 where proc_name = p_ProcName
230 and proc_type <> p_ProcType;
231 begin
232
233 p_Duplicate := FALSE;
234 p_DuplicateType := NULL;
235
236 for v_CheckDup in c_CheckDup loop
237 p_Duplicate := TRUE;
238 p_DuplicateType := v_CheckDup.proc_type;
239 exit;
240 end loop;
241
242 end CheckIfDuplicate;
243
244 --
245 -- This routine is used to generate the Specification of the PROCEDURE
246 Procedure GenerateProcSpec (p_ProcType in varchar2,
247 p_ProcName in varchar2)
248 is
249
250 begin
251 g_ProcSpec := g_ProcStart || p_ProcName || '( ' || g_new_line ||
252 getProcSpec(p_ProcType) || g_new_line;
253
254 end GenerateProcSpec;
255
256 --
257 -- Return the Default Procedure Body which needs to be generated.
258 -- The usual PL/SQL default body is "null;" !!
259 Function GenerateProcDefBody(p_ProcType in varchar2) return varchar2
260 is
261 begin
262 return ('null;' || g_new_line) ;
263
264 end GenerateProcDefBody;
265
266 --
267 -- Generate any code which needs to be created as part of the Procedure
268 -- Header. These could include any PL/SQL variables, exceptions etc
269 -- This is also used to generate all the necessary variables which needs
270 -- to be generated for backward compatibility of variable names
271 -- Also any Procedure type specific Initialization needs to ebe generated
272 Procedure GenerateProcHeader(p_ProcType in VARCHAR2,
273 p_ProcName in varchar2)
274 is
275 begin
276 g_ProcHeader := 'is ' || g_new_line ||
277 getAdditionalStuff(p_ProcType) || g_new_line ||
278 g_ProcBlockBegin || g_new_line ||
279 getComments(p_ProcType) || g_new_line ||
280 getInitialization(p_ProcType, p_ProcName)||g_ProcBlockDeclare;
281
282 end GenerateProcHeader;
283
284 -- Generate the Proceduer Footer
285 -- These could include exception blocks, cleanup code etc
286 Procedure GenerateProcFooter(p_ProcType in VARCHAR2,
287 p_ProcName in varchar2)
288 is
289 begin
290 g_procFooter := getFinilization(p_ProcType, p_ProcName) ||
291 g_ProcBlockEnd || p_ProcName || ' ;';
292
293 end GenerateProcFooter;
294
295 --
296 -- This routine is used to generate the complete Procedure Body
297 -- This is essentially Proc Spec + Proc Header + Proc Body + Proc Footer
298 -- When generating the Procedure Body this is also the place where all the
299 -- Macros are translated
300 Procedure GenerateProcBody(p_ProcType in VARCHAR2,
301 p_ProcName in varchar2,
302 p_ProcBody in varchar2 default null)
303 is
304 begin
305 GenerateProcSpec(p_ProcType, p_ProcName);
306 GenerateProcHeader(p_ProcType, p_ProcName);
307 GenerateProcFooter(p_ProcType, p_ProcName);
308
309 g_ProcBody := g_ProcSpec || g_ProcHeader ;
310
311 if p_ProcBody is null then
312 g_ProcBody := g_ProcBody || GenerateProcDefBody(p_ProcType);
313 else
314 ProcessMacros(p_ProcType, p_ProcName, p_ProcBody);
315 end if;
316
317 g_ProcBody := g_ProcBody || g_procFooter;
318
319 end GenerateProcBody;
320
321 --
322 -- Translate all the Macros used by the user in the procedure.
323 -- Translation of macros can depend on the type of procedures
324 Procedure ProcessMacros(p_ProcType in varchar2,
325 p_ProcName in varchar2,
326 p_ProcBody in varchar2)
327 is
328 l_TempProcBody varchar2(32767);
329 begin
330
331 if p_ProcType = g_FPType then
332 xdp_procedure_builder_util.TranslateFPMacros(ProcName => p_ProcName,
333 ProcStr => p_ProcBody,
334 CompiledProc => l_TempProcBody);
335 elsif p_ProcType = g_ConnectType then
336 xdp_procedure_builder_util.TranslateConnMacros(ProcName => p_ProcName,
337 ProcBody => p_ProcBody,
338 CompiledProc => l_TempProcBody);
339 elsif p_ProcType = g_DisconnectType then
340 xdp_procedure_builder_util.TranslateDisconnMacros(ProcName => p_ProcName,
341 ProcBody => p_ProcBody,
342 CompiledProc => l_TempProcBody);
343 else
344 xdp_procedure_builder_util.TranslateDefMacros(ProcName => p_ProcName,
345 ProcStr => p_ProcBody,
346 CompiledProc => l_TempProcBody);
347 end if;
348
349 g_ProcBody := g_ProcBody || g_new_line || l_TempProcBody;
350
351 end ProcessMacros;
352
353 --
354 -- This routine is used to generate the Header information for the
355 -- Package Spec or Package Body
356 -- This typically is the "CREATE" statement for the package
357 Procedure GeneratePackageHeader(p_PackageName in VARCHAR2,
358 p_SpecOrBody in VARCHAR2 default 'SPEC')
359 is
360 begin
361 if p_SpecOrBody = 'SPEC' then
362 g_PackageHeader :=
363 g_PkgCreateStmtStartSpec || ' ' || p_PackageName || ' ' ||
364 g_PkgCreateStmtEndSpec || g_new_line;
365 elsif p_SpecOrBody = 'BODY' then
366 g_PackageHeader :=
367 g_PkgCreateStmtStartBody || ' ' || p_PackageName || ' ' ||
368 g_PkgCreateStmtEndBody || g_new_line;
369 end if;
370
371 end GeneratePackageHeader;
372
373 --
374 -- This routine is used to generate the footer information for the Package
375 Procedure GeneratePackageFooter(p_PackageName in VARCHAR2)
376 is
377 begin
378 g_PackageFooter := g_PackageFooter || ' ' || p_PackageName || ';';
379
380 end GeneratePackageFooter;
381
382 --
383 -- Create the Package Specification
384 -- This includes the Package Create Statement and the Procedure name and
385 -- Specification to be created within the package
386 -- This version of the routine also generates the Package name from the
387 -- Procedure Name passed
388 -- The actual Package Specification is generated and any errors is given
389 -- to the user
390 Procedure GeneratePackageSpec(p_ProcType in varchar2,
391 p_ProcName in varchar2,
392 x_ErrorCode OUT NOCOPY number,
393 x_ErrorString OUT NOCOPY varchar2)
394 is
395
396 l_PackageName varchar2(80);
397 begin
398
399 l_PackageName := GetPackageName(p_ProcName);
400
401 GeneratePackageSpec(p_PackageName => l_PackageName,
402 p_ProcType => p_ProcType,
403 p_ProcName=> p_ProcName,
404 x_ErrorCode => x_ErrorCode,
405 x_ErrorString => x_ErrorString);
406 end GeneratePackageSpec;
407
408 --
409 -- Create the Package Specification (Over Loaded)
410 -- This includes the Package Create Statement and the Procedure name and
411 -- Specification to be created within the package
412 -- This version of the routine takes the package Name to be created as a
413 -- parameter
414 -- The actual Package Specification is generated and any errors is given
415 -- to the user
416 Procedure GeneratePackageSpec(p_PackageName in VARCHAR2,
417 p_ProcType in varchar2,
418 p_ProcName in varchar2,
419 x_ErrorCode OUT NOCOPY number,
420 x_ErrorString OUT NOCOPY varchar2)
421 is
422 begin
423 Init;
424
425 GeneratePackageHeader(p_PackageName);
426
427 GenerateProcSpec(p_ProcType, p_ProcName);
428
429 g_PackageSpec := g_PackageSpec || g_new_line || g_new_line ||
430 g_ProcSpec || g_ProcEnd;
431
432 GeneratePackageFooter(p_PackageName);
433
434 g_PackageSpec := g_PackageHeader || g_PackageSpec || g_new_line ||
435 g_PackageFooter;
436
437 xdp_utilities.Create_Pkg_Spec (
438 P_PKG_NAME => p_PackageName,
439 P_PKG_SPEC => g_PackageSpec,
440 P_APPLICATION_SHORT_NAME => 'XDP',
441 X_RETURN_CODE => x_ErrorCode,
442 X_ERROR_STRING => x_ErrorString);
443
444 if x_ErrorCode <> 0 then
445 x_ErrorCode := -197018;
446 FND_MESSAGE.SET_NAME('XDP', 'XDP_PROC_COMPILE_ERROR');
447 FND_MESSAGE.SET_TOKEN('PROC_TYPE', GetProcDispName(p_ProcType));
448 FND_MESSAGE.SET_TOKEN('PROC_NAME', p_ProcName);
449 FND_MESSAGE.SET_TOKEN('ERR_CODE', x_ErrorCode);
450 FND_MESSAGE.SET_TOKEN('ERR_STR', x_ErrorString);
451
452 x_ErrorString := FND_MESSAGE.GET;
453 end if;
454 end GeneratePackageSpec;
455
456 --
457 -- Create the Package Body (Over Loaded)
458 -- This includes the Package Create Statement and the Procedure name and
459 -- Body to be created within the package
460 -- This version of the routine generates the package Name to be created using
461 -- the Procedure Name passed
462 -- The actual Package Body is generated and any errors is given
463 -- to the user
464 Procedure GeneratePackageBody(p_ProcType in varchar2,
465 p_ProcName in varchar2,
466 p_ProcBody in varchar2 default null,
467 x_ErrorCode OUT NOCOPY number,
468 x_ErrorString OUT NOCOPY varchar2)
469 is
470
471 l_PackageName varchar2(80);
472 begin
473
474 l_PackageName := GetPackageName(p_ProcName);
475 GeneratePackageBody(p_PackageName => l_PackageName,
476 p_ProcType => p_ProcType,
477 p_ProcName=> p_ProcName,
478 p_ProcBody => p_ProcBody,
479 x_ErrorCode => x_ErrorCode,
480 x_ErrorString => x_ErrorString);
481
482 end GeneratePackageBody;
483
484 --
485 -- Create the Package Body (Over Loaded)
486 -- This includes the Package Create Statement and the Procedure name and
487 -- Body to be created within the package
488 -- This version of the routine takes the package Name to be created as a
489 -- parameter
490 -- The actual Package Body is generated and any errors is given
491 -- to the user
492 Procedure GeneratePackageBody(p_PackageName in VARCHAR2,
493 p_ProcType in varchar2,
494 p_ProcName in varchar2,
495 p_ProcBody in varchar2 default null,
496 x_ErrorCode OUT NOCOPY number,
497 x_ErrorString OUT NOCOPY varchar2)
498 is
499
500 begin
501 Init;
502
503 GeneratePackageHeader(p_PackageName, 'BODY');
504
505 GenerateProcBody(p_ProcType, p_ProcName, p_ProcBody);
506
507 g_PackageBody := g_PackageBody || g_new_line || g_new_line ||
508 g_ProcBody;
509
510 GeneratePackageFooter(p_PackageName);
511
512 g_PackageBody := g_PackageHeader || g_PackageBody || g_new_line ||
513 g_PackageFooter;
514
515
516 xdp_utilities.Create_Pkg_Body (
517 P_PKG_NAME => p_PackageName,
518 P_PKG_BODY => g_PackageBody,
519 P_APPLICATION_SHORT_NAME => 'XDP',
520 X_RETURN_CODE => x_ErrorCode,
521 X_ERROR_STRING => x_ErrorString);
522
523 if x_ErrorCode <> 0 then
524 x_ErrorCode := -197018;
525 FND_MESSAGE.SET_NAME('XDP', 'XDP_PROC_COMPILE_ERROR');
526 FND_MESSAGE.SET_TOKEN('PROC_TYPE', GetProcDispName(p_ProcType));
527 FND_MESSAGE.SET_TOKEN('PROC_NAME', p_ProcName);
528 FND_MESSAGE.SET_TOKEN('ERR_CODE', x_ErrorCode);
529 FND_MESSAGE.SET_TOKEN('ERR_STR', x_ErrorString);
530
531 x_ErrorString := FND_MESSAGE.GET;
532 end if;
533 end GeneratePackageBody;
534
535 --
536 -- This routine is used to pre-compile the Users Procedures
537 -- Mandaroty pre-compilation checks are: Macro Checks and PL/SQL logic checks
538 -- Optional check is Parameter Validation
539 -- Any errors are given to the user
540 Procedure PrecompileProcedure(p_ProcType in varchar2,
541 p_ProcBody in varchar2,
542 p_ID in number default null,
543 p_AdapterType in varchar2 default null,
544 p_ValidateParams in boolean default true,
545 x_ErrorCode OUT NOCOPY number,
546 x_ErrorString OUT NOCOPY varchar2)
547 is
548 begin
549 if p_ProcType = g_FPType then
550 FetchAdapterApplMode(p_AdapterType => p_AdapterType,
551 x_ErrorCode => x_ErrorCode,
552 x_ErrorString => x_ErrorString);
553 if x_ErrorCode <> 0 then
554 return;
555 end if;
556 end if;
557
558 ValidateMacros(p_ProcType, p_ProcBody, x_ErrorCode, x_ErrorString);
559 if x_ErrorCode <> 0 then
560 return;
561 end if;
562 if p_ValidateParams then
563 ValidateParameters(p_ProcType, p_ProcBody, p_ID, x_ErrorCode, x_ErrorString);
564 if x_ErrorCode <> 0 then
565 return;
566 end if;
567 end if;
568
569 x_ErrorCode := 0;
570 CheckForSQL(p_ProcBody, x_ErrorString);
571
572 end PrecompileProcedure;
573
574 --
575 -- This routine check the Validity of Paramter Usage within a Procedure
576 -- The types of paramterse which can be used depends on the Procedure Type
577 -- For example: ORDER related paramters CANNOT be used in a Connect/Disconnect Procedure
578 -- FA Paramters CANNOT be used in a Svc-WI Mapping Procedure etc
579 -- Once the usages of the paramters is validated the actual parameter names are
580 -- Checked against the configuration
581 Procedure ValidateParameters(p_ProcType in varchar2,
582 p_ProcBody in varchar2,
583 p_ID in number,
584 x_ErrorCode OUT NOCOPY number,
585 x_ErrorString OUT NOCOPY varchar2)
586 is
587 begin
588 -- Find the Paramter Usages
589 FindParamUsage(p_ProcBody);
590 if p_ProcType in (g_ConnectType, g_DisconnectType) then
591 -- Order related Parameters cannot be used in a
592 -- Connect/Disconnect Procedure
593 if pv_WIParamUsed or pv_FAParamUsed or
594 pv_OrderParamUsed or pv_LineParamUsed then
595 x_ErrorCode := -197019;
596 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_PARAM_USAGE');
597 FND_MESSAGE.SET_TOKEN('INVALID_PARAM_LIST',
598 GetParamDispName(p_OrderParam => true,
599 p_LineParam => true,
600 p_WIParam => true,
601 p_FAParam => true));
602 FND_MESSAGE.SET_TOKEN('PROCEDURE_TYPE',
603 GetProcDispName(p_ProcType));
604
605 x_ErrorString := FND_MESSAGE.GET;
606 return;
607 else
608 -- Check the actual FE Attribute name's validity for the
609 -- FE Type
610 xdp_procedure_builder_util.ValidateFEAttributes(
611 p_ID, p_ProcBody,x_ErrorCode,x_ErrorString);
612 x_ErrorString := x_ErrorString || 'FE: ' || p_ID;
613 return;
614 end if;
615
616 elsif p_ProcType = g_SvcWIMapType then
617 -- WI or FA Paramters cannot be used in a Svc-WI Mapping procedure
618 if pv_WIParamUsed or pv_FAParamUsed then
619 x_ErrorCode := -197019;
620 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_PARAM_USAGE');
621 FND_MESSAGE.SET_TOKEN('INVALID_PARAM_LIST',
622 GetParamDispName(p_WIParam => true,
623 p_FAParam => true));
624 FND_MESSAGE.SET_TOKEN('PROCEDURE_TYPE',
625 GetProcDispName(p_ProcType));
626
627 x_ErrorString := FND_MESSAGE.GET;
628 return;
629 end if;
630 elsif p_ProcType = g_WIFAMapType then
631 -- FA Paramters cannot be used in a WI-FA Mapping procedure
632 if pv_FAParamUsed then
633 x_ErrorCode := -197019;
634 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_PARAM_USAGE');
635 FND_MESSAGE.SET_TOKEN('INVALID_PARAM_LIST',
636 GetParamDispName(p_FAParam => true));
637 FND_MESSAGE.SET_TOKEN('PROCEDURE_TYPE',
638 GetProcDispName(p_ProcType));
639
640 x_ErrorString := FND_MESSAGE.GET;
641 return;
642 end if;
643 elsif p_ProcType = g_WIWFStartType then
644 -- FA Paramters cannot be used in a WI Workflow Start Procedure
645 if pv_FAParamUsed then
646 x_ErrorCode := -197019;
647 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_PARAM_USAGE');
648 FND_MESSAGE.SET_TOKEN('INVALID_PARAM_LIST',
649 GetParamDispName(p_FAParam => true));
650 FND_MESSAGE.SET_TOKEN('PROCEDURE_TYPE',
651 GetProcDispName(p_ProcType));
652
653 x_ErrorString := FND_MESSAGE.GET;
654 return;
655 end if;
656 elsif p_ProcType = g_WIParamEvalType then
657 -- FA Paramters cannot be used in a WI Parameter Evaluation Procedure
658 if pv_FAParamUsed then
659 x_ErrorCode := -197019;
660 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_PARAM_USAGE');
661 FND_MESSAGE.SET_TOKEN('INVALID_PARAM_LIST',
662 GetParamDispName(p_FAParam => true));
663 FND_MESSAGE.SET_TOKEN('PROCEDURE_TYPE',
664 GetProcDispName(p_ProcType));
665
666 x_ErrorString := FND_MESSAGE.GET;
667 return;
668 end if;
669 elsif p_ProcType = g_FAParamEvalType then
670 -- No specific Macro usage limitations
671 -- Can use $ORDER, $LINE, $WI, $FA
672 null;
673 elsif p_ProcType = g_LocateFEType then
674 -- No specific Macro usage limitations
675 -- Can use $ORDER, $LINE, $WI, $FA
676 null;
677 end if;
678
679 -- OK so far
680 -- Now check the validity of parameter usage for each procedure type
681 if p_ProcType not in (g_ConnectType, g_DisconnectType, g_SvcWIMapType) then
682
683 xdp_procedure_builder_util.ValidateFPParameters(
684 p_ID => p_ID,
685 p_ProcBody => p_ProcBody,
686 x_ErrorCode => x_ErrorCode,
687 x_ErrorString => x_ErrorString);
688 end if;
689
690 end ValidateParameters;
691
692
693 --
694 -- Validate the Macro Usage.
695 -- The types of macros which can be used depends on the type of the Procedure
696 -- for e.g. SEND cannot be used in a Work Item Paramter Evaluation Procedure
697 Procedure ValidateMacros(p_ProcType in varchar2,
698 p_ProcBody in varchar2,
699 x_ErrorCode OUT NOCOPY number,
700 x_ErrorString OUT NOCOPY varchar2)
701 is
702 begin
703 if p_ProcType not in (g_FPType, g_ConnectType, g_DisconnectType) then
704 -- GET_PARAM_VALUE is pretty much the only macro which can
705 -- be used in any procedure
706 if not CheckForMacros(p_MacroSend => true,
707 p_MacroSendHttp => true,
708 p_MacroGetResp => true,
709 p_MacroLogin => true,
710 p_MacroNotifError => true,
711 p_MacroGetLongResp => true,
712 p_MacroRespContains => true,
713 p_MacroAudit => true,
714 p_ProcBody => ValidateMacros.p_ProcBody) then
715 x_ErrorCode := 0;
716 return;
717 else
718 x_ErrorCode := -197017;
719 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_MACRO_USAGE');
720 FND_MESSAGE.SET_TOKEN('INVALID_MACRO_LIST',
721 'SEND, SEND_HTTP, GET_RESPONSE, GET_LONG_RESPONSE, ' ||
722 'LOGIN, NOTIFY_ERROR, AUDIT');
723 FND_MESSAGE.SET_TOKEN('PROCEDURE_TYPE',
724 GetProcDispName(p_ProcType));
725 FND_MESSAGE.SET_TOKEN('VALID_MACRO_LIST',
726 'GET_PARAM_VALUE');
727
728 x_ErrorString := FND_MESSAGE.GET;
729 return;
730 end if;
731 elsif p_ProcType = g_DisconnectType then
732 -- FP related mactos - SEND_HTTP, GET_RESPONSE, NOTIFY_ERROR, AUDIT
733 -- and the Connect Procedure's LOGIN cannot be used in a
734 -- Disconnect Procedure
735 if CheckForMacros (p_MacroSendHttp => true,
736 p_MacroGetResp => true,
737 p_MacroLogin => true,
738 p_MacroNotifError => true,
739 p_MacroGetLongResp => true,
740 p_MacroRespContains => true,
741 p_MacroAudit => true,
742 p_ProcBody => ValidateMacros.p_ProcBody) then
743 x_ErrorCode := -197017;
744 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_MACRO_USAGE');
745 FND_MESSAGE.SET_TOKEN('INVALID_MACRO_LIST',
746 'SEND_HTTP, GET_RESPONSE, GET_LONG_RESPONSE, ' ||
747 'LOGIN, NOTIFY_ERROR, AUDIT');
748 FND_MESSAGE.SET_TOKEN('PROCEDURE_TYPE',
749 GetProcDispName(p_ProcType));
750 FND_MESSAGE.SET_TOKEN('VALID_MACRO_LIST',
751 'SEND, GET_PARAM_VALUE');
752
753 x_ErrorString := FND_MESSAGE.GET;
754 return;
755 else
756 x_ErrorCode := 0;
757 return;
758 end if;
759 elsif p_ProcType = g_ConnectType then
760 -- FP related mactos - SEND_HTTP, GET_RESPONSE, NOTIFY_ERROR, AUDIT
761 -- cannot be used in a Connect Procedure
762 if CheckForMacros (p_MacroSendHttp => true,
763 p_MacroGetResp => true,
764 p_MacroNotifError => true,
765 p_MacroGetLongResp => true,
766 p_MacroRespContains => true,
767 p_MacroAudit => true,
768 p_ProcBody => ValidateMacros.p_ProcBody) then
769
770 x_ErrorCode := -197017;
771 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_MACRO_USAGE');
772 FND_MESSAGE.SET_TOKEN('INVALID_MACRO_LIST',
773 'SEND_HTTP, GET_RESPONSE, GET_LONG_RESPONSE, ' ||
774 'NOTIFY_ERROR, AUDIT');
775 FND_MESSAGE.SET_TOKEN('PROCEDURE_TYPE',
776 GetProcDispName(p_ProcType));
777 FND_MESSAGE.SET_TOKEN('VALID_MACRO_LIST',
778 'SEND, LOGIN, GET_PARAM_VALUE');
779
780 x_ErrorString := FND_MESSAGE.GET;
781 return;
782 else
783 x_ErrorCode := 0;
784 return;
785 end if;
786 elsif p_ProcType = g_FPType then
787 -- the Connect Procedure's LOGIN cannot be used in a
788 -- Fulfillment Procedure
789 if CheckForMacros (p_MacroLogin => true,
790 p_ProcBody => ValidateMacros.p_ProcBody) then
791
792 x_ErrorCode := -197017;
793 FND_MESSAGE.SET_NAME('XDP', 'XDP_INVALID_MACRO_USAGE');
794 FND_MESSAGE.SET_TOKEN('INVALID_MACRO_LIST', 'LOGIN');
795 FND_MESSAGE.SET_TOKEN('PROCEDURE_TYPE',
796 GetProcDispName(p_ProcType));
797 FND_MESSAGE.SET_TOKEN('VALID_MACRO_LIST',
798 'SEND, SEND_HTTP, GET_PARAM_VALUE, GET_RESPONSE ' ||
799 'GET_LONG_RESPONSE, NOTIFY_ERROR, AUDIT');
800
801 x_ErrorString := FND_MESSAGE.GET;
802 return;
803
804 end if;
805
806 -- Check Adapter Type Specific Macros
807 -- Only certain macros can be used and this depends upon
808 -- the Adapter Type
809 -- for e.g SEND_HTTP cannot be used with an INTERACTIVE Adapter
810 if g_ApplMode = g_Sync then
811 -- Check for Interactive Adapter
812 -- SEND_HTTP cannot be used
813 if CheckForMacros(
814 p_MacroSendHttp => true,
815 p_ProcBody => ValidateMacros.p_ProcBody) then
816 x_ErrorCode := -197017;
817 FND_MESSAGE.SET_NAME('XDP',
818 'XDP_INVALID_MACRO_FOR_ADAPTER');
819 FND_MESSAGE.SET_TOKEN('ADAPTER_TYPE',
820 nvl(g_AdapterTypeDisp, g_AdapterType));
821 FND_MESSAGE.SET_TOKEN('INVALID_MACRO_LIST',
822 'SEND_HTTP');
823 x_ErrorString := FND_MESSAGE.GET;
824 return;
825 else
826 x_ErrorCode := 0;
827 return;
828 end if;
829 elsif g_ApplMode = g_ASync then
830 -- Check for File Adapter and other "ASYNC" adapter
831 -- SEND, SEND_HTTP, GET_RESPONSE and GET_LONG_RESPONSE
832 -- cannot be used
833 if CheckForMacros(
834 p_MacroSend => true,
835 p_MacroSendHttp => true,
836 p_MacroGetResp => true,
837 p_MacroGetLongResp => true,
838 p_ProcBody => ValidateMacros.p_ProcBody) then
839 x_ErrorCode := -197017;
840 FND_MESSAGE.SET_NAME('XDP',
841 'XDP_INVALID_MACRO_FOR_ADAPTER');
842 FND_MESSAGE.SET_TOKEN('ADAPTER_TYPE',
843 nvl(g_AdapterTypeDisp, g_AdapterType));
844 FND_MESSAGE.SET_TOKEN('INVALID_MACRO_LIST',
845 'SEND_HTTP, SEND, GET_RESPONSE, GET_LONG_RESPONSE');
846
847 x_ErrorString := FND_MESSAGE.GET;
848 return;
849 else
850 x_ErrorCode := 0;
851 return;
852 end if;
853 elsif g_ApplMode = g_None then
854 -- HTTP adapter. SEND cannot be used.
855 if g_AdapterType = 'HTTP' then
856 if CheckForMacros(
857 p_MacroSend => true,
858 p_ProcBody => ValidateMacros.p_ProcBody) then
859
860 x_ErrorCode := -197017;
861 FND_MESSAGE.SET_NAME('XDP',
862 'XDP_INVALID_MACRO_FOR_ADAPTER');
863 FND_MESSAGE.SET_TOKEN('ADAPTER_TYPE',
864 nvl(g_AdapterTypeDisp, g_AdapterType));
865 FND_MESSAGE.SET_TOKEN('INVALID_MACRO_LIST',
866 'SEND');
867
868 x_ErrorString := FND_MESSAGE.GET;
869 return;
870 else
871 x_ErrorCode := 0;
872 return;
873 end if;
874 else
875 -- Check for other "Non-Implemented" adapter
876 -- SEND, SEND_HTTP, GET_RESPONSE and GET_LONG_RESPONSE
877 -- cannot be used
878 if CheckForMacros(
879 p_MacroSend => true,
880 p_MacroSendHttp => true,
881 p_MacroGetResp => true,
882 p_MacroGetLongResp => true,
883 p_ProcBody => ValidateMacros.p_ProcBody) then
884
885 x_ErrorCode := -197017;
886 FND_MESSAGE.SET_NAME('XDP',
887 'XDP_INVALID_MACRO_FOR_ADAPTER');
888 FND_MESSAGE.SET_TOKEN('ADAPTER_TYPE',
889 nvl(g_AdapterTypeDisp, g_AdapterType));
890 FND_MESSAGE.SET_TOKEN('INVALID_MACRO_LIST',
891 'SEND_HTTP, SEND, GET_RESPONSE,' ||
892 ' GET_LONG_RESPONSE');
893
894 x_ErrorString := FND_MESSAGE.GET;
895 return;
896 else
897 x_ErrorCode := 0;
898 return;
899 end if;
900 end if;
901 end if;
902
903 else
904 x_ErrorCode := 0;
905 return;
906 end if;
907 end ValidateMacros;
908
909 --
910 -- Warn the user if any native SQL logic is found.
911 -- This is not an error but just a warning
912 Procedure CheckForSQL( p_ProcBody in varchar2,
913 x_ErrorString OUT NOCOPY varchar2)
914 is
915 l_SQLStmt varchar2(80) := null;
916 begin
917
918 x_ErrorString := null;
919 if instr(upper(p_ProcBody), 'SELECT ', 1, 1) > 0 then
920 l_SQLStmt := g_SelectStmt;
921 end if;
922 if instr(upper(p_ProcBody), 'INSERT ', 1, 1) > 0 then
923 if l_SQLStmt is null then
924 l_SQLStmt := g_InsertStmt;
925 else
926 l_SQLStmt := l_SQLStmt || ',' || g_InsertStmt;
927 end if;
928 end if;
929 if instr(upper(p_ProcBody), 'UPDATE ', 1, 1) > 0 then
930 if l_SQLStmt is null then
931 l_SQLStmt := g_UpdateStmt;
932 else
933 l_SQLStmt := l_SQLStmt || ',' || g_UpdateStmt;
934 end if;
935 end if;
936 if instr(upper(p_ProcBody), 'DELETE ', 1, 1) > 0 then
937 if l_SQLStmt is null then
938 l_SQLStmt := g_DeleteStmt;
939 else
940 l_SQLStmt := l_SQLStmt || ',' || g_DeleteStmt;
941 end if;
942 end if;
943
944 if l_SQLStmt is not null then
945 FND_MESSAGE.SET_NAME('XDP','XDP_SQL_IN_PROC');
946 FND_MESSAGE.SET_TOKEN('SQLSTMT',l_SQLStmt);
947 x_ErrorString := FND_MESSAGE.GET;
948 end if;
949
950 end CheckForSQL;
951
952 --
953 -- Check for Macro usages within the procedure
954 Function CheckForMacros(p_ProcBody in varchar2,
955 p_MacroSend in boolean default false,
956 p_MacroSendHttp in boolean default false,
957 p_MacroLogin in boolean default false,
958 p_MacroGetResp in boolean default false,
959 p_MacroGetParam in boolean default false,
960 p_MacroNotifError in boolean default false,
961 p_MacroGetLongResp in boolean default false,
962 p_MacroRespContains in boolean default false,
963 p_MacroAudit in boolean default false) return boolean
964 is
965 l_UnionFlag boolean := FALSE;
966 l_TempFlag boolean := FALSE;
967
968 l_Found number := 0;
969
970 l_tempBody varchar2(32767) := p_ProcBody;
971 l_Clash_str1 varchar2(80) := g_MacroSendHttp;
972 l_Clash_str_replace1 varchar2(80) := 'XXXX_HTTP';
973 l_Clash_str2 varchar2(80) := '.SEND';
974 l_Clash_str_replace2 varchar2(80) := '.XXXX';
975 begin
976
977 if p_MacroSend then
978 l_tempBody := replace(l_tempBody, l_Clash_str1, l_Clash_str_replace1);
979 l_tempBody := replace(l_tempBody, l_Clash_str2, l_Clash_str_replace2);
980 l_UnionFlag := isMacroUsed(g_MacroSend, l_tempBody);
981 l_Found := l_Found + 1;
982 l_tempBody := replace(p_ProcBody, l_Clash_str_replace1, l_Clash_str1);
983 l_tempBody := replace(p_ProcBody, l_Clash_str_replace2, l_Clash_str2);
984 end if;
985
986 if p_MacroSendHttp then
987 l_TempFlag := isMacroUsed(g_MacroSendHttp, l_tempBody);
988 l_Found := l_Found + 1;
989 l_UnionFlag := l_UnionFlag OR l_TempFlag;
990 end if;
991
992
993 if p_MacroLogin then
994 l_TempFlag := isMacroUsed(g_MacroLogin, l_tempBody);
995 l_Found := l_Found + 1;
996 l_UnionFlag := l_UnionFlag OR l_TempFlag;
997 end if;
998
999
1000 if p_MacroGetResp then
1001 l_TempFlag := isMacroUsed(g_MacroGetResp, l_tempBody);
1002 l_Found := l_Found + 1;
1003 l_UnionFlag := l_UnionFlag OR l_TempFlag;
1004 end if;
1005
1006
1007 if p_MacroGetParam then
1008 l_TempFlag := isMacroUsed(g_MacroGetParam, l_tempBody);
1009 l_UnionFlag := l_UnionFlag OR l_TempFlag;
1010 l_Found := l_Found + 1;
1011 end if;
1012
1013 if p_MacroNotifError then
1014 l_TempFlag := isMacroUsed(g_MacroNotifError, l_tempBody);
1015 l_Found := l_Found + 1;
1016 l_UnionFlag := l_UnionFlag OR l_TempFlag;
1017 end if;
1018
1019 if p_MacroRespContains then
1020 l_TempFlag := isMacroUsed(g_MacroResponseContains, l_tempBody);
1021 l_Found := l_Found + 1;
1022 l_UnionFlag := l_UnionFlag OR l_TempFlag;
1023 end if;
1024
1025 if p_MacroGetLongResp then
1026 l_TempFlag := isMacroUsed(g_MacroGetLongResp, l_tempBody);
1027 l_Found := l_Found + 1;
1028 l_UnionFlag := l_UnionFlag OR l_TempFlag;
1029 end if;
1030
1031 if p_MacroAudit then
1032 l_TempFlag := isMacroUsed(g_MacroAudit, l_tempBody);
1033 l_Found := l_Found + 1;
1034 l_UnionFlag := l_UnionFlag OR l_TempFlag;
1035 end if;
1036
1037 if l_Found = 0 then
1038 -- No Macros to be validated. Return TRUE
1039 l_UnionFlag := TRUE;
1040 end if;
1041
1042 return (l_UnionFlag);
1043 end CheckForMacros;
1044
1045
1046 --
1047 -- Scan the Procedure for a particular Macro
1048 Function IsMacroUsed(p_Macro in varchar2,
1049 p_ProcBody in varchar2) return boolean
1050 is
1051 l_UnionFlag boolean;
1052 l_Found number := 0;
1053
1054 begin
1055 l_Found := INSTR(p_ProcBody, p_Macro, 1 , 1);
1056 if l_Found > 0 then
1057 l_UnionFlag := true;
1058 else
1059 l_UnionFlag := false;
1060 end if;
1061
1062 return (l_UnionFlag);
1063 end IsMacroUsed;
1064
1065 --
1066 -- Find the Paramter usages within the Procedure
1067 Procedure FindParamUsage(ProcBody in varchar2)
1068 is
1069
1070 begin
1071 pv_WIParamUsed := false;
1072 pv_OrderParamUsed := false;
1073 pv_LineParamUsed := false;
1074 pv_FAParamUsed := false;
1075
1076 if instr(upper(ProcBody), '$WI.') > 1 then
1077 pv_WIParamUsed := true;
1078 elsif instr(upper(ProcBody), '$LINE.') > 1 then
1079 pv_LineParamUsed := true;
1080 elsif instr(upper(ProcBody), '$ORDER.') > 1 then
1081 pv_OrderParamUsed := true;
1082 elsif instr(upper(ProcBody), '$FA.') > 1 then
1083 pv_FaParamUsed := true;
1084 end if;
1085
1086 end FindParamUsage;
1087
1088 -- Meta-Data for Procedure Specifications
1089 -- Package Initializations
1090 Function getProcSpec(p_ProcType in varchar2) return varchar2
1091 is
1092 begin
1093 if p_ProcType = g_SvcWIMapType then
1094 return (g_SvcWIMapSpec);
1095 elsif p_ProcType = g_WIFAMapType then
1096 return (g_WIFAMapSpec);
1097 elsif p_ProcType = g_WIWFStartType then
1098 return (g_WIWFStartSpec);
1099 elsif p_ProcType = g_WIParamEvalType then
1100 return (g_WIParamEvalSpec);
1101 elsif p_ProcType = g_FAParamEvalType then
1102 return (g_FAParamEvalSpec);
1103 elsif p_ProcType = g_EvalAllFAParamType then
1104 return (g_EvalAllFAParamSpec);
1105 elsif p_ProcType = g_LocateFEType then
1106 return (g_LocateFESpec);
1107 elsif p_ProcType = g_FPType then
1108 return (g_FPSpec);
1109 elsif p_ProcType = g_ConnectType then
1110 return (g_ConnectSpec);
1111 elsif p_ProcType = g_DisconnectType then
1112 return (g_DisconnectSpec);
1113 end if;
1114
1115 end getProcSpec;
1116
1117 -- Meta-Data for any comments to be generated
1118 Function getComments(p_ProcType in varchar2) return varchar2
1119 is
1120 begin
1121 if p_ProcType = g_SvcWIMapType then
1122 return (g_SvcWIMapComments);
1123 elsif p_ProcType = g_WIFAMapType then
1124 return (g_WIFAMapComments);
1125 elsif p_ProcType = g_WIWFStartType then
1126 return (g_WIWFStartComments);
1127 elsif p_ProcType = g_WIParamEvalType then
1128 return (g_WIParamEvalComments);
1129 elsif p_ProcType = g_FAParamEvalType then
1130 return (g_FAParamEvalComments);
1131 elsif p_ProcType = g_EvalAllFAParamType then
1132 return (g_EvalAllFAParamComments);
1133 elsif p_ProcType = g_LocateFEType then
1134 return (g_LocateFEComments);
1135 elsif p_ProcType = g_FPType then
1136 return (g_FPComments);
1137 elsif p_ProcType = g_ConnectType then
1138 return (g_ConnectComments);
1139 elsif p_ProcType = g_DisconnectType then
1140 return (g_DisconnectComments);
1141 end if;
1142
1143 end getComments;
1144
1145
1146 -- Meta-Data for any backward compatibility code to be generated
1147 -- For example: The Specification for the Fulfillment procedure is now
1148 -- changed to have the paramters named as "p_xxx" instead of just "xxx"
1149 -- p_channel_name instead of channel_name
1150 -- To allow backward compatibility to earlier procedures new PL/SQL variables
1151 -- channel_name is created so that the old procedures compile successfully
1152 Function getAdditionalStuff(p_ProcType in varchar2) return varchar2
1153 is
1154 l_AdditionalStuff varchar2(2000);
1155 begin
1156 if p_ProcType = g_FPType then
1157 l_AdditionalStuff :=
1158 'order_id NUMBER := p_order_id;' || g_new_line ||
1159 'line_item_id NUMBER := p_line_item_id;' || g_new_line ||
1160 'workitem_instance_id NUMBER := p_wi_instance_id;' || g_new_line ||
1161 'fa_instance_id NUMBER := p_fa_instance_id;' || g_new_line ||
1162 'db_channel_name VARCHAR2(40) := p_channel_name;' || g_new_line ||
1163 'fe_name VARCHAR2(80) := p_fe_name;' || g_new_line ||
1164 'fa_item_type VARCHAR2(8) := p_fa_item_type;' || g_new_line ||
1165 'fa_item_key VARCHAR2(240) := p_fa_item_key;' || g_new_line ||
1166 'LOG VARCHAR2(1) := ''N'';' || g_new_line ||
1167 'NOLOG VARCHAR2(1) := ''Y'';' || g_new_line ||
1168 'RETRY number := 1;' || g_new_line ||
1169 'NORETRY number := 0;' || g_new_line ||
1170 'fa_item_key VARCHAR2(240) := p_fa_item_key;' || g_new_line ||
1171 'sdp_internal_response VARCHAR2(32767);' || g_new_line ||
1172 'sdp_internal_err_code VARCHAR2(40) := p_return_code;' || g_new_line ||
1173 'sdp_internal_err_str VARCHAR2(40) := p_error_description;' || g_new_line;
1174
1175 return (l_AdditionalStuff);
1176 elsif p_ProcType in (g_ConnectType, g_DisconnectType) then
1177 l_AdditionalStuff :=
1178 'fe_name VARCHAR2(80) := p_fe_name;' || g_new_line ||
1179 'channel_name VARCHAR2(40) := p_channel_name;' || g_new_line ||
1180 'sdp_internal_response VARCHAR2(32767);' || g_new_line ||
1181 'sdp_internal_err_code VARCHAR2(40) := p_return_code;' || g_new_line ||
1182 'sdp_internal_err_str VARCHAR2(40) := p_error_description;' || g_new_line;
1183 return (l_AdditionalStuff);
1184 else
1185 return (null);
1186 end if;
1187
1188 end getAdditionalStuff;
1189
1190 --
1191 -- Meta-Data for Procedure Initializations to be done
1192 -- For e.g. The Fulfillment Procedures requires the Parameter Cache to be initialized
1193 -- the SYNC message to be sent etc. All this is wrapped in an "initfp" routine
1194 -- and it is registared in this routine to be generated
1195 -- A Default Initialization routine "initdefault" is generated
1196 -- All these initialzation routines are in XDP_MACROS package
1197 Function getInitialization(p_ProcType in varchar2,
1198 p_ProcName in varchar2) return varchar2
1199 is
1200 l_InitStuff varchar2(2000);
1201 begin
1202 if p_ProcType = g_FPType then
1203 -- Initialization for a Fulfillment Procedure
1204 -- Initialize the Cache etc..
1205 l_InitStuff := 'xdp_macros.initfp(p_order_id, p_line_item_id, p_wi_instance_id, ' ||
1206 'p_fa_instance_id, p_channel_name, p_fe_name, ''' ||
1207 p_ProcName || ''');' ||
1208 g_new_line;
1209 elsif p_ProcType in (g_ConnectType, g_DisconnectType) then
1210 -- Initialization for a Connect/DisconnectProcedure
1211 -- Cleanup buffers etc...
1212 l_InitStuff := 'xdp_macros.initconnection(p_channel_name, p_fe_name);' || g_new_line;
1213 elsif p_ProcType = g_SvcWIMapType then
1214 -- Default Initialization
1215 l_InitStuff := 'xdp_macros.initdefault(p_order_id, p_line_item_id, null, '||
1216 'null);' ||g_new_line;
1217 elsif p_ProcType in (g_WIFAMapType, g_WIWFStartType, g_WIParamEvalType) then
1218 l_InitStuff := 'xdp_macros.initdefault(p_order_id, p_line_item_id, p_wi_instance_id, '||
1219 'null);' ||g_new_line;
1220 else
1221 l_InitStuff := 'xdp_macros.initdefault(p_order_id, p_line_item_id, p_wi_instance_id, '||
1222 'p_fa_instance_id);' ||g_new_line;
1223 end if;
1224
1225 return (l_InitStuff);
1226
1227 end getInitialization;
1228
1229 --
1230 -- Meta-Data for Procedure exit to be done
1231 -- For e.g. The Fulfillment Procedures requires the Parameter Cache to be cleared
1232 -- All this is wrapped in an "EndProc" routine and it is registared in this routine
1233 -- to be generated
1234 -- All these finilization routines are in XDP_MACROS package
1235 Function getFinilization(p_ProcType in varchar2,
1236 p_ProcName in varchar2) return varchar2
1237 is
1238 l_finalStuff varchar2(500);
1239 begin
1240 l_finalStuff := g_new_line || g_new_line ||
1241 'xdp_macros.EndProc(p_return_code, p_error_description);' || g_new_line;
1242 return (l_finalStuff);
1243
1244 end getFinilization;
1245
1246 --
1247 -- Initilization to clean up package variables
1248 Procedure Init
1249 is
1250
1251 begin
1252 g_ProcSpec := null;
1253 g_ProcHeader := null;
1254 g_ProcFooter := null;
1255
1256 g_ProcBody := null;
1257
1258 g_PackageSpec := null;
1259 g_PackageHeader := null;
1260 g_PackageFooter := ' END ';
1261
1262 g_PackageBody := null;
1263
1264 end Init;
1265
1266 --
1267 -- Meta-Data for Procedure Specifications, Procedure Comments
1268 Procedure InitGlobals
1269 is
1270 l_temp varchar2(10) := ')' || g_new_line;
1271
1272 begin
1273 -- Svc - WI mapping
1274 g_SvcWIMapSpec :=
1275 'p_order_id IN NUMBER,' || g_new_line ||
1276 'p_line_item_id IN NUMBER,' || g_new_line ||
1277 'p_return_code OUT NUMBER,' || g_new_line ||
1278 'p_error_description OUT VARCHAR2';
1279
1280 g_SvcWIMapComments := g_StartComment || g_new_line ||
1281 g_SvcWIMapSpec || g_new_line ||
1282 g_EndComment || g_new_line;
1283
1284 g_SvcWIMapSpec := g_SvcWIMapSpec || l_temp;
1285
1286
1287 -- WI - FA Mapping
1288 g_WIFAMapSpec :=
1289 'p_order_id IN NUMBER,' || g_new_line ||
1290 'p_line_item_id IN NUMBER,' || g_new_line ||
1291 'p_wi_instance_id IN NUMBER,' || g_new_line ||
1292 'p_return_code OUT NUMBER,' || g_new_line ||
1293 'p_error_description OUT VARCHAR2';
1294
1295 g_WIFAMapComments := g_StartComment || g_new_line ||
1296 g_WIFAMapSpec || g_new_line ||
1297 g_EndComment || g_new_line;
1298
1299 g_WIFAMapSpec := g_WIFAMapSpec || l_temp;
1300
1301
1302 -- WI WF Start
1303 g_WIWFStartSpec :=
1304 'p_order_id IN NUMBER,' || g_new_line ||
1305 'p_line_item_id IN NUMBER,' || g_new_line ||
1306 'p_wi_instance_id IN NUMBER,' || g_new_line ||
1307 'p_wf_item_type OUT VARCHAR2,' || g_new_line ||
1308 'p_wf_item_key OUT VARCHAR2,' || g_new_line ||
1309 'p_wf_process_name OUT VARCHAR2,' || g_new_line ||
1310 'p_return_code OUT NUMBER,' || g_new_line ||
1311 'p_error_description OUT VARCHAR2';
1312
1313 g_WIWFStartComments := g_StartComment || g_new_line ||
1314 g_WIWFStartSpec || g_new_line ||
1315 g_EndComment || g_new_line;
1316
1317 g_WIWFStartSpec := g_WIWFStartSpec || l_temp;
1318
1319
1320 -- WI Param Eval
1321 g_WIParamEvalSpec :=
1322 'p_order_id IN NUMBER,' || g_new_line ||
1323 'p_line_item_id IN NUMBER,' || g_new_line ||
1324 'p_wi_instance_id IN NUMBER,' || g_new_line ||
1325 'p_param_val IN VARCHAR2,' || g_new_line ||
1326 'p_param_ref_val IN VARCHAR2,' || g_new_line ||
1327 'p_param_eval_val OUT VARCHAR2,' || g_new_line ||
1328 'p_param_eval_ref_val OUT VARCHAR2,' || g_new_line ||
1329 'p_return_code OUT NUMBER,' || g_new_line ||
1330 'p_error_description OUT VARCHAR2';
1331
1332 g_WIParamEvalComments := g_StartComment || g_new_line ||
1333 g_WIParamEvalSpec || g_new_line ||
1334 g_EndComment || g_new_line;
1335
1336 g_WIParamEvalSpec := g_WIParamEvalSpec || l_temp;
1337
1338
1339 -- FA Param Eval
1340 g_FAParamEvalSpec :=
1341 'p_order_id IN NUMBER,' || g_new_line ||
1342 'p_line_item_id IN NUMBER,' || g_new_line ||
1343 'p_wi_instance_id IN NUMBER,' || g_new_line ||
1344 'p_fa_instance_id IN NUMBER,' || g_new_line ||
1345 'p_param_val IN VARCHAR2,' || g_new_line ||
1346 'p_param_ref_val IN VARCHAR2,' || g_new_line ||
1347 'p_param_eval_val OUT VARCHAR2,' || g_new_line ||
1348 'p_param_eval_ref_val OUT VARCHAR2,' || g_new_line ||
1349 'p_return_code OUT NUMBER,' || g_new_line ||
1350 'p_error_description OUT VARCHAR2';
1351
1352 g_FAParamEvalComments := g_StartComment || g_new_line ||
1353 g_FAParamEvalSpec || g_new_line ||
1354 g_EndComment || g_new_line;
1355
1356 g_FAParamEvalSpec := g_FAParamEvalSpec || l_temp;
1357
1358
1359 -- FA Eval All Param
1360 g_EvalAllFAParamSpec :=
1361 'p_order_id IN NUMBER,' || g_new_line ||
1362 'p_line_item_id IN NUMBER,' || g_new_line ||
1363 'p_wi_instance_id IN NUMBER,' || g_new_line ||
1364 'p_fa_instance_id IN NUMBER,' || g_new_line ||
1365 'p_return_code OUT NUMBER,' || g_new_line ||
1366 'p_error_description OUT VARCHAR2';
1367
1368 g_EvalAllFAParamComments := g_StartComment || g_new_line ||
1369 g_EvalAllFAParamSpec || g_new_line ||
1370 g_EndComment || g_new_line;
1371
1372 g_EvalAllFAParamSpec := g_EvalAllFAParamSpec || l_temp;
1373
1374
1375 -- Locate FE
1376 g_LocateFESpec := 'p_order_id IN NUMBER,' || g_new_line ||
1377 'p_line_item_id IN NUMBER,' || g_new_line ||
1378 'p_wi_instance_id IN NUMBER,' || g_new_line ||
1379 'p_fa_instance_id IN NUMBER,' || g_new_line ||
1380 'p_fe_name OUT VARCHAR2,' || g_new_line ||
1381 'p_return_code OUT NUMBER,' || g_new_line ||
1382 'p_error_description OUT VARCHAR2';
1383
1384 g_LocateFEComments := g_StartComment || g_new_line ||
1385 g_LocateFESpec || g_new_line ||
1386 g_EndComment || g_new_line;
1387
1388 g_LocateFESpec := g_LocateFESpec || l_temp;
1389
1390
1391 -- FP
1392 g_FPSpec := 'p_order_id IN NUMBER,' || g_new_line ||
1393 'p_line_item_id IN NUMBER,' || g_new_line ||
1394 'p_wi_instance_id IN NUMBER,' || g_new_line ||
1395 'p_fa_instance_id IN NUMBER,' || g_new_line ||
1396 'p_channel_name IN VARCHAR2,' || g_new_line ||
1397 'p_fe_name IN VARCHAR2,' || g_new_line ||
1398 'p_fa_item_type IN VARCHAR2,' || g_new_line ||
1399 'p_fa_item_key IN VARCHAR2,' || g_new_line ||
1400 'p_return_code OUT NUMBER,' || g_new_line ||
1401 'p_error_description OUT VARCHAR2';
1402
1403 g_FPComments := g_StartComment || g_new_line ||
1404 g_FPSpec || g_new_line ||
1405 g_EndComment || g_new_line;
1406
1407 g_FPSpec := g_FPSpec || l_temp;
1408
1409
1410 -- Connect
1411 g_ConnectSpec := 'p_fe_name IN VARCHAR2,' || g_new_line ||
1412 'p_channel_name IN VARCHAR2,' || g_new_line ||
1413 'p_return_code OUT NUMBER,' || g_new_line ||
1414 'p_error_description OUT VARCHAR2';
1415
1416 g_ConnectComments := g_StartComment || g_new_line ||
1417 g_ConnectSpec || g_new_line ||
1418 g_EndComment || g_new_line;
1419
1420 g_ConnectSpec := g_ConnectSpec || l_temp;
1421
1422
1423 -- Disconnect
1424 g_DisconnectSpec :='p_fe_name IN VARCHAR2,' || g_new_line ||
1425 'p_channel_name IN VARCHAR2,' || g_new_line ||
1426 'p_return_code OUT NUMBER,' || g_new_line ||
1427 'p_error_description OUT VARCHAR2';
1428
1429 g_DisconnectComments := g_StartComment || g_new_line ||
1430 g_DisconnectSpec || g_new_line ||
1431 g_EndComment || g_new_line;
1432
1433 g_DisconnectSpec := g_DisconnectSpec || l_temp;
1434
1435 InitDisplayNames;
1436 end InitGlobals;
1437
1438 --
1439 -- Meta-Data for Display Names. In case of errors the error message
1440 -- must contain translated messages. This routine sets it up once
1441 Procedure InitDisplayNames
1442 is
1443
1444 begin
1445
1446 g_SvcWIMapTypeDisp := FetchLookupMeaning(g_ProcType,'DYNAMIC_WI_MAPPING');
1447 g_WIFAMapTypeDisp := FetchLookupMeaning(g_ProcType,'DYNAMIC_FA_MAPPING');
1448 g_WIWFStartTypeDisp := FetchLookupMeaning(g_ProcType,'EXEC_WI_WORKFLOW');
1449
1450 g_WIParamEvalTypeDisp := FetchLookupMeaning(g_ProcType,'WI_PARAM_EVAL_PROC');
1451 g_FAParamEvalTypeDisp := FetchLookupMeaning(g_ProcType,'FA_PARAM_EVAL_PROC');
1452 g_EvalAllFAParamTypeDisp := FetchLookupMeaning(g_ProcType,'FA_PARAM_EVAL_ALL_PROC');
1453
1454 g_LocateFETypeDisp := FetchLookupMeaning(g_ProcType,'LOCATE_FE');
1455 g_FPTypeDisp := FetchLookupMeaning(g_ProcType,'PROVISIONING');
1456
1457 g_ConnectTypeDisp := FetchLookupMeaning(g_ProcType,'CONNECT');
1458 g_DisconnectTypeDisp := FetchLookupMeaning(g_ProcType,'DISCONNECT');
1459
1460 pv_ParamWIDisp := FetchLookupMeaning(g_ParamType, 'WORKITEM_PARAM');
1461 pv_ParamFADisp := FetchLookupMeaning(g_ParamType, 'FA_PARAM');
1462 pv_ParamLineDisp := FetchLookupMeaning(g_ParamType, 'LINE_PARAM');
1463 pv_ParamOrderDisp := FetchLookupMeaning(g_ParamType, 'ORDER_PARAM');
1464 pv_ParamFEDisp := FetchLookupMeaning(g_ParamType, 'FE_ATTR');
1465
1466 end InitDisplayNames;
1467
1468
1469 --
1470 -- Meta-data for Procedure Name Display names
1471 -- Return the display for a procedure type
1472 Function GetProcDispName(p_ProcType in varchar2) return varchar2
1473 is
1474
1475 begin
1476 if p_ProcType = g_FPType then
1477 return(g_FPTypeDisp);
1478 elsif p_ProcType = g_ConnectType then
1479 return (g_ConnectTypeDisp);
1480 elsif p_ProcType = g_DisconnectType then
1481 return (g_DisconnectTypeDisp);
1482 elsif p_ProcType = g_SvcWIMapType then
1483 return (g_SvcWIMapTypeDisp);
1484 elsif p_ProcType = g_WIFAMapType then
1485 return (g_WIFAMapTypeDisp);
1486 elsif p_ProcType = g_WIWFStartType then
1487 return (g_WIWFStartTypeDisp);
1488 elsif p_ProcType = g_WIParamEvalType then
1489 return (g_WIParamEvalTypeDisp);
1490 elsif p_ProcType = g_FAParamEvalType then
1491 return (g_FAParamEvalTypeDisp);
1492 elsif p_ProcType = g_EvalAllFAParamType then
1493 return (g_EvalAllFAParamTypeDisp);
1494 elsif p_ProcType = g_LocateFEType then
1495 return (g_LocateFETypeDisp);
1496 else
1497 return (p_ProcType);
1498 end if;
1499
1500
1501 end GetProcDispName;
1502
1503 --
1504 -- Meta-data for Parameter Name Display names
1505 -- Return the display for Parameter type(s) in a "," separated string
1506 -- This is used when setting the token for a list of paramter types
1507 Function GetParamDispName(p_OrderParam in boolean default false,
1508 p_LineParam in boolean default false,
1509 p_WIParam in boolean default false,
1510 p_FAParam in boolean default false) return varchar2
1511 is
1512 l_Token varchar2(500);
1513 l_prepend boolean := false;
1514 begin
1515 if p_OrderParam then
1516 l_Token := pv_ParamOrderDisp;
1517 l_prepend := true;
1518 end if;
1519 if p_LineParam then
1520 if l_prepend then
1521 l_Token := l_Token || ', ';
1522 end if;
1523 l_Token := l_Token || pv_ParamLineDisp ;
1524 l_prepend := true;
1525 end if;
1526 if p_WIParam then
1527 if l_prepend then
1528 l_Token := l_Token || ', ';
1529 end if;
1530 l_Token := l_Token || pv_ParamWIDisp ;
1531 l_prepend := true;
1532 end if;
1533 if p_FAParam then
1534 if l_prepend then
1535 l_Token := l_Token || ', ';
1536 end if;
1537 l_Token := l_Token || pv_ParamFADisp ;
1538 l_prepend := true;
1539 end if;
1540
1541 return (l_Token);
1542
1543 end GetParamDispName;
1544
1545 --
1546 -- Routine for getting Display Names.
1547 -- Get the display names for Different Procedure Types and Paramter Dispay Names
1548 Function FetchLookupMeaning(p_LookupType in varchar2,
1549 p_LookupCode in varchar2) return varchar2
1550 is
1551 l_Meaning varchar2(80) := p_LookupCode;
1552 begin
1553
1554 if p_LookupType = g_ProcType then
1555 for v_getProcLookupMeaning in c_getProcLookupMeaning(p_LookupCode) loop
1556 l_Meaning := v_getProcLookupMeaning.meaning;
1557 end loop;
1558 elsif p_LookupType = g_ParamType then
1559 for v_getParamLookupMeaning in c_getParamLookupMeaning(p_LookupCode) loop
1560 l_Meaning := v_getParamLookupMeaning.meaning;
1561 end loop;
1562 end if;
1563
1564 return (l_Meaning);
1565
1566 end FetchLookupMeaning;
1567
1568 Procedure FetchAdapterApplMode( p_AdapterType in varchar2,
1569 x_ErrorCode OUT NOCOPY number,
1570 x_ErrorString OUT NOCOPY varchar2)
1571 is
1572 begin
1573 g_AdapterType := p_AdapterType;
1574
1575 for v_getAdapterApplMode in c_getAdapterApplMode loop
1576 g_ApplMode := v_getAdapterApplMode.application_mode;
1577 g_AdapterTypeDisp := v_getAdapterApplMode.display_name;
1578 end loop;
1579
1580 end FetchAdapterApplMode;
1581
1582
1583 begin
1584
1585 InitGlobals;
1586 end XDP_PROCEDURE_BUILDER;