[Home] [Help]
PACKAGE BODY: APPS.FTE_LTL_LOADER
Source
1 PACKAGE BODY FTE_LTL_LOADER AS
2 /* $Header: FTELTLRB.pls 120.7.12000000.3 2007/01/20 11:04:07 htnguyen ship $ */
3
4 /*----------------------------------------------------------------------------------------
5 -- --
6 -- NAME: FTE_LTL_LOADER --
7 -- TYPE: BODY --
8 -- DESCRIPTION: Gets a block of data from FTE_BULKLOAD_PKG --
9 -- and creates RATE-CHARTS, ZONES, LANES and more !! --
10 -- --
11 -- PROCEDURES: --
12 -- --
13 -- CHANGE CONTROL LOG --
14 -- --
15 -- --
16 -- DATE VERSION BY DESCRIPTION --
17 -- --
18 -- 08/07/2002 I GLAMI Created. --
19 -- --
20 -- 05/02/2005 R12 PRABHAKHAR Refactored. --
21 ------------------------------------------------------------------------------------------*/
22
23 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_LTL_LOADER';
24
25 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
26 G_ACTION VARCHAR2(10);
27 G_LTL_UOM VARCHAR2(5) := 'Lbs';
28 G_ORIG_COUNTRY VARCHAR2(10) := 'US';
29 G_DEST_COUNTRY VARCHAR2(10) := 'US';
30 G_REF_COUNTRY VARCHAR2(10) := 'US';
31 G_LTL_CURRENCY VARCHAR2(20) := 'USD';
32 G_SERVICE_CODE VARCHAR2(30);
33 G_BULK_INSERT_LIMIT NUMBER := 250;
34 G_TOTAL_NUMCHARTS NUMBER := 0;
35 G_CHART_COUNT_TEMP NUMBER := 0;
36 G_VALID_DATE DATE;
37 G_VALID_DATE_STRING VARCHAR2(20);
38 G_DIRECTION_FLAG VARCHAR2(5);
39 G_ORIGIN_DEST VARCHAR2(30);
40 G_IN_OUT VARCHAR2(30);
41 G_PROCESSED_LINES NUMBER := 0;
42 G_NUM_COLUMNS CONSTANT NUMBER := 18;
43 G_NUM_CONC_PROCESSES NUMBER := 10;
44 G_LANE_FUNCTION_ID NUMBER;
45 G_MIN_CHARGE_ID NUMBER;
46 G_DEF_WT_ENABLED_ID NUMBER;
47 G_DEF_WT_BREAK_ID NUMBER;
48 G_DATE_FORMAT CONSTANT VARCHAR2(50) := 'MM-DD-YYYY hh24:mi:ss';
49 G_DATE CONSTANT VARCHAR2(50) := 'MM-DD-YYYY';
50
51 G_REPORT_HEADER LTL_Report_Header;
52 G_DUMMY_BLOCK_HDR_TBL FTE_BULKLOAD_PKG.block_header_tbl;
53
54 --+
55 -- Cursors used by more than one procedures
56 --+
57 CURSOR GET_LOAD_NUMBER(p_tariff_name IN VARCHAR2) IS
58 SELECT
59 SUBSTR(lane_type,INSTR(lane_type,'_', -1)+1)
60 FROM
61 fte_lanes
62 WHERE
63 tariff_name = p_tariff_name
64 ORDER BY creation_date DESC;
65
66 CURSOR GET_TARIFF_CARRIERS (p_tariff_name IN VARCHAR2, p_action_code IN VARCHAR2) IS
67 SELECT
68 carrier_id,
69 TO_CHAR(new_effective_date, G_DATE_FORMAT),
70 TO_CHAR(new_expiry_date,G_DATE_FORMAT)
71 FROM
72 fte_tariff_carriers
73 WHERE
74 tariff_name = p_tariff_name AND
75 action_code = p_action_code;
76
77
78 --+
79 -- ORDER BY is significant, because we need to fetch
80 -- the information for the lastest lane created, not the first.
81 --+
82 CURSOR GET_PREVIOUS_LOAD_INFO (p_tariff_name IN VARCHAR2) IS
83 SELECT
84 SUBSTR(l.lane_type,INSTR(lane_type,'_', -1) + 1),
85 l.service_type_code,
86 owr.country_code,
87 dwr.country_code,
88 l.carrier_id
89 FROM
90 fte_lanes l,
91 fte_tariff_carriers tc,
92 wsh_zone_regions ozr,
93 wsh_zone_regions dzr,
94 wsh_regions owr,
95 wsh_regions dwr
96 WHERE
97 l.tariff_name = p_tariff_name AND
98 l.tariff_name = tc.tariff_name AND
99 tc.action_code IN ('M', 'D') AND
100 l.carrier_id = tc.carrier_id AND
101 ozr.parent_region_id = l.origin_id AND
102 dzr.parent_region_id = l.destination_id AND
103 ozr.region_id = owr.region_id AND
104 dzr.region_id = dwr.region_id
105 ORDER BY l.creation_date DESC;
106
107 --_________________________________________________________________________________--
108 --
109 -- FUNCTION: GET_PHASE
110 --
111 -- Purpose
112 -- Return the phase of the LTL loading process.
113 -- In phase 1, rate charts and zones are prepared in the interface tables.
114 -- In phase 2, lanes are created and linked to the rate charts.
115 -- This happens after the sub-processes (for QP Rate Chart Loading) complete
116 -- successfully.
117 --_________________________________________________________________________________--
118
119 FUNCTION GET_PHASE RETURN NUMBER IS
120
121 l_request_data VARCHAR2(100) := NULL;
122 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.GET_PHASE';
123
124 BEGIN
125 FTE_UTIL_PKG.Enter_Debug(l_module_name);
126
127 l_request_data := FND_CONC_GLOBAL.REQUEST_DATA;
128
129 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
130 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_request_data', l_request_data);
131 END IF;
132
133 IF (l_request_data IS NULL) THEN
134 FTE_UTIL_PKG.Exit_Debug(l_module_name);
135 RETURN 1;
136 ELSE
137 FTE_UTIL_PKG.Exit_Debug(l_module_name);
138 RETURN 2;
139 END IF;
140
141 EXCEPTION
142 WHEN OTHERS THEN
143 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR FTE_LTL_LOADER.GET_PHASE', SQLERRM);
144 FTE_UTIL_PKG.Exit_Debug(l_module_name);
145 RAISE;
146 END GET_PHASE;
147
148
149 --_________________________________________________________________________________--
150 --
151 -- FUNCTION: ROW_NUM_MAX
152 --
153 -- PURPOSE: To get the next number to be used for ROW_NUMBER in
154 -- insertion into fte_interface_zones.
155 --
156 -- RETURNS: The number to use for ROW_NUMBER, by adding one to the max of the
157 -- ROW_NUMBER at present in FTE_INTERFACE_ZONES for a given zone.
158 --_________________________________________________________________________________--
159
160 FUNCTION ROW_NUM_MAX(l_zone_name IN VARCHAR2) RETURN NUMBER IS
161
162 l_max NUMBER;
163 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.GET_PHASE';
164
165 BEGIN
166 FTE_UTIL_PKG.Enter_Debug(l_module_name);
167
168 SELECT
169 MAX(row_number) INTO l_max
170 FROM
171 fte_interface_zones
172 WHERE
173 zone_name = l_zone_name;
174
175 FTE_UTIL_PKG.Exit_Debug(l_module_name);
176
177 RETURN l_max + 1;
178
179 EXCEPTION
180 WHEN OTHERS THEN
181 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR IN FTE_LTL_LOADER.ROW_NUM_MAX', sqlerrm);
182 FTE_UTIL_PKG.Exit_Debug(l_module_name);
183 RAISE;
184 END ROW_NUM_MAX;
185
186 --______________________________________________________________________________________--
187 --
188 -- FUNCTION: VERIFY_TARIFF_CARRIER
189 --
190 -- Purpose
191 -- check if carrier_id is associated with the tariff
192 --
193 -- IN Parameters
194 -- 1. p_tariff: The name of the tariff.
195 -- 2. p_carrier_id: The carrier_id to check
196 --
197 -- RETURNS:
198 -- true, if the carrier is associated with the tariff.
199 -- false, otherwise.
200 --______________________________________________________________________________________--
201
202 FUNCTION VERIFY_TARIFF_CARRIER(p_tariff_name IN VARCHAR2,
203 p_carrier_id IN NUMBER,
204 x_error_msg OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
205 l_carrier_ids NUMBER_TAB;
206 l_effective_dates STRINGARRAY;
207 l_expiry_dates STRINGARRAY;
208
209 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.VERIFY_TARIFF_CARRIER';
210
211 BEGIN
212
213 FTE_UTIL_PKG.Enter_Debug(l_module_name);
214
215 OPEN GET_TARIFF_CARRIERS(p_tariff_name => p_tariff_name,
216 p_action_code => 'D');
217 FETCH GET_TARIFF_CARRIERS
218
219 BULK COLLECT INTO l_carrier_ids, l_effective_dates, l_expiry_dates;
220
221 CLOSE GET_TARIFF_CARRIERS;
222
223 IF l_carrier_ids.COUNT > 0 THEN
224 FOR i in l_carrier_ids.FIRST..l_carrier_ids.LAST LOOP
225 IF (p_carrier_id = l_carrier_ids(i)) THEN
226 FTE_UTIL_PKG.Exit_Debug(l_module_name);
227 RETURN TRUE;
228 END IF;
229 END LOOP;
230
231 END IF;
232
233 FTE_UTIL_PKG.Exit_Debug(l_module_name);
234 RETURN FALSE;
235
236 EXCEPTION
237 WHEN OTHERS THEN
238
239 IF (GET_TARIFF_CARRIERS%ISOPEN) THEN
240 CLOSE GET_TARIFF_CARRIERS;
241 END IF;
242
243 x_error_msg := 'UNEXPECTED ERROR in' || l_module_name || ': ' || sqlerrm;
244 FTE_UTIL_PKG.Write_LogFile(l_module_name, x_error_msg);
245 FTE_UTIL_PKG.Exit_Debug(l_module_name);
246
247 END VERIFY_TARIFF_CARRIER;
248
249 --______________________________________________________________________________________--
250 --
251 -- FUNCTION: GET_TARIFF_RATECHARTS
252 --
253 -- PURPOSE
254 -- Get the ratechart ids belonging to the tariff <p_tariff>
255 --
256 -- IN Parameters
257 -- 1. p_tariff_name, The name of the tariff.
258 --
259 -- RETURNS:
260 -- The pricelist Ids
261 --______________________________________________________________________________________--
262
263 FUNCTION GET_TARIFF_RATECHARTS (p_tariff_name IN VARCHAR2,
264 x_error_msg OUT NOCOPY VARCHAR2)
265
266 RETURN WSH_UTIL_CORE.ID_TAB_TYPE IS
267
268 x_list_header_ids WSH_UTIL_CORE.ID_TAB_TYPE;
269 l_load_number NUMBER;
270 l_carrier_id NUMBER;
271 l_effective_date VARCHAR2(40);
272 l_expiry_date VARCHAR2(40);
273
274 --+
275 -- select distinct because a rate chart could be shared
276 -- by multiple lanes.
277 --+
278 CURSOR GET_TARIFF_CHARTS (p_load_number IN NUMBER,
279 p_carrier_id IN NUMBER) IS
280 SELECT distinct
281 lrc.list_header_id
282 FROM
283 fte_lanes l,
284 fte_lane_rate_charts lrc
285 WHERE
286 l.lane_id = lrc.lane_id AND
287 l.tariff_name = p_tariff_name AND
288 l.lane_type = 'LTL_' || p_tariff_name || '_' || p_load_number AND
289 l.carrier_id = p_carrier_id;
290
291 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.GET_TARIFF_RATECHARTS';
292
293 BEGIN
294 FTE_UTIL_PKG.Enter_Debug(l_module_name);
295
296 OPEN GET_LOAD_NUMBER(p_tariff_name => p_tariff_name);
297 FETCH GET_LOAD_NUMBER INTO l_load_number;
298
299 IF GET_LOAD_NUMBER%NOTFOUND THEN
300 x_error_msg := 'Tariff ' || p_tariff_name || ' does not have any existing data';
301 FTE_UTIL_PKG.Exit_Debug(l_module_name);
302 CLOSE GET_LOAD_NUMBER;
303 RETURN x_list_header_ids;
304 END IF;
305
306 CLOSE GET_LOAD_NUMBER;
307
308 OPEN GET_TARIFF_CARRIERS(p_tariff_name => p_tariff_name,
309 p_action_code => 'D');
310
311 FETCH GET_TARIFF_CARRIERS
312 INTO l_carrier_id, l_effective_date, l_expiry_date;
313
314 CLOSE GET_TARIFF_CARRIERS;
315
316 OPEN GET_TARIFF_CHARTS (l_load_number, l_carrier_id);
317
318 FETCH GET_TARIFF_CHARTS
319 BULK COLLECT INTO x_list_header_ids;
320 CLOSE GET_TARIFF_CHARTS;
321
322 IF (x_list_header_ids.COUNT <= 0) THEN
323 x_error_msg := 'Tariff ' || p_tariff_name || ' does not have any rate charts';
324 END IF;
325
326 FTE_UTIL_PKG.Exit_Debug(l_module_name);
327 RETURN x_list_header_ids;
328
329 EXCEPTION
330 WHEN OTHERS THEN
331 IF (GET_LOAD_NUMBER % ISOPEN) THEN
332 CLOSE GET_LOAD_NUMBER;
333 END IF;
334 IF (GET_TARIFF_CARRIERS % ISOPEN) THEN
335 CLOSE GET_TARIFF_CARRIERS;
336 END IF;
337 IF (GET_TARIFF_CHARTS % ISOPEN) THEN
338 CLOSE GET_TARIFF_CHARTS;
339 END IF;
340
341 x_error_msg := 'UNEXPECTED ERROR in getting rate charts for tariff ' || p_tariff_name || ': ' || sqlerrm;
342 FTE_UTIL_PKG.Write_LogFile(l_module_name, x_error_msg);
343 FTE_UTIL_PKG.Exit_Debug(l_module_name);
344
345 END GET_TARIFF_RATECHARTS;
346
347 --___________________________________________________________________________________--
348 --
349 -- PROCEDURE: BULK_INSERT_LANES
350 --
351 -- PURPOSE: To bulk insert the data stored in PL/SQL tables with the name LN_*
352 -- to the database.
353 --
354 --___________________________________________________________________________________--
355
356 PROCEDURE BULK_INSERT_LANES IS
357
358 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.BULK_INSERT_LANES';
359
360 BEGIN
361 FTE_UTIL_PKG.Enter_Debug(l_module_name);
362
363 FORALL i IN 1..LN_LANE_ID.COUNT
364 INSERT INTO fte_lanes( lane_id,
365 lane_number,
366 owner_id,
367 carrier_id,
368 origin_id,
369 destination_id,
370 mode_of_transportation_code,
371 commodity_detail_flag,
372 service_detail_flag,
373 equipment_detail_flag,
374 commodity_catg_id,
375 service_type_code,
376 basis,
377 pricelist_view_flag,
378 effective_date,
379 expiry_date,
380 comm_fc_class_code,
381 schedules_flag,
382 editable_flag,
383 lane_type,
384 tariff_name,
385 created_by,
386 creation_date,
387 last_updated_by,
388 last_update_date,
389 last_update_login)
390 VALUES ( LN_LANE_ID(i),
391 LN_LANE_ID(i),
392 -1,
393 LN_CARRIER_ID(i),
394 LN_ORIGIN_ID(i),
395 LN_DEST_ID(i),
396 'LTL',
397 'Y',
398 'Y',
399 'N',
400 LN_COMMODITY_CATG_ID(i),
401 G_SERVICE_CODE,
402 'WEIGHT',
403 'Y',
404 TO_DATE(LN_START_DATE(i), G_DATE_FORMAT),
405 TO_DATE(LN_END_DATE(i), G_DATE_FORMAT),
406 LN_COMM_FC_CLASS_CODE(i),
407 'N',
408 'N',
409 LN_LANE_TYPE(i),
410 LN_TARIFF_NAME(i),
411 G_USER_ID,
412 SYSDATE,
413 G_USER_ID,
414 SYSDATE,
415 G_USER_ID);
416
417 FORALL i IN 1..LN_LANE_ID.COUNT
418 INSERT INTO fte_lane_services(lane_service_id,
419 lane_id,
420 service_code,
421 created_by,
422 creation_date,
423 last_updated_by,
424 last_update_date,
425 last_update_login )
426 VALUES(fte_lane_services_s.nextval,
427 LN_LANE_ID(i),
428 G_SERVICE_CODE,
429 G_USER_ID,
430 SYSDATE,
431 G_USER_ID,
432 SYSDATE,
433 G_USER_ID);
434
435 LN_LANE_ID.DELETE;
436 LN_CARRIER_ID.DELETE;
437 LN_ORIGIN_ID.DELETE;
438 LN_DEST_ID.DELETE;
439 LN_COMMODITY_CATg_ID.DELETE;
440 LN_COMM_FC_CLASS_CODE.DELETE;
441 LN_LANE_TYPE.DELETE;
442 LN_TARIFF_NAME.DELETE;
443 LN_START_DATE.DELETE;
444 LN_END_DATE.DELETE;
445
446 FTE_UTIL_PKG.Exit_Debug(l_module_name);
447
448 EXCEPTION
449 WHEN OTHERS THEN
450 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR IN FTE_LTL_LOADER.BULK_INSERT_LANES', sqlerrm);
451 FTE_UTIL_PKG.Exit_Debug(l_module_name);
452 RAISE;
453 END BULK_INSERT_LANES;
454
455 --___________________________________________________________________________________--
456 --
457 -- PROCEDURE: BULK_INSERT_LANE_RATE_CHARTS
458 --
459 -- Purpose: To bulk insert the data stored in PL/SQL tables with the name LRC_*
460 -- to the database
461 --
462 --___________________________________________________________________________________--
463
464 PROCEDURE BULK_INSERT_LANE_RATE_CHARTS IS
465
466 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.BULK_INSERT_LANE_RATE_CHARTS';
467
468 BEGIN
469
470 FTE_UTIL_PKG.Enter_Debug(l_module_name);
471
472 FORALL i IN 1..LRC_LANE_ID.COUNT
473 INSERT INTO fte_lane_rate_charts(lane_id,
474 list_header_id,
475 start_date_active,
476 end_date_active,
477 created_by,
478 creation_date,
479 last_updated_by,
480 last_update_date,
481 last_update_login )
482 VALUES(LRC_LANE_ID(i),
483 LRC_LIST_HEADER_ID(i),
484 LRC_START_DATE(i),
485 LRC_END_DATE(i),
486 G_USER_ID,
487 SYSDATE,
488 G_USER_ID,
489 SYSDATE,
490 G_USER_ID);
491
492 LRC_LANE_ID.DELETE;
493 LRC_LIST_HEADER_ID.DELETE;
494 LRC_START_DATE.DELETE;
495 LRC_END_DATE.DELETE;
496
497 FTE_UTIL_PKG.Exit_Debug(l_module_name);
498
499 EXCEPTION
500 WHEN OTHERS THEN
501 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR IN FTE_LTL_LOADER.BULK_INSERT_LANE_RATE_CHARTS', sqlerrm);
502 FTE_UTIL_PKG.Exit_Debug(l_module_name);
503 RAISE;
504 END BULK_INSERT_LANE_RATE_CHARTS;
505
506 --___________________________________________________________________________________--
507 --
508 -- PROCEDURE: BULK_INSERT_LANE_PARAMETERS
509 --
510 -- Purpose: To bulk insert the data stored in PL/SQL tables with the name PRC_*
511 -- to the database
512 --
513 --___________________________________________________________________________________--
514
515 PROCEDURE BULK_INSERT_LANE_PARAMETERS IS
516
517 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.BULK_INSERT_LANE_PARAMETERS';
518
519 BEGIN
520 FTE_UTIL_PKG.Enter_Debug(l_module_name);
521
522 FORALL i IN 1..PRC_LANE_ID.COUNT
523 INSERT INTO fte_prc_parameters(value_from,
524 uom_code,
525 currency_code,
526 parameter_instance_id,
527 lane_id,
528 parameter_id,
529 created_by,
530 creation_date,
531 last_updated_by,
532 last_update_date,
533 last_update_login)
534 VALUES(PRC_VALUE_FROM(i),
535 G_LTL_UOM,
536 G_LTL_CURRENCY,
537 FTE_PRC_PARAMETERS_S.NEXTVAL,
538 PRC_LANE_ID(i),
539 PRC_PARAMETER_ID(i),
540 G_USER_ID,
541 SYSDATE,
542 G_USER_ID,
543 SYSDATE,
544 G_USER_ID);
545
546 -- Reset the tables
547 PRC_LANE_ID.DELETE;
548 PRC_VALUE_FROM.DELETE;
549 PRC_PARAMETER_ID.DELETE;
550
551 FTE_UTIL_PKG.Exit_Debug(l_module_name);
552
553 EXCEPTION
554 WHEN OTHERS THEN
555 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR IN FTE_LTL_LOADER_PKG.BULK_INSERT_LANE_PARAMETERS', sqlerrm);
556 FTE_UTIL_PKG.Exit_Debug(l_module_name);
557 RAISE;
558 END BULK_INSERT_LANE_PARAMETERS;
559
560 --___________________________________________________________________________________--
561 --
562 -- PROCEDURE: BULK_INSERT_LANE_COMMODITIES
563 --
564 -- Purpose: To bulk insert the data stored in PL/SQL tables with the name CM_*
565 -- to the database
566 --
567 --___________________________________________________________________________________--
568
569 PROCEDURE BULK_INSERT_LANE_COMMODITIES IS
570
571 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.BULK_INSERT_LANE_COMMODITIES';
572
573 BEGIN
574 FTE_UTIL_PKG.Enter_Debug(l_module_name);
575
576 FORALL i IN 1..CM_LANE_ID.COUNT
577
578 INSERT INTO FTE_LANE_COMMODITIES(lane_commodity_id,
579 lane_id,
580 commodity_catg_id,
581 created_by,
582 creation_date,
583 last_updated_by,
584 last_update_date,
585 last_update_login)
586 VALUES(FTE_BULKLOAD_DATA_S.NEXTVAL,
587 CM_LANE_ID(i),
588 CM_CATg_ID(i),
589 G_USER_ID,
590 SYSDATE,
591 G_USER_ID,
592 SYSDATE,
593 G_USER_ID);
594 CM_LANE_ID.DELETE;
595 CM_CATG_ID.DELETE;
596
597 FTE_UTIL_PKG.Exit_Debug(l_module_name);
598 EXCEPTION
599 WHEN OTHERS THEN
600 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR IN FTE_LTL_LOADER.BULK_INSERT_LANE_PARAMETERS', sqlerrm);
601 FTE_UTIL_PKG.Exit_Debug(l_module_name);
602 RAISE;
603 END BULK_INSERT_LANE_COMMODITIES;
604
605 --_________________________________________________________________________________________--
606 --
607 -- PROCEDURE: GENERATE_LTL_REPORT
608 --
609 -- PURPOSE
610 -- Writes the following data to the OUTPUT file
611 -- 1) The Zones Created.
612 -- 2) The Rate Charts Created.
613 -- 3) The Services Created.
614 --
615 -- PARAMETER
616 -- IN
617 -- p_load_id,
618 -- p_load_type,
619 --
620 --_________________________________________________________________________________________--
621
622 PROCEDURE GENERATE_LTL_REPORT(p_load_id IN NUMBER,
623 p_load_number IN NUMBER,
624 p_tariff_name IN VARCHAR2,
625 x_error_msg OUT NOCOPY VARCHAR2,
626 x_status OUT NOCOPY NUMBER) IS
627
628 zoneNames ZONENAMESTAB;
629 l_lane_numbers LANE_NUMBER_TAB;
630 l_origins ZONE_TAB;
631 l_dests ZONE_TAB;
632 l_chart_names RATECHARTNAMESTAB;
633 l_min_charges MIN_CHARGE_TAB;
634 l_carriers CARRIER_NAME_TAB;
635 rateCharts RATECHARTNAMESTAB;
636
637 l_num_reused_zones NUMBER;
638
639
640 CURSOR NEW_ZONES (p_load_id IN NUMBER) IS
641 SELECT
642 zone_name
643 FROM
644 fte_interface_zones
645 WHERE
646 load_id = p_load_id AND
647 hash_value <> 0;
648
649 CURSOR NEW_SERVICES_INFO (p_load_number IN NUMBER, p_tariff_name IN VARCHAR2) IS
650 SELECT
651 l.lane_number,
652 oz.zone Origin,
653 dz.zone Destination,
654 hzp.party_name carrier_name,
655 qlht.name rate_chart_name,
656 prc.value_from minimum_charge
657 FROM
658 fte_lanes l,
659 hz_parties hzp,
660 qp_list_headers_tl qlht,
661 wsh_regions_tl oz,
662 wsh_regions_tl dz,
663 fte_lane_rate_charts flrc,
664 fte_prc_parameters prc
665 WHERE
666 l.tariff_name = p_tariff_name AND
667 l.lane_type = 'LTL_' || p_tariff_name || '_' || p_load_number AND
668 l.lane_id = prc.lane_id AND
669 prc.parameter_id = g_min_charge_id AND
670 oz.language = dz.language AND
671 oz.language = userenv('LANG') AND
672 l.origin_id = oz.region_id AND
673 l.destination_id = dz.region_id AND
674 l.lane_id = flrc.lane_id AND
675 flrc.list_header_id = qlht.list_header_id AND
676 qlht.language = userenv('LANG') AND
677 hzp.party_id = l.carrier_id;
678
679 CURSOR NEW_RATE_CHARTS (p_load_number IN NUMBER, p_tariff_name IN VARCHAR2) IS
680 SELECT DISTINCT
681 qlht.name rate_chart_name
682 FROM
683 fte_lanes l,
684 fte_lane_rate_charts flrc,
685 qp_list_headers_tl qlht
686 WHERE
687 l.tariff_name = p_tariff_name AND
688 l.lane_type = 'LTL_' || p_tariff_name || '_' || p_load_number AND
689 l.lane_id = flrc.lane_id AND
690 flrc.list_header_id = qlht.list_header_id AND
691 qlht.language = userenv('LANG');
692
693 l_load_number NUMBER;
694 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.GENERATE_LTL_REPORT';
695 l_msg VARCHAR2(2000);
696
697 BEGIN
698
699 FTE_UTIL_PKG.Enter_Debug(l_module_name);
700 x_status := -1;
701
702 l_load_number := p_load_number;
703
704 IF l_load_number IS NULL THEN
705 OPEN GET_LOAD_NUMBER(p_tariff_name);
706 FETCH GET_LOAD_NUMBER INTO l_load_number;
707 CLOSE GET_LOAD_NUMBER;
708 END IF;
709
710 OPEN NEW_ZONES(p_load_id);
711 FETCH NEW_ZONES BULK COLLECT INTO zoneNames;
712
713 OPEN NEW_SERVICES_INFO(l_load_number,p_tariff_name);
714 FETCH NEW_SERVICES_INFO BULK COLLECT INTO l_lane_numbers, l_origins, l_dests, l_carriers, l_chart_names, l_min_charges;
715
716 OPEN NEW_RATE_CHARTS(l_load_number,p_tariff_name);
717 FETCH NEW_RATE_CHARTS BULK COLLECT INTO rateCharts;
718
719 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
720 p_msg => '+---------------------------------------------------------------------------+',
721 p_category => NULL);
722 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
723 p_msg => ' ',
724 p_category => NULL);
725
726 l_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_BULKLOAD_START_REPORT'); -- *** Start of BulkLoader Report ***
727
728 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
729 p_msg => l_msg,
730 p_category => NULL);
731
732 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
733 p_msg => ' ',
734 p_category => NULL);
735
736 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
737 p_msg => '+---------------------------------------------------------------------------+',
738 p_category => NULL);
739
740 --+
741 -- Print the Header Info already remembered in the global variable
742 -- "g_report_header"
743 --+
744
745 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
746 p_msg => 'Type of Process : LTL Carrier',
747 p_category => NULL);
748 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
749 p_msg => 'Start Date : ' || g_report_header.StartDate,
750 p_category => NULL);
751 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
752 p_msg => 'End Date : ' || to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS'),
753 p_category => NULL);
754 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
755 p_msg => 'File Name : ' || g_report_header.FileName,
756 p_category => NULL);
757 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
758 p_msg => 'Tariff Name : ' || g_report_header.TariffName,
759 p_category => NULL);
760 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
761 p_msg => 'Service Level : ' || g_report_header.ServiceLevel,
762 p_category => NULL);
763 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
764 p_msg => 'Origin Country : ' || g_report_header.Orig_Country,
765 p_category => NULL);
766 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
767 p_msg => 'Destination Country : ' || g_report_header.Dest_Country,
768 p_category => NULL);
769 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
770 p_msg => 'Currency : ' || g_report_header.Currency,
771 p_category => NULL);
772 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
773 p_msg => 'UOM : ' || g_report_header.UOM,
774 p_category => NULL);
775
776 l_num_reused_zones := rateCharts.COUNT - zoneNames.COUNT + 1;
777
778 --+
779 -- Print the Details part
780 --+
781 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
782 p_msg => ' ',
783 p_category => NULL);
784 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
785 p_msg =>'Created :',
786 p_category => NULL);
787 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
788 p_msg => zoneNames.COUNT ||' New zones. Reused ' || l_num_reused_zones || ' zones.',
789 p_category => NULL);
790 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
791 p_msg => l_lane_numbers.COUNT || ' Services',
792 p_category => NULL);
793 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
794 p_msg => rateCharts.COUNT || ' Rate Charts',
795 p_category => NULL);
796 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
797 p_msg => ' ',
798 p_category => NULL);
799 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
800 p_msg => 'Zones Information (Zone Name)',
801 p_category => NULL);
802
803 FOR i in 1..zoneNames.COUNT LOOP
804 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
805 p_msg => zoneNames(i),
806 p_category => NULL);
807 END LOOP;
808
809 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
810 p_msg => ' ',
811 p_category => NULL);
812 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
813 p_msg => 'Services Information',
814 p_category => NULL);
815 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
816 p_msg => 'Lane Number, Origin, Destination, Carrier, Rate Chart Name, Minimum Charge',
817 p_category => NULL);
818
819 FOR i in 1..l_lane_numbers.COUNT LOOP
820 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
821 p_msg => l_lane_numbers(i) || ', ' || l_origins(i) || ', '
822 || l_dests(i) || ', ' || l_carriers(i) || ', ' || l_chart_names(i) || ', ' || l_min_charges(i),
823 p_category => NULL);
824 END LOOP;
825
826 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
827 p_msg => ' ',
828 p_category => NULL);
829 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
830 p_msg => 'Rate Chart Information (Name)',
831 p_category => NULL);
832
833 FOR i in rateCharts.FIRST..rateCharts.LAST LOOP
834 FTE_UTIL_PKG.Write_OutFile(p_module_name => l_module_name,
835 p_msg => rateCharts(i),
836 p_category => NULL);
837 END LOOP;
838
839 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
840 p_msg => '+---------------------------------------------------------------------------+',
841 p_category => NULL);
842 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
843 p_msg => ' ',
844 p_category => NULL);
845
846 l_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_BULKLOAD_END_REPORT'); -- *** End of BulkLoader Report ***
847
848 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
849 p_msg => l_msg,
850 p_category => NULL);
851
852 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
853 p_msg => ' ',
854 p_category => NULL);
855
856 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
857 p_msg => '+---------------------------------------------------------------------------+',
858 p_category => NULL);
859
860
861 IF NEW_ZONES%ISOPEN THEN
862 CLOSE NEW_ZONES;
863 END IF;
864
865 IF NEW_SERVICES_INFO%ISOPEN THEN
866 CLOSE NEW_SERVICES_INFO;
867 END IF;
868
869 IF NEW_RATE_CHARTS%ISOPEN THEN
870 CLOSE NEW_RATE_CHARTS;
871 END IF;
872
873 FTE_UTIL_PKG.Exit_Debug(l_module_name);
874
875 EXCEPTION
876
877 WHEN OTHERS THEN
878
879 IF NEW_ZONES%ISOPEN THEN
880 CLOSE NEW_ZONES;
881 END IF;
882
883 IF NEW_SERVICES_INFO%ISOPEN THEN
884 CLOSE NEW_SERVICES_INFO;
885 END IF;
886
887 IF NEW_RATE_CHARTS%ISOPEN THEN
888 CLOSE NEW_RATE_CHARTS;
889 END IF;
890
891 x_error_msg := sqlerrm;
892 x_status := 2;
893 FTE_UTIL_PKG.Write_LogFile(l_module_name, sqlerrm);
894 FTE_UTIL_PKG.Exit_Debug(l_module_name);
895
896 END GENERATE_LTL_REPORT;
897
898 --_________________________________________________________________________________--
899 --
900 -- PROCEDURE: OBSOLETE_PREVIOUS_LOAD
901 --
902 -- PURPOSE Obsolete the lanes from a previous LTL load. This procedure
903 -- uses the lane_type column in FTE_LANES to identify the lanes
904 -- to be obsoleted.
905 --
906 -- PARAMETERS
907 -- IN
908 -- p_lane_type, The lane_type of the lanes to be obsoleted.
909 -- OUT
910 -- x_status, the return status of the procedure,
911 -- -1, success
912 -- any other non negative value indicates failure.
913 -- x_error_msg, the error message indicating the cause and detailing the error occured.
914 --_________________________________________________________________________________--
915
916 PROCEDURE OBSOLETE_PREVIOUS_LOAD (p_lane_type IN VARCHAR2,
917 p_delete_lanes IN BOOLEAN,
918 x_status OUT NOCOPY NUMBER,
919 x_error_msg OUT NOCOPY VARCHAR2) IS
920
921 l_lane_ids NUMBER_TAB;
922 l_effective_dates STRINGARRAY;
923 l_expiry_dates STRINGARRAY;
924
925 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.OBSOLETE_PREVIOUS_LOAD';
926
927 BEGIN
928
929 FTE_UTIL_PKG.Enter_Debug(l_module_name);
930 x_status := -1;
931
932 IF(FTE_BULKLOAD_PKG.g_debug_on) THEN
933
934 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Obsoleting Lanes...');
935
936 IF (p_delete_lanes) THEN
937 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_delete_lanes', 'true');
938 ELSE
939 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_delete_lanes', 'false');
940 END IF;
941
942 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_lane_type ', p_lane_type);
943 END IF;
944
945 --+
946 -- To 'delete' the lanes, set the effective date range
947 -- to one that doesn't make sense, and change the editable flag.
948 --+
949 IF p_delete_lanes THEN
950
951 UPDATE
952 FTE_LANES
953 SET
954 expiry_date = sysdate-1,
955 effective_date = sysdate,
956 editable_flag = 'D',
957 last_updated_by = G_USER_ID,
958 last_update_date = SYSDATE,
959 last_update_login = G_USER_ID
960 WHERE
961 lane_type LIKE p_lane_type
962 RETURNING
963 lane_id, effective_date, expiry_date
964 BULK COLLECT INTO
965 l_lane_ids, l_effective_dates, l_expiry_dates;
966
967 ELSE
968
969 UPDATE
970 FTE_LANES
971 SET
972 expiry_date = (G_VALID_DATE-Fnd_Number.Canonical_To_Number('0.0001')),
973 last_updated_by = G_USER_ID,
974 last_update_date = SYSDATE,
975 last_update_login = G_USER_ID
976 WHERE
977 lane_type LIKE p_lane_type AND
978 nvl(expiry_date, G_VALID_DATE) >= G_VALID_DATE
979 RETURNING
980 lane_id, effective_date, expiry_date
981 BULK COLLECT INTO
982 l_lane_ids, l_effective_dates, l_expiry_dates;
983
984 END IF;
985
986 IF(FTE_BULKLOAD_PKG.g_debug_on) THEN
987 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Obsoleted ' || sql%rowcount || ' lanes.');
988 END IF;
989
990 FORALL i in 1..l_lane_ids.COUNT
991 UPDATE
992 FTE_LANE_RATE_CHARTS
993 SET
994 START_DATE_ACTIVE = l_effective_dates(i),
995 END_DATE_ACTIVE = l_expiry_dates(i),
996 last_updated_by = G_USER_ID,
997 last_update_date = SYSDATE,
998 last_update_login = G_USER_ID
999 WHERE
1000 list_header_id = (select list_header_id
1001 from fte_lane_rate_charts
1002 where lane_id = l_lane_ids(i));
1003
1004 IF(FTE_BULKLOAD_PKG.g_debug_on) THEN
1005 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Obsolete Lane Rate Charts');
1006 END IF;
1007
1008 FORALL i in 1..l_lane_ids.COUNT
1009 UPDATE
1010 QP_LIST_HEADERS_B
1011 SET
1012 START_DATE_ACTIVE = l_effective_dates(i),
1013 END_DATE_ACTIVE = l_expiry_dates(i),
1014 last_updated_by = G_USER_ID,
1015 last_update_date = SYSDATE,
1016 last_update_login = G_USER_ID
1017 WHERE
1018 LIST_HEADER_ID = (SELECT list_header_id
1019 FROM fte_lane_rate_charts
1020 WHERE lane_id = l_lane_ids(i));
1021
1022 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1023 EXCEPTION
1024 WHEN OTHERS THEN
1025 x_status := 2;
1026 x_error_msg := 'Unexpected error while obsoleting previous lanes => ' || sqlerrm;
1027 FTE_UTIL_PKG.Write_LogFile(l_module_name, x_error_msg);
1028 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1029
1030 END OBSOLETE_PREVIOUS_LOAD;
1031
1032 --___________________________________________________________________________________--
1033 --
1034 -- PROCEDURE: ADD_CARRIERS_TO_RATECHARTS
1035 --
1036 -- PURPOSE
1037 -- Add new carriers as qualifiers to existing rate charts of a tariff.
1038 --
1039 -- PARAMETERS
1040 -- IN
1041 -- p_tariff_name
1042 -- p_load_number
1043 -- p_carrier_ids
1044 -- p_load_id
1045 -- OUT
1046 -- x_status, the return status of the procedure,
1047 -- -1, success
1048 -- any other non negative value indicates failure.
1049 -- x_error_msg, the error message indicating the cause and detailing the error occured.
1050 --___________________________________________________________________________________--
1051
1052 PROCEDURE ADD_CARRIERS_TO_RATECHARTS (p_tariff_name IN VARCHAR2,
1053 p_load_number IN NUMBER,
1054 p_carrier_ids IN NUMBER_TAB,
1055 p_load_id IN NUMBER,
1056 x_status OUT NOCOPY NUMBER,
1057 x_error_msg OUT NOCOPY VARCHAR2) IS
1058
1059 l_effective_dates STRINGARRAY;
1060 l_start_dates STRINGARRAY;
1061
1062 l_list_header_ids NUMBER_TAB;
1063 l_rate_names STRINGARRAY;
1064 l_descriptions VAR_ARR4000;
1065
1066 l_currency_codes STRINGARRAY;
1067 l_existing_carriers NUMBER_TAB;
1068 l_max_groups NUMBER_TAB;
1069
1070 l_process_id NUMBER;
1071 l_process_ids NUMBER_TAB;
1072
1073 l_rate_hdr_data FTE_BULKLOAD_PKG.data_values_tbl;
1074 l_rate_hdr_block_tbl FTE_BULKLOAD_PKG.block_data_tbl;
1075
1076 l_qualifier_data FTE_BULKLOAD_PKG.data_values_tbl;
1077 l_qualifier_block_tbl FTE_BULKLOAD_PKG.block_data_tbl;
1078
1079 l_currency_tbl FTE_RATE_CHART_PKG.LH_CURRENCY_CODE_TAB;
1080 l_name FTE_RATE_CHART_PKG.LH_NAME_TAB;
1081
1082 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.ADD_CARRIERS_TO_RATECHARTS';
1083
1084 CURSOR GET_RATE_CHART_DETAILS IS
1085 SELECT
1086 MAX(lh.list_header_id),
1087 MAX(lh.name),
1088 MAX(lh.description),
1089 MAX(b.currency_code),
1090 MAX(ql.qualifier_attr_value),
1091 MAX(ql.qualifier_grouping_no),
1092 MAX(b.start_date_active)
1093 FROM
1094 qp_list_headers_tl lh,
1095 qp_list_headers_b b,
1096 qp_qualifiers ql,
1097 fte_lane_rate_charts lrc,
1098 fte_lanes l
1099 WHERE
1100 l.tariff_name = p_tariff_name AND
1101 l.lane_type = 'LTL_' || p_tariff_name || '_' || p_load_number AND
1102 l.lane_id = lrc.lane_id AND
1103 lh.list_header_id = lrc.list_header_id AND
1104 lh.list_header_id = ql.list_header_id AND
1105 lh.list_header_id = b.list_header_id AND
1106 ql.qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' AND
1107 ql.qualifier_context = 'PARTY' AND
1108 lh.language = USERENV('LANG')
1109 GROUP BY
1110 lh.list_header_id;
1111 BEGIN
1112
1113 FTE_UTIL_PKG.Enter_Debug(l_module_name);
1114 x_status := -1;
1115
1116 OPEN GET_RATE_CHART_DETAILS;
1117
1118 FETCH GET_RATE_CHART_DETAILS
1119 BULK COLLECT INTO l_list_header_ids,
1120 l_rate_names,
1121 l_descriptions,
1122 l_currency_codes,
1123 l_existing_carriers,
1124 l_max_groups,
1125 l_start_dates;
1126
1127 CLOSE GET_RATE_CHART_DETAILS;
1128
1129 IF (l_list_header_ids.COUNT <= 0) THEN
1130 x_status := 2;
1131 x_error_msg := 'Error in updating LTL rates. Previous load does not exist.';
1132 FTE_UTIL_PKG.Write_LogFile(l_module_name, x_error_msg);
1133 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1134 RETURN;
1135 END IF;
1136
1137 IF(FTE_BULKLOAD_PKG.g_debug_on) THEN
1138 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Number of ratecharts to modify',l_list_header_ids.COUNT);
1139 END IF;
1140
1141 FOR i IN 1..l_list_header_ids.COUNT
1142 LOOP
1143 -- Get a process id
1144 SELECT qp_process_id_s.NEXTVAL
1145 INTO l_process_id
1146 FROM DUAL;
1147
1148 l_process_ids(l_process_ids.COUNT + 1) := l_process_id;
1149
1150 FTE_BULKLOAD_PKG.g_load_id := l_process_id;
1151
1152 l_rate_hdr_data('ACTION') := 'APPEND';
1153 l_rate_hdr_data('LTL_RATE_CHART_NAME') := l_rate_names(i);
1154 l_rate_hdr_data('CARRIER_ID') := l_existing_carriers(1);
1155 l_rate_hdr_data('DESCRIPTION') := l_descriptions(i);
1156 l_rate_hdr_data('CURRENCY') := l_currency_codes(i);
1157 l_rate_hdr_data('ATTRIBUTE1') := 'LTL_RC';
1158 l_rate_hdr_data('START_DATE') := to_char(l_start_dates(i),FTE_BULKLOAD_PKG.G_DATE_FORMAT);
1159
1160 l_rate_hdr_block_tbl(1) := l_rate_hdr_data;
1161
1162 FTE_RATE_CHART_LOADER.PROCESS_RATE_CHART(p_block_header => g_dummy_block_hdr_tbl,
1163 p_block_data => l_rate_hdr_block_tbl,
1164 p_line_number => 0,
1165 p_validate_column => FALSE,
1166 p_process_id => l_process_id,
1167 x_status => x_status,
1168 x_error_msg => x_error_msg);
1169
1170 IF x_status <> -1 THEN
1171 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1172 RETURN;
1173 END IF;
1174
1175 -- for each rate chart, add all the carriers
1176 FOR m IN 1..p_carrier_ids.COUNT LOOP
1177
1178 l_qualifier_data('ACTION') := 'ADD';
1179 l_qualifier_data('PROCESS_ID') := l_process_id;
1180 l_qualifier_data('ATTRIBUTE') := 'SUPPLIER';
1181 l_qualifier_data('VALUE') := p_carrier_ids(m);
1182 l_qualifier_data('CONTEXT') := 'PARTY';
1183 l_qualifier_data('GROUP') := l_max_groups(i)+m;
1184
1185 l_qualifier_block_tbl(1) := l_qualifier_data;
1186
1187 FTE_RATE_CHART_LOADER.PROCESS_QUALIFIER(p_block_header => g_dummy_block_hdr_tbl,
1188 p_block_data => l_qualifier_block_tbl,
1189 p_line_number => 0,
1190 x_status => x_status,
1191 x_error_msg => x_error_msg);
1192
1193 IF (x_status <> -1) THEN
1194 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1195 RETURN;
1196 END IF;
1197 END LOOP;
1198 END LOOP;
1199
1200 -- Inserting Data into interface tables
1201 FTE_RATE_CHART_LOADER.SUBMIT_QP_PROCESS(p_qp_call => FALSE,
1202 x_status => x_status,
1203 x_error_msg => x_error_msg);
1204
1205 IF (x_status <> -1) THEN
1206 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1207 RETURN;
1208 END IF;
1209
1210 IF(FTE_BULKLOAD_PKG.g_debug_on) THEN
1211 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Calling QP for update ...');
1212 END IF;
1213
1214 FOR i IN 1..l_process_ids.COUNT
1215 LOOP
1216 l_name(1) := l_rate_names(i);
1217 l_currency_tbl(1) := l_currency_codes(i);
1218
1219 FTE_RATE_CHART_PKG.QP_API_CALL(p_chart_type => 'LTL_RATE_CHART',
1220 p_process_id => l_process_ids(i),
1221 p_name => l_name,
1222 p_currency => l_currency_tbl,
1223 x_status => x_status,
1224 x_error_msg => x_error_msg);
1225
1226 IF (x_status <> -1) THEN
1227 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1228 RETURN;
1229 END IF;
1230 END LOOP;
1231
1232 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1233
1234 EXCEPTION
1235
1236 WHEN OTHERS THEN
1237
1238 IF (GET_RATE_CHART_DETAILS%ISOPEN) THEN
1239 CLOSE GET_RATE_CHART_DETAILS;
1240 END IF;
1241
1242 x_status := 2;
1243 x_error_msg := SQLERRM;
1244 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR OCCURED', SQLERRM);
1245 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1246
1247 END ADD_CARRIERS_TO_RATECHARTS;
1248
1249 --_____________________________________________________________________________________--
1250 -- --
1251 -- PROCEDURE: GET_PARAMETER_DEFAULTS --
1252 -- --
1253 -- Purpose --
1254 -- Populates all the default parameters in the global variables from --
1255 -- the table FTE_PRC_PARAMETER_DEFAULTS. --
1256 -- --
1257 -- The Parameters are: --
1258 -- G_LANE_FUNCTION_ID G_MIN_CHARGE_ID G_DEF_WT_ENABLED_ID G_DEF_WT_BREAK_ID --
1259 --
1260 -- OUT
1261 -- x_status, the return status of the procedure,
1262 -- -1, success
1263 -- any other non negative value indicates failure.
1264 -- x_error_msg, the error message indicating the cause and detailing the error occured.
1265 --_____________________________________________________________________________________--
1266
1267 PROCEDURE GET_PARAMETER_DEFAULTS (x_status OUT NOCOPY VARCHAR2,
1268 x_error_msg OUT NOCOPY VARCHAR2) IS
1269
1270 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.GET_PARAMETER_DEFAULTS';
1271
1272 BEGIN
1273 FTE_UTIL_PKG.Enter_Debug(l_module_name);
1274 x_status := -1;
1275
1276 IF (G_LANE_FUNCTION_ID is null) THEN
1277 SELECT
1278 parameter_id
1279 INTO
1280 G_LANE_FUNCTION_ID
1281 FROM
1282 FTE_PRC_PARAMETER_DEFAULTS
1283 WHERE
1284 parameter_type = 'PARAMETER' AND
1285 parameter_sub_type = 'LANE' AND
1286 parameter_name = 'LANE_FUNCTION' AND
1287 lane_function = 'NONE';
1288 END IF;
1289
1290 IF (G_MIN_CHARGE_ID is null) THEN
1291 SELECT
1292 parameter_id
1293 INTO
1294 G_MIN_CHARGE_ID
1295 FROM
1296 FTE_PRC_PARAMETER_DEFAULTS
1297 WHERE
1298 parameter_type = 'PARAMETER' AND
1299 parameter_sub_type = 'MIN_CHARGE' AND
1300 parameter_name = 'MIN_CHARGE_AMT' AND
1301 lane_function = 'NONE';
1302 END IF;
1303
1304 IF (G_DEF_WT_ENABLED_ID is null) THEN
1305
1306 SELECT
1307 parameter_id
1308 INTO
1309 G_DEF_WT_ENABLED_ID
1310 FROM
1311 FTE_PRC_PARAMETER_DEFAULTS
1312 WHERE
1313 parameter_type = 'PARAMETER' AND
1314 parameter_sub_type = 'DEFICIT_WT' AND
1315 parameter_name = 'ENABLED' AND
1316 lane_function = 'LTL';
1317
1318 END IF;
1319
1320 IF (G_DEF_WT_BREAK_ID is null) THEN
1321
1322 SELECT
1323 parameter_id
1324 INTO
1325 G_DEF_WT_BREAK_ID
1326 FROM
1327 FTE_PRC_PARAMETER_DEFAULTS
1328 WHERE
1329 parameter_type = 'PARAMETER' AND
1330 parameter_sub_type = 'DEFICIT_WT' AND
1331 parameter_name = 'WT_BREAK_POINT' AND
1332 lane_function = 'LTL';
1333
1334 END IF;
1335
1336 IF(FTE_BULKLOAD_PKG.g_debug_on) THEN
1337 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'G_LANE_FUNCTION_ID ', G_LANE_FUNCTION_ID );
1338 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'G_MIN_CHARGE_ID ', G_MIN_CHARGE_ID);
1339 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'G_DEF_WT_ENABLED_ID', G_DEF_WT_ENABLED_ID);
1340 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'G_DEF_WT_BREAK_ID ', G_DEF_WT_BREAK_ID);
1341 END IF;
1342
1343 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1344
1345 EXCEPTION
1346 WHEN NO_DATA_FOUND THEN
1347 x_status := 2;
1348 x_error_msg := 'The setup for pricing parameters defaults is invalid';
1349 FTE_UTIL_PKG.Write_LogFile(p_module_name => l_module_name,
1350 p_message => x_error_msg);
1351
1352 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1353 WHEN OTHERS THEN
1354 x_status := 2;
1355 x_error_msg := SQLERRM;
1356 FTE_UTIL_PKG.Write_LogFile(p_module_name => l_module_name,
1357 p_message => sqlerrm);
1358
1359 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1360 END GET_PARAMETER_DEFAULTS;
1361
1362 --_________________________________________________________________________________--
1363 --
1364 -- PROCEDURE: CLEANUP_TABLES
1365 --
1366 -- Purpose
1367 -- Clean up the temporary tables after the rate chart loading process.
1368 -- Also roll back the effects of a "commit" when an error is encountered.
1369 -- The "rollback" is necessary because we have to commit before we launch the
1370 -- sub-processes for rate chart loading. If an error is encountered after
1371 -- rate chart loading, we have to undo the commit.
1372 --
1373 -- PARAMETERS
1374 -- IN
1375 -- p_load_id, The load id of the bulkload job.
1376 -- p_abort, This should be set to true if we are aborting
1377 -- and want to get rid of all data. If abort is false,
1378 -- we only delete data in the interface tables.
1379 -- p_tariff_name, the tariff name
1380 -- p_action_code,
1381 --
1382 -- OUT
1383 -- x_status, the return status of the procedure,
1384 -- -1, success
1385 -- any other non negative value indicates failure.
1386 -- x_error_msg, the error message indicating the cause and detailing the error occured.
1387 --_________________________________________________________________________________--
1388
1389 PROCEDURE CLEANUP_TABLES (p_load_id IN NUMBER,
1390 p_abort IN BOOLEAN,
1391 p_tariff_name IN VARCHAR2,
1392 p_action_code IN VARCHAR2,
1393 p_save_data IN NUMBER,
1394 x_status OUT NOCOPY VARCHAR2,
1395 x_error_msg OUT NOCOPY VARCHAR2) IS
1396
1397 CURSOR GET_PROCESS_IDS IS
1398 SELECT
1399 process_id,
1400 rate_chart_name
1401 FROM
1402 fte_interface_lanes
1403 WHERE
1404 load_id = p_load_id;
1405
1406 l_process_id NUMBER;
1407 l_chart_name VARCHAR2(100);
1408 l_num_lh NUMBER := 0;
1409 l_num_ql NUMBER := 0;
1410 l_num_at NUMBER := 0;
1411 l_num_ll NUMBER := 0;
1412 l_num_ln NUMBER := 0;
1413 l_num_zn NUMBER := 0;
1414 l_num_lane NUMBER := 0;
1415 l_abort VARCHAR2(30);
1416
1417 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.CLEANUP_TABLES';
1418
1419 BEGIN
1420 FTE_UTIL_PKG.Enter_Debug(l_module_name);
1421 x_status := -1;
1422
1423 IF (p_abort) THEN
1424 l_abort := 'Yes';
1425 ELSE
1426 l_abort := 'No';
1427 END IF;
1428
1429 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
1430 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Cleanup Tables: Abort', ' ' || l_abort || ' ' );
1431 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_load_id', p_load_id);
1432 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Save Interface data', p_save_data);
1433 END IF;
1434
1435 FND_CONC_GLOBAL.set_req_globals(conc_status => 'COMPLETED', request_data => NULL);
1436
1437 OPEN GET_PROCESS_IDS;
1438 LOOP
1439 FETCH GET_PROCESS_IDS INTO l_process_id, l_chart_name;
1440 EXIT WHEN GET_PROCESS_IDS%NOTFOUND;
1441
1442 IF (p_abort) THEN
1443
1444 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
1445 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleting Data From QP Tables');
1446 END IF;
1447
1448 DELETE FROM
1449 QP_LIST_HEADERS_B
1450 WHERE
1451 list_header_id IN (SELECT list_header_id
1452 FROM qp_list_headers_tl
1453 WHERE name = l_chart_name);
1454 l_num_lh := l_num_lh + SQL%ROWCOUNT;
1455
1456 DELETE FROM
1457 QP_QUALIFIERS
1458 WHERE
1459 list_header_id IN (SELECT list_header_id
1460 FROM qp_list_headers_tl
1461 WHERE name = l_chart_name);
1462
1463 l_num_ql := l_num_ql + SQL%ROWCOUNT;
1464
1465 DELETE FROM
1466 QP_LIST_LINES
1467 WHERE
1468 list_header_id IN (SELECT list_header_id
1469 FROM qp_list_headers_tl
1470 WHERE name = l_chart_name);
1471 l_num_ll := l_num_ll + SQL%ROWCOUNT;
1472
1473 DELETE FROM
1474 QP_PRICING_ATTRIBUTES
1475 WHERE list_header_id IN (SELECT list_header_id
1476 FROM qp_list_headers_tl
1477 WHERE name = l_chart_name);
1478
1479 l_num_at := l_num_at + SQL%ROWCOUNT;
1480
1481 DELETE FROM
1482 FTE_LANE_RATE_CHARTS
1483 WHERE
1484 list_header_id IN (SELECT list_header_id
1485 FROM qp_list_headers_tl
1486 WHERE name = l_chart_name);
1487
1488 l_num_lane := l_num_lane + SQL%ROWCOUNT;
1489
1490 DELETE FROM
1491 QP_LIST_HEADERS_TL
1492 WHERE name = l_chart_name;
1493
1494 DELETE FROM
1495 FTE_INTERFACE_ZONES
1496 WHERE load_id = p_load_id;
1497
1498 l_num_zn := l_num_zn + SQL%ROWCOUNT;
1499 END IF;
1500
1501 IF p_save_data <> 1 THEN
1502
1503 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
1504 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleting Data From QP Interface Tables For Process Id ' || l_process_id);
1505 END IF;
1506
1507 DELETE FROM QP_INTERFACE_LIST_LINES WHERE process_id = l_process_id;
1508 DELETE FROM QP_INTERFACE_QUALIFIERS WHERE process_id = l_process_id;
1509 DELETE FROM QP_INTERFACE_PRICINg_ATTRIBS WHERE process_id = l_process_id;
1510 DELETE FROM QP_INTERFACE_LIST_HEADERS WHERE process_id = l_process_id;
1511 END IF;
1512 END LOOP;
1513
1514 CLOSE get_process_ids;
1515 -- delete from fte_bulkload_data where load_id = p_load_id; --could be too expensive..
1516
1517 DELETE FROM fte_interface_lanes WHERE load_id = p_load_id;
1518
1519 l_num_ln := SQL%ROWCOUNT;
1520
1521 DELETE FROM fte_interface_zones WHERE zone_id IS NULL;
1522
1523 l_num_zn := l_num_zn + SQL%ROWCOUNT;
1524
1525 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1526 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_lh || ' from QP_LIST_HEADERS');
1527 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_ll || ' from QP_LIST_LINES');
1528 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_at || ' from QP_PRICINg_ATTRIBUTES');
1529 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_ql || ' from QP_QUALIFIERS');
1530 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_ln || ' from FTE_INTERFACE_LANES');
1531 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_zn || ' from FTE_INTERFACE_ZONES');
1532 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_lane || ' from FTE_LANES');
1533 END IF;
1534
1535 --+
1536 -- Cleanup FTE_TARIFF_CARRIERS
1537 --+
1538 DELETE FROM
1539 fte_tariff_carriers
1540 WHERE
1541 tariff_name = p_tariff_name AND
1542 action_code = 'N';
1543
1544 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1545 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || SQL%ROWCOUNT || ' rows from fte_tariff_carriers for tariff ' || p_tariff_name);
1546 END IF;
1547
1548 UPDATE
1549 fte_tariff_carriers
1550 SET
1551 action_code = 'D',
1552 last_updated_by = G_USER_ID,
1553 last_update_date = SYSDATE,
1554 last_update_login = G_USER_ID
1555 WHERE
1556 tariff_name = p_tariff_name AND
1557 action_code = 'M';
1558
1559 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1560 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Updated ' || sql%rowcount || ' rows in fte_tariff_carriers.');
1561 END IF;
1562
1563 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1564 EXCEPTION
1565 WHEN OTHERS THEN
1566
1567 IF (GET_PROCESS_IDS%ISOPEN) THEN
1568 CLOSE GET_PROCESS_IDS;
1569 END IF;
1570
1571 x_status := 2;
1572 x_error_msg := sqlerrm;
1573 FTE_UTIL_PKG.Write_LogFile(p_module_name => l_module_name,
1574 p_message => sqlerrm);
1575 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1576
1577 END CLEANUP_TABLES;
1578
1579 --___________________________________________________________________________________--
1580 --
1581 -- PROCEDURE: CREATE_RATE_CHART
1582 --
1583 -- PURPOSE Create a rate chart from a string of rate breaks and freight classes
1584 --
1585 -- PARAMETERS
1586 --
1587 -- IN
1588 -- p_carrier_ids: The name of the carriers.
1589 -- p_chart_name: The name of the rate chart.
1590 -- p_break_string: String containing concatenation of rate breaks.
1591 -- p_class_string: String containing concatenation of freight classes.
1592 --
1593 -- OUT
1594 -- x_status: Completion status. Success ==> -1, Failure otherwise.
1595 -- x_error_msg: Error message, if there is an error.
1596 -- x_process_id: The process id associated with the rate chart.
1597 --___________________________________________________________________________________--
1598
1599 PROCEDURE CREATE_RATE_CHART(p_chart_name IN VARCHAR2,
1600 p_carrier_ids IN NUMBER_TAB,
1601 p_break_string IN VARCHAR2,
1602 p_class_string IN VARCHAR2,
1603 x_status OUT NOCOPY VARCHAR2,
1604 x_error_msg OUT NOCOPY VARCHAR2,
1605 x_process_id OUT NOCOPY NUMBER) IS
1606
1607 TYPE t_breakList IS TABLE OF VARCHAR2(6);
1608 TYPE t_break_max_vals IS TABLE OF NUMBER;
1609
1610 v_description VARCHAR2(200) := 'LTL RATE CHART ' || p_chart_name;
1611 v_status NUMBER;
1612
1613 v_class_value VARCHAR2(20);
1614 v_classes STRINGARRAY;
1615
1616 v_break_numchars CONSTANT NUMBER := 6;
1617 v_break_charge VARCHAR2(10);
1618
1619 v_break_charges t_breakList := t_breakList(); -- collection holding the list of prices
1620 v_price_charge_count NUMBER := 0;
1621
1622 v_validate_keys STRINGARRAY;
1623 v_validate_data STRINGARRAY;
1624
1625 v_break_max_vals t_break_max_vals := t_break_max_vals(Fnd_Number.Canonical_To_Number('499.999999999999999'), --15dp
1626 Fnd_Number.Canonical_To_Number('999.999999999999999'),
1627 Fnd_Number.Canonical_To_Number('1999.999999999999999'),
1628 Fnd_Number.Canonical_To_Number('4999.999999999999999'),
1629 Fnd_Number.Canonical_To_Number('9999.999999999999999'),
1630 Fnd_Number.Canonical_To_Number('19999.999999999999999'),
1631 Fnd_Number.Canonical_To_Number('29999.999999999999999'),
1632 Fnd_Number.Canonical_To_Number('39999.999999999999999'),
1633 Fnd_Number.Canonical_To_Number('9999999'));
1634
1635 -- variables for creating commodity classes from class_string
1636 v_class_string VARCHAR2(200) := p_class_string;
1637
1638 -- variables for creating break charges from class_string
1639 v_break_str_len NUMBER := length(p_break_string);
1640 v_break_start_index NUMBER := 1;
1641
1642 -- variables for creating lines from classes and break charges.
1643 v_parent_linenum NUMBER := 1;
1644 v_current_linenum NUMBER := 0;
1645 v_current_break NUMBER := 1; -- current break index in table of break charges
1646 v_break_min NUMBER; -- min charge for the current break
1647 v_break_max NUMBER := -1; -- max charge for the current break
1648
1649 v_numBreaks_perClass CONSTANT NUMBER := 9; -- this is standard for LTL.
1650
1651 l_rate_hdr_data FTE_BULKLOAD_PKG.data_values_tbl;
1652 l_rate_hdr_block_tbl FTE_BULKLOAD_PKG.block_data_tbl;
1653
1654 l_rate_line_data FTE_BULKLOAD_PKG.data_values_tbl;
1655 l_rate_line_block_tbl FTE_BULKLOAD_PKG.block_data_tbl;
1656
1657 l_qualifier_data FTE_BULKLOAD_PKG.data_values_tbl;
1658 l_qualifier_block_tbl FTE_BULKLOAD_PKG.block_data_tbl;
1659
1660 l_attribute_data FTE_BULKLOAD_PKG.data_values_tbl;
1661 l_attribute_block_tbl FTE_BULKLOAD_PKG.block_data_tbl;
1662
1663 l_rate_break_data FTE_BULKLOAD_PKG.data_values_tbl;
1664 l_rate_break_block_tbl FTE_BULKLOAD_PKG.block_data_tbl;
1665
1666 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.CREATE_RATE_CHART';
1667
1668 BEGIN
1669
1670 FTE_UTIL_PKG.Enter_Debug(l_module_name);
1671 x_status := -1;
1672 x_error_msg := 'COMPLETED';
1673
1674 BEGIN
1675 SELECT QP_PROCESS_ID_S.NEXTVAL
1676 INTO x_process_ID
1677 FROM DUAL;
1678 EXCEPTION
1679 WHEN OTHERS THEN
1680 x_status := 2;
1681 x_error_msg := 'Unexpected error while performing select qp_process_id_s.nextval ' || sqlerrm;
1682 FTE_UTIL_PKG.Write_LogFile(l_module_name, x_error_msg);
1683 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1684 END;
1685
1686 FTE_BULKLOAD_PKG.g_load_id := x_process_id;
1687
1688 G_TOTAL_NUMCHARTS := G_TOTAL_NUMCHARTS + 1;
1689
1690 --+
1691 -- Get the classes from the class_string and store in collection v_classes
1692 --+
1693
1694 v_class_string := REPLACE(v_class_string,' '); -- remove white spaces
1695 v_classes := FTE_UTIL_PKG.TOKENIZE_STRING(v_class_string, ',');
1696
1697 --+
1698 -- Get the breaks from the break_string and store in collection v_break_charges
1699 -- The break_string is a concatenation of break charges. Each charge consists of
1700 -- 'v_break_numchars'(6) characters.
1701 --+
1702
1703 BEGIN
1704
1705 WHILE v_break_start_index < v_break_str_len LOOP
1706
1707 v_price_charge_count := v_price_charge_count + 1;
1708 v_break_charge := SUBSTR(p_break_string, v_break_start_index, v_break_numchars);
1709
1710 IF LENGTH(v_break_charge) < v_break_numchars THEN
1711 x_error_msg := 'NOT ENOUGH PRICE BREAKS IN STRING ' || p_break_string;
1712 FTE_UTIL_PKG.Write_LogFile(p_module_name => l_module_name,
1713 p_message => x_error_msg); -- C
1714
1715 x_status := 2;
1716 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1717 RETURN;
1718 END IF;
1719
1720 v_break_start_index := v_break_start_index + v_break_numchars;
1721 v_break_charges.EXTEND;
1722 v_break_charges(v_price_charge_count) := v_break_charge;
1723
1724 END LOOP;
1725 END;
1726
1727 v_price_charge_count := v_break_charges.COUNT;
1728
1729 /* -------------------------------------------------------------------------------------------
1730 This rate chart creation process simulates a simple rate chart with freight classes,
1731 attributes and breaks. An example is shown below:
1732
1733 RATE_CHART
1734 ACTION CARRIER_NAME RATE_CHART_NAME CURRENCY START_DATE END_DATE DESCRIPTION
1735 ADD UPS RATE_CHART_1 USD 30-Jun-01 30-Jun-03 Rate Chart 1
1736
1737 RATE_LINE
1738 ACTION LINE_NUMBER DESCRIPTION RATE UOM RATE_BREAK_TYPE VOLUME_TYPE
1739 ADD 1 Line Lbs POINT TOTAL_QUANTITY
1740
1741 RATE_BREAK
1742 ACTION LINE_NUMBER LOWER_LIMIT UPPER_LIMIT RATE*
1743 ADD 2 0 499 58.28
1744 ADD 3 500 999 49.29
1745 . . . . .
1746 . . . . .
1747 ADD 9 50000 99999 28.41
1748
1749 RATING_ATTRIBUTE
1750 ACTION LINE_NUMBER ATTRIBUTE ATTRIBUTE_VALUE
1751 ADD 1 COMMODITY FC.200.US
1752
1753 RATE_LINE
1754 ACTION LINE_NUMBER DESCRIPTION RATE UOM RATE_BREAK_TYPE VOLUME_TYPE
1755 ADD 10 Line Lbs POINT TOTAL_QUANTITY
1756 . . . . . . .
1757 --------------------------------------------------------------------------------------------*/
1758
1759 -- Validate the header
1760
1761 l_rate_hdr_data('ACTION') := 'ADD';
1762 l_rate_hdr_data('LTL_RATE_CHART_NAME') := p_chart_name;
1763 l_rate_hdr_data('CARRIER_ID') := p_carrier_ids(1);
1764 l_rate_hdr_data('DESCRIPTION') := v_description;
1765 l_rate_hdr_data('CURRENCY') := G_LTL_CURRENCY;
1766 l_rate_hdr_data('ATTRIBUTE1') := 'LTL_RC';
1767 l_rate_hdr_data('START_DATE') := to_char(G_VALID_DATE,FTE_BULKLOAD_PKG.G_DATE_FORMAT);
1768 l_rate_hdr_data('END_DATE') := '';
1769
1770 l_rate_hdr_block_tbl(1) := l_rate_hdr_data;
1771
1772 FTE_RATE_CHART_LOADER.PROCESS_RATE_CHART(p_block_header => g_dummy_block_hdr_tbl,
1773 p_block_data => l_rate_hdr_block_tbl,
1774 p_line_number => 0,
1775 p_validate_column => FALSE,
1776 p_process_id => x_process_id,
1777 x_status => x_status,
1778 x_error_msg => x_error_msg);
1779
1780 IF (x_status <> -1) THEN
1781 -- if the rate chart already exists, delete it, then try creating the rate chart again.
1782 IF (x_status = 999) THEN
1783 -- v_validate_data(1) := 'DELETE';
1784 l_rate_hdr_data('ACTION') := 'DELETE';
1785 l_rate_hdr_block_tbl(1) := l_rate_hdr_data;
1786
1787 FTE_RATE_CHART_LOADER.PROCESS_RATE_CHART(p_block_header => g_dummy_block_hdr_tbl,
1788 p_block_data => l_rate_hdr_block_tbl,
1789 p_line_number => 0,
1790 p_validate_column => FALSE,
1791 p_process_id => x_process_id,
1792 x_status => x_status,
1793 x_error_msg => x_error_msg);
1794 -- v_validate_data(1) := 'ADD';
1795 l_rate_hdr_data('ACTION') := 'ADD';
1796 l_rate_hdr_block_tbl(1) := l_rate_hdr_data;
1797
1798 FTE_RATE_CHART_LOADER.PROCESS_RATE_CHART(p_block_header => g_dummy_block_hdr_tbl,
1799 p_block_data => l_rate_hdr_block_tbl,
1800 p_line_number => 0,
1801 p_validate_column => FALSE,
1802 p_process_id => x_process_id,
1803 x_status => x_status,
1804 x_error_msg => x_error_msg);
1805 ELSE
1806 x_status := 2;
1807 x_error_msg := 'Error in ' || p_chart_name || ': ' || x_error_msg;
1808 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1809 RETURN;
1810 END IF;
1811 END IF;
1812
1813 IF (x_status <> -1) THEN
1814 x_status := 2;
1815 return;
1816 END IF;
1817
1818 -- Validate Lines
1819 BEGIN
1820 --
1821 -- For each class, validate the parent line, and then validate
1822 -- the breaks for that class. We take out the first and last elements
1823 -- of this array since they are blank.
1824 -- e.g. v_class_string is like ,500,400,...,10,
1825 --
1826 FOR v_class_counter IN v_classes.first + 1..v_classes.last - 1 LOOP
1827
1828 l_rate_line_data('ACTION') := 'ADD';
1829 l_rate_line_data('LINE_NUMBER') := v_parent_linenum;
1830 l_rate_line_data('DESCRIPTION') := v_description;
1831 l_rate_line_data('RATE') := '';
1832 l_rate_line_data('UOM') := G_LTL_UOM;
1833 l_rate_line_data('RATE_BREAK_TYPE') := 'POINT';
1834 l_rate_line_data('VOLUME_TYPE') := 'TOTAL_QUANTITY';
1835 l_rate_line_data('RATE_TYPE') := 'PER_UOM';
1836
1837 l_rate_line_block_tbl(1) := l_rate_line_data;
1838
1839 FTE_RATE_CHART_LOADER.PROCESS_RATE_LINE(p_block_header => g_dummy_block_hdr_tbl,
1840 p_block_data => l_rate_line_block_tbl,
1841 p_line_number => 0,
1842 p_validate_column => FALSE,
1843 x_status => x_status,
1844 x_error_msg => x_error_msg);
1845
1846 IF (x_status <> -1) THEN
1847 x_error_msg := 'Line ' || v_parent_linenum || ' of ' || p_chart_name || ': ' || x_error_msg;
1848 x_status := 2;
1849 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1850 RETURN;
1851 END IF;
1852
1853 v_break_max := Fnd_Number.Canonical_To_Number('0');
1854 -- example (break_min,break_max) pairs are (0, 500..), (500, 1000..), ...
1855 v_current_linenum := 0;
1856
1857 FOR i IN 1..v_numBreaks_perClass LOOP
1858 v_break_min := v_break_max + Fnd_Number.Canonical_To_Number('0');
1859 v_break_max := v_break_max_vals(i);
1860 v_break_charge := v_break_charges(v_current_break);
1861
1862 v_break_charge := ltrim(v_break_charge, '0');
1863
1864 IF (LENGTH(v_break_charge) > 0) THEN -- skip if it was all 0's.
1865 v_current_linenum := v_current_linenum + 1;
1866 v_break_charge := Fnd_Number.Number_To_Canonical(Fnd_Number.Canonical_To_Number(v_break_charge)/10000);
1867
1868 l_rate_break_data('ACTION') := 'ADD';
1869 l_rate_break_data('LINE_NUMBER'):= (v_parent_linenum + v_current_linenum);
1870 l_rate_break_data('LOWER_LIMIT'):= Fnd_Number.Number_To_Canonical(v_break_min);
1871 l_rate_break_data('UPPER_LIMIT'):= Fnd_Number.Number_To_Canonical(v_break_max);
1872 l_rate_break_data('RATE') := v_break_charge;
1873
1874 l_rate_break_block_tbl(1) := l_rate_break_data;
1875
1876 FTE_RATE_CHART_LOADER.PROCESS_RATE_BREAK(p_block_header => g_dummy_block_hdr_tbl,
1877 p_block_data => l_rate_break_block_tbl,
1878 p_line_number => 0,
1879 p_validate_column => FALSE,
1880 x_status => x_status,
1881 x_error_msg => x_error_msg);
1882
1883 IF (x_status <> -1) THEN
1884 v_status := v_parent_linenum + v_current_linenum;
1885 x_status := 2;
1886 x_error_msg := 'Line Break ' || v_status || ' of ' || p_chart_name || ': ' || x_error_msg;
1887 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1888 RETURN;
1889 END IF;
1890 END IF;
1891 v_current_break := v_current_break + 1;
1892 END LOOP;
1893
1894 IF (v_current_linenum = 0) THEN
1895 x_error_msg := 'Line ' || v_parent_linenum || ' of ' || p_chart_name || ' has no breaks.';
1896 FTE_UTIL_PKG.Write_LogFile( p_module_name => l_module_name,
1897 p_message => x_error_msg);
1898
1899 x_status := 2;
1900 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1901 return;
1902 END IF;
1903
1904 v_class_value := v_classes(v_class_counter);
1905
1906 l_attribute_data('ACTION') := 'ADD';
1907 l_attribute_data('LINE_NUMBER') := v_parent_linenum;
1908 l_attribute_data('ATTRIBUTE') := 'COMMODITY';
1909 l_attribute_data('ATTRIBUTE_VALUE') := 'FC.'||v_class_value;
1910
1911 l_attribute_block_tbl(1) := l_attribute_data;
1912
1913 FTE_RATE_CHART_LOADER.PROCESS_RATING_ATTRIBUTE( p_block_header => g_dummy_block_hdr_tbl,
1914 p_block_data => l_attribute_block_tbl,
1915 p_line_number => 0,
1916 p_validate_column => FALSE,
1917 x_status => x_status,
1918 x_error_msg => x_error_msg);
1919
1920 IF (x_status <> -1) THEN
1921 x_error_msg := 'Error Validating Attribute FC.' || v_class_value|| ': ' || x_error_msg;
1922 x_status := 2;
1923 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1924 return;
1925 END IF;
1926
1927 v_parent_linenum := v_parent_linenum + v_current_linenum + 1;
1928 END LOOP; -- end looping over classes
1929
1930 -- Add the rest of the carriers as qualifiers.
1931 FOR q IN 2..p_carrier_ids.COUNT LOOP
1932
1933 l_qualifier_data('ACTION') := 'ADD';
1934 l_qualifier_data('PROCESS_ID') := x_process_id;
1935 l_qualifier_data('ATTRIBUTE') := 'SUPPLIER';
1936 l_qualifier_data('VALUE') := p_carrier_ids(q);
1937 l_qualifier_data('CONTEXT') := 'PARTY';
1938 l_qualifier_data('GROUP') := q;
1939
1940 l_qualifier_block_tbl(1) := l_qualifier_data;
1941
1942 FTE_RATE_CHART_LOADER.PROCESS_QUALIFIER(p_block_header => g_dummy_block_hdr_tbl,
1943 p_block_data => l_qualifier_block_tbl,
1944 p_line_number => 0,
1945 x_status => x_status,
1946 x_error_msg => x_error_msg);
1947
1948 IF (x_status <> -1) THEN
1949 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1950 RETURN;
1951 END IF;
1952 END LOOP;
1953
1954 G_CHART_COUNT_TEMP := G_CHART_COUNT_TEMP + 1;
1955
1956 IF (G_CHART_COUNT_TEMP = G_BULK_INSERT_LIMIT) THEN
1957
1958 FTE_RATE_CHART_LOADER.SUBMIT_QP_PROCESS(x_status => x_status,
1959 x_error_msg => x_error_msg);
1960
1961 IF (x_status <> -1) THEN
1962 x_error_msg := 'ERROR INSERTING DATA into QP_INTERFACE TABLES: ' || x_error_msg;
1963 x_status := 2;
1964 FTE_UTIL_PKG.Write_LogFile(l_module_name, x_error_msg);
1965 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1966 RETURN;
1967 END IF;
1968 G_CHART_COUNT_TEMP := 0;
1969 END IF;
1970 END;
1971
1972 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1973
1974 EXCEPTION
1975 WHEN OTHERS THEN
1976 x_status := 2;
1977 x_error_msg := sqlerrm;
1978 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1979 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR',sqlerrm);
1980 END CREATE_RATE_CHART;
1981
1982 --___________________________________________________________________________________--
1983 --
1984 -- PROCEDURE: CREATE_LANE_DATA
1985 --
1986 -- PURPOSE to create the lane data,
1987 --
1988 -- PARAMETERS
1989 -- p_origin_id
1990 -- p_destination_id
1991 -- p_carriers_id
1992 -- p_effective_Dates
1993 -- p_expiry_dates
1994 -- p_tariff_names
1995 -- p_lane_type
1996 -- p_category_ids
1997 -- p_list_header_id
1998 -- p_min_charge
1999 --
2000 -- OUT
2001 -- x_status, the return status of the procedure,
2002 -- -1, success
2003 -- any other non negative value indicates failure.
2004 -- x_error_msg, the error message indicating the cause and detailing the error occured.
2005 --___________________________________________________________________________________--
2006
2007 PROCEDURE CREATE_LANE_DATA (p_origin_id IN NUMBER,
2008 p_destination_id IN NUMBER,
2009 p_carrier_ids IN NUMBER_TAB,
2010 p_effective_dates IN STRINGARRAY,
2011 p_expiry_dates IN STRINGARRAY,
2012 p_tariff_name IN VARCHAR2,
2013 p_lane_type IN VARCHAR2,
2014 p_category_ids IN NUMBER_TAB,
2015 p_list_header_id IN NUMBER,
2016 p_min_charge IN NUMBER,
2017 x_status OUT NOCOPY NUMBER,
2018 x_error_msg OUT NOCOPY VARCHAR2) IS
2019
2020 l_fc_value VARCHAR2(10);
2021 l_category_ids NUMBER_TAB;
2022 l_catg_id NUMBER;
2023 l_lane_id NUMBER;
2024 l_deficit_wt_breaks STRINGARRAY;
2025 l_num_lanes NUMBER;
2026
2027 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.CREATE_LANE_DATA';
2028
2029 BEGIN
2030
2031 FTE_UTIL_PKG.Enter_Debug(l_module_name);
2032 x_status := -1;
2033
2034 l_catg_id := p_category_ids(1);
2035
2036 IF ( FTE_BULKLOAD_PKG.g_debug_on ) THEN
2037 FTE_UTIL_PKG.Write_LogFile(l_module_name,'p_origin_id ',p_origin_id);
2038 FTE_UTIL_PKG.Write_LogFile(l_module_name,'p_destination_id ',p_destination_id );
2039 FTE_UTIL_PKG.Write_LogFile(l_module_name,'p_tariff_name ',p_tariff_name);
2040 FTE_UTIL_PKG.Write_LogFile(l_module_name,'p_list_header_id ',p_list_header_id);
2041 FTE_UTIL_PKG.Write_LogFile(l_module_name,'p_lane_type ',p_tariff_name);
2042 END IF;
2043
2044 --+
2045 -- For each carrier, create a lane and its associated entities
2046 --+
2047 FOR k IN 1..p_carrier_ids.COUNT LOOP
2048
2049 SELECT fte_lanes_s.NEXTVAL INTO l_lane_id FROM DUAL;
2050
2051 -- FTE_LANE_COMMODITIES
2052 FOR m IN 1..p_category_ids.COUNT LOOP
2053 CM_LANE_ID(cm_lane_id.COUNT+1) := l_lane_id;
2054 CM_CATG_ID(cm_catg_id.COUNT+1) := p_category_ids(m);
2055 END LOOP;
2056
2057 --+
2058 -- If we have more than one commodity on the lane, we need to set the commodity
2059 -- in FTE_LANES to null and store everything in FTE_LANE_COMMODITIES.
2060 --+
2061 IF p_category_ids.COUNT > 1 THEN
2062 l_catg_id := NULL;
2063 END IF;
2064
2065 -- FTE_LANES
2066 LN_LANE_ID(ln_lane_id.COUNT+1) := l_lane_id;
2067 LN_CARRIER_ID(ln_carrier_id.COUNT+1):= p_carrier_ids(k);
2068 LN_START_DATE(ln_start_date.COUNT+1):= p_effective_dates(k);
2069 LN_END_DATE(ln_end_date.COUNT+1) := p_expiry_dates(k);
2070
2071 --+
2072 -- Switch back the origin and destination if we are dealing with inbound.
2073 --+
2074 IF (G_DIRECTION_FLAG = 'I') THEN
2075 LN_DEST_ID(ln_dest_id.COUNT+1) := p_origin_id;
2076 LN_ORIGIN_ID(ln_origin_id.COUNT+1) := p_destination_id;
2077 ELSIF (G_DIRECTION_FLAG = 'O' OR G_DIRECTION_FLAG IS NULL) THEN
2078 LN_ORIGIN_ID(ln_origin_id.COUNT+1) := p_origin_id;
2079 LN_DEST_ID(ln_dest_id.COUNT+1) := p_destination_id;
2080 END IF;
2081
2082 LN_COMMODITY_CATG_ID(ln_commodity_catg_id.COUNT+1) := l_catg_id;
2083 LN_COMM_FC_CLASS_CODE(ln_comm_fc_class_code.COUNT+1) := 'FC';
2084 LN_LANE_TYPE(ln_lane_type.COUNT+1) := p_lane_type;
2085 LN_TARIFF_NAME(ln_tariff_name.COUNT+1) := p_tariff_name;
2086
2087 -- FTE_LANE_RATE_CHARTS
2088 LRC_LANE_ID(lrc_lane_id.COUNT+1) := l_lane_id;
2089 LRC_LIST_HEADER_ID(lrc_list_header_id.COUNT+1) := p_list_header_id;
2090 LRC_START_DATE(lrc_start_date.COUNT+1) := G_VALID_DATE;
2091 LRC_END_DATE(lrc_end_date.COUNT+1) := NULL;
2092
2093 -- FTE_PRC_PARAMETERS
2094
2095 -- Deficit Weight Breaks
2096 l_deficit_wt_breaks := STRINGARRAY('0', '500', '1000', '2000', '5000', '10000', '20000', '30000', '40000');
2097
2098 FOR n IN 1..l_deficit_wt_breaks.COUNT LOOP
2099 PRC_VALUE_FROM(prc_value_from.COUNT+1) := l_deficit_wt_breaks(n);
2100 PRC_PARAMETER_ID(prc_parameter_id.COUNT+1) := G_DEF_WT_BREAK_ID;
2101 PRC_LANE_ID(prc_lane_id.COUNT+1) := l_lane_id;
2102 END LOOP;
2103
2104 -- Enable Deficit Weights
2105 PRC_VALUE_FROM(prc_value_from.COUNT+1) := 'Y';
2106 PRC_PARAMETER_ID(prc_parameter_id.COUNT+1) := G_DEF_WT_ENABLED_ID ;
2107 PRC_LANE_ID(prc_lane_id.COUNT+1) := l_lane_id;
2108
2109 -- Lane FunctionID
2110 PRC_VALUE_FROM(prc_value_from.COUNT+1) := 'LTL';
2111 PRC_PARAMETER_ID(prc_parameter_id.COUNT+1) := G_LANE_FUNCTION_ID;
2112 PRC_LANE_ID(prc_lane_id.COUNT+1) := l_lane_id;
2113
2114 -- Minimum charge
2115 PRC_VALUE_FROM(prc_value_from.COUNT+1) := p_min_charge;
2116 PRC_PARAMETER_ID(prc_parameter_id.COUNT+1) := G_MIN_CHARGE_ID;
2117 PRC_LANE_ID(prc_lane_id.COUNT+1) := l_lane_id;
2118
2119 IF (LN_LANE_ID.COUNT >= G_BULK_INSERT_LIMIT) THEN
2120 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Bulk inserting Lanes');
2121 Bulk_Insert_Lanes;
2122 Bulk_Insert_Lane_Rate_Charts;
2123 END IF;
2124
2125 IF (PRC_LANE_ID.COUNT >= G_BULK_INSERT_LIMIT) THEN
2126 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Bulk inserting Lane Parameters');
2127 Bulk_Insert_Lane_Parameters;
2128 END IF;
2129
2130 IF (CM_LANE_ID.COUNT >= G_BULK_INSERT_LIMIT) THEN
2131 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Bulk inserting Lane Commodities');
2132 Bulk_Insert_Lane_Commodities;
2133 END IF;
2134
2135 END LOOP;
2136
2137 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2138
2139 EXCEPTION
2140
2141 WHEN OTHERS THEN
2142 x_status := 2;
2143 x_error_msg := SQLERRM;
2144 FTE_UTIL_PKG.Write_LogFile(l_module_name,'UNEXPECTED ERROR', SQLERRM);
2145 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2146
2147 END CREATE_LANE_DATA;
2148
2149 --_________________________________________________________________________________--
2150 --
2151 -- PROCEDURE:CREATE_LANES
2152 --
2153 -- PURPOSE To create lanes. Actually, delegates the lane data creation to CREATE_LANE_DATA-
2154 -- Use the WSH API to create the regions and
2155 -- zones in the WSH tables.
2156 -- PARAMETERS
2157 -- IN
2158 -- p_load_id
2159 -- p_tariff_name
2160 -- p_carrier_ids
2161 -- p_effective_dates
2162 -- p_expiry_dates
2163 --
2164 -- OUT
2165 -- x_status, the return status of the procedure,
2166 -- -1, success
2167 -- any other non negative value indicates failure.
2168 -- x_error_msg, the error message indicating the cause and detailing the error occured.-
2169 --_________________________________________________________________________________--
2170
2171 PROCEDURE CREATE_LANES (p_load_id IN NUMBER,
2172 p_tariff_name IN VARCHAR2,
2173 p_carrier_ids IN NUMBER_TAB,
2174 p_effective_dates IN STRINGARRAY,
2175 p_expiry_dates IN STRINGARRAY,
2176 x_status OUT NOCOPY VARCHAR2,
2177 x_error_msg OUT NOCOPY VARCHAR2) IS
2178
2179
2180 l_fc_class_code VARCHAR2(2);
2181 l_lane_commodities STRINGARRAY;
2182 l_lanes_temp lanes_temp_record;
2183 l_zones_temp zones_temp_record;
2184 l_list_header_id NUMBER;
2185 c_current_fetch NUMBER;
2186 c_previous_fetch NUMBER;
2187 k_counter NUMBER;
2188 l_catg_id NUMBER;
2189 l_category_ids Number_Tab;
2190 l_number_of_loads NUMBER;
2191 l_previous_name VARCHAR2(40);
2192
2193 --+
2194 -- This cursor queries the RateChart Name and the String form the FTE_INTERFACE_LANES
2195 --+
2196 CURSOR GET_LANES(p_load_id NUMBER) IS
2197 SELECT
2198 origin_id,
2199 dest_id,
2200 rate_chart_name,
2201 class_string,
2202 min_charge1
2203 FROM
2204 fte_interface_lanes
2205 WHERE
2206 load_id = p_load_id;
2207
2208 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.CREATE_LANES';
2209
2210 BEGIN
2211 FTE_UTIL_PKG.Enter_Debug(l_module_name);
2212 x_status := -1;
2213
2214 l_previous_name := 'NULL';
2215 c_previous_fetch := 0;
2216
2217 OPEN GET_LANES(p_load_id);
2218 LOOP
2219 FETCH GET_LANES BULK COLLECT INTO l_lanes_temp.origin_id,
2220 l_lanes_temp.dest_id,
2221 l_lanes_temp.rate_chart_name,
2222 l_lanes_temp.class,
2223 l_lanes_temp.min_charge1 LIMIT 1000;
2224
2225 FOR i IN 1..l_lanes_temp.origin_id.COUNT LOOP
2226
2227 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
2228 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_lanes_temp.rate_chart_name('||i||')',l_lanes_temp.rate_chart_name(i));
2229 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_lanes_temp.origin_id('||i||')',l_lanes_temp.origin_id(i));
2230 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_lanes_temp.dest_id('||i||')',l_lanes_temp.dest_id(i));
2231 END IF;
2232
2233 BEGIN
2234 SELECT
2235 l.list_header_id
2236 INTO
2237 l_list_header_id
2238 FROM
2239 qp_list_headers_tl l,
2240 qp_list_headers_b b
2241 WHERE
2242 l.list_header_id = b.list_header_id AND
2243 l.name = l_lanes_temp.rate_chart_name(i) AND
2244 l.language = userenv('LANG');
2245
2246 EXCEPTION
2247
2248 WHEN NO_DATA_FOUND THEN
2249 x_status := 2;
2250 x_error_msg := 'Rate Chart ' || l_lanes_temp.rate_chart_name(i) || ' NOT FOUND!';
2251 FTE_UTIL_PKG.Write_LogFile(l_module_name, x_error_msg);
2252 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2253 RETURN;
2254
2255 WHEN OTHERS THEN
2256 x_status := 2;
2257 x_error_msg := sqlerrm;
2258 FTE_UTIL_PKG.Write_LogFile(l_module_name, sqlerrm);
2259 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2260 RETURN;
2261 END;
2262
2263 l_lane_commodities := FTE_UTIL_PKG.TOKENIZE_STRING(l_lanes_temp.class(i),',');
2264 l_category_ids.DELETE;
2265
2266 IF (l_lane_commodities.COUNT <= 2) THEN
2267 x_status := 2;
2268 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_COMMODITY_MISSING');
2269 FTE_UTIL_PKG.Write_OutFile( p_msg => x_error_msg,
2270 p_module_name => l_module_name,
2271 p_category => 'C',
2272 p_line_number => 0);
2273 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2274 RETURN;
2275 END IF;
2276
2277 FOR j IN 2..l_lane_commodities.COUNT-1 LOOP
2278
2279 FTE_UTIL_PKG.GET_CATEGORY_ID(p_commodity_value => 'FC.'||l_lane_commodities(j),
2280 x_catg_id => l_catg_id,
2281 x_class_code => l_fc_class_code,
2282 x_status => x_status,
2283 x_error_msg => x_error_msg);
2284
2285 IF (l_catg_id IS NULL OR x_status <> -1) THEN
2286 x_status := 2;
2287 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2288 RETURN;
2289 ELSE
2290 l_category_ids(j-1) := l_catg_id;
2291 END IF;
2292
2293 END LOOP;
2294
2295 CREATE_LANE_DATA(p_origin_id => l_lanes_temp.origin_id(i),
2296 p_destination_id => l_lanes_temp.dest_id(i),
2297 p_carrier_ids => p_carrier_ids,
2298 p_effective_dates => p_effective_dates,
2299 p_expiry_dates => p_expiry_dates,
2300 p_tariff_name => p_tariff_name,
2301 p_lane_type => 'LTL_' || p_tariff_name || '_' || l_number_of_loads,
2302 p_category_ids => l_category_ids,
2303 p_list_header_id => l_list_header_id,
2304 p_min_charge => l_lanes_temp.min_charge1(i),
2305 x_status => x_status,
2306 x_error_msg => x_error_msg);
2307 END LOOP;
2308
2309 EXIT WHEN (GET_LANES%NOTFOUND);
2310
2311 END LOOP;
2312
2313 CLOSE GET_LANES;
2314
2315 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
2316 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Bulk Inserting Last Set of Data');
2317 END IF;
2318
2319 Bulk_Insert_Lanes;
2320 Bulk_Insert_Lane_Rate_Charts;
2321 Bulk_Insert_Lane_Parameters;
2322 Bulk_Insert_Lane_Commodities;
2323
2324 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2325
2326 EXCEPTION
2327 WHEN OTHERS THEN
2328
2329 IF (GET_LANES%ISOPEN) THEN
2330 CLOSE GET_LANES;
2331 END IF;
2332
2333 x_status := 2;
2334 x_error_msg := SQLERRM;
2335 FTE_UTIL_PKG.Write_LogFile(l_module_name,'UNEXCEPTED ERROR ',sqlerrm);
2336 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2337
2338 END CREATE_LANES;
2339
2340 --____________________________________________________________________________________--
2341 --
2342 -- PROCEDURE: FIND_IDENTICAL_ZONE
2343 --
2344 -- PURPOSE: Given a reference zone name and a set of zone names, find out if
2345 -- any of the zones in the set is identical to the reference zone.
2346 -- If an identical zone is found, the out parameters x_identical_zone
2347 -- and x_zone_id are the zone_name and zone_id of the matching zone.
2348 --
2349 -- PARAMETERS
2350 -- IN
2351 -- p_zone_name The reference zone name
2352 -- p_matching_zones The set of zones to be checked.
2353 -- OUT
2354 -- x_identical_zone OUT NOCOPY VARCHAR2:
2355 -- x_zone_id OUT NOCOPY NUMBER:
2356 -- x_status, the return status of the procedure,
2357 -- -1, success
2358 -- any other non negative value indicates failure.
2359 -- x_error_msg, the error message indicating the cause and detailing the error occured.
2360 --____________________________________________________________________________________--
2361
2362 PROCEDURE FIND_IDENTICAL_ZONE(p_zone_name IN VARCHAR2,
2363 p_matching_zones IN STRINGARRAY,
2364 p_origin_flag IN BOOLEAN,
2365 x_identical_zone OUT NOCOPY VARCHAR2,
2366 x_zone_id OUT NOCOPY NUMBER,
2367 x_status OUT NOCOPY VARCHAR2,
2368 x_error_msg OUT NOCOPY VARCHAR2) IS
2369
2370 j NUMBER;
2371 l_row_numbers NUMBER_TAB;
2372 l_zone_ids NUMBER_TAB;
2373 l_zone_names STRINGARRAY;
2374 l_postal_strings var_arr4000;
2375 l_mismatch BOOLEAN;
2376 l_country_codes STRINGARRAY;
2377 l_new_country_code VARCHAR2(10);
2378
2379 CURSOR GET_ZONE_DETAILS( p_zone1 IN VARCHAR2, p_zone2 IN VARCHAR2) IS
2380 SELECT
2381 zone_name,
2382 postal_code_string,
2383 row_number,
2384 zone_id
2385 FROM
2386 fte_interface_zones
2387 WHERE
2388 zone_name IN (p_zone1, p_zone2) AND
2389 hash_value <> 0
2390 ORDER BY
2391 row_number;
2392
2393 CURSOR GET_ZONE_COUNTRY(p_zone_name IN VARCHAR2) IS
2394 SELECT
2395 w2.country_code
2396 FROM
2397 wsh_zone_regions w,
2398 fte_interface_zones f,
2399 wsh_regions w2
2400 WHERE
2401 w.parent_region_id = f.zone_id AND
2402 f.zone_name = p_zone_name AND
2403 w.region_id = w2.region_id;
2404
2405 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.FIND_IDENTICAL_ZONE';
2406
2407 BEGIN
2408
2409 FTE_UTIL_PKG.Enter_Debug(l_module_name);
2410 x_status := -1;
2411
2412 x_identical_zone := NULL;
2413 x_zone_id := NULL;
2414
2415 IF FTE_BULKLOAD_PKG.g_debug_on THEN
2416 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_zone_name ', p_zone_name);
2417 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_matching_zones.COUNT', p_matching_zones.COUNT);
2418 END IF;
2419
2420 FOR i IN 1..p_matching_zones.COUNT LOOP
2421
2422 OPEN GET_ZONE_DETAILS(p_zone_name, p_matching_zones(i));
2423
2424 FETCH GET_ZONE_DETAILS
2425 BULK COLLECT INTO
2426 l_zone_names,
2427 l_postal_strings,
2428 l_row_numbers,
2429 l_zone_ids;
2430
2431 CLOSE GET_ZONE_DETAILS;
2432
2433 j := 1;
2434 l_mismatch := FALSE;
2435
2436 WHILE (j <= l_postal_strings.COUNT-1) LOOP
2437 IF (l_postal_strings(j) <> l_postal_strings(j+1)) THEN
2438 -- we've found a mismatch
2439 l_mismatch := true;
2440 EXIT;
2441 END IF;
2442 j := j + 2;
2443 END LOOP;
2444 --+
2445 -- If we looped through everything, then all the postal
2446 -- code strings must match for the two zones.
2447 -- now check country
2448 --+
2449 IF (NOT l_mismatch AND j > l_postal_strings.COUNT AND j >= 2) THEN
2450
2451 IF (p_origin_flag) THEN
2452 l_new_country_code := G_ORIG_COUNTRY;
2453 ELSE
2454 l_new_country_code := G_DEST_COUNTRY;
2455 END IF;
2456
2457 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
2458 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_new_country_code', l_new_country_code);
2459 END IF;
2460
2461 OPEN GET_ZONE_COUNTRY(p_matching_zones(i));
2462
2463 FETCH GET_ZONE_COUNTRY
2464 BULK COLLECT INTO l_country_codes;
2465
2466 CLOSE GET_ZONE_COUNTRY;
2467
2468 --+
2469 -- verify new zone's country is in the matching zone's country list
2470 --+
2471 l_mismatch := TRUE;
2472
2473 FOR k IN 1..l_country_codes.COUNT LOOP
2474
2475 IF ( FTE_BULKLOAD_PKG.g_debug_on) THEN
2476 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_country_codes(' || k || ')', l_country_codes(k));
2477 END IF;
2478
2479 IF (l_new_country_code = l_country_codes(k)) THEN -- if found a country match, then can use this zone
2480 l_mismatch := FALSE;
2481 END IF;
2482
2483 END LOOP;
2484 END IF;
2485
2486 IF (NOT l_mismatch AND j > l_postal_strings.COUNT AND j >= 2) THEN
2487
2488 IF (l_zone_ids(1) IS NOT NULL) THEN
2489 x_zone_id := l_zone_ids(1);
2490 x_identical_zone := l_zone_names(1);
2491 ELSE
2492 x_zone_id := l_zone_ids(2);
2493 x_identical_zone := l_zone_names(2);
2494 END IF;
2495
2496 --+
2497 -- this shouldn't happen, but life is sometimes unfair...
2498 --+
2499 IF (x_zone_id IS NULL) THEN
2500 x_identical_zone := NULL;
2501 END IF;
2502
2503 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2504 RETURN;
2505
2506 END IF;
2507
2508 END LOOP; --END looping through matching zones
2509
2510 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2511
2512 EXCEPTION
2513 WHEN OTHERS THEN
2514
2515 IF(GET_ZONE_COUNTRY%ISOPEN) THEN
2516 CLOSE GET_ZONE_COUNTRY;
2517 END IF;
2518 x_status := 2;
2519 x_error_msg := SQLERRM;
2520 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR',sqlerrm);
2521 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2522
2523 END FIND_IDENTICAL_ZONE;
2524
2525
2526 --__________________________________________________________________________________--
2527 --
2528 -- PROCEDURE: LOAD_RATE_CHARTS
2529 --
2530 -- PURPOSE
2531 -- Analyze the data in FTE_BULKLOAD_FILE:
2532 -- a. Identify the destinations sharing the same rate chart and group
2533 -- them in the same zone. Put this information in FTE_INTERFACE_ZONES.
2534 -- b. Create a rate chart for each zone. Put this chart information
2535 -- into the QP_INTERFACE tables and later into the QP tables.
2536 -- PARAMETERS
2537 -- IN
2538 -- p_load_id
2539 -- p_number_of_loads
2540 -- OUT
2541 -- x_status, the return status of the procedure,
2542 -- -1, success
2543 -- any other non negative value indicates failure.
2544 -- x_error_msg, the error message indicating the cause and detailing the error occured.
2545 --__________________________________________________________________________________--
2546
2547 PROCEDURE LOAD_RATE_CHARTS (p_load_id IN NUMBER,
2548 p_number_of_loads IN NUMBER,
2549 p_origin IN VARCHAR2,
2550 p_origin_name IN VARCHAR2,
2551 x_status OUT NOCOPY NUMBER,
2552 x_error_msg OUT NOCOPY VARCHAR2) IS
2553
2554 l_counter NUMBER;
2555 l_debug_on NUMBER;
2556 l_conc_request_data VARCHAR2(1000);
2557 p_retcode VARCHAR2(100);
2558 x_request_ids NUMBER_TAB;
2559
2560 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.LOAD_RATE_CHARTS';
2561
2562 BEGIN
2563
2564 FTE_UTIL_PKG.Enter_Debug(l_module_name);
2565
2566 p_retcode := '0';
2567 x_status := -1;
2568
2569 IF(FTE_BULKLOAD_PKG.g_debug_on) THEN
2570 l_debug_on := 1;
2571 END IF;
2572
2573 --
2574 -- load the rate charts
2575 --
2576 FOR l_counter in 1..G_NUM_CONC_PROCESSES LOOP
2577
2578 x_request_ids(l_counter) := FND_REQUEST.SUBMIT_REQUEST(application => 'FTE',
2579 program => 'FTE_RC_LOADER',
2580 description => null,
2581 start_time => null,
2582 sub_request => true,
2583 argument1 => p_load_id,
2584 argument2 => l_counter,
2585 argument3 => l_debug_on);
2586
2587 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
2588 FTE_UTIL_PKG.Write_LogFile(l_module_name,'Submitted Sub-request with ID => ' || x_request_ids(l_counter));
2589 FTE_UTIL_PKG.Write_LogFile(l_module_name,'p_load_id ',p_load_id);
2590 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_counter ',l_counter);
2591 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_debug_on',l_debug_on);
2592 END IF;
2593
2594 IF (x_request_ids(l_counter) = 0 ) THEN
2595 x_error_msg := FND_MESSAGE.get;
2596 x_error_msg := 'Error submitting concurrent request: ' || x_error_msg;
2597 x_error_msg := substr(x_error_msg, 0, 300);
2598 x_status := 2;
2599 FTE_UTIL_PKG.Write_LogFile(l_module_name,x_error_msg);
2600 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2601 RETURN;
2602 END IF;
2603 END LOOP;
2604
2605 IF (x_status <> 2) THEN
2606 l_conc_request_data := G_VALID_DATE_STRING ||','|| Fnd_Number.Number_To_Canonical(p_number_of_loads) ||','||
2607 p_origin ||','|| p_origin_name ||','||
2608 l_debug_on ||','|| G_DIRECTION_FLAG ||',' ||
2609 G_REPORT_HEADER.STARTDATE;
2610
2611 FND_CONC_GLOBAL.SET_REQ_GLOBALS(conc_status => 'PAUSED',
2612 request_data => l_conc_request_data);
2613
2614 x_error_msg := 'SUB-REQUEST SUBMITTED';
2615 p_retcode := 0;
2616 END IF;
2617
2618 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2619
2620 EXCEPTION
2621 WHEN OTHERS THEN
2622 x_status := 2;
2623 x_error_msg := sqlerrm;
2624 FTE_UTIL_PKG.Write_LogFile(l_module_name,'UNEXCEPTED ERROR',sqlerrm);
2625 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2626
2627 END LOAD_RATE_CHARTS;
2628
2629 --__________________________________________________________________________________________--
2630 --
2631 -- PROCEDURE: BUILD_ZONES_AND_CHARTS
2632 --
2633 -- PURPOSE
2634 --
2635 -- Analyze the data in FTE_BULKLOAD_FILE:
2636 -- a. Identify the destinations sharing the same rate chart and group
2637 -- them in the same zone. Put this information in FTE_INTERFACE_ZONES.
2638 -- b. Create a rate chart for each zone. Put this chart information
2639 -- into the QP_INTERFACE tables and later into the QP tables.
2640 -- PARAMETERS
2641 -- IN p_load_id,
2642 -- p_tariff_name
2643 -- p_carrier_ids
2644 --
2645 -- OUT
2646 -- x_origin , x_origin_name
2647 -- x_status, the return status of the procedure,
2648 -- -1, success
2649 -- any other non negative value indicates failure.
2650 -- x_error_msg, the error message indicating the cause and detailing the error occured.
2651 --__________________________________________________________________________________________--
2652
2653 PROCEDURE BUILD_ZONES_AND_CHARTS(p_load_id IN NUMBER,
2654 p_tariff_name IN VARCHAR2,
2655 p_carrier_ids IN NUMBER_TAB,
2656 x_origin OUT NOCOPY VARCHAR2,
2657 x_origin_name OUT NOCOPY VARCHAR2,
2658 x_number_of_loads OUT NOCOPY NUMBER,
2659 x_status OUT NOCOPY NUMBER,
2660 x_error_msg OUT NOCOPY VARCHAR2) IS
2661
2662 l_origin VARCHAR2(15);
2663 l_origin_high VARCHAR2(15);
2664 l_origin_name VARCHAR2(125);
2665 l_previous_dest_low VARCHAR2(10);
2666 l_previous_dest_high VARCHAR2(10);
2667 l_number_of_loads NUMBER := 1;
2668 l_number_of_charts NUMBER;
2669 l_number_of_zones NUMBER;
2670 l_total_string VARCHAR2(4000);
2671 l_class_string VARCHAR2(200);
2672 l_lanes_temp LANES_TEMP_RECORD;
2673 l_process_id NUMBER;
2674 l_group_id NUMBER := 0;
2675 g_hash_base NUMBER := 1;
2676 g_hash_size NUMBER := power(2, 25);
2677 l_need_to_fetch BOOLEAN;
2678 l_class VARCHAR2(10);
2679 l_min_charge1 VARCHAR2(10);
2680 l_previous_name VARCHAR2(40);
2681 l_zone_name VARCHAR2(125);
2682 l_rate_name VARCHAR2(125);
2683 l_dest_low VARCHAR2(15);
2684 l_dest_high VARCHAR2(15);
2685 l_dest_names STRINGARRAY;
2686 l_rate_names STRINGARRAY;
2687 l_min_charges STRINGARRAY;
2688 l_hash_value NUMBER;
2689 l_min_charge_match BOOLEAN := FALSE;
2690 l_rate_chart_match VARCHAR2(1) := 'N';
2691 l_counter NUMBER;
2692 c_current_fetch NUMBER;
2693 c_previous_fetch NUMBER;
2694 l_last_block BOOLEAN;
2695 l_row_number NUMBER;
2696 j NUMBER;
2697
2698 CURSOR GET_LOAD_NUMBER(p_tariff_name IN VARCHAR2) IS
2699 SELECT
2700 substr(lane_type,instr(lane_type, '_', -1 ) + 1)
2701 FROM
2702 fte_lanes
2703 WHERE
2704 tariff_name = p_tariff_name
2705 ORDER BY creation_date desc;
2706
2707 --+
2708 -- Takes care of the number of available origins
2709 --+
2710 CURSOR NEW_ORIGIN(p_load_id NUMBER) IS
2711 SELECT DISTINCT
2712 origin_low,
2713 origin_high
2714 FROM
2715 fte_bulkload_file
2716 WHERE
2717 load_id = p_load_id;
2718
2719 --+
2720 -- Queries up an entire block that has to be used for creating the string
2721 -- Inputs: p_load_id, p_origin_low
2722 --+
2723 CURSOR CREATE_STRING(p_load_id NUMBER, p_origin_low VARCHAR2) IS
2724 SELECT
2725 lpad(nvl(l5c,0),6,'0') ||lpad(nvl(m5c,0),6,'0') ||
2726 lpad(nvl(m1m,0),6,'0') ||lpad(nvl(m2m,0),6,'0') ||
2727 lpad(nvl(m5m,0),6,'0') ||lpad(nvl(m10m,0),6,'0')||
2728 lpad(nvl(m20m,0),6,'0') ||lpad(nvl(m30m,0),6,'0')||
2729 lpad(nvl(m40m,0),6,'0'),
2730 dest_low,
2731 dest_high,
2732 class,
2733 lpad(min_charge1,6,'0')
2734 FROM
2735 fte_bulkload_file
2736 WHERE
2737 load_id = p_load_id AND
2738 origin_low = p_origin_low
2739 ORDER BY dest_low, dest_high, TO_NUMBER(class) desc;
2740
2741 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.BUILD_ZONES_AND_CHARTS';
2742
2743 BEGIN
2744
2745 FTE_UTIL_PKG.Enter_Debug(l_module_name);
2746 x_status := -1;
2747
2748 l_total_string := null;
2749 l_class_string := null;
2750
2751 l_last_block := false;
2752 l_need_to_fetch := false;
2753
2754 --+
2755 -- Get the load number for the previous load
2756 -- The LANE_TYPE of FTE_LANES has the format LTL_<p_tariff_name>_numberOfLoad.
2757 --+
2758 OPEN GET_LOAD_NUMBER(p_tariff_name);
2759
2760 FETCH GET_LOAD_NUMBER INTO l_number_of_loads;
2761
2762 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
2763 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_number_of_loads',l_number_of_loads);
2764 END IF;
2765
2766 IF (GET_LOAD_NUMBER%FOUND) THEN
2767 l_number_of_loads := l_number_of_loads + 1;
2768 END IF;
2769
2770 CLOSE GET_LOAD_NUMBER;
2771
2772 --+
2773 -- This Loop is to make sure that one only one origin or destination is supported
2774 -- Think about the NEW_ORIGIN % ROWCOUNT
2775 --+
2776 OPEN NEW_ORIGIN(p_load_id);
2777 LOOP
2778 FETCH NEW_ORIGIN into l_origin, l_origin_high;
2779
2780 l_number_of_charts := 1;
2781 l_number_of_zones := 1;
2782 l_origin_name := l_origin || '-' || l_origin_high || '-' || g_orig_country;
2783
2784 --+
2785 -- Multiple Origins not supported
2786 --+
2787 IF ( NEW_ORIGIN % ROWCOUNT > 1) THEN
2788 x_status := 2;
2789 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_MULTI_ORG_DEST_NO_SUPPORT');
2790 FTE_UTIL_PKG.Write_OutFile(p_msg => x_error_msg,
2791 p_module_name => l_module_name,
2792 p_category => 'D',
2793 p_line_number => 0);
2794 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2795 RETURN;
2796 END IF;
2797
2798 EXIT WHEN (NEW_ORIGIN%NOTFOUND);
2799
2800 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
2801 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'G_IN_OUT ', G_IN_OUT);
2802 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'G_ORIGIN_DEST', G_ORIGIN_DEST);
2803 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_origin ', l_origin);
2804 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_origin_high', l_origin_high);
2805 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_load_id ', p_load_id);
2806 END IF;
2807
2808 --+
2809 -- Insert a new origin into FTE_INTERFACE_ZONES
2810 --+
2811 BEGIN
2812 INSERT INTO fte_interface_zones(ZONE_NAME,
2813 POSTAL_CODE_FROM,
2814 POSTAL_CODE_TO,
2815 POSTAL_CODE_STRING,
2816 LOAD_ID,
2817 HASH_VALUE,
2818 ZONE_ID,
2819 ROW_NUMBER)
2820 VALUES (l_origin_name,
2821 l_origin,
2822 l_origin_high,
2823 '',
2824 p_load_id,
2825 0,
2826 null,
2827 1);
2828 EXCEPTION
2829 WHEN OTHERS THEN
2830 x_status := 2;
2831 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXCEPTED ERROR while inserting into FTE_INTERFACE_ZONES',sqlerrm);
2832 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2833 RETURN;
2834 END ;
2835
2836 l_previous_dest_low := null;
2837 l_previous_dest_high := null;
2838 c_current_fetch := 0;
2839 c_previous_fetch := 0;
2840 l_counter := 1;
2841
2842 OPEN CREATE_STRING(p_load_id, l_origin);
2843 LOOP
2844 l_counter := 1;
2845 FETCH CREATE_STRING BULK COLLECT INTO l_lanes_temp.rate_chart_string,
2846 l_lanes_temp.dest_low,
2847 l_lanes_temp.dest_high,
2848 l_lanes_temp.class,
2849 l_lanes_temp.min_charge1 LIMIT 1000;
2850
2851 c_current_fetch := CREATE_STRING % ROWCOUNT - c_previous_fetch;
2852
2853 EXIT WHEN (c_current_fetch <= 0);
2854
2855 WHILE(l_counter <= c_current_fetch)
2856 LOOP
2857 --+
2858 -- Re-initialize those variables only if we have to build a new string;
2859 -- only if dest changes
2860 --+
2861 IF (not l_need_to_fetch) THEN
2862 l_total_string := '';
2863 l_class_string := '';
2864 l_previous_dest_low := l_lanes_temp.dest_low(l_counter);
2865 l_previous_dest_high := l_lanes_temp.dest_high(l_counter);
2866 l_dest_low := l_lanes_temp.dest_low(l_counter);
2867 l_dest_high := l_lanes_temp.dest_high(l_counter);
2868 l_min_charge1 := l_lanes_temp.min_charge1(l_counter); -- it is the same for all of the classes
2869
2870 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
2871 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_previous_dest_low ', l_previous_dest_low);
2872 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_previous_dest_high', l_previous_dest_high);
2873 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_dest_low ', l_dest_low);
2874 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_dest_high ', l_dest_high);
2875 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_min_charge1 ', l_min_charge1);
2876 END IF;
2877
2878 END IF;
2879
2880 IF (l_dest_low > l_dest_high) THEN
2881 x_status := 2;
2882 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_INVALID_ZIP_CODE_RANGE');
2883 FTE_UTIL_PKG.Write_OutFile( p_msg => x_error_msg,
2884 p_module_name => l_module_name,
2885 p_category => 'D',
2886 p_line_number => 0 );
2887 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2888 RETURN;
2889 END IF;
2890
2891 --+
2892 -- Loop through till the destination remains same and till the last record.
2893 --+
2894 WHILE (l_counter <= c_current_fetch AND
2895 l_lanes_temp.dest_low(l_counter) = l_previous_dest_low AND
2896 l_lanes_temp.dest_high(l_counter) = l_previous_dest_high)
2897 LOOP
2898 l_class := l_lanes_temp.class(l_counter);
2899 --
2900 -- Check if the Class is already present in the string for same block.
2901 --
2902 IF (l_class_string IS NOT NULL) THEN
2903 IF (instr(l_class_string, ',' || l_class || ',') <> 0) THEN
2904 x_status := 2;
2905 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_DUPLICATE_FRIEGHT_CLASS');
2906 FTE_UTIL_PKG.Write_logFile(l_module_name, l_class_string || ' ' || l_class);
2907 FTE_UTIL_PKG.Write_OutFile( p_msg => x_error_msg,
2908 p_module_name => l_module_name,
2909 p_category => 'D',
2910 p_line_number => 0);
2911 FTE_UTIL_PKG.Exit_Debug(l_module_name);
2912 RETURN;
2913 END IF;
2914 END IF;
2915
2916 IF (l_class_string IS NULL) THEN
2917 l_class_string := ',' || l_class || ',';
2918 ELSE
2919 l_class_string := l_class_string || l_class || ',';
2920 END IF;
2921
2922 l_total_string := l_total_string || l_lanes_temp.rate_chart_string(l_counter);
2923 l_counter := l_counter + 1;
2924
2925 END LOOP;
2926
2927 l_need_to_fetch := l_counter > c_current_fetch;
2928
2929 --+
2930 -- Only if the last fetched row has a different destination we can insert into some table
2931 -- otherwise we have to fetch more rows and append them to
2932 --+
2933 l_last_block := c_current_fetch < 1000;
2934
2935 IF (l_counter <= c_current_fetch OR l_last_block ) THEN
2936
2937 l_hash_value := DBMS_UTILITY.GET_HASH_VALUE(name => l_total_string,
2938 base => g_hash_base,
2939 hash_size => g_hash_size);
2940 l_min_charge1 := ltrim(l_min_charge1, '0');
2941
2942 IF (l_min_charge1 IS NOT NULL AND length(l_min_charge1) > 0) THEN
2943 l_min_charge1 := Fnd_Number.Number_To_Canonical(Fnd_Number.Canonical_To_Number(l_min_charge1)/100);
2944 END IF;
2945
2946 l_min_charge_match := FALSE;
2947 l_rate_chart_match := 'N';
2948
2949 --+
2950 -- Find a zone in the existing load with the same rate chart
2951 -- i.e. Same rate chart string and class string.
2952 --+
2953 SELECT
2954 dest_name,
2955 min_charge1,
2956 rate_chart_name
2957 BULK COLLECT INTO
2958 l_dest_names,
2959 l_min_charges,
2960 l_rate_names
2961 FROM
2962 fte_interface_lanes
2963 WHERE
2964 hash_value = l_hash_value AND
2965 rate_chart_string = l_total_string AND
2966 class_string = l_class_string AND
2967 origin_name = l_origin_name AND
2968 load_id = p_load_id;
2969
2970 --+
2971 -- If we find an existing zone/lane with the same rate chart, we reuse the
2972 -- rate chart. However we only reuse the lane if the minimum charge is identical.
2973 -- If the minimum charge is not identical, we create a new zone/lane with this
2974 -- new minimum charge, but sharing the rate chart.
2975 --+
2976 IF (l_rate_names.COUNT > 0) THEN
2977
2978 l_rate_chart_match := 'Y';
2979
2980 -- reuse the rate chart
2981 l_rate_name := l_rate_names(1);
2982
2983 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
2984 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_rate_name' || l_rate_name);
2985 END IF;
2986
2987 --+
2988 -- Search the found lanes/zones with the same minimum charge.
2989 --+
2990 FOR i IN 1..l_rate_names.COUNT LOOP
2991 --+
2992 -- If you find an zone/lane with the same min. charge,
2993 -- add this information to the existing zone.
2994 --+
2995 IF (l_min_charges(i) = l_min_charge1) THEN
2996
2997 l_min_charge_match := TRUE;
2998 l_row_number := row_num_max(l_dest_names(i));
2999
3000 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
3001 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_row_number' || l_row_number);
3002 END IF;
3003
3004 INSERT INTO fte_interface_zones(ZONE_NAME,
3005 POSTAL_CODE_FROM,
3006 POSTAL_CODE_TO,
3007 POSTAL_CODE_STRING,
3008 LOAD_ID,
3009 HASH_VALUE,
3010 ZONE_ID,
3011 ROW_NUMBER )
3012 VALUES(l_dest_names(i),
3013 l_dest_low,
3014 l_dest_high,
3015 '',
3016 p_load_id,
3017 0,
3018 null,
3019 l_row_number);
3020 EXIT;
3021 END IF; -- if the min_charges also match
3022 END LOOP;
3023 END IF; -- if rate chart matched
3024
3025 --+
3026 -- If there was a rate chart match, but no minimum charge match
3027 -- then we reuse the rate chart. In all cases where there was no
3028 -- minimum charge match, we create a new zone and lane.
3029 --+
3030 IF (NOT l_min_charge_match) THEN
3031 --+
3032 -- If there was no rate chart match, create a new rate chart
3033 --+
3034 IF (l_rate_chart_match = 'N') THEN
3035
3036 l_rate_name := p_tariff_name || '_' || l_number_of_charts || '_' || l_number_of_loads;
3037
3038 CREATE_RATE_CHART(p_chart_name => l_rate_name,
3039 p_carrier_ids => p_carrier_ids,
3040 p_break_string => l_total_string,
3041 p_class_string => l_class_string,
3042 x_status => x_status,
3043 x_error_msg => x_error_msg,
3044 x_process_id => l_process_id);
3045
3046 IF (x_status <> -1) THEN
3047 x_status := 2;
3048 FTE_UTIL_PKG.Exit_Debug(l_module_name);
3049 RETURN;
3050 END IF;
3051
3052 l_group_id := 1 + mod(l_group_id, G_NUM_CONC_PROCESSES);
3053 l_number_of_charts := l_number_of_charts + 1;
3054
3055 END IF;
3056
3057 l_zone_name := p_load_id || '-' || l_number_of_zones;
3058
3059 INSERT INTO fte_interface_lanes(ORIGIN_NAME,
3060 DEST_NAME,
3061 RATE_CHART_STRING,
3062 RATE_CHART_NAME,
3063 HASH_VALUE,
3064 CLASS_STRING,
3065 MIN_CHARGE1,
3066 LOAD_ID,
3067 ORIGIN_ID,
3068 DEST_ID,
3069 PROCESS_ID,
3070 GROUP_PROCESS_ID)
3071 VALUES(l_origin_name,
3072 l_zone_name,
3073 l_total_string,
3074 l_rate_name,
3075 l_hash_value,
3076 l_class_string,
3077 l_min_charge1,
3078 p_load_id,
3079 null,
3080 null,
3081 l_process_id,
3082 decode(l_rate_chart_match, 'N', l_group_id, 'Y', NULL));
3083
3084 INSERT INTO fte_interface_zones(ZONE_NAME,
3085 POSTAL_CODE_FROM,
3086 POSTAL_CODE_TO,
3087 POSTAL_CODE_STRING,
3088 LOAD_ID,
3089 HASH_VALUE,
3090 ZONE_ID,
3091 ROW_NUMBER )
3092 VALUES( l_zone_name,
3093 l_dest_low,
3094 l_dest_high,
3095 '',
3096 p_load_id,
3097 0,
3098 null,
3099 1);
3100
3101 l_number_of_zones := l_number_of_zones + 1;
3102 END IF; --IF (NOT l_min_charge_match)
3103 END IF ; -- IF (l_counter <= c_current_fetch OR l_last_block)
3104 END LOOP; -- WHILE (l_counter <= c_current_fetch) LOOP
3105
3106 c_previous_fetch := CREATE_STRING%ROWCOUNT ;
3107
3108 END LOOP;
3109
3110 CLOSE CREATE_STRING;
3111
3112 END LOOP;
3113
3114 CLOSE NEW_ORIGIN;
3115
3116 IF (G_TOTAL_NUMCHARTS < G_NUM_CONC_PROCESSES) THEN
3117 G_NUM_CONC_PROCESSES := G_TOTAL_NUMCHARTS;
3118 END IF;
3119
3120 --
3121 -- Call SUBMIT_QP_PROCESS to insert the last set of rate charts into qp_interface tables.
3122 --
3123 FTE_RATE_CHART_LOADER.SUBMIT_QP_PROCESS(p_qp_call => FALSE,
3124 x_status => x_status,
3125 x_error_msg => x_error_msg);
3126
3127 IF (x_status <> -1) THEN
3128 FTE_UTIL_PKG.Exit_Debug(l_module_name);
3129 RETURN;
3130 ELSE
3131
3132 x_origin := l_origin;
3133 x_origin_name := l_origin_name;
3134 x_number_of_loads := l_number_of_loads;
3135
3136 --
3137 -- Need to commit here because we are going to exit this
3138 -- program, call QP_PROCESS, and come back later.
3139 --
3140 COMMIT;
3141
3142 END IF;
3143
3144 FTE_UTIL_PKG.Exit_Debug(l_module_name);
3145 EXCEPTION
3146 WHEN OTHERS THEN
3147
3148 IF (GET_LOAD_NUMBER%ISOPEN) THEN
3149 CLOSE GET_LOAD_NUMBER;
3150 END IF;
3151
3152 IF(CREATE_STRING%ISOPEN)THEN
3153 CLOSE CREATE_STRING;
3154 END IF;
3155
3156 IF(NEW_ORIGIN%ISOPEN)THEN
3157 CLOSE NEW_ORIGIN;
3158 END IF;
3159
3160 x_status := 2;
3161 x_error_msg := SQLERRM;
3162 FTE_UTIL_PKG.Exit_Debug(l_module_name);
3163
3164 END BUILD_ZONES_AND_CHARTS;
3165
3166 --___________________________________________________________________________________--
3167 --
3168 -- PROCEDURE: MANAGE_ZONES
3169 --
3170 -- PURPOSE
3171 -- Remove redundant rows from FTE_INTERFACE_ZONES by grouping
3172 -- all zones sharing the same rate chart into a single row.
3173 -- Reuse old zones if the new zones are exactly the same.
3174 --
3175 -- PARAMETERS
3176 -- IN
3177 -- p_load_id, the process_id which identifies self load.
3178 -- p_tariff_name, the tariff name in question
3179 -- OUT
3180 -- x_status, the return status of the procedure,
3181 -- -1, success
3182 -- any other non negative value indicates failure.
3183 -- x_error_msg, the error message indicating the cause and detailing the error occured.
3184 --___________________________________________________________________________________--
3185
3186 PROCEDURE MANAGE_ZONES(p_load_id IN NUMBER,
3187 p_tariff_name IN VARCHAR2,
3188 p_origin_name IN VARCHAR2,
3189 x_status OUT NOCOPY VARCHAR2,
3190 x_error_msg OUT NOCOPY VARCHAR2) IS
3191
3192 l_identical_zone VARCHAR2(40);
3193 l_zone_id NUMBER;
3194 l_matching_zones STRINGARRAY;
3195 l_sum_row_number NUMBER;
3196 l_max_row_number NUMBER;
3197 l_sum_hash_value NUMBER;
3198 l_num_zone_matches NUMBER;
3199 l_new_zones_info zone_info_record;
3200 l_more_rows BOOLEAN;
3201 l_counter NUMBER;
3202 l_zone_name VARCHAR2(125);
3203 l_zones_temp zones_temp_record;
3204 l_row_number NUMBER;
3205 l_last_block BOOLEAN;
3206 l_dest_string VARCHAR2(4000);
3207 c_previous_fetch NUMBER;
3208 c_current_fetch NUMBER;
3209 l_previous_name VARCHAR2(40);
3210 l_need_to_fetch BOOLEAN;
3211 l_hash_value NUMBER;
3212 g_hash_base NUMBER := 1;
3213 g_hash_size NUMBER := power(2, 25);
3214
3215 l_return_status VARCHAR2(100);
3216 l_error_msg VARCHAR2(100);
3217
3218 --+
3219 -- This cursor is used to collect queried rows from FTE_INTERFACE_ZONES. With those rows we want to
3220 -- check if zones are already present into the system.
3221 --+
3222 CURSOR DEFINE_ZONES(p_load_id NUMBER) IS
3223 SELECT
3224 zone_name,
3225 postal_code_from,
3226 postal_code_to
3227 FROM
3228 fte_interface_zones
3229 WHERE
3230 load_id = p_load_id
3231 ORDER BY
3232 zone_name ASC, row_number ASC ;
3233
3234 --
3235 -- Get summary zone information about existing zones in
3236 -- fte_interface_zones. This is used to check if a new
3237 -- zone already exists in the table.
3238 --
3239 CURSOR GET_EXISTING_ZONE_INFO(p_load_id IN NUMBER,
3240 p_sum_rownum IN NUMBER,
3241 p_max_rownum IN NUMBER,
3242 p_sum_hash IN NUMBER)IS
3243 SELECT
3244 zone_name
3245 FROM
3246 fte_interface_zones
3247 WHERE
3248 load_id <> p_load_id
3249 HAVING
3250 SUM(hash_value) = p_sum_hash AND
3251 SUM(row_number) = p_sum_rownum AND
3252 MAX(row_number) = p_max_rownum
3253 GROUP BY zone_name;
3254 --
3255 --
3256 CURSOR GET_NEW_ZONES_INFO(p_load_id IN NUMBER)IS
3257 SELECT
3258 zone_name,
3259 SUM(row_number),
3260 MAX(row_number),
3261 SUM(hash_value)
3262 FROM
3263 fte_interface_zones
3264 WHERE
3265 load_id = p_load_id AND
3266 hash_value <> 0
3267 GROUP BY
3268 zone_name;
3269
3270 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.MANAGE_ZONES';
3271
3272 BEGIN
3273
3274 FTE_UTIL_PKG.Enter_Debug(l_module_name);
3275 x_status := -1;
3276
3277 c_previous_fetch := 0;
3278 l_previous_name := null;
3279 l_zone_name := null;
3280 l_need_to_fetch := FALSE;
3281 l_row_number := 1;
3282 l_more_rows := false;
3283 l_dest_string := '';
3284
3285 OPEN DEFINE_ZONES(p_load_id);
3286 LOOP
3287 l_counter := 1;
3288
3289 FETCH DEFINE_ZONES BULK COLLECT INTO l_zones_temp.zone_name,
3290 l_zones_temp.dest_low,
3291 l_zones_temp.dest_high LIMIT 1000;
3292
3293 c_current_fetch := DEFINE_ZONES%ROWCOUNT - c_previous_fetch;
3294
3295 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
3296 FTE_UTIL_PKG.Write_LogFile(l_module_name,'c_current_fetch ', c_current_fetch);
3297 FTE_UTIL_PKG.Write_LogFile(l_module_name,'DEFINE_ZONES%ROWCOUNT',define_zones%ROWCOUNT);
3298 FTE_UTIL_PKG.Write_LogFile(l_module_name,'c_previous_fetch ',c_previous_fetch);
3299 END IF;
3300
3301 EXIT WHEN (c_current_fetch <= 0);
3302
3303 WHILE (l_counter <= c_current_fetch) LOOP
3304
3305 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
3306 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_counter ', l_counter);
3307 FTE_UTIL_PKG.Write_LogFile(l_module_name,'c_current_fetch', c_current_fetch);
3308 IF (l_need_to_fetch) THEN
3309 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_need_to_fetch','TRUE');
3310 ELSE
3311 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_need_to_fetch','FALSE');
3312 END IF;
3313 END IF;
3314
3315 IF (not l_need_to_fetch) THEN
3316 l_dest_string := '';
3317 l_previous_name := l_zones_temp.zone_name(l_counter); -- save zone name
3318 END IF;
3319
3320 IF (l_need_to_fetch AND l_previous_name <> l_zones_temp.zone_name(l_counter)) THEN
3321 --
3322 -- zone name has changed at fetch boundary. We need to update
3323 -- the previous zone in fte_interface_zones before building the
3324 -- next zone.
3325 --
3326 l_zone_name := l_previous_name;
3327 ELSE
3328 l_zone_name := l_zones_temp.zone_name(l_counter);
3329 END IF;
3330
3331 -- build the postal code string
3332 WHILE (l_counter <= c_current_fetch and l_zones_temp.zone_name(l_counter) = l_previous_name and
3333 (l_dest_string is null or LENGTH(l_dest_string) <= 3600) ) LOOP
3334
3335 l_dest_string := l_dest_string ||
3336 l_zones_temp.dest_low(l_counter) ||','||
3337 l_zones_temp.dest_high(l_counter)||';';
3338
3339 l_counter := l_counter + 1;
3340
3341 END LOOP;
3342
3343 --
3344 -- If the dest_string is too long we have to start storing in following rows of the same zone.
3345 --
3346 l_more_rows := LENGTH(l_dest_string) >= 3600;
3347
3348 --
3349 -- I only have to update the ROW and increment the number of rows to compare with
3350 --
3351 l_need_to_fetch := l_counter > c_current_fetch;
3352
3353 l_last_block := c_current_fetch < 1000;
3354
3355 IF (l_counter <= c_current_fetch OR l_last_block) THEN
3356 --
3357 -- Before insert the string in the zones row we have to check if that string
3358 -- is already in the zones_temp, in that case we only have to update lanes_temp with the zone_name
3359 --
3360 l_hash_value := DBMS_UTILITY.GET_HASH_VALUE(name => l_dest_string,
3361 base => g_hash_base,
3362 hash_size => g_hash_size );
3363
3364 UPDATE
3365 FTE_INTERFACE_ZONES
3366 SET
3367 POSTAL_CODE_STRING = l_dest_string,
3368 HASH_VALUE = l_hash_value
3369 WHERE
3370 ZONE_NAME = l_zone_name AND
3371 LOAD_ID = p_load_id AND
3372 ROW_NUMBER = l_row_number;
3373
3374 IF (l_more_rows ) THEN
3375 l_row_number := l_row_number + 1;
3376 ELSE
3377 l_row_number := 1;
3378 END IF;
3379 END IF; -- IF (l_counter <= c_current_fetch OR l_last_block)
3380 END LOOP; -- WHILE (l_counter <= c_current_fetch)
3381
3382 c_previous_fetch := define_zones%ROWCOUNT ;
3383
3384 END LOOP;
3385
3386 CLOSE DEFINE_ZONES;
3387
3388 c_current_fetch := 0;
3389 c_previous_fetch := 0;
3390
3391 -- Part 2
3392 OPEN GET_NEW_ZONES_INFO(p_load_id);
3393 --
3394 -- Get summary information about all the new zones, and compare with
3395 -- summary information about all the existing zones.
3396 --
3397 LOOP
3398 FETCH GET_NEW_ZONES_INFO
3399 BULK COLLECT INTO l_new_zones_info.zone_name,
3400 l_new_zones_info.sum_row_number,
3401 l_new_zones_info.max_row_number,
3402 l_new_zones_info.sum_hash_value LIMIT 1000;
3403
3404 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
3405 FTE_UTIL_PKG.Write_LogFile(l_module_name,'GET_NEW_ZONES_INFO%ROWCOUNT',GET_NEW_ZONES_INFO%ROWCOUNT);
3406 END IF;
3407
3408
3409 FOR i IN 1..l_new_zones_info.zone_name.COUNT
3410 LOOP
3411 l_zone_name := l_new_zones_info.zone_name(i);
3412 l_sum_row_number := l_new_zones_info.sum_row_number(i);
3413 l_max_row_number := l_new_zones_info.max_row_number(i);
3414 l_sum_hash_value := l_new_zones_info.sum_hash_value(i);
3415
3416 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
3417 FTE_UTIL_PKG.Write_LogFile(l_module_name,'i',i);
3418 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_zone_name ',l_zone_name);
3419 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_sum_row_number ',l_sum_row_number);
3420 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_max_row_number ',l_max_row_number);
3421 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_sum_hash_value ',l_sum_hash_value);
3422 END IF;
3423
3424 OPEN GET_EXISTING_ZONE_INFO(p_load_id => p_load_id,
3425 p_sum_rownum => l_sum_row_number,
3426 p_max_rownum => l_max_row_number,
3427 p_sum_hash => l_sum_hash_value);
3428
3429 FETCH GET_EXISTING_ZONE_INFO
3430 BULK COLLECT INTO l_matching_zones;
3431
3432 l_num_zone_matches := GET_EXISTING_ZONE_INFO%ROWCOUNT;
3433
3434 CLOSE GET_EXISTING_ZONE_INFO;
3435
3436 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
3437 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_num_zone_matches', l_num_zone_matches);
3438 END IF;
3439
3440 IF (l_num_zone_matches >= 1) THEN
3441 --
3442 -- We've found a zone whose summary information matches the new zone.
3443 -- compare their postal code strings
3444 --
3445 IF (l_zone_name = p_origin_name) THEN
3446 FIND_IDENTICAL_ZONE(p_zone_name => l_zone_name,
3447 p_matching_zones => l_matching_zones,
3448 p_origin_flag => TRUE,
3449 x_identical_zone => l_identical_zone,
3450 x_zone_id => l_zone_id,
3451 x_status => l_return_status,
3452 x_error_msg => l_error_msg );
3453 ELSE
3454 FIND_IDENTICAL_ZONE(p_zone_name => l_zone_name,
3455 p_matching_zones => l_matching_zones,
3456 p_origin_flag => FALSE,
3457 x_identical_zone => l_identical_zone,
3458 x_zone_id => l_zone_id,
3459 x_status => l_return_status,
3460 x_error_msg => l_error_msg);
3461 END IF;
3462
3463 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
3464 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_identical_zone', l_identical_zone);
3465 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_zone_name ', l_zone_name);
3466 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_zone_id ', l_zone_id);
3467 END IF;
3468
3469 IF (l_identical_zone IS NOT NULL) THEN
3470
3471 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
3472 FTE_UTIL_PKG.Write_LogFile(l_module_name,'Found Matching Zone');
3473 END IF;
3474
3475 IF (l_zone_id IS NULL) THEN
3476 -- Serious Error --
3477 FTE_UTIL_PKG.Write_LogFile(l_module_name,'Cannot Find Zone ID!!!');
3478 RETURN;
3479 END IF;
3480
3481 --
3482 -- we've found an identical zone!
3483 --
3484 IF (l_zone_name = p_origin_name) THEN
3485 --+
3486 -- We need to update origin
3487 --+
3488 UPDATE
3489 fte_interface_lanes
3490 SET
3491 origin_name = l_identical_zone,
3492 origin_id = l_zone_id
3493 WHERE
3494 load_id = p_load_id AND
3495 origin_name = l_zone_name;
3496 ELSE
3497 -- We need to update dest
3498 UPDATE
3499 fte_interface_lanes
3500 SET
3501 dest_name = l_identical_zone,
3502 dest_id = l_zone_id
3503 WHERE
3504 load_id = p_load_id AND
3505 dest_name = l_zone_name;
3506
3507 END IF;
3508
3509 --
3510 -- we don't need this current zone
3511 --
3512 DELETE FROM
3513 fte_interface_zones
3514 WHERE
3515 zone_name = l_zone_name AND
3516 load_id = p_load_id;
3517 END IF;
3518 END IF;
3519
3520 END LOOP;
3521
3522 EXIT WHEN (GET_NEW_ZONES_INFO%NOTFOUND);
3523
3524 END LOOP;
3525
3526 CLOSE GET_NEW_ZONES_INFO;
3527
3528 FTE_UTIL_PKG.Exit_Debug(l_module_name);
3529
3530 EXCEPTION
3531 WHEN OTHERS THEN
3532
3533 IF (DEFINE_ZONES%ISOPEN) THEN
3534 CLOSE DEFINE_ZONES;
3535 END IF;
3536 IF (GET_NEW_ZONES_INFO%ISOPEN) THEN
3537 CLOSE GET_NEW_ZONES_INFO;
3538 END IF;
3539
3540 x_status := 2;
3541 x_error_msg := SQLERRM;
3542 FTE_UTIL_PKG.Write_LogFile(l_module_name,'UNEXPECTED ERROR',SQLERRM);
3543 FTE_UTIL_PKG.Exit_Debug(l_module_name);
3544
3545 END MANAGE_ZONES;
3546
3547
3548 --_________________________________________________________________________________________--
3549 --
3550 -- PROCEDURE: CREATE_ZONES_AND_REGIONS
3551 --
3552 -- PURPOSE
3553 -- Use the WSH API to create the regions and
3554 -- zones in the WSH tables.
3555 -- PARAMETERS
3556 -- IN
3557 -- p_load_id, the process_id which identifies self load.
3558 --__________________________________________________________________________________________--
3559
3560 PROCEDURE CREATE_ZONES_AND_REGIONS (p_load_id IN NUMBER,
3561 p_origin_name IN VARCHAR2,
3562 x_status OUT NOCOPY VARCHAR2,
3563 x_error_msg OUT NOCOPY VARCHAR2) IS
3564
3565 l_zone_id NUMBER;
3566 l_region_id NUMBER;
3567 l_parent_region_id NUMBER;
3568 l_status NUMBER;
3569 c_previous_fetch NUMBER;
3570 c_current_fetch NUMBER;
3571 l_country_code VARCHAR2(10);
3572 l_previous_name VARCHAR2(40);
3573 l_counter NUMBER;
3574 l_zones_temp zones_temp_record;
3575 l_zone_name VARCHAR2(125);
3576
3577 --+
3578 -- This cursor is used to collect queried rows from fte_interface_zones. Those are used to
3579 -- create Zones into WSH_REGIONS, WSH_REGIONS_TL and WSH_ZONE_REGIONS
3580 --+
3581 CURSOR BULK_ZONES(p_load_id NUMBER) IS
3582 SELECT
3583 zone_name,
3584 postal_code_from,
3585 postal_code_to
3586 FROM
3587 fte_interface_zones
3588 WHERE
3589 load_id = p_load_id AND
3590 zone_id is null
3591 ORDER BY
3592 zone_name,
3593 row_number asc;
3594
3595 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.CREATE_ZONES_AND_REGIONS';
3596
3597 BEGIN
3598 FTE_UTIL_PKG.Enter_Debug(l_module_name);
3599 x_status := -1;
3600
3601 l_previous_name := 'NULL';
3602 l_zone_id := -1;
3603 c_previous_fetch := 0;
3604
3605 OPEN BULK_ZONES(p_load_id);
3606 LOOP
3607 l_counter := 1;
3608
3609 FETCH BULK_ZONES BULK COLLECT INTO l_zones_temp.zone_name,
3610 l_zones_temp.dest_low,
3611 l_zones_temp.dest_high LIMIT 1000;
3612
3613 c_current_fetch := BULK_ZONES%ROWCOUNT - c_previous_fetch;
3614
3615 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
3616 FTE_UTIL_PKG.Write_LogFile(l_module_name,'c_current_fetch ', c_current_fetch);
3617 FTE_UTIL_PKG.Write_LogFile(l_module_name,'BULK_ZONES%ROWCOUNT ', BULK_ZONES%ROWCOUNT);
3618 FTE_UTIL_PKG.Write_LogFile(l_module_name,'c_previous_fetch ', c_previous_fetch);
3619 END IF;
3620
3621 EXIT WHEN (c_current_fetch <= 0);
3622
3623 WHILE (l_counter <= c_current_fetch) LOOP
3624
3625 l_zone_name := l_zones_temp.zone_name(l_counter);
3626
3627 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
3628 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_zone_name ', l_zone_name);
3629 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_counter ', l_counter);
3630 END IF;
3631
3632 IF (l_zones_temp.zone_name(l_counter) <> l_previous_name) THEN
3633 --+
3634 -- Anytime that the ZONE NAME changes we have to create a new ZONE
3635 --+
3636 WSH_REGIONS_PKG.UPDATE_ZONE(p_insert_type => 'INSERT',
3637 p_zone_id => '',
3638 p_zone_name => l_zone_name,
3639 p_zone_level => 11,
3640 p_zone_type => 11,
3641 p_lang_code => userenv('LANG'),
3642 p_user_id => G_USER_ID,
3643 x_zone_id => l_zone_id,
3644 x_status => l_status,
3645 x_error_msg => x_error_msg);
3646
3647 IF (l_status = 2) THEN
3648 --+
3649 -- It means that the Zone already exists.
3650 --+
3651 SELECT
3652 region_id
3653 INTO
3654 l_zone_id
3655 FROM
3656 wsh_regions_tl
3657 WHERE
3658 zone = l_zone_name;
3659 END IF;
3660
3661 IF (l_zone_id IS NULL) THEN
3662 x_status := 2;
3663 x_error_msg := 'Zone ID is NULL';
3664 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Zone ID is NULL after WSH_REGIONS_PKG.Update_Zone ');
3665 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_zone_name',l_zone_name);
3666 FTE_UTIL_PKG.Exit_Debug(l_module_name);
3667 RETURN;
3668 END IF;
3669
3670 BEGIN
3671 UPDATE
3672 FTE_INTERFACE_ZONES
3673 SET
3674 ZONE_ID = l_zone_id
3675 WHERE
3676 zone_name = l_zone_name AND
3677 HASH_VALUE <> 0 AND
3678 LOAD_ID = p_load_id;
3679
3680 IF (l_zone_name = p_origin_name) THEN
3681 --
3682 -- We need to update origin ID
3683 --
3684 UPDATE
3685 FTE_INTERFACE_LANES
3686 SET
3687 ORIGIN_ID = l_zone_id
3688 WHERE
3689 load_id = p_load_id AND
3690 origin_name = l_zone_name;
3691
3692 l_country_code := g_orig_country;
3693
3694 ELSE
3695 -- We need to update dest
3696 UPDATE
3697 FTE_INTERFACE_LANES
3698 SET
3699 DEST_ID = l_zone_id
3700 WHERE
3701 load_id = p_load_id AND
3702 dest_name = l_zone_name;
3703
3704 l_country_code := G_DEST_COUNTRY;
3705 END IF;
3706
3707 EXCEPTION
3708
3709 WHEN OTHERS THEN
3710 x_status := 2;
3711 x_error_msg := sqlerrm;
3712 FTE_UTIL_PKG.Write_LogFile(l_module_name,'Premature UNEXPECTED Error', sqlerrm);
3713 FTE_UTIL_PKG.Exit_Debug(l_module_name);
3714 RETURN;
3715 END;
3716
3717 END IF;
3718
3719 WSH_REGIONS_PKG.UPDATE_ZONE_REGION(p_insert_type => 'INSERT',
3720 p_zone_region_id => null,
3721 p_zone_id => l_zone_id,
3722 p_region_id => null,
3723 p_country => '',
3724 p_state => '',
3725 p_city => '',
3726 p_postal_code_from => l_zones_temp.dest_low(l_counter),
3727 p_postal_code_to => l_zones_temp.dest_high(l_counter),
3728 p_lang_code => userenv('LANG'),
3729 p_country_code => l_country_code,
3730 p_state_code => '',
3731 p_city_code => '',
3732 p_user_id => G_USER_ID,
3733 p_zone_type => '11',
3734 x_zone_region_id => l_region_id,
3735 x_region_id => l_parent_region_id,
3736 x_status => l_status,
3737 x_error_msg => x_error_msg);
3738 l_previous_name := l_zone_name;
3739 l_counter := l_counter +1;
3740
3741 IF (l_status = 1 AND x_error_msg = 'WSH_SAME_REGION_IN_ZONE') THEN
3742 --+
3743 -- if the region already exists in that zone, then we'll
3744 -- get an error, but that's ok.
3745 --+
3746 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'WSH_SAME_REGION_IN_ZONE');
3747 FTE_UTIL_PKG.Write_OutFile(p_msg => x_error_msg,
3748 p_module_name => l_module_name,
3749 p_category => 'D',
3750 p_line_number => 0);
3751
3752 -- If it failed for any other reason, we need to exit.
3753 ELSIF (l_status = 1) THEN
3754 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => x_error_msg);
3755 FTE_UTIL_PKG.Write_OutFile(p_msg => x_error_msg,
3756 p_module_name => l_module_name,
3757 p_category => 'D',
3758 p_line_number => 0);
3759
3760 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
3761 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_zones_temp.dest_low(' || l_counter || ')', l_zones_temp.dest_low(l_counter-1));
3762 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_zones_temp.dest_high(' || l_counter || ')', l_zones_temp.dest_high(l_counter-1));
3763 END IF;
3764
3765 x_status := 2;
3766 FTE_UTIL_PKG.Exit_Debug(l_module_name);
3767
3768 RETURN;
3769
3770 END IF;
3771
3772 END LOOP;
3773
3774 c_previous_fetch := bulk_zones%ROWCOUNT ;
3775
3776 END LOOP;
3777
3778 CLOSE BULK_ZONES;
3779 EXCEPTION
3780 WHEN OTHERS THEN
3781
3782 IF (BULK_ZONES%ISOPEN) THEN
3783 CLOSE BULK_ZONES;
3784 END IF;
3785 x_status := 2;
3786 x_error_msg := SQLERRM;
3787 FTE_UTIL_PKG.Write_LogFile(p_module_name => l_module_name,
3788 p_message => sqlerrm);
3789 FTE_UTIL_PKG.Exit_Debug(l_module_name);
3790
3791 END CREATE_ZONES_AND_REGIONS;
3792
3793
3794 --___________________________________________________________________________________--
3795 --
3796 -- PROCEDURE: LOAD_TEMP_TABLES
3797 --
3798 -- Purpose
3799 -- Create the lanes, zones and rate charts from the data in FTE_BULKLOAD_FILE.
3800 --
3801 -- It calls 4 sub procedures.
3802 -- i. BUILD_ZONES_AND_CHARTS
3803 -- ii. MANAGE_ZONES
3804 -- iii. CREATE_ZONES_AND_REGIONS
3805 -- iv. CREATE_LANES
3806 --
3807 -- IN Parameters
3808 -- 1. p_load_id: The load id for the bulkload job.
3809 -- 2. p_service_code: The service level for this load
3810 -- 3. p_tariff_name: The tariff name
3811 -- 4. p_carrier_ids: a nuber table of carrier ids.
3812 --
3813 -- OUT Parameters
3814 -- 1. x_status: return status.
3815 -- -1, Success, Failure otherwise.
3816 -- 2. x_error_msg: Error message, if there is an error.
3817 --
3818 --___________________________________________________________________________________--
3819
3820 PROCEDURE LOAD_TEMP_TABLES (p_load_id IN NUMBER,
3821 p_tariff_name IN VARCHAR2,
3822 p_carrier_ids IN NUMBER_TAB,
3823 p_effective_dates IN STRINGARRAY,
3824 p_expiry_dates IN STRINGARRAY,
3825 x_status OUT NOCOPY VARCHAR2,
3826 x_error_msg OUT NOCOPY VARCHAR2) IS
3827
3828 l_phase NUMBER := 1;
3829 l_requests_tab FND_CONCURRENT.REQUESTS_TAB_TYPE;
3830 l_var_string VARCHAR2(200);
3831 l_scac VARCHAR2(50);
3832 l_origin VARCHAR2(15);
3833 l_status VARCHAR2(10);
3834 l_error_msg VARCHAR2(1000);
3835 l_number_of_loads NUMBER;
3836 l_origin_name VARCHAR2(125);
3837 l_counter NUMBER;
3838 l_debug_on NUMBER;
3839 t_varchar2_tab STRINGARRAY;
3840
3841 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.LOAD_TEMP_TABLES';
3842
3843 BEGIN
3844 FTE_UTIL_PKG.Enter_Debug(l_module_name);
3845 x_status := -1;
3846
3847 --+
3848 -- Remember these G_ORIG/DEST_COUNTRY are mantadory fields
3849 -- in the UI. Now, you should know where these variables had been intialized.
3850 -- Try PROCESS_LTL_DATA -> LOAD_LTL_DATA.
3851 --+
3852
3853 IF (G_DIRECTION_FLAG = 'I') THEN
3854 G_REF_COUNTRY := G_ORIG_COUNTRY;
3855 ELSE
3856 G_REF_COUNTRY := G_DEST_COUNTRY;
3857 END IF;
3858
3859 l_phase := GET_PHASE;
3860
3861 IF(l_phase = 1) THEN
3862
3863 BUILD_ZONES_AND_CHARTS(p_load_id => p_load_id,
3864 p_tariff_name => p_tariff_name,
3865 p_carrier_ids => p_carrier_ids,
3866 x_origin => l_origin,
3867 x_origin_name => l_origin_name,
3868 x_number_of_loads => l_number_of_loads,
3869 x_status => x_status,
3870 x_error_msg => x_error_msg );
3871
3872 IF ( x_status <> -1 ) THEN
3873 FTE_UTIL_PKG.Write_LogFile(l_module_name,'Return status from BUILD_ZONES_AND_CHARTS', x_status);
3874 RETURN;
3875 END IF;
3876
3877 LOAD_RATE_CHARTS(p_load_id => p_load_id,
3878 p_number_of_loads => l_number_of_loads,
3879 p_origin => l_origin,
3880 p_origin_name => l_origin_name,
3881 x_status => l_status,
3882 x_error_msg => l_error_msg );
3883
3884 IF ( x_status <> -1 ) THEN
3885 FTE_UTIL_PKG.Write_LogFile(l_module_name,'Return status from LOAD_RATE_CHARTS', x_status);
3886 RETURN;
3887 END IF;
3888
3889 ELSE
3890
3891 --+
3892 -- Sub-requests have been submitted already.
3893 -- get the stored variables and parse the string. Dont you belive me.
3894 -- Please, verify at the end of LOAD_RATE_CHARTS procedure.
3895 --+
3896
3897 l_var_string := FND_CONC_GLOBAL.request_data;
3898 t_varchar2_tab := FTE_UTIL_PKG.TOKENIZE_STRING(l_var_string, ',');
3899
3900 G_VALID_DATE := to_date(t_varchar2_tab(1), 'rrrrmmdd');
3901 l_number_of_loads := Fnd_Number.Canonical_To_Number(t_varchar2_tab(2));
3902 l_origin := t_varchar2_tab(3);
3903 l_origin_name := t_varchar2_tab(4);
3904 l_debug_on := t_varchar2_tab(5);
3905 G_DIRECTION_FLAG := t_varchar2_tab(6);
3906 G_REPORT_HEADER.StartDate := t_varchar2_tab(7);
3907
3908 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
3909 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'G_VALID_DATE ', G_VALID_DATE);
3910 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_number_of_loads', l_number_of_loads);
3911 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_origin ', l_origin);
3912 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_origin_name ', l_origin_name);
3913 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_debug_on ', l_debug_on);
3914 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'G_DIRECTION_FLAG ', G_DIRECTION_FLAG);
3915 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'G_REPORT_HEADER.StartDate', G_REPORT_HEADER.StartDate);
3916 END IF;
3917
3918 --+
3919 -- Get back the status from each child.
3920 --+
3921 l_requests_tab := FND_CONCURRENT.GET_SUB_REQUESTS(FND_GLOBAL.CONC_REQUEST_ID);
3922
3923 l_counter := l_requests_tab.first;
3924
3925 WHILE (l_counter is not null) LOOP
3926
3927 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
3928 FTE_UTIL_PKG.Write_LogFile(l_module_name,'Status of sub-request ' || l_counter, l_requests_tab(l_counter).dev_status);
3929 END IF;
3930
3931 IF (l_requests_tab(l_counter).dev_status IN('ERROR','TERMINATED')) THEN
3932 x_error_msg := 'QP ERROR ' || substr(l_requests_tab(l_counter).message, 0, 300);
3933 x_status := 2;
3934 FTE_UTIL_PKG.Write_LogFile(p_module_name => l_module_name,
3935 p_message => x_error_msg);
3936
3937 FTE_UTIL_PKG.Exit_Debug(l_module_name);
3938 RETURN;
3939 END IF;
3940
3941 l_counter := l_requests_tab.next(l_counter);
3942
3943 END LOOP;
3944
3945 GET_PARAMETER_DEFAULTS(x_status => x_status,
3946 x_error_msg => x_error_msg);
3947
3948 IF (x_status <> -1) THEN
3949 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
3950 FTE_UTIL_PKG.Write_LogFile(l_module_name,'Return status from GET_PARAMETER_DEFAULTS ',x_status);
3951 END IF;
3952 FTE_UTIL_PKG.Exit_Debug(l_module_name);
3953 RETURN;
3954 END IF;
3955
3956 MANAGE_ZONES(p_load_id => p_load_id,
3957 p_tariff_name => p_tariff_name,
3958 p_origin_name => l_origin_name,
3959 x_status => x_status,
3960 x_error_msg => x_error_msg);
3961
3962 IF ( x_status <> -1 ) THEN
3963 FTE_UTIL_PKG.Write_LogFile(l_module_name,'Return status from MANAGE_ZONES', x_status);
3964 RETURN;
3965 END IF;
3966
3967 CREATE_ZONES_AND_REGIONS(p_load_id => p_load_id,
3968 p_origin_name => l_origin_name,
3969 x_status => x_status,
3970 x_error_msg => x_error_msg);
3971
3972 IF ( x_status <> -1 ) THEN
3973 FTE_UTIL_PKG.Write_LogFile(l_module_name,'Return status from CREATE_ZONES_AND_REGIONS', x_status);
3974 RETURN;
3975 END IF;
3976
3977 --+
3978 -- The rows in FTE_INTERFACE_ZONES with hash value of 0 are no longer needed
3979 -- because the information has been put into another row.
3980 --+
3981 DELETE FROM FTE_INTERFACE_ZONES WHERE hash_value = 0;
3982
3983 IF (l_number_of_loads = 1 and G_SERVICE_CODE = 'LTL') THEN
3984
3985 --+
3986 -- Obsolete pre-10+ loads.
3987 -- Previous lane type is : <direction_flag>_LTL_<scac>_<origin>_<number_of_loads>
3988 -- This should only be done only the first time we move from 10 to 10+
3989 --+
3990 FOR q IN 1..p_carrier_ids.COUNT LOOP
3991
3992 SELECT
3993 scac_code
3994 INTO
3995 l_scac
3996 FROM
3997 wsh_carriers
3998 WHERE
3999 carrier_id = p_carrier_ids(q);
4000
4001 OBSOLETE_PREVIOUS_LOAD (p_lane_type => G_DIRECTION_FLAG || '_LTL_' || l_scac || '_' || l_origin || '_%',
4002 p_delete_lanes => FALSE,
4003 x_status => x_status,
4004 x_error_msg => x_error_msg);
4005 END LOOP;
4006
4007 ELSE
4008
4009 --+
4010 -- Since the lane is being replaced by other lanes with the same dates,
4011 -- we need to delete the current lanes from the system.
4012 --+
4013 OBSOLETE_PREVIOUS_LOAD (p_lane_type => 'LTL_' || p_tariff_name || '_' || (l_number_of_loads-1),
4014 p_delete_lanes => TRUE,
4015 x_status => x_status,
4016 x_error_msg => x_error_msg);
4017 END IF;
4018
4019 CREATE_LANES(p_load_id => p_load_id,
4020 p_tariff_name => p_tariff_name,
4021 p_carrier_ids => p_carrier_ids,
4022 p_effective_dates => p_effective_dates,
4023 p_expiry_dates => p_expiry_dates,
4024 x_status => l_status,
4025 x_error_msg => l_error_msg);
4026
4027 --+
4028 -- Update the dates in fte_tariff_carriers
4029 --+
4030
4031 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
4032 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Update FTE_TARIFF_CARRIERS to complete process');
4033 END IF;
4034
4035 FORALL n IN 1..p_carrier_ids.COUNT
4036 UPDATE
4037 fte_tariff_carriers
4038 SET
4039 effective_date = to_date(p_effective_dates(n), G_DATE_FORMAT),
4040 expiry_date = to_date(p_expiry_dates(n), G_DATE_FORMAT),
4041 action_code = 'D',
4042 last_updated_by = G_USER_ID,
4043 last_update_date = SYSDATE,
4044 last_update_login = G_USER_ID
4045 WHERE
4046 tariff_name = p_tariff_name AND
4047 carrier_id = p_carrier_ids(n);
4048 END IF;
4049
4050 IF (x_status = -1) THEN
4051 COMMIT;
4052 ELSE
4053 ROLLBACK;
4054 END IF;
4055
4056 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4057
4058 EXCEPTION
4059
4060 WHEN OTHERS THEN
4061 x_status := 2;
4062 x_error_msg := SQLERRM;
4063 FTE_UTIL_PKG.Write_LogFile(l_module_name,'UNEXPECTED EEROR in' ,sqlerrm);
4064 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4065
4066 END LOAD_TEMP_TABLES;
4067
4068 --___________________________________________________________________________________--
4069 --
4070 -- PROCEDURE: INSERT_LTL_DATA
4071 --
4072 -- PURPOSE:
4073 -- Insert the read data into FTE_BULKLOAD_FILE.
4074 --
4075 -- PARAMETERS:
4076 -- IN
4077 -- p_load_id, the load id for the current job.
4078 --
4079 -- OUT
4080 -- x_status, the return status of the procedure,
4081 -- -1, success
4082 -- any other non negative value indicates failure.
4083 -- x_error_msg, the error message indicating the cause and detailing the error occured.
4084 --___________________________________________________________________________________--
4085
4086 PROCEDURE INSERT_LTL_DATA (p_load_id IN NUMBER,
4087 x_status OUT NOCOPY VARCHAR2,
4088 x_error_msg OUT NOCOPY VARCHAR2) IS
4089
4090 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.INSERT_LTL_DATA';
4091
4092 BEGIN
4093
4094 FTE_UTIL_PKG.Enter_Debug(l_module_name);
4095 x_status := -1;
4096
4097 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
4098 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Inserting ' || FL_ORIGIN_LOW.COUNT || ' rows into fte_bulkload_file.');
4099 END IF;
4100
4101 FORALL i in 1..FL_ORIGIN_LOW.COUNT
4102 INSERT INTO FTE_BULKLOAD_FILE ( LOAD_ID,
4103 ORIGIN_LOW,
4104 ORIGIN_HIGH,
4105 DEST_LOW,
4106 DEST_HIGH,
4107 CLASS,
4108 MIN_CHARGE1,
4109 L5C,
4110 M5C,
4111 M1M,
4112 M2M,
4113 M5M,
4114 M10M,
4115 M20M,
4116 M30M,
4117 M40M,
4118 EFFECTIVE_DATE,
4119 OUTBOUND_FLAG,
4120 MILEAGE)
4121 VALUES (P_LOAD_ID,
4122 FL_ORIGIN_LOW(i),
4123 FL_ORIGIN_HIGH(i),
4124 FL_DEST_LOW(i),
4125 FL_DEST_HIGH(i),
4126 FL_CLASS(i),
4127 FL_MIN_CHARGE1(i),
4128 FL_L5C(i),
4129 FL_M5C(i),
4130 FL_M1M(i),
4131 FL_M2M(i),
4132 FL_M5M(i),
4133 FL_M10M(i),
4134 FL_M20M(i),
4135 FL_M30M(i),
4136 FL_M40M(i),
4137 G_VALID_DATE,
4138 FL_OUTBOUND_FLAG(i),
4139 FL_MILEAGE(i));
4140
4141 FL_ORIGIN_LOW.DELETE;
4142 FL_ORIGIN_HIGH.DELETE;
4143 FL_DEST_LOW.DELETE;
4144 FL_DEST_HIGH.DELETE;
4145 FL_CLASS.DELETE;
4146 FL_MIN_CHARGE1.DELETE;
4147 FL_L5C.DELETE;
4148 FL_M5C.DELETE;
4149 FL_M1M.DELETE;
4150 FL_M2M.DELETE;
4151 FL_M5M.DELETE;
4152 FL_M10M.DELETE;
4153 FL_M20M.DELETE;
4154 FL_M30M.DELETE;
4155 FL_M40M.DELETE;
4156 FL_OUTBOUND_FLAG.DELETE;
4157 FL_MILEAGE.DELETE;
4158
4159 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4160 EXCEPTION
4161 WHEN OTHERS THEN
4162 x_status := 2;
4163 FTE_UTIL_PKG.Write_LogFile(l_module_name,'UNEXPECTED ERROR',sqlerrm);
4164 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4165 END INSERT_LTL_DATA;
4166
4167
4168 --______________________________________________________________________________________--
4169 --
4170 -- PROCEDURE: PROCESS_LTL_LINE
4171 --
4172 -- PURPOSE
4173 -- Populate the global arrays of form 'FL_%' with the appropriate
4174 -- values passed in 'p_ltl_line'
4175 --
4176 -- PARAMETERS
4177 -- IN
4178 -- p_ltl_line, A line of data from the LOB object.
4179 -- p_load_id, The load id of the bulkload job.
4180 --
4181 -- OUT
4182 -- x_status, the return status of the procedure,
4183 -- -1, success
4184 -- any other non negative value indicates failure.
4185 -- x_error_msg, the error message indicating the cause and detailing the error occured.
4186 --______________________________________________________________________________________--
4187
4188 PROCEDURE PROCESS_LTL_LINE (p_load_id IN VARCHAR2,
4189 p_ltl_line IN VARCHAR2,
4190 x_status OUT NOCOPY VARCHAR2,
4191 x_error_msg OUT NOCOPY VARCHAR2) IS
4192
4193 l_num NUMBER;
4194 l_table STRINGARRAY;
4195
4196 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.PROCESS_LTL_LINE';
4197
4198 BEGIN
4199
4200 FTE_UTIL_PKG.Enter_Debug(l_module_name);
4201 x_status := -1;
4202
4203 IF ( p_ltl_line IS NULL OR p_ltl_line = '' ) THEN
4204 x_status := 2;
4205 x_error_msg := 'Returning from PROCESS_LTL_LINE as p_ltl_line';
4206 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
4207 FTE_UTIL_PKG.Write_LogFile(l_module_name,x_error_msg, p_ltl_line);
4208 END IF;
4209 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4210 RETURN;
4211 END IF;
4212
4213 l_table := FTE_UTIL_PKG.TOKENIZE_STRING(p_ltl_line, ',');
4214
4215 IF (l_table.COUNT <> G_NUM_COLUMNS) THEN
4216 x_status := 2;
4217 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_INVALID_FILE_FORMAT');
4218 FTE_UTIL_PKG.Write_OutFile( p_msg => x_error_msg,
4219 p_module_name => l_module_name,
4220 p_category => 'D',
4221 p_line_number => G_PROCESSED_LINES);
4222 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4223 RETURN;
4224 END IF;
4225
4226 --+
4227 -- LTL FileFormat for reference:
4228 -- ORIGIN_LOW(1), ORIGIN_HIGH(2), DEST_LOW(3), DEST_HIGH(4),
4229 -- FREIGHT_CLASS(5), MINIMUM_CHARGE(6), L5C(7), M5C(8), M1M(9), M2M(10), M5M(11), M10M(12), M20M(13), M30M(14), M40M(15),
4230 -- VALID_DATE(16), DIRECTION(17), MILEAGE(18)
4231 --+
4232
4233 --+
4234 -- Initialize variables if this is the first line
4235 --+
4236 IF (G_PROCESSED_LINES = 0) THEN
4237 G_VALID_DATE := to_date(l_table(16), 'rrrrmmdd');
4238 G_VALID_DATE_STRING := l_table(16);
4239 G_DIRECTION_FLAG := upper(l_table(17));
4240 END IF;
4241
4242 l_num := FL_ORIGIN_LOW.count + 1;
4243
4244 IF (UPPER(G_DIRECTION_FLAG) = 'O') THEN
4245 --+
4246 -- OUTBOUND
4247 -- Origin is the same for all rows. Destination is processed into zones.
4248 --+
4249 G_ORIGIN_DEST := 'destinations';
4250 G_IN_OUT := 'OUTBOUND';
4251
4252 FL_ORIGIN_LOW(l_num) := l_table(1);
4253 FL_ORIGIN_HIGH(l_num) := l_table(2);
4254 FL_DEST_LOW(l_num) := l_table(3);
4255 FL_DEST_HIGH(l_num) := l_table(4);
4256
4257 ELSIF (UPPER(G_DIRECTION_FLAG) = 'I') THEN
4258 --+
4259 -- INBOUND
4260 -- Destination is the same for all rows.
4261 -- For the purpose of processing,
4262 -- we will switch the origin and destination and treat is an inbound problem.
4263 --+
4264 G_ORIGIN_DEST := 'origins';
4265 G_IN_OUT := 'INBOUND';
4266
4267 FL_DEST_LOW(l_num) := l_table(1);
4268 FL_DEST_HIGH(l_num) := l_table(2);
4269 FL_ORIGIN_LOW(l_num) := l_table(3);
4270 FL_ORIGIN_HIGH(l_num) := l_table(4);
4271
4272 ELSE
4273 x_status := 2;
4274 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_INVALID_DIRECTION_FLAG');
4275 FTE_UTIL_PKG.Write_OutFile(p_msg => x_error_msg,
4276 p_module_name => l_module_name,
4277 p_category => 'D',
4278 p_line_number => G_PROCESSED_LINES);
4279 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4280 RETURN;
4281 END IF;
4282
4283 FL_CLASS(l_num) := l_table(5);
4284 FL_MIN_CHARGE1(l_num) := l_table(6);
4285 FL_L5C(l_num) := l_table(7);
4286 FL_M5C(l_num) := l_table(8);
4287 FL_M1M(l_num) := l_table(9);
4288 FL_M2M(l_num) := l_table(10);
4289 FL_M5M(l_num) := l_table(11);
4290 FL_M10M(l_num) := l_table(12);
4291 FL_M20M(l_num) := l_table(13);
4292 FL_M30M(l_num) := l_table(14);
4293 FL_M40M(l_num) := l_table(15);
4294 FL_OUTBOUND_FLAG(l_num) := l_table(17);
4295 FL_MILEAGE(l_num) := l_table(18);
4296
4297 IF (FL_ORIGIN_LOW.COUNT = G_BULK_INSERT_LIMIT) THEN
4298
4299 INSERT_LTL_DATA(p_load_id => p_load_id,
4300 x_status => x_status,
4301 x_error_msg => x_error_msg);
4302
4303 IF (x_status <> -1) THEN
4304
4305 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
4306 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'INSERT_LTL_DATA returned with ERROR');
4307 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Return status ', x_status);
4308 END IF;
4309 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4310 RETURN;
4311
4312 END IF;
4313
4314 END IF;
4315
4316 G_PROCESSED_LINES := G_PROCESSED_LINES + 1;
4317
4318 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4319
4320 EXCEPTION
4321 WHEN SUBSCRIPT_BEYOND_COUNT THEN
4322
4323 x_status := 2;
4324 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_WRONG_FILE_FORMAT');
4325 FTE_UTIL_PKG.Write_OutFile(p_msg => x_error_msg,
4326 p_module_name => l_module_name,
4327 p_category => 'D',
4328 p_line_number => 0);
4329 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4330
4331 WHEN VALUE_ERROR THEN
4332
4333 x_status := 2;
4334 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_VALUE_ERROR');
4335 FTE_UTIL_PKG.Write_OutFile(p_msg => x_error_msg,
4336 p_module_name => l_module_name,
4337 p_category => 'D',
4338 p_line_number => 0);
4339 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4340
4341 WHEN OTHERS THEN
4342 x_status := 2;
4343 x_error_msg := sqlerrm;
4344 FTE_UTIL_PKG.Write_LogFile(l_module_name, sqlerrm);
4345 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4346 RAISE;
4347
4348 END PROCESS_LTL_LINE;
4349
4350 --___________________________________________________________________________________--
4351 --
4352 -- PROCEDURE: READ_LTL_FILE_FROM_TABLE
4353 --
4354 -- PURPOSE
4355 -- Read the LTL data file from the table FTE_BULKLOAD_DATA and insert the
4356 -- contents in FTE_BULKLOAD_FILE.
4357 --
4358 -- PARAMETERS
4359 -- IN
4360 -- 1. p_file_name: The filename of the data file.
4361 -- 2. p_load_id: The load id of the bulkload job.
4362 --
4363 -- OUT
4364 -- 1. x_status: 2 ==> Error during file reading process.
4365 -- -1 ==> Completed successfully.
4366 --___________________________________________________________________________________--
4367
4368 PROCEDURE READ_LTL_FILE_FROM_TABLE (p_file_name IN VARCHAR2,
4369 p_load_id IN VARCHAR2,
4370 x_status OUT NOCOPY NUMBER,
4371 x_error_msg OUT NOCOPY VARCHAR2) IS
4372
4373 l_carriage_return VARCHAR2(1) := Fnd_Global.Local_Chr(13);
4374 l_linefeed VARCHAR2(1) := Fnd_Global.Local_Chr(10);
4375 l_size NUMBER;
4376 l_content BLOB;
4377 l_amount BINARY_INTEGER := 12000;
4378 l_position INTEGER := 1;
4379 data_buffer VARCHAR2(32000);
4380 temp_buffer VARCHAR2(32000);
4381 l_lines STRINGARRAY;
4382
4383 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.READ_LTL_FILE_FROM_TABLE';
4384
4385 BEGIN
4386 FTE_UTIL_PKG.Enter_Debug(l_module_name);
4387 x_status := -1;
4388
4389 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
4390 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'File name', p_file_name);
4391 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Load Id', p_load_id);
4392 END IF;
4393
4394 SELECT
4395 content
4396 INTO
4397 l_content
4398 FROM
4399 fte_bulkload_data
4400 WHERE
4401 file_name = p_file_name and
4402 load_id = p_load_id;
4403
4404 l_size := dbms_lob.getlength(l_content);
4405 data_buffer := NULL;
4406
4407 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
4408 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'File size', l_size);
4409 END IF;
4410
4411 WHILE l_size > 0 LOOP
4412
4413 -- read a big chunk at a time:
4414 dbms_lob.read (l_content, l_amount, l_position, temp_buffer);
4415 data_buffer := data_buffer || utl_raw.cast_to_varchar2(temp_buffer);
4416 data_buffer := replace(data_buffer, l_carriage_return, ''); -- dos2unix conversion
4417
4418 -- Now tokenize by linefeed
4419 l_lines := FTE_UTIL_PKG.TOKENIZE_STRING(data_buffer, l_linefeed);
4420
4421 FOR k IN 1..l_lines.COUNT-1 LOOP
4422
4423 PROCESS_LTL_LINE (p_load_id => p_load_id,
4424 p_ltl_line => l_lines(k),
4425 x_error_msg => x_error_msg,
4426 x_status => x_status);
4427
4428 IF (x_status <> -1) THEN
4429 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4430 RETURN;
4431 END IF;
4432
4433 END LOOP;
4434
4435 l_position := l_position + l_amount;
4436 l_size := l_size - l_amount;
4437 --+
4438 -- Append the last remaining to the next chunk because it might not be complete
4439 --+
4440 data_buffer := l_lines(l_lines.COUNT);
4441 END LOOP;
4442
4443 IF (data_buffer IS NOT NULL) THEN
4444
4445 PROCESS_LTL_LINE(p_load_id => p_load_id,
4446 p_ltl_line => data_buffer,
4447 x_error_msg => x_error_msg,
4448 x_status => x_status);
4449
4450 IF (x_status <> -1) THEN
4451 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4452 return;
4453 END IF;
4454
4455 END IF;
4456
4457 --+
4458 -- Insert the last set of lines
4459 --+
4460 INSERT_LTL_DATA(p_load_id => p_load_id,
4461 x_status => x_status,
4462 x_error_msg => x_error_msg);
4463
4464 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
4465 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Finished Reading File.');
4466 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Number of lines processed ', g_processed_lines);
4467 END IF;
4468
4469 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4470
4471 EXCEPTION
4472 WHEN OTHERS THEN
4473 x_status := 2;
4474 x_error_msg := SQLERRM;
4475 FTE_UTIL_PKG.Write_LogFile(l_module_name,'Unexpected error while reading file: [Row ' || g_processed_lines || '].'
4476 || fnd_global.newline || sqlerrm);
4477 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4478
4479 END READ_LTL_FILE_FROM_TABLE;
4480
4481
4482 --___________________________________________________________________________________--
4483 --
4484 -- PROCEDURE: READ_LTL_FILE_FROM_DIR
4485 --
4486 -- Purpose:
4487 -- Read the LTL data file and insert the contents in FTE_BULKLOAD_FILE.
4488 --
4489 -- IN Parameters
4490 -- 1. p_file_name: The filename of the data file.
4491 -- 2. p_load_id: The load id of the bulkload job.
4492 --
4493 -- OUT Parameters:
4494 -- 1. x_status: 0 ==> File not found
4495 -- 2 ==> Error during file reading process.
4496 -- -1 ==> Completed successfully.
4497 --____________________________________________________________________________________--
4498
4499 PROCEDURE READ_LTL_FILE_FROM_DIR (p_file_name IN VARCHAR2,
4500 p_load_id IN VARCHAR2,
4501 x_error_msg OUT NOCOPY VARCHAR2,
4502 x_status OUT NOCOPY NUMBER) IS
4503
4504 l_chart_file UTL_FILE.file_type;
4505 l_src_file_dir VARCHAR2(500);
4506 l_carriage_return VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(13);
4507 l_line VARCHAR2(1000);
4508 isFileEmpty VARCHAR2(1) := 'Y';
4509
4510 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.READ_LTL_FILE_FROM_DIR';
4511
4512 BEGIN
4513 FTE_UTIL_PKG.Enter_Debug(l_module_name);
4514 x_status := -1;
4515
4516 l_src_file_dir := FTE_BULKLOAD_PKG.GET_UPLOAD_DIR;
4517
4518 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
4519 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_src_file_dir ',l_src_file_dir);
4520 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_file_name ',p_file_name);
4521 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'G_BULK_INSERT_LIMIT', G_BULK_INSERT_LIMIT);
4522 END IF;
4523
4524 l_chart_file := UTL_FILE.FOPEN(l_src_file_dir, p_file_name, 'R');
4525
4526 LOOP
4527 UTL_FILE.GET_LINE(l_chart_file, l_line);
4528
4529 l_line := replace(l_line, l_carriage_return, ''); -- dos2unix conversion
4530
4531 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
4532 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_line', l_line);
4533 END IF;
4534
4535 PROCESS_LTL_LINE(p_load_id => p_load_id,
4536 p_ltl_line => l_line,
4537 x_error_msg => x_error_msg,
4538 x_status => x_status);
4539
4540 isFileEmpty := 'N';
4541
4542 IF (x_status <> -1) THEN
4543 FTE_UTIL_PKG.Write_LogFile(l_module_name,'PROCESS_LTL_LINE returned with ERROR ' || l_line , sqlerrm);
4544 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4545 RETURN;
4546 END IF;
4547 END LOOP;
4548
4549 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4550
4551 EXCEPTION
4552 WHEN NO_DATA_FOUND THEN
4553 UTL_FILE.FCLOSE(l_chart_file);
4554 IF(isFileEmpty = 'N') THEN
4555 --
4556 -- Insert the last set of lines
4557 --
4558 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
4559 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Inserting last set of lines :-)');
4560 END IF;
4561
4562 INSERT_LTL_DATA(p_load_id => p_load_id,
4563 x_status => x_status,
4564 x_error_msg => x_error_msg);
4565
4566 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
4567 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Finished Reading File.');
4568 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Number of lines processed: ' || g_processed_lines);
4569 END IF;
4570 x_status := -1;
4571 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4572
4573 ELSIF(isFileEmpty = 'Y') THEN
4574 x_status := 2;
4575 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_WRONG_FILE_FORMAT');
4576 FTE_UTIL_PKG.Write_OutFile( p_msg => x_error_msg,
4577 p_module_name => l_module_name,
4578 p_category => 'E',
4579 p_line_number => 0);
4580 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4581 END IF;
4582
4583 WHEN UTL_FILE.INVALID_PATH THEN
4584
4585 x_status := 2;
4586 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_INVALID_PATH');
4587 FTE_UTIL_PKG.Write_OutFile( p_msg => x_error_msg,
4588 p_module_name => l_module_name,
4589 p_category => 'E',
4590 p_line_number => 0);
4591 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4592
4593 WHEN UTL_FILE.INVALID_OPERATION THEN
4594
4595 x_status := 2;
4596 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_INVALID_FILE_OPERATION');
4597 FTE_UTIL_PKG.Write_OutFile( p_msg => x_error_msg,
4598 p_module_name => l_module_name,
4599 p_category => 'E',
4600 p_line_number => 0);
4601 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4602
4603 WHEN OTHERS THEN
4604 x_status := 2;
4605 x_error_msg := SQLERRM;
4606 FTE_UTIL_PKG.Write_LogFile(l_module_name,'UNEXPECTED ERROR while reading file: [Row ' || g_processed_lines || '].'
4607 || fnd_global.newline || l_line || fnd_global.newline || sqlerrm);
4608 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4609
4610 END READ_LTL_FILE_FROM_DIR;
4611
4612 --___________________________________________________________________________________--
4613 --
4614 -- PROCEDURE: UPDATE_TARIFF_LANES
4615 --
4616 -- PURPOSE
4617 -- Associates a carrier to a tariff by creating new lanes for that carrier
4618 -- OR updates the dates on lanes for an existing carrier.
4619 --
4620 -- PARAMETERS
4621 -- IN
4622 -- 1. p_tariff_name:
4623 -- 2. p_load_id: The load id of the bulkload job.
4624 --
4625 -- OUT
4626 -- 1. x_error_msg: A buffer of error messages.
4627 -- 2. p_retcode: The return code. A return code of '2' specifies ERROR.
4628 --___________________________________________________________________________________--
4629
4630 PROCEDURE UPDATE_TARIFF_LANES(p_tariff_name IN VARCHAR2,
4631 p_load_id IN NUMBER,
4632 x_abort OUT NOCOPY BOOLEAN,
4633 x_status OUT NOCOPY NUMBER,
4634 x_error_msg OUT NOCOPY VARCHAR2) IS
4635
4636 CURSOR GET_TARIFF_LANES(p_load_number IN NUMBER, p_carrier_id IN NUMBER) IS
4637 SELECT
4638 l.lane_id,
4639 l.origin_id,
4640 l.destination_id,
4641 lrc.list_header_id,
4642 lrc.start_date_active,
4643 prc.value_from
4644 FROM
4645 fte_lanes l,
4646 fte_lane_rate_charts lrc,
4647 fte_prc_parameters prc
4648 WHERE
4649 l.tariff_name = p_tariff_name AND
4650 l.LANE_ID = lrc.LANE_ID AND
4651 prc.LANE_ID = l.LANE_ID AND
4652 prc.LANE_ID = lrc.LANE_ID AND
4653 prc.PARAMETER_ID = g_min_charge_id AND
4654 mode_of_transportation_code = 'LTL' AND
4655 l.lane_type = 'LTL_' || p_tariff_name || '_' || p_load_number AND
4656 l.carrier_id = p_carrier_id;
4657
4658 CURSOR GET_LANE_COMMODITIES (p_lane_id IN NUMBER) IS
4659 SELECT
4660 commodity_catg_id
4661 FROM
4662 fte_lane_commodities
4663 WHERE
4664 lane_id = p_lane_id;
4665
4666 CURSOR GET_PREVIOUS_REGIONS_INFO (p_tariff_name IN VARCHAR2,
4667 p_lane_type IN VARCHAR2) IS
4668 SELECT
4669 DECODE (COUNT(DISTINCT origin_id), 1, 'O', 'I') direction,
4670 MAX(ozr.postal_code_from) origin_low,
4671 MAX(dzr.postal_code_from) destination_low
4672 FROM
4673 fte_lanes l,
4674 wsh_zone_regions ozr,
4675 wsh_zone_regions dzr
4676 WHERE
4677 l.lane_type = p_lane_type AND
4678 l.tariff_name = p_tariff_name AND
4679 ozr.parent_region_id = l.origin_id AND
4680 dzr.parent_region_id = l.destination_id;
4681
4682 l_load_number NUMBER;
4683 l_lane_ids NUMBER_TAB;
4684 l_origin_ids NUMBER_Tab;
4685 l_destination_ids NUMBER_TAB;
4686 l_catg_ids NUMBER_TAB;
4687 l_min_charges NUMBER_TAB;
4688 l_list_header_ids NUMBER_TAB;
4689 l_existing_carrier NUMBER;
4690 l_scac VARCHAR2(30);
4691 l_origin VARCHAR2(10);
4692 l_origin_low VARCHAR2(10);
4693 l_dest_low VARCHAR2(10);
4694 l_rc_start_dates STRINGARRAY;
4695
4696 -- New carriers being added
4697 l_add_carriers NUMBER_TAB;
4698 l_add_start_dates STRINGARRAY;
4699 l_add_end_dates STRINGARRAY;
4700
4701 -- Carriers being modified
4702 l_modified_carriers NUMBER_TAB;
4703 l_mod_start_dates STRINGARRAY;
4704 l_mod_end_dates STRINGARRAY;
4705 l_current_fetch NUMBER;
4706 l_fetch_total NUMBER;
4707
4708 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.UPDATE_TARIFF_LANES';
4709
4710 BEGIN
4711 FTE_UTIL_PKG.Enter_Debug(l_module_name);
4712 x_status := -1;
4713 x_abort := FALSE;
4714
4715 -- SECTION: Create New Lanes
4716 -- Get the carriers that need new lanes created.
4717 OPEN GET_TARIFF_CARRIERS(p_tariff_name => p_tariff_name,
4718 p_action_code => 'N');
4719
4720 FETCH GET_TARIFF_CARRIERS
4721 BULK COLLECT INTO l_add_carriers,
4722 l_add_start_dates,
4723 l_add_end_dates;
4724 CLOSE GET_TARIFF_CARRIERS;
4725
4726 IF (l_add_carriers.COUNT <= 0) THEN
4727
4728 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
4729 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'No new carriers to add');
4730 END IF;
4731
4732 OPEN GET_LOAD_NUMBER(p_tariff_name => p_tariff_name);
4733
4734 FETCH GET_LOAD_NUMBER INTO l_load_number;
4735
4736 IF (GET_LOAD_NUMBER%NOTFOUND) THEN
4737 x_error_msg := 'Error updating LTL rates. Load Number does not exist.';
4738 FTE_UTIL_PKG.Write_LogFile(p_module_name => l_module_name,
4739 p_message => x_error_msg);
4740
4741 x_status := 2;
4742 CLOSE GET_LOAD_NUMBER;
4743 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4744 RETURN;
4745 END IF;
4746
4747 CLOSE GET_LOAD_NUMBER;
4748 ELSE
4749 -- Validate the service level for each carrier.
4750 FOR i IN 1..l_add_carriers.COUNT LOOP
4751 G_SERVICE_CODE := FTE_VALIDATION_PKG.Validate_Service_Level(p_carrier_id => l_add_carriers(i),
4752 p_carrier_name => NULL,
4753 p_service_level => G_SERVICE_CODE,
4754 p_line_number => 0,
4755 x_error_msg => x_error_msg,
4756 x_status => x_status);
4757 IF (x_status <> -1) THEN
4758 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4759 RETURN;
4760 END IF;
4761 END LOOP;
4762
4763 --+
4764 -- Get information from a previous LTL load for this tariff
4765 --+
4766 OPEN GET_PREVIOUS_LOAD_INFO(p_tariff_name => p_tariff_name);
4767 FETCH GET_PREVIOUS_LOAD_INFO INTO l_load_number,
4768 G_SERVICE_CODE,
4769 g_orig_country,
4770 g_dest_country,
4771 l_existing_carrier;
4772
4773 IF (GET_PREVIOUS_LOAD_INFO%NOTFOUND) THEN
4774 x_error_msg := 'Error updating LTL rates. Previous load does not exist.';
4775 FTE_UTIL_PKG.Write_LogFile(p_module_name => l_module_name,
4776 p_message => x_error_msg);
4777
4778 x_status := 2;
4779 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4780 CLOSE GET_PREVIOUS_LOAD_INFO;
4781 RETURN;
4782 END IF;
4783
4784 CLOSE GET_PREVIOUS_LOAD_INFO;
4785
4786 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
4787 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Service Code ', G_SERVICE_CODE);
4788 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Origin Country Code ', g_orig_country);
4789 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Destination Country Code', g_dest_country);
4790 END IF;
4791
4792 Get_Parameter_Defaults(x_status => x_status,
4793 x_error_msg => x_error_msg);
4794
4795 IF (x_status <> -1) THEN
4796 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4797 RETURN;
4798 END IF;
4799
4800 --+
4801 -- Get a representative set of lane data from the existing
4802 -- carrier. The new lanes created will be duplicates of this
4803 -- set of lanes.
4804 --+
4805 OPEN Get_Tariff_Lanes (p_load_number => l_load_number,
4806 p_carrier_id => l_existing_carrier);
4807 LOOP
4808 l_fetch_total := Get_Tariff_lanes%ROWCOUNT ;
4809
4810 FETCH GET_TARIFF_LANES
4811 BULK COLLECT INTO l_lane_ids,
4812 l_origin_ids,
4813 l_destination_ids,
4814 l_list_header_ids,
4815 l_rc_start_dates,
4816 l_min_charges LIMIT 1000;
4817
4818 l_current_fetch := Get_Tariff_Lanes%ROWCOUNT - l_fetch_total;
4819
4820 EXIT WHEN (l_current_fetch <= 0);
4821
4822 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
4823 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Creating ' || l_lane_ids.COUNT || ' new lanes for carriers');
4824 END IF;
4825
4826 IF (l_rc_start_dates.COUNT > 0) THEN
4827 g_valid_date := l_rc_start_dates(1);
4828 END IF;
4829
4830 FOR k IN 1..l_lane_ids.COUNT LOOP
4831 OPEN Get_Lane_Commodities (p_lane_id => l_lane_ids(k));
4832 FETCH Get_Lane_Commodities
4833 BULK COLLECT INTO l_catg_ids;
4834 CLOSE Get_Lane_Commodities;
4835
4836 -- the dates passed into here should be from fte_lane_rate_charts --nii
4837 Create_Lane_Data (p_origin_id => l_origin_ids(k),
4838 p_destination_id => l_destination_ids(k),
4839 p_carrier_ids => l_add_carriers,
4840 p_effective_dates => l_add_start_dates,
4841 p_expiry_dates => l_add_end_dates,
4842 p_tariff_name => p_tariff_name,
4843 p_lane_type => 'LTL_' || p_tariff_name || '_' || l_load_number,
4844 p_category_ids => l_catg_ids,
4845 p_list_header_id => l_list_header_ids(k),
4846 p_min_charge => l_min_charges(k),
4847 x_status => x_status,
4848 x_error_msg => x_error_msg);
4849
4850 IF x_status <> -1 THEN
4851 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4852 RETURN;
4853 END IF;
4854 END LOOP;
4855 END LOOP;
4856 CLOSE GET_TARIFF_LANES;
4857
4858
4859 -- Update the dates in fte_tariff_carriers
4860 FORALL p IN 1..l_add_carriers.COUNT
4861 UPDATE
4862 fte_tariff_carriers
4863 SET
4864 effective_date = l_add_start_dates(p),
4865 expiry_date = l_add_end_dates(p),
4866 action_code = 'D',
4867 last_updated_by = G_USER_ID,
4868 last_update_date = SYSDATE,
4869 last_update_login = G_USER_ID
4870 WHERE
4871 tariff_name = p_tariff_name AND
4872 action_code = 'N' AND
4873 carrier_id = l_add_carriers(p);
4874
4875 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
4876 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Calling Add_Carriers_To_RateCharts');
4877 END IF;
4878
4879 ADD_CARRIERS_TO_RATECHARTS(p_tariff_name => p_tariff_name,
4880 p_load_number => l_load_number,
4881 p_carrier_ids => l_add_carriers,
4882 p_load_id => p_load_id,
4883 x_error_msg => x_error_msg,
4884 x_status => x_status);
4885
4886 IF x_status <> -1 THEN
4887 FTE_UTIL_PKG.Exit_Debug(l_module_name);
4888 return;
4889 END IF;
4890
4891 END IF;
4892
4893 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
4894 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Bulk Insert Lane Data');
4895 END IF;
4896
4897 Bulk_Insert_Lanes;
4898 Bulk_Insert_Lane_Rate_Charts;
4899 Bulk_Insert_Lane_Parameters;
4900 Bulk_Insert_Lane_Commodities;
4901
4902 -- Obsolete pre-10+ lanes for these carriers
4903 IF (G_SERVICE_CODE = 'LTL') THEN
4904 FOR q IN 1..l_add_carriers.COUNT LOOP
4905 SELECT scac_code INTO l_scac
4906 FROM wsh_carriers
4907 WHERE carrier_id = l_add_carriers(q);
4908
4909 OPEN Get_Previous_Regions_Info (p_tariff_name => p_tariff_name,
4910 p_lane_type => 'LTL_' || p_tariff_name || '_' || l_load_number);
4911
4912 FETCH Get_Previous_Regions_Info
4913 INTO g_direction_flag,
4914 l_origin_low,
4915 l_dest_low;
4916
4917 CLOSE Get_Previous_Regions_Info;
4918
4919 IF (g_direction_flag = 'I') THEN
4920 l_origin := l_dest_low;
4921 ELSE
4922 l_origin := l_origin_low;
4923 END IF;
4924
4925 OBSOLETE_PREVIOUS_LOAD (p_lane_type => g_direction_flag||'_LTL_'||l_scac||'_'||l_origin||'_%',
4926 p_delete_lanes => FALSE,
4927 x_status => x_status,
4928 x_error_msg => x_error_msg);
4929 END LOOP;
4930 END IF;
4931
4932 --+
4933 -- Update
4934 -- Get the carriers that have been modified
4935 --+
4936 OPEN GET_TARIFF_CARRIERS(p_tariff_name => p_tariff_name,
4937 p_action_code => 'M');
4938
4939 FETCH GET_TARIFF_CARRIERS
4940 BULK COLLECT INTO l_modified_carriers,
4941 l_mod_start_dates,
4942 l_mod_end_dates;
4943
4944 CLOSE GET_TARIFF_CARRIERS;
4945
4946 IF (l_modified_carriers.COUNT <= 0) THEN
4947 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'No carriers to modify');
4948 ELSE
4949 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Updating Tariff Lanes for ' || l_modified_carriers.COUNT || ' existing carrier(s)');
4950 END IF;
4951
4952 IF(FTE_BULKLOAD_PKG.g_debug_on)THEN
4953 FOR lt IN 1..l_modified_carriers.COUNT loop
4954 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'carrier ' || lt , l_modified_carriers(lt));
4955 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'start date ' || lt , l_mod_start_dates(lt));
4956 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'end date ' || lt , l_mod_end_dates(lt));
4957 END LOOP;
4958 END IF;
4959
4960 IF l_modified_carriers.COUNT > 0 THEN
4961 FORALL n IN 1..l_modified_carriers.COUNT
4962 UPDATE
4963 fte_lanes
4964 SET
4965 effective_date = l_mod_start_dates(n),
4966 expiry_date = l_mod_end_dates(n),
4967 last_updated_by = G_USER_ID,
4968 last_update_date = SYSDATE,
4969 last_update_login = G_USER_ID
4970 WHERE
4971 tariff_name = p_tariff_name AND
4972 lane_type = 'LTL_' || p_tariff_name || '_' || l_load_number AND
4973 carrier_id = l_modified_carriers(n);
4974
4975 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
4976 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Update Tariff Carriers Table');
4977 END IF;
4978
4979 --+
4980 -- Update the dates in fte_tariff_carriers
4981 --+
4982 FORALL n IN 1..l_modified_carriers.COUNT
4983 UPDATE
4984 fte_tariff_carriers
4985 SET
4986 effective_date = l_mod_start_dates(n),
4987 expiry_date = l_mod_end_dates(n),
4988 action_code = 'D',
4989 last_updated_by = G_USER_ID,
4990 last_update_date = SYSDATE,
4991 last_update_login = G_USER_ID
4992 WHERE
4993 tariff_name = p_tariff_name AND
4994 action_code = 'M' AND
4995 carrier_id = l_modified_carriers(n);
4996
4997 -- FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Update Lane Rate Charts');
4998 -- update fte_lane_rate_charts:
4999 -- FORALL n IN 1..l_modified_carriers.COUNT
5000 -- UPDATE fte_lane_rate_charts
5001 -- SET start_date_active = l_mod_start_dates(n),
5002 -- end_date_active = l_mod_end_dates(n),
5003 -- last_update_date = sysdate
5004 -- WHERE lane_id IN
5005 -- (select lane_id from fte_lanes
5006 -- where lane_type = 'LTL_' || p_tariff_name || '_' || l_load_number
5007 -- and carrier_id = l_modified_carriers(n)
5008 -- and tariff_name = p_tariff_name);
5009 END IF;
5010
5011 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5012 EXCEPTION
5013 WHEN OTHERS THEN
5014
5015 IF (GET_PREVIOUS_LOAD_INFO%ISOPEN) THEN
5016 CLOSE GET_PREVIOUS_LOAD_INFO;
5017 END IF;
5018
5019 x_status := 2;
5020 x_error_msg := SQLERRM;
5021 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR' || sqlerrm);
5022 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5023
5024 END UPDATE_TARIFF_LANES;
5025
5026 --______________________________________________________________________________________--
5027 --
5028 -- PROCEDURE: UPLOAD_LTL_RATES
5029 --
5030 -- PURPOSE Starts the rate chart and lane loading process.
5031 --
5032 -- PARAMETERS
5033 -- IN
5034 -- 1. p_file_name The file name
5035 -- 2. p_load_id The load ID for this load.
5036 -- 3. p_tariff_name tariff_name
5037 -- 4. x_phase the current phase, to see what is a phase please refer to get_phase function.
5038 -- 5. x_abort
5039 --
5040 -- OUT
5041 -- 1. x_status Completion status. Success ==> -1, Failure otherwise.
5042 -- 2. x_error_msg Error message, if there is an error.
5043 --______________________________________________________________________________________--
5044
5045 PROCEDURE UPLOAD_LTL_RATES(p_file_name IN VARCHAR2,
5046 p_load_id IN VARCHAR2,
5047 p_tariff_name IN VARCHAR2,
5048 p_action_code IN VARCHAR2,
5049 x_phase OUT NOCOPY NUMBER,
5050 x_abort OUT NOCOPY BOOLEAN,
5051 x_status OUT NOCOPY NUMBER,
5052 x_error_msg OUT NOCOPY VARCHAR2) IS
5053
5054 l_source VARCHAR2(30);
5055 l_load_number NUMBER;
5056 l_carrier_ids Number_Tab;
5057 l_effective_dates STRINGARRAY;
5058 l_expiry_dates STRINGARRAY;
5059
5060 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.UPLOAD_LTL_RATES';
5061
5062 BEGIN
5063 FTE_UTIL_PKG.Enter_Debug(l_module_name);
5064
5065 --+
5066 -- Identify the phase we are in.
5067 -- Phase 1: rate charts and zones are prepared in the interface tables.
5068 -- Phase 2: lanes are created and linked to the rate charts.
5069 --+
5070 x_phase := GET_PHASE;
5071 x_abort := FALSE;
5072 x_status := -1;
5073
5074 FND_PROFILE.GET('FTE_BULKLOAD_SOURCE_TYPE', l_source);
5075
5076 IF( FTE_BULKLOAD_PKG.g_debug_on ) THEN
5077 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Phase ' || x_phase);
5078 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Reading from ' || l_source);
5079 END IF;
5080
5081 IF (x_phase = 1) THEN
5082 IF (upper(l_source) = 'SERVER') THEN
5083 READ_LTL_FILE_FROM_DIR(p_file_name => p_file_name,
5084 p_load_id => p_load_id,
5085 x_error_msg => x_error_msg,
5086 x_status => x_status);
5087 ELSE
5088 READ_LTL_FILE_FROM_TABLE(p_file_name => p_file_name,
5089 p_load_id => p_load_id,
5090 x_error_msg => x_error_msg,
5091 x_status => x_status);
5092 END IF;
5093 END IF;
5094
5095 IF (x_status = -1) THEN
5096
5097 IF (p_action_code = 'ADD') THEN
5098
5099 OPEN GET_TARIFF_CARRIERS(p_tariff_name => p_tariff_name,
5100 p_action_code => 'N');
5101 ELSIF (p_action_code = 'UPDATE') THEN
5102
5103 OPEN GET_TARIFF_CARRIERS(p_tariff_name => p_tariff_name,
5104 p_action_code => 'M');
5105 END IF;
5106
5107 FETCH GET_TARIFF_CARRIERS
5108 BULK COLLECT INTO l_carrier_ids, l_effective_dates, l_expiry_dates;
5109 CLOSE GET_TARIFF_CARRIERS;
5110
5111 IF (l_carrier_ids.COUNT <= 0) THEN
5112 x_status := 2;
5113 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CARRIERS_NOT_FOUND');
5114 FTE_UTIL_PKG.Write_OutFile( p_msg => x_error_msg,
5115 p_module_name => l_module_name,
5116 p_category => 'C',
5117 p_line_number => 0);
5118 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5119 RETURN;
5120 ELSE
5121 IF(FTE_BULKLOAD_PKG.g_debug_on) THEN
5122 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Fetched ' || l_carrier_ids.COUNT || ' carrier(s) to ' || p_action_code);
5123 END IF;
5124 END IF;
5125
5126 LOAD_TEMP_TABLES(p_load_id => p_load_id,
5127 p_tariff_name => p_tariff_name,
5128 p_carrier_ids => l_carrier_ids,
5129 p_effective_dates => l_effective_dates,
5130 p_expiry_dates => l_expiry_dates,
5131 x_status => x_status,
5132 x_error_msg => x_error_msg);
5133
5134 IF(FTE_BULKLOAD_PKG.g_debug_on) THEN
5135 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Returned from LOAD_TEMP_TABLES with status ', x_status);
5136 END IF;
5137 ELSE
5138 -- Error while reading file.
5139 x_status := 2;
5140 x_error_msg := 'LTL Loading Failed While Reading File. ' || x_error_msg || '. Please check logs for details.';
5141 x_abort := TRUE;
5142 END IF;
5143
5144 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5145
5146 EXCEPTION
5147
5148 WHEN OTHERS THEN
5149 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR ', sqlerrm);
5150 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5151 RAISE;
5152
5153 END UPLOAD_LTL_RATES;
5154
5155 --____________________________________________________________________________________--
5156 --
5157 -- PROCEDURE: PROCESS_LTL_DATA
5158 --
5159 -- PURPOSE
5160 -- This has been registered as the executable for the CP 'FTE_LTL_BULK_LOADER'.
5161 -- Runs the entire LTL Bulkloading process. When PROCESS_LTL_DATA submits the requests
5162 -- here the execution begins!
5163 --
5164 -- PARAMETERS
5165 -- IN
5166 -- p_load_id The load id of the bulkload job.
5167 -- p_src_filename The filename of the file containing the LTL data.
5168 -- p_currency The operating currency. (DEFAULT: USD);
5169 -- p_uom_code The unit of measurement of LTL packages.
5170 -- p_orig_country The Origin Country code
5171 -- p_dest_country The Destination Country code
5172 -- p_service_code service level
5173 -- p_action_code The action to be taken, ADD/UPDATE/UPDATE_ASSOC
5174 --
5175 -- OUT
5176 -- p_errbuf: A buffer of error messages.
5177 -- p_retcode: The return code. A return code of '2' specifies ERROR.
5178 --____________________________________________________________________________________--
5179
5180 PROCEDURE PROCESS_LTL_DATA(errbuf OUT NOCOPY VARCHAR2,
5181 retcode OUT NOCOPY VARCHAR2,
5182 p_load_id IN NUMBER,
5183 p_src_filename IN VARCHAR2,
5184 p_currency IN VARCHAR2,
5185 p_uom_code IN VARCHAR2,
5186 p_orig_country IN VARCHAR2,
5187 p_dest_country IN VARCHAR2,
5188 p_service_code IN VARCHAR2,
5189 p_action_code IN VARCHAR2,
5190 p_tariff_name IN VARCHAR2,
5191 p_user_debug IN NUMBER) IS
5192
5193 l_phase NUMBER := 2;
5194 x_status NUMBER;
5195 l_load_number NUMBER;
5196 l_existing_carrier NUMBER;
5197 l_abort BOOLEAN := false;
5198 l_return_status NUMBER;
5199
5200 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.LOAD_LTL_DATA';
5201
5202 BEGIN
5203 --+
5204 -- Start the WSH debugger
5205 --+
5206 FTE_UTIL_PKG.Init_Debug(p_user_debug);
5207
5208 FTE_UTIL_PKG.Enter_Debug(l_module_name);
5209 x_status := -1;
5210
5211 G_ACTION := p_action_code;
5212 G_LTL_UOM := p_uom_code;
5213 G_LTL_CURRENCY := p_currency;
5214 G_ORIG_COUNTRY := p_orig_country;
5215 G_DEST_COUNTRY := p_dest_country;
5216 G_SERVICE_CODE := p_service_code;
5217
5218 g_report_header.StartDate := to_char(sysdate, 'Dy MM/DD/YYYY HH24:MI:SS');
5219
5220 IF(FTE_BULKLOAD_PKG.g_debug_on) THEN
5221 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_action_code ', p_action_code);
5222 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_service_code', p_service_code);
5223 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_tariff_name ', p_tariff_name);
5224 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_uom_code ', p_uom_code);
5225 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_orig_country', p_orig_country);
5226 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_dest_country', p_dest_country);
5227 END IF;
5228
5229 IF (p_action_code IS NULL OR length(p_action_code) = 0) THEN
5230 x_status := 2;
5231 errbuf := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_INVALID_ACTION');
5232 FTE_UTIL_PKG.Write_OutFile( p_msg => errbuf,
5233 p_module_name => l_module_name,
5234 p_category => 'B',
5235 p_line_number => 0);
5236 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5237 RETURN;
5238
5239 ELSIF (p_tariff_name IS NULL OR length(p_tariff_name) = 0) THEN
5240 x_status := 2;
5241 errbuf := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_LTL_TARIFF_NAME_MISSING');
5242 FTE_UTIL_PKG.Write_OutFile( p_msg => errbuf,
5243 p_module_name => l_module_name,
5244 p_category => 'B',
5245 p_line_number => 0);
5246 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5247 RETURN;
5248
5249 ELSIF ((p_src_filename IS NULL OR length(p_src_filename) = 0) AND
5250 p_action_code IN ('ADD', 'UPDATE')) THEN
5251 x_status := 2;
5252 errbuf := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_FILE_NAME_MISSING');
5253 FTE_UTIL_PKG.Write_OutFile( p_msg => errbuf,
5254 p_module_name => l_module_name,
5255 p_category => 'B',
5256 p_line_number => 0);
5257 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5258 RETURN;
5259
5260 ELSIF(p_action_code = 'ADD') THEN
5261
5262 IF (p_service_code IS NULL OR length(p_service_code) = 0) THEN
5263 x_status := 2;
5264 errbuf := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_SERVICE_LEVEL_MISSING');
5265 FTE_UTIL_PKG.Write_OutFile( p_msg => errbuf,
5266 p_module_name => l_module_name,
5267 p_category => 'B',
5268 p_line_number => 0);
5269 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5270 RETURN;
5271
5272 ELSIF (p_orig_country IS NULL OR length(p_orig_country) = 0) THEN
5273 x_status := 2;
5274 errbuf := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_INVALID_ORIGIN');
5275 FTE_UTIL_PKG.Write_OutFile(p_msg => errbuf,
5276 p_module_name => l_module_name,
5277 p_category => 'B',
5278 p_line_number => 0);
5279 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5280 RETURN;
5281
5282 ELSIF (p_dest_country IS NULL OR length(p_dest_country) = 0) THEN
5283 x_status := 2;
5284 errbuf := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_INVALID_DESTINATION');
5285 FTE_UTIL_PKG.Write_OutFile(p_msg => errbuf,
5286 p_module_name => l_module_name,
5287 p_category => 'B',
5288 p_line_number => 0);
5289 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5290 RETURN;
5291 END IF;
5292
5293 END IF;
5294
5295 --+
5296 -- Gather information from the previous LTL load
5297 -- Get the load number of the target set of lanes.
5298 --+
5299 IF (p_action_code = 'UPDATE') THEN
5300
5301 OPEN GET_PREVIOUS_LOAD_INFO (p_tariff_name => p_tariff_name);
5302
5303 FETCH GET_PREVIOUS_LOAD_INFO
5304 INTO l_load_number,
5305 G_SERVICE_CODE,
5306 g_orig_country,
5307 g_dest_country,
5308 l_existing_carrier;
5309
5310 IF (GET_PREVIOUS_LOAD_INFO%NOTFOUND) THEN
5311 x_status := 2;
5312 errbuf := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_INVALID_DESTINATION');
5313 FTE_UTIL_PKG.Write_OutFile(p_msg => errbuf,
5314 p_module_name => l_module_name,
5315 p_category => 'C',
5316 p_line_number => 0);
5317 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5318
5319 IF(GET_PREVIOUS_LOAD_INFO%ISOPEN) THEN
5320 CLOSE GET_PREVIOUS_LOAD_INFO;
5321 END IF;
5322 RETURN;
5323 END IF;
5324
5325 IF(GET_PREVIOUS_LOAD_INFO%ISOPEN) THEN
5326 CLOSE GET_PREVIOUS_LOAD_INFO;
5327 END IF;
5328 END IF;
5329
5330 UPDATE
5331 fte_tariff_carriers
5332 SET
5333 new_expiry_date = to_date(to_char(to_date(to_char(new_expiry_date, G_DATE), G_DATE_FORMAT)+1-1/24/60/60, G_DATE_FORMAT), G_DATE_FORMAT),
5334 last_updated_by = G_USER_ID,
5335 last_update_date = SYSDATE,
5336 last_update_login = G_USER_ID
5337 WHERE
5338 tariff_name = p_tariff_name;
5339
5340 IF (p_action_code IN ('ADD', 'UPDATE')) THEN
5341
5342 UPLOAD_LTL_RATES(p_file_name => p_src_filename,
5343 p_load_id => p_load_id,
5344 p_tariff_name => p_tariff_name,
5345 p_action_code => p_action_code,
5346 x_phase => l_phase,
5347 x_abort => l_abort,
5348 x_status => x_status,
5349 x_error_msg => errbuf);
5350
5351 ELSIF (p_action_code = 'UPDATE_ASSOC') THEN
5352
5353 UPDATE_TARIFF_LANES(p_tariff_name => p_tariff_name,
5354 p_load_id => p_load_id,
5355 x_abort => l_abort,
5356 x_status => x_status,
5357 x_error_msg => errbuf);
5358 l_phase := 2;
5359
5360 END IF;
5361
5362 --+
5363 -- Clean up the tables if we failed, or after the whole process is complete.
5364 --+
5365 IF (l_phase = 2 OR x_status <> -1) THEN
5366
5367 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
5368 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Finished ' || p_action_code || ' with status ' || x_status);
5369 END IF;
5370
5371 CLEANUP_TABLES(p_load_id => p_load_id,
5372 p_abort => l_abort,
5373 p_tariff_name => p_tariff_name,
5374 p_action_code => p_action_code,
5375 p_save_data => p_user_debug,
5376 x_status => l_return_status,
5377 x_error_msg => errbuf);
5378
5379 END IF;
5380
5381 IF(x_status = -1) THEN
5382 --+
5383 -- Concurrent Manager expects 0 for success.
5384 --+
5385
5386 retcode := 0;
5387 errbuf := 'Completed phase ' || l_phase || ' successfully!' || errbuf;
5388
5389 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
5390 FTE_UTIL_PKG.Write_LogFile(l_module_name, errbuf);
5391 END IF;
5392
5393 COMMIT;
5394
5395 --+
5396 -- Generate a report of all created lanes and rate charts if completed successfully
5397 --+
5398 IF (l_phase = 2) THEN
5399 g_report_header.FileName := p_src_filename;
5400 g_report_header.TariffName := p_tariff_name;
5401 g_report_header.ServiceLevel := G_SERVICE_CODE;
5402 g_report_header.Orig_Country := g_orig_country;
5403 g_report_header.Dest_Country := g_dest_country;
5404 g_report_header.Currency := p_currency;
5405 g_report_header.UOM := p_uom_code;
5406
5407 IF (p_action_code = 'UPDATE') THEN
5408 l_load_number := l_load_number + 1;
5409 END IF;
5410
5411 GENERATE_LTL_REPORT(p_load_id => p_load_id,
5412 p_load_number => l_load_number,
5413 p_tariff_name => p_tariff_name,
5414 x_error_msg => errbuf,
5415 x_status => x_status);
5416
5417 --+
5418 -- If there is an error or exception during the printing
5419 -- of the report, it should only be reported as a warning.
5420 -- It should not cause the program to error out.
5421 --+
5422 IF (errbuf IS NOT NULL) THEN
5423 FTE_UTIL_PKG.Write_LogFile(l_module_name, errbuf);
5424 END IF;
5425
5426 END IF;
5427
5428 ELSE
5429 retcode := 2;
5430 errbuf := 'Completed with errors. ' || errbuf || '. Please check logs for more details.';
5431 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
5432 FTE_UTIL_PKG.Write_LogFile(l_module_name, errbuf);
5433 END IF;
5434 END IF;
5435
5436 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5437
5438 EXCEPTION
5439 WHEN OTHERS THEN
5440 CLEANUP_TABLES( p_load_id => p_load_id,
5441 p_abort => TRUE,
5442 p_tariff_name => p_tariff_name,
5443 p_action_code => p_action_code,
5444 p_save_data => p_user_debug,
5445 x_error_msg => errbuf,
5446 x_status => x_status);
5447 COMMIT;
5448
5449 FTE_UTIL_PKG.Write_LogFile(p_module_name => l_module_name,
5450 p_message => sqlerrm);
5451
5452 retcode := 2;
5453 errbuf := errbuf || sqlerrm;
5454 END PROCESS_LTL_DATA;
5455
5456 --______________________________________________________________________________________--
5457 --
5458 -- PROCEDURE: QP_PROCESS
5459 --
5460 -- Purpose
5461 -- Use the QP api to load a given group of rate charts.
5462 --
5463 -- IN Parameters
5464 -- 1. p_load_id: the load id of the bulkload job.
5465 -- 2. p_group_process_id: This specifies the group of rate charts to load.
5466 -- The process ids of this group of rate charts are
5467 -- obtained from the table FTE_INTERFACE_LANES.
5468 --
5469 -- Out Parameters
5470 -- 1. errbuf: A buffer of error messages.
5471 -- 2. retcode: The return code. A return code of '1' specifies ERROR.
5472 --______________________________________________________________________________________--
5473
5474 PROCEDURE QP_PROCESS (ERRBUF OUT NOCOPY VARCHAR2,
5475 RETCODE OUT NOCOPY VARCHAR2,
5476 p_load_id IN NUMBER,
5477 p_group_process_id IN NUMBER,
5478 p_user_debug IN NUMBER) IS
5479
5480 l_process_id NUMBER;
5481 x_status NUMBER := -1;
5482 x_error_msg VARCHAR2(8000);
5483 l_temp BOOLEAN;
5484 l_name VARCHAR2(200);
5485 l_currency_tbl FTE_RATE_CHART_PKG.LH_CURRENCY_CODE_TAB;
5486 l_name_tbl FTE_RATE_CHART_PKG.LH_NAME_TAB;
5487
5488 CURSOR GET_PROCESS_ID IS
5489 SELECT
5490 l.process_id, qh.name
5491 FROM
5492 fte_interface_lanes l, qp_interface_list_headers qh
5493 WHERE
5494 l.load_id = p_load_id AND
5495 l.group_process_id = p_group_process_id AND
5496 l.process_id = qh.process_id;
5497
5498 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.QP_PROCESS';
5499
5500 BEGIN
5501
5502 FTE_UTIL_PKG.Enter_Debug(l_module_name);
5503
5504 IF (p_user_debug = 1) THEN
5505 FTE_BULKLOAD_PKG.g_debug_on := TRUE;
5506 END IF;
5507
5508 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
5509 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'QP SUB PROCESS FOR Load ID, p_load_id', p_load_id);
5510 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_group_process_id ', p_group_process_id);
5511 END IF;
5512
5513 OPEN GET_PROCESS_ID;
5514
5515 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
5516 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Number of Rate Charts is ' || get_process_id%ROWCOUNT);
5517 END IF;
5518
5519 LOOP
5520 FETCH get_process_id INTO l_process_id, l_name_tbl(1);
5521 EXIT WHEN get_process_id%NOTFOUND;
5522
5523 IF (x_status <> -1) THEN
5524
5525 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
5526 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'QP ERROR ' || x_error_msg);
5527 END IF;
5528
5529 l_temp:= FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
5530 errbuf := x_error_msg;
5531 retcode := '1';
5532
5533 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5534 RETURN;
5535
5536 END IF;
5537
5538 FTE_RATE_CHART_PKG.QP_API_CALL(p_chart_type => 'LTL_RATE_CHART',
5539 p_process_id => l_process_id,
5540 p_name => l_name_tbl,
5541 p_currency => l_currency_tbl,
5542 x_status => x_status,
5543 x_error_msg => x_error_msg);
5544
5545 END LOOP;
5546
5547 CLOSE GET_PROCESS_ID;
5548
5549 IF (x_status = -1) THEN
5550 l_temp:= Fnd_Concurrent.Set_Completion_Status('NORMAL','');
5551 retcode := 0;
5552 ELSE
5553 errbuf := x_error_msg;
5554 FTE_UTIL_PKG.Write_LogFile(l_module_name,Fte_Util_Pkg.Get_Msg(p_name => 'FTE_LOADER_CATEGORY_O'),x_error_msg);
5555 l_temp:= Fnd_Concurrent.Set_Completion_Status('ERROR','');
5556 retcode := 2;
5557 END IF;
5558
5559 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5560 COMMIT;
5561
5562 EXCEPTION
5563
5564 WHEN OTHERS THEN
5565 errbuf := sqlcode || ', ' || sqlerrm;
5566 retcode := 2;
5567 FTE_UTIL_PKG.Write_LogFile(l_module_name,'Unexpected Error While Calling QP API', errbuf);
5568 FTE_UTIL_PKG.Exit_Debug(l_module_name);
5569
5570 END QP_PROCESS;
5571
5572 END FTE_LTL_LOADER;