DBA Data[Home] [Help]

PACKAGE BODY: APPS.XDP_UTILITIES

Source


1 PACKAGE BODY XDP_UTILITIES AS
2 /* $Header: XDPUTILB.pls 120.1 2005/06/09 00:17:33 appldev  $ */
3 
4 -- internal DBMS_DESCRIBE.DESCRIBE_PROCEDURE variables
5   v_Overload     DBMS_DESCRIBE.NUMBER_TABLE;
6   v_Position     DBMS_DESCRIBE.NUMBER_TABLE;
7   v_Level        DBMS_DESCRIBE.NUMBER_TABLE;
8   v_ArgumentName DBMS_DESCRIBE.VARCHAR2_TABLE;
9   v_Datatype     DBMS_DESCRIBE.NUMBER_TABLE;
10   v_DefaultValue DBMS_DESCRIBE.NUMBER_TABLE;
11   v_InOut        DBMS_DESCRIBE.NUMBER_TABLE;
12   v_Length       DBMS_DESCRIBE.NUMBER_TABLE;
13   v_Precision    DBMS_DESCRIBE.NUMBER_TABLE;
14   v_Scale        DBMS_DESCRIBE.NUMBER_TABLE;
15   v_Radix        DBMS_DESCRIBE.NUMBER_TABLE;
16   v_Spare        DBMS_DESCRIBE.NUMBER_TABLE;
17 
18  PROCEDURE DO_COMMIT IS
19  -- PL/SQL Block
20 BEGIN
21     COMMIT;
22  END DO_COMMIT;
23 
24  PROCEDURE DO_ROLLBACK IS
25  -- PL/SQL Block
26 BEGIN
27     ROLLBACK;
28  END DO_ROLLBACK;
29 
30 
31  -- Call any SFM Workitem Parameter evaluation procedure
32  --
33  -- the user defined WI parameter evaluation procedure should use
34  -- the following spec:
35  -- procedure <name of the proc>(
36  --      p_order_id 	IN NUMBER,
37  --      p_line_item_id 	IN NUMBER,
38  --      p_wi_instance_id 	IN NUMBER,
39  -- 	   p_param_val		IN Varchar2,
40  --      p_param_ref_val	IN Varchar2,
41  --      p_param_eval_val	OUT NOCOPY VARCHAR2,
42  --      p_param_eval_ref_val OUT NOCOPY Varchar2,
43  --	   p_return_code	OUT NOCOPY NUMBER,
44  --	   p_error_description  OUT NOCOPY VARCHAR2)
45  --
46   PROCEDURE CallWIParamEvalProc(
47 		p_procedure_name  IN Varchar2,
48 		p_order_id		IN NUMBER,
49 		p_line_item_id		IN NUMBER,
50        	p_wi_instance_id 	IN NUMBER,
51   		p_param_val		IN Varchar2,
52        	p_param_ref_val	IN Varchar2,
53        	p_param_eval_val	OUT NOCOPY VARCHAR2,
54        	p_param_eval_ref_val OUT NOCOPY Varchar2,
55  		p_return_code	OUT NOCOPY NUMBER,
56  		p_error_description  OUT NOCOPY VARCHAR2)
57   IS
58      lv_plsql_blk varchar2(32000);
59      l_message_params  varchar2(2000);
60      l_wi_disp_name varchar2(100);
61      e_wi_param_eval_failed exception;
62   BEGIN
63 
64     p_return_code := 0;
65     lv_plsql_blk := 'BEGIN  '||
66 		    p_procedure_name||
67 		    '( :order_id,
68 			:line_item_id,
69 			:wi_instance_id,
70 	 		:param_val,
71 			:param_ref_val,
72 			:param_eval_val,
73 			:param_eval_ref_val,
74 			:ret,
75 			:err_str); end;';
76     execute immediate lv_plsql_blk
77      USING p_order_id,
78 	   p_line_item_id,
79 	   p_wi_instance_id,
80 	   p_param_val,
81 	   p_param_ref_val,
82 	   OUT p_param_eval_val,
83 	   OUT p_param_eval_ref_val,
84 	   OUT p_return_code,
85 	   OUT p_error_description;
86    IF p_return_code <> 0 THEN
87        xdpcore.context( 'XDP_UTILITIES', 'CallWIParamEvalProc', 'WI', p_wi_instance_id, p_error_description );
88    END IF;
89   EXCEPTION
90   WHEN OTHERS THEN
91     xdpcore.context( 'XDP_UTILITIES', 'CallWIParamEvalProc', 'WI', p_wi_instance_id, SQLERRM);
92 
93     p_return_code := SQLCODE;
94     p_error_description := SQLERRM;
95   END CallWIParamEvalProc;
96 
97  -- Call any SFM FA Parameter evaluation procedure
98  --
99  -- the user defined FA parameter evaluation procedure should use
100  -- the following spec:
101  -- procedure <name of the proc>(
102  --      p_order_id 		IN NUMBER,
103  --      p_line_item_id		IN NUMBER,
104  --      p_wi_instance_id 	IN NUMBER,
105  --      p_fa_instance_id 	IN NUMBER,
106  -- 	   p_param_val		IN Varchar2,
107  --      p_param_ref_val	IN Varchar2,
108  --      p_param_eval_val	OUT NOCOPY VARCHAR2,
109  --      p_param_eval_ref_val OUT NOCOPY Varchar2,
110  --	   p_return_code	OUT NOCOPY NUMBER,
111  --	   p_error_description  OUT NOCOPY VARCHAR2)
112  --
113   PROCEDURE CallFAParamEvalProc(
114 		p_procedure_name IN Varchar2,
115 		p_order_id		IN NUMBER,
116 		p_line_item_id		IN NUMBER,
117        	p_wi_instance_id 	IN NUMBER,
118        	p_fa_instance_id 	IN NUMBER,
119   		p_param_val		IN Varchar2,
120        	p_param_ref_val	IN Varchar2,
121        	p_param_eval_val	OUT NOCOPY VARCHAR2,
122        	p_param_eval_ref_val OUT NOCOPY Varchar2,
123  		p_return_code	OUT NOCOPY NUMBER,
124   		p_error_description  OUT NOCOPY VARCHAR2)
125   IS
126      lv_plsql_blk varchar2(32000);
127      l_message_params varchar2(2000);
128      l_fa_disp_name varchar2(100);
129      l_evaluation_failed_exception exception;
130   BEGIN
131 
132     p_return_code := 0;
133     lv_plsql_blk := 'BEGIN  '||
134 		    p_procedure_name||
135 		    '( :order_id,
136 			:line_item_id,
137 			:wi_instance_id,
138 			:fa_instance_id,
139 	 		:param_val,
140 			:param_ref_val,
141 			:param_eval_val,
142 			:param_eval_ref_val,
143 			:ret,
144 			:err_str); end;';
145     execute immediate lv_plsql_blk
146      USING p_order_id,
147 	   p_line_item_id,
148 	   p_wi_instance_id,
149 	   p_fa_instance_id,
150 	   p_param_val,
151 	   p_param_ref_val,
152 	   OUT p_param_eval_val,
153 	   OUT p_param_eval_ref_val,
154 	   OUT p_return_code,
155 	   OUT p_error_description;
156 
157    IF p_return_code <> 0 THEN
158        xdpcore.context( 'XDP_UTILITIES', 'CallFAParamEvalProc', 'FA', p_fa_instance_id, p_error_description );
159    END IF;
160 
161   EXCEPTION
162   WHEN OTHERS THEN
163     xdpcore.context( 'XDP_UTILITIES', 'CallFAParamEvalProc', 'FA', p_fa_instance_id, SQLERRM );
164     p_return_code := SQLCODE;
165     p_error_description := SQLERRM;
166   END CallFAParamEvalProc;
167 
168  -- Call any SFM FA evaluation procedure
169  --
170  -- the user defined FA evaluation procedure will
171  -- evaluate all the FA parameters when the FA instance
172  -- is added to a workitem at runtime. The procedure should use
173  -- the following spec:
174  -- procedure <name of the proc>(
175  --      p_order_id 		IN NUMBER,
176  --      p_line_item_id		IN NUMBER,
177  --      p_wi_instance_id 	IN NUMBER,
178  --      p_fa_instance_id 	IN NUMBER,
179  --	   p_return_code	OUT NOCOPY NUMBER,
180  --	   p_error_description  OUT NOCOPY VARCHAR2)
181  --
182   PROCEDURE CallFAEvalAllProc(
183 		p_procedure_name IN Varchar2,
184 		p_order_id		IN NUMBER,
185 		p_line_item_id		IN NUMBER,
186        	p_wi_instance_id 	IN NUMBER,
187        	p_fa_instance_id 	IN NUMBER,
188  		p_return_code	OUT NOCOPY NUMBER,
189   		p_error_description  OUT NOCOPY VARCHAR2)
190   IS
191      lv_plsql_blk varchar2(32000);
192   BEGIN
193 
194     p_return_code := 0;
195     lv_plsql_blk := 'BEGIN  '||
196 		    p_procedure_name||
197 		    '( :order_id,
198 			:line_item_id,
199 			:wi_instance_id,
200 			:fa_instance_id,
201 			:ret,
202 			:err_str); end;';
203     execute immediate lv_plsql_blk
204      USING p_order_id,
205 	   p_line_item_id,
206 	   p_wi_instance_id,
207 	   p_fa_instance_id,
208 	   OUT p_return_code,
209 	   OUT p_error_description;
210    IF p_return_code <> 0 THEN
211        xdpcore.context( 'XDP_UTILITIES', 'CallFAEvalAllProc', 'FA', p_fa_instance_id, p_error_description );
212    END IF;
213 
214   EXCEPTION
215   WHEN OTHERS THEN
216     xdpcore.context( 'XDP_UTILITIES', 'CallFAEvalAllProc', 'FA', p_fa_instance_id, SQLERRM );
217     p_return_code := SQLCODE;
218     p_error_description := SQLERRM;
219   END CallFAEvalAllProc;
220 
221  -- Call any SFM FE routing procedure
222  --
223  --   the user defined FE routing procedure is used by
224  --   SFM to determine which FE to talk to for an FA at runtime
225  --   base on the order information.  The procedure should use
226  --   the following spec:
227  --   procedure <name of the proc>(
228  --	     p_order_id		IN NUMBER,
229  --	     p_line_item_id		IN NUMBER,
230  --        p_wi_instance_id 	IN NUMBER,
231  --        p_fa_instance_id 	IN NUMBER,
232  --	     p_fe_name 		OUT NOCOPY VARCHAR2,
233  --	     p_return_code	OUT NOCOPY NUMBER,
234  --	     p_error_description  OUT NOCOPY VARCHAR2)
235  --
236 
237   PROCEDURE CallFERoutingProc(
238 		p_procedure_name  IN Varchar2,
239   		p_order_id		IN NUMBER,
240   		p_line_item_id		IN NUMBER,
241       	p_wi_instance_id 	IN NUMBER,
242        	p_fa_instance_id 	IN NUMBER,
243    		p_fe_name 		OUT NOCOPY VARCHAR2,
244  		p_return_code	OUT NOCOPY NUMBER,
245   		p_error_description  OUT NOCOPY VARCHAR2)
246   IS
247      lv_plsql_blk varchar2(32000);
248   BEGIN
249 
250     p_return_code := 0;
251     lv_plsql_blk := 'BEGIN  '||
252 		    p_procedure_name||
253 		    '( :order_id,
254 			:line_item_id,
255 			:wi_instance_id,
256 			:fa_instance_id,
257 			:fe_name,
258 			:ret,
259 			:err_str); end;';
260     execute immediate lv_plsql_blk
261      USING p_order_id,
262 	   p_line_item_id,
263 	   p_wi_instance_id,
264 	   p_fa_instance_id,
265 	   OUT p_fe_name,
266 	   OUT p_return_code,
267 	   OUT p_error_description;
268    IF p_return_code <> 0 THEN
269        xdpcore.context( 'XDP_UTILITIES', 'CallFERoutingProc', 'FA', p_fa_instance_id, p_error_description );
270    END IF;
271 
272   EXCEPTION
273   WHEN OTHERS THEN
274     xdpcore.context( 'XDP_UTILITIES', 'CallFERoutingProc', 'FA', p_fa_instance_id, SQLERRM );
275     p_return_code := SQLCODE;
276     p_error_description := SQLERRM;
277   END CallFERoutingProc;
278 
279  -- Call any SFM NEM connect/disconnect procedure
280  --
281  --   the user defined NEM connect/disconnect procedure should use
282  --   the following spec:
283  --   procedure <name of the proc>(
284  --        p_fe_name IN Varchar2,
285  --	     p_channel_name	IN Varchar2,
286  --	     p_return_code IN OUT NOCOPY NUMBER,
287  --        p_error_description IN OUT NOCOPY VARCHAR2)
288  --
289 
290   PROCEDURE Call_NEConnection_Proc(
291 				p_procedure_name IN Varchar2,
292 				p_fe_name IN Varchar2,
293  				p_channel_name	IN Varchar2,
294  				p_return_code OUT NOCOPY NUMBER,
295  				p_error_description OUT NOCOPY VARCHAR2)
296   IS
297      lv_plsql_blk varchar2(32000);
298   BEGIN
299 
300     p_return_code := 0;
301     lv_plsql_blk := 'BEGIN  '||
302 		    p_procedure_name||
303 		    '( :fe_name,
304 			:channel_name,
305 			:ret,
306 			:err_str); end;';
307     execute immediate lv_plsql_blk
308      USING p_fe_name,
309 	   p_channel_name,
310 	   OUT p_return_code,
311 	   OUT p_error_description;
312 
313   EXCEPTION
314   WHEN OTHERS THEN
315     p_return_code := SQLCODE;
316     p_error_description := SQLERRM;
317   END Call_NEConnection_Proc;
318 
319 
320  -- Call any SFM FA fulfillment procedure
321  --
322  --  the user defined fulfillment procedure should use
323  --  the following spec:
324  --  procedure <name of the proc>(
325  --         p_order_id IN NUMBER,
326  --		p_line_item_id IN NUMBER,
327  --         p_wi_instance_id IN NUMBER,
328  --         p_fa_instance_id IN NUMBER,
329  --	      p_channel_name	IN Varchar2,
330  --		p_fe_name		IN VARCHAR2,
331  --		p_fa_item_type IN VARCHAR2,
332  --		p_fa_item_key  IN VARCHAR2,
333  --         p_return_code OUT NOCOPY NUMBER,
334  --         p_error_description OUT NOCOPY VARCHAR2)
335  --
336 
337   PROCEDURE CallFulfillmentProc(
338 		p_procedure_name IN Varchar2,
339           	p_order_id IN NUMBER,
340 		p_line_item_id IN NUMBER,
341           	p_wi_instance_id IN NUMBER,
342           	p_fa_instance_id IN NUMBER,
343  	      	p_channel_name	IN Varchar2,
344  		p_fe_name		IN VARCHAR2,
345  		p_fa_item_type IN VARCHAR2,
346  		p_fa_item_key  IN VARCHAR2,
347           	p_return_code OUT NOCOPY NUMBER,
348           	p_error_description OUT NOCOPY VARCHAR2)
349   IS
350      lv_plsql_blk varchar2(32000);
351   BEGIN
352 
353     p_return_code := 0;
354     lv_plsql_blk := 'BEGIN  '||
355 		    p_procedure_name||
356 		    '( :order_id,
357 			:line_item_id,
358 			:wi_instance_id,
359 			:fa_instance_id,
360 	 		:channel_name,
361 			:fe_name,
362 			:fa_item_type,
363 			:fa_item_key,
364 			:ret,
365 			:err_str); end;';
366     execute immediate lv_plsql_blk
367      USING p_order_id,
368 	   p_line_item_id,
369 	   p_wi_instance_id,
370 	   p_fa_instance_id,
371 	   p_channel_name,
372 	   p_fe_name,
373 	   p_fa_item_type,
374 	   p_fa_item_key,
375 	   OUT p_return_code,
376 	   OUT p_error_description;
377    IF p_return_code <> 0 THEN
378        xdpcore.context( 'XDP_UTILITIES', 'CallFulfillmentProc', 'FE', p_fe_name, p_error_description );
379    END IF;
380 
381   EXCEPTION
382   WHEN OTHERS THEN
383     xdpcore.context( 'XDP_UTILITIES', 'CallFulfillmentProc', 'FE', p_fe_name, SQLERRM );
384     p_return_code := SQLCODE;
385     p_error_description := SQLERRM;
386   END CallFulfillmentProc;
387 
388 
389  -- Call any SFM workitem FA dynamic mapping procedure
390  --
391  --  the user defined FA dynamic mapping procedure should use
392  --  the following spec:
393  --  procedure <name of the proc>(
394  --         p_order_id IN NUMBER,
395  --         p_line_item_id IN NUMBER,
396  --         p_wi_instance_id IN NUMBER,
397  --		p_return_code OUT NOCOPY NUMBER,
398  --         p_error_description OUT NOCOPY VARCHAR2)
399  --
400 
401   PROCEDURE CallFAMapProc(
402 		p_procedure_name IN Varchar2,
403        	p_order_id IN NUMBER,
404        	p_line_item_id IN NUMBER,
405        	p_wi_instance_id IN NUMBER,
406        	p_return_code OUT NOCOPY NUMBER,
407        	p_error_description OUT NOCOPY VARCHAR2)
408   IS
409      lv_plsql_blk varchar2(32000);
410   BEGIN
411 
412     p_return_code := 0;
413     lv_plsql_blk := 'BEGIN  '||
414 		    p_procedure_name||
415 		    '( :order_id,
416 			:line_item_id,
417 			:wi_instance_id,
418 			:ret,
419 			:err_str); end;';
420     execute immediate lv_plsql_blk
421      USING p_order_id,
422 	   p_line_item_id,
423 	   p_wi_instance_id,
424 	   OUT p_return_code,
425 	   OUT p_error_description;
426 
427    IF p_return_code <> 0 THEN
428        xdpcore.context( 'XDP_UTILITIES', 'CallFAMapProc', 'WI', p_wi_instance_id, p_error_description );
429    END IF;
430   EXCEPTION
431   WHEN OTHERS THEN
432     xdpcore.context( 'XDP_UTILITIES', 'CallFAMapProc', 'WI', p_wi_instance_id, SQLERRM );
433     p_return_code := SQLCODE;
434     p_error_description := SQLERRM;
435   END CallFAMapProc;
436 
437  --
438  -- Call any SFM service action to workitem dynamic mapping procedure
439  --
440  --  the user defined WI dynamic mapping procedure should use
441  --  the following spec:
442  --  procedure <name of the proc>(
443  --         p_order_id IN NUMBER,
444  --         p_line_item_id IN NUMBER,
445  --		p_return_code OUT NOCOPY NUMBER,
446  --         p_error_description OUT NOCOPY VARCHAR2)
447  --
448 
449   PROCEDURE CallWIMapProc(
450 		p_procedure_name IN Varchar2,
451        		p_order_id IN NUMBER,
452        		p_line_item_id IN NUMBER,
453        		p_return_code OUT NOCOPY NUMBER,
454        		p_error_description OUT NOCOPY VARCHAR2)
455   IS
456      lv_plsql_blk varchar2(32000);
457   BEGIN
458 
459     p_return_code := 0;
460     lv_plsql_blk := 'BEGIN  '||
461 		    p_procedure_name||
462 		    '( :order_id,
463 			:line_item_id,
464 			:ret,
465 			:err_str); end;';
466     execute immediate lv_plsql_blk
467      USING p_order_id,
468 	   p_line_item_id,
469 	   OUT p_return_code,
470 	   OUT p_error_description;
471 
472    IF p_return_code <> 0 THEN
473        xdpcore.context( 'XDP_UTILITIES', 'CallWIMapProc', 'LINE', p_line_item_id, p_error_description );
474    END IF;
475   EXCEPTION
476   WHEN OTHERS THEN
477     xdpcore.context( 'XDP_UTILITIES', 'CallWIMapProc', 'LINE', p_line_item_id, SQLERRM );
478     p_return_code := SQLCODE;
479     p_error_description := SQLERRM;
480 
481   END CallWIMapProc;
482 
483 
484  --
485  -- Call any SFM workitem user workflow start up procedure
486  --
487  --  the user defined WI workflow startup procedure should use
488  --  the following spec:
489  --  procedure <name of the proc>(
490  --         p_order_id IN NUMBER,
491  --         p_line_item_id IN NUMBER,
492  --         p_wi_instance_id IN NUMBER,
493  --		p_wf_item_type OUT NOCOPY varchar2,
494  --		p_wf_item_key  OUT NOCOPY varchar2,
495  --		p_wf_process_name  OUT NOCOPY varchar2,
496  --		p_return_code OUT NOCOPY NUMBER,
497  --         p_error_description OUT NOCOPY VARCHAR2)
498  --
499 
500   PROCEDURE CallWIWorkflowProc(
501 		p_procedure_name IN Varchar2,
502        	p_order_id IN NUMBER,
503        	p_line_item_id IN NUMBER,
504        	p_wi_instance_id IN NUMBER,
505 		p_wf_item_type OUT NOCOPY varchar2,
506 		p_wf_item_key  OUT NOCOPY varchar2,
507 		p_wf_process_name  OUT NOCOPY varchar2,
508        	p_return_code OUT NOCOPY NUMBER,
509        	p_error_description OUT NOCOPY VARCHAR2)
510   IS
511      lv_plsql_blk varchar2(32000);
512   BEGIN
513 
514     p_return_code := 0;
515     lv_plsql_blk := 'BEGIN  '||
516 		    p_procedure_name||
517 		    '( :order_id,
518 			:line_item_id,
519 			:wi_instance_id,
520 			:wf_item_type,
521 			:wf_item_key,
522 			:wf_process_name,
523 			:ret,
524 			:err_str); end;';
525     execute immediate lv_plsql_blk
526      USING p_order_id,
527 	   p_line_item_id,
528 	   p_wi_instance_id,
529 	   OUT p_wf_item_type,
530 	   OUT p_wf_item_key,
531 	   OUT p_wf_process_name,
532 	   OUT p_return_code,
533 	   OUT p_error_description;
534    IF p_return_code <> 0 THEN
535        xdpcore.context( 'XDP_UTILITIES', 'CallWIWorkflowProc', 'WI', p_wi_instance_id, p_error_description );
536    END IF;
537 
538   EXCEPTION
539   WHEN OTHERS THEN
540     xdpcore.context( 'XDP_UTILITIES', 'CallWIWorkflowProc', 'WI', p_wi_instance_id, SQLERRM );
541     p_return_code := SQLCODE;
542     p_error_description := SQLERRM;
543   END CallWIWorkflowProc;
544 
545  --
546  --  Call any SFM DRC Task Result Procedure.
547  --  DRC Task Result Procedure is used to construct the
548  --  DRC task result string after SFM has perfromed a DRC
549  --  task.  The user will use this procedure to examine
550  --  all the workitems which had been executed by SFM for
551  --  the given DRC task and return the result string accordingly.
552  --  The DRC Task Result Procedure should use
553  --  the following spec:
554  --  procedure <name of the proc>(
555  --         p_sdp_order_id IN NUMBER,
556  --		p_task_result OUT NOCOPY varchar2,
557  --		p_reurn_code OUT NOCOPY NUMBER,
558  --         p_error_description OUT NOCOPY VARCHAR2)
559  --
560 
561   PROCEDURE CallDRCTaskResultProc(
562 		p_procedure_name IN Varchar2,
563        		p_sdp_order_id IN NUMBER,
564 		p_task_result OUT NOCOPY varchar2,
565        		p_return_code OUT NOCOPY NUMBER,
566        		p_error_description OUT NOCOPY VARCHAR2)
567   IS
568      lv_plsql_blk varchar2(32000);
569   BEGIN
570 
571     p_return_code := 0;
572     lv_plsql_blk := 'BEGIN  '||
573 		    p_procedure_name||
574 		    '( :sdp_order_id,
575 			:task_result,
576 			:ret,
577 			:err_str); end;';
578     execute immediate lv_plsql_blk
579      USING p_sdp_order_id,
580 	   OUT p_task_result,
581 	   OUT p_return_code,
582 	   OUT p_error_description;
583 
584   EXCEPTION
585   WHEN OTHERS THEN
586     p_return_code := SQLCODE;
587     p_error_description := SQLERRM;
588   END CallDRCTaskResultProc;
589 
590 
591 
592  --
593  --  This procedure will insert a new row into XDP_PROC_BODY table
594  --
595  PROCEDURE Create_New_Proc_Body(
596 	p_proc_name IN VARCHAR2,
597 	p_proc_type IN VARCHAR2 := 'CONNECT',
598 	p_proc_spec IN VARCHAR2,
599 	p_proc_body IN VARCHAR2,
600 	p_creation_date IN DATE,
601 	p_created_by IN NUMBER,
602 	p_last_update_date IN DATE,
603 	p_last_updated_by IN NUMBER,
604 	p_last_update_login IN NUMBER,
605 	return_code OUT NOCOPY NUMBER,
606 	error_description OUT NOCOPY VARCHAR2) IS
607   lv_lob clob;
608  BEGIN
609    return_code := 0;
610    insert into xdp_proc_body
611    (proc_name, proc_type, protected_flag, proc_spec, proc_body, creation_date, created_by, last_update_date,
612     last_updated_by, last_update_login)
613    values
614    (p_proc_name, p_proc_type, 'N', p_proc_spec, empty_clob(),
615     p_creation_date, p_created_by, p_last_update_date, p_last_updated_by,p_last_update_login)
616    returning proc_body into lv_lob;
617 
618   dbms_lob.write(lv_lob, length(p_proc_body), 1, p_proc_body);
619 
620   EXCEPTION
621   WHEN OTHERS THEN
622     return_code := SQLCODE;
623     error_description := SUBSTR(SQLERRM,1,280);
624  END Create_New_Proc_Body;
625 
626  --
627  --  This procedure will update the XDP_PROC_BODY table
628  --
629  PROCEDURE Update_Proc_Body(
630 		p_proc_name IN VARCHAR2,
631 		p_proc_type IN VARCHAR2 := 'CONNECT',
632 		p_proc_body IN VARCHAR2,
633 		p_last_update_date IN DATE,
634 		p_last_updated_by IN NUMBER,
635 		p_last_update_login IN NUMBER,
636 		return_code OUT NOCOPY NUMBER,
637 		error_description OUT NOCOPY VARCHAR2) IS
638 
639    lv_lob clob;
640    lv_length number := 32767;
641   BEGIN
642       return_code := 0;
643 
644       update xdp_proc_body
645       set
646 	  last_update_date = p_last_update_date,
647 	  last_updated_by = p_last_updated_by,
648 	  last_update_login = p_last_update_login,
649 	  proc_body = empty_clob()
650       where
651  	    proc_type = p_proc_type
652 	and proc_name = p_proc_name
653       returning proc_body into lv_lob;
654 
655   DBMS_LOB.WRITE(lv_lob,length(p_proc_body),1,p_proc_body);
656   commit;
657 
658 
659   EXCEPTION
660   WHEN OTHERS THEN
661     return_code := SQLCODE;
662     error_description := SUBSTR(SQLERRM,1,280);
663   END Update_Proc_Body;
664 
665 
666  --
667  -- A function to convert clob to varchar2
668  --
669  FUNCTION Get_CLOB_Value(p_proc_name in varchar2)
670    RETURN VARCHAR2 IS
671   lv_str varchar2(32700);
672   lv_length number := 32600;
673   lv_lob clob;
674 
675   CURSOR l_get_clob_value_csr IS
676     select proc_body
677     from xdp_proc_body
678     where proc_name = p_proc_name;
679 
680  BEGIN
681 
682 /***
683     --skilaru 03/27/2001
684     --modified to use CURSOR
685     select proc_body into lv_lob
686     from xdp_proc_body
687     where proc_name = p_proc_name;
688     dbms_lob.read(lv_lob,lv_length,1,lv_str);
689 ***/
690     OPEN l_get_clob_value_csr;
691     FETCH l_get_clob_value_csr INTO lv_lob;
692 
693     IF l_get_clob_value_csr%FOUND THEN
694       dbms_lob.read(lv_lob,lv_length,1,lv_str);
695     END IF;
696 
697     CLOSE l_get_clob_value_csr;
698     RETURN lv_str;
699 
700  END Get_CLOB_Value;
701 
702 --
703 -- a procedure to execute any Query which returns a list of IDs
704 --
705 
706 PROCEDURE Execute_GetID_QUERY(
707 				p_query_block IN VARCHAR2,
708           			p_id_list OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
709 				return_code OUT NOCOPY NUMBER,
710 				error_description OUT NOCOPY VARCHAR2)
711 IS
712   TYPE v_cursorType IS REF CURSOR;
713   v_cursor v_cursorType;
714   v_numRows number := 0;
715   lv_tmp_id number;
716 BEGIN
717 
718   return_code := 0;
719   IF v_cursor%ISOPEN THEN
720   	CLOSE v_cursor;
721   END IF;
722   OPEN v_cursor FOR p_query_block;
723   LOOP
724    FETCH v_cursor INTO lv_tmp_id;
725    EXIT WHEN v_cursor%NOTFOUND OR v_cursor%NOTFOUND IS NULL;
726    v_numRows := v_numRows + 1;
727    p_id_list(v_numRows) := lv_tmp_id;
728   END LOOP;
729 
730   CLOSE v_cursor;
731 
732 EXCEPTION
733 WHEN OTHERS THEN
734     return_code := SQLCODE;
735     error_description := SUBSTR(SQLERRM,1,280);
736     CLOSE v_cursor;
737 END Execute_GetID_QUERY;
738 
739   -- Local function to convert parameter modes to strings.
740   FUNCTION ConvertMode(p_Code IN NUMBER)
741     RETURN VARCHAR2 IS
742     v_Output VARCHAR2(10);
743   BEGIN
744 
745 --
746 -- Changed DECODE statement to IF statements to improve performance.
747 -- skilaru 03/14/2001
748 --
749 /*
750     SELECT DECODE(p_Code, 0, 'IN',
751                           1, 'IN OUT',
752                           2, 'OUT')
753       INTO v_Output
754       FROM dual;
755 */
756     IF p_Code = 0 THEN
757         v_Output :=  'IN';
758     ELSIF p_Code = 1 THEN
759         v_Output :=  'IN OUT';
760     ELSIF p_Code = 2 THEN
761         v_Output :=  'OUT';
762     END IF;
763 
764     RETURN v_Output;
765   END ConvertMode;
766 
767 
768   --
769   -- Describe the procedure
770   PROCEDURE DescribeProc(p_ProcName IN VARCHAR2,
771                          p_Print IN BOOLEAN) IS
772     v_ArgCounter NUMBER := 1;
773   BEGIN
774     -- First call DESCRIBE_PROCEDURE to populate the internal variables
775 	-- about the procedure.
776     DBMS_DESCRIBE.DESCRIBE_PROCEDURE(
777       p_ProcName,
778       null,
779       null,
780       v_Overload,
781       v_Position,
782       v_Level,
783       v_ArgumentName,
784       v_Datatype,
785       v_DefaultValue,
786       v_InOut,
787       v_Length,
788       v_Precision,
789       v_Scale,
790       v_Radix,
791       v_Spare);
792 
793     IF NOT p_Print THEN
794       RETURN;
795     END IF;
796 
797   END DescribeProc;
798 
799   PROCEDURE RunProc(p_NumParams IN NUMBER,
800                     p_ProcName IN VARCHAR2,
801                     p_Parameters IN OUT NOCOPY t_ParameterList) IS
802 
803     -- DBMS_SQL variables
804     v_Cursor  NUMBER;
805     v_NumRows NUMBER;
806     lv_InOut        DBMS_DESCRIBE.NUMBER_TABLE;
807 
808     v_ProcCall VARCHAR2(32600);
809     v_FirstParam BOOLEAN := TRUE;
810   BEGIN
811 
812     -- First describe the procedure.
813     DescribeProc(p_ProcName, FALSE);
814     lv_InOut := v_InOut;
815 
816     -- Now we need to create the procedure call string.  This consists of
817 	-- 'BEGIN <procedure_name>(:p1, :p2, ...); END;'
818     v_ProcCall := 'BEGIN ' || p_ProcName || '(';
819 
820     FOR v_Counter IN 1..p_NumParams LOOP
821       IF v_FirstParam THEN
822         v_ProcCall := v_ProcCall || ':' || v_ArgumentName(v_Counter);
823         v_FirstParam := FALSE;
824       ELSE
825         v_ProcCall := v_ProcCall || ', :' || v_ArgumentName(v_Counter);
826       END IF;
827     END LOOP;
828 
829     v_ProcCall := v_ProcCall || '); END;';
830 
831     -- Open the cursor and parse the statement.
832     v_Cursor := DBMS_SQL.OPEN_CURSOR;
833     DBMS_SQL.PARSE(v_Cursor, v_ProcCall, DBMS_SQL.V7);
834 
835     -- Bind the procedure parameters.
836     FOR v_Counter IN 1..p_NumParams LOOP
837 
838       -- First set the parameter name.
839       p_Parameters(v_Counter).name := v_ArgumentName(v_Counter);
840 
841       -- Bind based on the parameter type.
842       IF p_Parameters(v_Counter).actual_type = 'NUMBER' THEN
843         DBMS_SQL.BIND_VARIABLE(v_Cursor, p_Parameters(v_Counter).name,
844                                p_Parameters(v_Counter).num_param);
845       ELSIF p_Parameters(v_Counter).actual_type = 'VARCHAR2' THEN
846         DBMS_SQL.BIND_VARIABLE(v_Cursor, p_Parameters(v_Counter).name,
847                                p_Parameters(v_Counter).vchar_param, 32767);
848       ELSIF p_Parameters(v_Counter).actual_type = 'DATE' THEN
849         DBMS_SQL.BIND_VARIABLE(v_Cursor, p_Parameters(v_Counter).name,
850                                p_Parameters(v_Counter).date_param);
851       ELSIF p_Parameters(v_Counter).actual_type = 'CHAR' THEN
852         DBMS_SQL.BIND_VARIABLE_CHAR(v_Cursor, p_Parameters(v_Counter).name,
853                                p_Parameters(v_Counter).char_param, 500);
854       ELSE
855         RAISE_APPLICATION_ERROR(-20501, 'Dynamic PL/SQL error: Invalid parameter type');
856       END IF;
857     END LOOP;
858 
859     -- Execute the procedure.
860     v_NumRows := DBMS_SQL.EXECUTE(v_Cursor);
861 
862     -- Call VARIABLE_VALUE for any OUT or IN OUT parameters.
863     FOR v_Counter IN 1..p_NumParams LOOP
864       IF lv_InOut(v_Counter) = 1 OR lv_InOut(v_Counter) = 2 THEN
865         IF p_Parameters(v_Counter).actual_type = 'NUMBER' THEN
866           DBMS_SQL.VARIABLE_VALUE(v_Cursor, ':' || p_Parameters(v_Counter).name,
867                                 p_Parameters(v_Counter).num_param);
868         ELSIF p_Parameters(v_Counter).actual_type = 'VARCHAR2' THEN
869           DBMS_SQL.VARIABLE_VALUE(v_Cursor, ':' || p_Parameters(v_Counter).name,
870                                 p_Parameters(v_Counter).vchar_param);
871         ELSIF p_Parameters(v_Counter).actual_type = 'DATE' THEN
872           DBMS_SQL.VARIABLE_VALUE(v_Cursor, ':' || p_Parameters(v_Counter).name,
873                                 p_Parameters(v_Counter).date_param);
874         ELSIF p_Parameters(v_Counter).actual_type = 'CHAR' THEN
875           DBMS_SQL.VARIABLE_VALUE_CHAR(v_Cursor, ':' || p_Parameters(v_Counter).name,
876                                 p_Parameters(v_Counter).char_param);
877         ELSE
878           RAISE_APPLICATION_ERROR(-20501, 'Dynamic PL/SQL error: Invalid parameter type');
879         END IF;
880       END IF;
881     END LOOP;
882     DBMS_SQL.CLOSE_CURSOR(v_Cursor);
883 
884   EXCEPTION
885   WHEN OTHERS THEN
886       DBMS_SQL.CLOSE_CURSOR(v_Cursor);
887       raise;
888   END RunProc;
889 
890   PROCEDURE Printparams(p_Parameters IN t_ParameterList,
891                         p_NumParams IN NUMBER) IS
892   BEGIN
893 	null;
894   END PrintParams;
895 
896  -- get line item parameter values which is used by order analyzer
897   FUNCTION OA_GetLineParam(
898 		p_line_item_id IN Number,
899 		p_line_param_name IN Varchar2)
900 	return Varchar2
901   IS
902     lv_param_value varchar2(4000);
903 
904     CURSOR l_oa_getlineparam_csr IS
905       select parameter_value
906       from XDP_ORDER_LINEITEM_DETS
907       where
908 	 line_item_id = p_line_item_id AND
909 	 line_parameter_name = p_line_param_name;
910   BEGIN
911 
912 /***
913     --skilaru 03/28/2001
914     --modified to use CURSOR
915     select parameter_value into lv_param_value
916     from XDP_ORDER_LINEITEM_DETS
917     where
918 	 line_item_id = p_line_item_id AND
919 	 line_parameter_name = p_line_param_name;
920 ***/
921 
922     OPEN l_oa_getlineparam_csr;
923     FETCH l_oa_getlineparam_csr INTO lv_param_value;
924 
925     IF l_oa_getlineparam_csr%NOTFOUND THEN
926       RAISE NO_DATA_FOUND;
927     END IF;
928 
929     CLOSE l_oa_getlineparam_csr;
930 
931     return lv_param_value;
932 
933   EXCEPTION
934   WHEN NO_DATA_FOUND THEN
935     lv_param_value := 'SDP_NO_DATA_FOUND';
936 
937     IF l_oa_getlineparam_csr%ISOPEN THEN
938       CLOSE l_oa_getlineparam_csr;
939     END IF;
940 
941     return lv_param_value;
942   WHEN OTHERS THEN
943     raise;
944   END OA_GetLineParam;
945 
946  -- get Workitem parameter values which is used by order analyzer
947   FUNCTION OA_GetWIParam(
948 		p_wi_instance_id IN Number,
949 		p_wi_param_name IN Varchar2)
950 	return Varchar2
951  IS
952     lv_param_value varchar2(4000);
953 
954 
955     CURSOR l_oa_getwiparam_csr IS
956       select parameter_value
957       from
958 	xdp_worklist_details wdl
959       where
960 	 wdl.workitem_instance_id = p_wi_instance_id AND
961 	 wdl.parameter_name = p_wi_param_name;
962   BEGIN
963 /***
964     --skilaru 03/28/2001
965     --modified to use CURSOR
966     select parameter_value into lv_param_value
967     from
968 	xdp_worklist_details wdl,
969 	xdp_parameter_pool ppl
970     where
971 	 wdl.workitem_instance_id = p_wi_instance_id AND
972 	 wdl.wi_parameter_id = ppl.parameter_id AND
973 	 ppl.parameter_name = p_wi_param_name;
974 ***/
975     OPEN l_oa_getwiparam_csr;
976     FETCH l_oa_getwiparam_csr INTO lv_param_value;
977 
978     IF l_oa_getwiparam_csr%NOTFOUND THEN
979       RAISE NO_DATA_FOUND;
980     END IF;
981 
982     CLOSE l_oa_getwiparam_csr;
983 
984     return lv_param_value;
985 
986   EXCEPTION
987   WHEN NO_DATA_FOUND THEN
988     lv_param_value := 'SDP_NO_DATA_FOUND';
989 
990     IF l_oa_getwiparam_csr%ISOPEN THEN
991       CLOSE l_oa_getwiparam_csr;
992     END IF;
993 
994     return lv_param_value;
995   WHEN OTHERS THEN
996     raise;
997 
998  END OA_GetWIParam;
999 
1000  -- Check if the order line is a workitem
1001  FUNCTION OA_Get_LINE_WI_FLAG(
1002 		p_line_item_id IN Number)
1003   return Varchar2
1004  IS
1005 	--lv_flag varchar2(1);
1006 	lv_flag varchar2(1) := 'N';
1007         lv_wi_id NUMBER;
1008 
1009         CURSOR l_oa_get_line_wi_flag_csr IS
1010 	select workitem_id
1011 	from xdp_order_line_items
1012 	where
1013 		line_item_id = p_line_item_id;
1014  BEGIN
1015 /***
1016     --skilaru 03/28/2001
1017     --modified to use CURSOR
1018 
1019 	select decode(workitem_id,NULL,'N','Y')
1020 	into lv_flag
1021 	from xdp_order_line_items
1022 	where
1023 		line_item_id = p_line_item_id;
1024 ***/
1025       OPEN l_oa_get_line_wi_flag_csr;
1026       FETCH l_oa_get_line_wi_flag_csr INTO lv_wi_id;
1027 
1028       IF lv_wi_id IS NOT NULL THEN
1029         lv_flag := 'Y';
1030       ELSE
1031         RAISE NO_DATA_FOUND;
1032       END IF;
1033 
1034       CLOSE l_oa_get_line_wi_flag_csr;
1035       return lv_flag;
1036 
1037  EXCEPTION
1038  WHEN NO_DATA_FOUND THEN
1039         --skilaru 03/31/2001
1040         lv_flag := NULL;
1041 
1042         IF l_oa_get_line_wi_flag_csr%ISOPEN THEN
1043           CLOSE l_oa_get_line_wi_flag_csr;
1044         END IF;
1045 
1046       return lv_flag;
1047  END OA_Get_LINE_WI_FLAG;
1048 
1049  -- get workitem name which is used by order analyzer
1050  FUNCTION OA_GetWIName(
1051 		p_wi_instance_id IN Number)
1052   return Varchar2
1053  IS
1054     lv_wi  varchar2(80);
1055     CURSOR l_oa_getwiname_csr IS
1056         select workitem_name
1057 	from xdp_workitems wim,
1058 	     XDP_FULFILL_WORKLIST fwt
1059 	where
1060 		fwt.workitem_instance_id = p_wi_instance_id and
1061 		fwt.workitem_id = wim.workitem_id;
1062  BEGIN
1063 
1064 /***
1065         --skilaru 03/31/2001
1066         --modified to use CURSOR
1067 
1068 	select workitem_name
1069 	into lv_wi
1070 	from xdp_workitems wim,
1071 	     XDP_FULFILL_WORKLIST fwt
1072 	where
1073 		fwt.workitem_instance_id = p_wi_instance_id and
1074 		fwt.workitem_id = wim.workitem_id;
1075 ***/
1076       OPEN l_oa_getwiname_csr;
1077       FETCH l_oa_getwiname_csr INTO lv_wi;
1078 
1079       IF l_oa_getwiname_csr%NOTFOUND THEN
1080         RAISE NO_DATA_FOUND;
1081       END IF;
1082 
1083       CLOSE l_oa_getwiname_csr;
1084       return lv_wi;
1085  EXCEPTION
1086  WHEN NO_DATA_FOUND THEN
1087 	lv_wi := NULL;
1088 
1089         IF l_oa_getwiname_csr%ISOPEN THEN
1090           CLOSE l_oa_getwiname_csr;
1091         END IF;
1092 
1093       return lv_wi;
1094 
1095  END OA_GetWIName;
1096 
1097 
1098 -- These set of routines are used by the Name/Value parser provided by SFM
1099 
1100  procedure Parse_String( buffer in varchar2,
1101                          NameValueList IN OUT NOCOPY XDP_TYPES.NAME_VALUE_LIST,
1102                          assign_str in varchar2 := '=',
1103                          p_term_str in varchar2 := NULL)
1104 
1105  IS
1106 	assign_str_pos		integer := 0;
1107 	assign_str_len		integer := 0;
1108 	line_count		integer := 0;
1109 	buffer_len		integer := 0;
1110 	done			boolean := FALSE;
1111 	line_buffer		varchar2(32767);
1112 	key_name		varchar2(32767);
1113 	key_value		varchar2(32767);
1114 	term_str		varchar2(6);
1115 	proc_tag varchar2(30) := 'PARSE_RESPONSE-';
1116 	line_list		string_list_t;
1117 	c			integer := 1;
1118 BEGIN
1119 	if buffer is null then
1120 		return;
1121 	end if;
1122 	if p_term_str is null then
1123 		term_str := FND_GLOBAL.LOCAL_CHR(10);
1124 	else
1125 		term_str := p_term_str;
1126 	end if;
1127 
1128   	buffer_len := LENGTH(buffer);
1129 	if buffer_len = 0 then
1130 		return;
1131 	end if;
1132 	split_lines(buffer,line_list,term_str);
1133 	line_count := 1;
1134 	for c in line_list.first .. line_list.last loop
1135 		key_name  := get_key_name(line_list(c),assign_str);
1136 		key_value := get_key_value(line_list(c),assign_str);
1137 		if key_name is not null then
1138 			NameValueList(line_count).NAME := key_name;
1139 			if key_value is not null then
1140 				NameValueList(line_count).VALUE := key_value;
1141 			end if;
1142 			line_count := line_count + 1;
1143 		end if;
1144 	  end loop;
1145 EXCEPTION
1146 	WHEN OTHERS THEN RAISE;
1147 end Parse_String;
1148 
1149 function get_key_name(buffer in varchar2,assign_str in varchar2 := '=' ) return varchar2
1150 is
1151  ret_val varchar2(32767);
1152  assign_str_pos integer := 0;
1153  assign_str_len integer := 0;
1154  minimum_start_pos integer := 0;
1155  buffer_len integer := 0;
1156  key_start_pos integer := 1;
1157  characters_to_read integer := 0;
1158  minimum_assign_pos integer := 2;
1159  proc_tag varchar2(30) := 'GET_KEY_NAME-';
1160 begin
1161 	buffer_len := length(buffer);
1162 	if buffer_len = 0 or buffer_len is null then
1163 		return null;
1164 	end if;
1165 	assign_str_len := length(assign_str);
1166 	assign_str_pos := instr(buffer,assign_str);
1167 	if assign_str_pos < minimum_assign_pos or assign_str_pos is null then
1168 		ret_val := null;
1169 	else
1170 		characters_to_read := assign_str_pos - key_start_pos ;
1171 		ret_val := substr(buffer, key_start_pos, characters_to_read);
1172 	end if;
1173 	return ret_val;
1174 EXCEPTION
1175 	WHEN OTHERS THEN
1176 		RAISE;
1177 end get_key_name;
1178 
1179 function get_key_value(buffer in varchar2,assign_str in varchar2 := '=' ) return varchar2
1180 is
1181 	ret_val			varchar2(32767);
1182 	assign_str_pos		integer := 0;
1183 	buffer_len		integer := 0;
1184 	assign_str_len		integer := 0;
1185 	value_start_pos		integer := 0;
1186 	characters_to_read	integer := 0;
1187 	proc_tag varchar2(30) := 'GET_KEY_VALUE-';
1188 begin
1189 	buffer_len		:= length(buffer);
1190 	if buffer_len = 0 or buffer_len is null then
1191 		return null;
1192 	end if;
1193 	assign_str_pos		:= instr(buffer,assign_str);
1194 	assign_str_len		:= length(assign_str);
1195 	value_start_pos		:= assign_str_pos + assign_str_len;
1196 	characters_to_read	:= buffer_len - assign_str_pos;
1197 	if assign_str_pos = 0 then
1198 		return null;
1199 	end if;
1200 	IF value_start_pos > buffer_len then
1201 		ret_val := null;
1202 	else
1203 		ret_val := substr(buffer, value_start_pos, characters_to_read );
1204 	end if;
1205 	return ret_val;
1206 EXCEPTION
1207  WHEN OTHERS THEN RAISE;
1208 end get_key_value;
1209 
1210 procedure split_lines(buffer in varchar2,string_list in OUT NOCOPY string_list_t ,split_str in varchar2)
1211 is
1212  c integer := 1;
1213  start_pos integer := 1;
1214  buffer_len integer := 0;
1215  end_pos integer := 1;
1216  done boolean := false;
1217  proc_tag varchar2(30) := 'SPLIT_LINES-';
1218  line_end_pos integer := 0;
1219 begin
1220         if buffer is null then
1221                 return;
1222         end if;
1223         buffer_len := length(buffer);
1224         if buffer_len = 0 then
1225                 return;
1226         end if;
1227 	end_pos := instr(buffer,split_str,start_pos);
1228 	if end_pos = 0 then
1229 		string_list(1) := buffer;
1230 		done := true;
1231 	end if;
1232 	start_pos := 1;
1233         while (start_pos < buffer_len) and (end_pos > 0 )  loop
1234                 end_pos := instr(buffer,split_str,start_pos,1);
1235 		line_end_pos := end_pos - start_pos ;
1236                 if end_pos = 0 then
1237                 	string_list(c) := substr(buffer,start_pos);
1238 		else
1239 			string_list(c) := substr(buffer,start_pos,line_end_pos );
1240 		end if;
1241                 start_pos := end_pos + 1;
1242 		c := c + 1;
1243         end loop;
1244 end split_lines;
1245 
1246 -- End of SFM Name/Value parser
1247 
1248 
1249 FUNCTION ISVALIDNAME( p_varname IN VARCHAR2 ) RETURN VARCHAR2 IS
1250 
1251 BEGIN
1252    IF( IS_VALID_NAME( p_varname ) ) THEN
1253     RETURN 'TRUE';
1254    ELSE
1255     RETURN 'FALSE';
1256    END IF;
1257 END ISVALIDNAME;
1258 
1259 /*
1260 	vrachur : 07/15/1999 - Added funtion IS_VALID_NAME
1261 
1262 	Function: IS_VALID_NAME( VARCHAR2 )
1263 	Input	: Variable Name
1264 
1265 	Purpose	: Checks if a name confirms to PL/SQL naming convention. This
1266 		  does not check for reserved words.
1267 
1268 	Returns : TRUE  - Valid Name
1269 		  FALSE - Invalid Name
1270 */
1271 FUNCTION IS_VALID_NAME( p_varname IN VARCHAR2 ) RETURN BOOLEAN IS
1272 	l_cur_char	CHAR(1) ;
1273 	l_var_length	NUMBER ;
1274 	l_char_ascii	NUMBER ;
1275 
1276 BEGIN
1277 	-- Return FALSE if nothing is passed.
1278 	l_var_length := LENGTH( p_varname ) ;
1279 
1280 	IF ( p_varname IS NULL ) OR ( l_var_length <= 0 ) THEN
1281 		RETURN FALSE ;
1282 
1283 	END IF ;
1284 
1285 	-- Check Char by Char. Make sure it adheres to PL/SQL naming Standards.
1286 	FOR i IN 1..l_var_length
1287 	LOOP
1288 		l_cur_char   := SUBSTR( p_varname, i, 1 ) ;
1289 		l_char_ascii := ASCII( l_cur_char ) ;
1290 
1291 		-- First Character should always be A-Z or a-z
1292 		IF ( i = 1 ) THEN
1293 			-- First Character
1294 			IF ( l_char_ascii NOT BETWEEN 65 AND 90 ) AND
1295 				( l_char_ascii NOT BETWEEN 97 AND 122 ) THEN
1296 				RETURN FALSE ;
1297 			END IF ;
1298 		ELSE
1299 			-- Rest can be A-Z, a-z, 0-9, $, #, _
1300 			IF ( l_char_ascii NOT BETWEEN 65 AND 90 ) AND
1301 				( l_char_ascii NOT BETWEEN 97 AND 122 ) AND
1302 				( l_char_ascii NOT BETWEEN 48 AND 57 ) AND
1303 				( l_cur_char NOT IN ( '_', '$', '#' ) ) THEN
1304 
1305 				RETURN FALSE ;
1306 			END IF ;
1307 		END IF ;
1308 	END LOOP ;
1309 
1310 	RETURN TRUE ;
1311 
1312 EXCEPTION
1313 	WHEN OTHERS THEN
1314 		RETURN FALSE ;
1315 END	IS_VALID_NAME ;
1316 
1317 --
1318 --  This is the function which will mimic the workflow function
1319 --  WF_STANDARD.WaitForFlow and handling the workflow concurrency
1320 --  problem in WaitForFlow.  The caller should pass its current item type,
1321 --  item key, and activity name to the API
1322 --
1323 Procedure WaitForFlow(
1324 	p_item_type varchar2,
1325       p_item_key  varchar2,
1326       p_activity_name varchar2)
1327 IS
1328   lv_child_count number := 0;
1329   lv_id number;
1330   cursor lc_child IS
1331    select item_type, item_key
1332    from wf_items_v wi
1333    where
1334 	wi.parent_item_type = p_item_type and
1335       wi.parent_item_key = p_item_key ;
1336   lv_attr_defined varchar2(1) := 'N';
1337   lv_previous_count number;
1338   lv_error_wf varchar2(1);
1339 BEGIN
1340 
1341    begin
1342      select 'Y' into lv_attr_defined
1343      from dual
1344      where exists(
1345          select 1
1346         from WF_ITEM_ATTRIBUTE_values WIA
1347         where WIA.ITEM_TYPE = p_item_type
1348 	  and WIA.ITEM_KEY = p_item_key
1349         and WIA.NAME = 'XDP_NUMBER_OF_CHILDREN');
1350       exception
1351       when no_data_found then
1352         wf_engine.AddItemAttr(
1353 		p_item_type,
1354 		p_item_key,
1355             'XDP_NUMBER_OF_CHILDREN');
1356     	  wf_engine.SetItemAttrNumber(
1357 		p_item_type,
1358 		p_item_key,
1359             'XDP_NUMBER_OF_CHILDREN',
1360 		0);
1361 
1362     end;
1363 
1364   lv_previous_count := wf_engine.GetItemAttrNumber(
1365 		p_item_type,
1366 		p_item_key,
1367             'XDP_NUMBER_OF_CHILDREN');
1368 
1369   select XDP_WF_COORDINATION_ID_S.NextVal
1370   into lv_id from dual;
1371 
1372   For lv_child_rec in lc_child loop
1373     begin
1374      select 'N' into lv_error_wf
1375      from dual
1376      where not exists(
1377          select 1
1378         from WF_ITEM_ATTRIBUTE_values WIA
1379         where WIA.ITEM_TYPE = lv_child_rec.item_type
1380 	  and WIA.ITEM_KEY = lv_child_rec.item_key
1381         and WIA.NAME = 'ERROR_ITEM_KEY'
1382         and WIA.TEXT_VALUE = p_item_key);
1383       exception
1384       when no_data_found then
1385         lv_error_wf := 'Y';
1386     end;
1387     IF lv_error_wf = 'N' THEN
1388       lv_child_count := lv_child_count + 1;
1389       begin
1390        select 'Y' into lv_attr_defined
1391        from dual
1392        where exists(
1393          select 1
1394         from WF_ITEM_ATTRIBUTE_values WIA
1395         where WIA.ITEM_TYPE = lv_child_rec.item_type
1396 	  and WIA.ITEM_KEY = lv_child_rec.item_key
1397         and WIA.NAME = 'XDP_WF_COORDINATION_ID');
1398        exception
1399         when no_data_found then
1400           wf_engine.AddItemAttr(
1401 		lv_child_rec.item_type,
1402 		lv_child_rec.item_key,
1403             'XDP_WF_COORDINATION_ID');
1404 	    wf_engine.SetItemAttrNumber(
1405 		lv_child_rec.item_type,
1406 		lv_child_rec.item_key,
1407             'XDP_WF_COORDINATION_ID',
1408 		lv_id);
1409       end;
1410     END IF;
1411   end loop;
1412 
1413   if lv_child_count = 0 then
1414     raise_application_error(-20111,
1415          'Call to WaitForFlow failed.  There is no child process');
1416   end if;
1417 
1418   insert into XDP_WF_PROCESS_COORD
1419   (wf_coordination_id,
1420    wf_item_type,
1421    wf_item_key,
1422    wf_activity_name,
1423    child_process_num,
1424    created_by,
1425    creation_date,
1426    last_updated_by,
1427    last_update_date,
1428    last_update_login
1429   )
1430   values
1431   (
1432     lv_id,
1433     p_item_type,
1434     p_item_key,
1435     p_activity_name,
1436     lv_child_count - lv_previous_count,
1437 	FND_GLOBAL.USER_ID,
1438 	sysdate,
1439 	FND_GLOBAL.USER_ID,
1440 	sysdate,
1441 	FND_GLOBAL.LOGIN_ID
1442   );
1443 
1444  wf_engine.SetItemAttrNumber(
1445 	p_item_type,
1446 	p_item_key,
1447       'XDP_NUMBER_OF_CHILDREN',
1448 	lv_child_count);
1449 
1450 END WaitForFlow;
1451 
1452 --
1453 --  This is the function which will mimic the workflow function
1454 --  WF_STANDARD.ContinueFlow and handling the workflow concurrency
1455 --  problem in ContinueFlow.  The caller should pass its current item type
1456 --  and item key to the API
1457 --
1458 Procedure ContinueFlow(
1459 	p_item_type varchar2,
1460       p_item_key  varchar2)
1461 IS
1462   lv_id number;
1463   lv_child_count number;
1464   lv_type varchar2(8);
1465   lv_key varchar2(240);
1466   lv_act varchar2(240);
1467 BEGIN
1468   lv_id := wf_engine.GetItemAttrNumber(
1469 		p_item_type,
1470 		p_item_key,
1471             'XDP_WF_COORDINATION_ID');
1472   Select child_process_num,wf_item_type,wf_item_key,wf_activity_name
1473   into lv_child_count,lv_type,lv_key,lv_act
1474   from XDP_WF_PROCESS_COORD
1475   where wf_coordination_id = lv_id for update;
1476 
1477   lv_child_count := lv_child_count - 1;
1478 
1479   IF lv_child_count > 0 THEN
1480 	update XDP_WF_PROCESS_COORD
1481       set child_process_num = lv_child_count
1482       where wf_coordination_id = lv_id ;
1483   ELSE
1484 	delete from XDP_WF_PROCESS_COORD
1485       where wf_coordination_id = lv_id ;
1486       WF_ENGINE.CompleteActivity(
1487         lv_type,
1488         lv_key,
1489 	  lv_act,
1490         wf_engine.eng_null);
1491   END IF;
1492 
1493 END ContinueFlow;
1494 
1495 --
1496 -- A procedure to create a package spec dynamically
1497 --  application_short_name should be either XDP or XNP
1498 --
1499 PROCEDURE Create_PKG_Spec(
1500 			p_pkg_name IN VARCHAR2,
1501 			p_pkg_spec IN VARCHAR2,
1502 			p_application_short_name IN VARCHAR2,
1503 			x_return_code OUT NOCOPY NUMBER,
1504 			x_error_string OUT NOCOPY VARCHAR2)
1505 IS
1506 
1507     lv1 varchar2(80);
1508     lv2 varchar2(80);
1509     lv_schema varchar2(80);
1510     lv_ret BOOLEAN;
1511 	lv_loc1 number := 1;
1512 	lv_loc2 number;
1513 	lv_len number := LENGTH(p_pkg_spec);
1514 	lv_row number := 0;
1515 	lv_status varchar2(40);
1516 	lv_owner varchar2(80);
1517 	lf_owner varchar2(80);
1518 	lv_tmp varchar2(32767);
1519 	lv_tmp_len number;
1520 	lv_lf varchar2(10);
1521 	lv_pkg_name varchar2(80) := UPPER(p_pkg_name);
1522 	CURSOR lc_status IS
1523 	  select status, owner
1524 	  from all_objects
1525 	  where object_name = lv_pkg_name and
1526 		   object_type = 'PACKAGE' and
1527                    owner = lf_owner and
1528 		   status <> 'VALID';
1529 	CURSOR lc_err(l_owner IN VARCHAR2) IS
1530 	 select text from all_errors
1531 	 where
1532 	   owner = l_owner and
1533 	   name = lv_pkg_name
1534 	   order by line;
1535 
1536 BEGIN
1537         select user into lf_owner from dual;
1538 
1539 	x_return_code := 0;
1540   	lv_ret := FND_INSTALLATION.get_app_info(
1541        'FND',
1542 		lv1,
1543 		lv2,
1544 		lv_schema);
1545 	lv_lf := '
1546 ';
1547     LOOP
1548 		if lv_loc1 > lv_len then
1549 			exit;
1550 		end if;
1551 
1552 		lv_loc2 := INSTR(p_pkg_spec,lv_lf,lv_loc1,1);
1553 		if lv_loc2 = 0 then
1554 			lv_tmp_len := lv_len - lv_loc1 + 1;
1555 			lv_tmp := SUBSTR(p_pkg_spec,lv_loc1,lv_tmp_len);
1556 			if lv_tmp_len <= 255 then
1557 				lv_row := lv_row + 1;
1558   				ad_ddl.build_package(
1559 					lv_tmp,
1560 					lv_row
1561 					);
1562 				exit;
1563 			else
1564 	  			x_return_code := -20111;
1565 	  			x_error_string :=
1566 					'Error:The following line exceeds 255 character.  '||
1567 					'Please insert a character return to break up the line.'||
1568 					SUBSTR(lv_tmp,1,257);
1569 				return;
1570 			end if;
1571 		else
1572 			lv_tmp_len := lv_loc2 - lv_loc1;
1573 			lv_tmp := SUBSTR(p_pkg_spec,lv_loc1,lv_tmp_len);
1574 			if lv_tmp_len <= 255 then
1575 				lv_row := lv_row + 1;
1576   				ad_ddl.build_package(
1577 					lv_tmp,
1578 					lv_row
1579 					);
1580 				lv_loc1 := lv_loc1 + lv_tmp_len + 1;
1581 			else
1582 	  			x_return_code := -20111;
1583 	  			x_error_string :=
1584 					'Error:The following line exceeds 255 character.  '||
1585 					'Please insert a character return to break up the line.'||
1586 					SUBSTR(lv_tmp,1,257);
1587 				return;
1588 			end if;
1589 		end if;
1590 	END LOOP;
1591 
1592 	IF lv_row = 0 THEN
1593 	  x_return_code := -20111;
1594 	  x_error_string := 'Error:The package spec can not be empty.';
1595 	  return;
1596 	END IF;
1597 
1598 	AD_DDL.CREATE_PACKAGE(
1599 		lv_schema  ,
1600 		p_application_short_name  ,
1601 		p_pkg_name,
1602 		'FALSE',
1603 		1,
1604 		lv_row
1605 		);
1606 
1607 	lv_status := 'VALID';
1608 	FOR lv_status_rec IN lc_status LOOP
1609       lv_status := lv_status_rec.status;
1610 	  lv_owner := lv_status_rec.owner;
1611 	  exit;
1612 	END LOOP;
1613     IF lv_status <> 'VALID' THEN
1614 	   x_return_code := -24344;
1615 	   for lv_err_rec in lc_err(lv_owner) loop
1616 		  x_error_string := x_error_string ||lv_err_rec.text||' ';
1617 	   end loop;
1618 	END IF;
1619 
1620 
1621 EXCEPTION
1622 	WHEN OTHERS THEN
1623 	x_return_code := SQLCODE;
1624 	x_error_string := SQLERRM;
1625 END Create_PKG_Spec;
1626 
1627 -- A procedure to create or replace a package body dynamically
1628 PROCEDURE Create_PKG_Body(
1629 			p_pkg_name IN VARCHAR2,
1630 			p_pkg_body IN VARCHAR2,
1631 			p_application_short_name IN VARCHAR2,
1632 			x_return_code OUT NOCOPY NUMBER,
1633 			x_error_string OUT NOCOPY VARCHAR2)
1634 IS
1635 
1636     lv1 varchar2(80);
1637     lv2 varchar2(80);
1638     lv_schema varchar2(80);
1639     lv_ret BOOLEAN;
1640 	lv_loc1 number := 1;
1641 	lv_loc2 number;
1642 	lv_len number := LENGTH(p_pkg_body);
1643 	lv_row number := 0;
1644 	lv_status varchar2(40);
1645 	lv_owner varchar2(80);
1646 	lf_owner varchar2(80);
1647 	lv_tmp varchar2(32767);
1648 	lv_tmp_len number;
1649 	lv_lf varchar2(10);
1650 	lv_pkg_name varchar2(80) := UPPER(p_pkg_name);
1651 	CURSOR lc_status IS
1652 	  select status, owner
1653 	  from all_objects
1654 	  where object_name = lv_pkg_name and
1655 		   object_type = 'PACKAGE BODY' and
1656                    owner = lf_owner and
1657 		   status <> 'VALID';
1658 	CURSOR lc_err(l_owner IN VARCHAR2) IS
1659 	 select text from all_errors
1660 	 where
1661 	   owner = l_owner and
1662 	   name = p_pkg_name
1663 	   order by line;
1664 BEGIN
1665 
1666         select user into lf_owner from dual;
1667 	x_return_code := 0;
1668   	lv_ret := FND_INSTALLATION.get_app_info(
1669        'FND',
1670 		lv1,
1671 		lv2,
1672 		lv_schema);
1673 	lv_lf := '
1674 ';
1675     LOOP
1676 		if lv_loc1 > lv_len then
1677 			exit;
1678 		end if;
1679 		lv_loc2 := INSTR(p_pkg_body,lv_lf,lv_loc1,1);
1680 		if lv_loc2 = 0 then
1681 			lv_tmp_len := lv_len - lv_loc1 + 1;
1682 			lv_tmp := SUBSTR(p_pkg_body,lv_loc1,lv_tmp_len);
1683 			if lv_tmp_len <= 255 then
1684 				lv_row := lv_row + 1;
1685   				ad_ddl.build_package(
1686 					lv_tmp,
1687 					lv_row
1688 					);
1689 				exit;
1690 			else
1691 	  			x_return_code := -20111;
1692 	  			x_error_string :=
1693 					'Error:The following line exceeds 255 character.  '||
1694 					'Please insert a character return to break up the line.'||
1695 					SUBSTR(lv_tmp,1,257);
1696 				return;
1697 			end if;
1698 		else
1699 			lv_tmp_len := lv_loc2 - lv_loc1;
1700 			lv_tmp := SUBSTR(p_pkg_body,lv_loc1,lv_tmp_len);
1701 			if lv_tmp_len <= 255 then
1702 				lv_row := lv_row + 1;
1703   				ad_ddl.build_package(
1704 					lv_tmp,
1705 					lv_row
1706 					);
1707 				lv_loc1 := lv_loc1 + lv_tmp_len + 1;
1708 			else
1709 	  			x_return_code := -20111;
1710 	  			x_error_string :=
1711 					'Error:The following line exceeds 255 character.  '||
1712 					'Please insert a character return to break up the line.'||
1713 					SUBSTR(lv_tmp,1,257);
1714 				return;
1715 			end if;
1716 		end if;
1717 	END LOOP;
1718 	IF lv_row = 0 THEN
1719 	  x_return_code := -20111;
1720 	  x_error_string := 'Error:The package body can not be empty.';
1721 	  return;
1722 	END IF;
1723 
1724 	AD_DDL.CREATE_PACKAGE(
1725 		lv_schema  ,
1726 		p_application_short_name  ,
1727 		p_pkg_name,
1728 		'TRUE',
1729 		1,
1730 		lv_row
1731 		);
1732 
1733 	lv_status := 'VALID';
1734 	FOR lv_status_rec IN lc_status LOOP
1735       lv_status := lv_status_rec.status;
1736 	  lv_owner := lv_status_rec.owner;
1737 	  exit;
1738 	END LOOP;
1739     IF lv_status <> 'VALID' THEN
1740 	   x_return_code := -24344;
1741 	   for lv_err_rec in lc_err(lv_owner) loop
1742 		  x_error_string := x_error_string ||lv_err_rec.text||' ';
1743 	   end loop;
1744 	END IF;
1745 EXCEPTION
1746 	WHEN OTHERS THEN
1747 	x_return_code := SQLCODE;
1748 	x_error_string := SQLERRM;
1749 END Create_PKG_Body;
1750 
1751 
1752 Procedure DISPLAY (
1753   p_OutputString in varchar2)
1754 IS
1755 lv_start number;
1756 lv_cnt number;
1757 lv_done number;
1758 
1759 BEGIN
1760  lv_done := 0;
1761  lv_start := 1;
1762  lv_cnt := 1;
1763  IF LENGTH(p_OutputString) > 255 THEN
1764   WHILE lv_done <> 1 LOOP
1765     IF (lv_start + 255) > LENGTH(p_OutputString) or (lv_start + 255) > 32767 THEN
1766      lv_done := 1;
1767 --   DBMS_OUTPUT.PUT_LINE(SUBSTR(p_OutputString,lv_start,(LENGTH(p_OutputString) - lv_start)));
1768     ELSE
1769 --     DBMS_OUTPUT.PUT_LINE(SUBSTR(p_OutputString,lv_start,255));
1770      lv_start := lv_cnt * 256;
1771      lv_cnt := lv_cnt + 1;
1772     END IF;
1773   END LOOP;
1774  ELSE
1775 --   DBMS_OUTPUT.PUT_LINE(p_OutputString);
1776   null;
1777  END IF;
1778 exception
1779 when others then
1780 -- DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,255));
1781   null;
1782 END Display;
1783 
1784 -- An API to get the wf_role_name for notifications generated by workflow
1785 -- The input parameter is the responsibity_key, i.e., OP_SYSADMIN, NP_SYSADMIN
1786 -- The notification must be sent to the role name returned by this API
1787 -- for FMC to retrieve it
1788 Function Get_WF_NotifRecipient(p_responsibility_key in varchar2)
1789   return varchar2
1790 IS
1791 
1792  l_ApplicationID number;
1793  l_ResponsibilityID number;
1794 
1795  l_NotifRecipient varchar2(80);
1796  l_DispName varchar2(200);
1797 begin
1798 
1799   select application_id, responsibility_id
1800     into l_ApplicationID, l_ResponsibilityID
1801   from fnd_responsibility
1802     where RESPONSIBILITY_KEY = p_responsibility_key;
1803 
1804 
1805   wf_directory.getrolename(P_ORIG_SYSTEM => 'FND_RESP' ||
1806 							to_char(l_ApplicationID),
1807                            P_ORIG_SYSTEM_ID => to_char(l_ResponsibilityID),
1808                            P_NAME => l_NotifRecipient,
1809                            P_DISPLAY_NAME => l_DispName);
1810 
1811  return l_NotifRecipient;
1812 
1813 
1814 END Get_WF_NotifRecipient;
1815 
1816 
1817 -- The API gets the Recipient of All System Error Notifications
1818 -- Get the Profile Option Value and if the profile Option Value is null
1819 -- return SFM System Administrator as value
1820 
1821 Function GetSystemErrNotifRecipient return varchar2 is
1822  l_NotifRecipient varchar2(2000);
1823 begin
1824 
1825  if fnd_profile.defined(pv_DefErrNotifProfile) then
1826    fnd_profile.get(pv_DefErrNotifProfile, l_NotifRecipient);
1827  else
1828    l_NotifRecipient := pv_DefErrNotifRecipient;
1829  end if;
1830 
1831  if l_NotifRecipient is null then
1832 	l_NotifRecipient := pv_DefErrNotifRecipient;
1833  end if;
1834 
1835  return (l_NotifRecipient);
1836 
1837 end GetSystemErrNotifRecipient;
1838 
1839 
1840 -- This procedure is used for compile pl/sql procedures stored in xdp tables
1841 -- It should be run when instance is migrated to a different instance. After data
1842 -- import/export, run this procedure to validate all the procedures configured in
1843 -- the old instance.
1844 
1845 -- This procedure will be the base of a concurrent program.
1846 
1847 PROCEDURE RECOMPPKG
1848 (
1849      ERRBUF	            	OUT NOCOPY	VARCHAR2,
1850      RETCODE	        	OUT NOCOPY	VARCHAR2
1851 ) IS
1852   lv_lob2 clob;
1853   lv_str varchar2(2000);
1854   lv_body varchar2(32767);
1855   lv_ret number;
1856   lv_src_length number;
1857   lv_fa_id number := NULL;
1858   lv_fetype_id number := NULL;
1859   lv_proc_name varchar2(80);
1860   lv_index number;
1861   lv_sw_gen_id number := NULL;
1862   lv_ActualID number := NULL;
1863   lv_adapter_type varchar2(80) := null;
1864 
1865   lv_amount number := 1000;
1866   lv_offset number := 1;
1867   lv_buffer varchar2(80);
1868 
1869   CURSOR lc_proc IS
1870    SELECT PROC_NAME,PROC_TYPE
1871    FROM XDP_PROC_BODY
1872    WHERE protected_flag = 'N'
1873    ORDER BY PROC_TYPE, PROC_NAME;
1874 
1875   CURSOR lc_adapter IS
1876    SELECT ADAPTER_TYPE
1877    FROM xdp_fe_sw_gen_lookup
1878    WHERE FE_SW_GEN_LOOKUP_ID = lv_sw_gen_id;
1879 
1880 
1881 BEGIN
1882 
1883 	FOR lv_rec IN lc_proc LOOP
1884 
1885        fnd_file.put_line(fnd_file.output,'--------------------------------------------------------------');
1886        fnd_file.put_line(fnd_file.output,' ');
1887        fnd_file.put_line(fnd_file.output,'Examining Procedure: ' || lv_rec.proc_name);
1888        fnd_file.put_line(fnd_file.output,'Procedure Type found to be: ' || lv_rec.proc_type);
1889 
1890       lv_body := null;
1891       lv_amount := 80;
1892       lv_offset := 1;
1893       lv_src_length := 0;
1894       lv_buffer := NULL;
1895 
1896       select proc_body into lv_lob2
1897       from xdp_proc_body
1898       where proc_name = lv_rec.proc_name
1899       and proc_type = lv_rec.proc_type;
1900 
1901       lv_src_length := dbms_lob.GETLENGTH(lv_lob2);
1902       IF lv_src_length = 0 THEN
1903         fnd_file.put_line(fnd_file.log, 'Procedure '||
1904 						lv_rec.proc_name||
1905 						' does not contain any body text.');
1906         fnd_file.put_line(fnd_file.log, 'Ignored...');
1907         GOTO l_continue;
1908       ELSE
1909         null;
1910       END IF;
1911 
1912    -- Read the CLOB into a varchar2 buffer!!!
1913       begin
1914         loop
1915            dbms_lob.read(lv_lob2, lv_amount, lv_offset, lv_buffer);
1916            lv_body := lv_body || lv_buffer;
1917 
1918            if lv_offset >= 32767 then
1919               exit;
1920            end if;
1921 
1922            lv_offset := lv_offset + lv_amount;
1923 
1924          end loop;
1925         exception
1926         when no_data_found then
1927             null;
1928         end;
1929 
1930        IF lv_rec.proc_type = 'PROVISIONING' THEN
1931          BEGIN
1932            SELECT FULFILLMENT_ACTION_ID, FE_SW_GEN_LOOKUP_ID
1933            INTO lv_fa_id, lv_sw_gen_id
1934            FROM
1935               XDP_FA_FULFILLMENT_PROC fp
1936            where
1937              fp.fulfillment_proc = lv_rec.proc_name AND
1938              rownum = 1;
1939 
1940 		for v_adapter in lc_adapter loop
1941 			lv_adapter_type := v_adapter.adapter_type;
1942 		end loop;
1943 
1944           EXCEPTION
1945           WHEN NO_DATA_FOUND THEN
1946              lv_fa_id := NULL;
1947          END;
1948        ELSIF lv_rec.proc_type = 'CONNECT' THEN
1949          BEGIN
1950            SELECT FETYPE_ID
1951            INTO lv_fetype_id
1952            FROM
1953               xdp_fe_sw_gen_lookup fp
1954            where
1955               fp.sw_start_proc = lv_rec.proc_name AND
1956               rownum = 1;
1957           EXCEPTION
1958           WHEN NO_DATA_FOUND THEN
1959             BEGIN
1960               SELECT fa.FETYPE_ID
1961               INTO lv_fetype_id
1962               FROM XDP_FES fa,
1963                xdp_fe_generic_config fp
1964               where
1965                fa.fe_id = fp.fe_id AND
1966                fp.sw_start_proc = lv_rec.proc_name AND
1967                rownum = 1;
1968              EXCEPTION
1969                WHEN NO_DATA_FOUND THEN
1970                  lv_fetype_id := NULL;
1971             END;
1972         END;
1973        ELSIF lv_rec.proc_type = 'DISCONNECT' THEN
1974          BEGIN
1975            SELECT FETYPE_ID
1976            INTO lv_fetype_id
1977            FROM
1978               xdp_fe_sw_gen_lookup fp
1979            where
1980               fp.sw_exit_proc = lv_rec.proc_name AND
1981               rownum = 1;
1982           EXCEPTION
1983           WHEN NO_DATA_FOUND THEN
1984             BEGIN
1985               SELECT fa.FETYPE_ID
1986               INTO lv_fetype_id
1987               FROM XDP_FES fa,
1988                xdp_fe_generic_config fp
1989               where
1990                fa.fe_id = fp.fe_id AND
1991                fp.sw_exit_proc = lv_rec.proc_name AND
1992                rownum = 1;
1993              EXCEPTION
1994                WHEN NO_DATA_FOUND THEN
1995                  lv_fetype_id := NULL;
1996             END;
1997         END;
1998       ELSE
1999           lv_fetype_id := NULL;
2000  		 lv_fa_id := NULL;
2001       END IF;
2002 
2003 	lv_proc_name := XDP_PROCEDURE_UTIL.decode_proc_name (lv_rec.proc_name) ;
2004 
2005 -- The ID for Pre-Compilation depends on the type of the procedure
2006 	if lv_rec.proc_type = 'PROVISIONING' then
2007 		lv_ActualID := lv_fa_id;
2008 	elsif lv_rec.proc_type in ('CONNECT','DISCONNECT') then
2009 		lv_ActualID := lv_fetype_id;
2010 	end if;
2011 
2012 	fnd_file.put_line(fnd_file.output,'Pre-Compiling Package...');
2013 	xdp_procedure_builder.PrecompileProcedure(
2014                         p_ProcType => lv_rec.proc_type,
2015                         p_ProcBody => lv_body,
2016                         p_ID => lv_ActualID,
2017 			p_AdapterType => lv_adapter_type,
2018                         x_ErrorCode => lv_ret,
2019                         x_ErrorString => lv_str);
2020 
2021 	 if lv_ret <> 0 then
2022                 fnd_file.put_line(fnd_file.log,
2023 		'--------------------------------------------------------------');
2024                 fnd_file.put_line(fnd_file.log,'Package Pre-Compilation failed');
2025 		fnd_file.put_line(fnd_file.log,
2026 			'Pre-Compilation failed for procedure: '||lv_rec.proc_name);
2027 		for lv_index in 1..(LENGTH(lv_str)/80 + 1) loop
2028 			fnd_file.put_line(fnd_file.log,
2029 				SUBSTR(lv_str,((lv_index - 1) * 80) + 1, 80));
2030 		end loop;
2031                 lv_ret := 0;
2032 		goto l_continue;
2033 	 else
2034 		fnd_file.put_line(fnd_file.output,
2035 				'Package Pre-Compiled created successfully... ');
2036 	end if;
2037 
2038 
2039          fnd_file.put_line(fnd_file.output,'Creating Package Spec... ');
2040 	 XDP_PROCEDURE_UTIL.Create_Package_Spec(
2041 		lv_proc_name,
2042 		lv_rec.proc_type,
2043 		lv_ret,
2044 		lv_str);
2045 	 if lv_ret <> 0 then
2046                 fnd_file.put_line(fnd_file.log,'--------------------------------------------------------------');
2047                 fnd_file.put_line(fnd_file.log,'Package Spec creation failed');
2048 		fnd_file.put_line(fnd_file.log,'Compilation failed for procedure: '||lv_rec.proc_name);
2049 		for lv_index in 1..(LENGTH(lv_str)/80 + 1) loop
2050 			fnd_file.put_line(fnd_file.log,SUBSTR(lv_str,((lv_index - 1) * 80) + 1, 80));
2051 		end loop;
2052                 lv_ret := 0;
2053 		goto l_continue;
2054 	 else
2055          fnd_file.put_line(fnd_file.output,'Package Spec created successfully... ');
2056          fnd_file.put_line(fnd_file.output,'Creating Package Body... ');
2057 	 	XDP_PROCEDURE_UTIL.Create_Package_Body(
2058 			lv_proc_name,
2059 			lv_rec.proc_type,
2060 			lv_fa_id,
2061 			lv_fetype_id,
2062 			lv_body,
2063 			lv_ret,
2064 			lv_str);
2065 
2066 	 	if lv_ret <> 0 then
2067                    fnd_file.put_line(fnd_file.log,'--------------------------------------------------------------');
2068                    fnd_file.put_line(fnd_file.log,'Package Body creation failed');
2069                    fnd_file.put_line(fnd_file.log,'Compilation failed for procedure: '||lv_rec.proc_name);
2070 			for lv_index in 1..(LENGTH(lv_str)/80 + 1) loop
2071 				fnd_file.put_line(fnd_file.log,SUBSTR(lv_str,((lv_index - 1) * 80) + 1, 80));
2072 			end loop;
2073 	                lv_ret := 0;
2074 			goto l_continue;
2075 		else
2076 			fnd_file.put_line(fnd_file.output,'Compilation succeed for procedure: '||lv_rec.proc_name);
2077                         lv_ret := 0;
2078 			commit;
2079 		end if;
2080 	 end if;
2081     <<l_continue>>
2082     	COMMIT;
2083  		fnd_file.put_line(fnd_file.output,' ');
2084 		fnd_file.put_line(fnd_file.output,'--------------------------------------------------------------');
2085   	END LOOP;
2086 
2087 	RETCODE := 0;
2088     ERRBUF := 'Success';
2089 EXCEPTION
2090 	WHEN OTHERS THEN
2091 		RETCODE := 2;
2092     	ERRBUF := SQLERRM;
2093 END RECOMPPKG;
2094 
2095 PROCEDURE Get_XDP_OrderID_QUERY
2096    (
2097     P_ORDER_ID        IN VARCHAR2,
2098     P_ORDER_NUMBER    IN VARCHAR2,
2099     P_ORDER_VERSION   IN VARCHAR2,
2100     P_ORDER_REF_NAME  IN VARCHAR2,
2101     P_ORDER_REF_VALUE IN VARCHAR2,
2102     P_CUST_ID         IN VARCHAR2,
2103     P_CUST_NAME       IN VARCHAR2,
2104     P_PHONE_NUMBER    IN VARCHAR2,
2105     P_DUE_DATE        IN VARCHAR2,
2106     P_ACCOUNT_ID      IN VARCHAR2,
2107     P_QUERY_BLOCK     IN VARCHAR2,
2108     P_ID_LIST         OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
2109     RETURN_CODE       OUT NOCOPY NUMBER,
2110     ERROR_DESCRIPTION OUT NOCOPY VARCHAR2)
2111 IS
2112 
2113   TYPE v_cursorType IS REF CURSOR;
2114   v_cursor       v_cursorType;
2115   v_numRows      NUMBER := 0;
2116   lv_tmp_id      NUMBER;
2117 
2118 BEGIN
2119 
2120   return_code := 0;
2121 
2122   IF v_cursor%ISOPEN THEN
2123      CLOSE v_cursor;
2124   END IF;
2125 
2126   OPEN v_cursor FOR p_query_block
2127                             USING P_ORDER_ID       ,
2128                                   P_ORDER_NUMBER   ,
2129                                   P_ORDER_VERSION  ,
2130                                   P_ORDER_REF_NAME ,
2131                                   P_ORDER_REF_VALUE,
2132                                   P_CUST_ID        ,
2133                                   P_CUST_NAME      ,
2134                                   P_PHONE_NUMBER   ,
2135                                   P_DUE_DATE       ,
2136                                   P_ACCOUNT_ID     ;
2137 
2138   LOOP
2139    FETCH v_cursor INTO lv_tmp_id;
2140 
2141    EXIT WHEN v_cursor%NOTFOUND OR v_cursor%NOTFOUND IS NULL;
2142 
2143    v_numRows := v_numRows + 1;
2144    p_id_list(v_numRows) := lv_tmp_id;
2145 
2146   END LOOP;
2147 
2148   CLOSE v_cursor;
2149 
2150 EXCEPTION
2151        WHEN others THEN
2152             return_code := SQLCODE;
2153             error_description := SUBSTR(SQLERRM,1,280);
2154             CLOSE v_cursor;
2155 
2156 END Get_XDP_OrderID_QUERY ;
2157 
2158 -- Modified by SXBANERJ 07/05/2001
2159 -- Procedure to call after raising user defined exception
2160 --
2161 -- Comment out fnd log as app_exception.raise_exception
2162 -- does the logging and it is controlled from a profile option
2163 --
2164   PROCEDURE raise_exception(p_object_type   IN VARCHAR2) IS
2165     BEGIN
2166 /*
2167       fnd_log.message(4
2168                     ,p_object_type
2169                     ,FALSE);
2170 */
2171       APP_EXCEPTION.RAISE_EXCEPTION;
2172     END raise_exception;
2173 
2174   -- Procedure to call in WHEN OTHERS
2175   PROCEDURE generic_error(p_object_type IN VARCHAR2
2176                          ,p_object_key  IN VARCHAR2
2177                          ,p_errcode     IN VARCHAR2
2178                          ,p_errmsg      IN VARCHAR2) IS
2179 
2180     e_dummy_exception EXCEPTION;
2181 
2182   BEGIN
2183     IF SQLCODE <> -20001 THEN -- i.e. if procedure is NOT invoked
2184                               -- through APP_EXCEPTION.RAISE_EXCEPTION
2185       FND_MESSAGE.SET_NAME('XDP','XDP_UNHANDLED_EXCEPTION'); -- New message
2186       FND_MESSAGE.SET_TOKEN('OBJECT_KEY',p_object_key);
2187       FND_MESSAGE.SET_TOKEN('ERRCODE',p_errcode);
2188       FND_MESSAGE.SET_TOKEN('ERRMSG',p_errmsg);
2189 /*
2190       fnd_log.message(4
2191                    ,p_object_type
2192                    ,FALSE);
2193 */
2194       APP_EXCEPTION.RAISE_EXCEPTION;
2195     ELSE
2196 /*
2197       fnd_log.message(4
2198                     ,p_object_type
2199                     ,FALSE);
2200 */
2201       APP_EXCEPTION.RAISE_EXCEPTION;
2202     END IF;
2203   END generic_error;
2204 
2205 -- Procedure to write data/text to CLOB from table of records
2206 
2207 PROCEDURE WRITE_TABLE_TO_CLOB (p_source_table       IN XDP_TYPES.VARCHAR2_32767_TAB,
2208                                p_dest_clob      IN OUT NOCOPY CLOB,
2209                                x_error_code        OUT NOCOPY NUMBER,
2210                                x_error_description OUT NOCOPY VARCHAR2) IS
2211 
2212 l_amount    NUMBER;
2213 
2214 BEGIN
2215      DBMS_LOB.CREATETEMPORARY(p_dest_clob,TRUE);
2216 
2217      DBMS_LOB.OPEN(p_dest_clob,DBMS_LOB.LOB_READWRITE);
2218 
2219 
2220      FOR i in 1..p_source_table.COUNT
2221          LOOP
2222              l_amount := LENGTH(p_source_table(i));
2223 
2224              DBMS_LOB.WRITEAPPEND(p_dest_clob,l_amount,p_source_table(i));
2225          END LOOP ;
2226 
2227     DBMS_LOB.CLOSE(p_dest_clob);
2228 
2229 
2230 EXCEPTION
2231      WHEN others THEN
2232           x_error_code := SQLCODE;
2233           x_error_description := SUBSTR(SQLERRM,1,280);
2234 
2235 END WRITE_TABLE_TO_CLOB ;
2236 
2237 
2238 PROCEDURE Initialize_pkg IS
2239 
2240 BEGIN
2241 
2242 g_message_list.DELETE ;
2243 
2244 END Initialize_pkg ;
2245 
2246 
2247 
2248 PROCEDURE Build_pkg(p_text IN VARCHAR2) IS
2249 
2250 l_count NUMBER;
2251 
2252 BEGIN
2253 
2254  l_count := g_message_list.COUNT;
2255  g_message_list(l_count+1) := p_text ;
2256 
2257 END Build_pkg;
2258 
2259 
2260 PROCEDURE Create_pkg (p_pkg_name               IN VARCHAR2,
2261                       p_pkg_type               IN VARCHAR2,
2262 		      p_application_short_name IN VARCHAR2,
2263 		      x_error_code            OUT NOCOPY NUMBER,
2264 		      x_error_message         OUT NOCOPY VARCHAR2) IS
2265 
2266 lv1         VARCHAR2(80);
2267 lv2         VARCHAR2(80);
2268 lv_schema   VARCHAR2(80);
2269 lv_ret      BOOLEAN;
2270 lv_loc1     NUMBER := 1;
2271 lv_loc2     NUMBER;
2272 lv_len      NUMBER := 0;
2273 lv_row      NUMBER := 0;
2274 lv_status   VARCHAR2(40);
2275 lv_owner    VARCHAR2(80);
2276 lf_owner    VARCHAR2(80);
2277 lv_tmp      VARCHAR2(32767);
2278 lv_tmp_len  NUMBER;
2279 lv_lf       VARCHAR2(10);
2280 lv_pkg_name VARCHAR2(80) := UPPER(p_pkg_name);
2281 l_text_line VARCHAR2(32767);
2282 
2283 CURSOR lc_status IS
2284        SELECT status, owner
2285          FROM all_objects
2286         WHERE object_name = lv_pkg_name
2287           AND object_type = p_pkg_type
2288           AND owner = lf_owner
2289           AND status <> 'VALID';
2290 
2291 CURSOR lc_err(l_owner IN VARCHAR2) IS
2292        SELECT text
2293          FROM all_errors
2294         WHERE owner = l_owner
2295           AND name  = lv_pkg_name
2296         ORDER BY line;
2297 
2298 BEGIN
2299         select user into lf_owner from dual;
2300 
2301 x_error_code := 0;
2302 
2303 lv_ret := FND_INSTALLATION.get_app_info(
2304             'FND',
2305              lv1,
2306              lv2,
2307              lv_schema);
2308              lv_lf := '
2309 ';
2310 
2311 FOR i IN 1..g_message_list.COUNT
2312   LOOP
2313      lv_loc1 := 1 ;
2314      lv_len := NVL(LENGTH(g_message_list(i)),0);
2315      l_text_line := g_message_list(i) ;
2316      LOOP
2317 	if lv_loc1 > lv_len then
2318 	   exit;
2319 	end if;
2320 
2321 	lv_loc2 := INSTR(l_text_line,lv_lf,lv_loc1,1);
2322 
2323 	if lv_loc2 = 0 then
2324  	   lv_tmp_len := lv_len - lv_loc1 + 1;
2325 	   lv_tmp := SUBSTR(l_text_line,lv_loc1,lv_tmp_len);
2326 
2327 	   if lv_tmp_len <= 255 then
2328 	      lv_row := lv_row + 1;
2329   	      ad_ddl.build_package(
2330 	          	lv_tmp,
2331 		        lv_row
2332 		        );
2333 	      exit;
2334 	   else
2335 	      x_error_code    := -20111;
2336 	      x_error_message :=
2337 	             'Error:The following line exceeds 255 character.  '||
2338 	             'Please insert a character return to break up the line.'|| SUBSTR(lv_tmp,1,257);
2339 	      return;
2340 	   end if;
2341 	else
2342 	   lv_tmp_len := lv_loc2 - lv_loc1;
2343 	   lv_tmp := SUBSTR(l_text_line,lv_loc1,lv_tmp_len);
2344 
2345 	   if lv_tmp_len <= 255 then
2346 	      lv_row := lv_row + 1;
2347   	      ad_ddl.build_package(
2348 			lv_tmp,
2349 			lv_row
2350 			);
2351 	      lv_loc1 := lv_loc1 + lv_tmp_len + 1;
2352 	   else
2353 	      x_error_code := -20111;
2354 	      x_error_message :=
2355 			'Error:The following line exceeds 255 character.  '||
2356 			'Please insert a character return to break up the line.'||
2357 			SUBSTR(lv_tmp,1,257);
2358 	      return;
2359 	   end if;
2360 	end if;
2361      END LOOP;
2362   END LOOP;
2363 
2364 	IF lv_row = 0 THEN
2365 	  x_error_code := -20111;
2366 	  x_error_message := 'Error:The package spec can not be empty.';
2367 	  return;
2368 	END IF;
2369 
2370 	AD_DDL.CREATE_PACKAGE(
2371 		lv_schema  ,
2372 		p_application_short_name  ,
2373 		p_pkg_name,
2374 		'FALSE',
2375 		1,
2376 		lv_row
2377 		);
2378 
2379 	lv_status := 'VALID';
2380 
2381 	FOR lv_status_rec IN lc_status
2382             LOOP
2383                lv_status := lv_status_rec.status;
2384 	       lv_owner := lv_status_rec.owner;
2385 	       exit;
2386 	    END LOOP;
2387 
2388     IF lv_status <> 'VALID' THEN
2389 	   x_error_code := -24344;
2390 	   FOR lv_err_rec in lc_err(lv_owner)
2391                LOOP
2392 		  x_error_message := x_error_message ||lv_err_rec.text||' ';
2393 	       END LOOP;
2394     END IF;
2395 
2396 EXCEPTION
2397      WHEN others THEN
2398           x_error_code := SQLCODE;
2399           x_error_message := SUBSTR(SQLERRM,1,280);
2400 END Create_pkg;
2401 
2402 PROCEDURE SET_TIME_OUT (itemtype  IN VARCHAR2,
2403 			              itemkey   IN VARCHAR2,
2404 			              actid     IN NUMBER,
2405 			              funcmode  IN VARCHAR2,
2406 			              resultout OUT NOCOPY VARCHAR2 ) IS
2407 
2408  x_Progress   VARCHAR2(2000);
2409  l_time_out_str   VARCHAR2(100);
2410  l_time_out_num   NUMBER;
2411 
2412 BEGIN
2413 
2414 -- RUN mode - normal process execution
2415 --
2416 	IF (funcmode = 'RUN') THEN
2417                if( fnd_profile.defined( 'XDP_TIME_OUT') ) THEN
2418                  fnd_profile.get( 'XDP_TIME_OUT',  l_time_out_str );
2419                  l_time_out_num := to_number( l_time_out_str );
2420                  wf_engine.SetItemAttrNumber(itemtype => SET_TIME_OUT.itemtype,
2421                                              itemkey  => SET_TIME_OUT.itemkey,
2422                                              aname    => 'TIME_OUT',
2423                                              avalue   => l_time_out_num );
2424 
2425                end if;
2426                resultout := 'COMPLETE';
2427                return;
2428         ELSE
2429                resultout := 'COMPLETE';
2430                return;
2431         END IF;
2432 
2433 
2434 EXCEPTION
2435      WHEN OTHERS THEN
2436       wf_core.context('XDP_UTILITIES', 'SET_TIME_OUT', itemtype, itemkey, to_char(actid), funcmode);
2437       raise;
2438 END SET_TIME_OUT;
2439 
2440 PROCEDURE SET_FP_RETRY_COUNT (itemtype  IN VARCHAR2,
2441 			              itemkey   IN VARCHAR2,
2442 			              actid     IN NUMBER,
2443 			              funcmode  IN VARCHAR2,
2444 			              resultout OUT NOCOPY VARCHAR2 ) IS
2445 
2446  x_Progress   VARCHAR2(2000);
2447  l_fp_retry_count_str   VARCHAR2(100);
2448  l_fp_retry_count_num   NUMBER;
2449  l_err_desc   VARCHAR2(2000);
2450  l_err_code   NUMBER;
2451  l_fp_current_numb_retries   NUMBER;
2452 
2453 BEGIN
2454 
2455 -- RUN mode - normal process execution
2456 --
2457 	IF (funcmode = 'RUN') THEN
2458                if( fnd_profile.defined( 'XDP_FP_RETRY_COUNT') ) THEN
2459 
2460                  fnd_profile.get( 'XDP_FP_RETRY_COUNT',  l_fp_retry_count_str );
2461                  l_fp_retry_count_num := to_number( l_fp_retry_count_str );
2462 
2463                  BEGIN
2464                    l_fp_current_numb_retries := wf_engine.getItemAttrNumber(
2465                                                   itemtype => SET_FP_RETRY_COUNT.itemtype,
2466                                                   itemkey  => SET_FP_RETRY_COUNT.itemkey,
2467                                                   aname    => 'RETRY_COUNT');
2468                    IF( l_fp_current_numb_retries > l_fp_retry_count_num ) THEN
2469                      --Clear the time out Item attribute
2470                      wf_engine.setItemAttrNumber( itemtype => SET_FP_RETRY_COUNT.itemtype,
2471                                                   itemkey  => SET_FP_RETRY_COUNT.itemkey,
2472                                                   aname    => 'TIME_OUT',
2473                                                   avalue   => 0);
2474 
2475                      resultout := 'Y';
2476                    ELSE
2477                      resultout := 'N';
2478                    END IF;
2479 
2480                    l_fp_current_numb_retries := l_fp_current_numb_retries + 1;
2481 
2482                    wf_engine.setItemAttrNumber( itemtype => SET_FP_RETRY_COUNT.itemtype,
2483                                                 itemkey  => SET_FP_RETRY_COUNT.itemkey,
2484                                                 aname    => 'RETRY_COUNT',
2485                                                 avalue   => l_fp_current_numb_retries);
2486 
2487                  EXCEPTION
2488                    WHEN others THEN
2489                      --Item attribute doesnt exist; set to retry Zero.
2490                      xdpcore.checkNAddItemAttrNumber( itemtype => SET_FP_RETRY_COUNT.itemtype,
2491                                                   itemkey  => SET_FP_RETRY_COUNT.itemkey,
2492                                                   attrname    => 'RETRY_COUNT',
2493                                                   attrvalue   => 0,
2494                                                   errcode  => l_err_code,
2495                                                   errstr   => l_err_desc);
2496                    resultout := 'N';
2497                  END;
2498                else
2499                  --If profile option is not defined..
2500                  resultout := 'N';
2501                end if;
2502         ELSE
2503                resultout := 'COMPLETE';
2504                return;
2505         END IF;
2506 
2507 
2508 EXCEPTION
2509      WHEN OTHERS THEN
2510       wf_core.context('XDP_UTILITIES', 'SET_FP_RETRY_COUNT', itemtype, itemkey, to_char(actid), funcmode);
2511       raise;
2512 END SET_FP_RETRY_COUNT;
2513 
2514 PROCEDURE GET_FA_RESPONSE_LOB_CONTENT ( p_FAInstanceID VARCHAR2,p_FECmdSequence VARCHAR2,  p_clob_content OUT NOCOPY VARCHAR2 )
2515 IS
2516  l_clob CLOB;
2517  l_length number := 32767;
2518 BEGIN
2519   SELECT response_long into l_clob
2520   FROM xdp_fe_cmd_aud_trails
2521   WHERE fa_instance_id = p_FAInstanceID
2522   AND fe_command_seq = p_FECmdSequence;
2523   -- get the content..
2524   dbms_lob.read(l_clob,l_length,1,p_clob_content);
2525 EXCEPTION
2526      WHEN OTHERS THEN
2527       xdpcore.context('XDP_UTILITIES', 'GET_FA_RESPONSE_LOB_CONTENT', 'FA', p_FAInstanceID);
2528       raise;
2529 end GET_FA_RESPONSE_LOB_CONTENT;
2530 
2531 
2532 
2533 Function GET_ASCII_TEXT( p_raw_string IN VARCHAR2 ) return VARCHAR2
2534 IS
2535  l_ascii_string VARCHAR2(32767);
2536  l_cur_char	CHAR(1) ;
2537  l_str_length	NUMBER ;
2538  l_char_ascii	NUMBER ;
2539 
2540 BEGIN
2541   l_str_length := LENGTH( p_raw_string ) ;
2542   IF l_str_length = 0 THEN
2543     RETURN NULL;
2544   END IF;
2545 
2546   FOR i IN 1..l_str_length LOOP
2547 
2548     l_cur_char   := SUBSTR( p_raw_string, i, 1 ) ;
2549     l_char_ascii := ASCII( l_cur_char ) ;
2550 
2551     -- ASCII character range 20 - 126 are displayble characters
2552     -- To keep the formatting we dont want to replace  ASCII 10(line feed)
2553     -- and 13(Vertical Tab).
2554 
2555     IF ( l_char_ascii  BETWEEN 20 AND 126 ) THEN
2556       l_ascii_string := l_ascii_string || l_cur_char;
2557     ELSE
2558       l_ascii_string := l_ascii_string || 'CHR('|| l_char_ascii || ')' || l_cur_char;
2559     END IF;
2560 
2561     IF ( LENGTH( l_ascii_string ) = 32767 ) THEN
2562       EXIT;
2563     END IF;
2564   END LOOP;
2565 
2566   RETURN l_ascii_string;
2567 
2568 EXCEPTION
2569      WHEN OTHERS THEN
2570       raise;
2571 END GET_ASCII_TEXT;
2572 
2573 
2574 END XDP_UTILITIES;