[Home] [Help]
PACKAGE BODY: APPS.FTE_LANE_SEARCH
Source
1 PACKAGE BODY FTE_LANE_SEARCH AS
2 /* $Header: FTELNSEB.pls 120.2 2006/05/02 00:51:21 jnpinto ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_LANE_SEARCH';
6 --
7
8 g_message VARCHAR2(12000);
9
10 -- ----------------------------------------------------------------
11 -- Name: Set_Up_Regions
12 -- Type: Procedure
13 --
14 -- Description: This procedure calls
15 -- WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches
16 -- with location_id and/or regions information
17 -- and obtains all the regions and parent regions
18 -- that match.
19 --
20 -- -----------------------------------------------------------------
21 PROCEDURE Set_Up_Regions(p_loc_id IN NUMBER,
22 p_country IN VARCHAR2,
23 p_state IN VARCHAR2,
24 p_city IN VARCHAR2,
25 p_zip IN OUT NOCOPY VARCHAR2,
26 x_regions OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
27 x_parent_regions OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab) IS
28
29 l_region_and_type FTE_LANE_SEARCH_QUERY_GEN.fte_regions_types;
30 l_country VARCHAR2(60) := null;
31 l_country_code VARCHAR2(10) := null;
32 l_state VARCHAR2(60) := null;
33 l_state_code VARCHAR2(10) := null;
34 l_city VARCHAR2(60) := null;
35 l_city_code VARCHAR2(10) := null;
36
37 l_status NUMBER;
38 l_regions_temp WSH_REGIONS_SEARCH_PKG.region_table;
39 l_region WSH_REGIONS_SEARCH_PKG.region_rec;
40
41 l_regions FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab;
42 l_parent_regions FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab;
43
44 CURSOR get_postal_code(p_loc_id NUMBER) IS
45 SELECT postal_code FROM wsh_locations
46 WHERE wsh_location_id = p_loc_id;
47
48 --
49 l_debug_on BOOLEAN;
50 --
51 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SET_UP_REGIONS';
52 --
53 BEGIN
54
55 --
56 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
57 --
58 IF l_debug_on IS NULL
59 THEN
60 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
61 END IF;
62 --
63 --
64 -- Debug Statements
65 --
66 IF l_debug_on THEN
67 WSH_DEBUG_SV.push(l_module_name);
68 --
69 WSH_DEBUG_SV.log(l_module_name,'P_LOC_ID',P_LOC_ID);
70 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
71 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
72 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
73 WSH_DEBUG_SV.log(l_module_name,'P_ZIP',P_ZIP);
74 END IF;
75 --
76
77 IF (p_loc_id is null AND p_country is null AND
78 p_state is null AND p_city is null AND
79 p_zip is null) THEN
80
81 --
82 -- Debug Statements
83 --
84 IF l_debug_on THEN
85 WSH_DEBUG_SV.logmsg(l_module_name,'no region information passed in',WSH_DEBUG_SV.C_PROC_LEVEL);
86 END IF;
87
88 l_region_and_type.region_id := NULL;
89 l_region_and_type.region_type := NULL;
90 l_regions(1) := l_region_and_type;
91
92 x_regions := l_regions;
93
94 RETURN;
95
96 END IF;
97
98 IF (LENGTH(p_country) <= 3) THEN
99 l_country_code := p_country;
100 ELSE
101 l_country := p_country;
102 END IF;
103
104 IF (LENGTH(p_state) <= 3) THEN
105 l_state_code := p_state;
106 ELSE
107 l_state := p_state;
108 END IF;
109
110 IF (LENGTH(p_city) <= 3) THEN
111 l_city_code := p_city;
112 ELSE
113 l_city := p_city;
114 END IF;
115
116 --
117 -- Debug Statements
118 --
119 IF l_debug_on THEN
120 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_ALL_REGION_MATCHES',WSH_DEBUG_SV.C_PROC_LEVEL);
121 END IF;
122 --
123 BEGIN
124 WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches(p_country => l_country,
125 p_country_region => null,
126 p_state => l_state,
127 p_city => l_city,
128 p_postal_code_from => p_zip,
129 p_postal_code_to => p_zip,
130 p_country_code => l_country_code,
131 p_country_region_code => null,
132 p_state_code => l_state_code,
133 p_city_code => l_city_code,
134 p_lang_code => userenv('LANG'),
135 p_location_id => p_loc_id,
136 p_zone_flag => 'N',
137 p_more_matches => TRUE,
138 x_status => l_status,
139 x_regions => l_regions_temp);
140
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 IF l_debug_on THEN
144 WSH_DEBUG_SV.logmsg(l_module_name,'No Region Found for ' || l_country || ', ' || l_state || ', ' || l_city);
145 END IF;
146 END;
147
148 -- using city field as a state if region obtained is a country
149 -- this is due to ambiguous free text input, so cannot
150 -- distinguish whether single input is city or state
151 IF (l_regions_temp.COUNT <= 1 AND
152 (l_city is not null OR l_city_code is not null) AND
153 (l_state is null AND l_state_code is null)) THEN
154 l_state := l_city;
155 l_state_code := l_city_code;
156 l_city := null;
157 l_city_code := null;
158 --
159 -- Debug Statements
160 --
161 IF l_debug_on THEN
162 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_ALL_REGION_MATCHES',WSH_DEBUG_SV.C_PROC_LEVEL);
163 END IF;
164 --
165 BEGIN
166 WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches(p_country => l_country,
167 p_country_region => null,
168 p_state => l_state,
169 p_city => l_city,
170 p_postal_code_from => p_zip,
171 p_postal_code_to => p_zip,
172 p_country_code => l_country_code,
173 p_country_region_code => null,
174 p_state_code => l_state_code,
175 p_city_code => l_city_code,
176 p_lang_code => userenv('LANG'),
177 p_location_id => p_loc_id,
178 p_zone_flag => 'N',
179 p_more_matches => TRUE,
180 x_status => l_status,
181 x_regions => l_regions_temp);
182
183 EXCEPTION
184 WHEN NO_DATA_FOUND THEN
185 IF l_debug_on THEN
186 WSH_DEBUG_SV.logmsg(l_module_name,'No Region Found for ' || l_country || ', ' || l_state || ', ' || l_city);
187 END IF;
188 END;
189 END IF;
190
191 -- set up array of base regions and parent regions
192 -- Parent regions contains all the regions found. The base regions
193 -- are all the regions that have the same input type as the passed in
194 -- criteria region type.
195 IF (l_regions_temp.COUNT > 0) THEN
196 FOR i IN l_regions_temp.FIRST..l_regions_temp.LAST LOOP
197 l_region := l_regions_temp(i);
198 l_region_and_type.region_id := l_region.region_id;
199 l_region_and_type.region_type := l_region.region_type;
200
201 l_region_and_type.STATE := l_region.STATE;
202 l_region_and_type.STATE_CODE := l_region.STATE_CODE;
203 l_region_and_type.CITY := l_region.CITY;
204 l_region_and_type.CITY_CODE := l_region.CITY_CODE;
205
206 IF (l_region.is_input_type ='Y') THEN
207 l_regions(l_regions.count +1) := l_region_and_type;
208 END IF;
209
210 l_parent_regions(l_parent_regions.count +1) := l_region_and_type;
211 END LOOP;
212 END IF;
213
214 -- if location id was passed in, obtain zip from wsh_locations API
215 IF (p_loc_id is not null AND p_zip is null) THEN
216 OPEN get_postal_code(p_loc_id);
217 FETCH get_postal_code INTO p_zip;
218 CLOSE get_postal_code;
219 END IF;
220
221 x_regions := l_regions;
222 x_parent_regions := l_parent_regions;
223
224 --
225 -- Debug Statements
226 --
227 IF l_debug_on THEN
228 WSH_DEBUG_SV.pop(l_module_name);
229 END IF;
230 END Set_Up_Regions;
231
232
233 -- ----------------------------------------------------------------------------
234 -- Name: Is_Vehicle_Available
235 -- Type: Function
236 --
237 -- Description: This procedure takes in a lane id, and checks to see
238 -- if the carrier has vehicles available for the lane on the
239 -- specified date.
240 -- -----------------------------------------------------------------------------
241 FUNCTION Is_Vehicle_Available(p_vehicle_id IN NUMBER, --fte_vehicle_type_id
242 p_carrier_id IN NUMBER,
243 p_origin_id IN NUMBER,
244 p_dest_id IN NUMBER,
245 p_mode IN VARCHAR2,
246 p_date IN DATE,
247 x_return_message OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
248
249 l_debug_on BOOLEAN;
250 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Is_Vehicle_Available';
251
252 l_id NUMBER;
253 l_available BOOLEAN;
254
255 cursor c_vehicle_available IS
256 select 1
257 from wsh_carrier_vehicle_types
258 where carrier_id = p_carrier_id
259 and vehicle_type_id = p_vehicle_id
260 and assigned_flag = 'Y'
261 and not exists
262 (select 1 from fte_lane_vehicles
263 where quantity = 0
264 and origin_id = p_origin_id
265 and destination_id = p_dest_id
266 and carrier_id = p_carrier_id
267 and nvl(effective_start_date, nvl(p_date, sysdate)) <= nvl(p_date, sysdate)
268 and nvl(effective_end_date, nvl(p_date, sysdate)) >= nvl(p_date, sysdate)
269 UNION
270 select 1 from fte_lane_group_components c, fte_lane_vehicles l
271 where l.quantity = 0
272 and c.lane_group_id = l.lane_group_id
273 and c.origin_id = p_origin_id
274 and c.destination_id = p_dest_id
275 and l.carrier_id = p_carrier_id
276 and nvl(effective_start_date, nvl(p_date, sysdate)) <= nvl(p_date, sysdate)
277 and nvl(effective_end_date, nvl(p_date, sysdate)) >= nvl(p_date, sysdate));
278
279
280 BEGIN
281
282 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
283 --
284 IF l_debug_on IS NULL
285 THEN
286 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
287 END IF;
288
289 IF l_debug_on THEN
290 WSH_DEBUG_SV.push(l_module_name);
291 WSH_DEBUG_SV.log(l_module_name,'p_vehicle_id',p_vehicle_id);
292 WSH_DEBUG_SV.log(l_module_name, 'p_origin_id', p_origin_id);
293 WSH_DEBUG_SV.log(l_module_name, 'p_destination_id', p_dest_id);
294 WSH_DEBUG_SV.log(l_module_name, 'p_carrier_id', p_carrier_id);
295 END IF;
296
297 open c_vehicle_available;
298 fetch c_vehicle_available into l_id;
299 l_available := c_vehicle_available%FOUND;
300 close c_vehicle_available;
301
302 --If we don't find a row for the carrier, origin and destination
303 --with zero quantity in fte_lane_vehicles, then the vehicle is available
304 --only if it is assigned to the carrier. This means that the vehicle HAS
305 --to be assigned to the carrier in order for the lane to be used.
306 IF (NOT l_available) THEN
310
307 x_return_message := 'The vehicle is either not assigned to the carrier ' ||
308 'or has an availability of 0 on the specified date.';
309 END IF;
311 --
312 -- Debug Statements
313 --
314 IF l_debug_on THEN
315 WSH_DEBUG_SV.pop(l_module_name);
316 END IF;
317
318 return l_available;
319
320 END Is_Vehicle_Available;
321
322
323
324
325
326 -- ----------------------------------------------------------------
327 -- Name: Bind_Vars
328 -- Type: Procedure
329 --
330 -- Description: This procedure takes in a bind variable array
331 -- and binds the variables according to the
332 -- bind variable index and bind variable type
333 -- using dbms_sql, given a cursor_id.
334 --
335 -- -----------------------------------------------------------------
336 PROCEDURE Bind_Vars(p_cursor_id IN NUMBER,
337 p_bindvars IN FTE_LANE_SEARCH_QUERY_GEN.bindvars) IS
338
339 --
340 l_debug_on BOOLEAN;
341 --
342 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'BIND_VARS';
343 --
344 BEGIN
345
346 --
347 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
348 --
349 IF l_debug_on IS NULL
350 THEN
351 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
352 END IF;
353 --
354 --
355 -- Debug Statements
356 --
357 IF l_debug_on THEN
358 WSH_DEBUG_SV.push(l_module_name);
359 --
360 WSH_DEBUG_SV.log(l_module_name,'P_CURSOR_ID',P_CURSOR_ID);
361 END IF;
362 --
363 IF (p_bindvars.COUNT > 0) THEN
364
365 FOR l_counter IN p_bindvars.FIRST..p_bindvars.LAST LOOP
366
367 --
368 -- Debug Statements
369 --
370 IF l_debug_on THEN
371 WSH_DEBUG_SV.logmsg(l_module_name,'bind var ' || p_bindvars(l_counter).bindvarindex || ' = ' || p_bindvars(l_counter).bindvar,WSH_DEBUG_SV.C_PROC_LEVEL);
372 END IF;
373
374 IF (p_bindvars(l_counter).bindtype = 'NUMBER') THEN
375 dbms_sql.BIND_VARIABLE(p_cursor_id, ':' || p_bindvars(l_counter).bindvarindex, to_number(p_bindvars(l_counter).bindvar));
376 --g_message := g_message || 'bind ' || p_bindvars(l_counter).bindvarindex || ' = ' || p_bindvars(l_counter).bindvar;
377 ELSIF (p_bindvars(l_counter).bindtype = 'DATE') THEN
378 dbms_sql.BIND_VARIABLE(p_cursor_id, ':' || p_bindvars(l_counter).bindvarindex, to_date(p_bindvars(l_counter).bindvar, 'mm-dd-yyyy hh24:mi'));
379 --g_message := g_message || 'bind ' || p_bindvars(l_counter).bindvarindex || ' = ' || 'to_date(''' || p_bindvars(l_counter).bindvar||''', ''mm-dd-yyyy hh24:mi'')';
380 ELSE
381 dbms_sql.BIND_VARIABLE(p_cursor_id, ':' || p_bindvars(l_counter).bindvarindex, p_bindvars(l_counter).bindvar);
382 --g_message := g_message || 'bind ' || p_bindvars(l_counter).bindvarindex || ' = ''' || p_bindvars(l_counter).bindvar || '''';
383 END IF;
384 END LOOP;
385
386 END IF;
387
388 --
389 -- Debug Statements
390 --
391 IF l_debug_on THEN
392 WSH_DEBUG_SV.pop(l_module_name);
393 END IF;
394 --
395 END Bind_Vars;
396
397
398 -- ----------------------------------------------------------------
399 -- Name: Get_Transit_Time (Pack J)
400 -- Type: Procedure
401 --
402 -- Description: This procedure checks whether for the ship method
403 -- passed in, transit time already exists in the
404 -- cache. If not, obtains the transit time from
405 -- ATP if the transit time passed in is null,
406 -- otherwise, converts the transit time passed
407 -- in into DAYS
408 --
409 -- Input: p_ship_from_loc_id
410 -- p_ship_to_site_id
411 -- p_carrier_id
412 -- p_service_code
413 -- p_mode_code
414 -- x_transit_time
415 -- x_transit_time_uom
416 -- ----------------------------------------------------------------
417 PROCEDURE Get_Transit_Time(p_ship_from_loc_id IN NUMBER,
418 p_ship_to_site_id IN NUMBER,
419 p_carrier_id IN NUMBER,
420 p_service_code IN VARCHAR2,
421 p_mode_code IN VARCHAR2,
422 p_from IN VARCHAR2,
423 x_transit_time OUT NOCOPY NUMBER,
424 x_return_status OUT NOCOPY VARCHAR2) IS
425
426 --
427 l_debug_on BOOLEAN;
428 --
429 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_TRANSIT_TIME';
430 --
431
432
433 BEGIN
434 --
435 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
436 --
437 IF l_debug_on IS NULL
438 THEN
439 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
440 END IF;
441 --
445 IF l_debug_on THEN
442 --
443 -- Debug Statements
444 --
446 WSH_DEBUG_SV.push(l_module_name);
447 --
448 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_LOC_ID',P_SHIP_FROM_LOC_ID);
449 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_SITE_ID',P_SHIP_TO_SITE_ID);
450 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
451 WSH_DEBUG_SV.log(l_module_name,'P_SERVICE_CODE',P_SERVICE_CODE);
452 WSH_DEBUG_SV.log(l_module_name,'P_MODE_CODE',P_MODE_CODE);
453 END IF;
454 --
455
456 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
457 --Logic for calculating Transit time is in WSH_MAP_LOCATION_REGION_PKG Bug 4653381
458 WSH_MAP_LOCATION_REGION_PKG.Get_Transit_Time(
459 p_ship_from_loc_id => p_ship_from_loc_id ,
460 p_ship_to_site_id=>p_ship_to_site_id,
461 p_ship_method_code=>null,
462 p_carrier_id=>p_carrier_id,
463 p_service_code=>p_service_code,
464 p_mode_code=>p_mode_code,
465 p_from=>p_from,
466 x_transit_time=>x_transit_time,
467 x_return_status=>x_return_status);
468
469 IF l_debug_on THEN
470 WSH_DEBUG_SV.log(l_module_name,'Transit Time: ', x_transit_time);
471 WSH_DEBUG_SV.log(l_module_name,'x_return_status', x_return_status);
472 END IF;
473 --
474 -- Debug Statements
475 --
476 IF l_debug_on THEN
477 WSH_DEBUG_SV.pop(l_module_name);
478 END IF;
479 --
480
481 END Get_Transit_Time;
482
483 -- ----------------------------------------------------------------
484 -- Name: Search_Lanes
485 -- Type: Procedure
486 --
487 -- Description: OverLoaded method for the search_lanes API. This
488 -- will be called if we have more than one set of search criteria
489 -- Duplicate fetches in the search would be removed
490 -- p_search_type 'L' - Lanes
491 -- 'S' - Scheduldes
492 -- Limitations:
493 -- and p_source_type = 'R'
494 -- -----------------------------------------------------------------
495 PROCEDURE Search_Lanes( p_search_criteria IN fte_search_criteria_tab,
496 p_num_results IN NUMBER,
497 p_search_type IN VARCHAR2,
498 x_lane_results OUT NOCOPY fte_lane_tab,
499 x_schedule_results OUT NOCOPY fte_schedule_tab,
500 x_return_message OUT NOCOPY VARCHAR2,
501 x_return_status OUT NOCOPY VARCHAR2) IS
502
503
504 type t_result_ids is table of NUMBER index by binary_integer;
505
506 l_lane_results fte_lane_tab;
507 l_lane_id NUMBER;
508 l_schedule_id NUMBER;
509 i NUMBER;
510 j NUMBER;
511 l_result_ids t_result_ids;
512
513 x_count NUMBER := 1;
514 l_schedule_results fte_schedule_tab;
515
516 --
517 l_debug_on BOOLEAN;
518 --
519 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SEARCH_LANES(Multi)';
520 --
521
522 BEGIN
523 --
524 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
525 --
526 x_return_message := WSH_UTIL_CORE.g_ret_sts_success;
527 IF l_debug_on IS NULL
528 THEN
529 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
530 END IF;
531 --
532 --
533 -- Debug Statements
534 --
535 IF l_debug_on THEN
536 WSH_DEBUG_SV.push(l_module_name);
537 --
538 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.COUNT',p_search_criteria.COUNT);
539 WSH_DEBUG_SV.log(l_module_name,'P_NUM_RESULTS',P_NUM_RESULTS);
540 END IF;
541 --
542 x_lane_results:= fte_lane_tab();
543 x_schedule_results := fte_schedule_tab();
544 i := p_search_criteria.FIRST;
545 WHILE i is not null LOOP
546 l_lane_results := fte_lane_tab();
547 l_schedule_results := fte_schedule_tab();
548 Search_Lanes( p_search_criteria => p_search_criteria(i),
549 p_search_type => p_search_type,
550 p_source_type => 'R',
551 p_num_results => (p_num_results-x_count),
552 x_lane_results => l_lane_results,
553 x_schedule_results => l_schedule_results,
554 x_return_message => x_return_message,
555 x_return_status => x_return_status);
556 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
557 x_return_message := FND_MESSAGE.GET;
558 return;
559 END IF;
560 IF p_search_type = 'L' THEN
561 IF(l_lane_results.COUNT > 0 ) THEN
562 j:=l_lane_results.FIRST;
563 WHILE j is not NULL LOOP
564 l_lane_id := l_lane_results(j).LANE_ID;
565 IF (NOT (l_result_ids.EXISTS(l_lane_id))) THEN
566 x_lane_results.EXTEND;
567 x_lane_results(x_count) := l_lane_results(j);
568 x_count := x_count + 1;
569 l_result_ids(l_lane_id) := l_lane_id;
570 END IF;
571 j := l_lane_results.NEXT(j);
572 END LOOP;
573 END IF;
574 ELSIF p_search_type = 'S' THEN
575 IF(l_schedule_results.COUNT > 0 ) THEN
576 j:=l_schedule_results.FIRST;
577 WHILE j is not NULL LOOP
578 l_schedule_id := l_schedule_results(j).SCHEDULE_ID;
582 x_count := x_count + 1;
579 IF (NOT (l_result_ids.EXISTS(l_schedule_id))) THEN
580 x_schedule_results.EXTEND;
581 x_schedule_results(x_count) := l_schedule_results(j);
583 l_result_ids(l_schedule_id) := l_schedule_id;
584 END IF;
585 j := l_schedule_results.NEXT(j);
586 END LOOP;
587 END IF;
588 END IF;
589 IF (x_count >= p_num_results) THEN
590 EXIT;
591 END IF;
592 i := p_search_criteria.NEXT(i);
593 END LOOP;
594 --
595 IF l_debug_on THEN
596 WSH_DEBUG_SV.log(l_module_name,'x_lane_results.COUNT',x_count);
597 WSH_DEBUG_SV.pop(l_module_name);
598 END IF;
599 --
600 EXCEPTION
601 WHEN OTHERS THEN
602 x_return_message := g_message || 'Unexpected Error in Search_Lanes Package: ' || sqlerrm;
603 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
604 IF l_debug_on THEN
605 WSH_DEBUG_SV.pop(l_module_name);
606 END IF;
607
608 END Search_Lanes;
609
610 -- ----------------------------------------------------------------
611 -- Name: Search_Lanes
612 -- Type: Procedure
613 --
614 -- Description: This procedure calls FTE_LANE_SEARCH_QUERY_GEN
615 -- procedures to create the dynamic SQL.
616 -- Binds the variables, executes the query, and
617 -- prepares the results in SQL records and SQL
618 -- types for returning to the calling procedure.
619 -- Constraints checking is done for lanes if
620 -- a delivery_id or delivery_leg_id is present.
621 --
622 -- Pack J:
623 -- Transit time info is obtained from ATP if
624 -- p_source_type = 'R' (for OM-freigh rating
625 -- integration)
626 --
627 -- Input: p_search_type 'L' = lanes; 'S' = schedules
628 -- p_source_type 'L' = lanes; 'R' = rating
629 -- p_source_type 'T' = Lane Group , Commitment
630 -- p_num_results maximum number of results
631 -- desired (OA should be 200)
632 -- -----------------------------------------------------------------
633 PROCEDURE Search_Lanes(p_search_criteria IN fte_search_criteria_rec,
634 p_search_type IN VARCHAR2, -- 'L' for lanes, 'S' for schedules
635 p_source_type IN VARCHAR2,
636 p_num_results IN NUMBER,
637 x_lane_results OUT NOCOPY fte_lane_tab,
638 x_schedule_results OUT NOCOPY fte_schedule_tab,
639 x_return_message OUT NOCOPY VARCHAR2,
640 x_return_status OUT NOCOPY VARCHAR2) IS
641
642 l_search_criteria FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_criteria_rec;
643 l_origin_zip VARCHAR2(30) := null;
644 l_destination_zip VARCHAR2(30) := null;
645
646 l_origins FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab;
647 l_destinations FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab;
648 l_parent_origins FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab;
649 l_parent_dests FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab;
650
651
652 l_query1 VARCHAR2(32000);
653 l_query2 VARCHAR2(32000);
654
655 l_bindvars1 FTE_LANE_SEARCH_QUERY_GEN.bindvars;
656 l_bindvars2 FTE_LANE_SEARCH_QUERY_GEN.bindvars;
657 l_bindvars3 FTE_LANE_SEARCH_QUERY_GEN.bindvars;
658 l_bindvars4 FTE_LANE_SEARCH_QUERY_GEN.bindvars;
659
660 l_status VARCHAR2(1);
661 l_msg VARCHAR2(2000);
662
663 l_cursor_id NUMBER;
664 l_counter NUMBER;
665 l_rows_affected NUMBER;
666 l_rows_fetched NUMBER := 0;
667 l_offset NUMBER := 0;
668 l_vehicles_discarded VARCHAR2(12000);
669
670 -- variables of column type for binding sql stmt for lanes
671 v_lane_id dbms_sql.NUMBER_TABLE;
672 v_lane_number dbms_sql.VARCHAR2_TABLE;
673 v_party_name dbms_sql.VARCHAR2_TABLE;
674 v_origin_id dbms_sql.NUMBER_TABLE;
675 v_destination_id dbms_sql.NUMBER_TABLE;
676 v_mode dbms_sql.VARCHAR2_TABLE;
677 v_service dbms_sql.VARCHAR2_TABLE;
678 v_commodity dbms_sql.VARCHAR2_TABLE;
679 --v_equipment dbms_sql.VARCHAR2_TABLE;
680 v_schedules_flag dbms_sql.VARCHAR2_TABLE;
681 v_distance dbms_sql.NUMBER_TABLE;
682 v_port_of_loading dbms_sql.VARCHAR2_TABLE;
683 v_port_of_discharge dbms_sql.VARCHAR2_TABLE;
684 v_transit_time dbms_sql.NUMBER_TABLE;
685 v_rate_chart_name dbms_sql.VARCHAR2_TABLE;
686 v_basis dbms_sql.VARCHAR2_TABLE;
687 v_owner_id dbms_sql.NUMBER_TABLE;
688 v_carrier_id dbms_sql.NUMBER_TABLE;
689 v_mode_code dbms_sql.VARCHAR2_TABLE;
690 v_transit_time_uom dbms_sql.VARCHAR2_TABLE;
691 v_special_handling dbms_sql.VARCHAR2_TABLE;
692 v_addl_instr dbms_sql.VARCHAR2_TABLE;
693 v_commodity_flag dbms_sql.VARCHAR2_TABLE;
694 --v_equipment_flag dbms_sql.VARCHAR2_TABLE;
695 v_service_flag dbms_sql.VARCHAR2_TABLE;
699 v_distance_uom dbms_sql.VARCHAR2_TABLE;
696 v_comm_catg_id dbms_sql.NUMBER_TABLE;
697 --v_equipment_code dbms_sql.VARCHAR2_TABLE;
698 v_service_code dbms_sql.VARCHAR2_TABLE;
700 v_rate_chart_id dbms_sql.NUMBER_TABLE;
701 v_rate_chart_view_flag dbms_sql.VARCHAR2_TABLE;
702 v_effective_date dbms_sql.DATE_TABLE;
703 v_expiry_date dbms_sql.DATE_TABLE;
704 v_origin_region_type dbms_sql.NUMBER_TABLE;
705 v_dest_region_type dbms_sql.NUMBER_TABLE;
706 v_comm_class_code dbms_sql.VARCHAR2_TABLE;
707 v_schedules_flag_code dbms_sql.VARCHAR2_TABLE;
708 v_lane_service_id dbms_sql.NUMBER_TABLE;
709 v_tariff_name dbms_sql.VARCHAR2_TABLE;
710 v_lane_type dbms_sql.VARCHAR2_TABLE;
711
712 -- variables for keeping track of transit time info
713 l_transit_time NUMBER;
714 l_transit_time_uom VARCHAR2(30);
715
716 -- variables of column type for binding sql stmt for schedules
717 s_schedule_id dbms_sql.NUMBER_TABLE;
718 s_lane_id dbms_sql.NUMBER_TABLE;
719 s_lane_number dbms_sql.VARCHAR2_TABLE;
720 s_dep_date dbms_sql.DATE_TABLE;
721 s_arr_date dbms_sql.DATE_TABLE;
722 s_transit_time dbms_sql.NUMBER_TABLE;
723 s_frequency dbms_sql.VARCHAR2_TABLE;
724 s_effective_date dbms_sql.DATE_TABLE;
725 s_port_of_loading dbms_sql.VARCHAR2_TABLE;
726 s_port_of_discharge dbms_sql.VARCHAR2_TABLE;
727 s_expiry_date dbms_sql.DATE_TABLE;
728 s_arr_date_indicator dbms_sql.NUMBER_TABLE;
729 s_frequency_arrival dbms_sql.VARCHAR2_TABLE;
730 s_origin_id dbms_sql.NUMBER_TABLE;
731 s_destination_id dbms_sql.NUMBER_TABLE;
732 s_mode dbms_sql.VARCHAR2_TABLE;
733 s_carrier_id dbms_sql.NUMBER_TABLE;
734 s_carrier_name dbms_sql.VARCHAR2_TABLE;
735 s_dep_time dbms_sql.VARCHAR2_TABLE;
736 s_arr_time dbms_sql.VARCHAR2_TABLE;
737 s_frequency_type dbms_sql.VARCHAR2_TABLE;
738 s_transit_time_uom dbms_sql.VARCHAR2_TABLE;
739 s_vessel_type dbms_sql.VARCHAR2_TABLE;
740 s_vessel_name dbms_sql.VARCHAR2_TABLE;
741 s_voyage_number dbms_sql.VARCHAR2_TABLE;
742 s_arr_time_w_ind dbms_sql.VARCHAR2_TABLE;
743 s_mode_code dbms_sql.VARCHAR2_TABLE;
744 s_service_code dbms_sql.VARCHAR2_TABLE;
745 s_service dbms_sql.VARCHAR2_TABLE;
746 s_frequency_type_code dbms_sql.VARCHAR2_TABLE;
747 s_active_flag dbms_sql.VARCHAR2_TABLE;
748
749 l_num_rows NUMBER := 0;
750 l_lanes_rec fte_lane_rec;
751 l_lanes_tab fte_lane_tab;
752 l_schedules_rec fte_schedule_rec;
753 l_schedules_tab fte_schedule_tab;
754
755 -- similar to hash table to keep track of unique lane ids
756 type t_result_ids is table of NUMBER index by binary_integer;
757 l_result_lane_ids t_result_ids;
758 l_result_service_ids t_result_ids;
759
760 -- constraints variables
761 l_shipping_control VARCHAR2(30);
762 l_dleg_info_rec WSH_FTE_CONSTRAINT_FRAMEWORK.dleg_ccinfo_rec_type;
763 l_lane_info_rec WSH_FTE_CONSTRAINT_FRAMEWORK.lane_ccinfo_rec_type;
764 l_lane_info_tab WSH_FTE_CONSTRAINT_FRAMEWORK.lane_ccinfo_tab_type;
765 l_success_trips WSH_UTIL_CORE.id_tab_type;
766 l_success_lanes WSH_UTIL_CORE.id_tab_type;
767 l_validate_result VARCHAR2(1);
768 l_msg_count NUMBER;
769 l_msg_data VARCHAR2(400);
770 l_good_lane VARCHAR2(1);
771 l_dummy_exception WSH_UTIL_CORE.Column_Tab_Type;
772 l_dummy_trips WSH_FTE_CONSTRAINT_FRAMEWORK.trip_ccinfo_tab_type;
773
774 l_cc_lane_counter NUMBER;
775
776 --
777 l_debug_on BOOLEAN;
778 --
779 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SEARCH_LANES';
780 --
781
782 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
783 l_source_type VARCHAR2(3);
784
785
786 BEGIN
787 --
788 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
789 --
790 IF l_debug_on IS NULL
791 THEN
792 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
793 END IF;
794 --
795 --
796 -- Debug Statements
797 --
798 IF l_debug_on THEN
799 WSH_DEBUG_SV.push(l_module_name);
800 --
801 WSH_DEBUG_SV.log(l_module_name,'P_SEARCH_TYPE',P_SEARCH_TYPE);
802 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_TYPE',P_SOURCE_TYPE);
803 WSH_DEBUG_SV.log(l_module_name,'P_NUM_RESULTS',P_NUM_RESULTS);
804 END IF;
805 --
806
807 -- get origin and parents set up
808 l_origin_zip := p_search_criteria.origin_zip;
809 Set_Up_Regions(p_loc_id => p_search_criteria.origin_loc_id,
810 p_country => p_search_criteria.origin_country,
811 p_state => p_search_criteria.origin_state,
812 p_city => p_search_criteria.origin_city,
813 p_zip => l_origin_zip,
814 x_regions => l_origins,
815 x_parent_regions => l_parent_origins);
816
817
818 -- get destination and parents set up
819 l_destination_zip := p_search_criteria.destination_zip;
820 Set_Up_Regions(p_loc_id => p_search_criteria.destination_loc_id,
824 p_zip => l_destination_zip,
821 p_country => p_search_criteria.destination_country,
822 p_state => p_search_criteria.destination_state,
823 p_city => p_search_criteria.destination_city,
825 x_regions => l_destinations,
826 x_parent_regions => l_parent_dests);
827
828
829 IF l_debug_on THEN
830 WSH_DEBUG_SV.log(l_module_name,'Number of base origins', l_origins.count);
831 WSH_DEBUG_SV.log(l_module_name,'Number of base destinations',l_destinations.count);
832 WSH_DEBUG_SV.log(l_module_name,'Number of parent origins', l_parent_origins.count);
833 WSH_DEBUG_SV.log(l_module_name,'Number of parent dests',l_parent_dests.count);
834 END IF;
835
836 --g_message := g_message || 'Number of base origins' || l_origins.count;
837 --g_message := g_message || 'Number of base destinations' || l_destinations.count;
838 --g_message := g_message || 'Number of parent origins' || l_parent_origins.count;
839 --g_message := g_message || 'Number of parent dests' || l_parent_dests.count;
840
841 --If no regions are found, return.
842 IF (l_parent_dests IS NULL OR l_parent_dests.COUNT <= 0 OR
843 l_parent_origins IS NULL OR l_parent_origins.COUNT <= 0) THEN
844
845 x_lane_results := fte_lane_tab();
846 x_schedule_results := fte_schedule_tab();
847
848 x_return_message := x_return_message || 'No regions found for search criteria';
849
850 IF l_debug_on THEN
851 WSH_DEBUG_SV.pop(l_module_name);
852 END IF;
853 --
854 RETURN;
855 END IF;
856
857 IF (l_origins.COUNT > 1) THEN
858 x_lane_results := fte_lane_tab();
859 x_schedule_results := fte_schedule_tab();
860 x_return_message := x_return_message || 'Ambiguous origin input -- ' || Fnd_Global.NewLine;
861
862 FOR i IN l_origins.FIRST .. l_origins.LAST LOOP
863 x_return_message := x_return_message || l_origins(i).CITY||','||
864 nvl(l_origins(i).STATE, l_origins(i).STATE_CODE) ||'; ' || Fnd_Global.NewLine;
865 END LOOP;
866 x_return_status := 'A';
867 RETURN;
868 END IF;
869
870 IF (l_destinations.COUNT > 1) THEN
871 x_lane_results := fte_lane_tab();
872 x_schedule_results := fte_schedule_tab();
873 x_return_message := x_return_message || 'Ambiguous destination input -- ' || Fnd_Global.NewLine;
874
875 FOR i IN l_destinations.FIRST .. l_destinations.LAST LOOP
876 x_return_message := x_return_message || l_destinations(i).CITY||','||
877 nvl(l_destinations(i).STATE, l_destinations(i).STATE_CODE) || '; '||Fnd_Global.NewLine;
878 END LOOP;
879 x_return_status := 'A';
880 RETURN;
881
882 END IF;
883
884 -- set up rest of parameters
885 l_search_criteria.relax_flag := p_search_criteria.relax_flag;
886 l_search_criteria.origin_zip_request := l_origin_zip;
887 l_search_criteria.dest_zip_request := l_destination_zip;
888 l_search_criteria.mode_of_transport := p_search_criteria.mode_of_transport;
889 l_search_criteria.lane_number := p_search_criteria.lane_number;
890 l_search_criteria.carrier_id := p_search_criteria.carrier_id;
891 l_search_criteria.carrier_name := p_search_criteria.carrier_name;
892 l_search_criteria.commodity_catg_id := p_search_criteria.commodity_catg_id;
893 l_search_criteria.commodity := p_search_criteria.commodity;
894 l_search_criteria.service_code := p_search_criteria.service_code;
895 l_search_criteria.service := p_search_criteria.service;
896 --l_search_criteria.equipment_code := p_search_criteria.equipment_code;
897 --l_search_criteria.equipment := p_search_criteria.equipment;
898 l_search_criteria.schedule_only_flag := p_search_criteria.schedule_only_flag;
899 l_search_criteria.dep_date_from := p_search_criteria.dep_date_from;
900 l_search_criteria.dep_date_to := p_search_criteria.dep_date_to;
901 l_search_criteria.arr_date_from := p_search_criteria.arr_date_from;
902 l_search_criteria.arr_date_to := p_search_criteria.arr_date_to;
903 l_search_criteria.lane_ids_list := p_search_criteria.lane_ids_string;
904 l_search_criteria.tariff_name := p_search_criteria.tariff_name;
905
906 IF (l_search_criteria.dep_date_from IS NOT NULL) then
907 l_search_criteria.dep_date_from := to_date(to_char(l_search_criteria.dep_date_from,'mm-dd-yyyy hh24:mi'),
908 'mm-dd-yyyy hh24:mi');
909 END IF;
910
911 IF (l_search_criteria.dep_date_to IS NOT NULL) then
912 l_search_criteria.dep_date_to := to_date(to_char(l_search_criteria.dep_date_to,'mm-dd-yyyy hh24:mi'),
913 'mm-dd-yyyy hh24:mi');
914 END IF;
915
916 IF (l_search_criteria.arr_date_from IS NOT NULL) then
917 l_search_criteria.arr_date_from := to_date(to_char(l_search_criteria.arr_date_from,'mm-dd-yyyy hh24:mi'),
918 'mm-dd-yyyy hh24:mi');
919 END IF;
920
921 IF (l_search_criteria.arr_date_to IS NOT NULL) then
922 l_search_criteria.arr_date_to := to_date(to_char(l_search_criteria.arr_date_to,'mm-dd-yyyy hh24:mi'),
923 'mm-dd-yyyy hh24:mi');
924 END IF;
925
926 -- [08/30] Add check for Vehicle_id
930 l_search_criteria.vehicle_id := FTE_VEHICLE_PKG.Get_Vehicle_Type_Id(p_search_criteria.vehicle_id);
927 -- [11/21] Get the vehicle_id from the inventory_item_id
928 --l_search_criteria.vehicle_id := p_search_criteria.vehicle_id;
929 IF (p_search_criteria.vehicle_id IS NOT NULL) THEN
931 ELSE
932 l_search_criteria.vehicle_id := NULL;
933 END IF;
934
935 -- glami added for effective date
936 IF (p_search_criteria.effective_date IS NOT NULL) then
937 l_search_criteria.effective_date := to_date(to_char(p_search_criteria.effective_date,'mm-dd-yyyy hh24:mi'),
938 'mm-dd-yyyy hh24:mi');
939 END IF;
940
941 IF l_debug_on THEN
942 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.relax_flag', p_search_criteria.relax_flag);
943 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.origin_zip', p_search_criteria.origin_zip);
944 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.destination_zip', p_search_criteria.destination_zip);
945 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.mode_of_transport', p_search_criteria.mode_of_transport);
946 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.lane_number', p_search_criteria.lane_number);
947 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.carrier_id', p_search_criteria.carrier_id);
948 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.carrier_name', p_search_criteria.carrier_name);
949 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.commodity_catg_id', p_search_criteria.commodity_catg_id);
950 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.commodity', p_search_criteria.commodity);
951 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.service_code', p_search_criteria.service_code);
952 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.service', p_search_criteria.service);
953 --WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.equipment_code', p_search_criteria.equipment_code);
954 --WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.equipment', p_search_criteria.equipment);
955 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.schedule_only_flag', p_search_criteria.schedule_only_flag);
956 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.dep_date_from', p_search_criteria.dep_date_from);
957 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.dep_date_to', p_search_criteria.dep_date_to);
958 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.arr_date_to', p_search_criteria.arr_date_to);
959 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.arr_date_to', p_search_criteria.arr_date_to);
960 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.lane_ids_string', substr(p_search_criteria.lane_ids_string, 0, 15) || '...');
961 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.vehicle_id', p_search_criteria.vehicle_id);
962 WSH_DEBUG_SV.log(l_module_name,'New Vehicle ID ', l_search_criteria.vehicle_id);
963 -- glami added for effective date
964 WSH_DEBUG_SV.log(l_module_name,'Effective Date ', l_search_criteria.effective_date);
965 WSH_DEBUG_SV.log(l_module_name,'Effective Date Type ', l_search_criteria.effective_date_type);
966 WSH_DEBUG_SV.log(l_module_name,'Tariff Name ', l_search_criteria.tariff_name);
967 END IF;
968
969 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
970 l_source_type := p_source_type;
971
972 IF (p_search_type = 'L') THEN
973 l_lanes_tab := fte_lane_tab();
974
975 --
976 -- Debug Statements
977 --
978 IF l_debug_on THEN
979 WSH_DEBUG_SV.logmsg(l_module_name,'Calling FTE_LANE_SEARCH_QUERY_GEN.CREATE_LANE_QUERY',WSH_DEBUG_SV.C_PROC_LEVEL);
980 END IF;
981 --
982 FTE_LANE_SEARCH_QUERY_GEN.Create_Lane_Query(p_search_criteria => l_search_criteria,
983 p_origins => l_origins,
984 p_destinations => l_destinations,
985 p_parent_origins => l_parent_origins,
986 p_parent_destinations => l_parent_dests,
987 p_source_type => l_source_type,
988 x_query1 => l_query1,
989 x_query2 => l_query2,
990 x_bindvars1 => l_bindvars1,
991 x_bindvars2 => l_bindvars2,
992 x_bindvars_common => l_bindvars3,
993 x_bindvars_orderby => l_bindvars4,
994 x_return_message => l_msg,
995 x_return_status => l_status);
996
997 --
998 -- Debug Statements
999 --
1000 IF l_debug_on THEN
1001 WSH_DEBUG_SV.logmsg(l_module_name,'query:' || l_query1,WSH_DEBUG_SV.C_PROC_LEVEL);
1002 END IF;
1003
1004 l_cursor_id := dbms_sql.OPEN_CURSOR;
1005 dbms_sql.PARSE(l_cursor_id, l_query1, DBMS_SQL.V7);
1006
1007 Bind_Vars(l_cursor_id, l_bindvars1);
1008 Bind_Vars(l_cursor_id, l_bindvars3);
1009 Bind_Vars(l_cursor_id, l_bindvars4);
1010
1011 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
1012 if (p_source_type ='T') THEN
1013
1014 dbms_sql.DEFINE_ARRAY(l_cursor_id, 1, v_party_name, p_num_results, 1);
1018 dbms_sql.DEFINE_ARRAY(l_cursor_id, 5, v_mode, p_num_results, 1);
1015 dbms_sql.DEFINE_ARRAY(l_cursor_id, 2, v_carrier_id, p_num_results, 1);
1016 dbms_sql.DEFINE_ARRAY(l_cursor_id, 3, v_origin_id, p_num_results, 1);
1017 dbms_sql.DEFINE_ARRAY(l_cursor_id, 4, v_destination_id, p_num_results, 1);
1019 dbms_sql.DEFINE_ARRAY(l_cursor_id, 6, v_mode_code, p_num_results, 1);
1020 dbms_sql.DEFINE_ARRAY(l_cursor_id, 7, v_origin_region_type, p_num_results, 1);
1021 dbms_sql.DEFINE_ARRAY(l_cursor_id, 8, v_lane_id, p_num_results, 1);
1022
1023 else
1024
1025 dbms_sql.DEFINE_ARRAY(l_cursor_id, 1, v_lane_id, p_num_results, 1);
1026 dbms_sql.DEFINE_ARRAY(l_cursor_id, 2, v_lane_number, p_num_results, 1);
1027 dbms_sql.DEFINE_ARRAY(l_cursor_id, 3, v_party_name, p_num_results, 1);
1028 dbms_sql.DEFINE_ARRAY(l_cursor_id, 4, v_origin_id, p_num_results, 1);
1029 dbms_sql.DEFINE_ARRAY(l_cursor_id, 5, v_destination_id, p_num_results, 1);
1030 dbms_sql.DEFINE_ARRAY(l_cursor_id, 6, v_mode, p_num_results, 1);
1031 dbms_sql.DEFINE_ARRAY(l_cursor_id, 7, v_service, p_num_results, 1);
1032 dbms_sql.DEFINE_ARRAY(l_cursor_id, 8, v_commodity, p_num_results, 1);
1033 dbms_sql.DEFINE_ARRAY(l_cursor_id, 9, v_schedules_flag, p_num_results, 1);
1034 dbms_sql.DEFINE_ARRAY(l_cursor_id, 10, v_distance, p_num_results, 1);
1035 dbms_sql.DEFINE_ARRAY(l_cursor_id, 11, v_port_of_loading, p_num_results, 1);
1036 dbms_sql.DEFINE_ARRAY(l_cursor_id, 12, v_port_of_discharge, p_num_results, 1);
1037 dbms_sql.DEFINE_ARRAY(l_cursor_id, 13, v_transit_time, p_num_results, 1);
1038 dbms_sql.DEFINE_ARRAY(l_cursor_id, 14, v_rate_chart_name, p_num_results, 1);
1039 dbms_sql.DEFINE_ARRAY(l_cursor_id, 15, v_basis, p_num_results, 1);
1040 dbms_sql.DEFINE_ARRAY(l_cursor_id, 16, v_owner_id, p_num_results, 1);
1041 dbms_sql.DEFINE_ARRAY(l_cursor_id, 17, v_carrier_id, p_num_results, 1);
1042 dbms_sql.DEFINE_ARRAY(l_cursor_id, 18, v_mode_code, p_num_results, 1);
1043 dbms_sql.DEFINE_ARRAY(l_cursor_id, 19, v_transit_time_uom, p_num_results, 1);
1044 dbms_sql.DEFINE_ARRAY(l_cursor_id, 20, v_special_handling, p_num_results, 1);
1045 dbms_sql.DEFINE_ARRAY(l_cursor_id, 21, v_addl_instr, p_num_results, 1);
1046 dbms_sql.DEFINE_ARRAY(l_cursor_id, 22, v_commodity_flag, p_num_results, 1);
1047 dbms_sql.DEFINE_ARRAY(l_cursor_id, 23, v_service_flag, p_num_results, 1);
1048 dbms_sql.DEFINE_ARRAY(l_cursor_id, 24, v_comm_catg_id, p_num_results, 1);
1049 dbms_sql.DEFINE_ARRAY(l_cursor_id, 25, v_service_code, p_num_results, 1);
1050 dbms_sql.DEFINE_ARRAY(l_cursor_id, 26, v_distance_uom, p_num_results, 1);
1051 dbms_sql.DEFINE_ARRAY(l_cursor_id, 27, v_rate_chart_id, p_num_results, 1);
1052 dbms_sql.DEFINE_ARRAY(l_cursor_id, 28, v_rate_chart_view_flag, p_num_results, 1);
1053 dbms_sql.DEFINE_ARRAY(l_cursor_id, 29, v_effective_date, p_num_results, 1);
1054 dbms_sql.DEFINE_ARRAY(l_cursor_id, 30, v_expiry_date, p_num_results, 1);
1055 dbms_sql.DEFINE_ARRAY(l_cursor_id, 31, v_origin_region_type, p_num_results, 1);
1056 dbms_sql.DEFINE_ARRAY(l_cursor_id, 32, v_dest_region_type, p_num_results, 1);
1057 dbms_sql.DEFINE_ARRAY(l_cursor_id, 33, v_comm_class_code, p_num_results, 1);
1058 dbms_sql.DEFINE_ARRAY(l_cursor_id, 34, v_schedules_flag_code, p_num_results, 1);
1059 dbms_sql.DEFINE_ARRAY(l_cursor_id, 35, v_lane_service_id, p_num_results, 1);
1060 dbms_sql.DEFINE_ARRAY(l_cursor_id, 36, v_tariff_name, p_num_results, 1);
1061 dbms_sql.DEFINE_ARRAY(l_cursor_id, 37, v_lane_type, p_num_results, 1);
1062
1063 end if;
1064
1065 l_rows_affected := dbms_sql.EXECUTE(l_cursor_id);
1066
1067 LOOP
1068 l_rows_fetched := dbms_sql.FETCH_ROWS(l_cursor_id);
1069 IF (l_rows_fetched = 0) THEN
1070 EXIT;
1071 END IF;
1072
1073 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
1074 if (p_source_type ='T') THEN
1075
1076 dbms_sql.COLUMN_VALUE(l_cursor_id, 1, v_party_name);
1077 dbms_sql.COLUMN_VALUE(l_cursor_id, 2, v_carrier_id);
1078 dbms_sql.COLUMN_VALUE(l_cursor_id, 3, v_origin_id);
1079 dbms_sql.COLUMN_VALUE(l_cursor_id, 4, v_destination_id);
1080 dbms_sql.COLUMN_VALUE(l_cursor_id, 5, v_mode);
1081 dbms_sql.COLUMN_VALUE(l_cursor_id, 6, v_mode_code);
1082 dbms_sql.COLUMN_VALUE(l_cursor_id, 7, v_origin_region_type);
1083 dbms_sql.COLUMN_VALUE(l_cursor_id, 8, v_lane_id);
1084
1085
1086 else
1087
1088 dbms_sql.COLUMN_VALUE(l_cursor_id, 1, v_lane_id);
1089 dbms_sql.COLUMN_VALUE(l_cursor_id, 2, v_lane_number);
1090 dbms_sql.COLUMN_VALUE(l_cursor_id, 3, v_party_name);
1091 dbms_sql.COLUMN_VALUE(l_cursor_id, 4, v_origin_id);
1092 dbms_sql.COLUMN_VALUE(l_cursor_id, 5, v_destination_id);
1093 dbms_sql.COLUMN_VALUE(l_cursor_id, 6, v_mode);
1094 dbms_sql.COLUMN_VALUE(l_cursor_id, 7, v_service);
1095 dbms_sql.COLUMN_VALUE(l_cursor_id, 8, v_commodity);
1096 dbms_sql.COLUMN_VALUE(l_cursor_id, 9, v_schedules_flag);
1097 dbms_sql.COLUMN_VALUE(l_cursor_id, 10, v_distance);
1098 dbms_sql.COLUMN_VALUE(l_cursor_id, 11, v_port_of_loading);
1099 dbms_sql.COLUMN_VALUE(l_cursor_id, 12, v_port_of_discharge);
1100 dbms_sql.COLUMN_VALUE(l_cursor_id, 13, v_transit_time);
1101 dbms_sql.COLUMN_VALUE(l_cursor_id, 14, v_rate_chart_name);
1102 dbms_sql.COLUMN_VALUE(l_cursor_id, 15, v_basis);
1106 dbms_sql.COLUMN_VALUE(l_cursor_id, 19, v_transit_time_uom);
1103 dbms_sql.COLUMN_VALUE(l_cursor_id, 16, v_owner_id);
1104 dbms_sql.COLUMN_VALUE(l_cursor_id, 17, v_carrier_id);
1105 dbms_sql.COLUMN_VALUE(l_cursor_id, 18, v_mode_code);
1107 dbms_sql.COLUMN_VALUE(l_cursor_id, 20, v_special_handling);
1108 dbms_sql.COLUMN_VALUE(l_cursor_id, 21, v_addl_instr);
1109 dbms_sql.COLUMN_VALUE(l_cursor_id, 22, v_commodity_flag);
1110 dbms_sql.COLUMN_VALUE(l_cursor_id, 23, v_service_flag);
1111 dbms_sql.COLUMN_VALUE(l_cursor_id, 24, v_comm_catg_id);
1112 dbms_sql.COLUMN_VALUE(l_cursor_id, 25, v_service_code);
1113 dbms_sql.COLUMN_VALUE(l_cursor_id, 26, v_distance_uom);
1114 dbms_sql.COLUMN_VALUE(l_cursor_id, 27, v_rate_chart_id);
1115 dbms_sql.COLUMN_VALUE(l_cursor_id, 28, v_rate_chart_view_flag);
1116 dbms_sql.COLUMN_VALUE(l_cursor_id, 29, v_effective_date);
1117 dbms_sql.COLUMN_VALUE(l_cursor_id, 30, v_expiry_date);
1118 dbms_sql.COLUMN_VALUE(l_cursor_id, 31, v_origin_region_type);
1119 dbms_sql.COLUMN_VALUE(l_cursor_id, 32, v_dest_region_type);
1120 dbms_sql.COLUMN_VALUE(l_cursor_id, 33, v_comm_class_code);
1121 dbms_sql.COLUMN_VALUE(l_cursor_id, 34, v_schedules_flag_code);
1122 dbms_sql.COLUMN_VALUE(l_cursor_id, 35, v_lane_service_id);
1123 dbms_sql.COLUMN_VALUE(l_cursor_id, 36, v_tariff_name);
1124 dbms_sql.COLUMN_VALUE(l_cursor_id, 37, v_lane_type);
1125 end if;
1126
1127 FOR n IN 1..l_rows_fetched LOOP
1128
1129 -- if this lane is not already there OR
1130 -- if this lane is already there, but the lane service is not already there
1131 IF ((NOT l_result_lane_ids.EXISTS(v_lane_id(n + l_offset)))
1132 OR (l_result_lane_ids.EXISTS(v_lane_id(n + l_offset)) AND
1133 v_lane_service_id(n + l_offset) is not null AND
1134 NOT l_result_service_ids.EXISTS(v_lane_service_id(n + l_offset)))) THEN
1135
1136 IF l_debug_on THEN
1137 WSH_DEBUG_SV.logmsg(l_module_name,'Seeking Transit Time');
1138 END IF;
1139
1140 l_transit_time := null; -- v_transit_time(n + l_offset); -- this is what's on the lane
1141 l_transit_time_uom := 'DAY';
1142
1143 -- obtain transit time from ATP if transit time on lane is null
1144 -- convert transit time on lane to DAYS if necessary
1145
1146 IF l_debug_on THEN
1147 WSH_DEBUG_SV.logmsg(l_module_name,'p_source_type:'||p_source_type );
1148 WSH_DEBUG_SV.logmsg(l_module_name,'p_search_criteria.pickupstop_location_id :'||p_search_criteria.pickupstop_location_id );
1149 WSH_DEBUG_SV.logmsg(l_module_name,' p_search_criteria.ship_to_site_id :'|| p_search_criteria.ship_to_site_id );
1150 WSH_DEBUG_SV.logmsg(l_module_name,' p_search_criteria.dropoffstop_location_id:'|| p_search_criteria.dropoffstop_location_id );
1151 END IF;
1152
1153 IF (p_source_type = 'R' AND
1154 p_search_criteria.pickupstop_location_id is not null AND
1155 p_search_criteria.ship_to_site_id is not null) THEN
1156
1157
1158 IF l_debug_on THEN
1159 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Transit_Time for OM:' );
1160 END IF;
1161 Get_Transit_Time(p_ship_from_loc_id => p_search_criteria.pickupstop_location_id,
1162 p_ship_to_site_id => p_search_criteria.ship_to_site_id,
1163 p_carrier_id => v_carrier_id(n + l_offset),
1164 p_service_code => v_service_code(n + l_offset),
1165 p_mode_code => v_mode_code(n + l_offset),
1166 p_from => 'OM',
1167 x_transit_time => l_transit_time,
1168 x_return_status => l_status);
1169
1170
1171 ELSE IF (p_source_type = 'R' AND p_search_criteria.pickupstop_location_id is not null ) THEN
1172
1173 IF l_debug_on THEN
1174 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Transit_Time for Trips / deliveries' );
1175 END IF;
1176 Get_Transit_Time(p_ship_from_loc_id => p_search_criteria.pickupstop_location_id,
1177 p_ship_to_site_id => p_search_criteria.dropoffstop_location_id,
1178 p_carrier_id => v_carrier_id(n + l_offset),
1179 p_service_code => v_service_code(n + l_offset),
1180 p_mode_code => v_mode_code(n + l_offset),
1181 p_from => null,
1182 x_transit_time => l_transit_time,
1186
1183 x_return_status => l_status);
1184 End IF;
1185 END IF;
1187 IF (l_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1188 x_return_status := l_status;
1189 FND_MESSAGE.SET_NAME('FTE', 'FTE_CANNOT_GET_TRANSIT_TIME');
1190 FND_MESSAGE.SET_TOKEN('LANE_NUMBER', v_lane_number(n + l_offset));
1191 x_return_message := FND_MESSAGE.GET;
1192 return;
1193 END IF;
1194 --END IF;
1195
1196 -- bug 3357370 - on 1/8/2004 by hjpark
1197 -- if transit_time is null, set transit_time_uom to null
1198 if (l_transit_time is null or l_transit_time = '') then
1199 l_transit_time_uom := null;
1200 end if;
1201
1202 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
1203 if (p_source_type ='T') THEN
1204
1205 l_lanes_rec := fte_lane_rec(v_lane_id(n + l_offset),
1206 '',
1207 v_origin_id(n + l_offset),
1208 v_destination_id(n + l_offset),
1209 v_carrier_id(n + l_offset),
1210 v_mode_code(n + l_offset),
1211 '',
1212 '',
1213 '',
1214 '',
1215 '',
1216 '',
1217 '',
1218 '',
1219 '',
1220 '',
1221 v_party_name(n + l_offset),
1222 v_mode(n + l_offset),
1223 '',
1224 '',
1225 '',
1226 '',
1227 '',
1228 '',
1229 '',
1230 '',
1231 '',
1232 '',
1233 '',
1234 '',
1235 '',
1236 '',
1237 v_origin_region_type(n + l_offset),
1238 '',
1239 '',
1240 '');
1241
1242 else
1243
1244 l_lanes_rec := fte_lane_rec(v_lane_id(n + l_offset),
1245 v_lane_number(n + l_offset),
1246 v_origin_id(n + l_offset),
1247 v_destination_id(n + l_offset),
1248 v_carrier_id(n + l_offset),
1249 v_mode_code(n + l_offset),
1250 v_comm_class_code(n + l_offset),
1251 v_comm_catg_id(n + l_offset),
1252 v_service_code(n + l_offset),
1253 v_schedules_flag_code(n + l_offset),
1254 v_rate_chart_id(n + l_offset),
1255 v_basis(n + l_offset),
1256 l_transit_time,
1257 l_transit_time_uom,
1258 v_distance(n + l_offset),
1259 v_distance_uom(n + l_offset),
1260 v_party_name(n + l_offset),
1261 v_mode(n + l_offset),
1262 v_commodity(n + l_offset),
1263 v_service(n + l_offset),
1264 v_schedules_flag(n + l_offset),
1265 v_port_of_loading(n + l_offset),
1266 v_port_of_discharge(n + l_offset),
1267 v_rate_chart_name(n + l_offset),
1268 v_owner_id(n + l_offset),
1269 v_special_handling(n + l_offset),
1270 v_addl_instr(n + l_offset),
1271 v_commodity_flag(n + l_offset),
1272 v_service_flag(n + l_offset),
1273 v_rate_chart_view_flag(n + l_offset),
1274 v_effective_date(n + l_offset),
1278 v_tariff_name(n + l_offset),
1275 v_expiry_date(n + l_offset),
1276 v_origin_region_type(n + l_offset),
1277 v_dest_region_type(n + l_offset),
1279 v_lane_type(n + l_offset));
1280
1281 end if;
1282
1283 -- [08/30] Add check for Vehicle_id
1284 -- only if the Lane has TL as Mode of Transport
1285 IF (l_search_criteria.vehicle_id is not null AND v_mode_code(n + l_offset) ='TRUCK') THEN
1286 WSH_DEBUG_SV.logmsg(l_module_name,
1287 'Check Vehicle Availability for Lane ' || v_lane_number(n + l_offset));
1288
1289 IF( Is_Vehicle_Available(p_vehicle_id => l_search_criteria.vehicle_id,
1290 p_carrier_id => v_carrier_id(n + l_offset),
1291 p_origin_id => v_origin_id(n + l_offset),
1292 p_dest_id => v_destination_id(n + l_offset),
1293 p_mode => v_mode(n + l_offset),
1294 p_date => l_search_criteria.dep_date_from,
1295 x_return_message => x_return_message) ) THEN
1296
1297 --g_message := g_message || '; keep ' || v_lane_id(n + l_offset);
1298 l_lanes_tab.EXTEND;
1299 l_lanes_tab(l_num_rows + 1) := l_lanes_rec;
1300 l_num_rows := l_num_rows + 1;
1301 l_result_lane_ids(v_lane_id(n + l_offset)) := v_lane_id(n + l_offset);
1302
1303 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
1304 IF (p_source_type <> 'T' and v_lane_service_id(n + l_offset) is not null ) THEN
1305 l_result_service_ids(v_lane_service_id(n + l_offset)) := v_lane_service_id(n + l_offset);
1306 END IF;
1307 ELSE
1308 l_vehicles_discarded := l_vehicles_discarded || v_lane_id(n+l_offset) || ', ';
1309
1310 WSH_DEBUG_SV.logmsg(l_module_name, x_return_message);
1311 WSH_DEBUG_SV.logmsg(l_module_name, 'Vehicle unavailable for lane ' || v_lane_number(n+l_offset));
1312
1313 END IF;
1314 ELSE
1315 --g_message := g_message || ' Not TL or vehicle null ' || v_lane_id(n + l_offset);
1316 l_lanes_tab.EXTEND;
1317 l_lanes_tab(l_num_rows + 1) := l_lanes_rec;
1318 l_num_rows := l_num_rows + 1;
1319 l_result_lane_ids(v_lane_id(n + l_offset)) := v_lane_id(n + l_offset);
1320
1321 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
1322 IF (p_source_type <>'T' and v_lane_service_id(n + l_offset) is not null ) THEN
1323 WSH_DEBUG_SV.logmsg(l_module_name,'Point 1.1',WSH_DEBUG_SV.C_PROC_LEVEL);
1324 l_result_service_ids(v_lane_service_id(n + l_offset)) := v_lane_service_id(n + l_offset);
1325 END IF;
1326
1327 END IF;
1328
1329 END IF;
1330
1331 END LOOP;
1332
1333 l_offset := l_offset + l_rows_fetched;
1334
1335 EXIT WHEN (l_rows_fetched < p_num_results OR l_lanes_tab.COUNT >= p_num_results);
1336 END LOOP;
1337
1338 x_return_message := x_return_message || l_msg;
1339
1340 -- GLAMI
1341 -- MAKE CHANGES FOR TENNESSEE TO NEW YORK ISSUE
1342 -- If there is not a Match we have to set l_search_criteria.relax_flag to 'N' so that will avoid
1343 -- to relax down
1344 IF (l_origins.COUNT = 0 or l_destinations.count =0) THEN
1345 l_search_criteria.relax_flag :='N';
1346 x_return_message := x_return_message || ' No exact match found for search criteria. ' ||
1347 '=> Cannot relax down.';
1348 END IF;
1349
1350 -- relax down only if want to relax and not enough results yet
1351 IF (l_search_criteria.relax_flag = 'Y' AND l_query2 is not null AND
1352 l_lanes_tab.COUNT < p_num_results AND l_lanes_tab.COUNT < 20) THEN
1353
1354 --g_message := g_message || 'Relaxing Down ...';
1355
1356 l_offset := 0; -- reset the offset
1357
1358 --
1359 -- Debug Statements
1360 --
1361 IF l_debug_on THEN
1362 WSH_DEBUG_SV.logmsg(l_module_name,'query2:' || l_query2,WSH_DEBUG_SV.C_PROC_LEVEL);
1363 END IF;
1364
1365 dbms_sql.PARSE(l_cursor_id, l_query2, DBMS_SQL.V7);
1366
1367 Bind_Vars(l_cursor_id, l_bindvars2);
1368 Bind_Vars(l_cursor_id, l_bindvars3);
1369 WSH_DEBUG_SV.logmsg(l_module_name,'Query^^^^^^^^ :'||l_query2 );
1370 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
1371 if (p_source_type ='T') THEN
1372
1373 dbms_sql.DEFINE_ARRAY(l_cursor_id, 1, v_party_name, p_num_results, 1);
1374 dbms_sql.DEFINE_ARRAY(l_cursor_id, 2, v_carrier_id, p_num_results, 1);
1378 dbms_sql.DEFINE_ARRAY(l_cursor_id, 6, v_mode_code, p_num_results, 1);
1375 dbms_sql.DEFINE_ARRAY(l_cursor_id, 3, v_origin_id, p_num_results, 1);
1376 dbms_sql.DEFINE_ARRAY(l_cursor_id, 4, v_destination_id, p_num_results, 1);
1377 dbms_sql.DEFINE_ARRAY(l_cursor_id, 5, v_mode, p_num_results, 1);
1379 dbms_sql.DEFINE_ARRAY(l_cursor_id, 7, v_origin_region_type, p_num_results, 1);
1380 dbms_sql.DEFINE_ARRAY(l_cursor_id, 8, v_lane_id, p_num_results, 1);
1381
1382 else
1383
1384 dbms_sql.DEFINE_ARRAY(l_cursor_id, 1, v_lane_id, p_num_results, 1);
1385 dbms_sql.DEFINE_ARRAY(l_cursor_id, 2, v_lane_number, p_num_results, 1);
1386 dbms_sql.DEFINE_ARRAY(l_cursor_id, 3, v_party_name, p_num_results, 1);
1387 dbms_sql.DEFINE_ARRAY(l_cursor_id, 4, v_origin_id, p_num_results, 1);
1388 dbms_sql.DEFINE_ARRAY(l_cursor_id, 5, v_destination_id, p_num_results, 1);
1389 dbms_sql.DEFINE_ARRAY(l_cursor_id, 6, v_mode, p_num_results, 1);
1390 dbms_sql.DEFINE_ARRAY(l_cursor_id, 7, v_service, p_num_results, 1);
1391 dbms_sql.DEFINE_ARRAY(l_cursor_id, 8, v_commodity, p_num_results, 1);
1392 dbms_sql.DEFINE_ARRAY(l_cursor_id, 9, v_schedules_flag, p_num_results, 1);
1393 dbms_sql.DEFINE_ARRAY(l_cursor_id, 10, v_distance, p_num_results, 1);
1394 dbms_sql.DEFINE_ARRAY(l_cursor_id, 11, v_port_of_loading, p_num_results, 1);
1395 dbms_sql.DEFINE_ARRAY(l_cursor_id, 12, v_port_of_discharge, p_num_results, 1);
1396 dbms_sql.DEFINE_ARRAY(l_cursor_id, 13, v_transit_time, p_num_results, 1);
1397 dbms_sql.DEFINE_ARRAY(l_cursor_id, 14, v_rate_chart_name, p_num_results, 1);
1398 dbms_sql.DEFINE_ARRAY(l_cursor_id, 15, v_basis, p_num_results, 1);
1399 dbms_sql.DEFINE_ARRAY(l_cursor_id, 16, v_owner_id, p_num_results, 1);
1400 dbms_sql.DEFINE_ARRAY(l_cursor_id, 17, v_carrier_id, p_num_results, 1);
1401 dbms_sql.DEFINE_ARRAY(l_cursor_id, 18, v_mode_code, p_num_results, 1);
1402 dbms_sql.DEFINE_ARRAY(l_cursor_id, 19, v_transit_time_uom, p_num_results, 1);
1403 dbms_sql.DEFINE_ARRAY(l_cursor_id, 20, v_special_handling, p_num_results, 1);
1404 dbms_sql.DEFINE_ARRAY(l_cursor_id, 21, v_addl_instr, p_num_results, 1);
1405 dbms_sql.DEFINE_ARRAY(l_cursor_id, 22, v_commodity_flag, p_num_results, 1);
1406 dbms_sql.DEFINE_ARRAY(l_cursor_id, 23, v_service_flag, p_num_results, 1);
1407 dbms_sql.DEFINE_ARRAY(l_cursor_id, 24, v_comm_catg_id, p_num_results, 1);
1408 dbms_sql.DEFINE_ARRAY(l_cursor_id, 25, v_service_code, p_num_results, 1);
1409 dbms_sql.DEFINE_ARRAY(l_cursor_id, 26, v_distance_uom, p_num_results, 1);
1410 dbms_sql.DEFINE_ARRAY(l_cursor_id, 27, v_rate_chart_id, p_num_results, 1);
1411 dbms_sql.DEFINE_ARRAY(l_cursor_id, 28, v_rate_chart_view_flag, p_num_results, 1);
1412 dbms_sql.DEFINE_ARRAY(l_cursor_id, 29, v_effective_date, p_num_results, 1);
1413 dbms_sql.DEFINE_ARRAY(l_cursor_id, 30, v_expiry_date, p_num_results, 1);
1414 dbms_sql.DEFINE_ARRAY(l_cursor_id, 31, v_origin_region_type, p_num_results, 1);
1415 dbms_sql.DEFINE_ARRAY(l_cursor_id, 32, v_dest_region_type, p_num_results, 1);
1416 dbms_sql.DEFINE_ARRAY(l_cursor_id, 33, v_comm_class_code, p_num_results, 1);
1417 dbms_sql.DEFINE_ARRAY(l_cursor_id, 34, v_schedules_flag_code, p_num_results, 1);
1418 dbms_sql.DEFINE_ARRAY(l_cursor_id, 35, v_lane_service_id, p_num_results, 1);
1419 dbms_sql.DEFINE_ARRAY(l_cursor_id, 36, v_tariff_name, p_num_results, 1);
1420 dbms_sql.DEFINE_ARRAY(l_cursor_id, 37, v_lane_type, p_num_results, 1);
1421 end if;
1422
1423 l_rows_affected := dbms_sql.EXECUTE(l_cursor_id);
1424 LOOP
1425 l_rows_fetched := dbms_sql.FETCH_ROWS(l_cursor_id);
1426 IF (l_rows_fetched = 0) THEN
1427 EXIT;
1428 END IF;
1429
1430 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
1431 if (p_source_type ='T') THEN
1432
1433 dbms_sql.COLUMN_VALUE(l_cursor_id, 1, v_party_name);
1434 dbms_sql.COLUMN_VALUE(l_cursor_id, 2, v_carrier_id);
1435 dbms_sql.COLUMN_VALUE(l_cursor_id, 3, v_origin_id);
1436 dbms_sql.COLUMN_VALUE(l_cursor_id, 4, v_destination_id);
1437 dbms_sql.COLUMN_VALUE(l_cursor_id, 5, v_mode);
1438 dbms_sql.COLUMN_VALUE(l_cursor_id, 6, v_mode_code);
1439 dbms_sql.COLUMN_VALUE(l_cursor_id, 7, v_origin_region_type);
1440 dbms_sql.COLUMN_VALUE(l_cursor_id, 8, v_lane_id);
1441
1442 else
1443
1444 dbms_sql.COLUMN_VALUE(l_cursor_id, 1, v_lane_id);
1445 dbms_sql.COLUMN_VALUE(l_cursor_id, 2, v_lane_number);
1446 dbms_sql.COLUMN_VALUE(l_cursor_id, 3, v_party_name);
1447 dbms_sql.COLUMN_VALUE(l_cursor_id, 4, v_origin_id);
1448 dbms_sql.COLUMN_VALUE(l_cursor_id, 5, v_destination_id);
1449 dbms_sql.COLUMN_VALUE(l_cursor_id, 6, v_mode);
1450 dbms_sql.COLUMN_VALUE(l_cursor_id, 7, v_service);
1451 dbms_sql.COLUMN_VALUE(l_cursor_id, 8, v_commodity);
1452 dbms_sql.COLUMN_VALUE(l_cursor_id, 9, v_schedules_flag);
1453 dbms_sql.COLUMN_VALUE(l_cursor_id, 10, v_distance);
1457 dbms_sql.COLUMN_VALUE(l_cursor_id, 14, v_rate_chart_name);
1454 dbms_sql.COLUMN_VALUE(l_cursor_id, 11, v_port_of_loading);
1455 dbms_sql.COLUMN_VALUE(l_cursor_id, 12, v_port_of_discharge);
1456 dbms_sql.COLUMN_VALUE(l_cursor_id, 13, v_transit_time);
1458 dbms_sql.COLUMN_VALUE(l_cursor_id, 15, v_basis);
1459 dbms_sql.COLUMN_VALUE(l_cursor_id, 16, v_owner_id);
1460 dbms_sql.COLUMN_VALUE(l_cursor_id, 17, v_carrier_id);
1461 dbms_sql.COLUMN_VALUE(l_cursor_id, 18, v_mode_code);
1462 dbms_sql.COLUMN_VALUE(l_cursor_id, 19, v_transit_time_uom);
1463 dbms_sql.COLUMN_VALUE(l_cursor_id, 20, v_special_handling);
1464 dbms_sql.COLUMN_VALUE(l_cursor_id, 21, v_addl_instr);
1465 dbms_sql.COLUMN_VALUE(l_cursor_id, 22, v_commodity_flag);
1466 dbms_sql.COLUMN_VALUE(l_cursor_id, 23, v_service_flag);
1467 dbms_sql.COLUMN_VALUE(l_cursor_id, 24, v_comm_catg_id);
1468 dbms_sql.COLUMN_VALUE(l_cursor_id, 25, v_service_code);
1469 dbms_sql.COLUMN_VALUE(l_cursor_id, 26, v_distance_uom);
1470 dbms_sql.COLUMN_VALUE(l_cursor_id, 27, v_rate_chart_id);
1471 dbms_sql.COLUMN_VALUE(l_cursor_id, 28, v_rate_chart_view_flag);
1472 dbms_sql.COLUMN_VALUE(l_cursor_id, 29, v_effective_date);
1473 dbms_sql.COLUMN_VALUE(l_cursor_id, 30, v_expiry_date);
1474 dbms_sql.COLUMN_VALUE(l_cursor_id, 31, v_origin_region_type);
1475 dbms_sql.COLUMN_VALUE(l_cursor_id, 32, v_dest_region_type);
1476 dbms_sql.COLUMN_VALUE(l_cursor_id, 33, v_comm_class_code);
1477 dbms_sql.COLUMN_VALUE(l_cursor_id, 34, v_schedules_flag_code);
1478 dbms_sql.COLUMN_VALUE(l_cursor_id, 35, v_lane_service_id);
1479 dbms_sql.COLUMN_VALUE(l_cursor_id, 36, v_tariff_name);
1480 dbms_sql.COLUMN_VALUE(l_cursor_id, 37, v_lane_type);
1481
1482 end if;
1483
1484 FOR n IN 1..l_rows_fetched LOOP
1485
1486 -- if this lane is not already there OR
1487 -- if this lane is already there, but the lane service is not already there
1488
1489 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
1490
1491 IF ((NOT l_result_lane_ids.EXISTS(v_lane_id(n + l_offset)))
1492 OR (l_result_lane_ids.EXISTS(v_lane_id(n + l_offset))
1493 AND p_source_type <> 'T' and v_lane_service_id(n + l_offset) is not null AND
1494 NOT l_result_service_ids.EXISTS(v_lane_service_id(n + l_offset)))) THEN
1495
1496
1497 l_transit_time := null; -- v_transit_time(n + l_offset); -- this is what's on the lane
1498 l_transit_time_uom := 'DAY';
1499
1500 -- obtain transit time from ATP if transit time on lane is null
1501 -- convert transit time on lane to DAYS if necessary
1502
1503 IF (p_source_type = 'R' AND
1504 p_search_criteria.pickupstop_location_id is not null AND
1505 p_search_criteria.ship_to_site_id is not null) THEN
1506
1507 IF l_debug_on THEN
1508 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Transit_Time for OM:' );
1509 END IF;
1510
1511 Get_Transit_Time(p_ship_from_loc_id => p_search_criteria.pickupstop_location_id,
1512 p_ship_to_site_id => p_search_criteria.ship_to_site_id,
1513 p_carrier_id => v_carrier_id(n + l_offset),
1514 p_service_code => v_service_code(n + l_offset),
1515 p_mode_code => v_mode_code(n + l_offset),
1516 p_from => 'OM',
1517 x_transit_time => l_transit_time,
1518 x_return_status => l_status);
1519
1520
1521 ELSE IF (p_source_type = 'R' AND p_search_criteria.pickupstop_location_id is not null ) THEN
1522
1523 IF l_debug_on THEN
1524 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Transit_Time for Trips / deliveries' );
1525 END IF;
1526
1527 Get_Transit_Time(p_ship_from_loc_id => p_search_criteria.pickupstop_location_id,
1528 p_ship_to_site_id => p_search_criteria.dropoffstop_location_id,
1529 p_carrier_id => v_carrier_id(n + l_offset),
1530 p_service_code => v_service_code(n + l_offset),
1531 p_mode_code => v_mode_code(n + l_offset),
1532 p_from => null,
1533 x_transit_time => l_transit_time,
1534 x_return_status => l_status);
1535 End IF;
1536 END IF;
1537
1538
1539 IF (l_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1540 x_return_status := l_status;
1541 FND_MESSAGE.SET_NAME('FTE', 'FTE_CANNOT_GET_TRANSIT_TIME');
1545 END IF;
1542 FND_MESSAGE.SET_TOKEN('LANE_NUMBER', v_lane_number(n + l_offset));
1543 x_return_message := FND_MESSAGE.GET;
1544 return;
1546
1547 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
1548 if (p_source_type ='T') THEN
1549
1550 l_lanes_rec := fte_lane_rec(v_lane_id(n + l_offset),
1551 '',
1552 v_origin_id(n + l_offset),
1553 v_destination_id(n + l_offset),
1554 v_carrier_id(n + l_offset),
1555 v_mode_code(n + l_offset),
1556 '',
1557 '',
1558 '',
1559 '',
1560 '',
1561 '',
1562 '',
1563 '',
1564 '',
1565 '',
1566 v_party_name(n + l_offset),
1567 v_mode(n + l_offset),
1568 '',
1569 '',
1570 '',
1571 '',
1572 '',
1573 '',
1574 '',
1575 '',
1576 '',
1577 '',
1578 '',
1579 '',
1580 '',
1581 '',
1582 v_origin_region_type(n + l_offset),
1583 '',
1584 '',
1585 '');
1586
1587 else
1588
1589 l_lanes_rec := fte_lane_rec(v_lane_id(n + l_offset),
1590 v_lane_number(n + l_offset),
1591 v_origin_id(n + l_offset),
1592 v_destination_id(n + l_offset),
1593 v_carrier_id(n + l_offset),
1594 v_mode_code(n + l_offset),
1595 v_comm_class_code(n + l_offset),
1596 v_comm_catg_id(n + l_offset),
1597 v_service_code(n + l_offset),
1598 v_schedules_flag_code(n + l_offset),
1599 v_rate_chart_id(n + l_offset),
1600 v_basis(n + l_offset),
1601 l_transit_time,
1602 l_transit_time_uom,
1603 v_distance(n + l_offset),
1604 v_distance_uom(n + l_offset),
1605 v_party_name(n + l_offset),
1606 v_mode(n + l_offset),
1607 v_commodity(n + l_offset),
1608 v_service(n + l_offset),
1609 v_schedules_flag(n + l_offset),
1610 v_port_of_loading(n + l_offset),
1611 v_port_of_discharge(n + l_offset),
1612 v_rate_chart_name(n + l_offset),
1613 v_owner_id(n + l_offset),
1614 v_special_handling(n + l_offset),
1615 v_addl_instr(n + l_offset),
1616 v_commodity_flag(n + l_offset),
1617 v_service_flag(n + l_offset),
1618 v_rate_chart_view_flag(n + l_offset),
1619 v_effective_date(n + l_offset),
1620 v_expiry_date(n + l_offset),
1621 v_origin_region_type(n + l_offset),
1622 v_dest_region_type(n + l_offset),
1623 v_tariff_name(n + l_offset),
1624 v_lane_type(n + l_offset));
1625 end if;
1626
1627 IF (l_search_criteria.vehicle_id is not null AND v_mode_code(n + l_offset) ='TRUCK') THEN
1628
1632 IF( Is_Vehicle_Available(p_vehicle_id => l_search_criteria.vehicle_id,
1629 WSH_DEBUG_SV.logmsg(l_module_name,
1630 'Check Vehicle Availability for Lane ' || v_lane_number(n + l_offset));
1631
1633 p_carrier_id => v_carrier_id(n + l_offset),
1634 p_origin_id => v_origin_id(n + l_offset),
1635 p_dest_id => v_destination_id(n + l_offset),
1636 p_mode => v_mode(n + l_offset),
1637 p_date => l_search_criteria.dep_date_from,
1638 x_return_message => x_return_message) ) THEN
1639
1640 --g_message := g_message || '; Keep ' || v_lane_id(n + l_offset);
1641 l_lanes_tab.EXTEND;
1642 l_lanes_tab(l_num_rows + 1) := l_lanes_rec;
1643 l_num_rows := l_num_rows + 1;
1644 l_result_lane_ids(v_lane_id(n + l_offset)) := v_lane_id(n + l_offset);
1645
1646 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
1647 IF (p_source_type <>'T' AND v_lane_service_id(n + l_offset) is not null) THEN
1648 l_result_service_ids(v_lane_service_id(n + l_offset)) := v_lane_service_id(n + l_offset);
1649 END IF;
1650 ELSE
1651 l_vehicles_discarded := l_vehicles_discarded || v_lane_id(n+l_offset) || ',';
1652
1653 WSH_DEBUG_SV.logmsg(l_module_name, 'Vehicle unavailable for lane ' || v_lane_number(n+l_offset));
1654 WSH_DEBUG_SV.logmsg(l_module_name, x_return_message);
1655 END IF;
1656
1657 ELSE
1658 --g_message := g_message || '; Not a TL ' || v_lane_id(n + l_offset);
1659 l_lanes_tab.EXTEND;
1660 l_lanes_tab(l_num_rows + 1) := l_lanes_rec;
1661 l_num_rows := l_num_rows + 1;
1662 l_result_lane_ids(v_lane_id(n + l_offset)) := v_lane_id(n + l_offset);
1663
1664 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
1665 IF (p_source_type <>'T' AND v_lane_service_id(n + l_offset) is not null) THEN
1666 l_result_service_ids(v_lane_service_id(n + l_offset)) := v_lane_service_id(n + l_offset);
1667 END IF;
1668
1669 END IF;
1670
1671 END IF;
1672
1673 END LOOP;
1674
1675 l_offset := l_offset + l_rows_fetched;
1676
1677 EXIT WHEN (l_rows_fetched < p_num_results OR l_lanes_tab.COUNT >= p_num_results);
1678
1679 END LOOP;
1680 dbms_sql.CLOSE_CURSOR(l_cursor_id);
1681
1682 END IF;
1683 -- call constraints API
1684
1685 ELSE -- p_search_type = 'S'
1686
1687 l_schedules_tab := fte_schedule_tab();
1688
1689 --
1690 -- Debug Statements
1691 --
1692 IF l_debug_on THEN
1693 WSH_DEBUG_SV.logmsg(l_module_name,'Calling FTE_LANE_SEARCH_QUERY_GEN.CREATE_SCHEDULE_QUERY',WSH_DEBUG_SV.C_PROC_LEVEL);
1694 END IF;
1695 --
1696 FTE_LANE_SEARCH_QUERY_GEN.Create_Schedule_Query(p_search_criteria => l_search_criteria,
1697 p_origins => l_origins,
1698 p_destinations => l_destinations,
1699 p_parent_origins => l_parent_origins,
1700 p_parent_destinations => l_parent_dests,
1701 x_query => l_query1,
1702 x_bindvars => l_bindvars1,
1703 x_return_message => l_msg,
1704 x_return_status => l_status);
1705
1706 --
1707 -- Debug Statements
1708 --
1709 IF l_debug_on THEN
1710 WSH_DEBUG_SV.logmsg(l_module_name,'schedule query: ' || l_query1,WSH_DEBUG_SV.C_PROC_LEVEL);
1711 END IF;
1712
1713 l_cursor_id := dbms_sql.OPEN_CURSOR;
1714 dbms_sql.PARSE(l_cursor_id, l_query1, DBMS_SQL.V7);
1715
1716 Bind_Vars(l_cursor_id, l_bindvars1);
1717
1718 dbms_sql.DEFINE_ARRAY(l_cursor_id, 1, s_schedule_id, p_num_results, 1);
1719 dbms_sql.DEFINE_ARRAY(l_cursor_id, 2, s_lane_id, p_num_results, 1);
1720 dbms_sql.DEFINE_ARRAY(l_cursor_id, 3, s_lane_number, p_num_results, 1);
1721 dbms_sql.DEFINE_ARRAY(l_cursor_id, 4, s_dep_date, p_num_results, 1);
1722 dbms_sql.DEFINE_ARRAY(l_cursor_id, 5, s_arr_date, p_num_results, 1);
1723 dbms_sql.DEFINE_ARRAY(l_cursor_id, 6, s_transit_time, p_num_results, 1);
1724 dbms_sql.DEFINE_ARRAY(l_cursor_id, 7, s_frequency, p_num_results, 1);
1725 dbms_sql.DEFINE_ARRAY(l_cursor_id, 8, s_effective_date, p_num_results, 1);
1729 dbms_sql.DEFINE_ARRAY(l_cursor_id, 12, s_arr_date_indicator, p_num_results, 1);
1726 dbms_sql.DEFINE_ARRAY(l_cursor_id, 9, s_port_of_loading, p_num_results, 1);
1727 dbms_sql.DEFINE_ARRAY(l_cursor_id, 10, s_port_of_discharge, p_num_results, 1);
1728 dbms_sql.DEFINE_ARRAY(l_cursor_id, 11, s_expiry_date, p_num_results, 1);
1730 dbms_sql.DEFINE_ARRAY(l_cursor_id, 13, s_frequency_arrival, p_num_results, 1);
1731 dbms_sql.DEFINE_ARRAY(l_cursor_id, 14, s_origin_id, p_num_results, 1);
1732 dbms_sql.DEFINE_ARRAY(l_cursor_id, 15, s_destination_id, p_num_results, 1);
1733 dbms_sql.DEFINE_ARRAY(l_cursor_id, 16, s_mode, p_num_results, 1);
1734 dbms_sql.DEFINE_ARRAY(l_cursor_id, 17, s_carrier_id, p_num_results, 1);
1735 dbms_sql.DEFINE_ARRAY(l_cursor_id, 18, s_carrier_name, p_num_results, 1);
1736 dbms_sql.DEFINE_ARRAY(l_cursor_id, 19, s_dep_time, p_num_results, 1);
1737 dbms_sql.DEFINE_ARRAY(l_cursor_id, 20, s_arr_time, p_num_results, 1);
1738 dbms_sql.DEFINE_ARRAY(l_cursor_id, 21, s_frequency_type, p_num_results, 1); -- meaning
1739 dbms_sql.DEFINE_ARRAY(l_cursor_id, 22, s_transit_time_uom, p_num_results, 1);
1740 dbms_sql.DEFINE_ARRAY(l_cursor_id, 23, s_vessel_type, p_num_results, 1);
1741 dbms_sql.DEFINE_ARRAY(l_cursor_id, 24, s_vessel_name, p_num_results, 1);
1742 dbms_sql.DEFINE_ARRAY(l_cursor_id, 25, s_voyage_number, p_num_results, 1);
1743 dbms_sql.DEFINE_ARRAY(l_cursor_id, 26, s_arr_time_w_ind, p_num_results, 1);
1744 dbms_sql.DEFINE_ARRAY(l_cursor_id, 27, s_mode_code, p_num_results, 1);
1745 dbms_sql.DEFINE_ARRAY(l_cursor_id, 28, s_service_code, p_num_results, 1);
1746 dbms_sql.DEFINE_ARRAY(l_cursor_id, 29, s_service, p_num_results, 1);
1747 dbms_sql.DEFINE_ARRAY(l_cursor_id, 30, s_frequency_type_code, p_num_results, 1);
1748 dbms_sql.DEFINE_ARRAY(l_cursor_id, 31, s_active_flag, p_num_results, 1);
1749
1750 l_rows_affected := dbms_sql.EXECUTE(l_cursor_id);
1751
1752 LOOP
1753 l_rows_fetched := dbms_sql.FETCH_ROWS(l_cursor_id);
1754 IF (l_rows_fetched = 0) THEN
1755 EXIT;
1756 END IF;
1757
1758 dbms_sql.COLUMN_VALUE(l_cursor_id, 1, s_schedule_id);
1759 dbms_sql.COLUMN_VALUE(l_cursor_id, 2, s_lane_id);
1760 dbms_sql.COLUMN_VALUE(l_cursor_id, 3, s_lane_number);
1761 dbms_sql.COLUMN_VALUE(l_cursor_id, 4, s_dep_date);
1762 dbms_sql.COLUMN_VALUE(l_cursor_id, 5, s_arr_date);
1763 dbms_sql.COLUMN_VALUE(l_cursor_id, 6, s_transit_time);
1764 dbms_sql.COLUMN_VALUE(l_cursor_id, 7, s_frequency);
1765 dbms_sql.COLUMN_VALUE(l_cursor_id, 8, s_effective_date);
1766 dbms_sql.COLUMN_VALUE(l_cursor_id, 9, s_port_of_loading);
1767 dbms_sql.COLUMN_VALUE(l_cursor_id, 10, s_port_of_discharge);
1768 dbms_sql.COLUMN_VALUE(l_cursor_id, 11, s_expiry_date);
1769 dbms_sql.COLUMN_VALUE(l_cursor_id, 12, s_arr_date_indicator);
1770 dbms_sql.COLUMN_VALUE(l_cursor_id, 13, s_frequency_arrival);
1771 dbms_sql.COLUMN_VALUE(l_cursor_id, 14, s_origin_id);
1772 dbms_sql.COLUMN_VALUE(l_cursor_id, 15, s_destination_id);
1773 dbms_sql.COLUMN_VALUE(l_cursor_id, 16, s_mode);
1774 dbms_sql.COLUMN_VALUE(l_cursor_id, 17, s_carrier_id);
1775 dbms_sql.COLUMN_VALUE(l_cursor_id, 18, s_carrier_name);
1776 dbms_sql.COLUMN_VALUE(l_cursor_id, 19, s_dep_time);
1777 dbms_sql.COLUMN_VALUE(l_cursor_id, 20, s_arr_time);
1778 dbms_sql.COLUMN_VALUE(l_cursor_id, 21, s_frequency_type);
1779 dbms_sql.COLUMN_VALUE(l_cursor_id, 22, s_transit_time_uom);
1780 dbms_sql.COLUMN_VALUE(l_cursor_id, 23, s_vessel_type);
1781 dbms_sql.COLUMN_VALUE(l_cursor_id, 24, s_vessel_name);
1782 dbms_sql.COLUMN_VALUE(l_cursor_id, 25, s_voyage_number);
1783 dbms_sql.COLUMN_VALUE(l_cursor_id, 26, s_arr_time_w_ind);
1784 dbms_sql.COLUMN_VALUE(l_cursor_id, 27, s_mode_code);
1785 dbms_sql.COLUMN_VALUE(l_cursor_id, 28, s_service_code);
1786 dbms_sql.COLUMN_VALUE(l_cursor_id, 29, s_service);
1787 dbms_sql.COLUMN_VALUE(l_cursor_id, 30, s_frequency_type_code);
1788 dbms_sql.COLUMN_VALUE(l_cursor_id, 31, s_active_flag);
1789
1790 FOR n IN 1..l_rows_fetched LOOP
1791 l_schedules_rec := fte_schedule_rec(s_schedule_id(n + l_offset),
1792 s_lane_id(n + l_offset),
1793 s_lane_number(n + l_offset),
1794 s_dep_date(n + l_offset),
1795 s_arr_date(n + l_offset),
1796 s_dep_time(n + l_offset),
1797 s_arr_time(n + l_offset),
1798 s_arr_time_w_ind(n + l_offset),
1799 s_frequency_type_code(n + l_offset),
1800 s_frequency_type(n + l_offset),
1801 s_frequency(n + l_offset),
1802 s_arr_date_indicator(n + l_offset),
1803 s_frequency_arrival(n + l_offset),
1804 s_transit_time(n + l_offset),
1808 s_port_of_loading(n + l_offset),
1805 s_transit_time_uom(n + l_offset),
1806 s_effective_date(n + l_offset),
1807 s_expiry_date(n + l_offset),
1809 s_port_of_discharge(n + l_offset),
1810 s_origin_id(n + l_offset),
1811 s_destination_id(n + l_offset),
1812 s_mode(n + l_offset),
1813 s_carrier_id(n + l_offset),
1814 s_carrier_name(n + l_offset),
1815 s_vessel_type(n + l_offset),
1816 s_vessel_name(n + l_offset),
1817 s_voyage_number(n + l_offset),
1818 s_mode_code(n + l_offset),
1819 s_service_code(n + l_offset),
1820 s_service(n + l_offset),
1821 s_active_flag(n + l_offset)
1822 );
1823
1824 l_schedules_tab.EXTEND;
1825 l_schedules_tab(l_num_rows + 1) := l_schedules_rec;
1826 l_num_rows := l_num_rows + 1;
1827 END LOOP;
1828
1829 l_offset := l_offset + l_rows_fetched;
1830
1831 -- have enough rows, exit loop
1832 IF (l_num_rows >= p_num_results OR l_rows_fetched < p_num_results) THEN
1833 EXIT;
1834 END IF;
1835
1836 END LOOP;
1837
1838 dbms_sql.CLOSE_CURSOR(l_cursor_id);
1839
1840 END IF; -- end if p_search_type = 'L'
1841
1842 --
1843 -- Debug Statements
1844 --
1845 IF (l_debug_on AND p_search_type = 'L') THEN
1846 WSH_DEBUG_SV.logmsg(l_module_name,'lanes obtained = ' || l_lanes_tab.COUNT,WSH_DEBUG_SV.C_PROC_LEVEL);
1847 WSH_DEBUG_SV.logmsg(l_module_name,'lane ids returned: ',WSH_DEBUG_SV.C_PROC_LEVEL);
1848
1849 IF (l_lanes_tab.count > 0) THEN
1850 FOR k IN l_lanes_tab.FIRST..l_lanes_tab.LAST LOOP
1851 WSH_DEBUG_SV.logmsg(l_module_name,l_lanes_tab(k).lane_id,WSH_DEBUG_SV.C_PROC_LEVEL);
1852 WSH_DEBUG_SV.logmsg(l_module_name,l_lanes_tab(k).tariff_name,WSH_DEBUG_SV.C_PROC_LEVEL);
1853 END LOOP;
1854 END IF;
1855 END IF;
1856
1857 -- check constraints if delivery leg search
1858 IF (p_search_type = 'L' AND l_lanes_tab.COUNT > 0 AND
1859 (p_search_criteria.delivery_id is not null OR
1860 p_search_criteria.delivery_leg_id is not null)) THEN
1861
1862 SELECT shipping_control INTO l_shipping_control
1863 FROM wsh_new_deliveries
1864 WHERE delivery_id = p_search_criteria.delivery_id;
1865
1866 -- only perform constraints checking if supplier does not manage freight for delivery
1867 IF (l_shipping_control is null OR l_shipping_control <> 'SUPPLIER') THEN
1868
1869 --
1870 -- Debug Statements
1871 --
1872 IF l_debug_on THEN
1873 WSH_DEBUG_SV.logmsg(l_module_name,'Delivery Leg Lane Search. Performing constraints checking',WSH_DEBUG_SV.C_PROC_LEVEL);
1874 END IF;
1875 --
1876
1877 -- setup delivery record for constraints check
1878 l_dleg_info_rec.delivery_leg_id := p_search_criteria.delivery_leg_id;
1879 l_dleg_info_rec.exists_in_database := p_search_criteria.exists_in_database;
1880 l_dleg_info_rec.delivery_id := p_search_criteria.delivery_id;
1881 l_dleg_info_rec.sequence_number := p_search_criteria.sequence_number;
1882 l_dleg_info_rec.pick_up_stop_id := p_search_criteria.pick_up_stop_id;
1883 l_dleg_info_rec.drop_off_stop_id := p_search_criteria.drop_off_stop_id;
1884 l_dleg_info_rec.pickupstop_location_id := p_search_criteria.pickupstop_location_id;
1885 l_dleg_info_rec.dropoffstop_location_id := p_search_criteria.dropoffstop_location_id;
1886
1887 -- setup lanes table of record for constraints check
1888 FOR j IN l_lanes_tab.FIRST..l_lanes_tab.LAST LOOP
1889
1890 l_lane_info_rec.lane_id := l_lanes_tab(j).lane_id;
1891 l_lane_info_rec.lane_number := l_lanes_tab(j).lane_number;
1892 l_lane_info_rec.owner_id := l_lanes_tab(j).owner_id;
1893 l_lane_info_rec.carrier_id := l_lanes_tab(j).carrier_id;
1894 l_lane_info_rec.origin_id := l_lanes_tab(j).origin_id;
1895 l_lane_info_rec.destination_id := l_lanes_tab(j).destination_id;
1896 l_lane_info_rec.mode_of_transportation_code := l_lanes_tab(j).mode_of_transport_code;
1897
1898 l_lane_info_tab(j) := l_lane_info_rec;
1899
1900 END LOOP;
1901
1902 -- call constraints API
1903 WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_dleg(
1904 p_init_msg_list => fnd_api.g_true,
1905 p_action_code => WSH_FTE_CONSTRAINT_FRAMEWORK.G_DLEG_LANE_SEARCH,
1909 p_target_lane => l_lane_info_tab,
1906 p_exception_list => l_dummy_exception,
1907 p_delivery_leg_rec => l_dleg_info_rec,
1908 p_target_trip => l_dummy_trips,
1910 x_succ_trips => l_success_trips,
1911 x_succ_lanes => l_success_lanes,
1912 x_validate_result => l_validate_result,
1913 x_msg_count => l_msg_count,
1914 x_msg_data => l_msg_data,
1915 x_return_status => l_status);
1916
1917 --
1918 -- Debug Statements
1919 --
1920 IF l_debug_on THEN
1921 WSH_DEBUG_SV.logmsg(l_module_name,'After calling WSH_FTE_COMP_CONSTRAINT_ENGINE.validate_constraint_dleg. Messages:',WSH_DEBUG_SV.C_PROC_LEVEL);
1922 IF (l_msg_count > 1) THEN
1923 FOR m IN 1..l_msg_count LOOP
1924 l_msg_data := FND_MSG_PUB.GET(m, 'T');
1925 WSH_DEBUG_SV.logmsg(l_module_name,l_msg_data,WSH_DEBUG_SV.C_PROC_LEVEL);
1926 END LOOP;
1927 ELSE
1928 WSH_DEBUG_SV.logmsg(l_module_name,l_msg_data,WSH_DEBUG_SV.C_PROC_LEVEL);
1929 END IF;
1930
1931 END IF;
1932 --
1933
1934 --
1935 -- Debug Statements
1936 --
1937 IF l_debug_on THEN
1938 WSH_DEBUG_SV.logmsg(l_module_name,'validate result = ' || l_validate_result,WSH_DEBUG_SV.C_PROC_LEVEL);
1939 WSH_DEBUG_SV.logmsg(l_module_name,'lane ids returned by constraints ' || l_success_lanes.COUNT,WSH_DEBUG_SV.C_PROC_LEVEL);
1940 END IF;
1941
1942 -- loop thru l_lanes_tab and for each lane_id, check to see if in l_success_lanes
1943 -- if not in there, then delete from l_lanes_tab
1944 IF (l_validate_result = 'F') THEN
1945 FOR k IN l_lanes_tab.FIRST..l_lanes_tab.LAST LOOP
1946 l_good_lane := 'N';
1947 l_cc_lane_counter := l_success_lanes.FIRST;
1948 WHILE l_cc_lane_counter <= l_success_lanes.LAST LOOP
1949 IF (l_lanes_tab(k).lane_id = l_success_lanes(l_cc_lane_counter)) THEN
1950 l_good_lane := 'Y';
1951 GOTO end_lane_loop;
1952 END IF;
1953 l_cc_lane_counter := l_success_lanes.NEXT(l_cc_lane_counter);
1954 END LOOP;
1955
1956 <<end_lane_loop>>
1957 NULL;
1958
1959 IF (l_good_lane <> 'Y') THEN
1960 --
1961 -- Debug Statements
1962 --
1963 IF l_debug_on THEN
1964 WSH_DEBUG_SV.logmsg(l_module_name,'removing lane ' || l_lanes_tab(k).lane_id,WSH_DEBUG_SV.C_PROC_LEVEL);
1965 END IF;
1966
1967 l_lanes_tab.DELETE(k);
1968
1969 END IF;
1970 END LOOP;
1971
1972 END IF; -- end l_validate_result = 'F'
1973
1974 ELSE
1975
1976 --
1977 -- Debug Statements
1978 --
1979 IF l_debug_on THEN
1980 WSH_DEBUG_SV.logmsg(l_module_name,'Supplier manages freight. Skipping constraints checking.', WSH_DEBUG_SV.C_PROC_LEVEL);
1981 END IF;
1982 --
1983
1984 END IF; -- end if <> supplier managing freight
1985
1986 END IF; -- end constraints checking
1987
1988 x_lane_results := l_lanes_tab;
1989 x_schedule_results := l_schedules_tab;
1990 IF (l_vehicles_discarded IS NOT NULL) THEN
1991 x_return_message := x_return_message || 'The ff. lanes were discarded because vehicles are unavailable: ' ||
1992 l_vehicles_discarded;
1993 END IF;
1994
1995 x_return_message := x_return_message || g_message;
1996
1997 g_message := null;
1998 --
1999 -- Debug Statements
2000 --
2001 IF l_debug_on THEN
2002 WSH_DEBUG_SV.pop(l_module_name);
2003 END IF;
2004 --
2005 EXCEPTION
2006 WHEN OTHERS THEN
2007 x_return_message := g_message || 'Unexpected Error in Search_Lanes Package: ' || sqlerrm;
2008 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2009 END Search_Lanes;
2010
2011
2012 -- ----------------------------------------------------------------
2013 -- Name: Get_Rate_Chart_Ids
2014 -- Type: Procedure
2015 --
2016 -- Description: This procedure returns a SQL table of rate
2017 -- chart ids that match the regions and carrier
2018 -- information.
2019 -- Called by Rate Chart Search to prevent
2020 -- duplication in the relax up regions query.
2021 --
2022 -- -----------------------------------------------------------------
2023 PROCEDURE Get_Rate_Chart_Ids(p_search_criteria IN fte_search_criteria_rec,
2027
2024 p_num_results IN NUMBER,
2025 x_rate_chart_ids OUT NOCOPY STRINGARRAY,
2026 x_return_status OUT NOCOPY VARCHAR2) IS
2028 l_origin_zip VARCHAR2(30) := null;
2029 l_destination_zip VARCHAR2(30) := null;
2030 l_carrier_name VARCHAR2(30) := null;
2031 l_tariff_name VARCHAR2(80) := null;
2032 l_origins FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab;
2033 l_destinations FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab;
2034 l_parent_origins FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab;
2035 l_parent_dests FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab;
2036
2037 l_query VARCHAR2(5000);
2038 l_bindvars FTE_LANE_SEARCH_QUERY_GEN.bindvars;
2039 l_cursor_id NUMBER;
2040 l_counter NUMBER;
2041 l_rows_affected NUMBER;
2042 l_rows_fetched NUMBER := 0;
2043 l_num_rows NUMBER := 0;
2044 l_rate_chart_tab STRINGARRAY;
2045
2046 v_rate_chart_id dbms_sql.VARCHAR2_TABLE;
2047
2048 --
2049 l_debug_on BOOLEAN;
2050 --
2051 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_RATE_CHART_IDS';
2052 --
2053
2054 BEGIN
2055 --
2056 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2057 --
2058 IF l_debug_on IS NULL
2059 THEN
2060 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2061 END IF;
2062 --
2063 --
2064 -- Debug Statements
2065 --
2066 IF l_debug_on THEN
2067 WSH_DEBUG_SV.push(l_module_name);
2068 --
2069 END IF;
2070 --
2071
2072 l_rate_chart_tab := STRINGARRAY();
2073
2074 -- get carrier name
2075 l_carrier_name := p_search_criteria.carrier_name;
2076 l_tariff_name := p_search_criteria.tariff_name;
2077 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.tariff_name', p_search_criteria.tariff_name);
2078 -- get origin and parents set up
2079 l_origin_zip := p_search_criteria.origin_zip;
2080 Set_Up_Regions(p_search_criteria.origin_loc_id,
2081 p_search_criteria.origin_country,
2082 p_search_criteria.origin_state,
2083 p_search_criteria.origin_city,
2084 l_origin_zip,
2085 l_origins,
2086 l_parent_origins);
2087
2088 -- get destination and parents set up
2089 l_destination_zip := p_search_criteria.destination_zip;
2090 Set_Up_Regions(p_search_criteria.destination_loc_id,
2091 p_search_criteria.destination_country,
2092 p_search_criteria.destination_state,
2093 p_search_criteria.destination_city,
2094 l_destination_zip,
2095 l_destinations,
2096 l_parent_dests);
2097
2098
2099 IF (( l_parent_dests IS NULL OR l_parent_dests.COUNT <= 0 OR
2100 l_parent_origins IS NULL OR l_parent_origins.COUNT <= 0) AND
2101 (l_tariff_name is NULL ))
2102 THEN
2103 x_rate_chart_ids := l_rate_chart_tab;
2104 RETURN;
2105 END IF;
2106 FTE_LANE_SEARCH_QUERY_GEN.Create_Rate_Chart_Query(
2107 p_parent_origins =>l_parent_origins,
2108 p_parent_destinations =>l_parent_dests,
2109 p_origin_zip_request =>l_origin_zip,
2110 p_dest_zip_request =>l_destination_zip,
2111 p_carrier_name =>l_carrier_name,
2112 p_tariff_name => l_tariff_name,
2113 x_query => l_query,
2114 x_bindvars => l_bindvars);
2115
2116
2117 --
2118 -- Debug Statements
2119 --
2120 IF l_debug_on THEN
2121 WSH_DEBUG_SV.logmsg(l_module_name,'rate chart query:' || l_query,WSH_DEBUG_SV.C_PROC_LEVEL);
2122 END IF;
2123
2124
2125 l_cursor_id := dbms_sql.OPEN_CURSOR;
2126 dbms_sql.PARSE(l_cursor_id, l_query, DBMS_SQL.V7);
2127
2128 Bind_Vars(l_cursor_id, l_bindvars);
2129
2130 dbms_sql.DEFINE_ARRAY(l_cursor_id, 1, v_rate_chart_id, p_num_results, 1);
2131
2132 l_rows_affected := dbms_sql.EXECUTE(l_cursor_id);
2133 LOOP
2134 l_rows_fetched := dbms_sql.FETCH_ROWS(l_cursor_id);
2135 IF (l_rows_fetched = 0) THEN
2136 EXIT;
2137 END IF;
2138 dbms_sql.COLUMN_VALUE(l_cursor_id, 1, v_rate_chart_id);
2139
2140 FOR n IN 1..l_rows_fetched LOOP
2141 l_rate_chart_tab.EXTEND;
2142 l_rate_chart_tab(l_num_rows + 1) := v_rate_chart_id(n);
2143 l_num_rows := l_num_rows + 1;
2144 END LOOP;
2145
2146 -- have enough rows, exit loop
2147 IF (l_num_rows >= p_num_results OR l_rows_fetched < p_num_results) THEN
2148 EXIT;
2149 END IF;
2150
2151 END LOOP;
2152 dbms_sql.CLOSE_CURSOR(l_cursor_id);
2153 x_rate_chart_ids := l_rate_chart_tab;
2154
2155 --
2156 -- Debug Statements
2157 --
2158 IF l_debug_on THEN
2159 WSH_DEBUG_SV.pop(l_module_name);
2160 END IF;
2161 --
2162 END Get_Rate_Chart_Ids;
2163
2164 END FTE_LANE_SEARCH;