1 PACKAGE BODY FTE_FREIGHT_PRICING_UTIL AS
2 /* $Header: FTEFRUTB.pls 120.2 2005/10/25 14:52:41 susurend noship $ */
3
4 -- package global variables
5 g_utl_file_name VARCHAR2(255);
6 g_utl_file_dir VARCHAR2(255);
7 g_file_ptr utl_file.file_type;
8 g_log_level NUMBER := G_LOG;
9 g_msg_count NUMBER := 0; -- this variable keeps track of how many messages we added to the
10 -- fnd message stack
11
12
13 PROCEDURE reset_dbg_vars IS
14 BEGIN
15 g_method := NULL;
16 g_location := NULL;
17 g_exception := NULL;
18 END;
19
20 PROCEDURE set_debug_on IS
21 BEGIN
22 g_debug := true;
23 END;
24
25 PROCEDURE set_debug_off IS
26 BEGIN
27 g_debug := false;
28 END;
29
30 PROCEDURE set_method(p_log_level IN NUMBER DEFAULT G_LOG,
31 p_met IN VARCHAR2,
32 p_loc IN VARCHAR2 DEFAULT NULL)
33 IS
34 BEGIN
35
36 IF (g_log_level >= p_log_level) THEN
37 g_method := p_met;
38 g_location := p_loc;
39 --print_msg('<Method '||p_met||' >');
40 --print_tag(p_msg => '<Method '||p_met||' >');
41 print_tag(p_msg => '<'||p_met||' >');
42 print_msg(G_DBG,'Method = '||p_met||' loc = '||'Start');
43 END IF;
44 END;
45
46 PROCEDURE unset_method(p_log_level IN NUMBER DEFAULT G_LOG,
47 p_met IN VARCHAR2)
48 IS
49 BEGIN
50 IF (g_log_level >= p_log_level) THEN
51 g_method := p_met;
52 print_msg(G_DBG,'Method = '||p_met||' loc = '||'End');
53 --print_tag(p_msg => '</Method '||p_met||' >');
54 print_tag(p_msg => '</'||p_met||' >');
55 --print_msg('</Method '||p_met||' >');
56 END IF;
57 END;
58
59 PROCEDURE set_location(p_log_level IN NUMBER DEFAULT G_DBG, p_loc IN VARCHAR2)
60 IS
61 BEGIN
62 IF (g_log_level >= p_log_level) THEN
63 g_location := p_loc;
64 print_msg(p_log_level,p_loc);
65 END IF;
66 END;
67
68 PROCEDURE set_exit_exception(p_met IN VARCHAR2, p_exc IN VARCHAR2)
69 IS
70 l_message_name VARCHAR2(1000);
71 BEGIN
72 set_exception(p_met=>p_met,p_exc=>p_exc);
73 l_message_name := get_log_file_name;
74 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_LOG_FILE_NAME');
75 FND_MESSAGE.SET_TOKEN('LOGFILE',l_message_name);
76 FND_MSG_PUB.ADD;
77 g_msg_count := g_msg_count + 1;
78 END set_exit_exception;
79
80 -- TODO handle token runtime
81 PROCEDURE set_exception(p_met IN VARCHAR2,
82 p_log_level IN NUMBER DEFAULT G_LOG,
83 p_exc IN VARCHAR2)
84 IS
85 l_message_name VARCHAR2(255);
86 BEGIN
87 IF (g_log_level >= p_log_level) THEN
88 print_tag(G_ERR,'<EXCEPTION>');
89 print_tag(G_ERR,'<METHOD>'||p_met||'</METHOD>');
90 print_msg(G_ERR,p_exc);
91 print_tag(G_ERR,'</EXCEPTION>');
92 END IF;
93 /* not put message on fnd error stack, outer wrapper decide what to put on message stack
94 g_exception := p_exc;
95 l_message_name := 'FTE'||UPPER(SUBSTR(p_exc,2,LENGTH(p_exc)));
96 IF p_exc <> 'g_others' THEN
97 IF p_exc IN ('g_no_currency_found','g_no_price_list_on_lane','g_qp_price_request_failed',
98 'g_category_not_found','g_no_segment_service_type','g_empty_delivery',
99 'g_pricing_not_required','g_not_on_pricelist',
100 'g_invalid_basis','g_loose_item_wrong_basis','g_invalid_fc_type',
101 'g_invalid_line_quantity','g_def_wt_break_not_found',
102 'g_invalid_uom_conversion',
103 -- new exceptions for pack I
104 'g_freight_costs_int_fail', 'g_freight_costs_int_fail', 'g_ln_no_lane_found',
105 'g_ln_too_many_found', 'g_lane_search_failed', 'g_no_ship_method',
106 'g_invalid_ship_method', 'g_get_cost_type_failed',
107 -- new exceptions for pack J
108 --SUSUREND 2-Oct-2003 only exceptions with no tokens listed here
109 'g_invalid_parameters','g_unsupported_action',
110 'g_tl_no_pallet_item_type','g_tl_fetch_alloc_param_fail'
111
112 ) THEN
113 FND_MESSAGE.SET_NAME('FTE',l_message_name);
114 FND_MSG_PUB.ADD;
115 g_msg_count := g_msg_count + 1;
116 END IF;
117 ELSE
118 FND_MESSAGE.SET_NAME('FTE','FTE_UNEXPECTED_ERROR');
119 FND_MSG_PUB.ADD;
120 g_msg_count := g_msg_count + 1;
121 END IF;
122 */
123 END;
124
125 PROCEDURE printf(p_msg IN VARCHAR2) IS
126 BEGIN
127 IF (utl_file.is_open(g_file_ptr)) THEN
128 utl_file.put_line(g_file_ptr,p_msg);
129 --utl_file.put_line(g_file_ptr,'<L>'||p_msg||'</L>');
130 utl_file.fflush(g_file_ptr);
131 END IF;
132 END printf;
133
134 PROCEDURE print_msg( p_log_level IN NUMBER DEFAULT G_LOG, p_msg IN VARCHAR2 )
135 IS
136 BEGIN
137 IF (g_debug) THEN
138 IF (g_log_level >= p_log_level) THEN
139 IF (g_debug_mode = 'CONC') THEN
140 FND_FILE.put_line(FND_FILE.LOG,p_msg);
141 ELSIF (g_debug_mode = 'FILE') THEN
142 --printf(p_msg);
143 printf('<L>'||p_msg||'</L>');
144 ELSE
145 --dbms_output.put_line(p_msg);
146 null;
147 END IF;
148 END IF;
149 END IF;
150 END;
151
152 PROCEDURE print_tag( p_log_level IN NUMBER DEFAULT G_LOG, p_msg IN VARCHAR2 )
153 IS
154 BEGIN
155 IF (g_debug) THEN
156 IF (g_log_level >= p_log_level) THEN
157 IF (g_debug_mode = 'CONC') THEN
158 FND_FILE.put_line(FND_FILE.LOG,p_msg);
159 ELSIF (g_debug_mode = 'FILE') THEN
160 printf(p_msg);
161 ELSE
162 --dbms_output.put_line(p_msg);
163 null;
164 END IF;
165 END IF;
166 END IF;
167 END;
168
169 -- flushes the log buffers
170 PROCEDURE flush_logs IS
171 BEGIN
172 IF (g_debug_mode = 'FILE') THEN
173 utl_file.fflush(g_file_ptr);
174 END IF;
175 END flush_logs;
176
177
178 -- flushes the log files. Should be called at all exit points
179 PROCEDURE close_logs IS
180 BEGIN
181 -- IF NOT G_OE_DEBUG THEN
182 -- OE_DEBUG_PUB.Debug_off;
183 -- END IF;
184 printf('<L>PROGRAM END TIME '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||'</L>');
185 print_tag(p_msg => '</PROGRAM_FTE_FREIGHT_PRICING>');
186 IF (g_debug_mode = 'FILE') THEN
187 IF (utl_file.is_open(g_file_ptr)) THEN
188 utl_file.fclose(g_file_ptr);
189 END IF;
190 g_utl_file_name := NULL;
191 ELSIF (g_debug_mode = 'CONC') THEN
192 -- FND_FILE.close;
193 null;
194 ELSE
195 null;
196 END IF;
197 EXCEPTION
198 WHEN OTHERS THEN
199 null;
200 END close_logs;
201
202
203 -- This procedure checks the profile options to:
204 -- check if debuging is turned on for the user
205 -- get the location of the debug trace directory
206 -- It initializes the debug trace file if debug is on
207 -- It should be called at the beginning of each entry point
208
209 PROCEDURE initialize_logging (p_debug_mode IN VARCHAR2 DEFAULT NULL,
210 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
211 x_return_status OUT NOCOPY VARCHAR2)
212 IS
213 l_debug_prof_val VARCHAR2(1);
214 l_qp_debug_prof_val VARCHAR2(1);
215 l_log_level_prof_val VARCHAR2(10);
216 BEGIN
217
218 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
219 -- Initialize message list if p_init_msg_list is set to TRUE.
220 g_msg_count := 0;
221 IF FND_API.to_Boolean( p_init_msg_list ) THEN
222 FND_MSG_PUB.initialize;
223 END IF;
224
225 IF (p_debug_mode IS NOT NULL) THEN
226 g_debug_mode := p_debug_mode;
227 END IF;
228
229 l_debug_prof_val := nvl(FND_PROFILE.value('FTE_PRC_DEBUG_FLAG'),'N');
230 IF (l_debug_prof_val = 'Y') THEN
231 g_debug := true;
232 END IF;
233
234 l_log_level_prof_val := nvl(FND_PROFILE.value('FTE_PRC_LOG_LEVEL'),'1');
235 g_log_level := TO_NUMBER(l_log_level_prof_val);
236
237 IF (g_debug_mode = 'FILE' AND g_debug = true AND g_utl_file_name IS NULL) THEN
238
239
240 --g_utl_file_dir := nvl(FND_PROFILE.value('FTE_PRC_DEBUG_LOG_DIR'),'/sqlcom/log/ftewshg');
241 g_utl_file_dir := FND_PROFILE.value('FTE_PRC_DEBUG_LOG_DIR');
242 IF g_utl_file_dir IS NULL THEN
243
244 --set_exception(p_met=>p_met,p_exc=>p_exc);
245 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_MISSING_LOG_DIR');
246 FND_MSG_PUB.ADD;
247 g_msg_count := g_msg_count + 1;
248 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
249 RETURN;
250
251 END IF;
252 SELECT 'prc_'||to_char(sysdate,'MMDDYYHH24MISS')||'-'||fte_prc_debug_s.nextval|| '.dbg'
253 INTO g_utl_file_name
254 FROM dual;
255 g_file_ptr := utl_file.fopen(g_utl_file_dir, g_utl_file_name, 'w');
256
257 print_tag(p_msg => '<PROGRAM_FTE_FREIGHT_PRICING>');
258 printf('<L>PROGRAM START TIME '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||'</L>');
259
260 -- IF nvl(FND_PROFILE.value('QP_DEBUG'),'N') = 'Y' THEN
261 -- IF NOT OE_DEBUG_PUB.ISDebugOn THEN
262 -- G_OE_DEBUG := FALSE;
263 -- OE_DEBUG_PUB.Debug_On;
264 -- OE_DEBUG_PUB.Initialize;
265 -- ELSE
266 -- G_OE_DEBUG := TRUE;
267 -- END IF;
268 -- printf('<L>The QP Debug File is '|| OE_DEBUG_PUB.Set_Debug_Mode('FILE')||'</L>');
269 -- ELSE
270 -- printf('<L>QP Debug is Off</L>');
271 -- END IF;
272
273 END IF;
274
275 EXCEPTION
276 WHEN others THEN
277 -- x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
278 g_debug := false;
279 -- dbms_output.put_line ('Unexpected error '||SQLCODE||'-'||SQLERRM);
280 END initialize_logging;
281
282 FUNCTION get_log_file_name RETURN VARCHAR2
283 IS
284 l_ret_val VARCHAR2(255);
285 BEGIN
286 IF (g_utl_file_name is NOT NULL) THEN
287 l_ret_val := g_utl_file_dir||'/'||g_utl_file_name;
288 END IF;
289 RETURN l_ret_val;
290 END get_log_file_name;
291
292 FUNCTION get_lookup_meaning (p_lookup_type IN VARCHAR2,
293 p_lookup_code IN VARCHAR2)
294 RETURN VARCHAR2
295 IS
296
297 CURSOR c_meaning IS
298 SELECT flv.meaning
299 FROM fnd_lookup_values flv, fnd_lookup_types flt
300 WHERE flv.lookup_type = flt.lookup_type
301 AND flv.lookup_code = p_lookup_code
302 AND flt.lookup_type = p_lookup_type
303 AND flv.language = USERENV('LANG')
304 AND nvl(flv.start_date_active,sysdate)<=sysdate
305 AND nvl(flv.end_date_active,sysdate)>=sysdate
306 AND flv.enabled_flag = 'Y';
307
308 l_meaning VARCHAR2(240) := NULL;
309
310 BEGIN
311
312 IF (p_lookup_code IS NULL) THEN
313 RETURN NULL;
314 END IF;
315
316 OPEN c_meaning;
317 LOOP
318 FETCH c_meaning INTO l_meaning;
319 EXIT WHEN c_meaning%NOTFOUND;
320 END LOOP;
321 RETURN l_meaning;
322
323 EXCEPTION
324 WHEN others THEN
325 RETURN null;
326
327 END get_lookup_meaning;
328
329 PROCEDURE comma_to_table (
330 p_list IN VARCHAR2,
331 x_tab OUT NOCOPY dbms_utility.uncl_array )
332 IS
333
334 l_temp VARCHAR2(4000);
335 idx NUMBER :=0;
336
337 BEGIN
338 print_msg(G_DBG,'Input String : '||p_list);
339 l_temp := p_list;
340 LOOP
341 idx := idx +1;
342 IF (instr(l_temp,',',1,1) <> 0) THEN
343 x_tab(idx) := substr(l_temp,0,instr(l_temp,',',1,1)-1);
344 l_temp := substr(l_temp,instr(l_temp,',',1,1)+1,length(l_temp));
345 print_msg(G_DBG,'Resulting element at index : '||idx||' is : '||x_tab(idx));
346 print_msg(G_DBG,'Now the string looks like : '||l_temp);
347 ELSE
348 x_tab(idx) := l_temp;
349 print_msg(G_DBG,'Resulting element at index : '||idx||' is : '||x_tab(idx));
350 EXIT;
351 END IF;
352
353 END LOOP;
354 EXCEPTION
355 WHEN others THEN
356 RETURN;
357 END comma_to_table;
358
359 PROCEDURE comma_to_number_table (
360 p_list IN VARCHAR2,
361 x_num_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type )
362 IS
363 l_arr dbms_utility.uncl_array;
364 i NUMBER :=0;
365 BEGIN
366
367 comma_to_table (
368 p_list => p_list,
369 x_tab => l_arr );
370
371 IF (l_arr.COUNT >0) THEN
372 i := l_arr.FIRST;
373 LOOP
374 x_num_tab(i) := to_number(NVL(l_arr(i),'0'));
375 EXIT WHEN i = l_arr.LAST;
376 i := l_arr.NEXT(i);
377 END LOOP;
378 END IF;
379
380 EXCEPTION
381 WHEN others THEN
382 RETURN;
383 END comma_to_number_table;
384
385 PROCEDURE table_to_comma (
386 p_tab IN dbms_utility.uncl_array,
387 x_list OUT NOCOPY VARCHAR2 )
388 IS
389
390 idx NUMBER :=0;
391 l_str VARCHAR2(4000):=NULL;
392
393 BEGIN
394 idx := p_tab.FIRST;
395 IF idx IS NOT NULL THEN
396 LOOP
397 IF (idx = p_tab.FIRST) THEN
398 l_str := p_tab(idx);
399 ELSE
400 l_str := l_str||','||p_tab(idx);
401 END IF;
402 EXIT WHEN idx = p_tab.LAST;
403 idx := p_tab.NEXT(idx);
404 END LOOP;
405 END IF;
406 print_msg(G_DBG,'Converted String : '||l_str);
407 x_list := l_str;
408 EXCEPTION
409 WHEN others THEN
410 RETURN;
411 END table_to_comma;
412
413 PROCEDURE number_table_to_comma (
414 p_num_tab IN wsh_util_core.id_tab_type,
415 x_list OUT NOCOPY VARCHAR2 )
416 IS
417 l_tab dbms_utility.uncl_array;
418 i NUMBER :=0;
419
420 BEGIN
421
422 IF (p_num_tab.COUNT > 0) THEN
423 i := p_num_tab.FIRST;
424 LOOP
425 l_tab(i) := to_char(p_num_tab(i));
426 EXIT WHEN i = p_num_tab.LAST;
427 i := p_num_tab.NEXT(i);
428 END LOOP;
429
430 table_to_comma (
431 p_tab => l_tab,
432 x_list => x_list);
433
434 END IF;
435
436 EXCEPTION
437 WHEN others THEN
438 RETURN;
439
440 END number_table_to_comma;
441
442 FUNCTION get_msg_count RETURN NUMBER
443 IS
444 BEGIN
445 RETURN g_msg_count;
446 END get_msg_count;
447
448 -- bug 2762257
449 PROCEDURE set_price_comp_exit_warn
450 IS
451 l_message_name VARCHAR2(1000);
452 BEGIN
453 set_exception(p_met=>'shipment_price_compare',p_exc=>'g_dummy');
454 l_message_name := get_log_file_name;
455 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_EST_WRN');
456 FND_MESSAGE.SET_TOKEN('LOGFILE',l_message_name);
457 FND_MSG_PUB.ADD;
458 g_msg_count := g_msg_count + 1;
459 END set_price_comp_exit_warn;
460
461 PROCEDURE set_trip_prc_comp_exit_warn
462 IS
463 l_message_name VARCHAR2(1000);
464 BEGIN
465 set_exception(p_met=>'Compare_Trip_Rates',p_exc=>'g_dummy');
466 l_message_name := get_log_file_name;
467 FND_MESSAGE.SET_NAME('FTE','FTE_TRP_PRC_EST_WRN');
468 FND_MESSAGE.SET_TOKEN('LOGFILE',l_message_name);
469 FND_MSG_PUB.ADD;
470 g_msg_count := g_msg_count + 1;
471 END set_trip_prc_comp_exit_warn;
472
473
474 PROCEDURE get_trip_name(
475 p_trip_id IN NUMBER,
476 x_trip_name IN OUT NOCOPY VARCHAR2)
477
478 IS
479
480 CURSOR get_name(c_trip_id IN NUMBER) IS
481 SELECT name
482 FROM wsh_trips
483 WHERE trip_id = c_trip_id;
484
485 BEGIN
486
487 OPEN get_name(p_trip_id);
488 FETCH get_name INTO x_trip_name;
489 CLOSE get_name;
490
491 END get_trip_name;
492
493
494 PROCEDURE get_lane_number(
495 p_lane_id IN NUMBER,
496 x_lane_number IN OUT NOCOPY VARCHAR2)
497
498 IS
499
500 CURSOR get_name(c_lane_id IN NUMBER) IS
501
502 SELECT lane_number
503 FROM fte_lanes
504 WHERE lane_id = c_lane_id;
505
506 BEGIN
507
508 OPEN get_name(p_lane_id);
509 FETCH get_name INTO x_lane_number;
510 CLOSE get_name;
511
512 END get_lane_number;
513
514
515
516 PROCEDURE get_carrier_name(
517 p_carrier_id IN NUMBER,
518 x_carrier_name IN OUT NOCOPY VARCHAR2)
519
520 IS
521
522 CURSOR get_name(c_carrier_id IN NUMBER) IS
523
524 SELECT hz.party_name
525 FROM hz_parties hz,
526 wsh_carriers wc
527 WHERE hz.party_id = wc.carrier_id
528 AND wc.carrier_id = c_carrier_id;
529
530 BEGIN
531
532 OPEN get_name(p_carrier_id);
533 FETCH get_name INTO x_carrier_name;
534 CLOSE get_name;
535
536 END get_carrier_name;
537
538
539 PROCEDURE get_list_header_name(
540 p_list_id IN NUMBER,
541 x_list_name IN OUT NOCOPY VARCHAR2)
542
543 IS
544
545 CURSOR get_name(c_list_id IN NUMBER) IS
546
547 SELECT name
548 FROM qp_list_headers
549 WHERE list_header_id = c_list_id;
550
551 BEGIN
552
553 OPEN get_name(p_list_id);
554 FETCH get_name INTO x_list_name;
555 CLOSE get_name;
556
557 END get_list_header_name;
558
559 PROCEDURE get_delivery_name(
560 p_id IN NUMBER,
561 x_name IN OUT NOCOPY VARCHAR2)
562
563 IS
564
565 CURSOR get_name(c_id IN NUMBER) IS
566
567 SELECT name
568 FROM wsh_new_deliveries
569 WHERE delivery_id = c_id;
570
571 BEGIN
572
573 OPEN get_name(p_id);
574 FETCH get_name INTO x_name;
575 CLOSE get_name;
576
577 END get_delivery_name;
578
579
580
581 --
582 -- Procedure setmsg
583 -- Used to add a message to the message stack
584 -- p_api -> calling program name
585 -- p_exc -> exception name (form g_... )
586 -- p_msg_type -> 'E' - Error (default), 'W'-Warning, 'U'- unexpected error
587 -- p_trip_id, ... -> tokens
588 --
589
590 PROCEDURE setmsg (p_api IN VARCHAR2,
591 p_exc IN VARCHAR2,
592 p_msg_name IN VARCHAR2 DEFAULT NULL,
593 p_msg_type IN VARCHAR2 DEFAULT 'E',
594 p_trip_id IN NUMBER DEFAULT NULL,
595 p_stop_id IN NUMBER DEFAULT NULL,
596 p_delivery_id IN NUMBER DEFAULT NULL,
597 p_delivery_leg_id IN NUMBER DEFAULT NULL,
598 p_delivery_detail_id IN NUMBER DEFAULT NULL,
599 p_carrier_id IN NUMBER DEFAULT NULL,
600 p_location_id IN NUMBER DEFAULT NULL,
601 p_list_header_id IN NUMBER DEFAULT NULL,
602 p_lane_id IN NUMBER DEFAULT NULL,
603 p_schedule_id IN NUMBER DEFAULT NULL,
604 p_move_id IN NUMBER DEFAULT NULL)
605 IS
606 l_message_name VARCHAR2(255);
607 l_name VARCHAR2(30);
608 BEGIN
609 IF p_msg_name is NULL THEN
610 l_message_name := 'FTE'||UPPER(SUBSTR(p_exc,2,LENGTH(p_exc)));
611 ELSE
612 l_message_name := p_msg_name;
613 END IF;
614
615 FND_MESSAGE.SET_NAME('FTE',l_message_name);
616 --SUJITH 2-Oct-2003 not showing PROGRAM_UNIT
617 --IF (p_api IS NOT NULL) THEN
618 -- FND_MESSAGE.SET_TOKEN('PROGRAM_UNIT_NAME', p_api);
619 --END IF;
620 -- FND_MSG_PUB.ADD;
621 g_msg_count := g_msg_count + 1;
622
623 l_name:=NULL;
624
625 IF p_trip_id IS NOT NULL
626 THEN
627 get_trip_name(p_trip_id=>p_trip_id,
628 x_trip_name=> l_name);
629 IF (l_name IS NULL)
630 THEN
631 FND_MESSAGE.SET_TOKEN('TRIP_ID', p_trip_id);
632 ELSE
633 FND_MESSAGE.SET_TOKEN('TRIP_ID', l_name);
634 END IF;
635 END IF;
636 IF p_stop_id IS NOT NULL
637 THEN
638 FND_MESSAGE.SET_TOKEN('STOP_ID', p_stop_id);
639 END IF;
640 IF p_delivery_id IS NOT NULL
641 THEN
642 get_delivery_name(p_id=>p_delivery_id,
643 x_name=> l_name);
644 IF (l_name IS NULL)
645 THEN
646 FND_MESSAGE.SET_TOKEN('DELIVERY_ID', p_delivery_id);
647 ELSE
648 FND_MESSAGE.SET_TOKEN('DELIVERY_ID', l_name);
649 END IF;
650 END IF;
651 IF p_delivery_detail_id IS NOT NULL
652 THEN
653 FND_MESSAGE.SET_TOKEN('DELIVERY_DETAIL_ID', p_delivery_detail_id);
654 END IF;
655 IF p_delivery_leg_id IS NOT NULL
656 THEN
657 FND_MESSAGE.SET_TOKEN('DELIVERY_LEG_ID', p_delivery_leg_id);
658 END IF;
659 IF p_carrier_id IS NOT NULL
660 THEN
661
662 get_carrier_name(p_carrier_id=>p_carrier_id,
663 x_carrier_name=> l_name);
664 IF (l_name IS NULL)
665 THEN
666 FND_MESSAGE.SET_TOKEN('CARRIER_ID', p_carrier_id);
667 ELSE
668 FND_MESSAGE.SET_TOKEN('CARRIER_ID', l_name);
669 END IF;
670
671
672
673 END IF;
674 IF p_location_id IS NOT NULL
675 THEN
676 FND_MESSAGE.SET_TOKEN('LOCATION_ID', p_location_id);
677 END IF;
678
679
680 IF p_list_header_id IS NOT NULL
681 THEN
682
683 get_list_header_name(p_list_id=>p_list_header_id,
684 x_list_name=> l_name);
685 IF (l_name IS NULL)
686 THEN
687 FND_MESSAGE.SET_TOKEN('LIST_HEADER_ID', p_list_header_id);
688 ELSE
689 FND_MESSAGE.SET_TOKEN('LIST_HEADER_ID', l_name);
690 END IF;
691
692
693 END IF;
694
695
696 IF p_lane_id IS NOT NULL
697 THEN
698
699 get_lane_number(p_lane_id=>p_lane_id,
700 x_lane_number=> l_name);
701 IF (l_name IS NULL)
702 THEN
703 FND_MESSAGE.SET_TOKEN('LANE_ID', p_lane_id);
704 ELSE
705 FND_MESSAGE.SET_TOKEN('LANE_ID', l_name);
706 END IF;
707
708
709 END IF;
710 IF p_schedule_id IS NOT NULL
711 THEN
712 FND_MESSAGE.SET_TOKEN('SCHEDULE_ID', p_schedule_id);
713 END IF;
714
715 IF p_move_id IS NOT NULL
716 THEN
717 FND_MESSAGE.SET_TOKEN('MOVE_ID', p_move_id);
718 END IF;
719
720 FND_MSG_PUB.ADD;
721
722 --IF (p_msg_type = 'E') THEN
723 --WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
724 --ELSIF (p_msg_type = 'W') THEN
725 --WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
726 --ELSIF (p_msg_type = 'U') THEN
727 --WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR);
728 --END IF;
729
730
731 EXCEPTION
732 WHEN others THEN
733 null;
734 END setmsg;
735
736
737
738 /*Given a combination of input parameters, this API can be called from
739 anywhere in rating engine. For example some of the combinations are:
740 P_delivery_id+ p_carrier_id - In case of Delivery Rating
741 P_trip_id - In case of Trip rating , carrier_id can be ontained from trip
742 P_location_id + p_carrier_id - In case of OM.
743 */
744
745 PROCEDURE get_currency_code(
746 p_delivery_id IN NUMBER DEFAULT NULL,
747 p_trip_id IN NUMBER DEFAULT NULL,
748 p_location_id IN NUMBER DEFAULT NULL,
749 p_carrier_id IN NUMBER DEFAULT NULL,
750 x_currency_code OUT NOCOPY VARCHAR2 ,
751 x_return_status OUT NOCOPY VARCHAR2 )
752
753 IS
754 l_init_msg_list VARCHAR2(1);
755 l_msg_count NUMBER;
756 l_msg_data VARCHAR2(32767);
757 l_log_level NUMBER := G_DBG;
758 l_method_name VARCHAR2(50) := 'fte_freight_pricing_util.get_currency_code';
759 l_entity_type VARCHAR2(20);
760 l_entity_id NUMBER;
761
762 BEGIN
763
764 l_init_msg_list := fnd_api.g_true;
765 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
766
767 reset_dbg_vars;
768 set_method(l_log_level,l_method_name);
769
770 --R12 Hiding Project
771 FTE_FREIGHT_PRICING.get_currency_code (
772 p_carrier_id=>p_carrier_id,
773 x_currency_code=>x_currency_code,
774 x_return_status=>x_return_status);
775 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS and
776 x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
777 THEN
778
779 print_msg(l_log_level,'get currency code failed');
780 print_msg(l_log_level,'x_return_status '|| x_return_status);
781 raise FND_API.G_EXC_ERROR;
782 END IF;
783
784
785 --R12 Hiding Project
786 /*
787 IF p_delivery_id IS NOT NULL THEN
788 l_entity_type := 'Delivery';
789 l_entity_id := p_delivery_id;
790 ELSIF p_trip_id IS NOT NULL THEN
791 l_entity_type := 'Trip';
792 l_entity_id := p_trip_id;
793 ELSIF p_location_id IS NOT NULL THEN
794 l_entity_type := 'Location';
795 l_entity_id := p_location_id;
796 END IF;
797
798 FTE_MLS_UTIL.GET_CURRENCY_CODE( p_init_msg_list => l_init_msg_list,
799 x_return_status => x_return_status,
800 x_msg_count => l_msg_count,
801 x_msg_data => l_msg_data,
802 x_currency_code => x_currency_code,
803 p_entity_type => l_entity_type,
804 p_entity_id => l_entity_id,
805 p_carrier_id => p_carrier_id
806 );
807
808
809 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS and
810 x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
811 THEN
812
813 print_msg(l_log_level,'get currency code failed');
814 print_msg(l_log_level,'l_msg_data '|| l_msg_data);
815 print_msg(l_log_level,'l_msg_count '|| l_msg_count);
816 raise FND_API.G_EXC_ERROR;
817 END IF;
818 */
819 print_msg(l_log_level,' l_currency_code '|| x_currency_code);
820
821 unset_method(l_log_level,l_method_name);
822
823 EXCEPTION
824 WHEN FND_API.G_EXC_ERROR THEN
825 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
826 set_exception(l_method_name,l_log_level,'FND_API.G_EXC_ERROR');
827 unset_method(l_log_level,l_method_name);
828 WHEN OTHERS THEN
829 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
830 set_exception(l_method_name,G_ERR,'g_others');
831 print_msg(G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
832 unset_method(l_log_level,l_method_name);
833
834 END get_currency_code;
835
836
837
838 FUNCTION convert_uom(from_uom IN VARCHAR2,
839 to_uom IN VARCHAR2,
840 quantity IN NUMBER,
841 item_id IN NUMBER DEFAULT NULL)
842
843 RETURN NUMBER
844 IS
845
846 result NUMBER;
847
848 BEGIN
849
850 IF from_uom = to_uom THEN
851 result := quantity;
852 ELSIF ( (from_uom IS NULL)
853 OR (to_uom IS NULL))
854 THEN
855
856 result := NULL;
857
858 ELSIF (quantity = 0)
859 THEN
860 --This will not be valid for all UOMs (Farenheit to Centigrade)
861 --but should work for UOMs in the context of freight rating
862 result:=0;
863 ELSE
864
865 result := WSH_WV_UTILS.convert_uom(
866 from_uom,
867 to_uom,
868 quantity,
869 0);
870
871 IF result = 0 THEN
872 result := NULL;
873
874 END IF;
875 END IF;
876
877 RETURN result;
878
879 END convert_uom;
880
881
882 END FTE_FREIGHT_PRICING_UTIL;
[Home] [Help]