[Home] [Help]
PACKAGE BODY: APPS.FTE_PARCEL_LOADER
Source
1 PACKAGE BODY FTE_PARCEL_LOADER AS
2 /* $Header: FTEPCLDB.pls 120.2 2005/08/25 06:18:35 pkaliyam noship $ */
3 -----------------------------------------------------------------------------
4 -- --
5 -- NAME: FTE_PARCEL_LOADER --
6 -- TYPE: PACKAGE BODY --
7 -- DESCRIPTION: Contains Parcel Loader functions for Bulk Loader --
8 -- --
9 -- PROCEDURES and FUNCTIONS: --
10 -- PROCEDURE GET_POSTAL_PART --
11 -- OBSOLETE_PREVIOUS_DATA --
12 -- CREATE_DEST_ZONES_LANES --
13 -- PROCESS_DATA --
14 -- PROCESS_RATING_ZONE_CHART --
15 -- PROCESS_RATING_SETUP --
16 -- PROCESS_ORIGIN --
17 -- PROCESS_DESTINATION --
18 -----------------------------------------------------------------------------
19
20 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_PARCEL_LOADER';
21
22 g_chart_info STRINGARRAY; -- rating zone chart info STRINGARRAY
23 g_origin_zone FTE_BULKLOAD_PKG.data_values_tbl; -- origin zone info STRINGARRAY
24 g_dest_zones FTE_BULKLOAD_PKG.block_data_tbl; -- table of STRINGARRAY of destination zone info
25 g_last_service_type VARCHAR2(200) := NULL;
26
27 -- table of STRINGARRAY with first item on string array being the service level,
28 -- if null, then that STRINGARRAY follows the last STRINGARRAY with first item not null.
29 g_setup_info FTE_BULKLOAD_PKG.array_tbl;
30 g_dest_indexes FTE_BULKLOAD_PKG.array_tbl;
31 g_dest_id NUMBER := 1;
32
33 -- table of STRINGARRAY with first item being destination name and the rest being the values
34 -- of a row of service columns in destination block
35 g_dest_info FTE_BULKLOAD_PKG.array_tbl;
36 g_service_columns STRINGARRAY := STRINGARRAY(); -- STRINGARRAY of the column names of the service columns in destination block
37
38 g_postal_from VARCHAR2(100);
39 g_postal_to VARCHAR2(100);
40
41 ----------------------------------------------------------------------------
42 -- PROCEDURE GET_POSTAL_PART
43 --
44 -- Purpose: parse the postal code into from and to
45 --
46 -- IN parameters:
47 -- 1. p_postal: postal code in format of (*-*, *..*, *)
48 --
49 -- OUT parameters:
50 -- 1. x_status: status of the processing, -1 means no error
51 -- 2. x_error_msg: error message if any.
52 ----------------------------------------------------------------------------
53 PROCEDURE GET_POSTAL_PART(p_postal IN VARCHAR2) IS
54 BEGIN
55 g_postal_from := '';
56 g_postal_to := '';
57
58 IF (INSTR(p_postal, '-') > 0) THEN -- separator -
59 g_postal_from := SUBSTR(p_postal, 1, INSTR(p_postal, '-')-1);
60 g_postal_to := SUBSTR(p_postal, INSTR(p_postal, '-')+1, LENGTH(p_postal));
61 ELSIF (INSTR(p_postal, '.') > 0) THEN -- separator ..
62 g_postal_from := SUBSTR(p_postal, 1, INSTR(p_postal, '-')-1);
63 g_postal_to := SUBSTR(p_postal, INSTR(p_postal, '-')+2, LENGTH(p_postal));
64 ELSE
65 g_postal_from := p_postal;
66 g_postal_to := p_postal;
67 END IF;
68
69 g_postal_from := TRIM(' ' FROM g_postal_from);
70 g_postal_to := TRIM(' ' FROM g_postal_to);
71 END GET_POSTAL_PART;
72
73 ----------------------------------------------------------------------------
74 -- PROCEDURE OBSOLETE_PREVIOUS_DATA
75 --
79 -- 1. p_zone_name: zone name to be matched
76 -- Purpose: remove or enddate old data if zone name exist
77 --
78 -- IN parameters:
80 --
81 -- OUT parameters:
82 -- 1. x_status: status of the processing, -1 means no error
83 -- 2. x_error_msg: error message if any.
84 --
85 -- FTE_LANES - Change Expiry_Date to new Effective_Date -1
86 -- FTE_PRC_PARAMETERS - No change
87 -- WSH_REGIONS - No change
88 -- WSH_REGIONS_TL - Change Zone Name
89 -- WSH_ZONE_REGIONS - No change
90 ----------------------------------------------------------------------------
91 PROCEDURE OBSOLETE_PREVIOUS_DATA(p_zone_name IN VARCHAR2,
92 x_status OUT NOCOPY NUMBER,
93 x_error_msg OUT NOCOPY VARCHAR2) IS
94 l_start_date VARCHAR2(100) := g_chart_info(8);
95 l_expiry_date DATE;
96 l_action VARCHAR2(20);
97
98 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.OBSOLETE_PREVIOUS_DATA';
99 BEGIN
100 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
101
102 x_status := -1;
103
104 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
105 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Zone Name', p_zone_name);
106 END IF;
107
108 IF (l_start_date IS NULL) THEN
109 l_expiry_date := SYSDATE - 1;
110 ELSE
111 BEGIN
112 l_expiry_date := TO_DATE(l_start_date, FTE_BULKLOAD_PKG.G_DATE_FORMAT3) - 1;
113 EXCEPTION
114 WHEN OTHERS THEN
115 BEGIN
116 l_expiry_date := TO_DATE(l_expiry_date,'MM/DD/YYYY') - 1;
117 l_expiry_date := TO_CHAR(l_expiry_date);
118 EXCEPTION
119 WHEN OTHERS THEN
120 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_INCORRECT_DATE',
121 p_tokens => STRINGARRAY('DATE'),
122 p_values => STRINGARRAY(l_expiry_date));
123
124 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
125 p_msg => x_error_msg,
126 p_category => 'D',
127 p_line_number => 0);
128 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
129 x_status := 1;
130 RETURN;
131 END;
132 END;
133
134 END IF;
135
136 BEGIN
137 -- check if there are previously loaded parcel lanes
138 SELECT 'UPDATE'
139 INTO l_action
140 FROM fte_lanes
141 WHERE lane_number like p_zone_name||'%'
142 AND editable_flag = 'N'
143 AND rownum = 1;
144 EXCEPTION
145 WHEN NO_DATA_FOUND THEN
146 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
147 RETURN;
148 END;
149
150 -- if there are previoiusly loaded parcel lanes,
151 -- check whether they need to be deleted or not
152
153 BEGIN
154 SELECT 'DELETE'
155 INTO l_action
156 FROM fte_lanes
157 WHERE lane_number like p_zone_name||'%'
158 AND editable_flag = 'N'
159 AND nvl(effective_date, SYSDATE) > l_expiry_date
160 AND rownum = 1;
161
162 EXCEPTION
163 WHEN NO_DATA_FOUND THEN
164
165 -- Update the Expiry_Date of the previously loaded lanes
166 -- to the previous date of the effective date of new lanes
167
168 -- Update EXPIRY_DATE of the previously loaded Parcel Lanes
169 UPDATE fte_lanes
170 SET expiry_date = l_expiry_date,
171 lane_number = lane_id||'-PARCEL',
172 last_update_date = SYSDATE
173 WHERE lane_number like p_zone_name||'%'
174 AND editable_flag = 'N';
175
176 -- Update Zone Name of the previously loaded Zones
177 UPDATE wsh_regions_tl t
178 SET t.zone = t.region_id||'-PARCEL'
179 WHERE t.zone like p_zone_name||'%'
180 AND t.region_id IN (SELECT region_id FROM wsh_regions
181 WHERE region_id=t.region_id AND region_type=11);
182
183 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
184 END;
185
186 -- mark the lanes to be deleted
187 -- and reuse the zone
188 BEGIN
189 UPDATE fte_lanes
190 SET editable_flag = 'D',
191 lane_number = lane_id||'-PARCEL',
192 last_update_date = SYSDATE
193 WHERE lane_number like p_zone_name||'%'
194 AND editable_flag = 'N';
195
196 -- Delete all the entries for the Zones from WSH_ZONE_REGIONS
197 DELETE wsh_zone_regions
198 WHERE parent_region_id in (SELECT region_id FROM wsh_regions_v
199 WHERE zone like p_zone_name||'%' AND region_type = 11);
200
201 END;
202
203 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
204 EXCEPTION
205 WHEN OTHERS THEN
206 x_error_msg := sqlerrm;
207 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
208 p_msg => x_error_msg,
209 p_category => 'O');
210 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
211 x_status := 1;
212 END OBSOLETE_PREVIOUS_DATA;
213
214 ----------------------------------------------------------------------------
215 -- PROCEDURE PROCESS_ZONES_AND_LANES
216 --
217 -- Purpose: make the zones and lanes
218 --
219 -- OUT parameters:
220 -- 1. x_status: status of the processing, -1 means no error
221 -- 2. x_error_msg: error message if any.
222 --
223 ----------------------------------------------------------------------------
224 PROCEDURE PROCESS_ZONES_AND_LANES(x_status OUT NOCOPY NUMBER,
225 x_error_msg OUT NOCOPY VARCHAR2) IS
226
227 l_dest_values STRINGARRAY;
231 l_lane_data FTE_BULKLOAD_PKG.data_values_tbl;
228 l_basis VARCHAR2(10) := 'WEIGHT';
229 l_lane_table FTE_BULKLOAD_PKG.block_data_tbl;
230 l_rate_table FTE_BULKLOAD_PKG.block_data_tbl;
232 l_service_data FTE_BULKLOAD_PKG.data_values_tbl;
233 l_rate_data FTE_BULKLOAD_PKG.data_values_tbl;
234 l_setup_data FTE_BULKLOAD_PKG.data_values_tbl;
235 l_lane_header FTE_BULKLOAD_PKG.block_header_tbl;
236 l_rate_header FTE_BULKLOAD_PKG.block_header_tbl;
237 l_zone_header FTE_BULKLOAD_PKG.block_header_tbl;
238 l_index NUMBER;
239
240 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_ZONES_AND_LANES';
241 BEGIN
242 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
243
244 x_status := -1;
245
246 FOR i IN 1..FTE_VALIDATION_PKG.SERVICE.COUNT LOOP
247 l_lane_data(FTE_VALIDATION_PKG.SERVICE(i)) := null;
248 l_service_data(FTE_VALIDATION_PKG.SERVICE(i)) := null;
249 l_rate_data(FTE_VALIDATION_PKG.SERVICE(i)) := null;
250 l_lane_header(FTE_VALIDATION_PKG.SERVICE(i)) := i;
251 END LOOP;
252
253 FOR i IN 1..FTE_VALIDATION_PKG.SERVICE_RATING_SETUP.COUNT LOOP
254 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(i)) := null;
255 l_rate_header(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(i)) := i;
256 END LOOP;
257
258 FOR i IN 1..FTE_VALIDATION_PKG.ZONE.COUNT LOOP
259 l_zone_header(FTE_VALIDATION_PKG.ZONE(i)) := i;
260 END LOOP;
261
262 g_dest_zones(g_dest_zones.COUNT+1) := g_origin_zone;
263
264 FTE_REGION_ZONE_LOADER.PROCESS_ZONE(p_block_header => l_zone_header,
265 p_block_data => g_dest_zones,
266 p_line_number => NULL,
267 p_region_type => '11',
268 x_status => x_status,
269 x_error_msg => x_error_msg);
270
271 IF (x_status <> -1) THEN
272 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
273 RETURN;
274 END IF;
275
276 FOR i IN 1..g_dest_info.COUNT LOOP
277 l_dest_values := g_dest_info(i);
278
279 FOR j IN 1..g_service_columns.COUNT LOOP
280
281 IF (l_dest_values(j+1) IS NOT NULL) THEN
282 -- BASIS is always WEIGHT
283 -- Except when the Service Type is for Letter
284
285 IF (INSTR(UPPER(g_service_columns(j)), 'LETTER') > 0) THEN
286 l_basis := 'CONTAINER';
287 END IF;
288
289 l_lane_data(FTE_VALIDATION_PKG.SERVICE(2)) := 'SERVICE';
290 l_lane_data(FTE_VALIDATION_PKG.SERVICE(1)) := 'ADD';
291 l_lane_data(FTE_VALIDATION_PKG.SERVICE(4)) := l_dest_values(1)||'-'||g_dest_id;
292 l_lane_data(FTE_VALIDATION_PKG.SERVICE(11)) := g_origin_zone(FTE_VALIDATION_PKG.ZONE(2));
293 l_lane_data(FTE_VALIDATION_PKG.SERVICE(17)) := l_dest_values(1);
294 l_lane_data(FTE_VALIDATION_PKG.SERVICE(5)) := g_chart_info(2);
295 l_lane_data(FTE_VALIDATION_PKG.SERVICE(21)) := l_basis;
296 l_lane_data(FTE_VALIDATION_PKG.SERVICE(23)) := g_chart_info(6);
297 l_lane_data(FTE_VALIDATION_PKG.SERVICE(3)) := g_chart_info(4);
298 l_lane_data('EDITABLE_FLAG') := 'N';
299 l_lane_data(FTE_VALIDATION_PKG.SERVICE(32)) := g_chart_info(8);
300 l_lane_data(FTE_VALIDATION_PKG.SERVICE(33)) := g_chart_info(9);
301
302 g_dest_id := g_dest_id + 1;
303
304 l_service_data(FTE_VALIDATION_PKG.SERVICE(2)) := 'SERVICE_LEVEL';
305 l_service_data(FTE_VALIDATION_PKG.SERVICE(1)) := 'ADD';
306 l_service_data(FTE_VALIDATION_PKG.SERVICE(19)) := g_service_columns(j);
307
308 l_rate_data(FTE_VALIDATION_PKG.SERVICE(2)) := 'RATE_CHART';
309 l_rate_data(FTE_VALIDATION_PKG.SERVICE(1)) := 'ADD';
310 l_rate_data(FTE_VALIDATION_PKG.SERVICE(22)) := g_chart_info(5) || l_dest_values(j+1);
311
312 l_lane_table(l_lane_table.COUNT+1) := l_lane_data;
313 l_lane_table(l_lane_table.COUNT+1) := l_service_data;
314 l_lane_table(l_lane_table.COUNT+1) := l_rate_data;
315
316 FOR k IN 1..g_setup_info.COUNT LOOP
317
318 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
319 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'service', g_setup_info(k)(1));
320 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'service columns', g_service_columns(j));
321 END IF;
322
323 IF (g_setup_info(k)(1) IS NOT NULL AND g_setup_info(k)(1) = g_service_columns(j)) THEN
324
325 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(1)) := 'SYNC';
326 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(2)) := g_chart_info(4);
327 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(3)) := l_lane_data(FTE_VALIDATION_PKG.SERVICE(4));
328 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(4)) := g_setup_info(k)(2);
329 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(5)) := g_setup_info(k)(3);
330 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(6)) := g_setup_info(k)(4);
331 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(7)) := g_setup_info(k)(5);
332 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(8)) := g_setup_info(k)(6);
333 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(9)) := g_setup_info(k)(7);
334 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(10)) := g_setup_info(k)(8);
335
336 l_rate_table(l_rate_table.COUNT+1) := l_setup_data;
337
338 l_index := k + 1;
339
340 -- looping through the table after the service is found and before the next service
341 WHILE (l_index <= g_setup_info.COUNT AND g_setup_info(l_index)(1) IS NULL) LOOP
342 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(1)) := 'SYNC';
346 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(5)) := g_setup_info(l_index)(3);
343 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(2)) := g_chart_info(4);
344 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(3)) := l_lane_data(FTE_VALIDATION_PKG.SERVICE(4));
345 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(4)) := g_setup_info(l_index)(2);
347 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(6)) := g_setup_info(l_index)(4);
348 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(7)) := g_setup_info(l_index)(5);
349 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(8)) := g_setup_info(l_index)(6);
350 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(9)) := g_setup_info(l_index)(7);
351 l_setup_data(FTE_VALIDATION_PKG.SERVICE_RATING_SETUP(10)) := g_setup_info(l_index)(8);
352
353 l_rate_table(l_rate_table.COUNT+1) := l_setup_data;
354 l_index := l_index + 1;
355 END LOOP;
356 END IF;
357 END LOOP;
358 END IF;
359 END LOOP;
360 END LOOP;
361
362 FTE_LANE_LOADER.PROCESS_SERVICE(p_block_header => l_lane_header,
363 p_block_data => l_lane_table,
364 p_line_number => NULL,
365 x_status => x_status,
366 x_error_msg => x_error_msg);
367
368 IF (x_status <> -1) THEN
369 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
370 RETURN;
371 END IF;
372
373 FTE_LANE_LOADER.PROCESS_SERVICE_RATING_SETUP(p_block_header => l_rate_header,
374 p_block_data => l_rate_table,
375 p_line_number => NULL,
376 x_status => x_status,
377 x_error_msg => x_error_msg);
378
379 IF (x_status <> -1) THEN
380 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
381 RETURN;
382 END IF;
383
384 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
385 EXCEPTION
386 WHEN OTHERS THEN
387 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
388 p_msg => sqlerrm,
389 p_category => 'O');
390 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
391 x_status := 1;
392 END PROCESS_ZONES_AND_LANES;
393
394 ----------------------------------------------------------------------------
395 -- PROCEDURE PROCESS_DATA
396 --
397 -- Purpose: Call appropriate process function according to the type.
398 --
399 -- IN parameters:
400 -- 1. p_type: type of the block (Rating zone chart, rating setup, orign, destination)
401 -- 2. p_table: pl/sql table of STRINGARRAY containing the block information
402 -- 3. p_line_number: line number for the beginning of the block
403 --
404 -- OUT parameters:
405 -- 1. x_status: status of the processing, -1 means no error
406 -- 2. x_error_msg: error message if any.
407 ----------------------------------------------------------------------------
408 PROCEDURE PROCESS_DATA (p_type IN VARCHAR2,
409 p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
410 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
411 p_line_number IN NUMBER,
412 x_status OUT NOCOPY NUMBER,
413 x_error_msg OUT NOCOPY VARCHAR2) IS
414 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_DATA';
415 BEGIN
416 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
417
418 x_status := -1;
419
420 IF (p_type = 'RATING_ZONE_CHART') THEN
421 PROCESS_RATING_ZONE_CHART(p_block_header => p_block_header,
422 p_block_data => p_block_data,
423 p_line_number => p_line_number,
424 x_status => x_status,
425 x_error_msg => x_error_msg);
426 ELSIF (p_type = 'RATING_SETUP') THEN
427 PROCESS_RATING_SETUP(p_block_header => p_block_header,
428 p_block_data => p_block_data,
429 p_line_number => p_line_number,
430 x_status => x_status,
431 x_error_msg => x_error_msg);
432 ELSIF (p_type = 'ORIGIN') THEN
433 PROCESS_ORIGIN(p_block_header => p_block_header,
434 p_block_data => p_block_data,
435 p_line_number => p_line_number,
436 x_status => x_status,
437 x_error_msg => x_error_msg);
438 ELSE
439 PROCESS_DESTINATION(p_block_data => p_block_data,
440 p_line_number => p_line_number,
441 x_status => x_status,
442 x_error_msg => x_error_msg);
443 END IF;
444 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
445 EXCEPTION
446 WHEN OTHERS THEN
447 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
448 p_msg => sqlerrm,
449 p_category => 'O',
450 p_line_number => p_line_number);
451 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
452 x_status := 1;
453 END PROCESS_DATA;
454
455 ----------------------------------------------------------------------------
456 -- PROCEDURE PROCESS_RATING_ZONE_CHART
457 --
458 -- Purpose: process the lines in p_table for rating zone chart
459 --
460 -- IN parameters:
461 -- 1. p_table: pl/sql table of STRINGARRAY containing the block information
462 -- 2. p_line_number: line number for the beginning of the block
463 --
464 -- OUT parameters:
465 -- 1. x_status: status of the processing, -1 means no error
466 -- 2. x_error_msg: error message if any.
467 ----------------------------------------------------------------------------
468
469 PROCEDURE PROCESS_RATING_ZONE_CHART(p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
470 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
471 p_line_number IN NUMBER,
472 x_status OUT NOCOPY NUMBER,
473 x_error_msg OUT NOCOPY VARCHAR2) IS
474
475 l_values FTE_BULKLOAD_PKG.data_values_tbl;
479
476 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_RATING_ZONE_CHART';
477 BEGIN
478 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
480 x_status := -1;
481
482 --verify the column name
483 FTE_VALIDATION_PKG.VALIDATE_COLUMNS(p_keys => p_block_header,
484 p_type => 'RATING_ZONE_CHART',
485 p_line_number => p_line_number+1,
486 x_status => x_status,
487 x_error_msg => x_error_msg);
488 IF (x_status <> -1) THEN
489 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
490 RETURN;
491 END IF;
492
493 --now the body of the block
494 FOR i IN p_block_data.FIRST..p_block_data.LAST LOOP
495 l_values := p_block_data(i);
496
497 FTE_VALIDATION_PKG.VALIDATE_RATING_ZONE_CHART(p_values => l_values,
498 p_line_number => p_line_number+i+1,
499 p_chart_info => g_chart_info,
500 x_status => x_status,
501 x_error_msg => x_error_msg);
502
503 IF (x_status <> -1) THEN
504 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
505 RETURN;
506 END IF;
507
508 END LOOP;
509 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
510 EXCEPTION
511 WHEN OTHERS THEN
512 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
513 p_msg => sqlerrm,
514 p_category => 'O',
515 p_line_number => p_line_number);
516 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
517 x_status := 1;
518 END PROCESS_RATING_ZONE_CHART;
519
520 ----------------------------------------------------------------------------
521 -- PROCEDURE PROCESS_RATING_SETUP
522 --
523 -- Purpose: process the lines in p_table for rating setup
524 --
525 -- IN parameters:
526 -- 1. p_table: pl/sql table of STRINGARRAY containing the block information
527 -- 2. p_line_number: line number for the beginning of the block
528 --
529 -- OUT parameters:
530 -- 1. x_status: status of the processing, -1 means no error
531 -- 2. x_error_msg: error message if any.
532 ----------------------------------------------------------------------------
533
534 PROCEDURE PROCESS_RATING_SETUP(p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
535 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
536 p_line_number IN NUMBER,
537 x_status OUT NOCOPY NUMBER,
538 x_error_msg OUT NOCOPY VARCHAR2) IS
539
540 l_values FTE_BULKLOAD_PKG.data_values_tbl;
541 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_RATING_SETUP';
542 BEGIN
543 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
544
545 x_status := -1;
546
547 --verify the column name
548 FTE_VALIDATION_PKG.VALIDATE_COLUMNS(p_keys => p_block_header,
549 p_type => 'RATING_SETUP',
550 p_line_number => p_line_number+1,
551 x_status => x_status,
552 x_error_msg => x_error_msg);
553 IF (x_status <> -1) THEN
554 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
555 RETURN;
556 END IF;
557
558 --now the body of the block
559 FOR i IN p_block_data.FIRST..p_block_data.LAST LOOP
560 l_values := p_block_data(i);
561
562 FTE_VALIDATION_PKG.VALIDATE_RATING_SETUP(p_values => l_values,
563 p_line_number => p_line_number+i+1,
564 p_setup_info => g_setup_info,
565 p_last_service_type => g_last_service_type,
566 x_status => x_status,
567 x_error_msg => x_error_msg);
568 IF (x_status <> -1) THEN
569 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
570 RETURN;
571 END IF;
572
573 END LOOP;
574 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
575 EXCEPTION
576 WHEN OTHERS THEN
577 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
578 p_msg => sqlerrm,
579 p_category => 'O',
580 p_line_number => p_line_number);
581 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
582 x_status := 1;
583 END PROCESS_RATING_SETUP;
584
585 ----------------------------------------------------------------------------
586 -- PROCEDURE PROCESS_ORIGIN
587 --
588 -- Purpose: process the lines in p_table for origin
589 --
590 -- IN parameters:
591 -- 1. p_table: pl/sql table of STRINGARRAY containing the block information
592 -- 2. p_line_number: line number for the beginning of the block
593 --
594 -- OUT parameters:
595 -- 1. x_status: status of the processing, -1 means no error
596 -- 2. x_error_msg: error message if any.
597 ----------------------------------------------------------------------------
598
599 PROCEDURE PROCESS_ORIGIN(p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
600 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
601 p_line_number IN NUMBER,
602 x_status OUT NOCOPY NUMBER,
603 x_error_msg OUT NOCOPY VARCHAR2) IS
604
605 l_origin_postal VARCHAR2(200);
606 l_origin_country VARCHAR2(200);
607 l_origin_state VARCHAR2(200);
608 l_origin_city VARCHAR2(200);
609 l_origin_postal_from VARCHAR2(100);
610 l_origin_postal_to VARCHAR2(100);
611 l_values FTE_BULKLOAD_PKG.data_values_tbl;
612 l_origin STRINGARRAY;
613
614 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_ORIGIN';
615 BEGIN
616 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
617
618 x_status := -1;
622 p_type => 'ORIGIN',
619
620 --verify the column name
621 FTE_VALIDATION_PKG.VALIDATE_COLUMNS(p_keys => p_block_header,
623 p_line_number => p_line_number+1,
624 x_status => x_status,
625 x_error_msg => x_error_msg);
626 IF (x_status <> -1) THEN
627 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
628 RETURN;
629 END IF;
630
631 --now the body of the block
632 FOR i IN p_block_data.FIRST..p_block_data.LAST LOOP
633 l_values := p_block_data(i);
634
635 FTE_VALIDATION_PKG.VALIDATE_ORIGIN(p_values => l_values,
636 p_line_number => p_line_number+i+1,
637 p_origin => l_origin,
638 x_status => x_status,
639 x_error_msg => x_error_msg);
640
641 IF (x_status <> -1) THEN
642 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
643 RETURN;
644 END IF;
645
646 l_origin_postal := l_origin(1);
647 l_origin_country := l_origin(2);
648 l_origin_state := l_origin(3);
649 l_origin_city := l_origin(4);
650
651 GET_POSTAL_PART(p_postal => l_origin_postal);
652
653 l_origin_postal_from := g_postal_from;
654 l_origin_postal_to := g_postal_to;
655
656 g_origin_zone(FTE_VALIDATION_PKG.ZONE(1)) := 'ADD';
657 g_origin_zone(FTE_VALIDATION_PKG.ZONE(2)) := g_chart_info(1) || '-' || l_origin_postal_from;
658 g_origin_zone(FTE_VALIDATION_PKG.ZONE(5)) := l_origin_city;
659 g_origin_zone(FTE_VALIDATION_PKG.ZONE(4)) := l_origin_state;
660 g_origin_zone(FTE_VALIDATION_PKG.ZONE(3)) := l_origin_country;
661 g_origin_zone(FTE_VALIDATION_PKG.ZONE(6)) := l_origin_postal_from;
662 g_origin_zone(FTE_VALIDATION_PKG.ZONE(7)) := l_origin_postal_to;
663
664 -- enhancement for patchset I : hjpark on 12/13/2002
665 -- make the previously loaded data obsolete, if any
666
667 OBSOLETE_PREVIOUS_DATA(p_zone_name => g_chart_info(1) || '-' || l_origin_postal_from,
668 x_status => x_status,
669 x_error_msg => x_error_msg);
670
671 IF (x_status <> -1) THEN
672 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
673 RETURN;
674 END IF;
675
676 END LOOP;
677 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
678 EXCEPTION
679 WHEN OTHERS THEN
680 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
681 p_msg => sqlerrm,
682 p_category => 'O',
683 p_line_number => p_line_number);
684 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
685 x_status := 1;
686 END PROCESS_ORIGIN;
687
688 ----------------------------------------------------------------------------
689 -- PROCEDURE PROCESS_DESTINATION
690 --
691 -- Purpose: process the lines in p_table for destination
692 --
693 -- IN parameters:
694 -- 1. p_table: pl/sql table of STRINGARRAY containing the block information
695 -- 2. p_line_number: line number for the beginning of the block
696 --
697 -- OUT parameters:
698 -- 1. x_status: status of the processing, -1 means no error
699 -- 2. x_error_msg: error message if any.
700 ----------------------------------------------------------------------------
701
702 PROCEDURE PROCESS_DESTINATION(p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
703 p_line_number IN NUMBER,
704 x_status OUT NOCOPY NUMBER,
705 x_error_msg OUT NOCOPY VARCHAR2) IS
706
707 l_dest_postal VARCHAR2(200);
708 l_dest_country VARCHAR2(200);
709 l_dest_state VARCHAR2(200);
710 l_dest_city VARCHAR2(200);
711 l_dest_postal_from VARCHAR2(100);
712 l_dest_postal_to VARCHAR2(100);
713 l_values FTE_BULKLOAD_PKG.data_values_tbl;
714 l_dest_zone_name VARCHAR2(200);
715 l_services service_array := service_array(); -- name of the services
716 l_service_count NUMBER := 0; -- number of services
717 l_name_value VARCHAR2(2000);
718 l_dest_values STRINGARRAY := STRINGARRAY();
719 l_exists BOOLEAN := FALSE;
720 l_dest STRINGARRAY := STRINGARRAY();
721 l_count NUMBER;
722
723 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_DESTINATION';
724 BEGIN
725 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
726
727 x_status := -1;
728
729 --+
730 -- Do not verify the column name for destination since it's dynamic
731 -- find out the mapping for column number to service columns
732 --+
733 FOR i IN 1..FTE_BULKLOAD_PKG.g_block_header_index.COUNT LOOP
734
735 IF (FTE_BULKLOAD_PKG.g_block_header_index(i) NOT IN ('POSTAL_CODE_RANGE', 'COUNTRY', 'STATE', 'CITY')) THEN
736 --+
737 -- If not those column names, then it's a service. Map it
738 --+
739 l_service_count := l_service_count + 1;
740 l_services.EXTEND;
741 l_services(l_service_count) := i;
742 g_service_columns.EXTEND;
743 g_service_columns(g_service_columns.COUNT) := FTE_BULKLOAD_PKG.g_block_header_index(i);
744
745 END IF;
746
747 END LOOP;
748
749 --+
750 -- process the body of the block
751 --+
752 FOR i IN p_block_data.FIRST..p_block_data.LAST LOOP
753
754 l_values := p_block_data(i);
755
756 FTE_VALIDATION_PKG.VALIDATE_DESTINATION(p_values => l_values,
757 p_line_number => p_line_number+i+1,
758 p_price_prefix => g_chart_info(5),
759 p_carrier_id => TO_NUMBER(g_chart_info(7)),
760 p_origin_zone => g_origin_zone,
761 p_service_count => l_service_count,
762 p_services => l_services,
763 p_dest => l_dest,
764 x_status => x_status,
765 x_error_msg => x_error_msg);
766
767 IF (x_status <> -1) THEN
768 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
769 RETURN;
770 END IF;
771
772 l_dest_postal := l_dest(1);
773 l_dest_country := l_dest(2);
774 l_dest_state := l_dest(3);
775 l_dest_city := l_dest(4);
776
777 GET_POSTAL_PART(p_postal => l_dest_postal);
778
779 l_dest_postal_from := g_postal_from;
780 l_dest_postal_to := g_postal_to;
781
782 l_dest_zone_name := g_origin_zone(FTE_VALIDATION_PKG.ZONE(2));
783
784 IF(FTE_BULKLOAD_PKG.g_debug_on) THEN
785 FTE_UTIL_PKG.WRITE_LogFile(l_module_name,'l_dest_zone_name',l_dest_zone_name);
786 END IF;
787
788 l_name_value := null;
789
790 FOR i IN 1..l_service_count LOOP
791 l_name_value := l_name_value || '-' || l_values(FTE_BULKLOAD_PKG.g_block_header_index(l_services(i)));
792 END LOOP;
793
794 FOR i in 1..g_dest_indexes.COUNT LOOP
795 IF (g_dest_indexes(i)(1) = l_name_value) THEN
796 l_dest_zone_name := l_dest_zone_name || '-' || g_dest_indexes(i)(2);
797 END IF;
798 END LOOP;
799
800 IF(FTE_BULKLOAD_PKG.g_debug_on) THEN
801 FTE_UTIL_PKG.WRITE_LogFile(l_module_name,'l_name_value',l_name_value);
802 FTE_UTIL_PKG.WRITE_LogFile(l_module_name,'l_dest_zone_name',l_dest_zone_name);
803 END IF;
804
805 --+
806 -- l_name_value was not found in the g_dest_indexes table
807 --+
808 IF (l_dest_zone_name = g_origin_zone(FTE_VALIDATION_PKG.ZONE(2))) THEN
809 g_dest_indexes(g_dest_indexes.COUNT+1) := STRINGARRAY(l_name_value, g_dest_id);
810 l_dest_zone_name := l_dest_zone_name || '-' || g_dest_id;
811 g_dest_id := g_dest_id + 1;
812 END IF;
813
814 l_exists := FALSE;
815
816 FOR i IN 1..g_dest_info.COUNT LOOP
817 IF (g_dest_info(i)(1) = l_dest_zone_name) THEN
818 l_exists := TRUE;
819 EXIT;
820 END IF;
821 END LOOP;
822
823 IF (NOT l_exists) THEN
824 l_dest_values := STRINGARRAY();
825 l_dest_values.EXTEND;
826 l_dest_values(l_dest_values.COUNT) := l_dest_zone_name;
827
828 FOR i IN 1..l_service_count LOOP
829 l_dest_values.EXTEND;
830 l_dest_values(l_dest_values.COUNT) := l_values(FTE_BULKLOAD_PKG.g_block_header_index(l_services(i)));
831 END LOOP;
832 g_dest_info(g_dest_info.COUNT+1) := l_dest_values;
833
834 END IF;
835
836 l_count := g_dest_zones.COUNT+1;
837 g_dest_zones(l_count)(FTE_VALIDATION_PKG.ZONE(1)) := 'ADD';
838 g_dest_zones(l_count)(FTE_VALIDATION_PKG.ZONE(2)) := l_dest_zone_name;
839 g_dest_zones(l_count)(FTE_VALIDATION_PKG.ZONE(5)) := l_dest_city;
840 g_dest_zones(l_count)(FTE_VALIDATION_PKG.ZONE(4)) := l_dest_state;
841 g_dest_zones(l_count)(FTE_VALIDATION_PKG.ZONE(3)) := l_dest_country;
842 g_dest_zones(l_count)(FTE_VALIDATION_PKG.ZONE(6)) := l_dest_postal_from;
843 g_dest_zones(l_count)(FTE_VALIDATION_PKG.ZONE(7)) := l_dest_postal_to;
844
845 END LOOP;
846
847 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
848 EXCEPTION
849 WHEN OTHERS THEN
850 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
851 p_msg => sqlerrm,
852 p_category => 'O',
853 p_line_number => p_line_number);
854 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
855 x_status := 1;
856 END PROCESS_DESTINATION;
857
858 END FTE_PARCEL_LOADER;