[Home] [Help]
41: BEGIN
42:
43: l_list(1) := 'BIS_GLOBAL_START_DATE';
44: IF (NOT bis_common_parameters.check_global_parameters(l_list)) THEN
45: BIS_COLLECTION_UTILITIES.put_line(' ');
46: BIS_COLLECTION_UTILITIES.put_line('Error! Collection aborted because the global start date has not been set up.');
47: BIS_COLLECTION_UTILITIES.put_line(' ');
48: l_setup := -999;
49: END IF;
42:
43: l_list(1) := 'BIS_GLOBAL_START_DATE';
44: IF (NOT bis_common_parameters.check_global_parameters(l_list)) THEN
45: BIS_COLLECTION_UTILITIES.put_line(' ');
46: BIS_COLLECTION_UTILITIES.put_line('Error! Collection aborted because the global start date has not been set up.');
47: BIS_COLLECTION_UTILITIES.put_line(' ');
48: l_setup := -999;
49: END IF;
50:
43: l_list(1) := 'BIS_GLOBAL_START_DATE';
44: IF (NOT bis_common_parameters.check_global_parameters(l_list)) THEN
45: BIS_COLLECTION_UTILITIES.put_line(' ');
46: BIS_COLLECTION_UTILITIES.put_line('Error! Collection aborted because the global start date has not been set up.');
47: BIS_COLLECTION_UTILITIES.put_line(' ');
48: l_setup := -999;
49: END IF;
50:
51: IF (nvl(FND_PROFILE.VALUE('ISC_WSH_FTE_DBI_INSTALLED'),'N') <> 'Y') THEN
48: l_setup := -999;
49: END IF;
50:
51: IF (nvl(FND_PROFILE.VALUE('ISC_WSH_FTE_DBI_INSTALLED'),'N') <> 'Y') THEN
52: BIS_COLLECTION_UTILITIES.put_line(' ');
53: BIS_COLLECTION_UTILITIES.put_line('Error! Collection aborted because the profile option "ISC: Shipping/Transportation Execution DBI Installation" has not been set to Y.');
54: BIS_COLLECTION_UTILITIES.put_line(' ');
55: l_setup := -999;
56: END IF;
49: END IF;
50:
51: IF (nvl(FND_PROFILE.VALUE('ISC_WSH_FTE_DBI_INSTALLED'),'N') <> 'Y') THEN
52: BIS_COLLECTION_UTILITIES.put_line(' ');
53: BIS_COLLECTION_UTILITIES.put_line('Error! Collection aborted because the profile option "ISC: Shipping/Transportation Execution DBI Installation" has not been set to Y.');
54: BIS_COLLECTION_UTILITIES.put_line(' ');
55: l_setup := -999;
56: END IF;
57:
50:
51: IF (nvl(FND_PROFILE.VALUE('ISC_WSH_FTE_DBI_INSTALLED'),'N') <> 'Y') THEN
52: BIS_COLLECTION_UTILITIES.put_line(' ');
53: BIS_COLLECTION_UTILITIES.put_line('Error! Collection aborted because the profile option "ISC: Shipping/Transportation Execution DBI Installation" has not been set to Y.');
54: BIS_COLLECTION_UTILITIES.put_line(' ');
55: l_setup := -999;
56: END IF;
57:
58: g_sec_curr_def := isc_dbi_currency_pkg.is_sec_curr_defined;
56: END IF;
57:
58: g_sec_curr_def := isc_dbi_currency_pkg.is_sec_curr_defined;
59: IF (g_sec_curr_def = 'E') THEN
60: BIS_COLLECTION_UTILITIES.put_line(' ');
61: BIS_COLLECTION_UTILITIES.put_line('Error! Collection aborted because the set-up of the DBI Global Parameter "Secondary Global Currency" is incomplete. Please verify the proper set-up of the Global Currency Rate Type and the Global Currency Code.');
62: BIS_COLLECTION_UTILITIES.put_line(' ');
63: l_setup := -999;
64: END IF;
57:
58: g_sec_curr_def := isc_dbi_currency_pkg.is_sec_curr_defined;
59: IF (g_sec_curr_def = 'E') THEN
60: BIS_COLLECTION_UTILITIES.put_line(' ');
61: BIS_COLLECTION_UTILITIES.put_line('Error! Collection aborted because the set-up of the DBI Global Parameter "Secondary Global Currency" is incomplete. Please verify the proper set-up of the Global Currency Rate Type and the Global Currency Code.');
62: BIS_COLLECTION_UTILITIES.put_line(' ');
63: l_setup := -999;
64: END IF;
65:
58: g_sec_curr_def := isc_dbi_currency_pkg.is_sec_curr_defined;
59: IF (g_sec_curr_def = 'E') THEN
60: BIS_COLLECTION_UTILITIES.put_line(' ');
61: BIS_COLLECTION_UTILITIES.put_line('Error! Collection aborted because the set-up of the DBI Global Parameter "Secondary Global Currency" is incomplete. Please verify the proper set-up of the Global Currency Rate Type and the Global Currency Code.');
62: BIS_COLLECTION_UTILITIES.put_line(' ');
63: l_setup := -999;
64: END IF;
65:
66: g_batch_size := bis_common_parameters.get_batch_size(bis_common_parameters.high);
63: l_setup := -999;
64: END IF;
65:
66: g_batch_size := bis_common_parameters.get_batch_size(bis_common_parameters.high);
67: BIS_COLLECTION_UTILITIES.put_line('The batch size is ' || g_batch_size);
68:
69: g_global_start_date := bis_common_parameters.get_global_start_date;
70: BIS_COLLECTION_UTILITIES.put_line('The global start date is ' || g_global_start_date);
71:
66: g_batch_size := bis_common_parameters.get_batch_size(bis_common_parameters.high);
67: BIS_COLLECTION_UTILITIES.put_line('The batch size is ' || g_batch_size);
68:
69: g_global_start_date := bis_common_parameters.get_global_start_date;
70: BIS_COLLECTION_UTILITIES.put_line('The global start date is ' || g_global_start_date);
71:
72: g_global_currency := bis_common_parameters.get_currency_code;
73: BIS_COLLECTION_UTILITIES.put_line('The global currency code is ' || g_global_currency);
74:
69: g_global_start_date := bis_common_parameters.get_global_start_date;
70: BIS_COLLECTION_UTILITIES.put_line('The global start date is ' || g_global_start_date);
71:
72: g_global_currency := bis_common_parameters.get_currency_code;
73: BIS_COLLECTION_UTILITIES.put_line('The global currency code is ' || g_global_currency);
74:
75: g_global_rate_type := bis_common_parameters.get_rate_type;
76: BIS_COLLECTION_UTILITIES.put_line('The primary rate type is ' || g_global_rate_type);
77:
72: g_global_currency := bis_common_parameters.get_currency_code;
73: BIS_COLLECTION_UTILITIES.put_line('The global currency code is ' || g_global_currency);
74:
75: g_global_rate_type := bis_common_parameters.get_rate_type;
76: BIS_COLLECTION_UTILITIES.put_line('The primary rate type is ' || g_global_rate_type);
77:
78: g_sec_global_currency := bis_common_parameters.get_secondary_currency_code;
79: BIS_COLLECTION_UTILITIES.put_line('The secondary global currency code is ' || g_sec_global_currency);
80:
75: g_global_rate_type := bis_common_parameters.get_rate_type;
76: BIS_COLLECTION_UTILITIES.put_line('The primary rate type is ' || g_global_rate_type);
77:
78: g_sec_global_currency := bis_common_parameters.get_secondary_currency_code;
79: BIS_COLLECTION_UTILITIES.put_line('The secondary global currency code is ' || g_sec_global_currency);
80:
81: g_sec_global_rate_type := bis_common_parameters.get_secondary_rate_type;
82: BIS_COLLECTION_UTILITIES.put_line('The secondary rate type is ' || g_sec_global_rate_type);
83:
78: g_sec_global_currency := bis_common_parameters.get_secondary_currency_code;
79: BIS_COLLECTION_UTILITIES.put_line('The secondary global currency code is ' || g_sec_global_currency);
80:
81: g_sec_global_rate_type := bis_common_parameters.get_secondary_rate_type;
82: BIS_COLLECTION_UTILITIES.put_line('The secondary rate type is ' || g_sec_global_rate_type);
83:
84: g_treasury_rate_type := bis_common_parameters.get_treasury_rate_type;
85: -- IF (g_treasury_rate_type IS NULL) THEN
86: -- g_errbuf := 'Collection aborted because the set-up of the DBI Global Parameter "Treasury Rate Type" is incomplete. Please verify the proper set-up of the Treasury Rate Type.';
85: -- IF (g_treasury_rate_type IS NULL) THEN
86: -- g_errbuf := 'Collection aborted because the set-up of the DBI Global Parameter "Treasury Rate Type" is incomplete. Please verify the proper set-up of the Treasury Rate Type.';
87: -- return(-1);
88: -- END IF;
89: BIS_COLLECTION_UTILITIES.put_line('The treasury rate type is ' || g_treasury_rate_type);
90:
91: BEGIN
92: g_new_arr_window := FND_PROFILE.VALUE('FTE_CARRIER_ARR_WINDOW');
93: IF (g_new_arr_window IS NULL) THEN
90:
91: BEGIN
92: g_new_arr_window := FND_PROFILE.VALUE('FTE_CARRIER_ARR_WINDOW');
93: IF (g_new_arr_window IS NULL) THEN
94: BIS_COLLECTION_UTILITIES.put_line(' ');
95: BIS_COLLECTION_UTILITIES.Put_Line('Error! Collection aborted because the profile option "FTE: Carrier On-time Arrival Window" has not been set up.');
96: BIS_COLLECTION_UTILITIES.put_line(' ');
97: l_setup := -999;
98: ELSE
91: BEGIN
92: g_new_arr_window := FND_PROFILE.VALUE('FTE_CARRIER_ARR_WINDOW');
93: IF (g_new_arr_window IS NULL) THEN
94: BIS_COLLECTION_UTILITIES.put_line(' ');
95: BIS_COLLECTION_UTILITIES.Put_Line('Error! Collection aborted because the profile option "FTE: Carrier On-time Arrival Window" has not been set up.');
96: BIS_COLLECTION_UTILITIES.put_line(' ');
97: l_setup := -999;
98: ELSE
99: BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window is ' || g_new_arr_window);
92: g_new_arr_window := FND_PROFILE.VALUE('FTE_CARRIER_ARR_WINDOW');
93: IF (g_new_arr_window IS NULL) THEN
94: BIS_COLLECTION_UTILITIES.put_line(' ');
95: BIS_COLLECTION_UTILITIES.Put_Line('Error! Collection aborted because the profile option "FTE: Carrier On-time Arrival Window" has not been set up.');
96: BIS_COLLECTION_UTILITIES.put_line(' ');
97: l_setup := -999;
98: ELSE
99: BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window is ' || g_new_arr_window);
100: END IF;
95: BIS_COLLECTION_UTILITIES.Put_Line('Error! Collection aborted because the profile option "FTE: Carrier On-time Arrival Window" has not been set up.');
96: BIS_COLLECTION_UTILITIES.put_line(' ');
97: l_setup := -999;
98: ELSE
99: BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window is ' || g_new_arr_window);
100: END IF;
101: EXCEPTION
102: WHEN VALUE_ERROR THEN
103: g_retcode := sqlcode;
100: END IF;
101: EXCEPTION
102: WHEN VALUE_ERROR THEN
103: g_retcode := sqlcode;
104: BIS_COLLECTION_UTILITIES.put_line(' ');
105: BIS_COLLECTION_UTILITIES.Put_Line('Error! Collection aborted because the profile option "FTE: Carrier On-time Arrival Window" has not been set up correctly.');
106: BIS_COLLECTION_UTILITIES.Put_Line('Please set up the profile option as a valid number.');
107: BIS_COLLECTION_UTILITIES.put_line(' ');
108: l_setup := -999;
101: EXCEPTION
102: WHEN VALUE_ERROR THEN
103: g_retcode := sqlcode;
104: BIS_COLLECTION_UTILITIES.put_line(' ');
105: BIS_COLLECTION_UTILITIES.Put_Line('Error! Collection aborted because the profile option "FTE: Carrier On-time Arrival Window" has not been set up correctly.');
106: BIS_COLLECTION_UTILITIES.Put_Line('Please set up the profile option as a valid number.');
107: BIS_COLLECTION_UTILITIES.put_line(' ');
108: l_setup := -999;
109: END;
102: WHEN VALUE_ERROR THEN
103: g_retcode := sqlcode;
104: BIS_COLLECTION_UTILITIES.put_line(' ');
105: BIS_COLLECTION_UTILITIES.Put_Line('Error! Collection aborted because the profile option "FTE: Carrier On-time Arrival Window" has not been set up correctly.');
106: BIS_COLLECTION_UTILITIES.Put_Line('Please set up the profile option as a valid number.');
107: BIS_COLLECTION_UTILITIES.put_line(' ');
108: l_setup := -999;
109: END;
110:
103: g_retcode := sqlcode;
104: BIS_COLLECTION_UTILITIES.put_line(' ');
105: BIS_COLLECTION_UTILITIES.Put_Line('Error! Collection aborted because the profile option "FTE: Carrier On-time Arrival Window" has not been set up correctly.');
106: BIS_COLLECTION_UTILITIES.Put_Line('Please set up the profile option as a valid number.');
107: BIS_COLLECTION_UTILITIES.put_line(' ');
108: l_setup := -999;
109: END;
110:
111: IF (NOT FND_INSTALLATION.GET_APP_INFO('ISC', l_status, l_industry, g_isc_schema)) THEN
108: l_setup := -999;
109: END;
110:
111: IF (NOT FND_INSTALLATION.GET_APP_INFO('ISC', l_status, l_industry, g_isc_schema)) THEN
112: BIS_COLLECTION_UTILITIES.put_line(' ');
113: BIS_COLLECTION_UTILITIES.Put_Line('Error! Collection aborted while retrieving schema information.');
114: BIS_COLLECTION_UTILITIES.put_line(' ');
115: l_setup := -999;
116: END IF;
109: END;
110:
111: IF (NOT FND_INSTALLATION.GET_APP_INFO('ISC', l_status, l_industry, g_isc_schema)) THEN
112: BIS_COLLECTION_UTILITIES.put_line(' ');
113: BIS_COLLECTION_UTILITIES.Put_Line('Error! Collection aborted while retrieving schema information.');
114: BIS_COLLECTION_UTILITIES.put_line(' ');
115: l_setup := -999;
116: END IF;
117:
110:
111: IF (NOT FND_INSTALLATION.GET_APP_INFO('ISC', l_status, l_industry, g_isc_schema)) THEN
112: BIS_COLLECTION_UTILITIES.put_line(' ');
113: BIS_COLLECTION_UTILITIES.Put_Line('Error! Collection aborted while retrieving schema information.');
114: BIS_COLLECTION_UTILITIES.put_line(' ');
115: l_setup := -999;
116: END IF;
117:
118: IF (l_setup = -999) THEN
119: g_errbuf := 'Collection aborted because the setup has not been completed. Please refer to the log file for the details.';
120: return(-1);
121: END IF;
122:
123: BIS_COLLECTION_UTILITIES.put_line('Truncating the temp tables');
124: FII_UTIL.Start_Timer;
125:
126: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_WDD_LOG';
127: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_WTS_LOG';
135: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_FTE_UOM_RATES';
136:
137: FII_UTIL.Stop_Timer;
138: FII_UTIL.Print_Timer('Truncated the temp tables in');
139: BIS_COLLECTION_UTILITIES.Put_Line(' ');
140:
141: RETURN(1);
142:
143: EXCEPTION
153: g_reporting_weight_uom := opi_dbi_rep_uom_pkg.get_reporting_uom('WT');
154: IF (g_reporting_weight_uom IS NULL) THEN
155: g_retcode := 1;
156: g_errbuf := g_errbuf || 'DBI Weight Reporting UOM has not been set up.';
157: BIS_COLLECTION_UTILITIES.put_line(' ');
158: BIS_COLLECTION_UTILITIES.put_line('Warning! DBI Weight Reporting UOM has not been set up.');
159: BIS_COLLECTION_UTILITIES.put_line(' ');
160: ELSE
161: BIS_COLLECTION_UTILITIES.put_line('The reporting weight uom is ' || g_reporting_weight_uom);
154: IF (g_reporting_weight_uom IS NULL) THEN
155: g_retcode := 1;
156: g_errbuf := g_errbuf || 'DBI Weight Reporting UOM has not been set up.';
157: BIS_COLLECTION_UTILITIES.put_line(' ');
158: BIS_COLLECTION_UTILITIES.put_line('Warning! DBI Weight Reporting UOM has not been set up.');
159: BIS_COLLECTION_UTILITIES.put_line(' ');
160: ELSE
161: BIS_COLLECTION_UTILITIES.put_line('The reporting weight uom is ' || g_reporting_weight_uom);
162: END IF;
155: g_retcode := 1;
156: g_errbuf := g_errbuf || 'DBI Weight Reporting UOM has not been set up.';
157: BIS_COLLECTION_UTILITIES.put_line(' ');
158: BIS_COLLECTION_UTILITIES.put_line('Warning! DBI Weight Reporting UOM has not been set up.');
159: BIS_COLLECTION_UTILITIES.put_line(' ');
160: ELSE
161: BIS_COLLECTION_UTILITIES.put_line('The reporting weight uom is ' || g_reporting_weight_uom);
162: END IF;
163:
157: BIS_COLLECTION_UTILITIES.put_line(' ');
158: BIS_COLLECTION_UTILITIES.put_line('Warning! DBI Weight Reporting UOM has not been set up.');
159: BIS_COLLECTION_UTILITIES.put_line(' ');
160: ELSE
161: BIS_COLLECTION_UTILITIES.put_line('The reporting weight uom is ' || g_reporting_weight_uom);
162: END IF;
163:
164: g_reporting_volume_uom := opi_dbi_rep_uom_pkg.get_reporting_uom('VOL');
165: IF (g_reporting_volume_uom IS NULL) THEN
164: g_reporting_volume_uom := opi_dbi_rep_uom_pkg.get_reporting_uom('VOL');
165: IF (g_reporting_volume_uom IS NULL) THEN
166: g_retcode := 1;
167: g_errbuf := g_errbuf || 'DBI Volume Reporting UOM has not been set up.';
168: BIS_COLLECTION_UTILITIES.put_line(' ');
169: BIS_COLLECTION_UTILITIES.put_line('Warning! DBI Volume Reporting UOM has not been set up.');
170: BIS_COLLECTION_UTILITIES.put_line(' ');
171: ELSE
172: BIS_COLLECTION_UTILITIES.put_line('The reporting volume uom is ' || g_reporting_volume_uom);
165: IF (g_reporting_volume_uom IS NULL) THEN
166: g_retcode := 1;
167: g_errbuf := g_errbuf || 'DBI Volume Reporting UOM has not been set up.';
168: BIS_COLLECTION_UTILITIES.put_line(' ');
169: BIS_COLLECTION_UTILITIES.put_line('Warning! DBI Volume Reporting UOM has not been set up.');
170: BIS_COLLECTION_UTILITIES.put_line(' ');
171: ELSE
172: BIS_COLLECTION_UTILITIES.put_line('The reporting volume uom is ' || g_reporting_volume_uom);
173: END IF;
166: g_retcode := 1;
167: g_errbuf := g_errbuf || 'DBI Volume Reporting UOM has not been set up.';
168: BIS_COLLECTION_UTILITIES.put_line(' ');
169: BIS_COLLECTION_UTILITIES.put_line('Warning! DBI Volume Reporting UOM has not been set up.');
170: BIS_COLLECTION_UTILITIES.put_line(' ');
171: ELSE
172: BIS_COLLECTION_UTILITIES.put_line('The reporting volume uom is ' || g_reporting_volume_uom);
173: END IF;
174:
168: BIS_COLLECTION_UTILITIES.put_line(' ');
169: BIS_COLLECTION_UTILITIES.put_line('Warning! DBI Volume Reporting UOM has not been set up.');
170: BIS_COLLECTION_UTILITIES.put_line(' ');
171: ELSE
172: BIS_COLLECTION_UTILITIES.put_line('The reporting volume uom is ' || g_reporting_volume_uom);
173: END IF;
174:
175: g_reporting_distance_uom := opi_dbi_rep_uom_pkg.get_reporting_uom('DIS');
176: IF (g_reporting_distance_uom IS NULL) THEN
175: g_reporting_distance_uom := opi_dbi_rep_uom_pkg.get_reporting_uom('DIS');
176: IF (g_reporting_distance_uom IS NULL) THEN
177: g_retcode := 1;
178: g_errbuf := g_errbuf || 'DBI Distance Reporting UOM has not been set up.';
179: BIS_COLLECTION_UTILITIES.put_line(' ');
180: BIS_COLLECTION_UTILITIES.put_line('Warning! DBI Distance Reporting UOM has not been set up.');
181: BIS_COLLECTION_UTILITIES.put_line(' ');
182: ELSE
183: BIS_COLLECTION_UTILITIES.put_line('The reporting distance uom is ' || g_reporting_distance_uom);
176: IF (g_reporting_distance_uom IS NULL) THEN
177: g_retcode := 1;
178: g_errbuf := g_errbuf || 'DBI Distance Reporting UOM has not been set up.';
179: BIS_COLLECTION_UTILITIES.put_line(' ');
180: BIS_COLLECTION_UTILITIES.put_line('Warning! DBI Distance Reporting UOM has not been set up.');
181: BIS_COLLECTION_UTILITIES.put_line(' ');
182: ELSE
183: BIS_COLLECTION_UTILITIES.put_line('The reporting distance uom is ' || g_reporting_distance_uom);
184: END IF;
177: g_retcode := 1;
178: g_errbuf := g_errbuf || 'DBI Distance Reporting UOM has not been set up.';
179: BIS_COLLECTION_UTILITIES.put_line(' ');
180: BIS_COLLECTION_UTILITIES.put_line('Warning! DBI Distance Reporting UOM has not been set up.');
181: BIS_COLLECTION_UTILITIES.put_line(' ');
182: ELSE
183: BIS_COLLECTION_UTILITIES.put_line('The reporting distance uom is ' || g_reporting_distance_uom);
184: END IF;
185:
179: BIS_COLLECTION_UTILITIES.put_line(' ');
180: BIS_COLLECTION_UTILITIES.put_line('Warning! DBI Distance Reporting UOM has not been set up.');
181: BIS_COLLECTION_UTILITIES.put_line(' ');
182: ELSE
183: BIS_COLLECTION_UTILITIES.put_line('The reporting distance uom is ' || g_reporting_distance_uom);
184: END IF;
185:
186: RETURN(1);
187:
201: BEGIN
202: SELECT on_time_window INTO l_old_arr_window FROM isc_dbi_fte_parameters;
203: EXCEPTION
204: WHEN NO_DATA_FOUND THEN
205: BIS_COLLECTION_UTILITIES.Put_Line('Inserting data into isc_dbi_fte_parameters.');
206: INSERT INTO isc_dbi_fte_parameters (ON_TIME_WINDOW, LAST_UPDATE_DATE) VALUES (g_new_arr_window, sysdate);
207: l_old_arr_window := g_new_arr_window;
208: END;
209:
209:
210: IF (l_old_arr_window IS NULL or g_new_arr_window <> l_old_arr_window) THEN
211: g_retcode := 1;
212: g_errbuf := 'The profile option "FTE: Carrier On-time Arrival Window" has been changed since last collection.';
213: BIS_COLLECTION_UTILITIES.Put_Line(' ');
214: BIS_COLLECTION_UTILITIES.Put_Line('Warning! Profile option "FTE: Carrier On-time Arrival Window" has been changed since last collection.');
215: BIS_COLLECTION_UTILITIES.Put_Line('The new setting will affect current and future data, but not past data.');
216: BIS_COLLECTION_UTILITIES.Put_Line('Depending on your implementation');
217: BIS_COLLECTION_UTILITIES.Put_Line('- No action is required if past data should be preserved in the context of the previous profile option setting.');
210: IF (l_old_arr_window IS NULL or g_new_arr_window <> l_old_arr_window) THEN
211: g_retcode := 1;
212: g_errbuf := 'The profile option "FTE: Carrier On-time Arrival Window" has been changed since last collection.';
213: BIS_COLLECTION_UTILITIES.Put_Line(' ');
214: BIS_COLLECTION_UTILITIES.Put_Line('Warning! Profile option "FTE: Carrier On-time Arrival Window" has been changed since last collection.');
215: BIS_COLLECTION_UTILITIES.Put_Line('The new setting will affect current and future data, but not past data.');
216: BIS_COLLECTION_UTILITIES.Put_Line('Depending on your implementation');
217: BIS_COLLECTION_UTILITIES.Put_Line('- No action is required if past data should be preserved in the context of the previous profile option setting.');
218: BIS_COLLECTION_UTILITIES.Put_Line('- If past data should be updated with the latest setting, an initial load is required to be executed.');
211: g_retcode := 1;
212: g_errbuf := 'The profile option "FTE: Carrier On-time Arrival Window" has been changed since last collection.';
213: BIS_COLLECTION_UTILITIES.Put_Line(' ');
214: BIS_COLLECTION_UTILITIES.Put_Line('Warning! Profile option "FTE: Carrier On-time Arrival Window" has been changed since last collection.');
215: BIS_COLLECTION_UTILITIES.Put_Line('The new setting will affect current and future data, but not past data.');
216: BIS_COLLECTION_UTILITIES.Put_Line('Depending on your implementation');
217: BIS_COLLECTION_UTILITIES.Put_Line('- No action is required if past data should be preserved in the context of the previous profile option setting.');
218: BIS_COLLECTION_UTILITIES.Put_Line('- If past data should be updated with the latest setting, an initial load is required to be executed.');
219: BIS_COLLECTION_UTILITIES.Put_Line(' ');
212: g_errbuf := 'The profile option "FTE: Carrier On-time Arrival Window" has been changed since last collection.';
213: BIS_COLLECTION_UTILITIES.Put_Line(' ');
214: BIS_COLLECTION_UTILITIES.Put_Line('Warning! Profile option "FTE: Carrier On-time Arrival Window" has been changed since last collection.');
215: BIS_COLLECTION_UTILITIES.Put_Line('The new setting will affect current and future data, but not past data.');
216: BIS_COLLECTION_UTILITIES.Put_Line('Depending on your implementation');
217: BIS_COLLECTION_UTILITIES.Put_Line('- No action is required if past data should be preserved in the context of the previous profile option setting.');
218: BIS_COLLECTION_UTILITIES.Put_Line('- If past data should be updated with the latest setting, an initial load is required to be executed.');
219: BIS_COLLECTION_UTILITIES.Put_Line(' ');
220: BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window was ' || l_old_arr_window || ' as last collection.');
213: BIS_COLLECTION_UTILITIES.Put_Line(' ');
214: BIS_COLLECTION_UTILITIES.Put_Line('Warning! Profile option "FTE: Carrier On-time Arrival Window" has been changed since last collection.');
215: BIS_COLLECTION_UTILITIES.Put_Line('The new setting will affect current and future data, but not past data.');
216: BIS_COLLECTION_UTILITIES.Put_Line('Depending on your implementation');
217: BIS_COLLECTION_UTILITIES.Put_Line('- No action is required if past data should be preserved in the context of the previous profile option setting.');
218: BIS_COLLECTION_UTILITIES.Put_Line('- If past data should be updated with the latest setting, an initial load is required to be executed.');
219: BIS_COLLECTION_UTILITIES.Put_Line(' ');
220: BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window was ' || l_old_arr_window || ' as last collection.');
221: BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window is set to ' || g_new_arr_window || ' now.');
214: BIS_COLLECTION_UTILITIES.Put_Line('Warning! Profile option "FTE: Carrier On-time Arrival Window" has been changed since last collection.');
215: BIS_COLLECTION_UTILITIES.Put_Line('The new setting will affect current and future data, but not past data.');
216: BIS_COLLECTION_UTILITIES.Put_Line('Depending on your implementation');
217: BIS_COLLECTION_UTILITIES.Put_Line('- No action is required if past data should be preserved in the context of the previous profile option setting.');
218: BIS_COLLECTION_UTILITIES.Put_Line('- If past data should be updated with the latest setting, an initial load is required to be executed.');
219: BIS_COLLECTION_UTILITIES.Put_Line(' ');
220: BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window was ' || l_old_arr_window || ' as last collection.');
221: BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window is set to ' || g_new_arr_window || ' now.');
222: UPDATE isc_dbi_fte_parameters SET on_time_window = g_new_arr_window, last_update_date = sysdate;
215: BIS_COLLECTION_UTILITIES.Put_Line('The new setting will affect current and future data, but not past data.');
216: BIS_COLLECTION_UTILITIES.Put_Line('Depending on your implementation');
217: BIS_COLLECTION_UTILITIES.Put_Line('- No action is required if past data should be preserved in the context of the previous profile option setting.');
218: BIS_COLLECTION_UTILITIES.Put_Line('- If past data should be updated with the latest setting, an initial load is required to be executed.');
219: BIS_COLLECTION_UTILITIES.Put_Line(' ');
220: BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window was ' || l_old_arr_window || ' as last collection.');
221: BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window is set to ' || g_new_arr_window || ' now.');
222: UPDATE isc_dbi_fte_parameters SET on_time_window = g_new_arr_window, last_update_date = sysdate;
223: END IF;
216: BIS_COLLECTION_UTILITIES.Put_Line('Depending on your implementation');
217: BIS_COLLECTION_UTILITIES.Put_Line('- No action is required if past data should be preserved in the context of the previous profile option setting.');
218: BIS_COLLECTION_UTILITIES.Put_Line('- If past data should be updated with the latest setting, an initial load is required to be executed.');
219: BIS_COLLECTION_UTILITIES.Put_Line(' ');
220: BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window was ' || l_old_arr_window || ' as last collection.');
221: BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window is set to ' || g_new_arr_window || ' now.');
222: UPDATE isc_dbi_fte_parameters SET on_time_window = g_new_arr_window, last_update_date = sysdate;
223: END IF;
224:
217: BIS_COLLECTION_UTILITIES.Put_Line('- No action is required if past data should be preserved in the context of the previous profile option setting.');
218: BIS_COLLECTION_UTILITIES.Put_Line('- If past data should be updated with the latest setting, an initial load is required to be executed.');
219: BIS_COLLECTION_UTILITIES.Put_Line(' ');
220: BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window was ' || l_old_arr_window || ' as last collection.');
221: BIS_COLLECTION_UTILITIES.Put_Line('Carrier On-time Arrival Window is set to ' || g_new_arr_window || ' now.');
222: UPDATE isc_dbi_fte_parameters SET on_time_window = g_new_arr_window, last_update_date = sysdate;
223: END IF;
224:
225: RETURN(1);
238: BEGIN
239:
240: l_overwrite := FALSE;
241:
242: BIS_COLLECTION_UTILITIES.Put_Line('Setting the 11.5.10 CU1 date for WMS page.');
243: OPI_DBI_WMS_UTILITY_PKG.Set_Wms_Pts_Gsd(l_overwrite);
244:
245: RETURN(1);
246:
276:
277: -- l_from_date := to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS');
278: -- l_to_date := to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS');
279:
280: BIS_COLLECTION_UTILITIES.put_line('Identifying delivery details');
281: FII_UTIL.Start_Timer;
282:
283: INSERT /*+ APPEND PARALLEL(F) */ INTO isc_dbi_tmp_del_details F (
284: DELIVERY_DETAIL_ID,
337: FII_UTIL.Stop_Timer;
338: FII_UTIL.Print_Timer('Identified ' || l_detail_count || ' delivery details in');
339: COMMIT;
340:
341: BIS_COLLECTION_UTILITIES.put_line('Identifying trip stops');
342: FII_UTIL.Start_Timer;
343:
344: INSERT /*+ APPEND PARALLEL(tmp) */ INTO isc_dbi_tmp_trip_stops tmp (
345: STOP_ID,
389: FII_UTIL.Stop_Timer;
390: FII_UTIL.Print_Timer('Identified ' || l_stop_count || ' trip stops in');
391: COMMIT;
392:
393: BIS_COLLECTION_UTILITIES.put_line('Identifying delivery legs');
394: FII_UTIL.Start_Timer;
395:
396: INSERT /*+ APPEND PARALLEL(tmp) */ INTO isc_dbi_tmp_del_legs tmp (
397: DELIVERY_LEG_ID,
676:
677: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
678: TABNAME => 'ISC_DBI_TMP_WDD_LOG');
679:
680: BIS_COLLECTION_UTILITIES.put_line('Deleting obsolete records from the base summary');
681: FII_UTIL.Start_Timer;
682:
683: DELETE FROM isc_dbi_del_details_f
684: WHERE delivery_detail_id IN (SELECT DISTINCT log.delivery_detail_id
791:
792: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
793: TABNAME => 'ISC_DBI_TMP_WTS_LOG');
794:
795: BIS_COLLECTION_UTILITIES.put_line('Deleting obsolete records from the base summary');
796: FII_UTIL.Start_Timer;
797:
798: DELETE /*+ index(a, ISC_DBI_DEL_LEGS_F_U1) */ FROM isc_dbi_del_legs_f a
799: WHERE delivery_leg_id IN (SELECT /*+ index(idl,ISC_DBI_DEL_LEGS_F_U1) use_nl( log, idl)*/ idl.delivery_leg_id
1052:
1053: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
1054: TABNAME => 'ISC_DBI_TMP_FIH_LOG');
1055:
1056: BIS_COLLECTION_UTILITIES.put_line('Deleting obsolete records from the base summary');
1057: FII_UTIL.Start_Timer;
1058:
1059: DELETE FROM isc_dbi_fte_invoices_f
1060: WHERE invoice_header_id IN (SELECT DISTINCT log.invoice_header_id
1252: l_time_missing := TRUE;
1253:
1254: FII_UTIL.Start_Timer;
1255:
1256: BIS_COLLECTION_UTILITIES.Put_Line('Begin to retrieve the time boundary for the initial load');
1257:
1258: SELECT /*+ PARALLEL(tmp) */
1259: min(time_ip_date_id), max(time_ip_date_id),
1260: min(time_pr_date_id), max(time_pr_date_id)
1275:
1276: FII_UTIL.Stop_Timer;
1277: FII_UTIL.Print_Timer('Retrieved the time boundary ' || l_min || ' - ' || l_max || ' in ');
1278:
1279: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1280: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1281: FII_UTIL.Start_Timer;
1282:
1283: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1276: FII_UTIL.Stop_Timer;
1277: FII_UTIL.Print_Timer('Retrieved the time boundary ' || l_min || ' - ' || l_max || ' in ');
1278:
1279: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1280: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1281: FII_UTIL.Start_Timer;
1282:
1283: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1284:
1282:
1283: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1284:
1285: IF (l_is_missing) THEN
1286: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');
1287: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1288:
1289: SELECT min(report_date), max(report_date)
1290: INTO l_time_min, l_time_max
1283: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1284:
1285: IF (l_is_missing) THEN
1286: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');
1287: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1288:
1289: SELECT min(report_date), max(report_date)
1290: INTO l_time_min, l_time_max
1291: FROM fii_time_day;
1292:
1293: OPEN lines_missing_date;
1294: FETCH lines_missing_date INTO l_line;
1295:
1296: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1297: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1298: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_DETAIL'),18,' ')
1299: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_IP_DATE'),19,' ')
1300: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PR_DATE'),19,' '));
1293: OPEN lines_missing_date;
1294: FETCH lines_missing_date INTO l_line;
1295:
1296: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1297: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1298: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_DETAIL'),18,' ')
1299: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_IP_DATE'),19,' ')
1300: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PR_DATE'),19,' '));
1301: BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------- - ------------------');
1294: FETCH lines_missing_date INTO l_line;
1295:
1296: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1297: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1298: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_DETAIL'),18,' ')
1299: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_IP_DATE'),19,' ')
1300: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PR_DATE'),19,' '));
1301: BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------- - ------------------');
1302:
1297: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1298: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_DETAIL'),18,' ')
1299: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_IP_DATE'),19,' ')
1300: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PR_DATE'),19,' '));
1301: BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------- - ------------------');
1302:
1303: WHILE LINES_MISSING_DATE%FOUND LOOP
1304: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_line.delivery_detail_id,18,' ')
1305: ||' - '||RPAD(l_line.time_ip_date_id,19,' ')
1300: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PR_DATE'),19,' '));
1301: BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------- - ------------------');
1302:
1303: WHILE LINES_MISSING_DATE%FOUND LOOP
1304: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_line.delivery_detail_id,18,' ')
1305: ||' - '||RPAD(l_line.time_ip_date_id,19,' ')
1306: ||' - '||RPAD(nvl(l_line.time_pr_date_id,' '),19,' '));
1307: FETCH Lines_Missing_Date INTO l_line;
1308: END LOOP;
1307: FETCH Lines_Missing_Date INTO l_line;
1308: END LOOP;
1309:
1310: CLOSE LINES_MISSING_DATE;
1311: BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------------------------------------------+');
1312:
1313: OPEN stops_missing_date;
1314: FETCH stops_missing_date INTO l_stop;
1315:
1312:
1313: OPEN stops_missing_date;
1314: FETCH stops_missing_date INTO l_stop;
1315:
1316: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1317: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1318: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_TRIP_ID'),18,' ')
1319: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_STOP_ID'),18,' ')
1320: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_ARRL_DATE'),19,' ')
1313: OPEN stops_missing_date;
1314: FETCH stops_missing_date INTO l_stop;
1315:
1316: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1317: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1318: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_TRIP_ID'),18,' ')
1319: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_STOP_ID'),18,' ')
1320: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_ARRL_DATE'),19,' ')
1321: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_DEPT_DATE'),21,' ')
1314: FETCH stops_missing_date INTO l_stop;
1315:
1316: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1317: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1318: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_TRIP_ID'),18,' ')
1319: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_STOP_ID'),18,' ')
1320: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_ARRL_DATE'),19,' ')
1321: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_DEPT_DATE'),21,' ')
1322: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PLN_ARRL_DATE'),20,' '));
1319: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_STOP_ID'),18,' ')
1320: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_ARRL_DATE'),19,' ')
1321: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_DEPT_DATE'),21,' ')
1322: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PLN_ARRL_DATE'),20,' '));
1323: BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------ - ------------------- - --------------------- - --------------------');
1324:
1325: WHILE STOPS_MISSING_DATE%FOUND LOOP
1326: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_stop.trip_id,18,' ')
1327: ||' - '||RPAD(l_stop.stop_id,18,' ')
1322: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PLN_ARRL_DATE'),20,' '));
1323: BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------ - ------------------- - --------------------- - --------------------');
1324:
1325: WHILE STOPS_MISSING_DATE%FOUND LOOP
1326: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_stop.trip_id,18,' ')
1327: ||' - '||RPAD(l_stop.stop_id,18,' ')
1328: ||' - '||RPAD(l_stop.time_actl_arrl_date_id,19,' ')
1329: ||' - '||RPAD(l_stop.time_actl_dept_date_id,21,' ')
1330: ||' - '||RPAD(nvl(l_stop.time_pln_arrl_date_id,' '),20,' '));
1331: FETCH STOPS_MISSING_DATE INTO l_stop;
1332: END LOOP;
1333:
1334: CLOSE STOPS_MISSING_DATE;
1335: BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------------------------------------------------------------------+');
1336:
1337: RETURN (-999);
1338: ELSE
1339: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1335: BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------------------------------------------------------------------+');
1336:
1337: RETURN (-999);
1338: ELSE
1339: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1340: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1341: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1342: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1343: END IF;
1336:
1337: RETURN (-999);
1338: ELSE
1339: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1340: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1341: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1342: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1343: END IF;
1344:
1337: RETURN (-999);
1338: ELSE
1339: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1340: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1341: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1342: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1343: END IF;
1344:
1345: FII_UTIL.Stop_Timer;
1338: ELSE
1339: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1340: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1341: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1342: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1343: END IF;
1344:
1345: FII_UTIL.Stop_Timer;
1346: FII_UTIL.Print_Timer('Completed time continuity check in');
1383: l_time_missing := TRUE;
1384:
1385: FII_UTIL.Start_Timer;
1386:
1387: BIS_COLLECTION_UTILITIES.Put_Line('Begin to retrieve the time boundary for the incremental load');
1388:
1389: SELECT min(time_ip_date_id), max(time_ip_date_id),
1390: min(time_pr_date_id), max(time_pr_date_id)
1391: INTO l_min_ip_date, l_max_ip_date, l_min_pr_date, l_max_pr_date
1396:
1397: FII_UTIL.Stop_Timer;
1398: FII_UTIL.Print_Timer('Retrieved the time boundary ' || l_min || ' - ' || l_max || ' in ');
1399:
1400: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1401: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1402: FII_UTIL.Start_Timer;
1403:
1404: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1397: FII_UTIL.Stop_Timer;
1398: FII_UTIL.Print_Timer('Retrieved the time boundary ' || l_min || ' - ' || l_max || ' in ');
1399:
1400: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1401: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1402: FII_UTIL.Start_Timer;
1403:
1404: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1405:
1403:
1404: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1405:
1406: IF (l_is_missing) THEN
1407: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');
1408: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1409:
1410: SELECT min(report_date), max(report_date)
1411: INTO l_time_min, l_time_max
1404: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1405:
1406: IF (l_is_missing) THEN
1407: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');
1408: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1409:
1410: SELECT min(report_date), max(report_date)
1411: INTO l_time_min, l_time_max
1412: FROM fii_time_day;
1413:
1414: OPEN lines_missing_date;
1415: FETCH lines_missing_date INTO l_line;
1416:
1417: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1418: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1419: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_DETAIL_ID'),18,' ')
1420: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_IP_DATE'),19,' ')
1421: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PR_DATE'),19,' '));
1414: OPEN lines_missing_date;
1415: FETCH lines_missing_date INTO l_line;
1416:
1417: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1418: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1419: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_DETAIL_ID'),18,' ')
1420: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_IP_DATE'),19,' ')
1421: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PR_DATE'),19,' '));
1422: BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------- - ------------------');
1415: FETCH lines_missing_date INTO l_line;
1416:
1417: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1418: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1419: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_DETAIL_ID'),18,' ')
1420: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_IP_DATE'),19,' ')
1421: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PR_DATE'),19,' '));
1422: BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------- - ------------------');
1423:
1418: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1419: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_DETAIL_ID'),18,' ')
1420: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_IP_DATE'),19,' ')
1421: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PR_DATE'),19,' '));
1422: BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------- - ------------------');
1423:
1424: WHILE LINES_MISSING_DATE%FOUND LOOP
1425: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_line.delivery_detail_id,18,' ')
1426: ||' - '||RPAD(l_line.time_ip_date_id,19,' ')
1421: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PR_DATE'),19,' '));
1422: BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------- - ------------------');
1423:
1424: WHILE LINES_MISSING_DATE%FOUND LOOP
1425: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_line.delivery_detail_id,18,' ')
1426: ||' - '||RPAD(l_line.time_ip_date_id,19,' ')
1427: ||' - '||RPAD(nvl(l_line.time_pr_date_id,' '),19,' '));
1428: FETCH Lines_Missing_Date INTO l_line;
1429: END LOOP;
1428: FETCH Lines_Missing_Date INTO l_line;
1429: END LOOP;
1430:
1431: CLOSE LINES_MISSING_DATE;
1432: BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------------------------------------------+');
1433:
1434: RETURN (-999);
1435: ELSE
1436: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1432: BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------------------------------------------+');
1433:
1434: RETURN (-999);
1435: ELSE
1436: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1437: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1438: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1439: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1440: END IF;
1433:
1434: RETURN (-999);
1435: ELSE
1436: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1437: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1438: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1439: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1440: END IF;
1441:
1434: RETURN (-999);
1435: ELSE
1436: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1437: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1438: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1439: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1440: END IF;
1441:
1442: FII_UTIL.Stop_Timer;
1435: ELSE
1436: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1437: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1438: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1439: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1440: END IF;
1441:
1442: FII_UTIL.Stop_Timer;
1443: FII_UTIL.Print_Timer('Completed time continuity check in');
1483: l_is_missing := TRUE;
1484: l_time_missing := TRUE;
1485:
1486: FII_UTIL.Start_Timer;
1487: BIS_COLLECTION_UTILITIES.Put_Line('Begin to retrieve the time boundary for the incremental load');
1488:
1489: SELECT min(time_actl_arrl_date_id), max(time_actl_arrl_date_id),
1490: min(time_init_dept_date_id), max(time_init_dept_date_id),
1491: min(time_pln_arrl_date_id), max(time_pln_arrl_date_id)
1497:
1498: FII_UTIL.Stop_Timer;
1499: FII_UTIL.Print_Timer('Retrieved the time boundary ' || l_min || ' - ' || l_max || ' in ');
1500:
1501: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1502: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1503: FII_UTIL.Start_Timer;
1504:
1505: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1498: FII_UTIL.Stop_Timer;
1499: FII_UTIL.Print_Timer('Retrieved the time boundary ' || l_min || ' - ' || l_max || ' in ');
1500:
1501: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1502: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1503: FII_UTIL.Start_Timer;
1504:
1505: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1506:
1504:
1505: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1506:
1507: IF (l_is_missing) THEN
1508: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');
1509: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1510:
1511: SELECT min(report_date), max(report_date)
1512: INTO l_time_min, l_time_max
1505: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1506:
1507: IF (l_is_missing) THEN
1508: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');
1509: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1510:
1511: SELECT min(report_date), max(report_date)
1512: INTO l_time_min, l_time_max
1513: FROM fii_time_day;
1514:
1515: OPEN stops_missing_date;
1516: FETCH stops_missing_date INTO l_stop;
1517:
1518: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1519: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1520: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_TRIP_ID'),18,' ')
1521: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_STOP_ID'),18,' ')
1522: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_ARRL_DATE'),19,' ')
1515: OPEN stops_missing_date;
1516: FETCH stops_missing_date INTO l_stop;
1517:
1518: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1519: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1520: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_TRIP_ID'),18,' ')
1521: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_STOP_ID'),18,' ')
1522: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_ARRL_DATE'),19,' ')
1523: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_DEPT_DATE'),21,' ')
1516: FETCH stops_missing_date INTO l_stop;
1517:
1518: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1519: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1520: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_TRIP_ID'),18,' ')
1521: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_STOP_ID'),18,' ')
1522: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_ARRL_DATE'),19,' ')
1523: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_DEPT_DATE'),21,' ')
1524: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PLN_ARRL_DATE'),20,' '));
1521: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_STOP_ID'),18,' ')
1522: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_ARRL_DATE'),19,' ')
1523: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ACTL_DEPT_DATE'),21,' ')
1524: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PLN_ARRL_DATE'),20,' '));
1525: BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------ - ------------------- - --------------------- - --------------------');
1526:
1527: WHILE STOPS_MISSING_DATE%FOUND LOOP
1528: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_stop.trip_id,18,' ')
1529: ||' - '||RPAD(l_stop.stop_id,18,' ')
1524: ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_PLN_ARRL_DATE'),20,' '));
1525: BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------ - ------------------- - --------------------- - --------------------');
1526:
1527: WHILE STOPS_MISSING_DATE%FOUND LOOP
1528: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_stop.trip_id,18,' ')
1529: ||' - '||RPAD(l_stop.stop_id,18,' ')
1530: ||' - '||RPAD(l_stop.time_actl_arrl_date_id,19,' ')
1531: ||' - '||RPAD(l_stop.time_actl_dept_date_id,21,' ')
1532: ||' - '||RPAD(nvl(l_stop.time_pln_arrl_date_id,' '),20,' '));
1533: FETCH STOPS_MISSING_DATE INTO l_stop;
1534: END LOOP;
1535:
1536: CLOSE STOPS_MISSING_DATE;
1537: BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------------------------------------------------------------------+');
1538:
1539: RETURN (-999);
1540: ELSE
1541: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1537: BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------------------------------------------------------------------+');
1538:
1539: RETURN (-999);
1540: ELSE
1541: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1542: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1543: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1544: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1545: END IF;
1538:
1539: RETURN (-999);
1540: ELSE
1541: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1542: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1543: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1544: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1545: END IF;
1546:
1539: RETURN (-999);
1540: ELSE
1541: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1542: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1543: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1544: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1545: END IF;
1546:
1547: FII_UTIL.Stop_Timer;
1540: ELSE
1541: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1542: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1543: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1544: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1545: END IF;
1546:
1547: FII_UTIL.Stop_Timer;
1548: FII_UTIL.Print_Timer('Completed time continuity check in');
1592: OPEN dangling_items_init;
1593: FETCH dangling_items_init INTO l_item, l_org;
1594:
1595: IF dangling_items_init%ROWCOUNT <> 0 THEN
1596: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1597: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1598:
1599: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1600: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1593: FETCH dangling_items_init INTO l_item, l_org;
1594:
1595: IF dangling_items_init%ROWCOUNT <> 0 THEN
1596: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1597: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1598:
1599: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1600: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1601: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1595: IF dangling_items_init%ROWCOUNT <> 0 THEN
1596: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1597: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1598:
1599: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1600: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1601: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1602: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1603: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1596: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1597: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1598:
1599: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1600: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1601: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1602: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1603: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1604: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1597: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1598:
1599: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1600: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1601: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1602: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1603: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1604: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1605:
1598:
1599: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1600: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1601: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1602: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1603: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1604: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1605:
1606: WHILE Dangling_Items_Init%FOUND LOOP
1599: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1600: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1601: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1602: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1603: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1604: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1605:
1606: WHILE Dangling_Items_Init%FOUND LOOP
1607: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,23,' ')||' - '||RPAD(l_org,20,' '));
1600: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1601: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1602: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1603: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1604: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1605:
1606: WHILE Dangling_Items_Init%FOUND LOOP
1607: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,23,' ')||' - '||RPAD(l_org,20,' '));
1608: FETCH Dangling_Items_Init INTO l_item, l_org;
1603: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1604: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1605:
1606: WHILE Dangling_Items_Init%FOUND LOOP
1607: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,23,' ')||' - '||RPAD(l_org,20,' '));
1608: FETCH Dangling_Items_Init INTO l_item, l_org;
1609: END LOOP;
1610: BIS_COLLECTION_UTILITIES.Put_Line_Out('+--------------------------------------------+');
1611: ELSE
1606: WHILE Dangling_Items_Init%FOUND LOOP
1607: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,23,' ')||' - '||RPAD(l_org,20,' '));
1608: FETCH Dangling_Items_Init INTO l_item, l_org;
1609: END LOOP;
1610: BIS_COLLECTION_UTILITIES.Put_Line_Out('+--------------------------------------------+');
1611: ELSE
1612: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1613: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1614: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1608: FETCH Dangling_Items_Init INTO l_item, l_org;
1609: END LOOP;
1610: BIS_COLLECTION_UTILITIES.Put_Line_Out('+--------------------------------------------+');
1611: ELSE
1612: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1613: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1614: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1615: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1616: END IF;
1609: END LOOP;
1610: BIS_COLLECTION_UTILITIES.Put_Line_Out('+--------------------------------------------+');
1611: ELSE
1612: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1613: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1614: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1615: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1616: END IF;
1617: l_total := Dangling_Items_Init%ROWCOUNT;
1610: BIS_COLLECTION_UTILITIES.Put_Line_Out('+--------------------------------------------+');
1611: ELSE
1612: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1613: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1614: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1615: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1616: END IF;
1617: l_total := Dangling_Items_Init%ROWCOUNT;
1618: CLOSE Dangling_Items_Init;
1611: ELSE
1612: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1613: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1614: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1615: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1616: END IF;
1617: l_total := Dangling_Items_Init%ROWCOUNT;
1618: CLOSE Dangling_Items_Init;
1619:
1621: OPEN dangling_items_incre;
1622: FETCH dangling_items_incre INTO l_item, l_org;
1623:
1624: IF dangling_items_incre%ROWCOUNT <> 0 THEN
1625: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1626: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1627:
1628: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1629: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1622: FETCH dangling_items_incre INTO l_item, l_org;
1623:
1624: IF dangling_items_incre%ROWCOUNT <> 0 THEN
1625: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1626: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1627:
1628: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1629: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1630: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1624: IF dangling_items_incre%ROWCOUNT <> 0 THEN
1625: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1626: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1627:
1628: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1629: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1630: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1631: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1632: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1625: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1626: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1627:
1628: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1629: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1630: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1631: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1632: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1633: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1626: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1627:
1628: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1629: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1630: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1631: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1632: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1633: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1634:
1627:
1628: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1629: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1630: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1631: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1632: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1633: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1634:
1635: WHILE Dangling_Items_Incre%FOUND LOOP
1628: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1629: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1630: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1631: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1632: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1633: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1634:
1635: WHILE Dangling_Items_Incre%FOUND LOOP
1636: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,23,' ')||' - '||RPAD(l_org,20,' '));
1629: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1630: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1631: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1632: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1633: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1634:
1635: WHILE Dangling_Items_Incre%FOUND LOOP
1636: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,23,' ')||' - '||RPAD(l_org,20,' '));
1637: FETCH Dangling_Items_Incre INTO l_item, l_org;
1632: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1633: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1634:
1635: WHILE Dangling_Items_Incre%FOUND LOOP
1636: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,23,' ')||' - '||RPAD(l_org,20,' '));
1637: FETCH Dangling_Items_Incre INTO l_item, l_org;
1638: END LOOP;
1639: BIS_COLLECTION_UTILITIES.Put_Line_Out('+--------------------------------------------+');
1640: ELSE
1635: WHILE Dangling_Items_Incre%FOUND LOOP
1636: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,23,' ')||' - '||RPAD(l_org,20,' '));
1637: FETCH Dangling_Items_Incre INTO l_item, l_org;
1638: END LOOP;
1639: BIS_COLLECTION_UTILITIES.Put_Line_Out('+--------------------------------------------+');
1640: ELSE
1641: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1642: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1643: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1637: FETCH Dangling_Items_Incre INTO l_item, l_org;
1638: END LOOP;
1639: BIS_COLLECTION_UTILITIES.Put_Line_Out('+--------------------------------------------+');
1640: ELSE
1641: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1642: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1643: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1644: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1645: END IF;
1638: END LOOP;
1639: BIS_COLLECTION_UTILITIES.Put_Line_Out('+--------------------------------------------+');
1640: ELSE
1641: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1642: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1643: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1644: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1645: END IF;
1646: l_total := Dangling_Items_Incre%ROWCOUNT;
1639: BIS_COLLECTION_UTILITIES.Put_Line_Out('+--------------------------------------------+');
1640: ELSE
1641: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1642: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1643: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1644: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1645: END IF;
1646: l_total := Dangling_Items_Incre%ROWCOUNT;
1647: CLOSE Dangling_Items_Incre;
1640: ELSE
1641: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1642: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1643: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1644: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1645: END IF;
1646: l_total := Dangling_Items_Incre%ROWCOUNT;
1647: CLOSE Dangling_Items_Incre;
1648: END IF;
1699: FETCH Missing_Currency_Conversion INTO l_record;
1700:
1701: IF Missing_Currency_Conversion%ROWCOUNT <> 0
1702: THEN
1703: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing currency conversion rates.');
1704: BIS_COLLECTION_UTILITIES.Put_Line(fnd_message.get_string('BIS', 'BIS_DBI_CURR_NO_LOAD'));
1705:
1706: BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
1707: WHILE Missing_Currency_Conversion%FOUND LOOP
1700:
1701: IF Missing_Currency_Conversion%ROWCOUNT <> 0
1702: THEN
1703: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing currency conversion rates.');
1704: BIS_COLLECTION_UTILITIES.Put_Line(fnd_message.get_string('BIS', 'BIS_DBI_CURR_NO_LOAD'));
1705:
1706: BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
1707: WHILE Missing_Currency_Conversion%FOUND LOOP
1708: l_total := l_total + 1;
1702: THEN
1703: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing currency conversion rates.');
1704: BIS_COLLECTION_UTILITIES.Put_Line(fnd_message.get_string('BIS', 'BIS_DBI_CURR_NO_LOAD'));
1705:
1706: BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
1707: WHILE Missing_Currency_Conversion%FOUND LOOP
1708: l_total := l_total + 1;
1709: BIS_COLLECTION_UTILITIES.writeMissingRate(
1710: l_record.rate_type,
1705:
1706: BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
1707: WHILE Missing_Currency_Conversion%FOUND LOOP
1708: l_total := l_total + 1;
1709: BIS_COLLECTION_UTILITIES.writeMissingRate(
1710: l_record.rate_type,
1711: l_record.from_currency,
1712: l_record.to_currency,
1713: l_record.curr_conv_date);
1712: l_record.to_currency,
1713: l_record.curr_conv_date);
1714: FETCH Missing_Currency_Conversion INTO l_record;
1715: END LOOP;
1716: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1717: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1718:
1719: ELSE -- Missing_Currency_Conversion%ROWCOUNT = 0
1720: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1713: l_record.curr_conv_date);
1714: FETCH Missing_Currency_Conversion INTO l_record;
1715: END LOOP;
1716: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1717: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1718:
1719: ELSE -- Missing_Currency_Conversion%ROWCOUNT = 0
1720: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1721: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');
1716: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1717: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1718:
1719: ELSE -- Missing_Currency_Conversion%ROWCOUNT = 0
1720: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1721: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');
1722: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1723: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1724: END IF; -- Missing_Currency_Conversion%ROWCOUNT <> 0
1717: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1718:
1719: ELSE -- Missing_Currency_Conversion%ROWCOUNT = 0
1720: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1721: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');
1722: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1723: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1724: END IF; -- Missing_Currency_Conversion%ROWCOUNT <> 0
1725:
1718:
1719: ELSE -- Missing_Currency_Conversion%ROWCOUNT = 0
1720: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1721: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');
1722: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1723: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1724: END IF; -- Missing_Currency_Conversion%ROWCOUNT <> 0
1725:
1726: CLOSE Missing_Currency_Conversion;
1719: ELSE -- Missing_Currency_Conversion%ROWCOUNT = 0
1720: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1721: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');
1722: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1723: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1724: END IF; -- Missing_Currency_Conversion%ROWCOUNT <> 0
1725:
1726: CLOSE Missing_Currency_Conversion;
1727:
1765: OPEN Missing_UOM_Conversion;
1766: FETCH Missing_UOM_Conversion INTO l_record;
1767:
1768: IF Missing_UOM_Conversion%ROWCOUNT <> 0 THEN
1769: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing UOM conversion rates.');
1770: BIS_COLLECTION_UTILITIES.Put_Line(fnd_message.get_string('BIS', 'BIS_DBI_UOM_NO_LOAD'));
1771:
1772: OPI_DBI_REP_UOM_PKG.err_msg_header;
1773: WHILE Missing_UOM_Conversion%FOUND LOOP
1766: FETCH Missing_UOM_Conversion INTO l_record;
1767:
1768: IF Missing_UOM_Conversion%ROWCOUNT <> 0 THEN
1769: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing UOM conversion rates.');
1770: BIS_COLLECTION_UTILITIES.Put_Line(fnd_message.get_string('BIS', 'BIS_DBI_UOM_NO_LOAD'));
1771:
1772: OPI_DBI_REP_UOM_PKG.err_msg_header;
1773: WHILE Missing_UOM_Conversion%FOUND LOOP
1774: l_total := l_total + 1;
1781: END LOOP;
1782: OPI_DBI_REP_UOM_PKG.Err_Msg_Footer;
1783:
1784: ELSE -- Missing_UOM_Conversion%ROWCOUNT = 0
1785: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1786: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING UOM CONVERSION RATE ');
1787: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1788: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1789: END IF; -- Missing_UOM_Conversion%ROWCOUNT <> 0
1782: OPI_DBI_REP_UOM_PKG.Err_Msg_Footer;
1783:
1784: ELSE -- Missing_UOM_Conversion%ROWCOUNT = 0
1785: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1786: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING UOM CONVERSION RATE ');
1787: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1788: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1789: END IF; -- Missing_UOM_Conversion%ROWCOUNT <> 0
1790:
1783:
1784: ELSE -- Missing_UOM_Conversion%ROWCOUNT = 0
1785: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1786: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING UOM CONVERSION RATE ');
1787: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1788: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1789: END IF; -- Missing_UOM_Conversion%ROWCOUNT <> 0
1790:
1791: CLOSE Missing_UOM_Conversion;
1784: ELSE -- Missing_UOM_Conversion%ROWCOUNT = 0
1785: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1786: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING UOM CONVERSION RATE ');
1787: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1788: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1789: END IF; -- Missing_UOM_Conversion%ROWCOUNT <> 0
1790:
1791: CLOSE Missing_UOM_Conversion;
1792:
1793: OPEN Missing_Transaction_UOM;
1794: FETCH Missing_Transaction_UOM INTO l_uom_record;
1795:
1796: IF Missing_Transaction_UOM%ROWCOUNT <> 0 THEN
1797: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing UOM conversion rates.');
1798: g_errbuf := g_errbuf || 'There are transactions that do not have transaction UOMs.';
1799:
1800: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1801: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_UOM_NO_LOAD'));
1796: IF Missing_Transaction_UOM%ROWCOUNT <> 0 THEN
1797: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing UOM conversion rates.');
1798: g_errbuf := g_errbuf || 'There are transactions that do not have transaction UOMs.';
1799:
1800: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1801: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_UOM_NO_LOAD'));
1802: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1803: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_NAME'),21,' '));
1804:
1797: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing UOM conversion rates.');
1798: g_errbuf := g_errbuf || 'There are transactions that do not have transaction UOMs.';
1799:
1800: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1801: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_UOM_NO_LOAD'));
1802: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1803: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_NAME'),21,' '));
1804:
1805: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------');
1798: g_errbuf := g_errbuf || 'There are transactions that do not have transaction UOMs.';
1799:
1800: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1801: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_UOM_NO_LOAD'));
1802: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1803: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_NAME'),21,' '));
1804:
1805: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------');
1806:
1799:
1800: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1801: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_UOM_NO_LOAD'));
1802: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1803: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_NAME'),21,' '));
1804:
1805: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------');
1806:
1807: WHILE Missing_Transaction_UOM%FOUND LOOP
1801: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_UOM_NO_LOAD'));
1802: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1803: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_DELIVERY_NAME'),21,' '));
1804:
1805: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------');
1806:
1807: WHILE Missing_Transaction_UOM%FOUND LOOP
1808: l_total := l_total + 1;
1809: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_uom_record.name,16,' '));
1805: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------');
1806:
1807: WHILE Missing_Transaction_UOM%FOUND LOOP
1808: l_total := l_total + 1;
1809: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_uom_record.name,16,' '));
1810:
1811: FETCH Missing_Transaction_UOM INTO l_uom_record;
1812: END LOOP;
1813: BIS_COLLECTION_UTILITIES.Put_Line_Out('+---------------------------------------------------------------------------+');
1809: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_uom_record.name,16,' '));
1810:
1811: FETCH Missing_Transaction_UOM INTO l_uom_record;
1812: END LOOP;
1813: BIS_COLLECTION_UTILITIES.Put_Line_Out('+---------------------------------------------------------------------------+');
1814: END IF;
1815:
1816: CLOSE Missing_Transaction_UOM;
1817:
1848: -- Identify Missing Currency Rate
1849: -- When there is missing rate, exit the collection with error
1850: -- ----------------------------------------------------------
1851:
1852: BIS_COLLECTION_UTILITIES.put_line(' ');
1853: BIS_COLLECTION_UTILITIES.put_line('Identifying the missing currency conversion rates');
1854: FII_UTIL.Start_Timer;
1855:
1856: l_miss_conv := REPORT_MISSING_RATE;
1849: -- When there is missing rate, exit the collection with error
1850: -- ----------------------------------------------------------
1851:
1852: BIS_COLLECTION_UTILITIES.put_line(' ');
1853: BIS_COLLECTION_UTILITIES.put_line('Identifying the missing currency conversion rates');
1854: FII_UTIL.Start_Timer;
1855:
1856: l_miss_conv := REPORT_MISSING_RATE;
1857:
1869: -- Identify Missing UOM Rate
1870: -- When there is missing UOM rate, exit the collection with error
1871: -- --------------------------------------------------------------
1872:
1873: BIS_COLLECTION_UTILITIES.put_line(' ');
1874: BIS_COLLECTION_UTILITIES.put_line('Identifying the missing UOM conversion rates');
1875: FII_UTIL.Start_Timer;
1876:
1877: l_miss_uom := REPORT_MISSING_UOM_RATE;
1870: -- When there is missing UOM rate, exit the collection with error
1871: -- --------------------------------------------------------------
1872:
1873: BIS_COLLECTION_UTILITIES.put_line(' ');
1874: BIS_COLLECTION_UTILITIES.put_line('Identifying the missing UOM conversion rates');
1875: FII_UTIL.Start_Timer;
1876:
1877: l_miss_uom := REPORT_MISSING_UOM_RATE;
1878:
1889: -- ---------------------
1890: -- CHECK_TIME_CONTINUITY
1891: -- ---------------------
1892:
1893: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1894: BIS_COLLECTION_UTILITIES.put_line('Checking Time Continuity');
1895:
1896: l_time_danling := CHECK_TIME_CONTINUITY_INIT;
1897:
1890: -- CHECK_TIME_CONTINUITY
1891: -- ---------------------
1892:
1893: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1894: BIS_COLLECTION_UTILITIES.put_line('Checking Time Continuity');
1895:
1896: l_time_danling := CHECK_TIME_CONTINUITY_INIT;
1897:
1898: IF (l_time_danling = -1) THEN
1905: -- -------------------------------------
1906: -- Check Dangling Key for Item Dimension
1907: -- -------------------------------------
1908:
1909: BIS_COLLECTION_UTILITIES.put_line(' ');
1910: BIS_COLLECTION_UTILITIES.put_line('Identifying the dangling items');
1911:
1912: FII_UTIL.Start_Timer;
1913:
1906: -- Check Dangling Key for Item Dimension
1907: -- -------------------------------------
1908:
1909: BIS_COLLECTION_UTILITIES.put_line(' ');
1910: BIS_COLLECTION_UTILITIES.put_line('Identifying the dangling items');
1911:
1912: FII_UTIL.Start_Timer;
1913:
1914: l_item_count := IDENTIFY_DANGLING_ITEM;
1952: -- ---------------------
1953: -- CHECK_TIME_CONTINUITY
1954: -- ---------------------
1955:
1956: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1957: BIS_COLLECTION_UTILITIES.put_line('Checking Time Continuity');
1958:
1959: l_time_danling := CHECK_TIME_CONTINUITY_DETAIL;
1960:
1953: -- CHECK_TIME_CONTINUITY
1954: -- ---------------------
1955:
1956: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1957: BIS_COLLECTION_UTILITIES.put_line('Checking Time Continuity');
1958:
1959: l_time_danling := CHECK_TIME_CONTINUITY_DETAIL;
1960:
1961: IF (l_time_danling = -1) THEN
1968: -- -------------------------------------
1969: -- Check Dangling Key for Item Dimension
1970: -- -------------------------------------
1971:
1972: BIS_COLLECTION_UTILITIES.put_line(' ');
1973: BIS_COLLECTION_UTILITIES.put_line('Identifying the dangling items');
1974:
1975: FII_UTIL.Start_Timer;
1976:
1969: -- Check Dangling Key for Item Dimension
1970: -- -------------------------------------
1971:
1972: BIS_COLLECTION_UTILITIES.put_line(' ');
1973: BIS_COLLECTION_UTILITIES.put_line('Identifying the dangling items');
1974:
1975: FII_UTIL.Start_Timer;
1976:
1977: l_item_count := IDENTIFY_DANGLING_ITEM;
2019: -- Identify Missing Currency Rate
2020: -- When there is missing rate, exit the collection with error
2021: -- ----------------------------------------------------------
2022:
2023: BIS_COLLECTION_UTILITIES.put_line(' ');
2024: BIS_COLLECTION_UTILITIES.put_line('Identifying the missing currency conversion rates');
2025: FII_UTIL.Start_Timer;
2026:
2027: l_miss_conv := REPORT_MISSING_RATE;
2020: -- When there is missing rate, exit the collection with error
2021: -- ----------------------------------------------------------
2022:
2023: BIS_COLLECTION_UTILITIES.put_line(' ');
2024: BIS_COLLECTION_UTILITIES.put_line('Identifying the missing currency conversion rates');
2025: FII_UTIL.Start_Timer;
2026:
2027: l_miss_conv := REPORT_MISSING_RATE;
2028:
2040: -- Identify Missing UOM Rate
2041: -- When there is missing UOM rate, exit the collection with error
2042: -- --------------------------------------------------------------
2043:
2044: BIS_COLLECTION_UTILITIES.put_line(' ');
2045: BIS_COLLECTION_UTILITIES.put_line('Identifying the missing UOM conversion rates');
2046: FII_UTIL.Start_Timer;
2047:
2048: l_miss_uom := REPORT_MISSING_UOM_RATE;
2041: -- When there is missing UOM rate, exit the collection with error
2042: -- --------------------------------------------------------------
2043:
2044: BIS_COLLECTION_UTILITIES.put_line(' ');
2045: BIS_COLLECTION_UTILITIES.put_line('Identifying the missing UOM conversion rates');
2046: FII_UTIL.Start_Timer;
2047:
2048: l_miss_uom := REPORT_MISSING_UOM_RATE;
2049:
2060: -- ---------------------
2061: -- CHECK_TIME_CONTINUITY
2062: -- ---------------------
2063:
2064: BIS_COLLECTION_UTILITIES.Put_Line(' ');
2065: BIS_COLLECTION_UTILITIES.put_line('Checking Time Continuity');
2066:
2067: l_time_danling := CHECK_TIME_CONTINUITY_STOP;
2068:
2061: -- CHECK_TIME_CONTINUITY
2062: -- ---------------------
2063:
2064: BIS_COLLECTION_UTILITIES.Put_Line(' ');
2065: BIS_COLLECTION_UTILITIES.put_line('Checking Time Continuity');
2066:
2067: l_time_danling := CHECK_TIME_CONTINUITY_STOP;
2068:
2069: IF (l_time_danling = -1) THEN
2104: -- Identify Missing Currency Rate
2105: -- When there is missing rate, exit the collection with error
2106: -- ----------------------------------------------------------
2107:
2108: BIS_COLLECTION_UTILITIES.put_line(' ');
2109: BIS_COLLECTION_UTILITIES.put_line('Identifying the missing currency conversion rates');
2110: FII_UTIL.Start_Timer;
2111:
2112: l_miss_conv := REPORT_MISSING_RATE;
2105: -- When there is missing rate, exit the collection with error
2106: -- ----------------------------------------------------------
2107:
2108: BIS_COLLECTION_UTILITIES.put_line(' ');
2109: BIS_COLLECTION_UTILITIES.put_line('Identifying the missing currency conversion rates');
2110: FII_UTIL.Start_Timer;
2111:
2112: l_miss_conv := REPORT_MISSING_RATE;
2113:
2147: l_invoice_count NUMBER;
2148:
2149: BEGIN
2150:
2151: BIS_COLLECTION_UTILITIES.put_line(' ');
2152: BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_del_details_f');
2153: FII_UTIL.Start_Timer;
2154:
2155: INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_DEL_DETAILS_F F
2148:
2149: BEGIN
2150:
2151: BIS_COLLECTION_UTILITIES.put_line(' ');
2152: BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_del_details_f');
2153: FII_UTIL.Start_Timer;
2154:
2155: INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_DEL_DETAILS_F F
2156: (DELIVERY_DETAIL_ID,
2209: l_detail_count := sql%rowcount;
2210: FII_UTIL.Stop_Timer;
2211: FII_UTIL.Print_Timer('Inserted '|| l_detail_count ||' rows into isc_dbi_del_details_f in');
2212:
2213: BIS_COLLECTION_UTILITIES.put_line(' ');
2214: BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_del_legs_f');
2215: FII_UTIL.Start_Timer;
2216:
2217: INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_DEL_LEGS_F F
2210: FII_UTIL.Stop_Timer;
2211: FII_UTIL.Print_Timer('Inserted '|| l_detail_count ||' rows into isc_dbi_del_details_f in');
2212:
2213: BIS_COLLECTION_UTILITIES.put_line(' ');
2214: BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_del_legs_f');
2215: FII_UTIL.Start_Timer;
2216:
2217: INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_DEL_LEGS_F F
2218: (DELIVERY_LEG_ID,
2297: l_leg_count := sql%rowcount;
2298: FII_UTIL.Stop_Timer;
2299: FII_UTIL.Print_Timer('Inserted '|| l_leg_count ||' rows into isc_dbi_del_legs_f in');
2300:
2301: BIS_COLLECTION_UTILITIES.put_line(' ');
2302: BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_trip_stops_f');
2303: FII_UTIL.Start_Timer;
2304:
2305: INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_TRIP_STOPS_F F
2298: FII_UTIL.Stop_Timer;
2299: FII_UTIL.Print_Timer('Inserted '|| l_leg_count ||' rows into isc_dbi_del_legs_f in');
2300:
2301: BIS_COLLECTION_UTILITIES.put_line(' ');
2302: BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_trip_stops_f');
2303: FII_UTIL.Start_Timer;
2304:
2305: INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_TRIP_STOPS_F F
2306: (STOP_ID,
2381: l_stop_count := sql%rowcount;
2382: FII_UTIL.Stop_Timer;
2383: FII_UTIL.Print_Timer('Inserted '|| l_stop_count ||' rows into isc_dbi_trip_stops_f in');
2384:
2385: BIS_COLLECTION_UTILITIES.put_line(' ');
2386: BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_fte_invoices_f');
2387: FII_UTIL.Start_Timer;
2388:
2389: INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_FTE_INVOICES_F F
2382: FII_UTIL.Stop_Timer;
2383: FII_UTIL.Print_Timer('Inserted '|| l_stop_count ||' rows into isc_dbi_trip_stops_f in');
2384:
2385: BIS_COLLECTION_UTILITIES.put_line(' ');
2386: BIS_COLLECTION_UTILITIES.put_line('Inserting data into isc_dbi_fte_invoices_f');
2387: FII_UTIL.Start_Timer;
2388:
2389: INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_DBI_FTE_INVOICES_F F
2390: (INVOICE_HEADER_ID,
2490:
2491: FOR v_batch_id IN 1..p_batch
2492: LOOP
2493: FII_UTIL.Start_Timer;
2494: BIS_COLLECTION_UTILITIES.put_line('Merging batch '||v_batch_id);
2495:
2496: l_count := 0;
2497:
2498: MERGE INTO ISC_DBI_DEL_DETAILS_F f
2617:
2618: FOR v_batch_id IN 1..p_batch
2619: LOOP
2620: FII_UTIL.Start_Timer;
2621: BIS_COLLECTION_UTILITIES.put_line('Merging batch '||v_batch_id);
2622:
2623: l_count := 0;
2624:
2625: MERGE INTO ISC_DBI_DEL_LEGS_F f
2945:
2946: FOR v_batch_id IN 1..p_batch
2947: LOOP
2948: FII_UTIL.Start_Timer;
2949: BIS_COLLECTION_UTILITIES.put_line('Merging batch '||v_batch_id);
2950:
2951: l_count := 0;
2952:
2953: MERGE INTO ISC_DBI_FTE_INVOICES_F f
3107: -- ------------------------
3108: -- Truncate temp tables
3109: -- ------------------------
3110:
3111: BIS_COLLECTION_UTILITIES.put_line('Truncating the temp tables');
3112: FII_UTIL.Start_Timer;
3113:
3114: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_WDD_LOG';
3115: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_WTS_LOG';
3123: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_FTE_UOM_RATES';
3124:
3125: FII_UTIL.Stop_Timer;
3126: FII_UTIL.Print_Timer('Truncated the temp tables in');
3127: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3128:
3129: -- ----------------------------------------------
3130: -- No exception raised so far. Successful. Call
3131: -- Wrapup to commit and insert messages into logs
3130: -- No exception raised so far. Successful. Call
3131: -- Wrapup to commit and insert messages into logs
3132: -- ----------------------------------------------
3133:
3134: BIS_COLLECTION_UTILITIES.WRAPUP(
3135: TRUE,
3136: g_row_count,
3137: NULL,
3138: ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3167: errbuf := NULL;
3168: retcode := '0';
3169: g_load_mode := 'INITIAL';
3170:
3171: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3172: BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3173:
3174: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_WSH_FTE_F')) THEN
3175: RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3168: retcode := '0';
3169: g_load_mode := 'INITIAL';
3170:
3171: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3172: BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3173:
3174: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_WSH_FTE_F')) THEN
3175: RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3176: return;
3170:
3171: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3172: BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3173:
3174: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_WSH_FTE_F')) THEN
3175: RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3176: return;
3177: END IF;
3178:
3190:
3191: ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date := g_global_start_date;
3192: ISC_DBI_WSH_FTE_OBJECTS_C.g_push_to_date := sysdate;
3193:
3194: BIS_COLLECTION_UTILITIES.put_line( 'The collection date range is from '||
3195: to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
3196: to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
3197: BIS_COLLECTION_UTILITIES.put_line(' ');
3198:
3193:
3194: BIS_COLLECTION_UTILITIES.put_line( 'The collection date range is from '||
3195: to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
3196: to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
3197: BIS_COLLECTION_UTILITIES.put_line(' ');
3198:
3199: EXECUTE IMMEDIATE 'alter session set hash_area_size=104857600';
3200: EXECUTE IMMEDIATE 'alter session set sort_area_size=104857600';
3201:
3213: THEN RAISE l_failure;
3214: ELSIF (l_row_count = 0) THEN
3215:
3216: -- Fix bug 4150188
3217: BIS_COLLECTION_UTILITIES.put_line(' ');
3218: BIS_COLLECTION_UTILITIES.put_line('Truncating the fact tables');
3219: FII_UTIL.Start_Timer;
3220:
3221: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_DEL_DETAILS_F';
3214: ELSIF (l_row_count = 0) THEN
3215:
3216: -- Fix bug 4150188
3217: BIS_COLLECTION_UTILITIES.put_line(' ');
3218: BIS_COLLECTION_UTILITIES.put_line('Truncating the fact tables');
3219: FII_UTIL.Start_Timer;
3220:
3221: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_DEL_DETAILS_F';
3222: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_DEL_LEGS_F';
3233: -- --------------
3234: -- Analyze tables
3235: -- --------------
3236:
3237: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3238: BIS_COLLECTION_UTILITIES.Put_Line('Analyzing temp tables');
3239: FII_UTIL.Start_Timer;
3240:
3241: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3234: -- Analyze tables
3235: -- --------------
3236:
3237: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3238: BIS_COLLECTION_UTILITIES.Put_Line('Analyzing temp tables');
3239: FII_UTIL.Start_Timer;
3240:
3241: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3242: TABNAME => 'ISC_DBI_TMP_DEL_DETAILS');
3261: -- ----------------------------------------
3262: -- Truncate base summaries for initial load
3263: -- ----------------------------------------
3264:
3265: BIS_COLLECTION_UTILITIES.put_line(' ');
3266: BIS_COLLECTION_UTILITIES.put_line('Truncating the fact tables');
3267: FII_UTIL.Start_Timer;
3268:
3269: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_DEL_DETAILS_F';
3262: -- Truncate base summaries for initial load
3263: -- ----------------------------------------
3264:
3265: BIS_COLLECTION_UTILITIES.put_line(' ');
3266: BIS_COLLECTION_UTILITIES.put_line('Truncating the fact tables');
3267: FII_UTIL.Start_Timer;
3268:
3269: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_DEL_DETAILS_F';
3270: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_DEL_LEGS_F';
3305: EXCEPTION
3306:
3307: WHEN L_FAILURE THEN
3308: ROLLBACK;
3309: BIS_COLLECTION_UTILITIES.put_line(' ');
3310: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3311: retcode := -1;
3312: errbuf := g_errbuf;
3313:
3306:
3307: WHEN L_FAILURE THEN
3308: ROLLBACK;
3309: BIS_COLLECTION_UTILITIES.put_line(' ');
3310: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3311: retcode := -1;
3312: errbuf := g_errbuf;
3313:
3314: BIS_COLLECTION_UTILITIES.WRAPUP(
3310: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3311: retcode := -1;
3312: errbuf := g_errbuf;
3313:
3314: BIS_COLLECTION_UTILITIES.WRAPUP(
3315: FALSE,
3316: g_row_count,
3317: g_errbuf,
3318: ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3321:
3322: WHEN OTHERS THEN
3323: ROLLBACK;
3324: g_errbuf := sqlerrm ||' - '||sqlcode;
3325: BIS_COLLECTION_UTILITIES.put_line(' ');
3326: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3327: retcode := -1;
3328: errbuf := g_errbuf;
3329:
3322: WHEN OTHERS THEN
3323: ROLLBACK;
3324: g_errbuf := sqlerrm ||' - '||sqlcode;
3325: BIS_COLLECTION_UTILITIES.put_line(' ');
3326: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3327: retcode := -1;
3328: errbuf := g_errbuf;
3329:
3330: BIS_COLLECTION_UTILITIES.WRAPUP(
3326: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3327: retcode := -1;
3328: errbuf := g_errbuf;
3329:
3330: BIS_COLLECTION_UTILITIES.WRAPUP(
3331: FALSE,
3332: g_row_count,
3333: g_errbuf,
3334: ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3348: retcode := '0';
3349: g_load_mode := 'INCREMENTAL';
3350: l_row_count := 0;
3351:
3352: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3353: BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3354:
3355: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_DEL_DETAILS_F_INC')) THEN
3356: RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3349: g_load_mode := 'INCREMENTAL';
3350: l_row_count := 0;
3351:
3352: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3353: BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3354:
3355: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_DEL_DETAILS_F_INC')) THEN
3356: RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3357: return;
3351:
3352: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3353: BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3354:
3355: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_DEL_DETAILS_F_INC')) THEN
3356: RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3357: return;
3358: END IF;
3359:
3371: -- --------------------------------------------
3372: -- Identify Change for Delivery Details
3373: -- --------------------------------------------
3374:
3375: BIS_COLLECTION_UTILITIES.put_line('Identifying changed records');
3376:
3377: g_incre_start_date := sysdate;
3378: BIS_COLLECTION_UTILITIES.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
3379:
3374:
3375: BIS_COLLECTION_UTILITIES.put_line('Identifying changed records');
3376:
3377: g_incre_start_date := sysdate;
3378: BIS_COLLECTION_UTILITIES.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
3379:
3380: l_row_count := IDENTIFY_CHANGE_DETAIL_ICRL;
3381:
3382: IF (l_row_count = -1) THEN
3384: ELSIF (l_row_count = 0) THEN
3385: g_row_count := 0;
3386: ELSE
3387:
3388: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3389: BIS_COLLECTION_UTILITIES.Put_Line('Analyzing temp tables');
3390: FII_UTIL.Start_Timer;
3391:
3392: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3385: g_row_count := 0;
3386: ELSE
3387:
3388: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3389: BIS_COLLECTION_UTILITIES.Put_Line('Analyzing temp tables');
3390: FII_UTIL.Start_Timer;
3391:
3392: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3393: TABNAME => 'ISC_DBI_TMP_DEL_DETAILS');
3414: -- --------------------------------------------
3415: -- Merge data into Sum2 table
3416: -- --------------------------------------------
3417:
3418: BIS_COLLECTION_UTILITIES.put_line(' ');
3419: BIS_COLLECTION_UTILITIES.put_line('Merging data to fact tables');
3420:
3421: g_row_count := Merge_Detail_Fact(ceil(l_row_count/g_batch_size));
3422:
3415: -- Merge data into Sum2 table
3416: -- --------------------------------------------
3417:
3418: BIS_COLLECTION_UTILITIES.put_line(' ');
3419: BIS_COLLECTION_UTILITIES.put_line('Merging data to fact tables');
3420:
3421: g_row_count := Merge_Detail_Fact(ceil(l_row_count/g_batch_size));
3422:
3423: BIS_COLLECTION_UTILITIES.put_line('Merged '||nvl(g_row_count,0)||' rows into the fact table');
3419: BIS_COLLECTION_UTILITIES.put_line('Merging data to fact tables');
3420:
3421: g_row_count := Merge_Detail_Fact(ceil(l_row_count/g_batch_size));
3422:
3423: BIS_COLLECTION_UTILITIES.put_line('Merged '||nvl(g_row_count,0)||' rows into the fact table');
3424:
3425: IF (g_row_count = -1) THEN
3426: RAISE l_failure;
3427: END IF;
3431: -- -------------------------------------------------
3432: -- Delete rows from change log tables base on rowid
3433: -- -------------------------------------------------
3434:
3435: BIS_COLLECTION_UTILITIES.put_line('Deleting rows from log tables');
3436: FII_UTIL.Start_Timer;
3437:
3438: DELETE FROM ISC_DBI_WDD_CHANGE_LOG
3439: WHERE rowid IN (select log_rowid from isc_dbi_tmp_wdd_log);
3453: EXCEPTION
3454:
3455: WHEN L_FAILURE THEN
3456: ROLLBACK;
3457: BIS_COLLECTION_UTILITIES.put_line(' ');
3458: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3459: retcode := -1;
3460: errbuf := g_errbuf;
3461:
3454:
3455: WHEN L_FAILURE THEN
3456: ROLLBACK;
3457: BIS_COLLECTION_UTILITIES.put_line(' ');
3458: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3459: retcode := -1;
3460: errbuf := g_errbuf;
3461:
3462: BIS_COLLECTION_UTILITIES.WRAPUP(
3458: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3459: retcode := -1;
3460: errbuf := g_errbuf;
3461:
3462: BIS_COLLECTION_UTILITIES.WRAPUP(
3463: FALSE,
3464: g_row_count,
3465: g_errbuf,
3466: ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3469:
3470: WHEN OTHERS THEN
3471: ROLLBACK;
3472: g_errbuf := sqlerrm ||' - '||sqlcode;
3473: BIS_COLLECTION_UTILITIES.put_line(' ');
3474: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3475: retcode := -1;
3476: errbuf := g_errbuf;
3477:
3470: WHEN OTHERS THEN
3471: ROLLBACK;
3472: g_errbuf := sqlerrm ||' - '||sqlcode;
3473: BIS_COLLECTION_UTILITIES.put_line(' ');
3474: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3475: retcode := -1;
3476: errbuf := g_errbuf;
3477:
3478: BIS_COLLECTION_UTILITIES.WRAPUP(
3474: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3475: retcode := -1;
3476: errbuf := g_errbuf;
3477:
3478: BIS_COLLECTION_UTILITIES.WRAPUP(
3479: FALSE,
3480: g_row_count,
3481: g_errbuf,
3482: ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3500: retcode := '0';
3501: g_load_mode := 'INCREMENTAL';
3502: l_row_count := 0;
3503:
3504: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3505: BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3506:
3507: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_LEG_STOP_F_INC')) THEN
3508: RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3501: g_load_mode := 'INCREMENTAL';
3502: l_row_count := 0;
3503:
3504: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3505: BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3506:
3507: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_LEG_STOP_F_INC')) THEN
3508: RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3509: return;
3503:
3504: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3505: BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3506:
3507: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_LEG_STOP_F_INC')) THEN
3508: RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3509: return;
3510: END IF;
3511:
3523: -- ------------------------------------------------
3524: -- Identify Change for Delivery Legs and Trip Stops
3525: -- ------------------------------------------------
3526:
3527: BIS_COLLECTION_UTILITIES.put_line('Identifying changed records');
3528:
3529: g_incre_start_date := sysdate;
3530: BIS_COLLECTION_UTILITIES.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
3531: l_row_count := IDENTIFY_CHANGE_STOP_LEG_ICRL;
3526:
3527: BIS_COLLECTION_UTILITIES.put_line('Identifying changed records');
3528:
3529: g_incre_start_date := sysdate;
3530: BIS_COLLECTION_UTILITIES.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
3531: l_row_count := IDENTIFY_CHANGE_STOP_LEG_ICRL;
3532:
3533: IF (l_row_count = -1) THEN
3534: RAISE l_failure;
3538: -- --------------
3539: -- Analyze tables
3540: -- --------------
3541:
3542: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3543: BIS_COLLECTION_UTILITIES.Put_Line('Analyzing temp tables');
3544: FII_UTIL.Start_Timer;
3545:
3546: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3539: -- Analyze tables
3540: -- --------------
3541:
3542: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3543: BIS_COLLECTION_UTILITIES.Put_Line('Analyzing temp tables');
3544: FII_UTIL.Start_Timer;
3545:
3546: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3547: TABNAME => 'ISC_DBI_TMP_DEL_LEGS');
3574: -- --------------------------------------------
3575: -- Merge data into Sum2 table
3576: -- --------------------------------------------
3577:
3578: BIS_COLLECTION_UTILITIES.put_line(' ');
3579: BIS_COLLECTION_UTILITIES.put_line('Merging data to fact tables');
3580:
3581: g_row_count := Merge_Leg_Stop_Fact(ceil(l_row_count/g_batch_size));
3582:
3575: -- Merge data into Sum2 table
3576: -- --------------------------------------------
3577:
3578: BIS_COLLECTION_UTILITIES.put_line(' ');
3579: BIS_COLLECTION_UTILITIES.put_line('Merging data to fact tables');
3580:
3581: g_row_count := Merge_Leg_Stop_Fact(ceil(l_row_count/g_batch_size));
3582:
3583: BIS_COLLECTION_UTILITIES.put_line('Merged '||nvl(g_row_count,0)||' rows into the fact tables');
3579: BIS_COLLECTION_UTILITIES.put_line('Merging data to fact tables');
3580:
3581: g_row_count := Merge_Leg_Stop_Fact(ceil(l_row_count/g_batch_size));
3582:
3583: BIS_COLLECTION_UTILITIES.put_line('Merged '||nvl(g_row_count,0)||' rows into the fact tables');
3584:
3585: IF (g_row_count = -1) THEN
3586: RAISE l_failure;
3587: END IF;
3591: -- -------------------------------------------------
3592: -- Delete rows from change log tables base on rowid
3593: -- -------------------------------------------------
3594:
3595: BIS_COLLECTION_UTILITIES.put_line('Deleting rows from log tables');
3596: FII_UTIL.Start_Timer;
3597:
3598: DELETE FROM ISC_DBI_WTS_CHANGE_LOG
3599: WHERE rowid IN (select log_rowid from isc_dbi_tmp_wts_log);
3613: EXCEPTION
3614:
3615: WHEN L_FAILURE THEN
3616: ROLLBACK;
3617: BIS_COLLECTION_UTILITIES.put_line(' ');
3618: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3619: retcode := -1;
3620: errbuf := g_errbuf;
3621:
3614:
3615: WHEN L_FAILURE THEN
3616: ROLLBACK;
3617: BIS_COLLECTION_UTILITIES.put_line(' ');
3618: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3619: retcode := -1;
3620: errbuf := g_errbuf;
3621:
3622: BIS_COLLECTION_UTILITIES.WRAPUP(
3618: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3619: retcode := -1;
3620: errbuf := g_errbuf;
3621:
3622: BIS_COLLECTION_UTILITIES.WRAPUP(
3623: FALSE,
3624: g_row_count,
3625: g_errbuf,
3626: ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3629:
3630: WHEN OTHERS THEN
3631: ROLLBACK;
3632: g_errbuf := sqlerrm ||' - '||sqlcode;
3633: BIS_COLLECTION_UTILITIES.put_line(' ');
3634: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3635: retcode := -1;
3636: errbuf := g_errbuf;
3637:
3630: WHEN OTHERS THEN
3631: ROLLBACK;
3632: g_errbuf := sqlerrm ||' - '||sqlcode;
3633: BIS_COLLECTION_UTILITIES.put_line(' ');
3634: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3635: retcode := -1;
3636: errbuf := g_errbuf;
3637:
3638: BIS_COLLECTION_UTILITIES.WRAPUP(
3634: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3635: retcode := -1;
3636: errbuf := g_errbuf;
3637:
3638: BIS_COLLECTION_UTILITIES.WRAPUP(
3639: FALSE,
3640: g_row_count,
3641: g_errbuf,
3642: ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3656: retcode := '0';
3657: g_load_mode := 'INCREMENTAL';
3658: l_row_count := 0;
3659:
3660: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3661: BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3662:
3663: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_FTE_INVOICES_F_INC')) THEN
3664: RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3657: g_load_mode := 'INCREMENTAL';
3658: l_row_count := 0;
3659:
3660: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3661: BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3662:
3663: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_FTE_INVOICES_F_INC')) THEN
3664: RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3665: return;
3659:
3660: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3661: BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load');
3662:
3663: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_FTE_INVOICES_F_INC')) THEN
3664: RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
3665: return;
3666: END IF;
3667:
3675: -- --------------------------------------------
3676: -- Identify Change for Invoice Headers
3677: -- --------------------------------------------
3678:
3679: BIS_COLLECTION_UTILITIES.put_line('Identifying changed records');
3680:
3681: g_incre_start_date := sysdate;
3682: BIS_COLLECTION_UTILITIES.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
3683: l_row_count := IDENTIFY_CHANGE_INVOICE_ICRL;
3678:
3679: BIS_COLLECTION_UTILITIES.put_line('Identifying changed records');
3680:
3681: g_incre_start_date := sysdate;
3682: BIS_COLLECTION_UTILITIES.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
3683: l_row_count := IDENTIFY_CHANGE_INVOICE_ICRL;
3684:
3685: IF (l_row_count = -1) THEN
3686: RAISE l_failure;
3690: -- --------------
3691: -- Analyze tables
3692: -- --------------
3693:
3694: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3695: BIS_COLLECTION_UTILITIES.Put_Line('Analyzing temp tables');
3696: FII_UTIL.Start_Timer;
3697:
3698: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3691: -- Analyze tables
3692: -- --------------
3693:
3694: BIS_COLLECTION_UTILITIES.Put_Line(' ');
3695: BIS_COLLECTION_UTILITIES.Put_Line('Analyzing temp tables');
3696: FII_UTIL.Start_Timer;
3697:
3698: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
3699: TABNAME => 'ISC_DBI_TMP_FTE_INVOICES');
3722: -- --------------------------------------------
3723: -- Merge data into Base Summary
3724: -- --------------------------------------------
3725:
3726: BIS_COLLECTION_UTILITIES.put_line(' ');
3727: BIS_COLLECTION_UTILITIES.put_line('Merging data to fact tables');
3728:
3729: g_row_count := Merge_Invoice_Fact(ceil(l_row_count/g_batch_size));
3730:
3723: -- Merge data into Base Summary
3724: -- --------------------------------------------
3725:
3726: BIS_COLLECTION_UTILITIES.put_line(' ');
3727: BIS_COLLECTION_UTILITIES.put_line('Merging data to fact tables');
3728:
3729: g_row_count := Merge_Invoice_Fact(ceil(l_row_count/g_batch_size));
3730:
3731: BIS_COLLECTION_UTILITIES.put_line('Merged '||nvl(g_row_count,0)||' rows into the fact tables');
3727: BIS_COLLECTION_UTILITIES.put_line('Merging data to fact tables');
3728:
3729: g_row_count := Merge_Invoice_Fact(ceil(l_row_count/g_batch_size));
3730:
3731: BIS_COLLECTION_UTILITIES.put_line('Merged '||nvl(g_row_count,0)||' rows into the fact tables');
3732:
3733: IF (g_row_count = -1) THEN
3734: RAISE l_failure;
3735: END IF;
3739: -- -------------------------------------------------
3740: -- Delete rows from change log tables base on rowid
3741: -- -------------------------------------------------
3742:
3743: BIS_COLLECTION_UTILITIES.put_line('Deleting rows from log tables');
3744:
3745: FII_UTIL.Start_Timer;
3746:
3747: DELETE FROM ISC_DBI_FIH_CHANGE_LOG
3762: EXCEPTION
3763:
3764: WHEN L_FAILURE THEN
3765: ROLLBACK;
3766: BIS_COLLECTION_UTILITIES.put_line(' ');
3767: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3768: retcode := -1;
3769: errbuf := g_errbuf;
3770:
3763:
3764: WHEN L_FAILURE THEN
3765: ROLLBACK;
3766: BIS_COLLECTION_UTILITIES.put_line(' ');
3767: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3768: retcode := -1;
3769: errbuf := g_errbuf;
3770:
3771: BIS_COLLECTION_UTILITIES.WRAPUP(
3767: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
3768: retcode := -1;
3769: errbuf := g_errbuf;
3770:
3771: BIS_COLLECTION_UTILITIES.WRAPUP(
3772: FALSE,
3773: g_row_count,
3774: g_errbuf,
3775: ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,
3778:
3779: WHEN OTHERS THEN
3780: ROLLBACK;
3781: g_errbuf := sqlerrm ||' - '||sqlcode;
3782: BIS_COLLECTION_UTILITIES.put_line(' ');
3783: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3784: retcode := -1;
3785: errbuf := g_errbuf;
3786:
3779: WHEN OTHERS THEN
3780: ROLLBACK;
3781: g_errbuf := sqlerrm ||' - '||sqlcode;
3782: BIS_COLLECTION_UTILITIES.put_line(' ');
3783: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3784: retcode := -1;
3785: errbuf := g_errbuf;
3786:
3787: BIS_COLLECTION_UTILITIES.WRAPUP(
3783: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
3784: retcode := -1;
3785: errbuf := g_errbuf;
3786:
3787: BIS_COLLECTION_UTILITIES.WRAPUP(
3788: FALSE,
3789: g_row_count,
3790: g_errbuf,
3791: ISC_DBI_WSH_FTE_OBJECTS_C.g_push_from_date,