[Home] [Help]
PACKAGE BODY: APPS.M4U_UCC_UTILS
Source
1 PACKAGE BODY m4u_ucc_utils AS
2 /* $Header: m4uutilb.pls 120.4 2006/07/13 10:43:44 bsaratna noship $ */
3 l_debug_level NUMBER;
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'm4u_ucc_utils';
5
6 -- converts oracle date into cXML date format
7 PROCEDURE convert_to_uccnet_date ( p_ora_date IN DATE,
8 x_ucc_date OUT NOCOPY VARCHAR2
9 )
10 IS
11
12 l_year varchar2(200);
13 l_month varchar2(200);
14 l_day varchar2(200);
15 BEGIN
16
17 IF (p_ora_date IS NOT NULL)
18 THEN
19 l_year := to_char(p_ora_date, 'YYYY');
20 l_month := to_char(p_ora_date, 'MM');
21 l_day := to_char(p_ora_date, 'DD');
22
23 x_ucc_date := l_year || '-' || l_month || '-' || l_day;
24 ELSE
25 x_ucc_date := NULL;
26 END IF;
27 EXCEPTION
28 WHEN OTHERS THEN
29 raise ;
30 end convert_to_uccnet_date;
31
32 -- converts oracle date into cXML datetime format
33 PROCEDURE convert_to_uccnet_datetime ( p_ora_date IN DATE,
34 x_ucc_date OUT NOCOPY VARCHAR2
35 )
36 IS
37 l_ora_date varchar2(200);
38 l_year varchar2(200);
39 l_month varchar2(200);
40 l_day varchar2(200);
41 l_hour varchar2(200);
42 l_min varchar2(200);
43 l_sec varchar2(200);
44 BEGIN
45 l_ora_date := to_char(p_ora_date, 'YYYYMMDD HH24MISS');
46
47 IF (l_ora_date IS NOT NULL)
48 THEN
49 l_year := to_char(p_ora_date, 'YYYY');
50 l_month := to_char(p_ora_date, 'MM');
51 l_day := to_char(p_ora_date, 'DD');
52 l_hour := to_char(p_ora_date, 'HH');
53 l_min := to_char(p_ora_date, 'MI');
54 l_sec := to_char(p_ora_date, 'SS');
55 x_ucc_date := l_year || '-' || l_month || '-' || l_day || 'T' || l_hour || ':' ||
56 l_min || ':' || l_sec;
57 ELSE
58 x_ucc_date := NULL;
59 END IF;
60 EXCEPTION
61 WHEN OTHERS THEN
62 raise ;
63 END convert_to_uccnet_datetime;
64
65
66 FUNCTION get_lookup_meaning( p_lookup_type VARCHAR2, p_lookup_code VARCHAR)
67 RETURN VARCHAR2
68 IS
69 v_meaning fnd_lookups.meaning%TYPE;
70 BEGIN
71 SELECT meaning INTO v_meaning
72 FROM fnd_lookups
73 WHERE lookup_type = p_lookup_type
74 AND lookup_code = p_lookup_code
75 AND ROWNUM =1 ;
76 RETURN v_meaning;
77
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
80 RETURN null;
81 WHEN OTHERS THEN
82 RETURN null; -- lets not raise errors here, it will stop the XML Generation
83 END;
84
85 FUNCTION get_time RETURN VARCHAR2
86 IS
87 l_time VARCHAR2(50);
88 BEGIN
89 l_time := to_char(sysdate, 'HH24:MI:SS');
90 RETURN l_time;
91 EXCEPTION
92 WHEN OTHERS THEN
93 RETURN NULL;-- don't want to stop XMLG generation due to this
94 END;
95
96 FUNCTION get_time_zone RETURN VARCHAR2
97 IS
98 l_time_zone VARCHAR2(50);
99 BEGIN
100 l_time_zone := 'EST';
101 RETURN l_time_zone;
102 EXCEPTION
103 WHEN OTHERS THEN
104 RETURN NULL;-- don't want to stop XMLG generation due to this
105 END;
106
107 FUNCTION get_sys_date RETURN VARCHAR2
108 IS
109 l_date VARCHAR2(50);
110 BEGIN
111 l_date := to_char(sysdate, 'YYYY-MM-DD');
112 RETURN l_date;
113 EXCEPTION
114 WHEN OTHERS THEN
115 RETURN NULL; -- don't want to stop XMLG generation due to this
116 END;
117
118 FUNCTION get_guid RETURN VARCHAR2
119 IS
120 l_guid VARCHAR2(400);
121 BEGIN
122 l_guid := SYS_GUID();
123 RETURN l_guid;
124 EXCEPTION
125 WHEN OTHERS THEN
126 RETURN NULL; -- continue if guid fail (not likey!)
127 END;
128
129
130 -- calculate the status type of an item based on the difference between dates if provided
131 PROCEDURE process_catalogue_item_status(
132 p_cancel_date IN DATE,
133 p_discontinue_date IN DATE,
134 x_catalogue_item_status OUT NOCOPY VARCHAR2
135 )
136 IS
137 l_current_date DATE;
138 l_cancel_date DATE;
139 l_discontinue_date DATE;
140
141 l_day NUMBER;
142 BEGIN
143 IF (l_debug_level <= 2) THEN
144 cln_debug_pub.Add('M4U:----- Entering process_catalogue_item_status ------- ',2);
145 END IF;
146
147 -- By Default, the status type is REGISTERED
148 x_catalogue_item_status := 'REGISTERED';
149
150 -- If cancel date is not null
151 IF (p_cancel_date IS NOT NULL) THEN
152 l_current_date := to_date(to_char(SYSDATE, 'YYYY-MM-DD'),'YYYY-MM-DD');
153 l_cancel_date := to_date(to_char(p_cancel_date, 'YYYY-MM-DD'),'YYYY-MM-DD');
154
155 IF (l_debug_level <= 1) THEN
156 cln_debug_pub.Add('Current Date -> '||l_current_date,1);
157 cln_debug_pub.Add('Cancel Date -> '||l_cancel_date,1);
158 END IF;
159
160 l_day := l_cancel_date - l_current_date;
161 IF (l_debug_level <= 1) THEN
162 cln_debug_pub.Add('Days Difference -> '||l_day,1);
163 END IF;
164
165 --IF (l_day = 0) THEN /*bug5368180*/
166 IF (l_day <= 0) THEN
167 x_catalogue_item_status := 'CANCELED';
168 END IF;
169
170 IF (l_debug_level <= 1) THEN
171 cln_debug_pub.Add('p_catalogue_item_status -> '||x_catalogue_item_status,1);
172 END IF;
173
174
175 IF (l_debug_level <= 2) THEN
176 cln_debug_pub.Add('M4U:----- Exiting process_catalogue_item_status ------- ',2);
177 END IF;
178
179 RETURN;
180 END IF;
181
182 -- If discontinue date is not null
183 IF (p_discontinue_date IS NOT NULL) THEN
184 l_current_date := to_date(to_char(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD');
185 l_discontinue_date := to_date(to_char(p_discontinue_date, 'YYYY-MM-DD'),'YYYY-MM-DD');
186
187 IF (l_debug_level <= 1) THEN
188 cln_debug_pub.Add('Current Date -> '||l_current_date,1);
189 cln_debug_pub.Add('Discontinue Date -> '||l_discontinue_date,1);
190 END IF;
191
192 l_day := l_discontinue_date - l_current_date;
193 IF (l_debug_level <= 1) THEN
194 cln_debug_pub.Add('Days Difference -> '||l_day,1);
195 END IF;
196
197 --IF (l_day = 0) THEN /*bug5368180*/
198 IF (l_day <= 0) THEN
199 x_catalogue_item_status := 'DISCONTINUED';
200 END IF;
201
202 IF (l_debug_level <= 1) THEN
203 cln_debug_pub.Add('p_catalogue_item_status -> '||x_catalogue_item_status,1);
204 END IF;
205
206
207 IF (l_debug_level <= 2) THEN
208 cln_debug_pub.Add('M4U:----- Exiting process_catalogue_item_status ------- ',2);
209 END IF;
210
211 RETURN;
212 END IF;
213 EXCEPTION
214 WHEN OTHERS THEN
215 IF (l_debug_level <= 6) THEN
216 cln_debug_pub.Add('M4U:----- Exiting process_catalogue_item_status ERROR------- ',6);
217 END IF;
218
219 raise ;
220 END process_catalogue_item_status;
221
222 -- Name
223 -- CONVERT_TO_DATE
224 -- Purpose
225 -- This is internal procedure to convert the string into a date
226 --
227 FUNCTION CONVERT_TO_DATE(
228 p_string VARCHAR2
229 ) RETURN DATE
230 IS
231 l_date DATE;
232 BEGIN
233 l_date := NULL;
234 BEGIN
235 l_date := to_date(p_string,'YYYY/MM/DD HH24:MI:SS');
236 EXCEPTION
237 WHEN OTHERS THEN
238 BEGIN
239 l_date := to_date(p_string,'YYYY/MM/DD');
240 EXCEPTION
241 WHEN OTHERS THEN
242 l_date := NULL;
243 END;
244 END;
245 RETURN l_date;
246 END;
247
248
249
250 -- change the value of the input string as per xslt processing
251 PROCEDURE format_industry_ext_string(
252 p_industry_column IN VARCHAR2,
253 x_mutiple_industry_ext OUT NOCOPY VARCHAR2
254 )
255 IS
256
257 countchk NUMBER;
258 strlength NUMBER;
259 industry_var VARCHAR2(3);
260 BEGIN
261 IF (l_debug_level <= 2) THEN
262 cln_debug_pub.Add('M4U:----- Entering format_industry_ext_string ------- ',2);
263 END IF;
264
265 IF (l_debug_level <= 1) THEN
266 cln_debug_pub.Add('Industry Column value = '||p_industry_column,1);
267 END IF;
268
269 IF(p_industry_column IS NULL) THEN
270 IF (l_debug_level <= 1) THEN
271 cln_debug_pub.Add('Industry Column value is null',1);
272 END IF;
273 RETURN;
274 END IF;
275
276 strlength := length(p_industry_column);
277 IF (l_debug_level <= 1) THEN
278 cln_debug_pub.Add('Length of the Industry Column String '||strlength,1);
279 END IF;
280
281 countchk := 1;
282
283 LOOP
284 industry_var :=substr(p_industry_column,countchk,1);
285
286 IF (l_debug_level <= 1) THEN
287 cln_debug_pub.Add('Industry Ext Variable - '||industry_var,1);
288 END IF;
289
290 IF ( industry_var ='f')THEN
291 IF(length(x_mutiple_industry_ext) <>0) THEN
292 x_mutiple_industry_ext := x_mutiple_industry_ext||':';
293 END IF;
294
295 x_mutiple_industry_ext := x_mutiple_industry_ext||'fmcg';
296
297 IF (l_debug_level <= 1) THEN
298 cln_debug_pub.Add('Industry Ext value - '||x_mutiple_industry_ext,1);
299 END IF;
300 END IF;
301
302 IF ( industry_var ='h')THEN
303 IF(length(x_mutiple_industry_ext) <>0) THEN
304 x_mutiple_industry_ext := x_mutiple_industry_ext||':';
305 END IF;
306
307 x_mutiple_industry_ext := x_mutiple_industry_ext||'hardlines';
308
309 IF (l_debug_level <= 1) THEN
310 cln_debug_pub.Add('Industry Ext value - '||x_mutiple_industry_ext,1);
311 END IF;
312 END IF;
313
314 IF ( industry_var ='s')THEN
315 IF(length(x_mutiple_industry_ext) <>0) THEN
316 x_mutiple_industry_ext := x_mutiple_industry_ext||':';
317 END IF;
318
319 x_mutiple_industry_ext := x_mutiple_industry_ext||'sbdh';
320 IF (l_debug_level <= 1) THEN
321 cln_debug_pub.Add('Industry Ext value - '||x_mutiple_industry_ext,1);
322 END IF;
323 END IF;
324
325 countchk := countchk+2;
326 IF (l_debug_level <= 1) THEN
327 cln_debug_pub.Add('Count Check'||countchk,1);
328 END IF;
329 EXIT WHEN countchk > strlength;
330 END LOOP;
331
332 IF (l_debug_level <= 2) THEN
333 cln_debug_pub.Add('M4U:----- Exiting format_industry_ext_string ------- ',2);
334 END IF;
335
336 EXCEPTION
337 WHEN OTHERS THEN
338 IF (l_debug_level <= 6) THEN
339 cln_debug_pub.Add('M4U:----- Exiting format_industry_ext_string ERROR------- ',6);
340 END IF;
341
342 raise ;
343 END format_industry_ext_string;
344
345
346
347 FUNCTION validate_checkdigit_gtin(
348 p_gtin IN VARCHAR2
349 )RETURN BOOLEAN
350 IS
351 l_right_sum NUMBER;
352 l_left_sum NUMBER;
353 l_con_sum NUMBER;
354 l_calculatedCheckDigit NUMBER;
355 l_remainder NUMBER;
356 l_checkDigit NUMBER;
357 l_error_code VARCHAR2(50);
358 l_error_msg VARCHAR2(200);
359
360 BEGIN
361 IF (l_debug_level <= 2) THEN
362 cln_debug_pub.Add('M4U:----- Entering validate_checkdigit_gtin ------- ',2);
363 END IF;
364
365 l_checkDigit:= TO_NUMBER(SUBSTR(p_gtin,14,1 ));
366
367 IF (l_debug_level <= 1) THEN
368 cln_debug_pub.Add('M4U: Checksum Attached : '||l_checkDigit,1);
369 END IF;
370
371
372 l_right_sum := TO_NUMBER(SUBSTR(p_gtin,1,1 ))+
373 TO_NUMBER(SUBSTR(p_gtin,3,1 ))+
374 TO_NUMBER(SUBSTR(p_gtin,5,1 ))+
375 TO_NUMBER(SUBSTR(p_gtin,7,1 ))+
376 TO_NUMBER(SUBSTR(p_gtin,9,1 ))+
377 TO_NUMBER(SUBSTR(p_gtin,11,1 ))+
378 TO_NUMBER(SUBSTR(p_gtin,13,1 ));
379 IF (l_debug_level <= 1) THEN
380 cln_debug_pub.Add('M4U: Sum Of Odd Numbers: '||l_right_sum,1);
381 END IF;
382
383
384 l_left_sum := TO_NUMBER(SUBSTR(p_gtin,2,1 ))+
385 TO_NUMBER(SUBSTR(p_gtin,4,1 ))+
386 TO_NUMBER(SUBSTR(p_gtin,6,1 ))+
387 TO_NUMBER(SUBSTR(p_gtin,8,1 ))+
388 TO_NUMBER(SUBSTR(p_gtin,10,1 ))+
389 TO_NUMBER(SUBSTR(p_gtin,12,1 ));
390
391 IF (l_debug_level <= 1) THEN
392 cln_debug_pub.Add('M4U: Sum Of Even Numbers: '||l_left_sum,1);
393 END IF;
394
395 l_con_sum := l_right_sum*3 + l_left_sum;
396 IF (l_debug_level <= 1) THEN
397 cln_debug_pub.Add('M4U: consolidated sum : '||l_con_sum,1);
398 END IF;
399
400 l_remainder := Mod(l_con_sum,10);
401 IF (l_debug_level <= 1) THEN
402 cln_debug_pub.Add('M4U: Remainder : '||l_remainder,1);
403 END IF;
404
405 IF (l_remainder = 0) THEN
406 l_remainder := 10;
407 END IF;
408
409 l_calculatedCheckDigit := 10 - l_remainder;
413
410 IF (l_debug_level <= 1) THEN
411 cln_debug_pub.Add('M4U: Calculated Checksum: '||l_calculatedCheckDigit,1);
412 END IF;
414 IF (l_checkDigit <> l_calculatedCheckDigit) THEN
415 IF (l_debug_level <= 1) THEN
416 cln_debug_pub.Add('M4U: Incorrect Checksum',1);
417 cln_debug_pub.Add('------- Exiting validate_checkdigit_gtin - ',1);
418 END IF;
419
420 RETURN FALSE;
421 END IF;
422
423 IF (l_debug_level <= 2) THEN
424 cln_debug_pub.Add('------- Exiting validate_checkdigit_gtin --------- ',2);
425 END IF;
426
427 RETURN TRUE;
428 EXCEPTION
429 WHEN OTHERS THEN
430 l_error_code :=SQLCODE;
431 l_error_msg :=SQLERRM;
432
433 IF (l_debug_level <= 5) THEN
434 cln_debug_pub.Add(' :: '||l_error_code||' :: '||l_error_msg,5);
435 END IF;
436
437 IF (l_debug_level <= 2) THEN
438 cln_debug_pub.Add('------- Exiting validate_checkdigit_gtin - Exception --------- ',2);
439 END IF;
440 RETURN FALSE;
441 END;
442
443
444 FUNCTION validate_checkdigit_gln(
445 p_gln IN VARCHAR2
446 )RETURN BOOLEAN
447 IS
448 l_right_sum NUMBER;
449 l_left_sum NUMBER;
450 l_con_sum NUMBER;
451 l_calculatedCheckDigit NUMBER;
452 l_remainder NUMBER;
453 l_checkDigit NUMBER;
454 l_error_code VARCHAR2(50);
455 l_error_msg VARCHAR2(200);
456
457 BEGIN
458 IF (l_debug_level <= 2) THEN
459 cln_debug_pub.Add('M4U:----- Entering validate_checkdigit_gln ------- ',2);
460 END IF;
461
462 l_checkDigit:= TO_NUMBER(SUBSTR(p_gln,13,1 ));
463 IF (l_debug_level <= 1) THEN
464 cln_debug_pub.Add('M4U: Checksum Attached : '||l_checkDigit,1);
465 END IF;
466
467
468 l_left_sum := TO_NUMBER(SUBSTR(p_gln,1,1 ))+
469 TO_NUMBER(SUBSTR(p_gln,3,1 ))+
470 TO_NUMBER(SUBSTR(p_gln,5,1 ))+
471 TO_NUMBER(SUBSTR(p_gln,7,1 ))+
472 TO_NUMBER(SUBSTR(p_gln,9,1 ))+
473 TO_NUMBER(SUBSTR(p_gln,11,1 ));
474
475 IF (l_debug_level <= 1) THEN
476 cln_debug_pub.Add('M4U: Sum Of Odd Numbers: '||l_right_sum,1);
477 END IF;
478
479
480 l_right_sum := TO_NUMBER(SUBSTR(p_gln,2,1 ))+
481 TO_NUMBER(SUBSTR(p_gln,4,1 ))+
482 TO_NUMBER(SUBSTR(p_gln,6,1 ))+
483 TO_NUMBER(SUBSTR(p_gln,8,1 ))+
484 TO_NUMBER(SUBSTR(p_gln,10,1 ))+
485 TO_NUMBER(SUBSTR(p_gln,12,1 ));
486 IF (l_debug_level <= 1) THEN
487 cln_debug_pub.Add('M4U: Sum Of Even Numbers: '||l_left_sum,1);
488 END IF;
489
490 l_con_sum := l_right_sum*3 + l_left_sum;
491 IF (l_debug_level <= 1) THEN
492 cln_debug_pub.Add('M4U: consolidated sum : '||l_con_sum,1);
493 END IF;
494
495 l_remainder := Mod(l_con_sum,10);
496 IF (l_debug_level <= 1) THEN
497 cln_debug_pub.Add('M4U: Remainder : '||l_remainder,1);
498 END IF;
499
500 if (l_remainder = 0) then
501 l_remainder := 10;
502 end if;
503
504 l_calculatedCheckDigit := 10 - l_remainder;
505 IF (l_debug_level <= 1) THEN
506 cln_debug_pub.Add('M4U: Calculated Checksum: '||l_calculatedCheckDigit,1);
507 END IF;
508
509 IF (l_checkDigit <> l_calculatedCheckDigit) THEN
510 IF (l_debug_level <= 1) THEN
511 cln_debug_pub.Add('M4U: Incorrect Checksum',1);
512 cln_debug_pub.Add('------- Exiting validate_checkdigit_gln - ',1);
513 END IF;
514 return FALSE;
515 END IF;
516
517 IF (l_debug_level <= 2) THEN
518 cln_debug_pub.Add('------- Exiting validate_checkdigit_gln --------- ',2);
519 END IF;
520
521 return TRUE;
522 EXCEPTION
523 WHEN OTHERS THEN
524 l_error_code :=SQLCODE;
525 l_error_msg :=SQLERRM;
526
527 IF (l_debug_level <= 5) THEN
528 cln_debug_pub.Add(' :: '||l_error_code||' :: '||l_error_msg,5);
529 END IF;
530
531 IF (l_debug_level <= 2) THEN
532 cln_debug_pub.Add('------- Exiting validate_checkdigit_gln - Exception --------- ',2);
533 END IF;
534 RETURN FALSE;
535 END;
536
537 -- Name
538 -- validate_uccnet_attr
539 -- Purpose
540 -- This procedure is used for validating the GTIN/GLN at the moment
541 -- Arguments
542 -- Notes
543 --
544 FUNCTION validate_uccnet_attr(
545 x_return_status OUT NOCOPY VARCHAR2,
546 x_msg_data OUT NOCOPY VARCHAR2,
547 p_attr_type IN VARCHAR2,
548 p_attr_value IN VARCHAR2
549 )RETURN BOOLEAN
550
551 IS
552 l_attr_name VARCHAR2(20);
553 l_attr_len NUMBER;
554 l_attr_value NUMBER;
555 l_msg_data VARCHAR2(100);
556 l_error_code VARCHAR2(50);
557 l_error_msg VARCHAR2(200);
558 BEGIN
559
560 IF (l_debug_Level <= 2) THEN
561 cln_debug_pub.Add('M4U:----- Entering validate_uccnet_attr ------- ',2);
562 END IF;
563
564 -- Parameters received
565 IF (l_Debug_Level <= 1) THEN
566 cln_debug_pub.Add('++++ PARAMETERS RECEIVED +++ ',1);
567 cln_debug_pub.Add('p_attr_type - '||p_attr_type,1);
568 cln_debug_pub.Add('p_attr_value - '||p_attr_value,1);
569 cln_debug_pub.Add('==============================',1);
570 END IF;
571
572 IF (p_attr_type = 'GLN') THEN
573 l_msg_data := 'Validation Failed: GLN should be 13 length digits';
574 FND_MESSAGE.SET_NAME('CLN','M4U_ATTRVAL_GLN_1');
575 x_msg_data := FND_MESSAGE.GET;
576
577 select length(p_attr_value) into l_attr_len from dual;
578
579 If(l_attr_len <> 13) THEN
580 RAISE FND_API.G_EXC_ERROR;
581 END IF;
582
583 l_msg_data := 'Validation Failed: GLN should be a numeric value';
584
585 FND_MESSAGE.SET_NAME('CLN','M4U_ATTRVAL_GLN_2');
586 x_msg_data := FND_MESSAGE.GET;
587 select to_number(p_attr_value,'9999999999999') into l_attr_value from dual;
588
589
590 l_msg_data := 'Validation Failed: GLN check digitsum is wrong';
591
592 FND_MESSAGE.SET_NAME('CLN','M4U_ATTRVAL_GLN_3');
593 x_msg_data := FND_MESSAGE.GET;
594
595 IF(NOT validate_checkdigit_gln(p_attr_value)) THEN
596 RAISE FND_API.G_EXC_ERROR;
597 END IF;
598 return TRUE;
599 l_msg_data := 'GLN validated';
600
601 FND_MESSAGE.SET_NAME('CLN','M4U_ATTRVAL_GLN_VALID');
602 x_msg_data := FND_MESSAGE.GET;
603 END IF;
604
605 IF (p_attr_type = 'GTIN') THEN
606 -- 1. GTINs submitted in RCI messages must be 14 characters
607 l_msg_data := 'Validation Failed: GTIN should be 14 length digits';
608 FND_MESSAGE.SET_NAME('CLN','M4U_ATTRVAL_GTIN_1');
609 x_msg_data := FND_MESSAGE.GET;
610
611 select length(p_attr_value) into l_attr_len from dual;
612
613 If(l_attr_len <> 14) THEN
614 RAISE FND_API.G_EXC_ERROR;
615 END IF;
616
617
618 -- 2. GTIN should be a numeric entity
619 l_msg_data := 'Validation Failed: GTIN should be a numeric value';
620 FND_MESSAGE.SET_NAME('CLN','M4U_ATTRVAL_GTIN_2');
621 x_msg_data := FND_MESSAGE.GET;
622
623 select to_number(p_attr_value,'99999999999999') into l_attr_value from dual;
624
625 -- 3. Checksum should be correct
626 l_msg_data := 'Validation Failed: GTIN check digitsum is wrong';
627 FND_MESSAGE.SET_NAME('CLN','M4U_ATTRVAL_GTIN_3');
628 x_msg_data := FND_MESSAGE.GET;
629
630 IF(NOT validate_checkdigit_gtin(p_attr_value)) THEN
631 RAISE FND_API.G_EXC_ERROR;
632 END IF;
633 return TRUE;
634
635 l_msg_data := 'GTIN validated';
636 FND_MESSAGE.SET_NAME('CLN','M4U_ATTRVAL_GTIN_VALID');
637 x_msg_data := FND_MESSAGE.GET;
638 END IF;
639
640 IF (p_attr_type = 'TRGMKT') THEN
641 -- Parameters received
642 IF (l_Debug_Level <= 1) THEN
643 cln_debug_pub.Add('Target Mkt Check - '||p_attr_value,1);
644 END IF;
645
646
647 l_msg_data := 'Validation Failed: Target Market should be 1-3 digits';
648 FND_MESSAGE.SET_NAME('CLN','M4U_ATTRVAL_TRGMKT_1');
649 x_msg_data := FND_MESSAGE.GET;
650
651 select length(p_attr_value) into l_attr_len from dual;
652
653 If(l_attr_len > 3) THEN
654 RAISE FND_API.G_EXC_ERROR;
655 END IF;
656 return TRUE;
657 END IF;
658
659 -- Exception Handling
660 EXCEPTION
661 WHEN FND_API.G_EXC_ERROR THEN
662 x_return_status := FND_API.G_RET_STS_ERROR ;
663 IF (l_Debug_Level <= 4) THEN
664 cln_debug_pub.Add(l_msg_data,4);
665 cln_debug_pub.Add('------- Exiting validate_uccnet_attr API --------- ',2);
666 END IF;
667
668 return FALSE;
669 WHEN OTHERS THEN
670 l_error_code :=SQLCODE;
671 l_error_msg :=SQLERRM;
672
673 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
674 x_msg_data :=l_error_code||' : '||l_error_msg||' : '||x_msg_data;
675
676 IF (l_Debug_Level <= 5) THEN
677 cln_debug_pub.Add(x_msg_data,5);
678 END IF;
679
680 IF (l_Debug_Level <= 2) THEN
681 cln_debug_pub.Add('------- Exiting validate_uccnet_attr - Exception --------- ',2);
682 END IF;
683
684 Return false;
685
686 END;
687
688
689
690 BEGIN
691 /* Package initialization. */
692 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
693
694 IF (l_Debug_Level <= 1) THEN
695 cln_debug_pub.Add('... Initialization for UTILS package ...',1);
696 END IF;
697
698 SELECT to_char(e.party_id), to_char(e.party_site_id)
699 INTO g_party_id, g_party_site_id
700 FROM hr_locations_all h,
701 ecx_tp_headers e
702 WHERE h.location_id = e.party_id
703 AND UPPER(e.party_type) = UPPER(c_party_type)
704 AND UPPER(h.location_code) = UPPER(c_party_site_name);
705
706 IF (l_Debug_Level <= 1) THEN
707 cln_debug_pub.Add('Party ID set as '||g_party_id,1);
708 cln_debug_pub.Add('Party Site ID set as '||g_party_site_id,1);
709 END IF;
710
711 IF (l_Debug_Level <= 1) THEN
712 cln_debug_pub.Add('Trading Partner Setup in the XML Gateway Defined',1);
713 END IF;
714
715 SELECT name
716 INTO g_local_system
717 FROM wf_systems
718 WHERE guid = wf_core.translate('WF_SYSTEM_GUID');
719
720 IF (l_Debug_Level <= 1) THEN
721 cln_debug_pub.Add('Local System '||g_local_system,1);
722 cln_debug_pub.Add('wf_core.translate(WF_SYSTEM_GUID) returned normal data',1);
723 END IF;
724
725 SELECT FND_PROFILE.VALUE('ORG_ID'),
726 FND_PROFILE.VALUE('M4U_UCCNET_GLN'),
727 FND_PROFILE.VALUE('M4U_SUPP_GLN')
728 INTO g_org_id,g_host_gln,g_supp_gln
729 FROM DUAL;
730
731 IF (l_Debug_Level <= 1) THEN
732 cln_debug_pub.Add('Org ID -'||g_org_id,1);
733 cln_debug_pub.Add('Host GLN -'||g_host_gln,1);
734 cln_debug_pub.Add('Supplier GLN -'||g_supp_gln,1);
735 cln_debug_pub.Add('profile values set for M4U',1);
736 END IF;
737 END m4u_ucc_utils;