1 PACKAGE BODY XDP_PROCEDURE_UTIL AS
2 /* $Header: XDPPUTLB.pls 120.1 2005/06/16 02:27:41 appldev $ */
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
9 FUNCTION get_compilation_error(p_proc_name IN VARCHAR2)
10 RETURN varchar2;
11
12 Function get_procedure_body (proc_name varchar2)
13 RETURN varchar2;
14
15 Procedure CheckIfDuplicate(p_proc_name IN VARCHAR2,
16 p_proc_type IN VARCHAR2,
17 p_duplicate OUT NOCOPY BOOLEAN,
18 p_dup_type OUT NOCOPY VARCHAR2);
19
20 Function CheckIfProcExists(p_proc_name IN VARCHAR2) return BOOLEAN;
21
22 Procedure PreCompileProvProc(p_ProcName in varchar2,
23 p_FAID in number,
24 p_ProcBody in varchar2,
25 p_ErrCode OUT NOCOPY number,
26 p_ProcErrors OUT NOCOPY varchar2);
27
28 Procedure PreCompileConnectProc(p_ProcName in varchar2,
29 p_FeTypeID in number,
30 p_ProcBody in varchar2,
31 p_ErrCode OUT NOCOPY number,
32 p_ProcErrors OUT NOCOPY varchar2);
33
34 FUNCTION get_compilation_error(p_proc_name IN VARCHAR2)
35 RETURN varchar2
36 IS
37 lv_err varchar2(2000):= ' ';
38 CURSOR lc_text IS
39 select text from user_errors
40 where name = p_proc_name;
41 BEGIN
42
43 for lc_text_rec IN lc_text LOOP
44 if length(lv_err) < 2000 then
45 lv_err := lv_err || substr(lc_text_rec.text, 1, 1998 - length(lv_err)) || ' ';
46 end if;
47 end loop;
48
49 return lv_err;
50 END get_compilation_error;
51
52 procedure get_package_name(p_proc_name IN VARCHAR2,
53 p_package_name OUT NOCOPY varchar2,
54 return_code OUT NOCOPY NUMBER,
55 error_string OUT NOCOPY VARCHAR2)
56 IS
57 lv_tmp varchar2(80);
58 BEGIN
59 if length(p_proc_name) > 23 then
60 return_code := -1;
61 return;
62 end if;
63
64 if fnd_profile.defined('XDP_PACKAGE_SUFFIX') then
65 fnd_profile.get('XDP_PACKAGE_SUFFIX',lv_tmp);
66 lv_tmp := substr(lv_tmp,1,3);
67 else
68 lv_tmp := '_U';
69 end if;
70
71 p_package_name := 'XDP_' || p_proc_name || lv_tmp;
72
73 END get_package_name;
74
75
76 Function decode_proc_name(ProcName in varchar2) return varchar2 is
77 begin
78 return (substr(ProcName,(instr(ProcName,'.',1) + 1), length(ProcName)));
79
80 end decode_proc_name;
81
82
83
84
85
86 FUNCTION get_procedure_body (proc_name varchar2)
87 RETURN VARCHAR2
88 IS
89 lv_tmp_string varchar2(32767);
90 BEGIN
91 lv_tmp_string := XDP_Utilities.Get_CLOB_Value(proc_name);
92 return lv_tmp_string;
93
94 END get_procedure_body;
95
96
97
98 Procedure PreCompileProvProc(p_ProcName in varchar2,
99 p_FAID in number,
100 p_ProcBody in varchar2,
101 p_ErrCode OUT NOCOPY number,
102 p_ProcErrors OUT NOCOPY varchar2)
103 is
104
105 begin
106 p_ErrCode := 0;
107 p_ProcErrors := NULL;
108
109 XDP_PROC_CTL.FIND_PARAMETERS(p_FAID, 0, p_ProcBody, p_ErrCode, p_ProcErrors);
110
111 if p_ErrCode <> 0 then
112 return;
113 end if;
114
115 exception
116 when others then
117 p_ErrCode := SQLCODE;
118 p_ProcErrors := SUBSTR(SQLERRM,1,255);
119 end PreCompileProvProc;
120
121
122
123
124 Procedure PreCompileConnectProc(p_ProcName in varchar2,
125 p_FeTypeID in number,
126 p_ProcBody in varchar2,
127 p_ErrCode OUT NOCOPY number,
128 p_ProcErrors OUT NOCOPY varchar2)
129 is
130 begin
131
132 p_ErrCode := 0;
133 p_ProcErrors := NULL;
134
135 XDP_PROC_CTL.FIND_CONNECT_PARAMETERS(p_FeTypeID, p_ProcBody, p_ErrCode, p_ProcErrors);
136
137 if p_ErrCode <> 0 then
138 return;
139 end if;
140
141 exception
142 when others then
143 p_ErrCode := SQLCODE;
144 p_ProcErrors := SUBSTR(SQLERRM,1,255);
145 end PreCompileConnectProc;
146
147
148 Function Get_Package_Spec(
149 p_proc_type IN VARCHAR2) return varchar2
150 is
151
152 l_ProcSpec varchar2(32767);
153
154 e_InvalidProcException exception;
155 begin
156
157 IF p_proc_type = 'PROVISIONING' THEN
158
159 l_ProcSpec :=
160 '/*****************************************************************************
161 This procedure is called by the FA to provision a FE for a particular service.
162 It has the following input parameters and no output parameters:
163
164 order_id IN NUMBER -- order ID
165 line_item_id IN NUMBER -- Line Item ID
166 workitem_instance_id IN NUMBER -- workitem instance ID
167 fa_instance_id IN NUMBER -- FA instance ID
168 db_channel_name IN VARCHAR2 -- Channel name used by this procedure
169 fe_name IN VARCHAR2 -- FE name to be provisioned by this procedure
170 fa_item_type IN VARCHAR2 -- FA workflow process item type
171 fa_item_key IN VARCHAR2 -- FA workflow process item key
172 *****************************************************************************/
173
174 -- Enter your procedure below:
175
176 BEGIN
177
178 -- your code...
179 null;
180
181 END;';
182
183
184 ELSIF p_proc_type in ('CONNECT', 'DISCONNECT') THEN
185
186 l_ProcSpec :=
187 '/*****************************************************************************
188 This procedure is used by the INTERACTIVE adapter to establish a connection
189 to the FE. This procedure is invoked when SFM is started or through the
190 Connection Management Utility (CMU).
191 It has the following input parameters and no output parameters:
192
193 fe_name IN Varchar2 -- name of the FE this procedure will connect to
194 channel_name IN Varchar2 -- name of the channel this procedure will use
195 *****************************************************************************/
196
197 -- Enter your procedure below:
198
199 BEGIN
200
201 -- your code...
202 null;
203
204 END;';
205
206 ELSIF p_proc_type = 'LOCATE_FE' THEN
207
208 l_ProcSpec :=
209 '/*****************************************************************************
210 This procedure returns the Fulfillment Element (FE) name of the FE
211 that is to be provisioned by this Fulfillment Action (FA).
212 It has the following input and output parameters:
213
214 p_order_id IN NUMBER -- order ID
215 p_wi_instance_id IN NUMBER -- workitem instance ID
216 p_fa_instance_id IN NUMBER -- FA instance ID
217
218 p_fe_name OUT VARCHAR2 -- FE to be provisioned
219 *****************************************************************************/
220
221 -- Enter your procedure below:
222
223 BEGIN
224
225 -- your code...
226 null;
227
228 END;';
229
230
231 ELSIF p_proc_type = 'WI_PARAM_EVAL_PROC' THEN
232
233 l_ProcSpec :=
234 '/*****************************************************************************
235 This procedure calculates a new value for the Workitem parameter.
236 It has the following input and output parameters:
237
238 p_order_id IN NUMBER -- order ID
239 p_wi_instance_id IN NUMBER -- workitem instance ID
240 p_param_val IN VARCHAR2 -- parameter initial value
241 p_param_ref_val IN VARCHAR2 -- reference value (if order amendment)
242
243 p_param_eval_val OUT VARCHAR2 -- parameter new value
244 p_param_eval_ref_val OUT VARCHAR2 -- new reference value (if order amendment)
245 *****************************************************************************/
246
247 -- Enter your procedure below:
248
249 BEGIN
250
251 -- your code...
252 null;
253
254 END;';
255
256 ELSIF p_proc_type = 'FA_PARAM_EVAL_PROC' THEN
257
258 l_ProcSpec :=
259 '/*****************************************************************************
260 This procedure calculates a new value for the Fulfullment Action parameter.
261 It has the following input and output parameters:
262
263 p_order_id IN NUMBER -- order ID
264 p_wi_instance_id IN NUMBER -- workitem instance ID
265 p_fa_instance_id IN NUMBER -- FA instance ID
266 p_param_val IN VARCHAR2 -- parameter initial value
267 p_param_ref_val IN VARCHAR2 -- reference value (if order amendment)
268
269 p_param_eval_val OUT VARCHAR2 -- parameter new value
270 p_param_eval_ref_val OUT VARCHAR2 -- new reference value (if order amendment)
271 *****************************************************************************/
272
273 -- Enter your procedure below:
274
275 BEGIN
276
277 -- your code...
278 null;
279
280 END;';
281
282 ELSIF p_proc_type = 'FA_PARAM_EVAL_ALL_PROC' THEN
283
284 l_ProcSpec :=
285 '/*****************************************************************************
286 This procedure evaluates all parameters for the Fulfillment Action (FA).
287 It has the following input parameters and no output parameters:
288
289 p_order_id IN NUMBER -- order ID
290 p_wi_instance_id IN NUMBER -- workitem instance ID
291 p_fa_instance_id IN NUMBER -- FA instance ID
292
293 *****************************************************************************/
294
295 -- Enter your procedure below:
296
297 BEGIN
298
299 -- your code...
300 null;
301
302 END;';
303
304 ELSIF p_proc_type = 'DYNAMIC_FA_MAPPING' THEN
305
306 l_ProcSpec :=
307 '/*****************************************************************************
308 This procedure determines at runtime (dynamically) which FAs are used
309 by this workitem.
310 Use the XDP_ENG_UTIL.Add_FA_toWI() procedure to specify the FAs
311 that need to be called as part of the execution of this workitem.
312 the spec of Add_FA_to_WI() is:
313 procedure Add_FA_to_WI( p_wi_instance_id IN NUMBER,
314 p_FA_name IN VARCHAR2,
315 p_FE_name IN VARCHAR2,
316 p_priority IN NUMBER,
317 p_provisioning_seq IN NUMBER)
318
319 This procedure has the following input parameters and no output parameters:
320
321 p_order_id IN NUMBER -- order ID
322 p_wi_instance_id IN NUMBER -- workitem instance ID
323 *****************************************************************************/
324
325 -- Enter your procedure below:
326
327 BEGIN
328
329 -- your code...
330 null;
331
332 END;';
333
334 ELSIF p_proc_type = 'DYNAMIC_WI_MAPPING' THEN
335
336 l_ProcSpec :=
337 '/*****************************************************************************
338 This procedure is used to determine at runtime (dynamically) the workitems
339 that are to be executed to provision this service.
340 It has the following input parameters and no output parameters:
341
342 p_order_id IN NUMBER -- order ID
343 p_line_item_id IN NUMBER -- line item ID
344 *****************************************************************************/
345
346 -- Enter your procedure below:
347
348 BEGIN
349
350 -- your code...
351 null;
352
353 END;';
354
355 ELSIF p_proc_type = 'EXEC_WI_WORKFLOW' THEN
356
357 l_ProcSpec :=
358 '/*****************************************************************************
359 This procedure can be used to specify a user defined workflow that should be
360 used to provision the service. This allows you to decide which workflow to
361 use at runtime (dynamically).
362 NOTE: you MUST create the workflow process (WF_ENGINE.createProcess()), BUT
363 you can NOT start the process. Starting the process will be performed
364 by SFM. You MUST return the itemtype, itemkey and process name in the
365 output parameters provided.
366 It has the following input and output parameters:
367
368 p_order_id IN NUMBER -- order ID
369 p_wi_instance_id IN NUMBER -- workitem instance ID
370
371 p_wf_item_type OUT VARCHAR2 -- itemtype of user workflow
372 p_wf_item_key OUT VARCHAR2 -- itemkey of user workflow
373 p_wf_process_name OUT VARCHAR2 -- process name of user workflow
374 *****************************************************************************/
375
376 -- Enter your procedure below:
377
378 BEGIN
379
380 -- your code...
381 null;
382
383 END;';
384
385
386 ELSE
387 raise e_InvalidProcException;
388
389 END IF;
390
391 return l_ProcSpec;
392
393 end Get_Package_Spec;
394
395
396 PROCEDURE Create_Package_Spec(
397 p_proc_name IN VARCHAR2,
398 p_proc_type IN VARCHAR2,
399 return_code OUT NOCOPY NUMBER,
400 error_string OUT NOCOPY VARCHAR2)
401 IS
402
403 lv_return_code NUMBER;
404 lv_error_description VARCHAR2(32000);
405 lv_out_str VARCHAR2(32600);
406
407 lv_package_name varchar2(80);
408
409 lv_dup_type varchar2(80);
410 lv_dup BOOLEAN;
411
412 BEGIN
413 return_code := 0;
414
415 /*
416 CheckIfDuplicate(p_proc_name, p_proc_type, lv_dup, lv_dup_type);
417
418 IF lv_dup then
419 return_code := -20111;
420 error_string := 'Error: A procedure of a different type '||lv_dup_type ||' already exists with the same name.';
421 return;
422 END IF;
423
424
425 get_package_name(p_proc_name, lv_package_name, return_code, error_string);
426 if return_code <> 0 then
427 return;
428 end if;
429 */
430 xdp_procedure_builder.generatepackagename(p_ProcType => p_proc_type,
431 p_ProcName => p_proc_name,
432 p_Validate => true,
433 x_PackageName => lv_package_name,
434 x_ErrorCode => return_code,
435 x_ErrorString => error_string);
436 if return_code <> 0 then
437 return;
438 end if;
439
440 xdp_procedure_builder.generatepackagespec(p_PackageName => lv_package_name,
441 p_ProcType => p_proc_type,
442 p_ProcName => p_proc_name,
443 x_ErrorCode => return_code,
444 x_ErrorString => error_string);
445
446 exception
447 when OTHERS THEN
448 return_code := SQLCODE;
449 error_string := SQLERRM;
450 END Create_Package_Spec;
451
452
453
454 PROCEDURE Create_Package_Body(
455 p_proc_name IN VARCHAR2,
456 p_proc_type IN VARCHAR2,
457 p_FaID in NUMBER,
458 p_FeTypeID in NUMBER,
459 p_proc_body IN VARCHAR2,
460 return_code OUT NOCOPY NUMBER,
461 error_string OUT NOCOPY VARCHAR2)
462 IS
463
464 lv_return_code NUMBER;
465 lv_error_description VARCHAR2(32000);
466 lv_out_str VARCHAR2(32767);
467
468
469 lv_package_name varchar2(80);
470
471 lv_dup_type varchar2(80);
475
472 lv_dup BOOLEAN;
473
474 lv_Id number;
476 e_NotProfileFoundException exception;
477
478 BEGIN
479 return_code := 0;
480
481 IF p_proc_body IS NULL THEN
482 return_code := -20111;
483 error_string := 'Error: The procedure body is empty.';
484 return;
485 END IF;
486
487 xdp_procedure_builder.generatepackagename(p_ProcType => p_proc_type,
488 p_ProcName => p_proc_name,
489 p_Validate => true,
490 x_PackageName => lv_package_name,
491 x_ErrorCode => return_code,
492 x_ErrorString => error_string);
493 if return_code <> 0 then
494 return;
495 end if;
496
497 if p_Proc_type in
498 (xdp_procedure_builder.g_ConnectType, xdp_procedure_builder.g_DisconnectType) then
499 lv_ID := p_FeTypeID;
500 elsif p_Proc_type in (xdp_procedure_builder.g_FPType,
501 xdp_procedure_builder.g_LocateFEType,
502 xdp_procedure_builder.g_FAParamEvalType) then
503 lv_ID := p_FaID;
504 else
505 lv_ID := null;
506 end if;
507
508 return_code := 0;
509 xdp_procedure_builder.generatepackagebody(p_PackageName => lv_package_name,
510 p_ProcType => p_proc_type,
511 p_ProcName => p_proc_name,
512 p_ProcBody => p_proc_body,
513 x_ErrorCode => return_code,
514 x_ErrorString => error_string);
515
516 exception
517 when OTHERS THEN
518 return_code := SQLCODE;
519 error_string := SQLERRM;
520 END Create_Package_Body;
521
522
523 PROCEDURE Load_Proc_Table(
524 p_proc_name IN VARCHAR2,
525 p_proc_type IN VARCHAR2,
526 p_proc_body IN VARCHAR2,
527 return_code OUT NOCOPY NUMBER,
528 error_string OUT NOCOPY VARCHAR2)
529 IS
530
531 lv_exists varchar2(1);
532 BEGIN
533 -- No longer supported
534 -- Obsoleted
535 return_code := 0;
536
537 END Load_Proc_Table;
538
539 PROCEDURE Rollback_Proc(
540 p_proc_name IN VARCHAR2,
541 p_proc_type IN VARCHAR2,
542 p_FaID IN NUMBER,
543 p_FeTypeID IN NUMBER,
544 return_code OUT NOCOPY NUMBER,
545 error_string OUT NOCOPY VARCHAR2)
546 IS
547
548 lv_proc_body varchar2(32600);
549
550 BEGIN
551 return_code := 0;
552
553 if not CheckIfProcExists(p_proc_name) then
554 return_code := -20111;
555 error_string := 'Error: Can not rollback. Procedure not exists.';
556 return;
557 end if;
558
559 lv_proc_body := get_procedure_body (p_proc_name );
560 Create_Package_Body(
561 p_proc_name ,
562 p_proc_type ,
563 p_FaID,
564 p_FeTypeID,
565 lv_proc_body,
566 return_code ,
567 error_string );
568
569 END Rollback_Proc;
570
571
572 Procedure CheckIfDuplicate(p_proc_name IN VARCHAR2,
573 p_proc_type IN VARCHAR2,
574 p_duplicate OUT NOCOPY BOOLEAN,
575 p_dup_type OUT NOCOPY VARCHAR2)
576 is
577
578 CURSOR c_CheckDup is
579 select proc_type
580 from xdp_proc_body
581 where proc_name = p_proc_name
582 and proc_type <> p_proc_type;
583 begin
584
585 p_duplicate := FALSE;
586 p_dup_type := NULL;
587
588 for v_CheckDup in c_CheckDup loop
589 p_duplicate := TRUE;
590 p_dup_type := v_CheckDup.proc_type;
591 exit;
592 end loop;
593
594 end CheckIfDuplicate;
595
596
597 Function CheckIfProcExists(p_proc_name IN VARCHAR2) return BOOLEAN
598 is
599 lv_exists boolean := FALSE;
600
601 CURSOR c_CheckExists is
602 select 1
603 from xdp_proc_body
604 where proc_name = p_proc_name;
605 begin
606
607 for v_CheckExists in c_CheckExists loop
608 lv_exists := TRUE;
609 exit;
610 end loop;
611
612 return (lv_exists);
613
614 end CheckIfProcExists;
615
616 END XDP_PROCEDURE_UTIL;
[Home] [Help]