[Home] [Help]
PACKAGE BODY: APPS.M4U_DMD_UTILS
Source
1 PACKAGE BODY m4u_dmd_utils AS
2 /* $Header: M4UDUTLB.pls 120.1 2007/07/17 07:08:31 bsaratna noship $ */
3
4 PROCEDURE handle_error(
5 p_err_api IN VARCHAR2,
6 p_err_msg IN VARCHAR2,
7 p_sql_cod IN VARCHAR2,
8 p_sql_err IN VARCHAR2,
9 x_ret_sts OUT NOCOPY VARCHAR2,
10 x_ret_msg OUT NOCOPY VARCHAR2)
11 IS
12 BEGIN
13
14 log('Error api/msg - ' || p_err_api || '/' || p_err_msg,6);
15 log('Error cod/err - ' || p_sql_cod || '/' || p_sql_err,6);
16
17 IF p_err_msg IS NOT NULL THEN
18 x_ret_msg := substr(p_err_msg,1,400);
19 ELSE
20 fnd_message.set_name('CLN','M4U_DMD_API_FAIL');
21 fnd_message.set_name('API' ,nvl(p_err_api,'M4U'));
22 fnd_message.set_name('ERRM',substr(p_sql_cod || '-' || p_sql_err,1,400));
23 x_ret_msg := fnd_message.get;
24 END IF;
25
26 x_ret_sts := fnd_api.g_ret_sts_error;
27 RETURN;
28 EXCEPTION
29 WHEN OTHERS THEN
30 x_ret_sts := fnd_api.g_ret_sts_error;
31 x_ret_msg := 'Unexpected error - ' || p_err_api;
32 RETURN;
33 END handle_error;
34
35 PROCEDURE log
36 (
37 p_stmt IN VARCHAR2,
38 p_level IN NUMBER
39 )
40 AS
41 BEGIN
42 -- TBD: replace with fnd api
43 -- TBD: check module log level
44 -- cln_debug_pub.add(substr(p_log_stmt,1,255),1);
45 IF p_level <= 6 THEN
46 cln_debug_pub.add(p_stmt,6);
47 END IF;
48
49 IF p_level >= fnd_log.g_current_runtime_level THEN
50 fnd_log.string
51 (
52 log_level => p_level,
53 module => 'CLN.M4UD.PLSQL.',
54 message => p_stmt
55 );
56 cln_debug_pub.add(p_stmt,p_level);
57 END IF;
58
59 EXCEPTION
60 WHEN OTHERS THEN
61 null; -- gobble exception
62 END log;
63
64 FUNCTION valid_gln
65 (
66 p_gln IN VARCHAR2,
67 p_null_allowed IN BOOLEAN := true
68 )
69 RETURN BOOLEAN IS
70 len1 NUMBER;
71 len2 NUMBER;
72 tmp NUMBER;
73 BEGIN
74 IF p_gln IS NULL and p_null_allowed THEN
75 RETURN true;
76 ELSE
77 len1 := length(p_gln);
78 len2 := length(translate(p_gln,'0123456789.^+-','0123456789'));
79 tmp := to_number(p_gln);
80 IF len1 = len2 AND len1 = c_gln_len THEN
81 RETURN true;
82 END IF;
83 END IF;
84
85 RETURN false;
86
87 EXCEPTION
88 WHEN OTHERS THEN
89 RETURN false;
90 END;
91
92 FUNCTION valid_gtin
93 (
94 p_gtin IN VARCHAR2,
95 p_null_allowed IN BOOLEAN := true
96 )
97 RETURN BOOLEAN IS
98 len1 NUMBER;
99 len2 NUMBER;
100 tmp NUMBER;
101 BEGIN
102 IF p_gtin IS NULL and p_null_allowed THEN
103 RETURN true;
104 ELSE
105 len1 := length(p_gtin);
106 len2 := length(translate(p_gtin,'0123456789.+-^','0123456789'));
107 tmp := to_number(p_gtin);
108 IF len1 = len2 AND len1 = c_gtin_len THEN
109 RETURN true;
110 END IF;
111 END IF;
112
113 RETURN false;
114
115 EXCEPTION
116 WHEN OTHERS THEN
117 RETURN false;
118 END valid_gtin;
119
120 FUNCTION date_xml_to_db
121 (
122 p_datetime IN VARCHAR2
123 ) RETURN DATE
124 AS
125 BEGIN
126 RETURN to_date(translate(p_datetime,'0123456789T:-','0123456789'),'YYYYMMDDHH24MISS');
127 EXCEPTION
128 WHEN OTHERS THEN
129 RAISE;
130 END date_xml_to_db;
131
132 FUNCTION valid_type
133 (
134 p_param IN VARCHAR2,
135 p_value IN VARCHAR2,
136 p_null_allowed IN BOOLEAN := true
137 ) RETURN BOOLEAN
138 AS
139 BEGIN
140 IF p_null_allowed AND p_value IS NULL THEN
141 RETURN true;
142 END IF;
143
144 IF p_param = 'DOC_STATUS' AND
145 p_value IN (c_sts_ready,c_sts_in_process,c_sts_sent,c_sts_success,
146 c_sts_fail,c_sts_error )THEN
147 RETURN true;
148 END IF;
149
150 IF p_param = 'MSG_STATUS' AND
151 p_value IN (c_sts_ready,c_sts_sent,c_sts_in_process,c_sts_success,
152 c_sts_fail,c_sts_error )THEN
153 RETURN true;
154 END IF;
155
156 IF p_param = 'PAYLOAD_TYPE' AND
157 p_value IN (c_type_cin,c_type_rfcin,c_type_cic,c_type_cis,
158 c_type_cin_ack,c_type_cis_ack,c_type_cic_ack,c_type_rfcin_ack,
159 c_type_item_ebm,c_type_resp_ebm)THEN
160 RETURN true;
161 END IF;
162
163 IF p_param = 'DOC_TYPE' AND
164 p_value IN (c_type_cin,c_type_rfcin,
165 c_type_cic,c_type_cis)THEN
166 RETURN true;
167 END IF;
168
169 IF p_param = 'MSG_TYPE' AND
170 p_value IN (c_type_cin,c_type_cin_ack,c_type_rfcin,
171 c_type_cic,c_type_cis)THEN
172 RETURN true;
173 END IF;
174
175 IF p_param = 'ACTION' AND
176 p_value IN (c_action_add,c_action_delete,c_action_accepted,
177 c_action_rejected,c_action_sync,c_action_review,
178 c_action_new,c_action_init_load,c_action_modify,
179 c_action_correct)THEN
180 RETURN true;
181 END IF;
182
183 IF p_param = 'DIRECTION' AND
184 p_value IN (c_dir_out,c_dir_in)THEN
185 RETURN true;
186 END IF;
187
188 IF p_param = 'RETRY_MODE' AND
189 p_value IN (c_retry_all,c_retry_err,c_retry_timeout)THEN
190 RETURN true;
191 END IF;
192
193 RETURN false;
194
195 END valid_type;
196
197 FUNCTION valid_len
198 (
199 p_value IN VARCHAR2,
200 p_min_len IN NUMBER,
201 p_max_len IN NUMBER,
202 p_null_allowed IN BOOLEAN := false
203 ) RETURN BOOLEAN
204 AS
205 BEGIN
206 IF p_null_allowed AND p_value IS NULL THEN
207 RETURN true;
208 ELSIF length(p_value) >= p_min_len AND
209 length(p_value) <= p_max_len THEN
210 RETURN true;
211 END IF;
212 RETURN false;
213 EXCEPTION
214 WHEN OTHERS THEN
215 RETURN false;
216 END valid_len;
217
218 FUNCTION valid_payload_id
219 (
220 p_payload_id IN VARCHAR2,
221 p_null_allowed IN BOOLEAN := true
222 ) RETURN BOOLEAN
223 AS
224 l_count NUMBER;
225 BEGIN
226 IF p_null_allowed AND p_payload_id IS NULL THEN
227 RETURN true;
228 END IF;
229
230 SELECT count(*)
231 INTO l_count
232 FROM m4u_dmd_payloads
233 WHERE payload_id = p_payload_id;
234
235 IF l_count = 1 THEN
236 RETURN true;
237 END IF;
238 RETURN false;
239 EXCEPTION
240 WHEN OTHERS THEN
241 RETURN false;
242 END valid_payload_id;
243
244 FUNCTION valid_doc_id
245 (
246 p_doc_id IN VARCHAR2,
247 p_null_allowed IN BOOLEAN := true
248 ) RETURN BOOLEAN
249 AS
250 l_count NUMBER;
251 BEGIN
252 IF p_null_allowed AND p_doc_id IS NULL THEN
253 RETURN true;
254 END IF;
255
256 SELECT count(*)
257 INTO l_count
258 FROM m4u_dmd_documents
259 WHERE doc_id = p_doc_id;
260
261 IF l_count = 1 THEN
262 RETURN true;
263 END IF;
264 RETURN false;
265 EXCEPTION
266 WHEN OTHERS THEN
267 RETURN false;
268 END valid_doc_id;
269
270 FUNCTION valid_msg_id
271 (
272 p_msg_id IN VARCHAR2,
273 p_null_allowed IN BOOLEAN := true
274 ) RETURN BOOLEAN
275 AS
276 l_count NUMBER;
277 BEGIN
278 IF p_null_allowed AND p_msg_id IS NULL THEN
279 RETURN true;
280 END IF;
281
282 SELECT count(*)
283 INTO l_count
284 FROM m4u_dmd_messages
285 WHERE msg_id = p_msg_id;
286
287 IF l_count = 1 THEN
288 RETURN true;
289 END IF;
290 RETURN false;
291 EXCEPTION
292 WHEN OTHERS THEN
293 RETURN false;
294 END valid_msg_id;
295
296 FUNCTION valid_orig_doc_id
297 (
298 p_orig_doc_id IN VARCHAR2,
299 p_null_allowed IN BOOLEAN := true
300 ) RETURN BOOLEAN
301 AS
302 l_count NUMBER;
303 BEGIN
304 IF p_null_allowed AND p_orig_doc_id IS NULL THEN
305 RETURN true;
306 END IF;
307
308 SELECT count(*)
309 INTO l_count
310 FROM m4u_dmd_documents
311 WHERE orig_doc_id = p_orig_doc_id;
312
313 IF l_count = 1 THEN
314 RETURN true;
315 END IF;
316 RETURN false;
317 EXCEPTION
318 WHEN OTHERS THEN
319 RETURN false;
320 END valid_orig_doc_id;
321
322 FUNCTION valid_orig_msg_id
323 (
324 p_orig_msg_id IN VARCHAR2,
325 p_null_allowed IN BOOLEAN := true
326 ) RETURN BOOLEAN
327 AS
328 l_count NUMBER;
329 BEGIN
330 IF p_null_allowed AND p_orig_msg_id IS NULL THEN
331 RETURN true;
332 END IF;
333
334 SELECT count(*)
335 INTO l_count
336 FROM m4u_dmd_messages
337 WHERE orig_msg_id = p_orig_msg_id;
338
339 IF l_count = 1 THEN
340 RETURN true;
341 END IF;
342 RETURN false;
343 EXCEPTION
344 WHEN OTHERS THEN
345 RETURN false;
346 END valid_orig_msg_id;
347
348
349 FUNCTION get_inv_param_msg
350 (
351 p_api IN VARCHAR2,
352 p_param IN VARCHAR2,
353 p_value IN VARCHAR2
354 ) RETURN VARCHAR2
355 AS
356 BEGIN
357
358 log('Validation failure in api -|' || p_api || '|',6);
359 log(' param -|' || p_param || '|',6);
360 log(' value -|' || p_value || '|',6);
361
362
363 fnd_message.set_name ('CLN', 'M4U_DMD_INV_PARAM_VALUE');
364 fnd_message.set_token('API' ,nvl(p_api,'M4U'));
365 fnd_message.set_token('PARAM' ,nvl(p_param,'UNKNOWN'));
366 fnd_message.set_token('VALUE' ,nvl(p_value,'null'));
367 RETURN fnd_message.get;
368
369 EXCEPTION
370 WHEN OTHERS THEN
371 RETURN 'Invalid input for - ' || p_param;
372 END get_inv_param_msg;
373
374 FUNCTION get_gln_user
375 (
376 p_gln IN VARCHAR2
377 ) RETURN VARCHAR2
378 AS
379 l_user_id VARCHAR2(100);
380 BEGIN
381 SELECT meaning
382 INTO l_user_id
383 FROM fnd_lookup_values
384 WHERE lookup_type = 'M4U_USER_GLNS'
385 AND language = userenv('lang')
386 AND lookup_code = p_gln;
387
388 RETURN l_user_id;
389 EXCEPTION
390 WHEN OTHERS THEN
391 --RAISE;
392 RETURN p_gln;
393 END get_gln_user;
394
395 END m4u_dmd_utils;