[Home] [Help]
PACKAGE BODY: APPS.FTE_LANE_SEARCH_QUERY_GEN
Source
4 -- global variables
1 PACKAGE BODY FTE_LANE_SEARCH_QUERY_GEN AS
2 /* $Header: FTELNQYB.pls 120.4 2006/01/23 22:44:22 pkaliyam ship $ */
3
5 g_type1 CONSTANT VARCHAR2(1) := '1';
6 g_type2 CONSTANT VARCHAR2(1) := '2';
7 g_type3 CONSTANT VARCHAR2(1) := '3'; -- common
8 g_type4 CONSTANT VARCHAR2(1) := '4'; -- order by for first query
9
10 g_bind_counter1 NUMBER; -- relax up
11 g_bind_counter2 NUMBER; -- relax down
12 g_bind_counter_common NUMBER;
13 g_bind_counter_orderby NUMBER;
14
15 g_bind_counter_global NUMBER;
16
17 --
18 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_LANE_SEARCH_QUERY_GEN';
19 --
20
21 -- ----------------------------------------------------------------
22 -- Name: Process_Bind_Var
23 -- Type: Procedure
24 --
25 -- Description: This procedure takes in bind variables and the
26 -- type of bind variable and puts it in the
27 -- appropriate bind variable array, increments
28 -- the corresponding bind variable counter
29 --
30 -- -----------------------------------------------------------------
31 PROCEDURE Process_Bind_Var(p_bindvars IN OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars,
32 p_bindvar IN VARCHAR2,
33 p_bindtype IN VARCHAR2,
34 p_bind_counter_type IN VARCHAR2) IS
35
36 l_bindvar_type FTE_LANE_SEARCH_QUERY_GEN.bindvar_type;
37
38 --
39 l_debug_on BOOLEAN;
40 --
41 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_BIND_VAR';
42 --
43 BEGIN
44
45 --
46 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
47 --
48 IF l_debug_on IS NULL
49 THEN
50 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
51 END IF;
52 --
53 --
54 -- Debug Statements
55 --
56 IF l_debug_on THEN
57 WSH_DEBUG_SV.push(l_module_name);
58 --
59 WSH_DEBUG_SV.log(l_module_name,'P_BINDVAR',P_BINDVAR);
60 WSH_DEBUG_SV.log(l_module_name,'P_BINDTYPE',P_BINDTYPE);
61 WSH_DEBUG_SV.log(l_module_name,'P_BIND_COUNTER_TYPE',P_BIND_COUNTER_TYPE);
62 END IF;
63 --
64 l_bindvar_type.bindvar := p_bindvar;
65 l_bindvar_type.bindtype := p_bindtype;
66 l_bindvar_type.bindvarindex := g_bind_counter_global;
67
68 IF (p_bind_counter_type = g_type1) THEN
69 p_bindvars(g_bind_counter1) := l_bindvar_type;
70 g_bind_counter1 := g_bind_counter1 + 1;
71 ELSIF (p_bind_counter_type = g_type2) THEN
72 p_bindvars(g_bind_counter2) := l_bindvar_type;
73 g_bind_counter2 := g_bind_counter2 + 1;
74 ELSIF (p_bind_counter_type = g_type3) THEN
75 p_bindvars(g_bind_counter_common) := l_bindvar_type;
76 g_bind_counter_common := g_bind_counter_common + 1;
77 ELSE
78 p_bindvars(g_bind_counter_orderby) := l_bindvar_type;
79 g_bind_counter_orderby := g_bind_counter_orderby + 1;
80 END IF;
81
82 g_bind_counter_global := g_bind_counter_global + 1;
83
84 --
85 -- Debug Statements
86 --
87 IF l_debug_on THEN
88 WSH_DEBUG_SV.pop(l_module_name);
89 END IF;
90 --
91 END Process_Bind_Var;
92
93 -- -----------------------------------------------------------
94 -- forward declaration for procedure Create_Regions_Clause
95 -- -----------------------------------------------------------
96 PROCEDURE Create_Regions_Clause(p_parent_origins IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
97 p_parent_destinations IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
98 p_origin_zip_request IN VARCHAR2,
99 p_dest_zip_request IN VARCHAR2,
100 x_query OUT NOCOPY VARCHAR2,
101 x_bindvars IN OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars);
102
103 -- ----------------------------------------------------------------
104 -- Name: Create_Lane_Query
105 -- Type: Procedure
106 --
107 -- Description: This procedure takes in search criteria and
108 -- origin, destination ids, and creates the
109 -- dynamic sql statement for searching lanes.
110 -- Returns the relax up and relax down queries,
111 -- and also returns the bind variable arrays.
112 --
113 -- -----------------------------------------------------------------
114 PROCEDURE Create_Lane_Query(p_search_criteria IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_criteria_rec,
115 p_origins IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
116 p_destinations IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
117 p_parent_origins IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab, -- includes origins
118 p_parent_destinations IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab, -- includes dests
119 p_source_type IN VARCHAR2,
123 x_bindvars2 OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars,
120 x_query1 OUT NOCOPY VARCHAR2,
121 x_query2 OUT NOCOPY VARCHAR2,
122 x_bindvars1 OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars,
124 x_bindvars_common OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars,
125 x_bindvars_orderby OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars,
126 x_return_message OUT NOCOPY VARCHAR2,
127 x_return_status OUT NOCOPY VARCHAR2) IS
128
129 -- declare local variables
130 l_relax_flag VARCHAR2(1);
131 l_origins FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab;
132 l_destinations FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab;
133 l_parent_origins FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab;
134 l_parent_destinations FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab;
135 l_origin_zip_request VARCHAR2(30);
136 l_dest_zip_request VARCHAR2(30);
137 l_mode VARCHAR2(30);
138 l_lane_number VARCHAR2(30);
139 l_carrier_id NUMBER;
140 l_carrier_name VARCHAR2(360);
141 l_commodity_catg_id NUMBER;
142 l_commodity VARCHAR2(240);
143 l_service_code VARCHAR2(30);
144 l_service VARCHAR2(80);
145 --l_equipment_code VARCHAR2(30);
146 -- l_equipment VARCHAR2(80);
147 l_schedule_only_flag VARCHAR2(1);
148 l_lane_ids_list VARCHAR2(2000);
149 l_lane_eff_date VARCHAR2(30);
150 l_date_clause VARCHAR2(1300);
151 l_tariff_name VARCHAR2(80);
152 l_lane_number_search VARCHAR2(1) := 'N';
153 l_lane_id_search VARCHAR2(1) := 'N';
154 l_counter NUMBER;
155
156 -- local variables to hold query
157 l_query_select VARCHAR2(2000);
158 l_query_from VARCHAR2(1000);
159 l_query_from_regions VARCHAR2(2000);
160 l_query_from_lane_num VARCHAR2(2000);
161 l_query_common_join VARCHAR2(2000);
162 l_query_common_criteria VARCHAR2(4000);
163 l_query_schedule VARCHAR2(8000);
164 l_query_relax_up VARCHAR2(2000);
165 l_query_relax_down VARCHAR2(2000);
166 l_query_order_by VARCHAR2(2000);
167 l_query1 VARCHAR2(32000);
168 l_query2 VARCHAR2(32000);
169
170
171
172 l_bindvars1 FTE_LANE_SEARCH_QUERY_GEN.bindvars;
173 l_bindvars2 FTE_LANE_SEARCH_QUERY_GEN.bindvars;
174 l_bindvars_common FTE_LANE_SEARCH_QUERY_GEN.bindvars;
175 l_bindvars_orderby FTE_LANE_SEARCH_QUERY_GEN.bindvars;
176 l_bindvars_schedule FTE_LANE_SEARCH_QUERY_GEN.bindvars;
177 l_bindvar_type FTE_LANE_SEARCH_QUERY_GEN.bindvar_type;
178
179 l_message VARCHAR2(200);
180 l_status VARCHAR2(1);
181 x_status NUMBER;
182
183 -- cursor definitions
184 CURSOR c_get_carrier_id(p_carrier_name VARCHAR2) IS
185 select party_id
186 from hz_parties, wsh_carriers
187 where party_name = p_carrier_name and party_id = carrier_id;
188
189 CURSOR c_get_lookup_code(p_lookup_type VARCHAR2, p_meaning VARCHAR2) IS
190 select lookup_code from fnd_lookup_values_vl
191 where lookup_type = p_lookup_type
192 and upper(meaning) like upper(p_meaning)||'%';
193
194 --
195 l_debug_on BOOLEAN;
196 --
197 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_LANE_QUERY';
198 --
199
200 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
201 l_query_group_by VARCHAR2(2000);
202
203
204 BEGIN
205
206 --
207 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
208 --
209 IF l_debug_on IS NULL THEN
210 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
211 END IF;
212 --
213 --
214 -- Debug Statements
215 --
216 IF l_debug_on THEN
217 WSH_DEBUG_SV.push(l_module_name);
218 END IF;
219 --
220 g_bind_counter1 := 1;
221 g_bind_counter2 := 1;
222 g_bind_counter_common := 1;
223 g_bind_counter_orderby := 1;
224
225 g_bind_counter_global := 1;
226
227 -- obtain serach criteria variables
228 l_relax_flag := p_search_criteria.relax_flag;
229 l_origins := p_origins;
230 l_destinations := p_destinations;
231 l_parent_origins := p_parent_origins; -- includes origins
232 l_parent_destinations := p_parent_destinations; -- includes dests
233 l_origin_zip_request := p_search_criteria.origin_zip_request;
234 l_dest_zip_request := p_search_criteria.dest_zip_request;
235 l_mode := p_search_criteria.mode_of_transport;
236 l_lane_number := p_search_criteria.lane_number;
237 l_carrier_id := p_search_criteria.carrier_id;
238 l_carrier_name := p_search_criteria.carrier_name;
239 l_commodity_catg_id := p_search_criteria.commodity_catg_id;
240 l_commodity := p_search_criteria.commodity;
241 l_service_code := p_search_criteria.service_code;
242 l_service := p_search_criteria.service;
243 -- l_equipment_code := p_search_criteria.equipment_code;
244 -- l_equipment := p_search_criteria.equipment;
248 WSH_DEBUG_SV.log(l_module_name,'p_search_criteria.tariff_name',p_search_criteria.tariff_name);
245 l_schedule_only_flag := p_search_criteria.schedule_only_flag;
246 l_lane_ids_list := p_search_criteria.lane_ids_list;
247 l_tariff_name := p_search_criteria.tariff_name;
249 WSH_DEBUG_SV.log(l_module_name,'l_tariff_name',l_tariff_name);
250
251 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
252 IF (p_source_type ='T') THEN
253
254 l_query_select := 'select wc.carrier_name, l.carrier_id, l.origin_id, l.destination_id, lv2.meaning, l.mode_of_transportation_code, r.region_type, min(l.lane_id) lane_id ';
255
256 l_query_from_regions :=' from fte_lanes l, wsh_carriers_v wc, wsh_zone_regions zr,' ||
257 ' wsh_zone_regions pd, fnd_lookup_values_vl lv2, wsh_regions r';
258
259 l_query_common_join := ' where l.carrier_id = wc.carrier_id and' ||
260 ' wc.active = ''A'' AND' ||
261 ' lv2.lookup_type = ''WSH_MODE_OF_TRANSPORT'' AND' ||
262 ' lv2.lookup_code = l.mode_of_transportation_code AND' ||
263 ' l.origin_id = r.region_id AND' ||
264 ' nvl(l.editable_flag, ''Y'') <> ''D'' AND' || -- only for non-edit
265 ' nvl(l.effective_date, sysdate) <= sysdate AND' || -- only for non-edit
266 ' nvl(l.expiry_date, sysdate) >= sysdate '; -- only for non-edit
267 ELSE
268
269 -- select statement
270 l_query_select := 'select l.lane_id, l.lane_number, wc.carrier_name, ' ||
271 'l.origin_id, l.destination_id, lv2.meaning, ' ||
272 'lv3.meaning ,' ||
273 'decode(l.commodity_detail_flag, ''Y'', ' ||
274 'decode(l.commodity_catg_id, null, ''multiple'', m.description), null), ' ||
275 'lv1.meaning, l.distance, l.port_of_loading, ' ||
276 'l.port_of_discharge, l.transit_time, lh.name, ' ||
277 'l.basis, l.owner_id, l.carrier_id, l.mode_of_transportation_code, ' ||
278 'l.transit_time_uom, l.special_handling, l.additional_instructions, ' ||
279 'l.commodity_detail_flag,' ||
280 'l.service_detail_flag, ' ||
281 'l.commodity_catg_id,'||
282 ' ls.service_code, ' ||
283 'l.distance_uom, l.pricelist_id, l.pricelist_view_flag, l.effective_date, ' ||
284 'l.expiry_date, ro.region_type, rd.region_type, l.comm_fc_class_code, ' ||
285 'l.schedules_flag, ls.lane_service_id, l.tariff_name, l.lane_type';
286
287 -- from clauses
288 l_query_from_regions := ' from fte_lanes l, wsh_carriers_v wc, wsh_zone_regions zr,' ||
289 ' wsh_zone_regions pd, fnd_lookup_values_vl lv1,' ||
290 ' qp_list_headers_tl lh, fnd_lookup_values_vl lv2,' ||
291 ' fnd_lookup_values_vl lv3, mtl_categories m,' ||
292 ' wsh_regions ro, wsh_regions rd,' ||
293 ' fte_lane_services ls ';
294
295 l_query_from_lane_num := ' from fte_lanes l, wsh_carriers_v wc, fnd_lookup_values_vl lv1,' ||
296 ' qp_list_headers_tl lh, fnd_lookup_values_vl lv2,' ||
297 ' fnd_lookup_values_vl lv3, mtl_categories m,' ||
298 ' wsh_regions ro, wsh_regions rd, ' ||
299 ' fte_lane_services ls ';
300
301 -- common join clauses
302 l_query_common_join := ' where l.carrier_id = wc.carrier_id and' ||
303 ' wc.active = ''A'' AND' ||
304 ' l.schedules_flag = lv1.lookup_code AND' ||
305 ' lv1.lookup_type = ''FTE_YES_NO'' AND' ||
306 ' lv1.view_application_id = 716 AND' ||
307 ' lh.list_header_id (+) = l.pricelist_id AND' ||
308 ' lh.language(+) = userenv(''LANG'') AND' ||
309 ' lv2.lookup_type = ''WSH_MODE_OF_TRANSPORT'' AND' ||
310 ' lv2.lookup_code = l.mode_of_transportation_code AND' ||
311 ' lv3.lookup_type = ''WSH_SERVICE_LEVELS'' AND' ||
312 ' lv3.lookup_code = ls.service_code AND' ||
313 ' lv3.enabled_flag = ''Y'' AND' ||
314 ' nvl(lv3.start_date_active, sysdate) <= sysdate AND' ||
315 ' nvl(lv3.end_date_active, sysdate) >= sysdate AND' ||
316 ' l.lane_id = ls.lane_id(+) AND' ||
317 ' m.category_id(+) = l.commodity_catg_id AND' ||
318 ' l.origin_id = ro.region_id AND' ||
319 ' l.destination_id = rd.region_id AND' ||
320 ' nvl(l.editable_flag, ''Y'') <> ''D'' ';
321
322 -- [02/18] Bug # 3401165
323 -- Effective Date field has been added to Lane Search UI
324 --
325 --We return only currently effective dates unless we are doing
326 --freight rating, in which case we want to return the lanes
327 --that are effective on the departure date.
328 IF (p_search_criteria.effective_date IS NOT NULL) THEN
329 l_lane_eff_date := to_char(p_search_criteria.effective_date, 'mm-dd-yyyy hh24:mi');
330
331 -- Effective date for Lane
332 IF (p_search_criteria.effective_date_type IS NULL OR
333 p_search_criteria.effective_date_type = '=' OR
334 p_search_criteria.effective_date_type = '<') THEN
335
339 Process_Bind_Var(l_bindvars_common, l_lane_eff_date, g_date, g_type3);
336 l_date_clause := ' AND nvl(l.effective_date, :' || g_bind_counter_global || ') <= :';
337 Process_Bind_Var(l_bindvars_common, l_lane_eff_date, g_date, g_type3);
338 l_date_clause := l_date_clause || g_bind_counter_global;
340
341 END IF;
342
343
344 -- Expiry date for Lane
345 IF (p_search_criteria.effective_date_type IS NULL OR
346 p_search_criteria.effective_date_type = '=' OR
347 p_search_criteria.effective_date_type = '>') THEN
348
349 l_date_clause := l_date_clause || ' AND nvl(l.expiry_date, :' || g_bind_counter_global || ') >= :';
350 Process_Bind_Var(l_bindvars_common, l_lane_eff_date, g_date, g_type3);
351
352 l_date_clause := l_date_clause || g_bind_counter_global;
353 Process_Bind_Var(l_bindvars_common, l_lane_eff_date, g_date, g_type3);
354
355 END IF;
356
357 ELSE
358 l_date_clause := ' AND nvl(l.expiry_date, sysdate) >= sysdate ';
359 --' AND nvl(l.effective_date, sysdate) <= sysdate ' ||
360 END IF;
361 /*
362 --We return only currently effective dates unless we are doing
363 --freight rating, in which case we want to return the lanes
364 --that are effective on the departure date.
365 IF (p_search_criteria.dep_date_from IS NOT NULL) THEN
366 l_lane_eff_date := to_char(p_search_criteria.dep_date_from, 'mm-dd-yyyy hh24:mi');
367 l_date_clause := ' AND nvl(l.effective_date, :' || g_bind_counter_global || ') <= :';
368
369 Process_Bind_Var(l_bindvars_common, l_lane_eff_date, g_date, g_type3);
370 l_date_clause := l_date_clause || g_bind_counter_global;
371 Process_Bind_Var(l_bindvars_common, l_lane_eff_date, g_date, g_type3);
372
373 l_date_clause := l_date_clause || ' AND nvl(l.expiry_date, :' || g_bind_counter_global || ') >= :';
374 Process_Bind_Var(l_bindvars_common, l_lane_eff_date, g_date, g_type3);
375 l_date_clause := l_date_clause || g_bind_counter_global;
376 Process_Bind_Var(l_bindvars_common, l_lane_eff_date, g_date, g_type3);
377 ELSE
378 l_date_clause := ' AND nvl(l.effective_date, sysdate) <= sysdate ' ||
379 ' AND nvl(l.expiry_date, sysdate) >= sysdate ';
380 END IF;
381 */
382 l_query_common_join := l_query_common_join || l_date_clause;
383
384 END IF;
385
386 -- build query logic for plain lane id search (used with lane groups view lane details)
387 IF (l_lane_ids_list is not null) THEN
388 l_lane_id_search := 'Y';
389 l_query_from := l_query_from_lane_num;
390 END IF;
391
392 -- build query logic for plain lane number search
393 IF (l_lane_id_search = 'N' AND l_lane_number is not null AND
394 l_origins.count > 0 and l_destinations.count > 0 and
395 l_origins(1).region_type is null AND l_origins(1).region_id is null AND
396 l_destinations(1).region_type is null AND l_destinations(1).region_id is null) THEN
397 l_lane_number_search := 'Y';
398 l_query_from := l_query_from_lane_num;
399 ELSIF (l_lane_id_search = 'N') THEN
400 l_query_from := l_query_from_regions;
401 END IF;
402
403 -- figure out the regions query part or no regions
404 IF (l_lane_number_search = 'N' AND l_lane_id_search = 'N') THEN
405
406 -- add common join stuff for queries with regions
407 l_query_common_join := l_query_common_join || ' AND l.owner_id = zr.party_id' ||
408 ' AND l.owner_id = pd.party_id' ||
409 ' AND l.origin_id = zr.parent_region_id' ||
410 ' AND l.destination_id = pd.parent_region_id';
411
412 -- first query
413 Create_Regions_Clause(p_parent_origins => l_parent_origins,
414 p_parent_destinations => l_parent_destinations,
415 p_origin_zip_request => l_origin_zip_request,
416 p_dest_zip_request => l_dest_zip_request,
417 x_query => l_query_relax_up,
418 x_bindvars => l_bindvars1);
419
420 --Relax down only if the origin and the destination were found.
421 IF (l_relax_flag = 'Y' AND l_origins.COUNT > 0 AND l_destinations.COUNT > 0) THEN
422
423 -- second query
424 l_query_relax_down := ' AND zr.region_id in (select f1.region_id from wsh_regions f1 where f1.parent_region_id = :' || g_bind_counter_global;
425 Process_Bind_Var(l_bindvars2, to_char(l_origins(1).region_id), g_number, g_type2);
426
427 -- duplicating predicate for performance
428 l_query_relax_down := l_query_relax_down || ' AND f1.parent_region_id = :' || g_bind_counter_global;
429 Process_Bind_Var(l_bindvars2, to_char(l_origins(1).region_id), g_number, g_type2);
430
431 l_query_relax_down := l_query_relax_down || ' UNION select :' || g_bind_counter_global ||
432 ' from dual) ';
433 Process_Bind_Var(l_bindvars2, to_char(l_origins(1).region_id), g_number, g_type2);
434
435
436 l_query_relax_down := l_query_relax_down || ' AND pd.region_id in (select f1.region_id from wsh_regions f1 where f1.parent_region_id = :' || g_bind_counter_global;
437 Process_Bind_Var(l_bindvars2, to_char(l_destinations(1).region_id), g_number, g_type2);
438
439 -- duplicating predicate for performance
440 l_query_relax_down := l_query_relax_down || ' AND f1.parent_region_id = :' || g_bind_counter_global;
444 ' from dual) ';
441 Process_Bind_Var(l_bindvars2, to_char(l_destinations(1).region_id), g_number, g_type2);
442
443 l_query_relax_down := l_query_relax_down || ' UNION select :' || g_bind_counter_global ||
445 Process_Bind_Var(l_bindvars2, to_char(l_destinations(1).region_id), g_number, g_type2);
446
447 -- add postal code filter
448 IF (l_origin_zip_request is not null) THEN
449 l_query_relax_down := l_query_relax_down || ' AND :' || g_bind_counter_global;
450 Process_Bind_Var(l_bindvars2, l_origin_zip_request, g_varchar2, g_type2);
451 l_query_relax_down := l_query_relax_down || ' between rpad(nvl(zr.postal_code_from, ''0''), :' || g_bind_counter_global || ', '' '')';
452 Process_Bind_Var(l_bindvars2, length(l_origin_zip_request), g_number, g_type2);
453 l_query_relax_down := l_query_relax_down || ' AND rpad(nvl(zr.postal_code_to, ''zzzzzzz''), :' || g_bind_counter_global || ', ''z'')';
454 Process_Bind_Var(l_bindvars2, length(l_origin_zip_request), g_number, g_type2);
455 END IF;
456
457 IF (l_dest_zip_request is not null) THEN
458 l_query_relax_down := l_query_relax_down || ' AND :' || g_bind_counter_global;
459 Process_Bind_Var(l_bindvars2, l_dest_zip_request, g_varchar2, g_type2);
460 l_query_relax_down := l_query_relax_down || ' between rpad(nvl(pd.postal_code_from, ''0''), :' || g_bind_counter_global || ', '' '')';
461 Process_Bind_Var(l_bindvars2, length(l_dest_zip_request), g_number, g_type2);
462 l_query_relax_down := l_query_relax_down || ' AND rpad(nvl(pd.postal_code_to, ''zzzzzzz''), :' || g_bind_counter_global || ', ''z'')';
463 Process_Bind_Var(l_bindvars2, length(l_dest_zip_request), g_number, g_type2);
464 END IF;
465 END IF;
466 END IF;
467
468 -- other search parameters
469 l_query_common_criteria := '';
470
471 -- append other query clauses if not lane id search
472 IF (l_lane_id_search = 'N') THEN
473
474 IF (l_mode is not null) THEN
475 l_query_common_criteria := l_query_common_criteria ||
476 ' AND l.mode_of_transportation_code = :' || g_bind_counter_global;
477 Process_Bind_Var(l_bindvars_common, l_mode, g_varchar2, g_type3);
478 END IF;
479
480 IF (l_carrier_name is not null AND l_carrier_id is null) THEN
481 OPEN c_get_carrier_id(l_carrier_name);
482 FETCH c_get_carrier_id INTO l_carrier_id;
483 CLOSE c_get_carrier_id;
484 END IF;
485 IF (l_tariff_name is not null) THEN
486 l_query_common_criteria := l_query_common_criteria ||
487 ' AND l.tariff_name like :' || g_bind_counter_global;
488 Process_Bind_Var(l_bindvars_common, l_tariff_name, g_varchar2, g_type3);
489 END IF;
490 IF (l_carrier_id is not null) THEN
491 l_query_common_criteria := l_query_common_criteria ||
492 ' AND l.carrier_id = :' || g_bind_counter_global;
493 Process_Bind_Var(l_bindvars_common, to_char(l_carrier_id), g_number, g_type3);
494 END IF;
495
496 IF (l_service is not null) THEN
497 OPEN c_get_lookup_code('WSH_SERVICE_LEVELS', l_service);
498 FETCH c_get_lookup_code INTO l_service_code;
499 CLOSE c_get_lookup_code;
500 END IF;
501
502 IF (l_service_code is not null) THEN
503 --l_query_from := l_query_from || ', fte_lane_services ls';
504 l_query_common_criteria := l_query_common_criteria ||
505 ' AND ls.lane_id = l.lane_id' ||
506 ' AND ls.service_code = :' || g_bind_counter_global;
507 Process_Bind_Var(l_bindvars_common, l_service_code, g_varchar2, g_type3);
508 END IF;
509
510 /*IF (l_equipment is not null) THEN
511 OPEN c_get_lookup_code('CONTAINER_TYPE', l_equipment);
512 FETCH c_get_lookup_code INTO l_equipment_code;
513 CLOSE c_get_lookup_code;
514 END IF;
515
516 IF (l_equipment_code is not null) THEN
517 l_query_from := l_query_from || ', fte_lane_equipments le';
518 l_query_common_criteria := l_query_common_criteria ||
519 ' AND le.lane_id = l.lane_id' ||
520 ' AND le.equipment_code = :' || g_bind_counter_global;
521 Process_Bind_Var(l_bindvars_common, l_equipment_code, g_varchar2, g_type3);
522 END IF;*/
523
524 IF (l_commodity is not null) THEN
525 FTE_UTIL_PKG.Get_Category_Id(p_commodity_value => l_commodity,
526 x_catg_id => l_commodity_catg_id,
527 x_status => x_status,
528 x_error_msg => x_return_message);
529
530
531 END IF;
532
533 IF (l_commodity_catg_id is not null AND x_status = -1) THEN
534 l_query_from := l_query_from || ', fte_lane_commodities lc';
535 l_query_common_criteria := l_query_common_criteria ||
536 ' AND lc.lane_id = l.lane_id' ||
537 ' AND lc.commodity_catg_id = :' || g_bind_counter_global;
538 Process_Bind_Var(l_bindvars_common, to_char(l_commodity_catg_id), g_number, g_type3);
539 END IF;
540
541 IF (l_lane_number is not null) THEN
545 ELSE
542 l_query_common_criteria := l_query_common_criteria || ' AND l.lane_number ';
543 IF (instr(l_lane_number, '%') > 0) THEN
544 l_query_common_criteria := l_query_common_criteria || 'like :';
546 l_query_common_criteria := l_query_common_criteria || '= :';
547 END IF;
548 l_query_common_criteria := l_query_common_criteria || g_bind_counter_global;
549
550 Process_Bind_Var(l_bindvars_common, l_lane_number, g_varchar2, g_type3);
551 END IF;
552
553 IF (l_schedule_only_flag is not null AND l_schedule_only_flag = 'Y') THEN
554 l_query_common_criteria := l_query_common_criteria ||
555 ' AND nvl(l.schedules_flag, ''N'') = ''Y''';
556 END IF;
557
558 -- create schedule filter part of the query
559 -- by concatenating to l_query_common_criteria and l_bindvars_common
560 IF (p_search_criteria.dep_date_from is not null OR
561 p_search_criteria.dep_date_to is not null OR
562 p_search_criteria.arr_date_from is not null OR
563 p_search_criteria.arr_date_to is not null) THEN
564
565 l_query_from := l_query_from || ', fte_schedules s';
566 l_query_common_criteria := l_query_common_criteria || ' AND l.lane_id = s.lane_id(+)' ||
567 ' AND (( nvl(s.expiry_date, sysdate) >= sysdate' ||
568 ' AND nvl(s.effective_date, sysdate) <= sysdate';
569
570 Create_Schedule_Clause(p_dep_date_from => p_search_criteria.dep_date_from,
571 p_dep_date_to => p_search_criteria.dep_date_to,
572 p_arr_date_from => p_search_criteria.arr_date_from,
573 p_arr_date_to => p_search_criteria.arr_date_to,
574 x_query => l_query_schedule,
575 x_bindvars => l_bindvars_common, -- nocopy to procedure
576 x_return_message => l_message,
577 x_return_status => l_status);
578
579 l_query_common_criteria := l_query_common_criteria || l_query_schedule ||
580 ' OR NVL(l.SCHEDULES_FLAG, ''N'') = ''N'' )';
581
582 END IF;
583
584 -- create order by clause for first query
585 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
586
587 IF (l_lane_number_search = 'N' and p_source_type<>'T') THEN
588
589 -- origins
590 IF (l_parent_origins is not null) THEN
591 l_query_order_by := ' order by decode(l.origin_id, ';
592
593 l_counter := l_parent_origins.FIRST;
594 LOOP
595 IF (l_parent_origins(l_counter).region_id is not null) THEN
596 IF (l_counter = l_parent_origins.FIRST) THEN
597 l_query_order_by := l_query_order_by || ':' || g_bind_counter_global;
598 ELSE
599 l_query_order_by := l_query_order_by || ', :' || g_bind_counter_global;
600 END IF;
601
602 Process_Bind_Var(l_bindvars_orderby, to_char(l_parent_origins(l_counter).region_id), g_number, g_type4);
603
604 l_query_order_by := l_query_order_by || ', :' || g_bind_counter_global;
605 Process_Bind_Var(l_bindvars_orderby, to_char(l_parent_origins(l_counter).region_type), g_number, g_type4);
606
607 END IF;
608
609 EXIT WHEN l_counter = l_parent_origins.LAST;
610 l_counter := l_parent_origins.NEXT(l_counter);
611
612 END LOOP;
613
614 l_query_order_by := l_query_order_by || ') desc';
615
616 END IF;
617
618 -- destinations
619 IF (l_parent_destinations is not null) THEN
620 l_query_order_by := l_query_order_by || ', decode(l.destination_id, ';
621
622 l_counter := l_parent_destinations.FIRST;
623 LOOP
624 IF (l_parent_destinations(l_counter).region_id is not null) THEN
625 IF (l_counter = l_parent_destinations.FIRST) THEN
626 l_query_order_by := l_query_order_by || ':' || g_bind_counter_global;
627 ELSE
628 l_query_order_by := l_query_order_by || ', :' || g_bind_counter_global;
629 END IF;
630
631 Process_Bind_Var(l_bindvars_orderby, to_char(l_parent_destinations(l_counter).region_id), g_number, g_type4);
632
633 l_query_order_by := l_query_order_by || ', :' || g_bind_counter_global;
634 Process_Bind_Var(l_bindvars_orderby, to_char(l_parent_destinations(l_counter).region_type), g_number, g_type4);
635
636 END IF;
637
638 EXIT WHEN l_counter = l_parent_destinations.LAST;
639 l_counter := l_parent_destinations.NEXT(l_counter);
640
641 END LOOP;
642
643 l_query_order_by := l_query_order_by || ') desc';
644
645 END IF;
646
647 END IF;
648
649
650 ELSE -- lane id search
651
652 l_query_common_criteria := ' AND l.lane_id = :' || g_bind_counter_global;
653 Process_Bind_Var(l_bindvars_common, l_lane_ids_list, g_number, g_type3);
654
655 END IF; -- end if l_lane_id_search = 'N'
656
657
658 l_query_group_by :='';
659
663 l_query_group_by := ' group by wc.carrier_name, l.carrier_id, l.origin_id, l.destination_id, lv2.meaning, l.mode_of_transportation_code, r.region_type ';
660 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
661 -- create a group by when p_source_type = 'T'
662 IF (p_source_type ='T') THEN
664 END IF;
665
666 IF l_debug_on THEN
667 WSH_DEBUG_SV.log(l_module_name,l_query_group_by);
668 END IF;
669
670 -- return the query string and bind variables
671 IF (l_lane_id_search = 'Y') THEN
672 l_query1 := l_query_select || l_query_from ||
673 l_query_common_join ||
674 l_query_common_criteria;
675 ELSIF (l_lane_number_search = 'Y') THEN
676 l_query1 := l_query_select || l_query_from ||
677 l_query_common_join || l_query_relax_up ||
678 l_query_common_criteria;
679 ELSE
680 -- [11/11] Add query type for Lane Group and Commitment -> 'T'
681 IF (p_source_type = 'T') THEN
682
683 l_query1 := l_query_select || l_query_from ||
684 l_query_common_join || l_query_relax_up ||
685 l_query_common_criteria || l_query_group_by;
686 ELSE
687
688 l_query1 := l_query_select || l_query_from ||
689 l_query_common_join || l_query_relax_up ||
690 l_query_common_criteria ||l_query_order_by;
691
692 END if;
693
694 l_query2 := l_query_select || l_query_from ||
695 l_query_common_join || l_query_relax_down ||
696 l_query_common_criteria||l_query_group_by;
697 END IF;
698
699 IF l_debug_on THEN
700 WSH_DEBUG_SV.log(l_module_name,l_query1);
701 END IF;
702
703 x_query1 := l_query1;
704 x_query2 := l_query2;
705 x_bindvars1 := l_bindvars1;
706 x_bindvars2 := l_bindvars2;
707 x_bindvars_common := l_bindvars_common;
708 x_bindvars_orderby := l_bindvars_orderby;
709
710 --
711 -- Debug Statements
712 --
713 IF l_debug_on THEN
714 WSH_DEBUG_SV.pop(l_module_name);
715 END IF;
716 --
717 END Create_Lane_Query;
718
719
720 -- ----------------------------------------------------------------
721 -- Name: Create_Regions_Clause
722 -- Type: Procedure
723 --
724 -- Description: This procedure takes in origin, destination
725 -- information, as well as postal codes entered
726 -- by the user, and creates the regions portion
727 -- of the relax up query.
728 -- Returns bind variable array.
729 --
730 -- This is also used by Rate Chart Search, which is why it is
731 -- separated out from Create_Lane_Query.
732 --
733 -- -----------------------------------------------------------------
734 PROCEDURE Create_Regions_Clause(p_parent_origins IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
735 p_parent_destinations IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
736 p_origin_zip_request IN VARCHAR2,
737 p_dest_zip_request IN VARCHAR2,
738 x_query OUT NOCOPY VARCHAR2,
739 x_bindvars IN OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars) IS
740
741 l_query VARCHAR2(4000);
742 l_counter NUMBER;
743
744 --
745 l_debug_on BOOLEAN;
746 --
747 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_REGIONS_CLAUSE';
748 --
749 BEGIN
750
751 --
752 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
753 --
754 IF l_debug_on IS NULL
755 THEN
756 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
757 END IF;
758 --
759 --
760 -- Debug Statements
761 --
762 IF l_debug_on THEN
763 WSH_DEBUG_SV.push(l_module_name);
764 END IF;
765 --
766
767 -- origins and origin parents
768 IF (p_parent_origins is not null AND p_parent_origins.COUNT > 0) THEN
769 l_query := ' AND zr.region_id in (';
770
771 l_counter := p_parent_origins.FIRST;
772 LOOP
773 IF (p_parent_origins(l_counter).region_id is not null) THEN
774 IF (l_counter = 1) THEN
775 l_query := l_query || ':' || g_bind_counter_global;
776 ELSE
777 l_query := l_query || ', :' || g_bind_counter_global;
778 END IF;
779 Process_Bind_Var(x_bindvars, to_char(p_parent_origins(l_counter).region_id), g_number, g_type1);
780
781 END IF;
782
783 EXIT WHEN l_counter = p_parent_origins.LAST;
784 l_counter := p_parent_origins.NEXT(l_counter);
785
786 END LOOP;
787 l_query := l_query || ')';
788 END IF;
789
790 -- destinations and destination parents
791 IF (p_parent_destinations is not null AND p_parent_destinations.COUNT > 0 ) THEN
792 l_query := l_query || ' AND pd.region_id in (';
793
794 l_counter := p_parent_destinations.FIRST;
795 LOOP
799 ELSE
796 IF (p_parent_destinations(l_counter).region_id is NOT null) THEN
797 IF (l_counter = p_parent_destinations.FIRST) THEN
798 l_query := l_query || ':' || g_bind_counter_global;
800 l_query := l_query || ', :' || g_bind_counter_global;
801 END IF;
802 Process_Bind_Var(x_bindvars, to_char(p_parent_destinations(l_counter).region_id), g_number, g_type1);
803
804 END IF;
805
806 EXIT WHEN l_counter = p_parent_destinations.LAST;
807 l_counter := p_parent_destinations.NEXT(l_counter);
808
809 END LOOP;
810 l_query := l_query || ')';
811 END IF;
812
813 -- add postal code filter
814 IF (p_origin_zip_request is not null) THEN
815 l_query := l_query || ' AND :' || g_bind_counter_global;
816 Process_Bind_Var(x_bindvars, p_origin_zip_request, g_varchar2, g_type1);
817 l_query := l_query || ' between rpad(nvl(zr.postal_code_from, ''0''), :' || g_bind_counter_global || ', '' '')';
818 Process_Bind_Var(x_bindvars, length(p_origin_zip_request), g_number, g_type1);
819 l_query := l_query || ' AND rpad(nvl(zr.postal_code_to, ''zzzzzzz''), :' || g_bind_counter_global || ', ''z'')';
820 Process_Bind_Var(x_bindvars, length(p_origin_zip_request), g_number, g_type1);
821 ELSIF (p_dest_zip_request IS NULL) THEN
822 l_query := l_query || ' AND zr.postal_code_from IS NULL AND zr.postal_code_to IS NULL ';
823 END IF;
824
825 IF (p_dest_zip_request is not null) THEN
826 l_query := l_query || ' AND :' || g_bind_counter_global;
827 Process_Bind_Var(x_bindvars, p_dest_zip_request, g_varchar2, g_type1);
828 l_query := l_query || ' between rpad(nvl(pd.postal_code_from, ''0''), :' || g_bind_counter_global || ', '' '')';
829 Process_Bind_Var(x_bindvars, length(p_dest_zip_request), g_number, g_type1);
830 l_query := l_query || ' AND rpad(nvl(pd.postal_code_to, ''zzzzzzz''), :' || g_bind_counter_global || ', ''z'')';
831 Process_Bind_Var(x_bindvars, length(p_dest_zip_request), g_number, g_type1);
832 ELSIF (p_origin_zip_request IS NULL) THEN
833 l_query := l_query || ' AND pd.postal_code_from IS NULL AND pd.postal_code_to IS NULL';
834 END IF;
835 x_query := l_query;
836
837 --
838 -- Debug Statements
839 --
840 IF l_debug_on THEN
841 WSH_DEBUG_SV.pop(l_module_name);
842 END IF;
843 --
844 END Create_Regions_Clause;
845
846 -- ----------------------------------------------------------------
847 -- Name: Create_Schedule_Query
848 -- Type: Procedure
849 --
850 -- Description: This procedure takes in search criteria and
851 -- origin, destination ids, and creates the
852 -- dynamic sql statement for searching schedules.
853 -- This procedure relies on the lane_id's of the
854 -- lanes whose schedules are sought for to be
855 -- passed in via p_search_criteria.lane_ids_list.
856 -- Returns bind variable array and query string.
857 --
858 -- -----------------------------------------------------------------
859 PROCEDURE Create_Schedule_Query(p_search_criteria IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_criteria_rec,
860 p_origins IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
861 p_destinations IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
862 p_parent_origins IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
863 p_parent_destinations IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
864 x_query OUT NOCOPY VARCHAR2,
865 x_bindvars OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars,
866 x_return_message OUT NOCOPY VARCHAR2,
867 x_return_status OUT NOCOPY VARCHAR2) IS
868
869 l_query VARCHAR2(8000);
870 l_query_dates VARCHAR2(8000);
871
872 l_bindvars FTE_LANE_SEARCH_QUERY_GEN.bindvars;
873 l_message VARCHAR2(200);
874 l_status VARCHAR2(1);
875
876 --
877 l_debug_on BOOLEAN;
878 --
879 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_SCHEDULE_QUERY';
880 --
881 BEGIN
882
883 --
884 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
885 --
886 IF l_debug_on IS NULL
887 THEN
888 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
889 END IF;
890 --
891 --
892 -- Debug Statements
893 --
894 IF l_debug_on THEN
895 WSH_DEBUG_SV.push(l_module_name);
896 END IF;
897 --
898 g_bind_counter_common := 1;
899 g_bind_counter_global := 1;
900
901 l_query := 'SELECT S.SCHEDULES_ID, ' ||
902 'S.LANE_ID, ' ||
903 'L.LANE_NUMBER, ' ||
904 'S.DEPARTURE_DATE, ' ||
905 'S.ARRIVAL_DATE, ' ||
906 'S.TRANSIT_TIME, ' ||
907 'S.FREQUENCY, ' ||
908 'S.EFFECTIVE_DATE, ' ||
909 'S.PORT_OF_LOADING, ' ||
913 'S.FREQUENCY_ARRIVAL, ' ||
910 'S.PORT_OF_DISCHARGE, ' ||
911 'S.EXPIRY_DATE, ' ||
912 'S.ARRIVAL_DATE_INDICATOR, ' ||
914 'L.ORIGIN_ID, ' ||
915 'L.DESTINATION_ID, ' ||
916 'LV2.MEANING AS MODE_OF_TRANSPORTATION, ' ||
917 'L.CARRIER_ID, ' ||
918 'H.PARTY_NAME AS CARRIER_NAME, ' ||
919 'S.DEPARTURE_TIME, ' ||
920 'S.ARRIVAL_TIME, ' ||
921 'LV1.MEANING AS FREQUENCY_TYPE_MEANING, ' ||
922 'S.TRANSIT_TIME_UOM, ' ||
923 'S.VESSEL_TYPE, ' ||
924 'S.VESSEL_NAME, ' ||
925 'S.VOYAGE_NUMBER, ' ||
926 'S.ARRIVAL_TIME || decode(S.ARRIVAL_DATE_INDICATOR, null, '''', '' ('' || decode(substr(S.ARRIVAL_DATE_INDICATOR, 1, 1), ''-'',' ||
927 ' TO_CHAR(S.ARRIVAL_DATE_INDICATOR), ''+'' || S.ARRIVAL_DATE_INDICATOR) || '')'') AS ARRIVAL_TIME_WITH_INDICATOR, ' ||
928 'L.MODE_OF_TRANSPORTATION_CODE, ' ||
929 'L.SERVICE_TYPE_CODE, ' ||
930 'DECODE(L.SERVICE_DETAIL_FLAG, ''Y'', DECODE(L.SERVICE_TYPE_CODE, null, ''multiple'', LV3.MEANING), null) AS SERVICE_TYPE_MEANING, ' ||
931 'S.FREQUENCY_TYPE AS FREQUENCY_TYPE, ' ||
932 --'S.FREQUENCY AS ORIG_FREQUENCY, ' ||
933 'NVL((SELECT ''Y'' FROM FTE_SCHEDULES S1 WHERE S1.SCHEDULES_ID = S.SCHEDULES_ID AND SYSDATE BETWEEN NVL(S.EFFECTIVE_DATE, SYSDATE) AND NVL(S.EXPIRY_DATE, SYSDATE)), ''N'') AS ACTIVE_FLAG ' ||
934 'FROM FTE_SCHEDULES S, FTE_LANES L, FND_LOOKUP_VALUES_VL LV1, ' ||
935 'FND_LOOKUP_VALUES_VL LV2, HZ_PARTIES H, FND_LOOKUP_VALUES_VL LV3 ' ||
936 'WHERE S.LANE_ID = L.LANE_ID ' ||
937 'AND NVL(S.EDITABLE_FLAG, ''Y'') = ''Y'' ' ||
938 'AND NVL(S.FREQUENCY_TYPE, ''NULL'') = LV1.LOOKUP_CODE ' ||
939 'AND LV1.LOOKUP_TYPE = ''FTE_FREQUENCY_TYPE'' ' ||
940 'AND LV2.LOOKUP_TYPE = ''WSH_MODE_OF_TRANSPORT'' ' ||
941 'AND LV2.LOOKUP_CODE = L.MODE_OF_TRANSPORTATION_CODE ' ||
942 'AND L.CARRIER_ID = H.PARTY_ID ' ||
943 'AND LV3.LOOKUP_TYPE(+) = ''WSH_SERVICE_LEVELS'' ' ||
944 'AND LV3.LOOKUP_CODE(+) = L.SERVICE_TYPE_CODE ' ||
945 'AND L.LANE_ID IN (' || p_search_criteria.lane_ids_list || ')';
946
947 IF (p_search_criteria.dep_date_from is not null OR
948 p_search_criteria.dep_date_to is not null OR
949 p_search_criteria.arr_date_from is not null OR
950 p_search_criteria.arr_date_to is not null) THEN
951
952 Create_Schedule_Clause(p_dep_date_from => p_search_criteria.dep_date_from,
953 p_dep_date_to => p_search_criteria.dep_date_to,
954 p_arr_date_from => p_search_criteria.arr_date_from,
955 p_arr_date_to => p_search_criteria.arr_date_to,
956 x_query => l_query_dates,
957 x_bindvars => l_bindvars,
958 x_return_message => l_message,
959 x_return_status => l_status);
960
961 l_query := l_query || 'AND ((NVL(S.EXPIRY_DATE, sysdate) >= sysdate ' ||
962 'AND NVL(S.EFFECTIVE_DATE, sysdate) <= sysdate ' || l_query_dates || ')';
963
964 END IF;
965
966 x_query := l_query;
967 x_bindvars := l_bindvars;
968 x_return_message := l_message;
969 x_return_status := l_status;
970
971 --
972 -- Debug Statements
973 --
974 IF l_debug_on THEN
975 WSH_DEBUG_SV.pop(l_module_name);
976 END IF;
977 --
978 END Create_Schedule_Query;
979
980
981 -- ----------------------------------------------------------------
982 -- Name: Create_Schedule_Clause
983 -- Type: Procedure
984 --
985 -- Description: This procedure takes in 4 dates and creates
986 -- the dynamic sql statement for the schedules
987 -- filter.
988 -- Returns bind variables and query string.
989 --
990 -- -----------------------------------------------------------------
991 PROCEDURE Create_Schedule_Clause(p_dep_date_from IN DATE,
992 p_dep_date_to IN DATE,
993 p_arr_date_from IN DATE,
994 p_arr_date_to IN DATE,
995 x_query OUT NOCOPY VARCHAR2,
996 x_bindvars IN OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars,
997 x_return_message OUT NOCOPY VARCHAR2,
998 x_return_status OUT NOCOPY VARCHAR2) IS
999
1000 l_query VARCHAR2(8000);
1001
1002 -- used by frequency-based schedules in case any are missing
1003 l_dep_date_from DATE;
1004 l_dep_date_to DATE;
1005 l_arr_date_from DATE;
1006 l_arr_date_to DATE;
1007
1008 l_dep_date_from_time VARCHAR2(10);
1009 l_dep_date_to_time VARCHAR2(10);
1010 l_arr_date_from_time VARCHAR2(10);
1011 l_arr_date_to_time VARCHAR2(10);
1012
1016 l_arr_to_day_of_week NUMBER;
1013 l_dep_from_day_of_week NUMBER; -- Sunday = 1, Monday = 2, etc.
1014 l_dep_to_day_of_week NUMBER;
1015 l_arr_from_day_of_week NUMBER;
1017
1018 TYPE t_days_of_week_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1019 l_dep_date_array t_days_of_week_array;
1020 l_arr_date_array t_days_of_week_array;
1021
1022 l_diff_dep NUMBER;
1023 l_diff_arr NUMBER;
1024 l_date_range_from NUMBER;
1025 l_date_range_to NUMBER;
1026 l_counter NUMBER;
1027 l_counter2 NUMBER;
1028 l_mod_var NUMBER;
1029 l_tag VARCHAR2(1) := 'N';
1030 l_condition_one VARCHAR2(1) := 'N';
1031
1032 l_date_format VARCHAR2(20) := 'MM-dd-yyyy HH24:mi';
1033
1034 --
1035 l_debug_on BOOLEAN;
1036 --
1037 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_SCHEDULE_CLAUSE';
1038 --
1039 BEGIN
1040
1041 -- date-based schedules
1042 --
1043 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1044 --
1045 IF l_debug_on IS NULL
1046 THEN
1047 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1048 END IF;
1049 --
1050 --
1051 -- Debug Statements
1052 --
1053 IF l_debug_on THEN
1054 WSH_DEBUG_SV.push(l_module_name);
1055 --
1056 WSH_DEBUG_SV.log(l_module_name,'P_DEP_DATE_FROM',P_DEP_DATE_FROM);
1057 WSH_DEBUG_SV.log(l_module_name,'P_DEP_DATE_TO',P_DEP_DATE_TO);
1058 WSH_DEBUG_SV.log(l_module_name,'P_ARR_DATE_FROM',P_ARR_DATE_FROM);
1059 WSH_DEBUG_SV.log(l_module_name,'P_ARR_DATE_TO',P_ARR_DATE_TO);
1060 END IF;
1061 --
1062 l_query := ' AND (s.frequency_type is null AND ';
1063
1064 -- departure date
1065 IF (p_dep_date_from is not null AND p_dep_date_to is not null) THEN
1066
1067 l_query := l_query || ' trunc(s.departure_date, ''mi'') >= :' || g_bind_counter_global || ' AND';
1068 Process_Bind_Var(x_bindvars, to_char(p_dep_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1069
1070 l_query := l_query || ' trunc(s.departure_date, ''mi'') <= :' || g_bind_counter_global;
1071 Process_Bind_Var(x_bindvars, to_char(p_dep_date_to, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1072
1073 END IF;
1074
1075 IF (p_dep_date_from is not null AND p_dep_date_to is null) THEN
1076
1077 l_query := l_query || ' trunc(s.departure_date, ''mi'') >= :' || g_bind_counter_global;
1078 Process_Bind_Var(x_bindvars, to_char(p_dep_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1079
1080 END IF;
1081
1082 IF (p_dep_date_from is null AND p_dep_date_to is not null) THEN
1083
1084 l_query := l_query || ' trunc(s.departure_date, ''mi'') >= :' || g_bind_counter_global;
1085 Process_Bind_Var(x_bindvars, to_char(p_dep_date_to, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1086
1087 END IF;
1088
1089 -- check if AND condition needed
1090 IF ( (p_dep_date_from is not null OR p_dep_date_to is not null) AND
1091 (p_arr_date_from is not null OR p_arr_date_to is not null) ) THEN
1092
1093 l_query := l_query || ' AND ';
1094
1095 END IF;
1096
1097 -- arrival date
1098 IF (p_arr_date_from is not null AND p_arr_date_to is not null) THEN
1099
1100 l_query := l_query || ' trunc(s.arrival_date, ''mi'') >= :' || g_bind_counter_global || ' AND';
1101 Process_Bind_Var(x_bindvars, to_char(p_arr_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1102
1103 l_query := l_query || ' trunc(s.arrival_date, ''mi'') <= :' || g_bind_counter_global;
1104 Process_Bind_Var(x_bindvars, to_char(p_arr_date_to, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1105
1106 END IF;
1107
1108 IF (p_arr_date_from is not null AND p_arr_date_to is null) THEN
1109
1110 l_query := l_query || ' trunc(s.arrival_date, ''mi'') >= :' || g_bind_counter_global;
1111 Process_Bind_Var(x_bindvars, to_char(p_arr_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1112
1113 END IF;
1114
1115 IF (p_arr_date_from is null AND p_arr_date_to is not null) THEN
1116
1117 l_query := l_query || ' trunc(s.arrival_date, ''mi'') >= :' || g_bind_counter_global;
1118 Process_Bind_Var(x_bindvars, to_char(p_arr_date_to, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1119
1120 END IF;
1121
1122 l_query := l_query || ')'; -- closes frequency_type is null
1123 -- end of date-based schedules
1124 -- dbms_output.put_line('end of date based schedules');
1125 -- frequency-based schedules
1126 l_dep_date_from := p_dep_date_from;
1127 l_dep_date_to := p_dep_date_to;
1128 l_arr_date_from := p_arr_date_from;
1129 l_arr_date_to := p_arr_date_to;
1130
1131 -- daily schedules
1132 l_query := l_query || ' OR ( ( s.frequency_type = ''DAILY'''; -- added another open ( 11/15/02 11:30am
1133
1134 IF (p_dep_date_from is not null AND p_dep_date_to is null AND
1135 p_arr_date_from is not null AND p_arr_date_to is null) THEN
1136
1137 l_query := l_query || ' AND to_number(nvl(s.arrival_date_indicator, 0)) >= ceil(:' || g_bind_counter_global;
1138 Process_Bind_Var(x_bindvars, to_char(p_arr_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1139
1140 l_query := l_query || ' - :' || g_bind_counter_global || ')';
1141 Process_Bind_Var(x_bindvars, to_char(p_dep_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1142
1143 ELSE
1147 IF (p_dep_date_from is not null AND p_dep_date_to is not null AND
1144 IF (p_dep_date_from is not null AND p_dep_date_to is not null) THEN
1145 l_diff_dep := floor(p_dep_date_to - p_dep_date_from);
1146 END IF;
1148 p_arr_date_from is not null AND p_arr_date_to is not null) THEN
1149 IF (l_diff_dep >= 0 AND l_diff_dep <= 7) THEN
1150 l_query := l_query || ' AND (';
1151 FOR l_counter IN 0..l_diff_dep LOOP
1152 l_query := l_query || ' ( to_date(to_char(trunc(:' || g_bind_counter_global;
1153 Process_Bind_Var(x_bindvars, to_char(p_dep_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1154
1155 l_query := l_query || '), ''MM-dd-yyyy'') || '' '' || s.departure_time, :' || g_bind_counter_global;
1156 Process_Bind_Var(x_bindvars, l_date_format, g_varchar2, g_type3);
1157
1158 l_query := l_query || ') + :' || g_bind_counter_global;
1159 Process_Bind_Var(x_bindvars, to_char(l_counter), g_number, g_type3);
1160
1161 l_query := l_query || ' between :' || g_bind_counter_global;
1162 Process_Bind_Var(x_bindvars, to_char(p_dep_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1163
1164 l_query := l_query || ' and :' || g_bind_counter_global;
1165 Process_Bind_Var(x_bindvars, to_char(p_dep_date_to, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1166
1167 l_query := l_query || ' AND to_date(to_char(trunc(:' || g_bind_counter_global;
1168 Process_Bind_Var(x_bindvars, to_char(p_dep_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1169
1170 l_query := l_query || '), ''MM-dd-yyyy'') || '' '' || s.arrival_time, :' || g_bind_counter_global;
1171 Process_Bind_Var(x_bindvars, l_date_format, g_varchar2, g_type3);
1172
1173 l_query := l_query || ') + nvl(s.arrival_date_indicator, 0) + :' || g_bind_counter_global;
1174 Process_Bind_Var(x_bindvars, to_char(l_counter), g_number, g_type3);
1175
1176 l_query := l_query || ' between :' || g_bind_counter_global;
1177 Process_Bind_Var(x_bindvars, to_char(p_arr_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1178
1179 l_query := l_query || ' and :' || g_bind_counter_global || ')';
1180 Process_Bind_Var(x_bindvars, to_char(p_arr_date_to, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1181
1182 IF (l_counter < l_diff_dep) THEN
1183 l_query := l_query || ' OR ';
1184 END IF;
1185
1186 END LOOP;
1187
1188 l_query := l_query || ' ) ';
1189
1190 END IF; -- end if l_diff_dep >= 0 AND l_diff_dep <= 7
1191
1192 ELSIF (p_dep_date_from is not null AND p_dep_date_to is not null) THEN -- only departure dates
1193
1194 IF (l_diff_dep >= 0 AND l_diff_dep <= 7) THEN
1195 l_query := l_query || ' AND (';
1196 FOR l_counter IN 0..l_diff_dep LOOP
1197 l_query := l_query || ' ( to_date(to_char(trunc(:' || g_bind_counter_global;
1198 Process_Bind_Var(x_bindvars, to_char(p_dep_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1199
1200 l_query := l_query || '), ''MM-dd-yyyy'') || '' '' || s.departure_time, :' || g_bind_counter_global;
1201 Process_Bind_Var(x_bindvars, l_date_format, g_varchar2, g_type3);
1202
1203 l_query := l_query || ') + :' || g_bind_counter_global;
1204 Process_Bind_Var(x_bindvars, to_char(l_counter), g_number, g_type3);
1205
1206 l_query := l_query || ' between :' || g_bind_counter_global;
1207 Process_Bind_Var(x_bindvars, to_char(p_dep_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1208
1209 l_query := l_query || ' and :' || g_bind_counter_global || ' )';
1210 Process_Bind_Var(x_bindvars, to_char(p_dep_date_to, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1211
1212 IF (l_counter < l_diff_dep) THEN
1213 l_query := l_query || ' OR ';
1214 END IF;
1215
1216 END LOOP;
1217
1218 l_query := l_query || ' ) ';
1219
1220 END IF; -- end if l_diff_dep >= 0 AND l_diff_dep <= 7
1221
1222
1223 ELSIF (p_arr_date_from is not null AND p_arr_date_to is not null) THEN -- only arrival dates
1224
1225 l_diff_arr := floor(p_arr_date_to - p_arr_date_from);
1226
1227 IF (l_diff_arr >= 0 AND l_diff_arr <= 7) THEN
1228 l_query := l_query || ' AND (';
1229 FOR l_counter IN 0..l_diff_arr LOOP
1230 l_query := l_query || ' ( to_date(to_char(trunc(:' || g_bind_counter_global;
1231 Process_Bind_Var(x_bindvars, to_char(p_arr_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1232
1233 l_query := l_query || '), ''MM-dd-yyyy'') || '' '' || s.arrival_time, :' || g_bind_counter_global;
1234 Process_Bind_Var(x_bindvars, l_date_format, g_varchar2, g_type3);
1235
1236 l_query := l_query || ') + :' || g_bind_counter_global;
1237 Process_Bind_Var(x_bindvars, to_char(l_counter), g_number, g_type3);
1238
1239 l_query := l_query || ' between :' || g_bind_counter_global;
1240 Process_Bind_Var(x_bindvars, to_char(p_arr_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1241
1242 l_query := l_query || ' and :' || g_bind_counter_global || ' )';
1246 l_query := l_query || ' OR ';
1243 Process_Bind_Var(x_bindvars, to_char(p_arr_date_to, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1244
1245 IF (l_counter < l_diff_arr) THEN
1247 END IF;
1248
1249 END LOOP;
1250
1251 l_query := l_query || ' ) ';
1252
1253 END IF; -- end if l_diff_arr >= 0 AND l_diff_arr <= 7
1254
1255 END IF; -- end if all 4 dates are not null
1256 END IF;
1257
1258 l_query := l_query || ' ) '; -- closes OR ( s.frequency_type = 'DAILY'
1259 -- dbms_output.put_line('end of daily frequency schedules');
1260
1261 -- weekly schedules
1262 -- there are 3 conditions needed in AND
1263 -- 1) departure day is in frequency range values
1264 -- 2) arrival day is in frequency arrival range values
1265 -- 3) departure day - arrival day = arrival time indicator
1266
1267 IF (p_dep_date_from is not null AND p_dep_date_to is not null) THEN
1268 l_diff_dep := floor(p_dep_date_to - p_dep_date_from);
1269 END IF;
1270
1271 IF ( p_dep_date_from is not null OR p_dep_date_to is not null OR
1272 p_arr_date_from is not null OR p_arr_date_to is not null ) THEN
1273
1274 l_query := l_query || ' OR (';
1275
1276 -- condition 1
1277 IF (p_dep_date_from is not null AND p_dep_date_to is null) THEN
1278 l_dep_from_day_of_week := to_number(to_char(l_dep_date_from, 'D'));
1279
1280 ELSIF (p_dep_date_from is null AND p_dep_date_to is not null) THEN
1281 l_dep_to_day_of_week := to_number(to_char(l_dep_date_to, 'D'));
1282
1283 ELSIF (p_dep_date_from is not null AND p_dep_date_to is not null) THEN
1284
1285 l_condition_one := 'Y';
1286
1287 l_dep_from_day_of_week := to_number(to_char(l_dep_date_from, 'D'));
1288 l_dep_to_day_of_week := to_number(to_char(l_dep_date_to, 'D'));
1289
1290 -- dbms_output.put_line('CONDITION 1: l_diff_dep = ' || l_diff_dep);
1291 -- dbms_output.put_line('CONDITION 1: l_dep_from_day_of_week = ' || l_dep_from_day_of_week);
1292 -- dbms_output.put_line('CONDITION 1: l_dep_to_day_of_week = ' || l_dep_to_day_of_week);
1293
1294 IF (l_diff_dep >= 7) THEN
1295 l_query := l_query || ' (s.frequency_type = ''WEEKLY'')';
1296 ELSE
1297 l_dep_date_from_time := to_char(l_dep_date_from, 'HH24:MI');
1298 l_dep_date_to_time := to_char(l_dep_date_to, 'HH24:MI');
1299
1300 IF (l_dep_to_day_of_week >= l_dep_from_day_of_week) THEN
1301
1302 l_counter2 := 1;
1303 FOR l_counter IN l_dep_from_day_of_week..l_dep_to_day_of_week LOOP
1304 l_dep_date_array(l_counter2) := l_counter;
1305 l_counter2 := l_counter2 + 1;
1306 END LOOP;
1307
1308 ELSE
1309
1310 l_counter2 := 1;
1311 FOR l_counter IN l_dep_from_day_of_week..l_dep_to_day_of_week + 7 LOOP
1312 l_mod_var := mod(l_counter, 7);
1313 IF (l_mod_var = 0) THEN
1314 l_mod_var := 7;
1315 END IF;
1316 l_dep_date_array(l_counter2) := l_mod_var;
1317 l_counter2 := l_counter2 + 1;
1318 END LOOP;
1319
1320 END IF;
1321
1322 IF (l_dep_date_array.COUNT > 0) THEN
1323 l_query := l_query || ' ( ';
1324 END IF;
1325
1326 IF (l_dep_date_array.COUNT = 1) THEN
1327
1328 l_query := l_query || ' s.frequency like :' || g_bind_counter_global;
1329 Process_Bind_Var(x_bindvars, '%' || l_dep_date_array(1) || '%', g_varchar2, g_type3);
1330
1331 l_query := l_query || ' and to_date(departure_time, ''hh24:mi'') >= to_date(:' ||
1332 g_bind_counter_global || ', ''hh24:mi'')';
1333 Process_Bind_Var(x_bindvars, l_dep_date_from_time, g_varchar2, g_type3);
1334
1335 l_query := l_query || ' and to_date(departure_time, ''hh24:mi'') <= to_date(:' ||
1336 g_bind_counter_global || ', ''hh24:mi'')';
1337 Process_Bind_Var(x_bindvars, l_dep_date_to_time, g_varchar2, g_type3);
1338
1339 ELSE
1340
1341 FOR l_counter IN 1..l_dep_date_array.COUNT LOOP
1342
1343 l_query := l_query || ' ( s.frequency like :' || g_bind_counter_global;
1344 Process_Bind_Var(x_bindvars, '%' || l_dep_date_array(l_counter) || '%', g_varchar2, g_type3);
1345
1346 IF (l_counter = 1) THEN -- first frequency
1347 l_query := l_query || ' and to_date(departure_time, ''hh24:mi'') >= to_date(:' ||
1348 g_bind_counter_global || ', ''hh24:mi'')';
1349 Process_Bind_Var(x_bindvars, l_dep_date_from_time, g_varchar2, g_type3);
1350 END IF;
1351
1352 IF (l_counter = l_dep_date_array.COUNT) THEN -- last frequency
1353 l_query := l_query || ' and to_date(departure_time, ''hh24:mi'') <= to_date(:' ||
1354 g_bind_counter_global || ', ''hh24:mi'')';
1355 Process_Bind_Var(x_bindvars, l_dep_date_to_time, g_varchar2, g_type3);
1356 END IF;
1357
1361
1358 IF (l_counter <> 1 AND l_counter <> l_dep_date_array.COUNT) THEN
1359 l_query := l_query || ')';
1360 END IF;
1362 IF (l_counter < l_dep_date_array.COUNT) THEN
1363 l_query := l_query || ' OR ';
1364 END IF;
1365
1366 END LOOP;
1367
1368
1369 l_query := l_query || ' ) ) '; -- ends if l_dep_date_array.COUNT > 0 --EXTRA )?
1370
1371 END IF;
1372
1373 IF (l_dep_date_array.COUNT > 0) THEN
1374 l_query := l_query || ' ) '; -- ends OR ( in beginning of weekly schedules
1375 END IF;
1376
1377 END IF;
1378
1379 END IF; -- end condition 1
1380 -- dbms_output.put_line('end condition 1');
1381
1382 -- added 11/18/02 11:30am
1383 IF (l_condition_one = 'N') THEN
1384 -- dbms_output.put_line('adding clause for weekly');
1385 l_query := l_query || ' (s.frequency_type = ''WEEKLY'' ';
1386 END IF;
1387 -- end added
1388
1389 -- condition 2
1390 IF (p_arr_date_from is not null AND p_arr_date_to is null) THEN
1391 l_arr_from_day_of_week := to_number(to_char(l_arr_date_from, 'D'));
1392
1393 l_query := l_query || ' ) '; -- added 11/15/02 11:11am
1394
1395 ELSIF (p_arr_date_from is null AND p_arr_date_to is not null) THEN
1396 l_arr_to_day_of_week := to_number(to_char(l_arr_date_to, 'D'));
1397
1398 l_query := l_query || ' ) '; -- added 11/15/02 11:11am
1399
1400 ELSIF (p_arr_date_from is not null AND p_arr_date_to is not null) THEN
1401
1402 -- if condition 1 is true
1403 IF (l_condition_one = 'Y') THEN
1404 l_query := l_query || ' AND ';
1405 ELSE
1406 l_query := l_query || ' ) AND ';
1407 END IF;
1408
1409 l_arr_from_day_of_week := to_number(to_char(l_arr_date_from, 'D'));
1410 l_arr_to_day_of_week := to_number(to_char(l_arr_date_to, 'D'));
1411
1412 l_diff_arr := floor(p_arr_date_to - p_arr_date_from);
1413 -- dbms_output.put_line('CONDITION 2: l_diff_arr = ' || l_diff_arr);
1414 -- dbms_output.put_line('CONDITION 2: l_arr_from_day_of_week = ' || l_arr_from_day_of_week);
1415 -- dbms_output.put_line('CONDITION 2: l_arr_to_day_of_week = ' || l_arr_to_day_of_week);
1416
1417 IF (l_diff_arr >= 7) THEN
1418 l_query := l_query || ' (s.frequency_type = ''WEEKLY'')';
1419 ELSE
1420 l_arr_date_from_time := to_char(l_arr_date_from, 'HH24:MI');
1421 l_arr_date_to_time := to_char(l_arr_date_to, 'HH24:MI');
1422
1423 IF (l_arr_to_day_of_week >= l_arr_from_day_of_week) THEN
1424
1425 l_counter2 := 1;
1426 FOR l_counter IN l_arr_from_day_of_week..l_arr_to_day_of_week LOOP
1427 l_arr_date_array(l_counter2) := l_counter;
1428 l_counter2 := l_counter2 + 1;
1429 END LOOP;
1430
1431 ELSE
1432
1433 l_counter2 := 1;
1434 FOR l_counter IN l_arr_from_day_of_week..l_arr_to_day_of_week + 7 LOOP
1435 l_mod_var := mod(l_counter, 7);
1436 IF (l_mod_var = 0) THEN
1437 l_mod_var := 7;
1438 END IF;
1439 l_arr_date_array(l_counter2) := l_mod_var;
1440 l_counter2 := l_counter2 + 1;
1441 END LOOP;
1442
1443 END IF;
1444
1445 IF (l_arr_date_array.COUNT > 0) THEN
1446 l_query := l_query || ' ( ';
1447 END IF;
1448
1449 IF (l_arr_date_array.COUNT = 1) THEN
1450 l_query := l_query || ' s.frequency_arrival like :' || g_bind_counter_global;
1451 Process_Bind_Var(x_bindvars, '%' || l_arr_date_array(1) || '%', g_varchar2, g_type3);
1452
1453 l_query := l_query || ' and to_date(arrival_time, ''hh24:mi'') >= to_date(:' ||
1454 g_bind_counter_global || ', ''hh24:mi'')';
1455 Process_Bind_Var(x_bindvars, l_arr_date_from_time, g_varchar2, g_type3);
1456
1457 l_query := l_query || ' and to_date(arrival_time, ''hh24:mi'') <= to_date(:' ||
1458 g_bind_counter_global || ', ''hh24:mi'')';
1459 Process_Bind_Var(x_bindvars, l_arr_date_to_time, g_varchar2, g_type3);
1460
1461 ELSE
1462
1463 FOR l_counter IN 1..l_arr_date_array.COUNT LOOP
1464
1465 l_query := l_query || ' ( s.frequency_arrival like :' || g_bind_counter_global;
1466 Process_Bind_Var(x_bindvars, '%' || l_arr_date_array(l_counter) || '%', g_varchar2, g_type3);
1467
1468 IF (l_counter = 1) THEN -- first frequency
1469 l_query := l_query || ' and to_date(arrival_time, ''hh24:mi'') >= to_date(:' ||
1470 g_bind_counter_global || ', ''hh24:mi'')';
1471 Process_Bind_Var(x_bindvars, l_arr_date_from_time, g_varchar2, g_type3);
1472 END IF;
1473
1477 Process_Bind_Var(x_bindvars, l_arr_date_to_time, g_varchar2, g_type3);
1474 IF (l_counter = l_arr_date_array.COUNT) THEN -- last frequency
1475 l_query := l_query || ' and to_date(arrival_time, ''hh24:mi'') <= to_date(:' ||
1476 g_bind_counter_global || ', ''hh24:mi'')';
1478 END IF;
1479
1480 IF (l_counter <> 1 AND l_counter <> l_arr_date_array.COUNT) THEN
1481 l_query := l_query || ')';
1482 END IF;
1483
1484 IF (l_counter < l_arr_date_array.COUNT) THEN
1485 l_query := l_query || ' OR ';
1486 END IF;
1487
1488 END LOOP;
1489
1490 l_query := l_query || ' ) ) ';
1491
1492 END IF;
1493
1494 IF (l_arr_date_array.COUNT > 0) THEN
1495 l_query := l_query || ' ) ';
1496 END IF;
1497
1498 END IF;
1499
1500 ELSE -- both arrival dates are null
1501
1502 l_query := l_query || ' ) '; -- added 11/15/02 12:35am
1503
1504 END IF;
1505 -- end condition 2
1506 -- dbms_output.put_line('end condition 2');
1507
1508 IF (l_condition_one = 'N') THEN -- if not condition 1
1509 l_query := l_query || ' ) ';
1510 END IF;
1511
1512 l_query := l_query || ' ) '; -- added 11/15/02 11:30am to close the entire clause starting with daily schedules and before figuring out the arrival date indicator stuff
1513
1514 -- condition 3
1515 -- case A] from dep, to dep, from arr, to arr are all specified (full range)
1516 IF (p_dep_date_from is not null AND p_dep_date_to is not null AND
1517 p_arr_date_from is not null AND p_arr_date_to is not null) THEN
1518
1519 IF (l_dep_from_day_of_week > l_dep_to_day_of_week) THEN
1520
1521 l_dep_to_day_of_week := l_dep_to_day_of_week + 7;
1522 l_arr_from_day_of_week := l_arr_from_day_of_week + 7;
1523 l_arr_to_day_of_week := l_arr_to_day_of_week + 7;
1524 l_tag := 'Y';
1525
1526 END IF;
1527
1528 IF (l_arr_from_day_of_week > l_dep_to_day_of_week) THEN
1529
1530 IF (l_arr_to_day_of_week < l_arr_from_day_of_week) THEN
1531 l_arr_to_day_of_week := l_arr_to_day_of_week + 7;
1532 END IF;
1533
1534 ELSE
1535
1536 IF (l_arr_from_day_of_week <> l_dep_to_day_of_week OR
1537 l_tag = 'Y') THEN
1538
1539 l_arr_from_day_of_week := l_arr_from_day_of_week + 7;
1540 l_arr_to_day_of_week := l_arr_to_day_of_week + 7;
1541
1542 END IF;
1543
1544 END IF;
1545
1546 IF (l_arr_from_day_of_week > l_arr_to_day_of_week) THEN
1547
1548 l_arr_to_day_of_week := l_arr_to_day_of_week + 7;
1549
1550 END IF;
1551
1552 l_date_range_from := l_arr_from_day_of_week - l_dep_to_day_of_week;
1553 l_date_range_to := l_arr_to_day_of_week - l_dep_from_day_of_week;
1554
1555 l_query := l_query || ' AND to_number(nvl(s.arrival_date_indicator, 0)) <= ceil(:' || g_bind_counter_global;
1556 Process_Bind_Var(x_bindvars, to_char(p_arr_date_to, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1557 l_query := l_query || ' - :' || g_bind_counter_global || ')';
1558 Process_Bind_Var(x_bindvars, to_char(p_dep_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1559
1560 IF (p_dep_date_from <> p_dep_date_to OR
1561 p_arr_date_from <> p_arr_date_to) THEN
1562
1563 l_query := l_query || ' AND to_number(nvl(s.arrival_date_indicator, 0)) >= ceil(:' || g_bind_counter_global;
1564 Process_Bind_Var(x_bindvars, to_char(p_arr_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1565 l_query := l_query || ' - :' || g_bind_counter_global || ')';
1566 Process_Bind_Var(x_bindvars, to_char(p_dep_date_to, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1567
1568 END IF;
1569 -- dbms_output.put_line('end condition 3A');
1570 l_query := l_query || ')'; -- maybe should comment out? 11/15/02
1571
1572 END IF; -- end condition 3 case A]
1573 -- case B] from dep, to dep, from arr are specified, to arr is null
1574 IF (p_dep_date_from is not null AND p_dep_date_to is not null AND
1575 p_arr_date_from is not null AND p_arr_date_to is null) THEN
1576
1577 IF (p_arr_date_from > p_dep_date_from AND
1578 l_arr_from_day_of_week < l_dep_from_day_of_week) THEN
1579 l_date_range_from := l_arr_from_day_of_week - l_dep_from_day_of_week + 7;
1580 ELSE
1581 l_date_range_from := l_arr_from_day_of_week - l_dep_from_day_of_week;
1582 END IF;
1583
1584 l_query := l_query || ' AND mod(to_number(nvl(s.arrival_date_indicator, :' || g_bind_counter_global;
1585 Process_Bind_Var(x_bindvars, l_date_range_from, g_number, g_type3);
1586 l_query := l_query || ')), 7) >= :' || g_bind_counter_global || ' ';
1587 Process_Bind_Var(x_bindvars, l_date_range_from, g_number, g_type3);
1588 -- dbms_output.put_line('end condition 3B');
1589
1590 END IF; -- end condition 3 case B]
1591 -- condition 3 case C] from dep, from arr, to arr are specified, to dep is null
1595
1592 IF (p_dep_date_from is not null AND p_dep_date_to is null AND
1593 p_arr_date_from is not null AND p_arr_date_to is not null) THEN
1594 -- dbms_output.put_line('IN CONDITION 3C');
1596 IF (p_dep_date_from <= p_arr_date_to) THEN
1597 l_date_range_from := -1;
1598 l_date_range_to := mod(l_arr_to_day_of_week - l_dep_from_day_of_week + 7, 7);
1599 ELSE
1600 l_date_range_from := -2;
1601 l_date_range_to := l_date_range_from;
1602 END IF;
1603
1604 l_query := l_query || ' AND mod(to_number(nvl(s.arrival_date_indicator, :' || g_bind_counter_global;
1605 Process_Bind_Var(x_bindvars, l_date_range_from, g_number, g_type3);
1606 l_query := l_query || ')), 7) between :' || g_bind_counter_global;
1607 Process_Bind_Var(x_bindvars, l_date_range_from, g_number, g_type3);
1608 l_query := l_query || ' and :' || g_bind_counter_global || ' ';
1609 Process_Bind_Var(x_bindvars, l_date_range_to, g_number, g_type3);
1610 -- dbms_output.put_line('end condition 3C');
1611
1612 END IF; -- end condition 3 case C]
1613
1614 -- condition 3 case D] from dep, from arr are not null, to dep and to arr are null (no ranges)
1615 IF (p_dep_date_from is not null AND p_dep_date_to is null AND
1616 p_arr_date_from is not null AND p_arr_date_to is null) THEN
1617 -- dbms_output.put_line('IN CONDITION 3D');
1618
1619 IF (l_arr_from_day_of_week < l_dep_from_day_of_week) THEN
1620 l_date_range_from := -2;
1621 ELSE
1622 l_date_range_from := mod(l_arr_from_day_of_week - l_dep_from_day_of_week + 7, 7);
1623 END IF;
1624
1625 l_query := l_query || ' AND to_number(nvl(s.arrival_date_indicator, 0)) >= ceil(:' || g_bind_counter_global;
1626 Process_Bind_Var(x_bindvars, to_char(p_arr_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1627 l_query := l_query || ' - :' || g_bind_counter_global || ')';
1628 Process_Bind_Var(x_bindvars, to_char(p_dep_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1629 -- dbms_output.put_line('end condition 3D');
1630
1631 END IF; -- end condition 3 case D]
1632
1633 -- condition 3 case E]
1634 IF (p_dep_date_from is null AND p_dep_date_to is not null AND
1635 p_arr_date_from is not null AND p_arr_date_to is not null) THEN -- dep from is null
1636
1637 IF (p_dep_date_to <= p_arr_date_from AND
1638 p_dep_date_to <= p_arr_date_to) THEN
1639 l_date_range_from := mod(l_arr_from_day_of_week - l_dep_to_day_of_week + 7, 7);
1640 l_date_range_to := 200; -- doesn't matter, as long as it is big
1641 ELSIF (p_dep_date_to >= p_arr_date_from AND
1642 p_dep_date_to <= p_arr_date_to) THEN
1643 l_date_range_from := -1;
1644 l_date_range_to := mod(l_arr_to_day_of_week - l_dep_to_day_of_week + 7, 7);
1645 ELSE
1646 l_date_range_from := -2;
1647 l_date_range_to := l_date_range_from;
1648 END IF;
1649
1650 l_query := l_query || ' AND mod(to_number(nvl(s.arrival_date_indicator, :' || g_bind_counter_global;
1651 Process_Bind_Var(x_bindvars, l_date_range_from, g_number, g_type3);
1652 l_query := l_query || ')), 7) between :' || g_bind_counter_global;
1653 Process_Bind_Var(x_bindvars, l_date_range_from, g_number, g_type3);
1654 l_query := l_query || ' and :' || g_bind_counter_global || ' ';
1655 Process_Bind_Var(x_bindvars, l_date_range_to, g_number, g_type3);
1656 -- dbms_output.put_line('end condition 3E');
1657 END IF; -- end condition 3 case E]
1658
1659 -- condition 3 case F]
1660 IF (p_dep_date_from is not null AND p_dep_date_to is not null AND
1661 p_arr_date_from is null AND p_arr_date_to is not null) THEN
1662
1663 IF (p_arr_date_to >= p_dep_date_from AND
1664 p_arr_date_to >= p_dep_date_to) THEN
1665 l_date_range_from := mod(l_arr_to_day_of_week - l_dep_to_day_of_week + 7, 7);
1666 l_date_range_to := mod(l_arr_to_day_of_week - l_dep_from_day_of_week + 7, 7);
1667 ELSIF (p_arr_date_to >= p_dep_date_from AND
1668 p_arr_date_to <= p_dep_date_to) THEN
1669 l_date_range_from := -1;
1670 l_date_range_to := mod(l_arr_to_day_of_week - l_dep_from_day_of_week + 7, 7);
1671 ELSE
1672 l_date_range_from := -2;
1673 l_date_range_to := l_date_range_from;
1674 END IF;
1675
1676 l_query := l_query || ' AND mod(to_number(nvl(s.arrival_date_indicator, :' || g_bind_counter_global;
1677 Process_Bind_Var(x_bindvars, l_date_range_from, g_number, g_type3);
1678 l_query := l_query || ')), 7) between :' || g_bind_counter_global;
1679 Process_Bind_Var(x_bindvars, l_date_range_from, g_number, g_type3);
1680 l_query := l_query || ' and :' || g_bind_counter_global || ' ';
1681 Process_Bind_Var(x_bindvars, l_date_range_to, g_number, g_type3);
1682
1683 -- dbms_output.put_line('end condition 3F');
1684 END IF; -- end condition 3 case F]
1685
1686 -- condition 3 case G]
1687 IF (p_dep_date_from is not null AND p_dep_date_to is null AND
1688 p_arr_date_from is null AND p_arr_date_to is not null) THEN
1689
1690 IF (l_arr_to_day_of_week < l_dep_from_day_of_week) THEN
1691 l_date_range_from := -2;
1692 ELSE
1696 l_query := l_query || ' AND to_number(nvl(s.arrival_date_indicator, 0)) <= ceil(:' || g_bind_counter_global;
1693 l_date_range_from := mod(l_arr_to_day_of_week - l_dep_from_day_of_week + 7, 7);
1694 END IF;
1695
1697 Process_Bind_Var(x_bindvars, to_char(p_arr_date_to, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1698 l_query := l_query || ' - :' || g_bind_counter_global || ')';
1699 Process_Bind_Var(x_bindvars, to_char(p_dep_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1700
1701 END IF;
1702
1703 -- condition 3 case H]
1704 IF (p_dep_date_from is null AND p_dep_date_to is not null AND
1705 p_arr_date_from is not null AND p_arr_date_to is null) THEN
1706
1707 IF (l_arr_from_day_of_week < l_dep_to_day_of_week) THEN
1708 l_date_range_from := -2;
1709 ELSE
1710 l_date_range_from := mod(l_arr_from_day_of_week - l_dep_to_day_of_week + 7, 7);
1711 END IF;
1712
1713 l_query := l_query || ' AND to_number(nvl(s.arrival_date_indicator, 0)) >= ceil(:' || g_bind_counter_global;
1714 Process_Bind_Var(x_bindvars, to_char(p_arr_date_from, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1715 l_query := l_query || ' - :' || g_bind_counter_global || ')';
1716 Process_Bind_Var(x_bindvars, to_char(p_dep_date_to, 'mm-dd-yyyy hh24:mi'), g_date, g_type3);
1717
1718 END IF;
1719
1720 ELSE
1721 -- dbms_output.put_line('putting frequency_type = weekly');
1722 l_query := l_query || ' OR s.frequency_type = ''WEEKLY''';
1723
1724 END IF;
1725
1726
1727 l_query := l_query || ')';
1728
1729 x_query := l_query;
1730
1731 --
1732 -- Debug Statements
1733 --
1734 IF l_debug_on THEN
1735 WSH_DEBUG_SV.pop(l_module_name);
1736 END IF;
1737 --
1738 END Create_Schedule_Clause;
1739
1740
1741 -- ----------------------------------------------------------------
1742 -- Name: Create_Rate_Chart_Query
1743 -- Type: Procedure
1744 --
1745 -- Description: This procedure takes origin, destination info
1746 -- and the carrier name, and creates the query
1747 -- that will obtain a list of rate chart ids
1748 -- which match the criteria.
1749 -- Returns the bind variable array and query.
1750 --
1751 -- -----------------------------------------------------------------
1752 PROCEDURE Create_Rate_Chart_Query(p_parent_origins IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
1753 p_parent_destinations IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
1754 p_origin_zip_request IN VARCHAR2,
1755 p_dest_zip_request IN VARCHAR2,
1756 p_carrier_name IN VARCHAR2,
1757 p_tariff_name IN VARCHAR2,
1758 x_query OUT NOCOPY VARCHAR2,
1759 x_bindvars OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars) IS
1760
1761 l_query VARCHAR2(5000);
1762 l_reg_query VARCHAR2(4000);
1763
1764 l_bindvars FTE_LANE_SEARCH_QUERY_GEN.bindvars;
1765
1766 --
1767 l_debug_on BOOLEAN;
1768 --
1769 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_RATE_CHART_QUERY';
1770 --
1771
1772 BEGIN
1773
1774 --
1775 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1776 --
1777 IF l_debug_on IS NULL
1778 THEN
1779 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1780 END IF;
1781 --
1782 --
1783 -- Debug Statements
1784 --
1785 IF l_debug_on THEN
1786 WSH_DEBUG_SV.push(l_module_name);
1787 END IF;
1788 --
1789
1790 g_bind_counter_global := 1;
1791 --g_bind_counter_common := 1;
1792 g_bind_counter1 := 1;
1793
1794 -- Changes for J:Multiple Rate Charts
1795 --l_query := 'select distinct l.pricelist_id ';
1796 l_query := 'select distinct lrc.list_header_id ';
1797
1798 WSH_DEBUG_SV.log(l_module_name,'p_parent_origins Coiunt',p_parent_origins.COUNT);
1799 WSH_DEBUG_SV.log(l_module_name,'p_parent_destinations Count', p_parent_destinations.COUNT);
1800 WSH_DEBUG_SV.log(l_module_name,'p_tariff_name', p_tariff_name);
1801 IF (p_parent_origins is not null OR p_parent_destinations is not null)
1802 AND (p_parent_origins.COUNT > 0 OR p_parent_destinations.COUNT > 0) THEN
1803
1804 -- Changes for J:Multiple Rate Charts
1805 /*
1806 l_query := l_query || 'from fte_lanes l, hz_parties hz, wsh_zone_regions zr, ' ||
1807 'wsh_zone_regions pd where l.pricelist_id is not null ' ||
1808 'AND l.origin_id = zr.parent_region_id ' ||
1809 'AND l.destination_id = pd.parent_region_id ' ||
1810 'AND l.carrier_id = hz.party_id ' ||
1811 'and upper(hz.party_name) like upper(:' || g_bind_counter_global || ')';
1812 */
1813 l_query := l_query || 'from fte_lanes l, hz_parties hz, wsh_zone_regions zr, ' ||
1814 'wsh_zone_regions pd, fte_lane_rate_charts lrc where l.lane_id = lrc.lane_id ' ||
1815 'AND l.origin_id = zr.parent_region_id ' ||
1816 'AND l.destination_id = pd.parent_region_id ' ||
1820 Process_Bind_Var(l_bindvars, p_carrier_name || '%', g_varchar2, g_type1);
1817 'AND l.carrier_id = hz.party_id ' ||
1818 'and upper(hz.party_name) like upper(:' || g_bind_counter_global || ')';
1819
1821 /*IF (p_tariff_name is not null) THEN
1822 l_query := l_query ||' AND l.tariff_name like :' || g_bind_counter_global;
1823 Process_Bind_Var(l_bindvars, p_tariff_name || '%', g_varchar2, g_type1);
1824 END IF;*/
1825 Create_Regions_Clause(p_parent_origins => p_parent_origins,
1826 p_parent_destinations => p_parent_destinations,
1827 p_origin_zip_request => p_origin_zip_request,
1828 p_dest_zip_request => p_dest_zip_request,
1829 x_query => l_reg_query,
1830 x_bindvars => l_bindvars);
1831
1832 l_query := l_query || l_reg_query;
1833
1834 ELSE -- only have carrier
1835 -- Changes for J:Multiple Rate Charts
1836 /*
1837 l_query := l_query || 'from fte_lanes l, hz_parties hz ' ||
1838 'where pricelist_id is not null and upper(party_name) ' ||
1839 'like upper(:' || g_bind_counter_global || ')';
1840 */
1841 l_query := l_query || 'from fte_lanes l, hz_parties hz, fte_lane_rate_charts lrc ' ||
1842 'where l.lane_id = lrc.lane_id and upper(hz.party_name) ' ||
1843 'like upper(:' || g_bind_counter_global || ')';
1844
1845 Process_Bind_Var(l_bindvars, p_carrier_name || '%', g_varchar2, g_type1);
1846 /*IF (p_tariff_name is not null) THEN
1847 l_query := l_query ||' AND l.tariff_name like :' || g_bind_counter_global;
1848 Process_Bind_Var(l_bindvars, p_tariff_name || '%', g_varchar2, g_type1);
1849 END IF;*/
1850 END IF;
1851
1852
1853
1854 x_query := l_query;
1855 x_bindvars := l_bindvars;
1856
1857 --
1858 -- Debug Statements
1859 --
1860 IF l_debug_on THEN
1861 WSH_DEBUG_SV.pop(l_module_name);
1862 END IF;
1863 --
1864 END Create_Rate_Chart_Query;
1865
1866
1867 END FTE_LANE_SEARCH_QUERY_GEN;