[Home] [Help]
PACKAGE BODY: APPS.FTE_SS_INTERFACE
Source
1 PACKAGE BODY FTE_SS_INTERFACE AS
2 /* $Header: FTESSITB.pls 120.21 2005/11/04 14:18:32 nltan noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FTE_SS_INTERFACE';
6
7
8 -- For Rel 12 HBHAGAVA
9
10 --{
11 PROCEDURE RATE_SORT_WRAPPER(p_ss_rate_sort_tbl IN FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
12 x_ss_rate_sort_tbl OUT NOCOPY FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type)
13 IS
14
15 l_ss_rate_sort_rec FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_rec;
16
17
18 BEGIN
19
20 x_ss_rate_sort_tbl := FTE_SS_INTERFACE.G_SS_RATE_SORT_RESULTS;
21
22
23
24 EXCEPTION
25
26 WHEN OTHERS THEN
27 wsh_util_core.default_handler('FTE_SS_INTERFACE.RATE_SORT_WRAPPER');
28
29 END RATE_SORT_WRAPPER;
30 --}
31
32
33
34 --{
35 PROCEDURE GET_RANKED_RESULTS( p_rule_id IN NUMBER,
36 x_routing_results OUT NOCOPY FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
37 x_return_status OUT NOCOPY VARCHAR2)
38 IS
39
40
41
42 BEGIN
43
44 -- return back rank list tbl type
45
46 IF (G_RG_DEBUG = 'ON')
47 THEN
48 x_routing_results := G_ROUTING_GUIDE_RESULTS;
49 x_return_status := 'S';
50 ELSE
51 /**
52 FTE_ACS_TRIP_PKG.get_ranked_results(p_rule_id,
53 x_routing_results,
54 x_return_status);
55 */
56 x_return_status := 'S';
57 END IF;
58
59
60 END GET_RANKED_RESULTS;
61 --}
62
63
64 --{
65 PROCEDURE ROUTING_GUIDE_MAIN(x_routing_results OUT NOCOPY FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
66 x_return_status OUT NOCOPY VARCHAR2)
67 IS
68
69
70
71 BEGIN
72
73 -- return back rank list tbl type
74
75 IF (G_SEQ_DEBUG = 'ON')
76 THEN
77 x_routing_results := G_ROUTING_GUIDE_RESULTS;
78 END IF;
79
80 x_return_status := 'S';
81
82 END ROUTING_GUIDE_MAIN;
83 --}
84
85
86 PROCEDURE LOG(p_module_name VARCHAR2,
87 p_text VARCHAR2,
88 p_level VARCHAR2)
89 IS
90
91
92
93 BEGIN
94
95 WSH_DEBUG_SV.logmsg(p_module_name,p_text,p_level);
96 --dbms_output.put_line(p_module_name || ' ' || p_text);
97
98 END LOG;
99
100
101 PROCEDURE DERIVE_INITIAL_SHIPMETHOD(
102 p_rank_list_rec IN FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_rec,
103 x_carrier_id OUT NOCOPY NUMBER,
104 x_service_level OUT NOCOPY VARCHAR2,
105 x_mode_of_transport OUT NOCOPY VARCHAR2)
106 IS
107
108 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_INITIAL_SHIPMETHOD';
109 l_api_version CONSTANT NUMBER := 1.0;
110 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
111 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
112
113
114 BEGIN
115
116 IF l_debug_on THEN
117 WSH_DEBUG_SV.push(l_module_name);
118 END IF;
119
120
121 x_carrier_id := p_rank_list_rec.carrier_id;
122 x_service_level := p_rank_list_rec.service_level;
123 x_mode_of_transport := p_rank_list_rec.mode_of_transport;
124
125 IF l_debug_on
126 THEN
127 LOG(l_module_name,
128 ' InitSMConfig value ' || p_rank_list_rec.INITSMCONFIG || ' , ' ||
129 length(p_rank_list_rec.INITSMCONFIG),
130 WSH_DEBUG_SV.C_PROC_LEVEL);
131 END IF;
132
133
134 IF (length(p_rank_list_rec.INITSMCONFIG) = 1)
135 THEN
136 --{
137 IF (p_rank_list_rec.INITSMCONFIG = 'C')
138 THEN
139 IF l_debug_on
140 THEN
141 LOG(l_module_name,
142 ' Pass in carrier as search option ' ||
143 p_rank_list_rec.carrier_id,
144 WSH_DEBUG_SV.C_PROC_LEVEL);
145 END IF;
146 x_service_level := null;
147 x_mode_of_transport := null;
148
149 --}
150 ELSIF (p_rank_list_rec.INITSMCONFIG = 'S')
151 THEN
152 IF l_debug_on
153 THEN
154 LOG(l_module_name,
155 ' Pass in Service level as search option ' ||
156 p_rank_list_rec.service_level,
157 WSH_DEBUG_SV.C_PROC_LEVEL);
158 END IF;
159 x_carrier_id := null;
160 x_mode_of_transport := null;
161 -- Just service level is part of partial shipmethod
162 ELSIF (p_rank_list_rec.INITSMCONFIG = 'M')
163 THEN
164 --{
165 IF l_debug_on
166 THEN
167 LOG(l_module_name,
168 ' Pass in Mode as search option ' ||
169 p_rank_list_rec.mode_of_transport,
170 WSH_DEBUG_SV.C_PROC_LEVEL);
171 END IF;
172 x_carrier_id := null;
173 x_service_level := null;
174 --}
175 END IF;
176 --}
177 ELSIF (length(p_rank_list_rec.INITSMCONFIG) = 2)
178 THEN
179 --{
180 IF (p_rank_list_rec.INITSMCONFIG = 'CS')
181 THEN
182 -- carrier and service level is part of partial shipmethod
183 IF l_debug_on
184 THEN
185 LOG(l_module_name,
186 ' Carrier and Service level ' ||
187 p_rank_list_rec.carrier_id || ' ' ||
188 p_rank_list_rec.service_level,
189 WSH_DEBUG_SV.C_PROC_LEVEL);
190 END IF;
191 x_mode_of_transport := null;
192
193 ELSIF (p_rank_list_rec.INITSMCONFIG = 'CM')
194 THEN
195 IF l_debug_on
196 THEN
197 LOG(l_module_name,
198 ' Carrier and Mode ' ||
199 p_rank_list_rec.carrier_id || ' ' ||
200 p_rank_list_rec.mode_of_transport,
201 WSH_DEBUG_SV.C_PROC_LEVEL);
202 END IF;
203 x_service_level := null;
204
205 ELSIF (p_rank_list_rec.INITSMCONFIG = 'SM')
206 THEN
207 IF l_debug_on
208 THEN
209 LOG(l_module_name,
210 ' Service level and Mode ' ||
211 p_rank_list_rec.service_level|| ' ' ||
212 p_rank_list_rec.mode_of_Transport,
213 WSH_DEBUG_SV.C_PROC_LEVEL);
214 END IF;
215 x_carrier_id := null;
216 END IF;
217 --}
218 ELSE
219 IF l_debug_on
220 THEN
221 LOG(l_module_name,
222 ' Pass in null for all values ',
223 WSH_DEBUG_SV.C_PROC_LEVEL);
224 END IF;
225
226
227 x_carrier_id := NULL;
228 x_service_level := NULL;
229 x_mode_of_transport := NULL;
230
231 END IF;
232
233 IF l_debug_on THEN
234 WSH_DEBUG_SV.pop(l_module_name);
235 END IF;
236 EXCEPTION
237
238 WHEN OTHERS THEN
239 IF l_debug_on THEN
240 WSH_DEBUG_SV.pop(l_module_name);
241 END IF;
242 END DERIVE_INITIAL_SHIPMETHOD;
243
244
245 PROCEDURE CREATE_SEARCH_CRITERIA_WF(
246 P_API_VERSION_NUMBER IN NUMBER,
247 P_INIT_MSG_LIST IN VARCHAR2,
248 P_COMMIT IN VARCHAR2,
249 P_CALLER IN VARCHAR2,
250 P_FTE_SS_ATTR_REC IN FTE_SS_ATTR_REC,
251 X_SS_RATE_SORT_TAB OUT NOCOPY FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
252 X_SAVE_RANK_LIST OUT NOCOPY VARCHAR2,
253 X_RANK_EXIST_TAB OUT NOCOPY FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
254 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
255 X_MSG_COUNT OUT NOCOPY NUMBER,
256 X_MSG_DATA OUT NOCOPY VARCHAR2)
257 IS
258 --{
259
260
261 --{ Local variables
262
263 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SEARCH_CRITERIA_WF';
264 l_api_version CONSTANT NUMBER := 1.0;
265 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
266 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
267
268
269 l_return_status VARCHAR2(32767);
270 l_msg_count NUMBER;
271 l_msg_data VARCHAR2(32767);
272 l_number_of_warnings NUMBER;
273 l_number_of_errors NUMBER;
274
275 l_rank_list_tbl FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
276 l_routing_results FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
277 l_rank_list_rec FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_rec;
278 l_routing_results_rec FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_rec;
279
280 l_rank_exist_rec FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_rec;
281 l_new_rank_list_rec FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_rec;
282
283 l_tbl_count NUMBER;
284 l_initsmconfig VARCHAR2(3);
285 l_uismconfig VARCHAR2(3);
286
287 l_carrier_id NUMBER;
288 l_service_level VARCHAR2(30);
289 l_mode_of_transport VARCHAR2(30);
290
291
292 --}
293
294 BEGIN
295
296
297 SAVEPOINT CREATE_SEARCH_CRITERIA_WF_PUB;
298 IF l_debug_on THEN
299 WSH_DEBUG_SV.push(l_module_name);
300 END IF;
301
302 IF FND_API.to_Boolean( p_init_msg_list )
303 THEN
304 FND_MSG_PUB.initialize;
305 END IF;
306
307 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
308 x_msg_count := 0;
309 x_msg_data := 0;
310 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
311 l_number_of_warnings := 0;
312 l_number_of_errors := 0;
313
314
315 -- Get the rank list
316 FTE_CARRIER_RANK_LIST_PVT.GET_RANK_LIST(
317 p_init_msg_list => 1.0,
318 x_return_status => l_return_status,
319 x_msg_count => l_msg_count,
320 x_msg_data => l_msg_data,
321 x_ranklist => l_rank_list_tbl,
322 p_trip_id => P_FTE_SS_ATTR_REC.TRIP_ID);
323
324 l_tbl_count := l_rank_list_tbl.COUNT;
325
326 IF l_debug_on
327 THEN
328 LOG(l_module_name,' Carrier Rank list count ' ||
329 l_tbl_count,WSH_DEBUG_SV.C_PROC_LEVEL);
330 LOG(l_module_name,' Append list flag = ' ||
331 P_FTE_SS_ATTR_REC.APPEND_LIST_FLAG,
332 WSH_DEBUG_SV.C_PROC_LEVEL);
333 LOG(l_module_name,' Rule Id = ' ||
334 P_FTE_SS_ATTR_REC.RULE_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
335 LOG(l_module_name,' Existing Rank Id = ' ||
336 P_FTE_SS_ATTR_REC.RANK_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
337 END IF;
338
339 IF (l_tbl_count = 0)
340 THEN
341 --{
342 -- This is not possible for workflow.
343
344 IF l_debug_on
345 THEN
346 LOG(l_module_name,' This is possible when there is nothing in rank list ' ||
347 'and user is doing search serivces from UI. Just add ' ||
348 ' UI level info and search ',
349 WSH_DEBUG_SV.C_PROC_LEVEL);
350 END IF;
351
352 --}
353 ELSIF (l_tbl_count = 1)
354 THEN
355 --{
356 l_rank_list_rec := l_rank_list_tbl(1);
357
358 IF l_debug_on
359 THEN
360 LOG(l_module_name,' Found entry. Source = ' ||
361 l_rank_list_rec.source,
362 WSH_DEBUG_SV.C_PROC_LEVEL);
363 LOG(l_module_name,' InitSMConfig = ' ||
364 l_rank_list_rec.INITSMCONFIG,
365 WSH_DEBUG_SV.C_PROC_LEVEL);
366 LOG(l_module_name,' Lane Id = ' ||
367 l_rank_list_rec.LANE_ID,
368 WSH_DEBUG_SV.C_PROC_LEVEL);
369 END IF;
370
371
372 X_RANK_EXIST_TAB(1) := l_rank_list_rec;
373
374 IF (l_rank_list_rec.source = FTE_CARRIER_RANK_LIST_PVT.S_SOURCE_RG)
375 THEN
376 --{
377 -- First copy the existing service to out tab so that it can be send back
378
379
380 -- there is a service on the rank entry but it is possible to have
381 -- initial shipmethod as partial. So we have to check initsmconfig
382
383 IF (l_rank_list_rec.INITSMCONFIG = 'CSM')
384 THEN
385 --{
386 IF l_debug_on
387 THEN
388 LOG(l_module_name,
389 ' Initial Shipmethod is full ',WSH_DEBUG_SV.C_PROC_LEVEL);
390 END IF;
391
392 -- 1 Full SM / 1 Full SM with Service Case
393 -- Check append list flag
394 IF (P_FTE_SS_ATTR_REC.APPEND_LIST_FLAG = 'Y')
395 THEN
396 --{
397 IF l_debug_on
398 THEN
399 LOG(l_module_name,
400 ' Calling Routing Guide Rule _id ' ||
401 P_FTE_SS_ATTR_REC.RULE_ID,
402 WSH_DEBUG_SV.C_PROC_LEVEL);
403 END IF;
404
405 GET_RANKED_RESULTS(p_rule_id => P_FTE_SS_ATTR_REC.RULE_ID,
406 x_routing_results => l_routing_results,
407 x_return_status => l_return_status);
408
409
410 IF (l_return_status = 'E')
411 THEN
412 RAISE FND_API.G_EXC_ERROR;
413 ELSIF (l_return_status = 'U')
414 THEN
415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
416 END IF;
417
418
419
420 IF l_debug_on
421 THEN
422 LOG(l_module_name,
423 ' Build X_SS_RATE_SORT_TAB for search ',
424 WSH_DEBUG_SV.C_PROC_LEVEL);
425 END IF;
426
427 IF (l_routing_results.COUNT > 0)
428 THEN
429 --{
430
431 FOR i IN l_routing_results.FIRST..l_routing_results.LAST
432 LOOP
433 --{
434 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) :=
435 l_routing_results(i);
436 --}
437 END LOOP;
438 --}
439 END IF;
440
441 --}
442 ELSE
443 --{
444 -- return. we don't have to do anything here.
445 IF l_debug_on
446 THEN
447 LOG(l_module_name,
448 ' Append flag is N return back ',
449 WSH_DEBUG_SV.C_PROC_LEVEL);
450 END IF;
451 --}
452 END IF;
453 --}
454 ELSE
455 --{ -- Initail routing guide condition is partial
456
457 -- initail shipmethod config is partial. So we have to derive
458 -- partial value and then do search based on that.
459 -- make sure we still keep existing serivce in ranklist
460 -- with ranksequence number 1.
461 -- Do not go to routing guide but we have to check
462 -- UI values and see if user changed shipmethod info
463
464 IF l_debug_on
465 THEN
466 LOG(l_module_name,
467 ' Initial configuration is Partial SM. ',
468 WSH_DEBUG_SV.C_PROC_LEVEL);
469 LOG(l_module_name,
470 ' Derive SM information ',
471 WSH_DEBUG_SV.C_PROC_LEVEL);
472 END IF;
473
474 DERIVE_INITIAL_SHIPMETHOD(
475 p_rank_list_rec => l_rank_list_rec,
476 x_carrier_id => l_carrier_id,
477 x_service_level => l_service_level,
478 x_mode_of_transport => l_mode_of_transport);
479
480 IF l_debug_on
481 THEN
482 LOG(l_module_name,
483 ' Pass in these parameters for search. ',
484 WSH_DEBUG_SV.C_PROC_LEVEL);
485 LOG(l_module_name,
486 ' Carrier _id ' || l_carrier_id,WSH_DEBUG_SV.C_PROC_LEVEL);
487 LOG(l_module_name,
488 ' Service level ' || l_service_level,WSH_DEBUG_SV.C_PROC_LEVEL);
489 LOG(l_module_name,
490 ' Mode ' || l_mode_of_transport,WSH_DEBUG_SV.C_PROC_LEVEL);
491 END IF;
492
493 l_new_rank_list_rec.CARRIER_ID := l_carrier_id;
494 l_new_rank_list_rec.SERVICE_LEVEL := l_mode_of_transport;
495 l_new_rank_list_rec.MODE_OF_TRANSPORT := l_service_level;
496 l_new_rank_list_rec.SOURCE := 'RG';
497 l_new_rank_list_rec.INITSMCONFIG := l_rank_list_rec.INITSMCONFIG;
498 l_new_rank_list_rec.SORT := 'RL';
499
500 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) :=
501 l_new_rank_list_rec;
502
503 --}
504 END IF;
505 --}
506 ELSIF (l_rank_list_rec.source = FTE_CARRIER_RANK_LIST_PVT.S_SOURCE_LCSS)
507 THEN
508 --{
509 IF l_debug_on
510 THEN
511 LOG(l_module_name,
512 ' Search using LCSS So just calling rate, sort search for open search',
513 WSH_DEBUG_SV.C_PROC_LEVEL);
514 END IF;
515
516 l_new_rank_list_rec.SOURCE := 'LCSS';
517 l_new_rank_list_rec.INITSMCONFIG := l_rank_list_rec.INITSMCONFIG;
518 l_new_rank_list_rec.SORT := 'UI';
519
520 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) :=
521 l_new_rank_list_rec;
522
523 --}
524 ELSIF (l_rank_list_rec.source = FTE_CARRIER_RANK_LIST_PVT.S_SOURCE_TP)
525 THEN
526 --{
527
528 IF l_debug_on
529 THEN
530 LOG(l_module_name,
531 ' We have a entry from TP So call Routing Guide to get Routing guide results',
532 WSH_DEBUG_SV.C_PROC_LEVEL);
533 LOG(l_module_name,
534 ' We Do not have Rule id so we have to call main Routing guide API ',
535 WSH_DEBUG_SV.C_PROC_LEVEL);
536 END IF;
537
538 -- Routing guide may enter these values into Rank list table directly
539 -- that i still need to resolve
540
541 ROUTING_GUIDE_MAIN(x_routing_results => l_routing_results,
542 x_return_status => l_return_status);
543
544 IF (l_routing_results.COUNT > 0)
545 THEN
546 --{
547
548 FOR i IN l_routing_results.FIRST..l_routing_results.LAST
549 LOOP
550 --{
551 l_routing_results(i).SOURCE := 'RG';
552 l_routing_results(i).SORT := 'RL';
553
554 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) :=
555 l_routing_results(i);
556
557 --}
558 END LOOP;
559 --}
560 END IF;
561
562 --}
563 END IF;
564
565 --}
566 END IF;
567
568 X_SAVE_RANK_LIST := FND_API.G_TRUE;
569
570
571 -- Standard call to get message count and if count is 1,get message info.
572 --
573 FND_MSG_PUB.Count_And_Get
574 (
575 p_count => x_msg_count,
576 p_data => x_msg_data,
577 p_encoded => FND_API.G_FALSE
578 );
579
580
581 --
582 --
583
584 IF l_debug_on THEN
585 WSH_DEBUG_SV.pop(l_module_name);
586 END IF;
587
588 --}
589 EXCEPTION
590 --{
591 WHEN FND_API.G_EXC_ERROR THEN
592 ROLLBACK TO CREATE_SEARCH_CRITERIA_WF_PUB;
593 x_return_status := FND_API.G_RET_STS_ERROR ;
594 FND_MSG_PUB.Count_And_Get
595 (
596 p_count => x_msg_count,
597 p_data => x_msg_data,
598 p_encoded => FND_API.G_FALSE
599 );
600 IF l_debug_on THEN
601 WSH_DEBUG_SV.pop(l_module_name);
602 END IF;
603
604 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
605 ROLLBACK TO CREATE_SEARCH_CRITERIA_WF_PUB;
606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
607 FND_MSG_PUB.Count_And_Get
608 (
609 p_count => x_msg_count,
610 p_data => x_msg_data,
611 p_encoded => FND_API.G_FALSE
612 );
613 IF l_debug_on THEN
614 WSH_DEBUG_SV.pop(l_module_name);
615 END IF;
616 WHEN OTHERS THEN
617 ROLLBACK TO CREATE_SEARCH_CRITERIA_WF_PUB;
618 wsh_util_core.default_handler('FTE_SS_INTERFACE.CREATE_SEARCH_CRITERIA_WF');
619 x_return_status := FND_API.G_RET_STS_ERROR;
620 FND_MSG_PUB.Count_And_Get
621 (
622 p_count => x_msg_count,
623 p_data => x_msg_data,
624 p_encoded => FND_API.G_FALSE
625 );
626 IF l_debug_on THEN
627 WSH_DEBUG_SV.pop(l_module_name);
628 END IF;
629
630 --}
631
632 END CREATE_SEARCH_CRITERIA_WF;
633
634 --}
635
636 PROCEDURE CREATE_SEARCH_CRITERIA_UI(
637 P_API_VERSION_NUMBER IN NUMBER,
638 P_INIT_MSG_LIST IN VARCHAR2,
639 P_COMMIT IN VARCHAR2,
640 P_CALLER IN VARCHAR2,
641 P_FTE_SS_ATTR_REC IN FTE_SS_ATTR_REC,
642 X_LIST_CREATE_TYPE OUT NOCOPY VARCHAR2,
643 X_SS_RATE_SORT_TAB OUT NOCOPY FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
644 X_SAVE_RANK_LIST OUT NOCOPY VARCHAR2,
645 X_RANK_EXIST_TAB OUT NOCOPY FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
646 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
647 X_MSG_COUNT OUT NOCOPY NUMBER,
648 X_MSG_DATA OUT NOCOPY VARCHAR2)
649 IS
650 --{
651
652
653 --{ Local variables
654
655 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SEARCH_CRITERIA_UI';
656 l_api_version CONSTANT NUMBER := 1.0;
657 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
658 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
659
660
661 l_return_status VARCHAR2(32767);
662 l_msg_count NUMBER;
663 l_msg_data VARCHAR2(32767);
664 l_number_of_warnings NUMBER;
665 l_number_of_errors NUMBER;
666
667 -- values existing in rank list table
668 l_rank_list_tbl FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
669 l_rank_list_rec FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_rec;
670
671
672 -- routing guide result table
673 l_routing_results FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
674
675 l_new_rank_list_rec FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_rec;
676 l_temp_rank_tbl FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
677
678 l_tbl_count NUMBER;
679 l_initsmconfig VARCHAR2(3);
680 l_uismconfig VARCHAR2(3);
681
682 l_carrier_id NUMBER;
683 l_service_level VARCHAR2(30);
684 l_mode_of_transport VARCHAR2(30);
685
686 l_sm_modified VARCHAR2(1);
687
688 l_uiShipmethod VARCHAR2(1000);
689 l_rankShipmethod VARCHAR2(1000);
690
691
692 --{Trip update parameters
693 p_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
694 p_trip_info WSH_TRIPS_PVT.Trip_Rec_Type;
695 p_trip_in_rec WSH_TRIPS_GRP.TripInRecType;
696 x_out_tab WSH_TRIPS_GRP.trip_Out_tab_type;
697 --}
698
699 --}
700
701 BEGIN
702
703
704 SAVEPOINT CREATE_SEARCH_CRITERIA_UI_PUB;
705 IF l_debug_on THEN
706 WSH_DEBUG_SV.push(l_module_name);
707 END IF;
708
709 IF FND_API.to_Boolean( p_init_msg_list )
710 THEN
711 FND_MSG_PUB.initialize;
712 END IF;
713
714 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
715 x_msg_count := 0;
716 x_msg_data := 0;
717 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
718 l_number_of_warnings := 0;
719 l_number_of_errors := 0;
720
721
722 -- Get the rank list
723 FTE_CARRIER_RANK_LIST_PVT.GET_RANK_LIST(
724 p_init_msg_list => 1.0,
725 x_return_status => l_return_status,
726 x_msg_count => l_msg_count,
727 x_msg_data => l_msg_data,
728 x_ranklist => l_rank_list_tbl,
729 p_trip_id => P_FTE_SS_ATTR_REC.TRIP_ID);
730
731 l_tbl_count := l_rank_list_tbl.COUNT;
732
733 IF l_debug_on
734 THEN
735 LOG(l_module_name,' Carrier Rank list count ' ||
736 l_tbl_count,WSH_DEBUG_SV.C_PROC_LEVEL);
737 LOG(l_module_name,' Append list flag = ' ||
738 P_FTE_SS_ATTR_REC.APPEND_LIST_FLAG,
739 WSH_DEBUG_SV.C_PROC_LEVEL);
740 LOG(l_module_name,' Rule Id = ' ||
741 P_FTE_SS_ATTR_REC.RULE_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
742 LOG(l_module_name,' Existing Rank Id = ' ||
743 P_FTE_SS_ATTR_REC.RANK_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
744
745 LOG(l_module_name,' P_FTE_SS_ATTR_REC.CARRIER_ID ' || P_FTE_SS_ATTR_REC.CARRIER_ID ,
746 WSH_DEBUG_SV.C_PROC_LEVEL);
747 LOG(l_module_name,' P_FTE_SS_ATTR_REC.SERVICE_LEVEL ' || P_FTE_SS_ATTR_REC.SERVICE_LEVEL,
748 WSH_DEBUG_SV.C_PROC_LEVEL);
749 LOG(l_module_name,' P_FTE_SS_ATTR_REC.MODE_OF_TRANSPORT ' || P_FTE_SS_ATTR_REC.MODE_OF_TRANSPORT,
750 WSH_DEBUG_SV.C_PROC_LEVEL);
751
752
753 END IF;
754
755 IF (l_tbl_count = 0)
756 THEN
757 --{
758 IF l_debug_on
759 THEN
760 LOG(l_module_name,' This is possible when there is nothing in rank list ' ||
761 ' and user does search services from UI ' ||
762 ' In this case just add user pref to search criteira ', WSH_DEBUG_SV.C_PROC_LEVEL);
763 END IF;
764
765 l_new_rank_list_rec.CARRIER_ID := P_FTE_SS_ATTR_REC.CARRIER_ID;
766 l_new_rank_list_rec.SERVICE_LEVEL := P_FTE_SS_ATTR_REC.SERVICE_LEVEL;
767 l_new_rank_list_rec.MODE_OF_TRANSPORT := P_FTE_SS_ATTR_REC.MODE_OF_TRANSPORT;
768 l_new_rank_list_rec.SOURCE := FTE_CARRIER_RANK_LIST_PVT.S_SOURCE_UI;
769 l_new_rank_list_rec.SORT := 'UI';
770
771 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) := l_new_rank_list_rec;
772
773 X_LIST_CREATE_TYPE := 'USER';
774 X_SAVE_RANK_LIST := FND_API.G_FALSE;
775
776 RETURN;
777
778 --}
779 ELSIF (l_tbl_count = 1)
780 THEN
781 --{
782 l_rank_list_rec := l_rank_list_tbl(1);
783
784 IF l_debug_on
785 THEN
786 LOG(l_module_name,' Found entry. Source = ' || l_rank_list_rec.source,
787 WSH_DEBUG_SV.C_PROC_LEVEL);
788 LOG(l_module_name,' InitSMConfig = ' ||l_rank_list_rec.INITSMCONFIG,
789 WSH_DEBUG_SV.C_PROC_LEVEL);
790 LOG(l_module_name,' Lane Id = ' || l_rank_list_rec.LANE_ID,
791 WSH_DEBUG_SV.C_PROC_LEVEL);
792
793 LOG(l_module_name,' l_rank_list_rec.CARRIER_ID ' || l_rank_list_rec.CARRIER_ID ,
794 WSH_DEBUG_SV.C_PROC_LEVEL);
795 LOG(l_module_name,' l_rank_list_rec.SERVICE_LEVEL ' || l_rank_list_rec.SERVICE_LEVEL,
796 WSH_DEBUG_SV.C_PROC_LEVEL);
797 LOG(l_module_name,' l_rank_list_rec.MODE_OF_TRANSPORT ' || l_rank_list_rec.MODE_OF_TRANSPORT,
798 WSH_DEBUG_SV.C_PROC_LEVEL);
799
800 END IF;
801
802
803 IF (l_rank_list_rec.source = FTE_CARRIER_RANK_LIST_PVT.S_SOURCE_RG)
804 THEN
805 --{
806
807 -- there is a service on the rank entry but it is possible to have
808 -- initial shipmethod as partial. So we have to check initsmconfig
809
810 IF (l_rank_list_rec.INITSMCONFIG = 'CSM'
811 AND l_rank_list_rec.LANE_ID IS NOT NULL)
812 THEN
813 --{
814 IF l_debug_on
815 THEN
816 LOG(l_module_name,
817 ' Initial Shipmethod is full ',WSH_DEBUG_SV.C_PROC_LEVEL);
818 END IF;
819
820 IF (l_rank_list_rec.LANE_ID IS NOT NULL)
821 THEN
822 -- First copy the existing service to out tab so that it can be send back
823 X_RANK_EXIST_TAB(1) := l_rank_list_rec;
824 END IF;
825
826 -- 1 Full SM / 1 Full SM with Service Case
827 -- Check append list flag
828 IF (P_FTE_SS_ATTR_REC.APPEND_LIST_FLAG = 'Y')
829 THEN
830 --{
831 IF l_debug_on
832 THEN
833 LOG(l_module_name,
834 ' Calling Routing Guide Rule _id ' ||
835 P_FTE_SS_ATTR_REC.RULE_ID,
836 WSH_DEBUG_SV.C_PROC_LEVEL);
837 END IF;
838
839 GET_RANKED_RESULTS(p_rule_id => P_FTE_SS_ATTR_REC.RULE_ID,
840 x_routing_results => l_routing_results,
841 x_return_status => l_return_status);
842
843
844 IF (l_return_status = 'E')
845 THEN
846 RAISE FND_API.G_EXC_ERROR;
847 ELSIF (l_return_status = 'U')
848 THEN
849 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
850 END IF;
851
852
853
854 IF l_debug_on
855 THEN
856 LOG(l_module_name,' Build X_SS_RATE_SORT_TAB for search ',
857 WSH_DEBUG_SV.C_PROC_LEVEL);
858 END IF;
859
860 IF (l_routing_results.COUNT > 0)
861 THEN
862 --{
863
864 FOR i IN l_routing_results.FIRST..l_routing_results.LAST
865 LOOP
866 --{
867 l_routing_results(i).SORT := 'RL';
868 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) :=
869 l_routing_results(i);
870 --}
871 END LOOP;
872 --}
873 END IF;
874
875 X_SAVE_RANK_LIST := FND_API.G_TRUE;
876
877 --}
878 ELSE
879 --{
880 IF l_debug_on
881 THEN
882 LOG(l_module_name, ' Append flag is N Do not call RG ', WSH_DEBUG_SV.C_PROC_LEVEL);
883 END IF;
884
885
886 IF (l_rank_list_rec.LANE_ID IS NOT NULL)
887 THEN
888 -- First copy the existing service to out tab so that it can be send back
889 X_RANK_EXIST_TAB(1) := l_rank_list_rec;
890 END IF;
891
892 -- add this entry to search criteria. we have to search again
893 -- to get rates and price request id
894 l_rank_list_rec.SORT := 'RL';
895 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) :=
896 l_rank_list_rec;
897
898 X_SAVE_RANK_LIST := FND_API.G_FALSE;
899 --}
900 END IF;
901
902 --}
903 ELSE
904 --{
905
906 IF l_debug_on
907 THEN
908 LOG(l_module_name,
909 ' Initial Shipmethod is Partial or only 1 Full shipmethod ',WSH_DEBUG_SV.C_PROC_LEVEL);
910 LOG(l_module_name,
911 ' l_rank_list_rec.INITSMCONFIG ' || l_rank_list_rec.INITSMCONFIG,WSH_DEBUG_SV.C_PROC_LEVEL);
912
913 END IF;
914
915
916 IF (P_FTE_SS_ATTR_REC.APPEND_LIST_FLAG = 'Y')
917 THEN
918 X_SAVE_RANK_LIST := FND_API.G_TRUE;
919 l_rank_list_rec.SORT := 'RL';
920 l_rank_list_rec.RANK_ID := null;
921 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) := l_rank_list_rec;
922 ELSE
923 X_SAVE_RANK_LIST := FND_API.G_FALSE;
924
925 END IF;
926
927
928 -- blow out the entry from rank list table. This is save because
929 -- if user cancels the transaction, we do not loose routing guide info.
930 -- but user clicks save that means he picked up some service so we do not
931 -- need this partial shipmethod info anyway. We have INITSMCONFIG to find out
932 -- what was the initial config anyway
933
934 FTE_CARRIER_RANK_LIST_PVT.RANK_LIST_ACTION(
935 p_api_version_number => 1.0,
936 p_init_msg_list => FND_API.G_FALSE,
937 x_return_status => l_return_status,
938 x_msg_count => l_msg_count,
939 x_msg_data => l_msg_data,
940 p_action_code => FTE_CARRIER_RANK_LIST_PVT.S_DELETE,
941 p_ranklist => l_temp_rank_tbl,
942 p_trip_id => P_FTE_SS_ATTR_REC.trip_id,
943 p_rank_id => null);
944
945 wsh_util_core.api_post_call(
946 p_return_status =>l_return_status,
947 x_num_warnings =>l_number_of_warnings,
948 x_num_errors =>l_number_of_errors,
949 p_msg_data =>l_msg_data);
950
951 -- and update trip rank id to null. User is going to pick on again.
952 -- calling shipping API
953
954
955 FTE_MLS_WRAPPER.INITIALIZE_TRIP_REC(x_trip_info => p_trip_info);
956
957 -- Update trip information
958 p_trip_info.RANK_ID := NULL;
959 p_trip_info.TRIP_ID := P_FTE_SS_ATTR_REC.trip_id;
960
961 p_trip_info_tab(1) :=p_trip_info;
962 p_trip_in_rec.caller :='FTE_LOAD_TENDER';
963 p_trip_in_rec.phase :=NULL;
964 p_trip_in_rec.action_code :='UPDATE';
965
966 IF l_debug_on
967 THEN
968 WSH_DEBUG_SV.logmsg(l_module_name,' Before calling CREATE_UPDATE_TRIP ' ||
969 ' Updating rank id to null in UIWrapper ' ,
970 WSH_DEBUG_SV.C_PROC_LEVEL);
971 END IF;
972
973
974 WSH_INTERFACE_GRP.Create_Update_Trip
975 (
976 p_api_version_number =>p_api_version_number,
977 p_init_msg_list =>FND_API.G_FALSE,
978 p_commit =>FND_API.G_FALSE,
979 x_return_status =>l_return_status,
980 x_msg_count =>l_msg_count,
981 x_msg_data =>l_msg_data,
982 p_trip_info_tab =>p_trip_info_tab,
983 p_in_rec =>p_trip_in_rec,
984 x_out_tab =>x_out_tab
985 );
986
987 IF l_debug_on
988 THEN
989 WSH_DEBUG_SV.logmsg(l_module_name,' REturn value from Create update trip ' ||
990 l_return_status,
991 WSH_DEBUG_SV.C_PROC_LEVEL);
992 END IF;
993
994 wsh_util_core.api_post_call(
995 p_return_status =>l_return_status,
996 x_num_warnings =>l_number_of_warnings,
997 x_num_errors =>l_number_of_errors,
998 p_msg_data =>l_msg_data);
999
1000
1001 IF l_number_of_errors > 0
1002 THEN
1003 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1004 RAISE FND_API.G_EXC_ERROR;
1005 ELSIF l_number_of_warnings > 0
1006 THEN
1007 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1008 ELSE
1009 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1010 END IF;
1011
1012
1013
1014 --}
1015 END IF;
1016 --}
1017 ELSIF (l_rank_list_rec.source = FTE_CARRIER_RANK_LIST_PVT.S_SOURCE_TP)
1018 THEN
1019 --{
1020
1021 -- Call routing guide if Append flag is set to Y.
1022 IF l_debug_on
1023 THEN
1024 LOG(l_module_name,
1025 ' TP Initial Shipmethod is full ',WSH_DEBUG_SV.C_PROC_LEVEL);
1026 END IF;
1027
1028 X_RANK_EXIST_TAB(1) := l_rank_list_rec;
1029
1030 -- 1 Full SM / 1 Full SM with Service Case
1031 -- Check append list flag
1032 IF (P_FTE_SS_ATTR_REC.APPEND_LIST_FLAG = 'Y')
1033 THEN
1034 --{
1035 IF l_debug_on
1036 THEN
1037 LOG(l_module_name,
1038 ' Calling Routing Guide Rule _id ' ||
1039 P_FTE_SS_ATTR_REC.RULE_ID,
1040 WSH_DEBUG_SV.C_PROC_LEVEL);
1041 END IF;
1042
1043 GET_RANKED_RESULTS(p_rule_id => P_FTE_SS_ATTR_REC.RULE_ID,
1044 x_routing_results => l_routing_results,
1045 x_return_status => l_return_status);
1046
1047
1048 IF (l_return_status = 'E')
1049 THEN
1050 RAISE FND_API.G_EXC_ERROR;
1051 ELSIF (l_return_status = 'U')
1052 THEN
1053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1054 END IF;
1055
1056 IF l_debug_on
1057 THEN
1058 LOG(l_module_name,' Build X_SS_RATE_SORT_TAB for search ',
1059 WSH_DEBUG_SV.C_PROC_LEVEL);
1060 END IF;
1061
1062 IF (l_routing_results.COUNT > 0)
1063 THEN
1064 --{
1065
1066 FOR i IN l_routing_results.FIRST..l_routing_results.LAST
1067 LOOP
1068 --{
1069 l_routing_results(i).SORT := 'RL';
1070 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) :=
1071 l_routing_results(i);
1072 --}
1073 END LOOP;
1074 --}
1075 END IF;
1076
1077 X_SAVE_RANK_LIST := FND_API.G_TRUE;
1078
1079 --}
1080 ELSE
1081 --{
1082 IF l_debug_on
1083 THEN
1084 LOG(l_module_name, ' Append flag is N Do not call RG ', WSH_DEBUG_SV.C_PROC_LEVEL);
1085 END IF;
1086
1087
1088 X_SAVE_RANK_LIST := FND_API.G_FALSE;
1089 --}
1090 END IF;
1091 --}
1092 ELSIF (l_rank_list_rec.source = FTE_CARRIER_RANK_LIST_PVT.S_SOURCE_UI)
1093 THEN
1094 --{
1095
1096 IF l_debug_on
1097 THEN
1098 LOG(l_module_name,
1099 ' Rank list created manually add 1 entry to existing services list ',
1100 WSH_DEBUG_SV.C_PROC_LEVEL);
1101 END IF;
1102
1103 IF (l_rank_list_rec.LANE_ID IS NOT NULL)
1104 THEN
1105 -- First copy the existing service to out tab so that it can be send back
1106 X_RANK_EXIST_TAB(1) := l_rank_list_rec;
1107 END IF;
1108
1109 -- add this entry to search criteria. we have to search again
1110 -- to get rates and price request id
1111 l_rank_list_rec.SORT := 'RL';
1112 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) :=
1113 l_rank_list_rec;
1114
1115
1116 --}
1117 ELSIF (l_rank_list_rec.source = FTE_CARRIER_RANK_LIST_PVT.S_SOURCE_LCSS)
1118 THEN
1119 --{
1120 IF l_debug_on
1121 THEN
1122 LOG(l_module_name,
1123 ' Search using LCSS So just calling rate, sort search for open search',
1124 WSH_DEBUG_SV.C_PROC_LEVEL);
1125 END IF;
1126
1127 IF (l_rank_list_rec.LANE_ID IS NOT NULL)
1128 THEN
1129 -- First copy the existing service to out tab so that it can be send back
1130 X_RANK_EXIST_TAB(1) := l_rank_list_rec;
1131 END IF;
1132
1133
1134 l_new_rank_list_rec.SOURCE := 'LCSS';
1135 l_new_rank_list_rec.INITSMCONFIG := l_rank_list_rec.INITSMCONFIG;
1136 l_new_rank_list_rec.SORT := 'UI';
1137
1138 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) :=
1139 l_new_rank_list_rec;
1140
1141 X_SAVE_RANK_LIST := FND_API.G_TRUE;
1142 X_LIST_CREATE_TYPE := 'SYSTEM';
1143
1144 END IF;
1145
1146
1147 -- Check if UI level shipmethod is different than the
1148 -- one in rank list. If different then we have to search based on that
1149 IF l_debug_on
1150 THEN
1151 LOG(l_module_name,' Checking If UI level is different than the rank list entry ',
1152 WSH_DEBUG_SV.C_PROC_LEVEL);
1153 LOG(l_module_name,' l_rank_list_rec.CARRIER_ID ' || l_rank_list_rec.CARRIER_ID ,
1154 WSH_DEBUG_SV.C_PROC_LEVEL);
1155 LOG(l_module_name,' l_rank_list_rec.SERVICE_LEVEL ' || l_rank_list_rec.SERVICE_LEVEL,
1156 WSH_DEBUG_SV.C_PROC_LEVEL);
1157 LOG(l_module_name,' l_rank_list_rec.MODE_OF_TRANSPORT ' || l_rank_list_rec.MODE_OF_TRANSPORT,
1158 WSH_DEBUG_SV.C_PROC_LEVEL);
1159 LOG(l_module_name,' P_FTE_SS_ATTR_REC.CARRIER_ID ' || P_FTE_SS_ATTR_REC.CARRIER_ID ,
1160 WSH_DEBUG_SV.C_PROC_LEVEL);
1161 LOG(l_module_name,' P_FTE_SS_ATTR_REC.SERVICE_LEVEL ' || P_FTE_SS_ATTR_REC.SERVICE_LEVEL,
1162 WSH_DEBUG_SV.C_PROC_LEVEL);
1163 LOG(l_module_name,' P_FTE_SS_ATTR_REC.MODE_OF_TRANSPORT ' || P_FTE_SS_ATTR_REC.MODE_OF_TRANSPORT,
1164 WSH_DEBUG_SV.C_PROC_LEVEL);
1165
1166 END IF;
1167
1168
1169 IF ((P_FTE_SS_ATTR_REC.CARRIER_ID = l_rank_list_rec.CARRIER_ID)
1170 AND (P_FTE_SS_ATTR_REC.SERVICE_LEVEL = l_rank_list_rec.SERVICE_LEVEL)
1171 AND (P_FTE_SS_ATTR_REC.MODE_OF_TRANSPORT = l_rank_list_rec.MODE_OF_TRANSPORT))
1172 THEN
1173 --{
1174
1175
1176 IF l_debug_on
1177 THEN
1178 LOG(l_module_name,' Not Different ',WSH_DEBUG_SV.C_PROC_LEVEL);
1179 END IF;
1180 --}
1181 ELSE
1182 --{
1183
1184 IF l_debug_on
1185 THEN
1186 LOG(l_module_name,' Different ',WSH_DEBUG_SV.C_PROC_LEVEL);
1187 END IF;
1188
1189
1190 l_new_rank_list_rec.CARRIER_ID := P_FTE_SS_ATTR_REC.CARRIER_ID;
1191 l_new_rank_list_rec.SERVICE_LEVEL := P_FTE_SS_ATTR_REC.SERVICE_LEVEL;
1192 l_new_rank_list_rec.MODE_OF_TRANSPORT := P_FTE_SS_ATTR_REC.MODE_OF_TRANSPORT;
1193 l_new_rank_list_rec.SOURCE := FTE_CARRIER_RANK_LIST_PVT.S_SOURCE_UI;
1194 l_new_rank_list_rec.SORT := 'UI';
1195
1196 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) := l_new_rank_list_rec;
1197
1198 --}
1199 END IF;
1200 --}
1201 ELSIF (l_tbl_count > 1)
1202 THEN
1203 --{
1204
1205 l_sm_modified := 'Y';
1206
1207 FOR i IN l_rank_list_tbl.FIRST..l_rank_list_tbl.LAST
1208 LOOP
1209 --{
1210
1211 -- Add to rank exist only if it has a lane
1212
1213 IF (l_rank_list_tbl(i).LANE_ID IS NOT NULL)
1214 THEN
1215
1216 X_RANK_EXIST_TAB(X_RANK_EXIST_TAB.COUNT) := l_rank_list_tbl(i);
1217
1218 -- add this to search parameters.
1219 l_rank_list_tbl(i).SORT := 'RL';
1220 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) := l_rank_list_tbl(i);
1221 X_LIST_CREATE_TYPE := 'SYSTEM';
1222 ELSE
1223 -- add it to search parameter. we have to search based on this
1224 -- SM
1225
1226 IF l_debug_on
1227 THEN
1228 WSH_DEBUG_SV.logmsg(l_module_name,' X_LIST_CREATE_TYPE ' ||
1229 X_LIST_CREATE_TYPE,WSH_DEBUG_SV.C_PROC_LEVEL);
1230 END IF;
1231
1232 IF (X_LIST_CREATE_TYPE = 'SYSTEM')
1233 THEN
1234 IF l_debug_on
1235 THEN
1236 WSH_DEBUG_SV.logmsg(l_module_name,' X_LIST_CREATE_TYPE ' ||
1237 'SYSTEM' ,WSH_DEBUG_SV.C_PROC_LEVEL);
1238 END IF;
1239 ELSE
1240 X_LIST_CREATE_TYPE := 'USER';
1241 END IF;
1242
1243 l_rank_list_tbl(i).SORT := 'RL';
1244 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) := l_rank_list_tbl(i);
1245
1246 -- delete this from rank list table.
1247 DELETE FTE_CARRIER_RANK_LIST
1248 WHERE RANK_ID = l_rank_list_tbl(i).RANK_ID;
1249
1250 IF (P_FTE_SS_ATTR_REC.RANK_ID = l_rank_list_tbl(i).RANK_ID)
1251 THEN
1252
1253 FTE_MLS_WRAPPER.INITIALIZE_TRIP_REC(x_trip_info => p_trip_info);
1254
1255 -- Update trip information
1256 p_trip_info.RANK_ID := NULL;
1257 p_trip_info.TRIP_ID := P_FTE_SS_ATTR_REC.trip_id;
1258
1259 p_trip_info_tab(1) :=p_trip_info;
1260 p_trip_in_rec.caller :='FTE_LOAD_TENDER';
1261 p_trip_in_rec.phase :=NULL;
1262 p_trip_in_rec.action_code :='UPDATE';
1263
1264 IF l_debug_on
1265 THEN
1266 WSH_DEBUG_SV.logmsg(l_module_name,' Before calling CREATE_UPDATE_TRIP ' ||
1267 ' Setting rank id to null in UIWrapper ',
1268 WSH_DEBUG_SV.C_PROC_LEVEL);
1269 END IF;
1270
1271
1272 WSH_INTERFACE_GRP.Create_Update_Trip
1273 (
1274 p_api_version_number =>p_api_version_number,
1275 p_init_msg_list =>FND_API.G_FALSE,
1276 p_commit =>FND_API.G_FALSE,
1277 x_return_status =>l_return_status,
1278 x_msg_count =>l_msg_count,
1279 x_msg_data =>l_msg_data,
1280 p_trip_info_tab =>p_trip_info_tab,
1281 p_in_rec =>p_trip_in_rec,
1282 x_out_tab =>x_out_tab
1283 );
1284
1285 IF l_debug_on
1286 THEN
1287 WSH_DEBUG_SV.logmsg(l_module_name,' REturn value from Create update trip ' ||
1288 l_return_status,
1289 WSH_DEBUG_SV.C_PROC_LEVEL);
1290 END IF;
1291
1292 wsh_util_core.api_post_call(
1293 p_return_status =>l_return_status,
1294 x_num_warnings =>l_number_of_warnings,
1295 x_num_errors =>l_number_of_errors,
1296 p_msg_data =>l_msg_data);
1297
1298 IF l_number_of_errors > 0
1299 THEN
1300 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1301 RAISE FND_API.G_EXC_ERROR;
1302 ELSIF l_number_of_warnings > 0
1303 THEN
1304 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1305 ELSE
1306 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1307 END IF;
1308
1309
1310 END IF;
1311
1312 END IF;
1313
1314
1315
1316 -- Check if shipmethod on the UI is different from
1317 -- rank list existing shipmethods
1318 -- If l_is_sm_modified is set to Y then we have UI sm modified
1319 -- so we do not have to check anymore
1320
1321 IF (l_sm_modified = 'Y')
1322 THEN
1323
1324
1325 IF ((P_FTE_SS_ATTR_REC.CARRIER_ID = l_rank_list_tbl(i).CARRIER_ID)
1326 AND (P_FTE_SS_ATTR_REC.SERVICE_LEVEL = l_rank_list_tbl(i).SERVICE_LEVEL)
1327 AND (P_FTE_SS_ATTR_REC.MODE_OF_TRANSPORT = l_rank_list_tbl(i).MODE_OF_TRANSPORT))
1328 THEN
1329 --{
1330 IF l_debug_on
1331 THEN
1332 LOG(l_module_name,' Shipmethod did not modify ',WSH_DEBUG_SV.C_PROC_LEVEL);
1333 END IF;
1334
1335 l_sm_modified := 'N';
1336 END IF;
1337 END IF;
1338 --}
1339 END LOOP;
1340
1341 IF (l_sm_modified = 'Y')
1342 THEN
1343
1344 l_new_rank_list_rec.CARRIER_ID := P_FTE_SS_ATTR_REC.CARRIER_ID;
1345 l_new_rank_list_rec.SERVICE_LEVEL := P_FTE_SS_ATTR_REC.SERVICE_LEVEL;
1346 l_new_rank_list_rec.MODE_OF_TRANSPORT := P_FTE_SS_ATTR_REC.MODE_OF_TRANSPORT;
1347
1348 l_new_rank_list_rec.SOURCE := FTE_CARRIER_RANK_LIST_PVT.S_SOURCE_UI;
1349
1350 IF (P_FTE_SS_ATTR_REC.CARRIER_ID IS NULL
1351 AND P_FTE_SS_ATTR_REC.SERVICE_LEVEL IS NULL
1352 AND P_FTE_SS_ATTR_REC.MODE_OF_TRANSPORT IS NULL)
1353 THEN
1354 l_new_rank_list_rec.SOURCE := FTE_CARRIER_RANK_LIST_PVT.S_SOURCE_LCSS;
1355 END IF;
1356
1357 l_new_rank_list_rec.SORT := 'UI';
1358
1359 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) :=
1360 l_new_rank_list_rec;
1361
1362 END IF;
1363
1364 X_SAVE_RANK_LIST := FND_API.G_TRUE;
1365
1366
1367 --}
1368 END IF;
1369
1370 IF l_debug_on
1371 THEN
1372 LOG(l_module_name,
1373 ' save rank list Value ' || X_SAVE_RANK_LIST,
1374 WSH_DEBUG_SV.C_PROC_LEVEL);
1375 END IF;
1376
1377
1378 -- Standard call to get message count and if count is 1,get message info.
1379 --
1380 FND_MSG_PUB.Count_And_Get
1381 (
1382 p_count => x_msg_count,
1383 p_data => x_msg_data,
1384 p_encoded => FND_API.G_FALSE
1385 );
1386
1387
1388 --
1389 --
1390
1391 IF l_debug_on THEN
1392 WSH_DEBUG_SV.pop(l_module_name);
1393 END IF;
1394
1395 --}
1396 EXCEPTION
1397 --{
1398 WHEN FND_API.G_EXC_ERROR THEN
1399 ROLLBACK TO CREATE_SEARCH_CRITERIA_UI_PUB;
1400 x_return_status := FND_API.G_RET_STS_ERROR ;
1401 FND_MSG_PUB.Count_And_Get
1402 (
1403 p_count => x_msg_count,
1404 p_data => x_msg_data,
1405 p_encoded => FND_API.G_FALSE
1406 );
1407 IF l_debug_on THEN
1408 WSH_DEBUG_SV.pop(l_module_name);
1409 END IF;
1410
1411 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1412 ROLLBACK TO CREATE_SEARCH_CRITERIA_UI_PUB;
1413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1414 FND_MSG_PUB.Count_And_Get
1415 (
1416 p_count => x_msg_count,
1417 p_data => x_msg_data,
1418 p_encoded => FND_API.G_FALSE
1419 );
1420 IF l_debug_on THEN
1421 WSH_DEBUG_SV.pop(l_module_name);
1422 END IF;
1423 WHEN OTHERS THEN
1424 ROLLBACK TO CREATE_SEARCH_CRITERIA_UI_PUB;
1425 wsh_util_core.default_handler('FTE_SS_INTERFACE.CREATE_SEARCH_CRITERIA_UI');
1426 x_return_status := FND_API.G_RET_STS_ERROR;
1427 FND_MSG_PUB.Count_And_Get
1428 (
1429 p_count => x_msg_count,
1430 p_data => x_msg_data,
1431 p_encoded => FND_API.G_FALSE
1432 );
1433 IF l_debug_on THEN
1434 WSH_DEBUG_SV.pop(l_module_name);
1435 END IF;
1436
1437 --}
1438
1439 END CREATE_SEARCH_CRITERIA_UI;
1440
1441 --}
1442
1443
1444
1445 PROCEDURE SEARCH_SERVICES(
1446 P_INIT_MSG_LIST IN VARCHAR2,
1447 P_API_VERSION_NUMBER IN NUMBER,
1448 P_COMMIT IN VARCHAR2,
1449 P_CALLER IN VARCHAR2,
1450 P_FTE_SS_ATTR_REC IN FTE_SS_ATTR_REC,
1451 X_RATING_REQUEST_ID OUT NOCOPY NUMBER,
1452 X_LIST_CREATE_TYPE OUT NOCOPY VARCHAR2,
1453 X_SS_RATE_SORT_TAB OUT NOCOPY FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
1454 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1455 X_MSG_COUNT OUT NOCOPY NUMBER,
1456 X_MSG_DATA OUT NOCOPY VARCHAR2)
1457 IS
1458 --{
1459
1460
1461 --{ Local variables
1462
1463
1464 l_api_name CONSTANT VARCHAR2(30) := 'SEARCH_SERVICES';
1465 l_api_version CONSTANT NUMBER := 1.0;
1466 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1467 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
1468
1469
1470 l_return_status VARCHAR2(1);
1471 l_msg_count NUMBER;
1472 l_msg_data VARCHAR2(32767);
1473 l_number_of_warnings NUMBER;
1474 l_number_of_errors NUMBER;
1475
1476 l_list_create_type VARCHAR2(32767);
1477
1478 l_search_criteria_tbl FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
1479 l_search_results_tbl FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
1480 l_existing_service_tbl FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
1481 l_existing_sm_tbl FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
1482
1483 l_manual_services_tbl FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
1484
1485 l_rank_list_in_param_tbl FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
1486
1487 l_save_rank_list VARCHAR2(1); -- Search Services
1488
1489 l_rank_list_action VARCHAR2(30);
1490
1491 l_rating_request_id NUMBER;
1492
1493 --{Trip update parameters
1494 p_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
1495 p_trip_info WSH_TRIPS_PVT.Trip_Rec_Type;
1496 p_trip_in_rec WSH_TRIPS_GRP.TripInRecType;
1497 x_out_tab WSH_TRIPS_GRP.trip_Out_tab_type;
1498 --}
1499
1500
1501 --}
1502
1503 BEGIN
1504
1505
1506 SAVEPOINT SEARCH_SERVICES_PUB;
1507 IF l_debug_on THEN
1508 WSH_DEBUG_SV.push(l_module_name);
1509 END IF;
1510
1511 IF FND_API.to_Boolean( p_init_msg_list )
1512 THEN
1513 FND_MSG_PUB.initialize;
1514 END IF;
1515
1516 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1517 x_msg_count := 0;
1518 x_msg_data := 0;
1519 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1520 l_number_of_warnings := 0;
1521 l_number_of_errors := 0;
1522
1523
1524 IF l_debug_on
1525 THEN
1526 LOG(l_module_name,' P_caller ' ||
1527 p_caller,WSH_DEBUG_SV.C_PROC_LEVEL);
1528 LOG(l_module_name,' Append list flag ' ||
1529 P_FTE_SS_ATTR_REC.append_list_flag,WSH_DEBUG_SV.C_PROC_LEVEL);
1530 END IF;
1531
1532 IF (p_caller = S_CALLER_WF)
1533 THEN
1534 --{
1535
1536 -- Use NVL
1537 IF (P_FTE_SS_ATTR_REC.append_list_flag = 'N' OR
1538 P_FTE_SS_ATTR_REC.append_list_flag IS NULL)
1539 THEN
1540 --{
1541 IF l_debug_on
1542 THEN
1543 LOG(l_module_name,' Return back because we cannot expand ' ||
1544 p_caller,WSH_DEBUG_SV.C_PROC_LEVEL);
1545 END IF;
1546
1547 return;
1548 --}
1549 END IF;
1550
1551
1552 IF l_debug_on
1553 THEN
1554 LOG(l_module_name,' Call FTE_SS_INTERFACE.CREATE_SEARCH_CRITERIA_WF ',
1555 WSH_DEBUG_SV.C_PROC_LEVEL);
1556 END IF;
1557
1558 CREATE_SEARCH_CRITERIA_WF(
1559 P_API_VERSION_NUMBER => 1.0,
1560 P_INIT_MSG_LIST => FND_API.G_FALSE,
1561 P_COMMIT => p_commit,
1562 P_CALLER => p_caller,
1563 P_FTE_SS_ATTR_REC => p_fte_ss_attr_rec,
1564 X_SS_RATE_SORT_TAB => l_search_criteria_tbl,
1565 X_SAVE_RANK_LIST => l_save_rank_list,
1566 X_RANK_EXIST_TAB => l_existing_service_tbl,
1567 X_RETURN_STATUS => l_return_status,
1568 X_MSG_COUNT => l_msg_count,
1569 X_MSG_DATA => l_msg_data);
1570
1571 IF l_debug_on
1572 THEN
1573 LOG(l_module_name,' Result FTE_SS_INTERFACE.CREATE_SEARCH_CRITERIA_WF ' ||
1574 l_return_status,
1575 WSH_DEBUG_SV.C_PROC_LEVEL);
1576 END IF;
1577
1578 wsh_util_core.api_post_call(
1579 p_return_status =>l_return_status,
1580 x_num_warnings =>l_number_of_warnings,
1581 x_num_errors =>l_number_of_errors,
1582 p_msg_data =>l_msg_data);
1583
1584 IF l_debug_on
1585 THEN
1586 LOG(l_module_name,' copy existing service in the rank list to out tab X_SS_RATE_SORT TAB ' ||
1587 l_existing_service_tbl.COUNT,
1588 WSH_DEBUG_SV.C_PROC_LEVEL);
1589 END IF;
1590 --}
1591 ELSIF (p_caller = S_CALLER_UI) THEN
1592 --{
1593 IF l_debug_on
1594 THEN
1595 LOG(l_module_name,' Call FTE_SS_INTERFACE.CREATE_SEARCH_CRITERIA_UI ',
1596 WSH_DEBUG_SV.C_PROC_LEVEL);
1597 END IF;
1598
1599 CREATE_SEARCH_CRITERIA_UI(
1600 P_API_VERSION_NUMBER => 1.0,
1601 P_INIT_MSG_LIST => FND_API.G_FALSE,
1602 P_COMMIT => p_commit,
1603 P_CALLER => p_caller,
1604 P_FTE_SS_ATTR_REC => p_fte_ss_attr_rec,
1605 X_LIST_CREATE_TYPE => x_list_create_type,
1606 X_SS_RATE_SORT_TAB => l_search_criteria_tbl,
1607 X_SAVE_RANK_LIST => l_save_rank_list,
1608 X_RANK_EXIST_TAB => l_existing_service_tbl,
1609 X_RETURN_STATUS => l_return_status,
1610 X_MSG_COUNT => l_msg_count,
1611 X_MSG_DATA => l_msg_data);
1612
1613 IF l_debug_on
1614 THEN
1615 LOG(l_module_name,' Result FTE_SS_INTERFACE.CREATE_SEARCH_CRITERIA_UI ' ||
1616 l_return_status,
1617 WSH_DEBUG_SV.C_PROC_LEVEL);
1618 LOG(l_module_name,' List Create Type ' || x_list_create_type,
1619 WSH_DEBUG_SV.C_PROC_LEVEL);
1620
1621 END IF;
1622
1623 wsh_util_core.api_post_call(
1624 p_return_status =>l_return_status,
1625 x_num_warnings =>l_number_of_warnings,
1626 x_num_errors =>l_number_of_errors,
1627 p_msg_data =>l_msg_data);
1628
1629 --}
1630 END IF;
1631
1632
1633 IF l_debug_on
1634 THEN
1635 LOG(l_module_name,' Printing the SEARCH CRITERIA ', WSH_DEBUG_SV.C_PROC_LEVEL);
1636 LOG(l_module_name,' ********************************************** ', WSH_DEBUG_SV.C_PROC_LEVEL);
1637
1638 IF (l_search_criteria_tbl.COUNT > 0)
1639 THEN
1640 --{
1641
1642 FOR i IN l_search_criteria_tbl.FIRST..l_search_criteria_tbl.LAST
1643 LOOP
1644 --{
1645
1646 LOG(l_module_name,' &&&&&&&&&&&&&&&&&&&&&&&& ', WSH_DEBUG_SV.C_PROC_LEVEL);
1647 Log(l_module_name,' l_search_criteria_tbl.RANK_ID ' || l_search_criteria_tbl(i).RANK_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
1648 LOG(l_module_name,' l_search_criteria_tbl.RANK_SEQUENCE ' || l_search_criteria_tbl(i).RANK_SEQUENCE,WSH_DEBUG_SV.C_PROC_LEVEL);
1649 LOG(l_module_name,' l_search_criteria_tbl.CARRIER_ID ' || l_search_criteria_tbl(i).CARRIER_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
1650 Log(l_module_name,' l_search_criteria_tbl.SERVICE_LEVEL ' || l_search_criteria_tbl(i).SERVICE_LEVEL,WSH_DEBUG_SV.C_PROC_LEVEL);
1651 Log(l_module_name,' l_search_criteria_tbl.MODE_OF_TRANSPORT ' || l_search_criteria_tbl(i).MODE_OF_TRANSPORT,WSH_DEBUG_SV.C_PROC_LEVEL);
1652 Log(l_module_name,' l_search_criteria_tbl.SOURCE ' || l_search_criteria_tbl(i).SOURCE,WSH_DEBUG_SV.C_PROC_LEVEL);
1653 Log(l_module_name,' l_search_criteria_tbl.SORT ' || l_search_criteria_tbl(i).SORT,WSH_DEBUG_SV.C_PROC_LEVEL);
1654 Log(l_module_name,' l_search_criteria_tbl.INITSMCONFIG ' || l_search_criteria_tbl(i).INITSMCONFIG,WSH_DEBUG_SV.C_PROC_LEVEL);
1655 LOG(l_module_name,' &&&&&&&&&&&&&&&&&&&&&&&& ', WSH_DEBUG_SV.C_PROC_LEVEL);
1656
1657 --}
1658 END LOOP;
1659 --}
1660 END IF;
1661 LOG(l_module_name,' ********************************************** ', WSH_DEBUG_SV.C_PROC_LEVEL);
1662 LOG(l_module_name,' End Printing the search criteria ', WSH_DEBUG_SV.C_PROC_LEVEL);
1663
1664 END IF;
1665
1666
1667
1668
1669 IF (l_search_criteria_tbl.COUNT > 0)
1670 THEN
1671 --{
1672
1673 IF (G_SEQ_DEBUG = 'ON')
1674 THEN
1675 --{
1676 IF l_debug_on
1677 THEN
1678 LOG(l_module_name,' Calling RATE_SORT_WRAPPER ',
1679 WSH_DEBUG_SV.C_PROC_LEVEL);
1680 END IF;
1681
1682 RATE_SORT_WRAPPER(p_ss_rate_sort_tbl => l_search_criteria_tbl,
1683 x_ss_rate_sort_tbl => l_search_results_tbl);
1684 x_rating_request_id := 121212;
1685 --}
1686 ELSE
1687 --{
1688
1689 IF l_debug_on
1690 THEN
1691 LOG(l_module_name,' Calling FTE_TRIP_RATING_GRP.Search_Rate_Sort ',
1692 WSH_DEBUG_SV.C_PROC_LEVEL);
1693 END IF;
1694
1695
1696 FTE_TRIP_RATING_GRP.Search_Rate_Sort(
1697 p_api_version => 1.0,
1698 p_init_msg_list => FND_API.G_FALSE,
1699 p_ss_rate_sort_tab => l_search_criteria_tbl,
1700 p_ss_rate_sort_atr_rec => P_FTE_SS_ATTR_REC,
1701 x_ss_rate_sort_tab => l_search_results_tbl,
1702 x_rating_request_id => x_rating_request_id,
1703 x_return_status => l_return_status,
1704 x_msg_count => l_msg_count,
1705 x_msg_data => l_msg_data);
1706 --}
1707 END IF;
1708 --}
1709 END IF;
1710
1711 IF l_debug_on
1712 THEN
1713 LOG(l_module_name,' Return message after calling Search Rate sort ' ||
1714 l_return_status,
1715 WSH_DEBUG_SV.C_PROC_LEVEL);
1716 END IF;
1717
1718
1719 wsh_util_core.api_post_call(
1720 p_return_status =>l_return_status,
1721 x_num_warnings =>l_number_of_warnings,
1722 x_num_errors =>l_number_of_errors,
1723 p_msg_data =>l_msg_data);
1724
1725
1726 IF l_debug_on
1727 THEN
1728 LOG(l_module_name,' Existing Service count l_existing_service_tbl.COUNT ' ||
1729 l_existing_service_tbl.COUNT,
1730 WSH_DEBUG_SV.C_PROC_LEVEL);
1731 LOG(l_module_name,' Search Service result count l_search_results_tbl.COUNT ' ||
1732 l_search_results_tbl.COUNT,
1733 WSH_DEBUG_SV.C_PROC_LEVEL);
1734 LOG(l_module_name,' Rating request id ' || x_rating_request_id,
1735 WSH_DEBUG_SV.C_PROC_LEVEL);
1736 END IF;
1737
1738
1739
1740 IF (l_search_results_tbl.COUNT=0)
1741 THEN
1742 --{
1743 IF l_debug_on
1744 THEN
1745 LOG(l_module_name,' search result count is 0 ' ||
1746 l_return_status,
1747 WSH_DEBUG_SV.C_PROC_LEVEL);
1748 END IF;
1749 -- we cannot do anything
1750 --}
1751 ELSIF (x_rating_request_id IS NOT NULL)
1752 THEN
1753 --{ We have to procced only if price request id is not null.
1754
1755 -- Remove duplicated between existing and search resutls
1756
1757 IF (l_existing_service_tbl.COUNT > 0)
1758 THEN
1759 --{
1760
1761 IF l_debug_on
1762 THEN
1763 LOG(l_module_name,' Eliminating Duplicates ',
1764 WSH_DEBUG_SV.C_PROC_LEVEL);
1765
1766 END IF;
1767
1768 -- remove the existing services from l_search_results tbl
1769 FOR i IN l_search_results_tbl.FIRST..l_search_results_tbl.LAST
1770 LOOP
1771 --{
1772 FOR j IN l_existing_service_tbl.FIRST..l_existing_service_tbl.LAST
1773 LOOP
1774 --{
1775 LOG(l_module_name,' Compare Lane Id. l_search_results_tbl ' ||
1776 l_search_results_tbl(i).LANE_ID || ' with l_existing_service_tbl ' ||
1777 l_existing_service_tbl(j).LANE_ID , WSH_DEBUG_SV.C_PROC_LEVEL);
1778
1779 LOG(l_module_name,' Compare VEHICLE. l_search_results_tbl ' ||
1780 l_search_results_tbl(i).VEHICLE_ITEM_ID || ' with l_existing_service_tbl ' ||
1781 l_existing_service_tbl(j).VEHICLE_ITEM_ID, WSH_DEBUG_SV.C_PROC_LEVEL);
1782
1783
1784 IF (l_search_results_tbl(i).LANE_ID <> -99)
1785 THEN
1786 --{
1787 IF (l_search_results_tbl(i).LANE_ID = l_existing_service_tbl(j).LANE_ID)
1788 THEN
1789 --{
1790 IF (((l_search_results_tbl(i).VEHICLE_ITEM_ID IS NULL)
1791 AND (l_existing_service_tbl(j).VEHICLE_ITEM_ID IS NULL))
1792 OR ((l_search_results_tbl(i).VEHICLE_ITEM_ID =
1793 l_existing_service_tbl(j).VEHICLE_ITEM_ID )))
1794 THEN
1795
1796 -- Check if vehicle is same. if yes
1797 IF l_debug_on
1798 THEN
1799 LOG(l_module_name,
1800 ' Remove this service. Do not send it to Rank list ' || l_search_results_tbl(i).LANE_ID, WSH_DEBUG_SV.C_PROC_LEVEL);
1801
1802 END IF;
1803
1804 l_search_results_tbl(i).LANE_ID := -99;
1805 l_search_results_tbl(i).VEHICLE_ITEM_ID := -99;
1806
1807 IF l_debug_on
1808 THEN
1809 LOG(l_module_name,
1810 ' But copy the rate information from search restuls to the existins ' ||
1811 ' because that is the latest rate ' , WSH_DEBUG_SV.C_PROC_LEVEL);
1812 END IF;
1813
1814 l_existing_service_tbl(j).ESTIMATED_RATE :=
1815 l_search_results_tbl(i).ESTIMATED_RATE;
1816 l_existing_service_tbl(j).CURRENCY_CODE :=
1817 l_search_results_tbl(i).CURRENCY_CODE;
1818 l_existing_service_tbl(j).ESTIMATED_TRANSIT_TIME :=
1819 l_search_results_tbl(i).ESTIMATED_TRANSIT_TIME;
1820 l_existing_service_tbl(j).TRANSIT_TIME_UOM :=
1821 l_search_results_tbl(i).TRANSIT_TIME_UOM;
1822
1823 END IF;
1824
1825 --}
1826 END IF;
1827 --}
1828 END IF;
1829 --}
1830 END LOOP;
1831 --}
1832 END LOOP;
1833
1834 -- Set action to APPEND since we are going to append to the list instead
1835 -- of creating scratch.
1836
1837 l_rank_list_action := FTE_CARRIER_RANK_LIST_PVT.S_APPEND;
1838 --}
1839 ELSE
1840 --{
1841 -- no existing service
1842 -- so save everything to RANK LIST TABLE.
1843
1844 IF l_debug_on
1845 THEN
1846 LOG(l_module_name,' Save everything. Except Manual. ' ,
1847 WSH_DEBUG_SV.C_PROC_LEVEL);
1848 END IF;
1849
1850
1851 l_rank_list_action := FTE_CARRIER_RANK_LIST_PVT.S_CREATE;
1852
1853 --}
1854 END IF;
1855
1856
1857 -- Add all existing services to out parameter.
1858
1859 IF (l_existing_service_tbl.COUNT > 0)
1860 THEN
1861 --{
1862 FOR i IN l_existing_service_tbl.FIRST..l_existing_service_tbl.LAST
1863 LOOP
1864 --{
1865 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) := l_existing_service_tbl(i);
1866
1867 IF l_debug_on
1868 THEN
1869 LOG(l_module_name,' Printing existing service ', WSH_DEBUG_SV.C_PROC_LEVEL);
1870 LOG(l_module_name,' ********************************************** ', WSH_DEBUG_SV.C_PROC_LEVEL);
1871
1872
1873 Log(l_module_name,' l_existing_service_tbl.RANK_ID ' || l_existing_service_tbl(i).RANK_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
1874 Log(l_module_name,' l_existing_service_tbl.RANK_SEQUENCE ' || l_existing_service_tbl(i).RANK_SEQUENCE,WSH_DEBUG_SV.C_PROC_LEVEL);
1875 Log(l_module_name,' l_existing_service_tbl.CARRIER_ID ' || l_existing_service_tbl(i).CARRIER_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
1876 Log(l_module_name,' l_existing_service_tbl.SERVICE_LEVEL ' || l_existing_service_tbl(i).SERVICE_LEVEL,WSH_DEBUG_SV.C_PROC_LEVEL);
1877 Log(l_module_name,' l_existing_service_tbl.MODE_OF_TRANSPORT ' || l_existing_service_tbl(i).MODE_OF_TRANSPORT,WSH_DEBUG_SV.C_PROC_LEVEL);
1878 Log(l_module_name,' l_existing_service_tbl.LANE_ID ' || l_existing_service_tbl(i).LANE_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
1879 Log(l_module_name,' l_existing_service_tbl.SOURCE ' || l_existing_service_tbl(i).SOURCE,WSH_DEBUG_SV.C_PROC_LEVEL);
1880 Log(l_module_name,' l_existing_service_tbl.SORT ' || l_existing_service_tbl(i).SORT,WSH_DEBUG_SV.C_PROC_LEVEL);
1881
1882 LOG(l_module_name,' ********************************************** ', WSH_DEBUG_SV.C_PROC_LEVEL);
1883 LOG(l_module_name,' End Printing existing service ', WSH_DEBUG_SV.C_PROC_LEVEL);
1884
1885 END IF;
1886
1887 --}
1888 END LOOP;
1889 --}
1890 END IF;
1891
1892
1893
1894
1895 -- Now create the l_rank_list_in_param_tbl. from l_search_results_tbl.
1896 -- Just make sure that we do not pass in any service with -99 because it is eliminated
1897 -- in previous step because it is a duplicate. And also check for Manual. We
1898 -- should not save them
1899
1900 FOR j IN l_search_results_tbl.FIRST..l_search_results_tbl.LAST
1901 LOOP
1902 --{
1903 IF (l_search_results_tbl(j).LANE_ID <> -99)
1904 THEN
1905 --{
1906 l_search_results_tbl(j).IS_CURRENT := 'N';
1907
1908
1909 IF (l_search_results_tbl(j).SOURCE = 'MAN')
1910 THEN
1911 --{
1912
1913 l_manual_services_tbl(l_manual_services_tbl.COUNT+1) :=
1914 l_search_results_tbl(j);
1915 --}
1916 ELSE
1917 --{
1918 l_rank_list_in_param_tbl(l_rank_list_in_param_tbl.COUNT+1)
1919 := l_search_results_tbl(j);
1920 --}
1921 END IF;
1922 --}
1923 END IF;
1924 --}
1925 END LOOP;
1926
1927
1928
1929 IF (l_save_rank_list = FND_API.G_TRUE AND
1930 l_rank_list_in_param_tbl.COUNT > 0)
1931 THEN
1932 --{
1933
1934 -- Since we are saving something, we should
1935 -- we should set the X_LIST_CREATE_TYPE to SYSTEM.
1936 -- So that from UI it can be append instead of create new
1937 X_LIST_CREATE_TYPE := 'SYSTEM';
1938
1939 -- If there are no existing services, then we just need to assign
1940 -- rank sequences to the search results (not the manual once).
1941 -- because these services will be saved to DB.
1942 IF (l_existing_service_tbl.COUNT = 0)
1943 THEN
1944 --{
1945 FOR j IN l_rank_list_in_param_tbl.FIRST..l_rank_list_in_param_tbl.LAST
1946 LOOP
1947 --{
1948 l_rank_list_in_param_tbl(j).RANK_SEQUENCE := j;
1949 --}
1950 END LOOP;
1951 --}
1952 END IF;
1953
1954 -- Now call RANK_ACTION API to save information
1955 FTE_CARRIER_RANK_LIST_PVT.RANK_LIST_ACTION(
1956 p_api_version_number => 1.0,
1957 p_init_msg_list => FND_API.G_FALSE,
1958 x_return_status => l_return_status,
1959 x_msg_count => l_msg_count,
1960 x_msg_data => l_msg_data,
1961 p_action_code => l_rank_list_action,
1962 p_ranklist => l_rank_list_in_param_tbl,
1963 p_trip_id => P_FTE_SS_ATTR_REC.trip_id,
1964 p_rank_id => null);
1965
1966 wsh_util_core.api_post_call(
1967 p_return_status =>l_return_status,
1968 x_num_warnings =>l_number_of_warnings,
1969 x_num_errors =>l_number_of_errors,
1970 p_msg_data =>l_msg_data);
1971
1972
1973 IF l_number_of_errors > 0
1974 THEN
1975 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1976 RAISE FND_API.G_EXC_ERROR;
1977 ELSIF l_number_of_warnings > 0
1978 THEN
1979 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1980 ELSE
1981 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1982 END IF;
1983 --}
1984 END IF;
1985
1986 IF (l_rank_list_in_param_tbl.COUNT > 0)
1987 THEN
1988 --{
1989
1990 -- If there are no existing services, then we just need to assign
1991 -- rank sequences to the search results (not the manual once).
1992 -- because these services will be saved to DB.
1993 IF (l_existing_service_tbl.COUNT = 0)
1994 THEN
1995 --{
1996 FOR j IN l_rank_list_in_param_tbl.FIRST..l_rank_list_in_param_tbl.LAST
1997 LOOP
1998 --{
1999 l_rank_list_in_param_tbl(j).RANK_SEQUENCE := j;
2000 --}
2001 END LOOP;
2002 --}
2003 END IF;
2004
2005 IF (l_existing_service_tbl.COUNT =0)
2006 THEN
2007 --{
2008
2009 IF l_debug_on
2010 THEN
2011 LOG(l_module_name,' l_existing_service_tbl.count = 0 ' ,
2012 WSH_DEBUG_SV.C_PROC_LEVEL);
2013 END IF;
2014
2015
2016 FOR i IN l_rank_list_in_param_tbl.FIRST..l_rank_list_in_param_tbl.LAST
2017 LOOP
2018 --{
2019 l_rank_list_in_param_tbl(i).RANK_SEQUENCE := i;
2020 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) :=
2021 l_rank_list_in_param_tbl(i);
2022
2023 --}
2024 END LOOP;
2025 --}
2026 ELSE
2027 --{
2028 IF l_debug_on
2029 THEN
2030 LOG(l_module_name,' l_existing_service_tbl.count is not 0 ' ,
2031 WSH_DEBUG_SV.C_PROC_LEVEL);
2032 END IF;
2033
2034 FOR i IN l_rank_list_in_param_tbl.FIRST..l_rank_list_in_param_tbl.LAST
2035 LOOP
2036 --{
2037 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) :=
2038 l_rank_list_in_param_tbl(i);
2039
2040 --}
2041 END LOOP;
2042 --}
2043 END IF;
2044 --}
2045 END IF;
2046
2047 -- Add manual services to the out record at the end.
2048 IF (l_manual_services_tbl.COUNT > 0)
2049 THEN
2050 --{
2051 FOR i IN l_manual_services_tbl.FIRST..l_manual_services_tbl.LAST
2052 LOOP
2053 --{
2054 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT+1) :=
2055 l_manual_services_tbl(i);
2056 --}
2057 END LOOP;
2058 --}
2059 END IF;
2060 --}
2061 ELSE
2062 --{
2063 IF l_debug_on
2064 THEN
2065 LOG(l_module_name,' Rating request id is null so do not do anything. ' ,
2066 WSH_DEBUG_SV.C_PROC_LEVEL);
2067 END IF;
2068
2069 --}
2070 END IF;
2071
2072 IF l_number_of_errors > 0
2073 THEN
2074 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2075 RAISE FND_API.G_EXC_ERROR;
2076 ELSIF l_number_of_warnings > 0
2077 THEN
2078 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2079 ELSE
2080 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2081 END IF;
2082
2083
2084
2085 -- If trip is is null, in DWB case, we should not update the trip
2086 -- because there is no trip. UI call is going to set append flag to N
2087 -- later : HBHAGAVA
2088 IF (P_FTE_SS_ATTR_REC.TRIP_ID IS NOT NULL)
2089 THEN
2090
2091 FTE_MLS_WRAPPER.INITIALIZE_TRIP_REC(x_trip_info => p_trip_info);
2092
2093 -- Update trip information
2094 p_trip_info.APPEND_FLAG := 'N';
2095 p_trip_info.TRIP_ID := P_FTE_SS_ATTR_REC.trip_id;
2096
2097 p_trip_info_tab(1) :=p_trip_info;
2098 p_trip_in_rec.caller :='FTE_LOAD_TENDER';
2099 p_trip_in_rec.phase :=NULL;
2100 p_trip_in_rec.action_code :='UPDATE';
2101
2102 IF l_debug_on
2103 THEN
2104 WSH_DEBUG_SV.logmsg(l_module_name,' Before calling CREATE_UPDATE_TRIP ' ||
2105 ' Updating append flag to N ',
2106 WSH_DEBUG_SV.C_PROC_LEVEL);
2107 WSH_DEBUG_SV.logmsg(l_module_name,' Before calling CREATE_UPDATE_TRIP ' ||
2108 ' Trip Id ' || p_trip_info.TRIP_ID,
2109 WSH_DEBUG_SV.C_PROC_LEVEL);
2110 END IF;
2111
2112
2113 WSH_INTERFACE_GRP.Create_Update_Trip
2114 (
2115 p_api_version_number =>p_api_version_number,
2116 p_init_msg_list =>FND_API.G_FALSE,
2117 p_commit =>FND_API.G_FALSE,
2118 x_return_status =>l_return_status,
2119 x_msg_count =>l_msg_count,
2120 x_msg_data =>l_msg_data,
2121 p_trip_info_tab =>p_trip_info_tab,
2122 p_in_rec =>p_trip_in_rec,
2123 x_out_tab =>x_out_tab
2124 );
2125
2126 IF l_debug_on
2127 THEN
2128 WSH_DEBUG_SV.logmsg(l_module_name,' REturn value from Create update trip ' ||
2129 l_return_status,
2130 WSH_DEBUG_SV.C_PROC_LEVEL);
2131 END IF;
2132
2133 wsh_util_core.api_post_call(
2134 p_return_status =>l_return_status,
2135 x_num_warnings =>l_number_of_warnings,
2136 x_num_errors =>l_number_of_errors,
2137 p_msg_data =>l_msg_data);
2138
2139
2140 IF l_number_of_errors > 0
2141 THEN
2142 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2143 RAISE FND_API.G_EXC_ERROR;
2144 ELSIF l_number_of_warnings > 0
2145 THEN
2146 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2147 ELSE
2148 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2149 END IF;
2150 END IF;
2151
2152 IF l_debug_on
2153 THEN
2154 Log(l_module_name,' PRINTING ALL SERVICES ********** ',WSH_DEBUG_SV.C_PROC_LEVEL);
2155 END IF;
2156
2157 -- Printing all the services
2158 IF (X_SS_RATE_SORT_TAB.COUNT > 0)
2159 THEN
2160
2161
2162 FOR i IN X_SS_RATE_SORT_TAB.FIRST..X_SS_RATE_SORT_TAB.LAST
2163 LOOP
2164 --{
2165 IF l_debug_on
2166 THEN
2167 Log(l_module_name,' ******************** ',WSH_DEBUG_SV.C_PROC_LEVEL);
2168
2169 Log(l_module_name,' X_SS_RATE_SORT_TAB(i).RANK_ID ' || X_SS_RATE_SORT_TAB(i).RANK_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
2170 Log(l_module_name,' X_SS_RATE_SORT_TAB(i).RANK_SEQUENCE ' || X_SS_RATE_SORT_TAB(i).RANK_SEQUENCE,WSH_DEBUG_SV.C_PROC_LEVEL);
2171 Log(l_module_name,' X_SS_RATE_SORT_TAB(i).CARRIER_ID ' || X_SS_RATE_SORT_TAB(i).CARRIER_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
2172 Log(l_module_name,' X_SS_RATE_SORT_TAB(i).SERVICE_LEVEL ' || X_SS_RATE_SORT_TAB(i).SERVICE_LEVEL,WSH_DEBUG_SV.C_PROC_LEVEL);
2173 Log(l_module_name,' X_SS_RATE_SORT_TAB(i).MODE_OF_TRANSPORT ' || X_SS_RATE_SORT_TAB(i).MODE_OF_TRANSPORT,WSH_DEBUG_SV.C_PROC_LEVEL);
2174 Log(l_module_name,' X_SS_RATE_SORT_TAB(i).LANE_ID ' || X_SS_RATE_SORT_TAB(i).LANE_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
2175 Log(l_module_name,' X_SS_RATE_SORT_TAB(i).SOURCE ' || X_SS_RATE_SORT_TAB(i).SOURCE,WSH_DEBUG_SV.C_PROC_LEVEL);
2176 Log(l_module_name,' X_SS_RATE_SORT_TAB(i).ESTIMATED_RATE ' || X_SS_RATE_SORT_TAB(i).ESTIMATED_RATE,WSH_DEBUG_SV.C_PROC_LEVEL);
2177 Log(l_module_name,' X_SS_RATE_SORT_TAB(i).CURRENCY_CODE ' || X_SS_RATE_SORT_TAB(i).CURRENCY_CODE,WSH_DEBUG_SV.C_PROC_LEVEL);
2178 Log(l_module_name,' X_SS_RATE_SORT_TAB(i).VEHICLE_ITEM_ID ' || X_SS_RATE_SORT_TAB(i).VEHICLE_ITEM_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
2179 Log(l_module_name,' X_SS_RATE_SORT_TAB(i).VEHICLE_ORG_ID ' || X_SS_RATE_SORT_TAB(i).VEHICLE_ORG_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
2180 Log(l_module_name,' X_SS_RATE_SORT_TAB(i).ESTIMATED_TRANSIT_TIME ' || X_SS_RATE_SORT_TAB(i).ESTIMATED_TRANSIT_TIME,WSH_DEBUG_SV.C_PROC_LEVEL);
2181 Log(l_module_name,' X_SS_RATE_SORT_TAB(i).TRANSIT_TIME_UOM ' || X_SS_RATE_SORT_TAB(i).TRANSIT_TIME_UOM,WSH_DEBUG_SV.C_PROC_LEVEL);
2182 Log(l_module_name,' X_SS_RATE_SORT_TAB(i).CONSIGNEE_CARRIER_AC_NO ' || X_SS_RATE_SORT_TAB(i).CONSIGNEE_CARRIER_AC_NO,WSH_DEBUG_SV.C_PROC_LEVEL);
2183 Log(l_module_name,' X_SS_RATE_SORT_TAB(i).FREIGHT_TERMS_CODE ' || X_SS_RATE_SORT_TAB(i).FREIGHT_TERMS_CODE,WSH_DEBUG_SV.C_PROC_LEVEL);
2184 END IF;
2185
2186 --}
2187 END LOOP;
2188 END IF;
2189
2190 IF l_debug_on
2191 THEN
2192 Log(l_module_name,' END PRINTING ALL SERVICES ********** ',WSH_DEBUG_SV.C_PROC_LEVEL);
2193 LOG(l_module_name,' X_LIST_CREATE_TYPE value ' || X_LIST_CREATE_TYPE,
2194 WSH_DEBUG_SV.C_PROC_LEVEL);
2195 END IF;
2196
2197
2198 -- Standard call to get message count and if count is 1,get message info.
2199 --
2200 FND_MSG_PUB.Count_And_Get
2201 (
2202 p_count => x_msg_count,
2203 p_data => x_msg_data,
2204 p_encoded => FND_API.G_FALSE
2205 );
2206
2207 --
2208 --
2209
2210 IF l_debug_on THEN
2211 WSH_DEBUG_SV.pop(l_module_name);
2212 END IF;
2213
2214 --}
2215 EXCEPTION
2216 --{
2217 WHEN FND_API.G_EXC_ERROR THEN
2218 ROLLBACK TO SEARCH_SERVICES_PUB;
2219 x_return_status := FND_API.G_RET_STS_ERROR ;
2220 FND_MSG_PUB.Count_And_Get
2221 (
2222 p_count => x_msg_count,
2223 p_data => x_msg_data,
2224 p_encoded => FND_API.G_FALSE
2225 );
2226 IF l_debug_on THEN
2227 WSH_DEBUG_SV.pop(l_module_name);
2228 END IF;
2229
2230 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2231 ROLLBACK TO SEARCH_SERVICES_PUB;
2232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2233 FND_MSG_PUB.Count_And_Get
2234 (
2235 p_count => x_msg_count,
2236 p_data => x_msg_data,
2237 p_encoded => FND_API.G_FALSE
2238 );
2239 IF l_debug_on THEN
2240 WSH_DEBUG_SV.pop(l_module_name);
2241 END IF;
2242 WHEN OTHERS THEN
2243 ROLLBACK TO SEARCH_SERVICES_PUB;
2244 wsh_util_core.default_handler('FTE_SS_INTERFACE.SEARCH_SERVICES');
2245 x_return_status := FND_API.G_RET_STS_ERROR;
2246 FND_MSG_PUB.Count_And_Get
2247 (
2248 p_count => x_msg_count,
2249 p_data => x_msg_data,
2250 p_encoded => FND_API.G_FALSE
2251 );
2252 IF l_debug_on THEN
2253 WSH_DEBUG_SV.pop(l_module_name);
2254 END IF;
2255
2256
2257 --}
2258
2259 END SEARCH_SERVICES;
2260
2261 --}
2262 PROCEDURE SEARCH_SERVICES_UIWRAPPER(
2263 P_INIT_MSG_LIST IN VARCHAR2,
2264 P_API_VERSION_NUMBER IN NUMBER,
2265 P_COMMIT IN VARCHAR2,
2266 P_CALLER IN VARCHAR2,
2267 P_FTE_SS_ATTR_REC IN FTE_SS_ATTR_REC,
2268 X_RATING_REQUEST_ID OUT NOCOPY NUMBER,
2269 X_LIST_CREATE_TYPE OUT NOCOPY VARCHAR2,
2270 X_SS_RATE_SORT_TAB OUT NOCOPY FTE_SS_RATE_SORT_TAB_TYPE,
2271 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2272 X_MSG_COUNT OUT NOCOPY NUMBER,
2273 X_MSG_DATA OUT NOCOPY VARCHAR2)
2274 IS
2275
2276
2277 --{ Local variables
2278
2279
2280 l_api_name CONSTANT VARCHAR2(30) := 'SEARCH_SERVICES_UIWRAPPER';
2281 l_api_version CONSTANT NUMBER := 1.0;
2282 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2283 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
2284
2285
2286 l_return_status VARCHAR2(1);
2287 l_msg_count NUMBER;
2288 l_msg_data VARCHAR2(32767);
2289 l_number_of_warnings NUMBER;
2290 l_number_of_errors NUMBER;
2291
2292 l_list_create_type VARCHAR2(32767);
2293
2294 l_SS_RATE_SORT_TAB FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
2295
2296 l_ss_rate_sort_rec FTE_SS_RATE_SORT_REC;
2297
2298 --}
2299
2300 BEGIN
2301
2302
2303 SAVEPOINT SEARCH_SERVICES_UIWRAPPER_PUB;
2304 IF l_debug_on THEN
2305 WSH_DEBUG_SV.push(l_module_name);
2306 END IF;
2307
2308 IF FND_API.to_Boolean( p_init_msg_list )
2309 THEN
2310 FND_MSG_PUB.initialize;
2311 END IF;
2312
2313 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2314 x_msg_count := 0;
2315 x_msg_data := 0;
2316 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2317 l_number_of_warnings := 0;
2318 l_number_of_errors := 0;
2319
2320
2321 IF l_debug_on
2322 THEN
2323 LOG(l_module_name,' P_caller ' ||
2324 p_caller,WSH_DEBUG_SV.C_PROC_LEVEL);
2325 LOG(l_module_name,' Append list flag ' ||
2326 P_FTE_SS_ATTR_REC.append_list_flag,WSH_DEBUG_SV.C_PROC_LEVEL);
2327 LOG(l_module_name,' Calling FTE_SS_INTERFACE.SEARCH_SERVICES ' ||
2328 P_FTE_SS_ATTR_REC.append_list_flag,WSH_DEBUG_SV.C_PROC_LEVEL);
2329
2330 END IF;
2331
2332
2333 FTE_SS_INTERFACE.SEARCH_SERVICES(
2334 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
2335 P_API_VERSION_NUMBER => 1.0,
2336 P_COMMIT => P_COMMIT,
2337 P_CALLER => P_CALLER,
2338 P_FTE_SS_ATTR_REC => p_FTE_SS_ATTR_REC,
2339 X_RATING_REQUEST_ID => X_RATING_REQUEST_ID,
2340 X_LIST_CREATE_TYPE => X_LIST_CREATE_TYPE,
2341 X_SS_RATE_SORT_TAB => l_SS_RATE_SORT_TAB,
2342 x_return_status => l_return_status,
2343 x_msg_count => l_msg_count,
2344 x_msg_data => l_msg_data);
2345
2346 wsh_util_core.api_post_call(
2347 p_return_status =>l_return_status,
2348 x_num_warnings =>l_number_of_warnings,
2349 x_num_errors =>l_number_of_errors,
2350 p_msg_data =>l_msg_data);
2351
2352
2353 IF l_number_of_errors > 0
2354 THEN
2355 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2356 RAISE FND_API.G_EXC_ERROR;
2357 ELSIF l_number_of_warnings > 0
2358 THEN
2359 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2360 ELSE
2361 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2362 END IF;
2363
2364 -- Update trip append flag to N
2365
2366 -- looping through
2367
2368
2369 IF l_debug_on
2370 THEN
2371
2372 LOG(l_module_name,' List Create Type ' || x_list_create_type,
2373 WSH_DEBUG_SV.C_PROC_LEVEL);
2374
2375 Log(l_module_name,' Looping through all results to return back values to UI ',WSH_DEBUG_SV.C_PROC_LEVEL);
2376
2377 END IF;
2378
2379 -- Printing all the services
2380 IF (L_SS_RATE_SORT_TAB.COUNT > 0)
2381 THEN
2382
2383 X_SS_RATE_SORT_TAB := FTE_SS_RATE_SORT_TAB_TYPE();
2384
2385 FOR i IN l_SS_RATE_SORT_TAB.FIRST..l_SS_RATE_SORT_TAB.LAST
2386 LOOP
2387 --{
2388
2389 l_ss_rate_sort_rec := FTE_SS_RATE_SORT_REC(
2390 l_SS_RATE_SORT_TAB(i).RANK_ID,
2391 l_SS_RATE_SORT_TAB(i).RANK_SEQUENCE,
2392 l_SS_RATE_SORT_TAB(i).LANE_ID,
2393 l_SS_RATE_SORT_TAB(i).SCHEDULE_ID,
2394 l_SS_RATE_SORT_TAB(i).CARRIER_ID,
2395 l_SS_RATE_SORT_TAB(i).MODE_OF_TRANSPORT,
2396 l_SS_RATE_SORT_TAB(i).SERVICE_LEVEL,
2397 l_SS_RATE_SORT_TAB(i).VEHICLE_ITEM_ID,
2398 l_SS_RATE_SORT_TAB(i).VEHICLE_ORG_ID,
2399 l_SS_RATE_SORT_TAB(i).SORT,
2400 l_SS_RATE_SORT_TAB(i).SOURCE,
2401 l_SS_RATE_SORT_TAB(i).ESTIMATED_RATE,
2402 l_SS_RATE_SORT_TAB(i).CURRENCY_CODE,
2403 l_SS_RATE_SORT_TAB(i).ESTIMATED_TRANSIT_TIME,
2404 l_SS_RATE_SORT_TAB(i).TRANSIT_TIME_UOM,
2405 l_SS_RATE_SORT_TAB(i).SCHEDULE_FROM,
2406 l_SS_RATE_SORT_TAB(i).SCHEDULE_TO,
2407 l_SS_RATE_SORT_TAB(i).IS_CURRENT,
2408 l_SS_RATE_SORT_TAB(i).VERSION,
2409 l_SS_RATE_SORT_TAB(i).SINGLE_CURR_RATE,
2410 l_SS_RATE_SORT_TAB(i).CONSIGNEE_CARRIER_AC_NO,
2411 l_SS_RATE_SORT_TAB(i).FREIGHT_TERMS_CODE);
2412 X_SS_RATE_SORT_TAB.EXTEND;
2413 X_SS_RATE_SORT_TAB(X_SS_RATE_SORT_TAB.COUNT) := l_ss_rate_sort_rec;
2414
2415 --}
2416 END LOOP;
2417 END IF;
2418
2419 IF l_debug_on
2420 THEN
2421 Log(l_module_name,' END PRINTING ALL SERVICES ********** ',WSH_DEBUG_SV.C_PROC_LEVEL);
2422 END IF;
2423
2424
2425 -- Standard call to get message count and if count is 1,get message info.
2426 --
2427 FND_MSG_PUB.Count_And_Get
2428 (
2429 p_count => x_msg_count,
2430 p_data => x_msg_data,
2431 p_encoded => FND_API.G_FALSE
2432 );
2433
2434 --
2435 --
2436
2437 IF l_debug_on THEN
2438 WSH_DEBUG_SV.pop(l_module_name);
2439 END IF;
2440
2441 --}
2442 EXCEPTION
2443 --{
2444 WHEN FND_API.G_EXC_ERROR THEN
2445 ROLLBACK TO SEARCH_SERVICES_UIWRAPPER_PUB;
2446 x_return_status := FND_API.G_RET_STS_ERROR ;
2447 FND_MSG_PUB.Count_And_Get
2448 (
2449 p_count => x_msg_count,
2450 p_data => x_msg_data,
2451 p_encoded => FND_API.G_FALSE
2452 );
2453 IF l_debug_on THEN
2454 WSH_DEBUG_SV.pop(l_module_name);
2455 END IF;
2456
2457 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2458 ROLLBACK TO SEARCH_SERVICES_UIWRAPPER_PUB;
2459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2460 FND_MSG_PUB.Count_And_Get
2461 (
2462 p_count => x_msg_count,
2463 p_data => x_msg_data,
2464 p_encoded => FND_API.G_FALSE
2465 );
2466 IF l_debug_on THEN
2467 WSH_DEBUG_SV.pop(l_module_name);
2468 END IF;
2469 WHEN OTHERS THEN
2470 ROLLBACK TO SEARCH_SERVICES_UIWRAPPER_PUB;
2471 wsh_util_core.default_handler('FTE_SS_INTERFACE.SEARCH_SERVICES_UIWRAPPER');
2472 x_return_status := FND_API.G_RET_STS_ERROR;
2473 FND_MSG_PUB.Count_And_Get
2474 (
2475 p_count => x_msg_count,
2476 p_data => x_msg_data,
2477 p_encoded => FND_API.G_FALSE
2478 );
2479 IF l_debug_on THEN
2480 WSH_DEBUG_SV.pop(l_module_name);
2481 END IF;
2482
2483
2484 --}
2485
2486 END SEARCH_SERVICES_UIWRAPPER;
2487
2488
2489
2490
2491 --{
2492
2493
2494 PROCEDURE GET_RANKED_RESULTS_WRAPPER
2495 ( p_api_version_number IN NUMBER,
2496 p_init_msg_list IN VARCHAR2,
2497 x_return_status OUT NOCOPY VARCHAR2,
2498 x_msg_count OUT NOCOPY NUMBER,
2499 x_msg_data OUT NOCOPY VARCHAR2,
2500 x_routing_guide OUT NOCOPY FTE_SS_RATE_SORT_TAB_TYPE,
2501 p_routing_rule_id IN NUMBER)
2502
2503 IS
2504 --{
2505
2506
2507 --{ Local variables
2508
2509
2510 l_api_name CONSTANT VARCHAR2(30) := 'GET_RANKED_RESULTS_WRAPPER';
2511 l_api_version CONSTANT NUMBER := 1.0;
2512 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2513 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
2514
2515
2516 l_return_status VARCHAR2(32767);
2517 l_msg_count NUMBER;
2518 l_msg_data VARCHAR2(32767);
2519 l_number_of_warnings NUMBER;
2520 l_number_of_errors NUMBER;
2521
2522 l_routing_results FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
2523
2524 l_mode VARCHAR2(80);
2525 l_service VARCHAR2(80);
2526 l_carrierName VARCHAR2(360);
2527
2528 --}
2529
2530 BEGIN
2531
2532
2533 SAVEPOINT GET_RANKED_RESULTS_WRAPPER_PUB;
2534 IF l_debug_on THEN
2535 WSH_DEBUG_SV.push(l_module_name);
2536 END IF;
2537
2538 IF FND_API.to_Boolean( p_init_msg_list )
2539 THEN
2540 FND_MSG_PUB.initialize;
2541 END IF;
2542
2543 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2544 x_msg_count := 0;
2545 x_msg_data := 0;
2546 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2547 l_number_of_warnings := 0;
2548 l_number_of_errors := 0;
2549
2550
2551 IF l_debug_on
2552 THEN
2553 LOG(l_module_name,' Calling Routing guide with rule id ',WSH_DEBUG_SV.C_PROC_LEVEL);
2554 END IF;
2555
2556 x_routing_guide := FTE_SS_RATE_SORT_TAB_TYPE();
2557
2558 /**
2559 FTE_ACS_TRIP_PKG.get_ranked_results(p_rule_id => p_routing_rule_id,
2560 x_routing_results => l_routing_results,
2561 x_return_status => l_return_status);
2562 */
2563 IF l_debug_on
2564 THEN
2565 LOG(l_module_name,' return status from GET_RANKED_RESULTS ' || l_return_status,
2566 WSH_DEBUG_SV.C_PROC_LEVEL);
2567 END IF;
2568
2569 wsh_util_core.api_post_call(
2570 p_return_status =>l_return_status,
2571 x_num_warnings =>l_number_of_warnings,
2572 x_num_errors =>l_number_of_errors,
2573 p_msg_data =>'');
2574
2575
2576
2577
2578 FOR i IN l_routing_results.FIRST..l_routing_results.LAST
2579 LOOP
2580 x_routing_guide.EXTEND;
2581
2582
2583 /**
2584 l_service := null;
2585
2586 IF (l_routing_results(i).SERVICE_LEVEL IS NOT NULL)
2587 THEN
2588 l_service := WSH_UTIL_CORE.GET_LOOKUP_MEANING('WSH_SERVICE_LEVELS',
2589 l_routing_results(i).SERVICE_LEVEL);
2590 END IF;
2591
2592 l_mode := null;
2593 IF (l_routing_results(i).MODE_OF_TRANSPORT IS NOT NULL)
2594 THEN
2595 l_mode := WSH_UTIL_CORE.GET_LOOKUP_MEANING('WSH_MODE_OF_TRANSPORT',
2596 l_routing_results(i).MODE_OF_TRANSPORT);
2597 END IF;
2598
2599 l_carrierName := null;
2600 IF (l_routing_results(i).CARRIER_ID IS NOT NULL)
2601 THEN
2602 l_carrierName := FTE_MLS_UTIL.GET_CARRIER_NAME(
2603 l_routing_results(i).CARRIER_ID);
2604 END IF;
2605 */
2606
2607 x_routing_guide(x_routing_guide.COUNT) :=
2608 FTE_SS_RATE_SORT_REC(
2609 null,
2610 l_routing_results(i).RANK_SEQUENCE,
2611 null,
2612 NULL,
2613 l_routing_results(i).CARRIER_ID,
2614 l_routing_results(i).MODE_OF_TRANSPORT,
2615 l_routing_results(i).SERVICE_LEVEL,
2616 l_routing_results(i).VEHICLE_ITEM_ID,
2617 NULL,
2618 NULL,
2619 l_routing_results(i).SOURCE,
2620 null,
2621 null,
2622 null,
2623 null,
2624 NULL,--l_carrier_rank_list_rec.SCHEDULE_FROM ,
2625 NULL,--l_carrier_rank_list_rec.SCHEDULE_TO ,
2626 null,
2627 null,
2628 NULL,
2629 l_routing_results(i).CONSIGNEE_CARRIER_AC_NO ,
2630 l_routing_results(i).FREIGHT_TERMS_CODE);
2631
2632 END LOOP;
2633
2634 IF l_number_of_errors > 0
2635 THEN
2636 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2637 RAISE FND_API.G_EXC_ERROR;
2638 ELSIF l_number_of_warnings > 0
2639 THEN
2640 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2641 ELSE
2642 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2643 END IF;
2644
2645 -- Update trip append flag to N
2646
2647 -- Standard call to get message count and if count is 1,get message info.
2648 --
2649 FND_MSG_PUB.Count_And_Get
2650 (
2651 p_count => x_msg_count,
2652 p_data => x_msg_data,
2653 p_encoded => FND_API.G_FALSE
2654 );
2655
2656 --
2657 --
2658
2659 IF l_debug_on THEN
2660 WSH_DEBUG_SV.pop(l_module_name);
2661 END IF;
2662
2663 --}
2664 EXCEPTION
2665 --{
2666 WHEN FND_API.G_EXC_ERROR THEN
2667 ROLLBACK TO GET_RANKED_RESULTS_WRAPPER_PUB;
2668 x_return_status := FND_API.G_RET_STS_ERROR ;
2669 FND_MSG_PUB.Count_And_Get
2670 (
2671 p_count => x_msg_count,
2672 p_data => x_msg_data,
2673 p_encoded => FND_API.G_FALSE
2674 );
2675 IF l_debug_on THEN
2676 WSH_DEBUG_SV.pop(l_module_name);
2677 END IF;
2678
2679 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2680 ROLLBACK TO GET_RANKED_RESULTS_WRAPPER_PUB;
2681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2682 FND_MSG_PUB.Count_And_Get
2683 (
2684 p_count => x_msg_count,
2685 p_data => x_msg_data,
2686 p_encoded => FND_API.G_FALSE
2687 );
2688 IF l_debug_on THEN
2689 WSH_DEBUG_SV.pop(l_module_name);
2690 END IF;
2691 WHEN OTHERS THEN
2692 ROLLBACK TO GET_RANKED_RESULTS_WRAPPER_PUB;
2693 wsh_util_core.default_handler('FTE_SS_INTERFACE.GET_RANKED_RESULTS_WRAPPER');
2694 x_return_status := FND_API.G_RET_STS_ERROR;
2695 FND_MSG_PUB.Count_And_Get
2696 (
2697 p_count => x_msg_count,
2698 p_data => x_msg_data,
2699 p_encoded => FND_API.G_FALSE
2700 );
2701 IF l_debug_on THEN
2702 WSH_DEBUG_SV.pop(l_module_name);
2703 END IF;
2704
2705
2706 --}
2707 --
2708 END GET_RANKED_RESULTS_WRAPPER;
2709
2710 --========================================================================
2711 -- PROCEDURE : ASSIGN_SERVICE_TENDER FTE wrapper
2712 --
2713 -- COMMENT : Procedure assigns service, creates/updates ranked list,
2714 -- tenders, and deletes rates. TripId should exist in the db.
2715 -- If FTE_SS_ATTR_REC.DELIVERY_ID and
2716 -- FTE_SS_ATTR_REC.DELIVERY_LEG_ID are null, then it means the
2717 -- caller is TWB. Otherwise, the caller is DWB or ManItinerary
2718 -- CALLER : FTE UI: TripWB, DeliveryWB, ManageItinerary
2719 --========================================================================
2720 --
2721 PROCEDURE ASSIGN_SERVICE_TENDER
2722 (
2723 p_API_VERSION_NUMBER IN NUMBER,
2724 p_INIT_MSG_LIST IN VARCHAR2,
2725 p_COMMIT IN VARCHAR2,
2726 p_SS_ATTR_REC IN FTE_SS_ATTR_REC,
2727 p_SS_RATE_SORT_TAB IN OUT NOCOPY FTE_SS_RATE_SORT_TAB_TYPE,
2728 p_TENDER_ATTR_REC IN FTE_TENDER_ATTR_REC,
2729 p_REQUEST_ID IN NUMBER,
2730 p_SERVICE_ACTION IN VARCHAR2,
2731 p_LIST_ACTION IN VARCHAR2,
2732 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
2733 x_MSG_COUNT OUT NOCOPY NUMBER,
2734 x_MSG_DATA OUT NOCOPY VARCHAR2)
2735 IS
2736 l_number_of_warnings NUMBER;
2737 l_number_of_errors NUMBER;
2738 l_return_status VARCHAR2(32767);
2739 l_msg_count NUMBER;
2740 l_msg_data VARCHAR2(32767);
2741
2742 l_trip_id NUMBER;
2743 l_delivery_leg_id NUMBER;
2744 l_delivery_id NUMBER;
2745 l_lane_id NUMBER;
2746 l_carrier_id NUMBER;
2747 l_mode VARCHAR2(30);
2748 l_service_level VARCHAR2(30);
2749 l_veh_item_id NUMBER;
2750 l_veh_org_id NUMBER;
2751 l_rank_id NUMBER;
2752 l_schedule_id NUMBER;
2753
2754 l_ret_trip_name VARCHAR2(30);
2755 l_ret_trip_id NUMBER;
2756 l_list_action VARCHAR2(30);
2757
2758 l_ss_rate_sort_rec FTE_SS_RATE_SORT_REC;
2759 l_action_out_rec FTE_ACTION_OUT_REC;
2760 l_trip_action_param FTE_TRIP_ACTION_PARAM_REC;
2761
2762 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2763 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.'|| G_PKG_NAME ||'.'||'ASSIGN_SERVICE_TENDER';
2764
2765 BEGIN
2766
2767 SAVEPOINT ASSIGN_SERVICE_TENDER_PUB;
2768
2769 -- Initialize message list if p_init_msg_list is set to TRUE.
2770 --
2771 IF FND_API.to_Boolean( p_init_msg_list )
2772 THEN
2773 FND_MSG_PUB.initialize;
2774 END IF;
2775 --
2776 IF l_debug_on THEN
2777 wsh_debug_sv.push(l_module_name);
2778 END IF;
2779 --
2780 --
2781 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2782 x_msg_count := 0;
2783
2784 -- local variables used to check API return values
2785 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2786 l_number_of_warnings := 0;
2787 l_number_of_errors := 0;
2788
2789 l_trip_id := p_ss_attr_rec.trip_id;
2790 l_delivery_leg_id := p_ss_attr_rec.delivery_leg_id;
2791 l_delivery_id := p_ss_attr_rec.delivery_id;
2792 l_list_action := p_list_action;
2793
2794 IF l_debug_on THEN
2795 WSH_DEBUG_SV.logmsg(l_module_name, 'TripId:LegId:DeliveryId:'
2796 ||l_trip_id||':'||l_delivery_leg_id||':'||l_delivery_id);
2797 WSH_DEBUG_SV.logmsg(l_module_name, 'Service Action:List Action:'
2798 ||p_service_action||':'||p_list_action);
2799 END IF;
2800
2801 -- Step 1: Check if old service exists on trip
2802 -- If p_service_action indicates service is currently assigned, delete old rates
2803 -- and raise appropriate business event
2804 IF ( p_service_action = 'UPDATE' AND l_trip_id IS NOT NULL
2805 AND l_delivery_leg_id IS NULL ) THEN
2806
2807 IF l_debug_on THEN
2808 WSH_DEBUG_SV.logmsg(l_module_name, 'Deleting Main Records for:'||l_trip_id);
2809 END IF;
2810
2811 FTE_TRIP_RATING_GRP.DELETE_MAIN_RECORDS(
2812 p_trip_id => l_trip_id,
2813 x_return_status => l_return_status);
2814
2815
2816 WSH_UTIL_CORE.API_POST_CALL(
2817 p_return_status =>l_return_status,
2818 x_num_warnings =>l_number_of_warnings,
2819 x_num_errors =>l_number_of_errors,
2820 p_msg_data =>l_msg_data);
2821 END IF;
2822
2823 -- Step 2: Check if l_list_action is correct.
2824 -- Get the first record in the service table.
2825 -- If LaneId is null, change l_list_action = 'SET_CURRENT'.
2826
2827 /* R12 Hiding Project
2828 IF l_debug_on THEN
2829 WSH_DEBUG_SV.logmsg(l_module_name, 'List Action:'||p_list_action);
2830 WSH_DEBUG_SV.logmsg(l_module_name, ' Service List Size:'||p_ss_rate_sort_tab.count);
2831 END IF;
2832
2833 IF ( l_list_action = 'APPEND' ) THEN
2834 l_ss_rate_sort_rec := p_ss_rate_sort_tab(1);
2835
2836 IF l_debug_on THEN
2837 WSH_DEBUG_SV.logmsg(l_module_name, 'Append Check Rank Id:'||l_ss_rate_sort_rec.rank_id);
2838 WSH_DEBUG_SV.logmsg(l_module_name, 'Append Check Lane Id:'||l_ss_rate_sort_rec.lane_id);
2839 END IF;
2840
2841 IF (l_ss_rate_sort_rec.rank_id IS NOT NULL AND l_ss_rate_sort_rec.lane_id IS NULL) THEN
2842 -- l_ss_rate_sort_rec.schedule_id IS NULL AND
2843 l_rank_id := l_ss_rate_sort_rec.rank_id;
2844 l_list_action := 'SET_CURRENT';
2845
2846 IF l_debug_on THEN
2847 WSH_DEBUG_SV.logmsg(l_module_name, 'Only rank id populated:'||l_rank_id);
2848 END IF;
2849 END IF;
2850 END IF;
2851
2852 IF l_debug_on THEN
2853 WSH_DEBUG_SV.logmsg(l_module_name, ' New List Action:'||l_list_action);
2854 END IF;
2855
2856 -- Step 3: Create, Append or SetCurrent Ranked List depending on list action_code
2857 -- Values will be CREATE OR APPEND. Modify APPEND to be either APPEND or SET_CURRENT
2858 -- CREATE: Manual condition, set IS_CURRENT for selected service
2859 -- SET_CURRENT: Only pass user entry as current. Version is increased. Do no
2860 -- pass service tab, only rankId
2861 -- APPEND: Pass in one record service tab (entry has no rankId or sequence)
2862 -- Entry appended to existing list with next ranked seq. Set IS_CURRENT on record.
2863 -- OUT param is the new rankId to be stored on the trip
2864
2865 IF ( l_list_action IS NOT NULL AND l_trip_id IS NOT NULL) THEN
2866 IF ( p_list_action = 'SET_CURRENT' ) THEN
2867 FTE_CARRIER_RANK_LIST_PVT.RANK_LIST_ACTION_UIWRAPPER(
2868 p_API_VERSION_NUMBER => 1.0,
2869 p_INIT_MSG_LIST => FND_API.G_TRUE,
2870 p_ACTION_CODE => l_list_action,
2871 p_RANKLIST => p_ss_rate_sort_tab,
2872 p_RANK_ID => l_rank_id,
2873 p_TRIP_ID => l_trip_id,
2874 x_RETURN_STATUS => l_return_status,
2875 x_MSG_COUNT => l_msg_count,
2876 x_MSG_DATA => l_msg_data);
2877 ELSE
2878 FTE_CARRIER_RANK_LIST_PVT.RANK_LIST_ACTION_UIWRAPPER(
2879 p_API_VERSION_NUMBER => 1.0,
2880 p_INIT_MSG_LIST => FND_API.G_TRUE,
2881 p_ACTION_CODE => l_list_action,
2882 p_RANKLIST => p_ss_rate_sort_tab,
2883 p_RANK_ID => l_rank_id,
2884 p_TRIP_ID => l_trip_id,
2885 x_RETURN_STATUS => l_return_status,
2886 x_MSG_COUNT => l_msg_count,
2887 x_MSG_DATA => l_msg_data);
2888 END IF;
2889
2890 WSH_UTIL_CORE.API_POST_CALL(
2891 p_return_status => l_return_status,
2892 x_num_warnings => l_number_of_warnings,
2893 x_num_errors => l_number_of_errors,
2894 p_msg_data => l_msg_data);
2895
2896 END IF;
2897
2898 IF l_debug_on THEN
2899 WSH_DEBUG_SV.logmsg(l_module_name, 'New Rank Id:'||l_rank_id);
2900 END IF;
2901 */
2902
2903 -- Step 4: Update Trip
2904 -- If delivery_id is null, this call is coming from TripWB. Update Trip with
2905 -- current service from FTE_SS_ATTR_REC and rank id.
2906 -- Else this call is coming from DWB or MI. Update Trip with Rank Id only.
2907
2908 -- Step 4a: Set reprice flag to N
2909
2910 IF l_trip_id IS NOT NULL THEN
2911 FTE_FREIGHT_PRICING.unmark_reprice_required(
2912 p_segment_id => l_trip_id,
2913 x_return_status => l_return_status);
2914
2915 IF l_debug_on THEN
2916 WSH_DEBUG_SV.logmsg(l_module_name, 'After unmark reprice status:'||l_return_status);
2917 END IF;
2918 END IF;
2919
2920 IF (l_delivery_id IS NULL ) THEN -- Coming from TWB
2921
2922 l_lane_id := p_ss_attr_rec.lane_id;
2923 l_schedule_id := p_ss_attr_rec.schedule_id;
2924 l_carrier_id := p_ss_attr_rec.carrier_id;
2925 l_mode := p_ss_attr_rec.mode_of_transport;
2926 l_service_level := p_ss_attr_rec.service_level;
2927 l_veh_item_id := p_ss_attr_rec.vehicle_item_id;
2928 l_veh_org_id := p_ss_attr_rec.vehicle_org_id;
2929
2930 FTE_MLS_WRAPPER.UPDATE_SERVICE_ON_TRIP(
2931 p_API_VERSION_NUMBER => 1.0,
2932 p_INIT_MSG_LIST => FND_API.G_TRUE,
2933 p_COMMIT => FND_API.G_FALSE,
2934 p_CALLER => 'FTE',
2935 p_SERVICE_ACTION => p_service_action,
2936 p_DELIVERY_ID => l_delivery_id,
2937 p_DELIVERY_LEG_ID => l_delivery_leg_id,
2938 p_TRIP_ID => l_trip_id,
2939 p_LANE_ID => l_lane_id,
2940 p_SCHEDULE_ID => null, -- Need to change to real schedule
2941 p_CARRIER_ID => l_carrier_id,
2942 p_SERVICE_LEVEL => l_service_level,
2943 p_MODE_OF_TRANSPORT => l_mode,
2944 p_VEHICLE_ITEM_ID => l_veh_item_id,
2945 p_VEHICLE_ORG_ID => l_veh_org_id,
2946 p_CONSIGNEE_CARRIER_AC_NO => FND_API.G_MISS_CHAR,
2947 p_FREIGHT_TERMS_CODE => FND_API.G_MISS_CHAR,
2948 x_RETURN_STATUS => l_return_status,
2949 x_MSG_COUNT => l_msg_count,
2950 x_MSG_DATA => l_msg_data);
2951
2952 WSH_UTIL_CORE.API_POST_CALL(
2953 p_return_status =>l_return_status,
2954 x_num_warnings =>l_number_of_warnings,
2955 x_num_errors =>l_number_of_errors,
2956 p_msg_data =>l_msg_data);
2957
2958 /* Hiding Project - Do not Raise Service Events
2959 ELSE -- Coming from DWB/MI
2960 FTE_MLS_WRAPPER.UPDATE_SERVICE_ON_TRIP(
2961 p_API_VERSION_NUMBER => 1.0,
2962 p_INIT_MSG_LIST => FND_API.G_TRUE,
2963 p_COMMIT => FND_API.G_FALSE,
2964 p_CALLER => 'FTE',
2965 p_SERVICE_ACTION => p_service_action,
2966 p_DELIVERY_ID => l_delivery_id,
2967 p_DELIVERY_LEG_ID => l_delivery_leg_id,
2968 p_TRIP_ID => l_trip_id,
2969 p_LANE_ID => FND_API.G_MISS_NUM,
2970 p_SCHEDULE_ID => FND_API.G_MISS_NUM,
2971 p_CARRIER_ID => FND_API.G_MISS_NUM,
2972 p_SERVICE_LEVEL => FND_API.G_MISS_CHAR,
2973 p_MODE_OF_TRANSPORT => FND_API.G_MISS_CHAR,
2974 p_VEHICLE_ITEM_ID => FND_API.G_MISS_NUM,
2975 p_VEHICLE_ORG_ID => FND_API.G_MISS_NUM,
2976 p_CONSIGNEE_CARRIER_AC_NO => FND_API.G_MISS_CHAR,
2977 p_FREIGHT_TERMS_CODE => FND_API.G_MISS_CHAR,
2978 x_RETURN_STATUS => l_return_status,
2979 x_MSG_COUNT => l_msg_count,
2980 x_MSG_DATA => l_msg_data);
2981
2982 WSH_UTIL_CORE.API_POST_CALL(
2983 p_return_status =>l_return_status,
2984 x_num_warnings =>l_number_of_warnings,
2985 x_num_errors =>l_number_of_errors,
2986 p_msg_data =>l_msg_data);
2987
2988 */
2989 END IF;
2990
2991 IF l_debug_on THEN
2992 WSH_DEBUG_SV.logmsg(l_module_name, 'API:FTE_MLS_WRAPPER.UPDATE_SERVICE_ON_TRIP');
2993 WSH_DEBUG_SV.logmsg(l_module_name, 'l_return_status:'||l_return_status);
2994 WSH_DEBUG_SV.logmsg(l_module_name, 'l_number_of_warnings:'||l_number_of_warnings);
2995 WSH_DEBUG_SV.logmsg(l_module_name, 'l_number_of_errors:'||l_number_of_errors);
2996 WSH_DEBUG_SV.logmsg(l_module_name, 'l_msg_data:'||l_msg_data);
2997 END IF;
2998
2999 -- Step 5: Move FC Rates
3000 -- If delivery_id is null, this call is coming from TripWB.
3001
3002 IF l_debug_on THEN
3003 WSH_DEBUG_SV.logmsg(l_module_name,'Calling Move FC TEMP Request Id:'||p_request_id);
3004 WSH_DEBUG_SV.logmsg(l_module_name,' If Delivery Id is NULL, from TWB:'||l_delivery_id);
3005 END IF;
3006
3007 IF ( p_request_id IS NOT NULL ) THEN
3008
3009 IF (l_delivery_id IS NULL ) THEN -- Coming from TWB
3010
3011 FTE_TRIP_RATING_GRP.Move_Records_To_Main(
3012 p_trip_id => l_trip_id,
3013 p_lane_id => l_lane_id,
3014 p_schedule_id => l_schedule_id,
3015 p_service_type_code => l_service_level,
3016 p_comparison_request_id => p_request_id,
3017 x_return_status => l_return_status);
3018 /*
3019 ELSE -- Coming from DWB/MI
3020
3021 FTE_FREIGHT_PRICING.MOVE_FC_TEMP_TO_MAIN(
3022 p_init_msg_list => FND_API.G_FALSE,
3023 p_request_id => p_request_id,
3024 p_trip_id => l_trip_id,
3025 p_lane_id => l_lane_id,
3026 p_schedule_id => l_schedule_id,
3027 p_service_type_code => l_service_level,
3028 x_return_status => l_return_status);
3029 */
3030
3031 END IF;
3032
3033 WSH_UTIL_CORE.API_POST_CALL(
3034 p_return_status =>l_return_status,
3035 x_num_warnings =>l_number_of_warnings,
3036 x_num_errors =>l_number_of_errors,
3037 p_msg_data =>l_msg_data);
3038
3039 END IF;
3040
3041 IF l_debug_on THEN
3042 WSH_DEBUG_SV.logmsg(l_module_name,'Pre Raising Tender Event CarrierContactId:'
3043 ||p_tender_attr_rec.car_contact_id);
3044 END IF;
3045
3046 -- Step 6: Raise Tender event
3047 IF ( p_tender_attr_rec IS NOT NULL AND
3048 p_tender_attr_rec.car_contact_id IS NOT NULL) THEN
3049
3050 IF l_debug_on THEN
3051 WSH_DEBUG_SV.logmsg(l_module_name,'Raising Tender Event CarrierContactId:'
3052 ||p_tender_attr_rec.car_contact_id);
3053 END IF;
3054
3055 -- Create Trip Actions Tab
3056 l_trip_action_param := FTE_TRIP_ACTION_PARAM_REC(null,'TENDERED',
3057 null,null,null,null,null,null,
3058 null,null,null,null,null,null,
3059 null,null);
3060
3061 FTE_MLS_WRAPPER.TRIP_ACTION(
3062 p_api_version_number => 1.0,
3063 p_init_msg_list => FND_API.G_TRUE,
3064 p_action_prms => l_trip_action_param,
3065 p_trip_info_rec => p_tender_attr_rec,
3066 x_action_out_rec => l_action_out_rec,
3067 x_return_status => l_return_status,
3068 x_msg_count => l_msg_count,
3069 x_msg_data => l_msg_data);
3070
3071 WSH_UTIL_CORE.API_POST_CALL(
3072 p_return_status =>l_return_status,
3073 x_num_warnings =>l_number_of_warnings,
3074 x_num_errors =>l_number_of_errors,
3075 p_msg_data =>l_msg_data);
3076 END IF;
3077
3078 IF l_number_of_errors > 0
3079 THEN
3080 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3081 RAISE FND_API.G_EXC_ERROR;
3082 ELSIF l_number_of_warnings > 0 THEN
3083 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3084 ELSE
3085 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3086 END IF;
3087
3088
3089 FND_MSG_PUB.Count_And_Get
3090 (
3091 p_count => x_msg_count,
3092 p_data => x_msg_data,
3093 p_encoded => FND_API.G_FALSE
3094 );
3095
3096 IF l_debug_on THEN
3097 WSH_DEBUG_SV.POP(l_module_name);
3098 END IF;
3099
3100 EXCEPTION
3101 WHEN FND_API.G_EXC_ERROR THEN
3102 ROLLBACK TO ASSIGN_SERVICE_TENDER_PUB;
3103 x_return_status := FND_API.G_RET_STS_ERROR;
3104 FND_MSG_PUB.Count_And_Get
3105 (
3106 p_count => x_msg_count,
3107 p_data => x_msg_data,
3108 p_encoded => FND_API.G_FALSE
3109 );
3110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3111 ROLLBACK TO ASSIGN_SERVICE_TENDER_PUB;
3112 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3113 FND_MSG_PUB.Count_And_Get
3114 (
3115 p_count => x_msg_count,
3116 p_data => x_msg_data,
3117 p_encoded => FND_API.G_FALSE
3118 );
3119 WHEN OTHERS THEN
3120 ROLLBACK TO ASSIGN_SERVICE_TENDER_PUB;
3121 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3122 FND_MSG_PUB.Count_And_Get
3123 (
3124 p_count => x_msg_count,
3125 p_data => x_msg_data,
3126 p_encoded => FND_API.G_FALSE
3127 );
3128 END ASSIGN_SERVICE_TENDER;
3129
3130
3131 END FTE_SS_INTERFACE;