[Home] [Help]
PACKAGE BODY: APPS.MST_PQ_WORKS
Source
1 PACKAGE BODY MST_PQ_WORKS AS
2 /* $Header: MSTPQWKB.pls 120.1 2005/06/13 03:47:24 appldev $ */
3
4 --g_select_all CONSTANT VARCHAR2(6) := 'All'; --'<#~$>'; -- need to modify.
5 g_select_all VARCHAR2(30);
6 g_New_query_id NUMBER;
7
8 FUNCTION execute_dyn_sql(P_STMT IN VARCHAR2,P_BIND_VAR IN VARCHAR2) RETURN NUMBER IS
9 BEGIN
10 --KSA_DEBUG(SYSDATE,p_stmt,'dyn sql');
11 IF P_BIND_VAR IS NOT NULL THEN
12 EXECUTE IMMEDIATE(P_STMT) USING P_BIND_VAR;
13 ELSE
14 EXECUTE IMMEDIATE(P_STMT);
15 END IF;
16 RETURN 1;
17 EXCEPTION
18 WHEN OTHERS THEN
19 --KSA_DEBUG(SYSDATE,sqlerrm(sqlcode),'dyn sql');
20 RETURN 0;
21 END execute_dyn_sql;
22
23 PROCEDURE populate_result_table(errbuf OUT NOCOPY VARCHAR2,
24 retcode OUT NOCOPY NUMBER,
25 p_query_id IN NUMBER,
26 p_plan_id IN NUMBER ) IS
27 CURSOR CUR_QUERY IS
28 SELECT QUERY_TYPE , PUBLIC_FLAG,
29 AND_OR_FLAG , APPLIES_TO,
30 EXECUTE_FLAG
31 FROM MST_PERSONAL_QUERIES
32 WHERE QUERY_ID = P_QUERY_ID;
33
34 CURSOR cur_definition IS
35 SELECT msc.QUERY_ID , msc.FIELD_NAME ,
36 ms.FIELD_TYPE , msc.SEQUENCE ,
37 msc.FILTER_TYPE , msc.FIELD_VALUE_FROM,
38 msc.FIELD_VALUE_TO, msc.MULTI_SELECT ,
39 msc.ACTIVE_FLAG , msc.CREATED_BY
40 FROM mst_selection_criteria msc,
41 mst_selection ms
42 WHERE msc.query_id = p_query_id
43 AND ms.field_name = msc.field_name
44 AND msc.active_flag = 1
45 ORDER BY msc.SEQUENCE;
46
47 l_rec_query cur_query%ROWTYPE;
48 l_rec_definition cur_definition%ROWTYPE;
49 l_field_type_temp NUMBER;
50 l_delim CONSTANT VARCHAR2(1) := ',';
51 l_separator CONSTANT VARCHAR2(1):= ';';
52 l_where_str1 VARCHAR2(10000); --VARCHAR2(4000);
53 l_where_str2 VARCHAR2(10000); --VARCHAR2(3000);
54 l_where_str3 VARCHAR2(10000); --VARCHAR2(2000);
55
56 g_plan_criteria VARCHAR2(200);
57 l_plan_criteria VARCHAR2(200);
58 l_cm_criteria VARCHAR2(200);
59 l_mode_of_transport_temp VARCHAR2(200);
60 l_multi_select_temp NUMBER;
61 l_fetch_required BOOLEAN ;
62 l_changed CONSTANT VARCHAR2(4):='@<~#';
63
64 l_user NUMBER ; --:= TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
65 l_insert_str VARCHAR2(200) ; --:= 'INSERT INTO MST_PERSONAL_QUERY_RESULTS ';
66 l_insert_Col VARCHAR2(200) ;
67 l_select_str VARCHAR2(2000);
68 l_executed NUMBER ;
69 l_update_str VARCHAR2(500);
70 l_delete_str VARCHAR2(500);
71 execution_failed EXCEPTION;
72 related_field_notfound EXCEPTION;
73 BEGIN
74 l_fetch_required := TRUE;
75 l_user := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
76 l_insert_str := 'INSERT INTO MST_PERSONAL_QUERY_RESULTS ';
77 BEGIN
78 l_delete_str := 'DELETE MST_PERSONAL_QUERY_RESULTS WHERE QUERY_ID = :P_QUERY_ID AND PLAN_ID=:P_PLAN_ID';
79 EXECUTE IMMEDIATE(l_delete_str) USING p_query_id,p_plan_id;
80 l_update_str := 'UPDATE MST_PERSONAL_QUERIES SET EXECUTE_FLAG = 2 WHERE QUERY_ID = :P_QUERY_ID';
81 l_executed:= execute_dyn_sql(l_update_str, p_query_id);
82 EXCEPTION
83 WHEN no_data_Found THEN
84 NULL;
85 END;
86 --l_executed:= execute_dyn_sql(l_delete_str,p_query_id||','||p_plan_id);
87 OPEN cur_query;
88 FETCH cur_query INTO l_rec_query;
89 CLOSE cur_query;
90
91 IF l_rec_query.query_type IN ( 1, 2, 3, 4, 5 ) THEN -- loads,CM, Orders, exceptions, rules
92 IF l_rec_query.query_type = 1 OR
93 ( l_rec_query.query_type = 5 AND l_rec_query.applies_to = 1) THEN -- loads
94 l_insert_Col := '(QUERY_ID, PLAN_ID, TRIP_ID,CREATED_BY, CREATION_DATE) ';
95 l_select_str := 'SELECT DISTINCT '||p_query_id||',PLAN_ID,TRIP_ID,'|| l_user||', SYSDATE FROM MST_PQ_LOAD_DETAILS_V ';
96 ELSIF l_rec_query.query_type = 2 OR
97 ( l_rec_query.query_type = 5 AND l_rec_query.applies_to = 3) THEN -- CM
98 l_insert_Col := '(QUERY_ID, PLAN_ID, CONTINUOUS_MOVE_ID,CREATED_BY, CREATION_DATE) ';
99 l_select_str := 'SELECT DISTINCT '||p_query_id||',PLAN_ID,CONTINUOUS_MOVE_ID,'|| l_user||', SYSDATE FROM MST_PQ_CM_DETAILS_V ';
100 ELSIF l_rec_query.query_type = 3 OR
101 ( l_rec_query.query_type = 5 AND l_rec_query.applies_to = 2) THEN -- Orders
102 l_insert_Col := '(QUERY_ID, PLAN_ID, SOURCE_CODE,SOURCE_HEADER_NUMBER,CREATED_BY, CREATION_DATE) ';
103 l_select_str := 'SELECT DISTINCT '||p_query_id||',PLAN_ID,SOURCE_CODE,SOURCE_HEADER_NUMBER,'|| l_user||', SYSDATE FROM MST_PQ_ORDER_DETAILS_V ';
104 ELSIF l_rec_query.query_type = 4 THEN -- Exceptions
105 l_insert_Col := '(QUERY_ID, PLAN_ID, EXCEPTION_ID,CREATED_BY, CREATION_DATE) ';
106 l_select_str := 'SELECT DISTINCT '||p_query_id||',PLAN_ID,EXCEPTION_ID,'|| l_user||', SYSDATE FROM MST_PQ_EXCEP_DETAILS_V ';
107 ELSIF l_rec_query.query_type = 5 AND l_rec_query.applies_to = 4 THEN -- Deliveries
108 l_insert_Col := '(QUERY_ID, PLAN_ID, DELIVERY_ID, CREATED_BY, CREATION_DATE) ';
109 l_select_str := 'SELECT DISTINCT '||p_query_id||',PLAN_ID,DELIVERY_ID,'|| l_user||', SYSDATE FROM MST_PQ_UA_DEL_DETAILS_V ';
110 END IF;
111 OPEN cur_definition;
112 LOOP
113 l_field_type_temp := NULL;
114 DECLARE
115 skip_condition EXCEPTION;
116 l_temp_str VARCHAR2(30);
117 l_where_str4 VARCHAR2(10000); --VARCHAR2(2000);
118 l_where_str5 VARCHAR2(10000);
119 BEGIN
120 IF l_fetch_required THEN
121 FETCH cur_definition INTO l_rec_definition;
122 ELSE
123 l_fetch_required := TRUE;
124 END IF;
125 --EXIT WHEN cur_definition%NOTFOUND;
126 --KSA_DEBUG(SYSDATE,'inside loop ','dyn sql');
127 IF cur_definition%NOTFOUND THEN
128 --KSA_DEBUG(SYSDATE,'inside loop -l_where_str1 '||l_where_str1,'dyn sql');
129 IF l_where_str1 IS NOT NULL THEN
130 l_where_str1 :=l_where_str1 ||')';
131 END IF;
132 EXIT;
133 END IF;
134 IF l_rec_definition.FILTER_TYPE IN(1,2,3) THEN -- =,not,in
135 l_where_str2 := l_rec_definition.FIELD_NAME;
136 IF l_rec_query.query_type = 4 AND
137 l_rec_definition.FIELD_NAME = 'MODE_OF_TRANSPORT' THEN
138 IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
139 l_where_str3 := '('''||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,''''||l_delim||'''')||''')';
140 l_where_str2 := '('||l_where_str2 ||' IN '||l_where_str3;
141 ELSIF l_rec_definition.MULTI_SELECT = 2
142 AND l_rec_definition.filter_type = 1 THEN -- NO
143 l_where_str3 := ''''||l_rec_definition.FIELD_VALUE_FROM||'''';
144 l_where_str2 := '('||l_where_str2||' = '||l_where_str3;
145 ELSE
146 l_where_str2 := '('||l_where_str2 ||' IS NULL';
147 END IF;
148 FETCH cur_definition INTO l_rec_definition;
149 IF cur_definition%FOUND AND l_rec_definition.FIELD_NAME IN ('DELIVERY_ID', 'CONTINUOUS_MOVE_ID') THEN
150 l_where_str2 := l_where_str2||' OR '||l_rec_definition.FIELD_NAME;
151 IF l_rec_definition.FIELD_VALUE_FROM = 2 THEN
152 l_where_str2 := l_where_str2 ||' IS NULL';
153 ELSIF l_rec_definition.FIELD_VALUE_FROM = 1 THEN
154 l_where_str2 := l_where_str2 ||' IS NOT NULL';
155 END IF;
156
157 FETCH cur_definition INTO l_rec_definition;
158 IF cur_definition%FOUND AND l_rec_definition.FIELD_NAME IN ('DELIVERY_ID', 'CONTINUOUS_MOVE_ID') THEN
159 l_where_str2 := l_where_str2||' OR '||l_rec_definition.FIELD_NAME;
160 IF l_rec_definition.FIELD_VALUE_FROM = 2 THEN
161 l_where_str2 := l_where_str2 ||' IS NULL'||')';
162 ELSIF l_rec_definition.FIELD_VALUE_FROM = 1 THEN
163 l_where_str2 := l_where_str2 ||' IS NOT NULL'||')';
164 END IF;
165 ELSE
166 l_where_str2 := l_where_str2 ||')';
167 l_fetch_required := FALSE;
168 END IF;
169 ELSE
170 l_where_str2 := l_where_str2 ||')';
171 l_fetch_required := FALSE;
172 END IF;
173 /***********************************/
174 ELSIF l_rec_query.query_type = 1 AND
175 l_rec_definition.FIELD_NAME = 'TL_TRIP_NUMBER' THEN
176 l_where_str2 := 'TRIP_NUMBER';
177 IF l_plan_criteria IS NOT NULL AND l_plan_criteria <> l_changed THEN
178 l_where_str1:= l_plan_criteria; -- Very important line @@@
179 l_plan_criteria := l_changed;
180 ELSIF g_plan_criteria IS NOT NULL AND g_plan_criteria <> l_changed THEN
181 l_where_str1:= g_plan_criteria; -- Very important line @@@
182 g_plan_criteria := l_changed;
183 l_plan_criteria := l_changed;
184 END IF;
185 l_temp_str := l_where_str2;
186 IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
187 l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
188 l_where_str2 := l_where_str2 ||' IN '||l_where_str3;
189 ELSIF l_rec_definition.MULTI_SELECT = 2
190 AND l_rec_definition.filter_type = 1 THEN -- NO
191 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
192 l_where_str2 := l_where_str2||' = '||l_where_str3;
193 END IF;
194 IF l_mode_of_transport_temp IS NOT NULL THEN
195 IF l_cm_criteria IS NOT NULL THEN
196 l_where_str2 := '('||l_where_str2||' AND '||l_cm_criteria||')';
197 END IF;
198 IF l_multi_select_temp = 1 THEN
199 l_where_str2 := '((' ||l_where_str2 || ' AND MODE_OF_TRANSPORT = ''TRUCK'')'||
200 ' OR MODE_OF_TRANSPORT IN '||l_mode_of_transport_temp||')';
201 ELSIF l_multi_select_temp = 2 THEN
202 l_where_str2 := '((' ||l_where_str2 || ' AND MODE_OF_TRANSPORT = ''TRUCK'')'||
203 ' OR MODE_OF_TRANSPORT = '||l_mode_of_transport_temp||')';
204 END IF;
205 ELSE
206 IF l_cm_criteria IS NOT NULL THEN
207 l_where_str2 := '('||l_where_str2||' AND '||l_cm_criteria||')';
208 END IF;
209 l_where_str2 := '(' ||l_where_str2 || ' AND MODE_OF_TRANSPORT = ''TRUCK'')';
210 END IF;
211 FETCH cur_definition INTO l_rec_definition;
212 IF l_rec_definition.FIELD_NAME = 'LTL_TRIP_NUMBER' THEN
213 IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
214 l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
215 l_where_str4 := l_temp_str ||' IN '||l_where_str3;
216 ELSIF l_rec_definition.MULTI_SELECT = 2 THEN
217 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
218 l_where_str4 := l_temp_str||' = '||l_where_str3;
219 END IF;
220 l_where_str4 := '(' ||l_where_str4 || ' AND MODE_OF_TRANSPORT = ''LTL'')';
221 l_where_str2 := '('||l_where_str2 ||' OR '|| l_where_str4||')';
222 FETCH cur_definition INTO l_rec_definition;
223 IF l_rec_definition.FIELD_NAME = 'PARCEL_TRIP_NUMBER' THEN
224 IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
225 l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
226 l_where_str4 := l_temp_str ||' IN '||l_where_str3;
227 ELSIF l_rec_definition.MULTI_SELECT = 2 THEN
228 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
229 l_where_str4 := l_temp_str||' = '||l_where_str3;
230 END IF;
231 l_where_str4 := '(' ||l_where_str4 || ' AND MODE_OF_TRANSPORT = ''PARCEL'')';
232 l_where_str2 := '('||l_where_str2 ||' OR '|| l_where_str4||')';
233 ELSE
234 l_fetch_required := FALSE;
238 l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
235 END IF;
236 ELSIF l_rec_definition.FIELD_NAME = 'PARCEL_TRIP_NUMBER' THEN
237 IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
239 l_where_str4 := l_temp_str ||' IN '||l_where_str3;
240 ELSIF l_rec_definition.MULTI_SELECT = 2 THEN
241 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
242 l_where_str4 := l_temp_str||' = '||l_where_str3;
243 END IF;
244 l_where_str4 := '(' ||l_where_str4 || ' AND MODE_OF_TRANSPORT = ''PARCEL'')';
245 l_where_str2 := '('||l_where_str2 ||' OR '|| l_where_str4||')';
246 ELSE
247 l_fetch_required := FALSE;
248 END IF;
249 ELSIF l_rec_query.query_type = 1 AND
250 l_rec_definition.FIELD_NAME = 'LTL_TRIP_NUMBER' THEN
251 l_where_str2 := 'TRIP_NUMBER';
252 IF l_plan_criteria IS NOT NULL AND l_plan_criteria <> l_changed THEN
253 l_where_str1:= l_plan_criteria; -- Very important line @@@
254 l_plan_criteria := l_changed;
255 ELSIF g_plan_criteria IS NOT NULL AND g_plan_criteria <> l_changed THEN
256 l_where_str1:= g_plan_criteria; -- Very important line @@@
257 g_plan_criteria := l_changed;
258 l_plan_criteria := l_changed;
259 END IF;
260 IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
261 l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
262 l_where_str2 := l_where_str2 ||' IN '||l_where_str3;
263 ELSIF l_rec_definition.MULTI_SELECT = 2
264 AND l_rec_definition.filter_type = 1 THEN -- NO
265 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
266 l_where_str2 := l_where_str2||' = '||l_where_str3;
267 END IF;
268 IF l_mode_of_transport_temp IS NOT NULL THEN
269
270 IF l_multi_select_temp = 1 THEN
271 l_where_str2 := '((' ||l_where_str2 || ' AND MODE_OF_TRANSPORT = ''LTL'')'||
272 ' OR MODE_OF_TRANSPORT IN '||l_mode_of_transport_temp||')';
273 ELSIF l_multi_select_temp = 2 THEN
274 l_where_str2 := '((' ||l_where_str2 || ' AND MODE_OF_TRANSPORT = ''LTL'')'||
275 ' OR MODE_OF_TRANSPORT = '||l_mode_of_transport_temp||')';
276 END IF;
277 ELSE
278 l_where_str2 := '(' ||l_where_str2 || ' AND MODE_OF_TRANSPORT = ''LTL'')';
279 END IF;
280 IF l_cm_criteria IS NOT NULL THEN
281 l_where_str2 := '('||l_where_str2||' OR ( MODE_OF_TRANSPORT = ''TRUCK'''||
282 ' AND '||l_cm_criteria||'))';
283 END IF;
284 FETCH cur_definition INTO l_rec_definition;
285 IF l_rec_definition.FIELD_NAME = 'PARCEL_TRIP_NUMBER' THEN
286 IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
287 l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
288 l_where_str4 := l_temp_str ||' IN '||l_where_str3;
289 ELSIF l_rec_definition.MULTI_SELECT = 2 THEN
290 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
291 l_where_str4 := l_temp_str||' = '||l_where_str3;
292 END IF;
293 l_where_str4 := '(' ||l_where_str4 || ' AND MODE_OF_TRANSPORT = ''PARCEL'')';
294 l_where_str2 := '('||l_where_str2 ||' OR '|| l_where_str4||')';
295 ELSE
296 l_fetch_required := FALSE;
297 END IF;
298 ELSIF l_rec_query.query_type = 1 AND
299 l_rec_definition.FIELD_NAME = 'PARCEL_TRIP_NUMBER' THEN
300 l_where_str2 := 'TRIP_NUMBER';
301 IF l_plan_criteria IS NOT NULL AND l_plan_criteria <> l_changed THEN
302 l_where_str1:= l_plan_criteria; -- Very important line @@@
303 l_plan_criteria := l_changed;
304 ELSIF g_plan_criteria IS NOT NULL AND g_plan_criteria <> l_changed THEN
305 l_where_str1:= g_plan_criteria; -- Very important line @@@
306 g_plan_criteria := l_changed;
307 l_plan_criteria := l_changed;
308 END IF;
309 IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
310 l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
311 l_where_str2 := '('||l_where_str2 ||' IN '||l_where_str3;
315 l_where_str2 := l_where_str2||' = '||l_where_str3;
312 ELSIF l_rec_definition.MULTI_SELECT = 2
313 AND l_rec_definition.filter_type = 1 THEN -- NO
314 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
316 END IF;
317 IF l_mode_of_transport_temp IS NOT NULL THEN
318 IF l_multi_select_temp = 1 THEN
319 l_where_str2 := '((' ||l_where_str2 || ' AND MODE_OF_TRANSPORT = ''PARCEL'')'||
320 ' OR MODE_OF_TRANSPORT IN '||l_mode_of_transport_temp||')';
321 ELSIF l_multi_select_temp = 2 THEN
322 l_where_str2 := '((' ||l_where_str2 || ' AND MODE_OF_TRANSPORT = ''PARCEL'')'||
323 ' OR MODE_OF_TRANSPORT = '||l_mode_of_transport_temp||')';
324 END IF;
325 ELSE
326 l_where_str2 := '(' ||l_where_str2 || ' AND MODE_OF_TRANSPORT = ''PARCEL'')';
327 END IF;
328 IF l_cm_criteria IS NOT NULL THEN
329 l_where_str2 := '('||l_where_str2||' OR ( MODE_OF_TRANSPORT = ''TRUCK'''||
330 ' AND '||l_cm_criteria||'))';
331 END IF;
332 /***********************************/
333 ELSIF l_rec_query.query_type = 5 AND
334 l_rec_definition.FIELD_NAME ='MODE_OF_TRANSPORT' THEN
335 l_where_str3 := ''''||l_rec_definition.FIELD_VALUE_FROM||'''';
336 l_where_str2 := l_where_str2||' = '||l_where_str3;
337 --ELSIF l_rec_query.query_type = 5 AND
338 -- l_rec_definition.FIELD_NAME ='CONTINUOUS_MOVE_ID' THEN
339 /***********************************/
340 ELSIF l_rec_query.query_type = 1 AND
341 l_rec_definition.FIELD_NAME ='CONTINUOUS_MOVE_ID' THEN
342 IF l_rec_definition.FIELD_VALUE_FROM = 2 THEN
343 l_where_str2 := l_where_str2 ||' IS NULL';
344 ELSIF l_rec_definition.FIELD_VALUE_FROM = 1 THEN
345 l_where_str2 := l_where_str2 ||' IS NOT NULL';
346 END IF;
347 l_cm_criteria := l_where_str2;
348 /***********************************/
349 ELSIF l_rec_query.query_type = 5 AND
350 l_rec_definition.FIELD_NAME ='CONTINUOUS_MOVE_ID' THEN
351 IF l_rec_definition.FIELD_VALUE_FROM = 2 THEN
352 l_where_str2 := l_where_str2 ||' IS NULL';
353 ELSIF l_rec_definition.FIELD_VALUE_FROM = 1 THEN
354 l_where_str2 := l_where_str2 ||' IS NOT NULL';
355 END IF;
356 ELSE
357 IF l_rec_definition.FIELD_NAME = 'CUSTOMER_NAME' THEN
358 l_field_type_temp := 1;
359 l_where_str2 := 'CUSTOMER_ID';
360 ELSIF l_rec_definition.FIELD_NAME = 'SUPPLIER_NAME' THEN
361 l_field_type_temp := 1;
362 l_where_str2 := 'SUPPLIER_ID';
363 ELSIF l_rec_definition.FIELD_NAME = 'CARRIER_NAME' THEN
364 l_field_type_temp := 1;
365 l_where_str2 := 'CARRIER_ID';
366 ELSIF l_rec_definition.FIELD_NAME = 'ORIGIN_FACILITY' THEN
367 l_field_type_temp := 1;
368 l_where_str2 := 'ORIGIN_FACILITY_ID';
369 ELSIF l_rec_definition.FIELD_NAME = 'DESTINATION_FACILITY' THEN
370 l_field_type_temp := 1;
371 l_where_str2 := 'DESTINATION_FACILITY_ID';
372 ELSIF l_rec_definition.FIELD_NAME = 'ITEM_NAME' THEN
373 l_field_type_temp := 1;
374 l_where_str2 := 'INVENTORY_ITEM_ID';
375 ELSIF l_rec_definition.FIELD_NAME = 'FACILITY' THEN
376 --RAISE skip_condition; -- Need review latter.
377 l_where_str2 := 'ORIGIN_FACILITY_ID';
378 l_where_str4 := 'DESTINATION_FACILITY_ID';
379 END IF;
380 IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
381 IF l_rec_definition.FIELD_TYPE = 1 OR l_field_type_temp = 1 THEN -- NUMBER
382 l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
383 ELSIF l_rec_definition.FIELD_TYPE =2 THEN -- varchar2
384 l_where_str3 := '('''||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,''''||l_delim||'''')||''')';
385 END IF;
386 /**************************/
387 IF l_rec_query.query_type = 1 AND
388 l_rec_definition.FIELD_NAME = 'MODE_OF_TRANSPORT' THEN
389 IF l_cm_criteria IS NOT NULL THEN
390 l_where_str5 := '('||l_rec_definition.FIELD_NAME||' = ''TRUCK'' AND '|| l_cm_criteria ||')';
391 IF instr(l_where_str3, '''TRUCK'',') > 0 THEN
392 l_where_str3 := REPLACE(l_where_str3,'''TRUCK'',');
396 l_multi_select_temp := 1;
393 END IF;
394 END IF;
395 l_mode_of_transport_temp := l_where_str3;
397 END IF;
398 /**************************/
399 IF l_rec_definition.FILTER_TYPE = 2 THEN -- NOT
400 l_where_str2 := l_where_str2 ||' NOT IN '||l_where_str3;
401 IF l_where_str4 IS NOT NULL THEN
402 l_where_str4 := l_where_str4 ||' NOT IN '||l_where_str3;
403 END IF;
404 ELSE
405 l_where_str2 := l_where_str2 ||' IN '||l_where_str3;
406 IF l_where_str4 IS NOT NULL THEN
407 l_where_str4 := l_where_str4 ||' IN '||l_where_str3;
408 END IF;
409 END IF;
410 IF l_rec_query.query_type = 1
411 AND l_rec_definition.FIELD_NAME = 'MODE_OF_TRANSPORT'
412 AND l_where_str5 IS NOT NULL THEN
413 l_where_str1:= l_plan_criteria; -- Very important line @@@
414 g_plan_criteria := l_plan_criteria;
415 l_plan_criteria := l_changed ;
416 l_where_str2 := l_where_str2||' OR '|| l_where_str5;
417 END IF;
418 ELSIF l_rec_definition.MULTI_SELECT =2 THEN -- no
419 IF l_rec_definition.FIELD_TYPE = 1 OR l_field_type_temp = 1 THEN -- NUMBER
420 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
421 ELSIF l_rec_definition.FIELD_TYPE =2 THEN -- varchar2
422 l_where_str3 := ''''||l_rec_definition.FIELD_VALUE_FROM||'''';
423 END IF;
424 /**************************/
425 IF l_rec_query.query_type = 1 AND
426 l_rec_definition.FIELD_NAME = 'MODE_OF_TRANSPORT' THEN
427 IF l_cm_criteria IS NOT NULL THEN
428 l_where_str5 := '('||l_rec_definition.FIELD_NAME||' = ''TRUCK'' AND '|| l_cm_criteria ||')';
429 IF instr(l_where_str3, '''TRUCK''') > 0 THEN
430 l_where_str3 := REPLACE(l_where_str3,'TRUCK','TRUCK_CM'); -- Just to skip TRUCK condition
431 END IF;
432 END IF;
433 l_mode_of_transport_temp := l_where_str3;
434 l_multi_select_temp := 2;
435 END IF;
436 /**************************/
437 IF l_rec_definition.FILTER_TYPE = 2 THEN -- NOT
438 l_where_str2 := l_where_str2 ||' <> '||l_where_str3;
439 IF l_where_str4 IS NOT NULL THEN
440 l_where_str4 := l_where_str4 ||' <> '||l_where_str3;
441 END IF;
442 ELSE
443 l_where_str2 := l_where_str2 ||' = '||l_where_str3;
444 IF l_where_str4 IS NOT NULL THEN
445 l_where_str4 := l_where_str4 ||' = '||l_where_str3;
446 END IF;
447 END IF;
448 IF l_rec_query.query_type = 1
449 AND l_rec_definition.FIELD_NAME = 'MODE_OF_TRANSPORT'
450 AND l_where_str5 IS NOT NULL THEN
451 l_where_str1:= l_plan_criteria; -- Very important line @@@
452 g_plan_criteria := l_plan_criteria;
453 l_plan_criteria := l_changed ;
454 l_where_str2 := l_where_str2||' OR '|| l_where_str5;
455 END IF;
456 END IF;
457 IF l_where_str4 IS NOT NULL THEN
458 l_where_str2 := '( '||l_where_str2 ||' OR '|| l_where_str4 ||' )';
459 END IF;
460 END IF;
461
462 IF l_where_str1 IS NULL THEN
463 l_where_str1 := ' WHERE PLAN_ID = '||P_PLAN_ID||' AND (';
464 IF l_rec_query.query_type = 1 AND
465 l_rec_definition.FIELD_NAME IN ('MODE_OF_TRANSPORT', 'CONTINUOUS_MOVE_ID') THEN
466 l_plan_criteria := l_where_str1;
467
468 END IF;
469 l_where_str1 := l_where_str1 ||l_where_str2;
470 ELSIF l_where_str1 IS NOT NULL AND l_plan_criteria = l_changed THEN -- Very important line @@@
471 l_where_str1 := l_where_str1 ||l_where_str2;
472 l_plan_criteria := NULL;
473 ELSE
474 IF NVL(l_rec_query.AND_OR_FLAG,2) = 1 THEN -- 1 AND, 2 OR
475 l_where_str1 := l_where_str1 ||' AND '||l_where_str2;
476 ELSE
477 l_where_str1 := l_where_str1 ||' OR '||l_where_str2;
478 END IF;
479 END IF;
480 ELSIF l_rec_definition.FILTER_TYPE = 4 THEN -- Between
481 IF l_rec_definition.FIELD_NAME IN ('ORIGIN_FACILITY_ID',
482 'ORIGIN_ZIP',
483 'ORIGIN_CITY',
487 DECLARE
484 'ORIGIN_STATE',
485 'ORIGIN_COUNTRY') THEN
486 IF l_rec_definition.FIELD_TYPE = 1 THEN -- NUMBER
488 TYPE num_List IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
489 l_field VARCHAR2(30) ; --:= l_rec_definition.FIELD_NAME;
490 l_field_values num_list;
491 l_related_field VARCHAR2(30);
492 l_related_field_values num_list;
493 l_pos NUMBER;
494 l_priv_pos NUMBER;
495 l_index NUMBER := 0;
496 BEGIN
497 l_field := l_rec_definition.FIELD_NAME;
498 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
499 l_pos := instrb(l_where_str3,l_separator);
500 IF l_pos > 0 THEN
501 l_priv_pos := 1;
502 LOOP
503 l_pos := instrb(l_where_str3,l_separator,l_priv_pos,1);
504 EXIT WHEN l_pos <=0;
505 l_index := l_index + 1;
506 l_field_values(l_index) := TO_NUMBER(substrb(l_where_str3,l_priv_pos,l_pos - l_priv_pos));
507 l_priv_pos := l_pos + 1;
508 IF l_index >99 THEN
509 RAISE too_many_rows;
510 END IF;
511 END LOOP;
512 ELSE
513 l_field_values(1) := TO_NUMBER(l_where_str3);
514 END IF;
515 FETCH cur_definition INTO l_rec_definition;
516 IF cur_definition%NOTFOUND THEN
517 RAISE related_field_notfound;
518 END IF;
519 l_pos := 0;
520 l_index := 0;
521 l_related_field := l_rec_definition.FIELD_NAME;
522 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
523 l_pos := instrb(l_where_str3,l_separator);
524 IF l_pos > 0 THEN
525 l_priv_pos := 1;
526 LOOP
527 l_pos := instrb(l_where_str3,l_separator,l_priv_pos,1);
528 EXIT WHEN l_pos <=0;
529 l_index := l_index + 1;
530 l_related_field_values(l_index) := TO_NUMBER(substrb(l_where_str3,l_priv_pos,l_pos - l_priv_pos));
531 l_priv_pos := l_pos + 1;
532 IF l_index >99 THEN
533 RAISE too_many_rows;
534 END IF;
535 END LOOP;
536 ELSE
537 l_related_field_values(1) := TO_NUMBER(l_where_str3);
538 END IF;
539 l_where_str2 := NULL;
540 FOR i IN l_field_values.first..l_field_values.last LOOP
541 FOR j IN l_related_field_values.first..l_related_field_values.last LOOP
542 IF l_where_str2 IS NOT NULL THEN
543 l_where_str2 := l_where_str2 ||' OR ';
544 END IF;
545 l_where_str2 := l_where_str2||'('||
546 l_field ||'='||l_field_values(i)||' AND '||
547 l_related_field||'='||l_related_field_values(j)||')';
548 END LOOP;
549 END LOOP;
550 IF l_where_str2 IS NOT NULL THEN
551 l_where_str2 := '('||l_where_str2||')';
552 END IF;
553 END ;
554 ELSIF l_rec_definition.FIELD_TYPE =2 THEN -- varchar2
555 DECLARE
556 TYPE char_List IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
557 l_field VARCHAR2(30); -- := l_rec_definition.FIELD_NAME;
558 l_field_values char_List;
559 l_related_field VARCHAR2(30);
560 l_related_field_values char_List;
561 l_pos NUMBER;
562 l_priv_pos NUMBER;
563 l_index NUMBER := 0;
564 BEGIN
565 l_field := l_rec_definition.FIELD_NAME;
566 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
567 l_pos := instrb(l_where_str3,l_separator);
568 IF l_pos > 0 THEN
569 l_priv_pos := 1;
570 LOOP
571 l_pos := instrb(l_where_str3,l_separator,l_priv_pos,1);
572 EXIT WHEN l_pos <=0;
573 l_index := l_index + 1;
574 l_field_values(l_index) := substrb(l_where_str3,l_priv_pos,l_pos - l_priv_pos);
575 l_priv_pos := l_pos + 1;
576 IF l_index >99 THEN
580 ELSE
577 RAISE too_many_rows;
578 END IF;
579 END LOOP;
581 l_field_values(1) := l_where_str3;
582 END IF;
583 FETCH cur_definition INTO l_rec_definition;
584 IF cur_definition%NOTFOUND THEN
585 RAISE related_field_notfound;
586 END IF;
587 l_pos := 0;
588 l_index := 0;
589 l_related_field := l_rec_definition.FIELD_NAME;
590 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
591 l_pos := instrb(l_where_str3,l_separator);
592 IF l_pos > 0 THEN
593 l_priv_pos := 1;
594 LOOP
595 l_pos := instrb(l_where_str3,l_separator,l_priv_pos,1);
596 EXIT WHEN l_pos <=0;
597 l_index := l_index + 1;
598 l_related_field_values(l_index) := substrb(l_where_str3,l_priv_pos,l_pos - l_priv_pos);
599 l_priv_pos := l_pos + 1;
600 IF l_index >99 THEN
601 RAISE too_many_rows;
602 END IF;
603 END LOOP;
604 ELSE
605 l_related_field_values(1) := l_where_str3;
606 END IF;
607 l_where_str2 := NULL;
608 FOR i IN l_field_values.first..l_field_values.last LOOP
609 FOR j IN l_related_field_values.first..l_related_field_values.last LOOP
610 IF l_where_str2 IS NOT NULL THEN
611 l_where_str2 := l_where_str2 ||' OR ';
612 END IF;
613 l_where_str2 := l_where_str2||'('||
614 l_field ||'='''||l_field_values(i)||''' AND '||
615 l_related_field||'='''||l_related_field_values(j)||''')';
616 END LOOP;
617 END LOOP;
618 IF l_where_str2 IS NOT NULL THEN
619 l_where_str2 := '('||l_where_str2||')';
620 END IF;
621 END ;
622 END IF;
623 ELSE
624 l_where_str2 := l_rec_definition.FIELD_NAME;
625 IF l_rec_definition.FIELD_TYPE = 1 THEN -- NUMBER
626 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM||' AND '||l_rec_definition.FIELD_VALUE_TO;
627 ELSIF l_rec_definition.FIELD_TYPE =2 THEN -- varchar2
628 l_where_str3 := ''''||l_rec_definition.FIELD_VALUE_FROM||''' AND '''||l_rec_definition.FIELD_VALUE_FROM||'''';
629 END IF;
630 l_where_str2 := l_where_str2 ||' BETWEEN '||l_where_str3;
631 END IF; -- end FIELD_NAME.
632 IF l_where_str1 IS NULL THEN
633 l_where_str1 := ' WHERE PLAN_ID = '||P_PLAN_ID||' AND (';
634 l_where_str1 := l_where_str1 ||l_where_str2;
635 ELSE
636 IF NVL(l_rec_query.AND_OR_FLAG,2) = 1 THEN -- 1 AND, 2 OR
637 l_where_str1 := l_where_str1 ||' AND '||l_where_str2;
638 ELSE
639 l_where_str1 := l_where_str1 ||' OR '||l_where_str2;
640 END IF;
641 END IF;
642 ELSIF l_rec_definition.FILTER_TYPE = 5 THEN -- LIKE
643 l_where_str2 := l_rec_definition.FIELD_NAME;
644 IF l_rec_definition.FIELD_NAME = 'FACILITY' THEN
645 --RAISE skip_condition;
646 l_where_str2 := 'ORIGIN_FACILITY';
647 l_where_str4 := 'DESTINATION_FACILITY';
648 END IF;
649 IF l_rec_definition.FIELD_TYPE = 1 THEN -- NUMBER
650 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM||'%';
651 ELSIF l_rec_definition.FIELD_TYPE =2 THEN -- varchar2
652 l_where_str3 := ''''||l_rec_definition.FIELD_VALUE_FROM||'%''';
653 END IF;
654 l_where_str2 := l_where_str2 ||' LIKE '||l_where_str3;
655 IF l_where_str4 IS NOT NULL THEN
656 l_where_str4 := l_where_str4 ||' LIKE '||l_where_str3;
657 l_where_str2 := '('||l_where_str2||' OR '||l_where_str4||' )';
658 END IF;
659 IF l_where_str1 IS NULL THEN
660 l_where_str1 := ' WHERE PLAN_ID = '||P_PLAN_ID||' AND (';
661 l_where_str1 := l_where_str1 ||l_where_str2;
662 ELSE
663 IF NVL(l_rec_query.AND_OR_FLAG,2) = 1 THEN -- 1 AND, 2 OR
664 l_where_str1 := l_where_str1 ||' AND '||l_where_str2;
665 ELSE
666 l_where_str1 := l_where_str1 ||' OR '||l_where_str2;
667 END IF;
668 END IF;
669 ELSIF l_rec_definition.FILTER_TYPE = 6 THEN -- <
670 l_where_str2 := l_rec_definition.FIELD_NAME;
671
675 l_where_str3 := ''''||l_rec_definition.FIELD_VALUE_FROM||'''';
672 IF l_rec_definition.FIELD_TYPE = 1 THEN -- NUMBER
673 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
674 ELSIF l_rec_definition.FIELD_TYPE =2 THEN -- varchar2
676 END IF;
677 l_where_str2 := l_where_str2 ||' < '||l_where_str3;
678 IF l_where_str1 IS NULL THEN
679 l_where_str1 := ' WHERE PLAN_ID = '||P_PLAN_ID||' AND (';
680 l_where_str1 := l_where_str1 ||l_where_str2;
681 ELSE
682 IF NVL(l_rec_query.AND_OR_FLAG,2) = 1 THEN -- 1 AND, 2 OR
683 l_where_str1 := l_where_str1 ||' AND '||l_where_str2;
684 ELSE
685 l_where_str1 := l_where_str1 ||' OR '||l_where_str2;
686 END IF;
687 END IF;
688 ELSIF l_rec_definition.FILTER_TYPE = 7 THEN -- >
689 l_where_str2 := l_rec_definition.FIELD_NAME;
690
691 IF l_rec_definition.FIELD_TYPE = 1 THEN -- NUMBER
692 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
693 ELSIF l_rec_definition.FIELD_TYPE =2 THEN -- varchar2
694 l_where_str3 := ''''||l_rec_definition.FIELD_VALUE_FROM||'''';
695 END IF;
696 l_where_str2 := l_where_str2 ||' > '||l_where_str3;
697 IF l_where_str1 IS NULL THEN
698 l_where_str1 := ' WHERE PLAN_ID = '||P_PLAN_ID||' AND (';
699 l_where_str1 := l_where_str1 ||l_where_str2;
700 ELSE
701 IF NVL(l_rec_query.AND_OR_FLAG,2) = 1 THEN -- 1 AND, 2 OR
702 l_where_str1 := l_where_str1 ||' AND '||l_where_str2;
703 ELSE
704 l_where_str1 := l_where_str1 ||' OR '||l_where_str2;
705 END IF;
706 END IF;
707 ELSIF l_rec_definition.FILTER_TYPE = 8 THEN -- >= atleast
708 l_where_str2 := l_rec_definition.FIELD_NAME;
709
710 IF l_rec_definition.FIELD_TYPE = 1 THEN -- NUMBER
711 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
712 ELSIF l_rec_definition.FIELD_TYPE =2 THEN -- varchar2
713 l_where_str3 := ''''||l_rec_definition.FIELD_VALUE_FROM||'''';
714 END IF;
715 l_where_str2 := l_where_str2 ||' >= '||l_where_str3;
716 IF l_where_str1 IS NULL THEN
717 l_where_str1 := ' WHERE PLAN_ID = '||P_PLAN_ID||' AND (';
718 l_where_str1 := l_where_str1 ||l_where_str2;
719 ELSE
720 IF NVL(l_rec_query.AND_OR_FLAG,2) = 1 THEN -- 1 AND, 2 OR
721 l_where_str1 := l_where_str1 ||' AND '||l_where_str2;
722 ELSE
723 l_where_str1 := l_where_str1 ||' OR '||l_where_str2;
724 END IF;
725 END IF;
726 ELSIF l_rec_definition.FILTER_TYPE = 9 THEN -- <= atmost
727 l_where_str2 := l_rec_definition.FIELD_NAME;
728
729 IF l_rec_definition.FIELD_TYPE = 1 THEN -- NUMBER
730 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
731 ELSIF l_rec_definition.FIELD_TYPE =2 THEN -- varchar2
732 l_where_str3 := ''''||l_rec_definition.FIELD_VALUE_FROM||'''';
733 END IF;
734 l_where_str2 := l_where_str2 ||' <= '||l_where_str3;
735 IF l_where_str1 IS NULL THEN
736 l_where_str1 := ' WHERE PLAN_ID = '||P_PLAN_ID||' AND (';
737 l_where_str1 := l_where_str1 ||l_where_str2;
738 ELSE
739 IF NVL(l_rec_query.AND_OR_FLAG,2) = 1 THEN -- 1 AND, 2 OR
740 l_where_str1 := l_where_str1 ||' AND '||l_where_str2;
741 ELSE
742 l_where_str1 := l_where_str1 ||' OR '||l_where_str2;
743 END IF;
744 END IF;
745 -- --------------------------------
746 ELSIF l_rec_definition.FILTER_TYPE = 10 THEN -- From or To
747 l_where_str2 := l_rec_definition.FIELD_NAME;
748 IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
749 IF l_rec_definition.FIELD_TYPE = 1 THEN -- NUMBER
750 l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
751 ELSIF l_rec_definition.FIELD_TYPE =2 THEN -- varchar2
752 l_where_str3 := '('''||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,''''||l_delim||'''')||''')';
753 END IF;
754 IF l_rec_definition.FILTER_TYPE = 2 THEN -- NOT
755 l_where_str2 := l_where_str2 ||' NOT IN '||l_where_str3;
756 ELSE
757 l_where_str2 := l_where_str2 ||' IN '||l_where_str3;
758 END IF;
759 ELSIF l_rec_definition.MULTI_SELECT =2 THEN -- no
760 IF l_rec_definition.FIELD_TYPE = 1 THEN -- NUMBER
761 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
762 ELSIF l_rec_definition.FIELD_TYPE =2 THEN -- varchar2
766 l_where_str2 := l_where_str2 ||' <> '||l_where_str3;
763 l_where_str3 := ''''||l_rec_definition.FIELD_VALUE_FROM||'''';
764 END IF;
765 IF l_rec_definition.FILTER_TYPE = 2 THEN -- NOT
767 ELSE
768 l_where_str2 := l_where_str2 ||' = '||l_where_str3;
769 END IF;
770 END IF;
771 l_where_str2 := '('||l_where_str2||' OR ';
772 FETCH cur_definition INTO l_rec_definition;
773 IF cur_definition%NOTFOUND THEN
774 RAISE related_field_notfound;
775 END IF;
776 IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
777 IF l_rec_definition.FIELD_TYPE = 1 THEN -- NUMBER
778 l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
779 ELSIF l_rec_definition.FIELD_TYPE =2 THEN -- varchar2
780 l_where_str3 := '('''||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,''''||l_delim||'''')||''')';
781 END IF;
782 IF l_rec_definition.FILTER_TYPE = 2 THEN -- NOT
783 l_where_str2 := l_where_str2 ||l_rec_definition.FIELD_NAME||' NOT IN '||l_where_str3;
784 ELSE
785 l_where_str2 := l_where_str2 ||l_rec_definition.FIELD_NAME||' IN '||l_where_str3;
786 END IF;
787 ELSIF l_rec_definition.MULTI_SELECT =2 THEN -- no
788 IF l_rec_definition.FIELD_TYPE = 1 THEN -- NUMBER
789 l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
790 ELSIF l_rec_definition.FIELD_TYPE =2 THEN -- varchar2
791 l_where_str3 := ''''||l_rec_definition.FIELD_VALUE_FROM||'''';
792 END IF;
793 IF l_rec_definition.FILTER_TYPE = 2 THEN -- NOT
794 l_where_str2 := l_where_str2 ||l_rec_definition.FIELD_NAME||' <> '||l_where_str3;
795 ELSE
796 l_where_str2 := l_where_str2 ||l_rec_definition.FIELD_NAME||' = '||l_where_str3;
797 END IF;
798 END IF;
799 l_where_str2 := l_where_str2 ||')';
800 --END IF;
801 IF l_where_str1 IS NULL THEN
802 l_where_str1 := ' WHERE PLAN_ID = '||P_PLAN_ID||' AND (';
803 l_where_str1 := l_where_str1 ||l_where_str2;
804 ELSE
805 IF NVL(l_rec_query.AND_OR_FLAG,2) = 1 THEN -- 1 AND, 2 OR
806 l_where_str1 := l_where_str1 ||' AND '||l_where_str2;
807 ELSE
808 l_where_str1 := l_where_str1 ||' OR '||l_where_str2;
809 END IF;
810 END IF;
811 END IF; -- end FILTER_TYPE
812 EXCEPTION
813 WHEN skip_condition THEN
814 NULL;
815 END;
816 END LOOP;
817 CLOSE cur_definition;
818 --KSA_DEBUG(SYSDATE,'out side loop -l_where_str1 '||l_where_str1,'populate_result_table');
819 IF l_where_str1 IS NULL THEN
820 RAISE no_data_found;
821 END IF;
822 l_executed:= execute_dyn_sql(l_insert_str||l_insert_Col||l_select_str||l_where_str1,NULL);
823 IF l_executed = 0 THEN -- Continuous Moves
824 RAISE execution_failed;
825 END IF;
826 END IF; -- end query_type
827 retcode := 0;
828 errbuf := NULL;
829 COMMIT;
830 EXCEPTION
831 WHEN execution_failed THEN
832 --RAISE;
833 --errbuf := l_where_str1;
834 retcode := 2;
835 WHEN no_data_found THEN
836 FND_MESSAGE.set_name('MST','MST_PQ_INADEQUATE_FILTER' ); --'Inadequate Filter Conditions.');
837 errbuf := FND_MESSAGE.GET;
838 --errbuf := 'No logical conditions';
839 retcode := 2;
840 WHEN OTHERS THEN
841 IF cur_definition%ISOPEN THEN
842 CLOSE cur_definition;
843 END IF;
844 --KSA_DEBUG(SYSDATE,SQLERRM(SQLCODE),'populate_result_table');
845 errbuf := SQLERRM(SQLCODE);
846 retcode := 2;
847 --RAISE;
848 END populate_result_table;
849
850 PROCEDURE remove_query(P_QUERY_ID IN NUMBER,
851 P_QUERY_TYPE IN NUMBER) IS
852 BEGIN
853 DELETE mst_personal_query_results
854 WHERE query_id = p_query_id;
855 --IF p_query_type IN (1,2,3,4) THEN
856 DELETE mst_selection_criteria
857 WHERE query_id = p_query_id;
858 --END IF;
859 EXCEPTION
860 WHEN OTHERS THEN
861 RAISE;
862 END remove_query;
863
864 PROCEDURE remove_qry_and_results(P_QUERY_ID IN NUMBER,
865 P_QUERY_TYPE IN NUMBER) IS
866 --pragma autonomous_transaction;
867 BEGIN
868
869 DELETE mst_personal_queries
870 WHERE query_id = p_query_id;
871 IF NOT sql%FOUND THEN
872 RETURN;
873 END IF;
874 IF P_QUERY_TYPE = 1 THEN
878 DELETE MST_CM_SELECTION_CRITERIA
875 DELETE MST_LOAD_SELECTION_CRITERIA
876 WHERE query_id = p_query_id;
877 ELSIF P_QUERY_TYPE = 2 THEN
879 WHERE query_id = p_query_id;
880 ELSIF P_QUERY_TYPE = 3 THEN
881 DELETE MST_ORDER_SELECTION_CRITERIA
882 WHERE query_id = p_query_id;
883 ELSIF P_QUERY_TYPE = 4 THEN
884 DELETE MST_EXCEP_SELECTION_CRITERIA
885 WHERE query_id = p_query_id;
886 END IF;
887 DELETE mst_personal_query_results
888 WHERE query_id = p_query_id;
889
890 DELETE mst_selection_criteria
891 WHERE query_id = p_query_id;
892 COMMIT;
893 EXCEPTION
894 WHEN OTHERS THEN
895 RAISE;
896 END remove_qry_and_results;
897
898 PROCEDURE RENAME_QUERY(P_QUERY_ID IN NUMBER,
899 p_query_name IN VARCHAR2,
900 p_description IN VARCHAR2,
901 p_public_flag IN NUMBER) IS
902 BEGIN
903 UPDATE mst_personal_queries
904 SET query_name = p_query_name,
905 description = p_description,
906 public_flag = p_public_flag
907 WHERE query_id = p_query_id;
908 commit;
909 END RENAME_QUERY;
910
911 PROCEDURE insert_load_selection(p_query_id IN NUMBER) IS
912
913 cursor cur_loads is
914 select ALL_TL, ALL_LTL, ALL_PARCEL,
915 TLS_IN_CM,
916 TL_NUMBERS,LTL_NUMBERS,PARCEL_NUMBERS,
917 RANGE_TYPE, USER_EDITS_ONLY,
918 ORIGIN_FACILITIES, DESTINATION_FACILITIES,
919 ORIGIN_FACILITY_IDS, DESTINATION_FACILITY_IDS,
920 ORIGIN_ZIP , DESTINATION_ZIP,
921 ORIGIN_CITY , DESTINATION_CITY,
922 ORIGIN_STATE , DESTINATION_STATE,
923 ORIGIN_COUNTRY , DESTINATION_COUNTRY,
924 CARRIERS , CUSTOMERS , SUPPLIERS ,
925 CARRIER_IDS, CUSTOMER_IDS, SUPPLIER_IDS,
926 COST_TYPE , COST_FROM , COST_TO ,
927 WEIGHT_TYPE, WEIGHT_FROM , WEIGHT_TO ,
928 CUBE_TYPE , CUBE_FROM , CUBE_TO ,
929 UTILIZATION_TYPE , UTILIZATION_FROM, UTILIZATION_TO,
930 DEPARTURE_TIME_TYPE, DEPARTURE_TIME_FROM,
931 DEPARTURE_TIME_TO , DEPARTURE_TIME_UNIT
932 from mst_load_selection_criteria
933 where query_id = p_query_id;
934
935 l_rec_loads cur_loads%ROWTYPE;
936
937 l_insert_begin VARCHAR2(500);
938 l_insert_what VARCHAR2(3000);
939 l_insert_who VARCHAR2(500);
940 l_delete_str VARCHAR2(500);
941 l_executed NUMBER ;
942 l_filter_type NUMBER;
943 l_sequence NUMBER ; --:= 0;
944 l_multi_select NUMBER ; --:= 2; -- 1 true, 2 false
945 l_active_flag NUMBER ; --:= 1;
946 l_delim CONSTANT VARCHAR2(1) := ',';
947 l_separator CONSTANT VARCHAR2(1):= ';';
948 l_null_str CONSTANT VARCHAR2(6) := 'NULL';
949 l_userid NUMBER ; --:= TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
950 execution_failed EXCEPTION;
951 BEGIN
952 l_sequence := 0;
953 l_multi_select := 2; -- 1 true, 2 false
954 l_active_flag := 1;
955 l_userid := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
956 l_delete_str := 'DELETE MST_SELECTION_CRITERIA WHERE QUERY_ID = :P_QUERYID';
957 l_executed := execute_dyn_sql(l_delete_str,p_query_id);
958 IF l_executed = 0 THEN
959 RAISE execution_failed;
960 END IF;
961 IF g_select_all IS NULL THEN
962 FND_MESSAGE.set_name('MST','MST_PQ_ALL');
963 g_select_all:= FND_MESSAGE.GET;
964 --g_select_all := 'All';
965 END IF;
966 l_insert_begin := 'INSERT INTO MST_SELECTION_CRITERIA '||
967 '(QUERY_ID , FIELD_NAME , SEQUENCE ,'||
968 'FILTER_TYPE , FIELD_VALUE_FROM, DISPLAY_VALUE,'||
969 'FIELD_VALUE_TO, MULTI_SELECT , ACTIVE_FLAG ,'||
970 'CREATED_BY , CREATION_DATE ) VALUES ';
971 OPEN cur_loads;
972 FETCH cur_loads INTO l_rec_loads;
973 IF cur_loads%NOTFOUND THEN
974 CLOSE cur_loads;
975 RAISE no_data_found;
976 END IF;
977 CLOSE cur_loads;
978 -- ---------------------------
979 -- Criteria 1-A - CM Trips
980 -- ---------------------------
981 l_filter_type := NULL;
982 l_multi_select := 2;
983 l_insert_what := '(';
984 l_executed := NULL;
985 IF l_rec_loads.TLS_IN_CM = 1 THEN
986 DECLARE
987 l_cm NUMBER;
988 BEGIN
989 l_sequence := l_sequence +1 ;
990 l_filter_type := 1;
991 l_cm := 1;
992 l_insert_what := l_insert_what ||p_query_id||l_delim||'''CONTINUOUS_MOVE_ID'''||l_delim||
993 l_sequence ||l_delim||l_filter_type||l_delim||
994 ''''||l_cm||''''||l_delim||
995 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
996 l_active_flag||l_delim;
1000 IF l_executed = 0 THEN
997 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
998 --KSA_DEBUG(SYSDATE,L_INSERT_WHO,'INSERT LOAD - WHO');
999 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1001 RAISE execution_failed;
1002 END IF;
1003 END;
1004 END IF;
1005 l_filter_type := NULL;
1006 l_multi_select := 2;
1007 l_insert_what := '(';
1008 l_executed := NULL;
1009 -- ---------------------------------
1010 -- Criteria 1- mode of Transport
1011 -- ---------------------------------
1012 IF (l_rec_loads.ALL_TL = 1 AND l_rec_loads.TLS_IN_CM <> 1 )
1013 OR l_rec_loads.ALL_LTL = 1
1014 OR l_rec_loads.ALL_PARCEL = 1 THEN
1015 DECLARE
1016 l_modes VARCHAR2(50);
1017 BEGIN
1018 IF l_rec_loads.all_tl = 1 AND --OR
1019 --l_rec_loads.TL_NUMBERS IS NOT NULL OR
1020 l_rec_loads.TLS_IN_CM <> 1 THEN
1021 l_modes := 'TRUCK';
1022 END IF;
1023 IF l_rec_loads.all_ltl =1 THEN --OR
1024 --l_rec_loads.LTL_NUMBERS IS NOT NULL THEN
1025 IF l_modes IS NOT NULL THEN
1026 l_modes := l_modes||l_separator||'LTL';
1027 ELSE
1028 l_modes := 'LTL';
1029 END IF;
1030 END IF;
1031 IF l_rec_loads.all_parcel =1 THEN --OR
1032 --l_rec_loads.PARCEL_NUMBERS IS NOT NULL THEN
1033 IF l_modes IS NOT NULL THEN
1034 l_modes := l_modes||l_separator||'PARCEL';
1035 ELSE
1036 l_modes := 'PARCEL';
1037 END IF;
1038 END IF;
1039 l_sequence := l_sequence +1 ;
1040 IF instrb(l_modes,l_separator) > 0 THEN
1041 l_filter_type := 3;
1042 l_multi_select:= 1;
1043 ELSE
1044 l_filter_type := 1;
1045 END IF;
1046 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''MODE_OF_TRANSPORT'''||l_delim||
1047 l_sequence ||l_delim||l_filter_type||l_delim||
1048 ''''||l_modes||''''||l_delim||
1049 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1050 l_active_flag||l_delim;
1051 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1052 --KSA_DEBUG(SYSDATE,L_INSERT_WHO,'INSERT LOAD - WHO');
1053 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1054 IF l_executed = 0 THEN
1055 RAISE execution_failed;
1056 END IF;
1057 END;
1058 END IF;
1059 -- ---------------------------------
1060 -- Criteria 1-B - Trip Numbers
1061 -- ---------------------------------
1062 l_filter_type := NULL;
1063 l_multi_select := 2;
1064 l_insert_what := '(';
1065 l_executed := NULL;
1066 -- ---------------------------------
1067 -- Criteria 1-B-1 - TL Trip Numbers
1068 -- ---------------------------------
1069 IF l_rec_loads.TL_NUMBERS IS NOT NULL THEN
1070 DECLARE
1071 l_trip_numbers VARCHAR2(2000);
1072 BEGIN
1073 l_trip_numbers := l_rec_loads.TL_NUMBERS;
1074
1075 IF instrb(l_trip_numbers,l_separator) > 0
1076 AND instrb(l_trip_numbers,l_separator,-1)<>lengthb(l_trip_numbers) THEN
1077 l_trip_numbers := l_trip_numbers||l_separator;
1078 END IF;
1079 l_sequence := l_sequence +1 ;
1080 IF instrb(l_trip_numbers,l_separator) > 0 THEN
1081 l_filter_type := 3;
1082 l_multi_select:= 1;
1083 ELSE
1084 l_filter_type := 1;
1085 END IF;
1086 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''TL_TRIP_NUMBER'''||l_delim||
1087 l_sequence ||l_delim||l_filter_type||l_delim||
1088 ''''||l_trip_numbers||''''||l_delim||
1089 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1090 l_active_flag||l_delim;
1091 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1092 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1093 IF l_executed = 0 THEN
1094 RAISE execution_failed;
1095 END IF;
1096 END;
1097 END IF;
1098 l_filter_type := NULL;
1099 l_multi_select := 2;
1100 l_insert_what := '(';
1101 l_executed := NULL;
1102 -- ---------------------------------
1103 -- Criteria 1-B-2 - LTL Trip Numbers
1104 -- ---------------------------------
1105 IF l_rec_loads.LTL_NUMBERS IS NOT NULL THEN
1106 DECLARE
1107 l_trip_numbers VARCHAR2(2000);
1108 BEGIN
1109 l_trip_numbers := l_rec_loads.LTL_NUMBERS;
1110
1111 IF instrb(l_trip_numbers,l_separator) > 0
1112 AND instrb(l_trip_numbers,l_separator,-1)<>lengthb(l_trip_numbers) THEN
1116 IF instrb(l_trip_numbers,l_separator) > 0 THEN
1113 l_trip_numbers := l_trip_numbers||l_separator;
1114 END IF;
1115 l_sequence := l_sequence +1 ;
1117 l_filter_type := 3;
1118 l_multi_select:= 1;
1119 ELSE
1120 l_filter_type := 1;
1121 END IF;
1122 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''LTL_TRIP_NUMBER'''||l_delim||
1123 l_sequence ||l_delim||l_filter_type||l_delim||
1124 ''''||l_trip_numbers||''''||l_delim||
1125 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1126 l_active_flag||l_delim;
1127 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1128 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1129 IF l_executed = 0 THEN
1130 RAISE execution_failed;
1131 END IF;
1132 END;
1133 END IF;
1134 l_filter_type := NULL;
1135 l_multi_select := 2;
1136 l_insert_what := '(';
1137 l_executed := NULL;
1138 -- ------------------------------------
1139 -- Criteria 1-B-3 - PARCEL Trip Numbers
1140 -- ------------------------------------
1141 IF l_rec_loads.PARCEL_NUMBERS IS NOT NULL THEN
1142 DECLARE
1143 l_trip_numbers VARCHAR2(2000);
1144 BEGIN
1145 l_trip_numbers := l_rec_loads.PARCEL_NUMBERS;
1146
1147 IF instrb(l_trip_numbers,l_separator) > 0
1148 AND instrb(l_trip_numbers,l_separator,-1)<>lengthb(l_trip_numbers) THEN
1149 l_trip_numbers := l_trip_numbers||l_separator;
1150 END IF;
1151 l_sequence := l_sequence +1 ;
1152 IF instrb(l_trip_numbers,l_separator) > 0 THEN
1153 l_filter_type := 3;
1154 l_multi_select:= 1;
1155 ELSE
1156 l_filter_type := 1;
1157 END IF;
1158 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''PARCEL_TRIP_NUMBER'''||l_delim||
1159 l_sequence ||l_delim||l_filter_type||l_delim||
1160 ''''||l_trip_numbers||''''||l_delim||
1161 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1162 l_active_flag||l_delim;
1163 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1164 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1165 IF l_executed = 0 THEN
1166 RAISE execution_failed;
1167 END IF;
1168 END;
1169 END IF;
1170 /*@@@SKAKANI@@@IF l_rec_loads.TL_NUMBERS IS NOT NULL
1171 OR l_rec_loads.LTL_NUMBERS IS NOT NULL
1172 OR l_rec_loads.PARCEL_NUMBERS IS NOT NULL THEN
1173 DECLARE
1174 l_trip_numbers VARCHAR2(2000);
1175 BEGIN
1176 l_trip_numbers := l_rec_loads.TL_NUMBERS;
1177 IF l_rec_loads.LTL_NUMBERS IS NOT NULL THEN
1178 IF l_trip_numbers IS NOT NULL THEN
1179 IF instrb(l_trip_numbers,l_separator,-1)<>lengthb(l_trip_numbers) THEN
1180 l_trip_numbers := l_trip_numbers||l_separator||l_rec_loads.LTL_NUMBERS;
1181 ELSE
1182 l_trip_numbers := l_trip_numbers||l_rec_loads.LTL_NUMBERS;
1183 END IF;
1184 ELSE
1185 l_trip_numbers := l_rec_loads.LTL_NUMBERS;
1186 END IF;
1187 END IF;
1188 IF l_rec_loads.PARCEL_NUMBERS IS NOT NULL THEN
1189 IF l_trip_numbers IS NOT NULL THEN
1190 IF instrb(l_trip_numbers,l_separator,-1)<>lengthb(l_trip_numbers) THEN
1191 l_trip_numbers := l_trip_numbers||l_separator||l_rec_loads.PARCEL_NUMBERS;
1192 ELSE
1193 l_trip_numbers := l_trip_numbers||l_rec_loads.PARCEL_NUMBERS;
1194 END IF;
1195 ELSE
1196 l_trip_numbers := l_rec_loads.PARCEL_NUMBERS;
1197 END IF;
1198 END IF;
1199 IF instrb(l_trip_numbers,l_separator) > 0
1200 AND instrb(l_trip_numbers,l_separator,-1)<>lengthb(l_trip_numbers) THEN
1201 l_trip_numbers := l_trip_numbers||l_separator;
1202 END IF;
1203 l_sequence := l_sequence +1 ;
1204 IF instrb(l_trip_numbers,l_separator) > 0 THEN
1205 l_filter_type := 3;
1206 l_multi_select:= 1;
1207 ELSE
1208 l_filter_type := 1;
1209 END IF;
1210 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''TRIP_NUMBER'''||l_delim||
1211 l_sequence ||l_delim||l_filter_type||l_delim||
1212 ''''||l_trip_numbers||''''||l_delim||
1213 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1214 l_active_flag||l_delim;
1215 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1216 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1217 IF l_executed = 0 THEN
1218 RAISE execution_failed;
1222 -- ---------------------------------
1219 END IF;
1220 END;
1221 END IF;*/
1223 -- Criteria 2 - USER EDITS
1224 -- ---------------------------------
1225 l_filter_type := NULL;
1226 l_multi_select := 2;
1227 l_insert_what := '(';
1228 l_executed := NULL;
1229 IF l_rec_loads.USER_EDITS_ONLY = 1 THEN
1230 DECLARE
1231 l_user_edits NUMBER;
1232 l_field_name VARCHAR2(30);
1233 BEGIN
1234 l_field_name := 'CHANGED_BY_USER';
1235 l_user_edits := 1;
1236 l_sequence := l_sequence +1 ;
1237 l_filter_type := 1;
1238 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1239 l_sequence ||l_delim||l_filter_type||l_delim||
1240 ''''||l_user_edits||''''||l_delim||
1241 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1242 l_active_flag||l_delim;
1243 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1244 --KSA_DEBUG(SYSDATE,L_INSERT_WHO,'INSERT LOAD - WHO');
1245 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1246 IF l_executed = 0 THEN
1247 RAISE execution_failed;
1248 END IF;
1249 END;
1250 END IF;
1251 -- -------------------
1252 -- Criteria 3 - Range.
1253 -- -------------------
1254 l_filter_type := NULL;
1255 l_multi_select := 2;
1256 l_insert_what := '(';
1257 l_executed := NULL;
1258 IF l_rec_loads.RANGE_TYPE IN (1,2) THEN
1259 -- -----------------------------------
1260 -- Criteria 3 - Range -(1,2)- Facility
1261 -- -----------------------------------
1262 IF l_rec_loads.ORIGIN_FACILITIES <> g_select_all THEN
1263 DECLARE
1264 l_facilities VARCHAR2(2000);
1265 l_field_name VARCHAR2(30);
1266 BEGIN
1267 l_facilities := l_rec_loads.ORIGIN_FACILITY_IDS;
1268
1269 IF l_rec_loads.RANGE_TYPE = 1 THEN
1270 l_field_name := 'ORIGIN_FACILITY_ID';
1271 ELSIF l_rec_loads.RANGE_TYPE = 2 THEN
1272 l_field_name := 'DESTINATION_FACILITY_ID';
1273 END IF;
1274 l_sequence := l_sequence +1 ;
1275 IF instrb(l_facilities,l_separator) > 0 THEN
1276 l_filter_type := 3;
1277 l_multi_select:= 1;
1278 ELSE
1279 l_filter_type := 1;
1280 END IF;
1281 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1282 l_sequence ||l_delim||l_filter_type||l_delim||
1283 ''''||l_facilities||''''||l_delim||
1284 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1285 l_active_flag||l_delim;
1286 --KSA_DEBUG(SYSDATE,L_INSERT_WHAT,'INSERT LOAD - WHAT');
1287 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1288 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1289 END;
1290 END IF; -- end ORIGIN_FACILITIES <> g_select_all
1291 -- -----------------------------------
1292 -- Criteria 3 - Range -(1,2)- Zip
1293 -- -----------------------------------
1294 l_filter_type := NULL;
1295 l_multi_select := 2;
1296 l_insert_what := '(';
1297 l_executed := NULL;
1298 IF l_rec_loads.ORIGIN_ZIP <> g_select_all THEN
1299 DECLARE
1300 l_postalcodes VARCHAR2(2000);
1301 l_field_name VARCHAR2(30);
1302 BEGIN
1303 l_postalcodes := l_rec_loads.ORIGIN_ZIP;
1304
1305 IF l_rec_loads.RANGE_TYPE = 1 THEN
1306 l_field_name := 'ORIGIN_ZIP';
1307 ELSIF l_rec_loads.RANGE_TYPE = 2 THEN
1308 l_field_name := 'DESTINATION_ZIP';
1309 END IF;
1310 l_sequence := l_sequence +1 ;
1311 IF instrb(l_postalcodes,l_separator) > 0 THEN
1312 l_filter_type := 3;
1313 l_multi_select:= 1;
1314 ELSE
1315 l_filter_type := 1;
1316 END IF;
1317 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1318 l_sequence ||l_delim||l_filter_type||l_delim||
1319 ''''||l_postalcodes||''''||l_delim||
1320 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1321 l_active_flag||l_delim;
1322 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1323 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1324 END;
1325 END IF; -- end ORIGIN_ZIP <> g_select_all
1329 l_filter_type := NULL;
1326 -- -----------------------------------
1327 -- Criteria 3 - Range -(1,2)- City
1328 -- -----------------------------------
1330 l_multi_select := 2;
1331 l_insert_what := '(';
1332 l_executed := NULL;
1333 IF l_rec_loads.ORIGIN_CITY <> g_select_all THEN
1334 DECLARE
1335 l_cities VARCHAR2(2000);
1336 l_field_name VARCHAR2(30);
1337 BEGIN
1338 l_cities := l_rec_loads.ORIGIN_CITY;
1339
1340 IF l_rec_loads.RANGE_TYPE = 1 THEN
1341 l_field_name := 'ORIGIN_CITY';
1342 ELSIF l_rec_loads.RANGE_TYPE = 2 THEN
1343 l_field_name := 'DESTINATION_CITY';
1344 END IF;
1345 l_sequence := l_sequence +1 ;
1346 IF instrb(l_cities,l_separator) > 0 THEN
1347 l_filter_type := 3;
1348 l_multi_select:= 1;
1349 ELSE
1350 l_filter_type := 1;
1351 END IF;
1352 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1353 l_sequence ||l_delim||l_filter_type||l_delim||
1354 ''''||l_cities||''''||l_delim||
1355 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1356 l_active_flag||l_delim;
1357 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1358 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1359 END;
1360 END IF; -- end ORIGIN_CITY <> g_select_all
1361 -- -----------------------------------
1362 -- Criteria 3 - Range -(1,2)- State
1363 -- -----------------------------------
1364 l_filter_type := NULL;
1365 l_multi_select := 2;
1366 l_insert_what := '(';
1367 l_executed := NULL;
1368 IF l_rec_loads.ORIGIN_STATE <> g_select_all THEN
1369 DECLARE
1370 l_states VARCHAR2(2000);
1371 l_field_name VARCHAR2(30);
1372 BEGIN
1373 l_states := l_rec_loads.ORIGIN_STATE;
1374
1375 IF l_rec_loads.RANGE_TYPE = 1 THEN
1376 l_field_name := 'ORIGIN_STATE';
1377 ELSIF l_rec_loads.RANGE_TYPE = 2 THEN
1378 l_field_name := 'DESTINATION_STATE';
1379 END IF;
1380 l_sequence := l_sequence +1 ;
1381 IF instrb(l_states,l_separator) > 0 THEN
1382 l_filter_type := 3;
1383 l_multi_select:= 1;
1384 ELSE
1385 l_filter_type := 1;
1386 END IF;
1387 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1388 l_sequence ||l_delim||l_filter_type||l_delim||
1389 ''''||l_states||''''||l_delim||
1390 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1391 l_active_flag||l_delim;
1392 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1393 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1394 END;
1395 END IF; -- end ORIGIN_STATE <> g_select_all
1396 -- -----------------------------------
1400 l_multi_select := 2;
1397 -- Criteria 3 - Range -(1,2)- Country
1398 -- -----------------------------------
1399 l_filter_type := NULL;
1401 l_insert_what := '(';
1402 l_executed := NULL;
1403 IF l_rec_loads.ORIGIN_COUNTRY <> g_select_all THEN
1404 DECLARE
1405 l_countries VARCHAR2(2000);
1406 l_field_name VARCHAR2(30);
1407 BEGIN
1408 l_countries := l_rec_loads.ORIGIN_COUNTRY;
1409
1410 IF l_rec_loads.RANGE_TYPE = 1 THEN
1411 l_field_name := 'ORIGIN_COUNTRY';
1412 ELSIF l_rec_loads.RANGE_TYPE = 2 THEN
1413 l_field_name := 'DESTINATION_COUNTRY';
1414 END IF;
1415 l_sequence := l_sequence +1 ;
1416 IF instrb(l_countries,l_separator) > 0 THEN
1417 l_filter_type := 3;
1418 l_multi_select:= 1;
1419 ELSE
1420 l_filter_type := 1;
1421 END IF;
1422 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1423 l_sequence ||l_delim||l_filter_type||l_delim||
1424 ''''||l_countries||''''||l_delim||
1425 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1426 l_active_flag||l_delim;
1427 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1428 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1429 END;
1430 END IF; -- end ORIGIN_COUNTRY <> g_select_all
1431 ELSIF l_rec_loads.RANGE_TYPE = 3 THEN
1432 -- ---------------------------------
1433 -- Criteria 3 - Range -(3)- Facility
1434 -- ---------------------------------
1435 IF l_rec_loads.ORIGIN_FACILITIES <> g_select_all THEN
1436 DECLARE
1437 l_facilities VARCHAR2(2000);
1438 l_field_name VARCHAR2(30);
1439 BEGIN
1440 l_facilities := l_rec_loads.ORIGIN_FACILITY_IDS;
1441 l_field_name := 'ORIGIN_FACILITY_ID';
1442 l_sequence := l_sequence +1 ;
1443 IF instrb(l_facilities,l_separator) > 0 THEN
1444 --l_filter_type := 3;
1445 l_multi_select:= 1;
1446 ELSE
1447 l_multi_select:= 2;
1448 --l_filter_type := 1;
1449 END IF;
1450 l_filter_type := 10;
1451 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1452 l_sequence ||l_delim||l_filter_type||l_delim||
1453 ''''||l_facilities||''''||l_delim||
1454 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1458
1455 l_active_flag||l_delim;
1456 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1457 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1459 l_filter_type := NULL;
1460 l_multi_select := 2;
1461 l_insert_what := '(';
1462 l_executed := NULL;
1463 l_field_name := 'DESTINATION_FACILITY_ID';
1464 l_sequence := l_sequence +1 ;
1465 IF instrb(l_facilities,l_separator) > 0 THEN
1466 --l_filter_type := 3;
1467 l_multi_select:= 1;
1468 ELSE
1469 --l_filter_type := 1;
1470 l_multi_select:= 2;
1471 END IF;
1472 l_filter_type := 10;
1473 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1474 l_sequence ||l_delim||l_filter_type||l_delim||
1475 ''''||l_facilities||''''||l_delim||
1476 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1477 l_active_flag||l_delim;
1478 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1479 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1480 END;
1481 END IF; -- end ORIGIN_FACILITIES <> g_select_all
1482 -- ---------------------------------
1483 -- Criteria 3 - Range -(3)- Zip
1484 -- ---------------------------------
1485 l_filter_type := NULL;
1486 l_multi_select := 2;
1487 l_insert_what := '(';
1488 l_executed := NULL;
1489 IF l_rec_loads.ORIGIN_ZIP <> g_select_all THEN
1490 DECLARE
1491 l_postalcodes VARCHAR2(2000);
1492 l_field_name VARCHAR2(30);
1493 BEGIN
1494 l_postalcodes := l_rec_loads.ORIGIN_ZIP;
1495 l_field_name := 'ORIGIN_ZIP';
1496 l_sequence := l_sequence +1 ;
1497 IF instrb(l_postalcodes,l_separator) > 0 THEN
1498 --l_filter_type := 3;
1499 l_multi_select:= 1;
1500 ELSE
1501 --l_filter_type := 1;
1502 l_multi_select:= 2;
1503 END IF;
1504 l_filter_type := 10;
1505 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1506 l_sequence ||l_delim||l_filter_type||l_delim||
1507 ''''||l_postalcodes||''''||l_delim||
1508 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1509 l_active_flag||l_delim;
1510 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1511 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1512
1513 l_filter_type := NULL;
1514 l_multi_select := 2;
1515 l_insert_what := '(';
1519 IF instrb(l_postalcodes,l_separator) > 0 THEN
1516 l_executed := NULL;
1517 l_field_name := 'DESTINATION_ZIP';
1518 l_sequence := l_sequence +1 ;
1520 --l_filter_type := 3;
1521 l_multi_select:= 1;
1522 ELSE
1523 --l_filter_type := 1;
1524 l_multi_select:= 2;
1525 END IF;
1526 l_filter_type := 10;
1527 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1528 l_sequence ||l_delim||l_filter_type||l_delim||
1529 ''''||l_postalcodes||''''||l_delim||
1530 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1531 l_active_flag||l_delim;
1532 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1533 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1534 END;
1535 END IF; -- end ORIGIN_ZIP <> g_select_all
1536 -- ---------------------------------
1537 -- Criteria 3 - Range -(3)- City
1538 -- ---------------------------------
1539 l_filter_type := NULL;
1540 l_multi_select := 2;
1541 l_insert_what := '(';
1542 l_executed := NULL;
1543 IF l_rec_loads.ORIGIN_CITY <> g_select_all THEN
1544 DECLARE
1545 l_cities VARCHAR2(2000);
1546 l_field_name VARCHAR2(30);
1547 BEGIN
1548 l_cities := l_rec_loads.ORIGIN_CITY;
1549 l_field_name := 'ORIGIN_CITY';
1550 l_sequence := l_sequence +1 ;
1551 IF instrb(l_cities,l_separator) > 0 THEN
1552 --l_filter_type := 3;
1553 l_multi_select:= 1;
1554 ELSE
1555 --l_filter_type := 1;
1556 l_multi_select:= 2;
1557 END IF;
1558 l_filter_type := 10;
1559 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1560 l_sequence ||l_delim||l_filter_type||l_delim||
1561 ''''||l_cities||''''||l_delim||
1562 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1563 l_active_flag||l_delim;
1564 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1565 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1566
1567 l_filter_type := NULL;
1568 l_multi_select := 2;
1569 l_insert_what := '(';
1570 l_executed := NULL;
1571 l_field_name := 'DESTINATION_CITY';
1572 l_sequence := l_sequence +1 ;
1573 IF instrb(l_cities,l_separator) > 0 THEN
1574 --l_filter_type := 3;
1575 l_multi_select:= 1;
1576 ELSE
1577 --l_filter_type := 1;
1578 l_multi_select:= 2;
1579 END IF;
1580 l_filter_type := 10;
1581 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1582 l_sequence ||l_delim||l_filter_type||l_delim||
1583 ''''||l_cities||''''||l_delim||
1584 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1585 l_active_flag||l_delim;
1586 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1587 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1588 END;
1589 END IF; -- end ORIGIN_CITY <> g_select_all
1590 -- ---------------------------------
1591 -- Criteria 3 - Range -(3)- State
1592 -- ---------------------------------
1593 l_filter_type := NULL;
1594 l_multi_select := 2;
1595 l_insert_what := '(';
1596 l_executed := NULL;
1597 IF l_rec_loads.ORIGIN_STATE <> g_select_all THEN
1598 DECLARE
1599 l_states VARCHAR2(2000);
1600 l_field_name VARCHAR2(30);
1601 BEGIN
1602 l_states := l_rec_loads.ORIGIN_STATE;
1603 l_field_name := 'ORIGIN_STATE';
1604 l_sequence := l_sequence +1 ;
1608 ELSE
1605 IF instrb(l_states,l_separator) > 0 THEN
1606 --l_filter_type := 3;
1607 l_multi_select:= 1;
1609 --l_filter_type := 1;
1610 l_multi_select:= 2;
1611 END IF;
1612 l_filter_type := 10;
1613 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1614 l_sequence ||l_delim||l_filter_type||l_delim||
1615 ''''||l_states||''''||l_delim||
1616 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1617 l_active_flag||l_delim;
1618 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1619 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1620
1621 l_filter_type := NULL;
1622 l_multi_select := 2;
1623 l_insert_what := '(';
1624 l_executed := NULL;
1625 l_field_name := 'DESTINATION_STATE';
1626 l_sequence := l_sequence +1 ;
1627 IF instrb(l_states,l_separator) > 0 THEN
1628 --l_filter_type := 3;
1629 l_multi_select:= 1;
1630 ELSE
1631 --l_filter_type := 1;
1632 l_multi_select:= 2;
1633 END IF;
1634 l_filter_type := 10;
1635 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1636 l_sequence ||l_delim||l_filter_type||l_delim||
1637 ''''||l_states||''''||l_delim||
1638 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1639 l_active_flag||l_delim;
1640 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1641 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1642 END;
1643 END IF; -- end ORIGIN_STATE <> g_select_all
1644 -- ---------------------------------
1645 -- Criteria 3 - Range -(3)- Country
1646 -- ---------------------------------
1647 l_filter_type := NULL;
1648 l_multi_select := 2;
1649 l_insert_what := '(';
1650 l_executed := NULL;
1651 IF l_rec_loads.ORIGIN_COUNTRY <> g_select_all THEN
1652 DECLARE
1653 l_countries VARCHAR2(2000);
1654 l_field_name VARCHAR2(30);
1655 BEGIN
1656 l_countries := l_rec_loads.ORIGIN_COUNTRY;
1657 l_field_name := 'ORIGIN_COUNTRY';
1658 l_sequence := l_sequence +1 ;
1659 IF instrb(l_countries,l_separator) > 0 THEN
1660 --l_filter_type := 3;
1661 l_multi_select:= 1;
1662 ELSE
1663 --l_filter_type := 1;
1664 l_multi_select:= 2;
1665 END IF;
1666 l_filter_type := 10;
1667 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1668 l_sequence ||l_delim||l_filter_type||l_delim||
1669 ''''||l_countries||''''||l_delim||
1670 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1671 l_active_flag||l_delim;
1672 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1673 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1674
1675 l_filter_type := NULL;
1676 l_multi_select := 2;
1677 l_insert_what := '(';
1678 l_executed := NULL;
1679 l_field_name := 'DESTINATION_COUNTRY';
1680 l_sequence := l_sequence +1 ;
1681 IF instrb(l_countries,l_separator) > 0 THEN
1682 --l_filter_type := 3;
1683 l_multi_select:= 1;
1684 ELSE
1685 --l_filter_type := 1;
1686 l_multi_select:= 2;
1687 END IF;
1688 l_filter_type := 10;
1689 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1690 l_sequence ||l_delim||l_filter_type||l_delim||
1691 ''''||l_countries||''''||l_delim||
1692 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1693 l_active_flag||l_delim;
1694 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1695 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1696 END;
1697 END IF; -- end ORIGIN_COUNTRY <> g_select_all
1698 ELSIF l_rec_loads.RANGE_TYPE = 4 THEN
1699 -- ---------------------------------
1700 -- Criteria 3 - Range -(4)- Facility
1701 -- ---------------------------------
1702 IF l_rec_loads.ORIGIN_FACILITIES <> g_select_all THEN
1703 DECLARE
1707 l_facilities := l_rec_loads.ORIGIN_FACILITY_IDS;
1704 l_facilities VARCHAR2(2000);
1705 l_field_name VARCHAR2(30);
1706 BEGIN
1708 l_field_name := 'ORIGIN_FACILITY_ID';
1709 l_sequence := l_sequence +1 ;
1710 IF instrb(l_facilities,l_separator) > 0 THEN
1711 IF l_rec_loads.DESTINATION_FACILITIES = g_select_all THEN
1712 l_filter_type := 3;
1713 ELSE
1714 l_filter_type := 4;
1715 END IF;
1716 l_multi_select:= 1;
1717 ELSE
1718 IF l_rec_loads.DESTINATION_FACILITIES = g_select_all THEN
1719 l_filter_type := 1;
1720 ELSE
1721 l_filter_type := 4;
1722 END IF;
1723 END IF;
1724 --l_filter_type := 4;
1725 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1726 l_sequence ||l_delim||l_filter_type||l_delim||
1727 ''''||l_facilities||''''||l_delim||
1728 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1729 l_active_flag||l_delim;
1730 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1731 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1732 END;
1733 END IF; -- end ORIGIN_FACILITY <> g_select_all
1734 l_filter_type := NULL;
1735 l_multi_select := 2;
1736 l_insert_what := '(';
1737 l_executed := NULL;
1738 IF l_rec_loads.DESTINATION_FACILITIES <> g_select_all THEN
1739 DECLARE
1740 l_facilities VARCHAR2(2000);
1741 l_field_name VARCHAR2(30);
1742 BEGIN
1743 l_facilities := l_rec_loads.DESTINATION_FACILITY_IDS;
1744 l_field_name := 'DESTINATION_FACILITY_ID';
1745 l_sequence := l_sequence +1 ;
1746 IF instrb(l_facilities,l_separator) > 0 THEN
1747 IF l_rec_loads.ORIGIN_FACILITIES = g_select_all THEN
1748 l_filter_type := 3;
1749 ELSE
1750 l_filter_type := 4;
1751 END IF;
1752 l_multi_select:= 1;
1753 ELSE
1754 IF l_rec_loads.ORIGIN_FACILITIES = g_select_all THEN
1755 l_filter_type := 1;
1756 ELSE
1757 l_filter_type := 4;
1758 END IF;
1759 END IF;
1760 --l_filter_type := 4;
1761 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1762 l_sequence ||l_delim||l_filter_type||l_delim||
1763 ''''||l_facilities||''''||l_delim||
1767 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1764 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1765 l_active_flag||l_delim;
1766 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1768 END;
1769 END IF; -- end DESTINATION_FACILITIES <> g_select_all
1770 -- ---------------------------------
1771 -- Criteria 3 - Range -(4)- Zip
1772 -- ---------------------------------
1773 l_filter_type := NULL;
1774 l_multi_select := 2;
1775 l_insert_what := '(';
1776 l_executed := NULL;
1777 IF l_rec_loads.ORIGIN_ZIP <> g_select_all THEN
1778 DECLARE
1779 l_postalcodes VARCHAR2(2000);
1780 l_field_name VARCHAR2(30);
1781 BEGIN
1782 l_postalcodes := l_rec_loads.ORIGIN_ZIP;
1783 l_field_name := 'ORIGIN_ZIP';
1784 l_sequence := l_sequence +1 ;
1785 IF instrb(l_postalcodes,l_separator) > 0 THEN
1786 --l_filter_type := 3;
1787 IF l_rec_loads.DESTINATION_ZIP = g_select_all THEN
1788 l_filter_type := 3;
1789 ELSE
1790 l_filter_type := 4;
1791 END IF;
1792 l_multi_select:= 1;
1793 ELSE
1794 IF l_rec_loads.DESTINATION_ZIP = g_select_all THEN
1795 l_filter_type := 1;
1796 ELSE
1797 l_filter_type := 4;
1798 END IF;
1799 --l_filter_type := 1;
1800 END IF;
1801 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1802 l_sequence ||l_delim||l_filter_type||l_delim||
1803 ''''||l_postalcodes||''''||l_delim||
1804 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1805 l_active_flag||l_delim;
1806 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1807 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1808 END;
1809 END IF; -- end ORIGIN_ZIP <> g_select_all
1810 l_filter_type := NULL;
1811 l_multi_select := 2;
1812 l_insert_what := '(';
1813 l_executed := NULL;
1814 IF l_rec_loads.DESTINATION_ZIP <> g_select_all THEN
1815 DECLARE
1816 l_postalcodes VARCHAR2(2000);
1817 l_field_name VARCHAR2(30);
1818 BEGIN
1819 l_postalcodes := l_rec_loads.DESTINATION_ZIP;
1820 l_field_name := 'DESTINATION_ZIP';
1821 l_sequence := l_sequence +1 ;
1822 IF instrb(l_postalcodes,l_separator) > 0 THEN
1823 IF l_rec_loads.ORIGIN_ZIP = g_select_all THEN
1824 l_filter_type := 3;
1825 ELSE
1826 l_filter_type := 4;
1827 END IF;
1828 --l_filter_type := 3;
1829 l_multi_select:= 1;
1830 ELSE
1831 --l_filter_type := 1;
1832 IF l_rec_loads.ORIGIN_ZIP = g_select_all THEN
1833 l_filter_type := 1;
1834 ELSE
1835 l_filter_type := 4;
1836 END IF;
1837 END IF;
1838 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1839 l_sequence ||l_delim||l_filter_type||l_delim||
1840 ''''||l_postalcodes||''''||l_delim||
1841 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1842 l_active_flag||l_delim;
1843 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1844 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1845 END;
1846 END IF; -- end DESTINATION_ZIP <> g_select_all
1847 -- ---------------------------------
1848 -- Criteria 3 - Range -(4)- City
1849 -- ---------------------------------
1850 l_filter_type := NULL;
1851 l_multi_select := 2;
1852 l_insert_what := '(';
1853 l_executed := NULL;
1854 IF l_rec_loads.ORIGIN_CITY <> g_select_all THEN
1855 DECLARE
1856 l_cities VARCHAR2(2000);
1857 l_field_name VARCHAR2(30);
1858 BEGIN
1859 l_cities := l_rec_loads.ORIGIN_CITY;
1860 l_field_name := 'ORIGIN_CITY';
1861 l_sequence := l_sequence +1 ;
1862 IF instrb(l_cities,l_separator) > 0 THEN
1863 --l_filter_type := 3;
1864 IF l_rec_loads.DESTINATION_CITY = g_select_all THEN
1865 l_filter_type := 3;
1866 ELSE
1867 l_filter_type := 4;
1868 END IF;
1869 l_multi_select:= 1;
1873 l_filter_type := 1;
1870 ELSE
1871 --l_filter_type := 1;
1872 IF l_rec_loads.DESTINATION_CITY = g_select_all THEN
1874 ELSE
1875 l_filter_type := 4;
1876 END IF;
1877 END IF;
1878 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1879 l_sequence ||l_delim||l_filter_type||l_delim||
1880 ''''||l_cities||''''||l_delim||
1881 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1882 l_active_flag||l_delim;
1883 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1884 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1885 END;
1886 END IF; -- end ORIGIN_CITY <> g_select_all
1887 l_filter_type := NULL;
1888 l_multi_select := 2;
1889 l_insert_what := '(';
1890 l_executed := NULL;
1891 IF l_rec_loads.DESTINATION_CITY <> g_select_all THEN
1892 DECLARE
1893 l_cities VARCHAR2(2000);
1894 l_field_name VARCHAR2(30);
1895 BEGIN
1896 l_cities := l_rec_loads.DESTINATION_CITY;
1897 l_field_name := 'DESTINATION_CITY';
1898 l_sequence := l_sequence +1 ;
1899 IF instrb(l_cities,l_separator) > 0 THEN
1900 --l_filter_type := 3;
1901 IF l_rec_loads.ORIGIN_CITY = g_select_all THEN
1902 l_filter_type := 3;
1903 ELSE
1904 l_filter_type := 4;
1905 END IF;
1906 l_multi_select:= 1;
1907 ELSE
1908 --l_filter_type := 1;
1909 IF l_rec_loads.ORIGIN_CITY = g_select_all THEN
1910 l_filter_type := 1;
1911 ELSE
1912 l_filter_type := 4;
1913 END IF;
1914 END IF;
1915 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1916 l_sequence ||l_delim||l_filter_type||l_delim||
1917 ''''||l_cities||''''||l_delim||
1918 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1919 l_active_flag||l_delim;
1920 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1921 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1922 END;
1923 END IF; -- end DESTINATION_CITY <> g_select_all
1924 -- ---------------------------------
1925 -- Criteria 3 - Range -(4)- State
1926 -- ---------------------------------
1927 l_filter_type := NULL;
1928 l_multi_select := 2;
1929 l_insert_what := '(';
1930 l_executed := NULL;
1931 IF l_rec_loads.ORIGIN_STATE <> g_select_all THEN
1932 DECLARE
1933 l_states VARCHAR2(2000);
1934 l_field_name VARCHAR2(30);
1935 BEGIN
1936 l_states := l_rec_loads.ORIGIN_STATE;
1937 l_field_name := 'ORIGIN_STATE';
1938 l_sequence := l_sequence +1 ;
1939 IF instrb(l_states,l_separator) > 0 THEN
1940 --l_filter_type := 3;
1941 IF l_rec_loads.DESTINATION_STATE = g_select_all THEN
1942 l_filter_type := 3;
1943 ELSE
1944 l_filter_type := 4;
1945 END IF;
1946 l_multi_select:= 1;
1947 ELSE
1948 --l_filter_type := 1;
1949 IF l_rec_loads.DESTINATION_STATE = g_select_all THEN
1950 l_filter_type := 1;
1951 ELSE
1952 l_filter_type := 4;
1953 END IF;
1954 END IF;
1955 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1956 l_sequence ||l_delim||l_filter_type||l_delim||
1957 ''''||l_states||''''||l_delim||
1958 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1959 l_active_flag||l_delim;
1960 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1961 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1962 END;
1963 END IF; -- end ORIGIN_STATE <> g_select_all
1964 l_filter_type := NULL;
1965 l_multi_select := 2;
1966 l_insert_what := '(';
1967 l_executed := NULL;
1968 IF l_rec_loads.DESTINATION_STATE <> g_select_all THEN
1969 DECLARE
1970 l_states VARCHAR2(2000);
1971 l_field_name VARCHAR2(30);
1972 BEGIN
1973 l_states := l_rec_loads.DESTINATION_STATE;
1974 l_field_name := 'DESTINATION_STATE';
1978 IF l_rec_loads.ORIGIN_STATE = g_select_all THEN
1975 l_sequence := l_sequence +1 ;
1976 IF instrb(l_states,l_separator) > 0 THEN
1977 --l_filter_type := 3;
1979 l_filter_type := 3;
1980 ELSE
1981 l_filter_type := 4;
1982 END IF;
1983 l_multi_select:= 1;
1984 ELSE
1985 --l_filter_type := 1;
1986 IF l_rec_loads.ORIGIN_STATE = g_select_all THEN
1987 l_filter_type := 1;
1988 ELSE
1989 l_filter_type := 4;
1990 END IF;
1991 END IF;
1992 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
1993 l_sequence ||l_delim||l_filter_type||l_delim||
1994 ''''||l_states||''''||l_delim||
1995 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
1996 l_active_flag||l_delim;
1997 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
1998 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
1999 END;
2000 END IF; -- end DESTINATION_STATE <> g_select_all
2001 -- ---------------------------------
2002 -- Criteria 3 - Range -(4)- Country
2003 -- ---------------------------------
2004 l_filter_type := NULL;
2005 l_multi_select := 2;
2006 l_insert_what := '(';
2007 l_executed := NULL;
2008 IF l_rec_loads.ORIGIN_COUNTRY <> g_select_all THEN
2009 DECLARE
2010 l_countries VARCHAR2(2000);
2011 l_field_name VARCHAR2(30);
2012 BEGIN
2013 l_countries := l_rec_loads.ORIGIN_COUNTRY;
2014 l_field_name := 'ORIGIN_COUNTRY';
2015 l_sequence := l_sequence +1 ;
2016 IF instrb(l_countries,l_separator) > 0 THEN
2017 --l_filter_type := 3;
2018 IF l_rec_loads.DESTINATION_COUNTRY = g_select_all THEN
2019 l_filter_type := 3;
2020 ELSE
2021 l_filter_type := 4;
2022 END IF;
2023 l_multi_select:= 1;
2024 ELSE
2025 --l_filter_type := 1;
2026 IF l_rec_loads.DESTINATION_COUNTRY = g_select_all THEN
2027 l_filter_type := 1;
2028 ELSE
2029 l_filter_type := 4;
2030 END IF;
2031 END IF;
2032 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2033 l_sequence ||l_delim||l_filter_type||l_delim||
2034 ''''||l_countries||''''||l_delim||
2035 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2036 l_active_flag||l_delim;
2037 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2038 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2039 END;
2040 END IF; -- end ORIGIN_COUNTRY <> g_select_all
2041 l_filter_type := NULL;
2042 l_multi_select := 2;
2043 l_insert_what := '(';
2044 l_executed := NULL;
2045 IF l_rec_loads.DESTINATION_COUNTRY <> g_select_all THEN
2046 DECLARE
2047 l_countries VARCHAR2(2000);
2048 l_field_name VARCHAR2(30);
2049 BEGIN
2050 l_countries := l_rec_loads.DESTINATION_COUNTRY;
2051 l_field_name := 'DESTINATION_COUNTRY';
2052 l_sequence := l_sequence +1 ;
2053 IF instrb(l_countries,l_separator) > 0 THEN
2054 --l_filter_type := 3;
2055 IF l_rec_loads.ORIGIN_COUNTRY = g_select_all THEN
2056 l_filter_type := 3;
2057 ELSE
2058 l_filter_type := 4;
2059 END IF;
2060 l_multi_select:= 1;
2061 ELSE
2062 --l_filter_type := 1;
2063 IF l_rec_loads.ORIGIN_COUNTRY = g_select_all THEN
2064 l_filter_type := 1;
2065 ELSE
2066 l_filter_type := 4;
2067 END IF;
2068 END IF;
2069 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2070 l_sequence ||l_delim||l_filter_type||l_delim||
2071 ''''||l_countries||''''||l_delim||
2072 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2073 l_active_flag||l_delim;
2074 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2075 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2076 END;
2077 END IF; -- end DESTINATION_COUNTRY <> g_select_all
2081 -- ---------------------------------
2078 END IF; -- end RANGE_TYPE
2079 -- ---------------------------------
2080 -- Criteria 4 - Carriers
2082 l_filter_type := NULL;
2083 l_multi_select := 2;
2084 l_insert_what := '(';
2085 l_executed := NULL;
2086 IF l_rec_loads.Carriers <> g_select_all THEN
2087 DECLARE
2088 l_Carriers VARCHAR2(2000);
2089 l_field_name VARCHAR2(30);
2090 BEGIN
2091 l_Carriers := l_rec_loads.Carrier_ids;
2092 l_field_name := 'CARRIER_ID';
2093 l_sequence := l_sequence +1 ;
2094 IF instrb(l_Carriers,l_separator) > 0 THEN
2095 l_filter_type := 3;
2096 l_multi_select:= 1;
2097 ELSE
2098 l_filter_type := 1;
2099 END IF;
2100 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2101 l_sequence ||l_delim||l_filter_type||l_delim||
2102 ''''||l_Carriers||''''||l_delim||
2103 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2104 l_active_flag||l_delim;
2105 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2106 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2107 IF l_executed = 0 THEN
2108 RAISE execution_failed;
2109 END IF;
2110 END;
2111 END IF; -- end Carriers <> g_select_all
2112 -- ---------------------------------
2113 -- Criteria 5 - Customers
2114 -- ---------------------------------
2115 l_filter_type := NULL;
2116 l_multi_select := 2;
2117 l_insert_what := '(';
2118 l_executed := NULL;
2119 IF l_rec_loads.Customers <> g_select_all THEN
2120 DECLARE
2121 l_Customers VARCHAR2(2000);
2122 l_field_name VARCHAR2(30);
2123 BEGIN
2124 l_Customers := l_rec_loads.Customer_ids;
2125 l_field_name := 'CUSTOMER_ID';
2126 l_sequence := l_sequence +1 ;
2127 IF instrb(l_Customers,l_separator) > 0 THEN
2128 l_filter_type := 3;
2129 l_multi_select:= 1;
2130 ELSE
2131 l_filter_type := 1;
2132 END IF;
2133 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2134 l_sequence ||l_delim||l_filter_type||l_delim||
2135 ''''||l_Customers||''''||l_delim||
2136 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2137 l_active_flag||l_delim;
2138 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2139 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2140 IF l_executed = 0 THEN
2141 RAISE execution_failed;
2142 END IF;
2143 END;
2144 END IF; -- end Customers <> g_select_all
2145 -- ---------------------------------
2146 -- Criteria 6 - Suppliers
2147 -- ---------------------------------
2148 l_filter_type := NULL;
2149 l_multi_select := 2;
2150 l_insert_what := '(';
2151 l_executed := NULL;
2152 IF l_rec_loads.Suppliers <> g_select_all THEN
2153 DECLARE
2154 l_Suppliers VARCHAR2(2000);
2155 l_field_name VARCHAR2(30);
2156 BEGIN
2157 l_Suppliers := l_rec_loads.Supplier_ids;
2158 l_field_name := 'SUPPLIER_ID';
2159 l_sequence := l_sequence +1 ;
2160 IF instrb(l_Suppliers,l_separator) > 0 THEN
2161 l_filter_type := 3;
2162 l_multi_select:= 1;
2163 ELSE
2164 l_filter_type := 1;
2165 END IF;
2166 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2167 l_sequence ||l_delim||l_filter_type||l_delim||
2168 ''''||l_Suppliers||''''||l_delim||
2169 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2170 l_active_flag||l_delim;
2171 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2172 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2173 IF l_executed = 0 THEN
2174 RAISE execution_failed;
2175 END IF;
2176 END;
2177 END IF; -- end Suppliers <> g_select_all
2178 -- ---------------------------------
2179 -- Criteria 7 - Cost
2180 -- ---------------------------------
2181 l_filter_type := NULL;
2182 l_multi_select := 2;
2183 l_insert_what := '(';
2184 l_executed := NULL;
2185 IF l_rec_loads.COST_FROM IS NOT NULL THEN
2186 DECLARE
2187 l_cost_from NUMBER;
2188 l_cost_to NUMBER;
2189 l_field_name VARCHAR2(30);
2190 BEGIN
2191 l_cost_from := l_rec_loads.COST_FROM;
2192 l_field_name := 'TOTAL_COST';
2196 IF l_filter_type = 4 THEN
2193 l_sequence := l_sequence +1 ;
2194 l_filter_type := l_rec_loads.COST_TYPE;
2195 l_multi_select:= 2;
2197 l_cost_to := l_rec_loads.COST_TO;
2198 END IF;
2199 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2200 l_sequence ||l_delim||l_filter_type||l_delim||
2201 ''''||l_cost_from||''''||l_delim||
2202 l_null_str||l_delim||''''||l_cost_to||''''||l_delim||
2203 l_multi_select||l_delim||
2204 l_active_flag||l_delim;
2205 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2206 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2207 IF l_executed = 0 THEN
2208 RAISE execution_failed;
2209 END IF;
2210 END;
2211 END IF; -- end COST_FROM IS NOT NULL
2212 -- ---------------------------------
2213 -- Criteria 8 - Weight
2214 -- ---------------------------------
2215 l_filter_type := NULL;
2216 l_multi_select := 2;
2217 l_insert_what := '(';
2218 l_executed := NULL;
2219 IF l_rec_loads.WEIGHT_FROM IS NOT NULL THEN
2220 DECLARE
2221 l_WEIGHT_from NUMBER;
2222 l_WEIGHT_to NUMBER;
2223 l_field_name VARCHAR2(30);
2224 BEGIN
2225 l_WEIGHT_from := l_rec_loads.WEIGHT_FROM;
2226 l_field_name := 'TOTAL_WEIGHT';
2227 l_sequence := l_sequence +1 ;
2228 l_filter_type := l_rec_loads.WEIGHT_TYPE;
2229 l_multi_select:= 2;
2230 IF l_filter_type = 4 THEN
2231 l_WEIGHT_to := l_rec_loads.WEIGHT_TO;
2232 END IF;
2233 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2234 l_sequence ||l_delim||l_filter_type||l_delim||
2235 ''''||l_WEIGHT_from||''''||l_delim||
2236 l_null_str||l_delim||''''||l_WEIGHT_to||''''||l_delim||
2237 l_multi_select||l_delim||
2238 l_active_flag||l_delim;
2239 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2240 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2241 IF l_executed = 0 THEN
2242 RAISE execution_failed;
2243 END IF;
2244 END;
2245 END IF; -- end WEIGHT_FROM IS NOT NULL
2246 -- ---------------------------------
2247 -- Criteria 9 - Cube
2248 -- ---------------------------------
2249 l_filter_type := NULL;
2250 l_multi_select := 2;
2251 l_insert_what := '(';
2252 l_executed := NULL;
2253 IF l_rec_loads.CUBE_FROM IS NOT NULL THEN
2254 DECLARE
2255 l_CUBE_from NUMBER;
2256 l_CUBE_to NUMBER;
2257 l_field_name VARCHAR2(30);
2258 BEGIN
2259 l_CUBE_from := l_rec_loads.CUBE_FROM;
2260 l_field_name := 'TOTAL_CUBE';
2261 l_sequence := l_sequence +1 ;
2262 l_filter_type := l_rec_loads.CUBE_TYPE;
2263 l_multi_select:= 2;
2264 IF l_filter_type = 4 THEN
2265 l_CUBE_to := l_rec_loads.CUBE_TO;
2266 END IF;
2267 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2268 l_sequence ||l_delim||l_filter_type||l_delim||
2269 ''''||l_CUBE_from||''''||l_delim||
2270 l_null_str||l_delim||''''||l_CUBE_to||''''||l_delim||
2271 l_multi_select||l_delim||
2272 l_active_flag||l_delim;
2273 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2274 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2275 IF l_executed = 0 THEN
2276 RAISE execution_failed;
2277 END IF;
2278 END;
2279 END IF; -- end CUBE_FROM IS NOT NULL
2280 -- ---------------------------------
2281 -- Criteria 10 - Utilization
2282 -- ---------------------------------
2283 l_filter_type := NULL;
2284 l_multi_select := 2;
2285 l_insert_what := '(';
2286 l_executed := NULL;
2287 IF l_rec_loads.UTILIZATION_FROM IS NOT NULL THEN
2288 DECLARE
2289 l_UTILIZATION_from NUMBER;
2290 l_UTILIZATION_to NUMBER;
2291 l_field_name VARCHAR2(30);
2292 BEGIN
2293 l_UTILIZATION_from := l_rec_loads.UTILIZATION_FROM;
2294 l_field_name := 'TOTAL_UTILIZATION';
2295 l_sequence := l_sequence +1 ;
2296 l_filter_type := l_rec_loads.UTILIZATION_TYPE;
2297 l_multi_select:= 2;
2298 IF l_filter_type = 4 THEN
2299 l_UTILIZATION_to := l_rec_loads.UTILIZATION_TO;
2300 END IF;
2301 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2305 l_multi_select||l_delim||
2302 l_sequence ||l_delim||l_filter_type||l_delim||
2303 ''''||l_UTILIZATION_from||''''||l_delim||
2304 l_null_str||l_delim||''''||l_UTILIZATION_to||''''||l_delim||
2306 l_active_flag||l_delim;
2307 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2308 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2309 IF l_executed = 0 THEN
2310 RAISE execution_failed;
2311 END IF;
2312 END;
2313 END IF; -- end UTILIZATION_FROM IS NOT NULL
2314 -- ---------------------------------
2315 -- Criteria 11 - Departure Time
2316 -- ---------------------------------
2317 l_filter_type := NULL;
2318 l_multi_select := 2;
2319 l_insert_what := '(';
2320 l_executed := NULL;
2321 IF l_rec_loads.DEPARTURE_TIME_FROM IS NOT NULL THEN
2322 DECLARE
2323 l_DEPARTURE_TIME_from NUMBER;
2324 l_DEPARTURE_TIME_to NUMBER;
2325 l_field_name VARCHAR2(30);
2326 BEGIN
2327 l_DEPARTURE_TIME_from := l_rec_loads.DEPARTURE_TIME_FROM;
2328 IF l_rec_loads.DEPARTURE_TIME_UNIT = 1 THEN
2329 l_field_name := 'DAYS_LEFT';
2330 ELSIF l_rec_loads.DEPARTURE_TIME_UNIT = 2 THEN
2331 l_field_name := 'HOURS_LEFT';
2332 END IF;
2333 l_sequence := l_sequence +1 ;
2334 l_filter_type := l_rec_loads.DEPARTURE_TIME_TYPE;
2335 l_multi_select:= 2;
2336 IF l_filter_type = 4 THEN
2337 l_DEPARTURE_TIME_to := l_rec_loads.DEPARTURE_TIME_TO;
2338 END IF;
2339 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2340 l_sequence ||l_delim||l_filter_type||l_delim||
2341 ''''||l_DEPARTURE_TIME_from||''''||l_delim||
2342 l_null_str||l_delim||''''||l_DEPARTURE_TIME_to||''''||l_delim||
2343 l_multi_select||l_delim||
2344 l_active_flag||l_delim;
2345 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2346 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2347 IF l_executed = 0 THEN
2348 RAISE execution_failed;
2349 END IF;
2350 END;
2351 END IF; -- end DEPARTURE_TIME_FROM IS NOT NULL
2352 EXCEPTION
2353 WHEN no_data_found THEN
2354 --KSA_DEBUG(SYSDATE,'query_id->'||p_query_id||'-'||sqlerrm(sqlcode),'insert_load_selection');
2355 RAISE;
2356 WHEN OTHERS THEN
2357 --KSA_DEBUG(SYSDATE,'query_id->'||p_query_id||'-'||sqlerrm(sqlcode),'insert_load_selection');
2358 RAISE;
2359 END insert_load_selection;
2360
2361 PROCEDURE insert_cm_selection(p_query_id IN NUMBER) IS
2362
2363 cursor cur_cms is
2364 select ALL_CM, CM_TRIP_NUMBERS , USER_EDITS_ONLY ,
2365 CARRIER_IDS ,
2366 CARRIERS , COST_TYPE ,
2367 COST_FROM , COST_TO ,
2368 DEPARTURE_TIME_TYPE, DEPARTURE_TIME_FROM,
2369 DEPARTURE_TIME_TO , DEPARTURE_TIME_UNIT
2370 from mst_cm_selection_criteria
2371 where query_id = p_query_id;
2372
2373 l_rec_loads cur_cms%ROWTYPE;
2374
2375 l_insert_begin VARCHAR2(500);
2376 l_insert_what VARCHAR2(3000);
2377 l_insert_who VARCHAR2(500);
2378 l_delete_str VARCHAR2(500);
2379 l_executed NUMBER ;
2380 l_filter_type NUMBER;
2381 l_sequence NUMBER ; --:= 0;
2382 l_multi_select NUMBER ; --:= 2; -- 1 true, 2 false
2383 l_active_flag NUMBER ; --:= 1;
2384 l_delim CONSTANT VARCHAR2(1) := ',';
2385 l_separator CONSTANT VARCHAR2(1):= ';';
2386 l_null_str CONSTANT VARCHAR2(6) := 'NULL';
2387 l_userid NUMBER ; --:= TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
2388 execution_failed EXCEPTION;
2389 BEGIN
2390 l_sequence := 0;
2391 l_multi_select := 2; -- 1 true, 2 false
2392 l_active_flag := 1;
2393 l_userid := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
2394 l_delete_str := 'DELETE MST_SELECTION_CRITERIA WHERE QUERY_ID = :P_QUERYID';
2395 l_executed := execute_dyn_sql(l_delete_str,p_query_id);
2396 IF l_executed = 0 THEN
2397 RAISE execution_failed;
2398 END IF;
2399 IF g_select_all IS NULL THEN
2400 FND_MESSAGE.set_name('MST','MST_PQ_ALL');
2401 g_select_all:= FND_MESSAGE.GET;
2402 --g_select_all := 'All';
2403 END IF;
2404 l_insert_begin := 'INSERT INTO MST_SELECTION_CRITERIA '||
2405 '(QUERY_ID , FIELD_NAME , SEQUENCE ,'||
2406 'FILTER_TYPE , FIELD_VALUE_FROM, DISPLAY_VALUE,'||
2407 'FIELD_VALUE_TO, MULTI_SELECT , ACTIVE_FLAG ,'||
2408 'CREATED_BY , CREATION_DATE ) VALUES ';
2409 OPEN cur_cms;
2410 FETCH cur_cms INTO l_rec_loads;
2414 END IF;
2411 IF cur_cms%NOTFOUND THEN
2412 CLOSE cur_cms;
2413 RAISE no_data_found;
2415 CLOSE cur_cms;
2416 l_insert_what := '(';
2417 l_executed := NULL;
2418 -- ---------------------------------
2419 -- Criteria 1 CM_TRIP_NUMBERS
2420 -- ---------------------------------
2421 IF l_rec_loads.CM_TRIP_NUMBERS IS NOT NULL THEN
2422 DECLARE
2423 l_cm_trip_numbers VARCHAR2(2000);
2424 BEGIN
2425 l_cm_trip_numbers := l_rec_loads.CM_TRIP_NUMBERS;
2426
2427 IF instrb(l_cm_trip_numbers,l_separator) > 0
2428 AND instrb(l_cm_trip_numbers,l_separator,-1)<>lengthb(l_cm_trip_numbers) THEN
2429 l_cm_trip_numbers := l_cm_trip_numbers||l_separator;
2430 END IF;
2431 l_sequence := l_sequence +1 ;
2432 IF instrb(l_cm_trip_numbers,l_separator) > 0 THEN
2433 l_filter_type := 3;
2434 l_multi_select:= 1;
2435 ELSE
2436 l_filter_type := 1;
2437 END IF;
2438 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''CM_TRIP_NUMBER'''||l_delim||
2439 l_sequence ||l_delim||l_filter_type||l_delim||
2440 ''''||l_cm_trip_numbers||''''||l_delim||
2441 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2442 l_active_flag||l_delim;
2443 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2444 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2445 IF l_executed = 0 THEN
2446 RAISE execution_failed;
2447 END IF;
2448 END;
2449 END IF; -- end CM_TRIP_NUMBERS
2450
2451 -- ---------------------------------
2452 -- Criteria 1a ALL_CM
2453 -- ---------------------------------
2454 l_filter_type := NULL;
2455 l_multi_select := 2;
2456 l_insert_what := '(';
2457 l_executed := NULL;
2458
2459 IF l_rec_loads.ALL_CM IS NOT NULL THEN
2460 DECLARE
2461 l_cm_trip_numbers VARCHAR2(2000):= '0';
2462 BEGIN
2463 l_sequence := l_sequence +1 ;
2464 l_filter_type := 7;
2465 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''CM_TRIP_NUMBER'''||l_delim||
2466 l_sequence ||l_delim||l_filter_type||l_delim||
2467 ''''||l_cm_trip_numbers||''''||l_delim||
2468 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2469 l_active_flag||l_delim;
2470 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2471 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2472 IF l_executed = 0 THEN
2473 RAISE execution_failed;
2474 END IF;
2475 END;
2476 END IF; -- end All_CM
2477
2478 -- ---------------------------------
2479 -- Criteria 1-B - USER EDITS
2480 -- ---------------------------------
2481 l_filter_type := NULL;
2482 l_multi_select := 2;
2483 l_insert_what := '(';
2484 l_executed := NULL;
2485 IF l_rec_loads.USER_EDITS_ONLY = 1 THEN
2486 DECLARE
2487 l_user_edits NUMBER;
2488 l_field_name VARCHAR2(30);
2489 BEGIN
2490 l_field_name := 'CHANGED_BY_USER';
2491 l_user_edits := 1;
2492 l_sequence := l_sequence +1 ;
2493 l_filter_type := 1;
2494 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2495 l_sequence ||l_delim||l_filter_type||l_delim||
2496 ''''||l_user_edits||''''||l_delim||
2497 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2498 l_active_flag||l_delim;
2499 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2500 --KSA_DEBUG(SYSDATE,L_INSERT_WHO,'INSERT LOAD - WHO');
2501 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2502 IF l_executed = 0 THEN
2503 RAISE execution_failed;
2504 END IF;
2505 END;
2506 END IF;
2507 -- ---------------------------------
2508 -- Criteria 2 Carriers
2509 -- ---------------------------------
2510 l_filter_type := NULL;
2511 l_multi_select := 2;
2512 l_insert_what := '(';
2513 l_executed := NULL;
2514 IF l_rec_loads.Carriers <> g_select_all THEN
2515 DECLARE
2516 l_Carriers VARCHAR2(2000);
2517 l_field_name VARCHAR2(30);
2518 BEGIN
2519 l_Carriers := l_rec_loads.Carrier_ids;
2520 l_field_name := 'CARRIER_ID';
2521 l_sequence := l_sequence +1 ;
2522 IF instrb(l_Carriers,l_separator) > 0 THEN
2523 l_filter_type := 3;
2527 END IF;
2524 l_multi_select:= 1;
2525 ELSE
2526 l_filter_type := 1;
2528 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2529 l_sequence ||l_delim||l_filter_type||l_delim||
2530 ''''||l_Carriers||''''||l_delim||
2531 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2532 l_active_flag||l_delim;
2533 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2534 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2535 IF l_executed = 0 THEN
2536 RAISE execution_failed;
2537 END IF;
2538 END;
2539 END IF; -- end Carriers <> g_select_all
2540 -- ---------------------------------
2541 -- Criteria 3 - Cost
2542 -- ---------------------------------
2543 l_filter_type := NULL;
2544 l_multi_select := 2;
2545 l_insert_what := '(';
2546 l_executed := NULL;
2547 IF l_rec_loads.COST_FROM IS NOT NULL THEN
2548 DECLARE
2549 l_cost_from NUMBER;
2550 l_cost_to NUMBER;
2551 l_field_name VARCHAR2(30);
2552 BEGIN
2553 l_cost_from := l_rec_loads.COST_FROM;
2554 l_field_name := 'TOTAL_COST';
2555 l_sequence := l_sequence +1 ;
2556 l_filter_type := l_rec_loads.COST_TYPE;
2557 l_multi_select:= 2;
2558 IF l_filter_type = 4 THEN
2559 l_cost_to := l_rec_loads.COST_TO;
2560 END IF;
2561 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2562 l_sequence ||l_delim||l_filter_type||l_delim||
2563 ''''||l_cost_from||''''||l_delim||
2564 l_null_str||l_delim||''''||l_cost_to||''''||l_delim||
2565 l_multi_select||l_delim||
2566 l_active_flag||l_delim;
2567 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2568 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2569 IF l_executed = 0 THEN
2570 RAISE execution_failed;
2571 END IF;
2572 END;
2573 END IF; -- end COST_FROM IS NOT NULL
2574 -- ---------------------------------
2575 -- Criteria 4 - Departure Time
2576 -- ---------------------------------
2577 l_filter_type := NULL;
2578 l_multi_select := 2;
2579 l_insert_what := '(';
2580 l_executed := NULL;
2581 IF l_rec_loads.DEPARTURE_TIME_FROM IS NOT NULL THEN
2582 DECLARE
2583 l_DEPARTURE_TIME_from NUMBER;
2584 l_DEPARTURE_TIME_to NUMBER;
2585 l_field_name VARCHAR2(30);
2586 BEGIN
2587 l_DEPARTURE_TIME_from := l_rec_loads.DEPARTURE_TIME_FROM;
2588 IF l_rec_loads.DEPARTURE_TIME_UNIT = 1 THEN
2589 l_field_name := 'DAYS_LEFT';
2590 ELSIF l_rec_loads.DEPARTURE_TIME_UNIT = 2 THEN
2591 l_field_name := 'HOURS_LEFT';
2592 END IF;
2593 l_sequence := l_sequence +1 ;
2594 l_filter_type := l_rec_loads.DEPARTURE_TIME_TYPE;
2595 l_multi_select:= 2;
2596 IF l_filter_type = 4 THEN
2597 l_DEPARTURE_TIME_to := l_rec_loads.DEPARTURE_TIME_TO;
2598 END IF;
2599 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2600 l_sequence ||l_delim||l_filter_type||l_delim||
2601 ''''||l_DEPARTURE_TIME_from||''''||l_delim||
2602 l_null_str||l_delim||''''||l_DEPARTURE_TIME_to||''''||l_delim||
2603 l_multi_select||l_delim||
2604 l_active_flag||l_delim;
2605 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2606 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2607 IF l_executed = 0 THEN
2608 RAISE execution_failed;
2609 END IF;
2610 END;
2611 END IF; -- end DEPARTURE_TIME_FROM IS NOT NULL
2612 EXCEPTION
2613 WHEN OTHERS THEN
2614 RAISE;
2615 END insert_cm_selection;
2616
2617 PROCEDURE insert_order_selection(p_query_id IN NUMBER) IS
2618 CURSOR cur_orders IS
2619 SELECT ALL_SALES_ORDERS , SALES_ORDER_NUMBERS,
2620 ALL_PURCHASE_ORDERS, PURCHASE_ORDER_NUMBERS,
2621 ALL_OTHER_ORDERS , OTHER_ORDER_NUMBERS,
2622 RANGE_TYPE ,
2623 ORIGIN_FACILITIES , DESTINATION_FACILITIES,
2624 ORIGIN_FACILITY_IDS, DESTINATION_FACILITY_IDS,
2625 ORIGIN_ZIP , DESTINATION_ZIP ,
2626 ORIGIN_CITY , DESTINATION_CITY ,
2627 ORIGIN_STATE , DESTINATION_STATE ,
2628 ORIGIN_COUNTRY , DESTINATION_COUNTRY ,
2629 ITEMS , INVENTORY_ITEM_IDS ,
2630 CUSTOMERS , CUSTOMER_IDS ,
2631 SUPPLIERS , SUPPLIER_IDS ,
2635 WHERE query_id = p_query_id;
2632 WEIGHT_TYPE , WEIGHT_FROM , WEIGHT_TO ,
2633 CUBE_TYPE , CUBE_FROM , CUBE_TO
2634 FROM mst_order_selection_criteria
2636
2637 l_rec_orders cur_orders%ROWTYPE;
2638
2639 l_insert_begin VARCHAR2(500);
2640 l_insert_what VARCHAR2(3000);
2641 l_insert_who VARCHAR2(500);
2642 l_delete_str VARCHAR2(500);
2643 l_executed NUMBER ;
2644 l_filter_type NUMBER;
2645 l_sequence NUMBER ;--:= 0;
2646 l_multi_select NUMBER ; --:= 2; -- 1 true, 2 false
2647 l_active_flag NUMBER ; --:= 1;
2648 l_delim CONSTANT VARCHAR2(1) := ',';
2649 l_separator CONSTANT VARCHAR2(1):= ';';
2650 l_null_str CONSTANT VARCHAR2(6) := 'NULL';
2651 l_userid NUMBER ; --:= TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
2652 execution_failed EXCEPTION;
2653 BEGIN
2654 l_sequence := 0;
2655 l_multi_select := 2; -- 1 true, 2 false
2656 l_active_flag := 1;
2657 l_userid := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
2658 l_delete_str := 'DELETE MST_SELECTION_CRITERIA WHERE QUERY_ID = :P_QUERYID';
2659 l_executed := execute_dyn_sql(l_delete_str,p_query_id);
2660 IF l_executed = 0 THEN
2661 RAISE execution_failed;
2662 END IF;
2663 IF g_select_all IS NULL THEN
2664 FND_MESSAGE.set_name('MST','MST_PQ_ALL');
2665 g_select_all:= FND_MESSAGE.GET;
2666 --g_select_all := 'All';
2667 END IF;
2668 l_insert_begin := 'INSERT INTO MST_SELECTION_CRITERIA '||
2669 '(QUERY_ID , FIELD_NAME , SEQUENCE ,'||
2670 'FILTER_TYPE , FIELD_VALUE_FROM, DISPLAY_VALUE,'||
2671 'FIELD_VALUE_TO, MULTI_SELECT , ACTIVE_FLAG ,'||
2672 'CREATED_BY , CREATION_DATE ) VALUES ';
2673 OPEN cur_orders;
2674 FETCH cur_orders INTO l_rec_orders;
2675 IF cur_orders%NOTFOUND THEN
2676 CLOSE cur_orders;
2677 RAISE no_data_found;
2678 END IF;
2679 CLOSE cur_orders;
2680 l_filter_type := NULL;
2681 l_multi_select := 2;
2682 l_insert_what := '(';
2683 l_executed := NULL;
2684 -- ---------------------------------
2685 -- Criteria 1- mode of Transport
2686 -- ---------------------------------
2687 IF l_rec_orders.ALL_SALES_ORDERS = 1
2688 OR l_rec_orders.ALL_PURCHASE_ORDERS = 1
2689 OR l_rec_orders.ALL_OTHER_ORDERS = 1 THEN
2690 DECLARE
2691 l_orders VARCHAR2(50);
2692 BEGIN
2693 IF l_rec_orders.ALL_SALES_ORDERS = 1 THEN
2694 --l_orders := 'SO';
2695 l_orders := 'OE';
2696 END IF;
2697 IF l_rec_orders.ALL_PURCHASE_ORDERS =1 THEN
2698 IF l_orders IS NOT NULL THEN
2699 l_orders := l_orders||l_separator||'PO';
2700 ELSE
2701 l_orders := 'PO';
2702 END IF;
2703 END IF;
2704 IF l_rec_orders.ALL_OTHER_ORDERS =1 THEN
2705 IF l_orders IS NOT NULL THEN
2706 l_orders := l_orders||l_separator||'XXXXX';
2707 ELSE
2708 l_orders := 'XXXXX';
2709 END IF;
2710 END IF;
2711 l_sequence := l_sequence +1 ;
2712 IF instrb(l_orders,l_separator) > 0 THEN
2713 l_filter_type := 3;
2714 l_multi_select:= 1;
2715 ELSE
2716 l_filter_type := 1;
2717 END IF;
2718 --l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''REFERENCE_SOURCE_TYPE'''||l_delim||
2719 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''SOURCE_CODE'''||l_delim||
2720 l_sequence ||l_delim||l_filter_type||l_delim||
2721 ''''||l_orders||''''||l_delim||
2722 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2723 l_active_flag||l_delim;
2724 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2725 --KSA_DEBUG(SYSDATE,L_INSERT_WHO,'INSERT LOAD - WHO');
2726 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2727 IF l_executed = 0 THEN
2728 RAISE execution_failed;
2729 END IF;
2730 END;
2731 END IF;
2732 -- ---------------------------------
2733 -- Criteria 1 - Order Numbers
2734 -- ---------------------------------
2735 l_filter_type := NULL;
2736 l_multi_select := 2;
2737 l_insert_what := '(';
2738 l_executed := NULL;
2739 IF l_rec_orders.SALES_ORDER_NUMBERS IS NOT NULL
2740 OR l_rec_orders.PURCHASE_ORDER_NUMBERS IS NOT NULL
2741 OR l_rec_orders.OTHER_ORDER_NUMBERS IS NOT NULL THEN
2742 DECLARE
2743 l_order_numbers VARCHAR2(2000);
2744 BEGIN
2745 IF NVL(l_rec_orders.ALL_SALES_ORDERS,2) = 2
2746 AND l_rec_orders.SALES_ORDER_NUMBERS IS NOT NULL THEN
2747 l_order_numbers := l_rec_orders.SALES_ORDER_NUMBERS;
2751 AND l_rec_orders.PURCHASE_ORDER_NUMBERS IS NOT NULL THEN
2748 END IF;
2749
2750 IF NVL(l_rec_orders.ALL_PURCHASE_ORDERS,2) = 2
2752 IF l_order_numbers IS NOT NULL THEN
2753 IF instrb(l_order_numbers,l_separator,-1)<>lengthb(l_order_numbers) THEN
2754 l_order_numbers := l_order_numbers||l_separator||l_rec_orders.PURCHASE_ORDER_NUMBERS;
2755 ELSE
2756 l_order_numbers := l_order_numbers||l_rec_orders.PURCHASE_ORDER_NUMBERS;
2757 END IF;
2758 ELSE
2759 l_order_numbers := l_rec_orders.PURCHASE_ORDER_NUMBERS;
2760 END IF;
2761 END IF;
2762 IF NVL(l_rec_orders.ALL_OTHER_ORDERS,2) = 2
2763 AND l_rec_orders.OTHER_ORDER_NUMBERS IS NOT NULL THEN
2764 IF l_order_numbers IS NOT NULL THEN
2765 IF instrb(l_order_numbers,l_separator,-1)<>lengthb(l_order_numbers) THEN
2766 l_order_numbers := l_order_numbers||l_separator||l_rec_orders.OTHER_ORDER_NUMBERS;
2767 ELSE
2768 l_order_numbers := l_order_numbers||l_rec_orders.OTHER_ORDER_NUMBERS;
2769 END IF;
2770 ELSE
2771 l_order_numbers := l_rec_orders.OTHER_ORDER_NUMBERS;
2772 END IF;
2773 END IF;
2774 IF instrb(l_order_numbers,l_separator) > 0
2775 AND instrb(l_order_numbers,l_separator,-1)<>lengthb(l_order_numbers) THEN
2776 l_order_numbers := l_order_numbers||l_separator;
2777 END IF;
2778 l_sequence := l_sequence +1 ;
2779 IF instrb(l_order_numbers,l_separator) > 0 THEN
2780 l_filter_type := 3;
2781 l_multi_select:= 1;
2782 ELSE
2783 l_filter_type := 1;
2784 END IF;
2785 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''ORDER_NUMBER'''||l_delim||
2786 l_sequence ||l_delim||l_filter_type||l_delim||
2787 ''''||l_order_numbers||''''||l_delim||
2788 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2789 l_active_flag||l_delim;
2790 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2791 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2792 IF l_executed = 0 THEN
2793 RAISE execution_failed;
2794 END IF;
2795 END;
2796 END IF;
2797 -- -------------------
2798 -- Criteria 2 - Range.
2799 -- -------------------
2800 l_filter_type := NULL;
2801 l_multi_select := 2;
2802 l_insert_what := '(';
2803 l_executed := NULL;
2804 IF l_rec_orders.RANGE_TYPE IN (1,2) THEN
2805 -- -----------------------------------
2806 -- Criteria 2 - Range -(1,2)- Facility
2807 -- -----------------------------------
2808 IF l_rec_orders.ORIGIN_FACILITIES <> g_select_all THEN
2809 DECLARE
2810 l_facilities VARCHAR2(2000);
2811 l_field_name VARCHAR2(30);
2812 BEGIN
2813 l_facilities := l_rec_orders.ORIGIN_FACILITY_IDS;
2814
2815 IF l_rec_orders.RANGE_TYPE = 1 THEN
2816 l_field_name := 'ORIGIN_FACILITY_ID';
2817 ELSIF l_rec_orders.RANGE_TYPE = 2 THEN
2818 l_field_name := 'DESTINATION_FACILITY_ID';
2819 END IF;
2820 l_sequence := l_sequence +1 ;
2821 IF instrb(l_facilities,l_separator) > 0 THEN
2822 l_filter_type := 3;
2823 l_multi_select:= 1;
2824 ELSE
2825 l_filter_type := 1;
2826 END IF;
2827 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2828 l_sequence ||l_delim||l_filter_type||l_delim||
2829 ''''||l_facilities||''''||l_delim||
2830 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2831 l_active_flag||l_delim;
2832 --KSA_DEBUG(SYSDATE,L_INSERT_WHAT,'INSERT LOAD - WHAT');
2833 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2834 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2835 END;
2836 END IF; -- end ORIGIN_FACILITIES <> g_select_all
2837 -- -----------------------------------
2838 -- Criteria 2 - Range -(1,2)- Zip
2839 -- -----------------------------------
2840 l_filter_type := NULL;
2841 l_multi_select := 2;
2842 l_insert_what := '(';
2843 l_executed := NULL;
2844 IF l_rec_orders.ORIGIN_ZIP <> g_select_all THEN
2845 DECLARE
2846 l_postalcodes VARCHAR2(2000);
2847 l_field_name VARCHAR2(30);
2848 BEGIN
2849 l_postalcodes := l_rec_orders.ORIGIN_ZIP;
2850
2851 IF l_rec_orders.RANGE_TYPE = 1 THEN
2852 l_field_name := 'ORIGIN_ZIP';
2853 ELSIF l_rec_orders.RANGE_TYPE = 2 THEN
2854 l_field_name := 'DESTINATION_ZIP';
2855 END IF;
2859 l_multi_select:= 1;
2856 l_sequence := l_sequence +1 ;
2857 IF instrb(l_postalcodes,l_separator) > 0 THEN
2858 l_filter_type := 3;
2860 ELSE
2861 l_filter_type := 1;
2862 END IF;
2863 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2864 l_sequence ||l_delim||l_filter_type||l_delim||
2865 ''''||l_postalcodes||''''||l_delim||
2866 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2867 l_active_flag||l_delim;
2868 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2869 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2870 END;
2871 END IF; -- end ORIGIN_ZIP <> g_select_all
2872 -- -----------------------------------
2873 -- Criteria 2 - Range -(1,2)- City
2874 -- -----------------------------------
2875 l_filter_type := NULL;
2876 l_multi_select := 2;
2877 l_insert_what := '(';
2878 l_executed := NULL;
2879 IF l_rec_orders.ORIGIN_CITY <> g_select_all THEN
2880 DECLARE
2881 l_cities VARCHAR2(2000);
2882 l_field_name VARCHAR2(30);
2883 BEGIN
2884 l_cities := l_rec_orders.ORIGIN_CITY;
2885
2886 IF l_rec_orders.RANGE_TYPE = 1 THEN
2887 l_field_name := 'ORIGIN_CITY';
2888 ELSIF l_rec_orders.RANGE_TYPE = 2 THEN
2889 l_field_name := 'DESTINATION_CITY';
2890 END IF;
2891 l_sequence := l_sequence +1 ;
2892 IF instrb(l_cities,l_separator) > 0 THEN
2893 l_filter_type := 3;
2894 l_multi_select:= 1;
2895 ELSE
2896 l_filter_type := 1;
2897 END IF;
2898 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2899 l_sequence ||l_delim||l_filter_type||l_delim||
2900 ''''||l_cities||''''||l_delim||
2901 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2902 l_active_flag||l_delim;
2903 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2904 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2905 END;
2906 END IF; -- end ORIGIN_CITY <> g_select_all
2907 -- -----------------------------------
2908 -- Criteria 2 - Range -(1,2)- State
2909 -- -----------------------------------
2910 l_filter_type := NULL;
2911 l_multi_select := 2;
2912 l_insert_what := '(';
2913 l_executed := NULL;
2914 IF l_rec_orders.ORIGIN_STATE <> g_select_all THEN
2915 DECLARE
2916 l_states VARCHAR2(2000);
2917 l_field_name VARCHAR2(30);
2918 BEGIN
2919 l_states := l_rec_orders.ORIGIN_STATE;
2920
2921 IF l_rec_orders.RANGE_TYPE = 1 THEN
2922 l_field_name := 'ORIGIN_STATE';
2923 ELSIF l_rec_orders.RANGE_TYPE = 2 THEN
2924 l_field_name := 'DESTINATION_STATE';
2925 END IF;
2926 l_sequence := l_sequence +1 ;
2927 IF instrb(l_states,l_separator) > 0 THEN
2928 l_filter_type := 3;
2929 l_multi_select:= 1;
2930 ELSE
2931 l_filter_type := 1;
2932 END IF;
2933 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2934 l_sequence ||l_delim||l_filter_type||l_delim||
2935 ''''||l_states||''''||l_delim||
2936 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2937 l_active_flag||l_delim;
2938 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2939 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2940 END;
2941 END IF; -- end ORIGIN_STATE <> g_select_all
2942 -- -----------------------------------
2943 -- Criteria 2 - Range -(1,2)- Country
2944 -- -----------------------------------
2945 l_filter_type := NULL;
2946 l_multi_select := 2;
2947 l_insert_what := '(';
2948 l_executed := NULL;
2949 IF l_rec_orders.ORIGIN_COUNTRY <> g_select_all THEN
2950 DECLARE
2951 l_countries VARCHAR2(2000);
2952 l_field_name VARCHAR2(30);
2953 BEGIN
2954 l_countries := l_rec_orders.ORIGIN_COUNTRY;
2955
2956 IF l_rec_orders.RANGE_TYPE = 1 THEN
2957 l_field_name := 'ORIGIN_COUNTRY';
2958 ELSIF l_rec_orders.RANGE_TYPE = 2 THEN
2959 l_field_name := 'DESTINATION_COUNTRY';
2960 END IF;
2964 l_multi_select:= 1;
2961 l_sequence := l_sequence +1 ;
2962 IF instrb(l_countries,l_separator) > 0 THEN
2963 l_filter_type := 3;
2965 ELSE
2966 l_filter_type := 1;
2967 END IF;
2968 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2969 l_sequence ||l_delim||l_filter_type||l_delim||
2970 ''''||l_countries||''''||l_delim||
2971 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
2972 l_active_flag||l_delim;
2973 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
2974 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
2975 END;
2976 END IF; -- end ORIGIN_COUNTRY <> g_select_all
2977 ELSIF l_rec_orders.RANGE_TYPE = 3 THEN
2978 -- ---------------------------------
2979 -- Criteria 2 - Range -(3)- Facility
2980 -- ---------------------------------
2981 IF l_rec_orders.ORIGIN_FACILITIES <> g_select_all THEN
2982 DECLARE
2983 l_facilities VARCHAR2(2000);
2984 l_field_name VARCHAR2(30);
2985 BEGIN
2986 l_facilities := l_rec_orders.ORIGIN_FACILITY_IDS;
2987 l_field_name := 'ORIGIN_FACILITY_ID';
2988 l_sequence := l_sequence +1 ;
2989 IF instrb(l_facilities,l_separator) > 0 THEN
2990 --l_filter_type := 3;
2991 l_multi_select:= 1;
2992 ELSE
2993 --l_filter_type := 1;
2994 l_multi_select:= 2;
2995 END IF;
2996 l_filter_type := 10;
2997 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
2998 l_sequence ||l_delim||l_filter_type||l_delim||
2999 ''''||l_facilities||''''||l_delim||
3000 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3001 l_active_flag||l_delim;
3002 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3003 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3004
3005 l_filter_type := NULL;
3006 l_multi_select := 2;
3007 l_insert_what := '(';
3008 l_executed := NULL;
3009 l_field_name := 'DESTINATION_FACILITY_ID';
3010 l_sequence := l_sequence +1 ;
3011 IF instrb(l_facilities,l_separator) > 0 THEN
3012 --l_filter_type := 3;
3013 l_multi_select:= 1;
3014 ELSE
3015 --l_filter_type := 1;
3016 l_multi_select:= 2;
3017 END IF;
3018 l_filter_type := 10;
3019 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3020 l_sequence ||l_delim||l_filter_type||l_delim||
3021 ''''||l_facilities||''''||l_delim||
3022 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3023 l_active_flag||l_delim;
3024 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3025 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3026 END;
3027 END IF; -- end ORIGIN_FACILITIES <> g_select_all
3028 -- ---------------------------------
3029 -- Criteria 2 - Range -(3)- Zip
3030 -- ---------------------------------
3031 l_filter_type := NULL;
3032 l_multi_select := 2;
3033 l_insert_what := '(';
3034 l_executed := NULL;
3035 IF l_rec_orders.ORIGIN_ZIP <> g_select_all THEN
3036 DECLARE
3037 l_postalcodes VARCHAR2(2000);
3038 l_field_name VARCHAR2(30);
3039 BEGIN
3040 l_postalcodes := l_rec_orders.ORIGIN_ZIP;
3041 l_field_name := 'ORIGIN_ZIP';
3042 l_sequence := l_sequence +1 ;
3043 IF instrb(l_postalcodes,l_separator) > 0 THEN
3044 --l_filter_type := 3;
3045 l_multi_select:= 1;
3046 ELSE
3047 --l_filter_type := 1;
3048 l_multi_select:= 2;
3049 END IF;
3050 l_filter_type := 10;
3051 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3052 l_sequence ||l_delim||l_filter_type||l_delim||
3053 ''''||l_postalcodes||''''||l_delim||
3054 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3055 l_active_flag||l_delim;
3056 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3057 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3058
3059 l_filter_type := NULL;
3060 l_multi_select := 2;
3064 l_sequence := l_sequence +1 ;
3061 l_insert_what := '(';
3062 l_executed := NULL;
3063 l_field_name := 'DESTINATION_ZIP';
3065 IF instrb(l_postalcodes,l_separator) > 0 THEN
3066 --l_filter_type := 3;
3067 l_multi_select:= 1;
3068 ELSE
3069 --l_filter_type := 1;
3070 l_multi_select:= 2;
3071 END IF;
3072 l_filter_type := 10;
3073 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3074 l_sequence ||l_delim||l_filter_type||l_delim||
3075 ''''||l_postalcodes||''''||l_delim||
3076 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3077 l_active_flag||l_delim;
3078 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3079 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3080 END;
3081 END IF; -- end ORIGIN_ZIP <> g_select_all
3082 -- ---------------------------------
3083 -- Criteria 2 - Range -(3)- City
3084 -- ---------------------------------
3085 l_filter_type := NULL;
3086 l_multi_select := 2;
3087 l_insert_what := '(';
3088 l_executed := NULL;
3089 IF l_rec_orders.ORIGIN_CITY <> g_select_all THEN
3090 DECLARE
3091 l_cities VARCHAR2(2000);
3092 l_field_name VARCHAR2(30);
3093 BEGIN
3094 l_cities := l_rec_orders.ORIGIN_CITY;
3095 l_field_name := 'ORIGIN_CITY';
3096 l_sequence := l_sequence +1 ;
3097 IF instrb(l_cities,l_separator) > 0 THEN
3098 --l_filter_type := 3;
3099 l_multi_select:= 1;
3100 ELSE
3101 --l_filter_type := 1;
3102 l_multi_select:= 2;
3103 END IF;
3104 l_filter_type := 10;
3105 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3106 l_sequence ||l_delim||l_filter_type||l_delim||
3107 ''''||l_cities||''''||l_delim||
3108 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3109 l_active_flag||l_delim;
3110 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3111 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3112
3113 l_filter_type := NULL;
3114 l_multi_select := 2;
3115 l_insert_what := '(';
3116 l_executed := NULL;
3117 l_field_name := 'DESTINATION_CITY';
3118 l_sequence := l_sequence +1 ;
3119 IF instrb(l_cities,l_separator) > 0 THEN
3120 --l_filter_type := 3;
3121 l_multi_select:= 1;
3122 ELSE
3123 --l_filter_type := 1;
3124 l_multi_select:= 2;
3125 END IF;
3126 l_filter_type := 10;
3127 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3128 l_sequence ||l_delim||l_filter_type||l_delim||
3129 ''''||l_cities||''''||l_delim||
3130 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3131 l_active_flag||l_delim;
3132 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3133 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3134 END;
3135 END IF; -- end ORIGIN_CITY <> g_select_all
3136 -- ---------------------------------
3137 -- Criteria 2 - Range -(3)- State
3138 -- ---------------------------------
3139 l_filter_type := NULL;
3140 l_multi_select := 2;
3141 l_insert_what := '(';
3142 l_executed := NULL;
3143 IF l_rec_orders.ORIGIN_STATE <> g_select_all THEN
3144 DECLARE
3145 l_states VARCHAR2(2000);
3146 l_field_name VARCHAR2(30);
3147 BEGIN
3148 l_states := l_rec_orders.ORIGIN_STATE;
3149 l_field_name := 'ORIGIN_STATE';
3150 l_sequence := l_sequence +1 ;
3151 IF instrb(l_states,l_separator) > 0 THEN
3152 --l_filter_type := 3;
3153 l_multi_select:= 1;
3154 ELSE
3155 --l_filter_type := 1;
3156 l_multi_select:= 2;
3157 END IF;
3158 l_filter_type := 10;
3159 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3160 l_sequence ||l_delim||l_filter_type||l_delim||
3161 ''''||l_states||''''||l_delim||
3162 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3163 l_active_flag||l_delim;
3167 l_filter_type := NULL;
3164 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3165 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3166
3168 l_multi_select := 2;
3169 l_insert_what := '(';
3170 l_executed := NULL;
3171 l_field_name := 'DESTINATION_STATE';
3172 l_sequence := l_sequence +1 ;
3173 IF instrb(l_states,l_separator) > 0 THEN
3174 --l_filter_type := 3;
3175 l_multi_select:= 1;
3176 ELSE
3177 --l_filter_type := 1;
3178 l_multi_select:= 2;
3179 END IF;
3180 l_filter_type := 10;
3181 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3182 l_sequence ||l_delim||l_filter_type||l_delim||
3183 ''''||l_states||''''||l_delim||
3184 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3185 l_active_flag||l_delim;
3186 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3187 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3188 END;
3189 END IF; -- end ORIGIN_STATE <> g_select_all
3190 -- ---------------------------------
3191 -- Criteria 2 - Range -(3)- Country
3192 -- ---------------------------------
3193 l_filter_type := NULL;
3194 l_multi_select := 2;
3195 l_insert_what := '(';
3196 l_executed := NULL;
3197 IF l_rec_orders.ORIGIN_COUNTRY <> g_select_all THEN
3198 DECLARE
3199 l_countries VARCHAR2(2000);
3200 l_field_name VARCHAR2(30);
3201 BEGIN
3202 l_countries := l_rec_orders.ORIGIN_COUNTRY;
3203 l_field_name := 'ORIGIN_COUNTRY';
3204 l_sequence := l_sequence +1 ;
3205 IF instrb(l_countries,l_separator) > 0 THEN
3206 --l_filter_type := 3;
3207 l_multi_select:= 1;
3208 ELSE
3209 --l_filter_type := 1;
3210 l_multi_select:= 2;
3211 END IF;
3212 l_filter_type := 10;
3213 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3214 l_sequence ||l_delim||l_filter_type||l_delim||
3215 ''''||l_countries||''''||l_delim||
3216 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3217 l_active_flag||l_delim;
3218 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3219 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3220
3221 l_filter_type := NULL;
3222 l_multi_select := 2;
3223 l_insert_what := '(';
3224 l_executed := NULL;
3225 l_field_name := 'DESTINATION_COUNTRY';
3226 l_sequence := l_sequence +1 ;
3227 IF instrb(l_countries,l_separator) > 0 THEN
3228 --l_filter_type := 3;
3229 l_multi_select:= 1;
3230 ELSE
3231 --l_filter_type := 1;
3232 l_multi_select:= 2;
3233 END IF;
3234 l_filter_type := 10;
3235 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3236 l_sequence ||l_delim||l_filter_type||l_delim||
3237 ''''||l_countries||''''||l_delim||
3238 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3239 l_active_flag||l_delim;
3240 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3241 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3242 END;
3243 END IF; -- end ORIGIN_COUNTRY <> g_select_all
3244 ELSIF l_rec_orders.RANGE_TYPE = 4 THEN
3245 -- ---------------------------------
3246 -- Criteria 2 - Range -(4)- Facility
3247 -- ---------------------------------
3248 IF l_rec_orders.ORIGIN_FACILITIES <> g_select_all THEN
3249 DECLARE
3250 l_facilities VARCHAR2(2000);
3251 l_field_name VARCHAR2(30);
3252 BEGIN
3253 l_facilities := l_rec_orders.ORIGIN_FACILITY_IDS;
3254 l_field_name := 'ORIGIN_FACILITY_ID';
3255 l_sequence := l_sequence +1 ;
3256 IF instrb(l_facilities,l_separator) > 0 THEN
3257 --l_filter_type := 3;
3258 IF l_rec_orders.DESTINATION_FACILITIES = g_select_all THEN
3259 l_filter_type := 3;
3260 ELSE
3261 l_filter_type := 4;
3262 END IF;
3263 l_multi_select:= 1;
3264 ELSE
3265 NULL;
3266 --l_filter_type := 1;
3267 IF l_rec_orders.DESTINATION_FACILITIES = g_select_all THEN
3271 END IF;
3268 l_filter_type := 1;
3269 ELSE
3270 l_filter_type := 4;
3272 END IF;
3273 --l_filter_type := 4;
3274 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3275 l_sequence ||l_delim||l_filter_type||l_delim||
3276 ''''||l_facilities||''''||l_delim||
3277 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3278 l_active_flag||l_delim;
3279 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3280 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3281 END;
3282 END IF; -- end ORIGIN_FACILITY <> g_select_all
3283 l_filter_type := NULL;
3284 l_multi_select := 2;
3285 l_insert_what := '(';
3286 l_executed := NULL;
3287 IF l_rec_orders.DESTINATION_FACILITIES <> g_select_all THEN
3288 DECLARE
3289 l_facilities VARCHAR2(2000);
3290 l_field_name VARCHAR2(30);
3291 BEGIN
3292 l_facilities := l_rec_orders.DESTINATION_FACILITY_IDS;
3293 l_field_name := 'DESTINATION_FACILITY_ID';
3294 l_sequence := l_sequence +1 ;
3295 IF instrb(l_facilities,l_separator) > 0 THEN
3296 IF l_rec_orders.ORIGIN_FACILITIES = g_select_all THEN
3297 l_filter_type := 3;
3298 ELSE
3299 l_filter_type := 4;
3300 END IF;
3301 l_multi_select:= 1;
3302 ELSE
3303 IF l_rec_orders.ORIGIN_FACILITIES = g_select_all THEN
3304 l_filter_type := 1;
3305 ELSE
3306 l_filter_type := 4;
3307 END IF;
3308 END IF;
3309 --l_filter_type := 4;
3310 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3311 l_sequence ||l_delim||l_filter_type||l_delim||
3312 ''''||l_facilities||''''||l_delim||
3313 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3314 l_active_flag||l_delim;
3315 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3316 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3317 END;
3318 END IF; -- end DESTINATION_FACILITIES <> g_select_all
3319 -- ---------------------------------
3320 -- Criteria 2 - Range -(4)- Zip
3321 -- ---------------------------------
3322 l_filter_type := NULL;
3323 l_multi_select := 2;
3324 l_insert_what := '(';
3325 l_executed := NULL;
3326 IF l_rec_orders.ORIGIN_ZIP <> g_select_all THEN
3327 DECLARE
3328 l_postalcodes VARCHAR2(2000);
3329 l_field_name VARCHAR2(30);
3330 BEGIN
3331 l_postalcodes := l_rec_orders.ORIGIN_ZIP;
3332 l_field_name := 'ORIGIN_ZIP';
3333 l_sequence := l_sequence +1 ;
3334 IF instrb(l_postalcodes,l_separator) > 0 THEN
3335 IF l_rec_orders.DESTINATION_ZIP = g_select_all THEN
3336 l_filter_type := 3;
3337 ELSE
3338 l_filter_type := 4;
3339 END IF;
3340 l_multi_select:= 1;
3341 ELSE
3342 IF l_rec_orders.DESTINATION_ZIP = g_select_all THEN
3343 l_filter_type := 1;
3344 ELSE
3345 l_filter_type := 4;
3346 END IF;
3347 END IF;
3348 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3349 l_sequence ||l_delim||l_filter_type||l_delim||
3350 ''''||l_postalcodes||''''||l_delim||
3351 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3352 l_active_flag||l_delim;
3353 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3354 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3355 END;
3356 END IF; -- end ORIGIN_ZIP <> g_select_all
3357 l_filter_type := NULL;
3358 l_multi_select := 2;
3359 l_insert_what := '(';
3360 l_executed := NULL;
3361 IF l_rec_orders.DESTINATION_ZIP <> g_select_all THEN
3362 DECLARE
3363 l_postalcodes VARCHAR2(2000);
3364 l_field_name VARCHAR2(30);
3365 BEGIN
3366 l_postalcodes := l_rec_orders.DESTINATION_ZIP;
3367 l_field_name := 'DESTINATION_ZIP';
3368 l_sequence := l_sequence +1 ;
3369 IF instrb(l_postalcodes,l_separator) > 0 THEN
3370 IF l_rec_orders.ORIGIN_ZIP = g_select_all THEN
3371 l_filter_type := 3;
3375 l_multi_select:= 1;
3372 ELSE
3373 l_filter_type := 4;
3374 END IF;
3376 ELSE
3377 IF l_rec_orders.ORIGIN_ZIP = g_select_all THEN
3378 l_filter_type := 1;
3379 ELSE
3380 l_filter_type := 4;
3381 END IF;
3382 END IF;
3383 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3384 l_sequence ||l_delim||l_filter_type||l_delim||
3385 ''''||l_postalcodes||''''||l_delim||
3386 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3387 l_active_flag||l_delim;
3388 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3389 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3390 END;
3391 END IF; -- end DESTINATION_ZIP <> g_select_all
3392 -- ---------------------------------
3393 -- Criteria 2 - Range -(4)- City
3394 -- ---------------------------------
3395 l_filter_type := NULL;
3396 l_multi_select := 2;
3397 l_insert_what := '(';
3398 l_executed := NULL;
3399 IF l_rec_orders.ORIGIN_CITY <> g_select_all THEN
3400 DECLARE
3401 l_cities VARCHAR2(2000);
3402 l_field_name VARCHAR2(30);
3403 BEGIN
3404 l_cities := l_rec_orders.ORIGIN_CITY;
3405 l_field_name := 'ORIGIN_CITY';
3406 l_sequence := l_sequence +1 ;
3407 IF instrb(l_cities,l_separator) > 0 THEN
3408 IF l_rec_orders.DESTINATION_CITY = g_select_all THEN
3409 l_filter_type := 3;
3410 ELSE
3411 l_filter_type := 4;
3412 END IF;
3413 l_multi_select:= 1;
3414 ELSE
3415 IF l_rec_orders.DESTINATION_CITY = g_select_all THEN
3416 l_filter_type := 1;
3417 ELSE
3418 l_filter_type := 4;
3419 END IF;
3420 END IF;
3421 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3422 l_sequence ||l_delim||l_filter_type||l_delim||
3423 ''''||l_cities||''''||l_delim||
3424 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3425 l_active_flag||l_delim;
3426 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3427 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3428 END;
3429 END IF; -- end ORIGIN_CITY <> g_select_all
3430 l_filter_type := NULL;
3431 l_multi_select := 2;
3432 l_insert_what := '(';
3433 l_executed := NULL;
3434 IF l_rec_orders.DESTINATION_CITY <> g_select_all THEN
3435 DECLARE
3436 l_cities VARCHAR2(2000);
3437 l_field_name VARCHAR2(30);
3438 BEGIN
3439 l_cities := l_rec_orders.DESTINATION_CITY;
3440 l_field_name := 'DESTINATION_CITY';
3441 l_sequence := l_sequence +1 ;
3442 IF instrb(l_cities,l_separator) > 0 THEN
3443 IF l_rec_orders.ORIGIN_CITY = g_select_all THEN
3444 l_filter_type := 3;
3445 ELSE
3446 l_filter_type := 4;
3447 END IF;
3448 l_multi_select:= 1;
3449 ELSE
3450 IF l_rec_orders.ORIGIN_CITY = g_select_all THEN
3451 l_filter_type := 1;
3452 ELSE
3453 l_filter_type := 4;
3454 END IF;
3455 END IF;
3456 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3457 l_sequence ||l_delim||l_filter_type||l_delim||
3458 ''''||l_cities||''''||l_delim||
3459 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3460 l_active_flag||l_delim;
3461 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3462 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3463 END;
3464 END IF; -- end DESTINATION_CITY <> g_select_all
3465 -- ---------------------------------
3466 -- Criteria 2 - Range -(4)- State
3467 -- ---------------------------------
3468 l_filter_type := NULL;
3469 l_multi_select := 2;
3470 l_insert_what := '(';
3471 l_executed := NULL;
3472 IF l_rec_orders.ORIGIN_STATE <> g_select_all THEN
3473 DECLARE
3477 l_states := l_rec_orders.ORIGIN_STATE;
3474 l_states VARCHAR2(2000);
3475 l_field_name VARCHAR2(30);
3476 BEGIN
3478 l_field_name := 'ORIGIN_STATE';
3479 l_sequence := l_sequence +1 ;
3480 IF instrb(l_states,l_separator) > 0 THEN
3481 IF l_rec_orders.DESTINATION_STATE = g_select_all THEN
3482 l_filter_type := 3;
3483 ELSE
3484 l_filter_type := 4;
3485 END IF;
3486 l_multi_select:= 1;
3487 ELSE
3488 IF l_rec_orders.DESTINATION_STATE = g_select_all THEN
3489 l_filter_type := 1;
3490 ELSE
3491 l_filter_type := 4;
3492 END IF;
3493 END IF;
3494 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3495 l_sequence ||l_delim||l_filter_type||l_delim||
3496 ''''||l_states||''''||l_delim||
3497 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3498 l_active_flag||l_delim;
3499 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3500 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3501 END;
3502 END IF; -- end ORIGIN_STATE <> g_select_all
3503 l_filter_type := NULL;
3504 l_multi_select := 2;
3505 l_insert_what := '(';
3506 l_executed := NULL;
3507 IF l_rec_orders.DESTINATION_STATE <> g_select_all THEN
3508 DECLARE
3509 l_states VARCHAR2(2000);
3510 l_field_name VARCHAR2(30);
3511 BEGIN
3512 l_states := l_rec_orders.DESTINATION_STATE;
3513 l_field_name := 'DESTINATION_STATE';
3514 l_sequence := l_sequence +1 ;
3515 IF instrb(l_states,l_separator) > 0 THEN
3516 IF l_rec_orders.ORIGIN_STATE = g_select_all THEN
3517 l_filter_type := 3;
3518 ELSE
3519 l_filter_type := 4;
3520 END IF;
3521 l_multi_select:= 1;
3522 ELSE
3523 IF l_rec_orders.ORIGIN_STATE = g_select_all THEN
3524 l_filter_type := 1;
3525 ELSE
3526 l_filter_type := 4;
3527 END IF;
3528 END IF;
3529 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3530 l_sequence ||l_delim||l_filter_type||l_delim||
3531 ''''||l_states||''''||l_delim||
3532 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3533 l_active_flag||l_delim;
3534 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3535 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3536 END;
3537 END IF; -- end DESTINATION_STATE <> g_select_all
3538 -- ---------------------------------
3539 -- Criteria 2 - Range -(4)- Country
3540 -- ---------------------------------
3541 l_filter_type := NULL;
3542 l_multi_select := 2;
3543 l_insert_what := '(';
3544 l_executed := NULL;
3545 IF l_rec_orders.ORIGIN_COUNTRY <> g_select_all THEN
3546 DECLARE
3547 l_countries VARCHAR2(2000);
3548 l_field_name VARCHAR2(30);
3549 BEGIN
3550 l_countries := l_rec_orders.ORIGIN_COUNTRY;
3551 l_field_name := 'ORIGIN_COUNTRY';
3552 l_sequence := l_sequence +1 ;
3553 IF instrb(l_countries,l_separator) > 0 THEN
3554 IF l_rec_orders.DESTINATION_COUNTRY = g_select_all THEN
3555 l_filter_type := 3;
3556 ELSE
3557 l_filter_type := 4;
3558 END IF;
3559 l_multi_select:= 1;
3560 ELSE
3561 IF l_rec_orders.DESTINATION_COUNTRY = g_select_all THEN
3562 l_filter_type := 1;
3563 ELSE
3564 l_filter_type := 4;
3565 END IF;
3566 END IF;
3567 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3568 l_sequence ||l_delim||l_filter_type||l_delim||
3569 ''''||l_countries||''''||l_delim||
3570 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3571 l_active_flag||l_delim;
3572 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3573 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3574 END;
3575 END IF; -- end ORIGIN_COUNTRY <> g_select_all
3576 l_filter_type := NULL;
3577 l_multi_select := 2;
3578 l_insert_what := '(';
3579 l_executed := NULL;
3580 IF l_rec_orders.DESTINATION_COUNTRY <> g_select_all THEN
3581 DECLARE
3582 l_countries VARCHAR2(2000);
3583 l_field_name VARCHAR2(30);
3584 BEGIN
3588 IF instrb(l_countries,l_separator) > 0 THEN
3585 l_countries := l_rec_orders.DESTINATION_COUNTRY;
3586 l_field_name := 'DESTINATION_COUNTRY';
3587 l_sequence := l_sequence +1 ;
3589 IF l_rec_orders.ORIGIN_COUNTRY = g_select_all THEN
3590 l_filter_type := 3;
3591 ELSE
3592 l_filter_type := 4;
3593 END IF;
3594 l_multi_select:= 1;
3595 ELSE
3596 IF l_rec_orders.ORIGIN_COUNTRY = g_select_all THEN
3597 l_filter_type := 1;
3598 ELSE
3599 l_filter_type := 4;
3600 END IF;
3601 END IF;
3602 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3603 l_sequence ||l_delim||l_filter_type||l_delim||
3604 ''''||l_countries||''''||l_delim||
3605 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3606 l_active_flag||l_delim;
3607 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3608 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3609 END;
3610 END IF; -- end DESTINATION_COUNTRY <> g_select_all
3611 END IF; -- end RANGE_TYPE
3612 -- ---------------------------------
3613 -- Criteria 3 - Items
3614 -- ---------------------------------
3615 l_filter_type := NULL;
3616 l_multi_select := 2;
3617 l_insert_what := '(';
3618 l_executed := NULL;
3619 IF l_rec_orders.Items <> g_select_all THEN
3620 DECLARE
3621 l_items VARCHAR2(2000);
3622 l_field_name VARCHAR2(30);
3623 BEGIN
3624 l_items := l_rec_orders.Inventory_Item_Ids;
3625 l_field_name := 'INVENTORY_ITEM_ID';
3626 l_sequence := l_sequence +1 ;
3627 IF instrb(l_items,l_separator) > 0 THEN
3628 l_filter_type := 3;
3629 l_multi_select:= 1;
3630 ELSE
3631 l_filter_type := 1;
3632 END IF;
3633 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3634 l_sequence ||l_delim||l_filter_type||l_delim||
3635 ''''||l_items||''''||l_delim||
3636 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3637 l_active_flag||l_delim;
3638 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3639 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3640 IF l_executed = 0 THEN
3641 RAISE execution_failed;
3642 END IF;
3643 END;
3644 END IF; -- end Items <> g_select_all
3645 -- ---------------------------------
3646 -- Criteria 4 - Customers
3647 -- ---------------------------------
3648 l_filter_type := NULL;
3649 l_multi_select := 2;
3650 l_insert_what := '(';
3651 l_executed := NULL;
3652 IF l_rec_orders.Customers <> g_select_all THEN
3653 DECLARE
3654 l_Customers VARCHAR2(2000);
3655 l_field_name VARCHAR2(30);
3656 BEGIN
3657 l_Customers := l_rec_orders.Customer_ids;
3658 l_field_name := 'CUSTOMER_ID';
3659 l_sequence := l_sequence +1 ;
3660 IF instrb(l_Customers,l_separator) > 0 THEN
3661 l_filter_type := 3;
3662 l_multi_select:= 1;
3663 ELSE
3664 l_filter_type := 1;
3665 END IF;
3666 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3667 l_sequence ||l_delim||l_filter_type||l_delim||
3668 ''''||l_Customers||''''||l_delim||
3669 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3670 l_active_flag||l_delim;
3671 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3672 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3673 IF l_executed = 0 THEN
3674 RAISE execution_failed;
3675 END IF;
3676 END;
3677 END IF; -- end Customers <> g_select_all
3678 -- ---------------------------------
3679 -- Criteria 5 - Suppliers
3680 -- ---------------------------------
3681 l_filter_type := NULL;
3682 l_multi_select := 2;
3683 l_insert_what := '(';
3684 l_executed := NULL;
3685 IF l_rec_orders.Suppliers <> g_select_all THEN
3686 DECLARE
3687 l_Suppliers VARCHAR2(2000);
3688 l_field_name VARCHAR2(30);
3689 BEGIN
3690 l_Suppliers := l_rec_orders.Supplier_ids;
3691 l_field_name := 'SUPPLIER_ID';
3692 l_sequence := l_sequence +1 ;
3693 IF instrb(l_Suppliers,l_separator) > 0 THEN
3694 l_filter_type := 3;
3695 l_multi_select:= 1;
3696 ELSE
3697 l_filter_type := 1;
3698 END IF;
3699 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3700 l_sequence ||l_delim||l_filter_type||l_delim||
3704 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3701 ''''||l_Suppliers||''''||l_delim||
3702 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3703 l_active_flag||l_delim;
3705 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3706 IF l_executed = 0 THEN
3707 RAISE execution_failed;
3708 END IF;
3709 END;
3710 END IF; -- end Suppliers <> g_select_all
3711 -- ---------------------------------
3712 -- Criteria 6 - Weight
3713 -- ---------------------------------
3714 l_filter_type := NULL;
3715 l_multi_select := 2;
3716 l_insert_what := '(';
3717 l_executed := NULL;
3718 IF l_rec_orders.WEIGHT_FROM IS NOT NULL THEN
3719 DECLARE
3720 l_WEIGHT_from NUMBER;
3721 l_WEIGHT_to NUMBER;
3722 l_field_name VARCHAR2(30);
3723 BEGIN
3724 l_WEIGHT_from := l_rec_orders.WEIGHT_FROM;
3725 l_field_name := 'TOTAL_WEIGHT';
3726 l_sequence := l_sequence +1 ;
3727 l_filter_type := l_rec_orders.WEIGHT_TYPE;
3728 l_multi_select:= 2;
3729 IF l_filter_type = 4 THEN
3730 l_WEIGHT_to := l_rec_orders.WEIGHT_TO;
3731 END IF;
3732 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3733 l_sequence ||l_delim||l_filter_type||l_delim||
3734 ''''||l_WEIGHT_from||''''||l_delim||
3735 l_null_str||l_delim||''''||l_WEIGHT_to||''''||l_delim||
3736 l_multi_select||l_delim||
3737 l_active_flag||l_delim;
3738 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3739 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3740 IF l_executed = 0 THEN
3741 RAISE execution_failed;
3742 END IF;
3743 END;
3744 END IF; -- end WEIGHT_FROM IS NOT NULL
3745 -- ---------------------------------
3746 -- Criteria 7 - Cube
3747 -- ---------------------------------
3748 l_filter_type := NULL;
3749 l_multi_select := 2;
3750 l_insert_what := '(';
3751 l_executed := NULL;
3752 IF l_rec_orders.CUBE_FROM IS NOT NULL THEN
3753 DECLARE
3754 l_CUBE_from NUMBER;
3755 l_CUBE_to NUMBER;
3756 l_field_name VARCHAR2(30);
3757 BEGIN
3758 l_CUBE_from := l_rec_orders.CUBE_FROM;
3759 l_field_name := 'TOTAL_CUBE';
3760 l_sequence := l_sequence +1 ;
3761 l_filter_type := l_rec_orders.CUBE_TYPE;
3762 l_multi_select:= 2;
3763 IF l_filter_type = 4 THEN
3764 l_CUBE_to := l_rec_orders.CUBE_TO;
3765 END IF;
3766 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
3767 l_sequence ||l_delim||l_filter_type||l_delim||
3768 ''''||l_CUBE_from||''''||l_delim||
3769 l_null_str||l_delim||''''||l_CUBE_to||''''||l_delim||
3770 l_multi_select||l_delim||
3771 l_active_flag||l_delim;
3772 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3773 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3774 IF l_executed = 0 THEN
3775 RAISE execution_failed;
3776 END IF;
3777 END;
3778 END IF; -- end CUBE_FROM IS NOT NULL
3779 EXCEPTION
3780 WHEN OTHERS THEN
3781 RAISE;
3782 END insert_order_selection;
3783
3784 PROCEDURE insert_excep_selection(p_query_id IN NUMBER) IS
3785 cursor cur_excep is
3786 select ALL_EXCEPTION_TYPES ,EXCEPTION_TYPES ,
3787 EXCEPTION_TYPE_IDS ,TRUCKLOADS ,
3788 PARCELS ,LTLS ,
3789 CONTINUOUS_MOVES ,DELIVERIES ,
3790 FACILITIES ,FACILITY_IDS ,
3791 CARRIERS ,CARRIER_IDS ,
3792 CUSTOMERS ,CUSTOMER_IDS ,
3793 SUPPLIERS ,SUPPLIER_IDS ,
3794 EXCEPTION_COUNT_TYPE ,EXCEPTION_COUNT_FROM ,
3795 EXCEPTION_COUNT_TO ,EXCEPTION_STATUS ,
3796 EXCEPTION_STATUS_IDS
3797 from mst_excep_selection_criteria
3798 where query_id = p_query_id;
3799
3800 l_rec_excep cur_excep%ROWTYPE;
3801
3802 l_insert_begin VARCHAR2(500);
3803 l_insert_what VARCHAR2(3000);
3804 l_insert_who VARCHAR2(500);
3805 l_delete_str VARCHAR2(500);
3806 l_executed NUMBER ;
3807 l_filter_type NUMBER;
3808 l_sequence NUMBER ;--:= 0;
3809 l_multi_select NUMBER ;--:= 2; -- 1 true, 2 false
3810 l_active_flag NUMBER ;--:= 1;
3811 l_delim CONSTANT VARCHAR2(1) := ',';
3812 l_separator CONSTANT VARCHAR2(1):= ';';
3813 l_null_str CONSTANT VARCHAR2(6) := 'NULL';
3814 l_userid NUMBER ; --:= TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
3815 execution_failed EXCEPTION;
3816 BEGIN
3817 l_sequence := 0;
3818 l_multi_select := 2; -- 1 true, 2 false
3819 l_active_flag := 1;
3820 l_userid := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
3824 RAISE execution_failed;
3821 l_delete_str := 'DELETE MST_SELECTION_CRITERIA WHERE QUERY_ID = :P_QUERYID';
3822 l_executed := execute_dyn_sql(l_delete_str,p_query_id);
3823 IF l_executed = 0 THEN
3825 END IF;
3826 IF g_select_all IS NULL THEN
3827 FND_MESSAGE.set_name('MST','MST_PQ_ALL');
3828 g_select_all:= FND_MESSAGE.GET;
3829 --g_select_all := 'All';
3830 END IF;
3831 l_insert_begin := 'INSERT INTO MST_SELECTION_CRITERIA '||
3832 '(QUERY_ID , FIELD_NAME , SEQUENCE ,'||
3833 'FILTER_TYPE , FIELD_VALUE_FROM, DISPLAY_VALUE,'||
3834 'FIELD_VALUE_TO, MULTI_SELECT , ACTIVE_FLAG ,'||
3835 'CREATED_BY , CREATION_DATE ) VALUES ';
3836 OPEN cur_excep;
3837 FETCH cur_excep INTO l_rec_excep;
3838 IF cur_excep%NOTFOUND THEN
3839 CLOSE cur_excep;
3840 RAISE no_data_found;
3841 END IF;
3842 CLOSE cur_excep;
3843 l_insert_what := '(';
3844 l_executed := NULL;
3848 IF l_rec_excep.EXCEPTION_TYPES IS NOT NULL THEN
3845 -- ---------------------------------
3846 -- Criteria 1 EXCEPTION_TYPES
3847 -- ---------------------------------
3849 DECLARE
3850 l_excep_type_ids VARCHAR2(2000);
3851 BEGIN
3852 l_excep_type_ids := l_rec_excep.EXCEPTION_TYPE_IDS;
3853
3854 IF instrb(l_excep_type_ids,l_separator) > 0
3855 AND instrb(l_excep_type_ids,l_separator,-1)<>lengthb(l_excep_type_ids) THEN
3856 l_excep_type_ids := l_excep_type_ids||l_separator;
3857 END IF;
3858 l_sequence := l_sequence +1 ;
3859 IF instrb(l_excep_type_ids,l_separator) > 0 THEN
3860 l_filter_type := 3;
3861 l_multi_select:= 1;
3862 ELSE
3863 l_filter_type := 1;
3864 END IF;
3865 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''EXCEPTION_TYPE'''||l_delim||
3866 l_sequence ||l_delim||l_filter_type||l_delim||
3867 ''''||l_excep_type_ids||''''||l_delim||
3868 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3869 l_active_flag||l_delim;
3870 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3871 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3872 IF l_executed = 0 THEN
3873 RAISE execution_failed;
3874 END IF;
3875 END;
3876 END IF; -- end EXCEPTION_TYPES
3877 l_filter_type := NULL;
3878 l_multi_select := 2;
3879 l_insert_what := '(';
3880 l_executed := NULL;
3881 -- ---------------------------------
3882 -- Criteria 2 TL/LTL/PARCEL
3883 -- ---------------------------------
3884 IF l_rec_excep.TRUCKLOADS = 1 OR
3885 l_rec_excep.LTLS = 1 OR
3886 l_rec_excep.PARCELS = 1 THEN
3887 DECLARE
3891 l_modes := 'TRUCK';
3888 l_modes VARCHAR2(50);
3889 BEGIN
3890 IF l_rec_excep.truckloads = 1 THEN
3892 END IF;
3893 IF l_rec_excep.LTLS =1 THEN
3894 IF l_modes IS NOT NULL THEN
3895 l_modes := l_modes||l_separator||'LTL';
3896 ELSE
3897 l_modes := 'LTL';
3898 END IF;
3899 END IF;
3900 IF l_rec_excep.PARCELS =1 THEN
3901 IF l_modes IS NOT NULL THEN
3902 l_modes := l_modes||l_separator||'PARCEL';
3903 ELSE
3904 l_modes := 'PARCEL';
3905 END IF;
3906 END IF;
3907 l_sequence := l_sequence +1 ;
3908 IF instrb(l_modes,l_separator) > 0 THEN
3909 l_filter_type := 3;
3910 l_multi_select:= 1;
3911 ELSE
3912 l_filter_type := 1;
3913 END IF;
3917 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3914 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''MODE_OF_TRANSPORT'''||l_delim||
3915 l_sequence ||l_delim||l_filter_type||l_delim||
3916 ''''||l_modes||''''||l_delim||
3918 l_active_flag||l_delim;
3919 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3920 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3921 IF l_executed = 0 THEN
3922 RAISE execution_failed;
3923 END IF;
3924 END;
3925 ELSE
3926 l_sequence := l_sequence +1 ;
3927 --l_filter_type := 10;
3928 l_filter_type := 1;
3929 DECLARE
3930 l_modes NUMBER;
3931 BEGIN
3932 l_modes := 2;
3933 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''MODE_OF_TRANSPORT'''||l_delim||
3934 l_sequence ||l_delim||l_filter_type||l_delim||
3935 ''''||l_modes||''''||l_delim||
3936 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3937 l_active_flag||l_delim;
3938 END;
3939 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3940 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3941 IF l_executed = 0 THEN
3942 RAISE execution_failed;
3943 END IF;
3944 END IF; -- end TL/LTL/PARCEL
3945 l_filter_type := NULL;
3946 l_multi_select := 2;
3947 l_insert_what := '(';
3948 l_executed := NULL;
3949 -- ---------------------------------
3950 -- Criteria 3 Continuous Moves
3951 -- ---------------------------------
3952 IF l_rec_excep.CONTINUOUS_MOVES = 1 THEN
3953 DECLARE
3954 l_cm NUMBER;
3955 BEGIN
3956 IF l_rec_excep.CONTINUOUS_MOVES = 1 THEN
3957 l_sequence := l_sequence +1 ;
3958 --l_filter_type := 11;
3959 l_filter_type := 1;
3960 l_cm := 1;
3961 ELSE
3962 l_sequence := l_sequence +1 ;
3963 --l_filter_type := 10;
3964 l_filter_type := 1;
3965 l_cm := 2;
3966 END IF;
3967 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''CONTINUOUS_MOVE_ID'''||l_delim||
3968 l_sequence ||l_delim||l_filter_type||l_delim||
3969 ''''||l_cm||''''||l_delim||
3970 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
3971 l_active_flag||l_delim;
3972 END;
3973 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
3974 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
3975 IF l_executed = 0 THEN
3976 RAISE execution_failed;
3977 END IF;
3978 END IF;
3979 l_filter_type := NULL;
3980 l_multi_select := 2;
3981 l_insert_what := '(';
3982 l_executed := NULL;
3983 -- ---------------------------------
3984 -- Criteria 4 Deliveries
3985 -- ---------------------------------
3986 IF l_rec_excep.Deliveries = 1 THEN
3987 DECLARE
3988 l_deliveries NUMBER;
3989 BEGIN
3990 IF l_rec_excep.Deliveries = 1 THEN
3991 l_sequence := l_sequence +1 ;
3992 --l_filter_type := 11;
3993 l_filter_type := 1;
3994 l_deliveries := 1;
3995 ELSE
3996 l_sequence := l_sequence +1 ;
3997 --l_filter_type := 10;
3998 l_filter_type := 1;
3999 l_deliveries := 2;
4000 END IF;
4001 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''DELIVERY_ID'''||l_delim||
4002 l_sequence ||l_delim||l_filter_type||l_delim||
4003 ''''||l_deliveries||''''||l_delim||
4004 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
4005 l_active_flag||l_delim;
4006 END;
4007 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
4008 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
4009 IF l_executed = 0 THEN
4010 RAISE execution_failed;
4011 END IF;
4012 END IF;
4013 l_filter_type := NULL;
4014 l_multi_select := 2;
4015 l_insert_what := '(';
4016 l_executed := NULL;
4017 -- ---------------------------------
4018 -- Criteria 5 Facilities
4019 -- ---------------------------------
4020 IF l_rec_excep.FACILITIES <> g_select_all THEN
4021 DECLARE
4022 l_facilities VARCHAR2(2000);
4023 l_field_name VARCHAR2(30);
4024 BEGIN
4025 l_facilities := l_rec_excep.FACILITY_IDS;
4026 l_field_name := 'FACILITY_ID';
4027 l_sequence := l_sequence +1 ;
4028 IF instrb(l_facilities,l_separator) > 0 THEN
4029 l_filter_type := 3;
4030 l_multi_select:= 1;
4031 ELSE
4035 l_sequence ||l_delim||l_filter_type||l_delim||
4032 l_filter_type := 1;
4033 END IF;
4034 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
4036 ''''||l_facilities||''''||l_delim||
4037 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
4038 l_active_flag||l_delim;
4039 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
4040 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
4041 END;
4042 END IF; -- end FACILITIES <> g_select_all
4043 l_filter_type := NULL;
4044 l_multi_select := 2;
4045 l_insert_what := '(';
4046 l_executed := NULL;
4047 -- ---------------------------------
4048 -- Criteria 6 Carriers
4049 -- ---------------------------------
4050 IF l_rec_excep.Carriers <> g_select_all THEN
4051 DECLARE
4052 l_Carriers VARCHAR2(2000);
4053 l_field_name VARCHAR2(30);
4054 BEGIN
4055 l_Carriers := l_rec_excep.Carrier_ids;
4056 l_field_name := 'CARRIER_ID';
4057 l_sequence := l_sequence +1 ;
4058 IF instrb(l_Carriers,l_separator) > 0 THEN
4059 l_filter_type := 3;
4060 l_multi_select:= 1;
4061 ELSE
4062 l_filter_type := 1;
4063 END IF;
4064 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
4065 l_sequence ||l_delim||l_filter_type||l_delim||
4066 ''''||l_Carriers||''''||l_delim||
4067 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
4068 l_active_flag||l_delim;
4069 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
4070 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
4071 IF l_executed = 0 THEN
4072 RAISE execution_failed;
4073 END IF;
4074 END;
4075 END IF; -- end Carriers <> g_select_all
4076 l_filter_type := NULL;
4077 l_multi_select := 2;
4078 l_insert_what := '(';
4079 l_executed := NULL;
4080 -- ---------------------------------
4081 -- Criteria 7 Customers
4082 -- ---------------------------------
4083 IF l_rec_excep.Customers <> g_select_all THEN
4084 DECLARE
4085 l_Customers VARCHAR2(2000);
4086 l_field_name VARCHAR2(30);
4087 BEGIN
4088 l_Customers := l_rec_excep.Customer_ids;
4089 l_field_name := 'CUSTOMER_ID';
4090 l_sequence := l_sequence +1 ;
4091 IF instrb(l_Customers,l_separator) > 0 THEN
4092 l_filter_type := 3;
4093 l_multi_select:= 1;
4094 ELSE
4095 l_filter_type := 1;
4096 END IF;
4097 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
4098 l_sequence ||l_delim||l_filter_type||l_delim||
4099 ''''||l_Customers||''''||l_delim||
4100 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
4101 l_active_flag||l_delim;
4102 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
4103 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
4104 IF l_executed = 0 THEN
4105 RAISE execution_failed;
4106 END IF;
4107 END;
4108 END IF; -- end Customers <> g_select_all
4109 l_filter_type := NULL;
4110 l_multi_select := 2;
4111 l_insert_what := '(';
4112 l_executed := NULL;
4113 -- ---------------------------------
4114 -- Criteria 8 - Suppliers
4115 -- ---------------------------------
4116 IF l_rec_excep.Suppliers <> g_select_all THEN
4117 DECLARE
4118 l_Suppliers VARCHAR2(2000);
4119 l_field_name VARCHAR2(30);
4120 BEGIN
4121 l_Suppliers := l_rec_excep.Supplier_ids;
4122 l_field_name := 'SUPPLIER_ID';
4123 l_sequence := l_sequence +1 ;
4124 IF instrb(l_Suppliers,l_separator) > 0 THEN
4125 l_filter_type := 3;
4126 l_multi_select:= 1;
4127 ELSE
4128 l_filter_type := 1;
4129 END IF;
4130 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
4131 l_sequence ||l_delim||l_filter_type||l_delim||
4132 ''''||l_Suppliers||''''||l_delim||
4133 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
4134 l_active_flag||l_delim;
4135 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
4136 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
4137 IF l_executed = 0 THEN
4138 RAISE execution_failed;
4139 END IF;
4140 END;
4141 END IF; -- end Suppliers <> g_select_all
4142 l_filter_type := NULL;
4143 l_multi_select := 2;
4144 l_insert_what := '(';
4145 l_executed := NULL;
4146 -- ---------------------------------
4147 -- Criteria 9 - Exception_count
4148 -- ---------------------------------
4149 IF l_rec_excep.EXCEPTION_COUNT_FROM IS NOT NULL THEN
4150 DECLARE
4151 l_excep_Count_from NUMBER;
4152 l_excep_Count_to NUMBER;
4153 l_field_name VARCHAR2(30);
4154 BEGIN
4155 l_excep_Count_from := l_rec_excep.EXCEPTION_COUNT_FROM;
4156 l_field_name := 'TOTAL_EXCEPTIONS';
4157 l_sequence := l_sequence +1 ;
4158 l_filter_type := l_rec_excep.EXCEPTION_COUNT_TYPE;
4159 l_multi_select:= 2;
4160 IF l_filter_type = 4 THEN
4161 l_excep_Count_to := l_rec_excep.EXCEPTION_COUNT_TO;
4162 END IF;
4163 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
4164 l_sequence ||l_delim||l_filter_type||l_delim||
4165 ''''||l_excep_Count_from ||''''||l_delim||
4166 l_null_str||l_delim||''''||l_excep_Count_to||''''||l_delim||
4167 l_multi_select||l_delim||
4168 l_active_flag||l_delim;
4169 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
4170 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
4171 IF l_executed = 0 THEN
4172 RAISE execution_failed;
4173 END IF;
4174 END;
4175 END IF; -- end Exception_count IS NOT NULL
4176 l_filter_type := NULL;
4177 l_multi_select := 2;
4178 l_insert_what := '(';
4179 l_executed := NULL;
4180 -- ---------------------------------
4181 -- Criteria 10 - EXCEPTION_STATUS
4182 -- ---------------------------------
4183 IF l_rec_excep.EXCEPTION_STATUS IS NOT NULL THEN
4184 DECLARE
4185 l_exceptions VARCHAR2(2000);
4186 l_field_name VARCHAR2(30);
4187 BEGIN
4188 l_exceptions := l_rec_excep.EXCEPTION_STATUS_IDS;
4189 l_field_name := 'EXCEPTION_STATUS';
4190 l_sequence := l_sequence +1 ;
4191 IF instrb(l_exceptions,l_separator) > 0 THEN
4192 l_filter_type := 3;
4193 l_multi_select:= 1;
4194 ELSE
4195 l_filter_type := 1;
4196 END IF;
4197 l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
4198 l_sequence ||l_delim||l_filter_type||l_delim||
4199 ''''||l_exceptions||''''||l_delim||
4200 l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
4201 l_active_flag||l_delim;
4202 l_insert_who := l_userid||l_delim||'SYSDATE'||')';
4203 l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
4204 IF l_executed = 0 THEN
4205 RAISE execution_failed;
4206 END IF;
4207 END;
4208 END IF; -- END EXCEPTION_STATUS
4209 END insert_excep_selection;
4210
4211 PROCEDURE save_query_result(p_query_id IN NUMBER) IS
4212 CURSOR query_def IS
4213 SELECT 'x' FROM mst_personal_queries
4214 WHERE query_id = p_query_id
4215 FOR UPDATE OF execute_flag NOWAIT;
4216 l_dummy VARCHAR2(1);
4217 BEGIN
4218 OPEN query_def;
4219 FETCH query_def INTO l_dummy;
4220 IF query_def%FOUND THEN
4221 UPDATE mst_personal_queries
4222 SET execute_flag = 1
4223 WHERE CURRENT OF query_def;
4224 commit;
4225 END IF;
4226 CLOSE query_def;
4227 EXCEPTION
4228 WHEN OTHERS THEN
4229 RAISE;
4230 END save_query_result;
4231
4232 PROCEDURE clear_temp_query(p_query_id IN NUMBER) IS
4233 CURSOR cur_temp_qry IS
4234 SELECT query_id, query_type
4235 FROM mst_personal_queries
4236 WHERE query_id = p_query_id; -- Temp. Query
4237
4238 l_temp_qry cur_temp_qry%ROWTYPE;
4239
4240 BEGIN
4241 OPEN cur_temp_qry;
4242 FETCH cur_temp_qry INTO l_temp_qry;
4243 IF cur_temp_qry%FOUND THEN
4244 CLOSE cur_temp_qry;
4245 remove_qry_and_results
4246 (l_temp_qry.query_id,
4247 l_temp_qry.query_type);
4248 ELSE
4249 CLOSE cur_temp_qry;
4250 END IF;
4251 END clear_temp_query;
4252
4253 FUNCTION launch_request(p_query_id IN NUMBER,p_plan_id IN NUMBER)
4254 RETURN NUMBER IS
4255 l_req_id NUMBER;
4256 BEGIN
4257 l_req_id := FND_REQUEST.SUBMIT_REQUEST
4258 ( 'MST', -- application
4259 'MSTPQTST', -- program
4260 NULL, -- description
4261 NULL, -- start time
4262 FALSE, -- sub_request
4263 p_query_Id,
4264 p_plan_id);
4265 RETURN l_req_id;
4266 END launch_request;
4267
4268 end MST_PQ_WORKS;