1 PACKAGE XDP_UTILITIES AUTHID CURRENT_USER AS
2 /* $Header: XDPUTILS.pls 120.1 2005/06/16 02:44:42 appldev $ */
3
4
5 G_MESSAGE_LIST XDP_TYPES.VARCHAR2_32767_TAB;
6 G_CLOB CLOB;
7 g_fp_object_type VARCHAR2(5) := 'FP';
8 g_fa_object_type VARCHAR2(5) := 'FA';
9 g_wi_object_type VARCHAR2(5) := 'WI';
10 g_service_object_type VARCHAR2(10) := 'SERVICE';
11
12 g_wait_for_resource VARCHAR2(40) := 'WAIT_FOR_RESOURCE';
13 g_system_hold VARCHAR2(40) := 'SYSTEM_HOLD';
14
15 pv_DefErrNotifProfile varchar2(30) := 'XDP_SYS_ERR_RECIPIENT';
16 pv_DefErrNotifRecipient varchar2(30) := 'FND_RESP535:21704';
17
18 pv_ErrorNotifItemType varchar2(8) := 'XDPWFSTD';
19
20 -- Call any SFM Workitem Parameter evaluation procedure
21 --
22 -- the user defined WI parameter evaluation procedure should use
23 -- the following spec:
24 -- procedure <name of the proc>(
25 -- p_order_id IN NUMBER,
26 -- p_line_item_id IN NUMBER,
27 -- p_wi_instance_id IN NUMBER,
28 -- p_param_val IN Varchar2,
29 -- p_param_ref_val IN Varchar2,
30 -- p_param_eval_val OUT NOCOPY VARCHAR2,
31 -- p_param_eval_ref_val OUT NOCOPY Varchar2,
32 -- p_return_code OUT NOCOPY NUMBER,
33 -- p_error_description OUT NOCOPY VARCHAR2)
34 --
35 PROCEDURE CallWIParamEvalProc(
36 p_procedure_name IN Varchar2,
37 p_order_id IN NUMBER,
38 p_line_item_id IN NUMBER,
39 p_wi_instance_id IN NUMBER,
40 p_param_val IN Varchar2,
41 p_param_ref_val IN Varchar2,
42 p_param_eval_val OUT NOCOPY VARCHAR2,
43 p_param_eval_ref_val OUT NOCOPY Varchar2,
44 p_return_code OUT NOCOPY NUMBER,
45 p_error_description OUT NOCOPY VARCHAR2);
46
47 -- Call any SFM FA Parameter evaluation procedure
48 --
49 -- the user defined FA parameter evaluation procedure should use
50 -- the following spec:
51 -- procedure <name of the proc>(
52 -- p_order_id IN NUMBER,
53 -- p_line_item_id IN NUMBER,
54 -- p_wi_instance_id IN NUMBER,
55 -- p_fa_instance_id IN NUMBER,
56 -- p_param_val IN Varchar2,
57 -- p_param_ref_val IN Varchar2,
58 -- p_param_eval_val OUT NOCOPY VARCHAR2,
59 -- p_param_eval_ref_val OUT NOCOPY Varchar2,
60 -- p_return_code OUT NOCOPY NUMBER,
61 -- p_error_description OUT NOCOPY VARCHAR2)
62 --
63 PROCEDURE CallFAParamEvalProc(
64 p_procedure_name IN Varchar2,
65 p_order_id IN NUMBER,
66 p_line_item_id IN NUMBER,
67 p_wi_instance_id IN NUMBER,
68 p_fa_instance_id IN NUMBER,
69 p_param_val IN Varchar2,
70 p_param_ref_val IN Varchar2,
71 p_param_eval_val OUT NOCOPY VARCHAR2,
72 p_param_eval_ref_val OUT NOCOPY Varchar2,
73 p_return_code OUT NOCOPY NUMBER,
74 p_error_description OUT NOCOPY VARCHAR2);
75
76 -- Call any SFM FA evaluation procedure
77 --
78 -- the user defined FA evaluation procedure will
79 -- evaluate all the FA parameters when the FA instance
80 -- is added to a workitem at runtime. The procedure should use
81 -- the following spec:
82 -- procedure <name of the proc>(
83 -- p_order_id IN NUMBER,
84 -- p_line_item_id IN NUMBER,
85 -- p_wi_instance_id IN NUMBER,
86 -- p_fa_instance_id IN NUMBER,
87 -- p_return_code OUT NOCOPY NUMBER,
88 -- p_error_description OUT NOCOPY VARCHAR2)
89 --
90 PROCEDURE CallFAEvalAllProc(
91 p_procedure_name IN Varchar2,
92 p_order_id IN NUMBER,
93 p_line_item_id IN NUMBER,
94 p_wi_instance_id IN NUMBER,
95 p_fa_instance_id IN NUMBER,
96 p_return_code OUT NOCOPY NUMBER,
97 p_error_description OUT NOCOPY VARCHAR2);
98
99 -- Call any SFM FE routing procedure
100 --
101 -- the user defined FE routing procedure is used by
102 -- SFM to determine which FE to talk to for an FA at runtime
103 -- base on the order information. The procedure should use
104 -- the following spec:
105 -- procedure <name of the proc>(
106 -- p_order_id IN NUMBER,
107 -- p_line_item_id IN NUMBER,
108 -- p_wi_instance_id IN NUMBER,
109 -- p_fa_instance_id IN NUMBER,
110 -- p_fe_name OUT NOCOPY VARCHAR2,
111 -- p_return_code OUT NOCOPY NUMBER,
112 -- p_error_description OUT NOCOPY VARCHAR2)
113 --
114
115 PROCEDURE CallFERoutingProc(
116 p_procedure_name IN Varchar2,
117 p_order_id IN NUMBER,
118 p_line_item_id IN NUMBER,
119 p_wi_instance_id IN NUMBER,
120 p_fa_instance_id IN NUMBER,
121 p_fe_name OUT NOCOPY VARCHAR2,
122 p_return_code OUT NOCOPY NUMBER,
123 p_error_description OUT NOCOPY VARCHAR2);
124
125 -- Call any SFM NEM connect/disconnect procedure
126 --
127 -- the user defined NEM connect/disconnect procedure should use
128 -- the following spec:
129 -- procedure <name of the proc>(
130 -- p_fe_name IN Varchar2,
131 -- p_channel_name IN Varchar2,
132 -- p_return_code IN OUT NOCOPY NUMBER,
133 -- p_error_description IN OUT NOCOPY VARCHAR2)
134 --
135
136 PROCEDURE Call_NEConnection_Proc(
137 p_procedure_name IN Varchar2,
138 p_fe_name IN Varchar2,
139 p_channel_name IN Varchar2,
140 p_return_code OUT NOCOPY NUMBER,
141 p_error_description OUT NOCOPY VARCHAR2);
142
143
144 -- Call any SFM FA fulfillment procedure
145 --
146 -- the user defined fulfillment procedure should use
147 -- the following spec:
148 -- procedure <name of the proc>(
149 -- p_order_id IN NUMBER,
150 -- p_line_item_id IN NUMBER,
151 -- p_wi_instance_id IN NUMBER,
152 -- p_fa_instance_id IN NUMBER,
153 -- p_channel_name IN Varchar2,
154 -- p_fe_name IN VARCHAR2,
155 -- p_fa_item_type IN VARCHAR2,
156 -- p_fa_item_key IN VARCHAR2,
157 -- p_return_code OUT NOCOPY NUMBER,
158 -- p_error_description OUT NOCOPY VARCHAR2)
159 --
160
161 PROCEDURE CallFulfillmentProc(
162 p_procedure_name IN Varchar2,
163 p_order_id IN NUMBER,
164 p_line_item_id IN NUMBER,
165 p_wi_instance_id IN NUMBER,
166 p_fa_instance_id IN NUMBER,
167 p_channel_name IN Varchar2,
168 p_fe_name IN VARCHAR2,
169 p_fa_item_type IN VARCHAR2,
170 p_fa_item_key IN VARCHAR2,
171 p_return_code OUT NOCOPY NUMBER,
172 p_error_description OUT NOCOPY VARCHAR2);
173
174
175 -- Call any SFM workitem FA dynamic mapping procedure
176 --
177 -- the user defined FA dynamic mapping procedure should use
178 -- the following spec:
179 -- procedure <name of the proc>(
180 -- p_order_id IN NUMBER,
181 -- p_line_item_id IN NUMBER,
182 -- p_wi_instance_id IN NUMBER,
183 -- p_return_code OUT NOCOPY NUMBER,
184 -- p_error_description OUT NOCOPY VARCHAR2)
185 --
186
187 PROCEDURE CallFAMapProc(
188 p_procedure_name IN Varchar2,
189 p_order_id IN NUMBER,
190 p_line_item_id IN NUMBER,
191 p_wi_instance_id IN NUMBER,
192 p_return_code OUT NOCOPY NUMBER,
193 p_error_description OUT NOCOPY VARCHAR2);
194
195 --
196 -- Call any SFM service action to workitem dynamic mapping procedure
197 --
198 -- the user defined WI dynamic mapping procedure should use
199 -- the following spec:
200 -- procedure <name of the proc>(
201 -- p_order_id IN NUMBER,
202 -- p_line_item_id IN NUMBER,
203 -- p_return_code OUT NOCOPY NUMBER,
204 -- p_error_description OUT NOCOPY VARCHAR2)
205 --
206
207 PROCEDURE CallWIMapProc(
208 p_procedure_name IN Varchar2,
209 p_order_id IN NUMBER,
210 p_line_item_id IN NUMBER,
211 p_return_code OUT NOCOPY NUMBER,
212 p_error_description OUT NOCOPY VARCHAR2);
213
214 --
215 -- Call any SFM workitem user workflow start up procedure
216 -- WI workflow startup procedure is used when the user wishes
217 -- to use their own WI workflow to process the workitem.
218 -- This procedure should create the workflow process and
219 -- return the itemtype, itemkey, and process name to SFM.
220 -- This procedure however, SHOULD NOT start the workflow process as
221 -- it will be handled by SFM.
222 -- The user defined WI workflow startup procedure should use
223 -- the following spec:
224 -- procedure <name of the proc>(
225 -- p_order_id IN NUMBER,
226 -- p_line_item_id IN NUMBER,
227 -- p_wi_instance_id IN NUMBER,
228 -- p_wf_item_type OUT NOCOPY varchar2,
229 -- p_wf_item_key OUT NOCOPY varchar2,
230 -- p_wf_process_name OUT NOCOPY varchar2,
231 -- p_reurn_code OUT NOCOPY NUMBER,
232 -- p_error_description OUT NOCOPY VARCHAR2)
233 --
234
235 PROCEDURE CallWIWorkflowProc(
236 p_procedure_name IN Varchar2,
237 p_order_id IN NUMBER,
238 p_line_item_id IN NUMBER,
239 p_wi_instance_id IN NUMBER,
240 p_wf_item_type OUT NOCOPY varchar2,
241 p_wf_item_key OUT NOCOPY varchar2,
242 p_wf_process_name OUT NOCOPY varchar2,
243 p_return_code OUT NOCOPY NUMBER,
244 p_error_description OUT NOCOPY VARCHAR2);
245
246
247 --
248 -- Call any SFM DRC Task Result Procedure.
249 -- DRC Task Result Procedure is used to construct the
250 -- DRC task result string after SFM has perfromed a DRC
251 -- task. The user will use this procedure to examine
252 -- all the workitems which had been executed by SFM for
253 -- the given DRC task and return the result string accordingly.
254 -- The DRC Task Result Procedure should use
255 -- the following spec:
256 -- procedure <name of the proc>(
257 -- p_sdp_order_id IN NUMBER,
258 -- p_task_result OUT NOCOPY varchar2,
259 -- p_reurn_code OUT NOCOPY NUMBER,
260 -- p_error_description OUT NOCOPY VARCHAR2)
261 --
262
263 PROCEDURE CallDRCTaskResultProc(
264 p_procedure_name IN Varchar2,
265 p_sdp_order_id IN NUMBER,
266 p_task_result OUT NOCOPY varchar2,
267 p_return_code OUT NOCOPY NUMBER,
268 p_error_description OUT NOCOPY VARCHAR2);
269
270
271
272 --
273 -- This procedure will insert a new row into sdp_proc_body table
274 --
275 PROCEDURE Create_New_Proc_Body(
276 p_proc_name IN VARCHAR2,
277 p_proc_type IN VARCHAR2 := 'CONNECT',
278 p_proc_spec IN VARCHAR2,
279 p_proc_body IN VARCHAR2,
280 p_creation_date IN DATE,
281 p_created_by IN NUMBER,
282 p_last_update_date IN DATE,
283 p_last_updated_by IN NUMBER,
284 p_last_update_login IN NUMBER,
285 return_code OUT NOCOPY NUMBER,
286 error_description OUT NOCOPY VARCHAR2);
287 --
288 -- This procedure will update the sdp_proc_body table
289 --
290 PROCEDURE Update_Proc_Body(
291 p_proc_name IN VARCHAR2,
292 p_proc_type IN VARCHAR2 := 'CONNECT',
293 p_proc_body IN VARCHAR2,
294 p_last_update_date IN DATE,
295 p_last_updated_by IN NUMBER,
296 p_last_update_login IN NUMBER,
297 return_code OUT NOCOPY NUMBER,
298 error_description OUT NOCOPY VARCHAR2);
299
300 -- A function to convert a clob to a varchar2 value
301 FUNCTION Get_CLOB_Value( p_proc_name IN VARCHAR2)
302 RETURN VARCHAR2;
303
304 -- a procedure to execute any Query which returns a list of IDs
305 PROCEDURE Execute_GetID_QUERY(
306 p_query_block IN VARCHAR2,
307 p_id_list OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
308 return_code OUT NOCOPY NUMBER,
309 error_description OUT NOCOPY VARCHAR2);
310
311 --A procedure to create a package spec dynamically
312 --Param p_pkg_name: the name of the package
313 --Param p_pkg_spec: the ddl statement which creates the package spec
314 --Param p_application_short_name: The short name of our apps,
315 -- should be XDP for OP
316 PROCEDURE Create_PKG_Spec(
317 p_pkg_name IN VARCHAR2,
318 p_pkg_spec IN VARCHAR2,
319 p_application_short_name IN VARCHAR2,
320 x_return_code OUT NOCOPY NUMBER,
321 x_error_string OUT NOCOPY VARCHAR2);
322
323 --A procedure to create or replace a package body dynamically
324 --Param p_pkg_name: the name of the package
325 --Param p_pkg_body: the ddl statement which creates the package body
326 --Param p_application_short_name: The short name of our apps,
327 -- should be XDP for OP
328 PROCEDURE Create_PKG_Body(
329 p_pkg_name IN VARCHAR2,
330 p_pkg_body IN VARCHAR2,
331 p_application_short_name IN VARCHAR2,
332 x_return_code OUT NOCOPY NUMBER,
333 x_error_string OUT NOCOPY VARCHAR2);
334
335 -- Generic parameter for a stored procedure or function
336 TYPE t_Parameter IS RECORD (
337 actual_type VARCHAR2(8), -- One of 'NUMBER', 'VARCHAR2', 'DATE', 'CHAR'
338 actual_length INTEGER,
339 name VARCHAR2(50),
340 num_param NUMBER,
341 vchar_param VARCHAR2(500),
342 char_param CHAR(500),
343 date_param DATE);
344
345
346 -- Generic parameter list
347 TYPE t_ParameterList IS TABLE OF t_Parameter
348 INDEX BY BINARY_INTEGER;
349
350 -- Runs an arbitrary procedure. All of the IN parameters in
351 -- p_Parameters must have at least the _param and actual_type fields
352 -- filled in, and all OUT parameters must have the actual_type field
353 -- populated. On output, the name field is populated.
354 PROCEDURE RunProc(p_NumParams IN NUMBER,
355 p_ProcName IN VARCHAR2,
356 p_Parameters IN OUT NOCOPY t_ParameterList);
357
358 -- Populates the internal data structures with description about the
359 -- procedure given by p_ProcName. If p_Print is TRUE, this information
360 -- is output using DBMS_OUTPUT.
361 PROCEDURE DescribeProc(p_ProcName IN VARCHAR2,
362 p_Print IN BOOLEAN);
363
364 -- Displays, using DBMS_OUTPUT, the parameters in p_Parameters.
365 PROCEDURE Printparams(p_Parameters IN t_ParameterList,
366 p_NumParams IN NUMBER);
367
368 -- Quick workaround for form commit problem
369 PROCEDURE DO_COMMIT;
370
371 -- Quick workaround for form rollback problem
372 PROCEDURE DO_ROLLBACK;
373
374 -- get line item parameter values which is used by order analyzer
375 FUNCTION OA_GetLineParam(
376 p_line_item_id IN Number,
377 p_line_param_name IN Varchar2)
378 return Varchar2;
379 pragma RESTRICT_REFERENCES(OA_GetLineParam, WNDS, WNPS);
380
381 -- get workitem name which is used by order analyzer
382 FUNCTION OA_GetWIName(
383 p_wi_instance_id IN Number)
384 return Varchar2;
385 pragma RESTRICT_REFERENCES(OA_GetWIName, WNDS, WNPS);
386
387 -- get Workitem parameter values which is used by order analyzer
388 FUNCTION OA_GetWIParam(
389 p_wi_instance_id IN Number,
390 p_wi_param_name IN Varchar2)
391 return Varchar2;
392 pragma RESTRICT_REFERENCES(OA_GetWIParam, WNDS, WNPS);
393
394 -- Check if the order line is a workitem
395 FUNCTION OA_Get_LINE_WI_FLAG(
396 p_line_item_id IN Number)
397 return Varchar2;
398 pragma RESTRICT_REFERENCES(OA_Get_LINE_WI_FLAG, WNDS, WNPS);
399
400
401 -- These set of routines are used for the Name/Value parser provided by SFM
402
403 type string_list_t is table of varchar2(32767) index by binary_integer;
404
405 procedure split_lines(buffer in varchar2,string_list in OUT NOCOPY string_list_t ,split_str in varchar2);
406
407 function get_key_name(buffer in varchar2,assign_str in varchar2 := '=' ) return varchar2;
408
409 function get_key_value(buffer in varchar2,assign_str in varchar2 := '=' ) return varchar2;
410
411 procedure Parse_String( buffer in varchar2,
412 NameValueList in OUT NOCOPY XDP_TYPES.NAME_VALUE_LIST,
413 assign_str in varchar2 := '=',
414 p_term_str in varchar2 := NULL);
415 pragma restrict_references(get_key_name,WNDS);
416 pragma restrict_references(get_key_value,WNDS);
417
418 -- End of SFM Name/Value Parser related Functions
419
420 -- vrachur : 07/15/1999 - Added function to Validate names.
421 -- Names should confirm to PL/SQL naming convention.
422
423 FUNCTION IS_VALID_NAME( p_varname IN VARCHAR2 ) RETURN BOOLEAN ;
424 -- skilaru : 08/15/01 - wrapper for JDBC call.
425 FUNCTION ISVALIDNAME( p_varname IN VARCHAR2 ) RETURN VARCHAR2 ;
426
427 --
428 -- This is the function which will mimic the workflow function
429 -- WF_STANDARD.WaitForFlow and handling the workflow concurrency
430 -- problem in WaitForFlow. The caller should pass its current item type,
431 -- item key, and activity name to the API
432 --
433 Procedure WaitForFlow(
434 p_item_type varchar2,
435 p_item_key varchar2,
436 p_activity_name varchar2);
437
438 --
439 -- This is the function which will mimic the workflow function
440 -- WF_STANDARD.ContinueFlow and handling the workflow concurrency
441 -- problem in ContinueFlow. The caller should pass its current item type
442 -- and item key to the API
443 --
444 Procedure ContinueFlow(
445 p_item_type varchar2,
446 p_item_key varchar2);
447
448 -- An API to get the wf_role_name for notifications generated by workflow
449 -- The input parameter is the responsibity_key, i.e., OP_SYSADMIN, NP_SYSADMIN
450 -- The notification must be sent to the role name returned by this API
451 -- for FMC to retrieve it
452 Function Get_WF_NotifRecipient(p_responsibility_key in varchar2)
453 return varchar2;
454
455 -- The API gets the Recipient of All System Error Notifications
456 Function GetSystemErrNotifRecipient return varchar2;
457
458 Procedure Display (
459 p_OutputString in varchar2);
460
461 -- This procedure is used for compile pl/sql procedures stored in xdp tables
462 -- It should be run when instance is migrated to a different instance. After data
463 -- import/export, run this procedure to validate all the procedures configured in
464 -- the old instance.
465
466 -- This procedure will be the base of a concurrent program.
467
468 PROCEDURE RECOMPPKG
469 (
470 ERRBUF OUT NOCOPY VARCHAR2,
471 RETCODE OUT NOCOPY VARCHAR2
472 );
473
474
475 -- a procedure to execute a Query which returns a list of Order Ids for the passed parameter set
476
477 PROCEDURE Get_XDP_OrderID_QUERY
478 (
479 P_ORDER_ID IN VARCHAR2,
480 P_ORDER_NUMBER IN VARCHAR2,
481 P_ORDER_VERSION IN VARCHAR2,
482 P_ORDER_REF_NAME IN VARCHAR2,
483 P_ORDER_REF_VALUE IN VARCHAR2,
484 P_CUST_ID IN VARCHAR2,
485 P_CUST_NAME IN VARCHAR2,
486 P_PHONE_NUMBER IN VARCHAR2,
487 P_DUE_DATE IN VARCHAR2,
488 P_ACCOUNT_ID IN VARCHAR2,
489 P_QUERY_BLOCK IN VARCHAR2,
490 P_ID_LIST OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
491 RETURN_CODE OUT NOCOPY NUMBER,
492 ERROR_DESCRIPTION OUT NOCOPY VARCHAR2);
493
494
495 -- added procedure for error handling
496 -- SXBANERJ 07/05/2001
497
498 -- Procedure to call after raising user defined exception
499 PROCEDURE raise_exception(p_object_type IN VARCHAR2);
500
501 -- Procedure to call in WHEN OTHERS
502 PROCEDURE generic_error(p_object_type IN VARCHAR2
503 ,p_object_key IN VARCHAR2
504 ,p_errcode IN VARCHAR2
505 ,p_errmsg IN VARCHAR2);
506
507 -- Procedure to write data/text to CLOB from table of records
508
509
510 PROCEDURE WRITE_TABLE_TO_CLOB(p_source_table IN XDP_TYPES.VARCHAR2_32767_TAB,
511 p_dest_clob IN OUT NOCOPY CLOB,
512 x_error_code OUT NOCOPY NUMBER,
513 x_error_description OUT NOCOPY VARCHAR2) ;
514
515 PROCEDURE Initialize_pkg ;
516
517
518 PROCEDURE Build_pkg(p_text IN VARCHAR2);
519
520 PROCEDURE Create_pkg (p_pkg_name IN VARCHAR2,
521 p_pkg_type IN VARCHAR2,
522 p_application_short_name IN VARCHAR2,
523 x_error_code OUT NOCOPY NUMBER,
524 x_error_message OUT NOCOPY VARCHAR2) ;
525
526 Procedure SET_TIME_OUT (itemtype in varchar2,
527 itemkey in varchar2,
528 actid in number,
529 funcmode in varchar2,
530 resultout OUT NOCOPY varchar2);
531
532 Procedure GET_FA_RESPONSE_LOB_CONTENT ( p_FAInstanceID VARCHAR2,
533 p_FECmdSequence VARCHAR2,
534 p_clob_content OUT NOCOPY VARCHAR2 );
535
536 PROCEDURE SET_FP_RETRY_COUNT (itemtype IN VARCHAR2,
537 itemkey IN VARCHAR2,
538 actid IN NUMBER,
539 funcmode IN VARCHAR2,
540 resultout OUT NOCOPY VARCHAR2 );
541
542 Function GET_ASCII_TEXT( p_raw_string IN VARCHAR2 ) return VARCHAR2;
543 END XDP_UTILITIES;
[Home] [Help]