1 PACKAGE XNP_UTILS AUTHID CURRENT_USER AS
2 /* $Header: XNPUTILS.pls 120.1 2005/06/24 04:49:47 appldev ship $ */
3
4 G_DEBUG_LEVEL NUMBER ;
5 G_ERROR CONSTANT NUMBER := 1 ;
6 G_WARNING CONSTANT NUMBER := 2 ;
7 G_INFORMATIONAL CONSTANT NUMBER := 3 ;
8
9 -- Cursor to retrive the fe name for a service provider given
10 -- what adapters are serving the service provider
11 --
12 CURSOR g_get_fe_name_for_sp_csr( p_sp_id IN NUMBER ) IS
13 SELECT fulfillment_element_name
14 FROM xdp_fes FET,
15 xnp_sp_adapters SPA,
16 xdp_fe_generic_config SWG
17 WHERE FET.fe_id = SPA.fe_id
18 AND SPA.fe_id = SWG.fe_id
19 AND SPA.sp_id = p_sp_id
20 AND (sysdate BETWEEN SWG.start_date
21 AND NVL(SWG.end_date, sysdate))
22 ORDER BY preferred_flag desc, sequence asc ;
23
24 -- Retrieves order parameters from a flat XML message
25 -- Poplulates the Order header and line item structures
26 -- for submitting the order.
27 -- Note: Works only to extract values from flat XML structure
28 --
29 PROCEDURE MSG_TO_ORDER
30 (P_MSG_TEXT IN VARCHAR2
31 ,P_WI_NAME IN VARCHAR2
32 ,X_LINE_PARAM_LIST OUT NOCOPY XDP_TYPES.LINE_PARAM_LIST
33 ,X_ORDER_LINE_LIST OUT NOCOPY XDP_TYPES.ORDER_LINE_LIST
34 ,X_ERROR_CODE OUT NOCOPY NUMBER
35 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
36 );
37
38 -- Returns the Geo area name for the given porting ID.
39 -- Returned from xnp_geo_areas_b
40 --
41 FUNCTION GET_GEO_INFO ( P_PORTING_ID IN VARCHAR2 )
42 RETURN VARCHAR2 ;
43
44 -- Internal procedure for creating an ACK message.
45 -- Note: Not an user API
46 --
47 PROCEDURE SEND_ACK_MSG (P_MSG_TO_ACK IN NUMBER
48 ,P_CODE IN NUMBER
49 ,P_DESCRIPTION IN VARCHAR2
50 ,X_ERROR_CODE OUT NOCOPY NUMBER
51 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
52 ) ;
53
54 -- Get the adapter of the donor for current transaction.
55 --
56 --
57 PROCEDURE GET_DONOR_FE
58 (p_ORDER_ID IN NUMBER
59 ,p_WI_INSTANCE_ID IN NUMBER
60 ,p_FA_INSTANCE_ID IN NUMBER
61 ,x_FE_NAME OUT NOCOPY VARCHAR2
62 ,x_RETURN_CODE OUT NOCOPY NUMBER
63 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
64 );
65
66 -- Get the adapter of the initial donor for current transaction.
67 --
68 PROCEDURE GET_ORIG_DONOR_FE
69 (p_ORDER_ID IN NUMBER
70 ,p_WI_INSTANCE_ID IN NUMBER
71 ,p_FA_INSTANCE_ID IN NUMBER
72 ,x_FE_NAME OUT NOCOPY VARCHAR2
73 ,x_RETURN_CODE OUT NOCOPY NUMBER
74 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
75 );
76
77 -- Get the adapter of the NRC for current transaction.
78 --
79 PROCEDURE GET_NRC_FE
80 (p_ORDER_ID IN NUMBER
81 ,p_WI_INSTANCE_ID IN NUMBER
82 ,p_FA_INSTANCE_ID IN NUMBER
83 ,x_FE_NAME OUT NOCOPY VARCHAR2
84 ,x_RETURN_CODE OUT NOCOPY NUMBER
85 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
86 );
87
88 -- Get the adapter of the Recipient SP for current transaction.
89 --
90 PROCEDURE GET_RECIPIENT_FE
91 (p_ORDER_ID IN NUMBER
92 ,p_WI_INSTANCE_ID IN NUMBER
93 ,p_FA_INSTANCE_ID IN NUMBER
94 ,x_FE_NAME OUT NOCOPY VARCHAR2
95 ,x_RETURN_CODE OUT NOCOPY NUMBER
96 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
97 );
98
99 -- Get the adapter of the Sedner for current transaction.
100 --
101 PROCEDURE GET_SENDER_FE
102 (p_ORDER_ID IN NUMBER
103 ,p_WI_INSTANCE_ID IN NUMBER
104 ,p_FA_INSTANCE_ID IN NUMBER
105 ,x_FE_NAME OUT NOCOPY VARCHAR2
106 ,x_RETURN_CODE OUT NOCOPY NUMBER
107 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
108 );
109
110
111 -- Logs an error message into XNP_DEBUG
112 --
113 PROCEDURE LOG_MSG
114 ( P_SEVERITY_LEVEL IN NUMBER
115 ,P_CONTEXT IN VARCHAR2
116 ,P_DESCRIPTION IN VARCHAR2
117 );
118
119 -- Converts dates to the canonical format.
120 -- The date string format should be 'YYYY/MM/DD HH24:MI:SS'
121 -- or 'YYYY/MM/DD'
122 --
123 FUNCTION CANONICAL_TO_DATE
124 (p_DATE_AS_CHAR VARCHAR2
125 )
126 RETURN DATE;
127
128 -- Converts dates to the canonical format chars.
129 -- Canonical format : 'YYYY/MM/DD' or 'YYYY/MM/DD HH24:MI:SS'
130 --
131 FUNCTION DATE_TO_CANONICAL
132 (p_DATE DATE
133 ,p_MASK_TYPE VARCHAR2 DEFAULT 'DATETIME'
134 )
135 RETURN VARCHAR2;
136
137 -- Get the name (SP code) of the donor for current transaction..
138 --
139 PROCEDURE GET_DONOR_NAME
140 (p_ORDER_ID IN NUMBER
141 ,p_WI_INSTANCE_ID IN NUMBER
142 ,p_FA_INSTANCE_ID IN NUMBER
143 ,x_RECIPIENT_NAME OUT NOCOPY VARCHAR2
144 ,x_RETURN_CODE OUT NOCOPY NUMBER
145 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
146 );
147
148 -- Get the name (SP code) of the sender for earlier message.
149 --
150 PROCEDURE GET_SENDER_NAME
151 (p_ORDER_ID IN NUMBER
152 ,p_WI_INSTANCE_ID IN NUMBER
153 ,p_FA_INSTANCE_ID IN NUMBER
154 ,x_RECIPIENT_NAME OUT NOCOPY VARCHAR2
155 ,x_RETURN_CODE OUT NOCOPY NUMBER
156 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
157 );
158
159 -- Get the name (SP code) of the initial donor for current transaction .
160 --
161 PROCEDURE GET_ORIG_DONOR_NAME
162 (p_ORDER_ID IN NUMBER
163 ,p_WI_INSTANCE_ID IN NUMBER
164 ,p_FA_INSTANCE_ID IN NUMBER
165 ,x_RECIPIENT_NAME OUT NOCOPY VARCHAR2
166 ,x_RETURN_CODE OUT NOCOPY NUMBER
167 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
168 );
169
170 -- Get the name (SP code) of the NRC for the current transaction.
171 --
172 PROCEDURE GET_NRC_NAME
173 (p_ORDER_ID IN NUMBER
174 ,p_WI_INSTANCE_ID IN NUMBER
175 ,p_FA_INSTANCE_ID IN NUMBER
176 ,x_RECIPIENT_NAME OUT NOCOPY VARCHAR2
177 ,x_RETURN_CODE OUT NOCOPY NUMBER
178 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
179 );
180
181 -- Get the name (SP code) of the Recipient SP for current transaction.
182 --
183 PROCEDURE GET_RECIPIENT_NAME
184 (p_ORDER_ID IN NUMBER
185 ,p_WI_INSTANCE_ID IN NUMBER
186 ,p_FA_INSTANCE_ID IN NUMBER
187 ,x_RECIPIENT_NAME OUT NOCOPY VARCHAR2
188 ,x_RETURN_CODE OUT NOCOPY NUMBER
189 ,x_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
190 );
191
192 -- Gets the FE Name for the given SP ID.
193 --
194 PROCEDURE GET_FE_NAME_FOR_SP
195 (p_SP_ID NUMBER
196 ,x_FE_NAME OUT NOCOPY VARCHAR2
197 ,x_ERROR_CODE OUT NOCOPY NUMBER
198 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
199 );
200
201 -- Gets the FE Name for the given SP ID.
202 --
203 PROCEDURE GET_FE_NAME
204 (p_SP_ID NUMBER
205 ,x_FE_NAME OUT NOCOPY VARCHAR2
206 ,x_ERROR_CODE OUT NOCOPY NUMBER
207 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
208 );
209
210 -- Gets the FE Name for the given FE ID.
211 --
212 PROCEDURE GET_FE_NAME
213 (p_FE_ID NUMBER
214 ,x_FE_NAME OUT NOCOPY VARCHAR2
215 ,x_ERROR_CODE OUT NOCOPY NUMBER
216 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
217 );
218
219 -- Get the Adapter Name for the given SP ID.
220 --
221 PROCEDURE GET_ADAPTER_NAME
222 (p_SP_ID NUMBER
223 ,x_ADAPTER_NAME OUT NOCOPY VARCHAR2
224 ,x_ERROR_CODE OUT NOCOPY NUMBER
225 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
226 );
227
228 -- Execute dynamic SQL for message create.
229 --
230 PROCEDURE EXEC_DYNAMIC_CREATE_MSG
231 (P_DYNAMIC_MSG_TEXT IN VARCHAR2
232 ,X_MSG_TEXT OUT NOCOPY VARCHAR2
233 ,X_ERROR_CODE OUT NOCOPY NUMBER
234 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
235 );
236
237 -- Execute dynamic SQL for message send and publish.
238 --
239 PROCEDURE EXEC_DYNAMIC_SEND_PUBLISH
240 (P_DYNAMIC_MSG_TEXT IN VARCHAR2
241 ,X_MSG_ID OUT NOCOPY NUMBER
242 ,X_ERROR_CODE OUT NOCOPY NUMBER
243 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
244 );
245
246 -- Utility to notify errors in the NP workflow activities.
247 -- Takes a mesg name and upto 3 tokens and values for each.
248 -- Internally the STARTING_NUMBER and ENDING_NUMBER
249 -- tokens are set.
250 -- Note: the defined message must have STARTING_NUMBER
251 -- and ENDING_NUMBER defined as tokens.
252 --
253 PROCEDURE NOTIFY_ERROR
254 (P_PKG_NAME VARCHAR2
255 ,P_PROC_NAME VARCHAR2
256 ,P_MSG_NAME VARCHAR2
257 ,P_WORKITEM_INSTANCE_ID NUMBER
258 ,P_TOK1 VARCHAR2 DEFAULT NULL
259 ,P_VAL1 VARCHAR2 DEFAULT NULL
260 ,P_TOK2 VARCHAR2 DEFAULT NULL
261 ,P_VAL2 VARCHAR2 DEFAULT NULL
262 ,P_TOK3 VARCHAR2 DEFAULT NULL
263 ,P_VAL3 VARCHAR2 DEFAULT NULL
264 );
265 --
266 -- Gets the WF instance by parsing the reference ID
267 -- into its constituent item type, key and activity.
268 -- Usage Notes: This is done from the process refernce in the format
269 -- "itemtype:itemkey:activitylabel" or any string in the
270 -- above format. The string is parsed and the 3 tokens delimited
271 -- by ':' are returned.
272 --
273 PROCEDURE GET_WF_INSTANCE
274 (
275 P_PROCESS_REFERENCE IN VARCHAR2
276 ,X_WF_TYPE OUT NOCOPY VARCHAR2
277 ,X_WF_KEY OUT NOCOPY VARCHAR2
278 ,X_WF_ACTIVITY OUT NOCOPY VARCHAR2
279 );
280 --
281 -- Utility to get the associated Workitem instance ID.
282 -- for the reference ID in the message.
283 -- This can be used in the processing logic for a
284 -- REGISTERED message to the workitem instance ID
285 -- of the associated workflow. This way, the processing
286 -- logic gets a handle to all the context information
287 -- of the workflow.
288 -- Procedure the process reference against the reference_ID
289 -- from xnp_callback_events. Then looks up the WF to
290 -- get the item attribute WORKITEM_INSTANCE_ID
291 --
292 PROCEDURE GET_WORKITEM_INSTANCE_ID
293 (p_reference_id VARCHAR2
294 ,x_workitem_instance_id OUT NOCOPY NUMBER
295 ,x_error_code OUT NOCOPY NUMBER
296 ,x_error_message OUT NOCOPY VARCHAR2
297 );
298 --
299 -- Procedure Get the PORTING_ID from the body text
300 -- of the message and overwrites the REFERENCE_ID
301 -- with this value
302 -- For the subsequent transactions, the PORITNG_ID's
303 -- value will be the REFERENCE_ID for this transaction
304 -- Note: Not to be used! The reseting of the
305 -- reference ID must be done in the adapter only
306 --
307 PROCEDURE RESET_REFERENCE_ID
308 (
309 p_msg_header IN OUT NOCOPY XNP_MESSAGE.MSG_HEADER_REC_TYPE,
310 p_msg_text IN VARCHAR2,
311 x_error_code OUT NOCOPY NUMBER,
312 x_error_message OUT NOCOPY VARCHAR2 );
313 --
314 -- Get the reference id value based on the user choice.
315 -- and is used in the workflow function
316 -- Gets the value in CALLBACK_REF_ID_NAME activity attribute
317 -- if the value is NOT CUSTOM
318 -- then it refers to a WI param name so the value is
319 -- got from there if the value IS CUSTOM
320 -- then the actual value is got from the CUSTOM_CALLBACK_REFERENCE_ID
321 -- activity attrbute and directly returned.
322 --
323 --
324 PROCEDURE CHECK_TO_GET_REF_ID
325 (p_itemtype in varchar2
326 ,p_itemkey in varchar2
327 ,p_actid in number
328 ,p_workitem_instance_id in number
329 ,x_reference_id OUT NOCOPY varchar2
330 );
331 --
332 -- Copies the workitem parameter value from source to destination.
333 -- Usage Notes: Must be used incase of a modify
334 -- order to keep multiple workitem context information
335 -- in sync.
336 --
337 PROCEDURE COPY_WI_PARAM_VALUE
338 (p_src_wi_id number
339 ,p_dest_wi_id number
340 ,p_param_name varchar2
341 );
342 --
343 -- Copies all WI parameter values from the source to destination.
344 -- Usage Notes: Must be used incase of a modify
345 -- order to keep multiple workitem context information
346 -- in sync.
347 --
348 PROCEDURE COPY_ALL_WI_PARAMS
349 (p_src_wi_id number
350 ,p_dest_wi_id number
351 );
352 --
353 -- Utility used by PREPARE_CUSTOM_NOTIFICATION workflow activity.
354 -- All the tokens in the MLS message are replaced by
355 -- the values as in the workitem parameters
356 -- The first line in the MLS message is returned in x_subject
357 -- while the whole message with the tokens interepreted
358 -- is returned in the x_body.
359 -- Note:
360 -- 1. Ensure that each workitem parameter (token) has
361 -- atleast 1 space after it
362 -- 2. The subject will be the character after the first new line
363 --
364 PROCEDURE GET_INTERPRETED_NOTIFICATION
365 (p_workitem_instance_id number
366 ,p_mls_message_name varchar2
367 ,x_subject OUT NOCOPY varchar2
368 ,x_body OUT NOCOPY varchar2
369 ,x_error_code OUT NOCOPY number
370 ,x_error_message OUT NOCOPY varchar2
371 );
372 --
373 -- Gets the Adapter for the given FE.
374 --
375 FUNCTION GET_ADAPTER_USING_FE
376 (
377 p_fe_name IN VARCHAR2
378 )
379 RETURN VARCHAR2 ;
380
381 PRAGMA RESTRICT_REFERENCES(get_adapter_using_fe, WNDS);
382 --
383 --
384 -- Checks if the number range exists.
385 --
386 PROCEDURE CHECK_IF_NUM_RANGE_EXISTS
387 (p_STARTING_NUMBER IN VARCHAR2
388 ,p_ENDING_NUMBER IN VARCHAR2
389 ,p_NUMBER_RANGE_ID IN NUMBER
390 );
391
392 END XNP_UTILS;
[Home] [Help]