[Home] [Help]
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
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);
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;
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
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:
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:
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
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:
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: --______________________________________________________________________________________--
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
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:
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
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:
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: --______________________________________________________________________________________--
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:
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:
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
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:
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: --___________________________________________________________________________________--
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,
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);
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:
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: --___________________________________________________________________________________--
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,
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);
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:
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: --___________________________________________________________________________________--
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,
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);
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:
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: --___________________________________________________________________________________--
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,
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);
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:
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: --_________________________________________________________________________________________--
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:
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 => ' ',
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 ***
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);
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,
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,
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,
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'),
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,
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,
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,
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,
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,
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,
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,
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 :',
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.',
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',
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',
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 => ' ',
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)',
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
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:
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',
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',
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
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;
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)',
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
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:
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 => ' ',
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 ***
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);
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,
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,
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:
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
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:
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: --_________________________________________________________________________________--
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:
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
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:
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;
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
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
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
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;
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:
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: --___________________________________________________________________________________--
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:
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:
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
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
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
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;
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
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
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:
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
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:
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: --_____________________________________________________________________________________--
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
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;
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:
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);
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:
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;
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
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,
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;
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: --
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';
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:
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);
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:
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
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;
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');
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');
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');
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;
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:
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: --+
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
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
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
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
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;
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: --___________________________________________________________________________________--
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
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;
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:
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
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);
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;
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:
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');
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);
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);
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;
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:
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;
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;
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;
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: --___________________________________________________________________________________--
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: --
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:
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);
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;
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:
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: --+
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
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:
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
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;
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
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:
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: --_________________________________________________________________________________--
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;
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:
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
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
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
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;
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:
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),',');
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;
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);
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);
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
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);
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;
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;
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:
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:
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: --____________________________________________________________________________________--
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;
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
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:
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:
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;
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:
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:
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:
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:
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:
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;
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:
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
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;
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;
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:
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;
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:
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: --__________________________________________________________________________________________--
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;
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;
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);
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);
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);
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);
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;
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:
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: --+
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
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:
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;
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);
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;
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:
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;
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:
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 );
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);
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: --+
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',
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);
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);
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:
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.
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,
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);
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;
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
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: --___________________________________________________________________________________--
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;
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:
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);
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:
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
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');
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;
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
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
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);
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;
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:
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,
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,
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: --
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:
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
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:
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 --
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: --
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:
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:
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:
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;
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:
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);
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:
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
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: --+
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;
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:
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
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:
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;
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);
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:
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);
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:
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;
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);
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;
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;
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:
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
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,
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
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);
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);
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);
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);
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;
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:
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: --+
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.
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);
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;
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);
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;
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,
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,
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: --+
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
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
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:
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: --___________________________________________________________________________________--
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.');
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,
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);
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:
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: --______________________________________________________________________________________--
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;
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;
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, ',');
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');
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);
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);
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: --+
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);
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);
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);
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;
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:
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:
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);
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);
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;
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);
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);
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;
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;
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:
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);
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
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
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:
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,
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;
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;
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);
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:
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;
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;
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:
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:
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:
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');
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:
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,
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;
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:
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);
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,
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);
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:
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');
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);
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);
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:
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);
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);
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;
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);
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);
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;
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;
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: --___________________________________________________________________________________--
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
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;
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;
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:
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);
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:
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:
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,
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);
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: --+
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);
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;
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,
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;
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;
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:
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
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;
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:
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
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
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),
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
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:
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: --______________________________________________________________________________________--
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.
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
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
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);
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);
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);
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,
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
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;
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:
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:
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;
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);
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);
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;
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:
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
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;
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);
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);
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;
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);
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);
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
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);
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);
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:
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);
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);
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;
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);
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);
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;
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);
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);
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;
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);
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);
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;
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,
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:
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);
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,
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;
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;
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;
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:
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);
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;
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:
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:
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:
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:
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;