[Home] [Help]
PACKAGE BODY: APPS.FTE_LANE_LOADER
Source
1 PACKAGE BODY FTE_LANE_LOADER AS
2 /* $Header: FTELNLDB.pls 120.1 2005/08/19 00:16:13 pkaliyam noship $ */
3 -------------------------------------------------------------------------- --
4 -- --
5 -- NAME: FTE_LANE_LOADER --
6 -- TYPE: PACKAGE BODY --
7 -- DESCRIPTION: Contains Lane and Schedule loader functions for Bulk Loader--
8 -- --
9 -- PROCEDURES and FUNCTIONS: --
10 -- FUNCTION GET_NEXT_SCHEDULE_ID --
11 -- GET_SCHEDULE --
12 -- PROCEDURE PROCESS_DATA --
13 -- PROCESS_SERVICE --
14 -- PROCESS_SERVICE_RATING_SETUP --
15 -- PROCESS_SCHEDULE --
16 -------------------------------------------------------------------------- --
17
18 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_LANE_LOADER';
19 g_lane_tbl FTE_LANE_PKG.lane_tbl;
20 g_lane_rate_chart_tbl FTE_LANE_PKG.lane_rate_chart_tbl;
21 g_lane_service_tbl FTE_LANE_PKG.lane_service_tbl;
22 g_lane_commodity_tbl FTE_LANE_PKG.lane_commodity_tbl;
23 g_schedule_tbl FTE_LANE_PKG.schedule_tbl;
24 g_prc_parameter_tbl FTE_LANE_PKG.prc_parameter_tbl;
25
26 g_lane_function VARCHAR2(100);
27 g_deficit_wt BOOLEAN := FALSE; -- if it's DEFICIT_WT
28 g_first BOOLEAN := TRUE; -- if it's New Lane
29 g_pre_lane_number VARCHAR2(200);
30
31 ----------------------------------------------------------------------------
32 -- PROCEDURE PROCESS_DATA
33 --
34 -- Purpose: Call appropriate process function according to the type.
35 --
36 -- IN parameters:
37 -- 1. p_type: type of the block (Service, Schedule, Service_rating_setup)
38 -- 2. p_table: pl/sql table of STRINGARRAY containing the block information
39 -- 3. p_line_number: line number for the beginning of the block
40 --
41 -- OUT parameters:
42 -- 1. x_status: status of the processing, -1 means no error
43 -- 2. x_error_msg: error message if any.
44 ----------------------------------------------------------------------------
45 PROCEDURE PROCESS_DATA (p_type IN VARCHAR2,
46 p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
47 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
48 p_line_number IN NUMBER,
49 x_status OUT NOCOPY NUMBER,
50 x_error_msg OUT NOCOPY VARCHAR2) IS
51 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_DATA';
52 BEGIN
53 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
54
55 x_status := -1;
56
57 IF (p_type = 'SCHEDULE') THEN
58 PROCESS_SCHEDULE(p_block_header => p_block_header,
59 p_block_data => p_block_data,
60 p_line_number => p_line_number,
61 x_status => x_status,
62 x_error_msg => x_error_msg);
63 ELSIF (p_type = 'SERVICE') THEN
64 PROCESS_SERVICE(p_block_header => p_block_header,
65 p_block_data => p_block_data,
66 p_line_number => p_line_number,
67 x_status => x_status,
68 x_error_msg => x_error_msg);
69 ELSE
70 PROCESS_SERVICE_RATING_SETUP(p_block_header => p_block_header,
71 p_block_data => p_block_data,
72 p_line_number => p_line_number,
73 x_status => x_status,
74 x_error_msg => x_error_msg);
75 END IF;
76 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
77 EXCEPTION
78 WHEN OTHERS THEN
79 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
80 p_msg => sqlerrm,
81 p_category => 'O');
82 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
83 x_status := 1;
84 END PROCESS_DATA;
85
86 ----------------------------------------------------------------------------
87 -- PROCEDURE PROCESS_SERVICE
88 --
89 -- Purpose: process the lines in p_table for service
90 --
91 -- IN parameters:
92 -- 1. p_table: pl/sql table of STRINGARRAY containing the block information
93 -- 2. p_line_number: line number for the beginning of the block
94 --
95 -- OUT parameters:
96 -- 1. x_status: status of the processing, -1 means no error
97 -- 2. x_error_msg: error message if any.
98 ----------------------------------------------------------------------------
99 PROCEDURE PROCESS_SERVICE (p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
100 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
101 p_line_number IN NUMBER,
102 x_status OUT NOCOPY NUMBER,
103 x_error_msg OUT NOCOPY VARCHAR2) IS
104
105 l_values FTE_BULKLOAD_PKG.data_values_tbl;
106 l_action VARCHAR2(20);
107 l_type VARCHAR2(100);
108 l_find_lane BOOLEAN := false;
109 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_SERVICE';
110
111 BEGIN
112 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
113 x_status := -1;
114
115 --verify the column name
116
117 FTE_VALIDATION_PKG.VALIDATE_COLUMNS(p_keys => p_block_header,
118 p_type => 'SERVICE',
119 p_line_number => p_line_number+1,
120 x_status => x_status,
121 x_error_msg => x_error_msg);
122 IF (x_status <> -1) THEN
123 FTE_UTIL_PKG.Exit_Debug(l_module_name);
124 RETURN;
125 END IF;
126
127 --now the body of the block
128 FOR i IN p_block_data.FIRST..p_block_data.LAST LOOP
129
130 l_values := p_block_data(i);
131
132 FTE_VALIDATION_PKG.VALIDATE_SERVICE(p_values => l_values,
133 p_line_number => p_line_number+i+1,
134 p_type => l_type,
135 p_action => l_action,
136 p_lane_tbl => g_lane_tbl,
137 p_lane_rate_chart_tbl => g_lane_rate_chart_tbl,
138 p_lane_service_tbl => g_lane_service_tbl,
139 p_lane_commodity_tbl => g_lane_commodity_tbl,
140 x_status => x_status,
141 x_error_msg => x_error_msg);
142
143 IF (x_status <> -1) THEN
144 FTE_UTIL_PKG.Exit_Debug(l_module_name);
145 RETURN;
146 END IF;
147
148 IF (l_action = 'DELETE') THEN
149 IF (l_type = 'SERVICE') THEN
150 FTE_LANE_PKG.DELETE_ROW(p_id => g_lane_tbl(g_lane_tbl.COUNT).lane_id,
151 p_table => 'FTE_LANES',
152 p_code => NULL,
153 p_line_number => p_line_number+i+1,
154 x_status => x_status,
155 x_error_msg => x_error_msg);
156 IF (x_status <> -1) THEN
157 FTE_UTIL_PKG.Exit_Debug(l_module_name);
158 RETURN;
159 END IF;
160
161 g_lane_tbl(g_lane_tbl.COUNT).lane_id := 0; -- set to 0 so the next component line can be skipped
162 ELSIF (l_type = 'RATE_CHART' AND g_lane_tbl(g_lane_tbl.COUNT).lane_id <> 0) THEN
163 FTE_LANE_PKG.DELETE_ROW(p_id => g_lane_rate_chart_tbl(g_lane_rate_chart_tbl.COUNT).lane_id,
164 p_table => 'FTE_LANE_RATE_CHARTS',
165 p_code => to_char(g_lane_rate_chart_tbl(g_lane_rate_chart_tbl.COUNT).list_header_id),
166 p_line_number => p_line_number+i+1,
167 x_status => x_status,
168 x_error_msg => x_error_msg);
169
170 IF (x_status <> -1) THEN
171 FTE_UTIL_PKG.Exit_Debug(l_module_name);
172 RETURN;
173 END IF;
174
175 FTE_LANE_PKG.UPDATE_LANE_FLAGS(p_type => l_type,
176 p_lane_id => g_lane_rate_chart_tbl(g_lane_rate_chart_tbl.COUNT).lane_id,
177 x_status => x_status,
178 x_error_msg => x_error_msg);
179
180 IF (x_status <> -1) THEN
181 FTE_UTIL_PKG.Exit_Debug(l_module_name);
182 RETURN;
183 END IF;
184
185 g_lane_rate_chart_tbl.DELETE(g_lane_rate_chart_tbl.COUNT);
186 ELSIF (l_type = 'SERVICE_TYPE' AND g_lane_tbl(g_lane_tbl.COUNT).lane_id <> 0) THEN
187 l_find_lane := FTE_LANE_PKG.FIND_TYPE(p_type => 'SERVICE_LEVEL',
188 p_value => g_lane_service_tbl(g_lane_service_tbl.COUNT).service_code,
189 p_lane_id => g_lane_tbl(g_lane_tbl.COUNT).lane_id,
190 p_line_number => p_line_number+i+1,
191 x_status => x_status,
192 x_error_msg => x_error_msg);
193
194 IF (x_status <> -1) THEN
195 FTE_UTIL_PKG.Exit_Debug(l_module_name);
196 RETURN;
197 END IF;
198
199 IF (l_find_lane) THEN
200 FTE_LANE_PKG.DELETE_ROW(p_id => g_lane_tbl(g_lane_tbl.COUNT).lane_id,
201 p_table => 'FTE_LANE_SERVICES',
202 p_code => g_lane_service_tbl(g_lane_service_tbl.COUNT).service_code,
203 p_line_number => p_line_number+i+1,
204 x_status => x_status,
205 x_error_msg => x_error_msg);
206 IF (x_status <> -1) THEN
207 FTE_UTIL_PKG.Exit_Debug(l_module_name);
208 RETURN;
209 END IF;
210
211 FTE_LANE_PKG.UPDATE_LANE_FLAGS(p_type => l_type,
212 p_lane_id => g_lane_tbl(g_lane_tbl.COUNT).lane_id,
213 x_status => x_status,
214 x_error_msg => x_error_msg);
215
216 IF (x_status <> -1) THEN
217 FTE_UTIL_PKG.Exit_Debug(l_module_name);
218 RETURN;
219 END IF;
220
221 END IF;
222
223 g_lane_service_tbl.DELETE(g_lane_service_tbl.COUNT);
224 ELSIF (l_type = 'COMMODITY_TYPE' AND g_lane_tbl(g_lane_tbl.COUNT).lane_id <> 0) THEN
225 l_find_lane := FTE_LANE_PKG.FIND_TYPE(p_type => 'COMMODITY',
226 p_value => TO_CHAR(g_lane_commodity_tbl(g_lane_commodity_tbl.COUNT).commodity_catg_id),
227 p_lane_id => g_lane_tbl(g_lane_tbl.COUNT).lane_id,
228 p_line_number => p_line_number+i+1,
229 x_status => x_status,
230 x_error_msg => x_error_msg);
231
232 IF (x_status <> -1) THEN
233 FTE_UTIL_PKG.Exit_Debug(l_module_name);
234 RETURN;
235 END IF;
236
237 IF (l_find_lane) THEN
238 FTE_LANE_PKG.DELETE_ROW(p_id => g_lane_tbl(g_lane_tbl.COUNT).lane_id,
239 p_table => 'FTE_LANE_COMMODITIES',
240 p_code => to_char(g_lane_commodity_tbl(g_lane_commodity_tbl.COUNT).commodity_catg_id),
241 p_line_number => p_line_number+i+1,
242 x_status => x_status,
243 x_error_msg => x_error_msg);
244 IF (x_status <> -1) THEN
245 RETURN;
246 END IF;
247
248 FTE_LANE_PKG.UPDATE_LANE_FLAGS(p_type => l_type,
249 p_lane_id => g_lane_tbl(g_lane_tbl.COUNT).lane_id,
250 x_status => x_status,
251 x_error_msg => x_error_msg);
252
253 IF (x_status <> -1) THEN
254 FTE_UTIL_PKG.Exit_Debug(l_module_name);
255 RETURN;
256 END IF;
257
258 END IF;
259 g_lane_commodity_tbl.DELETE(g_lane_commodity_tbl.COUNT);
260 END IF;
261
262 END IF;
263 END LOOP;
264
265 FTE_LANE_LOADER.SUBMIT_LANE(x_status => x_status,
266 x_error_msg => x_error_msg);
267
268 IF (x_status <> -1) THEN
269 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
270 RETURN;
271 END IF;
272
273 /* IF (x_status = -1) THEN
274 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
275 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Commiting for Service load');
276 END IF;
277 commit;
278 END IF;
279 */
280 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
281 EXCEPTION
282 WHEN OTHERS THEN
283 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
284 p_msg => sqlerrm,
285 p_category => 'O');
286 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
287 x_status := 1;
288 END PROCESS_SERVICE;
289
290 ----------------------------------------------------------------------------
291 -- PROCEDURE PROCESS_SERVICE_RATING_SETUP
292 --
293 -- Purpose: process the lines in p_table for service_rating_setup
294 --
295 -- IN parameters:
296 -- 1. p_table: pl/sql table of STRINGARRAY containing the block information
297 -- 2. p_line_number: line number for the beginning of the block
298 --
299 -- OUT parameters:
300 -- 1. x_status: status of the processing, -1 means no error
301 -- 2. x_error_msg: error message if any.
302 ----------------------------------------------------------------------------
303 PROCEDURE PROCESS_SERVICE_RATING_SETUP (p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
304 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
305 p_line_number IN NUMBER,
306 x_status OUT NOCOPY NUMBER,
307 x_error_msg OUT NOCOPY VARCHAR2) IS
308
309 l_values FTE_BULKLOAD_PKG.data_values_tbl;
310 l_action VARCHAR2(100);
311 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_SERVICE_RATING_SETUP';
312 l_lane_number VARCHAR2(200);
313
314 BEGIN
315 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
316 x_status := -1;
317 --verify the column name
318
319 FTE_VALIDATION_PKG.VALIDATE_COLUMNS(p_keys => p_block_header,
320 p_type => 'SERVICE_RATING_SETUP',
321 p_line_number => p_line_number+1,
322 x_status => x_status,
323 x_error_msg => x_error_msg);
324 IF (x_status <> -1) THEN
325 FTE_UTIL_PKG.Exit_Debug(l_module_name);
326 RETURN;
327 END IF;
328
329 --now the body of the block
330 FOR i IN p_block_data.FIRST..p_block_data.LAST LOOP
331 l_values := p_block_data(i);
332
333 FTE_VALIDATION_PKG.VALIDATE_SERVICE_RATING_SETUP(p_values => l_values,
334 p_line_number => p_line_number+i+1,
335 p_pre_lane_number => g_pre_lane_number,
336 p_prc_parameter_tbl => g_prc_parameter_tbl,
337 p_deficit_wt => g_deficit_wt,
338 p_lane_function => g_lane_function,
339 p_lane_number => l_lane_number,
340 p_action => l_action,
341 x_status => x_status,
342 x_error_msg => x_error_msg);
343
344 IF (x_status <> -1) THEN
345 FTE_UTIL_PKG.Exit_Debug(l_module_name);
346 RETURN;
347 END IF;
348
349 IF (l_action = 'DELETE') THEN
350 IF (g_deficit_wt) THEN
351 FTE_LANE_PKG.DELETE_ROW(p_id => g_prc_parameter_tbl(g_prc_parameter_tbl.COUNT).lane_id,
352 p_table => 'FTE_PRC_PARAMETERS',
353 p_code => TO_CHAR(g_prc_parameter_tbl(g_prc_parameter_tbl.COUNT).parameter_id),
354 p_line_number => p_line_number+i+1,
355 x_status => x_status,
356 x_error_msg => x_error_msg);
357
358 ELSIF (g_prc_parameter_tbl(g_prc_parameter_tbl.COUNT).parameter_instance_id <> -1) THEN
359 FTE_LANE_PKG.DELETE_ROW(p_id => g_prc_parameter_tbl(g_prc_parameter_tbl.COUNT).parameter_instance_id,
360 p_table => 'FTE_PRC_PARAMETERS',
361 p_code => NULL,
362 p_line_number => p_line_number+i+1,
363 x_status => x_status,
364 x_error_msg => x_error_msg);
365
366 END IF;
367 END IF;
368
369 IF (x_status <> -1) THEN
370 FTE_UTIL_PKG.Exit_Debug(l_module_name);
371 RETURN;
372 END IF;
373
374 IF (l_action = 'ADD') THEN
375 IF (g_deficit_wt AND g_first) THEN
376 FTE_LANE_PKG.DELETE_ROW(p_id => g_prc_parameter_tbl(g_prc_parameter_tbl.COUNT).lane_id,
377 p_table => 'FTE_PRC_PARAMETERS',
378 p_code => TO_CHAR(g_prc_parameter_tbl(g_prc_parameter_tbl.COUNT).parameter_id),
379 p_line_number => p_line_number+i+1,
380 x_status => x_status,
381 x_error_msg => x_error_msg);
382
383 IF (x_status <> -1) THEN
384 FTE_UTIL_PKG.Exit_Debug(l_module_name);
385 RETURN;
386 END IF;
387
388 END IF;
389
390 FTE_LANE_PKG.INSERT_PRC_PARAMETERS(p_prc_parameter_tbl => g_prc_parameter_tbl,
391 x_status => x_status,
392 x_error_msg => x_error_msg);
393
394 ELSIF (l_action = 'UPDATE') THEN
395 FTE_LANE_PKG.UPDATE_PRC_PARAMETER(p_prc_parameter_tbl => g_prc_parameter_tbl,
396 x_status => x_status,
397 x_error_msg => x_error_msg);
398 END IF;
399
400 IF (x_status <> -1) THEN
401 FTE_UTIL_PKG.Exit_Debug(l_module_name);
402 RETURN;
403 END IF;
404
405 IF (l_lane_number <> nvl(g_pre_lane_number, l_lane_number || l_lane_number)) THEN
406 g_first := TRUE;
407 g_pre_lane_number := l_lane_number;
408
409 ELSIF (g_deficit_wt AND g_first) THEN
410 g_first := FALSE;
411 END IF;
412
413 g_prc_parameter_tbl.DELETE(g_prc_parameter_tbl.COUNT);
414 END LOOP;
415
416 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
417 EXCEPTION
418 WHEN OTHERS THEN
419 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
420 p_msg => sqlerrm,
421 p_category => 'O');
422 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
423 x_status := 1;
424 END PROCESS_SERVICE_RATING_SETUP;
425
426 ----------------------------------------------------------------------------
427 -- PROCEDURE PROCESS_SCHEDULE
428 --
429 -- Purpose: process the lines in p_table for schedule
430 --
431 -- IN parameters:
432 -- 1. p_table: pl/sql table of STRINGARRAY containing the block information
433 -- 2. p_line_number: line number for the beginning of the block
434 --
435 -- OUT parameters:
436 -- 1. x_status: status of the processing, -1 means no error
437 -- 2. x_error_msg: error message if any.
438 ----------------------------------------------------------------------------
439 PROCEDURE PROCESS_SCHEDULE (p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
440 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
441 p_line_number IN NUMBER,
442 x_status OUT NOCOPY NUMBER,
443 x_error_msg OUT NOCOPY VARCHAR2) IS
444
445 l_rows NUMBER := -1;
446 l_old_schedule_id NUMBER := -1;
447 l_values FTE_BULKLOAD_PKG.data_values_tbl;
448 l_action VARCHAR2(100);
449 l_voyage VARCHAR2(200);
450 l_data_keys STRINGARRAY := STRINGARRAY();
451 l_data_values STRINGARRAY := STRINGARRAY();
452 l_lane_id NUMBER;
453
454 l_header_printed BOOLEAN := false;
455 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_SCHEDULE';
456
457 BEGIN
458 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
459 x_status := -1;
460 --verify the column name
461
462 FTE_VALIDATION_PKG.VALIDATE_COLUMNS(p_keys => p_block_header,
463 p_type => 'SCHEDULE',
464 p_line_number => p_line_number+1,
465 x_status => x_status,
466 x_error_msg => x_error_msg);
467 IF (x_status <> -1) THEN
468 FTE_UTIL_PKG.Exit_Debug(l_module_name);
469 RETURN;
470 END IF;
471
472 --now the body of the block
473 FOR i IN p_block_data.FIRST..p_block_data.LAST LOOP
474 l_values := p_block_data(i);
475
476 FTE_VALIDATION_PKG.VALIDATE_SCHEDULE(p_values => l_values,
477 p_line_number => p_line_number+i+1,
478 p_schedule_tbl => g_schedule_tbl,
479 p_action => l_action,
480 x_status => x_status,
481 x_error_msg => x_error_msg);
482
483 IF (x_status <> -1) THEN
484 FTE_UTIL_PKG.Exit_Debug(l_module_name);
485 RETURN;
486 END IF;
487
488 IF (l_action = 'ADD') THEN
489
490 IF (l_header_printed = false) THEN
491 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
492 p_msg_name => 'FTE_SHCEDULES_LOADED',
493 p_category => NULL);
494 l_header_printed := true;
495 END IF;
496
497 FTE_LANE_PKG.INSERT_SCHEDULES(p_schedule_tbl => g_schedule_tbl,
498 x_status => x_status,
499 x_error_msg => x_error_msg);
500
501 IF (x_status <> -1) THEN
502 FTE_UTIL_PKG.Exit_Debug(l_module_name);
503 RETURN;
504 END IF;
505
506 FTE_LANE_PKG.UPDATE_LANE_FLAGS(p_type => 'SCHEDULE',
507 p_lane_id => g_schedule_tbl(g_schedule_tbl.COUNT).lane_id,
508 p_value => 'Y',
509 x_status => x_status,
510 x_error_msg => x_error_msg);
511
512 IF (x_status <> -1) THEN
513 FTE_UTIL_PKG.Exit_Debug(l_module_name);
514 RETURN;
515 END IF;
516
517 -- ELSIF (l_action = 'SYNC') THEN
518 -- need to figure out about update later
519
520 ELSIF (l_action = 'DELETE') THEN
521 -- Delete a schedule and modify a lane
522 FTE_LANE_PKG.DELETE_ROW(p_id => g_schedule_tbl(g_schedule_tbl.COUNT).schedules_id,
523 p_table => 'FTE_SCHEDULES',
524 p_code => g_schedule_tbl(g_schedule_tbl.COUNT).voyage_number,
525 p_line_number => p_line_number+i+1,
526 x_status => x_status,
527 x_error_msg => x_error_msg);
528
529 IF (x_status <> -1) THEN
530 FTE_UTIL_PKG.Exit_Debug(l_module_name);
531 RETURN;
532 END IF;
533
534 -- to change Schedules_flag for the lane
535 l_lane_id := g_schedule_tbl(g_schedule_tbl.COUNT).lane_id;
536 SELECT COUNT(schedules_id)
537 INTO l_rows
538 FROM FTE_SCHEDULES
539 WHERE LANE_ID = l_lane_id
540 AND NVL(EDITABLE_FLAG,'Y') = 'Y';
541
542 IF (l_rows = 0) THEN
543 FTE_LANE_PKG.UPDATE_LANE_FLAGS(p_type => 'SCHEDULE',
544 p_lane_id => g_schedule_tbl(g_schedule_tbl.COUNT).lane_id,
545 p_value => 'N',
546 x_status => x_status,
547 x_error_msg => x_error_msg);
548
549 IF (x_status <> -1) THEN
550 FTE_UTIL_PKG.Exit_Debug(l_module_name);
551 RETURN;
552 END IF;
553
554 END IF;
555 END IF;
556 g_schedule_tbl.DELETE(g_schedule_tbl.COUNT);
557 END LOOP;
558
559 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
560 EXCEPTION
561 WHEN OTHERS THEN
562 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
563 p_msg => sqlerrm,
564 p_category => 'O');
565 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
566 x_status := 1;
567 END PROCESS_SCHEDULE;
568
569 ----------------------------------------------------------------------
570 -- PROCEDURE SUBMIT_LANE
571 --
572 -- Purpose: insert all pl/sql tables into fte_lane_* tables
573 ----------------------------------------------------------------------
574
575 PROCEDURE SUBMIT_LANE(x_status OUT NOCOPY NUMBER,
576 x_error_msg OUT NOCOPY VARCHAR2) IS
577 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.SUBMIT_LANE';
578 BEGIN
579 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
580 x_status := -1;
581
582 -- have to check if the lanes that has no commodity have to have basis
583 FTE_LANE_PKG.INSERT_LANE_TABLES(p_lane_tbl => g_lane_tbl,
584 p_lane_rate_chart_tbl => g_lane_rate_chart_tbl,
585 p_lane_commodity_tbl => g_lane_commodity_tbl,
586 p_lane_service_tbl => g_lane_service_tbl,
587 x_status => x_status,
588 x_error_msg => x_error_msg);
589 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
590 EXCEPTION
591 WHEN OTHERS THEN
592 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
593 p_msg => sqlerrm,
594 p_category => 'O');
595 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
596 x_status := 1;
597 END SUBMIT_LANE;
598
599 END FTE_LANE_LOADER;