1 PACKAGE BODY FTE_UTIL_PKG AS
2 /* $Header: FTEUTILB.pls 120.8 2005/08/02 00:12:27 pkaliyam ship $ */
3
4 --
5 -- Package FTE_UTIL_PKG
6 --
7 --
8
9 g_debug_set BOOLEAN := TRUE;
10 g_debug_on BOOLEAN := TRUE;
11 g_user_debug BOOLEAN;
12 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_UTIL_PKG';
13
14 TYPE VARCHAR2_TAB IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
15
16 concat_segments varchar2_tab;
17 catg_ids varchar2_tab;
18 segment_code varchar2_tab;
19 segment_val varchar2_tab;
20 segment_cc varchar2_tab;
21 l_delimiter varchar2(3);
22
23 ----------------------------------------------------------------
24 -- FUNCTION : Tokenize_String
25 --
26 -- Parameters :
27 -- IN:
28 -- 1. p_string VARCHAR2 REQUIRED
29 -- The string to be tokenized.
30 -- 2. p_delim VARCHAR2 REQUIRED
31 -- The delimiter, or token.
32 -- RETURN: A Stringarray containing the tokens of the string.
33 ----------------------------------------------------------------
34 FUNCTION Tokenize_String (p_string IN VARCHAR2,
35 p_delim IN VARCHAR2) RETURN STRINGARRAY IS
36
37 l_tokens STRINGARRAY := STRINGARRAY();
38 l_token VARCHAR2(4000);
39 l_start_index NUMBER := 1;
40 l_end_index NUMBER := 1;
41 l_count NUMBER := 0;
42 l_delim_count NUMBER := 1;
43
44 BEGIN
45 l_delim_count := length(p_delim);
46 WHILE l_end_index <> 0 LOOP
47 l_end_index := INSTR(p_string, p_delim, l_start_index);
48 l_count := l_count + 1;
49 IF (l_end_index = 0) THEN
50 --no more tokens after this one
51 l_token := SUBSTR(p_string, l_start_index);
52 ELSE
53 l_token := SUBSTR(p_string, l_start_index,
54 l_end_index - l_start_index);
55 END IF;
56 l_start_index := l_end_index + l_delim_count;
57 l_tokens.extend;
58 l_tokens(l_count) := l_token;
59 END LOOP;
60 RETURN l_tokens;
61 END TOKENIZE_STRING;
62
63
64 ------------------------------------------------------------
65 --FUNCTION : Get_Msg
66 --
67 -- Parameters :
68 -- IN:
69 -- 1. p_name VARCHAR2 REQUIRED
70 -- 2. p_tokens STRINGARRAY NOT REQUIRED.
71 -- The tokens for the message.
72 -- 3. p_values STRINGARRAY NOT REQUIRED.
73 -- The token values. Required if p_tokens is passed.
74 --
75 -- RETURN The token-substituted message text
76 -------------------------------------------------------------
77 FUNCTION Get_Msg (p_name IN VARCHAR2,
78 p_tokens IN STRINGARRAY DEFAULT NULL,
79 p_values IN STRINGARRAY DEFAULT NULL)
80 RETURN VARCHAR2 IS
81
82 l_msg varchar2(5000);
83
84 BEGIN
85
86 IF (p_name IS NULL) THEN
87 Fnd_File.Put_Line(Fnd_File.Log, 'Fte_Util_Pkg => Programmer Error: Message Name NULL');
88 RETURN NULL;
89 END IF;
90
91 IF (p_tokens.EXISTS(1) AND p_values.EXISTS(1)
92 AND p_tokens.COUNT = p_values.COUNT ) THEN
93 Fnd_Message.Set_Name('APPLICATION' => 'FTE',
94 'NAME' => p_name);
95
96 FOR i IN 1..p_tokens.COUNT LOOP
97 Fnd_Message.Set_Token(token => p_tokens(i), value => p_values(i));
98 END LOOP;
99
100 l_msg := Fnd_Message.Get;
101 ELSE
102 l_msg := Fnd_Message.Get_String('APPIN' => 'FTE',
103 'NAMEIN' => p_name);
104 END IF;
105
106 return l_msg;
107
108 EXCEPTION
109 WHEN OTHERS THEN
110 Fnd_File.Put_Line(Fnd_File.Log, 'UNEXP. ERROR IN FTE_UTIL_PKG.Get_Msg: ' || sqlerrm);
111 RAISE;
112 END Get_Msg;
113
114 -----------------------------------------------------------------
115 -- FUNCTION : Canonicalize_Number
116 --
117 -- Parameters :
118 -- IN:
119 -- 1. p_number NUMBER REQUIRED
120 ------------------------------------------------------------------
121 FUNCTION Canonicalize_Number (p_number IN NUMBER)
122 RETURN NUMBER IS
123 BEGIN
124 return
125 fnd_number.canonical_to_number(fnd_number.number_to_canonical(p_number));
126 END;
127
128 -----------------------------------------------------------------------------
129 -- FUNCTION GET_CARRIER_ID
130 --
131 -- Purpose Get Carrier Id From Carrier Name
132 --
133 -- IN Parameters:
134 -- 1. p_carrier_name: carrier name
135 -----------------------------------------------------------------------------
136 FUNCTION GET_CARRIER_ID(p_carrier_name IN VARCHAR2) RETURN NUMBER IS
137
138 l_carrier_id NUMBER := NULL;
139 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.GET_CARRIER_ID';
140
141 BEGIN
142 SELECT hz.PARTY_ID INTO l_carrier_id
143 FROM HZ_PARTIES hz, WSH_CARRIERS ca
144 WHERE hz.party_name = p_carrier_name
145 AND hz.party_id = ca.carrier_id;
146
147 RETURN l_carrier_id;
148 EXCEPTION
149 WHEN NO_DATA_FOUND THEN
150 RETURN -1;
151 WHEN OTHERS THEN
152 WRITE_OUTFILE(p_module_name => l_module_name,
153 p_msg => sqlerrm,
154 p_category => 'O');
155 RETURN -2;
156 END GET_CARRIER_ID;
157
158 -----------------------------------------------------------------------------
159 -- FUNCTION GET_CARRIER_NAME
160 --
161 -- Purpose Get Carrier Name From Carrier ID
162 --
163 -- IN Parameters:
164 -- 1. p_carrier_name: carrier name
165 -----------------------------------------------------------------------------
166 FUNCTION GET_CARRIER_NAME(p_carrier_id IN NUMBER) RETURN VARCHAR2 IS
167
168 l_carrier_name HZ_PARTIES.PARTY_NAME%TYPE;
169 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.GET_CARRIER_NAME';
170
171 BEGIN
172
173 SELECT hz.PARTY_name
174 INTO l_carrier_name
175 FROM HZ_PARTIES hz
176 WHERE hz.party_id = p_carrier_id;
177
178 RETURN l_carrier_name;
179
180 EXCEPTION
181
182 WHEN NO_DATA_FOUND THEN
183 RETURN -1;
184
185 WHEN OTHERS THEN
186 WRITE_OUTFILE(p_module_name => l_module_name,
187 p_msg => sqlerrm,
188 p_category => 'O');
189 RETURN -2;
190 END GET_CARRIER_NAME;
191
192 -------------------------------------------------------------------------------
193 -- FUNCTION GET_LOOKUP_CODE
194 --
195 -- Purpose: Get the code from fnd_lookup_values
196 --
197 -- IN parameter:
198 -- 1. p_lookup_type: type of the lookup
199 -- 2. p_value: value to lookup
200 -------------------------------------------------------------------------------
201 FUNCTION GET_LOOKUP_CODE(p_lookup_type IN VARCHAR2,
202 p_value IN VARCHAR2) RETURN VARCHAR2 IS
203
204 l_code VARCHAR2(100);
205 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.GET_LOOKUP_CODE';
206
207 BEGIN
208 l_code := null;
209
210 -- First check with LOOKUP_CODE
211 BEGIN
212 SELECT lookup_code INTO l_code
213 FROM fnd_lookup_values_vl
214 WHERE lookup_type = p_lookup_type
215 AND lookup_code = p_value;
216 EXCEPTION
217 WHEN NO_DATA_FOUND THEN
218 BEGIN
219 -- If the given value can't be found as LOOKUP_CODE
220 -- try with 'like MEANING%'
221 SELECT lookup_code INTO l_code
222 FROM fnd_lookup_values_vl
223 WHERE lookup_type = p_lookup_type
224 AND meaning like p_value;
225 EXCEPTION
226 WHEN NO_DATA_FOUND THEN
227 RETURN NULL;
228 END;
229 END;
230
231 RETURN l_code;
232
233 EXCEPTION
234 WHEN OTHERS THEN
235 WRITE_OUTFILE(p_module_name => l_module_name,
236 p_msg => sqlerrm,
237 p_category => 'O');
238
239 RETURN NULL;
240 END GET_LOOKUP_CODE;
241
242 -------------------------------------------------------------------------------
243 -- FUNCTION GET_SHIPPING_UOM_CLASS
244 --
245 -- Purpose: get the shipping uom class from the class name
246 --
247 -- IN parameters:
248 -- 1. p_uom_class: uom class name
249 --
250 -- Returns a uom class name corresponding to the input class, NULL if exception raised
251 -------------------------------------------------------------------------------
252 FUNCTION GET_SHIPPING_UOM_CLASS(p_uom_class IN VARCHAR2) RETURN VARCHAR2 IS
253 l_uom_class VARCHAR2(20) := '';
254 BEGIN
255 BEGIN
256 SELECT p_uom_class--||'_uom_class'
257 INTO l_uom_class
258 FROM wsh_shipping_parameters
259 WHERE rownum < 2;
260 EXCEPTION
261 WHEN OTHERS THEN
262 RETURN NULL;
263 END;
264 RETURN l_uom_class;
265 END GET_SHIPPING_UOM_CLASS;
266
267 -------------------------------------------------------------------------------
268 -- FUNCTION GET_UOM_CODE
269 --
270 -- Purpose: get the uom code from uom name and class
271 --
272 -- IN parameters:
273 -- 1. p_uom: uom name
274 -- 2. p_uom_class: class name (only applies to weight and volumn)
275 --
276 -- Returns a Uom_Code searching into mtl_units_of_measure
277 -- using first uom_code then unit_of_measure
278 -------------------------------------------------------------------------------
279 FUNCTION GET_UOM_CODE(p_uom IN VARCHAR2,
280 p_uom_class IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
281
282 l_code VARCHAR2(100) := '';
283 l_shipping_uom_class VARCHAR2(100) := '';
284 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.GET_UOM_CODE';
285
286 BEGIN
287 IF (p_uom_class IS NULL OR p_uom_class NOT IN ('Weight', 'Volume')) THEN
288 BEGIN
289 -- First check with UOM_CODE
290 SELECT uom_code INTO l_code
291 FROM mtl_units_of_measure
292 WHERE uom_code = p_uom;
293 EXCEPTION
294 WHEN NO_DATA_FOUND THEN
295 BEGIN
296 -- If the given value can't be found as UOM_CODE
297 -- try with UNIT_OF_MEASURE
298 SELECT uom_code INTO l_code
299 FROM mtl_units_of_measure
300 WHERE unit_of_measure = p_uom;
301 EXCEPTION
302 WHEN OTHERS THEN
303 RETURN NULL;
304 END;
305 WHEN OTHERS THEN
306 RETURN NULL;
307 END;
308 ELSE
309 l_shipping_uom_class := GET_SHIPPING_UOM_CLASS(p_uom_class);
310 IF (l_shipping_uom_class IS NOT NULL) THEN
311 BEGIN
312 -- First check with UOM_CODE
313 SELECT uom_code INTO l_code
314 FROM mtl_units_of_measure
315 WHERE uom_code = p_uom
316 AND uom_class = l_shipping_uom_class;
317 EXCEPTION
318 WHEN NO_DATA_FOUND THEN
319 BEGIN
320 -- If the given value can't be found as UOM_CODE
321 -- try with UNIT_OF_MEASURE
322 SELECT uom_code INTO l_code
323 FROM mtl_units_of_measure
324 WHERE unit_of_measure = p_uom
325 AND uom_class = l_shipping_uom_class;
326 EXCEPTION
327 WHEN OTHERS THEN
328 RETURN NULL;
329 END;
330 WHEN OTHERS THEN
331 RETURN NULL;
332 END;
333 ELSE
334 RETURN NULL;
335 END IF;
336 END IF;
337
338 RETURN l_code;
339 END GET_UOM_CODE;
340
341 -----------------------------------------------------------------------------
342 -- FUNCTION GET_DATA
343 --
344 -- Purpose Given two String arrays representing key/value pairs, and a 'key',
345 -- it returns the corresponding 'value'.
346 --
347 -- IN Parameters
348 -- 1. p_key: The 'key' whose corresponding value is required
349 -- 2. p_keys: An array of keys.
350 -- 3. p_values : An array of values.
351 --
352 -- RETURN: The corresponding 'value' of the input parameter 'p_key', or NULL
353 -- if 'p_key' is not in the array of keys.
354 -----------------------------------------------------------------------------
355 FUNCTION GET_DATA(p_key IN VARCHAR2,
356 p_values IN FTE_BULKLOAD_PKG.data_values_tbl) RETURN VARCHAR2 IS
357
358 l_data VARCHAR2(50) := NULL;
359 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.GET_DATA';
360
361 BEGIN
362 IF (p_values.EXISTS(p_key)) THEN
363 RETURN p_values(p_key);
364 ELSE
365 RETURN NULL;
366 END IF;
367 EXCEPTION
368 WHEN SUBSCRIPT_BEYOND_COUNT THEN
369 Enter_Debug(l_module_name);
370 WRITE_LOGFILE(l_module_name, 'WARNING: Key or Value not found for Tag ' || p_key);
371 Exit_Debug(l_module_name);
372 RETURN NULL;
373 WHEN NO_DATA_FOUND THEN
374 ENTER_Debug(l_module_name);
375 WRITE_LOGFILE(l_module_name, 'WARNING: No data found for key ' || p_key);
376 Exit_Debug(l_module_name);
377 RETURN NULL;
378 WHEN OTHERS THEN
379 WRITE_OUTFILE(p_module_name => l_module_name,
380 p_msg => sqlerrm,
381 p_category => 'O');
382
383 RETURN NULL;
384 END GET_DATA;
385
386 -----------------------------------------------------------------------------
387 -- FUNCTION Get_Vehicle_Type
388 --
389 -- Purpose Get the vehicle ID Given the vehicle type or id
390 --
391 -- IN Parameters
392 -- 1. l_vehicle_type IN VARCHAR2 : The vehicle type to be validated.
393 --
394 -- RETURN:
395 -- the vehicle ID, or null if it doesn't exist.
396 -----------------------------------------------------------------------------
397 FUNCTION Get_Vehicle_Type (p_vehicle_type IN VARCHAR2) RETURN VARCHAR2 IS
398
399 l_veh_id NUMBER := NULL;
400 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.GET_VEHICLE_TYPE';
401
402 BEGIN
403
404 BEGIN
405 SELECT veh.vehicle_type_id
406 INTO l_veh_id
407 FROM mtl_system_items_kfv mtl, fte_vehicle_types veh
408 WHERE mtl.concatenated_segments = p_vehicle_type
409 AND mtl.inventory_item_id = veh.inventory_item_id
410 AND mtl.vehicle_item_flag = 'Y'
411 AND rownum = 1;
412
413 EXCEPTION
414 WHEN NO_DATA_FOUND THEN
415 BEGIN
416 SELECT veh.vehicle_type_id
417 INTO l_veh_id
418 FROM mtl_system_items_kfv mtl, fte_vehicle_types veh
419 WHERE to_char(veh.vehicle_type_id) = p_vehicle_type
420 AND mtl.inventory_item_id = veh.inventory_item_id
421 AND mtl.vehicle_item_flag = 'Y'
422 AND rownum = 1;
423 EXCEPTION
424 WHEN NO_DATA_FOUND THEN
425 RETURN null;
426 END;
427 END;
428
429 return l_veh_id;
430
431 EXCEPTION
432 WHEN OTHERS THEN
433 ENTER_Debug(l_module_name);
434 WRITE_OUTFILE(p_module_name => l_module_name,
435 p_msg => sqlerrm,
436 p_category => 'O');
437 Exit_Debug(l_module_name);
438 RETURN NULL;
439 END Get_Vehicle_Type;
440
441
442 -----------------------------------------------------------------------------
443 -- FUNCTION GET_CATG_ID
444 --
445 -- Purpose Get the category ID using the commodity value
446 --
447 -- IN Parameters
448 -- 1. p_com_class: commodity class
449 -- 2. p_value: commodity value
450 --
451 -- RETURN:
452 -- the category id for the commodity, -1 if not found
453 -----------------------------------------------------------------------------
454
455 FUNCTION GET_CATG_ID (p_com_class IN VARCHAR2,
456 p_value IN VARCHAR2) RETURN NUMBER IS
457 l_delimiter VARCHAR2(5);
458 l_id NUMBER;
459 l_class VARCHAR2(100);
460 token_tab STRINGARRAY;
461 l_value VARCHAR2(30);
462 BEGIN
463 BEGIN
464 SELECT concatenated_segment_delimiter
465 INTO l_delimiter
466 FROM fnd_id_flex_structures
467 WHERE id_flex_code = 'MCAT'
468 AND id_flex_structure_code='WSH_COMMODITY_CODE'
469 AND application_id = 401;
470
471 EXCEPTION
472 WHEN NO_DATA_FOUND THEN
473 -- addError(FIELD_COMMODITY,"FTE_CAT_COMM_DELIMITER_ERROR", DataLoader.CATEGORY_B);
474 return -1;
475 END;
476
477 -- Validate against Commodify Class in the lane
478
479 IF (INSTR(p_value, l_delimiter) <= 0) THEN
480 l_class := p_value;
481 ELSE
482 l_class := SUBSTR(p_value, 1, (INSTR(p_value, l_delimiter)-1));
483 END IF;
484
485 IF (l_class <> p_com_class) THEN
486 RETURN -3;
487 END IF;
488
489 token_tab := TOKENIZE_STRING(p_value, l_delimiter);
490
491 IF (token_tab.COUNT = 1) THEN
492 l_value := token_tab(1);
493 ELSIF (token_tab.COUNT > 1) THEN
494 l_value := token_tab(1) || l_delimiter || token_tab(2);
495 END IF;
496
497 BEGIN
498 SELECT category_id
499 INTO l_id
500 FROM mtl_categories_kfv c, mtl_category_sets s
501 WHERE s.structure_id = c.structure_id
502 AND s.category_set_name = 'WSH_COMMODITY_CODE'
503 AND c.concatenated_segments like l_value||'%'
504 AND rownum = 1;
505
506 EXCEPTION
507 WHEN NO_DATA_FOUND THEN
508 RETURN -1;
509 END;
510 RETURN l_id;
511 END GET_CATG_ID;
512
513 -----------------------------------------------------------------------------
514 -- PROCEDURE GET_CATEGORY_ID
515 --
516 -- Purpose
517 -- Return the category ID of the freight class represented in
518 -- the string 'p_commodity_value'. Caches the values of all commodities
519 -- the first time it is called, for greater efficiency.
520 --
521 -- IN Parameters
522 -- 1. p_commodity_value: Three different (but equivalent) configurations of
523 -- the commodity will all evaluate to the same value. E.g. '500', 'FC.500'
524 -- and 'FC.500.US' are all acceptable inputs, and will return the same
525 -- category ID.
526 --
527 -- Out Parameters
528 -- 1. x_catg_id: The category ID of the input.
529 -- 2. x_class_code: The class_code of the input. (e.g. FC)
530 -----------------------------------------------------------------------------
531
532 PROCEDURE GET_CATEGORY_ID (p_commodity_value IN VARCHAR2,
533 x_catg_id OUT NOCOPY NUMBER,
534 x_class_code OUT NOCOPY VARCHAR2,
535 x_status OUT NOCOPY NUMBER,
536 x_error_msg OUT NOCOPY VARCHAR2) IS
537
538 i NUMBER;
539 token_tab STRINGARRAY;
540 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.GET_CATEGORY_ID';
541
542 CURSOR get_freight_classes IS
543 SELECT c.category_id, c.concatenated_segments, c.segment1, c.segment2, c.segment3
544 FROM mtl_categories_kfv c, mtl_category_sets s
545 WHERE s.structure_id = c.structure_id
546 AND s.category_set_name = 'WSH_COMMODITY_CODE';
547
548 CURSOR get_delimiter IS
549 SELECT concatenated_segment_delimiter
550 FROM fnd_id_flex_structures
551 WHERE id_flex_code = 'MCAT'
552 AND id_flex_structure_code ='WSH_COMMODITY_CODE'
553 AND application_id = 401;
554
555 BEGIN
556 x_catg_id := NULL;
557 x_status := -1;
558
559 IF (concat_segments IS NULL OR concat_segments.COUNT = 0) THEN
560
561 Enter_Debug(l_module_name);
562
563 OPEN get_delimiter;
564 FETCH get_delimiter INTO l_delimiter;
565
566 IF get_delimiter%NOTFOUND OR length(l_delimiter) = 0 THEN
567 x_catg_id := NULL;
568 CLOSE get_delimiter;
569 x_error_msg := GET_MSG(p_name => 'FTE_DELIMITER_NOT_FOUND');
570 WRITE_OUTFILE(p_module_name => l_module_name,
571 p_msg => x_error_msg,
572 p_category => 'B');
573
574 Exit_Debug(l_module_name);
575 return;
576 END IF;
577 CLOSE get_delimiter;
578
579 OPEN GET_FREIGHT_CLASSES;
580 FETCH GET_FREIGHT_CLASSES BULK COLLECT INTO catg_ids, concat_segments, segment_code,
581 segment_val, segment_cc;
582
583 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
584 WRITE_LOGFILE(l_module_name, 'Fetched ' || catg_ids.COUNT || ' commodities');
585 END IF;
586
587 close get_freight_classes;
588 Exit_Debug(l_module_name);
589 END IF;
590
591 token_tab := TOKENIZE_STRING(p_commodity_value, l_delimiter);
592
593 IF (token_tab.count = 1) THEN --ASSUME USER PASSED IN ONLY THE VALUE.
594 FOR i in 1..concat_segments.COUNT LOOP
595 IF (segment_val(i) = p_commodity_value) THEN
596 x_catg_id := catg_ids(i);
597 x_class_code := segment_code(i);
598 return;
599 END IF;
600 END LOOP;
601 ELSIF (token_tab.count > 1) THEN
602 FOR i in 1..concat_segments.COUNT LOOP
603 IF (segment_code(i) = token_tab(1) AND segment_val(i) = token_tab(2)) THEN
604 x_catg_id := catg_ids(i);
605 x_class_code := segment_code(i);
606 return;
607 END IF;
608 END LOOP;
609 END IF;
610
611 Enter_Debug(l_module_name);
612 x_status := 2;
613
614 x_error_msg := GET_MSG(p_name => 'FTE_CAT_COMMODITY_UNKNOWN',
615 p_tokens => STRINGARRAY('COMMODITY'),
616 p_values => STRINGARRAY(p_commodity_value));
617
618 WRITE_OUTFILE(p_module_name => l_module_name,
619 p_msg => x_error_msg,
620 p_category => 'D');
621
622 Exit_Debug(l_module_name);
623
624 EXCEPTION
625 WHEN OTHERS THEN
626 IF (get_freight_classes%ISOPEN) THEN
627 CLOSE get_freight_classes;
628 END IF;
629 IF (get_delimiter%ISOPEN) THEN
630 CLOSE get_delimiter;
631 END IF;
632 WRITE_OUTFILE(p_module_name => l_module_name,
633 p_msg => sqlerrm,
634 p_category => 'O');
635 RETURN;
636 END GET_CATEGORY_ID;
637
638 -----------------------------------------------------------------------------
639 -- PROCEDURE GET_CATEGORY_ID
640 --
641 -- Purpose
642 -- Overloaded version of the function GET_CATEGORY_ID. See above.
643 -----------------------------------------------------------------------------
644
645 PROCEDURE GET_CATEGORY_ID (p_commodity_value IN VARCHAR2,
646 x_catg_id OUT NOCOPY NUMBER,
647 x_status OUT NOCOPY NUMBER,
648 x_error_msg OUT NOCOPY VARCHAR2) IS
649
650 x_class_code VARCHAR2(40);
651 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.GET_CATEGORY_ID';
652 BEGIN
653 GET_CATEGORY_ID(p_commodity_value => p_commodity_value,
654 x_catg_id => x_catg_id,
655 x_class_code => x_class_code,
656 x_status => x_status,
657 x_error_msg => x_error_msg);
658
659 EXCEPTION
660 WHEN OTHERS THEN
661 WRITE_OUTFILE(p_module_name => l_module_name,
662 p_msg => sqlerrm,
663 p_category => 'O');
664 RETURN;
665 END GET_CATEGORY_ID;
666
667 -----------------------------------------------------------------------------
668 -- PROCEDURE GET_Fnd_Currency
669 --
670 -- Purpose Validate a currency against Fnd_Currencies.
671 --
672 -- Parameters
673 -- p_currency : The currency name to be validated.
674 --
675 -- RETURN : The currency, if the currency is valid. NULL if the currency
676 -- is not valid.
677 -----------------------------------------------------------------------------
678 FUNCTION GET_Fnd_Currency (p_currency IN VARCHAR2,
679 x_error_msg OUT NOCOPY VARCHAR2,
680 x_status OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
681
682 l_currency VARCHAR2(45);
683
684 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.GET_FND_CURRENCY';
685
686 BEGIN
687 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
688 x_status := -1;
689
690 BEGIN
691 --try the currency code.
692 SELECT currency_code INTO l_currency
693 FROM fnd_currencies
694 WHERE currency_code = p_currency
695 AND enabled_flag = 'Y'
696 AND currency_flag = 'Y'
697 AND nvl(start_date_active, sysdate) <= sysdate
698 AND nvl(end_date_active, sysdate) >= sysdate
699 AND rownum = 1;
700
701 EXCEPTION
702 WHEN NO_DATA_FOUND THEN
703 BEGIN
704 --try the currency name.
705 SELECT currency_code INTO l_currency
706 FROM fnd_currencies_vl
707 WHERE name = p_currency
708 AND enabled_flag = 'Y'
709 AND currency_flag = 'Y'
710 AND nvl(start_date_active, sysdate) <= sysdate
711 AND nvl(end_date_active, sysdate) >= sysdate
712 AND rownum = 1;
713 EXCEPTION
714 WHEN NO_DATA_FOUND THEN
715 x_error_msg := Get_Msg(P_Name => 'FTE_INVALID_CARRIER_CURRENCY');
716 WRITE_OUTFILE(p_module_name => l_module_name,
717 p_msg => x_error_msg,
718 p_category => 'D');
719
720 x_status := 2;
721 END;
722 END;
723 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
724 RETURN l_currency;
725
726 EXCEPTION
727 WHEN OTHERS THEN
728 WRITE_OUTFILE(p_module_name => l_module_name,
729 p_msg => sqlerrm,
730 p_category => 'O');
731 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
732 RETURN NULL;
733 END GET_Fnd_Currency;
734
735 ------------------------------------------------------------
736 -- FUNCTION GET_CATEGORY_MESSAGE
737 --
738 -- Purpose: get the category message for error
739 --
740 -- IN parameters:
741 -- 1. p_category: the category letter
742 --
743 -- Return category message
744 ------------------------------------------------------------
745 FUNCTION Get_Category_message(p_category IN VARCHAR2)
746 RETURN VARCHAR2 IS
747 l_category_msg VARCHAR2(1000);
748 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.Get_Category_message';
749 BEGIN
750
751 IF (UPPER(p_category) = 'A') THEN
752 l_category_msg := GET_MSG('FTE_LOADER_CATEGORY_A');
753 ELSIF (UPPER(p_category) = 'B') THEN
754 l_category_msg := GET_MSG('FTE_LOADER_CATEGORY_B');
755 ELSIF (UPPER(p_category) = 'C') THEN
756 l_category_msg := GET_MSG('FTE_LOADER_CATEGORY_C');
757 ELSIF (UPPER(p_category) = 'D') THEN
758 l_category_msg := GET_MSG('FTE_LOADER_CATEGORY_D');
759 ELSIF (UPPER(p_category) = 'E') THEN
760 l_category_msg := GET_MSG('FTE_LOADER_CATEGORY_E');
761 ELSIF (UPPER(p_category) = 'F') THEN
762 l_category_msg := GET_MSG('FTE_LOADER_CATEGORY_F');
763 ELSIF (UPPER(p_category) = 'O') THEN
764 l_category_msg := GET_MSG('FTE_LOADER_CATEGORY_O');
765 ELSE
766 Write_LogFile(l_module_name, 'Programmer Error: Invalid Message Category - ' || p_category);
767 RETURN NULL;
768 END IF;
769
770 RETURN l_category_msg;
771
772 END Get_Category_message;
773
774 -------------------------------------------------------------------------------------
775 -- PROCEDURE Init_Debug
776 --
777 -- Purpose: This procedure turns the debug on depending on the value p_user_debug.
778 -- and starts the WSH debugger.
779 --
780 -- IN Parameters:
781 -- 1. p_user_debug: user debug flag
782 -------------------------------------------------------------------------------------
783
784 PROCEDURE Init_Debug(p_user_debug NUMBER) IS
785 BEGIN
786
787 IF (p_user_debug = 1) THEN
788 FTE_BULKLOAD_PKG.g_debug_on := TRUE;
789 WSH_UTIL_CORE.Set_Log_Level(p_log_level => 1);
790 -- NEED TO SET QP debug.
791 END IF;
792
793 END Init_Debug;
794
795 -----------------------------------------------------------------------------
796 -- PROCEDURE Enter_Debug
797 --
798 -- Purpose: Enter the debug for current procedure/function in wsh debug file
799 --
800 -- IN Parameters:
801 -- 1. p_module_name: module name to enter
802 --
803 -----------------------------------------------------------------------------
804
805 PROCEDURE Enter_Debug(p_module_name IN VARCHAR2) IS
806 BEGIN
807 WSH_DEBUG_SV.push(p_module_name);
808 END Enter_Debug;
809
810 -----------------------------------------------------------------------------
811 -- PROCEDURE Exit_Debug
812 --
813 -- Purpose: Exit the debug for current procedure/function in wsh debug file
814 --
815 -- IN Parameters:
816 -- 1. p_module_name: module name to exit
817 --
818 -----------------------------------------------------------------------------
819
820 PROCEDURE Exit_Debug(p_module_name IN VARCHAR2) IS
821
822 BEGIN
823 WSH_DEBUG_SV.pop(p_module_name);
824 END Exit_Debug;
825
826
827 -------------------------------------------------------------------------------------
828 -- PROCEDURE Write_OutFile
829 --
830 -- Purpose: Writing a message in the concurrent output file(without tokens).
831 --
832 -- IN Parameters:
833 -- 1. p_msg the message (i.e. sqlerrm)
834 -- 2. p_module_name procedure name
835 -- 3. p_category category the p_msg_name belongs to.
836 -- 4. p_line_number the line number where the error occurs.
837 -------------------------------------------------------------------------------------
838
839 PROCEDURE Write_OutFile(p_msg IN VARCHAR2,
840 p_module_name IN VARCHAR2,
841 p_category IN VARCHAR2,
842 p_line_number IN NUMBER DEFAULT NULL) IS
843
844 BEGIN
845
846 WRITE_LOGFILE(p_module_name => p_module_name,
847 p_message => p_msg);
848
849 IF (p_category IS NOT NULL) THEN
850 Fnd_File.Put_Line(Fnd_File.Output, Get_Category_message(p_category));
851 END IF;
852
853 IF (p_line_number IS NOT NULL) THEN
854 -- make 'Line No.' a message
855 Fnd_File.Put_Line(Fnd_File.Output, 'Line No. ' || p_line_number || ' : ' || p_msg);
856 ELSE
857 Fnd_File.Put_Line(Fnd_File.Output, p_msg);
858 END IF;
859
860 END Write_OutFile;
861
862 -------------------------------------------------------------------------------------
863 -- PROCEDURE Write_OutFile
864 --
865 -- Purpose: Writing a message with tokens in the concurrent output file.
866 --
867 -- IN Parameters:
868 -- 1. p_msg_name the message name. e.g, 'FTE_CAT_ACTION_INVALID'
869 -- 2. p_tokens the tokens the message text of p_msg_name has.
870 -- 3. p_values the values for the p_tokens.
871 -- 4. p_module_name: module where error occured
872 -- 4. p_category category the p_msg_name belongs to.
873 -- 5. p_line_number the line number where the error occurs.
874 -------------------------------------------------------------------------------------
875
876 PROCEDURE Write_OutFile(p_msg_name IN VARCHAR2,
877 p_tokens IN STRINGARRAY DEFAULT NULL,
878 p_values IN STRINGARRAY DEFAULT NULL,
879 p_module_name IN VARCHAR2,
880 p_category IN VARCHAR2,
881 p_line_number IN NUMBER DEFAULT NULL) IS
882 l_message VARCHAR2(2000);
883
884 BEGIN
885
886 l_message := GET_MSG(p_name => p_msg_name,
887 p_tokens => p_tokens,
888 p_values => p_values);
889
890 WRITE_LOGFILE(p_module_name => p_module_name,
891 p_message => l_message);
892
893 IF (p_category IS NOT NULL) THEN
894 Fnd_File.Put_Line(Fnd_File.Output, Get_Category_message(p_category));
895 END IF;
896 IF (p_line_number IS NOT NULL) THEN
897 -- make 'Line No.' a message
898 Fnd_File.Put_Line(Fnd_File.Output, 'Line No. ' || p_line_number || ' : ' || l_message);
899 ELSE
900 Fnd_File.Put_Line(Fnd_File.Output, l_message);
901 END IF;
902
903 END Write_OutFile;
904
905 -------------------------------------------------------------------------------------
906 -- PROCEDURE Write_LogFile
907 --
908 -- Purpose: Logging a message.
909 --
910 -- IN Parameters:
911 -- 1. p_module_name the module messages were logged at
912 -- 2. p_message the message to be logged.
913 -------------------------------------------------------------------------------------
914
915 PROCEDURE Write_LogFile(p_module_name IN VARCHAR2,
916 p_message IN VARCHAR2) IS
917
918 BEGIN
919 WSH_DEBUG_SV.LogMsg(p_module_name, p_message);
920 END Write_LogFile;
921
922 -------------------------------------------------------------------------------------
923 -- PROCEDURE Write_LogFile
924 --
925 -- Purpose: Logging a message with an attribute and value (ie. carrier_id = 100)
926 --
927 -- IN Parameters:
928 -- 1. p_module_name the module messages were logged at
929 -- 2. p_attribute the attribute displayed
930 -- 3. p_value the value of the attribute
931 -------------------------------------------------------------------------------------
932 PROCEDURE Write_LogFile(p_module_name IN VARCHAR2,
933 p_attribute IN VARCHAR2,
934 p_value IN VARCHAR2) IS
935 BEGIN
936 WSH_DEBUG_SV.Log(x_Module => p_module_name,
937 x_Text => p_attribute,
938 x_Value => p_value);
939
940 END Write_LogFile;
941
942 END FTE_UTIL_PKG;
[Home] [Help]