[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;